【O】成本计算相关数据检查脚本

[code]--检查当前期间费用分配金额和成本计算单本期投入金额是否相等
select acctSys.fname as 核算体系,org.FNAME as 核算组织,policy.FNAME as 会计政策,acctg.FYEAR as 会计年,acctg.FPERIOD as 会计期间,
b.FOUTACCTGID,dim.FPRODUCTNO,dim.FBILLSEQ,b.FAmount as 费用分配结果金额,isnull(c.famount,0) as 成本计算单本期投入金额,b.FPRODUCTDIMEID
from
(
select a.FOUTACCTGID,a.FPRODUCTDIMEID,SUM(a.FAmount) as FAmount
from
(
select send.FOUTACCTGID,rec.FPRODUCTDIMEID,SUM(rec.famount) as FAmount from T_CB_COSTALLORESULTREC rec
inner join T_CB_COSTALLORESULTSEND send on send.FSENDID=rec.FSENDID
where rec.FPRODUCTDIMEID>0 and send.FSTEP=6 and rec.FPRODUCTDIMEID=send.FPRODUCTDIMEID
group by send.FOUTACCTGID,rec.FPRODUCTDIMEID
union all
select send.FOUTACCTGID,rec.FPRODUCTDIMEID,SUM(rec.famount) as FAmount from T_CB_EXPALLORESULTREC rec
inner join T_CB_EXPALLORESULTSEND send on send.FSENDID=rec.FSENDID
where rec.FPRODUCTDIMEID>0 --and rec.FPRODUCTDIMEID=send.FPRODUCTDIMEID
group by send.FOUTACCTGID,rec.FPRODUCTDIMEID
)a group by a.FOUTACCTGID,a.FPRODUCTDIMEID
)b
inner join T_CB_PROORDERDIME dim on dim.FPRODUCTDIMEID=b.FPRODUCTDIMEID
inner join T_HS_OUTACCTG acctg on acctg.FID=b.FOUTACCTGID
inner join T_HS_CALDIMENSIONS calDim on calDim.FDIMENSIONID=acctg.FDIMENSIONID
inner join T_ORG_ACCOUNTSYSTEM_L acctSys on acctSys.FACCTSYSTEMID=calDim.FACCTSYSTEMID and acctSys.FLOCALEID=2052
inner join T_ORG_ORGANIZATIONS_L org on org.FORGID=calDim.FFINORGID and org.FLOCALEID=2052
inner join T_FA_ACCTPOLICY_L policy on policy.FACCTPOLICYID=calDim.FACCTPOLICYID and policy.FLOCALEID=2052
left join
(
select info.FACCTGID,dim.FPRODUCTDIMEID,SUM(exp.FCURRINPUTAMOUNT) as FAmount from T_CB_COSTCALEXPENSE exp
inner join T_CB_PROORDERINFO info on info.FID= exp.FID
inner join T_CB_PROORDERDIME dim on dim.FPRODUCTDIMEID=info.FPRODUCTDIMEID
where info.FENDINITKEY='1'
group by info.FACCTGID,dim.FPRODUCTDIMEID
)c on c.FACCTGID=b.FOUTACCTGID and c.FPRODUCTDIMEID=b.FPRODUCTDIMEID
where b.FAmount <> ISNULL(c.FAmount,0) ;

--检查历史期间费用分配金额和成本计算单本期投入金额是否相等
select acctSys.fname as 核算体系,org.FNAME as 核算组织,policy.FNAME as 会计政策,acctg.FYEAR as 会计年,acctg.FPERIOD as 会计期间,
b.FOUTACCTGID,dim.FPRODUCTNO,dim.FBILLSEQ,b.FAmount as 费用分配结果金额,isnull(c.famount,0) as 成本计算单本期投入金额,b.FPRODUCTDIMEID
from
(
select a.FOUTACCTGID,a.FPRODUCTDIMEID,SUM(a.FAmount) as FAmount
from
(
select send.FOUTACCTGID,rec.FPRODUCTDIMEID,SUM(rec.famount) as FAmount from T_CB_COSTALLORESULTREC_H rec
inner join T_CB_COSTALLORESULTSEND_H send on send.FSENDID=rec.FSENDID
where rec.FPRODUCTDIMEID>0 and send.FSTEP=6 and rec.FPRODUCTDIMEID=send.FPRODUCTDIMEID
group by send.FOUTACCTGID,rec.FPRODUCTDIMEID
union all
select send.FOUTACCTGID,rec.FPRODUCTDIMEID,SUM(rec.famount) as FAmount from T_CB_EXPALLORESULTREC_H rec
inner join T_CB_EXPALLORESULTSEND_H send on send.FSENDID=rec.FSENDID
where rec.FPRODUCTDIMEID>0 --and rec.FPRODUCTDIMEID=send.FPRODUCTDIMEID
group by send.FOUTACCTGID,rec.FPRODUCTDIMEID
)a group by a.FOUTACCTGID,a.FPRODUCTDIMEID
)b
inner join T_CB_PROORDERDIME dim on dim.FPRODUCTDIMEID=b.FPRODUCTDIMEID
inner join T_HS_OUTACCTG acctg on acctg.FID=b.FOUTACCTGID
inner join T_HS_CALDIMENSIONS calDim on calDim.FDIMENSIONID=acctg.FDIMENSIONID
inner join T_ORG_ACCOUNTSYSTEM_L acctSys on acctSys.FACCTSYSTEMID=calDim.FACCTSYSTEMID and acctSys.FLOCALEID=2052
inner join T_ORG_ORGANIZATIONS_L org on org.FORGID=calDim.FFINORGID and org.FLOCALEID=2052
inner join T_FA_ACCTPOLICY_L policy on policy.FACCTPOLICYID=calDim.FACCTPOLICYID and policy.FLOCALEID=2052
left join
(
select info.FACCTGID,dim.FPRODUCTDIMEID,SUM(exp.FCURRINPUTAMOUNT) as FAmount from T_CB_COSTCALEXPENSE_H exp
inner join T_CB_PROORDERINFO_H info on info.FID= exp.FID
inner join T_CB_PROORDERDIME dim on dim.FPRODUCTDIMEID=info.FPRODUCTDIMEID
where info.FENDINITKEY='1'
group by info.FACCTGID,dim.FPRODUCTDIMEID
)c on c.FACCTGID=b.FOUTACCTGID and c.FPRODUCTDIMEID=b.FPRODUCTDIMEID
where b.FAmount <> ISNULL(c.FAmount,0) ;
[/code]