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