[Home] [Help]
PACKAGE BODY: APPS.BOM_OP_SEQ_UTIL
Source
4 --
1 PACKAGE BODY BOM_Op_Seq_UTIL AS
2 /* $Header: BOMUOPSB.pls 120.5.12020000.3 2012/09/04 11:36:20 rambkond ship $ */
3 /****************************************************************************
5 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMUOPSS.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Op_Seq_UTIL
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 10-AUG-00 Masanroi Kimizuka Initial Creation
21 -- 25-OCT-00 Masanori Kimizuka Modified Insert_Row to add Eco_For_Production
22 --
23 ****************************************************************************/
24
25 G_Pkg_Name CONSTANT VARCHAR2(30) := 'BOM_Op_Seq_UTIL';
26
27
28
29 /*****************************************************************
30 * Procedure : Query_Row used by RTG BO
31 * Parameters IN : Rtg Operation Key
32 * Parameters out: Rtg Operation Exposed column Record
33 * Rtg Operation Unexposed column Record
34 * Returns : None
35 * Purpose : Convert Record and Call Query_Row for Common
36 * Query will query the database record and seperate
37 * the unexposed and exposed attributes before returning
38 * the records.
39 ********************************************************************/
40
41 PROCEDURE Query_Row
42 ( p_operation_sequence_number IN NUMBER
43 , p_effectivity_date IN DATE
44 , p_routing_sequence_id IN NUMBER
45 , p_operation_type IN NUMBER
46 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
47 , x_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Operation_Rec_Type
48 , x_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Unexposed_Rec_Type
49 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
50 , x_return_status IN OUT NOCOPY VARCHAR2
51 )
52
53
54 IS
55
56 /* Define Variable */
57 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
58 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type;
59 l_err_text VARCHAR2(2000);
60
61 BEGIN
62
63 x_mesg_token_tbl := p_mesg_token_tbl;
64
65 BOM_Op_Seq_UTIL.Query_Row
66 ( p_operation_sequence_number => p_operation_sequence_number
67 , p_effectivity_date => p_effectivity_date
68 , p_routing_sequence_id => p_routing_sequence_id
69 , p_operation_type => p_operation_type
70 , p_mesg_Token_tbl => p_Mesg_Token_Tbl
71 , x_com_operation_rec => l_com_operation_rec
72 , x_com_op_unexp_rec => l_com_op_unexp_rec
73 , x_mesg_token_tbl => x_mesg_token_tbl
74 , x_return_status => x_return_status
75 ) ;
76
77 -- Convert the ECO record to Routing Record
78 Bom_Rtg_Pub.Convert_ComOp_To_RtgOp
79 ( p_com_operation_rec => l_com_operation_rec
80 , p_com_op_unexp_rec => l_com_op_unexp_rec
81 , x_rtg_operation_rec => x_operation_rec
82 , x_rtg_op_unexp_rec => x_op_unexp_rec
83 ) ;
84
85 END Query_Row;
86
87
88 /*****************************************************************
89 * Procedure : Query_Row used by ECO BO
90 * Parameters IN : Revised Operation Key
91 * Parameters out: Revised Operation Exposed column Record
92 * Revised Operation Unexposed column Record
93 * Returns : None
94 * Purpose : Convert Record and Call Query_Row for Common
95 * Query will query the database record and seperate
96 * the unexposed and exposed attributes before returning
97 * the records.
98 ********************************************************************/
99
100 PROCEDURE Query_Row
101 ( p_operation_sequence_number IN NUMBER
102 , p_effectivity_date IN DATE
103 , p_routing_sequence_id IN NUMBER
104 , p_operation_type IN NUMBER
105 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
106 , x_rev_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Operation_Rec_Type
107 , x_rev_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
108 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
109 , x_return_status IN OUT NOCOPY VARCHAR2
110 )
111
112
113 IS
114
115 /* Define Variable */
116 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
117 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type;
118 l_err_text VARCHAR2(2000);
119
120 BEGIN
121
122 x_mesg_token_tbl := p_mesg_token_tbl;
123
124 BOM_Op_Seq_UTIL.Query_Row
125 ( p_operation_sequence_number => p_operation_sequence_number
126 , p_effectivity_date => p_effectivity_date
127 , p_routing_sequence_id => p_routing_sequence_id
128 , p_operation_type => p_operation_type
129 , p_mesg_Token_tbl => p_Mesg_Token_Tbl
130 , x_com_operation_rec => l_com_operation_rec
131 , x_com_op_unexp_rec => l_com_op_unexp_rec
132 , x_mesg_token_tbl => x_mesg_token_tbl
136 -- Convert the Common record to Revised Operation record
133 , x_return_status => x_return_status
134 );
135
137 Bom_Rtg_Pub.Convert_ComOp_To_EcoOp
138 ( p_com_operation_rec => l_com_operation_rec
139 , p_com_op_unexp_rec => l_com_op_unexp_rec
140 , x_rev_operation_rec => x_rev_operation_rec
141 , x_rev_op_unexp_rec => x_rev_op_unexp_rec
142 ) ;
143
144 END Query_Row;
145
146
147
148
149 /*****************************************************************
150 * Procedure : Query_Row used for Common
151 * internally called by RTG BO and Eco Rtg
152 * Parameters IN : Common Operation Key
153 * Parameters out: Common Operation Exposed column Record
154 * Common Operation Unexposed column Record
155 * Returns : None
156 * Purpose : Common Operation Query Row
157 * will query the database record and seperate
158 * the unexposed and exposed attributes before returning
159 * the records.
163 PROCEDURE Query_Row
160 ********************************************************************/
161
162
164 ( p_operation_sequence_number IN NUMBER
165 , p_effectivity_date IN DATE
166 , p_routing_sequence_id IN NUMBER
167 , p_operation_type IN NUMBER
168 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
169 , x_com_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Operation_Rec_Type
170 , x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
171 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
172 , x_return_status IN OUT NOCOPY VARCHAR2
173 )
174 IS
175
176 /* Define Variable */
177 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
178 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type;
179 l_bo_id VARCHAR2(3) ;
180 l_err_text VARCHAR2(2000);
181
182
183 /* Define Cursor */
184 Cursor op_seq_csr( p_operation_sequence_number NUMBER
185 , p_effectivity_date DATE
186 , p_routing_sequence_id NUMBER
187 , p_operation_type NUMBER
188 , l_bo_id VARCHAR2
189 )
190 IS
191
192 SELECT * FROM BOM_OPERATION_SEQUENCES
193 WHERE (( l_bo_id = BOM_Rtg_Globals.G_ECO_BO )
194 -- AND implementation_date IS NULL )
195 OR (l_bo_id = BOM_Rtg_Globals.G_RTG_BO
196 AND implementation_date IS NOT NULL )
197 )
198 AND ( ( NVL(OPERATION_TYPE,1) = 1 AND -- Added nvl for bug 2856314
199 EFFECTIVITY_DATE = p_effectivity_date -- Changed for bug 2647027
200 -- /** time **/ TRUNC(EFFECTIVITY_DATE) = TRUNC(p_effectivity_date)
201 )
202 OR p_operation_type IN (2,3)
203 )
207 AND routing_sequence_id = p_routing_sequence_id
204 AND NVL(OPERATION_TYPE, 1) = DECODE(p_operation_type, FND_API.G_MISS_NUM, 1
205 , NVL(p_operation_type, 1))
206 AND OPERATION_SEQ_NUM = p_operation_sequence_number
208 ;
209
210 op_seq_rec BOM_OPERATION_SEQUENCES%ROWTYPE ;
211
212
213 BEGIN
214
215 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
216 Error_Handler.Write_Debug
217 ('Querying an operation sequence record : Seq Number '
218 || to_char(p_operation_sequence_number) || '. . . ' ) ;
219 Error_Handler.Write_Debug (' : Routing seq id ' || to_char( p_routing_sequence_id));
220 Error_Handler.Write_Debug (' : Effecitive date' || to_char( p_effectivity_date));
221 Error_Handler.Write_Debug (' : Operation type ' || to_char( NVL(p_operation_type , 1)));
222 END IF ;
223
224 -- dbms_output.put_line('Op Type : ' ||
225 -- to_char(p_operation_type);
226 -- dbms_output.put_line('Effective: ' || to_char(p_effectivity_date ));
227 -- dbms_output.put_line('Rtg Seq : ' || to_char(p_routing_Sequence_Id));
228
229 x_mesg_token_tbl := p_mesg_token_tbl;
230 l_bo_id := BOM_Rtg_Globals.Get_Bo_Identifier ;
231
232 IF NOT op_seq_csr%ISOPEN
233 THEN
234 OPEN op_seq_csr( p_operation_sequence_number
235 , p_effectivity_date
236 , p_routing_sequence_id
237 , p_operation_type
238 , l_bo_id
239 ) ;
240 END IF ;
241
242 FETCH op_seq_csr INTO op_seq_rec ;
243
244 IF op_seq_csr%FOUND
245 THEN
246
247 -- Set Queried Record to Exposed and Unexposed Recourd
248 -- Unexposed Column
249 l_com_op_unexp_rec.Revised_Item_Sequence_Id := op_seq_rec.REVISED_ITEM_SEQUENCE_ID ;
250 l_com_op_unexp_rec.Operation_Sequence_Id := op_seq_rec.OPERATION_SEQUENCE_ID ;
251 l_com_op_unexp_rec.Old_Operation_Sequence_Id := op_seq_rec.OLD_OPERATION_SEQUENCE_ID ;
252 l_com_op_unexp_rec.Routing_Sequence_Id := op_seq_rec.routing_sequence_id ;
253 l_com_op_unexp_rec.Standard_Operation_Id := op_seq_rec.STANDARD_OPERATION_ID ;
254 l_com_op_unexp_rec.Department_Id := op_seq_rec.DEPARTMENT_ID ;
255 l_com_op_unexp_rec.Process_Op_Seq_Id := op_seq_rec.PROCESS_OP_SEQ_ID ;
256 l_com_op_unexp_rec.Line_Op_Seq_Id := op_seq_rec.LINE_OP_SEQ_ID ;
257 l_com_op_unexp_rec.User_Elapsed_Time := op_seq_rec.TOTAL_TIME_USER ;
258 l_com_op_unexp_rec.lowest_acceptable_yield := op_seq_rec.LOWEST_ACCEPTABLE_YIELD ; -- Added for MES Enhancement
259 l_com_op_unexp_rec.use_org_settings := op_seq_rec.USE_ORG_SETTINGS ;
260 l_com_op_unexp_rec.queue_mandatory_flag := op_seq_rec.QUEUE_MANDATORY_FLAG ;
261 l_com_op_unexp_rec.run_mandatory_flag := op_seq_rec.RUN_MANDATORY_FLAG ;
262 l_com_op_unexp_rec.to_move_mandatory_flag := op_seq_rec.TO_MOVE_MANDATORY_FLAG ;
263 l_com_op_unexp_rec.show_next_op_by_default := op_seq_rec.SHOW_NEXT_OP_BY_DEFAULT ;
264 l_com_op_unexp_rec.show_scrap_code := op_seq_rec.SHOW_SCRAP_CODE ;
265 l_com_op_unexp_rec.show_lot_attrib := op_seq_rec.SHOW_LOT_ATTRIB ;
266 l_com_op_unexp_rec.track_multiple_res_usage_dates := op_seq_rec.TRACK_MULTIPLE_RES_USAGE_DATES ; -- End of MES Changes
267
268 -- Exposed Column
269 l_com_operation_rec.Eco_Name := op_seq_rec.CHANGE_NOTICE ;
270 l_com_operation_rec.ACD_Type := op_seq_rec.ACD_TYPE ;
271 l_com_operation_rec.Operation_Sequence_Number := op_seq_rec.OPERATION_SEQ_NUM ;
272 l_com_operation_rec.Operation_Type := op_seq_rec.OPERATION_TYPE ;
273 l_com_operation_rec.Start_Effective_Date := op_seq_rec.EFFECTIVITY_DATE ;
274 l_com_operation_rec.Op_Lead_Time_Percent := op_seq_rec.OPERATION_LEAD_TIME_PERCENT ;
275 l_com_operation_rec.Minimum_Transfer_Quantity := op_seq_rec.MINIMUM_TRANSFER_QUANTITY ;
276 l_com_operation_rec.Count_Point_Type := op_seq_rec.COUNT_POINT_TYPE ;
277 l_com_operation_rec.Operation_Description := op_seq_rec.OPERATION_DESCRIPTION ;
278 l_com_operation_rec.Disable_Date := op_seq_rec.DISABLE_DATE ;
279 l_com_operation_rec.Backflush_Flag := op_seq_rec.BACKFLUSH_FLAG ;
280 l_com_operation_rec.Option_Dependent_Flag := op_seq_rec.OPTION_DEPENDENT_FLAG ;
281 l_com_operation_rec.Reference_Flag := op_seq_rec.REFERENCE_FLAG ;
282 l_com_operation_rec.Yield := op_seq_rec.YIELD ;
283 l_com_operation_rec.Cumulative_Yield := op_seq_rec.CUMULATIVE_YIELD ;
284 l_com_operation_rec.Reverse_CUM_Yield := op_seq_rec.REVERSE_CUMULATIVE_YIELD ;
285 -- l_com_operation_rec.Calculated_Labor_Time := op_seq_rec.LABOR_TIME_CALC ;
286 -- l_com_operation_rec.Calculated_Machine_Time := op_seq_rec.MACHINE_TIME_CALC ;
287 -- l_com_operation_rec.Calculated_Elapsed_Time := op_seq_rec.TOTAL_TIME_CALC ;
288 l_com_operation_rec.User_Labor_Time := op_seq_rec.LABOR_TIME_USER ;
289 l_com_operation_rec.User_Machine_Time := op_seq_rec.MACHINE_TIME_USER ;
290 l_com_operation_rec.Net_Planning_Percent := op_seq_rec.NET_PLANNING_PERCENT ;
291 l_com_operation_rec.Include_In_Rollup := op_seq_rec.INCLUDE_IN_ROLLUP ;
292 l_com_operation_rec.Op_Yield_Enabled_Flag := op_seq_rec.OPERATION_YIELD_ENABLED ;
293 -- Added by MK on 04/10/2001 for eAM changes
294 l_com_operation_rec.Shutdown_Type := op_seq_rec.SHUTDOWN_TYPE ;
295 l_com_operation_rec.Attribute_category := op_seq_rec.ATTRIBUTE_CATEGORY ;
296 l_com_operation_rec.Attribute1 := op_seq_rec.ATTRIBUTE1 ;
300 l_com_operation_rec.Attribute5 := op_seq_rec.ATTRIBUTE5 ;
297 l_com_operation_rec.Attribute2 := op_seq_rec.ATTRIBUTE2 ;
298 l_com_operation_rec.Attribute3 := op_seq_rec.ATTRIBUTE3 ;
299 l_com_operation_rec.Attribute4 := op_seq_rec.ATTRIBUTE4 ;
301 l_com_operation_rec.Attribute6 := op_seq_rec.ATTRIBUTE6 ;
302 l_com_operation_rec.Attribute7 := op_seq_rec.ATTRIBUTE7 ;
303 l_com_operation_rec.Attribute8 := op_seq_rec.ATTRIBUTE8 ;
304 l_com_operation_rec.Attribute9 := op_seq_rec.ATTRIBUTE9 ;
305 l_com_operation_rec.Attribute10 := op_seq_rec.ATTRIBUTE10 ;
306 l_com_operation_rec.Attribute11 := op_seq_rec.ATTRIBUTE11 ;
307 l_com_operation_rec.Attribute12 := op_seq_rec.ATTRIBUTE12 ;
308 l_com_operation_rec.Attribute13 := op_seq_rec.ATTRIBUTE13 ;
309 l_com_operation_rec.Attribute14 := op_seq_rec.ATTRIBUTE14 ;
310 l_com_operation_rec.Attribute15 := op_seq_rec.ATTRIBUTE15 ;
311 l_com_operation_rec.Original_System_Reference := op_seq_rec.ORIGINAL_SYSTEM_REFERENCE ;
312 l_com_operation_rec.Long_Description := op_seq_rec.LONG_DESCRIPTION ; -- Added for long description project (Bug 2689249)
313
314 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Finished querying and assigning operation record . . .') ;
315 END IF ;
316
317 x_return_status := BOM_Rtg_Globals.G_RECORD_FOUND ;
318 x_com_operation_rec := l_com_operation_rec ;
319 x_com_op_unexp_rec := l_com_op_unexp_rec ;
320
321 ELSE
322 x_return_status := BOM_Rtg_Globals.G_RECORD_NOT_FOUND ;
323 x_com_operation_rec := l_com_operation_rec ;
324 x_com_op_unexp_rec := l_com_op_unexp_rec ;
325
326 END IF ;
327
328 IF op_seq_csr%ISOPEN
329 THEN
330 CLOSE op_seq_csr ;
331 END IF ;
332
333 EXCEPTION
334 WHEN OTHERS THEN
335 l_err_text := G_PKG_NAME || ' Utility (Operation Query Row) '
336 || substrb(SQLERRM,1,200);
337
338 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
339
340 Error_Handler.Add_Error_Token
341 ( p_message_name => NULL
342 , p_message_text => l_err_text
343 , p_mesg_token_tbl => p_mesg_token_tbl
344 , x_mesg_token_tbl => x_mesg_token_tbl
345 );
346
347 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR ;
348
349 END Query_Row;
350
351
352 /*********************************************************************
353 * Procedure : Perform_Writes used by RTG BO
354 * Parameters IN : Rtg Operation exposed column record
355 * Rtg Operation unexposed column record
356 * Parameters ouT: Return Status
357 * Message Token Table
358 * Purpose : Convert Rtg Operation to Common Operation and
359 * Call Check_Entity for Common Operation.
360 * Perform Writes is the only exposed procedure when the
361 * user has to perform any insert/update/deletes to the
362 * Operation Sequences table.
363 *********************************************************************/
364 PROCEDURE Perform_Writes
365 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
366 , p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
367 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
368 , x_return_status IN OUT NOCOPY VARCHAR2
369 )
370 IS
371 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
372 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
373
374 BEGIN
375 -- Convert Routing Operation to Common Operation
376 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
377 ( p_rtg_operation_rec => p_operation_rec
378 , p_rtg_op_unexp_rec => p_op_unexp_rec
379 , x_com_operation_rec => l_com_operation_rec
380 , x_com_op_unexp_rec => l_com_op_unexp_rec
381 ) ;
382
383 -- Call Perform Writes Procedure
384
385 Bom_Op_Seq_UTIL.Perform_Writes
386 ( p_com_operation_rec => l_com_operation_rec
387 , p_com_op_unexp_rec => l_com_op_unexp_rec
388 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
389 , x_return_status => x_return_status
390 , x_mesg_token_tbl => x_mesg_token_tbl
391 ) ;
392
393 END Perform_Writes ;
394
395
396
397 /*********************************************************************
398 * Procedure : Perform_Writes used by ECO BO
399 * Parameters IN : Revised Operation exposed column record
400 * Revised Operation unexposed column record
401 * Parameters out: Return Status
402 * Message Token Table
403 * Purpose : Convert Revised Operation to Common Operation and
404 * Call Check_Entity for Common Operation.
405 * Perform Writes is the only exposed procedure when the
406 * user has to perform any insert/update/deletes to the
407 * Operation Sequences table.
408 *********************************************************************/
409 PROCEDURE Perform_Writes
410 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
411 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
412 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
416 IS
413 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
414 , x_return_status IN OUT NOCOPY VARCHAR2
415 )
417 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
418 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
419
420 BEGIN
421 -- Convert Revised Operation to Common Operation
422 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
423 ( p_rev_operation_rec => p_rev_operation_rec
424 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
425 , x_com_operation_rec => l_com_operation_rec
426 , x_com_op_unexp_rec => l_com_op_unexp_rec
427 ) ;
428
429
430 -- Call Perform Writes Procedure
431 Bom_Op_Seq_UTIL.Perform_Writes
432 ( p_com_operation_rec => l_com_operation_rec
433 , p_com_op_unexp_rec => l_com_op_unexp_rec
434 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
435 , x_return_status => x_return_status
436 , x_mesg_token_tbl => x_mesg_token_tbl
437 ) ;
438
439 END Perform_Writes;
440
441
442 /*********************************************************************
443 * Procedure : Perform_Writes internally called by RTG BO and by ECO BO
444 * Parameters IN : Common Operation exposed column record
445 * Common Operation unexposed column record
446 * Parameters out: Return Status
447 * Message Token Table
448 * Purpose : Perform any insert/update/deletes to the
449 * Operation Sequences table.
450 *********************************************************************/
451
452 PROCEDURE Perform_Writes
453 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
454 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
455 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
456 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
457 , x_return_status IN OUT NOCOPY VARCHAR2
458 )
459
460 IS
461
462 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
463 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
464 l_routing_sequence_id NUMBER ; -- Routing Sequence Id
465 l_routing_type NUMBER ; -- Routing Type
466
467 -- Error Handlig Variables
468 l_return_status VARCHAR2(1);
469 l_temp_return_status VARCHAR2(1);
470 l_err_text VARCHAR2(2000) ;
471 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
472 l_temp_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
473 l_Token_Tbl Error_Handler.Token_Tbl_Type;
474
475 -- Check if Routing exists
476 CURSOR l_rtg_exists_csr ( p_revised_item_id NUMBER
477 , p_organization_id NUMBER
478 , p_alternate_rtg_code VARCHAR2
479 )
480 IS
481 SELECT 'Routing Exists'
482 FROM DUAL
483 WHERE NOT EXISTS ( SELECT routing_sequence_id
484 FROM bom_operational_routings
485 WHERE assembly_item_id = p_revised_item_id
486 AND organization_id = p_organization_id
487 AND NVL(alternate_routing_designator, FND_API.G_MISS_CHAR) =
488 NVL(p_alternate_rtg_code,FND_API.G_MISS_CHAR)
489 );
490
491 -- Get Eng_Item_Flag for Routing Type value
492 CURSOR l_routing_type_csr ( p_revised_item_id NUMBER
493 , p_organization_id NUMBER )
494 IS
495 SELECT decode(eng_item_flag, 'N', 1, 2) eng_item_flag
496 FROM MTL_SYSTEM_ITEMS
497 WHERE inventory_item_id = p_revised_item_id
498 AND organization_id = p_organization_id ;
499
500 -- Get Routing_Sequence_id
501 CURSOR l_get_rtg_seq_csr
502 IS
503 SELECT bom_operational_routings_s.NEXTVAL routing_sequence_id
504 FROM DUAL ;
505
506 BEGIN
507 --
508 -- Initialize Common Record and Status
509 --
510 l_com_operation_rec := p_com_operation_rec ;
511 l_com_op_unexp_rec := p_com_op_unexp_rec ;
512 l_return_status := FND_API.G_RET_STS_SUCCESS ;
513 x_return_status := FND_API.G_RET_STS_SUCCESS ;
514
515 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
516 ('Performing Database Writes . . .') ;
517 END IF ;
518
519
520 IF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE THEN
521 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
522 ('Operation Sequence: Executing Insert Row. . . ') ;
523 END IF;
524
525 /**************************************************************************
526 -- commenting calling following logic for the release 11i.4 to remove dependancy
527 -- on the eng odf. This is because the RTG and ECO objects should be
528 -- independant of each other. But commenting out because we will reuse it
529 --
530 -- This logic is moved to Eng_Globals.Perform_Writes_For_Primary_RTG and
531 -- it is called from Rev Operation Procedure in Private API.
532 --
533 -- Revised Operation
534 --
535 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
536 THEN
537 FOR l_rtg_exists_rec IN l_rtg_exists_csr
538 ( p_revised_item_id => p_com_op_unexp_rec.revised_item_id
539 , p_organization_id => p_com_op_unexp_rec.organization_id
543 --
540 , p_alternate_rtg_code => l_com_operation_rec.alternate_routing_code
541 )
542 LOOP
544 -- Loop executes then the Routing does not exist.
545 --
546 FOR l_routing_type_rec IN l_routing_type_csr
547 ( p_revised_item_id => p_com_op_unexp_rec.revised_item_id
548 , p_organization_id => p_com_op_unexp_rec.organization_id)
549 LOOP
550 l_routing_type := l_routing_type_rec.eng_item_flag ;
551 END LOOP ;
552
553 --
554 -- If Caller Type is FORM, Generate new routing_sequence_id
555 --
556 IF p_control_rec.caller_type IS NOT NULL AND
557 p_control_rec.caller_type = 'FORM'
558 THEN
559 FOR l_get_rtg_seq_rec IN l_get_rtg_seq_csr
560 LOOP
561 l_com_op_unexp_rec.routing_sequence_id :=
562 l_get_rtg_seq_rec.routing_sequence_id;
563 END LOOP;
564
565 l_Token_Tbl(1).token_name := 'REVISED_ITEM_NAME';
566 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
567 l_Token_Tbl(2).token_name := 'OP_SEQ_NUMBER';
568 l_Token_Tbl(2).token_value := p_com_operation_rec.operation_sequence_number ;
569
570 Error_Handler.Add_Error_Token
571 ( p_Message_Name => 'BOM_NEW_PRIMARY_RTG_CREATED'
572 , p_Message_Text => NULL
573 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
574 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
575 , p_message_type => 'W'
576
577 ) ;
578 ELSE
579
580 --
581 -- Log a warning indicating that a new bill has been created
582 -- as a result of the operation being added.
583 --
584 l_Token_Tbl(1).token_name := 'REVISED_ITEM_NAME';
585 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
586 l_Token_Tbl(2).token_name := 'OP_SEQ_NUMBER';
587 l_Token_Tbl(2).token_value := p_com_operation_rec.operation_sequence_number ;
588
589 Error_Handler.Add_Error_Token
590 ( p_Message_Name => 'BOM_NEW_PRIMARY_RTG_CREATED'
591 , p_Message_Text => NULL
592 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
593 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
594 , p_Token_Tbl => l_Token_Tbl
595 , p_message_type => 'W'
596 ) ;
597 END IF ;
598
599 --
600 -- Create New Routing using Routing Attributes in Revised Items table
601 --
602 Create_New_Routing
603 ( p_assembly_item_id => l_com_op_unexp_rec.revised_item_id
604 , p_organization_id => l_com_op_unexp_rec.organization_id
605 , p_pending_from_ecn => l_com_operation_rec.eco_name
606 , p_routing_sequence_id => l_com_op_unexp_rec.routing_sequence_id
607 , p_common_routing_sequence_id => l_com_op_unexp_rec.routing_sequence_id
608 , p_routing_type => l_routing_type
609 , p_last_update_date => SYSDATE
610 , p_last_updated_by => BOM_Rtg_Globals.Get_User_Id
611 , p_creation_date => SYSDATE
612 , p_created_by => BOM_Rtg_Globals.Get_User_Id
613 , p_login_id => BOM_Rtg_Globals.Get_Login_Id
614 , p_revised_item_sequence_id => l_com_op_unexp_rec.revised_item_sequence_id
615 , p_original_system_reference => l_com_operation_rec.original_system_reference
616 , x_Mesg_Token_Tbl => l_temp_mesg_token_Tbl
617 , x_return_status => l_temp_return_status
618 ) ;
619
620
621 IF l_temp_return_status <> FND_API.G_RET_STS_SUCCESS
622 THEN
623 l_return_status := l_temp_return_status ;
624 l_mesg_token_Tbl := l_temp_Mesg_Token_Tbl ;
625
626 ELSE
627 -- Create a new routing revision for the created primary routing
628 INSERT INTO MTL_RTG_ITEM_REVISIONS
629 ( inventory_item_id
630 , organization_id
631 , process_revision
632 , implementation_date
633 , last_update_date
634 , last_updated_by
635 , creation_date
636 , created_by
637 , last_update_login
638 , change_notice
639 , ecn_initiation_date
640 , effectivity_date
641 , revised_item_sequence_id
642 )
643 SELECT
644 l_com_op_unexp_rec.revised_item_id
645 , l_com_op_unexp_rec.organization_id
646 , mp.starting_revision
647 , SYSDATE
648 , SYSDATE
649 , BOM_Rtg_Globals.Get_User_Id
650 , SYSDATE
651 , BOM_Rtg_Globals.Get_User_Id
652 , BOM_Rtg_Globals.Get_Login_Id
653 , l_com_operation_rec.eco_name
654 , SYSDATE
655 , SYSDATE
659 AND NOT EXISTS( SELECT NULL
656 , l_com_op_unexp_rec.revised_item_sequence_id
657 FROM MTL_PARAMETERS mp
658 WHERE mp.organization_id = l_com_op_unexp_rec.organization_id
660 FROM MTL_RTG_ITEM_REVISIONS
661 WHERE implementation_date IS NOT NULL
662 AND organization_id = l_com_op_unexp_rec.organization_id
663 AND inventory_item_id = l_com_op_unexp_rec.revised_item_id
664 ) ;
665
666 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
667 ('Creating new routing revision for the created primary routing for the revised item . . . ') ;
668 END IF;
669
670
671 END IF ;
672
673 END LOOP ;
674 END IF ;
675
676 **************************************************************************/
677
678 Insert_Row
679 ( p_com_operation_rec => l_com_operation_rec
680 , p_com_op_unexp_rec => l_com_op_unexp_rec
681 , x_return_status => l_temp_return_status
682 , x_mesg_token_tbl => l_temp_mesg_token_tbl
683 ) ;
684
685 IF l_temp_return_status <> FND_API.G_RET_STS_SUCCESS
686 THEN
687 l_return_status := l_temp_return_status ;
688 l_mesg_token_Tbl := l_temp_Mesg_Token_Tbl ;
689 END IF ;
690
691 ELSIF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
692 THEN
693 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
694 ('Operatin Sequence: Executing Update Row. . . ') ;
695 END IF ;
696
697 Update_Row
698 ( p_com_operation_rec => l_com_operation_rec
699 , p_com_op_unexp_rec => l_com_op_unexp_rec
700 , x_return_status => l_return_status
701 , x_mesg_token_tbl => l_mesg_token_tbl
702 ) ;
703
704 ELSIF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
705 THEN
706
707 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
708 ('Operatin Sequence: Executing Delete Row. . . ') ;
709 END IF ;
710
711 Delete_Row
712 ( p_com_operation_rec => l_com_operation_rec
713 , p_com_op_unexp_rec => l_com_op_unexp_rec
714 , x_com_operation_rec => l_com_operation_rec
715 , x_com_op_unexp_rec => l_com_op_unexp_rec
716 , x_return_status => l_return_status
717 , x_mesg_token_tbl => l_mesg_token_tbl
718 ) ;
719
720
721 /**************************************************************************
722 -- commenting this calling procedure for the release 11i.4 to remove dependancy
723 -- on the eng odf. This is because the RTG and ECO objects should be
724 -- independant of each other. But commenting out because we will reuse it
725 -- in release 12 when these all files alongwith the odf will be base.
726 --
727 -- Cancel_Operation procedure is moved to Eng_Globals packate and
728 -- Eng_Globals.Cancel_Operation is called from Rev Op Private Procedure
729
730
731 ELSIF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CANCEL
732 THEN
733
734
735 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
736 ('Operatin Sequence: Perform Cancel Operation . . .') ;
737 END IF ;
738
739 Cancel_Operation
740 ( p_operation_sequence_id => l_com_op_unexp_rec.operation_sequence_id
741 , p_cancel_comments => l_com_operation_rec.cancel_comments
742 , p_op_seq_num => l_com_operation_rec.operation_sequence_number
743 , p_user_id => BOM_Rtg_Globals.Get_User_Id
744 , p_login_id => BOM_Rtg_Globals.Get_Login_Id
745 , p_prog_id => BOM_Rtg_Globals.Get_Prog_Id
746 , p_prog_appid => BOM_Rtg_Globals.Get_Prog_AppId
747 , x_return_status => l_return_status
748 , x_mesg_token_tbl => l_mesg_token_tbl
749 ) ;
750
751 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
752 ('Cancel Operation is completed with return status ' || l_return_status ) ;
753 END IF ;
754
755
756 **************************************************************************/
757
758
759
760 END IF ;
761
762 --
763 -- Return Status
764 --
765 x_return_status := l_return_status ;
766 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl ;
767
768 EXCEPTION
769 WHEN OTHERS THEN
770 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
771 ('Some unknown error in Perform Writes . . .' || SQLERRM );
772 END IF ;
773
774 l_err_text := G_PKG_NAME || ' Utility (Perform Writes) '
775 || substrb(SQLERRM,1,200);
776
777 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
778
779 Error_Handler.Add_Error_Token
780 ( p_message_name => NULL
781 , p_message_text => l_err_text
782 , p_mesg_token_tbl => l_mesg_token_tbl
783 , x_mesg_token_tbl => l_mesg_token_tbl
784 ) ;
785
786 -- Return the status and message table.
787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
788 x_mesg_token_tbl := l_mesg_token_tbl ;
789
790 END Perform_Writes;
791
792
793
797 * Organization_Id
794 /******************************************************************************
795 * Procedure : Create_New_Routing
796 * Parameters IN : Assembly_Item_Id
798 * Alternate_Routing_Code
799 * Pending from ECN
800 * Common_Routing_Sequence_Id
801 * Routing_Type
802 * WHO columns
803 * Revised_Item_Sequence_Id
804 * Purpose : This procedure will be called when a revised operation is
805 * the first operation being added on a revised item. This
806 * procedure will create a Routing and update the revised item
807 * information indicating that routing for this revised item now
808 * exists.
809 ******************************************************************************/
810 PROCEDURE Create_New_Routing
811 ( p_assembly_item_id IN NUMBER
812 , p_organization_id IN NUMBER
813 , p_alternate_routing_code IN VARCHAR2
814 , p_pending_from_ecn IN VARCHAR2
815 , p_routing_sequence_id IN NUMBER
816 , p_common_routing_sequence_id IN NUMBER
817 , p_routing_type IN NUMBER
818 , p_last_update_date IN DATE
819 , p_last_updated_by IN NUMBER
820 , p_creation_date IN DATE
821 , p_created_by IN NUMBER
822 , p_login_id IN NUMBER
823 , p_revised_item_sequence_id IN NUMBER
824 , p_original_system_reference IN VARCHAR2
825 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
826 , x_return_status IN OUT NOCOPY VARCHAR2
827 )
828 IS
829 -- Error Handlig Variables
830 l_return_status VARCHAR2(1);
831 l_err_text VARCHAR2(2000) ;
832 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
833
834 BEGIN
835 NULL ;
836 /* commenting this following updating for the release 11i.4 to remove dependancy
837 -- on the eng odf.This is because the RTG and ECO objects should be
838 -- independant of each other. But commenting out because we will reuse it
839 -- in release 12 when these all files alongwith the odf will be base
840
841 x_return_status := FND_API.G_RET_STS_SUCCESS ;
842
843 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
844 ('Create New Routing for ECO . . .') ;
845 END IF ;
846
847 --
848 -- Create New Routing using Routing Information in Revised Item table
849 --
850 INSERT INTO bom_operational_routings
851 ( assembly_item_id
852 , organization_id
853 , alternate_routing_designator
854 , pending_from_ecn
855 , routing_sequence_id
856 , common_routing_sequence_id
857 , routing_type
858 , last_update_date
859 , last_updated_by
860 , creation_date
861 , created_by
862 , last_update_login
863 , original_system_reference
864 , cfm_routing_flag
865 , completion_subinventory
866 , completion_locator_id
867 , mixed_model_map_flag
868 , priority
869 , ctp_flag
870 , routing_comment
871 )
872 SELECT p_assembly_item_id
873 , p_organization_id
874 , p_alternate_routing_code
875 , p_pending_from_ecn
876 , p_routing_sequence_id
877 , p_common_routing_sequence_id
878 , p_routing_type
879 , p_last_update_date
880 , p_last_updated_by
881 , p_creation_date
882 , p_created_by
883 , p_login_id
884 , p_original_system_reference
885 , cfm_routing_flag
886 , completion_subinventory
887 , completion_locator_id
888 , mixed_model_map_flag
889 , priority
890 , ctp_flag
891 , routing_comment
892 FROM ENG_REVISED_ITEMS
893 WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
894
895
896 --
897 --
898 -- Set Routing Sequence Id to Revised Item table
899 --
900 UPDATE ENG_REVISED_ITEMS
901 SET routing_sequence_id = p_routing_sequence_id
902 , last_update_date = p_last_update_date -- Last Update Date
903 , last_updated_by = p_last_updated_by -- Last Updated By
904 , last_update_login = p_login_id -- Last Update Login
905 WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
906
907 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
908 ('Set created routing sequence id : ' || to_char(p_routing_sequence_id)
909 || ' to the parenet revised item . . .') ;
910 END IF ;
911
912
913 EXCEPTION
914 WHEN OTHERS THEN
915 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
916 ('Some unknown error in Creating New Routing . . .' || SQLERRM );
917 END IF ;
918
919 l_err_text := G_PKG_NAME || 'Utilities (Create New Routing) '
923
920 || substrb(SQLERRM,1,200);
921
922 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
924 Error_Handler.Add_Error_Token
925 ( p_message_name => NULL
926 , p_message_text => l_err_text
927 , p_mesg_token_tbl => l_mesg_token_tbl
928 , x_mesg_token_tbl => l_mesg_token_tbl
929 ) ;
930
931 -- Return the status and message table.
932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
933 x_mesg_token_tbl := l_mesg_token_tbl ;
934 */
935
936
937 END Create_New_Routing ;
938
939
940
941 /******************************************************************
942 * Procedure : Copy_Std_Res_and_Docs
943 * internally called by RTG BO and by ECO BO
944 * Parameters IN : p_std_operation_id
945 * Purpose : Copy Standard Operation Resource
946 * to Operation Resources
947 **********************************************************************/
948 /*
949 Bug : 3728894
950 Desc : For Standered Operations, Eco_Name was not Inserted at the time of insertion of the
951 operation_resources , so that needed to insert explicitly, because resources query at the form level searches for Eco_Name
952 and thes Resources could not be Picked at that time.
953
954 Procedure Overloaded because tree parameters are passing this time, Two argument Procedure is also taken care after this.
955
956 */
957
958 PROCEDURE Copy_Std_Res_and_Docs
959 ( p_operation_sequence_id IN NUMBER
960 , p_std_operation_id IN NUMBER
961 , p_change_notice IN VARCHAR2
962 )
963 IS
964 BEGIN
965
966 INSERT INTO BOM_OPERATION_RESOURCES
967 ( operation_sequence_id
968 , resource_seq_num
969 , resource_id
970 , acd_type
971 , activity_id
972 , standard_rate_flag
973 , assigned_units
974 , usage_rate_or_amount
975 , usage_rate_or_amount_inverse
976 , basis_type
977 , schedule_flag
978 , last_update_date
979 , last_updated_by
980 , creation_date
981 , created_by
982 , last_update_login
983 , autocharge_type
984 , attribute_category
985 , attribute1
986 , attribute2
987 , attribute3
988 , attribute4
989 , attribute5
990 , attribute6
991 , attribute7
992 , attribute8
993 , attribute9
994 , attribute10
995 , attribute11
996 , attribute12
997 , attribute13
998 , attribute14
999 , attribute15
1000 , request_id
1001 , program_application_id
1002 , program_id
1003 , program_update_date
1004 , substitute_group_num
1005 , schedule_seq_num -- Bug 7370692
1006 , principle_flag -- Bug#14550670
1007 , change_notice)
1008 SELECT p_operation_sequence_id
1009 , resource_seq_num
1010 , resource_id
1011 , DECODE( BOM_Rtg_Globals.Get_Bo_Identifier
1012 , BOM_Rtg_Globals.G_ECO_BO
1013 , 1 -- Acd Type : ADD
1014 , NULL )
1015 , activity_id
1016 , standard_rate_flag
1017 , assigned_units
1018 , usage_rate_or_amount
1019 , usage_rate_or_amount_inverse
1020 , basis_type
1021 , schedule_flag
1022 , SYSDATE -- Last Update Date
1023 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1024 , SYSDATE -- Creation Date
1025 , BOM_Rtg_Globals.Get_User_Id -- Created By
1026 , BOM_Rtg_Globals.Get_User_Id -- Last Update Login
1027 , autocharge_type
1028 , attribute_category
1029 , attribute1
1030 , attribute2
1031 , attribute3
1032 , attribute4
1033 , attribute5
1034 , attribute6
1035 , attribute7
1036 , attribute8
1037 , attribute9
1038 , attribute10
1039 , attribute11
1040 , attribute12
1041 , attribute13
1042 , attribute14
1043 , attribute15
1044 , NULL -- Request Id
1045 , BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
1046 , BOM_Rtg_Globals.Get_Prog_Id -- Prog id
1047 , SYSDATE -- Program Update Date
1048 , substitute_group_num
1049 --, resource_seq_num -- Bug 7370692 (Schedule_Seq_num=resource_seq_num)
1053 FROM BOM_STD_OP_RESOURCES
1050 , schedule_seq_num --Bug#14550670
1051 , principle_flag --Bug#14550670
1052 , p_change_notice
1054 WHERE standard_operation_id = p_std_operation_id ;
1055
1056 --
1057 -- Copy alternate resources
1058 --
1059
1060 INSERT INTO BOM_SUB_OPERATION_RESOURCES
1061 ( operation_sequence_id
1062 , substitute_group_num
1063 , resource_id
1064 , schedule_seq_num
1065 , replacement_group_num
1066 , activity_id
1067 , standard_rate_flag
1068 , assigned_units
1069 , usage_rate_or_amount
1070 , usage_rate_or_amount_inverse
1071 , basis_type
1072 , schedule_flag
1073 , autocharge_type
1074 , last_update_date
1075 , last_updated_by
1076 , creation_date
1077 , created_by
1078 , last_update_login
1079 , request_id
1080 , program_application_id
1081 , program_id
1082 , program_update_date
1083 , attribute_category
1084 , attribute1
1085 , attribute2
1086 , attribute3
1087 , attribute4
1088 , attribute5
1089 , attribute6
1090 , attribute7
1091 , attribute8
1092 , attribute9
1093 , attribute10
1094 , attribute11
1095 , attribute12
1096 , attribute13
1097 , attribute14
1098 , attribute15
1099 , principle_flag
1100 , setup_id
1101 , change_notice
1102 , acd_type
1103 , original_system_reference)
1104 SELECT p_operation_sequence_id
1105 , substitute_group_num
1106 , resource_id
1107 , schedule_seq_num -- Bug 7370692 0 -- defaulting SSN to zero and the user has to change this value.
1108 , replacement_group_num
1109 , activity_id
1110 , standard_rate_flag
1111 , assigned_units
1112 , usage_rate_or_amount
1113 , usage_rate_or_amount_inverse
1114 , basis_type
1115 , schedule_flag
1116 , autocharge_type
1117 , SYSDATE -- Last Update Date
1118 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1119 , SYSDATE -- Creation Date
1120 , BOM_Rtg_Globals.Get_User_Id -- Created By
1121 , BOM_Rtg_Globals.Get_User_Id -- Last Update Login
1122 , NULL -- Request Id
1123 , BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
1124 , BOM_Rtg_Globals.Get_Prog_Id -- Prog id
1125 , SYSDATE -- Program Update Date
1126 , attribute_category
1127 , attribute1
1128 , attribute2
1129 , attribute3
1130 , attribute4
1131 , attribute5
1132 , attribute6
1133 , attribute7
1134 , attribute8
1135 , attribute9
1136 , attribute10
1137 , attribute11
1138 , attribute12
1139 , attribute13
1140 , attribute14
1141 , attribute15
1142 , NULL -- principle_flag
1143 , NULL -- setup_id
1144 , NULL -- change_notice
1145 , DECODE( BOM_Rtg_Globals.Get_Bo_Identifier
1146 , BOM_Rtg_Globals.G_ECO_BO
1147 , 1 -- Acd Type : ADD
1148 , NULL )
1149 , NULL -- original_system_reference
1150 FROM BOM_STD_SUB_OP_RESOURCES
1151 WHERE standard_operation_id = p_std_operation_id ;
1152
1153 --
1154 -- Copy Attachment
1155 --
1156 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
1157 X_from_entity_name => 'BOM_STANDARD_OPERATIONS',
1158 X_from_pk1_value => to_char(p_std_operation_id),
1159 X_from_pk2_value => null,
1160 X_from_pk3_value => null,
1161 X_from_pk4_value => null,
1162 X_from_pk5_value => null,
1163 X_to_entity_name => 'BOM_OPERATION_SEQUENCES',
1164 X_to_pk1_value => to_char( p_operation_sequence_id ),
1165 X_to_pk2_value => null,
1166 X_to_pk3_value => null,
1167 X_to_pk4_value => null,
1168 X_to_pk5_value => null,
1172 X_program_id => BOM_Rtg_Globals.Get_Prog_Id,
1169 X_created_by => BOM_Rtg_Globals.Get_User_Id,
1170 X_last_update_login => BOM_Rtg_Globals.Get_User_Id,
1171 X_program_application_id => BOM_Rtg_Globals.Get_Prog_AppId,
1173 X_request_id => null
1174 ) ;
1175
1176
1177
1178 END Copy_Std_Res_and_Docs ;
1179 /*
1180 Bug : 3728894
1181 Desc : As Part of fix this Procedure is overloaded, with three argument.
1182 */
1183
1184
1185 PROCEDURE Copy_Std_Res_and_Docs
1186 ( p_operation_sequence_id IN NUMBER
1187 , p_std_operation_id IN NUMBER
1188 )
1189 IS
1190 BEGIN
1191 Copy_Std_Res_and_Docs( p_operation_sequence_id,p_std_operation_id,null);
1192 END Copy_Std_Res_and_Docs;
1193
1194
1195 /******************************************************************
1196 * Procedure : Copy_Old_Op_Seq_Children
1197 * internally called by RTG BO and by ECO BO
1198 * Parameters IN : p_operation_sequence_id
1199 * Purpose : Copy Old Operation Children
1200 * for Revised Operation Acd Type is Disable
1201 **********************************************************************/
1202 PROCEDURE Copy_Old_Op_Seq_Children
1203 ( p_operation_sequence_id IN NUMBER
1204 , p_old_operation_sequence_id IN NUMBER
1205 , p_eco_name IN VARCHAR2
1206 )
1207 IS
1208 BEGIN
1209
1210 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1211 ('Debug1. . . ' ) ;
1212 END IF ;
1213
1214 INSERT INTO BOM_OPERATION_RESOURCES
1215 ( operation_sequence_id
1216 , resource_seq_num
1217 , resource_id
1218 , activity_id
1219 , standard_rate_flag
1220 , assigned_units
1221 , usage_rate_or_amount
1222 , usage_rate_or_amount_inverse
1223 , basis_type
1224 , schedule_flag
1225 , last_update_date
1226 , last_updated_by
1227 , creation_date
1228 , created_by
1229 , last_update_login
1230 , resource_offset_percent
1231 , autocharge_type
1232 , attribute_category
1233 , attribute1
1234 , attribute2
1235 , attribute3
1236 , attribute4
1237 , attribute5
1238 , attribute6
1239 , attribute7
1240 , attribute8
1241 , attribute9
1242 , attribute10
1243 , attribute11
1244 , attribute12
1245 , attribute13
1246 , attribute14
1247 , attribute15
1248 , request_id
1249 , program_application_id
1250 , program_id
1251 , program_update_date
1252 , schedule_seq_num
1253 , substitute_group_num
1254 , principle_flag
1255 , change_notice
1256 , acd_type
1257 -- , original_system_reference
1258 -- , setup_id
1259 )
1260 SELECT
1261 p_operation_sequence_id
1262 , resource_seq_num
1263 , resource_id
1264 , activity_id
1265 , standard_rate_flag
1266 , assigned_units
1267 , usage_rate_or_amount
1268 , usage_rate_or_amount_inverse
1269 , basis_type
1270 , schedule_flag
1271 , SYSDATE -- Last Update Date
1272 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1273 , SYSDATE -- Creation Date
1274 , BOM_Rtg_Globals.Get_User_Id -- Created By
1275 , BOM_Rtg_Globals.Get_User_Id -- Last Update Login
1276 , resource_offset_percent
1277 , autocharge_type
1278 , attribute_category
1279 , attribute1
1280 , attribute2
1281 , attribute3
1282 , attribute4
1283 , attribute5
1284 , attribute6
1285 , attribute7
1286 , attribute8
1287 , attribute9
1288 , attribute10
1289 , attribute11
1290 , attribute12
1291 , attribute13
1292 , attribute14
1293 , attribute15
1294 , NULL -- Request Id
1295 , BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
1296 , BOM_Rtg_Globals.Get_Prog_Id -- Prog id
1297 , SYSDATE -- Program Update Date
1298 , schedule_seq_num
1299 , substitute_group_num
1300 , principle_flag
1301 , p_eco_name -- change_notice
1305 FROM BOM_OPERATION_RESOURCES
1302 , 3 -- acd_type : Disable
1303 -- , original_system_reference
1304 -- , setup_id
1306 WHERE operation_sequence_id = p_old_operation_sequence_id ;
1307
1308 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1309 ('Debug2. . . ' ) ;
1310 END IF ;
1311
1312 INSERT INTO BOM_SUB_OPERATION_RESOURCES
1313 ( operation_sequence_id
1314 , substitute_group_num
1315 , resource_id
1316 , replacement_group_num
1317 , activity_id
1318 , standard_rate_flag
1319 , assigned_units
1320 , usage_rate_or_amount
1321 , usage_rate_or_amount_inverse
1322 , basis_type
1323 , schedule_flag
1324 , last_update_date
1325 , last_updated_by
1326 , creation_date
1327 , created_by
1328 , last_update_login
1329 , resource_offset_percent
1330 , autocharge_type
1331 , principle_flag
1332 , attribute_category
1333 , attribute1
1334 , attribute2
1335 , attribute3
1336 , attribute4
1337 , attribute5
1338 , attribute6
1339 , attribute7
1340 , attribute8
1341 , attribute9
1342 , attribute10
1343 , attribute11
1344 , attribute12
1345 , attribute13
1346 , attribute14
1347 , attribute15
1348 , request_id
1349 , program_application_id
1350 , program_id
1351 , program_update_date
1352 , schedule_seq_num
1353 , change_notice
1354 , acd_type
1355 -- , original_system_reference
1356 -- , setup_id
1357 )
1358 SELECT
1359 p_operation_sequence_id
1360 , substitute_group_num
1361 , resource_id
1362 , replacement_group_num
1363 , activity_id
1364 , standard_rate_flag
1365 , assigned_units
1366 , usage_rate_or_amount
1367 , usage_rate_or_amount_inverse
1368 , basis_type
1369 , schedule_flag
1370 , SYSDATE -- Last Update Date
1371 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1372 , SYSDATE -- Creation Date
1373 , BOM_Rtg_Globals.Get_User_Id -- Created By
1374 , BOM_Rtg_Globals.Get_User_Id -- Last Update Login
1375 , resource_offset_percent
1376 , autocharge_type
1377 , principle_flag
1378 , attribute_category
1379 , attribute1
1380 , attribute2
1381 , attribute3
1382 , attribute4
1383 , attribute5
1384 , attribute6
1385 , attribute7
1386 , attribute8
1387 , attribute9
1388 , attribute10
1389 , attribute11
1390 , attribute12
1391 , attribute13
1392 , attribute14
1393 , attribute15
1394 , NULL -- Request Id
1395 , BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
1396 , BOM_Rtg_Globals.Get_Prog_Id -- Prog id
1397 , SYSDATE -- Program Update Date
1398 , schedule_seq_num
1399 , p_eco_name -- change_notice
1400 , 3 -- acd_type : Disable
1401 -- , original_system_reference
1402 -- , setup_id
1403 FROM BOM_SUB_OPERATION_RESOURCES
1404 WHERE operation_sequence_id = p_old_operation_sequence_id ;
1405
1406 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1407 ('Debug3. . . ' ) ;
1408 END IF ;
1409
1410 END Copy_Old_Op_Seq_Children ;
1411
1412
1413
1414
1415
1416 /*****************************************************************************
1417 * Procedure : Insert_Row
1418 * Parameters IN : Common Operation exposed column record
1419 * Common Operation unexposed column record
1420 * Parameters out: Return Status
1421 * Message Token Table
1422 * Purpose : This procedure will insert a record in the Operation Sequence
1423 * table; BOM_OPERATION_SEQUENCES.
1424 *
1425 *****************************************************************************/
1426 PROCEDURE Insert_Row
1427 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1428 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1429 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1430 , x_return_status IN OUT NOCOPY VARCHAR2
1431 )
1432 IS
1433 l_Bo_Id VARCHAR2(3) ;
1437 l_return_status VARCHAR2(1);
1434 l_Eco_For_Production NUMBER ;
1435
1436 -- Error Handlig Variables
1438 l_err_text VARCHAR2(2000) ;
1439 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1440
1441
1442 /* Comment out to resolve Eco Bo dependency
1443
1444 CURSOR l_eco_for_production_csr ( p_revised_item_sequence_id NUMBER )
1445 IS
1446 SELECT NVL(eco_for_production, 2)
1447 FROM ENG_REVISED_ITEMS
1448 WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
1449 */
1450
1451
1452 BEGIN
1453
1454 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1455 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1456
1457 l_Bo_Id := BOM_Rtg_Globals.Get_Bo_Identifier ;
1458
1459
1460 /* Comment out to resolve Eco Bo dependency
1461 IF l_Bo_Id = BOM_Rtg_Globals.G_ECO_BO THEN
1462
1463 IF BOM_Rtg_Globals.get_debug = 'Y'
1464 THEN
1465 error_handler.write_debug('Now Eco_For_Production in Parent Revised Item is set to this revised operation');
1466 END IF;
1467
1468 OPEN l_eco_for_production_csr ( p_revised_item_sequence_id =>
1469 p_com_op_unexp_rec.revised_item_sequence_id
1470 );
1471 FETCH l_eco_for_production_csr INTO l_Eco_For_Production ;
1472 END IF ;a
1473 */
1474
1475
1476 l_eco_for_production := BOM_Rtg_Globals.Get_Eco_For_Production;
1477
1478 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
1479 INSERT INTO BOM_OPERATION_SEQUENCES(
1480 operation_sequence_id ,
1481 routing_sequence_id ,
1482 operation_seq_num ,
1483 last_update_date ,
1484 last_updated_by ,
1485 creation_date ,
1486 created_by ,
1487 last_update_login ,
1488 standard_operation_id ,
1489 department_id ,
1490 operation_lead_time_percent ,
1491 minimum_transfer_quantity ,
1492 count_point_type ,
1493 operation_description ,
1494 effectivity_date ,
1495 disable_date ,
1496 backflush_flag ,
1497 option_dependent_flag ,
1498 attribute_category ,
1499 attribute1 ,
1500 attribute2 ,
1501 attribute3 ,
1502 attribute4 ,
1503 attribute5 ,
1504 attribute6 ,
1505 attribute7 ,
1506 attribute8 ,
1507 attribute9 ,
1508 attribute10 ,
1509 attribute11 ,
1510 attribute12 ,
1511 attribute13 ,
1512 attribute14 ,
1513 attribute15 ,
1514 request_id ,
1515 program_application_id ,
1516 program_id ,
1517 program_update_date ,
1518 operation_type ,
1519 reference_flag ,
1520 process_op_seq_id ,
1521 line_op_seq_id ,
1522 yield ,
1523 cumulative_yield ,
1524 reverse_cumulative_yield ,
1525 -- labor_time_calc ,
1526 -- machine_time_calc ,
1527 -- total_time_calc ,
1528 labor_time_user ,
1529 machine_time_user ,
1530 total_time_user ,
1531 net_planning_percent ,
1532 include_in_rollup ,
1533 operation_yield_enabled ,
1534 change_notice ,
1535 implementation_date ,
1536 old_operation_sequence_id ,
1537 acd_type ,
1538 revised_item_sequence_id ,
1539 original_system_reference ,
1540 eco_for_production ,
1541 shutdown_type -- Added by MK for eAM changes
1542 , long_description, -- Added for long description project (Bug 2689249)
1543 lowest_acceptable_yield, -- Added for MES Enhancement
1544 use_org_settings,
1545 queue_mandatory_flag,
1546 run_mandatory_flag,
1547 to_move_mandatory_flag,
1548 show_next_op_by_default,
1549 show_scrap_code,
1550 show_lot_attrib,
1551 track_multiple_res_usage_dates -- End of MES Changes
1552 )
1553 VALUES (
1554 p_com_op_unexp_rec.operation_sequence_id
1555 , p_com_op_unexp_rec.routing_sequence_id
1556 , p_com_operation_rec.operation_sequence_number
1557 , SYSDATE -- Last Update Date
1558 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1559 , SYSDATE -- Creation Date
1560 , BOM_Rtg_Globals.Get_User_Id -- Created By
1561 , BOM_Rtg_Globals.Get_Login_Id -- Last Update Login
1562 , p_com_op_unexp_rec.standard_operation_id
1563 , p_com_op_unexp_rec.department_id
1564 , p_com_operation_rec.op_lead_time_percent
1565 , p_com_operation_rec.minimum_transfer_quantity
1566 , p_com_operation_rec.count_point_type
1567 , p_com_operation_rec.operation_description
1568 , p_com_operation_rec.start_effective_date
1569 , p_com_operation_rec.disable_date
1570 , p_com_operation_rec.backflush_flag
1571 , p_com_operation_rec.option_dependent_flag
1572 , p_com_operation_rec.attribute_category
1573 , p_com_operation_rec.attribute1
1574 , p_com_operation_rec.attribute2
1575 , p_com_operation_rec.attribute3
1576 , p_com_operation_rec.attribute4
1577 , p_com_operation_rec.attribute5
1578 , p_com_operation_rec.attribute6
1579 , p_com_operation_rec.attribute7
1580 , p_com_operation_rec.attribute8
1581 , p_com_operation_rec.attribute9
1582 , p_com_operation_rec.attribute10
1583 , p_com_operation_rec.attribute11
1584 , p_com_operation_rec.attribute12
1585 , p_com_operation_rec.attribute13
1586 , p_com_operation_rec.attribute14
1587 , p_com_operation_rec.attribute15
1588 , Fnd_Global.Conc_Request_Id -- Request Id
1589 , BOM_Rtg_Globals.Get_Prog_AppId -- Application Id
1590 , BOM_Rtg_Globals.Get_Prog_Id -- Program Id
1591 , SYSDATE -- program_update_date
1595 , p_com_op_unexp_rec.line_op_seq_id
1592 , p_com_operation_rec.operation_type
1593 , p_com_operation_rec.reference_flag
1594 , p_com_op_unexp_rec.process_op_seq_id
1596 , p_com_operation_rec.yield
1597 , p_com_operation_rec.cumulative_yield
1598 , p_com_operation_rec.reverse_CUM_yield
1599 -- , p_com_operation_rec.calculated_labor_time
1600 -- , p_com_operation_rec.calculated_machine_time
1601 -- , p_com_operation_rec.calculated_elapsed_time
1602 , p_com_operation_rec.user_labor_time
1603 , p_com_operation_rec.user_machine_time
1604 , p_com_op_unexp_rec.user_elapsed_time
1605 , p_com_operation_rec.net_planning_percent
1606 -- , p_com_operation_rec.include_in_rollup
1607 -- , p_com_operation_rec.op_yield_enabled_flag
1608 , NVL(p_com_operation_rec.include_in_rollup, 1)
1609 , NVL(p_com_operation_rec.op_yield_enabled_flag,1)
1610 -- For bugfix 1744254, revop does not have these cols
1611 , p_com_operation_rec.eco_name
1612 , DECODE( l_Bo_Id, BOM_Rtg_Globals.G_RTG_BO, SYSDATE, NULL ) -- Implementation Date
1613 , p_com_op_unexp_rec.old_operation_sequence_id
1614 , p_com_operation_rec.acd_type
1615 , p_com_op_unexp_rec.revised_item_sequence_id
1616 , p_com_operation_rec.original_system_reference
1617 , l_Eco_For_Production
1618 -- DECODE( l_Bo_Id, BOM_Rtg_Globals.G_ECO_BO, l_Eco_For_Production, 2) -- Eco for Production flag
1619 , p_com_operation_rec.shutdown_type -- Added by MK for eAM changes
1620 , p_com_operation_rec.long_description -- Added for long description project (Bug 2689249)
1621 , p_com_op_unexp_rec.lowest_acceptable_yield -- Added for MES Enhancement
1622 , p_com_op_unexp_rec.use_org_settings
1623 , p_com_op_unexp_rec.queue_mandatory_flag
1624 , p_com_op_unexp_rec.run_mandatory_flag
1625 , p_com_op_unexp_rec.to_move_mandatory_flag
1626 , p_com_op_unexp_rec.show_next_op_by_default
1627 , p_com_op_unexp_rec.show_scrap_code
1628 , p_com_op_unexp_rec.show_lot_attrib
1629 , p_com_op_unexp_rec.track_multiple_res_usage_dates -- End of MES Changes
1630 );
1631
1632 IF BOM_Rtg_Globals.get_debug = 'Y'
1633 THEN
1634 error_handler.write_debug('Operation : '|| to_char(p_com_op_unexp_rec.operation_sequence_id)
1635 ||' has been created. ' );
1636 END IF;
1637
1638
1639
1640 IF p_com_op_unexp_rec.operation_sequence_id IS NOT NULL
1641 AND NVL(p_com_operation_rec.acd_type , 1 ) = 1 -- Acd Type : ADD
1642 THEN
1643 --
1644 -- Copy Standard Operation Resources
1645 --
1646 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1647 ('Copy Standard Operation Resources. . . ' ) ;
1648 END IF ;
1649
1650 IF p_com_operation_rec.reference_flag = 1 OR (p_com_operation_rec.reference_flag = 2 AND nvl(BOM_Globals.Get_Caller_Type, '') <> 'MIGRATION') THEN
1651 Copy_Std_Res_and_Docs
1652 ( p_operation_sequence_id =>
1653 p_com_op_unexp_rec.operation_sequence_id
1654 , p_std_operation_id =>
1655 p_com_op_unexp_rec.standard_operation_id
1656 , p_change_notice =>
1657 p_com_operation_rec.Eco_Name
1658 ) ;
1659 END IF;
1660
1661 ELSIF NVL(p_com_operation_rec.acd_type , 1 ) = 3 -- Acd Type : Disable
1662 AND l_Bo_Id = BOM_Rtg_Globals.G_ECO_BO
1663 THEN
1664 --
1665 -- Copy Old Operation childeren when Rev Op's Acd type is disable
1666 --
1667 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1668 ('Copy Operation Resources of Old Rev Operation when the Acd Type is disable. . . ' ) ;
1669 END IF ;
1670
1671 Copy_Old_Op_Seq_Children
1672 ( p_operation_sequence_id =>
1673 p_com_op_unexp_rec.operation_sequence_id
1674 , p_old_operation_sequence_id =>
1675 p_com_op_unexp_rec.old_operation_sequence_id
1676 , p_eco_name =>
1677 p_com_operation_rec.eco_name
1678 ) ;
1679
1680 END IF ;
1681
1682
1683
1684 EXCEPTION
1685
1686 WHEN OTHERS THEN
1687 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1688 ('Unexpected Error occured in Insert . . .' || SQLERRM);
1689 END IF;
1690
1691 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1692 THEN
1693 l_err_text := G_PKG_NAME || ' : Utility (Operation Insert) ' ||
1694 SUBSTR(SQLERRM, 1, 200);
1695 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1696
1697 Error_Handler.Add_Error_Token
1698 ( p_message_name => NULL
1699 , p_message_text => l_err_text
1700 , p_mesg_token_tbl => l_mesg_token_tbl
1701 , x_mesg_token_tbl => l_mesg_token_tbl
1702 ) ;
1703 END IF ;
1704
1705 -- Return the status and message table.
1706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1707 x_mesg_token_tbl := l_mesg_token_tbl ;
1708
1709 END Insert_Row ;
1710
1711
1712 /***************************************************************************
1713 * Procedure : Update_Row
1714 * Parameters IN : Common Operation exposed column record
1715 * Common Operation unexposed column record
1716 * Parameters out: Return Status
1717 * Message Token Table
1718 * Purpose : Update_Row procedure will update the production record with
1719 * the user given values. Any errors will be returned by filling
1720 * the Mesg_Token_Tbl and setting the return_status.
1721 ****************************************************************************/
1725 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1722 PROCEDURE Update_Row
1723 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1724 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1726 , x_return_status IN OUT NOCOPY VARCHAR2
1727 )
1728 IS
1729
1730 -- Error Handlig Variables
1731 l_return_status VARCHAR2(1);
1732 l_err_text VARCHAR2(2000) ;
1733 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1734 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1735
1736 BEGIN
1737
1738 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1739 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1740
1741
1742 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing update operation . . .') ;
1743 END IF ;
1744
1745 UPDATE BOM_OPERATION_SEQUENCES
1746 SET operation_seq_num = DECODE(p_com_operation_rec.new_operation_sequence_number ,
1747 NULL , p_com_operation_rec.operation_sequence_number ,
1748 p_com_operation_rec.new_operation_sequence_number
1749 )
1750 , last_update_date = SYSDATE /* Last Update Date */
1751 , last_updated_by = BOM_Rtg_Globals.Get_User_Id /* Last Updated By */
1752 , last_update_login = BOM_Rtg_Globals.Get_Login_Id /* Last Update Login */
1753 , standard_operation_id = p_com_op_unexp_rec.standard_operation_id
1754 , department_id = p_com_op_unexp_rec.department_id
1755 , operation_lead_time_percent = p_com_operation_rec.op_lead_time_percent
1756 , minimum_transfer_quantity = p_com_operation_rec.minimum_transfer_quantity
1757 , count_point_type = p_com_operation_rec.count_point_type
1758 , operation_description = p_com_operation_rec.operation_description
1759 , effectivity_date = DECODE(p_com_operation_rec.new_start_effective_date ,
1760 NULL , p_com_operation_rec.start_effective_date ,
1761 p_com_operation_rec.new_start_effective_date
1762 )
1763 , disable_date = p_com_operation_rec.disable_date
1764 , backflush_flag = p_com_operation_rec.backflush_flag
1765 , option_dependent_flag = p_com_operation_rec.option_dependent_flag
1766 , attribute_category = p_com_operation_rec.attribute_category
1767 , attribute1 = p_com_operation_rec.attribute1
1768 , attribute2 = p_com_operation_rec.attribute2
1769 , attribute3 = p_com_operation_rec.attribute3
1770 , attribute4 = p_com_operation_rec.attribute4
1771 , attribute5 = p_com_operation_rec.attribute5
1772 , attribute6 = p_com_operation_rec.attribute6
1773 , attribute7 = p_com_operation_rec.attribute7
1774 , attribute8 = p_com_operation_rec.attribute8
1775 , attribute9 = p_com_operation_rec.attribute9
1776 , attribute10 = p_com_operation_rec.attribute10
1777 , attribute11 = p_com_operation_rec.attribute11
1778 , attribute12 = p_com_operation_rec.attribute12
1779 , attribute13 = p_com_operation_rec.attribute13
1780 , attribute14 = p_com_operation_rec.attribute14
1781 , attribute15 = p_com_operation_rec.attribute15
1782 , program_application_id = BOM_Rtg_Globals.Get_Prog_AppId /* Application Id */
1783 , program_id = BOM_Rtg_Globals.Get_Prog_Id /* Program Id */
1784 , program_update_date = SYSDATE /* program_update_date */
1785 , reference_flag = p_com_operation_rec.reference_flag
1786 , process_op_seq_id = p_com_op_unexp_rec.process_op_seq_id
1787 , line_op_seq_id = p_com_op_unexp_rec.line_op_seq_id
1788 , yield = p_com_operation_rec.yield
1789 , cumulative_yield = p_com_operation_rec.cumulative_yield
1790 , reverse_cumulative_yield = p_com_operation_rec.reverse_CUM_yield
1791 -- , labor_time_calc = p_com_operation_rec.calculated_labor_time
1792 -- , machine_time_calc = p_com_operation_rec.calculated_machine_time
1793 -- , total_time_calc = p_com_operation_rec.calculated_elapsed_time
1794 , labor_time_user = p_com_operation_rec.user_labor_time
1795 , machine_time_user = p_com_operation_rec.user_machine_time
1796 , total_time_user = p_com_op_unexp_rec.user_elapsed_time
1797 , net_planning_percent = p_com_operation_rec.net_planning_percent
1798 -- , include_in_rollup = p_com_operation_rec.include_in_rollup
1799 -- , operation_yield_enabled = p_com_operation_rec.op_yield_enabled_flag
1800 , include_in_rollup = NVL(p_com_operation_rec.include_in_rollup,1)
1801 , operation_yield_enabled = NVL(p_com_operation_rec.op_yield_enabled_flag,1)
1802 -- For bugfix 1744254, RevOp does not have these cols
1803 , acd_type = p_com_operation_rec.acd_type
1804 , original_system_reference = p_com_operation_rec.original_system_reference
1805 , shutdown_type = p_com_operation_rec.shutdown_type -- Added by MK for eAM changes
1806 , long_description = p_com_operation_rec.long_description -- Added for long description (Bug 2689249)
1807 , request_id = Fnd_Global.Conc_Request_Id
1808 WHERE operation_sequence_id = p_com_op_unexp_rec.operation_sequence_id ;
1809
1810
1814 , bic.LAST_UPDATED_BY = BOM_Rtg_Globals.Get_User_Id
1811 UPDATE BOM_INVENTORY_COMPONENTS bic
1812 SET bic.operation_lead_time_percent = p_com_operation_rec.op_lead_time_percent
1813 , bic.LAST_UPDATE_DATE = SYSDATE
1815 , bic.LAST_UPDATE_LOGIN = BOM_Rtg_Globals.Get_Login_Id
1816 , bic.REQUEST_ID = Fnd_Global.Conc_Request_Id
1817 , bic.PROGRAM_ID = Fnd_Global.Conc_Program_Id
1818 , bic.PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
1819 , bic.PROGRAM_UPDATE_DATE = SYSDATE
1820 WHERE bic.operation_seq_num = p_com_operation_rec.operation_sequence_number
1821 AND bic.bill_sequence_id = (SELECT bom.BILL_SEQUENCE_ID
1822 FROM BOM_BILL_OF_MATERIALS bom
1823 , bom_operational_routings bor
1824 WHERE NVL(bor.alternate_routing_designator, 'NONE')
1825 = nvl(bom.alternate_bom_designator,'NONE')
1826 AND bom.assembly_item_id = bor.assembly_item_id
1827 AND bom.organization_id = bor.organization_id
1828 AND bor.routing_sequence_id = p_com_op_unexp_rec.routing_sequence_id) ;
1829
1830 IF SQL%FOUND THEN
1831 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1832 ('Operation lead time in bom inventory components refered Operation Seq Num is updated.') ;
1833 END IF ;
1834
1835 --
1836 -- Log Warning Message
1837 --
1838 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
1839 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
1840
1841 Error_Handler.Add_Error_Token
1842 ( p_message_name => 'BOM_OP_LT_PCT_UPDATED'
1843 , p_mesg_token_tbl => l_mesg_token_Tbl
1844 , x_mesg_token_tbl => l_mesg_token_tbl
1845 , p_message_type => 'W' /* Warning */
1846 , p_Token_Tbl => l_Token_Tbl
1847 ) ;
1848 END IF ;
1849
1850
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1854 ('Unexpected Error occured in Update . . .' || SQLERRM);
1855 END IF;
1856
1857 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1858 THEN
1859 l_err_text := G_PKG_NAME || ' : Utility (Operation Update) ' ||
1860 SUBSTR(SQLERRM, 1, 200);
1861 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1862
1863 Error_Handler.Add_Error_Token
1864 ( p_message_name => NULL
1865 , p_message_text => l_err_text
1866 , p_mesg_token_tbl => l_mesg_token_tbl
1867 , x_mesg_token_tbl => l_mesg_token_tbl
1868 ) ;
1869 END IF ;
1870
1871 -- Return the status and message table.
1872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1873 x_mesg_token_tbl := l_mesg_token_tbl ;
1874
1875 END Update_Row ;
1876
1877
1878 /********************************************************************
1879 * Procedure : Delete_Row
1880 * Parameters IN : Common Operation exposed column record
1881 * Common Operation unexposed column record
1882 * Parameters out: Return Status
1883 * Message Token Table
1884 * Purpose : For ECO BO, procedure will delete a revised operation
1885 * record for a ECO.
1886 * This procedure will not delete a record in production
1887 * which is already implemented.
1888 * For RTG BO, Procedure will perfrom an Delete from the
1889 * BOM_Operation_Sequences by creating a delete Group.
1890 *********************************************************************/
1891 PROCEDURE Delete_Row
1892 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1893 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1894 , x_com_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Operation_Rec_Type
1895 , x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1896 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1897 , x_return_status IN OUT NOCOPY VARCHAR2
1898 )
1899 IS
1900
1901 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1902 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1903 l_dg_sequence_id NUMBER;
1904
1905
1906 -- Error Handlig Variables
1907 l_return_status VARCHAR2(1);
1908 l_err_text VARCHAR2(2000) ;
1909 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1910 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1911 l_rtg_type NUMBER;
1912
1913
1914
1915 -- Check Delete Group
1916 CURSOR l_del_grp_csr ( p_del_group_name VARCHAR2
1917 , p_organization_id NUMBER )
1918 IS
1919 SELECT description
1920 , delete_group_sequence_id
1921 , delete_type
1922 FROM BOM_DELETE_GROUPS
1923 WHERE delete_group_name = p_del_group_name
1924 AND organization_id = p_organization_id ;
1925
1926 -- Exception
1927 DUPLICATE_DEL_GROUP EXCEPTION ;
1928
1929 BEGIN
1930 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1931 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1932
1933 --
1934 -- Initialize Common Record and Status
1935 --
1939
1936 l_com_operation_rec := p_com_operation_rec ;
1937 l_com_op_unexp_rec := p_com_op_unexp_rec ;
1938
1940 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1941 THEN
1942
1943 DELETE FROM BOM_OPERATION_SEQUENCES
1944 WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
1945
1946
1947 DELETE FROM BOM_OPERATION_RESOURCES
1948 WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
1949
1950
1951 /******************************************************************
1952 -- Also delete the operation resources and substitute resources
1953 -- by first logging a warning notifying the user of the cascaded
1954 -- Delete.
1955 *******************************************************************/
1956
1957 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1958 AND SQL%FOUND
1959 -- This is a warning.
1960 THEN
1961 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
1962 l_Token_Tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
1963
1964 Error_Handler.Add_Error_Token
1965 ( p_Message_Name => 'BOM_OP_DEL_CHILDREN'
1966 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1967 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1968 , p_Token_Tbl => l_Token_Tbl
1969 , p_message_type => 'W'
1970 ) ;
1971 END IF;
1972
1973
1974 DELETE FROM BOM_SUB_OPERATION_RESOURCES
1975 WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
1976
1977
1978 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1979 Error_Handler.Write_Debug('Finished deleting revised operation record . . .') ;
1980 END IF ;
1981
1982
1983 ELSIF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
1984 THEN
1985 FOR l_del_grp_rec IN l_del_grp_csr
1986 ( p_del_group_name => l_com_operation_rec.delete_group_name
1987 , p_organization_id => l_com_op_unexp_rec.organization_id )
1988 LOOP
1989 IF l_del_grp_rec.delete_type <> 5 /* Operation */
1990 THEN
1991 RAISE DUPLICATE_DEL_GROUP ;
1992 END IF ;
1993
1994 --
1995 -- If Delete Group for Opetaion exists, Set Delete Group Sequence Id
1996 -- and Description to Unexposed Column
1997 --
1998 l_com_op_unexp_rec.DG_Sequence_Id := l_del_grp_rec.delete_group_sequence_id ;
1999 l_com_op_unexp_rec.DG_Description := l_del_grp_rec.description ;
2000
2001 END LOOP;
2002
2003 IF l_com_op_unexp_rec.DG_Sequence_Id <> FND_API.G_MISS_NUM
2004 THEN
2005 l_dg_sequence_id := l_com_op_unexp_rec.DG_Sequence_Id;
2006 ELSE
2007 --
2008 -- If l_dg_seqeunce_id is Null, Modal_Delete.Delte_Manager procedure
2009 -- would create New Delete Group
2010 --
2011 l_dg_sequence_id := NULL;
2012
2013 Error_Handler.Add_Error_Token
2014 ( p_message_name => 'NEW_DELETE_GROUP'
2015 , p_mesg_token_tbl => l_mesg_token_Tbl
2016 , x_mesg_token_tbl => l_mesg_token_tbl
2017 , p_message_type => 'W' /* Warning */
2018 ) ;
2019
2020 END IF;
2021
2022
2023 IF l_com_operation_rec.alternate_routing_code = FND_API.G_MISS_CHAR THEN
2024 l_com_operation_rec.alternate_routing_code := NULL ;
2025 END IF ;
2026
2027 -- bug 5199643
2028 select routing_type into l_rtg_type from bom_operational_routings
2029 where routing_sequence_id = l_com_op_unexp_rec.routing_sequence_id;
2030
2031
2032 l_dg_sequence_id :=
2033 MODAL_DELETE.DELETE_MANAGER
2034 ( new_group_seq_id => l_dg_sequence_id,
2035 name => l_com_operation_rec.Delete_Group_Name,
2036 group_desc => l_com_operation_rec.dg_description,
2037 org_id => l_com_op_unexp_rec.organization_id,
2038 bom_or_eng => l_rtg_type /* bug 5199643 */,
2039 del_type => 5 /* Operation */,
2040 ent_bill_seq_id => NULL,
2041 ent_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id,
2042 ent_inv_item_id => l_com_op_unexp_rec.revised_item_id,
2043 ent_alt_designator => l_com_operation_rec.alternate_routing_code,
2044 ent_comp_seq_id => NULL,
2045 ent_op_seq_id => l_com_op_unexp_rec.operation_sequence_id ,
2046 user_id => BOM_Rtg_Globals.Get_User_Id
2047 ) ;
2048
2049 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2050 Error_Handler.Write_Debug('Finished creatin new delete group . . .') ;
2051 END IF ;
2052
2053 END IF ;
2054
2055 -- Return records
2056 x_com_operation_rec := l_com_operation_rec ;
2057 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2058
2059 -- Return the status and message table.
2060 x_return_status := l_return_status;
2061 x_mesg_token_tbl := l_mesg_token_tbl;
2062
2063
2064 EXCEPTION
2065 WHEN DUPLICATE_DEL_GROUP THEN
2066
2067 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2068 ('Duplicate Delete Group Error occured in Delete . . .' || SQLERRM);
2069 END IF;
2070
2071
2075 Error_Handler.Add_Error_Token
2072 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2073 THEN
2074
2076 ( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
2077 , p_mesg_token_tbl => l_mesg_token_Tbl
2078 , x_mesg_token_tbl => l_mesg_token_tbl
2079 ) ;
2080 END IF ;
2081
2082 -- Return records
2083 x_com_operation_rec := l_com_operation_rec ;
2084 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2085
2086 -- Return the status and message table.
2087 x_return_status := FND_API.G_RET_STS_ERROR;
2088 x_mesg_token_tbl := l_mesg_token_tbl;
2089
2090 WHEN OTHERS THEN
2091 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2092 ('Unexpected Error occured in Delete . . .' || SQLERRM);
2093 END IF;
2094
2095 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2096 THEN
2097 l_err_text := G_PKG_NAME || ' : Utility (Operation Delete) ' ||
2098 SUBSTR(SQLERRM, 1, 200);
2099 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2100
2101 Error_Handler.Add_Error_Token
2102 ( p_message_name => NULL
2103 , p_message_text => l_err_text
2104 , p_mesg_token_tbl => l_mesg_token_tbl
2105 , x_mesg_token_tbl => l_mesg_token_tbl
2106 ) ;
2107 END IF ;
2108
2109 -- Return records
2110 x_com_operation_rec := l_com_operation_rec ;
2111 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2112
2113 -- Return the status and message table.
2114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2115 x_mesg_token_tbl := l_mesg_token_tbl ;
2116
2117 END Delete_Row ;
2118
2119
2120
2121 /********************************************************************
2122 * Procedure : Cancel_Operaiton
2123 * Parameters IN : Common Operation exposed column record
2124 * Common Operation unexposed column record
2125 * Parameters out: Return Status
2126 * Message Token Table
2127 * Purpose : This procedure will move revised operation to Eng Revised
2128 * Operation table and set cansel information.
2129 * Also it will delte any child operation resources and sub
2130 * operation resources.
2131 *********************************************************************/
2132 PROCEDURE Cancel_Operation
2133 ( p_operation_sequence_id IN NUMBER
2134 , p_cancel_comments IN VARCHAR2
2135 , p_op_seq_num IN NUMBER
2136 , p_user_id IN NUMBER
2137 , p_login_id IN NUMBER
2138 , p_prog_id IN NUMBER
2139 , p_prog_appid IN NUMBER
2140 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2141 , x_return_status IN OUT NOCOPY VARCHAR2
2142 )
2143
2144
2145 IS
2146
2147 -- Error Handlig Variables
2148 l_return_status VARCHAR2(1);
2149 l_err_text VARCHAR2(2000) ;
2150 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
2151 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2152
2153
2154 BEGIN
2155 null;
2156 /* commenting this procedure for the release 11i.4 to remove dependancy
2157 on the eng odf.This is because the RTG and ECO objects should be
2158 independant of each other. But commenting out because we will reuse it
2159 in release 12 when these all files alongwith the odf will be base
2160
2161 l_return_status := FND_API.G_RET_STS_SUCCESS ;
2162 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2163
2164 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2165 Error_Handler.Write_Debug('Performing cancel revised operation : '
2166 || to_char(p_operation_sequence_id) || ' . . .') ;
2167 END IF ;
2168
2169 --
2170 -- Insert the cancelled revised operation into
2171 -- ENG_REVISED_OPERATIONS
2172 --
2173 INSERT INTO ENG_REVISED_OPERATIONS (
2174 operation_sequence_id
2175 , routing_sequence_id
2176 , operation_seq_num
2177 , last_update_date
2178 , last_updated_by
2179 , creation_date
2180 , created_by
2181 , last_update_login
2182 , standard_operation_id
2183 , department_id
2184 , operation_lead_time_percent
2185 , minimum_transfer_quantity
2186 , count_point_type
2187 , operation_description
2188 , effectivity_date
2189 , disable_date
2190 , backflush_flag
2191 , option_dependent_flag
2192 , attribute_category
2193 , attribute1
2194 , attribute2
2195 , attribute3
2196 , attribute4
2197 , attribute5
2198 , attribute6
2199 , attribute7
2200 , attribute8
2201 , attribute9
2202 , attribute10
2203 , attribute11
2204 , attribute12
2205 , attribute13
2206 , attribute14
2207 , attribute15
2208 , request_id
2209 , program_application_id
2210 , program_id
2211 , program_update_date
2212 , operation_type
2213 , reference_flag
2214 , process_op_seq_id
2215 , line_op_seq_id
2219 , labor_time_calc
2216 , yield
2217 , cumulative_yield
2218 , reverse_cumulative_yield
2220 , machine_time_calc
2221 , total_time_calc
2222 , labor_time_user
2223 , machine_time_user
2224 , total_time_user
2225 , net_planning_percent
2226 , x_coordinate
2227 , y_coordinate
2228 , include_in_rollup
2229 , operation_yield_enabled
2230 , change_notice
2231 , implementation_date
2232 , old_operation_sequence_id
2233 , acd_type
2234 , revised_item_sequence_id
2235 , cancellation_date
2236 , cancel_comments
2237 , original_system_reference )
2238 SELECT
2239 OPERATION_SEQUENCE_ID
2240 , routing_sequence_id
2241 , OPERATION_SEQ_NUM
2242 , SYSDATE -- Last Update Date
2243 , p_user_id -- Last Updated By
2244 , SYSDATE -- Creation Date
2245 , p_user_id -- Created By
2246 , p_login_id -- Last Update Login
2247 , STANDARD_OPERATION_ID
2248 , DEPARTMENT_ID
2249 , OPERATION_LEAD_TIME_PERCENT
2250 , MINIMUM_TRANSFER_QUANTITY
2251 , COUNT_POINT_TYPE
2252 , OPERATION_DESCRIPTION
2253 , EFFECTIVITY_DATE
2254 , DISABLE_DATE
2255 , BACKFLUSH_FLAG
2256 , OPTION_DEPENDENT_FLAG
2257 , ATTRIBUTE_CATEGORY
2258 , ATTRIBUTE1
2259 , ATTRIBUTE2
2260 , ATTRIBUTE3
2261 , ATTRIBUTE4
2262 , ATTRIBUTE5
2263 , ATTRIBUTE6
2264 , ATTRIBUTE7
2265 , ATTRIBUTE8
2266 , ATTRIBUTE9
2267 , ATTRIBUTE10
2268 , ATTRIBUTE11
2269 , ATTRIBUTE12
2270 , ATTRIBUTE13
2271 , ATTRIBUTE14
2272 , ATTRIBUTE15
2273 , NULL -- Request Id
2274 , p_prog_appid -- Application Id
2275 , p_prog_id -- Program Id
2276 , SYSDATE -- program_update_date
2277 , OPERATION_TYPE
2278 , REFERENCE_FLAG
2279 , PROCESS_OP_SEQ_ID
2280 , LINE_OP_SEQ_ID
2281 , YIELD
2282 , CUMULATIVE_YIELD
2283 , REVERSE_CUMULATIVE_YIELD
2284 , LABOR_TIME_CALC
2285 , MACHINE_TIME_CALC
2286 , TOTAL_TIME_CALC
2287 , LABOR_TIME_USER
2288 , MACHINE_TIME_USER
2289 , TOTAL_TIME_USER
2290 , NET_PLANNING_PERCENT
2291 , X_COORDINATE
2292 , Y_COORDINATE
2293 , INCLUDE_IN_ROLLUP
2294 , OPERATION_YIELD_ENABLED
2295 , CHANGE_NOTICE
2296 , IMPLEMENTATION_DATE
2297 , OLD_OPERATION_SEQUENCE_ID
2298 , ACD_TYPE
2299 , REVISED_ITEM_SEQUENCE_ID
2300 , SYSDATE -- Cancellation Date
2301 , p_cancel_comments -- Cancel Comments
2302 , ORIGINAL_SYSTEM_REFERENCE
2303 FROM BOM_OPERATION_SEQUENCES
2304 WHERE operation_sequence_id = p_operation_sequence_id ;
2305
2306
2307 --
2308 -- Delete Cancel Revisd Operation from operation table
2309 --
2310 DELETE FROM BOM_OPERATION_SEQUENCES
2311 WHERE operation_sequence_id = p_operation_sequence_id ;
2312
2313 --
2314 -- Delete child Operation Resources
2315 --
2316 DELETE FROM BOM_OPERATION_RESOURCES
2317 WHERE operation_sequence_id = p_operation_sequence_id ;
2318
2319
2320 IF SQL%FOUND THEN
2321
2322 --
2323 -- Log a warning indicating operation resources and
2324 -- substitute operation resources also get deleted.
2325 --
2326 -- l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
2327 -- l_Token_Tbl(1).token_value := p_op_seq_num ;
2328
2329 Error_Handler.Add_Error_Token
2330 ( p_Message_Name => 'BOM_OP_CANCEL_DEL_CHILDREN'
2331 , p_Message_Text => NULL
2332 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2333 , p_Token_Tbl => l_Token_Tbl
2334 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2335 , p_message_type => 'W'
2336 ) ;
2337
2338 END IF ;
2339
2340
2341 --
2342 -- Delete child Sub Operation Resources
2343 --
2344 DELETE FROM BOM_SUB_OPERATION_RESOURCES
2345 WHERE operation_sequence_id = p_operation_sequence_id ;
2346
2347 -- Return Token
2348 x_mesg_token_tbl := l_mesg_token_tbl ;
2349
2350
2351 EXCEPTION
2352 WHEN OTHERS THEN
2353 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2354 ('Unexpected Error occured in Cancel . . .' || SQLERRM);
2355 END IF;
2356
2357 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2358 THEN
2359 l_err_text := G_PKG_NAME || ' : Utility (Operation Cancel) ' ||
2360 SUBSTR(SQLERRM, 1, 200);
2361 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2362
2363 Error_Handler.Add_Error_Token
2364 ( p_message_name => NULL
2365 , p_message_text => l_err_text
2366 , p_mesg_token_tbl => l_mesg_token_tbl
2367 , x_mesg_token_tbl => l_mesg_token_tbl
2368 ) ;
2369 END IF ;
2370
2371 -- Return the status and message table.
2372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2373 x_mesg_token_tbl := l_mesg_token_tbl ;
2374 */
2375 END Cancel_Operation ;
2376
2377 END BOM_Op_Seq_UTIL ;