Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- ACTION FOR UPDATE
- IF EXISTS (SELECT 1 FROM tt_ul_product WHERE upload_header_id = pUploadHeaderId AND action_type = vActionUpdate) THEN
- -- Ubah update_status menjadi U jika data digunakan untuk Update
- UPDATE ul_product A SET
- update_status = vActionUpdate
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM tt_ul_product B
- WHERE A.kode_produk = B.product_code
- AND B.tenant_id = vTenantId
- AND B.action_type = vActionUpdate
- );
- -- REMOVE existing data specs untuk product yang di upload
- DELETE FROM m_product_specs A
- WHERE EXISTS (
- SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
- AND Z.upload_header_id = pUploadHeaderId
- );
- -- INSERT specs untuk semua product dari upload yang specsnya tidak kosong
- INSERT INTO m_product_specs(
- product_id, tenant_id, product_specs, create_datetime, create_user_id,
- update_datetime, update_user_id, version, active, active_datetime,
- non_active_datetime)
- SELECT A.product_id, A.tenant_id, B.specs, vDatetime, vUserId,
- vDatetime, vUserId, 0, vYes, vDatetime, vSpaceValue
- FROM tt_ul_product A
- INNER JOIN ul_product B ON A.product_code = B.kode_produk
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id=B.upload_header_id
- AND A.action_type = vActionUpdate
- AND TRIM(B.specs)<>vEmpty;
- -- UPDATE product custom
- UPDATE m_product_custom A SET
- style_product=C.golongan_product,
- color=C.color,
- size=C.size,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM tt_ul_product B
- INNER JOIN ul_product C ON B.product_code = C.kode_produk AND B.tenant_id = vTenantId
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id=C.upload_header_id
- AND A.product_id = B.product_id
- AND B.action_type = vActionUpdate;
- -- UPDATE product custom for dlg
- UPDATE m_product_custom_for_dlg A SET
- supplier_id=D.partner_id,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM tt_ul_product B
- INNER JOIN ul_product C ON B.product_code = C.kode_produk
- INNER JOIN m_partner D ON C.kode_supplier = D.partner_code AND D.tenant_id = B.tenant_id
- INNER JOIN m_partner_type E ON D.partner_id = E.partner_id
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id=C.upload_header_id
- AND A.product_id = B.product_id
- AND B.action_type = vActionUpdate
- AND E.group_partner = 'S';
- -- UPDATE product weight dimension
- UPDATE m_product_weight_dimension A SET
- weight=C.weight_in_kg::numeric,
- dimension_length=C.length_in_cm::numeric,
- dimension_width=C.width_in_cm::numeric,
- dimension_height=C.height_in_cm::numeric,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM tt_ul_product B
- INNER JOIN ul_product C ON B.product_code = C.kode_produk
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id=C.upload_header_id
- AND A.product_id = B.product_id
- AND B.action_type = vActionUpdate;
- -- REMOVE EXISTING PRODUCT KEYWORD BY PRODUCT YANG DI UPDATE
- DELETE FROM m_product_keyword A
- WHERE EXISTS (
- SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
- );
- -- INSERT PRODUCT KEYWORD BARU
- INSERT INTO m_product_keyword(
- product_id, tenant_id, keyword, create_datetime,
- create_user_id, update_datetime, update_user_id, version, active,
- active_datetime, non_active_datetime)
- SELECT A.product_id, A.tenant_id, LTRIM(unnest(string_to_array(B.keywords, ','))) AS keywords, vDatetime,
- vUserId, vDatetime, vUserId, 0, vYes,
- vDatetime, vSpaceValue
- FROM tt_ul_product A
- INNER JOIN ul_product B ON A.product_code = B.kode_produk
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id=B.upload_header_id
- AND A.action_type = vActionUpdate
- AND TRIM(B.keywords)<>vEmpty;
- -- REMOVE DATA BARCODE untuk semua product yang diupload
- DELETE FROM m_product_barcode A
- WHERE EXISTS (
- SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
- );
- -- INSERT DATA BARCODE untuk semua product yang diupload
- INSERT INTO m_product_barcode(
- product_id, tenant_id, barcode, create_datetime, create_user_id,
- update_datetime, update_user_id, version, active, active_datetime,
- non_active_datetime)
- SELECT A.product_id, A.tenant_id, B.barcode, vDatetime, vUserId,
- vDatetime, vUserId, 0, vYes, vDatetime,
- vSpaceValue
- FROM tt_ul_product A
- INNER JOIN ul_product B ON A.product_code = B.kode_produk
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id=B.upload_header_id
- AND A.action_type = vActionUpdate
- AND TRIM(B.barcode)<>vEmpty;
- -- REMOVE EXISTING PRODUCT UOM CNV BY PRODUCT YANG DI UPDATE
- DELETE FROM m_product_uom_cnv A
- WHERE EXISTS (
- SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
- );
- -- INSERT PRODUCT UOM CNV ATAS UOM 1
- INSERT INTO m_product_uom_cnv(
- product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
- amount_uom_to, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime)
- SELECT A.product_id, A.tenant_id, A.uom_id_1, 1, A.base_uom_id,
- B.conv_uom_1_to_base::numeric, vDatetime, vUserId, vDatetime,
- vUserId, 0, vYes, vDatetime, vSpaceValue
- FROM tt_ul_product A
- INNER JOIN ul_product B ON A.product_code = B.kode_produk
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id=B.upload_header_id
- AND A.action_type = vActionUpdate
- AND TRIM(B.conv_uom_1_to_base)<>vEmpty
- AND is_numeric(B.conv_uom_1_to_base)
- AND B.conv_uom_1_to_base::numeric > 0;
- -- INSERT PRODUCT UOM CNV ATAS UOM 2
- INSERT INTO m_product_uom_cnv(
- product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
- amount_uom_to, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime)
- SELECT A.product_id, A.tenant_id, A.uom_id_2, 1, A.base_uom_id,
- B.conv_uom_2_to_base::numeric, vDatetime, vUserId, vDatetime,
- vUserId, 0, vYes, vDatetime, vSpaceValue
- FROM tt_ul_product A
- INNER JOIN ul_product B ON A.product_code = B.kode_produk
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id=B.upload_header_id
- AND A.action_type = vActionUpdate
- AND TRIM(B.conv_uom_2_to_base)<>vEmpty
- AND is_numeric(B.conv_uom_2_to_base)
- AND B.conv_uom_2_to_base::numeric > 0;
- -- INSERT PRODUCT UOM CNV ATAS UOM 3
- INSERT INTO m_product_uom_cnv(
- product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
- amount_uom_to, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime)
- SELECT A.product_id, A.tenant_id, A.uom_id_3, 1, A.base_uom_id,
- B.conv_uom_3_to_base::numeric, vDatetime, vUserId, vDatetime,
- vUserId, 0, vYes, vDatetime, vSpaceValue
- FROM tt_ul_product A
- INNER JOIN ul_product B ON A.product_code = B.kode_produk
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id=B.upload_header_id
- AND A.action_type = vActionUpdate
- AND TRIM(B.conv_uom_3_to_base)<>vEmpty
- AND is_numeric(B.conv_uom_3_to_base)
- AND B.conv_uom_3_to_base::numeric > 0;
- -- Update product group brand
- UPDATE m_product_group_brand A SET
- group_brand_product=C.kode_group_brand,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM tt_ul_product B
- INNER JOIN ul_product C ON B.product_code = C.kode_produk
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id = C.upload_header_id
- AND A.product_id = B.product_id
- AND B.action_type = vActionUpdate;
- END IF;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement