饲料板块毛利润分析主逻辑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”

相关内容

热门资讯

怎么解除订阅安卓系统,安卓系统... 你是不是也和我一样,手机里订阅了好多服务,结果现在想解除订阅,却一头雾水?别急,今天就来手把手教你如...
安卓系统停用怎么开启,轻松恢复... 亲爱的手机控们,你是否曾经遇到过安卓系统突然停用的情况,让你手忙脚乱,不知所措?别担心,今天就来教你...
安卓系统电池健康度,电池健康度... 你有没有发现,你的安卓手机最近是不是有点儿不给力了?电池续航能力大不如前,充电速度也慢了不少?别急,...
安卓系统按键怎么截图,安卓系统... 你是不是也和我一样,有时候想截个图分享给朋友,却发现安卓手机的截图功能有点神秘呢?别急,今天就来手把...
购票系统安卓源代码,架构设计与... 你有没有想过,那些我们每天离不开的购票系统,它们背后的秘密是什么呢?今天,就让我带你一探究竟,揭开购...
安卓手机系统后台测试,深度解析... 你有没有发现,你的安卓手机后台总是悄悄地忙碌着?别小看了这些后台程序,它们可是手机系统稳定运行的关键...
安卓系统重启的图标,解锁设备新... 手机突然重启,是不是心里有点慌?别急,今天就来和你聊聊安卓系统重启的图标,让你一眼就能认出它,再也不...
车载智慧屏安卓系统,智能出行新... 你有没有发现,现在的车载智慧屏越来越智能了?尤其是那些搭载了安卓系统的,简直就像是个移动的小电脑,不...
安卓系统连上网权限,解锁设备无... 你有没有发现,你的安卓手机里有些应用总是偷偷连上网?别小看这个小小的网络权限,它可是能影响你隐私、消...
安卓谷歌操作系统,探索安卓谷歌... 你知道吗?在智能手机的世界里,有一个操作系统可是无人不知、无人不晓,那就是安卓谷歌操作系统。它就像一...
安卓系统手写%怎样调出,具体实... 你有没有遇到过这种情况:在使用安卓手机的时候,突然想用手写输入法来记录一些灵感或者重要信息,可是怎么...
安卓手机重置 系统设置,轻松恢... 手机用久了是不是感觉卡顿得厉害?别急,今天就来教你怎么给安卓手机来个大变身——重置系统设置!想象你的...
win如何安装安卓系统,Win... 哇,你有没有想过,让你的Win系统也能玩转安卓应用?没错,就是那种在手机上轻松自如的安卓系统,现在也...
苹果qq和安卓系统,跨平台体验... 你有没有发现,现在手机市场上,苹果和安卓的较量可是越来越激烈了呢!咱们就来聊聊这个话题,看看苹果QQ...
显示最好的安卓系统,探索最新旗... 你有没有想过,为什么安卓系统那么受欢迎呢?它就像一个魔法盒子,里面装满了各种神奇的魔法。今天,就让我...
安卓app怎么降级系统,系统版... 你有没有发现,有时候安卓手机的系统更新后,新功能虽然炫酷,但老系统用起来更顺手呢?别急,今天就来教你...
雷军脱离安卓系统,引领科技变革... 你知道吗?最近科技圈可是炸开了锅,因为我们的雷军大大竟然宣布要脱离安卓系统,这可真是让人大跌眼镜啊!...
安卓系统自动开网络,安卓系统自... 你有没有发现,手机里的安卓系统有时候会自动开启网络连接,这可真是让人又爱又恨啊!有时候,你正专心致志...
安卓系统怎样控制后台,因为服务... 手机里的安卓系统是不是感觉越来越卡了?后台程序太多,不仅耗电还影响性能。别急,今天就来教你怎么巧妙地...
安卓系统打游戏推荐,一触即达! 你有没有发现,现在手机游戏越来越好玩了?不管是休闲小游戏还是大型MMORPG,都能在手机上畅玩。但是...