[Home] [Help]
853: 'fem.plsql.fem_dim_member_loader_pkg.get_put_messages';
854:
855: BEGIN
856:
857: FEM_ENGINES_PKG.TECH_MESSAGE
858: (p_severity => c_log_level_2,
859: p_module => v_block||'.msg_count',
860: p_msg_text => p_msg_count);
861:
865: THEN
866: FND_MESSAGE.Set_Encoded(v_msg_data);
867: v_message := FND_MESSAGE.Get;
868:
869: FEM_ENGINES_PKG.User_Message(
870: p_msg_text => v_message);
871:
872: FEM_ENGINES_PKG.TECH_MESSAGE
873: (p_severity => c_log_level_2,
868:
869: FEM_ENGINES_PKG.User_Message(
870: p_msg_text => v_message);
871:
872: FEM_ENGINES_PKG.TECH_MESSAGE
873: (p_severity => c_log_level_2,
874: p_module => v_block||'.msg_data',
875: p_msg_text => v_message);
876:
883: p_encoded => c_false,
884: p_data => v_message,
885: p_msg_index_out => v_msg_out);
886:
887: FEM_ENGINES_PKG.User_Message(
888: p_msg_text => v_message);
889:
890: FEM_ENGINES_PKG.TECH_MESSAGE
891: (p_severity => c_log_level_2,
886:
887: FEM_ENGINES_PKG.User_Message(
888: p_msg_text => v_message);
889:
890: FEM_ENGINES_PKG.TECH_MESSAGE
891: (p_severity => c_log_level_2,
892: p_module => v_block||'.msg_data',
893: p_msg_text => v_message);
894:
932: v_param_list wf_parameter_list_t;
933: v_event_sequence NUMBER;
934: c_proc_name VARCHAR2(30) := 'Raise_bus_event';
935: BEGIN
936: FEM_ENGINES_PKG.TECH_MESSAGE
937: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
938:
939:
940: SELECT FEM_DHM_METADATA_OPS_KEY_S.nextval
951: WF_Event.Raise( p_event_name => 'oracle.apps.fem.dhm.dimension.event'
952: ,p_event_key => v_event_sequence
953: ,p_parameters => v_param_list);
954:
955: FEM_ENGINES_PKG.TECH_MESSAGE
956: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
957:
958:
959: END raise_member_bus_event;
1058: WHERE dimension_id = p_dimension_id;
1059:
1060: BEGIN
1061:
1062: FEM_ENGINES_PKG.TECH_MESSAGE
1063: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
1064:
1065: /**************************************************************
1066: RCF 5/31/2005 Bug#3923485 Commenting out date_format_mask validation
1158: ELSE v_dim_label_where_cond := '';
1159: END IF;
1160: END IF;
1161:
1162: FEM_ENGINES_PKG.TECH_MESSAGE
1163: (p_severity => c_log_level_1,
1164: p_module => c_block||'.'||'Engine_Master_Prep',
1165: p_msg_text => 'Check for Source rows');
1166:
1167: v_sql_stmt := 'SELECT count(*) FROM '||x_source_b_table||
1168: ' WHERE status '||x_exec_mode_clause||
1169: v_dim_label_where_cond;
1170:
1171: FEM_ENGINES_PKG.TECH_MESSAGE
1172: (p_severity => c_log_level_1,
1173: p_module => c_block||'.'||'Engine_Master_Prep'||'.source_b',
1174: p_msg_text => v_sql_stmt);
1175: execute immediate v_sql_stmt
1179:
1180: v_sql_stmt := 'SELECT count(*) FROM '||x_source_tl_table||
1181: ' WHERE status '||x_exec_mode_clause||
1182: v_dim_label_where_cond;
1183: FEM_ENGINES_PKG.TECH_MESSAGE
1184: (p_severity => c_log_level_1,
1185: p_module => c_block||'.'||'Engine_Master_Prep'||'.source_tl',
1186: p_msg_text => v_sql_stmt);
1187: execute immediate v_sql_stmt
1191: IF x_simple_dimension_flag ='N' THEN
1192: v_sql_stmt := 'SELECT count(*) FROM '||x_source_attr_table||
1193: ' WHERE status '||x_exec_mode_clause||
1194: v_dim_label_where_cond;
1195: FEM_ENGINES_PKG.TECH_MESSAGE
1196: (p_severity => c_log_level_1,
1197: p_module => c_block||'.'||'Engine_Master_Prep'||'.source_attr',
1198: p_msg_text => v_sql_stmt);
1199: execute immediate v_sql_stmt
1199: execute immediate v_sql_stmt
1200: into v_source_attr_count;
1201: END IF;
1202:
1203: FEM_ENGINES_PKG.TECH_MESSAGE
1204: (c_log_level_1,c_block||'.'||c_proc_name||'.after attr count',null);
1205:
1206: IF x_hier_dimension_flag = 'Y' THEN
1207: v_sql_stmt := 'SELECT count(*) FROM fem_dimension_grps_b_t '||
1207: v_sql_stmt := 'SELECT count(*) FROM fem_dimension_grps_b_t '||
1208: ' WHERE dimension_varchar_label ='||
1209: ''''||p_dimension_varchar_label||''''||
1210: ' AND status '||x_exec_mode_clause;
1211: FEM_ENGINES_PKG.TECH_MESSAGE
1212: (p_severity => c_log_level_1,
1213: p_module => c_block||'.'||'Engine_Master_Prep'||'.dimgrp_b',
1214: p_msg_text => v_sql_stmt);
1215: execute immediate v_sql_stmt
1217: v_sql_stmt := 'SELECT count(*) FROM fem_dimension_grps_tl_t '||
1218: 'WHERE dimension_varchar_label ='||
1219: ''''||p_dimension_varchar_label||''''||
1220: ' AND status '||x_exec_mode_clause;
1221: FEM_ENGINES_PKG.TECH_MESSAGE
1222: (p_severity => c_log_level_1,
1223: p_module => c_block||'.'||'Engine_Master_Prep'||'.dimgrp_tl',
1224: p_msg_text => v_sql_stmt);
1225:
1250: + v_source_dimgrpb_count + v_source_dimgrptl_count;
1251:
1252: x_eng_master_prep_status := 'SUCCESS';
1253:
1254: FEM_ENGINES_PKG.TECH_MESSAGE
1255: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
1256:
1257: EXCEPTION
1258: WHEN e_no_rows_to_load THEN
1261: INTO v_obj_def_display_name
1262: FROM fem_object_definition_vl
1263: WHERE object_definition_id = p_object_definition_id;
1264:
1265: FEM_ENGINES_PKG.TECH_MESSAGE
1266: (p_severity => c_log_level_5
1267: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1268: ,p_app_name => c_fem
1269: ,p_msg_name => G_NO_ROWS_TO_LOAD
1269: ,p_msg_name => G_NO_ROWS_TO_LOAD
1270: ,P_TOKEN1 => 'OPERATION'
1271: ,P_VALUE1 => v_obj_def_display_name);
1272:
1273: FEM_ENGINES_PKG.USER_MESSAGE
1274: (p_app_name => c_fem
1275: ,p_msg_name => G_NO_ROWS_TO_LOAD
1276: ,P_TOKEN1 => 'OPERATION'
1277: ,P_VALUE1 => v_obj_def_display_name);
1279: x_eng_master_prep_status := 'ERROR';
1280:
1281: WHEN e_invalid_date_mask THEN
1282:
1283: FEM_ENGINES_PKG.TECH_MESSAGE
1284: (p_severity => c_log_level_5
1285: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1286: ,p_app_name => c_fem
1287: ,p_msg_name => G_INVALID_DATE_MASK);
1285: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1286: ,p_app_name => c_fem
1287: ,p_msg_name => G_INVALID_DATE_MASK);
1288:
1289: FEM_ENGINES_PKG.USER_MESSAGE
1290: (p_app_name => c_fem
1291: ,p_msg_name => G_INVALID_DATE_MASK);
1292:
1293: x_eng_master_prep_status := 'ERROR';
1294:
1295:
1296: WHEN e_dimension_not_found THEN
1297:
1298: FEM_ENGINES_PKG.TECH_MESSAGE
1299: (p_severity => c_log_level_5
1300: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1301: ,p_app_name => c_fem
1302: ,p_msg_name => G_DIM_NOT_FOUND);
1300: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1301: ,p_app_name => c_fem
1302: ,p_msg_name => G_DIM_NOT_FOUND);
1303:
1304: FEM_ENGINES_PKG.USER_MESSAGE
1305: (p_app_name => c_fem
1306: ,p_msg_name => G_DIM_NOT_FOUND);
1307:
1308: x_eng_master_prep_status := 'ERROR';
1308: x_eng_master_prep_status := 'ERROR';
1309:
1310: WHEN e_invalid_simple_dim THEN
1311:
1312: FEM_ENGINES_PKG.TECH_MESSAGE
1313: (p_severity => c_log_level_5
1314: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1315: ,p_app_name => c_fem
1316: ,p_msg_name => G_INVALID_SIMPLE_DIM);
1314: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1315: ,p_app_name => c_fem
1316: ,p_msg_name => G_INVALID_SIMPLE_DIM);
1317:
1318: FEM_ENGINES_PKG.USER_MESSAGE
1319: (p_app_name => c_fem
1320: ,p_msg_name => G_INVALID_SIMPLE_DIM);
1321:
1322: x_eng_master_prep_status := 'ERROR';
1322: x_eng_master_prep_status := 'ERROR';
1323:
1324: WHEN e_mult_default_version THEN
1325:
1326: FEM_ENGINES_PKG.TECH_MESSAGE
1327: (p_severity => c_log_level_5
1328: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1329: ,p_app_name => c_fem
1330: ,p_msg_name => G_MULT_DEFAULT_VERSION);
1328: ,p_module => c_block||'.'||c_proc_name||'.Exception'
1329: ,p_app_name => c_fem
1330: ,p_msg_name => G_MULT_DEFAULT_VERSION);
1331:
1332: FEM_ENGINES_PKG.USER_MESSAGE
1333: (p_app_name => c_fem
1334: ,p_msg_name => G_MULT_DEFAULT_VERSION
1335: ,P_TOKEN1 => 'ATTRIBUTE_VARCHAR_LABEL'
1336: ,P_VALUE1 => v_temp_attribute);
1394:
1395: BEGIN
1396: x_completion_status := 'SUCCESS';
1397:
1398: FEM_ENGINES_PKG.Tech_Message
1399: (p_severity => c_log_level_2,
1400: p_module => c_block||'.'||'Register_process_execution',
1401: p_msg_text => 'BEGIN');
1402:
1427: X_MSG_COUNT => v_msg_count,
1428: X_MSG_DATA => v_msg_data,
1429: X_RETURN_STATUS => v_API_return_status);
1430:
1431: FEM_ENGINES_PKG.Tech_Message
1432: (p_severity => c_log_level_1,
1433: p_module => c_block||'.'||'Register_request.v_api_return_status',
1434: p_msg_text => v_API_return_status);
1435:
1477: END IF;
1478:
1479: -- Successful completion
1480:
1481: FEM_ENGINES_PKG.Tech_Message
1482: (p_severity => c_log_level_1,
1483: p_module => c_block||'.'||'Register_process_execution',
1484: p_msg_text => 'END');
1485:
1493: p_msg_count => v_msg_count,
1494: p_msg_data => v_msg_data);
1495:
1496: -- display user message
1497: FEM_ENGINES_PKG.USER_MESSAGE
1498: (P_APP_NAME => c_fem
1499: ,P_MSG_NAME => G_PL_REG_REQUEST_ERR);
1500:
1501: FEM_ENGINES_PKG.USER_MESSAGE
1497: FEM_ENGINES_PKG.USER_MESSAGE
1498: (P_APP_NAME => c_fem
1499: ,P_MSG_NAME => G_PL_REG_REQUEST_ERR);
1500:
1501: FEM_ENGINES_PKG.USER_MESSAGE
1502: (P_APP_NAME => c_fem
1503: ,P_MSG_NAME => 'FEM_DIM_MBR_LDR_OBJEXEC');
1504:
1505: x_completion_status := 'ERROR';
1512: -- Get_Put_Messages (
1513: -- p_msg_count => v_msg_count,
1514: -- p_msg_data => v_msg_data);
1515:
1516: --FEM_ENGINES_PKG.USER_MESSAGE
1517: --(P_APP_NAME => c_fem
1518: --,P_MSG_NAME => G_PL_OBJ_EXEC_LOCK_ERR);
1519:
1520: FEM_ENGINES_PKG.USER_MESSAGE
1516: --FEM_ENGINES_PKG.USER_MESSAGE
1517: --(P_APP_NAME => c_fem
1518: --,P_MSG_NAME => G_PL_OBJ_EXEC_LOCK_ERR);
1519:
1520: FEM_ENGINES_PKG.USER_MESSAGE
1521: (P_APP_NAME => c_fem
1522: ,P_MSG_NAME => 'FEM_DIM_MBR_LDR_OBJEXEC');
1523:
1524: FEM_ENGINES_PKG.Tech_Message
1520: FEM_ENGINES_PKG.USER_MESSAGE
1521: (P_APP_NAME => c_fem
1522: ,P_MSG_NAME => 'FEM_DIM_MBR_LDR_OBJEXEC');
1523:
1524: FEM_ENGINES_PKG.Tech_Message
1525: (p_severity => c_log_level_1,
1526: p_module => c_block||'.'||'Register_process_execution',
1527: p_msg_text => 'raising Exec_Lock_failed');
1528:
1670: -------------------
1671: -- Post Messages --
1672: -------------------
1673:
1674: FEM_ENGINES_PKG.USER_MESSAGE
1675: (P_APP_NAME => c_fem
1676: ,P_MSG_NAME => 'FEM_SD_LDR_PROCESS_SUMMARY'
1677: ,P_TOKEN1 => 'LOADNUM'
1678: ,P_VALUE1 => v_rows_loaded
1680: ,P_VALUE2 => p_rows_rejected_accum);
1681:
1682: IF v_execution_status = 'ERROR_RERUN' THEN
1683:
1684: FEM_ENGINES_PKG.USER_MESSAGE
1685: (P_APP_NAME => c_fem
1686: ,P_MSG_NAME => 'FEM_EXEC_RERUN');
1687:
1688: ELSE
1685: (P_APP_NAME => c_fem
1686: ,P_MSG_NAME => 'FEM_EXEC_RERUN');
1687:
1688: ELSE
1689: FEM_ENGINES_PKG.USER_MESSAGE
1690: (P_APP_NAME => c_fem
1691: ,P_MSG_NAME => 'FEM_EXEC_SUCCESS');
1692: END IF;
1693:
1690: (P_APP_NAME => c_fem
1691: ,P_MSG_NAME => 'FEM_EXEC_SUCCESS');
1692: END IF;
1693:
1694: FEM_ENGINES_PKG.TECH_MESSAGE
1695: (p_severity => c_log_level_3,
1696: p_module => c_block||'.'||'Eng_Master_Post_Proc',
1697: p_msg_text => 'End');
1698:
1702: Get_Put_Messages (
1703: p_msg_count => v_msg_count,
1704: p_msg_data => v_msg_data);
1705:
1706: FEM_ENGINES_PKG.TECH_MESSAGE
1707: (p_severity => c_log_level_1,
1708: p_module => c_block||'.'||'Eng_Master_Post_Proc',
1709: p_msg_text => 'Post Process failed');
1710:
1707: (p_severity => c_log_level_1,
1708: p_module => c_block||'.'||'Eng_Master_Post_Proc',
1709: p_msg_text => 'Post Process failed');
1710:
1711: FEM_ENGINES_PKG.USER_MESSAGE
1712: (P_APP_NAME => c_fem
1713: ,P_MSG_NAME => G_EXT_LDR_POST_PROC_ERR);
1714:
1715:
1721:
1722: gv_prg_msg := sqlerrm;
1723: gv_callstack := dbms_utility.format_call_stack;
1724:
1725: FEM_ENGINES_PKG.TECH_MESSAGE
1726: (p_severity => c_log_level_6
1727: ,p_module => c_block||'.'||'Eng_Master_Post_Proc.Unexpected Exception'
1728: ,p_msg_text => gv_prg_msg);
1729:
1726: (p_severity => c_log_level_6
1727: ,p_module => c_block||'.'||'Eng_Master_Post_Proc.Unexpected Exception'
1728: ,p_msg_text => gv_prg_msg);
1729:
1730: FEM_ENGINES_PKG.TECH_MESSAGE
1731: (p_severity => c_log_level_6
1732: ,p_module => c_block||'.'||'Eng_Master_Post_Proc.Unexpected Exception'
1733: ,p_msg_text => gv_callstack);
1734:
1731: (p_severity => c_log_level_6
1732: ,p_module => c_block||'.'||'Eng_Master_Post_Proc.Unexpected Exception'
1733: ,p_msg_text => gv_callstack);
1734:
1735: FEM_ENGINES_PKG.USER_MESSAGE
1736: (p_app_name => c_fem
1737: ,p_msg_text => gv_prg_msg);
1738:
1739: FEM_ENGINES_PKG.USER_MESSAGE
1735: FEM_ENGINES_PKG.USER_MESSAGE
1736: (p_app_name => c_fem
1737: ,p_msg_text => gv_prg_msg);
1738:
1739: FEM_ENGINES_PKG.USER_MESSAGE
1740: (P_APP_NAME => c_fem
1741: ,P_MSG_NAME => G_EXT_LDR_POST_PROC_ERR);
1742:
1743:
1855: ---------------------
1856: cv_get_bad_mbr cv_curs;
1857:
1858: BEGIN
1859: FEM_ENGINES_PKG.TECH_MESSAGE
1860: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
1861:
1862:
1863: --x_status := 0; -- initialize status of the Pre_Validation procedure
1888: IF v_fetch_limit IS NULL THEN
1889: v_fetch_limit := 10000;
1890: END IF;
1891:
1892: FEM_ENGINES_PKG.TECH_MESSAGE
1893: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_new_mbrs_select_stmt',x_bad_mbr_select_stmt);
1894:
1895: build_status_update_stmt (p_source_b_table
1896: ,x_update_mbr_status_stmt);
1894:
1895: build_status_update_stmt (p_source_b_table
1896: ,x_update_mbr_status_stmt);
1897:
1898: FEM_ENGINES_PKG.TECH_MESSAGE
1899: (c_log_level_1,c_block||'.'||c_proc_name||'.update_mbr_status_stmt '
1900: ,x_update_mbr_status_stmt);
1901:
1902: v_loop_counter := 0; -- used for DIMENSION_GROUP loads to identify when to exit
1915: x_part_name => v_part_name,
1916: p_req_id => p_master_request_id,
1917: p_proc_num => p_proc_num);
1918:
1919: FEM_ENGINES_PKG.TECH_MESSAGE
1920: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
1921: ,v_slc_val);
1922: FEM_ENGINES_PKG.TECH_MESSAGE
1923: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
1918:
1919: FEM_ENGINES_PKG.TECH_MESSAGE
1920: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
1921: ,v_slc_val);
1922: FEM_ENGINES_PKG.TECH_MESSAGE
1923: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
1924: ,v_slc_val2);
1925: FEM_ENGINES_PKG.TECH_MESSAGE
1926: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
1921: ,v_slc_val);
1922: FEM_ENGINES_PKG.TECH_MESSAGE
1923: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
1924: ,v_slc_val2);
1925: FEM_ENGINES_PKG.TECH_MESSAGE
1926: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
1927: ,v_slc_val3);
1928: FEM_ENGINES_PKG.TECH_MESSAGE
1929: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
1924: ,v_slc_val2);
1925: FEM_ENGINES_PKG.TECH_MESSAGE
1926: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
1927: ,v_slc_val3);
1928: FEM_ENGINES_PKG.TECH_MESSAGE
1929: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
1930: ,v_slc_val4);
1931:
1932:
1960: ----------------------------------------------
1961:
1962: v_mbr_last_row := t_rowid.LAST;
1963:
1964: FEM_ENGINES_PKG.TECH_MESSAGE
1965: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mbr_last_row'
1966: ,v_mbr_last_row);
1967:
1968: IF (v_mbr_last_row IS NULL)
1984: ----------------------------------------------------------
1985: -- Count the error rows
1986: ----------------------------------------------------------
1987: v_rows_rejected := v_rows_rejected + v_mbr_last_row;
1988: FEM_ENGINES_PKG.TECH_MESSAGE
1989: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_rejected'
1990: ,v_rows_rejected);
1991:
1992: ----------------------------------------------------------
2017: IF cv_get_bad_mbr%ISOPEN THEN
2018: CLOSE cv_get_bad_mbr;
2019: END IF;
2020: --x_rows_rejected := v_rows_rejected;
2021: FEM_ENGINES_PKG.TECH_MESSAGE
2022: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
2023:
2024: EXCEPTION
2025: WHEN OTHERS THEN
2031:
2032: gv_prg_msg := sqlerrm;
2033: gv_callstack := dbms_utility.format_call_stack;
2034:
2035: FEM_ENGINES_PKG.TECH_MESSAGE
2036: (p_severity => c_log_level_6
2037: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
2038: ,p_msg_text => gv_prg_msg);
2039:
2036: (p_severity => c_log_level_6
2037: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
2038: ,p_msg_text => gv_prg_msg);
2039:
2040: FEM_ENGINES_PKG.TECH_MESSAGE
2041: (p_severity => c_log_level_6
2042: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
2043: ,p_msg_text => gv_callstack);
2044:
2041: (p_severity => c_log_level_6
2042: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
2043: ,p_msg_text => gv_callstack);
2044:
2045: FEM_ENGINES_PKG.USER_MESSAGE
2046: (p_app_name => c_fem
2047: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
2048: ,P_TOKEN1 => 'ERR_MSG'
2049: ,P_VALUE1 => gv_prg_msg);
2171: cv_get_bad_attr cv_curs;
2172: cv_get_bad_attr_vers cv_curs;
2173:
2174: BEGIN
2175: FEM_ENGINES_PKG.TECH_MESSAGE
2176: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
2177:
2178: --DBMS_SESSION.SET_SQL_TRACE (sql_trace => TRUE);
2179: --x_status := 0; -- initialize status of the Pre_Validation procedure
2190: -----------------------------------------------------------------------
2191: -- Update rows in the _ATTR_T table where the attribute_varchar_label
2192: -- doesn't exist
2193: ----------------------------------------------------------------------------
2194: FEM_ENGINES_PKG.TECH_MESSAGE
2195: (c_log_level_3,c_block||'.'||c_proc_name
2196: ,'Invalid ATTRIBUTE_VARCHAR_LABEL');
2197:
2198:
2207: ELSE
2208: x_bad_attrlab_status_stmt := REPLACE(x_bad_attrlab_status_stmt,'{{data_slice}}','1=1');
2209: END IF;
2210:
2211: FEM_ENGINES_PKG.TECH_MESSAGE
2212: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_attrlab_status_stmt',x_bad_attrlab_status_stmt);
2213:
2214:
2215: build_not_user_label_upd_stmt (p_dimension_varchar_label
2223: ELSE
2224: x_not_user_label_status_stmt := REPLACE(x_not_user_label_status_stmt,'{{data_slice}}','1=1');
2225: END IF;
2226:
2227: FEM_ENGINES_PKG.TECH_MESSAGE
2228: (c_log_level_1,c_block||'.'||c_proc_name||'.not_user_label_status_stmt',x_not_user_label_status_stmt);
2229:
2230:
2231:
2228: (c_log_level_1,c_block||'.'||c_proc_name||'.not_user_label_status_stmt',x_not_user_label_status_stmt);
2229:
2230:
2231:
2232: FEM_ENGINES_PKG.TECH_MESSAGE
2233: (c_log_level_3,c_block||'.'||c_proc_name
2234: ,'Attribute assignments for Invalid member');
2235:
2236: build_status_update_stmt (p_source_attr_table
2288: x_bad_attr_vers_select_stmt := REPLACE(x_bad_attr_vers_select_stmt,'{{data_slice}}','1=1');
2289: END IF;
2290:
2291:
2292: FEM_ENGINES_PKG.TECH_MESSAGE
2293: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_vers_select_stmt',x_bad_attr_vers_select_stmt);
2294:
2295: FEM_ENGINES_PKG.TECH_MESSAGE
2296: (c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_status_stmt '
2291:
2292: FEM_ENGINES_PKG.TECH_MESSAGE
2293: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_vers_select_stmt',x_bad_attr_vers_select_stmt);
2294:
2295: FEM_ENGINES_PKG.TECH_MESSAGE
2296: (c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_status_stmt '
2297: ,x_update_attr_status_stmt);
2298:
2299: FEM_ENGINES_PKG.TECH_MESSAGE
2295: FEM_ENGINES_PKG.TECH_MESSAGE
2296: (c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_status_stmt '
2297: ,x_update_attr_status_stmt);
2298:
2299: FEM_ENGINES_PKG.TECH_MESSAGE
2300: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_select_stmt'
2301: ,x_bad_attr_select_stmt);
2302:
2303: FEM_ENGINES_PKG.TECH_MESSAGE
2299: FEM_ENGINES_PKG.TECH_MESSAGE
2300: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_select_stmt'
2301: ,x_bad_attr_select_stmt);
2302:
2303: FEM_ENGINES_PKG.TECH_MESSAGE
2304: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_lvlspec_select_stmt'
2305: ,x_attr_lvlspec_select_stmt);
2306:
2307: LOOP
2318: x_part_name => v_part_name,
2319: p_req_id => p_master_request_id,
2320: p_proc_num => p_proc_num);
2321:
2322: FEM_ENGINES_PKG.TECH_MESSAGE
2323: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
2324: ,v_slc_val);
2325: FEM_ENGINES_PKG.TECH_MESSAGE
2326: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
2321:
2322: FEM_ENGINES_PKG.TECH_MESSAGE
2323: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
2324: ,v_slc_val);
2325: FEM_ENGINES_PKG.TECH_MESSAGE
2326: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
2327: ,v_slc_val2);
2328: FEM_ENGINES_PKG.TECH_MESSAGE
2329: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
2324: ,v_slc_val);
2325: FEM_ENGINES_PKG.TECH_MESSAGE
2326: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
2327: ,v_slc_val2);
2328: FEM_ENGINES_PKG.TECH_MESSAGE
2329: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
2330: ,v_slc_val3);
2331: FEM_ENGINES_PKG.TECH_MESSAGE
2332: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
2327: ,v_slc_val2);
2328: FEM_ENGINES_PKG.TECH_MESSAGE
2329: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
2330: ,v_slc_val3);
2331: FEM_ENGINES_PKG.TECH_MESSAGE
2332: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
2333: ,v_slc_val4);
2334:
2335:
2383: -- EXIT Fetch LOOP If No Rows are Retrieved --
2384: ----------------------------------------------
2385: v_attr_last_row := t_rowid.LAST;
2386:
2387: FEM_ENGINES_PKG.TECH_MESSAGE
2388: (c_log_level_1,c_block||'.'||c_proc_name||'.v_attr_last_row'
2389: ,v_attr_last_row);
2390:
2391: IF (v_attr_last_row IS NULL)
2407: ----------------------------------------------------------
2408: -- Count the error rows
2409: ----------------------------------------------------------
2410: v_rows_rejected := v_rows_rejected + v_attr_last_row;
2411: FEM_ENGINES_PKG.TECH_MESSAGE
2412: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_rejected'
2413: ,v_rows_rejected);
2414:
2415: ----------------------------------------------------------
2442: -- EXIT Fetch LOOP If No Rows are Retrieved --
2443: ----------------------------------------------
2444: v_attr_last_row := t_rowid.LAST;
2445:
2446: FEM_ENGINES_PKG.TECH_MESSAGE
2447: (c_log_level_1,c_block||'.'||c_proc_name||'.v_attr_last_row'
2448: ,v_attr_last_row);
2449:
2450: IF (v_attr_last_row IS NULL)
2466: ----------------------------------------------------------
2467: -- Count the error rows
2468: ----------------------------------------------------------
2469: v_rows_rejected := v_rows_rejected + v_attr_last_row;
2470: FEM_ENGINES_PKG.TECH_MESSAGE
2471: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_rejected'
2472: ,v_rows_rejected);
2473:
2474: ----------------------------------------------------------
2506:
2507: EXECUTE IMMEDIATE x_special_calp_status_stmt USING v_slc_val, v_slc_val2;
2508: v_bulk_rows_rejected := SQL%ROWCOUNT;
2509: v_rows_rejected := v_rows_rejected + v_bulk_rows_rejected;
2510: FEM_ENGINES_PKG.TECH_MESSAGE
2511: (c_log_level_1,c_block||'.'||c_proc_name||'.bulk_rows_rejected',v_bulk_rows_rejected);
2512:
2513:
2514: x_special_calp_status_stmt := 'UPDATE fem_cal_periods_attr_t B'||
2527:
2528: EXECUTE IMMEDIATE x_special_calp_status_stmt USING v_slc_val, v_slc_val2;
2529: v_bulk_rows_rejected := SQL%ROWCOUNT;
2530: v_rows_rejected := v_rows_rejected + v_bulk_rows_rejected;
2531: FEM_ENGINES_PKG.TECH_MESSAGE
2532: (c_log_level_1,c_block||'.'||c_proc_name||'.bulk_rows_rejected',v_bulk_rows_rejected);
2533:
2534: --------------------------------------------------------------------------
2535: -- END Special CAL_PERIOD Status Update
2537: END IF; -- special CAL_PERIOD status update
2538: --------------------------------------------------------------------------
2539: -- BEGIN Invalid Version Display Code in ATTR_T table
2540: --------------------------------------------------------------------------
2541: FEM_ENGINES_PKG.TECH_MESSAGE
2542: (c_log_level_3,c_block||'.'||c_proc_name
2543: ,'Attribute assignments with invalid version_display_code');
2544:
2545: FEM_ENGINES_PKG.TECH_MESSAGE
2541: FEM_ENGINES_PKG.TECH_MESSAGE
2542: (c_log_level_3,c_block||'.'||c_proc_name
2543: ,'Attribute assignments with invalid version_display_code');
2544:
2545: FEM_ENGINES_PKG.TECH_MESSAGE
2546: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_vers_select_stmt',x_bad_attr_vers_select_stmt);
2547:
2548: FEM_ENGINES_PKG.TECH_MESSAGE
2549: (c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_status_stmt '
2544:
2545: FEM_ENGINES_PKG.TECH_MESSAGE
2546: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_attr_vers_select_stmt',x_bad_attr_vers_select_stmt);
2547:
2548: FEM_ENGINES_PKG.TECH_MESSAGE
2549: (c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_status_stmt '
2550: ,x_update_attr_status_stmt);
2551:
2552: IF p_load_type <> ('DIMENSION_GROUP') THEN
2571: -- EXIT Fetch LOOP If No Rows are Retrieved --
2572: ----------------------------------------------
2573: v_attr_last_row := t_rowid.LAST;
2574:
2575: FEM_ENGINES_PKG.TECH_MESSAGE
2576: (c_log_level_1,c_block||'.'||c_proc_name||'.v_attr_last_row'
2577: ,v_attr_last_row);
2578:
2579: IF (v_attr_last_row IS NULL)
2595: ----------------------------------------------------------
2596: -- Count the error rows
2597: ----------------------------------------------------------
2598: v_rows_rejected := v_rows_rejected + v_attr_last_row;
2599: FEM_ENGINES_PKG.TECH_MESSAGE
2600: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_rejected'
2601: ,v_rows_rejected);
2602:
2603: ----------------------------------------------------------
2622: CLOSE cv_get_bad_attr_vers;
2623: END IF;
2624:
2625: --x_rows_rejected := v_rows_rejected;
2626: FEM_ENGINES_PKG.TECH_MESSAGE
2627: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
2628:
2629: EXCEPTION
2630: WHEN OTHERS THEN
2640:
2641: gv_prg_msg := sqlerrm;
2642: gv_callstack := dbms_utility.format_call_stack;
2643:
2644: FEM_ENGINES_PKG.TECH_MESSAGE
2645: (p_severity => c_log_level_6
2646: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
2647: ,p_msg_text => gv_prg_msg);
2648:
2645: (p_severity => c_log_level_6
2646: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
2647: ,p_msg_text => gv_prg_msg);
2648:
2649: FEM_ENGINES_PKG.TECH_MESSAGE
2650: (p_severity => c_log_level_6
2651: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
2652: ,p_msg_text => gv_callstack);
2653:
2650: (p_severity => c_log_level_6
2651: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
2652: ,p_msg_text => gv_callstack);
2653:
2654: FEM_ENGINES_PKG.USER_MESSAGE
2655: (p_app_name => c_fem
2656: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
2657: ,P_TOKEN1 => 'ERR_MSG'
2658: ,P_VALUE1 => gv_prg_msg);
2656: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
2657: ,P_TOKEN1 => 'ERR_MSG'
2658: ,P_VALUE1 => gv_prg_msg);
2659:
2660: /* FEM_ENGINES_PKG.USER_MESSAGE
2661: (p_app_name => c_fem
2662: ,p_msg_text => gv_prg_msg); */
2663:
2664:
3016: cv_get_rows cv_curs;
3017: cv_get_attr_rows cv_curs;
3018:
3019: BEGIN
3020: FEM_ENGINES_PKG.TECH_MESSAGE
3021: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
3022:
3023: --DBMS_SESSION.SET_SQL_TRACE (sql_trace => TRUE);
3024:
3021: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
3022:
3023: --DBMS_SESSION.SET_SQL_TRACE (sql_trace => TRUE);
3024:
3025: FEM_ENGINES_PKG.TECH_MESSAGE
3026: (c_log_level_1,c_block||'.'||c_proc_name||'.p_simple_dimension_flag'
3027: ,p_simple_dimension_flag);
3028:
3029: --x_status := 0; -- initialize status of the New_Members procedure
3064: IF v_fetch_limit IS NULL THEN
3065: v_fetch_limit := 10000;
3066: END IF;
3067:
3068: FEM_ENGINES_PKG.TECH_MESSAGE
3069: (c_log_level_1,c_block||'.'||c_proc_name||'.member select_stmt'
3070: ,x_select_stmt);
3071:
3072:
3080: ,p_member_dc_col
3081: ,p_member_name_col
3082: ,x_insert_member_stmt);
3083:
3084: FEM_ENGINES_PKG.TECH_MESSAGE
3085: (c_log_level_1,c_block||'.'||c_proc_name||'.insert member stmt'
3086: ,x_insert_member_stmt);
3087:
3088: build_status_update_stmt (p_source_b_table
3108: ,p_member_name_col
3109: ,p_value_set_required_flag
3110: ,'NEW_MEMBERS'
3111: ,x_dupname_count_stmt);
3112: FEM_ENGINES_PKG.TECH_MESSAGE
3113: (c_log_level_1,c_block||'.'||c_proc_name||'.dupname select stmt'
3114: ,x_dupname_count_stmt);
3115:
3116:
3136: ELSE
3137: x_attr_select_stmt := REPLACE(x_attr_select_stmt,'{{data_slice}}','1=1');
3138: END IF;
3139:
3140: FEM_ENGINES_PKG.TECH_MESSAGE
3141: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute select stmt'
3142: ,x_attr_select_stmt);
3143:
3144:
3158: ,p_member_dc_col
3159: ,p_value_set_required_flag
3160: ,x_insert_attr_stmt);
3161:
3162: FEM_ENGINES_PKG.TECH_MESSAGE
3163: (c_log_level_1,c_block||'.'||c_proc_name||'.insert attr stmt'
3164: ,x_insert_attr_stmt);
3165:
3166:
3192: x_part_name => v_part_name,
3193: p_req_id => p_master_request_id,
3194: p_proc_num => p_proc_num);
3195:
3196: FEM_ENGINES_PKG.TECH_MESSAGE
3197: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
3198: ,v_slc_val);
3199: FEM_ENGINES_PKG.TECH_MESSAGE
3200: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
3195:
3196: FEM_ENGINES_PKG.TECH_MESSAGE
3197: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
3198: ,v_slc_val);
3199: FEM_ENGINES_PKG.TECH_MESSAGE
3200: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
3201: ,v_slc_val2);
3202: FEM_ENGINES_PKG.TECH_MESSAGE
3203: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
3198: ,v_slc_val);
3199: FEM_ENGINES_PKG.TECH_MESSAGE
3200: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
3201: ,v_slc_val2);
3202: FEM_ENGINES_PKG.TECH_MESSAGE
3203: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
3204: ,v_slc_val3);
3205: FEM_ENGINES_PKG.TECH_MESSAGE
3206: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
3201: ,v_slc_val2);
3202: FEM_ENGINES_PKG.TECH_MESSAGE
3203: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
3204: ,v_slc_val3);
3205: FEM_ENGINES_PKG.TECH_MESSAGE
3206: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
3207: ,v_slc_val4);
3208:
3209: EXIT WHEN (v_slc_id IS NULL);
3261: THEN
3262: EXIT;
3263: END IF;
3264:
3265: FEM_ENGINES_PKG.TECH_MESSAGE
3266: (c_log_level_1,c_block||'.'||c_proc_name||'.Rows retrieved this fetch',v_mbr_last_row);
3267:
3268: v_rows_fetched := v_rows_fetched + v_mbr_last_row;
3269:
3272: ----------------------------------------------
3273: FOR i IN 1..v_mbr_last_row
3274: LOOP
3275:
3276: FEM_ENGINES_PKG.TECH_MESSAGE
3277: (c_log_level_1,c_block||'.'||c_proc_name||'.member display code'
3278: ,t_member_dc(i));
3279: FEM_ENGINES_PKG.TECH_MESSAGE
3280: (c_log_level_1,c_block||'.'||c_proc_name||'.dimension_group_id'
3275:
3276: FEM_ENGINES_PKG.TECH_MESSAGE
3277: (c_log_level_1,c_block||'.'||c_proc_name||'.member display code'
3278: ,t_member_dc(i));
3279: FEM_ENGINES_PKG.TECH_MESSAGE
3280: (c_log_level_1,c_block||'.'||c_proc_name||'.dimension_group_id'
3281: ,t_dimension_group_id(i));
3282:
3283: -- initializing the adj_period_flag
3310: AND V.attribute_id = DA.attribute_id;
3311: EXCEPTION
3312: WHEN no_data_found THEN
3313: t_periods_in_year(i) := 0;
3314: FEM_ENGINES_PKG.TECH_MESSAGE
3315: (c_log_level_1,c_block||'.'||c_proc_name||'.periods_in_year'
3316: ,t_periods_in_year(i));
3317:
3318: END;
3411: FROM fem_dim_attr_grps
3412: WHERE dimension_group_id = t_dimension_group_id(i))); */
3413:
3414:
3415: FEM_ENGINES_PKG.TECH_MESSAGE
3416: (c_log_level_1,c_block||'.'||c_proc_name||'.member status'
3417: ,t_b_status(i));
3418:
3419: ------------------------------------------------------------
3478: ,ta_calpattr_period_num
3479: LIMIT v_fetch_limit;
3480:
3481: v_attr_last_row := NVL(ta_attribute_id.LAST,0);
3482: FEM_ENGINES_PKG.TECH_MESSAGE
3483: (c_log_level_1,c_block||'.'||c_proc_name||'.assignment rows',v_attr_last_row);
3484:
3485: FEM_ENGINES_PKG.TECH_MESSAGE
3486: (c_log_level_1,c_block||'.'||c_proc_name||'.req_attr_rows',v_req_attribute_count);
3481: v_attr_last_row := NVL(ta_attribute_id.LAST,0);
3482: FEM_ENGINES_PKG.TECH_MESSAGE
3483: (c_log_level_1,c_block||'.'||c_proc_name||'.assignment rows',v_attr_last_row);
3484:
3485: FEM_ENGINES_PKG.TECH_MESSAGE
3486: (c_log_level_1,c_block||'.'||c_proc_name||'.req_attr_rows',v_req_attribute_count);
3487:
3488: -- Not enough attribute rows were returned because of either:
3489: -- 1) the join to the Version table (bad version display_code in _ATTR_T)
3501: IF t_b_status(i) IN ('LOAD')
3502: AND v_attr_last_row < v_req_attribute_count THEN
3503: t_b_status(i) := 'MISSING_REQUIRED_ATTRIBUTE';
3504: t_tl_status(i) := 'INVALID_MEMBER';
3505: FEM_ENGINES_PKG.TECH_MESSAGE
3506: (c_log_level_1,c_block||'.'||c_proc_name||'.missing_req - end_date=',t_cal_period_end_date(i));
3507: ELSIF t_b_status(i) IN ('LOAD')
3508: AND v_attr_last_row > v_req_attribute_count THEN
3509: t_b_status(i) := 'DUPLICATE_REQUIRED_ATTRIBUTES';
3511: ELSE
3512: FOR j IN 1..v_attr_last_row
3513: LOOP
3514:
3515: FEM_ENGINES_PKG.TECH_MESSAGE
3516: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_label'
3517: ,ta_attribute_varchar_label(j));
3518: FEM_ENGINES_PKG.TECH_MESSAGE
3519: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute assign value'
3514:
3515: FEM_ENGINES_PKG.TECH_MESSAGE
3516: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_label'
3517: ,ta_attribute_varchar_label(j));
3518: FEM_ENGINES_PKG.TECH_MESSAGE
3519: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute assign value'
3520: ,ta_attribute_assign_value(j));
3521: FEM_ENGINES_PKG.TECH_MESSAGE
3522: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_column_name'
3517: ,ta_attribute_varchar_label(j));
3518: FEM_ENGINES_PKG.TECH_MESSAGE
3519: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute assign value'
3520: ,ta_attribute_assign_value(j));
3521: FEM_ENGINES_PKG.TECH_MESSAGE
3522: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_column_name'
3523: ,ta_attr_value_column_name(j));
3524:
3525: /*
3605: ,ta_calpattr_end_date(j)
3606: ,ta_calpattr_period_num(j));
3607:
3608: IF ta_status(j) NOT IN ('LOAD') THEN
3609: FEM_ENGINES_PKG.TECH_MESSAGE
3610: (c_log_level_1,c_block||'.'||c_proc_name
3611: ,'Invalid Member - exiting Attribute loop');
3612:
3613: t_b_status(i) := 'INVALID_REQUIRED_ATTRIBUTE';
3652: ta_attribute_varchar_label(j) = 'GL_PERIOD_NUM' AND
3653: ((t_cal_period_number(i) <> ta_number_assign_value(j) OR
3654: t_cal_period_number(i) < 1 )) THEN
3655:
3656: FEM_ENGINES_PKG.TECH_MESSAGE
3657: (c_log_level_1,c_block||'.'||c_proc_name||'.period_number'
3658: ,ta_number_assign_value(j));
3659:
3660: RAISE e_invalid_cal_period_number;
3696: ELSIF (ta_attr_value_column_name(j) = 'DATE_ASSIGN_VALUE'
3697: AND ta_attribute_assign_value(j) IS NOT NULL
3698: AND ta_version_id(j) IS NOT NULL) THEN
3699: BEGIN
3700: FEM_ENGINES_PKG.TECH_MESSAGE
3701: (c_log_level_1,c_block||'.'||c_proc_name||'.assigning date'
3702: ,ta_attribute_assign_value(j));
3703: ta_date_assign_value(j)
3704: := to_date(ta_attribute_assign_value(j),p_date_format_mask);
3702: ,ta_attribute_assign_value(j));
3703: ta_date_assign_value(j)
3704: := to_date(ta_attribute_assign_value(j),p_date_format_mask);
3705:
3706: FEM_ENGINES_PKG.TECH_MESSAGE
3707: (c_log_level_1,c_block||'.'||c_proc_name||'.end assigning date'
3708: ,ta_date_assign_value(j));
3709:
3710: -- Special validations for CAL_PERIOD
3814: ' and VE.attribute_id = AE.attribute_id'||
3815: ' and CE.date_assign_value'||
3816: ' >= :b_new_start_date';
3817:
3818: FEM_ENGINES_PKG.TECH_MESSAGE
3819: (c_log_level_1,c_block||'.'||c_proc_name||'.overlap_sql_stmt3'
3820: ,x_overlap_sql_stmt);
3821:
3822: EXECUTE IMMEDIATE x_overlap_sql_stmt INTO v_overlap_count
3906: ' >= '''||to_char(ta_date_assign_value(j),p_date_format_mask)||''''||
3907: ' and to_char(CS.date_assign_value,'''||p_date_format_mask||''')'||
3908: ' <= '''||to_char(t_cal_period_end_date(i),p_date_format_mask)||'''';
3909:
3910: FEM_ENGINES_PKG.TECH_MESSAGE
3911: (c_log_level_1,c_block||'.'||c_proc_name||'.overlap_sql_stmt4'
3912: ,x_overlap_sql_stmt);
3913: EXECUTE IMMEDIATE x_overlap_sql_stmt INTO v_overlap_count;
3914:
3998: ,ta_attribute_assign_value(j)
3999: ,ta_attr_assign_vs_dc(j)
4000: ,v_attr_success
4001: ,v_temp_member);
4002: FEM_ENGINES_PKG.TECH_MESSAGE
4003: (c_log_level_1,c_block||'.'||c_proc_name||'.verify_attribute_success'
4004: ,v_attr_success);
4005:
4006: IF (v_attr_success = 'N') THEN
4031: t_b_status(i) := 'INVALID_REQUIRED_ATTRIBUTE';
4032: t_tl_status(i) := 'INVALID_MEMBER';
4033: END IF; -- Main IF on validating the attributes
4034:
4035: FEM_ENGINES_PKG.TECH_MESSAGE
4036: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_status'
4037: ,ta_status(j));
4038: FEM_ENGINES_PKG.TECH_MESSAGE
4039: (c_log_level_1,c_block||'.'||c_proc_name||'.member_status'
4034:
4035: FEM_ENGINES_PKG.TECH_MESSAGE
4036: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_status'
4037: ,ta_status(j));
4038: FEM_ENGINES_PKG.TECH_MESSAGE
4039: (c_log_level_1,c_block||'.'||c_proc_name||'.member_status'
4040: ,t_b_status(i));
4041:
4042: IF (t_b_status(i) NOT IN ('LOAD')) THEN
4039: (c_log_level_1,c_block||'.'||c_proc_name||'.member_status'
4040: ,t_b_status(i));
4041:
4042: IF (t_b_status(i) NOT IN ('LOAD')) THEN
4043: FEM_ENGINES_PKG.TECH_MESSAGE
4044: (c_log_level_1,c_block||'.'||c_proc_name
4045: ,'Invalid Member - exiting Attribute loop');
4046:
4047: EXIT; -- member is no good so exit
4048: END IF;
4049: END LOOP; -- attribute validations
4050: END IF; -- If v_attr_last_row = v_req_attribute_count
4051: --------------------------------------------------------------------
4052: FEM_ENGINES_PKG.TECH_MESSAGE
4053: (c_log_level_1,c_block||'.'||c_proc_name||'.member status'
4054: ,t_b_status(i));
4055:
4056: FEM_ENGINES_PKG.TECH_MESSAGE
4052: FEM_ENGINES_PKG.TECH_MESSAGE
4053: (c_log_level_1,c_block||'.'||c_proc_name||'.member status'
4054: ,t_b_status(i));
4055:
4056: FEM_ENGINES_PKG.TECH_MESSAGE
4057: (c_log_level_1,c_block||'.'||c_proc_name||'.tl_member status'
4058: ,t_tl_status(i));
4059:
4060: IF (t_b_status(i) NOT IN ('LOAD')) THEN
4057: (c_log_level_1,c_block||'.'||c_proc_name||'.tl_member status'
4058: ,t_tl_status(i));
4059:
4060: IF (t_b_status(i) NOT IN ('LOAD')) THEN
4061: FEM_ENGINES_PKG.TECH_MESSAGE
4062: (c_log_level_1,c_block||'.'||c_proc_name
4063: ,'Invalid Member - Reset all attributes of the member to INVALID_MEMBER');
4064:
4065: FOR k IN 1..v_attr_last_row
4163: -----------------------------------------------------------------
4164: v_mbr_last_row := t_member_dc.LAST;
4165: v_mbr_count := 1;
4166:
4167: FEM_ENGINES_PKG.TECH_MESSAGE
4168: (c_log_level_1,c_block||'.'||c_proc_name||'.before copy good members',
4169: v_mbr_last_row);
4170:
4171:
4173: -- Count the attribute error rows
4174: ----------------------------------------------------------
4175: v_rows_rejected := v_rows_rejected + v_temp_rows_rejected;
4176: v_temp_rows_rejected := 0; -- initialize so we can use again
4177: FEM_ENGINES_PKG.TECH_MESSAGE
4178: (c_log_level_1,c_block||'.'||c_proc_name||'.attr error rows',
4179: v_rows_rejected);
4180:
4181: WHILE v_mbr_count <= v_mbr_last_row
4187: FEM_CALP_INTERIM_T and FEM_CALP_ATTR_INTERIM_T tables
4188: so that we can use MP for CAL_PERIOD loads
4189: ______________________________________________________
4190:
4191: FEM_ENGINES_PKG.TECH_MESSAGE
4192: (c_log_level_1,c_block||'.'||c_proc_name
4193: ,'Start Overlap Check - t_b_status = '||t_b_status(v_mbr_count));
4194:
4195:
4198: -- date overlaps
4199: v_mbr_subcount := v_mbr_count + 1; -- we always start checking
4200: -- for date overlaps starting one
4201: -- member below our current position
4202: FEM_ENGINES_PKG.TECH_MESSAGE
4203: (c_log_level_1,c_block||'.'||c_proc_name
4204: ,'Overlap Check - v_mbr_count = '||v_mbr_count);
4205:
4206: IF p_dimension_varchar_label = 'CAL_PERIOD'
4207: AND p_load_type NOT IN ('DIMENSION_GROUP')
4208: AND t_b_status(v_mbr_count) = 'LOAD'
4209: AND t_adj_period_flag(v_mbr_count) = 'N' THEN
4210:
4211: FEM_ENGINES_PKG.TECH_MESSAGE
4212: (c_log_level_1,c_block||'.'||c_proc_name
4213: ,'Overlap Check inside CAL_PERIOD = ');
4214:
4215: WHILE v_mbr_subcount <= v_mbr_last_row AND t_b_status(v_mbr_count) = 'LOAD'
4214:
4215: WHILE v_mbr_subcount <= v_mbr_last_row AND t_b_status(v_mbr_count) = 'LOAD'
4216: LOOP
4217:
4218: FEM_ENGINES_PKG.TECH_MESSAGE
4219: (c_log_level_1,c_block||'.'||c_proc_name
4220: ,'Overlap Check - sub start_date = '||t_cal_period_start_date(v_mbr_subcount));
4221:
4222: FEM_ENGINES_PKG.TECH_MESSAGE
4218: FEM_ENGINES_PKG.TECH_MESSAGE
4219: (c_log_level_1,c_block||'.'||c_proc_name
4220: ,'Overlap Check - sub start_date = '||t_cal_period_start_date(v_mbr_subcount));
4221:
4222: FEM_ENGINES_PKG.TECH_MESSAGE
4223: (c_log_level_1,c_block||'.'||c_proc_name
4224: ,'Overlap Check - start_date = '||t_cal_period_start_date(v_mbr_count));
4225:
4226: FEM_ENGINES_PKG.TECH_MESSAGE
4222: FEM_ENGINES_PKG.TECH_MESSAGE
4223: (c_log_level_1,c_block||'.'||c_proc_name
4224: ,'Overlap Check - start_date = '||t_cal_period_start_date(v_mbr_count));
4225:
4226: FEM_ENGINES_PKG.TECH_MESSAGE
4227: (c_log_level_1,c_block||'.'||c_proc_name
4228: ,'Overlap Check - sub end_date = '||t_cal_period_end_date(v_mbr_subcount));
4229:
4230: FEM_ENGINES_PKG.TECH_MESSAGE
4226: FEM_ENGINES_PKG.TECH_MESSAGE
4227: (c_log_level_1,c_block||'.'||c_proc_name
4228: ,'Overlap Check - sub end_date = '||t_cal_period_end_date(v_mbr_subcount));
4229:
4230: FEM_ENGINES_PKG.TECH_MESSAGE
4231: (c_log_level_1,c_block||'.'||c_proc_name
4232: ,'Overlap Check - end_date = '||t_cal_period_end_date(v_mbr_count));
4233:
4234: FEM_ENGINES_PKG.TECH_MESSAGE
4230: FEM_ENGINES_PKG.TECH_MESSAGE
4231: (c_log_level_1,c_block||'.'||c_proc_name
4232: ,'Overlap Check - end_date = '||t_cal_period_end_date(v_mbr_count));
4233:
4234: FEM_ENGINES_PKG.TECH_MESSAGE
4235: (c_log_level_1,c_block||'.'||c_proc_name
4236: ,'Overlap Check - sub adj_flag = '||t_adj_period_flag(v_mbr_subcount));
4237:
4238: IF t_adj_period_flag(v_mbr_subcount) = 'N'
4246: t_cal_period_end_date(v_mbr_count)))
4247: THEN
4248: v_attr_last_row := NVL(tfa_attribute_id.LAST,0);
4249: v_attr_count := 0;
4250: FEM_ENGINES_PKG.TECH_MESSAGE
4251: (c_log_level_1,c_block||'.'||c_proc_name
4252: ,'Overlap Member - Reset all attributes of the member to INVALID_MEMBER');
4253:
4254: FOR k IN 1..v_attr_last_row
4296: tf_time_group_type_code(v_mbr_final_count) :=t_time_group_type_code(v_mbr_count);
4297: tf_dimension_group_seq(v_mbr_final_count) :=t_dimension_group_seq(v_mbr_count);
4298:
4299: END IF;
4300: FEM_ENGINES_PKG.TECH_MESSAGE
4301: (c_log_level_1,c_block||'.'||c_proc_name||'.In the copy',
4302: null);
4303:
4304:
4327: -- count the error members
4328: -- we add 2 because every new member always has 1 B_T record
4329: -- and a TL_T record
4330: v_temp_rows_rejected := v_temp_rows_rejected + 2;
4331: FEM_ENGINES_PKG.TECH_MESSAGE
4332: (c_log_level_1,c_block||'.'||c_proc_name||'.v_temp_rows_rejected',
4333: v_temp_rows_rejected);
4334:
4335: END IF; -- Copy good members
4338: -- count the error members
4339: -- we add 2 because every new member always has 1 B_T record
4340: -- and a TL_T record
4341: v_temp_rows_rejected := v_temp_rows_rejected + 2;
4342: FEM_ENGINES_PKG.TECH_MESSAGE
4343: (c_log_level_1,c_block||'.'||c_proc_name||'.v_temp_rows_rejected',
4344: v_temp_rows_rejected);
4345:
4346: END IF; -- status = LOAD
4349:
4350: ------------------------------------------------------------------------
4351: -- INSERTING
4352: ------------------------------------------------------------------------
4353: FEM_ENGINES_PKG.TECH_MESSAGE
4354: (c_log_level_3,c_block||'.'||c_proc_name||'.Member_Insert',
4355: null);
4356:
4357: ---------------------------------------------------------
4357: ---------------------------------------------------------
4358: -- set the v_final_mbr_last_row in case a member was removed
4359: ---------------------------------------------------------
4360: v_final_mbr_last_row := tf_member_dc.LAST;
4361: FEM_ENGINES_PKG.TECH_MESSAGE
4362: (c_log_level_3,c_block||'.'||c_proc_name||'.v_attr_final_count',
4363: v_final_mbr_last_row);
4364:
4365:
4385: ,tf_dimgrp_dimension_group_id(i)
4386: ,tf_dimension_id(i);
4387:
4388: END IF;
4389: FEM_ENGINES_PKG.TECH_MESSAGE
4390: (c_log_level_1,c_block||'.'||c_proc_name||'.update member status.p_value_set_req_flag'
4391: ,p_value_set_required_flag);
4392:
4393: FEM_ENGINES_PKG.TECH_MESSAGE
4389: FEM_ENGINES_PKG.TECH_MESSAGE
4390: (c_log_level_1,c_block||'.'||c_proc_name||'.update member status.p_value_set_req_flag'
4391: ,p_value_set_required_flag);
4392:
4393: FEM_ENGINES_PKG.TECH_MESSAGE
4394: (c_log_level_1,c_block||'.'||c_proc_name||'.update member status.p_hier_dim_flag'
4395: ,p_hier_dimension_flag);
4396:
4397:
4394: (c_log_level_1,c_block||'.'||c_proc_name||'.update member status.p_hier_dim_flag'
4395: ,p_hier_dimension_flag);
4396:
4397:
4398: FEM_ENGINES_PKG.TECH_MESSAGE
4399: (c_log_level_1,c_block||'.'||c_proc_name||'.update member status.gv_apps_user_id'
4400: ,gv_apps_user_id);
4401:
4402: IF (p_load_type = 'DIMENSION_MEMBER') THEN
4402: IF (p_load_type = 'DIMENSION_MEMBER') THEN
4403: IF (p_value_set_required_flag = 'Y'
4404: AND p_hier_dimension_flag = 'Y') THEN
4405:
4406: FEM_ENGINES_PKG.TECH_MESSAGE
4407: (c_log_level_1,c_block||'.'||c_proc_name||'.update member status.in the forall'
4408: ,null);
4409:
4410: FORALL i IN 1..v_final_mbr_last_row
4552: EXECUTE IMMEDIATE x_update_mbr_status_stmt
4553: USING t_b_status(i)
4554: ,t_rowid(i)
4555: ,t_b_status(i);
4556: FEM_ENGINES_PKG.TECH_MESSAGE
4557: (c_log_level_1,c_block||'.'||c_proc_name||'.update member status.v_mbr_last_row'
4558: ,v_mbr_last_row);
4559:
4560: FORALL i IN 1..v_mbr_last_row
4562: USING t_tl_status(i)
4563: ,t_tl_rowid(i)
4564: ,t_tl_status(i);
4565:
4566: FEM_ENGINES_PKG.TECH_MESSAGE
4567: (c_log_level_1,c_block||'.'||c_proc_name||'.update tl status.v_mbr_last_row'
4568: ,v_mbr_last_row);
4569:
4570:
4736: END IF;
4737: --x_rows_rejected := v_rows_rejected;
4738: --x_rows_loaded := v_rows_loaded;
4739:
4740: FEM_ENGINES_PKG.TECH_MESSAGE
4741: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
4742:
4743: EXCEPTION
4744: WHEN OTHERS THEN
4754:
4755: gv_prg_msg := sqlerrm;
4756: gv_callstack := dbms_utility.format_call_stack;
4757:
4758: FEM_ENGINES_PKG.TECH_MESSAGE
4759: (p_severity => c_log_level_6
4760: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
4761: ,p_msg_text => gv_prg_msg);
4762:
4759: (p_severity => c_log_level_6
4760: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
4761: ,p_msg_text => gv_prg_msg);
4762:
4763: FEM_ENGINES_PKG.TECH_MESSAGE
4764: (p_severity => c_log_level_6
4765: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
4766: ,p_msg_text => gv_callstack);
4767:
4764: (p_severity => c_log_level_6
4765: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
4766: ,p_msg_text => gv_callstack);
4767:
4768: FEM_ENGINES_PKG.USER_MESSAGE
4769: (p_app_name => c_fem
4770: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
4771: ,P_TOKEN1 => 'ERR_MSG'
4772: ,P_VALUE1 => gv_prg_msg);
4770: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
4771: ,P_TOKEN1 => 'ERR_MSG'
4772: ,P_VALUE1 => gv_prg_msg);
4773:
4774: -- FEM_ENGINES_PKG.USER_MESSAGE
4775: -- (p_app_name => c_fem
4776: -- ,p_msg_text => gv_prg_msg);
4777:
4778: RAISE e_main_terminate;
4927: ---------------------
4928: cv_get_rows cv_curs;
4929:
4930: BEGIN
4931: FEM_ENGINES_PKG.TECH_MESSAGE
4932: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
4933:
4934: --x_status := 0; -- initialize status of the TL_Update procedure
4935: --x_message := 'COMPLETE:NORMAL';
4968: ELSE
4969: x_ro_mbr_upd_stmt := REPLACE(x_ro_mbr_upd_stmt,'{{data_slice}}','1=1');
4970: END IF;
4971:
4972: FEM_ENGINES_PKG.TECH_MESSAGE
4973: (c_log_level_1,c_block||'.'||c_proc_name||'.ro_mbr_upd stmt'
4974: ,x_ro_mbr_upd_stmt);
4975:
4976:
5026: v_fetch_limit := 10000;
5027: END IF;
5028:
5029:
5030: FEM_ENGINES_PKG.TECH_MESSAGE
5031: (c_log_level_1,c_block||'.'||c_proc_name||'.member select stmt'
5032: ,x_select_stmt);
5033:
5034: FEM_ENGINES_PKG.TECH_MESSAGE
5030: FEM_ENGINES_PKG.TECH_MESSAGE
5031: (c_log_level_1,c_block||'.'||c_proc_name||'.member select stmt'
5032: ,x_select_stmt);
5033:
5034: FEM_ENGINES_PKG.TECH_MESSAGE
5035: (c_log_level_1,c_block||'.'||c_proc_name||'.bad_tl_select_stmt'
5036: ,x_bad_tl_select_stmt);
5037:
5038:
5047: ,p_member_description_col
5048: ,p_value_set_required_flag
5049: ,x_update_tl_stmt);
5050:
5051: FEM_ENGINES_PKG.TECH_MESSAGE
5052: (c_log_level_1,c_block||'.'||c_proc_name||'.tl table update stmt'
5053: ,x_update_tl_stmt);
5054:
5055: build_delete_stmt (p_source_tl_table
5055: build_delete_stmt (p_source_tl_table
5056: ,x_delete_tl_stmt);
5057:
5058:
5059: FEM_ENGINES_PKG.TECH_MESSAGE
5060: (c_log_level_1,c_block||'.'||c_proc_name||'.delete stmt'
5061: ,x_delete_tl_stmt);
5062:
5063: build_status_update_stmt (p_source_tl_table
5074: ,p_value_set_required_flag
5075: ,'TL_UPDATE'
5076: ,x_dupname_count_stmt);
5077:
5078: FEM_ENGINES_PKG.TECH_MESSAGE
5079: (c_log_level_1,c_block||'.'||c_proc_name||'.dupname select stmt'
5080: ,x_dupname_count_stmt);
5081:
5082:
5100: x_part_name => v_part_name,
5101: p_req_id => p_master_request_id,
5102: p_proc_num => p_proc_num);
5103:
5104: FEM_ENGINES_PKG.TECH_MESSAGE
5105: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
5106: ,v_slc_val);
5107: FEM_ENGINES_PKG.TECH_MESSAGE
5108: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
5103:
5104: FEM_ENGINES_PKG.TECH_MESSAGE
5105: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
5106: ,v_slc_val);
5107: FEM_ENGINES_PKG.TECH_MESSAGE
5108: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
5109: ,v_slc_val2);
5110: FEM_ENGINES_PKG.TECH_MESSAGE
5111: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
5106: ,v_slc_val);
5107: FEM_ENGINES_PKG.TECH_MESSAGE
5108: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
5109: ,v_slc_val2);
5110: FEM_ENGINES_PKG.TECH_MESSAGE
5111: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
5112: ,v_slc_val3);
5113: FEM_ENGINES_PKG.TECH_MESSAGE
5114: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
5109: ,v_slc_val2);
5110: FEM_ENGINES_PKG.TECH_MESSAGE
5111: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
5112: ,v_slc_val3);
5113: FEM_ENGINES_PKG.TECH_MESSAGE
5114: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
5115: ,v_slc_val4);
5116:
5117:
5190: THEN
5191: EXIT;
5192: END IF;
5193:
5194: FEM_ENGINES_PKG.TECH_MESSAGE
5195: (c_log_level_1,c_block||'.'||c_proc_name||'.Rows retrieved this fetch',v_mbr_last_row);
5196:
5197: --v_rows_fetched := v_rows_fetched + v_mbr_last_row;
5198:
5274:
5275: ---------------------------------------------------------
5276: -- Delete Loaded member records from the _TL_T table
5277: ---------------------------------------------------------
5278: FEM_ENGINES_PKG.TECH_MESSAGE
5279: (c_log_level_1,c_block||'.'||c_proc_name||'.Deleting TL_T rows','start');
5280: FORALL i IN 1..v_mbr_last_row
5281: EXECUTE IMMEDIATE x_delete_tl_stmt
5282: USING t_tl_rowid(i)
5358: THEN
5359: EXIT;
5360: END IF;
5361:
5362: FEM_ENGINES_PKG.TECH_MESSAGE
5363: (c_log_level_1,c_block||'.'||c_proc_name||'.Rows retrieved this fetch',v_mbr_last_row);
5364:
5365: ----------------------------------------------------------
5366: -- Update Status of Bad TL Collection
5425: END IF;
5426:
5427: --x_rows_rejected := v_rows_rejected + v_bulk_rows_rejected;
5428:
5429: FEM_ENGINES_PKG.TECH_MESSAGE
5430: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
5431:
5432: EXCEPTION
5433: WHEN OTHERS THEN
5440:
5441: gv_prg_msg := sqlerrm;
5442: gv_callstack := dbms_utility.format_call_stack;
5443:
5444: FEM_ENGINES_PKG.TECH_MESSAGE
5445: (p_severity => c_log_level_6
5446: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
5447: ,p_msg_text => gv_prg_msg);
5448:
5445: (p_severity => c_log_level_6
5446: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
5447: ,p_msg_text => gv_prg_msg);
5448:
5449: FEM_ENGINES_PKG.TECH_MESSAGE
5450: (p_severity => c_log_level_6
5451: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
5452: ,p_msg_text => gv_callstack);
5453:
5450: (p_severity => c_log_level_6
5451: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
5452: ,p_msg_text => gv_callstack);
5453:
5454: FEM_ENGINES_PKG.USER_MESSAGE
5455: (p_app_name => c_fem
5456: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
5457: ,P_TOKEN1 => 'ERR_MSG'
5458: ,P_VALUE1 => gv_prg_msg);
5456: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
5457: ,P_TOKEN1 => 'ERR_MSG'
5458: ,P_VALUE1 => gv_prg_msg);
5459:
5460: -- FEM_ENGINES_PKG.USER_MESSAGE
5461: -- (p_app_name => c_fem
5462: -- ,p_msg_text => gv_prg_msg);
5463:
5464: RAISE e_main_terminate;
5596: ---------------------
5597: cv_get_remain_mbr cv_curs;
5598:
5599: BEGIN
5600: FEM_ENGINES_PKG.TECH_MESSAGE
5601: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
5602:
5603: --x_status := 0; -- initialize status of the Base_Update procedure
5604: --x_message := 'COMPLETE:NORMAL';
5630: ELSE
5631: x_remain_mbr_select_stmt := REPLACE(x_remain_mbr_select_stmt,'{{data_slice}}','1=1');
5632: END IF;
5633:
5634: FEM_ENGINES_PKG.TECH_MESSAGE
5635: (c_log_level_1,c_block||'.'||c_proc_name||'.member select stmt'
5636: ,x_remain_mbr_select_stmt);
5637:
5638: build_status_update_stmt (p_source_b_table
5637:
5638: build_status_update_stmt (p_source_b_table
5639: ,x_update_mbr_status_stmt);
5640:
5641: FEM_ENGINES_PKG.TECH_MESSAGE
5642: (c_log_level_1,c_block||'.'||c_proc_name||'.status update stmt'
5643: ,x_update_mbr_status_stmt);
5644:
5645: build_delete_stmt (p_source_b_table
5644:
5645: build_delete_stmt (p_source_b_table
5646: ,x_delete_mbr_stmt);
5647:
5648: FEM_ENGINES_PKG.TECH_MESSAGE
5649: (c_log_level_1,c_block||'.'||c_proc_name||'.delete stmt'
5650: ,x_delete_mbr_stmt);
5651:
5652: build_seq_enf_hiercount_stmt (p_value_set_required_flag
5652: build_seq_enf_hiercount_stmt (p_value_set_required_flag
5653: ,p_hier_table_name
5654: ,x_seq_enf_hiercount_stmt);
5655:
5656: FEM_ENGINES_PKG.TECH_MESSAGE
5657: (c_log_level_1,c_block||'.'||c_proc_name||'.seq_enf_hiercount_stmt'
5658: ,x_seq_enf_hiercount_stmt);
5659:
5660: build_dimgrp_update_stmt (p_target_b_table
5661: ,p_value_set_required_flag
5662: ,p_member_dc_col
5663: ,x_update_dimgrp_stmt);
5664:
5665: FEM_ENGINES_PKG.TECH_MESSAGE
5666: (c_log_level_1,c_block||'.'||c_proc_name||'.update member base table stmt'
5667: ,x_update_dimgrp_stmt);
5668:
5669: build_enable_update_stmt (p_dimension_varchar_label
5679: ELSE
5680: x_enabled_flag_update_stmt := REPLACE(x_enabled_flag_update_stmt,'{{data_slice}}','1=1');
5681: END IF;
5682:
5683: FEM_ENGINES_PKG.TECH_MESSAGE
5684: (c_log_level_1,c_block||'.'||c_proc_name||'.update member enabled_flag stmt'
5685: ,x_enabled_flag_update_stmt);
5686:
5687:
5702: x_part_name => v_part_name,
5703: p_req_id => p_master_request_id,
5704: p_proc_num => p_proc_num);
5705:
5706: FEM_ENGINES_PKG.TECH_MESSAGE
5707: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
5708: ,v_slc_val);
5709: FEM_ENGINES_PKG.TECH_MESSAGE
5710: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
5705:
5706: FEM_ENGINES_PKG.TECH_MESSAGE
5707: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
5708: ,v_slc_val);
5709: FEM_ENGINES_PKG.TECH_MESSAGE
5710: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
5711: ,v_slc_val2);
5712: FEM_ENGINES_PKG.TECH_MESSAGE
5713: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
5708: ,v_slc_val);
5709: FEM_ENGINES_PKG.TECH_MESSAGE
5710: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
5711: ,v_slc_val2);
5712: FEM_ENGINES_PKG.TECH_MESSAGE
5713: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
5714: ,v_slc_val3);
5715: FEM_ENGINES_PKG.TECH_MESSAGE
5716: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
5711: ,v_slc_val2);
5712: FEM_ENGINES_PKG.TECH_MESSAGE
5713: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
5714: ,v_slc_val3);
5715: FEM_ENGINES_PKG.TECH_MESSAGE
5716: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
5717: ,v_slc_val4);
5718:
5719:
5779: ----------------------------------------------------------
5780: -- Count the error rows
5781: ----------------------------------------------------------
5782: v_temp_rows_rejected := v_temp_rows_rejected + 1;
5783: FEM_ENGINES_PKG.TECH_MESSAGE
5784: (c_log_level_1,c_block||'.'||c_proc_name||'.v_temp_rows_rejected1',v_temp_rows_rejected);
5785: END IF; -- seq_conflict_count
5786:
5787: IF (p_dimension_varchar_label = 'CAL_PERIOD') THEN
5812: ----------------------------------------------------------
5813: -- Count the error rows
5814: ----------------------------------------------------------
5815: v_temp_rows_rejected := v_temp_rows_rejected + 1;
5816: FEM_ENGINES_PKG.TECH_MESSAGE
5817: (c_log_level_1,c_block||'.'||c_proc_name||'.v_temp_rows_rejected2',v_temp_rows_rejected);
5818: END IF; -- validation on dimgrp being not null
5819:
5820: -- Get count of sequence enforced hiers in which the member participates
5836: ----------------------------------------------------------
5837: -- Count the error rows
5838: ----------------------------------------------------------
5839: v_temp_rows_rejected := v_temp_rows_rejected + 1;
5840: FEM_ENGINES_PKG.TECH_MESSAGE
5841: (c_log_level_1,c_block||'.'||c_proc_name||'.v_temp_rows_rejected3',v_temp_rows_rejected);
5842: ELSE -- update the dimension_group
5843: IF p_value_set_required_flag = 'Y' THEN
5844: EXECUTE IMMEDIATE x_update_dimgrp_stmt
5860: END IF; -- not CAL_PERIOD
5861: END IF; -- dimensions that use_groups
5862: END IF; -- Load_Type 'DIMENSION_GROUP' v.s. 'DIMENSION_MEMBER'
5863: END LOOP;
5864: FEM_ENGINES_PKG.TECH_MESSAGE
5865: (c_log_level_1,c_block||'.'||c_proc_name||'.v_temp_rows_rejected4',v_temp_rows_rejected);
5866:
5867: ---------------------------------------------------------
5868: -- Update member records in the _B table for Rejected records
5866:
5867: ---------------------------------------------------------
5868: -- Update member records in the _B table for Rejected records
5869: ---------------------------------------------------------
5870: FEM_ENGINES_PKG.TECH_MESSAGE
5871: (c_log_level_1,c_block||'.'||c_proc_name||'.updating mbr status','start');
5872: FORALL i IN 1..v_mbr_last_row
5873: EXECUTE IMMEDIATE x_update_mbr_status_stmt
5874: USING t_b_status(i)
5887: ---------------------------------------------------------
5888: -- Bug#3906218 NEED ABILITY TO UNDELETE DIMENSIONS
5889: -- Perform the Enabled Flag update on the B table
5890: ---------------------------------------------------------
5891: FEM_ENGINES_PKG.TECH_MESSAGE
5892: (c_log_level_1,c_block||'.'||c_proc_name||'.updating enabled flag','start');
5893:
5894: IF (p_value_set_required_flag = 'Y') THEN
5895: FORALL i IN 1..v_mbr_last_row
5911: -- Count the error and loaded rows
5912: ----------------------------------------------------------
5913: v_rows_rejected := v_rows_rejected + v_temp_rows_rejected;
5914: v_rows_loaded := v_rows_loaded + (v_mbr_last_row - v_temp_rows_rejected);
5915: FEM_ENGINES_PKG.TECH_MESSAGE
5916: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_rejected',v_rows_rejected);
5917:
5918: --x_rows_loaded := x_rows_loaded + (v_mbr_last_row - v_temp_rows_rejected);
5919: v_temp_rows_rejected := 0; -- initialize for next pass of the loop
5956: CLOSE cv_get_remain_mbr;
5957: END IF;
5958:
5959: --x_rows_rejected := v_rows_rejected;
5960: FEM_ENGINES_PKG.TECH_MESSAGE
5961: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
5962:
5963: EXCEPTION
5964: WHEN OTHERS THEN
5971:
5972: gv_prg_msg := sqlerrm;
5973: gv_callstack := dbms_utility.format_call_stack;
5974:
5975: FEM_ENGINES_PKG.TECH_MESSAGE
5976: (p_severity => c_log_level_6
5977: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
5978: ,p_msg_text => gv_prg_msg);
5979:
5976: (p_severity => c_log_level_6
5977: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
5978: ,p_msg_text => gv_prg_msg);
5979:
5980: FEM_ENGINES_PKG.TECH_MESSAGE
5981: (p_severity => c_log_level_6
5982: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
5983: ,p_msg_text => gv_callstack);
5984:
5981: (p_severity => c_log_level_6
5982: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
5983: ,p_msg_text => gv_callstack);
5984:
5985: FEM_ENGINES_PKG.USER_MESSAGE
5986: (p_app_name => c_fem
5987: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
5988: ,P_TOKEN1 => 'ERR_MSG'
5989: ,P_VALUE1 => gv_prg_msg);
5987: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
5988: ,P_TOKEN1 => 'ERR_MSG'
5989: ,P_VALUE1 => gv_prg_msg);
5990:
5991: -- FEM_ENGINES_PKG.USER_MESSAGE
5992: -- (p_app_name => c_fem
5993: -- ,p_msg_text => gv_prg_msg);
5994:
5995: RAISE e_main_terminate;
6286:
6287: v_session_sql varchar2(1000);
6288:
6289: BEGIN
6290: FEM_ENGINES_PKG.TECH_MESSAGE
6291: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
6292:
6293: FEM_ENGINES_PKG.TECH_MESSAGE
6294: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_final_count'
6289: BEGIN
6290: FEM_ENGINES_PKG.TECH_MESSAGE
6291: (c_log_level_2,c_block||'.'||c_proc_name||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
6292:
6293: FEM_ENGINES_PKG.TECH_MESSAGE
6294: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_final_count'
6295: ,v_attr_final_count);
6296:
6297: --x_status := 0; -- initialize status of the Attr_Assign_Update procedure
6322: x_attr_select_stmt := REPLACE(x_attr_select_stmt,'{{data_slice}}','1=1');
6323: END IF;
6324:
6325:
6326: FEM_ENGINES_PKG.TECH_MESSAGE
6327: (c_log_level_1,c_block||'.'||c_proc_name||'.attr select stmt'
6328: ,x_attr_select_stmt);
6329:
6330: build_does_attr_exist_stmt (p_target_attr_table
6335: ,'Y'
6336: ,x_does_attr_exist_stmt);
6337:
6338:
6339: FEM_ENGINES_PKG.TECH_MESSAGE
6340: (c_log_level_1,c_block||'.'||c_proc_name||'.does attr exist for version stmt'
6341: ,x_does_attr_exist_stmt);
6342:
6343:
6363: ,p_member_col
6364: ,p_value_set_required_flag
6365: ,x_attr_update_stmt);
6366:
6367: FEM_ENGINES_PKG.TECH_MESSAGE
6368: (c_log_level_1,c_block||'.'||c_proc_name||'.attr update stmt'
6369: ,x_attr_update_stmt);
6370:
6371: build_delete_stmt (p_source_attr_table
6381: ,p_member_dc_col
6382: ,p_value_set_required_flag
6383: ,x_insert_attr_stmt);
6384:
6385: FEM_ENGINES_PKG.TECH_MESSAGE
6386: (c_log_level_1,c_block||'.'||c_proc_name||'.insert attr stmt'
6387: ,x_insert_attr_stmt);
6388:
6389: build_status_update_stmt (p_source_attr_table
6410: x_part_name => v_part_name,
6411: p_req_id => p_master_request_id,
6412: p_proc_num => p_proc_num);
6413:
6414: FEM_ENGINES_PKG.TECH_MESSAGE
6415: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
6416: ,v_slc_val);
6417: FEM_ENGINES_PKG.TECH_MESSAGE
6418: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
6413:
6414: FEM_ENGINES_PKG.TECH_MESSAGE
6415: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
6416: ,v_slc_val);
6417: FEM_ENGINES_PKG.TECH_MESSAGE
6418: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
6419: ,v_slc_val2);
6420: FEM_ENGINES_PKG.TECH_MESSAGE
6421: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
6416: ,v_slc_val);
6417: FEM_ENGINES_PKG.TECH_MESSAGE
6418: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
6419: ,v_slc_val2);
6420: FEM_ENGINES_PKG.TECH_MESSAGE
6421: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
6422: ,v_slc_val3);
6423: FEM_ENGINES_PKG.TECH_MESSAGE
6424: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
6419: ,v_slc_val2);
6420: FEM_ENGINES_PKG.TECH_MESSAGE
6421: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
6422: ,v_slc_val3);
6423: FEM_ENGINES_PKG.TECH_MESSAGE
6424: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
6425: ,v_slc_val4);
6426:
6427: EXIT WHEN (v_slc_id IS NULL);
6477: IF (v_attr_last_row IS NULL) THEN
6478: EXIT;
6479: END IF;
6480:
6481: FEM_ENGINES_PKG.TECH_MESSAGE
6482: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_last_row'
6483: ,v_attr_last_row);
6484:
6485: FOR j IN 1..v_attr_last_row
6484:
6485: FOR j IN 1..v_attr_last_row
6486: LOOP
6487:
6488: FEM_ENGINES_PKG.TECH_MESSAGE
6489: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_label'
6490: ,ta_attribute_varchar_label(j));
6491:
6492: FEM_ENGINES_PKG.TECH_MESSAGE
6488: FEM_ENGINES_PKG.TECH_MESSAGE
6489: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_label'
6490: ,ta_attribute_varchar_label(j));
6491:
6492: FEM_ENGINES_PKG.TECH_MESSAGE
6493: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_assign_value'
6494: ,ta_attribute_assign_value(j));
6495:
6496: FEM_ENGINES_PKG.TECH_MESSAGE
6492: FEM_ENGINES_PKG.TECH_MESSAGE
6493: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_assign_value'
6494: ,ta_attribute_assign_value(j));
6495:
6496: FEM_ENGINES_PKG.TECH_MESSAGE
6497: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_value_column_name'
6498: ,ta_attr_value_column_name(j));
6499:
6500: FEM_ENGINES_PKG.TECH_MESSAGE
6496: FEM_ENGINES_PKG.TECH_MESSAGE
6497: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_value_column_name'
6498: ,ta_attr_value_column_name(j));
6499:
6500: FEM_ENGINES_PKG.TECH_MESSAGE
6501: (c_log_level_1,c_block||'.'||c_proc_name||'.use_interim_table_flag'
6502: ,ta_use_interim_table_flag(j));
6503:
6504: -- Bug#4429725
6522: ,ta_calpattr_dimgrp_dc(j)
6523: ,ta_calpattr_end_date(j)
6524: ,ta_calpattr_period_num(j));
6525: END IF;
6526: FEM_ENGINES_PKG.TECH_MESSAGE
6527: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_assign_value - post conv'
6528: ,ta_attribute_assign_value(j));
6529:
6530: FEM_ENGINES_PKG.TECH_MESSAGE
6526: FEM_ENGINES_PKG.TECH_MESSAGE
6527: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_assign_value - post conv'
6528: ,ta_attribute_assign_value(j));
6529:
6530: FEM_ENGINES_PKG.TECH_MESSAGE
6531: (c_log_level_1,c_block||'.'||c_proc_name||'.status post conv'
6532: ,ta_status(j));
6533:
6534: -----------------------------------------
6538: ,ta_attribute_varchar_label(j)
6539: ,ta_version_display_code(j)
6540: ,ta_version_id(j));
6541:
6542: FEM_ENGINES_PKG.TECH_MESSAGE
6543: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute version id'
6544: ,ta_version_id(j));
6545:
6546: ----------------------------------------------------------
6793: ' <= :b_new_end_date';
6794:
6795: **************************************************/
6796:
6797: FEM_ENGINES_PKG.TECH_MESSAGE
6798: (c_log_level_1,c_block||'.'||c_proc_name||'.overlap_sql_stmt3'
6799: ,x_overlap_sql_stmt);
6800:
6801: EXECUTE IMMEDIATE x_overlap_sql_stmt INTO v_overlap_count
6808: ,ta_cal_period_end_date(j);
6809:
6810: IF v_overlap_count > 0 THEN
6811: ta_status(j) := 'OVERLAP_EXIST_START_DATE';
6812: FEM_ENGINES_PKG.TECH_MESSAGE
6813: (c_log_level_1,c_block||'.'||c_proc_name||'.overlap_count'
6814: ,v_overlap_count);
6815:
6816: END IF;
6853: ' >= '''||to_char(ta_date_assign_value(j),p_date_format_mask)||''''||
6854: ' and to_char(CS.date_assign_value,''j'')'||
6855: ' <= '||to_char(substr(ta_member_dc(j),1,7));
6856:
6857: FEM_ENGINES_PKG.TECH_MESSAGE
6858: (c_log_level_1,c_block||'.'||c_proc_name||'.overlap_sql_stmt4'
6859: ,x_overlap_sql_stmt);
6860: EXECUTE IMMEDIATE x_overlap_sql_stmt INTO v_overlap_count;
6861:
6937: ELSE -- Assignment is NULL or Assignment Column not valid
6938: ta_status(j) := 'INVALID_ATTRIBUTE_ASSIGNMENT';
6939:
6940: END IF; -- Main IF on validating the attributes
6941: FEM_ENGINES_PKG.TECH_MESSAGE
6942: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_status'
6943: ,ta_status(j));
6944:
6945: -----------------------------------------------------------------
6944:
6945: -----------------------------------------------------------------
6946: -- Check to see if the attribute row already exists in the _ATTR table
6947: -----------------------------------------------------------------
6948: FEM_ENGINES_PKG.TECH_MESSAGE
6949: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_id'
6950: ,ta_attribute_id(j));
6951:
6952: FEM_ENGINES_PKG.TECH_MESSAGE
6948: FEM_ENGINES_PKG.TECH_MESSAGE
6949: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_id'
6950: ,ta_attribute_id(j));
6951:
6952: FEM_ENGINES_PKG.TECH_MESSAGE
6953: (c_log_level_1,c_block||'.'||c_proc_name||'.version_id'
6954: ,ta_version_id(j));
6955:
6956: FEM_ENGINES_PKG.TECH_MESSAGE
6952: FEM_ENGINES_PKG.TECH_MESSAGE
6953: (c_log_level_1,c_block||'.'||c_proc_name||'.version_id'
6954: ,ta_version_id(j));
6955:
6956: FEM_ENGINES_PKG.TECH_MESSAGE
6957: (c_log_level_1,c_block||'.'||c_proc_name||'.member display code'
6958: ,ta_member_dc(j));
6959:
6960: FEM_ENGINES_PKG.TECH_MESSAGE
6956: FEM_ENGINES_PKG.TECH_MESSAGE
6957: (c_log_level_1,c_block||'.'||c_proc_name||'.member display code'
6958: ,ta_member_dc(j));
6959:
6960: FEM_ENGINES_PKG.TECH_MESSAGE
6961: (c_log_level_1,c_block||'.'||c_proc_name||'.value_set display code'
6962: ,ta_value_set_dc(j));
6963:
6964: FEM_ENGINES_PKG.TECH_MESSAGE
6960: FEM_ENGINES_PKG.TECH_MESSAGE
6961: (c_log_level_1,c_block||'.'||c_proc_name||'.value_set display code'
6962: ,ta_value_set_dc(j));
6963:
6964: FEM_ENGINES_PKG.TECH_MESSAGE
6965: (c_log_level_1,c_block||'.'||c_proc_name||'.value_set_id'
6966: ,ta_value_set_id(j));
6967:
6968: FEM_ENGINES_PKG.TECH_MESSAGE
6964: FEM_ENGINES_PKG.TECH_MESSAGE
6965: (c_log_level_1,c_block||'.'||c_proc_name||'.value_set_id'
6966: ,ta_value_set_id(j));
6967:
6968: FEM_ENGINES_PKG.TECH_MESSAGE
6969: (c_log_level_1,c_block||'.'||c_proc_name||'.member_id'
6970: ,ta_member_id(j));
6971:
6972:
7032: ,ta_attr_value_column_name(j)
7033: ,ta_attr_assign_vs_id(j)
7034: ,x_does_multattr_exist_stmt);
7035:
7036: FEM_ENGINES_PKG.TECH_MESSAGE
7037: (c_log_level_1,c_block||'.'||c_proc_name||'.does multattr vs exist stmt'
7038: ,x_does_multattr_exist_stmt);
7039:
7040: IF ta_attr_value_column_name(j) = 'DIM_ATTRIBUTE_NUMERIC_MEMBER'
7082: ,ta_attr_value_column_name(j)
7083: ,ta_attr_assign_vs_id(j)
7084: ,x_does_multattr_exist_stmt);
7085:
7086: FEM_ENGINES_PKG.TECH_MESSAGE
7087: (c_log_level_1,c_block||'.'||c_proc_name||'.does multattr exist stmt'
7088: ,x_does_multattr_exist_stmt);
7089:
7090: IF ta_attr_value_column_name(j) = 'DIM_ATTRIBUTE_NUMERIC_MEMBER'
7121: ELSE
7122: ta_attr_exists_count(j) := 0;
7123: ta_multattr_exists_count(j) := 0;
7124: END IF; -- value_set_required
7125: FEM_ENGINES_PKG.TECH_MESSAGE
7126: (c_log_level_1,c_block||'.'||c_proc_name||'.does_attr_exist'
7127: ,null);
7128:
7129: ---------------------------------------------------
7159: -- to go ahead and let it update.
7160: -- If it is not identical, we want to mark the row with an ERROR status
7161: ----------------------------------------------------------------------
7162: IF (ta_status(j) = 'LOAD') AND (ta_read_only_flag(j) = 'Y') THEN
7163: FEM_ENGINES_PKG.TECH_MESSAGE
7164: (c_log_level_1,c_block||'.'||c_proc_name||'.identical attr select stmt'
7165: ,x_identical_attr_select_stmt);
7166:
7167: FEM_ENGINES_PKG.TECH_MESSAGE
7163: FEM_ENGINES_PKG.TECH_MESSAGE
7164: (c_log_level_1,c_block||'.'||c_proc_name||'.identical attr select stmt'
7165: ,x_identical_attr_select_stmt);
7166:
7167: FEM_ENGINES_PKG.TECH_MESSAGE
7168: (c_log_level_1,c_block||'.'||c_proc_name||'.dim_attr_numeric'
7169: ,ta_dim_attr_numeric_member(j));
7170: FEM_ENGINES_PKG.TECH_MESSAGE
7171: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_assign_vs'
7166:
7167: FEM_ENGINES_PKG.TECH_MESSAGE
7168: (c_log_level_1,c_block||'.'||c_proc_name||'.dim_attr_numeric'
7169: ,ta_dim_attr_numeric_member(j));
7170: FEM_ENGINES_PKG.TECH_MESSAGE
7171: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_assign_vs'
7172: ,ta_attr_assign_vs_id(j));
7173: FEM_ENGINES_PKG.TECH_MESSAGE
7174: (c_log_level_1,c_block||'.'||c_proc_name||'.dim_attr_varchar'
7169: ,ta_dim_attr_numeric_member(j));
7170: FEM_ENGINES_PKG.TECH_MESSAGE
7171: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_assign_vs'
7172: ,ta_attr_assign_vs_id(j));
7173: FEM_ENGINES_PKG.TECH_MESSAGE
7174: (c_log_level_1,c_block||'.'||c_proc_name||'.dim_attr_varchar'
7175: ,ta_dim_attr_varchar_member(j));
7176: FEM_ENGINES_PKG.TECH_MESSAGE
7177: (c_log_level_1,c_block||'.'||c_proc_name||'.number_assign'
7172: ,ta_attr_assign_vs_id(j));
7173: FEM_ENGINES_PKG.TECH_MESSAGE
7174: (c_log_level_1,c_block||'.'||c_proc_name||'.dim_attr_varchar'
7175: ,ta_dim_attr_varchar_member(j));
7176: FEM_ENGINES_PKG.TECH_MESSAGE
7177: (c_log_level_1,c_block||'.'||c_proc_name||'.number_assign'
7178: ,ta_number_assign_value(j));
7179: FEM_ENGINES_PKG.TECH_MESSAGE
7180: (c_log_level_1,c_block||'.'||c_proc_name||'.varchar_assign'
7175: ,ta_dim_attr_varchar_member(j));
7176: FEM_ENGINES_PKG.TECH_MESSAGE
7177: (c_log_level_1,c_block||'.'||c_proc_name||'.number_assign'
7178: ,ta_number_assign_value(j));
7179: FEM_ENGINES_PKG.TECH_MESSAGE
7180: (c_log_level_1,c_block||'.'||c_proc_name||'.varchar_assign'
7181: ,ta_varchar_assign_value(j));
7182: FEM_ENGINES_PKG.TECH_MESSAGE
7183: (c_log_level_1,c_block||'.'||c_proc_name||'.date_assign'
7178: ,ta_number_assign_value(j));
7179: FEM_ENGINES_PKG.TECH_MESSAGE
7180: (c_log_level_1,c_block||'.'||c_proc_name||'.varchar_assign'
7181: ,ta_varchar_assign_value(j));
7182: FEM_ENGINES_PKG.TECH_MESSAGE
7183: (c_log_level_1,c_block||'.'||c_proc_name||'.date_assign'
7184: ,to_char(ta_temp_date_assign_value(j),'YYYY/MM/DD'));
7185:
7186: -- we use a temp variable to handle the case where
7237: ,ta_temp_date_assign_value(j)
7238: ,ta_temp_date_assign_value(j);
7239: END IF; -- value_set_required
7240:
7241: FEM_ENGINES_PKG.TECH_MESSAGE
7242: (c_log_level_1,c_block||'.'||c_proc_name||'.identical_count'
7243: ,ta_attr_identical_count(j));
7244:
7245: IF ta_attr_identical_count(j) = 0 THEN
7260: IF ta_status(j) <> 'LOAD' THEN
7261: v_temp_rows_rejected := v_temp_rows_rejected + 1;
7262: END IF;
7263:
7264: FEM_ENGINES_PKG.TECH_MESSAGE
7265: (c_log_level_1,c_block||'.'||c_proc_name||'.v_temp_rows_rejected'
7266: ,v_temp_rows_rejected);
7267:
7268: END LOOP; -- attr_validations
7265: (c_log_level_1,c_block||'.'||c_proc_name||'.v_temp_rows_rejected'
7266: ,v_temp_rows_rejected);
7267:
7268: END LOOP; -- attr_validations
7269: FEM_ENGINES_PKG.TECH_MESSAGE
7270: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_validation'
7271: ,'end');
7272:
7273: /*********************************************************
7280: IF p_dimension_varchar_label = 'CAL_PERIOD' THEN
7281: v_attr_count := 1;
7282: WHILE v_attr_count <= v_attr_last_row
7283: LOOP
7284: FEM_ENGINES_PKG.TECH_MESSAGE
7285: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_varchar_label'
7286: ,ta_attribute_varchar_label(v_attr_count));
7287:
7288: FEM_ENGINES_PKG.TECH_MESSAGE
7284: FEM_ENGINES_PKG.TECH_MESSAGE
7285: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_varchar_label'
7286: ,ta_attribute_varchar_label(v_attr_count));
7287:
7288: FEM_ENGINES_PKG.TECH_MESSAGE
7289: (c_log_level_1,c_block||'.'||c_proc_name||'.status'
7290: ,ta_status(v_attr_count));
7291:
7292: FEM_ENGINES_PKG.TECH_MESSAGE
7288: FEM_ENGINES_PKG.TECH_MESSAGE
7289: (c_log_level_1,c_block||'.'||c_proc_name||'.status'
7290: ,ta_status(v_attr_count));
7291:
7292: FEM_ENGINES_PKG.TECH_MESSAGE
7293: (c_log_level_1,c_block||'.'||c_proc_name||'.adj_period_flag'
7294: ,ta_adj_period_flag(v_attr_count));
7295:
7296: v_attr_subcount := v_attr_count + 1; -- we always check 1 lower than
7438:
7439: ----------------------------------------------------------------
7440: -- Delete records that successfully updated
7441: ----------------------------------------------------------------
7442: FEM_ENGINES_PKG.TECH_MESSAGE
7443: (c_log_level_1,c_block||'.'||c_proc_name||'.prior_to_special_delete'
7444: ,null);
7445:
7446: FORALL j IN 1..v_attr_last_row
7469: -----------------------------------------------------------------
7470: v_attr_last_row := ta_attribute_id.LAST;
7471: v_attr_count := 1;
7472:
7473: FEM_ENGINES_PKG.TECH_MESSAGE
7474: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_final_count'
7475: ,v_attr_final_count);
7476: FEM_ENGINES_PKG.TECH_MESSAGE
7477: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_count'
7472:
7473: FEM_ENGINES_PKG.TECH_MESSAGE
7474: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_final_count'
7475: ,v_attr_final_count);
7476: FEM_ENGINES_PKG.TECH_MESSAGE
7477: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_count'
7478: ,v_attr_count);
7479: FEM_ENGINES_PKG.TECH_MESSAGE
7480: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_last_row'
7475: ,v_attr_final_count);
7476: FEM_ENGINES_PKG.TECH_MESSAGE
7477: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_count'
7478: ,v_attr_count);
7479: FEM_ENGINES_PKG.TECH_MESSAGE
7480: (c_log_level_1,c_block||'.'||c_proc_name||'.attr_last_row'
7481: ,v_attr_last_row);
7482:
7483:
7509: END IF; -- Copy ATTR for good members
7510: v_attr_count := v_attr_count + 1;
7511: END LOOP; -- WHILE loop
7512:
7513: FEM_ENGINES_PKG.TECH_MESSAGE
7514: (c_log_level_1,c_block||'.'||c_proc_name||'.after_the_copy'
7515: ,null);
7516:
7517: FEM_ENGINES_PKG.TECH_MESSAGE
7513: FEM_ENGINES_PKG.TECH_MESSAGE
7514: (c_log_level_1,c_block||'.'||c_proc_name||'.after_the_copy'
7515: ,null);
7516:
7517: FEM_ENGINES_PKG.TECH_MESSAGE
7518: (c_log_level_1,c_block||'.'||c_proc_name||'.attribute_final_count'
7519: ,v_attr_final_count);
7520:
7521: ----------------------------------------------------------
7526: USING ta_status(j)
7527: ,ta_rowid(j)
7528: ,ta_status(j);
7529:
7530: FEM_ENGINES_PKG.TECH_MESSAGE
7531: (c_log_level_1,c_block||'.'||c_proc_name||'.after_status_update'
7532: ,null);
7533:
7534: ---------------------------------------------------------
7573: v_rows_rejected := v_rows_rejected + v_temp_rows_rejected;
7574: --x_rows_loaded := x_rows_loaded + (v_attr_last_row - v_temp_rows_rejected);
7575: v_temp_rows_rejected := 0; -- initialize for next pass of the loop
7576:
7577: FEM_ENGINES_PKG.TECH_MESSAGE
7578: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_rejected'
7579: ,v_rows_rejected);
7580:
7581: ----------------------------------------------------------------
7663: CLOSE cv_get_attr_rows;
7664: END IF;
7665: --x_rows_rejected := v_rows_rejected;
7666:
7667: FEM_ENGINES_PKG.TECH_MESSAGE
7668: (c_log_level_2,c_block||'.'||c_proc_name||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
7669:
7670: EXCEPTION
7671: WHEN OTHERS THEN
7678:
7679: gv_prg_msg := sqlerrm;
7680: gv_callstack := dbms_utility.format_call_stack;
7681:
7682: FEM_ENGINES_PKG.TECH_MESSAGE
7683: (p_severity => c_log_level_6
7684: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
7685: ,p_msg_text => gv_prg_msg);
7686:
7683: (p_severity => c_log_level_6
7684: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
7685: ,p_msg_text => gv_prg_msg);
7686:
7687: FEM_ENGINES_PKG.TECH_MESSAGE
7688: (p_severity => c_log_level_6
7689: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
7690: ,p_msg_text => gv_callstack);
7691:
7688: (p_severity => c_log_level_6
7689: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
7690: ,p_msg_text => gv_callstack);
7691:
7692: FEM_ENGINES_PKG.USER_MESSAGE
7693: (p_app_name => c_fem
7694: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
7695: ,P_TOKEN1 => 'ERR_MSG'
7696: ,P_VALUE1 => gv_prg_msg);
7695: ,P_TOKEN1 => 'ERR_MSG'
7696: ,P_VALUE1 => gv_prg_msg);
7697:
7698:
7699: -- FEM_ENGINES_PKG.USER_MESSAGE
7700: -- (p_app_name => c_fem
7701: -- ,p_msg_text => gv_prg_msg);
7702:
7703: RAISE e_main_terminate;
7873:
7874:
7875: BEGIN
7876:
7877: FEM_ENGINES_PKG.TECH_MESSAGE
7878: (c_log_level_2,c_block||'.'||'get_dimension_info','Begin');
7879:
7880: SELECT dimension_id
7881: ,member_b_table_name
7950: x_hier_dimension_flag := 'N';
7951: END IF;
7952: *******************/
7953:
7954: FEM_ENGINES_PKG.TECH_MESSAGE
7955: (c_log_level_2,c_block||'.'||'get_dimension_info','End');
7956:
7957: EXCEPTION
7958: WHEN no_data_found THEN
8051: v_dimgrp_load_col VARCHAR2(1000);
8052:
8053: BEGIN
8054:
8055: FEM_ENGINES_PKG.TECH_MESSAGE
8056: (c_log_level_2,c_block||'.'||'build_mbr_select_stmt','Begin Build member select statement');
8057:
8058: IF p_value_set_required_flag = 'Y' THEN
8059:
8226: v_vs_where_cond;
8227:
8228: END IF;
8229:
8230: FEM_ENGINES_PKG.TECH_MESSAGE
8231: (c_log_level_2,c_block||'.'||'build_mbr_select_stmt','End');
8232:
8233: END build_mbr_select_stmt;
8234:
8265: -- dimension grp TL table
8266:
8267: BEGIN
8268:
8269: FEM_ENGINES_PKG.TECH_MESSAGE
8270: (c_log_level_2,c_block||'.'||'build_bad_tl_lang_upd_stmt','Begin');
8271:
8272: IF (p_load_type = 'DIMENSION_GROUP')
8273: OR (p_shared_dimension_flag ='Y' AND p_value_set_required_flag = 'N') THEN
8286: ' AND B.status'||p_exec_mode_clause||
8287: ' AND {{data_slice}} ';
8288:
8289:
8290: FEM_ENGINES_PKG.TECH_MESSAGE
8291: (c_log_level_2,c_block||'.'||'build_bad_tl_lang_upd_stmt','End');
8292:
8293:
8294: END build_bad_lang_upd_stmt;
8334: v_calp_from_cond VARCHAR2(1000);
8335:
8336: BEGIN
8337:
8338: FEM_ENGINES_PKG.TECH_MESSAGE
8339: (c_log_level_2,c_block||'.'||'build_tl_ro_mbr_upd_stmt','Begin');
8340:
8341: IF (p_load_type = 'DIMENSION_GROUP')
8342: OR (p_shared_dimension_flag ='Y' AND p_value_set_required_flag = 'N') THEN
8399: ' AND {{data_slice}} ';
8400:
8401: END IF;
8402:
8403: FEM_ENGINES_PKG.TECH_MESSAGE
8404: (c_log_level_2,c_block||'.'||'build_tl_ro_mbr_upd_stmt','End');
8405:
8406:
8407: END build_tl_ro_mbr_upd_stmt;
8494: v_dimgrp_load_col VARCHAR2(1000);
8495:
8496: BEGIN
8497:
8498: FEM_ENGINES_PKG.TECH_MESSAGE
8499: (c_log_level_2,c_block||'.'||'build_bad_tl_select_stmt','Begin');
8500:
8501: IF p_value_set_required_flag = 'Y' THEN
8502:
8618: v_vs_subq_where_condB2||')'||
8619: v_dimension_grp_where_cond||
8620: v_calendar_where_cond;
8621:
8622: FEM_ENGINES_PKG.TECH_MESSAGE
8623: (c_log_level_2,c_block||'.'||'build_bad_TL_select_stmt','End');
8624:
8625: END build_bad_tl_select_stmt;
8626:
8733: v_truncate_stmt VARCHAR2(4000);
8734:
8735: BEGIN
8736:
8737: FEM_ENGINES_PKG.TECH_MESSAGE
8738: (c_log_level_2,c_block||'.'||'Truncate_calp_interim'||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
8739:
8740: v_truncate_stmt := 'delete from fem_calp_interim_t';
8741: EXECUTE IMMEDIATE v_truncate_stmt;
8745: EXECUTE IMMEDIATE v_truncate_stmt;
8746:
8747: commit;
8748:
8749: FEM_ENGINES_PKG.TECH_MESSAGE
8750: (c_log_level_2,c_block||'.'||'Truncate_calp_interim'||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
8751:
8752:
8753: END truncate_calp_interim;
8898: cv_get_rows cv_curs;
8899:
8900: BEGIN
8901:
8902: FEM_ENGINES_PKG.TECH_MESSAGE
8903: (c_log_level_2,c_block||'.'||'Post_Cal_Periods'||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
8904:
8905: FEM_ENGINES_PKG.TECH_MESSAGE
8906: (c_log_level_1,c_block||'.'||'Post_Cal_Periods'||'.p_operation_mode',p_operation_mode);
8901:
8902: FEM_ENGINES_PKG.TECH_MESSAGE
8903: (c_log_level_2,c_block||'.'||'Post_Cal_Periods'||'.Begin',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
8904:
8905: FEM_ENGINES_PKG.TECH_MESSAGE
8906: (c_log_level_1,c_block||'.'||'Post_Cal_Periods'||'.p_operation_mode',p_operation_mode);
8907:
8908: --x_status := 0; -- initialize status of the New_Members procedure
8909: --x_message := 'COMPLETE:NORMAL';
8937: ELSE
8938: x_select_mbr_stmt := REPLACE(x_select_mbr_stmt,'{{data_slice}}','1=1');
8939: END IF;
8940: ----------------------------------------------------------------------
8941: FEM_ENGINES_PKG.TECH_MESSAGE
8942: (c_log_level_1,c_block||'.'||c_proc_name||'.select_mbr_stmt'
8943: ,x_select_mbr_stmt);
8944:
8945: IF p_operation_mode = 'NEW_MEMBERS' THEN
8961: ',x_last_update_date => sysdate '||
8962: ',x_last_updated_by => :b_apps_user_id2'||
8963: ',x_last_update_login => null ); END;';
8964:
8965: FEM_ENGINES_PKG.TECH_MESSAGE
8966: (c_log_level_1,c_block||'.'||c_proc_name||'.insert_mbr_stmt'
8967: ,x_insert_mbr_stmt);
8968:
8969: x_insert_attr_stmt := 'INSERT INTO FEM_CAL_PERIODS_ATTR '||
9000: ', 1'||
9001: ', ''N'''||
9002: ' FROM fem_calp_attr_interim_t'||
9003: ' WHERE cal_period_id = :b_cal_period_id';
9004: FEM_ENGINES_PKG.TECH_MESSAGE
9005: (c_log_level_1,c_block||'.'||c_proc_name||'.insert_attr_stmt'
9006: ,x_insert_attr_stmt);
9007:
9008: build_calp_delete_stmt ('FEM_CAL_PERIODS_B_T'
9027: ' WHERE cal_period_id = :b_cal_period_id'||
9028: ' AND attribute_id = :b_attribute_id'||
9029: ' AND version_id = :b_version_id';
9030:
9031: FEM_ENGINES_PKG.TECH_MESSAGE
9032: (c_log_level_1,c_block||'.'||c_proc_name||'.update_attr_stmt'
9033: ,x_update_attr_stmt);
9034:
9035: build_calp_delete_stmt ('FEM_CAL_PERIODS_ATTR_T'
9035: build_calp_delete_stmt ('FEM_CAL_PERIODS_ATTR_T'
9036: ,p_operation_mode
9037: ,x_delete_attr_stmt);
9038:
9039: FEM_ENGINES_PKG.TECH_MESSAGE
9040: (c_log_level_1,c_block||'.'||c_proc_name||'.delete_attr_stmt'
9041: ,x_delete_attr_stmt);
9042:
9043: -- identify the attribute_id and default version for the START_DATE attribute
9068: x_part_name => v_part_name,
9069: p_req_id => p_master_request_id,
9070: p_proc_num => p_proc_num);
9071:
9072: FEM_ENGINES_PKG.TECH_MESSAGE
9073: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
9074: ,v_slc_val);
9075: FEM_ENGINES_PKG.TECH_MESSAGE
9076: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
9071:
9072: FEM_ENGINES_PKG.TECH_MESSAGE
9073: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val'
9074: ,v_slc_val);
9075: FEM_ENGINES_PKG.TECH_MESSAGE
9076: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
9077: ,v_slc_val2);
9078: FEM_ENGINES_PKG.TECH_MESSAGE
9079: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
9074: ,v_slc_val);
9075: FEM_ENGINES_PKG.TECH_MESSAGE
9076: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val2'
9077: ,v_slc_val2);
9078: FEM_ENGINES_PKG.TECH_MESSAGE
9079: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
9080: ,v_slc_val3);
9081: FEM_ENGINES_PKG.TECH_MESSAGE
9082: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
9077: ,v_slc_val2);
9078: FEM_ENGINES_PKG.TECH_MESSAGE
9079: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val3'
9080: ,v_slc_val3);
9081: FEM_ENGINES_PKG.TECH_MESSAGE
9082: (c_log_level_1,c_block||'.'||c_proc_name||'.get_data_slice.slc_val4'
9083: ,v_slc_val4);
9084:
9085:
9236: IF cv_get_rows%ISOPEN THEN
9237: CLOSE cv_get_rows;
9238: END IF;
9239:
9240: FEM_ENGINES_PKG.TECH_MESSAGE
9241: (c_log_level_2,c_block||'.'||'Post_Cal_Periods'||'.End',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
9242:
9243: END Post_Cal_Periods;
9244:
9287:
9288: v_dimlabel_cond VARCHAR2(1000);
9289:
9290: BEGIN
9291: FEM_ENGINES_PKG.TECH_MESSAGE
9292: (c_log_level_2,c_block||'.'||
9293: 'build_enable_update_stmt','Begin Build Enabled flag update statement');
9294:
9295: IF p_load_type = 'DIMENSION_GROUP' THEN
9344: ')'||
9345: ' AND :b_t_a_status = ''LOAD''';
9346:
9347: END IF;
9348: FEM_ENGINES_PKG.TECH_MESSAGE
9349: (c_log_level_2,c_block||'.'||
9350: 'build_enable_update_stmt','End');
9351:
9352: END build_enable_update_stmt;
9385: v_dim_id_where_cond VARCHAR2(1000);
9386: v_vs_where_cond VARCHAR2(1000);
9387:
9388: BEGIN
9389: FEM_ENGINES_PKG.TECH_MESSAGE
9390: (c_log_level_2,c_block||'.'||
9391: 'build_tl_dupname_stmt','Begin Build select statement for dupname check');
9392:
9393: IF p_load_type IN ('DIMENSION_GROUP') THEN
9454: END IF;
9455:
9456: END IF; -- cal_period
9457:
9458: FEM_ENGINES_PKG.TECH_MESSAGE
9459: (c_log_level_2,c_block||'.'||
9460: 'build_tl_dupname_stmt','End');
9461:
9462:
9512:
9513: v_dim_id_where_cond VARCHAR2(1000);
9514:
9515: BEGIN
9516: FEM_ENGINES_PKG.TECH_MESSAGE
9517: (c_log_level_2,c_block||'.'||
9518: 'build_tl_update_stmt','Begin Build update statement for TL table');
9519:
9520: IF p_load_type IN ('DIMENSION_GROUP') THEN
9580: ' AND language = :b_language'||
9581: ' AND :b_status = ''LOAD''';
9582:
9583: END IF;
9584: FEM_ENGINES_PKG.TECH_MESSAGE
9585: (c_log_level_2,c_block||'.'||
9586: 'build_tl_update_stmt','End');
9587:
9588: END build_tl_update_stmt;
9642: v_dim_label_where_cond VARCHAR2(1000);
9643:
9644:
9645: BEGIN
9646: FEM_ENGINES_PKG.TECH_MESSAGE
9647: (c_log_level_2,c_block||'.'||
9648: 'build_attr_lvlspec_select_stmt',
9649: 'Begin Build select statement for level specific attribute assignments from source _ATTR_T table');
9650:
9805: END IF;
9806: END IF; -- IF Cal Period 'Y' or 'N'
9807:
9808:
9809: FEM_ENGINES_PKG.TECH_MESSAGE
9810: (c_log_level_2,c_block||'.'||
9811: 'build_attr_lvlspec_select_stmt',
9812: 'End');
9813:
9934: v_spcmbr_dim_label_where_cond VARCHAR2(1000);
9935:
9936:
9937: BEGIN
9938: FEM_ENGINES_PKG.TECH_MESSAGE
9939: (c_log_level_2,c_block||'.'||
9940: 'build_attr_select_stmt',
9941: 'Begin Build select statement for attributes from source _ATTR_T table');
9942:
10679: END IF; -- Specific member flag 'Y' or 'N'
10680: END IF; -- IF Cal Period 'Y' or 'N'
10681:
10682:
10683: FEM_ENGINES_PKG.TECH_MESSAGE
10684: (c_log_level_2,c_block||'.'||
10685: 'build_attr_select_stmt',
10686: 'End');
10687:
10787: v_dim_label_where_cond VARCHAR2(1000);
10788:
10789: BEGIN
10790:
10791: FEM_ENGINES_PKG.TECH_MESSAGE
10792: (c_log_level_2,c_block||'.'||
10793: 'build_bad_attr_select_stmt',
10794: 'Begin Build_bad_attr_select_stmt');
10795:
10934:
10935: */
10936: END IF;
10937:
10938: FEM_ENGINES_PKG.TECH_MESSAGE
10939: (c_log_level_2,c_block||'.'||
10940: 'build_bad_attr_select_stmt',
10941: 'End Build_bad_attr_select_stmt');
10942:
10965: ,x_select_stmt OUT NOCOPY VARCHAR2)
10966: IS
10967:
10968: BEGIN
10969: FEM_ENGINES_PKG.TECH_MESSAGE
10970: (c_log_level_2,c_block||'.'||'build_seq_enf_hiercount_stmt',
10971: 'Begin Build SQL statement to identify if member participates in sequence enf hier');
10972:
10973: IF p_value_set_required_flag = 'Y' THEN
10993: ' AND to_char(child_id) = to_char(:b_member_id)';
10994:
10995: END IF;
10996:
10997: FEM_ENGINES_PKG.TECH_MESSAGE
10998: (c_log_level_2,c_block||'.'||'build_seq_enf_hiercount_stmt',
10999: 'End Build SQL statement to identify if member participates in sequence enf hier');
11000:
11001: END build_seq_enf_hiercount_stmt;
11039: v_version_where_cond VARCHAR2(1000);
11040:
11041:
11042: BEGIN
11043: FEM_ENGINES_PKG.TECH_MESSAGE
11044: (c_log_level_2,c_block||'.'||
11045: 'build_does_attr_exist_stmt',
11046: 'Begin Build SQL statement to identify if attribute assignment already exists');
11047:
11073: v_version_where_cond||
11074: ' AND A.'||p_member_col||' = :b_member_id'||
11075: v_value_set_where_cond;
11076:
11077: FEM_ENGINES_PKG.TECH_MESSAGE
11078: (c_log_level_2,c_block||'.'||
11079: 'build_does_attr_exist_stmt',
11080: 'End');
11081:
11120: v_assign_where_cond VARCHAR2(4000);
11121:
11122:
11123: BEGIN
11124: FEM_ENGINES_PKG.TECH_MESSAGE
11125: (c_log_level_2,c_block||'.'||
11126: 'build_does_multattr_exist_stmt',
11127: 'Begin Build SQL statement to identify if attribute assignment already exists for multi-assign attributes');
11128:
11160: ' AND A.'||p_member_col||' = :b_member_id'||
11161: v_value_set_where_cond||
11162: v_assign_where_cond;
11163:
11164: FEM_ENGINES_PKG.TECH_MESSAGE
11165: (c_log_level_2,c_block||'.'||
11166: 'build_does_multattr_exist_stmt',
11167: 'End');
11168:
11202: v_value_set_table VARCHAR2(100);
11203:
11204:
11205: BEGIN
11206: FEM_ENGINES_PKG.TECH_MESSAGE
11207: (c_log_level_2,c_block||'.'||
11208: 'build_get_identical_assgn_stmt',
11209: 'Begin Build SQL statement to identify if existing attr assignment is identical to one from the interface table');
11210:
11254: ' OR (:b_date_assign_value IS NOT NULL '||
11255: ' AND A.date_assign_value = :b_date_assign_value))';
11256:
11257:
11258: FEM_ENGINES_PKG.TECH_MESSAGE
11259: (c_log_level_2,c_block||'.'||
11260: 'build_get_identical_assgn_stmt',
11261: 'End');
11262:
11329: x_member_exists_stmt VARCHAR2(4000);
11330:
11331:
11332: BEGIN
11333: FEM_ENGINES_PKG.TECH_MESSAGE
11334: (c_log_level_2,c_block||'.'||
11335: 'verify_attr_member',
11336: 'Begin identify if attribute assignment member is valid');
11337:
11334: (c_log_level_2,c_block||'.'||
11335: 'verify_attr_member',
11336: 'Begin identify if attribute assignment member is valid');
11337:
11338: FEM_ENGINES_PKG.TECH_MESSAGE
11339: (c_log_level_1,c_block||'.'||
11340: 'verify_attr_member.p_attr_member_vs_dc',
11341: p_attr_member_vs_dc);
11342:
11399: INTO x_member
11400: USING p_attr_member_dc;
11401: END IF;
11402:
11403: FEM_ENGINES_PKG.TECH_MESSAGE
11404: (c_log_level_1,c_block||'.'||
11405: 'verify_attr_member.after the member exists query',
11406: null);
11407:
11413: ELSE
11414: x_attr_success := 'N';
11415: END IF;
11416:
11417: FEM_ENGINES_PKG.TECH_MESSAGE
11418: (c_log_level_1,c_block||'.'||
11419: 'x_attr_sucess',
11420: x_attr_success);
11421: FEM_ENGINES_PKG.TECH_MESSAGE
11417: FEM_ENGINES_PKG.TECH_MESSAGE
11418: (c_log_level_1,c_block||'.'||
11419: 'x_attr_sucess',
11420: x_attr_success);
11421: FEM_ENGINES_PKG.TECH_MESSAGE
11422: (c_log_level_2,c_block||'.'||
11423: 'verify_attr_member',
11424: 'End');
11425:
11458: v_value_set_where_cond VARCHAR2(1000);
11459: v_value_set_table VARCHAR2(1000);
11460:
11461: BEGIN
11462: FEM_ENGINES_PKG.TECH_MESSAGE
11463: (c_log_level_2,c_block||'.'||
11464: 'build_member_exists_stmt',
11465: 'Begin Build SQL statement to identify if a dimension member exists in FEM');
11466:
11480: ' FROM '||p_member_table||' B1 '||v_value_set_table||
11481: ' WHERE to_char('||p_member_dc_col||') = to_char(:b_member_dc)'||
11482: v_value_set_where_cond;
11483:
11484: FEM_ENGINES_PKG.TECH_MESSAGE
11485: (c_log_level_1,c_block||'.'||
11486: 'build_member_exists_stmt',
11487: x_member_exists_stmt);
11488:
11485: (c_log_level_1,c_block||'.'||
11486: 'build_member_exists_stmt',
11487: x_member_exists_stmt);
11488:
11489: FEM_ENGINES_PKG.TECH_MESSAGE
11490: (c_log_level_2,c_block||'.'||
11491: 'build_member_exists_stmt',
11492: 'End');
11493:
11532:
11533: v_member_code VARCHAR2(1000); -- used only in the CAL_PERIOD select
11534:
11535: BEGIN
11536: FEM_ENGINES_PKG.TECH_MESSAGE
11537: (c_log_level_2,c_block||'.'||
11538: 'build_bad_new_members_stmt',
11539: 'Begin Build SQL statement to retrieve invalid new members from the source _B_T table');
11540:
11628:
11629: END IF;
11630:
11631:
11632: FEM_ENGINES_PKG.TECH_MESSAGE
11633: (c_log_level_2,c_block||'.'||
11634: 'build_bad_new_members_stmt',
11635: 'End');
11636:
11673: IS
11674: v_dim_label_where_cond VARCHAR2(1000);
11675:
11676: BEGIN
11677: FEM_ENGINES_PKG.TECH_MESSAGE
11678: (c_log_level_2,c_block||'.'||
11679: 'build_bad_attr_vers_stmt',
11680: 'Begin Build SQL statement to retrieve invalid attribute assignments from the source _ATTR_T table');
11681:
11703: ' WHERE A.attribute_varchar_label = B.attribute_varchar_label'||
11704: ' AND nvl(A.user_assign_allowed_flag,''Y'') not in (''N''))'||
11705: ' AND {{data_slice}} ';
11706:
11707: FEM_ENGINES_PKG.TECH_MESSAGE
11708: (c_log_level_2,c_block||'.'||
11709: 'build_bad_attr_vers_stmt',
11710: 'End');
11711:
11742: IS
11743:
11744:
11745: BEGIN
11746: FEM_ENGINES_PKG.TECH_MESSAGE
11747: (c_log_level_2,c_block||'.'||
11748: 'get_attr_version',
11749: 'Begin verify that a version display_code exists for an attribute');
11750:
11747: (c_log_level_2,c_block||'.'||
11748: 'get_attr_version',
11749: 'Begin verify that a version display_code exists for an attribute');
11750:
11751: FEM_ENGINES_PKG.TECH_MESSAGE
11752: (c_log_level_1,c_block||'.'||
11753: 'get_attr_version.attribute_varchar_label',
11754: p_attribute_varchar_label);
11755:
11752: (c_log_level_1,c_block||'.'||
11753: 'get_attr_version.attribute_varchar_label',
11754: p_attribute_varchar_label);
11755:
11756: FEM_ENGINES_PKG.TECH_MESSAGE
11757: (c_log_level_1,c_block||'.'||
11758: 'get_attr_version.version_display_code',
11759: p_version_display_code);
11760:
11766: AND A.attribute_varchar_label = p_attribute_varchar_label
11767: AND D.dimension_varchar_label = p_dimension_varchar_label
11768: AND D.dimension_id = A.dimension_id;
11769:
11770: FEM_ENGINES_PKG.TECH_MESSAGE
11771: (c_log_level_2,c_block||'.'||
11772: 'get_attr_version',
11773: 'End');
11774:
11817:
11818: IS
11819:
11820: BEGIN
11821: FEM_ENGINES_PKG.TECH_MESSAGE
11822: (c_log_level_2,c_block||'.'||
11823: 'build_insert_member_stmt',
11824: 'Begin SQL statement to insert new dimension members');
11825:
11975: ',x_last_update_login => null ); END;';
11976:
11977: END IF;
11978:
11979: FEM_ENGINES_PKG.TECH_MESSAGE
11980: (c_log_level_2,c_block||'.'||
11981: 'build_insert_member_stmt',
11982: 'End');
11983:
12024: v_value_set_where_cond VARCHAR2(1000);
12025: v_value_set_table VARCHAR2(100);
12026:
12027: BEGIN
12028: FEM_ENGINES_PKG.TECH_MESSAGE
12029: (c_log_level_2,c_block||'.'||
12030: 'build_insert_attr_stmt',
12031: 'Begin SQL statement to insert new attribute assignments');
12032:
12088: ' WHERE B1.'||p_member_dc_col||' = :b_member_dc'||
12089: v_value_set_where_cond||
12090: ' AND :b_status = ''LOAD''';
12091:
12092: FEM_ENGINES_PKG.TECH_MESSAGE
12093: (c_log_level_2,c_block||'.'||
12094: 'build_insert_attr_stmt',
12095: 'End');
12096:
12120:
12121: IS
12122:
12123: BEGIN
12124: FEM_ENGINES_PKG.TECH_MESSAGE
12125: (c_log_level_2,c_block||'.'||
12126: 'build_status_update_stmt',
12127: 'Begin SQL statement to update the status in the source interface table');
12128:
12131: ' SET status = :b_t_a_status '||
12132: ' WHERE rowid = :b_rowid'||
12133: ' AND :b_t_a_status <> ''LOAD''';
12134:
12135: FEM_ENGINES_PKG.TECH_MESSAGE
12136: (c_log_level_2,c_block||'.'||
12137: 'build_status_update_stmt',
12138: 'End');
12139:
12164:
12165: IS
12166:
12167: BEGIN
12168: FEM_ENGINES_PKG.TECH_MESSAGE
12169: (c_log_level_2,c_block||'.'||
12170: 'build_calp_status_update_stmt',
12171: 'Begin SQL statement to update the status in the interface table based on bad records from the CALP Interim table');
12172:
12190: ' AND :b_overlap_flag = ''Y''';
12191:
12192: END IF;
12193:
12194: FEM_ENGINES_PKG.TECH_MESSAGE
12195: (c_log_level_2,c_block||'.'||
12196: 'build_calp_status_update_stmt',
12197: 'End');
12198:
12227: v_attr_where_cond VARCHAR2(4000);
12228: v_tl_where_cond VARCHAR2(4000);
12229:
12230: BEGIN
12231: FEM_ENGINES_PKG.TECH_MESSAGE
12232: (c_log_level_2,c_block||'.'||
12233: 'build_calp_delete_stmt',
12234: 'Begin SQL statement to delete rows from the CAL_PERIOD interface tables');
12235: IF p_source_table = 'FEM_CAL_PERIODS_ATTR_T' THEN
12267: ' AND dimension_group_display_code = :b_dimension_group_dc'||
12268: v_attr_where_cond||v_tl_where_cond;
12269:
12270:
12271: FEM_ENGINES_PKG.TECH_MESSAGE
12272: (c_log_level_2,c_block||'.'||
12273: 'build_calp_delete_stmt',
12274: 'End');
12275:
12310:
12311: IS
12312:
12313: BEGIN
12314: FEM_ENGINES_PKG.TECH_MESSAGE
12315: (c_log_level_2,c_block||'.'||
12316: 'build_dep_status_update_stmt',
12317: 'Begin');
12318:
12335: ' AND :b_t_a_status <> ''LOAD'''||
12336: ' AND status IN (''LOAD'')';
12337: END IF;
12338:
12339: FEM_ENGINES_PKG.TECH_MESSAGE
12340: (c_log_level_2,c_block||'.'||
12341: 'build_dep_status_update_stmt',
12342: 'End');
12343:
12373:
12374: v_dim_label_where_cond VARCHAR2(1000);
12375:
12376: BEGIN
12377: FEM_ENGINES_PKG.TECH_MESSAGE
12378: (c_log_level_2,c_block||'.'||
12379: 'build_attrlab_update_stmt',
12380: 'Begin');
12381:
12398: v_dim_label_where_cond||
12399: ' AND {{data_slice}} ';
12400:
12401:
12402: FEM_ENGINES_PKG.TECH_MESSAGE
12403: (c_log_level_2,c_block||'.'||
12404: 'build_attrlab_update_stmt',
12405: 'End');
12406:
12436:
12437: v_dim_label_where_cond VARCHAR2(1000);
12438:
12439: BEGIN
12440: FEM_ENGINES_PKG.TECH_MESSAGE
12441: (c_log_level_2,c_block||'.'||
12442: 'build_attrlab_update_stmt',
12443: 'Begin');
12444:
12462: v_dim_label_where_cond||
12463: ' AND {{data_slice}} ';
12464:
12465:
12466: FEM_ENGINES_PKG.TECH_MESSAGE
12467: (c_log_level_2,c_block||'.'||
12468: 'build_attrlab_update_stmt',
12469: 'End');
12470:
12499:
12500: IS
12501:
12502: BEGIN
12503: FEM_ENGINES_PKG.TECH_MESSAGE
12504: (c_log_level_2,c_block||'.'||
12505: 'build_delete_stmt',
12506: 'Begin');
12507:
12509: ' WHERE rowid = :b_rowid'||
12510: ' AND :b_t_a_status = ''LOAD'''||
12511: ' AND :b_use_interim_table_flag = ''N''';
12512:
12513: FEM_ENGINES_PKG.TECH_MESSAGE
12514: (c_log_level_2,c_block||'.'||
12515: 'build_delete_stmt',
12516: 'End');
12517:
12545:
12546: IS
12547:
12548: BEGIN
12549: FEM_ENGINES_PKG.TECH_MESSAGE
12550: (c_log_level_2,c_block||'.'||
12551: 'build_special_delete_stmt',
12552: 'Begin');
12553:
12558: ' AND :b_status = ''LOAD'''||
12559: ' AND :b_use_interim_table_flag = ''N''';
12560:
12561:
12562: FEM_ENGINES_PKG.TECH_MESSAGE
12563: (c_log_level_2,c_block||'.'||
12564: 'build_special_delete_stmt',
12565: 'End');
12566:
12617: v_dim_label_where_cond VARCHAR2(1000);
12618:
12619:
12620: BEGIN
12621: FEM_ENGINES_PKG.TECH_MESSAGE
12622: (c_log_level_2,c_block||'.'||
12623: 'build_remain_mbr_select_stmt',
12624: 'Begin');
12625:
12801: *******************************************************/
12802:
12803: END IF;
12804:
12805: FEM_ENGINES_PKG.TECH_MESSAGE
12806: (c_log_level_2,c_block||'.'||
12807: 'build_remain_mbr_select_stmt',
12808: 'End');
12809:
12840: IS
12841:
12842:
12843: BEGIN
12844: FEM_ENGINES_PKG.TECH_MESSAGE
12845: (c_log_level_2,c_block||'.'||
12846: 'build_dimgrp_update_stmt',
12847: 'Begin');
12848:
12868: ' WHERE '||p_member_dc_col||' = :b_member_dc'||
12869: ' AND :b_status = ''LOAD''';
12870:
12871: END IF;
12872: FEM_ENGINES_PKG.TECH_MESSAGE
12873: (c_log_level_2,c_block||'.'||
12874: 'build_dimgrp_update_stmt',
12875: 'End');
12876:
12915: IS
12916:
12917:
12918: BEGIN
12919: FEM_ENGINES_PKG.TECH_MESSAGE
12920: (c_log_level_2,c_block||'.'||
12921: 'build_attr_update_stmt',
12922: 'Begin');
12923:
13010: *******************************************/
13011:
13012:
13013: END IF; -- p_value_set_required_flag
13014: FEM_ENGINES_PKG.TECH_MESSAGE
13015: (c_log_level_2,c_block||'.'||
13016: 'build_attr_update_stmt',
13017: 'End');
13018:
13126: cv_get_rows cv_curs;
13127:
13128: BEGIN
13129:
13130: FEM_ENGINES_PKG.TECH_MESSAGE
13131: (c_log_level_2,c_block||'.'||'calp_date_overlap_check','Begin Date Overlap Check for CAL_PERIOD load');
13132:
13133: -- initialize the fetch limit to 10,000 rows
13134: -- since calp_date_overlap_check is not an MP procedure, we can't get
13215: ' SET overlap_flag = ''Y'''||
13216: ' WHERE rowid = :b_rowid'||
13217: ' AND :b_overlap_flag = ''Y''';
13218:
13219: FEM_ENGINES_PKG.TECH_MESSAGE
13220: (c_log_level_1,c_block||'.'||'x_select_stmt',x_select_stmt);
13221:
13222: OPEN cv_get_rows FOR x_select_stmt;
13223:
13253: -- cal_period_ids that caused the overlap, because to do so would be
13254: -- expensive in terms of code and even performance because it would require
13255: -- an additional I/O since the overlaps might not exist within the array
13256: ------------------------------------------------------------------------
13257: FEM_ENGINES_PKG.TECH_MESSAGE
13258: (c_log_level_1,c_block||'.'||'calp_date_overlap_check','Begin looking for overlaps');
13259:
13260: FOR i IN 1..v_mbr_last_row
13261: LOOP
13276: -- Update the Interim table Overlap Flag for all bad records
13277: -- this statement either updates FEM_CALP_INTERIM_T or
13278: -- FEM_CALP_ATTR_INTERIM_T, depending on the Operation Mode
13279: ------------------------------------------------------------
13280: FEM_ENGINES_PKG.TECH_MESSAGE
13281: (c_log_level_1,c_block||'.'||'calp_date_overlap_check','Update interim table');
13282:
13283: FORALL i IN 1..v_mbr_last_row
13284: EXECUTE IMMEDIATE x_update_stmt
13294: --
13295: -- Otherwise (Attr Assign Update mode), we only update the
13296: -- following interface table:
13297: -- FEM_CAL_PERIODS_ATTR_T (for the START_DATE attribute)
13298: FEM_ENGINES_PKG.TECH_MESSAGE
13299: (c_log_level_1,c_block||'.'||'calp_date_overlap_check','Update status of bad records');
13300:
13301: IF p_operation_mode = 'NEW_MEMBERS' THEN
13302: ----------------------------------------------------------
13386: END LOOP; -- main bulk collect
13387: CLOSE cv_get_rows;
13388: x_rows_rejected := v_rows_rejected;
13389:
13390: FEM_ENGINES_PKG.TECH_MESSAGE
13391: (c_log_level_2,c_block||'.'||'calp_date_overlap_check','End Date Overlap Check for CAL_PERIOD load');
13392:
13393:
13394: END calp_date_overlap_check;
13420:
13421: BEGIN
13422: -- Get the source_system_code for the input source_system_dc
13423: BEGIN
13424: FEM_ENGINES_PKG.TECH_MESSAGE
13425: (c_log_level_3,c_block||'.'||c_proc_name,'Begin');
13426:
13427: SELECT source_system_code
13428: INTO v_source_system_code
13489: WHERE dimension_id = p_dimension_id
13490: AND source_system_code = v_source_system_code;
13491: END IF;
13492:
13493: FEM_ENGINES_PKG.TECH_MESSAGE
13494: (c_log_level_3,c_block||'.'||c_proc_name,'End');
13495:
13496: EXCEPTION
13497: WHEN e_invalid_src_sys THEN
13534: i NUMBER; -- counting variable for no:of segments of Flex Field
13535:
13536: BEGIN
13537:
13538: fem_engines_pkg.tech_message (
13539: p_severity => c_log_level_1
13540: ,p_module => c_block||'.'||c_proc_name||'.Begin'
13541: ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
13542:
13620: t_metadata(j).member_sql := NULL;
13621:
13622: END LOOP;
13623:
13624: fem_engines_pkg.tech_message (
13625: p_severity => c_log_level_1
13626: ,p_module => c_block||'.'||c_proc_name||'.End'
13627: ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
13628:
13629: EXCEPTION
13630:
13631: WHEN others THEN
13632:
13633: fem_engines_pkg.tech_message (
13634: p_severity => c_log_level_4
13635: ,p_module => c_block||'.'||c_proc_name||'.Exception'
13636: ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
13637: 'Code'||SQLCODE||'Err'||SQLERRM);
13679: v_last_row NUMBER;
13680:
13681: BEGIN
13682:
13683: fem_engines_pkg.tech_message (
13684: p_severity => c_log_level_1
13685: ,p_module => c_block||'.'||c_proc_name||'.Begin'
13686: ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
13687:
13847: -----------------------------------------------------------------------------
13848: ---End Concatenate the segments
13849: -----------------------------------------------------------------------------
13850:
13851: fem_engines_pkg.tech_message (
13852: p_severity => c_log_level_1
13853: ,p_module => c_block||'.'||c_proc_name||'.End'
13854: ,p_msg_text => 'Dimension'||p_dimension_varchar_label);
13855:
13855:
13856: EXCEPTION
13857:
13858: WHEN others THEN
13859: fem_engines_pkg.tech_message (
13860: p_severity => c_log_level_4
13861: ,p_module => c_block||'.'||c_proc_name||'.Exception'
13862: ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
13863: 'Code'||SQLCODE||'Err'||SQLERRM);
13913:
13914: -- Initialize the return status
13915: x_pre_process_status := 'SUCCESS';
13916:
13917: fem_engines_pkg.tech_message (
13918: p_severity => c_log_level_1
13919: ,p_module => c_block||'.'||c_proc_name||'.Begin'
13920: ,p_msg_text => 'Mode'||p_execution_mode
13921: ||'Dimension'||p_dimension_varchar_label);
13933:
13934: EXCEPTION
13935:
13936: WHEN no_data_found THEN
13937: fem_engines_pkg.tech_message (
13938: p_severity => c_log_level_4
13939: ,p_module => c_block||'.'||c_proc_name||'.Freeze_Exception'
13940: ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
13941: 'Code'||SQLCODE||'Err'||SQLERRM);
13942: END;
13943:
13944: IF l_dim_active_flag = 'N' THEN
13945:
13946: fem_engines_pkg.tech_message (
13947: p_severity => c_log_level_5
13948: ,p_module => c_block||'.'||c_proc_name
13949: ,p_msg_text => 'Dimension'||p_dimension_varchar_label||' Flex Field
13950: Definition Not Freezed');
13962: --Initialize the TABLE TYPE Variable which holds the metadata information
13963: ---------------------------------------------------------------------------
13964: Metadata_Initialize(p_dimension_varchar_label);
13965:
13966: fem_engines_pkg.tech_message (
13967: p_severity => c_log_level_1
13968: ,p_module => c_block||'.'||c_proc_name
13969: ,p_msg_text => 'After Metadata_Initialize');
13970:
14122: END IF;
14123:
14124:
14125:
14126: fem_engines_pkg.tech_message (
14127: p_severity => c_log_level_1
14128: ,p_module => c_block||'.'||c_proc_name||'.End'
14129: ,p_msg_text => 'Mode'||p_execution_mode
14130: ||'Dimension'||p_dimension_varchar_label);
14132: EXCEPTION
14133:
14134: WHEN e_no_structure_defined THEN
14135:
14136: fem_engines_pkg.tech_message (
14137: p_severity => c_log_level_4
14138: ,p_module => c_block||'.'||c_proc_name||'.Exception'
14139: ,p_app_name => c_fem
14140: ,p_msg_name => G_NO_STRUCTURE_DEFINED
14144: x_pre_process_status := 'ERROR';
14145:
14146: WHEN others THEN
14147:
14148: fem_engines_pkg.tech_message (
14149: p_severity => c_log_level_4
14150: ,p_module => c_block||'.'||c_proc_name||'.Exception'
14151: ,p_msg_text => 'Mode'||p_execution_mode||
14152: 'Dimension'||p_dimension_varchar_label||
14568:
14569:
14570: BEGIN
14571:
14572: fem_engines_pkg.tech_message(
14573: p_severity => c_log_level_2
14574: ,p_module => c_block||'.'||c_proc_name||'.Begin'
14575: ,p_msg_text => 'Execution Mode' || p_execution_mode||
14576: 'Dimension' || p_dimension_varchar_label);
14593: ,p_dimension_varchar_label);
14594:
14595:
14596:
14597: fem_engines_pkg.tech_message(
14598: p_severity => c_log_level_5
14599: ,p_module => c_block||'.'||c_proc_name||'.After Pre_Process'
14600: ,p_msg_text => 'Pre_Process Error '||x_pre_process_status);
14601:
14606:
14607:
14608: IF x_pre_process_status = 'ERROR' THEN
14609:
14610: fem_engines_pkg.tech_message (
14611: p_severity => c_log_level_4
14612: ,p_module => c_block||'.'||c_proc_name||'Pre_Process Error'
14613: ,p_msg_text => 'Code'||SQLCODE||'Err'||SQLERRM);
14614:
14656:
14657: END IF;
14658:
14659:
14660: fem_engines_pkg.tech_message (
14661: p_severity => c_log_level_1
14662: ,p_module => c_block||'.'||c_proc_name
14663: ,p_msg_text => 'v_update_stmt '||v_update_stmt||
14664: 'v_member_table_name'||v_member_table_name);
14663: ,p_msg_text => 'v_update_stmt '||v_update_stmt||
14664: 'v_member_table_name'||v_member_table_name);
14665:
14666:
14667: fem_engines_pkg.tech_message (
14668: p_severity => c_log_level_1
14669: ,p_module => c_block||'.'||c_proc_name
14670: ,p_msg_text => 'v_delete_stmt '||v_delete_stmt||
14671: 'v_member_table_name'||v_member_table_name);
14680: ' SET status = ''LOAD'''||
14681: ' WHERE status <> ''LOAD'''||
14682: ' AND '||v_data_slc;
14683:
14684: fem_engines_pkg.tech_message (
14685: p_severity => c_log_level_1
14686: ,p_module => c_block||'.'||c_proc_name||'.Error Reproceesing Mode'
14687: ,p_msg_text => 'v_data_slc '||v_data_slc||
14688: 'v_member_table_name'||v_member_table_name);
14731: END IF; -- v_last_row
14732:
14733: END LOOP;
14734:
14735: fem_engines_pkg.tech_message (
14736: p_severity => c_log_level_1
14737: ,p_module => c_block||'.'||c_proc_name||'.Validation#1'
14738: ,p_msg_text => 'v_data_slc '||v_data_slc||
14739: 'v_member_table_name'||v_member_table_name||
14814: END LOOP;
14815:
14816: CLOSE cv_get_invalid_fin_elems;
14817:
14818: fem_engines_pkg.tech_message (
14819: p_severity => c_log_level_1
14820: ,p_module => c_block||'.'||c_proc_name||'.Validation#2.1'
14821: ,p_msg_text => 'v_data_slc '||v_data_slc||
14822: 'v_member_table_name'||v_member_table_name||
14860: END LOOP;
14861:
14862: CLOSE cv_get_invalid_fin_elems;
14863:
14864: fem_engines_pkg.tech_message (
14865: p_severity => c_log_level_1
14866: ,p_module => c_block||'.'||c_proc_name||'.Validation#2.2'
14867: ,p_msg_text => 'v_data_slc '||v_data_slc||
14868: 'v_member_table_name'||v_member_table_name||
14913: END LOOP;
14914:
14915: CLOSE cv_get_invalid_ledgers;
14916:
14917: fem_engines_pkg.tech_message (
14918: p_severity => c_log_level_1
14919: ,p_module=> c_block||'.'||c_proc_name||'.Validation#2.3'
14920: ,p_msg_text => 'v_data_slc '||v_data_slc||
14921: 'v_member_table_name'||v_member_table_name||
14998: END LOOP; -- FOR LOOP
14999:
15000: COMMIT;
15001:
15002: fem_engines_pkg.tech_message (
15003: p_severity => c_log_level_1
15004: ,p_module=> c_block||'.'||c_proc_name||'.Validation#3'
15005: ,p_msg_text => 'v_data_slc '||v_data_slc||
15006: 'v_member_table_name'||v_member_table_name||
15070:
15071: -- Get the no:of rows this fetch
15072: v_mbr_last_row := t_status.COUNT;
15073:
15074: fem_engines_pkg.tech_message (
15075: p_severity => c_log_level_1
15076: ,p_module=> c_block||'.'||c_proc_name||'.Validation#3'
15077: ,p_msg_text => 'v_data_slc '||v_data_slc||
15078: 'v_member_table_name'||v_member_table_name||
15409: END IF; -- Cost Object
15410:
15411: END LOOP; -- 1...v_member_last_row.(j)
15412:
15413: fem_engines_pkg.tech_message (
15414: p_severity => c_log_level_1
15415: ,p_module=> c_block||'.'||c_proc_name||'.Validation#4 - End');
15416:
15417:
15439: END LOOP;
15440: END LOOP;
15441:
15442:
15443: fem_engines_pkg.tech_message (
15444: p_severity => c_log_level_1
15445: ,p_module=> c_block||'.'||c_proc_name||'.Validation#5 - End');
15446:
15447: ----------------------------------------------------------------------------
15470: END IF;
15471: END LOOP;
15472:
15473:
15474: fem_engines_pkg.tech_message (
15475: p_severity => c_log_level_1
15476: ,p_module=> c_block||'.'||c_proc_name||'.Validation#6 - End');
15477:
15478: ----------------------------------------------------------------------------
15638:
15639: CLOSE cv_get_rows;
15640:
15641:
15642: fem_engines_pkg.tech_message (
15643: p_severity => c_log_level_4
15644: ,p_module => c_block||'.'||c_proc_name
15645: ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
15646: 'Data Slice'||v_data_slc||
15704:
15705: EXCEPTION
15706: WHEN e_terminate THEN
15707:
15708: fem_engines_pkg.tech_message(
15709: p_severity => c_log_level_4
15710: ,p_module => c_block||'.'||c_proc_name||'Exception');
15711:
15712: IF cv_get_rows%ISOPEN THEN
15734:
15735: RAISE e_main_terminate;
15736:
15737: WHEN OTHERS THEN
15738: fem_engines_pkg.tech_message (
15739: p_severity => c_log_level_4
15740: ,p_module => c_block||'.'||c_proc_name||'.Exception'
15741: ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
15742: 'Code'||SQLCODE||'Err'||SQLERRM);
15982:
15983: EXCEPTION
15984:
15985: WHEN no_data_found THEN
15986: fem_engines_pkg.tech_message (
15987: p_severity => c_log_level_4
15988: ,p_module => c_block||'.'||c_proc_name||'.Comp Dim Params not found'
15989: ,p_msg_text => 'Dimension'||p_dimension_varchar_label||
15990: 'Code'||SQLCODE||'Err'||SQLERRM);
15993: END IF;
15994:
15995:
15996:
15997: FEM_ENGINES_PKG.TECH_MESSAGE
15998: (c_log_level_3,c_block||'.'||c_proc_name||'.Preparation','Get dimension information');
15999: FEM_ENGINES_PKG.TECH_MESSAGE
16000: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.p_dimension_varchar_label',p_dimension_varchar_label);
16001: FEM_ENGINES_PKG.TECH_MESSAGE
15995:
15996:
15997: FEM_ENGINES_PKG.TECH_MESSAGE
15998: (c_log_level_3,c_block||'.'||c_proc_name||'.Preparation','Get dimension information');
15999: FEM_ENGINES_PKG.TECH_MESSAGE
16000: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.p_dimension_varchar_label',p_dimension_varchar_label);
16001: FEM_ENGINES_PKG.TECH_MESSAGE
16002: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.c_user_id',c_user_id);
16003: FEM_ENGINES_PKG.TECH_MESSAGE
15997: FEM_ENGINES_PKG.TECH_MESSAGE
15998: (c_log_level_3,c_block||'.'||c_proc_name||'.Preparation','Get dimension information');
15999: FEM_ENGINES_PKG.TECH_MESSAGE
16000: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.p_dimension_varchar_label',p_dimension_varchar_label);
16001: FEM_ENGINES_PKG.TECH_MESSAGE
16002: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.c_user_id',c_user_id);
16003: FEM_ENGINES_PKG.TECH_MESSAGE
16004: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.v_obj_def_id',v_object_definition_id);
16005: FEM_ENGINES_PKG.TECH_MESSAGE
15999: FEM_ENGINES_PKG.TECH_MESSAGE
16000: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.p_dimension_varchar_label',p_dimension_varchar_label);
16001: FEM_ENGINES_PKG.TECH_MESSAGE
16002: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.c_user_id',c_user_id);
16003: FEM_ENGINES_PKG.TECH_MESSAGE
16004: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.v_obj_def_id',v_object_definition_id);
16005: FEM_ENGINES_PKG.TECH_MESSAGE
16006: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.p_object_id',p_object_id);
16007:
16001: FEM_ENGINES_PKG.TECH_MESSAGE
16002: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.c_user_id',c_user_id);
16003: FEM_ENGINES_PKG.TECH_MESSAGE
16004: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.v_obj_def_id',v_object_definition_id);
16005: FEM_ENGINES_PKG.TECH_MESSAGE
16006: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.p_object_id',p_object_id);
16007:
16008: Engine_Master_Prep (p_dimension_varchar_label
16009: ,v_object_definition_id
16040: IF v_eng_master_prep_status IN ('ERROR') THEN
16041: RAISE e_terminate;
16042: END IF;
16043:
16044: FEM_ENGINES_PKG.TECH_MESSAGE
16045: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.source_b_table ',v_source_b_table);
16046:
16047: ------------------------------------------------------------------------------
16048: --Composite Dimensions dont have _TL and _ATTR tables.
16048: --Composite Dimensions dont have _TL and _ATTR tables.
16049: ------------------------------------------------------------------------------
16050:
16051: IF v_composite_dimension_flag = 'N' THEN
16052: FEM_ENGINES_PKG.TECH_MESSAGE
16053: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.source_tl_table ',v_source_tl_table);
16054: FEM_ENGINES_PKG.TECH_MESSAGE
16055: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.source_attr_table ',v_source_attr_table);
16056: END IF;
16050:
16051: IF v_composite_dimension_flag = 'N' THEN
16052: FEM_ENGINES_PKG.TECH_MESSAGE
16053: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.source_tl_table ',v_source_tl_table);
16054: FEM_ENGINES_PKG.TECH_MESSAGE
16055: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.source_attr_table ',v_source_attr_table);
16056: END IF;
16057:
16058:
16055: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.source_attr_table ',v_source_attr_table);
16056: END IF;
16057:
16058:
16059: FEM_ENGINES_PKG.TECH_MESSAGE
16060: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.value_set_required_flag ',v_value_set_required_flag);
16061: FEM_ENGINES_PKG.TECH_MESSAGE
16062: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.table handler ',v_table_handler_name);
16063:
16057:
16058:
16059: FEM_ENGINES_PKG.TECH_MESSAGE
16060: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.value_set_required_flag ',v_value_set_required_flag);
16061: FEM_ENGINES_PKG.TECH_MESSAGE
16062: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.table handler ',v_table_handler_name);
16063:
16064: FEM_ENGINES_PKG.TECH_MESSAGE
16065: (c_log_level_1,c_block||'.'||c_proc_name||'.v_exec_mode_clause ',v_exec_mode_clause);
16060: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.value_set_required_flag ',v_value_set_required_flag);
16061: FEM_ENGINES_PKG.TECH_MESSAGE
16062: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.table handler ',v_table_handler_name);
16063:
16064: FEM_ENGINES_PKG.TECH_MESSAGE
16065: (c_log_level_1,c_block||'.'||c_proc_name||'.v_exec_mode_clause ',v_exec_mode_clause);
16066:
16067: FEM_ENGINES_PKG.TECH_MESSAGE
16068: (c_log_level_1,c_block||'.'||c_proc_name||'.v_date_format_mask ',v_date_format_mask);
16063:
16064: FEM_ENGINES_PKG.TECH_MESSAGE
16065: (c_log_level_1,c_block||'.'||c_proc_name||'.v_exec_mode_clause ',v_exec_mode_clause);
16066:
16067: FEM_ENGINES_PKG.TECH_MESSAGE
16068: (c_log_level_1,c_block||'.'||c_proc_name||'.v_date_format_mask ',v_date_format_mask);
16069:
16070: -- The following are not needed composite dimension
16071:
16075: -- Preparation STEP: Determing the Source System Codes in the load
16076: ------------------------------------------------------------------------------
16077: IF v_simple_dimension_flag = 'N' THEN
16078:
16079: FEM_ENGINES_PKG.TECH_MESSAGE
16080: (c_log_level_3,c_block||'.'||c_proc_name||
16081: '.Preparation STEP','Determing Source System Codes for the load');
16082: build_src_sys_select_stmt (p_dimension_varchar_label
16083: ,v_source_attr_table
16082: build_src_sys_select_stmt (p_dimension_varchar_label
16083: ,v_source_attr_table
16084: ,v_shared_dimension_flag
16085: ,x_src_sys_select_stmt);
16086: FEM_ENGINES_PKG.TECH_MESSAGE
16087: (c_log_level_1,c_block||'.'||c_proc_name||'.Preparation.src_sys_select_stmt',x_src_sys_select_stmt);
16088: OPEN cv_get_src_sys FOR x_src_sys_select_stmt;
16089: FETCH cv_get_src_sys BULK COLLECT INTO
16090: tg_src_system_dc;
16088: OPEN cv_get_src_sys FOR x_src_sys_select_stmt;
16089: FETCH cv_get_src_sys BULK COLLECT INTO
16090: tg_src_system_dc;
16091: v_src_sys_last_row := tg_src_system_dc.LAST;
16092: FEM_ENGINES_PKG.TECH_MESSAGE
16093: (c_log_level_1,c_block||'.'||c_proc_name||'.Source_System_Step.v_src_sys_last_row'
16094: ,v_src_sys_last_row);
16095: END IF; -- src system selection
16096:
16097: IF (v_hier_dimension_flag = 'Y') THEN
16098: ------------------------------------------------------------------------------
16099: -- Load Dimension Groups
16100: ------------------------------------------------------------------------------
16101: FEM_ENGINES_PKG.TECH_MESSAGE
16102: (c_log_level_3,c_block||'.'||c_proc_name||
16103: '.Dimension Groups','Starting the Dimension Group load section');
16104: v_load_type := 'DIMENSION_GROUP';
16105:
16106: ------------------------------------------------------------------------------
16107: -- Pre_Validation
16108: -- This step identifies new Dimension Groups missing TL records
16109: ------------------------------------------------------------------------------
16110: FEM_ENGINES_PKG.TECH_MESSAGE
16111: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Grp Pre_Validation',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16112:
16113: Pre_Validation (P_ENG_SQL => null
16114: ,P_DATA_SLC => null
16128: ,P_SIMPLE_DIMENSION_FLAG => 'Y'
16129: ,P_SHARED_DIMENSION_FLAG => 'N'
16130: ,P_EXEC_MODE_CLAUSE => v_exec_mode_clause
16131: ,P_MASTER_REQUEST_ID => p_master_request_id);
16132: FEM_ENGINES_PKG.TECH_MESSAGE
16133: (c_log_level_1,c_block||'.'||c_proc_name||
16134: '.gv_dimgrp_rows_rejected',gv_dimgrp_rows_rejected);
16135:
16136: FEM_ENGINES_PKG.TECH_MESSAGE
16132: FEM_ENGINES_PKG.TECH_MESSAGE
16133: (c_log_level_1,c_block||'.'||c_proc_name||
16134: '.gv_dimgrp_rows_rejected',gv_dimgrp_rows_rejected);
16135:
16136: FEM_ENGINES_PKG.TECH_MESSAGE
16137: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Grp Pre_Valdation',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16138:
16139:
16140: ------------------------------------------------------------------------------
16139:
16140: ------------------------------------------------------------------------------
16141: -- New Dimension Groups
16142: ------------------------------------------------------------------------------
16143: FEM_ENGINES_PKG.TECH_MESSAGE
16144: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Grp New_Members',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16145:
16146: New_Members (P_ENG_SQL => null
16147: ,P_DATA_SLC => null
16171: ,P_HIER_DIMENSION_FLAG => 'N'
16172: ,P_MEMBER_ID_METHOD_CODE => 'FUNCTION'
16173: ,P_EXEC_MODE_CLAUSE => v_exec_mode_clause
16174: ,P_MASTER_REQUEST_ID => p_master_request_id);
16175: FEM_ENGINES_PKG.TECH_MESSAGE
16176: (c_log_level_1,c_block||'.'||c_proc_name||
16177: '.gv_dimgrp_rows_rejected',gv_dimgrp_rows_rejected);
16178:
16179: FEM_ENGINES_PKG.TECH_MESSAGE
16175: FEM_ENGINES_PKG.TECH_MESSAGE
16176: (c_log_level_1,c_block||'.'||c_proc_name||
16177: '.gv_dimgrp_rows_rejected',gv_dimgrp_rows_rejected);
16178:
16179: FEM_ENGINES_PKG.TECH_MESSAGE
16180: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Grp New_Members',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16181:
16182: ------------------------------------------------------------------------------
16183: -- Update Name/Descriptions for existing Members
16181:
16182: ------------------------------------------------------------------------------
16183: -- Update Name/Descriptions for existing Members
16184: ------------------------------------------------------------------------------
16185: FEM_ENGINES_PKG.TECH_MESSAGE
16186: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Grp TL_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16187:
16188: TL_Update (P_ENG_SQL => null
16189: ,P_DATA_SLC => null
16209: ,P_HIER_DIMENSION_FLAG => 'N'
16210: ,P_EXEC_MODE_CLAUSE => v_exec_mode_clause
16211: ,P_MASTER_REQUEST_ID => p_master_request_id);
16212:
16213: FEM_ENGINES_PKG.TECH_MESSAGE
16214: (c_log_level_1,c_block||'.'||c_proc_name||
16215: '.gv_dimgrp_rows_rejected',gv_dimgrp_rows_rejected);
16216:
16217: FEM_ENGINES_PKG.TECH_MESSAGE
16213: FEM_ENGINES_PKG.TECH_MESSAGE
16214: (c_log_level_1,c_block||'.'||c_proc_name||
16215: '.gv_dimgrp_rows_rejected',gv_dimgrp_rows_rejected);
16216:
16217: FEM_ENGINES_PKG.TECH_MESSAGE
16218: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Grp TL_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16219:
16220: ----------------------------------------------------------------------------------
16221: -- Base_Update
16219:
16220: ----------------------------------------------------------------------------------
16221: -- Base_Update
16222: ----------------------------------------------------------------------------------
16223: FEM_ENGINES_PKG.TECH_MESSAGE
16224: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Grp Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16225:
16226: Base_Update (P_ENG_SQL => null
16227: ,P_DATA_SLC => null
16243: ,P_MEMBER_COL => c_dimgrp_col
16244: ,P_EXEC_MODE_CLAUSE => v_exec_mode_clause
16245: ,P_MASTER_REQUEST_ID => p_master_request_id);
16246:
16247: FEM_ENGINES_PKG.TECH_MESSAGE
16248: (c_log_level_1,c_block||'.'||c_proc_name||
16249: '.gv_dimgrp_rows_rejected',gv_dimgrp_rows_rejected);
16250:
16251: FEM_ENGINES_PKG.TECH_MESSAGE
16247: FEM_ENGINES_PKG.TECH_MESSAGE
16248: (c_log_level_1,c_block||'.'||c_proc_name||
16249: '.gv_dimgrp_rows_rejected',gv_dimgrp_rows_rejected);
16250:
16251: FEM_ENGINES_PKG.TECH_MESSAGE
16252: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Grp Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16253:
16254: END IF; -- Dimension Group Processing only happens for v_hier_dimension_flag = 'Y'
16255: v_rows_rejected_accum := gv_dimgrp_rows_rejected;
16252: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Grp Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16253:
16254: END IF; -- Dimension Group Processing only happens for v_hier_dimension_flag = 'Y'
16255: v_rows_rejected_accum := gv_dimgrp_rows_rejected;
16256: FEM_ENGINES_PKG.TECH_MESSAGE
16257: (c_log_level_1,c_block||'.'||c_proc_name||
16258: '.v_rows_rejected_accum',v_rows_rejected_accum);
16259:
16260: ------------------------------------------------------------------------------
16259:
16260: ------------------------------------------------------------------------------
16261: -- Load Dimension Members
16262: ------------------------------------------------------------------------------
16263: FEM_ENGINES_PKG.TECH_MESSAGE
16264: (c_log_level_3,c_block||'.'||c_proc_name||
16265: '.Dimension Members','Starting the Dimension Member load section');
16266:
16267: v_load_type := 'DIMENSION_MEMBER';
16269: -- Pre_Validation
16270: -- This step identifies new members missing TL records
16271: ------------------------------------------------------------------------------
16272:
16273: FEM_ENGINES_PKG.TECH_MESSAGE
16274: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr Pre_Validation',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16275:
16276: fem_multi_proc_pkg.MASTER
16277: (X_PRG_STAT => v_mp_prg_status
16299: ,P_ARG12 => v_shared_dimension_flag
16300: ,P_ARG13 => v_exec_mode_clause
16301: ,P_ARG14 => p_master_request_id);
16302:
16303: FEM_ENGINES_PKG.TECH_MESSAGE
16304: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mp_pgr_status',v_mp_prg_status);
16305: FEM_ENGINES_PKG.TECH_MESSAGE
16306: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mp_exception_code',v_mp_exception_code);
16307:
16301: ,P_ARG14 => p_master_request_id);
16302:
16303: FEM_ENGINES_PKG.TECH_MESSAGE
16304: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mp_pgr_status',v_mp_prg_status);
16305: FEM_ENGINES_PKG.TECH_MESSAGE
16306: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mp_exception_code',v_mp_exception_code);
16307:
16308: IF v_mp_prg_status NOT IN ('COMPLETE:NORMAL') THEN
16309: IF v_mp_exception_code IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
16316: -- accumulate rejected row count
16317: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16318: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16319:
16320: FEM_ENGINES_PKG.TECH_MESSAGE
16321: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr Pre_Validation',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16322:
16323: FEM_ENGINES_PKG.TECH_MESSAGE
16324: (c_log_level_1,c_block||'.'||c_proc_name||
16319:
16320: FEM_ENGINES_PKG.TECH_MESSAGE
16321: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr Pre_Validation',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16322:
16323: FEM_ENGINES_PKG.TECH_MESSAGE
16324: (c_log_level_1,c_block||'.'||c_proc_name||
16325: '.v_rows_rejected_accum',v_rows_rejected_accum);
16326:
16327:
16333: IF p_dimension_varchar_label = 'CAL_PERIOD' THEN
16334: truncate_calp_interim;
16335: END IF;
16336:
16337: FEM_ENGINES_PKG.TECH_MESSAGE
16338: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr New_Members',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16339:
16340:
16341: fem_multi_proc_pkg.MASTER
16374: ,P_ARG22 => v_member_id_method_code
16375: ,P_ARG23 => v_exec_mode_clause
16376: ,P_ARG24 => p_master_request_id
16377: );
16378: FEM_ENGINES_PKG.TECH_MESSAGE
16379: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mp_exception',v_mp_exception_code);
16380:
16381:
16382: IF v_mp_prg_status NOT IN ('COMPLETE:NORMAL') THEN
16389: -- accumulate rejected row count
16390: v_rows_loaded := 0; -- initializing this variable so we can use it to
16391: -- figure out if any new members were created
16392: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16393: FEM_ENGINES_PKG.TECH_MESSAGE
16394: (c_log_level_1,c_block||'.'||c_proc_name||
16395: '.v_rows_loaded',v_rows_loaded);
16396:
16397: IF v_rows_loaded > 0 THEN
16398: raise_member_bus_event (p_dimension_varchar_label);
16399: END IF;
16400:
16401: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16402: FEM_ENGINES_PKG.TECH_MESSAGE
16403: (c_log_level_1,c_block||'.'||c_proc_name||
16404: '.v_rows_rejected_accum',v_rows_rejected_accum);
16405: FEM_ENGINES_PKG.TECH_MESSAGE
16406: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr New_Members',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16401: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16402: FEM_ENGINES_PKG.TECH_MESSAGE
16403: (c_log_level_1,c_block||'.'||c_proc_name||
16404: '.v_rows_rejected_accum',v_rows_rejected_accum);
16405: FEM_ENGINES_PKG.TECH_MESSAGE
16406: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr New_Members',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16407:
16408: FEM_ENGINES_PKG.TECH_MESSAGE
16409: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Overlap Processing',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16404: '.v_rows_rejected_accum',v_rows_rejected_accum);
16405: FEM_ENGINES_PKG.TECH_MESSAGE
16406: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr New_Members',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16407:
16408: FEM_ENGINES_PKG.TECH_MESSAGE
16409: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Overlap Processing',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16410: --------------------------------------------------------------
16411: -- Perform the Date Overlap Check for CAL_PERIOD loads
16412: -- and move all valid new members into FEM
16429: ,P_FAILED_REQ_ID => null
16430: ,P_ARG1 => 'NEW_MEMBERS'
16431: ,P_ARG2 => p_master_request_id);
16432:
16433: FEM_ENGINES_PKG.TECH_MESSAGE
16434: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mp_exception',v_mp_exception_code);
16435:
16436: IF v_mp_prg_status NOT IN ('COMPLETE:NORMAL') THEN
16437: IF v_mp_exception_code IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
16443:
16444: -- accumulate rejected row count
16445: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16446: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16447: FEM_ENGINES_PKG.TECH_MESSAGE
16448: (c_log_level_1,c_block||'.'||c_proc_name||
16449: '.v_rows_rejected_accum',v_rows_rejected_accum);
16450:
16451: FEM_ENGINES_PKG.TECH_MESSAGE
16447: FEM_ENGINES_PKG.TECH_MESSAGE
16448: (c_log_level_1,c_block||'.'||c_proc_name||
16449: '.v_rows_rejected_accum',v_rows_rejected_accum);
16450:
16451: FEM_ENGINES_PKG.TECH_MESSAGE
16452: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Overlap Processing',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16453:
16454: END IF; -- overlap check for CAL_PERIOD load
16455: --------------------------------------------------------------
16459: -- Base_Update
16460: ----------------------------------------------------------------------------------
16461:
16462:
16463: FEM_ENGINES_PKG.TECH_MESSAGE
16464: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16465:
16466: fem_multi_proc_pkg.MASTER
16467: (X_PRG_STAT => v_mp_prg_status
16498: RAISE e_terminate;
16499: END IF;
16500: END IF;
16501:
16502: FEM_ENGINES_PKG.TECH_MESSAGE
16503: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr Base_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16504:
16505: -- accumulate rejected row count
16506: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16504:
16505: -- accumulate rejected row count
16506: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16507: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16508: FEM_ENGINES_PKG.TECH_MESSAGE
16509: (c_log_level_1,c_block||'.'||c_proc_name||
16510: '.v_rows_rejected_accum',v_rows_rejected_accum);
16511: --END IF; -- simple dim = 'N' (for Base Update)
16512:
16513: ------------------------------------------------------------------------------
16514: -- Update Name/Descriptions for existing Members
16515: ------------------------------------------------------------------------------
16516:
16517: FEM_ENGINES_PKG.TECH_MESSAGE
16518: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr TL_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16519:
16520: fem_multi_proc_pkg.MASTER
16521: (X_PRG_STAT => v_mp_prg_status
16556: RAISE e_terminate;
16557: END IF;
16558: END IF;
16559:
16560: FEM_ENGINES_PKG.TECH_MESSAGE
16561: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr TL_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16562:
16563: -- accumulate rejected row count
16564: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16562:
16563: -- accumulate rejected row count
16564: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16565: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16566: FEM_ENGINES_PKG.TECH_MESSAGE
16567: (c_log_level_1,c_block||'.'||c_proc_name||
16568: '.v_rows_rejected_accum',v_rows_rejected_accum);
16569:
16570: ------------------------------------------------------------------------------
16571: -- Pre_Validation_Attr
16572: --This step identifies bad attr records (only run for Simple_dim='N'
16573: ------------------------------------------------------------------------------
16574: IF (v_simple_dimension_flag = 'N') THEN
16575: FEM_ENGINES_PKG.TECH_MESSAGE
16576: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr Pre_Validation_Attr',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16577:
16578: fem_multi_proc_pkg.MASTER
16579: (X_PRG_STAT => v_mp_prg_status
16602: ,P_ARG13 => v_hier_dimension_flag
16603: ,P_ARG14 => v_exec_mode_clause
16604: ,P_ARG15 => p_master_request_id
16605: );
16606: FEM_ENGINES_PKG.TECH_MESSAGE
16607: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mp_exception',v_mp_exception_code);
16608:
16609: IF v_mp_prg_status NOT IN ('COMPLETE:NORMAL') THEN
16610: IF v_mp_exception_code IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
16613: RAISE e_terminate;
16614: END IF;
16615: END IF;
16616:
16617: FEM_ENGINES_PKG.TECH_MESSAGE
16618: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Mbr Pre_Validation_Attr',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16619:
16620: -- accumulate rejected row count
16621: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16619:
16620: -- accumulate rejected row count
16621: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16622: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16623: FEM_ENGINES_PKG.TECH_MESSAGE
16624: (c_log_level_1,c_block||'.'||c_proc_name||
16625: '.v_rows_rejected_accum',v_rows_rejected_accum);
16626:
16627: ----------------------------------------------------------------------------------
16632: IF p_dimension_varchar_label = 'CAL_PERIOD' THEN
16633: truncate_calp_interim;
16634: END IF;
16635:
16636: FEM_ENGINES_PKG.TECH_MESSAGE
16637: (c_log_level_3,c_block||'.'||c_proc_name||'.Start Mbr Attr_Assign_Update',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16638:
16639: fem_multi_proc_pkg.MASTER
16640: (X_PRG_STAT => v_mp_prg_status
16676:
16677: -- accumulate rejected row count
16678: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16679: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16680: FEM_ENGINES_PKG.TECH_MESSAGE
16681: (c_log_level_1,c_block||'.'||c_proc_name||
16682: '.v_rows_rejected_accum',v_rows_rejected_accum);
16683:
16684: FEM_ENGINES_PKG.TECH_MESSAGE
16680: FEM_ENGINES_PKG.TECH_MESSAGE
16681: (c_log_level_1,c_block||'.'||c_proc_name||
16682: '.v_rows_rejected_accum',v_rows_rejected_accum);
16683:
16684: FEM_ENGINES_PKG.TECH_MESSAGE
16685: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Attr_Assign',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16686:
16687: FEM_ENGINES_PKG.TECH_MESSAGE
16688: (c_log_level_3,c_block||'.'||c_proc_name||'.Begin Attr Overlap Processing',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16683:
16684: FEM_ENGINES_PKG.TECH_MESSAGE
16685: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Attr_Assign',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16686:
16687: FEM_ENGINES_PKG.TECH_MESSAGE
16688: (c_log_level_3,c_block||'.'||c_proc_name||'.Begin Attr Overlap Processing',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16689:
16690: --------------------------------------------------------------
16691: -- Perform the Date Overlap Check for CAL_PERIOD loads
16709: ,P_FAILED_REQ_ID => null
16710: ,P_ARG1 => 'ATTR_ASSIGN_UPDATE'
16711: ,P_ARG2 => p_master_request_id);
16712:
16713: FEM_ENGINES_PKG.TECH_MESSAGE
16714: (c_log_level_1,c_block||'.'||c_proc_name||'.v_mp_exception',v_mp_exception_code);
16715:
16716: IF v_mp_prg_status NOT IN ('COMPLETE:NORMAL') THEN
16717: IF v_mp_exception_code IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
16722: END IF;
16723: -- accumulate rejected row count
16724: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16725: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16726: FEM_ENGINES_PKG.TECH_MESSAGE
16727: (c_log_level_1,c_block||'.'||c_proc_name||
16728: '.v_rows_rejected_accum',v_rows_rejected_accum);
16729: FEM_ENGINES_PKG.TECH_MESSAGE
16730: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Attr Overlap Processing',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16725: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16726: FEM_ENGINES_PKG.TECH_MESSAGE
16727: (c_log_level_1,c_block||'.'||c_proc_name||
16728: '.v_rows_rejected_accum',v_rows_rejected_accum);
16729: FEM_ENGINES_PKG.TECH_MESSAGE
16730: (c_log_level_3,c_block||'.'||c_proc_name||'.Finish Attr Overlap Processing',to_char(sysdate,'MM/DD/YYY:HH:MI:SS'));
16731:
16732: END IF; -- overlap check for CAL_PERIOD load
16733:
16739: -- Post Dimension Member load status to FEM_DIM_LOAD_STATUS
16740: ----------------------------------------------------------------------------------
16741: IF v_simple_dimension_flag = 'N' THEN
16742: v_src_sys_last_row := NVL(tg_src_system_dc.LAST,0);
16743: FEM_ENGINES_PKG.TECH_MESSAGE
16744: (c_log_level_1,c_block||'.'||c_proc_name||'.Step10.v_src_sys_last_row'
16745: ,v_src_sys_last_row);
16746:
16747: FOR i IN 1..v_src_sys_last_row
16745: ,v_src_sys_last_row);
16746:
16747: FOR i IN 1..v_src_sys_last_row
16748: LOOP
16749: FEM_ENGINES_PKG.TECH_MESSAGE
16750: (c_log_level_1,c_block||'.'||c_proc_name||'.Step10.tg_src_system_dc'
16751: ,tg_src_system_dc(i));
16752:
16753: Post_dim_status (v_dimension_id
16756: END LOOP;
16757: END IF; -- src_system Post_dim_status
16758:
16759: x_rows_rejected_accum := v_rows_rejected_accum;
16760: FEM_ENGINES_PKG.TECH_MESSAGE
16761: (c_log_level_1,c_block||'.'||c_proc_name||
16762: '.x_rows_rejected_accum',x_rows_rejected_accum);
16763:
16764: -----------------------------------------------------------------------------------
16777:
16778: ELSIF v_composite_dimension_flag = 'Y' THEN
16779:
16780:
16781: fem_engines_pkg.tech_message(
16782: p_severity => c_log_level_2
16783: ,p_module => c_block||'.'||c_proc_name||'.Before Process_Rows'
16784: ,p_msg_text => 'Calling Process_Rows via MP Framework');
16785:
16807: get_mp_rows_rejected (v_rows_rejected, v_rows_loaded);
16808: v_rows_rejected_accum := v_rows_rejected_accum + v_rows_rejected;
16809: x_rows_rejected_accum := v_rows_rejected_accum;
16810:
16811: fem_engines_pkg.tech_message(
16812: p_severity => c_log_level_5
16813: ,p_module => c_block||'.'||c_proc_name||'.After Process_Rows'
16814: ,p_msg_text => 'Process_Rows Status '||v_mp_prg_status||
16815: 'Process_Rows Err Code '||v_mp_exception_code);
16821: IF v_mp_prg_status NOT IN ('COMPLETE:NORMAL') THEN
16822:
16823: IF v_mp_exception_code IN ('FEM_MP_NO_DATA_SLICES_ERR') THEN
16824:
16825: fem_engines_pkg.tech_message(
16826: p_severity => c_log_level_1
16827: ,p_module => c_block||'.'||c_proc_name||'.After Process_Rows'
16828: ,p_msg_text => 'Process_Rows Error '||v_mp_exception_code);
16829:
16835:
16836: END IF;
16837:
16838:
16839: fem_engines_pkg.tech_message(
16840: p_severity => c_log_level_1
16841: ,p_module => c_block||'.'||c_proc_name||'.After Process_Rows'
16842: ,p_msg_text => 'End of Process_Rows');
16843:
16855:
16856: EXCEPTION
16857: WHEN e_terminate THEN
16858:
16859: fem_engines_pkg.tech_message(
16860: p_severity => c_log_level_4
16861: ,p_module => c_block||'.'||c_proc_name||'. Load_Dimension'
16862: ,p_msg_text => 'Process_Rows Error '||v_mp_exception_code);
16863:
16866: WHEN e_exec_lock_exists THEN
16867: gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
16868: IF v_num_msg > 0 THEN
16869: FOR i IN 1 .. v_num_msg LOOP
16870: FEM_ENGINES_PKG.User_Message
16871: (p_msg_text => FND_MSG_PUB.Get(p_encoded => 'F'));
16872: END LOOP;
16873: END IF;
16874:
16871: (p_msg_text => FND_MSG_PUB.Get(p_encoded => 'F'));
16872: END LOOP;
16873: END IF;
16874:
16875: FEM_ENGINES_PKG.TECH_MESSAGE
16876: (p_severity => c_log_level_5
16877: ,p_module => c_block||'.'||c_proc_name||'.Exception'
16878: ,p_app_name => c_fem
16879: ,p_msg_name => G_EXEC_LOCK_EXISTS
16879: ,p_msg_name => G_EXEC_LOCK_EXISTS
16880: ,P_TOKEN1 => 'OBJECT_ID'
16881: ,P_VALUE1 => v_object_id);
16882:
16883: FEM_ENGINES_PKG.USER_MESSAGE
16884: (P_APP_NAME => c_fem
16885: ,P_MSG_NAME => G_EXEC_LOCK_EXISTS
16886: ,P_TOKEN1 => 'OBJECT_ID'
16887: ,P_VALUE1 => v_object_id);
16891: gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
16892: gv_prg_msg := sqlerrm;
16893: gv_callstack := dbms_utility.format_call_stack;
16894:
16895: FEM_ENGINES_PKG.TECH_MESSAGE
16896: (p_severity => c_log_level_6
16897: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
16898: ,p_msg_text => gv_prg_msg);
16899:
16896: (p_severity => c_log_level_6
16897: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
16898: ,p_msg_text => gv_prg_msg);
16899:
16900: FEM_ENGINES_PKG.TECH_MESSAGE
16901: (p_severity => c_log_level_6
16902: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
16903: ,p_msg_text => gv_callstack);
16904:
16901: (p_severity => c_log_level_6
16902: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
16903: ,p_msg_text => gv_callstack);
16904:
16905: FEM_ENGINES_PKG.USER_MESSAGE
16906: (p_app_name => c_fem
16907: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
16908: ,P_TOKEN1 => 'ERR_MSG'
16909: ,P_VALUE1 => gv_prg_msg);
16907: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
16908: ,P_TOKEN1 => 'ERR_MSG'
16909: ,P_VALUE1 => gv_prg_msg);
16910:
16911: -- FEM_ENGINES_PKG.USER_MESSAGE
16912: -- (p_app_name => c_fem
16913: -- ,p_msg_text => gv_prg_msg);
16914:
16915: RAISE e_main_terminate;
17042: END;
17043: EXCEPTION
17044: WHEN e_dimension_not_found THEN
17045:
17046: FEM_ENGINES_PKG.TECH_MESSAGE
17047: (p_severity => c_log_level_5
17048: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17049: ,p_app_name => c_fem
17050: ,p_msg_name => G_DIM_NOT_FOUND);
17048: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17049: ,p_app_name => c_fem
17050: ,p_msg_name => G_DIM_NOT_FOUND);
17051:
17052: FEM_ENGINES_PKG.USER_MESSAGE
17053: (p_app_name => c_fem
17054: ,p_msg_name => G_DIM_NOT_FOUND);
17055:
17056: RAISE e_main_terminate;
17056: RAISE e_main_terminate;
17057:
17058: WHEN e_dim_load_not_enabled THEN
17059:
17060: FEM_ENGINES_PKG.TECH_MESSAGE
17061: (p_severity => c_log_level_5
17062: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17063: ,p_app_name => c_fem
17064: ,p_msg_name => G_INVALID_SIMPLE_DIM);
17062: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17063: ,p_app_name => c_fem
17064: ,p_msg_name => G_INVALID_SIMPLE_DIM);
17065:
17066: FEM_ENGINES_PKG.USER_MESSAGE
17067: (p_app_name => c_fem
17068: ,p_msg_name => G_INVALID_SIMPLE_DIM);
17069:
17070: RAISE e_main_terminate;
17145: when the dimension is invalid. In this case, we are not
17146: updating the records with a STATUS
17147: FOR dim IN cv_dimension LOOP
17148: v_dimension_varchar_label := dim.dimension_varchar_label;
17149: FEM_ENGINES_PKG.TECH_MESSAGE
17150: (c_log_level_1,c_block||'.'||c_proc_name||'.dimension'
17151: ,v_dimension_varchar_label);
17152:
17153:
17222: -- Engine Master Post Processing
17223: -- This step updates the status tables with number of rows processed,
17224: -- number of error rows, etc.
17225: ----------------------------------------------------------------------------------
17226: FEM_ENGINES_PKG.TECH_MESSAGE
17227: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_rejected_accum',v_rows_rejected_accum);
17228:
17229: FEM_ENGINES_PKG.TECH_MESSAGE
17230: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_to_load_accum',v_rows_to_load_accum);
17225: ----------------------------------------------------------------------------------
17226: FEM_ENGINES_PKG.TECH_MESSAGE
17227: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_rejected_accum',v_rows_rejected_accum);
17228:
17229: FEM_ENGINES_PKG.TECH_MESSAGE
17230: (c_log_level_1,c_block||'.'||c_proc_name||'.v_rows_to_load_accum',v_rows_to_load_accum);
17231:
17232: Eng_Master_Post_Proc (v_object_id
17233: ,v_rows_rejected_accum
17242: FROM fem_folders_vl
17243: WHERE folder_id=1000;
17244:
17245:
17246: FEM_ENGINES_PKG.TECH_MESSAGE
17247: (p_severity => c_log_level_5
17248: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17249: ,p_app_name => c_fem
17250: ,p_msg_name => G_NO_FOLDER_PRIVS
17250: ,p_msg_name => G_NO_FOLDER_PRIVS
17251: ,P_TOKEN1 => 'FOLDER'
17252: ,P_VALUE1 => v_sec_folder_name);
17253:
17254: FEM_ENGINES_PKG.USER_MESSAGE
17255: (p_app_name => c_fem
17256: ,p_msg_name => G_NO_FOLDER_PRIVS
17257: ,P_TOKEN1 => 'FOLDER'
17258: ,P_VALUE1 => v_sec_folder_name);
17260: gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
17261:
17262: WHEN e_invalid_exec_mode THEN
17263:
17264: FEM_ENGINES_PKG.TECH_MESSAGE
17265: (p_severity => c_log_level_5
17266: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17267: ,p_app_name => c_fem
17268: ,p_msg_name => G_INVALID_EXEC_MODE);
17266: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17267: ,p_app_name => c_fem
17268: ,p_msg_name => G_INVALID_EXEC_MODE);
17269:
17270: FEM_ENGINES_PKG.USER_MESSAGE
17271: (p_app_name => c_fem
17272: ,p_msg_name => G_INVALID_EXEC_MODE);
17273:
17274: gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
17304: X_RETURN_STATUS => v_API_return_status);
17305:
17306: WHEN e_invalid_obj_def THEN
17307:
17308: FEM_ENGINES_PKG.TECH_MESSAGE
17309: (p_severity => c_log_level_5
17310: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17311: ,p_app_name => c_fem
17312: ,p_msg_name => G_INVALID_OBJ_DEF);
17310: ,p_module => c_block||'.'||c_proc_name||'.Exception'
17311: ,p_app_name => c_fem
17312: ,p_msg_name => G_INVALID_OBJ_DEF);
17313:
17314: FEM_ENGINES_PKG.USER_MESSAGE
17315: (p_app_name => c_fem
17316: ,p_msg_name => G_INVALID_OBJ_DEF
17317: ,P_TOKEN1 => 'OBJECT'
17318: ,P_VALUE1 => null);
17329: gv_concurrent_status := fnd_concurrent.set_completion_status('ERROR',null);
17330: gv_prg_msg := sqlerrm;
17331: gv_callstack := dbms_utility.format_call_stack;
17332:
17333: FEM_ENGINES_PKG.TECH_MESSAGE
17334: (p_severity => c_log_level_6
17335: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
17336: ,p_msg_text => gv_prg_msg);
17337:
17334: (p_severity => c_log_level_6
17335: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
17336: ,p_msg_text => gv_prg_msg);
17337:
17338: FEM_ENGINES_PKG.TECH_MESSAGE
17339: (p_severity => c_log_level_6
17340: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
17341: ,p_msg_text => gv_callstack);
17342:
17339: (p_severity => c_log_level_6
17340: ,p_module => c_block||'.'||c_proc_name||'.Unexpected Exception'
17341: ,p_msg_text => gv_callstack);
17342:
17343: FEM_ENGINES_PKG.USER_MESSAGE
17344: (p_app_name => c_fem
17345: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
17346: ,P_TOKEN1 => 'ERR_MSG'
17347: ,P_VALUE1 => gv_prg_msg);
17345: ,p_msg_name => 'FEM_UNEXPECTED_ERROR'
17346: ,P_TOKEN1 => 'ERR_MSG'
17347: ,P_VALUE1 => gv_prg_msg);
17348: /*
17349: FEM_ENGINES_PKG.USER_MESSAGE
17350: (p_app_name => c_fem
17351: ,p_msg_text => gv_prg_msg); */
17352:
17353: