成本计算相关表

完工数量前端单据/报表 【完工成本结转(数量金额式)】

定额因子+每月写入报表(类型+物料+因子)

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 完工数量,
c.FSTANDARDVALUE 定额因子 , a.FCURCOMQTY*c.FSTANDARDVALUE  定额人工成本 ,a2.FNUMBER 成本项目编码, a1.FCURCOMAMOUNT 实际人工成本 
, (a.FCURCOMQTY*c.FSTANDARDVALUE/a1.FCURCOMAMOUNT)*100 直接人工生产效率
from V_CB_CMPTCOSTQTYAMT a 
left join V_CB_CMPTCOSTQTYAMTENTRY a1 on a.FID = a1.FID 
left join  T_HS_COSTITEM a2 on a1.FCOSTITEMID = a2.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 a2.FNUMBER ='CBXM00005_SYS' --筛选实际人工成本
and year(a.FPERIODENDDATE) = #FYear#
and  month(a.FPERIODENDDATE)  = #FMonth#
and j1.fname like '%#FCheJ#%'