[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_FEE_CAT_CI_HT_PKG
Source
1 package body IGS_FI_FEE_CAT_CI_HT_PKG AS
2 /* $Header: IGSSI24B.pls 115.7 2002/11/29 03:44:10 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_FI_FEE_CAT_CI_HT_ALL%RowType;
5 new_references IGS_FI_FEE_CAT_CI_HT_ALL%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_fee_cat IN VARCHAR2 DEFAULT NULL,
10 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
11 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
12 x_hist_start_dt IN DATE DEFAULT NULL,
13 x_hist_end_dt IN DATE DEFAULT NULL,
14 x_hist_who IN VARCHAR2 DEFAULT NULL,
15 x_fee_cat_ci_status IN VARCHAR2 DEFAULT NULL,
16 x_start_dt_alias IN VARCHAR2 DEFAULT NULL,
17 x_start_dai_sequence_number IN NUMBER DEFAULT NULL,
18 x_end_dt_alias IN VARCHAR2 DEFAULT NULL,
19 x_end_dai_sequence_number IN NUMBER DEFAULT NULL,
20 x_retro_dt_alias IN VARCHAR2 DEFAULT NULL,
21 x_retro_dai_sequence_number IN NUMBER DEFAULT NULL,
22 x_org_id IN NUMBER DEFAULT NULL,
23 x_creation_date IN DATE DEFAULT NULL,
24 x_created_by IN NUMBER DEFAULT NULL,
25 x_last_update_date IN DATE DEFAULT NULL,
26 x_last_updated_by IN NUMBER DEFAULT NULL,
27 x_last_update_login IN NUMBER DEFAULT NULL
28 ) AS
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_FI_FEE_CAT_CI_HT_ALL
32 WHERE rowid = x_rowid;
33 BEGIN
34 l_rowid := x_rowid;
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 Close cur_old_ref_values;
41 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
42 IGS_GE_MSG_STACK.ADD;
43 App_Exception.Raise_Exception;
44 Return;
45 END IF;
46 Close cur_old_ref_values;
47 -- Populate New Values.
48 new_references.fee_cat := x_fee_cat;
49 new_references.fee_cal_type := x_fee_cal_type;
50 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
51 new_references.hist_start_dt := x_hist_start_dt;
52 new_references.hist_end_dt := x_hist_end_dt;
53 new_references.hist_who := x_hist_who;
54 new_references.fee_cat_ci_status := x_fee_cat_ci_status;
55 new_references.start_dt_alias := x_start_dt_alias;
56 new_references.start_dai_sequence_number := x_start_dai_sequence_number;
57 new_references.end_dt_alias := x_end_dt_alias;
58 new_references.end_dai_sequence_number := x_end_dai_sequence_number;
59 new_references.retro_dt_alias := x_retro_dt_alias;
60 new_references.retro_dai_sequence_number := x_retro_dai_sequence_number;
61 new_references.org_id := x_org_id ;
62 IF (p_action = 'UPDATE') THEN
63 new_references.creation_date := old_references.creation_date;
64 new_references.created_by := old_references.created_by;
65 ELSE
66 new_references.creation_date := x_creation_date;
67 new_references.created_by := x_created_by;
68 END IF;
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72 END Set_Column_Values;
73
74 PROCEDURE Check_Constraints (
75 Column_Name IN VARCHAR2 DEFAULT NULL,
76 Column_Value IN VARCHAR2 DEFAULT NULL
77 )AS
78 /*----------------------------------------------------------------------------
79 || Created By :
80 || Created On :
81 || Purpose :
82 || Known limitations, enhancements or remarks :
83 || Change History :
84 || Who When What
85 || (reverse chronological order - newest change first)
86 || vvutukur 20-May-2002 removed upper check constraint on fee_cat,
87 || fee_cat_ci_status(alias of fee_structure_status) columns.bug#2344826.
88 ----------------------------------------------------------------------------*/
89 BEGIN
90 IF Column_Name is NULL THEN
91 NULL;
92 ELSIF upper(Column_Name) = 'START_DAI_SEQUENCE_NUMBER' then
93 new_references.start_dai_sequence_number := igs_ge_number.to_num(Column_Value);
94 ELSIF upper(Column_Name) = 'RETRO_DAI_SEQUENCE_NUMBER' then
95 new_references.retro_dai_sequence_number := igs_ge_number.to_num(Column_Value);
96 ELSIF upper(Column_Name) = 'END_DAI_SEQUENCE_NUMBER' then
97 new_references.end_dai_sequence_number := igs_ge_number.to_num(Column_Value);
98 ELSIF upper(Column_Name) = 'FEE_CI_SEQUENCE_NUMBER' then
99 new_references.fee_ci_sequence_number := igs_ge_number.to_num(Column_Value);
100 ELSIF upper(Column_Name) = 'END_DT_ALIAS' then
101 new_references.end_dt_alias := Column_Value;
102 ELSIF upper(Column_Name) = 'FEE_CAL_TYPE' then
103 new_references.fee_cal_type := Column_Value;
104 ELSIF upper(Column_Name) = 'RETRO_DT_ALIAS' then
105 new_references.retro_dt_alias := Column_Value;
106 ELSIF upper(Column_Name) = 'START_DT_ALIAS' then
107 new_references.start_dt_alias := Column_Value;
108 END IF;
109 IF upper(Column_Name) = 'START_DAI_SEQUENCE_NUMBER' OR
110 column_name is NULL THEN
111 IF new_references.start_dai_sequence_number < 1 OR new_references.start_dai_sequence_number > 999999 THEN
112 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
113 IGS_GE_MSG_STACK.ADD;
114 App_Exception.Raise_Exception;
115 END IF;
116 END IF;
117 IF upper(Column_Name) = 'RETRO_DAI_SEQUENCE_NUMBER' OR
118 column_name is NULL THEN
119 IF new_references.retro_dai_sequence_number < 1 OR new_references.retro_dai_sequence_number > 999999 THEN
120 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
121 IGS_GE_MSG_STACK.ADD;
122 App_Exception.Raise_Exception;
123 END IF;
124 END IF;
125 IF upper(Column_Name) = 'END_DAI_SEQUENCE_NUMBER' OR
126 column_name is NULL THEN
127 IF new_references.end_dai_sequence_number < 1 OR new_references.end_dai_sequence_number > 999999 THEN
128 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
129 IGS_GE_MSG_STACK.ADD;
130 App_Exception.Raise_Exception;
131 END IF;
132 END IF;
133 IF upper(Column_Name) = 'FEE_CI_SEQUENCE_NUMBER' OR
134 column_name is NULL THEN
135 IF new_references.fee_ci_sequence_number < 1 OR new_references.fee_ci_sequence_number > 999999 THEN
136 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 END IF;
140 END IF;
141
142 IF upper(Column_Name) = 'END_DT_ALIAS' OR
143 column_name is NULL THEN
144 IF new_references.end_dt_alias <> UPPER(new_references.end_dt_alias) THEN
145 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END IF;
149 END IF;
150
151 IF upper(Column_Name) = 'FEE_CAL_TYPE' OR
152 column_name is NULL THEN
153 IF new_references.fee_cal_type <> UPPER(new_references.fee_cal_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
160 IF upper(Column_Name) = 'RETRO_DT_ALIAS' OR
161 column_name is NULL THEN
162 IF new_references.retro_dt_alias <> UPPER(new_references.retro_dt_alias) THEN
163 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 END IF;
167 END IF;
168 IF upper(Column_Name) = 'START_DT_ALIAS' OR
169 column_name is NULL THEN
170 IF new_references.start_dt_alias <> UPPER(new_references.start_dt_alias) THEN
171 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
172 IGS_GE_MSG_STACK.ADD;
173 App_Exception.Raise_Exception;
174 END IF;
175 END IF;
176 END Check_Constraints;
177
178
179 FUNCTION Get_PK_For_Validation (
180 x_fee_cat IN VARCHAR2,
181 x_fee_cal_type IN VARCHAR2,
182 x_fee_ci_sequence_number IN NUMBER,
183 x_hist_start_dt IN DATE
184 ) RETURN BOOLEAN AS
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM IGS_FI_FEE_CAT_CI_HT_ALL
188 WHERE fee_cat = x_fee_cat
189 AND fee_cal_type = x_fee_cal_type
190 AND fee_ci_sequence_number = x_fee_ci_sequence_number
191 AND hist_start_dt = x_hist_start_dt
192 FOR UPDATE NOWAIT;
193 lv_rowid cur_rowid%RowType;
194 BEGIN
195 Open cur_rowid;
196 Fetch cur_rowid INTO lv_rowid;
197 IF (cur_rowid%FOUND) THEN
198 Close cur_rowid;
199 Return (TRUE);
200 ELSE
201 Close cur_rowid;
202 Return (FALSE);
203 END IF;
204 END Get_PK_For_Validation;
205 PROCEDURE Before_DML (
206 p_action IN VARCHAR2,
207 x_rowid IN VARCHAR2 DEFAULT NULL,
208 x_fee_cat IN VARCHAR2 DEFAULT NULL,
209 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
210 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
211 x_hist_start_dt IN DATE DEFAULT NULL,
212 x_hist_end_dt IN DATE DEFAULT NULL,
213 x_hist_who IN VARCHAR2 DEFAULT NULL,
214 x_fee_cat_ci_status IN VARCHAR2 DEFAULT NULL,
215 x_start_dt_alias IN VARCHAR2 DEFAULT NULL,
216 x_start_dai_sequence_number IN NUMBER DEFAULT NULL,
217 x_end_dt_alias IN VARCHAR2 DEFAULT NULL,
218 x_end_dai_sequence_number IN NUMBER DEFAULT NULL,
219 x_retro_dt_alias IN VARCHAR2 DEFAULT NULL,
220 x_retro_dai_sequence_number IN NUMBER DEFAULT NULL,
221 x_org_id IN NUMBER DEFAULT NULL,
222 x_creation_date IN DATE DEFAULT NULL,
223 x_created_by IN NUMBER DEFAULT NULL,
224 x_last_update_date IN DATE DEFAULT NULL,
225 x_last_updated_by IN NUMBER DEFAULT NULL,
226 x_last_update_login IN NUMBER DEFAULT NULL
227 ) AS
228 BEGIN
229 Set_Column_Values (
230 p_action,
231 x_rowid,
232 x_fee_cat,
233 x_fee_cal_type,
234 x_fee_ci_sequence_number,
235 x_hist_start_dt,
236 x_hist_end_dt,
237 x_hist_who,
238 x_fee_cat_ci_status,
239 x_start_dt_alias,
240 x_start_dai_sequence_number,
241 x_end_dt_alias,
242 x_end_dai_sequence_number,
243 x_retro_dt_alias,
244 x_retro_dai_sequence_number,
245 x_org_id,
246 x_creation_date,
247 x_created_by,
248 x_last_update_date,
249 x_last_updated_by,
250 x_last_update_login
251 );
252 IF (p_action = 'INSERT') THEN
253 -- Call all the procedures related to Before Insert.
254 IF Get_PK_For_Validation (
255 new_references.fee_cat,
256 new_references.fee_cal_type,
257 new_references.fee_ci_sequence_number,
258 new_references.hist_start_dt
259 ) THEN
260 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
261 IGS_GE_MSG_STACK.ADD;
262 App_Exception.Raise_Exception;
263 END IF;
264 Check_Constraints;
265 ELSIF (p_action = 'UPDATE') THEN
266 -- Call all the procedures related to Before Update.
267 Check_Constraints;
268 ELSIF (p_action = 'VALIDATE_INSERT') THEN
269 -- Call all the procedures related to Before Insert.
270 IF Get_PK_For_Validation (
271 new_references.fee_cat,
272 new_references.fee_cal_type,
273 new_references.fee_ci_sequence_number,
274 new_references.hist_start_dt
275 ) THEN
276 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
277 IGS_GE_MSG_STACK.ADD;
278 App_Exception.Raise_Exception;
279 END IF;
280 Check_Constraints;
281 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
282 Check_Constraints;
283 END IF;
284 END Before_DML;
285 procedure INSERT_ROW (
286 X_ROWID in out NOCOPY VARCHAR2,
287 X_FEE_CAT in VARCHAR2,
288 X_FEE_CAL_TYPE in VARCHAR2,
289 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
290 X_HIST_START_DT in DATE,
291 X_HIST_END_DT in DATE,
292 X_HIST_WHO in NUMBER,
293 X_FEE_CAT_CI_STATUS in VARCHAR2,
294 X_START_DT_ALIAS in VARCHAR2,
295 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
296 X_END_DT_ALIAS in VARCHAR2,
297 X_END_DAI_SEQUENCE_NUMBER in NUMBER,
298 X_RETRO_DT_ALIAS in VARCHAR2,
299 X_RETRO_DAI_SEQUENCE_NUMBER in NUMBER,
300 X_ORG_ID in NUMBER,
301 X_MODE in VARCHAR2 default 'R'
302 ) AS
303 cursor C is select ROWID from IGS_FI_FEE_CAT_CI_HT_ALL
304 where FEE_CAT = X_FEE_CAT
305 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
306 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
307 and HIST_START_DT = X_HIST_START_DT;
308 X_LAST_UPDATE_DATE DATE;
309 X_LAST_UPDATED_BY NUMBER;
310 X_LAST_UPDATE_LOGIN NUMBER;
311 begin
312 X_LAST_UPDATE_DATE := SYSDATE;
313 if(X_MODE = 'I') then
314 X_LAST_UPDATED_BY := 1;
315 X_LAST_UPDATE_LOGIN := 0;
316 elsif (X_MODE = 'R') then
317 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
318 if X_LAST_UPDATED_BY is NULL then
319 X_LAST_UPDATED_BY := -1;
320 end if;
321 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
322 if X_LAST_UPDATE_LOGIN is NULL then
323 X_LAST_UPDATE_LOGIN := -1;
324 end if;
325 else
326 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
327 IGS_GE_MSG_STACK.ADD;
328 app_exception.raise_exception;
329 end if;
330 Before_DML(
331 p_action=>'INSERT',
332 x_rowid=>X_ROWID,
333 x_end_dai_sequence_number=>X_END_DAI_SEQUENCE_NUMBER,
334 x_end_dt_alias=>X_END_DT_ALIAS,
335 x_fee_cal_type=>X_FEE_CAL_TYPE,
336 x_fee_cat=>X_FEE_CAT,
337 x_fee_cat_ci_status=>X_FEE_CAT_CI_STATUS,
338 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
339 x_hist_end_dt=>X_HIST_END_DT,
340 x_hist_start_dt=>X_HIST_START_DT,
341 x_hist_who=>X_HIST_WHO,
342 x_retro_dai_sequence_number=>X_RETRO_DAI_SEQUENCE_NUMBER,
343 x_retro_dt_alias=>X_RETRO_DT_ALIAS,
344 x_start_dai_sequence_number=>X_START_DAI_SEQUENCE_NUMBER,
345 x_start_dt_alias=>X_START_DT_ALIAS,
346 x_org_id => igs_ge_gen_003.get_org_id,
347 x_creation_date=>X_LAST_UPDATE_DATE,
348 x_created_by=>X_LAST_UPDATED_BY,
349 x_last_update_date=>X_LAST_UPDATE_DATE,
350 x_last_updated_by=>X_LAST_UPDATED_BY,
351 x_last_update_login=>X_LAST_UPDATE_LOGIN
352 );
353 insert into IGS_FI_FEE_CAT_CI_HT_ALL (
354 FEE_CAT,
355 FEE_CAL_TYPE,
356 FEE_CI_SEQUENCE_NUMBER,
357 HIST_START_DT,
358 HIST_END_DT,
359 HIST_WHO,
360 FEE_CAT_CI_STATUS,
361 START_DT_ALIAS,
362 START_DAI_SEQUENCE_NUMBER,
363 END_DT_ALIAS,
364 END_DAI_SEQUENCE_NUMBER,
365 RETRO_DT_ALIAS,
366 RETRO_DAI_SEQUENCE_NUMBER,
367 ORG_ID,
368 CREATION_DATE,
369 CREATED_BY,
370 LAST_UPDATE_DATE,
371 LAST_UPDATED_BY,
372 LAST_UPDATE_LOGIN
373 ) values (
374 NEW_REFERENCES.FEE_CAT,
375 NEW_REFERENCES.FEE_CAL_TYPE,
376 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
377 NEW_REFERENCES.HIST_START_DT,
378 NEW_REFERENCES.HIST_END_DT,
379 NEW_REFERENCES.HIST_WHO,
380 NEW_REFERENCES.FEE_CAT_CI_STATUS,
381 NEW_REFERENCES.START_DT_ALIAS,
382 NEW_REFERENCES.START_DAI_SEQUENCE_NUMBER,
383 NEW_REFERENCES.END_DT_ALIAS,
384 NEW_REFERENCES.END_DAI_SEQUENCE_NUMBER,
388 X_LAST_UPDATE_DATE,
385 NEW_REFERENCES.RETRO_DT_ALIAS,
386 NEW_REFERENCES.RETRO_DAI_SEQUENCE_NUMBER,
387 NEW_REFERENCES.ORG_ID,
389 X_LAST_UPDATED_BY,
390 X_LAST_UPDATE_DATE,
391 X_LAST_UPDATED_BY,
392 X_LAST_UPDATE_LOGIN
393 );
394 open c;
395 fetch c into X_ROWID;
396 if (c%notfound) then
397 close c;
398 raise no_data_found;
399 end if;
400 close c;
401 end INSERT_ROW;
402 procedure LOCK_ROW (
403 X_ROWID in VARCHAR2,
404 X_FEE_CAT in VARCHAR2,
405 X_FEE_CAL_TYPE in VARCHAR2,
406 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
407 X_HIST_START_DT in DATE,
408 X_HIST_END_DT in DATE,
409 X_HIST_WHO in NUMBER,
410 X_FEE_CAT_CI_STATUS in VARCHAR2,
411 X_START_DT_ALIAS in VARCHAR2,
412 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
413 X_END_DT_ALIAS in VARCHAR2,
414 X_END_DAI_SEQUENCE_NUMBER in NUMBER,
415 X_RETRO_DT_ALIAS in VARCHAR2,
416 X_RETRO_DAI_SEQUENCE_NUMBER in NUMBER
417 ) AS
418 cursor c1 is select
419 HIST_END_DT,
420 HIST_WHO,
421 FEE_CAT_CI_STATUS,
422 START_DT_ALIAS,
423 START_DAI_SEQUENCE_NUMBER,
424 END_DT_ALIAS,
425 END_DAI_SEQUENCE_NUMBER,
426 RETRO_DT_ALIAS,
427 RETRO_DAI_SEQUENCE_NUMBER
428 from IGS_FI_FEE_CAT_CI_HT_ALL
429 where ROWID = X_ROWID
430 for update nowait;
431 tlinfo c1%rowtype;
432 begin
433 open c1;
434 fetch c1 into tlinfo;
435 if (c1%notfound) then
436 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
437 IGS_GE_MSG_STACK.ADD;
438 app_exception.raise_exception;
439 close c1;
440 return;
441 end if;
442 close c1;
443 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
444 AND (tlinfo.HIST_WHO = X_HIST_WHO)
445 AND ((tlinfo.FEE_CAT_CI_STATUS = X_FEE_CAT_CI_STATUS)
446 OR ((tlinfo.FEE_CAT_CI_STATUS is null)
447 AND (X_FEE_CAT_CI_STATUS is null)))
448 AND ((tlinfo.START_DT_ALIAS = X_START_DT_ALIAS)
449 OR ((tlinfo.START_DT_ALIAS is null)
450 AND (X_START_DT_ALIAS is null)))
451 AND ((tlinfo.START_DAI_SEQUENCE_NUMBER = X_START_DAI_SEQUENCE_NUMBER)
452 OR ((tlinfo.START_DAI_SEQUENCE_NUMBER is null)
453 AND (X_START_DAI_SEQUENCE_NUMBER is null)))
454 AND ((tlinfo.END_DT_ALIAS = X_END_DT_ALIAS)
455 OR ((tlinfo.END_DT_ALIAS is null)
456 AND (X_END_DT_ALIAS is null)))
457 AND ((tlinfo.END_DAI_SEQUENCE_NUMBER = X_END_DAI_SEQUENCE_NUMBER)
458 OR ((tlinfo.END_DAI_SEQUENCE_NUMBER is null)
459 AND (X_END_DAI_SEQUENCE_NUMBER is null)))
460 AND ((tlinfo.RETRO_DT_ALIAS = X_RETRO_DT_ALIAS)
461 OR ((tlinfo.RETRO_DT_ALIAS is null)
462 AND (X_RETRO_DT_ALIAS is null)))
463 AND ((tlinfo.RETRO_DAI_SEQUENCE_NUMBER = X_RETRO_DAI_SEQUENCE_NUMBER)
464 OR ((tlinfo.RETRO_DAI_SEQUENCE_NUMBER is null)
465 AND (X_RETRO_DAI_SEQUENCE_NUMBER is null)))
466 ) then
467 null;
468 else
469 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
470 IGS_GE_MSG_STACK.ADD;
471 app_exception.raise_exception;
472 end if;
473 return;
474 end LOCK_ROW;
475 procedure UPDATE_ROW (
476 X_ROWID in VARCHAR2,
477 X_FEE_CAT in VARCHAR2,
478 X_FEE_CAL_TYPE in VARCHAR2,
479 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
480 X_HIST_START_DT in DATE,
481 X_HIST_END_DT in DATE,
482 X_HIST_WHO in NUMBER,
483 X_FEE_CAT_CI_STATUS in VARCHAR2,
484 X_START_DT_ALIAS in VARCHAR2,
485 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
486 X_END_DT_ALIAS in VARCHAR2,
487 X_END_DAI_SEQUENCE_NUMBER in NUMBER,
488 X_RETRO_DT_ALIAS in VARCHAR2,
489 X_RETRO_DAI_SEQUENCE_NUMBER in NUMBER,
490 X_MODE in VARCHAR2 default 'R'
491 ) AS
492 X_LAST_UPDATE_DATE DATE;
493 X_LAST_UPDATED_BY NUMBER;
494 X_LAST_UPDATE_LOGIN NUMBER;
495 begin
496 X_LAST_UPDATE_DATE := SYSDATE;
497 if(X_MODE = 'I') then
498 X_LAST_UPDATED_BY := 1;
499 X_LAST_UPDATE_LOGIN := 0;
500 elsif (X_MODE = 'R') then
501 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
502 if X_LAST_UPDATED_BY is NULL then
503 X_LAST_UPDATED_BY := -1;
504 end if;
505 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
506 if X_LAST_UPDATE_LOGIN is NULL then
507 X_LAST_UPDATE_LOGIN := -1;
508 end if;
509 else
510 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
511 IGS_GE_MSG_STACK.ADD;
512 app_exception.raise_exception;
513 end if;
514 Before_DML(
515 p_action=>'UPDATE',
516 x_rowid=>X_ROWID,
517 x_end_dai_sequence_number=>X_END_DAI_SEQUENCE_NUMBER,
518 x_end_dt_alias=>X_END_DT_ALIAS,
519 x_fee_cal_type=>X_FEE_CAL_TYPE,
520 x_fee_cat=>X_FEE_CAT,
521 x_fee_cat_ci_status=>X_FEE_CAT_CI_STATUS,
522 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
523 x_hist_end_dt=>X_HIST_END_DT,
524 x_hist_start_dt=>X_HIST_START_DT,
525 x_hist_who=>X_HIST_WHO,
526 x_retro_dai_sequence_number=>X_RETRO_DAI_SEQUENCE_NUMBER,
527 x_retro_dt_alias=>X_RETRO_DT_ALIAS,
528 x_start_dai_sequence_number=>X_START_DAI_SEQUENCE_NUMBER,
529 x_start_dt_alias=>X_START_DT_ALIAS,
530 x_creation_date=>X_LAST_UPDATE_DATE,
534 x_last_update_login=>X_LAST_UPDATE_LOGIN
531 x_created_by=>X_LAST_UPDATED_BY,
532 x_last_update_date=>X_LAST_UPDATE_DATE,
533 x_last_updated_by=>X_LAST_UPDATED_BY,
535 );
536 update IGS_FI_FEE_CAT_CI_HT_ALL set
537 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
538 HIST_WHO = NEW_REFERENCES.HIST_WHO,
539 FEE_CAT_CI_STATUS = NEW_REFERENCES.FEE_CAT_CI_STATUS,
540 START_DT_ALIAS = NEW_REFERENCES.START_DT_ALIAS,
541 START_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.START_DAI_SEQUENCE_NUMBER,
542 END_DT_ALIAS = NEW_REFERENCES.END_DT_ALIAS,
543 END_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.END_DAI_SEQUENCE_NUMBER,
544 RETRO_DT_ALIAS = NEW_REFERENCES.RETRO_DT_ALIAS,
545 RETRO_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.RETRO_DAI_SEQUENCE_NUMBER,
546 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
547 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
548 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
549 where ROWID = X_ROWID;
550 if (sql%notfound) then
551 raise no_data_found;
552 end if;
553 end UPDATE_ROW;
554 procedure ADD_ROW (
555 X_ROWID in out NOCOPY VARCHAR2,
556 X_FEE_CAT in VARCHAR2,
557 X_FEE_CAL_TYPE in VARCHAR2,
558 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
559 X_HIST_START_DT in DATE,
560 X_HIST_END_DT in DATE,
561 X_HIST_WHO in NUMBER,
562 X_FEE_CAT_CI_STATUS in VARCHAR2,
563 X_START_DT_ALIAS in VARCHAR2,
564 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
565 X_END_DT_ALIAS in VARCHAR2,
566 X_END_DAI_SEQUENCE_NUMBER in NUMBER,
567 X_RETRO_DT_ALIAS in VARCHAR2,
568 X_RETRO_DAI_SEQUENCE_NUMBER in NUMBER,
569 X_ORG_ID in NUMBER,
570 X_MODE in VARCHAR2 default 'R'
571 ) AS
572 cursor c1 is select rowid from IGS_FI_FEE_CAT_CI_HT_ALL
573 where FEE_CAT = X_FEE_CAT
574 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
575 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
576 and HIST_START_DT = X_HIST_START_DT
577 ;
578 begin
579 open c1;
580 fetch c1 into X_ROWID;
581 if (c1%notfound) then
582 close c1;
583 INSERT_ROW (
584 X_ROWID,
585 X_FEE_CAT,
586 X_FEE_CAL_TYPE,
587 X_FEE_CI_SEQUENCE_NUMBER,
588 X_HIST_START_DT,
589 X_HIST_END_DT,
590 X_HIST_WHO,
591 X_FEE_CAT_CI_STATUS,
592 X_START_DT_ALIAS,
593 X_START_DAI_SEQUENCE_NUMBER,
594 X_END_DT_ALIAS,
595 X_END_DAI_SEQUENCE_NUMBER,
596 X_RETRO_DT_ALIAS,
597 X_RETRO_DAI_SEQUENCE_NUMBER,
598 X_ORG_ID,
599 X_MODE);
600 return;
601 end if;
602 close c1;
603 UPDATE_ROW (
604 X_ROWID,
605 X_FEE_CAT,
606 X_FEE_CAL_TYPE,
607 X_FEE_CI_SEQUENCE_NUMBER,
608 X_HIST_START_DT,
609 X_HIST_END_DT,
610 X_HIST_WHO,
611 X_FEE_CAT_CI_STATUS,
612 X_START_DT_ALIAS,
613 X_START_DAI_SEQUENCE_NUMBER,
614 X_END_DT_ALIAS,
615 X_END_DAI_SEQUENCE_NUMBER,
616 X_RETRO_DT_ALIAS,
617 X_RETRO_DAI_SEQUENCE_NUMBER,
618 X_MODE);
619 end ADD_ROW;
620 procedure DELETE_ROW (
621 X_ROWID in VARCHAR2
622 ) AS
623 begin
624 Before_DML (
625 p_action => 'DELETE',
626 x_rowid => X_ROWID
627 );
628 delete from IGS_FI_FEE_CAT_CI_HT_ALL
629 where ROWID = X_ROWID;
630 if (sql%notfound) then
631 raise no_data_found;
632 end if;
633 end DELETE_ROW;
634 end IGS_FI_FEE_CAT_CI_HT_PKG;