Oracle Apps SQL Query to Fetch GST TDS Calculation
Hi Friends, in this post , I will share you the Oracle Apps SQL Query to Fetch GST TDS Calculation. We all know that in India , GST has been implemented and due to which all the India Localizations tables has been changed due to which all the past Localization reports tables and reports has been obsoleted. I am sharing you the new GST TDS Calculation query to fetch the TDS details.
Oracle Apps SQL Query to Fetch GST TDS Calculation Report
SQL Query: -
hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')) gl_date,
ap_inv_all.invoice_amount,
base_amt_tds tax_on,
(SELECT ABS (SUM (aa.amount))
FROM APPS.ap_invoice_distributions_all aa
WHERE 1 = 1
AND ap_inv_all.invoice_id = aa.invoice_id
AND aa.line_type_lookup_code = 'PREPAY'
AND aa.global_attribute1 IS NOT NULL
AND ap_inv_all.org_id = aa.org_id) prepay_on,
MAX (ap_ida.global_attribute1) tax_id, tax_tds.invoice_num tds_inv_no,
tax_tds.gl_date tds_inv_gl_date,
tax_tds.doc_sequence_value tds_voucher_no,
tax_tds.invoice_amount tds_amount, tax_tds.base_amt_tds, jit.tax_name,
jit.tax_rate, jath.pan_no, jit.section_code,
jath.tds_vendor_type_lookup_code, jit.section_type
FROM APPS.ap_invoices_all ap_inv_all,
APPS.ap_invoice_distributions_all ap_ida,
APPS.hz_parties hp,
APPS.fnd_user fu,
APPS.po_vendors pov,
APPS.jai_cmn_taxes_all jit,
APPS.jai_ap_tds_vendor_hdrs jath,
(SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
jti.invoice_amount base_amt_tds
FROM APPS.jai_ap_tds_invoices jti, APPS.ap_invoices_all ap_inv_all
WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
AND ap_inv_all.attribute1 IS NOT NULL
AND vendor_id = '2'
) tax_tds
WHERE 1 = 1
AND ap_inv_all.invoice_id = ap_ida.invoice_id
AND ap_ida.line_type_lookup_code != 'PREPAY'
AND ap_ida.global_attribute1 IS NOT NULL
AND ap_inv_all.org_id = ap_ida.org_id
AND tax_tds.attribute1 = to_char(ap_inv_all.invoice_id)
AND NVL (TO_CHAR (tax_tds.tds_tax_id), ap_ida.global_attribute1) =
ap_ida.global_attribute1
AND pov.vendor_id = ap_inv_all.vendor_id
AND fu.user_id = ap_inv_all.created_by
AND pov.party_id = hp.party_id
AND ap_inv_all.org_id = ap_ida.org_id
AND ap_inv_all.cancelled_date IS NULL
AND jath.vendor_site_id = ap_inv_all.vendor_site_id
AND jit.tax_id = ap_ida.global_attribute1
AND ap_ida.match_status_flag = 'A'
AND UPPER (tax_tds.invoice_num) NOT LIKE '%RTN%'
GROUP BY ap_inv_all.invoice_id,
ap_inv_all.invoice_num,
ap_inv_all.invoice_date,
TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR'),
ap_inv_all.invoice_amount,
ap_ida.global_attribute1,
tax_tds.invoice_num,
tax_tds.gl_date,
tax_tds.doc_sequence_value,
tax_tds.invoice_amount,
tax_tds.base_amt_tds,
pov.vendor_name,
hp.address1,
hp.address2,
hp.address3,
hp.address4,
hp.city,
hp.postal_code,
hp.state,
pov.segment1,
jit.tax_name,
jit.tax_rate,
jath.pan_no,
jit.section_code,
jath.tds_vendor_type_lookup_code,
jit.section_type
ap_inv_all.org_id
UNION ALL
SELECT ap_inv_all.org_id, pov.vendor_name, hp.address1, hp.address2,
hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')) gl_date,
ap_inv_all.invoice_amount,
base_amt_tds tax_on,
(SELECT ABS (SUM (aa.amount))
FROM APPS.ap_invoice_distributions_all aa
WHERE 1 = 1
AND ap_inv_all.invoice_id = aa.invoice_id
AND aa.line_type_lookup_code = 'PREPAY'
AND aa.line_type_lookup_code = 'ITEM'
AND aa.global_attribute1 IS NOT NULL
AND ap_inv_all.org_id = aa.org_id) prepay_on,
MAX (ap_ida.global_attribute1) tax_id, tax_tds.invoice_num tds_inv_no,
tax_tds.gl_date tds_inv_gl_date,
tax_tds.doc_sequence_value tds_voucher_no,
tax_tds.invoice_amount tds_amount, tax_tds.base_amt_tds, NULL tax_name,
tax_tds.tax_rate, jpl.registration_number, tax_tds.section_code,
(select REPORTING_CODE from APPS.JAI_reporting_associations_v JRA,APPS.JAI_PARTY_REGS_V JPR
where REGIME_CODE in ('TDS','GST')
AND ENTITY_ID=PARTY_REG_ID
AND PARTY_ID=pov.vendor_id
AND ORG_ID=ap_inv_all.org_id
AND REPORTING_TYPE_NAME='Vendor Type'
AND EFFECTIVE_TO IS NULL
AND PARTY_SITE_ID=ap_inv_all.VENDOR_SITE_ID
AND ROWNUM=1) tds_vendor_type_lookup_code, NULL section_code
FROM APPS.ap_invoices_all ap_inv_all,
APPS.ap_invoice_distributions_all ap_ida,
APPS.hz_parties hp,
APPS.fnd_user fu,
APPS.po_vendors pov,
APPS.jai_party_reg_lines jpl,
APPS.jai_party_regs jpr,
(SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
jti.invoice_amount base_amt_tds,
jti.invoice_id tds_invoice_id,
jtrl.tax_rate_percentage tax_rate,
jti.tds_section section_code
FROM APPS.jai_ap_tds_invoices jti,
APPS.ap_invoices_all ap_inv_all,
APPS.jai_tax_categories jtc,
APPS.jai_tax_category_lines jtl,
APPS.jai_tax_rates jtr,
APPS.jai_tax_rate_details jtrl
WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
AND jtc.tax_category_id = jtl.tax_category_id
AND jtc.tax_category_id = jti.tax_category_id
AND jtc.org_id = ap_inv_all.org_id
AND jtc.effective_to IS NULL
AND jtrl.effective_to IS NULL
AND jtrl.tax_rate_detail_code = 'RATE_DETAILS'
AND jtl.tax_rate_id = jtr.tax_rate_id
AND jtl.tax_type_id = jtr.tax_type_id
AND jtr.tax_rate_id = jtrl.tax_rate_id
AND vendor_id = '2'
) tax_tds
WHERE 1 = 1
AND ap_inv_all.invoice_id = ap_ida.invoice_id
AND ap_ida.line_type_lookup_code != 'PREPAY'
AND ap_inv_all.org_id = ap_ida.org_id
AND tax_tds.tds_invoice_id = ap_inv_all.invoice_id
AND pov.vendor_id = ap_inv_all.vendor_id
AND fu.user_id = ap_inv_all.created_by
AND pov.party_id = hp.party_id
AND ap_inv_all.org_id = ap_ida.org_id
AND ap_inv_all.cancelled_date IS NULL
AND jpr.party_reg_id = jpl.party_reg_id
AND ap_inv_all.org_id = jpr.org_id
AND jpl.registration_type_code = 'PAN'
AND jpl.effective_to IS NULL
AND ap_inv_all.vendor_id = jpr.party_id
AND ap_inv_all.vendor_site_id = jpr.party_site_id
AND jpr.party_type_code = 'THIRD_PARTY_SITE'
AND jpr.supplier_flag = 'Y'
AND ap_ida.match_status_flag = 'A'
AND UPPER (tax_tds.invoice_num) NOT LIKE '%RTN%'
GROUP BY ap_inv_all.invoice_id,
ap_inv_all.invoice_num,
ap_inv_all.invoice_date,
TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR'),
ap_inv_all.invoice_amount,
ap_ida.global_attribute1,
tax_tds.invoice_num,
tax_tds.gl_date,
tax_tds.doc_sequence_value,
tax_tds.invoice_amount,
tax_tds.base_amt_tds,
pov.vendor_name,
hp.address1,
hp.address2,
hp.address3,
hp.address4,
hp.city,
hp.postal_code,
hp.state,
pov.segment1,
tax_tds.tax_rate,
jpl.registration_number,
tax_tds.section_code,
ap_inv_all.org_id,
pov.vendor_id,
ap_inv_all.VENDOR_SITE_ID
UNION ALL
SELECT ap_inv_all.org_id, pov.vendor_name, hp.address1, hp.address2,
hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')),
ap_inv_all.invoice_amount,
(SELECT SUM (aa.amount)
FROM APPS.ap_invoice_distributions_all aa
WHERE 1 = 1
AND ap_inv_all.invoice_id = aa.invoice_id
AND aa.line_type_lookup_code != 'PREPAY'
AND aa.global_attribute1 IS NOT NULL
AND ap_inv_all.org_id = aa.org_id) tax_on,
(SELECT ABS (SUM (aa.amount))
FROM APPS.ap_invoice_distributions_all aa
WHERE 1 = 1
AND ap_inv_all.invoice_id = aa.invoice_id
AND aa.line_type_lookup_code = 'PREPAY'
AND aa.global_attribute1 IS NOT NULL
AND ap_inv_all.org_id = aa.org_id) prepay_on,
MAX (ap_ida.global_attribute2) tax_id, wct.invoice_num tds_inv_no,
wct.gl_date tds_inv_gl_date,
wct.doc_sequence_value tds_voucher_no,
wct.invoice_amount tds_amount, wct.base_amt_tds, NULL tax_name,
jit.tax_rate, jath.pan_no, jit.section_code,
jath.tds_vendor_type_lookup_code, jit.section_type
FROM APPS.ap_invoices_all ap_inv_all,
APPS.ap_invoice_distributions_all ap_ida,
APPS.hz_parties hp,
APPS.fnd_user fu,
APPS.po_vendors pov,
-- PO_VENDOR_SITES_ALL POVS,
APPS.jai_cmn_taxes_all jit,
APPS.jai_ap_tds_vendor_hdrs jath,
(SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
jti.invoice_amount base_amt_tds
FROM APPS.jai_ap_tds_invoices jti,APPS.ap_invoices_all ap_inv_all
WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
AND ap_inv_all.attribute1 IS NOT NULL
AND vendor_id = '2'
) wct
WHERE 1 = 1
AND ap_inv_all.invoice_id = ap_ida.invoice_id
AND ap_ida.line_type_lookup_code != 'PREPAY'
AND ap_ida.global_attribute2 IS NOT NULL
AND ap_inv_all.org_id = ap_ida.org_id
AND wct.attribute1 = to_char(ap_inv_all.invoice_id)
AND NVL (TO_CHAR (wct.tds_tax_id), ap_ida.global_attribute1) =
ap_ida.global_attribute1
AND pov.vendor_id = ap_inv_all.vendor_id
AND fu.user_id = ap_inv_all.created_by
AND pov.party_id = hp.party_id
AND ap_inv_all.org_id = ap_ida.org_id
AND ap_inv_all.cancelled_date IS NULL
AND jath.vendor_site_id = ap_inv_all.vendor_site_id
AND jit.tax_id = ap_ida.global_attribute2
AND ap_ida.match_status_flag = 'A'
AND UPPER (wct.invoice_num) NOT LIKE '%RTN%'
GROUP BY ap_inv_all.invoice_id,
ap_inv_all.invoice_num,
ap_inv_all.invoice_date,
ap_inv_all.invoice_amount,
ap_ida.global_attribute1,
wct.invoice_num,
wct.gl_date,
wct.doc_sequence_value,
wct.invoice_amount,
wct.base_amt_tds,
pov.vendor_name,
hp.address1,
hp.address2,
hp.address3,
hp.address4,
hp.city,
hp.postal_code,
hp.state,
pov.segment1,
jit.tax_name,
jit.tax_rate,
jath.pan_no,
jit.section_code,
jath.tds_vendor_type_lookup_code,
jit.section_type,
ap_inv_all.org_id
1 comments:
Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
¬Oracle Fusion HCM Online Training
Post a Comment