[Home] [Help]
PACKAGE BODY: APPS.ENG_VALIDATE_CHANGE_LINE
Source
1 PACKAGE BODY ENG_Validate_Change_Line AS
2 /* $Header: ENGLCHLB.pls 115.10 2003/05/05 06:31:25 akumar noship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- ENGLCHLB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package ENG_Validate_Change_Line
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 -- 13-AUG-2002Check_Entity Masanori Kimizuka Initial Creation
20 --
21 ****************************************************************************/
22
23 G_Pkg_Name VARCHAR2(30) := 'ENG_Validate_Change_Line';
24
25 l_MODEL CONSTANT NUMBER := 1 ;
26 l_OPTION_CLASS CONSTANT NUMBER := 2 ;
27 l_PLANNING CONSTANT NUMBER := 3 ;
28 l_STANDARD CONSTANT NUMBER := 4 ;
29 l_PRODFAMILY CONSTANT NUMBER := 5 ;
30
31
32 /******************************************************************
33 * Procedure : Check_Existence
34 *
35 * Parameters IN : Change Line exposed column record
36 * Change Line unexposed column record
37 * Parameters OUT: Old Change Line exposed column record
38 * Old Change Line unexposed column record
39 * Mesg Token Table
40 * Return Status
41 * Purpose : Check_Existence will query using the primary key
42 * information and return a success if the operation is
43 * CREATE and the record EXISTS or will return an
44 * error if the operation is UPDATE and record DOES NOT
45 * EXIST.
46 * In case of UPDATE if record exists, then the procedure
47 * will return old record in the old entity parameters
48 * with a success status.
49 *********************************************************************/
50 PROCEDURE Check_Existence
51 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
52 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
53 , x_old_change_line_rec IN OUT NOCOPY Eng_Eco_Pub.Change_Line_Rec_Type
54 , x_old_change_line_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
55 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
56 , x_return_status OUT NOCOPY VARCHAR2
57 )
58 IS
59 l_token_tbl Error_Handler.Token_Tbl_Type;
60 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
61 l_return_status VARCHAR2(1);
62
63 BEGIN
64
65 l_Token_Tbl(1).Token_Name := 'LINE_NAME';
66 l_Token_Tbl(1).Token_Value := p_change_line_rec.name ;
67
68 Eng_Change_Line_Util.Query_Row
69 ( p_line_sequence_number => p_change_line_rec.sequence_number
70 , p_organization_id => p_change_line_unexp_rec.organization_id
71 , p_change_notice => p_change_line_rec.eco_name
72 , p_change_line_name => p_change_line_rec.name
73 , p_mesg_token_tbl => l_mesg_token_tbl
74 , x_change_line_rec => x_old_change_line_rec
75 , x_change_line_unexp_rec => x_old_change_line_unexp_rec
76 , x_mesg_token_tbl => l_mesg_token_tbl
77 , x_return_status => l_return_status
78 );
79
80
81 IF l_return_status = Eng_Globals.G_RECORD_FOUND AND
82 p_change_line_rec.transaction_type = BOM_Globals.G_OPR_CREATE
83 THEN
84 Error_Handler.Add_Error_Token
85 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
86 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
87 , p_message_name => 'ENG_CL_ALREADY_EXISTS'
88 , p_token_tbl => l_token_tbl
89 ) ;
90 l_return_status := FND_API.G_RET_STS_ERROR ;
91
92 ELSIF l_return_status = Eng_Globals.G_RECORD_NOT_FOUND AND
93 p_change_line_rec.transaction_type IN
94 (ENG_Globals.G_OPR_UPDATE, ENG_Globals.G_OPR_DELETE )
95 THEN
96 Error_Handler.Add_Error_Token
97 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
98 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
99 , p_message_name => 'ENG_CL_DOESNOT_EXIST'
100 , p_token_tbl => l_token_tbl
101 ) ;
102 l_return_status := FND_API.G_RET_STS_ERROR ;
103
104 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
105 THEN
106 Error_Handler.Add_Error_Token
107 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
108 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
109 , p_message_name => NULL
110 , p_message_text => 'Unexpected error while existence verification of '
111 || 'Change Line Name '
112 || p_change_line_rec.name
113 , p_token_tbl => l_token_tbl
114 ) ;
115 ELSE
116 l_return_status := FND_API.G_RET_STS_SUCCESS;
117 END IF ;
118
119 x_return_status := l_return_status;
120 x_mesg_token_tbl := l_Mesg_Token_Tbl;
121
122 END Check_Existence;
123
124
125 /*****************************************************************
126 * Procedure : Check_Required
127 * Parameters IN : Change Line exposed column record
128 * Paramaters OUT: Return Status
129 * Mesg Token Table
130 * Purpose : Procedure will check if the user has given all the
131 * required columns for the type of operation user is
132 * trying to perform. If the required columns are not
133 * filled in, then the record would get an error.
134 ********************************************************************/
135 PROCEDURE Check_Required
136 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
137 , x_return_status OUT NOCOPY VARCHAR2
138 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
139 )
140 IS
141
142 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
143 l_err_text VARCHAR(2000) ;
144 l_Token_Tbl Error_Handler.Token_Tbl_Type;
145
146 BEGIN
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148 l_Token_Tbl(1).token_name := 'LINE_NAME';
149 l_Token_Tbl(1).token_value := p_change_line_rec.name ;
150
151
152 -- Sequence Number
153 IF ( p_change_line_rec.transaction_type = ENG_Globals.G_OPR_CREATE
154 AND ( p_change_line_rec.sequence_number IS NULL OR
155 p_change_line_rec.sequence_number = FND_API.G_MISS_NUM )
156 )
157 THEN
158
159 Error_Handler.Add_Error_Token
160 ( p_message_name => 'ENG_CL_SEQ_NUM_REQUIRED'
161 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
162 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
163 , p_Token_Tbl => l_Token_Tbl
164 ) ;
165
166 x_return_status := FND_API.G_RET_STS_ERROR ;
167
168
169 END IF ;
170
171 -- Change Type Code
172 IF ( p_change_line_rec.transaction_type = ENG_Globals.G_OPR_CREATE
173 AND ( p_change_line_rec.change_type_code IS NULL OR
174 p_change_line_rec.change_type_code = FND_API.G_MISS_CHAR )
175 --added for bug 2848506 as change type for header level line is not required
176 AND p_change_line_rec.sequence_number > 0
177 )
178 THEN
179
180 Error_Handler.Add_Error_Token
181 ( p_message_name => 'ENG_CL_CHANGE_TYPE_REQUIRED'
182 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
183 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
184 , p_Token_Tbl => l_Token_Tbl
185 ) ;
186
187 x_return_status := FND_API.G_RET_STS_ERROR ;
188
189
190 END IF ;
191
192
193 -- Return the message table.
194 x_mesg_token_tbl := l_Mesg_Token_Tbl ;
195
196
197 EXCEPTION
198 WHEN OTHERS THEN
199
200 l_err_text := G_PKG_NAME || ' Validation (Check Required) '
201 || substrb(SQLERRM,1,200);
202 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
203
204 Error_Handler.Add_Error_Token
205 ( p_message_name => NULL
206 , p_message_text => l_err_text
207 , p_mesg_token_tbl => l_mesg_token_tbl
208 , x_mesg_token_tbl => l_mesg_token_tbl
209 ) ;
210
211 -- Return the status and message table.
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
213 x_mesg_token_tbl := l_mesg_token_tbl ;
214
215 END Check_Required ;
216
217
218 /***************************************************************
219 * Procedure : Check_Attribute (Validation) for CREATE and UPDATE
220 * Parameters IN : Change Line exposed column record
221 * Change Line unexposed column record
222 * Parameters OUT: Return Status
223 * Message Token Table
224 * Purpose : Attribute validation procedure will validate each
225 * attribute of change line in its entirety. If
226 * the validation of a column requires looking at some
227 * other columns value then the validation is done at
228 * the Entity level instead.
229 * All errors in the attribute validation are accumulated
230 * before the procedure returns with a Return_Status
231 * of 'E'.
232 *********************************************************************/
233 PROCEDURE Check_Attributes
234 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
235 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
236 , p_old_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
237 , p_old_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
238 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
239 , x_return_status OUT NOCOPY VARCHAR2
240 )
241 IS
242
243 l_return_status VARCHAR2(1);
244 l_err_text VARCHAR2(2000) ;
245 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
246 l_Token_Tbl Error_Handler.Token_Tbl_Type;
247
248 BEGIN
249
250 l_return_status := FND_API.G_RET_STS_SUCCESS;
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252
253 -- Set the first token to be equal to the line name
254 l_Token_Tbl(1).token_name := 'LINE_NAME';
255 l_Token_Tbl(1).token_value := p_change_line_rec.name ;
256
257 --
258 -- Check if the user is trying to update a record with
259 -- missing value when the column value is required.
260 --
261 IF p_change_line_rec.transaction_type = ENG_Globals.G_OPR_UPDATE
262 THEN
263
264
265 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
266 ('Change Line Attr Validation: Missing Value. . . ' || l_return_status) ;
267 END IF;
268
269 -- Sequence Number
270 IF p_change_line_rec.sequence_number = FND_API.G_MISS_NUM
271 THEN
272 Error_Handler.Add_Error_Token
273 ( p_Message_Name => 'ENG_CL_SEQ_NUM_MISSING'
274 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
275 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
276 , p_Token_Tbl => l_Token_Tbl
277 );
278 l_return_status := FND_API.G_RET_STS_ERROR;
279 END IF ;
280
281
282 -- Change Type Code
283 IF p_change_line_rec.change_type_code = FND_API.G_MISS_CHAR
284 THEN
285 Error_Handler.Add_Error_Token
286 ( p_Message_Name => 'ENG_CL_CHANGE_TYPE_MISSING'
287 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
288 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
289 , p_Token_Tbl => l_Token_Tbl
290 );
291 l_return_status := FND_API.G_RET_STS_ERROR;
292 END IF;
293
294 END IF ;
295
296 --
297 -- Check if the user is trying to create/update a record with
298 -- invalid value.
299 --
300 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
301 ('Change Line Attr Validation: Invalid Value. . . ' || l_return_status) ;
302 END IF;
303
304 -- Sequence Number
305 IF p_change_line_rec.sequence_number IS NOT NULL
306 AND( p_change_line_rec.sequence_number < -1
307 OR p_change_line_rec.sequence_number > 9999
308 )
309 THEN
310
311 Error_Handler.Add_Error_Token
312 ( p_Message_Name => 'ENG_CL_SEQNUM_LESSTHAN_ZERO'
313 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
314 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
315 , p_Token_Tbl => l_Token_Tbl
316 );
317 l_return_status := FND_API.G_RET_STS_ERROR ;
318 END IF;
319
320 --Status Name
321
322 --Start of changes for Bug 2908248
323
324 IF p_change_line_rec.transaction_type = 'CREATE' and
325 (p_change_line_unexp_rec.status_code <> 1 AND p_change_line_unexp_rec.status_code <> 4
326 )
327 THEN
328 l_token_tbl(1).token_name := 'STATUS_NAME';
329 l_token_tbl(1).token_value := p_change_line_rec.Status_Name;
330
331 l_token_tbl(2).token_name :='CL_NAME';
332 l_token_tbl(2).token_value := p_change_line_rec.Name;
333
334 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
335 THEN
336 Error_Handler.Add_Error_Token
337 ( p_Message_Name => 'ENG_CL_CREATE_STAT_INVALID'
338 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
339 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
340 , p_Token_Tbl => l_Token_Tbl
341 );
342 END IF;
343 l_return_status := FND_API.G_RET_STS_ERROR;
344 l_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
345
346 END IF;
347
348 IF p_change_line_rec.transaction_type = 'UPDATE' and
349 ( p_change_line_unexp_rec.status_code <> 1 AND p_change_line_unexp_rec.status_code <> 4 AND p_change_line_unexp_rec.status_code <> 11
350 AND p_change_line_unexp_rec.status_code <> 5)
351 THEN
352 l_token_tbl(1).token_name := 'STATUS_NAME';
353 l_token_tbl(1).token_value := p_change_line_rec.Status_Name;
354
355 l_token_tbl(2).token_name :='CL_NAME';
356 l_token_tbl(2).token_value := p_change_line_rec.Name;
357
358 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
359 THEN
360 Error_Handler.Add_Error_Token
361 ( p_Message_Name => 'ENG_CL_CREATE_STAT_INVALID'
362 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
363 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
364 , p_Token_Tbl => l_Token_Tbl
365 );
366 END IF;
367 l_return_status := FND_API.G_RET_STS_ERROR;
368 l_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
369 END IF;
370
371 --End of changes for Bug 2908248
372
373 -- Done validating attributes
374 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
375 ('Change Line Attr Validation completed with return_status: ' || l_return_status) ;
376 END IF;
377
378 x_return_status := l_return_status;
379 x_mesg_token_tbl := l_Mesg_Token_Tbl;
380
381 EXCEPTION
382 WHEN OTHERS THEN
383
384 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
385 ('Some unknown error in Attribute Validation . . .' || SQLERRM );
386 END IF ;
387
388
389 l_err_text := G_PKG_NAME || ' Validation (Attr. Validation) '
390 || substrb(SQLERRM,1,200);
391 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
392
393 Error_Handler.Add_Error_Token
394 ( p_message_name => NULL
395 , p_message_text => l_err_text
396 , p_mesg_token_tbl => l_mesg_token_tbl
397 , x_mesg_token_tbl => l_mesg_token_tbl
398 ) ;
399
400 -- Return the status and message table.
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
402 x_mesg_token_tbl := l_mesg_token_tbl ;
403
404
405 END Check_Attributes ;
406
407
408 /*****************************************************************
409 * Procedure : Check_Conditionally_Required for Common
410 * Parameters IN : Change Line exposed column record
411 * Change Line Unexposed column record
412 * Paramaters OUT: Return Status
413 * Mesg Token Table
414 * Purpose : Check Conditionally Required Columns
415 *
416 ********************************************************************/
417 PROCEDURE Check_Conditionally_Required
418 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
419 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
420 , x_return_status OUT NOCOPY VARCHAR2
421 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
422 )
423 IS
424
425 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
426 l_err_text VARCHAR(2000) ;
427 l_token_tbl Error_Handler.Token_Tbl_Type;
428
429 BEGIN
430
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432 l_Token_Tbl(1).token_name := 'LINE_NAME';
433 l_Token_Tbl(1).token_value := p_change_line_rec.name ;
434
435 -- Return the message table.
436 x_mesg_token_tbl := l_Mesg_Token_Tbl ;
437
438
439 EXCEPTION
440 WHEN OTHERS THEN
441
442 l_err_text := G_PKG_NAME || ' Validation (Check Conditionally Required) '
443 || substrb(SQLERRM,1,200);
444
445 Error_Handler.Add_Error_Token
446 ( p_message_name => NULL
447 , p_message_text => l_err_text
448 , p_mesg_token_tbl => l_mesg_token_tbl
449 , x_mesg_token_tbl => l_mesg_token_tbl
450 ) ;
451
452 -- Return the status and message table.
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
454 x_mesg_token_tbl := l_mesg_token_tbl ;
455
456 END Check_Conditionally_Required ;
457
458
459
460 /******************************************************************
461 * Procedure : Check_Entity_Delete
462 * Parameters IN : Change Line exposed column record
463 * Change Line unexposed column record
464 * Parameters OUT: Return Status
465 * Message Token Table
466 * Purpose : Check_Entity validate the entity for the correct
467 * business logic to delete the record.
468 **********************************************************************/
469 PROCEDURE Check_Entity_Delete
470 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
471 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
472 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
473 , x_return_status OUT NOCOPY VARCHAR2
474 )
475 IS
476
477 l_return_status VARCHAR2(1);
478 l_err_text VARCHAR2(2000) ;
479 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
480 l_Token_Tbl Error_Handler.Token_Tbl_Type;
481
482
483 BEGIN
484
485 l_return_status := FND_API.G_RET_STS_SUCCESS;
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487
488 -- Set the first token to be equal to the line name
489 l_Token_Tbl(1).token_name := 'LINE_NAME';
490 l_Token_Tbl(1).token_value := p_change_line_rec.name ;
491
492 --
493 -- Check if the user is trying to update a record with
494 -- missing value when the column value is required.
495 --
496 IF p_change_line_rec.transaction_type = ENG_Globals.G_OPR_DELETE
497 THEN
498
499
500 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
501 ('Change Line Entity Validation on Delete . . . ' || l_return_status) ;
502 END IF;
503
504 END IF ;
505
506
507 -- Done validating attributes
508 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
509 ('Change Line Entity Validation on Delete completed with return_status: ' || l_return_status) ;
510 END IF;
511
512 x_return_status := l_return_status;
513 x_mesg_token_tbl := l_Mesg_Token_Tbl;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517
518 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
519 ('Some unknown error in Entity Validation on Delete. . .' || SQLERRM );
520 END IF ;
521
522
523 l_err_text := G_PKG_NAME || ' Validation (Entiy Validation) '
524 || substrb(SQLERRM,1,200);
525 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
526
527 Error_Handler.Add_Error_Token
528 ( p_message_name => NULL
529 , p_message_text => l_err_text
530 , p_mesg_token_tbl => l_mesg_token_tbl
531 , x_mesg_token_tbl => l_mesg_token_tbl
532 ) ;
533
534 -- Return the status and message table.
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
536 x_mesg_token_tbl := l_mesg_token_tbl ;
537
538
539 END Check_Entity_Delete ;
540
541
542
543 /******************************************************************
544 * Procedure : Check_Entity
545 * Parameters IN : Change Line exposed column record
546 * Change Line unexposed column record
547 * Old Change Line exposed column record
548 * Old Change Line unexposed column record
549 * Parameters OUT: Return Status
550 * Message Token Table
551 * Purpose : Check_Entity validate the entity for the correct
552 * business logic. It will verify the values by running
553 * checks on inter-dependent columns.
554 * It will also verify that changes in one column value
555 * does not invalidate some other columns.
556 **********************************************************************/
557 PROCEDURE Check_Entity
558 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
559 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
560 , p_old_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
561 , p_old_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
562 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
563 , x_return_status OUT NOCOPY VARCHAR2
564 )
565 IS
566
567 -- Variables
568 l_bom_item_type NUMBER ; -- Bom_Item_Type of Assembly
569 l_pto_flag CHAR ; -- PTO flag for Assembly
570 l_eng_item_flag CHAR ; -- Is assembly an Engineering Item
571 l_bom_enabled_flag CHAR ; -- Assembly's bom_enabled_flag
572
573 -- Error Handlig Variables
574 l_return_status VARCHAR2(1);
575 l_err_text VARCHAR2(2000) ;
576 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
577 l_token_tbl Error_Handler.Token_Tbl_Type;
578
579 -- Get CL Item Attr. Value
580 CURSOR l_item_cur (p_org_id NUMBER, p_item_id NUMBER) IS
581 SELECT bom_item_type
582 , pick_components_flag
583 , bom_enabled_flag
584 , eng_item_flag
585 FROM MTL_SYSTEM_ITEMS
586 WHERE organization_id = p_org_id
587 AND inventory_item_id = p_item_id
588 ;
589
590
591 -- Check if Seq Num Uniqueness
592 CURSOR l_duplicate_csr( p_change_line_id NUMBER
593 --, p_change_notice VARCHAR2
594 --, p_org_id NUMBER
595 , p_change_id NUMBER
596 , p_seq_num NUMBER )
597 IS
598 SELECT 'Duplicate Seq Num'
599 FROM DUAL
600 WHERE EXISTS (
601 SELECT NULL
602 FROM ENG_CHANGE_LINES
603 WHERE sequence_number = p_seq_num
604 AND change_line_id <> p_change_line_id
605 AND change_id = p_change_id
606 --AND change_notice = p_change_notice
607 --AND organization_id = p_org_id
608 ) ;
609
610
611 BEGIN
612
613 --
614 -- Initialize Common Record and Status
615 --
616
617 l_return_status := FND_API.G_RET_STS_SUCCESS ;
618
619 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
620 ('Performing Change Line Check Entitity Validation . . .') ;
621 END IF ;
622
623
624 --
625 -- Set the 1st token of Token Table to Change Line value
626 --
627 l_Token_Tbl(1).token_name := 'LINE_NAME';
628 l_Token_Tbl(1).token_value := p_change_line_rec.name;
629
630 -- BB start commenting out here
631 /* -- Change Line Item Validation
632 IF p_change_line_unexp_rec.item_id IS NOT NULL
633 THEN
634 -- First Query all the attributes for the Assembly item used Entity Validation
635 FOR l_item_rec IN l_item_cur
636 ( p_org_id => p_change_line_unexp_rec.organization_id
637 , p_item_id => p_change_line_unexp_rec.item_id
638 )
639 LOOP
640
641 l_bom_item_type := l_item_rec.bom_item_type ;
642 l_pto_flag := l_item_rec.pick_components_flag ;
643 l_eng_item_flag := l_item_rec.eng_item_flag ;
644 l_bom_enabled_flag := l_item_rec.bom_enabled_flag ;
645
646 END LOOP ;
647
648
649 --
650 -- Check Item Attributes for Change Line
651 --
652 --
653 -- Verify that the Parent has BOM Enabled
654 --
655 IF l_bom_enabled_flag <> 'Y'
656 THEN
657 l_token_tbl(2).token_name := 'ITEM_NAME';
658 l_token_tbl(2).token_value := p_change_line_rec.item_name;
659
660 Error_Handler.Add_Error_Token
661 ( p_message_name => 'BOM_CL_ITEM_BOM_NOT_ALLOWED'
662 , p_mesg_token_tbl => l_Mesg_Token_Tbl
663 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
664 , p_token_tbl => l_token_tbl
665 );
666
667 l_return_status := FND_API.G_RET_STS_ERROR;
668 END IF ;
669
670 --
671 -- Verify that the Item on CL is not PTO Item
672 -- Routing canot be created for PTO Item
673 */ /*
674 IF l_pto_flag <> 'N'
675 THEN
676 l_token_tbl(2).token_name := 'ITEM_NAME';
677 l_token_tbl(2).token_value := p_change_line_rec.item_name;
678
679 Error_Handler.Add_Error_Token
680 ( p_message_name => 'BOM_CL_ITEM_PTO_ITEM'
681 , p_mesg_token_tbl => l_Mesg_Token_Tbl
682 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
683 , p_token_tbl => l_token_tbl
684 );
685
686 l_return_status := FND_API.G_RET_STS_ERROR;
687 END IF ;
688 */
689 /*
690 --
691 -- Verify that the BOM Item Type is not 3:Planning Item
692 --
693 IF l_bom_item_type = l_PLANNING
694 THEN
695 l_token_tbl(2).token_name := 'ITEM_NAME';
696 l_token_tbl(2).token_value := p_change_line_rec.item_name;
697
698 Error_Handler.Add_Error_Token
699 ( p_message_name => 'BOM_CL_ITEM_PLANNING_ITEM'
700 , p_mesg_token_tbl => l_Mesg_Token_Tbl
701 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
702 , p_token_tbl => l_token_tbl
703 );
704
705 l_return_status := FND_API.G_RET_STS_ERROR;
706
707 END IF ;
708
709 END IF ; -- Change Line Item Validation
710 */
711 -- BB stop commenting here
712
713 --
714 -- Check uniquness of the sequence number.
715 --
716 IF p_change_line_rec.transaction_type = BOM_Globals.G_OPR_CREATE
717 OR ( p_change_line_rec.transaction_type = BOM_Globals.G_OPR_UPDATE
718 AND p_change_line_rec.sequence_number
719 <> p_old_change_line_rec.sequence_number)
720 THEN
721
722 FOR l_duplicate_rec IN l_duplicate_csr
723 ( p_change_line_id => p_change_line_unexp_rec.change_line_id
724 --, p_change_notice => p_change_line_rec.eco_name
725 --, p_org_id => p_change_line_unexp_rec.organization_id
726 , p_change_id => p_change_line_unexp_rec.change_id
727 , p_seq_num => p_change_line_rec.sequence_number )
728 LOOP
729
730 l_token_tbl(2).token_name := 'SEQ_NUM';
731 l_token_tbl(2).token_value := p_change_line_rec.sequence_number;
732
733 Error_Handler.Add_Error_Token
734 ( p_message_name => 'BOM_OP_NOT_UNIQUE'
735 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
736 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
737 , p_Token_Tbl => l_Token_Tbl
738 ) ;
739 l_return_status := FND_API.G_RET_STS_ERROR ;
740
741 END LOOP ;
742
743 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
744 ('Check uniqueness of the sequence number . . . ' || l_return_status) ;
745 END IF ;
746
747 END IF ;
748
749
750 --
751 -- Return Error Status
752 --
753 x_return_status := l_return_status;
754 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
755
756
757 EXCEPTION
758 WHEN OTHERS THEN
759
760 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
761 ('Some unknown error in Entity Validation . . .' || SQLERRM );
762 END IF ;
763
764
765 l_err_text := G_PKG_NAME || ' Validation (Entity Validation) '
766 || substrb(SQLERRM,1,200);
767
768 Error_Handler.Add_Error_Token
769 ( p_message_name => NULL
770 , p_message_text => l_err_text
771 , p_mesg_token_tbl => l_mesg_token_tbl
772 , x_mesg_token_tbl => l_mesg_token_tbl
773 ) ;
774
775 -- Return the status and message table.
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
777 x_mesg_token_tbl := l_mesg_token_tbl ;
778
779 END Check_Entity ;
780
781
782
783 /*************************************************************
784 * Procedure : Check_Access
785 * Parameters IN : Change line record
786 * Change line unexposed record
787 * Parameters OUT: Mesg_Token_Tbl
788 * Return_Status
789 * Purpose : Procedure will verify that the line item
790 * is accessible to the user.
791 ********************************************************************/
792 PROCEDURE Check_Access
793 ( p_change_line_rec IN Eng_Eco_Pub.Change_Line_Rec_Type
794 , p_change_line_unexp_rec IN Eng_Eco_Pub.Change_Line_Unexposed_Rec_Type
795 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type :=
796 Error_Handler.G_MISS_MESG_TOKEN_TBL
797 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
798 , x_Return_Status OUT NOCOPY VARCHAR2
799 )
800 IS
801 BEGIN
802 NULL;
803 END Check_Access;
804
805
806 PROCEDURE Check_Access
807 ( p_change_notice IN VARCHAR2
808 , p_organization_id IN NUMBER
809 , p_item_revision IN VARCHAR2
810 , p_item_name IN VARCHAR2
811 , p_item_id IN NUMBER
812 , p_item_revision_id IN NUMBER
813 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type :=
814 Error_Handler.G_MISS_MESG_TOKEN_TBL
815 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
816 , x_Return_Status OUT NOCOPY VARCHAR2
817 )
818 IS
819 l_Token_Tbl Error_Handler.Token_Tbl_Type;
820 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type :=
821 p_Mesg_Token_Tbl;
822 l_return_status VARCHAR2(1);
823
824 CURSOR c_ItemType IS
825 SELECT bom_item_type
826 FROM MTL_SYSTEM_ITEMS
827 WHERE inventory_item_id = p_item_id
828 AND organization_id = p_organization_id;
829
830
831 BEGIN
832
833 l_return_status := FND_API.G_RET_STS_SUCCESS;
834
835
836 --
837 -- Check that the user has access to the BOM Item Type
838 -- of the revised item
839 --
840 IF BOM_Globals.Get_STD_Item_Access IS NULL AND
841 BOM_Globals.Get_PLN_Item_Access IS NULL AND
842 BOM_Globals.Get_MDL_Item_Access IS NULL
843 THEN
844
845 --
846 -- Get respective profile values
847 --
848 IF NVL(fnd_profile.value('ENG:STANDARD_ITEM_ECN_ACCESS'), 1) = 1
849 THEN
850 BOM_Globals.Set_STD_Item_Access
851 ( p_std_item_access => 4);
852 ELSE
853 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('no access to standard items'); END IF;
854 BOM_Globals.Set_STD_Item_Access
855 (p_std_item_access => NULL);
856 END IF;
857
858 IF fnd_profile.value('ENG:MODEL_ITEM_ECN_ACCESS') = '1'
859 THEN
860 BOM_Globals.Set_MDL_Item_Access
861 ( p_mdl_item_access => 1);
862 BOM_Globals.Set_OC_Item_Access
863 ( p_oc_item_access => 2);
864 ELSE
865 BOM_Globals.Set_MDL_Item_Access
866 ( p_mdl_item_access => NULL);
867 BOM_Globals.Set_OC_Item_Access
868 ( p_oc_item_access => NULL);
869 END IF;
870
871 IF fnd_profile.value('ENG:PLANNING_ITEM_ECN_ACCESS') = '1'
872 THEN
873 BOM_Globals.Set_PLN_Item_Access
874 ( p_pln_item_access => 3);
875 ELSE
876 BOM_Globals.Set_PLN_Item_Access
877 ( p_pln_item_access => NULL);
878 END IF;
879 END IF;
880
881
882 FOR item_rec IN c_ItemType
883 LOOP
884 IF item_rec.Bom_Item_Type = 5
885 THEN
886 Error_Handler.Add_Error_Token
887 ( p_Message_Name => 'ENG_CL_ITEM_PROD_FAMILY'
888 , p_Mesg_Token_Tbl => l_mesg_token_tbl
889 , x_Mesg_Token_Tbl => l_mesg_token_tbl
890 , p_Token_Tbl => l_token_tbl
891 );
892 l_return_status := FND_API.G_RET_STS_ERROR;
893
894 ELSIF item_rec.Bom_Item_Type NOT IN
895 ( NVL(BOM_Globals.Get_STD_Item_Access, 0),
896 NVL(BOM_Globals.Get_PLN_Item_Access, 0),
897 NVL(BOM_Globals.Get_OC_Item_Access, 0) ,
898 NVL(BOM_Globals.Get_MDL_Item_Access, 0)
899 )
900 THEN
901 l_Token_Tbl(2).Token_Name := 'BOM_ITEM_TYPE';
902 l_Token_Tbl(2).Translate := TRUE;
903 IF item_rec.Bom_Item_Type = 1
904 THEN
905 l_Token_Tbl(2).Token_Value := 'ENG_MODEL';
906 ELSIF item_rec.Bom_Item_Type = 2
907 THEN
908 l_Token_Tbl(2).Token_Value:='ENG_OPTION_CLASS';
909 ELSIF item_rec.Bom_Item_Type = 3
910 THEN
911 l_Token_Tbl(2).Token_Value := 'ENG_PLANNING';
912 ELSIF item_rec.Bom_Item_Type = 4
913 THEN
914 l_Token_Tbl(2).Token_Value := 'ENG_STANDARD';
915 END IF;
916
917 Error_Handler.Add_Error_Token
918 ( p_Message_Name => 'ENG_CL_ITEM_ACCESS_DENIED'
919 , p_Mesg_Token_Tbl => l_mesg_token_tbl
920 , x_Mesg_Token_Tbl => l_mesg_token_tbl
921 , p_Token_Tbl => l_token_tbl
922 );
923
924 l_return_status := FND_API.G_RET_STS_ERROR;
925
926 END IF;
927 END LOOP;
928
929 -- If all the access checks are satisfied then return a status of
930 -- success, else return error.
931 --
932 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Revised Item Check Access returning . . . ' || l_return_status);
933 END IF;
934
935 x_Return_Status := l_return_status;
936 x_Mesg_Token_Tbl := l_mesg_token_tbl;
937
938 END Check_Access;
939
940
941 END ENG_Validate_Change_Line ;