XXSJ Report VAT Register

Description
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
                      ,NULL                                  tax_rate_description
                      ,decode(gl.currency_code,aia.invoice_currency_code,aila.amount,aila.BASE_AMOUNT)    NET_PUR_FUN_AMT
                      ,NULL                                  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
                      ,fnd_territories_tl ft
                      ,ZX_PARTY_TAX_PROFILE zp  -- Added by vasu on 12/6/14
                 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.vendor_id = asp.vendor_id
                   and asp.vendor_id = assa.vendor_id
                   and aia.vendor_site_id = assa.vendor_site_id
                   and zp.party_id(+)=asp.party_id
		   AND ZP.party_type_code='THIRD_PARTY'
                   and NVL(aila.DISCARDED_FLAG,'N') <> 'Y'  
                   AND NVL(aila.CANCELLED_FLAG,'N') = 'N'
                   and aila.LINE_TYPE_LOOKUP_CODE = 'ITEM'
                   and gl.ledger_id = aia.set_of_books_id
                   and xep.legal_entity_id = aia.legal_entity_id
                   AND aila.tax_classification_code IS NULL
                   and ft.TERRITORY_CODE = assa.COUNTRY
                   AND ft.LANGUAGE = 'US'
                   AND NOT EXISTS (Select 1 from zx_lines where trx_id = aia.invoice_id and trx_line_id = aila.line_number)
                   --AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(aia.INVOICE_ID,aia.INVOICE_AMOUNT, aia.PAYMENT_STATUS_FLAG, aia.INVOICE_TYPE_LOOKUP_CODE) IN ( 'APPROVED','AVAILABLE','FULL','PERMANENT','UNPAID')
                   and nvl(aida.posted_flag, 'N' ) = 'Y'
                   &lp_rep_context_entity_nameAP
                   &lp_legal_entity_idAP
                   &lp_gl_date_AP
                   &lp_trx_date_AP
                   &lp_currency_code_AP
                   &lp_vat_account_AP
