Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Adrian, Oct 30
- --History Master Product
- CREATE OR REPLACE FUNCTION r_history_master_product(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, BIGINT, CHARACTER VARYING, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pProductCode ALIAS FOR $6;
- pProductName ALIAS FOR $7;
- pSubCtgrProductId ALIAS FOR $8;
- pGroupBrand ALIAS FOR $9;
- pBrandId ALIAS FOR $10;
- pSerialNumber ALIAS FOR $11;
- pLotNumber ALIAS FOR $12;
- pExpiredDate ALIAS FOR $13;
- vYes character varying(1) := 'Y';
- vNo character varying(1) := 'N';
- vEmptyString character varying(1) := '';
- vRawProductDataType character varying(4) := 'RAW1';
- vRawBalanceDataType character varying(4) := 'RAW2';
- vRawReceiveDataType character varying(4) := 'RAW3';
- vGoodDataType character varying(4) := 'GOOD';
- vFilterProductCode character varying := '';
- vFilterProductName character varying := '';
- vFilterSubCtgrProduct character varying := '';
- vFilterGroupBrand character varying := '';
- vFilterBrand character varying := '';
- vFilterSerialNumber character varying := '';
- vFilterLotNumber character varying := '';
- vFilterExpiredDate character varying := '';
- vEmptyId bigint := -99;
- vDocTypeRG bigint := 111;
- vAll character varying := 'ALL';
- BEGIN
- -- RESET TABLE TEMP
- DELETE FROM tt_product WHERE session_id = pSessionId;
- DELETE FROM tt_r_product_list WHERE session_id = pSessionId;
- -- CREATE FILTER PRODUCT CODE
- IF (pProductCode<>vEmptyString) THEN
- vFilterProductCode := ' AND UPPER(A.product_code) LIKE UPPER(''%'||pProductCode||'%'') ';
- END IF;
- -- CREATE FILTER PRODUCT NAME
- IF (pProductName<>vEmptyString) THEN
- vFilterProductName := ' AND UPPER(A.product_name) LIKE UPPER(''%'||pProductName||'%'') ';
- END IF;
- -- CREATE FILTER SUB CTGR PRODUCT
- IF (pSubCtgrProductId<>vEmptyId) THEN
- vFilterSubCtgrProduct := ' AND A.sub_ctgr_product_id = '||pSubCtgrProductId||' ';
- END IF;
- -- CREATE FILTER GROUP BRAND
- IF (pGroupBrand<>vEmptyString) THEN
- vFilterGroupBrand := ' AND COALESCE(B.group_brand, '''||vEmptyString||''') = '''||pGroupBrand||''' ';
- END IF;
- -- CREATE FILTER BRAND
- IF (pBrandId<>vEmptyId) THEN
- vFilterBrand := ' AND A.brand_id = '||pBrandId||' ';
- END IF;
- -- CREATE FILTER SERIAL NUMBER
- IF (pSerialNumber<>vEmptyString) THEN
- vFilterSerialNumber := ' AND UPPER(Y.serial_number) LIKE UPPER(''%'||pSerialNumber||'%'') ';
- END IF;
- -- CREATE FILTER LOT NUMBER
- IF (pLotNumber<>vEmptyString) THEN
- vFilterLotNumber := ' AND Y.lot_number = '''||pLotNumber||''' AND (TRIM(Z.serial_number) <> '||vEmptyString||' AND TRIM(Z.lot_number) <> '||vEmptyString||') ';
- END IF;
- -- CREATE FILTER EXPIRED DATE
- IF (pExpiredDate<>vEmptyString) THEN
- vFilterExpiredDate := ' AND Y.product_expired_date <= '''||pExpiredDate||''' ';
- END IF;
- -- SIAPKAN DATA PRODUCT SESUAI FILTER --
- EXECUTE '
- INSERT INTO tt_product(
- session_id, product_id, tenant_id, product_code, product_name,
- sub_ctgr_product_id, brand_id)
- SELECT $1, A.product_id, A.tenant_id, A.product_code, A.product_name,
- A.sub_ctgr_product_id, A.brand_id
- FROM m_product A
- WHERE A.tenant_id = $2
- AND EXISTS (
- SELECT 1 FROM m_brand_ext B
- WHERE A.brand_id = B.brand_id '
- || vFilterGroupBrand || '
- ) AND EXISTS (
- SELECT 1 FROM in_product_balance Y
- WHERE A.product_id = Y.product_id '
- || vFilterSerialNumber
- || vFilterLotNumber
- || vFilterExpiredDate || '
- ) '
- || vFilterProductCode
- || vFilterProductName
- || vFilterSubCtgrProduct
- || vFilterBrand
- USING pSessionId, pTenantId, vEmptyString;
- -- INIT RAW DATA PRODUCT
- EXECUTE '
- INSERT INTO tt_r_product_list(
- session_id, type_data, num_row,
- product_id, product_code, product_name,
- sub_ctgr_product_id, sub_ctgr_product_code,
- sub_ctgr_product_name,
- group_brand,
- group_brand_parent,
- brand_id, brand_code, brand_name,
- color, size,
- product_balance_id,
- serial_number, lot_number, product_expired_date,
- rcv_date)
- SELECT $1, $3, ROW_NUMBER () OVER (PARTITION BY A.product_id) AS num_row,
- A.product_id, A.product_code, A.product_name,
- A.sub_ctgr_product_id, f_get_sub_ctgr_product_code(A.sub_ctgr_product_id) AS sub_ctgr_product_code,
- f_get_sub_ctgr_product_name(A.sub_ctgr_product_id) AS sub_ctgr_product_name,
- COALESCE(B.group_brand, $4),
- COALESCE(f_get_group_brand_parent_by_group_brand(B.group_brand), $4) AS group_brand_parent,
- A.brand_id, f_get_brand_code(A.brand_id) AS brand_code,
- f_get_brand_name(A.brand_id) AS brand_name,
- COALESCE(C.color, $4) AS color, COALESCE(C.size, $4) AS size,
- $5,
- $4, $4, $4,
- $4
- FROM tt_product A
- LEFT JOIN m_brand_ext B ON A.brand_id = B.brand_id
- LEFT JOIN m_product_custom C ON A.product_id = C.product_id
- WHERE A.session_id = $1'
- || vFilterGroupBrand
- USING pSessionId, pTenantId, vRawProductDataType, vEmptyString, vEmptyId;
- -- INIT RAW DATA BALANCE
- EXECUTE '
- INSERT INTO tt_r_product_list(
- session_id, type_data, num_row,
- product_id, product_code, product_name,
- sub_ctgr_product_id, sub_ctgr_product_code,
- sub_ctgr_product_name,
- group_brand,
- group_brand_parent,
- brand_id, brand_code, brand_name,
- color, size,
- product_balance_id,
- serial_number, lot_number, product_expired_date,
- rcv_date)
- SELECT $1, $3, ROW_NUMBER () OVER (PARTITION BY Y.product_id ORDER BY Y.product_expired_date) AS num_row,
- Y.product_id, $4, $4,
- $5, $4,
- $4,
- $4,
- $4,
- $5, $4, $4,
- $4, $4,
- Y.product_balance_id,
- Y.serial_number, Y.lot_number, Y.product_expired_date,
- $4
- FROM in_product_balance Y
- INNER JOIN tt_product B ON Y.product_id = B.product_id
- WHERE B.session_id = $1 '
- || vFilterSerialNumber
- || vFilterLotNumber
- || vFilterExpiredDate
- USING pSessionId, pTenantId, vRawBalanceDataType, vEmptyString, vEmptyId;
- -- INIT RAW DATA RECEIVE
- INSERT INTO tt_r_product_list(
- session_id, type_data, num_row,
- product_id, product_code, product_name,
- sub_ctgr_product_id, sub_ctgr_product_code,
- sub_ctgr_product_name,
- group_brand,
- group_brand_parent,
- brand_id, brand_code, brand_name,
- color, size,
- product_balance_id,
- serial_number, lot_number, product_expired_date,
- rcv_date)
- SELECT pSessionId, vRawReceiveDataType, ROW_NUMBER () OVER (PARTITION BY A.product_id) AS num_row,
- A.product_id, vEmptyString, vEmptyString,
- vEmptyId, vEmptyString,
- vEmptyString,
- vEmptyString,
- vEmptyString,
- vEmptyId, vEmptyString, vEmptyString,
- vEmptyString, vEmptyString,
- A.product_balance_id,
- vEmptyString, vEmptyString, vEmptyString,
- SUBSTR(MIN(A.create_datetime), 1, 8)
- FROM in_log_product_balance_stock A
- INNER JOIN tt_product B ON A.product_id = B.product_id
- WHERE B.session_id = pSessionId
- AND A.doc_type_id = vDocTypeRG
- GROUP BY A.product_id, A.product_balance_id;
- -- INSERT GOOD DATA
- WITH product AS (
- SELECT * FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawProductDataType -- RAW PRODUCT
- ), product_balance AS (
- SELECT * FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawBalanceDataType -- RAW BALANCE
- ), product_receive AS (
- SELECT * FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawReceiveDataType -- RAW RECEIVE
- ), product_and_balance AS (
- SELECT COALESCE(A.session_id, B.session_id) AS session_id,
- row_number() over(PARTITION BY COALESCE(A.product_id, B.product_id) ORDER BY COALESCE(A.num_row, B.num_row)) AS num_row,
- COALESCE(A.product_id, B.product_id) AS product_id, A.product_code, A.product_name,
- A.sub_ctgr_product_id, A.sub_ctgr_product_code,
- A.sub_ctgr_product_name,
- A.group_brand,
- A.group_brand_parent,
- A.brand_id, A.brand_code, A.brand_name,
- A.color, A.size,
- COALESCE(B.product_balance_id, vEmptyId) AS product_balance_id,
- COALESCE(B.serial_number, vEmptyString) AS serial_number, COALESCE(B.lot_number, vEmptyString) AS lot_number,
- COALESCE(B.product_expired_date, vEmptyString) AS product_expired_date
- FROM product A
- FULL OUTER JOIN product_balance B ON A.product_id = B.product_id AND A.num_row = B.num_row
- ), product_balance_receive AS (
- SELECT COALESCE(A.session_id, B.session_id) AS session_id,
- row_number() over(PARTITION BY COALESCE(A.product_id, B.product_id) ORDER BY COALESCE(A.num_row, B.num_row)) AS num_row,
- COALESCE(A.product_id, B.product_id) AS product_id, A.product_code, A.product_name,
- A.sub_ctgr_product_id, A.sub_ctgr_product_code,
- A.sub_ctgr_product_name,
- A.group_brand,
- A.group_brand_parent,
- A.brand_id, A.brand_code, A.brand_name,
- A.color, A.size,
- A.product_balance_id,
- A.serial_number, A.lot_number,
- A.product_expired_date,
- COALESCE(B.rcv_date, vEmptyString) AS rcv_date
- FROM product_and_balance A
- LEFT JOIN product_receive B ON A.product_id = B.product_id AND A.product_balance_id = B.product_balance_id
- )
- INSERT INTO tt_r_product_list(
- session_id, type_data, num_row,
- product_id, product_code, product_name,
- sub_ctgr_product_id, sub_ctgr_product_code,
- sub_ctgr_product_name,
- group_brand,
- group_brand_parent,
- brand_id, brand_code, brand_name,
- color, size,
- product_balance_id,
- serial_number, lot_number, product_expired_date,
- rcv_date)
- SELECT pSessionId, vGoodDataType, ROW_NUMBER () OVER (PARTITION BY A.product_id ORDER BY num_row),
- A.product_id, A.product_code, A.product_name,
- A.sub_ctgr_product_id, A.sub_ctgr_product_code,
- A.sub_ctgr_product_name,
- A.group_brand,
- A.group_brand_parent,
- A.brand_id, A.brand_code, A.brand_name,
- A.color, A.size,
- A.product_balance_id,
- A.serial_number, A.lot_number, A.product_expired_date,
- A.rcv_date
- FROM product_balance_receive A;
- -- HAPUS DATA RAW
- DELETE FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawBalanceDataType;
- DELETE FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawReceiveDataType;
- Open pRefHeader FOR
- SELECT CASE WHEN (pProductCode <> vEmptyString) THEN
- pProductCode
- ELSE
- vAll
- END AS product_code,
- CASE WHEN (pProductName <> vEmptyString) THEN
- pProductName
- ELSE
- vAll
- END AS product_name,
- CASE WHEN (pSubCtgrProductId <> vEmptyId) THEN
- f_get_sub_ctgr_product_name(pSubCtgrProductId)
- ELSE
- vAll
- END AS sub_ctgr_product_name,
- CASE WHEN (pGroupBrand <> vEmptyString) THEN
- pGroupBrand
- ELSE
- vAll
- END AS group_brand,
- CASE WHEN (pBrandId <> vEmptyId) THEN
- f_get_brand_code(pBrandId) || ' - ' || f_get_brand_name(pBrandId)
- ELSE
- vAll
- END AS brand,
- CASE WHEN (pSerialNumber <> vEmptyString) THEN
- pSerialNumber
- ELSE
- vAll
- END AS serial_number,
- CASE WHEN (pLotNumber <> vEmptyString) THEN
- pLotNumber
- ELSE
- vAll
- END AS lot_number,
- CASE WHEN (pExpiredDate <> vEmptyString) THEN
- pExpiredDate
- ELSE
- vEmptyString
- END AS expired_date,
- f_get_username(pUserId) AS username,
- SUBSTR(pDatetime, 1, 8) AS print_date;
- RETURN NEXT pRefHeader;
- Open pRefDetail FOR
- SELECT A.product_id, A.product_code, A.product_name,
- A.sub_ctgr_product_id, A.sub_ctgr_product_code,
- A.sub_ctgr_product_name,
- A.group_brand,
- A.group_brand_parent,
- A.brand_id, A.brand_code, A.brand_name,
- A.color, A.size AS product_size,
- A.product_balance_id,
- A.serial_number, A.lot_number, A.product_expired_date,
- A.rcv_date
- FROM tt_r_product_list A
- INNER JOIN tt_product C ON C.product_id = A.product_id
- WHERE A.session_id = pSessionId
- AND A.session_id = C.session_id
- AND A.type_data = vGoodDataType
- ORDER BY C.product_code, C.product_name, A.product_expired_date, A.num_row;
- RETURN NEXT pRefDetail;
- -- RESET TABLE TEMP
- DELETE FROM tt_product WHERE session_id = pSessionId;
- DELETE FROM tt_r_product_list WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement