JA India - Tax Liability Register Report(XML Publisher)

         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