SELECT data_type
,BILLING_TP_TAXPAYER_ID DECLARED_NIF
,BILLING_TP_NAME DECLARED_NAME
,BILLING_TP_TAX_REG_NUM SUPP_TRN
,to_char(TRX_DATE,'DD-MM-RRRR') ISSUE_DATE
,REPORTING_YEAR YEAR
,REPORTING_PERIOD PERIOD
,to_char(TAX_INVOICE_DATE,'DD-MM-RRRR') TRANSACTION_DATE
,DOC_SEQ_NAME INVOICE_IDENT
,DOC_SEQ_VALUE REGISTER_NUMBER
,TRANSACTION_DEADLINE TRANSACTION_DEADLINE
,LTRIM(to_char(SUM(tax_amt),'999999999990.99')) TAX_AMT
,LTRIM(to_char(tax_amt_funcl_curr,'999999999990.99') ) INV_TOTAL_AMT
,LTRIM(to_char(SUM(taxable_amt),'999999999990.99')) TAXABLE_AMT
,LTRIM(to_char(tax_paid_amt,'999999999990.99')) TRANSMISSION_PROPERTY_AMT
,LTRIM(to_char(tax_rate,'990.99')) TAX_RATE
,TRX_DESCRIPTION INV_DESC
,EVENT_CLASS_CODE EVENT_CLASS_CODE
,to_char(GL_DATE,'DD-MM-RRRR') GL_DATE
,COUNTRY_CODE COUNTRY_CODE
,KEY_TYPE KEY_TYPE
,SPECIAL_REGIME SPECIAL_REGIME
,TRX_LINE_TYPE INVOICE_TYPE
,max(RECTIFIED_TYPE) RECTIFIED_TYPE
,ADJUSTED_DOC_NUMBER RECTI_DOC_SEQ_VALUE
,to_char(ADJUSTED_DOC_DATE,'DD-MM-RRRR') RECTI_TRX_DATE
,LTRIM(to_char(RECTIFIED_TAXABLE_AMT,'999999999990.99')) RECTI_TAXABLE_AMT
,LTRIM(to_char(RECTIFIED_TAX_AMT,'999999999990.99')) RECTI_TAX_AMT
,LTRIM(to_char(NVL(RECTIFIED_SURCHARGE_AMT,0),'999999999990.99')) RECTI_SURCHARGE_AMT
,LTRIM(to_char(SUM(NVL(SURCHARGE_AMOUNT,0)),'999999999990.99')) SURCHARGE_AMT
,LTRIM(to_char(SURCHARGE_RATE,'990.99')) SURCHARGE_RATE
,tax_rate_register_type_code TAX_TYPE --S1,S2,SE
,exempt_reason_code EXEMPTION_REASON
,property_location PROP_LOC
,property_rental PROP_RENTAL
,billing_tp_tax_reporting_flag THIRD_PARTY_INV
,tax_display_type TAX_DISPLAY_TYPE
,intra_eu_trx_type_code GOODS_SERVICES
,trx_id
,rep_context_entity_name F4_LAST_INV_NUM
,LTRIM(to_char(taxable_amt_funcl_curr,'999999999990.99')) TOT_TAXABLE_AMT
FROM JE_ES_ONLINE_VAT_TRX jeovt
WHERE nvl(jeovt.data_type, 'X') = 'AR'
AND jeovt.request_id = :P_REQUEST_ID
AND jeovt.file_number = :P_FILE_NUMBER
AND :P_REGISTER_TYPE = 'IIR'
AND jeovt.register_type = 'IIR'
AND jeovt.TRANSACTION_STATUS = :P_COMMUNICATION_TYPE
AND jeovt.DOC_SEQ_NAME <> '/' -- don't select trx which doesn't have doc seq/doc seq vale
GROUP BY
data_type
,BILLING_TP_TAXPAYER_ID
,BILLING_TP_NAME
,BILLING_TP_TAX_REG_NUM
,to_char(TRX_DATE,'DD-MM-RRRR')
,REPORTING_YEAR
,REPORTING_PERIOD
,to_char(TAX_INVOICE_DATE,'DD-MM-RRRR')
,DOC_SEQ_NAME
,DOC_SEQ_VALUE
,TRANSACTION_DEADLINE
--,to_char(TAX_AMT,'999999999990.99')
,to_char(TAX_AMT_FUNCL_CURR,'999999999990.99')
-- ,to_char(TAXABLE_AMT,'999999999990.99')
,to_char(TAX_PAID_AMT,'999999999990.99')
,to_char(TAX_RATE,'990.99')
,TRX_DESCRIPTION
,EVENT_CLASS_CODE
,to_char(GL_DATE,'DD-MM-RRRR')
,COUNTRY_CODE
,KEY_TYPE
,SPECIAL_REGIME
,TRX_LINE_TYPE
-- ,RECTIFIED_TYPE
,ADJUSTED_DOC_NUMBER
,to_char(ADJUSTED_DOC_DATE,'DD-MM-RRRR')
,to_char(RECTIFIED_TAXABLE_AMT,'999999999990.99')
,to_char(RECTIFIED_TAX_AMT,'999999999990.99')
,to_char(NVL(RECTIFIED_SURCHARGE_AMT,0),'999999999990.99')
-- ,to_char(SURCHARGE_AMOUNT,'999999999990.99')
,to_char(SURCHARGE_RATE,'990.99')
,tax_rate_register_type_code --S1,s2,s3
,exempt_reason_code --E1..E6
,property_location
,property_rental
,billing_tp_tax_reporting_flag
,tax_display_type
,intra_eu_trx_type_code
,trx_id
,rep_context_entity_name
,LTRIM(to_char(taxable_amt_funcl_curr,'999999999990.99'))
ORDER BY rectified_type nulls first ,invoice_ident |
