```CREATE view costdetail as
select * from (SELECT
ROW_NUMBER() over (PARTITION by convert(varchar(10),year(ecE.fdate),111)+'年'+convert(varchar(10),month(ecE.fdate),111)+'期', DIM.FPRODUCTNO,DIM.FBILLSEQ order by DIM.FBILLSEQ) as aa,
((ec.FBILLNO + '-') + CONVERT(VARCHAR(8000), ecE.FSEQ)) fsrcbillnoseq,
convert(varchar(10),year(ecE.fdate),111)+'年'+convert(varchar(10),month(ecE.fdate),111)+'期' as FYearPeriod,
convert(varchar(10),ecE.fdate,111) as date,
16 frowtype,
aps.FSTEP fstep,
(aps.FSTEP - 100) fparentid,
aps.FSRCRECID fsrcrecid,
apr.FRECID frecid,
apr.FSENDID fsendid,
ccs.FNUMBER fsendccnumber,
ccsL.FNAME fsendccname,
WASL.FNAME fsendwaname,
EXSL.FNAME fexpenseitemname,
aps.FAMOUNT famount,
aps.FRECEIVEDAMOUNT freceivedamount,
aps.FALLOCATEDAMOUNT fallocatedamount,
((aps.FAMOUNT + aps.FRECEIVEDAMOUNT) - aps.FALLOCATEDAMOUNT) fwaitalloamount,
CLCL.FNAME fallocatestd,
apr.FWEIGHTQTY fworktimecosttoal, apr.FWEIGHTTOTAL fweighttotal, apr.FAMOUNT frecamount,
ccr.FNUMBER frecccnumber, ccrL.FNAME frecccname, WARL.FNAME frecwaname, MTR.FNUMBER fproductnumber, MTRL.FNAME fproductname, DIM.FPRODUCTNO fproorderno, DIM.FBILLSEQ fproorderseq, apr.FALLOCATEDSTATUS fallocatedstatus
FROM T_CB_EXPALLOPROCESSSEND_H aps
INNER JOIN T_CB_EXPALLOPROCESSREC_H apr ON aps.FSENDID = apr.FSENDID
INNER JOIN T_CB_EXPENSECOLLENTRY ecE ON (aps.FBILLID = ecE.FID AND aps.FBILLENTRYID = ecE.FENTRYID)
INNER JOIN T_CB_EXPENSECOLLECTION ec ON ec.FID = ecE.FID
LEFT OUTER JOIN T_CB_COSTCENTER ccs ON ccs.FCOSTCENTERID = aps.FCOSTCENTERID
LEFT OUTER JOIN T_CB_COSTCENTER_L ccsL ON (ccsL.FCOSTCENTERID = ccs.FCOSTCENTERID AND ccsL.FLOCALEID = 2052)
LEFT OUTER JOIN T_CB_COSTCENTER ccr ON ccr.FCOSTCENTERID = apr.FCOSTCENTERID LEFT OUTER JOIN T_CB_COSTCENTER_L ccrL ON (ccrL.FCOSTCENTERID = ccr.FCOSTCENTERID AND ccrL.FLOCALEID = 2052)
LEFT OUTER JOIN T_CB_WORKACTIVITIES_L WASL ON (aps.FWORKACTIVEID = WASL.FWORKACTIVEID AND WASL.FLOCALEID = 2052) LEFT OUTER JOIN T_CB_WORKACTIVITIES_L WARL ON (apr.FWORKACTIVEID = WARL.FWORKACTIVEID AND WARL.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_EXPENSE_L EXSL ON (aps.FEXPENSEITEMID = EXSL.FEXPID AND EXSL.FLOCALEID = 2052)
LEFT OUTER JOIN T_CB_PROORDERDIME DIM ON apr.FPRODUCTDIMEID = DIM.FPRODUCTDIMEID
LEFT OUTER JOIN t_bd_Material MTR ON DIM.FPRODUCTID = MTR.FMATERIALID
LEFT OUTER JOIN t_bd_Material_l MTRL ON (MTRL.FMATERIALID = MTR.FMATERIALID AND MTRL.FLOCALEID = 2052)
LEFT OUTER JOIN T_CB_COSTALLOCCRITERIA_L CLCL ON (CLCL.FSTANDARDID = apr.FSTDSETID AND CLCL.FLOCALEID = 2052)) costresult where costresult.aa=1;
```
