[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_RU_CA_TYPE_PKG
Source
1 package body IGS_PR_RU_CA_TYPE_PKG AS
2 /* $Header: IGSQI12B.pls 115.6 2002/11/29 03:16:51 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PR_RU_CA_TYPE_ALL%RowType;
6 new_references IGS_PR_RU_CA_TYPE_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
12 x_pra_sequence_number IN NUMBER DEFAULT NULL,
13 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_start_sequence_number IN NUMBER DEFAULT NULL,
15 x_end_sequence_number IN NUMBER DEFAULT NULL,
16 x_start_effective_period IN NUMBER DEFAULT NULL,
17 x_num_of_applications IN NUMBER DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL ,
23 x_org_id IN NUMBER DEFAULT NULL
24 ) AS
25
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_PR_RU_CA_TYPE_ALL
29 WHERE rowid = x_rowid;
30
31 BEGIN
32
33 l_rowid := x_rowid;
34
35 -- Code for setting the Old and New Reference Values.
36 -- Populate Old Values.
37 Open cur_old_ref_values;
38 Fetch cur_old_ref_values INTO old_references;
39 IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
40 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_GE_MSG_STACK.ADD;
42 Close cur_old_ref_values;
43 App_Exception.Raise_Exception;
44
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.progression_rule_cat := x_progression_rule_cat;
51 new_references.pra_sequence_number := x_pra_sequence_number;
52 new_references.prg_cal_type := x_prg_cal_type;
53 new_references.start_sequence_number := x_start_sequence_number;
54 new_references.end_sequence_number := x_end_sequence_number;
55 new_references.start_effective_period := x_start_effective_period;
56 new_references.num_of_applications := x_num_of_applications;
57 new_references.org_id := x_org_id;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 -- Trigger description :-
73 -- "OSS_TST".trg_prct_br_iu
74 -- BEFORE INSERT OR UPDATE
75 -- ON IGS_PR_RU_CA_TYPE
76 -- FOR EACH ROW
77
78 PROCEDURE BeforeRowInsertUpdate1(
79 p_inserting IN BOOLEAN DEFAULT FALSE,
80 p_updating IN BOOLEAN DEFAULT FALSE,
81 p_deleting IN BOOLEAN DEFAULT FALSE
82 ) AS
83 v_message_name varchar2(30);
84 BEGIN
85 -- Validate the progression calendar type
86 IF p_inserting THEN
87 IF igs_pr_val_scpca.prgp_val_cfg_cat (
88 new_references.prg_cal_type,
89 v_message_name) = FALSE THEN
90 Fnd_Message.Set_Name('IGS',v_message_name);
91 IGS_GE_MSG_STACK.ADD;
92 App_Exception.Raise_Exception;
93 END IF;
94 END IF;
95 -- Validate the start and end sequence number
96 IF p_inserting OR (p_updating AND
97 (NVL(new_references.start_sequence_number, 0) <> NVL(old_references.start_sequence_number, 0) OR
98 NVL(new_references.end_sequence_number, 0) <> NVL(old_references.end_sequence_number, 0))) THEN
99 IF IGS_PR_VAL_PRCT.prgp_val_prct_ci (
100 new_references.prg_cal_type,
101 new_references.start_sequence_number,
102 new_references.end_sequence_number,
103 v_message_name) = FALSE THEN
104 Fnd_Message.Set_Name('IGS',v_message_name);
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception;
107 END IF;
108 END IF;
109
110
111 END BeforeRowInsertUpdate1;
112
113 PROCEDURE Check_Parent_Existance AS
114 BEGIN
115
116 IF (((old_references.prg_cal_type = new_references.prg_cal_type)) OR
117 ((new_references.prg_cal_type IS NULL))) THEN
118 NULL;
119 ELSE
120 IF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
121 new_references.prg_cal_type
122 ) THEN
123 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126
127 END IF;
128
129 END IF;
130
131 IF (((old_references.prg_cal_type = new_references.prg_cal_type) AND
132 (old_references.end_sequence_number = new_references.end_sequence_number)) OR
133 ((new_references.prg_cal_type IS NULL) OR
134 (new_references.end_sequence_number IS NULL))) THEN
135 NULL;
136 ELSE
137 IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
138 new_references.prg_cal_type,
139 new_references.end_sequence_number
140 ) THEN
141 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144
145 END IF;
146
147 END IF;
148
149 IF (((old_references.prg_cal_type = new_references.prg_cal_type) AND
150 (old_references.start_sequence_number = new_references.start_sequence_number)) OR
151 ((new_references.prg_cal_type IS NULL) OR
152 (new_references.start_sequence_number IS NULL))) THEN
153 NULL;
154 ELSE
155 IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
156 new_references.prg_cal_type,
157 new_references.start_sequence_number
158 ) THEN
159
160 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception;
163
164 END IF;
165
166 END IF;
167
168 IF (((old_references.progression_rule_cat = new_references.progression_rule_cat) AND
169 (old_references.pra_sequence_number = new_references.pra_sequence_number)) OR
170 ((new_references.progression_rule_cat IS NULL) OR
171 (new_references.pra_sequence_number IS NULL))) THEN
172 NULL;
173 ELSE
174 IF NOT IGS_PR_RU_APPL_PKG.Get_PK_For_Validation (
175 new_references.progression_rule_cat,
176 new_references.pra_sequence_number
177 ) THEN
178
179 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182
183 END IF;
184
185 END IF;
186
187 END Check_Parent_Existance;
188
189 FUNCTION Get_PK_For_Validation (
190 x_progression_rule_cat IN VARCHAR2,
191 x_pra_sequence_number IN NUMBER,
192 x_prg_cal_type IN VARCHAR2
193 ) RETURN BOOLEAN AS
194
195 CURSOR cur_rowid IS
196 SELECT rowid
197 FROM IGS_PR_RU_CA_TYPE_ALL
198 WHERE progression_rule_cat = x_progression_rule_cat
199 AND pra_sequence_number = x_pra_sequence_number
200 AND prg_cal_type = x_prg_cal_type
201 FOR UPDATE NOWAIT;
202
203 lv_rowid cur_rowid%RowType;
204
205 BEGIN
206
207 Open cur_rowid;
208 Fetch cur_rowid INTO lv_rowid;
209 IF (cur_rowid%FOUND) THEN
210 Close cur_rowid;
211 Return (TRUE);
212 ELSE
213 Close cur_rowid;
214 Return (FALSE);
215 END IF;
216
217 END Get_PK_For_Validation;
218
219 PROCEDURE GET_FK_IGS_CA_TYPE (
220 x_cal_type IN VARCHAR2
221 ) AS
222
223 CURSOR cur_rowid IS
224 SELECT rowid
225 FROM IGS_PR_RU_CA_TYPE_ALL
226 WHERE prg_cal_type = x_cal_type ;
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 Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRCT_CAT_FK');
236 IGS_GE_MSG_STACK.ADD;
237 Close cur_rowid;
238 App_Exception.Raise_Exception;
239
240 Return;
241 END IF;
242 Close cur_rowid;
243
244 END GET_FK_IGS_CA_TYPE;
245
246 PROCEDURE GET_FK_IGS_CA_INST (
247 x_cal_type IN VARCHAR2,
248 x_sequence_number IN NUMBER
249 ) AS
250
251 CURSOR cur_rowid IS
252 SELECT rowid
253 FROM IGS_PR_RU_CA_TYPE_ALL
254 WHERE ( prg_cal_type = x_cal_type
255 AND end_sequence_number = x_sequence_number )
256 OR (prg_cal_type = x_cal_type
257 AND start_sequence_number = x_sequence_number) ;
258
259 lv_rowid cur_rowid%RowType;
260
261 BEGIN
262
263 Open cur_rowid;
264 Fetch cur_rowid INTO lv_rowid;
265 IF (cur_rowid%FOUND) THEN
266 Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRCT_CI_END_FK');
267 IGS_GE_MSG_STACK.ADD;
268 Close cur_rowid;
269 App_Exception.Raise_Exception;
270
271 Return;
272 END IF;
273 Close cur_rowid;
274
275 END GET_FK_IGS_CA_INST;
276
277 PROCEDURE GET_FK_IGS_PR_RU_APPL (
278 x_progression_rule_cat IN VARCHAR2,
279 x_sequence_number IN NUMBER
280 ) AS
281
282 CURSOR cur_rowid IS
283 SELECT rowid
284 FROM IGS_PR_RU_CA_TYPE_ALL
285 WHERE progression_rule_cat = x_progression_rule_cat
286 AND pra_sequence_number = x_sequence_number ;
287
288 lv_rowid cur_rowid%RowType;
289
290 BEGIN
291
292 Open cur_rowid;
293 Fetch cur_rowid INTO lv_rowid;
294 IF (cur_rowid%FOUND) THEN
295 Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRCT_PRA_FK');
296 IGS_GE_MSG_STACK.ADD;
297 Close cur_rowid;
298 App_Exception.Raise_Exception;
299
300 Return;
301 END IF;
302 Close cur_rowid;
303
304 END GET_FK_IGS_PR_RU_APPL;
305
306 PROCEDURE Before_DML (
307 p_action IN VARCHAR2,
308 x_rowid IN VARCHAR2 DEFAULT NULL,
309 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
310 x_pra_sequence_number IN NUMBER DEFAULT NULL,
311 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
312 x_start_sequence_number IN NUMBER DEFAULT NULL,
313 x_end_sequence_number IN NUMBER DEFAULT NULL,
314 x_start_effective_period IN NUMBER DEFAULT NULL,
315 x_num_of_applications IN NUMBER DEFAULT NULL,
316 x_creation_date IN DATE DEFAULT NULL,
317 x_created_by IN NUMBER DEFAULT NULL,
318 x_last_update_date IN DATE DEFAULT NULL,
319 x_last_updated_by IN NUMBER DEFAULT NULL,
320 x_last_update_login IN NUMBER DEFAULT NULL,
321 x_org_id IN NUMBER DEFAULT NULL
322 ) AS
323 BEGIN
324
325 Set_Column_Values (
326 p_action,
327 x_rowid,
328 x_progression_rule_cat,
329 x_pra_sequence_number,
330 x_prg_cal_type,
331 x_start_sequence_number,
332 x_end_sequence_number,
333 x_start_effective_period,
334 x_num_of_applications,
335 x_creation_date,
336 x_created_by,
337 x_last_update_date,
338 x_last_updated_by,
339 x_last_update_login ,
340 x_org_id
341 );
342
343 IF (p_action = 'INSERT') THEN
344 -- Call all the procedures related to Before Insert.
345 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
346 Check_Parent_Existance;
347 IF GET_PK_FOR_VALIDATION(
348 new_references.progression_rule_cat,
349 new_references.pra_sequence_number,
350 new_references.prg_cal_type ) THEN
351 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
352 IGS_GE_MSG_STACK.ADD;
353 App_Exception.Raise_Exception;
354
355 END IF;
356 CHECK_CONSTRAINTS;
357
358 ELSIF (p_action = 'UPDATE') THEN
359 -- Call all the procedures related to Before Update.
360 BeforeRowInsertUpdate1 ( p_updating => TRUE );
361 Check_Parent_Existance;
362 CHECK_CONSTRAINTS;
363
364 ELSIF (p_action = 'VALIDATE_INSERT') THEN
365 IF GET_PK_FOR_VALIDATION(
366 new_references.progression_rule_cat,
367 new_references.pra_sequence_number,
368 new_references.prg_cal_type ) THEN
369 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
370 IGS_GE_MSG_STACK.ADD;
371 App_Exception.Raise_Exception;
372
373 END IF;
374 CHECK_CONSTRAINTS;
375 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
376 CHECK_CONSTRAINTS;
377 END IF;
378
379 END Before_DML;
380
381 procedure INSERT_ROW (
382 X_ROWID in out NOCOPY VARCHAR2,
383 X_PROGRESSION_RULE_CAT in VARCHAR2,
384 X_PRA_SEQUENCE_NUMBER in NUMBER,
385 X_PRG_CAL_TYPE in VARCHAR2,
386 X_START_SEQUENCE_NUMBER in NUMBER,
387 X_END_SEQUENCE_NUMBER in NUMBER,
388 X_START_EFFECTIVE_PERIOD in NUMBER,
389 X_NUM_OF_APPLICATIONS in NUMBER,
390 X_MODE in VARCHAR2 default 'R',
391 x_ORG_ID IN NUMBER
392 ) AS
393 cursor C is select ROWID from IGS_PR_RU_CA_TYPE_ALL
394 where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
395 and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
396 and PRG_CAL_TYPE = X_PRG_CAL_TYPE;
397 X_LAST_UPDATE_DATE DATE;
398 X_LAST_UPDATED_BY NUMBER;
399 X_LAST_UPDATE_LOGIN NUMBER;
400 begin
401 X_LAST_UPDATE_DATE := SYSDATE;
402 if(X_MODE = 'I') then
403 X_LAST_UPDATED_BY := 1;
404 X_LAST_UPDATE_LOGIN := 0;
405 elsif (X_MODE = 'R') then
406 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
407 if X_LAST_UPDATED_BY is NULL then
408 X_LAST_UPDATED_BY := -1;
409 end if;
410 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
411 if X_LAST_UPDATE_LOGIN is NULL then
412 X_LAST_UPDATE_LOGIN := -1;
413 end if;
414 else
415 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
416 IGS_GE_MSG_STACK.ADD;
417 app_exception.raise_exception;
418 end if;
419 Before_DML (
420 p_action => 'INSERT',
421 x_rowid => x_rowid ,
422 x_progression_rule_cat => x_progression_rule_cat ,
423 x_pra_sequence_number => x_pra_sequence_number ,
424 x_prg_cal_type => x_prg_cal_type ,
425 x_start_sequence_number => x_start_sequence_number ,
426 x_end_sequence_number => x_end_sequence_number ,
427 x_start_effective_period => x_start_effective_period ,
428 x_num_of_applications => x_num_of_applications ,
429 x_creation_date => x_last_update_date ,
430 x_created_by => x_last_updated_by,
431 x_last_update_date => x_last_update_date ,
432 x_last_updated_by => x_last_updated_by,
433 x_last_update_login => x_last_update_login,
434 x_org_id => igs_ge_gen_003.get_org_id
435 );
436 insert into IGS_PR_RU_CA_TYPE_ALL (
437 PROGRESSION_RULE_CAT,
438 PRA_SEQUENCE_NUMBER,
439 PRG_CAL_TYPE,
440 START_SEQUENCE_NUMBER,
441 END_SEQUENCE_NUMBER,
442 START_EFFECTIVE_PERIOD,
443 NUM_OF_APPLICATIONS,
444 CREATION_DATE,
445 CREATED_BY,
446 LAST_UPDATE_DATE,
447 LAST_UPDATED_BY,
448 LAST_UPDATE_LOGIN,
449 ORG_ID
450 ) values (
451 NEW_REFERENCES.PROGRESSION_RULE_CAT,
452 NEW_REFERENCES.PRA_SEQUENCE_NUMBER,
453 NEW_REFERENCES.PRG_CAL_TYPE,
454 NEW_REFERENCES.START_SEQUENCE_NUMBER,
455 NEW_REFERENCES.END_SEQUENCE_NUMBER,
456 NEW_REFERENCES.START_EFFECTIVE_PERIOD,
457 NEW_REFERENCES.NUM_OF_APPLICATIONS,
458 X_LAST_UPDATE_DATE,
459 X_LAST_UPDATED_BY,
460 X_LAST_UPDATE_DATE,
461 X_LAST_UPDATED_BY,
462 X_LAST_UPDATE_LOGIN,
463 NEW_REFERENCES.ORG_ID
464 );
465
466 open c;
467 fetch c into X_ROWID;
468 if (c%notfound) then
469 close c;
470 raise no_data_found;
471 end if;
472 close c;
473 end INSERT_ROW;
474
475 procedure LOCK_ROW (
476 X_ROWID in VARCHAR2,
477 X_PROGRESSION_RULE_CAT in VARCHAR2,
478 X_PRA_SEQUENCE_NUMBER in NUMBER,
479 X_PRG_CAL_TYPE in VARCHAR2,
480 X_START_SEQUENCE_NUMBER in NUMBER,
481 X_END_SEQUENCE_NUMBER in NUMBER,
482 X_START_EFFECTIVE_PERIOD in NUMBER,
483 X_NUM_OF_APPLICATIONS in NUMBER
484 ) AS
485 cursor c1 is select
486 START_SEQUENCE_NUMBER,
487 END_SEQUENCE_NUMBER,
488 START_EFFECTIVE_PERIOD,
489 NUM_OF_APPLICATIONS
490 from IGS_PR_RU_CA_TYPE_ALL
491 where ROWID = X_ROWID for update nowait;
492 tlinfo c1%rowtype;
493
494 begin
495 open c1;
496 fetch c1 into tlinfo;
497 if (c1%notfound) then
498 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
499 IGS_GE_MSG_STACK.ADD;
500 close c1;
501 app_exception.raise_exception;
502
503 return;
504 end if;
505 close c1;
506
507 if ( ((tlinfo.START_SEQUENCE_NUMBER = X_START_SEQUENCE_NUMBER)
508 OR ((tlinfo.START_SEQUENCE_NUMBER is null)
509 AND (X_START_SEQUENCE_NUMBER is null)))
510 AND ((tlinfo.END_SEQUENCE_NUMBER = X_END_SEQUENCE_NUMBER)
511 OR ((tlinfo.END_SEQUENCE_NUMBER is null)
512 AND (X_END_SEQUENCE_NUMBER is null)))
513 AND ((tlinfo.START_EFFECTIVE_PERIOD = X_START_EFFECTIVE_PERIOD)
514 OR ((tlinfo.START_EFFECTIVE_PERIOD is null)
515 AND (X_START_EFFECTIVE_PERIOD is null)))
516 AND ((tlinfo.NUM_OF_APPLICATIONS = X_NUM_OF_APPLICATIONS)
517 OR ((tlinfo.NUM_OF_APPLICATIONS is null)
518 AND (X_NUM_OF_APPLICATIONS is null)))
519 ) then
520 null;
521 else
522 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
523 IGS_GE_MSG_STACK.ADD;
524 app_exception.raise_exception;
525 end if;
526 return;
527 end LOCK_ROW;
528
529 procedure UPDATE_ROW (
530 X_ROWID in VARCHAR2,
531 X_PROGRESSION_RULE_CAT in VARCHAR2,
532 X_PRA_SEQUENCE_NUMBER in NUMBER,
533 X_PRG_CAL_TYPE in VARCHAR2,
534 X_START_SEQUENCE_NUMBER in NUMBER,
535 X_END_SEQUENCE_NUMBER in NUMBER,
536 X_START_EFFECTIVE_PERIOD in NUMBER,
537 X_NUM_OF_APPLICATIONS in NUMBER,
538 X_MODE in VARCHAR2 default 'R'
539 ) AS
540 X_LAST_UPDATE_DATE DATE;
541 X_LAST_UPDATED_BY NUMBER;
542 X_LAST_UPDATE_LOGIN NUMBER;
543 begin
544 X_LAST_UPDATE_DATE := SYSDATE;
545 if(X_MODE = 'I') then
546 X_LAST_UPDATED_BY := 1;
547 X_LAST_UPDATE_LOGIN := 0;
548 elsif (X_MODE = 'R') then
549 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
550 if X_LAST_UPDATED_BY is NULL then
551 X_LAST_UPDATED_BY := -1;
552 end if;
553 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
554 if X_LAST_UPDATE_LOGIN is NULL then
555 X_LAST_UPDATE_LOGIN := -1;
556 end if;
557 else
558 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
559 IGS_GE_MSG_STACK.ADD;
560 app_exception.raise_exception;
561 end if;
562 Before_DML (
563 p_action => 'UPDATE',
564 x_rowid => x_rowid ,
565 x_progression_rule_cat => x_progression_rule_cat ,
566 x_pra_sequence_number => x_pra_sequence_number ,
567 x_prg_cal_type => x_prg_cal_type ,
568 x_start_sequence_number => x_start_sequence_number ,
569 x_end_sequence_number => x_end_sequence_number ,
570 x_start_effective_period => x_start_effective_period ,
571 x_num_of_applications => x_num_of_applications ,
572 x_creation_date => x_last_update_date ,
573 x_created_by => x_last_updated_by,
574 x_last_update_date => x_last_update_date ,
575 x_last_updated_by => x_last_updated_by,
576 x_last_update_login => x_last_update_login
577 );
578
579 update IGS_PR_RU_CA_TYPE_ALL set
580 START_SEQUENCE_NUMBER = NEW_REFERENCES.START_SEQUENCE_NUMBER,
581 END_SEQUENCE_NUMBER = NEW_REFERENCES.END_SEQUENCE_NUMBER,
582 START_EFFECTIVE_PERIOD = NEW_REFERENCES.START_EFFECTIVE_PERIOD,
583 NUM_OF_APPLICATIONS = NEW_REFERENCES.NUM_OF_APPLICATIONS,
584 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
585 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
586 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
587 where ROWID = X_ROWID
588 ;
589 if (sql%notfound) then
590 raise no_data_found;
591 end if;
592 end UPDATE_ROW;
593
594 procedure ADD_ROW (
595 X_ROWID in out NOCOPY VARCHAR2,
596 X_PROGRESSION_RULE_CAT in VARCHAR2,
597 X_PRA_SEQUENCE_NUMBER in NUMBER,
598 X_PRG_CAL_TYPE in VARCHAR2,
599 X_START_SEQUENCE_NUMBER in NUMBER,
600 X_END_SEQUENCE_NUMBER in NUMBER,
601 X_START_EFFECTIVE_PERIOD in NUMBER,
602 X_NUM_OF_APPLICATIONS in NUMBER,
603 X_MODE in VARCHAR2 default 'R',
604 X_ORG_ID IN NUMBER
605 ) AS
606 cursor c1 is select rowid from IGS_PR_RU_CA_TYPE_ALL
607 where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
608 and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
609 and PRG_CAL_TYPE = X_PRG_CAL_TYPE
610 ;
611 begin
612 open c1;
613 fetch c1 into X_ROWID;
614 if (c1%notfound) then
615 close c1;
616 INSERT_ROW (
617 X_ROWID,
618 X_PROGRESSION_RULE_CAT,
619 X_PRA_SEQUENCE_NUMBER,
620 X_PRG_CAL_TYPE,
621 X_START_SEQUENCE_NUMBER,
622 X_END_SEQUENCE_NUMBER,
623 X_START_EFFECTIVE_PERIOD,
624 X_NUM_OF_APPLICATIONS,
625 X_MODE,
626 X_ORG_ID);
627 return;
628 end if;
629 close c1;
630 UPDATE_ROW (
631 X_ROWID,
632 X_PROGRESSION_RULE_CAT,
633 X_PRA_SEQUENCE_NUMBER,
634 X_PRG_CAL_TYPE,
635 X_START_SEQUENCE_NUMBER,
636 X_END_SEQUENCE_NUMBER,
637 X_START_EFFECTIVE_PERIOD,
638 X_NUM_OF_APPLICATIONS,
639 X_MODE );
640
641 end ADD_ROW;
642
643 procedure DELETE_ROW (
644 X_ROWID in VARCHAR2
645 ) AS
646 begin
647 BEFORE_DML (
648 p_action => 'DELETE',
649 x_rowid => X_ROWID
650 );
651
652 delete from IGS_PR_RU_CA_TYPE_ALL
653 where ROWID = X_ROWID;
654 if (sql%notfound) then
655 raise no_data_found;
656 end if;
657 end DELETE_ROW;
658
659 PROCEDURE Check_Constraints (
660 Column_Name IN VARCHAR2 DEFAULT NULL,
661 Column_Value IN VARCHAR2 DEFAULT NULL
662 ) AS
663 BEGIN
664 IF column_name IS NULL THEN
665 NULL;
666 ELSIF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' then
667 new_references.pra_sequence_number := IGS_GE_NUMBER.to_num(column_value);
668 ELSIF upper(Column_name) = 'END_SEQUENCE_NUMBER' then
669 new_references.end_sequence_number := IGS_GE_NUMBER.to_num(column_value);
670 ELSIF upper(Column_name) = 'START_SEQUENCE_NUMBER' then
671 new_references.start_sequence_number := IGS_GE_NUMBER.to_num(column_value);
672 ELSIF upper(Column_name) = 'PRG_CAL_TYPE' then
673 new_references.prg_cal_type:= column_value;
674 ELSIF upper(Column_name) = 'PROGRESSION_RULE_CAT' then
675 new_references.progression_rule_cat:= column_value;
676 ELSIF upper(Column_name) = 'START_EFFECTIVE_PERIOD' then
677 new_references.start_effective_period := IGS_GE_NUMBER.to_num(column_value);
678 ELSIF upper(Column_name) = 'NUM_OF_APPLICATIONS' then
679 new_references.num_of_applications := IGS_GE_NUMBER.to_num(column_value);
680 END IF;
681
682 IF UPPER(column_name) = 'PRA_SEQUENCE_NUMBER' OR column_name IS NULL THEN
683 IF new_references.pra_sequence_number < 1 OR
684 new_references.pra_sequence_number > 999999 THEN
685 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
686 IGS_GE_MSG_STACK.ADD;
687 App_Exception.Raise_Exception;
688 END IF;
689 END IF;
690
691 IF UPPER(column_name) = 'END_SEQUENCE_NUMBER' OR column_name IS NULL THEN
692 IF new_references.end_sequence_number < 1 OR
693 new_references.end_sequence_number > 999999 THEN
694 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
695 IGS_GE_MSG_STACK.ADD;
696 App_Exception.Raise_Exception;
697 END IF;
698 END IF;
699 IF UPPER(column_name) = 'START_SEQUENCE_NUMBER' OR column_name IS NULL THEN
700 IF new_references.start_sequence_number < 1 OR
701 new_references.start_sequence_number > 999999 THEN
702 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
703 IGS_GE_MSG_STACK.ADD;
704 App_Exception.Raise_Exception;
705 END IF;
706 END IF;
707 IF UPPER(column_name) = 'START_EFFECTIVE_PERIOD' OR column_name IS NULL THEN
708 IF new_references.start_effective_period < 1 OR
709 new_references.start_effective_period > 99 THEN
710 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
711 IGS_GE_MSG_STACK.ADD;
712 App_Exception.Raise_Exception;
713 END IF;
714 END IF;
715
716 IF UPPER(column_name) = 'NUM_OF_APPLICATIONS' OR column_name IS NULL THEN
717 IF new_references.num_of_applications < 1 OR
718 new_references.num_of_applications > 99 THEN
719 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
720 IGS_GE_MSG_STACK.ADD;
721 App_Exception.Raise_Exception;
722 END IF;
723 END IF;
724
725 IF UPPER(column_name) = 'PRG_CAL_TYPE' OR column_name IS NULL THEN
726 IF new_references.prg_cal_type <> UPPER(new_references.prg_cal_type) THEN
727 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
728 IGS_GE_MSG_STACK.ADD;
729 App_Exception.Raise_Exception;
730 END IF;
731 END IF;
732
733 IF UPPER(column_name) = 'PROGRESSION_RULE_CAT' OR column_name IS NULL THEN
734 IF new_references.progression_rule_cat<> UPPER(new_references.progression_rule_cat) THEN
735 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
736 IGS_GE_MSG_STACK.ADD;
737 App_Exception.Raise_Exception;
738 END IF;
739 END IF;
740
741 END Check_Constraints;
742 end IGS_PR_RU_CA_TYPE_PKG;