70: BEGIN
71: -- validating parameters bug 3134719
72: IF p_mode_flag NOT IN ('A', 'M', 'D', 'C', 'S', 'E') THEN
73: x_return_status := 'E';
74: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
75: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Mode Flag');
76: END IF;
77: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
78: RETURN;
71: -- validating parameters bug 3134719
72: IF p_mode_flag NOT IN ('A', 'M', 'D', 'C', 'S', 'E') THEN
73: x_return_status := 'E';
74: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
75: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Mode Flag');
76: END IF;
77: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
78: RETURN;
79: END IF;
73: x_return_status := 'E';
74: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
75: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Mode Flag');
76: END IF;
77: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
78: RETURN;
79: END IF;
80:
81: IF p_object_type <> 'CATEGORY_SET' THEN
79: END IF;
80:
81: IF p_object_type <> 'CATEGORY_SET' THEN
82: x_return_status := 'E';
83: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
84: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Object Type. Must be CATEGORY_SET');
85: END IF;
86: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
87: RETURN;
80:
81: IF p_object_type <> 'CATEGORY_SET' THEN
82: x_return_status := 'E';
83: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
84: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Object Type. Must be CATEGORY_SET');
85: END IF;
86: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
87: RETURN;
88: END IF;
82: x_return_status := 'E';
83: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
84: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Object Type. Must be CATEGORY_SET');
85: END IF;
86: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
87: RETURN;
88: END IF;
89:
90: IF p_object_id IS NULL THEN
88: END IF;
89:
90: IF p_object_id IS NULL THEN
91: x_return_status := 'E';
92: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
93: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Object ID can not be NULL.');
94: END IF;
95: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
96: RETURN;
89:
90: IF p_object_id IS NULL THEN
91: x_return_status := 'E';
92: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
93: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Object ID can not be NULL.');
94: END IF;
95: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
96: RETURN;
97: END IF;
91: x_return_status := 'E';
92: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
93: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Object ID can not be NULL.');
94: END IF;
95: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
96: RETURN;
97: END IF;
98:
99: IF p_child_id IS NULL THEN
97: END IF;
98:
99: IF p_child_id IS NULL THEN
100: x_return_status := 'E';
101: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
102: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Child Category ID can not be NULL.');
103: END IF;
104: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
105: RETURN;
98:
99: IF p_child_id IS NULL THEN
100: x_return_status := 'E';
101: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
102: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Child Category ID can not be NULL.');
103: END IF;
104: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
105: RETURN;
106: END IF;
100: x_return_status := 'E';
101: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
102: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Child Category ID can not be NULL.');
103: END IF;
104: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
105: RETURN;
106: END IF;
107:
108: -- for sales and marketing enhancement
183: WHEN TOO_MANY_ROWS THEN
184: l_language_code := NULL;
185: WHEN NO_DATA_FOUND THEN
186: x_return_status := 'E';
187: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
188: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Category ID, Language combination');
189: END IF;
190: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
191: RETURN;
184: l_language_code := NULL;
185: WHEN NO_DATA_FOUND THEN
186: x_return_status := 'E';
187: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
188: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Category ID, Language combination');
189: END IF;
190: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
191: RETURN;
192: END;
186: x_return_status := 'E';
187: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
188: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Category ID, Language combination');
189: END IF;
190: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
191: RETURN;
192: END;
193: ELSE
194: -- Selecting userenv('LANG') into language code based on
231: x_msg_count := 0;
232: x_msg_data := null;
233: EXCEPTION WHEN OTHERS THEN
234: x_return_status := 'U';
235: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
236: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', SQLERRM);
237: END IF;
238: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
239: END INSERT_INTO_STAGING;
232: x_msg_data := null;
233: EXCEPTION WHEN OTHERS THEN
234: x_return_status := 'U';
235: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
236: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', SQLERRM);
237: END IF;
238: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
239: END INSERT_INTO_STAGING;
240:
234: x_return_status := 'U';
235: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
236: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', SQLERRM);
237: END IF;
238: FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
239: END INSERT_INTO_STAGING;
240:
241: -- for sales and marketing enhancement
242: -- This procedure loads the denorm parents table
1640:
1641: EXCEPTION
1642: WHEN OTHERS THEN
1643: x_return_status := 'U';
1644: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1645: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_CATEGORY_ASSIGNMENTS', SQLERRM);
1646: END IF;
1647: FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1648: END SYNC_CATEGORY_ASSIGNMENTS;
1641: EXCEPTION
1642: WHEN OTHERS THEN
1643: x_return_status := 'U';
1644: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1645: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_CATEGORY_ASSIGNMENTS', SQLERRM);
1646: END IF;
1647: FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1648: END SYNC_CATEGORY_ASSIGNMENTS;
1649:
1643: x_return_status := 'U';
1644: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1645: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_CATEGORY_ASSIGNMENTS', SQLERRM);
1646: END IF;
1647: FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1648: END SYNC_CATEGORY_ASSIGNMENTS;
1649:
1650: -- ER: 3185516
1651: -- This is a wrapper procedure, which will be called after import items
1773: x_return_status := 'S';
1774: EXCEPTION
1775: WHEN OTHERS THEN
1776: x_return_status := 'U';
1777: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1778: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_STAR_ITEMS_FROM_IOI', SQLERRM);
1779: END IF;
1780: FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1781: END SYNC_STAR_ITEMS_FROM_IOI;
1774: EXCEPTION
1775: WHEN OTHERS THEN
1776: x_return_status := 'U';
1777: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1778: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_STAR_ITEMS_FROM_IOI', SQLERRM);
1779: END IF;
1780: FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1781: END SYNC_STAR_ITEMS_FROM_IOI;
1782:
1776: x_return_status := 'U';
1777: IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1778: FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_STAR_ITEMS_FROM_IOI', SQLERRM);
1779: END IF;
1780: FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1781: END SYNC_STAR_ITEMS_FROM_IOI;
1782:
1783: -- This Function returns nth occurence of string value separated by delimiter parameter
1784: -- The occurence is determined by paramter p_level