[Home] [Help]
PACKAGE BODY: APPS.ENG_GLOBALS
Source
1 PACKAGE BODY ENG_Globals AS
2 /* $Header: ENGSGLBB.pls 115.34 2004/06/11 12:41:43 lkasturi ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Globals';
7
8 -- Global variable holding ECO workflow approval process name
9
10 G_PROCESS_NAME VARCHAR2(30) := NULL;
11 G_System_Information System_Information_Rec_Type;
12
13
14 -- Initialize system information record
15
16 PROCEDURE Init_System_Info_Rec
17 ( x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
18 , x_return_status OUT NOCOPY VARCHAR2
19 )
20 IS
21 BEGIN
22 Eng_Globals.Set_user_id( p_user_id => FND_GLOBAL.user_id);
23 Eng_Globals.Set_login_id( p_login_id => FND_GLOBAL.login_id);
24 Eng_Globals.Set_prog_id( p_prog_id => FND_GLOBAL.conc_program_id);
25 Eng_Globals.Set_prog_appid( p_prog_appid => FND_GLOBAL.prog_appl_id);
26 Eng_Globals.Set_request_id( p_request_id => FND_GLOBAL.conc_request_id);
27 END Init_System_Info_Rec;
28
29
30 -- Check transaction_type validity
31
32 PROCEDURE Transaction_Type_Validity
33 ( p_transaction_type IN VARCHAR2
34 , p_entity IN VARCHAR2
35 , p_entity_id IN VARCHAR2
36 , x_valid OUT NOCOPY BOOLEAN
37 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
38 )
39 IS
40 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
41 l_Token_Tbl Error_Handler.Token_Tbl_Type;
42 BEGIN
43 l_token_tbl(1).token_name := 'entity_id';
44 l_token_tbl(1).token_value := p_entity_id;
45
46 x_valid := TRUE;
47
48
49 IF ((p_entity IN ('ECO_Header', 'ECO_Rev', 'Rev_Items', 'Ref_Desgs',
50 'Sub_Comps','Sub_Res', 'Op_Res',
51 'Change_Lines', 'People') AND
52 NVL(p_transaction_type, FND_API.G_MISS_CHAR)
53 NOT IN ('CREATE', 'UPDATE', 'DELETE')))
54 OR
55 ((p_entity IN ('Rev_Comps','Op_Seq') AND -- L1
56 NVL(p_transaction_type, FND_API.G_MISS_CHAR)
57 NOT IN ('CREATE', 'UPDATE', 'DELETE', 'CANCEL')))
58 THEN
59 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
60 THEN
61 IF p_entity = 'ECO_Header'
62 THEN
63 Error_Handler.Add_Error_Token
64 ( p_Message_Name => 'ENG_ECO_TRANS_TYPE_INVALID'
65 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
66 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
67 );
68 ELSIF p_entity = 'ECO_Rev'
69 THEN
70 Error_Handler.Add_Error_Token
71 ( p_Message_Name => 'ENG_REV_TRANS_TYPE_INVALID'
72 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
73 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
74 );
75 ELSIF p_entity = 'Rev_Items'
76 THEN
77 Error_Handler.Add_Error_Token
78 ( p_Message_Name => 'ENG_RIT_TRANS_TYPE_INVALID'
79 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
80 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
81 );
82 ELSIF p_entity = 'Rev_Comps'
83 THEN
84 Error_Handler.Add_Error_Token
85 ( p_Message_Name => 'BOM_CMP_TRANS_TYPE_INVALID'
86 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
87 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
88 );
89 ELSIF p_entity = 'Ref_Desgs'
90 THEN
91 Error_Handler.Add_Error_Token
92 ( p_Message_Name => 'BOM_RFD_TRANS_TYPE_INVALID'
93 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
94 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
95 );
96 ELSIF p_entity = 'Sub_Comps'
97 THEN
98 Error_Handler.Add_Error_Token
99 ( p_Message_Name => 'BOM_SBC_TRANS_TYPE_INVALID'
100 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
101 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
102 );
103 ELSIF p_entity = 'Op_Seq' --L1
104 THEN --L1
105 Error_Handler.Add_Error_Token --L1
106 ( p_Message_Name => 'BOM_OP_TRANS_TYPE_INVALID' --L1
107 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl --L1
108 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl --L1
109 ); --L1
110 ELSIF p_entity = 'Op_Res' --L1
111 THEN --L1
112 Error_Handler.Add_Error_Token --L1
113 ( p_Message_Name => 'BOM_RES_TRANS_TYPE_INVALID'--L1
114 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl --L1
115 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl --L1
116 ); --L1
117 ELSIF p_entity = 'Sub_Res' --L1
118 THEN --L1
119 Error_Handler.Add_Error_Token --L1
120 ( p_Message_Name => 'BOM_SRC_TRANS_TYPE_INVALID'--L1
121 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl --L1
122 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl --L1
123 ); --L1
124 ELSIF p_entity = 'Change_Lines' -- Eng Change
125 THEN
126 Error_Handler.Add_Error_Token
127 ( p_Message_Name => 'ENG_CL_TRANS_TYPE_INVALID'
128 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
129 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
130 );
131 ELSIF p_entity = 'People' -- Eng Change
132 THEN
133 Error_Handler.Add_Error_Token
134 ( p_Message_Name => 'ENG_PEOPLE_TRANS_TYPE_INVALID'
135 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
136 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
137 );
138 END IF;
139
140
141
142 END IF;
143
144 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
145 x_valid := FALSE;
146 END IF;
147
148 END Transaction_Type_Validity;
149
150 -- Initialize control record.
151
152 FUNCTION Init_Control_Rec
153 ( p_operation IN VARCHAR2
154 , p_control_rec IN Control_Rec_Type
155 , x_err_text OUT NOCOPY VARCHAR2
156 )RETURN Control_Rec_Type
157 IS
158 l_control_rec Control_Rec_Type;
159 BEGIN
160
161 IF p_control_rec.controlled_operation THEN
162
163 RETURN p_control_rec;
164
165 ELSIF p_operation = G_OPR_NONE THEN
166
167 l_control_rec.default_attributes:= FALSE;
168 l_control_rec.change_attributes := FALSE;
169 l_control_rec.validate_entity := FALSE;
170 l_control_rec.write_to_DB := FALSE;
171 l_control_rec.process := p_control_rec.process;
172 -- l_control_rec.process_entity := p_control_rec.process_entity;
173 l_control_rec.request_category := p_control_rec.request_category;
174 l_control_rec.request_name := p_control_rec.request_name;
175 l_control_rec.clear_api_cache := p_control_rec.clear_api_cache;
176 l_control_rec.clear_api_requests:= p_control_rec.clear_api_requests;
177 -- if the transaction type is null or missing then Process Entity is set to XXXX
178 l_control_rec.process_entity := 'XXXX';
179 ELSIF p_operation = G_OPR_CREATE THEN
180
181 l_control_rec.default_attributes:= TRUE;
182 l_control_rec.change_attributes := FALSE;
183 l_control_rec.validate_entity := TRUE;
184 l_control_rec.write_to_DB := TRUE;
185 l_control_rec.process := TRUE;
186 l_control_rec.process_entity := G_ENTITY_ALL;
187 l_control_rec.clear_api_cache := TRUE;
188 l_control_rec.clear_api_requests:= TRUE;
189
190 ELSIF p_operation = G_OPR_UPDATE THEN
191
192 l_control_rec.default_attributes:= FALSE;
193 l_control_rec.change_attributes := TRUE;
194 l_control_rec.validate_entity := TRUE;
195 l_control_rec.write_to_DB := TRUE;
196 l_control_rec.process := TRUE;
197 l_control_rec.process_entity := G_ENTITY_ALL;
198 l_control_rec.clear_api_cache := TRUE;
199 l_control_rec.clear_api_requests:= TRUE;
200
201 ELSIF p_operation = G_OPR_DELETE THEN
202
203 l_control_rec.default_attributes:= FALSE;
204 l_control_rec.change_attributes := FALSE;
205 l_control_rec.validate_entity := TRUE;
206 l_control_rec.write_to_DB := TRUE;
207 l_control_rec.process := TRUE;
208 l_control_rec.process_entity := G_ENTITY_ALL;
209 l_control_rec.clear_api_cache := TRUE;
210 l_control_rec.clear_api_requests:= TRUE;
211
212 ELSIF p_operation = G_OPR_CANCEL THEN
213
214 l_control_rec.default_attributes:= FALSE;
215 l_control_rec.change_attributes := FALSE;
216 l_control_rec.validate_entity := TRUE;
217 l_control_rec.write_to_DB := TRUE;
218 l_control_rec.process := TRUE;
219 l_control_rec.process_entity := G_ENTITY_REV_COMPONENT;
220 l_control_rec.clear_api_cache := TRUE;
221 l_control_rec.clear_api_requests:= TRUE;
222
223 ELSE
224
225 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
226 THEN
227 x_err_text := G_PKG_NAME || '(Init_Control_Rec) - Invalid Operation' || substrb(SQLERRM,1,60);
228 l_control_rec.process_entity := 'XXXX';
229 END IF;
230 END IF;
231
232 RETURN l_control_rec;
233
234 END Init_Control_Rec;
235
236 PROCEDURE Init_Process_Name
237 ( p_change_order_type_id IN NUMBER
238 , p_priority_code IN VARCHAR2
239 , p_organization_id IN NUMBER
240 )
241 IS
242 l_process_name VARCHAR2(30) := NULL;
243 BEGIN
244
245 IF p_change_order_type_id IS NULL THEN
246 G_PROCESS_NAME := NULL;
247 END IF;
248
249 SELECT process_name
250 INTO l_process_name
251 FROM eng_change_type_processes
252 WHERE change_order_type_id = p_change_order_type_id
253 AND organization_id = p_organization_id -- 2230130
254 AND (( p_priority_code is NOT NULL
255 AND eng_change_priority_code = p_priority_code)
256 OR
257 (p_priority_code is NULL
258 AND eng_change_priority_code is NULL));
259
260 G_PROCESS_NAME := l_process_name;
261 G_system_information.wkfl_process := TRUE;
262
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265
266 G_PROCESS_NAME := NULL;
267
268 END Init_Process_Name;
269
270 FUNCTION Get_Process_Name
271 RETURN VARCHAR2
272 IS
273 BEGIN
274
275 RETURN G_PROCESS_NAME;
276 END Get_Process_Name;
277
278
279 PROCEDURE Add_Request
280 ( p_entity IN VARCHAR2
281 , p_step IN VARCHAR2 := NULL
282 , p_name IN VARCHAR2
283 , p_category IN VARCHAR2 := NULL
284 , p_processed IN BOOLEAN := FALSE
285 , p_attribute1 IN VARCHAR2 := NULL
286 , p_attribute2 IN VARCHAR2 := NULL
287 , p_attribute3 IN VARCHAR2 := NULL
288 , p_attribute4 IN VARCHAR2 := NULL
289 , p_attribute5 IN VARCHAR2 := NULL
290 )
291 IS
292 l_next_index INTEGER := 0;
293 BEGIN
294 l_next_index := NVL(G_REQUEST_TBL.LAST,0) + 1;
295
296 G_REQUEST_TBL(l_next_index).entity := p_entity;
297 G_REQUEST_TBL(l_next_index).step := p_step;
298 G_REQUEST_TBL(l_next_index).name := p_name;
299 G_REQUEST_TBL(l_next_index).category := p_category;
300 G_REQUEST_TBL(l_next_index).processed := p_processed;
301 G_REQUEST_TBL(l_next_index).attribute1 := p_attribute1;
302 G_REQUEST_TBL(l_next_index).attribute2 := p_attribute2;
303 G_REQUEST_TBL(l_next_index).attribute3 := p_attribute3;
304 G_REQUEST_TBL(l_next_index).attribute4 := p_attribute4;
305 G_REQUEST_TBL(l_next_index).attribute5 := p_attribute5;
306
307 END Add_Request;
308
309 FUNCTION Get_Request_Status
310 ( p_entity IN VARCHAR2
311 , p_step IN VARCHAR2 := NULL
312 , p_name IN VARCHAR2
313 , p_category IN VARCHAR2 := NULL
314 , p_attribute1 IN VARCHAR2 := NULL
315 , p_attribute2 IN VARCHAR2 := NULL
316 , p_attribute3 IN VARCHAR2 := NULL
317 , p_attribute4 IN VARCHAR2 := NULL
318 , p_attribute5 IN VARCHAR2 := NULL
319 )RETURN BOOLEAN
320 IS
321 l_processed BOOLEAN := TRUE;
322 l_index1 NUMBER;
323 BEGIN
324 l_index1 := G_REQUEST_TBL.COUNT;
325
326 FOR l_index IN 1 .. l_index1
327 LOOP
328 IF G_REQUEST_TBL(l_index).entity = p_entity AND
329 NVL(G_REQUEST_TBL(l_index).step,'NONE') = NVL(p_step,'NONE') AND
330 G_REQUEST_TBL(l_index).name = p_name AND
331 NVL(G_REQUEST_TBL(l_index).category,'NONE') = NVL(p_category,'NONE') AND
332 NVL(G_REQUEST_TBL(l_index).attribute1,'NONE') = NVL(p_attribute1,'NONE') AND
333 NVL(G_REQUEST_TBL(l_index).attribute2,'NONE') = NVL(p_attribute2,'NONE') AND
334 NVL(G_REQUEST_TBL(l_index).attribute3,'NONE') = NVL(p_attribute3,'NONE') AND
335 NVL(G_REQUEST_TBL(l_index).attribute4,'NONE') = NVL(p_attribute4,'NONE') AND
336 NVL(G_REQUEST_TBL(l_index).attribute5,'NONE') = NVL(p_attribute5,'NONE')
337 THEN
338 l_processed := G_REQUEST_TBL(l_index).processed;
339 END IF;
340 END LOOP;
341
342 RETURN l_processed;
343
344 END Get_Request_Status;
345
346 -- Checks if there is an unprocessed request that matches the parameters
347
348 FUNCTION Get_Unprocessed_Request
349 ( p_entity IN VARCHAR2
350 , p_step IN VARCHAR2 := NULL
351 , p_name IN VARCHAR2
352 , p_category IN VARCHAR2 := NULL
353 , p_attribute1 IN VARCHAR2 := NULL
354 , p_attribute2 IN VARCHAR2 := NULL
355 , p_attribute3 IN VARCHAR2 := NULL
356 , p_attribute4 IN VARCHAR2 := NULL
357 , p_attribute5 IN VARCHAR2 := NULL
358 )RETURN BOOLEAN
359 IS
360 l_found BOOLEAN := FALSE;
361 l_index1 NUMBER;
362 BEGIN
363 l_index1 := G_REQUEST_TBL.COUNT;
364
365 FOR l_index IN 1 .. l_index1
366 LOOP
367 IF G_REQUEST_TBL(l_index).entity = p_entity AND
368 NVL(G_REQUEST_TBL(l_index).step,'NONE') = NVL(p_step,'NONE') AND
369 G_REQUEST_TBL(l_index).name = p_name AND
370 NVL(G_REQUEST_TBL(l_index).category,'NONE') = NVL(p_category,'NONE') AND
371 NVL(G_REQUEST_TBL(l_index).attribute1,'NONE') = NVL(p_attribute1,'NONE') AND
372 NVL(G_REQUEST_TBL(l_index).attribute2,'NONE') = NVL(p_attribute2,'NONE') AND
373 NVL(G_REQUEST_TBL(l_index).attribute3,'NONE') = NVL(p_attribute3,'NONE') AND
374 NVL(G_REQUEST_TBL(l_index).attribute4,'NONE') = NVL(p_attribute4,'NONE') AND
375 NVL(G_REQUEST_TBL(l_index).attribute5,'NONE') = NVL(p_attribute5,'NONE') AND
376 NOT G_REQUEST_TBL(l_index).processed
377 THEN
378 l_found := TRUE;
379 END IF;
380 END LOOP;
381
382 RETURN l_found;
383
384 END Get_Unprocessed_Request;
385
386 PROCEDURE Get_Unprocessed_Request_Keys
387 ( p_entity IN VARCHAR2
388 , p_step IN VARCHAR2 := NULL
389 , p_name IN VARCHAR2
390 , p_category IN VARCHAR2 := NULL
391 , x_attribute OUT NOCOPY Request_Key_Tbl_Type
392 , x_max_count OUT NOCOPY NUMBER
393 )
394 IS
395 l_index1 NUMBER;
396 l_index2 NUMBER;
397 BEGIN
398 l_index1 := G_REQUEST_TBL.COUNT;
399 l_index2 := 0;
400
401 FOR l_index IN 1 .. l_index1
402 LOOP
403 IF G_REQUEST_TBL(l_index).entity = p_entity AND
404 NVL(G_REQUEST_TBL(l_index).step,'NONE') = NVL(p_step,'NONE') AND
405 G_REQUEST_TBL(l_index).name = p_name AND
406 NVL(G_REQUEST_TBL(l_index).category,'NONE') = NVL(p_category,'NONE') AND
407 G_REQUEST_TBL(l_index).processed = FALSE
408 THEN
409 l_index2 := l_index2 + 1;
410 x_attribute(l_index2).attribute1 := G_REQUEST_TBL(l_index).attribute1;
411 x_attribute(l_index2).attribute2 := G_REQUEST_TBL(l_index).attribute2;
412 x_attribute(l_index2).attribute3 := G_REQUEST_TBL(l_index).attribute3;
413 x_attribute(l_index2).attribute4 := G_REQUEST_TBL(l_index).attribute4;
414 x_attribute(l_index2).attribute5 := G_REQUEST_TBL(l_index).attribute5;
415 END IF;
416 END LOOP;
417
418 x_max_count := l_index2;
419
420 END Get_Unprocessed_Request_Keys;
421
422 PROCEDURE Clear_Request_Table
423 IS
424 BEGIN
425
426 G_REQUEST_TBL.DELETE;
427
428 END Clear_Request_Table;
429
430 PROCEDURE Init_WHO_Rec
431 ( p_org_id IN NUMBER
432 , p_user_id IN NUMBER
433 , p_login_id IN NUMBER
434 , p_prog_appid IN NUMBER
435 , p_prog_id IN NUMBER
436 , p_req_id IN NUMBER
437 )
438 IS
439 BEGIN
440 G_WHO_REC.org_id := p_org_id;
441 G_WHO_REC.user_id := p_user_id;
442 G_WHO_REC.login_id := p_login_id;
443 G_WHO_REC.prog_appid := p_prog_appid;
444 G_WHO_REC.prog_id:= p_prog_id;
445 G_WHO_REC.req_id := p_req_id;
446 END Init_WHO_Rec;
447
448 PROCEDURE Init_WHO_Rec_Entity_Details
449 ( p_entity IN VARCHAR2
450 , p_transaction_id IN NUMBER
451 )
452 IS
453 BEGIN
454 G_WHO_REC.entity := p_entity;
455 G_WHO_REC.transaction_id := p_transaction_id;
456 END Init_WHO_Rec_Entity_Details;
457
458 -- If an approved ECO has a process and any part of the ECO is being modified,
459 -- set ECO Approval Status to 'Not Submitted for Approval' and
460 -- set Status Type of any scheduled revised items to 'Open'.
461 -- Also issue warning.
462
463 PROCEDURE Check_Approved_For_Process
464 ( p_change_notice IN VARCHAR2
465 , p_organization_id IN NUMBER
466 , x_processed OUT NOCOPY BOOLEAN
467 , x_err_text OUT NOCOPY VARCHAR2
468 )
469 IS
470 l_process_name VARCHAR2(30) := NULL;
471 l_approval_status_type NUMBER;
472 BEGIN
473
474 x_processed := FALSE;
475
476 -- Get Workflow Process name
477
478 l_process_name := ENG_Globals.Get_Process_Name;
479
480 SELECT approval_status_type
481 INTO l_approval_status_type
482 FROM eng_engineering_changes
483 WHERE change_notice = p_change_notice
484 AND organization_id = p_organization_id;
485
486 -- ECO w/ Process is Approved
487
488 IF l_approval_status_type = 5 AND
489 l_process_name is NOT NULL
490 THEN
491 x_processed := TRUE;
492 END IF;
493
494 EXCEPTION
495 WHEN NO_DATA_FOUND THEN
496 x_processed := FALSE;
497
498 WHEN OTHERS THEN
499 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
500 THEN
501 x_err_text := G_PKG_NAME || '(Check_Approved_For_Process) - ECO Header' || substrb(SQLERRM,1,60);
502 END IF;
503
504 RAISE FND_API.G_EXC_ERROR;
505
506 END Check_Approved_For_Process;
507
508 -- Sets ECO to 'Not Submitted For Approval' and any
509 -- "Scheduled" revised items to "Open"
510
511 PROCEDURE Set_Request_For_Approval
512 ( p_change_notice IN VARCHAR2
513 , p_organization_id IN NUMBER
514 , x_err_text OUT NOCOPY VARCHAR2
515 )
516 IS
517 BEGIN
518 -- Set ECO to 'Not Submitted For Approval'
519
520 UPDATE eng_engineering_changes
521 SET approval_status_type = 1,
522 approval_request_date = null,
523 approval_date = null,
524 last_update_date = SYSDATE,
525 last_updated_by = FND_GLOBAL.USER_ID,
526 last_update_login = FND_GLOBAL.LOGIN_ID
527 WHERE organization_id = p_organization_id
528 AND change_notice = p_change_notice;
529
530 -- Set all "Scheduled" revised items to "Open"
531
532 UPDATE eng_revised_items
533 SET status_type = 1,
534 last_update_date = SYSDATE,
535 last_updated_by = FND_GLOBAL.USER_ID,
536 last_update_login = FND_GLOBAL.LOGIN_ID
537 WHERE organization_id = p_organization_id
538 AND change_notice = p_change_notice
539 AND status_type = 4;
540
541 -- Issue warning
542
543 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
544 THEN
545 NULL;
546 /* Commented out since a message should not be logged
547 from here anymore
548
549 Eng_Eco_Pub.Log_Error ( p_who_rec => ENG_GLOBALS.G_WHO_REC
550 , p_msg_name => 'ENG_APPROVE_WARNING'
551 , x_err_text => x_err_text );
552 */
553 END IF;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
558 THEN
559 x_err_text := G_PKG_NAME || '(Set_Request_For_Approval) -
560 ECO Header and Revised Items' || substrb(SQLERRM,1,60);
561 END IF;
562
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
564
565 END Set_Request_For_Approval;
566
567 -- Function Get_ECO_Assembly_Type
568 -- Returns ECO assembly type
569
570 FUNCTION Get_ECO_Assembly_Type
571 ( p_change_notice IN VARCHAR2
572 , p_organization_id IN NUMBER
573 ) RETURN NUMBER
574 IS
575 l_assembly_type NUMBER := NULL;
576 l_change_order_type_id NUMBER := NULL;
577 BEGIN
578 select assembly_type
579 into l_assembly_type
580 from eng_change_order_types
581 where change_order_type_id =
582 (select change_order_type_id
583 from eng_engineering_changes
584 where change_notice = p_change_notice
585 and organization_id = p_organization_id);
586
587 RETURN l_assembly_type;
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 RETURN 0;
592 END Get_ECO_Assembly_Type;
593
594 -- Function ECO_Cannot_Update
595 -- Checks if the ECO should not be updated
596
597 FUNCTION ECO_Cannot_Update
598 ( p_change_notice IN VARCHAR2
599 , p_organization_id IN NUMBER
600 )RETURN BOOLEAN
601 IS
602 l_ret BOOLEAN := FALSE;
603 l_process_name VARCHAR2(30) := NULL;
604 l_status_type NUMBER := NULL;
605
606 cursor get_pri_chgtype_stat is select priority_code, change_order_type_id, status_type
607 from ENG_ENGINEERING_CHANGES
608 where change_notice = p_change_notice
609 and organization_id = p_organization_id;
610
611 cursor check_ECO is select 1
612 from ENG_ENGINEERING_CHANGES
613 where change_notice = p_change_notice
614 and organization_id = p_organization_id
615 and approval_status_type = 3;
616 BEGIN
617 for l_open IN check_ECO loop
618 l_ret := TRUE;
619 end loop;
620
621 for l_details in get_pri_chgtype_stat loop
622 ENG_Globals.Init_Process_Name ( p_change_order_type_id => l_details.change_order_type_id
623 , p_priority_code => l_details.priority_code
624 , p_organization_id => p_organization_id
625 ) ;
626 l_process_name := ENG_Globals.Get_Process_Name;
627 l_status_type := l_details.status_type;
628 end loop;
629
630 if (l_ret and l_process_name is not null) or l_status_type in (5,6)
631 then
632 RETURN TRUE;
633 else
634 RETURN FALSE;
635 end if;
636 END ECO_Cannot_Update;
637
638 -- Function Get_PLM_Or_ERP_Change
639 -- Checks if the ECO is 'PLM' or 'ERP'
640 -- Added for 3618676
641
642 FUNCTION Get_PLM_Or_ERP_Change
643 ( p_change_notice IN VARCHAR2
644 , p_organization_id IN NUMBER
645 ) RETURN VARCHAR2
646 IS
647 CURSOR c_plm_or_erp IS
648 SELECT nvl(plm_or_erp_change, 'PLM') plm_or_erp
649 FROM eng_engineering_changes
650 WHERE change_notice = p_change_notice
651 AND organization_id = p_organization_id;
652
653 l_plm_or_erp_change VARCHAR2(3);
654
655 BEGIN
656 l_plm_or_erp_change := 'ERP';
657 FOR cp IN c_plm_or_erp
658 LOOP
659 l_plm_or_erp_change := cp.plm_or_erp;
660 END LOOP;
661 RETURN l_plm_or_erp_change;
662 EXCEPTION
663 WHEN OTHERS THEN
664 RETURN l_plm_or_erp_change;
665 END Get_PLM_Or_ERP_Change;
666
667 -- Function Validate_Change_Order_Access
668 -- The user must have access to the change order assembly type
669
670 /* Commented out since this procedure will require changes to
671 accomodate new design
672 */
673
674 /*
675 PROCEDURE Validate_Change_Order_Access
676 ( p_change_order_type_id IN NUMBER
677 , p_change_notice IN VARCHAR2
678 , p_organization_id IN NUMBER
679 , x_change_order_access OUT NOCOPY BOOLEAN
680 , x_err_text OUT NOCOPY VARCHAR2
681 )
682 IS
683 l_assembly_type NUMBER := 0;
684 l_profile_value NUMBER;
685 l_err_text VARCHAR2(2000) := NULL;
686 BEGIN
687
688 select assembly_type
689 into l_assembly_type
690 from eng_change_order_types
691 where change_order_type_id =
692 p_change_order_type_id;
693
694 IF fnd_profile.defined('ENG:ENG_ITEM_ECN_ACCESS')
695 THEN
696 l_profile_value := fnd_profile.value('ENG:ENG_ITEM_ECN_ACCESS');
697 END IF;
698
699 IF ((l_assembly_type = 2 AND l_profile_value = 1) OR (l_assembly_type = 1))
700 THEN
701 x_change_order_access := TRUE;
702 ELSE
703 x_change_order_access := FALSE;
704 END IF;
705
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
709 THEN
710 NULL;
711 Eng_Eco_Pub.Log_Error ( p_who_rec => ENG_GLOBALS.G_WHO_REC
712 , p_msg_name => 'ENG_CHGORD_TYPE_DELETED'
713 , x_err_text => x_err_text );
714
715 END IF;
716
717 RAISE FND_API.G_EXC_ERROR;
718
719 WHEN OTHER THEN
720 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
721 THEN
722 l_err_text := G_PKG_NAME || ' : (Validate_Change_Order_Access) -
723 Change_Notice ' || substrb(SQLERRM,1,200);
724 Eng_Eco_Pub.Log_Error ( p_who_rec => ENG_GLOBALS.G_WHO_REC
725 , p_msg_name => NULL
726 , p_err_text => l_err_text
727 , x_err_text => x_err_text );
728 END IF;
729
730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
731
732 END Validate_Change_Order_Access;
733 */
734
735 /****************************************************************************
736 * The following procedures and functions are the get and set routines for the
737 * system_information_record.
738 * Numeric attributes of the record have Get functions with a naming convention
739 * of Get_<Attribute_Name> ex. Get_Bill_Sequence_Id
740 * For attributes of type Boolean the convention is IS_<Boolean_Attribute_Name>
741 * Ex. Is_Eco_Impl will return value of the boolean attribute Eco_Impl.
742 * Similarly the set procedures will have the convention of Set_<Attribute_Name>
743 * with the respective attribute Type variable as an input.
744 * There are also two routines which get and set the entire record as a whole.
745 * Added 06/21/99 by RC.
746 *****************************************************************************/
747
748 /**************************************************************************
749 * Function : Get_System_Information
750 * Returns : System_Information Record
751 * Parameters IN : None
752 * Parameters OUT: None
753 * Purpose : This procedure will return the value of the system information
754 * record.
755 ****************************************************************************/
756 FUNCTION Get_System_Information RETURN Eng_Globals.System_Information_Rec_Type
757 IS
758 BEGIN
759 RETURN G_System_Information;
760
761 END Get_System_Information;
762
763
764 /***************************************************************************
765 * Procedure : Set_System_Information
766 * Returns : None
767 * Parameters IN : System_Information_Record
768 * Parameters OUT: None
769 * Purpose : This procedure will set the value of the system information
770 * record.
771 ****************************************************************************/
772 PROCEDURE Set_System_Information
773 ( p_system_information_rec IN
774 Eng_Globals.System_Information_Rec_Type)
775 IS
776 BEGIN
777 G_System_Information := p_system_information_rec;
778
779 END Set_System_Information;
780
781 /*****************************************************************************
782 * Procedure : Set_Bill_Sequence_id
783 * Returns : None
784 * Parameters IN : Bill_Sequence_Id
785 * Parameters OUT: None
786 * Purpose : This procedure will set the bill_sequence_id value in the
787 * system_information record.
788 *
789 *****************************************************************************/
790 PROCEDURE Set_Bill_Sequence_id
791 ( p_bill_sequence_id IN NUMBER)
792 IS
793 BEGIN
794 G_System_Information.bill_sequence_id := p_bill_sequence_id;
795 END;
796
797 /***************************************************************************
798 * Function : Get_Bill_Sequence_id
799 * Returns : Number
800 * Parameters IN : None
801 * Parameters OUT: None
802 * Purpose : This function will return the bill_sequence_id value in the
803 * system_information record.
804 ******************************************************************************/
805 FUNCTION Get_Bill_Sequence_id RETURN NUMBER
806 IS
807 BEGIN
808 RETURN G_System_Information.bill_sequence_id;
809
810 END Get_Bill_Sequence_id;
811
812 /*****************************************************************************
813 * Procedure : Set_Entity
814 * Returns : None
815 * Parameters IN : Entity Name
816 * Parameter OUT : None
817 * Purpose : Will set the entity name in the System Information Record.
818 *
819 ******************************************************************************/
820 PROCEDURE Set_Entity
821 ( p_entity IN VARCHAR2)
822 IS
823 BEGIN
824 G_System_information.entity := p_entity;
825 END Set_Entity;
826
827 /****************************************************************************
828 * Function : Get_Entity
829 * Returns : VARCHAR2
830 * Parameters IN : None
831 * Parameter OUT : None
832 * Purpose : Will return the entity name in the System Information Record.
833 *
834 *****************************************************************************/
835 FUNCTION Get_Entity RETURN VARCHAR2
836 IS
837 BEGIN
838 RETURN G_System_Information.entity;
839 END Get_Entity;
840
841 /****************************************************************************
842 * Procedure : Set_Org_id
843 * Returns : None
844 * Parameters IN : Organization_Id
845 * Parameters OUT: None
846 * Purpose : Will set the org_id attribute of the sytem_information_record
847 *
848 *****************************************************************************/
849 PROCEDURE Set_Org_id
850 ( p_org_id IN NUMBER)
851 IS
852 BEGIN
853 G_System_Information.org_id := p_org_id;
854
855 END Set_Org_Id;
856
857 /***************************************************************************
858 * Function : Get_Org_id
859 * Returns : Number
860 * Parameters IN : None
861 * Parameters OUT: None
862 * Purpose : Will return the org_id attribute of the
863 * sytem_information_record
864 *****************************************************************************/
865 FUNCTION Get_Org_id RETURN NUMBER
866 IS
867 BEGIN
868 RETURN G_System_Information.org_id;
869
870 END Get_Org_Id;
871
872 /****************************************************************************
873 * Procedure : Set_Eco_Name
874 * Returns : None
875 * Parameters IN : Eco_Name
876 * Parameters OUT: None
877 * Purpose : Will set the Eco_Name attribute of the
878 * system_information record
879 ******************************************************************************/
880 PROCEDURE Set_Eco_Name
881 ( p_eco_name IN VARCHAR2)
882 IS
883 BEGIN
884 G_System_Information.eco_name := p_eco_name;
885
886 END Set_Eco_Name;
887
888 /****************************************************************************
889 * Function : Get_Eco_Name
890 * Returns : VARCHAR2
891 * Parameters IN : None
892 * Parameters OUT: None
893 * Purpose : Will return the Eco_Name attribute of the
894 * system_information record
895 *****************************************************************************/
896 FUNCTION Get_Eco_Name RETURN VARCHAR2
897 IS
898 BEGIN
899 RETURN G_System_Information.eco_name;
900
901 END Get_Eco_Name;
902
903 /*****************************************************************************
904 * Procedure : Set_User_Id
905 * Returns : None
906 * Parameters IN : User ID
907 * Parameters OUT: None
908 * Purpose : Will set the user ID attribute of the
909 * system_information_record
910 *****************************************************************************/
911 PROCEDURE Set_User_Id
912 ( p_user_id IN NUMBER)
913 IS
914 BEGIN
915 -- Now sharing System Information with BOM
916 --G_System_Information.user_id := p_user_id;
917
918 Bom_Globals.Set_User_Id(p_user_id);
919
920 -- Also sharing System Information with RTG
921 -- added by MK on 11/15/00
922 Bom_Rtg_Globals.Set_User_Id(p_user_id);
923
924 END Set_User_Id;
925
926 /***************************************************************************
927 * Function : Get_User_Id
928 * Returns : Number
929 * Parameters IN : None
930 * Parameters OUT: None
931 * Purpose : Will return the user_id attribute from the
932 * system_information_record
933 *****************************************************************************/
934 FUNCTION Get_User_ID RETURN NUMBER
935 IS
936 BEGIN
937 -- Now sharing system information with BOM
938 -- RETURN G_System_Information.user_id;
939
940 RETURN Bom_Globals.Get_User_Id;
941
942
943 END Get_User_id;
944
945
946 /****************************************************************************
947 * Procedure : Set_Login_Id
948 * Returns : None
949 * Paramaters IN : p_login_id
950 * Parameters OUT: None
951 * Purpose : Will set the login ID attribute of the system information
952 * record.
953 *****************************************************************************/
954 PROCEDURE Set_Login_Id
955 ( p_login_id IN NUMBER )
956 IS
957 BEGIN
958 -- Now sharing system information with BOM
959 -- G_System_Information.login_id := p_login_id;
960
961 Bom_Globals.Set_Login_Id(p_login_id);
962
963 -- Also sharing System Information with RTG
964 -- added by MK on 11/15/00
965 Bom_Rtg_Globals.Set_Login_Id(p_login_id) ;
966
967 END Set_Login_Id;
968
969 /****************************************************************************
970 * Function : Get_Login_Id
971 * Returns : Number
972 * Paramaters IN : None
973 * Parameters OUT: None
974 * Purpose : Will retun the login ID attribute of the system information
975 * record.
976 *****************************************************************************/
977 FUNCTION Get_Login_Id RETURN NUMBER
978 IS
979 BEGIN
980 -- Now sharing system information with BOM
981 -- RETURN G_System_Information.Login_Id;
982
983 RETURN Bom_Globals.Get_Login_Id;
984 END;
985
986 /***************************************************************************
987 * Procedure : Set_Prog_AppId
988 * Returns : None
989 * Parameters IN : p_prog_appid
990 * Parameters OUT: None
991 * Purpose : Will set the Program Application Id attribute of the
992 * System Information Record.
993 *****************************************************************************/
994 PROCEDURE Set_Prog_AppId
995 ( p_prog_Appid IN NUMBER )
996 IS
997 BEGIN
998 -- Now sharing system information with BOM
999 -- G_System_Information.prog_appid := p_prog_appid;
1000
1001 Bom_Globals.Set_Prog_AppId(p_prog_appid);
1002
1003 -- Also sharing System Information with RTG
1004 -- added by MK on 11/15/00
1005 Bom_Rtg_Globals.Set_Prog_AppId(p_prog_appid);
1006
1007 END Set_Prog_AppId;
1008
1009 /***************************************************************************
1010 * Function : Get_Prog_AppId
1011 * Returns : Number
1012 * Parameters IN : None
1013 * Parameters OUT: None
1014 * Purpose : Will return the Program Application Id (prog_appid)
1015 * attribute of the system information record.
1016 *****************************************************************************/
1017 FUNCTION Get_Prog_AppId RETURN NUMBER
1018 IS
1019 BEGIN
1020 -- Now sharing system information with BOM
1021 -- RETURN G_System_Information.prog_AppId;
1022
1023 RETURN Bom_Globals.Get_Prog_AppId;
1024
1025 END Get_Prog_AppId;
1026
1027
1028 /***************************************************************************
1029 * Procedure : Set_Prog_Id
1030 * Returns : None
1031 * Parameters IN : p_prog_id
1032 * Parameters OUT: None
1033 * Purpose : Will set the Program Id attribute of the system information
1034 * record.
1035 *****************************************************************************/
1036 PROCEDURE Set_Prog_Id
1037 ( p_prog_id IN NUMBER )
1038 IS
1039 BEGIN
1040 -- Now sharing system information with BOM
1041 -- G_System_Information.prog_id := p_prog_id;
1042
1043 Bom_Globals.Set_Prog_Id(p_prog_id);
1044
1045 -- Also sharing System Information with RTG
1046 -- added by MK on 11/15/00
1047 Bom_Rtg_Globals.Set_Prog_Id(p_prog_id);
1048
1049 END Set_Prog_Id;
1050
1051 /***************************************************************************
1052 * Function : Get_Prog_Id
1053 * Returns : NUMBER
1054 * Parameters IN : None
1055 * Parameters OUT: None
1056 * Purpose : Function will return the Prog_Id attribute of the System
1057 * information record.
1058 *****************************************************************************/
1059 FUNCTION Get_Prog_Id RETURN NUMBER
1060 IS
1061 BEGIN
1062 -- Now sharing system information with BOM
1063 -- RETURN G_System_Information.prog_id;
1064
1065 RETURN Bom_Globals.Get_Prog_Id;
1066
1067 END Get_Prog_Id;
1068
1069 /***************************************************************************
1070 * Procedure : Set_Request_Id
1071 * Returns : None
1072 * Parameters IN : p_request_id
1073 * Parameters OUT: None
1074 * Purpose : Procedure will set the request_id attribute of the
1075 * system information record.
1076 *****************************************************************************/
1077 PROCEDURE Set_Request_Id
1078 ( p_request_id IN NUMBER )
1079 IS
1080 BEGIN
1081 -- Now sharing system information with BOM
1082 -- G_System_Information.request_id := p_request_id;
1083
1084 Bom_Globals.Set_Request_Id(p_request_id);
1085
1086 -- Also sharing System Information with RTG
1087 -- added by MK on 11/15/00
1088 Bom_Rtg_Globals.Set_Request_Id(p_request_id);
1089
1090 END;
1091
1092
1093 /***************************************************************************
1094 * Function : Get_Request_Id
1095 * Returns : NUMBER
1096 * Parameters IN : None
1097 * Parameters OUT: None
1098 * Purpose : Function will return the value of the request_id attribute
1099 * of the system information record.
1100 *****************************************************************************/
1101 FUNCTION Get_Request_id RETURN NUMBER
1102 IS
1103 BEGIN
1104 -- Now sharing system information with BOM
1105 -- RETURN G_System_Information.request_id;
1106
1107 RETURN Bom_Globals.Get_Request_id;
1108
1109 END Get_Request_Id;
1110
1111 /***************************************************************************
1112 * Procedure : Set_Eco_Impl
1113 * Returns : None
1114 * Parameters IN : p_eco_impl
1115 * Parameters OUT: None
1116 * Purpose : Will set the attribute Eco_Impl of system information record
1117 * to true or false based on the implemented status of the ECO
1118 *****************************************************************************/
1119 PROCEDURE Set_Eco_Impl
1120 ( p_eco_impl IN BOOLEAN )
1121 IS
1122 BEGIN
1123 G_System_Information.eco_impl := p_eco_impl;
1124
1125 END Set_Eco_Impl;
1126
1127 /***************************************************************************
1128 * Function : Is_Eco_Impl
1129 * Returns : BOOLEAN
1130 * Parameters IN : None
1131 * Parameters OUT: None
1132 * Purpose : Function will true or false value of the system information
1133 * record's attribute Eco_Impl. True if ECO is implemented and
1134 * false otherwise.
1135 *****************************************************************************/
1136 FUNCTION Is_Eco_Impl RETURN BOOLEAN
1137 IS
1138 BEGIN
1139 RETURN G_System_Information.eco_impl;
1140
1141 END Is_Eco_Impl;
1142
1143 /***************************************************************************
1144 * Procedure : Set_Eco_Cancl
1145 * Returns : None
1146 * Parameters IN : p_eco_cancl
1147 * Parameters OUT: None
1148 * Purpose : Procedure will set the value of the system information
1149 * record attribute, Eco_Cancl. True if the Eco is canceled
1150 * and false otherwise.
1151 *****************************************************************************/
1152 PROCEDURE Set_Eco_Cancl
1153 ( p_eco_cancl IN BOOLEAN )
1154 IS
1155 BEGIN
1156 G_System_Information.eco_cancl := p_eco_cancl;
1157
1158 END Set_Eco_Cancl;
1159
1160 /***************************************************************************
1161 * Function : Is_Eco_Cancl
1162 * Returns : BOOLEAN
1163 * Parameters IN : None
1164 * Parameters OUT: None
1165 * Purpose : Function will return true or false value of the system
1166 * information record's attribute Eco_Cancl.
1167 *****************************************************************************/
1168 FUNCTION Is_Eco_Cancl RETURN BOOLEAN
1169 IS
1170 BEGIN
1171 RETURN G_System_Information.eco_cancl;
1172
1173 END Is_Eco_Cancl;
1174
1175
1176 /***************************************************************************
1177 * Procedure : Set_Wkfl_Process
1178 * Returns : None
1179 * Parameters IN : p_wkfl_process
1180 * Parameters OUT: None
1181 * Purpose : Procedure will set a true or false value in the attribute
1182 * WKFL_Process of the system information record.
1183 *****************************************************************************/
1184 PROCEDURE Set_Wkfl_Process
1185 ( p_wkfl_process IN BOOLEAN )
1186 IS
1187 BEGIN
1188 G_System_Information.wkfl_process := p_wkfl_process;
1189
1190 END Set_Wkfl_Process;
1191
1192 /***************************************************************************
1193 * Function : Is_Wkfl_Process
1194 * Returns : BOOLEAN
1195 * Parameters IN : None
1196 * Parameters OUT: None
1197 * Purpose : Function will return the value of the system information
1198 * record attribute Wkfl_Process. True if a Workflow process
1199 * exists the ECO and false otherwise.
1200 *****************************************************************************/
1201 FUNCTION Is_Wkfl_Process RETURN BOOLEAN
1202 IS
1203 BEGIN
1204 RETURN G_System_Information.wkfl_process;
1205
1206 END Is_Wkfl_Process;
1207
1208
1209 /***************************************************************************
1210 * Procedure : Set_Eco_Access
1211 * Returns : None
1212 * Parameters IN : p_eco_access
1213 * Parameters OUT: None
1214 * Purpose : Procedure will set the value of the system information record
1215 * attribute Eco_Access. True if the user has access to the ECO
1216 * and false otherwise.
1217 *****************************************************************************/
1218 PROCEDURE Set_Eco_Access
1219 ( p_eco_access IN BOOLEAN )
1220 IS
1221 BEGIN
1222 G_System_Information.eco_access := p_eco_access;
1223
1224 END Set_Eco_Access;
1225
1226 /***************************************************************************
1227 * Function : Is_Eco_Access
1228 * Returns : BOOLEAN
1229 * Parameters IN : None
1230 * Parameters OUT: None
1231 * Purpose : Function will return true if the Eco_Access is True and
1232 * false otherwise.
1233 *****************************************************************************/
1234 FUNCTION Is_Eco_Access RETURN BOOLEAN
1235 IS
1236 BEGIN
1237 RETURN G_System_Information.eco_access;
1238
1239 END Is_Eco_Access;
1240
1241 /***************************************************************************
1242 * Procedure : Set_RItem_Impl
1243 * Returns : None
1244 * Parameters IN : p_ritem_impl
1245 * Parameters OUT: None
1246 * Purpose : Procedure will set the value of system iformation record
1247 * attribute RItem_Impl.
1248 *****************************************************************************/
1249 PROCEDURE Set_RItem_Impl
1250 ( p_ritem_impl IN BOOLEAN )
1251 IS
1252 BEGIN
1253 G_System_Information.ritem_impl := p_ritem_impl;
1254
1255 END Set_RItem_Impl;
1256
1257 /***************************************************************************
1258 * Function : Is_RItem_Impl
1259 * Returns : BOOLEAN
1260 * Parameters IN : None
1261 * Parameters OUT: None
1262 * Purpose : Function will answer true or false to the question
1263 * Is Revised Item Implemented ?
1264 *****************************************************************************/
1265 FUNCTION Is_RItem_Impl RETURN BOOLEAN
1266 IS
1267 BEGIN
1268 RETURN G_System_Information.RItem_Impl;
1269
1270 END Is_RItem_Impl;
1271
1272 /***************************************************************************
1273 * Procedure : Set_RItem_Cancl
1274 * Returns : None
1275 * Parameters IN : p_ritem_cancl
1276 * Parameters OUT: None
1277 * Purpose : Procedure will set the value of system information record
1278 * attribute RItem_cancl.
1279 *****************************************************************************/
1280 PROCEDURE Set_RItem_Cancl
1281 ( p_ritem_cancl IN BOOLEAN )
1282 IS
1283 BEGIN
1284 G_System_Information.ritem_cancl := p_ritem_cancl;
1285
1286 END Set_RItem_Cancl;
1287
1288 /***************************************************************************
1289 * Function : Is_RItem_Cancl
1290 * Returns : BOOLEAN
1291 * Parameters IN : None
1292 * Parameters OUT: None
1293 * Purpose : Function will answer true or false to the question
1294 * Is Revised Item Canceled?
1295 *****************************************************************************/
1296 FUNCTION Is_RItem_Cancl RETURN BOOLEAN
1297 IS
1298 BEGIN
1299 RETURN G_System_Information.ritem_cancl;
1300
1301 END Is_RItem_Cancl;
1302
1303 /***************************************************************************
1304 * Procedure : Set_RComp_Cancl
1305 * Returns : None
1306 * Parameters IN : p_Comp_Cancl
1307 * Parameters OUT: None
1308 * Purpose : Procedure will set the value of system iformation record
1309 * attribute RComp_Cancl.
1310 *****************************************************************************/
1311 PROCEDURE Set_RComp_Cancl
1312 ( p_rcomp_cancl IN BOOLEAN )
1313 IS
1314 BEGIN
1315 G_System_Information.rcomp_cancl := p_rcomp_cancl;
1316
1317 END Set_RComp_Cancl;
1318
1319 /***************************************************************************
1320 * Function : Is_RComp_Cancl
1321 * Returns : BOOLEAN
1322 * Parameters IN : None
1323 * Parameters OUT: None
1324 * Purpose : Function will answer true or false to the question
1325 * Is Revised Revised Component canceled ?
1326 *****************************************************************************/
1327 FUNCTION Is_RComp_Cancl RETURN BOOLEAN
1328 IS
1329 BEGIN
1330 RETURN G_System_Information.rcomp_cancl;
1331
1332 END Is_rcomp_cancl;
1333
1334
1335 /***************************************************************************
1336 * Added by MK on 09/01/2000
1337 * Procedure : Set_ROp_Cancl
1338 * Returns : None
1339 * Parameters IN : p_rop_cancl
1340 * Parameters OUT: None
1341 * Purpose : Procedure will set the value of system iformation record
1342 * attribute ROp_Cancl.
1343 *****************************************************************************/
1344 PROCEDURE Set_ROp_Cancl
1345 ( p_rcomp_cancl IN BOOLEAN )
1346 IS
1347 BEGIN
1348 G_System_Information.rcomp_cancl := p_rcomp_cancl;
1349
1350 END Set_ROp_Cancl;
1351
1352 /***************************************************************************
1353 * Added by MK on 09/01/2000
1354 * Function : Is_ROp_Cancl
1355 * Returns : BOOLEAN
1356 * Parameters IN : None
1357 * Parameters OUT: None
1358 * Purpose : Function will answer true or false to the question
1359 * Is Revised Operation canceled ?
1360 *****************************************************************************/
1361 FUNCTION Is_ROp_Cancl RETURN BOOLEAN
1362 IS
1363 BEGIN
1364 RETURN G_System_Information.ROp_cancl;
1365
1366 END Is_ROp_cancl;
1367
1368
1369 /***************************************************************************
1370 * Procedure : Set_Std_Item_Access
1371 * Returns : None
1372 * Parameters IN : p_std_item_access
1373 * Parameters OUT: None
1374 * Purpose : Will set the value of the attribute STD_Item_Access in the
1375 * system information record.
1376 *****************************************************************************/
1377 PROCEDURE Set_Std_Item_Access
1378 ( p_std_item_access IN NUMBER )
1379 IS
1380 BEGIN
1381 G_System_Information.std_item_access := p_std_item_access;
1382
1383 END Set_Std_Item_Access;
1384
1385 /**************************************************************************
1386 * Function : Get_Std_Item_Access
1387 * Returns : NUMBER
1388 * Parameters IN : None
1389 * Parameters OUT: None
1390 * Purpose : Will return the value of the Standard Item Access attribute
1391 * Std_Item_Access from the system information record.
1392 ***************************************************************************/
1393 FUNCTION Get_Std_Item_Access RETURN NUMBER
1394 IS
1395 BEGIN
1396 RETURN G_System_Information.std_item_access;
1397
1398 END Get_Std_Item_Access;
1399
1400 /***************************************************************************
1401 * Procedure : Set_Mdl_Item_Access
1402 * Returns : None
1403 * Parameters IN : p_Mdl_item_access
1404 * Parameters OUT: None
1405 * Purpose : Will set the value of the attribute Mdl_Item_Access in the
1406 * system information record.
1407 *****************************************************************************/
1408 PROCEDURE Set_Mdl_Item_Access
1409 ( p_mdl_item_access IN NUMBER )
1410 IS
1411 BEGIN
1412 G_System_Information.mdl_item_access := p_mdl_item_access;
1413
1414 END Set_Mdl_Item_Access;
1415
1416 /**************************************************************************
1417 * Function : Get_Mdl_Item_Access
1418 * Returns : NUMBER
1419 * Parameters IN : None
1420 * Parameters OUT: None
1421 * Purpose : Will return the value of the Model Item Access attribute
1422 * Mdl_Item_Access from the system information record.
1423 ***************************************************************************/
1424 FUNCTION Get_Mdl_Item_Access RETURN NUMBER
1425 IS
1426 BEGIN
1427 RETURN G_System_Information.mdl_item_access;
1428
1429 END Get_Mdl_Item_Access;
1430
1431
1432 /***************************************************************************
1433 * Procedure : Set_Pln_Item_Access
1434 * Returns : None
1435 * Parameters IN : p_Pln_item_access
1436 * Parameters OUT: None
1437 * Purpose : Will set the value of the attribute Pln_Item_Access in the
1438 * system information record.
1439 *****************************************************************************/
1440 PROCEDURE Set_Pln_Item_Access
1441 ( p_Pln_item_access IN NUMBER )
1442 IS
1443 BEGIN
1444 G_System_Information.Pln_item_access := p_Pln_item_access;
1445
1446 END Set_Pln_Item_Access;
1447
1448 /**************************************************************************
1449 * Function : Get_Pln_Item_Access
1450 * Returns : NUMBER
1451 * Parameters IN : None
1452 * Parameters OUT: None
1453 * Purpose : Will return the value of the Planning Item Access attribute
1454 * Pln_Item_Access from the system information record.
1455 ***************************************************************************/
1456 FUNCTION Get_Pln_Item_Access RETURN NUMBER
1457 IS
1458 BEGIN
1459 RETURN G_System_Information.Pln_item_access;
1460
1461 END Get_Pln_Item_Access;
1462
1463 /***************************************************************************
1464 * Procedure : Set_OC_Item_Access
1465 * Returns : None
1466 * Parameters IN : p_OC_item_access
1467 * Parameters OUT: None
1468 * Purpose : Will set the value of the attribute OC_Item_Access in the
1469 * system information record.
1470 *****************************************************************************/
1471 PROCEDURE Set_OC_Item_Access
1472 ( p_oc_item_access IN NUMBER )
1473 IS
1474 BEGIN
1475 G_System_Information.oc_item_access := p_oc_item_access;
1476
1477 END Set_OC_Item_Access;
1478
1479 /**************************************************************************
1480 * Function : Get_OC_Item_Access
1481 * Returns : NUMBER
1482 * Parameters IN : None
1483 * Parameters OUT: None
1484 * Purpose : Will return value of the Option Class Item Access attribute
1485 * OC_Item_Access from the system information record.
1486 ***************************************************************************/
1487 FUNCTION Get_OC_Item_Access RETURN NUMBER
1488 IS
1489 BEGIN
1490 RETURN G_System_Information.oc_item_access;
1491
1492 END Get_OC_Item_Access;
1493
1494 /***************************************************************************
1495 * Procedure : Set_Unit_Effectivity
1496 * Returns : None
1497 * Parameters IN : p_Unit_Effectivity
1498 * Parameters OUT: None
1499 * Purpose : Will set the value of the attribute Unit_Effectivity in the
1500 * system information record.
1501 *****************************************************************************/
1502 PROCEDURE Set_Unit_Effectivity
1503 ( p_Unit_Effectivity IN BOOLEAN )
1504 IS
1505 BEGIN
1506 G_System_Information.unit_effectivity := p_unit_effectivity;
1507
1508 END Set_Unit_Effectivity;
1509
1510 /**************************************************************************
1511 * Function : Get_Unit_Effectivity
1512 * Returns : NUMBER
1513 * Parameters IN : None
1514 * Parameters OUT: None
1515 * Purpose : Will return value of the unit effective item attribute
1516 * Unit_Effectivity from the system information record.
1517 ***************************************************************************/
1518 FUNCTION Get_Unit_Effectivity RETURN BOOLEAN
1519 IS
1520 BEGIN
1521 RETURN G_System_Information.Unit_Effectivity;
1522
1523 END Get_Unit_Effectivity;
1524
1525 /***************************************************************************
1526 * Procedure : Set_Unit_Controlled_Item
1527 * Returns : None
1528 * Parameters IN : p_Unit_Controlled_Item
1529 * Parameters OUT: None
1530 * Purpose : Will set the value of the attribute Unit_Controlled_Item in
1531 * system information record.
1532 *****************************************************************************/
1533 PROCEDURE Set_Unit_Controlled_Item
1534 ( p_Unit_Controlled_Item IN BOOLEAN)
1535 IS
1536 BEGIN
1537 G_System_Information.unit_controlled_item := p_unit_controlled_item;
1538
1539 END Set_Unit_Controlled_Item;
1540
1541 /**************************************************************************
1542 * Function : Get_Unit_Controlled_Item
1543 * Returns : NUMBER
1544 * Parameters IN : None
1545 * Parameters OUT: None
1546 * Purpose : Will return value of the unit effective item attribute
1547 * Unit_Controlled_Item from the system information record.
1548 ***************************************************************************/
1549 FUNCTION Get_Unit_Controlled_Item RETURN BOOLEAN
1550 IS
1551 BEGIN
1552 RETURN G_System_Information.Unit_Controlled_Item;
1553
1554 END Get_Unit_Controlled_Item;
1555
1556 /***************************************************************************
1557 * Procedure : Set_Unit_Controlled_Component
1558 * Returns : None
1559 * Parameters IN : p_Unit_Controlled_Component
1560 * Parameters OUT: None
1561 * Purpose : Will set the value of the attribute Unit_Controlled_Component
1562 * in the system information record.
1563 *****************************************************************************/
1564 PROCEDURE Set_Unit_Controlled_Component
1565 ( p_Unit_Controlled_Component IN BOOLEAN)
1566 IS
1567 BEGIN
1568 G_System_Information.unit_controlled_component
1569 := p_unit_controlled_component;
1570
1571 END Set_Unit_Controlled_Component;
1572
1573 /**************************************************************************
1574 * Function : Get_Unit_Controlled_Component
1575 * Returns : NUMBER
1576 * Parameters IN : None
1577 * Parameters OUT: None
1578 * Purpose : Will return value of the unit effective component attribute
1579 * Unit_Controlled_Component from the system information record.
1580 ***************************************************************************/
1581 FUNCTION Get_Unit_Controlled_Component RETURN BOOLEAN
1582 IS
1583 BEGIN
1584 RETURN G_System_Information.Unit_Controlled_Component;
1585
1586 END Get_Unit_Controlled_Component;
1587
1588 /***************************************************************************
1589 * Procedure : Set_Current_Revision
1590 * Returns : None
1591 * Parameters IN : p_current_revision
1592 * Parameters OUT: None
1593 * Purpose : Procedure will set the current revision attribute of the
1594 * system information record.
1595 *****************************************************************************/
1596 PROCEDURE Set_Current_Revision
1597 ( p_current_revision IN VARCHAR2 )
1598 IS
1599 BEGIN
1600 G_System_Information.current_revision := p_current_revision;
1601
1602 END Set_Current_Revision;
1603
1604 /***************************************************************************
1605 * Function : Get_Current_Revision
1606 * Returns : VARCHAR2(3)
1607 * Parameters IN : None
1608 * Parameters OUT: None
1609 * Purpose : Function will return the value of current revision attribute
1610 * of the system information record.
1611 *****************************************************************************/
1612 FUNCTION Get_Current_Revision RETURN VARCHAR2
1613 IS
1614 BEGIN
1615 RETURN G_System_Information.current_revision;
1616
1617 END Get_Current_Revision;
1618
1619 /***************************************************************************
1620 * Procedure : Set_BO_Identifier
1621 * Returns : None
1622 * Parameters IN : p_bo_identifier
1623 * Parameters OUT: None
1624 * Purpose : Procedure will set the Business object identifier attribute
1625 * BO_Identifier of the system information record.
1626 *****************************************************************************/
1627 PROCEDURE Set_BO_Identifier
1628 ( p_bo_identifier IN VARCHAR2 )
1629 IS
1630 BEGIN
1631 G_System_Information.bo_identifier := p_bo_identifier;
1632 Error_Handler.Set_Bo_Identifier(p_bo_identifier);
1633
1634 END Set_BO_Identifier;
1635
1636 /***************************************************************************
1637 * Function : Get_BO_Identifier
1638 * Returns : VARCHAR2
1639 * Parameters IN : None
1640 * Parameters OUT: None
1641 * Purpose : Function will return the value of the business object
1642 * identifier attribute BO_Identifier from the system
1643 * information record.
1644 *****************************************************************************/
1645 FUNCTION Get_BO_Identifier RETURN VARCHAR2
1646 IS
1647 BEGIN
1648 RETURN G_System_Information.bo_identifier;
1649
1650 END Get_BO_Identifier;
1651
1652
1653 /******************************************************************************
1654 * Procedure : Create_New_Routing
1655 * Parameters IN : Assembly_Item_Id
1656 * Organization_Id
1657 * Alternate_Routing_Code
1658 * Pending from ECN
1659 * Common_Routing_Sequence_Id
1660 * Routing_Type
1661 * WHO columns
1662 * Revised_Item_Sequence_Id
1663 * Purpose : This procedure will be called when a revised operation is
1664 * the first operation being added on a revised item. This
1665 * procedure will create a Routing and update the revised item
1666 * information indicating that routing for this revised item now
1667 * exists.
1668 ******************************************************************************/
1669 PROCEDURE Create_New_Routing
1670 ( p_assembly_item_id IN NUMBER
1671 , p_organization_id IN NUMBER
1672 , p_alternate_routing_code IN VARCHAR2 := NULL
1673 , p_pending_from_ecn IN VARCHAR2
1674 , p_routing_sequence_id IN NUMBER
1675 , p_common_routing_sequence_id IN NUMBER
1676 , p_routing_type IN NUMBER
1677 , p_last_update_date IN DATE
1678 , p_last_updated_by IN NUMBER
1679 , p_creation_date IN DATE
1680 , p_created_by IN NUMBER
1681 , p_login_id IN NUMBER
1682 , p_revised_item_sequence_id IN NUMBER
1683 , p_original_system_reference IN VARCHAR2
1684 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1685 , x_return_status OUT NOCOPY VARCHAR2
1686 )
1687 IS
1688 -- Error Handlig Variables
1689 l_return_status VARCHAR2(1);
1690 l_err_text VARCHAR2(2000) ;
1691 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1692
1693 BEGIN
1694 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1695
1696 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1697 ('Create New Routing for ECO . . .') ;
1698 END IF ;
1699
1700 --
1701 -- Create New Routing using Routing Information in Revised Item table
1702 --
1703 INSERT INTO BOM_OPERATIONAL_ROUTINGS
1704 ( assembly_item_id
1705 , organization_id
1706 , alternate_routing_designator
1707 , pending_from_ecn
1708 , routing_sequence_id
1709 , common_routing_sequence_id
1710 , routing_type
1711 , last_update_date
1712 , last_updated_by
1713 , creation_date
1714 , created_by
1715 , last_update_login
1716 , original_system_reference
1717 , cfm_routing_flag
1718 , completion_subinventory
1719 , completion_locator_id
1720 , mixed_model_map_flag
1721 , priority
1722 , ctp_flag
1723 , routing_comment
1724 )
1725 SELECT p_assembly_item_id
1726 , p_organization_id
1727 , p_alternate_routing_code
1728 , p_pending_from_ecn
1729 , p_routing_sequence_id
1730 , p_common_routing_sequence_id
1731 , p_routing_type
1732 , p_last_update_date
1733 , p_last_updated_by
1734 , p_creation_date
1735 , p_created_by
1736 , p_login_id
1737 , p_original_system_reference
1738 -- Bug 2232521
1739 -- Some time NULL value was defaulted to the cfm_routing_flag.
1740 -- So, the form ENGFDECN.fmb is not displaying the Operations.
1741 -- , cfm_routing_flag
1742 , NVL(cfm_routing_flag,2)
1743 , completion_subinventory
1744 , completion_locator_id
1745 , mixed_model_map_flag
1746 , priority
1747 , ctp_flag
1748 , routing_comment
1749 FROM ENG_REVISED_ITEMS
1750 WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
1751
1752 --
1753 -- Set Routing Sequence Id to Revised Item table
1754 --
1755 UPDATE ENG_REVISED_ITEMS
1756 SET routing_sequence_id = p_routing_sequence_id
1757 , last_update_date = p_last_update_date -- Last Update Date
1758 , last_updated_by = p_last_updated_by -- Last Updated By
1759 , last_update_login = p_login_id -- Last Update Login
1760 WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
1761
1762 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1763 ('Set created routing sequence id : ' || to_char(p_routing_sequence_id)
1764 || ' to the parenet revised item . . .') ;
1765 END IF ;
1766
1767
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1771 ('Some unknown error in Creating New Routing . . .' || SQLERRM );
1772 END IF ;
1773
1774 l_err_text := G_PKG_NAME || 'Utilities (Create New Routing) '
1775 || substrb(SQLERRM,1,200);
1776
1777 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1778
1779 Error_Handler.Add_Error_Token
1780 ( p_message_name => NULL
1781 , p_message_text => l_err_text
1782 , p_mesg_token_tbl => l_mesg_token_tbl
1783 , x_mesg_token_tbl => l_mesg_token_tbl
1784 ) ;
1785
1786 -- Return the status and message table.
1787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1788 x_mesg_token_tbl := l_mesg_token_tbl ;
1789 END Create_New_Routing ;
1790
1791
1792 /********************************************************************
1793 * Procedure : Cancel_Operaiton
1794 * Parameters IN : Common Operation exposed column record
1795 * Common Operation unexposed column record
1796 * Parameters OUT: Return Status
1797 * Message Token Table
1798 * Purpose : This procedure will move revised operation to Eng Revised
1799 * Operation table and set cansel information.
1800 * Also it will delte any child operation resources and sub
1801 * operation resources.
1802 *********************************************************************/
1803 PROCEDURE Cancel_Operation
1804 ( p_operation_sequence_id IN NUMBER
1805 , p_cancel_comments IN VARCHAR2
1806 , p_op_seq_num IN NUMBER
1807 , p_user_id IN NUMBER
1808 , p_login_id IN NUMBER
1809 , p_prog_id IN NUMBER
1810 , p_prog_appid IN NUMBER
1811 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1812 , x_return_status OUT NOCOPY VARCHAR2
1813 )
1814
1815
1816 IS
1817
1818 -- Error Handlig Variables
1819 l_return_status VARCHAR2(1);
1820 l_err_text VARCHAR2(2000) ;
1821 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1822 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1823
1824
1825 BEGIN
1826 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1827 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1828
1829 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1830 Error_Handler.Write_Debug('Performing cancel revised operation : '
1831 || to_char(p_operation_sequence_id) || ' . . .') ;
1832 END IF ;
1833
1834 --
1835 -- Insert the cancelled revised operation into
1836 -- ENG_REVISED_OPERATIONS
1837 --
1838 INSERT INTO ENG_REVISED_OPERATIONS (
1839 operation_sequence_id
1840 , routing_sequence_id
1841 , operation_seq_num
1842 , last_update_date
1843 , last_updated_by
1844 , creation_date
1845 , created_by
1846 , last_update_login
1847 , standard_operation_id
1848 , department_id
1849 , operation_lead_time_percent
1850 , minimum_transfer_quantity
1851 , count_point_type
1852 , operation_description
1853 , effectivity_date
1854 , disable_date
1855 , backflush_flag
1856 , option_dependent_flag
1857 , attribute_category
1858 , attribute1
1859 , attribute2
1860 , attribute3
1861 , attribute4
1862 , attribute5
1863 , attribute6
1864 , attribute7
1865 , attribute8
1866 , attribute9
1867 , attribute10
1868 , attribute11
1869 , attribute12
1870 , attribute13
1871 , attribute14
1872 , attribute15
1873 , request_id
1874 , program_application_id
1875 , program_id
1876 , program_update_date
1877 , operation_type
1878 , reference_flag
1879 , process_op_seq_id
1880 , line_op_seq_id
1881 , yield
1882 , cumulative_yield
1883 , reverse_cumulative_yield
1884 , labor_time_calc
1885 , machine_time_calc
1886 , total_time_calc
1887 , labor_time_user
1888 , machine_time_user
1889 , total_time_user
1890 , net_planning_percent
1891 , x_coordinate
1892 , y_coordinate
1893 , include_in_rollup
1894 , operation_yield_enabled
1895 , change_notice
1896 , implementation_date
1897 , old_operation_sequence_id
1898 , acd_type
1899 , revised_item_sequence_id
1900 , cancellation_date
1901 , cancel_comments
1902 , original_system_reference )
1903 SELECT
1904 OPERATION_SEQUENCE_ID
1905 , ROUTING_SEQUENCE_ID
1906 , OPERATION_SEQ_NUM
1907 , SYSDATE /* Last Update Date */
1908 , p_user_id /* Last Updated By */
1909 , SYSDATE /* Creation Date */
1910 , p_user_id /* Created By */
1911 , p_login_id /* Last Update Login */
1912 , STANDARD_OPERATION_ID
1913 , DEPARTMENT_ID
1914 , OPERATION_LEAD_TIME_PERCENT
1915 , MINIMUM_TRANSFER_QUANTITY
1916 , COUNT_POINT_TYPE
1917 , OPERATION_DESCRIPTION
1918 , EFFECTIVITY_DATE
1919 , DISABLE_DATE
1920 , BACKFLUSH_FLAG
1921 , OPTION_DEPENDENT_FLAG
1922 , ATTRIBUTE_CATEGORY
1923 , ATTRIBUTE1
1924 , ATTRIBUTE2
1925 , ATTRIBUTE3
1926 , ATTRIBUTE4
1927 , ATTRIBUTE5
1928 , ATTRIBUTE6
1929 , ATTRIBUTE7
1930 , ATTRIBUTE8
1931 , ATTRIBUTE9
1932 , ATTRIBUTE10
1933 , ATTRIBUTE11
1934 , ATTRIBUTE12
1935 , ATTRIBUTE13
1936 , ATTRIBUTE14
1937 , ATTRIBUTE15
1938 , NULL /* Request Id */
1939 , p_prog_appid /* Application Id */
1940 , p_prog_id /* Program Id */
1941 , SYSDATE /* program_update_date */
1942 , OPERATION_TYPE
1943 , REFERENCE_FLAG
1944 , PROCESS_OP_SEQ_ID
1945 , LINE_OP_SEQ_ID
1946 , YIELD
1947 , CUMULATIVE_YIELD
1948 , REVERSE_CUMULATIVE_YIELD
1949 , LABOR_TIME_CALC
1950 , MACHINE_TIME_CALC
1951 , TOTAL_TIME_CALC
1952 , LABOR_TIME_USER
1953 , MACHINE_TIME_USER
1954 , TOTAL_TIME_USER
1955 , NET_PLANNING_PERCENT
1956 , X_COORDINATE
1957 , Y_COORDINATE
1958 , INCLUDE_IN_ROLLUP
1959 , OPERATION_YIELD_ENABLED
1960 , CHANGE_NOTICE
1961 , IMPLEMENTATION_DATE
1962 , OLD_OPERATION_SEQUENCE_ID
1963 , ACD_TYPE
1964 , REVISED_ITEM_SEQUENCE_ID
1965 , SYSDATE /* Cancellation Date */
1966 , p_cancel_comments /* Cancel Comments */
1967 , ORIGINAL_SYSTEM_REFERENCE
1968 FROM BOM_OPERATION_SEQUENCES
1969 WHERE operation_sequence_id = p_operation_sequence_id ;
1970
1971
1972 --
1973 -- Delete Cancel Revisd Operation from operation table
1974 --
1975 DELETE FROM BOM_OPERATION_SEQUENCES
1976 WHERE operation_sequence_id = p_operation_sequence_id ;
1977
1978 --
1979 -- Delete child Operation Resources
1980 --
1981 DELETE FROM BOM_OPERATION_RESOURCES
1982 WHERE operation_sequence_id = p_operation_sequence_id ;
1983
1984
1985 IF SQL%FOUND THEN
1986
1987 --
1988 -- Log a warning indicating operation resources and
1989 -- substitute operation resources also get deleted.
1990 --
1991 -- l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
1992 -- l_Token_Tbl(1).token_value := p_op_seq_num ;
1993
1994 Error_Handler.Add_Error_Token
1995 ( p_Message_Name => 'BOM_OP_CANCEL_DEL_CHILDREN'
1996 , p_Message_Text => NULL
1997 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1998 , p_Token_Tbl => l_Token_Tbl
1999 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2000 , p_message_type => 'W'
2001 ) ;
2002
2003 END IF ;
2004
2005
2006 --
2007 -- Delete child Sub Operation Resources
2008 --
2009 DELETE FROM BOM_SUB_OPERATION_RESOURCES
2010 WHERE operation_sequence_id = p_operation_sequence_id ;
2011
2012 -- Return Token
2013 x_mesg_token_tbl := l_mesg_token_tbl ;
2014
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2018 ('Unexpected Error occured in Cancel . . .' || SQLERRM);
2019 END IF;
2020
2021 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2022 THEN
2023 l_err_text := G_PKG_NAME || ' : Utility (Operation Cancel) ' ||
2024 SUBSTR(SQLERRM, 1, 200);
2025 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2026
2027 Error_Handler.Add_Error_Token
2028 ( p_message_name => NULL
2029 , p_message_text => l_err_text
2030 , p_mesg_token_tbl => l_mesg_token_tbl
2031 , x_mesg_token_tbl => l_mesg_token_tbl
2032 ) ;
2033 END IF ;
2034
2035 -- Return the status and message table.
2036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2037 x_mesg_token_tbl := l_mesg_token_tbl ;
2038
2039 END Cancel_Operation ;
2040
2041
2042 /*********************************************************************
2043 * Procedure : Perform_Writes_For_Primary_RTG
2044 * Parameters IN : Revised Operation exposed column record
2045 * Revised Operation unexposed column record
2046 * Parameters OUT: Return Status
2047 * Message Token Table
2048 * Purpose : This procedure has been moved here from BOM_Op_Seq_UTIL
2049 * packages to make the RTG object independant of the ENG object.
2050 * Also modified Common Op record to Rev Op Record.
2051 * Check if Primary routing for current revised operation exists.
2052 * Then if not, Create New Primary Routing and
2053 * New Routing Revision
2054 *********************************************************************/
2055 PROCEDURE Perform_Writes_For_Primary_RTG
2056 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
2057 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
2058 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
2059 := Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
2060 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2061 , x_return_status OUT NOCOPY VARCHAR2
2062 )
2063 IS
2064
2065 l_rev_operation_rec Bom_Rtg_Pub.Rev_Operation_Rec_Type ;
2066 l_rev_op_unexp_rec Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type ;
2067 l_routing_sequence_id NUMBER ; -- Routing Sequence Id
2068 l_routing_type NUMBER ; -- Routing Type
2069 -- Bug 2233631
2070 l_routing_type1 NUMBER ; -- Assembly_type from ENG_CHANGE_ORDER_TYUPES, table.
2071 -- Error Handlig Variables
2072 l_return_status VARCHAR2(1);
2073 l_temp_return_status VARCHAR2(1);
2074 l_err_text VARCHAR2(2000) ;
2075 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2076 l_temp_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2077 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2078
2079 -- Check if Routing exists
2080 CURSOR l_rtg_exists_csr ( p_revised_item_id NUMBER
2081 , p_organization_id NUMBER
2082 , p_alternate_rtg_code VARCHAR2
2083 )
2084 IS
2085 SELECT 'Routing Exists'
2086 FROM DUAL
2087 WHERE NOT EXISTS ( SELECT routing_sequence_id
2088 FROM BOM_OPERATIONAL_ROUTINGS
2089 WHERE assembly_item_id = p_revised_item_id
2090 AND organization_id = p_organization_id
2091 AND NVL(alternate_routing_designator, FND_API.G_MISS_CHAR) =
2092 NVL(p_alternate_rtg_code,FND_API.G_MISS_CHAR)
2093 );
2094
2095 -- Get Eng_Item_Flag for Routing Type value
2096 CURSOR l_routing_type_csr ( p_revised_item_id NUMBER
2097 , p_organization_id NUMBER )
2098 IS
2099 SELECT decode(eng_item_flag, 'N', 1, 2) eng_item_flag
2100 FROM MTL_SYSTEM_ITEMS
2101 WHERE inventory_item_id = p_revised_item_id
2102 AND organization_id = p_organization_id ;
2103
2104 -- Get Routing_Sequence_id
2105 CURSOR l_get_rtg_seq_csr
2106 IS
2107 SELECT BOM_OPERATIONAL_ROUTINGS_S.NEXTVAL routing_sequence_id
2108 FROM DUAL ;
2109
2110
2111 BEGIN
2112
2113 --
2114 -- Initialize Rev Op Record and Status
2115 --
2116 l_rev_operation_rec := p_rev_operation_rec ;
2117 l_rev_op_unexp_rec := p_rev_op_unexp_rec ;
2118 l_return_status := FND_API.G_RET_STS_SUCCESS ;
2119 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2120
2121
2122 IF l_rev_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE THEN
2123
2124 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2125 ('Check if primary rtg exists and if not, new primary rtg is created. . . ') ;
2126 END IF;
2127
2128 --
2129 -- Revised Operation
2130 --
2131 FOR l_rtg_exists_rec IN l_rtg_exists_csr
2132 ( p_revised_item_id => l_rev_op_unexp_rec.revised_item_id
2133 , p_organization_id => l_rev_op_unexp_rec.organization_id
2134 , p_alternate_rtg_code => l_rev_operation_rec.alternate_routing_code
2135 )
2136 LOOP
2137 --
2138 -- Loop executes then the Routing does not exist.
2139 --
2140 FOR l_routing_type_rec IN l_routing_type_csr
2141 ( p_revised_item_id => p_rev_op_unexp_rec.revised_item_id
2142 , p_organization_id => p_rev_op_unexp_rec.organization_id)
2143 LOOP
2144 l_routing_type := l_routing_type_rec.eng_item_flag ;
2145 END LOOP ;
2146
2147 -- Bug 2233631
2148 -- Say assembly item a#1 is a Manufacturing Item, and routing was created
2149 -- through ECO (Engineering type). Then this routing should be created in
2150 -- Engineering routing. So, this routing should be displayed only in the
2151 -- Engineering (prototype) responsibility. But becasue of the above condition
2152 -- It was created as a Manufaturing routing. Now I am adding one more condition
2153 -- to check wether the routing is created for Engineering or Manufacturing res
2154 -- ponsibility.
2155
2156 l_routing_type1 := Get_ECO_Assembly_Type(l_rev_operation_rec.ECO_name,p_rev_op_unexp_rec.organization_id);
2157 if (l_routing_type1 <> 0) then
2158 l_routing_type := l_routing_type1;
2159 end if;
2160
2161 --Bug 2233631 End
2162
2163 -- If Caller Type is FORM, Generate new routing_sequence_id
2164 --
2165 IF p_control_rec.caller_type = 'FORM'
2166 THEN
2167 FOR l_get_rtg_seq_rec IN l_get_rtg_seq_csr
2168 LOOP
2169 l_rev_op_unexp_rec.routing_sequence_id :=
2170 l_get_rtg_seq_rec.routing_sequence_id;
2171 END LOOP;
2172
2173 l_Token_Tbl(1).token_name := 'REVISED_ITEM_NAME';
2174 --Bug 3614603
2175 l_Token_Tbl(1).token_value := p_rev_operation_rec.revised_item_name ;
2176 l_Token_Tbl(2).token_name := 'OP_SEQ_NUMBER';
2177 l_Token_Tbl(2).token_value := p_rev_operation_rec.operation_sequence_number ;
2178
2179 Error_Handler.Add_Error_Token
2180 ( p_Message_Name => 'BOM_NEW_PRIMARY_RTG_CREATED'
2181 , p_Message_Text => NULL
2182 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2183 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2184 , p_message_type => 'W'
2185
2186 ) ;
2187 ELSE
2188
2189 --
2190 -- Log a warning indicating that a new bill has been created
2191 -- as a result of the operation being added.
2192 --
2193 l_Token_Tbl(1).token_name := 'REVISED_ITEM_NAME';
2194 --Bug 3614603
2195 l_Token_Tbl(1).token_value := p_rev_operation_rec.revised_item_name;
2196 l_Token_Tbl(2).token_name := 'OP_SEQ_NUMBER';
2197 l_Token_Tbl(2).token_value := p_rev_operation_rec.operation_sequence_number ;
2198
2199 Error_Handler.Add_Error_Token
2200 ( p_Message_Name => 'BOM_NEW_PRIMARY_RTG_CREATED'
2201 , p_Message_Text => NULL
2202 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2203 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2204 , p_Token_Tbl => l_Token_Tbl
2205 , p_message_type => 'W'
2206 ) ;
2207 END IF ;
2208
2209 --
2210 -- Create New Routing using Routing Attributes in Revised Items table
2211 --
2212 Eng_Globals.Create_New_Routing
2213 ( p_assembly_item_id => l_rev_op_unexp_rec.revised_item_id
2214 , p_organization_id => l_rev_op_unexp_rec.organization_id
2215 , p_pending_from_ecn => l_rev_operation_rec.eco_name
2216 , p_routing_sequence_id => l_rev_op_unexp_rec.routing_sequence_id
2217 , p_common_routing_sequence_id => l_rev_op_unexp_rec.routing_sequence_id
2218 , p_routing_type => l_routing_type
2219 , p_last_update_date => SYSDATE
2220 , p_last_updated_by => BOM_Rtg_Globals.Get_User_Id
2221 , p_creation_date => SYSDATE
2222 , p_created_by => BOM_Rtg_Globals.Get_User_Id
2223 , p_login_id => BOM_Rtg_Globals.Get_Login_Id
2224 , p_revised_item_sequence_id => l_rev_op_unexp_rec.revised_item_sequence_id
2225 , p_original_system_reference => l_rev_operation_rec.original_system_reference
2226 , x_Mesg_Token_Tbl => l_temp_mesg_token_Tbl
2227 , x_return_status => l_temp_return_status
2228 ) ;
2229
2230
2231 IF l_temp_return_status <> FND_API.G_RET_STS_SUCCESS
2232 THEN
2233 l_return_status := l_temp_return_status ;
2234 l_mesg_token_Tbl := l_temp_Mesg_Token_Tbl ;
2235
2236 ELSE
2237 -- Create a new routing revision for the created primary routing
2238 INSERT INTO MTL_RTG_ITEM_REVISIONS
2239 ( inventory_item_id
2240 , organization_id
2241 , process_revision
2242 , implementation_date
2243 , last_update_date
2244 , last_updated_by
2245 , creation_date
2246 , created_by
2247 , last_update_login
2248 , change_notice
2249 , ecn_initiation_date
2250 , effectivity_date
2251 , revised_item_sequence_id
2252 )
2253 SELECT
2254 l_rev_op_unexp_rec.revised_item_id
2255 , l_rev_op_unexp_rec.organization_id
2256 , mp.starting_revision
2257 , SYSDATE
2258 , SYSDATE
2259 , BOM_Rtg_Globals.Get_User_Id
2260 , SYSDATE
2261 , BOM_Rtg_Globals.Get_User_Id
2262 , BOM_Rtg_Globals.Get_Login_Id
2263 , l_rev_operation_rec.eco_name
2264 , SYSDATE
2265 , SYSDATE
2266 , l_rev_op_unexp_rec.revised_item_sequence_id
2267 FROM MTL_PARAMETERS mp
2268 WHERE mp.organization_id = l_rev_op_unexp_rec.organization_id
2269 AND NOT EXISTS( SELECT NULL
2270 FROM MTL_RTG_ITEM_REVISIONS
2271 WHERE implementation_date IS NOT NULL
2272 AND organization_id = l_rev_op_unexp_rec.organization_id
2273 AND inventory_item_id = l_rev_op_unexp_rec.revised_item_id
2274 ) ;
2275
2276 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2277 ('Creating new routing revision for the created primary routing for the revised item . . . ') ;
2278 END IF;
2279
2280
2281 END IF ;
2282
2283 END LOOP ;
2284
2285 END IF ; -- End of Create
2286 --
2287 -- Return Status
2288 --
2289 x_return_status := l_return_status ;
2290 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl ;
2291
2292 EXCEPTION
2293 WHEN OTHERS THEN
2294 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2295 ('Some unknown error in Perform Writes . . .' || SQLERRM );
2296 END IF ;
2297
2298 l_err_text := G_PKG_NAME || ' Utility (Perform Writes for Primary Rtg) '
2299 || substrb(SQLERRM,1,200);
2300
2301 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2302
2303 Error_Handler.Add_Error_Token
2304 ( p_message_name => NULL
2305 , p_message_text => l_err_text
2306 , p_mesg_token_tbl => l_mesg_token_tbl
2307 , x_mesg_token_tbl => l_mesg_token_tbl
2308 ) ;
2309
2310 -- Return the status and message table.
2311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2312 x_mesg_token_tbl := l_mesg_token_tbl ;
2313
2314
2315 END Perform_Writes_for_Primary_RTG ;
2316
2317 END ENG_Globals;