— =============================================
— 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;