and rownum <5
        UNION ALL
                SELECT distinct 'Receivables'                  SOURCE
                       ,rctta.name                    INVOICE_JOURNAL_TYPE
                       ,rcta.trx_number               INVOICE_JOURNAL
                       ,to_char(rcta.doc_sequence_value)       VOUCHER_NUM
                       ,xep.name                      LEGAL_ENTITY_NAME
                       ,rctla.line_number             LINE_NUMBER
                       ,hp.party_name                 SUPPLIER_CUSTOMER
                       ,hca.ACCOUNT_NUMBER            SUPPLIER_CUST_NUMBER
                       ,hp.address1 ||' '|| hp.address2||' '|| hp.address3||' '|| hp.address4 ||' ' || hp.CITY ||' ' || hp.postal_code  ADDRESS
                       ,ft.territory_short_name       country
                       ,hcsua.tax_reference           Tax_registration
					   ,hcsua.tax_reference REP_REGISTRATION_NUMBER  -- Added by vasu on 12/06/2014 -- replaced by Anand on 12/16/14
                       ,to_char(rcta.trx_date,'DD-MON-RRRR') Transaction_date
                       ,to_Char(apsa.gl_date,'DD-MON-RRRR') gl_date
                       ,to_char(apsa.gl_date, 'MON-YY') PERIOD  -- added by Sachin Pathak
                       ,gcc.segment1                  company
                       ,gcc.segment2                  location
                       ,gcc.segment3                  cost_centre
                       ,gcc.segment4                  account
                       ,gcc.segment5                  product
                       ,gcc.segment7                  member_group
                       ,gcc.segment8                  project
                       ,gcc.segment6                  intercompany
                       ,WER_ZX_VAT_REGISTER_RPT_PKG.get_tax_authority(zl.tax_id)  tax_authority
                       ,zl.tax_rate_code              tax_code
                       ,apsa.amount_due_original      gross_amount
                       ,rctla.extended_amount         line_amount
                       ,zl.tax_amt                    rec_tax_amount
                       ,rctla.extended_amount+nvl(zl.tax_amt,0) Gross_line_amount  -- added by Sachin Pathak
                       ,null                          non_rec_tax_amt
                       ,rcta.INVOICE_CURRENCY_CODE    trx_currency_code
                       ,(zl.TAX_AMT * -1)             TRX_CURR_AMOUNT
                       ,gl.currency_code              fun_currency_code
                       ,(decode(gl.currency_code,rcta.INVOICE_CURRENCY_CODE,zl.TAX_AMT,zl.TAX_AMT_FUNCL_CURR) * -1)     FUN_CURR_AMOUNT
                       ,zl.tax_currency_code          vat_currency_code
					  -- ,(decode(zl.tax_currency_code,rcta.INVOICE_CURRENCY_CODE,zl.TAX_AMT,zl.TAX_AMT_TAX_CURR) * -1)           VAT_CURR_AMOUNT
                       ,decode(rctta.type,'CM',WER_ZX_VAT_REGISTER_RPT_PKG.get_tax_curr_amt(rcta.customer_trx_id),(decode(zl.tax_currency_code,rcta.INVOICE_CURRENCY_CODE,zl.TAX_AMT,zl.TAX_AMT_TAX_CURR)) * -1)           VAT_CURR_AMOUNT  -- Kamalakar 04-June-2020 modified for CM 
                       ,rctla.DESCRIPTION             description
                       ,WER_ZX_VAT_REGISTER_RPT_PKG.get_created_by(rcta.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(gl.currency_code,rcta.invoice_currency_code,(rctla.extended_amount + NVL(zl.NREC_TAX_AMT,0)),(zl.TAXABLE_AMT_FUNCL_CURR + NVL(zl.NREC_TAX_AMT_FUNCL_CURR,0)))*-1)                         NET_PUR_FUN_AMT
                       --,(decode(gl.currency_code,rcta.invoice_currency_code,(rctla.extended_amount + NVL(zl.NREC_TAX_AMT,0)),(zl.TAXABLE_AMT_TAX_CURR + NVL(zl.NREC_TAX_AMT_TAX_CURR,0)))*-1)                             NET_PUR_VAT_AMT
		      ,(decode(gl.currency_code,zl.tax_currency_code,(rctla.extended_amount + NVL(zl.NREC_TAX_AMT,0)),(zl.TAXABLE_AMT_TAX_CURR + NVL(zl.NREC_TAX_AMT_TAX_CURR,0)))*-1)                             NET_PUR_VAT_AMT
                  FROM ra_cust_trx_types_all     rctta
                      ,ra_customer_trx_all       rcta
                      ,ra_customer_trx_lines_all rctla
                      ,ar_payment_schedules_all  apsa
                      ,gl_code_combinations      gcc
                      ,zx_lines                  zl
                      ,ra_cust_trx_line_gl_dist_all rcgdla
                      ,hz_cust_acct_sites_all hcasa
                      ,hz_cust_accounts hca
                      ,hz_parties hp
                      ,hz_cust_site_uses_all hcsua
                      ,gl_ledgers gl
                      ,xle_entity_profiles xep
                      ,fnd_territories_tl ft
		      ,ZX_PARTY_TAX_PROFILE zp  -- Added by vasu on 12/6/14
                 WHERE 1=1
                   AND rcta.customer_trx_id =  rctla.customer_trx_id
                   AND rctta.cust_trx_type_id = rcta.cust_trx_type_id
                   and apsa.customer_trx_id = rcta.customer_trx_id
                   AND hcasa.cust_account_id = rcta.BILL_TO_CUSTOMER_ID
                   and rcta.BILL_TO_SITE_USE_ID= hcsua.SITE_USE_ID
                   AND hcasa.cust_account_id = hca.cust_account_id
                   AND apsa.cust_trx_type_id = rcta.cust_trx_type_id
                   AND hca.party_id = hp.party_id
				   AND zp.party_id(+)=hp.party_id -- Added by vasu
                   AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
                   AND hcsua.site_use_code = 'BILL_TO'
                   AND hcsua.status ='A'
                   AND rctta.org_id  = rcta.org_id
                   AND rcta.org_id = hcasa.org_id
                   and rcgdla.customer_trx_line_id = rctla.customer_trx_line_id
                   and rcta.CUSTOMER_TRX_ID = rcgdla.CUSTOMER_TRX_ID
                   and zl.TRX_ID = rcta.CUSTOMER_TRX_ID
                   and zl.TRX_NUMBER = rcta.TRX_NUMBER
                   AND nvl(zl.CANCEL_FLAG,'N') = 'N'
                   and rctla.LINE_NUMBER = zl.TRX_LINE_NUMBER
                   and rctla.line_type = 'LINE'
                   and gcc.code_combination_id =DECODE(WER_ZX_VAT_REGISTER_RPT_PKG.get_ar_tax_ccid(rctla.customer_trx_line_id,zl.tax_rate_id),0,rcgdla.code_combination_id,WER_ZX_VAT_REGISTER_RPT_PKG.get_ar_tax_ccid(rctla.customer_trx_line_id,zl.tax_rate_id))
                   and gl.ledger_id = rcta.set_of_books_id
                   and gl.ledger_id = rcgdla.set_of_books_id
                   and xep.legal_entity_id = rcta.legal_entity_id
                   and ft.TERRITORY_CODE = hp.COUNTRY
                   AND ft.LANGUAGE = 'US'
                   AND zl.application_id = 222
                   &lp_rep_context_entity_nameAR
                   &lp_legal_entity_idAR
                   &lp_gl_date_AR
                   &lp_trx_date_AR
                   &lp_tax_regimeAR
                   &lp_taxAR
                   &lp_currency_code_AR
                   &lp_vat_account_AR
