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 + |