View: FIIBV_AP_INV_ON_HOLD_FCV

View Text

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')))

Columns