EAS个别报表提取数据解决方案

个别报表取数解决方案
1 整体大致思路
分两部分：1).项目报表—报表模板—报表模板列表—报表项目；
2).数据报表—。

---对应数据；
3).项目与数据通过报表id、公司、年度、期间、数据类型相匹配进行关联；
2.数据解析
1.项目数据表解析
t_csl_cslreport -------财务报表表
t_rpt_template ------ 报表模板表
t_csl_templetitemlist—报表模板对应项目表
t_csl_itemfieldmapped –项目的对应字段
t_csl_dataelement ---数据类型如本期发生、本年累计等
t_org_company ---模板对应的公司
2.数据数据表解析
ys_itemdataentry是视图是由t_csl_itemdataentry001表行转列的视图；
t_csl_itemdataentry项目数据分录
t_csl_itemdata项目数据
t_csl_cslreport对应报表
t_org_company公司
3.财务个别报表数据提取
1.数据表-行转列视图：
create or replace view ys_itemdataentry as
select fid, fld, value
from t_csl_itemdataentry001
unpivot (value for fld in (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F
26,F27,F28,F29,F30,F31,F32,F33,F34,F35,F36,F37,F38,F39,F40,F41,F42,F43,F44,F45,F46,F47,F48, F49,F50,F51,F52,F53,F54,F55,F56,F57,F58,F59,F60,F61,F62,F63,F64,F65,F66,F67,F68,F69,F70,F71 ,F72,F73,F74,F75,F76,F77,F78,F79,F80,F81,F82,F83,F84,F85,F86,F87,F88,F89,F90,F91,F92,F93,F9 4,F95,F96,F97,F98,F99,F100,F101,F102,F103,F104,F105,F106,F107,F108,F109,F110,F111,F112,F1 13,F114,F115,F116,F117,F118,F119,F120,F121,F122,F123,F124,F125,F126,F127,F128,F129,F130, F131,F132,F133,F134,F135,F136,F137,F138,F139,F140,F141,F142,F143,F144,F145,F146,F147,F14 8,F149,F150,F151,F152,F153,F154,F155,F156,F157,F158,F159,F160,F161,F162,F163,F164,F165,F 166,F167,F168,F169,F170,F171,F172,F173,F174,F175,F176,F177,F178,F179,F180,F181,F182,F183 ,F184,F185,F186,F187,F188,F189,F190,F191,F192,F193,F194,F195,F196,F197,F198,F199,F200,F2 01,F202,F203,F204,F205,F206,F207,F208,F209,F210,F211,F212,F213,F214,F215,F216,F217,F218, F219,F220,F221,F222,F223,F224,F225,F226,F227,F228,F229,F230,F231,F232,F233,F234,F235,F23 6,F237,F238,F239,F240,F241,F242,F243,F244,F245,F246,F247,F248,F249,F250,F251,F252,F253,F 254,F255,F256,F257,F258,F259,F260,F261,F262,F263,F264,F265,F266,F267,F268,F269,F270,F271 ,F272,F273,F274,F275,F276,F277,F278,F279,F280,F281,F282,F283,F284,F285,F286,F287,F288,F2 89,F290,F291,F292,F293,F294,F295,F296,F297,F298,F299,F300));
2.项目视图
create or replace view ys_reportitem as
select ccr.fid as rptid,--报表ID
ccr.fyear as rptyear, --年度
ccr.fperiod as rptperiod,--期间
oc.fnumber as companycode, --公司编码
oc.fname_l2 as companyname, --公司名称
cde.fnumber as dataelementcode,--数据类型编码
cde.fname_l2 as dataelementname,--数据类型名称
cri.fname_l2 as itemname,--报表项目名称
cri.fnumber as itemcode,--报表项目编码
cifm.ftablename as tablename,--对应表
cifm.ffieldname as fieldname, --字段名
ctil.fsheetname as sheetname --对应的报表名称
from t_csl_templetitemlist ctil
left join t_rpt_template rt on rt.fid=ctil.ftemplateid
left join t_csl_cslreport ccr on ccr.ftemplateid=rt.fid
left join t_org_company oc on oc.fid=ccr.fcompanyid
left join t_csl_rptitem cri on cri.fnumber=ctil.fitemnumber
left join t_csl_itemfieldmapped cifm on cifm.fitemid=cri.fid
left join t_csl_dataelement cde on cde.finterseq=ctil.fdataelement
where 1=1
and ccr.fauditedstatus=2 ---报表审核状态;
3.数据视图
create or replace view ys_reportdata_new as
select ccr.fid as id,--报表id
ci.fid as dataid,--数据id
ci.fld as fieldname,
ci.value as rptvalue,
ob.fnumber as companycode,--公司编码
ob.fname_l2 as companyname,--公司名称
ccr.fnumber as rptcode,--报表编码
ccr.fname as rptname, --报表名称
ccr.fyear as rptyear, --年度
ccr.fperiod as rptperiod,--期间
cde.fname_l2 as datatypename,--数据类型名称期初数期末数
cde.fnumber as datatypecode--数据类型编码
from ys_itemdataentry ci
left join t_csl_itemdataentry cie on cie.fid=ci.fid
left join t_csl_dataelement cde on cde.finterseq=cie.fdataelement
left join t_csl_itemdata cid on cid.fid=cie.fitemdataid
left join t_csl_cslreport ccr on ccr.fid=cid.freportid
left join t_org_company ob on ob.fid =ccr.fcompanyid
where 1=1
and ccr.FAuditedStatus=2
--and ob.fnumber='01001'
--and ccr.fyear=2016
--and ccr.fperiod=2
--and ci.fld in ('F69','F167')
--and cie.fdataelement=4--期末数;
4.项目视图与数据视图整合
create or replace view ys_reportitemdata as
select
panycode as companycode,--公司编码
panyname as companyname,--公司名称
yri.itemcode as itemcode , --报表项目编码
yri.itemname as itemname,--报表项目名称
yrdn.rptyear as periodyear,--年度
yrdn.rptperiod as periodmonth ,--期间
yrdn.datatypecode as datatypecode,--数据类型编码
yrdn.datatypename as datatypename,--数据类型名称
yri.sheetname as sheetname,--对应报表execl表
yrdn.rptvalue as currentdebit, --对应数值
'' as deptcode,
'' as accountcode,
'0'as isdept
from ys_reportdata_new yrdn
left join ys_reportitem yri on yrdn.id=yri.rptid
and yrdn.rptyear=yri.rptyear
and yrdn.rptperiod=yri.rptperiod
and yrdn.rptperiod=yri.rptperiod
and panycode=panycode
and yrdn.datatypecode=yri.dataelementcode
and yrdn.fieldname=yri.fieldname
where 1=1
--and panycode='01001'
and yrdn.rptyear=to_char(sysdate,'YYYY')
--and yrdn.rptperiod=2
--and yrdn.fieldname in ('F69','F167')
--and yri.itemcode in ('2027','2002')
--and yrdn.datatypecode in ('AMOUNT')
--and yrdn.datatypecode in ('YTDAMOUNT')
and yri.sheetname in ('经营数据表')
--and cie.fdataelement=4--期末数;
order by yri.itemcode;
4.合并报表数据提取
/link?url=L-FZeSq-3uIUhsf3M8DZ3G0A8w2OQ4VcVIrkvbGRAM-Vbs2K_JZ eCdfl-GysDIy2cVs5GG8sLa6uO_Own88p6OOciSdI4mM0ztCy5FE6qHO。