fork download
copy 
  1. -----------------customer status report 29/04/26 ----------------- 
  2. select cust.c_bpartner_id
  3.     ,cust.customer_code, cust.customer_name, cust.customer_address, cust.customer_phone
  4.     , prod_segment.segment_id, prod_segment.segment_name
  5.     ,cust.district, cust.thana, cust.ad_org_id, cust.terr_id
  6.     ,terr.terrName, terr.terrCode, terr.terroffval, terr.terrOfficer, terr.C_SalesRegion_Parent_ID
  7.     ,terr.C_SalesRegion_ID, terr.AD_Org_ID
  8.     ,zone.zoneName, zone.zoneCode, zone.zoneoffval, zone.zoneOfficer, zone.C_SalesRegion_ID as zone_id
  9.     ,division.divisionName, division.divisionCode, division.divisionoffval, division.divisionOfficer, division.C_SalesRegion_ID as division_id
  10.     ,hos.hosName, hos.hosCode, hos.hosoffval, hos.hosOfficer, hos.hosEmpID, hos.hosDesig
  11.     ,(select name from ad_org where ad_org_id = cust.ad_org_id) as org_name
  12.     ,(select name from m_warehouse where m_warehouse_id = da.m_warehouse_id limit 1) as warehouse
  13.     ,da.m_warehouse_id
  14.     ,main_data.order_amt as order_amt
  15.     ,main_data.inv_amt as inv_amt
  16.     ,main_data.pay_amt as pay_amt
  17. ,customerType.name
  18. from
  19. (
  20.     select cb.c_bpartner_id,cb.value as customer_code,cb.name as customer_name
  21.     	,cl.customer_phone,cr.name as district,cc.name as thana,cb.ad_org_id,tca.territory_id as terr_id
  22.         ,trim(trailing ',' from concat(cl.address1, cl.address2, cl.address3, cl.city, ',')) as customer_address
  23.     from c_bpartner cb
  24.     LEFT JOIN LATERAL (
  25. 	    SELECT cl.address1, cl.address2, cl.address3,cl.city,cl.c_region_id, cl.c_city_id
  26. 	    ,case when cbl.phone is null and cbl.phone2 is not null then cbl.phone2 else cbl.phone end as customer_phone
  27. 	    FROM c_bpartner_location cbl
  28. 	    JOIN c_location cl ON cl.c_location_id = cbl.c_location_id
  29. 	    WHERE cbl.c_bpartner_id = cb.c_bpartner_id
  30. 	      AND cl.isactive = 'Y'
  31. 	    ORDER BY cbl.created
  32. 	    LIMIT 1
  33. 	) cl ON TRUE
  34.     left join c_region cr on cr.c_region_id = cl.c_region_id
  35.     left join c_city cc on cc.c_city_id = cl.c_city_id
  36.     left join c_activity ca on ca.c_activity_id = cb.c_activity_id
  37.     left join T_CustomerAssignment tca on tca.c_bpartner_id = cb.c_bpartner_id
  38.         and (tca.datestart <= $P{dd2}::date and (tca.datefinish is null or tca.datefinish >= $P{dd2}::date))
  39.     where ($P{AD_Org_ID} IS NULL OR cb.ad_org_id = $P{AD_Org_ID})
  40.       and ($P{C_BPartner_ID} IS NULL OR cb.c_bpartner_id = $P{C_BPartner_ID})
  41.     order by cb.c_bpartner_id
  42. ) cust
  43.  
  44. -- =================== PRODUCT SEGMENT BLOCK (from order lines) ===================
  45. join
  46. (
  47.     SELECT 
  48.         co.c_bpartner_id,
  49.         string_agg(DISTINCT ca.c_activity_id::text, ', ') as segment_id,
  50.         string_agg(DISTINCT ca.name, ', ') as segment_name
  51.     FROM c_order co
  52.     JOIN c_orderline col ON col.c_order_id = co.c_order_id
  53.     JOIN m_product mp ON mp.m_product_id = col.m_product_id
  54.     JOIN c_activity ca ON ca.c_activity_id = mp.c_activity_id
  55.     WHERE co.docstatus IN ('CO', 'CL')
  56.     AND ($P{C_Activity_ID} IS NULL OR ca.c_activity_id = $P{C_Activity_ID})
  57.     GROUP BY co.c_bpartner_id
  58. ) prod_segment ON prod_segment.c_bpartner_id = cust.c_bpartner_id
  59.  
  60. left join (
  61.     SELECT DISTINCT cd.c_doctype_id,cd.name,co.c_bpartner_id FROM c_order co JOIN c_doctype cd ON cd.c_doctype_id = co.c_doctype_id
  62.      WHERE cd.docbasetype LIKE 'SOO' AND cd.docsubtypeso LIKE 'SO'
  63. ) customerType on customerType.c_bpartner_id = cust.c_bpartner_id
  64.  
  65. left join
  66. (
  67.     select DISTINCT rg.name as terrName, rg.value as terrCode
  68.         ,bp.value as terroffval, bp.name as terrOfficer
  69.         ,srgm.C_SalesRegion_Parent_ID
  70.         ,rg.C_SalesRegion_ID, rg.AD_Org_ID
  71.     from C_SalesRegion rg
  72.     left join T_SupervisorAssignment cs on rg.C_SalesRegion_ID = cs.C_SalesRegion_ID
  73.         and (cs.datestart <= $P{dd2}::date and (cs.datefinish is null or cs.datefinish >= $P{dd2}::date))
  74.     left join C_BPartner bp on bp.C_BPartner_ID = cs.C_BPartner_ID
  75.     left join HR_Employee emp on bp.C_BPartner_ID = emp.C_BPartner_ID
  76.     left join HR_Job desig on emp.HR_Job_ID = desig.HR_Job_ID
  77.     left join T_SalesRegionMapping srgm on srgm.C_SalesRegion_ID = rg.C_SalesRegion_ID
  78.     left join C_SalesRegion prg on prg.C_SalesRegion_ID = srgm.C_SalesRegion_Parent_ID
  79.     left join T_SalesRegionMapping srgm1 on srgm1.C_SalesRegion_ID = prg.C_SalesRegion_ID
  80.     where rg.levelno = 4
  81.     order by terrName
  82. ) terr
  83.   on cust.terr_id = terr.C_SalesRegion_ID
  84.  and terr.AD_Org_ID = cust.ad_org_id
  85.  
  86. left join
  87. (
  88.     select distinct rg.name as zoneName, rg.value as zoneCode
  89.         ,bp.value as zoneoffval, bp.name as zoneOfficer
  90.         ,srgm.C_SalesRegion_Parent_ID, rg.C_SalesRegion_ID
  91.     from C_SalesRegion rg
  92.     left join T_SupervisorAssignment cs on rg.C_SalesRegion_ID = cs.C_SalesRegion_ID
  93.         and (cs.datestart <= $P{dd2}::date and (cs.datefinish is null or cs.datefinish >= $P{dd2}::date))
  94.     left join C_BPartner bp on bp.C_BPartner_ID = cs.C_BPartner_ID
  95.     left join HR_Employee emp on bp.C_BPartner_ID = emp.C_BPartner_ID
  96.     left join HR_Job desig on emp.HR_Job_ID = desig.HR_Job_ID
  97.     left join T_SalesRegionMapping srgm on srgm.C_SalesRegion_ID = rg.C_SalesRegion_ID
  98.     left join C_SalesRegion prg on prg.C_SalesRegion_ID = srgm.C_SalesRegion_Parent_ID
  99.     left join T_SalesRegionMapping srgm1 on srgm1.C_SalesRegion_ID = prg.C_SalesRegion_ID
  100.     where rg.levelno = 3
  101.     order by zoneName
  102. ) zone on terr.C_SalesRegion_Parent_ID = zone.C_SalesRegion_ID
  103.        and terr.AD_Org_ID = cust.AD_Org_ID
  104.  
  105. left join
  106. (
  107.     select DISTINCT rg.name as divisionName, rg.value as divisionCode
  108.         ,bp.value as divisionoffval, bp.name as divisionOfficer
  109.         ,srgm.C_SalesRegion_Parent_ID, rg.C_SalesRegion_ID
  110.     from C_SalesRegion rg
  111.     left join T_SupervisorAssignment cs on rg.C_SalesRegion_ID = cs.C_SalesRegion_ID
  112.         and (cs.datestart <= $P{dd2}::date and (cs.datefinish is null or cs.datefinish >= $P{dd2}::date))
  113.     left join C_BPartner bp on bp.C_BPartner_ID = cs.C_BPartner_ID
  114.     left join HR_Employee emp on bp.C_BPartner_ID = emp.C_BPartner_ID
  115.     left join HR_Job desig on emp.HR_Job_ID = desig.HR_Job_ID
  116.     left join T_SalesRegionMapping srgm on srgm.C_SalesRegion_ID = rg.C_SalesRegion_ID
  117.     left join C_SalesRegion prg on prg.C_SalesRegion_ID = srgm.C_SalesRegion_Parent_ID
  118.     left join T_SalesRegionMapping srgm1 on srgm1.C_SalesRegion_ID = prg.C_SalesRegion_ID
  119.     where rg.levelno = 2
  120.     order by divisionName
  121. ) division on zone.C_SalesRegion_Parent_ID = division.C_SalesRegion_ID
  122.            and terr.AD_Org_ID = cust.ad_org_id
  123.  
  124. left join
  125. (
  126.     select DISTINCT rg.name as hosName, rg.value as hosCode
  127.         ,bp.value as hosoffval, bp.name as hosOfficer
  128.         ,srgm.C_SalesRegion_Parent_ID, rg.C_SalesRegion_ID
  129.         ,emp.name as employee, emp.value as hosEmpID
  130.         ,desig.name as hosDesig
  131.     from C_SalesRegion rg
  132.     left join T_SupervisorAssignment cs on rg.C_SalesRegion_ID = cs.C_SalesRegion_ID
  133.         and (cs.datestart <= $P{dd2}::date and (cs.datefinish is null or cs.datefinish >= $P{dd2}::date))
  134.     left join C_BPartner bp on bp.C_BPartner_ID = cs.C_BPartner_ID
  135.     left join HR_Employee emp on bp.C_BPartner_ID = emp.C_BPartner_ID
  136.     left join HR_Job desig on emp.HR_Job_ID = desig.HR_Job_ID
  137.     left join T_SalesRegionMapping srgm on srgm.C_SalesRegion_ID = rg.C_SalesRegion_ID
  138.     left join C_SalesRegion prg on prg.C_SalesRegion_ID = srgm.C_SalesRegion_Parent_ID
  139.     left join T_SalesRegionMapping srgm1 on srgm1.C_SalesRegion_ID = prg.C_SalesRegion_ID
  140.     where rg.levelno = 1
  141.     order by hosName
  142. ) hos on division.C_SalesRegion_Parent_ID = hos.C_SalesRegion_ID
  143.  
  144. LEFT JOIN LATERAL (
  145.     select da.m_warehouse_id
  146.     from T_WH_SRAssignment da
  147.     where da.c_salesregion_id = cust.terr_id
  148.       and $P{dd2}::date >= da.datestart
  149.     order by da.datestart desc limit 1
  150. ) da on true
  151.  
  152. JOIN
  153. (
  154.     SELECT
  155.         CASE WHEN order_bp_id IS NOT NULL THEN order_bp_id
  156.              WHEN inv_bp_id IS NOT NULL THEN inv_bp_id
  157.              ELSE pay_bp_id END AS c_bpartner_id
  158.         ,CASE WHEN order_org_id IS NOT NULL THEN order_org_id
  159.               WHEN inv_org_id IS NOT NULL THEN inv_org_id
  160.               ELSE pay_org_id END AS ad_org_id
  161.         ,order_amt
  162.         ,inv_amt
  163.         ,pay_amt
  164.     FROM
  165.     (
  166.         -- -------- ORDER BLOCK --------
  167.         SELECT co.ad_org_id as order_org_id
  168.             ,cb.c_bpartner_id as order_bp_id
  169. --            ,max(aprv.approved_date) as approved_date
  170.             ,sum(co.grandtotal) as order_amt
  171.         FROM c_order co
  172.         JOIN c_bpartner cb on cb.c_bpartner_id = co.c_bpartner_id
  173.  
  174.         LEFT JOIN LATERAL
  175.         (
  176.             select max(case when oldvalue='AP' and newValue='CO' then chl.updated end) as approved_date
  177.                 ,max(case when oldvalue='AP' and newValue='CO' then
  178.                     (case when usr.C_BPartner_ID > 0
  179.                           then (select name from C_BPartner where C_BPartner_ID = usr.C_BPartner_ID)
  180.                           else usr.name end)
  181.                     end) as approvedBY
  182.             from AD_ChangeLog chl
  183.             join AD_User usr on usr.AD_User_ID = chl.updatedBy
  184.             where AD_Table_ID = 259 and AD_Column_ID = 2170 and record_id = co.c_order_id
  185.         ) aprv on (1=1)
  186.         WHERE co.isSotrx = 'Y'
  187.           AND co.docstatus IN ('CO','CL')
  188.           AND (aprv.approved_date::date BETWEEN $P{dd1}::date AND $P{dd2}::date)
  189.         GROUP BY co.ad_org_id,cb.c_bpartner_id
  190.     ) orderinfo
  191.  
  192.     FULL JOIN
  193.  
  194.     (
  195.         -- -------- INVOICE BLOCK --------
  196.         SELECT ci.ad_org_id as inv_org_id
  197.             ,cb.c_bpartner_id as inv_bp_id
  198.             ,sum(ci.grandtotal) as inv_amt
  199.         FROM c_invoice ci
  200.         JOIN c_bpartner cb on cb.c_bpartner_id = ci.c_bpartner_id
  201.         WHERE ci.isSotrx = 'Y'
  202.           AND ci.docstatus IN ('CO','CL')
  203.           AND ci.c_order_id IS NOT NULL
  204.           AND (ci.dateacct::date BETWEEN $P{dd1}::date AND $P{dd2}::date)
  205.         GROUP BY ci.ad_org_id, cb.c_bpartner_id
  206.     ) invinfo
  207.       ON invinfo.inv_org_id = orderinfo.order_org_id
  208.      AND invinfo.inv_bp_id = orderinfo.order_bp_id
  209.  
  210.     FULL JOIN
  211.  
  212.     (
  213.         -- -------- PAYMENT BLOCK --------
  214.         SELECT cp.ad_org_id as pay_org_id
  215.             ,cb.c_bpartner_id as pay_bp_id
  216.             ,sum(cp.payamt) as pay_amt
  217.         FROM c_payment cp
  218.         JOIN c_bpartner cb on cb.c_bpartner_id = cp.c_bpartner_id
  219.         WHERE cp.isreceipt = 'Y'
  220.           AND cp.docstatus IN ('CO','CL')
  221.           AND (cp.dateacct::date BETWEEN $P{dd1}::date AND $P{dd2}::date)
  222.         GROUP BY cp.ad_org_id, cb.c_bpartner_id
  223.     ) payinfo
  224.       ON payinfo.pay_org_id = orderinfo.order_org_id
  225.      AND payinfo.pay_bp_id = orderinfo.order_bp_id
  226.  
  227. ) main_data ON main_data.c_bpartner_id = cust.c_bpartner_id
  228.  
  229. WHERE ($P{Territory_ID} IS NULL OR terr.C_SalesRegion_ID = $P{Territory_ID})
  230.   AND ($P{Zone_ID} IS NULL OR zone.C_SalesRegion_ID = $P{Zone_ID})
  231.   AND ($P{Division_ID} IS NULL OR division.C_SalesRegion_ID = $P{Division_ID})
  232.   AND ($P{C_DocType_ID}::integer IS NULL OR cd.c_doctype_id = $P{C_DocType_ID}::integer)
  233.  
  234. --GROUP BY
  235. --     cust.c_bpartner_id, cust.customer_code, cust.customer_name, cust.customer_address, cust.customer_phone
  236. --    , prod_segment.segment_id, prod_segment.segment_name, cust.district, cust.thana
  237. --    ,cust.ad_org_id, cust.terr_id
  238. --    ,terr.terrName, terr.terrCode, terr.terroffval, terr.terrOfficer, terr.C_SalesRegion_Parent_ID
  239. --    ,terr.C_SalesRegion_ID, terr.AD_Org_ID
  240. --    ,zone.zoneName, zone.zoneCode, zone.zoneoffval, zone.zoneOfficer, zone.C_SalesRegion_ID
  241. --    ,division.divisionName, division.divisionCode, division.divisionoffval, division.divisionOfficer, division.C_SalesRegion_ID
  242. --    ,hos.hosName, hos.hosCode, hos.hosoffval, hos.hosOfficer, hos.hosEmpID, hos.hosDesig
  243. --    ,da.m_warehouse_id,inv_amt,pay_amt,customerType.name
  244. ----    ,main_data.approved_date
  245.  
  246. ORDER BY cust.customer_name;
Success #stdin #stdout #stderr 0s 5308KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: near line 2: near "','": syntax error
https://ideone.com/Wq0lRX
language:
SQLite (sqlite 3.27.2)
created:
14 hours ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!