— =============================================

— Author: 

— Create date:

— Description: 

— =============================================

ALTER PROCEDURE [dbo].[getCHMX]

AS

BEGIN

–声明游标

DECLARE orderNum_03_cursor CURSOR SCROLL

FOR SELECT FDIMEENTRYID,FNUMBER FROM T_UG_SKUIDANDNUMBER WHERE ID BETWEEN 3001 AND 4000

–AND t1.FNUMBER=’10106′

–and FDIMEENTRYID=123175

–2.打开游标

OPEN orderNum_03_cursor

–3.声明游标提取数据所要存放的变量

DECLARE @FDIMEENTRYID int,@FNUMBER NVARCHAR(200)

–4.定位游标到哪一行

FETCH FIRST FROM orderNum_03_cursor INTO @FDIMEENTRYID,@FNUMBER –into的变量数量必须与游标查询结果集的列数相同

WHILE @@fetch_status=0  –提取成功，进行下一条数据的提取操作

BEGIN

BEGIN

–DECLARE @FDIMEENTRYID int,@FNUMBER NVARCHAR(200)

–set @FDIMEENTRYID=123175

–set @FNUMBER=’10106′

INSERT INTO T_UG_StockInDetailAdjReport with(tablock)  (FDIMID, FDate, FBILLTYPEID, FBILLFORMID, FBILLID, FBillSeq, FYear, FPeriod, FBillNo, FType, FRowType, FRECEIVEAMOUNT, FSENDAMOUNT, FSTOCKSTATUSID)

SELECT T1.FDIMEENTRYID, t0.FDate, t0.FBILLTYPEID, ‘HS_AdjustmentBill’ fbillformid, t0.FID fbillid, t1.FSeq, outacct.FYear, outacct.FPeriod, t0.FBillNo, 6 ftype, 6 frowtype, CASE  WHEN t0.FINOUTINDEX = ‘1’ THEN t1.FAdjustmentAmount ELSE 0 END freceiveamount, CASE  WHEN t0.FINOUTINDEX = ‘1’ THEN 0 ELSE t1.FAdjustmentAmount END fsendamount, t1.FSTOCKSTATUSID FROM T_HS_AdjustmentBill t0 INNER JOIN T_HS_AdjustmentBillEntry t1 ON t0.FID = t1.FID INNER JOIN T_HS_OUTACCTG outacct ON outacct.FID = t0.FAcctgID INNER JOIN t_bd_Material mat ON (t1.FMATERIALID = mat.FMATERIALID AND t1.FSTOCKORGID = mat.FUSEORGID) WHERE ((((((((t0.FACCTGSYSTEMID = 1 AND t0.FAcctOrgID = 1) AND t0.FACCTPOLICYID = 1) AND t1.FACCTGDIMEENTRYID = @FDIMEENTRYID

) AND t0.FBUSINESSTYPE = ‘0’) AND (t0.FDate >= ‘2020-11-01 00:00:00’)) AND (t0.FDate < ‘2020-12-01 00:00:00’)) AND t0.FDocumentStatus = ‘C’) AND t0.FFORBIDSTATUS = ‘A’) UNION ALL SELECT T1.FDIMEENTRYID, t0.FDate, t0.FBILLTYPEID, ‘HS_AdjustmentBill’ fbillformid, t0.FID fbillid, t1.FSeq, outacct.FYear, outacct.FPeriod, t0.FBillNo, CASE WHEN t0.FFROMTYPE = ‘7’ THEN 5 ELSE 2 END ftype, CASE WHEN t1.FCROSSPERIODADJUST = ‘1’ THEN 2 ELSE 5 END frowtype, CASE WHEN t0.FINOUTINDEX = ‘1’ THEN t1.FAdjustmentAmount ELSE 0 END freceiveamount, CASE WHEN t0.FINOUTINDEX = ‘1’ THEN 0 ELSE t1.FAdjustmentAmount END fsendamount, t1.FSTOCKSTATUSID FROM T_HS_AdjustmentBill t0 INNER JOIN T_HS_AdjustmentBillEntry t1 ON t0.FID = t1.FID INNER JOIN T_HS_OUTACCTG outacct ON outacct.FID = t0.FAcctgID INNER JOIN t_bd_Material mat ON (t1.FMATERIALID = mat.FMATERIALID AND t1.FSTOCKORGID = mat.FUSEORGID) WHERE (((((((((t0.FACCTGSYSTEMID = 1 AND t0.FAcctOrgID = 1) AND t0.FACCTPOLICYID = 1) AND t1.FACCTGDIMEENTRYID = @FDIMEENTRYID

) AND (t0.FBusinessType <> ‘0’)) AND t0.FISACCTGGENERATE = ‘1’) AND (t0.FDate >= ‘2020-11-01 00:00:00’)) AND (t0.FDate < ‘2020-12-01 00:00:00’)) AND t0.FDocumentStatus = ‘C’) AND t0.FFORBIDSTATUS = ‘A’)

INSERT INTO t_UG_StockInDetailRet with(tablock)  (FISTOTAL, FNUMBER, FDIMID, FACCTGSYSTEMID, FACCTGORGID, FACCTPOLICYID, FYear, FPeriod, FMATERIALID, FMATERIALNAME, FMATERIALGROUPID, FMODEL, FLOTNO, FASSIPROPERTYID, FMATERPROPERTY, FMATERTYPE, FBOMNO, FPLANNO, FSEQUENCENO, FPROJECTNO, FOWNERID, FSTOCKORGID, FSTOCKID, FSTOCKPLACEID, FACCTGRANGEID, FACCTGRANGENAME, FUNITID, FVALUATION, FINOUTKEY, FROWTYPE, FBILLTYPEID, FBILLFORMID, FBILLID, FBILLENTRYID, FBILLNO, FOrderSeq, FBILLSEQ, FBILLDATE, FRECEIVEQTY, FRECEIVEPRICE, FSENDQTY, FSENDPRICE, FENDQTY, FENDPRICE, FRECEIVEAMOUNT, FSENDAMOUNT, FENDAMOUNT, FSTOCKSTATUSID, FQtyDigits)

