Query to get Receipt data with tax details -- Oracle Apps

SELECT
VENDOR_CODE,
VENDOR_NAME,
CATEGORY,
VENDOR_SITE_CODE,
ADDRESESS1,
ADDRESESS2,
CITY,
STATE,
COUNTRY,
CONTACT,
CONTACT_EMAIL, --CURRENCY_CODE,
SUM(NVL("BASE_VALUE",0)+NVL("SALES TAX",0)+NVL("EXCISE",0)+NVL("INSURANCE",0)+NVL("OTHERS",0)+NVL("FREIGHT",0))TOTAL
FROM
(
SELECT DISTINCT C.SEGMENT1 VENDOR_CODE,
                 C.VENDOR_NAME,
C.VENDOR_TYPE_LOOKUP_CODE Category,
E.VENDOR_SITE_CODE,
E.ADDRESS_LINE1 ADDRESESS1,
E.ADDRESS_LINE2 ADDRESESS2,
E.CITY,
                 E.STATE,
UPPER(PC.NAME) COUNTRY,
E.PHONE CONTACT,
E.ATTRIBUTE11 CONTACT_EMAIL,H.CURRENCY_CODE,A.RECEIPT_NUM,D.SHIPMENT_LINE_ID,
                NVL(DECODE (H.CURRENCY_CODE,
                            'INR',
                             P.ACT_QTY * H.PO_UNIT_PRICE,
                             P.ACT_QTY * H.PO_UNIT_PRICE * H.CURRENCY_CONVERSION_RATE
                            ),0) "BASE_VALUE",
                NVL(DECODE (H.CURRENCY_CODE,
                          'INR',
                           NULL,
                           P.ACT_QTY * H.PO_UNIT_PRICE
                           )
                     + ROUND (DECODE (H.CURRENCY_CODE,
                            'INR',
                             NULL,
                             I.TAX_AMOUNT / H.CURRENCY_CONVERSION_RATE
                                )
                        ),0) "GRN DOLLER VAL",
                ROUND ((I.TAX_AMOUNT / I.QTY_RECEIVED) * P.ACT_QTY ) "TOTAL_LINE_AMT",
                ST.TAX_TYPE,
            NVL((ST.SALESTAX * P.ACT_QTY / H.QUANTITY),0) "SALES TAX",
              ETAX.TAX_TYPE ETAX_TYPE,    
              ETAX.SALESTAX * P.ACT_QTY / H.QUANTITY "ENTRY TAX",
              NVL((ET.EXCISE * P.ACT_QTY / H.QUANTITY),0) "EXCISE",
              NVL((INS.INSURANCE * P.ACT_QTY / H.QUANTITY),0) "INSURANCE",
              NVL((Freight.Freight * P.ACT_QTY / H.QUANTITY),0) "FREIGHT",
              NVL((OTHES.OTHERS * P.ACT_QTY / H.QUANTITY),0) "OTHERS",
              DECODE (A.RECEIPT_SOURCE_CODE,
                        'VENDOR',
                         C.VENDOR_NAME,
                        'CUSTOMER',
                         NULL,
                        ORG.ORGANIZATION_NAME ) "VENDOR_NAME1"
           FROM
            PO.RCV_SHIPMENT_HEADERS A,
            PO.PO_HEADERS_ALL B,
   APPS.AP_SUPPLIERS C,
            PO.RCV_SHIPMENT_LINES D,
            APPS.AP_SUPPLIER_SITES_ALL E,  
            INV.MTL_SYSTEM_ITEMS_B F,
            PO.RCV_TRANSACTIONS H,
   APPS.JAI_RCV_LINES  I,
   APPS.JAI_RCV_LINE_TAXES RCV,
            INV.MTL_CATEGORIES_B J,
            GL.GL_CODE_COMBINATIONS K,
   PA_COUNTRY_V PC,
            APPS.JAI_CMN_VENDOR_SITES L,
            APPS.AP_TERMS M,
              (SELECT
                  TRIM (A.FLEX_VALUE) AS FLEXID,
                        B.DESCRIPTION AS PRODUCT
FROM
                   APPS.FND_FLEX_VALUES A,
                   APPS.FND_FLEX_VALUES_TL B
WHERE
                   A.FLEX_VALUE_ID = B.FLEX_VALUE_ID
                   AND A.FLEX_VALUE_SET_ID =
  (SELECT FLEX_VALUE_SET_ID
                      FROM APPS.FND_ID_FLEX_SEGMENTS
     WHERE APPLICATION_COLUMN_NAME = 'SEGMENT6'
AND ID_FLEX_CODE = 'GL#')) FLEX,
   (SELECT   SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
                          SUM (QUANTITY) ACT_QTY
                     FROM PO.RCV_TRANSACTIONS
                    WHERE TRANSACTION_TYPE = 'DELIVER'
                 GROUP BY SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID) P,
                (SELECT   SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
                          SUM (QUANTITY) RJT_QTY
                     FROM PO.RCV_TRANSACTIONS
                    WHERE UPPER(TRANSACTION_TYPE) = 'REJECT'
                 GROUP BY SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID) Q,
                (SELECT   SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID,
                          SUM (QUANTITY) RTN_QTY
                     FROM PO.RCV_TRANSACTIONS
                    WHERE UPPER(TRANSACTION_TYPE) = 'RETURN TO VENDOR'
                 GROUP BY SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID) R,
                (SELECT   SUM (TAX_AMOUNT) SALESTAX, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID,TAX_TYPE
                     FROM
     APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('SALES TAX', 'CST', 'LST')
       AND UPPER(TAX_NAME) NOT LIKE '%ENTRY%'
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID,TAX_TYPE
                ) ST,
                (SELECT   SUM (TAX_AMOUNT) SALESTAX, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID,TAX_TYPE
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('SALES TAX', 'CST', 'LST')
AND UPPER(TAX_NAME) LIKE '%ENTRY%'
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID,TAX_TYPE
) ETAX,
                (SELECT   SUM (TAX_AMOUNT) EXCISE, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('EXCISE', 'CUSTOMS', 'CVD')
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID) ET,
                (SELECT   SUM (TAX_AMOUNT) INSURANCE, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('INSURANCE')
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID) INS,
                 (SELECT   SUM (TAX_AMOUNT) FREIGHT, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) IN ('FREIGHT')
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID) Freight,
                (SELECT   SUM (TAX_AMOUNT) OTHERS, SHIPMENT_LINE_ID,
                          SHIPMENT_HEADER_ID
                     FROM
APPS.JAI_RCV_LINE_TAXES
                    WHERE UPPER(TAX_TYPE) NOT IN
                             ('EXCISE',
                              'CUSTOMS',
                              'CVD',
                              'SALES TAX',
                              'CST',
                              'LST',
                              'INSURANCE' ,
                              'FREIGHT'
                             )
                 GROUP BY SHIPMENT_LINE_ID, SHIPMENT_HEADER_ID) OTHES,
                APPS.ORG_ORGANIZATION_DEFINITIONS ORG
