[Home] [Help]
PACKAGE BODY: APPS.EGO_LIFECYCLE_USER_PUB
Source
1 PACKAGE BODY EGO_LIFECYCLE_USER_PUB AS
2 /* $Header: EGOPLCUB.pls 120.4 2007/05/30 10:49:09 srajapar ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'EGO_LIFECYCLE_USER_PUB';
5 g_app_name CONSTANT VARCHAR2(3) := 'EGO';
6 g_current_user_id NUMBER := FND_GLOBAL.User_Id;
7 g_current_login_id NUMBER := FND_GLOBAL.Login_Id;
8 g_validation_error EXCEPTION;
9 g_same_sequence_error EXCEPTION;
10 g_project_assoc_type CONSTANT VARCHAR2(24) := 'EGO_ITEM_PROJ_ASSOC_TYPE';
11 g_lifecycle_tracking_code CONSTANT VARCHAR2(18) := 'LIFECYCLE_TRACKING';
12 g_promote CONSTANT VARCHAR2(7) := 'PROMOTE';
13 g_demote CONSTANT VARCHAR2(6) := 'DEMOTE';
14 g_plsql_err VARCHAR2(17) := 'EGO_PLSQL_ERR';
15 g_pkg_name_token VARCHAR2(8) := 'PKG_NAME';
16 g_api_name_token VARCHAR2(8) := 'API_NAME';
17 g_sql_err_msg_token VARCHAR2(11) := 'SQL_ERR_MSG';
18 g_not_allowed CONSTANT VARCHAR2(11) := 'NOT_ALLOWED';
19 g_co_required CONSTANT VARCHAR2(21) := 'CHANGE_ORDER_REQUIRED';
20
21
22 -- Private Function
23 ----------------------------------------------------------------------
24 PROCEDURE code_debug (p_msg IN VARCHAR2) IS
25 BEGIN
26 -- sri_debug ('EGOPLCUB - EGO_LIFECYCLE_USER_PUB '||p_msg);
27 RETURN;
28 EXCEPTION
29 WHEN OTHERS THEN
30 NULL;
31 END;
32
33 FUNCTION Has_Lifecycle_Tracking_Project
34 (
35 p_inventory_item_id IN NUMBER
36 , p_organization_id IN NUMBER
37 , p_revision IN VARCHAR2
38 )
39 RETURN BOOLEAN
40 IS
41
42 l_count NUMBER;
43 l_has_tracking_proj BOOLEAN;
44
45 BEGIN
46
47 IF p_revision IS NULL THEN
48
49 SELECT COUNT(1) INTO l_count
50 FROM ego_item_projects
51 WHERE inventory_item_id = p_inventory_item_id
52 AND organization_id = p_organization_id
53 AND revision_id IS NULL
54 AND association_type = G_PROJECT_ASSOC_TYPE
55 AND association_code = G_LIFECYCLE_TRACKING_CODE
56 AND ROWNUM = 1;
57
58 ELSE
59
60 SELECT COUNT(1) INTO l_count
61 FROM EGO_ITEM_PROJECTS a
62 WHERE inventory_item_id = p_inventory_item_id
63 AND organization_id = p_organization_id
64 AND EXISTS
65 (SELECT revision_id
66 FROM mtl_item_revisions_b
67 WHERE inventory_item_id = p_inventory_item_id
68 AND organization_id = p_organization_id
69 AND revision = p_revision
70 )
71 AND association_type = G_PROJECT_ASSOC_TYPE
72 AND association_code = G_LIFECYCLE_TRACKING_CODE
73 AND ROWNUM = 1;
74
75 END IF;
76
77 IF (l_count > 0) THEN
78 l_has_tracking_proj := TRUE;
79 ELSE
80 l_has_tracking_proj := FALSE;
81 END IF;
82
83 RETURN l_has_tracking_proj;
84 END Has_Lifecycle_Tracking_Project;
85
86 ----------------------------------------------------------------------
87
88
89 FUNCTION Check_CM_Existance RETURN VARCHAR2 IS
90 ----------------------------------------------------------------------------
91 -- Start OF comments
92 -- API name : Check_Change_Management_Existance
93 -- TYPE : Public
94 -- Pre-reqs : None
95 -- FUNCTION : Check whether CM is installed and active
96 -- (the table ENG_CHANGE_MGMT_TYPES_VL is populated)
97 --
98 -- Return Parameter:
99 -- 'S' if view eng_change_mgmt_types_vl is populated
100 -- 'E' in all other cases
101 --
102 ----------------------------------------------------------------------------
103 l_product_exists VARCHAR2(1);
104 --l_status fnd_product_installations.status%TYPE;
105 --l_count NUMBER;
106
107 /*CURSOR c_product_check (cp_app_short_name IN VARCHAR2) IS
108 SELECT inst.status
109 FROM fnd_product_installations inst, fnd_application app
110 WHERE inst.application_id = app.application_id
111 AND app.application_short_name = cp_app_short_name
112 AND inst.status <> 'N';*/
113
114 BEGIN
115 -- Checking whether the product is installed.
116 /*OPEN c_product_check (cp_app_short_name => 'ENG');
117 FETCH c_product_check INTO l_status;
118 CLOSE c_product_check;
119 IF (l_status = 'I') THEN
120 -- package exists and DBI is installed
121 -- check if a record exists in eng_change_mgmt_types_vl
122 SELECT count(*)
123 INTO l_count
124 FROM eng_change_mgmt_types_vl
125 WHERE disable_flag = 'N';
126 IF l_count <> 0 THEN
127 l_product_exists := FND_API.G_RET_STS_SUCCESS;
128 END IF;
129 END IF;
130 RETURN (l_product_exists);
131 EXCEPTION
132 WHEN OTHERS THEN
133 IF c_product_check%ISOPEN THEN
134 CLOSE c_product_check;
135 END IF;
136 RETURN (l_product_exists);*/
137
138 l_product_exists := EGO_COMMON_PVT.Is_EGO_Installed(1.0, '');
139 IF (l_product_exists = 'T') THEN
140 RETURN FND_API.G_RET_STS_SUCCESS;
141 ELSE
142 RETURN FND_API.G_RET_STS_ERROR;
143 END IF;
144 EXCEPTION
145 WHEN OTHERS THEN
146 RETURN FND_API.G_RET_STS_ERROR;
147
148 END Check_CM_Existance;
149
150 ----------------------------------------------------------------------
151
152 -- Public Procedures
153 ----------------------------------------------------------------------
154
155 FUNCTION get_change_name (p_change_id IN NUMBER) RETURN VARCHAR2 IS
156 l_change_notice VARCHAR2(2000);
157 l_dynamic_sql VARCHAR2(2000);
158
159 BEGIN
160 l_change_notice := NULL;
161 IF (Check_CM_Existance() = FND_API.G_RET_STS_SUCCESS) THEN
162 IF (p_change_id IS NULL) THEN
163 l_change_notice := NULL;
164 ELSE
165 --Bug#5043988 : Literal Fix
166 l_dynamic_sql := ' SELECT change_notice FROM eng_engineering_changes'
167 ||' WHERE change_id = :p_change_id' ; --||TO_CHAR(p_change_id);
168 EXECUTE IMMEDIATE l_dynamic_sql INTO l_change_notice USING p_change_id ;
169 --Bug#5043988 : Literal Fix
170 END IF;
171 END IF;
172 RETURN l_change_notice;
173 EXCEPTION
174 WHEN OTHERS THEN
175 RETURN NULL;
176 END get_change_name;
177
178
179 PROCEDURE Check_Delete_Project_OK
180 (
181 p_api_version IN NUMBER
182 , p_project_id IN NUMBER
183 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
184 , x_delete_ok OUT NOCOPY VARCHAR2
185 , x_return_status OUT NOCOPY VARCHAR2
186 , x_errorcode OUT NOCOPY NUMBER
187 , x_msg_count OUT NOCOPY NUMBER
188 , x_msg_data OUT NOCOPY VARCHAR2
189 )
190 IS
191
192 l_api_version NUMBER;
193 l_count VARCHAR2(1);
194 l_api_name VARCHAR2(30);
195 l_message VARCHAR2(4000);
196
197 BEGIN
198
199 l_api_version := 1.0;
200 l_api_name := 'Check_Delete_Project_OK';
201 --Standard checks
202 IF NOT FND_API.Compatible_API_Call (l_api_version
203 ,p_api_version
204 ,l_api_name
205 ,g_pkg_name)
206 THEN
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 END IF;
209
210 -- Initialize message list if p_init_msg_list is set to TRUE
211 IF FND_API.To_Boolean(p_init_msg_list) THEN
212 FND_MSG_PUB.Initialize;
213 END IF;
214
215 --Check if there are any entries for it in EGO_ITEM_PROJECTS
216 SELECT
217 COUNT(*) INTO l_count
218 FROM
219 EGO_ITEM_PROJECTS
220 WHERE
221 PROJECT_ID = p_project_id;
222
223 IF (l_count > 0)
224 THEN
225 x_delete_ok := FND_API.G_FALSE;
226 l_message := 'EGO_ITEM_ASSOCIATED_PR';
227 END IF;
228
229 IF (l_message IS NOT NULL)
230 THEN
231 FND_MESSAGE.Set_Name(g_app_name, l_message);
232 FND_MSG_PUB.Add;
233 FND_MSG_PUB.Count_And_Get(
234 p_encoded => FND_API.G_FALSE,
235 p_count => x_msg_count,
236 p_data => x_msg_data
237 );
238 x_return_status := FND_API.G_RET_STS_ERROR;
239 ELSE
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241 END IF;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 x_delete_ok := FND_API.G_FALSE;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
248 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
249 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
250 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
251 FND_MSG_PUB.Add;
252 FND_MSG_PUB.Count_And_Get(
253 p_encoded => FND_API.G_FALSE,
254 p_count => x_msg_count,
255 p_data => x_msg_data
256 );
257
258 END Check_Delete_Project_OK;
259
260 ----------------------------------------------------------------------
261
262 PROCEDURE Get_Policy_For_Revise
263 (
264 p_api_version IN NUMBER
265 , p_inventory_item_id IN NUMBER
266 , p_organization_id IN NUMBER
267 , p_curr_phase_id IN NUMBER
268 , x_policy_code OUT NOCOPY VARCHAR2
269 , x_return_status OUT NOCOPY VARCHAR2
270 , x_errorcode OUT NOCOPY NUMBER
271 , x_msg_count OUT NOCOPY NUMBER
272 , x_msg_data OUT NOCOPY VARCHAR2
273 )
274 IS
275
276 BEGIN
277
278 Get_Policy_For_Phase_Change
279 (
280 p_api_version
281 ,NULL
282 ,p_inventory_item_id
283 ,p_organization_id
284 ,p_curr_phase_id
285 ,NULL
286 ,'REVISE'
287 ,NULL
288 ,x_policy_code
289 ,x_return_status
290 ,x_errorcode
291 ,x_msg_count
292 ,x_msg_data
293 );
294
295 END Get_Policy_For_Revise;
296
297 ----------------------------------------------------------------------
298
299 PROCEDURE Get_Policy_For_Phase_Change
300 (
301 p_api_version IN NUMBER
302 , p_project_id IN NUMBER DEFAULT NULL
303 , p_inventory_item_id IN NUMBER DEFAULT NULL
304 , p_organization_id IN NUMBER DEFAULT NULL
305 , p_curr_phase_id IN NUMBER
306 , p_future_phase_id IN NUMBER
307 , p_phase_change_code IN VARCHAR2
308 , p_lifecycle_id IN NUMBER
309 , x_policy_code OUT NOCOPY VARCHAR2
310 , x_return_status OUT NOCOPY VARCHAR2
311 , x_errorcode OUT NOCOPY NUMBER
312 , x_msg_count OUT NOCOPY NUMBER
313 , x_msg_data OUT NOCOPY VARCHAR2
314 )
315 IS
316
317 l_api_version NUMBER;
318 l_api_name VARCHAR2(30);
319 l_curr_sequence NUMBER;
320 l_future_sequence NUMBER;
321 l_phase_change_code EGO_LCPHASE_POLICY.ACTION_CODE%TYPE;
322 l_inventory_item_id MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
323 l_organization_id MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
324 l_catalog_category_id MTL_SYSTEM_ITEMS_B.ITEM_CATALOG_GROUP_ID%TYPE;
325 l_current_catalog_category_id MTL_SYSTEM_ITEMS_B.ITEM_CATALOG_GROUP_ID%TYPE;
326 l_associated_here VARCHAR2(1);
327
328 l_policy_object_name VARCHAR2(30);
329 l_policy_code VARCHAR2(30);
330 l_attr_object_name VARCHAR2(30);
331 l_attribute_code VARCHAR2(30);
332 l_attr_num NUMBER;
333 l_dynamic_sql VARCHAR2(32767);
334
335 -- CURSOR ALL_CATALOG_CATEGORY_IDS
336 -- (
337 -- cp_catalog_category_id IN NUMBER
338 -- ) IS
339 -- SELECT
340 -- ITEM_CATALOG_GROUP_ID
341 -- FROM
342 -- MTL_ITEM_CATALOG_GROUPS_B
343 -- CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
344 -- START WITH ITEM_CATALOG_GROUP_ID = cp_catalog_category_id;
345 --
346 CURSOR c_get_assoc_category_id (cp_catalog_category_id IN NUMBER
347 ,cp_lifecycle_id IN NUMBER
348 ) IS
349 SELECT ic.item_catalog_group_id
350 FROM MTL_ITEM_CATALOG_GROUPS_B ic
351 WHERE EXISTS (
352 SELECT olc.object_classification_code CatalogId
353 FROM ego_obj_type_lifecycles olc, fnd_objects o
354 WHERE o.obj_name = 'EGO_ITEM'
355 AND olc.object_id = o.object_id
356 AND olc.lifecycle_id = cp_lifecycle_id
357 AND olc.object_classification_code = item_catalog_group_id
358 )
359 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
360 START WITH item_catalog_group_id = cp_catalog_category_id;
361 BEGIN
362 l_api_name := 'Get_Policy_For_Phase_Change';
363 l_api_version := 1.0;
364 l_policy_object_name := 'CATALOG_LIFECYCLE_PHASE';
365 l_policy_code := 'CHANGE_POLICY';
366 l_attr_object_name := 'EGO_CATALOG_GROUP';
367 l_attribute_code := 'PROMOTE_DEMOTE';
368 code_debug (l_api_name ||' started project id '|| p_project_id ||' item id '||p_inventory_item_id||' organization id '||p_organization_id||' curr phase id '||p_curr_phase_id ||' future phase id '||p_future_phase_id);
369
370 --Standard checks
371 IF NOT FND_API.Compatible_API_Call (l_api_version
372 ,p_api_version
373 ,l_api_name
374 ,g_pkg_name)
375 THEN
376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
377 END IF;
378
379 -- First we need to determine the phase change code if it isn't passed in
380 IF (p_phase_change_code IS NULL)
381 THEN
382
383 SELECT
384 P1.DISPLAY_SEQUENCE INTO l_curr_sequence
385 FROM
386 PA_PROJ_ELEMENT_VERSIONS P1
387 ,PA_PROJ_ELEMENT_VERSIONS P2
388 WHERE
389 P1.PROJ_ELEMENT_ID = p_curr_phase_id
390 AND P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
391 AND P2.PROJ_ELEMENT_ID = p_lifecycle_id;
392
393 SELECT P1.DISPLAY_SEQUENCE INTO l_future_sequence
394 FROM
395 PA_PROJ_ELEMENT_VERSIONS P1
396 ,PA_PROJ_ELEMENT_VERSIONS P2
397 WHERE
398 P1.PROJ_ELEMENT_ID = p_future_phase_id
399 AND P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
400 AND P2.PROJ_ELEMENT_ID = p_lifecycle_id;
401
402 IF (l_curr_sequence > l_future_sequence)
403 THEN
404 l_phase_change_code := g_demote;
405 ELSIF (l_future_sequence > l_curr_sequence)
406 THEN
407 l_phase_change_code := g_promote;
408 ELSE
409 RAISE g_same_sequence_error;
410 END IF;
411
412 ELSE
413 l_phase_change_code := p_phase_change_code;
414 END IF;
415
416 --First get the item for the project if they are null
417 l_inventory_item_id := p_inventory_item_id;
418 l_organization_id := p_organization_id;
419
420 IF (l_inventory_item_id IS NULL OR l_organization_id IS NULL) THEN
421
422 SELECT
423 INVENTORY_ITEM_ID, ORGANIZATION_ID
424 INTO
425 l_inventory_item_id, l_organization_id
426 FROM
427 EGO_ITEM_PROJECTS
428 WHERE
429 PROJECT_ID = p_project_id
430 AND ASSOCIATION_TYPE = g_project_assoc_type
431 AND ASSOCIATION_CODE = g_lifecycle_tracking_code
432 AND ROWNUM = 1;
433
434 END IF;
435
436 --We need to get the catalog category id
437
438 SELECT
439 ITEM_CATALOG_GROUP_ID into l_current_catalog_category_id
440 FROM
441 MTL_SYSTEM_ITEMS_B
442 WHERE
443 ORGANIZATION_ID = l_organization_id
444 AND INVENTORY_ITEM_ID = l_inventory_item_id;
445
446 --Check which catalog category actually has the lifecycle associated with it
447 OPEN c_get_assoc_category_id (cp_lifecycle_id => p_lifecycle_id
448 ,cp_catalog_category_id => l_current_catalog_category_id
449 );
450 FETCH c_get_assoc_category_id INTO l_catalog_category_id;
451 CLOSE c_get_assoc_category_id;
452
453 code_debug (l_api_name ||' cat at which lc associated '||l_catalog_category_id);
454 IF (l_phase_change_code = 'REVISE') THEN
455 l_attr_num := 3;
456 ELSIF (l_phase_change_code = g_demote) THEN
457 l_attr_num := 2;
458 ELSIF (l_phase_change_code = g_promote) THEN
459 l_attr_num := 1;
460 END IF;
461
462 IF (Check_CM_Existance() = FND_API.G_RET_STS_SUCCESS) THEN
463 /*ENG_CHANGE_POLICY_PKG.GetChangePolicy
464 ( p_policy_object_name => l_policy_object_name
465 , p_policy_code => l_policy_code
466 , p_policy_pk1_value => l_catalog_category_id
467 , p_policy_pk2_value => p_lifecycle_id
468 , p_policy_pk3_value => p_curr_phase_id
469 , p_policy_pk4_value => null
470 , p_policy_pk5_value => null
471 , p_attribute_object_name => l_attr_object_name
472 , p_attribute_code => l_attribute_code
473 , p_attribute_value => l_attr_num
474 , x_policy_value => x_policy_code
475 );*/
476
477 l_dynamic_sql :=
478 ' BEGIN '||
479 ' ENG_CHANGE_POLICY_PKG.GetChangePolicy '||
480 ' ( '||
481 ' p_policy_object_name => :l_policy_object_name '||
482 ' , p_policy_code => :l_policy_code '||
483 ' , p_policy_pk1_value => TO_CHAR(:l_catalog_category_id) '||
484 ' , p_policy_pk2_value => TO_CHAR(:p_lifecycle_id) '||
485 ' , p_policy_pk3_value => TO_CHAR(:p_curr_phase_id) '||
486 ' , p_policy_pk4_value => NULL '||
487 ' , p_policy_pk5_value => NULL '||
488 ' , p_attribute_object_name => :l_attr_object_name '||
489 ' , p_attribute_code => :l_attribute_code '||
490 ' , p_attribute_value => :l_attr_num '||
491 ' , x_policy_value => :x_policy_code '||
492 ' ); '||
493 ' END;';
494
495 EXECUTE IMMEDIATE l_dynamic_sql
496 USING IN l_policy_object_name,
497 IN l_policy_code,
498 IN l_catalog_category_id,
499 IN p_lifecycle_id,
500 IN p_curr_phase_id,
501 IN l_attr_object_name,
502 IN l_attribute_code,
503 IN l_attr_num,
504 OUT x_policy_code;
505
506 END IF;
507 code_debug (l_api_name ||' policy code returned '||x_policy_code);
508 /*
509 SELECT
510 POLICY_CODE INTO x_policy_code
511 FROM
512 EGO_LCPHASE_POLICY
513 WHERE
514 PHASE_ID = p_curr_phase_id
515 AND ACTION_CODE = l_phase_change_code
516 AND LIFECYCLE_ID = p_lifecycle_id
517 AND ITEM_CATALOG_GROUP_ID = l_catalog_category_id;
518 */
519 EXCEPTION
520 WHEN g_same_sequence_error THEN
521 x_policy_code := NULL;
522 WHEN NO_DATA_FOUND THEN
523 x_policy_code := NULL;
524 WHEN OTHERS THEN
525 IF c_get_assoc_category_id%ISOPEN THEN
526 CLOSE c_get_assoc_category_id;
527 END IF;
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
530 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
531 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
532 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
533 FND_MSG_PUB.Add;
534 FND_MSG_PUB.Count_And_Get(
535 p_encoded => FND_API.G_FALSE,
536 p_count => x_msg_count,
537 p_data => x_msg_data
538 );
539
540 END Get_Policy_For_Phase_Change;
541
542 ----------------------------------------------------------------------
543
544 PROCEDURE Get_Policy_For_Phase_Change
545 (
546 p_api_version IN NUMBER
547 , p_project_id IN NUMBER
548 , p_curr_phase_id IN NUMBER
549 , p_future_phase_id IN NUMBER
550 , p_phase_change_code IN VARCHAR2
551 , p_lifecycle_id IN NUMBER
552 , x_policy_code OUT NOCOPY VARCHAR2
553 , x_error_message OUT NOCOPY VARCHAR2
554 , x_return_status OUT NOCOPY VARCHAR2
555 , x_errorcode OUT NOCOPY NUMBER
556 , x_msg_count OUT NOCOPY NUMBER
557 , x_msg_data OUT NOCOPY VARCHAR2
558 )
559 IS
560
561 BEGIN
562 code_debug (' Get Policy for Phase Change -- Projects version called ');
563 Get_Policy_For_Phase_Change
564 (
565 p_api_version
566 ,p_project_id
567 ,NULL
568 ,NULL
569 ,p_curr_phase_id
570 ,p_future_phase_id
571 ,p_phase_change_code
572 ,p_lifecycle_id
573 ,x_policy_code
574 ,x_return_status
575 ,x_errorcode
576 ,x_msg_count
577 ,x_msg_data
578 );
579
580 -- Return an error message if the policy is not allowed
581 IF ((x_policy_code = g_not_allowed) OR (x_policy_code = g_co_required)) THEN
582 x_error_message := 'EGO_PHASE_CHANGE_NOT_ALLOWED';
583 END IF;
584
585 END Get_Policy_For_Phase_Change;
586
587 ----------------------------------------
588
589 PROCEDURE Check_Lc_Tracking_Project
590 (
591 p_api_version IN NUMBER
592 , p_project_id IN NUMBER
593 , x_is_lifecycle_tracking OUT NOCOPY VARCHAR2
594 , x_return_status OUT NOCOPY VARCHAR2
595 , x_errorcode OUT NOCOPY NUMBER
596 , x_msg_count OUT NOCOPY NUMBER
597 , x_msg_data OUT NOCOPY VARCHAR2
598 )
599 IS
600
601 l_api_version NUMBER;
602 l_api_name VARCHAR2(30);
603 l_count NUMBER;
604
605 BEGIN
606
607 l_api_version := 1.0;
608 l_api_name := 'Check_Lc_Tracking_Project';
609 --Standard checks
610 IF NOT FND_API.Compatible_API_Call (l_api_version
611 ,p_api_version
612 ,l_api_name
613 ,g_pkg_name)
614 THEN
615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
616 END IF;
617
618 -- This procedure returns TRUE if and only if the project ID
619 -- passed in belongs to a project that is:
620 -- 1). associated to an Item in the EGO_ITEM_PROJECTS table, and
621 -- 2). associated as a Lifecycle tracking project.
622
623 SELECT COUNT(1) INTO l_count
624 FROM EGO_ITEM_PROJECTS
625 WHERE PROJECT_ID = p_project_id
626 AND ASSOCIATION_TYPE = g_project_assoc_type
627 AND ASSOCIATION_CODE = g_lifecycle_tracking_code
628 AND ROWNUM = 1;
629
630 IF (l_count > 0) THEN
631 x_is_lifecycle_tracking := FND_API.G_TRUE;
632 ELSE
633 x_is_lifecycle_tracking := FND_API.G_FALSE;
634 END IF;
635
636 EXCEPTION
637 WHEN OTHERS THEN
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
640 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
641 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
642 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
643 FND_MSG_PUB.Add;
644 FND_MSG_PUB.Count_And_Get(
645 p_encoded => FND_API.G_FALSE,
646 p_count => x_msg_count,
647 p_data => x_msg_data
648 );
649
650 END Check_Lc_Tracking_Project;
651
652 ----------------------------------------------------------------------
653
654 PROCEDURE Delete_All_Item_Assocs
655 (
656 p_api_version IN NUMBER
657 , p_project_id IN NUMBER
658 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
659 , x_return_status OUT NOCOPY VARCHAR2
660 , x_errorcode OUT NOCOPY NUMBER
661 , x_msg_count OUT NOCOPY NUMBER
662 , x_msg_data OUT NOCOPY VARCHAR2
663 )
664 IS
665 l_api_version NUMBER;
666 l_api_name VARCHAR2(30);
667
668 CURSOR c_item_project (cp_project_id IN NUMBER) IS
669 SELECT inventory_item_id, organization_id
670 FROM ego_item_projects
671 WHERE project_id = cp_project_id;
672
673 BEGIN
674
675 l_api_version := 1.0;
676 l_api_name := 'Delete_All_Item_Assocs';
677 --Standard checks
678 IF NOT FND_API.Compatible_API_Call (l_api_version
679 ,p_api_version
680 ,l_api_name
681 ,g_pkg_name)
682 THEN
683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
684 END IF;
685
686 -- 4052565 perform security check
687 FOR l_item_projs IN c_item_project(cp_project_id => p_project_id) LOOP
688 IF NOT EGO_ITEM_PVT.has_role_on_item
689 (p_function_name => 'EGO_CREATE_ITEM_LC_TRACK_PROJ'
690 ,p_inventory_item_id => l_item_projs.inventory_item_id
691 ,p_item_number => NULL
692 ,p_organization_id => l_item_projs.organization_id
693 ,p_organization_name => NULL
694 ,p_user_id => NULL
695 ,p_party_id => NULL
696 ,p_set_message => FND_API.G_TRUE
697 ) THEN
698 x_return_status := FND_API.G_RET_STS_ERROR;
699 FND_MSG_PUB.Count_And_Get(
700 p_encoded => FND_API.G_FALSE,
701 p_count => x_msg_count,
702 p_data => x_msg_data
703 );
704 RETURN;
705 END IF;
706 END LOOP;
707 DELETE
708 FROM EGO_ITEM_PROJECTS
709 WHERE PROJECT_ID = p_project_id;
710
711 -- Standard check of p_commit.
712 IF FND_API.To_Boolean(p_commit)
713 THEN
714 COMMIT WORK;
715 END IF;
716
717 x_return_status := FND_API.G_RET_STS_SUCCESS;
718
719 EXCEPTION
720 WHEN NO_DATA_FOUND THEN
721 x_return_status := FND_API.G_RET_STS_SUCCESS;
722 WHEN OTHERS THEN
723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
724 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
725 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
726 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
727 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
728 FND_MSG_PUB.Add;
729 FND_MSG_PUB.Count_And_Get(
730 p_encoded => FND_API.G_FALSE,
731 p_count => x_msg_count,
732 p_data => x_msg_data
733 );
734
735 END Delete_All_Item_Assocs;
736
737 ----------------------------------------------------------------------
738
739 PROCEDURE Sync_Phase_Change
740 (
741 p_api_version IN NUMBER
742 , p_project_id IN NUMBER
743 , p_lifecycle_id IN NUMBER
744 , p_phase_id IN NUMBER
745 , p_effective_date IN DATE
746 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_FALSE
747 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
748 , x_return_status OUT NOCOPY VARCHAR2
749 , x_errorcode OUT NOCOPY NUMBER
750 , x_msg_count OUT NOCOPY NUMBER
751 , x_msg_data OUT NOCOPY VARCHAR2
752 )
753 IS
754
755 l_api_version NUMBER;
756 l_api_name VARCHAR2(30);
757 l_status_code EGO_LCPHASE_ITEM_STATUS.ITEM_STATUS_CODE%TYPE;
758 l_phase_code EGO_LCPHASE_ITEM_STATUS.PHASE_CODE%TYPE;
759 -- l_revision_id MTL_ITEM_REVISIONS_B.REVISION_ID%TYPE;
760 l_revision MTL_ITEM_REVISIONS_B.REVISION%TYPE;
761 l_current_phase_id MTL_ITEM_REVISIONS_B.CURRENT_PHASE_ID%TYPE;
762
763 l_revision_master_controlled VARCHAR2(1);
764 l_status_master_controlled VARCHAR2(1);
765 l_is_master_org VARCHAR2(1);
766
767 l_return_status VARCHAR2(1);
768 l_error_code NUMBER;
769 l_msg_count NUMBER;
770 l_msg_data VARCHAR2(4000);
771
772
773 CURSOR l_item_revisions IS
774 SELECT
775 inventory_item_id
776 ,organization_id
777 -- ,revision
778 ,revision_id
779 FROM EGO_ITEM_PROJECTS proj
780 WHERE project_id = p_project_id
781 AND association_type = G_PROJECT_ASSOC_TYPE
782 AND association_code = G_LIFECYCLE_TRACKING_CODE
783 -- sync phase changes of items which are not in the same phase of project
784 AND ( (revision_id IS NULL
785 AND NOT EXISTS
786 (SELECT 'X'
787 FROM mtl_system_items_b item
788 WHERE item.inventory_item_id = proj.inventory_item_id
789 AND item.organization_id = proj.organization_id
790 AND item.lifecycle_id = p_lifecycle_id
791 AND item.current_phase_id = p_phase_id
792 )
793 )
794 OR
795 (revision_id IS NOT NULL
796 AND NOT EXISTS
797 (SELECT 'X'
798 FROM mtl_item_revisions_b rev
799 WHERE rev.inventory_item_id = proj.inventory_item_id
800 AND rev.organization_id = proj.organization_id
801 AND rev.revision_id = proj.revision_id
802 AND rev.lifecycle_id = p_lifecycle_id
803 AND rev.current_phase_id = p_phase_id
804 )
805 )
806 );
807
808
809 BEGIN
810 l_api_version := 1.0;
811 l_api_name := 'Proj_Sync_Phase_Change';
812 code_debug (l_api_name ||' started p_project_id '||p_project_id||' lc id '||p_lifecycle_id ||' phase id '||p_phase_id);
813 code_debug (l_api_name ||' p_effective_date '||to_char(p_effective_date,'DD-MON-YYYY HH24:MI:SS'));
814
815 --Standard checks
816 IF NOT FND_API.Compatible_API_Call (l_api_version
817 ,p_api_version
818 ,l_api_name
819 ,g_pkg_name)
820 THEN
821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
822 END IF;
823
824 IF FND_API.To_Boolean(p_commit) THEN
825 SAVEPOINT Sync_Phase_Change_PUB;
826 END IF;
827
828 -- Initialize message list if p_init_msg_list is set to TRUE
829 IF FND_API.To_Boolean(p_init_msg_list) THEN
830 FND_MSG_PUB.Initialize;
831 END IF;
832
833 l_revision_master_controlled := FND_API.g_false;
834 l_status_master_controlled := EGO_ITEM_LC_IMP_PC_PUB.get_master_controlled_status();
835
836 FOR l_item_record IN l_item_revisions LOOP
837 code_debug (l_api_name ||' creating pending phase change for item '||l_item_record.INVENTORY_ITEM_ID||'in org '||l_item_record.organization_id||' revision id '||l_item_record.REVISION_id);
838 -- 4052565 perform security check
839
840 -- validate the current status code in the new phase; if current status exists
841 -- in the new phase, keep it; otherwise, use the default status
842 SELECT msi.INVENTORY_ITEM_STATUS_CODE
843 INTO l_status_code
844 FROM MTL_SYSTEM_ITEMS_B msi
845 WHERE msi.INVENTORY_ITEM_ID = l_item_record.INVENTORY_ITEM_ID
846 AND msi.ORGANIZATION_ID = l_item_record.ORGANIZATION_ID;
847
848 BEGIN
849 SELECT status.ITEM_STATUS_CODE
850 INTO l_status_code
851 FROM EGO_LCPHASE_ITEM_STATUS status
852 ,PA_EGO_PHASES_V phases
853 WHERE phases.PROJ_ELEMENT_ID = p_phase_id
854 AND status.PHASE_CODE = phases.PHASE_CODE
855 AND status.ITEM_STATUS_CODE = l_status_code;
856 EXCEPTION
857 WHEN NO_DATA_FOUND THEN
858 l_status_code := NULL;
859 END;
860
861 EGO_ITEM_LC_IMP_PC_PUB.Create_Pending_Phase_Change
862 (p_api_version => p_api_version
863 ,p_commit => FND_API.G_FALSE
864 ,p_inventory_item_id => l_item_record.INVENTORY_ITEM_ID
865 ,p_organization_id => l_item_record.ORGANIZATION_ID
866 ,p_effective_date => p_effective_date
867 ,p_pending_flag => NULL
868 ,p_revision => NULL
869 ,p_revision_id => l_item_record.revision_id
870 ,p_lifecycle_id => p_lifecycle_id
871 ,p_phase_id => p_phase_id
872 ,p_status_code => l_status_code
873 ,p_change_id => NULL
874 ,p_change_line_id => NULL
875 ,p_perform_security_check => FND_API.G_TRUE
876 ,x_return_status => x_return_status
877 ,x_errorcode => x_errorcode
878 ,x_msg_count => x_msg_count
879 ,x_msg_data => x_msg_data
880 );
881 code_debug (l_api_name ||' creating pending phase change for item returned with status '||x_return_status);
882 EXIT WHEN x_return_status <> FND_API.G_RET_STS_SUCCESS;
883
884 l_is_master_org :=
885 EGO_ITEM_LC_IMP_PC_PUB.get_master_org_status(l_item_record.ORGANIZATION_ID);
886 -- l_revision_id :=
887 -- EGO_ITEM_LC_IMP_PC_PUB.get_revision_id
888 -- (p_inventory_item_id => l_item_record.INVENTORY_ITEM_ID
889 -- ,p_organization_id => l_item_record.ORGANIZATION_ID
890 -- ,p_revision => l_item_record.REVISION
891 -- );
892 code_debug (l_api_name ||' creating implement pending phase change ');
893 -- 4052565 perform security check
894 EGO_ITEM_LC_IMP_PC_PUB.Implement_Pending_Changes
895 (p_api_version => p_api_version
896 ,p_inventory_item_id => l_item_record.INVENTORY_ITEM_ID
897 ,p_organization_id => l_item_record.ORGANIZATION_ID
898 ,p_revision_id => l_item_record.REVISION_ID
899 ,p_revision_master_controlled => l_revision_master_controlled
900 ,p_status_master_controlled => l_status_master_controlled
901 ,p_is_master_org => l_is_master_org
902 ,p_perform_security_check => FND_API.G_FALSE
903 ,x_return_status => x_return_status
904 ,x_errorcode => x_errorcode
905 ,x_msg_count => x_msg_count
906 ,x_msg_data => x_msg_data
907 );
908 code_debug (l_api_name ||' returning implement pending phase change with status '||x_return_status||' msg count'||x_msg_count||' errorcode '||x_errorcode);
909 EXIT WHEN x_return_status <> FND_API.G_RET_STS_SUCCESS;
910 --
911 -- Commented as a part of fix for 3371749
912 --
913 -- Sync_Phase_Change
914 -- (
915 -- p_api_version => p_api_version
916 -- , p_organization_id => l_item_record.ORGANIZATION_ID
917 -- , p_inventory_item_id => l_item_record.INVENTORY_ITEM_ID
918 -- , p_revision => l_item_record.REVISION
919 -- , p_lifecycle_id => p_lifecycle_id
920 -- , p_phase_id => p_phase_id
921 -- , p_effective_date => p_effective_date
922 -- , p_init_msg_list => p_init_msg_list
923 -- , p_commit => p_commit
924 -- , x_return_status => x_return_status
925 -- , x_errorcode => x_errorcode
926 -- , x_msg_count => x_msg_count
927 -- , x_msg_data => x_msg_data
928 -- );
929 --
930 END LOOP;
931
932 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
933 -- Standard check of p_commit.
934 IF FND_API.To_Boolean(p_commit) THEN
935 COMMIT WORK;
936 END IF;
937 ELSE
938 IF FND_API.To_Boolean(p_commit) THEN
939 ROLLBACK TO Sync_Phase_Change_PUB;
940 END IF;
941 code_debug (l_api_name ||' returning with msg count'||x_msg_count);
942 IF NOT x_msg_count = 1 THEN
943 FND_MSG_PUB.Count_And_Get(
944 p_encoded => FND_API.G_FALSE,
945 p_count => x_msg_count,
946 p_data => x_msg_data
947 );
948 END IF;
949 END IF;
950
951
952 EXCEPTION
953 WHEN FND_API.G_EXC_ERROR THEN
954 IF FND_API.To_Boolean(p_commit) THEN
955 ROLLBACK TO Sync_Phase_Change_PUB;
956 END IF;
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 IF NOT x_msg_count = 1 THEN
959 FND_MSG_PUB.Count_And_Get(
960 p_encoded => FND_API.G_FALSE,
961 p_count => x_msg_count,
962 p_data => x_msg_data
963 );
964 END IF;
965 WHEN OTHERS THEN
966 IF FND_API.To_Boolean(p_commit) THEN
967 ROLLBACK TO Sync_Phase_Change_PUB;
968 END IF;
969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
970 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
971 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
972 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
973 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
974 FND_MSG_PUB.Add;
975 FND_MSG_PUB.Count_And_Get(
976 p_encoded => FND_API.G_FALSE,
977 p_count => x_msg_count,
978 p_data => x_msg_data
979 );
980
981 END Sync_Phase_Change;
982
983
984 ----------------------------------------------------------------------
985 PROCEDURE Sync_Phase_Change
986 (
987 p_api_version IN NUMBER
988 , p_organization_id IN NUMBER
989 , p_inventory_item_id IN NUMBER
990 , p_revision IN VARCHAR2 DEFAULT null
991 , p_lifecycle_id IN NUMBER
992 , p_phase_id IN NUMBER
993 , p_effective_date IN DATE
994 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_FALSE
995 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
996 , x_return_status OUT NOCOPY VARCHAR2
997 , x_errorcode OUT NOCOPY NUMBER
998 , x_msg_count OUT NOCOPY NUMBER
999 , x_msg_data OUT NOCOPY VARCHAR2
1000 )
1001 IS
1002
1003 l_api_version NUMBER;
1004 l_api_name VARCHAR2(30);
1005 l_status_code EGO_LCPHASE_ITEM_STATUS.ITEM_STATUS_CODE%TYPE;
1006 l_phase_code EGO_LCPHASE_ITEM_STATUS.PHASE_CODE%TYPE;
1007 l_revision_id MTL_ITEM_REVISIONS_B.REVISION_ID%TYPE;
1008 l_current_phase_id MTL_ITEM_REVISIONS_B.CURRENT_PHASE_ID%TYPE;
1009
1010 l_revision_master_controlled VARCHAR2(1);
1011 l_status_master_controlled VARCHAR2(1);
1012 l_is_master_org VARCHAR2(1);
1013
1014 l_return_status VARCHAR2(1);
1015 l_error_code NUMBER;
1016 l_msg_count NUMBER;
1017 l_msg_data VARCHAR2(4000);
1018
1019 BEGIN
1020 l_api_version := 1.0;
1021 l_api_name := 'Item_Sync_Phase_Change';
1022 code_debug (l_api_name ||' started p_inventory_item_id '||p_inventory_item_id||' revision '|| p_revision ||' lc id '||p_lifecycle_id ||' phase id '||p_phase_id);
1023 code_debug (l_api_name ||' p_effective_date '||to_char(p_effective_date,'DD-MON-YYYY HH24:MI:SS'));
1024
1025 --Standard checks
1026 IF NOT FND_API.Compatible_API_Call (l_api_version
1027 ,p_api_version
1028 ,l_api_name
1029 ,g_pkg_name)
1030 THEN
1031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1032 END IF;
1033
1034
1035 IF FND_API.To_Boolean(p_commit) THEN
1036 SAVEPOINT Implement_All_Pending_Changes;
1037 END IF;
1038
1039 -- Initialize message list if p_init_msg_list is set to TRUE
1040 IF FND_API.To_Boolean(p_init_msg_list) THEN
1041 FND_MSG_PUB.Initialize;
1042 END IF;
1043 code_debug (l_api_name ||' creating pending phase change for item ');
1044
1045 EGO_ITEM_LC_IMP_PC_PUB.Create_Pending_Phase_Change
1046 (p_api_version => p_api_version
1047 ,p_commit => FND_API.G_FALSE
1048 ,p_inventory_item_id => p_inventory_item_id
1049 ,p_organization_id => p_organization_id
1050 ,p_effective_date => p_effective_date
1051 ,p_pending_flag => NULL
1052 ,p_revision => p_revision
1053 ,p_revision_id => NULL
1054 ,p_lifecycle_id => p_lifecycle_id
1055 ,p_phase_id => p_phase_id
1056 ,p_change_id => NULL
1057 ,p_change_line_id => NULL
1058 ,p_perform_security_check => FND_API.G_TRUE
1059 ,x_return_status => x_return_status
1060 ,x_errorcode => x_errorcode
1061 ,x_msg_count => x_msg_count
1062 ,x_msg_data => x_msg_data
1063 );
1064 code_debug (l_api_name ||' returning pending phase change for item with status '||x_return_status);
1065 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1066 RETURN;
1067 END IF;
1068 l_revision_master_controlled := FND_API.g_false;
1069 l_status_master_controlled := EGO_ITEM_LC_IMP_PC_PUB.get_master_controlled_status();
1070 l_is_master_org :=
1071 EGO_ITEM_LC_IMP_PC_PUB.get_master_org_status(p_organization_id);
1072 l_revision_id :=
1073 EGO_ITEM_LC_IMP_PC_PUB.get_revision_id
1074 (p_inventory_item_id => p_inventory_item_id
1075 ,p_organization_id => p_organization_id
1076 ,p_revision => p_revision
1077 );
1078 code_debug (l_api_name ||' calling implement pending phase changes for item ');
1079
1080 EGO_ITEM_LC_IMP_PC_PUB.Implement_Pending_Changes
1081 (p_api_version => p_api_version
1082 ,p_inventory_item_id => p_inventory_item_id
1083 ,p_organization_id => p_organization_id
1084 ,p_revision_id => l_revision_id
1085 ,p_revision_master_controlled => l_revision_master_controlled
1086 ,p_status_master_controlled => l_status_master_controlled
1087 ,p_is_master_org => l_is_master_org
1088 ,p_perform_security_check => FND_API.G_FALSE
1089 ,x_return_status => x_return_status
1090 ,x_errorcode => x_errorcode
1091 ,x_msg_count => x_msg_count
1092 ,x_msg_data => x_msg_data
1093 );
1094 code_debug (l_api_name ||' returning implement pending phase change for item with status '||x_return_status);
1095 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1096 RETURN;
1097 END IF;
1098 --
1099 -- commented for fixing 3371749
1100 --
1101 /***
1102 --Get the revision id for the current cursor row
1103 IF p_revision IS NULL THEN
1104
1105 l_revision_id := NULL;
1106 --Bug: 2871650 getting Current phase id to compare
1107 SELECT
1108 CURRENT_PHASE_ID INTO l_current_phase_id
1109 FROM
1110 MTL_SYSTEM_ITEMS_B
1111 WHERE
1112 INVENTORY_ITEM_ID = p_inventory_item_id
1113 AND ORGANIZATION_ID = p_organization_id;
1114
1115 ELSE
1116
1117 SELECT
1118 --Bug: 2871650 getting Current phase id to compare
1119 REVISION_ID, CURRENT_PHASE_ID INTO l_revision_id, l_current_phase_id
1120 FROM
1121 MTL_ITEM_REVISIONS_B
1122 WHERE
1123 INVENTORY_ITEM_ID = p_inventory_item_id
1124 AND ORGANIZATION_ID = p_organization_id
1125 AND REVISION = p_revision;
1126
1127 END IF;
1128
1129 IF (l_current_phase_id <> p_phase_id) then
1130 l_status_code := NULL;
1131
1132 BEGIN
1133
1134 --Now get the phase code
1135 SELECT PHASE_CODE INTO l_phase_code
1136 FROM PA_EGO_LIFECYCLES_PHASES_V
1137 WHERE PROJ_ELEMENT_ID = p_phase_id;
1138
1139 IF p_revision IS NULL
1140 THEN
1141 SELECT ITEM_STATUS_CODE INTO l_status_code
1142 FROM
1143 EGO_LCPHASE_ITEM_STATUS
1144 WHERE
1145 PHASE_CODE = l_phase_code
1146 AND DEFAULT_FLAG = 'Y';
1147 END IF;
1148
1149 EXCEPTION
1150 WHEN NO_DATA_FOUND THEN
1151 NULL;
1152
1153 END;
1154
1155
1156 INSERT INTO MTL_PENDING_ITEM_STATUS
1157 (
1158 INVENTORY_ITEM_ID
1159 ,ORGANIZATION_ID
1160 ,STATUS_CODE
1161 ,EFFECTIVE_DATE
1162 ,PENDING_FLAG
1163 ,LAST_UPDATE_DATE
1164 ,LAST_UPDATED_BY
1165 ,CREATION_DATE
1166 ,CREATED_BY
1167 ,LAST_UPDATE_LOGIN
1168 ,LIFECYCLE_ID
1169 ,PHASE_ID
1170 ,REVISION_ID
1171 )
1172 VALUES
1173 (
1174 p_inventory_item_id
1175 ,p_organization_id
1176 ,l_status_code
1177 ,NVL(p_effective_date,SYSDATE)
1178 ,'Y'
1179 ,SYSDATE
1180 ,g_current_login_id
1181 ,SYSDATE
1182 ,g_current_login_id
1183 ,g_current_login_id
1184 ,p_lifecycle_id
1185 ,p_phase_id
1186 ,l_revision_id
1187 );
1188
1189 --Now call an api to implement all of the pendings we just added
1190
1191 SELECT DECODE(LOOKUP_CODE2, 1, FND_API.G_TRUE, 2, FND_API.G_FALSE, FND_API.G_FALSE) INTO l_status_master_controlled
1192 FROM MTL_ITEM_ATTRIBUTES_V
1193 WHERE ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
1194
1195 l_revision_master_controlled := FND_API.G_FALSE;
1196
1197 SELECT
1198 DECODE(MP.ORGANIZATION_ID,MP.MASTER_ORGANIZATION_ID, FND_API.G_TRUE, FND_API.G_FALSE) INTO l_is_master_org
1199 FROM MTL_PARAMETERS MP
1200 WHERE MP.ORGANIZATION_ID = p_organization_id;
1201
1202 EGO_ITEM_LC_IMP_PC_PUB.Implement_Pending_Changes(1.0
1203 ,p_inventory_item_id
1204 ,p_organization_id
1205 ,l_revision_id
1206 ,l_revision_master_controlled
1207 ,l_status_master_controlled
1208 ,l_is_master_org
1209 ,l_return_status
1210 ,l_error_code
1211 ,l_msg_count
1212 ,l_msg_data
1213 );
1214
1215
1216
1217 END IF;--Bug: 2871650 ended If condition
1218 ***/
1219 -- Standard check of p_commit.
1220 IF FND_API.To_Boolean(p_commit) THEN
1221 COMMIT WORK;
1222 END IF;
1223
1224 x_return_status := FND_API.G_RET_STS_SUCCESS;
1225
1226 EXCEPTION
1227 WHEN FND_API.G_EXC_ERROR THEN
1228 IF FND_API.To_Boolean(p_commit) THEN
1229 ROLLBACK TO Sync_Phase_Change_PUB;
1230 END IF;
1231 x_return_status := FND_API.G_RET_STS_ERROR;
1232 IF NOT x_msg_count = 1 THEN
1233 FND_MSG_PUB.Count_And_Get(
1234 p_encoded => FND_API.G_FALSE,
1235 p_count => x_msg_count,
1236 p_data => x_msg_data
1237 );
1238 END IF;
1239 WHEN OTHERS THEN
1240 IF FND_API.To_Boolean(p_commit) THEN
1241 ROLLBACK TO Sync_Phase_Change_PUB;
1242 END IF;
1243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1244 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1245 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1246 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1247 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1248 FND_MSG_PUB.Add;
1249 FND_MSG_PUB.Count_And_Get(
1250 p_encoded => FND_API.G_FALSE,
1251 p_count => x_msg_count,
1252 p_data => x_msg_data
1253 );
1254
1255 END Sync_Phase_Change;
1256
1257 ----------------------------------------------------------------------
1258
1259
1260 PROCEDURE Create_Project_Item_Assoc
1261 (
1262 p_api_version IN NUMBER
1263 , p_project_id IN NUMBER
1264 , p_organization_id IN NUMBER
1265 , p_inventory_item_id IN NUMBER
1266 , p_revision IN VARCHAR2 DEFAULT NULL
1267 , p_revision_id IN NUMBER DEFAULT NULL
1268 , p_task_id IN NUMBER DEFAULT NULL
1269 , p_association_type IN VARCHAR2
1270 , p_association_code IN VARCHAR2
1271 , p_organization_specific IN VARCHAR2 DEFAULT FND_API.G_FALSE
1272 -- Currently not used
1273 , p_check_privileges IN VARCHAR2 DEFAULT FND_API.G_TRUE
1274 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1275 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1276 , x_return_status OUT NOCOPY VARCHAR2
1277 , x_errorcode OUT NOCOPY NUMBER
1278 , x_msg_count OUT NOCOPY NUMBER
1279 , x_msg_data OUT NOCOPY VARCHAR2
1280 ) IS
1281
1282 l_api_version NUMBER;
1283 l_api_name VARCHAR2(30);
1284 l_item_project_id EGO_ITEM_PROJECTS.item_project_id%TYPE;
1285 l_revision_id EGO_ITEM_PROJECTS.revision_id%TYPE;
1286
1287 BEGIN
1288
1289 l_api_version := 1.0;
1290 l_api_name := 'Create_Project_Item_Assoc';
1291
1292 code_debug (l_api_name ||' is called with params '
1293 ||' p_project_id :' ||p_project_id );
1294 code_debug (l_api_name ||' p_organization_id : '||p_organization_id
1295 ||' p_inventory_item_id :' ||p_inventory_item_id);
1296 code_debug (l_api_name ||' p_revision : '||p_revision
1297 ||' p_revision_id :' ||p_revision_id );
1298 code_debug (l_api_name ||' p_association_type :' ||p_association_type
1299 ||' p_association_code : '||p_association_code);
1300
1301 --------------------------------------------------------------------------
1302 -- Validity Checking --
1303 --------------------------------------------------------------------------
1304
1305 --Standard checks
1306 IF NOT FND_API.Compatible_API_Call (l_api_version
1307 ,p_api_version
1308 ,l_api_name
1309 ,g_pkg_name)
1310 THEN
1311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1312 END IF;
1313
1314 IF (FND_API.To_Boolean(p_commit)) THEN
1315 SAVEPOINT Create_Proj_Item_Assoc_PUB;
1316 END IF;
1317
1318 -- Initialize message list if p_init_msg_list is set to TRUE
1319 IF FND_API.To_Boolean(p_init_msg_list) THEN
1320 FND_MSG_PUB.Initialize;
1321 END IF;
1322
1323 -- Since this is an API intended for use by Projects, it
1324 -- will almost always be used for creating Lifecycle tracking
1325 -- projects. There can only be one Lifecycle tracking
1326 -- project per Item, but there is no unique index enforcing
1327 -- such a constraint, so we have to check it ourselves.
1328
1329 IF (p_association_type = G_PROJECT_ASSOC_TYPE AND
1330 p_association_code = g_lifecycle_tracking_code) THEN
1331
1332
1333 IF (FND_API.To_Boolean(p_check_privileges)) THEN
1334
1335 -- 4052565 perform security check
1336 code_debug (l_api_name ||' performing security checks ');
1337 IF NOT EGO_ITEM_PVT.has_role_on_item
1338 (p_function_name => 'EGO_CREATE_ITEM_LC_TRACK_PROJ'
1339 ,p_inventory_item_id => p_inventory_item_id
1340 ,p_item_number => NULL
1341 ,p_organization_id => p_organization_id
1342 ,p_organization_name => NULL
1343 ,p_user_id => NULL
1344 ,p_party_id => NULL
1345 ,p_set_message => FND_API.G_TRUE
1346 ) THEN
1347 RAISE FND_API.G_EXC_ERROR;
1348 END IF;
1349 ELSE
1350 code_debug (l_api_name ||' security checks bypassed');
1351 END IF;
1352
1353 --we want to delete it
1354 IF p_revision IS NULL AND p_revision_id IS NULL THEN
1355 code_debug (l_api_name ||' working in context of item ');
1356 l_revision_id := NULL;
1357 DELETE
1358 FROM EGO_ITEM_PROJECTS
1359 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1360 AND ORGANIZATION_ID = p_organization_id
1361 AND REVISION_ID IS NULL
1362 AND ASSOCIATION_TYPE = g_project_assoc_type
1363 AND ASSOCIATION_CODE = g_lifecycle_tracking_code;
1364 ELSE
1365 code_debug (l_api_name ||' working in context of revision ');
1366 IF p_revision_id IS NULL THEN
1367 l_revision_id := EGO_ITEM_LC_IMP_PC_PUB.get_revision_id
1368 (p_inventory_item_id => p_inventory_item_id
1369 ,p_organization_id => p_organization_id
1370 ,p_revision => p_revision);
1371 ELSE
1372 l_revision_id := p_revision_id;
1373 END IF;
1374 DELETE
1375 FROM EGO_ITEM_PROJECTS
1376 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1377 AND ORGANIZATION_ID = p_organization_id
1378 AND REVISION_id = l_revision_id
1379 AND ASSOCIATION_TYPE = g_project_assoc_type
1380 AND ASSOCIATION_CODE = g_lifecycle_tracking_code;
1381 END IF;
1382
1383 END IF;
1384
1385 SELECT EGO_ITEM_PROJECTS_S.NEXTVAL
1386 INTO l_item_project_id
1387 FROM DUAL;
1388
1389 code_debug (l_api_name ||' revision id '||l_revision_id);
1390
1391 --------------------------------------------------------------------------
1392 -- Insert the new row into the item-projects table --
1393 --------------------------------------------------------------------------
1394
1395 INSERT INTO
1396 EGO_ITEM_PROJECTS
1397 (
1398 ITEM_PROJECT_ID
1399 ,INVENTORY_ITEM_ID
1400 ,ORGANIZATION_ID
1401 -- ,REVISION
1402 ,REVISION_ID
1403 ,PROJECT_ID
1404 ,TASK_ID
1405 ,ASSOCIATION_TYPE
1406 ,ASSOCIATION_CODE
1407 ,CREATED_BY
1408 ,CREATION_DATE
1409 ,LAST_UPDATED_BY
1410 ,LAST_UPDATE_DATE
1411 ,LAST_UPDATE_LOGIN
1412 )
1413 VALUES
1414 (
1415 l_item_project_id
1416 ,p_inventory_item_id
1417 ,p_organization_id
1418 -- ,p_revision
1419 ,l_revision_id
1420 ,p_project_id
1421 ,p_task_id
1422 ,p_association_type
1423 ,p_association_code
1424 ,G_CURRENT_USER_ID
1425 ,SYSDATE
1426 ,G_CURRENT_USER_ID
1427 ,SYSDATE
1428 ,G_CURRENT_LOGIN_ID
1429 );
1430
1431 --------------------------------------------------------------------------
1432 -- Commit --
1433 --------------------------------------------------------------------------
1434
1435 -- Standard check of p_commit.
1436 IF FND_API.To_Boolean(p_commit)
1437 THEN
1438 COMMIT WORK;
1439 END IF;
1440
1441 x_return_status := FND_API.G_RET_STS_SUCCESS;
1442
1443 EXCEPTION
1444 WHEN DUP_VAL_ON_INDEX THEN
1445 IF (FND_API.To_Boolean(p_commit)) THEN
1446 ROLLBACK TO Create_Proj_Item_Assoc_PUB;
1447 END IF;
1448 x_return_status := FND_API.G_RET_STS_ERROR;
1449 FND_MESSAGE.Set_Name(g_app_name, 'EGO_ITEM_PROJ_DUP_ERR');
1450 FND_MSG_PUB.Add;
1451 FND_MSG_PUB.Count_And_Get(
1452 p_encoded => FND_API.G_FALSE,
1453 p_count => x_msg_count,
1454 p_data => x_msg_data
1455 );
1456 WHEN G_VALIDATION_ERROR THEN
1457 IF (FND_API.To_Boolean(p_commit)) THEN
1458 ROLLBACK TO Create_Proj_Item_Assoc_PUB;
1459 END IF;
1460 x_return_status := FND_API.G_RET_STS_ERROR;
1461 FND_MESSAGE.Set_Name(g_app_name, 'EGO_ITEM_PROJ_TRACK_EXISTS');
1462 FND_MSG_PUB.Add;
1463 FND_MSG_PUB.Count_And_Get(
1464 p_encoded => FND_API.G_FALSE,
1465 p_count => x_msg_count,
1466 p_data => x_msg_data
1467 );
1468 WHEN FND_API.G_EXC_ERROR THEN
1469 IF FND_API.To_Boolean( p_commit ) THEN
1470 ROLLBACK TO Create_Proj_Item_Assoc_PUB;
1471 END IF;
1472 x_return_status := FND_API.G_RET_STS_ERROR;
1473 IF NOT x_msg_count = 1 THEN
1474 FND_MSG_PUB.Count_And_Get(
1475 p_encoded => FND_API.G_FALSE,
1476 p_count => x_msg_count,
1477 p_data => x_msg_data
1478 );
1479 END IF;
1480 WHEN OTHERS THEN
1481 IF (FND_API.To_Boolean(p_commit)) THEN
1482 ROLLBACK TO Create_Proj_Item_Assoc_PUB;
1483 END IF;
1484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1485 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1486 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1487 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1488 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1489 FND_MSG_PUB.Add;
1490 FND_MSG_PUB.Count_And_Get(
1491 p_encoded => FND_API.G_FALSE,
1492 p_count => x_msg_count,
1493 p_data => x_msg_data
1494 );
1495
1496 END Create_Project_Item_Assoc;
1497
1498 ----------------------------------------------------------------------
1499
1500 /*
1501
1502 DESCRIPTION
1503 Copies a project from a source item to a destination item.
1504
1505 AUTHOR
1506 ssarnoba
1507
1508 NOTES
1509 (-) This API copies a project associated at a source item to destination
1510 (-) Currently there is no need to return the ID of the association just
1511 created. It has no use to the caller.
1512
1513 PARAMETERS
1514 (-) Association code is NOT functionally dependent on association type
1515 (-) We support all types of association code, not just LIFECYCLE_TRACKING
1516 (-) NULL value for p_init_msg_list means fnd_api.G_FALSE
1517 (-) NULL value for p_commit means fnd_api.G_FALSE
1518
1519 PRECONDITIONS
1520 These must be respected by the caller and are not enforced here.
1521 (-) The source and destination items already exist
1522 (-) The user has VIEW privilege on source item.
1523 (-) The destination item is allowed to take on the same values as that
1524 of the project to which the destination item is being assigned.
1525
1526 RETURN
1527 (-) When we are copying a Development Project, the return value
1528 x_item_project_id is NULL, because there will usually be several
1529 newly created item_project associations.
1530
1531 */
1532 PROCEDURE Copy_Project
1533 (
1534 p_api_version IN NUMBER
1535 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1536 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1537 , p_source_item_id IN NUMBER
1538 , p_source_org_id IN NUMBER
1539 , p_source_rev_id IN NUMBER
1540 , p_association_type IN VARCHAR2
1541 , p_association_code IN VARCHAR2
1542 , p_dest_item_id IN NUMBER
1543 , p_dest_org_id IN NUMBER
1544 , p_dest_rev_id IN NUMBER
1545 , p_check_privileges IN VARCHAR2 DEFAULT FND_API.G_FALSE
1546 , x_return_status OUT NOCOPY VARCHAR2
1547 , x_error_code OUT NOCOPY NUMBER
1548 , x_msg_count OUT NOCOPY NUMBER
1549 , x_msg_data OUT NOCOPY VARCHAR2
1550 ) IS
1551 l_return_status VARCHAR2(1);
1552 l_error_code NUMBER;
1553 l_msg_count NUMBER;
1554 l_msg_data VARCHAR2(4000);
1555 l_api_name VARCHAR2(30);
1556 l_api_version NUMBER;
1557 l_has_errors BOOLEAN;
1558
1559 -- Query that fetches all association records between the source item and
1560 -- its project(s)
1561 CURSOR project_assocs_cursor (cp_inventory_item_id IN NUMBER
1562 ,cp_organization_id IN NUMBER
1563 ,cp_revision_id IN NUMBER
1564 ,cp_association_type IN VARCHAR2
1565 ,cp_association_code IN VARCHAR2
1566 ) IS
1567 SELECT project_id, task_id
1568 FROM ego_item_projects
1569 WHERE inventory_item_id = cp_inventory_item_id
1570 AND organization_id = cp_organization_id
1571 AND NVL(revision_id,-1) = NVL(cp_revision_id,-1) -- -1 is not a valid revision_id
1572 AND association_type = cp_association_type
1573 AND association_code = cp_association_code ;
1574
1575 BEGIN
1576
1577 x_return_status := NULL;
1578 l_api_name := 'Copy_Project';
1579 l_api_version := 1.0;
1580 l_has_errors := FALSE;
1581
1582 code_debug (l_api_name ||' is called with params:');
1583 code_debug (' p_source_item_id: ' || p_source_item_id);
1584 code_debug (' p_source_org_id: ' || p_source_org_id);
1585 code_debug (' p_source_rev_id: ' || p_source_rev_id);
1586 code_debug (' p_association_type: ' || p_association_type);
1587 code_debug (' p_association_code: ' || p_association_code);
1588 code_debug (' p_dest_item_id: ' || p_dest_item_id);
1589 code_debug (' p_dest_org_id: ' || p_dest_org_id);
1590 code_debug (' p_dest_rev_id: ' || p_dest_rev_id);
1591
1592 --Standard checks
1593 IF NOT FND_API.Compatible_API_Call (l_api_version
1594 ,p_api_version
1595 ,l_api_name
1596 ,g_pkg_name)
1597 THEN
1598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1599 END IF;
1600
1601 IF (FND_API.To_Boolean(p_init_msg_list)) THEN
1602 FND_MSG_PUB.Initialize;
1603 END IF;
1604
1605 IF FND_API.To_Boolean(p_commit) THEN
1606 SAVEPOINT Copy_Project_PUB;
1607 END IF;
1608
1609 IF (p_source_item_id IS NULL OR
1610 p_source_org_id IS NULL OR
1611 p_association_type IS NULL OR
1612 p_association_code IS NULL OR
1613 p_dest_item_id IS NULL OR
1614 p_dest_org_id IS NULL
1615 ) THEN
1616 FND_MESSAGE.Set_Name(g_app_name, 'EGO_IPI_INSUFFICIENT_PARAMS');
1617 FND_MESSAGE.Set_Token('PROG_NAME', g_pkg_name||'.'||l_api_name);
1618 FND_MSG_PUB.Add;
1619 RAISE FND_API.G_EXC_ERROR;
1620 END IF;
1621
1622 ----------------------------------------------------------------------------
1623 -- Obtain the data to be inserted --
1624 ----------------------------------------------------------------------------
1625
1626 -- Execute the query to get all rows for the existing project association
1627 FOR cr in project_assocs_cursor (cp_inventory_item_id => p_source_item_id
1628 ,cp_organization_id => p_source_org_id
1629 ,cp_revision_id => p_source_rev_id
1630 ,cp_association_type => p_association_type
1631 ,cp_association_code => p_association_code
1632 ) LOOP
1633
1634 --------------------------------------------------------------------------
1635 -- Insert a copy of each row into the item-projects table --
1636 --------------------------------------------------------------------------
1637 Create_Project_Item_Assoc(
1638 p_api_version => p_api_version
1639 ,p_project_id => cr.project_id
1640 ,p_organization_id => p_dest_org_id
1641 ,p_inventory_item_id => p_dest_item_id
1642 ,p_revision => NULL -- this is redundant if we pass revision_id
1643 ,p_revision_id => p_dest_rev_id
1644 ,p_task_id => cr.task_id
1645 ,p_association_type => p_association_type
1646 ,p_association_code => p_association_code
1647 ,p_organization_specific => NULL -- this gets ignored anyway
1648 ,p_check_privileges => p_check_privileges
1649 ,x_return_status => l_return_status
1650 ,x_errorcode => l_error_code
1651 ,x_msg_count => l_msg_count
1652 ,x_msg_data => l_msg_data);
1653
1654 --------------------------------------------------------------------------
1655 -- Error Handling --
1656 --------------------------------------------------------------------------
1657
1658 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
1659 l_has_errors := TRUE;
1660 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_UNEXP_ERROR THEN
1661 x_return_status := l_return_status;
1662 END IF;
1663 IF l_msg_count = 1 THEN
1664 -- add the fetched message into error stack
1665 FND_MESSAGE.Set_Name(g_app_name, 'EGO_GENERIC_MSG_TEXT');
1666 FND_MESSAGE.Set_Token('MESSAGE', l_msg_data);
1667 FND_MSG_PUB.Add;
1668 END IF;
1669 END IF;
1670 END LOOP;
1671
1672 -- raise an error if anything was unsuccessful
1673 IF l_has_errors THEN
1674 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1676 END IF;
1677 RAISE FND_API.G_EXC_ERROR;
1678 ELSE
1679 x_return_status := FND_API.G_RET_STS_SUCCESS;
1680 END IF;
1681
1682 -- Standard check of p_commit.
1683 IF FND_API.To_Boolean(p_commit) THEN
1684 COMMIT WORK;
1685 END IF;
1686
1687 EXCEPTION
1688 WHEN FND_API.G_EXC_ERROR THEN
1689 IF FND_API.To_Boolean(p_commit) THEN
1690 ROLLBACK TO Copy_Project_PUB;
1691 END IF;
1692 x_return_status := FND_API.G_RET_STS_ERROR;
1693 FND_MSG_PUB.Count_And_Get(
1694 p_encoded => FND_API.G_FALSE,
1695 p_count => x_msg_count,
1696 p_data => x_msg_data
1697 );
1698 WHEN OTHERS THEN
1699 IF FND_API.To_Boolean(p_commit) THEN
1700 ROLLBACK TO Copy_Project_PUB;
1701 END IF;
1702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1703 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1704 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1705 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1706 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1707 FND_MSG_PUB.Add;
1708 FND_MSG_PUB.Count_And_Get(
1709 p_encoded => FND_API.G_FALSE,
1710 p_count => x_msg_count,
1711 p_data => x_msg_data
1712 );
1713
1714 END Copy_Project;
1715
1716 ----------------------------------------------------------------------
1717
1718 PROCEDURE Copy_Item_Assocs
1719 (
1720 p_api_version IN NUMBER
1721 ,p_project_id_from IN NUMBER
1722 ,p_project_id_to IN NUMBER
1723 ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_FALSE
1724 ,p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
1725 ,x_return_status OUT NOCOPY VARCHAR2
1726 ,x_errorcode OUT NOCOPY NUMBER
1727 ,x_msg_count OUT NOCOPY NUMBER
1728 ,x_msg_data OUT NOCOPY VARCHAR2
1729 ) IS
1730
1731 l_api_version NUMBER;
1732 l_api_name VARCHAR2(30);
1733 l_is_org_specific VARCHAR2(1);
1734 l_return_status VARCHAR2(1);
1735 l_msg_count NUMBER;
1736 l_msg_data VARCHAR2(100);
1737
1738 CURSOR project_assocs_cursor (cp_project_id IN NUMBER)
1739 IS
1740 SELECT DISTINCT ORGANIZATION_ID
1741 ,INVENTORY_ITEM_ID
1742 ,REVISION_ID
1743 ,ASSOCIATION_TYPE
1744 ,ASSOCIATION_CODE
1745 FROM EGO_ITEM_PROJECTS
1746 WHERE PROJECT_ID = cp_project_id;
1747
1748 BEGIN
1749
1750 l_api_version := 1.0;
1751 l_api_name := 'Copy_Item_Assocs';
1752
1753 --Standard checks
1754 IF NOT FND_API.Compatible_API_Call (l_api_version
1755 ,p_api_version
1756 ,l_api_name
1757 ,g_pkg_name)
1758 THEN
1759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1760 END IF;
1761
1762 IF FND_API.To_Boolean(p_commit) THEN
1763 SAVEPOINT Copy_Item_Assocs_PUB;
1764 END IF;
1765
1766 -- Initialize message list if p_init_msg_list is set to TRUE
1767 IF FND_API.To_Boolean(p_init_msg_list) THEN
1768 FND_MSG_PUB.Initialize;
1769 END IF;
1770
1771 FOR assoc_rec IN project_assocs_cursor(p_project_id_from)
1772 LOOP
1773
1774 IF (assoc_rec.ORGANIZATION_ID IS NULL) THEN
1775 l_is_org_specific := FND_API.G_FALSE;
1776 ELSE
1777 l_is_org_specific := FND_API.G_TRUE;
1778 END IF;
1779
1780 IF (assoc_rec.ASSOCIATION_TYPE = g_project_assoc_type
1781 AND assoc_rec.ASSOCIATION_CODE <> g_lifecycle_tracking_code) THEN
1782
1783 Create_Project_Item_Assoc(
1784 p_api_version => 1.0
1785 ,p_project_id => p_project_id_to
1786 ,p_organization_id => assoc_rec.ORGANIZATION_ID
1787 ,p_inventory_item_id => assoc_rec.INVENTORY_ITEM_ID
1788 -- ,p_revision => assoc_rec.REVISION
1789 ,p_revision_id => assoc_rec.REVISION_ID
1790 ,p_association_type => assoc_rec.ASSOCIATION_TYPE
1791 ,p_association_code => assoc_rec.ASSOCIATION_CODE
1792 ,p_organization_specific => l_is_org_specific
1793 ,x_return_status => l_return_status
1794 ,x_errorcode => x_errorcode
1795 ,x_msg_count => x_msg_count
1796 ,x_msg_data => x_msg_data
1797 );
1798
1799 -- Keep track of and report the status of our worst failure
1800 IF (x_return_status IS NULL OR
1801 x_return_status = FND_API.G_RET_STS_SUCCESS OR
1802 (x_return_status = FND_API.G_RET_STS_ERROR AND
1803 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
1804
1805 x_return_status := l_return_status;
1806 END IF;
1807
1808 END IF;
1809 END LOOP;
1810
1811 -- (keep this code before the commit check or it may behave incorrectly)
1812 IF (x_return_status IS NULL) THEN
1813 x_return_status := FND_API.G_RET_STS_SUCCESS;
1814 END IF;
1815
1816 -- If we got no errors and the commit check passes, we commit
1817 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
1818 FND_API.To_Boolean(p_commit)) THEN
1819 COMMIT WORK;
1820 END IF;
1821
1822 EXCEPTION
1823 WHEN OTHERS THEN
1824 IF (FND_API.To_Boolean(p_commit)) THEN
1825 ROLLBACK TO Copy_Item_Assocs_PUB;
1826 END IF;
1827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1828 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1829 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1830 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1831 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1832 FND_MSG_PUB.Add;
1833 FND_MSG_PUB.Count_And_Get(
1834 p_encoded => FND_API.G_FALSE,
1835 p_count => x_msg_count,
1836 p_data => x_msg_data
1837 );
1838
1839 END Copy_Item_Assocs;
1840
1841 ----------------------------------------------------------------------
1842
1843
1844 FUNCTION Has_LC_Tracking_Project (
1845 p_organization_id IN NUMBER
1846 , p_inventory_item_id IN NUMBER
1847 , p_revision IN VARCHAR2 DEFAULT NULL
1848 ) RETURN VARCHAR2
1849 IS
1850
1851 BEGIN
1852
1853 if (Has_Lifecycle_Tracking_Project(p_inventory_item_id, p_organization_id, p_revision)) THEN
1854 RETURN 'TRUE';
1855 END IF;
1856
1857 RETURN 'FALSE';
1858
1859
1860 END Has_LC_Tracking_Project;
1861
1862 ----------------------------------------------------------------------
1863
1864
1865
1866 END EGO_LIFECYCLE_USER_PUB;
1867