[Home] [Help]
5400: END IF;
5401: -- End bug fix 5520680
5402:
5403: -- ------------------------------------------------------------
5404: -- 5. Populate FEM_INTG_EXEC_PARAMS_GT base on the balance type
5405: -- ------------------------------------------------------------
5406: IF (pv_ds_balance_type_cd = 'ACTUAL')
5407: THEN
5408: INSERT INTO FEM_INTG_EXEC_PARAMS_GT
5404: -- 5. Populate FEM_INTG_EXEC_PARAMS_GT base on the balance type
5405: -- ------------------------------------------------------------
5406: IF (pv_ds_balance_type_cd = 'ACTUAL')
5407: THEN
5408: INSERT INTO FEM_INTG_EXEC_PARAMS_GT
5409: ( OUTPUT_DATASET_CODE
5410: , EFFECTIVE_PERIOD_NUM
5411: , PERIOD_NAME
5412: , CAL_PERIOD_ID
5437: AND pv_to_period_eff_num;
5438:
5439: ELSIF (pv_ds_balance_type_cd = 'BUDGET')
5440: THEN
5441: INSERT INTO FEM_INTG_EXEC_PARAMS_GT
5442: ( OUTPUT_DATASET_CODE
5443: , BUDGET_ID
5444: , EFFECTIVE_PERIOD_NUM
5445: , PERIOD_NAME
5491: -- Bug 4394404 hkaniven end - to get the latest opened year of each
5492: -- budget
5493:
5494: ELSE -- i.e. Encumbrance Types
5495: INSERT INTO FEM_INTG_EXEC_PARAMS_GT
5496: ( OUTPUT_DATASET_CODE
5497: , ENCUMBRANCE_TYPE_ID
5498: , EFFECTIVE_PERIOD_NUM
5499: , PERIOD_NAME
5548: p_msg_name => 'FEM_GL_POST_216',
5549: p_token1 => 'NUM',
5550: p_value1 => TO_CHAR(pv_num_rows),
5551: p_token2 => 'TABLE',
5552: p_value2 => 'FEM_INTG_EXEC_PARAMS_GT');
5553:
5554: IF (pv_num_rows = 0)
5555: THEN
5556: -- No rows are inserted. It can be invalid budget/encumbrance type setup.
5586: -- periods whose accounting year has been loaded into other datasets.
5587: IF (pv_ds_balance_type_cd = 'ACTUAL')
5588: THEN
5589: -- The error OTHER_DS_LOADED will override other errors codes
5590: UPDATE FEM_INTG_EXEC_PARAMS_GT gt
5591: SET ERROR_CODE = 'OTHER_DS_LOADED'
5592: WHERE EFFECTIVE_PERIOD_NUM IN
5593: (SELECT DISTINCT per.EFFECTIVE_PERIOD_NUM
5594: FROM FEM_DL_DIMENSIONS dl
5614: AND per.LEDGER_ID = dl.LEDGER_ID
5615: AND per.EFFECTIVE_PERIOD_NUM BETWEEN pv_from_period_eff_num
5616: AND pv_to_period_eff_num);
5617:
5618: -- Log the number of rows updated in FEM_INTG_EXEC_PARAMS_GT
5619: FEM_ENGINES_PKG.Tech_Message
5620: (p_severity => pc_log_level_statement,
5621: p_module => v_module,
5622: p_app_name => 'FEM',
5623: p_msg_name => 'FEM_GL_POST_217',
5624: p_token1 => 'NUM',
5625: p_value1 => TO_CHAR(SQL%ROWCOUNT),
5626: p_token2 => 'TABLE',
5627: p_value2 => 'FEM_INTG_EXEC_PARAMS_GT');
5628:
5629: END IF; -- IF (pv_ds_balance_type_cd = 'ACTUAL')
5630:
5631: -- For peirods not mapped to the FEM Cal Periods, update their error codes
5628:
5629: END IF; -- IF (pv_ds_balance_type_cd = 'ACTUAL')
5630:
5631: -- For peirods not mapped to the FEM Cal Periods, update their error codes
5632: UPDATE FEM_INTG_EXEC_PARAMS_GT
5633: SET ERROR_CODE = 'PERIOD_NOT_MAPPED'
5634: WHERE ERROR_CODE IS NULL
5635: AND CAL_PERIOD_ID = -1;
5636:
5637: -- Set the Generate Report Flag to 'Y' i.e. indicating report should be
5638: -- generated when erroring out after this point
5639: x_generate_report_flag := 'Y';
5640:
5641: -- Log the number of rows updated in FEM_INTG_EXEC_PARAMS_GT
5642: FEM_ENGINES_PKG.Tech_Message
5643: (p_severity => pc_log_level_statement,
5644: p_module => v_module,
5645: p_app_name => 'FEM',
5646: p_msg_name => 'FEM_GL_POST_217',
5647: p_token1 => 'NUM',
5648: p_value1 => TO_CHAR(SQL%ROWCOUNT),
5649: p_token2 => 'TABLE',
5650: p_value2 => 'FEM_INTG_EXEC_PARAMS_GT');
5651:
5652: -- Find the min and max valid period effective numbers
5653: SELECT NVL(min(EFFECTIVE_PERIOD_NUM), -1)
5654: , NVL(max(EFFECTIVE_PERIOD_NUM), -1)
5653: SELECT NVL(min(EFFECTIVE_PERIOD_NUM), -1)
5654: , NVL(max(EFFECTIVE_PERIOD_NUM), -1)
5655: INTO pv_min_valid_period_eff_num
5656: , pv_max_valid_period_eff_num
5657: FROM FEM_INTG_EXEC_PARAMS_GT
5658: WHERE ERROR_CODE IS NULL;
5659:
5660: FEM_ENGINES_PKG.Tech_Message
5661: (p_severity => pc_log_level_statement,
5677: p_token2 => 'VAR_VAL',
5678: p_value2 => TO_CHAR(pv_max_valid_period_eff_num));
5679:
5680: -- Bug 4394404 hkaniven start - Get the no of valid rows in
5681: -- FEM_INTG_EXEC_PARAMS_GT if the Balance type is 'Budget'
5682: IF (pv_ds_balance_type_cd = 'BUDGET') THEN
5683: SELECT COUNT(*)
5684: INTO pv_num_rows_valid
5685: FROM FEM_INTG_EXEC_PARAMS_GT
5681: -- FEM_INTG_EXEC_PARAMS_GT if the Balance type is 'Budget'
5682: IF (pv_ds_balance_type_cd = 'BUDGET') THEN
5683: SELECT COUNT(*)
5684: INTO pv_num_rows_valid
5685: FROM FEM_INTG_EXEC_PARAMS_GT
5686: WHERE ERROR_CODE IS NULL;
5687: END IF;
5688: -- Bug 4394404 hkaniven end - Get the no of valid rows in
5689: -- FEM_INTG_EXEC_PARAMS_GT if the Balance type is 'Budget'
5685: FROM FEM_INTG_EXEC_PARAMS_GT
5686: WHERE ERROR_CODE IS NULL;
5687: END IF;
5688: -- Bug 4394404 hkaniven end - Get the no of valid rows in
5689: -- FEM_INTG_EXEC_PARAMS_GT if the Balance type is 'Budget'
5690:
5691: -- Check if there are any valid periods for processing
5692: IF (pv_min_valid_period_eff_num = -1)
5693: THEN
5708: OR pv_max_valid_period_eff_num <> pv_to_period_eff_num)
5709:
5710: -- Bug 4394404 hkaniven start - End the program with a warning
5711: -- if the Balance type is 'Budget' and if any of the rows in
5712: -- FEM_INTG_EXEC_PARAMS_GT is not valid
5713: OR
5714: (pv_ds_balance_type_cd = 'BUDGET'
5715: AND pv_num_rows <> pv_num_rows_valid))
5716: -- Bug 4394404 hkaniven end - End the program with a warning
5714: (pv_ds_balance_type_cd = 'BUDGET'
5715: AND pv_num_rows <> pv_num_rows_valid))
5716: -- Bug 4394404 hkaniven end - End the program with a warning
5717: -- if the Balance type is 'Budget' and if any of the rows in
5718: -- FEM_INTG_EXEC_PARAMS_GT is not valid
5719:
5720: THEN
5721: -- At least one but not all peroid are invalid, so set the return code to
5722: -- 1 i.e. indicating warning
6008: v_return_status VARCHAR2(1);
6009:
6010: CURSOR ds_cur IS
6011: SELECT DISTINCT OUTPUT_DATASET_CODE
6012: FROM FEM_INTG_EXEC_PARAMS_GT
6013: WHERE ERROR_CODE IS NULL;
6014:
6015: -- Bug fix 4285337: Get the end date for each period in order to populate
6016: -- it in FEM_PL_REQUESTS.EFFECTIVE_DATE
6019: , gt.LOAD_METHOD_CODE
6020: , gt.PERIOD_NAME
6021: , gt.EFFECTIVE_PERIOD_NUM
6022: , per.END_DATE
6023: FROM FEM_INTG_EXEC_PARAMS_GT gt
6024: , GL_PERIOD_STATUSES per
6025: WHERE gt.ERROR_CODE IS NULL
6026: AND gt.OUTPUT_DATASET_CODE = p_dataset_code
6027: AND per.PERIOD_NAME = gt.PERIOD_NAME
6030: ORDER BY gt.EFFECTIVE_PERIOD_NUM;
6031:
6032: CURSOR req_cur IS
6033: SELECT REQUEST_ID
6034: FROM FEM_INTG_EXEC_PARAMS_GT
6035: WHERE ERROR_CODE IS NULL
6036: AND REQUEST_ID IS NOT NULL;
6037:
6038: v_first_load_cal_per_id fem_pl_requests.cal_period_id%TYPE;
6047: v_min_valid_eff_per_num gl_period_statuses.effective_period_num%TYPE;
6048: v_max_valid_eff_per_num gl_period_statuses.effective_period_num%TYPE;
6049: v_any_period_gap VARCHAR2(1);
6050:
6051: v_load_method fem_intg_exec_params_gt.load_method_code%TYPE;
6052: v_dummy_flag VARCHAR2(1);
6053: v_req_id NUMBER;
6054:
6055: v_dim_name fem_dimensions_tl.dimension_name%TYPE;
6072: p_token2 => 'TIME',
6073: p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
6074:
6075: -- -------------------------------------------------------
6076: -- Loop for each Output Dataset in FEM_INTG_EXEC_PARAMS_GT
6077: -- -------------------------------------------------------
6078: FOR v_ds IN ds_cur LOOP
6079: -- Log the current Dataset Code
6080: FEM_ENGINES_PKG.Tech_Message
6099: SELECT NVL(min(EFFECTIVE_PERIOD_NUM), -1)
6100: , NVL(max(EFFECTIVE_PERIOD_NUM), -1)
6101: INTO v_min_valid_eff_per_num,
6102: v_max_valid_eff_per_num
6103: FROM FEM_INTG_EXEC_PARAMS_GT
6104: WHERE ERROR_CODE IS NULL
6105: AND OUTPUT_DATASET_CODE = v_ds.output_dataset_code;
6106:
6107: FEM_ENGINES_PKG.Tech_Message
6466: -- No period gap exists between the loaded period range and the valid
6467: -- period range, so start to register the valid periods
6468:
6469: -- ------------------------------------------------------------------
6470: -- Loop for each valid Cal Period in FEM_INTG_EXEC_PARAMS_GT for this
6471: -- Output Dataset
6472: -- ------------------------------------------------------------------
6473: FOR v_cp IN cp_cur(v_ds.output_dataset_code) LOOP
6474: -- Log the current Period Name, Cal Period ID, and Load Method Code
6685:
6686: END IF; -- IF v_msg_count = 1
6687:
6688: -- Update the load method code and error code of
6689: -- FEM_INTG_EXEC_PARAMS_GT for this Output Dataset/Cal Period
6690: UPDATE FEM_INTG_EXEC_PARAMS_GT
6691: SET LOAD_METHOD_CODE = v_load_method
6692: , ERROR_CODE = 'EXEC_LOCK_EXISTS'
6693: WHERE CAL_PERIOD_ID = v_cp.cal_period_id
6686: END IF; -- IF v_msg_count = 1
6687:
6688: -- Update the load method code and error code of
6689: -- FEM_INTG_EXEC_PARAMS_GT for this Output Dataset/Cal Period
6690: UPDATE FEM_INTG_EXEC_PARAMS_GT
6691: SET LOAD_METHOD_CODE = v_load_method
6692: , ERROR_CODE = 'EXEC_LOCK_EXISTS'
6693: WHERE CAL_PERIOD_ID = v_cp.cal_period_id
6694: AND OUTPUT_DATASET_CODE = v_ds.output_dataset_code;
6770: p_value1 => v_ds.output_dataset_code,
6771: p_token2 => 'PER_NAME',
6772: p_value2 => v_cp.PERIOD_NAME);
6773:
6774: UPDATE FEM_INTG_EXEC_PARAMS_GT
6775: SET REQUEST_ID = v_req_id
6776: , LOAD_METHOD_CODE = v_load_method
6777: WHERE CAL_PERIOD_ID = v_cp.cal_period_id
6778: AND OUTPUT_DATASET_CODE = v_ds.output_dataset_code;
6870: p_value1 => v_ds.output_dataset_code);
6871:
6872: -- Populate the error code PERIOD_GAP_EXISTS for all Cal Periods for
6873: -- this Output Dataset
6874: UPDATE FEM_INTG_EXEC_PARAMS_GT
6875: SET ERROR_CODE = 'PERIOD_GAP_EXISTS'
6876: WHERE OUTPUT_DATASET_CODE = v_ds.output_dataset_code
6877: AND ERROR_CODE IS NULL;
6878:
6891:
6892: BEGIN
6893: SELECT DISTINCT LOAD_METHOD_CODE
6894: INTO v_load_method
6895: FROM FEM_INTG_EXEC_PARAMS_GT
6896: WHERE ERROR_CODE IS NULL
6897: AND REQUEST_ID IS NOT NULL;
6898: EXCEPTION
6899: WHEN NO_DATA_FOUND THEN
7233: , CAL_PERIOD_ID
7234: , NUM_OF_ROWS_SELECTED
7235: , NUM_OF_ROWS_POSTED
7236: , ERROR_CODE
7237: FROM FEM_INTG_EXEC_PARAMS_GT
7238: WHERE REQUEST_ID IS NOT NULL
7239: ORDER BY OUTPUT_DATASET_CODE, CAL_PERIOD_ID;
7240:
7241: BEGIN