SELECT DISTINCT 0 fistotal, ROW_NUMBER() OVER( ORDER BY fyear ASC, fperiod ASC, FINOUTKEY ASC, FOrderSeq ASC, FBILLDATE ASC, FBILLNO ASC, FBILLSEQ ASC) fnumber, t.FDIMID, t.FACCTGSYSTEMID, t.FACCTGORGID, t.FACCTPOLICYID, t.FYear, t.FPeriod, t.FMATERIALID, t.FMATERIALNAME, t.FMATERIALGROUP, t.FMODEL, lot.FNumber flotno, t.FASSIPROPERTYID, FMATERPROPERTY, t.FMATERTYPE, t.FBOMNO, t.FPLANNO, t.FSEQUENCENO, t.FPROJECTNO, t.FOWNERID, t.FSTOCKORGID, t.FSTOCKID, t.FSTOCKPLACEID, FACCTGRANGEID, FACCTGRANGENAME, t.FUNITID, FVALUATION, t.FINOUTKEY, t.FROWTYPE, t.FBILLTYPEID, t.FBILLFORMID, t.FBILLID, t.FBILLENTRYID, t.FBILLNO, t.FOrderSeq, t.FBILLSEQ, t.FBILLDATE, FRECEIVEQTY, FRECEIVEPRICE, FSENDQTY, FSENDPRICE, FENDQTY, FENDPRICE, FRECEIVEAMOUNT, FSENDAMOUNT, FENDAMOUNT, FSTOCKSTATUSID, FQtyDigits

