VIEW: APPS.CEFV_CASH_FORECAST_CELLS

Source

View Text - Preformatted

SELECT cl.forecast_cell_id, l1.meaning, ps.start_date, pe.end_date, cl.amount, fr.description, fr.lead_time, sb.name, l6.meaning, fr.bank_account_id, ba.bank_account_name, ba.bank_account_num, '_KF:PAY:BANK:pb', rm.name, cp.name, decode(fr.trx_type,'PAY',pm.org_payment_method_name,fr.payment_method), rl.meaning, fr.pay_group, fr.vendor_type, fr.payment_priority, l5.meaning, ol.description, l2.meaning, fr.roll_forward_period, l3.meaning, l4.meaning, pa.payroll_name, oi.org_information9, fr.code_combination_id, '_KF:SQLGL:GL#:gc', en.encumbrance_type, fr.budget_name, fr.external_source_type, fr.criteria1, fr.criteria2, fr.criteria3, fr.criteria4, fr.criteria5, fr.criteria6, fr.criteria7, fr.criteria8, fr.criteria9, fr.criteria10, fr.criteria11, fr.criteria12, fr.criteria13, fr.criteria14, fr.criteria15, cl.forecast_id, fc.name, l7.meaning, fc.description, cl.creation_date, cl.created_by, cl.last_update_date, cl.last_updated_by from ce_forecast_cells cl, ce_forecast_headers fh, ce_forecasts fc, ce_forecast_rows fr, ce_forecast_columns co, ce_bank_accounts ba, ce_lookups l1, ce_lookups l2, ce_lookups l3, ce_lookups l4, ce_lookups l5, ce_lookups l6, ce_lookups l7, gl_sets_of_books sb, ar_receipt_methods rm, hz_cust_profile_classes cp, pay_org_payment_methods_f pm, ar_lookups rl, po_lookup_codes ol, pay_all_payrolls_f pa, hr_all_organization_units og, hr_organization_information oi, gl_code_combinations gc, gl_encumbrance_types en, pay_external_accounts pb, gl_periods ps, gl_periods pe, gl_periods lp, gl_period_types pt WHERE fh.forecast_header_id = cl.forecast_header_id and fc.forecast_id = cl.forecast_id and fr.forecast_row_id = cl.forecast_row_id and co.forecast_column_id = cl.forecast_column_id and l1.lookup_type = 'FORECAST_TRX_TYPE' and l1.lookup_code = fr.trx_type and sb.set_of_books_id (+) = fr.set_of_books_id and ba.bank_account_id (+) = fr.bank_account_id and rm.receipt_method_id (+) = fr.receipt_method_id and cp.profile_class_id (+) = fr.customer_profile_class_id and pm.org_payment_method_id (+) = fr.org_payment_method_id and rl.lookup_type (+) = 'YES/NO' and rl.lookup_code (+) = fr.include_dispute_flag and ol.lookup_type (+) = 'ASL_STATUS' and ol.lookup_code (+) = fr.authorization_status and l2.lookup_type (+) = 'FORECAST_ROLL_FORWARD_TYPE' and l2.lookup_code (+) = fr.roll_forward_type and l3.lookup_type (+) = 'FORECAST_ORDER_STATUS' and l3.lookup_code (+) = fr.order_status and l4.lookup_type (+) = 'FORECAST_ORDER_DATE_TYPE' and l4.lookup_code (+) = fr.order_date_type and l5.lookup_type (+) = 'FORECAST_DISCOUNT_OPTION' and l5.lookup_code (+) = fr.discount_option and l6.lookup_type (+) = 'FORECAST_METHOD' and l6.lookup_code (+) = fr.forecast_method and l7.lookup_type = 'FORECAST_AGING_TYPE' and l7.lookup_code = fh.aging_type and pa.payroll_id (+) = fr.payroll_id and og.organization_id (+) = pa.business_group_id and oi.organization_id (+) = og.organization_id and oi.org_information_context (+) = 'Business Group Information' and gc.code_combination_id (+) = fr.code_combination_id and en.encumbrance_type_id (+) = fr.encumbrance_type_id and pb.external_account_id (+) = fr.bank_account_id and ps.period_num = decode (greatest(lp.period_num+co.days_from - 1,pt.number_per_fiscal_year), pt.number_per_fiscal_year, lp.period_num + co.days_from -1, mod(lp.period_num + co.days_from - 1,pt.number_per_fiscal_year)) and pe.period_num = decode (greatest(lp.period_num + co.days_to - 1,pt.number_per_fiscal_year), pt.number_per_fiscal_year, lp.period_num + co.days_to -1, mod(lp.period_num + co.days_to- 1,pt.number_per_fiscal_year)) and ps.period_year = decode(mod(lp.period_num + co.days_from - 1,pt.number_per_fiscal_year), 0, lp.period_year + floor((lp.period_num+co.days_from- 1)/pt.number_per_fiscal_year) - 1, lp.period_year+ floor((lp.period_num + co.days_from-1) /pt.number_per_fiscal_year)) and pe.period_year = decode(mod(lp.period_num + co.days_to - 1,pt.number_per_fiscal_year), 0, lp.period_year + floor((lp.period_num + co.days_to-1)/pt.number_per_fiscal_year) - 1, lp.period_year + floor((lp.period_num + co.days_to -1) /pt.number_per_fiscal_year)) and pt.period_type = lp.period_type and ps.period_type = pe.period_type and ps.period_set_name = lp.period_set_name and pe.period_set_name = lp.period_set_name and lp.period_type = ps.period_type and lp.period_set_name = fc.period_set_name and lp.period_name = fc.start_period and co.forecast_header_id = fh.forecast_header_id and fh.forecast_header_id = fc.forecast_header_id and nvl(fh.aging_type,'D') = 'A' union all select cl.forecast_cell_id forecast_cell_id, l1.meaning source_type, fc.start_date + co.days_from - 1 start_period_date, fc.start_date + co.days_to - 1 end_period_date, cl.amount amount, fr.description source_description, fr.lead_time lead_time, sb.name set_of_books_name, l6.meaning forecast_method, fr.bank_account_id bank_account_id, ba.bank_account_name bank_account_name, ba.bank_account_num bank_account_number, '_KF:PAY:BANK:pb' payroll_bank_account, rm.name receipt_method, cp.name customer_profile_class, decode(fr.trx_type, 'PAY',pm.org_payment_method_name, fr.payment_method) payment_method, rl.meaning dispute_option, fr.pay_group pay_group, fr.vendor_type supplier_type, fr.payment_priority payment_priority, l5.meaning discount_option, ol.description po_authorization_status, l2.meaning roll_forward_type, fr.roll_forward_period roll_forward_period, l3.meaning order_status, l4.meaning order_date_type, pa.payroll_name payroll_name, oi.org_information9 payroll_legislation, fr.code_combination_id code_combination_id, '_KF:SQLGL:GL#:gc' gl_account_number, en.encumbrance_type encumbrance_type, fr.budget_name budget_name, fr.external_source_type external_source_type, fr.criteria1 external_source_criteria1, fr.criteria2 external_source_criteria2, fr.criteria3 external_source_criteria3, fr.criteria4 external_source_criteria4, fr.criteria5 external_source_criteria5, fr.criteria6 external_source_criteria6, fr.criteria7 external_source_criteria7, fr.criteria8 external_source_criteria8, fr.criteria9 external_source_criteria9, fr.criteria10 external_source_criteria10, fr.criteria11 external_source_criteria11, fr.criteria12 external_source_criteria12, fr.criteria13 external_source_criteria13, fr.criteria14 external_source_criteria14, fr.criteria15 external_source_criteria15, cl.forecast_id forecast_id, fc.name forecast_name, l7.meaning forecast_type, fc.description forecast_description, cl.creation_date created_on, cl.created_by created_by, cl.last_update_date updated_on, cl.last_updated_by updated_by from ce_forecast_cells cl, ce_forecast_headers fh, ce_forecasts fc, ce_forecast_rows fr, ce_forecast_columns co, ce_bank_accounts ba, ce_lookups l1, ce_lookups l2, ce_lookups l3, ce_lookups l4, ce_lookups l5, ce_lookups l6, ce_lookups l7, gl_sets_of_books sb, ar_receipt_methods rm, hz_cust_profile_classes cp, pay_org_payment_methods_f pm, ar_lookups rl, po_lookup_codes ol, pay_all_payrolls_f pa, hr_all_organization_units og, hr_organization_information oi, gl_code_combinations gc, gl_encumbrance_types en, pay_external_accounts pb where fh.forecast_header_id = cl.forecast_header_id and fc.forecast_id = cl.forecast_id and fr.forecast_row_id = cl.forecast_row_id and co.forecast_column_id = cl.forecast_column_id and l1.lookup_type = 'FORECAST_TRX_TYPE' and l1.lookup_code = fr.trx_type and sb.set_of_books_id (+) = fr.set_of_books_id and ba.bank_account_id (+) = fr.bank_account_id and rm.receipt_method_id (+) = fr.receipt_method_id and cp.profile_class_id (+) = fr.customer_profile_class_id and pm.org_payment_method_id (+) = fr.org_payment_method_id and rl.lookup_type (+) = 'YES/NO' and rl.lookup_code (+) = fr.include_dispute_flag and ol.lookup_type (+) = 'ASL_STATUS' and ol.lookup_code (+) = fr.authorization_status and l2.lookup_type (+) = 'FORECAST_ROLL_FORWARD_TYPE' and l2.lookup_code (+) = fr.roll_forward_type and l3.lookup_type (+) = 'FORECAST_ORDER_STATUS' and l3.lookup_code (+) = fr.order_status and l4.lookup_type (+) = 'FORECAST_ORDER_DATE_TYPE' and l4.lookup_code (+) = fr.order_date_type and l5.lookup_type (+) = 'FORECAST_DISCOUNT_OPTION' and l5.lookup_code (+) = fr.discount_option and l6.lookup_type (+) = 'FORECAST_METHOD' and l6.lookup_code (+) = fr.forecast_method and l7.lookup_type = 'FORECAST_AGING_TYPE' and l7.lookup_code = fh.aging_type and pa.payroll_id (+) = fr.payroll_id and og.organization_id (+) = pa.business_group_id and oi.organization_id (+) = og.organization_id and oi.org_information_context (+) = 'Business Group Information' and gc.code_combination_id (+) = fr.code_combination_id and en.encumbrance_type_id (+) = fr.encumbrance_type_id and pb.external_account_id (+) = fr.bank_account_id and co.forecast_header_id = fh.forecast_header_id and fh.forecast_header_id = fc.forecast_header_id and nvl(fh.aging_type,'D') = 'D' UNION ALL SELECT to_number(null), l1.meaning, ps.start_date, pe.end_date, cl.amount, fr.description, fr.lead_time, sb.name, l6.meaning, fr.bank_account_id, ba.bank_account_name, ba.bank_account_num, '_KF:PAY:BANK:pb', rm.name, cp.name, decode(fr.trx_type,'PAY',pm.org_payment_method_name,fr.payment_method), rl.meaning, fr.pay_group, fr.vendor_type, fr.payment_priority, l5.meaning, ol.description, l2.meaning, fr.roll_forward_period, l3.meaning, l4.meaning, pa.payroll_name, oi.org_information9, fr.code_combination_id, '_KF:SQLGL:GL#:gc', en.encumbrance_type, fr.budget_name, fr.external_source_type, fr.criteria1, fr.criteria2, fr.criteria3, fr.criteria4, fr.criteria5, fr.criteria6, fr.criteria7, fr.criteria8, fr.criteria9, fr.criteria10, fr.criteria11, fr.criteria12, fr.criteria13, fr.criteria14, fr.criteria15, cl.forecast_id, fc.name, l7.meaning, fc.description, to_date(null), to_number(null), to_date(null), to_number(null) from (SELECT FCELL.FORECAST_ID, FCELL.FORECAST_HEADER_ID, FCELL.FORECAST_ROW_ID, FCELL.FORECAST_COLUMN_ID, SUM(FCELL.AMOUNT) amount FROM CE_FORECAST_TRX_CELLS FCELL GROUP BY FCELL.FORECAST_ID, FCELL.FORECAST_HEADER_ID, FCELL.FORECAST_ROW_ID, FCELL.FORECAST_COLUMN_ID) cl, ce_forecast_headers fh, ce_forecasts fc, ce_forecast_rows fr, ce_forecast_columns co, ce_bank_accounts ba, ce_lookups l1, ce_lookups l2, ce_lookups l3, ce_lookups l4, ce_lookups l5, ce_lookups l6, ce_lookups l7, gl_sets_of_books sb, ar_receipt_methods rm, hz_cust_profile_classes cp, pay_org_payment_methods_f pm, ar_lookups rl, po_lookup_codes ol, pay_all_payrolls_f pa, hr_all_organization_units og, hr_organization_information oi, gl_code_combinations gc, gl_encumbrance_types en, pay_external_accounts pb, gl_periods ps, gl_periods pe, gl_periods lp, gl_period_types pt where fh.forecast_header_id = cl.forecast_header_id and fc.forecast_id = cl.forecast_id and fr.forecast_row_id = cl.forecast_row_id and co.forecast_column_id = cl.forecast_column_id and l1.lookup_type = 'FORECAST_TRX_TYPE' and l1.lookup_code = fr.trx_type and sb.set_of_books_id (+) = fr.set_of_books_id and ba.bank_account_id (+) = fr.bank_account_id and rm.receipt_method_id (+) = fr.receipt_method_id and cp.profile_class_id (+) = fr.customer_profile_class_id and pm.org_payment_method_id (+) = fr.org_payment_method_id and rl.lookup_type (+) = 'YES/NO' and rl.lookup_code (+) = fr.include_dispute_flag and ol.lookup_type (+) = 'ASL_STATUS' and ol.lookup_code (+) = fr.authorization_status and l2.lookup_type (+) = 'FORECAST_ROLL_FORWARD_TYPE' and l2.lookup_code (+) = fr.roll_forward_type and l3.lookup_type (+) = 'FORECAST_ORDER_STATUS' and l3.lookup_code (+) = fr.order_status and l4.lookup_type (+) = 'FORECAST_ORDER_DATE_TYPE' and l4.lookup_code (+) = fr.order_date_type and l5.lookup_type (+) = 'FORECAST_DISCOUNT_OPTION' and l5.lookup_code (+) = fr.discount_option and l6.lookup_type (+) = 'FORECAST_METHOD' and l6.lookup_code (+) = fr.forecast_method and l7.lookup_type = 'FORECAST_AGING_TYPE' and l7.lookup_code = fh.aging_type and pa.payroll_id (+) = fr.payroll_id and og.organization_id (+) = pa.business_group_id and oi.organization_id (+) = og.organization_id and oi.org_information_context (+) = 'Business Group Information' and gc.code_combination_id (+) = fr.code_combination_id and en.encumbrance_type_id (+) = fr.encumbrance_type_id and pb.external_account_id (+) = fr.bank_account_id and ps.period_num = decode (greatest(lp.period_num+co.days_from - 1,pt.number_per_fiscal_year), pt.number_per_fiscal_year, lp.period_num + co.days_from -1, mod(lp.period_num + co.days_from - 1,pt.number_per_fiscal_year)) and pe.period_num = decode (greatest(lp.period_num + co.days_to - 1,pt.number_per_fiscal_year), pt.number_per_fiscal_year, lp.period_num + co.days_to -1, mod(lp.period_num + co.days_to- 1,pt.number_per_fiscal_year)) and ps.period_year = decode(mod(lp.period_num + co.days_from - 1,pt.number_per_fiscal_year), 0, lp.period_year + floor((lp.period_num+co.days_from- 1)/pt.number_per_fiscal_year) - 1, lp.period_year+ floor((lp.period_num + co.days_from-1) /pt.number_per_fiscal_year)) and pe.period_year = decode(mod(lp.period_num + co.days_to - 1,pt.number_per_fiscal_year), 0, lp.period_year + floor((lp.period_num + co.days_to-1)/pt.number_per_fiscal_year) - 1, lp.period_year + floor((lp.period_num + co.days_to -1) /pt.number_per_fiscal_year)) and pt.period_type = lp.period_type and ps.period_type = pe.period_type and ps.period_set_name = lp.period_set_name and pe.period_set_name = lp.period_set_name and lp.period_type = ps.period_type and lp.period_set_name = fc.period_set_name and lp.period_name = fc.start_period and co.forecast_header_id = fh.forecast_header_id and fh.forecast_header_id = fc.forecast_header_id and nvl(fh.aging_type,'D') = 'A' union all select to_number(null) forecast_cell_id, l1.meaning source_type, fc.start_date + co.days_from - 1 start_period_date, fc.start_date + co.days_to - 1 end_period_date, cl.amount amount, fr.description source_description, fr.lead_time lead_time, sb.name set_of_books_name, l6.meaning forecast_method, fr.bank_account_id bank_account_id, ba.bank_account_name bank_account_name, ba.bank_account_num bank_account_number, '_KF:PAY:BANK:pb' payroll_bank_account, rm.name receipt_method, cp.name customer_profile_class, decode(fr.trx_type, 'PAY',pm.org_payment_method_name, fr.payment_method) payment_method, rl.meaning dispute_option, fr.pay_group pay_group, fr.vendor_type supplier_type, fr.payment_priority payment_priority, l5.meaning discount_option, ol.description po_authorization_status, l2.meaning roll_forward_type, fr.roll_forward_period roll_forward_period, l3.meaning order_status, l4.meaning order_date_type, pa.payroll_name payroll_name, oi.org_information9 payroll_legislation, fr.code_combination_id code_combination_id, '_KF:SQLGL:GL#:gc' gl_account_number, en.encumbrance_type encumbrance_type, fr.budget_name budget_name, fr.external_source_type external_source_type, fr.criteria1 external_source_criteria1, fr.criteria2 external_source_criteria2, fr.criteria3 external_source_criteria3, fr.criteria4 external_source_criteria4, fr.criteria5 external_source_criteria5, fr.criteria6 external_source_criteria6, fr.criteria7 external_source_criteria7, fr.criteria8 external_source_criteria8, fr.criteria9 external_source_criteria9, fr.criteria10 external_source_criteria10, fr.criteria11 external_source_criteria11, fr.criteria12 external_source_criteria12, fr.criteria13 external_source_criteria13, fr.criteria14 external_source_criteria14, fr.criteria15 external_source_criteria15, cl.forecast_id forecast_id, fc.name forecast_name, l7.meaning forecast_type, fc.description forecast_description, to_date(null) created_on, to_number(null) created_by, to_date(null) updated_on, to_number(null) updated_by from (SELECT FCELL.FORECAST_ID, FCELL.FORECAST_HEADER_ID, FCELL.FORECAST_ROW_ID, FCELL.FORECAST_COLUMN_ID, SUM(FCELL.AMOUNT) amount FROM CE_FORECAST_TRX_CELLS FCELL GROUP BY FCELL.FORECAST_ID, FCELL.FORECAST_HEADER_ID, FCELL.FORECAST_ROW_ID, FCELL.FORECAST_COLUMN_ID) cl, ce_forecast_headers fh, ce_forecasts fc, ce_forecast_rows fr, ce_forecast_columns co, ce_bank_accounts ba, ce_lookups l1, ce_lookups l2, ce_lookups l3, ce_lookups l4, ce_lookups l5, ce_lookups l6, ce_lookups l7, gl_sets_of_books sb, ar_receipt_methods rm, hz_cust_profile_classes cp, pay_org_payment_methods_f pm, ar_lookups rl, po_lookup_codes ol, pay_all_payrolls_f pa, hr_all_organization_units og, hr_organization_information oi, gl_code_combinations gc, gl_encumbrance_types en, pay_external_accounts pb where fh.forecast_header_id = cl.forecast_header_id and fc.forecast_id = cl.forecast_id and fr.forecast_row_id = cl.forecast_row_id and co.forecast_column_id = cl.forecast_column_id and l1.lookup_type = 'FORECAST_TRX_TYPE' and l1.lookup_code = fr.trx_type and sb.set_of_books_id (+) = fr.set_of_books_id and ba.bank_account_id (+) = fr.bank_account_id and rm.receipt_method_id (+) = fr.receipt_method_id and cp.profile_class_id (+) = fr.customer_profile_class_id and pm.org_payment_method_id (+) = fr.org_payment_method_id and rl.lookup_type (+) = 'YES/NO' and rl.lookup_code (+) = fr.include_dispute_flag and ol.lookup_type (+) = 'ASL_STATUS' and ol.lookup_code (+) = fr.authorization_status and l2.lookup_type (+) = 'FORECAST_ROLL_FORWARD_TYPE' and l2.lookup_code (+) = fr.roll_forward_type and l3.lookup_type (+) = 'FORECAST_ORDER_STATUS' and l3.lookup_code (+) = fr.order_status and l4.lookup_type (+) = 'FORECAST_ORDER_DATE_TYPE' and l4.lookup_code (+) = fr.order_date_type and l5.lookup_type (+) = 'FORECAST_DISCOUNT_OPTION' and l5.lookup_code (+) = fr.discount_option and l6.lookup_type (+) = 'FORECAST_METHOD' and l6.lookup_code (+) = fr.forecast_method and l7.lookup_type = 'FORECAST_AGING_TYPE' and l7.lookup_code = fh.aging_type and pa.payroll_id (+) = fr.payroll_id and og.organization_id (+) = pa.business_group_id and oi.organization_id (+) = og.organization_id and oi.org_information_context (+) = 'Business Group Information' and gc.code_combination_id (+) = fr.code_combination_id and en.encumbrance_type_id (+) = fr.encumbrance_type_id and pb.external_account_id (+) = fr.bank_account_id and co.forecast_header_id = fh.forecast_header_id and fh.forecast_header_id = fc.forecast_header_id and nvl(fh.aging_type,'D') = 'D' with read only

