Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * author fredi, 28 Feb 2018
- */
- CREATE OR REPLACE FUNCTION f_sdp_intgr_process_product(bigint)
- RETURNS bigint AS
- $BODY$
- DECLARE
- pHeaderId ALIAS FOR $1;
- vStatusOK character varying := 'OK';
- vStatusFail character varying := 'FAIL';
- vEmpty character varying := '';
- vProductStatusGood character varying := 'GOOD';
- vEmptyId bigint := -99;
- vInitial character varying := 'INITIAL';
- vDatetime character varying;
- vUserId bigint;
- vTenantId bigint;
- vWarehouseId bigint;
- vUomId bigint;
- vDate character varying;
- BEGIN
- vDatetime := f_get_intgr_parameter(pHeaderId, 'datetime');
- vUserId := CAST( f_get_intgr_parameter(pHeaderId, 'userId') AS bigint);
- vTenantId := CAST( f_get_intgr_parameter(pHeaderId, 'tenantId') AS bigint);
- vWarehouseId := CAST( f_get_intgr_parameter(pHeaderId, 'warehouseId') AS bigint);
- vUomId := CAST( f_get_intgr_parameter(pHeaderId, 'uomId') AS bigint);
- SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDD')::CHARACTER VARYING INTO vDate;
- UPDATE ul_intgr_product
- SET status = vStatusOK
- WHERE header_id = pHeaderId;
- /**
- * 1. Cek product_code tidak boleh ada yg sama di CSV
- * 2. Cek product code tidak boleh ada yg sama di database SDP
- */
- UPDATE ul_intgr_product
- SET status = vStatusFail,
- message = 'Duplicate Entry For this Product Code '||product_code
- WHERE header_id = pHeaderId
- AND EXISTS (
- SELECT 1
- FROM ul_intgr_product B
- WHERE ul_intgr_product.header_id = B.header_id
- AND ul_intgr_product.product_code = B.product_code
- AND ul_intgr_product.intgr_product_id <> B.intgr_product_id
- );
- UPDATE ul_intgr_product
- SET status = vStatusFail,
- message = message || ( CASE WHEN message <> vEmpty THEN ', ' ELSE '' END ) || 'Product code/Merchant SKU '||product_code||' already exists in database'
- WHERE header_id = pHeaderId
- AND EXISTS (
- SELECT 1
- FROM m_product A
- WHERE ul_intgr_product.product_code = A.merchant_sku
- );
- UPDATE ul_intgr_product
- SET status = vStatusFail,
- message = 'Product Code '||product_code||' already found in the system, '
- WHERE header_id = pHeaderId
- AND EXISTS (
- SELECT 1
- FROM ul_intgr_product B
- WHERE ul_intgr_product.header_id = B.header_id
- AND ul_intgr_product.product_code = B.product_code
- AND ul_intgr_product.intgr_product_id <> B.intgr_product_id
- );
- WITH inserted_product AS (
- INSERT INTO m_product(
- merchant_sku, product_name,
- product_url, product_description, upc_code,
- length, width, height, weight,
- specification_detail, unique_selling_point, product_story,
- create_datetime, create_user_id, update_datetime, update_user_id,
- version, active, active_datetime, non_active_datetime )
- SELECT A.product_code, A.product_name,
- vEmpty, vEmpty, A.product_barcode,
- CAST (A."length" AS numeric), CAST(A.width AS numeric), CAST(A.height AS numeric), CAST(A.weight AS numeric) AS weight,
- vEmpty, vEmpty, vEmpty,
- vDatetime, vUserId, vDatetime, vUserId,
- 0, 'Y', vDatetime, vDatetime
- FROM ul_intgr_product A
- WHERE A.header_id = pHeaderId
- AND A.status = vStatusOK
- RETURNING *
- ), inserted_product_balance AS (
- INSERT INTO sdp_product_balance(
- tenant_id, product_id, serial_number, lot_number,
- product_expired_date, product_year_made, create_user_id, create_datetime,
- update_user_id, update_datetime, version)
- SELECT vTenantId, A.product_id, vEmpty, vEmpty,
- vEmpty, vEmpty, vUserId, vDatetime,
- vUserId, vDatetime, 0
- FROM inserted_product A
- RETURNING *
- ), inserted_product_balance_stock AS (
- INSERT INTO sdp_product_balance_available_stock(
- tenant_id, warehouse_id,
- product_id, product_balance_id, product_status, base_uom_id,
- qty, create_user_id, create_datetime, update_user_id, update_datetime,
- version)
- SELECT A.tenant_id, vWarehouseId,
- A.product_id, A.product_balance_id, vProductStatusGood, vUomId,
- C.available_stock::numeric, vUserId, vDatetime, vUserId, vDatetime,
- 0
- FROM inserted_product_balance A
- INNER JOIN inserted_product B ON A.product_id = B.product_id
- INNER JOIN ul_intgr_product C ON B.merchant_sku = C.product_code
- WHERE C.header_id = pHeaderId
- AND C.status = vStatusOK
- RETURNING *
- )
- INSERT INTO sdp_log_product_balance_stock(
- tenant_id, ref_id, ref_doc_no,
- ref_doc_date, channel_id, product_id, product_balance_id, warehouse_id,
- product_status, base_uom_id, qty, create_user_id, create_datetime,
- update_user_id, update_datetime, version, ref_doc_type_id)
- SELECT A.tenant_id, vEmptyId, vInitial,
- vDate, vEmptyId, A.product_id, A.product_balance_id, A.warehouse_id,
- A.product_status, A.base_uom_id, A.qty, vUserId, vDatetime,
- vUserId, vDatetime, 0, vEmptyId
- FROM inserted_product_balance_stock A;
- RETURN 0;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement