Introduction
This post is about AP Remittance Advice, a document that describes payments that are being made. The person or company that is causing the payment will include a remittance guide, which is like a receipt of the payment. A remittance advice is usually used by companies processing either a purchase or a filed claim.
Script to Account Payable Remittance Advice
SELECT ch.vendor_site_code c_vendor_site_code,
ch.vendor_site_id vendor_site_id, pvs.vendor_id vendor_id,
pvs.vendor_site_code vendor_site_code, ch.check_id c_check_id,
ch.check_number c_check_number,
NVL (pv.vendor_name_alt, ch.vendor_name) c_vendor_name_pr,
bb.bank_name c_bank_name, ch.bank_account_name c_bank_account_name,
pv.vendor_type_lookup_code vendor_type,
DECODE (pv.vendor_type_lookup_code,
‘EMPLOYEE’, NULL,
bb.bank_number
) c_bank_num,
DECODE (pv.vendor_type_lookup_code,
‘EMPLOYEE’, NULL,
ba.bank_account_num
) c_bank_account_num,
DECODE (pv.vendor_type_lookup_code,
‘EMPLOYEE’, ‘Expense Remittance Advice’,
‘Remittance Advice’
) mail_subject,
ch.payment_method_code c_pay_method,
DECODE (ch.payment_method_code,
‘EFT’, 5,
‘BACS’, 5,
‘CHAPS’, 1
) credit_days,
ch.address_line1 c_address_line1_pr,
ch.address_line2 c_address_line2_pr,
ch.address_line3 c_address_line3_pr, ch.city c_city_pr,
TRIM (ch.state || ‘ ‘ || ch.zip || ‘ ‘ || ch.country) c_zip_pr,
ch.currency_code c_currency_code,
ch.doc_sequence_value x_check_voucher_num,
bb.bank_branch_name x_bank_branch_name,
ch.exchange_rate x_exchange_rate, flv.meaning x_payment_method,
TO_CHAR (ch.check_date, ‘dd/mm/yyyy’) x_check_date,
ch.amount x_check_amount, pv.segment1 c_vendor_num,
hl.description c_enq_addr_des, hl.address_line_1 c_enq_addr_1,
hl.address_line_2 c_enq_addr_2, hl.address_line_3 c_enq_addr_3,
NVL ((SELECT meaning
FROM fnd_common_lookups
WHERE lookup_code = hl.region_1 AND lookup_type = ‘IE_COUNTY’),
”
) c_enq_city,
NVL ((SELECT meaning
FROM fnd_common_lookups
WHERE lookup_code = hl.postal_code
AND lookup_type = ‘IE_POSTAL_CODE’),
”
) c_enq_code,
iep.remit_advice_email c_remit_email,
fnd_profile.VALUE (‘IE_AP_CONTACT_NUMBER’) contact_number,
pv.employee_id employee_id, ch.bank_account_id c_bank_acc_id,
ch.check_stock_id c_check_stock_id,
fnd_profile.VALUE (‘SMTP_EMAIL_SERVER’) server_address
FROM ap_checks_all ch,
ce_bank_acct_uses_all cbu,
ce_bank_accounts ba,
ce_bank_branches_v bb,
fnd_lookup_values_vl flv,
ap_suppliers pv,
ap_supplier_sites_all pvs,
financials_system_params_all fsp,
iby_external_payees_all iep,
hr_locations hl
WHERE ch.ce_bank_acct_use_id = cbu.bank_acct_use_id(+)
AND cbu.bank_account_id = ba.bank_account_id(+)
AND ch.org_id = fnd_profile.VALUE (‘ORG_ID’)
AND ba.bank_branch_id = bb.branch_party_id
AND flv.lookup_code = ch.payment_method_code
AND flv.lookup_type = ‘PAYMENT METHOD’
AND flv.enabled_flag = ‘Y’
AND SYSDATE BETWEEN NVL (flv.start_date_active, SYSDATE)
AND NVL (flv.end_date_active, SYSDATE)
AND pvs.vendor_id(+) = ch.vendor_id
AND pvs.vendor_site_id(+) = ch.vendor_site_id
AND pv.vendor_id = pvs.vendor_id
AND fsp.org_id(+) = ch.org_id
AND hl.location_id =
NVL (pvs.bill_to_location_id, fsp.bill_to_location_id)
AND pv.vendor_id = ch.vendor_id
AND ch.org_id = fsp.org_id
AND ch.party_id = iep.payee_party_id
AND iep.supplier_site_id = pvs.vendor_site_id
ORDER BY 34 DESC;
/*Query: 2*/
SELECT aip.check_id c_ip_check_id,
TO_CHAR (aiv.invoice_date, ‘dd/mm/yyyy’) c_invoice_date,
aiv.doc_sequence_value c_voucher_num, aiv.invoice_num c_invoice_num,
aiv.description c_description, aip.discount_taken c_discount_taken,
aip.amount c_amount
FROM ap_invoice_payments_all aip, ap_invoices_all aiv
WHERE aip.invoice_id = aiv.invoice_id
AND aip.org_id = fnd_profile.VALUE (‘ORG_ID’)
AND aiv.org_id = fnd_profile.VALUE (‘ORG_ID’)
ORDER BY aiv.invoice_num;
Summary
This Post described AP Remittance Advice, a document that describes payments that are being made. The person or company that is causing the payment will sometimes include a remittance advice, which is like a receipt of the payments. A remittance advice used by companies processing either a purchase or a filed claim in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at venkatesh.b@staging.doyensys.com or use the comment section below to ask your questions.
