Below is the query to get the GST applied for Invoices, Credit memos and Debit memos
WITH Parameter AS
(SELECT to_date(:P_Transaction_Start_Date,’YYYY/MM/DD HH24:MI:SS’) AS BV_Transaction_Start_Date ,
to_date(:P_Transaction_End_Date,’YYYY/MM/DD HH24:MI:SS’) AS BV_Transaction_End_Date,
to_date(:P_GL_Start_Date,’YYYY/MM/DD HH24:MI:SS’) AS BV_GL_Start_Date ,
to_date(:P_GL_End_Date,’YYYY/MM/DD HH24:MI:SS’) AS BV_GL_End_Date,
:P_Trx_Number_from AS BV_Trx_Number_From,
:P_Trx_Number_To AS BV_Trx_Number_To FROM DUAL)
SELECT ceil(rownum/65000) SHEET, T.*
FROM (SELECT
b.name Transaction_Source,
rt.name Transaction_Type,
trx.trx_date Transaction_Date,
gd.gl_date GL_Date,
j.ship_from_state CUS_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num — Fixed in V3.3 for defect 2189
FROM apps.jai_tax_lines jtl
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number) CUS_GST_Number,
loc.state Customer_State_Ship_to, — Defect 2189
party.party_name Party_Name_Customer,
cust.account_number Customer_Account_Number,
bill.location Party_Site_Name_Bill_to,
(SELECT DISTINCT REGEXP_REPLACE (jtl.third_party_primary_reg_num,'[^’ || CHR (32) || ‘-‘ || CHR (127) || ‘]’, ‘ ‘) — Fixed in V3.3 for defect 2189
FROM apps.jai_tax_lines jtl
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
) Customer_GST_Number,
trx.trx_number Invoice_Number,
trx.invoice_currency_code Invoice_Currency,
RIN.trx_number RELATED_INVOICE_NUMBER,
to_char(RIN.trx_date,’DD-Mon-YYYY’) RELATED_INVOICE_DATE,
RIN.amount_applied RELATED_INVOICE_TOTAL_AMT,
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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,
‘SGST’ Tax_Type_2, — Defect 2189
‘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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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,
(select app_trx2.trx_number trx_number,ps2.customer_trx_id,app_trx2.trx_date trx_date,app_trx2.org_id,app2.amount_applied
from
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where 1=1
and app2.customer_trx_id = ps2.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id
) RIN,
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
b.org_id = trx.org_id — Fixed in V3.3 for defect 2189
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 = FND_PROFILE.VALUE(‘ORG_ID’) — Defect 2144
AND
trx.org_id = FND_PROFILE.VALUE(‘ORG_ID’) — Defect 2144
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)
AND RIN.customer_trx_id(+) = trx.customer_trx_id
AND RIN.org_id(+)=trx.org_id
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 CUS_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num — Fixed in V3.3 for defect 2189
FROM apps.jai_tax_lines jtl
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number) CUS_GST_Number,
loc.state Customer_State_Ship_to, — Defect 2189
party.party_name Party_Name_Customer,
cust.account_number Customer_Account_Number,
bill.location Party_Site_Name_Bill_to,
(SELECT DISTINCT REGEXP_REPLACE (jtl.third_party_primary_reg_num,'[^’ || CHR (32) || ‘-‘ || CHR (127) || ‘]’, ‘ ‘) — Fixed in V3.3 for defect 2189
FROM apps.jai_tax_lines jtl
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
) Customer_GST_Number,
trx.trx_number Invoice_Number,
trx.invoice_currency_code Invoice_Currency,
RIN.trx_number RELATED_INVOICE_NUMBER,
to_char(RIN.trx_date,’DD-Mon-YYYY’) RELATED_INVOICE_DATE,
RIN.amount_applied RELATED_INVOICE_TOTAL_AMT,– Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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) )
(SELECT max( decode(jrav.reporting_code,’IGST’,jtl.tax_rate_percentage,null) ) — Fix V3.3 Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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,
(select app_trx2.trx_number trx_number,ps2.customer_trx_id,app_trx2.trx_date trx_date,app_trx2.org_id,app2.amount_applied
from
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where 1=1
and app2.customer_trx_id = ps2.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id
) RIN,
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
b.org_id = trx.org_id — Fixed in V3.3 for defect 2189
AND
trx.customer_trx_id = gd.customer_trx_id
AND
‘REC’ = gd.account_class
AND
‘Y’ = gd.latest_rec_flag
AND
trx.complete_flag = ‘Y’
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 = FND_PROFILE.VALUE(‘ORG_ID’) — Defect 2144
AND
trx.org_id = FND_PROFILE.VALUE(‘ORG_ID’) — Defect 2144
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)
AND RIN.customer_trx_id(+) = trx.customer_trx_id
AND RIN.org_id(+)=trx.org_id
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 CUS_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num — Fixed in V3.3 for defect 2189
FROM apps.jai_tax_lines jtl
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number ) CUS_GST_Number,
loc.state Customer_State_Ship_to, — Defect 2189
party.party_name Party_Name_Customer,
cust.account_number Customer_Account_Number,
bill.location Party_Site_Name_Bill_to,
(SELECT DISTINCT REGEXP_REPLACE (jtl.third_party_primary_reg_num,'[^’ || CHR (32) || ‘-‘ || CHR (127) || ‘]’, ‘ ‘) — Fixed in V3.3 for defect 2189
FROM apps.jai_tax_lines jtl
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
) Customer_GST_Number,
trx.trx_number Invoice_Number,
trx.invoice_currency_code Invoice_Currency,
RIN.trx_number RELATED_INVOICE_NUMBER,
to_char(RIN.trx_date,’DD-Mon-YYYY’) RELATED_INVOICE_DATE,
RIN.amount_applied RELATED_INVOICE_TOTAL_AMT,– Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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(jtt.tax_type_code) — Fixed in V3.3 for defect 2189
FROM apps.jai_tax_lines jtl,
apps.jai_tax_types jtt
WHERE jtl.trx_id = trx.customer_trx_id
AND jtl.trx_line_number = cl.line_number
AND jtl.TAX_TYPE_ID = jtt.TAX_TYPE_ID) 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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
AND jrav.ENTITY_CODE = ‘TAX_TYPE’
AND jrav.REPORTING_TYPE_CODE = ‘TAX_TYPES_CLASSIFICATION’
AND jrav.reporting_code NOT IN (‘CGST’,’IGST’) — Fixed in V3.3 for defect 2189
AND jrav.reporting_code (SELECT MIN(jtt2.tax_type_code)
FROM apps.jai_tax_lines jtl2,
apps.jai_tax_types jtt2
WHERE jtl2.trx_id = trx.customer_trx_id
AND jtl2.trx_line_number = cl.line_number
AND jtl2.TAX_TYPE_ID = jtt2.TAX_TYPE_ID)
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,’SGST’,0, — Fixed in V3.3 for defect 2189
jtl.tax_rate_percentage) )
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, ‘SGST’, NULL, — Fixed in V3.3 for defect 2189
cc.segment1||’.’||
cc.segment2||’.’||
cc.segment3||’.’||
cc.segment4||’.’||
cc.segment5||’.’||
cc.segment6||’.’||
cc.segment7||’.’||
cc.segment8||’.’||
cc.segment9 )) 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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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, to_date(’31-DEC-4017′,’DD-MON-YYYY’)) — Defect 2189
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,
(select app_trx2.trx_number trx_number,ps2.customer_trx_id,app_trx2.trx_date trx_date,app_trx2.org_id,app2.amount_applied
from
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where 1=1
and app2.customer_trx_id = ps2.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id
) RIN,
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
b.org_id = trx.org_id — Fixed in V3.3 for defect 2189
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 in (‘IN CM TDS’,’IN OEC CM TDS’,’IN REC 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 = FND_PROFILE.VALUE(‘ORG_ID’) — Defect 2144
AND
trx.org_id = FND_PROFILE.VALUE(‘ORG_ID’) — Defect 2144
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)
AND RIN.customer_trx_id(+) = trx.customer_trx_id
AND RIN.org_id(+)=trx.org_id ) T
For any queries, you can message us at contact@staging.doyensys.com