View Text - HTML Formatted

SELECT CL.FORECAST_CELL_ID
, L1.MEANING
, PS.START_DATE
, PE.END_DATE
, CL.AMOUNT
, FR.DESCRIPTION
, FR.LEAD_TIME
, SB.NAME
, L6.MEANING
, FR.BANK_ACCOUNT_ID
, BA.BANK_ACCOUNT_NAME
, BA.BANK_ACCOUNT_NUM
, '_KF:PAY:BANK:PB'
, RM.NAME
, CP.NAME
, DECODE(FR.TRX_TYPE
, 'PAY'
, PM.ORG_PAYMENT_METHOD_NAME
, FR.PAYMENT_METHOD)
, RL.MEANING
, FR.PAY_GROUP
, FR.VENDOR_TYPE
, FR.PAYMENT_PRIORITY
, L5.MEANING
, OL.DESCRIPTION
, L2.MEANING
, FR.ROLL_FORWARD_PERIOD
, L3.MEANING
, L4.MEANING
, PA.PAYROLL_NAME
, OI.ORG_INFORMATION9
, FR.CODE_COMBINATION_ID
, '_KF:SQLGL:GL#:GC'
, EN.ENCUMBRANCE_TYPE
, FR.BUDGET_NAME
, FR.EXTERNAL_SOURCE_TYPE
, FR.CRITERIA1
, FR.CRITERIA2
, FR.CRITERIA3
, FR.CRITERIA4
, FR.CRITERIA5
, FR.CRITERIA6
, FR.CRITERIA7
, FR.CRITERIA8
, FR.CRITERIA9
, FR.CRITERIA10
, FR.CRITERIA11
, FR.CRITERIA12
, FR.CRITERIA13
, FR.CRITERIA14
, FR.CRITERIA15
, CL.FORECAST_ID
, FC.NAME
, L7.MEANING
, FC.DESCRIPTION
, CL.CREATION_DATE
, CL.CREATED_BY
, CL.LAST_UPDATE_DATE
, CL.LAST_UPDATED_BY
FROM CE_FORECAST_CELLS CL
, CE_FORECAST_HEADERS FH
, CE_FORECASTS FC
, CE_FORECAST_ROWS FR
, CE_FORECAST_COLUMNS CO
, CE_BANK_ACCOUNTS BA
, CE_LOOKUPS L1
, CE_LOOKUPS L2
, CE_LOOKUPS L3
, CE_LOOKUPS L4
, CE_LOOKUPS L5
, CE_LOOKUPS L6
, CE_LOOKUPS L7
, GL_SETS_OF_BOOKS SB
, AR_RECEIPT_METHODS RM
, HZ_CUST_PROFILE_CLASSES CP
, PAY_ORG_PAYMENT_METHODS_F PM
, AR_LOOKUPS RL
, PO_LOOKUP_CODES OL
, PAY_ALL_PAYROLLS_F PA
, HR_ALL_ORGANIZATION_UNITS OG
, HR_ORGANIZATION_INFORMATION OI
, GL_CODE_COMBINATIONS GC
, GL_ENCUMBRANCE_TYPES EN
, PAY_EXTERNAL_ACCOUNTS PB
, GL_PERIODS PS
, GL_PERIODS PE
, GL_PERIODS LP
, GL_PERIOD_TYPES PT
WHERE FH.FORECAST_HEADER_ID = CL.FORECAST_HEADER_ID
AND FC.FORECAST_ID = CL.FORECAST_ID
AND FR.FORECAST_ROW_ID = CL.FORECAST_ROW_ID
AND CO.FORECAST_COLUMN_ID = CL.FORECAST_COLUMN_ID
AND L1.LOOKUP_TYPE = 'FORECAST_TRX_TYPE'
AND L1.LOOKUP_CODE = FR.TRX_TYPE
AND SB.SET_OF_BOOKS_ID (+) = FR.SET_OF_BOOKS_ID
AND BA.BANK_ACCOUNT_ID (+) = FR.BANK_ACCOUNT_ID
AND RM.RECEIPT_METHOD_ID (+) = FR.RECEIPT_METHOD_ID
AND CP.PROFILE_CLASS_ID (+) = FR.CUSTOMER_PROFILE_CLASS_ID
AND PM.ORG_PAYMENT_METHOD_ID (+) = FR.ORG_PAYMENT_METHOD_ID
AND RL.LOOKUP_TYPE (+) = 'YES/NO'
AND RL.LOOKUP_CODE (+) = FR.INCLUDE_DISPUTE_FLAG
AND OL.LOOKUP_TYPE (+) = 'ASL_STATUS'
AND OL.LOOKUP_CODE (+) = FR.AUTHORIZATION_STATUS
AND L2.LOOKUP_TYPE (+) = 'FORECAST_ROLL_FORWARD_TYPE'
AND L2.LOOKUP_CODE (+) = FR.ROLL_FORWARD_TYPE
AND L3.LOOKUP_TYPE (+) = 'FORECAST_ORDER_STATUS'
AND L3.LOOKUP_CODE (+) = FR.ORDER_STATUS
AND L4.LOOKUP_TYPE (+) = 'FORECAST_ORDER_DATE_TYPE'
AND L4.LOOKUP_CODE (+) = FR.ORDER_DATE_TYPE
AND L5.LOOKUP_TYPE (+) = 'FORECAST_DISCOUNT_OPTION'
AND L5.LOOKUP_CODE (+) = FR.DISCOUNT_OPTION
AND L6.LOOKUP_TYPE (+) = 'FORECAST_METHOD'
AND L6.LOOKUP_CODE (+) = FR.FORECAST_METHOD
AND L7.LOOKUP_TYPE = 'FORECAST_AGING_TYPE'
AND L7.LOOKUP_CODE = FH.AGING_TYPE
AND PA.PAYROLL_ID (+) = FR.PAYROLL_ID
AND OG.ORGANIZATION_ID (+) = PA.BUSINESS_GROUP_ID
AND OI.ORGANIZATION_ID (+) = OG.ORGANIZATION_ID
AND OI.ORG_INFORMATION_CONTEXT (+) = 'BUSINESS GROUP INFORMATION'
AND GC.CODE_COMBINATION_ID (+) = FR.CODE_COMBINATION_ID
AND EN.ENCUMBRANCE_TYPE_ID (+) = FR.ENCUMBRANCE_TYPE_ID
AND PB.EXTERNAL_ACCOUNT_ID (+) = FR.BANK_ACCOUNT_ID
AND PS.PERIOD_NUM = DECODE (GREATEST(LP.PERIOD_NUM+CO.DAYS_FROM - 1
, PT.NUMBER_PER_FISCAL_YEAR)
, PT.NUMBER_PER_FISCAL_YEAR
, LP.PERIOD_NUM + CO.DAYS_FROM -1
, MOD(LP.PERIOD_NUM + CO.DAYS_FROM - 1
, PT.NUMBER_PER_FISCAL_YEAR))
AND PE.PERIOD_NUM = DECODE (GREATEST(LP.PERIOD_NUM + CO.DAYS_TO - 1
, PT.NUMBER_PER_FISCAL_YEAR)
, PT.NUMBER_PER_FISCAL_YEAR
, LP.PERIOD_NUM + CO.DAYS_TO -1
, MOD(LP.PERIOD_NUM + CO.DAYS_TO- 1
, PT.NUMBER_PER_FISCAL_YEAR))
AND PS.PERIOD_YEAR = DECODE(MOD(LP.PERIOD_NUM + CO.DAYS_FROM - 1
, PT.NUMBER_PER_FISCAL_YEAR)
, 0
, LP.PERIOD_YEAR + FLOOR((LP.PERIOD_NUM+CO.DAYS_FROM- 1)/PT.NUMBER_PER_FISCAL_YEAR) - 1
, LP.PERIOD_YEAR+ FLOOR((LP.PERIOD_NUM + CO.DAYS_FROM-1) /PT.NUMBER_PER_FISCAL_YEAR))
AND PE.PERIOD_YEAR = DECODE(MOD(LP.PERIOD_NUM + CO.DAYS_TO - 1
, PT.NUMBER_PER_FISCAL_YEAR)
, 0
, LP.PERIOD_YEAR + FLOOR((LP.PERIOD_NUM + CO.DAYS_TO-1)/PT.NUMBER_PER_FISCAL_YEAR) - 1
, LP.PERIOD_YEAR + FLOOR((LP.PERIOD_NUM + CO.DAYS_TO -1) /PT.NUMBER_PER_FISCAL_YEAR))
AND PT.PERIOD_TYPE = LP.PERIOD_TYPE
AND PS.PERIOD_TYPE = PE.PERIOD_TYPE
AND PS.PERIOD_SET_NAME = LP.PERIOD_SET_NAME
AND PE.PERIOD_SET_NAME = LP.PERIOD_SET_NAME
AND LP.PERIOD_TYPE = PS.PERIOD_TYPE
AND LP.PERIOD_SET_NAME = FC.PERIOD_SET_NAME
AND LP.PERIOD_NAME = FC.START_PERIOD
AND CO.FORECAST_HEADER_ID = FH.FORECAST_HEADER_ID
AND FH.FORECAST_HEADER_ID = FC.FORECAST_HEADER_ID
AND NVL(FH.AGING_TYPE
, 'D') = 'A' UNION ALL SELECT CL.FORECAST_CELL_ID FORECAST_CELL_ID
, L1.MEANING SOURCE_TYPE
, FC.START_DATE + CO.DAYS_FROM - 1 START_PERIOD_DATE
, FC.START_DATE + CO.DAYS_TO - 1 END_PERIOD_DATE
, CL.AMOUNT AMOUNT
, FR.DESCRIPTION SOURCE_DESCRIPTION
, FR.LEAD_TIME LEAD_TIME
, SB.NAME SET_OF_BOOKS_NAME
, L6.MEANING FORECAST_METHOD
, FR.BANK_ACCOUNT_ID BANK_ACCOUNT_ID
, BA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, BA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER
, '_KF:PAY:BANK:PB' PAYROLL_BANK_ACCOUNT
, RM.NAME RECEIPT_METHOD
, CP.NAME CUSTOMER_PROFILE_CLASS
, DECODE(FR.TRX_TYPE
, 'PAY'
, PM.ORG_PAYMENT_METHOD_NAME
, FR.PAYMENT_METHOD) PAYMENT_METHOD
, RL.MEANING DISPUTE_OPTION
, FR.PAY_GROUP PAY_GROUP
, FR.VENDOR_TYPE SUPPLIER_TYPE
, FR.PAYMENT_PRIORITY PAYMENT_PRIORITY
, L5.MEANING DISCOUNT_OPTION
, OL.DESCRIPTION PO_AUTHORIZATION_STATUS
, L2.MEANING ROLL_FORWARD_TYPE
, FR.ROLL_FORWARD_PERIOD ROLL_FORWARD_PERIOD
, L3.MEANING ORDER_STATUS
, L4.MEANING ORDER_DATE_TYPE
, PA.PAYROLL_NAME PAYROLL_NAME
, OI.ORG_INFORMATION9 PAYROLL_LEGISLATION
, FR.CODE_COMBINATION_ID CODE_COMBINATION_ID
, '_KF:SQLGL:GL#:GC' GL_ACCOUNT_NUMBER
, EN.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
, FR.BUDGET_NAME BUDGET_NAME
, FR.EXTERNAL_SOURCE_TYPE EXTERNAL_SOURCE_TYPE
, FR.CRITERIA1 EXTERNAL_SOURCE_CRITERIA1
, FR.CRITERIA2 EXTERNAL_SOURCE_CRITERIA2
, FR.CRITERIA3 EXTERNAL_SOURCE_CRITERIA3
, FR.CRITERIA4 EXTERNAL_SOURCE_CRITERIA4
, FR.CRITERIA5 EXTERNAL_SOURCE_CRITERIA5
, FR.CRITERIA6 EXTERNAL_SOURCE_CRITERIA6
, FR.CRITERIA7 EXTERNAL_SOURCE_CRITERIA7
, FR.CRITERIA8 EXTERNAL_SOURCE_CRITERIA8
, FR.CRITERIA9 EXTERNAL_SOURCE_CRITERIA9
, FR.CRITERIA10 EXTERNAL_SOURCE_CRITERIA10
, FR.CRITERIA11 EXTERNAL_SOURCE_CRITERIA11
, FR.CRITERIA12 EXTERNAL_SOURCE_CRITERIA12
, FR.CRITERIA13 EXTERNAL_SOURCE_CRITERIA13
, FR.CRITERIA14 EXTERNAL_SOURCE_CRITERIA14
, FR.CRITERIA15 EXTERNAL_SOURCE_CRITERIA15
, CL.FORECAST_ID FORECAST_ID
, FC.NAME FORECAST_NAME
, L7.MEANING FORECAST_TYPE
, FC.DESCRIPTION FORECAST_DESCRIPTION
, CL.CREATION_DATE CREATED_ON
, CL.CREATED_BY CREATED_BY
, CL.LAST_UPDATE_DATE UPDATED_ON
, CL.LAST_UPDATED_BY UPDATED_BY
FROM CE_FORECAST_CELLS CL
, CE_FORECAST_HEADERS FH
, CE_FORECASTS FC
, CE_FORECAST_ROWS FR
, CE_FORECAST_COLUMNS CO
, CE_BANK_ACCOUNTS BA
, CE_LOOKUPS L1
, CE_LOOKUPS L2
, CE_LOOKUPS L3
, CE_LOOKUPS L4
, CE_LOOKUPS L5
, CE_LOOKUPS L6
, CE_LOOKUPS L7
, GL_SETS_OF_BOOKS SB
, AR_RECEIPT_METHODS RM
, HZ_CUST_PROFILE_CLASSES CP
, PAY_ORG_PAYMENT_METHODS_F PM
, AR_LOOKUPS RL
, PO_LOOKUP_CODES OL
, PAY_ALL_PAYROLLS_F PA
, HR_ALL_ORGANIZATION_UNITS OG
, HR_ORGANIZATION_INFORMATION OI
, GL_CODE_COMBINATIONS GC
, GL_ENCUMBRANCE_TYPES EN
, PAY_EXTERNAL_ACCOUNTS PB
WHERE FH.FORECAST_HEADER_ID = CL.FORECAST_HEADER_ID
AND FC.FORECAST_ID = CL.FORECAST_ID
AND FR.FORECAST_ROW_ID = CL.FORECAST_ROW_ID
AND CO.FORECAST_COLUMN_ID = CL.FORECAST_COLUMN_ID
AND L1.LOOKUP_TYPE = 'FORECAST_TRX_TYPE'
AND L1.LOOKUP_CODE = FR.TRX_TYPE
AND SB.SET_OF_BOOKS_ID (+) = FR.SET_OF_BOOKS_ID
AND BA.BANK_ACCOUNT_ID (+) = FR.BANK_ACCOUNT_ID
AND RM.RECEIPT_METHOD_ID (+) = FR.RECEIPT_METHOD_ID
AND CP.PROFILE_CLASS_ID (+) = FR.CUSTOMER_PROFILE_CLASS_ID
AND PM.ORG_PAYMENT_METHOD_ID (+) = FR.ORG_PAYMENT_METHOD_ID
AND RL.LOOKUP_TYPE (+) = 'YES/NO'
AND RL.LOOKUP_CODE (+) = FR.INCLUDE_DISPUTE_FLAG
AND OL.LOOKUP_TYPE (+) = 'ASL_STATUS'
AND OL.LOOKUP_CODE (+) = FR.AUTHORIZATION_STATUS
AND L2.LOOKUP_TYPE (+) = 'FORECAST_ROLL_FORWARD_TYPE'
AND L2.LOOKUP_CODE (+) = FR.ROLL_FORWARD_TYPE
AND L3.LOOKUP_TYPE (+) = 'FORECAST_ORDER_STATUS'
AND L3.LOOKUP_CODE (+) = FR.ORDER_STATUS
AND L4.LOOKUP_TYPE (+) = 'FORECAST_ORDER_DATE_TYPE'
AND L4.LOOKUP_CODE (+) = FR.ORDER_DATE_TYPE
AND L5.LOOKUP_TYPE (+) = 'FORECAST_DISCOUNT_OPTION'
AND L5.LOOKUP_CODE (+) = FR.DISCOUNT_OPTION
AND L6.LOOKUP_TYPE (+) = 'FORECAST_METHOD'
AND L6.LOOKUP_CODE (+) = FR.FORECAST_METHOD
AND L7.LOOKUP_TYPE = 'FORECAST_AGING_TYPE'
AND L7.LOOKUP_CODE = FH.AGING_TYPE
AND PA.PAYROLL_ID (+) = FR.PAYROLL_ID
AND OG.ORGANIZATION_ID (+) = PA.BUSINESS_GROUP_ID
AND OI.ORGANIZATION_ID (+) = OG.ORGANIZATION_ID
AND OI.ORG_INFORMATION_CONTEXT (+) = 'BUSINESS GROUP INFORMATION'
AND GC.CODE_COMBINATION_ID (+) = FR.CODE_COMBINATION_ID
AND EN.ENCUMBRANCE_TYPE_ID (+) = FR.ENCUMBRANCE_TYPE_ID
AND PB.EXTERNAL_ACCOUNT_ID (+) = FR.BANK_ACCOUNT_ID
AND CO.FORECAST_HEADER_ID = FH.FORECAST_HEADER_ID
AND FH.FORECAST_HEADER_ID = FC.FORECAST_HEADER_ID
AND NVL(FH.AGING_TYPE
, 'D') = 'D' UNION ALL SELECT TO_NUMBER(NULL)
, L1.MEANING
, PS.START_DATE
, PE.END_DATE
, CL.AMOUNT
, FR.DESCRIPTION
, FR.LEAD_TIME
, SB.NAME
, L6.MEANING
, FR.BANK_ACCOUNT_ID
, BA.BANK_ACCOUNT_NAME
, BA.BANK_ACCOUNT_NUM
, '_KF:PAY:BANK:PB'
, RM.NAME
, CP.NAME
, DECODE(FR.TRX_TYPE
, 'PAY'
, PM.ORG_PAYMENT_METHOD_NAME
, FR.PAYMENT_METHOD)
, RL.MEANING
, FR.PAY_GROUP
, FR.VENDOR_TYPE
, FR.PAYMENT_PRIORITY
, L5.MEANING
, OL.DESCRIPTION
, L2.MEANING
, FR.ROLL_FORWARD_PERIOD
, L3.MEANING
, L4.MEANING
, PA.PAYROLL_NAME
, OI.ORG_INFORMATION9
, FR.CODE_COMBINATION_ID
, '_KF:SQLGL:GL#:GC'
, EN.ENCUMBRANCE_TYPE
, FR.BUDGET_NAME
, FR.EXTERNAL_SOURCE_TYPE
, FR.CRITERIA1
, FR.CRITERIA2
, FR.CRITERIA3
, FR.CRITERIA4
, FR.CRITERIA5
, FR.CRITERIA6
, FR.CRITERIA7
, FR.CRITERIA8
, FR.CRITERIA9
, FR.CRITERIA10
, FR.CRITERIA11
, FR.CRITERIA12
, FR.CRITERIA13
, FR.CRITERIA14
, FR.CRITERIA15
, CL.FORECAST_ID
, FC.NAME
, L7.MEANING
, FC.DESCRIPTION
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
FROM (SELECT FCELL.FORECAST_ID
, FCELL.FORECAST_HEADER_ID
, FCELL.FORECAST_ROW_ID
, FCELL.FORECAST_COLUMN_ID
, SUM(FCELL.AMOUNT) AMOUNT
FROM CE_FORECAST_TRX_CELLS FCELL GROUP BY FCELL.FORECAST_ID
, FCELL.FORECAST_HEADER_ID
, FCELL.FORECAST_ROW_ID
, FCELL.FORECAST_COLUMN_ID) CL
, CE_FORECAST_HEADERS FH
, CE_FORECASTS FC
, CE_FORECAST_ROWS FR
, CE_FORECAST_COLUMNS CO
, CE_BANK_ACCOUNTS BA
, CE_LOOKUPS L1
, CE_LOOKUPS L2
, CE_LOOKUPS L3
, CE_LOOKUPS L4
, CE_LOOKUPS L5
, CE_LOOKUPS L6
, CE_LOOKUPS L7
, GL_SETS_OF_BOOKS SB
, AR_RECEIPT_METHODS RM
, HZ_CUST_PROFILE_CLASSES CP
, PAY_ORG_PAYMENT_METHODS_F PM
, AR_LOOKUPS RL
, PO_LOOKUP_CODES OL
, PAY_ALL_PAYROLLS_F PA
, HR_ALL_ORGANIZATION_UNITS OG
, HR_ORGANIZATION_INFORMATION OI
, GL_CODE_COMBINATIONS GC
, GL_ENCUMBRANCE_TYPES EN
, PAY_EXTERNAL_ACCOUNTS PB
, GL_PERIODS PS
, GL_PERIODS PE
, GL_PERIODS LP
, GL_PERIOD_TYPES PT
WHERE FH.FORECAST_HEADER_ID = CL.FORECAST_HEADER_ID
AND FC.FORECAST_ID = CL.FORECAST_ID
AND FR.FORECAST_ROW_ID = CL.FORECAST_ROW_ID
AND CO.FORECAST_COLUMN_ID = CL.FORECAST_COLUMN_ID
AND L1.LOOKUP_TYPE = 'FORECAST_TRX_TYPE'
AND L1.LOOKUP_CODE = FR.TRX_TYPE
AND SB.SET_OF_BOOKS_ID (+) = FR.SET_OF_BOOKS_ID
AND BA.BANK_ACCOUNT_ID (+) = FR.BANK_ACCOUNT_ID
AND RM.RECEIPT_METHOD_ID (+) = FR.RECEIPT_METHOD_ID
AND CP.PROFILE_CLASS_ID (+) = FR.CUSTOMER_PROFILE_CLASS_ID
AND PM.ORG_PAYMENT_METHOD_ID (+) = FR.ORG_PAYMENT_METHOD_ID
AND RL.LOOKUP_TYPE (+) = 'YES/NO'
AND RL.LOOKUP_CODE (+) = FR.INCLUDE_DISPUTE_FLAG
AND OL.LOOKUP_TYPE (+) = 'ASL_STATUS'
AND OL.LOOKUP_CODE (+) = FR.AUTHORIZATION_STATUS
AND L2.LOOKUP_TYPE (+) = 'FORECAST_ROLL_FORWARD_TYPE'
AND L2.LOOKUP_CODE (+) = FR.ROLL_FORWARD_TYPE
AND L3.LOOKUP_TYPE (+) = 'FORECAST_ORDER_STATUS'
AND L3.LOOKUP_CODE (+) = FR.ORDER_STATUS
AND L4.LOOKUP_TYPE (+) = 'FORECAST_ORDER_DATE_TYPE'
AND L4.LOOKUP_CODE (+) = FR.ORDER_DATE_TYPE
AND L5.LOOKUP_TYPE (+) = 'FORECAST_DISCOUNT_OPTION'
AND L5.LOOKUP_CODE (+) = FR.DISCOUNT_OPTION
AND L6.LOOKUP_TYPE (+) = 'FORECAST_METHOD'
AND L6.LOOKUP_CODE (+) = FR.FORECAST_METHOD
AND L7.LOOKUP_TYPE = 'FORECAST_AGING_TYPE'
AND L7.LOOKUP_CODE = FH.AGING_TYPE
AND PA.PAYROLL_ID (+) = FR.PAYROLL_ID
AND OG.ORGANIZATION_ID (+) = PA.BUSINESS_GROUP_ID
AND OI.ORGANIZATION_ID (+) = OG.ORGANIZATION_ID
AND OI.ORG_INFORMATION_CONTEXT (+) = 'BUSINESS GROUP INFORMATION'
AND GC.CODE_COMBINATION_ID (+) = FR.CODE_COMBINATION_ID
AND EN.ENCUMBRANCE_TYPE_ID (+) = FR.ENCUMBRANCE_TYPE_ID
AND PB.EXTERNAL_ACCOUNT_ID (+) = FR.BANK_ACCOUNT_ID
AND PS.PERIOD_NUM = DECODE (GREATEST(LP.PERIOD_NUM+CO.DAYS_FROM - 1
, PT.NUMBER_PER_FISCAL_YEAR)
, PT.NUMBER_PER_FISCAL_YEAR
, LP.PERIOD_NUM + CO.DAYS_FROM -1
, MOD(LP.PERIOD_NUM + CO.DAYS_FROM - 1
, PT.NUMBER_PER_FISCAL_YEAR))
AND PE.PERIOD_NUM = DECODE (GREATEST(LP.PERIOD_NUM + CO.DAYS_TO - 1
, PT.NUMBER_PER_FISCAL_YEAR)
, PT.NUMBER_PER_FISCAL_YEAR
, LP.PERIOD_NUM + CO.DAYS_TO -1
, MOD(LP.PERIOD_NUM + CO.DAYS_TO- 1
, PT.NUMBER_PER_FISCAL_YEAR))
AND PS.PERIOD_YEAR = DECODE(MOD(LP.PERIOD_NUM + CO.DAYS_FROM - 1
, PT.NUMBER_PER_FISCAL_YEAR)
, 0
, LP.PERIOD_YEAR + FLOOR((LP.PERIOD_NUM+CO.DAYS_FROM- 1)/PT.NUMBER_PER_FISCAL_YEAR) - 1
, LP.PERIOD_YEAR+ FLOOR((LP.PERIOD_NUM + CO.DAYS_FROM-1) /PT.NUMBER_PER_FISCAL_YEAR))
AND PE.PERIOD_YEAR = DECODE(MOD(LP.PERIOD_NUM + CO.DAYS_TO - 1
, PT.NUMBER_PER_FISCAL_YEAR)
, 0
, LP.PERIOD_YEAR + FLOOR((LP.PERIOD_NUM + CO.DAYS_TO-1)/PT.NUMBER_PER_FISCAL_YEAR) - 1
, LP.PERIOD_YEAR + FLOOR((LP.PERIOD_NUM + CO.DAYS_TO -1) /PT.NUMBER_PER_FISCAL_YEAR))
AND PT.PERIOD_TYPE = LP.PERIOD_TYPE
AND PS.PERIOD_TYPE = PE.PERIOD_TYPE
AND PS.PERIOD_SET_NAME = LP.PERIOD_SET_NAME
AND PE.PERIOD_SET_NAME = LP.PERIOD_SET_NAME
AND LP.PERIOD_TYPE = PS.PERIOD_TYPE
AND LP.PERIOD_SET_NAME = FC.PERIOD_SET_NAME
AND LP.PERIOD_NAME = FC.START_PERIOD
AND CO.FORECAST_HEADER_ID = FH.FORECAST_HEADER_ID
AND FH.FORECAST_HEADER_ID = FC.FORECAST_HEADER_ID
AND NVL(FH.AGING_TYPE
, 'D') = 'A' UNION ALL SELECT TO_NUMBER(NULL) FORECAST_CELL_ID
, L1.MEANING SOURCE_TYPE
, FC.START_DATE + CO.DAYS_FROM - 1 START_PERIOD_DATE
, FC.START_DATE + CO.DAYS_TO - 1 END_PERIOD_DATE
, CL.AMOUNT AMOUNT
, FR.DESCRIPTION SOURCE_DESCRIPTION
, FR.LEAD_TIME LEAD_TIME
, SB.NAME SET_OF_BOOKS_NAME
, L6.MEANING FORECAST_METHOD
, FR.BANK_ACCOUNT_ID BANK_ACCOUNT_ID
, BA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, BA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER
, '_KF:PAY:BANK:PB' PAYROLL_BANK_ACCOUNT
, RM.NAME RECEIPT_METHOD
, CP.NAME CUSTOMER_PROFILE_CLASS
, DECODE(FR.TRX_TYPE
, 'PAY'
, PM.ORG_PAYMENT_METHOD_NAME
, FR.PAYMENT_METHOD) PAYMENT_METHOD
, RL.MEANING DISPUTE_OPTION
, FR.PAY_GROUP PAY_GROUP
, FR.VENDOR_TYPE SUPPLIER_TYPE
, FR.PAYMENT_PRIORITY PAYMENT_PRIORITY
, L5.MEANING DISCOUNT_OPTION
, OL.DESCRIPTION PO_AUTHORIZATION_STATUS
, L2.MEANING ROLL_FORWARD_TYPE
, FR.ROLL_FORWARD_PERIOD ROLL_FORWARD_PERIOD
, L3.MEANING ORDER_STATUS
, L4.MEANING ORDER_DATE_TYPE
, PA.PAYROLL_NAME PAYROLL_NAME
, OI.ORG_INFORMATION9 PAYROLL_LEGISLATION
, FR.CODE_COMBINATION_ID CODE_COMBINATION_ID
, '_KF:SQLGL:GL#:GC' GL_ACCOUNT_NUMBER
, EN.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
, FR.BUDGET_NAME BUDGET_NAME
, FR.EXTERNAL_SOURCE_TYPE EXTERNAL_SOURCE_TYPE
, FR.CRITERIA1 EXTERNAL_SOURCE_CRITERIA1
, FR.CRITERIA2 EXTERNAL_SOURCE_CRITERIA2
, FR.CRITERIA3 EXTERNAL_SOURCE_CRITERIA3
, FR.CRITERIA4 EXTERNAL_SOURCE_CRITERIA4
, FR.CRITERIA5 EXTERNAL_SOURCE_CRITERIA5
, FR.CRITERIA6 EXTERNAL_SOURCE_CRITERIA6
, FR.CRITERIA7 EXTERNAL_SOURCE_CRITERIA7
, FR.CRITERIA8 EXTERNAL_SOURCE_CRITERIA8
, FR.CRITERIA9 EXTERNAL_SOURCE_CRITERIA9
, FR.CRITERIA10 EXTERNAL_SOURCE_CRITERIA10
, FR.CRITERIA11 EXTERNAL_SOURCE_CRITERIA11
, FR.CRITERIA12 EXTERNAL_SOURCE_CRITERIA12
, FR.CRITERIA13 EXTERNAL_SOURCE_CRITERIA13
, FR.CRITERIA14 EXTERNAL_SOURCE_CRITERIA14
, FR.CRITERIA15 EXTERNAL_SOURCE_CRITERIA15
, CL.FORECAST_ID FORECAST_ID
, FC.NAME FORECAST_NAME
, L7.MEANING FORECAST_TYPE
, FC.DESCRIPTION FORECAST_DESCRIPTION
, TO_DATE(NULL) CREATED_ON
, TO_NUMBER(NULL) CREATED_BY
, TO_DATE(NULL) UPDATED_ON
, TO_NUMBER(NULL) UPDATED_BY
FROM (SELECT FCELL.FORECAST_ID
, FCELL.FORECAST_HEADER_ID
, FCELL.FORECAST_ROW_ID
, FCELL.FORECAST_COLUMN_ID
, SUM(FCELL.AMOUNT) AMOUNT
FROM CE_FORECAST_TRX_CELLS FCELL GROUP BY FCELL.FORECAST_ID
, FCELL.FORECAST_HEADER_ID
, FCELL.FORECAST_ROW_ID
, FCELL.FORECAST_COLUMN_ID) CL
, CE_FORECAST_HEADERS FH
, CE_FORECASTS FC
, CE_FORECAST_ROWS FR
, CE_FORECAST_COLUMNS CO
, CE_BANK_ACCOUNTS BA
, CE_LOOKUPS L1
, CE_LOOKUPS L2
, CE_LOOKUPS L3
, CE_LOOKUPS L4
, CE_LOOKUPS L5
, CE_LOOKUPS L6
, CE_LOOKUPS L7
, GL_SETS_OF_BOOKS SB
, AR_RECEIPT_METHODS RM
, HZ_CUST_PROFILE_CLASSES CP
, PAY_ORG_PAYMENT_METHODS_F PM
, AR_LOOKUPS RL
, PO_LOOKUP_CODES OL
, PAY_ALL_PAYROLLS_F PA
, HR_ALL_ORGANIZATION_UNITS OG
, HR_ORGANIZATION_INFORMATION OI
, GL_CODE_COMBINATIONS GC
, GL_ENCUMBRANCE_TYPES EN
, PAY_EXTERNAL_ACCOUNTS PB
WHERE FH.FORECAST_HEADER_ID = CL.FORECAST_HEADER_ID
AND FC.FORECAST_ID = CL.FORECAST_ID
AND FR.FORECAST_ROW_ID = CL.FORECAST_ROW_ID
AND CO.FORECAST_COLUMN_ID = CL.FORECAST_COLUMN_ID
AND L1.LOOKUP_TYPE = 'FORECAST_TRX_TYPE'
AND L1.LOOKUP_CODE = FR.TRX_TYPE
AND SB.SET_OF_BOOKS_ID (+) = FR.SET_OF_BOOKS_ID
AND BA.BANK_ACCOUNT_ID (+) = FR.BANK_ACCOUNT_ID
AND RM.RECEIPT_METHOD_ID (+) = FR.RECEIPT_METHOD_ID
AND CP.PROFILE_CLASS_ID (+) = FR.CUSTOMER_PROFILE_CLASS_ID
AND PM.ORG_PAYMENT_METHOD_ID (+) = FR.ORG_PAYMENT_METHOD_ID
AND RL.LOOKUP_TYPE (+) = 'YES/NO'
AND RL.LOOKUP_CODE (+) = FR.INCLUDE_DISPUTE_FLAG
AND OL.LOOKUP_TYPE (+) = 'ASL_STATUS'
AND OL.LOOKUP_CODE (+) = FR.AUTHORIZATION_STATUS
AND L2.LOOKUP_TYPE (+) = 'FORECAST_ROLL_FORWARD_TYPE'
AND L2.LOOKUP_CODE (+) = FR.ROLL_FORWARD_TYPE
AND L3.LOOKUP_TYPE (+) = 'FORECAST_ORDER_STATUS'
AND L3.LOOKUP_CODE (+) = FR.ORDER_STATUS
AND L4.LOOKUP_TYPE (+) = 'FORECAST_ORDER_DATE_TYPE'
AND L4.LOOKUP_CODE (+) = FR.ORDER_DATE_TYPE
AND L5.LOOKUP_TYPE (+) = 'FORECAST_DISCOUNT_OPTION'
AND L5.LOOKUP_CODE (+) = FR.DISCOUNT_OPTION
AND L6.LOOKUP_TYPE (+) = 'FORECAST_METHOD'
AND L6.LOOKUP_CODE (+) = FR.FORECAST_METHOD
AND L7.LOOKUP_TYPE = 'FORECAST_AGING_TYPE'
AND L7.LOOKUP_CODE = FH.AGING_TYPE
AND PA.PAYROLL_ID (+) = FR.PAYROLL_ID
AND OG.ORGANIZATION_ID (+) = PA.BUSINESS_GROUP_ID
AND OI.ORGANIZATION_ID (+) = OG.ORGANIZATION_ID
AND OI.ORG_INFORMATION_CONTEXT (+) = 'BUSINESS GROUP INFORMATION'
AND GC.CODE_COMBINATION_ID (+) = FR.CODE_COMBINATION_ID
AND EN.ENCUMBRANCE_TYPE_ID (+) = FR.ENCUMBRANCE_TYPE_ID
AND PB.EXTERNAL_ACCOUNT_ID (+) = FR.BANK_ACCOUNT_ID
AND CO.FORECAST_HEADER_ID = FH.FORECAST_HEADER_ID
AND FH.FORECAST_HEADER_ID = FC.FORECAST_HEADER_ID
AND NVL(FH.AGING_TYPE
, 'D') = 'D' WITH READ ONLY