Wednesday, April 28, 2010
How pricing informations are stored in Oracle Apps R12? (Query involving tables under pricing module)
< Pricing Tables In Oracle Apps R12>
Below query will give you the important columns under pricing base tables and their links with other
Below query will give you the important columns under pricing base tables and their links with other
. We have tested this query in R12.1.1 instance.
SELECT
qph.list_header_id
,qph.name
,qph.description
,qphh.start_date_active
,qphh.currency_code
,qphh.source_system_code
,qphh.active_flag
,qphh.orig_system_header_ref
,qphh.orig_org_id
,qphh.global_flag
,qpl.list_line_id
,qpl.start_date_active
,qpl.end_date_active
,qpl.arithmetic_operator
,qpl.operand
,qpl.orig_sys_line_ref
,qpp.pricing_attribute_id
,qpp.product_attribute_context
,qpp.product_attribute
,qpp.product_attr_value
,qpp.product_uom_code
,qpp.comparison_operator_code
,qpp.orig_sys_pricing_attr_ref
,mtl.inventory_item_id
,mtl.segment1
,mtlc.cross_reference_type
,mtlc.cross_reference
FROM apps.qp_list_headers_b qphh
,apps.qp_list_headers_tl qph
,apps.qp_list_lines qpl
,apps.qp_pricing_attributes qpp
,apps.mtl_system_items_b mtl
,apps.mtl_cross_references_b mtlc
WHERE qph.list_header_id = qphh.list_header_id
AND qph.list_header_id = qpl.list_header_id
AND qph.list_header_id = qpp.list_header_id
AND qpl.list_line_id = qpp.list_line_id
AND mtl.inventory_item_id = qpp.product_attr_value
AND mtl.organization_id = (SELECT UNIQUE master_organization_id
FROM mtl_parameters)
AND mtl.inventory_item_id = mtlc.inventory_item_id
AND SYSDATE BETWEEN qpl.start_date_active
AND NVL(qpl.end_date_active,SYSDATE)
AND NVL(qpl.end_date_active,SYSDATE)
AND SYSDATE BETWEEN qphh.start_date_active
AND NVL(qphh.end_date_active,SYSDATE)
AND NVL(qphh.end_date_active,SYSDATE)
AND qph.name LIKE '%&priceListName%';
Wednesday, April 28, 2010 by Team search · 1
Monday, April 26, 2010
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' ;
Monday, April 26, 2010 by Team search · 0
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.