Order Management tables in Oracle Fusion

Hi friends, we are going to discuss about the Order Management tables in Oracle Fusion. We will share the some of the important order management tables which helps to store the Sales order information’s in oracle fusion. In this post, we will try to discuss those order management tables which helps to develop the sales order register and sales order print reports in oracle fusion. In our case, we have developed the Sale Order Register report using these order management tables. As a technical consultant, you should have the knowledge of the order management tables to develop the custom BIP reports as well the develop the data extraction queries. Please find below the detail about Order Management tables in Oracle Fusion.

Order Management tables in Oracle Fusion

8 Important Order Management Tables in Oracle Fusion

1.DOO_HEADERS_ALL

2.DOO_LINES_ALL

3.DOO_FULFILL_LINES_ALL

4.DOO_ORDER_PRICING_DETAILS_V

5.WSH_CARRIERS CARRIERPEO

6.WSH_ORG_CARRIER_SERVICES

7.WSH_CARRIERS

8.RCS_LOOKUPS

3 Order Management Price List Tables in Oracle Fusion

1.QP_MATRIX_RULES_VL

2.QP_PRICE_LISTS_VL

3.QP_PRICE_LIST_ITEMS

4 Order Management Item Information's Tables

1.EGP_SYSTEM_ITEMS

2.EGP_CATEGORY_SETS_VL

3.EGP_CATEGORIES_VL

4.EGP_ITEM_CATEGORIES

Details SQL Query Using Order Management Tables to extract the Sales order Details

Here below is the Detail sales order query using Order Management Tables in Oracle Fusion.

SELECT DISTINCT

    DHA.ORDER_NUMBER,

    TO_CHAR(DHA.ORDERED_DATE, 'DD-MON-RRRR') ORDER_DATE,

    DHA.TRANSACTIONAL_CURRENCY_CODE CURR_CODE,

    DHA.ORDER_TYPE_CODE,

    DHA.STATUS_CODE,

    DHA.SOLD_TO_PARTY_ID,

    DLA.LINE_ID,

    DLA.LINE_NUMBER,

    DLA.INVENTORY_ORGANIZATION_ID,

    DLA.INVENTORY_ITEM_ID,

    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,

    BC_PARTY_NAME,

    BC_ADD1,

    BC_ADD2,

    BC_CITY,

    BC_STT_CNTRY,

    SC_PARTY_NAME,

    SC_ADD1,

    SC_ADD2,

    SC_CITY,

    SC_STT_CNTRY,

    SC_CUST.SC_CNTRY,

    SC_CUST.SC_CNTRY FINAL_DESTINATION,

    (SELECT MAX(FOB_POINT_CODE) FROM DOO_HEADERS_ALL_V WHERE HEADER_ID = DHA.HEADER_ID) FOB,

    (NVL(ESI.UNIT_WEIGHT,0) * DFLA.ORDERED_QTY) GW,

    round((((nvl(esi.unit_length,0) / 1000) * (nvl(esi.unit_width,0) / 1000) * (nvl(esi.unit_height,0) / 1000)) * dfla.ordered_qty),3) CBM

  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,

    (select dfl.header_id, dfl.line_id, dfl.bill_to_customer_id bc_cust_id, dfl.bill_to_site_use_id bc_site_use_id,

            hp.party_name bc_party_name, hps.party_site_id bc_party_site_id, hzl.address1||' '||hzl.address2 bc_add1,

            hzl.address3||' '||hzl.address4 bc_add2,

            hzl.city||' - '||hzl.postal_code bc_city,

            hzl.state||' '||nvl((select territory_short_name from fnd_territories_tl where territory_code = hzl.country

                                    and language = 'US'), hzl.country) bc_stt_cntry,

            hp.party_id bc_party_id,

            hzl.state bc_stt,

            hzl.city bc_cty,

            (select territory_short_name

               from fnd_territories_tl

              where territory_code = hzl.country

                and language = 'US') bc_cntry,

            nvl(hzl.addr_element_attribute2,'Other') zone,

            nvl(hzl.addr_element_attribute3,'Other') branch

       from hz_cust_site_uses_all hcsu,

            doo_fulfill_lines_all dfl,

            hz_cust_acct_sites_all hcas,

            hz_party_sites hps,

            hz_parties hp,

            hz_locations hzl

      where hcsu.site_use_code = 'BILL_TO'

        and hcsu.site_use_id = dfl.bill_to_site_use_id

        and hcsu.cust_acct_site_id = hcas.cust_acct_site_id

        and hcas.party_site_id = hps.party_site_id

        and hps.party_id = hp.party_id

        and hps.location_id = hzl.location_id

        group by

            dfl.header_id, dfl.line_id, dfl.bill_to_customer_id, dfl.bill_to_site_use_id,

            hp.party_name, hps.party_site_id, hzl.address1||' '||hzl.address2,

            hzl.address3||' '||hzl.address4,

            hzl.city||' - '||hzl.postal_code,

            hzl.state, hzl.country, hp.party_id,

            hzl.city, nvl(hzl.addr_element_attribute2,'Other'), nvl(hzl.addr_element_attribute3,'Other')) bc_cust,

    (select dfl.header_id, dfl.line_id, dfl.ship_to_party_id sc_party_id,

            hp.party_name sc_party_name, hps.party_site_id sc_party_site_id, hzl.address1||' '||hzl.address2 sc_add1,

            hzl.address3||' '||hzl.address4 sc_add2,

            hzl.city,

            hzl.city||' - '||hzl.postal_code sc_city,

            hzl.state,

            hzl.state||' '||nvl((select territory_short_name from fnd_territories_tl where territory_code = hzl.country

                                    and language = 'US'), hzl.country) sc_stt_cntry,

            nvl((select territory_short_name from fnd_territories_tl where territory_code = hzl.country

                                    and language = 'US'), hzl.country) sc_cntry

       from doo_fulfill_lines_all dfl,

            hz_cust_acct_sites_all hcas,

            hz_party_sites hps,

            hz_parties hp,

            hz_locations hzl

      where dfl.ship_to_party_site_id = hcas.party_site_id

        and hcas.party_site_id = hps.party_site_id

        and hps.party_id = hp.party_id

        and hps.location_id = hzl.location_id

        and dfl.ship_to_party_id = hp.party_id

        and dfl.ship_to_party_site_id = hps.party_site_id

        group by

            dfl.header_id, dfl.line_id, dfl.ship_to_party_id,

            hp.party_name, hps.party_site_id, hzl.address1||' '||hzl.address2,

            hzl.address3||' '||hzl.address4,hzl.city,

            hzl.city||' - '||hzl.postal_code,

            hzl.state, hzl.country) sc_cust 

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

                                      from doo_headers_all dha1

                                     where dha1.header_id=dha.header_id)

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

   and dha.request_cancel_date is null

   and dha.order_type_code not in ('Sales_Return')

   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

   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 haou.location_id = lo.location_id

   and dha.header_id = bc_cust.header_id

   and dla.header_id = bc_cust.header_id

   and dla.line_id = bc_cust.line_id

   and dha.header_id = sc_cust.header_id

   and dla.header_id = sc_cust.header_id

   and dla.line_id = sc_cust.line_id

   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 (hou.organization_id in (:p_org) or 'All' in (:p_org || 'All'))

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

 order by to_number(dha.order_number) desc

Order Management tables in Oracle Fusion