[Home] [Help]
PACKAGE BODY: APPS.IGS_TR_TYPE_PKG
Source
1 PACKAGE BODY igs_tr_type_pkg AS
2 /* $Header: IGSTI05B.pls 115.10 2003/02/19 10:29:45 kpadiyar ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_tr_type_all%ROWTYPE;
6 new_references igs_tr_type_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_description IN VARCHAR2 DEFAULT NULL,
13 x_s_tracking_type IN VARCHAR2 DEFAULT NULL,
14 x_target_days IN NUMBER DEFAULT NULL,
15 x_sequence_ind IN VARCHAR2 DEFAULT NULL,
16 x_business_days_ind IN VARCHAR2 DEFAULT NULL,
17 x_closed_ind IN VARCHAR2 DEFAULT NULL,
18 x_publish_ind IN VARCHAR2 DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL ,
24 x_org_id IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM igs_tr_type_all
30 WHERE ROWID = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 OPEN cur_old_ref_values;
39 FETCH cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
41 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
42 igs_ge_msg_stack.add;
43 app_exception.raise_exception;
44 CLOSE cur_old_ref_values;
45 RETURN;
46 END IF;
47 CLOSE cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.tracking_type := x_tracking_type;
51 new_references.description := x_description;
52 new_references.s_tracking_type := x_s_tracking_type;
53 new_references.target_days := x_target_days;
54 new_references.sequence_ind := x_sequence_ind;
55 new_references.business_days_ind := x_business_days_ind;
56 new_references.closed_ind := x_closed_ind;
57 new_references.publish_ind := x_publish_ind;
58 new_references.org_id := x_org_id;
59
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74
75 PROCEDURE check_parent_existance AS
76 BEGIN
77
78 IF (((old_references.s_tracking_type = new_references.s_tracking_type)) OR
79 ((new_references.s_tracking_type IS NULL))) THEN
80 NULL;
81 ELSE
82 IF NOT igs_lookups_view_pkg.get_pk_for_validation('TRACKING_TYPE',new_references.s_tracking_type)THEN
83 fnd_message.set_name('FND','FORM_RECORD_DELETED');
84 igs_ge_msg_stack.add;
85 app_exception.raise_exception;
86 END IF;
87 END IF;
88
89 END check_parent_existance;
90
91 FUNCTION get_pk_for_validation (
92 x_tracking_type IN VARCHAR2
93 ) RETURN BOOLEAN AS
94
95 CURSOR cur_rowid IS
96 SELECT ROWID
97 FROM igs_tr_type_all
98 WHERE tracking_type = x_tracking_type;
99
100 lv_rowid cur_rowid%ROWTYPE;
101
102 BEGIN
103
104 OPEN cur_rowid;
105 FETCH cur_rowid INTO lv_rowid;
106 IF (cur_rowid%FOUND) THEN
107 CLOSE cur_rowid;
108 RETURN TRUE;
109 ELSE
110 CLOSE cur_rowid;
111 RETURN FALSE;
112 END IF;
113
114 END get_pk_for_validation;
115
116 PROCEDURE get_fk_igs_lookups_view(
117 x_s_tracking_type IN VARCHAR2
118 ) AS
119
120 CURSOR cur_rowid IS
121 SELECT ROWID
122 FROM igs_tr_type_all
123 WHERE s_tracking_type = x_s_tracking_type ;
124
125 lv_rowid cur_rowid%ROWTYPE;
126
127 BEGIN
128
129 OPEN cur_rowid;
130 FETCH cur_rowid INTO lv_rowid;
131 IF (cur_rowid%FOUND) THEN
132 CLOSE cur_rowid;
133 fnd_message.set_name ('IGS', 'IGS_TR_TRT_STT_FK');
134 igs_ge_msg_stack.add;
135 app_exception.raise_exception;
136 RETURN;
137 END IF;
138 CLOSE cur_rowid;
139
140 END get_fk_igs_lookups_view;
141
142 -- procedure to check constraints
143 PROCEDURE check_constraints(
144 column_name IN VARCHAR2 DEFAULT NULL,
145 column_value IN VARCHAR2 DEFAULT NULL
146 ) AS
147 BEGIN
148
149 IF column_name IS NULL THEN
150 NULL;
151 ELSIF UPPER(column_name) = 'TARGET_DAYS' THEN
152 new_references.target_days := igs_ge_number.to_num(column_value);
153 ELSIF UPPER(column_name) = 'BUSINESS_DAYS_IND' THEN
154 new_references.business_days_ind := column_value;
155 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
156 new_references.closed_ind := column_value;
157 ELSIF UPPER(column_name) = 'SEQUENCE_IND' THEN
158 new_references.sequence_ind := column_value;
159 ELSIF UPPER(column_name) = 'S_TRACKING_TYPE' THEN
160 new_references.s_tracking_type := column_value;
161 ELSIF UPPER(column_name) = 'TRACKING_TYPE' THEN
162 new_references.tracking_type := column_value;
163 ELSIF UPPER(column_name) = 'PUBLISH_IND' THEN
164 new_references.publish_ind := column_value;
165 END IF;
166 --kumma, 2702342, Increased the value of constant from 999 to 9999
167 IF UPPER(column_name) = 'TARGET_DAYS' OR column_name IS NULL THEN
168 IF (new_references.target_days < 0 OR new_references.target_days > 9999 )THEN
169 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
170 igs_ge_msg_stack.add;
171 app_exception.raise_exception;
172 END IF;
173 END IF;
174
175 IF UPPER(column_name) = 'BUSINESS_DAYS_IND' OR column_name IS NULL THEN
176 IF new_references.business_days_ind NOT IN ('Y','N') THEN
177 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
178 igs_ge_msg_stack.add;
179 app_exception.raise_exception;
180 END IF;
181 END IF;
182
183 IF UPPER(column_name) = 'CLOSED_IND' OR column_name IS NULL THEN
184 IF new_references.closed_ind NOT IN ('Y','N') THEN
185 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
186 igs_ge_msg_stack.add;
187 app_exception.raise_exception;
188 END IF;
189 END IF;
190
191 IF UPPER(column_name) = 'SEQUENCE_IND' OR column_name IS NULL THEN
192 IF new_references.sequence_ind NOT IN ('Y','N') THEN
193 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
194 igs_ge_msg_stack.add;
195 app_exception.raise_exception;
196 END IF;
197 END IF;
198
199 IF UPPER(column_name) = 'BUSINESS_DAYS_IND' OR column_name IS NULL THEN
200 IF new_references.business_days_ind <> UPPER(new_references.business_days_ind) THEN
201 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
202 igs_ge_msg_stack.add;
203 app_exception.raise_exception;
204 END IF;
205 END IF;
206
207 IF UPPER(column_name) = 'CLOSED_IND' OR column_name IS NULL THEN
208 IF new_references.closed_ind <> UPPER(new_references.closed_ind) THEN
209 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
210 igs_ge_msg_stack.add;
211 app_exception.raise_exception;
212 END IF;
213 END IF;
214
215 IF UPPER(column_name) = 'SEQUENCE_IND' OR column_name IS NULL THEN
216 IF new_references.sequence_ind <> UPPER(new_references.sequence_ind) THEN
217 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
218 igs_ge_msg_stack.add;
219 app_exception.raise_exception;
220 END IF;
221 END IF;
222
223 IF UPPER(column_name) = 'S_TRACKING_TYPE' OR column_name IS NULL THEN
224 IF new_references.s_tracking_type <> UPPER(new_references.s_tracking_type) THEN
225 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
226 igs_ge_msg_stack.add;
227 app_exception.raise_exception;
228 END IF;
229 END IF;
230
231 IF UPPER(column_name) = 'TRACKING_TYPE' OR column_name IS NULL THEN
232 IF new_references.tracking_type <> UPPER(new_references.tracking_type) THEN
233 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
234 igs_ge_msg_stack.add;
235 app_exception.raise_exception;
236 END IF;
237 END IF;
238
239 IF UPPER(column_name) = 'PUBLISH_IND' OR column_name IS NULL THEN
240 IF new_references.publish_ind NOT IN ('Y','N') THEN
241 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
242 igs_ge_msg_stack.add;
243 app_exception.raise_exception;
244 END IF;
245 END IF;
246
247 IF UPPER(column_name) = 'PUBLISH_IND' OR column_name IS NULL THEN
248 IF new_references.publish_ind <> UPPER(new_references.publish_ind) THEN
249 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
250 igs_ge_msg_stack.add;
251 app_exception.raise_exception;
252 END IF;
253 END IF;
254 END check_constraints;
255
256
257 PROCEDURE before_dml (
258 p_action IN VARCHAR2,
259 x_rowid IN VARCHAR2 DEFAULT NULL,
260 x_tracking_type IN VARCHAR2 DEFAULT NULL,
261 x_description IN VARCHAR2 DEFAULT NULL,
262 x_s_tracking_type IN VARCHAR2 DEFAULT NULL,
263 x_target_days IN NUMBER DEFAULT NULL,
264 x_sequence_ind IN VARCHAR2 DEFAULT NULL,
265 x_business_days_ind IN VARCHAR2 DEFAULT NULL,
266 x_closed_ind IN VARCHAR2 DEFAULT NULL,
267 x_publish_ind IN VARCHAR2 DEFAULT NULL,
268 x_creation_date IN DATE DEFAULT NULL,
269 x_created_by IN NUMBER DEFAULT NULL,
270 x_last_update_date IN DATE DEFAULT NULL,
271 x_last_updated_by IN NUMBER DEFAULT NULL,
272 x_last_update_login IN NUMBER DEFAULT NULL,
273 x_org_id IN NUMBER DEFAULT NULL
274 ) AS
275
276 BEGIN
277
278 set_column_values (
279 p_action,
280 x_rowid,
281 x_tracking_type,
282 x_description,
283 x_s_tracking_type,
284 x_target_days,
285 x_sequence_ind,
286 x_business_days_ind,
287 x_closed_ind,
288 x_publish_ind,
289 x_creation_date,
290 x_created_by,
291 x_last_update_date,
292 x_last_updated_by,
293 x_last_update_login ,
294 x_org_id
295 );
296
297 IF (p_action = 'INSERT') THEN
298 -- Call all the procedures related to Before Insert.
299 NULL;
300
301 IF get_pk_for_validation( new_references.tracking_type )THEN
302 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
303 igs_ge_msg_stack.add;
304 app_exception.raise_exception;
305 END IF;
306 check_constraints;
307 check_parent_existance;
308
309 ELSIF (p_action = 'UPDATE') THEN
310 -- Call all the procedures related to Before Update.
311 NULL;
312 check_constraints;
313 check_parent_existance;
314
315 ELSIF (p_action = 'VALIDATE_INSERT') THEN
316 -- Call all the procedures related to Before Delete.
317 IF get_pk_for_validation( new_references.tracking_type)THEN
318 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
319 igs_ge_msg_stack.add;
320 app_exception.raise_exception;
321 END IF;
322 check_constraints;
323
324 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
325 -- Call all the procedures related to Before Delete.
326 check_constraints;
327
328 END IF;
329
330 END before_dml;
331
332 PROCEDURE after_dml (
333 p_action IN VARCHAR2,
334 x_rowid IN VARCHAR2
335 ) AS
336 BEGIN
337 l_rowid := x_rowid;
338 END after_dml;
339
340
341 PROCEDURE insert_row (
342 x_rowid IN OUT NOCOPY VARCHAR2,
343 x_tracking_type IN VARCHAR2,
344 x_description IN VARCHAR2,
345 x_s_tracking_type IN VARCHAR2,
346 x_target_days IN NUMBER,
347 x_sequence_ind IN VARCHAR2,
348 x_business_days_ind IN VARCHAR2,
349 x_closed_ind IN VARCHAR2,
350 x_publish_ind IN VARCHAR2 DEFAULT 'N',
351 x_mode IN VARCHAR2 DEFAULT 'R',
352 x_org_id IN NUMBER
353 ) AS
354
355 CURSOR c IS
356 SELECT ROWID
357 FROM igs_tr_type_all
358 WHERE tracking_type = x_tracking_type;
359
360 x_last_update_date DATE;
361 x_last_updated_by NUMBER;
362 x_last_update_login NUMBER;
363
364 BEGIN
365
366 x_last_update_date := SYSDATE;
367
368 IF(x_mode = 'I') THEN
369 x_last_updated_by := 1;
370 x_last_update_login := 0;
371
372 ELSIF (x_mode = 'R') THEN
373 x_last_updated_by := fnd_global.user_id;
374 IF x_last_updated_by IS NULL THEN
375 x_last_updated_by := -1;
376 END IF;
377
378 x_last_update_login :=fnd_global.login_id;
379 IF x_last_update_login IS NULL THEN
380 x_last_update_login := -1;
381 END IF;
382
383 ELSE
384 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
385 igs_ge_msg_stack.add;
386 app_exception.raise_exception;
387 END IF;
388
389 before_dml(
390 p_action =>'INSERT',
391 x_rowid =>x_rowid,
392 x_tracking_type => x_tracking_type,
393 x_description => x_description,
394 x_s_tracking_type => x_s_tracking_type,
395 x_target_days => x_target_days,
396 x_sequence_ind => NVL(x_sequence_ind,'N'),
397 x_business_days_ind => NVL(x_business_days_ind,'Y'),
398 x_closed_ind => NVL(x_closed_ind,'N'),
399 x_publish_ind => NVL(x_publish_ind,'N'),
400 x_creation_date =>x_last_update_date,
401 x_created_by =>x_last_updated_by,
402 x_last_update_date =>x_last_update_date,
403 x_last_updated_by =>x_last_updated_by,
404 x_last_update_login =>x_last_update_login,
405 x_org_id => igs_ge_gen_003.get_org_id
406 );
407
408 INSERT INTO igs_tr_type_all (
409 tracking_type,
410 description,
411 s_tracking_type,
412 target_days,
413 sequence_ind,
414 business_days_ind,
415 closed_ind,
416 publish_ind,
417 org_id,
418 creation_date,
419 created_by,
420 last_update_date,
421 last_updated_by,
422 last_update_login
423 ) VALUES (
424 new_references.tracking_type,
425 new_references.description,
426 new_references.s_tracking_type,
427 new_references.target_days,
428 new_references.sequence_ind,
429 new_references.business_days_ind,
430 new_references.closed_ind,
431 new_references.publish_ind,
432 new_references.org_id,
433 x_last_update_date,
434 x_last_updated_by,
435 x_last_update_date,
436 x_last_updated_by,
437 x_last_update_login
438 );
439
440 OPEN c;
441 FETCH c INTO x_rowid;
442 IF (c%NOTFOUND) THEN
443 CLOSE c;
444 RAISE no_data_found;
445 END IF;
446 CLOSE c;
447
448 after_dml(
449 p_action =>'INSERT',
450 x_rowid => x_rowid
451 );
452
453 END insert_row;
454
455 PROCEDURE lock_row (
456 x_rowid IN VARCHAR2,
457 x_tracking_type IN VARCHAR2,
458 x_description IN VARCHAR2,
459 x_s_tracking_type IN VARCHAR2,
460 x_target_days IN NUMBER,
461 x_sequence_ind IN VARCHAR2,
462 x_business_days_ind IN VARCHAR2,
463 x_closed_ind IN VARCHAR2,
464 x_publish_ind IN VARCHAR2 DEFAULT 'N'
465 ) AS
466
467 CURSOR c1 IS
468 SELECT description,s_tracking_type,target_days,sequence_ind,business_days_ind,
469 closed_ind, publish_ind
470 FROM igs_tr_type_all
471 WHERE ROWID = x_rowid
472 FOR UPDATE NOWAIT;
473 tlinfo c1%ROWTYPE;
474
475 BEGIN
476
477 OPEN c1;
478 FETCH c1 INTO tlinfo;
479 IF (c1%NOTFOUND) THEN
480 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
481 igs_ge_msg_stack.add;
482 app_exception.raise_exception;
483 CLOSE c1;
484 RETURN;
485 END IF;
486 CLOSE c1;
487
488 IF ( (tlinfo.description = x_description)
489 AND (tlinfo.s_tracking_type = x_s_tracking_type)
490 AND (tlinfo.target_days = x_target_days)
491 AND (tlinfo.sequence_ind = x_sequence_ind)
492 AND (tlinfo.business_days_ind = x_business_days_ind)
493 AND (tlinfo.closed_ind = x_closed_ind)
494 AND ((tlinfo.publish_ind = x_publish_ind)
495 OR ((tlinfo.publish_ind IS NULL)
496 AND (x_publish_ind IS NULL)))
497 ) THEN
498 NULL;
499 ELSE
500 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
501 igs_ge_msg_stack.add;
502 app_exception.raise_exception;
503 END IF;
504 RETURN;
505 END lock_row;
506
507 PROCEDURE update_row (
508 x_rowid IN VARCHAR2,
509 x_tracking_type IN VARCHAR2,
510 x_description IN VARCHAR2,
511 x_s_tracking_type IN VARCHAR2,
512 x_target_days IN NUMBER,
513 x_sequence_ind IN VARCHAR2,
514 x_business_days_ind IN VARCHAR2,
515 x_closed_ind IN VARCHAR2,
516 x_publish_ind IN VARCHAR2 DEFAULT 'N',
517 x_mode IN VARCHAR2 DEFAULT 'R'
518 ) AS
519
520 x_last_update_date DATE;
521 x_last_updated_by NUMBER;
522 x_last_update_login NUMBER;
523
524 BEGIN
525 x_last_update_date := SYSDATE;
526 IF(x_mode = 'I') THEN
527 x_last_updated_by := 1;
528 x_last_update_login := 0;
529 ELSIF (x_mode = 'R') THEN
530 x_last_updated_by := fnd_global.user_id;
531 IF x_last_updated_by IS NULL THEN
532 x_last_updated_by := -1;
533 END IF;
534 x_last_update_login :=fnd_global.login_id;
535 IF x_last_update_login IS NULL THEN
536 x_last_update_login := -1;
537 END IF;
538 ELSE
539 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
540 igs_ge_msg_stack.add;
541 app_exception.raise_exception;
542 END IF;
543
544 before_dml(p_action =>'UPDATE',
545 x_rowid =>x_rowid,
546 x_tracking_type => x_tracking_type,
547 x_description => x_description,
548 x_s_tracking_type => x_s_tracking_type,
549 x_target_days => x_target_days,
550 x_sequence_ind => x_sequence_ind,
551 x_business_days_ind => x_business_days_ind,
552 x_closed_ind => x_closed_ind,
553 x_publish_ind => x_publish_ind,
554 x_creation_date =>x_last_update_date,
555 x_created_by =>x_last_updated_by,
556 x_last_update_date =>x_last_update_date,
557 x_last_updated_by =>x_last_updated_by,
558 x_last_update_login =>x_last_update_login
559 );
560
561 UPDATE igs_tr_type_all SET
562 description = new_references.description,
563 s_tracking_type = new_references.s_tracking_type,
564 target_days = new_references.target_days,
565 sequence_ind = new_references.sequence_ind,
566 business_days_ind = new_references.business_days_ind,
567 closed_ind = new_references.closed_ind,
568 publish_ind = new_references.publish_ind,
569 last_update_date = x_last_update_date,
570 last_updated_by = x_last_updated_by,
571 last_update_login = x_last_update_login
572 WHERE ROWID = x_rowid;
573
574 IF (SQL%NOTFOUND) THEN
575 RAISE no_data_found;
576 END IF;
577
578 after_dml(
579 p_action =>'UPDATE',
580 x_rowid => x_rowid
581 );
582
583 END update_row;
584
585 PROCEDURE add_row (
586 x_rowid IN OUT NOCOPY VARCHAR2,
587 x_tracking_type IN VARCHAR2,
588 x_description IN VARCHAR2,
589 x_s_tracking_type IN VARCHAR2,
590 x_target_days IN NUMBER,
591 x_sequence_ind IN VARCHAR2,
592 x_business_days_ind IN VARCHAR2,
593 x_closed_ind IN VARCHAR2,
594 x_publish_ind IN VARCHAR2 DEFAULT 'N',
595 x_mode IN VARCHAR2 DEFAULT 'R',
596 x_org_id IN NUMBER
597 ) AS
598
599 CURSOR c1 IS
600 SELECT ROWID
601 FROM igs_tr_type_all
602 WHERE tracking_type = x_tracking_type;
603
604 BEGIN
605
606 OPEN c1;
607 FETCH c1 INTO x_rowid;
608
609 IF (c1%NOTFOUND) THEN
610 CLOSE c1;
611 insert_row (
612 x_rowid,
613 x_tracking_type,
614 x_description,
615 x_s_tracking_type,
616 x_target_days,
617 x_sequence_ind,
618 x_business_days_ind,
619 x_closed_ind,
620 x_publish_ind,
621 x_mode,
622 x_org_id
623 );
624 RETURN;
625 END IF;
626 CLOSE c1;
627
628 update_row (
629 x_rowid,
630 x_tracking_type,
631 x_description,
632 x_s_tracking_type,
633 x_target_days,
634 x_sequence_ind,
635 x_business_days_ind,
636 x_closed_ind,
637 x_publish_ind,
638 x_mode);
639 END add_row;
640
641 END igs_tr_type_pkg;