DOO tables in Oracle Fusion

Hi friends, we are going to discuss about the DOO tables in Oracle Fusion. We will share the most important DOO tables in Oracle Fusion. First of all we need to understand the DOO tables. Oracle fusion has provided the DOO tables to store the sales order information's in oracle fusion. In oracle Fusion , Sales order tables starts with the Prefix DOO% as compared to oracle apps r12 sales order tables which starts with prefix OE%. In this post , We will share most commonly used DOO tables which helps to develop and write the sales order reports. Using DOO tables , we write the sql queries to develop the custom BIP reports in oracle fusion. Please find below the complete detail about DOO tables in oracle fusion.

DOO tables in Oracle Fusion

Top 6 DOO Tables in Oracle Fusion

1.DOO_HEADERS_ALL

2.DOO_LINES_ALL

3.DOO_FULFILL_LINES_ALL

4.DOO_FULFILL_LINE_DETAILS

5.DOO_ORDER_PRICING_DETAILS_V

6.DOO_ORDER_HEADERS_V

Important DOO tables sql Joins in Oracle Fusion

Here below is some of the example showing sql joins in oracle fusion.

Select HEADER_ID from DOO_HEADERS_ALL_V where Order_number like '2434343'

Select * from DOO_LINES_ALL_V where HEADER_ID = '34455522222'

Select HEADER_ID from DOO_ORDER_HEADERS_V where Order_number ='85543'

Select HEADER_ID from DOO_HEADERS_ALL where Order_number ='85543'

Select * from DOO_ORDER_HEADERS_V where REFERENCE_HEADER_ID = '34455522222'

 

Select * from DOO_LINES_ALL where SOURCE_ORDER_NUMBER ='85543'

Select * from DOO_LINES_ALL_V where SOURCE_ORDER_NUMBER ='85543'

DOO tables SQL query to extract the sales order informations in oracle fusion

SELECT DISTINCT

    DHA.ORDER_NUMBER,

    DHA.ORDERED_DATE ORDER_DATE,

    DHA.TRANSACTIONAL_CURRENCY_CODE "Order Currency",

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

        AND LANGUAGE = 'US')  "Sales Order Type",

    DHA.STATUS_CODE,

    DHA.SOLD_TO_PARTY_ID,

    DLA.LINE_NUMBER,

    DHA.ORDER_TYPE_CODE,

    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  "Sales Order PO",

    (SELECT NVL(DESCRIPTION, NAME)

    FROM RA_TERMS

    WHERE TERM_ID = DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('TERM_ID', 'PAYMENT_TERMS', DHA.PAYMENT_TERM_ID))  "Order Paymentterms",

    (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) "Sales Order Disc"

  from  

   doo_lines_all dla,

       doo_fulfill_lines_all dfla,

      doo_headers_all dha,

      hr_all_organization_units haou,

       hr_locations lo,

       egp_system_items esi,

       hr_operating_units hou

 where 1=1

   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 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

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

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

                                      from doo_headers_all dha1

                                     where dha1.header_id=dha.header_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

DOO tables in Oracle Fusion

DOO tables in Oracle Fusion