[Home] [Help]
PACKAGE BODY: APPS.EGO_INV_ITEM_CATALOG_PVT
Source
1 PACKAGE BODY EGO_INV_ITEM_CATALOG_PVT AS
2 /* $Header: EGOITCCB.pls 120.9 2007/08/06 10:39:52 asjohal ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_INV_ITEM_CATALOG_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := 'EGO';
6 G_PKG_NAME_TOKEN CONSTANT VARCHAR2(8) := 'PKG_NAME';
7 G_API_NAME_TOKEN CONSTANT VARCHAR2(8) := 'API_NAME';
8 G_SQL_ERR_MSG_TOKEN CONSTANT VARCHAR2(11) := 'SQL_ERR_MSG';
9 G_PLSQL_ERR CONSTANT VARCHAR2(17) := 'EGO_PLSQL_ERR';
10
11 -- Developer debugging
12 PROCEDURE code_debug (p_msg IN VARCHAR2) IS
13 BEGIN
14 --sri_debug ('EGOITCCB '||p_msg);
15 RETURN;
16 EXCEPTION
17 WHEN OTHERS THEN
18 NULL;
19 END code_debug;
20
21 ------------------------------------------------------------------------------
22 -- Start OF comments
23 -- API name : Get_Error_msg
24 -- TYPE : Private
25 -- Pre-reqs : None
26 -- PROCEDURE : To get the error message.
27 -- Remarks : Created as a part of bug 3637854
28 -----------------------------------------------------------------------------
29 Procedure Get_Error_msg (p_inventory_item_id IN NUMBER
30 ,p_organization_id IN NUMBER
31 ,p_item_revision_id IN NUMBER
32 ,p_message_name IN VARCHAR2
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 ) IS
37 l_item_number VARCHAR2(999);
38 l_org_name VARCHAR2(999);
39 l_revision VARCHAR2(999);
40
41 BEGIN
42 -- note:
43 -- revision id is passed if the error message should be revision specific
44 --
45 IF p_message_name IN ('EGO_ITEM_LC_PROJ_EXISTS',
46 'EGO_ITEM_PENDING_CHANGES_EXIST',
47 'EGO_ITEM_PENDING_REC_EXISTS') THEN
48 --
49 -- get item name
50 --
51 SELECT concatenated_segments
52 INTO l_item_number
53 FROM mtl_system_items_kfv
54 WHERE inventory_item_id = p_inventory_item_id
55 AND organization_id = p_organization_id;
56 --
57 -- get organiation name
58 --
59 SELECT name
60 INTO l_org_name
61 FROM hr_all_organization_units_vl
62 WHERE organization_id = p_organization_id;
63 --
64 -- create the mesage
65 --
66 IF p_item_revision_id IS NOT NULL THEN
67 --
68 -- bug: 3696801 decoding the messages
69 --
70 IF p_message_name = 'ITEM_LC_PROJ_EXISTS' THEN
71 fnd_message.set_name('EGO', 'EGO_ITEM_REV_LC_PROJ_EXISTS');
72 ELSIF p_message_name = 'EGO_ITEM_PENDING_CHANGES_EXIST' THEN
73 fnd_message.set_name('EGO', 'EGO_REV_PEND_CHANGES_EXIST');
74 ELSIF p_message_name = 'EGO_ITEM_PENDING_REC_EXISTS' THEN
75 fnd_message.set_name('EGO', 'EGO_REV_PEND_REC_EXISTS');
76 END IF;
77 --
78 -- get revision name
79 --
80 SELECT revision
81 INTO l_revision
82 FROM mtl_item_revisions_b
83 WHERE revision_id = p_item_revision_id;
84 fnd_message.set_token('REVISION', l_revision);
85 ELSE
86 fnd_message.set_name('EGO', p_message_name);
87 END IF;
88 fnd_message.set_token('ITEM_NUMBER', l_item_number);
89 fnd_message.set_token('ORG_NAME', l_org_name);
90 ELSE
91 fnd_message.set_name('EGO', p_message_name);
92 END IF;
93 x_msg_count := 1;
94 x_msg_data := fnd_message.get();
95 --
96 -- return status as Error
97 --
98 x_return_status := FND_API.G_RET_STS_ERROR;
99 EXCEPTION
100 WHEN OTHERS THEN
101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
102 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
103 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
104 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, 'GET_ERROR_MSG');
105 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
106 x_msg_count := 1;
107 x_msg_data := FND_MESSAGE.GET;
108 END Get_Error_Msg;
109
110 ------------------------------------------------------------------------------
111 -- Start OF comments
112 -- API name : Sync_Item_Revisions
113 -- TYPE : Private (made as a part of bug 3637854
114 -- Pre-reqs : None
115 -- PROCEDURE : Sets the LC of Item Rev to that of Item and phase to default phase
116 -- Remarks : Assumed that the same lifecycle is associated at all orgs
117 -- in the org hierarchy
118 -----------------------------------------------------------------------------
119
120 Procedure Sync_Item_Revisions (
121 p_inventory_item_id IN NUMBER
122 ,p_organization_id IN NUMBER
123 ,p_lifecycle_id IN NUMBER
124 ,p_lifecycle_phase_id IN NUMBER
125 ,p_validate_changes IN VARCHAR2
126 ,p_new_cc_in_hier IN BOOLEAN := FALSE --Bug: 4060185
127 ,x_return_status OUT NOCOPY VARCHAR2
128 ,x_msg_count OUT NOCOPY NUMBER
129 ,x_msg_data OUT NOCOPY VARCHAR2
130 ) IS
131
132 CURSOR c_get_default_phase_id (cp_lifecycle_id IN NUMBER) IS
133 SELECT pev_p.PROJ_ELEMENT_ID
134 FROM PA_PROJ_ELEMENT_VERSIONS pev_l,
135 PA_LIFECYCLE_USAGES plu,
136 PA_PROJ_ELEMENT_VERSIONS pev_p,
137 PA_PROJ_ELEMENTS PPE_P,
138 PA_PROJECT_STATUSES pc
139 WHERE pev_l.OBJECT_TYPE = 'PA_STRUCTURES'
140 AND pev_l.PROJ_ELEMENT_ID = cp_lifecycle_id
141 AND pev_l.PROJECT_ID = 0
142 AND plu.USAGE_TYPE = 'PRODUCTS'
143 AND plu.LIFECYCLE_ID = pev_l.PROJ_ELEMENT_ID
144 AND pev_l.ELEMENT_VERSION_ID = pev_p.PARENT_STRUCTURE_VERSION_ID
145 AND pev_p.PROJ_ELEMENT_ID = ppe_p.PROJ_ELEMENT_ID
146 AND ppe_p.PHASE_CODE = pc.PROJECT_STATUS_CODE
147 AND (pc.START_DATE_ACTIVE IS NULL OR pc.START_DATE_ACTIVE <= SYSDATE)
148 AND (pc.END_DATE_ACTIVE IS NULL OR pc.END_DATE_ACTIVE >= SYSDATE)
149 ORDER BY pev_p.DISPLAY_SEQUENCE;
150
151 CURSOR c_get_item_rev_details (cp_item_id IN NUMBER
152 ,cp_org_id IN NUMBER) IS
153 SELECT rowid, revision, revision_id, lifecycle_id, current_phase_id, organization_id
154 FROM mtl_item_revisions_b item_rev
155 WHERE inventory_item_id = cp_item_id
156 AND EXISTS
157 (SELECT P2.ORGANIZATION_ID
158 FROM MTL_PARAMETERS P1,
159 MTL_PARAMETERS P2
160 WHERE P1.ORGANIZATION_ID = cp_org_id
161 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
162 AND p2.organization_id = item_rev.organization_id
163 )
164 AND lifecycle_id IS NOT NULL
165 AND current_phase_id IS NOT NULL;
166
167 l_api_name VARCHAR2(30);
168 l_item_rev_def_phase_id NUMBER;
169 l_item_rev_lifecycle_id NUMBER;
170 l_user_id NUMBER;
171 l_login_id NUMBER;
172 l_sysdate DATE;
173
174 BEGIN
175 l_api_name := 'Sync_Item_Revisions';
176 code_debug(l_api_name||': Started ');
177 l_item_rev_lifecycle_id := p_lifecycle_id;
178 IF p_lifecycle_id IS NULL THEN
179 l_item_rev_lifecycle_id := NULL;
180 l_item_rev_def_phase_id := NULL;
181 ELSE
182 OPEN c_get_default_phase_id (cp_lifecycle_id => p_lifecycle_id);
183 FETCH c_get_default_phase_id INTO l_item_rev_def_phase_id;
184 IF c_get_default_phase_id%NOTFOUND THEN
185 l_item_rev_lifecycle_id := NULL;
186 l_item_rev_def_phase_id := NULL;
187 END IF;
188 CLOSE c_get_default_phase_id;
189 END IF; -- p_lifecycle_id
190
191 l_user_id := FND_GLOBAL.User_Id;
192 l_login_id := FND_GLOBAL.Login_Id;
193 l_sysdate := SYSDATE;
194
195 FOR Item_Rev_Record IN c_get_item_rev_details (cp_item_id => p_inventory_item_id
196 ,cp_org_id => p_organization_id)
197 LOOP
198 code_debug(l_api_name||' Checking pending change orders for revision '|| Item_Rev_Record.revision);
199 IF (FND_API.TO_BOOLEAN(p_validate_changes) AND NOT p_new_cc_in_hier) THEN --Bug: 4060185
200 Check_Pending_Change_Orders
201 (p_inventory_item_id => p_inventory_item_id
202 ,p_organization_id => Item_Rev_Record.organization_id
203 ,p_revision_id => Item_Rev_Record.revision_id
204 ,p_lifecycle_changed => FND_API.G_FALSE
205 ,p_lifecycle_phase_changed => FND_API.G_TRUE
206 ,p_change_id => NULL
207 ,p_change_line_id => NULL
208 ,x_return_status => x_return_status
209 ,x_msg_count => x_msg_count
210 ,x_msg_data => x_msg_data
211 );
212 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
213 code_debug(l_api_name||' returning Check_Pending_Change_Orders with status '|| x_return_status);
214 RETURN;
215 END IF;
216 END IF;
217 --
218 code_debug(l_api_name||': before in Sync revisions check_floating_attachments');
219
220 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => p_inventory_item_id
221 ,p_revision_id => Item_Rev_Record.revision_id
222 ,p_organization_id => p_organization_id
223 ,p_lifecycle_id => p_lifecycle_id
224 ,p_new_phase_id => p_lifecycle_phase_id
225 ,x_return_status => x_return_status
226 ,x_msg_count => x_msg_count
227 ,x_msg_data => x_msg_data );
228
229 code_debug(l_api_name||': check_floating_attachments' || x_return_status);
230 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
231 RETURN;
232 END IF;
233
234
235 -- update item revision
236 --
237 UPDATE mtl_item_revisions_b
238 SET lifecycle_id = l_item_rev_lifecycle_id,
239 current_phase_id = l_item_rev_def_phase_id,
240 last_update_date = l_sysdate,
241 last_updated_by = l_user_id,
242 last_update_login = l_login_id
243 WHERE rowid = Item_Rev_Record.rowid;
244 --
245 -- create history records
246 --
247 code_debug(l_api_name||': Creating Phase History Record ');
248 Create_Phase_History_Record (
249 p_api_version => 1.0
250 ,p_commit => FND_API.G_FALSE
251 ,p_inventory_item_id => p_inventory_item_id
252 ,p_organization_id => Item_Rev_Record.organization_id
253 ,p_revision_id => Item_Rev_Record.Revision_id
254 ,p_lifecycle_id => l_item_rev_lifecycle_id
255 ,p_lifecycle_phase_id => l_item_rev_def_phase_id
256 ,p_item_status_code => NULL
257 ,x_return_status => x_return_status
258 ,x_msg_count => x_msg_count
259 ,x_msg_data => x_msg_data
260 );
261 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
262 RETURN;
263 END IF;
264 --
265 -- update item revision project
266 --
267 code_debug(l_api_name||': Delete project associations ');
268 DELETE EGO_ITEM_PROJECTS
269 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
270 AND ORGANIZATION_ID = Item_Rev_Record.organization_id
271 -- AND REVISION = Item_Rev_Record.revision
272 AND revision_id = Item_Rev_Record.revision_id
273 AND ASSOCIATION_TYPE = 'EGO_ITEM_PROJ_ASSOC_TYPE'
274 AND ASSOCIATION_CODE = 'LIFECYCLE_TRACKING';
275 END LOOP; -- item revision loop
276 x_return_status := FND_API.G_RET_STS_SUCCESS;
277 code_debug(l_api_name||': Bye Bye ');
278 EXCEPTION
279 WHEN OTHERS THEN
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
281 IF c_get_default_phase_id%ISOPEN THEN
282 CLOSE c_get_default_phase_id;
283 END IF;
284 IF c_get_item_rev_details%ISOPEN THEN
285 CLOSE c_get_item_rev_details;
286 END IF;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
289 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
290 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, 'SYNC_ITEM_REVISIONS');
291 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
292 x_msg_count := 1;
293 x_msg_data := FND_MESSAGE.GET;
294 code_debug(' Exception in Sync_Item_Revisions : ' ||x_msg_data );
295 END Sync_Item_Revisions;
296
297 ------------------------------------------------------------------------------
298 -- Start OF comments
299 -- API name : Change_Item_Lifecycle
300 -- TYPE : Public
301 -- Pre-reqs : None
302 -- PROCEDURE : Changes Items lifecycle-phase-status(including child org items)
303 -- Org-Status profile is not honoured.
304 -- API doesnt validate the ID's passed.(Since gets ID's from UI)
305 ------------------------------------------------------------------------------
306 Procedure Change_Item_Lifecycle (
307 P_INVENTORY_ITEM_ID IN NUMBER,
308 P_ORGANIZATION_ID IN NUMBER,
309 P_NEW_CATALOG_CATEGORY_ID IN NUMBER,
310 P_NEW_LIFECYCLE_ID IN NUMBER,
311 P_NEW_PHASE_ID IN NUMBER,
312 P_NEW_ITEM_STATUS_CODE IN VARCHAR2,
313 P_COMMIT IN VARCHAR2,
314 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
315 X_MSG_COUNT OUT NOCOPY NUMBER,
316 X_MSG_DATA OUT NOCOPY VARCHAR2 ) IS
317
318 CURSOR c_get_item_details(cp_item_id IN NUMBER, cp_org_id IN NUMBER) IS
319 SELECT rowid,
320 organization_id,
321 lifecycle_id,
322 current_phase_id,
323 inventory_item_status_code,
324 item_catalog_group_id,
325 description,
326 concatenated_segments
327 FROM mtl_system_items_kfv item -- changed for Business Event Enh.
328 WHERE inventory_item_id = cp_item_id
329 AND EXISTS
330 (SELECT P2.ORGANIZATION_ID
331 FROM MTL_PARAMETERS P1,
332 MTL_PARAMETERS P2
333 WHERE P1.ORGANIZATION_ID = cp_org_id
334 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
335 AND P2.ORGANIZATION_ID = item.ORGANIZATION_ID
336 );
337
338 CURSOR c_get_item_curr_data (cp_item_id IN NUMBER, cp_org_id IN NUMBER) IS
339 SELECT item_catalog_group_id,
340 lifecycle_id,
341 current_phase_id,
342 inventory_item_status_code
343 FROM mtl_system_items_b item
344 WHERE inventory_item_id = cp_item_id
345 AND organization_id = cp_org_id;
346
347 --Return Status Variables
348 l_ret_success CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_SUCCESS; --'S'
349 l_ret_error CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_ERROR; --'E'
350 l_ret_unexp_error CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_UNEXP_ERROR; --'U'
354 l_status_rec BOOLEAN := FALSE;
351 l_user_id CONSTANT NUMBER := FND_GLOBAL.User_Id;
352 l_login_id CONSTANT NUMBER := FND_GLOBAL.Login_Id;
353
355 l_phase_rec BOOLEAN := FALSE;
356 l_pending_flag VARCHAR2(1) := 'Y';
357 l_sysdate DATE := SYSDATE;
358 l_implemented_date DATE := SYSDATE;
359 l_msg_data VARCHAR2(1000);
360 l_organization_id NUMBER;
361 l_api_name VARCHAR2(30);
362
363 l_new_lifecycle_id NUMBER;
364 l_new_phase_id NUMBER;
365 l_new_cc_id NUMBER;
366 l_curr_lifecycle_id NUMBER;
367 l_curr_phase_id NUMBER;
368 l_curr_cc_id NUMBER;
369 l_curr_status_code MTL_SYSTEM_ITEMS_B.inventory_item_status_code%TYPE;
370 --Start 4105841
371 l_event_ret_status VARCHAR2(1);
372 l_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
373 l_raise_event VARCHAR2(1);
374 --Start 4105841
375 l_item_description VARCHAR2(2000); --R12
376 l_item_number VARCHAR2(2000); --R12
377 l_control_level VARCHAR2(1);
378
379
380 BEGIN
381 x_return_status := l_ret_error;
382 l_api_name := 'Change_Item_Lifecycle';
383 code_debug(l_api_name||': started with org id '||to_char(p_organization_id));
384 IF FND_API.To_Boolean( p_commit ) THEN
385 SAVEPOINT Change_Item_Lifecycle;
386 END IF;
387 code_debug(l_api_name||': Taking the null values ');
388 IF p_new_catalog_category_id = -1 THEN
389 l_new_cc_id := NULL;
390 ELSE
391 l_new_cc_id := p_new_catalog_category_id;
392 END IF;
393 IF p_new_lifecycle_id = -1 THEN
394 l_new_lifecycle_id := NULL;
395 ELSE
396 l_new_lifecycle_id := p_new_lifecycle_id;
397 END IF;
398 IF p_new_phase_id = -1 THEN
399 l_new_phase_id := NULL;
400 ELSE
401 l_new_phase_id := p_new_phase_id;
402 END IF;
403
404 code_debug(l_api_name||': Fetching current values ');
405 OPEN c_get_item_curr_data (cp_item_id => p_inventory_item_id
406 ,cp_org_id => p_organization_id);
407 FETCH c_get_item_curr_data INTO
408 l_curr_cc_id,
409 l_curr_lifecycle_id,
410 l_curr_phase_id,
411 l_curr_status_code;
412 CLOSE c_get_item_curr_data;
413 -- when calling from UI, the hierarchy check is already done
414 -- if the chosen catalog category is not within the mentioned hierarchy
415 -- error is flashed at the UI itself.
416 code_debug(l_api_name||': Calling Validate_And_Change_Item_LC ');
417
418 code_debug(l_api_name||': check_floating_attachments');
419
420 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => p_inventory_item_id
421 ,p_revision_id => NULL
422 ,p_organization_id => p_organization_id
423 ,p_lifecycle_id => l_new_lifecycle_id
424 ,p_new_phase_id => l_new_phase_id
425 ,x_return_status => x_return_status
426 ,x_msg_count => x_msg_count
427 ,x_msg_data => x_msg_data );
428
429 code_debug(l_api_name||': check_floating_attachments' || x_return_status);
430 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
431 IF FND_API.To_Boolean( p_commit ) THEN
432 ROLLBACK TO Change_Item_Lifecycle;
433 END IF;
434 RETURN;
435 END IF;
436
437 Validate_And_Change_Item_LC(
438 p_api_version => 1.0
439 ,p_commit => FND_API.G_FALSE
440 ,p_inventory_item_id => p_inventory_item_id
441 ,p_item_revision_id => NULL
442 ,p_organization_id => p_organization_id
443 ,p_fetch_curr_values => FND_API.G_FALSE
444 ,p_curr_cc_id => l_curr_cc_id
445 ,p_new_cc_id => l_new_cc_id
446 ,p_is_new_cc_in_hier => FND_API.G_TRUE
447 ,p_curr_lc_id => l_curr_lifecycle_id
448 ,p_new_lc_id => l_new_lifecycle_id
449 ,p_curr_lcp_id => l_curr_phase_id
450 ,p_new_lcp_id => l_new_phase_id
451 ,p_change_id => NULL
452 ,p_change_line_id => NULL
453 ,x_return_status => x_return_status
454 ,x_msg_count => x_msg_count
455 ,x_msg_data => x_msg_data
456 );
457 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
458 IF FND_API.To_Boolean( p_commit ) THEN
459 ROLLBACK TO Change_Item_Lifecycle;
460 END IF;
461 RETURN;
462 END IF;
463
464 FOR Item_Record IN c_get_item_details(cp_item_id => p_inventory_item_id
465 ,cp_org_id => p_organization_id ) LOOP
466
467 l_status_rec := FALSE;
468 l_phase_rec := FALSE;
469 l_pending_flag := 'N';
470
471 -- bug 3571186
472 -- checking if there are any pending changes before flashing error
476 OR
473 code_debug(l_api_name||': curr lifecycle id'||Item_Record.lifecycle_id||' new lifecycle_id '|| p_new_lifecycle_id);
474 code_debug(l_api_name||': curr phase id'||Item_Record.current_phase_id||' new phase id '|| p_new_phase_id);
475 IF ( NVL(Item_Record.lifecycle_id,-1) <> NVL(p_new_lifecycle_id,-1)
477 NVL(Item_Record.current_phase_id,-1) <> NVL(p_new_phase_id,-1)
478 ) THEN
479 --
480 -- user is changing lifecycle OR lifecycle phase
481 --
482 --If there is a phase change, then insert a record into mtl_pending_item_status
483 code_debug(l_api_name||': changing the lifecycle now ');
484 l_phase_rec := TRUE;
485 l_pending_flag := 'N';
486 l_implemented_date := l_sysdate;
487 l_raise_event := 'Y';
488
489 UPDATE mtl_system_items_b
490 SET lifecycle_id = DECODE(p_new_lifecycle_id,-1,NULL,p_new_lifecycle_id),
491 current_phase_id = DECODE(p_new_phase_id,-1,NULL,p_new_phase_id),
492 last_update_date = l_sysdate,
493 last_updated_by = l_user_id,
494 last_update_login = l_login_id
495 WHERE rowid = Item_Record.rowid;
496
497 END IF;
498
499 SELECT control_level into l_control_level
500 from mtl_item_attributes
501 where attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
502
503 IF Item_Record.inventory_item_status_code <> p_new_item_status_code
504 AND
505 ( l_control_level <> '2'
506 OR l_NEW_PHASE_ID IS NOT NULL
507 OR l_NEW_LIFECYCLE_ID IS NOT NULL
508 OR (l_NEW_PHASE_ID IS NULL AND l_NEW_LIFECYCLE_ID IS NULL
509 AND l_control_level = '2' AND Item_Record.organization_id = p_organization_id)
510 ) THEN
511 --If status changes insert into pending table as pending
512 --Run the update item status function to set status controlled attributed
513 --Update Item Status of Item in current organization only if lifeCycle and Phase are not given and control
514 -- level for Item Status attribute is org controlled.
515 l_status_rec := TRUE;
516 l_pending_flag := 'Y';
517 l_implemented_date := NULL;
518 ELSE
519 l_status_rec := FALSE;
520 -- Bug 6241605: If the phase has been changed and the same status is selected same as old then
521 -- phase record should be inserted in the pending item status table.So commenting out the nex line.
522 --l_phase_rec := FALSE;
523 END IF;
524
525 IF (l_status_rec OR l_phase_rec) THEN
526 code_debug(l_api_name||': creating a pending item status ');
527 l_phase_rec := FALSE;
528 INSERT INTO mtl_pending_item_status(
529 inventory_item_id,
530 organization_id,
531 effective_date,
532 implemented_date,
533 pending_flag,
534 last_update_date,
535 last_updated_by,
536 creation_date,
537 created_by,
538 lifecycle_id,
539 phase_id,
540 status_code)
541 VALUES(
542 p_inventory_item_id,
543 Item_Record.organization_id,
544 l_sysdate,
545 l_implemented_date,
546 l_pending_flag,
547 l_sysdate,
548 l_user_id,
549 l_sysdate,
550 l_user_id,
551 DECODE(p_new_lifecycle_id,-1,NULL,p_new_lifecycle_id),
552 DECODE(p_new_phase_id,-1,NULL,p_new_phase_id),
553 p_new_item_status_code );
554 END IF;
555
556 IF l_pending_flag ='Y' THEN
557 --
558 -- change is from the status change only
559 --
560 code_debug(l_api_name||': implementing the pending status ');
561 INV_ITEM_STATUS_PUB.Update_Pending_Status(
562 p_api_version => 1.0
563 ,p_org_id => Item_Record.organization_id
564 ,p_item_id => p_inventory_item_id
565 ,p_init_msg_list => NULL
566 ,p_commit => NULL
567 ,x_return_status => x_return_status
568 ,x_msg_count => x_msg_count
569 ,x_msg_data => x_msg_data);
570 l_raise_event := 'Y';
571 END IF;
572
573 /*R12 Business Events
574 Store the item number and description
575 for passsing to raise event call */
576 IF l_item_number IS NULL THEN
577 l_item_number := Item_Record.Concatenated_segments;
578 l_item_description := Item_Record.Description;
579 END IF;
580 --R12 Business Events
581
582 END LOOP;
583
584 --R12 Moved the code outside the loop to raise the event
585 --Start 4105841
586 --Raise the Item Update Event for Lifecycle,Phase or Status Change
587 --If Catalog Category is also changed,then don't raise the event here
588 --as it will be raised by Change Item Catalog
589 IF l_raise_event = 'Y'
590 AND NVL(l_curr_cc_id,-1) = NVL(p_new_catalog_category_id,-1)
591 THEN
592 SELECT ORGANIZATION_CODE INTO l_org_code
593 FROM MTL_PARAMETERS
594 WHERE ORGANIZATION_ID = p_organization_id;
598 ,p_organization_code => l_org_code
595 EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
596 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
597 ,p_Organization_Id => p_Organization_Id
599 ,p_Inventory_Item_Id => p_inventory_item_id
600 ,p_item_number => l_item_number
601 ,p_item_description => l_item_description
602 ,x_msg_data => l_msg_data
603 ,x_return_status => l_event_ret_status
604 );
605 END IF;
606 --End 4105841 Raise events
607
608 --Call ICX APIs
609 BEGIN
610 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
611 p_entity_type => 'ITEM'
612 ,p_dml_type => 'UPDATE'
613 ,p_inventory_item_id => p_inventory_item_id
614 ,p_item_number => l_item_number
615 ,p_item_description => l_item_description
616 ,p_organization_id => p_Organization_Id
617 ,p_organization_code => l_org_code );
618 EXCEPTION
619 WHEN OTHERS THEN
620 NULL;
621 END;
622 --R12: Business Event Enhancement
623
624
625
626 IF FND_API.To_Boolean( p_commit ) THEN
627 COMMIT WORK;
628 END IF;
629 x_return_status := l_ret_success;
630 code_debug(l_api_name||': Bye Bye ');
631 EXCEPTION
632 WHEN OTHERS THEN
633 IF FND_API.To_Boolean( p_commit ) THEN
634 ROLLBACK TO Change_Item_Lifecycle;
635 END IF;
636 IF c_get_item_details%ISOPEN THEN
637 CLOSE c_get_item_details;
638 END IF;
639 IF c_get_item_curr_data%ISOPEN THEN
640 CLOSE c_get_item_curr_data;
641 END IF;
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
644 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
645 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, l_api_name);
646 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
647 x_msg_count := 1;
648 x_msg_data := FND_MESSAGE.GET;
649 code_debug(' Exception in '||l_api_name||' : ' ||x_msg_data );
650 END Change_Item_Lifecycle;
651
652 ------------------------------------------------------------------------------
653 -- Start OF comments
654 -- API name : Change_Item_Catalog
655 -- TYPE : Public
656 -- Pre-reqs : None
657 -- PROCEDURE : Changes Item(including child org item) catalog category.
658 -- In effect, it will update the items lifecycle-phase-status.
659 -- Org-Status profile is not honoured. Changes Items rev life
660 -- cycle-phase if the current doesnt lie in new catalog heircy
661 -- API doesnt validate the ID's passed.(Since gets ID's from UI)
662 ------------------------------------------------------------------------------
663 Procedure Change_Item_Catalog(
664 P_INVENTORY_ITEM_ID IN NUMBER,
665 P_ORGANIZATION_ID IN NUMBER,
666 P_CATALOG_GROUP_ID IN NUMBER,
667 P_NEW_CATALOG_GROUP_ID IN NUMBER,
668 P_NEW_LIFECYCLE_ID IN NUMBER,
669 P_NEW_PHASE_ID IN NUMBER,
670 P_NEW_ITEM_STATUS_CODE IN VARCHAR2,
671 P_NEW_APPROVAL_STATUS IN VARCHAR2 DEFAULT NULL,
672 P_COMMIT IN VARCHAR2,
673 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
674 X_MSG_COUNT OUT NOCOPY NUMBER,
675 X_MSG_DATA OUT NOCOPY VARCHAR2 ) IS
676
677 CURSOR c_get_item_details(cp_item_id NUMBER) IS
678 SELECT rowid,
679 organization_id,
680 lifecycle_id,
681 current_phase_id,
682 inventory_item_status_code,
683 concatenated_segments, --added for business events.
684 description
685 FROM mtl_system_items_kfv
686 WHERE inventory_item_id = cp_item_id;
687
688 CURSOR c_get_revisions(cp_org_id NUMBER,
689 cp_item_id NUMBER) IS
690 SELECT rowid,
691 revision, --3031284
692 lifecycle_id,
693 current_phase_id
694 FROM mtl_item_revisions_b
695 WHERE organization_id = cp_org_id
696 AND inventory_item_id = cp_item_id
697 AND lifecycle_id IS NOT NULL
698 FOR UPDATE OF lifecycle_id,current_phase_id;
699
700 CURSOR c_get_master_org_details (cp_org_id IN NUMBER) IS
701 SELECT organization_id
702 FROM mtl_parameters
703 WHERE organization_id = master_organization_id
704 and organization_id = NVL(cp_org_id, organization_id);
705
706 /* Bug: 3007563
707 CURSOR c_lost_catalogs(cp_old_id NUMBER,
708 cp_new_id NUMBER) IS
709 SELECT item_catalog_group_id
710 FROM ((SELECT item_catalog_group_id
711 FROM mtl_item_catalog_groups_b
712 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
713 START WITH item_catalog_group_id = cp_old_id)
714 MINUS
715 (SELECT item_catalog_group_id
716 FROM mtl_item_catalog_groups_b
717 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
718 START WITH item_catalog_group_id = cp_new_id));
719
720 */
724 l_ret_unexp_error CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_UNEXP_ERROR; --'U'
721
722 l_ret_success CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_SUCCESS; --'S'
723 l_ret_error CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_ERROR; --'E'
725
726 l_change BOOLEAN := FALSE;
727 l_organization_id NUMBER;
728 l_api_name VARCHAR2(30);
729 --Start 4105841
730 l_msg_data VARCHAR2(4000);
731 l_event_ret_status VARCHAR2(1);
732 l_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
733 l_item_number mtl_system_items_kfv.CONCATENATED_SEGMENTS%TYPE;
734 l_item_desc mtl_system_items_kfv.DESCRIPTION%TYPE;
735 -- End 4105841
736 BEGIN
737 l_api_name := 'Change_Item_Catalog';
738 code_debug(l_api_name||': Started ');
739 code_debug(l_api_name||': p_inventory_item_id: '||to_char(p_inventory_item_id)||' p_organization_id: '||to_char(p_organization_id));
740 code_debug(l_api_name||': p_catalog_group_id: '||to_char(p_catalog_group_id) ||' p_new_catalog_group_id:'|| to_char(P_NEW_CATALOG_GROUP_ID));
741 code_debug(l_api_name||': p_new_lifecycle_id: '||to_char(p_new_lifecycle_id)||' p_new_phase_id: '||to_char(p_new_phase_id));
742 code_debug(l_api_name||': P_NEW_ITEM_STATUS_CODE: '||P_NEW_ITEM_STATUS_CODE);
743
744 IF FND_API.To_Boolean( p_commit ) THEN
745 SAVEPOINT Change_Item_Catalog;
746 END IF;
747 x_return_status := l_ret_error;
748
749 IF NVL(p_catalog_group_id,-1) <> NVL(p_new_catalog_group_id,-1) THEN
750 code_debug(l_api_name||': Changing Catalog Category ');
751 l_organization_id := NULL;
752 ELSE
753 code_debug(l_api_name||': Changing Lifecycle ');
754 l_organization_id := p_organization_id;
755 END IF;
756
757 FOR cr in c_get_master_org_details (l_organization_id) LOOP
758 code_debug(l_api_name||': Calling Change Item Lifecycle with organization_id '||to_char(cr.organization_id));
759 Change_Item_Lifecycle(
760 p_inventory_item_id => p_inventory_item_id,
761 p_organization_id => cr.organization_id,
762 p_new_catalog_category_id => p_new_catalog_group_id,
763 p_new_lifecycle_id => p_new_lifecycle_id,
764 p_new_phase_id => p_new_phase_id,
765 p_new_item_status_code => p_new_item_status_code,
766 p_commit => FND_API.G_FALSE,
767 x_return_status => x_return_status,
768 x_msg_data => x_msg_data,
769 x_msg_count => x_msg_count);
770 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
771 RETURN;
772 END IF;
773 END LOOP;
774
775 IF NVL(p_catalog_group_id,-1) <> NVL(p_new_catalog_group_id,-1) THEN
776 UPDATE mtl_system_items_b
777 SET item_catalog_group_id = DECODE(p_new_catalog_group_id,-1,NULL,p_new_catalog_group_id)
778 WHERE inventory_item_id = p_inventory_item_id;
779
780 -- Bug: 3072079 Added code to update descriptive values of new item catalog group for an item
781 INVIDIT2.Match_Catalog_Descr_Elements (p_inventory_item_id, p_new_catalog_group_id);
782
783 --Bug: 3007563 Modified all the delete statements.
784 -- FOR catalog_record IN c_lost_catalogs(p_catalog_group_id,p_new_catalog_group_id)
785 -- LOOP
786
787 --Delete AG which doesnt fall under new catalog hierarchy.
788 DELETE ego_mtl_sy_items_ext_b ext
789 WHERE inventory_item_id = p_inventory_item_id
790 AND EXISTS (SELECT NULL
791 FROM ego_obj_attr_grp_assocs_v
792 WHERE attr_group_id = ext.attr_group_id
793 AND classification_code IN
794 (SELECT to_char(item_catalog_group_id)
795 FROM mtl_item_catalog_groups_b
796 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
797 START WITH item_catalog_group_id = p_catalog_group_id))
798 AND NOT EXISTS (SELECT NULL
799 FROM ego_obj_attr_grp_assocs_v
800 WHERE attr_group_id = ext.attr_group_id
801 AND classification_code IN
802 (SELECT to_char(item_catalog_group_id)
803 FROM mtl_item_catalog_groups_b
804 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
805 START WITH item_catalog_group_id = p_new_catalog_group_id));
806
807 DELETE ego_mtl_sy_items_ext_tl extl
808 WHERE inventory_item_id = p_inventory_item_id
809 AND EXISTS (SELECT NULL
810 FROM ego_obj_attr_grp_assocs_v
811 WHERE attr_group_id = extl.attr_group_id
812 AND classification_code IN
813 (SELECT to_char(item_catalog_group_id)
814 FROM mtl_item_catalog_groups_b
815 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
816 START WITH item_catalog_group_id = p_catalog_group_id))
817 AND NOT EXISTS (SELECT NULL
818 FROM ego_obj_attr_grp_assocs_v
819 WHERE attr_group_id = extl.attr_group_id
820 AND classification_code IN
821 (SELECT to_char(item_catalog_group_id)
822 FROM mtl_item_catalog_groups_b
826 --Update valid AG to point to new catalog group
823 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
824 START WITH item_catalog_group_id = p_new_catalog_group_id));
825
827 UPDATE ego_mtl_sy_items_ext_b
828 SET item_catalog_group_id = p_new_catalog_group_id
829 WHERE inventory_item_id = p_inventory_item_id;
830
831 UPDATE ego_mtl_sy_items_ext_tl
832 SET item_catalog_group_id = p_new_catalog_group_id
833 WHERE inventory_item_id = p_inventory_item_id;
834
835 --Delete attachments which doesnt fall under new catalog hierarchy.
836 DELETE fnd_attached_documents docs
837 WHERE pk2_value = to_char(p_inventory_item_id)
838 AND entity_name IN ('MTL_ITEM_REVISIONS','MTL_SYSTEM_ITEMS')
839 AND EXISTS (SELECT NULL
840 FROM ego_objtype_attach_cats
841 WHERE attach_category_id = docs.category_id
842 AND classification_code IN
843 (SELECT to_char(item_catalog_group_id)
844 FROM mtl_item_catalog_groups_b
845 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
846 START WITH item_catalog_group_id = p_catalog_group_id))
847 AND NOT EXISTS (SELECT NULL
848 FROM ego_objtype_attach_cats
849 WHERE attach_category_id = docs.category_id
850 AND classification_code IN
851 (SELECT to_char(item_catalog_group_id)
852 FROM mtl_item_catalog_groups_b
853 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
854 START WITH item_catalog_group_id = p_new_catalog_group_id));
855
856 -- END LOOP;
857
858 --Bug 5220298 Begin
859 IF P_NEW_APPROVAL_STATUS IS NOT NULL
860 THEN
861 --{
862 UPDATE MTL_SYSTEM_ITEMS_B
863 SET APPROVAL_STATUS = p_new_approval_status
864 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
865 AND ORGANIZATION_ID = p_organization_id;
866 --}
867 END IF;
868 --Bug 5220298 End
869
870 --Start 4105841 Raise the event for the organizations to which item is assigned.
871 --Raise only for the master item
872 SELECT ORGANIZATION_CODE INTO l_org_code
873 FROM MTL_PARAMETERS
874 WHERE ORGANIZATION_ID = p_organization_id;
875 --Added for Bug 4586769
876 SELECT CONCATENATED_SEGMENTS, DESCRIPTION
877 INTO l_item_number, l_item_desc
878 FROM mtl_system_items_kfv
879 WHERE inventory_item_id = p_inventory_item_id
880 AND organization_id = p_organization_id;
881
882 EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
883 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
884 ,p_Inventory_Item_Id => p_inventory_item_id
885 ,p_Organization_Id => p_Organization_Id
886 ,p_organization_code => l_org_code
887 ,p_item_number => l_item_number
888 ,p_item_description => l_item_desc
889 ,x_msg_data => l_msg_data
890 ,x_return_status => l_event_ret_status
891 );
892 --End 4105841
893 --Call ICX APIs
894 BEGIN
895 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
896 p_entity_type => 'ITEM'
897 ,p_dml_type => 'UPDATE'
898 ,p_inventory_item_id => p_inventory_item_id
899 ,p_organization_id => p_Organization_Id
900 ,p_organization_code => l_org_code );
901 EXCEPTION
902 WHEN OTHERS THEN
903 NULL;
904 END;
905 --R12: Business Event Enhancement
906
907 END IF;
908
909 /***
910 IF x_return_status = l_ret_success THEN
911
912 FOR Item_Record IN c_get_item_details(p_inventory_item_id) LOOP
913 --Update revisions lifecycle-phase to null
914 --if existing lifecyle is not valid under new catalog.
915 FOR revision_record IN c_get_revisions
916 (cp_org_id => Item_Record.organization_id,
917 cp_item_id => p_inventory_item_id)
918 LOOP
919 l_change := INV_EGO_REVISION_VALIDATE.Check_LifeCycle
920 (p_catalog_group_id => p_new_catalog_group_id
921 ,p_lifecycle_id => revision_record.lifecycle_id);
922 IF NOT l_change THEN
923 UPDATE mtl_item_revisions_b
924 SET lifecycle_id = NULL,
925 current_phase_id = NULL
926 WHERE rowid = revision_record.rowid;
927
928 -- Start 3031284
929 DELETE EGO_ITEM_PROJECTS
930 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
931 AND REVISION = revision_record.revision
932 AND ORGANIZATION_ID = Item_Record.organization_id
933 AND ASSOCIATION_TYPE = 'EGO_ITEM_PROJ_ASSOC_TYPE'
934 AND ASSOCIATION_CODE = 'LIFECYCLE_TRACKING' ;
935 -- End 3031284
936
937 END IF;
938
939 END LOOP; -- revision_record
940
941 END LOOP; --Item Record Loop
942
946 END IF;
943 x_return_status := l_ret_success;
944 ELSE
945 RETURN;
947 ***/
948 IF FND_API.To_Boolean( p_commit ) THEN
949 COMMIT WORK;
950 INV_ITEM_PVT.SYNC_IM_INDEX; --3026311 Sync iM index
951
952 -- Call IP Intermedia Sync
953 INV_ITEM_EVENTS_PVT.Sync_IP_IM_Index;
954
955 END IF;
956 code_debug(l_api_name||': Bye Bye ');
957 EXCEPTION
958 WHEN OTHERS THEN
959 IF FND_API.To_Boolean( p_commit ) THEN
960 ROLLBACK TO Change_Item_Catalog;
961 END IF;
962 IF c_get_item_details%ISOPEN THEN
963 CLOSE c_get_item_details;
964 END IF;
965 IF c_get_revisions%ISOPEN THEN
966 CLOSE c_get_revisions;
967 END IF;
968 IF c_get_master_org_details%ISOPEN THEN
969 CLOSE c_get_master_org_details;
970 END IF;
971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
972 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
973 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
974 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, l_api_name);
975 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
976 x_msg_count := 1;
977 x_msg_data := FND_MESSAGE.GET;
978 code_debug(' Exception in '||l_api_name||' : ' ||x_msg_data );
979 END Change_Item_Catalog;
980
981
982 ------------------------------------------------------------------------------
983 -- Start OF comments
984 -- API name : Validate_And_Change_Item_LC
985 -- TYPE : Public
986 -- Pre-reqs : None
987 -- PROCEDURE : To validate and change the lifecycle dependencies
988 -- Remarks : Created as a part of bug 3637854
989 -----------------------------------------------------------------------------
990 Procedure Validate_And_Change_Item_LC (
991 p_api_version IN NUMBER
992 ,p_commit IN VARCHAR2
993 ,p_inventory_item_id IN NUMBER
994 ,p_item_revision_id IN NUMBER
995 ,p_organization_id IN NUMBER
996 ,p_fetch_curr_values IN VARCHAR2
997 ,p_curr_cc_id IN NUMBER
998 ,p_new_cc_id IN NUMBER
999 ,p_is_new_cc_in_hier IN VARCHAR2
1000 ,p_curr_lc_id IN NUMBER
1001 ,p_new_lc_id IN NUMBER
1002 ,p_curr_lcp_id IN NUMBER
1003 ,p_new_lcp_id IN NUMBER
1004 ,p_change_id IN NUMBER
1005 ,p_change_line_id IN NUMBER
1006 ,x_return_status OUT NOCOPY VARCHAR2
1007 ,x_msg_count OUT NOCOPY NUMBER
1008 ,x_msg_data OUT NOCOPY VARCHAR2
1009 ) IS
1010
1011 CURSOR c_get_curr_item_val (cp_item_id IN NUMBER
1012 ,cp_org_id IN NUMBER) IS
1013 SELECT item_catalog_group_id, lifecycle_id, current_phase_id
1014 FROM mtl_system_items_b
1015 WHERE inventory_item_id = cp_item_id
1016 AND organization_id = cp_org_id;
1017
1018 -- bug: 3802017
1019 -- get curr catalog id from item and lifecycle and phase from item
1020 -- if not defined at revision
1021 CURSOR c_get_curr_rev_val (cp_item_id IN NUMBER
1022 ,cp_org_id IN NUMBER
1023 ,cp_rev_id IN NUMBER) IS
1024 SELECT itm.item_catalog_group_id, NVL(rev.lifecycle_id,itm.lifecycle_id) lifecycle_id,
1025 NVL(rev.current_phase_id,itm.current_phase_id) current_phase_id
1026 FROM mtl_system_items_b itm, mtl_item_revisions_b rev
1027 WHERE rev.inventory_item_id = cp_item_id
1028 AND rev.organization_id = cp_org_id
1029 AND rev.revision_id = cp_rev_id
1030 AND itm.inventory_item_id = rev.inventory_item_id
1031 AND itm.organization_id = rev.organization_id;
1032
1033 CURSOR c_check_cc_hier (cp_curr_cc_id IN NUMBER
1034 ,cp_new_cc_id IN NUMBER) IS
1035 SELECT item_catalog_group_id
1036 FROM mtl_item_catalog_groups
1037 WHERE item_catalog_group_id = cp_curr_cc_id
1038 AND item_catalog_group_id IN
1039 (SELECT item_catalog_group_id
1040 FROM mtl_item_catalog_groups
1041 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
1042 START WITH item_catalog_group_id = cp_new_cc_id
1043 );
1044
1045 l_api_version NUMBER;
1046 l_api_name VARCHAR2(30);
1047 l_null_id_value NUMBER;
1048 l_dummy_id_value NUMBER;
1049
1050 l_validate BOOLEAN;
1051 l_new_cc_in_hier BOOLEAN;
1052
1053 l_new_cc_id NUMBER;
1054 l_curr_cc_id NUMBER;
1055 l_curr_lc_id NUMBER;
1056 l_curr_lcp_id NUMBER;
1057 l_curr_rev_lc_id NUMBER;
1058 l_curr_rev_lcp_id NUMBER;
1059 l_lc_changed VARCHAR2(30);
1060 l_lcp_changed VARCHAR2(30);
1061 l_perform_sync_only VARCHAR2(30);
1062
1063 BEGIN
1064 l_api_version := 1.0;
1065 l_api_name := 'Validate_And_Change_Item_LC';
1066 l_lc_changed := FND_API.G_FALSE;
1067 l_lcp_changed := FND_API.G_FALSE;
1068 l_perform_sync_only := FND_API.G_FALSE;
1069 l_null_id_value := FND_API.G_MISS_NUM;
1070
1071 code_debug(l_api_name||': Started with parameters ');
1072 code_debug(' p_api_version : '||p_api_version||' p_commit : '|| p_commit);
1073 code_debug(' p_inventory_item_id :'||p_inventory_item_id||' p_item_revision_id : '|| p_item_revision_id||' p_organization_id : '|| p_organization_id);
1077
1074 code_debug(' p_fetch_curr_values : '||p_fetch_curr_values||' p_curr_cc_id : '|| p_curr_cc_id ||' p_new_cc_id : '|| p_new_cc_id);
1075 code_debug(' p_is_new_cc_in_hier : '|| p_is_new_cc_in_hier ||' p_curr_lc_id: '|| p_curr_lc_id ||' p_new_lc_id : '|| p_new_lc_id);
1076 code_debug(' p_curr_lcp_id : '|| p_curr_lcp_id ||' p_new_lcp_id: '|| p_new_lcp_id ||' p_change_id : '|| p_change_id);
1078 --Standard checks
1079 IF NOT FND_API.Compatible_API_Call (l_api_version
1080 ,p_api_version
1081 ,l_api_name
1082 ,g_pkg_name)
1083 THEN
1084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1085 END IF;
1086
1087 IF FND_API.To_Boolean( p_commit ) THEN
1088 SAVEPOINT Validate_And_Change_Item_LC_SP;
1089 END IF;
1090
1091 IF FND_API.TO_BOOLEAN(p_fetch_curr_values) THEN
1092 IF p_item_revision_id IS NULL THEN
1093 -- fetch from item
1094 OPEN c_get_curr_item_val (cp_item_id => p_inventory_item_id
1095 ,cp_org_id => p_organization_id
1096 );
1097 FETCH c_get_curr_item_val INTO l_curr_cc_id, l_curr_lc_id, l_curr_lcp_id;
1098 CLOSE c_get_curr_item_val;
1099 ELSE
1100 -- fetch from item revision
1101 OPEN c_get_curr_rev_val (cp_item_id => p_inventory_item_id
1102 ,cp_org_id => p_organization_id
1103 ,cp_rev_id => p_item_revision_id
1104 );
1105 FETCH c_get_curr_rev_val INTO l_curr_cc_id, l_curr_rev_lc_id, l_curr_rev_lcp_id;
1106 CLOSE c_get_curr_rev_val;
1107 END IF; -- p_item_revision_id IS NULL
1108 ELSE
1109 l_curr_cc_id := p_curr_cc_id;
1110 l_curr_lc_id := p_curr_lc_id;
1111 l_curr_lcp_id := p_curr_lcp_id;
1112 END IF;
1113
1114 -- bug: 3802017
1115 IF p_new_cc_id IS NULL AND p_new_lc_id IS NOT NULL AND p_new_lcp_id IS NOT NULL AND p_item_revision_id IS NOT NULL THEN
1116 -- in item revision context, take the cc as that of item.
1117 l_new_cc_id := l_curr_cc_id;
1118 ELSE
1119 l_new_cc_id := p_new_cc_id;
1120 END IF;
1121
1122 code_debug(l_api_name||' values obtained after fetching the old values if reqd ');
1123 code_debug(l_api_name||' curr_cc_id : '|| l_curr_cc_id ||' curr_lc_id : '|| l_curr_lc_id ||' curr_lcp_id : '|| l_curr_lcp_id);
1124 code_debug(l_api_name||' new_cc_id : '|| l_new_cc_id ||' new_lc_id : '|| p_new_lc_id ||' new_lcp_id : '|| p_new_lcp_id);
1125
1126 IF NVL(l_curr_cc_id,l_null_id_value) = NVL(l_new_cc_id,l_null_id_value)
1127 AND
1128 NVL(l_curr_lc_id,l_null_id_value) = NVL(p_new_lc_id,l_null_id_value)
1129 AND
1130 NVL(l_curr_lcp_id,l_null_id_value) = NVL(p_new_lcp_id,l_null_id_value) THEN
1131 -- none of the value are changed
1132 RETURN;
1133 END IF;
1134
1135 l_validate := FALSE;
1136 IF l_curr_cc_id IS NULL THEN
1137 -- item does not have an existing catalog category
1138 -- curr_cc_id IS NULL check
1139 IF l_new_cc_id IS NULL THEN
1140 -- perform no validations.
1141 -- user cannot associate any lifecycles.
1142 NULL;
1143 ELSE
1144 -- perform validations if lc is chosen
1145 code_debug(l_api_name||' validate item as CC has changed from null to NOT Null ');
1146 IF NVL(l_curr_lc_id,l_null_id_value) <> NVL(p_new_lc_id,l_null_id_value) THEN
1147 l_validate := TRUE;
1148 l_lc_changed := FND_API.G_TRUE;
1149 l_lcp_changed := FND_API.G_TRUE;
1150 ELSE
1151 -- perform no validations.
1152 -- user
1153 NULL;
1154 END IF;
1155 END IF;
1156 ELSE -- for curr_cc_id IS NOT NULL
1157 -- item has an existing catalog category
1158 IF p_new_lc_id IS NULL THEN
1159 IF l_new_cc_id IS NULL THEN
1160 code_debug(l_api_name||' validate item as CC has changed from ' ||l_curr_cc_id || ' TO '||l_new_cc_id||' and new LC id is null ');
1161 l_validate := TRUE;
1162 l_lc_changed := FND_API.G_TRUE;
1163 l_lcp_changed := FND_API.G_TRUE;
1164 ELSE
1165 -- catalog category is changed
1166 -- but no LC associated. check hierarchy
1167 IF p_is_new_cc_in_hier = FND_API.G_TRUE THEN
1168 l_new_cc_in_hier := TRUE;
1169 ELSIF p_is_new_cc_in_hier = FND_API.G_FALSE THEN
1170 l_new_cc_in_hier := FALSE;
1171 ELSE
1172 OPEN c_check_cc_hier (cp_curr_cc_id => l_curr_cc_id
1173 ,cp_new_cc_id => l_new_cc_id
1174 );
1175 FETCH c_check_cc_hier INTO l_dummy_id_value;
1176 IF c_check_cc_hier%FOUND THEN
1177 l_new_cc_in_hier := TRUE;
1178 ELSE
1179 l_new_cc_in_hier := FALSE;
1180 END IF;
1181 END IF; -- check for hierarchy ends
1182 -- decide based on hierarchy
1183 IF l_new_cc_in_hier THEN
1184 -- perform only sync
1185 IF NVL(l_curr_lc_id,l_null_id_value) <> NVL(p_new_lc_id,l_null_id_value) THEN
1186 code_debug(l_api_name||' perform only sync as the lifecycle is changed to null ');
1187 l_validate := TRUE;
1188 l_perform_sync_only := FND_API.G_TRUE;
1189 END IF;
1190 ELSE
1194 l_lc_changed := FND_API.G_TRUE;
1191 code_debug(l_api_name||' validate item as CC has changed from '|| l_curr_cc_id || ' TO '||l_new_cc_id||' and new LC id is null and CC not in hier');
1192 IF NVL(l_curr_lc_id,l_null_id_value) <> NVL(p_new_lc_id,l_null_id_value) THEN
1193 l_validate := TRUE;
1195 l_lcp_changed := FND_API.G_TRUE;
1196 END IF;
1197 END IF;
1198 END IF; -- for l_new_cc_id IS NULL check
1199 ELSE -- new lifecycle is chosen.
1200 code_debug(l_api_name||' validate item as LC has changed from '|| l_curr_lc_id || ' TO '|| p_new_lc_id);
1201 -- user has changed the lifecycle and catalog category
1202 l_validate := TRUE;
1203 IF NVL(l_curr_cc_id,l_null_id_value) = NVL(l_new_cc_id,l_null_id_value)
1204 AND
1205 NVL(l_curr_lc_id,l_null_id_value) = NVL(p_new_lc_id,l_null_id_value)
1206 AND
1207 NVL(l_curr_lcp_id,l_null_id_value) <> NVL(p_new_lcp_id,l_null_id_value) THEN
1208 l_lcp_changed := FND_API.G_TRUE;
1209 ELSE
1210 l_lc_changed := FND_API.G_TRUE;
1211 l_lcp_changed := FND_API.G_TRUE;
1212 END IF;
1213 END IF; -- for new lc_id IS NOT NULL
1214 END IF; -- for curr_cc_id check end
1215
1216 IF l_validate THEN
1217 Change_Item_LC_Dependecies(
1218 p_api_version => p_api_version
1219 ,p_inventory_item_id => p_inventory_item_id
1220 ,p_organization_id => p_organization_id
1221 ,p_item_revision_id => p_item_revision_id
1222 ,p_lifecycle_id => p_new_lc_id
1223 ,p_lifecycle_phase_id => p_new_lcp_id
1224 ,p_lifecycle_changed => l_lc_changed
1225 ,p_lifecycle_phase_changed => l_lcp_changed
1226 ,p_perform_sync_only => l_perform_sync_only
1227 ,p_new_cc_in_hier => l_new_cc_in_hier --Bug: 4060185
1228 ,x_return_status => x_return_status
1229 ,x_msg_count => x_msg_count
1230 ,x_msg_data => x_msg_data
1231 );
1232 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1233 RETURN;
1234 END IF;
1235 END IF; -- end l_validate
1236
1237 IF FND_API.To_Boolean(p_commit) THEN
1238 COMMIT WORK;
1239 END IF;
1240 x_return_status := FND_API.G_RET_STS_SUCCESS;
1241 code_debug(l_api_name||': Bye Bye ');
1242
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 IF FND_API.To_Boolean( p_commit ) THEN
1246 ROLLBACK TO Validate_And_Change_Item_LC_SP;
1247 END IF;
1248 IF c_get_curr_item_val%ISOPEN THEN
1249 CLOSE c_get_curr_item_val;
1250 END IF;
1251 IF c_get_curr_rev_val%ISOPEN THEN
1252 CLOSE c_get_curr_rev_val;
1253 END IF;
1254 IF c_check_cc_hier%ISOPEN THEN
1255 CLOSE c_check_cc_hier;
1256 END IF;
1257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1258 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
1259 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
1260 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, l_api_name);
1261 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
1262 x_msg_count := 1;
1263 x_msg_data := FND_MESSAGE.GET;
1264 code_debug(' Exception in Validate_And_Change_Item_LC : ' ||x_msg_data );
1265
1266 END Validate_And_Change_Item_LC;
1267
1268 ------------------------------------------------------------------------------
1269 -- Start OF comments
1270 -- API name : Change_Item_LC_Dependecies
1271 -- TYPE : Public
1272 -- Pre-reqs : None
1273 -- PROCEDURE : To change the lifecycle dependencies
1274 -- Remarks : Created as a part of bug 3637854
1275 -----------------------------------------------------------------------------
1276 Procedure Change_Item_LC_Dependecies (
1277 p_api_version IN NUMBER
1278 ,p_inventory_item_id IN NUMBER
1279 ,p_organization_id IN NUMBER
1280 ,p_item_revision_id IN NUMBER
1281 ,p_lifecycle_id IN NUMBER
1282 ,p_lifecycle_phase_id IN NUMBER
1283 ,p_lifecycle_changed IN VARCHAR2
1284 ,p_lifecycle_phase_changed IN VARCHAR2
1285 ,p_perform_sync_only IN VARCHAR2
1286 ,p_new_cc_in_hier IN BOOLEAN := FALSE --Bug: 4060185
1287 ,x_return_status OUT NOCOPY VARCHAR2
1288 ,x_msg_count OUT NOCOPY NUMBER
1289 ,x_msg_data OUT NOCOPY VARCHAR2
1290 ) IS
1291
1292 CURSOR c_check_item_proj_assocs (cp_item_id IN NUMBER
1293 ,cp_org_id IN NUMBER
1294 ) IS
1295 SELECT organization_id
1296 FROM ego_item_projects item_proj
1297 WHERE inventory_item_id = cp_item_id
1298 AND EXISTS
1299 (SELECT P2.ORGANIZATION_ID
1300 FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
1301 WHERE P1.ORGANIZATION_ID = cp_org_id
1302 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
1303 AND P2.ORGANIZATION_ID = item_proj.organization_id
1304 )
1305 -- AND revision IS NULL
1306 AND revision_id IS NULL
1307 AND association_type = 'EGO_ITEM_PROJ_ASSOC_TYPE'
1308 AND association_code = 'LIFECYCLE_TRACKING';
1309
1310 CURSOR c_check_rev_proj_assocs (cp_item_id IN NUMBER
1314 SELECT organization_id
1311 ,cp_org_id IN NUMBER
1312 ,cp_rev_id IN NUMBER
1313 ) IS
1315 FROM ego_item_projects rev_proj
1316 WHERE inventory_item_id = cp_item_id
1317 AND EXISTS
1318 (SELECT P2.ORGANIZATION_ID
1319 FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
1320 WHERE P1.ORGANIZATION_ID = cp_org_id
1321 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
1322 AND P2.ORGANIZATION_ID = rev_proj.organization_id
1323 )
1324 -- AND EXISTS
1325 -- (SELECT 'X'
1326 -- FROM mtl_item_revisions_b
1327 -- WHERE revision_id = cp_rev_id
1328 -- AND revision = rev_proj.revision
1329 -- )
1330 AND revision_id = cp_rev_id
1331 AND association_type = 'EGO_ITEM_PROJ_ASSOC_TYPE'
1332 AND association_code = 'LIFECYCLE_TRACKING';
1333
1334 CURSOR c_pending_phase_change (cp_item_id IN NUMBER
1335 ,cp_org_id IN NUMBER
1336 ,cp_rev_id IN NUMBER
1337 ) IS
1338 SELECT organization_id
1339 FROM mtl_pending_item_status mpis
1340 WHERE inventory_item_id = cp_item_id
1341 AND EXISTS
1342 (SELECT P2.ORGANIZATION_ID
1343 FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
1344 WHERE P1.ORGANIZATION_ID = cp_org_id
1345 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
1346 AND P2.ORGANIZATION_ID = mpis.organization_id
1347 )
1348 AND nvl(revision_id, -1) = nvl(cp_rev_id, nvl(revision_id,-1))
1349 AND pending_flag = 'Y'
1350 AND implemented_date IS NULL
1351 AND phase_id IS NOT NULL;
1352
1353 l_organization_id NUMBER;
1354 l_api_version NUMBER;
1355 l_api_name VARCHAR2(30);
1356
1357 BEGIN
1358 l_api_name := 'Change_Item_LC_Dependecies';
1359 l_api_version := 1.0;
1360 code_debug(l_api_name||': Started ');
1361 code_debug(l_api_name||': p_inventory_item_id: '||to_char(p_inventory_item_id)||' p_organization_id: '||to_char(p_organization_id));
1362 code_debug(l_api_name||': p_item_revision_id: '||to_char(p_item_revision_id));
1363 code_debug(l_api_name||': p_lifecycle_id: '||to_char(p_lifecycle_id)||' p_lifecycle_phase_id: '||to_char(p_lifecycle_phase_id));
1364 code_debug(l_api_name||': p_lifecycle_changed: '||p_lifecycle_changed||' p_lifecycle_phase_changed: '||p_lifecycle_phase_changed);
1365
1366 --Standard checks
1367 IF NOT FND_API.Compatible_API_Call (l_api_version
1368 ,p_api_version
1369 ,l_api_name
1370 ,g_pkg_name)
1371 THEN
1372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1373 END IF;
1374
1375 IF NOT( FND_API.To_Boolean(p_lifecycle_changed)
1376 OR
1377 FND_API.To_Boolean(p_lifecycle_phase_changed)
1378 OR
1379 FND_API.To_Boolean(p_perform_sync_only)
1380 ) THEN
1381 RETURN;
1382 END IF;
1383 --
1384 -- inv team wish to call only Sync on certain conditions
1385 -- instead of calling another procedure, then wish to have SYNC
1386 -- exposed from here only
1387 --
1388 IF FND_API.To_Boolean(p_perform_sync_only) THEN
1389 code_debug(l_api_name||': calling Sync_Item_Revisions using perform sync only ');
1390 Sync_Item_Revisions(
1391 p_inventory_item_id => p_inventory_item_id
1392 ,p_organization_id => p_organization_id
1393 ,p_lifecycle_id => p_lifecycle_id
1394 ,p_lifecycle_phase_id => p_lifecycle_phase_id
1395 ,p_validate_changes => p_perform_sync_only
1396 ,p_new_cc_in_hier => p_new_cc_in_hier --Bug: 4060185
1397 ,x_return_status => x_return_status
1398 ,x_msg_count => x_msg_count
1399 ,x_msg_data => x_msg_data
1400 );
1401 code_debug(l_api_name||': returning Sync_Item_Revisions perform sync only '|| x_return_status);
1402 RETURN;
1403 END IF;
1404
1405 IF ( NOT (FND_API.to_boolean(p_lifecycle_changed))
1406 AND
1407 FND_API.to_boolean(p_lifecycle_phase_changed)
1408 ) THEN
1409 --
1410 -- check if there are any project associations
1411 --
1412 code_debug(l_api_name||': checking for any projet associations ');
1413 l_organization_id := NULL;
1414 IF p_item_revision_id IS NULL THEN
1415 OPEN c_check_item_proj_assocs (cp_item_id => p_inventory_item_id
1416 ,cp_org_id => p_organization_id
1417 );
1418 FETCH c_check_item_proj_assocs INTO l_organization_id;
1419 CLOSE c_check_item_proj_assocs;
1420 ELSE
1421 OPEN c_check_rev_proj_assocs (cp_item_id => p_inventory_item_id
1422 ,cp_org_id => p_organization_id
1423 ,cp_rev_id => p_item_revision_id
1424 );
1425 FETCH c_check_rev_proj_assocs INTO l_organization_id;
1426 CLOSE c_check_rev_proj_assocs;
1427 END IF; -- for item revision id found
1428 IF l_organization_id IS NOT NULL THEN
1429 --
1430 -- project association found
1431 --
1432 code_debug(l_api_name||': projet associations found ');
1433 Get_Error_msg( p_inventory_item_id => p_inventory_item_id
1437 ,x_return_status => x_return_status
1434 ,p_organization_id => l_organization_id
1435 ,p_item_revision_id => p_item_revision_id
1436 ,p_message_name => 'EGO_ITEM_LC_PROJ_EXISTS'
1438 ,x_msg_count => x_msg_count
1439 ,x_msg_data => x_msg_data
1440 );
1441 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1442 RETURN;
1443 END IF;
1444 END IF; -- project association found
1445 END IF; -- lifecycle phase only has changed.
1446
1447 code_debug(l_api_name||': calling Checking Pending Phase Changes ');
1448 --
1449 -- check if there are any pending phase changes
1450 --
1451 OPEN c_pending_phase_change (cp_item_id => p_inventory_item_id
1452 ,cp_org_id => p_organization_id
1453 ,cp_rev_id => p_item_revision_id
1454 );
1455 FETCH c_pending_phase_change INTO l_organization_id;
1456 IF c_pending_phase_change%FOUND THEN
1457 code_debug(l_api_name||': pending phase changes found ');
1458 CLOSE c_pending_phase_change;
1459 Get_Error_msg(p_inventory_item_id => p_inventory_item_id
1460 ,p_organization_id => l_organization_id
1461 ,p_item_revision_id => p_item_revision_id
1462 ,p_message_name => 'EGO_ITEM_PENDING_REC_EXISTS'
1463 ,x_return_status => x_return_status
1464 ,x_msg_count => x_msg_count
1465 ,x_msg_data => x_msg_data
1466 );
1467 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1468 RETURN;
1469 END IF;
1470 ELSE
1471 CLOSE c_pending_phase_change;
1472 END IF;
1473
1474 code_debug(l_api_name||': calling Check_Pending_Change_Orders');
1475 IF NOT p_new_cc_in_hier THEN --Bug: 4060185
1476 Check_Pending_Change_Orders(
1477 p_inventory_item_id => p_inventory_item_id
1478 ,p_organization_id => p_organization_id
1479 ,p_revision_id => p_item_revision_id
1480 ,p_lifecycle_changed => p_lifecycle_changed
1481 ,p_lifecycle_phase_changed => p_lifecycle_phase_changed
1482 ,p_change_id => NULL
1483 ,p_change_line_id => NULL
1484 ,x_return_status => x_return_status
1485 ,x_msg_count => x_msg_count
1486 ,x_msg_data => x_msg_data
1487 );
1488 END IF; --Bug: 4060185
1489 code_debug(l_api_name||': returning Check_Pending_Change_Orders with status '|| x_return_status);
1490 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1491 RETURN;
1492 END IF;
1493
1494 IF (FND_API.To_Boolean(p_lifecycle_changed) AND p_item_revision_id IS NULL) THEN
1495 -- check for pending changes and remove the associated projects
1496 code_debug(l_api_name||': calling Sync_Item_Revisions LC of item has changed ');
1497 Sync_Item_Revisions (
1498 p_inventory_item_id => p_inventory_item_id
1499 ,p_organization_id => p_organization_id
1500 ,p_lifecycle_id => p_lifecycle_id
1501 ,p_lifecycle_phase_id => p_lifecycle_phase_id
1502 ,p_validate_changes => p_perform_sync_only
1503 ,p_new_cc_in_hier => p_new_cc_in_hier --Bug: 4060185
1504 ,x_return_status => x_return_status
1505 ,x_msg_count => x_msg_count
1506 ,x_msg_data => x_msg_data
1507 );
1508 code_debug(l_api_name||': returning Sync_Item_Revisions with status '|| x_return_status);
1509 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1510 RETURN;
1511 ELSE
1512 -- everything is fine
1513 -- delete the projects associated with the item.
1514 DELETE EGO_ITEM_PROJECTS proj
1515 WHERE inventory_item_id = p_inventory_item_id
1516 -- AND revision IS NULL
1517 AND revision_id IS NULL
1518 AND EXISTS
1519 (SELECT 'X'
1520 FROM mtl_parameters p1, mtl_parameters p2
1521 WHERE p1.organization_id = p_organization_id
1522 AND p1.master_organization_id = p2.master_organization_id
1523 AND p2.organization_id = proj.organization_id
1524 )
1525 AND association_type = 'EGO_ITEM_PROJ_ASSOC_TYPE'
1526 AND association_code = 'LIFECYCLE_TRACKING' ;
1527 END IF;
1528 END IF; -- lifecycle has changed
1529
1530 x_return_status := FND_API.G_RET_STS_SUCCESS;
1531 code_debug(l_api_name||': Bye Bye ');
1532
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 IF c_check_item_proj_assocs%ISOPEN THEN
1536 CLOSE c_check_item_proj_assocs;
1537 END IF;
1538 IF c_check_rev_proj_assocs%ISOPEN THEN
1539 CLOSE c_check_rev_proj_assocs;
1540 END IF;
1541 IF c_pending_phase_change%ISOPEN THEN
1542 CLOSE c_pending_phase_change;
1543 END IF;
1544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1545 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
1546 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
1547 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, 'CHANGE_ITEM_LC_DEPENDENCIES');
1548 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
1552 END Change_Item_LC_Dependecies;
1549 x_msg_count := 1;
1550 x_msg_data := FND_MESSAGE.GET;
1551 code_debug(' Exception in '||l_api_name ||': '||x_msg_data );
1553
1554
1555 ------------------------------------------------------------------------------
1556 -- Start OF comments
1557 -- API name : Create_phase_History_Record
1558 -- TYPE : Public
1559 -- Pre-reqs : None
1560 -- PROCEDURE : To check if there are any pending change orders
1561 -- Remarks : Created as a part of bug 3637854
1562 -----------------------------------------------------------------------------
1563
1564 PROCEDURE Create_phase_History_Record (
1565 p_api_version IN NUMBER
1566 ,p_commit IN VARCHAR2
1567 ,p_inventory_item_id IN NUMBER
1568 ,p_organization_id IN NUMBER
1569 ,p_revision_id IN NUMBER
1570 ,p_lifecycle_id IN VARCHAR2
1571 ,p_lifecycle_phase_id IN VARCHAR2
1572 ,p_item_status_code IN VARCHAR2
1573 ,x_return_status OUT NOCOPY VARCHAR2
1574 ,x_msg_count OUT NOCOPY NUMBER
1575 ,x_msg_data OUT NOCOPY VARCHAR2
1576 ) IS
1577
1578 l_user_id NUMBER;
1579 l_sysdate DATE;
1580 l_api_version NUMBER;
1581 l_api_name VARCHAR2(30);
1582
1583 BEGIN
1584 l_api_version := 1.0;
1585 l_api_name := 'Create_Phase_History_Record';
1586 --Standard checks
1587 code_debug(l_api_name||': Started ');
1588 IF NOT FND_API.Compatible_API_Call (l_api_version
1589 ,p_api_version
1590 ,l_api_name
1591 ,g_pkg_name)
1592 THEN
1593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1594 END IF;
1595
1596 IF FND_API.To_Boolean( p_commit ) THEN
1597 SAVEPOINT Create_Phase_History_SP;
1598 END IF;
1599
1600 l_user_id := FND_GLOBAL.User_Id;
1601 l_sysdate := SYSDATE;
1602 INSERT INTO mtl_pending_item_status(
1603 inventory_item_id,
1604 organization_id,
1605 status_code,
1606 revision_id,
1607 effective_date,
1608 implemented_date,
1609 pending_flag,
1610 last_update_date,
1611 last_updated_by,
1612 creation_date,
1613 created_by,
1614 lifecycle_id,
1615 phase_id)
1616 VALUES(
1617 p_inventory_item_id,
1618 p_organization_id,
1619 p_item_status_code,
1620 p_revision_id,
1621 l_sysdate,
1622 l_sysdate,
1623 'N',
1624 l_sysdate,
1625 l_user_id,
1626 l_sysdate,
1627 l_user_id,
1628 p_lifecycle_id,
1629 p_lifecycle_phase_id);
1630 IF FND_API.To_Boolean(p_commit) THEN
1631 COMMIT WORK;
1632 END IF;
1633 x_return_status := FND_API.G_RET_STS_SUCCESS;
1634 code_debug(l_api_name||': Bye Bye ');
1635 EXCEPTION
1636 WHEN OTHERS THEN
1637 IF FND_API.To_Boolean( p_commit ) THEN
1638 ROLLBACK TO Create_Phase_History_SP;
1639 END IF;
1640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1641 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
1642 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
1643 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, 'CREATE_PHASE_HISTORY_RECORD');
1644 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
1645 x_msg_count := 1;
1646 x_msg_data := FND_MESSAGE.GET;
1647 END Create_phase_History_Record;
1648
1649 ------------------------------------------------------------------------------
1650 -- Start OF comments
1651 -- API name : Check_pending_Change_Orders
1652 -- TYPE : Public
1653 -- Pre-reqs : None
1654 -- PROCEDURE : To check if there are any pending change orders
1655 -- Remarks : Created as a part of bug 3637854
1656 -----------------------------------------------------------------------------
1657
1658 PROCEDURE Check_pending_Change_Orders (
1659 p_inventory_item_id IN NUMBER
1660 ,p_organization_id IN NUMBER
1661 ,p_revision_id IN NUMBER
1662 ,p_lifecycle_changed IN VARCHAR2
1663 ,p_lifecycle_phase_changed IN VARCHAR2
1664 ,p_change_id IN NUMBER
1665 ,p_change_line_id IN NUMBER
1666 ,x_return_status OUT NOCOPY VARCHAR2
1667 ,x_msg_count OUT NOCOPY NUMBER
1668 ,x_msg_data OUT NOCOPY VARCHAR2
1669 ) IS
1670
1671 CURSOR c_get_null_revisions (cp_item_id IN NUMBER
1672 ,cp_org_id IN NUMBER) IS
1673 SELECT inventory_item_id, organization_id, revision_id
1674 FROM mtl_item_revisions_b item_rev
1675 WHERE item_rev.inventory_item_id = cp_item_id
1676 AND EXISTS
1677 (SELECT 'X'
1678 FROM mtl_parameters p1, mtl_parameters p2
1679 WHERE p1.organization_id = cp_org_id
1680 AND p1.master_organization_id = p2.master_organization_id
1681 AND p2.organization_id = item_rev.organization_id
1682 )
1683 AND item_rev.lifecycle_id IS NULL
1684 AND item_rev.current_phase_id IS NULL;
1685
1689 l_api_name VARCHAR2(100);
1686 l_organization_id NUMBER;
1687 l_revision_id NUMBER;
1688 l_fetch_error_message BOOLEAN;
1690 l_change_notice VARCHAR2(100);
1691 l_change_line_id NUMBER;
1692 l_dynamic_sql VARCHAR2(32767);
1693 l_dyn_sql_pend_chg_rev VARCHAR2(32767);
1694 TYPE DYNAMIC_CUR IS REF CURSOR;
1695 l_dynamic_cursor DYNAMIC_CUR;
1696
1697 BEGIN
1698 l_api_name := 'Check_Pending_Change_Orders';
1699 l_fetch_error_message := FALSE;
1700 code_debug(l_api_name||':start ');
1701 code_debug(l_api_name||': p_inventory_item_id: '||to_char(p_inventory_item_id)||' p_organization_id: '||to_char(p_organization_id));
1702 code_debug(l_api_name||': p_revision_id: '||to_char(p_revision_id));
1703 code_debug(l_api_name||': p_lifecycle_changed: '||p_lifecycle_changed||' p_lifecycle_phase_changed: '||p_lifecycle_phase_changed);
1704
1705 IF p_change_id IS NULL THEN
1706 IF p_change_line_id IS NOT NULL THEN
1707 --
1708 -- this should never occur
1709 --
1710 FND_MESSAGE.Set_Name(G_APP_NAME, 'EGO_PKG_MAND_VALUES_MISS');
1711 FND_MESSAGE.Set_Token('PACKAGE', G_PKG_NAME||l_api_name);
1712 FND_MESSAGE.Set_Token('VALUE1', 'CHECK_ID');
1713 FND_MESSAGE.Set_Token('VALUE2', 'CHANGE_LINE_ID');
1714 x_msg_count := 1;
1715 x_msg_data := FND_MESSAGE.GET;
1716 x_return_status := FND_API.G_RET_STS_ERROR;
1717 RETURN;
1718 END IF;
1719 l_change_notice := FND_API.G_MISS_CHAR;
1720 ELSE
1721 -- 3878336 replacing the existing cursor with dynamic query
1722 l_dynamic_sql := 'SELECT change_notice FROM eng_engineering_changes WHERE change_id = :1' ;
1723 BEGIN
1724 OPEN l_dynamic_cursor FOR l_dynamic_sql USING p_change_id;
1725 FETCH l_dynamic_cursor INTO l_change_notice;
1726 CLOSE l_dynamic_cursor;
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 code_debug(l_api_name||': error1: ' || SQLERRM);
1730 if (l_dynamic_cursor%ISOPEN) then
1731 CLOSE l_dynamic_cursor;
1732 END IF;
1733 END;
1734
1735 END IF;
1736
1737 l_change_line_id := NVL(p_change_line_id,FND_API.G_MISS_NUM);
1738 l_revision_id := p_revision_id;
1739 code_debug(l_api_name||': Change context passed -- change notice: '||l_change_notice ||' change line: '||l_change_line_id);
1740
1741 -- 3878336 replacing the existing cursor with dynamic query
1742 l_dyn_sql_pend_chg_rev := ' SELECT organization_id FROM eng_revised_items change ' ||
1743 ' WHERE change.revised_item_id = :1 ' ||
1744 ' AND change.organization_id = :2 ' ||
1745 ' AND change.change_notice <> :3 ' ||
1746 ' AND change.revised_item_sequence_id <> :4 ' ||
1747 ' AND change.current_item_revision_id = :5 ' ||
1748 ' AND change.status_type NOT IN (5, 6) ' ||
1749 ' AND ' ||
1750 ' ( change.NEW_ITEM_REVISION_ID IS NOT null ' || --this CO creates a revision
1751 ' OR EXISTS ' ||
1752 -- 4177523 DM changes through bug 4045666
1753 -- from CM side to store the pending doc changes in eng_attachment_changes
1754 -- ' (SELECT ''X'' FROM eng_attachment_changes ENG, fnd_attached_documents doc ' ||
1755 -- ' WHERE eng.revised_item_sequence_id = change.revised_item_sequence_id ' ||
1756 -- ' AND eng.attachment_id = doc.attached_document_id ' ||
1757 -- ' AND doc.entity_name = ''MTL_ITEM_REVISIONS'' AND doc.pk1_value = to_char(change.organization_id) ' ||
1758 -- ' AND doc.pk2_value = to_char(:6) AND doc.pk3_value = to_char(:7)) ' ||
1759 ' (SELECT ''X'' FROM eng_attachment_changes ENG ' ||
1760 ' WHERE eng.revised_item_sequence_id = change.revised_item_sequence_id ' ||
1761 ' AND eng.entity_name = ''MTL_ITEM_REVISIONS'''||
1762 ' AND eng.pk1_value = to_char(change.organization_id) ' ||
1763 ' AND eng.pk2_value = to_char(:6) '||
1764 ' AND eng.pk3_value = to_char(:7)) ' ||
1765 -- 4177523 DM changes through bug 4045666 added condition for structure changes
1766 ' OR EXISTS ' ||
1767 ' (SELECT ''X'' FROM bom_components_b bom_comp '||
1768 ' WHERE bom_comp.revised_item_sequence_id = change.revised_item_sequence_id '||
1769 ' AND bom_comp.bill_sequence_id = change.bill_sequence_id '||
1770 ' AND bom_comp.obj_name IS NULL '||
1771 ' AND bom_comp.implementation_date IS NULL) '||
1772 ' OR EXISTS ' ||
1773 ' (SELECT ''X'' FROM ego_items_attrs_changes_b attr_chg, ego_obj_ag_assocs_b assoc, fnd_objects obj ' ||
1774 ' WHERE attr_chg.change_line_id = change.revised_item_sequence_id ' ||
1775 -- 3710038 check for the attributes in the hierarchy
1776 -- AND to_char(attr_chg.item_catalog_group_id) = assoc.classification_code
1777 ' AND assoc.classification_code IN ( ' ||
1778 ' SELECT TO_CHAR(item_catalog_group_id) FROM mtl_item_catalog_groups_b ' ||
1779 ' CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id ' ||
1783 ' AND assoc.object_id = obj.object_id AND obj.obj_name = ''EGO_ITEM'') ' ||
1780 ' START WITH item_catalog_group_id = attr_chg.item_catalog_group_id ) ' ||
1781 ' AND attr_chg.attr_group_id = assoc.attr_group_id ' ||
1782 ' AND assoc.data_level = ''ITEM_REVISION_LEVEL'' AND assoc.enabled_flag = ''Y''' ||
1784 ' OR EXISTS ' || --this CO has Related Doc Change
1785 ' (SELECT NULL ' ||
1786 ' FROM eng_relationship_changes ' ||
1787 ' WHERE ENTITY_ID = change.revised_item_sequence_id ' ||
1788 ' AND change_id = change.change_id ' ||
1789 ' AND ENTITY_NAME=''ITEM'' ' ||
1790 ' AND FROM_ENTITY_NAME = ''EGO_ITEM_REVISION'' ) )';
1791
1792 IF l_revision_id IS NOT NULL THEN
1793 IF FND_API.TO_BOOLEAN(p_lifecycle_phase_changed) THEN
1794 code_debug(l_api_name||': Validating only revision changes ');
1795 BEGIN
1796 code_debug(l_api_name||': executing l_dyn_sql_pend_chg_rev ');
1797 code_debug(l_api_name||': executing p_inventory_item_id ' || p_inventory_item_id);
1798 code_debug(l_api_name||': executing p_organization_id ' || p_organization_id);
1799 code_debug(l_api_name||': executing l_change_notice ' || l_change_notice);
1800 code_debug(l_api_name||': executing l_change_line_id ' || l_change_line_id);
1801 code_debug(l_api_name||': executing l_revision_id ' || l_revision_id);
1802 OPEN l_dynamic_cursor FOR l_dyn_sql_pend_chg_rev USING p_inventory_item_id,
1803 p_organization_id,
1804 l_change_notice,
1805 l_change_line_id,
1806 l_revision_id,
1807 p_inventory_item_id,
1808 l_revision_id;
1809 LOOP
1810 FETCH l_dynamic_cursor INTO l_organization_id;
1811 EXIT WHEN l_dynamic_cursor%NOTFOUND;
1812 IF l_dynamic_cursor%FOUND THEN
1813 code_debug(l_api_name||': Validating only revision changes FOUND ');
1814 l_fetch_error_message := TRUE;
1815 ELSE
1816 code_debug(l_api_name||': Validating only revision changes NOT FOUND ');
1817 END IF;
1818 END LOOP;
1819 CLOSE l_dynamic_cursor;
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 code_debug(l_api_name||': error2: ' || SQLERRM);
1823 if (l_dynamic_cursor%ISOPEN) then
1824 CLOSE l_dynamic_cursor;
1825 END IF;
1826 END;
1827 ELSE
1828 -- you cannot send revision and say lifecycle phase changed
1829 -- lifecycle change is implemented through the item and not revision.
1830 code_debug(l_api_name||': Returning as you cannot change lifecycle of item revision ');
1831 RETURN;
1832 END IF;
1833 ELSE
1834
1835 -- changes are at item level
1836 IF FND_API.TO_BOOLEAN(p_lifecycle_changed) THEN
1837 -- changing item lc
1841 'FROM eng_revised_items change ' ||
1838 code_debug(l_api_name||': Validating all item changes ');
1839 -- 3878336 replacing the existing cursor with dynamic query
1840 l_dynamic_sql := 'SELECT organization_id ' ||
1842 'WHERE revised_item_id = :1' ||
1843 'AND change_notice <> :2'||
1844 'AND revised_item_sequence_id <> :3' ||
1845 'AND EXISTS ' ||
1846 ' (SELECT ''X'' FROM mtl_parameters p1, mtl_parameters p2 ' ||
1847 ' WHERE p1.organization_id = :4 ' ||
1848 ' AND p1.master_organization_id = p2.master_organization_id ' ||
1849 ' AND p2.organization_id = change.organization_id )' ||
1850 ' AND status_type NOT IN (5,6) ' ||
1851 ' AND ' ||
1852 ' ( EXISTS ' ||
1853 ' (SELECT ''X'' FROM ego_mfg_part_num_chgs ' ||
1854 ' WHERE change_line_id = change.revised_item_sequence_id ) ' ||
1855 ' OR EXISTS ' ||
1856 ' (SELECT ''X'' FROM eng_attachment_changes ' ||
1857 ' WHERE revised_item_sequence_id = change.revised_item_sequence_id) ' ||
1858 ' OR EXISTS ' ||
1859 ' (SELECT ''X'' FROM ego_items_attrs_changes_b attr_chg ' ||
1860 ' WHERE change_line_id = change.revised_item_sequence_id ) ' ||
1861 ' OR EXISTS ' || --this CO has Operational Attribute Change
1862 ' (SELECT NULL ' ||
1863 ' FROM ego_mtl_sy_items_chg_b ' ||
1864 ' WHERE change_line_id = change.revised_item_sequence_id ' ||
1865 ' AND change_id = change.change_id) ' ||
1866 ' OR EXISTS ' || --this CO has GTIN Single Change
1867 ' (SELECT NULL ' ||
1868 ' FROM ego_gtn_attr_chg_b ' ||
1869 ' WHERE change_line_id = change.revised_item_sequence_id ' ||
1870 ' AND change_id = change.change_id) ' ||
1871 ' OR EXISTS ' || --this CO has GTIN Multi Change
1872 ' (SELECT NULL ' ||
1873 ' FROM ego_gtn_mul_attr_chg_b ' ||
1874 ' WHERE change_line_id = change.revised_item_sequence_id ' ||
1875 ' AND change_id = change.change_id) ' ||
1876 ' OR EXISTS ' || --this CO has Related Doc Change
1877 ' (SELECT NULL ' ||
1878 ' FROM eng_relationship_changes ' ||
1879 ' WHERE ENTITY_ID = change.revised_item_sequence_id ' ||
1880 ' AND change_id = change.change_id ' ||
1881 ' AND ENTITY_NAME=''ITEM'') ' ||
1882 ' OR EXISTS ' || --this CO has Structure Changes
1883 ' (SELECT NULL ' ||
1884 ' FROM bom_components_b ' ||
1885 ' WHERE revised_item_sequence_id = change.revised_item_sequence_id) ) ';
1886
1887 BEGIN
1888 code_debug(l_api_name||': Executing l_dynamic_sql 1 ');
1889 code_debug(l_api_name||': executing p_inventory_item_id ' || p_inventory_item_id);
1890 code_debug(l_api_name||': executing l_change_notice ' || l_change_notice);
1891 code_debug(l_api_name||': executing l_change_line_id ' || l_change_line_id);
1892 code_debug(l_api_name||': executing p_organization_id ' || p_organization_id);
1893 OPEN l_dynamic_cursor FOR l_dynamic_sql USING p_inventory_item_id, l_change_notice, l_change_line_id, p_organization_id;
1894 LOOP
1895 FETCH l_dynamic_cursor INTO l_organization_id;
1896 EXIT WHEN l_dynamic_cursor%NOTFOUND;
1897 IF l_dynamic_cursor%FOUND THEN
1898 code_debug(l_api_name||': Validating all item changes FOUND ');
1899 l_fetch_error_message := TRUE;
1900 ELSE
1901 code_debug(l_api_name||': Validating all item changes NOT FOUND ');
1902 END IF;
1903 END LOOP;
1904 CLOSE l_dynamic_cursor;
1905 EXCEPTION
1906 WHEN OTHERS THEN
1907 code_debug(l_api_name||': error3: ' || SQLERRM);
1908 if (l_dynamic_cursor%ISOPEN) then
1909 CLOSE l_dynamic_cursor;
1910 end if;
1911 END;
1912 ELSIF FND_API.TO_BOOLEAN(p_lifecycle_phase_changed) THEN
1913 -- check if there are any pending change
1914 -- associated with the item revision that is NULL
1915 code_debug(l_api_name||': Validating only item phase changes ');
1916 -- 3878336 replacing the existing cursor with dynamic query
1917 l_dynamic_sql := ' SELECT organization_id FROM eng_revised_items change ' ||
1918 ' WHERE change.revised_item_id = :1 ' ||
1919 ' AND change.change_notice <> :2 ' ||
1920 ' AND change.revised_item_sequence_id <> :3 ' ||
1921 ' AND EXISTS ' ||
1925 ' AND p2.organization_id = change.organization_id ' ||
1922 ' (SELECT ''X'' FROM mtl_parameters p1, mtl_parameters p2 ' ||
1923 ' WHERE p1.organization_id = :4 ' ||
1924 ' AND p1.master_organization_id = p2.master_organization_id ' ||
1926 ' ) ' ||
1927 ' AND change.status_type NOT IN (5, 6 ) ' ||
1928 ' AND ( ' ||
1929 ' EXISTS (SELECT ''X'' FROM ego_mfg_part_num_chgs ' ||
1930 ' WHERE change_line_id = change.revised_item_sequence_id ' ||
1931 ' ) OR EXISTS ' ||
1932 -- 4177523 DM changes through bug 4045666
1933 -- from CM side to store the pending doc changes in eng_attachment_changes
1934 -- ' (SELECT ''X'' FROM eng_attachment_changes ENG, fnd_attached_documents doc ' ||
1935 -- ' WHERE eng.revised_item_sequence_id = change.revised_item_sequence_id ' ||
1936 -- ' AND eng.attachment_id = doc.attached_document_id ' ||
1937 -- ' AND doc.entity_name = ''MTL_SYSTEM_ITEMS'' ' ||
1938 -- ' AND doc.pk1_value = to_char(change.organization_id) ' ||
1939 -- ' AND doc.pk2_value = to_char(:5) ' ||
1940 ' (SELECT ''X'' FROM eng_attachment_changes ENG ' ||
1941 ' WHERE eng.revised_item_sequence_id = change.revised_item_sequence_id ' ||
1942 ' AND eng.entity_name = ''MTL_SYSTEM_ITEMS'' ' ||
1943 ' AND eng.pk1_value = to_char(change.organization_id) ' ||
1944 ' AND eng.pk2_value = to_char(:5) ' ||
1945 ' ) OR EXISTS ' ||
1946 ' (SELECT ''X'' FROM ego_items_attrs_changes_b attr_chg, ego_obj_ag_assocs_b assoc, fnd_objects obj ' ||
1947 ' WHERE attr_chg.change_line_id = change.revised_item_sequence_id ' ||
1948 -- 3710038 check for the attributes in the hierarchy
1949 -- AND to_char(attr_chg.item_catalog_group_id) = assoc.classification_code
1950 ' AND assoc.classification_code IN (' ||
1951 ' SELECT TO_CHAR(item_catalog_group_id) FROM mtl_item_catalog_groups_b ' ||
1952 ' CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id ' ||
1953 ' START WITH item_catalog_group_id = attr_chg.item_catalog_group_id ) ' ||
1954 ' AND attr_chg.attr_group_id = assoc.attr_group_id ' ||
1955 ' AND assoc.data_level = ''ITEM_LEVEL'' AND assoc.object_id = obj.object_id ' ||
1956 ' AND obj.obj_name = ''EGO_ITEM'' AND assoc.enabled_flag = ''Y'' ) ' ||
1957 ' OR EXISTS ' || --this CO has Operational Attribute Change
1958 ' (SELECT NULL ' ||
1959 ' FROM ego_mtl_sy_items_chg_b ' ||
1960 ' WHERE change_line_id = change.revised_item_sequence_id ' ||
1961 ' AND change_id = change.change_id) ' ||
1962 ' OR EXISTS ' || --this CO has GTIN Single Change
1963 ' (SELECT NULL ' ||
1964 ' FROM ego_gtn_attr_chg_b ' ||
1965 ' WHERE change_line_id = change.revised_item_sequence_id ' ||
1966 ' AND change_id = change.change_id) ' ||
1967 ' OR EXISTS ' || --this CO has GTIN Multi Change
1968 ' (SELECT NULL ' ||
1969 ' FROM ego_gtn_mul_attr_chg_b ' ||
1970 ' WHERE change_line_id = change.revised_item_sequence_id ' ||
1971 ' AND change_id = change.change_id) ' ||
1972 ' OR EXISTS ' || --this CO has Related Doc Change
1973 ' (SELECT NULL ' ||
1974 ' FROM eng_relationship_changes ' ||
1975 ' WHERE ENTITY_ID = change.revised_item_sequence_id ' ||
1976 ' AND change_id = change.change_id ' ||
1977 ' AND ENTITY_NAME=''ITEM'') ' ||
1978 ' OR EXISTS ' || --this CO has Structure Changes
1979 ' (SELECT NULL ' ||
1980 ' FROM bom_components_b ' ||
1981 ' WHERE revised_item_sequence_id = change.revised_item_sequence_id) ) ';
1982
1983 BEGIN
1984 code_debug(l_api_name||': Executing l_dynamic_sql 2: ' || l_dynamic_sql);
1985 code_debug(l_api_name||': executing p_inventory_item_id ' || p_inventory_item_id);
1986 code_debug(l_api_name||': executing l_change_notice ' || l_change_notice);
1987 code_debug(l_api_name||': executing l_change_line_id ' || l_change_line_id);
1988 code_debug(l_api_name||': executing p_organization_id ' || p_organization_id);
1989 OPEN l_dynamic_cursor FOR l_dynamic_sql USING p_inventory_item_id,
1990 l_change_notice,
1991 l_change_line_id,
1995 code_debug(l_api_name||': executed l_dynamic_sql 2 ');
1992 p_organization_id,
1993 p_inventory_item_id;
1994 FETCH l_dynamic_cursor INTO l_organization_id;
1996 IF l_dynamic_cursor%FOUND THEN
1997 code_debug(l_api_name||': Validating only item phase changes FOUND ');
1998 l_fetch_error_message := TRUE;
1999 ELSE
2000 code_debug(l_api_name||': Validating only item phase changes NOT FOUND ');
2001 CLOSE l_dynamic_cursor;
2002 -- check if there are any item revision level pending phase changes
2003 FOR cr IN c_get_null_revisions(cp_item_id => p_inventory_item_id
2004 ,cp_org_id => p_organization_id) LOOP
2005 code_debug(l_api_name||': Validating NULL revision changes for organization_id '||cr.organization_id);
2006 code_debug(l_api_name||': Validating NULL revision changes for revision '||cr.revision_id);
2007 BEGIN
2008 OPEN l_dynamic_cursor FOR l_dyn_sql_pend_chg_rev USING p_inventory_item_id,
2009 p_organization_id,
2010 l_change_notice,
2011 l_change_line_id,
2012 l_revision_id,
2013 p_inventory_item_id,
2014 l_revision_id;
2015 FETCH l_dynamic_cursor INTO l_organization_id;
2016 IF l_dynamic_cursor%FOUND THEN
2017 code_debug(l_api_name||': Validating NULL revision changes FOUND for revision '||cr.revision_id);
2018 l_fetch_error_message := TRUE;
2019 l_revision_id := cr.revision_id;
2020 CLOSE l_dynamic_cursor;
2021 EXIT;
2022 ELSE
2023 code_debug(l_api_name||': Validating NULL revision changes NOT FOUND for revision '||cr.revision_id);
2024 CLOSE l_dynamic_cursor;
2025 END IF;
2026 EXCEPTION
2027 WHEN OTHERS THEN
2028 code_debug(l_api_name||': error4: ' || SQLERRM);
2029 if (l_dynamic_cursor%ISOPEN) then
2030 CLOSE l_dynamic_cursor;
2031 END IF;
2032 END;
2033 END LOOP; -- revision null check.
2034 END IF; -- pending changes found at item
2035 EXCEPTION
2036 WHEN OTHERS THEN
2037 code_debug(l_api_name||': error5: ' || SQLERRM);
2038 if (l_dynamic_cursor%ISOPEN) then
2039 CLOSE l_dynamic_cursor;
2040 END IF;
2041 END;
2042 ELSE
2043 RETURN;
2044 END IF; -- changes at item level
2045 END IF; -- revision exists or not
2046
2047 code_debug(l_api_name||': All validations complete ');
2048 IF l_fetch_error_message THEN
2049 -- flashing message at item level only.
2050 code_debug(l_api_name||': Flash error message for pending changes exist ');
2051 Get_Error_Msg(p_inventory_item_id => p_inventory_item_id
2052 ,p_organization_id => l_organization_id
2053 ,p_item_revision_id => l_revision_id
2054 ,p_message_name => 'EGO_ITEM_PENDING_CHANGES_EXIST'
2055 ,x_return_status => x_return_status
2056 ,x_msg_count => x_msg_count
2057 ,x_msg_data => x_msg_data
2058 );
2059 ELSE
2060 code_debug(l_api_name||': No Pending Changes ');
2061 x_return_status := FND_API.G_RET_STS_SUCCESS;
2062 x_msg_count := 0;
2063 x_msg_data := NULL;
2064 END IF;
2065 code_debug(l_api_name||': Bye Bye ');
2066 EXCEPTION
2067 WHEN OTHERS THEN
2068 code_debug(l_api_name||': error6: ' || SQLERRM);
2069 IF l_dynamic_cursor%ISOPEN THEN
2070 CLOSE l_dynamic_cursor;
2071 END IF;
2072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2073 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
2074 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
2075 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, 'CHECK_PENDING_CHANGE_ORDERS');
2076 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
2077 x_msg_count := 1;
2078 x_msg_data := FND_MESSAGE.GET;
2079 END Check_pending_Change_Orders;
2080
2081
2082 END EGO_INV_ITEM_CATALOG_PVT;