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 |