[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_HEADER_UTIL
Source
1 PACKAGE BODY BOM_Rtg_Header_Util AS
2 /* $Header: BOMURTGB.pls 120.1 2005/08/17 03:27:54 bbpatel noship $*/
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- ENGURTGB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Rtg_Header_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 -- 02-AUGL-2000 Biao Zhang Initial Creation
20 ****************************************************************************/
21
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BOM_Rtg_Header_Util';
23
24 /*********************************************************************
25 * Procedure : Query_Row
26 * Parameters IN : Assembly item id
27 * Organization Id
28 * Alternate_Rtg_Code
29 * Parameters out: Rtg header exposed column record
30 * Rtg Header unexposed column record
31 * Mesg token Table
32 * Return Status
33 * Purpose : Procedure will query the database record, seperate the
34 * values into exposed columns and unexposed columns and
35 * return with those records.
36 ***********************************************************************/
37 PROCEDURE Query_Row
38 ( p_assembly_item_id IN NUMBER
39 , p_organization_id IN NUMBER
40 , p_alternate_routing_code IN VARCHAR2
41 , x_rtg_header_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Rec_Type
42 , x_rtg_header_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_unexposed_Rec_Type
43 , x_Return_status IN OUT NOCOPY VARCHAR2
44 )
45 IS
46 l_rtg_header_rec Bom_Rtg_Pub.Rtg_header_Rec_Type;
47 l_rtg_header_unexp_rec Bom_Rtg_Pub.Rtg_header_Unexposed_Rec_Type;
48 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
49 l_dummy varchar2(10);
50 BEGIN
51
52 SELECT assembly_item_id
53 , organization_id
54 , alternate_routing_designator
55 , routing_sequence_id
56 , routing_type
57 , common_assembly_item_id
58 , common_routing_sequence_id
59 , routing_comment
60 , completion_subinventory
61 , completion_locator_id
62 , line_id
63 , cfm_routing_flag
64 , mixed_model_map_flag
65 , priority
66 , ctp_flag
67 , serialization_start_op -- Added for SSOS (bug 2689249)
68 , attribute_category
69 , attribute1
70 , attribute2
71 , attribute3
72 , attribute4
73 , attribute5
74 , attribute6
75 , attribute7
76 , attribute8
77 , attribute9
78 , attribute10
79 , attribute11
80 , attribute12
81 , attribute13
82 , attribute14
83 , attribute15
84 INTO l_rtg_header_unexp_rec.assembly_item_id
85 , l_rtg_header_unexp_rec.organization_id
86 , l_rtg_header_rec.alternate_routing_code
87 , l_rtg_header_unexp_rec.routing_sequence_id
88 , l_rtg_header_unexp_rec.routing_type
89 , l_rtg_header_unexp_rec.common_assembly_item_id
90 , l_rtg_header_unexp_rec.common_routing_sequence_id
91 , l_rtg_header_rec.routing_comment
92 , l_rtg_header_rec.completion_subinventory
93 , l_rtg_header_unexp_rec.completion_locator_id
94 , l_rtg_header_unexp_rec.line_id
95 , l_rtg_header_rec.cfm_routing_flag
96 , l_rtg_header_rec.mixed_model_map_flag
97 , l_rtg_header_rec.priority
98 , l_rtg_header_rec.ctp_flag
99 , l_rtg_header_rec.ser_start_op_seq -- Added for SSOS (bug 2689249)
100 , l_rtg_header_rec.attribute_category
101 , l_rtg_header_rec.attribute1
102 , l_rtg_header_rec.attribute2
103 , l_rtg_header_rec.attribute3
104 , l_rtg_header_rec.attribute4
105 , l_rtg_header_rec.attribute5
106 , l_rtg_header_rec.attribute6
107 , l_rtg_header_rec.attribute7
108 , l_rtg_header_rec.attribute8
109 , l_rtg_header_rec.attribute9
110 , l_rtg_header_rec.attribute10
111 , l_rtg_header_rec.attribute11
112 , l_rtg_header_rec.attribute12
113 , l_rtg_header_rec.attribute13
114 , l_rtg_header_rec.attribute14
115 , l_rtg_header_rec.attribute15
116 FROM bom_operational_routings
117 WHERE assembly_item_id = p_assembly_item_id
118 AND organization_id = p_organization_id
119 AND NVL(alternate_routing_designator, FND_API.G_MISS_CHAR )
120 = NVL( p_alternate_routing_code, FND_API.G_MISS_CHAR )
121 ;
122
123
124 x_return_status := BOM_Rtg_Globals.G_RECORD_FOUND;
125 x_rtg_header_rec := l_rtg_header_rec;
126 x_rtg_header_unexp_rec := l_rtg_header_unexp_rec;
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 x_return_status := BOM_Rtg_Globals.G_RECORD_NOT_FOUND;
131 x_rtg_header_rec := l_rtg_header_rec;
132 x_rtg_header_unexp_rec := l_rtg_header_unexp_rec;
133 WHEN OTHERS THEN
134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
135 x_rtg_header_rec := l_rtg_header_rec;
136 x_rtg_header_unexp_rec := l_rtg_header_unexp_rec;
137
138 END Query_Row;
139
140 /********************************************************************
141 * Procedure : Insert_Row
142 * Parameters IN : rtg Header exposed column record
143 * rtg Header unexposed column record
144 * Parameters out: Message Token Table
145 * Return Status
146 * Purpose : Procedure will perfrom an insert into the
147 * rtg_Bill_Of_Materials table thus creating a new bill
148 *********************************************************************/
149 PROCEDURE Insert_Row
150 ( p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
151 , p_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
152 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
153 , x_return_Status IN OUT NOCOPY VARCHAR2
154 )
155 IS
156 BEGIN
157
158 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Writing Rtg Header rec for ' || p_rtg_header_rec.assembly_item_name); END IF;
159
160 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
161 INSERT INTO bom_operational_routings
162 ( assembly_item_id
163 , organization_id
164 , alternate_routing_designator
165 , common_assembly_item_id
166 , routing_type
167 , routing_sequence_id
168 , common_routing_sequence_id
169 , completion_subinventory
170 , completion_locator_id
171 , line_id
172 , cfm_routing_flag
173 , mixed_model_map_flag
174 , priority
175 , ctp_flag
176 , total_product_cycle_time
177 , routing_comment
178 , serialization_start_op -- Added for SSOS (bug 2689249)
179 , attribute_category
180 , attribute1
181 , attribute2
182 , attribute3
183 , attribute4
184 , attribute5
185 , attribute6
186 , attribute7
187 , attribute8
188 , attribute9
189 , attribute10
190 , attribute11
191 , attribute12
192 , attribute13
193 , attribute14
194 , attribute15
195 , creation_date
196 , created_by
197 , last_update_date
198 , last_updated_by
199 , last_update_login
200 , original_system_reference
201 , request_id
202 , program_id
203 , program_application_id
204 , program_update_date
205 )
206 VALUES
207 ( p_rtg_header_unexp_rec.assembly_item_id
208 , p_rtg_header_unexp_rec.organization_id
209 , p_rtg_header_rec.alternate_routing_code
210 , p_rtg_header_unexp_rec.common_assembly_item_id
211 , p_rtg_header_unexp_rec.routing_type
212 , p_rtg_header_unexp_rec.routing_sequence_id
213 , p_rtg_header_unexp_rec.common_routing_sequence_id
214 , p_rtg_header_rec.completion_subinventory
215 , p_rtg_header_unexp_rec.completion_locator_id
216 , p_rtg_header_unexp_rec.line_id
217 , p_rtg_header_rec.cfm_routing_flag
218 , p_rtg_header_rec.mixed_model_map_flag
219 , p_rtg_header_rec.priority
220 , p_rtg_header_rec.ctp_flag
221 , p_rtg_header_rec.total_cycle_time
222 , p_rtg_header_rec.routing_comment
223 , p_rtg_header_rec.ser_start_op_seq -- Added for SSOS (bug 2689249)
224 , p_rtg_header_rec.attribute_category
225 , p_rtg_header_rec.attribute1
226 , p_rtg_header_rec.attribute2
227 , p_rtg_header_rec.attribute3
228 , p_rtg_header_rec.attribute4
229 , p_rtg_header_rec.attribute5
230 , p_rtg_header_rec.attribute6
231 , p_rtg_header_rec.attribute7
232 , p_rtg_header_rec.attribute8
233 , p_rtg_header_rec.attribute9
234 , p_rtg_header_rec.attribute10
235 , p_rtg_header_rec.attribute11
236 , p_rtg_header_rec.attribute12
237 , p_rtg_header_rec.attribute13
238 , p_rtg_header_rec.attribute14
239 , p_rtg_header_rec.attribute15
240 , SYSDATE
241 , BOM_Rtg_Globals.Get_User_Id
242 , SYSDATE
243 , BOM_Rtg_Globals.Get_User_Id
244 , BOM_Rtg_Globals.Get_Login_Id
245 , p_rtg_header_rec.original_system_reference
246 , Fnd_Global.Conc_Request_Id
247 , Fnd_Global.Conc_Program_Id
248 , Fnd_Global.Prog_Appl_Id
249 , SYSDATE
250 );
251
252
253 IF p_rtg_header_rec.alternate_routing_code IS NULL
254 AND nvl(Bom_Globals.get_caller_type(),'') <> 'MIGRATION' -- Bug 2871039
255 THEN
256 -- Create a new routing revision for the created primary routing
257 INSERT INTO MTL_RTG_ITEM_REVISIONS
258 ( inventory_item_id
259 , organization_id
260 , process_revision
261 , implementation_date
262 , last_update_date
263 , last_updated_by
264 , creation_date
265 , created_by
266 , last_update_login
267 , effectivity_date
268 , request_id
269 , program_id
270 , program_application_id
271 , program_update_date
272 )
273 SELECT
274 p_rtg_header_unexp_rec.assembly_item_id
275 , p_rtg_header_unexp_rec.organization_id
276 , mp.starting_revision
277 , SYSDATE
278 , SYSDATE
279 , BOM_Rtg_Globals.Get_User_Id
280 , SYSDATE
281 , BOM_Rtg_Globals.Get_User_Id
282 , BOM_Rtg_Globals.Get_Login_Id
283 , SYSDATE
284 , Fnd_Global.Conc_Request_Id
285 , Fnd_Global.Conc_Program_Id
286 , Fnd_Global.Prog_Appl_Id
287 , SYSDATE
288 FROM MTL_PARAMETERS mp
289 WHERE mp.organization_id = p_rtg_header_unexp_rec.organization_id
290 AND NOT EXISTS( SELECT NULL
291 FROM MTL_RTG_ITEM_REVISIONS
292 WHERE implementation_date IS NOT NULL
293 AND organization_id = p_rtg_header_unexp_rec.organization_id
294 AND inventory_item_id = p_rtg_header_unexp_rec.assembly_item_id
295 ) ;
296
297 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
298 ('Creating new routing revision for the created primary routing for the revised item . . . ') ;
299 END IF;
300
301 END IF ;
302
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304
305 EXCEPTION
306 WHEN OTHERS THEN
307 Error_Handler.Add_Error_Token
308 ( p_message_name => NULL
309 , p_message_text => G_PKG_NAME ||
310 ' :Inserting Record ' ||
311 SQLERRM
312 , x_mesg_token_Tbl => x_mesg_token_tbl
313 );
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315
316 END Insert_Row;
317
318 /********************************************************************
319 * Procedure : Update_Row
320 * Parameters IN : RTG Header exposed column record
321 * RTG Header unexposed column record
322 * Parameters out: Message Token Table
323 * Return Status
324 * Purpose : Procedure will perfrom an Update into the
325 * rtg_Bill_Of_Materials table.
326 *********************************************************************/
327 PROCEDURE Update_Row
328 ( p_RTG_header_rec IN Bom_Rtg_Pub.RTG_Header_Rec_Type
329 , p_RTG_header_unexp_rec IN Bom_Rtg_Pub.RTG_Header_Unexposed_Rec_Type
330 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
331 , x_return_Status IN OUT NOCOPY VARCHAR2
332 )
333 IS
334 BEGIN
335
336 --
337 -- The only fields that are updateable in RTG Header are the
338 -- CTP, Priority, completion subinventory, completion_locator,
339 -- comcommon routing information, cfm_routing_flag, mixed_model
340 -- map_flag
341 --
342 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
343 Error_Handler.Write_Debug('Updating routing seq '
344 || p_rtg_header_unexp_rec.routing_sequence_id);
345 END IF;
346
347 UPDATE bom_operational_routings
348 SET common_assembly_item_id =
349 p_rtg_header_unexp_rec.common_assembly_item_id
350 , common_routing_sequence_id =
351 p_rtg_header_unexp_rec.common_routing_sequence_id
352 , ctp_flag = p_rtg_header_rec.ctp_flag
353 , priority = p_rtg_header_rec.priority
354 , line_id = p_rtg_header_unexp_rec.line_id
355 , cfm_routing_flag = p_rtg_header_rec.cfm_routing_flag
356 , mixed_model_map_flag =
357 p_rtg_header_rec.mixed_model_map_flag
358 , completion_subinventory =
359 p_rtg_header_rec.completion_subinventory
360 , completion_locator_id =
361 p_rtg_header_unexp_rec.completion_locator_id
362 , routing_comment =
363 p_rtg_header_rec.routing_comment
364 , total_product_cycle_time =
365 p_rtg_header_rec.total_cycle_time
366 , serialization_start_op =
367 p_rtg_header_rec.ser_start_op_seq -- Added for SSOS (bug 2689249)
368 , last_update_date = SYSDATE
369 , last_updated_by = BOM_Rtg_Globals.Get_User_Id
370 , last_update_login = BOM_Rtg_Globals.Get_Login_Id
371 , attribute_category = p_rtg_header_rec.attribute_category
372 , attribute1 = p_rtg_header_rec.attribute1
373 , attribute2 = p_rtg_header_rec.attribute2
374 , attribute3 = p_rtg_header_rec.attribute3
375 , attribute4 = p_rtg_header_rec.attribute4
376 , attribute5 = p_rtg_header_rec.attribute5
377 , attribute6 = p_rtg_header_rec.attribute6
378 , attribute7 = p_rtg_header_rec.attribute7
379 , attribute8 = p_rtg_header_rec.attribute8
380 , attribute9 = p_rtg_header_rec.attribute9
381 , attribute10= p_rtg_header_rec.attribute10
382 , attribute11= p_rtg_header_rec.attribute11
383 , attribute12= p_rtg_header_rec.attribute12
384 , attribute13= p_rtg_header_rec.attribute13
385 , attribute14= p_rtg_header_rec.attribute14
386 , attribute15= p_rtg_header_rec.attribute15
387 , original_system_reference = p_rtg_header_rec.original_system_reference
388 , request_id = Fnd_Global.Conc_Request_Id
389 , program_id = Fnd_Global.Conc_Program_Id
390 , program_application_id = Fnd_Global.Prog_Appl_Id
391 , program_update_date = SYSDATE
392 WHERE routing_sequence_id =
393 p_rtg_header_unexp_rec.routing_sequence_id
394 ;
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 END Update_Row;
398
399
400 /********************************************************************
401 * Procedure : Delete_Row
402 * Parameters IN : rtg Header exposed column record
403 * rtg Header unexposed column record
404 * Parameters out: Message Token Table
405 * Return Status
406 * Purpose : Procedure will perfrom an Delete from the
407 * rtg_Bill_Of_Materials by creating a delete Group.
408 *********************************************************************/
409 PROCEDURE Delete_Row
410 ( p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
411 , p_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
412 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
413 , x_return_Status IN OUT NOCOPY VARCHAR2
414 )
415 IS
416 Cursor CheckGroup is
417 SELECT description,
418 delete_group_sequence_id,
419 delete_type
420 FROM bom_delete_groups
421 WHERE delete_group_name = p_rtg_header_rec.delete_group_name
422 AND organization_id = p_rtg_header_unexp_rec.organization_id;
423
424 l_rtg_header_unexp_rec Bom_Rtg_Pub.Rtg_Header_Unexposed_Rec_Type
425 := p_rtg_header_unexp_rec;
426 l_rtg_header_rec Bom_Rtg_Pub.rtg_header_Rec_Type
427 := p_rtg_header_rec;
428 l_dg_sequence_id NUMBER;
429 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
430
431 BEGIN
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433
434 FOR DG IN CheckGroup
435 LOOP
436 IF DG.delete_type <> 3 /* Routing */ then
437 Error_Handler.Add_Error_Token
438 ( p_message_name =>
439 'BOM_DUPLICATE_DELETE_GROUP'
440 , p_mesg_token_tbl =>
441 l_mesg_token_Tbl
442 , x_mesg_token_tbl =>
443 l_mesg_token_tbl
444 );
445 x_return_status := FND_API.G_RET_STS_ERROR;
446 x_mesg_token_tbl := l_mesg_token_tbl;
447 RETURN;
448 END IF;
449
450 l_rtg_header_unexp_rec.DG_Sequence_Id :=
451 DG.delete_group_sequence_id;
452 l_rtg_header_rec.DG_Description := DG.description;
453
454 END LOOP;
455
456 IF l_rtg_header_unexp_rec.DG_Sequence_Id <> FND_API.G_MISS_NUM
457 THEN
458 l_dg_sequence_id := l_rtg_header_unexp_rec.DG_Sequence_Id;
459 ELSE
460 l_dg_sequence_id := NULL;
461 Error_Handler.Add_Error_Token
462 ( p_message_name => 'NEW_DELETE_GROUP'
463 , p_mesg_token_tbl => l_mesg_token_Tbl
464 , x_mesg_token_tbl => l_mesg_token_tbl
465 , p_message_type => 'W' /* Warning */
466 );
467 END IF;
468
469
470 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
471 Error_Handler.Write_Debug('Calling MODAL_DELETE.DELETE_MANAGER ') ;
472 Error_Handler.Write_Debug('Rtg_Seq ID ' || to_char(l_rtg_header_unexp_rec.routing_sequence_id) ) ;
473 Error_Handler.Write_Debug('Alt '|| l_rtg_header_rec.alternate_routing_code ) ;
474 Error_Handler.Write_Debug('Routing Type '|| to_char(l_rtg_header_unexp_rec.routing_type) ) ;
475 Error_Handler.Write_Debug('Org'|| to_char(l_rtg_header_unexp_rec.organization_id) ) ;
476
477 END IF;
478
479
480
481 l_dg_sequence_id :=
482 MODAL_DELETE.DELETE_MANAGER
483 ( new_group_seq_id => l_dg_sequence_id,
484 name => l_rtg_header_rec.Delete_Group_Name,
485 group_desc => l_rtg_header_rec.dg_description,
486 org_id => l_rtg_header_unexp_rec.organization_id,
487 bom_or_eng => l_rtg_header_unexp_rec.routing_type,
488 del_type => 3 /* routing */,
489 ent_bill_seq_id => NULL,
490 ent_rtg_seq_id => l_rtg_header_unexp_rec.routing_sequence_id,
491 ent_inv_item_id => l_rtg_header_unexp_rec.assembly_item_id,
492 ent_alt_designator=>
493 l_rtg_header_rec.alternate_routing_code,
494 ent_comp_seq_id => NULL,
495 ent_op_seq_id => NULL,
496 user_id => BOM_Rtg_Globals.Get_User_Id
497 );
498
499 x_mesg_token_tbl := l_mesg_token_tbl;
500
501 END Delete_Row;
502
503 /*********************************************************************
504 * Procedure : Perform_Writes
505 * Parameters IN : Rtg Header Exposed Column Record
506 * Rtg Header Unexposed column record
507 * Parameters out: Messgae Token Table
508 * Return Status
509 * Purpose : This is the only procedure that the user will have
510 * access to when he/she needs to perform any kind of
511 * writes to the bom_operational_routings.
512 *********************************************************************/
513 PROCEDURE Perform_Writes
514 ( p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
515 , p_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
516 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
517 , x_return_status IN OUT NOCOPY VARCHAR2
518 )
519 IS
520 l_Mesg_Token_tbl Error_Handler.Mesg_Token_Tbl_Type;
521 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
522 BEGIN
523 IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
524 THEN
525 Insert_Row
526 ( p_rtg_header_rec => p_rtg_header_rec
527 , p_rtg_header_unexp_rec => p_rtg_header_unexp_rec
528 , x_mesg_token_Tbl => l_mesg_token_tbl
529 , x_return_Status => l_return_status
530 );
531 ELSIF p_rtg_header_rec.transaction_type =
532 BOM_Rtg_Globals.G_OPR_UPDATE
533 THEN
534 Update_Row
535 ( p_rtg_header_rec => p_rtg_header_rec
536 , p_rtg_header_unexp_rec => p_rtg_header_unexp_rec
537 , x_mesg_token_Tbl => l_mesg_token_tbl
538 , x_return_Status => l_return_status
539 );
540
541 ELSIF p_rtg_header_rec.transaction_type =
542 BOM_Rtg_Globals.G_OPR_DELETE
543 THEN
544 Delete_Row
545 ( p_rtg_header_rec => p_rtg_header_rec
546 , p_rtg_header_unexp_rec => p_rtg_header_unexp_rec
547 , x_mesg_token_Tbl => l_mesg_token_tbl
548 , x_return_Status => l_return_status
549 );
550 END IF;
551
552 x_return_status := l_return_status;
553 x_mesg_token_tbl := l_mesg_token_tbl;
554
555 END Perform_Writes;
556
557
558 END BOM_Rtg_Header_Util;