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.FIIBV_AP_INV_ON_HOLD_FCV

Source

View Text - Preformatted


SELECT ftp.seq_id, ai.accts_pay_code_combination_id, ai.creation_date, NULL, decode(fu.employee_id, NULL, 'NA_EDW', to_char(fu.employee_id)||'-'||li.instance_code||'-EMPLOYEE-PERS'), ai.exchange_date, ai.exchange_rate, ai.exchange_rate_type, DECODE(ai.vendor_site_id, NULL, 'NA_EDW', ai.vendor_site_id||'-'||ai.org_id||'-'||li.instance_code ||'-SUPPLIER_SITE') GEOGRAPHY_FK, li.instance_code, li.instance_code, NVL(ai.base_amount, ai.invoice_amount), to_number(NULL), ai.invoice_amount, NVL(ai.invoice_currency_code, 'NA_EDW'), DECODE(ai.invoice_date, to_date(NULL), 'NA_EDW', to_char(ai.invoice_date, 'DD-MM-YYYY')||'-'||gsob.period_set_name ||'-'||gsob.accounted_period_type||'-'||li.instance_code||'-CD') INV_DATE_FK, to_char(ai.invoice_id)||'-'||to_char(ai.org_id) ||'-'||li.instance_code, ai.invoice_num, 1, to_char(ai.invoice_id)||'-'||to_char(ai.org_id) ||'-' || li.instance_code, NVL(ai.source, 'NA_EDW'), ai.invoice_type_lookup_code, ai.last_update_date, DECODE(ai.org_id, NULL, 'NA_EDW', ai.org_id||'-'||li.instance_code) ORG_FK, DECODE(ai.terms_id, NULL, 'NA_EDW', to_char(ai.terms_id)||'-AP-'|| li.instance_code), NVL(pv.standard_industry_class, 'NA_EDW'), 'NA_EDW', DECODE(ai.set_of_books_id,NULL, 'NA_EDW', to_char(ai.set_of_books_id)||'-'||li.instance_code), DECODE(ai.vendor_site_id, NULL, 'NA_EDW', ai.vendor_site_id||'-'||ai.org_id||'-'||li.instance_code ||'-SUPPLIER_SITE'), ai.set_of_books_id, gsob.chart_of_accounts_id, NVL( edw_currency.get_rate ( gsob.currency_code, DECODE(ftp.Primary_Key5,1, ai.GL_DATE, ai.invoice_date), ai.exchange_rate_type), -1) GLOBAL_CURRENCY_RATE, 'NA_EDW' GL_ACCT1_FK, 'NA_EDW' GL_ACCT2_FK, 'NA_EDW' GL_ACCT3_FK, 'NA_EDW' GL_ACCT4_FK, 'NA_EDW' GL_ACCT5_FK, 'NA_EDW' GL_ACCT6_FK, 'NA_EDW' GL_ACCT7_FK, 'NA_EDW' GL_ACCT8_FK, 'NA_EDW' GL_ACCT9_FK, 'NA_EDW' GL_ACCT10_FK, '_KF:SQLGL:GL#:gcc', '_DF:SQLAP:AP_INVOICES:ai', '_DF:JG:JG_AP_INVOICES:ai' ,GSOB.currency_code ,ai.GL_DATE ,ai.INVOICE_DATE FROM fii_ap_tmp_invh_pk ftp, ap_invoices_all ai, gl_sets_of_books gsob, po_vendors pv, fnd_user fu, gl_code_combinations gcc, edw_local_instance li, edw_local_system_parameters ls WHERE ftp.primary_key1 = ai.invoice_id AND ai.set_of_books_id = gsob.set_of_books_id AND ai.cancelled_date IS NULL AND ai.vendor_id = pv.vendor_id AND gcc.code_combination_id = ai.accts_pay_code_combination_id AND fu.user_id (+) = ai.created_by AND ((EXISTS (SELECT 'this invoice is on hold' FROM ap_holds_all ah WHERE ai.invoice_id = ah.invoice_id AND ai.org_id = ah.org_id AND ah.hold_lookup_code IS NOT NULL AND ah.release_lookup_code IS NULL)) OR (EXISTS (SELECT 'this invoice has payment schedule hold' FROM ap_payment_schedules_all aps WHERE ai.invoice_id = aps.invoice_id AND ai.org_id = aps.org_id AND NVL(aps.hold_flag, 'N') = 'Y')))

View Text - HTML Formatted

