Introduction
This Post illustrate steps required to India AR GST tax report in Oracle EBS R12.
Query to India AR GST tax report .
WITH Parameter AS
(SELECT :Transaction_Start_Date AS BV_Transaction_Start_Date ,
:Transaction_End_Date AS BV_Transaction_End_Date,
:GL_Start_Date AS BV_GL_Start_Date ,
:GL_End_Date AS BV_GL_End_Date,
:Trx_Number_from AS BV_Trx_Number_From,
:Trx_Number_To AS BV_Trx_Number_To FROM DUAL)
SELECT
b.name Transaction_Source,
rt.name Transaction_Type,
trx.trx_date Transaction_Date,
gd.gl_date GL_Date,
j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) IRM_GST_Number,
j.location_name Customer_State_Ship_to,
party.party_name Party_Name_Customer,
cust.account_number Customer_Account_Number,
bill.location Party_Site_Name_Bill_to,
(SELECT DISTINCT jtl.third_party_primary_reg_num
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Customer_GST_Number,
trx.trx_number Invoice_Number,
trx.invoice_currency_code Invoice_Currency,
(select max(app_trx2.trx_number)
from
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id) Related_Invoice_Number,
cl.line_number Invoice_Line_Number,
cl.UOM_CODE UOM,
nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
cl.unit_selling_price Unit_Price,
cl.extended_amount Transaction_Line_Amount,
cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,’CGST’,jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,’SGST’,jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Line_Amount_2,
j.tax_category_name Tax_Category,
‘CGST’ Tax_Type_1,
‘SGCT’ Tax_Type_2,
‘TBC’ Line_Total_Gross,
j.hsn_code HSN_Code,
j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,’CGST’,jtl.tax_rate_percentage,null) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,’SGST’,jtl.tax_rate_percentage,null) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Rate_2,
(select max( cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9 ) Revenue_Account
from apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where gld.customer_trx_id = trx.customer_trx_id
and gld.customer_trx_line_id = cl.customer_trx_line_id
and cc.code_combination_id = Gld.Code_Combination_Id
and gld.account_class = ‘REV’ ) Revenue_Account,
(SELECT max( decode (jrav.reporting_code, ‘CGST’, cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9, null) ) Expense_Account
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav,
apps.jai_tax_accounts ta,
apps.gl_code_combinations cc
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
AND ta.tax_account_entity_id = jtl.tax_type_id
AND ta.organization_id = jtl.organization_id
AND ta.location_id = Jtl.Location_Id
AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT max( decode (jrav.reporting_code, ‘SGST’, cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9, null) ) Expense_Account
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav,
apps.jai_tax_accounts ta,
apps.gl_code_combinations cc
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
AND ta.tax_account_entity_id = jtl.tax_type_id
AND ta.organization_id = jtl.organization_id
AND ta.location_id = Jtl.Location_Id
AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
apps.hz_cust_accounts cust,
apps.hz_cust_acct_sites_all acct,
apps.hz_cust_site_uses_all bill,
apps.hz_party_sites party_site,
apps.hz_locations loc,
apps.hz_parties party,
apps.ar_payment_schedules_all aps,
apps.ra_customer_trx_all trx,
apps.ra_cust_trx_types_all rt,
apps.ra_batch_sources_all b,
apps.ra_cust_trx_line_gl_dist_all gd,
apps.ra_customer_trx_lines_all cl,
apps.JAI_TRX_LINES_V J,
Parameter
WHERE
cust.cust_account_id = acct.cust_account_id
AND
acct.cust_acct_site_id = bill.cust_acct_site_id
AND
acct.org_id = bill.org_id
AND
bill.site_use_code = ‘BILL_TO’
AND
loc.location_id = party_site.location_id
AND
acct.party_site_id = party_site.party_site_id
AND
cust.party_id = party.party_id
AND
aps.customer_id (+) = cust.cust_account_id
AND
aps.customer_site_use_id (+) = bill.site_use_id
AND
trx.customer_trx_id = aps.customer_trx_id
AND
rt.cust_trx_type_id = trx.cust_trx_type_id
AND
b.batch_source_id = trx.batch_source_id
AND
trx.customer_trx_id = gd.customer_trx_id
AND
‘REC’ = gd.account_class
AND
‘Y’ = gd.latest_rec_flag
AND
cl.customer_trx_id = trx.customer_trx_id
AND
( cl.quantity_invoiced is not null or cl.quantity_credited is not null or cl.extended_amount is not null)
AND
j.trx_id = trx.customer_trx_id
AND
j.trx_line_id = cl.customer_trx_line_id
AND
j.tax_category_name like ‘Intrastate %’
AND
bill.org_id = :org_id — India
AND
trx.org_id = :org_id– India
AND
trx.complete_flag = ‘Y’
AND
trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
AND
trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
AND
gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)
UNION ALL
SELECT
b.name Transaction_Source,
rt.name Transaction_Type,
trx.trx_date Transaction_Date,
gd.gl_date GL_Date,
j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) IRM_GST_Number,
j.location_name Customer_State_Ship_to,
party.party_name Party_Name_Customer,
cust.account_number Customer_Account_Number,
bill.location Party_Site_Name_Bill_to,
(SELECT DISTINCT jtl.third_party_primary_reg_num
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Customer_GST_Number,
trx.trx_number Invoice_Number,
trx.invoice_currency_code Invoice_Currency,
(select max(app_trx2.trx_number)
from
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id) Related_Invoice_Number,
cl.line_number Invoice_Line_Number,
cl.UOM_CODE UOM,
nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
cl.unit_selling_price Unit_Price,
cl.extended_amount Transaction_Line_Amount,
cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,’IGST’,jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,’XXXX’,jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Line_Amount_2,
j.tax_category_name Tax_Category,
‘IGST’ Tax_Type_1,
” Tax_Type_2,
‘TBC’ Line_Total_Gross,
j.hsn_code HSN_Code,
j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,’CGST’,jtl.tax_rate_percentage,null) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,’SGST’,jtl.tax_rate_percentage,null) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Rate_2,
(select max( cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9 ) Revenue_Account
from apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where gld.customer_trx_id = trx.customer_trx_id
and gld.customer_trx_line_id = cl.customer_trx_line_id
and cc.code_combination_id = Gld.Code_Combination_Id
and gld.account_class = ‘REV’ ) Revenue_Account,
(SELECT max( decode (jrav.reporting_code, ‘IGST’, cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9, null) ) Expense_Account
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav,
apps.jai_tax_accounts ta,
apps.gl_code_combinations cc
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
AND ta.tax_account_entity_id = jtl.tax_type_id
AND ta.organization_id = jtl.organization_id
AND ta.location_id = Jtl.Location_Id
AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT max( decode (jrav.reporting_code, ‘XXXX’, cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9, null) ) Expense_Account
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav,
apps.jai_tax_accounts ta,
apps.gl_code_combinations cc
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
AND ta.tax_account_entity_id = jtl.tax_type_id
AND ta.organization_id = jtl.organization_id
AND ta.location_id = Jtl.Location_Id
AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
apps.hz_cust_accounts cust,
apps.hz_cust_acct_sites_all acct,
apps.hz_cust_site_uses_all bill,
apps.hz_party_sites party_site,
apps.hz_locations loc,
apps.hz_parties party,
apps.ar_payment_schedules_all aps,
apps.ra_customer_trx_all trx,
apps.ra_cust_trx_types_all rt,
apps.ra_batch_sources_all b,
apps.ra_cust_trx_line_gl_dist_all gd,
apps.ra_customer_trx_lines_all cl,
apps.JAI_TRX_LINES_V J,
Parameter
WHERE
cust.cust_account_id = acct.cust_account_id
AND
acct.cust_acct_site_id = bill.cust_acct_site_id
AND
acct.org_id = bill.org_id
AND
bill.site_use_code = ‘BILL_TO’
AND
loc.location_id = party_site.location_id
AND
acct.party_site_id = party_site.party_site_id
AND
cust.party_id = party.party_id
AND
aps.customer_id (+) = cust.cust_account_id
AND
aps.customer_site_use_id (+) = bill.site_use_id
AND
trx.customer_trx_id = aps.customer_trx_id
AND
rt.cust_trx_type_id = trx.cust_trx_type_id
AND
b.batch_source_id = trx.batch_source_id
AND
trx.customer_trx_id = gd.customer_trx_id
AND
‘REC’ = gd.account_class
AND
‘Y’ = gd.latest_rec_flag
AND
cl.customer_trx_id = trx.customer_trx_id
AND
( cl.quantity_invoiced is not null or cl.quantity_credited is not null or cl.extended_amount is not null)
AND
j.trx_id = trx.customer_trx_id
AND
j.trx_line_id = cl.customer_trx_line_id
AND
j.tax_category_name like ‘Interstate %’
AND
bill.org_id = :org_id — India
AND
trx.org_id = :org_id — India
AND
trx.complete_flag = ‘Y’
AND
trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
AND
trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
AND
gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)
UNION ALL
SELECT
b.name Transaction_Source,
rt.name Transaction_Type,
trx.trx_date Transaction_Date,
gd.gl_date GL_Date,
j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) IRM_GST_Number,
j.location_name Customer_State_Ship_to,
party.party_name Party_Name_Customer,
cust.account_number Customer_Account_Number,
bill.location Party_Site_Name_Bill_to,
(SELECT DISTINCT jtl.third_party_primary_reg_num
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Customer_GST_Number,
trx.trx_number Invoice_Number,
trx.invoice_currency_code Invoice_Currency,
(select max(app_trx2.trx_number)
from
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id) Related_Invoice_Number,
cl.line_number Invoice_Line_Number,
cl.UOM_CODE UOM,
nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
cl.unit_selling_price Unit_Price,
cl.extended_amount Transaction_Line_Amount,
cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,’CGST’,jtl.ROUNDED_TAX_AMT_FUN_CURR,
‘IGST’,jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,’SGST’,jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Line_Amount_2,
j.tax_category_name Tax_Category,
(SELECT MIN(jrav.reporting_code)
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Type_1,
(SELECT MAX(jrav.reporting_code)
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
AND jrav.reporting_code NOT IN (‘CGST’,’IGST’)
group by jrav.reporting_code) Tax_Type_2,
‘TBC’ Line_Total_Gross,
j.hsn_code HSN_Code,
j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,’CGST’,jtl.tax_rate_percentage,
‘IGST’,jtl.tax_rate_percentage,null) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,’SGST’,jtl.tax_rate_percentage,null) )
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’) Tax_Rate_2,
(select max( cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9 ) Revenue_Account
from apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where gld.customer_trx_id = trx.customer_trx_id
and gld.customer_trx_line_id = cl.customer_trx_line_id
and cc.code_combination_id = Gld.Code_Combination_Id
and gld.account_class = ‘REV’ ) Revenue_Account,
(SELECT max( decode (jrav.reporting_code, ‘CGST’, cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9,
‘IGST’, cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9, null) ) Expense_Account
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav,
apps.jai_tax_accounts ta,
apps.gl_code_combinations cc
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
AND ta.tax_account_entity_id = jtl.tax_type_id
AND ta.organization_id = jtl.organization_id
AND ta.location_id = Jtl.Location_Id
AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT max( decode (jrav.reporting_code, ‘SGST’, cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9, null) ) Expense_Account
FROM apps.jai_tax_lines jtl,
apps.jai_reporting_associations_v jrav,
apps.jai_tax_accounts ta,
apps.gl_code_combinations cc
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jrav.ENTITY_ID
AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
AND ta.tax_account_entity_id = jtl.tax_type_id
AND ta.organization_id = jtl.organization_id
AND ta.location_id = Jtl.Location_Id
AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
apps.hz_cust_accounts cust,
apps.hz_cust_acct_sites_all acct,
apps.hz_cust_site_uses_all bill,
apps.hz_party_sites party_site,
apps.hz_locations loc,
apps.hz_parties party,
apps.ar_payment_schedules_all aps,
apps.ra_customer_trx_all trx,
apps.ra_cust_trx_types_all rt,
apps.ra_batch_sources_all b,
apps.ra_cust_trx_line_gl_dist_all gd,
apps.ra_customer_trx_lines_all cl,
apps.JAI_TRX_LINES_V J,
Parameter
WHERE
cust.cust_account_id = acct.cust_account_id
AND
acct.cust_acct_site_id = bill.cust_acct_site_id
AND
acct.org_id = bill.org_id
AND
bill.site_use_code = ‘BILL_TO’
AND
loc.location_id = party_site.location_id
AND
acct.party_site_id = party_site.party_site_id
AND
cust.party_id = party.party_id
AND
aps.customer_id (+) = cust.cust_account_id
AND
aps.customer_site_use_id (+) = bill.site_use_id
AND
trx.customer_trx_id = aps.customer_trx_id
AND
rt.cust_trx_type_id = trx.cust_trx_type_id
AND
b.batch_source_id = trx.batch_source_id
AND
trx.customer_trx_id = gd.customer_trx_id
AND
‘REC’ = gd.account_class
AND
‘Y’ = gd.latest_rec_flag
AND
cl.customer_trx_id = trx.customer_trx_id
AND
( (cl.quantity_invoiced is not null or cl.quantity_credited is not null) or
( cl.extended_amount is not null and rt.name = :Trx_source –‘IN CM TDS’)
)
AND
j.trx_id(+) = trx.customer_trx_id
AND
j.trx_line_id(+) = cl.customer_trx_line_id
AND
( j.tax_category_name is null or (
j.tax_category_name not like ‘Interstate %’
AND j.tax_category_name not like ‘Intrastate %’ )
)
AND
bill.org_id = :org_id–379 — India
AND
trx.org_id = :org_id–379 — India
AND
trx.complete_flag = ‘Y’
AND
trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
AND
trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
AND
gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)
Summary
This Post described the script India AR GST tax report in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
