[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_POPULATE_MI_GRP
Source
1 PACKAGE BODY ICX_CAT_POPULATE_MI_GRP AS
2 /* $Header: ICXGPPMB.pls 120.5 2006/06/21 19:11:25 sbgeorge noship $*/
3
4 -- Constants
5 G_PKG_NAME CONSTANT VARCHAR2(30):='ICX_CAT_POPULATE_MI_GRP';
6
7 -- Called from inventory master item forms / html interface for the following actions:
8 -- 1. Create an item
9 -- 2. Update an item
10 -- 3. Delete an item (only from delete groups)
11 -- 4. Translation of an item is updated
12 -- 5. An item is assigned to an org
13 PROCEDURE populateItemChange
14 ( p_api_version IN NUMBER ,
15 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
17 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL ,
18 x_return_status OUT NOCOPY VARCHAR2 ,
19 p_dml_type IN VARCHAR2 ,
20 p_inventory_item_id IN NUMBER ,
21 p_item_number IN VARCHAR2 ,
22 p_organization_id IN NUMBER ,
23 p_organization_code IN VARCHAR2 ,
24 p_master_org_flag IN VARCHAR2 ,
25 p_item_description IN VARCHAR2
26 )
27 IS
28 l_api_name CONSTANT VARCHAR2(30) := 'populateItemChange';
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_err_loc PLS_INTEGER;
31 l_start_date DATE;
32 l_end_date DATE;
33 l_log_string VARCHAR2(2000);
34 l_tmp_count NUMBER := 0;
35 BEGIN
36 l_err_loc := 100;
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38
39 l_err_loc := 200;
40 -- Standard Start of API savepoint
41 SAVEPOINT populateItemChange_sp;
42
43 l_err_loc := 300;
44 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
45 l_start_date := sysdate;
46 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
47 '; Parameter List: p_api_version:' || p_api_version ||
48 ', p_commit:' || p_commit ||
49 ', p_dml_type:' || p_dml_type ||
50 ', p_inventory_item_id:' || p_inventory_item_id ||
51 ', p_item_number:' || p_item_number ||
52 ', p_organization_id:' || p_organization_id ||
53 ', p_organization_code:' || p_organization_code ||
54 ', p_master_org_flag:' || p_master_org_flag ||
55 ', p_item_description:' || p_item_description ;
56 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
57 END IF;
58
59 l_err_loc := 400;
60 -- Standard call to check for call compatibility.
61 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
62 p_api_version ,
63 l_api_name ,
64 G_PKG_NAME )
65 THEN
66 l_err_loc := 500;
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69
70 l_err_loc := 600;
71 ICX_CAT_UTIL_PVT.setCommitParameter(P_COMMIT);
72
73 /* START OF TO BE REMOVED */
74 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
75 l_err_loc := 650;
76 SELECT COUNT(*)
77 INTO l_tmp_count
78 FROM mtl_system_items_kfv mi
79 WHERE mi.inventory_item_id = p_inventory_item_id
80 AND mi.organization_id = p_organization_id;
81
82 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
83 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
84 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
85 'Num. of rows from mi for the given inventory_item_id and organization_id:' ||
86 l_tmp_count);
87 END IF;
88
89 SELECT COUNT(*)
90 INTO l_tmp_count
91 FROM mtl_system_items_tl mtl
92 WHERE mtl.inventory_item_id = p_inventory_item_id
93 AND mtl.organization_id = p_organization_id
94 AND mtl.language = mtl.source_lang;
95
96 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
97 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
98 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
99 'Num. of rows from mtl for the given inventory_item_id and organization_id:' ||
100 l_tmp_count);
101 END IF;
102
103 SELECT COUNT(*)
104 INTO l_tmp_count
105 FROM mtl_item_categories mic
106 WHERE mic.inventory_item_id = p_inventory_item_id
107 AND mic.organization_id = p_organization_id
108 AND mic.category_set_id = 2;
109
110 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
111 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
112 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
113 'Num. of rows from mic for the given inventory_item_id and organization_id:' ||
114 l_tmp_count);
115 END IF;
116 END IF;
117 /* END OF TO BE REMOVED */
118
119 l_err_loc := 700;
120 -- Set the global parameter ICX_CAT_UTIL_PVT.g_ItemCatgChange_const
121 ICX_CAT_UTIL_PVT.g_ItemCatgChange_const := FALSE;
122
123 --populateItemChange will always join with mtl_parameters to figure out
124 --the child orgs, If we do it otherwise, then we have to open the
125 --populateItemChange cursor many times for each child org
126
127 IF (P_DML_TYPE = 'DELETE') THEN
128 -- Info: Deleting an item from the master org also deletes the items from its child orgs
129 l_err_loc := 800;
130 ICX_CAT_POPULATE_MI_PVT.populateItemDelete(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
131 ELSE
132 l_err_loc := 900;
133 ICX_CAT_POPULATE_MI_PVT.populateItemChange(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID, NULL, NULL);
134 END IF;
135
136 l_err_loc := 1000;
137 -- Standard check of P_COMMIT
138 IF (FND_API.To_Boolean(P_COMMIT)) THEN
139 l_err_loc := 1100;
140 COMMIT;
141 l_err_loc := 1200;
142 -- Call the rebuild index
143 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
144 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
145 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
146 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
147 'Rebuild indexes called.');
148 END IF;
149 ELSE
150 l_err_loc := 1300;
151 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
152 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
153 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
154 'p_commit is false, so Rebuild indexes is not called in.');
155 END IF;
156 END IF;
157 l_err_loc := 1400;
158
159 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
160 l_end_date := sysdate;
161 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
162 l_api_name || ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
163 END IF;
164 EXCEPTION
165 WHEN OTHERS THEN
166 BEGIN
167 ROLLBACK TO populateItemChange_sp;
168 EXCEPTION
169 WHEN OTHERS THEN
170 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
171 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
172 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
173 'ROLLBACK TO the savepoint caused the exception -->'
174 || SQLERRM);
175 END IF;
176 NULL;
177 END;
178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
179 END populateItemChange;
180
181 -- Called from inventory's item open interface (IOI) for the following actions:
182 -- 1. Create/Update an item P_ENTITY_TYPE = 'ITEM'
183 -- 2. Translation of an item is updated P_ENTITY_TYPE = 'ITEM'
184 -- 3. An item is assigned to an org P_ENTITY_TYPE = 'ITEM'
185 -- 4. Create/Update/Delete of an items category assignment P_ENTITY_TYPE = 'ITEM_CATEGORY'
186 -- Join with MTL_ITEM_BULKLOAD_RECS to get the changed inventory_item_id and organization_id
187 PROCEDURE populateBulkItemChange
188 ( p_api_version IN NUMBER ,
189 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
190 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
191 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL ,
192 x_return_status OUT NOCOPY VARCHAR2 ,
193 p_request_id IN NUMBER ,
194 p_entity_type IN VARCHAR2
195 )
196 IS
197 l_api_name CONSTANT VARCHAR2(30) := 'populateBulkItemChange';
198 l_api_version CONSTANT NUMBER := 1.0;
199 l_err_loc PLS_INTEGER;
200 l_start_date DATE;
201 l_end_date DATE;
202 l_log_string VARCHAR2(2000);
203 BEGIN
204 l_err_loc := 100;
205 x_return_status := FND_API.G_RET_STS_SUCCESS;
206
207 l_err_loc := 200;
208 -- Standard Start of API savepoint
209 SAVEPOINT populateBulkItemChange_sp;
210
211 l_err_loc := 300;
212 l_start_date := sysdate;
213
214 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
215 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
216 ', p_api_version:' || p_api_version ||
217 ', p_commit:' || p_commit ||
218 ', p_request_id:' || p_request_id ||
219 ', p_entity_type:' || p_entity_type;
220 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
221 END IF;
222
223 l_err_loc := 400;
224 -- Standard call to check for call compatibility.
225 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
226 p_api_version ,
227 l_api_name ,
228 G_PKG_NAME )
229 THEN
230 l_err_loc := 500;
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 END IF;
233
234 l_err_loc := 600;
235 ICX_CAT_UTIL_PVT.setCommitParameter(P_COMMIT);
236
237 l_err_loc := 650;
238 -- Log the values from mtl_bulkload_item_recs:
239 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
240 ICX_CAT_UTIL_PVT.logMtlItemBulkloadRecsData(p_request_id);
241 END IF;
242
243 l_err_loc := 700;
244 IF (P_ENTITY_TYPE = 'ITEM') THEN
245 l_err_loc := 800;
246 -- Set the global parameter ICX_CAT_UTIL_PVT.g_ItemCatgChange_const
247 ICX_CAT_UTIL_PVT.g_ItemCatgChange_const := FALSE;
248
249 l_err_loc := 850;
250 ICX_CAT_POPULATE_MI_PVT.populateItemChange(NULL, NULL, P_REQUEST_ID, P_ENTITY_TYPE);
251 ELSIF (P_ENTITY_TYPE = 'ITEM_CATEGORY') THEN
252 l_err_loc := 900;
253 -- Set the global parameter ICX_CAT_UTIL_PVT.g_ItemCatgChange_const
254 ICX_CAT_UTIL_PVT.g_ItemCatgChange_const := TRUE;
255
256 l_err_loc := 950;
257 ICX_CAT_POPULATE_MI_PVT.populateItemCatgChange(NULL, NULL, NULL, P_REQUEST_ID, P_ENTITY_TYPE);
258 ELSE
259 l_err_loc := 1000;
260 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
261 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
262 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
263 'Invalid entity_type:' || P_ENTITY_TYPE);
264 END IF;
265 END IF;
266
267 l_err_loc := 1100;
268 -- Standard check of P_COMMIT
269 IF (FND_API.To_Boolean(P_COMMIT)) THEN
270 l_err_loc := 1200;
271 COMMIT;
272 l_err_loc := 1300;
273 -- Call the rebuild index
274 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
275 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
276 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
277 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
278 'Rebuild indexes called.');
279 END IF;
280 ELSE
281 l_err_loc := 1400;
282 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
283 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
284 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
285 'p_commit is false, so Rebuild indexes is not called.');
286 END IF;
287 END IF;
288
289 l_err_loc := 1500;
290 l_end_date := sysdate;
291 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
292 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
293 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
294 END IF;
295 EXCEPTION
296 WHEN OTHERS THEN
297 BEGIN
298 ROLLBACK TO populateBulkItemChange_sp;
299 EXCEPTION
300 WHEN OTHERS THEN
301 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
302 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
303 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
304 'ROLLBACK TO the savepoint caused the exception -->'
305 || SQLERRM);
306 END IF;
307 NULL;
308 END;
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 END populateBulkItemChange;
311
312 -- Called from inventory forms / HTML interface for the following actions:
313 -- 1. Item category assignment is created
314 -- 2. Item category assignment is updated
315 -- 3. Item category assignment is deleted
316 PROCEDURE populateItemCategoryChange
317 ( p_api_version IN NUMBER ,
318 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
319 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
320 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL ,
321 x_return_status OUT NOCOPY VARCHAR2 ,
322 p_dml_type IN VARCHAR2 ,
323 p_inventory_item_id IN NUMBER ,
324 p_item_number IN VARCHAR2 ,
325 p_organization_id IN NUMBER ,
326 p_master_org_flag IN VARCHAR2 ,
327 p_category_set_id IN NUMBER ,
328 p_category_id IN NUMBER
329 )
330 IS
331 l_api_name CONSTANT VARCHAR2(30) := 'populateItemCategoryChange';
332 l_api_version CONSTANT NUMBER := 1.0;
333 l_err_loc PLS_INTEGER;
334 l_start_date DATE;
335 l_end_date DATE;
336 l_log_string VARCHAR2(2000);
337 BEGIN
338 l_err_loc := 100;
339 x_return_status := FND_API.G_RET_STS_SUCCESS;
340
341 l_err_loc := 200;
342 -- Standard Start of API savepoint
343 SAVEPOINT populateItemCategoryChange_sp;
344
345 l_err_loc := 300;
346 l_start_date := sysdate;
347
348 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
349 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
350 ', p_api_version:' || p_api_version ||
351 ', p_commit:' || p_commit ||
352 ', p_dml_type:' || p_dml_type ||
353 ', p_inventory_item_id:' || p_inventory_item_id ||
354 ', p_item_number:' || p_item_number ||
355 ', p_organization_id:' || p_organization_id ||
356 ', p_master_org_flag:' || p_master_org_flag ||
357 ', p_category_set_id:' || p_category_set_id ||
358 ', p_category_id:' || p_category_id;
359 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
360 END IF;
361
362 l_err_loc := 400;
363 -- Standard call to check for call compatibility.
364 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
365 p_api_version ,
366 l_api_name ,
367 G_PKG_NAME )
368 THEN
369 l_err_loc := 500;
370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
371 END IF;
372
373 l_err_loc := 600;
374 --Initialize the purchasing category set info.
375 ICX_CAT_UTIL_PVT.getPurchasingCategorySetInfo;
376
377 l_err_loc := 700;
378 IF (ICX_CAT_UTIL_PVT.g_category_set_id <> P_CATEGORY_SET_ID) THEN
379 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
380 FND_LOG.string(FND_LOG.LEVEL_EVENT,
381 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
382 'returning; g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id );
383 END IF;
384 RETURN;
385 END IF;
386
387 l_err_loc := 800;
388 ICX_CAT_UTIL_PVT.setCommitParameter(P_COMMIT);
389
390 l_err_loc := 900;
391 -- Set the global parameter ICX_CAT_UTIL_PVT.g_ItemCatgChange_const
392 ICX_CAT_UTIL_PVT.g_ItemCatgChange_const := TRUE;
393
394 IF (P_DML_TYPE = 'DELETE') THEN
395 l_err_loc := 1000;
396 ICX_CAT_POPULATE_MI_PVT.populateItemCatgDelete(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
397 ELSE
398 l_err_loc := 1100;
399 ICX_CAT_POPULATE_MI_PVT.populateItemCatgChange(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID, P_CATEGORY_ID, NULL, NULL);
400 END IF;
401
402 l_err_loc := 1200;
403 -- Standard check of P_COMMIT
404 IF (FND_API.To_Boolean(P_COMMIT)) THEN
405 l_err_loc := 1300;
406 COMMIT;
407 l_err_loc := 1400;
408 -- Call the rebuild index
409 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
410 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
411 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
412 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
413 'Rebuild indexes called.');
414 END IF;
415 ELSE
416 l_err_loc := 1500;
417 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
419 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
420 'p_commit is false, so Rebuild indexes is not called.');
421 END IF;
422 END IF;
423
424 l_err_loc := 1600;
425 l_end_date := sysdate;
426 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
428 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
429 END IF;
430 EXCEPTION
431 WHEN OTHERS THEN
432 BEGIN
433 ROLLBACK TO populateItemCategoryChange_sp;
434 EXCEPTION
435 WHEN OTHERS THEN
436 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
437 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
438 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
439 'ROLLBACK TO the savepoint caused the exception -->'
440 || SQLERRM);
441 END IF;
442 NULL;
443 END;
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445 END populateItemCategoryChange;
446
447 END ICX_CAT_POPULATE_MI_GRP;