材料成本计算相关表

完工成本结转(数量金额模)

V_CB_CMPTCOSTQTYAMT.生产订单信息表 / V_CB_CMPTCOSTQTYAMTENTRY 费用计算明细

完工数量来自于T_CB_COSTCALEXPENSE_H主表

costmain.FWASTEINQTY as  + costmain.FDYSINQTY + costmain.FQUALIFIEDINQTY AS fcurcomqty

本期废品入库-数量 + 本期不良入库-数量 +  本期合格入库-数量 = 完工数量

7月 5.05.01.0069 【FMATERIALID.物料代码 = 124431】

基础数据与明细

生产订单信息表

-- 完工数量
select FCURCOMQTY from V_CB_CMPTCOSTQTYAMT 
where FPRODUCTID  = 124431 
and FPERIODENDDATE = '2023-07-31'

select * from V_CB_CMPTCOSTQTYAMT  e
where  1=1 
and e.FYEAR = 2023  -- 年份
and e.FPERIOD = 7 -- 月份
-- 2207行

-- 完工数量	
select * from (
    select c.FNUMBER 物料代码, d.FNAME 物料,FSPECIFICATIONGG 型号
    ,  z.FWASTEINQTY + z.FDYSINQTY + z.FQUALIFIEDINQTY AS  完工数量 
    from T_CB_PROORDERINFO a
    left join  T_CB_COSTCALEXPENSE  z on z.FID = a.FID 
    left join T_CB_PROORDERDIME b on a.FPRODUCTDIMEID =b.FPRODUCTDIMEID
    left join T_BD_MATERIAL c on b.FPRODUCTID = c.FMATERIALID
    left join T_BD_MATERIAL_L d on d.FMATERIALID =c.FMATERIALID 
    left join  T_HS_OUTACCTG e on e.FID = a.FACCTGID
    where 1=1 --b.FPRODUCTID  = 124431  -- 物料代码 
    and e.FYEAR = 2023  -- 年份
    and e.FPERIOD = 7 -- 月份
) va  where 完工数量>0
-- 2206行

定额因子+明细

select *  from T_CB_QUOTAFACTOR  a
left join T_CB_QUOTAFACTOR_L b on a.FQUOTAID = b.FQUOTAID



select a.FNUMBER,b.FNAME,c.FSTANDARDVALUE 
from T_CB_QUOTAFACTOR  a
left join T_CB_QUOTAFACTOR_L b on a.FQUOTAID = b.FQUOTAID
left join T_CB_QUOTAFACTORENTRY c on c.FQUOTAID =  b.FQUOTAID
where  1=1
and c.FPRODUCTID =   124431 
and  b.FNAME like '7月%工资定额'

生产订单+明细

select * from T_PRD_MO  where FBILLNO='515-20230324014'

select * from T_PRD_MOENTRY where FMATERIALID = 124431  -- FID = 108500

生产入库单+明细

select * from T_PRD_INSTOCK where FBILLNO='SCRK00002201' --  120825  FMODIFIERID
select  FMUSTQTY,FREALQTY ,FMATERIALID from T_PRD_INSTOCKENTRY  
where FMATERIALID = 124431 --  FID = 102562

成本中心

select a.FNUMBER,b.*  from T_HS_COSTITEM a
left join T_HS_COSTITEM_L b on a.FCOSTITEMID = b.FCOSTITEMID
where a.FNUMBER = 'CBXM00005_SYS'

物料

select * from t_bd_material  a
left join T_BD_MATERIAL_L b on a.FMASTERID = b.FMATERIALID

where a.FNUMBER ='5.06.52.0006'

v1备份

select year(a.FPERIODENDDATE) 年份, month(a.FPERIODENDDATE) 期间,a.FPRODUCTNO 单据编号,j1.fname 车间, d.FNUMBER 物料编码 ,d1.FNAME 名称 
,d1.FSPECIFICATIONGG 规格,d1.F_QINL_SPECIFICATION 型号
,g1.FNAME 单位, a.FCURCOMQTY 完工数量
,s.fnumerator 分子 ,s.fdenominator 分母
,s.fnumerator/ s.fdenominator 单位定额材料
,a.FCURCOMQTY *(s.fnumerator/ s.fdenominator) 定额材料成本
,isnull(a1.FCURCOMAMOUNT,0) 直接材料成本
,isnull(a1s.FCURCOMAMOUNT,0) 间接材料成本
,isnull(a1.FCURCOMAMOUNT,0) + isnull(a1s.FCURCOMAMOUNT,0) 实际材料成本合计
,(isnull(a1.FCURCOMAMOUNT,0) + isnull(a1s.FCURCOMAMOUNT,0) - a.FCURCOMQTY *(s.fnumerator/ s.fdenominator))/a.FCURCOMQTY *(s.fnumerator/ s.fdenominator)*100 直接材料损耗率
from V_CB_CMPTCOSTQTYAMT a 
--连接用料清单
left join (select s1.fmobillno,s2.fpickedqty,s1.fnumerator,s1.fdenominator 
           from T_PRD_PPBOMENTRY s1 join T_PRD_PPBOMENTRY_Q s2
           on s1.fentryid =s2.fentryid
           where s2.fpickedqty > 0) s 
on a.FPRODUCTNO = s.fmobillno
--筛选 直接材料成本和间接材料成本
left join V_CB_CMPTCOSTQTYAMTENTRY a1 on a.FID = a1.FID 
and a1.FCOSTITEMID = '20522'
left join  T_HS_COSTITEM a2 on a1.FCOSTITEMID = a2.FCOSTITEMID 
left join V_CB_CMPTCOSTQTYAMTENTRY a1s on a.FID = a1s.FID 
and a1s.FCOSTITEMID = '167381'
left join  T_HS_COSTITEM a2s on a1s.FCOSTITEMID = a2s.FCOSTITEMID 
--连接其他字段
left join T_CB_QUOTAFACTORENTRY c on c.FPRODUCTID = a.fproductid 
left join T_BD_MATERIAL d on a.fproductid = d.FMATERIALID 
left join t_BD_MaterialBase e on d.FMATERIALID =e.FMATERIALID 
left join T_BD_MATERIAL_L d1 on d.FMATERIALID =d1.FMATERIALID 
left join T_BD_UNIT_L g1 on e.FBASEUNITID = g1.FUNITID 
left join T_CB_COSTCENTER j on a.FCOSTCENTERID = j.FCOSTCENTERID
left join T_CB_COSTCENTER_L j1 on j.fcostcenterid = j1.fcostcenterid 
where j1.fname is not null
and year(a.FPERIODENDDATE) = #FYear#
and  month(a.FPERIODENDDATE)  = #FMonth#
and j1.fname like '%#FCheJ#%'