Oracle Receivable Query for AR Sales TAX

BEGIN

  FND_CLIENT_INFO.SET_ORG_CONTEXT(204);

END;

SELECT

 asp.tax_registration_number,

 cta.trx_number,

 cta.gd_gl_date,

 cta.rac_ship_to_customer_name,

 cta.ctt_type_name,

 SELECT sum(RCTLA.NET_EXTENDED_AMOUNT)

 FROM RA_CUSTOMER_TRX_LINES_V RCTLA

 HERE RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

  AND RCTLA.LINE_TYPE in ('LINE','TAX')  ) total,

 SELECT RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE

 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,

      AR_VAT_TAX_ALL_B AVTAB

 WHERE  AVTAB.TAX_CODE='VAT20'

 AND    AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID

 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

 AND    RCTLA.LINE_TYPE='LINE')VAT20_AMOUNT,

 (SELECT RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE*AVTAB.TAX_RATE/100

 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,

      AR_VAT_TAX_ALL_B AVTAB

 WHERE  AVTAB.TAX_CODE='VAT20'

 AND    AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID

 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

 AND    RCTLA.LINE_TYPE='LINE') VAT20,

 (SELECT RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE

 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,

      AR_VAT_TAX_ALL_B AVTAB

 WHERE  AVTAB.TAX_CODE='VAT'

 AND    AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID

 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

 AND    RCTLA.LINE_TYPE='LINE') VAT0_AMOUNT,

 (SELECT RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE*AVTAB.TAX_RATE/100

 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,

      AR_VAT_TAX_ALL_B AVTAB

 WHERE  AVTAB.TAX_CODE='VAT'

 AND    AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID

 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

 AND    RCTLA.LINE_TYPE='LINE') VAT0,

 (SELECT SUM(RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE)

 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,

      AR_VAT_TAX_ALL_B AVTAB

 WHERE  AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID

 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

 AND    RCTLA.LINE_TYPE='LINE') LINE_TOTAL,

 (SELECT SUM(RCTLA.QUANTITY*RCTLA.NET_UNIT_SELLING_PRICE*AVTAB.TAX_RATE/100)

 FROM RA_CUSTOMER_TRX_LINES_V RCTLA,

      AR_VAT_TAX_ALL_B AVTAB

 WHERE  AVTAB.VAT_TAX_ID=RCTLA.VAT_TAX_ID

 AND    RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

 AND    RCTLA.LINE_TYPE='LINE') VAT_TOTAL

FROM ra_customer_trx_v rcta,

     ar_system_parameters asp

WHERE rcta.trx_number='11794'

AND   rcta.of_organization_id=asp.org_id