饲料板块毛利润分析主逻辑SQL
admin
2024-04-01 04:34:40
0

/dialect/

select
“MLR”.“FL” “FL”,
“MLR”.“productGroupNumA” “productGroupNumA”, —物料大类代码
“MLR”.“productGroupNameA” “productGroupNameA”, —物料大类名称
“MLR”.“productGroupNum” “productGroupNum”, —物料大类代码
“MLR”.“productGroupName” “productGroupName”, —物料大类名称
“MLR”.“materialGroupNum” “materialGroupNum”,—物料类别代码
“MLR”.“KH” “KH”,
“MLR”.“PQD” “PQD”,
“MLR”.“PQC” “PQC”,
“MLR”.“PQB” “PQB”,
“MLR”.“PQA” “PQA”,
“MLR”.“PQ” “PQ”,
“MLR”.“GS” “GS”,
“MLR”.“XS” “XS”,
“MLR”.“Wlname” “Wlname”,
“MLR”.“materialGroupName” “materialGroupName”,—物料类别名称
“MLR”.“customFISCOMPANY” “customFISCOMPANY”,
sum(“MLR”.“saleQty”) “saleQty”, —销售数量
sum(“MLR”.“saleAmt”) “saleAmt”, --销售金额
sum(“MLR”.“cost”) “cost”, —销售成本
sum(“MLR”.“FH”) “FH”

