[Home] [Help]
676:
677: -- Initialize Message Stack on FND_MSG_PUB
678: FND_MSG_PUB.Initialize;
679:
680: FEM_ENGINES_PKG.tech_message (
681: p_severity => g_log_level_2
682: ,p_module => G_BLOCK||'.'||l_api_name
683: ,p_msg_text => 'BEGIN'
684: );
682: ,p_module => G_BLOCK||'.'||l_api_name
683: ,p_msg_text => 'BEGIN'
684: );
685:
686: FEM_ENGINES_PKG.tech_message (
687: p_severity => g_log_level_1
688: ,p_module => G_BLOCK||'.'||l_api_name
689: ,p_msg_text =>
690: ' p_execution_mode='||p_execution_mode||
700: l_request_id := FND_GLOBAL.conc_request_id;
701: l_pgm_id := FND_GLOBAL.conc_program_id;
702: l_pgm_app_id := FND_GLOBAL.prog_appl_id;
703:
704: FEM_ENGINES_PKG.tech_message (
705: p_severity => g_log_level_1
706: ,p_module => G_BLOCK||'.'||l_api_name
707: ,p_msg_text =>
708: ' l_user_id='||l_user_id||
734: exception
735: when no_data_found then
736: l_loader_object_id := 1400;
737: when others then
738: FEM_ENGINES_PKG.user_message (
739: p_app_name => G_FEM
740: ,p_msg_name => G_EXT_LDR_BAD_LDR_OBJ_ERR
741: ,p_token1 => 'OBJECT_DEFINITION_ID'
742: ,p_value1 => l_loader_obj_def_id
764: ,fem_object_catalog_b o
765: where o.object_id = l_loader_object_id
766: and f.folder_id = o.folder_id;
767:
768: FEM_ENGINES_PKG.user_message (
769: p_app_name => G_FEM
770: ,p_msg_name => G_EXEC_NO_FOLDER_ACCESS_ERR
771: ,p_token1 => 'FOLDER_NAME'
772: ,p_value1 => l_loader_folder_name
778: ------------------------------------------------------------------------------
779: -- Validate the execution mode input parameter.
780: ------------------------------------------------------------------------------
781: if p_execution_mode not in (g_snapshot,g_error_reprocessing) then
782: FEM_ENGINES_PKG.user_message (
783: p_app_name => G_FEM
784: ,p_msg_name => G_EXT_LDR_EXEC_MODE_ERR
785: );
786: raise e_loader_error;
1028: -- STEP 1: Check to see that the specified hierarchy object name and
1029: -- hierarchy object definition name exist in FEM_HIERARCHIES_T for the
1030: -- given dimension.
1031: ------------------------------------------------------------------------------
1032: FEM_ENGINES_PKG.tech_message (
1033: p_severity => g_log_level_1
1034: ,p_module => G_BLOCK||'.'||l_api_name
1035: ,p_msg_text => 'Step 1: Hierarchy Count in FEM_HIERARCHIES_T'
1036: );
1049: ,p_dimension_varchar_label;
1050:
1051: if (l_count = 0) then
1052: -- Hierarchy not found in FEM_HIERARCHIES_T
1053: FEM_ENGINES_PKG.user_message (
1054: p_app_name => G_FEM
1055: ,p_msg_name => G_HIER_LDR_NO_HIER_ERR
1056: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1057: ,p_value1 => p_hierarchy_object_name
1060: );
1061: raise e_loader_error;
1062: elsif (l_count > 1) then
1063: -- Multiple hierarchy definitions found in FEM_HIERARCHIES_T
1064: FEM_ENGINES_PKG.user_message (
1065: p_app_name => G_FEM
1066: ,p_msg_name => G_HIER_LDR_MULTI_HIER_ERR
1067: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1068: ,p_value1 => p_hierarchy_object_name
1075:
1076: ------------------------------------------------------------------------------
1077: -- STEP 2: Register the process execution in the Processing Locks data model
1078: ------------------------------------------------------------------------------
1079: FEM_ENGINES_PKG.tech_message (
1080: p_severity => g_log_level_1
1081: ,p_module => G_BLOCK||'.'||l_api_name
1082: ,p_msg_text => 'Step 2: Register Process Execution'
1083: );
1133:
1134: <
1135: loop
1136:
1137: FEM_ENGINES_PKG.tech_message (
1138: p_severity => g_log_level_1
1139: ,p_module => G_BLOCK||'.'||l_api_name||'.cv_get_hier_defs'
1140: ,p_msg_text =>
1141: ' ld_folder_name='||ld_folder_name||
1158: ------------------------------------------------------------------------------
1159: -- STEP 3: Check for Existing Object ID and Object Definition ID, and check
1160: -- Effective Dates.
1161: ------------------------------------------------------------------------------
1162: FEM_ENGINES_PKG.tech_message (
1163: p_severity => g_log_level_1
1164: ,p_module => G_BLOCK||'.'||l_api_name
1165: ,p_msg_text => 'Step 3: Catalog of Objects record and effective date checks'
1166: );
1168: ------------------------------------------------------------------------------
1169: -- STEP 3.1: Check to see if there is an existing object id with the same
1170: -- hierarchy object name and dimension id.
1171: ------------------------------------------------------------------------------
1172: FEM_ENGINES_PKG.tech_message (
1173: p_severity => g_log_level_1
1174: ,p_module => G_BLOCK||'.'||l_api_name
1175: ,p_msg_text => 'Step 3.1: Check for Existing Object ID'
1176: );
1203: and cat.object_type_code = 'HIERARCHY'
1204: and h.hierarchy_obj_id = cat.object_id
1205: and h.dimension_id = l_dimension_id;
1206:
1207: FEM_ENGINES_PKG.tech_message (
1208: p_severity => g_log_level_1
1209: ,p_module => G_BLOCK||'.'||l_api_name||'.get_hier_obj_id'
1210: ,p_msg_text =>
1211: ' ld_hierarchy_object_id='||ld_hierarchy_object_id||
1227: -- STEP 3.2: For an existing object id, check to see if there is an existing
1228: -- object definition id with the same hierarchy object definition display
1229: -- name.
1230: ------------------------------------------------------------------------------
1231: FEM_ENGINES_PKG.tech_message (
1232: p_severity => g_log_level_1
1233: ,p_module => G_BLOCK||'.'||l_api_name
1234: ,p_msg_text => 'Step 3.2: Check for Existing Object Definition ID'
1235: );
1248: where object_id = ld_hierarchy_object_id
1249: and display_name = ld_hier_obj_def_display_name
1250: and old_approved_copy_flag = 'N';
1251:
1252: FEM_ENGINES_PKG.tech_message (
1253: p_severity => g_log_level_1
1254: ,p_module => G_BLOCK||'.'||l_api_name||'.get_hier_obj_def_id'
1255: ,p_msg_text =>
1256: ' ld_hier_obj_def_id='||ld_hier_obj_def_id||
1272: -- overlaps with existing object definitions. If the user does not specify
1273: -- values for the effective start and end dates, then we must try to provide
1274: -- default values.
1275: ------------------------------------------------------------------------------
1276: FEM_ENGINES_PKG.tech_message (
1277: p_severity => g_log_level_1
1278: ,p_module => G_BLOCK||'.'||l_api_name
1279: ,p_msg_text => 'Step 3.3: Effective Date Validations'
1280: );
1300:
1301: -- Check that start date is not greater than end date
1302: if (ld_effective_start_date > ld_effective_end_date) then
1303:
1304: FEM_ENGINES_PKG.user_message (
1305: p_app_name => G_FEM
1306: ,p_msg_name => G_HIER_LDR_EFF_DATE_RANG_ERR
1307: ,p_token1 => 'END_DATE'
1308: ,p_value1 => FND_DATE.date_to_chardate(ld_effective_end_date)
1345: -- If the largest end date is greater than the default end date, then
1346: -- the largest object definition is invalid.
1347: if (l_max_effective_end_date > get_default_end_date) then
1348:
1349: FEM_ENGINES_PKG.user_message (
1350: p_app_name => G_FEM
1351: ,p_msg_name => G_HIER_LDR_EFF_DATE_OVLP_ERR
1352: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1353: ,p_value1 => ld_hierarchy_object_name
1368: -- the largest object definition will be updated to have an
1369: -- end date that is smaller than its start date.
1370: if (l_max_effective_start_date > l_current_date) then
1371:
1372: FEM_ENGINES_PKG.user_message (
1373: p_app_name => G_FEM
1374: ,p_msg_name => G_HIER_LDR_EFF_DATE_OVLP_ERR
1375: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1376: ,p_value1 => ld_hierarchy_object_name
1407: p_msg_count => l_msg_count
1408: ,p_msg_data => l_msg_data
1409: );
1410:
1411: FEM_ENGINES_PKG.user_message (
1412: p_app_name => G_FEM
1413: ,p_msg_name => G_HIER_LDR_DEF_DATA_RANG_ERR
1414: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1415: ,p_value1 => ld_hierarchy_object_name
1439:
1440: -- Check that start date is not greater than end date
1441: if (ld_effective_start_date > ld_effective_end_date) then
1442:
1443: FEM_ENGINES_PKG.user_message (
1444: p_app_name => G_FEM
1445: ,p_msg_name => G_HIER_LDR_EFF_DATE_RANG_ERR
1446: ,p_token1 => 'END_DATE'
1447: ,p_value1 => FND_DATE.date_to_chardate(ld_effective_end_date)
1472: p_msg_count => l_msg_count
1473: ,p_msg_data => l_msg_data
1474: );
1475:
1476: FEM_ENGINES_PKG.user_message (
1477: p_app_name => G_FEM
1478: ,p_msg_name => G_HIER_LDR_EFF_DATE_OVLP_ERR
1479: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1480: ,p_value1 => ld_hierarchy_object_name
1492:
1493: ------------------------------------------------------------------------------
1494: -- STEP 3.4: Update Hierarchy Definition Validations.
1495: ------------------------------------------------------------------------------
1496: FEM_ENGINES_PKG.tech_message (
1497: p_severity => g_log_level_1
1498: ,p_module => G_BLOCK||'.'||l_api_name
1499: ,p_msg_text => 'Step 3.4: Update Hierarchy Definition Validations'
1500: );
1503:
1504: ------------------------------------------------------------------------------
1505: -- STEP 3.4.1: Check for Edit or Approval Lock.
1506: ------------------------------------------------------------------------------
1507: FEM_ENGINES_PKG.tech_message (
1508: p_severity => g_log_level_1
1509: ,p_module => G_BLOCK||'.'||l_api_name
1510: ,p_msg_text => 'Step 3.4.1: Check for Edit or Approval Lock'
1511: );
1515: ,x_approval_edit_lock_exists => l_approval_edit_lock_exists
1516: ,x_data_edit_lock_exists => l_data_edit_lock_exists
1517: );
1518:
1519: FEM_ENGINES_PKG.tech_message (
1520: p_severity => g_log_level_1
1521: ,p_module => G_BLOCK||'.'||l_api_name||'.get_object_def_edit_locks'
1522: ,p_msg_text => 'l_approval_edit_lock_exists='||l_approval_edit_lock_exists||' l_data_edit_lock_exists='||l_data_edit_lock_exists
1523: );
1524:
1525: if ( FND_API.To_Boolean(l_approval_edit_lock_exists)
1526: or FND_API.To_Boolean(l_data_edit_lock_exists) ) then
1527:
1528: FEM_ENGINES_PKG.user_message (
1529: p_app_name => G_FEM
1530: ,p_msg_name => G_HIER_LDR_DEF_DATA_LOCK_ERR
1531: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1532: ,p_value1 => ld_hierarchy_object_name
1541:
1542: ------------------------------------------------------------------------------
1543: -- STEP 3.4.2: Check for Read Only Relationships.
1544: ------------------------------------------------------------------------------
1545: FEM_ENGINES_PKG.tech_message (
1546: p_severity => g_log_level_1
1547: ,p_module => G_BLOCK||'.'||l_api_name
1548: ,p_msg_text => 'Step 3.4.2: Check for Read Only Relationships'
1549: );
1559: if (l_count > 0) then
1560:
1561: -- Cannot update an existing hierarchy object definition if it
1562: -- has read only parent-child relationships.
1563: FEM_ENGINES_PKG.user_message (
1564: p_app_name => G_FEM
1565: ,p_msg_name => G_HIER_LDR_READONLY_HIER_ERR
1566: );
1567:
1577: ------------------------------------------------------------------------------
1578: -- STEP 4: Check to see if the user executing the hierarchy load has write
1579: -- access to the folder. If yes, the obtain the folder id.
1580: ------------------------------------------------------------------------------
1581: FEM_ENGINES_PKG.tech_message (
1582: p_severity => g_log_level_1
1583: ,p_module => G_BLOCK||'.'||l_api_name
1584: ,p_msg_text => 'Step 4: Folder Validation'
1585: );
1595: and uf.write_flag = 'Y'
1596: and f.folder_name = ld_folder_name;
1597: exception
1598: when no_data_found then
1599: FEM_ENGINES_PKG.user_message (
1600: p_app_name => G_FEM
1601: ,p_msg_name => G_HIER_LDR_FOLDER_ERR
1602: ,p_token1 => 'FOLDER_NAME'
1603: ,p_value1 => ld_folder_name
1611: -- hierarchy object validations.
1612: ------------------------------------------------------------------------------
1613: if (ld_load_type = g_new_hier) then
1614:
1615: FEM_ENGINES_PKG.tech_message (
1616: p_severity => g_log_level_1
1617: ,p_module => G_BLOCK||'.'||l_api_name
1618: ,p_msg_text => 'Step 5: New Hierarchy Validations'
1619: );
1621: ------------------------------------------------------------------------------
1622: -- STEP 5.1: Verify that HIERARCHY_TYPE_CODE on FEM_HIERARCHIES_T
1623: -- is valid (OPEN, RECONCILIATION, DAG).
1624: ------------------------------------------------------------------------------
1625: FEM_ENGINES_PKG.tech_message (
1626: p_severity => g_log_level_1
1627: ,p_module => G_BLOCK||'.'||l_api_name
1628: ,p_msg_text => 'Step 5.1: HIERARCHY_TYPE_CODE Validation'
1629: );
1635: where lookup_type = 'FEM_HIERARCHY_TYPE_DSC'
1636: and lookup_code = ld_hierarchy_type_code;
1637: exception
1638: when no_data_found then
1639: FEM_ENGINES_PKG.user_message (
1640: p_app_name => G_FEM
1641: ,p_msg_name => G_HIER_LDR_HIER_TYPE_CD_ERR
1642: ,p_token1 => 'HIERARCHY_TYPE_CODE'
1643: ,p_value1 => ld_hierarchy_type_code
1647:
1648: if ( ( l_hier_type_allowed_code <> 'ALL')
1649: and ( l_hier_type_allowed_code <> ld_hierarchy_type_code) ) then
1650:
1651: FEM_ENGINES_PKG.user_message (
1652: p_app_name => G_FEM
1653: ,p_msg_name => G_HIER_LDR_HIER_TYPE_VAL_ERR
1654: ,p_token1 => 'HIERARCHY_TYPE_CODE'
1655: ,p_value1 => l_hier_type_allowed_code
1661: ------------------------------------------------------------------------------
1662: -- STEP 5.2: Verify that HIERARCHY_USAGE_CODE on FEM_HIERARCHIES_T
1663: -- is valid (PLANNING, STANDARD, CONSOLIDATION).
1664: ------------------------------------------------------------------------------
1665: FEM_ENGINES_PKG.tech_message (
1666: p_severity => g_log_level_1
1667: ,p_module => G_BLOCK||'.'||l_api_name
1668: ,p_msg_text => 'Step 5.2: HIERARCHY_USAGE_CODE Validation'
1669: );
1675: where lookup_type = 'FEM_HIERARCHY_USAGE_DSC'
1676: and lookup_code = ld_hierarchy_usage_code;
1677: exception
1678: when no_data_found then
1679: FEM_ENGINES_PKG.user_message (
1680: p_app_name => G_FEM
1681: ,p_msg_name => G_HIER_LDR_HIER_USG_CD_ERR
1682: ,p_token1 => 'HIERARCHY_USAGE_CODE'
1683: ,p_value1 => ld_hierarchy_usage_code
1688: ------------------------------------------------------------------------------
1689: -- STEP 5.3: Verify that GROUP_SEQUENCE_ENFORCED_CODE on FEM_HIERARCHIES_T
1690: -- is valid.
1691: ------------------------------------------------------------------------------
1692: FEM_ENGINES_PKG.tech_message (
1693: p_severity => g_log_level_1
1694: ,p_module => G_BLOCK||'.'||l_api_name
1695: ,p_msg_text => 'Step 5.3: GROUP_SEQUENCE_ENFORCED_CODE Validation'
1696: );
1702: where lookup_type = 'FEM_GROUP_SEQ_ENFORCED_DSC'
1703: and lookup_code = ld_group_seq_enforced_code;
1704: exception
1705: when no_data_found then
1706: FEM_ENGINES_PKG.user_message (
1707: p_app_name => G_FEM
1708: ,p_msg_name => G_HIER_LDR_GRP_SQ_ENF_CD_ERR
1709: ,p_token1 => 'GROUP_SEQ_ENFORCED_CODE'
1710: ,p_value1 => ld_group_seq_enforced_code
1715: if (l_group_use_code = 'REQUIRED') then
1716:
1717: if (ld_group_seq_enforced_code = 'NO_GROUPS') then
1718:
1719: FEM_ENGINES_PKG.user_message (
1720: p_app_name => G_FEM
1721: ,p_msg_name => G_HIER_LDR_GRP_SQ_REQ_ERR
1722: );
1723: l_hierarchy_error_flag := true;
1727: elsif (l_group_use_code = 'NOT_SUPPORTED') then
1728:
1729: if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
1730:
1731: FEM_ENGINES_PKG.user_message (
1732: p_app_name => G_FEM
1733: ,p_msg_name => G_HIER_LDR_GRP_SQ_VAL_ERR
1734: ,p_token1 => 'GROUP_SEQ_ENFORCED_CODE'
1735: ,p_value1 => 'NO_GROUPS'
1742:
1743: ------------------------------------------------------------------------------
1744: -- STEP 5.4: Verify that MULTI_TOP_FLAG on FEM_HIERARCHIES_T is valid (Y,N).
1745: ------------------------------------------------------------------------------
1746: FEM_ENGINES_PKG.tech_message (
1747: p_severity => g_log_level_1
1748: ,p_module => G_BLOCK||'.'||l_api_name
1749: ,p_msg_text => 'Step 5.4: MULTI_TOP_FLAG Validation'
1750: );
1750: );
1751:
1752: if (ld_multi_top_flag not in ('Y','N')) then
1753:
1754: FEM_ENGINES_PKG.user_message (
1755: p_app_name => G_FEM
1756: ,p_msg_name => G_HIER_LDR_MULTI_TOP_FLG_ERR
1757: ,p_token1 => 'MULTI_TOP_FLAG'
1758: ,p_value1 => ld_multi_top_flag
1764: ------------------------------------------------------------------------------
1765: -- STEP 5.5: Verify that MULTI_VALUE_SET_FLAG on FEM_HIERARCHIES_T is
1766: -- valid (Y,N).
1767: ------------------------------------------------------------------------------
1768: FEM_ENGINES_PKG.tech_message (
1769: p_severity => g_log_level_1
1770: ,p_module => G_BLOCK||'.'||l_api_name
1771: ,p_msg_text => 'Step 5.5: MULTI_VALUE_SET_FLAG Validation'
1772: );
1772: );
1773:
1774: if (ld_multi_value_set_flag not in ('Y','N')) then
1775:
1776: FEM_ENGINES_PKG.user_message (
1777: p_app_name => G_FEM
1778: ,p_msg_name => G_HIER_LDR_MULTI_VS_FLG_ERR
1779: ,p_token1 => 'MULTI_VALUE_SET_FLAG'
1780: ,p_value1 => ld_multi_value_set_flag
1785:
1786: if ( (l_value_set_required_flag = 'N')
1787: and (ld_multi_value_set_flag = 'Y') ) then
1788:
1789: FEM_ENGINES_PKG.user_message (
1790: p_app_name => G_FEM
1791: ,p_msg_name => G_HIER_LDR_MULT_VS_FLG_V_ERR
1792: ,p_token1 => 'MULTI_VALUE_SET_FLAG'
1793: ,p_value1 => 'Y'
1801: ------------------------------------------------------------------------------
1802: -- STEP 5.6: Verify that FLATTENED_ROWS_FLAG on FEM_HIERARCHIES_T is
1803: -- valid (Y,N).
1804: ------------------------------------------------------------------------------
1805: FEM_ENGINES_PKG.tech_message (
1806: p_severity => g_log_level_1
1807: ,p_module => G_BLOCK||'.'||l_api_name
1808: ,p_msg_text => 'Step 5.6: FLATTENED_ROWS_FLAG Validation'
1809: );
1809: );
1810:
1811: if (ld_flattened_rows_flag not in ('Y','N')) then
1812:
1813: FEM_ENGINES_PKG.user_message (
1814: p_app_name => G_FEM
1815: ,p_msg_name => G_HIER_LDR_FLAT_ROWS_FLG_ERR
1816: ,p_token1 => 'FLATTENED_ROWS_FLAG'
1817: ,p_value1 => ld_flattened_rows_flag
1826: -- FEM_HIERARCHIES_T.
1827: ------------------------------------------------------------------------------
1828: if (p_dimension_varchar_label = 'CAL_PERIOD') then
1829:
1830: FEM_ENGINES_PKG.tech_message (
1831: p_severity => g_log_level_1
1832: ,p_module => G_BLOCK||'.'||l_api_name
1833: ,p_msg_text => 'Step 5.7: CALENDAR_DISPLAY_CODE Validation'
1834: );
1839: from fem_calendars_b
1840: where calendar_display_code = ld_calendar_dc;
1841: exception
1842: when no_data_found then
1843: FEM_ENGINES_PKG.user_message (
1844: p_app_name => G_FEM
1845: ,p_msg_name => G_HIER_LDR_CALENDAR_ERR
1846: ,p_token1 => 'CALENDAR_DISPLAY_CODE'
1847: ,p_value1 => ld_calendar_dc
1855: -- STEP 5.8: Value Set validations for new hierarchies.
1856: ------------------------------------------------------------------------------
1857: if (l_value_set_required_flag = 'Y') then
1858:
1859: FEM_ENGINES_PKG.tech_message (
1860: p_severity => g_log_level_1
1861: ,p_module => G_BLOCK||'.'||l_api_name
1862: ,p_msg_text => 'Step 5.8: Value Set Validations'
1863: );
1866: -- STEP 5.8.1: Verify that MULTI_VALUE_SET_FLAG on FEM_HIERARCHIES_T
1867: -- is valid. If flag ='N', check that only one value set row is listed in
1868: -- FEM_HIER_VALUE_SETS_T for that hierarchy.
1869: ------------------------------------------------------------------------------
1870: FEM_ENGINES_PKG.tech_message (
1871: p_severity => g_log_level_1
1872: ,p_module => G_BLOCK||'.'||l_api_name
1873: ,p_msg_text => 'Step 5.8.1: MULTI_VALUE_SET_FLAG Validation'
1874: );
1883: using ld_hierarchy_object_name;
1884:
1885: if (l_vs_count = 0) then
1886:
1887: FEM_ENGINES_PKG.user_message (
1888: p_app_name => G_FEM
1889: ,p_msg_name => G_HIER_LDR_NO_HIER_VS_ERR
1890: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
1891: ,p_value1 => ld_hierarchy_object_name
1923: -- STEP 5.9: Dimension Group validations.
1924: ------------------------------------------------------------------------------
1925: if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
1926:
1927: FEM_ENGINES_PKG.tech_message (
1928: p_severity => g_log_level_1
1929: ,p_module => G_BLOCK||'.'||l_api_name
1930: ,p_msg_text => 'Step 5.9: Dimension Group Validations'
1931: );
1932:
1933: ------------------------------------------------------------------------------
1934: -- STEP 5.9.1: Verify that FEM_HIER_DIM_GRPS_T is not empty
1935: ------------------------------------------------------------------------------
1936: FEM_ENGINES_PKG.tech_message (
1937: p_severity => g_log_level_1
1938: ,p_module => G_BLOCK||'.'||l_api_name
1939: ,p_msg_text => 'Step 5.9.1: FEM_HIER_DIM_GRPS_T Not Empty Validation'
1940: );
1950: using p_hierarchy_object_name;
1951:
1952: if (l_count = 0) then
1953:
1954: FEM_ENGINES_PKG.user_message (
1955: p_app_name => G_FEM
1956: ,p_msg_name => G_HIER_LDR_DIM_GRPS_REQ_ERR
1957: );
1958: l_hierarchy_error_flag := true;
1964: ------------------------------------------------------------------------------
1965: -- STEP 5.9.2: Verify that DIMENSION_GROUP_DISPLAY_CODE on FEM_HIER_DIM_GRPS_T
1966: -- is a valid dimension group for the dimension.
1967: ------------------------------------------------------------------------------
1968: FEM_ENGINES_PKG.tech_message (
1969: p_severity => g_log_level_1
1970: ,p_module => G_BLOCK||'.'||l_api_name
1971: ,p_msg_text => 'Step 5.9.2: DIMENSION_GROUP_DISPLAY_CODE in FEM_HIER_DIM_GRPS_T Validation'
1972: );
2038: -- more value sets to be added (Bug 4661474).
2039: ------------------------------------------------------------------------------
2040: if ( (ld_load_type = g_new_hier) or (ld_multi_value_set_flag = 'Y') ) then
2041:
2042: FEM_ENGINES_PKG.tech_message (
2043: p_severity => g_log_level_1
2044: ,p_module => G_BLOCK||'.'||l_api_name
2045: ,p_msg_text => 'Step 5.10.1: VALUE_SET_DISPLAY_CODE in FEM_HIER_VALUE_SETS_T Validation'
2046: );
2112: -- STEP 6: Parent-Child relationship validations. All hierarchy load types
2113: -- must pass this validation (new hierarchy, new hierarchy definition, and
2114: -- update hierarchy definition).
2115: ------------------------------------------------------------------------------
2116: FEM_ENGINES_PKG.tech_message (
2117: p_severity => g_log_level_1
2118: ,p_module => G_BLOCK||'.'||l_api_name
2119: ,p_msg_text => 'Step 6: Parent-Child Validations'
2120: );
2124: -- CALENDAR_DISPLAY_CODE is valid for the hierarchy in FEM_CAL_PERIODS_HIER_T
2125: ------------------------------------------------------------------------------
2126: if (p_dimension_varchar_label = 'CAL_PERIOD') then
2127:
2128: FEM_ENGINES_PKG.tech_message (
2129: p_severity => g_log_level_1
2130: ,p_module => G_BLOCK||'.'||l_api_name
2131: ,p_msg_text => 'Step 6.1: CALENDAR_DISPLAY_CODE in FEM_CAL_PERIODS_HIER_T Validation'
2132: );
2191: -- FEM_HIER_VALUE_SETS_T (new) or FEM_HIER_VALUE_SETS (existing)
2192: ------------------------------------------------------------------------------
2193: if (l_value_set_required_flag = 'Y') then
2194:
2195: FEM_ENGINES_PKG.tech_message (
2196: p_severity => g_log_level_1
2197: ,p_module => G_BLOCK||'.'||l_api_name
2198: ,p_msg_text => 'Step 6.2: PARENT_VALUE_SET_DISPLAY_CODE and CHILD_VALUE_SET_DISPLAY_CODE Validations'
2199: );
2270: ------------------------------------------------------------------------------
2271: -- STEP 6.3: Verify that all PARENT_DISPLAY_CODE and CHILD_DISPLAY_CODE values
2272: -- are valid dimension members in the appropriate FEM_xName_B table.
2273: ------------------------------------------------------------------------------
2274: FEM_ENGINES_PKG.tech_message (
2275: p_severity => g_log_level_1
2276: ,p_module => G_BLOCK||'.'||l_api_name
2277: ,p_msg_text => 'Step 6.3: PARENT_ID and CHILD_ID Validations'
2278: );
2354: -- each child only has a single parent in the FEM_xName_HIER_T table.
2355: ------------------------------------------------------------------------------
2356: if (ld_hierarchy_type_code in ('OPEN','RECONCILIATION')) then
2357:
2358: FEM_ENGINES_PKG.tech_message (
2359: p_severity => g_log_level_1
2360: ,p_module => G_BLOCK||'.'||l_api_name
2361: ,p_msg_text => 'Step 6.4: Single Parent Validation'
2362: );
2480: ------------------------------------------------------------------------------
2481: -- STEP 6.5: Verify for MULTI_TOP_FLAG = N that there is a single top node
2482: -- for the hierarchy in FEM_xName_HIER_T.
2483: ------------------------------------------------------------------------------
2484: FEM_ENGINES_PKG.tech_message (
2485: p_severity => g_log_level_1
2486: ,p_module => G_BLOCK||'.'||l_api_name
2487: ,p_msg_text => 'Step 6.5: Missing and Single Root Node Validations'
2488: );
2501: end if;
2502:
2503: if (l_num_roots = 0) then
2504:
2505: FEM_ENGINES_PKG.user_message (
2506: p_app_name => G_FEM
2507: ,p_msg_name => G_HIER_LDR_MISSING_ROOT_ERR
2508: );
2509: l_hierarchy_error_flag := true;
2592: ------------------------------------------------------------------------------
2593: -- STEP 6.6: Verify that root nodes do not appear as children of other
2594: -- nodes for the hierarchy in FEM_xName_HIER_T.
2595: ------------------------------------------------------------------------------
2596: FEM_ENGINES_PKG.tech_message (
2597: p_severity => g_log_level_1
2598: ,p_module => G_BLOCK||'.'||l_api_name
2599: ,p_msg_text => 'Step 6.6: Invalid Root Node Validation'
2600: );
2672: -- STEP 7: Load hierarchy records from FEM_xNAME_HIER_T into
2673: -- FEM_HIERVAL_VSR_T/_CALP_T by flattening hierarchy.
2674: ------------------------------------------------------------------------------
2675:
2676: FEM_ENGINES_PKG.tech_message (
2677: p_severity => g_log_level_1
2678: ,p_module => G_BLOCK||'.'||l_api_name
2679: ,p_msg_text => 'Step 7: Inserting Flattened Hierarchy Records in '||l_target_hierval_table
2680: );
2683: -- STEP 7.1: Insert root node records from FEM_xNAME_HIER_T into
2684: -- FEM_HIERVAL_VSR_T/_CALP_T.
2685: ------------------------------------------------------------------------------
2686:
2687: FEM_ENGINES_PKG.tech_message (
2688: p_severity => g_log_level_1
2689: ,p_module => G_BLOCK||'.'||l_api_name
2690: ,p_msg_text => 'Step 7.1: Inserting Root Nodes Records in '||l_target_hierval_table
2691: );
2782: -- FEM_HIERVAL_VSR_T/_CALP_T. Only the "connect by" records need to be
2783: -- created at this stage.
2784: ------------------------------------------------------------------------------
2785:
2786: FEM_ENGINES_PKG.tech_message (
2787: p_severity => g_log_level_1
2788: ,p_module => G_BLOCK||'.'||l_api_name
2789: ,p_msg_text => 'Step 7.2: Inserting Relationship Records in '||l_target_hierval_table
2790: );
2956:
2957: ------------------------------------------------------------------------------
2958: -- STEP 8.1: Verify that all leaf nodes have RECON_LEAF_NODE_FLAG = Y
2959: ------------------------------------------------------------------------------
2960: FEM_ENGINES_PKG.tech_message (
2961: p_severity => g_log_level_1
2962: ,p_module => G_BLOCK||'.'||l_api_name
2963: ,p_msg_text => 'Step 8.1: Reconciliation Leaf Validation'
2964: );
2975: and ver.attribute_id = att.attribute_id
2976: and ver.default_version_flag = 'Y';
2977: exception
2978: when others then
2979: FEM_ENGINES_PKG.user_message (
2980: p_app_name => G_FEM
2981: ,p_msg_name => G_HIER_LDR_NO_LEAF_ATTR_ERR
2982: ,p_token1 => 'DIMENSION'
2983: ,p_value1 => p_dimension_varchar_label
3042:
3043: ------------------------------------------------------------------------------
3044: -- STEP 8.2: Verify that all parent nodes have RECON_LEAF_NODE_FLAG = N
3045: ------------------------------------------------------------------------------
3046: FEM_ENGINES_PKG.tech_message (
3047: p_severity => g_log_level_1
3048: ,p_module => G_BLOCK||'.'||l_api_name
3049: ,p_msg_text => 'Step 8.2: Reconciliation Non-Leaf Validation'
3050: );
3109: -- that all parent/child relationships obey the Group sequence rules.
3110: ------------------------------------------------------------------------------
3111: if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
3112:
3113: FEM_ENGINES_PKG.tech_message (
3114: p_severity => g_log_level_1
3115: ,p_module => G_BLOCK||'.'||l_api_name
3116: ,p_msg_text => 'Step 9: Group Sequencing Validation'
3117: );
3120: -- STEP 9.1: Validating that all hierarchy Nodes belong to a hierarchy
3121: -- dimension group.
3122: ------------------------------------------------------------------------------
3123:
3124: FEM_ENGINES_PKG.tech_message (
3125: p_severity => g_log_level_1
3126: ,p_module => G_BLOCK||'.'||l_api_name
3127: ,p_msg_text => 'Step 9.1: Validating that all Hierarchy Nodes belong to a Hierarchy Dimension Group'
3128: );
3190: -- STEP 9.2: Validating that all hierarchy nodes follow the correct hierarchy
3191: -- group sequencing order
3192: ------------------------------------------------------------------------------
3193:
3194: FEM_ENGINES_PKG.tech_message (
3195: p_severity => g_log_level_1
3196: ,p_module => G_BLOCK||'.'||l_api_name
3197: ,p_msg_text => 'Step 9.2: Validating that all Hierarchy Nodes follow the correct Hierarchy Group Sequencing Order'
3198: );
3207: ------------------------------------------------------------------------------
3208: -- STEP 9.2.1: Skip-Level Hierarchy Group Sequence Validation
3209: ------------------------------------------------------------------------------
3210:
3211: FEM_ENGINES_PKG.tech_message (
3212: p_severity => g_log_level_1
3213: ,p_module => G_BLOCK||'.'||l_api_name
3214: ,p_msg_text => 'Step 9.2.1: Skip-Level Hierarchy Group Sequence validation'
3215: );
3267: ------------------------------------------------------------------------------
3268: -- STEP 9.2.2: Standard Hierarchy Group Sequence Validation
3269: ------------------------------------------------------------------------------
3270:
3271: FEM_ENGINES_PKG.tech_message (
3272: p_severity => g_log_level_1
3273: ,p_module => G_BLOCK||'.'||l_api_name
3274: ,p_msg_text => 'Step 9.2.2: Standard Hierarchy Group Sequence validation'
3275: );
3391:
3392: ------------------------------------------------------------------------------
3393: -- STEP 10.1: Get the VALUE_SET_ID for FEM_HIERARCHIES
3394: ------------------------------------------------------------------------------
3395: FEM_ENGINES_PKG.tech_message (
3396: p_severity => g_log_level_1
3397: ,p_module => G_BLOCK||'.'||l_api_name
3398: ,p_msg_text => 'Step 10.1: Get the VALUE_SET_ID for FEM_HIERARCHIES'
3399: );
3407:
3408: exception
3409:
3410: when no_data_found then
3411: FEM_ENGINES_PKG.user_message (
3412: p_app_name => G_FEM
3413: ,p_msg_name => G_HIER_LDR_NO_HIER_VS_ERR
3414: ,p_token1 => 'HIERARCHY_OBJECT_NAME'
3415: ,p_value1 => ld_hierarchy_object_name
3448:
3449: ------------------------------------------------------------------------------
3450: -- STEP 10.2: Insert into FEM_OBJECT_CATALOG_B/_TL
3451: ------------------------------------------------------------------------------
3452: FEM_ENGINES_PKG.tech_message (
3453: p_severity => g_log_level_1
3454: ,p_module => G_BLOCK||'.'||l_api_name
3455: ,p_msg_text => 'Step 10.2: Insert into FEM_OBJECT_CATALOG_B/_TL'
3456: );
3479:
3480: ------------------------------------------------------------------------------
3481: -- STEP 10.3: Insert into FEM_HIERARCHIES
3482: ------------------------------------------------------------------------------
3483: FEM_ENGINES_PKG.tech_message (
3484: p_severity => g_log_level_1
3485: ,p_module => G_BLOCK||'.'||l_api_name
3486: ,p_msg_text => 'Step 10.3: Insert into FEM_HIERARCHIES'
3487: );
3532: -- STEP 10.4: Insert into FEM_HIER_DIMENSION_GRPS
3533: ------------------------------------------------------------------------------
3534: if (ld_group_seq_enforced_code <> 'NO_GROUPS') then
3535:
3536: FEM_ENGINES_PKG.tech_message (
3537: p_severity => g_log_level_1
3538: ,p_module => G_BLOCK||'.'||l_api_name
3539: ,p_msg_text => 'Step 10.4: Insert into FEM_HIER_DIMENSION_GRPS'
3540: );
3606: -- updates on multi value set hierarchies where we allow more value sets
3607: -- to be added (Bug 4661474).
3608: if ( (ld_load_type = g_new_hier) or (ld_multi_value_set_flag = 'Y') ) then
3609:
3610: FEM_ENGINES_PKG.tech_message (
3611: p_severity => g_log_level_1
3612: ,p_module => G_BLOCK||'.'||l_api_name
3613: ,p_msg_text => 'Step 10.5: Insert into FEM_HIER_VALUE_SETS'
3614: );
3728: ------------------------------------------------------------------------------
3729:
3730: if (ld_load_type in (g_new_hier, g_new_hier_def)) then
3731:
3732: FEM_ENGINES_PKG.tech_message (
3733: p_severity => g_log_level_1
3734: ,p_module => G_BLOCK||'.'||l_api_name
3735: ,p_msg_text => 'Step 11: Insert Hierarchy Object Definition Records'
3736: );
3803: ------------------------------------------------------------------------------
3804:
3805: if (ld_load_type = g_update_hier_def) then
3806:
3807: FEM_ENGINES_PKG.tech_message (
3808: p_severity => g_log_level_1
3809: ,p_module => G_BLOCK||'.'||l_api_name
3810: ,p_msg_text => 'Step 12: Deleting Hierarchy Relationship Records'
3811: );
3837: -- STEP 13: Insert all records for this request id from
3838: -- FEM_HIERVAL_VSR_T/_CALP_T into FEM_xName_HIER.
3839: ------------------------------------------------------------------------------
3840:
3841: FEM_ENGINES_PKG.tech_message (
3842: p_severity => g_log_level_1
3843: ,p_module => G_BLOCK||'.'||l_api_name
3844: ,p_msg_text => 'Step 13: Inserting Relationship Records into '||l_target_hierval_table
3845: );
3858: ------------------------------------------------------------------------------
3859: -- STEP 14: Delete from the FEM_xName_HIER_T table rows for each hierarchy
3860: -- object definition that was successfully loaded.
3861: ------------------------------------------------------------------------------
3862: FEM_ENGINES_PKG.tech_message (
3863: p_severity => g_log_level_1
3864: ,p_module => G_BLOCK||'.'||l_api_name
3865: ,p_msg_text => 'Step 14: Purging Relationship Interface Records in '||l_source_hier_table
3866: );
3875: ------------------------------------------------------------------------------
3876: -- STEP 15: Delete from the FEM_HIERARCHIES_T table rows for each hierarchy
3877: -- object definition that was successfully loaded.
3878: ------------------------------------------------------------------------------
3879: FEM_ENGINES_PKG.tech_message (
3880: p_severity => g_log_level_1
3881: ,p_module => G_BLOCK||'.'||l_api_name
3882: ,p_msg_text => 'Step 15: Purging all other Hierarchy Interface tables'
3883: );
3927: -- exploded rows by calling the DHM api.
3928: ------------------------------------------------------------------------------
3929: if (ld_flattened_rows_flag = 'Y') then
3930:
3931: FEM_ENGINES_PKG.tech_message (
3932: p_severity => g_log_level_1
3933: ,p_module => G_BLOCK||'.'||l_api_name
3934: ,p_msg_text => 'Step 16: Flattening Hierarchy'
3935: );
3950: p_msg_count => l_msg_count
3951: ,p_msg_data => l_msg_data
3952: );
3953:
3954: FEM_ENGINES_PKG.user_message (
3955: p_app_name => G_FEM
3956: ,p_msg_name => G_HIER_LDR_HIER_FLATTEN_ERR
3957: );
3958:
3975:
3976:
3977: if (l_hierarchy_error_flag) then
3978:
3979: FEM_ENGINES_PKG.tech_message (
3980: p_severity => g_log_level_1
3981: ,p_module => G_BLOCK||'.'||l_api_name
3982: ,p_msg_text => 'Validation Error. Updating Status on Interface Tables.'
3983: );
4033:
4034: ------------------------------------------------------------------------------
4035: -- STEP 17: Engine Master Post Processing.
4036: ------------------------------------------------------------------------------
4037: FEM_ENGINES_PKG.tech_message (
4038: p_severity => g_log_level_1
4039: ,p_module => G_BLOCK||'.'||l_api_name
4040: ,p_msg_text => 'Step 17: Post Processing'
4041: );
4050: ,p_execution_mode => p_execution_mode
4051: ,p_target_hierval_table => l_target_hierval_table
4052: );
4053:
4054: FEM_ENGINES_PKG.tech_message (
4055: p_severity => g_log_level_2
4056: ,p_module => G_BLOCK||'.'||l_api_name
4057: ,p_msg_text => 'END'
4058: );
4060: EXCEPTION
4061:
4062: when e_loader_error then
4063:
4064: FEM_ENGINES_PKG.tech_message(
4065: p_severity => g_log_level_6
4066: ,p_module => G_BLOCK||'.'||l_api_name
4067: ,p_msg_text => 'Dimension Hierarchy Loader Exception'
4068: );
4081: );
4082:
4083: when e_hierarchy_error then
4084:
4085: FEM_ENGINES_PKG.tech_message(
4086: p_severity => g_log_level_6
4087: ,p_module => G_BLOCK||'.'||l_api_name
4088: ,p_msg_text => 'Hierarchy Exception'
4089: );
4109:
4110: gv_prg_msg := sqlerrm;
4111: gv_callstack := dbms_utility.format_call_stack;
4112:
4113: FEM_ENGINES_PKG.tech_message(
4114: p_severity => g_log_level_6
4115: ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception.Error_Message'
4116: ,p_msg_text => gv_prg_msg
4117: );
4115: ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception.Error_Message'
4116: ,p_msg_text => gv_prg_msg
4117: );
4118:
4119: FEM_ENGINES_PKG.tech_message(
4120: p_severity => g_log_level_6
4121: ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception.Callstack'
4122: ,p_msg_text => gv_callstack
4123: );
4121: ,p_module => G_BLOCK||'.'||l_api_name||'.Unexpected_Exception.Callstack'
4122: ,p_msg_text => gv_callstack
4123: );
4124:
4125: FEM_ENGINES_PKG.User_Message (
4126: p_app_name => G_FEM
4127: ,p_msg_name => G_UNEXPECTED_ERROR
4128: ,p_token1 => 'ERR_MSG'
4129: ,p_value1 => gv_prg_msg
4231: l_api_name constant varchar2(30) := 'get_dimension_info';
4232:
4233: BEGIN
4234:
4235: FEM_ENGINES_PKG.tech_message (
4236: p_severity => g_log_level_2
4237: ,p_module => G_BLOCK||'.'||l_api_name
4238: ,p_msg_text => 'BEGIN'
4239: );
4265: and composite_dimension_flag = 'N'
4266: and hierarchy_table_name is not null
4267: and read_only_flag = 'N';
4268:
4269: FEM_ENGINES_PKG.tech_message (
4270: p_severity => g_log_level_2
4271: ,p_module => G_BLOCK||'.'||l_api_name
4272: ,p_msg_text => 'END'
4273: );
4274:
4275: EXCEPTION
4276:
4277: when no_data_found then
4278: FEM_ENGINES_PKG.user_message (
4279: p_app_name => G_FEM
4280: ,p_msg_name => G_DIM_NOT_FOUND_ERR
4281: );
4282: raise e_loader_error;
4325: e_pl_register_obj_def_failed exception;
4326:
4327: BEGIN
4328:
4329: FEM_ENGINES_PKG.tech_message (
4330: p_severity => g_log_level_2
4331: ,p_module => G_BLOCK||'.'||l_api_name
4332: ,p_msg_text => 'BEGIN'
4333: );
4415: end if;
4416:
4417: commit;
4418:
4419: FEM_ENGINES_PKG.tech_message (
4420: p_severity => g_log_level_2
4421: ,p_module => G_BLOCK||'.'||l_api_name
4422: ,p_msg_text => 'END'
4423: );
4426:
4427: when e_pl_register_req_failed then
4428:
4429: rollback to register_process_execution_pub;
4430: FEM_ENGINES_PKG.tech_message(
4431: p_severity => g_log_level_6
4432: ,p_module => G_BLOCK||'.'||l_api_name
4433: ,p_msg_text => 'Reqister Request Exception'
4434: );
4431: p_severity => g_log_level_6
4432: ,p_module => G_BLOCK||'.'||l_api_name
4433: ,p_msg_text => 'Reqister Request Exception'
4434: );
4435: FEM_ENGINES_PKG.user_message (
4436: p_app_name => G_FEM
4437: ,p_msg_name => G_PL_REG_REQUEST_ERR
4438: );
4439: raise e_loader_error;
4440:
4441: when e_pl_register_exec_failed then
4442:
4443: rollback to register_process_execution_pub;
4444: FEM_ENGINES_PKG.tech_message(
4445: p_severity => g_log_level_6
4446: ,p_module => G_BLOCK||'.'||l_api_name
4447: ,p_msg_text => 'Reqister Object Execution Exception'
4448: );
4445: p_severity => g_log_level_6
4446: ,p_module => G_BLOCK||'.'||l_api_name
4447: ,p_msg_text => 'Reqister Object Execution Exception'
4448: );
4449: FEM_ENGINES_PKG.user_message (
4450: p_app_name => G_FEM
4451: ,p_msg_name => G_PL_OBJ_EXEC_LOCK_ERR
4452: );
4453: raise e_loader_error;
4454:
4455: when e_pl_register_obj_def_failed then
4456:
4457: rollback to register_process_execution_pub;
4458: FEM_ENGINES_PKG.tech_message(
4459: p_severity => g_log_level_6
4460: ,p_module => G_BLOCK||'.'||l_api_name
4461: ,p_msg_text => 'Reqister Object Definition Exception'
4462: );
4459: p_severity => g_log_level_6
4460: ,p_module => G_BLOCK||'.'||l_api_name
4461: ,p_msg_text => 'Reqister Object Definition Exception'
4462: );
4463: FEM_ENGINES_PKG.user_message (
4464: p_app_name => G_FEM
4465: ,p_msg_name => G_PL_OBJ_EXECLOCK_EXISTS_ERR
4466: ,p_token1 => 'OBJECT_ID'
4467: ,p_value1 => p_object_id
4509: e_post_process exception;
4510:
4511: BEGIN
4512:
4513: FEM_ENGINES_PKG.tech_message (
4514: p_severity => g_log_level_2
4515: ,p_module => G_BLOCK||'.'||l_api_name
4516: ,p_msg_text => 'BEGIN'
4517: );
4521: -- FEM_HIERVAL_VSR_T/_CALP_T table.
4522: ------------------------------------------------------------------------------
4523: if (p_target_hierval_table is not null) then
4524:
4525: FEM_ENGINES_PKG.tech_message (
4526: p_severity => g_log_level_1
4527: ,p_module => G_BLOCK||'.'||l_api_name
4528: ,p_msg_text => 'Step 1: Purging Relationship Records in '||p_target_hierval_table
4529: );
4545: ------------------------------------------------------------------------------
4546: -- STEP 2: Update Object Execution Errors and Status.
4547: ------------------------------------------------------------------------------
4548:
4549: FEM_ENGINES_PKG.tech_message (
4550: p_severity => g_log_level_1
4551: ,p_module => G_BLOCK||'.'||l_api_name
4552: ,p_msg_text => 'Step 2: Update Object Execution Errors and the Status'
4553: );
4630: commit;
4631:
4632: -- Set the final execution status message in the Log File
4633: if (p_exec_status_code = g_exec_status_success) then
4634: FEM_ENGINES_PKG.user_message (
4635: p_app_name => G_FEM
4636: ,p_msg_name => G_EXEC_SUCCESS
4637: );
4638: else
4635: p_app_name => G_FEM
4636: ,p_msg_name => G_EXEC_SUCCESS
4637: );
4638: else
4639: FEM_ENGINES_PKG.user_message (
4640: p_app_name => G_FEM
4641: ,p_msg_name => G_EXEC_RERUN
4642: );
4643: end if;
4641: ,p_msg_name => G_EXEC_RERUN
4642: );
4643: end if;
4644:
4645: FEM_ENGINES_PKG.tech_message (
4646: p_severity => g_log_level_2
4647: ,p_module => G_BLOCK||'.'||l_api_name
4648: ,p_msg_text => 'END'
4649: );
4651: EXCEPTION
4652:
4653: when e_post_process then
4654:
4655: FEM_ENGINES_PKG.tech_message(
4656: p_severity => g_log_level_6
4657: ,p_module => G_BLOCK||'.'||l_api_name
4658: ,p_msg_text => 'Post Process Exception'
4659: );
4657: ,p_module => G_BLOCK||'.'||l_api_name
4658: ,p_msg_text => 'Post Process Exception'
4659: );
4660:
4661: FEM_ENGINES_PKG.user_message (
4662: p_app_name => G_FEM
4663: ,p_msg_name => G_EXT_LDR_POST_PROC_ERR
4664: );
4665:
4663: ,p_msg_name => G_EXT_LDR_POST_PROC_ERR
4664: );
4665:
4666: -- Set the final execution status message in the Log File to RERUN
4667: FEM_ENGINES_PKG.user_message (
4668: p_app_name => G_FEM
4669: ,p_msg_name => G_EXEC_RERUN
4670: );
4671:
4702: l_api_name constant varchar2(80) := 'get_put_messages';
4703:
4704: BEGIN
4705:
4706: FEM_ENGINES_PKG.tech_message (
4707: p_severity => g_log_level_2
4708: ,p_module => G_BLOCK||'.'||l_api_name
4709: ,p_msg_text => 'msg_count='||p_msg_count
4710: );
4715:
4716: FND_MESSAGE.set_encoded(l_msg_data);
4717: l_message := FND_MESSAGE.get;
4718:
4719: FEM_ENGINES_PKG.user_message(
4720: p_msg_text => l_message
4721: );
4722:
4723: FEM_ENGINES_PKG.tech_message (
4719: FEM_ENGINES_PKG.user_message(
4720: p_msg_text => l_message
4721: );
4722:
4723: FEM_ENGINES_PKG.tech_message (
4724: p_severity => g_log_level_2
4725: ,p_module => G_BLOCK||'.'||l_api_name
4726: ,p_msg_text => 'msg_data='||l_message
4727: );
4736: ,p_data => l_message
4737: ,p_msg_index_out => l_msg_out
4738: );
4739:
4740: FEM_ENGINES_PKG.user_message (
4741: p_msg_text => l_message
4742: );
4743:
4744: FEM_ENGINES_PKG.tech_message (
4740: FEM_ENGINES_PKG.user_message (
4741: p_msg_text => l_message
4742: );
4743:
4744: FEM_ENGINES_PKG.tech_message (
4745: p_severity => g_log_level_2
4746: ,p_module => G_BLOCK||'.'||l_api_name
4747: ,p_msg_text => 'msg_data='||l_message
4748: );
4858: if (l_message_name is not null) then
4859: FND_MESSAGE.Set_Name(G_FEM,l_message_name);
4860: end if;
4861:
4862: FEM_ENGINES_PKG.user_message (
4863: p_app_name => G_FEM
4864: ,p_msg_name => G_HIER_LDR_HIER_DETAILS_ERR
4865: ,p_token1 => 'HIER_TABLE_NAME'
4866: ,p_value1 => p_hier_table_name
4887: l_status_clause varchar2(100) := '';
4888:
4889: BEGIN
4890:
4891: FEM_ENGINES_PKG.tech_message (
4892: p_severity => g_log_level_2
4893: ,p_module => G_BLOCK||'.'||l_api_name
4894: ,p_msg_text => 'BEGIN'
4895: );
4927: end if;
4928:
4929: end if;
4930:
4931: FEM_ENGINES_PKG.tech_message (
4932: p_severity => g_log_level_2
4933: ,p_module => G_BLOCK||'.'||l_api_name
4934: ,p_msg_text => 'END'
4935: );
4949: l_status_clause varchar2(100) := '';
4950:
4951: BEGIN
4952:
4953: FEM_ENGINES_PKG.tech_message (
4954: p_severity => g_log_level_2
4955: ,p_module => G_BLOCK||'.'||l_api_name
4956: ,p_msg_text => 'BEGIN'
4957: );
4973: ' where dg.dimension_group_display_code = hdgt.dimension_group_display_code'||
4974: ' and dg.dimension_id = :b_dimension_id'||
4975: ' )';
4976:
4977: FEM_ENGINES_PKG.tech_message (
4978: p_severity => g_log_level_2
4979: ,p_module => G_BLOCK||'.'||l_api_name
4980: ,p_msg_text => 'END'
4981: );
4996: l_status_clause varchar2(100) := '';
4997:
4998: BEGIN
4999:
5000: FEM_ENGINES_PKG.tech_message (
5001: p_severity => g_log_level_2
5002: ,p_module => G_BLOCK||'.'||l_api_name
5003: ,p_msg_text => 'BEGIN'
5004: );
5024: x_bad_hier_calendars_stmt := '';
5025:
5026: end if;
5027:
5028: FEM_ENGINES_PKG.tech_message (
5029: p_severity => g_log_level_2
5030: ,p_module => G_BLOCK||'.'||l_api_name
5031: ,p_msg_text => 'END'
5032: );
5049: l_status_clause_2 varchar2(100) := '';
5050:
5051: BEGIN
5052:
5053: FEM_ENGINES_PKG.tech_message (
5054: p_severity => g_log_level_2
5055: ,p_module => G_BLOCK||'.'||l_api_name
5056: ,p_msg_text => 'BEGIN'
5057: );
5104: end if;
5105:
5106: end if;
5107:
5108: FEM_ENGINES_PKG.tech_message (
5109: p_severity => g_log_level_2
5110: ,p_module => G_BLOCK||'.'||l_api_name
5111: ,p_msg_text => 'END'
5112: );
5128: l_status_clause varchar2(100) := '';
5129:
5130: BEGIN
5131:
5132: FEM_ENGINES_PKG.tech_message (
5133: p_severity => g_log_level_2
5134: ,p_module => G_BLOCK||'.'||l_api_name
5135: ,p_msg_text => 'BEGIN'
5136: );
5182: end if;
5183:
5184: end if;
5185:
5186: FEM_ENGINES_PKG.tech_message (
5187: p_severity => g_log_level_2
5188: ,p_module => G_BLOCK||'.'||l_api_name
5189: ,p_msg_text => 'END'
5190: );
5207: l_status_clause_2 varchar2(100) := '';
5208:
5209: BEGIN
5210:
5211: FEM_ENGINES_PKG.tech_message (
5212: p_severity => g_log_level_2
5213: ,p_module => G_BLOCK||'.'||l_api_name
5214: ,p_msg_text => 'BEGIN'
5215: );
5283: end if;
5284:
5285: end if;
5286:
5287: FEM_ENGINES_PKG.tech_message (
5288: p_severity => g_log_level_2
5289: ,p_module => G_BLOCK||'.'||l_api_name
5290: ,p_msg_text => 'END'
5291: );
5310: l_status_clause varchar2(100) := '';
5311:
5312: BEGIN
5313:
5314: FEM_ENGINES_PKG.tech_message (
5315: p_severity => g_log_level_2
5316: ,p_module => G_BLOCK||'.'||l_api_name
5317: ,p_msg_text => 'BEGIN'
5318: );
5431: end if;
5432:
5433: end if;
5434:
5435: FEM_ENGINES_PKG.tech_message (
5436: p_severity => g_log_level_2
5437: ,p_module => G_BLOCK||'.'||l_api_name
5438: ,p_msg_text => 'END'
5439: );
5455: l_status_clause varchar2(100) := '';
5456:
5457: BEGIN
5458:
5459: FEM_ENGINES_PKG.tech_message (
5460: p_severity => g_log_level_2
5461: ,p_module => G_BLOCK||'.'||l_api_name
5462: ,p_msg_text => 'BEGIN'
5463: );
5536: end if;
5537:
5538: end if;
5539:
5540: FEM_ENGINES_PKG.tech_message (
5541: p_severity => g_log_level_2
5542: ,p_module => G_BLOCK||'.'||l_api_name
5543: ,p_msg_text => 'END'
5544: );
5559: l_api_name constant varchar2(30) := 'bld_bad_hier_rec_leafs_stmt';
5560:
5561: BEGIN
5562:
5563: FEM_ENGINES_PKG.tech_message (
5564: p_severity => g_log_level_2
5565: ,p_module => G_BLOCK||'.'||l_api_name
5566: ,p_msg_text => 'BEGIN'
5567: );
5624: ' )';
5625:
5626: end if;
5627:
5628: FEM_ENGINES_PKG.tech_message (
5629: p_severity => g_log_level_2
5630: ,p_module => G_BLOCK||'.'||l_api_name
5631: ,p_msg_text => 'END'
5632: );
5647: l_api_name constant varchar2(30) := 'bld_bad_hier_rec_nodes_stmt';
5648:
5649: BEGIN
5650:
5651: FEM_ENGINES_PKG.tech_message (
5652: p_severity => g_log_level_2
5653: ,p_module => G_BLOCK||'.'||l_api_name
5654: ,p_msg_text => 'BEGIN'
5655: );
5702: ' )';
5703:
5704: end if;
5705:
5706: FEM_ENGINES_PKG.tech_message (
5707: p_severity => g_log_level_2
5708: ,p_module => G_BLOCK||'.'||l_api_name
5709: ,p_msg_text => 'END'
5710: );
5729: l_status_clause varchar2(100) := '';
5730:
5731: BEGIN
5732:
5733: FEM_ENGINES_PKG.tech_message (
5734: p_severity => g_log_level_2
5735: ,p_module => G_BLOCK||'.'||l_api_name
5736: ,p_msg_text => 'BEGIN'
5737: );
5839: end if;
5840:
5841: end if;
5842:
5843: FEM_ENGINES_PKG.tech_message (
5844: p_severity => g_log_level_2
5845: ,p_module => G_BLOCK||'.'||l_api_name
5846: ,p_msg_text => 'END'
5847: );
5862: l_status_clause varchar2(100) := '';
5863:
5864: BEGIN
5865:
5866: FEM_ENGINES_PKG.tech_message (
5867: p_severity => g_log_level_2
5868: ,p_module => G_BLOCK||'.'||l_api_name
5869: ,p_msg_text => 'BEGIN'
5870: );
5889: ' and dg.dimension_id = :b_dimension_id'||
5890: ' and dg.dimension_group_id = hv.child_dimension_grp_id'||
5891: ' )';
5892:
5893: FEM_ENGINES_PKG.tech_message (
5894: p_severity => g_log_level_2
5895: ,p_module => G_BLOCK||'.'||l_api_name
5896: ,p_msg_text => 'END'
5897: );
5910: l_api_name constant varchar2(30) := 'bld_bad_hier_dim_groups_stmt';
5911:
5912: BEGIN
5913:
5914: FEM_ENGINES_PKG.tech_message (
5915: p_severity => g_log_level_2
5916: ,p_module => G_BLOCK||'.'||l_api_name
5917: ,p_msg_text => 'BEGIN'
5918: );
5928: ' where hdg.hierarchy_obj_id = :b_hierarchy_object_id'||
5929: ' and hdg.dimension_group_id = hv.child_dimension_grp_id'||
5930: ' )';
5931:
5932: FEM_ENGINES_PKG.tech_message (
5933: p_severity => g_log_level_2
5934: ,p_module => G_BLOCK||'.'||l_api_name
5935: ,p_msg_text => 'END'
5936: );
5949: l_api_name constant varchar2(30) := 'bld_bad_hier_dim_grp_skp_stmt';
5950:
5951: BEGIN
5952:
5953: FEM_ENGINES_PKG.tech_message (
5954: p_severity => g_log_level_2
5955: ,p_module => G_BLOCK||'.'||l_api_name
5956: ,p_msg_text => 'BEGIN'
5957: );
5990: ' and dgp.dimension_group_seq >= dgc.dimension_group_seq';
5991:
5992: end if;
5993:
5994: FEM_ENGINES_PKG.tech_message (
5995: p_severity => g_log_level_2
5996: ,p_module => G_BLOCK||'.'||l_api_name
5997: ,p_msg_text => 'END'
5998: );
6011: l_api_name constant varchar2(30) := 'bld_bad_hier_dim_grp_reg_stmt';
6012:
6013: BEGIN
6014:
6015: FEM_ENGINES_PKG.tech_message (
6016: p_severity => g_log_level_2
6017: ,p_module => G_BLOCK||'.'||l_api_name
6018: ,p_msg_text => 'BEGIN'
6019: );
6025: ' where request_id = :b_request_id'||
6026: ' and child_depth_num = :b_depth_num'||
6027: ' and child_dimension_grp_id <> :b_dimension_group_id';
6028:
6029: FEM_ENGINES_PKG.tech_message (
6030: p_severity => g_log_level_2
6031: ,p_module => G_BLOCK||'.'||l_api_name
6032: ,p_msg_text => 'END'
6033: );
6049: l_status_clause varchar2(100) := '';
6050:
6051: BEGIN
6052:
6053: FEM_ENGINES_PKG.tech_message (
6054: p_severity => g_log_level_2
6055: ,p_module => G_BLOCK||'.'||l_api_name
6056: ,p_msg_text => 'BEGIN'
6057: );
6102: end if;
6103:
6104: end if;
6105:
6106: FEM_ENGINES_PKG.tech_message (
6107: p_severity => g_log_level_2
6108: ,p_module => G_BLOCK||'.'||l_api_name
6109: ,p_msg_text => 'END'
6110: );
6125: l_status_clause varchar2(100) := '';
6126:
6127: BEGIN
6128:
6129: FEM_ENGINES_PKG.tech_message (
6130: p_severity => g_log_level_2
6131: ,p_module => G_BLOCK||'.'||l_api_name
6132: ,p_msg_text => 'BEGIN'
6133: );
6161: end if;
6162:
6163: end if;
6164:
6165: FEM_ENGINES_PKG.tech_message (
6166: p_severity => g_log_level_2
6167: ,p_module => G_BLOCK||'.'||l_api_name
6168: ,p_msg_text => 'END'
6169: );
6183: l_status_clause varchar2(100) := '';
6184:
6185: BEGIN
6186:
6187: FEM_ENGINES_PKG.tech_message (
6188: p_severity => g_log_level_2
6189: ,p_module => G_BLOCK||'.'||l_api_name
6190: ,p_msg_text => 'BEGIN'
6191: );
6208: ' and dg.dimension_id = :b_dimension_id'||
6209: ' order by dg.dimension_group_seq'||
6210: ' )';
6211:
6212: FEM_ENGINES_PKG.tech_message (
6213: p_severity => g_log_level_2
6214: ,p_module => G_BLOCK||'.'||l_api_name
6215: ,p_msg_text => 'END'
6216: );
6229: l_api_name constant varchar2(30) := 'bld_get_dim_groups_stmt';
6230:
6231: BEGIN
6232:
6233: FEM_ENGINES_PKG.tech_message (
6234: p_severity => g_log_level_2
6235: ,p_module => G_BLOCK||'.'||l_api_name
6236: ,p_msg_text => 'BEGIN'
6237: );
6245: ' where hdg.hierarchy_obj_id = :b_hierarchy_object_id'||
6246: ' order by hdg.relative_dimension_group_seq'||
6247: ' )';
6248:
6249: FEM_ENGINES_PKG.tech_message (
6250: p_severity => g_log_level_2
6251: ,p_module => G_BLOCK||'.'||l_api_name
6252: ,p_msg_text => 'END'
6253: );
6267: l_status_clause varchar2(100) := '';
6268:
6269: BEGIN
6270:
6271: FEM_ENGINES_PKG.tech_message (
6272: p_severity => g_log_level_2
6273: ,p_module => G_BLOCK||'.'||l_api_name
6274: ,p_msg_text => 'BEGIN'
6275: );
6301: ' and ht.hier_obj_def_display_name = :b_hier_obj_def_display_name'||
6302: l_status_clause||
6303: ' and ht.language = userenv(''LANG'')';
6304:
6305: FEM_ENGINES_PKG.tech_message (
6306: p_severity => g_log_level_2
6307: ,p_module => G_BLOCK||'.'||l_api_name
6308: ,p_msg_text => 'END'
6309: );
6331: l_dim_grp_clause varchar2(100);
6332:
6333: BEGIN
6334:
6335: FEM_ENGINES_PKG.tech_message (
6336: p_severity => g_log_level_2
6337: ,p_module => G_BLOCK||'.'||l_api_name
6338: ,p_msg_text => 'BEGIN'
6339: );
6416: end if;
6417:
6418: end if;
6419:
6420: FEM_ENGINES_PKG.tech_message (
6421: p_severity => g_log_level_2
6422: ,p_module => G_BLOCK||'.'||l_api_name
6423: ,p_msg_text => 'END'
6424: );
6447: l_child_dim_grp_clause varchar2(100);
6448:
6449: BEGIN
6450:
6451: FEM_ENGINES_PKG.tech_message (
6452: p_severity => g_log_level_2
6453: ,p_module => G_BLOCK||'.'||l_api_name
6454: ,p_msg_text => 'BEGIN'
6455: );
6675: end if;
6676:
6677: end if;
6678:
6679: FEM_ENGINES_PKG.tech_message (
6680: p_severity => g_log_level_2
6681: ,p_module => G_BLOCK||'.'||l_api_name
6682: ,p_msg_text => 'END'
6683: );
6698: l_api_name constant varchar2(30) := 'bld_insert_hier_rels_stmt';
6699:
6700: BEGIN
6701:
6702: FEM_ENGINES_PKG.tech_message (
6703: p_severity => g_log_level_2
6704: ,p_module => G_BLOCK||'.'||l_api_name
6705: ,p_msg_text => 'BEGIN'
6706: );
6828: end if;
6829:
6830: end if;
6831:
6832: FEM_ENGINES_PKG.tech_message (
6833: p_severity => g_log_level_2
6834: ,p_module => G_BLOCK||'.'||l_api_name
6835: ,p_msg_text => 'END'
6836: );
6853: l_status_clause varchar2(100) := '';
6854:
6855: BEGIN
6856:
6857: FEM_ENGINES_PKG.tech_message (
6858: p_severity => g_log_level_2
6859: ,p_module => G_BLOCK||'.'||l_api_name
6860: ,p_msg_text => 'BEGIN'
6861: );
6888: ' where request_id = :b_request_id';
6889:
6890: end if;
6891:
6892: FEM_ENGINES_PKG.tech_message (
6893: p_severity => g_log_level_2
6894: ,p_module => G_BLOCK||'.'||l_api_name
6895: ,p_msg_text => 'END'
6896: );
6909: l_api_name constant varchar2(30) := 'bld_insert_hierval_rels_stmt';
6910:
6911: BEGIN
6912:
6913: FEM_ENGINES_PKG.tech_message (
6914: p_severity => g_log_level_2
6915: ,p_module => G_BLOCK||'.'||l_api_name
6916: ,p_msg_text => 'BEGIN'
6917: );
6975: ' )';
6976:
6977: end if;
6978:
6979: FEM_ENGINES_PKG.tech_message (
6980: p_severity => g_log_level_2
6981: ,p_module => G_BLOCK||'.'||l_api_name
6982: ,p_msg_text => 'END'
6983: );
7005: l_update_status_stmt varchar2(4000);
7006:
7007: BEGIN
7008:
7009: FEM_ENGINES_PKG.tech_message (
7010: p_severity => g_log_level_2
7011: ,p_module => G_BLOCK||'.'||l_api_name
7012: ,p_msg_text => 'BEGIN'
7013: );
7100: end if;
7101:
7102: end if;
7103:
7104: FEM_ENGINES_PKG.tech_message (
7105: p_severity => g_log_level_2
7106: ,p_module => G_BLOCK||'.'||l_api_name
7107: ,p_msg_text => 'END'
7108: );