EGP_SYSTEM_ITEMS_B Tables SQL Query in Oracle Fusion

2195 Views

SaveSavedRemoved 0

egp system items b and qp price lists query in oracle fusion cloud EGP_SYSTEM_ITEMS_B 1

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