EAS8.6 存货收发存计算期初数SQL

业务场景： 某粮食企业需要开发穿透报表。用户版本为EAS8.6.1，计算期初库存SQL。 为了解决期初有库存，但是本期无收发的产品，以及期初无库存，但是有收发的产品，所以后面加0的截止日期计算，本期只作为取数逻辑后期需要优化成为存错过程。

语句如下： /dialect/ SELECT “KCQT”.“hy” “hy”, “KCQT”.“Orgname” “lzname”, “KCQT”.“orgnumber” “lznumber”, “KCQT”.“gsname” “gsname”, “KCQT”.“gsnumber” “gsnumber”, “KCQT”.“number” “WLnumber”, “KCQT”.“name” “WLname”, “KCQT”.“Baseunit” “JLDW”, “KCQT”.“kf” “kf”, sum(“KCQT”.“rkqty”)- sum(“KCQT”.“rckqty”) “kcbaseqty”, sum(“KCQT”.“rkamount”)- sum(“KCQT”.“ckamount”) “kcamount”

from ( SELECT —期初库存 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, “QCKCEntry”.fbaseqty “rkqty”, “QCKCEntry”.FAmount “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_InventoryInitEntry “QCKCEntry”

left join T_IM_InventoryInitBill “QCKC” on “QCKCEntry”.FParentID=“QCKC”.fid

LEFT JOIN T_BD_Material “MATERIAL” ON “QCKCEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “QCKC”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “QCKC”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_DB_WAREHOUSE “KF” on “QCKCEntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid = “ADMINORGUNITA”.FINDUSTRYID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“QCKCEntry”.FBaseUnitID

where “QCKC”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

   union all 

SELECT --采购入库

        "hy".fname_l2                               "hy",
        "ADMINORGUNIT".Fname_l2                    "Orgname",
        "ADMINORGUNIT".Fnumber                     "orgnumber",
        "ADMINORGUNITA".Fname_l2                    "gsname",
        "ADMINORGUNITA".Fnumber                     "gsnumber",
        "MATERIAL".Fnumber                         "number",
        "MATERIAL".Fname_l2                        "name",
        "JBJLDW".Fname_l2                          "Baseunit",
         "KF".fname_l2                             "kf",
        "CGRKEntry".fbaseqty                       "rkqty",
		"CGRKEntry".FActualCost                    "rkamount",
        0                                          "ckqty",
		0                                          "ckamount"

FROM T_IM_PurInWarehsEntry “CGRKEntry”

LEFT JOIN T_IM_PurInWarehsBill “CGRK” ON “CGRK”.FID=“CGRKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “CGRKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “CGRK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “CGRK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“CGRKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “CGRKEntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char(“CGRK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char(“CGRK”.FBizDate,‘yyyy-mm-dd’)< ‘@fromdate’ and “CGRK”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT --生产入库

        "hy".fname_l2                               "hy",
        "ADMINORGUNIT".Fname_l2                    "Orgname",
        "ADMINORGUNIT".Fnumber                     "orgnumber",
        "ADMINORGUNITA".Fname_l2                    "gsname",
        "ADMINORGUNITA".Fnumber                     "gsnumber",
        "MATERIAL".Fnumber                         "number",
        "MATERIAL".Fname_l2                        "name",
        "JBJLDW".Fname_l2                          "Baseunit",
         "KF".fname_l2                             "kf",
        "CGRKEntry".fbaseqty                       "rkqty",
		"CGRKEntry".FActualCost                    "rkamount",
        0                                          "ckqty",
		0                                          "ckamount"

FROM T_IM_ManufactureRecBillEntry “CGRKEntry”

LEFT JOIN T_IM_ManufactureRecBill “CGRK” ON “CGRK”.FID=“CGRKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “CGRKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “CGRK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “CGRK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“CGRKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “CGRKEntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char(“CGRK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char(“CGRK”.FBizDate,‘yyyy-mm-dd’)< ‘@fromdate’ and “CGRK”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT --其他入库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, “QTREntry”.fbaseqty “rkqty”, “QTREntry”.FActualCost “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_OtherInWarehsBillEntry “QTREntry”

LEFT JOIN T_IM_OtherInWarehsBill “QTR” ON “QTR”.FID=“QTREntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “QTREntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “QTR”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “QTR”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“QTREntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “QTREntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “QTR”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “QTR”.FBizDate,‘yyyy-mm-dd’)< ‘@fromdate’ and “QTR”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT --调拨入库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, “DBREntry”.fbaseqty “rkqty”, “DBREntry”.FActualCost “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_MoveInWarehsBillEntry “DBREntry”

LEFT JOIN T_IM_MoveInWarehsBill “DBR” ON “DBR”.FID=“DBREntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “DBREntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “DBR”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “DBR”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“DBREntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “DBREntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “DBR”.FBizDate,‘yyyy-mm-dd’)>= ‘2020-01-01’ and to_char( “DBR”.FBizDate,‘yyyy-mm-dd’)< ‘@fromdate’ and “DBR”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

   union all

SELECT —累计销售出库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, “XSCKEntry”.fbaseqty “ckqty”, “XSCKEntry”.FActualCost “ckamount”

FROM T_IM_SaleIssueEntry “XSCKEntry”

LEFT JOIN T_IM_SaleIssueBill “XSCK” ON “XSCK”.FID=“XSCKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “XSCKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “XSCK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “XSCK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“XSCKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “XSCKEntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “XSCK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “XSCK”.FBizDate,‘yyyy-mm-dd’)< ‘@fromdate’ and “XSCK”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

	union all

