select c.rep_context_entity_name operating_unit1,
d.first_party_primary_reg_num tax_registration_num1,
c.rep_organization_id organization_id1,
c.rep_organization_name organization_name1,
c.rep_location_id location_id1,
c.rep_location_name location_name1,
d.tax_regime_id tax_regime_id1,
d.tax_regime_name tax_regime_name1,
d.tax_type_id tax_type_id1,
d.tax_type_name tax_type_name1,
d.tax_opening_balance opening_balance_amt,
sum(nvl(d.liability_amount,0)) liability_amt,
sum(nvl(d.credit_utilized_amount,0)) recovery_offset_amt1,
sum(nvl(d.amount_applied,0)) cash_payment_amt1,
(nvl(d.tax_opening_balance,0) + sum(nvl(d.liability_amount,0)) -
sum(nvl(d.credit_utilized_amount,0)) - sum(nvl(d.amount_applied,0))) closing_balance_amt,
(select count(1)
from jai_rep_context_t c1, jai_rep_trx_detail_t d1
where c1.rep_context_id = d1.rep_context_id
and c1.extract_summary_code = 'L'
and d1.first_party_primary_reg_num = :tax_registration_num4
and c1.rep_organization_id = :organization_id4
and c1.rep_location_id = :location_id4
and d1.tax_regime_id = :tax_regime_id4
and (d1.trx_id, d1.trx_line_id) in (
select d2.trx_id , d2.trx_line_id
from jai_rep_context_t c2, jai_rep_trx_detail_t d2
where c2.rep_context_id = d2.rep_context_id
and c2.extract_summary_code = 'S'
and d2.first_party_primary_reg_num = :tax_registration_num4
and c2.rep_organization_id = :organization_id4
and c2.rep_location_id = :location_id4
and d2.tax_regime_id = :tax_regime_id4
and d2.tax_type_id = d.tax_type_id
and c2.request_id = :p_conc_request_id
)
and c1.request_id = :p_conc_request_id) offset_sum_count,
d.self_assessed_flag self_assessed_flag,
decode(d.self_assessed_flag,'Y',1,0) rev_num,
d.remark remark
from jai_rep_context_t c, jai_rep_trx_detail_t d
where c.rep_context_id = d.rep_context_id
and c.extract_summary_code = 'S'
and d.first_party_primary_reg_num = :tax_registration_num4
and c.rep_organization_id = :organization_id4
and c.rep_location_id = :location_id4
and d.tax_regime_id = :tax_regime_id4
and c.request_id = :p_conc_request_id
group by
c.rep_context_entity_name ,
d.first_party_primary_reg_num ,
c.rep_organization_id ,
c.rep_organization_name ,
c.rep_location_id ,
c.rep_location_name ,
d.tax_regime_id ,
d.tax_regime_name ,
d.tax_type_id ,
d.tax_type_name ,
d.tax_opening_balance ,
d.self_assessed_flag,
d.remark
order by rev_num,d.tax_type_name |
