As there is no standard Public API for Oracle invoice payment.
We can use the below code to make payment based on template or specific invoices.
create or replace
PROCEDURE xxx_dm_payment_pkg(
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 xxx_dm_payment_pkg;
We can use the below code to make payment based on template or specific invoices.
create or replace
PROCEDURE xxx_dm_payment_pkg(
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 xxx_dm_payment_pkg;
No comments:
Post a Comment