[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_AWD_CERT_RESPS_PKG
Source
1 PACKAGE BODY igf_aw_awd_cert_resps_pkg AS
2 /* $Header: IGFWI75B.pls 120.0 2005/09/09 17:13:40 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_awd_cert_resps%ROWTYPE;
6 new_references igf_aw_awd_cert_resps%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ci_cal_type IN VARCHAR2,
12 x_ci_sequence_number IN NUMBER,
13 x_award_prd_cd IN VARCHAR2,
14 x_base_id IN NUMBER,
15 x_awd_cert_code IN VARCHAR2,
16 x_response_txt IN VARCHAR2,
17 x_object_version_number IN NUMBER,
18 x_creation_date IN DATE,
19 x_created_by IN NUMBER,
20 x_last_update_date IN DATE,
21 x_last_updated_by IN NUMBER,
22 x_last_update_login IN NUMBER
23 ) AS
24 /*
25 || Created By :
26 || Created On : 05-JUL-2005
27 || Purpose : Initialises the Old and New references for the columns of the table.
28 || Known limitations, enhancements or remarks :
29 || Change History :
30 || Who When What
31 || (reverse chronological order - newest change first)
32 */
33
34 CURSOR cur_old_ref_values IS
35 SELECT *
36 FROM igf_aw_awd_cert_resps
37 WHERE rowid = x_rowid;
38
39 BEGIN
40
41 l_rowid := x_rowid;
42
43 -- Code for setting the Old and New Reference Values.
44 -- Populate Old Values.
45 OPEN cur_old_ref_values;
46 FETCH cur_old_ref_values INTO old_references;
47 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
48 CLOSE cur_old_ref_values;
49 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
50 igs_ge_msg_stack.add;
51 app_exception.raise_exception;
52 RETURN;
53 END IF;
54 CLOSE cur_old_ref_values;
55
56 -- Populate New Values.
57 new_references.ci_cal_type := x_ci_cal_type;
58 new_references.ci_sequence_number := x_ci_sequence_number;
59 new_references.award_prd_cd := x_award_prd_cd;
60 new_references.base_id := x_base_id;
61 new_references.awd_cert_code := x_awd_cert_code;
62 new_references.response_txt := x_response_txt;
63 new_references.object_version_number := x_object_version_number;
64
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72
73 new_references.last_update_date := x_last_update_date;
74 new_references.last_updated_by := x_last_updated_by;
75 new_references.last_update_login := x_last_update_login;
76
77 END set_column_values;
78
79
80 FUNCTION get_pk_for_validation (
81 x_ci_cal_type IN VARCHAR2,
82 x_ci_sequence_number IN NUMBER,
83 x_award_prd_cd IN VARCHAR2,
84 x_base_id IN NUMBER,
85 x_awd_cert_code IN VARCHAR2
86 ) RETURN BOOLEAN AS
87 /*
88 || Created By :
89 || Created On : 05-JUL-2005
90 || Purpose : Validates the Primary Key of the table.
91 || Known limitations, enhancements or remarks :
92 || Change History :
93 || Who When What
94 || (reverse chronological order - newest change first)
95 */
96 CURSOR cur_rowid IS
97 SELECT rowid
98 FROM igf_aw_awd_cert_resps
99 WHERE ci_cal_type = x_ci_cal_type
100 AND ci_sequence_number = x_ci_sequence_number
101 AND award_prd_cd = x_award_prd_cd
102 AND base_id = x_base_id
103 AND awd_cert_code = x_awd_cert_code
104 FOR UPDATE NOWAIT;
105
106 lv_rowid cur_rowid%RowType;
107
108 BEGIN
109
110 OPEN cur_rowid;
111 FETCH cur_rowid INTO lv_rowid;
112 IF (cur_rowid%FOUND) THEN
113 CLOSE cur_rowid;
114 RETURN(TRUE);
115 ELSE
116 CLOSE cur_rowid;
117 RETURN(FALSE);
118 END IF;
119
120 END get_pk_for_validation;
121
122 PROCEDURE after_dml(
123 p_action IN VARCHAR2
124 ) AS
125 /*
126 || Created By : veramach
127 || Created On : 26/August/2005
128 || Purpose : Initialises the columns, Checks Constraints, Calls the
129 || Trigger Handlers for the table, before any DML operation.
130 || Known limitations, enhancements or remarks :
131 || Change History :
132 || Who When What
133 || (reverse chronological order - newest change first)
134 */
135
136 CURSOR c_update_funds_auth(
137 cp_ci_cal_type igf_aw_awd_cert_resps.ci_cal_type%TYPE,
138 cp_ci_sequence_number igf_aw_awd_cert_resps.ci_sequence_number%TYPE,
139 cp_award_prd_cd igf_aw_awd_cert_resps.award_prd_cd%TYPE,
140 cp_awd_cert_code igf_aw_awd_cert_resps.awd_cert_code%TYPE
141 ) IS
142 SELECT update_funds_auth_flag
143 FROM igf_aw_award_certs
144 WHERE ci_cal_type = cp_ci_cal_type
145 AND ci_sequence_number = cp_ci_sequence_number
146 AND award_prd_cd = cp_award_prd_cd
147 AND awd_cert_code = cp_awd_cert_code;
148 l_funds_auth igf_aw_award_certs.update_funds_auth_flag%TYPE;
149
150 CURSOR c_pe_hz_parties(
151 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
152 ) IS
153 SELECT pe.ROWID row_id,
154 pe.*
155 FROM igs_pe_hz_parties pe,
156 igf_ap_fa_base_rec_all fa
157 WHERE pe.party_id = fa.person_id
158 AND fa.base_id = cp_base_id;
159 l_pe_hz_parties c_pe_hz_parties%ROWTYPE;
160
161 BEGIN
162 IF p_action = 'INSERT' THEN
163 l_funds_auth := NULL;
164 OPEN c_update_funds_auth(
165 new_references.ci_cal_type,
166 new_references.ci_sequence_number,
167 new_references.award_prd_cd,
168 new_references.awd_cert_code
169 );
170 FETCH c_update_funds_auth INTO l_funds_auth;
171 CLOSE c_update_funds_auth;
172
173 IF NVL(l_funds_auth,'N') = 'Y' THEN
174 /*
175 Update student's authorization flag
176 */
177 OPEN c_pe_hz_parties(new_references.base_id);
178 FETCH c_pe_hz_parties INTO l_pe_hz_parties;
179 CLOSE c_pe_hz_parties;
180
181 igs_pe_hz_parties_pkg.update_row(
182 x_rowid => l_pe_hz_parties.row_id,
183 x_party_id => l_pe_hz_parties.party_id,
184 x_deceased_ind => l_pe_hz_parties.deceased_ind,
185 x_archive_exclusion_ind => l_pe_hz_parties.archive_exclusion_ind,
186 x_archive_dt => l_pe_hz_parties.archive_dt,
187 x_purge_exclusion_ind => l_pe_hz_parties.purge_exclusion_ind,
188 x_purge_dt => l_pe_hz_parties.purge_dt,
189 x_oracle_username => l_pe_hz_parties.oracle_username,
190 x_proof_of_ins => l_pe_hz_parties.proof_of_ins,
191 x_proof_of_immu => l_pe_hz_parties.proof_of_immu,
192 x_level_of_qual => l_pe_hz_parties.level_of_qual,
193 x_military_service_reg => l_pe_hz_parties.military_service_reg,
194 x_veteran => l_pe_hz_parties.veteran,
195 x_institution_cd => l_pe_hz_parties.institution_cd,
196 x_oi_local_institution_ind => l_pe_hz_parties.oi_local_institution_ind,
197 x_oi_os_ind => l_pe_hz_parties.oi_os_ind,
198 x_oi_govt_institution_cd => l_pe_hz_parties.oi_govt_institution_cd,
199 x_oi_inst_control_type => l_pe_hz_parties.oi_inst_control_type,
200 x_oi_institution_type => l_pe_hz_parties.oi_institution_type,
201 x_oi_institution_status => l_pe_hz_parties.oi_institution_status,
202 x_ou_start_dt => l_pe_hz_parties.ou_start_dt,
203 x_ou_end_dt => l_pe_hz_parties.ou_end_dt,
204 x_ou_member_type => l_pe_hz_parties.ou_member_type,
205 x_ou_org_status => l_pe_hz_parties.ou_org_status,
206 x_ou_org_type => l_pe_hz_parties.ou_org_type,
207 x_inst_org_ind => l_pe_hz_parties.inst_org_ind,
208 x_inst_priority_cd => l_pe_hz_parties.inst_priority_cd,
209 x_inst_eps_code => l_pe_hz_parties.inst_eps_code,
210 x_inst_phone_country_code => l_pe_hz_parties.inst_phone_country_code,
211 x_inst_phone_area_code => l_pe_hz_parties.inst_phone_area_code,
212 x_inst_phone_number => l_pe_hz_parties.inst_phone_number,
213 x_adv_studies_classes => l_pe_hz_parties.adv_studies_classes,
214 x_honors_classes => l_pe_hz_parties.honors_classes,
215 x_class_size => l_pe_hz_parties.class_size,
216 x_sec_school_location_id => l_pe_hz_parties.sec_school_location_id,
217 x_percent_plan_higher_edu => l_pe_hz_parties.percent_plan_higher_edu,
218 x_fund_authorization => new_references.response_txt,
219 x_pe_info_verify_time => l_pe_hz_parties.pe_info_verify_time,
220 x_birth_city => l_pe_hz_parties.birth_city,
221 x_birth_country => l_pe_hz_parties.birth_country,
222 x_oss_org_unit_cd => l_pe_hz_parties.oss_org_unit_cd,
223 x_felony_convicted_flag => l_pe_hz_parties.felony_convicted_flag,
224 x_mode => 'R'
225 );
226
227 END IF;
228 END IF;
229
230 IF p_action = 'UPDATE' THEN
231 l_funds_auth := NULL;
232 OPEN c_update_funds_auth(
233 new_references.ci_cal_type,
234 new_references.ci_sequence_number,
235 new_references.award_prd_cd,
236 new_references.awd_cert_code
237 );
238 FETCH c_update_funds_auth INTO l_funds_auth;
239 CLOSE c_update_funds_auth;
240
241 IF NVL(l_funds_auth,'N') = 'Y' AND
242 old_references.response_txt IS NOT NULL AND
243 new_references.response_txt IS NOT NULL AND
244 old_references.response_txt <> new_references.response_txt THEN
245 /*
246 Update student's authorization flag
247 */
248 OPEN c_pe_hz_parties(new_references.base_id);
249 FETCH c_pe_hz_parties INTO l_pe_hz_parties;
250 CLOSE c_pe_hz_parties;
251
252 igs_pe_hz_parties_pkg.update_row(
253 x_rowid => l_pe_hz_parties.row_id,
254 x_party_id => l_pe_hz_parties.party_id,
255 x_deceased_ind => l_pe_hz_parties.deceased_ind,
256 x_archive_exclusion_ind => l_pe_hz_parties.archive_exclusion_ind,
257 x_archive_dt => l_pe_hz_parties.archive_dt,
258 x_purge_exclusion_ind => l_pe_hz_parties.purge_exclusion_ind,
259 x_purge_dt => l_pe_hz_parties.purge_dt,
260 x_oracle_username => l_pe_hz_parties.oracle_username,
261 x_proof_of_ins => l_pe_hz_parties.proof_of_ins,
262 x_proof_of_immu => l_pe_hz_parties.proof_of_immu,
263 x_level_of_qual => l_pe_hz_parties.level_of_qual,
264 x_military_service_reg => l_pe_hz_parties.military_service_reg,
268 x_oi_os_ind => l_pe_hz_parties.oi_os_ind,
265 x_veteran => l_pe_hz_parties.veteran,
266 x_institution_cd => l_pe_hz_parties.institution_cd,
267 x_oi_local_institution_ind => l_pe_hz_parties.oi_local_institution_ind,
269 x_oi_govt_institution_cd => l_pe_hz_parties.oi_govt_institution_cd,
270 x_oi_inst_control_type => l_pe_hz_parties.oi_inst_control_type,
271 x_oi_institution_type => l_pe_hz_parties.oi_institution_type,
272 x_oi_institution_status => l_pe_hz_parties.oi_institution_status,
273 x_ou_start_dt => l_pe_hz_parties.ou_start_dt,
274 x_ou_end_dt => l_pe_hz_parties.ou_end_dt,
275 x_ou_member_type => l_pe_hz_parties.ou_member_type,
276 x_ou_org_status => l_pe_hz_parties.ou_org_status,
277 x_ou_org_type => l_pe_hz_parties.ou_org_type,
278 x_inst_org_ind => l_pe_hz_parties.inst_org_ind,
279 x_inst_priority_cd => l_pe_hz_parties.inst_priority_cd,
280 x_inst_eps_code => l_pe_hz_parties.inst_eps_code,
281 x_inst_phone_country_code => l_pe_hz_parties.inst_phone_country_code,
282 x_inst_phone_area_code => l_pe_hz_parties.inst_phone_area_code,
283 x_inst_phone_number => l_pe_hz_parties.inst_phone_number,
284 x_adv_studies_classes => l_pe_hz_parties.adv_studies_classes,
285 x_honors_classes => l_pe_hz_parties.honors_classes,
286 x_class_size => l_pe_hz_parties.class_size,
287 x_sec_school_location_id => l_pe_hz_parties.sec_school_location_id,
288 x_percent_plan_higher_edu => l_pe_hz_parties.percent_plan_higher_edu,
289 x_fund_authorization => new_references.response_txt,
290 x_pe_info_verify_time => l_pe_hz_parties.pe_info_verify_time,
291 x_birth_city => l_pe_hz_parties.birth_city,
292 x_birth_country => l_pe_hz_parties.birth_country,
293 x_oss_org_unit_cd => l_pe_hz_parties.oss_org_unit_cd,
294 x_felony_convicted_flag => l_pe_hz_parties.felony_convicted_flag,
295 x_mode => 'R'
296 );
297 END IF;
298 END IF;
299 END after_dml;
300
301 PROCEDURE before_dml (
302 p_action IN VARCHAR2,
303 x_rowid IN VARCHAR2,
304 x_ci_cal_type IN VARCHAR2,
305 x_ci_sequence_number IN NUMBER,
306 x_award_prd_cd IN VARCHAR2,
307 x_base_id IN NUMBER,
308 x_awd_cert_code IN VARCHAR2,
309 x_response_txt IN VARCHAR2,
310 x_object_version_number IN NUMBER,
311 x_creation_date IN DATE,
312 x_created_by IN NUMBER,
313 x_last_update_date IN DATE,
314 x_last_updated_by IN NUMBER,
315 x_last_update_login IN NUMBER
316 ) AS
317 /*
318 || Created By :
319 || Created On : 05-JUL-2005
320 || Purpose : Initialises the columns, Checks Constraints, Calls the
321 || Trigger Handlers for the table, before any DML operation.
322 || Known limitations, enhancements or remarks :
323 || Change History :
324 || Who When What
325 || (reverse chronological order - newest change first)
326 */
327 BEGIN
328
329 set_column_values (
330 p_action,
331 x_rowid,
332 x_ci_cal_type,
333 x_ci_sequence_number,
334 x_award_prd_cd,
335 x_base_id,
336 x_awd_cert_code,
337 x_response_txt,
338 x_object_version_number,
339 x_creation_date,
340 x_created_by,
341 x_last_update_date,
342 x_last_updated_by,
343 x_last_update_login
344 );
345
346 IF (p_action = 'INSERT') THEN
347 -- Call all the procedures related to Before Insert.
348 IF ( get_pk_for_validation(
349 new_references.ci_cal_type,
350 new_references.ci_sequence_number,
351 new_references.award_prd_cd,
352 new_references.base_id,
353 new_references.awd_cert_code
354 )
355 ) THEN
356 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
357 igs_ge_msg_stack.add;
358 app_exception.raise_exception;
359 END IF;
360 ELSIF (p_action = 'VALIDATE_INSERT') THEN
361 -- Call all the procedures related to Before Insert.
362 IF ( get_pk_for_validation (
363 new_references.ci_cal_type,
364 new_references.ci_sequence_number,
365 new_references.award_prd_cd,
366 new_references.base_id,
367 new_references.awd_cert_code
368 )
372 app_exception.raise_exception;
369 ) THEN
370 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
371 igs_ge_msg_stack.add;
373 END IF;
374 END IF;
375
376 END before_dml;
377
378
379 PROCEDURE insert_row (
380 x_rowid IN OUT NOCOPY VARCHAR2,
381 x_ci_cal_type IN VARCHAR2,
382 x_ci_sequence_number IN NUMBER,
383 x_award_prd_cd IN VARCHAR2,
384 x_base_id IN NUMBER,
385 x_awd_cert_code IN VARCHAR2,
386 x_response_txt IN VARCHAR2,
387 x_object_version_number IN NUMBER,
388 x_mode IN VARCHAR2
389 ) AS
390 /*
391 || Created By :
392 || Created On : 05-JUL-2005
393 || Purpose : Handles the INSERT DML logic for the table.
394 || Known limitations, enhancements or remarks :
395 || Change History :
396 || Who When What
397 || (reverse chronological order - newest change first)
398 */
399
400 x_last_update_date DATE;
401 x_last_updated_by NUMBER;
402 x_last_update_login NUMBER;
403
404 BEGIN
405
406 x_last_update_date := SYSDATE;
407 IF (x_mode = 'I') THEN
408 x_last_updated_by := 1;
409 x_last_update_login := 0;
410 ELSIF (x_mode = 'R') THEN
411 x_last_updated_by := fnd_global.user_id;
412 IF (x_last_updated_by IS NULL) THEN
413 x_last_updated_by := -1;
414 END IF;
415 x_last_update_login := fnd_global.login_id;
416 IF (x_last_update_login IS NULL) THEN
417 x_last_update_login := -1;
418 END IF;
419 ELSE
420 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
421 fnd_message.set_token ('ROUTINE', 'IGF_AW_AWD_CERT_RESPS_PKG.INSERT_ROW');
422 igs_ge_msg_stack.add;
423 app_exception.raise_exception;
424 END IF;
425
426 before_dml(
427 p_action => 'INSERT',
428 x_rowid => x_rowid,
429 x_ci_cal_type => x_ci_cal_type,
430 x_ci_sequence_number => x_ci_sequence_number,
431 x_award_prd_cd => x_award_prd_cd,
432 x_base_id => x_base_id,
433 x_awd_cert_code => x_awd_cert_code,
434 x_response_txt => x_response_txt,
435 x_object_version_number => x_object_version_number,
436 x_creation_date => x_last_update_date,
437 x_created_by => x_last_updated_by,
438 x_last_update_date => x_last_update_date,
439 x_last_updated_by => x_last_updated_by,
440 x_last_update_login => x_last_update_login
441 );
442
443 INSERT INTO igf_aw_awd_cert_resps (
444 ci_cal_type,
445 ci_sequence_number,
446 award_prd_cd,
447 base_id,
448 awd_cert_code,
449 response_txt,
450 object_version_number,
451 creation_date,
452 created_by,
453 last_update_date,
454 last_updated_by,
455 last_update_login
456 ) VALUES (
457 new_references.ci_cal_type,
458 new_references.ci_sequence_number,
459 new_references.award_prd_cd,
460 new_references.base_id,
461 new_references.awd_cert_code,
462 new_references.response_txt,
463 new_references.object_version_number,
464 x_last_update_date,
465 x_last_updated_by,
466 x_last_update_date,
467 x_last_updated_by,
468 x_last_update_login
469 ) RETURNING ROWID INTO x_rowid;
470
471 after_dml(
472 p_action => 'INSERT'
473 );
474 END insert_row;
475
476
477 PROCEDURE lock_row (
478 x_rowid IN VARCHAR2,
479 x_ci_cal_type IN VARCHAR2,
480 x_ci_sequence_number IN NUMBER,
481 x_award_prd_cd IN VARCHAR2,
482 x_base_id IN NUMBER,
483 x_awd_cert_code IN VARCHAR2,
484 x_response_txt IN VARCHAR2,
485 x_object_version_number IN NUMBER
486 ) AS
487 /*
488 || Created By :
489 || Created On : 05-JUL-2005
490 || Purpose : Handles the LOCK mechanism for the table.
491 || Known limitations, enhancements or remarks :
492 || Change History :
493 || Who When What
494 || (reverse chronological order - newest change first)
495 */
496 CURSOR c1 IS
497 SELECT
498 response_txt,
499 object_version_number
500 FROM igf_aw_awd_cert_resps
501 WHERE rowid = x_rowid
502 FOR UPDATE NOWAIT;
503
504 tlinfo c1%ROWTYPE;
505
506 BEGIN
507
508 OPEN c1;
509 FETCH c1 INTO tlinfo;
510 IF (c1%notfound) THEN
511 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
512 igs_ge_msg_stack.add;
513 CLOSE c1;
514 app_exception.raise_exception;
515 RETURN;
516 END IF;
517 CLOSE c1;
518
519 IF (
520 (tlinfo.response_txt = x_response_txt)
521 AND (tlinfo.object_version_number = x_object_version_number)
522 ) THEN
523 NULL;
524 ELSE
528 END IF;
525 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
526 igs_ge_msg_stack.add;
527 app_exception.raise_exception;
529
530 RETURN;
531
532 END lock_row;
533
534
535 PROCEDURE update_row (
536 x_rowid IN VARCHAR2,
537 x_ci_cal_type IN VARCHAR2,
538 x_ci_sequence_number IN NUMBER,
539 x_award_prd_cd IN VARCHAR2,
540 x_base_id IN NUMBER,
541 x_awd_cert_code IN VARCHAR2,
542 x_response_txt IN VARCHAR2,
543 x_object_version_number IN NUMBER,
544 x_mode IN VARCHAR2
545 ) AS
546 /*
547 || Created By :
548 || Created On : 05-JUL-2005
549 || Purpose : Handles the UPDATE DML logic for the table.
550 || Known limitations, enhancements or remarks :
551 || Change History :
552 || Who When What
553 || (reverse chronological order - newest change first)
554 */
555 x_last_update_date DATE ;
556 x_last_updated_by NUMBER;
557 x_last_update_login NUMBER;
558
559 BEGIN
560
561 x_last_update_date := SYSDATE;
562 IF (X_MODE = 'I') THEN
563 x_last_updated_by := 1;
564 x_last_update_login := 0;
565 ELSIF (x_mode = 'R') THEN
566 x_last_updated_by := fnd_global.user_id;
567 IF x_last_updated_by IS NULL THEN
568 x_last_updated_by := -1;
569 END IF;
570 x_last_update_login := fnd_global.login_id;
571 IF (x_last_update_login IS NULL) THEN
572 x_last_update_login := -1;
573 END IF;
574 ELSE
575 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
576 fnd_message.set_token ('ROUTINE', 'IGF_AW_AWD_CERT_RESPS_PKG.UPDATE_ROW');
577 igs_ge_msg_stack.add;
578 app_exception.raise_exception;
579 END IF;
580
581 before_dml(
582 p_action => 'UPDATE',
583 x_rowid => x_rowid,
584 x_ci_cal_type => x_ci_cal_type,
585 x_ci_sequence_number => x_ci_sequence_number,
586 x_award_prd_cd => x_award_prd_cd,
587 x_base_id => x_base_id,
588 x_awd_cert_code => x_awd_cert_code,
589 x_response_txt => x_response_txt,
590 x_object_version_number => x_object_version_number,
591 x_creation_date => x_last_update_date,
592 x_created_by => x_last_updated_by,
593 x_last_update_date => x_last_update_date,
594 x_last_updated_by => x_last_updated_by,
595 x_last_update_login => x_last_update_login
596 );
597
598 UPDATE igf_aw_awd_cert_resps
599 SET
600 response_txt = new_references.response_txt,
601 object_version_number = new_references.object_version_number,
602 last_update_date = x_last_update_date,
603 last_updated_by = x_last_updated_by,
604 last_update_login = x_last_update_login
605 WHERE rowid = x_rowid;
606
607 IF (SQL%NOTFOUND) THEN
608 RAISE NO_DATA_FOUND;
609 END IF;
610
611 after_dml(
612 p_action => 'UPDATE'
613 );
614
615 END update_row;
616
617
618 PROCEDURE add_row (
619 x_rowid IN OUT NOCOPY VARCHAR2,
620 x_ci_cal_type IN VARCHAR2,
621 x_ci_sequence_number IN NUMBER,
622 x_award_prd_cd IN VARCHAR2,
623 x_base_id IN NUMBER,
624 x_awd_cert_code IN VARCHAR2,
625 x_response_txt IN VARCHAR2,
626 x_object_version_number IN NUMBER,
627 x_mode IN VARCHAR2
628 ) AS
629 /*
630 || Created By :
631 || Created On : 05-JUL-2005
632 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
633 || Known limitations, enhancements or remarks :
634 || Change History :
635 || Who When What
636 || (reverse chronological order - newest change first)
637 */
638 CURSOR c1 IS
639 SELECT rowid
640 FROM igf_aw_awd_cert_resps
641 WHERE ci_cal_type = x_ci_cal_type
642 AND ci_sequence_number = x_ci_sequence_number
643 AND award_prd_cd = x_award_prd_cd
644 AND base_id = x_base_id
645 AND awd_cert_code = x_awd_cert_code;
646
647 BEGIN
648
649 OPEN c1;
650 FETCH c1 INTO x_rowid;
651 IF (c1%NOTFOUND) THEN
652 CLOSE c1;
653
654 insert_row (
655 x_rowid,
656 x_ci_cal_type,
657 x_ci_sequence_number,
658 x_award_prd_cd,
659 x_base_id,
660 x_awd_cert_code,
661 x_response_txt,
662 x_object_version_number,
663 x_mode
664 );
665 RETURN;
666 END IF;
667 CLOSE c1;
668
669 update_row (
670 x_rowid,
671 x_ci_cal_type,
672 x_ci_sequence_number,
673 x_award_prd_cd,
674 x_base_id,
678 x_mode
675 x_awd_cert_code,
676 x_response_txt,
677 x_object_version_number,
679 );
680
681 END add_row;
682
683
684 PROCEDURE delete_row (
685 x_rowid IN VARCHAR2
686 ) AS
687 /*
688 || Created By :
689 || Created On : 05-JUL-2005
690 || Purpose : Handles the DELETE DML logic for the table.
691 || Known limitations, enhancements or remarks :
692 || Change History :
693 || Who When What
694 || (reverse chronological order - newest change first)
695 */
696 BEGIN
697
698 before_dml (
699 p_action => 'DELETE',
700 x_rowid => x_rowid
701 );
702
703 DELETE FROM igf_aw_awd_cert_resps
704 WHERE rowid = x_rowid;
705
706 IF (SQL%NOTFOUND) THEN
707 RAISE NO_DATA_FOUND;
708 END IF;
709
710 END delete_row;
711
712
713 END igf_aw_awd_cert_resps_pkg;