饲料板块毛利润分析主逻辑SQL_民生

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

  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