[Home] [Help]
PACKAGE BODY: APPS.BOM_OP_NETWORK_UTIL
Source
1 PACKAGE BODY BOM_Op_Network_UTIL AS
2 /* $Header: BOMUONWB.pls 120.2 2006/03/09 21:49:48 bbpatel noship $*/
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMUONWB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Op_Network_UTIL
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 07-AUG-00 Biao Zhang Initial Creation
21 --
22 ****************************************************************************/
23 G_Pkg_Name VARCHAR2(30) := 'BOM_Op_Network_UTIL';
24 g_token_tbl Error_Handler.Token_Tbl_Type;
25
26
27 /*********************************************************************
28 * Procedure : Query_Row
29 * Parameters IN : Assembly item id
30 * Organization Id
31 * Alternate_Rtg_Code
32 * Parameters OUT: Operation network exposed column record
33 * Operation network unexposed column record
34 * Mesg token Table
35 * Return Status
36 * Purpose : Procedure will query the database record, seperate the
37 * values into exposed columns and unexposed columns and
38 * return with those records.
39 ***********************************************************************/
40 PROCEDURE Query_Row
41 ( p_from_op_seq_id IN NUMBER
42 , p_to_op_seq_id IN NUMBER
43 , x_op_network_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Network_Rec_Type
44 , x_op_network_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Network_unexposed_Rec_Type
45 , x_Return_status IN OUT NOCOPY VARCHAR2
46 )
47 IS
48 l_op_network_rec Bom_Rtg_Pub.op_network_Rec_Type;
49 l_op_network_unexp_rec Bom_Rtg_Pub.op_network_Unexposed_Rec_Type;
50 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
51 l_dummy varchar2(10);
52 BEGIN
53 IF BOM_Rtg_Globals.Get_Debug = 'Y'
54 THEN Error_Handler.Write_Debug('Query row for operation network');
55 END IF;
56
57 SELECT from_op_seq_id
58 , to_op_seq_id
59 , transition_type
60 , planning_pct
61 -- , effecitvity_date
62 -- , disable_date
63 , attribute_category
64 , attribute1
65 , attribute2
66 , attribute3
67 , attribute4
68 , attribute5
69 , attribute6
70 , attribute7
71 , attribute8
72 , attribute9
73 , attribute10
74 , attribute11
75 , attribute12
76 , attribute13
77 , attribute14
78 , attribute15
79 INTO l_op_network_unexp_rec.from_op_seq_id
80 , l_op_network_unexp_rec.to_op_seq_id
81 , l_op_network_rec.connection_type
82 , l_op_network_rec.planning_percent
83 -- , l_op_network_rec.effecitvity_date
84 -- , l_op_network_rec.disable_date
85 , l_op_network_rec.attribute_category
86 , l_op_network_rec.attribute1
87 , l_op_network_rec.attribute2
88 , l_op_network_rec.attribute3
89 , l_op_network_rec.attribute4
90 , l_op_network_rec.attribute5
91 , l_op_network_rec.attribute6
92 , l_op_network_rec.attribute7
93 , l_op_network_rec.attribute8
94 , l_op_network_rec.attribute9
95 , l_op_network_rec.attribute10
96 , l_op_network_rec.attribute11
97 , l_op_network_rec.attribute12
98 , l_op_network_rec.attribute13
99 , l_op_network_rec.attribute14
100 , l_op_network_rec.attribute15
101 FROM bom_operation_networks
102 WHERE from_op_seq_id = p_from_op_seq_id
103 AND to_op_seq_id = p_to_op_seq_id;
104
105 x_return_status := BOM_Rtg_Globals.G_RECORD_FOUND;
106 x_op_network_rec := l_op_network_rec;
107 x_op_network_unexp_rec := l_op_network_unexp_rec;
108
109 EXCEPTION
110 WHEN NO_DATA_FOUND THEN
111 x_return_status := BOM_Rtg_Globals.G_RECORD_NOT_FOUND;
112 x_op_network_rec := l_op_network_rec;
113 x_op_network_unexp_rec := l_op_network_unexp_rec;
114 WHEN OTHERS THEN
115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
116 x_op_network_rec := l_op_network_rec;
117 x_op_network_unexp_rec := l_op_network_unexp_rec;
118
119 END Query_Row;
120
121 /********************************************************************
122 * Procedure : Insert_Row
123 * Parameters IN : Operation networker exposed column record
124 * Operation networker unexposed column record
125 * Parameters OUT: Message Token Table
126 * Return Status
127 * Purpose : Procedure will perfrom an insert into the
128 * rtg_Bill_Of_Materials table thus creating a new bill
129 *********************************************************************/
130 PROCEDURE Insert_Row
131 ( p_op_network_rec IN Bom_Rtg_Pub.op_network_Rec_Type
132 , p_op_network_unexp_rec IN Bom_Rtg_Pub.op_network_Unexposed_Rec_Type
133 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
134 , x_return_Status IN OUT NOCOPY VARCHAR2
135 )
136 IS
137 p_start_effectivity_date DATE;
138 p_implementation_date DATE;
139
140 BEGIN
141
142 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
143 Error_Handler.Write_Debug('Writing Operation networker rec for '
144 || p_op_network_rec.assembly_item_name);
145 END IF;
146
147 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
148 INSERT INTO bom_operation_networks
149 ( from_op_seq_id
150 , to_op_seq_id
151 , transition_type
152 , planning_pct
153 -- , effectivity_date
154 -- , disable_date
155 , attribute_category
156 , attribute1
157 , attribute2
158 , attribute3
159 , attribute4
160 , attribute5
161 , attribute6
162 , attribute7
163 , attribute8
164 , attribute9
165 , attribute10
166 , attribute11
167 , attribute12
168 , attribute13
169 , attribute14
170 , attribute15
171 , creation_date
172 , created_by
173 , last_update_date
174 , last_updated_by
175 , last_update_login
176 , original_system_reference -- Added by MK 05/01
177 , request_id
178 , program_id
179 , program_application_id
180 , program_update_date
181 )
182 VALUES
183 ( p_op_network_unexp_rec.from_op_seq_id
184 , p_op_network_unexp_rec.to_op_seq_id
185 , p_op_network_rec.connection_type
186 , p_op_network_rec.planning_percent
187 -- , NULL
188 -- , NULL
189 , p_op_network_rec.attribute_category
190 , p_op_network_rec.attribute1
191 , p_op_network_rec.attribute2
192 , p_op_network_rec.attribute3
193 , p_op_network_rec.attribute4
194 , p_op_network_rec.attribute5
195 , p_op_network_rec.attribute6
196 , p_op_network_rec.attribute7
197 , p_op_network_rec.attribute8
198 , p_op_network_rec.attribute9
199 , p_op_network_rec.attribute10
200 , p_op_network_rec.attribute11
201 , p_op_network_rec.attribute12
202 , p_op_network_rec.attribute13
203 , p_op_network_rec.attribute14
204 , p_op_network_rec.attribute15
205 , SYSDATE
206 , BOM_Rtg_Globals.Get_User_Id
207 , SYSDATE
208 , BOM_Rtg_Globals.Get_User_Id
209 , BOM_Rtg_Globals.Get_Login_Id
210 , p_op_network_rec.original_system_reference -- Added by MK 05/01
211 , Fnd_Global.Conc_Request_Id
212 , Fnd_Global.Conc_Program_Id
213 , Fnd_Global.Prog_Appl_Id
214 , SYSDATE
215 );
216 -- Update bom_operation sequences to set the X and Y coordinates
217 -- for the operations involved in this network.
218 update BOM_OPERATION_SEQUENCES set
219 X_COORDINATE = p_op_network_rec.From_X_Coordinate
220 ,Y_COORDINATE = p_op_network_rec.From_Y_Coordinate
221 , last_update_date = SYSDATE
222 , last_updated_by = BOM_Rtg_Globals.Get_User_Id
223 , last_update_login = BOM_Rtg_Globals.Get_Login_Id
224 , request_id = Fnd_Global.Conc_Request_Id
225 , program_id = Fnd_Global.Conc_Program_Id
226 , program_application_id = Fnd_Global.Prog_Appl_Id
227 , program_update_date = SYSDATE
228 where operation_sequence_id =
229 p_op_network_unexp_rec.from_op_seq_id;
230
231 update BOM_OPERATION_SEQUENCES set
232 X_COORDINATE = p_op_network_rec.To_X_Coordinate
233 ,Y_COORDINATE = p_op_network_rec.To_Y_Coordinate
234 , last_update_date = SYSDATE
235 , last_updated_by = BOM_Rtg_Globals.Get_User_Id
236 , last_update_login = BOM_Rtg_Globals.Get_Login_Id
237 , request_id = Fnd_Global.Conc_Request_Id
238 , program_id = Fnd_Global.Conc_Program_Id
239 , program_application_id = Fnd_Global.Prog_Appl_Id
240 , program_update_date = SYSDATE
241 where operation_sequence_id =
242 p_op_network_unexp_rec.to_op_seq_id;
243
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 Error_Handler.Add_Error_Token
249 ( p_message_name => NULL
250 , p_message_text => G_PKG_NAME ||
251 ' :Inserting Record ' ||
252 SQLERRM
253 , x_mesg_token_Tbl => x_mesg_token_tbl
254 );
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256
257 END Insert_Row;
258
259 /********************************************************************
260 * Procedure : Update_Row
261 * Parameters IN : Operation networker exposed column record
262 * Operation networker unexposed column record
263 * Parameters OUT: Message Token Table
264 * Return Status
265 * Purpose : Procedure will perfrom an Update into the
266 * rtg_Bill_Of_Materials table.
267 *********************************************************************/
268 PROCEDURE Update_Row
269 ( p_op_network_rec IN Bom_Rtg_Pub.op_network_Rec_Type
270 , p_op_network_unexp_rec IN Bom_Rtg_Pub.op_network_Unexposed_Rec_Type
271 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
272 , x_return_Status IN OUT NOCOPY VARCHAR2
273 )
274 IS
275 p_start_effectivity_date DATE;
276 p_implementation_date DATE;
277 BEGIN
278
279 --
280 -- The fields that are updateable in Operation networker
281 -- are...
282 --
283
284 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
285 Error_Handler.Write_Debug('Updating operation network ');
286 END IF;
287
288 UPDATE bom_operation_networks
289 SET
290 from_op_seq_id = DECODE(p_op_network_unexp_rec.new_from_op_seq_id,
291 NULL , p_op_network_unexp_rec.from_op_seq_id,
292 p_op_network_unexp_rec.new_from_op_seq_id
293 )
294 , to_op_seq_id = DECODE(p_op_network_unexp_rec.new_to_op_seq_id,
295 NULL , p_op_network_unexp_rec.to_op_seq_id,
296 p_op_network_unexp_rec.new_to_op_seq_id
297 )
298 , planning_pct = p_op_network_rec.planning_percent
299 , transition_type =p_op_network_rec.connection_type
300 , last_update_date = SYSDATE
301 , last_updated_by = BOM_Rtg_Globals.Get_User_Id
302 , last_update_login = BOM_Rtg_Globals.Get_Login_Id
303 , attribute_category =p_op_network_rec.attribute_category
304 , attribute1 = p_op_network_rec.attribute1
305 , attribute2 = p_op_network_rec.attribute2
306 , attribute3 = p_op_network_rec.attribute3
307 , attribute4 = p_op_network_rec.attribute4
308 , attribute5 = p_op_network_rec.attribute5
309 , attribute6 = p_op_network_rec.attribute6
310 , attribute7 = p_op_network_rec.attribute7
311 , attribute8 = p_op_network_rec.attribute8
312 , attribute9 = p_op_network_rec.attribute9
313 , attribute10= p_op_network_rec.attribute10
314 , attribute11= p_op_network_rec.attribute11
315 , attribute12= p_op_network_rec.attribute12
316 , attribute13= p_op_network_rec.attribute13
317 , attribute14= p_op_network_rec.attribute14
318 , attribute15= p_op_network_rec.attribute15
319 , original_system_reference = p_op_network_rec.original_system_reference
320 , request_id = Fnd_Global.Conc_Request_Id
321 , program_id = Fnd_Global.Conc_Program_Id
322 , program_application_id = Fnd_Global.Prog_Appl_Id
323 , program_update_date = SYSDATE
324 WHERE from_op_seq_id = p_op_network_unexp_rec.from_op_seq_id
325 AND to_op_seq_id = p_op_network_unexp_rec.to_op_seq_id;
326
327 -- Update bom_operation sequences to set the X and Y coordinates
328 -- for the operations involved in this network.
329 update BOM_OPERATION_SEQUENCES set
330 X_COORDINATE = p_op_network_rec.From_X_Coordinate
334 , last_update_login = BOM_Rtg_Globals.Get_Login_Id
331 ,Y_COORDINATE = p_op_network_rec.From_Y_Coordinate
332 , last_update_date = SYSDATE
333 , last_updated_by = BOM_Rtg_Globals.Get_User_Id
335 , request_id = Fnd_Global.Conc_Request_Id
336 , program_id = Fnd_Global.Conc_Program_Id
337 , program_application_id = Fnd_Global.Prog_Appl_Id
338 , program_update_date = SYSDATE
339 where operation_sequence_id =
340 DECODE(p_op_network_unexp_rec.new_from_op_seq_id,
341 NULL , p_op_network_unexp_rec.from_op_seq_id,
342 p_op_network_unexp_rec.new_from_op_seq_id);
343
344
345 update BOM_OPERATION_SEQUENCES set
346 X_COORDINATE = p_op_network_rec.To_X_Coordinate
347 ,Y_COORDINATE = p_op_network_rec.To_Y_Coordinate
348 , last_update_date = SYSDATE
349 , last_updated_by = BOM_Rtg_Globals.Get_User_Id
350 , last_update_login = BOM_Rtg_Globals.Get_Login_Id
351 , request_id = Fnd_Global.Conc_Request_Id
352 , program_id = Fnd_Global.Conc_Program_Id
353 , program_application_id = Fnd_Global.Prog_Appl_Id
354 , program_update_date = SYSDATE
355 where operation_sequence_id =
356 DECODE(p_op_network_unexp_rec.new_to_op_seq_id,
357 NULL , p_op_network_unexp_rec.to_op_seq_id,
358 p_op_network_unexp_rec.new_to_op_seq_id);
359
360 END Update_Row;
361
362
363 /********************************************************************
364 * Procedure : Delete_Row
365 * Parameters IN : Operation networker exposed column record
366 * Operation networker unexposed column record
367 * Parameters OUT: Message Token Table
368 * Return Status
369 * Purpose : Procedure will perfrom an Delete from the
370 * rtg_Bill_Of_Materials by creating a delete Group.
371 *********************************************************************/
372 PROCEDURE Delete_Row
373 ( p_op_network_rec IN Bom_Rtg_Pub.op_network_Rec_Type
374 , p_op_network_unexp_rec IN Bom_Rtg_Pub.op_network_Unexposed_Rec_Type
375 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
376 , x_return_Status IN OUT NOCOPY VARCHAR2
377 )
378 IS
379 l_op_network_unexp_rec Bom_Rtg_Pub.op_network_Unexposed_Rec_Type := p_op_network_unexp_rec;
380 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
381
382 BEGIN
383 x_return_status := FND_API.G_RET_STS_SUCCESS;
384
385 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
386 Error_Handler.Write_Debug('Delete operation network.'
387 );
388 END IF;
389
390 DELETE FROM bom_operation_networks
391 WHERE from_op_seq_id = p_op_network_unexp_rec.from_op_seq_id
392 AND to_op_seq_id = p_op_network_unexp_rec.to_op_seq_id;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 Error_Handler.Add_Error_Token
397 ( p_Message_Name => NULL
398 , p_Message_Text => 'ERROR in Delete Operation Network:'
399 || substr(SQLERRM, 1, 100) || ' ' ||
400 to_char(SQLCODE)
401 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
402 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
403 );
404 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
405 x_mesg_token_tbl := l_mesg_token_tbl;
406
407 END Delete_Row;
408
409 /*********************************************************************
410 * Procedure : Perform_Writes
411 * Parameters IN : Operation network Exposed Column Record
412 * Operation network Unexposed column record
413 * Parameters OUT: Messgae Token Table
414 * Return Status
415 * Purpose : This is the only procedure that the user will have
416 * access to when he/she needs to perform any kind of
417 * writes to the bom_operational_routings.
418 *********************************************************************/
419 PROCEDURE Perform_Writes
420 ( p_Op_Network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
421 , p_Op_Network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
422 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
423 , x_return_status IN OUT NOCOPY VARCHAR2
424 )
425 IS
426 l_Mesg_Token_tbl Error_Handler.Mesg_Token_Tbl_Type;
427 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
428 l_err_text VARCHAR2(2000) ;
429
430 BEGIN
431 IF p_op_network_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
432 THEN
433 Insert_Row
434 ( p_op_network_rec => p_op_network_rec
438 );
435 , p_op_network_unexp_rec => p_op_network_unexp_rec
436 , x_mesg_token_Tbl => l_mesg_token_tbl
437 , x_return_Status => l_return_status
439 ELSIF p_op_network_rec.transaction_type =
440 Bom_Rtg_Globals.G_OPR_UPDATE
441 THEN
442 Update_Row
443 ( p_op_network_rec => p_op_network_rec
444 , p_op_network_unexp_rec => p_op_network_unexp_rec
445 , x_mesg_token_Tbl => l_mesg_token_tbl
446 , x_return_Status => l_return_status
447 );
448
449 ELSIF p_op_network_rec.transaction_type =
450 BOM_Rtg_Globals.G_OPR_DELETE
451 THEN
452 Delete_Row
453 ( p_op_network_rec => p_op_network_rec
454 , p_op_network_unexp_rec => p_op_network_unexp_rec
455 , x_mesg_token_Tbl => l_mesg_token_tbl
456 , x_return_Status => l_return_status
457 );
458 END IF;
459
460 x_return_status := l_return_status;
461 x_mesg_token_tbl := l_mesg_token_tbl;
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
466 ('Some unknown error in Perform Writes . . .' || SQLERRM );
467 END IF ;
468
469
470 l_err_text := G_PKG_NAME || ' Utility (Perform Writes) '
471 || substrb(SQLERRM,1,200);
472
473 Error_Handler.Add_Error_Token
474 ( p_message_name => NULL
475 , p_message_text => l_err_text
476 , p_mesg_token_tbl => l_mesg_token_tbl
477 , x_mesg_token_tbl => l_mesg_token_tbl
478 ) ;
479
480 -- Return the status and message table.
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
482 x_mesg_token_tbl := l_mesg_token_tbl ;
483
484 END Perform_Writes;
485
486 /*********************************************************************
487 * Procedure : Get_WSM_Netowrk_Attribs
488 * Parameters IN : Assembly item id
489 * Organization Id
490 * Alternate_Rtg_Code
491 * Parameters OUT:
492 * previous start operation id
493 * previous end operation id
494 * Mesg token Table
495 * Return Status
496 * Purpose : Procedure will query start and end operation of the
497 * entire routing and return those
498 ***********************************************************************/
499 PROCEDURE Get_WSM_Netowrk_Attribs
500 ( p_routing_sequence_id IN NUMBER
501 , x_prev_start_id IN OUT NOCOPY NUMBER
502 , x_prev_end_id IN OUT NOCOPY NUMBER
503 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
504 , x_Return_status IN OUT NOCOPY VARCHAR2
505 )
506 IS
507 l_routing_sequence_id NUMBER :=0;
508 l_common_routing_sequence_id NUMBER :=0;
509 l_cfm_routing_flag NUMBER :=0;
510 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type ;
511 err_code NUMBER;
512 err_msg VARCHAR2(2000);
513 BEGIN
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515
516 select routing_sequence_id, cfm_routing_flag
517 into l_routing_sequence_id,l_cfm_routing_flag
518 from bom_operational_routings where
519 routing_sequence_id = p_routing_sequence_id;
520 -- Get Common Routing Seq Id from System Info Rec.
521 -- If the value is Null, set Common Routing Seq Id.
522 l_common_routing_sequence_id := BOM_Rtg_Globals.Get_Common_Rtg_Seq_id ;
523
524 IF l_common_routing_sequence_id IS NULL OR
525 l_common_routing_sequence_id = FND_API.G_MISS_NUM THEN
526 BEGIN
527 SELECT common_routing_sequence_id
528 INTO l_common_routing_sequence_id
529 FROM bom_operational_routings
530 WHERE routing_sequence_id = l_routing_sequence_id;
531 END ;
532 BOM_Rtg_Globals.Set_Common_Rtg_Seq_id
533 ( p_common_rtg_seq_id => l_common_routing_sequence_id );
534 END IF;
535 IF nvl(l_cfm_routing_flag,2) = 3 THEN
536 WSMPUTIL.FIND_ROUTING_START(l_common_routing_sequence_id,
537 x_prev_start_id,
538 err_code,
539 err_msg );
540
541 IF err_code = -1 THEN --for OSFM routings
542 RETURN;
543 END IF;
544
545 IF err_msg IS NOT NULL THEN
546
547 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
548 Error_Handler.Add_Error_Token
549 ( p_message_name => NULL
550 , p_message_text => err_msg
551 , p_mesg_token_tbl => l_mesg_token_tbl
552 , x_mesg_token_tbl => l_mesg_token_tbl
553 ) ;
554
555 END IF;
559
556 x_return_status := FND_API.G_RET_STS_ERROR ;
557 RETURN;
558 END IF;
560 WSMPUTIL.FIND_ROUTING_END(l_common_routing_sequence_id,
561 x_prev_end_id,
562 err_code,
563 err_msg );
564
565 IF err_code = -1 THEN --for OSFM routings
566 RETURN;
567 END IF;
568
569 IF err_msg IS NOT NULL THEN
570
571 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
572 Error_Handler.Add_Error_Token
573 ( p_message_name => NULL
574 , p_message_text => err_msg
575 , p_mesg_token_tbl => l_mesg_token_tbl
576 , x_mesg_token_tbl => l_mesg_token_tbl
577 ) ;
578
579 END IF;
580 x_return_status := FND_API.G_RET_STS_ERROR ;
581 RETURN;
582 END IF;
583 END IF;
584
585
586 -- Return messages
587 x_mesg_token_tbl := l_mesg_token_tbl ;
588 END Get_WSM_Netowrk_Attribs;
589
590 /*********************************************************************
591 * Procedure : Set_WSM_Network_Sub_Loc
592 * Parameters IN : Assembly item id
593 * Organization Id
594 * Alternate_Rtg_Code
595 * end operation id
596 * Parameters OUT:
597 * Mesg token Table
598 * Return Status
599 * Purpose : Procedure checks and then sets the sub inventory and
600 * locator for the OSFM routing
601 ***********************************************************************/
602 /**********************************************************************
603 * The logic : we get assembly's default sub inv and loc. *
604 * Then we get the last op's sinv/loc. If these are null then we check*
605 * if routing has sub-inv/loc. If not then we check item's default *
606 * subinv/loc. *
607 * If nothing then error, else, *
608 * if item has then take those and put at the routing level. *
609 * If last op's values are not null, then *
610 * if routing values are null we set those to these. *
611 * But if routing values are NOT null then we check followin : *
612 * a. If sub-inv are same then we tell user that they are same and if *
613 * you want these to change to one of op's do so in the forms *
614 * interface. *
615 * b. If loc are the same , similar message. *
616 **********************************************************************/
617
618 PROCEDURE Set_WSM_Network_Sub_Loc
619 ( p_routing_sequence_id IN NUMBER
620 , p_end_id IN NUMBER
621 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
622 , x_Return_status IN OUT NOCOPY VARCHAR2
623 )
624 IS
625 l_item_compl_subinv varchar2(10);
626 l_item_loc_id NUMBER;
627 err_code NUMBER;
628 err_msg VARCHAR2(2000);
629 l_completion_subinventory VARCHAR2(10);
630 l_rtg_comp_sub_inv VARCHAR2(10);
631 l_rtg_comp_sub_loc NUMBER;
632 l_inventory_location_id NUMBER;
633 l_routing_sequence_id NUMBER :=0;
634 l_common_routing_sequence_id NUMBER :=0;
635 l_cfm_routing_flag NUMBER :=0;
636 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type ;
637 l_assembly_item_id NUMBER :=0;
638 l_org_id NUMBER :=0;
639 actual_end_id NUMBER; --for bug3134027
640 BEGIN
641 x_return_status := FND_API.G_RET_STS_SUCCESS;
642
643 select routing_sequence_id, cfm_routing_flag,
644 completion_subinventory, completion_locator_id,
645 assembly_item_id , organization_id
646 into l_routing_sequence_id,l_cfm_routing_flag,
647 l_rtg_comp_sub_inv , l_rtg_comp_sub_loc,
648 l_assembly_item_id , l_org_id
649 from bom_operational_routings where
650 routing_sequence_id = p_routing_sequence_id;
651
652 -- Get Common Routing Seq Id from System Info Rec.
653 -- If the value is Null, set Common Routing Seq Id.
654 l_common_routing_sequence_id := BOM_Rtg_Globals.Get_Common_Rtg_Seq_id ;
655
656 IF l_common_routing_sequence_id IS NULL OR
657 l_common_routing_sequence_id = FND_API.G_MISS_NUM THEN
658 BEGIN
659 SELECT common_routing_sequence_id
660 INTO l_common_routing_sequence_id
661 FROM bom_operational_routings
662 WHERE routing_sequence_id = l_routing_sequence_id;
663 END ;
664 BOM_Rtg_Globals.Set_Common_Rtg_Seq_id
665 ( p_common_rtg_seq_id => l_common_routing_sequence_id );
666 END IF;
667
668 IF nvl(l_cfm_routing_flag,2) = 3 THEN
669 WSMPUTIL.FIND_ROUTING_END(l_common_routing_sequence_id, --for bug3134027
670 actual_end_id,
671 err_code,
672 err_msg);
673 -- Get the Assembly item's completion sub-inv and loc_id
674 SELECT wip_supply_subinventory, wip_supply_locator_id
675 INTO l_item_compl_subinv, l_item_loc_id
679
676 FROM mtl_system_items
677 WHERE organization_id = l_org_id
678 AND inventory_item_id =l_assembly_item_id;
680 err_code := 0;
681 err_msg := NULL;
682 l_completion_subinventory := NULL;
683
684 WSMPUTIL.GET_DEFAULT_SUB_LOC
685 (l_org_id,
686 l_common_routing_sequence_id,
687 actual_end_id, --for bug3134027
688 l_completion_subinventory,
689 l_inventory_location_id,
690 err_code,
691 err_msg );
692
693 IF err_msg IS NOT NULL THEN
694 -- Last op doesn't have a completion sub-inv defined
695
696 IF l_rtg_comp_sub_inv is NULL THEN
697 -- No Manual Entry for comp. sub-inv
698
699 IF l_item_compl_subinv IS NULL THEN
700 -- Comple. sub-inv for assy item is also not defined
701 Error_Handler.Add_Error_Token
702 ( p_message_name => NULL
703 , p_message_text => err_msg
704 , p_mesg_token_tbl => l_mesg_token_tbl
705 , x_mesg_token_tbl => l_mesg_token_tbl
706 ) ;
707 x_return_status := FND_API.G_RET_STS_ERROR ;
708 x_mesg_token_tbl := l_mesg_token_tbl;
709 RETURN;
710 ELSE
711 l_rtg_comp_sub_inv := l_item_compl_subinv;
712 l_rtg_comp_sub_loc := l_item_loc_id;
713 END IF;
714 END IF;
715 ELSE -- Last op HAS a completion sub-inv
716 IF l_rtg_comp_sub_inv IS NULL THEN
717 -- No Manual Entry for comp. sub-inv
718 l_rtg_comp_sub_inv := l_completion_subinventory;
719 l_rtg_comp_sub_loc := l_inventory_location_id;
720
721 ELSIF l_completion_subinventory <>
722 l_rtg_comp_sub_inv THEN
723 Error_Handler.Add_Error_Token
724 ( p_message_name => 'BOM_RBO_WSM_NTWK_OP_RTG_DIF_SI'
725 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
726 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
727 , p_message_type => 'W'
728 ) ;
729 ELSIF nvl( l_inventory_location_id ,
730 l_rtg_comp_sub_loc ) <>
731 l_rtg_comp_sub_loc THEN
732 Error_Handler.Add_Error_Token
733 ( p_message_name => 'BOM_RBO_WSM_NTWK_OP_RTG_DIF_LC'
734 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
735 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
736 , p_message_type => 'W'
737 ) ;
738 END IF ;
739 END IF;
740
741 err_code := 0;
742 err_msg := NULL;
743
744 WSMPUTIL.UPDATE_SUB_LOC ( l_common_routing_sequence_id,
745 l_rtg_comp_sub_inv,
746 l_rtg_comp_sub_loc,
747 err_code ,
748 err_msg ) ;
749
750 IF err_msg IS NOT NULL THEN
751 Error_Handler.Add_Error_Token
752 ( p_message_name => NULL
753 , p_message_text => err_msg
754 , p_mesg_token_tbl => l_mesg_token_tbl
755 , x_mesg_token_tbl => l_mesg_token_tbl
756 ) ;
757 x_return_status := FND_API.G_RET_STS_ERROR ;
758 RETURN;
759 END IF;
760 END IF;
761 x_mesg_token_tbl := l_mesg_token_tbl;
762 END Set_WSM_Network_Sub_Loc;
763
764
765 -- bug:5060186 Added two procedures Copy_First_Last_Dis_Op and Copy_Operation
766 -- to copy the first or last operation of the network if disabled.
767 /*#
768 * Procedure to copy the disabled first or last operation of the network.
769 * @param p_routing_sequence_id Routing Sequence Id
770 * @param x_Mesg_Token_Tbl Message Token Table
771 * @rep:paraminfo { @rep:innertype Error_Handler.Mesg_Token_Tbl_Type }
772 * @param x_Return_status Return Status
773 * @rep:scope private
774 * @rep:lifecycle active
775 * @rep:displayname Copy First Last Disabled Operation
776 */
777 PROCEDURE Copy_First_Last_Dis_Op
778 ( p_routing_sequence_id IN NUMBER
782 IS
779 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
780 , x_return_status IN OUT NOCOPY VARCHAR2
781 )
783 -- Get the First Operation of the network ordered by descending effectivity date
784 CURSOR First_Op_Of_Nw( c_routing_sequence_id NUMBER ) IS
785 SELECT bos.OPERATION_SEQUENCE_ID, bos.DISABLE_DATE
786 FROM BOM_OPERATION_SEQUENCES bos
787 WHERE
788 bos.IMPLEMENTATION_DATE IS NOT NULL
789 AND bos.OPERATION_SEQ_NUM IN
790 ( SELECT bos_first.OPERATION_SEQ_NUM
791 FROM
792 BOM_OPERATION_NETWORKS bon_from,
793 BOM_OPERATION_NETWORKS bon_to,
794 BOM_OPERATION_SEQUENCES bos_first
795 WHERE
796 bon_from.FROM_OP_SEQ_ID = bos_first.OPERATION_SEQUENCE_ID
797 AND bon_from.TO_OP_SEQ_ID = bon_to.FROM_OP_SEQ_ID
798 AND bon_from.FROM_OP_SEQ_ID <> bon_to.FROM_OP_SEQ_ID
799 AND bos_first.IMPLEMENTATION_DATE IS NOT NULL
800 AND bos_first.ROUTING_SEQUENCE_ID = c_routing_sequence_id )
801 AND bos.ROUTING_SEQUENCE_ID = c_routing_sequence_id
802 ORDER BY bos.EFFECTIVITY_DATE DESC;
803
804 -- Get the Last Operation of the network ordered by descending effectivity date
805 CURSOR Last_Op_Of_Nw( c_routing_sequence_id NUMBER ) IS
806 SELECT bos.OPERATION_SEQUENCE_ID, bos.DISABLE_DATE
807 FROM BOM_OPERATION_SEQUENCES bos
808 WHERE
809 bos.IMPLEMENTATION_DATE IS NOT NULL
810 AND bos.OPERATION_SEQ_NUM IN
811 ( SELECT bos_last.OPERATION_SEQ_NUM
812 FROM
813 BOM_OPERATION_NETWORKS bon_from,
814 BOM_OPERATION_NETWORKS bon_to,
815 BOM_OPERATION_SEQUENCES bos_last
816 WHERE
817 bon_to.TO_OP_SEQ_ID = bos_last.OPERATION_SEQUENCE_ID
818 AND bon_from.TO_OP_SEQ_ID = bon_to.FROM_OP_SEQ_ID
819 AND bon_to.TO_OP_SEQ_ID <> bon_from.TO_OP_SEQ_ID
820 AND bos_last.IMPLEMENTATION_DATE IS NOT NULL
821 AND bos_last.ROUTING_SEQUENCE_ID = c_routing_sequence_id )
822 AND bos.ROUTING_SEQUENCE_ID = c_routing_sequence_id
823 ORDER BY bos.EFFECTIVITY_DATE DESC;
824
825 BEGIN
826 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
827 Error_Handler.Write_Debug( ' Copying the First/Last operation if disabled');
828 END IF;
829
830 FOR l_operation IN First_Op_Of_Nw( p_routing_sequence_id )
831 LOOP
832 IF ( l_operation.DISABLE_DATE IS NOT NULL ) THEN
833 -- First Operation is disabled, copy the operation
834 Copy_Operation ( l_operation.OPERATION_SEQUENCE_ID );
835 EXIT;
836 ELSE
837 -- First Operation is not disabled
838 EXIT;
839 END IF;
840 END LOOP;
841
842 FOR l_operation IN Last_Op_Of_Nw( p_routing_sequence_id )
843 LOOP
844 IF ( l_operation.DISABLE_DATE IS NOT NULL ) THEN
845 -- Last Operation is disabled, copy the operation
846 Copy_Operation ( l_operation.OPERATION_SEQUENCE_ID );
847 EXIT;
848 ELSE
849 -- Last Operation is not disabled
850 EXIT;
851 END IF;
852 END LOOP;
853
854 x_return_status := FND_API.G_RET_STS_SUCCESS;
855
856 END Copy_First_Last_Dis_Op;
857
858 /*#
859 * Procedure to copy the operation with new effectivity date as (disable date + 1 sec)
860 * Also copy resources and alternate resources.
861 *
862 * @param p_operation_sequence_id Operation Sequence Id
863 * @rep:scope private
864 * @rep:lifecycle active
865 * @rep:displayname Copy Operation
866 */
867 PROCEDURE Copy_Operation ( p_operation_sequence_id IN NUMBER )
868 IS
869 l_op_seq_id NUMBER;
870 BEGIN
871
872 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
873 Error_Handler.Write_Debug( ' Copying operation ' || p_operation_sequence_id );
874 END IF;
875
876 -- Get a new operation sequence id
877 SELECT BOM_OPERATION_SEQUENCES_S.NEXTVAL
878 INTO l_op_seq_id
879 FROM DUAL;
880
881 -- Copy the disabled operation with effectivity date as disable date plus one minute
882 INSERT INTO BOM_OPERATION_SEQUENCES
883 (
884 OPERATION_SEQUENCE_ID,
885 ROUTING_SEQUENCE_ID,
886 OPERATION_SEQ_NUM,
887 LAST_UPDATE_DATE,
888 LAST_UPDATED_BY,
889 CREATION_DATE,
890 CREATED_BY,
891 LAST_UPDATE_LOGIN,
892 STANDARD_OPERATION_ID,
893 DEPARTMENT_ID,
894 OPERATION_LEAD_TIME_PERCENT,
895 MINIMUM_TRANSFER_QUANTITY,
896 COUNT_POINT_TYPE,
897 OPERATION_DESCRIPTION,
898 EFFECTIVITY_DATE,
899 DISABLE_DATE,
900 BACKFLUSH_FLAG,
901 OPTION_DEPENDENT_FLAG,
902 ATTRIBUTE_CATEGORY,
903 ATTRIBUTE1,
904 ATTRIBUTE2,
905 ATTRIBUTE3,
906 ATTRIBUTE4,
907 ATTRIBUTE5,
908 ATTRIBUTE6,
909 ATTRIBUTE7,
910 ATTRIBUTE8,
911 ATTRIBUTE9,
912 ATTRIBUTE10,
913 ATTRIBUTE11,
914 ATTRIBUTE12,
915 ATTRIBUTE13,
916 ATTRIBUTE14,
917 ATTRIBUTE15,
918 REQUEST_ID,
919 PROGRAM_APPLICATION_ID,
923 REFERENCE_FLAG,
920 PROGRAM_ID,
921 PROGRAM_UPDATE_DATE,
922 OPERATION_TYPE,
924 PROCESS_OP_SEQ_ID,
925 LINE_OP_SEQ_ID,
926 YIELD,
927 CUMULATIVE_YIELD,
928 REVERSE_CUMULATIVE_YIELD,
929 LABOR_TIME_CALC,
930 MACHINE_TIME_CALC,
931 TOTAL_TIME_CALC,
932 LABOR_TIME_USER,
933 MACHINE_TIME_USER,
934 TOTAL_TIME_USER,
935 NET_PLANNING_PERCENT,
936 INCLUDE_IN_ROLLUP,
937 OPERATION_YIELD_ENABLED,
938 CHANGE_NOTICE,
939 IMPLEMENTATION_DATE,
940 SHUTDOWN_TYPE,
941 LONG_DESCRIPTION,
942 LOWEST_ACCEPTABLE_YIELD,
943 USE_ORG_SETTINGS,
944 QUEUE_MANDATORY_FLAG,
945 RUN_MANDATORY_FLAG,
946 TO_MOVE_MANDATORY_FLAG,
947 SHOW_NEXT_OP_BY_DEFAULT,
948 SHOW_SCRAP_CODE,
949 SHOW_LOT_ATTRIB,
950 TRACK_MULTIPLE_RES_USAGE_DATES
951 )
952 SELECT
953 l_op_seq_id,
954 BOS.ROUTING_SEQUENCE_ID,
955 BOS.OPERATION_SEQ_NUM,
956 SYSDATE, --LAST_UPDATE_DATE
957 BOM_Rtg_Globals.Get_User_Id, --LAST_UPDATED_BY
958 SYSDATE, --CREATION_DATE
959 BOM_Rtg_Globals.Get_User_Id, --CREATED_BY
960 BOM_Rtg_Globals.Get_Login_Id, --LAST_UPDATE_LOGIN
961 BOS.STANDARD_OPERATION_ID,
962 BOS.DEPARTMENT_ID,
963 BOS.OPERATION_LEAD_TIME_PERCENT,
964 BOS.MINIMUM_TRANSFER_QUANTITY,
965 BOS.COUNT_POINT_TYPE,
966 BOS.OPERATION_DESCRIPTION,
967 BOS.DISABLE_DATE + (1/86400), -- EFFECTIVITY_DATE
968 NULL, -- DISABLE_DATE
969 BOS.BACKFLUSH_FLAG,
970 BOS.OPTION_DEPENDENT_FLAG,
971 BOS.ATTRIBUTE_CATEGORY,
972 BOS.ATTRIBUTE1,
973 BOS.ATTRIBUTE2,
974 BOS.ATTRIBUTE3,
975 BOS.ATTRIBUTE4,
976 BOS.ATTRIBUTE5,
977 BOS.ATTRIBUTE6,
978 BOS.ATTRIBUTE7,
979 BOS.ATTRIBUTE8,
980 BOS.ATTRIBUTE9,
981 BOS.ATTRIBUTE10,
982 BOS.ATTRIBUTE11,
983 BOS.ATTRIBUTE12,
984 BOS.ATTRIBUTE13,
985 BOS.ATTRIBUTE14,
986 BOS.ATTRIBUTE15,
987 BOM_Rtg_Globals.Get_Request_Id,
988 BOM_Rtg_Globals.Get_Prog_AppId,
989 BOM_Rtg_Globals.Get_Prog_Id,
990 SYSDATE, --PROGRAM_UPDATE_DATE
991 DECODE(BOS.OPERATION_TYPE, 4, 1, BOS.OPERATION_TYPE),
992 BOS.REFERENCE_FLAG,
993 BOS.PROCESS_OP_SEQ_ID,
994 BOS.LINE_OP_SEQ_ID,
995 BOS.YIELD,
996 BOS.CUMULATIVE_YIELD,
997 BOS.REVERSE_CUMULATIVE_YIELD,
998 BOS.LABOR_TIME_CALC,
999 BOS.MACHINE_TIME_CALC,
1000 BOS.TOTAL_TIME_CALC,
1001 BOS.LABOR_TIME_USER,
1002 BOS.MACHINE_TIME_USER,
1003 BOS.TOTAL_TIME_USER,
1004 BOS.NET_PLANNING_PERCENT,
1005 BOS.INCLUDE_IN_ROLLUP,
1006 BOS.OPERATION_YIELD_ENABLED,
1007 BOS.CHANGE_NOTICE,
1008 SYSDATE,--IMPLEMENTATION_DATE
1009 BOS.SHUTDOWN_TYPE,
1010 BOS.LONG_DESCRIPTION,
1011 BOS.LOWEST_ACCEPTABLE_YIELD,
1012 BOS.USE_ORG_SETTINGS,
1013 BOS.QUEUE_MANDATORY_FLAG,
1014 BOS.RUN_MANDATORY_FLAG,
1015 BOS.TO_MOVE_MANDATORY_FLAG,
1016 BOS.SHOW_NEXT_OP_BY_DEFAULT,
1017 BOS.SHOW_SCRAP_CODE,
1018 BOS.SHOW_LOT_ATTRIB,
1019 BOS.TRACK_MULTIPLE_RES_USAGE_DATES
1020 FROM
1021 BOM_OPERATION_SEQUENCES BOS
1022 WHERE
1023 BOS.OPERATION_SEQUENCE_ID = p_operation_sequence_id;
1024
1025 -- Copy Operation Resources
1026 INSERT INTO BOM_OPERATION_RESOURCES
1027 (
1028 OPERATION_SEQUENCE_ID,
1029 RESOURCE_SEQ_NUM,
1030 RESOURCE_ID,
1031 ACTIVITY_ID,
1032 STANDARD_RATE_FLAG,
1033 ASSIGNED_UNITS,
1034 USAGE_RATE_OR_AMOUNT,
1035 USAGE_RATE_OR_AMOUNT_INVERSE,
1036 BASIS_TYPE,
1037 SCHEDULE_FLAG,
1038 LAST_UPDATE_DATE,
1039 LAST_UPDATED_BY,
1040 CREATION_DATE,
1041 CREATED_BY,
1042 LAST_UPDATE_LOGIN,
1043 RESOURCE_OFFSET_PERCENT,
1044 AUTOCHARGE_TYPE,
1045 ATTRIBUTE_CATEGORY,
1046 ATTRIBUTE1,
1047 ATTRIBUTE2,
1048 ATTRIBUTE3,
1049 ATTRIBUTE4,
1050 ATTRIBUTE5,
1051 ATTRIBUTE6,
1052 ATTRIBUTE7,
1056 ATTRIBUTE11,
1053 ATTRIBUTE8,
1054 ATTRIBUTE9,
1055 ATTRIBUTE10,
1057 ATTRIBUTE12,
1058 ATTRIBUTE13,
1059 ATTRIBUTE14,
1060 ATTRIBUTE15,
1061 REQUEST_ID,
1062 PROGRAM_APPLICATION_ID,
1063 PROGRAM_ID,
1064 PROGRAM_UPDATE_DATE,
1065 SCHEDULE_SEQ_NUM,
1066 SUBSTITUTE_GROUP_NUM,
1067 PRINCIPLE_FLAG,
1068 SETUP_ID,
1069 CHANGE_NOTICE,
1070 ACD_TYPE,
1071 ORIGINAL_SYSTEM_REFERENCE
1072 )
1073 SELECT
1074 l_op_seq_id,
1075 BOR.RESOURCE_SEQ_NUM,
1076 BOR.RESOURCE_ID,
1077 BOR.ACTIVITY_ID,
1078 BOR.STANDARD_RATE_FLAG,
1079 BOR.ASSIGNED_UNITS,
1080 BOR.USAGE_RATE_OR_AMOUNT,
1081 BOR.USAGE_RATE_OR_AMOUNT_INVERSE,
1082 BOR.BASIS_TYPE,
1083 BOR.SCHEDULE_FLAG,
1084 SYSDATE, --LAST_UPDATE_DATE,
1085 BOM_Rtg_Globals.Get_User_Id, --LAST_UPDATED_BY
1086 SYSDATE, --CREATION_DATE
1087 BOM_Rtg_Globals.Get_User_Id, --CREATED_BY
1088 BOM_Rtg_Globals.Get_Login_Id, --LAST_UPDATE_LOGIN
1089 BOR.RESOURCE_OFFSET_PERCENT,
1090 BOR.AUTOCHARGE_TYPE,
1091 BOR.ATTRIBUTE_CATEGORY,
1092 BOR.ATTRIBUTE1,
1093 BOR.ATTRIBUTE2,
1094 BOR.ATTRIBUTE3,
1095 BOR.ATTRIBUTE4,
1096 BOR.ATTRIBUTE5,
1097 BOR.ATTRIBUTE6,
1098 BOR.ATTRIBUTE7,
1099 BOR.ATTRIBUTE8,
1100 BOR.ATTRIBUTE9,
1101 BOR.ATTRIBUTE10,
1102 BOR.ATTRIBUTE11,
1103 BOR.ATTRIBUTE12,
1104 BOR.ATTRIBUTE13,
1105 BOR.ATTRIBUTE14,
1106 BOR.ATTRIBUTE15,
1107 BOM_Rtg_Globals.Get_Request_Id,
1108 BOM_Rtg_Globals.Get_Prog_AppId,
1109 BOM_Rtg_Globals.Get_Prog_Id,
1110 SYSDATE, --PROGRAM_UPDATE_DATE
1111 BOR.SCHEDULE_SEQ_NUM,
1112 BOR.SUBSTITUTE_GROUP_NUM,
1113 BOR.PRINCIPLE_FLAG,
1114 BOR.SETUP_ID,
1115 BOR.CHANGE_NOTICE,
1116 BOR.ACD_TYPE,
1117 BOR.ORIGINAL_SYSTEM_REFERENCE
1118 FROM
1119 BOM_OPERATION_RESOURCES BOR
1120 WHERE
1121 BOR.OPERATION_SEQUENCE_ID = p_operation_sequence_id;
1122
1123 -- Copy Alternate Operation Resources
1124 INSERT INTO BOM_SUB_OPERATION_RESOURCES
1125 (
1126 OPERATION_SEQUENCE_ID,
1127 SUBSTITUTE_GROUP_NUM,
1128 RESOURCE_ID,
1129 SCHEDULE_SEQ_NUM,
1130 REPLACEMENT_GROUP_NUM,
1131 ACTIVITY_ID,
1132 STANDARD_RATE_FLAG,
1133 ASSIGNED_UNITS,
1134 USAGE_RATE_OR_AMOUNT,
1135 USAGE_RATE_OR_AMOUNT_INVERSE,
1136 BASIS_TYPE,
1137 SCHEDULE_FLAG,
1138 LAST_UPDATE_DATE,
1139 LAST_UPDATED_BY,
1140 CREATION_DATE,
1141 CREATED_BY,
1142 LAST_UPDATE_LOGIN,
1143 RESOURCE_OFFSET_PERCENT,
1144 AUTOCHARGE_TYPE,
1145 ATTRIBUTE_CATEGORY,
1146 REQUEST_ID,
1147 PROGRAM_APPLICATION_ID,
1148 PROGRAM_ID,
1149 PROGRAM_UPDATE_DATE,
1150 ATTRIBUTE1,
1151 ATTRIBUTE2,
1152 ATTRIBUTE3,
1153 ATTRIBUTE4,
1154 ATTRIBUTE5,
1155 ATTRIBUTE6,
1156 ATTRIBUTE7,
1157 ATTRIBUTE8,
1158 ATTRIBUTE9,
1159 ATTRIBUTE10,
1160 ATTRIBUTE11,
1161 ATTRIBUTE12,
1162 ATTRIBUTE13,
1163 ATTRIBUTE14,
1164 ATTRIBUTE15,
1165 PRINCIPLE_FLAG,
1166 SETUP_ID,
1167 CHANGE_NOTICE,
1168 ACD_TYPE,
1169 ORIGINAL_SYSTEM_REFERENCE
1170 )
1171 SELECT
1172 l_op_seq_id,
1173 BSOR.SUBSTITUTE_GROUP_NUM,
1174 BSOR.RESOURCE_ID,
1175 BSOR.SCHEDULE_SEQ_NUM,
1176 BSOR.REPLACEMENT_GROUP_NUM,
1177 BSOR.ACTIVITY_ID,
1178 BSOR.STANDARD_RATE_FLAG,
1179 BSOR.ASSIGNED_UNITS,
1180 BSOR.USAGE_RATE_OR_AMOUNT,
1181 BSOR.USAGE_RATE_OR_AMOUNT_INVERSE,
1182 BSOR.BASIS_TYPE,
1183 BSOR.SCHEDULE_FLAG,
1184 SYSDATE, --LAST_UPDATE_DATE,
1185 BOM_Rtg_Globals.Get_User_Id, --LAST_UPDATED_BY
1186 SYSDATE, --CREATION_DATE
1187 BOM_Rtg_Globals.Get_User_Id, --CREATED_BY
1188 BOM_Rtg_Globals.Get_Login_Id, --LAST_UPDATE_LOGIN
1189 BSOR.RESOURCE_OFFSET_PERCENT,
1190 BSOR.AUTOCHARGE_TYPE,
1191 BSOR.ATTRIBUTE_CATEGORY,
1192 BOM_Rtg_Globals.Get_Request_Id,
1193 BOM_Rtg_Globals.Get_Prog_AppId,
1194 BOM_Rtg_Globals.Get_Prog_Id,
1195 SYSDATE, --PROGRAM_UPDATE_DATE
1196 BSOR.ATTRIBUTE1,
1197 BSOR.ATTRIBUTE2,
1198 BSOR.ATTRIBUTE3,
1199 BSOR.ATTRIBUTE4,
1200 BSOR.ATTRIBUTE5,
1201 BSOR.ATTRIBUTE6,
1202 BSOR.ATTRIBUTE7,
1203 BSOR.ATTRIBUTE8,
1204 BSOR.ATTRIBUTE9,
1205 BSOR.ATTRIBUTE10,
1206 BSOR.ATTRIBUTE11,
1207 BSOR.ATTRIBUTE12,
1208 BSOR.ATTRIBUTE13,
1209 BSOR.ATTRIBUTE14,
1210 BSOR.ATTRIBUTE15,
1211 BSOR.PRINCIPLE_FLAG,
1212 BSOR.SETUP_ID,
1213 BSOR.CHANGE_NOTICE,
1214 BSOR.ACD_TYPE,
1215 BSOR.ORIGINAL_SYSTEM_REFERENCE
1216 FROM
1217 BOM_SUB_OPERATION_RESOURCES BSOR
1218 WHERE
1219 BSOR.OPERATION_SEQUENCE_ID = p_operation_sequence_id;
1220
1221 END Copy_Operation;
1222
1223
1224 END BOM_Op_Network_UTIL;