SELECT —累计其他出库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, “QTCKEntry”.fbaseqty “ckqty”, “QTCKEntry”.FActualCost “ckamount”

FROM T_IM_OtherIssueBillEntry “QTCKEntry”

LEFT JOIN T_IM_OtherIssueBill “QTCK” ON “QTCK”.FID=“QTCKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “QTCKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “QTCK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “QTCK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“QTCKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “QTCKEntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “QTCK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “QTCK”.FBizDate,‘yyyy-mm-dd’)< ‘@fromdate’ and “QTCK”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT —累计调拨出库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, “DBCKEntry”.fbaseqty “ckqty”, “DBCKEntry”.FActualCost “ckamount”

FROM T_IM_MoveIssueBillEntry “DBCKEntry”

LEFT JOIN T_IM_MoveIssueBill “DBCK” ON “DBCK”.FID=“DBCKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “DBCKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “DBCK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “DBCK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“DBCKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “DBCKEntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “DBCK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “DBCK”.FBizDate,‘yyyy-mm-dd’)< ‘@fromdate’ and “DBCK”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT —累计领料出库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, “DBCKEntry”.fbaseqty “ckqty”, “DBCKEntry”.FActualCost “ckamount”

FROM T_IM_MaterialReqBillEntry “DBCKEntry”

LEFT JOIN T_IM_MaterialReqBill “DBCK” ON “DBCK”.FID=“DBCKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “DBCKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “DBCK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “DBCK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“DBCKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “DBCKEntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “DBCK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “DBCK”.FBizDate,‘yyyy-mm-dd’)< ‘@fromdate’ and “DBCK”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_PurInWarehsEntry “CGRKEntry”

LEFT JOIN T_IM_PurInWarehsBill “CGRK” ON “CGRK”.FID=“CGRKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “CGRKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “CGRK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “CGRK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“CGRKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “CGRKEntry”.FWarehouseID=“KF”.Fid

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char(“CGRK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char(“CGRK”.FBizDate,‘yyyy-mm-dd’)<= ‘@todate’ and “CGRK”.FBasestatus=4 “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_OtherInWarehsBillEntry “QTREntry”

LEFT JOIN T_IM_OtherInWarehsBill “QTR” ON “QTR”.FID=“QTREntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “QTREntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “QTR”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“QTREntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “QTREntry”.FWarehouseID=“KF”.Fid

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “QTR”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_Industry “hy” on “hy”.fid = “ADMINORGUNITA”.FINDUSTRYID where to_char( “QTR”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “QTR”.FBizDate,‘yyyy-mm-dd’)<= ‘@todate’ and “QTR”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT --调拨入库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_MoveInWarehsBillEntry “DBREntry”

LEFT JOIN T_IM_MoveInWarehsBill “DBR” ON “DBR”.FID=“DBREntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “DBREntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “DBR”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“DBREntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “DBREntry”.FWarehouseID=“KF”.Fid

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “DBR”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “DBR”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “DBR”.FBizDate,‘yyyy-mm-dd’)<= ‘@todate’ and “DBR”.FBasestatus=4 and “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT --生产入库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_ManufactureRecBillEntry “DBREntry”