FROM (SELECT DISTINCT init.FDIMID, init.FACCTGSYSTEMID, init.FACCTGORGID, init.FACCTPOLICYID, init.FYear, init.FPeriod, init.FMATERIALID, init.FMATERIALNAME, init.FMATERIALGROUP, init.FMODEL, init.FLOT, init.FASSIPROPERTYID, init.FMATERTYPE, init.FBOMNO, init.FPLANNO, init.FSEQUENCENO, init.FPROJECTNO, init.FOWNERID, init.FSTOCKORGID, init.FSTOCKID, init.FSTOCKPLACEID, init.FUNITID, init.FINOUTKEY, init.FROWTYPE, init.FBILLTYPEID, init.FBILLFORMID, init.FBILLID, init.FBILLENTRYID, init.FBILLNO, 0 forderseq, init.FBILLSEQ, init.FBILLDATE, FRECEIVEQTY, FRECEIVEPRICE, FSENDQTY, FSENDPRICE, FENDQTY, FENDPRICE, FRECEIVEAMOUNT, FSENDAMOUNT, FENDAMOUNT, FSTOCKSTATUSID, rang.FNUMBER facctgrangeid, rangl.FNAME facctgrangename, enumL.FCAPTION fmaterproperty, enumL1.FCAPTION fvaluation, unit.FPRECISION fqtydigits FROM (SELECT ((ag.fyear * 100) + ag.fperiod) fyearperiod, bal.FEndInitKey, dim.FEntryId fdimid, cald.FACCTSYSTEMID facctgsystemid, cald.FFinOrgID facctgorgid, cald.FACCTPOLICYID facctpolicyid, ag.FYear fyear, ag.FPeriod fperiod, mat.FNUMBER fmaterialid, mat_L.FNAME fmaterialname, mat.FMATERIALGROUP, mat_L.FSPECIFICATION fmodel, dim.FLOT flot, dim.FAuxPropId fassipropertyid, mat_T.FERPCLSID fmaterproperty, mat_T.FCATEGORYID fmatertype, dim.FBOMId fbomno, dim.FMtoNo fplanno, N’ ‘ fsequenceno, dim.FPROJECTNO fprojectno, dim.FCargoOwnerId fownerid, dim.FStockOrgId fstockorgid, dim.FSTOCKSTATUSID, dim.FStockId fstockid, dim.FStockLocId fstockplaceid, dim.FAcctgRangeId facctgrangeid, mat_T.FBASEUNITID funitid, dim.FVALUATIONMETHOD fvaluation, 1 finoutkey, 1 frowtype, ‘ ‘ fbilltypeid, ‘ ‘ fbillformid, 0 fbillid, 0 fbillentryid, ‘ ‘ fbillno, NULL fbillseq, NULL fbilldate, bal.FYearSumQty freceiveqty, bal.FYearOutSumQty fsendqty, bal.FQty fendqty, bal.FYearSumAmount freceiveamount, CASE  WHEN (bal.FYearSumAmount = 0 OR bal.FYearSumQty = 0) THEN 0 ELSE (bal.FYearSumAmount / bal.FYearSumQty) END freceiveprice, bal.FYearOutSumAmount fsendamount, CASE  WHEN (bal.FYearOutSumAmount = 0 OR bal.FYearOutSumQty = 0) THEN 0 ELSE (bal.FYearOutSumAmount / bal.FYearOutSumQty) END fsendprice, bal.FAmount fendamount, CASE  WHEN (bal.FAmount = 0 OR bal.FQty = 0) THEN 0 ELSE (bal.FAmount / bal.FQty) END fendprice FROM T_HS_OUTACCTG ag INNER JOIN T_HS_BALANCE_H bal ON (ag.fid = bal.fid AND ((bal.FYearSumAmount <> 0) OR (bal.FYearOutSumAmount <> 0) OR (bal.FAmount <> 0) OR (bal.FYearSumQty <> 0) OR (bal.FYearOutSumQty <> 0) OR (bal.FQty <> 0))) INNER JOIN T_HS_CALDIMENSIONS cald ON ag.FDIMENSIONID = cald.FDIMENSIONID INNER JOIN (SELECT TOP 1 stDim.*, ISNULL(ivDim.FLOT, 0) flot, ISNULL(ivDim.FSTOCKORGID, 0) fstockorgid, ISNULL(ivDim.FCargoOwnerId, 0) fcargoownerid, ISNULL(ivDim.FSTOCKSTATUSID, 0) fstockstatusid FROM T_HS_StockDimension stDim LEFT OUTER JOIN T_HS_InivStockDimension ivDim ON stDim.FEntryID = ivDim.FDIMEENTRYID WHERE stDim.FEntryID = @FDIMEENTRYID

) dim ON bal.FDimeEntryId = dim.FEntryId INNER JOIN t_bd_Material mat ON dim.FMASTERID = mat.FMATERIALID INNER JOIN T_BD_MATERIALBASE mat_T ON (mat.FMATERIALID = mat_T.FMATERIALID AND mat.FUSEORGID = mat_T.FUSEORGID) LEFT OUTER JOIN t_bd_Material_l mat_L ON ((mat.FMATERIALID = mat_L.FMATERIALID AND mat.FUSEORGID = mat_L.FUSEORGID) AND

mat_L.FLOCALEID = 2052)) init INNER JOIN T_HS_ACCTGRANGE rang ON init.FACCTGRANGEID = rang.FAcctgRangeId LEFT OUTER JOIN T_HS_ACCTGRANGE_L rangl ON (init.FACCTGRANGEID = rangl.FACCTGRANGEID AND rangl.FLOCALEID = 2052) INNER JOIN T_META_FORMENUMITEM enum ON (enum.FID = ‘ac14913e-bd72-416d-a50b-2c7432bbff63’ AND

enum.FValue = init.FMATERPROPERTY) LEFT OUTER JOIN T_META_FORMENUMITEM_L enumL ON (enum.FENUMID = enumL.FENUMID AND enumL.FLOCALEID = 2052) INNER JOIN T_META_FORMENUMITEM enum1 ON (enum1.FID = ‘eca675f6-d296-4ba9-b9df-170b7b286a73’ AND enum1.FValue = init.FVALUATION) LEFT OUTER JOIN T_META_FORMENUMITEM_L enumL1 ON

(enum1.FENUMID = enumL1.FENUMID AND enumL1.FLOCALEID = 2052) LEFT OUTER JOIN T_BD_UNIT unit ON unit.funitid = init.FUNITID WHERE ((((((((

(

(FEndInitKey = ‘0’ AND FDIMID = @FDIMEENTRYID

) AND FMATERIALID = @FNUMBER

)

AND

init.FYearPeriod = ‘202011’) AND init.FACCTGSYSTEMID = 1) AND init.FACCTGORGID = 1) AND init.FACCTPOLICYID

= 1) AND (init.FYearPeriod >= 202011)) AND (init.FYearPeriod <= 202011)) AND (init.FMATERIALID >= @FNUMBER)

) AND (init.FMATERIALID <= @FNUMBER)

)

UNION

SELECT rt.FDIMID, rt.FACCTGSYSTEMID, rt.FACCTGORGID, rt.FACCTPOLICYID, rt.FYear, rt.FPeriod, rt.FMATERIALID, rt.FMATERIALNAME, rt.FMATERIALGROUP, rt.FMODEL, rt.FLOT,

rt.FASSIPROPERTYID, rt.FMATERTYPE, rt.FBOMNO, rt.FPLANNO, rt.FSEQUENCENO, rt.FPROJECTNO, rt.FOWNERID, rt.FSTOCKORGID, rt.FSTOCKID, rt.FSTOCKPLACEID, rt.FUNITID, rt.FINOUTKEY, rt.FRowType, rt.FBILLTYPEID, rt.FBILLFORMID, rt.FBILLID, rt.FBILLENTRYID, rt.FBILLNO, rt.FOrderSeq, rt.FBILLSEQ, rt.FBILLDATE, FRECEIVEQTY,

FRECEIVEPRICE, FSENDQTY, FSENDPRICE, FENDQTY, FENDPRICE, FRECEIVEAMOUNT, FSENDAMOUNT, FENDAMOUNT, FSTOCKSTATUSID, rang.FNUMBER facctgrangeid, rangl.FNAME facctgrangename, enumL.FCAPTION fmaterproperty, enumL1.FCAPTION fvaluation, unit.FPRECISION fqtydigits

FROM (

——————————-

SELECT ((T1.fyear * 100) + T1.fperiod) fyearperiod,

‘2’ fendinitkey, stockdim.FEntryId fdimid, cald.FACCTSYSTEMID facctgsystemid, cald.FFinOrgID facctgorgid, cald.FACCTPOLICYID facctpolicyid, t1.FYear fyear, t1.FPeriod fperiod, mat.FNUMBER fmaterialid, mat_L.FNAME fmaterialname, MAT.FMATERIALGROUP, mat_L.FSPECIFICATION fmodel, dim.FLOT flot, dim.FAuxPropId fassipropertyid, mat_T.FERPCLSID fmaterproperty, mat_T.FCATEGORYID fmatertype, dim.FBOMId fbomno, dim.FMtoNo fplanno, N’ ‘ fsequenceno, dim.FPROJECTNO fprojectno, dim.FCargoOwnerId fownerid, dim.FStockOrgId fstockorgid, dim.FSTOCKSTATUSID, dim.FStockId fstockid, dim.FStockLocId fstockplaceid, stockdim.FAcctgRangeId facctgrangeid, mat_T.FBASEUNITID funitid, stockdim.FVALUATIONMETHOD fvaluation,

CASE  WHEN t3.FProphaseAdjSign = ‘1’ THEN 2

WHEN t3.FInOutIndex = ‘1’ THEN 4 ELSE 4 END finoutkey,

CASE  WHEN t3.FProphaseAdjSign = ‘1’ THEN 2

WHEN t3.FInOutIndex = ‘1’ THEN 3 ELSE 4 END frowtype,

t3.FBillTypeId fbilltypeid, t3.FBillFromId fbillformid, t3.FBillId fbillid, t3.FBillEntryId fbillentryid, ISNULL(t3.FBillNo, ‘ ‘) fbillno, ISNULL(t3_Seq.FORDETBYSEQ, t3.FSeq) forderseq, t3.FBillSeqId fbillseq, t3.FBillDate fbilldate,

CASE  WHEN t3.FInOutIndex = ‘1’ THEN t3.FQty ELSE 0 END freceiveqty,

CASE  WHEN t3.FInOutIndex = ‘0’ THEN t3.FQty ELSE 0 END fsendqty,

0 fendqty,

CASE  WHEN (t3.FInOutIndex = ‘1’ AND (t3.FINDEXENTRYID <> 32)) THEN t3.FAcctgAmount ELSE 0 END freceiveamount,

CASE  WHEN t3.FInOutIndex = ‘1’ THEN t3.FACCTGPRICE ELSE 0 END freceiveprice,

CASE  WHEN t3.FInOutIndex = ‘0’ THEN t3.FAcctgAmount

WHEN t3.FINDEXENTRYID = 32 THEN (0 – t3.FAcctgAmount) ELSE 0 END fsendamount,

CASE  WHEN t3.FInOutIndex = ‘0’ THEN t3.FACCTGPRICE ELSE 0 END fsendprice,

0 fendamount, 0 fendprice

FROM T_HS_OUTACCTG t1

INNER JOIN T_HS_CALDIMENSIONS cald ON t1.FDIMENSIONID = cald.FDIMENSIONID

INNER JOIN

(

SELECT t2.FID, t2.FDimeentryid, t30.FENTRYID fseqentryid, max(pe.FORDETBYSEQ) fordetbyseq

FROM T_HS_ACCTGPROCESS_H t2

INNER JOIN T_HS_OUTINSTOCKSEQ_H t30 ON (t2.FEntryId = t30.FProcessEntryId AND t30.fdocumentstatus = ‘C’)

LEFT OUTER JOIN T_HS_ACCTGPROCESSENTRY_H pe ON ((pe.fprocessEntryID = t2.FEntryId AND pe.FSEQENTRYID = t30.FEntryId) AND (pe.FCALDATATYPE <> ‘6’))

GROUP BY t2.FID, t2.FDimeentryid, t30.FENTRYID

) t3_seq ON t3_Seq.FID = t1.FID

INNER JOIN T_HS_OUTINSTOCKSEQ_H t3 ON t3_Seq.FSeqEntryID = t3.FENTRYID

INNER JOIN T_HS_InivStockDimension dim ON t3.FDimeEntryId = dim.FEntryId

INNER JOIN T_HS_StockDimension stockdim ON t3_Seq.FDimeentryid = stockdim.FEntryId

INNER JOIN t_bd_Material mat ON dim.FMATERIALID = mat.FMATERIALID

INNER JOIN T_BD_MATERIALBASE mat_T ON (mat.FMATERIALID = mat_T.FMATERIALID AND mat.FUSEORGID = mat_T.FUSEORGID)

LEFT OUTER JOIN t_bd_Material_l mat_L ON ((mat.FMATERIALID = mat_L.FMATERIALID AND mat.FUSEORGID = mat_L.FUSEORGID) AND mat_L.FLOCALEID = 2052) WHERE 1 = 1

———————

) rt

INNER JOIN T_HS_ACCTGRANGE rang ON rt.FACCTGRANGEID = rang.FAcctgRangeId

LEFT OUTER JOIN T_HS_ACCTGRANGE_L rangl ON (rt.FACCTGRANGEID = rangl.FACCTGRANGEID AND rangl.FLOCALEID = 2052)

INNER JOIN T_META_FORMENUMITEM enum ON (

enum.FID = ‘ac14913e-bd72-416d-a50b-2c7432bbff63’ AND

enum.FValue =

rt.FMATERPROPERTY)

LEFT OUTER JOIN T_META_FORMENUMITEM_L enumL ON (enum.FENUMID = enumL.FENUMID AND enumL.FLOCALEID = 2052)

INNER JOIN T_META_FORMENUMITEM enum1 ON (enum1.FID = ‘eca675f6-d296-4ba9-b9df-170b7b286a73’ AND enum1.FValue = rt.FVALUATION)

LEFT OUTER JOIN T_META_FORMENUMITEM_L enumL1 ON (enum1.FENUMID =

enumL1.FENUMID AND enumL1.FLOCALEID = 2052)

LEFT OUTER JOIN T_BD_UNIT unit ON unit.funitid = rt.FUNITID

WHERE (((((((((

(

FDIMID = @FDIMEENTRYID AND

FMATERIALID = @FNUMBER

) AND

(rt.FYearPeriod >= ‘202011’)) AND (rt.FYearPeriod <= ‘202011’)) AND rt.FACCTGSYSTEMID = 1) AND rt.FACCTGORGID = 1) AND rt.FACCTPOLICYID = 1) AND

(rt.FYearPeriod >= 202011)) AND (rt.FYearPeriod <= 202011)) AND (rt.FMATERIALID >= @FNUMBER)

) AND (rt.FMATERIALID <= @FNUMBER)

)

UNION

SELECT @FDIMEENTRYID fdimid, 1 facctgsystemid, 1 facctgorgid, 1 facctpolicyid, t1.fyear, t1.fperiod, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, MAT.FMATERIALGROUP,

mat_L.FSPECIFICATION fmodel, STOCKDIME.FLOT flot, STOCKDIME.FAUXPROPID fassipropertyid, MAT_T.FCATEGORYID fmatertype, STOCKDIME.FBOMID fbomno, STOCKDIME.FMTONO fplanno, N’ ‘ fsequenceno, N’ ‘ fprojectno, STOCKDIME.FCARGOOWNERID fownerid, STOCKDIME.FSTOCKORGID fstockorgid, STOCKDIME.FSTOCKID, STOCKDIME.FSTOCKLOCID fstockplaceid, mat_T.FBASEUNITID funitid, t1.FType, t1.FRowType, t1.FBILLTYPEID, t1.FBILLFORMID, t1.FBILLID, 0 fbillentryid, t1.FBillNo, 9999 forderseq, t1.FBillSeq, t1.FDate fbilldate, 0 freceiveqty, 0 freceiveprice, 0 fsendqty, 0 fsendprice, 0 fendqty, 0 fendprice, t1.FRECEIVEAMOUNT, t1.FSENDAMOUNT, 0 fendamount, t1.FSTOCKSTATUSID, rang.FNUMBER facctgrangeid, rangl.FNAME facctgrangename, enumL.FCAPTION fmaterproperty, N’加权平均法’ fvaluation, unit.FPRECISION fqtydigits  –into #temp001

FROM T_UG_StockInDetailAdjReport t1

INNER JOIN T_HS_InivStockDimension STOCKDIME ON t1.FDIMID = STOCKDIME.FENTRYID

INNER JOIN T_HS_StockDimension DIME ON STOCKDIME.FDIMEENTRYID = DIME.FENTRYID

INNER JOIN t_bd_Material MAT ON (STOCKDIME.FMATERIALID = MAT.FMATERIALID AND STOCKDIME.FSTOCKORGID = mat.FUSEORGID)

INNER JOIN T_BD_MATERIALBASE MAT_T ON (MAT.FMATERIALID = mat_T.FMATERIALID AND mat.FUSEORGID = mat_T.FUSEORGID)

LEFT OUTER JOIN t_bd_Material_l MAT_L ON ((MAT.FMATERIALID = MAT_L.FMATERIALID AND mat.FUSEORGID = mat_L.FUSEORGID) AND MAT_L.FLOCALEID = 2052)

INNER JOIN T_HS_ACCTGRANGE rang ON DIME.FACCTGRANGEID = rang.FACCTGRANGEID

LEFT OUTER JOIN T_HS_ACCTGRANGE_L rangl ON (rang.FACCTGRANGEID = rangl.FACCTGRANGEID AND rangl.FLOCALEID = 2052)

INNER JOIN T_META_FORMENUMITEM enum ON (enum.FID = ‘ac14913e-bd72-416d-a50b-2c7432bbff63’ AND enum.FValue = mat_T.FERPCLSID)

LEFT OUTER JOIN T_META_FORMENUMITEM_L enumL ON (enum.FENUMID = enumL.FENUMID AND enumL.FLOCALEID = 2052)

LEFT OUTER JOIN T_BD_UNIT unit ON MAT_T.FBASEUNITID = unit.FUNITID

) t

