[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_COA_RATE_DET_PKG
Source
1 PACKAGE BODY igf_aw_coa_rate_det_pkg AS
2 /* $Header: IGFWI70B.pls 120.0 2005/06/01 14:36:38 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_coa_rate_det%ROWTYPE;
6 new_references igf_aw_coa_rate_det%ROWTYPE;
7
8 FUNCTION check_data(
9 p_ci_cal_type igf_aw_coa_rate_det.ci_cal_type%TYPE,
10 p_ci_sequence_number igf_aw_coa_rate_det.ci_sequence_number%TYPE,
11 p_item_code igf_aw_coa_rate_det.item_code%TYPE
12 ) RETURN BOOLEAN AS
13 CURSOR chk_data_uk ( cp_ci_cal_type igf_aw_coa_rate_det.ci_cal_type%TYPE,
14 cp_ci_sequence_number igf_aw_coa_rate_det.ci_sequence_number%TYPE,
15 cp_item_code igf_aw_coa_rate_det.item_code%TYPE
16 ) IS
17 SELECT ci_cal_type
18 FROM igf_aw_coa_rate_det
19 WHERE ci_cal_type = cp_ci_cal_type
20 AND ci_sequence_number = cp_ci_sequence_number
21 AND item_code = cp_item_code
22 GROUP BY ci_cal_type, ci_sequence_number, item_code,
23 pid_group_cd, org_unit_cd, program_type,
24 program_location_cd, program_cd, class_standing,
25 residency_status_code, housing_status_code,
26 attendance_type, attendance_mode, ld_cal_type,ld_sequence_number
27 HAVING COUNT(*) > 1 ;
28
29 lv_data chk_data_uk%ROWTYPE;
30 BEGIN
31 OPEN chk_data_uk(p_ci_cal_type, p_ci_sequence_number, p_item_code);
32 FETCH chk_data_uk INTO lv_data;
33 IF chk_data_uk%FOUND THEN
34 CLOSE chk_data_uk;
35 RETURN TRUE;
36 ELSE
37 CLOSE chk_data_uk;
38 RETURN FALSE;
39 END IF;
40
41 RETURN TRUE;
42 END check_data;
43
44 PROCEDURE set_column_values (
45 p_action IN VARCHAR2,
46 x_rowid IN VARCHAR2,
47 x_ci_cal_type IN VARCHAR2,
48 x_ci_sequence_number IN NUMBER,
49 x_item_code IN VARCHAR2,
50 x_rate_order_num IN NUMBER,
51 x_pid_group_cd IN VARCHAR2,
52 x_org_unit_cd IN VARCHAR2,
53 x_program_type IN VARCHAR2,
54 x_program_location_cd IN VARCHAR2,
55 x_program_cd IN VARCHAR2,
56 x_class_standing IN VARCHAR2,
57 x_residency_status_code IN VARCHAR2,
58 x_housing_status_code IN VARCHAR2,
59 x_attendance_type IN VARCHAR2,
60 x_attendance_mode IN VARCHAR2,
61 x_ld_cal_type IN VARCHAR2,
62 x_ld_sequence_number IN NUMBER,
63 x_mult_factor_code IN VARCHAR2,
64 x_mult_amount_num IN NUMBER,
65 x_creation_date IN DATE,
66 x_created_by IN NUMBER,
67 x_last_update_date IN DATE,
68 x_last_updated_by IN NUMBER,
69 x_last_update_login IN NUMBER
70 ) AS
71 /*
72 || Created By :
73 || Created On : 02-NOV-2004
74 || Purpose : Initialises the Old and New references for the columns of the table.
75 || Known limitations, enhancements or remarks :
76 || Change History :
77 || Who When What
78 || (reverse chronological order - newest change first)
79 */
80
81 CURSOR cur_old_ref_values IS
82 SELECT *
83 FROM igf_aw_coa_rate_det
84 WHERE rowid = x_rowid;
85
86 BEGIN
87
88 l_rowid := x_rowid;
89
90 -- Code for setting the Old and New Reference Values.
91 -- Populate Old Values.
92 OPEN cur_old_ref_values;
93 FETCH cur_old_ref_values INTO old_references;
94 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
95 CLOSE cur_old_ref_values;
96 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
97 igs_ge_msg_stack.add;
98 app_exception.raise_exception;
99 RETURN;
100 END IF;
101 CLOSE cur_old_ref_values;
102
103 -- Populate New Values.
104 new_references.ci_cal_type := x_ci_cal_type;
105 new_references.ci_sequence_number := x_ci_sequence_number;
106 new_references.item_code := x_item_code;
107 new_references.rate_order_num := x_rate_order_num;
108 new_references.pid_group_cd := x_pid_group_cd;
109 new_references.org_unit_cd := x_org_unit_cd;
110 new_references.program_type := x_program_type;
111 new_references.program_location_cd := x_program_location_cd;
112 new_references.program_cd := x_program_cd;
113 new_references.class_standing := x_class_standing;
114 new_references.residency_status_code := x_residency_status_code;
115 new_references.housing_status_code := x_housing_status_code;
116 new_references.attendance_type := x_attendance_type;
117 new_references.attendance_mode := x_attendance_mode;
118 new_references.ld_cal_type := x_ld_cal_type;
119 new_references.ld_sequence_number := x_ld_sequence_number;
120 new_references.mult_factor_code := x_mult_factor_code;
121 new_references.mult_amount_num := x_mult_amount_num;
122
123 IF (p_action = 'UPDATE') THEN
124 new_references.creation_date := old_references.creation_date;
125 new_references.created_by := old_references.created_by;
126 ELSE
127 new_references.creation_date := x_creation_date;
128 new_references.created_by := x_created_by;
129 END IF;
130
131 new_references.last_update_date := x_last_update_date;
132 new_references.last_updated_by := x_last_updated_by;
133 new_references.last_update_login := x_last_update_login;
134
135 END set_column_values;
136
137
138 PROCEDURE before_dml (
139 p_action IN VARCHAR2,
140 x_rowid IN VARCHAR2,
141 x_ci_cal_type IN VARCHAR2,
142 x_ci_sequence_number IN NUMBER,
143 x_item_code IN VARCHAR2,
144 x_rate_order_num IN NUMBER,
145 x_pid_group_cd IN VARCHAR2,
146 x_org_unit_cd IN VARCHAR2,
147 x_program_type IN VARCHAR2,
148 x_program_location_cd IN VARCHAR2,
149 x_program_cd IN VARCHAR2,
150 x_class_standing IN VARCHAR2,
151 x_residency_status_code IN VARCHAR2,
152 x_housing_status_code IN VARCHAR2,
153 x_attendance_type IN VARCHAR2,
154 x_attendance_mode IN VARCHAR2,
155 x_ld_cal_type IN VARCHAR2,
156 x_ld_sequence_number IN NUMBER,
157 x_mult_factor_code IN VARCHAR2,
158 x_mult_amount_num IN NUMBER,
159 x_creation_date IN DATE,
160 x_created_by IN NUMBER,
161 x_last_update_date IN DATE,
162 x_last_updated_by IN NUMBER,
163 x_last_update_login IN NUMBER
164 ) AS
165 /*
166 || Created By :
167 || Created On : 02-NOV-2004
168 || Purpose : Initialises the columns, Checks Constraints, Calls the
169 || Trigger Handlers for the table, before any DML operation.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175
176 -- Get
177
178 BEGIN
179
180 set_column_values (
181 p_action,
182 x_rowid,
183 x_ci_cal_type,
184 x_ci_sequence_number,
185 x_item_code,
186 x_rate_order_num,
187 x_pid_group_cd,
188 x_org_unit_cd,
189 x_program_type,
190 x_program_location_cd,
191 x_program_cd,
192 x_class_standing,
193 x_residency_status_code,
194 x_housing_status_code,
195 x_attendance_type,
196 x_attendance_mode,
197 x_ld_cal_type,
198 x_ld_sequence_number,
199 x_mult_factor_code,
200 x_mult_amount_num,
201 x_creation_date,
202 x_created_by,
203 x_last_update_date,
204 x_last_updated_by,
205 x_last_update_login
206 );
207
208 IF (p_action = 'INSERT') THEN
209 -- Call all the procedures related to Before Insert.
210 IF ( get_pk_for_validation( x_ci_cal_type, x_ci_sequence_number, x_item_code, x_rate_order_num)) THEN
211 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
212 igs_ge_msg_stack.add;
213 app_exception.raise_exception;
214 END IF;
215 ELSIF (p_action = 'VALIDATE_INSERT') THEN
216 -- Call all the procedures related to Before Insert.
217 IF ( get_pk_for_validation ( x_ci_cal_type, x_ci_sequence_number, x_item_code, x_rate_order_num) ) THEN
218 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
219 igs_ge_msg_stack.add;
220 app_exception.raise_exception;
221 END IF;
222 END IF;
223
224 END before_dml;
225
226
227 FUNCTION get_pk_for_validation (
228 x_ci_cal_type IN VARCHAR2,
229 x_ci_sequence_number IN NUMBER,
230 x_item_code IN VARCHAR2,
231 x_rate_order_num IN NUMBER
232 ) RETURN BOOLEAN AS
233 /*
234 || Created By :
235 || Created On : 08-OCT-2004
236 || Purpose : Validates the Primary Key of the table.
237 || Known limitations, enhancements or remarks :
238 || Change History :
239 || Who When What
240 || (reverse chronological order - newest change first)
241 */
242 CURSOR cur_rowid IS
243 SELECT rowid
244 FROM igf_aw_coa_rate_det
245 WHERE ci_cal_type = x_ci_cal_type
246 AND ci_sequence_number = x_ci_sequence_number
247 AND item_code = x_item_code
248 AND rate_order_num = x_rate_order_num
249 FOR UPDATE NOWAIT;
250
251 lv_rowid cur_rowid%RowType;
252
253 BEGIN
254
255 OPEN cur_rowid;
256 FETCH cur_rowid INTO lv_rowid;
257 IF (cur_rowid%FOUND) THEN
258 CLOSE cur_rowid;
259 RETURN(TRUE);
260 ELSE
261 CLOSE cur_rowid;
262 RETURN(FALSE);
263 END IF;
264
265 END get_pk_for_validation;
266
267
268 PROCEDURE insert_row (
269 x_rowid IN OUT NOCOPY VARCHAR2,
270 x_ci_cal_type IN VARCHAR2,
271 x_ci_sequence_number IN NUMBER,
272 x_item_code IN VARCHAR2,
273 x_rate_order_num IN NUMBER,
274 x_pid_group_cd IN VARCHAR2,
275 x_org_unit_cd IN VARCHAR2,
276 x_program_type IN VARCHAR2,
277 x_program_location_cd IN VARCHAR2,
278 x_program_cd IN VARCHAR2,
279 x_class_standing IN VARCHAR2,
280 x_residency_status_code IN VARCHAR2,
281 x_housing_status_code IN VARCHAR2,
282 x_attendance_type IN VARCHAR2,
283 x_attendance_mode IN VARCHAR2,
284 x_ld_cal_type IN VARCHAR2,
285 x_ld_sequence_number IN NUMBER,
286 x_mult_factor_code IN VARCHAR2,
287 x_mult_amount_num IN NUMBER,
288 x_mode IN VARCHAR2
289 ) AS
290 /*
291 || Created By :
292 || Created On : 02-NOV-2004
293 || Purpose : Handles the INSERT DML logic for the table.
294 || Known limitations, enhancements or remarks :
295 || Change History :
296 || Who When What
297 || (reverse chronological order - newest change first)
298 */
299
300 x_last_update_date DATE;
301 x_last_updated_by NUMBER;
302 x_last_update_login NUMBER;
303
304 BEGIN
305
306 x_last_update_date := SYSDATE;
307 IF (x_mode = 'I') THEN
308 x_last_updated_by := 1;
309 x_last_update_login := 0;
310 ELSIF (x_mode = 'R') THEN
311 x_last_updated_by := fnd_global.user_id;
312 IF (x_last_updated_by IS NULL) THEN
313 x_last_updated_by := -1;
314 END IF;
315 x_last_update_login := fnd_global.login_id;
316 IF (x_last_update_login IS NULL) THEN
317 x_last_update_login := -1;
318 END IF;
319 ELSE
320 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
321 fnd_message.set_token ('ROUTINE', 'IGF_AW_COA_RATE_DET_PKG.INSERT_ROW');
322 igs_ge_msg_stack.add;
323 app_exception.raise_exception;
324 END IF;
325
326 before_dml(
327 p_action => 'INSERT',
328 x_rowid => x_rowid,
329 x_ci_cal_type => x_ci_cal_type,
330 x_ci_sequence_number => x_ci_sequence_number,
331 x_item_code => x_item_code,
332 x_rate_order_num => x_rate_order_num,
333 x_pid_group_cd => x_pid_group_cd,
334 x_org_unit_cd => x_org_unit_cd,
335 x_program_type => x_program_type,
336 x_program_location_cd => x_program_location_cd,
340 x_housing_status_code => x_housing_status_code,
337 x_program_cd => x_program_cd,
338 x_class_standing => x_class_standing,
339 x_residency_status_code => x_residency_status_code,
341 x_attendance_type => x_attendance_type,
342 x_attendance_mode => x_attendance_mode,
343 x_ld_cal_type => x_ld_cal_type,
344 x_ld_sequence_number => x_ld_sequence_number,
345 x_mult_factor_code => x_mult_factor_code,
346 x_mult_amount_num => x_mult_amount_num,
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
354 INSERT INTO igf_aw_coa_rate_det (
355 ci_cal_type,
356 ci_sequence_number,
357 item_code,
358 rate_order_num,
359 pid_group_cd,
360 org_unit_cd,
361 program_type,
362 program_location_cd,
363 program_cd,
364 class_standing,
365 residency_status_code,
366 housing_status_code,
367 attendance_type,
368 attendance_mode,
369 ld_cal_type,
370 ld_sequence_number,
371 mult_factor_code,
372 mult_amount_num,
373 creation_date,
374 created_by,
375 last_update_date,
376 last_updated_by,
377 last_update_login
378 ) VALUES (
379 new_references.ci_cal_type,
380 new_references.ci_sequence_number,
381 new_references.item_code,
382 new_references.rate_order_num,
383 new_references.pid_group_cd,
384 new_references.org_unit_cd,
385 new_references.program_type,
386 new_references.program_location_cd,
387 new_references.program_cd,
388 new_references.class_standing,
389 new_references.residency_status_code,
390 new_references.housing_status_code,
391 new_references.attendance_type,
392 new_references.attendance_mode,
393 new_references.ld_cal_type,
394 new_references.ld_sequence_number,
395 new_references.mult_factor_code,
396 new_references.mult_amount_num,
397 x_last_update_date,
398 x_last_updated_by,
399 x_last_update_date,
400 x_last_updated_by,
401 x_last_update_login
402 ) RETURNING ROWID INTO x_rowid;
403
404 IF check_data(new_references.ci_cal_type,
405 new_references.ci_sequence_number,
406 new_references.item_code
407 ) THEN
408 fnd_message.set_name('IGF','IGF_AW_DUP_COA_RATE_DATA');
409 igs_ge_msg_stack.add;
410 app_exception.raise_exception;
411 END IF;
412
413 END insert_row;
414
415
416 PROCEDURE lock_row (
417 x_rowid IN VARCHAR2,
418 x_ci_cal_type IN VARCHAR2,
419 x_ci_sequence_number IN NUMBER,
420 x_item_code IN VARCHAR2,
421 x_rate_order_num IN NUMBER,
422 x_pid_group_cd IN VARCHAR2,
423 x_org_unit_cd IN VARCHAR2,
424 x_program_type IN VARCHAR2,
425 x_program_location_cd IN VARCHAR2,
426 x_program_cd IN VARCHAR2,
427 x_class_standing IN VARCHAR2,
428 x_residency_status_code IN VARCHAR2,
429 x_housing_status_code IN VARCHAR2,
430 x_attendance_type IN VARCHAR2,
431 x_attendance_mode IN VARCHAR2,
432 x_ld_cal_type IN VARCHAR2,
433 x_ld_sequence_number IN NUMBER,
434 x_mult_factor_code IN VARCHAR2,
435 x_mult_amount_num IN NUMBER
436 ) AS
437 /*
438 || Created By :
439 || Created On : 02-NOV-2004
440 || Purpose : Handles the LOCK mechanism for the table.
441 || Known limitations, enhancements or remarks :
442 || Change History :
443 || Who When What
444 || (reverse chronological order - newest change first)
445 */
446 CURSOR c1 IS
447 SELECT
448 ci_cal_type,
449 ci_sequence_number,
450 item_code,
451 rate_order_num,
452 pid_group_cd,
453 org_unit_cd,
454 program_type,
455 program_location_cd,
456 program_cd,
457 class_standing,
458 residency_status_code,
459 housing_status_code,
460 attendance_type,
461 attendance_mode,
462 ld_cal_type,
463 ld_sequence_number,
464 mult_factor_code,
465 mult_amount_num
466 FROM igf_aw_coa_rate_det
467 WHERE rowid = x_rowid
468 FOR UPDATE NOWAIT;
469
470 tlinfo c1%ROWTYPE;
471
472 BEGIN
473
474 OPEN c1;
475 FETCH c1 INTO tlinfo;
476 IF (c1%notfound) THEN
477 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
478 igs_ge_msg_stack.add;
479 CLOSE c1;
480 app_exception.raise_exception;
481 RETURN;
482 END IF;
483 CLOSE c1;
484
485 IF (
486 (tlinfo.ci_cal_type = x_ci_cal_type)
490 AND ((tlinfo.pid_group_cd = x_pid_group_cd) OR ((tlinfo.pid_group_cd IS NULL) AND (X_pid_group_cd IS NULL)))
487 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
488 AND (tlinfo.item_code = x_item_code)
489 AND (tlinfo.rate_order_num = x_rate_order_num)
491 AND ((tlinfo.org_unit_cd = x_org_unit_cd) OR ((tlinfo.org_unit_cd IS NULL) AND (X_org_unit_cd IS NULL)))
492 AND ((tlinfo.program_type = x_program_type) OR ((tlinfo.program_type IS NULL) AND (X_program_type IS NULL)))
493 AND ((tlinfo.program_location_cd = x_program_location_cd) OR ((tlinfo.program_location_cd IS NULL) AND (X_program_location_cd IS NULL)))
494 AND ((tlinfo.program_cd = x_program_cd) OR ((tlinfo.program_cd IS NULL) AND (X_program_cd IS NULL)))
495 AND ((tlinfo.class_standing = x_class_standing) OR ((tlinfo.class_standing IS NULL) AND (X_class_standing IS NULL)))
496 AND ((tlinfo.residency_status_code = x_residency_status_code) OR ((tlinfo.residency_status_code IS NULL) AND (X_residency_status_code IS NULL)))
497 AND ((tlinfo.housing_status_code = x_housing_status_code) OR ((tlinfo.housing_status_code IS NULL) AND (X_housing_status_code IS NULL)))
498 AND ((tlinfo.attendance_type = x_attendance_type) OR ((tlinfo.attendance_type IS NULL) AND (X_attendance_type IS NULL)))
499 AND ((tlinfo.attendance_mode = x_attendance_mode) OR ((tlinfo.attendance_mode IS NULL) AND (X_attendance_mode IS NULL)))
500 AND ((tlinfo.ld_cal_type = x_ld_cal_type) OR ((tlinfo.ld_cal_type IS NULL) AND (X_ld_cal_type IS NULL)))
501 AND ((tlinfo.ld_sequence_number = x_ld_sequence_number) OR ((tlinfo.ld_sequence_number IS NULL) AND (X_ld_sequence_number IS NULL)))
502 AND (tlinfo.mult_factor_code = x_mult_factor_code)
503 AND (tlinfo.mult_amount_num = x_mult_amount_num)
504 ) THEN
505 NULL;
506 ELSE
507 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
508 igs_ge_msg_stack.add;
509 app_exception.raise_exception;
510 END IF;
511
512 RETURN;
513
514 END lock_row;
515
516
517 PROCEDURE update_row (
518 x_rowid IN VARCHAR2,
519 x_ci_cal_type IN VARCHAR2,
520 x_ci_sequence_number IN NUMBER,
521 x_item_code IN VARCHAR2,
522 x_rate_order_num IN NUMBER,
523 x_pid_group_cd IN VARCHAR2,
524 x_org_unit_cd IN VARCHAR2,
525 x_program_type IN VARCHAR2,
526 x_program_location_cd IN VARCHAR2,
527 x_program_cd IN VARCHAR2,
528 x_class_standing IN VARCHAR2,
529 x_residency_status_code IN VARCHAR2,
530 x_housing_status_code IN VARCHAR2,
531 x_attendance_type IN VARCHAR2,
532 x_attendance_mode IN VARCHAR2,
533 x_ld_cal_type IN VARCHAR2,
534 x_ld_sequence_number IN NUMBER,
535 x_mult_factor_code IN VARCHAR2,
536 x_mult_amount_num IN NUMBER,
537 x_mode IN VARCHAR2
538 ) AS
539 /*
540 || Created By :
541 || Created On : 02-NOV-2004
542 || Purpose : Handles the UPDATE DML logic for the table.
543 || Known limitations, enhancements or remarks :
544 || Change History :
545 || Who When What
546 || (reverse chronological order - newest change first)
547 */
548 x_last_update_date DATE ;
549 x_last_updated_by NUMBER;
550 x_last_update_login NUMBER;
551
552 BEGIN
553
554 x_last_update_date := SYSDATE;
555 IF (X_MODE = 'I') THEN
556 x_last_updated_by := 1;
557 x_last_update_login := 0;
558 ELSIF (x_mode = 'R') THEN
559 x_last_updated_by := fnd_global.user_id;
560 IF x_last_updated_by IS NULL THEN
561 x_last_updated_by := -1;
562 END IF;
563 x_last_update_login := fnd_global.login_id;
564 IF (x_last_update_login IS NULL) THEN
565 x_last_update_login := -1;
566 END IF;
567 ELSE
568 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
569 fnd_message.set_token ('ROUTINE', 'IGF_AW_COA_RATE_DET_PKG.UPDATE_ROW');
570 igs_ge_msg_stack.add;
571 app_exception.raise_exception;
572 END IF;
573
574 before_dml(
575 p_action => 'UPDATE',
576 x_rowid => x_rowid,
577 x_ci_cal_type => x_ci_cal_type,
578 x_ci_sequence_number => x_ci_sequence_number,
579 x_item_code => x_item_code,
580 x_rate_order_num => x_rate_order_num,
581 x_pid_group_cd => x_pid_group_cd,
582 x_org_unit_cd => x_org_unit_cd,
583 x_program_type => x_program_type,
584 x_program_location_cd => x_program_location_cd,
585 x_program_cd => x_program_cd,
586 x_class_standing => x_class_standing,
587 x_residency_status_code => x_residency_status_code,
588 x_housing_status_code => x_housing_status_code,
589 x_attendance_type => x_attendance_type,
590 x_attendance_mode => x_attendance_mode,
591 x_ld_cal_type => x_ld_cal_type,
592 x_ld_sequence_number => x_ld_sequence_number,
593 x_mult_factor_code => x_mult_factor_code,
594 x_mult_amount_num => x_mult_amount_num,
595 x_creation_date => x_last_update_date,
599 x_last_update_login => x_last_update_login
596 x_created_by => x_last_updated_by,
597 x_last_update_date => x_last_update_date,
598 x_last_updated_by => x_last_updated_by,
600 );
601
602 UPDATE igf_aw_coa_rate_det
603 SET
604 ci_cal_type = new_references.ci_cal_type,
605 ci_sequence_number = new_references.ci_sequence_number,
606 item_code = new_references.item_code,
607 rate_order_num = new_references.rate_order_num,
608 pid_group_cd = new_references.pid_group_cd,
609 org_unit_cd = new_references.org_unit_cd,
610 program_type = new_references.program_type,
611 program_location_cd = new_references.program_location_cd,
612 program_cd = new_references.program_cd,
613 class_standing = new_references.class_standing,
614 residency_status_code = new_references.residency_status_code,
615 housing_status_code = new_references.housing_status_code,
616 attendance_type = new_references.attendance_type,
617 attendance_mode = new_references.attendance_mode,
618 ld_cal_type = new_references.ld_cal_type,
619 ld_sequence_number = new_references.ld_sequence_number,
620 mult_factor_code = new_references.mult_factor_code,
621 mult_amount_num = new_references.mult_amount_num,
622 last_update_date = x_last_update_date,
623 last_updated_by = x_last_updated_by,
624 last_update_login = x_last_update_login
625 WHERE rowid = x_rowid;
626
627 IF (SQL%NOTFOUND) THEN
628 RAISE NO_DATA_FOUND;
629 END IF;
630
631 IF check_data(new_references.ci_cal_type,
632 new_references.ci_sequence_number,
633 new_references.item_code
634 ) THEN
635 fnd_message.set_name('IGF','IGF_AW_DUP_COA_RATE_DATA');
636 igs_ge_msg_stack.add;
637 app_exception.raise_exception;
638 END IF;
639
640 END update_row;
641
642
643 PROCEDURE add_row (
644 x_rowid IN OUT NOCOPY VARCHAR2,
645 x_ci_cal_type IN VARCHAR2,
646 x_ci_sequence_number IN NUMBER,
647 x_item_code IN VARCHAR2,
648 x_rate_order_num IN NUMBER,
649 x_pid_group_cd IN VARCHAR2,
650 x_org_unit_cd IN VARCHAR2,
651 x_program_type IN VARCHAR2,
652 x_program_location_cd IN VARCHAR2,
653 x_program_cd IN VARCHAR2,
654 x_class_standing IN VARCHAR2,
655 x_residency_status_code IN VARCHAR2,
656 x_housing_status_code IN VARCHAR2,
657 x_attendance_type IN VARCHAR2,
658 x_attendance_mode IN VARCHAR2,
659 x_ld_cal_type IN VARCHAR2,
660 x_ld_sequence_number IN NUMBER,
661 x_mult_factor_code IN VARCHAR2,
662 x_mult_amount_num IN NUMBER,
663 x_mode IN VARCHAR2
664 ) AS
665 /*
666 || Created By :
667 || Created On : 02-NOV-2004
668 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
669 || Known limitations, enhancements or remarks :
670 || Change History :
671 || Who When What
672 || (reverse chronological order - newest change first)
673 */
674 CURSOR c1 IS
675 SELECT rowid
676 FROM igf_aw_coa_rate_det
677 WHERE ci_cal_type = x_ci_cal_type
678 AND ci_sequence_number = x_ci_sequence_number
679 AND item_code = x_item_code
680 AND rate_order_num = x_rate_order_num;
681
682 BEGIN
683
684 OPEN c1;
685 FETCH c1 INTO x_rowid;
686 IF (c1%NOTFOUND) THEN
687 CLOSE c1;
688
689 insert_row (
690 x_rowid,
691 x_ci_cal_type,
692 x_ci_sequence_number,
693 x_item_code,
694 x_rate_order_num,
695 x_pid_group_cd,
696 x_org_unit_cd,
697 x_program_type,
698 x_program_location_cd,
699 x_program_cd,
700 x_class_standing,
701 x_residency_status_code,
702 x_housing_status_code,
703 x_attendance_type,
704 x_attendance_mode,
705 x_ld_cal_type,
706 x_ld_sequence_number,
707 x_mult_factor_code,
708 x_mult_amount_num,
709 x_mode
710 );
711 RETURN;
712 END IF;
713 CLOSE c1;
714
715 update_row (
716 x_rowid,
717 x_ci_cal_type,
718 x_ci_sequence_number,
719 x_item_code,
720 x_rate_order_num,
721 x_pid_group_cd,
722 x_org_unit_cd,
723 x_program_type,
724 x_program_location_cd,
725 x_program_cd,
726 x_class_standing,
727 x_residency_status_code,
728 x_housing_status_code,
729 x_attendance_type,
730 x_attendance_mode,
731 x_ld_cal_type,
732 x_ld_sequence_number,
733 x_mult_factor_code,
734 x_mult_amount_num,
735 x_mode
736 );
737
738 END add_row;
739
740
741 PROCEDURE delete_row (
742 x_rowid IN VARCHAR2
743 ) AS
744 /*
745 || Created By :
746 || Created On : 02-NOV-2004
747 || Purpose : Handles the DELETE DML logic for the table.
748 || Known limitations, enhancements or remarks :
749 || Change History :
750 || Who When What
751 || (reverse chronological order - newest change first)
752 */
753 BEGIN
754
755 before_dml (
756 p_action => 'DELETE',
757 x_rowid => x_rowid
758 );
759
760 DELETE FROM igf_aw_coa_rate_det
761 WHERE rowid = x_rowid;
762
763 IF (SQL%NOTFOUND) THEN
764 RAISE NO_DATA_FOUND;
765 END IF;
766
767 END delete_row;
768
769
770 END igf_aw_coa_rate_det_pkg;