VIEW: APPS.FIIBV_AP_HOLD_DATA_FCV

Source

View Text - Preformatted

SELECT ftp.seq_id, ai.accts_pay_code_combination_id, ah.creation_date, NULL, DECODE(ai.vendor_site_id, NULL, 'NA_EDW', ai.vendor_site_id||'-'||ah.org_id||'-'||li.instance_code ||'-SUPPLIER_SITE') GEOGRAPHY_FK, ah.held_by, DECODE(fu.employee_id, NULL, 'NA_EDW', to_char(fu.employee_id)||'-'||li.instance_code||'-EMPLOYEE-PERS'), 1, (ah.rowid || '-' || to_char(ah.invoice_id) || '-' || to_char(ah.line_location_id) || '-' || ah.hold_lookup_code || '-' || to_char(ah.org_id) || '-' || li.instance_code), ah.hold_date, DECODE(ah.hold_date, to_date(NULL), 'NA_EDW', to_char(ah.hold_date, 'DD-MM-YYYY')||'-'||gsob.period_set_name ||'-'||gsob.accounted_period_type||'-'||li.instance_code||'-CD') HOLD_DATE_FK, NVL(ah.hold_lookup_code, 'NA_EDW'), ah.hold_lookup_code, ah.hold_reason, DECODE(ah.release_lookup_code, NULL, 0, 1), DECODE(ah.release_lookup_code, NULL, to_number(NULL), trunc(ah.last_update_date) - trunc(ah.hold_date)), 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'), to_char(ai.invoice_id)||'-'||to_char(ai.org_id)|| '-'|| li.instance_code, ai.invoice_num, ai.source, NVL(ai.source, 'NA_EDW'), ai.invoice_type_lookup_code, ah.last_update_date, ai.last_update_date, DECODE(ah.org_id, NULL, 'NA_EDW', ah.org_id||'-'||li.instance_code), DECODE(ai.terms_id, NULL, 'NA_EDW', to_char(ai.terms_id)||'-'|| 'AP' || '-' ||li.instance_code), NVL(ah.release_lookup_code, 'NA_EDW'), ah.release_lookup_code, ah.release_reason, NVL(pv.standard_industry_class, 'NA_EDW'), '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.vendor_site_id, ai.set_of_books_id, gsob.chart_of_accounts_id, ai.exchange_rate_type, 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_HOLDS:ah', '_DF:SQLAP:AP_INVOICES:ai', '_DF:JG:JG_AP_INVOICES:ai' , GSOB.currency_code , NVL(ai.exchange_date, ai.creation_date) , ai.GL_DATE , ai.INVOICE_DATE FROM FII_AP_TMP_HOLD_PK ftp, ap_invoices_all ai, ap_holds_all ah, 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 = ah.invoice_id AND ai.invoice_id = ah.invoice_id AND ai.cancelled_date IS NULL AND ai.org_id = ah.org_id AND ai.set_of_books_id = gsob.set_of_books_id AND ai.vendor_id = pv.vendor_id AND gcc.code_combination_id = ai.accts_pay_code_combination_id AND fu.user_id (+) = ah.held_by

View Text - HTML Formatted

SELECT FTP.SEQ_ID
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, AH.CREATION_DATE
, NULL
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AH.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE') GEOGRAPHY_FK
, AH.HELD_BY
, DECODE(FU.EMPLOYEE_ID
, NULL
, 'NA_EDW'
, TO_CHAR(FU.EMPLOYEE_ID)||'-'||LI.INSTANCE_CODE||'-EMPLOYEE-PERS')
, 1
, (AH.ROWID || '-' || TO_CHAR(AH.INVOICE_ID) || '-' || TO_CHAR(AH.LINE_LOCATION_ID) || '-' || AH.HOLD_LOOKUP_CODE || '-' || TO_CHAR(AH.ORG_ID) || '-' || LI.INSTANCE_CODE)
, AH.HOLD_DATE
, DECODE(AH.HOLD_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(AH.HOLD_DATE
, 'DD-MM-YYYY')||'-'||GSOB.PERIOD_SET_NAME ||'-'||GSOB.ACCOUNTED_PERIOD_TYPE||'-'||LI.INSTANCE_CODE||'-CD') HOLD_DATE_FK
, NVL(AH.HOLD_LOOKUP_CODE
, 'NA_EDW')
, AH.HOLD_LOOKUP_CODE
, AH.HOLD_REASON
, DECODE(AH.RELEASE_LOOKUP_CODE
, NULL
, 0
, 1)
, DECODE(AH.RELEASE_LOOKUP_CODE
, NULL
, TO_NUMBER(NULL)
, TRUNC(AH.LAST_UPDATE_DATE) - TRUNC(AH.HOLD_DATE))
, 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')
, TO_CHAR(AI.INVOICE_ID)||'-'||TO_CHAR(AI.ORG_ID)|| '-'|| LI.INSTANCE_CODE
, AI.INVOICE_NUM
, AI.SOURCE
, NVL(AI.SOURCE
, 'NA_EDW')
, AI.INVOICE_TYPE_LOOKUP_CODE
, AH.LAST_UPDATE_DATE
, AI.LAST_UPDATE_DATE
, DECODE(AH.ORG_ID
, NULL
, 'NA_EDW'
, AH.ORG_ID||'-'||LI.INSTANCE_CODE)
, DECODE(AI.TERMS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(AI.TERMS_ID)||'-'|| 'AP' || '-' ||LI.INSTANCE_CODE)
, NVL(AH.RELEASE_LOOKUP_CODE
, 'NA_EDW')
, AH.RELEASE_LOOKUP_CODE
, AH.RELEASE_REASON
, NVL(PV.STANDARD_INDUSTRY_CLASS
, 'NA_EDW')
, '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.VENDOR_SITE_ID
, AI.SET_OF_BOOKS_ID
, GSOB.CHART_OF_ACCOUNTS_ID
, AI.EXCHANGE_RATE_TYPE
, 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_HOLDS:AH'
, '_DF:SQLAP:AP_INVOICES:AI'
, '_DF:JG:JG_AP_INVOICES:AI'
, GSOB.CURRENCY_CODE
, NVL(AI.EXCHANGE_DATE
, AI.CREATION_DATE)
, AI.GL_DATE
, AI.INVOICE_DATE
FROM FII_AP_TMP_HOLD_PK FTP
, AP_INVOICES_ALL AI
, AP_HOLDS_ALL AH
, 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 = AH.INVOICE_ID
AND AI.INVOICE_ID = AH.INVOICE_ID
AND AI.CANCELLED_DATE IS NULL
AND AI.ORG_ID = AH.ORG_ID
AND AI.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND AI.VENDOR_ID = PV.VENDOR_ID
AND GCC.CODE_COMBINATION_ID = AI.ACCTS_PAY_CODE_COMBINATION_ID
AND FU.USER_ID (+) = AH.HELD_BY