Friday 30 December 2016

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

1 comment:

  1. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

    ReplyDelete