ORACLE MASTERMINDS

Account Payables Remittance Advice

Description

AP Remittance Advice, a document that describes payments that are being made. The person or company that is making the payment will sometimes include a remittance advice, which is like a receipt of the payment. A remittance advice is usually used by companies processing either a purchase or a filed claim.

/*Query: 1*/

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;

By

Deepak J