eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.JAI_AP_STREVIEW_AEL_V

Source

View Text - Preformatted


SELECT 7000 application_id, 'Repository Review' event_type, 'JAI AP REPOREVIEW' trx_class, 'JAI AP REPOSITORY REVIEW' trx_class_name, apip.invoice_payment_id trx_hdr_id, 'JAI_RGM_TRX_RECORDS' trx_hdr_table, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, ai.invoice_date transaction_date, pv.party_number party_number, pvs.vendor_site_code party_site, ood.organization_name organization_name, hr1.description location, pv.vendor_name party_name, gjr.reference_3 TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, apip.invoice_payment_id TRX_SOURCE_ID FROM ap_invoice_payments_all apip, ap_invoices_all ai, po_vendors pv, po_vendor_sites_all pvs, org_organization_definitions ood, hr_locations hr1, ( SELECT DISTINCT jrr.inv_organization_id organization_id, jrr.location_id location_id, jrr.source_document_id invoice_payment_id FROM jai_rgm_trx_records jrr WHERE jrr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL' AND jrr.source = 'AP' ) rgm, gl_daily_conversion_types glct, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND apip.invoice_id = ai.invoice_id AND ai.vendor_id = pv.vendor_id(+) AND ai.party_site_id = pvs.vendor_site_id(+) AND ood.organization_id = rgm.organization_id AND hr1.location_id = rgm.location_id AND rgm.invoice_payment_id = apip.invoice_payment_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'AP_PROCESSING' AND gjr.reference_4 = 'AP_INVOICE_PAYMENTS_ALL' AND gjr.reference_5 = TO_CHAR(rgm.invoice_payment_id) AND gjr.reference_6 = 'JAI_RGM_TRX_RECORDS' AND gjs.je_source_name = 'Service Tax India' AND gjc.je_category_name = 'Register India' UNION ALL SELECT 7000 APPLICATION_ID, 'Repository Review' EVENT_TYPE, 'JAI AP REPOREVIEW' TRX_CLASS, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME, rgm.invoice_distribution_id TRX_HDR_ID, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE, GJH.LEDGER_ID SET_OF_BOOKS_ID, GJH.JE_CATEGORY JE_CATEGORY, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME, GJH.JE_SOURCE JE_SOURCE, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME, GJL.JE_HEADER_ID JE_HEADER_ID, GJL.JE_LINE_NUM JE_LINE_NUM, GJH.CURRENCY_CODE ENTERED_CURRENCY, GJL.ENTERED_DR ENTERED_DR, GJL.ENTERED_CR ENTERED_CR, GJL.ACCOUNTED_DR ACCOUNTED_DR, GJL.ACCOUNTED_CR ACCOUNTED_CR, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE, GJL.CODE_COMBINATION_ID ACCOUNT_ID, GJL.EFFECTIVE_DATE ACCOUNTING_DATE, AI.INVOICE_DATE TRANSACTION_DATE, PV.PARTY_NUMBER PARTY_NUMBER, PVS.VENDOR_SITE_CODE PARTY_SITE, OOD.ORGANIZATION_NAME ORGANIZATION_NAME, HR1.DESCRIPTION LOCATION, PV.VENDOR_NAME PARTY_NAME, gjr.reference_3 TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, rgm.invoice_distribution_id TRX_SOURCE_ID FROM ap_invoice_distributions_all aida, AP_INVOICES_ALL AI, PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS, ORG_ORGANIZATION_DEFINITIONS OOD, HR_LOCATIONS HR1, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID, JRR.LOCATION_ID LOCATION_ID, JRR.SOURCE_DOCUMENT_ID invoice_distribution_id FROM JAI_RGM_TRX_RECORDS JRR WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND JRR.SOURCE = 'AP' ) RGM, GL_DAILY_CONVERSION_TYPES GLCT, GL_JE_CATEGORIES GJC, GL_JE_SOURCES GJS, GL_IMPORT_REFERENCES GJR, GL_JE_HEADERS GJH, GL_JE_LINES GJL WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE AND aida.INVOICE_ID = AI.INVOICE_ID AND AI.VENDOR_ID = PV.VENDOR_ID(+) AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+) AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID AND HR1.LOCATION_ID = RGM.LOCATION_ID AND RGM.invoice_distribution_id = aida.invoice_distribution_id AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID AND GJR.REFERENCE_2 = 'India Localization Entry' AND GJR.REFERENCE_3 = 'AP_PROCESSING' AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND GJR.REFERENCE_5 = TO_CHAR(RGM.invoice_distribution_id) AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS' AND GJS.JE_SOURCE_NAME = 'Service Tax India' AND GJC.JE_CATEGORY_NAME = 'Register India' UNION ALL SELECT 7000 application_id, 'Repository Review' event_type, 'JAI AP REPOREVIEW' trx_class, 'JAI AP REPOSITORY REVIEW' trx_class_name, aida.invoice_distribution_id trx_hdr_id, 'JAI_RGM_TRX_RECORDS' trx_hdr_table, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, ai.invoice_date transaction_date, pv.party_number party_number, pvs.vendor_site_code party_site, ood.organization_name organization_name, hr1.description location, pv.vendor_name party_name, gjr.reference_3 TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, aida.invoice_distribution_id TRX_SOURCE_ID FROM ap_invoice_distributions_all aida, ap_invoices_all ai, po_vendors pv, po_vendor_sites_all pvs, org_organization_definitions ood, hr_locations hr1, ( SELECT DISTINCT jrr.inv_organization_id organization_id, jrr.location_id location_id, jrr.source_document_id invoice_distribution_id FROM jai_rgm_trx_records jrr WHERE jrr.source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND jrr.source = 'AP' ) rgm, gl_daily_conversion_types glct, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND aida.invoice_id = ai.invoice_id AND ai.vendor_id = pv.vendor_id(+) AND ai.party_site_id = pvs.vendor_site_id(+) AND ood.organization_id = rgm.organization_id AND hr1.location_id = rgm.location_id AND rgm.invoice_distribution_id = aida.invoice_distribution_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'AP_ACCOUNTING' AND gjr.reference_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND gjr.reference_5 = TO_CHAR(rgm.invoice_distribution_id) AND gjr.reference_6 = 'invoice_distribution_id' AND gjs.je_source_name = 'Service Tax India' AND gjc.je_category_name = 'Register India' UNION ALL SELECT 7000 APPLICATION_ID, 'Repository Review' EVENT_TYPE, 'JAI AP REPOREVIEW' TRX_CLASS, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME, aida.invoice_distribution_id trx_hdr_id, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE, GJH.LEDGER_ID SET_OF_BOOKS_ID, GJH.JE_CATEGORY JE_CATEGORY, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME, GJH.JE_SOURCE JE_SOURCE, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME, GJL.JE_HEADER_ID JE_HEADER_ID, GJL.JE_LINE_NUM JE_LINE_NUM, GJH.CURRENCY_CODE ENTERED_CURRENCY, GJL.ENTERED_DR ENTERED_DR, GJL.ENTERED_CR ENTERED_CR, GJL.ACCOUNTED_DR ACCOUNTED_DR, GJL.ACCOUNTED_CR ACCOUNTED_CR, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE, GJL.CODE_COMBINATION_ID ACCOUNT_ID, GJL.EFFECTIVE_DATE ACCOUNTING_DATE, AI.INVOICE_DATE TRANSACTION_DATE, PV.PARTY_NUMBER PARTY_NUMBER, PVS.VENDOR_SITE_CODE PARTY_SITE, OOD.ORGANIZATION_NAME ORGANIZATION_NAME, HR1.DESCRIPTION LOCATION, PV.VENDOR_NAME PARTY_NAME, gjr.reference_3 TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, aida.invoice_distribution_id TRX_SOURCE_ID FROM ap_invoice_distributions_all aida, AP_INVOICES_ALL AI, PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS, ORG_ORGANIZATION_DEFINITIONS OOD, HR_LOCATIONS HR1, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID, JRR.LOCATION_ID LOCATION_ID, JRR.SOURCE_DOCUMENT_ID invoice_distribution_id FROM JAI_RGM_TRX_RECORDS JRR WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND JRR.source_trx_type = 'INVOICE_CANCELLATION' AND JRR.SOURCE = 'AP' ) RGM, GL_DAILY_CONVERSION_TYPES GLCT, GL_JE_CATEGORIES GJC, GL_JE_SOURCES GJS, GL_IMPORT_REFERENCES GJR, GL_JE_HEADERS GJH, GL_JE_LINES GJL WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE AND AI.INVOICE_ID = aida.INVOICE_ID AND AI.VENDOR_ID = PV.VENDOR_ID(+) AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+) AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID AND HR1.LOCATION_ID = RGM.LOCATION_ID AND RGM.invoice_distribution_id = aida.invoice_distribution_id AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID AND GJR.REFERENCE_2 = 'India Localization Entry' AND GJR.REFERENCE_3 = 'AP_CANCELLATION' AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND GJR.REFERENCE_5 = TO_CHAR(RGM.invoice_distribution_id) AND GJR.REFERENCE_6 = 'invoice_distribution_id' AND GJS.JE_SOURCE_NAME = 'Service Tax India' AND GJC.JE_CATEGORY_NAME = 'Register India' UNION ALL SELECT 7000 APPLICATION_ID, 'Repository Review' EVENT_TYPE, 'JAI AP REPOREVIEW' TRX_CLASS, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME, aida.invoice_distribution_id trx_hdr_id, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE, GJH.LEDGER_ID SET_OF_BOOKS_ID, GJH.JE_CATEGORY JE_CATEGORY, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME, GJH.JE_SOURCE JE_SOURCE, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME, GJL.JE_HEADER_ID JE_HEADER_ID, GJL.JE_LINE_NUM JE_LINE_NUM, GJH.CURRENCY_CODE ENTERED_CURRENCY, GJL.ENTERED_DR ENTERED_DR, GJL.ENTERED_CR ENTERED_CR, GJL.ACCOUNTED_DR ACCOUNTED_DR, GJL.ACCOUNTED_CR ACCOUNTED_CR, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE, GJL.CODE_COMBINATION_ID ACCOUNT_ID, GJL.EFFECTIVE_DATE ACCOUNTING_DATE, AI.INVOICE_DATE TRANSACTION_DATE, PV.PARTY_NUMBER PARTY_NUMBER, PVS.VENDOR_SITE_CODE PARTY_SITE, OOD.ORGANIZATION_NAME ORGANIZATION_NAME, HR1.DESCRIPTION LOCATION, PV.VENDOR_NAME PARTY_NAME, gjr.reference_3 TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, aida.invoice_distribution_id TRX_SOURCE_ID FROM ap_invoice_distributions_all aida, AP_INVOICES_ALL AI, PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS, ORG_ORGANIZATION_DEFINITIONS OOD, HR_LOCATIONS HR1, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID, JRR.LOCATION_ID LOCATION_ID, JRR.SOURCE_DOCUMENT_ID invoice_distribution_id FROM JAI_RGM_TRX_RECORDS JRR WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND JRR.SOURCE = 'AP_REVERSAL' ) RGM, GL_DAILY_CONVERSION_TYPES GLCT, GL_JE_CATEGORIES GJC, GL_JE_SOURCES GJS, GL_IMPORT_REFERENCES GJR, GL_JE_HEADERS GJH, GL_JE_LINES GJL WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE AND AI.INVOICE_ID = aida.INVOICE_ID AND AI.VENDOR_ID = PV.VENDOR_ID(+) AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+) AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID AND HR1.LOCATION_ID = RGM.LOCATION_ID AND RGM.invoice_distribution_id = aida.invoice_distribution_id AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID AND GJR.REFERENCE_2 = 'India Localization Entry' AND GJR.REFERENCE_3 = 'REVERSAL_ACCOUNTING' AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND GJR.REFERENCE_5 = TO_CHAR(RGM.invoice_distribution_id) AND GJR.REFERENCE_6 = 'invoice_distribution_id' AND GJS.JE_SOURCE_NAME = 'Service Tax India' AND GJC.JE_CATEGORY_NAME = 'Register India' UNION ALL SELECT 7000 application_id, 'Repository Review' event_type, 'JAI AP REPOREVIEW' trx_class, 'JAI AP REPOSITORY REVIEW' trx_class_name, apip.invoice_payment_id trx_hdr_id, 'JAI_RGM_TRX_RECORDS' trx_hdr_table, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, ai.invoice_date transaction_date, pv.party_number party_number, pvs.vendor_site_code party_site, ood.organization_name organization_name, hr1.description location, pv.vendor_name party_name, gjr.reference_3 TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, apip.invoice_payment_id TRX_SOURCE_ID FROM ap_invoice_payments_all apip, ap_invoices_all ai, po_vendors pv, po_vendor_sites_all pvs, org_organization_definitions ood, hr_locations hr1, ( SELECT DISTINCT jrr.inv_organization_id organization_id, jrr.location_id location_id, jrr.source_document_id invoice_payment_id FROM jai_rgm_trx_records jrr WHERE jrr.source_table_name = 'AP_INVOICE_PAYMENTS_ALL' AND jrr.source = 'AP_CLAIM' ) rgm, gl_daily_conversion_types glct, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND apip.invoice_id = ai.invoice_id AND ai.vendor_id = pv.vendor_id(+) AND ai.party_site_id = pvs.vendor_site_id(+) AND ood.organization_id = rgm.organization_id AND hr1.location_id = rgm.location_id AND rgm.invoice_payment_id = apip.invoice_payment_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'CLAIM_ACCOUNTING' AND gjr.reference_4 = 'AP_INVOICE_PAYMENTS_ALL' AND gjr.reference_5 = TO_CHAR(rgm.invoice_payment_id) AND gjr.reference_6 = 'invoice_payment_id' AND gjs.je_source_name = 'Service Tax India' AND gjc.je_category_name = 'Register India' UNION ALL SELECT 7000 APPLICATION_ID, 'Repository Review' EVENT_TYPE, 'JAI AP REPOREVIEW' TRX_CLASS, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME, aida.invoice_distribution_id trx_hdr_id, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE, GJH.LEDGER_ID SET_OF_BOOKS_ID, GJH.JE_CATEGORY JE_CATEGORY, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME, GJH.JE_SOURCE JE_SOURCE, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME, GJL.JE_HEADER_ID JE_HEADER_ID, GJL.JE_LINE_NUM JE_LINE_NUM, GJH.CURRENCY_CODE ENTERED_CURRENCY, GJL.ENTERED_DR ENTERED_DR, GJL.ENTERED_CR ENTERED_CR, GJL.ACCOUNTED_DR ACCOUNTED_DR, GJL.ACCOUNTED_CR ACCOUNTED_CR, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE, GJL.CODE_COMBINATION_ID ACCOUNT_ID, GJL.EFFECTIVE_DATE ACCOUNTING_DATE, AI.INVOICE_DATE TRANSACTION_DATE, PV.PARTY_NUMBER PARTY_NUMBER, PVS.VENDOR_SITE_CODE PARTY_SITE, OOD.ORGANIZATION_NAME ORGANIZATION_NAME, HR1.DESCRIPTION LOCATION, PV.VENDOR_NAME PARTY_NAME, SUBSTR(GJL.description, LENGTH('Service Tax Accounting for A') + LENGTH('P. Transaction Type:') + 1, instr(GJL.description, '. Invoice Number:') - LENGTH('Service Tax Accounting for A') - LENGTH('P. Transaction Type:') - 1) TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, aida.invoice_distribution_id TRX_SOURCE_ID FROM ap_invoice_distributions_all aida, AP_INVOICES_ALL AI, PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS, ORG_ORGANIZATION_DEFINITIONS OOD, HR_LOCATIONS HR1, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID, JRR.LOCATION_ID LOCATION_ID, JRR.SOURCE_DOCUMENT_ID invoice_distribution_id FROM JAI_RGM_TRX_RECORDS JRR WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND JRR.SOURCE = 'AP' AND JRR.SOURCE_TRX_TYPE IN ('REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING') ) RGM, GL_DAILY_CONVERSION_TYPES GLCT, GL_JE_CATEGORIES GJC, GL_JE_SOURCES GJS, GL_IMPORT_REFERENCES GJR, GL_JE_HEADERS GJH, GL_JE_LINES GJL WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE AND AI.INVOICE_ID = aida.INVOICE_ID AND AI.VENDOR_ID = PV.VENDOR_ID(+) AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+) AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID AND HR1.LOCATION_ID = RGM.LOCATION_ID AND RGM.invoice_distribution_id = aida.invoice_distribution_id AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID AND GJR.REFERENCE_2 = 'India Localization Entry' AND GJR.REFERENCE_3 = 'REVERSE_CHARGE_ACCOUNTING' AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL' AND GJR.REFERENCE_5 = TO_CHAR(RGM.invoice_distribution_id) AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS' AND GJS.JE_SOURCE_NAME = 'Service Tax India' AND GJC.JE_CATEGORY_NAME = 'Register India' UNION ALL SELECT 7000 application_id, 'Repository Review' event_type, 'JAI AP REPOREVIEW' trx_class, 'JAI AP REPOSITORY REVIEW' trx_class_name, apip.invoice_payment_id trx_hdr_id, 'JAI_RGM_TRX_RECORDS' trx_hdr_table, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, ai.invoice_date transaction_date, pv.party_number party_number, pvs.vendor_site_code party_site, ood.organization_name organization_name, hr1.description location, pv.vendor_name party_name, SUBSTR(GJL.description, LENGTH('Service Tax Accounting for A') + LENGTH('P. Transaction Type:') + 1, instr(GJL.description, '. Invoice Number:') - LENGTH('Service Tax Accounting for A') - LENGTH('P. Transaction Type:') - 1) TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, apip.invoice_payment_id TRX_SOURCE_ID FROM ap_invoice_payments_all apip, ap_invoices_all ai, po_vendors pv, po_vendor_sites_all pvs, org_organization_definitions ood, hr_locations hr1, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID, JRR.LOCATION_ID LOCATION_ID, JRR.SOURCE_DOCUMENT_ID invoice_payment_id FROM JAI_RGM_TRX_RECORDS JRR WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_PAYMENTS_ALL' AND JRR.SOURCE = 'AP' AND JRR.SOURCE_TRX_TYPE IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING') ) rgm, gl_daily_conversion_types glct, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND apip.invoice_id = ai.invoice_id AND ai.vendor_id = pv.vendor_id(+) AND ai.party_site_id = pvs.vendor_site_id(+) AND ood.organization_id = rgm.organization_id AND hr1.location_id = rgm.location_id AND rgm.invoice_payment_id = apip.invoice_payment_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND GJR.REFERENCE_2 = 'India Localization Entry' AND GJR.REFERENCE_3 = 'REVERSE_CHARGE_ACCOUNTING' AND GJR.REFERENCE_4 = 'AP_INVOICE_PAYMENTS_ALL' AND GJR.REFERENCE_5 = TO_CHAR(RGM.invoice_payment_id) AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS' AND GJS.JE_SOURCE_NAME = 'Service Tax India' AND GJC.JE_CATEGORY_NAME = 'Register India' UNION ALL SELECT 7000 application_id, 'Repository Review' event_type, 'JAI AP REPOREVIEW' trx_class, 'JAI AP REPOSITORY REVIEW' trx_class_name, RGM.settlement_id trx_hdr_id, 'JAI_RGM_TRX_RECORDS' trx_hdr_table, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, ai.invoice_date transaction_date, pv.party_number party_number, pvs.vendor_site_code party_site, ood.organization_name organization_name, hr1.description location, pv.vendor_name party_name, SUBSTR(GJL.description, LENGTH('Service Tax Accounting for A') + LENGTH('P. Transaction Type:') + 1, instr(GJL.description, '. Invoice Number:') - LENGTH('Service Tax Accounting for A') - LENGTH('P. Transaction Type:') - 1) TRX_SOURCE_TYPE_NAME, gjr.reference_4 SOURCE_TABLE, to_number(gjr.reference_6) TRX_SOURCE_ID FROM ap_invoices_all ai, po_vendors pv, po_vendor_sites_all pvs, org_organization_definitions ood, hr_locations hr1, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID, JRR.LOCATION_ID LOCATION_ID, JRR.SOURCE_DOCUMENT_ID settlement_id, JRF.INVOICE_ID invoice_id FROM JAI_RGM_TRX_RECORDS JRR, JAI_RGM_TRX_REFS JRF WHERE JRR.SOURCE_TABLE_NAME = 'JAI_RGM_SETTLEMENTS' AND JRR.SOURCE = 'AP' AND JRR.SOURCE_TRX_TYPE = 'INVOICE_SETTLEMENT' AND JRR.REFERENCE_ID = JRF.REFERENCE_ID ) rgm, gl_daily_conversion_types glct, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND ai.vendor_id = pv.vendor_id(+) AND ai.party_site_id = pvs.vendor_site_id(+) AND ood.organization_id = rgm.organization_id AND hr1.location_id = rgm.location_id AND rgm.invoice_id = ai.invoice_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND GJR.REFERENCE_2 = 'India Localization Entry' AND GJR.REFERENCE_3 = 'JAIRGMSP' AND GJR.REFERENCE_4 = 'JAI_RGM_SETTLEMENTS' AND GJR.REFERENCE_5 = TO_CHAR(RGM.settlement_id) AND GJR.REFERENCE_6 = TO_CHAR(RGM.invoice_id) AND GJS.JE_SOURCE_NAME = 'Service Tax India' AND GJC.JE_CATEGORY_NAME = 'Register India'

