[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_RTG_REVISION
Source
1 PACKAGE BODY BOM_Validate_Rtg_Revision AS
2 /* $Header: BOMLRRVB.pls 120.2 2011/05/17 23:54:38 umajumde ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMLRRVB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Validate_Rtg_Revision
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 07-AUG-00 Biao Zhang Initial Creation
21 -- 22-DEC-00 Lochan Narvekar Modification
22 --
23 ****************************************************************************/
24 G_Pkg_Name VARCHAR2(30) := 'BOM_Validate_Rtg_Revision';
25 g_token_tbl Error_Handler.Token_Tbl_Type;
26
27
28 /*******************************************************************
29 * Procedure : Check_Existence
30 * Returns : None
31 * Parameters IN : Rtg Revision Exposed Record
32 * Rtg Revision Unexposed Record
33 * Parameters out: Old Rtg Revision exposed Record
34 * Old Rtg Revision Unexposed Record
35 * Mesg Token Table
36 * Return Status
37 * Purpose : Procedure will query the routing revision
38 * record and return it in old record variables. If the
39 * Transaction Type is Create and the record already
40 * exists the return status would be error or if the
41 * transaction type is Update or Delete and the record
42 * does not exist then the return status would be an
43 * error as well. Mesg_Token_Table will carry the
44 * error messsage and the tokens associated with the
45 * message.
46 *********************************************************************/
47 PROCEDURE Check_Existence
48 ( p_rtg_revision_rec IN Bom_Rtg_Pub.Rtg_Revision_Rec_Type
49 , p_rtg_rev_unexp_rec IN Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
50 , x_old_rtg_revision_rec IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Revision_Rec_Type
51 , x_old_rtg_rev_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
52 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
53 , x_return_status IN OUT NOCOPY VARCHAR2
54 )
55 IS
56 l_token_tbl Error_Handler.Token_Tbl_Type;
57 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
58 l_return_status VARCHAR2(1);
59 BEGIN
60
61 IF BOM_Rtg_Globals.Get_Debug = 'Y'
62 THEN Error_Handler.Write_Debug('Quering Assembly item '
63 || to_char(p_rtg_rev_unexp_rec.assembly_item_id));
64 END IF;
65
66 IF BOM_Rtg_Globals.Get_Debug = 'Y'
67 THEN Error_Handler.Write_Debug(' Org: '
68 || to_char(p_rtg_rev_unexp_rec.organization_id)) ;
69 END IF;
70
71 IF BOM_Rtg_Globals.Get_Debug = 'Y'
72 THEN Error_Handler.Write_Debug('Revision'
73 || p_rtg_revision_rec.revision );
74 END IF;
75
76 Bom_Rtg_Revision_Util.Query_Row
77 ( p_assembly_item_id =>
78 p_rtg_rev_unexp_rec.assembly_item_id
79 , p_organization_id =>
80 p_rtg_rev_unexp_rec.organization_id
81 , p_revision => p_rtg_revision_rec.revision
82 , x_rtg_revision_rec => x_old_rtg_revision_rec
83 , x_rtg_rev_unexp_rec => x_old_rtg_rev_unexp_rec
84 , x_return_status => l_return_status
85 );
86 IF BOM_Rtg_Globals.Get_Debug = 'Y'
87 THEN Error_Handler.Write_Debug('Query Row Returned with : '
88 || l_return_status); END IF;
89
90 IF l_return_status = BOM_Rtg_Globals.G_RECORD_FOUND AND
91 p_rtg_revision_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
92 THEN
93 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
94 l_token_tbl(1).token_value :=
95 p_rtg_revision_rec.assembly_item_name;
96 l_token_tbl(2).token_name := 'REVISION';
97 l_token_tbl(2).token_value :=
98 p_rtg_revision_rec.revision;
99 Error_Handler.Add_Error_Token
100 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
101 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
102 , p_message_name => 'BOM_RTG_REV_ALREADY_EXISTS'
103 , p_token_tbl => l_token_tbl
104 );
105 l_return_status := FND_API.G_RET_STS_ERROR;
106 ELSIF l_return_status = BOM_Rtg_Globals.G_RECORD_NOT_FOUND AND
107 p_rtg_revision_rec.transaction_type IN
108 (BOM_RTG_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
109 THEN
110 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
111 l_token_tbl(1).token_value := p_rtg_revision_rec.assembly_item_name;
112 l_token_tbl(2).token_name := 'REVISION';
113 l_token_tbl(2).token_value := p_rtg_revision_rec.revision;
114 Error_Handler.Add_Error_Token
115 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
116 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
117 , p_message_name => 'BOM_RTG_REV_DOESNOT_EXISTS'
118 , p_token_tbl => l_token_tbl
119 );
120 l_return_status := FND_API.G_RET_STS_ERROR;
121 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
122 THEN
123 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
124 l_token_tbl(1).token_value :=
125 p_rtg_revision_rec.assembly_item_name;
126 l_token_tbl(2).token_name := 'REVISION';
127 l_token_tbl(2).token_value :=
128 p_rtg_revision_rec.revision;
129 Error_Handler.Add_Error_Token
130 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
131 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
132 , p_message_name => NULL
133 , p_message_text =>
134 'Unexpected error while existence verification of ' ||
135 'Assembly item revision'||
136 p_rtg_revision_rec.assembly_item_name
137 , p_token_tbl => l_token_tbl
138 );
139 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
140 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS
141 THEN
142 l_return_status := FND_API.G_RET_STS_SUCCESS;
143 END IF;
144
145 x_return_status := l_return_status;
146 x_mesg_token_tbl := l_mesg_token_tbl;
147 END Check_Existence;
148
149
150 /********************************************************************
151 * Procedure : Check_Attributes
152 * Parameters IN : Revised Item Exposed Column record
153 * Revised Item Unexposed Column record
154 * Old Revised Item Exposed Column record
155 * Old Revised Item unexposed column record
156 * Parameters out: Return Status
157 * Mesg Token Table
158 * Purpose : Check_Attrbibutes procedure will validate every
159 * revised item attrbiute in its entirety.
160 **********************************************************************/
161 PROCEDURE Check_Attributes
162 ( x_return_status IN OUT NOCOPY VARCHAR2
163 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
164 , p_rtg_revision_Rec IN Bom_Rtg_Pub.Rtg_Revision_Rec_Type
165 , p_rtg_rev_unexp_rec IN Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
166 , p_old_rtg_revision_rec IN Bom_Rtg_Pub.Rtg_Revision_Rec_Type
167 , p_old_rtg_rev_unexp_rec IN Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
168 )
169 IS
170 l_err_text VARCHAR2(2000) := NULL;
171 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
172 l_Token_Tbl Error_Handler.Token_Tbl_Type;
173 l_eco VARCHAR2(10):= NULL;
174 CURSOR c_created_by_eco IS
175 SELECT change_notice
176 FROM mtl_rtg_item_revisions
177 WHERE inventory_item_id = p_rtg_rev_unexp_rec.assembly_item_id
178 AND organization_id = p_rtg_rev_unexp_rec.organization_id
179 AND process_revision = p_rtg_revision_rec.revision;
180
181 BEGIN
182
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
186 Error_Handler.Write_Debug(
187 'Within Rtg Revision Check Attributes . . . ');
188 Error_Handler.Write_Debug(
189 'transaction_type:'||p_rtg_revision_rec.transaction_type);
190 Error_Handler.Write_Debug(
191 'change_notice:'||p_rtg_rev_unexp_rec.change_notice);
192 END IF;
193
194 IF p_rtg_revision_rec.transaction_type= BOM_Rtg_Globals.G_OPR_UPDATE
195 THEN
196 open c_created_by_eco;
197 fetch c_created_by_eco into l_eco;
198 close c_created_by_eco;
199 IF l_eco IS NOT NULL AND l_eco <> FND_API.G_MISS_CHAR THEN
200 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
201 Error_Handler.Write_Debug(
202 'Within Rtg Revision Check Attributes 1. . . ');
203 END IF;
204
205 IF p_rtg_revision_rec.start_effective_date IS NOT NULL
206 AND p_rtg_revision_rec.start_effective_date <>
207 FND_API.G_MISS_DATE THEN
208 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
209 Error_Handler.Write_Debug
210 ('Within Rtg Revision Check Attributes 2. . . ');
211 END IF;
212
213 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
214 l_token_tbl(1).token_value :=
215 p_rtg_revision_rec.assembly_item_name;
216 l_token_tbl(2).token_name := 'REVISION';
217 l_token_tbl(2).token_value :=
218 p_rtg_revision_rec.revision;
219 l_token_tbl(2).token_name := 'START_EFFECITVE_DATE';
220 l_token_tbl(2).token_value :=
221 p_rtg_revision_rec.start_effective_date;
222 Error_Handler.Add_Error_Token
223 ( p_message_name =>
224 'BOM_RTG_REV_EFFDT_NT_UPDATABLE'
225 , p_token_tbl => l_token_tbl
226 , p_mesg_token_tbl => l_mesg_token_tbl
227 , x_mesg_token_tbl => l_mesg_token_tbl
228 );
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 END IF;
231 END IF;-- if eco is NOT NULL
232 END IF;
233 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
234
235 END Check_Attributes;
236
237
238 /********************************************************************
239 * Procedure : Check_Entity
240 * Parameters IN : Rtg Revision Exposed column record
241 * Rtg Revision Unexposed column record
242 * Old Rtg Revision exposed column record
243 * Old Rtg Revision unexposed column record
244 * Parameters out: Message Token Table
245 * Return Status
246 * Purpose : This procedure will perform the business logic
247 * validation for the RTG Revision Entity.It will perform
248 * any cross entity validations and make sure that the
249 * user is not entering values which may disturb the
250 * integrity of the data.
251 *********************************************************************/
252 PROCEDURE Check_Entity
253 ( p_rtg_revision_rec IN Bom_Rtg_Pub.Rtg_Revision_Rec_Type
254 , p_rtg_rev_unexp_rec IN Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
255 , p_old_rtg_revision_rec IN Bom_Rtg_Pub.Rtg_Revision_Rec_Type
256 , p_old_rtg_rev_unexp_rec IN Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
257 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
258 , x_return_status IN OUT NOCOPY VARCHAR2
259 )
260 IS
261 CURSOR c_Get_Revision IS
262 SELECT process_revision
263 FROM mtl_rtg_item_revisions
264 WHERE inventory_item_id = p_rtg_rev_unexp_rec.assembly_item_id
265 AND organization_id = p_rtg_rev_unexp_rec.organization_id
266 AND effectivity_date <= sysdate --added by arudresh, bug: 3756380
267 AND IMPLEMENTATION_DATE IS NOT NULL
268 ORDER BY effectivity_date desc, process_revision desc;
269
270 l_current_rev VARCHAR2(3);
271 l_effectivity_date DATE;
272 l_token_tbl Error_Handler.Token_Tbl_Type;
273 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
274 l_dummy NUMBER;
275 BEGIN
276
277 --
278 -- For CREATE type, do the following check.
279 --
280
281 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
282 l_token_tbl(1).token_value := p_rtg_revision_rec.assembly_item_name;
283 l_token_tbl(2).token_name := 'REVISION';
284 l_token_tbl(2).token_value := p_rtg_revision_rec.revision;
285
286
287 IF p_rtg_revision_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
288 THEN
289
290 --
291 -- Revision name is not allowed to use 'Quote'.
292 --
293
294 IF INSTR(p_rtg_revision_Rec.revision, '''') <> 0 THEN
295 x_return_status := FND_API.G_RET_STS_ERROR;
296
297 Error_Handler.Add_Error_Token
298 ( p_mesg_token_tbl => l_mesg_token_tbl
299 ,x_mesg_token_tbl => l_mesg_token_tbl
300 ,p_message_name => 'BOM_RTG_REV_QUOTE_NOT_ALLOWED'
301 ,p_token_tbl => l_token_tbl
302 );
303
304 END IF;
305
306
307 -- Check if the user has entered a revision that
308 -- is greater tha the most current revision. If not then it is an error --
309 IF (nvl(Bom_Globals.get_caller_type(),'') <> 'MIGRATION')
310 --skip this check for migration bug: 3756380
311 THEN
312 OPEN c_Get_Revision;
313 FETCH c_Get_Revision INTO l_current_rev;
314 IF (l_current_rev is not null and
315 p_rtg_revision_rec.revision <= l_current_rev)
316 THEN
317 l_token_tbl(3).token_name := 'CURRENT_REVISION';
318 l_token_tbl(3).token_value := l_current_rev;
319 Error_Handler.Add_Error_Token
320 ( p_message_name => 'BOM_NEXT_REVISION'
321 , p_mesg_token_tbl => l_mesg_token_tbl
322 , x_mesg_token_tbl => l_mesg_token_tbl
323 , p_token_tbl => l_token_tbl
324 );
325 x_return_status := FND_API.G_RET_STS_ERROR;
326 l_token_tbl.delete(3);
327 END IF;
328 END IF;
329
330 --
331 -- If the user is attempting to create a new revision, then the
332 -- routing for the item must exist for the user to be able to
333 -- create a revision through BOM
334 --
335 BEGIN
336 SELECT routing_sequence_id
337 INTO l_dummy
338 FROM bom_operational_routings
339 WHERE assembly_item_id = p_rtg_rev_Unexp_Rec.assembly_item_id
340 AND organization_id = p_rtg_rev_Unexp_Rec.organization_id
341 AND ((alternate_routing_designator is null and p_rtg_revision_rec.alternate_routing_code is null) --modified for 12558505
342 or (alternate_routing_designator = p_rtg_revision_rec.alternate_routing_code));
343 --AND nvl(alternate_routing_designator,'A') =
344 --nvl(p_rtg_revision_rec.alternate_routing_code,'A');
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 x_return_status := FND_API.G_RET_STS_ERROR;
348 l_token_tbl.delete(2) ;
349
350 Error_Handler.Add_Error_Token
351 ( p_mesg_token_tbl => l_mesg_token_tbl
352 ,x_mesg_token_tbl => l_mesg_token_tbl
353 ,p_message_name => 'BOM_RTG_REV_RTG_MISS'
354 ,p_token_tbl => l_token_tbl
355 );
356
357 l_token_tbl(2).token_name := 'REVISION';
358 l_token_tbl(2).token_value := p_rtg_revision_rec.revision;
359
360 END;
361
362 --
363 -- For CREATE, revision must be unique.
364 --
365 BEGIN
366 SELECT 1
367 INTO l_dummy
368 FROM dual
369 WHERE not exists
370 (SELECT 1
371 FROM mtl_rtg_item_revisions
372 WHERE organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
373 AND inventory_item_id = p_rtg_rev_Unexp_Rec.assembly_Item_Id
374 AND process_revision = p_rtg_revision_Rec.revision
375 );
376
377 EXCEPTION
378 WHEN NO_DATA_FOUND then
379 x_return_status := FND_API.G_RET_STS_ERROR;
380
381 Error_Handler.Add_Error_Token
382 ( p_mesg_token_tbl => l_mesg_token_tbl
383 ,x_mesg_token_tbl => l_mesg_token_tbl
384 ,p_message_name => 'BOM_RTG_REV_NOT_UNIQUE'
385 ,p_token_tbl => l_token_tbl
386 );
387 END;
388 END IF;
389
390 -- If the user is attempting to create or update effective date of the
391 -- revision and the date is less than the current date then it should
392 -- get an error.
393 IF ( p_rtg_revision_rec.transaction_type =
394 BOM_Rtg_Globals.G_OPR_CREATE AND
395 nvl(Bom_Globals.get_caller_type(),'') <> 'MIGRATION' AND -- bug 2871039
396 TRUNC(NVL(p_rtg_revision_rec.start_effective_date, SYSDATE))
397 < TRUNC(SYSDATE)
398 ) OR
399 ( p_rtg_revision_rec.transaction_type =
400 BOM_Rtg_Globals.G_OPR_UPDATE AND
401 p_old_rtg_revision_Rec.start_effective_date <>
402 p_rtg_revision_rec.start_effective_date AND
403 TRUNC(NVL(p_rtg_revision_rec.start_effective_date,SYSDATE))
404 < TRUNC(SYSDATE)
405 )
406 THEN
407 x_return_status := FND_API.G_RET_STS_ERROR;
408 l_token_tbl(3).token_name := 'START_EFFECTIVE_DATE';
409 l_token_tbl(3).token_value :=
410 to_char(p_rtg_revision_rec.start_effective_date);
411 Error_Handler.Add_Error_Token
412 ( p_message_name => 'BOM_RTG_REV_START_DT_LESS_CURR'
413 , p_mesg_token_tbl => l_mesg_token_tbl
414 , x_mesg_token_tbl => l_mesg_token_tbl
415 , p_token_tbl => l_token_tbl
416 );
417 l_token_tbl.delete(3) ;
418 END IF;
419
420 --
421 -- Start effective date can not lie between the effective date of an
422 -- existing revision.
423 --
424 IF p_rtg_revision_rec.transaction_type <> BOM_Rtg_Globals.G_OPR_DELETE
425 THEN
426 BEGIN
427 SELECT 1
428 INTO l_dummy
429 FROM dual
430 WHERE p_rtg_revision_rec.start_effective_date >
431 (SELECT nvl(max(effectivity_date),
432 p_rtg_revision_rec.start_effective_Date-1)
433 FROM mtl_rtg_item_revisions
434 WHERE inventory_item_id = p_rtg_rev_Unexp_Rec.assembly_Item_Id
435 AND organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
436 AND process_revision < p_rtg_revision_rec.Revision
437 )
438 AND p_rtg_revision_rec.start_effective_date <
439 (SELECT nvl(min(effectivity_date),
440 p_rtg_revision_rec.start_effective_Date+1)
441 FROM mtl_rtg_item_revisions
442 WHERE inventory_item_id =p_rtg_rev_Unexp_Rec.assembly_Item_Id
443 AND organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
444 AND process_revision > p_rtg_revision_rec.Revision
445 );
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 l_token_tbl(3).token_name := 'START_EFFECTIVE_DATE';
450 l_token_tbl(3).token_value :=
451 to_char(p_rtg_revision_rec.start_effective_date);
452
453 Error_Handler.Add_Error_Token
454 ( p_message_name => 'BOM_RTG_REV_START_DATE_INVALID'
455 , p_mesg_token_tbl => l_mesg_token_tbl
456 , x_mesg_token_tbl => l_mesg_token_tbl
457 , p_token_tbl => l_token_tbl
458 );
459 l_token_tbl.delete(3) ;
460 END;
461 END IF;
462
463 IF p_rtg_revision_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
464 THEN
465 SELECT effectivity_date into l_effectivity_date
466 FROM mtl_rtg_item_revisions
467 WHERE inventory_item_id =p_rtg_rev_Unexp_Rec.assembly_Item_Id
468 AND organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
469 AND process_revision = p_rtg_revision_rec.Revision;
470 IF trunc( l_effectivity_date) <= trunc(SYSDATE)
471 THEN
472 x_return_status := FND_API.G_RET_STS_ERROR;
473 Error_Handler.Add_Error_Token
474 ( p_message_name => 'BOM_RTG_REV_CANNOT_DELETE'
475 , p_mesg_token_tbl => l_mesg_token_tbl
476 , x_mesg_token_tbl => l_mesg_token_tbl
477 , p_token_tbl => l_token_tbl
478 );
479 END IF;
480
481 END IF;
482
483 x_mesg_token_tbl := l_mesg_token_tbl;
484
485 END Check_Entity;
486
487
488 END BOM_Validate_Rtg_Revision;