[Home] [Help]
PACKAGE BODY: APPS.IGS_TR_STEP_PKG
Source
1 PACKAGE BODY igs_tr_step_pkg AS
2 /* $Header: IGSTI03B.pls 115.10 2003/02/19 12:44:07 kpadiyar ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_tr_step%ROWTYPE;
5 new_references igs_tr_step%ROWTYPE;
6
7 PROCEDURE set_column_values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_tracking_id IN NUMBER DEFAULT NULL,
11 x_tracking_step_id IN NUMBER DEFAULT NULL,
12 x_tracking_step_number IN NUMBER DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_completion_dt IN DATE DEFAULT NULL,
15 x_action_days IN NUMBER DEFAULT NULL,
16 x_step_completion_ind IN VARCHAR2 DEFAULT NULL,
17 x_by_pass_ind IN VARCHAR2 DEFAULT NULL,
18 x_recipient_id IN NUMBER DEFAULT NULL,
19 x_s_tracking_step_type IN VARCHAR2 DEFAULT NULL,
20 x_step_group_id IN NUMBER DEFAULT NULL,
21 x_publish_ind IN VARCHAR2 DEFAULT NULL,
22 x_step_catalog_cd IN VARCHAR2 DEFAULT NULL,
23 x_creation_date IN DATE DEFAULT NULL,
24 x_created_by IN NUMBER DEFAULT NULL,
25 x_last_update_date IN DATE DEFAULT NULL,
26 x_last_updated_by IN NUMBER DEFAULT NULL,
27 x_last_update_login IN NUMBER DEFAULT NULL
28 ) AS
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM igs_tr_step
33 WHERE ROWID = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 OPEN cur_old_ref_values;
42 FETCH cur_old_ref_values INTO old_references;
43 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
44 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
45 igs_ge_msg_stack.add;
46 app_exception.raise_exception;
47 CLOSE cur_old_ref_values;
48 RETURN;
49 END IF;
50 CLOSE cur_old_ref_values;
51
52 old_references.completion_dt := TRUNC(old_references.completion_dt);
53
54 -- Populate New Values.
55 new_references.tracking_id := x_tracking_id;
56 new_references.tracking_step_id := x_tracking_step_id;
57 new_references.tracking_step_number := x_tracking_step_number;
58 new_references.description := x_description;
59 new_references.completion_dt := TRUNC(x_completion_dt);
60 new_references.action_days := x_action_days;
61 new_references.step_completion_ind := x_step_completion_ind;
62 new_references.by_pass_ind := x_by_pass_ind;
63 new_references.recipient_id := x_recipient_id;
64 new_references.s_tracking_step_type := x_s_tracking_step_type;
65 new_references.step_group_id := x_step_group_id;
66 new_references.publish_ind :=x_publish_ind;
67 new_references.step_catalog_cd := x_step_catalog_cd;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76 new_references.last_update_date := x_last_update_date;
77 new_references.last_updated_by := x_last_updated_by;
78 new_references.last_update_login := x_last_update_login;
79
80 END set_column_values;
81
82 PROCEDURE BeforeRowInsertUpdate(
83 p_inserting IN BOOLEAN DEFAULT FALSE,
84 p_updating IN BOOLEAN DEFAULT FALSE
85 ) as
86 v_message_name VARCHAR2(30);
87 BEGIN
88 IF (p_inserting OR (p_updating AND (old_references.step_catalog_cd <> new_references.step_catalog_cd))) THEN
89 IF NOT IGS_TR_VAL_TRI.val_tr_step_ctlg (new_references.step_catalog_cd,
90 v_message_name) THEN
91 Fnd_Message.Set_Name('IGS', v_message_name);
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception;
94 END IF;
95 END IF;
96 END BeforeRowInsertUpdate;
97
98 PROCEDURE beforerowinsertupdatedelete1(
99 p_inserting IN BOOLEAN DEFAULT FALSE,
100 p_updating IN BOOLEAN DEFAULT FALSE,
101 p_deleting IN BOOLEAN DEFAULT FALSE
102 ) AS
103
104 v_message_name VARCHAR2(30);
105 v_tracking_id igs_tr_step.tracking_id%TYPE;
106 v_tracking_type igs_tr_item.tracking_type%TYPE;
107
108 CURSOR c_tri (cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
109 SELECT tracking_type
110 FROM igs_tr_item
111 WHERE tracking_id = cp_tracking_id;
112
113 BEGIN
114
115 -- Validate the completion date, step completion indicator and by pass
116 -- indicator.
117 IF (p_inserting OR
118 (p_updating AND ((old_references.step_completion_ind <> new_references.step_completion_ind) OR
119 (old_references.by_pass_ind <> new_references.by_pass_ind) OR
120 (NVL(old_references.completion_dt, igs_ge_date.igsdate ('1900/01/01')) <>
121 NVL(new_references.completion_dt, igs_ge_date.igsdate ('1900/01/01')))))) THEN
122
123 IF igs_tr_val_trst.trkp_val_trst_sci_cd(
124 new_references.step_completion_ind,
125 new_references.completion_dt,
126 new_references.by_pass_ind,
127 v_message_name) = FALSE THEN
128
129 fnd_message.set_name('IGS',v_message_name);
130 igs_ge_msg_stack.add;
131 app_exception.raise_exception;
132 END IF;
133 END IF;
134
135 -- Validate the tracking step type.
136 IF (p_inserting OR
137 (p_updating AND (NVL(old_references.s_tracking_step_type, 'NULL') <>
138 NVL(new_references.s_tracking_step_type, 'NULL')))) AND
139 new_references.s_tracking_step_type IS NOT NULL THEN
140
141 OPEN c_tri (new_references.tracking_id);
142 FETCH c_tri
143 INTO v_tracking_type;
144 CLOSE c_tri;
145
146 IF igs_tr_val_trst.trkp_val_stst_stt(
147 new_references.s_tracking_step_type,
148 v_tracking_type,
149 v_message_name) = FALSE THEN
150
151 fnd_message.set_name('IGS',v_message_name);
152 igs_ge_msg_stack.add;
153 app_exception.raise_exception;
154 END IF;
155 END IF;
156
157 END beforerowinsertupdatedelete1;
158
159
160 PROCEDURE check_parent_existance AS
161 BEGIN
162
163 IF (((old_references.recipient_id = new_references.recipient_id)) OR
164 ((new_references.recipient_id IS NULL))) THEN
165 NULL;
166 ELSE
167 IF NOT igs_pe_person_pkg.get_pk_for_validation ( new_references.recipient_id )THEN
168 fnd_message.set_name('FND','FORM_RECORD_DELETED');
169 igs_ge_msg_stack.add;
170 app_exception.raise_exception;
171 END IF;
172 END IF;
173
174 IF (((old_references.s_tracking_step_type = new_references.s_tracking_step_type)) OR
175 ((new_references.s_tracking_step_type IS NULL))) THEN
176 NULL;
177 ELSE
178 IF NOT igs_lookups_view_pkg.get_pk_for_validation('TRACKING_STEP_TYPE',new_references.s_tracking_step_type)THEN
179 fnd_message.set_name('FND','FORM_RECORD_DELETED');
180 igs_ge_msg_stack.add;
181 app_exception.raise_exception;
182 END IF;
183 END IF;
184
185 IF (((old_references.tracking_id = new_references.tracking_id)) OR ((new_references.tracking_id IS NULL))) THEN
186 NULL;
187 ELSE
188 IF NOT igs_tr_item_pkg.get_pk_for_validation ( new_references.tracking_id )THEN
189 fnd_message.set_name('FND','FORM_RECORD_DELETED');
190 igs_ge_msg_stack.add;
191 app_exception.raise_exception;
192 END IF;
193 END IF;
194
195 IF (((old_references.step_catalog_cd = new_references.step_catalog_cd)) OR ((new_references.step_catalog_cd IS NULL))) THEN
196 NULL;
197 ELSE
198 IF NOT igs_tr_step_ctlg_pkg.get_uk_for_validation ( new_references.step_catalog_cd )THEN
199 fnd_message.set_name('FND','FORM_RECORD_DELETED');
200 igs_ge_msg_stack.add;
201 app_exception.raise_exception;
202 END IF;
203 END IF;
204 END check_parent_existance;
205
206 PROCEDURE check_child_existance AS
207 BEGIN
208
209 igs_tr_step_note_pkg.get_fk_igs_tr_step (
210 old_references.tracking_id,
211 old_references.tracking_step_id
212 );
213
214 END check_child_existance;
215
216 FUNCTION get_pk_for_validation (
217 x_tracking_id IN NUMBER,
218 x_tracking_step_id IN NUMBER)
219 RETURN BOOLEAN AS
220
221 CURSOR cur_rowid IS
222 SELECT ROWID
223 FROM igs_tr_step
224 WHERE tracking_id = x_tracking_id
225 AND tracking_step_id = x_tracking_step_id
226 FOR UPDATE NOWAIT;
227
228 lv_rowid cur_rowid%ROWTYPE;
229
230 BEGIN
231
232 OPEN cur_rowid;
233 FETCH cur_rowid INTO lv_rowid;
234 IF (cur_rowid%FOUND) THEN
235 CLOSE cur_rowid;
236 RETURN TRUE;
237 ELSE
238 CLOSE cur_rowid;
239 RETURN FALSE;
240 END IF;
241
242 END get_pk_for_validation;
243
244 PROCEDURE get_fk_igs_pe_person (
245 x_person_id IN NUMBER
246 ) AS
247
248 CURSOR cur_rowid IS
249 SELECT ROWID
250 FROM igs_tr_step
251 WHERE recipient_id = x_person_id ;
252
253 lv_rowid cur_rowid%ROWTYPE;
254
255 BEGIN
256
257 OPEN cur_rowid;
258 FETCH cur_rowid INTO lv_rowid;
259 IF (cur_rowid%FOUND) THEN
260 CLOSE cur_rowid;
261 fnd_message.set_name ('IGS', 'IGS_TR_TRST_PE_RECIPIENT_FK');
262 igs_ge_msg_stack.add;
263 app_exception.raise_exception;
264 RETURN;
265 END IF;
266 CLOSE cur_rowid;
267
268 END get_fk_igs_pe_person;
269
270 PROCEDURE get_fk_igs_tr_item (
271 x_tracking_id IN NUMBER
272 ) AS
273
274 CURSOR cur_rowid IS
275 SELECT ROWID
276 FROM igs_tr_step
277 WHERE tracking_id = x_tracking_id ;
278
279 lv_rowid cur_rowid%ROWTYPE;
280
281 BEGIN
282
283 OPEN cur_rowid;
284 FETCH cur_rowid INTO lv_rowid;
285 IF (cur_rowid%FOUND) THEN
286 CLOSE cur_rowid;
287 fnd_message.set_name ('IGS', 'IGS_TR_TRST_TRI_FK');
288 igs_ge_msg_stack.add;
289 app_exception.raise_exception;
290 RETURN;
291 END IF;
292 CLOSE cur_rowid;
293
294 END get_fk_igs_tr_item;
295
296
297 PROCEDURE get_fk_igs_lookups_view(
298 x_s_tracking_step_type IN VARCHAR2
299 ) AS
300
301 CURSOR cur_rowid IS
302 SELECT ROWID
303 FROM igs_tr_step
304 WHERE s_tracking_step_type = x_s_tracking_step_type ;
305
306 lv_rowid cur_rowid%ROWTYPE;
307
308 BEGIN
309
310 OPEN cur_rowid;
311 FETCH cur_rowid INTO lv_rowid;
312 IF (cur_rowid%FOUND) THEN
313 CLOSE cur_rowid;
314 fnd_message.set_name ('IGS', 'IGS_TR_TRST_STST_FK');
315 igs_ge_msg_stack.add;
316 app_exception.raise_exception;
317 RETURN;
318 END IF;
319 CLOSE cur_rowid;
320
321 END get_fk_igs_lookups_view;
322
323 -- procedure to check constraints
324 PROCEDURE check_constraints(
325 column_name IN VARCHAR2 DEFAULT NULL,
326 column_value IN VARCHAR2 DEFAULT NULL
327 ) AS
328 BEGIN
329
330 IF column_name IS NULL THEN
331 NULL;
332 ELSIF UPPER(column_name) = 'BY_PASS_IND' THEN
333 new_references.by_pass_ind := column_value;
334 ELSIF UPPER(column_name) = 'STEP_COMPLETION_IND' THEN
335 new_references.step_completion_ind := column_value;
336 ELSIF UPPER(column_name) = 'S_TRACKING_STEP_TYPE' THEN
337 new_references.s_tracking_step_type := column_value;
338 ELSIF UPPER(column_name) = 'TRACKING_STEP_NUMBER' THEN
339 new_references.tracking_step_number := igs_ge_number.to_num(column_value);
340 ELSIF UPPER(column_name) = 'ACTION_DAYS' THEN
341 new_references.action_days := igs_ge_number.to_num(column_value);
342 ELSIF UPPER(column_name) = 'STEP_COMPLETION_IND' THEN
343 new_references.step_completion_ind := column_value;
344 ELSIF UPPER(column_name) = 'PUBLISH_IND' THEN
345 new_references.publish_ind := column_value;
346 END IF;
347
348 IF UPPER(column_name) = 'TRACKING_STEP_NUMBER' OR column_name IS NULL THEN
349 IF (new_references.tracking_step_number < 1 OR new_references.tracking_step_number > 99 )THEN
350 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
351 igs_ge_msg_stack.add;
352 app_exception.raise_exception;
353 END IF;
354 END IF;
355
356 --kumma, 2702342, increased the constant value from 999 to 9999
357 IF UPPER(column_name) = 'ACTION_DAYS' OR column_name IS NULL THEN
358 IF (new_references.action_days < 0 OR new_references.action_days > 9999 )THEN
359 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
360 igs_ge_msg_stack.add;
361 app_exception.raise_exception;
362 END IF;
363 END IF;
364
365 IF UPPER(column_name) = 'STEP_COMPLETION_IND' OR column_name IS NULL THEN
366 IF new_references.step_completion_ind NOT IN ('Y','N') THEN
367 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
368 igs_ge_msg_stack.add;
369 app_exception.raise_exception;
370 END IF;
371 END IF;
372
373 IF UPPER(column_name) = 'BY_PASS_IND' OR column_name IS NULL THEN
374 IF new_references.by_pass_ind NOT IN ('Y','N') THEN
375 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
376 igs_ge_msg_stack.add;
377 app_exception.raise_exception;
378 END IF;
379 END IF;
380
381 IF UPPER(column_name) = 'BY_PASS_IND' OR column_name IS NULL THEN
382 IF new_references.by_pass_ind <> UPPER(new_references.by_pass_ind) THEN
383 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
384 igs_ge_msg_stack.add;
385 app_exception.raise_exception;
386 END IF;
387 END IF;
388
389 IF UPPER(column_name) = 'STEP_COMPLETION_IND' OR column_name IS NULL THEN
390 IF new_references.step_completion_ind <> UPPER(new_references.step_completion_ind) THEN
391 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
392 igs_ge_msg_stack.add;
393 app_exception.raise_exception;
394 END IF;
395 END IF;
396
397 IF UPPER(column_name) = 'S_TRACKING_STEP_TYPE' OR column_name IS NULL THEN
398 IF new_references.s_tracking_step_type <> UPPER(new_references.s_tracking_step_type) THEN
399 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
400 igs_ge_msg_stack.add;
401 app_exception.raise_exception;
402 END IF;
403 END IF;
404
405 IF UPPER(column_name) = 'PUBLISH_IND' OR column_name IS NULL THEN
406 IF new_references.publish_ind <> UPPER(new_references.publish_ind) THEN
407 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
408 igs_ge_msg_stack.add;
409 app_exception.raise_exception;
410 END IF;
411 END IF;
412
413 IF UPPER(column_name) = 'PUBLISH_IND' OR column_name IS NULL THEN
414 IF new_references.publish_ind NOT IN ('Y','N') THEN
415 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
416 igs_ge_msg_stack.add;
417 app_exception.raise_exception;
418 END IF;
419 END IF;
420
421 END check_constraints;
422
423 PROCEDURE before_dml (
424 p_action IN VARCHAR2,
425 x_rowid IN VARCHAR2 DEFAULT NULL,
426 x_tracking_id IN NUMBER DEFAULT NULL,
427 x_tracking_step_id IN NUMBER DEFAULT NULL,
428 x_tracking_step_number IN NUMBER DEFAULT NULL,
429 x_description IN VARCHAR2 DEFAULT NULL,
430 x_completion_dt IN DATE DEFAULT NULL,
431 x_action_days IN NUMBER DEFAULT NULL,
432 x_step_completion_ind IN VARCHAR2 DEFAULT NULL,
433 x_by_pass_ind IN VARCHAR2 DEFAULT NULL,
434 x_recipient_id IN NUMBER DEFAULT NULL,
435 x_s_tracking_step_type IN VARCHAR2 DEFAULT NULL,
436 x_step_group_id IN NUMBER DEFAULT NULL,
437 x_publish_ind IN VARCHAR2 DEFAULT NULL,
438 x_step_catalog_cd IN VARCHAR2 DEFAULT NULL,
439 x_creation_date IN DATE DEFAULT NULL,
440 x_created_by IN NUMBER DEFAULT NULL,
441 x_last_update_date IN DATE DEFAULT NULL,
442 x_last_updated_by IN NUMBER DEFAULT NULL,
443 x_last_update_login IN NUMBER DEFAULT NULL
444 ) AS
445
446 BEGIN
447
448 set_column_values (
449 p_action,
450 x_rowid,
451 x_tracking_id,
452 x_tracking_step_id,
453 x_tracking_step_number,
454 x_description,
455 x_completion_dt,
456 x_action_days,
457 x_step_completion_ind,
458 x_by_pass_ind,
459 x_recipient_id,
460 x_s_tracking_step_type,
461 x_step_group_id,
462 x_publish_ind,
463 x_step_catalog_cd,
464 x_creation_date,
465 x_created_by,
466 x_last_update_date,
467 x_last_updated_by,
468 x_last_update_login
469 );
470
471 IF (p_action = 'INSERT') THEN
472 -- Call all the procedures related to Before Insert.
473 BeforeRowInsertUpdate ( p_inserting => TRUE );
474 beforerowinsertupdatedelete1 ( p_inserting => TRUE );
475 IF get_pk_for_validation(
476 new_references.tracking_id,
477 new_references.tracking_step_id
478 )THEN
479 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
480 igs_ge_msg_stack.add;
481 app_exception.raise_exception;
482 END IF;
483 check_constraints;
484 check_parent_existance;
485
486 ELSIF (p_action = 'UPDATE') THEN
487 -- Call all the procedures related to Before Update.
488 BeforeRowInsertUpdate ( p_updating => TRUE );
489 beforerowinsertupdatedelete1 ( p_updating => TRUE );
490 check_constraints;
491 check_parent_existance;
492
493 ELSIF (p_action = 'DELETE') THEN
494 -- Call all the procedures related to Before Delete.
495 beforerowinsertupdatedelete1 ( p_deleting => TRUE );
496 check_child_existance;
497
498 ELSIF (p_action = 'VALIDATE_INSERT') THEN
499 -- Call all the procedures related to Before Delete.
500 IF get_pk_for_validation(
501 new_references.tracking_id,
502 new_references.tracking_step_id
503 )THEN
504 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
505 igs_ge_msg_stack.add;
506 app_exception.raise_exception;
507 END IF;
508 check_constraints;
509
510 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
511 -- Call all the procedures related to Before Delete.
512 check_constraints;
513
514 ELSIF (p_action = 'VALIDATE_DELETE') THEN
515 -- Call all the procedures related to Before Delete.
516 check_child_existance;
517
518 END IF;
519
520 END before_dml;
521
522
523 PROCEDURE insert_row (
524 x_rowid IN OUT NOCOPY VARCHAR2,
525 x_tracking_id IN NUMBER,
526 x_tracking_step_id IN NUMBER,
527 x_tracking_step_number IN NUMBER,
528 x_description IN VARCHAR2,
529 x_s_tracking_step_type IN VARCHAR2,
530 x_completion_dt IN DATE,
531 x_action_days IN NUMBER,
532 x_step_completion_ind IN VARCHAR2,
533 x_by_pass_ind IN VARCHAR2,
534 x_recipient_id IN NUMBER,
535 x_step_group_id IN NUMBER DEFAULT NULL,
536 x_publish_ind IN VARCHAR2 DEFAULT 'N',
537 x_step_catalog_cd IN VARCHAR2 DEFAULT NULL,
538 x_mode IN VARCHAR2 DEFAULT 'R'
539 ) AS
540
541 CURSOR c IS
542 SELECT ROWID
543 FROM igs_tr_step
544 WHERE tracking_id = x_tracking_id AND tracking_step_id = x_tracking_step_id;
545
546 x_last_update_date DATE;
547 x_last_updated_by NUMBER;
548 x_last_update_login NUMBER;
549 x_request_id NUMBER;
550 x_program_id NUMBER;
551 x_program_application_id NUMBER;
552 x_program_update_date DATE;
553
554 BEGIN
555
556 x_last_update_date := SYSDATE;
557 IF(x_mode = 'I') THEN
558 x_last_updated_by := 1;
559 x_last_update_login := 0;
560
561 ELSIF (x_mode = 'R') THEN
562
563 x_last_updated_by := fnd_global.user_id;
564 IF x_last_updated_by IS NULL THEN
565 x_last_updated_by := -1;
566 END IF;
567
568 x_last_update_login :=fnd_global.login_id;
569 IF x_last_update_login IS NULL THEN
570 x_last_update_login := -1;
571 END IF;
572 x_request_id := fnd_global.conc_request_id;
573 x_program_id := fnd_global.conc_program_id;
574 x_program_application_id := fnd_global.prog_appl_id;
575
576 IF (x_request_id = -1) THEN
577 x_request_id := NULL;
578 x_program_id := NULL;
579 x_program_application_id := NULL;
580 x_program_update_date := NULL;
581 ELSE
582 x_program_update_date := SYSDATE;
583 END IF;
584
585 ELSE
586 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
587 igs_ge_msg_stack.add;
588 app_exception.raise_exception;
589 END IF;
590
591 before_dml(p_action =>'INSERT',
592 x_rowid =>x_rowid,
593 x_tracking_id => x_tracking_id,
594 x_tracking_step_id => x_tracking_step_id,
595 x_tracking_step_number => x_tracking_step_number,
596 x_description => x_description,
597 x_completion_dt => x_completion_dt,
598 x_action_days => x_action_days,
599 x_step_completion_ind => x_step_completion_ind,
600 x_by_pass_ind => NVL(x_by_pass_ind,'N'),
601 x_recipient_id => x_recipient_id,
602 x_s_tracking_step_type => x_s_tracking_step_type,
603 x_step_group_id => x_step_group_id,
604 x_publish_ind => NVL(x_publish_ind,'N'),
605 x_step_catalog_cd => x_step_catalog_cd,
606 x_creation_date =>x_last_update_date,
607 x_created_by =>x_last_updated_by,
608 x_last_update_date =>x_last_update_date,
609 x_last_updated_by =>x_last_updated_by,
610 x_last_update_login =>x_last_update_login
611 );
612
613 INSERT INTO igs_tr_step (
614 tracking_id,
615 tracking_step_id,
616 tracking_step_number,
617 description,
618 s_tracking_step_type,
619 completion_dt,
620 action_days,
621 step_completion_ind,
622 by_pass_ind,
623 recipient_id,
624 step_group_id,
625 publish_ind,
626 step_catalog_cd,
627 creation_date,
628 created_by,
629 last_update_date,
630 last_updated_by,
631 last_update_login,
632 request_id,
633 program_id,
634 program_application_id,
635 program_update_date
636 ) VALUES (
637 new_references.tracking_id,
638 new_references.tracking_step_id,
639 new_references.tracking_step_number,
640 new_references.description,
641 new_references.s_tracking_step_type,
642 new_references.completion_dt,
643 new_references.action_days,
644 new_references.step_completion_ind,
645 new_references.by_pass_ind,
646 new_references.recipient_id,
647 new_references.step_group_id,
648 new_references.publish_ind,
649 new_references.step_catalog_cd,
650 x_last_update_date,
651 x_last_updated_by,
652 x_last_update_date,
653 x_last_updated_by,
654 x_last_update_login,
655 x_request_id,
656 x_program_id,
657 x_program_application_id,
658 x_program_update_date
659 );
660
661 OPEN c;
662 FETCH c INTO x_rowid;
663 IF (c%NOTFOUND) THEN
664 CLOSE c;
665 RAISE no_data_found;
666 END IF;
667 CLOSE c;
668
669 END insert_row;
670
671 PROCEDURE lock_row (
672 x_rowid IN VARCHAR2,
673 x_tracking_id IN NUMBER,
674 x_tracking_step_id IN NUMBER,
675 x_tracking_step_number IN NUMBER,
676 x_description IN VARCHAR2,
677 x_s_tracking_step_type IN VARCHAR2,
678 x_completion_dt IN DATE,
679 x_action_days IN NUMBER,
680 x_step_completion_ind IN VARCHAR2,
681 x_by_pass_ind IN VARCHAR2,
682 x_recipient_id IN NUMBER,
683 x_step_group_id IN NUMBER DEFAULT NULL,
684 x_publish_ind IN VARCHAR2 DEFAULT 'N',
685 x_step_catalog_cd IN VARCHAR2 DEFAULT NULL
686 ) AS
687
688 CURSOR c1 IS
689 SELECT tracking_step_number, description,
690 s_tracking_step_type, completion_dt,
691 action_days, step_completion_ind,by_pass_ind, recipient_id,
692 step_group_id, publish_ind, step_catalog_cd
693 FROM igs_tr_step
694 WHERE ROWID = x_rowid
695 FOR UPDATE NOWAIT;
696 tlinfo c1%ROWTYPE;
697
698 BEGIN
699
700 OPEN c1;
701 FETCH c1 INTO tlinfo;
702 IF (c1%NOTFOUND) THEN
703 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
704 igs_ge_msg_stack.add;
705 app_exception.raise_exception;
706 CLOSE c1;
707 RETURN;
708 END IF;
709 CLOSE c1;
710
711 IF ( (tlinfo.tracking_step_number = x_tracking_step_number)
712 AND (tlinfo.description = x_description)
713 AND ((tlinfo.s_tracking_step_type = x_s_tracking_step_type)
714 OR ((tlinfo.s_tracking_step_type IS NULL)
715 AND (x_s_tracking_step_type IS NULL)))
716 AND ((TRUNC(tlinfo.completion_dt) = TRUNC(x_completion_dt))
717 OR ((tlinfo.completion_dt IS NULL)
718 AND (x_completion_dt IS NULL)))
719 AND (tlinfo.action_days = x_action_days)
720 AND (tlinfo.step_completion_ind = x_step_completion_ind)
721 AND (tlinfo.by_pass_ind = x_by_pass_ind)
722 AND ((tlinfo.recipient_id = x_recipient_id)
723 OR ((tlinfo.recipient_id IS NULL)
724 AND (x_recipient_id IS NULL)))
725 AND ((tlinfo.step_group_id = x_step_group_id)
726 OR ((tlinfo.step_group_id IS NULL)
727 AND (x_step_group_id IS NULL)))
728 AND ((tlinfo.publish_ind = x_publish_ind)
729 OR ((tlinfo.publish_ind IS NULL)
730 AND (x_publish_ind IS NULL)))
731 AND ((tlinfo.step_catalog_cd = x_step_catalog_cd)
732 OR ((tlinfo.step_catalog_cd IS NULL)
733 AND (x_step_catalog_cd IS NULL)))
734 ) THEN
735 NULL;
736
737 ELSE
738 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
739 igs_ge_msg_stack.add;
740 app_exception.raise_exception;
741 END IF;
742 RETURN;
743
744 END lock_row;
745
746 PROCEDURE update_row (
747 x_rowid IN VARCHAR2,
748 x_tracking_id IN NUMBER,
749 x_tracking_step_id IN NUMBER,
750 x_tracking_step_number IN NUMBER,
751 x_description IN VARCHAR2,
752 x_s_tracking_step_type IN VARCHAR2,
753 x_completion_dt IN DATE,
754 x_action_days IN NUMBER,
755 x_step_completion_ind IN VARCHAR2,
756 x_by_pass_ind IN VARCHAR2,
757 x_recipient_id IN NUMBER,
758 x_step_group_id IN NUMBER DEFAULT NULL,
759 x_publish_ind IN VARCHAR2 DEFAULT 'N',
760 x_step_catalog_cd IN VARCHAR2 DEFAULT NULL,
761 x_mode IN VARCHAR2 DEFAULT 'R'
762 ) AS
763 x_last_update_date DATE;
764 x_last_updated_by NUMBER;
765 x_last_update_login NUMBER;
766 x_request_id NUMBER;
767 x_program_id NUMBER;
768 x_program_application_id NUMBER;
769 x_program_update_date DATE;
770 BEGIN
771 x_last_update_date := SYSDATE;
772 IF(x_mode = 'I') THEN
773 x_last_updated_by := 1;
774 x_last_update_login := 0;
775 ELSIF (x_mode = 'R') THEN
776 x_last_updated_by := fnd_global.user_id;
777 IF x_last_updated_by IS NULL THEN
778 x_last_updated_by := -1;
779 END IF;
780 x_last_update_login :=fnd_global.login_id;
781 IF x_last_update_login IS NULL THEN
782 x_last_update_login := -1;
783 END IF;
784 ELSE
785 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
786 igs_ge_msg_stack.add;
787 app_exception.raise_exception;
788 END IF;
789
790 before_dml(p_action =>'UPDATE',
791 x_rowid =>x_rowid,
792 x_tracking_id => x_tracking_id,
793 x_tracking_step_id => x_tracking_step_id,
794 x_tracking_step_number => x_tracking_step_number,
795 x_description => x_description,
796 x_completion_dt => x_completion_dt,
797 x_action_days => x_action_days,
798 x_step_completion_ind => x_step_completion_ind,
799 x_by_pass_ind => x_by_pass_ind,
800 x_recipient_id => x_recipient_id,
801 x_s_tracking_step_type => x_s_tracking_step_type,
802 x_step_group_id => x_step_group_id,
803 x_publish_ind => x_publish_ind,
804 x_step_catalog_cd => x_step_catalog_cd,
805 x_creation_date =>x_last_update_date,
806 x_created_by =>x_last_updated_by,
807 x_last_update_date =>x_last_update_date,
808 x_last_updated_by =>x_last_updated_by,
809 x_last_update_login =>x_last_update_login
810 );
811
812 IF (x_mode = 'R') THEN
813 x_request_id := fnd_global.conc_request_id;
814 x_program_id := fnd_global.conc_program_id;
815 x_program_application_id := fnd_global.prog_appl_id;
816
817 IF (x_request_id = -1) THEN
818 x_request_id := old_references.request_id;
819 x_program_id := old_references.program_id;
820 x_program_application_id := old_references.program_application_id;
821 x_program_update_date := old_references.program_update_date;
822
823 ELSE
824 x_program_update_date := SYSDATE;
825 END IF;
826
827 END IF;
828
829 UPDATE igs_tr_step SET
830 tracking_step_number = new_references.tracking_step_number,
831 description = new_references.description,
832 s_tracking_step_type = new_references.s_tracking_step_type,
833 completion_dt = new_references.completion_dt,
834 action_days = new_references.action_days,
835 step_completion_ind = new_references.step_completion_ind,
836 by_pass_ind = new_references.by_pass_ind,
837 recipient_id = new_references.recipient_id,
838 step_group_id = new_references.step_group_id,
839 publish_ind = new_references.publish_ind,
840 step_catalog_cd = new_references.step_catalog_cd,
841 last_update_date = x_last_update_date,
842 last_updated_by = x_last_updated_by,
843 last_update_login = x_last_update_login,
844 request_id = x_request_id,
845 program_id = x_program_id,
846 program_application_id = x_program_application_id,
847 program_update_date = x_program_update_date
848 WHERE ROWID = x_rowid;
849
850 IF (SQL%NOTFOUND) THEN
851 RAISE no_data_found;
852 END IF;
853
854 END update_row;
855
856 PROCEDURE add_row (
857 x_rowid IN OUT NOCOPY VARCHAR2,
858 x_tracking_id IN NUMBER,
859 x_tracking_step_id IN NUMBER,
860 x_tracking_step_number IN NUMBER,
861 x_description IN VARCHAR2,
862 x_s_tracking_step_type IN VARCHAR2,
863 x_completion_dt IN DATE,
864 x_action_days IN NUMBER,
865 x_step_completion_ind IN VARCHAR2,
866 x_by_pass_ind IN VARCHAR2,
867 x_recipient_id IN NUMBER,
868 x_step_group_id IN NUMBER DEFAULT NULL,
869 x_publish_ind IN VARCHAR2 DEFAULT 'N',
870 x_step_catalog_cd IN VARCHAR2 DEFAULT NULL,
871 x_mode IN VARCHAR2 DEFAULT 'R'
872 ) AS
873 CURSOR c1 IS
874 SELECT ROWID
875 FROM igs_tr_step
876 WHERE tracking_id = x_tracking_id
877 AND tracking_step_id = x_tracking_step_id;
878
879 BEGIN
880
881 OPEN c1;
882 FETCH c1 INTO x_rowid;
883 IF (c1%NOTFOUND) THEN
884 CLOSE c1;
885 insert_row (
886 x_rowid,
887 x_tracking_id,
888 x_tracking_step_id,
889 x_tracking_step_number,
890 x_description,
891 x_s_tracking_step_type,
892 x_completion_dt,
893 x_action_days,
894 x_step_completion_ind,
895 x_by_pass_ind,
896 x_recipient_id,
897 x_step_group_id,
898 x_publish_ind,
899 x_step_catalog_cd,
900 x_mode
901 );
902 RETURN;
903 END IF;
904 CLOSE c1;
905 update_row (
906 x_rowid,
907 x_tracking_id,
908 x_tracking_step_id,
909 x_tracking_step_number,
910 x_description,
911 x_s_tracking_step_type,
912 x_completion_dt,
913 x_action_days,
914 x_step_completion_ind,
915 x_by_pass_ind,
916 x_recipient_id,
917 x_step_group_id,
918 x_publish_ind,
919 x_step_catalog_cd,
920 x_mode
921 );
922
923 END add_row;
924
925 PROCEDURE delete_row (
926 x_rowid IN VARCHAR2
927 ) AS
928
929 BEGIN
930
931 before_dml(p_action =>'DELETE',
932 x_rowid =>x_rowid
933 );
934
935 DELETE FROM igs_tr_step
936 WHERE ROWID = x_rowid;
937 IF (SQL%NOTFOUND) THEN
938 RAISE no_data_found;
939 END IF;
940
941 END delete_row;
942
943 END igs_tr_step_pkg;