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 |