LEFT OUTER JOIN T_BD_LOTMASTER lot ON lot.flotid = t.flot

WHERE 1 = 1

MERGE INTO t_UG_StockInDetailRet t using(SELECT t1.Fyear, t1.Fperiod, t1.FDIMID, t1.FMATERIALID, t1.FBILLNO, t1.FBILLENTRYID, t1.FBILLSEQ, t1.FINOUTKEY, t1.FRowType, sum((ISNULL(t1.FENDQTY, 0) + ISNULL(t1.FRECEIVEQTY, 0)) – ISNULL(t1.FSENDQTY, 0)) fendqty, sum((ISNULL(t1.FENDAMOUNT, 0) + ISNULL(t1.FRECEIVEAMOUNT, 0)) – ISNULL(t1.FSENDAMOUNT, 0)) fendamount FROM t_UG_StockInDetailRet t1 GROUP BY t1.Fyear, t1.Fperiod, t1.FDIMID, t1.FMATERIALID, t1.FBILLNO, t1.FBILLENTRYID, t1.FINOUTKEY, t1.FRowType, t1.FBILLSEQ) t2 ON (((((((((t.FDIMID = t2.FDIMID AND t.FMATERIALID = t2.FMATERIALID) AND t.FBILLNO = t2.FBILLNO) AND t.FBILLENTRYID = t2.FBILLENTRYID) AND t.FBILLSEQ = t2.FBILLSEQ) AND t.Fperiod = t2.Fperiod) AND t.Fyear = t2.Fyear) AND t.FRowType = t2.FRowType) AND t.FRowType IN (2, 3, 4, 6, 5))) WHEN MATCHED  THEN UPDATE SET t.FENDQTY = t2.FENDQTY, t.FENDAMOUNT = t2.FENDAMOUNT;

