[Home] [Help]
PACKAGE BODY: APPS.BOM_OP_SEQ_UTIL
Source
1 PACKAGE BODY BOM_Op_Seq_UTIL AS
2 /* $Header: BOMUOPSB.pls 120.4.12010000.2 2008/10/15 12:05:28 tbhande ship $ */
3 /****************************************************************************
4 --
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
133 , x_return_status => x_return_status
134 );
135
136 -- Convert the Common record to Revised Operation record
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.
160 ********************************************************************/
161
162
163 PROCEDURE Query_Row
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 )
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
207 AND routing_sequence_id = p_routing_sequence_id
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 ;
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 ;
300 l_com_operation_rec.Attribute5 := op_seq_rec.ATTRIBUTE5 ;
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
413 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
414 , x_return_status IN OUT NOCOPY VARCHAR2
415 )
416 IS
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
540 , p_alternate_rtg_code => l_com_operation_rec.alternate_routing_code
541 )
542 LOOP
543 --
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
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
659 AND NOT EXISTS( SELECT NULL
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
794 /******************************************************************************
795 * Procedure : Create_New_Routing
796 * Parameters IN : Assembly_Item_Id
797 * Organization_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) '
920 || substrb(SQLERRM,1,200);
921
922 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
923
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 , change_notice)
1007 SELECT p_operation_sequence_id
1008 , resource_seq_num
1009 , resource_id
1010 , DECODE( BOM_Rtg_Globals.Get_Bo_Identifier
1011 , BOM_Rtg_Globals.G_ECO_BO
1012 , 1 -- Acd Type : ADD
1013 , NULL )
1014 , activity_id
1015 , standard_rate_flag
1016 , assigned_units
1017 , usage_rate_or_amount
1018 , usage_rate_or_amount_inverse
1019 , basis_type
1020 , schedule_flag
1021 , SYSDATE -- Last Update Date
1022 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1023 , SYSDATE -- Creation Date
1024 , BOM_Rtg_Globals.Get_User_Id -- Created By
1025 , BOM_Rtg_Globals.Get_User_Id -- Last Update Login
1026 , autocharge_type
1027 , attribute_category
1028 , attribute1
1029 , attribute2
1030 , attribute3
1031 , attribute4
1032 , attribute5
1033 , attribute6
1034 , attribute7
1035 , attribute8
1036 , attribute9
1037 , attribute10
1038 , attribute11
1039 , attribute12
1040 , attribute13
1041 , attribute14
1042 , attribute15
1043 , NULL -- Request Id
1044 , BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
1045 , BOM_Rtg_Globals.Get_Prog_Id -- Prog id
1046 , SYSDATE -- Program Update Date
1047 , substitute_group_num
1048 , resource_seq_num -- Bug 7370692 (Schedule_Seq_num=resource_seq_num)
1049 , p_change_notice
1050 FROM BOM_STD_OP_RESOURCES
1051 WHERE standard_operation_id = p_std_operation_id ;
1052
1053 --
1054 -- Copy alternate resources
1055 --
1056
1057 INSERT INTO BOM_SUB_OPERATION_RESOURCES
1058 ( operation_sequence_id
1059 , substitute_group_num
1060 , resource_id
1061 , schedule_seq_num
1062 , replacement_group_num
1063 , activity_id
1064 , standard_rate_flag
1065 , assigned_units
1066 , usage_rate_or_amount
1067 , usage_rate_or_amount_inverse
1068 , basis_type
1069 , schedule_flag
1070 , autocharge_type
1071 , last_update_date
1072 , last_updated_by
1073 , creation_date
1074 , created_by
1075 , last_update_login
1076 , request_id
1077 , program_application_id
1078 , program_id
1079 , program_update_date
1080 , attribute_category
1081 , attribute1
1082 , attribute2
1083 , attribute3
1084 , attribute4
1085 , attribute5
1086 , attribute6
1087 , attribute7
1088 , attribute8
1089 , attribute9
1090 , attribute10
1091 , attribute11
1092 , attribute12
1093 , attribute13
1094 , attribute14
1095 , attribute15
1096 , principle_flag
1097 , setup_id
1098 , change_notice
1099 , acd_type
1100 , original_system_reference)
1101 SELECT p_operation_sequence_id
1102 , substitute_group_num
1103 , resource_id
1104 , schedule_seq_num -- Bug 7370692 0 -- defaulting SSN to zero and the user has to change this value.
1105 , replacement_group_num
1106 , activity_id
1107 , standard_rate_flag
1108 , assigned_units
1109 , usage_rate_or_amount
1110 , usage_rate_or_amount_inverse
1111 , basis_type
1112 , schedule_flag
1113 , autocharge_type
1114 , SYSDATE -- Last Update Date
1115 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1116 , SYSDATE -- Creation Date
1117 , BOM_Rtg_Globals.Get_User_Id -- Created By
1118 , BOM_Rtg_Globals.Get_User_Id -- Last Update Login
1119 , NULL -- Request Id
1120 , BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
1121 , BOM_Rtg_Globals.Get_Prog_Id -- Prog id
1122 , SYSDATE -- Program Update Date
1123 , attribute_category
1124 , attribute1
1125 , attribute2
1126 , attribute3
1127 , attribute4
1128 , attribute5
1129 , attribute6
1130 , attribute7
1131 , attribute8
1132 , attribute9
1133 , attribute10
1134 , attribute11
1135 , attribute12
1136 , attribute13
1137 , attribute14
1138 , attribute15
1139 , NULL -- principle_flag
1140 , NULL -- setup_id
1141 , NULL -- change_notice
1142 , DECODE( BOM_Rtg_Globals.Get_Bo_Identifier
1143 , BOM_Rtg_Globals.G_ECO_BO
1144 , 1 -- Acd Type : ADD
1145 , NULL )
1146 , NULL -- original_system_reference
1147 FROM BOM_STD_SUB_OP_RESOURCES
1148 WHERE standard_operation_id = p_std_operation_id ;
1149
1150 --
1151 -- Copy Attachment
1152 --
1153 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
1154 X_from_entity_name => 'BOM_STANDARD_OPERATIONS',
1155 X_from_pk1_value => to_char(p_std_operation_id),
1156 X_from_pk2_value => null,
1157 X_from_pk3_value => null,
1158 X_from_pk4_value => null,
1159 X_from_pk5_value => null,
1160 X_to_entity_name => 'BOM_OPERATION_SEQUENCES',
1161 X_to_pk1_value => to_char( p_operation_sequence_id ),
1162 X_to_pk2_value => null,
1163 X_to_pk3_value => null,
1164 X_to_pk4_value => null,
1165 X_to_pk5_value => null,
1166 X_created_by => BOM_Rtg_Globals.Get_User_Id,
1167 X_last_update_login => BOM_Rtg_Globals.Get_User_Id,
1168 X_program_application_id => BOM_Rtg_Globals.Get_Prog_AppId,
1169 X_program_id => BOM_Rtg_Globals.Get_Prog_Id,
1170 X_request_id => null
1171 ) ;
1172
1173
1174
1175 END Copy_Std_Res_and_Docs ;
1176 /*
1177 Bug : 3728894
1178 Desc : As Part of fix this Procedure is overloaded, with three argument.
1179 */
1180
1181
1182 PROCEDURE Copy_Std_Res_and_Docs
1183 ( p_operation_sequence_id IN NUMBER
1184 , p_std_operation_id IN NUMBER
1185 )
1186 IS
1187 BEGIN
1188 Copy_Std_Res_and_Docs( p_operation_sequence_id,p_std_operation_id,null);
1189 END Copy_Std_Res_and_Docs;
1190
1191
1192 /******************************************************************
1193 * Procedure : Copy_Old_Op_Seq_Children
1194 * internally called by RTG BO and by ECO BO
1195 * Parameters IN : p_operation_sequence_id
1196 * Purpose : Copy Old Operation Children
1197 * for Revised Operation Acd Type is Disable
1198 **********************************************************************/
1199 PROCEDURE Copy_Old_Op_Seq_Children
1200 ( p_operation_sequence_id IN NUMBER
1201 , p_old_operation_sequence_id IN NUMBER
1202 , p_eco_name IN VARCHAR2
1203 )
1204 IS
1205 BEGIN
1206
1207 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1208 ('Debug1. . . ' ) ;
1209 END IF ;
1210
1211 INSERT INTO BOM_OPERATION_RESOURCES
1212 ( operation_sequence_id
1213 , resource_seq_num
1214 , resource_id
1215 , activity_id
1216 , standard_rate_flag
1217 , assigned_units
1218 , usage_rate_or_amount
1219 , usage_rate_or_amount_inverse
1220 , basis_type
1221 , schedule_flag
1222 , last_update_date
1223 , last_updated_by
1224 , creation_date
1225 , created_by
1226 , last_update_login
1227 , resource_offset_percent
1228 , autocharge_type
1229 , attribute_category
1230 , attribute1
1231 , attribute2
1232 , attribute3
1233 , attribute4
1234 , attribute5
1235 , attribute6
1236 , attribute7
1237 , attribute8
1238 , attribute9
1239 , attribute10
1240 , attribute11
1241 , attribute12
1242 , attribute13
1243 , attribute14
1244 , attribute15
1245 , request_id
1246 , program_application_id
1247 , program_id
1248 , program_update_date
1249 , schedule_seq_num
1250 , substitute_group_num
1251 , principle_flag
1252 , change_notice
1253 , acd_type
1254 -- , original_system_reference
1255 -- , setup_id
1256 )
1257 SELECT
1258 p_operation_sequence_id
1259 , resource_seq_num
1260 , resource_id
1261 , activity_id
1262 , standard_rate_flag
1263 , assigned_units
1264 , usage_rate_or_amount
1265 , usage_rate_or_amount_inverse
1266 , basis_type
1267 , schedule_flag
1268 , SYSDATE -- Last Update Date
1269 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1270 , SYSDATE -- Creation Date
1271 , BOM_Rtg_Globals.Get_User_Id -- Created By
1272 , BOM_Rtg_Globals.Get_User_Id -- Last Update Login
1273 , resource_offset_percent
1274 , autocharge_type
1275 , attribute_category
1276 , attribute1
1277 , attribute2
1278 , attribute3
1279 , attribute4
1280 , attribute5
1281 , attribute6
1282 , attribute7
1283 , attribute8
1284 , attribute9
1285 , attribute10
1286 , attribute11
1287 , attribute12
1288 , attribute13
1289 , attribute14
1290 , attribute15
1291 , NULL -- Request Id
1292 , BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
1293 , BOM_Rtg_Globals.Get_Prog_Id -- Prog id
1294 , SYSDATE -- Program Update Date
1295 , schedule_seq_num
1296 , substitute_group_num
1297 , principle_flag
1298 , p_eco_name -- change_notice
1299 , 3 -- acd_type : Disable
1300 -- , original_system_reference
1301 -- , setup_id
1302 FROM BOM_OPERATION_RESOURCES
1303 WHERE operation_sequence_id = p_old_operation_sequence_id ;
1304
1305 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1306 ('Debug2. . . ' ) ;
1307 END IF ;
1308
1309 INSERT INTO BOM_SUB_OPERATION_RESOURCES
1310 ( operation_sequence_id
1311 , substitute_group_num
1312 , resource_id
1313 , replacement_group_num
1314 , activity_id
1315 , standard_rate_flag
1316 , assigned_units
1317 , usage_rate_or_amount
1318 , usage_rate_or_amount_inverse
1319 , basis_type
1320 , schedule_flag
1321 , last_update_date
1322 , last_updated_by
1323 , creation_date
1324 , created_by
1325 , last_update_login
1326 , resource_offset_percent
1327 , autocharge_type
1328 , principle_flag
1329 , attribute_category
1330 , attribute1
1331 , attribute2
1332 , attribute3
1333 , attribute4
1334 , attribute5
1335 , attribute6
1336 , attribute7
1337 , attribute8
1338 , attribute9
1339 , attribute10
1340 , attribute11
1341 , attribute12
1342 , attribute13
1343 , attribute14
1344 , attribute15
1345 , request_id
1346 , program_application_id
1347 , program_id
1348 , program_update_date
1349 , schedule_seq_num
1350 , change_notice
1351 , acd_type
1352 -- , original_system_reference
1353 -- , setup_id
1354 )
1355 SELECT
1356 p_operation_sequence_id
1357 , substitute_group_num
1358 , resource_id
1359 , replacement_group_num
1360 , activity_id
1361 , standard_rate_flag
1362 , assigned_units
1363 , usage_rate_or_amount
1364 , usage_rate_or_amount_inverse
1365 , basis_type
1366 , schedule_flag
1367 , SYSDATE -- Last Update Date
1368 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1369 , SYSDATE -- Creation Date
1370 , BOM_Rtg_Globals.Get_User_Id -- Created By
1371 , BOM_Rtg_Globals.Get_User_Id -- Last Update Login
1372 , resource_offset_percent
1373 , autocharge_type
1374 , principle_flag
1375 , attribute_category
1376 , attribute1
1377 , attribute2
1378 , attribute3
1379 , attribute4
1380 , attribute5
1381 , attribute6
1382 , attribute7
1383 , attribute8
1384 , attribute9
1385 , attribute10
1386 , attribute11
1387 , attribute12
1388 , attribute13
1389 , attribute14
1390 , attribute15
1391 , NULL -- Request Id
1392 , BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
1393 , BOM_Rtg_Globals.Get_Prog_Id -- Prog id
1394 , SYSDATE -- Program Update Date
1395 , schedule_seq_num
1396 , p_eco_name -- change_notice
1397 , 3 -- acd_type : Disable
1398 -- , original_system_reference
1399 -- , setup_id
1400 FROM BOM_SUB_OPERATION_RESOURCES
1401 WHERE operation_sequence_id = p_old_operation_sequence_id ;
1402
1403 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1404 ('Debug3. . . ' ) ;
1405 END IF ;
1406
1407 END Copy_Old_Op_Seq_Children ;
1408
1409
1410
1411
1412
1413 /*****************************************************************************
1414 * Procedure : Insert_Row
1415 * Parameters IN : Common Operation exposed column record
1416 * Common Operation unexposed column record
1417 * Parameters out: Return Status
1418 * Message Token Table
1419 * Purpose : This procedure will insert a record in the Operation Sequence
1420 * table; BOM_OPERATION_SEQUENCES.
1421 *
1422 *****************************************************************************/
1423 PROCEDURE Insert_Row
1424 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1425 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1426 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1427 , x_return_status IN OUT NOCOPY VARCHAR2
1428 )
1429 IS
1430 l_Bo_Id VARCHAR2(3) ;
1431 l_Eco_For_Production NUMBER ;
1432
1433 -- Error Handlig Variables
1434 l_return_status VARCHAR2(1);
1435 l_err_text VARCHAR2(2000) ;
1436 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1437
1438
1439 /* Comment out to resolve Eco Bo dependency
1440
1441 CURSOR l_eco_for_production_csr ( p_revised_item_sequence_id NUMBER )
1442 IS
1443 SELECT NVL(eco_for_production, 2)
1444 FROM ENG_REVISED_ITEMS
1445 WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
1446 */
1447
1448
1449 BEGIN
1450
1451 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1452 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1453
1454 l_Bo_Id := BOM_Rtg_Globals.Get_Bo_Identifier ;
1455
1456
1457 /* Comment out to resolve Eco Bo dependency
1458 IF l_Bo_Id = BOM_Rtg_Globals.G_ECO_BO THEN
1459
1460 IF BOM_Rtg_Globals.get_debug = 'Y'
1461 THEN
1462 error_handler.write_debug('Now Eco_For_Production in Parent Revised Item is set to this revised operation');
1463 END IF;
1464
1465 OPEN l_eco_for_production_csr ( p_revised_item_sequence_id =>
1466 p_com_op_unexp_rec.revised_item_sequence_id
1467 );
1468 FETCH l_eco_for_production_csr INTO l_Eco_For_Production ;
1469 END IF ;a
1470 */
1471
1472
1473 l_eco_for_production := BOM_Rtg_Globals.Get_Eco_For_Production;
1474
1475 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
1476 INSERT INTO BOM_OPERATION_SEQUENCES(
1477 operation_sequence_id ,
1478 routing_sequence_id ,
1479 operation_seq_num ,
1480 last_update_date ,
1481 last_updated_by ,
1482 creation_date ,
1483 created_by ,
1484 last_update_login ,
1485 standard_operation_id ,
1486 department_id ,
1487 operation_lead_time_percent ,
1488 minimum_transfer_quantity ,
1489 count_point_type ,
1490 operation_description ,
1491 effectivity_date ,
1492 disable_date ,
1493 backflush_flag ,
1494 option_dependent_flag ,
1495 attribute_category ,
1496 attribute1 ,
1497 attribute2 ,
1498 attribute3 ,
1499 attribute4 ,
1500 attribute5 ,
1501 attribute6 ,
1502 attribute7 ,
1503 attribute8 ,
1504 attribute9 ,
1505 attribute10 ,
1506 attribute11 ,
1507 attribute12 ,
1508 attribute13 ,
1509 attribute14 ,
1510 attribute15 ,
1511 request_id ,
1512 program_application_id ,
1513 program_id ,
1514 program_update_date ,
1515 operation_type ,
1516 reference_flag ,
1517 process_op_seq_id ,
1518 line_op_seq_id ,
1519 yield ,
1520 cumulative_yield ,
1521 reverse_cumulative_yield ,
1522 -- labor_time_calc ,
1523 -- machine_time_calc ,
1524 -- total_time_calc ,
1525 labor_time_user ,
1526 machine_time_user ,
1527 total_time_user ,
1528 net_planning_percent ,
1529 include_in_rollup ,
1530 operation_yield_enabled ,
1531 change_notice ,
1532 implementation_date ,
1533 old_operation_sequence_id ,
1534 acd_type ,
1535 revised_item_sequence_id ,
1536 original_system_reference ,
1537 eco_for_production ,
1538 shutdown_type -- Added by MK for eAM changes
1539 , long_description, -- Added for long description project (Bug 2689249)
1540 lowest_acceptable_yield, -- Added for MES Enhancement
1541 use_org_settings,
1542 queue_mandatory_flag,
1543 run_mandatory_flag,
1544 to_move_mandatory_flag,
1545 show_next_op_by_default,
1546 show_scrap_code,
1547 show_lot_attrib,
1548 track_multiple_res_usage_dates -- End of MES Changes
1549 )
1550 VALUES (
1551 p_com_op_unexp_rec.operation_sequence_id
1552 , p_com_op_unexp_rec.routing_sequence_id
1553 , p_com_operation_rec.operation_sequence_number
1554 , SYSDATE -- Last Update Date
1555 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
1556 , SYSDATE -- Creation Date
1557 , BOM_Rtg_Globals.Get_User_Id -- Created By
1558 , BOM_Rtg_Globals.Get_Login_Id -- Last Update Login
1559 , p_com_op_unexp_rec.standard_operation_id
1560 , p_com_op_unexp_rec.department_id
1561 , p_com_operation_rec.op_lead_time_percent
1562 , p_com_operation_rec.minimum_transfer_quantity
1563 , p_com_operation_rec.count_point_type
1564 , p_com_operation_rec.operation_description
1565 , p_com_operation_rec.start_effective_date
1566 , p_com_operation_rec.disable_date
1567 , p_com_operation_rec.backflush_flag
1568 , p_com_operation_rec.option_dependent_flag
1569 , p_com_operation_rec.attribute_category
1570 , p_com_operation_rec.attribute1
1571 , p_com_operation_rec.attribute2
1572 , p_com_operation_rec.attribute3
1573 , p_com_operation_rec.attribute4
1574 , p_com_operation_rec.attribute5
1575 , p_com_operation_rec.attribute6
1576 , p_com_operation_rec.attribute7
1577 , p_com_operation_rec.attribute8
1578 , p_com_operation_rec.attribute9
1579 , p_com_operation_rec.attribute10
1580 , p_com_operation_rec.attribute11
1581 , p_com_operation_rec.attribute12
1582 , p_com_operation_rec.attribute13
1583 , p_com_operation_rec.attribute14
1584 , p_com_operation_rec.attribute15
1585 , Fnd_Global.Conc_Request_Id -- Request Id
1586 , BOM_Rtg_Globals.Get_Prog_AppId -- Application Id
1587 , BOM_Rtg_Globals.Get_Prog_Id -- Program Id
1588 , SYSDATE -- program_update_date
1589 , p_com_operation_rec.operation_type
1590 , p_com_operation_rec.reference_flag
1591 , p_com_op_unexp_rec.process_op_seq_id
1592 , p_com_op_unexp_rec.line_op_seq_id
1593 , p_com_operation_rec.yield
1594 , p_com_operation_rec.cumulative_yield
1595 , p_com_operation_rec.reverse_CUM_yield
1596 -- , p_com_operation_rec.calculated_labor_time
1597 -- , p_com_operation_rec.calculated_machine_time
1598 -- , p_com_operation_rec.calculated_elapsed_time
1599 , p_com_operation_rec.user_labor_time
1600 , p_com_operation_rec.user_machine_time
1601 , p_com_op_unexp_rec.user_elapsed_time
1602 , p_com_operation_rec.net_planning_percent
1603 -- , p_com_operation_rec.include_in_rollup
1604 -- , p_com_operation_rec.op_yield_enabled_flag
1605 , NVL(p_com_operation_rec.include_in_rollup, 1)
1606 , NVL(p_com_operation_rec.op_yield_enabled_flag,1)
1607 -- For bugfix 1744254, revop does not have these cols
1608 , p_com_operation_rec.eco_name
1609 , DECODE( l_Bo_Id, BOM_Rtg_Globals.G_RTG_BO, SYSDATE, NULL ) -- Implementation Date
1610 , p_com_op_unexp_rec.old_operation_sequence_id
1611 , p_com_operation_rec.acd_type
1612 , p_com_op_unexp_rec.revised_item_sequence_id
1613 , p_com_operation_rec.original_system_reference
1614 , l_Eco_For_Production
1615 -- DECODE( l_Bo_Id, BOM_Rtg_Globals.G_ECO_BO, l_Eco_For_Production, 2) -- Eco for Production flag
1616 , p_com_operation_rec.shutdown_type -- Added by MK for eAM changes
1617 , p_com_operation_rec.long_description -- Added for long description project (Bug 2689249)
1618 , p_com_op_unexp_rec.lowest_acceptable_yield -- Added for MES Enhancement
1619 , p_com_op_unexp_rec.use_org_settings
1620 , p_com_op_unexp_rec.queue_mandatory_flag
1621 , p_com_op_unexp_rec.run_mandatory_flag
1622 , p_com_op_unexp_rec.to_move_mandatory_flag
1623 , p_com_op_unexp_rec.show_next_op_by_default
1624 , p_com_op_unexp_rec.show_scrap_code
1625 , p_com_op_unexp_rec.show_lot_attrib
1626 , p_com_op_unexp_rec.track_multiple_res_usage_dates -- End of MES Changes
1627 );
1628
1629 IF BOM_Rtg_Globals.get_debug = 'Y'
1630 THEN
1631 error_handler.write_debug('Operation : '|| to_char(p_com_op_unexp_rec.operation_sequence_id)
1632 ||' has been created. ' );
1633 END IF;
1634
1635
1636
1637 IF p_com_op_unexp_rec.operation_sequence_id IS NOT NULL
1638 AND NVL(p_com_operation_rec.acd_type , 1 ) = 1 -- Acd Type : ADD
1639 THEN
1640 --
1641 -- Copy Standard Operation Resources
1642 --
1643 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1644 ('Copy Standard Operation Resources. . . ' ) ;
1645 END IF ;
1646
1647 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
1648 Copy_Std_Res_and_Docs
1649 ( p_operation_sequence_id =>
1650 p_com_op_unexp_rec.operation_sequence_id
1651 , p_std_operation_id =>
1652 p_com_op_unexp_rec.standard_operation_id
1653 , p_change_notice =>
1654 p_com_operation_rec.Eco_Name
1655 ) ;
1656 END IF;
1657
1658 ELSIF NVL(p_com_operation_rec.acd_type , 1 ) = 3 -- Acd Type : Disable
1659 AND l_Bo_Id = BOM_Rtg_Globals.G_ECO_BO
1660 THEN
1661 --
1662 -- Copy Old Operation childeren when Rev Op's Acd type is disable
1663 --
1664 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1665 ('Copy Operation Resources of Old Rev Operation when the Acd Type is disable. . . ' ) ;
1666 END IF ;
1667
1668 Copy_Old_Op_Seq_Children
1669 ( p_operation_sequence_id =>
1670 p_com_op_unexp_rec.operation_sequence_id
1671 , p_old_operation_sequence_id =>
1672 p_com_op_unexp_rec.old_operation_sequence_id
1673 , p_eco_name =>
1674 p_com_operation_rec.eco_name
1675 ) ;
1676
1677 END IF ;
1678
1679
1680
1681 EXCEPTION
1682
1683 WHEN OTHERS THEN
1684 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1685 ('Unexpected Error occured in Insert . . .' || SQLERRM);
1686 END IF;
1687
1688 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1689 THEN
1690 l_err_text := G_PKG_NAME || ' : Utility (Operation Insert) ' ||
1691 SUBSTR(SQLERRM, 1, 200);
1692 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1693
1694 Error_Handler.Add_Error_Token
1695 ( p_message_name => NULL
1696 , p_message_text => l_err_text
1697 , p_mesg_token_tbl => l_mesg_token_tbl
1698 , x_mesg_token_tbl => l_mesg_token_tbl
1699 ) ;
1700 END IF ;
1701
1702 -- Return the status and message table.
1703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1704 x_mesg_token_tbl := l_mesg_token_tbl ;
1705
1706 END Insert_Row ;
1707
1708
1709 /***************************************************************************
1710 * Procedure : Update_Row
1711 * Parameters IN : Common Operation exposed column record
1712 * Common Operation unexposed column record
1713 * Parameters out: Return Status
1714 * Message Token Table
1715 * Purpose : Update_Row procedure will update the production record with
1716 * the user given values. Any errors will be returned by filling
1717 * the Mesg_Token_Tbl and setting the return_status.
1718 ****************************************************************************/
1719 PROCEDURE Update_Row
1720 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1721 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1722 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1723 , x_return_status IN OUT NOCOPY VARCHAR2
1724 )
1725 IS
1726
1727 -- Error Handlig Variables
1728 l_return_status VARCHAR2(1);
1729 l_err_text VARCHAR2(2000) ;
1730 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1731 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1732
1733 BEGIN
1734
1735 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1736 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1737
1738
1739 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing update operation . . .') ;
1740 END IF ;
1741
1742 UPDATE BOM_OPERATION_SEQUENCES
1743 SET operation_seq_num = DECODE(p_com_operation_rec.new_operation_sequence_number ,
1744 NULL , p_com_operation_rec.operation_sequence_number ,
1745 p_com_operation_rec.new_operation_sequence_number
1746 )
1747 , last_update_date = SYSDATE /* Last Update Date */
1748 , last_updated_by = BOM_Rtg_Globals.Get_User_Id /* Last Updated By */
1749 , last_update_login = BOM_Rtg_Globals.Get_Login_Id /* Last Update Login */
1750 , standard_operation_id = p_com_op_unexp_rec.standard_operation_id
1751 , department_id = p_com_op_unexp_rec.department_id
1752 , operation_lead_time_percent = p_com_operation_rec.op_lead_time_percent
1753 , minimum_transfer_quantity = p_com_operation_rec.minimum_transfer_quantity
1754 , count_point_type = p_com_operation_rec.count_point_type
1755 , operation_description = p_com_operation_rec.operation_description
1756 , effectivity_date = DECODE(p_com_operation_rec.new_start_effective_date ,
1757 NULL , p_com_operation_rec.start_effective_date ,
1758 p_com_operation_rec.new_start_effective_date
1759 )
1760 , disable_date = p_com_operation_rec.disable_date
1761 , backflush_flag = p_com_operation_rec.backflush_flag
1762 , option_dependent_flag = p_com_operation_rec.option_dependent_flag
1763 , attribute_category = p_com_operation_rec.attribute_category
1764 , attribute1 = p_com_operation_rec.attribute1
1765 , attribute2 = p_com_operation_rec.attribute2
1766 , attribute3 = p_com_operation_rec.attribute3
1767 , attribute4 = p_com_operation_rec.attribute4
1768 , attribute5 = p_com_operation_rec.attribute5
1769 , attribute6 = p_com_operation_rec.attribute6
1770 , attribute7 = p_com_operation_rec.attribute7
1771 , attribute8 = p_com_operation_rec.attribute8
1772 , attribute9 = p_com_operation_rec.attribute9
1773 , attribute10 = p_com_operation_rec.attribute10
1774 , attribute11 = p_com_operation_rec.attribute11
1775 , attribute12 = p_com_operation_rec.attribute12
1776 , attribute13 = p_com_operation_rec.attribute13
1777 , attribute14 = p_com_operation_rec.attribute14
1778 , attribute15 = p_com_operation_rec.attribute15
1779 , program_application_id = BOM_Rtg_Globals.Get_Prog_AppId /* Application Id */
1780 , program_id = BOM_Rtg_Globals.Get_Prog_Id /* Program Id */
1781 , program_update_date = SYSDATE /* program_update_date */
1782 , reference_flag = p_com_operation_rec.reference_flag
1783 , process_op_seq_id = p_com_op_unexp_rec.process_op_seq_id
1784 , line_op_seq_id = p_com_op_unexp_rec.line_op_seq_id
1785 , yield = p_com_operation_rec.yield
1786 , cumulative_yield = p_com_operation_rec.cumulative_yield
1787 , reverse_cumulative_yield = p_com_operation_rec.reverse_CUM_yield
1788 -- , labor_time_calc = p_com_operation_rec.calculated_labor_time
1789 -- , machine_time_calc = p_com_operation_rec.calculated_machine_time
1790 -- , total_time_calc = p_com_operation_rec.calculated_elapsed_time
1791 , labor_time_user = p_com_operation_rec.user_labor_time
1792 , machine_time_user = p_com_operation_rec.user_machine_time
1793 , total_time_user = p_com_op_unexp_rec.user_elapsed_time
1794 , net_planning_percent = p_com_operation_rec.net_planning_percent
1795 -- , include_in_rollup = p_com_operation_rec.include_in_rollup
1796 -- , operation_yield_enabled = p_com_operation_rec.op_yield_enabled_flag
1797 , include_in_rollup = NVL(p_com_operation_rec.include_in_rollup,1)
1798 , operation_yield_enabled = NVL(p_com_operation_rec.op_yield_enabled_flag,1)
1799 -- For bugfix 1744254, RevOp does not have these cols
1800 , acd_type = p_com_operation_rec.acd_type
1801 , original_system_reference = p_com_operation_rec.original_system_reference
1802 , shutdown_type = p_com_operation_rec.shutdown_type -- Added by MK for eAM changes
1803 , long_description = p_com_operation_rec.long_description -- Added for long description (Bug 2689249)
1804 , request_id = Fnd_Global.Conc_Request_Id
1805 WHERE operation_sequence_id = p_com_op_unexp_rec.operation_sequence_id ;
1806
1807
1808 UPDATE BOM_INVENTORY_COMPONENTS bic
1809 SET bic.operation_lead_time_percent = p_com_operation_rec.op_lead_time_percent
1810 , bic.LAST_UPDATE_DATE = SYSDATE
1811 , bic.LAST_UPDATED_BY = BOM_Rtg_Globals.Get_User_Id
1812 , bic.LAST_UPDATE_LOGIN = BOM_Rtg_Globals.Get_Login_Id
1813 , bic.REQUEST_ID = Fnd_Global.Conc_Request_Id
1814 , bic.PROGRAM_ID = Fnd_Global.Conc_Program_Id
1815 , bic.PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
1816 , bic.PROGRAM_UPDATE_DATE = SYSDATE
1817 WHERE bic.operation_seq_num = p_com_operation_rec.operation_sequence_number
1818 AND bic.bill_sequence_id = (SELECT bom.BILL_SEQUENCE_ID
1819 FROM BOM_BILL_OF_MATERIALS bom
1820 , bom_operational_routings bor
1821 WHERE NVL(bor.alternate_routing_designator, 'NONE')
1822 = nvl(bom.alternate_bom_designator,'NONE')
1823 AND bom.assembly_item_id = bor.assembly_item_id
1824 AND bom.organization_id = bor.organization_id
1825 AND bor.routing_sequence_id = p_com_op_unexp_rec.routing_sequence_id) ;
1826
1827 IF SQL%FOUND THEN
1828 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1829 ('Operation lead time in bom inventory components refered Operation Seq Num is updated.') ;
1830 END IF ;
1831
1832 --
1833 -- Log Warning Message
1834 --
1835 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
1836 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
1837
1838 Error_Handler.Add_Error_Token
1839 ( p_message_name => 'BOM_OP_LT_PCT_UPDATED'
1840 , p_mesg_token_tbl => l_mesg_token_Tbl
1841 , x_mesg_token_tbl => l_mesg_token_tbl
1842 , p_message_type => 'W' /* Warning */
1843 , p_Token_Tbl => l_Token_Tbl
1844 ) ;
1845 END IF ;
1846
1847
1848 EXCEPTION
1849 WHEN OTHERS THEN
1850 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1851 ('Unexpected Error occured in Update . . .' || SQLERRM);
1852 END IF;
1853
1854 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1855 THEN
1856 l_err_text := G_PKG_NAME || ' : Utility (Operation Update) ' ||
1857 SUBSTR(SQLERRM, 1, 200);
1858 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1859
1860 Error_Handler.Add_Error_Token
1861 ( p_message_name => NULL
1862 , p_message_text => l_err_text
1863 , p_mesg_token_tbl => l_mesg_token_tbl
1864 , x_mesg_token_tbl => l_mesg_token_tbl
1865 ) ;
1866 END IF ;
1867
1868 -- Return the status and message table.
1869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1870 x_mesg_token_tbl := l_mesg_token_tbl ;
1871
1872 END Update_Row ;
1873
1874
1875 /********************************************************************
1876 * Procedure : Delete_Row
1877 * Parameters IN : Common Operation exposed column record
1878 * Common Operation unexposed column record
1879 * Parameters out: Return Status
1880 * Message Token Table
1881 * Purpose : For ECO BO, procedure will delete a revised operation
1882 * record for a ECO.
1883 * This procedure will not delete a record in production
1884 * which is already implemented.
1885 * For RTG BO, Procedure will perfrom an Delete from the
1886 * BOM_Operation_Sequences by creating a delete Group.
1887 *********************************************************************/
1888 PROCEDURE Delete_Row
1889 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1890 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1891 , x_com_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Operation_Rec_Type
1892 , x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1893 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1894 , x_return_status IN OUT NOCOPY VARCHAR2
1895 )
1896 IS
1897
1898 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1899 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1900 l_dg_sequence_id NUMBER;
1901
1902
1903 -- Error Handlig Variables
1904 l_return_status VARCHAR2(1);
1905 l_err_text VARCHAR2(2000) ;
1906 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1907 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1908 l_rtg_type NUMBER;
1909
1910
1911
1912 -- Check Delete Group
1913 CURSOR l_del_grp_csr ( p_del_group_name VARCHAR2
1914 , p_organization_id NUMBER )
1915 IS
1916 SELECT description
1917 , delete_group_sequence_id
1918 , delete_type
1919 FROM BOM_DELETE_GROUPS
1920 WHERE delete_group_name = p_del_group_name
1921 AND organization_id = p_organization_id ;
1922
1923 -- Exception
1924 DUPLICATE_DEL_GROUP EXCEPTION ;
1925
1926 BEGIN
1927 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1928 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1929
1930 --
1931 -- Initialize Common Record and Status
1932 --
1933 l_com_operation_rec := p_com_operation_rec ;
1934 l_com_op_unexp_rec := p_com_op_unexp_rec ;
1935
1936
1937 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1938 THEN
1939
1940 DELETE FROM BOM_OPERATION_SEQUENCES
1941 WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
1942
1943
1944 DELETE FROM BOM_OPERATION_RESOURCES
1945 WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
1946
1947
1948 /******************************************************************
1949 -- Also delete the operation resources and substitute resources
1950 -- by first logging a warning notifying the user of the cascaded
1951 -- Delete.
1952 *******************************************************************/
1953
1954 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1955 AND SQL%FOUND
1956 -- This is a warning.
1957 THEN
1958 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
1959 l_Token_Tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
1960
1961 Error_Handler.Add_Error_Token
1962 ( p_Message_Name => 'BOM_OP_DEL_CHILDREN'
1963 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1964 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1965 , p_Token_Tbl => l_Token_Tbl
1966 , p_message_type => 'W'
1967 ) ;
1968 END IF;
1969
1970
1971 DELETE FROM BOM_SUB_OPERATION_RESOURCES
1972 WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
1973
1974
1975 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1976 Error_Handler.Write_Debug('Finished deleting revised operation record . . .') ;
1977 END IF ;
1978
1979
1980 ELSIF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
1981 THEN
1982 FOR l_del_grp_rec IN l_del_grp_csr
1983 ( p_del_group_name => l_com_operation_rec.delete_group_name
1984 , p_organization_id => l_com_op_unexp_rec.organization_id )
1985 LOOP
1986 IF l_del_grp_rec.delete_type <> 5 /* Operation */
1987 THEN
1988 RAISE DUPLICATE_DEL_GROUP ;
1989 END IF ;
1990
1991 --
1992 -- If Delete Group for Opetaion exists, Set Delete Group Sequence Id
1993 -- and Description to Unexposed Column
1994 --
1995 l_com_op_unexp_rec.DG_Sequence_Id := l_del_grp_rec.delete_group_sequence_id ;
1996 l_com_op_unexp_rec.DG_Description := l_del_grp_rec.description ;
1997
1998 END LOOP;
1999
2000 IF l_com_op_unexp_rec.DG_Sequence_Id <> FND_API.G_MISS_NUM
2001 THEN
2002 l_dg_sequence_id := l_com_op_unexp_rec.DG_Sequence_Id;
2003 ELSE
2004 --
2005 -- If l_dg_seqeunce_id is Null, Modal_Delete.Delte_Manager procedure
2006 -- would create New Delete Group
2007 --
2008 l_dg_sequence_id := NULL;
2009
2010 Error_Handler.Add_Error_Token
2011 ( p_message_name => 'NEW_DELETE_GROUP'
2012 , p_mesg_token_tbl => l_mesg_token_Tbl
2013 , x_mesg_token_tbl => l_mesg_token_tbl
2014 , p_message_type => 'W' /* Warning */
2015 ) ;
2016
2017 END IF;
2018
2019
2020 IF l_com_operation_rec.alternate_routing_code = FND_API.G_MISS_CHAR THEN
2021 l_com_operation_rec.alternate_routing_code := NULL ;
2022 END IF ;
2023
2024 -- bug 5199643
2025 select routing_type into l_rtg_type from bom_operational_routings
2026 where routing_sequence_id = l_com_op_unexp_rec.routing_sequence_id;
2027
2028
2029 l_dg_sequence_id :=
2030 MODAL_DELETE.DELETE_MANAGER
2031 ( new_group_seq_id => l_dg_sequence_id,
2032 name => l_com_operation_rec.Delete_Group_Name,
2033 group_desc => l_com_operation_rec.dg_description,
2034 org_id => l_com_op_unexp_rec.organization_id,
2035 bom_or_eng => l_rtg_type /* bug 5199643 */,
2036 del_type => 5 /* Operation */,
2037 ent_bill_seq_id => NULL,
2038 ent_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id,
2039 ent_inv_item_id => l_com_op_unexp_rec.revised_item_id,
2040 ent_alt_designator => l_com_operation_rec.alternate_routing_code,
2041 ent_comp_seq_id => NULL,
2042 ent_op_seq_id => l_com_op_unexp_rec.operation_sequence_id ,
2043 user_id => BOM_Rtg_Globals.Get_User_Id
2044 ) ;
2045
2046 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2047 Error_Handler.Write_Debug('Finished creatin new delete group . . .') ;
2048 END IF ;
2049
2050 END IF ;
2051
2052 -- Return records
2053 x_com_operation_rec := l_com_operation_rec ;
2054 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2055
2056 -- Return the status and message table.
2057 x_return_status := l_return_status;
2058 x_mesg_token_tbl := l_mesg_token_tbl;
2059
2060
2061 EXCEPTION
2062 WHEN DUPLICATE_DEL_GROUP THEN
2063
2064 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2065 ('Duplicate Delete Group Error occured in Delete . . .' || SQLERRM);
2066 END IF;
2067
2068
2069 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2070 THEN
2071
2072 Error_Handler.Add_Error_Token
2073 ( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
2074 , p_mesg_token_tbl => l_mesg_token_Tbl
2075 , x_mesg_token_tbl => l_mesg_token_tbl
2076 ) ;
2077 END IF ;
2078
2079 -- Return records
2080 x_com_operation_rec := l_com_operation_rec ;
2081 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2082
2083 -- Return the status and message table.
2084 x_return_status := FND_API.G_RET_STS_ERROR;
2085 x_mesg_token_tbl := l_mesg_token_tbl;
2086
2087 WHEN OTHERS THEN
2088 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2089 ('Unexpected Error occured in Delete . . .' || SQLERRM);
2090 END IF;
2091
2092 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2093 THEN
2094 l_err_text := G_PKG_NAME || ' : Utility (Operation Delete) ' ||
2095 SUBSTR(SQLERRM, 1, 200);
2096 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2097
2098 Error_Handler.Add_Error_Token
2099 ( p_message_name => NULL
2100 , p_message_text => l_err_text
2101 , p_mesg_token_tbl => l_mesg_token_tbl
2102 , x_mesg_token_tbl => l_mesg_token_tbl
2103 ) ;
2104 END IF ;
2105
2106 -- Return records
2107 x_com_operation_rec := l_com_operation_rec ;
2108 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2109
2110 -- Return the status and message table.
2111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2112 x_mesg_token_tbl := l_mesg_token_tbl ;
2113
2114 END Delete_Row ;
2115
2116
2117
2118 /********************************************************************
2119 * Procedure : Cancel_Operaiton
2120 * Parameters IN : Common Operation exposed column record
2121 * Common Operation unexposed column record
2122 * Parameters out: Return Status
2123 * Message Token Table
2124 * Purpose : This procedure will move revised operation to Eng Revised
2125 * Operation table and set cansel information.
2126 * Also it will delte any child operation resources and sub
2127 * operation resources.
2128 *********************************************************************/
2129 PROCEDURE Cancel_Operation
2130 ( p_operation_sequence_id IN NUMBER
2131 , p_cancel_comments IN VARCHAR2
2132 , p_op_seq_num IN NUMBER
2133 , p_user_id IN NUMBER
2134 , p_login_id IN NUMBER
2135 , p_prog_id IN NUMBER
2136 , p_prog_appid IN NUMBER
2137 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2138 , x_return_status IN OUT NOCOPY VARCHAR2
2139 )
2140
2141
2142 IS
2143
2144 -- Error Handlig Variables
2145 l_return_status VARCHAR2(1);
2146 l_err_text VARCHAR2(2000) ;
2147 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
2148 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2149
2150
2151 BEGIN
2152 null;
2153 /* commenting this procedure for the release 11i.4 to remove dependancy
2154 on the eng odf.This is because the RTG and ECO objects should be
2155 independant of each other. But commenting out because we will reuse it
2156 in release 12 when these all files alongwith the odf will be base
2157
2158 l_return_status := FND_API.G_RET_STS_SUCCESS ;
2159 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2160
2161 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2162 Error_Handler.Write_Debug('Performing cancel revised operation : '
2163 || to_char(p_operation_sequence_id) || ' . . .') ;
2164 END IF ;
2165
2166 --
2167 -- Insert the cancelled revised operation into
2168 -- ENG_REVISED_OPERATIONS
2169 --
2170 INSERT INTO ENG_REVISED_OPERATIONS (
2171 operation_sequence_id
2172 , routing_sequence_id
2173 , operation_seq_num
2174 , last_update_date
2175 , last_updated_by
2176 , creation_date
2177 , created_by
2178 , last_update_login
2179 , standard_operation_id
2180 , department_id
2181 , operation_lead_time_percent
2182 , minimum_transfer_quantity
2183 , count_point_type
2184 , operation_description
2185 , effectivity_date
2186 , disable_date
2187 , backflush_flag
2188 , option_dependent_flag
2189 , attribute_category
2190 , attribute1
2191 , attribute2
2192 , attribute3
2193 , attribute4
2194 , attribute5
2195 , attribute6
2196 , attribute7
2197 , attribute8
2198 , attribute9
2199 , attribute10
2200 , attribute11
2201 , attribute12
2202 , attribute13
2203 , attribute14
2204 , attribute15
2205 , request_id
2206 , program_application_id
2207 , program_id
2208 , program_update_date
2209 , operation_type
2210 , reference_flag
2211 , process_op_seq_id
2212 , line_op_seq_id
2213 , yield
2214 , cumulative_yield
2215 , reverse_cumulative_yield
2216 , labor_time_calc
2217 , machine_time_calc
2218 , total_time_calc
2219 , labor_time_user
2220 , machine_time_user
2221 , total_time_user
2222 , net_planning_percent
2223 , x_coordinate
2224 , y_coordinate
2225 , include_in_rollup
2226 , operation_yield_enabled
2227 , change_notice
2228 , implementation_date
2229 , old_operation_sequence_id
2230 , acd_type
2231 , revised_item_sequence_id
2232 , cancellation_date
2233 , cancel_comments
2234 , original_system_reference )
2235 SELECT
2236 OPERATION_SEQUENCE_ID
2237 , routing_sequence_id
2238 , OPERATION_SEQ_NUM
2239 , SYSDATE -- Last Update Date
2240 , p_user_id -- Last Updated By
2241 , SYSDATE -- Creation Date
2242 , p_user_id -- Created By
2243 , p_login_id -- Last Update Login
2244 , STANDARD_OPERATION_ID
2245 , DEPARTMENT_ID
2246 , OPERATION_LEAD_TIME_PERCENT
2247 , MINIMUM_TRANSFER_QUANTITY
2248 , COUNT_POINT_TYPE
2249 , OPERATION_DESCRIPTION
2250 , EFFECTIVITY_DATE
2251 , DISABLE_DATE
2252 , BACKFLUSH_FLAG
2253 , OPTION_DEPENDENT_FLAG
2254 , ATTRIBUTE_CATEGORY
2255 , ATTRIBUTE1
2256 , ATTRIBUTE2
2257 , ATTRIBUTE3
2258 , ATTRIBUTE4
2259 , ATTRIBUTE5
2260 , ATTRIBUTE6
2261 , ATTRIBUTE7
2262 , ATTRIBUTE8
2263 , ATTRIBUTE9
2264 , ATTRIBUTE10
2265 , ATTRIBUTE11
2266 , ATTRIBUTE12
2267 , ATTRIBUTE13
2268 , ATTRIBUTE14
2269 , ATTRIBUTE15
2270 , NULL -- Request Id
2271 , p_prog_appid -- Application Id
2272 , p_prog_id -- Program Id
2273 , SYSDATE -- program_update_date
2274 , OPERATION_TYPE
2275 , REFERENCE_FLAG
2276 , PROCESS_OP_SEQ_ID
2277 , LINE_OP_SEQ_ID
2278 , YIELD
2279 , CUMULATIVE_YIELD
2280 , REVERSE_CUMULATIVE_YIELD
2281 , LABOR_TIME_CALC
2282 , MACHINE_TIME_CALC
2283 , TOTAL_TIME_CALC
2284 , LABOR_TIME_USER
2285 , MACHINE_TIME_USER
2286 , TOTAL_TIME_USER
2287 , NET_PLANNING_PERCENT
2288 , X_COORDINATE
2289 , Y_COORDINATE
2290 , INCLUDE_IN_ROLLUP
2291 , OPERATION_YIELD_ENABLED
2292 , CHANGE_NOTICE
2293 , IMPLEMENTATION_DATE
2294 , OLD_OPERATION_SEQUENCE_ID
2295 , ACD_TYPE
2296 , REVISED_ITEM_SEQUENCE_ID
2297 , SYSDATE -- Cancellation Date
2298 , p_cancel_comments -- Cancel Comments
2299 , ORIGINAL_SYSTEM_REFERENCE
2300 FROM BOM_OPERATION_SEQUENCES
2301 WHERE operation_sequence_id = p_operation_sequence_id ;
2302
2303
2304 --
2305 -- Delete Cancel Revisd Operation from operation table
2306 --
2307 DELETE FROM BOM_OPERATION_SEQUENCES
2308 WHERE operation_sequence_id = p_operation_sequence_id ;
2309
2310 --
2311 -- Delete child Operation Resources
2312 --
2313 DELETE FROM BOM_OPERATION_RESOURCES
2314 WHERE operation_sequence_id = p_operation_sequence_id ;
2315
2316
2317 IF SQL%FOUND THEN
2318
2319 --
2320 -- Log a warning indicating operation resources and
2321 -- substitute operation resources also get deleted.
2322 --
2323 -- l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
2324 -- l_Token_Tbl(1).token_value := p_op_seq_num ;
2325
2326 Error_Handler.Add_Error_Token
2327 ( p_Message_Name => 'BOM_OP_CANCEL_DEL_CHILDREN'
2328 , p_Message_Text => NULL
2329 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2330 , p_Token_Tbl => l_Token_Tbl
2331 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2332 , p_message_type => 'W'
2333 ) ;
2334
2335 END IF ;
2336
2337
2338 --
2339 -- Delete child Sub Operation Resources
2340 --
2341 DELETE FROM BOM_SUB_OPERATION_RESOURCES
2342 WHERE operation_sequence_id = p_operation_sequence_id ;
2343
2344 -- Return Token
2345 x_mesg_token_tbl := l_mesg_token_tbl ;
2346
2347
2348 EXCEPTION
2349 WHEN OTHERS THEN
2350 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2351 ('Unexpected Error occured in Cancel . . .' || SQLERRM);
2352 END IF;
2353
2354 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2355 THEN
2356 l_err_text := G_PKG_NAME || ' : Utility (Operation Cancel) ' ||
2357 SUBSTR(SQLERRM, 1, 200);
2358 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2359
2360 Error_Handler.Add_Error_Token
2361 ( p_message_name => NULL
2362 , p_message_text => l_err_text
2363 , p_mesg_token_tbl => l_mesg_token_tbl
2364 , x_mesg_token_tbl => l_mesg_token_tbl
2365 ) ;
2366 END IF ;
2367
2368 -- Return the status and message table.
2369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2370 x_mesg_token_tbl := l_mesg_token_tbl ;
2371 */
2372 END Cancel_Operation ;
2373
2374 END BOM_Op_Seq_UTIL ;