Oracle Fusion scm Tables

Hi friends, we are going to discuss about the Oracle Fusion scm Tables. We will share some of the important SCM tables which we do use in our day to operation while working in Oracle fusion applications. In Oracle fusion, SCM tables have been completely changed as compared to Oracle apps SCM tables. In this post , We will share some of those important SCM tables which helps to extract the different types of SCM data in oracle fusion. We will also share some of the SCM module sql queries which helps to extract the SCM related data in oracle fusion. Using these sql queries we can develop the custom BIP reports in oracle fusion related to SCM module. Please find below the more detail about SCM tables in Oracle Fusion.

Oracle Fusion scm Tables

TOP 14 SCM tables in Oracle Fusion

Here below is the list of some of the important SCM Tables of Oracle Fusion.

1.DOO_HEADERS_ALL

2.DOO_LINES_ALL

3.DOO_FULFILL_LINES_ALL

4.DOO_ORDER_PRICING_DETAILS_V

5.QP_PRICE_LISTS_VL

6.QP_PRICE_LIST_ITEMS

7.WSH_CARRIERS CARRIERPEO

8.WSH_ORG_CARRIER_SERVICES

9.WSH_CARRIERS

10.RCS_LOOKUPS

11.EGP_SYSTEM_ITEMS

12.EGP_CATEGORY_SETS_VL

13.EGP_CATEGORIES_VL

14.EGP_ITEM_CATEGORIES

SCM related SQL Queries in Oracle Fusion

SCM Query 1:-

SELECT DISTINCT

    DHA.ORDER_NUMBER,

    DHA.ORDERED_DATE ORDER_DATE,

    DHA.TRANSACTIONAL_CURRENCY_CODE CURR_CODE,

    DHA.ORDER_TYPE_CODE,

    DHA.STATUS_CODE,

    DHA.SOLD_TO_PARTY_ID,

    DLA.LINE_NUMBER,

    ESI.ITEM_NUMBER ITEM_CODE,

    ESI.DESCRIPTION ITEM_DESC,

    DLA.ORDERED_UOM,

    DFLA.ORDERED_QTY,

    DLA.UNIT_LIST_PRICE,

    DLA.UNIT_SELLING_PRICE,

    (DFLA.ORDERED_QTY * DLA.UNIT_SELLING_PRICE) LINE_PRICE,

    DHA.CUSTOMER_PO_NUMBER,

    (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'ORA_DOO_ORDER_TYPES' AND LOOKUP_CODE = DHA.ORDER_TYPE_CODE

        AND LANGUAGE = 'US') ORDER_TYPE,

    (SELECT NVL(DESCRIPTION, NAME)

    FROM RA_TERMS

    WHERE TERM_ID = DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('TERM_ID', 'PAYMENT_TERMS', DHA.PAYMENT_TERM_ID)) PYMT_TERMS,

    (SELECT NVL(SUM(HEADER_CURRENCY_EXT_AMOUNT),0)

        FROM DOO_ORDER_PRICING_DETAILS_V

        WHERE 1 = 1

        AND UPPER(EXPLANATION) LIKE 'ADD%DISC%'

        AND PRICE_ELEMENT_USAGE_CODE = 'PRICE_ADJUSTMENT'

        AND FULFILL_LINE_ID = DFLA.FULFILL_LINE_ID) ADD_DISC

  from doo_headers_all dha,

       doo_lines_all dla,

       doo_fulfill_lines_all dfla,

       egp_system_items esi,

       hr_operating_units hou,

       hr_all_organization_units haou,

       hr_locations lo

 where 1=1

   and dha.status_code NOT IN ('DOO_REFERENCE','CANCELED','DOO_DRAFT')

   and trunc(dha.ordered_date) >= nvl(:p_from_date, trunc(dha.ordered_date))

   and trunc(dha.ordered_date) <= nvl(:p_to_date, trunc(dha.ordered_date))

   and dha.order_number = nvl(:p_order_num,dha.order_number)  

   and dha.request_cancel_date is null

   and dha.order_type_code not in ('Sales_Return')

   and dla.inventory_organization_id = esi.organization_id

   and dla.inventory_item_id = esi.inventory_item_id

   and dha.org_id = hou.organization_id

   and hou.organization_id = haou.organization_id

   and dha.change_version_number = (select max(dha1.change_version_number)

                                      from doo_headers_all dha1

                                     where dha1.header_id=dha.header_id)

   and haou.location_id = lo.location_id

   and dha.header_id = dla.header_id

   and dha.header_id = dfla.header_id

   and dla.header_id = dfla.header_id

   and dla.line_id = dfla.line_id

SCM Query 2:-

SELECT  QPLT.NAME "Price List Name"  ,

        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=85) "Price List Customer Item",

        QPLC.BASE_PRICE "Price List Item Price"     ,

        (SELECT DISTINCT DESCRIPTION FROM EGP_SYSTEM_ITEMS ESI WHERE QPLI.ITEM_ID=ESI.INVENTORY_ITEM_ID and organization_id=85) "Price List Item Desc"          

FROM        QP_PRICE_LIST_ITEMS QPLI,

        QP_PRICE_LIST_CHARGES QPLC,

QP_PRICE_LISTS_ALL_B QPLB,

         QP_PRICE_LISTS_TL QPLT

WHERE   1=1

AND QPLT.NAME=:P_PRICE_LIST

AND QPLT.PRICE_LIST_ID = QPLI.PRICE_LIST_ID

AND QPLB.PRICE_LIST_ID=QPLI.PRICE_LIST_ID

AND QPLC.PARENT_ENTITY_ID (+) = QPLI.PRICE_LIST_ITEM_ID

ORDER BY 1

Oracle Fusion scm Tables

Oracle Fusion scm Tables