from
(
select
“FL”.fname_l2 “FL”,
“productGroupA”.fnumber “productGroupNumA”, —物料大类代码
“productGroupA”.fname_l2 “productGroupNameA”, —物料大类名称
“productGroup”.fnumber “productGroupNum”, —物料大类代码
“productGroup”.fname_l2 “productGroupName”, —物料大类名称
“materialGroup”.fnumber “materialGroupNum”,—物料类别代码
“customer”.fname_l2 “KH”,
case when “PQ1”.FLEVEL=4 then “PQ4”.fname_l2 else “PQ3”.fname_l2 end “PQD”,
case when “PQ1”.FLEVEL=4 then “PQ3”.fname_l2 else “PQ2”.fname_l2 end “PQC”,
case when “PQ1”.FLEVEL=4 then “PQ2”.fname_l2 else “PQ1”.fname_l2 end “PQB”,
case when “PQ1”.FLEVEL=4 then “PQ1”.fname_l2 else “PQ”.fname_l2 end “PQA”,
“PQ”.fname_l2 “PQ”,
“company”.fname_l2 “GS”,
“XSY”.fname_l2 “XS”,
“material”.fname_l2 “Wlname”,
“materialGroup”.fname_l2 “materialGroupName”,—物料类别名称
case when “customer”.FISINTERNALCOMPANY= 1 then ‘内部’ else ‘外部’ end “customFISCOMPANY”, —内/外
sum(“arbillentry”.FQuantity) “saleQty”, —销售数量
sum(“arbillentry”.FRecievePayAmount)+sum(nvl(“arbillentry”.CFYearDisAmt,0))+sum(nvl(“arbillentry”.CFMonthDisAmt,0))+sum(nvl(“arbillentry”.CFWindowsDisAmt,0))+sum(nvl(“arbillentry”.CFTempDisAmt,0))-sum(“arbillentry”.FTaxAmount) “saleAmt”, --销售金额
0 “cost”, —销售成本
0 “FH”

from   T_AR_OtherBillentry "arbillentry"  inner join  T_AR_OtherBill "arbill"
on "arbill".fid="arbillentry".fparentidinner join t_bd_customer "customer"
on "arbill".fasstactid="customer".fidinner join t_org_admin "company"
on "arbill".fcompanyid="company".fidleft join t_bd_material "material"
on "arbillentry".FMaterialID="material".fidleft join CT_TKF_MaterialGroup "materialGroup"
on "material".CFMATERIALCUSGROUP="materialGroup".fidleft join T_TKF_MaterialGroupTREE "productGroup"
on  "materialGroup".FTREEID="productGroup".fid left join T_TKF_MaterialGroupTREE "productGroupA"
on  "productGroup".Fparentid="productGroupA".fid inner join CT_TKF_DivideAreaEntry  "PQCUS"
on "customer".FID="PQCUS".CFCUSTOMERNUMIDinner join CT_TKF_DivideAreaAssEntry  "PQCUSA"
on "material".fid="PQCUSA".CFMaterialNumIDinner join CT_TKF_DivideArea  "PQB"
on  "PQCUS".FPARENTID="PQB".fid   and "PQB".FCONTROLUNITID="company".FID and "PQCUSA".FPARENTID="PQB".fid  and  "PQB".CFBASESTATUS=4LEFT JOIN CT_TKF_Area "PQ"
ON  "PQ".FID="PQB".CFAREAIDleft join  T_BD_Person "XSY"
on  "XSY".fid= "PQB".CFSALESPERSONID and "PQB".FCONTROLUNITID="company".FIDLEFT JOIN T_TKF_AreaTREE "PQ1"
ON "PQ1".FID="PQ".FTREEIDLEFT JOIN T_TKF_AreaTREE "PQ2"
ON "PQ2".FID="PQ1".FPARENTIDLEFT JOIN T_TKF_AreaTREE "PQ3"
ON "PQ3".FID="PQ2".FPARENTIDLEFT JOIN T_TKF_AreaTREE "PQ4"
ON "PQ4".FID="PQ3".FPARENTID

left join T_BD_MaterialSales “WLXS”
on “WLXS”.FMATERIALID=“material”.fid and “WLXS”.FCONTROLUNITID=“company”.FID

left join CT_CUS_Producttypea “FL”
on “FL”.fid= “WLXS”.CFPRODUCTTYPEAID

where "arbill".FBillType=102 and"arbill".FBillStatus=3 and"arbill".fistransbill=0 andTO_CHAR("arbill".FBillDate, 'yyyy-MM-dd')>= '@strDate' andTO_CHAR("arbill".FBillDate, 'yyyy-MM-dd')<= '@endDate'   and("PQ3".fid in ('@pq') or ? is null) and "company".fid in ('@adminco') and(("customer".FInternalCompanyID is  null )or ("customer".fnumber in ('01-00025','01-00075','01-100058','01-128372','01-128849','01-100002','01-127555','01-127730','01-132390','01-131543','01-132594','01-133068','01-134821','01-133681','01-100053','01-119765','01-133646','01-137270','01-132594')))

group by “FL”.fname_l2,“productGroupA”.fnumber,“productGroupA”.fname_l2,“productGroup”.fnumber,“productGroup”.fname_l2,“materialGroup”.fnumber,“customer”.fname_l2,“PQ1”.FLEVEL,“PQ4”.fname_l2,“PQ3”.fname_l2,“PQ2”.fname_l2,“PQ1”.fname_l2,“PQ”.fname_l2,“company”.fname_l2,“XSY”.fname_l2,“material”.fname_l2,“materialGroup”.fname_l2,“customer”.FISINTERNALCOMPANY,“customer”.FNUMBER

union all

select
“FL”.fname_l2 “FL”,
“productGroupA”.fnumber “productGroupNumA”, —物料大类代码
“productGroupA”.fname_l2 “productGroupNameA”, —物料大类名称
“productGroup”.fnumber “productGroupNum”, —物料大类代码
“productGroup”.fname_l2 “productGroupName”, —物料大类名称
“materialGroup”.fnumber “materialGroupNum”,—物料类别代码
“customer”.fname_l2 “KH”,
case when “PQ1”.FLEVEL=4 then “PQ4”.fname_l2 else “PQ3”.fname_l2 end “PQD”,
case when “PQ1”.FLEVEL=4 then “PQ3”.fname_l2 else “PQ2”.fname_l2 end “PQC”,
case when “PQ1”.FLEVEL=4 then “PQ2”.fname_l2 else “PQ1”.fname_l2 end “PQB”,
case when “PQ1”.FLEVEL=4 then “PQ1”.fname_l2 else “PQ”.fname_l2 end “PQA”,
“PQ”.fname_l2 “PQ”,
“company”.fname_l2 “GS”,
“XSY”.fname_l2 “XS”,
“material”.fname_l2 “Wlname”,
“materialGroup”.fname_l2 “materialGroupName”,—物料类别名称
case when “customer”.FISINTERNALCOMPANY= 1 then ‘内部’ else ‘外部’ end “customFISCOMPANY”,
0 “saleQty”, —销售数量
0 “saleAmt”, --销售金额
sum(“IssueEntry”.factualcost) “cost”, —销售成本
0 “FH”

from T_IM_SaleIssueEntry “IssueEntry”

inner join T_IM_SaleIssueBill “Issue” on “Issue”.fid=“IssueEntry”.fparentid

inner join t_bd_customer “customer”
on “Issue”.fcustomerid=“customer”.fid

inner join t_org_admin “company”
on “Issue”.fstorageorgunitid=“company”.fid

left join t_bd_material “material”
on “IssueEntry”.FMaterialID=“material”.fid

left join CT_TKF_MaterialGroup “materialGroup”
on “material”.CFMATERIALCUSGROUP=“materialGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroup”
on “materialGroup”.FTREEID=“productGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroupA”
on “productGroup”.Fparentid=“productGroupA”.fid

inner join CT_TKF_DivideAreaEntry “PQCUS”
on “customer”.FID=“PQCUS”.CFCUSTOMERNUMID

inner join CT_TKF_DivideAreaAssEntry “PQCUSA”
on “material”.fid=“PQCUSA”.CFMaterialNumID

inner join CT_TKF_DivideArea “PQB”
on “PQCUS”.FPARENTID=“PQB”.fid and “PQB”.FCONTROLUNITID=“company”.FID and “PQCUSA”.FPARENTID=“PQB”.fid and “PQB”.CFBASESTATUS=4

LEFT JOIN CT_TKF_Area “PQ”
ON “PQ”.FID=“PQB”.CFAREAID

left join T_BD_Person “XSY”
on “XSY”.fid= “PQB”.CFSALESPERSONID and “PQB”.FCONTROLUNITID=“company”.FID

LEFT JOIN T_TKF_AreaTREE “PQ1”
ON “PQ1”.FID=“PQ”.FTREEID

LEFT JOIN T_TKF_AreaTREE “PQ2”
ON “PQ2”.FID=“PQ1”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ3”
ON “PQ3”.FID=“PQ2”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ4”
ON “PQ4”.FID=“PQ3”.FPARENTID

left join T_BD_MaterialSales “WLXS”
on “WLXS”.FMATERIALID=“material”.fid and “WLXS”.FCONTROLUNITID=“company”.FID

left join CT_CUS_Producttypea “FL”
on “FL”.fid= “WLXS”.CFPRODUCTTYPEAID

where “Issue”.fbasestatus=4 and
TO_CHAR(“Issue”.FBizDate, ‘yyyy-MM-dd’)>= ‘@strDate’ and
TO_CHAR(“Issue”.FBizDate, ‘yyyy-MM-dd’)<= ‘@endDate’ and
(“PQ3”.fid in (‘@pq’) or ? is null) and
“company”.fid in (‘@adminco’) and
((“customer”.FInternalCompanyID is null )or (“customer”.fnumber in (‘01-00025’,‘01-00075’,‘01-100058’,‘01-128372’,‘01-128849’,‘01-100002’,‘01-127555’,‘01-127730’,‘01-132390’,‘01-131543’,‘01-132594’,‘01-133068’,‘01-134821’,‘01-133681’,‘01-100053’,‘01-119765’,‘01-133646’,‘01-137270’,‘01-132594’)))

group by “FL”.fname_l2,“productGroupA”.fnumber,“productGroupA”.fname_l2,“productGroup”.fnumber,“productGroup”.fname_l2,“materialGroup”.fnumber,“customer”.fname_l2,“PQ1”.FLEVEL,“PQ4”.fname_l2,“PQ3”.fname_l2,“PQ2”.fname_l2,“PQ1”.fname_l2,“PQ”.fname_l2,“company”.fname_l2,“XSY”.fname_l2,“material”.fname_l2,“materialGroup”.fname_l2,“customer”.FISINTERNALCOMPANY,“customer”.FNUMBER

  union all 

select
“FL”.fname_l2 “FL”,
“productGroupA”.fnumber “productGroupNumA”, —物料大类代码
“productGroupA”.fname_l2 “productGroupNameA”, —物料大类名称
“productGroup”.fnumber “productGroupNum”, —物料大类代码
“productGroup”.fname_l2 “productGroupName”, —物料大类名称
“materialGroup”.fnumber “materialGroupNum”,—物料类别代码
“KH”.fname_l2 “KH”,
case when “PQ1”.FLEVEL=4 then “PQ4”.fname_l2 else “PQ3”.fname_l2 end “PQD”,
case when “PQ1”.FLEVEL=4 then “PQ3”.fname_l2 else “PQ2”.fname_l2 end “PQC”,
case when “PQ1”.FLEVEL=4 then “PQ2”.fname_l2 else “PQ1”.fname_l2 end “PQB”,
case when “PQ1”.FLEVEL=4 then “PQ1”.fname_l2 else “PQ”.fname_l2 end “PQA”,
“PQ”.fname_l2 “PQ”,
“company”.fname_l2 “GS”,
“XSY”.fname_l2 “XS”,
“MATERIAL”.fname_l2 “Wlname”,
“materialGroup”.fname_l2 “materialGroupName”,—物料类别名称
case when “KH”.FISINTERNALCOMPANY= 1 then ‘内部’ else ‘外部’ end “customFISCOMPANY”,
0 “saleQty”, —销售数量
0 “saleAmt”, --销售金额
0 “cost”, —销售成本
sum(“XSCKEntry” .CFDisAmt) “FH”

from CT_DIS_DiscalEntry “XSCKEntry”

inner join CT_DIS_Discal “XSCK”
on “XSCK”.fid=“XSCKEntry”.FParentID

inner join T_BD_Material “MATERIAL”
on “XSCKEntry”.CFMaterialNumID=“MATERIAL”.FID

inner join T_ORG_Admin “company”
on “XSCK”.CFSaleOrgUnitID=“company”.FID

inner join T_BD_Customer “KH”
on “XSCK”.CFCustomerID=“KH”.fid

left join CT_TKF_MaterialGroup “materialGroup”
on “MATERIAL”.CFMATERIALCUSGROUP=“materialGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroup”
on “materialGroup”.FTREEID=“productGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroupA”
on “productGroup”.Fparentid=“productGroupA”.fid

inner join CT_TKF_DivideAreaEntry “PQCUS”
on “KH”.FID=“PQCUS”.CFCUSTOMERNUMID

inner join CT_TKF_DivideAreaAssEntry “PQCUSA”
on “MATERIAL”.fid=“PQCUSA”.CFMaterialNumID

inner join CT_TKF_DivideArea “PQB”
on “PQCUS”.FPARENTID=“PQB”.fid and “PQB”.FCONTROLUNITID=“company”.FID and “PQCUSA”.FPARENTID=“PQB”.fid and “PQB”.CFBASESTATUS=4

LEFT JOIN CT_TKF_Area “PQ”
ON “PQ”.FID=“PQB”.CFAREAID

left join T_BD_Person “XSY”
on “XSY”.fid= “PQB”.CFSALESPERSONID and “PQB”.FCONTROLUNITID=“company”.FID

LEFT JOIN T_TKF_AreaTREE “PQ1”
ON “PQ1”.FID=“PQ”.FTREEID

LEFT JOIN T_TKF_AreaTREE “PQ2”
ON “PQ2”.FID=“PQ1”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ3”
ON “PQ3”.FID=“PQ2”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ4”
ON “PQ4”.FID=“PQ3”.FPARENTID

LEFT JOIN CT_DIS_DisType “TYPE”
on “XSCK”.CFDisTypeID= “TYPE”.fid

left join T_BD_MaterialSales “WLXS”
on “WLXS”.FMATERIALID=“MATERIAL”.fid and “WLXS”.FCONTROLUNITID=“company”.FID

left join CT_CUS_Producttypea “FL”
on “FL”.fid= “WLXS”.CFPRODUCTTYPEAID

where
TO_CHAR(“XSCK”.FBizDate, ‘yyyy-MM-dd’) >= ‘@strDate’ AND
TO_CHAR(“XSCK”.FBizDate, ‘yyyy-MM-dd’) <= ‘@endDate’ AND
“company”.fid in (‘@adminco’) and
“XSCK”.CFBaseStatus=4 and
(“PQ3”.fid in (‘@pq’) or ? is null) and
“XSCK”.CFDisTypeID<>‘K5kAAAA05DGCMu2P’ and
(“XSCKEntry”.CFDetailCustomerID is null ) and
“XSCK”.CFIsTransBill=0 and
“XSCK”.CFIsAdjustBill=0

group by “FL”.fname_l2,“productGroupA”.fnumber,“productGroupA”.fname_l2,“productGroup”.fnumber,“productGroup”.fname_l2,“materialGroup”.fnumber,“KH”.fname_l2,“PQ1”.FLEVEL,“PQ4”.fname_l2,“PQ3”.fname_l2,“PQ2”.fname_l2,“PQ1”.fname_l2,“PQ”.fname_l2,“company”.fname_l2,“XSY”.fname_l2,“MATERIAL”.fname_l2,“materialGroup”.fname_l2,“KH”.FISINTERNALCOMPANY,“KH”.FNUMBER

union all

select
“FL”.fname_l2 “FL”,
“productGroupA”.fnumber “productGroupNumA”, —物料大类代码
“productGroupA”.fname_l2 “productGroupNameA”, —物料大类名称
“productGroup”.fnumber “productGroupNum”, —物料大类代码
“productGroup”.fname_l2 “productGroupName”, —物料大类名称
“materialGroup”.fnumber “materialGroupNum”,—物料类别代码
“KH”.fname_l2 “KH”,
case when “PQ1”.FLEVEL=4 then “PQ4”.fname_l2 else “PQ3”.fname_l2 end “PQD”,
case when “PQ1”.FLEVEL=4 then “PQ3”.fname_l2 else “PQ2”.fname_l2 end “PQC”,
case when “PQ1”.FLEVEL=4 then “PQ2”.fname_l2 else “PQ1”.fname_l2 end “PQB”,
case when “PQ1”.FLEVEL=4 then “PQ1”.fname_l2 else “PQ”.fname_l2 end “PQA”,
“PQ”.fname_l2 “PQ”,
“company”.fname_l2 “GS”,
“XSY”.fname_l2 “XS”,
“MATERIAL”.fname_l2 “Wlname”,
“materialGroup”.fname_l2 “materialGroupName”,—物料类别名称
case when “KH”.FISINTERNALCOMPANY= 1 then ‘内部’ else ‘外部’ end “customFISCOMPANY”,
0 “saleQty”, —销售数量
0 “saleAmt”, --销售金额
0 “cost”, —销售成本
sum(“XSCKEntry” .CFDisAmt) “FH”

from CT_DIS_DiscalEntry “XSCKEntry”

inner join CT_DIS_Discal “XSCK”
on “XSCK”.fid=“XSCKEntry”.FParentID

inner join T_BD_Material “MATERIAL”
on “XSCKEntry”.CFMaterialNumID=“MATERIAL”.FID

inner join T_ORG_Admin “company”
on “XSCK”.CFSaleOrgUnitID=“company”.FID

inner join T_BD_Customer “KH”
on “XSCKEntry”.CFDetailCustomerID=“KH”.fid

left join CT_TKF_MaterialGroup “materialGroup”
on “MATERIAL”.CFMATERIALCUSGROUP=“materialGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroup”
on “materialGroup”.FTREEID=“productGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroupA”
on “productGroup”.Fparentid=“productGroupA”.fid

inner join CT_TKF_DivideAreaEntry “PQCUS”
on “KH”.FID=“PQCUS”.CFCUSTOMERNUMID

inner join CT_TKF_DivideAreaAssEntry “PQCUSA”
on “MATERIAL”.fid=“PQCUSA”.CFMaterialNumID

inner join CT_TKF_DivideArea “PQB”
on “PQCUS”.FPARENTID=“PQB”.fid and “PQB”.FCONTROLUNITID=“company”.FID and “PQCUSA”.FPARENTID=“PQB”.fid and “PQB”.CFBASESTATUS=4

LEFT JOIN CT_TKF_Area “PQ”
ON “PQ”.FID=“PQB”.CFAREAID

left join T_BD_Person “XSY”
on “XSY”.fid= “PQB”.CFSALESPERSONID and “PQB”.FCONTROLUNITID=“company”.FID

LEFT JOIN T_TKF_AreaTREE “PQ1”
ON “PQ1”.FID=“PQ”.FTREEID

LEFT JOIN T_TKF_AreaTREE “PQ2”
ON “PQ2”.FID=“PQ1”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ3”
ON “PQ3”.FID=“PQ2”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ4”
ON “PQ4”.FID=“PQ3”.FPARENTID

LEFT JOIN CT_DIS_DisType “TYPE”
on “XSCK”.CFDisTypeID= “TYPE”.fid

left join T_BD_MaterialSales “WLXS”
on “WLXS”.FMATERIALID=“MATERIAL”.fid and “WLXS”.FCONTROLUNITID=“company”.FID

left join CT_CUS_Producttypea “FL”
on “FL”.fid= “WLXS”.CFPRODUCTTYPEAID

where
TO_CHAR(“XSCK”.FBizDate, ‘yyyy-MM-dd’) >= ‘@strDate’ AND
TO_CHAR(“XSCK”.FBizDate, ‘yyyy-MM-dd’) <= ‘@endDate’ AND
“company”.fid in (‘@adminco’) and
“XSCK”.CFBaseStatus=4 and
(“PQ3”.fid in (‘@pq’) or ? is null) and
“XSCK”.CFDisTypeID<>‘K5kAAAA05DGCMu2P’ and
(“XSCKEntry”.CFDetailCustomerID is not null ) and
“XSCK”.CFIsTransBill=0 and
“XSCK”.CFIsAdjustBill=0

group by “FL”.fname_l2,“productGroupA”.fnumber,“productGroupA”.fname_l2,“productGroup”.fnumber,“productGroup”.fname_l2,“materialGroup”.fnumber,“KH”.fname_l2,“PQ1”.FLEVEL,“PQ4”.fname_l2,“PQ3”.fname_l2,“PQ2”.fname_l2,“PQ1”.fname_l2,“PQ”.fname_l2,“company”.fname_l2,“XSY”.fname_l2,“MATERIAL”.fname_l2,“materialGroup”.fname_l2,“KH”.FISINTERNALCOMPANY,“KH”.FNUMBER

union all

select
“FL”.fname_l2 “FL”,
“productGroupA”.fnumber “productGroupNumA”, —物料大类代码
“productGroupA”.fname_l2 “productGroupNameA”, —物料大类名称
“productGroup”.fnumber “productGroupNum”, —物料大类代码
“productGroup”.fname_l2 “productGroupName”, —物料大类名称
“materialGroup”.fnumber “materialGroupNum”,—物料类别代码
“KH”.fname_l2 “KH”,
case when “PQ1”.FLEVEL=4 then “PQ4”.fname_l2 else “PQ3”.fname_l2 end “PQD”,
case when “PQ1”.FLEVEL=4 then “PQ3”.fname_l2 else “PQ2”.fname_l2 end “PQC”,
case when “PQ1”.FLEVEL=4 then “PQ2”.fname_l2 else “PQ1”.fname_l2 end “PQB”,
case when “PQ1”.FLEVEL=4 then “PQ1”.fname_l2 else “PQ”.fname_l2 end “PQA”,
“PQ”.fname_l2 “PQ”,
“company”.fname_l2 “GS”,
“XSY”.fname_l2 “XS”,
“MATERIAL”.fname_l2 “Wlname”,
“materialGroup”.fname_l2 “materialGroupName”,—物料类别名称
case when “KH”.FISINTERNALCOMPANY= 1 then ‘内部’ else ‘外部’ end “customFISCOMPANY”,
0 “saleQty”, —销售数量
0 “saleAmt”, --销售金额
0 “cost”, —销售成本
sum(“XSCKEntry” .CFDisAmt) “FH”

from CT_DIS_DiscalEntry “XSCKEntry”

inner join CT_DIS_Discal “XSCK”
on “XSCK”.fid=“XSCKEntry”.FParentID

inner join T_BD_Material “MATERIAL”
on “XSCKEntry”.CFMaterialNumID=“MATERIAL”.FID

inner join T_ORG_Admin “company”
on “XSCK”.CFSaleOrgUnitID=“company”.FID

inner join T_BD_Customer “KH”
on “XSCK”.CFCustomerID=“KH”.fid

left join CT_TKF_MaterialGroup “materialGroup”
on “MATERIAL”.CFMATERIALCUSGROUP=“materialGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroup”
on “materialGroup”.FTREEID=“productGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroupA”
on “productGroup”.Fparentid=“productGroupA”.fid

inner join CT_TKF_DivideAreaEntry “PQCUS”
on “KH”.FID=“PQCUS”.CFCUSTOMERNUMID

inner join CT_TKF_DivideAreaAssEntry “PQCUSA”
on “MATERIAL”.fid=“PQCUSA”.CFMaterialNumID

inner join CT_TKF_DivideArea “PQB”
on “PQCUS”.FPARENTID=“PQB”.fid and “PQB”.FCONTROLUNITID=“company”.FID and “PQCUSA”.FPARENTID=“PQB”.fid and “PQB”.CFBASESTATUS=4

LEFT JOIN CT_TKF_Area “PQ”
ON “PQ”.FID=“PQB”.CFAREAID

left join T_BD_Person “XSY”
on “XSY”.fid= “PQB”.CFSALESPERSONID and “PQB”.FCONTROLUNITID=“company”.FID

LEFT JOIN T_TKF_AreaTREE “PQ1”
ON “PQ1”.FID=“PQ”.FTREEID

LEFT JOIN T_TKF_AreaTREE “PQ2”
ON “PQ2”.FID=“PQ1”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ3”
ON “PQ3”.FID=“PQ2”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ4”
ON “PQ4”.FID=“PQ3”.FPARENTID

LEFT JOIN CT_DIS_DisType “TYPE”
on “XSCK”.CFDisTypeID= “TYPE”.fid

left join T_BD_MaterialSales “WLXS”
on “WLXS”.FMATERIALID=“MATERIAL”.fid and “WLXS”.FCONTROLUNITID=“company”.FID

left join CT_CUS_Producttypea “FL”
on “FL”.fid= “WLXS”.CFPRODUCTTYPEAID

where
TO_CHAR(“XSCK”.FBizDate, ‘yyyy-MM-dd’) >= ‘@strDate’ AND
TO_CHAR(“XSCK”.FBizDate, ‘yyyy-MM-dd’) <= ‘@endDate’ AND
“company”.fid in (‘@adminco’) and
“XSCK”.CFBaseStatus=2 and
(“PQ3”.fid in (‘@pq’) or ? is null) and
(“XSCKEntry”.CFDetailCustomerID is null ) and
“XSCK”.CFIsTransBill=0 and
“XSCK”.CFIsAdjustBill=0

group by “FL”.fname_l2,“productGroupA”.fnumber,“productGroupA”.fname_l2,“productGroup”.fnumber,“productGroup”.fname_l2,“materialGroup”.fnumber,“KH”.fname_l2,“PQ1”.FLEVEL,“PQ4”.fname_l2,“PQ3”.fname_l2,“PQ2”.fname_l2,“PQ1”.fname_l2,“PQ”.fname_l2,“company”.fname_l2,“XSY”.fname_l2,“MATERIAL”.fname_l2,“materialGroup”.fname_l2,“KH”.FISINTERNALCOMPANY,“KH”.FNUMBER

union all

select
“FL”.fname_l2 “FL”,
“productGroupA”.fnumber “productGroupNumA”, —物料大类代码
“productGroupA”.fname_l2 “productGroupNameA”, —物料大类名称
“productGroup”.fnumber “productGroupNum”, —物料大类代码
“productGroup”.fname_l2 “productGroupName”, —物料大类名称
“materialGroup”.fnumber “materialGroupNum”,—物料类别代码
“KH”.fname_l2 “KH”,
case when “PQ1”.FLEVEL=4 then “PQ4”.fname_l2 else “PQ3”.fname_l2 end “PQD”,
case when “PQ1”.FLEVEL=4 then “PQ3”.fname_l2 else “PQ2”.fname_l2 end “PQC”,
case when “PQ1”.FLEVEL=4 then “PQ2”.fname_l2 else “PQ1”.fname_l2 end “PQB”,
case when “PQ1”.FLEVEL=4 then “PQ1”.fname_l2 else “PQ”.fname_l2 end “PQA”,
“PQ”.fname_l2 “PQ”,
“company”.fname_l2 “GS”,
“XSY”.fname_l2 “XS”,
“MATERIAL”.fname_l2 “Wlname”,
“materialGroup”.fname_l2 “materialGroupName”,—物料类别名称
case when “KH”.FISINTERNALCOMPANY= 1 then ‘内部’ else ‘外部’ end “customFISCOMPANY”,
0 “saleQty”, —销售数量
0 “saleAmt”, --销售金额
0 “cost”, —销售成本
sum(“XSCKEntry” .CFDisAmt) “FH”

from CT_DIS_DiscalEntry “XSCKEntry”

inner join CT_DIS_Discal “XSCK”
on “XSCK”.fid=“XSCKEntry”.FParentID

inner join T_BD_Material “MATERIAL”
on “XSCKEntry”.CFMaterialNumID=“MATERIAL”.FID

inner join T_ORG_Admin “company”
on “XSCK”.CFSaleOrgUnitID=“company”.FID

inner join T_BD_Customer “KH”
on “XSCKEntry”.CFDetailCustomerID=“KH”.fid

left join CT_TKF_MaterialGroup “materialGroup”
on “MATERIAL”.CFMATERIALCUSGROUP=“materialGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroup”
on “materialGroup”.FTREEID=“productGroup”.fid

left join T_TKF_MaterialGroupTREE “productGroupA”
on “productGroup”.Fparentid=“productGroupA”.fid

inner join CT_TKF_DivideAreaEntry “PQCUS”
on “KH”.FID=“PQCUS”.CFCUSTOMERNUMID

inner join CT_TKF_DivideAreaAssEntry “PQCUSA”
on “MATERIAL”.fid=“PQCUSA”.CFMaterialNumID

inner join CT_TKF_DivideArea “PQB”
on “PQCUS”.FPARENTID=“PQB”.fid and “PQB”.FCONTROLUNITID=“company”.FID and “PQCUSA”.FPARENTID=“PQB”.fid and “PQB”.CFBASESTATUS=4

LEFT JOIN CT_TKF_Area “PQ”
ON “PQ”.FID=“PQB”.CFAREAID

left join T_BD_Person “XSY”
on “XSY”.fid= “PQB”.CFSALESPERSONID and “PQB”.FCONTROLUNITID=“company”.FID

LEFT JOIN T_TKF_AreaTREE “PQ1”
ON “PQ1”.FID=“PQ”.FTREEID

LEFT JOIN T_TKF_AreaTREE “PQ2”
ON “PQ2”.FID=“PQ1”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ3”
ON “PQ3”.FID=“PQ2”.FPARENTID

LEFT JOIN T_TKF_AreaTREE “PQ4”
ON “PQ4”.FID=“PQ3”.FPARENTID

LEFT JOIN CT_DIS_DisType “TYPE”
on “XSCK”.CFDisTypeID= “TYPE”.fid

left join T_BD_MaterialSales “WLXS”
on “WLXS”.FMATERIALID=“MATERIAL”.fid and “WLXS”.FCONTROLUNITID=“company”.FID

left join CT_CUS_Producttypea “FL”
on “FL”.fid= “WLXS”.CFPRODUCTTYPEAID

where
TO_CHAR(“XSCK”.FBizDate, ‘yyyy-MM-dd’) >= ‘@strDate’ AND
TO_CHAR(“XSCK”.FBizDate, ‘yyyy-MM-dd’) <= ‘@endDate’ AND
“company”.fid in (‘@adminco’) and
“XSCK”.CFBaseStatus=2 and
(“PQ3”.fid in (‘@pq’) or ? is null) and
(“XSCKEntry”.CFDetailCustomerID is not null ) and
“XSCK”.CFIsTransBill=0 and
“XSCK”.CFIsAdjustBill=0

group by “FL”.fname_l2,“productGroupA”.fnumber,“productGroupA”.fname_l2,“productGroup”.fnumber,“productGroup”.fname_l2,“materialGroup”.fnumber,“KH”.fname_l2,“PQ1”.FLEVEL,“PQ4”.fname_l2,“PQ3”.fname_l2,“PQ2”.fname_l2,“PQ1”.fname_l2,“PQ”.fname_l2,“company”.fname_l2,“XSY”.fname_l2,“MATERIAL”.fname_l2,“materialGroup”.fname_l2,“KH”.FISINTERNALCOMPANY,“KH”.FNUMBER

) “MLR”

