View: FIIBV_AP_HOLD_DATA_FCV

Product:	FII - Financial Intelligence
Description:	FIIBV_AP_HOLD_DATA_FCV is the base view for FII_AP_HOLD_DATA_F fact
Implementation/DBA Data:	APPS.FIIBV_AP_HOLD_DATA_FCV

View Text

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

Columns