MERGE INTO t_UG_StockInDetailRet t using(SELECT b.FNumber, sum(a.FENDQTY) fendqty, CAST(sum(a.FENDAMOUNT) AS NUMERIC(23, 10)) fendamount FROM t_UG_StockInDetailRet a INNER JOIN t_UG_StockInDetailRet b ON (a.FNUMBER <= b.FNUMBER) GROUP BY b.FNumber) m ON (t.FNUMBER = m.FNUMBER) WHEN MATCHED THEN UPDATE SET t.FENDQTY = m.FENDQTY, t.FENDAMOUNT = m.FENDAMOUNT, t.FENDPRICE = CASE m.FENDQTY WHEN 0 THEN 0 ELSE (m.FENDAMOUNT / m.FENDQTY) END;

INSERT INTO t_UG_StockInDetailRet with(tablock)  (FYear, FPeriod, FISTOTAL, FDIMID, FINOUTKEY, FRowType, FRECEIVEQTY, FRECEIVEAMOUNT, FSENDQTY, FSENDAMOUNT, FRECEIVEPRICE, FSENDPRICE, FQtyDigits) SELECT FYear, FPeriod, 1 fistotal, FDIMID, FINOUTKEY, FRowType, AllReceiveQty, AllReceiveAmt, AllSendQty, AllSendAmt, CASE  WHEN AllReceiveQty = 0 THEN 0 ELSE ROUND((AllReceiveAmt / AllReceiveQty), 10) END allreceiveprice, CASE  WHEN AllSendQty = 0 THEN 0 ELSE ROUND((AllSendAmt / AllSendQty), 10) END allsendprice, FQtyDigits FROM (SELECT FYear, FPeriod, FDIMID, 7 finoutkey, 8 frowtype, sum(FRECEIVEQTY) allreceiveqty, CAST(sum(FRECEIVEAMOUNT) AS NUMERIC(23, 10)) allreceiveamt, sum(FSENDQTY) allsendqty, CAST(sum(FSENDAMOUNT) AS NUMERIC(23, 10)) allsendamt, Max(FQtyDIgits) fqtydigits FROM t_UG_StockInDetailRet WHERE FINOUTKEY IN (2, 3, 4, 6, 5) GROUP BY FDIMID, FBILLNO, FYear, FPeriod) t

