VIEW: APPS.FIIBV_AP_SCH_PAYMTS_FCV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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