AR Transactions India Localization Tax Query in Oracle apps

AR Transactions India Localization Tax Query in Oracle apps

SELECT TO_CHAR (trx_date, 'DD-MON-YYYY') ar_posting_date,
       TO_CHAR (jatt.excise_invoice_date, 'DD-MON-YYYY') excise_invoice_date,
       rcta.trx_number ar_trx_number, flv.meaning document_type,
       jatt.excise_invoice_no, hca.account_number cust_bill_to,
       hp.party_name cust_name, rctla.extended_amount basic_amt,
       (SELECT NVL (jattt.tax_amount, 0)
          FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
         WHERE jattt.tax_id = jcta.tax_id
           AND UPPER (jcta.tax_type) = 'EXCISE'
           AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
               excise_amt,       (SELECT NVL (jattt.tax_amount, 0)
          FROM jai_ar_trx_tax_lines jattt,
               jai_cmn_taxes_all jcta
         WHERE jattt.tax_id = jcta.tax_id
           AND UPPER (jcta.tax_type) = 'CST'
           AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
               sales_tax_amt,       (  (SELECT NVL (SUM (extended_amount), 0)
             FROM ra_customer_trx_lines_all
            WHERE customer_trx_id = rcta.customer_trx_id)
        - (SELECT NVL (jattt.tax_amount, 0)
             FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
            WHERE jattt.tax_id = jcta.tax_id
              AND UPPER (jcta.tax_type) = 'TCS'
              AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
       ) tcs_base_amt,
       (SELECT NVL (jattt.tax_amount, 0)
          FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
         WHERE jattt.tax_id = jcta.tax_id
           AND UPPER (jcta.tax_type) = 'TCS'
           AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)
                                                                      tcs_amt,
       (SELECT NVL (SUM (extended_amount), 0)
          FROM ra_customer_trx_lines_all
         WHERE customer_trx_id = rcta.customer_trx_id) tot_inv_amt,
       (SELECT pan_no
          FROM jai_cmn_cus_addresses
         WHERE customer_id = rcta.bill_to_customer_id
           AND ROWNUM = 1) party_pan_no,
       NULL challan_no,
       (SELECT tax_descr
          FROM jai_ar_trx_tax_lines jattt, jai_cmn_taxes_all jcta
         WHERE jattt.tax_id = jcta.tax_id
           AND UPPER (jcta.tax_type) = 'TCS'
           AND jattt.link_to_cust_trx_line_id = rctla.customer_trx_line_id)tax_desc,
       NULL tcs_pay_voucher_no
  FROM ra_customer_trx_all rcta,       ra_cust_trx_types_all rctt,
       fnd_lookup_values flv,       ra_customer_trx_lines_all rctla,
       jai_ar_trx_lines jatt,       hz_cust_accounts hca,
       hz_parties hp
 WHERE rcta.cust_trx_type_id = rctt.cust_trx_type_id
   AND rcta.customer_trx_id = rctla.customer_trx_id
   AND flv.lookup_type = 'TRX TYPES'
   AND rctt.TYPE = flv.lookup_code
   AND rctla.line_type = 'LINE'
   AND jatt.customer_trx_id = rcta.customer_trx_id
   AND jatt.customer_trx_line_id = rctla.customer_trx_line_id
   AND rcta.bill_to_customer_id = hca.cust_account_id
   AND hca.party_id = hp.party_id
   AND jatt.excise_invoice_no IS NOT NULL
   AND TO_CHAR (trx_date, 'DD-MON-YYYY') BETWEEN
    NVL (TO_CHAR (:p_from_date,
        'DD-MON-YYYY'
         ),
         TO_DATE (SYSDATE,
         'DD-MON-YYYY'
            )
         ) AND NVL (TO_CHAR (:p_to_date,  'DD-MON-YYYY' ), TO_DATE (SYSDATE, 'DD-MON-YYYY' ) )
   AND hca.cust_account_id = NVL (:p_cust_name, hca.cust_account_id)