abirama62

Step-step query OA aktif

Feb 16th, 2021 (edited)
260
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --1. Init temp table
  2. CREATE TABLE tt_data_balance_invoice_20210216
  3. (
  4.     so_balance_invoice_id bigint,
  5.     ou_id bigint,
  6.     partner_id bigint,
  7.     so_id bigint,
  8.     ref_doc_type_id bigint,
  9.     ref_id bigint,
  10.     ref_doc_no character varying(30),
  11.     ref_doc_date character varying(8),
  12.     ref_item_id bigint,
  13.     invoice_id bigint,
  14.     invoice_doc_no character varying(30),
  15.     invoice_doc_date character varying(8),
  16.     product_id bigint,
  17.     group_brand_id bigint
  18. )
  19. WITH (
  20.   OIDS=FALSE
  21. );
  22.  
  23. --2. Insert data ke temp table
  24. WITH data_balance_invoice AS (
  25.     SELECT A.so_balance_invoice_id,
  26.     A.ou_id, A.partner_id, A.so_id, A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.ref_item_id,
  27.     A.invoice_id, B.doc_no, B.doc_date, C.product_id
  28.     --SELECT *
  29.     FROM sl_so_balance_invoice A
  30.     INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
  31.     INNER JOIN sl_do_item C ON A.ref_id = C.do_id AND A.ref_item_id = C.do_item_id
  32.     WHERE A.ref_doc_type_id IN (311) -- data SO/SI
  33.     AND B.doc_date BETWEEN '20201001' AND '20210131'
  34. ) INSERT INTO tt_data_balance_invoice_20210216
  35. SELECT A.*, C.group_brand_id
  36. FROM data_balance_invoice A
  37. INNER JOIN m_product B ON A.product_id = B.product_id
  38. INNER JOIN m_product_custom_for_sasa C ON B.product_id = C.product_id AND B.tenant_id = C.tenant_id
  39.  
  40. --3. Keterangan terkait kelompok barang (group barang) dari user:
  41. /*
  42. group_brand_code (group_brand_id)
  43. Group Brand FB:
  44. 1. FB2 (91)
  45. 2. FB4 (83)
  46. 3. FBL (146)
  47.  
  48. Group Brand FE:
  49. 1. FE2 (82)
  50. 2. FE4 (84)
  51. 3. FEX (100)
  52.  
  53. Group Brand FL:
  54. 1. FL2 (130)
  55. 2. FL4 (131)
  56. */
  57.  
  58. --4. Init table temp final
  59. CREATE TABLE tt_outlet_aktif_20210216
  60. (
  61.     ou_id bigint
  62.     ou_code character varying(10),
  63.     partner_id bigint,
  64.     partner_code character varying(30),
  65.     partner_name character varying(1024),
  66.     address1 text,
  67.     city character varying(100),
  68.     oa_be bigint,
  69.     oa_fx bigint,
  70.     oa bigint
  71. )
  72. WITH (
  73.   OIDS=FALSE
  74. );
  75.  
  76. --5. Insert data ke temp table per data OU dan customer
  77. INSERT INTO tt_outlet_aktif_20210216
  78. SELECT A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city, 0 AS oa_be, 0 AS oa_fx, 0 AS oa
  79.     FROM tt_data_balance_invoice_20210216 A
  80.     INNER JOIN t_ou B ON A.ou_id = B.ou_id
  81.     INNER JOIN m_partner C ON A.partner_id = C.partner_id
  82.     INNER JOIN m_partner_address D ON C.partner_id = D.partner_id AND D.flg_official = 'Y'
  83.     GROUP BY A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
  84.  
  85.  
  86. --6. Update kolom oa_be
  87. WITH data_oa_be AS (
  88.     SELECT A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
  89.     FROM tt_data_balance_invoice_20210216 A
  90.     INNER JOIN t_ou B ON A.ou_id = B.ou_id
  91.     INNER JOIN m_partner C ON A.partner_id = C.partner_id
  92.     INNER JOIN m_partner_address D ON C.partner_id = D.partner_id AND D.flg_official = 'Y'
  93.     WHERE A.group_brand_id IN (91, 83, 146, 82, 84, 100, 130, 131)
  94.     GROUP BY A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
  95. )
  96. UPDATE tt_outlet_aktif_20210216 SET oa_be = 1
  97. WHERE EXISTS(
  98.     SELECT 1
  99.     FROM data_oa_be A
  100.     WHERE A.ou_id = tt_outlet_aktif_20210216.ou_id
  101.     AND A.partner_id = tt_outlet_aktif_20210216.partner_id
  102. )
  103.  
  104. --7. Update kolom oa_fx
  105. WITH data_oa_be AS (
  106.     SELECT A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
  107.     FROM tt_data_balance_invoice_20210216 A
  108.     INNER JOIN t_ou B ON A.ou_id = B.ou_id
  109.     INNER JOIN m_partner C ON A.partner_id = C.partner_id
  110.     INNER JOIN m_partner_address D ON C.partner_id = D.partner_id AND D.flg_official = 'Y'
  111.     WHERE A.group_brand_id NOT IN (91, 83, 146, 82, 84, 100, 130, 131)
  112.     GROUP BY A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
  113. )
  114. UPDATE tt_outlet_aktif_20210216 SET oa_fx = 1
  115. WHERE EXISTS(
  116.     SELECT 1
  117.     FROM data_oa_be A
  118.     WHERE A.ou_id = tt_outlet_aktif_20210216.ou_id
  119.     AND A.partner_id = tt_outlet_aktif_20210216.partner_id
  120. )
  121.  
  122. --8. Update kolom oa
  123. UPDATE tt_outlet_aktif_20210216 SET oa = 1
  124. WHERE tt_outlet_aktif_20210216.oa_fx = 1 OR tt_outlet_aktif_20210216.oa_be = 1
  125.  
  126. --Get data OA BE
  127. -- Partner (customer) yang pernah transaksi untuk kelompok barang FB, FE dan FL
  128.  
  129. -- Get data OA FX
  130. -- Partner (customer) yang pernah transaksi untuk kelompok barang selain FB, FE dan FL
  131.  
  132. Cek data
  133. WITH data_balance_invoice AS (
  134.     SELECT A.so_balance_invoice_id,
  135.     A.ou_id, A.partner_id, A.so_id, A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.ref_item_id,
  136.     A.invoice_id, B.doc_no, B.doc_date, C.product_id
  137.     --SELECT *
  138.     FROM sl_so_balance_invoice A
  139.     INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
  140.     INNER JOIN sl_do_item C ON A.ref_id = C.do_id AND A.ref_item_id = C.do_item_id
  141.     WHERE A.ref_doc_type_id IN (311) -- data SO/SI
  142.     AND B.doc_date BETWEEN '20201001' AND '20210131'
  143. ) SELECT A.*, C.group_brand_id
  144. FROM data_balance_invoice A
  145. WHERE partner_id = vPartnerId AND A.ou_id = vOuId
  146. INNER JOIN m_product B ON A.product_id = B.product_id
  147. INNER JOIN m_product_custom_for_sasa C ON B.product_id = C.product_id AND B.tenant_id = C.tenant_id
  148.  
  149. 1. vPartnerId dan vOuId diisi dengan partnerId dan ouId yang ingin dibandingkan
  150. 2. Result dari query diatas, cek kolom group_brand_id, apakah termasuk kelompok FB, FE, atau FL,
  151. 3. Dari pengecekan poin 2, bandingkan dengan hasil di excel. Apakah kolom OA BE, OA FX, dan OA sudah sesuai nilainya
RAW Paste Data