VIEW: APPS.FIIBV_AP_INV_PAYMTS_FCV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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