K3 Cloud存货收发汇总表

SELECT STOCKDIM.FENTRYID fdimid, HSDIM.FENTRYID facctgdimid, CALD.FACCTSYSTEMID facctgsystemid, CALD.FFINORGID facctgorgid, 
CALD.FACCTPOLICYID facctpolicyid, 2019, 2, STOCKDIM.FMATERIALID fmaterialbaseid, MAT.FNUMBER fmaterialid, 
MAT_L.FNAME fmaterialname, MAT_L.FSPECIFICATION fmodel, LOT.FNUMBER flotno, STOCKDIM.FAUXPROPID fassipropertyid, 
MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, STOCKDIM.FBOMID fbomno, STOCKDIM.FMTONO fplanno, N'' fsequenceno, 
STOCKDIM.FPROJECTNO fprojectno, STOCKDIM.FCargoOwnerId fownerid, STOCKDIM.FStockOrgId fstockorgid, STOCKDIM.FSTOCKSTATUSID fstockstatusid, 
STOCKDIM.FSTOCKID fstockid, STOCKDIM.FSTOCKLOCID fstockplaceid, HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, 
HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 FROM T_HS_OUTACCTG AG INNER JOIN T_HS_InivBalance_H IV ON AG.FID = IV.FID 
INNER JOIN T_HS_CALDIMENSIONS CALD ON (AG.FDIMENSIONID = CALD.FDIMENSIONID AND AG.FDIMENSIONID = 1) 
INNER JOIN T_HS_InivStockDimension STOCKDIM ON IV.FDimeEntryId = STOCKDIM.FENTRYID 
LEFT OUTER JOIN T_BD_LOTMASTER LOT ON STOCKDIM.FLOT = LOT.FLOTID 
INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = IV.FACCTGDIMEENTRYID 
INNER JOIN t_bd_Material MAT ON STOCKDIM.FMATERIALID = MAT.FMATERIALID 
INNER JOIN T_BD_MATERIALBASE MAT_T ON MAT.FMATERIALID = MAT_T.FMATERIALID 
LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = STOCKDIM.FSTOCKID 
LEFT OUTER JOIN t_bd_Material_l MAT_L ON (MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052) WHERE 
(((((((((IV.FENDINITKEY = '0' AND AG.FYEAR = 2019) AND AG.FPERIOD = 2) AND CAlD.FACCTSYSTEMID = 1) AND CAlD.FFINORGID = 100401) AND 
CAlD.FACCTPOLICYID = 1) AND AG.FYEAR = 2019) AND AG.FPERIOD = 2) AND (MAT.FNUMBER >= '101010001-0014')) AND (MAT.FNUMBER <= '101010001-0014'))

UNION SELECT STOCKDIM.FENTRYID fdimid, HSDIM.FENTRYID facctgdimid, CALD.FACCTSYSTEMID facctgsystemid, CALD.FFINORGID facctgorgid, 
CALD.FACCTPOLICYID facctpolicyid, 2019, 2, STOCKDIM.FMATERIALID fmaterialbaseid, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, 
MAT_L.FSPECIFICATION fmodel, LOT.FNUMBER flotno, STOCKDIM.FAUXPROPID fassipropertyid, MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, 
STOCKDIM.FBOMID fbomno, STOCKDIM.FMTONO fplanno, N'' fsequenceno, STOCKDIM.FPROJECTNO fprojectno, STOCKDIM.FCargoOwnerId fownerid, 
STOCKDIM.FStockOrgId fstockorgid, STOCKDIM.FSTOCKSTATUSID fstockstatusid, STOCKDIM.FSTOCKID fstockid, STOCKDIM.FSTOCKLOCID fstockplaceid, 
HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 FROM T_HS_OUTACCTG AG 
INNER JOIN T_HS_CALDIMENSIONS CALD ON (AG.FDIMENSIONID = CALD.FDIMENSIONID AND AG.FDIMENSIONID = 1) 
INNER JOIN T_HS_OutInStockSeq_H SEQ ON (AG.FID = SEQ.FACCTGID AND SEQ.FDOCUMENTSTATUS = 'C') 
INNER JOIN T_HS_InivStockDimension STOCKDIM ON SEQ.FDIMEENTRYID = STOCKDIM.FENTRYID 
LEFT OUTER JOIN T_BD_LOTMASTER LOT ON STOCKDIM.FLOT = LOT.FLOTID INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = SEQ.FACCTGDIMEENTRYID 
INNER JOIN t_bd_Material MAT ON STOCKDIM.FMATERIALID = MAT.FMATERIALID 
INNER JOIN T_BD_MATERIALBASE MAT_T ON MAT.FMATERIALID = MAT_T.FMATERIALID 
LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = STOCKDIM.FSTOCKID 
LEFT OUTER JOIN t_bd_Material_l MAT_L ON (MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052) 
WHERE (((((((1 = 1 AND CAlD.FACCTSYSTEMID = 1) AND CAlD.FFINORGID = 100401) AND CAlD.FACCTPOLICYID = 1) AND AG.FYEAR = 2019) AND AG.FPERIOD = 2) 
AND (MAT.FNUMBER >= '101010001-0014')) AND (MAT.FNUMBER <= '101010001-0014')) 

