Oracle Invoice Payment API
There is no standard Public API for Oracle invoice payment. The below code is to make payment based on template for specific invoices.
create or replace
PROCEDURE xx_dm_payment_prc( p_template_id IN NUMBER) AS
errbuf VARCHAR2(100);
retcode NUMBER;
x_return_status VARCHAR2(110);
p_check_run_id NUMBER;
l_req_id LONG;
checkrun_name VARCHAR2(250);
l_current_calling_sequence VARCHAR2(240) := 'select invoices';
user_id NUMBER;
resp_id NUMBER;
resp_appl_id VARCHAR2(250);
payment_profile VARCHAR2(250);
bank_account_id VARCHAR2(250);
doc_rej_level_code VARCHAR2(250);
pay_rej_level_code VARCHAR2(250);
zero_payment_flag VARCHAR2(1);
review_proposed_pmts_flag VARCHAR2(1);
create_instrs_flag VARCHAR2(1);
v_payment_date DATE:=SYSDATE;
v_pay_thru_date DATE:=SYSDATE;
v_pay_from_date DATE:=SYSDATE-3600;
v_status VARCHAR2(240);
--
BEGIN
dbms_output.put_line('1');
--
apps.mo_global.init('SQLAP');
--
apps.fnd_global.apps_initialize(31128,50878,201);
--
EXECUTE IMMEDIATE 'alter session set current_schema = APPS';
--
mo_global.set_policy_context('S',9094);
--
DBMS_OUTPUT.PUT_LINE('MO Global Org ID Set is: '|| fnd_global.org_id);
--
ap_autoselect_pkg.create_checkrun (p_check_run_id,
p_template_id,
v_payment_date,
v_pay_thru_date,
v_pay_from_date,
l_current_calling_sequence);
--
DBMS_OUTPUT.PUT_LINE ('Check Run ID is ' || p_check_run_id);
--
COMMIT;
--
dbms_output.put_line('Checkrun Id is'||'-'||P_CHECK_RUN_ID);
--
ap_autoselect_pkg.select_invoices (retcode,
errbuf,
p_check_run_id,
NULL,
v_payment_date,
v_pay_thru_date,
v_pay_from_date);
-- Start of Comment
-- Delete Other Invoices from the selected template to process only specific Invoice --
DELETE apps.ap_selected_invoices_all
WHERE invoice_id NOT IN (26035,26024)
AND checkrun_id = p_check_run_id;
--
UPDATE ap_payment_schedules_all aps
SET checkrun_id = null
WHERE checkrun_id = p_check_run_id
AND NOT EXISTS (SELECT /*+HASH_AJ */ 'no row in asi'
FROM ap_selected_invoices_all asi
WHERE asi.invoice_id = aps.invoice_id
AND asi.payment_num = aps.payment_num
AND asi.checkrun_id = p_check_run_id);
-- End of Comment
--
COMMIT;
--
dbms_output.put_line('Select Invoices - ERRORBUF And RETCODE for auto select Value is'||'-'||errbuf||'-'||retcode);
--
SELECT checkrun_name, status
INTO checkrun_name, v_status
FROM ap_inv_selection_criteria_all
WHERE checkrun_ID = p_check_run_id;
--
dbms_output.put_line(checkrun_name || 'Status is: '|| v_status);
--
SELECT payment_profile_id,
bank_account_id,
document_rejection_level_code,
payment_rejection_level_code,
zero_amounts_allowed,
payments_review_settings,
create_instrs_flag
INTO payment_profile,
bank_account_id,
doc_rej_level_code,
pay_rej_level_code,
zero_payment_flag,
review_proposed_pmts_flag,
create_instrs_flag
FROM ap_payment_templates
WHERE template_id = p_template_id;
--
DBMS_OUTPUT.PUT_LINE ('Payment Template Id: '|| p_template_id);
--
dbms_output.put_line('Payment Process Profile Id is'||'-'||payment_profile);
dbms_output.put_line('Bank Account Id is'||'-'||bank_account_id);
dbms_output.put_line('Document Rejection Level Code is'||'-'||doc_rej_level_code);
dbms_output.put_line('Payment Rejection Level Code is'||'-'||pay_rej_level_code);
dbms_output.put_line('Zero Payment Flag is'||'-'||zero_payment_flag);
dbms_output.put_line('Review Proposed Payments Flag is'||'-'||review_proposed_pmts_flag);
dbms_output.put_line('Create Payment Instructions Flag is'||'-'||create_instrs_flag);
--
iby_disburse_submit_pub_pkg.submit_payment_process_request (errbuf,
retcode,
'200',
checkrun_name,
bank_account_id,
payment_profile,
zero_payment_flag,
'',
'',
doc_rej_level_code,
pay_rej_level_code,
review_proposed_pmts_flag,
create_instrs_flag,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'');
dbms_output.put_line('ERRORBUF And RETCODE Value for payment process request is'||'-'||ERRBUF||'-'||RETCODE);
--
COMMIT;
--
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE||'-'|| SQLERRM);
END xx_dm_payment_prc;
