Query to get Invoice Tax Amount in Oracle Apps R12 - Orapoint Orapoint.com

Overview: This SQL query is use to get AP Invoice Tax amount such as VAT amount with line description, line amount, trx number in Oracle R12 Applications.

SELECT  CUSTOMER_TRX_ID,
         trx_number,
         LINE_DESCR,
         QUANTITY,
         UNIT_PRICE,
         LINE_AMOUNT,
         VAT_AMOUNT,
         (UNIT_PRICE+VAT_AMOUNT)INCL_VAT,  
          TRX_TYPE       
 FROM (
 SELECT RCT.CUSTOMER_TRX_ID,
        rct.trx_number,
        RCTL.DESCRIPTION LINE_DESCR,
        NVL (RCTL.QUANTITY_INVOICED, 0) QUANTITY,
        NVL (RCTL.UNIT_SELLING_PRICE, 0) UNIT_PRICE,
        (NVL (RCTL.QUANTITY_INVOICED, 0) * NVL (RCTL.UNIT_SELLING_PRICE, 0))
           LINE_AMOUNT,
 (SELECT NVL(RCTL1.EXTENDED_AMOUNT,0) 
           FROM RA_CUSTOMER_TRX_LINES_ALL RCTL1
          WHERE     1 = 1
                AND RCTL1.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
                AND RCTL1.LINE_TYPE = 'TAX'
                and RCTL.CUSTOMER_TRX_LINE_ID = RCTL1.LINK_TO_CUST_TRX_LINE_ID) VAT_AMOUNT,
                (select DECODE(NAME,'Projects Credit Memo','Credit Note','Tax Invoice') from ra_cust_trx_types_all where CUST_TRX_TYPE_ID = rct.CUST_TRX_TYPE_ID and rownum=1)TRX_TYPE  
   FROM RA_CUSTOMER_TRX_ALL RCT, RA_CUSTOMER_TRX_LINES_ALL RCTL--, ra_cust_trx_types_all rcta
  WHERE     1 = 1
        AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
        AND RCTL.LINE_TYPE = 'LINE'
        )
 WHERE CUSTOMER_TRX_ID = :CUSTOMER_TRX_ID2;