Sales Order Query in Oracle Fusion

Hi friends, we are going to discuss about the Sales Order Query in Oracle Fusion. We will share the detail sql query which helps to extract the complete sales order informations in oracle fusion. Using this sales order query , we can develop some of the important sales order reports like Sales register and sales order print report. We will also share some of the important sales order tables too which we do use to write the sales order query in oracle fusion. In Oracle fusion, sales order tables have been completely changed as compared to oracle apps r12. In Oracle Fusion, sales order tables starts with DOO%. In this below post, the shared sales order sql query has all the required/relevant columns which we need to have for sales order. Please find below the complete detail about Sales Order Query in Oracle Fusion.

Sales Order Query in Oracle Fusion

9 Important Tables uses in the Sales Order Query in Oracle Fusion

1.DOO_HEADERS_ALL

2.DOO_LINES_ALL

3.DOO_FULFILL_LINES_ALL

4.WSH_ORG_CARRIER_SERVICES

6.WSH_CARRIERS

7.DOO_ORDER_PRICING_DETAILS_V

8.WSH_CARRIERS CARRIERPEO

9.RCS_LOOKUPS

Detail Level Sales Order Query in Oracle Fusion

Here below is the sales order query which helps to extract the complete informations about sales order in oracle fusion.

SELECT 

    HOU.NAME ,

