XXSJ Report VAT Register

Description
Columns: Source, Invoice Journal Type, Invoice Journal, Voucher Num, Legal Entity Name, Line Number, Supplier Customer, Supplier Cust Number, Address, Country ...
XXSJ Report VAT Register
SELECT distinct 'Payables'                   SOURCE
                          ,DECODE(AILA.PO_HEADER_ID, NULL, AIA.INVOICE_TYPE_LOOKUP_CODE, WER_ZX_VAT_REGISTER_RPT_PKG.GET_BUYER(AILA.PO_HEADER_ID)) INVOICE_JOURNAL_TYPE 
                          ,aia.INVOICE_NUM                       INVOICE_JOURNAL
                          ,to_char(aia.DOC_SEQUENCE_VALUE)       VOUCHER_NUM
                          ,xep.name                              legal_entity_name
                          ,aila.line_number                      LINE_NUMBER
                          ,asp.VENDOR_NAME                       SUPPLIER_CUSTOMER
                          ,asp.segment1                          SUPPLIER_CUST_NUMBER
                          ,assa.address_line1 ||' '|| assa.address_line2||' '|| assa.address_line3||' '|| assa.address_line4 ||' ' || assa.CITY       ADDRESS
                          ,ft.territory_short_name               country
                          ,assa.VAT_REGISTRATION_NUM             Tax_registration
                          ,zp.REP_REGISTRATION_NUMBER  
                          ,TO_CHAR(aia.invoice_date,'DD-MON-RRRR') Transaction_date
              ,TO_CHAR(aila.accounting_date,'DD-MON-RRRR')    gl_date 
              ,to_char(aila.accounting_date, 'MON-YY') PERIOD  
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment2(aia.invoice_id,aila.line_number,1,aila.tax_classification_code,zl.offset_flag)  company
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment2(aia.invoice_id,aila.line_number,2,aila.tax_classification_code,zl.offset_flag)  location
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment2(aia.invoice_id,aila.line_number,3,aila.tax_classification_code,zl.offset_flag)  cost_center
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment2(aia.invoice_id,aila.line_number,4,aila.tax_classification_code,zl.offset_flag)  account
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment2(aia.invoice_id,aila.line_number,5,aila.tax_classification_code,zl.offset_flag)  product
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment2(aia.invoice_id,aila.line_number,7,aila.tax_classification_code,zl.offset_flag)  member_group
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment2(aia.invoice_id,aila.line_number,8,aila.tax_classification_code,zl.offset_flag)  project
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment2(aia.invoice_id,aila.line_number,6,aila.tax_classification_code,zl.offset_flag)  intercompany
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_tax_authority(zl.tax_rate_id)  tax_authority
                          ,NVL(aila.tax_classification_code,zl.TAX_RATE_CODE)       tax_code
                          ,aia.invoice_amount                    gross_amount
                          ,decode(zl.offset_flag,'Y',0,WER_ZX_VAT_REGISTER_RPT_PKG.get_ap_line_amount(aia.invoice_id,aila.line_number,NVL(aila.tax_classification_code,zl.TAX_RATE_CODE))) line_amount
                          ,decode(zl.process_for_recovery_flag, 'Y', zl.tax_amt, zl.rec_tax_amt ) rec_tax_amount -- added by Sachin Pathak
                          ,decode(zl.offset_flag,'Y',0,WER_ZX_VAT_REGISTER_RPT_PKG.get_ap_line_amount(aia.invoice_id,aila.line_number,NVL(aila.tax_classification_code,zl.TAX_RATE_CODE)))+nvl(zl.REC_TAX_AMT,0)+nvl(zl.NREC_TAX_AMT,0)  Gross_line_amount -- added by Sachin Pathak
                          ,zl.NREC_TAX_AMT non_rec_tax_amt
                          ,aia.invoice_currency_code             trx_currency_code
                          ,decode(zl.process_for_recovery_flag, 'Y', zl.tax_amt, zl.rec_tax_amt ) TRX_CURR_AMOUNT -- added by Sachin Pathak
                          ,gl.currency_code                      fun_currency_code
                          ,decode(gl.currency_code,aia.invoice_currency_code, decode(zl.process_for_recovery_flag, 'Y', zl.tax_amt, zl.rec_tax_amt ),zl.REC_TAX_AMT_FUNCL_CURR)             FUN_CURR_AMOUNT  -- added by Sachin Pathak
                          ,zl.tax_currency_code                  vat_currency_code
                          ,decode(zl.tax_currency_code,aia.INVOICE_CURRENCY_CODE, decode(zl.process_for_recovery_flag, 'Y', zl.tax_amt, zl.rec_tax_amt ),gl.currency_code,zl.REC_TAX_AMT_FUNCL_CURR,zl.REC_TAX_AMT_TAX_CURR)           VAT_CURR_AMOUNT   -- added by Sachin Pathak
                          ,aila.description
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_created_by(aia.created_by)            created_by
                          ,zl.tax_rate                                                           tax_percentage
                          ,WER_ZX_VAT_REGISTER_RPT_PKG.get_tax_rate_description(zl.tax_rate_id)  tax_rate_description
                          ,decode(zl.offset_flag,'Y',0,decode(gl.currency_code,aia.invoice_currency_code,(DECODE(zl.TAX_AMT_INCLUDED_FLAG,'Y', (aila.amount- NVL(decode(zl.process_for_recovery_flag, 'Y', zl.tax_amt, zl.rec_tax_amt ),0)),aila.amount  + NVL(zl.NREC_TAX_AMT,0))),(decode(zl.TAX_AMT_INCLUDED_FLAG,'Y', (aila.base_amount - NVL(zl.REC_TAX_AMT_FUNCL_CURR,0)),(aila.base_amount + NVL(zl.NREC_TAX_AMT_FUNCL_CURR,0))))))                         NET_PUR_FUN_AMT
                          ,decode(zl.offset_flag,'Y',0,(WER_ZX_VAT_REGISTER_RPT_PKG.get_ap_net_pur_vat_amt(aia.invoice_id,aila.line_number, aia.invoice_currency_code,aia.invoice_date, zl.tax_rate_id ) + zl.NREC_TAX_AMT))                  NET_PUR_VAT_AMT
                      FROM ap_invoices_all aia
                          ,ap_invoice_lines_all aila
                          ,ap_invoice_distributions_all aida
                          ,ap_suppliers asp
                          ,ap_supplier_sites_all assa
                          ,gl_ledgers gl
                          ,xle_entity_profiles xep
                          ,zx_lines zl
                          ,fnd_territories_tl ft
                          ,ZX_PARTY_TAX_PROFILE zp
                     where 1=1
                       and aia.INVOICE_ID = aila.invoice_id
                       and aida.invoice_id = aia.invoice_id
                  and aida.INVOICE_LINE_NUMBER = aila.line_number
                       and aia.org_id = zl.INTERNAL_ORGANIZATION_ID
                       and zp.party_id(+) = asp.party_id
               AND ZP.party_type_code='THIRD_PARTY'
                       and aia.vendor_id = asp.vendor_id
                       and asp.vendor_id = assa.vendor_id
                       and aia.vendor_site_id = assa.vendor_site_id
                       and NVL(aila.DISCARDED_FLAG,'N') <> 'Y'  
                       AND NVL(aila.CANCELLED_FLAG,'N') = 'N'   
                       and aila.LINE_TYPE_LOOKUP_CODE IN ('ITEM') 
                       and gl.ledger_id = aia.set_of_books_id
                       and xep.legal_entity_id = aia.legal_entity_id
                       and zl.TRX_ID = aia.invoice_id
                       and zl.TRX_LINE_NUMBER = aila.line_number
                       and zl.TRX_NUMBER = aia.invoice_num
                       AND nvl(zl.CANCEL_FLAG,'N') = 'N'   
                       and ft.TERRITORY_CODE = assa.COUNTRY
                       AND ft.LANGUAGE = 'US'
                       and nvl(aida.posted_flag, 'N' ) = 'Y'
