出库应收料工费分摊

update T_SAL_OUTSTOCKENTRY set FCLAmount=0,FRGAmount=0,FFYAmount=0 from T_SAL_OUTSTOCKENTRY

update t2 set t2.FCLAmount=t3.FAmount from T_SAL_OUTSTOCK t1 inner join T_SAL_OUTSTOCKENTRY t2 on t1.FID=t2.FID
inner join (select T1.FBILLNO,T1.FBILLENTRYID,SUM(T2.FACCTGAMOUNT) as FAmount from T_HS_OUTINSTOCKSEQ T1
INNER JOIN T_HS_Expenses T2 ON T2.FSeqEntryId = T1.FENTRYID
INNER JOIN T_BD_EXPENSE_L T3 ON T2.FEXPENSESITEMID = T3.FEXPID AND T3.FLOCALEID = 2052
--and T1.FACCTGID = 100006
and (t3.FNAME='直接材料' or t3.FNAME='间接材料')
group by T1.FBILLNO,T1.FBILLENTRYID
) t3 on t1.FBILLNO=t3.FBILLNO and t2.FENTRYID=t3.FBILLENTRYID

update t2 set t2.FRGAmount=t3.FAmount from T_SAL_OUTSTOCK t1 inner join T_SAL_OUTSTOCKENTRY t2 on t1.FID=t2.FID
inner join (select T1.FBILLNO,T1.FBILLENTRYID,SUM(T2.FACCTGAMOUNT) as FAmount from T_HS_OUTINSTOCKSEQ T1 INNER JOIN T_HS_Expenses T2 ON T2.FSeqEntryId = T1.FENTRYID
INNER JOIN T_BD_EXPENSE_L T3 ON T2.FEXPENSESITEMID = T3.FEXPID AND T3.FLOCALEID = 2052
--and T1.FACCTGID = 100006
and (t3.FNAME='工资' or t3.FNAME='奖金' )
group by T1.FBILLNO,T1.FBILLENTRYID
) t3 on t1.FBILLNO=t3.FBILLNO and t2.FENTRYID=t3.FBILLENTRYID

update t2 set t2.FFYAmount=t3.FAmount from T_SAL_OUTSTOCK t1 inner join T_SAL_OUTSTOCKENTRY t2 on t1.FID=t2.FID
inner join (select T1.FBILLNO,T1.FBILLENTRYID,SUM(T2.FACCTGAMOUNT) as FAmount from T_HS_OUTINSTOCKSEQ T1 INNER JOIN T_HS_Expenses T2 ON T2.FSeqEntryId = T1.FENTRYID
INNER JOIN T_BD_EXPENSE_L T3 ON T2.FEXPENSESITEMID = T3.FEXPID AND T3.FLOCALEID = 2052
--and T1.FACCTGID = 100006
and t3.FNAME<>'工资' and t3.FNAME<>'奖金' and t3.FNAME<>'直接材料' and t3.FNAME<>'间接材料'
group by T1.FBILLNO,T1.FBILLENTRYID
) t3 on t1.FBILLNO=t3.FBILLNO and t2.FENTRYID=t3.FBILLENTRYID

---------------------------------历史

update t2 set t2.FCLAmount=t3.FAmount from T_SAL_OUTSTOCK t1 inner join T_SAL_OUTSTOCKENTRY t2 on t1.FID=t2.FID
inner join (select T1.FBILLNO,T1.FBILLENTRYID,SUM(T2.FACCTGAMOUNT) as FAmount from T_HS_OUTINSTOCKSEQ_H T1
INNER JOIN T_HS_Expenses T2 ON T2.FSeqEntryId = T1.FENTRYID
INNER JOIN T_BD_EXPENSE_L T3 ON T2.FEXPENSESITEMID = T3.FEXPID AND T3.FLOCALEID = 2052
--and T1.FACCTGID = 100006
and (t3.FNAME='直接材料' or t3.FNAME='间接材料')
group by T1.FBILLNO,T1.FBILLENTRYID
) t3 on t1.FBILLNO=t3.FBILLNO and t2.FENTRYID=t3.FBILLENTRYID

update t2 set t2.FRGAmount=t3.FAmount from T_SAL_OUTSTOCK t1 inner join T_SAL_OUTSTOCKENTRY t2 on t1.FID=t2.FID
inner join (select T1.FBILLNO,T1.FBILLENTRYID,SUM(T2.FACCTGAMOUNT) as FAmount from T_HS_OUTINSTOCKSEQ_H T1 INNER JOIN T_HS_Expenses T2 ON T2.FSeqEntryId = T1.FENTRYID
INNER JOIN T_BD_EXPENSE_L T3 ON T2.FEXPENSESITEMID = T3.FEXPID AND T3.FLOCALEID = 2052
--and T1.FACCTGID = 100006
and (t3.FNAME='工资' or t3.FNAME='奖金' )
group by T1.FBILLNO,T1.FBILLENTRYID
) t3 on t1.FBILLNO=t3.FBILLNO and t2.FENTRYID=t3.FBILLENTRYID

update t2 set t2.FFYAmount=t3.FAmount from T_SAL_OUTSTOCK t1 inner join T_SAL_OUTSTOCKENTRY t2 on t1.FID=t2.FID
inner join (select T1.FBILLNO,T1.FBILLENTRYID,SUM(T2.FACCTGAMOUNT) as FAmount from T_HS_OUTINSTOCKSEQ_H T1 INNER JOIN T_HS_Expenses T2 ON T2.FSeqEntryId = T1.FENTRYID
INNER JOIN T_BD_EXPENSE_L T3 ON T2.FEXPENSESITEMID = T3.FEXPID AND T3.FLOCALEID = 2052
--and T1.FACCTGID = 100006
and t3.FNAME<>'工资' and t3.FNAME<>'奖金' and t3.FNAME<>'直接材料' and t3.FNAME<>'间接材料'
group by T1.FBILLNO,T1.FBILLENTRYID
) t3 on t1.FBILLNO=t3.FBILLNO and t2.FENTRYID=t3.FBILLENTRYID

----------------应收单

update b set FCLAmount=CONVERT(decimal(28,2),(e.FCLAmount/e.FREALQTY*FPRICEQTY))
from t_AR_receivable a inner join t_AR_receivableEntry b
on a.FID=b.FID
inner join T_AR_RECEIVABLEENTRY_LK c on c.FENTRYID=b.FENTRYID
inner join
(select f.FCLAmount,f.FREALQTY,c.FBILLNO,c.FID,f.FENTRYID from T_SAL_OUTSTOCK c
inner join T_SAL_OUTSTOCKENTRY_F d on c.FID=d.FID
inner join T_SAL_OUTSTOCKENTRY f on f.FID=d.FID and f.FENTRYID=d.FENTRYID) e
on c.FSBILLID=e.FID and c.FSID=e.FENTRYID

update b set FFYAmount=CONVERT(decimal(28,2),(e.FFYAmount/e.FREALQTY*FPRICEQTY))
from t_AR_receivable a inner join t_AR_receivableEntry b
on a.FID=b.FID
inner join T_AR_RECEIVABLEENTRY_LK c on c.FENTRYID=b.FENTRYID
inner join
(select f.FFYAmount,f.FREALQTY,c.FBILLNO,c.FID,f.FENTRYID from T_SAL_OUTSTOCK c
inner join T_SAL_OUTSTOCKENTRY_F d on c.FID=d.FID
inner join T_SAL_OUTSTOCKENTRY f on f.FID=d.FID and f.FENTRYID=d.FENTRYID) e
on c.FSBILLID=e.FID and c.FSID=e.FENTRYID

update b set FRGAmount=CONVERT(decimal(28,2),(e.FRGAmount/e.FREALQTY*FPRICEQTY))
from t_AR_receivable a inner join t_AR_receivableEntry b
on a.FID=b.FID
inner join T_AR_RECEIVABLEENTRY_LK c on c.FENTRYID=b.FENTRYID
inner join
(select f.FRGAmount,f.FREALQTY,c.FBILLNO,c.FID,f.FENTRYID from T_SAL_OUTSTOCK c
inner join T_SAL_OUTSTOCKENTRY_F d on c.FID=d.FID
inner join T_SAL_OUTSTOCKENTRY f on f.FID=d.FID and f.FENTRYID=d.FENTRYID) e
on c.FSBILLID=e.FID and c.FSID=e.FENTRYID