SQL Query to Extract Sales Order Tax details in Oracle Fusion

Hi friends, we are going to discuss about the SQL Query to Extract Sales Order Tax details in Oracle Fusion. We will share the detail sql query which helps to extract the taxes details applied to sales order in oracle fusion. In this post , we will also share some of the important sales order taxes tables too which do store these sales order taxes details in oracle fusion. Using this sales order tax query we can develop the custom BIP Sales order Taxes report report in oracle fusion. This is one of the useful query related to sales order. Please find below the complete detail about SQL Query to Extract Sales Order Tax details in Oracle Fusion.

SQL Query to Extract Sales Order Tax details in Oracle Fusion

6 Important Tables for Sales Order Tax Query

1.DOO_FULFILL_LINES_ALL

2.DOO_ORDER_CHARGES

3.DOO_ORDER_CHARGE_COMPONENTS

4.DOO_ORDER_TAX_DETAILS

5.ZX_RATES_B

6.DOO_HEADERS_ALL

Detail SQL Query to Extract Sales Order Tax details in Oracle Fusion

Here below is the detail sql query which helps to extract the complete sales order informations with tax rates and tax value applied to sales order in oracle fusion.

SELECT  DHA.HEADER_ID,

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,

        ZR.TAX||' '||ZR.PERCENTAGE_RATE||'%' TAX_RATE,

        SUM(DFL.ORDERED_QTY * DOTD.HDR_CURR_TAX_UNIT_AMT) TAX_VALUE

   FROM DOO_FULFILL_LINES_ALL DFL,

        DOO_ORDER_CHARGES DOO,

        DOO_ORDER_CHARGE_COMPONENTS DOCC,

        DOO_ORDER_TAX_DETAILS DOTD,

        ZX_RATES_B ZR,

        DOO_HEADERS_ALL DHA

  WHERE DFL.FULFILL_LINE_ID = DOO.PARENT_ENTITY_ID

    AND DOO.ORDER_CHARGE_ID = DOCC.ORDER_CHARGE_ID

    AND DOCC.ORDER_CHARGE_COMPONENT_ID = DOTD.ORDER_CHARGE_COMPONENT_ID

    AND DOTD.TAX_RATE_ID = ZR.TAX_RATE_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 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 = DFL.HEADER_ID

    GROUP BY DHA.HEADER_ID,

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,

             ZR.TAX||' '||ZR.PERCENTAGE_RATE||'%'

SQL Query to Extract Sales Order Tax details in Oracle Fusion

SQL Query to Extract Sales Order Tax details in Oracle Fusion