group by “MLR”.“FL”,“MLR”.“productGroupNumA”,“MLR”.“productGroupNameA”,“MLR”.“productGroupNum”,“MLR”.“productGroupName”,“MLR”.“materialGroupNum”,“MLR”.“KH”,“MLR”.“PQD”,“MLR”.“PQC”,“MLR”.“PQB”,“MLR”.“PQA”,“MLR”.“PQ”, “MLR”.“GS”,“MLR”.“XS”,“MLR”.“Wlname”,“MLR”.“materialGroupName”,“MLR”.“customFISCOMPANY”

相关内容

热门资讯

安卓系统所有手机通用,揭秘所有... 你有没有发现,现在市面上各种各样的手机,品牌琳琅满目,让人挑花了眼?不过,不管你用的是哪个品牌的手机...
安卓系统变ios系统好用吗,体... 你有没有想过,为什么有些人从安卓手机换到了苹果的iPhone,而且好像换了之后整个人都精神焕发呢?没...
华为安卓系统文件删除,轻松掌握... 你有没有遇到过这种情况?手机里的文件突然不见了,尤其是华为安卓系统里的那些重要文件,真是让人心头一紧...
安卓1.5系统软件,初代智能革... 你有没有想过,手机里的那些看似普通的系统,其实背后藏着无数的故事呢?今天,就让我带你走进安卓1.5系...
安卓 代替系统音效设置,打造个... 你有没有发现,手机里的那些系统音效有时候真的让人有点烦躁呢?比如那个每次接电话时“叮咚”一声,或者是...
安卓系统整体性,构建移动设备的... 你知道吗?在智能手机的世界里,安卓系统就像是个全能的魔术师,它不仅拥有丰富的魔法,还能让各种神奇的魔...
安卓系统能玩鬼泣么?,畅享动作... 你有没有想过,在安卓系统上玩《鬼泣》这款游戏会是怎样的体验呢?没错,就是那个让你热血沸腾、挑战极限的...
安卓系统手机编曲软件,音乐创作... 你有没有想过,用手机也能轻松编曲?没错,就是那个你每天不离手的安卓系统手机!现在,我就要给你揭秘几款...
车载导航显示安卓系统,车载导航... 你有没有发现,现在的车载导航系统越来越智能了?尤其是那些搭载了安卓系统的车载导航,简直就像是个贴心的...
安卓系统启动黑屏,安卓系统启动... 手机屏幕突然黑了,这可怎么办?别急,今天就来和你聊聊安卓系统启动黑屏这个让人头疼的问题。你知道吗,这...
怎么解除订阅安卓系统,安卓系统... 你是不是也和我一样,手机里订阅了好多服务,结果现在想解除订阅,却一头雾水?别急,今天就来手把手教你如...
安卓系统停用怎么开启,轻松恢复... 亲爱的手机控们,你是否曾经遇到过安卓系统突然停用的情况,让你手忙脚乱,不知所措?别担心,今天就来教你...
安卓系统电池健康度,电池健康度... 你有没有发现,你的安卓手机最近是不是有点儿不给力了?电池续航能力大不如前,充电速度也慢了不少?别急,...
安卓系统按键怎么截图,安卓系统... 你是不是也和我一样,有时候想截个图分享给朋友,却发现安卓手机的截图功能有点神秘呢?别急,今天就来手把...
购票系统安卓源代码,架构设计与... 你有没有想过,那些我们每天离不开的购票系统,它们背后的秘密是什么呢?今天,就让我带你一探究竟,揭开购...
安卓手机系统后台测试,深度解析... 你有没有发现,你的安卓手机后台总是悄悄地忙碌着?别小看了这些后台程序,它们可是手机系统稳定运行的关键...
安卓系统重启的图标,解锁设备新... 手机突然重启,是不是心里有点慌?别急,今天就来和你聊聊安卓系统重启的图标,让你一眼就能认出它,再也不...
车载智慧屏安卓系统,智能出行新... 你有没有发现,现在的车载智慧屏越来越智能了?尤其是那些搭载了安卓系统的,简直就像是个移动的小电脑,不...
安卓系统连上网权限,解锁设备无... 你有没有发现,你的安卓手机里有些应用总是偷偷连上网?别小看这个小小的网络权限,它可是能影响你隐私、消...
安卓谷歌操作系统,探索安卓谷歌... 你知道吗?在智能手机的世界里,有一个操作系统可是无人不知、无人不晓,那就是安卓谷歌操作系统。它就像一...