Sales order table in Oracle Fusion

Hi friends, we are going to discuss about the Sales order table in Oracle Fusion. We will share the complete list of sales order tables which helps to store the sales order informations in oracle fusion. In oracle fusion , sales order tables are completely changed as compared to oracle apps r12. In oracle fusion, if we want to search the sales order tables then we need to search the fusion tables starts with 'DOO%'. All sales order tables starts with DOO% in oracle fusion. If we want to develop the custom BIP sales order reports or want to extract the sales order data in oracle fusion , we should have the complete knowledge of the sales order tables. In this post , We are trying to share some of the important sales order tables which we do commonly used in day to day operations and have most of the important sales order informations in oracle fusion. Please find below the complete detail about Sales order table in Oracle Fusion.

Sales order table in Oracle Fusion

4 Important Sales order Tables in Oracle Fusion

1.DOO_HEADERS_ALL

2.DOO_LINES_ALL

3.DOO_FULFILL_LINES_ALL

4.DOO_ORDER_PRICING_DETAILS_V

Sales Order Price List Tables in Oracle Fusion

1.QP_PRICE_LISTS_VL

2.QP_PRICE_LIST_ITEMS

Important SQL Query to extract the sales order details in Oracle Fusion

Here below is the sales register query using the some of the important sales order tables in oracle fusion.

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

Sales order table in Oracle Fusion