PURCHASE TAX DETAIL QUERT

/* Formatted on 1/9/2012 3:29:29 PM (QP5 v5.115.810.9015) */
  SELECT   TRANS_TYPE,
           RECEIPT_NUM,
           NAME,
           ITEM,
           DESCRIPTION,
           UNIT_PRICE,
           DECODE (TRANS_TYPE,
                   'R',
                   QTY,
                   'T',
                   QTY * -1)
              QTY,
           VENDOR_NAME,
           VENDORCODE,
           VENDOR_ID,
           VENDOR_ADDRESS,
           SHIPPED_DATE,
           CREATION_DATE,
           WAYBILL_AIRBILL_NUM,
           PACKING_SLIP,
           DECODE (TRANS_TYPE,
                   'R',
                   ADDL_EXCISE_COST * QTY / RECEIPT_QTY,
                   'T',
                   ADDL_EXCISE_COST * -1 * QTY / RECEIPT_QTY)
              ADDL_EXCISE,
           DECODE (TRANS_TYPE,
                   'R',
                   EXCISE * QTY / RECEIPT_QTY,
                   'T',
                   EXCISE * -1 * QTY / RECEIPT_QTY)
              EXCISE,
           DECODE (TRANS_TYPE,
                   'R',
                   ADDNEXCISE * QTY / RECEIPT_QTY,
                   'T',
                   ADDNEXCISE * -1 * QTY / RECEIPT_QTY)
              OTHEREXCISE,
           DECODE (TRANS_TYPE,
                   'R',
                   CST * QTY / RECEIPT_QTY,
                   'T',
                   CST * -1 * QTY / RECEIPT_QTY)
              CST,
           DECODE (TRANS_TYPE,
                   'R',
                   LST * QTY / RECEIPT_QTY,
                   'T',
                   LST * -1 * QTY / RECEIPT_QTY)
              LST,
           DECODE (TRANS_TYPE,
                   'R',
                   CUSTOMS * QTY / RECEIPT_QTY,
                   'T',
                   CUSTOMS * -1 * QTY / RECEIPT_QTY)
              CUSTOMS,
           DECODE (TRANS_TYPE,
                   'R',
                   CVD * QTY / RECEIPT_QTY,
                   'T',
                   CVD * -1 * QTY / RECEIPT_QTY)
              CVD,
           DECODE (TRANS_TYPE,
                   'R',
                   INSURANCE * QTY / RECEIPT_QTY,
                   'T',
                   INSURANCE * -1 * QTY / RECEIPT_QTY)
              INSURANCE,
           DECODE (TRANS_TYPE,
                   'R',
                   FREIGHT * QTY / RECEIPT_QTY,
                   'T',
                   FREIGHT * -1 * QTY / RECEIPT_QTY)
              FREIGHT,
           DECODE (TRANS_TYPE,
                   'R',
                   SERVICE * QTY / RECEIPT_QTY,
                   'T',
                   SERVICE * -1 * QTY / RECEIPT_QTY)
              SERVICE,
           DECODE (TRANS_TYPE,
                   'R',
                   OCTRAI * QTY / RECEIPT_QTY,
                   'T',
                   OCTRAI * -1 * QTY / RECEIPT_QTY)
              OCTRAI,
           DECODE (TRANS_TYPE,
                   'R',
                   OTHER * QTY / RECEIPT_QTY,
                   'T',
                   OTHER * -1 * QTY / RECEIPT_QTY)
              OTHER,
           DECODE (TRANS_TYPE,
                   'R',
                   EXCISE_COST * QTY / RECEIPT_QTY,
                   'T',
                   EXCISE_COST * -1 * QTY / RECEIPT_QTY)
              EXCISE_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   ADDNEXCISE_COST * QTY / RECEIPT_QTY,
                   'T',
                   ADDNEXCISE_COST * -1 * QTY / RECEIPT_QTY)
              ADDNEXCISE_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   CST_COST * QTY / RECEIPT_QTY,
                   'T',
                   CST_COST * -1 * QTY / RECEIPT_QTY)
              CST_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   LST_COST * QTY / RECEIPT_QTY,
                   'T',
                   LST_COST * -1 * QTY / RECEIPT_QTY)
              LST_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   CUSTOMS_COST * QTY / RECEIPT_QTY,
                   'T',
                   CUSTOMS_COST * -1 * QTY / RECEIPT_QTY)
              CUSTOMS_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   CVD_COST * QTY / RECEIPT_QTY,
                   'T',
                   CVD_COST * -1 * QTY / RECEIPT_QTY)
              CVD_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   INSURANCE_COST * QTY / RECEIPT_QTY,
                   'T',
                   INSURANCE_COST * -1 * QTY / RECEIPT_QTY)
              INSURANCE_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   FREIGHT_COST * QTY / RECEIPT_QTY,
                   'T',
                   FREIGHT_COST * -1 * QTY / RECEIPT_QTY)
              FREIGHT_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   SERVICE_COST * QTY / RECEIPT_QTY,
                   'T',
                   SERVICE_COST * -1 * QTY / RECEIPT_QTY)
              SERVICE_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   OCTRAI_COST * QTY / RECEIPT_QTY,
                   'T',
                   OCTRAI_COST * -1 * QTY / RECEIPT_QTY)
              OCTRAI_COST,
           DECODE (TRANS_TYPE,
                   'R',
                   OTHER_COST * QTY / RECEIPT_QTY,
                   'T',
                   OTHER_COST * -1 * QTY / RECEIPT_QTY)
              OTHER_COST,
           TRANSACTION_DATE,
           UNIT_OF_MEASURE,
           SUBINVENTORY,
           PO_NUMBER,
           PO_LINE,
           DECODE (TRANS_TYPE,
                   'R',
                   PURCHASEVALUE * QTY / RECEIPT_QTY,
                   'T',
                   PURCHASEVALUE * -1 * QTY / RECEIPT_QTY)
              PURCHASEVALUE,
           DECODE (TRANS_TYPE,
                   'R',
                   VALUE * QTY / RECEIPT_QTY,
                   'T',
                   VALUE * -1 * QTY / RECEIPT_QTY)
              VALUE,
           VENDOR_SITE_ID,
           SHIPMENT_NUM,
           VEHICAL_NO,
           GR_NO
    FROM   (  SELECT   TRANS_TYPE,
                       RECEIPT_NUM,
                       NAME,
                       ITEM,
                       DESCRIPTION,
                       AVG (PO_UNIT_PRICE) UNIT_PRICE,
                       (QUANTITY) QTY,
                       VENDOR_NAME,
                       VENDORCODE,
                       VENDOR_ID,
                       AVG (RECEIPT_QTY) RECEIPT_QTY,
                       VENDOR_ADDRESS,
                       SHIPPED_DATE,
                       CREATION_DATE,
                       WAYBILL_AIRBILL_NUM,
                       PACKING_SLIP,
                       SUM (EXCISE) EXCISE,
                       SUM (ADDNEXCISE) ADDNEXCISE,
                       SUM (CST) CST,
                       SUM (LST) LST,
                       SUM (CUSTOMS) CUSTOMS,
                       SUM (CVD) CVD,
                       SUM (INSURANCE) INSURANCE,
                       SUM (FREIGHT) FREIGHT,
                       SUM (SERVICE) SERVICE,
                       SUM (OCTRAI) OCTRAI,
                       SUM (OTHER) OTHER,
                       SUM (EXCISE_COST) EXCISE_COST,
                       SUM (ADDNEXCISE_COST) ADDNEXCISE_COST,
                       SUM (CST_COST) CST_COST,
                       SUM (LST_COST) LST_COST,
                       SUM (CUSTOMS_COST) CUSTOMS_COST,
                       SUM (CVD_COST) CVD_COST,
                       SUM (FREIGHT_COST) FREIGHT_COST,
                       SUM (INSURANCE_COST) INSURANCE_COST,
                       SUM (OCTRAI_COST) OCTRAI_COST,
                       SUM (SERVICE_COST) SERVICE_COST,
                       SUM (OTHER_COST) OTHER_COST,
                       SUM (ADDITIONAL_EXCISE_COST) ADDL_EXCISE_COST,
                       TRANSACTION_DATE,
                       UNIT_OF_MEASURE,
                       SUBINVENTORY,
                       PO_NUMBER,
                       PO_LINE,
                         /*==========================================================================================*/
                         --                       (AVG(QUANTITY) * AVG(PO_UNIT_PRICE))
                         --                         + NVL (TOTAL_TAX_AMT ,
                         --                              0
                         --                           )
                         /*  (AVG (PO_UNIT_PRICE) * AVG (QUANTITY))
                         + SUM (EXCISE)
                         + SUM (ADDNEXCISE)
                         + SUM (CST)
  --                       + SUM (LST)
                         + SUM (CUSTOMS)
                         + SUM (CVD)
                         + SUM (FREIGHT)
                         + SUM (INSURANCE)
                         + SUM (OCTRAI)
                         + SUM (SERVICE)
                         + SUM (OTHER)
                            PURCHASEVALUE,
  --                          (AVG(QUANTITY) * AVG(PO_UNIT_PRICE))
  --                         + NVL (TOTAL_VALUE ,
  --                              0
  --                           )
                           (AVG (PO_UNIT_PRICE) * AVG (QUANTITY))
                         + SUM (EXCISE_COST)
                         + SUM (ADDNEXCISE_COST)
                         + SUM (CST_COST)
                         + SUM (LST_COST)
                         + SUM (CUSTOMS_COST)
                         + SUM (CVD_COST)
                         + SUM (FREIGHT_COST)
                         + SUM (INSURANCE_COST)
                         + SUM (ADDITIONAL_EXCISE_COST)
                         + SUM (OCTRAI_COST)
                         + SUM (SERVICE)
  --                       + SUM (SERVICE_COST)
                         + SUM (OTHER_COST)
                            VALUE,*/
                         /*==========================================================================================*/
                         (AVG (PO_UNIT_PRICE) * AVG (QUANTITY))
                       + SUM (EXCISE)
                       + SUM (ADDNEXCISE)
                       + SUM (CST)
                       --                       + SUM (LST)                        --------  ADDED BY ELANGOVAN ----------
                       + SUM (CUSTOMS)
                       + SUM (CVD)
                       + SUM (FREIGHT)
                       + SUM (INSURANCE)
                       + SUM (OCTRAI)
                       + SUM (SERVICE)
                       + SUM (OTHER)
                          PURCHASEVALUE,
                         (AVG (PO_UNIT_PRICE) * AVG (QUANTITY))
                       + SUM (EXCISE)
                       + SUM (ADDNEXCISE)
                       + SUM (CST)
                       + SUM (LST)     --------  ADDED BY ELANGOVAN ----------
                       + SUM (CUSTOMS)
                       + SUM (CVD)
                       + SUM (FREIGHT)
                       + SUM (INSURANCE)
                       + SUM (OCTRAI)
                       + SUM (SERVICE)
                       + SUM (OTHER)
                          VALUE,
                       VENDOR_SITE_ID,
                       SHIPMENT_NUM,
                       VEHICAL_NO,
                       GR_NO
                FROM   (SELECT   DECODE (T.TRANSACTION_TYPE,
                                         'RECEIVE',
                                         'R',
                                         'RETURN TO VENDOR',
                                         'T')
                                    TRANS_TYPE,
                                 O.NAME,
                                 H.RECEIPT_NUM,
                                 L.LINE_NUM,
                                 SUBSTR (NVL (I.SEGMENT1, 'DD ITEMS'), 1, 8) ITEM,
                                 L.ITEM_DESCRIPTION DESCRIPTION,
                                 (SELECT   QUANTITY
                                    FROM   RCV_TRANSACTIONS
                                   WHERE   SHIPMENT_HEADER_ID =
                                              T.SHIPMENT_HEADER_ID
                                           AND SHIPMENT_LINE_ID =
                                                 T.SHIPMENT_LINE_ID
                                           AND TRANSACTION_TYPE = 'RECEIVE'
                                           AND ROWNUM = 1)
                                    RECEIPT_QTY,
                                 V.VENDOR_NAME VENDOR_NAME,
                                 V.SEGMENT1 VENDORCODE,
                                 V.VENDOR_ID VENDOR_ID,
                                    VS.ADDRESS_LINE1
                                 || ' '
                                 || VS.ADDRESS_LINE2
                                 || ' '
                                 || VS.ADDRESS_LINE3
                                 || ' '
                                 || VS.ADDRESS_LINE4
                                 || ' '
                                 || VS.CITY
                                 || ' '
                                 || VS.STATE
                                 || ' '
                                 || VS.COUNTRY
                                    VENDOR_ADDRESS,
                                 H.SHIPPED_DATE,
                                 H.CREATION_DATE,
                                 H.WAYBILL_AIRBILL_NUM,
                                 H.PACKING_SLIP,
                                 T.PO_UNIT_PRICE * T.CURRENCY_CONVERSION_RATE
                                    PO_UNIT_PRICE,
                                 T.QUANTITY,
                                 JTX.TAX_ID,
                                 JTX.MODVAT_FLAG,
                                 DECODE (JTM.TAX_TYPE,
                                         'Excise', JTX.TAX_AMOUNT,
                                         0)
                                    EXCISE,
                                 DECODE (
                                    JTM.TAX_TYPE,
                                    'Excise',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    EXCISE_COST,
                                 /*   ((select DECODE (JTM.TAX_TYPE,
                                         'EXCISE_EDUCATION_CESS', JTX.TAX_AMOUNT,
                                         0) from dual) +
                                 (select DECODE (JTM.TAX_TYPE,
                                         'CUSTOMS_EDUCATION_CESS', JTX.TAX_AMOUNT,
                                         0) from dual))
                                   ADDNEXCISE,
                                 ((select DECODE (
                                    JTM.TAX_TYPE,
                                    'CVD_EDUCATION_CESS',                              ----- TEST BY ELANGO  -----------
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0)
                                 from dual)  +
                                 (select DECODE (
                                    JTM.TAX_TYPE,
                                    'SERVICE_EDUCATION_CESS',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0)
                                 from dual)
                                 )
                                 ADDNEXCISE_COST,  */
                                 ( (SELECT   DECODE (JTM.TAX_TYPE,
                                                     'EXCISE_EDUCATION_CESS',
                                                     JTX.TAX_AMOUNT,
                                                     0)
                                      FROM   DUAL)
                                  + (SELECT   DECODE (JTM.TAX_TYPE,
                                                      'EXCISE_SH_EDU_CESS',
                                                      JTX.TAX_AMOUNT,
                                                      0)
                                       FROM   DUAL))
                                    ADDNEXCISE,
                                 ( (SELECT   DECODE (
                                                JTM.TAX_TYPE,
                                                'EXCISE_EDUCATION_CESS',
                                                DECODE (
                                                   NVL (JTX.MODVAT_FLAG, 'N'),
                                                   'Y',
                                                   JTX.TAX_AMOUNT
                                                   * (100
                                                      - NVL (
                                                           JTM.MOD_CR_PERCENTAGE,
                                                           0
                                                        ))
                                                   / 100,
                                                   JTX.TAX_AMOUNT
                                                ),
                                                0
                                             )
                                      FROM   DUAL)
                                  + (SELECT   DECODE (
                                                 JTM.TAX_TYPE,
                                                 'EXCISE_SH_EDU_CESS',
                                                 DECODE (
                                                    NVL (JTX.MODVAT_FLAG, 'N'),
                                                    'Y',
                                                    JTX.TAX_AMOUNT
                                                    * (100
                                                       - NVL (
                                                            JTM.MOD_CR_PERCENTAGE,
                                                            0
                                                         ))
                                                    / 100,
                                                    JTX.TAX_AMOUNT
                                                 ),
                                                 0
                                              )
                                       FROM   DUAL))
                                    ADDNEXCISE_COST,
                                 DECODE (JTM.TAX_TYPE, 'CST', JTX.TAX_AMOUNT, 0)
                                    CST,
                                 DECODE (
                                    JTM.TAX_TYPE,
                                    'CST',           ------ ELANGO  ----------
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    CST_COST,
                                 DECODE (JTM.TAX_TYPE,
                                         'VALUE ADDED TAX', JTX.TAX_AMOUNT,
                                         0)
                                    LST,
                                 DECODE (
                                    JTM.TAX_TYPE,
                                    'VALUE ADDED TAX',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    LST_COST,
                                 DECODE (JTM.TAX_TYPE,
                                         'Customs', JTX.TAX_AMOUNT,
                                         0)
                                    CUSTOMS,
                                 DECODE (
                                    JTM.TAX_TYPE,
                                    'Customs',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    CUSTOMS_COST,
                                 DECODE (JTM.TAX_TYPE,
                                         'Additional CVD', JTX.TAX_AMOUNT,
                                         0)
                                    CVD,
                                 DECODE (
                                    JTM.TAX_TYPE,
                                    'Additional CVD',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    CVD_COST,
                                 DECODE (JTM.TAX_TYPE,
                                         'Freight', JTX.TAX_AMOUNT,
                                         0)
                                    FREIGHT,
                                 DECODE (
                                    JTM.TAX_TYPE,
                                    'Freight',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    FREIGHT_COST,
                                 DECODE (UPPER (TRIM (JTM.TAX_TYPE)),
                                         'INSURANCE', JTX.TAX_AMOUNT,
                                         0)
                                    INSURANCE,
                                 DECODE (
                                    UPPER (TRIM (JTM.TAX_TYPE)),
                                    'INSURANCE',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    INSURANCE_COST,
                                 DECODE (UPPER (TRIM (JTM.TAX_TYPE)),
                                         'OCTRAI', JTX.TAX_AMOUNT,
                                         0)
                                    OCTRAI,
                                 DECODE (
                                    UPPER (TRIM (JTM.TAX_TYPE)),
                                    'OCTRAI',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    OCTRAI_COST,
                                 DECODE (JTM.TAX_TYPE,
                                         'Service', JTX.TAX_AMOUNT,
                                         0)
                                    SERVICE,
                                 DECODE (
                                    JTM.TAX_TYPE,
                                    'Service',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'), ----  MODIFIED BY ELANGOVAN    --------------
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    SERVICE_COST,
                                 DECODE (JTM.TAX_TYPE,
                                         'Other', JTX.TAX_AMOUNT,
                                         0)
                                    OTHER,
                                 DECODE (
                                    JTM.TAX_TYPE,
                                    'Other',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    OTHER_COST,
                                 DECODE (
                                    UPPER (TRIM (JTM.TAX_TYPE)),
                                    'ADDL. EXCISE',
                                    DECODE (
                                       NVL (JTX.MODVAT_FLAG, 'N'),
                                       'Y',
                                         JTX.TAX_AMOUNT
                                       * (100 - NVL (JTM.MOD_CR_PERCENTAGE, 0))
                                       / 100,
                                       JTX.TAX_AMOUNT
                                    ),
                                    0
                                 )
                                    ADDITIONAL_EXCISE_COST,
                                 T.TRANSACTION_DATE,
                                 T.UNIT_OF_MEASURE,
                                 T.CURRENCY_CODE,
                                 T.SUBINVENTORY,
                                 PH.SEGMENT1 PO_NUMBER,
                                 PL.LINE_NUM PO_LINE,
                                 H.VENDOR_SITE_ID,
                                 H.SHIPMENT_NUM,
                                 H.ATTRIBUTE7 VEHICAL_NO,
                                 H.NUM_OF_CONTAINERS GR_NO,
                                 PH.AGENT_ID AGENTID,
                                 /*(SELECT   SUM (NVL (JTX.TAX_AMOUNT, 0))
                                    FROM   APPS.JAI_PO_TAXES JTX--, APPS.JAI_CMN_TAXES_ALL J
                                   WHERE   JTX.PO_HEADER_ID = T.PO_HEADER_ID
                                     AND   JTX.PO_LINE_ID = T.PO_LINE_ID
                                     AND   JTX.LINE_LOCATION_ID = T.PO_LINE_LOCATION_ID
                                           -- AND JTX.THIRD_PARTY_FLAG = 'N'
                                            --AND JTX.TAX_ID = J.TAX_ID
                                            ) */
                                 (SELECT   SUM (NVL (tax_amount, 0))
                                    FROM   jai_rcv_line_taxes
                                   WHERE   shipment_header_id =
                                              H.shipment_header_id)
                                    TOTAL_TAX_AMT,
                                 /*(SELECT   SUM (NVL (JTX.TAX_AMOUNT, 0))
                                    FROM   APPS.JAI_PO_TAXES JTX, APPS.JAI_CMN_TAXES_ALL J
                                   WHERE   JTX.PO_HEADER_ID = T.PO_HEADER_ID
                                     AND   JTX.PO_LINE_ID =  T.PO_LINE_ID
                                     AND   JTX.TAX_ID = J.TAX_ID
                                     AND   JTX.LINE_LOCATION_ID = T.PO_LINE_LOCATION_ID
                                     AND   j.MOD_CR_PERCENTAGE IS NULL or j.MOD_CR_PERCENTAGE != 100
                                           ) */
                                 (SELECT   SUM (NVL (tax_amount, 0))
                                    FROM   jai_rcv_line_taxes
                                   WHERE   shipment_header_id =
                                              H.shipment_header_id
                                           AND modvat_flag = 'N')
                                    TOTAL_VALUE
                          FROM   RCV_TRANSACTIONS T,
                                 RCV_SHIPMENT_HEADERS H,
                                 RCV_SHIPMENT_LINES L,
                                 MTL_SYSTEM_ITEMS I,
                                 HR_ORGANIZATION_UNITS O,
                                 PO_HEADERS_ALL PH,
                                 PO_LINES_ALL PL,
                                 PO_VENDORS V,
                                 PO_VENDOR_SITES_ALL VS,
                                 JAI_RCV_LINE_TAXES JTX,
                                 JAI_RCV_LINES JL,
                                 JAI_CMN_TAXES_ALL JTM
                         WHERE       H.SHIPMENT_HEADER_ID = L.SHIPMENT_HEADER_ID
                                 AND T.SHIPMENT_HEADER_ID = H.SHIPMENT_HEADER_ID
                                 AND T.SHIPMENT_LINE_ID = L.SHIPMENT_LINE_ID
                                 AND L.ITEM_ID = I.INVENTORY_ITEM_ID(+)
                                 AND L.TO_ORGANIZATION_ID = I.ORGANIZATION_ID(+)
                                 AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
                                 AND L.PO_HEADER_ID = PH.PO_HEADER_ID
                                 AND L.PO_HEADER_ID = PL.PO_HEADER_ID
                                 AND L.PO_LINE_ID = PL.PO_LINE_ID
                                 AND V.VENDOR_ID = VS.VENDOR_ID
                                 AND PH.VENDOR_ID = V.VENDOR_ID
                                 AND PH.VENDOR_SITE_ID = VS.VENDOR_SITE_ID
                                 AND JTX.SHIPMENT_HEADER_ID(+) =
                                       JL.SHIPMENT_HEADER_ID
                                 AND JTX.SHIPMENT_LINE_ID(+) =
                                       JL.SHIPMENT_LINE_ID
                                 AND L.SHIPMENT_HEADER_ID = JL.SHIPMENT_HEADER_ID
                                 AND L.SHIPMENT_LINE_ID = JL.SHIPMENT_LINE_ID
                                 AND JTM.TAX_ID(+) = JTX.TAX_ID
                                 AND T.ORGANIZATION_ID = O.ORGANIZATION_ID
                                                                  AND TRUNC (T.TRANSACTION_DATE) BETWEEN NVL (
                                                                                                            :S_DATE,
                                                                                                            T.TRANSACTION_DATE
                                                                                                         )
                                                                                                     AND  NVL (
                                                                                                             :E_DATE,
                                                                                                             T.TRANSACTION_DATE
                                                                                                          )
                                                                  AND O.ORGANIZATION_ID =
                                                                        NVL (:WAREHOUSE, O.ORGANIZATION_ID)
                                                                  AND V.VENDOR_ID = NVL (:VENDOR_ID, V.VENDOR_ID)
                                                         AND SUBSTR(I.INVENTORY_ITEM_ID,1,8) BETWEEN nvl(:ITEM_NO_FROM,I.INVENTORY_ITEM_ID) AND nvl(:ITEM_NO_TO,I.INVENTORY_ITEM_ID)
                                 --&ITEM_VALUE
                               --  AND H.RECEIPT_NUM = '30126500084'
                                 AND T.TRANSACTION_TYPE IN
                                          ('RECEIVE', 'RETURN TO VENDOR'))
            GROUP BY   ITEM,
                       RECEIPT_NUM,
                       NAME,
                       TRANS_TYPE,
                       DESCRIPTION,
                       TRANSACTION_DATE,
                       VENDOR_NAME,
                       VENDORCODE,
                       VENDOR_ID,
                       VENDOR_ADDRESS,
                       UNIT_OF_MEASURE,
                       SUBINVENTORY,
                       PO_NUMBER,
                       PO_LINE,
                       SHIPPED_DATE,
                       QUANTITY,
                       CREATION_DATE,
                       WAYBILL_AIRBILL_NUM,
                       PACKING_SLIP,
                       VENDOR_SITE_ID,
                       SHIPMENT_NUM,
                       VEHICAL_NO,
                       GR_NO,
                       TOTAL_TAX_AMT,
                       TOTAL_VALUE,
                       LINE_NUM      ----------------ADDED BY HARI ON 7-DEC-08
                               )
ORDER BY   PO_NUMBER