--              AP.AP_INVOICES_ALL AP,
--              (SELECT * FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL WHERE REVERSAL_FLAG IS NULL) APID
          WHERE D.PO_HEADER_ID = B.PO_HEADER_ID(+)
            AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
            AND A.VENDOR_ID = C.VENDOR_ID(+)
            AND A.VENDOR_SITE_ID = E.VENDOR_SITE_ID(+)
            AND D.TO_ORGANIZATION_ID = F.ORGANIZATION_ID
            AND D.ITEM_ID = F.INVENTORY_ITEM_ID
            AND D.SHIPMENT_HEADER_ID = H.SHIPMENT_HEADER_ID
            AND D.SHIPMENT_LINE_ID = H.SHIPMENT_LINE_ID
            AND H.TRANSACTION_TYPE = 'RECEIVE'
            AND D.CATEGORY_ID = J.CATEGORY_ID
            AND F.COST_OF_SALES_ACCOUNT = K.CODE_COMBINATION_ID
            AND H.SHIPMENT_HEADER_ID = I.SHIPMENT_HEADER_ID(+)
            AND H.SHIPMENT_LINE_ID = I.SHIPMENT_LINE_ID(+)
            AND H.SHIPMENT_HEADER_ID = P.SHIPMENT_HEADER_ID(+)
            AND H.SHIPMENT_LINE_ID = P.SHIPMENT_LINE_ID(+)
            AND H.SHIPMENT_HEADER_ID = Q.SHIPMENT_HEADER_ID(+)
            AND H.SHIPMENT_LINE_ID = Q.SHIPMENT_LINE_ID(+)
            AND H.SHIPMENT_HEADER_ID = R.SHIPMENT_HEADER_ID(+)
            AND H.SHIPMENT_LINE_ID = R.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_HEADER_ID = ST.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = ST.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_LINE_ID = ET.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_HEADER_ID = ET.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = INS.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_HEADER_ID = INS.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_HEADER_ID = OTHES.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = OTHES.SHIPMENT_LINE_ID(+)
            AND RCV.SHIPMENT_HEADER_ID(+) = H.SHIPMENT_HEADER_ID
            AND RCV.SHIPMENT_LINE_ID(+) = H.SHIPMENT_LINE_ID
            AND ORG.ORGANIZATION_ID(+) = A.ORGANIZATION_ID
--           AND APID.INVOICE_ID = AP.INVOICE_ID(+)
--           AND H.TRANSACTION_ID = APID.RCV_TRANSACTION_ID(+)
            AND C.VENDOR_ID IS NOT NULL
---         AND F.COST_OF_SALES_ACCOUNT = K.CODE_COMBINATION_ID
            AND FLEX.FLEXID = K.SEGMENT6
             AND E.VENDOR_ID = L.VENDOR_ID(+)
             AND E.VENDOR_SITE_ID  =  L.VENDOR_SITE_ID(+)
     AND B.TERMS_ID=M.TERM_ID  
AND PC.COUNTRY_CODE=E.COUNTRY
AND RCV.SHIPMENT_HEADER_ID = ETAX.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = ETAX.SHIPMENT_LINE_ID(+)
AND RCV.SHIPMENT_HEADER_ID = Freight.SHIPMENT_HEADER_ID(+)
            AND RCV.SHIPMENT_LINE_ID = Freight.SHIPMENT_LINE_ID(+)
AND H.CURRENCY_CODE  IN ('INR')
AND TRUNC(H.TRANSACTION_DATE) BETWEEN :PFROM AND :PTO
)
GROUP BY
VENDOR_CODE,
VENDOR_NAME,
CATEGORY,
VENDOR_SITE_CODE,
ADDRESESS1,
ADDRESESS2,
CITY,
STATE,
COUNTRY,
CONTACT,
CONTACT_EMAIL
ORDER BY
VENDOR_CODE