JA India - Tax Recovery Register Report(XML Publisher)

         select c.rep_context_entity_name operating_unit2,
				       d.first_party_primary_reg_num tax_registration_num2,
				       c.rep_organization_name organization2,
				       c.rep_location_name location2,
				       d.document_type,
				       d.trx_number                  document_number,
				       d.tax_regime_id tax_regime_id2,
				       d.tax_regime_name tax_regime_name2,
				       d.tax_type_name tax_type_name2,
				       d.tax_invoice_number,
				       -- d.tax_invoice_date, -- commented out by zhiwei.xin for bug#19450172
					   to_char(d.tax_invoice_date, 'dd-Mon-yy')  tax_invoice_date, -- added by zhiwei.xin for bug#19450172
				       d.party_name,
				       d.party_site_name,
				       null                          ecc_code,
				       d.product_code,
				       d.product_code,
				       d.trx_line_amt                transaction_amt,
				       d.tax_amt_funcl_curr          tax_amt,
				       -- e.date1                       recovered_date,  -- commented out by zhiwei.xin for bug#19450172
					   to_char(e.date1, 'dd-Mon-yy') recovered_date,     -- added by zhiwei.xin for bug#19450172  
				       e.numeric1                    recovered_amt,
				       d.applied_to_trx_number       receipt_number,
				       d.item_classification_code    item_class,
				       d.intended_use,
				       d.tax_reporting_type,
				       d.tax_reporting_code,
				       d.challan_number||decode(nvl(d.challan_number,'X'),d.challan_number,'/','X','')||d.challan_date	remark2
				  from jai_rep_context_t    c,
				       jai_rep_trx_detail_t d,
				       jai_rep_trx_jx_ext_t e
				 where c.rep_context_id = d.rep_context_id
				   and d.detail_tax_line_id = e.detail_tax_line_id
				   and c.extract_summary_code = 'H'
				   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 d.tax_type_id in (select d.tax_type_id
																  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
																   )
				   and c.request_id = :p_conc_request_id
				   --add by hongjun.wu for bug#21024088 begin--
				   AND (:cp_reg_reporting_context IS NULL OR
					   nvl(decode(:cp_reg_reporting_context,
								   'INTENDED_USE',
								   d.intended_use,
								   'ITEM_CLASS',
								   d.item_classification_code,
								   'REPORTING_CODE',
								   d.tax_reporting_code),
							'$$$') = nvl(:reporting_context_value, '$$$'))
				    --add by hongjun.wu for bug#21024088 end--
				 order by d.tax_type_name,d.document_type,d.trx_number,e.date1