[Home] [Help]
PACKAGE BODY: APPS.ENG_VAL_TO_ID
Source
1 PACKAGE BODY ENG_Val_To_Id AS
2 /* $Header: ENGSVIDB.pls 120.11 2011/11/28 09:16:00 rambkond ship $ */
3 -- Global constant holding the package name
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Val_To_Id';
6 g_Token_Tbl Error_Handler.Token_Tbl_Type;
7
8 -- Prototypes for val_to_id functions.
9
10 -- START GEN val_to_id
11
12 -- Key Flex
13
14 FUNCTION Key_Flex
15 ( p_key_flex_code IN VARCHAR2
16 , p_structure_number IN NUMBER
17 , p_appl_short_name IN VARCHAR2
18 , p_segment_array IN FND_FLEX_EXT.SegmentArray
19 )
20 RETURN NUMBER
21 IS
22 l_id NUMBER;
23 l_segment_array FND_FLEX_EXT.SegmentArray;
24 BEGIN
25
26 l_segment_array := p_segment_array;
27
28 -- Convert any missing values to NULL
29
30 FOR I IN 1..l_segment_array.COUNT LOOP
31
32 IF l_segment_array(I) = FND_API.G_MISS_CHAR THEN
33 l_segment_array(I) := NULL;
34 END IF;
35
36 END LOOP;
37
38 -- Call Flex conversion routine
39
40 IF NOT FND_FLEX_EXT.get_combination_id
41 ( application_short_name => p_appl_short_name
42 , key_flex_code => p_key_flex_code
43 , structure_number => p_structure_number
44 , validation_date => NULL
45 , n_segments => l_segment_array.COUNT
46 , segments => l_segment_array
47 , combination_id => l_id
48 )
49 THEN
50
51 -- Error getting combination id.
52 -- Function has already pushed a message on the stack. Add to
53 -- the API message list.
54
55 FND_MSG_PUB.Add;
56 l_id := FND_API.G_MISS_NUM;
57
58 END IF;
59
60 RETURN l_id;
61
62 EXCEPTION
63
64 WHEN OTHERS THEN
65
66 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
67 THEN
68 FND_MSG_PUB.Add_Exc_Msg
69 ( G_PKG_NAME
70 , 'Key_Flex'
71 );
72 END IF;
73
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75
76 END Key_Flex;
77
78 -- Generator will append new prototypes before end generate comment.
79
80
81 -- Approval_List
82 /*****************************************************************************
83 * Function : Approval_List
84 * Returns : NULL if the function is unsuccessful else the Id of the
85 * approval list name
86 * Purpose : Convert the Approval_List_name to its ID.
87 *****************************************************************************/
88
89 FUNCTION Approval_List
90 ( p_approval_list IN VARCHAR2
91 , x_err_text OUT NOCOPY VARCHAR2
92 )
93 RETURN NUMBER
94 IS
95 l_id NUMBER;
96 ret_code NUMBER;
97 BEGIN
98
99 SELECT approval_list_id
100 INTO l_id
101 FROM eng_ecn_approval_lists
102 WHERE approval_list_name = p_approval_list;
103
104 RETURN l_id;
105
106 EXCEPTION
107
108 WHEN NO_DATA_FOUND THEN
109 RETURN NULL;
110
111 WHEN OTHERS THEN
112 x_err_text := G_PKG_NAME || ' : (Approval List Value-id conversion) '
113 || substrb(SQLERRM,1,200);
114 RETURN FND_API.G_MISS_NUM;
115
116 END Approval_List;
117
118
119
120
121
122 -- Approval_List
123 /*****************************************************************************
124 * Function : Lifecycle_Id
125 * Returns : NULL if the function is unsuccessful else the Id of the
126 * lifecycle name
127 * Purpose : Convert the Lifecycle name to its ID.
128 *****************************************************************************/
129
130 FUNCTION Lifecycle_id
131 ( p_lifecycle_name IN VARCHAR2
132 , p_inventory_item_id IN NUMBER
133 , p_org_id IN NUMBER
134 , x_err_text OUT NOCOPY VARCHAR2
135 )
136 RETURN NUMBER
137 IS
138 l_id NUMBER;
139 ret_code NUMBER;
140 l_sql_stmt VARCHAR2(2000);
141 l_item_lifecycle_id NUMBER;
142
143 BEGIN
144 --
145 -- Bug 3311072: Made changes to fetch lifecycle
146 -- Added By LKASTURI
147 l_sql_stmt := 'SELECT OLC.LIFECYCLE_ID '
148 || 'FROM EGO_OBJ_TYPE_LIFECYCLES OLC, '
149 || 'FND_OBJECTS O '
150 || 'WHERE O.OBJ_NAME = :1 '
151 || 'AND OLC.OBJECT_ID = O.OBJECT_ID '
152 || 'AND OLC.OBJECT_CLASSIFICATION_CODE in '
153 || '(SELECT TO_CHAR(IC.ITEM_CATALOG_GROUP_ID) '
154 || ' FROM MTL_ITEM_CATALOG_GROUPS_B IC '
155 || ' CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id '
156 || ' START WITH item_catalog_group_id = '
157 || ' (SELECT item_catalog_group_id '
158 || ' FROM mtl_system_items '
159 || ' WHERE inventory_item_id = :2 '
160 || ' AND organization_id = :3 '
161 || ' )) ';
162
163 EXECUTE IMMEDIATE l_sql_stmt INTO l_item_lifecycle_id USING 'EGO_ITEM', p_inventory_item_id, p_org_id;
164
165 l_sql_stmt := ' SELECT LP.PROJ_ELEMENT_ID '
166 || 'from pa_ego_phases_v LP '
167 || 'where name = :1 '
168 || 'and parent_structure_id = :2 '
169 || 'and object_type = :3 ';
170
171 EXECUTE IMMEDIATE l_sql_stmt
172 INTO l_id
173 USING p_lifecycle_name, l_item_lifecycle_id, 'PA_TASKS';
174
175 -- End Changes
176
177 RETURN l_id;
178
179 EXCEPTION
180
181 WHEN NO_DATA_FOUND THEN
182 RETURN NULL;
183
184 WHEN OTHERS THEN
185 x_err_text := G_PKG_NAME || ' : (Lifecycle Value-id conversion) '
186 || substrb(SQLERRM,1,200);
187 RETURN FND_API.G_MISS_NUM;
188
189 END Lifecycle_Id;
190
191
192 --Bug 2848506 added the below function to get object name
193 --when the user does'nt supply it via OBJECT_DISPLAY_NAME column in ENG_CHANGE_LINES_INTERFACE
194
195
196
197
198 FUNCTION Get_Object_name
199 (
200 p_object_id IN NUMBER
201 )
202 RETURN VARCHAR2
203 IS
204 l_obj_name VARCHAR2(240);
205 BEGIN
206
207 SELECT display_name
208 into l_obj_name
209 from fnd_objects_vl
210 where object_id = p_object_id;
211
212 RETURN l_obj_name;
213
214 EXCEPTION
215
216 WHEN NO_DATA_FOUND THEN
217 RETURN NULL;
218
219 END Get_Object_name;
220
221 PROCEDURE Preprocess_Key
222 ( p_object_name IN VARCHAR2
223 , p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
224 , x_pk1_name IN OUT NOCOPY VARCHAR2
225 , x_pk2_name IN OUT NOCOPY VARCHAR2
226 , x_pk3_name IN OUT NOCOPY VARCHAR2
227 , x_pk4_name IN OUT NOCOPY VARCHAR2
228 , x_pk5_name IN OUT NOCOPY VARCHAR2
229 )
230 IS
231 l_err_text VARCHAR2(2000);
232 l_org_id NUMBER;
233 BEGIN
234 x_pk1_name := NULL;
235 x_pk2_name := NULL;
236 x_pk3_name := NULL;
237 x_pk4_name := NULL;
238 x_pk5_name := NULL;
239
240 IF p_object_name = 'EGO_ITEM' THEN
241 x_pk1_name := p_change_line_rec.pk1_name;
242 x_pk3_name := to_char(Organization(p_change_line_rec.pk3_name, l_err_text));
243 ELSIF p_object_name = 'EGO_ITEM_REVISION' THEN
244 x_pk1_name := p_change_line_rec.pk1_name;
245 x_pk2_name := p_change_line_rec.pk2_name;
246 l_org_id := Organization(p_change_line_rec.pk4_name, l_err_text);
247 x_pk4_name := to_char(l_org_id);
248 x_pk3_name := Revised_Item(p_revised_item_num => p_change_line_rec.pk3_name
249 ,p_organization_id => l_org_id
250 ,x_err_text => l_err_text);
251 ELSE
252 x_pk1_name := p_change_line_rec.pk1_name;
253 x_pk2_name := p_change_line_rec.pk2_name;
254 x_pk3_name := p_change_line_rec.pk3_name;
255 x_pk4_name := p_change_line_rec.pk4_name;
256 x_pk5_name := p_change_line_rec.pk5_name;
257 END IF;
258 END Preprocess_Key;
259
260
261 PROCEDURE Object_Name
262 ( p_display_name IN VARCHAR2
263 , x_object_name IN OUT NOCOPY VARCHAR2
264 , x_query_object_name IN OUT NOCOPY VARCHAR2
265 , x_query_column1_name IN OUT NOCOPY VARCHAR2
266 , x_query_column2_name IN OUT NOCOPY VARCHAR2
267 , x_query_column3_name IN OUT NOCOPY VARCHAR2
268 , x_query_column4_name IN OUT NOCOPY VARCHAR2
269 , x_query_column5_name IN OUT NOCOPY VARCHAR2
270 , x_query_column1_type IN OUT NOCOPY VARCHAR2
271 , x_query_column2_type IN OUT NOCOPY VARCHAR2
272 , x_query_column3_type IN OUT NOCOPY VARCHAR2
273 , x_query_column4_type IN OUT NOCOPY VARCHAR2
274 , x_query_column5_type IN OUT NOCOPY VARCHAR2
275 , x_fk1_column_name IN OUT NOCOPY VARCHAR2
276 , x_fk2_column_name IN OUT NOCOPY VARCHAR2
277 , x_fk3_column_name IN OUT NOCOPY VARCHAR2
278 , x_fk4_column_name IN OUT NOCOPY VARCHAR2
279 , x_fk5_column_name IN OUT NOCOPY VARCHAR2
280 , x_object_id IN OUT NOCOPY NUMBER --Bug 2848506 Required for adding Items for HeaderCO's
281 )
282 IS
283 BEGIN
284
285
286 -- assuming obj.query_object_name is the name of a view that has column names
287 -- which match the column names in the table vl.object_name
288 SELECT vl.obj_name, obj.query_object_name, obj.query_column1_name,
289 obj.query_column2_name, obj.query_column3_name, obj.query_column4_name,
290 obj.query_column5_name, obj.query_column1_type, obj.query_column2_type,
291 obj.query_column3_type, obj.query_column4_type, obj.query_column5_type,
292 vl.pk1_column_name, vl.pk2_column_name, vl.pk3_column_name,
293 vl.pk4_column_name, vl.pk5_column_name ,vl.object_id
294 INTO x_object_name, x_query_object_name, x_query_column1_name,
295 x_query_column2_name, x_query_column3_name, x_query_column4_name,
296 x_query_column5_name, x_query_column1_type, x_query_column2_type,
297 x_query_column3_type, x_query_column4_type, x_query_column5_type,
298 x_fk1_column_name, x_fk2_column_name, x_fk3_column_name, x_fk4_column_name,
299 x_fk5_column_name,x_object_id
300 FROM fnd_objects_vl vl, eng_change_objects obj
301 WHERE vl.display_name = p_display_name AND obj.object_id = vl.object_id;
302
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 x_object_name := NULL;
306 END Object_Name;
307
308
309 FUNCTION Get_Type_From_Header
310 ( p_change_notice IN VARCHAR2
311 , p_org_id IN NUMBER
312 )
313 RETURN NUMBER
314 IS
315 l_id NUMBER;
316 BEGIN
317
318 SELECT change_order_type_id
319 INTO l_id
320 FROM eng_engineering_changes
321 WHERE change_notice = p_change_notice
322 AND organization_id = p_org_id;
323
324 RETURN l_id;
325
326 EXCEPTION
327
328 WHEN NO_DATA_FOUND THEN
329 RETURN NULL;
330
331 WHEN OTHERS THEN
332 RETURN FND_API.G_MISS_NUM;
333
334 END Get_Type_From_Header;
335
336
337
338 /*****************************************************************************
339 * Function : Get_Change_Id
340 * Returns : NULL if the function is unsuccessful, else the Id of the
341 * change, given change notice and org id
342 * Purpose : Convert the change notice and org id into a change id
343 *****************************************************************************/
344
345 FUNCTION Get_Change_Id
346 ( p_change_notice IN VARCHAR2
347 , p_org_id IN NUMBER
348 , x_change_mgmt_type_code OUT NOCOPY VARCHAR2
349 )
350 RETURN NUMBER
351 IS
352 l_id NUMBER;
353 BEGIN
354
355 SELECT change_id, change_mgmt_type_code
356 INTO l_id, x_change_mgmt_type_code
357 FROM eng_engineering_changes
358 WHERE change_notice = p_change_notice
359 AND organization_id = p_org_id;
360
361 RETURN l_id;
362
363 EXCEPTION
364
365 WHEN NO_DATA_FOUND THEN
366 RETURN NULL;
367
368 WHEN OTHERS THEN
369 RETURN FND_API.G_MISS_NUM;
370
371 END Get_Change_Id;
372
373
374 -- Project
375 /*****************************************************************************
376 * Function : Project
377 * Returns : NULL if the function is unsuccessful, else the Id of the
378 * project name
379 * Purpose : Convert the Project_Number to its ID.
380 *****************************************************************************/
381
382 FUNCTION Project
383 ( p_project_name IN VARCHAR2
384 , x_err_text OUT NOCOPY VARCHAR2
385 )
386 RETURN NUMBER
387 IS
388 l_id NUMBER;
389 ret_code NUMBER;
390 BEGIN
391
392 /*
393 Changed the Table name from mtl_projects_v to PA_PROJECTS_ALL
394 to avoid the Non-Mergeable view Performance issues
395 */
396 SELECT project_id
397 INTO l_id
398 FROM pa_projects_all
399 WHERE name = p_project_name;
400
401 RETURN l_id;
402
403 EXCEPTION
404
405 WHEN NO_DATA_FOUND THEN
406 RETURN NULL;
407
408 WHEN OTHERS THEN
409 x_err_text := G_PKG_NAME || ' : (Project Value-id conversion)'
410 || substrb(SQLERRM,1,200);
411 RETURN FND_API.G_MISS_NUM;
412
413 END Project;
414
415 -- Task
416 /*****************************************************************************
417 * Function : Task
418 * Returns : NULL if the function is unsuccessful, else the Id of the
419 * task name
420 * Purpose : Convert the Task_Number to its ID.
421 *****************************************************************************/
422
423 FUNCTION Task
424 ( p_task_number IN VARCHAR2
425 , p_project_Id IN NUMBER
426 , x_err_text OUT NOCOPY VARCHAR2
427 )
428 RETURN NUMBER
429 IS
430 l_id NUMBER;
431 ret_code NUMBER;
432 BEGIN
433
434 SELECT task_id
435 INTO l_id
436 FROM pa_tasks
437 WHERE task_number = p_task_number
438 AND project_id = p_project_id;
439
440 RETURN l_id;
441
442
443 EXCEPTION
444
445 WHEN NO_DATA_FOUND THEN
446 RETURN NULL;
447
448 WHEN OTHERS THEN
449 x_err_text := G_PKG_NAME || ' : (Task Value-id conversion)'
450 || substrb(SQLERRM,1,200);
451 RETURN FND_API.G_MISS_NUM;
452
453 END Task;
454
455
456 -- Requestor
457 /*****************************************************************************
458 * Function : Requestor
459 * Returns : NULL if the function is unsuccessful else the Id of the
460 * approval list name
461 * Purpose : Convert the Requestor to its ID.
462 *****************************************************************************/
463
464 FUNCTION Requestor
465 ( p_requestor IN VARCHAR2
466 , p_organization_id IN NUMBER
467 , x_err_text OUT NOCOPY VARCHAR2
468 )
469
470 RETURN NUMBER
471 IS
472 l_id NUMBER;
473 BEGIN
474
475 l_id := Eng_Change_Common_Util.Get_User_Party_Id
476 ( p_user_name => p_requestor
477 , x_err_text => x_err_text);
478
479 RETURN l_id;
480
481 END Requestor;
482
483 --* Function added for Bug 4402842
484 --* Employee
485 /*****************************************************************************
486 * Function : Employee
487 * Returns : NULL if the function is unsuccessful else the Id of the
488 * Employee
489 * Purpose : Convert the Employee Number to its Party Id
490 *****************************************************************************/
491 FUNCTION Employee (
492 p_employee_number IN VARCHAR2
493 , x_err_text OUT NOCOPY VARCHAR2
494 )
495 RETURN NUMBER IS
496
497 l_party_id NUMBER;
498
499 CURSOR c_employee IS
500 SELECT hz.party_id
501 FROM PER_PEOPLE_F P, HZ_PARTIES HZ , PER_ASSIGNMENTS_X A, PER_PERSON_TYPES T
502 WHERE A.PERSON_ID = P.PERSON_ID
503 AND HZ.PARTY_ID = P.PARTY_ID
504 AND HZ.PARTY_TYPE = 'PERSON'
505 AND A.PRIMARY_FLAG = 'Y'
506 AND A.ASSIGNMENT_TYPE = 'E'
507 AND P.PERSON_TYPE_ID = T.PERSON_TYPE_ID
508 AND P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID
509 AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
510 AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
511 AND T.system_person_TYPE = 'EMP'
512 AND P.EMPLOYEE_NUMBER = p_employee_number;
513
514 BEGIN
515 OPEN c_employee;
516 FETCH c_employee INTO l_party_id;
517 CLOSE c_employee;
518
519 return l_party_id;
520 EXCEPTION
521 WHEN OTHERS THEN
522 IF (c_employee%ISOPEN)
523 THEN
524 CLOSE c_employee;
525 END IF;
526 x_err_text := G_PKG_NAME || ' : (employee Value-id conversion)' || substrb(SQLERRM,1,200);
527 RETURN FND_API.G_MISS_NUM;
528 END Employee;
529 --* End of Bug 4402842
530
531 -- Assignee
532 /*****************************************************************************
533 * Function : Assignee
534 * Returns : NULL if the function is unsuccessful else the Id of the
535 * Assignee name (Person or Group)
536 * Purpose : Convert the Assignee to its party id for Eng Change
537 *****************************************************************************/
538 FUNCTION Assignee
539 ( p_assignee IN VARCHAR2 -- party name
540 --, p_assignee_company_name IN VARCHAR2
541 --, p_organization_id IN NUMBER
542 , x_err_text OUT NOCOPY VARCHAR2
543 )
544
545 RETURN NUMBER
546 IS
547 l_id NUMBER;
548 ret_code NUMBER;
549 BEGIN
550
551 l_id := Eng_Change_Common_Util.Get_User_Party_Id
552 ( p_user_name => p_assignee
553 , x_err_text => x_err_text);
554
555 IF l_id IS NULL THEN -- take this as a group name
556
557 SELECT party_id INTO l_id
558 FROM hz_parties
559 WHERE party_name = p_assignee AND party_type = 'GROUP';
560 END IF;
561
562 RETURN l_id;
563
564 EXCEPTION
565
566 WHEN NO_DATA_FOUND THEN
567 x_err_text := NULL;
568 RETURN NULL;
569
570 WHEN OTHERS THEN
571 x_err_text := G_PKG_NAME || ' : (Assignee Value-id conversion) '
572 || substrb(SQLERRM,1,200);
573 RETURN FND_API.G_MISS_NUM;
574
575 END Assignee ;
576
577 /*
578 FUNCTION Status_Type(p_status_name IN VARCHAR2)
579 RETURN NUMBER
580 IS
581 l_result NUMBER;
582 BEGIN
583 SELECT status_code into l_result from eng_change_statuses_vl
584 where status_name = p_status_name;
585
586 return l_result;
587 EXCEPTION
588 WHEN OTHERS THEN
589 return NULL;
590 END Status_Type;*/
591
592 /* 11.5 .10 we need both status_code and status_type */
593 PROCEDURE Status_Type(
594 p_status_name IN VARCHAR2
595 ,x_status_code OUT NOCOPY NUMBER
596 ,x_status_type OUT NOCOPY NUMBER
597 ,x_return_status OUT NOCOPY VARCHAR2
598 ,p_change_order_type_id IN NUMBER
599 ,p_plm_or_erp IN VARCHAR2
600 )
601 IS
602 l_st_code NUMBER;
603 l_st_type NUMBER;
604
605 BEGIN
606 x_return_status := FND_API.G_RET_STS_SUCCESS;
607 if p_plm_or_erp ='PLM' then
608 SELECT status_code ,status_type into l_st_code,l_st_type
609 from eng_change_statuses_vl
610 where status_name = p_status_name
611 and ((status_code in (select status_code from eng_lifecycle_statuses
612 where entity_name='ENG_CHANGE_TYPE'
613 and entity_id1 = p_change_order_type_id)
614 AND status_type <> 0)
615 OR status_type =0);
616 else
617 SELECT status_code ,status_code into l_st_code,l_st_type
618 /* Changed the above line from SELECT status_code ,status_type into l_st_code,l_st_type for Bug 8823124*/
619 from eng_change_statuses_vl
620 where status_name = p_status_name;
621 -- and status_code =status_type;//commented for bug 3332992
622 end if;
623
624 x_status_code := l_st_code;
625 x_status_type := l_st_type;
626
627 EXCEPTION
628 WHEN NO_DATA_FOUND THEN
629 x_return_status := NULL;
630
631 WHEN OTHERS THEN
632 x_return_status := FND_API.G_MISS_NUM;
633
634 END Status_Type;
635
636
637
638 FUNCTION Approval_Status_Type(p_approval_status_name IN VARCHAR2)
639 RETURN NUMBER
640 IS
641 l_result NUMBER;
642 BEGIN
643 SELECT to_number(lookup_code) into l_result from mfg_lookups
644 where lookup_type = 'ENG_ECN_APPROVAL_STATUS'
645 and meaning = p_approval_status_name;
646
647 return l_result;
648 EXCEPTION
649 WHEN OTHERS THEN
650 return NULL;
651 END Approval_Status_Type;
652
653
654 FUNCTION Line_Status(p_status_name IN VARCHAR2)
655 RETURN VARCHAR2
656 IS
657 l_result VARCHAR2(30);
658 BEGIN
659 /* Status will not be saved in FND table anymore but in eng_change_statuses_vl
660 SELECT lookup_code INTO l_result
661 FROM fnd_lookup_values
662 where lookup_type = 'ENG_CHANGE_LINE_STATUSES'
663 AND meaning = p_status_name AND language = userenv('LANG'); */
664 -- Bug 2908248
665
666 SELECT status_code into l_result from eng_change_statuses_vl
667 where status_name = p_status_name;
668
669 return l_result;
670 EXCEPTION
671 WHEN OTHERS THEN
672 return NULL;
673 END Line_Status;
674
675
676 -- Change_Management_Type
677 /*****************************************************************
678 * FUNCTION : Change_Management_Type
679 * Returns : Change_Mgmt_Type_Code
680 * Purpose : Will verify that the change management type that
681 * the user has specified exists for the language.
682 ******************************************************************/
683 FUNCTION Change_Management_Type
684 ( p_change_management_type IN VARCHAR2
685 , x_err_text OUT NOCOPY VARCHAR2
686 )
687 RETURN VARCHAR2
688 IS
689 l_result VARCHAR2(30);
690
691 BEGIN
692
693
694 SELECT change_mgmt_type_code
695 INTO l_result
696 FROM eng_change_order_types_VL --11.5.10 changes
697 WHERE trim(type_name) = trim(p_change_management_type)
698 and type_classification='CATEGORY' and NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE
699 and START_DATE <=SYSDATE;
700
701 return l_result;
702
703 EXCEPTION
704
705 WHEN NO_DATA_FOUND THEN
706 x_err_text := NULL;
707 RETURN NULL;
708
709 WHEN OTHERS THEN
710 x_err_text := G_PKG_NAME || ' : (Change_Management_Type Value-id conversion) '
711 || substrb(SQLERRM,1,200);
712 RETURN FND_API.G_MISS_CHAR ;
713
714 END Change_Management_Type;
715
716
717 -- Source_Type
718 /*****************************************************************
719 * FUNCTION : Source_Type
720 * Returns : Source_Type_Code
721 * Purpose : Will verify that the source type that
722 * the user has specified exists for the language.
723 ******************************************************************/
724 FUNCTION Source_Type
725 ( p_source_type IN VARCHAR2
726 , x_err_text OUT NOCOPY VARCHAR2
727 )
728 RETURN VARCHAR2
729 IS
730 l_result VARCHAR2(30);
731
732 BEGIN
733
734 SELECT lookup_code
735 INTO l_result
736 FROM fnd_lookup_values_vl
737 WHERE lookup_type = 'ENG_CHANGE_SOURCE_TYPES'
738 AND upper(trim(meaning)) = upper(trim(p_source_type));
739
740 return l_result;
741
742 EXCEPTION
743
744 WHEN NO_DATA_FOUND THEN
745 x_err_text := NULL;
746 RETURN NULL;
747
748 WHEN OTHERS THEN
749 x_err_text := G_PKG_NAME || ' : (Source_Type Value-id conversion) '
750 || substrb(SQLERRM,1,200);
751 RETURN FND_API.G_MISS_CHAR ;
752
753 END Source_Type;
754
755
756 -- Source_Name
757 /*****************************************************************
758 * FUNCTION : Source_Name
759 * Returns : Source_Id
760 * Purpose : Will verify that the source type that
761 * the user has specified exists for the language.
762 ******************************************************************/
763 FUNCTION Source_Name
764 ( p_source_name IN VARCHAR2
765 , p_source_type_code IN VARCHAR2
766 , x_err_text OUT NOCOPY VARCHAR2
767 )
768 RETURN NUMBER
769 IS
770 l_result NUMBER ;
771
772 BEGIN
773
774 SELECT person_id
775 INTO l_result
776 FROM ego_people_v
777 WHERE person_type = p_source_type_code
778 AND Upper(person_name) = Upper(p_source_name);
779
780 return l_result;
781
782 EXCEPTION
783
784 WHEN NO_DATA_FOUND THEN
785 x_err_text := NULL;
786 RETURN NULL;
787
788 WHEN OTHERS THEN
789 x_err_text := G_PKG_NAME || ' : (Source_Name Value-id conversion) '
790 || substrb(SQLERRM,1,200);
791 RETURN FND_API.G_MISS_CHAR ;
792
793 END Source_Name ;
794
795
796 -- Start bug 4967902
797 FUNCTION Hierarchy
798 ( p_organization_hierarchy IN VARCHAR2
799 , x_err_text OUT NOCOPY VARCHAR2
800 )
801 RETURN NUMBER
802 IS
803 l_id NUMBER ;
804
805 BEGIN
806
807 Select organization_structure_id
808 into l_id
809 from per_organization_structures
810 where name = p_organization_hierarchy;
811
812 return l_id;
813
814 EXCEPTION
815
816 WHEN NO_DATA_FOUND THEN
817 x_err_text := NULL;
818 RETURN NULL;
819
820 WHEN OTHERS THEN
821 x_err_text := G_PKG_NAME || ' : (Organization_Hierarchy Value-id conversion) '
822 || substrb(SQLERRM,1,200);
823 RETURN FND_API.G_MISS_CHAR ;
824
825 END Hierarchy ;
826 -- End bug 4967902
827
828 -- Item_Revision
829 /*****************************************************************
830 * FUNCTION : Item_Revision
831 * Returns : Item_Revision_Id in Future
832 * Purpose : Will verify that the Item Revision that
833 * the user has specified exists.
834 ******************************************************************/
835 FUNCTION Item_Revision
836 ( p_item_id IN NUMBER
837 , p_organization_id IN NUMBER
838 , p_item_revision IN VARCHAR2
839 , x_err_text OUT NOCOPY VARCHAR2
840 )
841 RETURN NUMBER
842 IS
843 l_result NUMBER;
844
845 BEGIN
846
847 SELECT -100
848 INTO l_result
849 FROM MTL_ITEM_REVISIONS
850 WHERE inventory_item_id = p_item_id
851 AND organization_id = p_organization_id
852 AND revision = p_item_revision ;
853
854 return l_result;
855
856 EXCEPTION
857
858 WHEN NO_DATA_FOUND THEN
859 x_err_text := NULL;
860 RETURN NULL;
861
862 WHEN OTHERS THEN
863 x_err_text := G_PKG_NAME || ' : (Item Revision Value-id conversion) '
864 || substrb(SQLERRM,1,200);
865 RETURN FND_API.G_MISS_CHAR ;
866
867 END Item_Revision ;
868
869
870 -- Change_Order_Type
871 /**************************************************************************
872 * Procedure : Change_Order_Type
873 * Returns : NUMBER, DISABLE DATE
874 * Purpose : Will convert the change_order_type to change_order_type_id
875 * and will return the id and disable date. If it fails then
876 will return NULL.
877 * For an unexpected error it will return a missing value.
878 ***************************************************************************/
879 PROCEDURE Change_Order_Type
880 ( p_change_order_type IN VARCHAR2
881 , p_change_mgmt_type IN VARCHAR2
882 , x_err_text OUT NOCOPY VARCHAR2
883 , x_change_order_id OUT NOCOPY NUMBER
884 , x_disable_date OUT NOCOPY DATE
885 , x_object_id OUT NOCOPY NUMBER
886 )
887 IS
888 BEGIN
889
890
891
892 SELECT change_order_type_id, disable_date, object_id
893 INTO x_change_order_id, x_disable_date, x_object_id
894 FROM eng_change_order_types_vl
895 WHERE type_name = p_change_order_type
896 AND change_mgmt_type_code = p_change_mgmt_type
897 AND type_classification='HEADER';
898
899 EXCEPTION
900
901 WHEN NO_DATA_FOUND THEN
902 x_change_order_id := NULL;
903
904 WHEN OTHERS THEN
905 x_err_text := G_PKG_NAME || ' : (Change Order Type Value-id conversion) '
906 || substrb(SQLERRM,1,200);
907 x_change_order_id := FND_API.G_MISS_NUM;
908
909 END Change_Order_Type;
910
911 -- Change_Order_Line_Type
912 /**************************************************************************
913 * Procedure : Change_Order_Line_Type
914 * Returns : NUMBER, DISABLE DATE
915 * Purpose : Procedure added on 25-Feb-2004 to enable line types import.
916 * Bug No: 3463472
917 * Issue: DEF-1694
918 * Will convert the change_order_type to change_order_type_id
919 * and will return the id and disable date. If it fails then
920 will return NULL. This works only for Line Types
921 * For an unexpected error it will return a missing value.
922 ***************************************************************************/
923 PROCEDURE Change_Order_Line_Type
924 ( p_change_order_type IN VARCHAR2
925 , p_change_mgmt_type IN VARCHAR2
926 , x_err_text OUT NOCOPY VARCHAR2
927 , x_change_order_id OUT NOCOPY NUMBER
928 , x_disable_date OUT NOCOPY DATE
929 , x_object_id OUT NOCOPY NUMBER
930 )
931 IS
932 BEGIN
933
934 SELECT change_order_type_id, disable_date, object_id
935 INTO x_change_order_id, x_disable_date, x_object_id
936 FROM eng_change_order_types_vl
937 WHERE type_name = p_change_order_type
938 AND change_mgmt_type_code = p_change_mgmt_type
939 AND type_classification='LINE';
940
941 EXCEPTION
942
943 WHEN NO_DATA_FOUND THEN
944 x_change_order_id := NULL;
945
946 WHEN OTHERS THEN
947 x_err_text := G_PKG_NAME || ' : (Change Order Type Value-id conversion) '
948 || substrb(SQLERRM,1,200);
949 x_change_order_id := FND_API.G_MISS_NUM;
950
951 END Change_Order_Line_Type;
952
953
954 -- Change_Mgmt_Type
955 /**************************************************************************
956 * Procedure : Change_Mgmt_Type
957 * Returns : Change mgmt type code
958 * Purpose : Will convert the change mgmt type name to the
959 * corresponding code. If it fails then will return NULL.
960 ***************************************************************************/
961 PROCEDURE Change_Mgmt_Type
962 (
963 p_change_mgmt_type_name IN VARCHAR2
964 , x_change_mgmt_type_code OUT NOCOPY VARCHAR2
965 , x_err_text OUT NOCOPY VARCHAR2
966 )
967 IS
968 BEGIN
969
970 SELECT change_mgmt_type_code
971 INTO x_change_mgmt_type_code
972 FROM eng_change_order_types_vl
973 WHERE type_name = p_change_mgmt_type_name
974 and type_classification='CATEGORY'
975 and NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE
976 and START_DATE <=SYSDATE; --11.5.10
977
978 EXCEPTION
979
980 WHEN OTHERS THEN
981 x_change_mgmt_type_code := NULL;
982 x_err_text := G_PKG_NAME || ' : (Change Mgmt Type Value-id conversion) '
983 || substrb(SQLERRM,1,200);
984
985 END Change_Mgmt_Type;
986
987
988 -- From Work Order and To Work Order
989 /*****************************************************************************
990 * Added by MK for ECO New Effectivities on 08/24/2000
991 * Function : Work_Order
992 * Returns : NULL if the function is unsuccessful else the Id of the
993 * work order number
994 * Purpose : Convert the Requestor to its ID.
995 *****************************************************************************/
996
997 FUNCTION Work_Order
998 ( p_work_order IN VARCHAR2
999 , p_organization_id IN NUMBER
1000 , x_err_text OUT NOCOPY VARCHAR2
1001 )
1002
1003 RETURN NUMBER
1004 IS
1005 l_id NUMBER;
1006 ret_code NUMBER;
1007 BEGIN
1008
1009 SELECT wip_entity_id
1010 INTO l_id
1011 FROM WIP_ENTITIES
1012 WHERE organization_id = p_organization_id
1013 AND wip_entity_name = p_work_order ;
1014
1015 RETURN l_id;
1016
1017 EXCEPTION
1018
1019 WHEN NO_DATA_FOUND THEN
1020 x_err_text := NULL;
1021 RETURN NULL;
1022
1023 WHEN OTHERS THEN
1024 x_err_text := G_PKG_NAME || ' : (Work Order Value-id conversion) '
1025 || substrb(SQLERRM,1,200);
1026 END Work_Order ;
1027
1028 -- Change Order VID conversion procedure
1029 -- changed the signature to get status id :enhancement:5414834
1030 PROCEDURE Change_Order_VID
1031 ( p_ECO_rec IN Eng_Eco_Pub.ECO_Rec_Type
1032 , p_old_eco_unexp_rec IN Eng_Eco_Pub.ECO_Unexposed_Rec_Type
1033 , P_eco_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.ECO_Unexposed_Rec_Type
1034 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1035 , x_return_status OUT NOCOPY VARCHAR2
1036 )
1037 IS
1038 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1039 l_change_order_type_id NUMBER;
1040 l_change_mgmt_type_code VARCHAR2(30);
1041 l_disable_date DATE;
1042 l_object_id NUMBER;
1043 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1044 l_err_text VARCHAR2(2000);
1045
1046 l_change_type_code VARCHAR2(80);
1047 l_change_mgmt_type_name VARCHAR2(45);
1048 l_change_notice VARCHAR2(10);
1049 l_transaction_type VARCHAR2(10);
1050 l_old_change_type_id NUMBER;
1051 l_old_change_mgmt_type VARCHAR2(30);
1052
1053
1054 l_status_type NUMBER;
1055 l_status_code NUMBER;
1056
1057 BEGIN
1058 l_change_type_code := p_ECO_rec.change_type_code;
1059 l_change_mgmt_type_name := p_ECO_rec.change_management_type;
1060 l_change_notice := p_ECO_rec.ECO_Name;
1061 l_transaction_type := p_ECO_rec.transaction_type;
1062 l_old_change_type_id := p_old_eco_unexp_rec.change_order_type_id;
1063 l_old_change_mgmt_type := p_old_eco_unexp_rec.change_mgmt_type_code;
1064
1065 l_token_tbl(1).token_name := 'ECO_NAME';
1066 l_token_tbl(1).token_value := l_change_notice;
1067
1068 IF l_change_mgmt_type_name IS NULL OR
1069 l_change_mgmt_type_name = FND_API.G_MISS_CHAR
1070 THEN
1071 l_change_mgmt_type_code := 'CHANGE_ORDER';
1072 ELSE
1073 Change_Mgmt_Type
1074 (
1075 p_change_mgmt_type_name => l_change_mgmt_type_name
1076 , x_change_mgmt_type_code => l_change_mgmt_type_code
1077 , x_err_text => l_err_text
1078 );
1079 END IF;
1080
1081 IF l_change_mgmt_type_code IS NULL
1082 THEN
1083 l_token_tbl(2).token_name := 'CHANGE_MGMT_TYPE_NAME';
1084 l_token_tbl(2).token_value := l_change_mgmt_type_name;
1085
1086 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1087 THEN
1088 Error_Handler.Add_Error_Token
1089 (
1090 p_Message_Name => 'ENG_CHANGE_MGMT_TYPE_INVALID'
1091 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1092 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1093 , p_Token_Tbl => l_Token_Tbl
1094 );
1095 END IF;
1096
1097 x_Return_Status := FND_API.G_RET_STS_ERROR;
1098 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1099 return; --added for bug 10234492
1100 END IF;
1101
1102
1103 -- Convert change_order_type_code to change_order_type_id
1104
1105 IF l_change_type_code IS NOT NULL AND
1106 l_change_type_code <> FND_API.G_MISS_CHAR AND
1107 l_change_mgmt_type_code IS NOT NULL
1108 THEN
1109 Change_Order_Type
1110 ( p_change_order_type => l_change_type_code
1111 , p_change_mgmt_type => l_change_mgmt_type_code
1112 , x_err_text => l_err_text
1113 , x_change_order_id => l_change_order_type_id
1114 , x_disable_date => l_disable_date
1115 , x_object_id => l_object_id
1116 );
1117
1118 IF l_change_order_type_id IS NULL
1119 THEN
1120 l_token_tbl(2).token_name := 'CHANGE_TYPE_CODE';
1121 l_token_tbl(2).token_value := l_change_type_code;
1122
1123 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1124 THEN
1125 Error_Handler.Add_Error_Token
1126 ( p_Message_Name => 'ENG_CHANGE_TYPE_INVALID'
1127 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1128 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1129 , p_Token_Tbl => l_Token_Tbl
1130 );
1131 END IF;
1132
1133 x_Return_Status := FND_API.G_RET_STS_ERROR;
1134 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1135 ELSIF l_change_order_type_id = FND_API.G_MISS_NUM
1136 THEN
1137 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1138 THEN
1139 Error_Handler.Add_Error_Token
1140 ( p_Message_Text => l_err_text
1141 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1142 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1143 );
1144 END IF;
1145
1146 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1148 ELSE
1149 p_eco_unexp_rec.change_order_type_id := l_change_order_type_id;
1150 p_eco_unexp_rec.change_mgmt_type_code := l_change_mgmt_type_code;
1151 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1152 END IF;
1153 ELSE
1154 IF l_transaction_type = Bom_GLOBALS.G_OPR_CREATE
1155 THEN
1156 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1157 THEN
1158 Error_Handler.Add_Error_Token
1159 ( p_Message_Name => 'ENG_CHANGE_TYPE_MISSING'
1160 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1161 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1162 , p_Token_Tbl => l_Token_Tbl
1163 );
1164 END IF;
1165 x_Return_Status := FND_API.G_RET_STS_ERROR;
1166 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1167 p_eco_unexp_rec.change_order_type_id := NULL;
1168 ELSE
1169 p_eco_unexp_rec.change_order_type_id := l_old_change_type_id;
1170 p_eco_unexp_rec.change_mgmt_type_code := l_old_change_mgmt_type;
1171 END IF;
1172 END IF;
1173
1174 -- Change order type must not be disabled
1175
1176 IF l_disable_date < SYSDATE
1177 THEN
1178 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1179 THEN
1180 l_token_tbl(2).token_name := 'CHANGE_TYPE_CODE';
1181 l_token_tbl(2).token_value := l_change_type_code;
1182 Error_Handler.Add_Error_Token
1183 ( p_Message_Name => 'ENG_CHANGE_TYPE_DISABLED'
1184 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1185 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1186 , p_Token_Tbl => l_Token_Tbl
1187 );
1188 END IF;
1189 x_Return_Status := FND_API.G_RET_STS_ERROR;
1190 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1191 p_eco_unexp_rec.change_order_type_id := l_change_order_type_id;
1192 END IF;
1193 --folowing has been added for bug 10234492
1194 if x_Return_Status = FND_API.G_RET_STS_ERROR then
1195 return;
1196 end if;
1197
1198
1199 IF p_ECO_rec.Status_Name IS NOT NULL AND
1200 p_ECO_rec.Status_Name <> FND_API.G_MISS_CHAR
1201 THEN
1202 Status_Type(p_status_name => p_ECO_rec.Status_Name
1203 ,x_status_code => l_status_code
1204 ,x_status_type => l_status_type
1205 , x_return_status => x_return_status
1206 ,p_change_order_type_id =>p_ECO_Unexp_Rec.change_order_type_id
1207 ,p_plm_or_erp => p_ECO_rec.plm_or_erp_change);
1208 IF l_status_type IS NULL or l_status_code IS NULL
1209 THEN
1210 l_token_tbl(1).token_name := 'STATUS_TYPE';
1211 l_token_tbl(1).token_value := p_ECO_rec.Status_Name;
1212
1213 Error_Handler.Add_Error_Token
1214 ( p_message_name => 'ENG_STATUS_TYPE_INVALID'
1215 , p_mesg_token_tbl => l_mesg_token_tbl
1216 , p_token_tbl => l_token_tbl
1217 , x_mesg_token_tbl => l_mesg_token_tbl
1218 );
1219
1220 l_token_tbl.DELETE;
1221 x_return_status := FND_API.G_RET_STS_ERROR;
1222 ELSE
1223 p_ECO_Unexp_Rec.Status_Type := l_status_type ;
1224 p_ECO_Unexp_Rec.Status_Code := l_status_code ;
1225 END IF;
1226
1227 ELSE
1228 p_ECO_Unexp_Rec.Status_Type := NULL;
1229 END IF;
1230
1231 END Change_Order_VID;
1232
1233 /***************************************************************************
1234 * Function : Responsible_Org
1235 * Returns : NUMBER
1236 * Purpose : Will convert the value of responsible_org to organization_id
1237 * using the table HR_ALL_ORGANIZATION_UNITS.
1238 * If the conversion fails then the function will return a NULL
1239 * otherwise will return the org_id. For an unexpected error
1240 * function will return a missing value.
1241 ****************************************************************************/
1242 FUNCTION Responsible_Org
1243 ( p_responsible_org IN VARCHAR2
1244 , p_current_org IN NUMBER
1245 , x_err_text OUT NOCOPY VARCHAR2
1246 )
1247 RETURN NUMBER
1248 IS
1249 l_id NUMBER;
1250 ret_code NUMBER;
1251 BEGIN
1252 -- Bug 4947857
1253 -- The following query has been fixed to fetch valid departments immaterial of the business group in context.
1254 -- The view hr_organization_units in iteself is restricted based on the profile HR: Cross Business group
1255 -- Value and per_business_group_id in context if the prior value is N.
1256 -- Also , it is being assumed here that the user will login to Oracle Appliction for doing an import from
1257 -- 11.5.10 onwards because Change Import concurrent pogram is used. Otherwise the query should return all
1258 -- departments
1259 SELECT hou.organization_id
1260 INTO l_id
1261 FROM hr_organization_units hou
1262 -- , org_organization_definitions org_def
1263 WHERE hou.name = p_responsible_org
1264 --AND org_def.business_group_id = hou.business_group_id
1265 --AND org_def.organization_id = p_current_org ;
1266 AND exists (SELECT null FROM hr_organization_information hoi
1267 WHERE hoi.organization_id = hou.organization_id
1268 AND hoi.org_information_context = 'CLASS'
1269 AND hoi.org_information1 = 'BOM_ECOD'
1270 AND hoi.org_information2 = 'Y');
1271
1272 RETURN l_id;
1273
1274 EXCEPTION
1275
1276 WHEN NO_DATA_FOUND THEN
1277 RETURN NULL;
1278
1279 WHEN OTHERS THEN
1280 x_err_text := G_PKG_NAME || ' : (Responsible Org Value-id conversion) '
1281 || substrb(SQLERRM,1,200);
1282 RETURN FND_API.G_MISS_NUM;
1283
1284 END Responsible_Org;
1285
1286 /***************************************************************************
1287 * Function : Organization
1288 * Returns : NUMBER
1289 * Purpose : Will convert the value of organization_code to organization_id
1290 * using MTL_PARAMETERS.
1291 * If the conversion fails then the function will return a NULL
1292 * otherwise will return the org_id. For an unexpected error
1293 * function will return a missing value.
1294 ****************************************************************************/
1295 FUNCTION Organization(p_organization IN VARCHAR2,
1296 x_err_text OUT NOCOPY VARCHAR2 )
1297
1298 RETURN NUMBER
1299 IS
1300 l_id NUMBER;
1301 ret_code NUMBER;
1302 l_err_text VARCHAR2(2000);
1303 BEGIN
1304
1305 SELECT organization_id
1306 INTO l_id
1307 FROM mtl_parameters
1308 WHERE organization_code = p_organization;
1309
1310 RETURN l_id;
1311
1312 EXCEPTION
1313
1314 WHEN NO_DATA_FOUND THEN
1315 RETURN NULL;
1316
1317 WHEN OTHERS THEN
1318 RETURN FND_API.G_MISS_NUM;
1319
1320 END Organization;
1321
1322 /****************************************************************************
1323 * Function : Revised_Item
1324 * Parameters IN : Revised Item Name
1325 * Organization ID
1326 * Parameters OUT: Error_Text
1327 * Returns : Revised Item Id
1328 * Purpose : This function will get the ID for the revised item and
1329 * return the ID. If the revised item is invalid then the
1330 * ID will returned as NULL.
1331 ****************************************************************************/
1332 FUNCTION Revised_Item( p_revised_item_num IN VARCHAR2,
1333 p_organization_id IN NUMBER,
1334 x_err_text OUT NOCOPY VARCHAR2 )
1335 RETURN NUMBER
1336 IS
1337 l_id NUMBER;
1338 ret_code NUMBER;
1339 l_err_text VARCHAR2(2000);
1340 BEGIN
1341
1342 /* ret_code := INVPUOPI.mtl_pr_parse_flex_name(
1343 org_id => p_organization_id,
1344 flex_code => 'MSTK',
1345 flex_name => p_revised_item_num,
1346 flex_id => l_id,
1347 set_id => -1,
1348 err_text => x_err_text);
1349
1350 IF (ret_code <> 0) THEN
1351 RETURN NULL;
1352 ELSE
1353 RETURN l_id;
1354 END IF;
1355 */
1356 select inventory_item_id into l_id
1357 from mtl_system_items_kfv
1358 where concatenated_segments = p_revised_item_num
1359 and organization_id = p_organization_id;
1360
1361 return l_id;
1362
1363 EXCEPTION
1364 when others then
1365 return null;
1366
1367 END Revised_Item;
1368
1369
1370 FUNCTION Revised_Item_Code( p_revised_item_num IN NUMBER,
1371 p_organization_id IN NUMBER,
1372 p_revison_code IN VARCHAR2 )
1373 RETURN number
1374 IS
1375 l_id NUMBER;
1376 ret_code NUMBER;
1377 BEGIN
1378 select revision_id into l_id
1379 from mtl_item_revisions
1380 where inventory_item_id = p_revised_item_num
1381 and organization_id = p_organization_id
1382 and revision = p_revison_code ;
1383
1384 return l_id;
1385
1386 EXCEPTION
1387 when others then
1388 return null;
1389
1390 END Revised_Item_Code;
1391
1392 -- Use_Up_Item
1393
1394 FUNCTION Use_Up_Item( p_use_up_item_num IN VARCHAR2,
1395 p_organization_id IN NUMBER,
1396 x_err_text OUT NOCOPY VARCHAR2 )
1397 RETURN NUMBER
1398 IS
1399 l_id NUMBER;
1400 ret_code NUMBER;
1401 l_err_text VARCHAR2(2000);
1402 BEGIN
1403
1404 /*ret_code := INVPUOPI.mtl_pr_parse_flex_name(
1405 org_id => p_organization_id,
1406 flex_code => 'MSTK',
1407 flex_name => p_use_up_item_num,
1408 flex_id => l_id,
1409 set_id => -1,
1410 err_text => x_err_text);
1411
1412 IF (ret_code <> 0) THEN
1413 RETURN NULL;
1414 END IF;*/
1415 select inventory_item_id into l_id
1416 from mtl_system_items_kfv
1417 where concatenated_segments = p_use_up_item_num
1418 and organization_id = p_organization_id;
1419
1420 RETURN l_id;
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 RETURN NULL;
1424 END Use_Up_Item;
1425
1426 -- Assembly item id
1427
1428 FUNCTION ASSEMBLY_ITEM( p_organization_id IN NUMBER,
1429 p_assembly_item_num IN VARCHAR2,
1430 x_err_text OUT NOCOPY VARCHAR2)
1431 return NUMBER
1432 IS
1433 l_id NUMBER;
1434 ret_code NUMBER;
1435 BEGIN
1436
1437 /*ret_code := INVPUOPI.mtl_pr_parse_flex_name(
1438 org_id => p_organization_id,
1439 flex_code => 'MSTK',
1440 flex_name => p_assembly_item_num,
1441 flex_id => l_id,
1442 set_id => -1,
1443 err_text => x_err_text);
1444
1445 IF (ret_code <> 0) THEN
1446 NULL;
1447 END IF;*/
1448 select inventory_item_id into l_id
1449 from mtl_system_items_kfv
1450 where concatenated_segments = p_assembly_item_num
1451 and organization_id = p_organization_id;
1452
1453 RETURN l_id;
1454 EXCEPTION
1455 WHEN OTHERS THEN
1456 RETURN NULL;
1457 END;
1458
1459
1460 -- Bill_Sequence
1461
1462 FUNCTION Bill_Sequence( p_assembly_item_id IN NUMBER,
1463 p_alternate_bom_designator IN VARCHAR2,
1464 p_organization_id IN NUMBER,
1465 x_err_text OUT NOCOPY VARCHAR2
1466 )
1467 RETURN NUMBER
1468 IS
1469 l_id NUMBER;
1470 l_err_text VARCHAR2(2000);
1471 BEGIN
1472
1473 SELECT bill_sequence_id
1474 INTO l_id
1475 FROM bom_bill_of_materials
1476 WHERE assembly_item_id = p_assembly_item_id
1477 AND NVL(alternate_bom_designator, 'NONE') =
1478 NVL(p_alternate_bom_designator, 'NONE')
1479 AND organization_id = p_organization_id;
1480
1481 RETURN l_id;
1482
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 RETURN NULL;
1486
1487
1488 END Bill_Sequence;
1489
1490
1491 FUNCTION BillandAssembly( p_revised_item_seq_id IN NUMBER,
1492 x_bill_sequence_id OUT NOCOPY NUMBER,
1493 x_assembly_item_id OUT NOCOPY NUMBER,
1494 x_err_text OUT NOCOPY VARCHAR2)
1495 RETURN NUMBER IS
1496 l_dummy VARCHAR2(80);
1497 CURSOR c_BillExists IS
1498 SELECT 'Valid' b_valid
1499 FROM eng_revised_items
1500 WHERE revised_item_sequence_id = p_revised_item_seq_id
1501 AND bill_sequence_id IS NOT NULL;
1502 BEGIN
1503 -- Derive Bill_Sequence_id and assembly_item_id from Revised_item_sequence_id
1504 --Do this only if the Bill of that item exists.
1505
1506 /********************** Temporarily commented ****************
1507
1508 FOR BillExists IN c_BillExists LOOP
1509 BEGIN
1510 SELECT bill_sequence_id,
1511 revised_item_id
1512 INTO x_bill_sequence_id,
1513 x_assembly_item_id
1514 FROM eng_revised_items
1515 WHERE revised_item_sequence_id = p_revised_item_seq_id;
1516 ****************************************/
1517
1518 RETURN 1; --indicating success of the conversion
1519
1520
1521 END BillandAssembly;
1522
1523 /*************************************************************************
1524 * Function : BillAndRevItemSeq
1525 * Parameters IN : Revised Item Unique Key information
1526 * Parameters OUT: Bill Sequence ID
1527 * Returns : Revised Item Sequence
1528 * Purpose : Will use the revised item information to find the bill
1529 * sequence and the revised item sequence.
1530 FUNCTION BillAndRevItemSeq( p_revised_item_id IN NUMBER
1531 , p_item_revision IN VARCHAR2
1532 , p_effective_date IN DATE
1533 , p_change_notice IN VARCHAR2
1534 , p_organization_id IN NUMBER
1535 , p_from_end_item_number IN NUMBER := NULL
1536 , x_Bill_Sequence_Id OUT NOCOPY NUMBER
1537 )
1538 RETURN NUMBER
1539 IS
1540 l_Bill_Seq NUMBER;
1541 l_Rev_Item_Seq NUMBER;
1542 BEGIN
1543 SELECT bill_sequence_id, revised_item_Sequence_id
1544 INTO l_Bill_Seq, l_Rev_Item_Seq
1545 FROM eng_revised_items
1546 WHERE revised_item_id = p_revised_item_id
1547 AND NVL(new_item_revision,'NULL')= NVL(p_item_revision,'NULL')
1548 AND TRUNC(scheduled_date) = trunc(p_effective_date)
1549 AND NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR)
1550 = NVL(p_from_end_item_number, FND_API.G_MISS_CHAR)
1551 AND change_notice = p_change_notice
1552 AND organization_id = p_organization_id;
1553
1554 x_Bill_Sequence_Id := l_Bill_Seq;
1555 RETURN l_Rev_Item_Seq;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 x_Bill_Sequence_Id := NULL;
1560 RETURN NULL;
1561 END BillAndRevItemSeq;
1562 **************************************************************************/
1563
1564
1565 FUNCTION AsmblyFromRevItem(p_revised_item_seq_id IN NUMBER,
1566 x_err_text OUT NOCOPY VARCHAR2
1567 )
1568 RETURN NUMBER
1569 IS
1570 l_assembly_item_id NUMBER;
1571 BEGIN
1572 /*********************************************************
1573 SELECT revised_item_id
1574 INTO l_assembly_item_id
1575 FROM eng_revised_items
1576 WHERE revised_item_sequence_id = p_revised_item_seq_id;
1577
1578 RETURN l_assembly_item_id;
1579
1580 EXCEPTION
1581 WHEN NO_DATA_FOUND THEN
1582 RETURN NULL;
1583 **********************************************************/
1584 NULL;
1585 END;
1586
1587 FUNCTION Revised_Item_Sequence( p_revised_item_id IN NUMBER
1588 , p_change_notice IN VARCHAR2
1589 , p_organization_id IN NUMBER
1590 , p_new_item_revision IN VARCHAR2
1591 )
1592 RETURN NUMBER
1593 IS
1594 l_id NUMBER;
1595 BEGIN
1596
1597 SELECT revised_item_sequence_id
1598 INTO l_id
1599 FROM Eng_revised_items
1600 WHERE revised_item_id = p_revised_item_id
1601 AND change_notice = p_change_notice
1602 AND organization_id = p_organization_id
1603 AND NVL(new_item_revision, 'NONE') =
1604 NVL(p_new_item_revision, 'NONE');
1605
1606 RETURN l_id;
1607
1608 EXCEPTION
1609
1610 WHEN NO_DATA_FOUND THEN
1611
1612 RETURN NULL;
1613
1614 WHEN OTHERS THEN
1615 RETURN FND_API.G_MISS_NUM;
1616
1617 END Revised_Item_Sequence;
1618
1619
1620 -- Eco Revision
1621 FUNCTION Revision ( p_rev IN VARCHAR2
1622 , p_organization_id IN NUMBER
1623 , p_change_notice IN VARCHAR2
1624 , x_err_text OUT NOCOPY VARCHAR2
1625 ) RETURN NUMBER
1626 IS
1627 l_revision_id NUMBER;
1628 BEGIN
1629 SELECT revision_id
1630 INTO l_revision_id
1631 FROM eng_change_order_revisions
1632 WHERE change_notice = p_change_notice
1633 AND organization_id = p_organization_id
1634 AND revision = p_rev;
1635
1636 RETURN l_revision_id;
1637
1638 EXCEPTION
1639 WHEN NO_DATA_FOUND THEN
1640 RETURN NULL;
1641 WHEN OTHERS THEN
1642 RETURN FND_API.G_MISS_NUM;
1643
1644 END Revision;
1645
1646 /* 11.5.10 Function to return parent_revised_item_sequence_id ,
1647 given revised_item_id ,schedule_date and alternate_bom_designator
1648 */
1649
1650 FUNCTION ParentRevSeqId
1651 ( parent_item_name IN VARCHAR2
1652 , p_organization_id IN NUMBER
1653 , p_alternate_bom_code IN VARCHAR2
1654 ,p_schedule_date DATE
1655 ,p_change_id NUMBER
1656 ) RETURN NUMBER
1657 IS
1658 cursor parent (rev_item_id eng_revised_items.revised_item_id%TYPE
1659 ) is
1660 select revised_item_sequence_id
1661 from eng_revised_items
1662 where REVISED_ITEM_ID = rev_item_id and
1663 organization_id = p_organization_id and
1664 nvl(alternate_bom_designator,'NULL') = nvl(p_alternate_bom_code,'NULL')and
1665 SCHEDULED_DATE = p_schedule_date and
1666 change_id= p_change_id ;
1667
1668
1669
1670 l_id NUMBER;
1671 l_revised_item_seq_id NUMBER;
1672 ret_code NUMBER;
1673 l_err_text VARCHAR2(2000);
1674 begin
1675
1676 l_id := Use_Up_Item( p_use_up_item_num =>
1677 parent_item_name
1678 , p_organization_id =>
1679 p_organization_id
1680 , x_err_text => l_Err_Text);
1681
1682 open parent(l_id);
1683 fetch parent into l_revised_item_seq_id;
1684 close parent;
1685
1686 return l_revised_item_seq_id;
1687
1688 EXCEPTION
1689 WHEN NO_DATA_FOUND THEN
1690 RETURN NULL;
1691 WHEN OTHERS THEN
1692 RETURN FND_API.G_MISS_NUM;
1693
1694
1695 END ParentRevSeqId;
1696
1697 --11.5.10
1698
1699 -- 11.5.10E
1700 /**************************************************************************
1701 * Function : From_Revision_Id
1702 * Returns : NUMBER
1703 * Purpose : This function takes the from revision and returns the
1704 * revision Id for the revised Item. If the
1705 * revision is passed as null, then the revision Id of the
1706 * current revision is returned.
1707 ***************************************************************************/
1708
1709 FUNCTION From_Revision_Id( p_assembly_item_id IN VARCHAR2,
1710 p_organization_id IN NUMBER,
1711 p_revision IN VARCHAR2,
1712 p_revision_date IN DATE,
1713 x_err_text OUT NOCOPY VARCHAR2 )
1714 RETURN NUMBER
1715 IS
1716 l_revision_id NUMBER;
1717 l_revision VARCHAR2(3);
1718 l_cur_rev_ef_date DATE;
1719
1720 CURSOR FROM_REVISION_ID_CURRENT IS
1721 SELECT REVISION_ID, REVISION, EFFECTIVITY_DATE
1722 FROM MTL_ITEM_REVISIONS
1723 WHERE INVENTORY_ITEM_ID = p_assembly_item_id
1724 AND ORGANIZATION_ID = p_organization_id
1725 AND EFFECTIVITY_DATE <= p_revision_date
1726 AND IMPLEMENTATION_DATE IS NOT NULL
1727 ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
1728
1729 CURSOR FROM_REVISION_ID_FUTURE(p_date IN DATE) IS
1730 SELECT REVISION_ID
1731 FROM MTL_ITEM_REVISIONS
1732 WHERE INVENTORY_ITEM_ID = p_assembly_item_id
1733 AND ORGANIZATION_ID = p_organization_id
1734 AND REVISION = p_revision
1735 AND EFFECTIVITY_DATE > p_date;
1736 BEGIN
1737 OPEN FROM_REVISION_ID_CURRENT;
1738 FETCH FROM_REVISION_ID_CURRENT INTO l_revision_id, l_revision, l_cur_rev_ef_date;
1739 CLOSE FROM_REVISION_ID_CURRENT;
1740
1741 IF (l_revision = p_revision)
1742 THEN
1743 RETURN l_revision_id;
1744 ELSE
1745 l_revision_id := NULL;
1746
1747 OPEN FROM_REVISION_ID_FUTURE(l_cur_rev_ef_date);
1748 FETCH FROM_REVISION_ID_FUTURE INTO l_revision_id;
1749 CLOSE FROM_REVISION_ID_FUTURE;
1750 RETURN l_revision_id;
1751 END IF;
1752
1753 EXCEPTION
1754 WHEN NO_DATA_FOUND THEN
1755 IF FROM_REVISION_ID_CURRENT%ISOPEN
1756 THEN
1757 CLOSE FROM_REVISION_ID_CURRENT;
1758 END IF;
1759 IF FROM_REVISION_ID_FUTURE%ISOPEN
1760 THEN
1761 CLOSE FROM_REVISION_ID_FUTURE;
1762 END IF;
1763
1764 RETURN NULL;
1765
1766 WHEN OTHERS THEN
1767 IF FROM_REVISION_ID_CURRENT%ISOPEN
1768 THEN
1769 CLOSE FROM_REVISION_ID_CURRENT;
1770 END IF;
1771 IF FROM_REVISION_ID_FUTURE%ISOPEN
1772 THEN
1773 CLOSE FROM_REVISION_ID_FUTURE;
1774 END IF;
1775
1776 RETURN FND_API.G_MISS_NUM;
1777
1778 END From_Revision_Id;
1779
1780
1781 /**************************************************************************
1782 * Function : New_Revision_Reason_Code
1783 * Returns : VARCHAR2
1784 * Purpose : This function takes the reason and returns the reason
1785 * code for the reason given.
1786 ***************************************************************************/
1787
1788 FUNCTION New_Revision_Reason_Code( p_reason IN VARCHAR2,
1789 x_err_text OUT NOCOPY VARCHAR2 )
1790 RETURN VARCHAR2
1791 IS
1792 l_reason_code VARCHAR2(30) := NULL;
1793
1794 BEGIN
1795 SELECT LOOKUP_CODE
1796 INTO l_reason_code
1797 FROM FND_LOOKUPS
1798 WHERE LOOKUP_TYPE = 'EGO_ITEM_REVISION_REASON'
1799 AND ENABLED_FLAG = 'Y'
1800 AND MEANING = p_reason;
1801
1802 RETURN l_reason_code;
1803
1804 EXCEPTION
1805 WHEN NO_DATA_FOUND THEN
1806 RETURN NULL;
1807 WHEN OTHERS THEN
1808 RETURN FND_API.G_MISS_CHAR;
1809 END New_Revision_Reason_Code;
1810
1811
1812 /**************************************************************************
1813 * Function : Get_Structure_Type_Id
1814 * Returns : NUMBER
1815 * Purpose : This function takes the structure name and returns the
1816 * structure id.
1817 ***************************************************************************/
1818
1819 FUNCTION Get_Structure_Type_Id( p_structure_type_name IN VARCHAR2,
1820 x_err_text OUT NOCOPY VARCHAR2 )
1821 RETURN VARCHAR2
1822 IS
1823 l_structure_type_id VARCHAR2(30) := NULL;
1824
1825 BEGIN
1826 SELECT structure_type_id
1827 INTO l_structure_type_id
1828 FROM bom_structure_types_vl
1829 WHERE structure_type_name = p_structure_type_name;
1830
1831 RETURN l_structure_type_id;
1832
1833 EXCEPTION
1834 WHEN NO_DATA_FOUND THEN
1835 RETURN NULL;
1836 WHEN OTHERS THEN
1837 RETURN FND_API.G_MISS_CHAR;
1838 END Get_Structure_Type_Id;
1839
1840
1841 /****************************************************************************
1842 * Procedure : Revised_Item_VID
1843 * Parameters IN : Revised Item Unexposed column Record
1844 * Revised Item exposed column record
1845 * Parameters OUT: Revised item Unexposed column record after conversion
1846 * Return Status
1847 * Mesg_Token_Tbl
1848 * Purpose : This procedure will drive the Value-To_Id conversion for the
1849 * revised item entity.
1850 ****************************************************************************/
1851 PROCEDURE Revised_Item_VID
1852 ( x_Return_Status OUT NOCOPY VARCHAR2
1853 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1854 , p_rev_item_unexp_Rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
1855 , x_rev_item_unexp_Rec IN OUT NOCOPY Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
1856 , p_revised_item_Rec IN Eng_Eco_Pub.Revised_Item_Rec_Type
1857 )
1858 IS
1859 l_return_value NUMBER;
1860 l_Return_Status VARCHAR2(1);
1861 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1862 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1863 l_Rev_Item_Unexp_Rec Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type;
1864 l_Err_Text VARCHAR2(2000);
1865 l_bill_seq_id NUMBER;
1866 l_eco_type VARCHAR2(3);
1867 l_from_revision VARCHAR2(3) := NULL;
1868 l_from_revision_id NUMBER;
1869 l_structure_type_id NUMBER;
1870
1871 BEGIN
1872 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
1873 l_Rev_item_Unexp_Rec := p_Rev_item_Unexp_Rec;
1874
1875 IF p_revised_item_rec.use_up_item_name IS NOT NULL AND
1876 p_revised_item_rec.use_up_item_name <> FND_API.G_MISS_CHAR
1877 THEN
1878 l_rev_item_unexp_rec.use_up_item_id :=
1879 Use_Up_Item( p_use_up_item_num =>
1880 p_revised_item_rec.use_up_item_name
1881 , p_organization_id =>
1882 p_rev_item_unexp_Rec.organization_id
1883 , x_err_text => l_Err_Text);
1884
1885 IF l_rev_item_unexp_rec.use_up_item_id IS NULL
1886 THEN
1887 l_return_status := FND_API.G_RET_STS_ERROR;
1888
1889 l_token_tbl(1).token_name := 'USE_UP_ITEM_NAME';
1890 l_token_tbl(1).token_value :=
1891 p_revised_item_rec.use_up_item_name;
1892 l_token_tbl(2).token_name := 'ORGANIZATION_CODE';
1893 l_token_tbl(2).token_value :=
1894 p_revised_item_rec.organization_code;
1895
1896 Error_Handler.Add_Error_Token
1897 ( p_Message_Name =>'ENG_USE_UP_ITEM_ID_VID_INVALID'
1898 , p_Message_Text => NULL
1899 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1900 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1901 , p_Token_Tbl => l_Token_Tbl);
1902 END IF;
1903 END IF;
1904
1905 --
1906 -- If the user has give requestor name then convert requestor name
1907 -- to requestor ID.
1908 --
1909
1910 IF p_revised_item_rec.requestor IS NOT NULL AND
1911 p_revised_item_rec.requestor <> FND_API.G_MISS_CHAR
1912 THEN
1913 l_rev_item_unexp_rec.requestor_id :=
1914 Requestor
1915 ( p_requestor => p_revised_item_rec.requestor
1916 , p_organization_id => p_rev_item_unexp_rec.organization_id
1917 , x_err_text => l_err_text
1918 );
1919
1920 IF l_err_text IS NOT NULL
1921 THEN
1922 Error_Handler.Add_Error_Token
1923 ( p_Message_Name => NULL
1924 , p_Message_Text => l_err_text
1925 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1926 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1927 );
1928 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1929 ELSIF l_rev_item_unexp_rec.requestor_id IS NULL
1930 THEN
1931 l_token_tbl(1).token_name := 'REQUESTOR';
1932 l_token_tbl(1).token_value :=
1933 p_revised_item_rec.requestor;
1934 l_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
1935 l_token_tbl(2).token_value :=
1936 p_revised_item_rec.organization_code;
1937
1938 Error_Handler.Add_Error_Token
1939 ( p_Message_Name => 'ENG_RIT_REQUESTOR_INVALID'
1940 , p_Message_Text => NULL
1941 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1942 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1943 , p_token_tbl => l_token_tbl
1944 );
1945 l_return_status := FND_API.G_RET_STS_ERROR;
1946 END IF;
1947 END IF;
1948
1949
1950 /*******************************************************
1951 -- Followings are for ECO Routing and New Effectivities
1952 -- Added by MK on 08/24/2000
1953 ********************************************************/
1954 --
1955 -- If the user has give from_work_order or to_work_order,
1956 -- then convert to wip_entity_id
1957 --
1958 IF p_revised_item_rec.from_work_order IS NOT NULL AND
1959 p_revised_item_rec.from_work_order <> FND_API.G_MISS_CHAR
1960 THEN
1961 l_rev_item_unexp_rec.from_wip_entity_id :=
1962 Work_Order
1963 ( p_work_order => p_revised_item_rec.from_work_order
1964 , p_organization_id => p_rev_item_unexp_rec.organization_id
1965 , x_err_text => l_err_text
1966 );
1967
1968 IF l_err_text IS NOT NULL
1969 THEN
1970 Error_Handler.Add_Error_Token
1971 ( p_Message_Name => NULL
1972 , p_Message_Text => l_err_text
1973 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1974 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1975 );
1976 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1977 ELSIF l_rev_item_unexp_rec.from_wip_entity_id IS NULL
1978 THEN
1979 l_token_tbl(1).token_name := 'FROM_WORK_ORDER';
1980 l_token_tbl(1).token_value :=
1981 p_revised_item_rec.from_work_order ;
1982 l_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
1983 l_token_tbl(2).token_value :=
1984 p_revised_item_rec.revised_item_name ;
1985
1986 Error_Handler.Add_Error_Token
1987 ( p_Message_Name => 'ENG_RIT_FROM_WO_INVALID'
1988 , p_Message_Text => NULL
1989 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1990 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1991 , p_token_tbl => l_token_tbl
1992 );
1993 l_return_status := FND_API.G_RET_STS_ERROR;
1994 END IF;
1995 END IF ;
1996
1997 IF p_revised_item_rec.to_work_order IS NOT NULL AND
1998 p_revised_item_rec.to_work_order <> FND_API.G_MISS_CHAR
1999 THEN
2000 l_rev_item_unexp_rec.to_wip_entity_id :=
2001 Work_Order
2002 ( p_work_order => p_revised_item_rec.to_work_order
2003 , p_organization_id => p_rev_item_unexp_rec.organization_id
2004 , x_err_text => l_err_text
2005 );
2006
2007 IF l_err_text IS NOT NULL
2008 THEN
2009 Error_Handler.Add_Error_Token
2010 ( p_Message_Name => NULL
2011 , p_Message_Text => l_err_text
2012 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2013 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2014 );
2015 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2016 ELSIF l_rev_item_unexp_rec.from_wip_entity_id IS NULL
2017 THEN
2018 l_token_tbl(1).token_name := 'TO_WORK_ORDER';
2019 l_token_tbl(1).token_value :=
2020 p_revised_item_rec.to_work_order ;
2021 l_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
2022 l_token_tbl(2).token_value :=
2023 p_revised_item_rec.revised_item_name ;
2024
2025 Error_Handler.Add_Error_Token
2026 ( p_Message_Name => 'ENG_RIT_TO_WO_INVALID'
2027 , p_Message_Text => NULL
2028 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2029 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2030 , p_token_tbl => l_token_tbl
2031 );
2032 l_return_status := FND_API.G_RET_STS_ERROR;
2033 END IF;
2034 END IF ;
2035
2036 --
2037 -- If the user has give Copmletion_Location_Name,
2038 -- then convert to completion_location_id
2039 --
2040 IF p_revised_item_rec.completion_location_name IS NOT NULL AND
2041 p_revised_item_rec.completion_location_name <> FND_API.G_MISS_CHAR
2042 THEN
2043 l_rev_item_unexp_rec.completion_locator_id :=
2044 BOM_RTG_Val_To_Id.Completion_locator_id
2045 ( p_completion_location_name => p_revised_item_rec.completion_location_name
2046 , p_organization_id => p_rev_item_unexp_rec.organization_id
2047 , x_err_text => l_err_text
2048 );
2049 IF l_err_text IS NOT NULL
2050 THEN
2051 Error_Handler.Add_Error_Token
2052 ( p_Message_Name => NULL
2053 , p_Message_Text => l_err_text
2054 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2055 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2056 );
2057 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2058 ELSIF l_rev_item_unexp_rec.completion_locator_id IS NULL
2059 THEN
2060 l_token_tbl(1).token_name := 'COMPLETION_LOCATION_NAME';
2061 l_token_tbl(1).token_value :=
2062 p_revised_item_rec.completion_location_name ;
2063 l_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
2064 l_token_tbl(2).token_value :=
2065 p_revised_item_rec.revised_item_name ;
2066
2067 Error_Handler.Add_Error_Token
2068 ( p_Message_Name => 'ENG_RIT_LOCATION_NAME_INVALID'
2069 , p_Message_Text => NULL
2070 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2071 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2072 , p_token_tbl => l_token_tbl
2073 );
2074 l_return_status := FND_API.G_RET_STS_ERROR;
2075 END IF;
2076 END IF;
2077 -- Added by MK 08/24/2000
2078
2079 -- Added by MK on 02/15/2001
2080 IF ( l_rev_item_unexp_rec.bill_sequence_id IS NULL OR
2081 l_rev_item_unexp_rec.bill_sequence_id = FND_API.G_MISS_NUM ) AND
2082 (( p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE OR
2083 p_revised_item_rec.alternate_bom_code IS NOT NULL ) OR
2084 /* Added for Bug 2992001 */
2085 (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE))
2086 THEN
2087
2088 l_rev_item_unexp_rec.bill_sequence_id :=
2089 BOM_Val_To_Id.Bill_Sequence_Id
2090 ( p_assembly_item_id => l_rev_item_unexp_rec.revised_item_id
2091 , p_organization_id => l_rev_item_unexp_rec.organization_id
2092 , p_alternate_bom_code =>
2093 p_revised_item_rec.alternate_bom_code
2094 , x_err_text => l_err_text
2095 );
2096
2097 IF Bom_Globals.Get_Debug = 'Y' THEN
2098 Error_Handler.Write_Debug('Getting Bill Seq Id . . . : ' ||
2099 to_char(l_rev_item_unexp_rec.bill_sequence_id));
2100 END IF;
2101
2102 END IF;
2103
2104 IF ( l_rev_item_unexp_rec.routing_sequence_id IS NULL OR
2105 l_rev_item_unexp_rec.routing_sequence_id = FND_API.G_MISS_NUM ) AND
2106 ( p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE OR
2107 p_revised_item_rec.alternate_bom_code IS NOT NULL )
2108
2109 THEN
2110
2111 l_rev_item_unexp_rec.routing_sequence_id :=
2112 BOM_RTG_Val_To_Id.Routing_Sequence_id
2113 ( p_assembly_item_id => l_rev_item_unexp_rec.revised_item_id
2114 , p_organization_id => l_rev_item_unexp_rec.organization_id
2115 , p_alternate_routing_designator =>
2116 p_revised_item_rec.alternate_bom_code
2117 , x_err_text => l_err_text
2118 );
2119
2120
2121 IF Bom_Globals.Get_Debug = 'Y' THEN
2122 Error_Handler.Write_Debug('Getting Routing Seq Id . . . : ' ||
2123 to_char(l_rev_item_unexp_rec.routing_sequence_id));
2124 END IF;
2125
2126 END IF;
2127 ---11.5.10
2128 IF (p_revised_item_rec.parent_revised_item_name is not null) then
2129
2130 l_rev_item_unexp_rec.parent_revised_item_seq_id:=
2131 ParentRevSeqId
2132 ( parent_item_name => p_revised_item_rec.parent_revised_item_name
2133 , p_organization_id => l_rev_item_unexp_rec.organization_id
2134 , p_alternate_bom_code => p_revised_item_rec.parent_alternate_name
2135 , p_schedule_date => p_revised_item_rec.start_effective_date
2136 , p_change_id => l_rev_item_unexp_rec.change_id);
2137 end if;
2138
2139
2140
2141 IF (p_revised_item_rec.from_end_item_name is not null) then
2142
2143 l_rev_item_unexp_rec.from_end_item_id :=
2144 Revised_Item
2145 ( p_revised_item_num =>p_revised_item_rec.from_end_item_name,
2146 p_organization_id =>l_rev_item_unexp_rec.organization_id,
2147 x_err_text =>l_err_text );
2148
2149 IF (p_revised_item_rec.from_end_item_revision is not null) then
2150
2151 select revision_id into l_rev_item_unexp_rec.from_end_item_revision_id
2152 from mtl_item_revisions
2153 where inventory_item_id =l_rev_item_unexp_rec.from_end_item_id
2154 and organization_id =l_rev_item_unexp_rec.organization_id;
2155
2156 select bill_sequence_id into l_bill_seq_id from bom_bill_of_materials
2157 where
2158 ASSEMBLY_ITEM_ID = l_rev_item_unexp_rec.from_end_item_id
2159 and ORGANIZATION_ID = l_rev_item_unexp_rec.organization_id
2160 and ALTERNATE_BOM_DESIGNATOR = p_revised_item_rec.from_end_item_alternate;
2161
2162 /* not supported for 11.5.10
2163 select STRUCTURE_REVISION_ID into l_rev_item_unexp_rec.from_end_item_struct_rev_id
2164 from should be using minor revision table
2165 where BILL_SEQUENCE_ID =l_bill_seq_id
2166 and REVISION =p_revised_item_rec.from_end_item_revision
2167 and OBJECT_REVISION_ID = l_rev_item_unexp_rec.from_end_item_revision_id;
2168 */
2169
2170 end if;
2171
2172 end if;
2173 --11.5.10
2174
2175 -- 11.5.10E
2176 -- Querying to find if the change is ERP or PLM change
2177 -- 'From Revision' changes are valid only for PLM changes
2178 l_eco_type := Eng_Globals.Get_PLM_Or_ERP_Change
2179 (p_revised_item_rec.eco_name,
2180 l_rev_item_unexp_rec.organization_id
2181 );
2182
2183 IF (l_eco_type IS NULL)
2184 THEN
2185 l_eco_type := 'PLM';
2186 END IF;
2187
2188 IF (l_eco_type = 'PLM')
2189 THEN
2190 -- Changes to enable 'From Revision' for revised items.
2191 -- The from revision id is set from the revision label
2192 IF ( (p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE OR
2193 p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE) AND
2194 p_revised_item_rec.from_item_revision IS NOT NULL)
2195 THEN
2196 l_rev_item_unexp_rec.from_item_revision_id := From_Revision_Id
2197 ( p_assembly_item_id => l_rev_item_unexp_rec.revised_item_id
2198 , p_organization_id => l_rev_item_unexp_rec.organization_id
2199 , p_revision => p_revised_item_rec.from_item_revision
2200 , p_revision_date => SYSDATE
2201 , x_err_text => l_err_text
2202 );
2203 IF Bom_Globals.Get_Debug = 'Y'
2204 THEN
2205 Error_Handler.Write_Debug('Getting From Revision Id . . . : ' ||
2206 to_char(l_rev_item_unexp_rec.from_item_revision_id));
2207 END IF;
2208
2209 IF ( l_rev_item_unexp_rec.from_item_revision_id IS NULL OR
2210 l_rev_item_unexp_rec.from_item_revision_id = FND_API.G_MISS_NUM)
2211 THEN
2212 l_token_tbl(1).token_name := 'FROM_REVISION';
2213 l_token_tbl(1).token_value := l_rev_item_unexp_rec.from_item_revision_id ;
2214 l_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
2215 l_token_tbl(2).token_value := p_revised_item_rec.revised_item_name ;
2216
2217 Error_Handler.Add_Error_Token
2218 ( p_Message_Name => 'ENG_INVALID_FROM_REVISION'
2219 , p_Message_Text => NULL
2220 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2221 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2222 , p_token_tbl => l_token_tbl
2223 );
2224 l_return_status := FND_API.G_RET_STS_ERROR;
2225 END IF;
2226 END IF;
2227
2228 -- The new revision reason, if provided is converted to the corresponding
2229 -- code.
2230 IF((p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE OR
2231 p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE) AND
2232 p_revised_item_rec.New_Revised_Item_Revision IS NOT NULL AND
2233 p_revised_item_rec.new_revision_reason IS NOT NULL)
2234 THEN
2235 l_rev_item_unexp_rec.new_revision_reason_code :=
2236 New_Revision_Reason_Code
2237 ( p_reason => p_revised_item_rec.new_revision_reason
2238 , x_err_text => l_err_text
2239 );
2240
2241 IF ( l_rev_item_unexp_rec.new_revision_reason_code IS NULL OR
2242 l_rev_item_unexp_rec.new_revision_reason_code = FND_API.G_MISS_CHAR)
2243 THEN
2244 l_token_tbl(1).token_name := 'NEW_REVISION_REASON';
2245 l_token_tbl(1).token_value := p_revised_item_rec.new_revision_reason ;
2246 l_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
2247 l_token_tbl(2).token_value := p_revised_item_rec.revised_item_name ;
2248
2249 Error_Handler.Add_Error_Token
2250 ( p_Message_Name => 'ENG_INVALID_REVISION_REASON'
2251 , p_Message_Text => NULL
2252 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2253 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2254 , p_token_tbl => l_token_tbl
2255 );
2256 l_return_status := FND_API.G_RET_STS_ERROR;
2257 END IF;
2258
2259 IF Bom_Globals.Get_Debug = 'Y'
2260 THEN
2261 Error_Handler.Write_Debug('Getting New Revision reason Code . . . : ' ||
2262 l_rev_item_unexp_rec.new_revision_reason_code); -- bug 4309885: removed to_char(varchar) as it is not supported in 8i
2263 END IF;
2264 END IF;
2265 END IF; -- End of (l_eco_type = 'PLM')
2266
2267 --
2268 -- Convert structure type name to structure type id
2269 -- if it is given
2270 --
2271 IF p_revised_item_rec.structure_type_name IS NOT NULL AND
2272 p_revised_item_rec.structure_type_name <> FND_API.G_MISS_CHAR
2273 THEN
2274 l_rev_item_unexp_rec.structure_type_id :=
2275 Get_Structure_Type_Id
2276 ( p_structure_type_name => p_revised_item_rec.structure_type_name
2277 , x_err_text => l_err_text
2278 );
2279 IF l_err_text IS NOT NULL
2280 THEN
2281 Error_Handler.Add_Error_Token
2282 ( p_Message_Name => NULL
2283 , p_Message_Text => l_err_text
2284 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2285 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2286 );
2287 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2288 ELSIF l_rev_item_unexp_rec.structure_type_id IS NULL
2289 THEN
2290 l_token_tbl(1).token_name := 'STRUCTURE_TYPE_NAME';
2291 l_token_tbl(1).token_value :=
2292 p_revised_item_rec.structure_type_name ;
2293 l_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
2294 l_token_tbl(2).token_value :=
2295 p_revised_item_rec.revised_item_name ;
2296
2297 Error_Handler.Add_Error_Token
2298 ( p_Message_Name => 'ENG_STRUC_TYPE_NAME_INVALID'
2299 , p_Message_Text => NULL
2300 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2301 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2302 , p_token_tbl => l_token_tbl
2303 );
2304 l_return_status := FND_API.G_RET_STS_ERROR;
2305 END IF;
2306 END IF;
2307
2308 x_return_status := l_return_status;
2309 x_rev_item_unexp_rec := l_rev_item_unexp_rec;
2310 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2311
2312 END Revised_Item_VID;
2313
2314
2315 PROCEDURE ECO_Header_VID
2316 ( x_Return_Status OUT NOCOPY VARCHAR2
2317 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2318 , p_ECO_Rec IN Eng_Eco_Pub.ECO_Rec_Type
2319 , p_ECO_Unexp_Rec IN Eng_Eco_Pub.Eco_Unexposed_Rec_Type
2320 , x_ECO_Unexp_Rec IN OUT NOCOPY Eng_Eco_Pub.Eco_Unexposed_Rec_Type
2321 )
2322 IS
2323 l_err_text VARCHAR2(2000) := NULL;
2324 l_return_value NUMBER;
2325 l_Return_Status VARCHAR2(1);
2326 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2327 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2328 l_ECO_Unexp_Rec Eng_Eco_Pub.Eco_Unexposed_Rec_Type;
2329
2330
2331 l_change_mgmt_type_code VARCHAR2(30) ;
2332 l_source_type_code VARCHAR2(30) ;
2333 l_status_type NUMBER;
2334 l_approval_status_type NUMBER;
2335 l_status_code NUMBER;
2336 l_plm_or_erp_change eng_engineering_changes.plm_or_erp_change%type;
2337 BEGIN
2338 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
2339 l_ECO_Unexp_Rec := p_ECO_Unexp_Rec;
2340
2341 l_token_tbl(1).token_name := 'ECO_Name';
2342 l_token_tbl(1).token_value := p_ECO_rec.ECO_name;
2343
2344 -- Initializing Plm or Erp Change
2345 IF p_eco_rec.transaction_type = 'CREATE'
2346 THEN
2347 l_plm_or_erp_change := p_eco_rec.plm_or_erp_change;
2348 ELSE
2349 l_plm_or_erp_change := Eng_Globals.Get_PLM_Or_ERP_Change(
2350 p_ECO_Rec.eco_name
2351 , p_ECO_Unexp_Rec.organization_id);
2352 END IF;
2353
2354 --
2355 -- Convert Approval_List_Name to Approval_List_Id
2356 --
2357
2358 IF p_ECO_rec.approval_list_name IS NOT NULL AND
2359 p_ECO_rec.approval_list_name <> FND_API.G_MISS_CHAR
2360 THEN
2361 l_Return_Value :=
2362 Approval_List( p_approval_list => p_ECO_rec.approval_list_name
2363 , x_err_text => l_err_text
2364 );
2365
2366 IF l_Return_Value IS NULL
2367 THEN
2368 l_token_tbl(2).token_name := 'ECO_NAME';
2369 l_token_tbl(2).token_value := p_ECO_rec.ECO_name;
2370 l_token_tbl(1).token_name := 'APPROVAL_LIST_NAME';
2371 l_token_tbl(1).token_value := p_ECO_rec.Approval_List_Name;
2372
2373 Error_Handler.Add_Error_Token
2374 ( p_Message_Name => 'ENG_APPROVAL_LIST_INVALID'
2375 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2376 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2377 , p_Token_Tbl => l_Token_Tbl
2378 );
2379 l_Return_Status := FND_API.G_RET_STS_ERROR;
2380
2381 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2382 THEN
2383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2384
2385 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2386 THEN
2387 Error_Handler.Add_Error_Token
2388 ( p_Message_Text => l_err_text
2389 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2390 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2391 );
2392 END IF;
2393
2394 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2395 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2396 RETURN;
2397
2398 ELSE
2399 l_ECO_Unexp_Rec.approval_list_id := l_Return_Value;
2400 END IF;
2401 ELSE
2402 l_ECO_Unexp_Rec.approval_list_id := NULL;
2403 END IF;
2404 --
2405 -- Convert ECO_Department to Responsible_Org_Id
2406 --
2407
2408 IF p_ECO_rec.ECO_Department_Name IS NOT NULL AND
2409 p_ECO_rec.ECO_Department_name <> FND_API.G_MISS_CHAR
2410 THEN
2411 l_Return_Value :=
2412 Responsible_Org
2413 ( p_responsible_org => p_ECO_rec.ECO_Department_name
2414 , p_current_org => l_ECO_Unexp_Rec.organization_id
2415 , x_err_text => l_err_text
2416 );
2417
2418 IF l_Return_Value IS NULL
2419 THEN
2420 l_token_tbl(2).token_name := 'ECO_NAME';
2421 l_token_tbl(2).token_value := p_ECO_rec.ECO_name;
2422 l_token_tbl(1).token_name := 'ECO_DEPARTMENT';
2423 l_token_tbl(1).token_value := p_ECO_rec.ECO_Department_name;
2424
2425 Error_Handler.Add_Error_Token
2426 ( p_Message_Name => 'ENG_RESP_ORG_INVALID'
2427 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2428 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2429 , p_Token_Tbl => l_Token_Tbl
2430 );
2431 l_Return_Status := FND_API.G_RET_STS_ERROR;
2432
2433 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2434 THEN
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436
2437 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2438 THEN
2439 Error_Handler.Add_Error_Token
2440 ( p_Message_Text => l_err_text
2441 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2442 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2443 );
2444 END IF;
2445
2446 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2447 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2448 RETURN;
2449
2450 ELSE
2451 l_ECO_Unexp_Rec.responsible_org_id := l_Return_Value;
2452 END IF;
2453 ELSE
2454 l_ECO_Unexp_Rec.responsible_org_id := NULL;
2455 END IF;
2456
2457 -- Added for Bug 4402842
2458 IF p_ECO_rec.employee_number IS NOT NULL AND
2459 p_ECO_rec.employee_number <> FND_API.G_MISS_CHAR AND
2460 l_plm_or_erp_change <> 'PLM'
2461 THEN
2462 l_Return_Value :=
2463 Employee( p_employee_number => p_ECO_rec.employee_number
2464 , x_err_text => l_err_text);
2465 IF l_Return_Value IS NULL
2466 THEN
2467 l_token_tbl(2).token_name := 'ECO_NAME';
2468 l_token_tbl(2).token_value := p_ECO_rec.ECO_name;
2469 l_token_tbl(1).token_name := 'EMPLOYEE_NUMBER';
2470 l_token_tbl(1).token_value := p_ECO_rec.employee_number;
2471
2472 Error_Handler.Add_Error_Token(
2473 p_Message_Name => 'ENG_EMP_NUMBER_INVALID'
2474 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2475 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2476 , p_Token_Tbl => l_Token_Tbl );
2477 l_Return_Status := FND_API.G_RET_STS_ERROR;
2478
2479 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2480 THEN
2481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2482
2483 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2484 THEN
2485 Error_Handler.Add_Error_Token(
2486 p_Message_Text => l_err_text
2487 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2488 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl );
2489 END IF;
2490
2491 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2492 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2493 RETURN;
2494 ELSE
2495 l_ECO_Unexp_Rec.Requestor_id := l_Return_Value;
2496 END IF;
2497 --* End of Bug 4402842
2498
2499 --
2500 -- Convert Requestor to Requestor_Id
2501 --
2502 ELSIF p_ECO_rec.Requestor IS NOT NULL AND
2503 p_ECO_rec.Requestor <> FND_API.G_MISS_CHAR
2504 THEN
2505 l_Return_Value :=
2506 Requestor
2507 ( p_Requestor => p_ECO_rec.Requestor
2508 , p_organization_id => p_ECO_Unexp_rec.organization_id
2509 , x_err_text => l_err_text
2510 );
2511
2512 IF l_Return_Value IS NULL
2513 THEN
2514 l_token_tbl(2).token_name := 'ECO_NAME';
2515 l_token_tbl(2).token_value := p_ECO_rec.ECO_name;
2516 l_token_tbl(1).token_name := 'REQUESTOR';
2517 l_token_tbl(1).token_value := p_ECO_rec.Requestor;
2518
2519 Error_Handler.Add_Error_Token
2520 ( p_Message_Name => 'ENG_REQUESTOR_INVALID'
2521 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2522 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2523 , p_Token_Tbl => l_Token_Tbl
2524 );
2525 l_Return_Status := FND_API.G_RET_STS_ERROR;
2526
2527 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2528 THEN
2529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2530
2531 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2532 THEN
2533 Error_Handler.Add_Error_Token
2534 ( p_Message_Text => l_err_text
2535 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2536 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2537 );
2538 END IF;
2539
2540 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2541 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2542 RETURN;
2543
2544 ELSE
2545 l_ECO_Unexp_Rec.Requestor_id := l_Return_Value;
2546 END IF;
2547 ELSE
2548 l_ECO_Unexp_Rec.Requestor_Id := NULL;
2549 END IF;
2550
2551 IF p_ECO_rec.Project_Name IS NOT NULL AND
2552 p_ECO_rec.Project_Name <> FND_API.G_MISS_CHAR
2553 THEN
2554 l_Return_Value :=
2555 Project
2556 ( p_project_name => p_ECO_rec.project_name
2557 , x_err_text => l_err_text
2558 );
2559
2560 IF l_Return_Value IS NULL
2561 THEN
2562 l_token_tbl(2).token_name := 'ECO_NAME';
2563 l_token_tbl(2).token_value := p_ECO_rec.ECO_name;
2564 l_token_tbl(1).token_name := 'PROJECT_NUMBER';
2565 l_token_tbl(1).token_value := p_ECO_rec.project_name;
2566
2567 Error_Handler.Add_Error_Token
2568 ( p_Message_Name => 'ENG_PROJECT_INVALID'
2569 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2570 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2571 , p_Token_Tbl => l_Token_Tbl
2572 );
2573 l_Return_Status := FND_API.G_RET_STS_ERROR;
2574
2575 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2576 THEN
2577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2578
2579 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2580 THEN
2581 Error_Handler.Add_Error_Token
2582 ( p_Message_Text => l_err_text
2583 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2584 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2585 );
2586 END IF;
2587
2588 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2589 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2590 RETURN;
2591
2592 ELSE
2593 l_ECO_Unexp_Rec.project_id := l_Return_Value;
2594 END IF;
2595 ELSE
2596 l_ECO_Unexp_Rec.project_id := NULL;
2597 END IF;
2598
2599 IF p_ECO_rec.Task_Number IS NOT NULL AND
2600 p_ECO_rec.Task_Number <> FND_API.G_MISS_CHAR
2601 THEN
2602 l_Return_Value :=
2603 Task
2604 ( p_task_number => p_ECO_rec.task_number
2605 , p_project_id => l_ECO_Unexp_Rec.project_id
2606 , x_err_text => l_err_text
2607 );
2608
2609 IF l_Return_Value IS NULL
2610 THEN
2611 l_token_tbl(2).token_name := 'ECO_NAME';
2612 l_token_tbl(2).token_value := p_ECO_rec.ECO_name;
2613 l_token_tbl(1).token_name := 'TASK_NUMBER';
2614 l_token_tbl(1).token_value := p_ECO_rec.task_number;
2615
2616 Error_Handler.Add_Error_Token
2617 ( p_Message_Name => 'ENG_TASK_INVALID'
2618 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2619 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2620 , p_Token_Tbl => l_Token_Tbl
2621 );
2622 l_Return_Status := FND_API.G_RET_STS_ERROR;
2623
2624 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2625 THEN
2626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2627
2628 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2629 THEN
2630 Error_Handler.Add_Error_Token
2631 ( p_Message_Text => l_err_text
2632 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2633 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2634 );
2635 END IF;
2636
2637 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2638 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2639 RETURN;
2640
2641 ELSE
2642 l_ECO_Unexp_Rec.task_id := l_Return_Value;
2643 END IF;
2644 ELSE
2645 l_ECO_Unexp_Rec.task_id := NULL;
2646 END IF;
2647
2648 --
2649 -- Assignee
2650 -- Convert Assignee to Assignee_Id (Party Id of Group or Person)
2651 --
2652 IF p_ECO_rec.Assignee IS NOT NULL AND
2653 p_ECO_rec.Assignee <> FND_API.G_MISS_CHAR
2654 THEN
2655 l_Return_Value :=
2656 Assignee
2657 ( p_assignee => p_ECO_rec.Assignee
2658 --, p_assignee_company_name => p_ECO_Unexp_rec.Assignee_Company_Name
2659 --, p_organization_id => p_ECO_Unexp_rec.organization_id
2660 , x_err_text => l_err_text
2661 );
2662 IF l_Return_Value IS NULL
2663 THEN
2664 -- Bug No :3463516
2665 l_token_tbl(2).token_name := 'ECO_NAME';
2666 l_token_tbl(2).token_value := p_ECO_rec.ECO_name;
2667 l_token_tbl(1).token_name := 'ASSIGNEE';
2668 l_token_tbl(1).token_value := p_ECO_rec.Assignee ;
2669 --l_token_tbl(3).token_name := 'COMPANY_NAME';
2670 --l_token_tbl(3).token_value := p_ECO_rec.Assignee_Company_Name ;
2671
2672 Error_Handler.Add_Error_Token
2673 ( p_Message_Name => 'ENG_ASSIGNEE_INVALID'
2674 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2675 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2676 , p_Token_Tbl => l_Token_Tbl
2677 );
2678
2679 l_token_tbl.DELETE;
2680 l_Return_Status := FND_API.G_RET_STS_ERROR;
2681
2682 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2683 THEN
2684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2685
2686 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2687 THEN
2688 Error_Handler.Add_Error_Token
2689 ( p_Message_Text => l_err_text
2690 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2691 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2692 );
2693 END IF;
2694
2695 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2696 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2697 RETURN;
2698
2699 ELSE
2700 l_ECO_Unexp_Rec.Assignee_id := l_Return_Value;
2701 END IF;
2702
2703 --
2704 -- User should be able to null out assignee
2705 -- ELSE
2706 -- l_ECO_Unexp_Rec.Assignee_id := NULL;
2707
2708 END IF;
2709
2710
2711 --
2712 -- Change_Management_Type
2713 -- Get the Change Management Type code for the corresponding
2714 -- change management type
2715 --
2716 IF p_ECO_rec.Change_Management_Type IS NOT NULL AND
2717 p_ECO_rec.Change_Management_Type <> FND_API.G_MISS_CHAR
2718 THEN
2719
2720 l_change_mgmt_type_code :=
2721 Change_Management_Type( p_change_management_type => p_ECO_rec.Change_Management_Type
2722 , x_err_text => l_err_text
2723 );
2724 IF l_change_mgmt_type_code IS NULL
2725 THEN
2726 l_token_tbl(1).token_name := 'CHANGE_MANAGEMENT_TYPE';
2727 l_token_tbl(1).token_value := p_ECO_rec.Change_Management_Type;
2728
2729 Error_Handler.Add_Error_Token
2730 ( p_message_name => 'ENG_CHG_MGMT_TYPE_INVALID'
2731 , p_mesg_token_tbl => l_mesg_token_tbl
2732 , p_token_tbl => g_token_tbl
2733 , x_mesg_token_tbl => l_mesg_token_tbl
2734 );
2735
2736 l_token_tbl.DELETE;
2737
2738 l_return_status := FND_API.G_RET_STS_ERROR;
2739
2740 ELSIF l_change_mgmt_type_code = FND_API.G_MISS_CHAR
2741 THEN
2742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2743
2744 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2745 THEN
2746 Error_Handler.Add_Error_Token
2747 ( p_Message_Text => l_err_text
2748 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2749 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2750 );
2751 END IF;
2752
2753 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2754 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2755 RETURN;
2756
2757 ELSE
2758 l_ECO_Unexp_Rec.Change_Mgmt_Type_Code := l_Change_Mgmt_Type_Code ;
2759 END IF;
2760
2761 ELSE
2762 l_ECO_Unexp_Rec.Change_Mgmt_Type_Code := NULL;
2763 END IF;
2764
2765
2766 IF p_ECO_rec.Status_Name IS NOT NULL AND
2767 p_ECO_rec.Status_Name <> FND_API.G_MISS_CHAR
2768 THEN
2769
2770
2771
2772 -- l_status_type :=
2773 Status_Type(p_status_name => p_ECO_rec.Status_Name
2774 ,x_status_code => l_status_code
2775 ,x_status_type => l_status_type
2776 , x_return_status =>l_return_status
2777 ,p_change_order_type_id =>l_ECO_Unexp_Rec.change_order_type_id
2778 ,p_plm_or_erp => p_ECO_rec.plm_or_erp_change );
2779 IF l_status_type IS NULL or l_status_code IS NULL
2780 THEN
2781 l_token_tbl(1).token_name := 'STATUS_TYPE';
2782 l_token_tbl(1).token_value := p_ECO_rec.Status_Name;
2783
2784 Error_Handler.Add_Error_Token
2785 ( p_message_name => 'ENG_STATUS_TYPE_INVALID'
2786 , p_mesg_token_tbl => l_mesg_token_tbl
2787 , p_token_tbl => g_token_tbl
2788 , x_mesg_token_tbl => l_mesg_token_tbl
2789 );
2790
2791 l_token_tbl.DELETE;
2792 l_return_status := FND_API.G_RET_STS_ERROR;
2793 ELSE
2794 l_ECO_Unexp_Rec.Status_Type := l_status_type ;
2795 l_ECO_Unexp_Rec.Status_Code := l_status_code ;
2796 END IF;
2797
2798 ELSE
2799 l_ECO_Unexp_Rec.Status_Type := NULL;
2800 END IF;
2801
2802
2803 IF p_ECO_rec.Approval_Status_Name IS NOT NULL AND
2804 p_ECO_rec.Approval_Status_Name <> FND_API.G_MISS_CHAR
2805 THEN
2806
2807 l_approval_status_type :=
2808 Approval_Status_Type(p_approval_status_name => p_ECO_rec.Approval_Status_Name);
2809
2810 IF l_approval_status_type IS NULL
2811 THEN
2812 l_token_tbl(1).token_name := 'APPROVAL_STATUS_TYPE';
2813 l_token_tbl(1).token_value := p_ECO_rec.Approval_Status_Name;
2814
2815 Error_Handler.Add_Error_Token
2816 ( p_message_name => 'ENG_APPR_STATUS_TYPE_INVALID'
2817 , p_mesg_token_tbl => l_mesg_token_tbl
2818 , p_token_tbl => g_token_tbl
2819 , x_mesg_token_tbl => l_mesg_token_tbl
2820 );
2821
2822 l_token_tbl.DELETE;
2823 l_return_status := FND_API.G_RET_STS_ERROR;
2824
2825 ELSE
2826 l_ECO_Unexp_Rec.Approval_Status_Type := l_approval_status_type ;
2827 END IF;
2828
2829 ELSE
2830 l_ECO_Unexp_Rec.Approval_Status_Type := NULL;
2831 END IF;
2832
2833 --
2834 -- Source_Type
2835 -- Get the Source Type code for the corresponding
2836 -- source type
2837 --
2838 IF p_ECO_rec.Source_Type IS NOT NULL AND
2839 p_ECO_rec.Source_Type <> FND_API.G_MISS_CHAR
2840 THEN
2841
2842 l_source_type_code :=
2843 Source_Type( p_source_type => p_ECO_rec.Source_Type
2844 , x_err_text => l_err_text
2845 );
2846 IF l_source_type_code IS NULL
2847 THEN
2848 l_token_tbl(1).token_name := 'SOURCE_TYPE';
2849 l_token_tbl(1).token_value := p_ECO_rec.Source_Type ;
2850
2851 Error_Handler.Add_Error_Token
2852 ( p_message_name => 'ENG_CHG_SRC_TYPE_INVALID'
2853 , p_mesg_token_tbl => l_mesg_token_tbl
2854 , p_token_tbl => g_token_tbl
2855 , x_mesg_token_tbl => l_mesg_token_tbl
2856 );
2857
2858 l_token_tbl.DELETE;
2859
2860 l_return_status := FND_API.G_RET_STS_ERROR;
2861
2862 ELSIF l_source_type_code = FND_API.G_MISS_CHAR
2863 THEN
2864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2865
2866 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2867 THEN
2868 Error_Handler.Add_Error_Token
2869 ( p_Message_Text => l_err_text
2870 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2871 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2872 );
2873 END IF;
2874
2875 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2876 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2877 RETURN;
2878
2879 ELSE
2880 l_ECO_Unexp_Rec.Source_Type_Code := l_source_type_code ;
2881 END IF;
2882
2883 ELSE
2884 l_ECO_Unexp_Rec.Source_Type_Code := NULL;
2885 END IF;
2886
2887
2888 --
2889 -- Source_Name
2890 -- Get the Source Id for the corresponding
2891 -- source name and source type code
2892 IF l_ECO_Unexp_Rec.Source_Type_Code IS NOT NULL AND
2893 l_ECO_Unexp_Rec.Source_Type_Code <> FND_API.G_MISS_CHAR AND
2894 p_ECO_rec.Source_Name IS NOT NULL AND
2895 p_ECO_rec.Source_Name <> FND_API.G_MISS_CHAR
2896 THEN
2897
2898 l_Return_Value :=
2899 Source_Name( p_source_name => p_ECO_rec.Source_Name
2900 , p_source_type_code => l_ECO_Unexp_Rec.Source_Type_Code
2901 , x_err_text => l_err_text
2902 );
2903
2904 IF l_Return_Value IS NULL
2905 THEN
2906 l_token_tbl(1).token_name := 'SOURCE_TYPE';
2907 l_token_tbl(1).token_value := p_ECO_rec.Source_Type ;
2908 l_token_tbl(2).token_name := 'SOURCE_NAME';
2909 l_token_tbl(2).token_value := p_ECO_rec.Source_Name ;
2910
2911 Error_Handler.Add_Error_Token
2912 ( p_message_name => 'ENG_CHG_SRC_NAME_INVALID'
2913 , p_mesg_token_tbl => l_mesg_token_tbl
2914 , p_token_tbl => g_token_tbl
2915 , x_mesg_token_tbl => l_mesg_token_tbl
2916 );
2917
2918 l_token_tbl.DELETE;
2919
2920 l_return_status := FND_API.G_RET_STS_ERROR;
2921
2922 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2923 THEN
2924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2925
2926 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2927 THEN
2928 Error_Handler.Add_Error_Token
2929 ( p_Message_Text => l_err_text
2930 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2931 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2932 );
2933 END IF;
2934
2935 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2936 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2937 RETURN;
2938
2939 ELSE
2940 l_ECO_Unexp_Rec.Source_Id := l_Return_Value ;
2941 END IF;
2942
2943 ELSE
2944 l_ECO_Unexp_Rec.Source_Id := NULL;
2945 END IF;
2946
2947 -- Start Bug 4967902
2948 IF p_ECO_rec.Organization_Hierarchy IS NOT NULL AND
2949 p_ECO_rec.Organization_Hierarchy <> FND_API.G_MISS_CHAR
2950 THEN
2951 l_Return_Value :=
2952 Hierarchy
2953 ( p_organization_hierarchy => p_ECO_rec.Organization_Hierarchy
2954 , x_err_text => l_err_text
2955 );
2956 IF l_Return_Value IS NULL
2957 THEN
2958 l_token_tbl(2).token_name := 'ECO_NAME';
2959 l_token_tbl(2).token_value := p_ECO_rec.ECO_name;
2960 l_token_tbl(1).token_name := 'ORGANIZATION_HIERARCHY';
2961 l_token_tbl(1).token_value := p_ECO_rec.Organization_Hierarchy;
2962
2963 Error_Handler.Add_Error_Token
2964 ( p_Message_Name => 'ENG_HIERARCHY_INVALID'
2965 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2966 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2967 , p_Token_Tbl => l_Token_Tbl
2968 );
2969 l_Return_Status := FND_API.G_RET_STS_ERROR;
2970
2971 ELSIF l_Return_Value = FND_API.G_MISS_NUM
2972 THEN
2973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2974
2975 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2976 THEN
2977 Error_Handler.Add_Error_Token
2978 ( p_Message_Text => l_err_text
2979 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2980 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2981 );
2982 END IF;
2983
2984 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2985 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2986 RETURN;
2987
2988 ELSE
2989 l_ECO_Unexp_Rec.hierarchy_id := l_Return_Value;
2990 END IF;
2991 ELSE
2992 l_ECO_Unexp_Rec.hierarchy_id := NULL;
2993 END IF;
2994 -- End bug 4967902
2995
2996 x_return_status := l_return_status;
2997 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2998 x_ECO_Unexp_Rec := l_ECO_Unexp_Rec;
2999
3000 END ECO_Header_VID;
3001
3002 /*****************************************************************************
3003 * Procedure : ECO_Header_UUI_To_UI
3004 * Parameters IN : ECO Header exposed columns record
3005 * ECO Header unexposed columns record
3006 * Parameters OUT: ECO Header unexposed columns record after the conversion
3007 * Mesg_Token_Tbl
3008 * Return_Status
3009 * Purpose : This procedure will perform value to id conversion for all
3010 * the eco header columns that form the unique key for this
3011 * entity.
3012 ******************************************************************************/
3013 PROCEDURE ECO_Header_UUI_To_UI
3014 ( p_eco_rec IN Eng_Eco_Pub.Eco_Rec_Type
3015 , p_eco_unexp_rec IN Eng_Eco_Pub.Eco_Unexposed_Rec_Type
3016 , x_eco_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Eco_Unexposed_Rec_Type
3017 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3018 , x_Return_Status OUT NOCOPY VARCHAR2
3019 )
3020 IS
3021
3022 l_dummy VARCHAR2(30); -- Bug 3591992
3023
3024 BEGIN
3025 x_eco_unexp_rec := p_eco_unexp_rec;
3026
3027 --
3028 -- Get the change_id
3029 -- Added for Bug 3591992
3030 x_eco_unexp_rec.change_id := Get_Change_Id(p_ECO_rec.eco_name, p_eco_unexp_rec.organization_id, l_dummy);
3031
3032 END ECO_Header_UUI_To_UI;
3033
3034 /*****************************************************************************
3035 * Procedure : ECO_Revision_UUI_To_UI
3036 * Parameters IN : ECO Revision exposed columns record
3037 * ECO Revision unexposed columns record
3038 * Parameters OUT: ECO revision unexposed columns record after the conversion
3039 * Mesg_Token_Tbl
3040 * Return_Status
3041 * Purpose : This procedure will perform value to id conversion for all
3042 * the ECO revision columns that form the unique key for this
3043 * entity.
3044 ******************************************************************************/
3045 PROCEDURE ECO_Revision_UUI_To_UI
3046 ( p_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
3047 , p_eco_rev_unexp_rec IN Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
3048 , x_eco_rev_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
3049 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3050 , x_Return_Status OUT NOCOPY VARCHAR2
3051 )
3052 IS
3053 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3054 l_dummy VARCHAR2(30);
3055
3056 BEGIN
3057
3058 x_return_status := FND_API.G_RET_STS_SUCCESS;
3059 x_eco_rev_unexp_rec := p_eco_rev_unexp_rec;
3060
3061 IF p_eco_revision_rec.revision IS NULL OR
3062 p_eco_revision_rec.revision = FND_API.G_MISS_CHAR
3063 THEN
3064 Error_Handler.Add_Error_Token
3065 ( p_Message_Name => 'ENG_REVISION_KEYCOL_NULL'
3066 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3067 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3068 );
3069
3070 x_return_status := FND_API.G_RET_STS_ERROR;
3071 x_mesg_token_tbl := l_mesg_token_tbl;
3072
3073 END IF;
3074
3075 x_eco_rev_unexp_rec.change_id := Get_Change_Id(p_eco_revision_rec.eco_name, p_eco_rev_unexp_rec.organization_id, l_dummy);
3076
3077 END ECO_Revision_UUI_To_UI;
3078
3079 /*****************************************************************************
3080 * Procedure : Revised_Item_UUI_To_UI
3081 * Parameters IN : Revised Item exposed columns record
3082 * Revised Item unexposed columns record
3083 * Parameters OUT: Revised Item unexposed columns record after the conversion
3084 * Mesg_Token_Tbl
3085 * Return_Status
3086 * Purpose : This procedure will perform value to id conversion for all
3087 * the revised item columns that form the unique key for this
3088 * entity.
3089 ******************************************************************************/
3090 PROCEDURE Revised_Item_UUI_To_UI
3091 ( p_revised_item_rec IN Eng_Eco_Pub.Revised_Item_Rec_Type
3092 , p_rev_item_unexp_rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
3093 , x_rev_item_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
3094 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3095 , x_Return_Status OUT NOCOPY VARCHAR2
3096 )
3097 IS
3098 l_err_text VARCHAR2(2000);
3099 l_rev_item_unexp_rec Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type;
3100 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3101 l_return_status VARCHAR2(1);
3102 l_dummy VARCHAR2(30);
3103 BEGIN
3104
3105 l_rev_item_unexp_rec := p_rev_item_unexp_rec;
3106 l_return_status := FND_API.G_RET_STS_SUCCESS;
3107
3108 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3109 g_Token_Tbl(1).Token_Value := p_revised_item_rec.revised_item_name;
3110
3111 /***********************************************************
3112 --
3113 -- Verify that the unique key columns are not null or missing
3114 --
3115 ************************************************************/
3116 IF p_revised_item_rec.revised_item_name IS NULL OR
3117 p_revised_item_rec.revised_item_name = FND_API.G_MISS_CHAR
3118 THEN
3119 Error_Handler.Add_Error_Token
3120 ( p_Message_Name => 'ENG_RITEM_NAME_KEYCOL_NULL'
3121 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3122 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3123 );
3124 l_Return_Status := FND_API.G_RET_STS_ERROR;
3125 END IF;
3126
3127 IF p_revised_item_rec.start_effective_date IS NULL OR
3128 p_revised_item_rec.start_Effective_date = FND_API.G_MISS_DATE
3129 THEN
3130 Error_Handler.Add_Error_Token
3131 ( p_Message_Name => 'ENG_RITEM_DATE_KEYCOL_NULL'
3132 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3133 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3134 );
3135 l_Return_Status := FND_API.G_RET_STS_ERROR;
3136 END IF;
3137
3138 --
3139 -- If Key columns are NULL then return
3140 --
3141 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3142 THEN
3143 x_Return_Status := l_Return_Status;
3144 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3145 RETURN;
3146 END IF;
3147
3148 --
3149 -- User Unique Key for Revised Item is:
3150 -- ECO Name, Revised Item ID, Start_Effective Date, Item_Revision, Org
3151 -- Org Code -> ID conversion will happen before this step
3152 -- Therefore converting revised item name to ID
3153 --
3154
3155 l_rev_item_unexp_rec.revised_item_id :=
3156 Revised_Item( p_revised_item_num =>
3157 p_revised_item_rec.revised_item_name
3158 , p_organization_id =>
3159 l_rev_item_unexp_rec.organization_id
3160 , x_err_text => l_err_text
3161 );
3162
3163 IF l_rev_item_unexp_rec.revised_item_id IS NULL
3164 THEN
3165 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3166 g_token_tbl(2).token_value :=
3167 p_revised_item_rec.organization_code;
3168 Error_Handler.Add_Error_Token
3169 ( p_Message_Name => 'ENG_REVISED_ITEM_INVALID'
3170 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3171 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3172 , p_Token_Tbl => g_Token_Tbl
3173 );
3174 l_Return_Status := FND_API.G_RET_STS_ERROR;
3175 END IF;
3176
3177 l_rev_item_unexp_rec.change_id :=
3178 Get_Change_Id(p_revised_item_rec.eco_name,
3179 l_rev_item_unexp_rec.organization_id,
3180 l_dummy);
3181 IF l_rev_item_unexp_rec.change_id IS NULL
3182 THEN
3183 g_token_tbl(2).token_name := 'CHANGE_NOTICE';
3184 g_token_tbl(2).token_value :=
3185 p_revised_item_rec.eco_name;
3186 Error_Handler.Add_Error_Token
3187 ( p_Message_Name => 'ENG_REVISED_ITEM_INVALID'
3188 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3189 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3190 , p_Token_Tbl => g_Token_Tbl
3191 );
3192 l_Return_Status := FND_API.G_RET_STS_ERROR;
3193 END IF;
3194
3195 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3196 x_rev_item_unexp_rec := l_rev_item_unexp_rec;
3197 x_Return_Status := l_Return_Status;
3198
3199 END Revised_Item_UUI_To_UI;
3200
3201
3202 /*****************************************************************
3203 * Function : Component_Sequence
3204 * Parameters IN : Revised Component unique index information
3205 * Parameters OUT: Error Text
3206 * Returns : Component_Sequence_Id
3207 * Purpose : Function will query the component sequence id using
3208 * alternate unique key information. If unsuccessfull
3209 * function will return a NULL.
3210 ********************************************************************/
3211 FUNCTION Component_Sequence(p_component_item_id IN NUMBER,
3212 p_operation_sequence_num IN VARCHAR2,
3213 p_effectivity_date IN DATE,
3214 p_bill_sequence_id IN NUMBER,
3215 x_err_text OUT NOCOPY VARCHAR2 )
3216 RETURN NUMBER
3217 IS
3218 l_id NUMBER;
3219 ret_code NUMBER;
3220 l_err_text VARCHAR2(2000);
3221 BEGIN
3222
3223 select component_sequence_id
3224 into l_id
3225 from bom_inventory_components
3226 where bill_sequence_id = p_bill_sequence_id
3227 and component_item_id = p_component_item_id
3228 and operation_seq_num = p_operation_sequence_num
3229 and effectivity_date = p_effectivity_date;
3230
3231 RETURN l_id;
3232
3233 EXCEPTION
3234
3235 WHEN OTHERS THEN
3236 RETURN NULL;
3237
3238 END Component_Sequence;
3239
3240
3241 /*************************************************************
3242 * Function : BillAndRevItemSeq
3243 * Parameters IN : Revised Item Unique Key information
3244 * Parameters OUT: Bill Sequence ID
3245 * Returns : Revised Item Sequence
3246 * Purpose : Will use the revised item information to find the bill
3247 * sequence and the revised item sequence.
3248 * History : Added p_new_routing_revsion and
3249 * p_from_end_item_number in argument
3250 *
3251 * Moved from BOM_Val_To_Id BOMSVIDB.pls to resolve Eco dependency
3252 * by MK on 12/03/00
3253 ******************************************************************/
3254 FUNCTION BillAndRevItemSeq( p_revised_item_id IN NUMBER
3255 , p_alternate_bom_code IN VARCHAR2 := NULL --- Bug 2429272 Change 1
3256 , p_item_revision IN VARCHAR2
3257 , p_effective_date IN DATE
3258 , p_change_notice IN VARCHAR2
3259 , p_organization_id IN NUMBER
3260 , p_new_routing_revision IN VARCHAR2 := NULL
3261 , p_from_end_item_number IN VARCHAR2 := NULL
3262 , x_Bill_Sequence_Id OUT NOCOPY NUMBER
3263 , x_lot_number OUT NOCOPY VARCHAR2
3264 , x_from_wip_entity_id OUT NOCOPY NUMBER
3265 , x_to_wip_entity_id OUT NOCOPY NUMBER
3266 , x_from_cum_qty OUT NOCOPY NUMBER
3267 , x_eco_for_production OUT NOCOPY NUMBER
3268 , x_cfm_routing_flag OUT NOCOPY NUMBER
3269 )
3270 RETURN NUMBER
3271 IS
3272 l_Bill_Seq NUMBER;
3273 l_Rev_Item_Seq NUMBER;
3274 l_Bill_Seq1 NUMBER := NULL; -- Bug 2429272 Change2 Begin
3275
3276 cursor c1 (rev_item_id eng_revised_items.revised_item_id%TYPE,
3277 org_id eng_revised_items.organization_id%TYPE,
3278 alt_bom_code bom_bill_of_materials.alternate_bom_designator%TYPE) is
3279 select bill_sequence_id
3280 from bom_bill_of_materials
3281 where assembly_item_id = rev_item_id and
3282 organization_id = org_id and
3283 nvl(effectivity_control, 1) <> 4 AND -- Bug 4210718
3284 nvl(alternate_bom_designator,'NULL') = nvl(alt_bom_code,'NULL');
3285 -- Bug 2429272 Change2 End
3286 /* Bug 2429272
3287 User is trying to create a eco (through ECOBO or MCO) for the both
3288 primary bill and alternate bill. But the calling procedure/function
3289 is calling this procedure only with revised_item_id , organization_id
3290 and change_notice but not using the parameter alternate_bom_designator.
3291 But there are two records(primary and alt with same revised_item_id,
3292 organization and change_notice) exists for this eco in eng_revised_items
3293 . So, querying eng_revised_items only with revised_item_id, org_id and
3294 change_notice will retrieve two records which is causing the error
3295 */
3296
3297 BEGIN
3298 -- Bug 2429272 Change3 Begin
3299 open c1(p_revised_item_id, p_organization_id,p_alternate_bom_code);
3300 fetch c1 into l_bill_seq1;
3301 if (c1%NOTFOUND) then
3302 l_bill_seq1 := NULL;
3303 end if;
3304
3305 if (l_bill_seq1 is not null) then
3306 SELECT bill_sequence_id
3307 , revised_item_Sequence_id
3308 , lot_number
3309 , from_wip_entity_id
3310 , to_wip_entity_id
3311 , from_cum_qty
3312 , NVL(eco_for_production,2)
3313 , NVL(cfm_routing_flag,2)
3314 INTO l_Bill_Seq
3315 , l_Rev_Item_Seq
3316 , x_lot_number
3317 , x_from_wip_entity_id
3318 , x_to_wip_entity_id
3319 , x_from_cum_qty
3320 , x_eco_for_production
3321 , x_cfm_routing_flag
3322 FROM eng_revised_items
3323 WHERE NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR )
3324 = NVL(p_from_end_item_number,FND_API.G_MISS_CHAR)
3325 AND NVL(new_routing_revision,FND_API.G_MISS_CHAR)
3326 = NVL(p_new_routing_revision,FND_API.G_MISS_CHAR)
3327 AND NVL(new_item_revision, FND_API.G_MISS_CHAR)
3328 = NVL(p_item_revision , FND_API.G_MISS_CHAR)
3329 AND scheduled_date = p_effective_date --bug 5096309 removed trunc
3330 AND change_notice = p_change_notice
3331 AND organization_id = p_organization_id
3332 AND revised_item_id = p_revised_item_id
3333 and bill_sequence_id = l_bill_seq1;
3334 else -- Bug 2429272 Change3 End
3335 SELECT bill_sequence_id
3336 , revised_item_Sequence_id
3337 , lot_number
3338 , from_wip_entity_id
3339 , to_wip_entity_id
3340 , from_cum_qty
3341 , NVL(eco_for_production,2)
3342 , NVL(cfm_routing_flag,2)
3343 INTO l_Bill_Seq
3344 , l_Rev_Item_Seq
3345 , x_lot_number
3346 , x_from_wip_entity_id
3347 , x_to_wip_entity_id
3348 , x_from_cum_qty
3349 , x_eco_for_production
3350 , x_cfm_routing_flag
3351 FROM eng_revised_items
3352 WHERE NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR )
3353 = NVL(p_from_end_item_number,FND_API.G_MISS_CHAR)
3354 AND NVL(new_routing_revision,FND_API.G_MISS_CHAR)
3355 = NVL(p_new_routing_revision,FND_API.G_MISS_CHAR)
3356 AND NVL(new_item_revision, FND_API.G_MISS_CHAR)
3357 = NVL(p_item_revision , FND_API.G_MISS_CHAR)
3358 AND scheduled_date = p_effective_date --bug 5096309 removed trunc
3359 AND change_notice = p_change_notice
3360 AND organization_id = p_organization_id
3361 AND revised_item_id = p_revised_item_id ;
3362 end if; -- Bug 2429272
3363
3364
3365 x_Bill_Sequence_Id := l_Bill_Seq;
3366 RETURN l_Rev_Item_Seq;
3367
3368 EXCEPTION
3369 WHEN OTHERS THEN
3370 x_Bill_Sequence_Id := NULL;
3371 RETURN NULL;
3372 END BillAndRevItemSeq;
3373
3374
3375
3376
3377 /*************************************************************
3378 * Function : RtgAndRevItemSeq
3379 * Parameters IN : Revised Item Unique Key information
3380 * Parameters OUT: Routing Sequence ID
3381 * Returns : Revised Item Sequence
3382 * Purpose : Will use the revised item information to find the bill
3383 * sequence and the revised item sequence.
3384 * History : Added p_new_routing_revsion and
3385 * p_from_end_item_number in argument by MK
3386 * on 11/02/00
3387 * Moved from BOM_RTG_Val_To_Id BOMRVIDB.pls to resolve Eco dependency
3388 * by MK on 12/03/00
3389 **************************************************************/
3390 FUNCTION RtgAndRevItemSeq( p_revised_item_id IN NUMBER
3391 , p_item_revision IN VARCHAR2
3392 , p_effective_date IN DATE
3393 , p_change_notice IN VARCHAR2
3394 , p_organization_id IN NUMBER
3395 , p_new_routing_revision IN VARCHAR2
3396 , p_from_end_item_number IN VARCHAR2 := NULL
3397 , p_alternate_routing_code IN VARCHAR2 := NULL -- Added for bug 13329115
3398 , x_routing_sequence_id OUT NOCOPY NUMBER
3399 , x_lot_number OUT NOCOPY VARCHAR2
3400 , x_from_wip_entity_id OUT NOCOPY NUMBER
3401 , x_to_wip_entity_id OUT NOCOPY NUMBER
3402 , x_from_cum_qty OUT NOCOPY NUMBER
3403 , x_eco_for_production OUT NOCOPY NUMBER
3404 , x_cfm_routing_flag OUT NOCOPY NUMBER
3405 )
3406 RETURN NUMBER
3407 IS
3408 l_Rev_Item_Seq NUMBER;
3409
3410
3411
3412 BEGIN
3413 SELECT routing_sequence_id
3414 , revised_item_Sequence_id
3415 , lot_number
3416 , from_wip_entity_id
3417 , to_wip_entity_id
3418 , from_cum_qty
3419 , NVL(eco_for_production,2)
3420 , NVL(cfm_routing_flag,2)
3421 INTO x_routing_sequence_id
3422 , l_Rev_Item_Seq
3423 , x_lot_number
3424 , x_from_wip_entity_id
3425 , x_to_wip_entity_id
3426 , x_from_cum_qty
3427 , x_eco_for_production
3428 , x_cfm_routing_flag
3429 FROM eng_revised_items
3430 WHERE NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR )
3431 = NVL(p_from_end_item_number, FND_API.G_MISS_CHAR)
3432 AND NVL(new_routing_revision, FND_API.G_MISS_CHAR) =
3433 NVL(p_new_routing_revision, FND_API.G_MISS_CHAR)
3434 AND NVL(new_item_revision,FND_API.G_MISS_CHAR)=
3435 NVL(p_item_revision,FND_API.G_MISS_CHAR)
3436 AND TRUNC(scheduled_date) = TRUNC(p_effective_date)
3437 AND change_notice = p_change_notice
3438 AND organization_id = p_organization_id
3439 AND revised_item_id = p_revised_item_id
3440 AND NVL(alternate_bom_designator, FND_API.G_MISS_CHAR) =
3441 NVL(p_alternate_routing_code, FND_API.G_MISS_CHAR); -- Added for bug 13329115
3442
3443 RETURN l_Rev_Item_Seq;
3444
3445 EXCEPTION
3446 WHEN OTHERS THEN
3447 x_routing_sequence_id := NULL;
3448 RETURN NULL;
3449 END RtgAndRevItemSeq;
3450
3451
3452
3453 /*****************************************************************************
3454 * Procedure : BillAndRevitem_UUI_To_UI
3455 * Parameters IN : Revised Item Unique Key information
3456 * Parameters OUT: Revised Item Seq Id and Bill Sequence ID
3457 * Mesg_Token_Tbl
3458 * Return_Status
3459 * Purpose : Will use the revised item information to find the bill
3460 * sequence and the revised item sequence.
3461 *
3462 * Added by MK on 12/03/00
3463 ******************************************************************************/
3464 PROCEDURE BillAndRevitem_UUI_To_UI
3465 ( p_revised_item_name IN VARCHAR2
3466 , p_alternate_bom_code IN varchar2 := NULL -- Bug 2429272
3467 , p_revised_item_id IN NUMBER
3468 , p_item_revision IN VARCHAR2
3469 , p_effective_date IN DATE
3470 , p_change_notice IN VARCHAR2
3471 , p_organization_id IN NUMBER
3472 , p_new_routing_revision IN VARCHAR2 := NULL
3473 , p_from_end_item_number IN VARCHAR2 := NULL
3474 , p_entity_processed IN VARCHAR2 := 'RC'
3475 , p_component_item_name IN VARCHAR2 := NULL
3476 , p_component_item_id IN NUMBER := NULL
3477 , p_operation_sequence_number IN NUMBER := NULL
3478 , p_rfd_sbc_name IN VARCHAR2 := NULL
3479 , p_transaction_type IN VARCHAR2 := NULL
3480 , x_revised_item_sequence_id OUT NOCOPY NUMBER
3481 , x_bill_sequence_id OUT NOCOPY NUMBER
3482 , x_component_sequence_id OUT NOCOPY NUMBER
3483 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3484 , x_other_message OUT NOCOPY VARCHAR2
3485 , x_other_token_tbl OUT NOCOPY Error_Handler.Token_Tbl_Type
3486 , x_return_status OUT NOCOPY VARCHAR2
3487 )
3488 IS
3489 l_lot_number VARCHAR2(30) ;
3490 l_from_wip_entity_id NUMBER ;
3491 l_to_wip_entity_id NUMBER ;
3492 l_from_cum_qty NUMBER ;
3493 l_eco_for_production NUMBER ;
3494 l_cfm_routing_flag NUMBER ;
3495
3496 l_err_text VARCHAR2(2000);
3497 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3498 l_return_status VARCHAR2(1);
3499 BEGIN
3500
3501 l_return_status := FND_API.G_RET_STS_SUCCESS;
3502
3503
3504 x_revised_item_sequence_id :=
3505 BillAndRevItemSeq( p_revised_item_id => p_revised_item_id
3506 , p_alternate_bom_code => p_alternate_bom_code -- Bug 2429272
3507 , p_item_revision => p_item_revision
3508 , p_effective_date => p_effective_date
3509 , p_change_notice => p_change_notice
3510 , p_organization_id => p_organization_id
3511 , p_new_routing_revision => p_new_routing_revision
3512 , p_from_end_item_number => p_from_end_item_number
3513 , x_Bill_Sequence_Id => x_Bill_Sequence_Id
3514 , x_lot_number => l_lot_number
3515 , x_from_wip_entity_id => l_from_wip_entity_id
3516 , x_to_wip_entity_id => l_to_wip_entity_id
3517 , x_from_cum_qty => l_from_cum_qty
3518 , x_eco_for_production => l_eco_for_production
3519 , x_cfm_routing_flag => l_cfm_routing_flag
3520 );
3521
3522
3523 IF x_revised_item_sequence_id IS NULL AND p_entity_processed = 'RC'
3524 THEN
3525 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
3526 g_Token_Tbl(1).Token_Value := p_component_item_name ;
3527 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
3528 g_Token_Tbl(2).Token_Value := p_revised_item_name;
3529 g_token_tbl(3).token_name := 'ECO_NAME';
3530 g_token_tbl(3).token_value := p_change_notice ;
3531
3532 Error_Handler.Add_Error_Token
3533 ( p_Message_Name => 'BOM_REV_SEQUENCE_NOT_FOUND'
3534 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3535 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3536 , p_Token_Tbl => g_Token_Tbl
3537 );
3538
3539 l_Return_Status := FND_API.G_RET_STS_ERROR;
3540 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3541 x_Return_Status := l_Return_Status;
3542 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
3543 x_other_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
3544 x_other_token_tbl(1).token_value := p_component_item_name ;
3545
3546 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('And this call returned with ' || l_Return_Status); END IF ;
3547
3548 RETURN;
3549
3550 ELSIF p_transaction_type IN
3551 ( BOM_Globals.G_OPR_UPDATE, BOM_globals.G_OPR_DELETE,
3552 BOM_Globals.G_OPR_CANCEL
3553 ) AND
3554 x_bill_sequence_id IS NULL AND p_entity_processed = 'RC'
3555 THEN
3556 l_return_status := FND_API.G_RET_STS_ERROR;
3557
3558 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3559 g_Token_Tbl(1).Token_Value := p_revised_item_name;
3560
3561 Error_Handler.Add_Error_Token
3562 ( p_Message_Name => 'BOM_BILL_SEQUENCE_NOT_FOUND'
3563 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3564 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3565 , p_Token_Tbl => g_Token_Tbl
3566 );
3567 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3568 x_Return_Status := l_Return_Status;
3569 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
3570 x_other_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
3571 x_other_token_tbl(1).token_value := p_component_item_name ;
3572
3573 RETURN;
3574
3575
3576 ELSIF x_revised_item_sequence_id IS NULL AND p_entity_processed = 'SBC'
3577 THEN
3578 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3579 g_Token_Tbl(1).Token_Value := p_revised_item_name ;
3580 g_Token_Tbl(2).Token_Name := 'SUBSTITUTE_ITEM_NAME';
3581 g_Token_Tbl(2).Token_Value := p_rfd_sbc_name ;
3582 g_token_tbl(3).token_name := 'ECO_NAME';
3583 g_token_tbl(3).token_value := p_change_notice ;
3584
3585 Error_Handler.Add_Error_Token
3586 ( p_Message_Name => 'BOM_SBC_REV_SEQ_NOT_FOUND'
3587 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3588 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3589 , p_Token_Tbl => g_Token_Tbl
3590 );
3591
3592 l_Return_Status := FND_API.G_RET_STS_ERROR;
3593 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3594
3595 --
3596 -- Set the other message and its tokens
3597 --
3598 x_other_message := 'BOM_SBC_UUI_SEV_ERROR';
3599 x_other_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
3600 x_other_token_tbl(1).token_value := p_rfd_sbc_name ;
3601
3602 x_Return_Status := l_Return_Status;
3603 RETURN;
3604
3605 ELSIF x_revised_item_sequence_id IS NULL AND p_entity_processed = 'RFD'
3606 THEN
3607
3608 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3609 g_Token_Tbl(1).Token_Value := p_revised_item_name ;
3610 g_Token_Tbl(2).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
3611 g_Token_Tbl(2).Token_Value := p_rfd_sbc_name ;
3612 g_Token_Tbl(3).Token_Name := 'ECO_NAME';
3613 g_Token_Tbl(3).Token_Value := p_change_notice ;
3614
3615 Error_Handler.Add_Error_Token
3616 ( p_Message_Name => 'BOM_RFD_REV_SEQ_NOT_FOUND'
3617 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3618 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3619 , p_Token_Tbl => g_Token_Tbl
3620 );
3621
3622 l_Return_Status := FND_API.G_RET_STS_ERROR;
3623 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3624
3625 --
3626 -- Set the other message
3627 --
3628 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
3629 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
3630 x_other_token_tbl(1).token_value := p_rfd_sbc_name ;
3631
3632 x_Return_Status := l_Return_Status;
3633 RETURN;
3634
3635 ELSIF x_bill_sequence_id IS NULL and p_entity_processed = 'RFD'
3636 THEN
3637 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3638 g_Token_Tbl(1).Token_Value := p_revised_item_name ;
3639 g_Token_Tbl(2).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
3640 g_Token_Tbl(2).Token_Value := p_rfd_sbc_name ;
3641
3642 Error_Handler.Add_Error_Token
3643 ( p_Message_Name => 'BOM_RFD_BILL_SEQ_NOT_FOUND'
3644 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3645 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3646 , p_Token_Tbl => g_Token_Tbl
3647 );
3648 l_Return_Status := FND_API.G_RET_STS_ERROR;
3649 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3650
3651 --
3652 -- Set the other message
3653 --
3654 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
3655 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
3656 x_other_token_tbl(1).token_value := p_rfd_sbc_name ;
3657
3658 x_Return_Status := l_Return_Status;
3659 RETURN;
3660
3661 END IF;
3662
3663
3664 IF p_entity_processed IN ( 'SBC' , 'RFD' )
3665 THEN
3666 x_component_sequence_id :=
3667 Component_Sequence
3668 ( p_component_item_id => p_component_item_id
3669 , p_operation_sequence_num => p_operation_sequence_number
3670 , p_effectivity_date => p_effective_date
3671 , p_bill_sequence_id => x_bill_sequence_id
3672 , x_err_text => l_Err_Text
3673 );
3674
3675 IF Bom_Globals.get_debug = 'Y' then Error_Handler.write_debug
3676 ('Component sequence ' || x_component_sequence_id ) ;
3677 END IF;
3678
3679 IF x_component_sequence_id IS NULL
3680 AND p_entity_processed = 'SBC'
3681 THEN
3682 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
3683 g_Token_Tbl(1).Token_Value := p_component_item_name ;
3684 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
3685 g_Token_Tbl(2).Token_Value := p_revised_item_name ;
3686 g_Token_Tbl(3).Token_Name := 'SUBSTITUTE_ITEM_NAME';
3687 g_Token_Tbl(3).Token_Value := p_rfd_sbc_name ;
3688
3689 Error_Handler.Add_Error_Token
3690 ( p_Message_Name => 'BOM_SBC_COMP_SEQ_NOT_FOUND'
3691 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3692 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3693 , p_Token_Tbl => g_Token_Tbl
3694 );
3695 --
3696 -- Set the other message and its tokens
3697 --
3698 x_other_message := 'BOM_SBC_UUI_SEV_ERROR';
3699 x_other_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
3700 x_other_token_tbl(1).token_value := p_rfd_sbc_name ;
3701
3702 l_Return_Status := FND_API.G_RET_STS_ERROR;
3703 g_Token_Tbl.Delete;
3704
3705 ELSIF x_component_sequence_id IS NULL
3706 AND p_entity_processed = 'RFD'
3707 THEN
3708 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3709 g_Token_Tbl(1).Token_Value := p_revised_item_name ;
3710 g_Token_Tbl(2).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
3711 g_Token_Tbl(2).Token_Value := p_rfd_sbc_name ;
3712 g_Token_Tbl(3).Token_Name := 'REVISED_COMPONENT_NAME';
3713 g_Token_Tbl(3).Token_Value := p_component_item_name ;
3714
3715 Error_Handler.Add_Error_Token
3716 ( p_Message_Name => 'BOM_RFD_COMP_SEQ_NOT_FOUND'
3717 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3718 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3719 , p_Token_Tbl => g_Token_Tbl
3720 );
3721 --
3722 -- Set the other message
3723 --
3724 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
3725 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
3726 x_other_token_tbl(1).token_value := p_rfd_sbc_name ;
3727
3728 l_Return_Status := FND_API.G_RET_STS_ERROR;
3729 g_Token_Tbl.Delete;
3730 END IF;
3731
3732 END IF ;
3733
3734 -- Set Revised Item Attributes to Global System Information.
3735 Bom_Globals.Set_Lot_Number(l_lot_number) ;
3736 Bom_Globals.Set_From_Wip_Entity_Id(l_from_wip_entity_id) ;
3737 Bom_Globals.Set_To_Wip_Entity_Id(l_to_wip_entity_id) ;
3738 Bom_Globals.Set_From_Cum_Qty(l_from_cum_qty) ;
3739 Bom_Globals.Set_Eco_For_Production(l_eco_for_production) ;
3740
3741 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3742 x_Return_Status := l_Return_Status;
3743
3744
3745 END BillAndRevitem_UUI_To_UI ;
3746
3747
3748
3749 /*****************************************************************************
3750 * Procedure : RtgAndRevitem_UUI_UI
3751 * Parameters IN : Revised Item Unique Key information
3752 * Parameters OUT: Revised Item Seq Id and Routing Sequence ID
3753 * Mesg_Token_Tbl
3754 * Return_Status
3755 * Purpose : Will use the revised item information to find the bill
3756 * sequence and the revised item sequence.
3757 *
3758 * Added by MK on 12/03/00
3759 ******************************************************************************/
3760 PROCEDURE RtgAndRevitem_UUI_To_UI
3761 ( p_revised_item_name IN VARCHAR2
3762 , p_revised_item_id IN NUMBER
3763 , p_item_revision IN VARCHAR2
3764 , p_effective_date IN DATE
3765 , p_change_notice IN VARCHAR2
3766 , p_organization_id IN NUMBER
3767 , p_new_routing_revision IN VARCHAR2 := NULL
3768 , p_from_end_item_number IN VARCHAR2 := NULL
3769 , p_entity_processed IN VARCHAR2 := 'ROP'
3770 , p_operation_sequence_number IN NUMBER := NULL
3771 , p_operation_type IN NUMBER := NULL
3772 , p_resource_sequence_number IN NUMBER := NULL
3773 , p_sub_resource_code IN VARCHAR2 := NULL
3774 , p_schedule_sequence_number IN NUMBER := NULL
3775 , p_transaction_type IN VARCHAR2 := NULL
3776 , p_alternate_routing_code IN VARCHAR2 := NULL -- Added for bug 13329115
3777 , x_revised_item_sequence_id OUT NOCOPY NUMBER
3778 , x_routing_sequence_id OUT NOCOPY NUMBER
3779 , x_operation_sequence_id OUT NOCOPY NUMBER
3780 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3781 , x_other_message OUT NOCOPY VARCHAR2
3782 , x_other_token_tbl OUT NOCOPY Error_Handler.Token_Tbl_Type
3783 , x_return_status OUT NOCOPY VARCHAR2
3784 )
3785 IS
3786 l_lot_number VARCHAR2(30) ;
3787 l_from_wip_entity_id NUMBER ;
3788 l_to_wip_entity_id NUMBER ;
3789 l_from_cum_qty NUMBER ;
3790 l_eco_for_production NUMBER ;
3791 l_cfm_routing_flag NUMBER ;
3792 l_err_text VARCHAR2(2000);
3793 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3794 l_return_status VARCHAR2(1);
3795 BEGIN
3796
3797 l_return_status := FND_API.G_RET_STS_SUCCESS;
3798
3799 x_revised_item_sequence_id :=
3800 RtgAndRevItemSeq
3801 ( p_revised_item_id => p_revised_item_id
3802 , p_item_revision => p_item_revision
3803 , p_effective_date => p_effective_date
3804 , p_change_notice => p_change_notice
3805 , p_organization_id => p_organization_id
3806 , p_new_routing_revision => p_new_routing_revision
3807 , p_from_end_item_number => p_from_end_item_number
3808 , p_alternate_routing_code => p_alternate_routing_code -- Added for bug 13329115
3809 , x_routing_sequence_id => x_routing_sequence_id
3810 , x_lot_number => l_lot_number
3811 , x_from_wip_entity_id => l_from_wip_entity_id
3812 , x_to_wip_entity_id => l_to_wip_entity_id
3813 , x_from_cum_qty => l_from_cum_qty
3814 , x_eco_for_production => l_eco_for_production
3815 , x_cfm_routing_flag => l_cfm_routing_flag
3816 );
3817
3818 IF x_revised_item_sequence_id IS NULL AND p_entity_processed = 'ROP'
3819 THEN
3820 g_Token_Tbl(1).Token_Name := 'OP_SEQ_NUMBER';
3821 g_Token_Tbl(1).Token_Value := p_operation_sequence_number;
3822 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
3823 g_Token_Tbl(2).Token_Value := p_revised_item_name;
3824 g_token_tbl(3).token_name := 'ECO_NAME';
3825 g_token_tbl(3).token_value := p_change_notice ;
3826
3827 Error_Handler.Add_Error_Token
3828 ( p_Message_Name => 'BOM_OP_RIT_SEQUENCE_NOT_FOUND'
3829 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3830 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3831 , p_Token_Tbl => g_Token_Tbl
3832 );
3833
3834 l_Return_Status := FND_API.G_RET_STS_ERROR;
3835 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3836 x_Return_Status := l_Return_Status;
3837
3838
3839 RETURN;
3840
3841 ELSIF x_revised_item_sequence_id IS NULL AND p_entity_processed = 'RES'
3842 THEN
3843
3844 g_Token_Tbl(1).Token_Name := 'RES_SEQ_NUMBER';
3845 g_Token_Tbl(1).Token_Value := p_resource_sequence_number;
3846 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
3847 g_Token_Tbl(2).Token_Value := p_revised_item_name;
3848 g_token_tbl(3).token_name := 'ECO_NAME';
3849 g_token_tbl(3).token_value := p_change_notice ;
3850
3851 Error_Handler.Add_Error_Token
3852 ( p_Message_Name => 'BOM_RES_RIT_SEQUENCE_NOT_FOUND'
3853 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3854 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3855 , p_Token_Tbl => g_Token_Tbl
3856 );
3857
3858 l_Return_Status := FND_API.G_RET_STS_ERROR;
3859 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3860 x_Return_Status := l_Return_Status;
3861
3862 RETURN;
3863
3864 ELSIF x_revised_item_sequence_id IS NULL AND p_entity_processed = 'SR'
3865 THEN
3866 g_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
3867 g_Token_Tbl(1).token_value := p_sub_resource_code ;
3868 g_Token_Tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
3869 g_Token_Tbl(2).token_value := p_schedule_sequence_number ;
3870 g_Token_Tbl(3).Token_Name := 'REVISED_ITEM_NAME';
3871 g_Token_Tbl(3).Token_Value := p_revised_item_name;
3872 g_token_tbl(4).token_name := 'ECO_NAME';
3873 g_token_tbl(4).token_value := p_change_notice ;
3874
3875 Error_Handler.Add_Error_Token
3876 ( p_Message_Name => 'BOM_SUB_RES_RIT_SEQ_NOT_FOUND'
3877 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3878 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3879 , p_Token_Tbl => g_Token_Tbl
3880 );
3881
3882 l_Return_Status := FND_API.G_RET_STS_ERROR;
3883 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3884 x_Return_Status := l_Return_Status;
3885
3886 RETURN;
3887
3888 END IF;
3889
3890 IF p_entity_processed IN ( 'RES' , 'SR' )
3891 THEN
3892
3893 x_operation_sequence_id :=
3894 BOM_RTG_Val_To_Id.Operation_Sequence_id
3895 ( p_routing_sequence_id => x_routing_sequence_id
3896 , p_operation_type => p_operation_type
3897 , p_operation_seq_num => p_operation_sequence_number
3898 , p_effectivity_date => p_effective_date
3899 , x_err_text => l_err_text
3900 );
3901
3902 IF x_operation_sequence_id IS NULL AND p_entity_processed = 'RES'
3903 THEN
3904 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3905 g_token_tbl(1).token_value := p_operation_sequence_number ;
3906 Error_Handler.Add_Error_Token
3907 ( p_Message_Name => 'BOM_RES_OP_NOT_FOUND'
3908 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3909 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3910 , p_Token_Tbl => g_Token_Tbl
3911 );
3912 l_Return_Status := FND_API.G_RET_STS_ERROR;
3913
3914 ELSIF x_operation_sequence_id IS NULL AND p_entity_processed = 'SOR'
3915 THEN
3916 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3917 g_token_tbl(1).token_value := p_operation_sequence_number ;
3918
3919 Error_Handler.Add_Error_Token
3920 ( p_Message_Name => 'BOM_SUB_RES_OP_NOT_FOUND'
3921 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3922 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3923 , p_Token_Tbl => g_Token_Tbl
3924 );
3925 l_Return_Status := FND_API.G_RET_STS_ERROR;
3926 RETURN;
3927
3928 ELSIF l_err_text IS NOT NULL AND
3929 (x_operation_sequence_id IS NULL OR
3930 x_operation_sequence_id = FND_API.G_MISS_NUM
3931 )
3932 THEN
3933 -- This is an unexpected error.
3934 Error_Handler.Add_Error_Token
3935 ( p_Message_Name => NULL
3936 , p_Message_Text => l_err_text || ' in ' ||
3937 G_PKG_NAME
3938 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3939 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3940 );
3941 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3942 END IF;
3943 END IF ;
3944
3945 -- Set Revised Item Attributes to Global System Information.
3946 Bom_Rtg_Globals.Set_Lot_Number(l_lot_number) ;
3947 Bom_Rtg_Globals.Set_From_Wip_Entity_Id(l_from_wip_entity_id) ;
3948 Bom_Rtg_Globals.Set_To_Wip_Entity_Id(l_to_wip_entity_id) ;
3949 Bom_Rtg_Globals.Set_From_Cum_Qty(l_from_cum_qty) ;
3950 Bom_Rtg_Globals.Set_Eco_For_Production(l_eco_for_production) ;
3951 Bom_Rtg_Globals.Set_Routing_Sequence_Id(x_routing_sequence_id) ;
3952
3953
3954 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3955 x_Return_Status := l_Return_Status;
3956
3957 END RtgAndRevitem_UUI_To_UI ;
3958
3959 PROCEDURE Change_Line_UUI_To_UI
3960 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
3961 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
3962 , x_change_line_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
3963 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3964 , x_Return_Status OUT NOCOPY VARCHAR2
3965 )
3966 IS
3967
3968 l_err_text VARCHAR2(2000);
3969 l_change_line_unexp_rec Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type ;
3970 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3971 l_return_status VARCHAR2(1);
3972 BEGIN
3973
3974 l_change_line_unexp_rec := p_change_line_unexp_rec;
3975 l_return_status := FND_API.G_RET_STS_SUCCESS;
3976
3977 g_Token_Tbl(1).Token_Name := 'LINE_NAME';
3978 g_Token_Tbl(1).Token_Value := p_change_line_rec.name;
3979
3980 /***********************************************************
3981 --
3982 -- Verify that the unique key columns are not null or missing
3983 --
3984 ************************************************************/
3985 IF (p_change_line_rec.name IS NULL OR
3986 p_change_line_rec.name = FND_API.G_MISS_CHAR)
3987
3988 THEN
3989 Error_Handler.Add_Error_Token
3990 ( p_Message_Name => 'ENG_CL_NAME_KEYCOL_NULL'
3991 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3992 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3993 );
3994 l_Return_Status := FND_API.G_RET_STS_ERROR;
3995 END IF;
3996
3997 --
3998 -- If Key columns are NULL then return
3999 --
4000 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4001 THEN
4002 x_Return_Status := l_Return_Status;
4003 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4004 RETURN;
4005 END IF;
4006
4007 --
4008 -- User Unique Key for Change Line is:
4009 -- ECO Name, Line Name, Org
4010 -- Org Code -> ID conversion will happen before this step
4011 -- No need to convert to Line Name to Id in this step
4012 --
4013 g_Token_Tbl.Delete ;
4014
4015 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4016 x_change_line_unexp_rec := l_change_line_unexp_rec;
4017 x_Return_Status := l_Return_Status;
4018
4019
4020 END Change_Line_UUI_To_UI ;
4021
4022
4023 PROCEDURE Change_Line_VID
4024 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
4025 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
4026 , x_change_line_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
4027 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
4028 , x_Return_Status OUT NOCOPY VARCHAR2
4029 )
4030 IS
4031
4032 l_err_text VARCHAR2(2000) := NULL;
4033 l_Return_Status VARCHAR2(1);
4034 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
4035 l_Token_Tbl Error_Handler.Token_Tbl_Type;
4036 l_change_mgmt_type_code VARCHAR2(30);
4037 l_hdr_change_mgmt_type_code VARCHAR2(30);
4038 l_change_line_unexp_rec Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type ;
4039 l_dynamic_sql VARCHAR2(4000);
4040 l_dynamic_cursor INTEGER;
4041 l_dummy INTEGER;
4042
4043 l_query_object_name VARCHAR2(30);
4044 l_query_column1_name VARCHAR2(30);
4045 l_query_column2_name VARCHAR2(30);
4046 l_query_column3_name VARCHAR2(30);
4047 l_query_column4_name VARCHAR2(30);
4048 l_query_column5_name VARCHAR2(30);
4049 l_query_column1_type VARCHAR2(8);
4050 l_query_column2_type VARCHAR2(8);
4051 l_query_column3_type VARCHAR2(8);
4052 l_query_column4_type VARCHAR2(8);
4053 l_query_column5_type VARCHAR2(8);
4054 l_fk1_column_name VARCHAR2(30);
4055 l_fk2_column_name VARCHAR2(30);
4056 l_fk3_column_name VARCHAR2(30);
4057 l_fk4_column_name VARCHAR2(30);
4058 l_fk5_column_name VARCHAR2(30);
4059 l_pk1_name VARCHAR2(240);
4060 l_pk2_name VARCHAR2(240);
4061 l_pk3_name VARCHAR2(240);
4062 l_pk4_name VARCHAR2(240);
4063 l_pk5_name VARCHAR2(240);
4064 l_where_clause_empty BOOLEAN;
4065
4066 l_change_type_id NUMBER ;
4067 l_disable_date DATE ;
4068 l_item_id NUMBER ;
4069 l_item_revision_id NUMBER ;
4070 l_return_value NUMBER;
4071 --Bug 2848506
4072 l_display_name VARCHAR2(240);
4073 l_error_text VARCHAR2(240);
4074 l_org_id NUMBER;
4075 l_rev_id NUMBER;
4076 l_inv_item_id NUMBER;
4077
4078
4079
4080 BEGIN
4081 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
4082 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
4083 l_display_name :=p_change_line_rec.object_display_name;
4084 l_change_line_unexp_rec := p_change_line_unexp_rec ;
4085 l_token_tbl(1).token_name := 'LINE_NAME';
4086 l_token_tbl(1).token_value := p_change_line_rec.name;
4087
4088 l_change_line_unexp_rec.organization_id := Organization(p_change_line_rec.organization_code, l_err_text);
4089
4090 l_change_line_unexp_rec.change_id :=
4091 Get_Change_Id(p_change_notice => p_change_line_rec.eco_name,
4092 p_org_id => l_change_line_unexp_rec.organization_id,
4093 x_change_mgmt_type_code => l_hdr_change_mgmt_type_code
4094 );
4095
4096 IF p_change_line_rec.change_management_type IS NOT NULL AND
4097 p_change_line_rec.change_management_type <> FND_API.G_MISS_CHAR
4098 THEN
4099 Change_Mgmt_Type
4100 (
4101 p_change_mgmt_type_name => p_change_line_rec.change_management_type
4102 , x_change_mgmt_type_code => l_change_mgmt_type_code
4103 , x_err_text => l_err_text
4104 );
4105
4106 IF l_change_mgmt_type_code IS NULL
4107 OR l_change_mgmt_type_code <> l_hdr_change_mgmt_type_code
4108 THEN
4109 l_token_tbl(2).token_name := 'CHANGE_MGMT_TYPE_NAME';
4110 l_token_tbl(2).token_value := p_change_line_rec.change_management_type;
4111
4112 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4113 THEN
4114 Error_Handler.Add_Error_Token
4115 (
4116 p_Message_Name => 'ENG_CHANGE_MGMT_TYPE_INVALID'
4117 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4118 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4119 , p_Token_Tbl => l_Token_Tbl
4120 );
4121 END IF;
4122
4123 x_Return_Status := FND_API.G_RET_STS_ERROR;
4124 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4125 END IF;
4126
4127 --Bug No: 3463472
4128 --Issue: DEF-1694
4129 --Desctiption: Removed the _LINE coming after the change code.
4130 --l_change_mgmt_type_code := l_change_mgmt_type_code || '_LINE';
4131 END IF;
4132
4133
4134 -- Convert change_type_code to change_type_id
4135 IF p_change_line_rec.change_type_code IS NOT NULL AND
4136 p_change_line_rec.change_type_code <> FND_API.G_MISS_CHAR
4137 THEN
4138 --Bug No: 3463472
4139 --Issue: DEF-1694
4140 --Calling procedure Change_Order_Line_Type
4141 --Change_Order_Type
4142 Change_Order_Line_Type
4143 ( p_change_order_type => p_change_line_rec.change_type_code
4144 , p_change_mgmt_type => l_change_mgmt_type_code
4145 , x_err_text => l_err_text
4146 , x_change_order_id => l_change_type_id
4147 , x_disable_date => l_disable_date
4148 , x_object_id => l_change_line_unexp_rec.object_id
4149 );
4150
4151 IF l_change_type_id IS NULL
4152 THEN
4153 l_token_tbl(2).token_name := 'CHANGE_TYPE_CODE';
4154 l_token_tbl(2).token_value := p_change_line_rec.change_type_code;
4155
4156 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4157 THEN
4158 Error_Handler.Add_Error_Token
4159 ( p_Message_Name => 'ENG_CL_CHANGE_TYPE_INVALID'
4160 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4161 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4162 , p_Token_Tbl => l_Token_Tbl
4163 );
4164 END IF;
4165
4166 l_Return_Status := FND_API.G_RET_STS_ERROR;
4167 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4168
4169 ELSIF l_change_type_id = FND_API.G_MISS_NUM
4170 THEN
4171 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4172 THEN
4173 Error_Handler.Add_Error_Token
4174 ( p_Message_Text => l_err_text
4175 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4176 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4177 );
4178 END IF;
4179
4180 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4182 ELSE
4183 l_change_line_unexp_rec.change_type_id := l_change_type_id;
4184 END IF;
4185
4186 END IF;
4187
4188 -- Change order type must not be disabled
4189
4190 IF l_change_line_unexp_rec.change_type_id IS NOT NULL
4191 AND l_disable_date < SYSDATE
4192 THEN
4193 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4194 THEN
4195 l_token_tbl(2).token_name := 'CHANGE_TYPE_CODE';
4196 l_token_tbl(2).token_value := p_change_line_rec.change_type_code ;
4197
4198 Error_Handler.Add_Error_Token
4199 ( p_Message_Name => 'ENG_CL_CHANGE_TYPE_DISABLED'
4200 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4201 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4202 , p_Token_Tbl => l_Token_Tbl
4203 );
4204 END IF;
4205 l_Return_Status := FND_API.G_RET_STS_ERROR;
4206 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4207
4208 END IF;
4209 /* Not required any more as subjects have moved to eng_change_subjects
4210 IF p_change_line_rec.sequence_number = -1
4211 THEN
4212 l_change_line_unexp_rec.change_type_id :=
4213 Get_Type_From_Header(p_change_notice => p_change_line_rec.eco_name,
4214 p_org_id => l_change_line_unexp_rec.organization_id
4215 );
4216 --Bug 2848506
4217
4218 END IF;
4219 if l_display_name is null then
4220 l_display_name:=Get_object_name(l_change_line_unexp_rec.object_id);
4221 end if;
4222
4223
4224 -- IF (p_change_line_rec.change_type_code IS NOT NULL)
4225 -- THEN
4226 IF (l_display_name =
4227 bom_globals.retrieve_message ('ENG', 'ENG_SUBJECT_ITEM_REVISION')
4228 )
4229 THEN
4230 --For Item Revision PK1_NAME,PK3_NAME,PK4_NAME Columns are mandatory
4231 IF ( p_change_line_rec.pk1_name IS NOT NULL
4232 AND p_change_line_rec.pk3_name IS NOT NULL
4233 AND p_change_line_rec.pk4_name IS NOT NULL
4234 )
4235 THEN
4236 l_org_id := ORGANIZATION (p_change_line_rec.pk4_name, l_err_text);
4237
4238 IF (l_org_id IS NOT NULL AND l_org_id <> fnd_api.g_miss_num)
4239 THEN
4240 l_inv_item_id := revised_item (
4241 p_change_line_rec.pk3_name,
4242 l_org_id,
4243 l_err_text
4244 );
4245
4246 IF ( l_inv_item_id IS NOT NULL
4247 AND l_inv_item_id <> fnd_api.g_miss_num
4248 )
4249 THEN
4250 l_rev_id := revised_item_code (
4251 l_inv_item_id,
4252 l_org_id,
4253 p_change_line_rec.pk1_name
4254 );
4255
4256 IF (l_rev_id IS NOT NULL AND l_rev_id <> fnd_api.g_miss_num)
4257 THEN
4258 l_return_status := 'S'; --fnd_api.g_ret_sts_error;
4259 ELSE
4260 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4261 l_token_tbl (1).token_value :=
4262 p_change_line_rec.change_type_code;
4263 error_handler.add_error_token (
4264 p_message_name=> 'ENG_PK1_NAME_INVALID',
4265 p_mesg_token_tbl=> l_mesg_token_tbl,
4266 x_mesg_token_tbl=> l_mesg_token_tbl,
4267 p_token_tbl=> l_token_tbl
4268 );
4269 l_return_status := fnd_api.g_ret_sts_error;
4270 END IF; --end of l_rev_id IS NOT NULL
4271 ELSE
4272 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4273 l_token_tbl (1).token_value :=
4274 p_change_line_rec.change_type_code;
4275 error_handler.add_error_token (
4276 p_message_name=> 'ENG_PK3_NAME_INVALID',
4277 p_mesg_token_tbl=> l_mesg_token_tbl,
4278 x_mesg_token_tbl=> l_mesg_token_tbl,
4279 p_token_tbl=> l_token_tbl
4280 );
4281 l_return_status := fnd_api.g_ret_sts_error;
4282 END IF; -- l_inv_item_id IS NOT NULL
4283 ELSE
4284 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4285 l_token_tbl (1).token_value :=
4286 p_change_line_rec.change_type_code;
4287 error_handler.add_error_token (
4288 p_message_name=> 'ENG_PK4_NAME_INVALID',
4289 p_mesg_token_tbl=> l_mesg_token_tbl,
4290 x_mesg_token_tbl=> l_mesg_token_tbl,
4291 p_token_tbl=> l_token_tbl
4292 );
4293 l_return_status := fnd_api.g_ret_sts_error;
4294 END IF; --l_org_id IS NOT NULL
4295 ELSE
4296 IF ( p_change_line_rec.pk1_name IS NULL
4297 OR p_change_line_rec.pk1_name = fnd_api.g_miss_char
4298 )
4299 THEN
4300 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4301 l_token_tbl (1).token_value :=
4302 p_change_line_rec.change_type_code;
4303 error_handler.add_error_token (
4304 p_message_name=> 'ENG_PK1_NAME_INVALID',
4305 p_mesg_token_tbl=> l_mesg_token_tbl,
4306 x_mesg_token_tbl=> l_mesg_token_tbl,
4307 p_token_tbl=> l_token_tbl
4308 );
4309 l_return_status := fnd_api.g_ret_sts_error;
4310 END IF;
4311
4312 IF ( p_change_line_rec.pk3_name IS NULL
4313 OR p_change_line_rec.pk3_name = fnd_api.g_miss_char
4314 )
4315 THEN
4316 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4317 l_token_tbl (1).token_value :=
4318 p_change_line_rec.change_type_code;
4319 error_handler.add_error_token (
4320 p_message_name=> 'ENG_PK3_NAME_INVALID',
4321 p_mesg_token_tbl=> l_mesg_token_tbl,
4322 x_mesg_token_tbl=> l_mesg_token_tbl,
4323 p_token_tbl=> l_token_tbl
4324 );
4325 l_return_status := fnd_api.g_ret_sts_error;
4326 END IF;
4327
4328 IF ( p_change_line_rec.pk4_name IS NULL
4329 OR p_change_line_rec.pk4_name = fnd_api.g_miss_char
4330 )
4331 THEN
4332 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4333 l_token_tbl (1).token_value :=
4334 p_change_line_rec.change_type_code;
4335 error_handler.add_error_token (
4336 p_message_name=> 'ENG_PK4_NAME_INVALID',
4337 p_mesg_token_tbl=> l_mesg_token_tbl,
4338 x_mesg_token_tbl=> l_mesg_token_tbl,
4339 p_token_tbl=> l_token_tbl
4340 );
4341 l_return_status := fnd_api.g_ret_sts_error;
4342 END IF;
4343 END IF; --p_change_line_rec.Pk1_Name
4344 ELSIF (l_display_name =
4345 bom_globals.retrieve_message ('ENG', 'ENG_SUBJECT_ITEM')
4346 )
4347 THEN
4348 --For Item PK1_NAME,PK3_NAME Columns are mandatory
4349 IF ( p_change_line_rec.pk1_name IS NOT NULL
4350 AND p_change_line_rec.pk3_name IS NOT NULL
4351 )
4352 THEN
4353 l_org_id := ORGANIZATION (p_change_line_rec.pk3_name, l_err_text);
4354
4355 IF (l_org_id IS NOT NULL AND l_org_id <> fnd_api.g_miss_num)
4356 THEN
4357 l_rev_id := revised_item (
4358 p_change_line_rec.pk1_name,
4359 l_org_id,
4360 l_err_text
4361 );
4362
4363 IF (l_rev_id IS NOT NULL AND l_rev_id <> fnd_api.g_miss_num)
4364 THEN
4365 l_return_status := 'S';
4366 ELSE
4367 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4368 l_token_tbl (1).token_value :=
4369 p_change_line_rec.change_type_code;
4370 error_handler.add_error_token (
4371 p_message_name=> 'ENG_PK1_NAME_INVALID',
4372 p_mesg_token_tbl=> l_mesg_token_tbl,
4373 x_mesg_token_tbl=> l_mesg_token_tbl,
4374 p_token_tbl=> l_token_tbl
4375 );
4376 l_return_status := fnd_api.g_ret_sts_error;
4377 END IF; --l_rev_id IS NOT NULL
4378 ELSE
4379 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4380 l_token_tbl (1).token_value :=
4381 p_change_line_rec.change_type_code;
4382 error_handler.add_error_token (
4383 p_message_name=> 'ENG_PK3_NAME_INVALID',
4384 p_mesg_token_tbl=> l_mesg_token_tbl,
4385 x_mesg_token_tbl=> l_mesg_token_tbl,
4386 p_token_tbl=> l_token_tbl
4387 );
4388 l_return_status := fnd_api.g_ret_sts_error;
4389 END IF; --l_org_id IS NOT NULL
4390 ELSE
4391 IF ( p_change_line_rec.pk1_name IS NULL
4392 OR p_change_line_rec.pk1_name = fnd_api.g_miss_char
4393 )
4394 THEN
4395 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4396 l_token_tbl (1).token_value :=
4397 p_change_line_rec.change_type_code;
4398 error_handler.add_error_token (
4399 p_message_name=> 'ENG_PK1_NAME_INVALID',
4400 p_mesg_token_tbl=> l_mesg_token_tbl,
4401 x_mesg_token_tbl=> l_mesg_token_tbl,
4402 p_token_tbl=> l_token_tbl
4403 );
4404 l_return_status := fnd_api.g_ret_sts_error;
4405 END IF;
4406
4407 IF ( p_change_line_rec.pk3_name IS NULL
4408 OR p_change_line_rec.pk3_name = fnd_api.g_miss_char
4409 )
4410 THEN
4411 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4412 l_token_tbl (1).token_value :=
4413 p_change_line_rec.change_type_code;
4414 error_handler.add_error_token (
4415 p_message_name=> 'ENG_PK3_NAME_INVALID',
4416 p_mesg_token_tbl=> l_mesg_token_tbl,
4417 x_mesg_token_tbl=> l_mesg_token_tbl,
4418 p_token_tbl=> l_token_tbl
4419 );
4420 l_return_status := fnd_api.g_ret_sts_error;
4421 END IF;
4422 END IF; --p_change_line_rec.Pk1_Name
4423 END IF; --End Of If of check for l_display_name
4424 -- END IF; --End of IF (p_change_line_rec.change_type_code is not null )
4425
4426
4427 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4428 THEN
4429 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4430 x_change_line_unexp_rec := l_change_line_unexp_rec;
4431 x_Return_Status := l_Return_Status;
4432 RETURN;
4433 END IF;
4434
4435 --End Of Bug 2848506
4436 */
4437
4438 IF p_change_line_rec.status_name IS NOT NULL AND
4439 p_change_line_rec.status_name <> FND_API.G_MISS_CHAR
4440 THEN
4441 l_change_line_unexp_rec.status_code := Line_Status(p_change_line_rec.status_name);
4442 END IF;
4443
4444
4445 IF p_change_line_rec.Assignee_Name IS NOT NULL AND
4446 p_change_line_rec.Assignee_Name <> FND_API.G_MISS_CHAR
4447 THEN
4448 l_Return_Value :=
4449 Assignee
4450 ( p_assignee => p_change_line_rec.Assignee_Name
4451 , x_err_text => l_err_text
4452 );
4453
4454
4455 IF l_Return_Value IS NULL
4456 THEN
4457 l_token_tbl(2).token_name := 'ASSIGNEE';
4458 l_token_tbl(2).token_value := p_change_line_rec.Assignee_Name;
4459
4460 Error_Handler.Add_Error_Token
4461 ( p_Message_Name => 'ENG_ASSIGNEE_INVALID'
4462 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4463 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4464 , p_Token_Tbl => l_Token_Tbl
4465 );
4466
4467 l_token_tbl.DELETE;
4468 l_Return_Status := FND_API.G_RET_STS_ERROR;
4469
4470 ELSIF l_Return_Value = FND_API.G_MISS_NUM
4471 THEN
4472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4473
4474 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4475 THEN
4476 Error_Handler.Add_Error_Token
4477 ( p_Message_Text => l_err_text
4478 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4479 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4480 );
4481 END IF;
4482
4483 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4484 x_Change_Line_Unexp_Rec := l_Change_Line_Unexp_Rec;
4485 RETURN;
4486
4487 ELSE
4488 l_change_line_unexp_rec.Assignee_id := l_Return_Value;
4489 END IF;
4490
4491 --
4492 -- User should be able to null out assignee
4493 -- ELSE
4494 -- l_ECO_Unexp_Rec.Assignee_id := NULL;
4495
4496
4497
4498 END IF;
4499 /* not required for 11.5.10
4500 -- Subject Validation
4501 Object_Name(p_display_name => l_display_name --p_change_line_rec.object_display_name --Bug 2848506
4502 ,x_object_name => l_change_line_unexp_rec.object_name
4503 ,x_query_object_name => l_query_object_name
4504 ,x_query_column1_name => l_query_column1_name
4505 ,x_query_column2_name => l_query_column2_name
4506 ,x_query_column3_name => l_query_column3_name
4507 ,x_query_column4_name => l_query_column4_name
4508 ,x_query_column5_name => l_query_column5_name
4509 ,x_query_column1_type => l_query_column1_type
4510 ,x_query_column2_type => l_query_column2_type
4511 ,x_query_column3_type => l_query_column3_type
4512 ,x_query_column4_type => l_query_column4_type
4513 ,x_query_column5_type => l_query_column5_type
4514 ,x_fk1_column_name => l_fk1_column_name
4515 ,x_fk2_column_name => l_fk2_column_name
4516 ,x_fk3_column_name => l_fk3_column_name
4517 ,x_fk4_column_name => l_fk4_column_name
4518 ,x_fk5_column_name => l_fk5_column_name
4519 ,x_object_id => l_change_line_unexp_rec.object_id --Bug 2848506
4520 );
4521
4522
4523 IF l_change_line_unexp_rec.object_name IS NOT NULL
4524 and
4525 --Bug 2848506 ,for 'None' line types no validations is required
4526 l_change_line_unexp_rec.object_name <> 'ENG_CHANGE_MISC'
4527
4528 THEN
4529 -- prepare dynamic sql to validate subject instance
4530 Preprocess_Key(p_object_name => l_change_line_unexp_rec.object_name
4531 ,p_change_line_rec => p_change_line_rec
4532 ,x_pk1_name => l_pk1_name
4533 ,x_pk2_name => l_pk2_name
4534 ,x_pk3_name => l_pk3_name
4535 ,x_pk4_name => l_pk4_name
4536 ,x_pk5_name => l_pk5_name
4537 );
4538
4539
4540 l_dynamic_sql := 'SELECT ';
4541 l_dynamic_sql := l_dynamic_sql || l_fk1_column_name;
4542 IF l_fk2_column_name IS NOT NULL THEN
4543 l_dynamic_sql := l_dynamic_sql || ', ' || l_fk2_column_name;
4544 END IF;
4545 IF l_fk3_column_name IS NOT NULL THEN
4546 l_dynamic_sql := l_dynamic_sql || ', ' || l_fk3_column_name;
4547 END IF;
4548 IF l_fk4_column_name IS NOT NULL THEN
4549 l_dynamic_sql := l_dynamic_sql || ', ' || l_fk4_column_name;
4550 END IF;
4551 IF l_fk5_column_name IS NOT NULL THEN
4552 l_dynamic_sql := l_dynamic_sql || ', ' || l_fk5_column_name;
4553 END IF;
4554 l_dynamic_sql := l_dynamic_sql || ' FROM ' || l_query_object_name;
4555 l_dynamic_sql := l_dynamic_sql || ' WHERE ';
4556 l_where_clause_empty := TRUE;
4557
4558 IF l_pk1_name IS NOT NULL THEN
4559 l_where_clause_empty := FALSE;
4560 l_dynamic_sql := l_dynamic_sql || l_query_column1_name || ' = :pk1';
4561 END IF;
4562 IF l_pk2_name IS NOT NULL THEN
4563 IF NOT l_where_clause_empty THEN
4564 l_dynamic_sql := l_dynamic_sql || ' AND ';
4565 END IF;
4566 l_where_clause_empty := FALSE;
4567 l_dynamic_sql := l_dynamic_sql || l_query_column2_name || ' = :pk2';
4568 END IF;
4569 IF l_pk3_name IS NOT NULL THEN
4570 IF NOT l_where_clause_empty THEN
4571 l_dynamic_sql := l_dynamic_sql || ' AND ';
4572 END IF;
4573 l_where_clause_empty := FALSE;
4574 l_dynamic_sql := l_dynamic_sql || l_query_column3_name || ' = :pk3';
4575 END IF;
4576 IF l_pk4_name IS NOT NULL THEN
4577 IF NOT l_where_clause_empty THEN
4578 l_dynamic_sql := l_dynamic_sql || ' AND ';
4579 END IF;
4580 l_where_clause_empty := FALSE;
4581 l_dynamic_sql := l_dynamic_sql || l_query_column4_name || ' = :pk4';
4582 END IF;
4583 IF l_pk5_name IS NOT NULL THEN
4584 IF NOT l_where_clause_empty THEN
4585 l_dynamic_sql := l_dynamic_sql || ' AND ';
4586 END IF;
4587 l_where_clause_empty := FALSE;
4588 l_dynamic_sql := l_dynamic_sql || l_query_column5_name || ' = :pk5';
4589 END IF;
4590
4591 l_dynamic_cursor := dbms_sql.open_cursor;
4592 dbms_sql.parse(l_dynamic_cursor, l_dynamic_sql, dbms_sql.native);
4593 IF l_pk1_name IS NOT NULL THEN
4594 dbms_sql.bind_variable(l_dynamic_cursor, ':pk1', l_pk1_name);
4595 END IF;
4596 IF l_pk2_name IS NOT NULL THEN
4597 dbms_sql.bind_variable(l_dynamic_cursor, ':pk2', l_pk2_name);
4598 END IF;
4599 IF l_pk3_name IS NOT NULL THEN
4600 dbms_sql.bind_variable(l_dynamic_cursor, ':pk3', l_pk3_name);
4601 END IF;
4602 IF l_pk4_name IS NOT NULL THEN
4603 dbms_sql.bind_variable(l_dynamic_cursor, ':pk4', l_pk4_name);
4604 END IF;
4605 IF l_pk5_name IS NOT NULL THEN
4606 dbms_sql.bind_variable(l_dynamic_cursor, ':pk5', l_pk5_name);
4607 END IF;
4608
4609 IF l_fk1_column_name IS NOT NULL THEN
4610 dbms_sql.define_column(l_dynamic_cursor, 1, l_change_line_unexp_rec.pk1_value, 100);
4611 END IF;
4612 IF l_fk2_column_name IS NOT NULL THEN
4613 dbms_sql.define_column(l_dynamic_cursor, 2, l_change_line_unexp_rec.pk2_value, 100);
4614 END IF;
4615 IF l_fk3_column_name IS NOT NULL THEN
4616 dbms_sql.define_column(l_dynamic_cursor, 3, l_change_line_unexp_rec.pk3_value, 100);
4617 END IF;
4618 IF l_fk4_column_name IS NOT NULL THEN
4619 dbms_sql.define_column(l_dynamic_cursor, 4, l_change_line_unexp_rec.pk4_value, 100);
4620 END IF;
4621 IF l_fk5_column_name IS NOT NULL THEN
4622 dbms_sql.define_column(l_dynamic_cursor, 5, l_change_line_unexp_rec.pk5_value, 100);
4623 END IF;
4624
4625 l_dummy := dbms_sql.execute(l_dynamic_cursor);
4626
4627 IF dbms_sql.fetch_rows(l_dynamic_cursor) > 0 THEN
4628 IF l_fk1_column_name IS NOT NULL THEN
4629 dbms_sql.column_value(l_dynamic_cursor, 1, l_change_line_unexp_rec.pk1_value);
4630 END IF;
4631 IF l_fk2_column_name IS NOT NULL THEN
4632 dbms_sql.column_value(l_dynamic_cursor, 2, l_change_line_unexp_rec.pk2_value);
4633 END IF;
4634 IF l_fk3_column_name IS NOT NULL THEN
4635 dbms_sql.column_value(l_dynamic_cursor, 3, l_change_line_unexp_rec.pk3_value);
4636 END IF;
4637 IF l_fk4_column_name IS NOT NULL THEN
4638 dbms_sql.column_value(l_dynamic_cursor, 4, l_change_line_unexp_rec.pk4_value);
4639 END IF;
4640 IF l_fk5_column_name IS NOT NULL THEN
4641 dbms_sql.column_value(l_dynamic_cursor, 5, l_change_line_unexp_rec.pk5_value);
4642 END IF;
4643 END IF;
4644
4645 dbms_sql.close_cursor(l_dynamic_cursor);
4646
4647
4648 IF l_change_line_unexp_rec.pk1_value IS NULL THEN
4649 l_token_tbl(2).token_name := 'PK_VALUES';
4650 l_token_tbl(2).token_value := p_change_line_rec.pk1_name ;
4651
4652 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4653 THEN
4654 Error_Handler.Add_Error_Token
4655 ( p_Message_Name => 'ENG_PK_VALUES_INVALID'
4656 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4657 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4658 , p_Token_Tbl => l_Token_Tbl
4659 );
4660 END IF;
4661
4662 x_Return_Status := FND_API.G_RET_STS_ERROR;
4663 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4664
4665 l_token_tbl.DELETE ;
4666 l_token_tbl(1).token_name := 'LINE_NAME';
4667 l_token_tbl(1).token_value := p_change_line_rec.name;
4668 END IF;
4669 END IF;
4670 */
4671 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4672 AND x_return_status <> FND_API.G_RET_STS_UNEXP_ERROR
4673 THEN
4674 x_Return_Status := l_return_status ;
4675 END IF;
4676
4677 x_change_line_unexp_rec := l_change_line_unexp_rec ;
4678 END Change_Line_VID ;
4679
4680
4681 END ENG_Val_To_Id;