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