[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_LC_IMP_PC_PUB
Source
1 PACKAGE BODY EGO_ITEM_LC_IMP_PC_PUB AS
2 /* $Header: EGOCIPSB.pls 120.4.12000000.2 2007/03/27 10:45:34 syalaman ship $ */
3
4 G_SUCCESS CONSTANT NUMBER := 0;
5 G_WARNING CONSTANT NUMBER := 1;
6 G_ERROR CONSTANT NUMBER := 2;
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_LC_IMP_PC_PUB';
9 G_APP_NAME CONSTANT VARCHAR2(3) := 'EGO';
10 G_PKG_NAME_TOKEN CONSTANT VARCHAR2(8) := 'PKG_NAME';
11 G_API_NAME_TOKEN CONSTANT VARCHAR2(8) := 'API_NAME';
12 G_SQL_ERR_MSG_TOKEN CONSTANT VARCHAR2(11) := 'SQL_ERR_MSG';
13 G_PLSQL_ERR CONSTANT VARCHAR2(17) := 'EGO_PLSQL_ERR';
14
15 G_EGO_ITEM CONSTANT VARCHAR2(20) := 'EGO_ITEM';
16 G_CURRENT_USER_ID NUMBER := FND_GLOBAL.User_Id;
17 G_CURRENT_LOGIN_ID NUMBER := FND_GLOBAL.Login_Id;
18
19
20 ----------------------------------------------------------------------
21 -- Private Procedures / Functions
22 ----------------------------------------------------------------------
23
24 -- Developer debugging
25 PROCEDURE code_debug (p_msg IN VARCHAR2) IS
26 BEGIN
27 --sri_debug (' EGOCIPSB - EGO_ITEM_LC_IMP_PC_PUB.'||p_msg);
28 RETURN;
29 EXCEPTION
30 WHEN OTHERS THEN
31 NULL;
32 END;
33
34
35 /***
36 -------------------------------------------------------
37 -- fix as a part of bug 3696801
38 -- this is no more used. replaced by
39 -- EGO_INV_ITEM_CATALOG_PVT.check_pending_change_orders
40 -------------------------------------------------------
41 PROCEDURE Check_Pending_Change_Orders
42 (
43 p_inventory_item_id IN NUMBER
44 ,p_organization_id IN NUMBER
45 ,p_revision_id IN NUMBER
46 ,x_return_status OUT NOCOPY VARCHAR2
47 ,x_msg_data OUT NOCOPY VARCHAR2
48 )
49 IS
50
51 CURSOR c_pending_changes (cp_item_id IN NUMBER
52 ,cp_org_id IN NUMBER
53 ,cp_rev_id IN NUMBER
54 ) IS
55 SELECT change.organization_id
56 FROM eng_revised_items change
57 WHERE change.revised_item_id = cp_item_id
58 AND change.organization_id = cp_org_id
59 AND nvl(change.current_item_revision_id, -1) = nvl(cp_rev_id, nvl(change.current_item_revision_id,-1))
60 AND change.status_type NOT IN (5, -- CANCELLED
61 6 -- IMPLEMENTED
62 )
63 AND
64 ( EXISTS
65 (SELECT 'X'
66 FROM ego_mfg_part_num_chgs
67 WHERE change_line_id = change.revised_item_sequence_id )
68 OR EXISTS
69 (SELECT 'X'
70 FROM ego_items_attrs_changes_vl
71 WHERE change_line_id = change.revised_item_sequence_id )
72 OR EXISTS
73 (SELECT 'X'
74 FROM eng_attachment_changes
75 WHERE revised_item_sequence_id = change.revised_item_sequence_id )
76 );
77
78 CURSOR c_pending_org_changes (cp_item_id IN NUMBER
79 ,cp_org_id IN NUMBER
80 ,cp_rev_id IN NUMBER
81 ) IS
82 SELECT organization_id
83 FROM eng_revised_items change
84 WHERE revised_item_id = cp_item_id
85 AND organization_id IN
86 (SELECT P2.ORGANIZATION_ID
87 FROM MTL_PARAMETERS P1,
88 MTL_PARAMETERS P2
89 WHERE P1.ORGANIZATION_ID = cp_org_id
90 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
91 )
92 AND nvl(current_item_revision_id, -1) = nvl(cp_rev_id, nvl(current_item_revision_id,-1))
93 AND status_type NOT IN (5, -- CANCELLED
94 6 -- IMPLEMENTED
95 )
96 AND
97 ( EXISTS
98 (SELECT 'X'
99 FROM ego_mfg_part_num_chgs
100 WHERE change_line_id = change.revised_item_sequence_id )
101 OR EXISTS
102 (SELECT 'X'
103 FROM ego_items_attrs_changes_vl
104 WHERE change_line_id = change.revised_item_sequence_id )
105 OR EXISTS
106 (SELECT 'X'
107 FROM eng_attachment_changes
108 WHERE revised_item_sequence_id = change.revised_item_sequence_id )
109 );
110
111 l_organization_id MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
112 l_item_number VARCHAR2(999);
113 l_org_name VARCHAR2(999);
114 l_is_master_org VARCHAR2(100);
115 l_status_master_controlled VARCHAR2(100);
116 l_pending_change_found VARCHAR2(100);
117
118 BEGIN
119 code_debug(' Check_Pending_Change_Orders Started ');
120 code_debug(' item id '||p_inventory_item_id||' org id '||p_organization_id||' revision id '||p_revision_id);
121 IF (p_inventory_item_id IS NULL
122 OR
123 p_organization_id is NULL) THEN
124 code_debug(' invalid params ');
125 x_return_status := FND_API.G_RET_STS_SUCCESS;
126 RETURN;
127 END IF;
128 l_pending_change_found := FND_API.G_FALSE;
129 l_is_master_org := get_master_org_status(p_organization_id => p_organization_id);
130 l_status_master_controlled := get_master_controlled_status();
131 IF p_revision_id IS NULL THEN
132 IF (FND_API.TO_BOOLEAN(l_status_master_controlled)) THEN
133 IF (FND_API.TO_BOOLEAN(l_is_master_org)) THEN
134 OPEN c_pending_changes(cp_item_id => p_inventory_item_id
135 ,cp_org_id => p_organization_id
136 ,cp_rev_id => p_revision_id
137 );
138 FETCH c_pending_changes INTO l_organization_id;
139 IF c_pending_changes%FOUND THEN
140 l_pending_change_found := FND_API.G_TRUE;
141 END IF;
142 CLOSE c_pending_changes;
143 ELSE
144 --
145 -- check if there are any changes in org hierarchy
146 --
147 OPEN c_pending_org_changes(cp_item_id => p_inventory_item_id
148 ,cp_org_id => p_organization_id
149 ,cp_rev_id => p_revision_id
150 );
151 FETCH c_pending_org_changes INTO l_organization_id;
152 IF c_pending_org_changes%FOUND THEN
153 l_pending_change_found := FND_API.G_TRUE;
154 END IF;
155 CLOSE c_pending_org_changes;
156 END IF; -- in master org
157 ELSE
158 -- status is not master controlled
159 OPEN c_pending_changes(cp_item_id => p_inventory_item_id
160 ,cp_org_id => p_organization_id
161 ,cp_rev_id => p_revision_id
162 );
163 FETCH c_pending_changes INTO l_organization_id;
164 IF c_pending_changes%FOUND THEN
165 l_pending_change_found := FND_API.G_TRUE;
166 END IF;
167 CLOSE c_pending_changes;
168 END IF; -- status is master controlled
169 ELSE
170 --
171 -- revision is present
172 -- revision is never master controlled, only org control
173 --
174 OPEN c_pending_changes(cp_item_id => p_inventory_item_id
175 ,cp_org_id => p_organization_id
176 ,cp_rev_id => p_revision_id
177 );
178 FETCH c_pending_changes INTO l_organization_id;
179 IF c_pending_changes%FOUND THEN
180 l_pending_change_found := FND_API.G_TRUE;
181 END IF;
182 CLOSE c_pending_changes;
183 END IF; -- p_revision_id IS NULL
184
185 IF (FND_API.TO_BOOLEAN(l_pending_change_found)) THEN
186 code_debug(' pending changes exist ');
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 --
189 -- get item name
190 --
191 SELECT concatenated_segments
192 INTO l_item_number
193 FROM mtl_system_items_kfv
194 WHERE inventory_item_id = p_inventory_item_id
195 AND organization_id = l_organization_id;
196 --
197 -- get organiation name
198 --
199 SELECT organization_name
200 INTO l_org_name
201 FROM org_organization_definitions
202 WHERE organization_id = l_organization_id;
203 fnd_message.set_name('EGO', 'EGO_ITEM_PENDING_CHANGES_EXIST');
204 fnd_message.set_token('ITEM_NUMBER', l_item_number);
205 fnd_message.set_token('ORG_NAME', l_org_name);
206 x_msg_data := fnd_message.get();
207 code_debug(' error msg '|| x_msg_data);
208 ELSE
209 code_debug(' no pending changes ');
210 x_return_status := FND_API.G_RET_STS_SUCCESS;
211 END IF;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 code_debug(' EXCEPTION in Check_Pending_Change_Orders ');
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 IF c_pending_changes%ISOPEN THEN
218 CLOSE c_pending_changes;
219 END IF;
220 IF c_pending_org_changes%ISOPEN THEN
221 CLOSE c_pending_org_changes;
222 END IF;
223 END Check_Pending_Change_Orders;
224 ***/
225
226 --
227 -- function to determine what action is being done
228 -- during phase / status change. returned values are
229 -- 'EGO_PRO_ITEM_LIFE_CYCLE' if user is trying to promote
230 -- 'EGO_DEM_ITEM_LIFE_CYCLE' if user is trying to demote
231 -- 'EGO_EDIT_ITEM_STATUS' if user is trying to change status
232 -- these are the data security function names.
233 --
234 FUNCTION get_privlige_name_for_action
235 (p_curr_item_id IN NUMBER
236 ,p_curr_org_id IN NUMBER
237 ,p_curr_rev_id IN NUMBER
238 ,p_curr_lc_id IN NUMBER
239 ,p_curr_phase_id IN NUMBER
240 ,p_curr_status_code IN VARCHAR2
241 ,p_new_lc_id IN NUMBER
242 ,p_new_phase_id IN NUMBER
243 ,p_new_status_code IN VARCHAR2
244 ) RETURN VARCHAR2 IS
245 l_curr_lc_id mtl_system_items_b.lifecycle_id%TYPE;
246 l_curr_phase_id mtl_system_items_b.current_phase_id%TYPE;
247 l_curr_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
248 l_curr_phase_seq NUMBER;
249 l_new_lc_id mtl_system_items_b.lifecycle_id%TYPE;
250 l_new_phase_id mtl_system_items_b.current_phase_id%TYPE;
251 l_new_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
252 l_new_phase_seq NUMBER;
253
254 -- data securiry functions supported
255 l_fn_name_promote VARCHAR2(50);
256 l_fn_name_demote VARCHAR2(50);
257 l_fn_name_change_status VARCHAR2(50);
258
259
260 CURSOR c_get_item_det (cp_inventory_item_id IN NUMBER
261 ,cp_organization_id IN NUMBER) IS
262 SELECT lifecycle_id, current_phase_id, inventory_item_status_code
263 FROM mtl_system_items_b
264 WHERE inventory_item_id = cp_inventory_item_id
265 AND organization_id = cp_organization_id;
266
267 CURSOR c_get_item_rev_det (cp_inventory_item_id IN NUMBER
268 ,cp_organization_id IN NUMBER
269 ,cp_revision_id IN NUMBER) IS
270 SELECT rev.lifecycle_id, rev.current_phase_id, itm.inventory_item_status_code
271 FROM mtl_system_items_b itm, mtl_item_revisions_b rev
272 WHERE itm.inventory_item_id = cp_inventory_item_id
273 AND itm.organization_id = cp_organization_id
274 AND rev.inventory_item_id = itm.inventory_item_id
275 AND rev.organization_id = rev.organization_id
276 AND rev.revision_id = rev.revision_id;
277
278 CURSOR c_get_phase_seq (cp_phase_id IN NUMBER ) IS
279 SELECT display_sequence
280 FROM pa_proj_element_versions
281 WHERE proj_element_id = cp_phase_id;
282
283 BEGIN
284 code_debug (' Get_privilege_name_for_action started');
285 l_fn_name_promote := 'EGO_PRO_ITEM_LIFE_CYCLE';
286 l_fn_name_demote := 'EGO_DEM_ITEM_LIFE_CYCLE';
287 l_fn_name_change_status := 'EGO_EDIT_ITEM_STATUS';
288 IF p_curr_status_code IS NULL THEN
289 IF (p_curr_rev_id IS NOT NULL) THEN
290 --
291 -- context of item revision
292 --
293 OPEN c_get_item_rev_det (cp_inventory_item_id => p_curr_item_id
294 ,cp_organization_id => p_curr_org_id
295 ,cp_revision_id => p_curr_rev_id
296 );
297 FETCH c_get_item_rev_det
298 INTO l_curr_lc_id, l_curr_phase_id, l_curr_status_code;
299 CLOSE c_get_item_rev_det;
300 ELSE
301 --
302 -- context of item
303 --
304 OPEN c_get_item_det (cp_inventory_item_id => p_curr_item_id
305 ,cp_organization_id => p_curr_org_id
306 );
307 FETCH c_get_item_det
308 INTO l_curr_lc_id, l_curr_phase_id, l_curr_status_code;
309 CLOSE c_get_item_det;
310 END IF;
311 ELSE
312 l_curr_lc_id := p_curr_lc_id;
313 l_curr_phase_id := p_curr_phase_id;
314 l_curr_status_code := p_curr_status_code;
315 END IF;
316 code_debug (' curr details lc '||l_curr_lc_id||' phase '||l_curr_phase_id||' status '||l_curr_status_code);
317 code_debug (' new details lc '||p_new_lc_id||' phase '||p_new_phase_id||' status '||p_new_status_code);
318 IF ( (p_new_lc_id IS NULL OR p_new_lc_id = l_curr_lc_id)
319 AND
320 (p_new_phase_id IS NULL OR p_new_phase_id = l_curr_phase_id)
321 AND
322 (l_curr_status_code <> NVL(p_new_status_code,l_curr_status_code))
323 ) THEN
324 -- user is trying to change status
325 RETURN l_fn_name_change_status;
326 ELSE
327 OPEN c_get_phase_seq(cp_phase_id => l_curr_phase_id);
328 FETCH c_get_phase_seq INTO l_curr_phase_seq;
329 IF c_get_phase_seq%NOTFOUND THEN
330 l_curr_phase_seq := -1;
331 END IF;
332 CLOSE c_get_phase_seq;
333 OPEN c_get_phase_seq(cp_phase_id => p_new_phase_id);
334 FETCH c_get_phase_seq INTO l_new_phase_seq;
335 IF c_get_phase_seq%NOTFOUND THEN
336 l_new_phase_seq := -1;
337 END IF;
338 CLOSE c_get_phase_seq;
339 IF l_curr_phase_seq < l_new_phase_seq THEN
340 RETURN l_fn_name_promote;
341 ELSIF l_curr_phase_seq > l_new_phase_seq THEN
342 RETURN l_fn_name_demote;
343 ELSE
344 RETURN l_fn_name_change_status;
345 END IF;
346 END IF;
347 RETURN NULL;
348 END get_privlige_name_for_action;
349
350 --
351 -- procedure to implement all pending changes
352 --
353 PROCEDURE Implement_All_Pending_Changes
354 (
355 p_api_version IN NUMBER
356 , p_commit IN VARCHAR2
357 , p_inventory_item_id IN NUMBER
358 , p_organization_id IN NUMBER
359 , p_revision_id IN NUMBER
360 , p_change_id IN NUMBER
361 , p_change_line_id IN NUMBER
362 , p_revision_master_controlled IN VARCHAR2
363 , p_status_master_controlled IN VARCHAR2
364 , p_perform_security_check IN VARCHAR2
365 , p_is_master_org IN VARCHAR2
366 , x_return_status OUT NOCOPY VARCHAR2
367 , x_errorcode OUT NOCOPY NUMBER
368 , x_msg_count OUT NOCOPY NUMBER
369 , x_msg_data OUT NOCOPY VARCHAR2
370 ) IS
371
372 CURSOR l_pending_revision_statuses IS
373 SELECT
374 inventory_item_id
375 ,revision_id
376 ,organization_id
377 ,phase_id
378 ,lifecycle_id
379 ,status_code
380 ,effective_date
381 FROM
382 MTL_PENDING_ITEM_STATUS
383 WHERE inventory_item_id = p_inventory_item_id
384 AND organization_id = p_organization_id
385 AND revision_id = p_revision_id
386 AND pending_flag = 'Y'
387 AND implemented_date IS NULL
388 AND effective_date <= SYSDATE
389 AND NVL(change_id,-1) = NVL(p_change_id, NVL(change_id,-1))
390 AND NVL(change_line_id,-1) = NVL(p_change_line_id, NVL(change_line_id,-1))
391 ORDER BY effective_date ASC
392 FOR UPDATE OF IMPLEMENTED_DATE, PENDING_FLAG;
393
394 CURSOR l_phase_ids IS
395 SELECT lifecycle_id, phase_id, status_code
396 FROM MTL_PENDING_ITEM_STATUS
397 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
398 AND ORGANIZATION_ID = p_organization_id
399 AND REVISION_ID IS NULL
400 AND PENDING_FLAG = 'Y'
401 AND IMPLEMENTED_DATE IS NULL
402 AND STATUS_CODE IS NOT NULL
403 AND EFFECTIVE_DATE <= SYSDATE
404 AND NVL(change_id,-1) = NVL(p_change_id, NVL(change_id,-1))
405 AND NVL(change_line_id,-1) = NVL(p_change_line_id, NVL(change_line_id,-1))
406 ORDER BY EFFECTIVE_DATE, LAST_UPDATE_DATE, ROWID;
407
408 CURSOR c_item_pending_phase_change IS
409 SELECT pending_status.phase_id
410 FROM MTL_PENDING_ITEM_STATUS pending_status, mtl_system_items_b item
411 WHERE pending_status.INVENTORY_ITEM_ID = p_inventory_item_id
412 AND pending_status.ORGANIZATION_ID = p_organization_id
413 AND pending_status.PENDING_FLAG = 'Y'
414 AND pending_status.IMPLEMENTED_DATE IS NULL
415 AND pending_status.EFFECTIVE_DATE <= SYSDATE
416 AND pending_status.inventory_item_id = item.inventory_item_id
417 AND pending_status.organization_id = item.organization_id
418 AND ( NVL(pending_status.lifecycle_id,NVL(item.lifecycle_id,-1)) <> NVL(item.lifecycle_id,-1)
419 OR
420 NVL(pending_status.phase_id,NVL(item.current_phase_id,-1)) <> NVL(item.current_phase_id,-1)
421 );
422
423 -- bug 3833932
424 CURSOR c_rev_pending_phase_change IS
425 SELECT pending_status.phase_id
426 FROM MTL_PENDING_ITEM_STATUS pending_status, mtl_item_revisions_b rev
427 WHERE pending_status.INVENTORY_ITEM_ID = p_inventory_item_id
428 AND pending_status.ORGANIZATION_ID = p_organization_id
429 AND pending_status.PENDING_FLAG = 'Y'
430 AND pending_status.IMPLEMENTED_DATE IS NULL
431 AND pending_status.EFFECTIVE_DATE <= SYSDATE
432 AND pending_status.inventory_item_id = rev.inventory_item_id
433 AND pending_status.organization_id = rev.organization_id
434 AND pending_status.revision_id = rev.revision_id
435 AND ( NVL(pending_status.lifecycle_id,NVL(rev.lifecycle_id,-1)) <> NVL(rev.lifecycle_id,-1)
436 OR
437 NVL(pending_status.phase_id,NVL(rev.current_phase_id,-1)) <> NVL(rev.current_phase_id,-1)
438 );
439
440 --Start: 4105841 Business Event Enhancement
441
442 Cursor c_get_item_details(p_inventory_item_id NUMBER,
443 p_organization_id NUMBER) IS
444 SELECT MSI.organization_id,
445 MSI.description,
446 MSI.concatenated_segments,
447 MP.ORGANIZATION_CODE
448 FROM MTL_SYSTEM_ITEMS_KFV MSI,
449 MTL_PARAMETERS MP
450 WHERE
451 MSI.INVENTORY_ITEM_ID = p_inventory_item_id
452 AND MSI.ORGANIZATION_ID = p_organization_id
453 AND MSI.Organization_ID = MP.Organization_ID;
454
455 l_event_return_status VARCHAR2(1);
456 l_phase_update VARCHAR2(1);
457 l_msg_data VARCHAR2(2000);
458 l_old_status MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE%TYPE;
459 l_new_status MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE%TYPE;
460 --End 4105841
461
462 l_api_version NUMBER;
463 l_api_name VARCHAR2(30);
464 l_current_phase_id MTL_SYSTEM_ITEMS_B.CURRENT_PHASE_ID%TYPE;
465 l_current_revision MTL_ITEM_REVISIONS_B.REVISION%TYPE;
466 l_perform_policy_check BOOLEAN;
467 l_priv_name_to_check VARCHAR2(100);
468
469
470
471 BEGIN
472 l_api_version := 1.0;
473 l_api_name := 'Implement_All_Pending_Changes';
474 l_perform_policy_check := FALSE;
475 l_phase_update := NULL;
476 code_debug(' Implement All Pending Changes called with params ');
477 code_debug(' p_api_version : '||p_api_version||' p_commit : '|| p_commit);
478 code_debug(' p_inventory_item_id :'||p_inventory_item_id||' p_organization_id : '||p_organization_id);
479 code_debug(' p_revision_id : '||p_revision_id||' p_revision_master_controlled: '||p_revision_master_controlled);
480 code_debug(' p_status_master_controlled : '||p_status_master_controlled||' p_is_master_org: '||p_is_master_org);
481
482 IF FND_API.To_Boolean(p_commit) THEN
483 SAVEPOINT Implement_All_Pending_Changes;
484 END IF;
485
486 --Standard checks
487 IF NOT FND_API.Compatible_API_Call (l_api_version
488 ,p_api_version
489 ,l_api_name
490 ,g_pkg_name)
491 THEN
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493 END IF;
494
495 IF Nvl(FND_GLOBAL.User_Id,-1) <> Nvl(G_CURRENT_USER_ID,-1)
496 THEN
497 G_CURRENT_USER_ID := FND_GLOBAL.User_Id;
498 G_CURRENT_LOGIN_ID := FND_GLOBAL.Login_Id;
499 END IF;
500
501 --
502 -- replaced call to EGO_INV_ITEM_CATALOG_PVT.Check_pending_Change_Orders
503 -- as a part of bug 3696801
504 -- Check_Pending_Change_Orders (p_inventory_item_id => p_inventory_item_id
505 -- ,p_organization_id => p_organization_id
506 -- ,p_revision_id => p_revision_id
507 -- ,x_return_status => x_return_status
508 -- ,x_msg_data => x_msg_data
509 -- );
510 -- bug 3833932 doing pending phase change only on the corresponding item/rev
511 IF p_revision_id IS NULL THEN
512 OPEN c_item_pending_phase_change;
513 FETCH c_item_pending_phase_change INTO l_current_Phase_id;
514 IF c_item_pending_phase_change%FOUND THEN
515 l_perform_policy_check := TRUE;
516 END IF;
517 CLOSE c_item_pending_phase_change;
518 ELSE
519 OPEN c_rev_pending_phase_change;
520 FETCH c_rev_pending_phase_change INTO l_current_Phase_id;
521 IF c_rev_pending_phase_change%FOUND THEN
522 l_perform_policy_check := TRUE;
523 END IF;
524 CLOSE c_rev_pending_phase_change;
525 END IF;
526
527 IF l_perform_policy_check THEN
528 code_debug (' performing policy check ');
529 EGO_INV_ITEM_CATALOG_PVT.Check_pending_Change_Orders (
530 p_inventory_item_id => p_inventory_item_id
531 ,p_organization_id => p_organization_id
532 ,p_revision_id => p_revision_id
533 ,p_lifecycle_changed => FND_API.G_FALSE
534 ,p_lifecycle_phase_changed => FND_API.G_TRUE
535 ,p_change_id => p_change_id
536 ,p_change_line_id => p_change_line_id
537 ,x_return_status => x_return_status
538 ,x_msg_count => x_msg_count
539 ,x_msg_data => x_msg_data
540 );
541 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
542 code_debug (' pending co exist '|| x_msg_data);
543 IF FND_API.To_Boolean(p_commit) THEN
544 ROLLBACK TO Implement_All_Pending_Changes;
545 END IF;
546 RETURN;
547 END IF;
548 END IF;
549
550 code_debug (' no pending change orders exist ');
551
552 IF FND_API.TO_BOOLEAN(p_is_master_org) THEN
553 code_debug (' in context of master org ');
554 ELSE
555 code_debug (' in context of child org ');
556 END IF;
557
558 IF FND_API.TO_BOOLEAN(p_status_master_controlled) THEN
559 code_debug (' status is master controlled ');
560 ELSE
561 code_debug (' status is controlled at org level ');
562 END IF;
563
564 --
565 -- to be removed after bug 3874132 is resoloved.
566 --
567 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
568 IF FND_API.To_Boolean( p_commit ) THEN
569 ROLLBACK TO Implement_All_Pending_Changes;
570 END IF;
571 RETURN;
572 END IF;
573
574 IF (p_revision_id IS NULL) THEN
575
576 code_debug (' processing changes for item ');
577 code_debug ('p_revision_id is null ');
578
579 --
580 -- If it's at the master and status is master controlled,
581 -- then do it for all assigned orgs
582 --
583 IF (FND_API.TO_BOOLEAN(p_is_master_org)
584 OR
585 FND_API.TO_BOOLEAN(p_status_master_controlled) = FALSE
586 ) THEN
587 IF (FND_API.TO_BOOLEAN(p_is_master_org)
588 AND
589 FND_API.TO_BOOLEAN(p_status_master_controlled) = TRUE
590 ) THEN
591 -- Get the most recent phase id
592 code_debug(' before the loop' );
593
594 FOR l_phase_id IN l_phase_ids LOOP
595 IF FND_API.To_boolean(p_perform_security_check) THEN
596 -- 4052565 perform security check
597 l_priv_name_to_check := get_privlige_name_for_action
598 (p_curr_item_id => p_inventory_item_id
599 ,p_curr_org_id => p_organization_id
600 ,p_curr_rev_id => p_revision_id
601 ,p_curr_lc_id => NULL
602 ,p_curr_phase_id => NULL
603 ,p_curr_status_code => NULL
604 ,p_new_lc_id => l_phase_id.lifecycle_id
605 ,p_new_phase_id => l_phase_id.phase_id
606 ,p_new_status_code => l_phase_id.status_code
607 );
608 IF l_priv_name_to_check IS NOT NULL THEN
609 IF NOT EGO_ITEM_PVT.has_role_on_item
610 (p_function_name => l_priv_name_to_check
611 ,p_inventory_item_id => p_inventory_item_id
612 ,p_item_number => NULL
613 ,p_organization_id => p_organization_id
614 ,p_organization_name => NULL
615 ,p_user_id => G_CURRENT_USER_ID
616 ,p_party_id => NULL
617 ,p_set_message => FND_API.G_TRUE
618 ) THEN
619 RAISE FND_API.G_EXC_ERROR;
620 END IF;
621 END IF;
622 END IF;
623
624 code_debug(' before check_floating_attachments 1');
625 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => p_inventory_item_id
626 ,p_revision_id => p_revision_id
627 ,p_organization_id => p_organization_id
628 ,p_lifecycle_id => NULL
629 ,p_new_phase_id => l_phase_id.phase_id
630 ,x_return_status => x_return_status
631 ,x_msg_count => x_msg_count
632 ,x_msg_data => x_msg_data );
633
634 code_debug(' after check_floating_attachments 1 ' || x_return_status);
635 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
636 IF FND_API.To_Boolean( p_commit ) THEN
637 ROLLBACK TO Implement_All_Pending_Changes;
638 END IF;
639 RETURN;
640 END IF;
641 UPDATE MTL_SYSTEM_ITEMS_B
642 SET CURRENT_PHASE_ID = NVL(l_phase_id.PHASE_ID,current_phase_id)
643 WHERE
644 INVENTORY_ITEM_ID = p_inventory_item_id
645 AND ORGANIZATION_ID IN
646 (SELECT P2.ORGANIZATION_ID
647 FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
648 WHERE P1.ORGANIZATION_ID = p_organization_id
649 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID);
650 l_phase_update := 'Y';
651 END LOOP;
652 code_debug(' out side the loop ');
653 --
654 -- Otherwise, either we are not at master or not master controlled.
655 -- So if not master controlled, do it for the current org
656 --
657 ELSIF (FND_API.TO_BOOLEAN(p_status_master_controlled) = FALSE) THEN
658 --
659 -- Get the most recent phase id
660 --
661 FOR l_phase_id IN l_phase_ids LOOP
662
663 IF FND_API.To_boolean(p_perform_security_check) THEN
664 -- 4052565 perform security check
665 l_priv_name_to_check := get_privlige_name_for_action
666 (p_curr_item_id => p_inventory_item_id
667 ,p_curr_org_id => p_organization_id
668 ,p_curr_rev_id => p_revision_id
669 ,p_curr_lc_id => NULL
670 ,p_curr_phase_id => NULL
671 ,p_curr_status_code => NULL
672 ,p_new_lc_id => l_phase_id.lifecycle_id
673 ,p_new_phase_id => l_phase_id.phase_id
674 ,p_new_status_code => l_phase_id.status_code
675 );
676 IF l_priv_name_to_check IS NOT NULL THEN
677 IF NOT EGO_ITEM_PVT.has_role_on_item
678 (p_function_name => l_priv_name_to_check
679 ,p_inventory_item_id => p_inventory_item_id
680 ,p_item_number => NULL
681 ,p_organization_id => p_organization_id
682 ,p_organization_name => NULL
683 ,p_user_id => G_CURRENT_USER_ID
684 ,p_party_id => NULL
685 ,p_set_message => FND_API.G_TRUE
686 ) THEN
687 RAISE FND_API.G_EXC_ERROR;
688 END IF;
689 END IF;
690 END IF;
691
692 code_debug(' before check_floating_attachments 2');
693 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => p_inventory_item_id
694 ,p_revision_id => p_revision_id
695 ,p_organization_id => p_organization_id
696 ,p_lifecycle_id => NULL
697 ,p_new_phase_id => l_phase_id.phase_id
698 ,x_return_status => x_return_status
699 ,x_msg_count => x_msg_count
700 ,x_msg_data => x_msg_data );
701
702 code_debug(' after check_floating_attachments 2 ' || x_return_status);
703 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
704 IF FND_API.To_Boolean( p_commit ) THEN
705 ROLLBACK TO Implement_All_Pending_Changes;
706 END IF;
707 RETURN;
708 END IF;
709
710 UPDATE MTL_SYSTEM_ITEMS_B
711 SET CURRENT_PHASE_ID = NVL(l_phase_id.PHASE_ID, current_phase_id)
712 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
713 AND ORGANIZATION_ID = p_organization_id;
714 l_phase_update := 'Y';
715 END LOOP;
716 END IF;
717 --
718 -- Now call another api to update statuses,
719 -- but only if we are at the master org or status is org controlled
720 --
721 ---Start 4105841 Business events
722 SELECT inventory_item_status_code INTO l_old_status
723 FROM mtl_system_items_b msi
724 WHERE msi.inventory_item_id = p_inventory_item_id
725 AND msi.organization_id = p_organization_id
726 AND rownum < 2;
727 ---End 4105841
728
729 INV_ITEM_STATUS_PUB.Update_Pending_Status (1.0
730 ,p_organization_id
731 ,p_inventory_item_id
732 ,NULL
733 ,NULL
734 ,x_return_status
735 ,x_msg_count
736 ,x_msg_data
737 );
738 --Added for bug 5230594
739 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
740 ROLLBACK TO Implement_All_Pending_Changes;
741 RETURN;
742 END IF;
743
744
745 ---Start 4105841 Business events
746 SELECT inventory_item_status_code INTO l_new_status
747 FROM mtl_system_items_b msi
748 WHERE msi.inventory_item_id = p_inventory_item_id
749 AND msi.organization_id = p_organization_id
750 AND rownum < 2;
751
752 IF l_phase_update = 'Y' OR
753 NVL(l_old_status,-1) <> NVL(l_new_status,-1) THEN
754 FOR Item_Rec IN c_get_item_details(p_inventory_item_id
755 ,p_organization_id) LOOP
756 EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
757 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
758 ,p_organization_id => p_organization_id
759 ,p_organization_code => Item_Rec.organization_code
760 ,p_item_number => Item_Rec.concatenated_segments
761 ,p_item_description => Item_Rec.DESCRIPTION
762 ,p_inventory_item_id => p_inventory_item_id
763 ,x_msg_data => l_msg_data
764 ,x_return_status => l_event_return_status);
765
766 --Call ICX APIs
767 BEGIN
768 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
769 p_entity_type => 'ITEM'
770 ,p_dml_type => 'UPDATE'
771 ,p_inventory_item_id => p_inventory_item_id
772 ,p_item_number => Item_Rec.concatenated_segments
773 ,p_item_description => Item_Rec.DESCRIPTION
774 ,p_organization_id => p_organization_id
775 ,p_organization_code => Item_Rec.organization_code );
776 EXCEPTION
777 WHEN OTHERS THEN
778 NULL;
779 END;
780 --R12: Business Event Enhancement
781 END LOOP;
782 END IF;
783 --End 4105841 Business Event
784
785 END IF;
786
787 ELSE
788 code_debug (' processing changes for item revision ');
789 --
790 -- Get all of the pending records
791 --
792 FOR l_pending_record IN l_pending_revision_statuses LOOP
793 code_debug (' processing revision '||l_pending_record.REVISION_ID);
794 IF NVL(p_perform_security_check,FND_API.G_FALSE) = FND_API.G_TRUE THEN
795 -- 4052565 perform security check
796 l_priv_name_to_check := get_privlige_name_for_action
797 (p_curr_item_id => p_inventory_item_id
798 ,p_curr_org_id => p_organization_id
799 ,p_curr_rev_id => p_revision_id
800 ,p_curr_lc_id => NULL
801 ,p_curr_phase_id => NULL
802 ,p_curr_status_code => NULL
803 ,p_new_lc_id => l_pending_record.lifecycle_id
804 ,p_new_phase_id => l_pending_record.phase_id
805 ,p_new_status_code => l_pending_record.status_code
806 );
807 IF l_priv_name_to_check IS NOT NULL THEN
808 IF NOT EGO_ITEM_PVT.has_role_on_item
809 (p_function_name => l_priv_name_to_check
810 ,p_inventory_item_id => p_inventory_item_id
811 ,p_item_number => NULL
812 ,p_organization_id => p_organization_id
813 ,p_organization_name => NULL
814 ,p_user_id => G_CURRENT_USER_ID
815 ,p_party_id => NULL
816 ,p_set_message => FND_API.G_TRUE
817 ) THEN
818 RAISE FND_API.G_EXC_ERROR;
819 END IF;
820 END IF;
821 END IF;
822
823 IF l_pending_record.PHASE_ID IS NOT NULL THEN
824 --
825 -- If master controlled and we are at master
826 --
827 IF (FND_API.TO_BOOLEAN(p_revision_master_controlled)
828 AND
829 FND_API.TO_BOOLEAN(p_is_master_org)
830 ) THEN
831 code_debug (' rev is master controlled and we are at master org ');
832 --
833 -- First get the revision code
834 --
835 SELECT REVISION INTO l_current_revision
836 FROM MTL_ITEM_REVISIONS_B
837 WHERE
838 INVENTORY_ITEM_ID = l_pending_record.INVENTORY_ITEM_ID
839 AND REVISION_ID = l_pending_record.REVISION_ID
840 AND ORGANIZATION_ID = l_pending_record.ORGANIZATION_ID;
841 --
842 -- Update for all orgs
843 --
844
845 code_debug(' before check_floating_attachments 3');
846 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => l_pending_record.INVENTORY_ITEM_ID
847 ,p_revision_id => l_pending_record.REVISION_ID
848 ,p_organization_id => l_pending_record.ORGANIZATION_ID
849 ,p_lifecycle_id => NULL
850 ,p_new_phase_id => l_pending_record.PHASE_ID
851 ,x_return_status => x_return_status
852 ,x_msg_count => x_msg_count
853 ,x_msg_data => x_msg_data );
854
855 code_debug(' after check_floating_attachments 3 ' || x_return_status);
856 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
857 IF FND_API.To_Boolean( p_commit ) THEN
858 ROLLBACK TO Implement_All_Pending_Changes;
859 END IF;
860 RETURN;
861 END IF;
862
863 UPDATE MTL_ITEM_REVISIONS_B
864 SET CURRENT_PHASE_ID = l_pending_record.PHASE_ID
865 WHERE
866 INVENTORY_ITEM_ID = l_pending_record.INVENTORY_ITEM_ID
867 AND REVISION = l_current_revision
868 AND ORGANIZATION_ID IN
869 (SELECT P2.ORGANIZATION_ID
870 FROM MTL_PARAMETERS P1,
871 MTL_PARAMETERS P2
872 WHERE P1.ORGANIZATION_ID = p_organization_id
873 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID);
874
875 ELSIF (FND_API.TO_BOOLEAN(p_revision_master_controlled) = FALSE) THEN
876 code_debug (' rev is org controlled and we are at master org ');
877 --
878 -- Just update for the current one
879 --
880 code_debug(' before check_floating_attachments 4##########');
881 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => l_pending_record.INVENTORY_ITEM_ID
882 ,p_revision_id => l_pending_record.REVISION_ID
883 ,p_organization_id => l_pending_record.ORGANIZATION_ID
884 ,p_lifecycle_id => NULL
885 ,p_new_phase_id => l_pending_record.PHASE_ID
886 ,x_return_status => x_return_status
887 ,x_msg_count => x_msg_count
888 ,x_msg_data => x_msg_data );
889
890 code_debug(' after check_floating_attachments 4 ' || x_return_status);
891 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
892 IF FND_API.To_Boolean( p_commit ) THEN
893 ROLLBACK TO Implement_All_Pending_Changes;
894 END IF;
895 RETURN;
896 END IF;
897
898 UPDATE MTL_ITEM_REVISIONS_B
899 SET CURRENT_PHASE_ID = l_pending_record.PHASE_ID
900 WHERE
901 INVENTORY_ITEM_ID = l_pending_record.INVENTORY_ITEM_ID
902 AND REVISION_ID = l_pending_record.REVISION_ID
903 AND ORGANIZATION_ID = l_pending_record.ORGANIZATION_ID;
904 END IF;
905
906 code_debug (' modifying the pending status table now ');
907 IF (FND_API.TO_BOOLEAN(p_is_master_org)
908 OR
909 FND_API.TO_BOOLEAN(p_revision_master_controlled) = FALSE
910 ) THEN
911 UPDATE MTL_PENDING_ITEM_STATUS
912 SET
913 PENDING_FLAG = 'N'
914 ,IMPLEMENTED_DATE = SYSDATE
915 WHERE CURRENT OF l_pending_revision_statuses;
916 END IF;
917
918 END IF;
919
920 END LOOP;
921
922 END IF;
923
924 IF FND_API.To_Boolean(p_commit) THEN
925 COMMIT WORK;
926 -- Call IP Intermedia Sync
927 INV_ITEM_EVENTS_PVT.Sync_IP_IM_Index;
928 END IF;
929 x_return_status := FND_API.G_RET_STS_SUCCESS;
930
931 EXCEPTION
932 WHEN FND_API.G_EXC_ERROR THEN
933 IF FND_API.To_Boolean( p_commit ) THEN
934 ROLLBACK TO Implement_All_Pending_Changes;
935 END IF;
936 x_return_status := FND_API.G_RET_STS_ERROR;
937 FND_MSG_PUB.Count_And_Get(
938 p_encoded => FND_API.G_FALSE,
939 p_count => x_msg_count,
940 p_data => x_msg_data
941 );
942 WHEN OTHERS THEN
943 IF FND_API.To_Boolean(p_commit) THEN
944 ROLLBACK TO Implement_All_Pending_Changes;
945 END IF;
946 IF c_item_pending_phase_change%ISOPEN THEN
947 CLOSE c_item_pending_phase_change;
948 END IF;
949 IF c_rev_pending_phase_change%ISOPEN THEN
950 CLOSE c_rev_pending_phase_change;
951 END IF;
952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
953 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
954 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
955 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, 'IMPLEMENT_ALL_PENDING_CHANGES');
956 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
957 x_msg_count := 1;
958 x_msg_data := FND_MESSAGE.GET;
959 -- x_return_status := FND_API.G_RET_STS_ERROR;
960 -- x_msg_data := FND_MESSAGE.Get_String('EGO', 'EGO_EXT_EXCEPTION_OCCURED');
961
962 END Implement_All_Pending_Changes;
963
964
965
966 ----------------------------------------------------------------------
967 -- Public Procedures / Functions
968 ----------------------------------------------------------------------
969
970 FUNCTION get_master_controlled_status RETURN VARCHAR2 IS
971 l_status_master_controlled VARCHAR2(100);
972 BEGIN
973 l_status_master_controlled := FND_API.G_FALSE;
974 SELECT DECODE(LOOKUP_CODE2,
975 1, FND_API.G_TRUE,
976 2, FND_API.G_FALSE,
977 FND_API.G_FALSE)
978 INTO l_status_master_controlled
979 FROM MTL_ITEM_ATTRIBUTES_V
980 WHERE ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
981
982 RETURN l_status_master_controlled;
983 EXCEPTION
984 WHEN OTHERS THEN
985 RETURN l_status_master_controlled;
986 END get_master_controlled_status;
987
988
989 -------------------------------------------------------
990 FUNCTION get_master_org_status (p_organization_id IN NUMBER)
991 RETURN VARCHAR2 IS
992 l_is_master_org VARCHAR2(100);
993 BEGIN
994 l_is_master_org := FND_API.G_FALSE;
995 SELECT DECODE(MP.ORGANIZATION_ID,
996 MP.MASTER_ORGANIZATION_ID, FND_API.G_TRUE,
997 FND_API.G_FALSE)
998 INTO l_is_master_org
999 FROM MTL_PARAMETERS MP
1000 WHERE MP.ORGANIZATION_ID = p_organization_id;
1001
1002 RETURN l_is_master_org;
1003
1004 EXCEPTION
1005 WHEN OTHERS THEN
1006 RETURN l_is_master_org;
1007 END get_master_org_status;
1008
1009
1010 -------------------------------------------------------
1011 FUNCTION get_revision_id (p_inventory_item_id IN NUMBER
1012 ,p_organization_id IN NUMBER
1013 ,p_revision IN VARCHAR2)
1014 RETURN NUMBER IS
1015 l_revision_id NUMBER;
1016 BEGIN
1017 l_revision_id := NULL;
1018 SELECT REVISION_ID
1019 INTO l_revision_id
1020 FROM MTL_ITEM_REVISIONS
1021 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1022 AND ORGANIZATION_ID = p_organization_id
1023 AND revision = p_revision;
1024 RETURN l_revision_id;
1025
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 RETURN l_revision_id;
1029 END get_revision_id;
1030
1031
1032 -------------------------------------------------------
1033 PROCEDURE Create_Pending_Phase_Change
1034 (
1035 p_api_version IN NUMBER
1036 ,p_commit IN VARCHAR2
1037 ,p_inventory_item_id IN NUMBER
1038 ,p_item_number IN VARCHAR2 DEFAULT NULL
1039 ,p_organization_id IN NUMBER
1040 ,p_effective_date IN DATE
1041 ,p_pending_flag IN VARCHAR2
1042 ,p_revision IN VARCHAR2
1043 ,p_revision_id IN NUMBER DEFAULT NULL
1044 ,p_lifecycle_id IN NUMBER
1045 ,p_phase_id IN NUMBER
1046 ,p_status_code IN VARCHAR2 DEFAULT NULL
1047 ,p_change_id IN NUMBER
1048 ,p_change_line_id IN NUMBER
1049 ,p_perform_security_check IN VARCHAR2 DEFAULT 'F'
1050 ,x_return_status OUT NOCOPY VARCHAR2
1051 ,x_errorcode OUT NOCOPY NUMBER
1052 ,x_msg_count OUT NOCOPY NUMBER
1053 ,x_msg_data OUT NOCOPY VARCHAR2
1054 ) IS
1055
1056 l_api_version NUMBER;
1057 l_api_name VARCHAR2(50);
1058 l_sysdate DATE;
1059 l_revision mtl_item_revisions_b.revision%TYPE;
1060 l_revision_id mtl_pending_item_status.revision_id%TYPE;
1061 l_phase_id mtl_pending_item_status.phase_id%TYPE;
1062 l_lifecycle_id mtl_pending_item_status.lifecycle_id%TYPE;
1063 l_status_code mtl_pending_item_status.status_code%TYPE;
1064 l_phase_id_curr mtl_pending_item_status.phase_id%TYPE;
1065 l_lifecycle_id_itm mtl_pending_item_status.lifecycle_id%TYPE;
1066 l_status_code_itm mtl_pending_item_status.status_code%TYPE;
1067 l_status_code_def mtl_pending_item_status.status_code%TYPE;
1068 l_pending_rec_count NUMBER;
1069 l_item_number MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
1070 l_org_name HR_ALL_ORGANIZATION_UNITS_VL.NAME%TYPE;
1071 l_dummy_char VARCHAR2(32767);
1072 l_approval_status mtl_system_items_b.approval_status%TYPE;
1073 l_priv_name_to_check VARCHAR2(100);
1074
1075 l_revision_master_controlled VARCHAR2(1);
1076 l_status_master_controlled VARCHAR2(1);
1077 l_is_master_org VARCHAR2(1);
1078
1079 CURSOR c_get_rev_details (cp_item_id IN NUMBER
1080 ,cp_org_id IN NUMBER
1081 ,cp_revision IN VARCHAR2
1082 ,cp_revision_id IN NUMBER) IS
1083 SELECT rev.current_phase_id, rev.lifecycle_id, rev.revision, rev.revision_id, item.approval_status
1084 FROM mtl_item_revisions_b rev, mtl_system_items_b item
1085 WHERE rev.inventory_item_id = cp_item_id
1086 AND rev.organization_id = cp_org_id
1087 AND rev.revision = NVL(cp_revision, rev.revision)
1088 AND rev.revision_id = NVL(cp_revision_id, rev.revision_id)
1089 AND item.inventory_item_id = rev.inventory_item_id
1090 AND item.organization_id = rev.organization_id;
1091
1092 CURSOR c_get_item_details (cp_item_id IN NUMBER
1093 ,cp_org_id IN NUMBER) IS
1094 SELECT current_phase_id, lifecycle_id, inventory_item_status_code, approval_status
1095 FROM mtl_system_items_b
1096 WHERE inventory_item_id = cp_item_id
1097 AND organization_id = cp_org_id;
1098
1099 CURSOR c_get_def_status_code (cp_phase_id IN NUMBER) IS
1100 SELECT status.item_status_code
1101 FROM ego_lcphase_item_status status, pa_proj_elements lc_phases
1102 WHERE lc_phases.proj_element_id = cp_phase_id
1103 AND status.phase_code = lc_phases.phase_code
1104 AND status.default_flag = 'Y'
1105 AND lc_phases.PROJECT_ID = 0 AND lc_phases.OBJECT_TYPE = 'PA_TASKS';
1106
1107 CURSOR c_validate_status_code (cp_phase_id IN NUMBER
1108 ,cp_status_code IN VARCHAR2) IS
1109 SELECT status.item_status_code
1110 FROM ego_lcphase_item_status status, pa_ego_phases_v lc_phases
1111 WHERE lc_phases.proj_element_id = cp_phase_id
1112 AND status.phase_code = lc_phases.phase_code
1113 AND status.item_status_code = cp_status_code;
1114
1115 BEGIN
1116
1117 l_api_version := 1.0;
1118 l_api_name := 'Create_Pending_Phase_Change';
1119
1120 IF FND_API.To_Boolean(p_commit) THEN
1121 SAVEPOINT Create_Pending_Phase_Change_SP;
1122 END IF;
1123 code_debug( l_api_name ||' started with params');
1124 code_debug( l_api_name ||' p_inventory_item_id '|| p_inventory_item_id ||' p_organization_id '|| p_organization_id || ' p_revision '|| p_revision);
1125 code_debug( l_api_name ||' p_lifecycle_id '|| p_lifecycle_id ||' p_phase_id '|| p_phase_id ||' p_status_code '|| p_status_code);
1126 code_debug( l_api_name ||' p_pending_flag '|| p_pending_flag ||' p_change_id '|| p_change_id ||' p_change_line_id '|| p_change_line_id);
1127
1128 --Standard checks
1129 IF NOT FND_API.Compatible_API_Call (l_api_version
1130 ,p_api_version
1131 ,l_api_name
1132 ,g_pkg_name)
1133 THEN
1134 RAISE FND_API.G_EXC_ERROR;
1135 END IF;
1136
1137 IF Nvl(FND_GLOBAL.User_Id,-1) <> Nvl(G_CURRENT_USER_ID,-1)
1138 THEN
1139 G_CURRENT_USER_ID := FND_GLOBAL.User_Id;
1140 G_CURRENT_LOGIN_ID := FND_GLOBAL.Login_Id;
1141 END IF;
1142
1143 IF (p_inventory_item_id IS NULL
1144 OR
1145 p_organization_id IS NULL
1146 OR
1147 ( (p_revision IS NOT NULL OR p_revision_id IS NOT NULL) AND p_status_code IS NOT NULL)
1148 OR
1149 (p_phase_id IS NULL AND p_status_code IS NULL)
1150 ) THEN
1151 fnd_message.Set_Name(G_APP_NAME, 'EGO_API_INVALID_PARAMS');
1152 fnd_message.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
1153 fnd_message.Set_Token(G_API_NAME_TOKEN, l_api_name);
1154 FND_MSG_PUB.Add;
1155 RAISE FND_API.G_EXC_ERROR;
1156 END IF;
1157
1158 l_revision := p_revision;
1159 l_revision_id := p_revision_id;
1160
1161 IF (l_revision IS NULL AND l_revision_id IS NULL) THEN
1162 OPEN c_get_item_details (cp_item_id => p_inventory_item_id
1163 ,cp_org_id => p_organization_id);
1164 FETCH c_get_item_details
1165 INTO l_phase_id_curr, l_lifecycle_id_itm, l_status_code_itm, l_approval_status;
1166 IF c_get_item_details%NOTFOUND THEN
1167 l_phase_id_curr := NULL;
1168 l_lifecycle_id_itm := NULL;
1169 END IF;
1170 CLOSE c_get_item_details;
1171 ELSE
1172 l_status_code_itm := NULL;
1173 OPEN c_get_rev_details (cp_item_id => p_inventory_item_id
1174 ,cp_org_id => p_organization_id
1175 ,cp_revision => l_revision
1176 ,cp_revision_id => l_revision_id);
1177 FETCH c_get_rev_details
1178 INTO l_phase_id_curr, l_lifecycle_id_itm, l_revision, l_revision_id, l_approval_status;
1179 IF c_get_rev_details%NOTFOUND THEN
1180 l_phase_id_curr := NULL;
1181 l_lifecycle_id_itm := NULL;
1182 l_revision_id := NULL;
1183 END IF;
1184 CLOSE c_get_rev_details;
1185 END IF;
1186
1187 code_debug( l_api_name ||' curr values - current_phase_id '||l_phase_id_curr||' lifecycle_id '||l_lifecycle_id_itm||' status_code '||l_status_code_itm );
1188
1189 -- bug 3909677
1190 IF NVL(l_approval_status,'A') <> 'A' THEN
1191 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_NOT_READY_FOR_CHANGE');
1192 SELECT CONCATENATED_SEGMENTS
1193 INTO l_dummy_char
1194 FROM MTL_SYSTEM_ITEMS_KFV
1195 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1196 AND ORGANIZATION_ID = p_organization_id;
1197 fnd_message.set_token('ITEM_NUMBER', l_dummy_char);
1198 SELECT name
1199 INTO l_dummy_char
1200 FROM hr_all_organization_units_vl
1201 WHERE organization_id = p_organization_id;
1202 fnd_message.set_token('ORGANIZATION', l_dummy_char);
1203 fnd_msg_pub.Add;
1204 RAISE FND_API.G_EXC_ERROR;
1205 END IF;
1206
1207 IF FND_API.To_Boolean(p_perform_security_check) THEN
1208 code_debug( l_api_name ||' need to perform security check ');
1209 -- 4052565 perform security check
1210 l_priv_name_to_check := get_privlige_name_for_action
1211 (p_curr_item_id => p_inventory_item_id
1212 ,p_curr_org_id => p_organization_id
1213 ,p_curr_rev_id => l_revision_id
1214 ,p_curr_lc_id => l_lifecycle_id_itm
1215 ,p_curr_phase_id => l_phase_id_curr
1216 ,p_curr_status_code => l_status_code_itm
1217 ,p_new_lc_id => p_lifecycle_id
1218 ,p_new_phase_id => p_phase_id
1219 ,p_new_status_code => p_status_code
1220 );
1221 code_debug( l_api_name ||' priv check name '||l_priv_name_to_check);
1222 IF l_priv_name_to_check IS NOT NULL THEN
1223 IF NOT EGO_ITEM_PVT.has_role_on_item
1224 (p_function_name => l_priv_name_to_check
1225 ,p_inventory_item_id => p_inventory_item_id
1226 ,p_item_number => p_item_number
1227 ,p_organization_id => p_organization_id
1228 ,p_organization_name => NULL
1229 ,p_user_id => G_CURRENT_USER_ID
1230 ,p_party_id => NULL
1231 ,p_set_message => FND_API.G_TRUE
1232 ) THEN
1233 code_debug( l_api_name ||' user does not have privilege for '||l_priv_name_to_check);
1234 RAISE FND_API.G_EXC_ERROR;
1235 ELSE
1236 code_debug( l_api_name ||' user can perform the action '||l_priv_name_to_check);
1237 END IF;
1238 END IF;
1239 ELSE
1240 code_debug( l_api_name ||' NO need to perform security check ');
1241 END IF;
1242
1243 IF p_phase_id IS NOT NULL THEN
1244 --
1245 -- if status is not passed, get the default status code
1246 --
1247 l_phase_id := p_phase_id;
1248 l_lifecycle_id := NVL(p_lifecycle_id, l_lifecycle_id_itm);
1249 IF p_status_code IS NULL THEN
1250 -- get the default phase for the new phase
1251 OPEN c_get_def_status_code (cp_phase_id => p_phase_id);
1252 FETCH c_get_def_status_code INTO l_status_code_def;
1253 IF c_get_def_status_code%NOTFOUND THEN
1254 CLOSE c_get_def_status_code;
1255 SELECT name
1256 INTO l_dummy_char
1257 FROM PA_EGO_PHASES_V
1258 WHERE proj_element_id = p_phase_id;
1259 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_STATUS_FOR_PHASE_ERR');
1260 fnd_message.Set_Token('PHASE', l_dummy_char);
1261 FND_MSG_PUB.Add;
1262 RAISE FND_API.G_EXC_ERROR;
1263 ELSE
1264 CLOSE c_get_def_status_code;
1265 END IF;
1266 ELSE
1267 l_status_code_def := NULL;
1268 END IF; -- p_status_code IS NULL
1269 --
1270 -- user trying to do a phase change
1271 --
1272 IF (NVL(l_phase_id_curr,-1) <> p_phase_id) THEN
1273 SELECT count(*)
1274 INTO l_pending_rec_count
1275 FROM mtl_pending_item_status
1276 WHERE inventory_item_id = p_inventory_item_id
1277 AND organization_id = p_organization_id
1278 AND pending_flag = 'Y'
1279 AND implemented_date IS NULL
1280 AND NVL(revision_id,-1) = NVL(l_revision_id,-1)
1281 AND lifecycle_id IS NOT NULL
1282 AND phase_id IS NOT NULL;
1283 IF l_pending_rec_count <> 0 THEN
1284 --
1285 -- pending chanes already exist
1286 --
1287 IF p_item_number IS NULL THEN
1288 SELECT concatenated_segments
1289 INTO l_dummy_char
1290 FROM mtl_system_items_b_kfv
1291 WHERE inventory_item_id = p_inventory_item_id
1292 AND organization_id = p_organization_id;
1293 ELSE
1294 l_dummy_char := p_item_number;
1295 END IF;
1296 IF l_revision_id IS NULL THEN
1297 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_PENDING_PHASE_CHANGE');
1298 fnd_message.Set_Token('ITEM_NUMBER', l_dummy_char);
1299 FND_MSG_PUB.Add;
1300 RAISE FND_API.G_EXC_ERROR;
1301 ELSE
1302 fnd_message.Set_Name(G_APP_NAME, 'EGO_REV_PENDING_PHASE_CHANGE');
1303 fnd_message.Set_Token('ITEM_NUMBER', l_dummy_char);
1304 fnd_message.Set_Token('REVISION', l_revision);
1305 FND_MSG_PUB.Add;
1306 RAISE FND_API.G_EXC_ERROR;
1307 END IF;
1308 END IF; -- l_pending_rec_count
1309 END IF; -- NVL(l_phase_id_curr,-1) <> p_phase_id
1310 ELSE
1311 l_phase_id := NULL;
1312 l_lifecycle_id := NULL;
1313 END IF; -- phase id is not null
1314 code_debug( l_api_name ||' no pending phase changes ');
1315
1316 IF l_revision IS NULL THEN
1317 -- check if the current status is valid
1318 OPEN c_validate_status_code (cp_phase_id => NVL(p_phase_id,l_phase_id_curr)
1319 ,cp_status_code => NVL(p_status_code,l_status_code_def)
1320 );
1321 FETCH c_validate_status_code INTO l_status_code;
1322 IF c_validate_status_code%NOTFOUND THEN
1323 CLOSE c_validate_status_code;
1324 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_INVALID_STATUS');
1325 fnd_message.Set_Token('STATUS', p_status_code);
1326 SELECT name
1327 INTO l_dummy_char
1328 FROM PA_EGO_PHASES_V
1329 WHERE proj_element_id = NVL(p_phase_id,l_phase_id_curr);
1330 fnd_message.Set_Token('PHASE', l_dummy_char);
1331 FND_MSG_PUB.Add;
1332 RAISE FND_API.G_EXC_ERROR;
1333 ELSE
1334 code_debug (l_api_name ||' checking for master controlled status ');
1335 l_is_master_org := get_master_org_status(p_organization_id);
1336 l_revision_master_controlled := FND_API.g_false;
1337 l_status_master_controlled := get_master_controlled_status();
1338 IF ('T' = l_status_master_controlled) AND NOT ('T' = l_is_master_org) THEN
1339 IF l_status_code_itm <> l_status_code THEN
1340 code_debug (l_api_name ||' status changes controlled at master cannot change ');
1341 fnd_message.Set_Name(G_APP_NAME,'EGO_ITEM_STATUS_MC');
1342 fnd_msg_pub.Add;
1343 RAISE FND_API.G_EXC_ERROR;
1344 END IF;
1345 END IF; -- status is master controlled and we are in context of child org
1346 END IF; -- status is valid
1347 ELSE
1348 -- context of revision
1349 l_status_code := NULL;
1350 END IF; -- revision IS NULL
1351 code_debug(' comparing values before insert p_lifecycle_id '||p_lifecycle_id ||' l_lifecycle_id '||l_lifecycle_id);
1352 --
1353 -- to be removed after bug 3874132 is resoloved.
1354 --
1355 l_sysdate := SYSDATE;
1356 INSERT INTO MTL_PENDING_ITEM_STATUS
1357 (
1358 inventory_item_id
1359 ,organization_id
1360 ,status_code
1361 ,effective_date
1362 ,implemented_date
1363 ,pending_flag
1364 ,last_update_date
1365 ,last_updated_by
1366 ,creation_date
1367 ,created_by
1368 ,last_update_login
1369 -- ,request_id
1370 -- ,program_update_date
1371 ,revision_id
1372 ,lifecycle_id
1373 ,phase_id
1374 ,change_id
1375 ,change_line_id
1376 )
1377 VALUES
1378 (
1379 p_inventory_item_id
1380 ,p_organization_id
1381 ,l_status_code
1382 ,NVL(p_effective_date,l_sysdate)
1383 ,NULL
1384 ,NVL(p_pending_flag,'Y')
1385 ,l_sysdate
1386 ,G_CURRENT_USER_ID
1387 ,l_sysdate
1388 ,G_CURRENT_USER_ID
1389 ,G_CURRENT_LOGIN_ID
1390 -- ,NULL
1391 -- ,l_sysdate
1392 ,l_revision_id
1393 ,l_lifecycle_id
1394 ,l_phase_id
1395 ,p_change_id
1396 ,p_change_line_id
1397 );
1398
1399 IF FND_API.To_Boolean(p_commit) THEN
1400 COMMIT WORK;
1401 END IF;
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403 EXCEPTION
1404 WHEN FND_API.G_EXC_ERROR THEN
1405 IF FND_API.To_Boolean( p_commit ) THEN
1406 ROLLBACK TO Create_Pending_Phase_Change_SP;
1407 END IF;
1408 IF c_get_rev_details%ISOPEN THEN
1409 CLOSE c_get_rev_details;
1410 END IF;
1411 IF c_get_item_details%ISOPEN THEN
1412 CLOSE c_get_item_details;
1413 END IF;
1414 IF c_get_def_status_code%ISOPEN THEN
1415 CLOSE c_get_def_status_code;
1416 END IF;
1417 IF c_validate_status_code%ISOPEN THEN
1418 CLOSE c_validate_status_code;
1419 END IF;
1420 x_return_status := FND_API.G_RET_STS_ERROR;
1421 WHEN OTHERS THEN
1422 IF FND_API.To_Boolean(p_commit) THEN
1423 ROLLBACK TO Create_Pending_Phase_Change_SP;
1424 END IF;
1425 IF c_get_rev_details%ISOPEN THEN
1426 CLOSE c_get_rev_details;
1427 END IF;
1428 IF c_get_item_details%ISOPEN THEN
1429 CLOSE c_get_item_details;
1430 END IF;
1431 IF c_get_def_status_code%ISOPEN THEN
1432 CLOSE c_get_def_status_code;
1433 END IF;
1434 IF c_validate_status_code%ISOPEN THEN
1435 CLOSE c_validate_status_code;
1436 END IF;
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1439 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1440 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1441 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1442 FND_MSG_PUB.Add;
1443 END Create_Pending_Phase_Change;
1444
1445
1446 -------------------------------------------------------
1447 PROCEDURE Modify_Pending_Phase_Change
1448 (p_api_version IN NUMBER
1449 ,p_commit IN VARCHAR2
1450 ,p_transaction_type IN VARCHAR2
1451 ,p_inventory_item_id IN NUMBER
1452 ,p_organization_id IN NUMBER
1453 ,p_revision_id IN NUMBER
1454 ,p_lifecycle_id IN NUMBER
1455 ,p_phase_id IN NUMBER
1456 ,p_status_code IN VARCHAR2
1457 ,p_change_id IN NUMBER
1458 ,p_change_line_id IN NUMBER
1459 ,p_effective_date IN DATE
1460 ,p_new_effective_date IN DATE
1461 ,p_perform_security_check IN VARCHAR2
1462 ,x_return_status OUT NOCOPY VARCHAR2
1463 ,x_errorcode OUT NOCOPY NUMBER
1464 ,x_msg_count OUT NOCOPY NUMBER
1465 ,x_msg_data OUT NOCOPY VARCHAR2
1466 ) IS
1467
1468 l_api_version NUMBER;
1469 l_api_name VARCHAR2(50);
1470 l_miss_num NUMBER;
1471 l_miss_char VARCHAR2(1);
1472 l_priv_name_to_check VARCHAR2(100);
1473
1474
1475 BEGIN
1476 l_api_version := 1.0;
1477 l_api_name := 'Modify_Pending_Phase_Change';
1478 l_miss_num := FND_API.G_MISS_NUM;
1479 l_miss_char := FND_API.G_MISS_CHAR;
1480
1481 IF FND_API.To_Boolean(p_commit) THEN
1482 SAVEPOINT Modify_Pending_Phase_Change_SP;
1483 END IF;
1484
1485 --Standard checks
1486 IF NOT FND_API.Compatible_API_Call (l_api_version
1487 ,p_api_version
1488 ,l_api_name
1489 ,g_pkg_name)
1490 THEN
1491 RAISE FND_API.G_EXC_ERROR;
1492 END IF;
1493
1494 IF Nvl(FND_GLOBAL.User_Id,-1) <> Nvl(G_CURRENT_USER_ID,-1)
1495 THEN
1496 G_CURRENT_USER_ID := FND_GLOBAL.User_Id;
1497 G_CURRENT_LOGIN_ID := FND_GLOBAL.Login_Id;
1498 END IF;
1499
1500
1501 IF ( p_inventory_item_id IS NULL
1502 OR
1503 p_organization_id IS NULL
1504 OR
1505 p_effective_date IS NULL
1506 OR
1507 p_transaction_type NOT IN (EGO_ITEM_PUB.G_TTYPE_UPDATE, EGO_ITEM_PUB.G_TTYPE_DELETE)
1508 ) THEN
1509 fnd_message.Set_Name(G_APP_NAME, 'EGO_API_INVALID_PARAMS');
1510 fnd_message.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
1511 fnd_message.Set_Token(G_API_NAME_TOKEN, l_api_name);
1512 FND_MSG_PUB.Add;
1513 RAISE FND_API.G_EXC_ERROR;
1514 END IF;
1515
1516 IF FND_API.To_Boolean(p_perform_security_check) THEN
1517 -- 4052565 perform security check
1518 l_priv_name_to_check := get_privlige_name_for_action
1519 (p_curr_item_id => p_inventory_item_id
1520 ,p_curr_org_id => p_organization_id
1521 ,p_curr_rev_id => p_revision_id
1522 ,p_curr_lc_id => NULL
1523 ,p_curr_phase_id => NULL
1524 ,p_curr_status_code => NULL
1525 ,p_new_lc_id => p_lifecycle_id
1526 ,p_new_phase_id => p_phase_id
1527 ,p_new_status_code => p_status_code
1528 );
1529 IF l_priv_name_to_check IS NOT NULL THEN
1530 IF NOT EGO_ITEM_PVT.has_role_on_item
1531 (p_function_name => l_priv_name_to_check
1532 ,p_inventory_item_id => p_inventory_item_id
1533 ,p_item_number => NULL
1534 ,p_organization_id => p_organization_id
1535 ,p_organization_name => NULL
1536 ,p_user_id => G_CURRENT_USER_ID
1537 ,p_party_id => NULL
1538 ,p_set_message => FND_API.G_TRUE
1539 ) THEN
1540 RAISE FND_API.G_EXC_ERROR;
1541 END IF;
1542 END IF;
1543 END IF;
1544
1545 IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
1546 --
1547 -- to be removed after bug 3874132 is resoloved.
1548 --
1549 code_debug(l_api_name|| ' Updating pending change record ');
1550 UPDATE mtl_pending_item_status
1551 SET effective_date = p_new_effective_date,
1552 last_update_date = SYSDATE,
1553 last_updated_by = G_CURRENT_USER_ID,
1554 last_update_login = G_CURRENT_LOGIN_ID
1555 WHERE inventory_item_id = p_inventory_item_id
1556 AND organization_id = p_organization_id
1557 AND NVL(revision_id,l_miss_num) = NVL(p_revision_id, l_miss_num)
1558 AND NVL(lifecycle_id, l_miss_num) = NVL(p_lifecycle_id, l_miss_num)
1559 AND NVL(phase_id, l_miss_num) = NVL(p_phase_id, l_miss_num)
1560 AND NVL(status_code,l_miss_char) = NVL(p_status_code, l_miss_char)
1561 AND NVL(p_change_id, l_miss_num) = NVL(p_change_id, l_miss_num)
1562 AND NVL(p_change_line_id, l_miss_num) = NVL(p_change_line_id, l_miss_num)
1563 AND effective_date = p_effective_date
1564 AND pending_flag = 'Y'
1565 AND implemented_date IS NULL;
1566 IF SQL%ROWCOUNT = 0 THEN
1567 code_debug(l_api_name|| ' cannot update record!! ');
1568 -- no records found for update
1569 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_REC_UPDATE');
1570 FND_MSG_PUB.Add;
1571 RAISE FND_API.G_EXC_ERROR;
1572 END IF;
1573
1574 ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE THEN
1575 --
1576 -- to be removed after bug 3874132 is resoloved.
1577 --
1578 code_debug(l_api_name|| ' Deleting pending change record ');
1579 DELETE mtl_pending_item_status
1580 WHERE inventory_item_id = p_inventory_item_id
1581 AND organization_id = p_organization_id
1582 AND NVL(revision_id,l_miss_num) = NVL(p_revision_id, l_miss_num)
1583 AND NVL(lifecycle_id, l_miss_num) = NVL(p_lifecycle_id, l_miss_num)
1584 AND NVL(phase_id, l_miss_num) = NVL(p_phase_id, l_miss_num)
1585 AND NVL(status_code,l_miss_char) = NVL(p_status_code, l_miss_char)
1586 AND NVL(p_change_id, l_miss_num) = NVL(p_change_id, l_miss_num)
1587 AND NVL(p_change_line_id, l_miss_num) = NVL(p_change_line_id, l_miss_num)
1588 AND effective_date = p_effective_date
1589 AND pending_flag = 'Y'
1590 AND implemented_date IS NULL;
1591 IF SQL%ROWCOUNT = 0 THEN
1592 -- no records found for delete
1593 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_REC_DELETE');
1594 FND_MSG_PUB.Add;
1595 RAISE FND_API.G_EXC_ERROR;
1596 END IF;
1597
1598 END IF;
1599
1600 EXCEPTION
1601 WHEN FND_API.G_EXC_ERROR THEN
1602 IF FND_API.To_Boolean( p_commit ) THEN
1603 ROLLBACK TO Create_Pending_Phase_Change_SP;
1604 END IF;
1605 x_return_status := FND_API.G_RET_STS_ERROR;
1606
1607 END Modify_Pending_Phase_change;
1608
1609
1610
1611 /***
1612 PROCEDURE Delete_Pending_Phase_Change
1613 (
1614 p_api_version IN NUMBER
1615 ,p_commit IN VARCHAR2
1616 ,p_inventory_item_id IN NUMBER
1617 ,p_organization_id IN NUMBER
1618 ,p_change_id IN NUMBER
1619 ,p_change_line_id IN NUMBER
1620 ,x_return_status OUT NOCOPY VARCHAR2
1621 ,x_errorcode OUT NOCOPY NUMBER
1622 ,x_msg_count OUT NOCOPY NUMBER
1623 ,x_msg_data OUT NOCOPY VARCHAR2
1624 ) IS
1625
1626 l_api_version NUMBER;
1627 l_api_name VARCHAR2(50);
1628
1629 BEGIN
1630 l_api_version := 1.0;
1631 l_api_name := 'Delete_Pending_Phase_Change';
1632 --Standard checks
1633 IF NOT FND_API.Compatible_API_Call (l_api_version
1634 ,p_api_version
1635 ,l_api_name
1636 ,g_pkg_name)
1637 THEN
1638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1639 END IF;
1640
1641 IF ( (p_inventory_item_id IS NULL
1642 AND
1643 p_change_id IS NULL
1644 AND
1645 p_change_line_id IS NULL
1646 )
1647 OR
1648 p_organization_id IS NULL
1649 ) THEN
1650 x_return_status := FND_API.G_RET_STS_ERROR;
1651 RETURN;
1652 END IF;
1653
1654 IF FND_API.To_Boolean(p_commit) THEN
1655 SAVEPOINT Delete_Pending_Phase_Change_SP;
1656 END IF;
1657
1658 IF p_change_id IS NOT NULL THEN
1659 IF p_change_line_id IS NOT NULL THEN
1660 IF p_inventory_item_id IS NOT NULL THEN
1661 --
1662 -- change_id, change_line_id, inventory_item_id present
1663 --
1664 DELETE MTL_PENDING_ITEM_STATUS
1665 WHERE organization_id = p_organization_id
1666 AND inventory_item_id = p_inventory_item_id
1667 AND change_id = p_change_id
1668 AND change_line_id = p_change_line_id
1669 AND implemented_date IS NULL
1670 AND pending_flag = 'Y';
1671 ELSE
1672 --
1673 -- change_id, change_line_id present
1674 --
1675 DELETE MTL_PENDING_ITEM_STATUS
1676 WHERE organization_id = p_organization_id
1677 AND change_id = p_change_id
1678 AND change_line_id = p_change_line_id
1679 AND implemented_date IS NULL
1680 AND pending_flag = 'Y';
1681 END IF;
1682 ELSE -- change line id is null
1683 IF p_inventory_item_id IS NOT NULL THEN
1684 --
1685 -- change_id, inventory_item_id present
1686 --
1687 DELETE MTL_PENDING_ITEM_STATUS
1688 WHERE organization_id = p_organization_id
1689 AND inventory_item_id = p_inventory_item_id
1690 AND change_id = p_change_id
1691 AND implemented_date IS NULL
1692 AND pending_flag = 'Y';
1693 ELSE
1694 --
1695 -- only change_id present
1696 --
1697 DELETE MTL_PENDING_ITEM_STATUS
1698 WHERE organization_id = p_organization_id
1699 AND change_id = p_change_id
1700 AND implemented_date IS NULL
1701 AND pending_flag = 'Y';
1702 END IF;
1703 END IF;
1704 ELSE -- change id is null
1705 IF p_change_line_id IS NOT NULL THEN
1706 IF p_inventory_item_id IS NOT NULL THEN
1707 --
1708 -- change_line_id, inventory_item_id present
1709 --
1710 DELETE MTL_PENDING_ITEM_STATUS
1711 WHERE organization_id = p_organization_id
1712 AND inventory_item_id = p_inventory_item_id
1713 AND change_line_id = p_change_line_id
1714 AND implemented_date IS NULL
1715 AND pending_flag = 'Y';
1716 ELSE
1717 --
1718 -- only change_line_id present
1719 --
1720 DELETE MTL_PENDING_ITEM_STATUS
1721 WHERE organization_id = p_organization_id
1722 AND change_line_id = p_change_line_id
1723 AND implemented_date IS NULL
1724 AND pending_flag = 'Y';
1725 END IF;
1726 ELSE -- change line id is null
1727 IF p_inventory_item_id IS NOT NULL THEN
1728 --
1729 -- only inventory_item_id present
1730 -- delete where change_id and change_line_id are null
1731 --
1732 DELETE MTL_PENDING_ITEM_STATUS
1733 WHERE organization_id = p_organization_id
1734 AND inventory_item_id = p_inventory_item_id
1735 AND change_id IS NULL
1736 AND change_line_id IS NULL
1737 AND implemented_date IS NULL
1738 AND pending_flag = 'Y';
1739 END IF;
1740 END IF;
1741 END IF;
1742
1743 IF FND_API.To_Boolean(p_commit) THEN
1744 COMMIT WORK;
1745 END IF;
1746 x_return_status := FND_API.G_RET_STS_SUCCESS;
1747
1748 EXCEPTION
1749 WHEN OTHERS THEN
1750 IF FND_API.To_Boolean(p_commit) THEN
1751 ROLLBACK TO Delete_Pending_Phase_Change_SP;
1752 END IF;
1753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1754 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1755 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1756 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1757 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1758 FND_MSG_PUB.Add;
1759
1760 END Delete_Pending_Phase_Change;
1761 ***/
1762
1763 PROCEDURE Implement_Pending_Changes
1764 (
1765 p_api_version IN NUMBER
1766 , p_inventory_item_id IN NUMBER
1767 , p_organization_id IN NUMBER
1768 , p_revision_id IN NUMBER
1769 , p_revision_master_controlled IN VARCHAR2
1770 , p_status_master_controlled IN VARCHAR2
1771 , p_is_master_org IN VARCHAR2
1772 , p_perform_security_check IN VARCHAR2 DEFAULT 'F'
1773 , x_return_status OUT NOCOPY VARCHAR2
1774 , x_errorcode OUT NOCOPY NUMBER
1775 , x_msg_count OUT NOCOPY NUMBER
1776 , x_msg_data OUT NOCOPY VARCHAR2
1777 ) IS
1778
1779 l_commit VARCHAR2(1);
1780
1781 BEGIN
1782 --
1783 -- existing functionality is doing a commit always
1784 --
1785 code_debug(' Implement Pending Changes from Projects area item id '||p_inventory_item_id||' org id '||p_organization_id||' rev id '||p_revision_id);
1786 l_commit := FND_API.G_TRUE;
1787 Implement_All_Pending_Changes
1788 (p_api_version => p_api_version
1789 ,p_commit => l_commit
1790 ,p_inventory_item_id => p_inventory_item_id
1791 ,p_organization_id => p_organization_id
1792 ,p_revision_id => p_revision_id
1793 ,p_change_id => NULL
1794 ,p_change_line_id => NULL
1795 ,p_revision_master_controlled => p_revision_master_controlled
1796 ,p_status_master_controlled => p_status_master_controlled
1797 ,p_is_master_org => p_is_master_org
1798 ,p_perform_security_check => p_perform_security_check
1799 ,x_return_status => x_return_status
1800 ,x_errorcode => x_errorcode
1801 ,x_msg_count => x_msg_count
1802 ,x_msg_data => x_msg_data
1803 );
1804
1805 END Implement_Pending_Changes;
1806
1807 --
1808 -- Created as a part of Fix for 3371749
1809 --
1810 PROCEDURE Implement_Pending_Changes
1811 (
1812 p_api_version IN NUMBER
1813 , p_commit IN VARCHAR2
1814 , p_change_id IN NUMBER
1815 , p_change_line_id IN NUMBER
1816 , p_perform_security_check IN VARCHAR2 DEFAULT 'F'
1817 , x_return_status OUT NOCOPY VARCHAR2
1818 , x_errorcode OUT NOCOPY NUMBER
1819 , x_msg_count OUT NOCOPY NUMBER
1820 , x_msg_data OUT NOCOPY VARCHAR2
1821 )
1822 IS
1823
1824 l_api_version NUMBER;
1825 l_api_name VARCHAR2(50);
1826
1827 l_revision_master_controlled VARCHAR2(1);
1828 l_status_master_controlled VARCHAR2(1);
1829 l_is_master_org VARCHAR2(1);
1830
1831 CURSOR c_get_pending_items (cp_change_id IN NUMBER
1832 ,cp_change_line_id IN NUMBER) IS
1833 SELECT *
1834 FROM mtl_pending_item_status
1835 WHERE implemented_date IS NULL
1836 AND pending_flag = 'Y'
1837 AND change_id = NVL(cp_change_id, change_id)
1838 AND change_line_id = NVL(cp_change_line_id, change_line_id);
1839
1840 BEGIN
1841
1842 l_api_version := 1.0;
1843 l_api_name := 'Implement_Pending_Changes';
1844 code_debug(' Implement Pending Changes from Change area ');
1845 --Standard checks
1846 IF NOT FND_API.Compatible_API_Call (l_api_version
1847 ,p_api_version
1848 ,l_api_name
1849 ,g_pkg_name)
1850 THEN
1851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1852 END IF;
1853
1854 IF (p_change_id IS NULL AND p_change_line_id IS NULL) THEN
1855 FND_MESSAGE.Set_name(G_APP_NAME, 'EGO_API_INVALID_PARAMS');
1856 FND_MESSAGE.Set_token('PKG_NAME', G_PKG_NAME);
1857 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
1858 x_msg_data := FND_MESSAGE.get();
1859 x_msg_count := 1;
1860 x_return_status := FND_API.G_RET_STS_ERROR;
1861 RETURN;
1862 END IF;
1863
1864 IF FND_API.To_Boolean(p_commit) THEN
1865 SAVEPOINT Implement_Pending_Changes_SP;
1866 END IF;
1867
1868 l_revision_master_controlled := FND_API.g_false;
1869 l_status_master_controlled := EGO_ITEM_LC_IMP_PC_PUB.get_master_controlled_status();
1870
1871 FOR l_item_record IN
1872 c_get_pending_items (cp_change_id => p_change_id
1873 ,cp_change_line_id => p_change_line_id)
1874 LOOP
1875
1876 l_is_master_org := get_master_org_status(l_item_record.ORGANIZATION_ID);
1877 Implement_All_Pending_Changes
1878 (p_api_version => p_api_version
1879 ,p_commit => FND_API.G_FALSE
1880 ,p_inventory_item_id => l_item_record.INVENTORY_ITEM_ID
1881 ,p_organization_id => l_item_record.ORGANIZATION_ID
1882 ,p_revision_id => l_item_record.REVISION_ID
1883 ,p_change_id => p_change_id
1884 ,p_change_line_id => p_change_line_id
1885 ,p_revision_master_controlled => l_revision_master_controlled
1886 ,p_status_master_controlled => l_status_master_controlled
1887 ,p_is_master_org => l_is_master_org
1888 ,p_perform_security_check => p_perform_security_check
1889 ,x_return_status => x_return_status
1890 ,x_errorcode => x_errorcode
1891 ,x_msg_count => x_msg_count
1892 ,x_msg_data => x_msg_data
1893 );
1894 EXIT WHEN x_return_status <> FND_API.G_RET_STS_SUCCESS;
1895 END LOOP;
1896
1897 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1898 IF FND_API.To_Boolean(p_commit) THEN
1899 COMMIT WORK;
1900 END IF;
1901 ELSE
1902 IF x_msg_count <>1 THEN
1903 FND_MSG_PUB.Count_And_Get(
1904 p_encoded => FND_API.G_FALSE,
1905 p_count => x_msg_count,
1906 p_data => x_msg_data
1907 );
1908 END IF;
1909 END IF;
1910
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 IF c_get_pending_items%ISOPEN THEN
1914 CLOSE c_get_pending_items;
1915 END IF;
1916 IF FND_API.To_Boolean(p_commit) THEN
1917 ROLLBACK TO Implement_Pending_Changes_SP;
1918 END IF;
1919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1920 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1921 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1922 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1923 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1924 FND_MSG_PUB.Add;
1925 FND_MSG_PUB.Count_And_Get(
1926 p_encoded => FND_API.G_FALSE,
1927 p_count => x_msg_count,
1928 p_data => x_msg_data
1929 );
1930
1931 END Implement_Pending_Changes;
1932
1933
1934 PROCEDURE Implement_Pending_Changes_CP
1935 (
1936 ERRBUF OUT NOCOPY VARCHAR2
1937 , RETCODE OUT NOCOPY NUMBER
1938 , p_organization_id IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE
1939 , p_inventory_item_id IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE
1940 , p_revision_code IN MTL_ITEM_REVISIONS_B.REVISION%TYPE
1941 )
1942 IS
1943
1944 TYPE ORG_TABLE IS TABLE OF MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
1945 TYPE ITEM_TABLE IS TABLE OF MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
1946 TYPE REV_TABLE IS TABLE OF MTL_ITEM_REVISIONS_B.REVISION%TYPE;
1947
1948 l_revision_master_controlled VARCHAR2(1);
1949 l_status_master_controlled VARCHAR2(1);
1950 l_is_master_org VARCHAR2(1);
1951
1952 l_return_status VARCHAR2(1);
1953 l_errorcode NUMBER;
1954 l_msg_count NUMBER;
1955 l_msg_data VARCHAR2(4000);
1956
1957 l_orgs ORG_TABLE;
1958 l_items ITEM_TABLE;
1959 l_revs REV_TABLE;
1960 l_revision_id MTL_ITEM_REVISIONS_B.REVISION_ID%TYPE;
1961 l_master_org MTL_ITEM_REVISIONS_B.ORGANIZATION_ID%TYPE;
1962
1963 l_ret VARCHAR2(1);
1964 l_error_mesg VARCHAR2(4000);
1965
1966
1967 BEGIN
1968
1969 code_debug(' Implement Pending Changes from Concurrent Program ');
1970 SELECT
1971 DECODE(LOOKUP_CODE2, 1, FND_API.G_TRUE, 2, FND_API.G_FALSE, FND_API.G_FALSE) INTO l_status_master_controlled
1972 FROM
1973 MTL_ITEM_ATTRIBUTES_V
1974 WHERE
1975 ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
1976
1977 l_revision_master_controlled := FND_API.G_FALSE;
1978
1979 --If there is no organization id, then do everything
1980 IF p_organization_id IS NULL
1981 THEN
1982
1983 SELECT
1984 ORGANIZATION_ID BULK COLLECT INTO l_orgs
1985 FROM
1986 ORG_ACCESS_VIEW
1987 WHERE RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
1988 AND RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID;
1989
1990 ELSE
1991
1992 l_orgs := ORG_TABLE(p_organization_id);
1993
1994 END IF;
1995
1996 --For each org
1997 IF (l_orgs IS NOT NULL AND l_orgs.COUNT > 0)
1998 THEN
1999
2000 FOR org_index IN l_orgs.FIRST..l_orgs.LAST
2001 LOOP
2002
2003 BEGIN
2004
2005 --Select the master org
2006 SELECT
2007 MP.MASTER_ORGANIZATION_ID INTO l_master_org
2008 FROM
2009 MTL_PARAMETERS MP
2010 WHERE
2011 MP.ORGANIZATION_ID = l_orgs(org_index);
2012
2013 l_is_master_org := FND_API.G_FALSE;
2014
2015 IF l_master_org = l_orgs(org_index)
2016 THEN
2017 l_is_master_org := FND_API.G_TRUE;
2018 END IF;
2019
2020 --If item is null, then get them all
2021 IF p_inventory_item_id IS NULL
2022 THEN
2023
2024 SELECT
2025 INVENTORY_ITEM_ID BULK COLLECT INTO l_items
2026 FROM
2027 MTL_SYSTEM_ITEMS_B
2028 WHERE
2029 ORGANIZATION_ID = l_orgs(org_index);
2030
2031 ELSE
2032
2033 l_items := ITEM_TABLE(p_inventory_item_id);
2034
2035 END IF;
2036
2037 IF (l_items IS NOT NULL AND l_items.COUNT > 0)
2038 THEN
2039
2040 --For each item
2041 FOR item_index IN l_items.FIRST..l_items.LAST
2042 LOOP
2043
2044 BEGIN
2045
2046 IF p_revision_code = 'ALL' OR p_revision_code is NULL
2047 THEN
2048
2049 SELECT
2050 REVISION BULK COLLECT INTO l_revs
2051 FROM
2052 MTL_ITEM_REVISIONS_B
2053 WHERE ORGANIZATION_ID = l_orgs(org_index)
2054 AND INVENTORY_ITEM_ID = l_items(item_index);
2055
2056 --If it's all, then also add a null entry to mean no revision
2057 --(NULL will do the work of 'ALL' plus 'NONE')
2058 l_revs.EXTEND();
2059
2060 ELSIF p_revision_code = 'NONE'
2061 THEN
2062
2063 l_revs := REV_TABLE(NULL);
2064
2065 ELSE
2066
2067 l_revs := REV_TABLE(p_revision_code);
2068
2069 END IF;
2070
2071 IF (l_revs IS NOT NULL AND l_revs.COUNT > 0)
2072 THEN
2073
2074 FOR rev_index IN l_revs.FIRST..l_revs.LAST
2075 LOOP
2076
2077 BEGIN
2078
2079 --First we need to get the revision_id
2080 l_revision_id := NULL;
2081
2082 IF l_revs(rev_index) IS NOT NULL
2083 THEN
2084
2085 --Either the master or current
2086 IF FND_API.To_Boolean(l_revision_master_controlled)
2087 THEN
2088
2089 SELECT
2090 REVISION_ID INTO l_revision_id
2091 FROM
2092 MTL_ITEM_REVISIONS_B
2093 WHERE
2094 ORGANIZATION_ID = l_master_org
2095 AND INVENTORY_ITEM_ID = l_items(item_index)
2096 AND REVISION = l_revs(rev_index);
2097
2098 ELSE
2099
2100 SELECT
2101 REVISION_ID INTO l_revision_id
2102 FROM
2103 MTL_ITEM_REVISIONS_B
2104 WHERE
2105 ORGANIZATION_ID = l_orgs(org_index)
2106 AND INVENTORY_ITEM_ID = l_items(item_index)
2107 AND REVISION = l_revs(rev_index);
2108
2109 END IF; -- select rev id
2110
2111 END IF; -- if revision is not null
2112
2113 Implement_Pending_Changes
2114 (
2115 p_api_version => 1.0
2116 , p_inventory_item_id => l_items(item_index)
2117 , p_organization_id => l_orgs(org_index)
2118 , p_revision_id => l_revision_id
2119 , p_revision_master_controlled => l_revision_master_controlled
2120 , p_status_master_controlled => l_status_master_controlled
2121 , p_is_master_org => l_is_master_org
2122 , p_perform_security_check => FND_API.G_FALSE
2123 , x_return_status => l_return_status
2124 , x_errorcode => l_errorcode
2125 , x_msg_count => l_msg_count
2126 , x_msg_data => l_msg_data
2127 );
2128
2129 EXCEPTION
2130
2131 WHEN OTHERS
2132 THEN
2133 NULL;
2134
2135 END; -- rev block
2136
2137 END LOOP; -- for each rev
2138
2139 END IF;
2140
2141 EXCEPTION
2142
2143 WHEN OTHERS
2144 THEN
2145 NULL;
2146
2147 END; -- item block
2148
2149 END LOOP; -- for each item
2150
2151 END IF; -- if there are any items
2152
2153 EXCEPTION
2154
2155 WHEN OTHERS
2156 THEN
2157 NULL;
2158
2159 END; -- org block
2160
2161 END LOOP; -- for each org
2162
2163 END IF; -- if there are any orgs
2164
2165 RETCODE := G_SUCCESS;
2166 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_IPC_SUCCESS');
2167
2168 END Implement_Pending_Changes_CP;
2169
2170 END EGO_ITEM_LC_IMP_PC_PUB;
2171