and rownum < 5
        UNION ALL
                SELECT distinct 'Receivables'                  SOURCE
                       ,rctta.name                    INVOICE_JOURNAL_TYPE
                       ,rcta.trx_number               INVOICE_JOURNAL
                       ,to_char(rcta.doc_sequence_value)       VOUCHER_NUM
                       ,xep.name                      LEGAL_ENTITY_NAME
                       ,rctla.line_number             LINE_NUMBER
                       ,hp.party_name                 SUPPLIER_CUSTOMER
                       ,hca.ACCOUNT_NUMBER            SUPPLIER_CUST_NUMBER
                       ,hp.address1 ||' '|| hp.address2||' '|| hp.address3||' '|| hp.address4 ||' ' || hp.CITY ||' ' || hp.postal_code  ADDRESS
                       ,ft.territory_short_name       country
                       ,hcsua.tax_reference           Tax_registration
					   ,hcsua.tax_reference 		  REP_REGISTRATION_NUMBER  -- Added by vasu on 12/06/2014 -- replaced by Anand on 12/16/14
                       ,to_char(rcta.trx_date,'DD-MON-RRRR') Transaction_date
                       ,to_Char(apsa.gl_date,'DD-MON-RRRR') gl_date
                       ,to_Char(apsa.gl_date,'MON-YY') PERIOD
                       ,gcc.segment1                  company
                       ,gcc.segment2                  location
                       ,gcc.segment3                  cost_centre
                       ,gcc.segment4                  account
                       ,gcc.segment5                  product
                       ,gcc.segment7                  member_group
                       ,gcc.segment8                  project
                       ,gcc.segment6                  intercompany
                       ,NULL                          tax_authority
                       ,NULL                         tax_code
                       ,apsa.amount_due_original     gross_amount
                       ,rctla.extended_AMOUNT        line_amount
                       ,NULL                         rec_tax_amount
                       ,NVL(rctla.extended_AMOUNT,0) Gross_line_amount
                       ,null                          non_rec_tax_amt
                       ,rcta.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
                       ,rctla.DESCRIPTION             description
                       ,WER_ZX_VAT_REGISTER_RPT_PKG.get_created_by(rcta.created_by)           created_by
                       ,NULL                                                                  tax_percentage
                       ,NULL                                                                  tax_rate_description
                       ,WER_ZX_VAT_REGISTER_RPT_PKG.get_ar_dist_amt(rctla.customer_trx_line_id) NET_PUR_FUN_AMT
                       ,NULL                                                    NET_PUR_VAT_AMT
                  FROM ra_cust_trx_types_all     rctta
                      ,ra_customer_trx_all       rcta
                      ,ra_customer_trx_lines_all rctla
                      ,ar_payment_schedules_all  apsa
                      ,gl_code_combinations      gcc
                      ,ra_cust_trx_line_gl_dist_all rcgdla
                      ,hz_cust_acct_sites_all hcasa
                      ,hz_cust_accounts hca
                      ,hz_parties hp
                      ,hz_cust_site_uses_all hcsua
                      ,gl_ledgers gl
                      ,xle_entity_profiles xep
                      ,fnd_territories_tl ft
					  ,ZX_PARTY_TAX_PROFILE zp  -- Added by vasu on 12/6/14
                 WHERE 1=1
                   AND rcta.customer_trx_id =  rctla.customer_trx_id
                   AND rctta.cust_trx_type_id = rcta.cust_trx_type_id
                   and apsa.customer_trx_id = rcta.customer_trx_id
                   AND hcasa.cust_account_id = rcta.BILL_TO_CUSTOMER_ID
                   and rcta.BILL_TO_SITE_USE_ID= hcsua.SITE_USE_ID
                   AND hcasa.cust_account_id = hca.cust_account_id
                   AND apsa.cust_trx_type_id = rcta.cust_trx_type_id
                   AND hca.party_id = hp.party_id
		   AND zp.party_id(+)=hp.party_id -- Added by vasu
                   AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
                   AND hcsua.site_use_code = 'BILL_TO'
                   AND hcsua.status ='A'
                   AND rctta.org_id  = rcta.org_id
                   AND rcta.org_id = hcasa.org_id
                   and rcgdla.customer_trx_line_id = rctla.customer_trx_line_id
                   and rcta.CUSTOMER_TRX_ID = rcgdla.CUSTOMER_TRX_ID
                   and rctla.line_type = 'LINE'
                   and gcc.code_combination_id =rcgdla.code_combination_id
                   and gl.ledger_id = rcta.set_of_books_id
                   and gl.ledger_id = rcgdla.set_of_books_id
                   and xep.legal_entity_id = rcta.legal_entity_id
                   and ft.TERRITORY_CODE = hp.COUNTRY
                   AND ft.LANGUAGE = 'US'
                   AND rctla.tax_classification_code is null
                   and NOT EXISTS (select 1 from apps.zx_lines
                                   where trx_id = rcta.customer_trx_id
                                   and trx_line_id = rctla.customer_trx_line_id)
                   &lp_rep_context_entity_nameAR
                   &lp_legal_entity_idAR
                   &lp_gl_date_AR
                   &lp_trx_date_AR
                   &lp_currency_code_AR
                   &lp_vat_account_AR
