XXSJ Report VAT Register
Description
XXSJ Report VAT Register
Run
XXSJ Report VAT Register and other Oracle EBS reports with Blitz Report™ on our demo environment
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 Name | SQL text | Validation | |
|---|---|---|---|
| P_REP_CONTEXT_LVL_MNG |
|
Char | |
| P_REP_CONTEXT_ENTITY_NAME |
|
Char | |
| P_LEGAL_ENTITY_ID |
|
Char | |
| P_GL_DATE_LOW |
|
Char | |
| P_GL_DATE_HIGH |
|
Char | |
| P_TRX_DATE_LOW |
|
Char | |
| P_TRX_DATE_HIGH |
|
Char | |
| P_TAX_REGIME |
|
Char | |
| P_TAX |
|
Char | |
| P_CURRENCY_CODE_LOW |
|
Char | |
| P_CURRENCY_CODE_HIGH |
|
Char | |
| P_INPUT_VAT_ACCOUNT |
|
Char | |
| P_OUTPUT_VAT_ACCOUNT |
|
Char |
