select data_type
,EXTRACT(year from trx_date) YEAR
,es_correction_period PERIOD
,billing_tp_taxpayer_id DECLARED_NIF
,billing_tp_name DECLARED_NAME
,billing_tp_tax_reg_num SUPP_TRN
,doc_seq_name INVOICE_IDENT
,to_char(trx_date,'DD-MM-RRRR') ISSUE_DATE
,trx_description ASSET_DESC
,to_char(tax_invoice_date,'DD-MM-RRRR') PIS_DATE
,country_code COUNTRY_CODE
,key_type KEY_TYPE
,trx_number ASSET_NUMBER
,LTRIM(to_char(SUM(taxable_amt),'999999999990.99')) TAXABLE_AMT
,LTRIM(to_char(SUM(tax_amt),'999999999990.99')) TAX_AMT
,tax_recovery_rate TAX_RECOVERY_RATE
FROM je_es_online_vat_trx jeovt
WHERE nvl(jeovt.data_type, 'X') = 'IG'
AND jeovt.request_id = :P_REQUEST_ID
AND jeovt.file_number = :P_FILE_NUMBER
AND :P_REGISTER_TYPE = 'IGR'
AND jeovt.register_type = 'IGR'
--AND JEOVT.TRANSACTION_STATUS = :P_COMMUNICATION_TYPE
GROUP BY data_type
,EXTRACT(year from trx_date)
,es_correction_period
,billing_tp_taxpayer_id
,billing_tp_name
,billing_tp_tax_reg_num
,doc_seq_name
,to_char(trx_date,'DD-MM-RRRR')
,trx_description
,to_char(tax_invoice_date,'DD-MM-RRRR')
,country_code
,key_type
,trx_number
,tax_recovery_rate
,declared_key
ORDER BY doc_seq_name |
