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