<ROOT>
 <APPS_INITIALIZE_DATA>
  <USER_NAME>ENGINATICS</USER_NAME>
  <RESPONSIBILITY_KEY>SYSTEM_ADMINISTRATOR</RESPONSIBILITY_KEY>
  <APPLICATION_SHORT_NAME>SYSADMIN</APPLICATION_SHORT_NAME>
 </APPS_INITIALIZE_DATA>
<REPORTS>
<!-- loader xml for Enginatics Blitz Report: JA India GSTR-3B Return -->
 <REPORTS_ROW>
  <GUID>31BB3FD7FDE50ADBE06362FB0905F2D4</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>with jai_party_reg as (
select
jprlv.registration_number,
jprv.party_name,
jprv.operating_unit
from
jai_party_reg_lines_v jprlv,
jai_party_regs_v jprv
where
1=1 and
jprlv.registration_number=:p_first_pty_reg_num and
jprlv.party_reg_id=jprv.party_reg_id and
jprv.party_type_code in (&apos;IO&apos;,&apos;OU&apos;) and
rownum=1)
-- 31a
select
&apos;31a&apos; section_code,
tax_dtls.registration_number gstin,
tax_dtls.party_name registered_person,
tax_dtls.operating_unit,
null pos,
null type,
tax_dtls.taxable_basis taxable_value,
tax_dtls.cgst,
tax_dtls.sgst,
tax_dtls.igst,
tax_dtls.cess_amount
from
(select
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.det_factor_id,
jtlv.organization_name,
jtlv.location_code,
jtlv.application_name,
jtlv.entity_code,
jtlv.event_class_code,
jtlv.event_type_code,
jtlv.tax_event_class_code,
jtlv.tax_event_type_code,
jtlv.trx_type,
jtlv.trx_number,
jtlv.trx_date,
jtlv.trx_line_number,
jtlv.item_id,
jtlv.frozen_flag,
jtlv.party_type,
jtlv.party_number,
jtlv.party_site_name,
nvl(jtlv.line_amt,0) line_amt,
nvl(jtlv.taxable_basis,0) taxable_basis,
nvl(sum(decode(jrav.reporting_code,&apos;IGST&apos;,jtlv.rounded_tax_amt_fun_curr)),0) igst,
nvl(sum(decode(jrav.reporting_code,&apos;CGST&apos;,jtlv.rounded_tax_amt_fun_curr)),0) cgst,
nvl(sum(decode(jrav.reporting_code,&apos;SGST&apos;,jtlv.rounded_tax_amt_fun_curr)),0) sgst,
nvl(sum(decode(jrav.reporting_code,&apos;CESS&apos;,jtlv.rounded_tax_amt_fun_curr)),0) cess_amount
from
jai_tax_det_factors jtdf,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav,
jai_rgm_recovery_lines jrrl,
jai_party_reg jpr
where
1=1 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jtdf.det_factor_id=jtlv.det_factor_id and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jrrl.tax_line_id=jtlv.tax_line_id and
jtlv.tax_type_id=jrav.entity_id and
jtlv.actual_tax_rate&lt;&gt;0 and
jtlv.actual_tax_rate is not null and
jtlv.exemption_type is null and
jtdf.bill_to_country=&apos;IN&apos; and
jrrl.liability_amount&lt;&gt;0 and
jrrl.status=&apos;CONFIRMED&apos; and
jtlv.self_assessed_flag&lt;&gt;&apos;Y&apos; and
not exists (select null
from
jai_party_regs jpr2,
jai_reporting_associations_v jrav2
where
upper(jrav2.reporting_code) in (&apos;DEEMED EXPORTS EOU&apos;,&apos;SEZ&apos;) and
jrav2.entity_code=&apos;THIRD_PARTY&apos; and
jrav2.reporting_type_code=&apos;THIRD_PARTY_CLASSIFICATION&apos; and
jpr2.party_reg_id=jrav2.entity_id and
jpr2.party_id=jtdf.party_id)
group by
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.det_factor_id,
jtlv.organization_name,
jtlv.location_code,
jtlv.application_name,
jtlv.entity_code,
jtlv.event_class_code,
jtlv.event_type_code,
jtlv.tax_event_class_code,
jtlv.tax_event_type_code,
jtlv.trx_type,
jtlv.trx_number,
jtlv.trx_date,
jtlv.trx_line_number,
jtlv.item_id,
jtlv.frozen_flag,
jtlv.party_type,
jtlv.party_number,
jtlv.party_site_name,
nvl(jtlv.line_amt,0),
nvl(jtlv.taxable_basis,0)) tax_dtls
union all
-- 31b
select
&apos;31b&apos; section_code,
tax_dtls.registration_number gstin,
tax_dtls.party_name registered_person,
tax_dtls.operating_unit,
null pos,
null type,
tax_dtls.line_amt taxable_value,
null cgst,
null sgst,
tax_dtls.igst,
tax_dtls.cess_amount
from
(select
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.det_factor_id,
jtdf.line_amt,
sum(decode(jrav.reporting_code,&apos;IGST&apos;,jtlv.rounded_tax_amt_fun_curr)) igst,
sum(decode(jrav.reporting_code,&apos;CESS&apos;,jtlv.rounded_tax_amt_fun_curr)) cess_amount
from
jai_party_reg jpr,
jai_tax_det_factors jtdf,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jtdf.det_factor_id=jtlv.det_factor_id and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jrrl.tax_line_id=jtlv.tax_line_id and
jtlv.tax_type_id=jrav.entity_id and
jrrl.liability_amount&lt;&gt;0 and
jrrl.status=&apos;CONFIRMED&apos; and
(jtdf.ship_to_country&lt;&gt;&apos;IN&apos; or
exists (select null
from
jai_party_regs jpr2,
jai_reporting_associations_v jrav2
where
upper(jrav2.reporting_code) in (&apos;DEEMED EXPORTS EOU&apos;,&apos;SEZ&apos;) and
jrav2.entity_code=&apos;THIRD_PARTY&apos; and
jrav2.reporting_type_code=&apos;THIRD_PARTY_CLASSIFICATION&apos; and
jpr2.party_reg_id=jrav2.entity_id and
jpr2.party_id=jtdf.party_id))
group by
jtdf.det_factor_id,
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.line_amt) tax_dtls
union all
-- 31c
select
&apos;31c&apos; section_code,
tax_dtls.registration_number gstin,
tax_dtls.party_name registered_person,
tax_dtls.operating_unit,
null pos,
null type,
tax_dtls.line_amt taxable_value,
null cgst,
null sgst,
null igst,
null cess_amount
from
(select distinct
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.det_factor_id,
jtdf.line_amt
from
jai_party_reg jpr,
jai_tax_det_factors jtdf,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jtdf.det_factor_id=jtlv.det_factor_id and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jrrl.tax_line_id=jtlv.tax_line_id and
jtlv.tax_type_id=jrav.entity_id and
jrrl.status=&apos;CONFIRMED&apos; and
((jtlv.tax_rate_percentage is null or jtlv.tax_rate_percentage=0) or
(jtlv.tax_amt_before_exemption&gt;0 and nvl(jtlv.rounded_tax_amt_fun_curr,0)=0))) tax_dtls
union all
-- 31d
select
&apos;31d&apos; section_code,
tax_dtls.registration_number gstin,
tax_dtls.party_name registered_person,
tax_dtls.operating_unit,
null pos,
null type,
tax_dtls.line_amt taxable_value,
tax_dtls.cgst,
tax_dtls.sgst,
tax_dtls.igst,
tax_dtls.cess_amount
from
(select
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.det_factor_id,
jtdf.line_amt,
sum(decode(jrav.reporting_code,&apos;IGST&apos;,jtlv.rounded_tax_amt_fun_curr)) igst,
sum(decode(jrav.reporting_code,&apos;CGST&apos;,jtlv.rounded_tax_amt_fun_curr)) cgst,
sum(decode(jrav.reporting_code,&apos;SGST&apos;,jtlv.rounded_tax_amt_fun_curr)) sgst,
sum(decode(jrav.reporting_code,&apos;CESS&apos;,jtlv.rounded_tax_amt_fun_curr)) cess_amount
from
jai_party_reg jpr,
jai_tax_det_factors jtdf,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jtdf.det_factor_id=jtlv.det_factor_id and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jrrl.tax_line_id=jtlv.tax_line_id and
jtlv.tax_type_id=jrav.entity_id and
jrrl.liability_amount&lt;&gt;0 and
jrrl.status=&apos;CONFIRMED&apos; and
jtlv.self_assessed_flag=&apos;Y&apos;
group by
jtdf.det_factor_id,
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.line_amt) tax_dtls
union all
-- 31e
select
&apos;31e&apos; section_code,
tax_dtls.registration_number gstin,
tax_dtls.party_name registered_person,
tax_dtls.operating_unit,
null pos,
null type,
tax_dtls.line_amt taxable_value,
null cgst,
null sgst,
null igst,
null cess_amount
from
(select distinct
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.det_factor_id,
jtdf.line_amt
from
jai_party_reg jpr,
jai_tax_det_factors jtdf,
jai_tax_lines_v jtlv,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jtdf.det_factor_id=jtlv.det_factor_id and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jrrl.tax_line_id=jtlv.tax_line_id and
jrrl.liability_amount&lt;&gt;0 and
jrrl.status=&apos;CONFIRMED&apos; and
not exists (select null
from
jai_tax_types_v jttv,
jai_reporting_associations_v jrav
where
jttv.tax_type_id=jtlv.tax_type_id and
jttv.tax_type_id=jrav.entity_id and
jrav.reporting_code in (&apos;IGST&apos;,&apos;SGST&apos;,&apos;CGST&apos;,&apos;CESS&apos;))) tax_dtls
union all
-- 32a
select
&apos;32a&apos; section_code,
y.registration_number gstin,
y.party_name registered_person,
y.operating_unit,
y.pos,
null type,
y.taxable_value,
null cgst,
null sgst,
y.igst,
null cess_amount
from
(select
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.bill_to_state pos,
sum(jtdf.line_amt) taxable_value,
sum(jtlv.rounded_tax_amt_fun_curr) igst
from
jai_party_reg jpr,
jai_tax_det_factors jtdf,
jai_tax_lines_v jtlv,
jai_reporting_associations_v jrav,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
jrav.reporting_code=&apos;IGST&apos; and
jrav.entity_id=jtlv.tax_type_id and
jtdf.det_factor_id=jtlv.det_factor_id and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jtlv.third_party_primary_reg_num is null and
jrrl.tax_line_id=jtlv.tax_line_id and
jrrl.liability_amount&lt;&gt;0 and
jrrl.status=&apos;CONFIRMED&apos; and
jtdf.ship_from_state&lt;&gt;jtdf.bill_to_state
group by
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
jtdf.bill_to_state) y
union all
-- 4a1
select
&apos;4a1&apos; section_code,
y.registration_number gstin,
y.party_name registered_person,
y.operating_unit,
null pos,
y.type,
null taxable_value,
y.cgst,
y.sgst,
y.igst,
y.cess_amount
from
(select
&apos;IMPG&apos; type,
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
sum(decode(jrav.reporting_code,&apos;IGST&apos;,jtlv.rounded_tax_amt_fun_curr)) igst,
sum(decode(jrav.reporting_code,&apos;CGST&apos;,jtlv.rounded_tax_amt_fun_curr)) cgst,
sum(decode(jrav.reporting_code,&apos;SGST&apos;,jtlv.rounded_tax_amt_fun_curr)) sgst,
sum(decode(jrav.reporting_code,&apos;CESS&apos;,jtlv.rounded_tax_amt_fun_curr)) cess_amount
from
jai_party_reg jpr,
jai_reporting_associations_v jrav,
jai_tax_lines_v jtlv,
jai_tax_det_factors jtdf,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
sysdate between nvl(jrav.effective_from,sysdate) and nvl(jrav.effective_to,&apos;31-DEC-4017&apos;) and
jtlv.tax_type_id=jrav.entity_id and
jtlv.first_party_primary_reg_num=jpr.registration_number and
exists (select null from jai_tax_lines jtl where jtdf.trx_id=jtl.trx_id and jtdf.trx_line_id=jtl.trx_line_id and jtl.applied_to_entity_code=&apos;BILL_OF_ENTRY&apos;) and
jtdf.det_factor_id=jtlv.det_factor_id and
jrrl.tax_line_id=jtlv.tax_line_id and
jtdf.hsn_code_id is not null
group by
jpr.registration_number,
jpr.party_name,
jpr.operating_unit) y
union all
-- 4a2
select
&apos;4a2&apos; section_code,
y.registration_number gstin,
y.party_name registered_person,
y.operating_unit,
null pos,
y.type,
null taxable_value,
y.cgst,
y.sgst,
y.igst,
y.cess_amount
from
(select
&apos;IMPS&apos; type,
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
sum(decode(jrav.reporting_code,&apos;IGST&apos;,jtlv.rounded_tax_amt_fun_curr)) igst,
sum(decode(jrav.reporting_code,&apos;CGST&apos;,jtlv.rounded_tax_amt_fun_curr)) cgst,
sum(decode(jrav.reporting_code,&apos;SGST&apos;,jtlv.rounded_tax_amt_fun_curr)) sgst,
sum(decode(jrav.reporting_code,&apos;CESS&apos;,jtlv.rounded_tax_amt_fun_curr)) cess_amount
from
jai_party_reg jpr,
jai_reporting_associations_v jrav,
jai_tax_lines_v jtlv,
jai_tax_det_factors jtdf,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
sysdate between nvl(jrav.effective_from,sysdate) and nvl(jrav.effective_to,&apos;31-DEC-4017&apos;) and
jtlv.tax_type_id=jrav.entity_id and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jtdf.ship_from_country&lt;&gt;&apos;IN&apos; and
jtdf.det_factor_id=jtlv.det_factor_id and
jrrl.tax_line_id=jtlv.tax_line_id and
jtdf.sac_code_id is not null
group by
jpr.registration_number,
jpr.party_name,
jpr.operating_unit) y
union all
-- 4a3
select
&apos;4a3&apos; section_code,
y.registration_number gstin,
y.party_name registered_person,
y.operating_unit,
null pos,
y.type,
null taxable_value,
y.cgst,
y.sgst,
y.igst,
y.cess_amount
from
(select
&apos;ISRC&apos; type,
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
sum(decode(jrav.reporting_code,&apos;IGST&apos;,jtlv.rounded_tax_amt_fun_curr)) igst,
sum(decode(jrav.reporting_code,&apos;CGST&apos;,jtlv.rounded_tax_amt_fun_curr)) cgst,
sum(decode(jrav.reporting_code,&apos;SGST&apos;,jtlv.rounded_tax_amt_fun_curr)) sgst,
sum(decode(jrav.reporting_code,&apos;CESS&apos;,jtlv.rounded_tax_amt_fun_curr)) cess_amount
from
jai_party_reg jpr,
jai_reporting_associations_v jrav,
jai_tax_lines_v jtlv,
jai_tax_det_factors jtdf,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
sysdate between nvl(jrav.effective_from,sysdate) and nvl(jrav.effective_to,&apos;31-DEC-4017&apos;) and
jtlv.tax_type_id=jrav.entity_id and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jtdf.det_factor_id=jtlv.det_factor_id and
jrrl.tax_line_id=jtlv.tax_line_id and
jtlv.self_assessed_flag=&apos;Y&apos; and
jrrl.status=&apos;RECOVERED&apos; and
jrrl.recovered_amount&lt;&gt;0
group by
jpr.registration_number,
jpr.party_name,
jpr.operating_unit) y
union all
-- 4a5
select
&apos;4a5&apos; section_code,
y.registration_number gstin,
y.party_name registered_person,
y.operating_unit,
null pos,
y.type,
null taxable_value,
y.cgst,
y.sgst,
y.igst,
y.cess_amount
from
(select
&apos;OTH&apos; type,
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
sum(decode(jrav.reporting_code,&apos;IGST&apos;,jtlv.rounded_tax_amt_fun_curr)) igst,
sum(decode(jrav.reporting_code,&apos;CGST&apos;,jtlv.rounded_tax_amt_fun_curr)) cgst,
sum(decode(jrav.reporting_code,&apos;SGST&apos;,jtlv.rounded_tax_amt_fun_curr)) sgst,
sum(decode(jrav.reporting_code,&apos;CESS&apos;,jtlv.rounded_tax_amt_fun_curr)) cess_amount
from
jai_party_reg jpr,
jai_reporting_associations_v jrav,
jai_tax_lines_v jtlv,
jai_tax_det_factors jtdf,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
sysdate between nvl(jrav.effective_from,sysdate) and nvl(jrav.effective_to,&apos;31-DEC-4017&apos;) and
jtlv.tax_type_id=jrav.entity_id and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jtdf.det_factor_id=jtlv.det_factor_id and
jrrl.tax_line_id=jtlv.tax_line_id and
(nvl(jtlv.applied_to_entity_code,&apos;X&apos;)&lt;&gt;&apos;BILL_OF_ENTRY&apos; and (jtdf.hsn_code_id is null or jtdf.sac_code_id is null)) and
(jtlv.self_assessed_flag&lt;&gt;&apos;Y&apos; and jrrl.status=&apos;RECOVERED&apos; and jrrl.recovered_amount&lt;&gt;0)
group by
jpr.registration_number,
jpr.party_name,
jpr.operating_unit) y
union all
-- 4b1
select
&apos;4b1&apos; section_code,
y.registration_number gstin,
y.party_name registered_person,
y.operating_unit,
null pos,
y.type,
null taxable_value,
y.cgst,
y.sgst,
y.igst,
y.cess_amount
from
(select
&apos;RUL&apos; type,
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
sum(decode(jrav.reporting_code,&apos;IGST&apos;,jtlv.rounded_tax_amt_fun_curr)) igst,
sum(decode(jrav.reporting_code,&apos;CGST&apos;,jtlv.rounded_tax_amt_fun_curr)) cgst,
sum(decode(jrav.reporting_code,&apos;SGST&apos;,jtlv.rounded_tax_amt_fun_curr)) sgst,
sum(decode(jrav.reporting_code,&apos;CESS&apos;,jtlv.rounded_tax_amt_fun_curr)) cess_amount
from
jai_party_reg jpr,
jai_reporting_associations_v jrav,
jai_tax_lines_v jtlv,
jai_tax_det_factors jtdf,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
sysdate between nvl(jrav.effective_from,sysdate) and nvl(jrav.effective_to,&apos;31-DEC-4017&apos;) and
jtlv.tax_type_id=jrav.entity_id and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jtdf.det_factor_id=jtlv.det_factor_id and
jrrl.tax_line_id=jtlv.tax_line_id and
jrrl.status=&apos;REVERSED&apos;
group by
jpr.registration_number,
jpr.party_name,
jpr.operating_unit) y
union all
-- 4d1
select
&apos;4d1&apos; section_code,
y.registration_number gstin,
y.party_name registered_person,
y.operating_unit,
null pos,
y.type,
null taxable_value,
y.cgst,
y.sgst,
y.igst,
y.cess_amount
from
(select
&apos;RUL&apos; type,
jpr.registration_number,
jpr.party_name,
jpr.operating_unit,
sum((select jtlv2.rounded_tax_amt_fun_curr
from jai_tax_lines_v jtlv2, jai_reporting_associations_v jrav2
where
jtlv2.tax_line_id=jtlv.tax_line_id and jrav.entity_id=jrav2.entity_id and jrav2.reporting_code=&apos;IGST&apos;)) igst,
sum((select jtlv2.rounded_tax_amt_fun_curr
from jai_tax_lines_v jtlv2, jai_reporting_associations_v jrav2
where
jtlv2.tax_line_id=jtlv.tax_line_id and jrav.entity_id=jrav2.entity_id and jrav2.reporting_code=&apos;CGST&apos;)) cgst,
sum((select jtlv2.rounded_tax_amt_fun_curr
from jai_tax_lines_v jtlv2, jai_reporting_associations_v jrav2
where
jtlv2.tax_line_id=jtlv.tax_line_id and jrav.entity_id=jrav2.entity_id and jrav2.reporting_code=&apos;SGST&apos;)) sgst,
sum((select jtlv2.rounded_tax_amt_fun_curr
from jai_tax_lines_v jtlv2, jai_reporting_associations_v jrav2
where
jtlv2.tax_line_id=jtlv.tax_line_id and jrav.entity_id=jrav2.entity_id and jrav2.reporting_code=&apos;CESS&apos;)) cess_amount
from
jai_party_reg jpr,
jai_reporting_associations_v jrav,
jai_tax_lines_v jtlv,
jai_tax_det_factors jtdf,
jai_rgm_recovery_lines jrrl
where
2=2 and
to_char(trunc(jtdf.tax_invoice_date),&apos;MONYYYY&apos;)=:p_period and
jrav.entity_code=&apos;TAX_TYPE&apos; and
jrav.reporting_type_code=&apos;TAX_TYPES_CLASSIFICATION&apos; and
sysdate between nvl(jrav.effective_from,sysdate) and nvl(jrav.effective_to,&apos;31-DEC-4017&apos;) and
jtlv.tax_type_id=jrav.entity_id and
jtlv.first_party_primary_reg_num=jpr.registration_number and
jtdf.det_factor_id=jtlv.det_factor_id and
jrrl.tax_line_id=jtlv.tax_line_id and
jrrl.status=&apos;INELIGIBLE&apos;
group by
jpr.registration_number,
jpr.party_name,
jpr.operating_unit) y
union all
-- placeholder sections
select
y.section_code,
jpr.registration_number gstin,
jpr.party_name registered_person,
jpr.operating_unit,
null pos,
y.type,
null taxable_value,
null cgst,
null sgst,
null igst,
null cess_amount
from
jai_party_reg jpr,
(select &apos;32b&apos; section_code, null type from dual union all
select &apos;32c&apos;, null from dual union all
select &apos;4a4&apos;, &apos;ISD&apos; from dual union all
select &apos;4b2&apos;, &apos;OTH&apos; from dual union all
select &apos;4c&apos;, null from dual union all
select &apos;4d2&apos;, &apos;OTH&apos; from dual) y</SQL_TEXT>
  <XDO_APPLICATION_SHORT_NAME>JA</XDO_APPLICATION_SHORT_NAME>
  <XDO_DATA_SOURCE_CODE>JAIGSTR3B</XDO_DATA_SOURCE_CODE>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>JA</LANGUAGE>
    <REPORT_NAME>JA インドGSTR-3B申告レポート</REPORT_NAME>
    <DESCRIPTION>Imported from BI Publisher
