[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_F_RET_SCHD_HT_PKG
Source
1 package body IGS_FI_F_RET_SCHD_HT_PKG AS
2 /* $Header: IGSSI48B.pls 120.1 2006/05/26 13:43:08 skharida noship $*/
3 l_rowid VARCHAR2(25);
4 old_references IGS_FI_F_RET_SCHD_HT_ALL%RowType;
5 new_references IGS_FI_F_RET_SCHD_HT_ALL%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
10 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
11 x_s_relation_type IN VARCHAR2 DEFAULT NULL,
12 x_sequence_number IN NUMBER DEFAULT NULL,
13 x_hist_start_dt IN DATE DEFAULT NULL,
14 x_hist_end_dt IN DATE DEFAULT NULL,
15 x_hist_who IN VARCHAR2 DEFAULT NULL,
16 x_fee_type IN VARCHAR2 DEFAULT NULL,
17 x_fee_cat IN VARCHAR2 DEFAULT NULL,
18 x_schedule_number IN NUMBER DEFAULT NULL,
19 x_dt_alias IN VARCHAR2 DEFAULT NULL,
20 x_dai_sequence_number IN NUMBER DEFAULT NULL,
21 x_retention_percentage IN NUMBER DEFAULT NULL,
22 x_retention_amount IN NUMBER DEFAULT NULL,
23 x_org_id IN NUMBER DEFAULT NULL,
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 ) AS
30 /*----------------------------------------------------------------------------
31 || Created By :
32 || Created On :
33 || Purpose :
34 || Known limitations, enhancements or remarks :
35 || Change History :
36 || Who When What
37 || (reverse chronological order - newest change first)
38 || vvutukur 19-Jul-2002 Bug#2425767.Removed parameter x_deduction_amount and its reference in
39 || copying old_references value into new_references.value.
40 ----------------------------------------------------------------------------*/
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM IGS_FI_F_RET_SCHD_HT_ALL
44 WHERE rowid = x_rowid;
45 BEGIN
46 l_rowid := x_rowid;
47 -- Code for setting the Old and New Reference Values.
48 -- Populate Old Values.
49 Open cur_old_ref_values;
50 Fetch cur_old_ref_values INTO old_references;
51 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
52 Close cur_old_ref_values;
53 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 Return;
57 END IF;
58 Close cur_old_ref_values;
59 -- Populate New Values.
60 new_references.fee_cal_type := x_fee_cal_type;
61 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
62 new_references.s_relation_type := x_s_relation_type;
63 new_references.sequence_number := x_sequence_number;
64 new_references.hist_start_dt := x_hist_start_dt;
65 new_references.hist_end_dt := x_hist_end_dt;
66 new_references.hist_who := x_hist_who;
67 new_references.fee_type := x_fee_type;
68 new_references.fee_cat := x_fee_cat;
69 new_references.schedule_number := x_schedule_number;
70 new_references.dt_alias := x_dt_alias;
71 new_references.dai_sequence_number := x_dai_sequence_number;
72 new_references.retention_percentage := x_retention_percentage;
73 new_references.retention_amount := x_retention_amount;
74 new_references.org_id := x_org_id;
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82 new_references.last_update_date := x_last_update_date;
83 new_references.last_updated_by := x_last_updated_by;
84 new_references.last_update_login := x_last_update_login;
85 END Set_Column_Values;
86 PROCEDURE Check_Constraints (
87 column_name IN VARCHAR2 DEFAULT NULL,
88 column_value IN VARCHAR2 DEFAULT NULL
89 ) AS
90 /*----------------------------------------------------------------------------
91 || Created By :
92 || Created On :
93 || Purpose :
94 || Known limitations, enhancements or remarks :
95 || Change History :
96 || Who When What
97 || (reverse chronological order - newest change first)
98 || skharida 26-May-2006 Bug 5217319 Removed the hardcoded precision check
99 || vvutukur 19_jul-2002 Bug#2425767.Removed x_deduction_amount references(from if conditions).
100 || vvutukur 18-May-2002 removed upper check on fee_type,fee_cat columns.bug#2344826.
101 ----------------------------------------------------------------------------*/
102 BEGIN
103 IF (column_name IS NULL) THEN
104 NULL;
105 ELSIF (UPPER (column_name) = 'RETENTION_PERCENTAGE') THEN
106 new_references.retention_percentage := IGS_GE_NUMBER.TO_NUM (column_value);
107 ELSIF (UPPER (column_name) = 'RETENTION_AMOUNT') THEN
108 new_references.retention_amount := IGS_GE_NUMBER.TO_NUM (column_value);
109 ELSIF (UPPER (column_name) = 'DT_ALIAS') THEN
110 new_references.dt_alias := column_value;
111 ELSIF (UPPER (column_name) = 'FEE_CAL_TYPE') THEN
112 new_references.fee_cal_type := column_value;
113 ELSIF (UPPER (column_name) = 'S_RELATION_TYPE') THEN
114 new_references.s_relation_type := column_value;
115 ELSIF (UPPER (column_name) = 'SEQUENCE_NUMBER') THEN
116 new_references.sequence_number := IGS_GE_NUMBER.TO_NUM (column_value);
117 ELSIF (UPPER (column_name) = 'DAI_SEQUENCE_NUMBER') THEN
118 new_references.dai_sequence_number := IGS_GE_NUMBER.TO_NUM (column_value);
119 ELSIF (UPPER (column_name) = 'SCHEDULE_NUMBER') THEN
120 new_references.schedule_number := IGS_GE_NUMBER.TO_NUM (column_value);
121 ELSIF (UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') THEN
122 new_references.fee_ci_sequence_number := IGS_GE_NUMBER.TO_NUM (column_value);
123 END IF;
124 IF ((UPPER (column_name) = 'RETENTION_PERCENTAGE') OR (column_name IS NULL)) THEN
125 IF ((new_references.retention_percentage < 0) OR (new_references.retention_percentage > 100.00)) THEN
126 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130 END IF;
131 IF ((UPPER (column_name) = 'RETENTION_AMOUNT') OR (column_name IS NULL)) THEN
132 IF ((new_references.retention_amount < 0)) THEN
133 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IF;
137 END IF;
138 IF ((UPPER (column_name) = 'DT_ALIAS') OR (column_name IS NULL)) THEN
139 IF (new_references.dt_alias <> UPPER (new_references.dt_alias)) THEN
140 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END IF;
144 END IF;
145 IF ((UPPER (column_name) = 'FEE_CAL_TYPE') OR (column_name IS NULL)) THEN
146 IF (new_references.fee_cal_type <> UPPER (new_references.fee_cal_type)) THEN
147 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152 IF ((UPPER (column_name) = 'S_RELATION_TYPE') OR (column_name IS NULL)) THEN
153 IF (new_references.s_relation_type <> UPPER (new_references.s_relation_type)) THEN
154 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 END IF;
158 END IF;
159 IF ((UPPER (column_name) = 'SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
160 IF ((new_references.sequence_number < 1) OR (new_references.sequence_number > 999999)) THEN
161 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
162 IGS_GE_MSG_STACK.ADD;
163 App_Exception.Raise_Exception;
164 END IF;
165 END IF;
166 IF ((UPPER (column_name) = 'DAI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
167 IF ((new_references.dai_sequence_number < 1) OR (new_references.dai_sequence_number > 999999)) THEN
168 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 END IF;
173 IF ((UPPER (column_name) = 'SCHEDULE_NUMBER') OR (column_name IS NULL)) THEN
174 IF ((new_references.schedule_number < 1) OR (new_references.schedule_number > 999999)) THEN
175 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179 END IF;
180 IF ((UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
181 IF ((new_references.fee_ci_sequence_number < 1) OR (new_references.fee_ci_sequence_number > 999999)) THEN
182 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END IF;
186 END IF;
187 END Check_Constraints;
188 PROCEDURE Check_Uniqueness AS
189 BEGIN
190 IF Get_UK1_For_Validation (
191 new_references.fee_cal_type,
192 new_references.fee_ci_sequence_number,
193 new_references.fee_type,
194 new_references.fee_cat,
195 new_references.schedule_number,
196 new_references.hist_start_dt
197 ) THEN
198 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
199 IGS_GE_MSG_STACK.ADD;
200 App_Exception.Raise_Exception;
201 END IF;
202 END Check_Uniqueness;
203 PROCEDURE Check_Parent_Existance AS
204 BEGIN
205 IF (((old_references.fee_cat = new_references.fee_cat) AND
206 (old_references.fee_cal_type = new_references.fee_cal_type) AND
207 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
208 ((new_references.fee_cat IS NULL) OR
209 (new_references.fee_cal_type IS NULL) OR
210 (new_references.fee_ci_sequence_number IS NULL))) THEN
211 NULL;
212 ELSE
213 IF NOT IGS_FI_F_CAT_CA_INST_PKG.Get_PK_For_Validation (
214 new_references.fee_cat,
215 new_references.fee_cal_type,
216 new_references.fee_ci_sequence_number
217 ) THEN
218 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
219 IGS_GE_MSG_STACK.ADD;
220 App_Exception.Raise_Exception;
221 END IF;
222 END IF;
223 IF (((old_references.fee_cat = new_references.fee_cat) AND
224 (old_references.fee_cal_type = new_references.fee_cal_type) AND
225 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
226 (old_references.fee_type = new_references.fee_type)) OR
227 ((new_references.fee_cat IS NULL) OR
228 (new_references.fee_cal_type IS NULL) OR
229 (new_references.fee_ci_sequence_number IS NULL) OR
230 (new_references.fee_type IS NULL))) THEN
231 NULL;
232 ELSE
233 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
234 new_references.fee_cat,
235 new_references.fee_cal_type,
236 new_references.fee_ci_sequence_number,
237 new_references.fee_type
238 ) THEN
239 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
240 IGS_GE_MSG_STACK.ADD;
241 App_Exception.Raise_Exception;
242 END IF;
243 END IF;
244 IF (((old_references.fee_type = new_references.fee_type) AND
245 (old_references.fee_cal_type = new_references.fee_cal_type) AND
246 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
247 ((new_references.fee_type IS NULL) OR
248 (new_references.fee_cal_type IS NULL) OR
249 (new_references.fee_ci_sequence_number IS NULL))) THEN
250 NULL;
251 ELSE
252 IF NOT IGS_FI_F_TYP_CA_INST_PKG.Get_PK_For_Validation (
253 new_references.fee_type,
254 new_references.fee_cal_type,
255 new_references.fee_ci_sequence_number
256 ) THEN
257 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
258 IGS_GE_MSG_STACK.ADD;
259 App_Exception.Raise_Exception;
260 END IF;
261 END IF;
262 END Check_Parent_Existance;
263 FUNCTION Get_PK_For_Validation (
264 x_fee_cal_type IN VARCHAR2,
265 x_fee_ci_sequence_number IN NUMBER,
266 x_s_relation_type IN VARCHAR2,
267 x_sequence_number IN NUMBER,
268 x_hist_start_dt IN DATE
269 ) RETURN BOOLEAN AS
270 CURSOR cur_rowid IS
271 SELECT rowid
272 FROM IGS_FI_F_RET_SCHD_HT_ALL
273 WHERE fee_cal_type = x_fee_cal_type
274 AND fee_ci_sequence_number = x_fee_ci_sequence_number
275 AND s_relation_type = x_s_relation_type
276 AND sequence_number = x_sequence_number
277 AND hist_start_dt = x_hist_start_dt
278 FOR UPDATE NOWAIT;
279 lv_rowid cur_rowid%RowType;
280 BEGIN
281 Open cur_rowid;
282 Fetch cur_rowid INTO lv_rowid;
283 IF (cur_rowid%FOUND) THEN
284 Close cur_rowid;
285 Return (TRUE);
286 ELSE
287 Close cur_rowid;
288 Return (FALSE);
289 END IF;
290 END Get_PK_For_Validation;
291 FUNCTION Get_UK1_For_Validation (
292 x_fee_cal_type IN VARCHAR2,
293 x_fee_ci_sequence_number IN NUMBER,
294 x_fee_type IN VARCHAR2,
295 x_fee_cat IN VARCHAR2,
296 x_schedule_number IN NUMBER,
297 x_hist_start_dt IN DATE
298 ) RETURN BOOLEAN AS
299 CURSOR cur_rowid IS
300 SELECT rowid
301 FROM IGS_FI_F_RET_SCHD_HT_ALL
302 WHERE fee_cal_type = x_fee_cal_type
303 AND fee_ci_sequence_number = x_fee_ci_sequence_number
304 AND fee_type = x_fee_type
305 AND fee_cat = x_fee_cat
306 AND schedule_number = x_schedule_number
307 AND hist_start_dt = x_hist_start_dt
308 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
309 FOR UPDATE NOWAIT;
310 lv_rowid cur_rowid%RowType;
311 BEGIN
312 Open cur_rowid;
313 Fetch cur_rowid INTO lv_rowid;
314 IF (cur_rowid%FOUND) THEN
315 Close cur_rowid;
316 Return (TRUE);
317 ELSE
318 Close cur_rowid;
319 Return (FALSE);
320 END IF;
321 END Get_UK1_For_Validation;
322 PROCEDURE GET_FK_IGS_FI_F_CAT_CA_INST (
323 x_fee_cat IN VARCHAR2,
324 x_fee_cal_type IN VARCHAR2,
325 x_fee_ci_sequence_number IN NUMBER
326 ) AS
327 CURSOR cur_rowid IS
328 SELECT rowid
329 FROM IGS_FI_F_RET_SCHD_HT_ALL
330 WHERE fee_cat = x_fee_cat
331 AND fee_cal_type = x_fee_cal_type
332 AND fee_ci_sequence_number = x_fee_ci_sequence_number ;
333 lv_rowid cur_rowid%RowType;
334 BEGIN
335 Open cur_rowid;
336 Fetch cur_rowid INTO lv_rowid;
337 IF (cur_rowid%FOUND) THEN
338 Close cur_rowid;
339 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FRTNSH_FCCI_FK');
340 IGS_GE_MSG_STACK.ADD;
341 App_Exception.Raise_Exception;
342 Return;
343 END IF;
344 Close cur_rowid;
345 END GET_FK_IGS_FI_F_CAT_CA_INST;
346
347 PROCEDURE Before_DML (
348 p_action IN VARCHAR2,
349 x_rowid IN VARCHAR2 DEFAULT NULL,
350 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
351 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
352 x_s_relation_type IN VARCHAR2 DEFAULT NULL,
353 x_sequence_number IN NUMBER DEFAULT NULL,
354 x_hist_start_dt IN DATE DEFAULT NULL,
355 x_hist_end_dt IN DATE DEFAULT NULL,
356 x_hist_who IN VARCHAR2 DEFAULT NULL,
357 x_fee_type IN VARCHAR2 DEFAULT NULL,
358 x_fee_cat IN VARCHAR2 DEFAULT NULL,
359 x_schedule_number IN NUMBER DEFAULT NULL,
360 x_dt_alias IN VARCHAR2 DEFAULT NULL,
361 x_dai_sequence_number IN NUMBER DEFAULT NULL,
362 x_retention_percentage IN NUMBER DEFAULT NULL,
363 x_retention_amount IN NUMBER DEFAULT NULL,
364 x_org_id IN NUMBER DEFAULT NULL,
365 x_creation_date IN DATE DEFAULT NULL,
366 x_created_by IN NUMBER DEFAULT NULL,
367 x_last_update_date IN DATE DEFAULT NULL,
368 x_last_updated_by IN NUMBER DEFAULT NULL,
369 x_last_update_login IN NUMBER DEFAULT NULL
370 ) AS
371 /*----------------------------------------------------------------------------
372 || Created By :
373 || Created On :
374 || Purpose :
375 || Known limitations, enhancements or remarks :
376 || Change History :
377 || Who When What
378 || (reverse chronological order - newest change first)
379 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_deduction_amount and its reference in
380 || from call to set_column_values.
381 ----------------------------------------------------------------------------*/
382 BEGIN
383 Set_Column_Values (
384 p_action,
385 x_rowid,
386 x_fee_cal_type,
387 x_fee_ci_sequence_number,
388 x_s_relation_type,
389 x_sequence_number,
390 x_hist_start_dt,
391 x_hist_end_dt,
392 x_hist_who,
393 x_fee_type,
394 x_fee_cat,
395 x_schedule_number,
396 x_dt_alias,
397 x_dai_sequence_number,
398 x_retention_percentage,
399 x_retention_amount,
400 x_org_id,
401 x_creation_date,
402 x_created_by,
403 x_last_update_date,
404 x_last_updated_by,
405 x_last_update_login
406 );
407 IF (p_action = 'INSERT') THEN
408 -- Call all the procedures related to Before Insert.
409 IF (Get_PK_For_Validation (
410 new_references.fee_cal_type,
411 new_references.fee_ci_sequence_number,
412 new_references.s_relation_type,
413 new_references.sequence_number,
414 new_references.hist_start_dt
415 )) THEN
416 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
417 IGS_GE_MSG_STACK.ADD;
418 App_Exception.Raise_Exception;
419 END IF;
420 Check_Uniqueness;
421 Check_Constraints;
422 Check_Parent_Existance;
423 ELSIF (p_action = 'UPDATE') THEN
424 -- Call all the procedures related to Before Update.
425 Null;
426 Check_Uniqueness;
427 Check_Constraints;
428 Check_Parent_Existance;
429 ELSIF (p_action = 'VALIDATE_INSERT') THEN
430 IF (Get_PK_For_Validation (
431 new_references.fee_cal_type,
432 new_references.fee_ci_sequence_number,
433 new_references.s_relation_type,
434 new_references.sequence_number,
435 new_references.hist_start_dt
436 )) THEN
437 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
438 IGS_GE_MSG_STACK.ADD;
439 App_Exception.Raise_Exception;
440 END IF;
441 Check_Uniqueness;
442 Check_Constraints;
443 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
444 Check_Uniqueness;
445 Check_Constraints;
446 END IF;
447 END Before_DML;
448 procedure INSERT_ROW (
449 X_ROWID in out NOCOPY VARCHAR2,
450 X_FEE_CAL_TYPE in VARCHAR2,
451 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
452 X_S_RELATION_TYPE in VARCHAR2,
453 X_SEQUENCE_NUMBER in NUMBER,
454 X_HIST_START_DT in DATE,
455 X_HIST_END_DT in DATE,
456 X_HIST_WHO in NUMBER,
457 X_FEE_TYPE in VARCHAR2,
458 X_FEE_CAT in VARCHAR2,
459 X_SCHEDULE_NUMBER in NUMBER,
460 X_DT_ALIAS in VARCHAR2,
461 X_DAI_SEQUENCE_NUMBER in NUMBER,
462 X_RETENTION_PERCENTAGE in NUMBER,
463 X_RETENTION_AMOUNT in NUMBER,
464 X_ORG_ID in NUMBER,
465 X_MODE in VARCHAR2 default 'R'
466 ) is
467 /*----------------------------------------------------------------------------
468 || Created By :
469 || Created On :
470 || Purpose :
471 || Known limitations, enhancements or remarks :
472 || Change History :
473 || Who When What
474 || (reverse chronological order - newest change first)
475 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_deduction_amount and its references
476 || (from call to before_dml and insert statement).
477 ----------------------------------------------------------------------------*/
478 cursor C is select ROWID from IGS_FI_F_RET_SCHD_HT_ALL
479 where FEE_CAL_TYPE = X_FEE_CAL_TYPE
480 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
481 and S_RELATION_TYPE = X_S_RELATION_TYPE
482 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
483 and HIST_START_DT = X_HIST_START_DT;
484 X_LAST_UPDATE_DATE DATE;
485 X_LAST_UPDATED_BY NUMBER;
486 X_LAST_UPDATE_LOGIN NUMBER;
487 begin
488 X_LAST_UPDATE_DATE := SYSDATE;
489 if(X_MODE = 'I') then
490 X_LAST_UPDATED_BY := 1;
491 X_LAST_UPDATE_LOGIN := 0;
492 elsif (X_MODE = 'R') then
493 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
494 if X_LAST_UPDATED_BY is NULL then
495 X_LAST_UPDATED_BY := -1;
496 end if;
497 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
498 if X_LAST_UPDATE_LOGIN is NULL then
499 X_LAST_UPDATE_LOGIN := -1;
500 end if;
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 Before_DML(
507 p_action=>'INSERT',
508 x_rowid=>X_ROWID,
509 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
510 x_dt_alias=>X_DT_ALIAS,
511 x_fee_cal_type=>X_FEE_CAL_TYPE,
512 x_fee_cat=>X_FEE_CAT,
513 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
514 x_fee_type=>X_FEE_TYPE,
515 x_hist_end_dt=>X_HIST_END_DT,
516 x_hist_start_dt=>X_HIST_START_DT,
517 x_hist_who=>X_HIST_WHO,
518 x_retention_amount=>X_RETENTION_AMOUNT,
519 x_retention_percentage=>X_RETENTION_PERCENTAGE,
520 x_s_relation_type=>X_S_RELATION_TYPE,
521 x_schedule_number=>X_SCHEDULE_NUMBER,
522 x_sequence_number=>X_SEQUENCE_NUMBER,
523 x_org_id=>igs_ge_gen_003.get_org_id,
524 x_creation_date=>X_LAST_UPDATE_DATE,
525 x_created_by=>X_LAST_UPDATED_BY,
526 x_last_update_date=>X_LAST_UPDATE_DATE,
527 x_last_updated_by=>X_LAST_UPDATED_BY,
528 x_last_update_login=>X_LAST_UPDATE_LOGIN
529 );
530 insert into IGS_FI_F_RET_SCHD_HT_ALL (
531 FEE_CAL_TYPE,
532 FEE_CI_SEQUENCE_NUMBER,
533 S_RELATION_TYPE,
534 SEQUENCE_NUMBER,
535 HIST_START_DT,
536 HIST_END_DT,
537 HIST_WHO,
538 FEE_TYPE,
539 FEE_CAT,
540 SCHEDULE_NUMBER,
541 DT_ALIAS,
542 DAI_SEQUENCE_NUMBER,
543 RETENTION_PERCENTAGE,
544 RETENTION_AMOUNT,
545 ORG_ID,
546 CREATION_DATE,
547 CREATED_BY,
548 LAST_UPDATE_DATE,
549 LAST_UPDATED_BY,
550 LAST_UPDATE_LOGIN
551 ) values (
552 NEW_REFERENCES.FEE_CAL_TYPE,
553 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
554 NEW_REFERENCES.S_RELATION_TYPE,
555 NEW_REFERENCES.SEQUENCE_NUMBER,
556 NEW_REFERENCES.HIST_START_DT,
557 NEW_REFERENCES.HIST_END_DT,
558 NEW_REFERENCES.HIST_WHO,
559 NEW_REFERENCES.FEE_TYPE,
560 NEW_REFERENCES.FEE_CAT,
561 NEW_REFERENCES.SCHEDULE_NUMBER,
562 NEW_REFERENCES.DT_ALIAS,
563 NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
564 NEW_REFERENCES.RETENTION_PERCENTAGE,
565 NEW_REFERENCES.RETENTION_AMOUNT,
566 NEW_REFERENCES.ORG_ID,
567 X_LAST_UPDATE_DATE,
568 X_LAST_UPDATED_BY,
569 X_LAST_UPDATE_DATE,
570 X_LAST_UPDATED_BY,
571 X_LAST_UPDATE_LOGIN
572 );
573 open c;
574 fetch c into X_ROWID;
575 if (c%notfound) then
576 close c;
577 raise no_data_found;
578 end if;
579 close c;
580 end INSERT_ROW;
581 procedure LOCK_ROW (
582 X_ROWID in VARCHAR2,
583 X_FEE_CAL_TYPE in VARCHAR2,
584 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
585 X_S_RELATION_TYPE in VARCHAR2,
586 X_SEQUENCE_NUMBER in NUMBER,
587 X_HIST_START_DT in DATE,
588 X_HIST_END_DT in DATE,
589 X_HIST_WHO in NUMBER,
590 X_FEE_TYPE in VARCHAR2,
591 X_FEE_CAT in VARCHAR2,
592 X_SCHEDULE_NUMBER in NUMBER,
593 X_DT_ALIAS in VARCHAR2,
594 X_DAI_SEQUENCE_NUMBER in NUMBER,
595 X_RETENTION_PERCENTAGE in NUMBER,
596 X_RETENTION_AMOUNT in NUMBER
597 ) is
598 /*----------------------------------------------------------------------------
599 || Created By :
600 || Created On :
601 || Purpose :
602 || Known limitations, enhancements or remarks :
603 || Change History :
604 || Who When What
605 || (reverse chronological order - newest change first)
606 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_payment_hierarchy_rank and its references
607 || (from cursor c1 and from if condition).
608 ----------------------------------------------------------------------------*/
609 cursor c1 is select
610 HIST_END_DT,
611 HIST_WHO,
612 FEE_TYPE,
613 FEE_CAT,
614 SCHEDULE_NUMBER,
615 DT_ALIAS,
616 DAI_SEQUENCE_NUMBER,
617 RETENTION_PERCENTAGE,
618 RETENTION_AMOUNT
619 from IGS_FI_F_RET_SCHD_HT_ALL
620 where ROWID=X_ROWID
621 for update nowait;
622 tlinfo c1%rowtype;
623 begin
624 open c1;
625 fetch c1 into tlinfo;
626 if (c1%notfound) then
627 close c1;
628 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
629 IGS_GE_MSG_STACK.ADD;
630 app_exception.raise_exception;
631 return;
632 end if;
633 close c1;
634 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
635 AND (tlinfo.HIST_WHO = X_HIST_WHO)
636 AND ((tlinfo.FEE_TYPE = X_FEE_TYPE)
637 OR ((tlinfo.FEE_TYPE is null)
638 AND (X_FEE_TYPE is null)))
639 AND ((tlinfo.FEE_CAT = X_FEE_CAT)
640 OR ((tlinfo.FEE_CAT is null)
641 AND (X_FEE_CAT is null)))
642 AND ((tlinfo.SCHEDULE_NUMBER = X_SCHEDULE_NUMBER)
643 OR ((tlinfo.SCHEDULE_NUMBER is null)
644 AND (X_SCHEDULE_NUMBER is null)))
645 AND ((tlinfo.DT_ALIAS = X_DT_ALIAS)
646 OR ((tlinfo.DT_ALIAS is null)
647 AND (X_DT_ALIAS is null)))
648 AND ((tlinfo.DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER)
649 OR ((tlinfo.DAI_SEQUENCE_NUMBER is null)
650 AND (X_DAI_SEQUENCE_NUMBER is null)))
651 AND ((tlinfo.RETENTION_PERCENTAGE = X_RETENTION_PERCENTAGE)
652 OR ((tlinfo.RETENTION_PERCENTAGE is null)
653 AND (X_RETENTION_PERCENTAGE is null)))
654 AND ((tlinfo.RETENTION_AMOUNT = X_RETENTION_AMOUNT)
655 OR ((tlinfo.RETENTION_AMOUNT is null)
656 AND (X_RETENTION_AMOUNT is null)))
657 ) then
658 null;
659 else
660 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
661 IGS_GE_MSG_STACK.ADD;
662 app_exception.raise_exception;
663 end if;
664 return;
665 end LOCK_ROW;
666 procedure UPDATE_ROW (
667 X_ROWID in VARCHAR2,
668 X_FEE_CAL_TYPE in VARCHAR2,
669 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
670 X_S_RELATION_TYPE in VARCHAR2,
671 X_SEQUENCE_NUMBER in NUMBER,
672 X_HIST_START_DT in DATE,
673 X_HIST_END_DT in DATE,
674 X_HIST_WHO in NUMBER,
675 X_FEE_TYPE in VARCHAR2,
676 X_FEE_CAT in VARCHAR2,
677 X_SCHEDULE_NUMBER in NUMBER,
678 X_DT_ALIAS in VARCHAR2,
679 X_DAI_SEQUENCE_NUMBER in NUMBER,
680 X_RETENTION_PERCENTAGE in NUMBER,
681 X_RETENTION_AMOUNT in NUMBER,
682 X_MODE in VARCHAR2 default 'R'
683 ) is
684 /*----------------------------------------------------------------------------
685 || Created By :
686 || Created On :
687 || Purpose :
688 || Known limitations, enhancements or remarks :
689 || Change History :
690 || Who When What
691 || (reverse chronological order - newest change first)
692 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_deduction_amount and its references
693 || (from call to before_dml and update statement).
694 ----------------------------------------------------------------------------*/
695 X_LAST_UPDATE_DATE DATE;
696 X_LAST_UPDATED_BY NUMBER;
697 X_LAST_UPDATE_LOGIN NUMBER;
698 begin
699 X_LAST_UPDATE_DATE := SYSDATE;
700 if(X_MODE = 'I') then
701 X_LAST_UPDATED_BY := 1;
702 X_LAST_UPDATE_LOGIN := 0;
703 elsif (X_MODE = 'R') then
704 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
705 if X_LAST_UPDATED_BY is NULL then
706 X_LAST_UPDATED_BY := -1;
707 end if;
708 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
709 if X_LAST_UPDATE_LOGIN is NULL then
710 X_LAST_UPDATE_LOGIN := -1;
711 end if;
712 else
713 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
714 IGS_GE_MSG_STACK.ADD;
715 app_exception.raise_exception;
716 end if;
717 Before_DML(
718 p_action=>'UPDATE',
719 x_rowid=>X_ROWID,
720 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
721 x_dt_alias=>X_DT_ALIAS,
722 x_fee_cal_type=>X_FEE_CAL_TYPE,
723 x_fee_cat=>X_FEE_CAT,
724 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
725 x_fee_type=>X_FEE_TYPE,
726 x_hist_end_dt=>X_HIST_END_DT,
727 x_hist_start_dt=>X_HIST_START_DT,
728 x_hist_who=>X_HIST_WHO,
729 x_retention_amount=>X_RETENTION_AMOUNT,
730 x_retention_percentage=>X_RETENTION_PERCENTAGE,
731 x_s_relation_type=>X_S_RELATION_TYPE,
732 x_schedule_number=>X_SCHEDULE_NUMBER,
733 x_sequence_number=>X_SEQUENCE_NUMBER,
734 x_creation_date=>X_LAST_UPDATE_DATE,
735 x_created_by=>X_LAST_UPDATED_BY,
736 x_last_update_date=>X_LAST_UPDATE_DATE,
737 x_last_updated_by=>X_LAST_UPDATED_BY,
738 x_last_update_login=>X_LAST_UPDATE_LOGIN
739 );
740 update IGS_FI_F_RET_SCHD_HT_ALL set
741 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
742 HIST_WHO = NEW_REFERENCES.HIST_WHO,
743 FEE_TYPE = NEW_REFERENCES.FEE_TYPE,
744 FEE_CAT = NEW_REFERENCES.FEE_CAT,
745 SCHEDULE_NUMBER = NEW_REFERENCES.SCHEDULE_NUMBER,
746 DT_ALIAS = NEW_REFERENCES.DT_ALIAS,
747 DAI_SEQUENCE_NUMBER = NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
748 RETENTION_PERCENTAGE = NEW_REFERENCES.RETENTION_PERCENTAGE,
749 RETENTION_AMOUNT = NEW_REFERENCES.RETENTION_AMOUNT,
750 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
751 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
752 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
753 where ROWID=X_ROWID
754 ;
755 if (sql%notfound) then
756 raise no_data_found;
757 end if;
758 end UPDATE_ROW;
759 procedure ADD_ROW (
760 X_ROWID in out NOCOPY VARCHAR2,
761 X_FEE_CAL_TYPE in VARCHAR2,
762 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
763 X_S_RELATION_TYPE in VARCHAR2,
764 X_SEQUENCE_NUMBER in NUMBER,
765 X_HIST_START_DT in DATE,
766 X_HIST_END_DT in DATE,
767 X_HIST_WHO in NUMBER,
768 X_FEE_TYPE in VARCHAR2,
769 X_FEE_CAT in VARCHAR2,
770 X_SCHEDULE_NUMBER in NUMBER,
771 X_DT_ALIAS in VARCHAR2,
772 X_DAI_SEQUENCE_NUMBER in NUMBER,
773 X_RETENTION_PERCENTAGE in NUMBER,
774 X_RETENTION_AMOUNT in NUMBER,
775 X_ORG_ID in NUMBER,
776 X_MODE in VARCHAR2 default 'R'
777 ) is
778 /*----------------------------------------------------------------------------
779 || Created By :
780 || Created On :
781 || Purpose :
782 || Known limitations, enhancements or remarks :
783 || Change History :
784 || Who When What
785 || (reverse chronological order - newest change first)
786 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_deduction_amount and its references
787 || (from calls to insert_row and update_row).
788 ----------------------------------------------------------------------------*/
789 cursor c1 is select rowid from IGS_FI_F_RET_SCHD_HT_ALL
790 where FEE_CAL_TYPE = X_FEE_CAL_TYPE
791 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
792 and S_RELATION_TYPE = X_S_RELATION_TYPE
793 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
794 and HIST_START_DT = X_HIST_START_DT
795 ;
796 begin
797 open c1;
798 fetch c1 into X_ROWID;
799 if (c1%notfound) then
800 close c1;
801 INSERT_ROW (
802 X_ROWID,
803 X_FEE_CAL_TYPE,
804 X_FEE_CI_SEQUENCE_NUMBER,
805 X_S_RELATION_TYPE,
806 X_SEQUENCE_NUMBER,
807 X_HIST_START_DT,
808 X_HIST_END_DT,
809 X_HIST_WHO,
810 X_FEE_TYPE,
811 X_FEE_CAT,
812 X_SCHEDULE_NUMBER,
813 X_DT_ALIAS,
814 X_DAI_SEQUENCE_NUMBER,
815 X_RETENTION_PERCENTAGE,
816 X_RETENTION_AMOUNT,
817 X_ORG_ID,
818 X_MODE);
819 return;
820 end if;
821 close c1;
822 UPDATE_ROW (
823 X_ROWID,
824 X_FEE_CAL_TYPE,
825 X_FEE_CI_SEQUENCE_NUMBER,
826 X_S_RELATION_TYPE,
827 X_SEQUENCE_NUMBER,
828 X_HIST_START_DT,
829 X_HIST_END_DT,
830 X_HIST_WHO,
831 X_FEE_TYPE,
832 X_FEE_CAT,
833 X_SCHEDULE_NUMBER,
834 X_DT_ALIAS,
835 X_DAI_SEQUENCE_NUMBER,
836 X_RETENTION_PERCENTAGE,
837 X_RETENTION_AMOUNT,
838 X_MODE);
839 end ADD_ROW;
840 procedure DELETE_ROW (
841 X_ROWID in VARCHAR2
842 ) is
843 begin
844 Before_DML (
845 p_action => 'DELETE',
846 x_rowid => X_ROWID
847 );
848 delete from IGS_FI_F_RET_SCHD_HT_ALL
849 where ROWID=X_ROWID;
850 if (sql%notfound) then
851 raise no_data_found;
852 end if;
853 end DELETE_ROW;
854 end IGS_FI_F_RET_SCHD_HT_PKG;