[Home] [Help]
PACKAGE BODY: APPS.BOM_BOM_HEADER_UTIL
Source
1 PACKAGE BODY Bom_Bom_Header_Util AS
2 /* $Header: BOMUBOMB.pls 120.5.12010000.2 2010/01/22 19:38:42 vbrobbey ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- ENGUBOMB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Bom_Header_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 -- 02-JUL-1999 Rahul Chitko Initial Creation
20 -- 19-Aug-2003 Hari Gelli added obj_name, pk1_value, pk2_value in insert_row
21 -- 06-Feb-2004 Vani added Is_Preferred Flag for inserts
22 -- 11-JAN-2005 Vani added effectivity control in Insert_Row
23 -- 21-FEB-2005 Vani added query_table_row method to query from
24 -- bom_structures_b instead of bom_bill_of_materials.
25 -- 06-MAY-2005 Abhsihek Rudresh Common BOM Attrs Update
26 -- 13-JUL-06 Bhavnesh Patel Added support for Structure Type
27 ****************************************************************************/
28
29 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Bom_Bom_Header_Util';
30 FUNCTION get_effectivity_control (item_id NUMBER, org_id NUMBER) return NUMBER;
31
32 /*********************************************************************
33 * Procedure : Query_Row
34 * Parameters IN : Assembly item id
35 * Organization Id
36 * Alternate_Bom_Code
37 * Parameters OUT: Bom header exposed column record
38 * BOm Header unexposed column record
39 * Mesg token Table
40 * Return Status
41 * Purpose : Procedure will query the database record, seperate the
42 * values into exposed columns and unexposed columns and
43 * return with those records.
44 ***********************************************************************/
45 PROCEDURE Query_Row
46 ( p_assembly_item_id IN NUMBER
47 , p_organization_id IN NUMBER
48 , p_alternate_bom_code IN VARCHAR2 := NULL
49 , x_bom_header_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_head_Rec_Type
50 , x_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_head_unexposed_Rec_Type
51 , x_Return_status IN OUT NOCOPY VARCHAR2
52 )
53 IS
54 l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type;
55 l_bom_head_unexp_rec Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type;
56 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
57 l_dummy varchar2(10);
58 BEGIN
59 SELECT decode(p_alternate_bom_code, FND_API.G_MISS_CHAR,
60 'Missing', NULL, 'XXXX', p_alternate_bom_code)
61 INTO l_dummy
62 from sys.dual;
63 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Alt: ' || l_dummy
64 ); END IF;
65
66 SELECT assembly_item_id
67 , organization_id
71 , specific_assembly_comment
68 , alternate_bom_designator
69 , common_assembly_item_id
70 , common_organization_id
72 , attribute_category
73 , attribute1
74 , attribute2
75 , attribute3
76 , attribute4
77 , attribute5
78 , attribute6
79 , attribute7
80 , attribute8
81 , attribute9
82 , attribute10
83 , attribute11
84 , attribute12
85 , attribute13
86 , attribute14
87 , attribute15
88 , assembly_type
89 , assembly_type
90 , common_bill_sequence_id
91 , bill_sequence_id
92 , structure_type_id
93 , implementation_date
94 INTO l_bom_head_unexp_rec.assembly_item_id
95 , l_bom_head_unexp_rec.organization_id
96 , l_bom_header_rec.alternate_bom_code
97 , l_bom_head_unexp_rec.common_assembly_item_id
98 , l_bom_head_unexp_rec.common_organization_id
99 , l_bom_header_rec.assembly_comment
100 , l_bom_header_rec.attribute_category
101 , l_bom_header_rec.attribute1
102 , l_bom_header_rec.attribute2
103 , l_bom_header_rec.attribute3
104 , l_bom_header_rec.attribute4
105 , l_bom_header_rec.attribute5
106 , l_bom_header_rec.attribute6
107 , l_bom_header_rec.attribute7
108 , l_bom_header_rec.attribute8
109 , l_bom_header_rec.attribute9
110 , l_bom_header_rec.attribute10
111 , l_bom_header_rec.attribute11
112 , l_bom_header_rec.attribute12
113 , l_bom_header_rec.attribute13
114 , l_bom_header_rec.attribute14
115 , l_bom_header_rec.attribute15
116 , l_bom_head_unexp_rec.assembly_type
117 , l_bom_header_rec.assembly_type
118 , l_bom_head_unexp_rec.common_bill_sequence_id
119 , l_bom_head_unexp_rec.bill_sequence_id
120 , l_bom_head_unexp_rec.structure_type_id
121 , l_bom_header_rec.bom_implementation_date
122 FROM bom_bill_of_materials
123 WHERE assembly_item_id = p_assembly_item_id
124 AND organization_id = p_organization_id
125 AND effectivity_control <>4 -- Rev effective structures should be filtered.
126 AND NVL(alternate_bom_designator, 'XXXX') =
127 NVL(DECODE( p_alternate_bom_code,FND_API.G_MISS_CHAR,
128 NULL, p_alternate_bom_code
129 ), 'XXXX');
130
131 x_return_status := BOM_Globals.G_RECORD_FOUND;
132 x_bom_header_rec := l_bom_header_rec;
133 x_bom_head_unexp_rec := l_bom_head_unexp_rec;
134
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 x_return_status := BOM_Globals.G_RECORD_NOT_FOUND;
138 x_bom_header_rec := l_bom_header_rec;
139 x_bom_head_unexp_rec := l_bom_head_unexp_rec;
140 WHEN OTHERS THEN
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 x_bom_header_rec := l_bom_header_rec;
143 x_bom_head_unexp_rec := l_bom_head_unexp_rec;
144
145 END Query_Row;
146
147
148 /*********************************************************************
149 * Procedure : Query_Table_Row
150 * Parameters IN : Assembly item id
151 * Organization Id
152 * Alternate_Bom_Code
153 * Parameters OUT: Bom header exposed column record
154 * BOm Header unexposed column record
155 * Mesg token Table
156 * Return Status
157 * Purpose : Procedure will query the database record, From BOM_STRUCTURES_B
158 * seperate the values into exposed columns and unexposed columns and
159 * return with those records.
160 ***********************************************************************/
161 PROCEDURE Query_Table_Row
162 ( p_assembly_item_id IN NUMBER
163 , p_organization_id IN NUMBER
164 , p_alternate_bom_code IN VARCHAR2 := NULL
165 , x_bom_header_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_head_Rec_Type
166 , x_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_head_unexposed_Rec_Type
167 , x_Return_status IN OUT NOCOPY VARCHAR2
168 )
169 IS
170 l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type;
171 l_bom_head_unexp_rec Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type;
172 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
173 l_dummy varchar2(10);
174 BEGIN
175 SELECT decode(p_alternate_bom_code, FND_API.G_MISS_CHAR,
176 'Missing', NULL, 'XXXX', p_alternate_bom_code)
177 INTO l_dummy
178 from sys.dual;
179 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Alt: ' || l_dummy
180 ); END IF;
181
182 SELECT assembly_item_id
183 , organization_id
184 , alternate_bom_designator
185 , common_assembly_item_id
186 , common_organization_id
187 , specific_assembly_comment
188 , attribute_category
189 , attribute1
190 , attribute2
191 , attribute3
192 , attribute4
193 , attribute5
194 , attribute6
195 , attribute7
196 , attribute8
197 , attribute9
198 , attribute10
199 , attribute11
200 , attribute12
201 , attribute13
202 , attribute14
203 , attribute15
204 , assembly_type
205 , common_bill_sequence_id
206 , bill_sequence_id
207 , structure_type_id
208 , implementation_date
209 INTO l_bom_head_unexp_rec.assembly_item_id
210 , l_bom_head_unexp_rec.organization_id
211 , l_bom_header_rec.alternate_bom_code
215 , l_bom_header_rec.attribute_category
212 , l_bom_head_unexp_rec.common_assembly_item_id
213 , l_bom_head_unexp_rec.common_organization_id
214 , l_bom_header_rec.assembly_comment
216 , l_bom_header_rec.attribute1
217 , l_bom_header_rec.attribute2
218 , l_bom_header_rec.attribute3
219 , l_bom_header_rec.attribute4
220 , l_bom_header_rec.attribute5
221 , l_bom_header_rec.attribute6
222 , l_bom_header_rec.attribute7
223 , l_bom_header_rec.attribute8
224 , l_bom_header_rec.attribute9
225 , l_bom_header_rec.attribute10
226 , l_bom_header_rec.attribute11
227 , l_bom_header_rec.attribute12
228 , l_bom_header_rec.attribute13
229 , l_bom_header_rec.attribute14
230 , l_bom_header_rec.attribute15
231 , l_bom_head_unexp_rec.assembly_type
232 , l_bom_head_unexp_rec.common_bill_sequence_id
233 , l_bom_head_unexp_rec.bill_sequence_id
234 , l_bom_head_unexp_rec.structure_type_id
235 , l_bom_header_rec.bom_implementation_date
236 FROM bom_structures_b
237 WHERE assembly_item_id = p_assembly_item_id
238 AND organization_id = p_organization_id
239 AND NVL(alternate_bom_designator, 'XXXX') =
240 NVL(DECODE( p_alternate_bom_code,FND_API.G_MISS_CHAR,
241 NULL, p_alternate_bom_code
242 ), 'XXXX');
243
244 x_return_status := BOM_Globals.G_RECORD_FOUND;
245 x_bom_header_rec := l_bom_header_rec;
246 x_bom_head_unexp_rec := l_bom_head_unexp_rec;
247
248 EXCEPTION
249 WHEN NO_DATA_FOUND THEN
250 x_return_status := BOM_Globals.G_RECORD_NOT_FOUND;
251 x_bom_header_rec := l_bom_header_rec;
252 x_bom_head_unexp_rec := l_bom_head_unexp_rec;
253 WHEN OTHERS THEN
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 x_bom_header_rec := l_bom_header_rec;
256 x_bom_head_unexp_rec := l_bom_head_unexp_rec;
257
258 END Query_Table_Row;
259
260
261 /********************************************************************
262 * Procedure : Insert_Row
263 * Parameters IN : BOM Header exposed column record
264 * BOM Header unexposed column record
265 * Parameters OUT: Message Token Table
266 * Return Status
267 * Purpose : Procedure will perfrom an insert into the
268 * BOM_Bill_Of_Materials table thus creating a new bill
269 *********************************************************************/
270 PROCEDURE Insert_Row
271 ( p_bom_header_rec IN BOM_Bo_Pub.Bom_Head_Rec_Type
272 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
273 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
274 , x_return_Status IN OUT NOCOPY VARCHAR2
275 )
276 IS
277 l_preferred_flag Varchar2(1);
278 l_effectivity_control NUMBER;
279 x_err_text varchar2(2000);
280 BEGIN
281
282 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Writing Bom Header rec for ' || p_bom_header_rec.assembly_item_name); END IF;
283
284 l_effectivity_control := get_effectivity_control
285 (p_bom_head_unexp_rec.assembly_item_id,
286 p_bom_head_unexp_rec.organization_id);
287 l_preferred_flag := BOM_Validate.Is_Preferred_Structure
288 (p_assembly_item_id =>p_bom_head_unexp_rec.assembly_item_id,
289 p_organization_id => p_bom_head_unexp_rec.organization_Id,
290 p_alternate_bom_code => p_bom_header_rec.alternate_bom_code,
291 x_err_text => x_err_text);
292
293 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
294 INSERT INTO bom_bill_of_materials
295 ( assembly_item_id
296 , organization_id
297 , alternate_bom_designator
298 , common_assembly_item_id
299 , common_organization_id
300 , assembly_type
301 , bill_sequence_id
302 , common_bill_sequence_id
303 , specific_assembly_comment -- Added on 05/31/01
304 , original_system_reference -- Added on 05/31/01
305 , attribute_category
306 , attribute1
307 , attribute2
308 , attribute3
309 , attribute4
310 , attribute5
311 , attribute6
312 , attribute7
313 , attribute8
314 , attribute9
315 , attribute10
316 , attribute11
317 , attribute12
318 , attribute13
319 , attribute14
320 , attribute15
321 , creation_date
322 , created_by
323 , last_update_date
324 , last_updated_by
325 , last_update_login
326 , structure_type_id
327 , implementation_date
328 , effectivity_control
329 , Obj_Name
330 , pk1_value
331 , pk2_value
332 , is_preferred
333 , source_bill_sequence_id
334 , request_id
335 , program_id
336 , program_application_id
337 , program_update_date
338 )
339 VALUES
340 ( p_bom_head_unexp_rec.assembly_item_id
341 , p_bom_head_unexp_rec.organization_id
342 , DECODE(p_bom_header_rec.alternate_bom_code,
343 FND_API.G_MISS_CHAR,
344 NULL,
345 p_bom_header_rec.alternate_bom_code)
346 , DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
347 FND_API.G_MISS_NUM,
348 NULL,
349 p_bom_head_unexp_rec.bill_sequence_id,
350 NULL,
351 p_bom_head_unexp_rec.common_assembly_item_id
355 NULL,
352 )
353 , DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
354 FND_API.G_MISS_NUM,
356 p_bom_head_unexp_rec.bill_sequence_id,
357 NULL,
358 p_bom_head_unexp_rec.common_organization_id
359 )
360 , p_bom_header_rec.Assembly_Type /* Assembly Type */
361 , p_bom_head_unexp_rec.bill_sequence_id
362 , DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
363 FND_API.G_MISS_NUM,
364 p_bom_head_unexp_rec.bill_sequence_id,
365 NULL,
366 p_bom_head_unexp_rec.bill_sequence_id,
367 p_bom_head_unexp_rec.common_bill_sequence_id
368 )
369 , DECODE(p_bom_header_rec.assembly_comment,
370 FND_API.G_MISS_CHAR,
371 NULL,
372 p_bom_header_rec.assembly_comment)
373 , DECODE(p_bom_header_rec.original_system_reference,
374 FND_API.G_MISS_CHAR,
375 NULL,
376 p_bom_header_rec.original_system_reference)
377 , p_bom_header_rec.attribute_category
378 , p_bom_header_rec.attribute1
379 , p_bom_header_rec.attribute2
380 , p_bom_header_rec.attribute3
381 , p_bom_header_rec.attribute4
382 , p_bom_header_rec.attribute5
383 , p_bom_header_rec.attribute6
384 , p_bom_header_rec.attribute7
385 , p_bom_header_rec.attribute8
386 , p_bom_header_rec.attribute9
387 , p_bom_header_rec.attribute10
388 , p_bom_header_rec.attribute11
389 , p_bom_header_rec.attribute12
390 , p_bom_header_rec.attribute13
391 , p_bom_header_rec.attribute14
392 , p_bom_header_rec.attribute15
393 , SYSDATE
394 , BOM_Globals.Get_User_Id
395 , SYSDATE
396 , BOM_Globals.Get_User_Id
397 , BOM_Globals.Get_User_Id
398 , p_bom_head_unexp_rec.structure_type_id
399 , p_bom_header_rec.bom_implementation_date
400 , l_effectivity_control
401 , NULL
402 , p_bom_head_unexp_rec.assembly_item_id
403 , p_bom_head_unexp_rec.organization_id
404 , decode ( l_preferred_flag, 'N',null,'Y')
405 -- , p_bom_head_unexp_rec.source_bill_sequence_id
406 , DECODE(p_bom_head_unexp_rec.source_bill_sequence_id,
407 FND_API.G_MISS_NUM,
408 p_bom_head_unexp_rec.bill_sequence_id,
409 NULL,
410 p_bom_head_unexp_rec.bill_sequence_id,
411 p_bom_head_unexp_rec.source_bill_sequence_id
412 )
413
414 , Fnd_Global.Conc_Request_Id
415 , Fnd_Global.Conc_Program_Id
416 , Fnd_Global.Prog_Appl_Id
417 , sysdate
418 );
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420
421 EXCEPTION
422 WHEN OTHERS THEN
423 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Oracle Error in Writing Bom Header rec ' || to_char(sqlcode)||'/'||sqlerrm); END IF;
424 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('The mandatory values are, Assembly_item_id : ' ||to_char(p_bom_head_unexp_rec.assembly_item_id)); END IF;
425 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Bill_Sequence_Id : '||to_char(p_bom_head_unexp_rec.bill_sequence_id)||'. User Id : '||to_char(Bom_Globals.Get_User_Id)); END IF;
426 Error_Handler.Add_Error_Token
427 ( p_message_name => NULL
428 , p_message_text => G_PKG_NAME ||
429 ' :Inserting Record ' ||
430 SQLERRM
431 , x_mesg_token_Tbl => x_mesg_token_tbl
432 );
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434
435 END Insert_Row;
436
437 /********************************************************************
438 * Procedure : Update_Row
439 * Parameters IN : BOM Header exposed column record
440 * BOM Header unexposed column record
441 * Parameters OUT: Message Token Table
442 * Return Status
443 * Purpose : Procedure will perfrom an Update into the
444 * BOM_Bill_Of_Materials table.
445 *********************************************************************/
446 PROCEDURE Update_Row
447 ( p_bom_header_rec IN BOM_Bo_Pub.Bom_Head_Rec_Type
448 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
449 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
450 , x_return_Status IN OUT NOCOPY VARCHAR2
451 )
452 IS
453
454 -- Added for bug 8208327
455 CURSOR c_CheckCommon IS
456 SELECT NVL(common_bill_sequence_id,bill_sequence_id) common_bill_seq,
457 bill_sequence_id
458 FROM bom_bill_of_materials
459 WHERE bill_sequence_id = p_bom_head_unexp_rec.bill_sequence_id;
460
461 l_is_common_bom BOOLEAN := FALSE;
462 -- End Added for bug 8208327
463
464 BEGIN
465
466 --
467 -- The only fields that are updateable in BOM Header is the
468 -- common bill information
469 --
470 IF Bom_Globals.Get_Debug = 'Y' THEN
471 Error_Handler.Write_Debug('Updating bill seq ' || p_bom_head_unexp_rec.bill_sequence_id);
472 END IF;
473
474 -- Added for bug 8208327
475 -- Check if current BOM is a common bom
476 FOR CheckCommon IN c_CheckCommon
477 LOOP
481 END IF;
478 IF CheckCommon.common_bill_seq <> CheckCommon.bill_sequence_id
479 THEN
480 l_is_common_bom := TRUE;
482
483 END LOOP;
484 --If bom is common bom, then do not update commmon_assembly_item_id, common_organization_id,
485 --and common_bill_sequence_id.
486
487 IF l_is_common_bom = TRUE THEN
488 UPDATE bom_bill_of_materials
489 SET specific_assembly_comment =
490 DECODE(p_bom_header_rec.assembly_comment,
491 FND_API.G_MISS_CHAR,
492 NULL,
493 p_bom_header_rec.assembly_comment
494 )
495 , original_system_reference =
496 DECODE(p_bom_header_rec.original_system_reference,
497 FND_API.G_MISS_CHAR,
498 NULL,
499 p_bom_header_rec.original_system_reference
500 )
501 , last_update_date = SYSDATE
502 , last_updated_by = BOM_Globals.Get_User_Id
503 , last_update_login = BOM_Globals.Get_User_Id
504 , attribute_category = p_bom_header_rec.attribute_category
505 , attribute1 = p_bom_header_rec.attribute1
506 , attribute2 = p_bom_header_rec.attribute2
507 , attribute3 = p_bom_header_rec.attribute3
508 , attribute4 = p_bom_header_rec.attribute4
509 , attribute5 = p_bom_header_rec.attribute5
510 , attribute6 = p_bom_header_rec.attribute6
511 , attribute7 = p_bom_header_rec.attribute7
512 , attribute8 = p_bom_header_rec.attribute8
513 , attribute9 = p_bom_header_rec.attribute9
514 , attribute10= p_bom_header_rec.attribute10
515 , attribute11= p_bom_header_rec.attribute11
516 , attribute12= p_bom_header_rec.attribute12
517 , attribute13= p_bom_header_rec.attribute13
518 , attribute14= p_bom_header_rec.attribute14
519 , attribute15= p_bom_header_rec.attribute15
520 , request_id = Fnd_Global.Conc_Request_Id
521 , program_id = Fnd_Global.Conc_Program_Id
522 , program_application_id = Fnd_Global.Prog_Appl_Id
523 , program_update_date = sysdate
524 WHERE bill_sequence_id = p_bom_head_unexp_rec.bill_sequence_id;
525
526
527 --If bom is not a common bom, then update commmon_assembly_item_id, common_organization_id,
528 --and common_bill_sequence_id.
529 ELSE
530
531 UPDATE bom_bill_of_materials
532 SET common_assembly_item_id =
533 DECODE(p_bom_head_unexp_rec.common_assembly_item_id,
534 FND_API.G_MISS_NUM,
535 null,
536 p_bom_head_unexp_rec.common_assembly_item_id
537 )
538 , common_organization_id =
539 DECODE(p_bom_head_unexp_rec.common_organization_id,
540 FND_API.G_MISS_NUM,
541 null,
542 p_bom_head_unexp_rec.common_organization_id
543 )
544 , common_bill_sequence_id =
545 DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
546 FND_API.G_MISS_NUM,
547 p_bom_head_unexp_rec.bill_sequence_id,
548 NULL,
549 p_bom_head_unexp_rec.bill_sequence_id,
550 p_bom_head_unexp_rec.common_bill_sequence_id
551 )
552 , specific_assembly_comment =
553 DECODE(p_bom_header_rec.assembly_comment,
554 FND_API.G_MISS_CHAR,
555 NULL,
556 p_bom_header_rec.assembly_comment
557 )
558 , original_system_reference =
559 DECODE(p_bom_header_rec.original_system_reference,
560 FND_API.G_MISS_CHAR,
561 NULL,
562 p_bom_header_rec.original_system_reference
563 )
564 , source_bill_sequence_id =
565 DECODE(p_bom_head_unexp_rec.source_bill_sequence_id,
566 FND_API.G_MISS_NUM,
567 p_bom_head_unexp_rec.bill_sequence_id,
568 NULL,
569 p_bom_head_unexp_rec.bill_sequence_id,
570 p_bom_head_unexp_rec.source_bill_sequence_id
571 )
572 , last_update_date = SYSDATE
573 , last_updated_by = BOM_Globals.Get_User_Id
574 , last_update_login = BOM_Globals.Get_User_Id
575 , attribute_category = p_bom_header_rec.attribute_category
576 , attribute1 = p_bom_header_rec.attribute1
577 , attribute2 = p_bom_header_rec.attribute2
578 , attribute3 = p_bom_header_rec.attribute3
579 , attribute4 = p_bom_header_rec.attribute4
580 , attribute5 = p_bom_header_rec.attribute5
581 , attribute6 = p_bom_header_rec.attribute6
582 , attribute7 = p_bom_header_rec.attribute7
583 , attribute8 = p_bom_header_rec.attribute8
584 , attribute9 = p_bom_header_rec.attribute9
585 , attribute10= p_bom_header_rec.attribute10
586 , attribute11= p_bom_header_rec.attribute11
587 , attribute12= p_bom_header_rec.attribute12
588 , attribute13= p_bom_header_rec.attribute13
589 , attribute14= p_bom_header_rec.attribute14
593 , program_application_id = Fnd_Global.Prog_Appl_Id
590 , attribute15= p_bom_header_rec.attribute15
591 , request_id = Fnd_Global.Conc_Request_Id
592 , program_id = Fnd_Global.Conc_Program_Id
594 , program_update_date = sysdate
595 , structure_type_id =
596 DECODE(p_bom_head_unexp_rec.structure_type_id,
597 FND_API.G_MISS_NUM,
598 structure_type_id,
599 NULL,
600 structure_type_id,
601 p_bom_head_unexp_rec.structure_type_id
602 )
603 WHERE bill_sequence_id = p_bom_head_unexp_rec.bill_sequence_id
604 ;
605 END IF;
606 -- End Added for bug 8208327
607
608 END Update_Row;
609
610
611 /********************************************************************
612 * Procedure : Delete_Row
613 * Parameters IN : BOM Header exposed column record
614 * BOM Header unexposed column record
615 * Parameters OUT: Message Token Table
616 * Return Status
617 * Purpose : Procedure will perfrom an Delete from the
618 * BOM_Bill_Of_Materials by creating a delete Group.
619 *********************************************************************/
620 PROCEDURE Delete_Row
621 ( p_bom_header_rec IN BOM_Bo_Pub.Bom_Head_Rec_Type
622 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
623 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
624 , x_return_Status IN OUT NOCOPY VARCHAR2
625 )
626 IS
627 Cursor CheckGroup is
628 SELECT description,
629 delete_group_sequence_id,
630 delete_type
631 FROM bom_delete_groups
632 WHERE delete_group_name = p_bom_header_rec.delete_group_name
633 AND organization_id = p_bom_head_unexp_rec.organization_id;
634 l_bom_head_unexp_rec Bom_bo_Pub.Bom_Head_Unexposed_Rec_Type :=
635 p_bom_head_unexp_rec;
636 l_bom_header_rec Bom_bo_Pub.Bom_Head_Rec_Type :=
637 p_bom_header_rec;
638 l_dg_sequence_id NUMBER;
639 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
640 l_assembly_type NUMBER;
641
642
643 BEGIN
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645
646 FOR DG IN CheckGroup
647 LOOP
648 IF DG.delete_type <> 2 /* Bill */ then
649 Error_Handler.Add_Error_Token
650 ( p_message_name =>
651 'BOM_DUPLICATE_DELETE_GROUP'
652 , p_mesg_token_tbl =>
653 l_mesg_token_Tbl
654 , x_mesg_token_tbl =>
655 l_mesg_token_tbl
656 );
657 x_return_status := FND_API.G_RET_STS_ERROR;
658 x_mesg_token_tbl := l_mesg_token_tbl;
659 RETURN;
660 END IF;
661
662 l_bom_head_unexp_rec.DG_Sequence_Id :=
663 DG.delete_group_sequence_id;
664 l_bom_header_rec.DG_Description := DG.description;
665
666 END LOOP;
667
668 IF l_bom_head_unexp_rec.DG_Sequence_Id <> FND_API.G_MISS_NUM
669 THEN
670 l_dg_sequence_id := l_bom_head_unexp_rec.DG_Sequence_Id;
671 ELSE
672 l_dg_sequence_id := NULL;
673 Error_Handler.Add_Error_Token
674 ( p_message_name => 'NEW_DELETE_GROUP'
675 , p_mesg_token_tbl => l_mesg_token_Tbl
676 , x_mesg_token_tbl => l_mesg_token_tbl
677 , p_message_type => 'W' /* Warning */
678 );
679 END IF;
680
681 --bug 5199643
682 select assembly_type into l_assembly_type
683 from bom_structures_b
684 where bill_sequence_id =l_bom_head_unexp_rec.bill_sequence_id;
685
686 l_dg_sequence_id :=
687 MODAL_DELETE.DELETE_MANAGER
688 ( new_group_seq_id => l_dg_sequence_id,
689 name => l_bom_header_rec.Delete_Group_Name,
690 group_desc => l_bom_header_rec.dg_description,
691 org_id => l_bom_head_unexp_rec.organization_id,
692 bom_or_eng => l_assembly_type /* dg type should be same as bill type */,
693 del_type => 2 /* Bill */,
694 ent_bill_seq_id => l_bom_head_unexp_rec.bill_sequence_id,
695 ent_rtg_seq_id => NULL,
696 ent_inv_item_id => l_bom_head_unexp_rec.assembly_item_id,
697 ent_alt_designator => l_bom_header_rec.alternate_bom_code,
698 ent_comp_seq_id => NULL,
699 ent_op_seq_id => NULL,
700 user_id => BOM_Globals.Get_User_Id
701 );
702
703 x_mesg_token_tbl := l_mesg_token_tbl;
704
705 END Delete_Row;
706
707 /*********************************************************************
708 * Procedure : Perform_Writes
709 * Parameters IN : BOM Header Exposed Column Record
710 * BOM Header Unexposed column record
711 * Parameters OUT: Messgae Token Table
712 * Return Status
713 * Purpose : This is the only procedure that the user will have
714 * access to when he/she needs to perform any kind of
715 * writes to the bom_bill_of_materials table.
716 *********************************************************************/
717 PROCEDURE Perform_Writes
721 , x_return_status IN OUT NOCOPY VARCHAR2
718 ( p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
719 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
720 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
722 )
723 IS
724 l_Mesg_Token_tbl Error_Handler.Mesg_Token_Tbl_Type;
725 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
726 BEGIN
727 IF p_bom_header_rec.transaction_type = BOM_GLOBALS.G_OPR_CREATE
728 THEN
729 Insert_Row
730 ( p_bom_header_rec => p_bom_header_rec
731 , p_bom_head_unexp_rec => p_bom_head_unexp_rec
732 , x_mesg_token_Tbl => l_mesg_token_tbl
733 , x_return_Status => l_return_status
734 );
735 ELSIF p_bom_header_rec.transaction_type =
736 BOM_GLOBALS.G_OPR_UPDATE
737 THEN
738 Update_Row
739 ( p_bom_header_rec => p_bom_header_rec
740 , p_bom_head_unexp_rec => p_bom_head_unexp_rec
741 , x_mesg_token_Tbl => l_mesg_token_tbl
742 , x_return_Status => l_return_status
743 );
744
745 ELSIF p_bom_header_rec.transaction_type =
746 BOM_GLOBALS.G_OPR_DELETE
747 THEN
748 Delete_Row
749 ( p_bom_header_rec => p_bom_header_rec
750 , p_bom_head_unexp_rec => p_bom_head_unexp_rec
751 , x_mesg_token_Tbl => l_mesg_token_tbl
752 , x_return_Status => l_return_status
753 );
754 END IF;
755
756 x_return_status := l_return_status;
757 x_mesg_token_tbl := l_mesg_token_tbl;
758
759 END Perform_Writes;
760
761
762 FUNCTION get_effectivity_control ( item_id NUMBER, org_id NUMBER) return NUMBER is
763
764 l_eff_control NUMBER;
765 BEGIN
766 select effectivity_control into l_eff_control
767 from mtl_system_items_b
768 where inventory_item_id = item_id
769 and organization_id = org_id;
770 return l_eff_control;
771 END;
772 END Bom_Bom_Header_Util;