with a as (
SELECT product.FID , masResult.FENTRYID,((CONVERT(VARCHAR(8000), acctg.FYear) + N’年’) + CONVERT(VARCHAR(8000), acctg.FPeriod)) + N’期’ c, (acctg.FYear * 100) + acctg.FPeriod 日期,
product.FORDERBILLNO, CASE WHEN product.FOrderBillSeq = 0 THEN ‘’ ELSE CONVERT(VARCHAR(8000), product.FOrderBillSeq) END a,
CASE WHEN productDime.FPROORDERTYPE = ‘PO’ THEN N’普通生产’ WHEN productDime.FPROORDERTYPE = ‘SPO’ THEN N’简单生产’
WHEN productDime.FPROORDERTYPE = ‘REM’ THEN N’重复生产’ ELSE N’委外生产’ END d, CONVERT(VARCHAR(8000), productDime.FCOSTCENTERID) 年月,
CONVERT(VARCHAR(8000), productDime.FPRODUCTID) b5, product.FPRODUCTDIMEID, productDime.FPRODUCTNO, ‘’ a5, CASE WHEN productDime.FBILLSeq = 0
THEN ‘’ ELSE CONVERT(VARCHAR(8000), productDime.FBILLSeq) END b1, product_M.FNUMBER, ISNULL(material_L.FName, ‘’) b2, ISNULL(material_L.FSpecification, ‘’) b,
ISNULL(center.FNumber, ‘’) a1, ISNULL(center_L.FName, ‘’) a2, ISNULL(lot.FNumber, ‘’) a3, ISNULL(productDime.FMTONO, N’ ') a4,
ISNULL(bom.FNumber, ‘’) a9, product.FAuxPropId, ISNULL(unit_L.FName, ‘’) a15, unit.FPrecision, ‘变量’ a6, product.FPlanQty,
masResult.FBeginQty, masResult.FBeginAmount, masResult.FCurrInputQty, masResult.FCurrInputAmount, masResult.FAdjCurrInputQty,
masResult.FAdjCurrInputAmount, masResult.FCurrInputQty + masResult.FAdjCurrInputQty d5, masResult.FCurrInputAmount + masResult.FAdjCurrInputAmount d4,
masResult.FSumCurrInputQty, masResult.FSumCurrInputAmount, masResult.FEndQty, masResult.FEndAmount,
masResult.FQualifiedInQty + masResult.FDysInQty + masResult.FWasteInQty d3, CASE WHEN (masResult.FQualifiedInQty + masResult.FDysInQty + masResult.FWasteInQty) = 0
THEN 0 ELSE ROUND(((masResult.FQualifiedInAmount + masResult.FDysInAmount + masResult.FWasteInAmount) / (masResult.FQualifiedInQty + masResult.FDysInQty + masResult.FWasteInQty)),
10) END b6, masResult.FQualifiedInAmount + masResult.FDysInAmount + masResult.FWasteInAmount b10, masResult.FSumQualifiedInQty, masResult.FSumQualifiedInAmount,
masResult.FSumDysInQty, masResult.FSumDysInAmount, masResult.FSumWasteInQty, masResult.FSumWasteInAmount,
masResult.FSumQualifiedInQty + masResult.FSumDysInQty + masResult.FSumWasteInQty d1,
CASE WHEN (masResult.FSumQualifiedInQty + masResult.FSumDysInQty + masResult.FSumWasteInQty) = 0 THEN 0 ELSE
ROUND(((masResult.FSumQualifiedInAmount + masResult.FSumDysInAmount + masResult.FSumWasteInAmount) / (masResult.FSumQualifiedInQty + masResult.FSumDysInQty + masResult.FSumWasteInQty)), 10) END b7,
masResult.FSumQualifiedInAmount + masResult.FSumDysInAmount + masResult.FSumWasteInAmount d9 FROM T_CB_PROORDERINFO_H product
INNER JOIN T_CB_CostCalExpense_H masResult ON product.FID = masResult.FID INNER JOIN T_CB_PROORDERDIME productDime ON product.FProductDimeId = productDime.FProductDimeId and product.FCONVEYDATE is not null
INNER JOIN T_HS_OUTACCTG acctg ON product.FACCTGID = acctg.FId INNER JOIN t_bd_Material product_M ON productDime.FPRODUCTID = product_M.FMATERIALID
LEFT OUTER JOIN t_bd_Material_l material_L ON (material_L.FMaterialId = product_M.FMATERIALID AND material_L.FLOCALEID = 2052)
INNER JOIN T_BD_MATERIALBASE baseunit ON baseunit.FMaterialId = product_M.FMATERIALID INNER JOIN T_BD_UNIT unit ON baseunit.FBaseUnitId = unit.FUNITID
LEFT OUTER JOIN T_BD_UNIT_L unit_L ON (unit.FUNITID = unit_L.FUNITID AND unit_L.FLOCALEID = 2052)
LEFT OUTER JOIN T_CB_COSTCENTER center ON productDime.FCOSTCENTERID = center.FCOSTCENTERID
LEFT OUTER JOIN T_CB_COSTCENTER_L center_L ON (center_L.FCOSTCENTERID = center.FCOSTCENTERID AND center_L.FLOCALEID = 2052)
LEFT OUTER JOIN t_eng_bom bom ON product.FBOMId = bom.FID LEFT OUTER JOIN T_BD_LOTMASTER lot ON product.FLOT = lot.FLOTID
)
select t1.*,t2.FCOSTITEMID,t2.FBEGINAMOUNT,t2.FCURRINPUTAMOUNT from a t1 left join T_CB_COSTCALEXPENSEDETAIL_H t2 on t1.FENTRYID=t2.FENTRYID
order by FPRODUCTNO asc ,a