SELECT
JZVTD.rep_context_entity_name LEGAL_ENTITY_NAME
,JZVTD.rep_context_entity_location_id LE_LOCATION_ID
,HLA.address_line_1 ADDRESS1
,HLA.address_line_2 ADDRESS2
,HLA.address_line_3 ADDRESS3
,HLA.town_or_city CITY
,HLA.postal_code POSTAL_CODE
,HLA.telephone_number_1 TELEPHONE
,JZVRS.tax_registration_number LE_TAX_REGISTRATION_NUMBER
,JZVTD.legal_contact_party_name CONTACT_NAME
,JZVRS.tax_calendar_year TAX_PERIOD_YEAR
,JZVRS.tax_calendar_period TAX_PERIOD_NAME
,to_char(JZVRS.period_start_date,'DD-Mon-RRRR') PERIOD_START_DATE
,to_char(JZVRS.period_end_date,'DD-Mon-RRRR') PERIOD_END_DATE
,JZVTD.extract_source_ledger EXTRACT_SOURCE_LEDGER
,JZVTD.event_class_code EVENT_CLASS_CODE
,JZVTD.period_name GL_PERIOD_NAME
,JZVTD.doc_seq_name DOC_SEQUENCE_NAME
,JZVTD.doc_seq_value DOC_SEQUENCE_NUMBER
,to_char(JZVTD.trx_date,'DD-Mon-RRRR') INVOICE_DATE
,JZVTD.billing_tp_party_number CUSTOMER_NUMBER
,JZVTD.billing_tp_taxpayer_id CUSTOMER_TAX_PAYER_ID
,JZVTD.billing_tp_site_name CUSTOMER_SITE_NAME
,JZVTD.billing_tp_name CUSTOMER_NAME
,JZVTD.billing_tp_tax_reg_num CUST_BILL_TAX_REG_NUM
,JZVTD.shipping_tp_tax_reg_num CUST_SHIP_AX_REG_NUM
,JZVTD.billing_tp_site_tax_reg_num CUST_SITE_BILL_TAX_REG_NUM
,JZVTD.shipping_tp_site_tax_reg_num CUST_SITE_SHIP_TAX_REG_NUM
,JZVTD.trx_number INVOICE_NUMBER
,NVL(JZVTD.taxable_amt,0) TAXABLE_AMT
,NVL(JZVTD.tax_amt,0) TAX_AMOUNT
,NVL(JZVTD.taxable_amt_funcl_curr,0) TAXABLE_AMT_FUNCL_CURR
,NVL(JZVTD.tax_amt_funcl_curr,0) TAX_AMOUNT_FUNCL_CURR
,JZVTD.trx_id TRX_ID
,to_char(JZVTD.cancelled_date,'DD-Mon-RRRR') TRX_CANCELLED_DATE
,JZVTD.cancel_flag TRX_CANCEL_FLAG
,JZVTD.offset_flag TAX_OFFSET_FLAG
,JZVTD.posted_flag POSTED_FLAG
,JZVTD.tax_recoverable_flag TAX_RECOVERABLE_FLAG
,JZVTD.account_flexfield ACCOUNT
,JZVTD.account_description ACCOUNT_DESCRIPTION
,JZVTD.trx_line_number LINE_NUMBER
,JZVTD.tax_type_code TAX_TYPE_CODE
,JZVTD.tax_type_mng TAX_TYPE_MEANING
,JZVTD.tax_rate TAX_RATE
,JZVTD.tax_rate_code TAX_RATE_CODE
,JZVTD.tax_rate_vat_trx_type_code TAX_RATE_VAT_TRX_TYPE_CODE
,JZVTD.tax_rate_vat_trx_type_desc TAX_RATE_VAT_TRX_TYPE_DESC
,JZVBA.taxable_box TAXABLE_REPORT_BOX
,JZVBA.tax_box TAX_REPORT_BOX
,to_char(JZVTD.accounting_date,'DD-Mon-RRRR') GL_DATE
,JZVTD.applied_to_trx_number APPLIED_TO_TRX_NUMBER
,VAT_REGISTERS.register_type VAT_REGISTER_TYPE
,VAT_REGISTERS.register_name VAT_REGISTER_NAME
,VAT_REGISTERS.db_sequence_name VAT_DOC_SEQ_NAME
,XEP.name REPORTER
,JZVTD.tax_rate_code_description TAX_RATE_DESCRIPTION
,JZVTD.trx_line_id TRX_LINE_ID
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,jg_zz_vat_box_allocs JZVBA
,hr_locations_all HLA
,xle_entity_profiles XEP
,(SELECT JZVRV.vat_reporting_entity_id vat_reporting_entity_id
,JZVRV.vat_register_id vat_register_id
,JZVRV.register_type register_type
,JZVRV.register_name register_name
,FDS.db_sequence_name db_sequence_name
,DOC_SEQ.doc_sequence_id doc_sequence_id
FROM jg_zz_vat_registers_vl JZVRV
,jg_zz_vat_doc_sequences DOC_SEQ
,fnd_document_sequences FDS
WHERE JZVRV.vat_register_id = DOC_SEQ.vat_register_id
AND FDS.doc_sequence_id = DOC_SEQ.doc_sequence_id
) VAT_REGISTERS
WHERE JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVTD.vat_transaction_id = JZVBA.vat_transaction_id(+)
/* AND VAT_REGISTERS.vat_reporting_entity_id (+)= JZVRS.mapping_vat_rep_entity_id */
AND VAT_REGISTERS.doc_sequence_id (+) = JZVTD.doc_seq_id
AND JZVTD.rep_context_entity_location_id = HLA.location_id
AND JZVTD.rep_entity_id = XEP.legal_entity_id
AND JZVRS.source = 'AR'
AND JZVRS.tax_calendar_period = :P_PERIOD
AND JZVRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
AND :P_REPORT_NAME is NULL |