and rownum < 5
and 1=1
                       &lp_rep_context_entity_nameAP
                       &lp_legal_entity_idAP
                       &lp_gl_date_AP
                       &lp_trx_date_AP
                       &lp_tax_regimeAP
                       &lp_taxAP
                       &lp_currency_code_AP
                       &lp_vat_account_AP
                       &LP_REP_CONTEXT_LVL_MNG
UNION ALL
                SELECT distinct 'Payables'                             SOURCE
                      --,aia.INVOICE_TYPE_LOOKUP_CODE          INVOICE_JOURNAL_TYPE --Commented by Sachin
                      ,DECODE(AILA.PO_HEADER_ID, NULL, AIA.INVOICE_TYPE_LOOKUP_CODE, WER_ZX_VAT_REGISTER_RPT_PKG.GET_BUYER(AILA.PO_HEADER_ID)) INVOICE_JOURNAL_TYPE -- Added by Sachin
                      ,aia.INVOICE_NUM                       INVOICE_JOURNAL
                      ,to_char(aia.DOC_SEQUENCE_VALUE)       VOUCHER_NUM
                      ,xep.name                              legal_entity_name
                      ,aila.line_number                      LINE_NUMBER
                      ,asp.VENDOR_NAME                       SUPPLIER_CUSTOMER
                      ,asp.segment1                          SUPPLIER_CUST_NUMBER
                      ,assa.address_line1 ||' '|| assa.address_line2||' '|| assa.address_line3||' '|| assa.address_line4 ||' ' || assa.CITY       ADDRESS
                      ,ft.territory_short_name               country
                      ,assa.VAT_REGISTRATION_NUM             Tax_registration
                      ,zp.REP_REGISTRATION_NUMBER  -- Added by vasu on 12/06/2014
                      ,TO_CHAR(aia.invoice_date,'DD-MON-RRRR') Transaction_date
                      --,TO_CHAR(aia.GL_DATE,'DD-MON-RRRR')    gl_date
		      ,TO_CHAR(aila.accounting_date,'DD-MON-RRRR')    gl_date -- added this by Sachin on 02/09/14
		      ,to_char(aila.accounting_date, 'MON-YY') PERIOD  -- added by Sachin Pathak
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment(aia.invoice_id,aila.line_number,1)  company
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment(aia.invoice_id,aila.line_number,2)  location
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment(aia.invoice_id,aila.line_number,3)  cost_center
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment(aia.invoice_id,aila.line_number,4)  account
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment(aia.invoice_id,aila.line_number,5)  product
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment(aia.invoice_id,aila.line_number,7)  member_group
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment(aia.invoice_id,aila.line_number,8)  project
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_gcc_segment(aia.invoice_id,aila.line_number,6)  intercompany
                      ,NULL                                  tax_authority
                      ,NULL                                  tax_code
                      ,aia.invoice_amount                    gross_amount
                      ,aila.AMOUNT                           line_amount
                      ,NULL                                  rec_tax_amount
                      ,aila.AMOUNT                           Gross_line_amount  -- added by Sachin Pathak
                      ,NULL                                  non_rec_tax_amt
                      ,aia.invoice_currency_code             trx_currency_code
                      ,NULL                                  TRX_CURR_AMOUNT
                      ,gl.currency_code                      fun_currency_code
                      ,NULL                                  FUN_CURR_AMOUNT
                      ,NULL                                  vat_currency_code
                      ,NULL                                  VAT_CURR_AMOUNT
                      ,aila.description
                      ,WER_ZX_VAT_REGISTER_RPT_PKG.get_created_by(aia.created_by)            created_by
                      ,NULL                                  tax_percentage
                      ,