LEFT JOIN T_IM_ManufactureRecBill “DBR” ON “DBR”.FID=“DBREntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “DBREntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “DBR”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“DBREntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “DBREntry”.FWarehouseID=“KF”.Fid

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “DBR”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “DBR”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “DBR”.FBizDate,‘yyyy-mm-dd’)<= ‘@todate’ and “DBR”.FBasestatus=4 and “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

   union all

SELECT —累计销售出库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_SaleIssueEntry “XSCKEntry”

LEFT JOIN T_IM_SaleIssueBill “XSCK” ON “XSCK”.FID=“XSCKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “XSCKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “XSCK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“XSCKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “XSCKEntry”.FWarehouseID=“KF”.Fid

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “XSCK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “XSCK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “XSCK”.FBizDate,‘yyyy-mm-dd’)<= ‘@todate’ and “XSCK”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

	union all

SELECT —累计其他出库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_OtherIssueBillEntry “QTCKEntry” LEFT JOIN T_IM_OtherIssueBill “QTCK” ON “QTCK”.FID=“QTCKEntry”.FPARENTID LEFT JOIN T_BD_Material “MATERIAL” ON “QTCKEntry”.FMaterialID = “MATERIAL”.FID LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “QTCK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“QTCKEntry”.FBaseUnitID left join T_DB_WAREHOUSE “KF” on “QTCKEntry”.FWarehouseID=“KF”.Fid LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “QTCK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID where to_char( “QTCK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “QTCK”.FBizDate,‘yyyy-mm-dd’)<= ‘@todate’ and “QTCK”.FBasestatus=4 AND “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT —累计调拨出库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_MoveIssueBillEntry “DBCKEntry”

LEFT JOIN T_IM_MoveIssueBill “DBCK” ON “DBCK”.FID=“DBCKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “DBCKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “DBCK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“DBCKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “DBCKEntry”.FWarehouseID=“KF”.Fid

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “DBCK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “DBCK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “DBCK”.FBizDate,‘yyyy-mm-dd’)<= ‘@todate’ and “DBCK”.FBasestatus=4 and “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

union all

SELECT —累计领料出库 “hy”.fname_l2 “hy”, “ADMINORGUNIT”.Fname_l2 “Orgname”, “ADMINORGUNIT”.Fnumber “orgnumber”, “ADMINORGUNITA”.Fname_l2 “gsname”, “ADMINORGUNITA”.Fnumber “gsnumber”, “MATERIAL”.Fnumber “number”, “MATERIAL”.Fname_l2 “name”, “JBJLDW”.Fname_l2 “Baseunit”, “KF”.fname_l2 “kf”, 0 “rkqty”, 0 “rkamount”, 0 “ckqty”, 0 “ckamount”

FROM T_IM_MaterialReqBillEntry “DBCKEntry”

LEFT JOIN T_IM_MaterialReqBill “DBCK” ON “DBCK”.FID=“DBCKEntry”.FPARENTID

LEFT JOIN T_BD_Material “MATERIAL” ON “DBCKEntry”.FMaterialID = “MATERIAL”.FID

LEFT JOIN T_ORG_Admin “ADMINORGUNIT” ON “DBCK”.FStorageOrgUnitID = “ADMINORGUNIT”.FID

left join T_BD_MeasureUnit “JBJLDW” on “JBJLDW”.fid=“DBCKEntry”.FBaseUnitID

left join T_DB_WAREHOUSE “KF” on “DBCKEntry”.FWarehouseID=“KF”.Fid

LEFT JOIN T_ORG_Admin “ADMINORGUNITA” ON “DBCK”.FControlUnitID = “ADMINORGUNITA”.FID

left join T_BD_Industry “hy” on “hy”.fid=“ADMINORGUNITA”.FINDUSTRYID

where to_char( “DBCK”.FBizDate,‘yyyy-mm-dd’)>= ‘2022-01-01’ and to_char( “DBCK”.FBizDate,‘yyyy-mm-dd’)<= ‘@todate’ and “DBCK”.FBasestatus=4 and “ADMINORGUNITA”.fname_l2 in (‘@adminco’) and ( “MATERIAL”.fname_l2 in (‘@wl’) or ? is null )

) “KCQT”

group by “KCQT”.“hy”,“KCQT”.“Orgname”,“KCQT”.“orgnumber”,“KCQT”.“gsname”,“KCQT”.“gsnumber”,“KCQT”.“number”,“KCQT”.“name”,“KCQT”.“Baseunit”,“KCQT”.“kf”