R12: Quaries

Find a query which fetches both invoice and prepayment information of a payable invoice:

SELECT pv.vendor_name "Vendor Name",

       ai.invoice_num "Invoice Num",

       ai.invoice_id "Invoice Id",

       ai.invoice_amount "Invoice amount",

       ail.line_number "Invoice Line Num",

       ai2.invoice_id "Prepay Invoice Id",

       ai2.invoice_num "Prepay Invoice Num",

       ail.prepay_line_number "Prepay Invoice Line Num",

       (-1)*(ail.amount - NVL(ail.included_tax_amount,0))

       "Prepay Amount Applied",

       NULLIF((-1)*(NVL(ail.total_rec_tax_amount, 0) +      

       NVL(ail.total_nrec_tax_amount, 0)), 0)

       "Tax amount Applied"

FROM   AP_INVOICES_ALL ai,

       AP_INVOICES_ALL ai2,

       AP_INVOICE_LINES_ALL ail,

       AP_SUPPLIERS pv

WHERE ai.invoice_id                  = ail.invoice_id

AND   ai2.invoice_id                   = ail.prepay_invoice_id

AND   ail.amount                       < 0

AND   NVL(ail.discarded_flag,'N')     <> 'Y'

AND   ail.line_type_lookup_code        = 'PREPAY'

AND   ai.vendor_id                     = pv.vendor_id

AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')

---AND   ai.invoice_id                    = '&invoice_id';

Bottom of Form

How payment information is stored in R12? ( Query to find payments in R12)

We all know that, In R12 payment related information is moved from Account Payables and stored in modules named “ORACLE PAYMENTS” and “Cash Management”. Today I tried to frame a query which starts from AP_INVOICES_ALL and finds its payment information in various tables.

Below query is tested in R12.1.1 instance.

SELECT invh.invoice_id
      ,invh.vendor_id
      ,invh.invoice_num
      ,invh.invoice_amount
      ,invh.amount_paid
      ,idpa.payment_date
      ,idpa.document_type
      ,idpa.payment_amount
      ,idpa.payment_method_code
       ,idpa.po_number

      ,idpa.document_description      
      ,ip.payment_id
      ,ip.payment_service_request_id
      ,ip.payment_instruction_id
      ,ip.paper_document_number
      ,ip.payment_amount
      ,ip.int_bank_number
      ,ip.int_bank_branch_name
      ,ip.int_bank_branch_number
      ,ip.int_bank_account_name
      ,ip.payer_legal_entity_name
      ,ip.org_name
      ,ip.payee_address_concat
      ,iupd.date_used
      ,iupd.document_use
      ,ieba.ext_bank_account_id
      ,ieba.country_code
      ,ieba.bank_account_name
      ,ieba.bank_account_num
      ,hzb.party_id   bank_id
      ,hzb.party_name bank_name
      ,hzbb.party_id  bank_branch_id
      ,hzbb.party_name bank_branch_name
      ,hzbb.address1
      ,hzbb.address2
      ,hzbb.address3
      ,hzbb.city   
      ,cpd.payment_document_id
      ,cpd.payment_doc_category
      ,cpd.payment_document_name
      ,cpd.format_code
      ,cpd.first_available_document_num
      ,cpd.last_available_document_number
FROM   AP_INVOICES_ALL invh
      ,iby_docs_payable_all idpa
      ,iby_payments_all ip
      ,IBY_USED_PAYMENT_DOCS iupd      
      ,iby_ext_bank_accounts ieba
      ,hz_parties hzb
      ,hz_parties hzbb
      ,ce_payment_documents cpd
WHERE 1 = 1
AND    idpa.calling_app_doc_ref_number = invh.invoice_num
AND    idpa.calling_app_doc_unique_ref2= invh.invoice_id
AND    idpa.payment_id               = ip.payment_id
AND    ip.paper_document_number      = iupd.used_document_number
AND    ip.external_bank_account_id   = ieba.ext_bank_account_id
AND    ieba.bank_id                  = hzb.party_id
AND    ieba.branch_id                = hzbb.party_id
AND    iupd.payment_document_id      = cpd.payment_document_id
AND    invh.invoice_id               = '&invoice_id' ;