UNION SELECT STOCKDIM.FENTRYID fdimid, HSDIM.FENTRYID facctgdimid, CALD.FACCTGSYSTEMID facctgsystemid, CALD.FACCTORGID facctgorgid, 
CALD.FACCTPOLICYID facctpolicyid, 2019, 2, STOCKDIM.FMATERIALID fmaterialbaseid, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, 
MAT_L.FSPECIFICATION fmodel, LOT.FNUMBER flotno, STOCKDIM.FAUXPROPID fassipropertyid, MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, 
STOCKDIM.FBOMID fbomno, STOCKDIM.FMTONO fplanno, N'' fsequenceno, STOCKDIM.FPROJECTNO fprojectno, STOCKDIM.FCargoOwnerId fownerid, 
STOCKDIM.FStockOrgId fstockorgid, STOCKDIM.FSTOCKSTATUSID fstockstatusid, STOCKDIM.FSTOCKID fstockid, STOCKDIM.FSTOCKLOCID fstockplaceid, 
HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 FROM T_HS_AdjustmentBill CALD 
INNER JOIN T_HS_AdjustmentBillEntry ADJENTRY ON CALD.FID = ADJENTRY.FID 
INNER JOIN T_HS_InivStockDimension STOCKDIM ON ADJENTRY.FDIMEENTRYID = STOCKDIM.FENTRYID 
LEFT OUTER JOIN T_BD_LOTMASTER LOT ON STOCKDIM.FLOT = LOT.FLOTID 
INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = ADJENTRY.FACCTGDIMEENTRYID 
INNER JOIN t_bd_Material MAT ON STOCKDIM.FMATERIALID = MAT.FMATERIALID INNER JOIN T_BD_MATERIALBASE MAT_T ON MAT.FMATERIALID = MAT_T.FMATERIALID 
INNER JOIN T_BD_ACCOUNTPERIOD AG ON (CALD.FDATE BETWEEN AG.FPERIODSTARTDATE AND AG.FPERIODENDDATE) 
LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = STOCKDIM.FSTOCKID 
LEFT OUTER JOIN t_bd_Material_l MAT_L ON (MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052) 
WHERE ((((((((((CALD.FBUSINESSTYPE = '0' OR CALD.FISACCTGGENERATE = '1') AND CALD.FDOCUMENTSTATUS = 'C') AND CALD.FFORBIDSTATUS = 'A') 
AND CAlD.FACCTGSYSTEMID = 1) AND CAlD.FACCTORGID = 100401) AND CAlD.FACCTPOLICYID = 1) AND AG.FYEAR = 2019) AND AG.FPERIOD = 2) AND 
(MAT.FNUMBER >= '101010001-0014')) AND (MAT.FNUMBER <= '101010001-0014'))