TDS REPORT in Oracle apps EBS R12

TDS Report Layout 










Report Builder :
Query Part:

Q_1 :

-- Query Written in Q_1 is here --

select * from
(SELECT DISTINCT a.invoice_to_tds_authority_num, a.tax_id tds_tax_id,
                -------a.taxable_amount,  ----Commented For negetive sign show in  taxable_amount
                  DECODE (a.tds_event,
                                   'PREPAYMENT APPLICATION', - (a.taxable_amount),
                                     a.taxable_amount
                                  ) taxable_amount,
                  ----------a.tax_amount, ------------ Commented on 01-FEB-2011  because not to calculate TAX where Taxable Amt is ZERO.
                ------  decode(a.taxable_amount, 0,0,a.tax_amount)  tax_amount,    ------ Added on 01-FEB-2011  because not to calculate TAX where Taxable Amt is ZERO.
                decode(a.taxable_amount, 0,0, 
                                   DECODE (a.tds_event,'PREPAYMENT APPLICATION',
                                                      - (a.tax_amount),
                                                      a.tax_amount
                                                   )
                            )  tax_amount,
                b.section_code tds_section,
                a.tax_rate tds_tax_rate, ss.NAME, ss.organization_id,aa.GL_DATE,
                DECODE (a.tds_event,
                        'PREPAYMENT APPLICATION', - (a.taxable_amount),
                        a.taxable_amount
                       ) inv_amount,
                DECODE (a.tds_event,
                        'PREPAYMENT APPLICATION', 'PREPAY APPLY',
                        'PREPAYMENT UNAPPLICATION', 'PREPAY UNAPPLY',
                        'ITEM'
                       ) line_type,
                bb.vendor_name, aa.invoice_id, aa.invoice_num,
                aa.invoice_date, aa.invoice_currency_code, aa.invoice_amount,
                aa.vendor_id, aa.vendor_site_id,
                aa.cancelled_date,
                aa.cancelled_by, jj.tds_vendor_type_lookup_code
           FROM jai_ap_tds_thhold_trxs a,
                jai_ap_tds_thhold_grps b,
                ap_invoices_all aa,
                po_vendors bb,
                hr_organization_units ss,
                ja_in_vendor_tds_info_hdr jj
          WHERE TRUNC (aa.GL_DATE) BETWEEN nvl(:p_from_date,aa.GL_DATE) AND nvl(:p_to_date,aa.GL_DATE)
            AND ss.organization_id = :p_org_id
            AND aa.vendor_id = bb.vendor_id
            AND bb.vendor_id = jj.vendor_id
            AND a.threshold_grp_id = b.threshold_grp_id
            and aa.ORG_ID(+)=ss.organization_id
            AND aa.invoice_id=a.invoice_id
            AND aa.invoice_id IN (
                   SELECT DISTINCT aa.attribute1
                              FROM ap_invoices_all aa
                             WHERE aa.attribute_category =
                             'India Original Invoice for TDS'
                               AND aa.attribute1 IS NOT NULL)
                     )
              WHERE inv_amount <> 0   and  cancelled_date is null
           -- AND tds_vendor_type_lookup_code = 'INDIVIDUAL-IND'
             ORDER BY  vendor_name, invoice_date, invoice_num
           
--------------- End of Q_1 Part ------------------