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%';
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “How pricing informations are stored in Oracle Apps R12? (Query involving tables under pricing module)”
February 25, 2013 at 11:11 PM
Thanks for the Query
Post a Comment