Description: GSTR-3B申告レポート
Application: Asia/Pacific Localizations
Source: インドGSTR-3B申告レポート
Short Name: JAIGSTR3B
DB package: </DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>KO</LANGUAGE>
    <REPORT_NAME>JA 인도 GSTR-3B 환급 신고서</REPORT_NAME>
    <DESCRIPTION>Imported from BI Publisher
Description: GSTR-3B 환급 신고서
Application: Asia/Pacific Localizations
Source: 인도 GSTR-3B 환급 신고서
Short Name: JAIGSTR3B
DB package: </DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>JA India GSTR-3B Return</REPORT_NAME>
    <DESCRIPTION>Imported from BI Publisher
Description: GSTR-3B Return Report
Application: Asia/Pacific Localizations
Source: India GSTR-3B Return Report
Short Name: JAIGSTR3B
DB package:</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>ZHS</LANGUAGE>
    <REPORT_NAME>JA 印度 GSTR-3B 报税单报表 (1)</REPORT_NAME>
    <DESCRIPTION>Imported from BI Publisher
Description: GSTR-3B 报税单报表
Application: 亚太地区本地化
Source: 印度 GSTR-3B 报税单报表
Short Name: JAIGSTR3B
DB package: </DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
  </REPORT_TRANSLATIONS>
  <CATEGORY_ASSIGNMENTS>
   <CATEGORY_ASSIGNMENTS_ROW>
    <CATEGORY>Enginatics</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
   <CATEGORY_ASSIGNMENTS_ROW>
    <CATEGORY>R12 only</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
  </CATEGORY_ASSIGNMENTS>
  <ANCHORS>
   <ANCHORS_ROW>
    <ANCHOR>1=1</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>2=2</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_first_pty_reg_num</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_period</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>:p_tax_regime</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV custom</PARAMETER_TYPE_DSP>
    <VALIDATE_FROM_LIST_DSP>Y</VALIDATE_FROM_LIST_DSP>
    <LOV_QUERY_DSP>select
