AccountPayables Remittance Advice DescriptionAPRem..

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
Recent Posts