View: FIIBV_AP_SCH_PAYMTS_FCV

View Text

SELECT BV.SEQ_ID
, BV.INVOICE_NUM
, BV.INV_TYPE
, BV.PINVOICE_ID
, BV.PAY_NUM
, BV.CCID
, BV.COMPLETELY_PAID_PAYMENT_COUNT
, BV.CREATION_DATE
, BV.DISC_AMT_LOST_B
, BV.DISC_AMT_LOST_G
, BV.DISC_AMT_LOST_T
, BV.DISC_AMT_TAKEN_B
, BV.DISC_AMT_TAKEN_G
, BV.DISC_AMT_TAKEN_T
, BV.DISC_DATE_FK
, BV.DUE_DATE
, BV.DUE_DATE_FK
, BV.EMPLOYEE_FK
, BV.INV_EXCHANGE_DATE
, BV.INV_EXCHANGE_RATE
, BV.INV_EXCHANGE_RATE_TYPE
, BV.PAYMENT_EXCHANGE_RATE
, BV.PAYMENT_EXCHANGE_DATE
, BV.PAYMENT_EXCHANGE_RATE_TYPE
, BV.FIRST_DISC_AVAILABLE_B
, BV.FIRST_DISC_AVAILABLE_G
, BV.FIRST_DISC_AVAILABLE_T
, BV.FIRST_DISC_DATE
, BV.GEOGRAPHY_FK
, BV.HOLD_FLAG
, BV.INSTANCE
, BV.INSTANCE_FK
, BV.INV_AMT_HAVING_DISC_B
, BV.INV_AMT_HAVING_DISC_G
, BV.INV_AMT_HAVING_DISC_T
, BV.INV_AMT_NOT_HAVING_DISC_B
, BV.INV_AMT_NOT_HAVING_DISC_G
, BV.INV_AMT_NOT_HAVING_DISC_T
, BV.INV_CURRENCY_FK
, BV.PAYMENT_CURRENCY_FK
, BV.INV_DATE_FK
, BV.INV_FK
, BV.INV_SOURCE_FK
, BV.LAST_UPDATE_DATE
, BV.LAST_UPDATE_DATE1
, BV.LAST_UPDATE_DATE2
, BV.ORG_FK
, BV.PARTIALLY_PAID_PAYMENT_COUNT
, BV.PAYMENT_AMT_B
, BV.PAYMENT_AMT_G
, BV.PAYMENT_AMT_T
, BV.PAYMENT_METHOD
, BV.PAYMENT_PRIORITY
, BV.PAYMENT_STATUS_FLAG
, BV.PAYMENT_TERM_FK
, BV.POTENTIAL_DISC_AMT_B
, BV.POTENTIAL_DISC_AMT_G
, BV.POTENTIAL_DISC_AMT_T
, BV.REMAINING_DISC_AMT_AT_RISK_B
, BV.REMAINING_DISC_AMT_AT_RISK_G
, BV.REMAINING_DISC_AMT_AT_RISK_T
, BV.REMAINING_INV_AMT_AT_RISK_B
, BV.REMAINING_INV_AMT_AT_RISK_G
, BV.REMAINING_INV_AMT_AT_RISK_T
, BV.SCH_PAYMENT_PK
, BV.SECOND_DISC_AVAILABLE_B
, BV.SECOND_DISC_AVAILABLE_G
, BV.SECOND_DISC_AVAILABLE_T
, BV.SECOND_DISC_DATE
, BV.SIC_CODE_FK
, BV.DUNS_FK
, BV.SOB_FK
, BV.SUPPLIER_FK
, BV.THIRD_DISC_AVAILABLE_B
, BV.THIRD_DISC_AVAILABLE_G
, BV.THIRD_DISC_AVAILABLE_T
, BV.THIRD_DISC_DATE
, BV.UNPAID_PAYMENT_COUNT
, BV.SET_OF_BOOKS_ID
, BV.CHART_OF_ACCOUNTS_ID
, BV.GLOBAL_CURRENCY_RATE
, BV.REMAINING_INV_AMT_B
, BV.REMAINING_INV_AMT_G
, BV.REMAINING_INV_AMT_T
, '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_PAYMENT_SCHEDULES:APS'
, '_DF:JG:JG_AP_PAYMENT_SCHEDULES:APS'
, '_DF:SQLAP:AP_INVOICES:AI'
, '_DF:JG:JG_AP_INVOICES:AI'
, BV.FROM_CURRENCY
, BV.GL_DATE
, BV.INVOICE_DATE
FROM AP_PAYMENT_SCHEDULES_ALL APS
, GL_CODE_COMBINATIONS GCC
, AP_INVOICES_ALL AI
, (SELECT FTP.SEQ_ID SEQ_ID
, AI.INVOICE_NUM INVOICE_NUM
, AI.INVOICE_TYPE_LOOKUP_CODE INV_TYPE
, APS.INVOICE_ID PINVOICE_ID
, APS.PAYMENT_NUM PAY_NUM
, AI.ACCTS_PAY_CODE_COMBINATION_ID CCID
, DECODE(APS.PAYMENT_STATUS_FLAG
, 'Y'
, 1
, 0) COMPLETELY_PAID_PAYMENT_COUNT
, APS.CREATION_DATE CREATION_DATE
, SUM(AIP.DISCOUNT_LOST / NVL(AI.PAYMENT_CROSS_RATE
, 1) * NVL(AIP.EXCHANGE_RATE
, 1)) DISC_AMT_LOST_B
, TO_NUMBER(NULL) DISC_AMT_LOST_G
, SUM(AIP.DISCOUNT_LOST) DISC_AMT_LOST_T
, SUM(AIP.DISCOUNT_TAKEN / NVL(AI.PAYMENT_CROSS_RATE
, 1) * NVL(AIP.EXCHANGE_RATE
, 1)) DISC_AMT_TAKEN_B
, TO_NUMBER(NULL) DISC_AMT_TAKEN_G
, SUM(AIP.DISCOUNT_TAKEN) DISC_AMT_TAKEN_T
, DECODE(APS.DISCOUNT_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(APS.DISCOUNT_DATE
, 'DD-MM-YYYY')||'-'||GSOB.PERIOD_SET_NAME ||'-'||GSOB.ACCOUNTED_PERIOD_TYPE||'-'||LI.INSTANCE_CODE||'-CD') DISC_DATE_FK
, APS.DUE_DATE DUE_DATE
, DECODE(APS.DUE_DATE
, TO_DATE(NULL)
, 'NA_EDW'
, TO_CHAR(APS.DUE_DATE
, 'DD-MM-YYYY')||'-'||GSOB.PERIOD_SET_NAME ||'-'||GSOB.ACCOUNTED_PERIOD_TYPE||'-'||LI.INSTANCE_CODE||'-CD') DUE_DATE_FK
, DECODE(FU.EMPLOYEE_ID
, NULL
, 'NA_EDW'
, TO_CHAR(FU.EMPLOYEE_ID)||'-'||LI.INSTANCE_CODE||'-EMPLOYEE-PERS') EMPLOYEE_FK
, AI.EXCHANGE_DATE INV_EXCHANGE_DATE
, AI.EXCHANGE_RATE INV_EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE INV_EXCHANGE_RATE_TYPE
, APS.PAYMENT_CROSS_RATE PAYMENT_EXCHANGE_RATE
, AI.PAYMENT_CROSS_RATE_DATE PAYMENT_EXCHANGE_DATE
, AI.PAYMENT_CROSS_RATE_TYPE PAYMENT_EXCHANGE_RATE_TYPE
, APS.DISCOUNT_AMOUNT_AVAILABLE* NVL(AI.EXCHANGE_RATE
, 1) FIRST_DISC_AVAILABLE_B
, TO_NUMBER(NULL) FIRST_DISC_AVAILABLE_G
, APS.DISCOUNT_AMOUNT_AVAILABLE FIRST_DISC_AVAILABLE_T
, APS.DISCOUNT_DATE FIRST_DISC_DATE
, DECODE(AI.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, AI.VENDOR_SITE_ID||'-'||AI.ORG_ID||'-'||LI.INSTANCE_CODE ||'-SUPPLIER_SITE') GEOGRAPHY_FK
, APS.HOLD_FLAG HOLD_FLAG
, LI.INSTANCE_CODE INSTANCE
, LI.INSTANCE_CODE INSTANCE_FK
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, APS.GROSS_AMOUNT*NVL(AI.EXCHANGE_RATE
, 1)) INV_AMT_HAVING_DISC_B
, TO_NUMBER(NULL) INV_AMT_HAVING_DISC_G
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, APS.GROSS_AMOUNT) INV_AMT_HAVING_DISC_T
, DECODE(APS.DISCOUNT_DATE
, NULL
, APS.GROSS_AMOUNT*NVL(AI.EXCHANGE_RATE
, 1)
, 0) INV_AMT_NOT_HAVING_DISC_B
, TO_NUMBER(NULL) INV_AMT_NOT_HAVING_DISC_G
, DECODE(APS.DISCOUNT_DATE
, NULL
, APS.GROSS_AMOUNT
, 0) INV_AMT_NOT_HAVING_DISC_T
, NVL(AI.INVOICE_CURRENCY_CODE
, 'NA_EDW') INV_CURRENCY_FK
, NVL(AI.PAYMENT_CURRENCY_CODE
, 'NA_EDW') PAYMENT_CURRENCY_FK
, 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 INV_FK
, NVL(AI.SOURCE
, 'NA_EDW') INV_SOURCE_FK
, APS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AI.LAST_UPDATE_DATE LAST_UPDATE_DATE1
, MAX(AIP.LAST_UPDATE_DATE) LAST_UPDATE_DATE2
, DECODE(APS.ORG_ID
, NULL
, 'NA_EDW'
, AI.ORG_ID||'-'||LI.INSTANCE_CODE) ORG_FK
, DECODE(APS.PAYMENT_STATUS_FLAG
, 'P'
, 1
, 0) PARTIALLY_PAID_PAYMENT_COUNT
, APS.GROSS_AMOUNT * NVL(AI.EXCHANGE_RATE
, 1) PAYMENT_AMT_B
, TO_NUMBER(NULL) PAYMENT_AMT_G
, APS.GROSS_AMOUNT PAYMENT_AMT_T
, APS.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APS.PAYMENT_PRIORITY PAYMENT_PRIORITY
, APS.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG
, DECODE(AI.TERMS_ID
, NULL
, 'NA_EDW'
, (TO_CHAR(AI.TERMS_ID) || '-AP-' || LI.INSTANCE_CODE)) PAYMENT_TERM_FK
, APS.DISCOUNT_AMOUNT_AVAILABLE * NVL(AI.EXCHANGE_RATE
, 1) POTENTIAL_DISC_AMT_B
, TO_NUMBER(NULL) POTENTIAL_DISC_AMT_G
, APS.DISCOUNT_AMOUNT_AVAILABLE POTENTIAL_DISC_AMT_T
, APS.AMOUNT_REMAINING * NVL(AI.EXCHANGE_RATE
, 1) REMAINING_INV_AMT_B
, TO_NUMBER(NULL) REMAINING_INV_AMT_G
, APS.AMOUNT_REMAINING REMAINING_INV_AMT_T
, APS.DISCOUNT_AMOUNT_REMAINING * NVL(AI.EXCHANGE_RATE
, 1) REMAINING_DISC_AMT_AT_RISK_B
, TO_NUMBER(NULL) REMAINING_DISC_AMT_AT_RISK_G
, APS.DISCOUNT_AMOUNT_REMAINING REMAINING_DISC_AMT_AT_RISK_T
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, APS.AMOUNT_REMAINING * NVL(AI.EXCHANGE_RATE
, 1)) REMAINING_INV_AMT_AT_RISK_B
, TO_NUMBER(NULL) REMAINING_INV_AMT_AT_RISK_G
, DECODE(APS.DISCOUNT_DATE
, NULL
, 0
, APS.AMOUNT_REMAINING) REMAINING_INV_AMT_AT_RISK_T
, TO_CHAR(APS.INVOICE_ID) || '-' || TO_CHAR(APS.PAYMENT_NUM) || '-' || TO_CHAR(APS.ORG_ID) || '-'|| LI.INSTANCE_CODE SCH_PAYMENT_PK
, APS.SECOND_DISC_AMT_AVAILABLE * NVL(AI.EXCHANGE_RATE
, 1) SECOND_DISC_AVAILABLE_B
, TO_NUMBER(NULL) SECOND_DISC_AVAILABLE_G
, APS.SECOND_DISC_AMT_AVAILABLE SECOND_DISC_AVAILABLE_T
, APS.SECOND_DISCOUNT_DATE SECOND_DISC_DATE
, NVL(PV.STANDARD_INDUSTRY_CLASS
, 'NA_EDW') SIC_CODE_FK
, 'NA_EDW' DUNS_FK
, DECODE(AI.SET_OF_BOOKS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(AI.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
, APS.THIRD_DISC_AMT_AVAILABLE * NVL(AI.EXCHANGE_RATE
, 1) THIRD_DISC_AVAILABLE_B
, TO_NUMBER(NULL) THIRD_DISC_AVAILABLE_G
, APS.THIRD_DISC_AMT_AVAILABLE THIRD_DISC_AVAILABLE_T
, APS.THIRD_DISCOUNT_DATE THIRD_DISC_DATE
, DECODE(APS.PAYMENT_STATUS_FLAG
, 'N'
, 1
, 0) UNPAID_PAYMENT_COUNT
, 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
, AI.GL_DATE
, AI.INVOICE_DATE)
, NULL)
, -1) GLOBAL_CURRENCY_RATE
, GSOB.CURRENCY_CODE FROM_CURRENCY
, AI.GL_DATE
, AI.INVOICE_DATE FROM FII_AP_TMP_SCHP_PK FTP
, AP_INVOICES_ALL AI
, AP_PAYMENT_SCHEDULES_ALL APS
, AP_INVOICE_PAYMENTS_ALL AIP
, 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 = APS.INVOICE_ID AND FTP.PRIMARY_KEY2 = APS.PAYMENT_NUM AND AI.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID AND AI.INVOICE_ID = APS.INVOICE_ID AND AI.ORG_ID = APS.ORG_ID AND AI.VENDOR_ID = PV.VENDOR_ID AND AI.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND AIP.INVOICE_ID (+) = APS.INVOICE_ID AND AIP.PAYMENT_NUM (+) = APS.PAYMENT_NUM AND AIP.ORG_ID (+) = APS.ORG_ID AND FU.USER_ID (+) = APS.CREATED_BY GROUP BY FTP.SEQ_ID
, FTP.PRIMARY_KEY5
, APS.INVOICE_ID
, AI.INVOICE_TYPE_LOOKUP_CODE
, APS.PAYMENT_NUM
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, APS.PAYMENT_STATUS_FLAG
, APS.CREATION_DATE
, AI.PAYMENT_CURRENCY_CODE
, AI.INVOICE_CURRENCY_CODE
, APS.DUE_DATE
, AI.GL_DATE
, AI.SET_OF_BOOKS_ID
, FU.EMPLOYEE_ID
, AI.EXCHANGE_DATE
, AI.EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE
, APS.PAYMENT_CROSS_RATE
, AI.PAYMENT_CROSS_RATE_DATE
, AI.PAYMENT_CROSS_RATE_TYPE
, APS.DISCOUNT_AMOUNT_AVAILABLE
, APS.DISCOUNT_DATE
, AI.VENDOR_SITE_ID
, AI.ORG_ID
, GSOB.CHART_OF_ACCOUNTS_ID
, APS.HOLD_FLAG
, APS.GROSS_AMOUNT
, AI.INVOICE_DATE
, AI.INVOICE_ID
, AI.ORG_ID
, AI.INVOICE_NUM
, AI.SOURCE
, APS.LAST_UPDATE_DATE
, AI.LAST_UPDATE_DATE
, APS.ORG_ID
, APS.PAYMENT_STATUS_FLAG
, APS.PAYMENT_METHOD_LOOKUP_CODE
, APS.PAYMENT_PRIORITY
, AI.TERMS_ID
, APS.DISCOUNT_AMOUNT_REMAINING
, APS.AMOUNT_REMAINING
, APS.DISCOUNT_DATE
, APS.SECOND_DISC_AMT_AVAILABLE
, APS.SECOND_DISCOUNT_DATE
, PV.STANDARD_INDUSTRY_CLASS
, AI.VENDOR_SITE_ID
, APS.THIRD_DISC_AMT_AVAILABLE
, APS.THIRD_DISCOUNT_DATE
, LI.INSTANCE_CODE
, LS.WAREHOUSE_CURRENCY_CODE
, GSOB.CURRENCY_CODE
, GSOB.PERIOD_SET_NAME
, GSOB.ACCOUNTED_PERIOD_TYPE) BV
WHERE BV.PINVOICE_ID = AI.INVOICE_ID
AND BV.PINVOICE_ID = APS.INVOICE_ID
AND BV.PAY_NUM = APS.PAYMENT_NUM
AND BV.CCID = GCC.CODE_COMBINATION_ID

Columns