[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_LINE_UTIL
Source
1 PACKAGE BODY ENG_Change_Line_Util AS
2 /* $Header: ENGUCHLB.pls 120.7 2007/08/14 06:36:57 prgopala ship $ */
3
4
5 G_Pkg_Name CONSTANT VARCHAR2(30) := 'ENG_Change_Line_Util';
6
7
8 /*****************************************************************
9 * Procedure : Query_Row
10 * Parameters IN : Change Line Key
11 * Parameters OUT: Change Line Exposed column Record
12 * Change Line Unexposed column Record
13 * Returns : None
14 * Purpose : Change Line Query Row
15 * will query the database record and seperate
16 * the unexposed and exposed attributes before returning
17 * the records.
18 ********************************************************************/
19 PROCEDURE Query_Row
20 ( p_line_sequence_number IN NUMBER
21 , p_organization_id IN NUMBER
22 , p_change_notice IN VARCHAR2
23 , p_change_line_name IN VARCHAR2
24 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
25 , x_change_line_rec OUT NOCOPY Eng_Eco_Pub.Change_Line_Rec_Type
26 , x_change_line_unexp_rec OUT NOCOPY Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
27 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
28 , x_return_status OUT NOCOPY VARCHAR2
29 )
30 IS
31
32 /* Define Variable */
33 l_change_line_rec Eng_Eco_Pub.Change_Line_Rec_Type ;
34 l_change_line_unexp_rec Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type;
35 l_bo_id VARCHAR2(3) ;
36 l_err_text VARCHAR2(2000);
37
38
39 /* Define Cursor */
40 Cursor cl_csr( p_line_sequence_number NUMBER
41 , p_change_id NUMBER
42 )
43 IS
44 SELECT cl.change_line_id
45 , cl.change_type_id
46 , cl.status_code
47 , cl.assignee_id
48 , cl.need_by_date
49 , cl.object_id
50 , cl.pk1_value
51 , cl.pk2_value
52 , cl.pk3_value
53 , cl.pk4_value
54 , cl.pk5_value
55 , cl.scheduled_date
56 , cl.implementation_date
57 , cl.cancelation_date
58 , cltl.name
59 , cltl.description
60 , cl.original_system_reference
61 FROM ENG_CHANGE_LINES cl
62 , ENG_CHANGE_LINES_TL cltl
63 WHERE cl.change_id = p_change_id
64 AND cl.sequence_number = p_line_sequence_number
65 AND cl.change_line_id = cltl.change_line_id
66 AND cltl.language = userenv('LANG') ;
67
68 Cursor c_csr( p_change_notice VARCHAR2
69 , p_organization_id NUMBER
70 )
71 IS
72 SELECT change_id FROM eng_engineering_changes
73 WHERE change_notice = p_change_notice and organization_id = p_organization_id;
74
75 cl_rec cl_csr%ROWTYPE ;
76 l_change_id NUMBER;
77
78
79 BEGIN
80
81 IF BOM_Globals.Get_Debug = 'Y' THEN
82 Error_Handler.Write_Debug ('Querying a change line record . . . ' ) ;
83 Error_Handler.Write_Debug (' : line sequence number ' || p_line_sequence_number);
84 Error_Handler.Write_Debug (' : change notice ' || p_change_notice );
85 Error_Handler.Write_Debug (' : organization_id ' || to_char( p_organization_id ));
86 END IF ;
87
88 x_mesg_token_tbl := p_mesg_token_tbl;
89
90 IF NOT c_csr %ISOPEN
91 THEN
92 OPEN c_csr( p_change_notice
93 , p_organization_id ) ;
94 END IF ;
95
96 FETCH c_csr INTO l_change_id;
97 l_change_line_unexp_rec.change_id := l_change_id;
98
99 IF NOT cl_csr %ISOPEN
100 THEN
101 OPEN cl_csr( p_line_sequence_number
102 , l_change_id ) ;
103 END IF ;
104
105 FETCH cl_csr INTO cl_rec ;
106
107 IF cl_csr%FOUND
108 THEN
109
110 -- Set Queried Record to Exposed and Unexposed Record
111 -- Unexposed Column
112 l_change_line_unexp_rec.change_line_id := cl_rec.change_line_id;
113 l_change_line_unexp_rec.organization_id := p_organization_id;
114 l_change_line_unexp_rec.change_type_id := cl_rec.change_type_id;
115 l_change_line_unexp_rec.status_code := cl_rec.status_code;
116 l_change_line_unexp_rec.assignee_id := cl_rec.assignee_id;
117 l_change_line_unexp_rec.object_id := cl_rec.object_id;
118 l_change_line_unexp_rec.pk1_value := cl_rec.pk1_value;
119 l_change_line_unexp_rec.pk2_value := cl_rec.pk2_value;
120 l_change_line_unexp_rec.pk3_value := cl_rec.pk3_value;
121 l_change_line_unexp_rec.pk4_value := cl_rec.pk4_value;
122 l_change_line_unexp_rec.pk5_value := cl_rec.pk5_value;
123
124 -- Exposed Column
125 l_change_line_rec.eco_name := p_change_notice;
126 l_change_line_rec.name := cl_rec.name;
127 l_change_line_rec.description := cl_rec.description;
128 l_change_line_rec.sequence_number := p_line_sequence_number;
129 l_change_line_rec.original_system_reference := cl_rec.original_system_reference ;
130 l_change_line_rec.need_by_date := cl_rec.need_by_date ;
131 l_change_line_rec.scheduled_date := cl_rec.scheduled_date ;
132 l_change_line_rec.implementation_date := cl_rec.implementation_date ;
133 l_change_line_rec.cancelation_date := cl_rec.cancelation_date ;
134
135 /*
136 l_change_line_rec.Attribute_category := cl_rec.ATTRIBUTE_CATEGORY ;
137 l_change_line_rec.Attribute1 := cl_rec.ATTRIBUTE1 ;
138 l_change_line_rec.Attribute2 := cl_rec.ATTRIBUTE2 ;
139 l_change_line_rec.Attribute3 := cl_rec.ATTRIBUTE3 ;
140 l_change_line_rec.Attribute4 := cl_rec.ATTRIBUTE4 ;
141 l_change_line_rec.Attribute5 := cl_rec.ATTRIBUTE5 ;
142 l_change_line_rec.Attribute6 := cl_rec.ATTRIBUTE6 ;
143 l_change_line_rec.Attribute7 := cl_rec.ATTRIBUTE7 ;
144 l_change_line_rec.Attribute8 := cl_rec.ATTRIBUTE8 ;
145 l_change_line_rec.Attribute9 := cl_rec.ATTRIBUTE9 ;
146 l_change_line_rec.Attribute10 := cl_rec.ATTRIBUTE10 ;
147 l_change_line_rec.Attribute11 := cl_rec.ATTRIBUTE11 ;
148 l_change_line_rec.Attribute12 := cl_rec.ATTRIBUTE12 ;
149 l_change_line_rec.Attribute13 := cl_rec.ATTRIBUTE13 ;
150 l_change_line_rec.Attribute14 := cl_rec.ATTRIBUTE14 ;
151 l_change_line_rec.Attribute15 := cl_rec.ATTRIBUTE15 ;
152 */
153
154 IF BOM_Globals.Get_Debug = 'Y' THEN
155 Error_Handler.Write_Debug('Finished querying chage line record . . .') ;
156 END IF ;
157
158 x_return_status := BOM_Globals.G_RECORD_FOUND ;
159 x_change_line_rec := l_change_line_rec ;
160 x_change_line_unexp_rec := l_change_line_unexp_rec;
161
162 ELSE
163
164 x_return_status := BOM_Globals.G_RECORD_NOT_FOUND ;
165 x_change_line_rec := l_change_line_rec ;
166 x_change_line_unexp_rec := l_change_line_unexp_rec ;
167
168 END IF ;
169
170 IF cl_csr%ISOPEN
171 THEN
172 CLOSE cl_csr ;
173 END IF ;
174
175 IF c_csr%ISOPEN
176 THEN
177 CLOSE c_csr ;
178 END IF ;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 l_err_text := G_PKG_NAME || ' Utility (Change Line Query Row) '
183 || substrb(SQLERRM,1,200);
184
185
186 Error_Handler.Add_Error_Token
187 ( p_message_name => NULL
188 , p_message_text => l_err_text
189 , p_mesg_token_tbl => x_mesg_token_tbl
190 , x_mesg_token_tbl => x_mesg_token_tbl
191 );
192
193 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR ;
194
195 END Query_Row;
196
197
198 /*********************************************************************
199 * Procedure : Perform_Writes
200 * Parameters IN : Change Line exposed column record
201 * Change Line unexposed column record
202 * Parameters OUT: Return Status
203 * Message Token Table
204 * Purpose : Perform any insert/update/deletes to the
205 * Change Line table.
206 *********************************************************************/
207 PROCEDURE Perform_Writes
208 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
209 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
210 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
211 , x_return_status OUT NOCOPY VARCHAR2
212 )
213 IS
214
215 l_change_line_rec Eng_Eco_Pub.Change_Line_Rec_Type ;
216 l_change_line_unexp_rec Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type ;
217
218 -- Error Handlig Variables
219 l_return_status VARCHAR2(1);
220 l_temp_return_status VARCHAR2(1);
221 l_err_text VARCHAR2(2000) ;
222 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
223 l_temp_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
224 l_Token_Tbl Error_Handler.Token_Tbl_Type;
225
226
227 BEGIN
228
229 --
230 -- Initialize Common Record and Status
231 --
232 l_change_line_rec := p_change_line_rec ;
233 l_change_line_unexp_rec := p_change_line_unexp_rec ;
234 l_return_status := FND_API.G_RET_STS_SUCCESS ;
235 x_return_status := FND_API.G_RET_STS_SUCCESS ;
236
237 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
238 ('Performing Database Writes . . .') ;
239 END IF ;
240
241
242 IF l_change_line_rec.transaction_type = BOM_Globals.G_OPR_CREATE THEN
243
244 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
245 ('Change Line: Executing Insert Row. . . ') ;
246 END IF;
247
248
249 Insert_Row
250 ( p_change_line_rec => l_change_line_rec
251 , p_change_line_unexp_rec => l_change_line_unexp_rec
252 , x_return_status => l_temp_return_status
253 , x_mesg_token_tbl => l_temp_mesg_token_tbl
254 ) ;
255
256 IF l_temp_return_status <> FND_API.G_RET_STS_SUCCESS
257 THEN
258 l_return_status := l_temp_return_status ;
259 l_mesg_token_Tbl := l_temp_Mesg_Token_Tbl ;
260 END IF ;
261
262 ELSIF l_change_line_rec.transaction_type = BOM_Globals.G_OPR_UPDATE
263 THEN
264
265
266 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
267 ('Change Line: Executing Update Row. . . ') ;
268 END IF ;
269
270 Update_Row
271 ( p_change_line_rec => l_change_line_rec
272 , p_change_line_unexp_rec => l_change_line_unexp_rec
273 , x_return_status => l_temp_return_status
274 , x_mesg_token_tbl => l_temp_mesg_token_tbl
275 ) ;
276
277 IF l_temp_return_status <> FND_API.G_RET_STS_SUCCESS
278 THEN
279 l_return_status := l_temp_return_status ;
280 l_mesg_token_Tbl := l_temp_Mesg_Token_Tbl ;
281 END IF ;
282
283
284
285 ELSIF l_change_line_rec.transaction_type = BOM_Globals.G_OPR_DELETE
286 THEN
287
288 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
289 ('Change Line: Executing Delete Row. . . ') ;
290 END IF ;
291
292 Delete_Row
293 ( p_change_line_rec => l_change_line_rec
294 , p_change_line_unexp_rec => l_change_line_unexp_rec
295 , x_return_status => l_temp_return_status
296 , x_mesg_token_tbl => l_temp_mesg_token_tbl
297 ) ;
298
299 IF l_temp_return_status <> FND_API.G_RET_STS_SUCCESS
300 THEN
301 l_return_status := l_temp_return_status ;
302 l_mesg_token_Tbl := l_temp_Mesg_Token_Tbl ;
303 END IF ;
304
305 END IF ;
306
307 --
308 -- Return Status
309 --
310 x_return_status := l_return_status ;
311 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl ;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315
316 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
317 ('Some unknown error in Perform Writes . . .' || SQLERRM );
318 END IF ;
319
320 l_err_text := G_PKG_NAME || ' Utility (Perform Writes) '
321 || substrb(SQLERRM,1,200);
322
323
324 Error_Handler.Add_Error_Token
325 ( p_message_name => NULL
326 , p_message_text => l_err_text
327 , p_mesg_token_tbl => l_mesg_token_tbl
328 , x_mesg_token_tbl => l_mesg_token_tbl
329 ) ;
330
331 -- Return the status and message table.
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
333 x_mesg_token_tbl := l_mesg_token_tbl ;
334
335 END Perform_Writes;
336
337
338
339 /*****************************************************************************
340 * Procedure :
341 * Parameters IN : Change Line exposed column record
342 * Change Line unexposed column record
343 * Parameters OUT: Return Status
344 * Message Token Table
345 * Purpose : This procedure will insert a record in the Change Line
346 * table: ENG_CHANGE_LINES/_TL.
347 *
348 *****************************************************************************/
349 PROCEDURE Insert_Row
350 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
351 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
352 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
353 , x_return_status OUT NOCOPY VARCHAR2
354 )
355 IS
356
357 -- Error Handlig Variables
358 l_return_status VARCHAR2(1);
359 l_err_text VARCHAR2(2000) ;
360 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
361
362 Cursor c_csr( p_change_notice VARCHAR2
363 , p_organization_id NUMBER
364 )
365 IS
366 SELECT change_id FROM eng_engineering_changes
367 WHERE change_notice = p_change_notice and organization_id = p_organization_id;
368
369 l_change_id NUMBER;
370 BEGIN
371
372 l_return_status := FND_API.G_RET_STS_SUCCESS ;
373 x_return_status := FND_API.G_RET_STS_SUCCESS ;
374
375 l_change_id := p_change_line_unexp_rec.change_id;
376 IF l_change_id IS NULL THEN
377 IF NOT c_csr %ISOPEN
378 THEN
379 OPEN c_csr( p_change_line_rec.eco_name
380 , p_change_line_unexp_rec.organization_id ) ;
381 END IF ;
382
383 FETCH c_csr INTO l_change_id;
384 END IF;
385
386 INSERT INTO ENG_CHANGE_LINES(
387 change_line_id
388 , change_id
389 , change_type_id
390 , status_code
391 , sequence_number
392 , need_by_date
393 , scheduled_date
394 , implementation_date
395 , cancelation_date
396 , assignee_id
397 , object_id
398 , pk1_value
399 , pk2_value
400 , pk3_value
401 , pk4_value
402 , pk5_value
403 , last_update_date
404 , last_updated_by
405 , last_update_login
406 , creation_date
407 , created_by
408 , request_id
409 , program_application_id
410 , program_id
411 , program_update_date
412 , original_system_reference
413 , Approval_Status_Type
414 , Required_Flag
415 , Complete_Before_Status_Code
416 , Start_After_Status_Code
417 )
418 VALUES (
419 p_change_line_unexp_rec.change_line_id
420 , l_change_id
421 , p_change_line_unexp_rec.change_type_id
422 , p_change_line_unexp_rec.status_code
423 , p_change_line_rec.sequence_number
424 , p_change_line_rec.need_by_date
425 , p_change_line_rec.scheduled_date
426 , p_change_line_rec.implementation_date
427 , p_change_line_rec.cancelation_date
428 , p_change_line_unexp_rec.assignee_id
429 , p_change_line_unexp_rec.object_id
430 , p_change_line_unexp_rec.pk1_value
431 , p_change_line_unexp_rec.pk2_value
432 , p_change_line_unexp_rec.pk3_value
433 , p_change_line_unexp_rec.pk4_value
434 , p_change_line_unexp_rec.pk5_value
435 , SYSDATE -- Last Update Date
436 , BOM_Globals.Get_User_Id -- Last Updated By
437 , BOM_Globals.Get_Login_Id -- Last Update Login
438 , SYSDATE -- Creation Date
439 , BOM_Globals.Get_User_Id -- Created By
440 , NULL -- Request Id
441 , BOM_Globals.Get_Prog_AppId -- Application Id
442 , BOM_Globals.Get_Prog_Id -- Program Id
443 , SYSDATE -- program_update_date
444 , p_change_line_rec.original_system_reference
445 , p_change_line_unexp_rec.Approval_Status_Type --Added as it is mandatory 18-6-2003
446 ,p_change_line_rec. Required_Flag
447 , p_change_line_rec.Complete_Before_Status_Code
448 ,p_change_line_rec. Start_After_Status_Code
449 );
450
451
452 INSERT INTO ENG_CHANGE_LINES_TL (
453 change_line_id
454 , language
455 , source_lang
456 , created_by
457 , creation_date
458 , last_update_date
459 , last_updated_by
460 , last_update_login
461 , name
462 , description
463 )
464 SELECT p_change_line_unexp_rec.change_line_id
465 , lang.language_code
466 , USERENV('LANG')
467 , BOM_Globals.Get_User_Id
468 , SYSDATE
469 , SYSDATE
470 , BOM_Globals.Get_User_Id
471 , BOM_Globals.Get_Login_Id
472 , p_change_line_rec.name
473 , p_change_line_rec.description
474 FROM FND_LANGUAGES lang
475 WHERE lang.installed_flag in ('I', 'B')
476 AND NOT EXISTS ( SELECT NULL
477 FROM ENG_CHANGE_LINES_TL tl
478 WHERE tl.change_line_id = p_change_line_unexp_rec.change_line_id
479 AND tl.language = lang.language_code
480 );
481
482
483 IF BOM_Globals.get_debug = 'Y'
484 THEN
485 error_handler.write_debug('Change Line: '|| to_char(p_change_line_unexp_rec.change_line_id)
486 ||' has been created. ' );
487 END IF;
488
489 -- update IM TEXT Table
490
491 BEGIN
492
493 ENG_CHANGE_TEXT_UTIL.Insert_Update_Change ( p_change_id => l_change_id );
494
495 EXCEPTION
496
497 WHEN others THEN
498
499 l_err_text := 'Error in ' || G_PKG_NAME || ' at ENG_CHANGE_TEXT_UTIL.Insert_Update_Change ';
500 END;
501
502 EXCEPTION
503
504 WHEN OTHERS THEN
505
506
507 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
508 ('Unexpected Error occured in Insert . . .' || SQLERRM);
509 END IF;
510
511 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
512 THEN
513 l_err_text := G_PKG_NAME || ' : Utility (Change Line Insert) ' ||
514 SUBSTR(SQLERRM, 1, 200);
515
516 Error_Handler.Add_Error_Token
517 ( p_message_name => NULL
518 , p_message_text => l_err_text
519 , p_mesg_token_tbl => l_mesg_token_tbl
520 , x_mesg_token_tbl => l_mesg_token_tbl
521 ) ;
522 END IF ;
523
524 -- Return the status and message table.
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526 x_mesg_token_tbl := l_mesg_token_tbl ;
527
528 END Insert_Row ;
529
530
531 /***************************************************************************
532 * Procedure : Update_Row
533 * Parameters IN : Change Line exposed column record
534 * Change Line unexposed column record
535 * Parameters OUT: Return Status
536 * Message Token Table
537 * Purpose : Update_Row procedure will update the production record with
538 * the user given values. Any errors will be returned by filling
539 * the Mesg_Token_Tbl and setting the return_status.
540 ****************************************************************************/
541 PROCEDURE Update_Row
542 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
543 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
544 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
545 , x_return_status OUT NOCOPY VARCHAR2
546 )
547 IS
548
549 -- Error Handlig Variables
550 l_return_status VARCHAR2(1);
551 l_err_text VARCHAR2(2000) ;
552 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
553 l_Token_Tbl Error_Handler.Token_Tbl_Type;
554
555 BEGIN
556
557 l_return_status := FND_API.G_RET_STS_SUCCESS ;
558 x_return_status := FND_API.G_RET_STS_SUCCESS ;
559
560 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing update change line . . .') ;
561 END IF ;
562
563 UPDATE ENG_CHANGE_LINES
564 SET last_update_date = SYSDATE
565 , last_updated_by = BOM_Globals.Get_User_Id
566 , last_update_login = BOM_Globals.Get_Login_Id
567 , change_type_id = p_change_line_unexp_rec.change_type_id
568 , status_code = p_change_line_unexp_rec.status_code
569 , sequence_number = p_change_line_rec.sequence_number
570 , need_by_date = p_change_line_rec.need_by_date
571 , scheduled_date = p_change_line_rec.scheduled_date
572 , implementation_date = p_change_line_rec.implementation_date
573 , cancelation_date = p_change_line_rec.cancelation_date
574 , assignee_id = p_change_line_unexp_rec.assignee_id
575 , object_id = p_change_line_unexp_rec.object_id
576 , pk1_value = p_change_line_unexp_rec.pk1_value
577 , pk2_value = p_change_line_unexp_rec.pk2_value
578 , pk3_value = p_change_line_unexp_rec.pk3_value
579 , pk4_value = p_change_line_unexp_rec.pk4_value
580 , pk5_value = p_change_line_unexp_rec.pk5_value
581 , original_system_reference = p_change_line_rec.original_system_reference
582 WHERE change_line_id = p_change_line_unexp_rec.change_line_id ;
583
584 UPDATE ENG_CHANGE_LINES_TL
585 SET last_update_date = SYSDATE
586 , last_updated_by = BOM_Globals.Get_User_Id
587 , last_update_login = BOM_Globals.Get_Login_Id
588 , name = p_change_line_rec.name
589 , description = p_change_line_rec.description
590 WHERE change_line_id = p_change_line_unexp_rec.change_line_id
591 AND USERENV('LANG') = language;
592
593
594
595 EXCEPTION
596 WHEN OTHERS THEN
597
598 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
599 ('Unexpected Error occured in Update . . .' || SQLERRM);
600 END IF;
601
602 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
603 THEN
604 l_err_text := G_PKG_NAME || ' : Utility (Chage Line Update) ' ||
605 SUBSTR(SQLERRM, 1, 200);
606
607 Error_Handler.Add_Error_Token
608 ( p_message_name => NULL
609 , p_message_text => l_err_text
610 , p_mesg_token_tbl => l_mesg_token_tbl
611 , x_mesg_token_tbl => l_mesg_token_tbl
612 ) ;
613 END IF ;
614
615 -- Return the status and message table.
616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
617 x_mesg_token_tbl := l_mesg_token_tbl ;
618
619 END Update_Row ;
620
621
622 /********************************************************************
623 * Procedure : Delete_Row
624 * Parameters IN : Change Line exposed column record
625 * Change Line unexposed column record
626 * Parameters OUT: Return Status
627 * Message Token Table
628 * Purpose : procedure will delete a change line record.
629 *********************************************************************/
630 PROCEDURE Delete_Row
631 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
632 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
633 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
634 , x_return_status OUT NOCOPY VARCHAR2
635 )
636 IS
637
638 l_change_line_rec Eng_Eco_Pub.Change_Line_Rec_Type ;
639 l_change_line_unexp_rec Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type ;
640
641 -- Error Handlig Variables
642 l_return_status VARCHAR2(1);
643 l_err_text VARCHAR2(2000) ;
644 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
645 l_Token_Tbl Error_Handler.Token_Tbl_Type;
646
647
648 BEGIN
649 l_return_status := FND_API.G_RET_STS_SUCCESS ;
650 x_return_status := FND_API.G_RET_STS_SUCCESS ;
651
652 --
653 -- Initialize Common Record and Status
654 --
655 l_change_line_rec := p_change_line_rec ;
656 l_change_line_unexp_rec := p_change_line_unexp_rec ;
657
658
659 -- Need to Delete Attachments ?
660
661 -- Need to Delete Associations ?
662
663
664 DELETE FROM ENG_CHANGE_LINES
665 WHERE change_line_id = l_change_line_unexp_rec.change_line_id ;
666
667
668 DELETE FROM ENG_CHANGE_LINES_TL
669 WHERE change_line_id = l_change_line_unexp_rec.change_line_id ;
670
671 -- Return the status and message table.
672 x_return_status := l_return_status;
673 x_mesg_token_tbl := l_mesg_token_tbl;
674
675
676 EXCEPTION
677
678 WHEN OTHERS THEN
679
680 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
681 ('Unexpected Error occured in Delete . . .' || SQLERRM);
682 END IF;
683
684 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
685 THEN
686 l_err_text := G_PKG_NAME || ' : Utility (Change Line Delete) ' ||
687 SUBSTR(SQLERRM, 1, 200);
688 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
689
690 Error_Handler.Add_Error_Token
691 ( p_message_name => NULL
692 , p_message_text => l_err_text
693 , p_mesg_token_tbl => l_mesg_token_tbl
694 , x_mesg_token_tbl => l_mesg_token_tbl
695 ) ;
696 END IF ;
697
698 -- Return the status and message table.
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
700 x_mesg_token_tbl := l_mesg_token_tbl ;
701
702 END Delete_Row ;
703
704
705
706 PROCEDURE Change_Subjects (
707 p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
708 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
709 , x_change_subject_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Change_Subject_Unexp_Rec_Type
710 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
711 , x_return_status IN OUT NOCOPY VARCHAR2)
712 IS
713
714 cursor Getsubject (p_change_type_id in NUMBER) is
715 select ect.type_name ,ect.subject_id ,ese.entity_name,ese.parent_entity_name from
716 eng_change_order_types_vl ect ,eng_subject_entities ese
717 where ect.subject_id =ese.subject_id
718 and change_order_type_id =p_change_type_id
719 and subject_level=1 ;
720
721 /*cursor getlifecycleid (item_id NUMBER ,revision VARCHAR2 , l_org_id NUMBER) is
722 SELECT LP.PROJ_ELEMENT_ID -- into l_current_lifecycle_id
723 FROM PA_EGO_LIFECYCLES_PHASES_V LP, MTL_ITEM_REVISIONS MIR
724 WHERE LP.PROJ_ELEMENT_ID = MIR.CURRENT_PHASE_ID
725 AND MIR.INVENTORY_ITEM_ID = item_id
726 AND MIR.ORGANIZATION_ID = l_org_id
727 AND MIR.REVISION = revision;
728 */ -- Commented by lkasturi
729
730 cursor getcataloggroupid(item_id NUMBER, l_org_id NUMBER) is
731 SELECT ITEM_CATALOG_GROUP_ID
732 from mtl_system_items msi
733 where msi.INVENTORY_ITEM_ID = item_id
734 AND msi.ORGANIZATION_ID = l_org_id;
735
736 subject_type Getsubject%ROWTYPE;
737 l_entity_name VARCHAR2(30);
738 l_parent_entity_name VARCHAR2(30);
739 l_item_catalog_group_id NUMBER;
740 l_subject_id NUMBER;
741 l_change_subject_unexp_rec Eng_Eco_Pub.Change_Subject_Unexp_Rec_Type;
742
743 l_user_id NUMBER;
744 l_login_id NUMBER;
745 l_prog_appid NUMBER;
746 l_prog_id NUMBER;
747 l_request_id NUMBER;
748 l_return_status VARCHAR2(1);
749 l_org_id NUMBER;
750 l_rev_id NUMBER;
751 l_inv_item_id NUMBER;
752 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
753 l_Token_Tbl Error_Handler.Token_Tbl_Type;
754 l_err_text VARCHAR2(2000);
755
756 BEGIN
757
758 l_return_status := FND_API.G_RET_STS_SUCCESS;
759
760 l_user_id := Eng_Globals.Get_User_Id;
761 l_login_id := Eng_Globals.Get_Login_Id;
762 l_request_id := ENG_GLOBALS.Get_request_id;
763 l_prog_appid := ENG_GLOBALS.Get_prog_appid;
764 l_prog_id := ENG_GLOBALS.Get_prog_id;
765
766 OPEN Getsubject (p_change_line_Unexp_Rec.Change_Type_Id);
767 FETCH Getsubject INTO subject_type;
768 CLOSE Getsubject;
769 l_entity_name := subject_type.entity_name;
770 l_subject_id := subject_type.subject_id;
771 l_parent_entity_name := subject_type.parent_entity_name;
772 l_change_subject_unexp_rec.change_line_id := p_change_line_Unexp_Rec.change_line_id;
773 l_change_subject_unexp_rec.ENTITY_NAME := l_entity_name;
774 l_change_subject_unexp_rec.subject_level := 1;
775 l_change_subject_unexp_rec.change_id := p_change_line_Unexp_Rec.change_id;
776
777 l_org_id := p_change_line_Unexp_Rec.organization_id; -- Added for bug 3651713
778
779
780 IF (l_entity_name = 'EGO_ITEM_REVISION')
781 THEN
782 IF p_change_line_rec.pk1_name IS NOT NULL
783 THEN
784 --l_org_id := ENG_Val_To_Id.ORGANIZATION(p_change_line_rec.pk2_name, l_err_text);
785 l_change_subject_unexp_rec.pk2_value := l_org_id;
786 IF (l_org_id IS NOT NULL AND l_org_id <> fnd_api.g_miss_num)
787 THEN
788 l_inv_item_id := ENG_Val_To_Id.revised_item(
789 p_change_line_rec.pk1_name,
790 l_org_id,
791 l_err_text);
792 l_change_subject_unexp_rec.pk1_value := l_inv_item_id;
793 IF l_inv_item_id IS NOT NULL
794 AND l_inv_item_id <> fnd_api.g_miss_num
795 THEN
796 IF p_change_line_rec.pk3_name IS NOT NULL
797 THEN
798 l_rev_id := ENG_Val_To_Id.revised_item_code(
799 l_inv_item_id,
800 l_org_id,
801 p_change_line_rec.pk3_name);
802 l_change_subject_unexp_rec.pk3_value := l_rev_id;
803 IF (l_rev_id IS NOT NULL AND l_rev_id <> fnd_api.g_miss_num)
804 THEN
805 l_return_status := 'S'; --FND_API.G_RET_STS_SUCCESS;
806 ELSE
807 l_token_tbl(1).token_name := 'CHANGE_LINE_TYPE';
808 l_token_tbl(1).token_value := p_change_line_rec.change_type_code;
809 error_handler.add_error_token(
810 p_message_name => 'ENG_PK3_NAME_INVALID',
811 p_mesg_token_tbl => l_mesg_token_tbl,
812 x_mesg_token_tbl => l_mesg_token_tbl,
813 p_token_tbl => l_token_tbl);
814 l_return_status := FND_API.G_RET_STS_ERROR;
815 END IF; --end of l_rev_id IS NOT NULL
816 END IF; -- end of pk3_name is not null
817 ELSE
818 l_token_tbl(1).token_name := 'CHANGE_LINE_TYPE';
819 l_token_tbl(1).token_value := p_change_line_rec.change_type_code;
820 error_handler.add_error_token (
821 p_message_name => 'ENG_PK1_NAME_INVALID',
822 p_mesg_token_tbl => l_mesg_token_tbl,
823 x_mesg_token_tbl => l_mesg_token_tbl,
824 p_token_tbl => l_token_tbl );
825 l_return_status := FND_API.G_RET_STS_ERROR;
826 END IF; -- l_inv_item_id IS NOT NULL
827 ELSE
828 l_token_tbl(1).token_name := 'CHANGE_LINE_TYPE';
829 l_token_tbl(1).token_value := p_change_line_rec.change_type_code;
830 error_handler.add_error_token (
831 p_message_name => 'ENG_PK2_NAME_INVALID',
832 p_mesg_token_tbl => l_mesg_token_tbl,
833 x_mesg_token_tbl => l_mesg_token_tbl,
834 p_token_tbl => l_token_tbl );
835 l_return_status := FND_API.G_RET_STS_ERROR;
836 END IF; --l_org_id IS NOT NULL
837 END IF; -- p_eco_rec.Pk1_Name is not null
838 ELSIF l_entity_name = 'EGO_ITEM'
839 THEN
840 --For Item PK1_NAME,PK2_NAME Columns are mandatory
841 IF p_change_line_rec.pk1_name IS NOT NULL
842 THEN
843 --l_org_id := ENG_Val_To_Id.ORGANIZATION(p_change_line_rec.pk2_name, l_err_text);
844 l_change_subject_unexp_rec.pk2_value := l_org_id;
845 IF (l_org_id IS NOT NULL AND l_org_id <> FND_API.G_MISS_NUM)
846 THEN
847 l_rev_id := ENG_Val_To_Id.revised_item (p_change_line_rec.pk1_name,
848 l_org_id,
849 l_err_text);
850 l_change_subject_unexp_rec.pk1_value := l_rev_id;
851 IF (l_rev_id IS NOT NULL AND l_rev_id <> FND_API.G_MISS_NUM)
852 THEN
853 l_return_status := 'S';
854 ELSE
855 l_token_tbl(1).token_name := 'CHANGE_LINE_TYPE';
856 l_token_tbl(1).token_value := p_change_line_rec.change_type_code;
857 error_handler.add_error_token (
858 p_message_name => 'ENG_PK1_NAME_INVALID',
859 p_mesg_token_tbl => l_mesg_token_tbl,
860 x_mesg_token_tbl => l_mesg_token_tbl,
861 p_token_tbl => l_token_tbl );
862 l_return_status := FND_API.G_RET_STS_ERROR;
863 END IF; --l_rev_id IS NOT NULL
864 ELSE
865 l_token_tbl(1).token_name := 'CHANGE_LINE_TYPE';
866 l_token_tbl(1).token_value := p_change_line_rec.change_type_code;
867 error_handler.add_error_token (
868 p_message_name => 'ENG_PK2_NAME_INVALID',
869 p_mesg_token_tbl => l_mesg_token_tbl,
870 x_mesg_token_tbl => l_mesg_token_tbl,
871 p_token_tbl => l_token_tbl );
872 l_return_status := FND_API.G_RET_STS_ERROR;
873 END IF; --l_org_id IS NOT NULL
874 END IF; -- p_eco_rec.Pk1_Name is not null
875 END IF; --End Of If of check for l_entity_name
876
877 IF l_return_status = 'S'
878 THEN
879 --
880 -- Bug 3311072: Change the query to select item phase
881 -- Added By LKASTURI
882 --
883 IF (l_change_subject_unexp_rec.pk1_value IS NOT NULL AND
884 l_change_subject_unexp_rec.pk2_value IS NOT NULL)
885 THEN
886 BEGIN
887 SELECT CURRENT_PHASE_ID
888 INTO l_change_subject_unexp_rec.lifecycle_state_id
889 FROM MTL_System_items_vl
890 WHERE INVENTORY_ITEM_ID = l_change_subject_unexp_rec.pk1_value
891 AND ORGANIZATION_ID = l_change_subject_unexp_rec.pk2_value;
892 EXCEPTION
893 WHEN NO_DATA_FOUND THEN
894 l_change_subject_unexp_rec.lifecycle_state_id := null;
895 WHEN TOO_MANY_ROWS THEN
896 l_change_subject_unexp_rec.lifecycle_state_id := null;
897 END;
898 ELSE
899 l_change_subject_unexp_rec.lifecycle_state_id := null;
900 END IF;
901
902 -- End Changes
903
904 IF p_change_line_rec.transaction_type = Eng_Globals.G_OPR_CREATE
905 THEN
906 Insert into eng_change_subjects
907 (CHANGE_SUBJECT_ID,
908 CHANGE_ID,
909 CHANGE_LINE_ID,
910 ENTITY_NAME,
911 PK1_VALUE,
912 PK2_VALUE,
913 PK3_VALUE,
914 PK4_VALUE,
915 PK5_VALUE,
916 SUBJECT_LEVEL,
917 LIFECYCLE_STATE_ID,
918 LAST_UPDATE_DATE,
919 LAST_UPDATED_BY,
920 CREATION_DATE,
921 CREATED_BY,
922 LAST_UPDATE_LOGIN,
923 REQUEST_ID,
924 PROGRAM_ID,
925 PROGRAM_APPLICATION_ID,
926 PROGRAM_UPDATE_DATE)
927 values
928 (eng_change_subjects_s.nextval,
929 l_change_subject_unexp_rec.change_id,
930 l_change_subject_unexp_rec.change_line_id,
931 l_change_subject_unexp_rec.entity_name,
932 l_change_subject_unexp_rec.pk1_value,
933 l_change_subject_unexp_rec.pk2_value,
934 l_change_subject_unexp_rec.pk3_value,
935 l_change_subject_unexp_rec.pk4_value,
936 l_change_subject_unexp_rec.pk5_value,
937 l_change_subject_unexp_rec.subject_level,
938 l_change_subject_unexp_rec.lifecycle_state_id,
939 SYSDATE,
940 l_User_Id,
941 SYSDATE,
942 l_User_Id,
943 l_Login_Id,
944 l_request_id,
945 l_prog_appid,
946 l_prog_id,sysdate);
947
948 IF l_parent_entity_name = 'EGO_ITEM'
949 THEN
950 Insert into eng_change_subjects
951 (CHANGE_SUBJECT_ID,
952 CHANGE_ID,
953 CHANGE_LINE_ID,
954 ENTITY_NAME,
955 PK1_VALUE,
956 PK2_VALUE,
957 PK3_VALUE,
958 PK4_VALUE,
959 PK5_VALUE,
960 SUBJECT_LEVEL,
961 LIFECYCLE_STATE_ID,
962 LAST_UPDATE_DATE,
963 LAST_UPDATED_BY,
964 CREATION_DATE,
965 CREATED_BY,
966 LAST_UPDATE_LOGIN,
967 REQUEST_ID,
968 PROGRAM_ID,
969 PROGRAM_APPLICATION_ID,
970 PROGRAM_UPDATE_DATE)
971 values
972 (eng_change_subjects_s.nextval,
973 l_change_subject_unexp_rec.change_id,
974 l_change_subject_unexp_rec.change_line_id,
975 l_parent_entity_name, --l_change_subject_unexp_rec.entity_name,
976 l_change_subject_unexp_rec.pk1_value,
977 l_change_subject_unexp_rec.pk2_value,
978 null,
979 null,
980 null,
981 2,
982 null,
983 SYSDATE,
984 l_User_Id,
985 SYSDATE,
986 l_User_Id,
987 l_Login_Id,
988 l_request_id,
989 l_prog_appid,
990 l_prog_id,
991 sysdate);
992 elsif l_parent_entity_name = 'EGO_CATALOG_GROUP'
993 THEN
994 OPEN getcataloggroupid(l_change_subject_unexp_rec.pk1_value,
995 l_change_subject_unexp_rec.pk2_value);
996 FETCH getcataloggroupid into l_item_catalog_group_id;
997 Insert into eng_change_subjects
998 (CHANGE_SUBJECT_ID,
999 CHANGE_ID,
1000 CHANGE_LINE_ID,
1001 ENTITY_NAME,
1002 PK1_VALUE,
1003 PK2_VALUE,
1004 PK3_VALUE,
1005 PK4_VALUE,
1006 PK5_VALUE,
1007 SUBJECT_LEVEL,
1008 LIFECYCLE_STATE_ID,
1009 LAST_UPDATE_DATE,
1010 LAST_UPDATED_BY,
1011 CREATION_DATE,
1012 CREATED_BY,
1013 LAST_UPDATE_LOGIN,
1014 REQUEST_ID,
1015 PROGRAM_ID,
1016 PROGRAM_APPLICATION_ID,
1017 PROGRAM_UPDATE_DATE)
1018 values
1019 (eng_change_subjects_s.nextval,
1020 l_change_subject_unexp_rec.change_id,
1021 l_change_subject_unexp_rec.change_line_id,
1022 l_parent_entity_name, --l_change_subject_unexp_rec.entity_name,
1023 l_item_catalog_group_id,
1024 null,
1025 null,
1026 null,
1027 null,
1028 2,
1029 null,
1030 SYSDATE,
1031 l_User_Id,
1032 SYSDATE,
1033 l_User_Id,
1034 l_Login_Id,
1035 l_request_id,
1036 l_prog_appid,
1037 l_prog_id,
1038 sysdate);
1039 END IF;
1040 ELSIF p_change_line_rec.transaction_type = Eng_Globals.G_OPR_UPDATE
1041 THEN
1042 UPDATE eng_change_subjects SET
1043 pk1_value = l_change_subject_unexp_rec.pk1_value,
1044 pk2_value = l_change_subject_unexp_rec.pk2_value,
1045 pk3_value = l_change_subject_unexp_rec.pk3_value
1046 WHERE change_id = l_change_subject_unexp_rec.change_id
1047 AND change_line_id = l_change_subject_unexp_rec.change_line_id
1048 AND subject_level = 1;
1049
1050 IF l_parent_entity_name = 'EGO_ITEM'
1051 THEN
1052 UPDATE eng_change_subjects SET
1053 pk1_value = l_change_subject_unexp_rec.pk1_value,
1054 pk2_value = l_change_subject_unexp_rec.pk2_value
1055 WHERE change_id = l_change_subject_unexp_rec.change_id
1056 AND subject_level = 2
1057 AND change_line_id = l_change_subject_unexp_rec.change_line_id;
1058 ELSIF l_parent_entity_name = 'EGO_CATALOG_GROUP'
1059 THEN
1060 OPEN getcataloggroupid(l_change_subject_unexp_rec.pk1_value,
1061 l_change_subject_unexp_rec.pk2_value);
1062 FETCH getcataloggroupid into l_item_catalog_group_id;
1063 UPDATE eng_change_subjects SET
1064 pk1_value = l_item_catalog_group_id
1065 WHERE change_id = l_change_subject_unexp_rec.change_id
1066 AND subject_level = 2
1067 AND change_line_id = l_change_subject_unexp_rec.change_line_id;
1068 END IF;
1069 ELSE
1070 DELETE FROM eng_change_subjects
1071 WHERE change_line_id = l_change_subject_unexp_rec.change_line_id
1072 AND change_id = l_change_subject_unexp_rec.change_id;
1073 END IF; -- if CREATE
1074 END IF; -- if return status is 'S'
1075
1076 x_mesg_token_tbl := l_mesg_token_tbl;
1077 x_return_status := l_return_status;
1078
1079 END Change_Subjects;
1080
1081 -- ****************************************************************** --
1082 -- API name : Get_Concatenated_Subjects --
1083 -- Type : Public --
1084 -- Pre-reqs : None. --
1085 -- Function : Gets the concatenated subject value for display --
1086 -- Parameters : --
1087 -- IN : --
1088 -- p_change_id NUMBER Required --
1089 -- p_change_line_id NUMBER --
1090 -- p_subject_id NUMBER --
1091 -- Version : --
1092 -- Current version 1.0 --
1093 -- Initial version 1.0 --
1094 -- --
1095 -- Notes : None --
1096 -- ****************************************************************** --
1097
1098 FUNCTION Get_Concatenated_Subjects (
1099 p_change_id NUMBER
1100 , p_change_line_id NUMBER
1101 , p_subject_id NUMBER
1102 ) RETURN VARCHAR2
1103 IS
1104
1105 CURSOR c_subject_details IS
1106 SELECT esev.query_object_name, esev.pk1_column_name, esev.pk2_column_name
1107 , esev.pk3_column_name, esev.pk4_column_name, esev.pk5_column_name
1108 , esev.query_column1_name, esev.query_column2_name, esev.query_column3_name
1109 , esev.query_column4_name, esev.query_column5_name
1110 , subs.entity_name, subs.subject_level, subs.pk1_value
1111 , subs.pk2_value, subs.pk3_value, subs.pk4_value, subs.pk5_value
1112 FROM eng_subject_entities_v esev, eng_change_subjects subs
1113 WHERE esev.subject_id = p_subject_id
1114 AND esev.subject_level = subs.subject_level
1115 AND esev.entity_name = subs.entity_name
1116 AND subs.change_id = p_change_id
1117 AND subs.change_line_id = p_change_line_id
1118 ORDER BY subs.subject_level DESC;
1119
1120 l_sql_stmt VARCHAR2(2000);
1121 l_where_clause VARCHAR2(2000);
1122 l_bind_count NUMBER;
1123 l_bind_values DBMS_SQL.VARCHAR2_TABLE;
1124 l_desc_table DBMS_SQL.Desc_Tab;
1125 l_cursor_id NUMBER;
1126 l_dummy NUMBER;
1127 l_value_char VARCHAR2(4000);
1128 l_column_count NUMBER;
1129 l_concat_subject VARCHAR2(2000);
1130 l_subject_name1 VARCHAR2(2000);
1131 l_query_column_cl VARCHAR2(1000);
1132 BEGIN
1133 FOR csd1 in c_subject_details
1134 LOOP
1135 l_sql_stmt := null;
1136 l_bind_count := 0;
1137 l_where_clause := NULL;
1138 IF (csd1.pk1_column_name is not null)
1139 THEN
1140 l_bind_count := l_bind_count+1;
1141 l_bind_values(l_bind_count) := csd1.pk1_value;
1142 l_where_clause := csd1.pk1_column_name || ' = :' || l_bind_count;
1143 END IF;
1144 IF (csd1.pk2_column_name is not null)
1145 THEN
1146 l_bind_count := l_bind_count+1;
1147 l_bind_values(l_bind_count) := csd1.pk2_value;
1148 l_where_clause := l_where_clause ||' AND '|| csd1.pk2_column_name || ' = :' || l_bind_count;
1149 END IF;
1150 IF (csd1.pk3_column_name is not null)
1151 THEN
1152 l_bind_count := l_bind_count+1;
1153 l_bind_values(l_bind_count) := csd1.pk3_value;
1154 l_where_clause := l_where_clause ||' AND '|| csd1.pk3_column_name || ' = :' || l_bind_count;
1155 END IF;
1156 IF (csd1.pk4_column_name is not null)
1157 THEN
1158 l_bind_count := l_bind_count+1;
1159 l_bind_values(l_bind_count) := csd1.pk4_value;
1160 l_where_clause := l_where_clause ||' AND '|| csd1.pk4_column_name || ' = :' || l_bind_count;
1161 END IF;
1162 IF (csd1.pk5_column_name is not null)
1163 THEN
1164 l_bind_count := l_bind_count+1;
1165 l_bind_values(l_bind_count) := csd1.pk5_value;
1166 l_where_clause := l_where_clause ||' AND '|| csd1.pk5_column_name || ' = :' || l_bind_count;
1167 END IF;
1168 IF l_where_clause IS NOT NULL
1169 THEN
1170 l_query_column_cl := NULL;
1171 IF csd1.query_column1_name = 'ALTERNATE_BOM_DESIGNATOR'
1172 THEN
1173 l_query_column_cl := 'NVL(' ||csd1.query_column1_name||', FND_MESSAGE.get_string(''BOM'', ''BOM_PRIMARY'')) '|| csd1.query_column1_name;
1174 ELSE
1175 l_query_column_cl := csd1.query_column1_name;
1176 END IF;
1177
1178 l_sql_stmt := 'SELECT '||l_query_column_cl|| ' FROM ' || csd1.query_object_name || ' WHERE ' || l_where_clause ;
1179 l_cursor_id := DBMS_SQL.Open_Cursor;
1180 DBMS_SQL.Parse(l_cursor_id, l_sql_stmt, DBMS_SQL.Native);
1181 DBMS_SQL.Describe_Columns(l_cursor_id, l_column_count, l_desc_table);
1182 FOR i IN 1..l_column_count
1183 LOOP
1184 DBMS_SQL.Define_Column(l_cursor_id, i, l_value_char, 1000);
1185 END LOOP;
1186 FOR l_bind_index IN 1..l_bind_count
1187 LOOP
1188 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':'||l_bind_index, l_bind_values(l_bind_index));
1189 END LOOP;
1190 l_dummy := DBMS_SQL.Execute(l_cursor_id);
1191 IF (DBMS_SQL.Fetch_Rows(l_cursor_id) > 0)
1192 THEN
1193 dbms_sql.column_value(l_cursor_id, 1, l_subject_name1);
1194 IF l_concat_subject IS NOT NULL
1195 THEN
1196 l_concat_subject := l_concat_subject || ' > ';
1197 END IF;
1198 l_concat_subject := l_concat_subject || l_subject_name1;
1199 END IF;
1200 END IF;
1201 dbms_sql.close_cursor(l_cursor_id);
1202 END LOOP;
1203 RETURN l_concat_subject;
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 IF dbms_sql.is_open(l_cursor_id)
1207 THEN
1208 dbms_sql.close_cursor(l_cursor_id);
1209 END IF;
1210 RAISE;
1211 END Get_Concatenated_Subjects;
1212
1213 -- Fix for bug no: 6038875
1214 FUNCTION Get_Concatenated_Subjects_URL
1215 ( p_change_id IN NUMBER
1216 , p_change_line_id IN NUMBER
1217 )RETURN VARCHAR2
1218 IS
1219 CURSOR c_subject_details IS
1220 SELECT * FROM(
1221 SELECT subs.entity_name, subs.pk1_value
1222 , REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(eco.entity_url,'pk1Value',subs.pk1_value)
1223 ,'pk2Value', subs.pk2_value)
1224 ,'pk3Value', subs.pk3_value)
1225 ,'pk4Value', subs.pk4_value)
1226 ,'pk1Value', subs.pk5_value) subject_url
1227 FROM ENG_CHANGE_OBJECTS eco, eng_change_subjects subs
1228 WHERE eco.entity_name = subs.entity_name
1229 AND subs.pk1_value IS NOT NULL
1230 AND subs.entity_name <> 'EGO_COMPONENT'
1231 AND subs.change_id = p_change_id
1232 AND subs.change_line_id = p_change_line_id
1233 ORDER BY subs.subject_level)
1234 WHERE ROWNUM=1;
1235
1236 CURSOR c_item_rev_details IS
1237 SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('&inventoryItemId=pk1Value&organizationId=pk2Value&revisionId=pk3Value'
1238 ,'pk1Value',subs.pk1_value)
1239 ,'pk2Value', subs.pk2_value)
1240 ,'pk3Value', subs.pk3_value)
1241 ,'pk4Value', subs.pk4_value)
1242 ,'pk1Value', subs.pk5_value) subject_url
1243 FROM eng_change_subjects subs
1244 WHERE subs.subject_level = 3 and subs.entity_name = 'EGO_ITEM_REVISION'
1245 AND subs.change_id = p_change_id
1246 AND subs.change_line_id = p_change_line_id;
1247
1248 l_subject_url VARCHAR2(4000);
1249 l_csd c_subject_details%ROWTYPE;
1250 l_structure_name VARCHAR2(400);
1251 l_structure_url_part VARCHAR2(4000);
1252
1253 BEGIN
1254 OPEN c_subject_details;
1255 FETCH c_subject_details INTO l_csd;
1256 l_subject_url := l_csd.subject_url;
1257 IF (l_csd.entity_name = 'EGO_STRUCTURE_NAME')
1258 THEN
1259 BEGIN
1260 SELECT Nvl(ALTERNATE_BOM_DESIGNATOR,bom_globals.retrieve_message('BOM','BOM_PRIMARY'))
1261 into l_structure_name
1262 FROM BOM_BILL_OF_MATERIALS
1263 WHERE bill_sequence_id = l_csd.pk1_value;
1264 if (l_structure_name IS NOT NULL) THEN
1265 OPEN c_item_rev_details;
1266 FETCH c_item_rev_details INTO l_structure_url_part;
1267 CLOSE c_item_rev_details;
1268 l_subject_url := 'OA.jsp?OAFunc=EGO_BOM_EXPLOSIONS&structName='||l_structure_name
1269 ||l_structure_url_part;
1270 END IF;
1271 EXCEPTION
1272 WHEN others THEN
1273 OPEN c_item_rev_details;
1274 FETCH c_item_rev_details INTO l_structure_url_part;
1275 CLOSE c_item_rev_details;
1276 l_subject_url:='OA.jsp?OAFunc=EGO_ITEM_OVERVIEW'||l_structure_url_part;
1277 END;
1278 END IF;
1279 CLOSE c_subject_details;
1280 return l_subject_url;
1281 END Get_Concatenated_Subjects_URL;
1282 END ENG_Change_Line_Util ;