View: FIIBV_AP_INV_PAYMTS_FCV

Product:	FII - Financial Intelligence
Description:	None. Internal Table.
Implementation/DBA Data:	APPS.FIIBV_AP_INV_PAYMTS_FCV

View Text

SELECT FTP.SEQ_ID SEQ_ID
, AIP.ACCOUNTING_DATE ACCOUNTING_DATE
, AIP.ACCRUAL_POSTED_FLAG ACCRUAL_POSTED_FLAG
, AIP.ASSETS_ADDITION_FLAG ASSETS_ADDITION_FLAG
, AC.BANK_ACCOUNT_ID BANK_ACCOUNT_ID
, GSOB.CURRENCY_CODE BASE_CURRENCY_CODE
, AIP.CASH_JE_BATCH_ID CASH_JE_BATCH_ID
, AIP.CASH_POSTED_FLAG CASH_POSTED_FLAG
, AI.ACCTS_PAY_CODE_COMBINATION_ID CCID
, AC.CHECK_DATE CHECK_DATE
, AIP.CREATION_DATE CREATION_DATE
, NVL(AIP.DISCOUNT_LOST
, 0) / NVL(AI.PAYMENT_CROSS_RATE
, 1) * NVL(AIP.EXCHANGE_RATE
, 1) DISC_AMT_LOST_B
, TO_NUMBER(NULL) DISC_AMT_LOST_G
, NVL(AIP.DISCOUNT_LOST
, 0) DISC_AMT_LOST_T
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(APS.DISCOUNT_DATE - AC.CHECK_DATE)
, 1
, NVL(AIP.INVOICE_BASE_AMOUNT
, AIP.AMOUNT)
, 0)) EARLY_DISC_PAYMENT_AMT_B
, TO_NUMBER(NULL) EARLY_DISC_PAYMENT_AMT_G
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(APS.DISCOUNT_DATE - AC.CHECK_DATE)
, 1
, AIP.AMOUNT
, 0
, AIP.AMOUNT
, 0)) EARLY_DISC_PAYMENT_AMT_T
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(APS.DISCOUNT_DATE - AC.CHECK_DATE)
, 1
, DECODE(SIGN(AIP.AMOUNT)
, -1
, (APS.DISCOUNT_DATE - AC.CHECK_DATE) * -1
, (APS.DISCOUNT_DATE - AC.CHECK_DATE))
, 0)) EARLY_DISC_PAYMENT_DAYS
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(APS.DISCOUNT_DATE - AC.CHECK_DATE)
, 1
, DECODE(SIGN(AIP.AMOUNT)
, -1
, -1
, 1)
, 0)) EARLY_DISC_PAYMENT_COUNT
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(APS.DISCOUNT_DATE - AC.CHECK_DATE)
, 1
, NVL(AIP.INVOICE_BASE_AMOUNT
, AIP.AMOUNT)
, 0)) EARLY_NET_PAYMENT_AMT_B
, TO_NUMBER(NULL) EARLY_NET_PAYMENT_AMT_G
, DECODE(SIGN(APS.DUE_DATE - AC.CHECK_DATE)
, 1
, AIP.AMOUNT
, 0
, AIP.AMOUNT
, 0) EARLY_NET_PAYMENT_AMT_T
, DECODE(SIGN(APS.DUE_DATE - AC.CHECK_DATE)
, 1
, DECODE(SIGN(AIP.AMOUNT)
, -1
, -1
, 1)
, 0) EARLY_NET_PAYMENT_COUNT
, DECODE(SIGN(APS.DUE_DATE - AC.CHECK_DATE)
, 1
, DECODE(SIGN(AIP.AMOUNT)
, -1
, (APS.DUE_DATE - AC.CHECK_DATE) * -1
, (APS.DUE_DATE - AC.CHECK_DATE))
, 0) EARLY_NET_PAYMENT_DAYS
, DECODE(FU.EMPLOYEE_ID
, NULL
, 'NA_EDW'
, TO_CHAR(FU.EMPLOYEE_ID)||'-'||LI.INSTANCE_CODE||'-EMPLOYEE-PERS') EMPLOYEE_FK
, AIP.FUTURE_PAY_POSTED_FLAG FUTURE_PAY_POSTED_FLAG
, 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 INSTANCE
, LI.INSTANCE_CODE INSTANCE_FK
, AI.INVOICE_DATE INVOICE_DATE
, AI.INVOICE_CURRENCY_CODE INV_CURRENCY_CODE
, AI.EXCHANGE_DATE INV_EXCHANGE_DATE
, AI.EXCHANGE_RATE INV_EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE INV_EXCHANGE_RATE_TYPE
, TO_CHAR(AIP.INVOICE_ID)||'-'||TO_CHAR(AIP.ORG_ID) ||'-'||LI.INSTANCE_CODE INV_FK
, AI.INVOICE_NUM INV_NUM
, TO_CHAR(AIP.INVOICE_PAYMENT_ID)||'-'||TO_CHAR(AIP.ORG_ID) ||'-'||LI.INSTANCE_CODE INV_PAYMENT_PK
, AI.PAYMENT_STATUS_FLAG INV_PAYMENT_STATUS_FLAG
, AIP.INVOICE_PAYMENT_TYPE INV_PAYMENT_TYPE
, AI.SOURCE INV_SOURCE
, NVL(AI.SOURCE
, 'NA_EDW') INV_SOURCE_FK
, AI.INVOICE_TYPE_LOOKUP_CODE INV_TYPE
, AIP.JE_BATCH_ID JE_BATCH_ID
, AIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE1
, APS.LAST_UPDATE_DATE LAST_UPDATE_DATE2
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(AC.CHECK_DATE - APS.DISCOUNT_DATE)
, 1
, DECODE(SIGN(AIP.AMOUNT)
, -1
, (AC.CHECK_DATE - APS.DISCOUNT_DATE) * -1
, (AC.CHECK_DATE - APS.DISCOUNT_DATE))
, 0)) LATE_DISC_DAYS
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(AC.CHECK_DATE - APS.DISCOUNT_DATE)
, 1
, NVL(AIP.INVOICE_BASE_AMOUNT
, AIP.AMOUNT)
, 0)) LATE_DISC_PAYMENT_AMT_B
, TO_NUMBER(NULL) LATE_DISC_PAYMENT_AMT_G
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(AC.CHECK_DATE - APS.DISCOUNT_DATE)
, 1
, AIP.AMOUNT
, 0)) LATE_DISC_PAYMENT_AMT_T
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, DECODE(SIGN(AC.CHECK_DATE - APS.DISCOUNT_DATE)
, 1
, DECODE(SIGN(AIP.AMOUNT)
, -1
, -1
, 1)
, 0)) LATE_DISC_PAYMENT_COUNT
, DECODE(SIGN(AC.CHECK_DATE - APS.DUE_DATE)
, 1
, NVL(AIP.INVOICE_BASE_AMOUNT
, AIP.AMOUNT)
, 0) LATE_NET_PAYMENT_AMT_B
, TO_NUMBER(NULL) LATE_NET_PAYMENT_AMT_G
, DECODE(SIGN(AC.CHECK_DATE - APS.DUE_DATE)
, 1
, AIP.AMOUNT
, 0) LATE_NET_PAYMENT_AMT_T
, DECODE(SIGN(AC.CHECK_DATE - APS.DUE_DATE)
, 1
, DECODE(SIGN(AIP.AMOUNT)
, -1
, -1
, 1)
, 0) LATE_NET_PAYMENT_COUNT
, DECODE(SIGN(AC.CHECK_DATE - APS.DUE_DATE)
, 1
, DECODE(SIGN(AIP.AMOUNT)
, -1
, (AC.CHECK_DATE - APS.DUE_DATE) * -1
, (AC.CHECK_DATE - APS.DUE_DATE))
, 0) LATE_NET_PAYMENT_DAYS
, DECODE(AIP.ORG_ID
, NULL
, 'NA_EDW'
, AIP.ORG_ID||'-'||LI.INSTANCE_CODE) ORG_FK
, NVL(AIP.PAYMENT_BASE_AMOUNT
, AIP.AMOUNT) PAYMENT_AMT_B
, TO_NUMBER(NULL) PAYMENT_AMT_G
, AIP.AMOUNT PAYMENT_AMT_T
, 1 PAYMENT_COUNT
, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, NVL(AC.CURRENCY_CODE
, 'NA_EDW') PAYMENT_CURRENCY_FK
, DECODE(AC.CHECK_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(AC.CHECK_DATE
, 'DD-MM-YYYY')||'-'||GSOB.PERIOD_SET_NAME ||'-'||GSOB.ACCOUNTED_PERIOD_TYPE||'-'||LI.INSTANCE_CODE||'-CD') PAYMENT_DATE_FK
, NVL(AIP.DISCOUNT_TAKEN
, 0) / NVL(AI.PAYMENT_CROSS_RATE
, 1) * NVL(AI.EXCHANGE_RATE
, 1) DISC_AMT_TAKEN_B
, TO_NUMBER(NULL) DISC_AMT_TAKEN_G
, NVL(AIP.DISCOUNT_TAKEN
, 0) DISC_AMT_TAKEN_T
, AIP.EXCHANGE_DATE PAYMENT_EXCHANGE_DATE
, AIP.EXCHANGE_RATE PAYMENT_EXCHANGE_RATE
, AIP.EXCHANGE_RATE_TYPE PAYMENT_EXCHANGE_RATE_TYPE
, TO_CHAR(AIP.INVOICE_PAYMENT_ID)||'-'||TO_CHAR(AIP.ORG_ID) ||'-'||LI.INSTANCE_CODE PAYMENT_FK
, DECODE(AI.TERMS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(AI.TERMS_ID)|| '-AP-'||LI.INSTANCE_CODE) PAYMENT_TERM_FK
, AIP.POSTED_FLAG POSTED_FLAG
, NVL(PV.STANDARD_INDUSTRY_CLASS
, 'NA_EDW') SIC_CODE_FK
, 'NA_EDW' DUNS_FK
, DECODE(AIP.SET_OF_BOOKS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(AIP.SET_OF_BOOKS_ID)||'-'||LI.INSTANCE_CODE) SOB_FK
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AI.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE') SUPPLIER_FK
, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, GSOB.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
, NVL( EDW_CURRENCY.GET_RATE ( GSOB.CURRENCY_CODE
, DECODE(FTP.PRIMARY_KEY5
, 1
, AIP.ACCOUNTING_DATE
, AI.INVOICE_DATE)
, NULL)
, -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:JG:JG_AP_INVOICE_PAYMENTS:AIP'
, '_DF:SQLAP:AP_INVOICES:AI'
, '_DF:JG:JG_AP_INVOICES:AI'
, '_DF:SQLAP:AP_CHECKS:AC'
, '_DF:JG:JG_AP_CHECKS:AC'
, '_DF:SQLAP:AP_PAYMENT_SCHEDULES:APS'
, '_DF:JG:JG_AP_PAYMENT_SCHEDULES:APS'
, NVL(AC.BASE_AMOUNT
, AC.AMOUNT) CHECK_AMT_B
, AC.AMOUNT CHECK_AMT_T
, TO_NUMBER(NULL) CHECK_AMT_G
, AC.CHECK_NUMBER CHECK_NUMBER
FROM FII_AP_TMP_IPAY_PK FTP
, AP_INVOICE_PAYMENTS_ALL AIP
, AP_INVOICES_ALL AI
, AP_CHECKS_ALL AC
, AP_PAYMENT_SCHEDULES_ALL APS
, GL_SETS_OF_BOOKS GSOB
, PO_VENDORS PV
, FND_USER FU
, EDW_LOCAL_INSTANCE LI
, EDW_LOCAL_SYSTEM_PARAMETERS LS
, GL_CODE_COMBINATIONS GCC
WHERE FTP.PRIMARY_KEY1 = AIP.INVOICE_PAYMENT_ID
AND AIP.INVOICE_ID = AI.INVOICE_ID
AND AIP.ORG_ID = AI.ORG_ID
AND AIP.INVOICE_ID = APS.INVOICE_ID
AND AIP.PAYMENT_NUM = APS.PAYMENT_NUM
AND AIP.ORG_ID = APS.ORG_ID
AND AIP.CHECK_ID = AC.CHECK_ID
AND AIP.ORG_ID = AC.ORG_ID
AND AC.STATUS_LOOKUP_CODE NOT IN ('OVERFLOW'
, 'SET UP'
, 'UNCONFIRMED SET UP')
AND AIP.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID (+)
AND FU.USER_ID (+) = AIP.CREATED_BY

Columns