jr.regime_id id,
jr.regime_name value,
null description
from
jai_regimes jr
order by jr.regime_code</LOV_QUERY_DSP>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Tax Regime</PARAMETER_NAME>
      <DESCRIPTION>Tax Regime</DESCRIPTION>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
    <ANCHOR>:p_first_pty_reg_num</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV custom</PARAMETER_TYPE_DSP>
    <VALIDATE_FROM_LIST_DSP>Y</VALIDATE_FROM_LIST_DSP>
    <LOV_QUERY_DSP>select distinct
jprlv.registration_number id,
jprlv.registration_number value,
null description
from
jai_party_regs_v jprv,
jai_party_reg_lines_v jprlv
where
jprv.party_reg_id=jprlv.party_reg_id and
jprv.reg_class_code=&apos;FIRST_PARTY&apos; and
jprlv.regime_id=:$flex$.tax_regime
order by jprlv.registration_number</LOV_QUERY_DSP>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>GST Registration Number</PARAMETER_NAME>
      <DESCRIPTION>GST Registration Number</DESCRIPTION>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>3</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</DISPLAY_SEQUENCE>
    <ANCHOR>:p_period</ANCHOR>
    <PARAMETER_TYPE_DSP>Char</PARAMETER_TYPE_DSP>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Return Period (MONYYYY)</PARAMETER_NAME>
      <DESCRIPTION>Return Period</DESCRIPTION>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
  </PARAMETERS>
  <PARAMETER_DEPENDENCIES>
   <PARAMETER_DEPENDENCIES_ROW>
    <FLEX_BIND>:$flex$.tax_regime</FLEX_BIND>
    <PARAMETER_NAME>Tax Regime</PARAMETER_NAME>
    <DEPENDENT_PARAMETER_NAME>GST Registration Number</DEPENDENT_PARAMETER_NAME>
   </PARAMETER_DEPENDENCIES_ROW>
  </PARAMETER_DEPENDENCIES>
  <TEMPLATES>
   <TEMPLATES_ROW>
    <GUID>64F00495ED53A1814232710CD6444B27</GUID>
    <TEMPLATE_NAME>Default</TEMPLATE_NAME>
    <OWNER>BRYAN.BARBA</OWNER>
    <TEMPLATE_COLUMNS>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>11</DISPLAY_SEQUENCE>
      <COLUMN_NAME>CESS_AMOUNT</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>8</DISPLAY_SEQUENCE>
      <COLUMN_NAME>CGST</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
      <COLUMN_NAME>GSTIN</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
      <COLUMN_NAME>IGST</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>4</DISPLAY_SEQUENCE>
      <COLUMN_NAME>OPERATING_UNIT</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>5</DISPLAY_SEQUENCE>
      <COLUMN_NAME>POS</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
      <COLUMN_NAME>REGISTERED_PERSON</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
      <COLUMN_NAME>SECTION_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>9</DISPLAY_SEQUENCE>
      <COLUMN_NAME>SGST</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>7</DISPLAY_SEQUENCE>
      <COLUMN_NAME>TAXABLE_VALUE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>6</DISPLAY_SEQUENCE>
      <COLUMN_NAME>TYPE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
    </TEMPLATE_COLUMNS>
    <TEMPLATE_PIVOT>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>CESS_AMOUNT</COLUMN_NAME>
      <FIELD_TYPE>VALUE</FIELD_TYPE>
      <DISPLAY_SEQUENCE>5</DISPLAY_SEQUENCE>
      <AGGREGATION>SUM</AGGREGATION>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>CGST</COLUMN_NAME>
      <FIELD_TYPE>VALUE</FIELD_TYPE>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
      <AGGREGATION>SUM</AGGREGATION>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>GSTIN</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>IGST</COLUMN_NAME>
      <FIELD_TYPE>VALUE</FIELD_TYPE>
      <DISPLAY_SEQUENCE>4</DISPLAY_SEQUENCE>
      <AGGREGATION>SUM</AGGREGATION>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>OPERATING_UNIT</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>4</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>POS</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>5</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>REGISTERED_PERSON</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>SECTION_CODE</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>SGST</COLUMN_NAME>
      <FIELD_TYPE>VALUE</FIELD_TYPE>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
      <AGGREGATION>SUM</AGGREGATION>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>TAXABLE_VALUE</COLUMN_NAME>
      <FIELD_TYPE>VALUE</FIELD_TYPE>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
      <AGGREGATION>SUM</AGGREGATION>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>TYPE</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>6</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
    </TEMPLATE_PIVOT>
    <TEMPLATE_SHARED_STRINGS>
    </TEMPLATE_SHARED_STRINGS>
    <TEMPLATE_PARAMETER_DEFAULTS>
    </TEMPLATE_PARAMETER_DEFAULTS>
    <TEMPLATE_STYLES>
    </TEMPLATE_STYLES>
    <TEMPLATE_SHARING>
     <TEMPLATE_SHARING_ROW>
      <SHARING_LEVEL>S</SHARING_LEVEL>
      <LEVEL_VALUE>Site</LEVEL_VALUE>
     </TEMPLATE_SHARING_ROW>
    </TEMPLATE_SHARING>
    <PARAMETER_EXCLUSION>
    </PARAMETER_EXCLUSION>
   </TEMPLATES_ROW>
  </TEMPLATES>
  <DEFAULT_TEMPLATES>
   <DEFAULT_TEMPLATES_ROW>
    <USER_NAME>MRUGESH.POOJARY_OLD</USER_NAME>
    <TEMPLATE_GUID>64F00495ED53A1814232710CD6444B27</TEMPLATE_GUID>
   </DEFAULT_TEMPLATES_ROW>
   <DEFAULT_TEMPLATES_ROW>
    <USER_NAME>BRYAN.BARBA</USER_NAME>
    <TEMPLATE_GUID>64F00495ED53A1814232710CD6444B27</TEMPLATE_GUID>
   </DEFAULT_TEMPLATES_ROW>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
  <UPLOAD_DEPENDENCIES>
  </UPLOAD_DEPENDENCIES>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
