Advertisement
Guest User

FUNGSI UPDATE

a guest
Jul 16th, 2019
112
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- ACTION FOR UPDATE
  2.         IF  EXISTS (SELECT 1 FROM tt_ul_product WHERE upload_header_id = pUploadHeaderId AND action_type = vActionUpdate) THEN
  3.        
  4.             -- Ubah update_status menjadi U jika data digunakan untuk Update
  5.             UPDATE ul_product A SET
  6.                 update_status = vActionUpdate
  7.             WHERE A.upload_header_id = pUploadHeaderId
  8.                 AND EXISTS (
  9.                     SELECT 1 FROM tt_ul_product B
  10.                     WHERE A.kode_produk = B.product_code
  11.                     AND B.tenant_id = vTenantId
  12.                     AND B.action_type = vActionUpdate
  13.                 );
  14.            
  15.             -- REMOVE existing data specs untuk product yang di upload
  16.             DELETE FROM m_product_specs A
  17.             WHERE EXISTS (
  18.                 SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
  19.                     AND Z.upload_header_id = pUploadHeaderId
  20.             );
  21.            
  22.             -- INSERT specs untuk semua product dari upload yang specsnya tidak kosong
  23.             INSERT INTO m_product_specs(
  24.                     product_id, tenant_id, product_specs, create_datetime, create_user_id,
  25.                     update_datetime, update_user_id, version, active, active_datetime,
  26.                     non_active_datetime)
  27.             SELECT A.product_id, A.tenant_id, B.specs, vDatetime, vUserId,
  28.                     vDatetime, vUserId, 0, vYes, vDatetime, vSpaceValue
  29.             FROM tt_ul_product A
  30.             INNER JOIN ul_product B ON A.product_code = B.kode_produk
  31.             WHERE A.upload_header_id = pUploadHeaderId
  32.             AND A.upload_header_id=B.upload_header_id
  33.             AND A.action_type = vActionUpdate
  34.             AND TRIM(B.specs)<>vEmpty;
  35.            
  36.             -- UPDATE product custom
  37.             UPDATE m_product_custom A SET
  38.                 style_product=C.golongan_product,
  39.                 color=C.color,
  40.                 size=C.size,
  41.                 update_datetime=vDatetime,
  42.                 update_user_id=vUserId,
  43.                 version=A.version+1
  44.             FROM tt_ul_product B
  45.             INNER JOIN ul_product C ON B.product_code = C.kode_produk AND B.tenant_id = vTenantId
  46.             WHERE B.upload_header_id = pUploadHeaderId
  47.             AND B.upload_header_id=C.upload_header_id
  48.             AND A.product_id = B.product_id
  49.             AND B.action_type = vActionUpdate;
  50.            
  51.             -- UPDATE product custom for dlg
  52.             UPDATE m_product_custom_for_dlg A SET
  53.                 supplier_id=D.partner_id,
  54.                 update_datetime=vDatetime,
  55.                 update_user_id=vUserId,
  56.                 version=A.version+1
  57.             FROM tt_ul_product B
  58.             INNER JOIN ul_product C ON B.product_code = C.kode_produk
  59.             INNER JOIN m_partner D ON C.kode_supplier = D.partner_code AND D.tenant_id = B.tenant_id
  60.             INNER JOIN m_partner_type E ON D.partner_id = E.partner_id
  61.             WHERE B.upload_header_id = pUploadHeaderId
  62.             AND B.upload_header_id=C.upload_header_id
  63.             AND A.product_id = B.product_id
  64.             AND B.action_type = vActionUpdate
  65.             AND E.group_partner = 'S';
  66.            
  67.             -- UPDATE product weight dimension
  68.             UPDATE m_product_weight_dimension A SET
  69.                 weight=C.weight_in_kg::numeric,
  70.                 dimension_length=C.length_in_cm::numeric,
  71.                 dimension_width=C.width_in_cm::numeric,
  72.                 dimension_height=C.height_in_cm::numeric,
  73.                 update_datetime=vDatetime,
  74.                 update_user_id=vUserId,
  75.                 version=A.version+1
  76.             FROM tt_ul_product B
  77.             INNER JOIN ul_product C ON B.product_code = C.kode_produk  
  78.             WHERE B.upload_header_id = pUploadHeaderId
  79.             AND B.upload_header_id=C.upload_header_id
  80.             AND A.product_id = B.product_id
  81.             AND B.action_type = vActionUpdate;
  82.            
  83.             -- REMOVE EXISTING PRODUCT KEYWORD BY PRODUCT YANG DI UPDATE
  84.             DELETE FROM m_product_keyword A
  85.             WHERE EXISTS (
  86.                 SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
  87.             );
  88.            
  89.             -- INSERT PRODUCT KEYWORD BARU
  90.             INSERT INTO m_product_keyword(
  91.                 product_id, tenant_id, keyword, create_datetime,
  92.                 create_user_id, update_datetime, update_user_id, version, active,
  93.                 active_datetime, non_active_datetime)
  94.             SELECT A.product_id, A.tenant_id, LTRIM(unnest(string_to_array(B.keywords, ','))) AS keywords, vDatetime,
  95.                 vUserId, vDatetime, vUserId, 0, vYes,
  96.                 vDatetime, vSpaceValue
  97.             FROM tt_ul_product A
  98.             INNER JOIN ul_product B ON A.product_code = B.kode_produk
  99.             WHERE A.upload_header_id = pUploadHeaderId
  100.             AND A.upload_header_id=B.upload_header_id
  101.             AND A.action_type = vActionUpdate
  102.             AND TRIM(B.keywords)<>vEmpty;
  103.            
  104.             -- REMOVE DATA BARCODE untuk semua product yang diupload
  105.             DELETE FROM m_product_barcode A
  106.             WHERE EXISTS (
  107.                 SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
  108.             );
  109.            
  110.             -- INSERT DATA BARCODE untuk semua product yang diupload
  111.             INSERT INTO m_product_barcode(
  112.                 product_id, tenant_id, barcode, create_datetime, create_user_id,
  113.                 update_datetime, update_user_id, version, active, active_datetime,
  114.                 non_active_datetime)
  115.             SELECT A.product_id, A.tenant_id, B.barcode, vDatetime, vUserId,
  116.                 vDatetime, vUserId, 0, vYes, vDatetime,
  117.                 vSpaceValue
  118.             FROM tt_ul_product A
  119.             INNER JOIN ul_product B ON A.product_code = B.kode_produk
  120.             WHERE A.upload_header_id = pUploadHeaderId
  121.             AND A.upload_header_id=B.upload_header_id
  122.             AND A.action_type = vActionUpdate
  123.             AND TRIM(B.barcode)<>vEmpty;
  124.            
  125.             -- REMOVE EXISTING PRODUCT UOM CNV BY PRODUCT YANG DI UPDATE
  126.             DELETE FROM m_product_uom_cnv A
  127.             WHERE EXISTS (
  128.                 SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
  129.             );
  130.            
  131.             -- INSERT PRODUCT UOM CNV ATAS UOM 1
  132.             INSERT INTO m_product_uom_cnv(
  133.                     product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
  134.                     amount_uom_to, create_datetime, create_user_id, update_datetime,
  135.                     update_user_id, version, active, active_datetime, non_active_datetime)
  136.             SELECT A.product_id, A.tenant_id, A.uom_id_1, 1, A.base_uom_id,
  137.                     B.conv_uom_1_to_base::numeric, vDatetime, vUserId, vDatetime,
  138.                     vUserId, 0, vYes, vDatetime, vSpaceValue
  139.             FROM tt_ul_product A
  140.             INNER JOIN ul_product B ON A.product_code = B.kode_produk
  141.             WHERE A.upload_header_id = pUploadHeaderId
  142.             AND A.upload_header_id=B.upload_header_id
  143.             AND A.action_type = vActionUpdate
  144.             AND TRIM(B.conv_uom_1_to_base)<>vEmpty
  145.             AND is_numeric(B.conv_uom_1_to_base)
  146.             AND B.conv_uom_1_to_base::numeric > 0;
  147.            
  148.             -- INSERT PRODUCT UOM CNV ATAS UOM 2
  149.             INSERT INTO m_product_uom_cnv(
  150.                     product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
  151.                     amount_uom_to, create_datetime, create_user_id, update_datetime,
  152.                     update_user_id, version, active, active_datetime, non_active_datetime)
  153.             SELECT A.product_id, A.tenant_id, A.uom_id_2, 1, A.base_uom_id,
  154.                     B.conv_uom_2_to_base::numeric, vDatetime, vUserId, vDatetime,
  155.                     vUserId, 0, vYes, vDatetime, vSpaceValue
  156.             FROM tt_ul_product A
  157.             INNER JOIN ul_product B ON A.product_code = B.kode_produk
  158.             WHERE A.upload_header_id = pUploadHeaderId
  159.             AND A.upload_header_id=B.upload_header_id
  160.             AND A.action_type = vActionUpdate
  161.             AND TRIM(B.conv_uom_2_to_base)<>vEmpty
  162.             AND is_numeric(B.conv_uom_2_to_base)
  163.             AND B.conv_uom_2_to_base::numeric > 0;
  164.            
  165.             -- INSERT PRODUCT UOM CNV ATAS UOM 3
  166.             INSERT INTO m_product_uom_cnv(
  167.                     product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
  168.                     amount_uom_to, create_datetime, create_user_id, update_datetime,
  169.                     update_user_id, version, active, active_datetime, non_active_datetime)
  170.             SELECT A.product_id, A.tenant_id, A.uom_id_3, 1, A.base_uom_id,
  171.                     B.conv_uom_3_to_base::numeric, vDatetime, vUserId, vDatetime,
  172.                     vUserId, 0, vYes, vDatetime, vSpaceValue
  173.             FROM tt_ul_product A
  174.             INNER JOIN ul_product B ON A.product_code = B.kode_produk
  175.             WHERE A.upload_header_id = pUploadHeaderId
  176.             AND A.upload_header_id=B.upload_header_id
  177.             AND A.action_type = vActionUpdate
  178.             AND TRIM(B.conv_uom_3_to_base)<>vEmpty
  179.             AND is_numeric(B.conv_uom_3_to_base)
  180.             AND B.conv_uom_3_to_base::numeric > 0;
  181.            
  182.             -- Update product group brand
  183.             UPDATE m_product_group_brand A SET
  184.                 group_brand_product=C.kode_group_brand,
  185.                 update_datetime=vDatetime,
  186.                 update_user_id=vUserId,
  187.                 version=A.version+1
  188.             FROM tt_ul_product B
  189.             INNER JOIN ul_product C ON B.product_code = C.kode_produk
  190.             WHERE B.upload_header_id = pUploadHeaderId
  191.             AND B.upload_header_id = C.upload_header_id
  192.             AND A.product_id = B.product_id
  193.             AND B.action_type = vActionUpdate;
  194.            
  195.         END IF;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement