abirama62

get_data_harga_jual_vs_hppa_vs_harga_beli

Jul 27th, 2021 (edited)
250
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. SASA-551
  3. 1. [OK] Kode produk
  4. 2. [OK] Nama produk
  5. 3. [OK] Sell price grosir
  6. 4. [OK] sell price HET
  7. 5. [OK] sell price end user
  8. 6. [OK] sell price ojol
  9. 7. [OK] sell price bengkel
  10. 8. [OK] sell price retail
  11. 9. [OK] sell price retail 2
  12. 10. [OK] sell price prosas
  13. 11. [OK] purch price DPP
  14. 12. [OK] avg price (hppa)
  15. */
  16.  
  17. CREATE TABLE tt_data_sell_price_vs_hppa_purch_price (
  18.         product_id bigint,
  19.         uom_id bigint,
  20.         grosir numeric default 0,
  21.         het numeric default 0,
  22.         enduser numeric default 0,
  23.         ojol numeric default 0,
  24.         bengkel numeric default 0,
  25.         retail numeric default 0,
  26.         retail2 numeric default 0,
  27.         prosas numeric default 0,
  28.         purch_price numeric default 0,
  29.         average_price numeric default 0,
  30.         active character varying default ''
  31. );
  32.  
  33. --DELETE FROM tt_data_sell_price_vs_hppa_purch_price;
  34.  
  35. --grosir
  36. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,grosir,active)
  37.     select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
  38.     from m_product A
  39.     inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
  40.     WHERE price_level = 'GROSIR'
  41.     AND '20210630' BETWEEN B.date_from AND B.date_to
  42.     AND B.ou_id = 14;
  43.  
  44. --het
  45. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,het,active)
  46.     select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
  47.     from m_product A
  48.     inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
  49.     WHERE price_level = 'HET'
  50.     AND '20210630' BETWEEN B.date_from AND B.date_to
  51.     AND B.ou_id = 10;
  52.  
  53. --enduser
  54. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,enduser,active)
  55.     select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
  56.     from m_product A
  57.     inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
  58.     WHERE price_level = 'ENDUSER'
  59.     AND '20210630' BETWEEN B.date_from AND B.date_to
  60.     AND B.ou_id = 10;
  61.    
  62. --ojol
  63. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,ojol,active)
  64.     select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
  65.     from m_product A
  66.     inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
  67.     WHERE price_level = 'OJOL'
  68.     AND '20210630' BETWEEN B.date_from AND B.date_to
  69.     AND B.ou_id = 10;
  70.    
  71. --retail
  72. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,retail,active)
  73.     select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
  74.     from m_product A
  75.     inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
  76.     WHERE price_level = 'RETAIL'
  77.     AND '20210630' BETWEEN B.date_from AND B.date_to
  78.     AND B.ou_id = 10;
  79.  
  80. --retail2
  81. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,retail2,active)
  82.     select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
  83.     from m_product A
  84.     inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
  85.     WHERE price_level = 'RETAIL2'
  86.     AND '20210630' BETWEEN B.date_from AND B.date_to
  87.     AND B.ou_id = 10;
  88.  
  89. --bengkel
  90. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,bengkel,active)
  91.     select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
  92.     from m_product A
  93.     inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
  94.     WHERE price_level = 'BENGKEL'
  95.     AND '20210630' BETWEEN B.date_from AND B.date_to
  96.     AND B.ou_id = 10;
  97.  
  98. --prosas
  99. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,prosas,active)
  100.     select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
  101.     from m_product A
  102.     inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
  103.     WHERE price_level = 'PROSAS'
  104.     AND '20210630' BETWEEN B.date_from AND B.date_to
  105.     AND B.ou_id = 10;
  106.  
  107. --average_price
  108. WITH get_data_avg_sale AS (
  109.     -- sales order
  110.     SELECT B.product_id, C.base_uom_id, (B.nett_sell_price) AS sell_price
  111.     --SELECT A.*
  112.     FROM sl_so A
  113.     INNER JOIN sl_so_item B ON A.so_id = B.so_id
  114.     INNER JOIN m_product C ON B.product_id = C.product_id
  115.     WHERE LEFT(A.doc_date, 6) = '202106'
  116.     AND A.status_doc IN ('F', 'R')
  117.     AND C.active = 'Y'
  118.     UNION ALL
  119.     --pos
  120.     SELECT B.product_id, C.base_uom_id, B.nett_sell_price AS sell_price
  121.     FROM i_trx_pos A
  122.     INNER JOIN i_trx_pos_item B ON A.trx_pos_id = B.trx_pos_id AND A.tenant_id = B.tenant_id AND A.process_no = B.process_no
  123.     INNER JOIN m_product C ON B.product_id = C.product_id
  124.     WHERE LEFT(A.doc_date, 6) = '202106'
  125.     AND A.status = 'S'
  126.     AND C.active = 'Y'
  127. )
  128. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,average_price)
  129. SELECT product_id, base_uom_id, AVG(sell_price)
  130. FROM get_data_avg_sale
  131. GROUP BY product_id, base_uom_id;
  132.    
  133. --purch price
  134. insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,purch_price,active)
  135. SELECT A.product_id, B.base_uom_id, ROUND(A.gl_amount/qty, 2), B.active_datetime
  136.     FROM in_summary_monthly_amount A
  137.     INNER JOIN m_product B ON A.product_id = B.product_id
  138.     WHERE A.date_year_month = '202106'
  139.     AND A.doc_type_id = 111
  140.     AND B.active = 'Y';
  141.  
  142.  
  143. SELECT B.product_code AS kode_produk,
  144.     B.product_name AS nama_produk ,f_get_uom_name(A.uom_id) satuan,
  145.     SUM(grosir) grosir ,
  146.     SUM(het) het ,
  147.     SUM(enduser) enduser ,
  148.     SUM(ojol) enduser ,
  149.     SUM(bengkel) bengel ,
  150.     SUM(retail) retail ,
  151.     SUM(retail2) retail2 ,
  152.     SUM(prosas) prosas ,
  153.     SUM(purch_price) purch_price ,
  154.     SUM(average_price) average_price
  155.     FROM tt_data_sell_price_vs_hppa_purch_price A
  156.     INNER JOIN m_product B ON A.product_id = B.product_id
  157.     WHERE B.active = 'Y'
  158.     GROUP BY B.product_code, B.product_name, A.uom_id
  159.     ORDER BY nama_produk;
RAW Paste Data