abirama62

fix data stock product status

Apr 28th, 2021 (edited)
598
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. SASA-489:
  3. Yang perlu di ubah
  4. 1. update in_product_balance_stock
  5.   1. update qty untuk data yang asli
  6.   1. record yang dobel dihapus (product_status <> 'GOOD')
  7.    
  8. 2. update in_log_product_balance_stock
  9.   1. update product_status
  10. */
  11.  
  12. --Preparation:
  13. --1. backup data table in_product_balance_stock dan in_log_product_balance_stock
  14. CREATE TABLE bak_in_product_balance_stock_20210428 AS
  15. TABLE in_product_balance_stock;
  16.  
  17. CREATE TABLE bak_in_log_product_balance_stock_20210428 AS
  18. TABLE in_log_product_balance_stock;
  19.  
  20. --Langkah update:
  21. --0. buat temp table
  22.     DROP TABLE IF EXISTS temp_table_fix_product_balance_stock;
  23.  
  24.     CREATE TABLE temp_table_fix_product_balance_stock(
  25.         product_balance_stock_id bigint,
  26.         tenant_id bigint,
  27.         warehouse_id bigint,
  28.         product_id bigint,
  29.         product_balance_id bigint,
  30.         product_status character varying(50),
  31.         qty numeric
  32.     );
  33.  
  34. --1. get daftar product di in_product_balance_stock yang status <> 'GOOD'
  35.     SELECT A.*
  36.     FROM in_product_balance_stock A
  37.     WHERE A.product_status <> 'GOOD'
  38.     ORDER BY A.create_datetime; -- (ada 6512 record)
  39.  
  40.    
  41. --2. get daftar product yg dipakai, di in_log_product_balance_stock
  42.     SELECT A.*
  43.     FROM in_log_product_balance_stock A
  44.     WHERE create_datetime > '20210424000000' -- kasusnya terjadi setelah tgl 24 April 2021
  45.     AND warehouse_id IN (20, 15) -- kasusnya hanya untuk WH 2809 dan 2802
  46.     AND product_status <> 'GOOD'
  47.     ORDER BY create_datetime ASC; -- (ada 6512 record)
  48.    
  49. --3. update status product pada in_log_product_balance_stock
  50.     UPDAET in_log_product_balance_stock
  51.     SET product_status = 'GOOD'
  52.     WHERE warehouse_id IN (20, 15)
  53.     AND product_status <> 'GOOD'
  54.     AND create_datetime > '20210424000000';
  55.    
  56. --4. simpan data poin 1 ke temp table
  57.     INSERT INTO temp_table_fix_product_balance_stock(
  58.         product_balance_stock_id, tenant_id, warehouse_id,
  59.         product_id, product_balance_id, product_status, qty
  60.     ) SELECT A.product_balance_stock_id, A.tenant_id, A.warehouse_id,
  61.         A.product_id, A.product_balance_id, A.product_status, A.qty
  62.     FROM in_product_balance_stock A
  63.     WHERE A.product_status <> 'GOOD'
  64.     ORDER BY A.create_datetime;
  65.  
  66. --5. update data in_product_balance_stock
  67.     WITH table_prepare_update AS (
  68.         SELECT A.tenant_id, A.warehouse_id,
  69.         A.product_id, A.product_balance_id, SUM(A.qty) AS qty_add
  70.         FROM temp_table_fix_product_balance_stock A
  71.         GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id
  72.     )
  73.     UPDAXTE in_product_balance_stock A
  74.         SET qty = qty + X.qty_add, version = + 1,
  75.         update_datetime = to_char(NOW(), 'YYYYMMDDHH24MIss'), update_user_id = -1
  76.     FROM table_prepare_update X
  77.     WHERE A.tenant_id = X.tenant_id
  78.     AND A.product_id = X.product_id
  79.     AND A.warehouse_id = B.warehouse_id
  80.     AND A.product_balance_id = X.product_balance_id
  81.     AND A.product_status = 'GOOD';
  82.    
  83. --6. Update data in_product_balance_stock yang
  84. --  1. product status <> 'GOOD'
  85. --  2. tidak ada record lain yang serupa (data product_id, product_balance_id, warehouse_id sama) dengan product_status = 'GOOD'
  86.     WITH table_prepare_update AS (
  87.         SELECT A.product_balance_stock_id, A.tenant_id, A.warehouse_id,
  88.         A.product_id, A.product_balance_id
  89.         FROM temp_table_fix_product_balance_stock A
  90.         WHERE NOT EXISTS (
  91.             SELECT 1
  92.             FROM in_product_balance_stock B
  93.             WHERE A.product_id = B.product_id
  94.             AND A.product_balance_id = B.product_balance_id
  95.             AND A.warehouse_id = B.warehouse_id
  96.             AND A.product_status <> B.product_status
  97.         ) --GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id
  98.     )UPDAXTE in_product_balance_stock A
  99.         SET product_status = 'GOOD', version = + 1,
  100.         update_datetime = to_char(NOW(), 'YYYYMMDDHH24MIss'), update_user_id = -1
  101.     FROM table_prepare_update X
  102.     WHERE A.product_balance_stock_id = X.product_balance_stock_id;
  103.  
  104. --7. Hapus data in_product_balance_stock yang tidak seharusnya ada (product_status <> 'GOOD')
  105.     DELETE FROM in_product_balance_stock A
  106.     WHERE EXISTS (
  107.         SELECT 1
  108.         FROM temp_table_fix_product_balance_stock B
  109.         WHERE A.product_balance_stock_id = B.product_balance_stock_id
  110.     ) AND A.product_status <> 'GOOD';
  111.  
  112. /*
  113. Fakta baru:
  114. 1. Ada satu record in_product_balance_stock dengan status <> 'GOOD', tanpa record lain yang serupa (data product_id, product_balance_id, warehouse_id sama) dengan product_status = 'GOOD'
  115. */
RAW Paste Data