Friday, 30 December 2016

Account Payables Remittance Advice

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, c_city_pr,
         TRIM (ch.state || ' ' || || ' ' || 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

/*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;

Deepak J


  1. BlueHost is the best hosting company for any hosting services you require.

  2. eToro is the ultimate forex broker for rookie and established traders.

  3. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (even if they're dating somebody else now) you gotta watch this video
    right away...

    (VIDEO) Get your ex back with TEXT messages?