View Text - HTML Formatted

SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, APIP.INVOICE_PAYMENT_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, GJR.REFERENCE_3 TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, APIP.INVOICE_PAYMENT_ID TRX_SOURCE_ID
FROM AP_INVOICE_PAYMENTS_ALL APIP
, AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID INVOICE_PAYMENT_ID
FROM JAI_RGM_TRX_RECORDS JRR
WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_PAYMENTS_ALL'
AND JRR.SOURCE = 'AP' ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND APIP.INVOICE_ID = AI.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_PAYMENT_ID = APIP.INVOICE_PAYMENT_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'AP_PROCESSING'
AND GJR.REFERENCE_4 = 'AP_INVOICE_PAYMENTS_ALL'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.INVOICE_PAYMENT_ID)
AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS'
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, RGM.INVOICE_DISTRIBUTION_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, GJR.REFERENCE_3 TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, RGM.INVOICE_DISTRIBUTION_ID TRX_SOURCE_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
, AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID INVOICE_DISTRIBUTION_ID
FROM JAI_RGM_TRX_RECORDS JRR
WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND JRR.SOURCE = 'AP' ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND AIDA.INVOICE_ID = AI.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'AP_PROCESSING'
AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.INVOICE_DISTRIBUTION_ID)
AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS'
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, AIDA.INVOICE_DISTRIBUTION_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, GJR.REFERENCE_3 TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, AIDA.INVOICE_DISTRIBUTION_ID TRX_SOURCE_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
, AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID INVOICE_DISTRIBUTION_ID
FROM JAI_RGM_TRX_RECORDS JRR
WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND JRR.SOURCE = 'AP' ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND AIDA.INVOICE_ID = AI.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'AP_ACCOUNTING'
AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.INVOICE_DISTRIBUTION_ID)
AND GJR.REFERENCE_6 = 'INVOICE_DISTRIBUTION_ID'
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, AIDA.INVOICE_DISTRIBUTION_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, GJR.REFERENCE_3 TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, AIDA.INVOICE_DISTRIBUTION_ID TRX_SOURCE_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
, AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID INVOICE_DISTRIBUTION_ID
FROM JAI_RGM_TRX_RECORDS JRR
WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND JRR.SOURCE_TRX_TYPE = 'INVOICE_CANCELLATION'
AND JRR.SOURCE = 'AP' ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND AI.INVOICE_ID = AIDA.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'AP_CANCELLATION'
AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.INVOICE_DISTRIBUTION_ID)
AND GJR.REFERENCE_6 = 'INVOICE_DISTRIBUTION_ID'
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, AIDA.INVOICE_DISTRIBUTION_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, GJR.REFERENCE_3 TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, AIDA.INVOICE_DISTRIBUTION_ID TRX_SOURCE_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
, AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID INVOICE_DISTRIBUTION_ID
FROM JAI_RGM_TRX_RECORDS JRR
WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND JRR.SOURCE = 'AP_REVERSAL' ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND AI.INVOICE_ID = AIDA.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'REVERSAL_ACCOUNTING'
AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.INVOICE_DISTRIBUTION_ID)
AND GJR.REFERENCE_6 = 'INVOICE_DISTRIBUTION_ID'
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, APIP.INVOICE_PAYMENT_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, GJR.REFERENCE_3 TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, APIP.INVOICE_PAYMENT_ID TRX_SOURCE_ID
FROM AP_INVOICE_PAYMENTS_ALL APIP
, AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID INVOICE_PAYMENT_ID
FROM JAI_RGM_TRX_RECORDS JRR
WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_PAYMENTS_ALL'
AND JRR.SOURCE = 'AP_CLAIM' ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND APIP.INVOICE_ID = AI.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_PAYMENT_ID = APIP.INVOICE_PAYMENT_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'CLAIM_ACCOUNTING'
AND GJR.REFERENCE_4 = 'AP_INVOICE_PAYMENTS_ALL'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.INVOICE_PAYMENT_ID)
AND GJR.REFERENCE_6 = 'INVOICE_PAYMENT_ID'
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, AIDA.INVOICE_DISTRIBUTION_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, SUBSTR(GJL.DESCRIPTION
, LENGTH('SERVICE TAX ACCOUNTING FOR A') + LENGTH('P. TRANSACTION TYPE:') + 1
, INSTR(GJL.DESCRIPTION
, '. INVOICE NUMBER:') - LENGTH('SERVICE TAX ACCOUNTING FOR A') - LENGTH('P. TRANSACTION TYPE:') - 1) TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, AIDA.INVOICE_DISTRIBUTION_ID TRX_SOURCE_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
, AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID INVOICE_DISTRIBUTION_ID
FROM JAI_RGM_TRX_RECORDS JRR
WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND JRR.SOURCE = 'AP'
AND JRR.SOURCE_TRX_TYPE IN ('REVERSE_APPLY_ACCOUNTING'
, 'REVERSE_UNAPPLY_ACCOUNTING') ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND AI.INVOICE_ID = AIDA.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'REVERSE_CHARGE_ACCOUNTING'
AND GJR.REFERENCE_4 = 'AP_INVOICE_DISTRIBUTIONS_ALL'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.INVOICE_DISTRIBUTION_ID)
AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS'
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, APIP.INVOICE_PAYMENT_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, SUBSTR(GJL.DESCRIPTION
, LENGTH('SERVICE TAX ACCOUNTING FOR A') + LENGTH('P. TRANSACTION TYPE:') + 1
, INSTR(GJL.DESCRIPTION
, '. INVOICE NUMBER:') - LENGTH('SERVICE TAX ACCOUNTING FOR A') - LENGTH('P. TRANSACTION TYPE:') - 1) TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, APIP.INVOICE_PAYMENT_ID TRX_SOURCE_ID
FROM AP_INVOICE_PAYMENTS_ALL APIP
, AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID INVOICE_PAYMENT_ID
FROM JAI_RGM_TRX_RECORDS JRR
WHERE JRR.SOURCE_TABLE_NAME = 'AP_INVOICE_PAYMENTS_ALL'
AND JRR.SOURCE = 'AP'
AND JRR.SOURCE_TRX_TYPE IN ('REVERSE_PAID_ACCOUNTING'
, 'REVERSE_AVOID_ACCOUNTING') ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND APIP.INVOICE_ID = AI.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_PAYMENT_ID = APIP.INVOICE_PAYMENT_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'REVERSE_CHARGE_ACCOUNTING'
AND GJR.REFERENCE_4 = 'AP_INVOICE_PAYMENTS_ALL'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.INVOICE_PAYMENT_ID)
AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS'
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'REPOSITORY REVIEW' EVENT_TYPE
, 'JAI AP REPOREVIEW' TRX_CLASS
, 'JAI AP REPOSITORY REVIEW' TRX_CLASS_NAME
, RGM.SETTLEMENT_ID TRX_HDR_ID
, 'JAI_RGM_TRX_RECORDS' TRX_HDR_TABLE
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, AI.INVOICE_DATE TRANSACTION_DATE
, PV.PARTY_NUMBER PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, HR1.DESCRIPTION LOCATION
, PV.VENDOR_NAME PARTY_NAME
, SUBSTR(GJL.DESCRIPTION
, LENGTH('SERVICE TAX ACCOUNTING FOR A') + LENGTH('P. TRANSACTION TYPE:') + 1
, INSTR(GJL.DESCRIPTION
, '. INVOICE NUMBER:') - LENGTH('SERVICE TAX ACCOUNTING FOR A') - LENGTH('P. TRANSACTION TYPE:') - 1) TRX_SOURCE_TYPE_NAME
, GJR.REFERENCE_4 SOURCE_TABLE
, TO_NUMBER(GJR.REFERENCE_6) TRX_SOURCE_ID
FROM AP_INVOICES_ALL AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HR1
, ( SELECT DISTINCT JRR.INV_ORGANIZATION_ID ORGANIZATION_ID
, JRR.LOCATION_ID LOCATION_ID
, JRR.SOURCE_DOCUMENT_ID SETTLEMENT_ID
, JRF.INVOICE_ID INVOICE_ID
FROM JAI_RGM_TRX_RECORDS JRR
, JAI_RGM_TRX_REFS JRF
WHERE JRR.SOURCE_TABLE_NAME = 'JAI_RGM_SETTLEMENTS'
AND JRR.SOURCE = 'AP'
AND JRR.SOURCE_TRX_TYPE = 'INVOICE_SETTLEMENT'
AND JRR.REFERENCE_ID = JRF.REFERENCE_ID ) RGM
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.PARTY_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND OOD.ORGANIZATION_ID = RGM.ORGANIZATION_ID
AND HR1.LOCATION_ID = RGM.LOCATION_ID
AND RGM.INVOICE_ID = AI.INVOICE_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAIRGMSP'
AND GJR.REFERENCE_4 = 'JAI_RGM_SETTLEMENTS'
AND GJR.REFERENCE_5 = TO_CHAR(RGM.SETTLEMENT_ID)
AND GJR.REFERENCE_6 = TO_CHAR(RGM.INVOICE_ID)
AND GJS.JE_SOURCE_NAME = 'SERVICE TAX INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA'