1、查询本期外购入库的钩稽情况：
/**********外购发票钩稽查询**********/
SELECT B.FAllHookQTY AS [已钩稽数量] ,
B.FAllHookAmount AS [已钩稽金额],
B.FAmount AS [金额],
B.FAmtRef AS [调拨金额],
B.FConsignAmount AS [代销金额],
B.FCurrentHookQTY AS [本期钩稽数量] ,
B.FCurrentHookAmount AS [本期钩稽金额],
B.FEntrySelfA0156 AS [付款金额],
B.FEntrySelfA0157 AS [未付款金额],
B.FEntrySelfB0185 AS [折前金额],
B.FEntrySelfB0450 AS [销售金额],
B.FPlanAmount AS [计划价金额],
B.FStdAllHookAmount AS [已钩稽金额(本位币)],
B.FStdCurrentHookAmount AS [本期钩稽金额(本位币)],
B.FEntrySelfB0162 AS [运费],
B.FEntrySelfB0164 AS [付运费],
B.FMaterialCost AS [材料费],
B.FProcessCost AS [加工费],
B.FCheckStatus AS [核销标志],--Case v1.FCheckStatus when 1 then 'Y' when 2 then 'P' else 'N' end
A.FHookStatus AS [钩稽标志]
FROM dbo.ICStockBill A,dbo.ICStockBillEntry B
WHERE B.FInterID IN (SELECT FInterID FROM dbo.ICStockBill WHERE FBillNo IN ('JIN000058','JIN000068')) AND A.FINTERID=B.FINTERID
2、更新外购入库的钩稽数据:
/*****外购发票钩稽更新*****/
UPDATE B000
SET 本期钩稽金额=加工费,本期钩稽数量=已钩稽数量,核销标志=2
FROM (SELECT B.FAllHookQTY AS [已钩稽数量] ,
B.FAllHookAmount AS [已钩稽金额],
B.FAmount AS [金额],
B.FAmtRef AS [调拨金额],
B.FConsignAmount AS [代销金额],
B.FCurrentHookQTY AS [本期钩稽数量] ,
B.FCurrentHookAmount AS [本期钩稽金额],
B.FEntrySelfA0156 AS [付款金额],
B.FEntrySelfA0157 AS [未付款金额],
B.FEntrySelfB0185 AS [折前金额],
B.FEntrySelfB0450 AS [销售金额],
B.FPlanAmount AS [计划价金额],
B.FStdAllHookAmount AS [已钩稽金额(本位币)],
B.FStdCurrentHookAmount AS [本期钩稽金额(本位币)],
B.FEntrySelfB0162 AS [运费],
B.FEntrySelfB0164 AS [付运费],
B.FMaterialCost AS [材料费],
B.FProcessCost AS [加工费],
B.FCheckStatus AS [核销标志],
A.FHookStatus AS [钩稽标志]
FROM dbo.ICStockBill A,dbo.ICStockBillEntry B
WHERE B.FInterID IN (SELECT FInterID FROM dbo.ICStockBill WHERE FBillNo IN ('JIN000058','JIN000068')) AND A.FINTERID=B.FINTERID) B000
WHERE 本期钩稽金额=0
3、查询外购发票的钩稽情况
SELECT * FROM dbo.ICPurchaseEntry
WHERE FInterID IN(SELECT FInterID FROM dbo.ICPurchase WHERE FBillNo IN ('ZPOFP003490','ZPOFP003354'))
SELECT * FROM dbo.ICPurchase WHERE FBillNo IN ('ZPOFP003490','ZPOFP003354')
/*外购发票当前钩稽数量查询*/
SELECT B.FAllHookAmount AS [已钩稽金额],
B.FAllHookQTY AS [已钩稽数量],
B.FAmount AS [金额],
B.FAmountMustOld AS [应计成本费用(本位币)],
B.FCheckAmount AS [本币核销金额],
B.FCheckAmountFor AS [原币已核销金额],
B.FCheckQty AS [已核销数量],
B.FCurrentHookAmount AS [本期钩稽金额],
B.FCurrentHookQTY AS [本期钩稽数量],
B.FInterID AS [购货发票内码],
B.FPrice AS [单价],
B.FQty AS [数量],
B.FRemainAmount AS [本币未核销金额],
B.FRemainAmountFor AS [原币未核销金额],
B.FStdAllHookAmount AS [已钩稽金额(本位币)],
B.FStdAmount AS [金额（本位币）],
B.FStdAmountincludetax AS [金额（本位币）],
B.FStdAmtDiscount AS [折扣额(本位币)],
B.FStdCurrentHookAmount AS [本期钩稽金额(本位币)],
B.FStdTaxAmount AS [税额（本位币）]
FROM dbo.ICPurchase AS A,dbo.ICPurchaseEntry B WHERE A.FINTERID=B.FInterID AND A.FBillNo IN ('ZPOFP003490','ZPOFP003354')
4、更新发票钩稽数据
UPDATE C000
SET 本期钩稽金额=金额,本期钩稽数量=已钩稽数量,[本期钩稽金额(本位币)]=金额
FROM (SELECT B.FAllHookAmount AS [已钩稽金额],
B.FAllHookQTY AS [已钩稽数量],
B.FAmount AS [金额],
B.FAmountMustOld AS [应计成本费用(本位币)],
B.FCheckAmount AS [本币核销金额],
B.FCheckAmountFor AS [原币已核销金额],
B.FCheckQty AS [已核销数量],
B.FCurrentHookAmount AS [本期钩稽金额],
B.FCurrentHookQTY AS [本期钩稽数量],
B.FInterID AS [购货发票内码],
B.FPrice AS [单价],
B.FQty AS [数量],
B.FRemainAmount AS [本币未核销金额],
B.FRemainAmountFor AS [原币未核销金额],
B.FStdAllHookAmount AS [已钩稽金额(本位币)],
B.FStdAmount AS [金额（本位币）],
B.FStdAmtDiscount AS [折扣额(本位币)],
B.FStdCurrentHookAmount AS [本期钩稽金额(本位币)],
B.FStdTaxAmount AS [税额（本位币）]
FROM dbo.ICPurchase AS A,dbo.ICPurchaseEntry B WHERE A.FINTERID=B.FInterID AND A.FBillNo IN ('ZPOFP003490','ZPOFP003354')) C000
WHERE C000.本期钩稽金额=0
5、查询单据钩稽表（钩稽日志）
SELECT * FROM ICHookRelations WHERE FIBNo IN ('ZPOFP003490','ZPOFP003354')
6、更改钩稽表（钩稽日志）数据:(委外加工费暂估冲销)
UPDATE dbo.ICHookRelations
SET FPeriod=11,FYear=2016
WHERE FIBInterID=4817 AND FIBNo IN ('ZPOFP003490','ZPOFP003354')
8、委外加工入库（有发票）-生成凭证
—**完**—
赞 5
打赏推荐阅读