1 PACKAGE BODY Bom_Default_Bom_Header AS
2 /* $Header: BOMDBOMB.pls 120.2.12020000.2 2012/11/30 17:08:43 umajumde ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMDBOMB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Default_Bom_Header
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 -- 07-JUL-1999 Rahul Chitko Initial Creation
20 -- 13-JUL-06 Bhavnesh Patel Added support for Structure Type
21 ****************************************************************************/
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Bom_Default_Bom_Header';
23
24
25 /********************************************************************
26 * Function : Get_Bill_Sequence
27 * Return : NUMBER
28 * Purpose : Function will return the bill_sequence_id.
29 *
30 **********************************************************************/
31 FUNCTION Get_Bill_Sequence
32 RETURN NUMBER
33 IS
34 l_bill_sequence_id NUMBER := NULL;
35 BEGIN
36
37 SELECT bom_inventory_components_s.nextval
38 INTO l_bill_sequence_id
39 FROM sys.dual;
40
41 RETURN l_bill_sequence_id;
42
43 EXCEPTION
44
45 WHEN OTHERS THEN
46 RETURN NULL;
47
48 END Get_Bill_Sequence;
49
50 PROCEDURE Get_Flex_Bom_Header
51 ( p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
52 , x_bom_header_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Rec_Type
53 )
54 IS
55 BEGIN
56
57 -- In the future call Flex APIs for defaults
58 x_bom_header_rec := p_bom_header_rec;
59
60 IF p_bom_header_rec.attribute_category =FND_API.G_MISS_CHAR THEN
61 x_bom_header_rec.attribute_category := NULL;
62 END IF;
63
64 IF p_bom_header_rec.attribute2 = FND_API.G_MISS_CHAR THEN
65 x_bom_header_rec.attribute2 := NULL;
66 END IF;
67
68 IF p_bom_header_rec.attribute3 = FND_API.G_MISS_CHAR THEN
69 x_bom_header_rec.attribute3 := NULL;
70 END IF;
71
72 IF p_bom_header_rec.attribute4 = FND_API.G_MISS_CHAR THEN
73 x_bom_header_rec.attribute4 := NULL;
74 END IF;
75
76 IF p_bom_header_rec.attribute5 = FND_API.G_MISS_CHAR THEN
77 x_bom_header_rec.attribute5 := NULL;
78 END IF;
79
80 IF p_bom_header_rec.attribute7 = FND_API.G_MISS_CHAR THEN
81 x_bom_header_rec.attribute7 := NULL;
82 END IF;
83
84 IF p_bom_header_rec.attribute8 = FND_API.G_MISS_CHAR THEN
85 x_bom_header_rec.attribute8 := NULL;
86 END IF;
87
88 IF p_bom_header_rec.attribute9 = FND_API.G_MISS_CHAR THEN
89 x_bom_header_rec.attribute9 := NULL;
90 END IF;
91
92 IF p_bom_header_rec.attribute11 = FND_API.G_MISS_CHAR THEN
93 x_bom_header_rec.attribute11 := NULL;
94 END IF;
95
96 IF p_bom_header_rec.attribute12 = FND_API.G_MISS_CHAR THEN
97 x_bom_header_rec.attribute12 := NULL;
98 END IF;
99
100 IF p_bom_header_rec.attribute13 = FND_API.G_MISS_CHAR THEN
101 x_bom_header_rec.attribute13 := NULL;
102 END IF;
103
104 IF p_bom_header_rec.attribute14 = FND_API.G_MISS_CHAR THEN
105 x_bom_header_rec.attribute14 := NULL;
106 END IF;
107
108 IF p_bom_header_rec.attribute15 = FND_API.G_MISS_CHAR THEN
109 x_bom_header_rec.attribute15 := NULL;
110 END IF;
111
112 IF p_bom_header_rec.attribute1 = FND_API.G_MISS_CHAR THEN
113 x_bom_header_rec.attribute1 := NULL;
114 END IF;
115
116 IF p_bom_header_rec.attribute6 = FND_API.G_MISS_CHAR THEN
117 x_bom_header_rec.attribute6 := NULL;
118 END IF;
119
120 IF p_bom_header_rec.attribute10 = FND_API.G_MISS_CHAR THEN
121 x_bom_header_rec.attribute10 := NULL;
122 END IF;
123
124 END Get_Flex_Bom_Header;
125
126 --Bug 12937561 (begin)
127 PROCEDURE Get_Flex_Common_Bom_Header
128 ( p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
129 , p_src_bill_seq_id IN NUMBER
130 , x_bom_header_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Rec_Type
131 )
132 IS
133 l_attribute_category VARCHAR2(30);
134 l_attribute1 VARCHAR2(150);
135 l_attribute2 VARCHAR2(150);
136 l_attribute3 VARCHAR2(150);
137 l_attribute4 VARCHAR2(150);
138 l_attribute5 VARCHAR2(150);
139 l_attribute6 VARCHAR2(150);
140 l_attribute7 VARCHAR2(150);
141 l_attribute8 VARCHAR2(150);
142 l_attribute9 VARCHAR2(150);
143 l_attribute10 VARCHAR2(150);
144 l_attribute11 VARCHAR2(150);
145 l_attribute12 VARCHAR2(150);
146 l_attribute13 VARCHAR2(150);
147 l_attribute14 VARCHAR2(150);
148 l_attribute15 VARCHAR2(150);
149 BEGIN
150 x_bom_header_rec := p_bom_header_rec;
151
152 SELECT attribute_category, attribute1,
153 attribute2, attribute3, attribute4, attribute5, attribute6,
154 attribute7, attribute8, attribute9, attribute10, attribute11,
158 l_attribute7, l_attribute8, l_attribute9, l_attribute10, l_attribute11,
155 attribute12, attribute13, attribute14, attribute15
156 INTO l_attribute_category, l_attribute1,
157 l_attribute2, l_attribute3, l_attribute4, l_attribute5, l_attribute6,
159 l_attribute12, l_attribute13, l_attribute14, l_attribute15
160 FROM BOM_STRUCTURES_B
161 WHERE bill_sequence_id = p_src_bill_seq_id;
162
163 x_bom_header_rec.attribute_category := l_attribute_category ;
164 x_bom_header_rec.attribute1 := l_attribute1 ;
165 x_bom_header_rec.attribute2 := l_attribute2 ;
166 x_bom_header_rec.attribute3 := l_attribute3 ;
167 x_bom_header_rec.attribute4 := l_attribute4 ;
168 x_bom_header_rec.attribute5 := l_attribute5 ;
169 x_bom_header_rec.attribute6 := l_attribute6 ;
170 x_bom_header_rec.attribute7 := l_attribute7 ;
171 x_bom_header_rec.attribute8 := l_attribute8 ;
172 x_bom_header_rec.attribute9 := l_attribute9 ;
173 x_bom_header_rec.attribute10 := l_attribute10 ;
174 x_bom_header_rec.attribute11 := l_attribute11 ;
175 x_bom_header_rec.attribute12 := l_attribute12 ;
176 x_bom_header_rec.attribute13 := l_attribute13 ;
177 x_bom_header_rec.attribute14 := l_attribute14 ;
178 x_bom_header_rec.attribute15 := l_attribute15 ;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 Error_Handler.Write_Debug ('Get_Flex_Common_Bom_Header: Some error occurred while retrieving the source bill');
183 END Get_Flex_Common_Bom_Header;
184
185 --Bug 12937561 (end)
186
187 /*********************************************************************
188 * Procedure : Attribute_Defaulting
189 * Parameters IN : Bom Header exposed record
190 * Bom Header unexposed record
191 * Parameters OUT: Bom Header exposed record after defaulting
192 * Bom Header unexposed record after defaulting
193 * Mesg_Token_Table
194 * Return_Status
195 * Purpose : Attribute Defaulting will default the necessary null
196 * attribute with appropriate values.
197 **********************************************************************/
198 PROCEDURE Attribute_Defaulting
199 ( p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
200 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
201 , x_bom_header_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Rec_Type
202 , x_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
203 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
204 , x_return_status IN OUT NOCOPY VARCHAR2
205 )
206 IS
207 l_token_tbl Error_Handler.Token_Tbl_Type;
208 BEGIN
209
210 x_bom_header_rec := p_bom_header_rec;
211 x_bom_head_unexp_rec := p_bom_head_unexp_rec;
212 x_return_status := FND_API.G_RET_STS_SUCCESS;
213
214 IF p_bom_head_unexp_rec.bill_sequence_id IS NULL OR
215 p_bom_head_unexp_rec.bill_sequence_id = FND_API.G_MISS_NUM
216 THEN
217 x_bom_head_unexp_rec.bill_sequence_id :=
218 Get_Bill_Sequence;
219 END IF;
220
221 Get_Flex_Bom_Header( p_bom_header_rec => p_bom_header_rec
222 , x_bom_header_rec => x_bom_header_rec
223 );
224
225 --begin Bug 12937561
226 IF p_bom_head_unexp_rec.source_bill_sequence_id <> p_bom_head_unexp_rec.bill_sequence_id
227 THEN
228 Get_Flex_Common_Bom_Header( p_bom_header_rec => p_bom_header_rec
229 , p_src_bill_seq_id => p_bom_head_unexp_rec.source_bill_sequence_id
230 , x_bom_header_rec => x_bom_header_rec
231
232 );
233 END IF;
234 --end Bug 12937561
235
236
237 IF(x_bom_header_rec.assembly_type is null)
238 THEN
239 SELECT decode(eng_item_flag, 'N', 1, 2)
240 INTO x_bom_header_rec.assembly_type
241 FROM mtl_system_items
242 WHERE inventory_item_id = p_bom_head_unexp_rec.assembly_item_id
243 AND organization_id = p_bom_head_unexp_rec.organization_id;
244 END IF;
245
246 -- Default implementation date. for bug3550305
247 IF(x_bom_header_rec.bom_implementation_date is null)
248 THEN
249 x_bom_header_rec.bom_implementation_date := sysdate;
250 ELSIF (x_bom_header_rec.bom_implementation_date =
251 FND_API.G_MISS_DATE) THEN
252 x_bom_header_rec.bom_implementation_date := null;
253 END IF;
254
255
256 /* Get the structure type information */
257
258 Error_Handler.Write_Debug ('Get the structure type id');
259 BEGIN
260 IF x_bom_head_unexp_rec.structure_type_id IS NULL THEN
261 SELECT structure_type_id,
262 enable_unimplemented_boms
263 INTO x_bom_head_unexp_rec.structure_type_id,
264 x_bom_head_unexp_rec.enable_unimplemented_boms
265 FROM bom_alt_designators_val_v
266 WHERE ( (x_bom_header_rec.alternate_bom_code IS NULL AND
267 alternate_designator_code IS NULL
268 AND organization_id = -1)
269 OR
270 (x_bom_header_rec.alternate_bom_code IS NOT NULL AND
271 x_bom_header_rec.alternate_bom_code = alternate_designator_code AND
272 x_bom_head_unexp_rec.organization_id = organization_id)
276 INTO x_bom_head_unexp_rec.enable_unimplemented_boms
273 );
274 ELSE
275 SELECT ENABLE_UNIMPLEMENTED_BOMS
277 FROM BOM_ALT_DESIGNATORS_VAL_V
278 WHERE ( ( x_bom_header_rec.alternate_bom_code IS NULL
279 AND ALTERNATE_DESIGNATOR_CODE IS NULL
280 AND ORGANIZATION_ID = -1 )
281 OR
282 ( x_bom_header_rec.alternate_bom_code IS NOT NULL
283 AND x_bom_header_rec.alternate_bom_code = ALTERNATE_DESIGNATOR_CODE
284 AND x_bom_head_unexp_rec.organization_id = ORGANIZATION_ID )
285 );
286 END IF; --end IF x_bom_head_unexp_rec.structure_type_id
287 EXCEPTION
288 WHEN NO_DATA_FOUND THEN
289 -- This stage is reached only when the BO is called from EAM due to the VPD security policy.
290 -- If any new security policy is later on added, post 11.5.10... we need to get the correct
291 -- context here and then take care of selecting the correct structure_type_id
292 IF x_bom_header_rec.alternate_bom_code IS NULL THEN
293 SELECT structure_type_id, nvl(enable_unimplemented_boms, 'N')
294 INTO x_bom_head_unexp_rec.structure_type_id,
295 x_bom_head_unexp_rec.enable_unimplemented_boms
296 FROM bom_structure_types_b
297 WHERE structure_type_name = 'Asset BOM';
298 ELSE
299 -- If we come here, then there is some other unexpected error
300 -- The alternate code given is incorrect and is currently not being handled
301 -- as any other error.
302 x_return_status := Error_Handler.G_STATUS_UNEXPECTED;
303 END IF;
304 END;
305
306 /* If the structure allows unimplemented BOMS and the BO is for BOM
307 and also the assembly is ENGG, create the BOM as unimplemented BOM */
308
309 Error_Handler.Write_Debug ('Checking for unimplemented BOM');
310 IF x_bom_header_rec.bom_implementation_date IS NULL
311 THEN
312 Error_Handler.Write_Debug ('IS NULL' || 'BO: ' || Bom_Globals.Get_Bo_Identifier || 'unimpl: ' || x_bom_head_unexp_rec.enable_unimplemented_boms || ' assembly type: ' || x_bom_header_rec.assembly_type);
313 IF x_bom_header_rec.assembly_type <> 2 OR
314 --Bom_Globals.Get_Bo_Identifier <> 'BOM' OR
315 x_bom_head_unexp_rec.enable_unimplemented_boms <> 'Y' THEN
316 l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
317 l_token_tbl(1).token_value :=
318 x_bom_header_rec.assembly_item_name;
319 l_token_tbl(1).token_name := 'ALTERNATE';
320 l_token_tbl(1).token_value :=
321 x_bom_header_rec.alternate_bom_code;
322 Error_Handler.Add_Error_Token
323 ( p_message_name => 'BOM_CANNOT_CREATE_UNIMPBOM'
324 , p_Mesg_Token_Tbl => x_Mesg_Token_Tbl
325 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
326 , p_Token_Tbl => l_Token_Tbl
327 );
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 END IF;
330 END IF;
331 Error_Handler.Write_Debug ('After the unimplemented check');
332
333 END Attribute_Defaulting;
334
335 /******************************************************************
336 * Procedure : Populate_Null_Columns
337 * Parameters IN : Bom Header Exposed column record
338 * Bom Header Unexposed column record
339 * Old Bom Header Exposed Column Record
340 * Old Bom Header Unexposed Column Record
341 * Parameters OUT: Bom Header Exposed column record after populating
342 * Bom Header Unexposed Column record after populating
343 * Purpose : This procedure will look at the columns that the user * has not filled in and will assign those columns a
344 * value from the old record.
345 * This procedure is not called CREATE
346 ********************************************************************/
347 PROCEDURE Populate_Null_Columns
348 ( p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
349 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
350 , p_old_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
351 , p_old_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
352 , x_bom_header_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Rec_Type
353 , x_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
354 )
355 IS
356 l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type; -- Bug 4909882
357 BEGIN
358 x_bom_header_rec := p_bom_header_rec;
359 x_bom_head_unexp_rec := p_bom_head_unexp_rec;
360
361 /* Assign NULL if the values are MISSING */
362
363 /* Bug 4909882. This should be called at the end after assigning
364 OLD values
365 Get_Flex_Bom_Header( p_bom_header_rec => p_bom_header_rec
366 , x_bom_header_rec => x_bom_header_rec
367 ); */
368
369 /* Assign OLD values if the values are NULL */
370
371 IF p_bom_header_rec.attribute_category IS NULL
372 THEN
373 x_bom_header_rec.attribute_category :=
374 p_old_bom_header_rec.attribute_category;
375
376 END IF;
377
378 IF p_bom_header_rec.attribute2 IS NULL
379 THEN
380 x_bom_header_rec.attribute2 :=
381 p_old_bom_header_rec.attribute2;
382 END IF;
383
384 IF p_bom_header_rec.attribute3 IS NULL
385 THEN
386 x_bom_header_rec.attribute3 :=
387 p_old_bom_header_rec.attribute3;
388 END IF;
389
390 IF p_bom_header_rec.attribute4 IS NULL THEN
391 x_bom_header_rec.attribute4 :=
392 p_old_bom_header_rec.attribute4;
393 END IF;
394
395 IF p_bom_header_rec.attribute5 IS NULL
396 THEN
397 x_bom_header_rec.attribute5 :=
398 p_old_bom_header_rec.attribute5;
399 END IF;
400
401 IF p_bom_header_rec.attribute7 IS NULL
402 THEN
403 x_bom_header_rec.attribute7 :=
404 p_old_bom_header_rec.attribute7;
405 END IF;
406
407 IF p_bom_header_rec.attribute8 IS NULL
408 THEN
409 x_bom_header_rec.attribute8 :=
410 p_old_bom_header_rec.attribute8;
411 END IF;
412
413 IF p_bom_header_rec.attribute9 IS NULL
414 THEN
415 x_bom_header_rec.attribute9 :=
416 p_old_bom_header_rec.attribute9;
417 END IF;
418
419 IF p_bom_header_rec.attribute11 IS NULL
420 THEN
421 x_bom_header_rec.attribute11 :=
422 p_old_bom_header_rec.attribute11;
423 END IF;
424
425 IF p_bom_header_rec.attribute12 IS NULL
426 THEN
427 x_bom_header_rec.attribute12 :=
428 p_old_bom_header_rec.attribute12;
429 END IF;
430
431 IF p_bom_header_rec.attribute13 IS NULL
432 THEN
433 x_bom_header_rec.attribute13 :=
434 p_old_bom_header_rec.attribute13;
435 END IF;
436
437 IF p_bom_header_rec.attribute14 IS NULL
438 THEN
439 x_bom_header_rec.attribute14 :=
440 p_old_bom_header_rec.attribute14;
441 END IF;
442
443 IF p_bom_header_rec.attribute15 IS NULL
444 THEN
445 x_bom_header_rec.attribute15 :=
446 p_old_bom_header_rec.attribute15;
447 END IF;
448
449 IF p_bom_header_rec.attribute1 IS NULL
450 THEN
451 x_bom_header_rec.attribute1 :=
452 p_old_bom_header_rec.attribute1;
453 END IF;
454
455 IF p_bom_header_rec.attribute6 IS NULL
456 THEN
457 x_bom_header_rec.attribute6 :=
458 p_old_bom_header_rec.attribute6;
459 END IF;
460
461 IF p_bom_header_rec.attribute10 IS NULL
462 THEN
463 x_bom_header_rec.attribute10 :=
464 p_old_bom_header_rec.attribute10;
465 END IF;
466
467 -- Bug 4909882
468 l_bom_header_rec := x_bom_header_rec;
469 Get_Flex_Bom_Header( p_bom_header_rec => l_bom_header_rec
470 , x_bom_header_rec => x_bom_header_rec
471 );
472 IF p_bom_header_rec.assembly_type = FND_API.G_MISS_NUM OR
473 p_bom_header_rec.assembly_type IS NULL
474 THEN
475 x_bom_header_rec.assembly_type :=
476 p_old_bom_header_rec.assembly_type;
477 END IF;
478
479 x_bom_header_rec.bom_implementation_date :=
480 p_old_bom_header_rec.bom_implementation_date;
481 --
482 -- Get the unexposed columns from the database and return
483 -- them as the unexposed columns for the current record.
484 --
485 x_bom_head_unexp_rec.bill_sequence_id := p_old_bom_head_unexp_rec.bill_sequence_id;
486
487 --structure type id can not be updated to null, so ignore FND_API.G_MISS_NUM
488 IF ( p_bom_head_unexp_rec.structure_type_id IS NULL
489 OR p_bom_head_unexp_rec.structure_type_id = FND_API.G_MISS_NUM )
490 THEN
491 x_bom_head_unexp_rec.structure_type_id :=
492 p_old_bom_head_unexp_rec.structure_type_id;
493 END IF;
494
495
496 END Populate_Null_Columns;
497
498 END Bom_Default_Bom_Header;
[Home] [Help]