RETURNS SETOF refcursor AS
$BODY$
DECLARE
pRefErrorValidate REFCURSOR := 'errorValidate';
pSessionId ALIAS FOR $1;
vYes character varying := 'Y';
vNo character varying := 'N';
vEmptyChar character varying := '';
BEGIN
DELETE FROM temp_error_data WHERE session_id = pSessionId;
/* update nilai flg_valid dan flg_migrate = N */
UPDATE temp_supp_merchant
SET flg_valid = vNo,
flg_migrate = vNo
WHERE flg_valid = vNo AND flg_migrate =vNo;
SELECT A.merchant_code
FROM temp_supp_merchant A
WHERE EXIST(SELECT B.merchant_code FROM supp_merchant B
WHERE A.merchant_code = B.merchant_code);
IF FOUND THEN
INSERT INTO temp_error_data(
session_id, group_data, validasi_data, data)
SELECT pSessionId, 'MERCHANT', 'KODE SUDAH ADA', merchant_code;
END IF;
IF NOT EXISTS(SELECT 1 FROM temp_error_data WHERE session_id = pSessionId) THEN
UPDATE temp_supp_merchant
SET flg_valid = vYes
WHERE merchant_code = pSessionId
AND flg_valid = vNo;
END IF;
Open pRefErrorValidate FOR
SELECT session_id, group_data, validasi_data, data
FROM temp_error_data
WHERE data = merchant_code;
RETURN NEXT pRefErrorValidate;
DELETE FROM temp_error_data WHERE session_id = pSessionId;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
/
