Monday, April 26, 2010
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
,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' ;
,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' ;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
2 Responses to “How payment information is stored in R12? ( Query to find payments in R12)”
August 16, 2021 at 2:53 AM
Include all of the relevant information regarding your small business in your website's directory description. This should include your business' name, location, telephone number, contact information and possibly reviews, business hours and anything else that is relevant. Add in photos, maps or other interactive features if the directory lets you. NZ invoice template
November 9, 2021 at 6:00 AM
To some extent upgrading programs, that may include creating a new and fresher look, is a lot like an insurance policy against slipping revenues, and do not necessarily relate to any incremental growth per se. slot online terpercaya, judi slot online
Post a Comment