-- Find the payment_instruction_id form below script
SELECT
payment_instruction_id PaymentInstructionID,
payment_instruction_status,
payments_complete_code
FROM
iby_pay_instructions_all
WHERE
pay_admin_assigned_ref_code = &PPR_Number;
----Payment Transmitted script below
SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 2000;
DECLARE
l_return_status VARCHAR2(1000);
l_instr_id NUMBER;
l_instr_status VARCHAR2(240);
l_completion_code VARCHAR2(240);
l_msg_index_out NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
l_instr_id := &PaymentInstructionID;
DBMS_OUTPUT.PUT_LINE('Payment instruction ID : '|| l_instr_id);
SELECT payment_instruction_status,
payments_complete_code
INTO l_instr_status,
l_completion_code
FROM iby_pay_instructions_all
WHERE payment_instruction_id = l_instr_id;
IF (NVL(l_completion_code,'NO') <> 'YES') THEN
DBMS_OUTPUT.PUT_LINE('Payment instruction status : '|| l_instr_status);
DBMS_OUTPUT.PUT_LINE('Payment instruction completion code : '|| l_completion_code);
DBMS_OUTPUT.PUT_LINE('Attempting to unlock pmt instruction id: '|| l_instr_id);
IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity( l_instr_id, 'PAYMENT_INSTRUCTION', l_return_status );
DBMS_OUTPUT.PUT_LINE('Finished unlocking pmt instruction id: '|| l_instr_id);
DBMS_OUTPUT.PUT_LINE('Attempting to terminate pmt instruction id: '|| l_instr_id);
IBY_DISBURSE_UI_API_PUB_PKG.terminate_pmt_instruction( l_instr_id, l_instr_status, l_return_status );
IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('ERROR: API return status: ' || l_return_status );
DBMS_OUTPUT.PUT_LINE('ERROR: API call failed');
DBMS_OUTPUT.PUT_LINE('Rolling back changes ...');
ROLLBACK;
ELSE
DBMS_OUTPUT.PUT_LINE('INFO: API return status: ' || l_return_status );
DBMS_OUTPUT.PUT_LINE('INFO: API call success');
DBMS_OUTPUT.PUT_LINE('Committing ...');
COMMIT;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Payment instruction status : '|| l_instr_status);
DBMS_OUTPUT.PUT_LINE('Payment instruction completion code : '|| l_completion_code);
DBMS_OUTPUT.PUT_LINE('ERROR: Payment instruction cannot be terminated since it is already complete');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR: Payment instruction not found !');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: Exception occured when executing termination script !');
DBMS_OUTPUT.PUT_LINE('SQLCODE is: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM is: ' || SQLERRM);
END;
/
EXIT;