BC_PARTY_NAME,

    BC_ADD1,

    BC_ADD2,

    BC_CITY,

    BC_STT_CNTRY,

    SC_PARTY_NAME,

    SC_ADD1,

    SC_ADD2,

    SC_CITY,

    SC_STT_CNTRY,

    (LO.ADDRESS_LINE_1||', '||LO.ADDRESS_LINE_2||', '||CHR(10)

        ||LO.ADDRESS_LINE_3||', '||LO.ADDRESS_LINE_4||', '||CHR(10)

        ||LO.TOWN_OR_CITY||' - '||LO.POSTAL_CODE||', '||CHR(10)

        ||LO.REGION_2||', '||(SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = LO.COUNTRY)) BU_ADDRESS,

    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,

    TO_CHAR(DFLA.SCHEDULE_SHIP_DATE,'DD-MON-RRRR') PROBABLE_DATE,

    (SELECT REG.REGISTRATION_NUMBER

       FROM HR_OPERATING_UNITS HU, XLE_ETB_PROFILES EST, ZX_PARTY_TAX_PROFILE PTP, ZX_REGISTRATIONS REG

      WHERE HU.DEFAULT_LEGAL_CONTEXT_ID = EST.LEGAL_ENTITY_ID

        AND EST.PARTY_ID = PTP.PARTY_ID

        AND PTP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID

        AND EST.EFFECTIVE_TO IS NULL

        AND REG.EFFECTIVE_TO IS NULL

        AND REG.TAX_REGIME_CODE IN ('GST GLOBAL REGIME', 'GST_REGIME')

        AND REG.EFFECTIVE_TO IS NULL

        AND HU.ORGANIZATION_ID = hou.organization_id

        AND ROWNUM = 1) OU_GSTIN,

    SC_CUST.SC_CNTRY,

    (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 MAX(MODEOFTRANSPORTPEO.LOOKUP_CODE)

       FROM FUSION.WSH_ORG_CARRIER_SERVICES WAREHOUSECARRIERSERVICEPEO,

            FUSION.RCS_LOOKUPS MODEOFTRANSPORTPEO              ,

            FUSION.WSH_CARRIERS CARRIERPEO,

            FUSION.MSC_XREF_MAPPING MXM,

            DOO_FULFILL_LINES_ALL DFL

      WHERE 1=1

        AND WAREHOUSECARRIERSERVICEPEO.MODE_OF_TRANSPORT = MODEOFTRANSPORTPEO.LOOKUP_CODE

        AND MODEOFTRANSPORTPEO.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'

        AND CARRIERPEO.CARRIER_ID = WAREHOUSECARRIERSERVICEPEO.CARRIER_ID

        AND CARRIERPEO.CARRIER_ID = MXM.SOURCE_VALUE

        AND ENTITY_NAME       = 'CARRIERS'

        AND ATTRIBUTE_NAME    = 'CARRIER_ID'

        AND SR_INSTANCE_ID   IN ( SELECT  A.INSTANCE_ID

                                    FROM    FUSION.MSC_APPS_INSTANCES A,

                                            FUSION.HZ_ORIG_SYSTEMS_VL B

                                    WHERE   A.INSTANCE_ID         = B.ORIG_SYSTEM_ID

                                            AND A.ORDER_ORCH_TYPE = 2

                                            AND B.STATUS          = 'A'

                                 )

        AND MXM.TARGET_VALUE = DFL.CARRIER_ID

        AND DFLA.HEADER_ID = DHA.HEADER_ID

     ) SHIPMENT_MODE,

    SC_CUST.SC_CNTRY FINAL_DESTINATION,

    CASE WHEN DFLA.STATUS_CODE IN ('AWAIT_SHIP', 'RESERVED') THEN 'Reserved' ELSE NULL END RESERVE_STATUS,

    (SELECT Z4.REGISTRATION_NUMBER

       FROM HZ_PARTIES Z1,

            HZ_PARTY_SITES Z2,

            ZX_PARTY_TAX_PROFILE Z3,

            ZX_REGISTRATIONS Z4

      WHERE 1 = 1

        AND Z1.PARTY_ID = Z2.PARTY_ID

        AND Z4.REGISTRATION_NUMBER IS NOT NULL

        AND Z2.PARTY_SITE_ID = Z3.PARTY_ID

        AND Z3.PARTY_TAX_PROFILE_ID = Z4.PARTY_TAX_PROFILE_ID

        AND Z1.PARTY_ID = SC_CUST.SC_PARTY_ID

        AND Z2.PARTY_SITE_ID = SC_CUST.SC_PARTY_SITE_ID

        AND Z4.TAX_REGIME_CODE NOT LIKE '%TDS%'

        AND Z4.TAX_REGIME_CODE = 'GST_REGIME'

        AND Z4.EFFECTIVE_TO IS NULL

        AND ROWNUM = 1) SC_GSTIN,

    (SELECT Z4.REGISTRATION_NUMBER

       FROM HZ_PARTIES Z1,

            HZ_PARTY_SITES Z2,

            ZX_PARTY_TAX_PROFILE Z3,

            ZX_REGISTRATIONS Z4

      WHERE 1 = 1

        AND Z1.PARTY_ID = Z2.PARTY_ID

        AND Z4.REGISTRATION_NUMBER IS NOT NULL

        AND Z2.PARTY_SITE_ID = Z3.PARTY_ID

        AND Z3.PARTY_TAX_PROFILE_ID = Z4.PARTY_TAX_PROFILE_ID

        AND Z1.PARTY_ID = BC_CUST.BC_PARTY_ID

        AND Z2.PARTY_SITE_ID = BC_CUST.BC_PARTY_SITE_ID

        AND Z4.TAX_REGIME_CODE NOT LIKE '%TDS%'

        AND Z4.TAX_REGIME_CODE = 'GST_REGIME'

        AND Z4.EFFECTIVE_TO IS NULL

        AND ROWNUM = 1) BC_GSTIN,

    (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 MAX(FOB_POINT_CODE) FROM DOO_HEADERS_ALL_V WHERE HEADER_ID = DHA.HEADER_ID) FOB,

    (SELECT REGEXP_SUBSTR(EC.CATEGORY_CODE,'[^-]+',1,3)

        FROM EGP_CATEGORY_SETS_VL ECS,

             EGP_CATEGORIES_VL EC,

             EGP_ITEM_CATEGORIES EIC,

             INV_ORGANIZATION_DEFINITIONS_V IOD

        WHERE ECS.CATEGORY_SET_NAME = 'XX_CAT_2020'

        AND ECS.CATEGORY_SET_ID = EIC.CATEGORY_SET_ID

        AND EC.CATEGORY_ID = EIC.CATEGORY_ID

        AND EIC.ORGANIZATION_ID = IOD.ORGANIZATION_ID

        AND IOD.ORGANIZATION_CODE = 'IMO'

        AND EIC.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID) HSN_CODE,

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

    ESI.UNIT_LENGTH,

    ESI.UNIT_WIDTH,

    ESI.UNIT_HEIGHT,

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

    NVL((SELECT MAX(CASE  WHEN (ATTRIBUTE_VALUE_1 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_1 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_2

                     WHEN (ATTRIBUTE_VALUE_2 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_2 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_3

                     WHEN (ATTRIBUTE_VALUE_3 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_3 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_4

                     WHEN (ATTRIBUTE_VALUE_4 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_4 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_5

                     WHEN (ATTRIBUTE_VALUE_5 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_5 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_6

                     WHEN (ATTRIBUTE_VALUE_6 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_6 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_7

                     WHEN (ATTRIBUTE_VALUE_7 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_7 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_8

                     WHEN (ATTRIBUTE_VALUE_8 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_8 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_9

                     WHEN (ATTRIBUTE_VALUE_9 = 'DISCOUNT_PERCENT' OR ATTRIBUTE_VALUE_9 = 'MARKUP_PERCENT') THEN ATTRIBUTE_VALUE_10 ELSE '0' END)

        FROM DOO_ORDER_PRICING_DETAILS_V DPD,

             QP_MATRIX_RULES_VL QMR

        WHERE 1 = 1

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

        AND DPD.PRICE_ELEMENT_USAGE_CODE = 'PRICE_ADJUSTMENT'

        AND DPD.FULFILL_LINE_ID = DFLA.FULFILL_LINE_ID

        AND QMR.DYNAMIC_MATRIX_RULE_ID = DPD.PRICING_SOURCE_ID),0) DISCOUNT_PRCT,

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

    (SELECT NVL(SUM(HEADER_CURRENCY_EXT_AMOUNT),0)

        FROM DOO_ORDER_PRICING_DETAILS_V

        WHERE 1 = 1

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

        AND PRICE_ELEMENT_USAGE_CODE = 'PRICE_ADJUSTMENT'

        AND FULFILL_LINE_ID = DFLA.FULFILL_LINE_ID) CASH_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,

    (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 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 = 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 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.line_id = sc_cust.line_id

Sales Order Query in Oracle Fusion