[Home] [Help]
PACKAGE BODY: APPS.BOM_BOM_HEADER_UTIL
Source
1 PACKAGE BODY Bom_Bom_Header_Util AS
2 /* $Header: BOMUBOMB.pls 120.5 2006/07/14 04:27:47 bbpatel noship $ */
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
68 , alternate_bom_designator
69 , common_assembly_item_id
70 , common_organization_id
71 , specific_assembly_comment
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
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
215 , l_bom_header_rec.attribute_category
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
352 )
353 , DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
354 FND_API.G_MISS_NUM,
355 NULL,
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
397 , BOM_Globals.Get_User_Id
394 , BOM_Globals.Get_User_Id
395 , SYSDATE
396 , 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 BEGIN
454
455 --
456 -- The only fields that are updateable in BOM Header is the
457 -- common bill information
458 --
459 IF Bom_Globals.Get_Debug = 'Y' THEN
460 Error_Handler.Write_Debug('Updating bill seq ' || p_bom_head_unexp_rec.bill_sequence_id);
461 END IF;
462
463 UPDATE bom_bill_of_materials
464 SET common_assembly_item_id =
465 DECODE(p_bom_head_unexp_rec.common_assembly_item_id,
466 FND_API.G_MISS_NUM,
467 null,
468 p_bom_head_unexp_rec.common_assembly_item_id
469 )
470 , common_organization_id =
471 DECODE(p_bom_head_unexp_rec.common_organization_id,
472 FND_API.G_MISS_NUM,
473 null,
474 p_bom_head_unexp_rec.common_organization_id
475 )
476 , common_bill_sequence_id =
477 DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
478 FND_API.G_MISS_NUM,
479 p_bom_head_unexp_rec.bill_sequence_id,
480 NULL,
481 p_bom_head_unexp_rec.bill_sequence_id,
482 p_bom_head_unexp_rec.common_bill_sequence_id
483 )
484 , specific_assembly_comment =
485 DECODE(p_bom_header_rec.assembly_comment,
486 FND_API.G_MISS_CHAR,
487 NULL,
488 p_bom_header_rec.assembly_comment
489 )
490 , original_system_reference =
491 DECODE(p_bom_header_rec.original_system_reference,
492 FND_API.G_MISS_CHAR,
493 NULL,
494 p_bom_header_rec.original_system_reference
495 )
496 , source_bill_sequence_id =
497 DECODE(p_bom_head_unexp_rec.source_bill_sequence_id,
498 FND_API.G_MISS_NUM,
499 p_bom_head_unexp_rec.bill_sequence_id,
500 NULL,
501 p_bom_head_unexp_rec.bill_sequence_id,
505 , last_updated_by = BOM_Globals.Get_User_Id
502 p_bom_head_unexp_rec.source_bill_sequence_id
503 )
504 , last_update_date = SYSDATE
506 , last_update_login = BOM_Globals.Get_User_Id
510 , attribute3 = p_bom_header_rec.attribute3
507 , attribute_category = p_bom_header_rec.attribute_category
508 , attribute1 = p_bom_header_rec.attribute1
509 , attribute2 = p_bom_header_rec.attribute2
511 , attribute4 = p_bom_header_rec.attribute4
512 , attribute5 = p_bom_header_rec.attribute5
513 , attribute6 = p_bom_header_rec.attribute6
514 , attribute7 = p_bom_header_rec.attribute7
515 , attribute8 = p_bom_header_rec.attribute8
516 , attribute9 = p_bom_header_rec.attribute9
517 , attribute10= p_bom_header_rec.attribute10
518 , attribute11= p_bom_header_rec.attribute11
519 , attribute12= p_bom_header_rec.attribute12
520 , attribute13= p_bom_header_rec.attribute13
521 , attribute14= p_bom_header_rec.attribute14
522 , attribute15= p_bom_header_rec.attribute15
523 , request_id = Fnd_Global.Conc_Request_Id
524 , program_id = Fnd_Global.Conc_Program_Id
525 , program_application_id = Fnd_Global.Prog_Appl_Id
526 , program_update_date = sysdate
527 , structure_type_id =
528 DECODE(p_bom_head_unexp_rec.structure_type_id,
529 FND_API.G_MISS_NUM,
530 structure_type_id,
531 NULL,
532 structure_type_id,
533 p_bom_head_unexp_rec.structure_type_id
534 )
535 WHERE bill_sequence_id = p_bom_head_unexp_rec.bill_sequence_id
536 ;
537
538 END Update_Row;
539
540
541 /********************************************************************
542 * Procedure : Delete_Row
543 * Parameters IN : BOM Header exposed column record
544 * BOM Header unexposed column record
545 * Parameters OUT: Message Token Table
546 * Return Status
547 * Purpose : Procedure will perfrom an Delete from the
548 * BOM_Bill_Of_Materials by creating a delete Group.
549 *********************************************************************/
550 PROCEDURE Delete_Row
551 ( p_bom_header_rec IN BOM_Bo_Pub.Bom_Head_Rec_Type
552 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
553 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
554 , x_return_Status IN OUT NOCOPY VARCHAR2
555 )
556 IS
557 Cursor CheckGroup is
558 SELECT description,
559 delete_group_sequence_id,
560 delete_type
561 FROM bom_delete_groups
562 WHERE delete_group_name = p_bom_header_rec.delete_group_name
563 AND organization_id = p_bom_head_unexp_rec.organization_id;
564 l_bom_head_unexp_rec Bom_bo_Pub.Bom_Head_Unexposed_Rec_Type :=
565 p_bom_head_unexp_rec;
566 l_bom_header_rec Bom_bo_Pub.Bom_Head_Rec_Type :=
567 p_bom_header_rec;
568 l_dg_sequence_id NUMBER;
569 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
570 l_assembly_type NUMBER;
571
572
573 BEGIN
574 x_return_status := FND_API.G_RET_STS_SUCCESS;
575
576 FOR DG IN CheckGroup
577 LOOP
578 IF DG.delete_type <> 2 /* Bill */ then
579 Error_Handler.Add_Error_Token
580 ( p_message_name =>
581 'BOM_DUPLICATE_DELETE_GROUP'
582 , p_mesg_token_tbl =>
583 l_mesg_token_Tbl
584 , x_mesg_token_tbl =>
585 l_mesg_token_tbl
586 );
587 x_return_status := FND_API.G_RET_STS_ERROR;
588 x_mesg_token_tbl := l_mesg_token_tbl;
589 RETURN;
590 END IF;
591
592 l_bom_head_unexp_rec.DG_Sequence_Id :=
593 DG.delete_group_sequence_id;
594 l_bom_header_rec.DG_Description := DG.description;
595
596 END LOOP;
597
598 IF l_bom_head_unexp_rec.DG_Sequence_Id <> FND_API.G_MISS_NUM
599 THEN
600 l_dg_sequence_id := l_bom_head_unexp_rec.DG_Sequence_Id;
601 ELSE
602 l_dg_sequence_id := NULL;
603 Error_Handler.Add_Error_Token
604 ( p_message_name => 'NEW_DELETE_GROUP'
605 , p_mesg_token_tbl => l_mesg_token_Tbl
606 , x_mesg_token_tbl => l_mesg_token_tbl
607 , p_message_type => 'W' /* Warning */
608 );
609 END IF;
610
611 --bug 5199643
612 select assembly_type into l_assembly_type
613 from bom_structures_b
614 where bill_sequence_id =l_bom_head_unexp_rec.bill_sequence_id;
615
616 l_dg_sequence_id :=
617 MODAL_DELETE.DELETE_MANAGER
618 ( new_group_seq_id => l_dg_sequence_id,
619 name => l_bom_header_rec.Delete_Group_Name,
620 group_desc => l_bom_header_rec.dg_description,
621 org_id => l_bom_head_unexp_rec.organization_id,
622 bom_or_eng => l_assembly_type /* dg type should be same as bill type */,
623 del_type => 2 /* Bill */,
624 ent_bill_seq_id => l_bom_head_unexp_rec.bill_sequence_id,
625 ent_rtg_seq_id => NULL,
626 ent_inv_item_id => l_bom_head_unexp_rec.assembly_item_id,
627 ent_alt_designator => l_bom_header_rec.alternate_bom_code,
631 );
628 ent_comp_seq_id => NULL,
629 ent_op_seq_id => NULL,
630 user_id => BOM_Globals.Get_User_Id
632
633 x_mesg_token_tbl := l_mesg_token_tbl;
634
635 END Delete_Row;
636
637 /*********************************************************************
638 * Procedure : Perform_Writes
639 * Parameters IN : BOM Header Exposed Column Record
640 * BOM Header Unexposed column record
641 * Parameters OUT: Messgae Token Table
642 * Return Status
643 * Purpose : This is the only procedure that the user will have
644 * access to when he/she needs to perform any kind of
645 * writes to the bom_bill_of_materials table.
646 *********************************************************************/
647 PROCEDURE Perform_Writes
648 ( p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
649 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
650 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
651 , x_return_status IN OUT NOCOPY VARCHAR2
652 )
653 IS
654 l_Mesg_Token_tbl Error_Handler.Mesg_Token_Tbl_Type;
655 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
656 BEGIN
657 IF p_bom_header_rec.transaction_type = BOM_GLOBALS.G_OPR_CREATE
658 THEN
659 Insert_Row
660 ( p_bom_header_rec => p_bom_header_rec
661 , p_bom_head_unexp_rec => p_bom_head_unexp_rec
662 , x_mesg_token_Tbl => l_mesg_token_tbl
663 , x_return_Status => l_return_status
664 );
665 ELSIF p_bom_header_rec.transaction_type =
666 BOM_GLOBALS.G_OPR_UPDATE
667 THEN
668 Update_Row
669 ( p_bom_header_rec => p_bom_header_rec
670 , p_bom_head_unexp_rec => p_bom_head_unexp_rec
671 , x_mesg_token_Tbl => l_mesg_token_tbl
672 , x_return_Status => l_return_status
673 );
674
675 ELSIF p_bom_header_rec.transaction_type =
676 BOM_GLOBALS.G_OPR_DELETE
677 THEN
678 Delete_Row
679 ( p_bom_header_rec => p_bom_header_rec
680 , p_bom_head_unexp_rec => p_bom_head_unexp_rec
681 , x_mesg_token_Tbl => l_mesg_token_tbl
682 , x_return_Status => l_return_status
683 );
684 END IF;
685
686 x_return_status := l_return_status;
687 x_mesg_token_tbl := l_mesg_token_tbl;
688
689 END Perform_Writes;
690
691
692 FUNCTION get_effectivity_control ( item_id NUMBER, org_id NUMBER) return NUMBER is
693
694 l_eff_control NUMBER;
695 BEGIN
696 select effectivity_control into l_eff_control
697 from mtl_system_items_b
698 where inventory_item_id = item_id
699 and organization_id = org_id;
700 return l_eff_control;
701 END;
702 END Bom_Bom_Header_Util;