abirama62

query_get_customer_omzet

Jun 1st, 2021
973
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- 1. prepare temp table
  2. --   1. temp table utama
  3. DROP TABLE IF EXISTS tr_data_omzet_toko_detail;
  4. CREATE TABLE tr_data_omzet_toko_detail
  5. (
  6.   ou_id bigint,
  7.   ou_code character varying(100),
  8.   partner_id bigint,
  9.   partner_code character varying(100),
  10.   product_id bigint,
  11.   product_code character varying(100),
  12.   product_group_brand character varying(100),
  13.   year_month character varying(6),
  14.   qty numeric,
  15.   gross_amount numeric,
  16.   nett_amount numeric,
  17.   tax_amount numeric
  18. )
  19. WITH (
  20.   OIDS=FALSE
  21. );
  22.  
  23. --   2. temp table sum per parent group brand
  24. DROP TABLE IF EXISTS tr_data_omzet_toko_group_brand;
  25. CREATE TABLE tr_data_omzet_toko_group_brand
  26. (
  27.   ou_id bigint,
  28.   partner_id bigint,
  29.   year_month character varying(6),
  30.   group_brand_data character varying(5),
  31.   gross_amount numeric,
  32.   nett_amount numeric,
  33.   tax_amount numeric
  34. )
  35. WITH (
  36.   OIDS=FALSE
  37. );
  38.  
  39.  
  40. DELETE FROM tr_data_omzet_toko_detail;
  41. DELETE FROM tr_data_omzet_toko_group_brand;
  42.  
  43. -- 2. get data trx (berdasarkan stock date)
  44. --   1. POS (non nempil)
  45. INSERT INTO tr_data_omzet_toko_detail(
  46.     ou_id, ou_code, partner_id, partner_code,
  47.     product_id, product_code, product_group_brand, year_month,
  48.     qty, gross_amount, nett_amount, tax_amount
  49. ) SELECT B.ou_id, B.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
  50.     G.product_id, G.product_code AS product_code, Q.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
  51.     F.qty, F.qty * F.gross_sell_price AS omzet_bruto_amount, F.nett_amount_item AS nett_item_amount, F.tax_amount
  52. FROM i_trx_pos A
  53.     INNER JOIN t_ou B on A.ou_id = B.ou_id
  54.     INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
  55.     INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
  56.     INNER JOIN m_product G on F.product_id = G.product_id
  57.     LEFT JOIN m_product_custom_for_sasa P on G.product_id = P.product_id
  58.     LEFT JOIN m_group_brand Q ON P.group_brand_id = Q.group_brand_id
  59.     WHERE A.tenant_id = 10
  60.         --AND A.ou_id = 15
  61.         AND A.doc_date BETWEEN '20210501' AND '20210531';
  62.  
  63. --   2. POS Void (non nempil)
  64. INSERT INTO tr_data_omzet_toko_detail(
  65.     ou_id, ou_code, partner_id, partner_code,
  66.     product_id, product_code, product_group_brand, year_month,
  67.     qty, gross_amount, nett_amount, tax_amount
  68. ) SELECT B.ou_id, B.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
  69.     G.product_id, G.product_code AS product_code, Q.group_brand_code, SUBSTRING(E.doc_date, 1, 6) AS year_month,
  70.     (F.qty)*-1, (F.qty*-1)* F.gross_sell_price AS omzet_bruto_amount, F.nett_amount_item*(-1)AS nett_item_amount, F.tax_amount*(-1)
  71. FROM i_trx_pos A
  72.     INNER JOIN t_ou B on A.ou_id = B.ou_id
  73.     INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
  74.     INNER JOIN i_trx_log_voided_pos_custom E ON A.doc_no = E.doc_no AND A.ou_id = E.ou_id AND A.tenant_id = E.tenant_id
  75.     INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
  76.     INNER JOIN m_product G on F.product_id = G.product_id
  77.     INNER JOIN m_product_custom_for_sasa P on G.product_id = P.product_id
  78.     INNER JOIN m_group_brand Q ON P.group_brand_id = Q.group_brand_id
  79.     WHERE A.status = 'V'
  80.         AND A.tenant_id = 10
  81.         AND E.doc_date BETWEEN '20210501' AND '20210531';
  82.  
  83. --   3. Penjualan (dari DO)
  84. INSERT INTO tr_data_omzet_toko_detail(
  85.     ou_id, ou_code, partner_id, partner_code,
  86.     product_id, product_code, product_group_brand, year_month,
  87.     qty, gross_amount, nett_amount, tax_amount
  88. ) SELECT B.ou_id, B.ou_code, Q.partner_id, COALESCE(f_get_partner_code(Q.partner_id), 'UMUM') AS partner_code,
  89.     E.product_id, E.product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
  90.     SUM(J.qty_dlv_so) AS qty,
  91.     SUM((J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) + I.tax_amount) AS omzet_bruto_amount,
  92.     SUM(J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) AS nett_item_amount,
  93.     SUM(I.tax_amount) AS tax_amount
  94. FROM sl_do A
  95.     INNER JOIN t_ou B ON A.ou_id = B.ou_id
  96.     INNER JOIN sl_do_item C ON A.do_id = C.do_id
  97.     INNER JOIN m_product E ON C.product_id = E.product_id
  98.     INNER JOIN sl_so_item H ON C.ref_id = H.so_item_id
  99.     INNER JOIN sl_so_balance_invoice J ON J.ref_doc_type_id = A.doc_type_id
  100.            AND J.ref_id = A.do_id
  101.            AND J.ref_item_id = C.do_item_id
  102.            AND J.do_receipt_item_id = -99
  103.     INNER JOIN sl_so_balance_invoice_tax I ON I.ref_doc_type_id = A.doc_type_id
  104.            AND I.ref_id = A.do_id
  105.            AND I.ref_item_id = C.do_item_id
  106.            AND I.do_receipt_item_id = -99
  107.     INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
  108.     INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
  109.     INNER JOIN sl_so Q ON J.so_id = Q.so_id
  110.     WHERE A.tenant_id = 10
  111.     AND A.status_doc = 'R'
  112.     AND A.doc_date BETWEEN '20210501' AND '20210531'
  113.     AND A.doc_type_id = 311 -- docType DO
  114.     GROUP BY B.ou_id, B.ou_code, A.doc_date, Q.partner_id, E.product_id, E.product_code, P.group_brand_code
  115.     HAVING SUM(J.qty_dlv_so) <> 0;
  116.  
  117. --   4. Retur (RN, RRS, DOR)
  118. INSERT INTO tr_data_omzet_toko_detail( -- RN
  119.     ou_id, ou_code, partner_id, partner_code,
  120.     product_id, product_code, product_group_brand, year_month,
  121.     qty, gross_amount, nett_amount, tax_amount
  122. ) SELECT I.ou_id, I.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
  123.     E.product_id, E.product_code AS product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
  124.     SUM(C.qty_dlv_so)*(-1) AS qty,
  125.     SUM((C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) + F.tax_amount)*-1,
  126.     SUM(C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0)))*-1,
  127.     SUM(F.tax_amount)*-1 AS tax_amount
  128. FROM in_inventory A
  129.     INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
  130.     INNER JOIN sl_so_balance_invoice C ON A.doc_type_id = C.ref_doc_type_id AND C.ref_id = A.inventory_id  AND B.ref_item_id = C.ref_item_id
  131.     INNER JOIN m_product E ON B.product_id = E.product_id
  132.     INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
  133.     INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
  134.     INNER JOIN sl_so Q ON C.so_id = Q.so_id
  135.     INNER JOIN sl_so_balance_invoice_tax F ON A.inventory_id = F.ref_id AND A.doc_type_id = F.ref_doc_type_id AND B.ref_item_id = F.ref_item_id
  136.     INNER JOIN sl_do_item G ON B.ref_item_id = G.do_item_id
  137.     INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
  138.     INNER JOIN t_ou I ON A.ou_from_id = I.ou_id
  139.     WHERE A.tenant_id = 10
  140.     AND A.status_doc = 'R'
  141.     AND A.doc_date BETWEEN '20210501' AND '20210531'
  142.     AND A.doc_type_id = 502 -- docType RN
  143.     GROUP BY I.ou_id, I.ou_code, A.partner_id, E.product_id, E.product_code, P.group_brand_code, A.doc_date;
  144.  
  145. INSERT INTO tr_data_omzet_toko_detail( -- RN from RRS
  146.     ou_id, ou_code, partner_id, partner_code,
  147.     product_id, product_code, product_group_brand, year_month,
  148.     qty, gross_amount, nett_amount, tax_amount
  149. ) SELECT X.ou_id, X.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
  150.     D.product_id, D.product_code AS product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
  151.     (G.qty_dlv_so)*-1,
  152.     C.gross_sell_price * G.qty_dlv_so * (-1) AS omzet_bruto_amount,
  153.     G.item_amount * (-1) AS nett_item_amount,
  154.     ROUND(((C.tax_percentage / (100.0 + C.tax_percentage)) * C.gross_sell_price) * G.qty_dlv_so * (-1)) AS tax_amount
  155. FROM  in_inventory  A
  156.     INNER JOIN sl_so_balance_invoice G ON a.tenant_id = G.tenant_id
  157.         AND G.ou_id = A.ou_from_id
  158.         AND G.ref_doc_type_id = 502
  159.         AND G.ref_id = A.inventory_id
  160.     INNER JOIN sl_so_balance_invoice_ext_for_return_note B ON G.so_balance_invoice_id = B.so_balance_invoice_id
  161.         AND B.flg_from_rrs = 'Y'
  162.     INNER JOIN sl_request_return_sales_item C ON a.tenant_id = C.tenant_id
  163.         AND B.request_return_sales_item_id = C.request_return_sales_item_id
  164.     INNER JOIN m_product D ON A.tenant_id = D.tenant_id
  165.         AND C.product_id = D.product_id
  166.     INNER JOIN t_ou X ON X.ou_id = A.ou_from_id
  167.     INNER JOIN m_product_custom_for_sasa O ON D.product_id = O.product_id
  168.     INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
  169.     LEFT OUTER JOIN sl_invoice H ON H.invoice_id = G.invoice_id AND G.flg_invoice = 'Y'
  170. WHERE A.tenant_id = 10
  171. AND A.status_doc = 'R'
  172. AND A.doc_date BETWEEN '20210501' AND '20210531'
  173. AND A.ref_doc_type_id= 381 -- RRS
  174. AND A.doc_type_id = 502; -- RN
  175.  
  176. INSERT INTO tr_data_omzet_toko_detail( -- DO Receipt
  177.     ou_id, ou_code, partner_id, partner_code,
  178.     product_id, product_code, product_group_brand, year_month,
  179.     qty, gross_amount, nett_amount, tax_amount
  180. ) SELECT I.ou_id, I.ou_code, Q.partner_id, COALESCE(f_get_partner_code(Q.partner_id), 'UMUM') AS partner_code,
  181. E.product_id, E.product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
  182. SUM(C.qty_dlv_so) AS qty,
  183. SUM((C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) + F.tax_amount) AS omzet_bruto_amount,
  184. SUM(C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) AS nett_item_amount,
  185. SUM(F.tax_amount) AS tax_amount
  186. FROM in_do_receipt A
  187.     INNER JOIN in_do_receipt_item B ON A.do_receipt_id = B.do_receipt_id
  188.     INNER JOIN sl_do_item G ON B.ref_id = G.do_item_id AND G.do_id = A.ref_id
  189.     INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
  190.     INNER JOIN sl_so_balance_invoice C ON B.ref_doc_type_id = C.ref_doc_type_id
  191.                     AND C.ref_id = A.ref_id
  192.                     AND B.ref_id = C.ref_item_id
  193.                     AND C.do_receipt_item_id = B.do_receipt_item_id
  194.     INNER JOIN m_product E ON B.product_id = E.product_id
  195.     INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
  196.     INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
  197.     INNER JOIN sl_so Q ON C.so_id = Q.so_id
  198.     INNER JOIN sl_so_balance_invoice_tax F ON B.ref_doc_type_id = F.ref_doc_type_id
  199.                     AND F.ref_id = A.ref_id
  200.                     AND B.ref_id = F.ref_item_id
  201.                     AND F.do_receipt_item_id = B.do_receipt_item_id
  202.     INNER JOIN t_ou I ON A.ou_id = I.ou_id
  203.     WHERE A.tenant_id = 10
  204.     AND A.status_doc = 'R'
  205.     AND A.doc_date BETWEEN '20210501' AND '20210531'
  206.     AND A.doc_type_id = 526 -- docType DOR
  207.     GROUP BY I.ou_id, I.ou_code, A.doc_date, Q.partner_id,E.product_id, E.product_code, P.group_brand_code;
  208.  
  209.  
  210. --3. kelompokkan berdasarkan parent group brand, masukkan ke tr_data_omzet_toko_group_brand
  211. -- get data product with group_brand_code 2 huruf
  212. INSERT INTO tr_data_omzet_toko_group_brand(
  213.         ou_id, partner_id, year_month, group_brand_data,
  214.         gross_amount, nett_amount, tax_amount
  215. )
  216. SELECT A.ou_id, A.partner_id, A.year_month,
  217.         CASE WHEN LEFT(group_brand_code, 2) = 'FB' THEN 'FB'
  218.             WHEN LEFT(group_brand_code, 2) = 'FE' THEN 'FE'
  219.             WHEN LEFT(group_brand_code, 2) = 'FL' THEN 'FL'
  220.             WHEN LEFT(group_brand_code, 2) = 'FX' THEN 'FX'
  221.         END AS group_brand_data,
  222.         SUM(A.gross_amount), SUM(A.nett_amount), SUM(A.tax_amount)
  223.     FROM tr_data_omzet_toko_detail A
  224.     INNER JOIN m_group_brand B ON A.product_group_brand = B.group_brand_code
  225.     WHERE LEFT(group_brand_code, 2) IN ('FB', 'FE', 'FL', 'FX')
  226.     GROUP BY A.ou_id, A.partner_id, A.year_month, group_brand_data
  227.     ORDER BY ou_id, partner_id, year_month, group_brand_data;
  228.  
  229. -- get data product with group_brand_code 3 huruf
  230. INSERT INTO tr_data_omzet_toko_group_brand(
  231.         ou_id, partner_id, year_month, group_brand_data,
  232.         gross_amount, nett_amount, tax_amount
  233. )
  234. SELECT A.ou_id, A.partner_id, A.year_month,
  235.         CASE WHEN group_brand_code = 'FAB' THEN 'FAB'
  236.              WHEN group_brand_code = 'FA2' THEN 'FA2'
  237.              WHEN group_brand_code = 'FA4' THEN 'FA4'
  238.         END AS group_brand_data,
  239.         SUM(A.gross_amount), SUM(A.nett_amount), SUM(A.tax_amount)
  240.     FROM tr_data_omzet_toko_detail A
  241.     INNER JOIN m_group_brand B ON A.product_group_brand = B.group_brand_code
  242.     WHERE group_brand_code IN ('FAB', 'FA2', 'FA4')
  243.     GROUP BY A.ou_id, A.partner_id, A.year_month, group_brand_data
  244.     ORDER BY ou_id, partner_id, year_month, group_brand_data;
  245.  
  246.  
  247. -- select terakhir
  248. WITH summary_table AS (
  249.     SELECT ou_id, partner_id, year_month,
  250.         CASE WHEN group_brand_data = 'FB' THEN SUM(gross_amount) ELSE 0 END AS omzet_fb,
  251.         CASE WHEN group_brand_data = 'FE' THEN SUM(gross_amount) ELSE 0 END AS omzet_fe,
  252.         CASE WHEN group_brand_data = 'FL' THEN SUM(gross_amount) ELSE 0 END AS omzet_fl,
  253.         CASE WHEN group_brand_data = 'FX' THEN SUM(gross_amount) ELSE 0 END AS omzet_fx,
  254.         CASE WHEN group_brand_data = 'FAB' THEN SUM(gross_amount) ELSE 0 END AS omzet_fab,
  255.         CASE WHEN group_brand_data IN ('FA2', 'FA4') THEN SUM(gross_amount) ELSE 0 END AS omzet_fa
  256.     FROM tr_data_omzet_toko_group_brand
  257.     --WHERE partner_id = 37406
  258.     GROUP BY ou_id, partner_id, year_month, group_brand_data
  259.     ORDER BY ou_id, partner_id, year_month, group_brand_data
  260. ) SELECT B.ou_code, C.partner_code, C.partner_name,
  261.     D.address1, D.address2, D.state_or_province, D.city, D.address3 AS kecamatan, C.price_level,
  262.     COALESCE(E.cp_name, '') AS cp_name, COALESCE(E.phone1, '') AS phone_no, COALESCE(E.cp_identity_no, '') AS ktp_no,
  263.     F.amount_limit AS plafon,
  264.     SUM(omzet_fb) AS omzet_fb, SUM(omzet_fe) AS omzet_fe, SUM(omzet_fl) AS omzet_fl,
  265.     SUM(omzet_fx) AS omzet_fx, SUM(omzet_fab) AS omzet_fab, SUM(omzet_fa) AS omzet_fa
  266. FROM summary_table A
  267. INNER JOIN t_ou B ON A.ou_id = B.ou_id
  268. INNER JOIN m_partner C ON A.partner_id = C.partner_id
  269. INNER JOIN m_partner_address D ON D.partner_id = C.partner_id AND flg_official = 'Y'
  270. LEFT JOIN m_partner_cp E ON A.partner_id = E.partner_id AND E.active = 'Y' AND E.cp_job = 'PIC'
  271. INNER JOIN m_partner_type F ON A.partner_id = F.partner_id
  272. GROUP BY B.ou_code, C.partner_code, C.partner_name, D.address1, D.address2, D.state_or_province, D.city, D.address3, C.price_level,
  273.     E.cp_name, E.phone1, E.cp_identity_no,
  274.     F.amount_limit
  275. ORDER BY B.ou_code, C.partner_code;
  276.  
  277.  
  278.  
RAW Paste Data