![EGP_SYSTEM_ITEMS_B Tables SQL Query in Oracle Fusion 1 egp system items b and qp price lists query in oracle fusion cloud EGP_SYSTEM_ITEMS_B 1](https://wpsbutton.com/wp-content/uploads/2024/01/egp-system-items-b-and-qp-price-lists-query-in-oracle-fusion-cloud.png)
Using EGP_SYSTEM_ITEMS_B AND QP_PRICE_LISTS Tables we can create an Item Price List query in oracle fusion Cloud joining some othere important base tables for Pricing. The core table of pricing starts with QP
Below are the important tables that store the Pricing information
Table of Contents
Important Price list tables in Oracle Fusion
- 1. QP_PRICE_LISTS_ALL_B
- 2. QP_PRICE_LISTS_TL
- 3. QP_PRICE_LIST_ITEMS
- 4. QP_PRICE_LIST_CHARGES
- 5. QP_PRICE_LISTS_VL
SQL Query To Retrieve The Price List And Charges For An Inventory Item
QP_PRICE_LISTS query in Oracle fusion
The following SQL can be executed to return Price List and Charge information for an Inventory Item. Obtain the inventory item ID and then search pricing tables
SELECT INVENTORY_ITEM_ID,
ORGANIZATION_ID ,
item_number
FROM egp_system_items_b
WHERE item_number = ‘&ITEM_NUMBER’
Obtain the inventory item ID from the above table egp_system_items_b and then search pricing tables with below query.
SELECT qplt.PRICE_LIST_ID ,
qplt.name ,
qpli.item_id ,
qpli.PRICE_LIST_ITEM_ID ,
qpli.PRICE_LIST_ID ,
qpli.LINE_TYPE_CODE ,
qpli.ITEM_LEVEL_CODE ,
qpli.ITEM_ID ,
qpli.PRICING_UOM_CODE ,
qpli.PRIMARY_PRICING_UOM_FLAG ,
qplc.PRICE_LIST_CHARGE_ID ,
qplc.PRICING_BASIS_ID ,
qplc.CHARGE_LINE_NUMBER ,
qplc.START_DATE ,
qplc.TIER_CONTEXT_CODE ,
qplc.CHARGE_DEFINITION_ID ,
qplc.PARENT_ENTITY_TYPE_CODE ,
qplc.PARENT_ENTITY_ID ,
qplc.BASE_PRICE ,
qplc.USAGE_UOM_CODE ,
qplc.PRICE_PERIODICITY_CODE ,
qplc.CAN_ADJUST_FLAG ,
qplc.END_DATE ,
qplc.TIERED_PRICING_HEADER_ID ,
qplc.ATTRIBUTE_PRICING_MATRIX_ID ,
qplc.CALCULATION_METHOD_CODE ,
qplc.CALCULATION_TYPE_CODE ,
qplc.COST_CALCULATION_AMOUNT ,
qplc.PRICE_LIST_ID
FROM QP_PRICE_LISTS_TL qplt ,
QP_PRICE_LIST_ITEMS qpli,
qp_price_list_charges qplc
WHERE qplt.PRICE_LIST_ID = qpli.PRICE_LIST_ID
AND qplc.parent_entity_id (+) = qpli.PRICE_LIST_ITEM_ID
and qpli.item_id = &INVENTORY_ITEM_ID
ORDER BY qplt.name ,
qpli.PRICE_LIST_ID,
qpli.PRICE_LIST_ITEM_ID
SQL Query to get Price List and item details in Oracle Fusion
For this we need to join the EGP tables to the QP tables
SELECT QPLT.NAME "Price List Name" ,
QPLI.ITEM_ID "Price List Item Id" ,
QPLB.CURRENCY_CODE "Price List Currency",
(SELECT DISTINCT ITEM_NUMBER FROM EGP_SYSTEM_ITEMS ESI WHERE QPLI.ITEM_ID=ESI.INVENTORY_ITEM_ID and organization_id=300000047274444) "Price List Item Code",
(SELECT DISTINCT DESCRIPTION FROM EGP_SYSTEM_ITEMS ESI WHERE QPLI.ITEM_ID=ESI.INVENTORY_ITEM_ID and organization_id=300000047274444) "Price List Item Desc",
QPLC.BASE_PRICE "Price List ITem Price" ,
QPLC.START_DATE
FROM QP_PRICE_LISTS_ALL_B QPLB,
QP_PRICE_LISTS_TL QPLT,
QP_PRICE_LIST_ITEMS QPLI,
QP_PRICE_LIST_CHARGES QPLC
WHERE QPLT.PRICE_LIST_ID = QPLI.PRICE_LIST_ID
AND QPLT.NAME=:P_PRICE_LIST
AND QPLB.PRICE_LIST_ID=QPLI.PRICE_LIST_ID
AND QPLC.PARENT_ENTITY_ID (+) = QPLI.PRICE_LIST_ITEM_ID
and QPLT.language='US'
ORDER BY 1
SQL Query to get price list details in Oracle Fusion?
SELECT QPLT.NAME “Price List Name” ,
QPLI.ITEM_ID “Price List Item Id” ,
QPLB.CURRENCY_CODE “Price List Currency”,EGB.ITEM_NUMBER,EGB.DESCRIPTION,hzp.party_number,
hzp.party_name,
QPLC.BASE_PRICE “Price List ITem Price” ,
QPLC.START_DATE,
epi.TRADING_PARTNER_ID,hzp.party_id
FROM QP_PRICE_LISTS_ALL_B QPLB,
QP_PRICE_LISTS_TL QPLT,
QP_PRICE_LIST_ITEMS QPLI,
QP_PRICE_LIST_CHARGES QPLC,
EGP_SYSTEM_ITEMS EGB,
EGP_ITEM_RELATIONSHIPS_B eir,
EGP_TRADING_PARTNER_ITEMS epi,
hz_parties hzp
WHERE QPLT.PRICE_LIST_ID = QPLI.PRICE_LIST_ID
AND QPLT.NAME=:P_PRICE_LIST
AND QPLB.PRICE_LIST_ID=QPLI.PRICE_LIST_ID
AND QPLC.PARENT_ENTITY_ID (+) = QPLI.PRICE_LIST_ITEM_ID
and QPLT.language=’US’
and QPLI.ITEM_ID=EGB.INVENTORY_ITEM_ID
and qpli.item_id =eir.INVENTORY_ITEM_ID
and eir.TP_ITEM_ID=epi.TP_ITEM_ID
and epi.TRADING_PARTNER_ID=hzp.party_id
and egb.organization_id =300000047274444
ORDER BY 1