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 ------------------
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 ------------------
No comments:
Post a Comment