Home » Developer & Programmer » Reports & Discoverer » Oracle payables Tds expensewise report issue (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Oracle payables Tds expensewise report issue [message #583441] Tue, 30 April 2013 23:24 Go to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
SELECT 
a.invoice_num, 
b.vendor_name, 
SUM(a.invoice_amount) 
invoice_amount, 
SUM(c.amount) 
taxable_amt, 
f.description, 
g.section_code 
TDS_SECTION, 
d.code_combination_id, 
SUM( 
( c.amount * g.tax_rate / 100 ) - ( 
( c.amount * g.tax_rate / 100 ) * Nvl(g.cess_rate, 0) / g.tax_rate ) - ( ( c.amount * g.tax_rate / 100 ) * Nvl(g.sh_cess_rate, 0) / g.tax_rate ) - (
( c.amount * g.tax_rate / 100 ) * Nvl(g.surcharge_rate, 0) / g.tax_rate ))AMT_OF_TDS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.cess_rate, 0) / g.tax_rate) 
AMT_OF_CESS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.sh_cess_rate, 0) / g.tax_rate) 
AMT_OF_SH_CESS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.surcharge_rate, 0) / g.tax_rate) 
AMT_OF_SURCHARGE, 
SUM(c.amount * g.tax_rate / 100) 
tds_total, 
( SUM(c.amount) - SUM(c.amount * g.tax_rate / 100) ) 
payable_amt, 
h.tds_vendor_type_lookup_code 
FROM   ap_invoices_all a, 
       po_vendors b, 
       ap_invoice_distributions_all c, 
       gl_code_combinations d, 
       fnd_flex_value_sets e, 
       fnd_flex_values_vl f, 
       jai_cmn_taxes_all g, 
       jai_ap_tds_vendor_hdrs h 
WHERE  1 = 1 
       AND a.invoice_id = c.invoice_id 
       AND a.invoice_num LIKE '10 lakh%' 
       AND a.vendor_id = b.vendor_id 
       AND c.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS' 
       AND c.dist_code_combination_id = d.code_combination_id 
       AND d.segment5 = f.flex_value 
       AND e.flex_value_set_id = f.flex_value_set_id 
       AND e.flex_value_set_name = 'STL_GL_NAT_ACCOUNT_VS' 
       AND a.invoice_id = c.invoice_id 
       AND a.org_id = c.org_id 
       AND a.org_id = :p_org_id 
       AND a.gl_date BETWEEN :p_from_date AND :p_to_date 
       AND g.tax_id = c.global_attribute1 
       AND a.vendor_id = h.vendor_id 
       AND a.vendor_site_id = h.vendor_site_id(+) 
       AND invoice_type_lookup_code NOT IN( 'CREDIT', 'DEBIT' ) 
GROUP  BY a.invoice_num, 
          b.vendor_name, 
          f.description, 
          g.section_code, 
          h.tds_vendor_type_lookup_code, 
          d.code_combination_id 
UNION 
SELECT 
b.vendor_name, 
a.invoice_num, 
SUM(a.invoice_amount) 
invoice_amount, 
SUM(c.amount) 
taxable_amt, 
f.description, 
g.section_code 
TDS_SECTION, 
d.code_combination_id, 
SUM( 
( c.amount * g.tax_rate / 100 ) - ( 
( c.amount * g.tax_rate / 100 ) * Nvl(g.cess_rate, 0) / g.tax_rate ) - ( ( c.amount * g.tax_rate / 100 ) * Nvl(g.sh_cess_rate, 0) / g.tax_rate ) - (
( c.amount * g.tax_rate / 100 ) * Nvl(g.surcharge_rate, 0) / g.tax_rate ))AMT_OF_TDS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.cess_rate, 0) / g.tax_rate) 
AMT_OF_CESS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.sh_cess_rate, 0) / g.tax_rate) 
AMT_OF_SH_CESS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.surcharge_rate, 0) / g.tax_rate) 
AMT_OF_SURCHARGE, 
SUM(c.amount * g.tax_rate / 100) 
tds_total, 
( SUM(c.amount) - SUM(c.amount * g.tax_rate / 100) ) 
payable_amt, 
h.tds_vendor_type_lookup_code 
FROM   ap_invoices_all a, 
       po_vendors b, 
       ap_invoice_distributions_all c, 
       gl_code_combinations d, 
       fnd_flex_value_sets e, 
       fnd_flex_values_vl f, 
       jai_cmn_taxes_all g, 
       jai_ap_tds_invoices p, 
       jai_ap_tds_vendor_hdrs h 
WHERE  1 = 1 
       AND a.invoice_id = c.invoice_id 
       AND a.vendor_id = b.vendor_id 
       AND c.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS' 
       AND c.dist_code_combination_id = d.code_combination_id 
       AND d.segment5 = f.flex_value 
       AND e.flex_value_set_id = f.flex_value_set_id 
       AND e.flex_value_set_name = 'STL_GL_NAT_ACCOUNT_VS' 
       AND a.invoice_id = c.invoice_id 
       AND a.org_id = c.org_id 
       AND a.org_id = :p_org_id 
       AND a.gl_date BETWEEN :p_from_date AND :p_to_date 
       AND c. global_attribute1 IS NULL 
       AND p.invoice_id = a.invoice_id 
       AND g.tax_id = p.tds_tax_id 
       AND a.vendor_id = h.vendor_id 
       AND a.vendor_site_id = h.vendor_site_id(+) 
       AND invoice_type_lookup_code NOT IN( 'CREDIT', 'DEBIT' ) 
       AND a.invoice_num LIKE '10 lakh%' 
GROUP  BY b.vendor_name, 
          a.invoice_num, 
          f.description, 
          g.section_code, 
          h.tds_vendor_type_lookup_code, 
          d.code_combination_id 



i have that query now i want the taxable amount should be = 0 and and amount of tds = 0 of the invoice which have status Cancelled
else the ouput is alright.there is only some record which is not showing hte data.

how can i found the status as there is no table where status=cancelled is going.
and when i opened the form to check this out it is not giving me the right way where to look at i have gone through the procedures there but all in vain.


help me

thanks in advance
Re: Oracle payables Tds expensewise report issue [message #583442 is a reply to message #583441] Tue, 30 April 2013 23:53 Go to previous messageGo to next message
bhawnakaamra
Messages: 66
Registered: March 2013
Location: delhi
Member
I am serching for the particular invoice which have invoice num = '10 lakh'

and i am getting tds amount 1000 and taxable amount 100000 .

and i want 0 at both places.
Re: Oracle payables Tds expensewise report issue [message #583451 is a reply to message #583442] Wed, 01 May 2013 02:41 Go to previous message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what's stopping you from getting 0 in both places?
Previous Topic: How to create report
Next Topic: Reports based on data
Goto Forum:
  


Current Time: Mon Dec 22 16:26:00 CST 2014

Total time taken to generate the page: 0.14152 seconds