SELECT FTP.SEQ_ID
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, AI.CREATION_DATE
, NULL
, DECODE(FU.EMPLOYEE_ID
, NULL
, 'NA_EDW'
, TO_CHAR(FU.EMPLOYEE_ID)||'-'||LI.INSTANCE_CODE||'-EMPLOYEE-PERS')
, AI.EXCHANGE_DATE
, AI.EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AI.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE') GEOGRAPHY_FK
, LI.INSTANCE_CODE
, LI.INSTANCE_CODE
, NVL(AI.BASE_AMOUNT
, AI.INVOICE_AMOUNT)
, TO_NUMBER(NULL)
, AI.INVOICE_AMOUNT
, NVL(AI.INVOICE_CURRENCY_CODE
, 'NA_EDW')
, DECODE(AI.INVOICE_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(AI.INVOICE_DATE
, 'DD-MM-YYYY')||'-'||GSOB.PERIOD_SET_NAME ||'-'||GSOB.ACCOUNTED_PERIOD_TYPE||'-'||LI.INSTANCE_CODE||'-CD') INV_DATE_FK
, TO_CHAR(AI.INVOICE_ID)||'-'||TO_CHAR(AI.ORG_ID) ||'-'||LI.INSTANCE_CODE
, AI.INVOICE_NUM
, 1
, TO_CHAR(AI.INVOICE_ID)||'-'||TO_CHAR(AI.ORG_ID) ||'-' || LI.INSTANCE_CODE
, NVL(AI.SOURCE
, 'NA_EDW')
, AI.INVOICE_TYPE_LOOKUP_CODE
, AI.LAST_UPDATE_DATE
, DECODE(AI.ORG_ID
, NULL
, 'NA_EDW'
, AI.ORG_ID||'-'||LI.INSTANCE_CODE) ORG_FK
, DECODE(AI.TERMS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(AI.TERMS_ID)||'-AP-'|| LI.INSTANCE_CODE)
, NVL(PV.STANDARD_INDUSTRY_CLASS
, 'NA_EDW')
, 'NA_EDW'
, DECODE(AI.SET_OF_BOOKS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(AI.SET_OF_BOOKS_ID)||'-'||LI.INSTANCE_CODE)
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AI.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE')
, AI.SET_OF_BOOKS_ID
, GSOB.CHART_OF_ACCOUNTS_ID
, NVL( EDW_CURRENCY.GET_RATE ( GSOB.CURRENCY_CODE
, DECODE(FTP.PRIMARY_KEY5
, 1
, AI.GL_DATE
, AI.INVOICE_DATE)
, AI.EXCHANGE_RATE_TYPE)
, -1) GLOBAL_CURRENCY_RATE
, 'NA_EDW' GL_ACCT1_FK
, 'NA_EDW' GL_ACCT2_FK
, 'NA_EDW' GL_ACCT3_FK
, 'NA_EDW' GL_ACCT4_FK
, 'NA_EDW' GL_ACCT5_FK
, 'NA_EDW' GL_ACCT6_FK
, 'NA_EDW' GL_ACCT7_FK
, 'NA_EDW' GL_ACCT8_FK
, 'NA_EDW' GL_ACCT9_FK
, 'NA_EDW' GL_ACCT10_FK
, '_KF:SQLGL:GL#:GCC'
, '_DF:SQLAP:AP_INVOICES:AI'
, '_DF:JG:JG_AP_INVOICES:AI'
, GSOB.CURRENCY_CODE
, AI.GL_DATE
, AI.INVOICE_DATE
FROM FII_AP_TMP_INVH_PK FTP
, AP_INVOICES_ALL AI
, GL_SETS_OF_BOOKS GSOB
, PO_VENDORS PV
, FND_USER FU
, GL_CODE_COMBINATIONS GCC
, EDW_LOCAL_INSTANCE LI
, EDW_LOCAL_SYSTEM_PARAMETERS LS
WHERE FTP.PRIMARY_KEY1 = AI.INVOICE_ID
AND AI.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND AI.CANCELLED_DATE IS NULL
AND AI.VENDOR_ID = PV.VENDOR_ID
AND GCC.CODE_COMBINATION_ID = AI.ACCTS_PAY_CODE_COMBINATION_ID
AND FU.USER_ID (+) = AI.CREATED_BY
AND ((EXISTS (SELECT 'THIS INVOICE IS ON HOLD'
FROM AP_HOLDS_ALL AH
WHERE AI.INVOICE_ID = AH.INVOICE_ID
AND AI.ORG_ID = AH.ORG_ID
AND AH.HOLD_LOOKUP_CODE IS NOT NULL
AND AH.RELEASE_LOOKUP_CODE IS NULL)) OR (EXISTS (SELECT 'THIS INVOICE HAS PAYMENT SCHEDULE HOLD'
FROM AP_PAYMENT_SCHEDULES_ALL APS
WHERE AI.INVOICE_ID = APS.INVOICE_ID
AND AI.ORG_ID = APS.ORG_ID
AND NVL(APS.HOLD_FLAG
, 'N') = 'Y')))