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