[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ROUTE_STEP_UTIL
Source
1 PACKAGE BODY Eng_Change_Route_Step_Util AS
2 /* $Header: ENGUSTPB.pls 115.5 2004/05/27 19:06:37 mkimizuk ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'Eng_Change_Route_Step_Util' ;
5
6 -- For Debug
7 g_debug_file UTL_FILE.FILE_TYPE ;
8 g_debug_flag BOOLEAN := FALSE ; -- For TEST : FALSE ;
9 g_output_dir VARCHAR2(80) ;
10 g_debug_filename VARCHAR2(35) ;
11 g_debug_errmesg VARCHAR2(240);
12
13
14 /********************************************************************
15 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
16 * Write_Debug
17 * Parameters IN :
18 * Parameters OUT:
19 * Purpose : These procedures are for test and debug
20 *********************************************************************/
21 -- Open_Debug_Session
22 PROCEDURE Open_Debug_Session
23 ( p_output_dir IN VARCHAR2 := NULL
24 , p_file_name IN VARCHAR2 := NULL
25 )
26 IS
27 l_found NUMBER := 0;
28 l_utl_file_dir VARCHAR2(2000);
29
30 BEGIN
31
32 IF p_output_dir IS NOT NULL THEN
33 g_output_dir := p_output_dir ;
34
35 END IF ;
36
37 IF p_file_name IS NOT NULL THEN
38 g_debug_filename := p_file_name ;
39 END IF ;
40
41 IF g_output_dir IS NULL
42 THEN
43
44 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
45
46 END IF;
47
48 select value
49 INTO l_utl_file_dir
50 FROM v$parameter
51 WHERE name = 'utl_file_dir';
52
53 l_found := INSTR(l_utl_file_dir, g_output_dir);
54
55 IF l_found = 0
56 THEN
57 RETURN;
58 END IF;
59
60
61 -- Set Defualt debug file name
62 IF g_debug_filename IS NULL THEN
63 g_debug_filename := 'Eng_Change_Route_Step_Util.log' ;
64 END IF ;
65
66 g_debug_file := utl_file.fopen( g_output_dir
67 , g_debug_filename
68 , 'w');
69 g_debug_flag := TRUE ;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
74 g_debug_flag := FALSE;
75
76 END Open_Debug_Session ;
77
78 -- Close Debug_Session
79 PROCEDURE Close_Debug_Session
80 IS
81 BEGIN
82 IF utl_file.is_open(g_debug_file)
83 THEN
84 utl_file.fclose(g_debug_file);
85 END IF ;
86
87 EXCEPTION
88 WHEN OTHERS THEN
89 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
90 g_debug_flag := FALSE;
91
92 END Close_Debug_Session ;
93
94 -- Test Debug
95 PROCEDURE Write_Debug
96 ( p_debug_message IN VARCHAR2 )
97 IS
98 BEGIN
99
100 IF utl_file.is_open(g_debug_file)
101 THEN
102 utl_file.put_line(g_debug_file, p_debug_message);
103 END IF ;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
108 g_debug_flag := FALSE;
109
110 END Write_Debug;
111
112
113
114 /********************************************************************
115 * API Type : Private Copy Steps APIs
116 * Purpose : Those APIs are private to Copy Steps
117 *********************************************************************/
118 PROCEDURE COPY_STEPS (
119 P_FROM_ROUTE_ID IN NUMBER ,
120 P_TO_ROUTE_ID IN NUMBER ,
121 P_USER_ID IN NUMBER := NULL ,
122 P_API_CALLER IN VARCHAR2 := NULL
123 )
124 IS
125
126 cursor c is select
127 STEP_ID,
128 ROUTE_ID,
129 STEP_SEQ_NUM,
130 ADHOC_STEP_FLAG,
131 WF_ITEM_TYPE,
132 WF_ITEM_KEY,
133 WF_PROCESS_NAME,
134 CONDITION_TYPE_CODE,
135 TIMEOUT_OPTION,
136 STEP_STATUS_CODE,
137 STEP_START_DATE,
138 STEP_END_DATE,
139 REQUIRED_RELATIVE_DAYS,
140 REQUIRED_DATE,
141 ATTRIBUTE_CATEGORY,
142 ATTRIBUTE1,
143 ATTRIBUTE2,
144 ATTRIBUTE3,
145 ATTRIBUTE4,
146 ATTRIBUTE5,
147 ATTRIBUTE6,
148 ATTRIBUTE7,
149 ATTRIBUTE8,
150 ATTRIBUTE9,
151 ATTRIBUTE10,
152 ATTRIBUTE11,
153 ATTRIBUTE12,
154 ATTRIBUTE13,
155 ATTRIBUTE14,
156 ATTRIBUTE15,
157 REQUEST_ID,
158 ORIGINAL_SYSTEM_REFERENCE,
159 PROGRAM_ID,
160 PROGRAM_APPLICATION_ID,
161 PROGRAM_UPDATE_DATE,
162 ASSIGNMENT_CODE,
163 INSTRUCTION
164 from ENG_CHANGE_ROUTE_STEPS_VL
165 where ROUTE_ID = P_FROM_ROUTE_ID ;
166
167 -- No Need to Lock
168 -- for update of STEP_ID nowait;
169
170 -- General variables
171 l_fnd_user_id NUMBER ;
172 l_fnd_login_id NUMBER ;
173 l_language VARCHAR2(4) ;
174 l_rowid ROWID;
175
176 l_step_id NUMBER ;
177
178
179 BEGIN
180
181 -- Init Vars
182 l_fnd_user_id := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
183 l_fnd_login_id := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
184 l_language := userenv('LANG');
185
186 -- Real code starts here
187 -- FND_PROFILE package is not available for workflow (WF),
188 -- therefore manually set WHO column values
189 IF p_api_caller = 'WF' THEN
190 l_fnd_user_id := p_user_id;
191 l_fnd_login_id := '';
192 END IF;
193
194 IF l_fnd_user_id IS NULL THEN
195
196 l_fnd_user_id := -10000 ;
197
198 END IF ;
199
200 for recinfo in c loop
201
202 -- Get Next Sequence Value for ROUTE_ID
203 SELECT ENG_CHANGE_ROUTE_STEPS_S.NEXTVAL into l_step_id
204 FROM DUAL;
205
206 INSERT_ROW (
207 X_ROWID => l_rowid,
208 X_STEP_ID => l_step_id ,
209 X_ROUTE_ID => P_TO_ROUTE_ID ,
210 X_STEP_SEQ_NUM => recinfo.STEP_SEQ_NUM,
211 X_ADHOC_STEP_FLAG => recinfo.ADHOC_STEP_FLAG,
212 X_WF_ITEM_TYPE => recinfo.WF_ITEM_TYPE,
213 X_WF_ITEM_KEY => recinfo.WF_ITEM_KEY,
214 X_WF_PROCESS_NAME => recinfo.WF_PROCESS_NAME,
215 X_CONDITION_TYPE_CODE => recinfo.CONDITION_TYPE_CODE,
216 X_TIMEOUT_OPTION => recinfo.TIMEOUT_OPTION,
217 X_STEP_STATUS_CODE => 'NOT_STARTED' ,
218 X_STEP_START_DATE => NULL ,
219 X_STEP_END_DATE => NULL ,
220 X_REQUIRED_RELATIVE_DAYS => recinfo.REQUIRED_RELATIVE_DAYS,
221 X_REQUIRED_DATE => NULL,
222 X_ATTRIBUTE_CATEGORY => recinfo.ATTRIBUTE_CATEGORY,
223 X_ATTRIBUTE1 => recinfo.ATTRIBUTE1,
224 X_ATTRIBUTE2 => recinfo.ATTRIBUTE2,
225 X_ATTRIBUTE3 => recinfo.ATTRIBUTE3,
226 X_ATTRIBUTE4 => recinfo.ATTRIBUTE4,
227 X_ATTRIBUTE5 => recinfo.ATTRIBUTE5,
228 X_ATTRIBUTE6 => recinfo.ATTRIBUTE6,
229 X_ATTRIBUTE7 => recinfo.ATTRIBUTE7,
230 X_ATTRIBUTE8 => recinfo.ATTRIBUTE8,
231 X_ATTRIBUTE9 => recinfo.ATTRIBUTE9,
232 X_ATTRIBUTE10 => recinfo.ATTRIBUTE10,
233 X_ATTRIBUTE11 => recinfo.ATTRIBUTE11,
234 X_ATTRIBUTE12 => recinfo.ATTRIBUTE12,
235 X_ATTRIBUTE13 => recinfo.ATTRIBUTE13,
236 X_ATTRIBUTE14 => recinfo.ATTRIBUTE14,
237 X_ATTRIBUTE15 => recinfo.ATTRIBUTE15,
238 X_REQUEST_ID => recinfo.REQUEST_ID,
239 X_ORIGINAL_SYSTEM_REFERENCE => recinfo.ORIGINAL_SYSTEM_REFERENCE,
240 X_INSTRUCTION => recinfo.INSTRUCTION,
241 X_CREATION_DATE => SYSDATE,
242 X_CREATED_BY => l_fnd_user_id,
243 X_LAST_UPDATE_DATE => SYSDATE,
244 X_LAST_UPDATED_BY => l_fnd_user_id,
245 X_LAST_UPDATE_LOGIN => l_fnd_login_id,
246 X_PROGRAM_ID => recinfo.PROGRAM_ID,
247 X_PROGRAM_APPLICATION_ID => recinfo.PROGRAM_APPLICATION_ID,
248 X_PROGRAM_UPDATE_DATE => recinfo.PROGRAM_UPDATE_DATE,
249 X_ASSIGNMENT_CODE => recinfo.ASSIGNMENT_CODE
250 ) ;
251
252 --
253 --
254 -- Call People's Copy Row Procedures
255 --
256 Eng_Change_Route_People_Util.COPY_PEOPLE (
257 P_FROM_STEP_ID => recinfo.STEP_ID,
258 P_TO_STEP_ID => l_step_id ,
259 P_USER_ID => l_fnd_user_id ,
260 P_API_CALLER => P_API_CALLER
261 ) ;
262
263 end loop;
264
265
266 END COPY_STEPS ;
267
268
269
270
271 /********************************************************************
272 * API Type : Private Table Hander APIs
273 * Purpose : Those APIs are private
274 * Table Hander for TL Entity Object: ENG_CHANGE_ROUTES_VL
275 * PROCEDURE INSERT_ROW;
276 * PROCEDURE LOCK_ROW;
277 * PROCEDURE UPDATE_ROW;
278 * PROCEDURE DELETE_ROW;
279 *********************************************************************/
280 PROCEDURE INSERT_ROW (
281 X_ROWID IN OUT NOCOPY VARCHAR2,
282 X_STEP_ID IN NUMBER,
283 X_ROUTE_ID IN NUMBER,
284 X_STEP_SEQ_NUM IN NUMBER,
285 X_ADHOC_STEP_FLAG IN VARCHAR2,
286 X_WF_ITEM_TYPE IN VARCHAR2,
287 X_WF_ITEM_KEY IN VARCHAR2,
288 X_WF_PROCESS_NAME IN VARCHAR2,
289 X_CONDITION_TYPE_CODE IN VARCHAR2,
290 X_TIMEOUT_OPTION IN VARCHAR2,
291 X_STEP_STATUS_CODE IN VARCHAR2,
292 X_STEP_START_DATE IN DATE,
293 X_STEP_END_DATE IN DATE,
294 X_REQUIRED_RELATIVE_DAYS IN NUMBER,
295 X_REQUIRED_DATE IN DATE,
296 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
297 X_ATTRIBUTE1 IN VARCHAR2,
298 X_ATTRIBUTE2 IN VARCHAR2,
299 X_ATTRIBUTE3 IN VARCHAR2,
300 X_ATTRIBUTE4 IN VARCHAR2,
301 X_ATTRIBUTE5 IN VARCHAR2,
302 X_ATTRIBUTE6 IN VARCHAR2,
303 X_ATTRIBUTE7 IN VARCHAR2,
304 X_ATTRIBUTE8 IN VARCHAR2,
305 X_ATTRIBUTE9 IN VARCHAR2,
306 X_ATTRIBUTE10 IN VARCHAR2,
307 X_ATTRIBUTE11 IN VARCHAR2,
308 X_ATTRIBUTE12 IN VARCHAR2,
309 X_ATTRIBUTE13 IN VARCHAR2,
310 X_ATTRIBUTE14 IN VARCHAR2,
311 X_ATTRIBUTE15 IN VARCHAR2,
312 X_REQUEST_ID IN NUMBER,
313 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
314 X_INSTRUCTION IN VARCHAR2,
315 X_CREATION_DATE IN DATE,
316 X_CREATED_BY IN NUMBER,
317 X_LAST_UPDATE_DATE IN DATE,
318 X_LAST_UPDATED_BY IN NUMBER,
319 X_LAST_UPDATE_LOGIN IN NUMBER,
320 X_PROGRAM_ID IN NUMBER,
321 X_PROGRAM_APPLICATION_ID IN NUMBER,
322 X_PROGRAM_UPDATE_DATE IN DATE,
323 X_ASSIGNMENT_CODE IN VARCHAR2
324 )
325 IS
326
327 CURSOR C IS select ROWID from ENG_CHANGE_ROUTE_STEPS
328 where STEP_ID = X_STEP_ID
329 ;
330
331 BEGIN
332
333 insert into ENG_CHANGE_ROUTE_STEPS (
334 STEP_ID,
335 ROUTE_ID,
336 STEP_SEQ_NUM,
337 ADHOC_STEP_FLAG,
338 WF_ITEM_TYPE,
339 WF_ITEM_KEY,
340 WF_PROCESS_NAME,
341 CONDITION_TYPE_CODE,
342 TIMEOUT_OPTION,
343 STEP_STATUS_CODE,
344 STEP_START_DATE,
345 STEP_END_DATE,
346 REQUIRED_RELATIVE_DAYS,
347 REQUIRED_DATE,
348 ATTRIBUTE_CATEGORY,
349 ATTRIBUTE1,
350 ATTRIBUTE2,
351 ATTRIBUTE3,
352 ATTRIBUTE4,
353 ATTRIBUTE5,
354 ATTRIBUTE6,
355 ATTRIBUTE7,
356 ATTRIBUTE8,
357 ATTRIBUTE9,
358 ATTRIBUTE10,
359 ATTRIBUTE11,
360 ATTRIBUTE12,
361 ATTRIBUTE13,
362 ATTRIBUTE14,
363 ATTRIBUTE15,
364 REQUEST_ID,
365 ORIGINAL_SYSTEM_REFERENCE,
366 CREATION_DATE,
367 CREATED_BY,
368 LAST_UPDATE_DATE,
369 LAST_UPDATED_BY,
370 LAST_UPDATE_LOGIN,
371 PROGRAM_ID,
372 PROGRAM_APPLICATION_ID,
373 PROGRAM_UPDATE_DATE,
374 ASSIGNMENT_CODE
375 ) values (
376 X_STEP_ID,
377 X_ROUTE_ID,
378 X_STEP_SEQ_NUM,
379 X_ADHOC_STEP_FLAG,
380 X_WF_ITEM_TYPE,
381 X_WF_ITEM_KEY,
382 X_WF_PROCESS_NAME,
383 X_CONDITION_TYPE_CODE,
384 X_TIMEOUT_OPTION,
385 X_STEP_STATUS_CODE,
386 X_STEP_START_DATE,
387 X_STEP_END_DATE,
388 X_REQUIRED_RELATIVE_DAYS,
389 X_REQUIRED_DATE,
390 X_ATTRIBUTE_CATEGORY,
391 X_ATTRIBUTE1,
392 X_ATTRIBUTE2,
393 X_ATTRIBUTE3,
394 X_ATTRIBUTE4,
395 X_ATTRIBUTE5,
396 X_ATTRIBUTE6,
397 X_ATTRIBUTE7,
398 X_ATTRIBUTE8,
399 X_ATTRIBUTE9,
400 X_ATTRIBUTE10,
401 X_ATTRIBUTE11,
402 X_ATTRIBUTE12,
403 X_ATTRIBUTE13,
404 X_ATTRIBUTE14,
405 X_ATTRIBUTE15,
406 X_REQUEST_ID,
407 X_ORIGINAL_SYSTEM_REFERENCE,
408 X_CREATION_DATE,
409 X_CREATED_BY,
410 X_LAST_UPDATE_DATE,
411 X_LAST_UPDATED_BY,
412 X_LAST_UPDATE_LOGIN,
413 X_PROGRAM_ID,
414 X_PROGRAM_APPLICATION_ID,
415 X_PROGRAM_UPDATE_DATE,
416 X_ASSIGNMENT_CODE
417 );
418
419 insert into ENG_CHANGE_ROUTE_STEPS_TL (
420 STEP_ID,
421 CREATION_DATE,
422 CREATED_BY,
423 LAST_UPDATE_DATE,
424 LAST_UPDATED_BY,
425 LAST_UPDATE_LOGIN,
426 INSTRUCTION,
427 LANGUAGE,
428 SOURCE_LANG
429 ) select
430 X_STEP_ID,
431 X_CREATION_DATE,
432 X_CREATED_BY,
433 X_LAST_UPDATE_DATE,
434 X_LAST_UPDATED_BY,
435 X_LAST_UPDATE_LOGIN,
436 X_INSTRUCTION,
437 L.LANGUAGE_CODE,
438 userenv('LANG')
439 from FND_LANGUAGES L
440 where L.INSTALLED_FLAG in ('I', 'B')
441 and not exists
442 (select NULL
443 from ENG_CHANGE_ROUTE_STEPS_TL T
444 where T.STEP_ID = X_STEP_ID
445 and T.LANGUAGE = L.LANGUAGE_CODE);
446
447 open c;
448 fetch c into X_ROWID;
449 if (c%notfound) then
450 close c;
451 raise no_data_found;
452 end if;
453 close c;
454
455 END INSERT_ROW ;
456
457 PROCEDURE LOCK_ROW (
458 X_STEP_ID IN NUMBER,
459 X_ROUTE_ID IN NUMBER,
460 X_STEP_SEQ_NUM IN NUMBER,
461 X_ADHOC_STEP_FLAG IN VARCHAR2,
462 X_WF_ITEM_TYPE IN VARCHAR2,
463 X_WF_ITEM_KEY IN VARCHAR2,
464 X_WF_PROCESS_NAME IN VARCHAR2,
465 X_CONDITION_TYPE_CODE IN VARCHAR2,
466 X_TIMEOUT_OPTION IN VARCHAR2,
467 X_STEP_STATUS_CODE IN VARCHAR2,
468 X_STEP_START_DATE IN DATE,
469 X_STEP_END_DATE IN DATE,
470 X_REQUIRED_RELATIVE_DAYS IN NUMBER,
471 X_REQUIRED_DATE IN DATE,
472 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
473 X_ATTRIBUTE1 IN VARCHAR2,
474 X_ATTRIBUTE2 IN VARCHAR2,
475 X_ATTRIBUTE3 IN VARCHAR2,
476 X_ATTRIBUTE4 IN VARCHAR2,
477 X_ATTRIBUTE5 IN VARCHAR2,
478 X_ATTRIBUTE6 IN VARCHAR2,
479 X_ATTRIBUTE7 IN VARCHAR2,
480 X_ATTRIBUTE8 IN VARCHAR2,
481 X_ATTRIBUTE9 IN VARCHAR2,
482 X_ATTRIBUTE10 IN VARCHAR2,
483 X_ATTRIBUTE11 IN VARCHAR2,
484 X_ATTRIBUTE12 IN VARCHAR2,
485 X_ATTRIBUTE13 IN VARCHAR2,
486 X_ATTRIBUTE14 IN VARCHAR2,
487 X_ATTRIBUTE15 IN VARCHAR2,
488 X_REQUEST_ID IN NUMBER,
489 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
490 X_INSTRUCTION IN VARCHAR2,
491 X_PROGRAM_ID IN NUMBER,
492 X_PROGRAM_APPLICATION_ID IN NUMBER,
493 X_PROGRAM_UPDATE_DATE IN DATE,
494 X_ASSIGNMENT_CODE IN VARCHAR2
495 )
496 IS
497
498 cursor c is select
499 ROUTE_ID,
500 STEP_SEQ_NUM,
501 ADHOC_STEP_FLAG,
502 WF_ITEM_TYPE,
503 WF_ITEM_KEY,
504 WF_PROCESS_NAME,
505 CONDITION_TYPE_CODE,
506 TIMEOUT_OPTION,
507 STEP_STATUS_CODE,
508 STEP_START_DATE,
509 STEP_END_DATE,
510 REQUIRED_RELATIVE_DAYS,
511 REQUIRED_DATE,
512 ATTRIBUTE_CATEGORY,
513 ATTRIBUTE1,
514 ATTRIBUTE2,
515 ATTRIBUTE3,
516 ATTRIBUTE4,
517 ATTRIBUTE5,
518 ATTRIBUTE6,
519 ATTRIBUTE7,
520 ATTRIBUTE8,
521 ATTRIBUTE9,
522 ATTRIBUTE10,
523 ATTRIBUTE11,
524 ATTRIBUTE12,
525 ATTRIBUTE13,
526 ATTRIBUTE14,
527 ATTRIBUTE15,
528 REQUEST_ID,
529 ORIGINAL_SYSTEM_REFERENCE,
530 PROGRAM_ID,
531 PROGRAM_APPLICATION_ID,
532 PROGRAM_UPDATE_DATE,
533 ASSIGNMENT_CODE
534 from ENG_CHANGE_ROUTE_STEPS
535 where STEP_ID = X_STEP_ID
536 for update of STEP_ID nowait;
537 recinfo c%rowtype;
538
539 cursor c1 is select
540 INSTRUCTION,
541 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
542 from ENG_CHANGE_ROUTE_STEPS_TL
543 where STEP_ID = X_STEP_ID
544 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
545 for update of STEP_ID nowait;
546
547 BEGIN
548
549 open c;
550 fetch c into recinfo;
551 if (c%notfound) then
552 close c;
553 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
554 app_exception.raise_exception;
555 end if;
556 close c;
557 if ( (recinfo.ROUTE_ID = X_ROUTE_ID)
558 AND (recinfo.STEP_SEQ_NUM = X_STEP_SEQ_NUM)
559 AND (recinfo.ADHOC_STEP_FLAG = X_ADHOC_STEP_FLAG)
560 AND ((recinfo.WF_ITEM_TYPE = X_WF_ITEM_TYPE)
561 OR ((recinfo.WF_ITEM_TYPE is null) AND (X_WF_ITEM_TYPE is null)))
562 AND ((recinfo.WF_ITEM_KEY = X_WF_ITEM_KEY)
563 OR ((recinfo.WF_ITEM_KEY is null) AND (X_WF_ITEM_KEY is null)))
564 AND ((recinfo.WF_PROCESS_NAME = X_WF_PROCESS_NAME)
565 OR ((recinfo.WF_PROCESS_NAME is null) AND (X_WF_PROCESS_NAME is null)))
566 AND (recinfo.CONDITION_TYPE_CODE = X_CONDITION_TYPE_CODE)
567 AND ((recinfo.TIMEOUT_OPTION = X_TIMEOUT_OPTION)
568 OR ((recinfo.TIMEOUT_OPTION is null) AND (X_TIMEOUT_OPTION is null)))
569 AND ((recinfo.STEP_STATUS_CODE = X_STEP_STATUS_CODE)
570 OR ((recinfo.STEP_STATUS_CODE is null) AND (X_STEP_STATUS_CODE is null)))
571 AND ((recinfo.STEP_START_DATE = X_STEP_START_DATE)
572 OR ((recinfo.STEP_START_DATE is null) AND (X_STEP_START_DATE is null)))
573 AND ((recinfo.STEP_END_DATE = X_STEP_END_DATE)
574 OR ((recinfo.STEP_END_DATE is null) AND (X_STEP_END_DATE is null)))
575 AND ((recinfo.REQUIRED_RELATIVE_DAYS = X_REQUIRED_RELATIVE_DAYS)
576 OR ((recinfo.REQUIRED_RELATIVE_DAYS is null) AND (X_REQUIRED_RELATIVE_DAYS is null)))
577 AND ((recinfo.REQUIRED_DATE = X_REQUIRED_DATE)
578 OR ((recinfo.REQUIRED_DATE is null) AND (X_REQUIRED_DATE is null)))
579 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
580 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
581 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
582 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
583 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
584 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
585 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
586 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
587 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
588 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
589 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
590 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
591 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
592 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
593 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
594 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
595 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
596 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
597 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
598 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
599 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
600 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
601 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
602 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
603 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
604 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
605 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
606 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
607 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
608 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
609 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
610 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
611 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
612 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
613 AND ((recinfo.ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE)
614 OR ((recinfo.ORIGINAL_SYSTEM_REFERENCE is null) AND (X_ORIGINAL_SYSTEM_REFERENCE is null)))
615 AND ((recinfo.ASSIGNMENT_CODE = X_ASSIGNMENT_CODE)
616 OR ((recinfo.ASSIGNMENT_CODE is null) AND (X_ASSIGNMENT_CODE is null)))
617 -- followings are not generated by tool
618 -- AND ((recinfo.PROGRAM_ID= X_PROGRAM_ID)
619 -- OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
620 -- AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
621 -- OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
622 -- AND ((recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE)
623 -- OR ((recinfo.PROGRAM_UPDATE_DATE is null) AND (X_PROGRAM_UPDATE_DATE is null)))
624 ) then
625 null;
626 else
627 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
628 app_exception.raise_exception;
629 end if;
630
631 for tlinfo in c1 loop
632 if (tlinfo.BASELANG = 'Y') then
633 if ( ((tlinfo.INSTRUCTION = X_INSTRUCTION)
634 OR ((tlinfo.INSTRUCTION is null) AND (X_INSTRUCTION is null)))
635 ) then
636 null;
637 else
638 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
639 app_exception.raise_exception;
640 end if;
641 end if;
642 end loop;
643
644 RETURN;
645
646
647 END LOCK_ROW;
648
649
650 PROCEDURE UPDATE_ROW (
651 X_STEP_ID IN NUMBER,
652 X_ROUTE_ID IN NUMBER,
653 X_STEP_SEQ_NUM IN NUMBER,
654 X_ADHOC_STEP_FLAG IN VARCHAR2,
655 X_WF_ITEM_TYPE IN VARCHAR2,
656 X_WF_ITEM_KEY IN VARCHAR2,
657 X_WF_PROCESS_NAME IN VARCHAR2,
658 X_CONDITION_TYPE_CODE IN VARCHAR2,
659 X_TIMEOUT_OPTION IN VARCHAR2,
660 X_STEP_STATUS_CODE IN VARCHAR2,
661 X_STEP_START_DATE IN DATE,
662 X_STEP_END_DATE IN DATE,
663 X_REQUIRED_RELATIVE_DAYS IN NUMBER,
664 X_REQUIRED_DATE IN DATE,
665 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
666 X_ATTRIBUTE1 IN VARCHAR2,
667 X_ATTRIBUTE2 IN VARCHAR2,
668 X_ATTRIBUTE3 IN VARCHAR2,
669 X_ATTRIBUTE4 IN VARCHAR2,
670 X_ATTRIBUTE5 IN VARCHAR2,
671 X_ATTRIBUTE6 IN VARCHAR2,
672 X_ATTRIBUTE7 IN VARCHAR2,
673 X_ATTRIBUTE8 IN VARCHAR2,
674 X_ATTRIBUTE9 IN VARCHAR2,
675 X_ATTRIBUTE10 IN VARCHAR2,
676 X_ATTRIBUTE11 IN VARCHAR2,
677 X_ATTRIBUTE12 IN VARCHAR2,
678 X_ATTRIBUTE13 IN VARCHAR2,
679 X_ATTRIBUTE14 IN VARCHAR2,
680 X_ATTRIBUTE15 IN VARCHAR2,
681 X_REQUEST_ID IN NUMBER,
682 X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
683 X_INSTRUCTION IN VARCHAR2,
684 X_LAST_UPDATE_DATE IN DATE,
685 X_LAST_UPDATED_BY IN NUMBER,
686 X_LAST_UPDATE_LOGIN IN NUMBER,
687 X_PROGRAM_ID IN NUMBER,
688 X_PROGRAM_APPLICATION_ID IN NUMBER,
689 X_PROGRAM_UPDATE_DATE IN DATE,
690 X_ASSIGNMENT_CODE IN VARCHAR2
691 )
692 IS
693
694 BEGIN
695
696 update ENG_CHANGE_ROUTE_STEPS set
697 ROUTE_ID = X_ROUTE_ID,
698 STEP_SEQ_NUM = X_STEP_SEQ_NUM,
699 ADHOC_STEP_FLAG = X_ADHOC_STEP_FLAG,
700 WF_ITEM_TYPE = X_WF_ITEM_TYPE,
701 WF_ITEM_KEY = X_WF_ITEM_KEY,
702 WF_PROCESS_NAME = X_WF_PROCESS_NAME,
703 CONDITION_TYPE_CODE = X_CONDITION_TYPE_CODE,
704 TIMEOUT_OPTION = X_TIMEOUT_OPTION,
705 STEP_STATUS_CODE = X_STEP_STATUS_CODE,
706 STEP_START_DATE = X_STEP_START_DATE,
707 STEP_END_DATE = X_STEP_END_DATE,
708 REQUIRED_RELATIVE_DAYS = X_REQUIRED_RELATIVE_DAYS,
709 REQUIRED_DATE = X_REQUIRED_DATE,
710 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
711 ATTRIBUTE1 = X_ATTRIBUTE1,
712 ATTRIBUTE2 = X_ATTRIBUTE2,
713 ATTRIBUTE3 = X_ATTRIBUTE3,
714 ATTRIBUTE4 = X_ATTRIBUTE4,
715 ATTRIBUTE5 = X_ATTRIBUTE5,
716 ATTRIBUTE6 = X_ATTRIBUTE6,
717 ATTRIBUTE7 = X_ATTRIBUTE7,
718 ATTRIBUTE8 = X_ATTRIBUTE8,
719 ATTRIBUTE9 = X_ATTRIBUTE9,
720 ATTRIBUTE10 = X_ATTRIBUTE10,
721 ATTRIBUTE11 = X_ATTRIBUTE11,
722 ATTRIBUTE12 = X_ATTRIBUTE12,
723 ATTRIBUTE13 = X_ATTRIBUTE13,
724 ATTRIBUTE14 = X_ATTRIBUTE14,
725 ATTRIBUTE15 = X_ATTRIBUTE15,
726 REQUEST_ID = X_REQUEST_ID,
727 ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE,
728 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
729 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
730 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
731 PROGRAM_ID = X_PROGRAM_ID,
732 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
733 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
734 ASSIGNMENT_CODE = X_ASSIGNMENT_CODE
735 where STEP_ID = X_STEP_ID;
736
737 if (sql%notfound) then
738 raise no_data_found;
739 end if;
740
741 update ENG_CHANGE_ROUTE_STEPS_TL set
742 INSTRUCTION = X_INSTRUCTION,
743 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
744 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
745 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
746 SOURCE_LANG = userenv('LANG')
747 where STEP_ID = X_STEP_ID
748 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
749
750 if (sql%notfound) then
751 raise no_data_found;
752 end if;
753
754
755 END UPDATE_ROW ;
756
757 PROCEDURE DELETE_ROW (
758 X_STEP_ID IN NUMBER
759 )
760 IS
761
762 BEGIN
763
764 delete from ENG_CHANGE_ROUTE_STEPS_TL
765 where STEP_ID = X_STEP_ID;
766
767 if (sql%notfound) then
768 raise no_data_found;
769 end if;
770
771 delete from ENG_CHANGE_ROUTE_STEPS
772 where STEP_ID = X_STEP_ID;
773
774 if (sql%notfound) then
775 raise no_data_found;
776 end if;
777
778
779 END DELETE_ROW ;
780
781
782 PROCEDURE ADD_LANGUAGE
783 IS
784
785 BEGIN
786
787 delete from ENG_CHANGE_ROUTE_STEPS_TL T
788 where not exists
789 (select NULL
790 from ENG_CHANGE_ROUTE_STEPS B
791 where B.STEP_ID = T.STEP_ID
792 );
793
794 update ENG_CHANGE_ROUTE_STEPS_TL T set (
795 INSTRUCTION
796 ) = (select
797 B.INSTRUCTION
798 from ENG_CHANGE_ROUTE_STEPS_TL B
799 where B.STEP_ID = T.STEP_ID
800 and B.LANGUAGE = T.SOURCE_LANG)
801 where (
802 T.STEP_ID,
803 T.LANGUAGE
804 ) in (select
805 SUBT.STEP_ID,
806 SUBT.LANGUAGE
807 from ENG_CHANGE_ROUTE_STEPS_TL SUBB, ENG_CHANGE_ROUTE_STEPS_TL SUBT
808 where SUBB.STEP_ID = SUBT.STEP_ID
809 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
810 and (SUBB.INSTRUCTION <> SUBT.INSTRUCTION
811 or (SUBB.INSTRUCTION is null and SUBT.INSTRUCTION is not null)
812 or (SUBB.INSTRUCTION is not null and SUBT.INSTRUCTION is null)
813 ));
814
815
816 insert into ENG_CHANGE_ROUTE_STEPS_TL (
817 STEP_ID,
818 CREATION_DATE,
819 CREATED_BY,
820 LAST_UPDATE_DATE,
821 LAST_UPDATED_BY,
822 LAST_UPDATE_LOGIN,
823 INSTRUCTION,
824 LANGUAGE,
825 SOURCE_LANG
826 ) select
827 B.STEP_ID,
828 B.CREATION_DATE,
829 B.CREATED_BY,
830 B.LAST_UPDATE_DATE,
831 B.LAST_UPDATED_BY,
832 B.LAST_UPDATE_LOGIN,
833 B.INSTRUCTION,
834 L.LANGUAGE_CODE,
835 B.SOURCE_LANG
836 from ENG_CHANGE_ROUTE_STEPS_TL B, FND_LANGUAGES L
837 where L.INSTALLED_FLAG in ('I', 'B')
838 and B.LANGUAGE = userenv('LANG')
839 and not exists
840 (select NULL
841 from ENG_CHANGE_ROUTE_STEPS_TL T
842 where T.STEP_ID = B.STEP_ID
843 and T.LANGUAGE = L.LANGUAGE_CODE);
844
845
846 END ADD_LANGUAGE ;
847
848
849 /********************************************************************
850 * API Type : Public APIs
851 * Purpose : Those APIs are public
852 *********************************************************************/
853
854
855 END Eng_Change_Route_Step_Util ;