Oracle AP Payment API

There is no public Oracle API for the AP payments. We have developed a custom interface by using the standard procedure : ap_checks_pkg.insert_row.

Suppose that we have the following informations at the beginning :

siaxp2.org_id,
siaxp2.invoice_currency_code,
siaxp2.invoice_amount,
siaxp.reference_id,
siaxp.currency_code,
DECODE(siaxp2.invoice_currency_code,'XOF',siaxp2.invoice_amount,siaxp.payment_amount) payment_amount,
siaxp.base_amount,
siaxp.payment_date,
siaxp.transaction_code,
siaxp.internal_bank_account,
siaxp.vendor_id,
siaxp.vendor_site_id

-- Calculate bank account

SELECT abaa2.bank_account_id,
abaa2.currency_code
INTO n_internal_bank_account_id,
n_currency_code
FROM ap_bank_accounts_all abaa,
ap_bank_accounts_all abaa2
WHERE abaa.attribute1 = r_payment_xrt.internal_bank_account
AND abaa.org_id = abaa2.org_id
AND abaa.bank_account_name = abaa2.bank_account_name
AND abaa.bank_branch_id = abaa2.bank_branch_id
AND abaa2.org_id = r_payment_xrt.org_id;

-- Calculate payment document

SELECT ACS.LAST_DOCUMENT_NUM+1,
acs.check_format_id,
acs.check_stock_id,
apaa.bank_account_num,
apaa.bank_account_name,
apaa.account_type,
acs.doc_category_code,
fdsa.doc_sequence_id
INTO n_check_number,
n_check_format_id,
n_check_stock_id,
n_bank_account_num,
n_bank_account_name,
n_account_type,
v_doc_category_code,
n_doc_sequence_id
FROM ap_check_stocks_all acs,
ap_check_formats acf,
ap_bank_accounts_all apaa,
fnd_doc_sequence_assignments fdsa,
ap_bank_branches abb
WHERE abb.bank_branch_id = apaa.bank_branch_id
AND apaa.bank_account_id = n_internal_bank_account_id
AND apaa.set_of_books_id = r_societe.set_of_books_id
AND apaa.bank_account_id = acs.bank_account_id
AND NVL(acs.inactive_date, SYSDATE+1) > SYSDATE
AND acs.check_format_id = acf.check_format_id
AND acf.payment_method_lookup_code = 'EFT'
AND acs.doc_category_code = fdsa.category_code
AND acs.name = DECODE(r_payment_xrt.currency_code,n_currency_code,'VIREMENT','VIRDEV')
AND fdsa.method_code= 'A'
AND fdsa.set_of_books_id = r_societe.set_of_books_id
AND ROWNUM = 1;

-- Calculate supplier info

SELECT pv.vendor_name,
pvsa.vendor_site_code,
pv.vendor_id,
pvsa.vendor_site_id,
pvsa.address_line1,
pvsa.address_line2,
pvsa.address_line3,
pvsa.city,
pvsa.country
INTO v_vendor_name,
v_vendor_site_code,
n_vendor_id,
n_vendor_site_id,
v_address_line1,
v_address_line2,
v_address_line3,
v_city,
v_country
FROM po_vendor_sites_all pvsa,
po_vendors pv
WHERE pvsa.vendor_site_id = r_payment_xrt.vendor_site_id
AND pv.vendor_id = pvsa.vendor_id;

-- Check id

SELECT ap_checks_s.NEXTVAL
INTO n_check_id
FROM dual;

-- Sequence

SELECT db_sequence_name
INTO v_sequence
FROM fnd_document_sequences
WHERE doc_sequence_id = n_doc_sequence_id;

-- Doc sequence value

v_proc_stmt := 'select ' || v_sequence || '.nextval into :next_val from sys.dual';
EXECUTE IMMEDIATE v_proc_stmt INTO n_doc_sequence_value;

-- Final Insertion

ap_checks_pkg.insert_row (
x_rowid => v_rowid,
x_amount => r_payment_xrt.payment_amount,
x_bank_account_id => n_internal_bank_account_id,
x_bank_account_name => n_bank_account_name,
x_check_date => r_payment_xrt.payment_date,
x_check_id => n_check_id,
x_check_number => n_check_number,
x_currency_code => r_payment_xrt.invoice_currency_code,
x_last_updated_by => l_user_id,
x_last_update_date => SYSDATE,
x_payment_method_lookup_code => 'EFT',
x_payment_type_flag => 'Q',
x_address_line1 => v_address_line1,
x_address_line2 => v_address_line2,
x_address_line3 => v_address_line3,
x_checkrun_name => 'Règlement express : ID='||TO_CHAR(n_check_id),
x_check_format_id => n_check_format_id,
x_check_stock_id => n_check_stock_id,
x_city => v_city,
x_country => v_country,
x_created_by => l_user_id,
x_creation_date => SYSDATE,
x_last_update_login => l_user_id,
x_status_lookup_code => 'NEGOTIABLE',
x_vendor_name => v_vendor_name,
x_vendor_site_code => v_vendor_site_code,
x_external_bank_account_id => n_external_bank_account_id,
x_bank_account_num => n_external_bank_account_num,
x_doc_category_code => v_doc_category_code,
x_doc_sequence_id => n_doc_sequence_id,
x_doc_sequence_value => n_doc_sequence_value,
x_org_id => n_org_id,
x_vendor_id => n_vendor_id,
x_vendor_site_id => n_vendor_site_id,
x_exchange_rate => n_exchange_rate, --r_payment_xrt.base_amount/r_payment_xrt.payment_amount,
x_exchange_date => n_exchange_date, --r_payment_xrt.payment_date,
x_exchange_rate_type => n_exchange_rate_type, --'User',
x_base_amount => n_base_amount, -- r_payment_xrt.base_amount, 1.3
x_calling_sequence => v_sequence
);

ap_pay_invoice_pkg.ap_pay_invoice (
p_invoice_id => r.invoice_id,
p_check_id => n_check_id,
p_payment_num => r.payment_num,
p_invoice_payment_id => n_invoice_payment_id,
p_period_name => n_period_name,
p_accounting_date => r_payment_xrt.payment_date,
p_amount => r.amount_remaining,
p_discount_taken => 0,
p_invoice_base_amount => ROUND(r.amount_remaining * n_exchange_rate, 2), -- 1.3 n_invoice_base_amount, -- A confirmer
p_payment_base_amount => ROUND(r.amount_remaining * n_exchange_rate, 2), -- A confirmer
p_accrual_posted_flag => 'N',
p_cash_posted_flag => 'N',
p_posted_flag => 'N',
p_set_of_books_id => n_sob_id,
p_last_updated_by => l_user_id,
p_last_update_login => l_user_id,
p_currency_code => r_payment_xrt.invoice_currency_code,
p_exchange_rate => n_exchange_rate,
p_exchange_rate_type => n_exchange_rate_type, --'User',
p_exchange_date => n_exchange_date, --r_payment_xrt.payment_date,
p_replace_flag => 'N',
p_bank_account_id => n_external_bank_account_id,
p_bank_account_num => n_external_bank_account_num,
p_payment_mode => 'PAY'
);