and rownum < 5
            UNION ALL
                   SELECT 'General Ledger'                             SOURCE
                           ,gjc.user_je_category_name                  INVOICE_JOURNAL_TYPE
                           ,gjh.name                                   INVOICE_JOURNAL
                           ,to_char(gjh.doc_sequence_value)            VOUCHER_NUM
                           ,flv1.meaning                               LEGAL_ENTITY_NAME
                           ,gjl.je_line_num                            LINE_NUMBER
                           ,NULL                                       SUPPLIER_CUSTOMER
                           ,NULL                                       SUPPLIER_CUST_NUMBER
                           ,NULL                                       ADDRESS
                           ,NULL                                       country
                           ,NULL                                       Tax_registration
						   ,NULL REP_REGISTRATION_NUMBER  -- Added by vasu on 12/06/2014
                           ,to_char(gjh.default_effective_date,'DD-MON-RRRR') Transaction_date
                           ,TO_CHAR(gjh.default_effective_date,'DD-MON-RRRR') gl_date
                           ,TO_CHAR(gjh.default_effective_date,'MON-YY') PERIOD
                           ,gcc.segment1                               company
                           ,gcc.segment2                               location
                           ,gcc.segment3                               cost_centre
                           ,gcc.segment4                               account
                           ,gcc.segment5                               product
                           ,gcc.segment7                               member_group
                           ,gcc.segment8                               project
                           ,gcc.segment6                               intercompany
                           ,NULL                                       tax_authority
                           ,NULL                                       TAX_CODE
                           ,NULL                                       gross_amount
                           ,NULL                                       line_amount
                           ,NULL                                       rec_tax_amount
                           ,NULL                                       Gross_line_amount
                           ,NULL                                       non_rec_tax_amt
                           ,gjh.currency_code                          trx_currency_code
                           ,DECODE(NVL(gjl.entered_dr,0),0,(-1 * gjl.entered_cr),gjl.entered_dr)         TRX_CURR_AMOUNT
                           ,gl.CURRENCY_CODE                           fun_currency_code
                           ,DECODE(NVL(gjl.accounted_dr,0),0, (-1 * gjl.accounted_cr),gjl.accounted_dr)     FUN_CURR_AMOUNT
                           ,NULL                                       vat_currency_code
                           ,NULL                                       VAT_CURR_AMOUNT
                           ,gjl.description                            description
                           ,WER_ZX_VAT_REGISTER_RPT_PKG.get_created_by(gjh.created_by)   created_by
                           ,NULL                                       tax_percentage
                           ,NULL                                       tax_rate_description
                           ,NULL                                       NET_PUR_FUN_AMT
                           ,NULL                                       NET_PUR_VAT_AMT
                       FROM gl_je_headers gjh
                           ,gl_je_lines gjl
                           ,gl_je_categories gjc
                           ,gl_code_combinations gcc
                           ,fnd_lookup_values flv
                           ,fnd_lookup_values flv1
                           ,xle_entity_profiles xep
                           ,hr_operating_units hou
                           ,gl_ledgers gl
                      WHERE gjh.je_header_id = gjl.je_header_id
                        AND gjh.je_category= gjc.je_category_name
                        AND gjh.LEDGER_ID = gl.ledger_id
                        AND gl.ledger_id = hou.SET_OF_BOOKS_ID
                        and flv.lookup_type = 'WER_TAX_REGISTER_GL_ACCOUNTS'
                        and flv.language = USERENV('LANG')
                        and flv.enabled_flag= 'Y'
                        and flv.lookup_code = gcc.segment4
                        and flv1.lookup_type = 'PAY GROUP'
                        and flv1.language = USERENV('LANG')
                        and flv1.enabled_flag= 'Y'
                        and flv1.lookup_code = gcc.segment1
                        and gcc.code_combination_id = gjl.code_combination_id
                        and hou.set_of_books_id = gjh.ledger_id
                        AND xep.name = flv1.meaning
                        and gjh.je_source NOT IN ('Receivables','Payables')
                        &lp_rep_context_entity_nameGL
                        &lp_legal_entity_idGL
                        &lp_gl_date_GL
                        &lp_trx_date_GL
                        &lp_currency_code_GL
                        &lp_vat_account_GL
and rownum < 5
                     ORDER BY SOURCE
                          ,invoice_journal_type
                          ,invoice_journal
                          ,legal_entity_name
                          ,line_number asc
Parameter NameSQL textValidation
P_REP_CONTEXT_LVL_MNG
and 
(:P_REP_CONTEXT_LVL_MNG ='1000'
        and gl.ledger_id = :P_REP_CONTEXT_ENTITY_NAME)
Char
P_REP_CONTEXT_ENTITY_NAME
and 1=1
Char
P_LEGAL_ENTITY_ID
and 1=1
Char
P_GL_DATE_LOW
and 1=1
Char
P_GL_DATE_HIGH
and 1=1
Char
P_TRX_DATE_LOW
and 1=1
Char
P_TRX_DATE_HIGH
and 1=1
Char
P_TAX_REGIME
and 1=1
Char
P_TAX
and 1=1
Char
P_CURRENCY_CODE_LOW
and 1=1
Char
P_CURRENCY_CODE_HIGH
and 1=1
Char
P_INPUT_VAT_ACCOUNT
and 1=1
Char
P_OUTPUT_VAT_ACCOUNT
and 1=1
Char
Download
Blitz Report™