create PROCEDURE UpdateRecordStatus AS
-- Declare cursors for each query, use the database link correctly
CURSOR c_update_record_master IS
SELECT f.FILE_REC_SEQ, [Link], f.INST_CODE, f.FILE_REF_NO,
f.EXT_REF_NO
FROM DIGX_FU_FT_DETAILS@FLEX_DBLINK.[Link] f
WHERE [Link] IN ('P', 'E');
CURSOR c_update_file_details IS
SELECT f.FILE_REF_NO, f.FILE_STATUS
FROM DIGX_FU_FT_MASTER@FLEX_DBLINK.[Link] f
WHERE f.FILE_STATUS = 'C';
BEGIN
-- Update REC_STATUS in DIGX_FU_RECORD_MASTER based on STATUS in
DIGX_FU_FT_DETAILS
FOR r IN c_update_record_master LOOP
-- Determine the appropriate REC_STATUS based on [Link]
IF [Link] = 'P' THEN
UPDATE DIGX_FU_RECORD_MASTER
SET REC_STATUS = 'PROCESSED'
WHERE REC_REF_ID = r.FILE_REC_SEQ;
-- Insertion in custom alert table to dispatch alerts of credit to
cusomers
IF r.INST_CODE = 'IBFT' THEN
INSERT INTO DIGX_FU_FT_CREDIT_ALERT_RECORDS (
RECORD_REF_ID, ALERT_STATUS, PAYMENT_TYPE, AMOUNT,
CURRENCY, CREDIT_ACCOUNT_ID,
VALUE_DATE, DEBIT_ACCOUNT_ID, FILE_REF_ID, CUSTOMER_ID,
HOST_REFERENCE_NUMBER, PAYEE_NAME, ACCOUNT_TYPE,
MOBILE_EMAIL, NETWORK,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATED_DATE, VERSION)
SELECT
r.FILE_REC_SEQ,
'PENDING',
'IBFT',
[Link],
f.AMOUNT_CURR,
f.CREDIT_ACCOUNT_ID,
[Link],
f.DEBIT_ACCOUNT_ID,
f.FILE_REF_ID,
f.PARTY_ID,
r.EXT_REF_NO,
[Link],
'CSA',
f.EMAIL_ID,
[Link],
'DB_USER',
SYSDATE,
'DB_USER',
SYSDATE,
1
FROM DIGX_FU_DOMESTICFT f
WHERE f.REC_REF_ID = r.FILE_REC_SEQ AND f.FILE_REF_ID =
r.FILE_REF_NO;
ELSE
INSERT INTO DIGX_FU_FT_CREDIT_ALERT_RECORDS (
RECORD_REF_ID, ALERT_STATUS, PAYMENT_TYPE, AMOUNT,
CURRENCY, CREDIT_ACCOUNT_ID,
VALUE_DATE, DEBIT_ACCOUNT_ID, FILE_REF_ID, CUSTOMER_ID,
HOST_REFERENCE_NUMBER, PAYEE_NAME, ACCOUNT_TYPE,
MOBILE_EMAIL, NETWORK,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATED_DATE, VERSION)
SELECT
r.FILE_REC_SEQ,
'PENDING',
'IFT',
[Link],
f.AMOUNT_CURR,
f.CREDIT_ACCOUNT_ID,
[Link],
f.DEBIT_ACCOUNT_ID,
f.FILE_REF_ID,
f.PARTY_ID,
r.EXT_REF_NO,
[Link],
'CSA',
f.EMAIL_ID,
'INTERNAL',
'DB_USER',
SYSDATE,
'DB_USER',
SYSDATE,
1
FROM DIGX_FU_INTERNALFT f
WHERE f.REC_REF_ID = r.FILE_REC_SEQ AND f.FILE_REF_ID =
r.FILE_REF_NO;
END IF;
ELSIF [Link] = 'E' THEN
UPDATE DIGX_FU_RECORD_MASTER
SET REC_STATUS = 'ERROR'
WHERE REC_REF_ID = r.FILE_REC_SEQ;
END IF;
END LOOP;
-- Update FILE_STATUS in DIGX_FU_FILEDETAILS based on FILE_STATUS in
DIGX_FU_FT_MASTER
FOR m IN c_update_file_details LOOP
-- Determine the appropriate FILE_STATUS based on f.FILE_STATUS
IF m.FILE_STATUS = 'C' THEN
UPDATE DIGX_FU_FILEDETAILS
SET FILE_STATUS = 'PROCESSED'
WHERE FILE_REF_ID = m.FILE_REF_NO;
END IF;
END LOOP;
COMMIT; -- Commit transaction if everything executes successfully
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- Rollback in case of any error
RAISE; -- Optional: re-raise the exception to log or propagate it
END;
/