INSERT INTO t_UG_StockInDetailRet with(tablock)  (FISTOTAL, FDIMID, FINOUTKEY, FRowType, FRECEIVEQTY, FRECEIVEAMOUNT, FSENDQTY, FSENDAMOUNT, FRECEIVEPRICE, FSENDPRICE, FQtyDigits) SELECT 2 fistotal, FDIMID, FINOUTKEY, FRowType, AllReceiveQty, AllReceiveAmt, AllSendQty, AllSendAmt, CASE  WHEN AllReceiveQty = 0 THEN 0 ELSE ROUND((AllReceiveAmt / AllReceiveQty), 10) END allreceiveprice, CASE  WHEN AllSendQty = 0 THEN 0 ELSE ROUND((AllSendAmt / AllSendQty), 10) END allsendprice, FQtyDIgits FROM (SELECT FDIMID, 9 finoutkey, 10 frowtype, sum(FRECEIVEQTY) allreceiveqty, CAST(sum(FRECEIVEAMOUNT) AS NUMERIC(23, 10)) allreceiveamt, sum(FSENDQTY) allsendqty, CAST(sum(FSENDAMOUNT) AS NUMERIC(23, 10)) allsendamt, max(FQtyDIgits) fqtydigits FROM t_UG_StockInDetailRet WHERE FRowType = 8 GROUP BY FDIMID) t

INSERT INTO t_UG_StockInDetailRet with(tablock)  (FYear, FPeriod, FISTOTAL, FDIMID, FBILLNO, FINOUTKEY, FRowType, FORDERSEQ, FRECEIVEAMOUNT, FSENDAMOUNT, FReceiveQty, FSendQty, FReceivePrice, FSendPrice, FQtyDigits) SELECT FYear, FPeriod, 2 fistotal, FDIMID, N’ ‘ fbillno, FINOUTKEY, FRowTYpe, 10000, AllReceiveAmt, AllSendAmt, FReceiveQty, FSendQty, CASE  WHEN FReceiveQty = 0 THEN 0 ELSE ROUND((AllReceiveAmt / FReceiveQty), 10) END freceiveprice, CASE  WHEN FSendQty = 0 THEN 0 ELSE ROUND((AllSendAmt / FSendQty), 10) END fsendprice, FQtyDigits FROM (SELECT FYear, FPeriod, FDIMID, 6 finoutkey, 7 frowtype, CAST(sum(FRECEIVEAMOUNT) AS NUMERIC(23, 10)) allreceiveamt, CAST(sum(FSENDAMOUNT) AS NUMERIC(23, 10)) allsendamt, sum(FReceiveQty) freceiveqty, sum(FSendQty) fsendqty, max(FQtyDIgits) fqtydigits FROM t_UG_StockInDetailRet WHERE FRowTYpe = 8 GROUP BY FYear, FPeriod, FDIMID) t

INSERT INTO t_UG_StockInDetailRet with(tablock)  (FYear, FISTOTAL, FDIMID, FINOUTKEY, FRowType, FRECEIVEQTY, FRECEIVEAMOUNT, FSENDQTY, FSENDAMOUNT, FRECEIVEPRICE, FSENDPRICE, FQtyDigits) SELECT FYear, 2 fistotal, FDIMID, FINOUTKEY, FRowType, AllReceiveQty, AllReceiveAmt, AllSendQty, AllSendAmt, CASE  WHEN AllReceiveQty = 0 THEN 0 ELSE ROUND((AllReceiveAmt / AllReceiveQty), 10) END allreceiveprice, CASE  WHEN AllSendQty = 0 THEN 0 ELSE ROUND((AllSendAmt / AllSendQty), 10) END allsendprice, FQtyDIgits FROM (SELECT FYear, FDIMID, 11 finoutkey, 12 frowtype, sum(FRECEIVEQTY) allreceiveqty, CAST(sum(FRECEIVEAMOUNT) AS NUMERIC(23, 10)) allreceiveamt, sum(FSENDQTY) allsendqty, CAST(sum(FSENDAMOUNT) AS NUMERIC(23, 10)) allsendamt, max(FQtyDIgits) fqtydigits FROM t_UG_StockInDetailRet WHERE FRowType = 8 GROUP BY FYear, FDIMID) t

INSERT INTO t_UG_StockInDetailRet with(tablock)  (FISTOTAL, FDIMID, FMATERIALID, FMATERIALNAME, FMODEL, FINOUTKEY, FRowType, FENDQTY, FENDAMOUNT, FENDPRICE, FQtyDigits) SELECT 3 fistotal, FDIMID, FMATERIALID, FMATERIALNAME, FMODEL, 8 finoutkey, 9 frowtype, FENDQTY, FENDAMOUNT, CASE  WHEN (FENDQTY = 0 OR FENDAMOUNT = 0) THEN 0 ELSE (FENDAMOUNT / FENDQTY) END fendprice, FQtyDigits FROM (SELECT FDIMID, max(FMATERIALID) fmaterialid, max(FMATERIALNAME) fmaterialname, max(FMODEL) fmodel, ((sum(ISNULL(FENDQTY, 0)) + sum(FRECEIVEQTY)) – sum(FSENDQTY)) fendqty, ((CAST(sum(ISNULL(FENDAMOUNT, 0)) AS NUMERIC(23, 10)) + CAST(sum(FRECEIVEAMOUNT) AS NUMERIC(23, 10))) – CAST(sum(FSENDAMOUNT) AS NUMERIC(23, 10))) fendamount, max(FQtyDigits) fqtydigits FROM (SELECT FDIMID, FMATERIALID, FMATERIALNAME, FMODEL, 0 freceiveqty, 0 freceiveamount, 0 fsendqty, 0 fsendamount, FENDQTY, FENDAMOUNT, FQtyDigits FROM t_UG_StockInDetailRet WHERE FRowType = 1 UNION SELECT FDIMID, FMATERIALID, FMATERIALNAME, FMODEL, FRECEIVEQTY, FRECEIVEAMOUNT, FSENDQTY, FSENDAMOUNT, FENDQTY, FENDAMOUNT, FQtyDigits FROM t_UG_StockInDetailRet WHERE FRowType = 10) tmp GROUP BY FDIMID) t

INSERT INTO t_UG_StockInDetailRet with(tablock)  (FYear, FISTOTAL, FDIMID, FBillNo, FINOUTKEY, FRowType, FEXPENSEID, FEXPENSENAME, FRECEIVEQTY, FRECEIVEAMOUNT, FSENDQTY, FSENDAMOUNT, FRECEIVEPRICE, FSENDPRICE, FENDQTY, FENDAMOUNT, FENDPRICE, FQtyDigits) SELECT FYear, 2 fistotal, FDIMID, N’ ‘ fbillno, 10 finoutkey, 11 frowtype, N’ ‘ fexpenseid, N’小计’ fexpensename, FRECEIVEQTY, FRECEIVEAMOUNT, FSENDQTY, FSENDAMOUNT, CASE  WHEN (FRECEIVEQTY = 0 OR FRECEIVEAMOUNT = 0) THEN 0 ELSE (FRECEIVEAMOUNT / FRECEIVEQTY) END freceiveprice, CASE  WHEN (FSENDQTY = 0 OR FSENDAMOUNT = 0) THEN 0 ELSE (FSENDAMOUNT / FSENDQTY) END fsendprice, FENDQTY, FENDAMOUNT, 0 fendprice, FQtyDigits FROM (SELECT FYear, FDIMID, sum(ISNULL(FRECEIVEQTY, 0)) freceiveqty, CAST(sum(ISNULL(FRECEIVEAMOUNT, 0)) AS NUMERIC(23, 10)) freceiveamount, sum(ISNULL(FSENDQTY, 0)) fsendqty, CAST(sum(ISNULL(FSENDAMOUNT, 0)) AS NUMERIC(23, 10)) fsendamount, 0 fendqty, 0 fendamount, max(FQtyDIgits) fqtydigits FROM t_UG_StockInDetailRet WHERE FRowType IN (1, 12) GROUP BY FYear, FDIMID) t

UPDATE T0 SET t0.FENDAMOUNT = t1.FENDAMOUNT, t0.FENDPRICE = t1.FENDPRICE, t0.FENDQTY = t1.FENDQTY FROM t_UG_StockInDetailRet T0, t_UG_StockInDetailRet t1 WHERE t1.frowtype = 9 AND (t0.frowtype = 11 AND t0.FYear = 2020)

UPDATE t_UG_StockInDetailRet SET FReceiveAmount = NULL, FReceiveQty = NULL, FSendAmount = NULL, FSendQty = NULL, FReceivePrice = NULL, FSendPrice = NULL WHERE frowtype = 1

MERGE INTO t_UG_StockInDetailRet t0 using(SELECT T.FID, CASE  WHEN ISNULL(T.FShowName, N’ ‘) = N’ ‘ THEN CONVERT(VARCHAR(8000), N”) ELSE LEFT(T.FShowName, (LEN(T.FShowName) – 1)) END fshowname FROM (SELECT t0.FID fid, (((CASE  WHEN ISNULL(st01_L.FNAME, N’ ‘) = ‘ ‘ THEN CONVERT(VARCHAR(8000), N”) ELSE (CONVERT(NVARCHAR(255), st01_L.FNAME) + CONVERT(VARCHAR(8000), ‘;’)) END + CASE  WHEN ISNULL(st02_L.FNAME, N’ ‘) = ‘ ‘ THEN CONVERT(VARCHAR(8000), N”) ELSE (CONVERT(NVARCHAR(255), st02_L.FNAME) + CONVERT(VARCHAR(8000), ‘;’)) END) + CASE  WHEN ISNULL(t0.FOPCODE, N’ ‘) = ‘ ‘ THEN CONVERT(VARCHAR(8000), N”) ELSE (CONVERT(NVARCHAR(255), t0.FOPCODE) + CONVERT(VARCHAR(8000), ‘;’)) END) + CONVERT(VARCHAR(8000), N”)) fshowname FROM T_BAS_FLEXVALUESDETAIL t0 LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st01_L ON (t0.FF100004 = st01_L.FEntryID AND st01_L.FLocaleId = 2052) LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st02_L ON (t0.FF100016 = st02_L.FEntryID AND st02_L.FLocaleId = 2052)) t) t1 ON (t1.FID = t0.FSTOCKPLACEID) WHEN MATCHED  THEN UPDATE SET t0.FSTOCKPLACENAME = CASE  WHEN (LEN(t1.FShowName) >= 255) THEN (LEFT(t1.FShowName, 252) + ‘…’) ELSE t1.FShowName END;

insert INTO TMPFAA6B8D1861C11EB8585F67B9D01

SELECT (CONVERT(VARCHAR(8000), FYEAR) + CASE  WHEN FPeriod = 99 THEN ‘ ‘ ELSE (‘.’ + CONVERT(VARCHAR(8000), FPeriod)) END) fperiod, FMATERIALID, FMATERIALNAME, FMATERIALGROUP, FMODEL, FLOTNO, FASSIPROPERTYID, FMATERPROPERTY, FMATERTYPE, FBOMNO, FPLANNO, FSEQUENCENO, FPROJECTNO, FOWNERID, FOWNERNAME, FSTOCKORGID, FSTOCKORGNAME, FSTOCKID, FSTOCKNAME, FSTOCKPLACEID, FSTOCKPLACENAME, FACCTGRANGEID, FACCTGRANGENAME, FBUSINESSTYPE, CONVERT(CHAR(10), FBILLDATE, 20) fbilldate, FBILLNO, FBillSeq, FRECEIVEQTY, FRECEIVEPRICE, FSENDQTY, FSENDPRICE, FENDQTY, FENDPRICE, FRECEIVEAMOUNT, FSENDAMOUNT, FENDAMOUNT, FBILLID, FBILLENTRYID,

FBILLFORMID, FDIMID, FASSIPROPNAME, FSTOCKSTATUSID, 2 fdigits, 6 fpricedigits, FQtyDigits, FInOutKey, FRowType, ROW_NUMBER() OVER( ORDER BY T.FYEAR ASC, T.FPERIOD ASC, T.FINOUTKEY ASC, T.FORDERSEQ ASC, T.FBILLDATE ASC, T.FBILLNO ASC, T.FBILLSEQ ASC, T.FISTOTAL DESC) fidentityid

FROM (SELECT FISTOTAL, FDIMID, m.FACCTGSYSTEMID, m.FACCTGORGID, m.FACCTPOLICYID, CASE  WHEN (FRowType = 9 OR FRowType = 10) THEN 2020 ELSE m.FYear END fyear, CASE  WHEN (FRowType = 9 OR FRowType = 10) THEN 12 WHEN FRowType = 11 THEN 99 ELSE m.FPeriod END fperiod, m.FMATERIALID, m.FMATERIALNAME, GROUPL.FNAME

fmaterialgroup, m.FMODEL, m.FLOTNO, m.FASSIPROPERTYID, m.FMATERPROPERTY, mt.FNAME fmatertype, bom.FNumber fbomno, FPLANNO, FSEQUENCENO, FPROJECTNO, m.FOWNERID, m.FSTOCKORGID, m.FSTOCKID, m.FSTOCKPLACEID, m.FACCTGRANGEID, unit.FNAME funitid, m.FVALUATION, FInOutKey, FRowType, CASE  WHEN FRowType = 1 THEN

N’期初结存’ WHEN FRowType = 2 THEN N’期初调整’ WHEN FRowType = 6 THEN N’期末调整’ WHEN FRowType = 8 THEN N’按单本期合计’ WHEN FRowType = 10 THEN N’合计’ WHEN FRowType = 9 THEN N’期末结存’ WHEN FRowType = 11 THEN N’本年累计’ WHEN FRowType = 5 THEN N’成本调整’ WHEN FRowType = 7 THEN N’本期小计’ ELSE bt.FNAME END fbusinesstype, m.FBILLFORMID, m.FBILLID, m.FBILLENTRYID, m.FBILLNO, m.FOrderSeq, m.FBILLSEQ fbillseq, FBILLDATE, FRECEIVEQTY, FRECEIVEPRICE, FSENDQTY, FSENDPRICE, FENDQTY, FENDPRICE, FRECEIVEAMOUNT, FSENDAMOUNT, FENDAMOUNT, asys.FNAME facctgsystemname, aorg.FNAME facctgorgname, sorg.FNAME fstockorgname, own.FNAME fownername, stock.FNAME fstockname, m.FSTOCKPLACENAME, m.FACCTGRANGENAME, unit.FNAME funitname, FASSIPROPNAME, sts.FNAME fstockstatusid, FQtyDigits FROM

t_UG_StockInDetailRet m

LEFT OUTER JOIN T_ORG_ACCOUNTSYSTEM_L asys ON (m.FACCTGSYSTEMID = asys.FACCTSYSTEMID AND asys.FLOCALEID = 2052)

LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L aorg ON (m.FACCTGORGID = aorg.FORGID AND aorg.FLOCALEID = 2052)

LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L sorg ON (m.FSTOCKORGID = sorg.FORGID AND sorg.FLOCALEID = 2052)

LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L own ON (m.FOWNERID = own.FORGID AND own.FLOCALEID = 2052)

LEFT OUTER JOIN t_BD_Stock_L stock ON (m.FSTOCKID = stock.FSTOCKID AND stock.FLOCALEID = 2052)

LEFT OUTER JOIN T_BD_UNIT_L unit ON (m.FUNITID = unit.FUNITID AND unit.FLOCALEID = 2052)

LEFT OUTER JOIN T_BAS_BILLTYPE_L bt ON (m.FBILLTYPEID = bt.FBILLTYPEID AND bt.FLOCALEID = 2052)

LEFT OUTER JOIN T_BD_MATERIALCATEGORY_L mt ON (m.FMATERTYPE = mt.FCATEGORYID AND mt.FLOCALEID = 2052)

LEFT OUTER JOIN t_eng_bom bom ON bom.FID = m.FBOMNO

LEFT OUTER JOIN T_BD_STOCKSTATUS_L sts ON (m.FSTOCKSTATUSID = sts.FSTOCKSTATUSID AND sts.FLOCALEID = 2052)

LEFT OUTER JOIN T_BD_MATERIALGROUP_L GROUPL ON (m.FMATERIALGROUPID = GROUPL.FID AND GROUPL.FLOCALEID = 2052)) t WHERE ((((((FRowType <> 8) AND (FRowType <> 13)) AND (FRowType <> 12)) AND (FRowType <> 10)) AND (FRowType <> 11)) AND (FRowType <> 7))

delete from t_UG_StockInDetailRet

delete from T_UG_StockInDetailAdjReport

END

FETCH NEXT FROM orderNum_03_cursor INTO @FDIMEENTRYID,@FNUMBER   –移动游标

END

END

–关闭游标

CLOSE orderNum_03_cursor

–释放游标

DEALLOCATE orderNum_03_cursor

–EXEC sp_helptext ‘getCHMX’;

–EXEC getCHMX;