饲料板块毛利润分析主逻辑SQL

/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”

代码语言：javascript复制

from   T_AR_OtherBillentry "arbillentry"  

inner join  T_AR_OtherBill "arbill"
on "arbill".fid="arbillentry".fparentid

inner join t_bd_customer "customer"
on "arbill".fasstactid="customer".fid

inner join t_org_admin "company"
on "arbill".fcompanyid="company".fid

left join t_bd_material "material"
on "arbillentry".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

代码语言：javascript复制

where "arbill".FBillType=102 and
      "arbill".FBillStatus=3 and
      "arbill".fistransbill=0 and
      TO_CHAR("arbill".FBillDate, 'yyyy-MM-dd')>= '@strDate' and
      TO_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’)))

代码语言：javascript复制

  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