1 PACKAGE BODY EGO_ITEM_LC_IMP_PC_PUB AS
2 /* $Header: EGOCIPSB.pls 120.6.12020000.2 2012/07/13 01:27:02 mshirkol 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
483 -- Bug 13495593
484 --IF FND_API.To_Boolean(p_commit) THEN
485 SAVEPOINT Implement_All_Pending_Changes;
486 --END IF;
487
488 --Standard checks
489 IF NOT FND_API.Compatible_API_Call (l_api_version
490 ,p_api_version
491 ,l_api_name
492 ,g_pkg_name)
493 THEN
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 END IF;
496
497 IF Nvl(FND_GLOBAL.User_Id,-1) <> Nvl(G_CURRENT_USER_ID,-1)
498 THEN
499 G_CURRENT_USER_ID := FND_GLOBAL.User_Id;
500 G_CURRENT_LOGIN_ID := FND_GLOBAL.Login_Id;
501 END IF;
502
503 --
504 -- replaced call to EGO_INV_ITEM_CATALOG_PVT.Check_pending_Change_Orders
505 -- as a part of bug 3696801
506 -- Check_Pending_Change_Orders (p_inventory_item_id => p_inventory_item_id
507 -- ,p_organization_id => p_organization_id
508 -- ,p_revision_id => p_revision_id
509 -- ,x_return_status => x_return_status
510 -- ,x_msg_data => x_msg_data
511 -- );
512 -- bug 3833932 doing pending phase change only on the corresponding item/rev
513 IF p_revision_id IS NULL THEN
514 OPEN c_item_pending_phase_change;
515 FETCH c_item_pending_phase_change INTO l_current_Phase_id;
516 IF c_item_pending_phase_change%FOUND THEN
517 l_perform_policy_check := TRUE;
518 END IF;
519 CLOSE c_item_pending_phase_change;
520 ELSE
521 OPEN c_rev_pending_phase_change;
522 FETCH c_rev_pending_phase_change INTO l_current_Phase_id;
523 IF c_rev_pending_phase_change%FOUND THEN
524 l_perform_policy_check := TRUE;
525 END IF;
526 CLOSE c_rev_pending_phase_change;
527 END IF;
528
529 IF l_perform_policy_check THEN
530 code_debug (' performing policy check ');
531 EGO_INV_ITEM_CATALOG_PVT.Check_pending_Change_Orders (
532 p_inventory_item_id => p_inventory_item_id
533 ,p_organization_id => p_organization_id
534 ,p_revision_id => p_revision_id
535 ,p_lifecycle_changed => FND_API.G_FALSE
536 ,p_lifecycle_phase_changed => FND_API.G_TRUE
537 ,p_change_id => p_change_id
538 ,p_change_line_id => p_change_line_id
539 ,x_return_status => x_return_status
540 ,x_msg_count => x_msg_count
541 ,x_msg_data => x_msg_data
542 );
543 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
544 code_debug (' pending co exist '|| x_msg_data);
545 IF FND_API.To_Boolean(p_commit) THEN
546 ROLLBACK TO Implement_All_Pending_Changes;
547 END IF;
548 RETURN;
549 END IF;
550 END IF;
551
552 code_debug (' no pending change orders exist ');
553
554 IF FND_API.TO_BOOLEAN(p_is_master_org) THEN
555 code_debug (' in context of master org ');
556 ELSE
557 code_debug (' in context of child org ');
558 END IF;
559
560 IF FND_API.TO_BOOLEAN(p_status_master_controlled) THEN
561 code_debug (' status is master controlled ');
562 ELSE
563 code_debug (' status is controlled at org level ');
564 END IF;
565
566 --
567 -- to be removed after bug 3874132 is resoloved.
568 --
569 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
570 IF FND_API.To_Boolean( p_commit ) THEN
571 ROLLBACK TO Implement_All_Pending_Changes;
572 END IF;
573 RETURN;
574 END IF;
575
576 IF (p_revision_id IS NULL) THEN
577
578 code_debug (' processing changes for item ');
579 code_debug ('p_revision_id is null ');
580
581 --
582 -- If it's at the master and status is master controlled,
583 -- then do it for all assigned orgs
584 --
585 IF (FND_API.TO_BOOLEAN(p_is_master_org)
586 OR
587 FND_API.TO_BOOLEAN(p_status_master_controlled) = FALSE
588 ) THEN
589 IF (FND_API.TO_BOOLEAN(p_is_master_org)
590 AND
591 FND_API.TO_BOOLEAN(p_status_master_controlled) = TRUE
592 ) THEN
593 -- Get the most recent phase id
594 code_debug(' before the loop' );
595
596 FOR l_phase_id IN l_phase_ids LOOP
597 IF FND_API.To_boolean(p_perform_security_check) THEN
598 -- 4052565 perform security check
599 l_priv_name_to_check := get_privlige_name_for_action
600 (p_curr_item_id => p_inventory_item_id
601 ,p_curr_org_id => p_organization_id
602 ,p_curr_rev_id => p_revision_id
603 ,p_curr_lc_id => NULL
604 ,p_curr_phase_id => NULL
605 ,p_curr_status_code => NULL
606 ,p_new_lc_id => l_phase_id.lifecycle_id
607 ,p_new_phase_id => l_phase_id.phase_id
608 ,p_new_status_code => l_phase_id.status_code
609 );
610 IF l_priv_name_to_check IS NOT NULL THEN
611 IF NOT EGO_ITEM_PVT.has_role_on_item
612 (p_function_name => l_priv_name_to_check
613 ,p_inventory_item_id => p_inventory_item_id
614 ,p_item_number => NULL
615 ,p_organization_id => p_organization_id
616 ,p_organization_name => NULL
617 ,p_user_id => G_CURRENT_USER_ID
618 ,p_party_id => NULL
619 ,p_set_message => FND_API.G_TRUE
620 ) THEN
621 RAISE FND_API.G_EXC_ERROR;
622 END IF;
623 END IF;
624 END IF;
625
626 code_debug(' before check_floating_attachments 1');
627 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => p_inventory_item_id
628 ,p_revision_id => p_revision_id
629 ,p_organization_id => p_organization_id
630 ,p_lifecycle_id => NULL
631 ,p_new_phase_id => l_phase_id.phase_id
632 ,x_return_status => x_return_status
633 ,x_msg_count => x_msg_count
634 ,x_msg_data => x_msg_data );
635
636 code_debug(' after check_floating_attachments 1 ' || x_return_status);
637 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
638 IF FND_API.To_Boolean( p_commit ) THEN
639 ROLLBACK TO Implement_All_Pending_Changes;
640 END IF;
641 RETURN;
642 END IF;
643 UPDATE MTL_SYSTEM_ITEMS_B
644 SET CURRENT_PHASE_ID = NVL(l_phase_id.PHASE_ID,current_phase_id)
645 WHERE
646 INVENTORY_ITEM_ID = p_inventory_item_id
647 AND ORGANIZATION_ID IN
648 (SELECT P2.ORGANIZATION_ID
649 FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
650 WHERE P1.ORGANIZATION_ID = p_organization_id
651 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID);
652 l_phase_update := 'Y';
653 END LOOP;
654 code_debug(' out side the loop ');
655 --
656 -- Otherwise, either we are not at master or not master controlled.
657 -- So if not master controlled, do it for the current org
658 --
659 ELSIF (FND_API.TO_BOOLEAN(p_status_master_controlled) = FALSE) THEN
660 --
661 -- Get the most recent phase id
662 --
663 FOR l_phase_id IN l_phase_ids LOOP
664
665 IF FND_API.To_boolean(p_perform_security_check) THEN
666 -- 4052565 perform security check
667 l_priv_name_to_check := get_privlige_name_for_action
668 (p_curr_item_id => p_inventory_item_id
669 ,p_curr_org_id => p_organization_id
670 ,p_curr_rev_id => p_revision_id
671 ,p_curr_lc_id => NULL
672 ,p_curr_phase_id => NULL
673 ,p_curr_status_code => NULL
674 ,p_new_lc_id => l_phase_id.lifecycle_id
675 ,p_new_phase_id => l_phase_id.phase_id
676 ,p_new_status_code => l_phase_id.status_code
677 );
678 IF l_priv_name_to_check IS NOT NULL THEN
679 IF NOT EGO_ITEM_PVT.has_role_on_item
680 (p_function_name => l_priv_name_to_check
681 ,p_inventory_item_id => p_inventory_item_id
682 ,p_item_number => NULL
683 ,p_organization_id => p_organization_id
684 ,p_organization_name => NULL
685 ,p_user_id => G_CURRENT_USER_ID
686 ,p_party_id => NULL
687 ,p_set_message => FND_API.G_TRUE
688 ) THEN
689 RAISE FND_API.G_EXC_ERROR;
690 END IF;
691 END IF;
692 END IF;
693
694 code_debug(' before check_floating_attachments 2');
695 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => p_inventory_item_id
696 ,p_revision_id => p_revision_id
697 ,p_organization_id => p_organization_id
698 ,p_lifecycle_id => NULL
699 ,p_new_phase_id => l_phase_id.phase_id
700 ,x_return_status => x_return_status
701 ,x_msg_count => x_msg_count
702 ,x_msg_data => x_msg_data );
703
704 code_debug(' after check_floating_attachments 2 ' || x_return_status);
705 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
706 IF FND_API.To_Boolean( p_commit ) THEN
707 ROLLBACK TO Implement_All_Pending_Changes;
708 END IF;
709 RETURN;
710 END IF;
711
712 UPDATE MTL_SYSTEM_ITEMS_B
713 SET CURRENT_PHASE_ID = NVL(l_phase_id.PHASE_ID, current_phase_id)
714 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
715 AND ORGANIZATION_ID = p_organization_id;
716 l_phase_update := 'Y';
717 END LOOP;
718 END IF;
719 --
720 -- Now call another api to update statuses,
721 -- but only if we are at the master org or status is org controlled
722 --
723 ---Start 4105841 Business events
724 SELECT inventory_item_status_code INTO l_old_status
725 FROM mtl_system_items_b msi
726 WHERE msi.inventory_item_id = p_inventory_item_id
727 AND msi.organization_id = p_organization_id
728 AND rownum < 2;
729 ---End 4105841
730
731 INV_ITEM_STATUS_PUB.Update_Pending_Status (1.0
732 ,p_organization_id
733 ,p_inventory_item_id
734 ,NULL
735 ,NULL
736 ,x_return_status
737 ,x_msg_count
738 ,x_msg_data
739 );
740 --Added for bug 5230594
741 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
742 ROLLBACK TO Implement_All_Pending_Changes;
743 RETURN;
744 END IF;
745
746
747 ---Start 4105841 Business events
748 SELECT inventory_item_status_code INTO l_new_status
749 FROM mtl_system_items_b msi
750 WHERE msi.inventory_item_id = p_inventory_item_id
751 AND msi.organization_id = p_organization_id
752 AND rownum < 2;
753
754 IF l_phase_update = 'Y' OR
755 NVL(l_old_status,-1) <> NVL(l_new_status,-1) THEN
756 FOR Item_Rec IN c_get_item_details(p_inventory_item_id
757 ,p_organization_id) LOOP
758 EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
759 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
760 ,p_organization_id => p_organization_id
761 ,p_organization_code => Item_Rec.organization_code
762 ,p_item_number => Item_Rec.concatenated_segments
763 ,p_item_description => Item_Rec.DESCRIPTION
764 ,p_inventory_item_id => p_inventory_item_id
765 ,x_msg_data => l_msg_data
766 ,x_return_status => l_event_return_status);
767
768 --Call ICX APIs
769 BEGIN
770 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
771 p_entity_type => 'ITEM'
772 ,p_dml_type => 'UPDATE'
773 ,p_inventory_item_id => p_inventory_item_id
774 ,p_item_number => Item_Rec.concatenated_segments
775 ,p_item_description => Item_Rec.DESCRIPTION
776 ,p_organization_id => p_organization_id
777 ,p_organization_code => Item_Rec.organization_code );
778 EXCEPTION
779 WHEN OTHERS THEN
780 NULL;
781 END;
782 --R12: Business Event Enhancement
783 END LOOP;
784 END IF;
785 --End 4105841 Business Event
786
787 END IF;
788
789 ELSE
790 code_debug (' processing changes for item revision ');
791 --
792 -- Get all of the pending records
793 --
794 FOR l_pending_record IN l_pending_revision_statuses LOOP
795 code_debug (' processing revision '||l_pending_record.REVISION_ID);
796 IF NVL(p_perform_security_check,FND_API.G_FALSE) = FND_API.G_TRUE THEN
797 -- 4052565 perform security check
798 l_priv_name_to_check := get_privlige_name_for_action
799 (p_curr_item_id => p_inventory_item_id
800 ,p_curr_org_id => p_organization_id
801 ,p_curr_rev_id => p_revision_id
802 ,p_curr_lc_id => NULL
803 ,p_curr_phase_id => NULL
804 ,p_curr_status_code => NULL
805 ,p_new_lc_id => l_pending_record.lifecycle_id
806 ,p_new_phase_id => l_pending_record.phase_id
807 ,p_new_status_code => l_pending_record.status_code
808 );
809 IF l_priv_name_to_check IS NOT NULL THEN
810 IF NOT EGO_ITEM_PVT.has_role_on_item
811 (p_function_name => l_priv_name_to_check
812 ,p_inventory_item_id => p_inventory_item_id
813 ,p_item_number => NULL
814 ,p_organization_id => p_organization_id
815 ,p_organization_name => NULL
816 ,p_user_id => G_CURRENT_USER_ID
817 ,p_party_id => NULL
818 ,p_set_message => FND_API.G_TRUE
819 ) THEN
820 RAISE FND_API.G_EXC_ERROR;
821 END IF;
822 END IF;
823 END IF;
824
825 IF l_pending_record.PHASE_ID IS NOT NULL THEN
826 --
827 -- If master controlled and we are at master
828 --
829 IF (FND_API.TO_BOOLEAN(p_revision_master_controlled)
830 AND
831 FND_API.TO_BOOLEAN(p_is_master_org)
832 ) THEN
833 code_debug (' rev is master controlled and we are at master org ');
834 --
835 -- First get the revision code
836 --
837 SELECT REVISION INTO l_current_revision
838 FROM MTL_ITEM_REVISIONS_B
839 WHERE
840 INVENTORY_ITEM_ID = l_pending_record.INVENTORY_ITEM_ID
841 AND REVISION_ID = l_pending_record.REVISION_ID
842 AND ORGANIZATION_ID = l_pending_record.ORGANIZATION_ID;
843 --
844 -- Update for all orgs
845 --
846
847 code_debug(' before check_floating_attachments 3');
848 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => l_pending_record.INVENTORY_ITEM_ID
849 ,p_revision_id => l_pending_record.REVISION_ID
850 ,p_organization_id => l_pending_record.ORGANIZATION_ID
851 ,p_lifecycle_id => NULL
852 ,p_new_phase_id => l_pending_record.PHASE_ID
853 ,x_return_status => x_return_status
854 ,x_msg_count => x_msg_count
855 ,x_msg_data => x_msg_data );
856
857 code_debug(' after check_floating_attachments 3 ' || x_return_status);
858 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
859 IF FND_API.To_Boolean( p_commit ) THEN
860 ROLLBACK TO Implement_All_Pending_Changes;
861 END IF;
862 RETURN;
863 END IF;
864
865 UPDATE MTL_ITEM_REVISIONS_B
866 SET CURRENT_PHASE_ID = l_pending_record.PHASE_ID
867 WHERE
868 INVENTORY_ITEM_ID = l_pending_record.INVENTORY_ITEM_ID
869 AND REVISION = l_current_revision
870 AND ORGANIZATION_ID IN
871 (SELECT P2.ORGANIZATION_ID
872 FROM MTL_PARAMETERS P1,
873 MTL_PARAMETERS P2
874 WHERE P1.ORGANIZATION_ID = p_organization_id
875 AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID);
876
877 ELSIF (FND_API.TO_BOOLEAN(p_revision_master_controlled) = FALSE) THEN
878 code_debug (' rev is org controlled and we are at master org ');
879 --
880 -- Just update for the current one
881 --
882 code_debug(' before check_floating_attachments 4##########');
883 EGO_DOM_UTIL_PUB.check_floating_attachments ( p_inventory_item_id => l_pending_record.INVENTORY_ITEM_ID
884 ,p_revision_id => l_pending_record.REVISION_ID
885 ,p_organization_id => l_pending_record.ORGANIZATION_ID
886 ,p_lifecycle_id => NULL
887 ,p_new_phase_id => l_pending_record.PHASE_ID
888 ,x_return_status => x_return_status
889 ,x_msg_count => x_msg_count
890 ,x_msg_data => x_msg_data );
891
892 code_debug(' after check_floating_attachments 4 ' || x_return_status);
893 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
894 IF FND_API.To_Boolean( p_commit ) THEN
895 ROLLBACK TO Implement_All_Pending_Changes;
896 END IF;
897 RETURN;
898 END IF;
899
900 UPDATE MTL_ITEM_REVISIONS_B
901 SET CURRENT_PHASE_ID = l_pending_record.PHASE_ID
902 WHERE
903 INVENTORY_ITEM_ID = l_pending_record.INVENTORY_ITEM_ID
904 AND REVISION_ID = l_pending_record.REVISION_ID
905 AND ORGANIZATION_ID = l_pending_record.ORGANIZATION_ID;
906 END IF;
907
908 code_debug (' modifying the pending status table now ');
909 IF (FND_API.TO_BOOLEAN(p_is_master_org)
910 OR
911 FND_API.TO_BOOLEAN(p_revision_master_controlled) = FALSE
912 ) THEN
913 UPDATE MTL_PENDING_ITEM_STATUS
914 SET
915 PENDING_FLAG = 'N'
916 ,IMPLEMENTED_DATE = SYSDATE
917 WHERE CURRENT OF l_pending_revision_statuses;
918 END IF;
919
920 END IF;
921
922 END LOOP;
923
924 END IF;
925
926 IF FND_API.To_Boolean(p_commit) THEN
927 COMMIT WORK;
928 -- Call IP Intermedia Sync
929 INV_ITEM_EVENTS_PVT.Sync_IP_IM_Index;
930 END IF;
931 x_return_status := FND_API.G_RET_STS_SUCCESS;
932
933 EXCEPTION
934 WHEN FND_API.G_EXC_ERROR THEN
935 IF FND_API.To_Boolean( p_commit ) THEN
936 ROLLBACK TO Implement_All_Pending_Changes;
937 END IF;
938 x_return_status := FND_API.G_RET_STS_ERROR;
939 FND_MSG_PUB.Count_And_Get(
940 p_encoded => FND_API.G_FALSE,
941 p_count => x_msg_count,
942 p_data => x_msg_data
943 );
944 WHEN OTHERS THEN
945 IF FND_API.To_Boolean(p_commit) THEN
946 ROLLBACK TO Implement_All_Pending_Changes;
947 END IF;
948 IF c_item_pending_phase_change%ISOPEN THEN
949 CLOSE c_item_pending_phase_change;
950 END IF;
951 IF c_rev_pending_phase_change%ISOPEN THEN
952 CLOSE c_rev_pending_phase_change;
953 END IF;
954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
956 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
957 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, 'IMPLEMENT_ALL_PENDING_CHANGES');
958 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
959 x_msg_count := 1;
960 x_msg_data := FND_MESSAGE.GET;
961 -- x_return_status := FND_API.G_RET_STS_ERROR;
962 -- x_msg_data := FND_MESSAGE.Get_String('EGO', 'EGO_EXT_EXCEPTION_OCCURED');
963
964 END Implement_All_Pending_Changes;
965
966
967
968 ----------------------------------------------------------------------
969 -- Public Procedures / Functions
970 ----------------------------------------------------------------------
971
972 FUNCTION get_master_controlled_status RETURN VARCHAR2 IS
973 l_status_master_controlled VARCHAR2(100);
974 BEGIN
975 l_status_master_controlled := FND_API.G_FALSE;
976 SELECT DECODE(LOOKUP_CODE2,
977 1, FND_API.G_TRUE,
978 2, FND_API.G_FALSE,
979 FND_API.G_FALSE)
980 INTO l_status_master_controlled
981 FROM MTL_ITEM_ATTRIBUTES_V
982 WHERE ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
983
984 RETURN l_status_master_controlled;
985 EXCEPTION
986 WHEN OTHERS THEN
987 RETURN l_status_master_controlled;
988 END get_master_controlled_status;
989
990
991 -------------------------------------------------------
992 FUNCTION get_master_org_status (p_organization_id IN NUMBER)
993 RETURN VARCHAR2 IS
994 l_is_master_org VARCHAR2(100);
995 BEGIN
996 l_is_master_org := FND_API.G_FALSE;
997 SELECT DECODE(MP.ORGANIZATION_ID,
998 MP.MASTER_ORGANIZATION_ID, FND_API.G_TRUE,
999 FND_API.G_FALSE)
1000 INTO l_is_master_org
1001 FROM MTL_PARAMETERS MP
1002 WHERE MP.ORGANIZATION_ID = p_organization_id;
1003
1004 RETURN l_is_master_org;
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 RETURN l_is_master_org;
1009 END get_master_org_status;
1010
1011
1012 -------------------------------------------------------
1013 FUNCTION get_revision_id (p_inventory_item_id IN NUMBER
1014 ,p_organization_id IN NUMBER
1015 ,p_revision IN VARCHAR2)
1016 RETURN NUMBER IS
1017 l_revision_id NUMBER;
1018 BEGIN
1019 l_revision_id := NULL;
1020 SELECT REVISION_ID
1021 INTO l_revision_id
1022 FROM MTL_ITEM_REVISIONS
1023 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1024 AND ORGANIZATION_ID = p_organization_id
1025 AND revision = p_revision;
1026 RETURN l_revision_id;
1027
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 RETURN l_revision_id;
1031 END get_revision_id;
1032
1033
1034 -------------------------------------------------------
1035 PROCEDURE Create_Pending_Phase_Change
1036 (
1037 p_api_version IN NUMBER
1038 ,p_commit IN VARCHAR2
1039 ,p_inventory_item_id IN NUMBER
1040 ,p_item_number IN VARCHAR2 DEFAULT NULL
1041 ,p_organization_id IN NUMBER
1042 ,p_effective_date IN DATE
1043 ,p_pending_flag IN VARCHAR2
1044 ,p_revision IN VARCHAR2
1045 ,p_revision_id IN NUMBER DEFAULT NULL
1046 ,p_lifecycle_id IN NUMBER
1047 ,p_phase_id IN NUMBER
1048 ,p_status_code IN VARCHAR2 DEFAULT NULL
1049 ,p_change_id IN NUMBER
1050 ,p_change_line_id IN NUMBER
1051 ,p_perform_security_check IN VARCHAR2 DEFAULT 'F'
1052 ,x_return_status OUT NOCOPY VARCHAR2
1053 ,x_errorcode OUT NOCOPY NUMBER
1054 ,x_msg_count OUT NOCOPY NUMBER
1055 ,x_msg_data OUT NOCOPY VARCHAR2
1056 ) IS
1057
1058 l_api_version NUMBER;
1059 l_api_name VARCHAR2(50);
1060 l_sysdate DATE;
1061 l_revision mtl_item_revisions_b.revision%TYPE;
1062 l_revision_id mtl_pending_item_status.revision_id%TYPE;
1063 l_phase_id mtl_pending_item_status.phase_id%TYPE;
1064 l_lifecycle_id mtl_pending_item_status.lifecycle_id%TYPE;
1065 l_status_code mtl_pending_item_status.status_code%TYPE;
1066 l_phase_id_curr mtl_pending_item_status.phase_id%TYPE;
1067 phase_id mtl_pending_item_status.phase_id%TYPE; -- Bug 14076356
1068 l_lifecycle_id_itm mtl_pending_item_status.lifecycle_id%TYPE;
1069 l_status_code_itm mtl_pending_item_status.status_code%TYPE;
1070 l_status_code_def mtl_pending_item_status.status_code%TYPE;
1071 l_pending_rec_count NUMBER;
1072 l_item_number MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
1073 l_org_name HR_ALL_ORGANIZATION_UNITS_VL.NAME%TYPE;
1074 l_dummy_char VARCHAR2(32767);
1075 l_approval_status mtl_system_items_b.approval_status%TYPE;
1076 l_priv_name_to_check VARCHAR2(100);
1077
1078 l_revision_master_controlled VARCHAR2(1);
1079 l_status_master_controlled VARCHAR2(1);
1080 l_is_master_org VARCHAR2(1);
1081
1082 CURSOR c_get_rev_details (cp_item_id IN NUMBER
1083 ,cp_org_id IN NUMBER
1084 ,cp_revision IN VARCHAR2
1085 ,cp_revision_id IN NUMBER) IS
1086 SELECT rev.current_phase_id, rev.lifecycle_id, rev.revision, rev.revision_id, item.approval_status
1087 FROM mtl_item_revisions_b rev, mtl_system_items_b item
1088 WHERE rev.inventory_item_id = cp_item_id
1089 AND rev.organization_id = cp_org_id
1090 AND rev.revision = NVL(cp_revision, rev.revision)
1091 AND rev.revision_id = NVL(cp_revision_id, rev.revision_id)
1092 AND item.inventory_item_id = rev.inventory_item_id
1093 AND item.organization_id = rev.organization_id;
1094
1095 CURSOR c_get_item_details (cp_item_id IN NUMBER
1096 ,cp_org_id IN NUMBER) IS
1097 SELECT current_phase_id, lifecycle_id, inventory_item_status_code, approval_status
1098 FROM mtl_system_items_b
1099 WHERE inventory_item_id = cp_item_id
1100 AND organization_id = cp_org_id;
1101
1102 CURSOR c_get_def_status_code (cp_phase_id IN NUMBER) IS
1103 SELECT status.item_status_code
1104 FROM ego_lcphase_item_status status, pa_proj_elements lc_phases
1105 WHERE lc_phases.proj_element_id = cp_phase_id
1106 AND status.phase_code = lc_phases.phase_code
1107 AND status.default_flag = 'Y'
1108 AND lc_phases.PROJECT_ID = 0 AND lc_phases.OBJECT_TYPE = 'PA_TASKS';
1109
1110 CURSOR c_validate_status_code (cp_phase_id IN NUMBER
1111 ,cp_status_code IN VARCHAR2) IS
1112 SELECT status.item_status_code
1113 FROM ego_lcphase_item_status status, pa_ego_phases_v lc_phases
1114 WHERE lc_phases.proj_element_id = cp_phase_id
1115 AND status.phase_code = lc_phases.phase_code
1116 AND status.item_status_code = cp_status_code;
1117 --Bug 14076356 , Added to validate the Item status
1118 CURSOR c_item_validate_status_code (cp_status_code IN VARCHAR2) IS
1119 SELECT status.INVENTORY_ITEM_STATUS_CODE
1120 FROM mtl_item_status status
1121 WHERE status.INVENTORY_ITEM_STATUS_CODE = cp_status_code;
1122
1123
1124 BEGIN
1125
1126 l_api_version := 1.0;
1127 l_api_name := 'Create_Pending_Phase_Change';
1128
1129 IF FND_API.To_Boolean(p_commit) THEN
1130 SAVEPOINT Create_Pending_Phase_Change_SP;
1131 END IF;
1132 code_debug( l_api_name ||' started with params');
1133 code_debug( l_api_name ||' p_inventory_item_id '|| p_inventory_item_id ||' p_organization_id '|| p_organization_id || ' p_revision '|| p_revision);
1134 code_debug( l_api_name ||' p_lifecycle_id '|| p_lifecycle_id ||' p_phase_id '|| p_phase_id ||' p_status_code '|| p_status_code);
1135 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);
1136
1137 --Standard checks
1138 IF NOT FND_API.Compatible_API_Call (l_api_version
1139 ,p_api_version
1140 ,l_api_name
1141 ,g_pkg_name)
1142 THEN
1143 RAISE FND_API.G_EXC_ERROR;
1144 END IF;
1145
1146 IF Nvl(FND_GLOBAL.User_Id,-1) <> Nvl(G_CURRENT_USER_ID,-1)
1147 THEN
1148 G_CURRENT_USER_ID := FND_GLOBAL.User_Id;
1149 G_CURRENT_LOGIN_ID := FND_GLOBAL.Login_Id;
1150 END IF;
1151
1152 IF (p_inventory_item_id IS NULL
1153 OR
1154 p_organization_id IS NULL
1155 OR
1156 ( (p_revision IS NOT NULL OR p_revision_id IS NOT NULL) AND p_status_code IS NOT NULL)
1157 OR
1158 (p_phase_id IS NULL AND p_status_code IS NULL)
1159 ) THEN
1160 fnd_message.Set_Name(G_APP_NAME, 'EGO_API_INVALID_PARAMS');
1161 fnd_message.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
1162 fnd_message.Set_Token(G_API_NAME_TOKEN, l_api_name);
1163 FND_MSG_PUB.Add;
1164 RAISE FND_API.G_EXC_ERROR;
1165 END IF;
1166
1167 l_revision := p_revision;
1168 l_revision_id := p_revision_id;
1169
1170 IF (l_revision IS NULL AND l_revision_id IS NULL) THEN
1171 OPEN c_get_item_details (cp_item_id => p_inventory_item_id
1172 ,cp_org_id => p_organization_id);
1173 FETCH c_get_item_details
1174 INTO l_phase_id_curr, l_lifecycle_id_itm, l_status_code_itm, l_approval_status;
1175 IF c_get_item_details%NOTFOUND THEN
1176 l_phase_id_curr := NULL;
1177 l_lifecycle_id_itm := NULL;
1178 END IF;
1179 CLOSE c_get_item_details;
1180 ELSE
1181 l_status_code_itm := NULL;
1182 OPEN c_get_rev_details (cp_item_id => p_inventory_item_id
1183 ,cp_org_id => p_organization_id
1184 ,cp_revision => l_revision
1185 ,cp_revision_id => l_revision_id);
1186 FETCH c_get_rev_details
1187 INTO l_phase_id_curr, l_lifecycle_id_itm, l_revision, l_revision_id, l_approval_status;
1188 IF c_get_rev_details%NOTFOUND THEN
1189 l_phase_id_curr := NULL;
1190 l_lifecycle_id_itm := NULL;
1191 l_revision_id := NULL;
1192 END IF;
1193 CLOSE c_get_rev_details;
1194 END IF;
1195
1196 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 );
1197
1198 -- bug 3909677
1199 IF NVL(l_approval_status,'A') <> 'A' THEN
1200 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_NOT_READY_FOR_CHANGE');
1201 SELECT CONCATENATED_SEGMENTS
1202 INTO l_dummy_char
1203 FROM MTL_SYSTEM_ITEMS_KFV
1204 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1205 AND ORGANIZATION_ID = p_organization_id;
1206 fnd_message.set_token('ITEM_NUMBER', l_dummy_char);
1207 SELECT name
1208 INTO l_dummy_char
1209 FROM hr_all_organization_units_vl
1210 WHERE organization_id = p_organization_id;
1211 fnd_message.set_token('ORGANIZATION', l_dummy_char);
1212 fnd_msg_pub.Add;
1213 RAISE FND_API.G_EXC_ERROR;
1214 END IF;
1215
1216 IF FND_API.To_Boolean(p_perform_security_check) THEN
1217 code_debug( l_api_name ||' need to perform security check ');
1218 -- 4052565 perform security check
1219 l_priv_name_to_check := get_privlige_name_for_action
1220 (p_curr_item_id => p_inventory_item_id
1221 ,p_curr_org_id => p_organization_id
1222 ,p_curr_rev_id => l_revision_id
1223 ,p_curr_lc_id => l_lifecycle_id_itm
1224 ,p_curr_phase_id => l_phase_id_curr
1225 ,p_curr_status_code => l_status_code_itm
1226 ,p_new_lc_id => p_lifecycle_id
1227 ,p_new_phase_id => p_phase_id
1228 ,p_new_status_code => p_status_code
1229 );
1230 code_debug( l_api_name ||' priv check name '||l_priv_name_to_check);
1231 IF l_priv_name_to_check IS NOT NULL THEN
1232 IF NOT EGO_ITEM_PVT.has_role_on_item
1233 (p_function_name => l_priv_name_to_check
1234 ,p_inventory_item_id => p_inventory_item_id
1235 ,p_item_number => p_item_number
1236 ,p_organization_id => p_organization_id
1237 ,p_organization_name => NULL
1238 ,p_user_id => G_CURRENT_USER_ID
1239 ,p_party_id => NULL
1240 ,p_set_message => FND_API.G_TRUE
1241 ) THEN
1242 code_debug( l_api_name ||' user does not have privilege for '||l_priv_name_to_check);
1243 RAISE FND_API.G_EXC_ERROR;
1244 ELSE
1245 code_debug( l_api_name ||' user can perform the action '||l_priv_name_to_check);
1246 END IF;
1247 END IF;
1248 ELSE
1249 code_debug( l_api_name ||' NO need to perform security check ');
1250 END IF;
1251
1252 IF p_phase_id IS NOT NULL THEN
1253 --
1254 -- if status is not passed, get the default status code
1255 --
1256 l_phase_id := p_phase_id;
1257 l_lifecycle_id := NVL(p_lifecycle_id, l_lifecycle_id_itm);
1258 IF p_status_code IS NULL THEN
1259 -- get the default phase for the new phase
1260 OPEN c_get_def_status_code (cp_phase_id => p_phase_id);
1261 FETCH c_get_def_status_code INTO l_status_code_def;
1262 IF c_get_def_status_code%NOTFOUND THEN
1263 CLOSE c_get_def_status_code;
1264 SELECT name
1265 INTO l_dummy_char
1266 FROM PA_EGO_PHASES_V
1267 WHERE proj_element_id = p_phase_id;
1268 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_STATUS_FOR_PHASE_ERR');
1269 fnd_message.Set_Token('PHASE', l_dummy_char);
1270 FND_MSG_PUB.Add;
1271 RAISE FND_API.G_EXC_ERROR;
1272 ELSE
1273 CLOSE c_get_def_status_code;
1274 END IF;
1275 ELSE
1276 l_status_code_def := NULL;
1277 END IF; -- p_status_code IS NULL
1278 --
1279 -- user trying to do a phase change
1280 --
1281 IF (NVL(l_phase_id_curr,-1) <> p_phase_id) THEN
1282 SELECT count(*)
1283 INTO l_pending_rec_count
1284 FROM mtl_pending_item_status
1285 WHERE inventory_item_id = p_inventory_item_id
1286 AND organization_id = p_organization_id
1287 AND pending_flag = 'Y'
1288 AND implemented_date IS NULL
1289 AND NVL(revision_id,-1) = NVL(l_revision_id,-1)
1290 AND lifecycle_id IS NOT NULL
1291 AND phase_id IS NOT NULL;
1292 IF l_pending_rec_count <> 0 THEN
1293 --
1294 -- pending chanes already exist
1295 --
1296 IF p_item_number IS NULL THEN
1297 SELECT concatenated_segments
1298 INTO l_dummy_char
1299 FROM mtl_system_items_b_kfv
1300 WHERE inventory_item_id = p_inventory_item_id
1301 AND organization_id = p_organization_id;
1302 ELSE
1303 l_dummy_char := p_item_number;
1304 END IF;
1305 IF l_revision_id IS NULL THEN
1306 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_PENDING_PHASE_CHANGE');
1307 fnd_message.Set_Token('ITEM_NUMBER', l_dummy_char);
1308 FND_MSG_PUB.Add;
1309 RAISE FND_API.G_EXC_ERROR;
1310 ELSE
1311 fnd_message.Set_Name(G_APP_NAME, 'EGO_REV_PENDING_PHASE_CHANGE');
1312 fnd_message.Set_Token('ITEM_NUMBER', l_dummy_char);
1313 fnd_message.Set_Token('REVISION', l_revision);
1314 FND_MSG_PUB.Add;
1315 RAISE FND_API.G_EXC_ERROR;
1316 END IF;
1317 END IF; -- l_pending_rec_count
1318 END IF; -- NVL(l_phase_id_curr,-1) <> p_phase_id
1319 ELSE
1320 /*
1321 Bug 14076356: swuppala: the phase and life cycle attached
1322 to the item is not captured in the Pending Item Status change record
1323 --l_phase_id := NULL;
1324 -- l_lifecycle_id := NULL;
1325 Hence setting the Items current phase and lifecycle in case of Status Change
1326 */
1327 l_phase_id := l_phase_id_curr;
1328 l_lifecycle_id := l_lifecycle_id_itm;
1329
1330 END IF; -- phase id is not null
1331 code_debug( l_api_name ||' no pending phase changes ');
1332
1333 IF l_revision IS NULL THEN
1334 /*Bug 14076356: swuppala:
1335 Get the phase id START
1336 */
1337 phase_id := NVL(p_phase_id,l_phase_id_curr);
1338 IF(phase_id IS NOT NULL) THEN
1339
1340 -- check if the current status is valid for the item phase
1341 OPEN c_validate_status_code (cp_phase_id => NVL(p_phase_id,l_phase_id_curr)
1342 ,cp_status_code => NVL(p_status_code,l_status_code_def)
1343 );
1344 FETCH c_validate_status_code INTO l_status_code;
1345 IF c_validate_status_code%NOTFOUND THEN
1346 CLOSE c_validate_status_code;
1347 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_INVALID_STATUS');
1348 fnd_message.Set_Token('STATUS', p_status_code);
1349 SELECT name
1350 INTO l_dummy_char
1351 FROM PA_EGO_PHASES_V
1352 WHERE proj_element_id = NVL(p_phase_id,l_phase_id_curr);
1353 fnd_message.Set_Token('PHASE', l_dummy_char);
1354 FND_MSG_PUB.Add;
1355 RAISE FND_API.G_EXC_ERROR;
1356 ELSE
1357 code_debug (l_api_name ||' checking for master controlled status ');
1358 l_is_master_org := get_master_org_status(p_organization_id);
1359 l_revision_master_controlled := FND_API.g_false;
1360 l_status_master_controlled := get_master_controlled_status();
1361 IF ('T' = l_status_master_controlled) AND NOT ('T' = l_is_master_org) THEN
1362 IF l_status_code_itm <> l_status_code THEN
1363 code_debug (l_api_name ||' status changes controlled at master cannot change ');
1364 fnd_message.Set_Name(G_APP_NAME,'EGO_ITEM_STATUS_MC');
1365 fnd_msg_pub.Add;
1366 RAISE FND_API.G_EXC_ERROR;
1367 END IF;
1368 END IF; -- status is master controlled and we are in context of child org
1369 END IF; -- status is valid
1370 ELSE -- phase id null
1371 /*
1372 Bug 14076356: swuppala:
1373 If there is no phase associated to item, check if the status is
1374 valid item status.
1375 */
1376 OPEN c_item_validate_status_code (cp_status_code => NVL(p_status_code,l_status_code_def)
1377 );
1378 FETCH c_item_validate_status_code INTO l_status_code;
1379 IF c_item_validate_status_code%NOTFOUND THEN
1380 CLOSE c_item_validate_status_code;
1381 fnd_message.Set_Name(G_APP_NAME, 'EGO_STATUS_INVALID');
1382 -- fnd_message.Set_Token('STATUS', p_status_code);
1383 -- SELECT name
1384 -- INTO l_dummy_char
1385 -- FROM PA_EGO_PHASES_V
1386 -- WHERE proj_element_id = NVL(p_phase_id,l_phase_id_curr);
1387 -- fnd_message.Set_Token('PHASE', l_dummy_char);
1388 FND_MSG_PUB.Add;
1389 RAISE FND_API.G_EXC_ERROR;
1390 ELSE
1391 code_debug (l_api_name ||' checking for master controlled status ');
1392 l_is_master_org := get_master_org_status(p_organization_id);
1393 l_revision_master_controlled := FND_API.g_false;
1394 l_status_master_controlled := get_master_controlled_status();
1395 IF ('T' = l_status_master_controlled) AND NOT ('T' = l_is_master_org) THEN
1396 IF l_status_code_itm <> l_status_code THEN
1397 code_debug (l_api_name ||' status changes controlled at master cannot change ');
1398 fnd_message.Set_Name(G_APP_NAME,'EGO_ITEM_STATUS_MC');
1399 fnd_msg_pub.Add;
1400 RAISE FND_API.G_EXC_ERROR;
1401 END IF;
1402 END IF; -- status is master controlled and we are in context of child org
1403 END IF; -- status is valid
1404 /*Bug 14076356: swuppala:
1405 Get the phase id END
1406 */
1407 END IF;
1408
1409 ELSE
1410 -- context of revision
1411 l_status_code := NULL;
1412 END IF; -- revision IS NULL
1413 code_debug(' comparing values before insert p_lifecycle_id '||p_lifecycle_id ||' l_lifecycle_id '||l_lifecycle_id);
1414 --
1415 -- to be removed after bug 3874132 is resoloved.
1416 --
1417 l_sysdate := SYSDATE;
1418 INSERT INTO MTL_PENDING_ITEM_STATUS
1419 (
1420 inventory_item_id
1421 ,organization_id
1422 ,status_code
1423 ,effective_date
1424 ,implemented_date
1425 ,pending_flag
1426 ,last_update_date
1427 ,last_updated_by
1428 ,creation_date
1429 ,created_by
1430 ,last_update_login
1431 -- ,request_id
1432 -- ,program_update_date
1433 ,revision_id
1434 ,lifecycle_id
1435 ,phase_id
1436 ,change_id
1437 ,change_line_id
1438 )
1439 VALUES
1440 (
1441 p_inventory_item_id
1442 ,p_organization_id
1443 ,l_status_code
1444 ,NVL(p_effective_date,l_sysdate)
1445 ,NULL
1446 ,NVL(p_pending_flag,'Y')
1447 ,l_sysdate
1448 ,G_CURRENT_USER_ID
1449 ,l_sysdate
1450 ,G_CURRENT_USER_ID
1451 ,G_CURRENT_LOGIN_ID
1452 -- ,NULL
1453 -- ,l_sysdate
1454 ,l_revision_id
1455 ,l_lifecycle_id
1456 ,l_phase_id
1457 ,p_change_id
1458 ,p_change_line_id
1459 );
1460
1461 IF FND_API.To_Boolean(p_commit) THEN
1462 COMMIT WORK;
1463 END IF;
1464 x_return_status := FND_API.G_RET_STS_SUCCESS;
1465 EXCEPTION
1466 WHEN FND_API.G_EXC_ERROR THEN
1467 IF FND_API.To_Boolean( p_commit ) THEN
1468 ROLLBACK TO Create_Pending_Phase_Change_SP;
1469 END IF;
1470 IF c_get_rev_details%ISOPEN THEN
1471 CLOSE c_get_rev_details;
1472 END IF;
1473 IF c_get_item_details%ISOPEN THEN
1474 CLOSE c_get_item_details;
1475 END IF;
1476 IF c_get_def_status_code%ISOPEN THEN
1477 CLOSE c_get_def_status_code;
1478 END IF;
1479 IF c_validate_status_code%ISOPEN THEN
1480 CLOSE c_validate_status_code;
1481 END IF;
1482 x_return_status := FND_API.G_RET_STS_ERROR;
1483 WHEN OTHERS THEN
1484 IF FND_API.To_Boolean(p_commit) THEN
1485 ROLLBACK TO Create_Pending_Phase_Change_SP;
1486 END IF;
1487 IF c_get_rev_details%ISOPEN THEN
1488 CLOSE c_get_rev_details;
1489 END IF;
1490 IF c_get_item_details%ISOPEN THEN
1491 CLOSE c_get_item_details;
1492 END IF;
1493 IF c_get_def_status_code%ISOPEN THEN
1494 CLOSE c_get_def_status_code;
1495 END IF;
1496 IF c_validate_status_code%ISOPEN THEN
1497 CLOSE c_validate_status_code;
1498 END IF;
1499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1500 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1501 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1502 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1503 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1504 FND_MSG_PUB.Add;
1505 END Create_Pending_Phase_Change;
1506
1507
1508 -------------------------------------------------------
1509 PROCEDURE Modify_Pending_Phase_Change
1510 (p_api_version IN NUMBER
1511 ,p_commit IN VARCHAR2
1512 ,p_transaction_type IN VARCHAR2
1513 ,p_inventory_item_id IN NUMBER
1514 ,p_organization_id IN NUMBER
1515 ,p_revision_id IN NUMBER
1516 ,p_lifecycle_id IN NUMBER
1517 ,p_phase_id IN NUMBER
1518 ,p_status_code IN VARCHAR2
1519 ,p_change_id IN NUMBER
1520 ,p_change_line_id IN NUMBER
1521 ,p_effective_date IN DATE
1522 ,p_new_effective_date IN DATE
1523 ,p_perform_security_check IN VARCHAR2
1524 ,x_return_status OUT NOCOPY VARCHAR2
1525 ,x_errorcode OUT NOCOPY NUMBER
1526 ,x_msg_count OUT NOCOPY NUMBER
1527 ,x_msg_data OUT NOCOPY VARCHAR2
1528 ) IS
1529
1530 l_api_version NUMBER;
1531 l_api_name VARCHAR2(50);
1532 l_miss_num NUMBER;
1533 l_miss_char VARCHAR2(1);
1534 l_priv_name_to_check VARCHAR2(100);
1535
1536
1537 BEGIN
1538 l_api_version := 1.0;
1539 l_api_name := 'Modify_Pending_Phase_Change';
1540 l_miss_num := FND_API.G_MISS_NUM;
1541 l_miss_char := FND_API.G_MISS_CHAR;
1542
1543 IF FND_API.To_Boolean(p_commit) THEN
1544 SAVEPOINT Modify_Pending_Phase_Change_SP;
1545 END IF;
1546
1547 --Standard checks
1548 IF NOT FND_API.Compatible_API_Call (l_api_version
1549 ,p_api_version
1550 ,l_api_name
1551 ,g_pkg_name)
1552 THEN
1553 RAISE FND_API.G_EXC_ERROR;
1554 END IF;
1555
1556 IF Nvl(FND_GLOBAL.User_Id,-1) <> Nvl(G_CURRENT_USER_ID,-1)
1557 THEN
1558 G_CURRENT_USER_ID := FND_GLOBAL.User_Id;
1559 G_CURRENT_LOGIN_ID := FND_GLOBAL.Login_Id;
1560 END IF;
1561
1562
1563 IF ( p_inventory_item_id IS NULL
1564 OR
1565 p_organization_id IS NULL
1566 OR
1567 p_effective_date IS NULL
1568 OR
1569 p_transaction_type NOT IN (EGO_ITEM_PUB.G_TTYPE_UPDATE, EGO_ITEM_PUB.G_TTYPE_DELETE)
1570 ) THEN
1571 fnd_message.Set_Name(G_APP_NAME, 'EGO_API_INVALID_PARAMS');
1572 fnd_message.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
1573 fnd_message.Set_Token(G_API_NAME_TOKEN, l_api_name);
1574 FND_MSG_PUB.Add;
1575 RAISE FND_API.G_EXC_ERROR;
1576 END IF;
1577
1578 IF FND_API.To_Boolean(p_perform_security_check) THEN
1579 -- 4052565 perform security check
1580 l_priv_name_to_check := get_privlige_name_for_action
1581 (p_curr_item_id => p_inventory_item_id
1582 ,p_curr_org_id => p_organization_id
1583 ,p_curr_rev_id => p_revision_id
1584 ,p_curr_lc_id => NULL
1585 ,p_curr_phase_id => NULL
1586 ,p_curr_status_code => NULL
1587 ,p_new_lc_id => p_lifecycle_id
1588 ,p_new_phase_id => p_phase_id
1589 ,p_new_status_code => p_status_code
1590 );
1591 IF l_priv_name_to_check IS NOT NULL THEN
1592 IF NOT EGO_ITEM_PVT.has_role_on_item
1593 (p_function_name => l_priv_name_to_check
1594 ,p_inventory_item_id => p_inventory_item_id
1595 ,p_item_number => NULL
1596 ,p_organization_id => p_organization_id
1597 ,p_organization_name => NULL
1598 ,p_user_id => G_CURRENT_USER_ID
1599 ,p_party_id => NULL
1600 ,p_set_message => FND_API.G_TRUE
1601 ) THEN
1602 RAISE FND_API.G_EXC_ERROR;
1603 END IF;
1604 END IF;
1605 END IF;
1606
1607 IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
1608 --
1609 -- to be removed after bug 3874132 is resoloved.
1610 --
1611 code_debug(l_api_name|| ' Updating pending change record ');
1612 UPDATE mtl_pending_item_status
1613 SET effective_date = p_new_effective_date,
1614 last_update_date = SYSDATE,
1615 last_updated_by = G_CURRENT_USER_ID,
1616 last_update_login = G_CURRENT_LOGIN_ID
1617 WHERE inventory_item_id = p_inventory_item_id
1618 AND organization_id = p_organization_id
1619 AND NVL(revision_id,l_miss_num) = NVL(p_revision_id, l_miss_num)
1620 AND NVL(lifecycle_id, l_miss_num) = NVL(p_lifecycle_id, l_miss_num)
1621 AND NVL(phase_id, l_miss_num) = NVL(p_phase_id, l_miss_num)
1622 AND NVL(status_code,l_miss_char) = NVL(p_status_code, l_miss_char)
1623 AND NVL(p_change_id, l_miss_num) = NVL(p_change_id, l_miss_num)
1624 AND NVL(p_change_line_id, l_miss_num) = NVL(p_change_line_id, l_miss_num)
1625 AND effective_date = p_effective_date
1626 AND pending_flag = 'Y'
1627 AND implemented_date IS NULL;
1628 IF SQL%ROWCOUNT = 0 THEN
1629 code_debug(l_api_name|| ' cannot update record!! ');
1630 -- no records found for update
1631 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_REC_UPDATE');
1632 FND_MSG_PUB.Add;
1633 RAISE FND_API.G_EXC_ERROR;
1634 END IF;
1635
1636 ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE THEN
1637 --
1638 -- to be removed after bug 3874132 is resoloved.
1639 --
1640 code_debug(l_api_name|| ' Deleting pending change record ');
1641 DELETE mtl_pending_item_status
1642 WHERE inventory_item_id = p_inventory_item_id
1643 AND organization_id = p_organization_id
1644 AND NVL(revision_id,l_miss_num) = NVL(p_revision_id, l_miss_num)
1645 AND NVL(lifecycle_id, l_miss_num) = NVL(p_lifecycle_id, l_miss_num)
1646 AND NVL(phase_id, l_miss_num) = NVL(p_phase_id, l_miss_num)
1647 AND NVL(status_code,l_miss_char) = NVL(p_status_code, l_miss_char)
1648 AND NVL(p_change_id, l_miss_num) = NVL(p_change_id, l_miss_num)
1649 AND NVL(p_change_line_id, l_miss_num) = NVL(p_change_line_id, l_miss_num)
1650 AND effective_date = p_effective_date
1651 AND pending_flag = 'Y'
1652 AND implemented_date IS NULL;
1653 IF SQL%ROWCOUNT = 0 THEN
1654 -- no records found for delete
1655 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_REC_DELETE');
1656 FND_MSG_PUB.Add;
1657 RAISE FND_API.G_EXC_ERROR;
1658 END IF;
1659
1660 END IF;
1661
1662 EXCEPTION
1663 WHEN FND_API.G_EXC_ERROR THEN
1664 IF FND_API.To_Boolean( p_commit ) THEN
1665 ROLLBACK TO Create_Pending_Phase_Change_SP;
1666 END IF;
1667 x_return_status := FND_API.G_RET_STS_ERROR;
1668
1669 END Modify_Pending_Phase_change;
1670
1671
1672
1673 /***
1674 PROCEDURE Delete_Pending_Phase_Change
1675 (
1676 p_api_version IN NUMBER
1677 ,p_commit IN VARCHAR2
1678 ,p_inventory_item_id IN NUMBER
1679 ,p_organization_id IN NUMBER
1680 ,p_change_id IN NUMBER
1681 ,p_change_line_id IN NUMBER
1682 ,x_return_status OUT NOCOPY VARCHAR2
1683 ,x_errorcode OUT NOCOPY NUMBER
1684 ,x_msg_count OUT NOCOPY NUMBER
1685 ,x_msg_data OUT NOCOPY VARCHAR2
1686 ) IS
1687
1688 l_api_version NUMBER;
1689 l_api_name VARCHAR2(50);
1690
1691 BEGIN
1692 l_api_version := 1.0;
1693 l_api_name := 'Delete_Pending_Phase_Change';
1694 --Standard checks
1695 IF NOT FND_API.Compatible_API_Call (l_api_version
1696 ,p_api_version
1697 ,l_api_name
1698 ,g_pkg_name)
1699 THEN
1700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1701 END IF;
1702
1703 IF ( (p_inventory_item_id IS NULL
1704 AND
1705 p_change_id IS NULL
1706 AND
1707 p_change_line_id IS NULL
1708 )
1709 OR
1710 p_organization_id IS NULL
1711 ) THEN
1712 x_return_status := FND_API.G_RET_STS_ERROR;
1713 RETURN;
1714 END IF;
1715
1716 IF FND_API.To_Boolean(p_commit) THEN
1717 SAVEPOINT Delete_Pending_Phase_Change_SP;
1718 END IF;
1719
1720 IF p_change_id IS NOT NULL THEN
1721 IF p_change_line_id IS NOT NULL THEN
1722 IF p_inventory_item_id IS NOT NULL THEN
1723 --
1724 -- change_id, change_line_id, inventory_item_id present
1725 --
1726 DELETE MTL_PENDING_ITEM_STATUS
1727 WHERE organization_id = p_organization_id
1728 AND inventory_item_id = p_inventory_item_id
1729 AND change_id = p_change_id
1730 AND change_line_id = p_change_line_id
1731 AND implemented_date IS NULL
1732 AND pending_flag = 'Y';
1733 ELSE
1734 --
1735 -- change_id, change_line_id present
1736 --
1737 DELETE MTL_PENDING_ITEM_STATUS
1738 WHERE organization_id = p_organization_id
1739 AND change_id = p_change_id
1740 AND change_line_id = p_change_line_id
1741 AND implemented_date IS NULL
1742 AND pending_flag = 'Y';
1743 END IF;
1744 ELSE -- change line id is null
1745 IF p_inventory_item_id IS NOT NULL THEN
1746 --
1747 -- change_id, inventory_item_id present
1748 --
1749 DELETE MTL_PENDING_ITEM_STATUS
1750 WHERE organization_id = p_organization_id
1751 AND inventory_item_id = p_inventory_item_id
1752 AND change_id = p_change_id
1753 AND implemented_date IS NULL
1754 AND pending_flag = 'Y';
1755 ELSE
1756 --
1757 -- only change_id present
1758 --
1759 DELETE MTL_PENDING_ITEM_STATUS
1760 WHERE organization_id = p_organization_id
1761 AND change_id = p_change_id
1762 AND implemented_date IS NULL
1763 AND pending_flag = 'Y';
1764 END IF;
1765 END IF;
1766 ELSE -- change id is null
1767 IF p_change_line_id IS NOT NULL THEN
1768 IF p_inventory_item_id IS NOT NULL THEN
1769 --
1770 -- change_line_id, inventory_item_id present
1771 --
1772 DELETE MTL_PENDING_ITEM_STATUS
1773 WHERE organization_id = p_organization_id
1774 AND inventory_item_id = p_inventory_item_id
1775 AND change_line_id = p_change_line_id
1776 AND implemented_date IS NULL
1777 AND pending_flag = 'Y';
1778 ELSE
1779 --
1780 -- only change_line_id present
1781 --
1782 DELETE MTL_PENDING_ITEM_STATUS
1783 WHERE organization_id = p_organization_id
1784 AND change_line_id = p_change_line_id
1785 AND implemented_date IS NULL
1786 AND pending_flag = 'Y';
1787 END IF;
1788 ELSE -- change line id is null
1789 IF p_inventory_item_id IS NOT NULL THEN
1790 --
1791 -- only inventory_item_id present
1792 -- delete where change_id and change_line_id are null
1793 --
1794 DELETE MTL_PENDING_ITEM_STATUS
1795 WHERE organization_id = p_organization_id
1796 AND inventory_item_id = p_inventory_item_id
1797 AND change_id IS NULL
1798 AND change_line_id IS NULL
1799 AND implemented_date IS NULL
1800 AND pending_flag = 'Y';
1801 END IF;
1802 END IF;
1803 END IF;
1804
1805 IF FND_API.To_Boolean(p_commit) THEN
1806 COMMIT WORK;
1807 END IF;
1808 x_return_status := FND_API.G_RET_STS_SUCCESS;
1809
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 IF FND_API.To_Boolean(p_commit) THEN
1813 ROLLBACK TO Delete_Pending_Phase_Change_SP;
1814 END IF;
1815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1816 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1817 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1818 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1819 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1820 FND_MSG_PUB.Add;
1821
1822 END Delete_Pending_Phase_Change;
1823 ***/
1824
1825 PROCEDURE Implement_Pending_Changes
1826 (
1827 p_api_version IN NUMBER
1828 , p_inventory_item_id IN NUMBER
1829 , p_organization_id IN NUMBER
1830 , p_revision_id IN NUMBER
1831 , p_revision_master_controlled IN VARCHAR2
1832 , p_status_master_controlled IN VARCHAR2
1833 , p_is_master_org IN VARCHAR2
1834 , p_perform_security_check IN VARCHAR2 DEFAULT 'F'
1835 , x_return_status OUT NOCOPY VARCHAR2
1836 , x_errorcode OUT NOCOPY NUMBER
1837 , x_msg_count OUT NOCOPY NUMBER
1838 , x_msg_data OUT NOCOPY VARCHAR2
1839 ) IS
1840
1841 l_commit VARCHAR2(1);
1842
1843 BEGIN
1844 --
1845 -- existing functionality is doing a commit always
1846 --
1847 code_debug(' Implement Pending Changes from Projects area item id '||p_inventory_item_id||' org id '||p_organization_id||' rev id '||p_revision_id);
1848 /* Fix for bug 11660573 - commenting the below code that hardcodes l_commit =
1849 * TRUE.
1850 * Hardcoding it to true was causing 'SAVEPOINT NEVER ESTABLISHED' exception
1851 * when any error was occuring in Implement_All_Pending_Changes() api.
1852 * l_commit := FND_API.G_TRUE;
1853 * */
1854 Implement_All_Pending_Changes
1855 (p_api_version => p_api_version
1856 ,p_commit => l_commit
1857 ,p_inventory_item_id => p_inventory_item_id
1858 ,p_organization_id => p_organization_id
1859 ,p_revision_id => p_revision_id
1860 ,p_change_id => NULL
1861 ,p_change_line_id => NULL
1862 ,p_revision_master_controlled => p_revision_master_controlled
1863 ,p_status_master_controlled => p_status_master_controlled
1864 ,p_is_master_org => p_is_master_org
1865 ,p_perform_security_check => p_perform_security_check
1866 ,x_return_status => x_return_status
1867 ,x_errorcode => x_errorcode
1868 ,x_msg_count => x_msg_count
1869 ,x_msg_data => x_msg_data
1870 );
1871
1872 END Implement_Pending_Changes;
1873
1874 --
1875 -- Created as a part of Fix for 3371749
1876 --
1877 PROCEDURE Implement_Pending_Changes
1878 (
1879 p_api_version IN NUMBER
1880 , p_commit IN VARCHAR2
1881 , p_change_id IN NUMBER
1882 , p_change_line_id IN NUMBER
1883 , p_perform_security_check IN VARCHAR2 DEFAULT 'F'
1884 , x_return_status OUT NOCOPY VARCHAR2
1885 , x_errorcode OUT NOCOPY NUMBER
1886 , x_msg_count OUT NOCOPY NUMBER
1887 , x_msg_data OUT NOCOPY VARCHAR2
1888 )
1889 IS
1890
1891 l_api_version NUMBER;
1892 l_api_name VARCHAR2(50);
1893
1894 l_revision_master_controlled VARCHAR2(1);
1895 l_status_master_controlled VARCHAR2(1);
1896 l_is_master_org VARCHAR2(1);
1897
1898 CURSOR c_get_pending_items (cp_change_id IN NUMBER
1899 ,cp_change_line_id IN NUMBER) IS
1900 SELECT *
1901 FROM mtl_pending_item_status
1902 WHERE implemented_date IS NULL
1903 AND pending_flag = 'Y'
1904 AND change_id = NVL(cp_change_id, change_id)
1905 AND change_line_id = NVL(cp_change_line_id, change_line_id);
1906
1907 BEGIN
1908
1909 l_api_version := 1.0;
1910 l_api_name := 'Implement_Pending_Changes';
1911 code_debug(' Implement Pending Changes from Change area ');
1912 --Standard checks
1913 IF NOT FND_API.Compatible_API_Call (l_api_version
1914 ,p_api_version
1915 ,l_api_name
1916 ,g_pkg_name)
1917 THEN
1918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1919 END IF;
1920
1921 IF (p_change_id IS NULL AND p_change_line_id IS NULL) THEN
1922 FND_MESSAGE.Set_name(G_APP_NAME, 'EGO_API_INVALID_PARAMS');
1923 FND_MESSAGE.Set_token('PKG_NAME', G_PKG_NAME);
1924 FND_MESSAGE.Set_Token('PROC_NAME', l_api_name);
1925 x_msg_data := FND_MESSAGE.get();
1926 x_msg_count := 1;
1927 x_return_status := FND_API.G_RET_STS_ERROR;
1928 RETURN;
1929 END IF;
1930
1931 IF FND_API.To_Boolean(p_commit) THEN
1932 SAVEPOINT Implement_Pending_Changes_SP;
1933 END IF;
1934
1935 l_revision_master_controlled := FND_API.g_false;
1936 l_status_master_controlled := EGO_ITEM_LC_IMP_PC_PUB.get_master_controlled_status();
1937
1938 FOR l_item_record IN
1939 c_get_pending_items (cp_change_id => p_change_id
1940 ,cp_change_line_id => p_change_line_id)
1941 LOOP
1942
1943 l_is_master_org := get_master_org_status(l_item_record.ORGANIZATION_ID);
1944 Implement_All_Pending_Changes
1945 (p_api_version => p_api_version
1946 ,p_commit => FND_API.G_FALSE
1947 ,p_inventory_item_id => l_item_record.INVENTORY_ITEM_ID
1948 ,p_organization_id => l_item_record.ORGANIZATION_ID
1949 ,p_revision_id => l_item_record.REVISION_ID
1950 ,p_change_id => p_change_id
1951 ,p_change_line_id => p_change_line_id
1952 ,p_revision_master_controlled => l_revision_master_controlled
1953 ,p_status_master_controlled => l_status_master_controlled
1954 ,p_is_master_org => l_is_master_org
1955 ,p_perform_security_check => p_perform_security_check
1956 ,x_return_status => x_return_status
1957 ,x_errorcode => x_errorcode
1958 ,x_msg_count => x_msg_count
1959 ,x_msg_data => x_msg_data
1960 );
1961 EXIT WHEN x_return_status <> FND_API.G_RET_STS_SUCCESS;
1962 END LOOP;
1963
1964 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1965 IF FND_API.To_Boolean(p_commit) THEN
1966 COMMIT WORK;
1967 END IF;
1968 ELSE
1969 IF x_msg_count <>1 THEN
1970 FND_MSG_PUB.Count_And_Get(
1971 p_encoded => FND_API.G_FALSE,
1972 p_count => x_msg_count,
1973 p_data => x_msg_data
1974 );
1975 END IF;
1976 END IF;
1977
1978 EXCEPTION
1979 WHEN OTHERS THEN
1980 IF c_get_pending_items%ISOPEN THEN
1981 CLOSE c_get_pending_items;
1982 END IF;
1983 IF FND_API.To_Boolean(p_commit) THEN
1984 ROLLBACK TO Implement_Pending_Changes_SP;
1985 END IF;
1986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1987 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1988 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1989 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1990 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1991 FND_MSG_PUB.Add;
1992 FND_MSG_PUB.Count_And_Get(
1993 p_encoded => FND_API.G_FALSE,
1994 p_count => x_msg_count,
1995 p_data => x_msg_data
1996 );
1997
1998 END Implement_Pending_Changes;
1999
2000
2001 PROCEDURE Implement_Pending_Changes_CP
2002 (
2003 ERRBUF OUT NOCOPY VARCHAR2
2004 , RETCODE OUT NOCOPY NUMBER
2005 , p_organization_id IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE
2006 , p_inventory_item_id IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE
2007 , p_revision_code IN MTL_ITEM_REVISIONS_B.REVISION%TYPE
2008 )
2009 IS
2010
2011 TYPE ORG_TABLE IS TABLE OF MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
2012 TYPE ITEM_TABLE IS TABLE OF MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
2013 TYPE REV_TABLE IS TABLE OF MTL_ITEM_REVISIONS_B.REVISION%TYPE;
2014
2015 l_revision_master_controlled VARCHAR2(1);
2016 l_status_master_controlled VARCHAR2(1);
2017 l_is_master_org VARCHAR2(1);
2018
2019 l_return_status VARCHAR2(1);
2020 l_errorcode NUMBER;
2021 l_msg_count NUMBER;
2022 l_msg_data VARCHAR2(4000);
2023
2024 l_orgs ORG_TABLE;
2025 l_items ITEM_TABLE;
2026 l_revs REV_TABLE;
2027 l_revision_id MTL_ITEM_REVISIONS_B.REVISION_ID%TYPE;
2028 l_master_org MTL_ITEM_REVISIONS_B.ORGANIZATION_ID%TYPE;
2029
2030 l_ret VARCHAR2(1);
2031 l_error_mesg VARCHAR2(4000);
2032
2033
2034 BEGIN
2035
2036 code_debug(' Implement Pending Changes from Concurrent Program ');
2037 SELECT
2038 DECODE(LOOKUP_CODE2, 1, FND_API.G_TRUE, 2, FND_API.G_FALSE, FND_API.G_FALSE) INTO l_status_master_controlled
2039 FROM
2040 MTL_ITEM_ATTRIBUTES_V
2041 WHERE
2042 ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
2043
2044 l_revision_master_controlled := FND_API.G_FALSE;
2045
2046 --If there is no organization id, then do everything
2047 IF p_organization_id IS NULL
2048 THEN
2049
2050 SELECT
2051 ORGANIZATION_ID BULK COLLECT INTO l_orgs
2052 FROM
2053 ORG_ACCESS_VIEW
2054 WHERE RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
2055 AND RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID;
2056
2057 ELSE
2058
2059 l_orgs := ORG_TABLE(p_organization_id);
2060
2061 END IF;
2062
2063 --For each org
2064 IF (l_orgs IS NOT NULL AND l_orgs.COUNT > 0)
2065 THEN
2066
2067 FOR org_index IN l_orgs.FIRST..l_orgs.LAST
2068 LOOP
2069
2070 BEGIN
2071
2072 --Select the master org
2073 SELECT
2074 MP.MASTER_ORGANIZATION_ID INTO l_master_org
2075 FROM
2076 MTL_PARAMETERS MP
2077 WHERE
2078 MP.ORGANIZATION_ID = l_orgs(org_index);
2079
2080 l_is_master_org := FND_API.G_FALSE;
2081
2082 IF l_master_org = l_orgs(org_index)
2083 THEN
2084 l_is_master_org := FND_API.G_TRUE;
2085 END IF;
2086
2087 --If item is null, then get them all
2088 IF p_inventory_item_id IS NULL
2089 THEN
2090
2091 SELECT
2092 INVENTORY_ITEM_ID BULK COLLECT INTO l_items
2093 FROM
2094 MTL_SYSTEM_ITEMS_B
2095 WHERE
2096 ORGANIZATION_ID = l_orgs(org_index);
2097
2098 ELSE
2099
2100 l_items := ITEM_TABLE(p_inventory_item_id);
2101
2102 END IF;
2103
2104 IF (l_items IS NOT NULL AND l_items.COUNT > 0)
2105 THEN
2106
2107 --For each item
2108 FOR item_index IN l_items.FIRST..l_items.LAST
2109 LOOP
2110
2111 BEGIN
2112
2113 IF p_revision_code = 'ALL' OR p_revision_code is NULL
2114 THEN
2115
2116 SELECT
2117 REVISION BULK COLLECT INTO l_revs
2118 FROM
2119 MTL_ITEM_REVISIONS_B
2120 WHERE ORGANIZATION_ID = l_orgs(org_index)
2121 AND INVENTORY_ITEM_ID = l_items(item_index);
2122
2123 --If it's all, then also add a null entry to mean no revision
2124 --(NULL will do the work of 'ALL' plus 'NONE')
2125 l_revs.EXTEND();
2126
2127 ELSIF p_revision_code = 'NONE'
2128 THEN
2129
2130 l_revs := REV_TABLE(NULL);
2131
2132 ELSE
2133
2134 l_revs := REV_TABLE(p_revision_code);
2135
2136 END IF;
2137
2138 IF (l_revs IS NOT NULL AND l_revs.COUNT > 0)
2139 THEN
2140
2141 FOR rev_index IN l_revs.FIRST..l_revs.LAST
2142 LOOP
2143
2144 BEGIN
2145
2146 --First we need to get the revision_id
2147 l_revision_id := NULL;
2148
2149 IF l_revs(rev_index) IS NOT NULL
2150 THEN
2151
2152 --Either the master or current
2153 IF FND_API.To_Boolean(l_revision_master_controlled)
2154 THEN
2155
2156 SELECT
2157 REVISION_ID INTO l_revision_id
2158 FROM
2159 MTL_ITEM_REVISIONS_B
2160 WHERE
2161 ORGANIZATION_ID = l_master_org
2162 AND INVENTORY_ITEM_ID = l_items(item_index)
2163 AND REVISION = l_revs(rev_index);
2164
2165 ELSE
2166
2167 SELECT
2168 REVISION_ID INTO l_revision_id
2169 FROM
2170 MTL_ITEM_REVISIONS_B
2171 WHERE
2172 ORGANIZATION_ID = l_orgs(org_index)
2173 AND INVENTORY_ITEM_ID = l_items(item_index)
2174 AND REVISION = l_revs(rev_index);
2175
2176 END IF; -- select rev id
2177
2178 END IF; -- if revision is not null
2179
2180 Implement_Pending_Changes
2181 (
2182 p_api_version => 1.0
2183 , p_inventory_item_id => l_items(item_index)
2184 , p_organization_id => l_orgs(org_index)
2185 , p_revision_id => l_revision_id
2186 , p_revision_master_controlled => l_revision_master_controlled
2187 , p_status_master_controlled => l_status_master_controlled
2188 , p_is_master_org => l_is_master_org
2189 , p_perform_security_check => FND_API.G_FALSE
2190 , x_return_status => l_return_status
2191 , x_errorcode => l_errorcode
2192 , x_msg_count => l_msg_count
2193 , x_msg_data => l_msg_data
2194 );
2195
2196 EXCEPTION
2197
2198 WHEN OTHERS
2199 THEN
2200 NULL;
2201
2202 END; -- rev block
2203
2204 END LOOP; -- for each rev
2205
2206 END IF;
2207
2208 EXCEPTION
2209
2210 WHEN OTHERS
2211 THEN
2212 NULL;
2213
2214 END; -- item block
2215
2216 END LOOP; -- for each item
2217
2218 END IF; -- if there are any items
2219
2220 EXCEPTION
2221
2222 WHEN OTHERS
2223 THEN
2224 NULL;
2225
2226 END; -- org block
2227
2228 END LOOP; -- for each org
2229
2230 END IF; -- if there are any orgs
2231
2232 RETCODE := G_SUCCESS;
2233 ERRBUF := FND_MESSAGE.Get_String('EGO', 'EGO_IPC_SUCCESS');
2234
2235 END Implement_Pending_Changes_CP;
2236
2237 END EGO_ITEM_LC_IMP_PC_PUB;
2238
[Home] [Help]