1 PACKAGE BODY igs_pe_hz_parties_pkg AS
2 /* $Header: IGSNI77B.pls 120.5 2006/02/22 06:24:28 vredkar ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_hz_parties%ROWTYPE;
6 new_references igs_pe_hz_parties%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_party_id IN NUMBER ,
12 x_deceased_ind IN VARCHAR2 ,
13 x_archive_exclusion_ind IN VARCHAR2 ,
14 x_archive_dt IN DATE ,
15 x_purge_exclusion_ind IN VARCHAR2 ,
16 x_purge_dt IN DATE ,
17 x_oracle_username IN VARCHAR2 ,
18 x_proof_of_ins IN VARCHAR2 ,
19 x_proof_of_immu IN VARCHAR2 ,
20 x_level_of_qual IN NUMBER ,
21 x_military_service_reg IN VARCHAR2 ,
22 x_veteran IN VARCHAR2 ,
23 x_institution_cd IN VARCHAR2 ,
24 x_oi_local_institution_ind IN VARCHAR2 ,
25 x_oi_os_ind IN VARCHAR2 ,
26 x_oi_govt_institution_cd IN VARCHAR2 ,
27 x_oi_inst_control_type IN VARCHAR2 ,
28 x_oi_institution_type IN VARCHAR2 ,
29 x_oi_institution_status IN VARCHAR2 ,
30 x_ou_start_dt IN DATE ,
31 x_ou_end_dt IN DATE ,
32 x_ou_member_type IN VARCHAR2 ,
33 x_ou_org_status IN VARCHAR2 ,
34 x_ou_org_type IN VARCHAR2 ,
35 x_inst_org_ind IN VARCHAR2 ,
36 x_inst_priority_cd IN VARCHAR2 ,
37 x_inst_eps_code IN VARCHAR2 ,
38 x_inst_phone_country_code IN VARCHAR2 ,
39 x_inst_phone_area_code IN VARCHAR2 ,
40 x_inst_phone_number IN VARCHAR2 ,
41 x_adv_studies_classes IN NUMBER ,
42 x_honors_classes IN NUMBER ,
43 x_class_size IN NUMBER ,
44 x_sec_school_location_id IN NUMBER ,
45 x_percent_plan_higher_edu IN NUMBER ,
46 x_fund_authorization IN VARCHAR2 ,
47 x_pe_info_verify_time IN DATE ,
48 x_birth_city IN VARCHAR2 ,
49 x_birth_country IN VARCHAR2 ,
50 x_oss_org_unit_cd IN VARCHAR2,
51 x_felony_convicted_flag IN VARCHAR2,
52 x_creation_date IN DATE ,
53 x_created_by IN NUMBER ,
54 x_last_update_date IN DATE ,
55 x_last_updated_by IN NUMBER ,
56 x_last_update_login IN NUMBER
57 ) AS
58 /*
59 || Created By : [email protected]
60 || Created On : 28-AUG-2000
61 || Purpose : Initialises the Old and New references for the columns of the table.
62 || Known limitations, enhancements or remarks :
63 || Change History :
64 || Who When What
65 || (reverse chronological order - newest change first)
66 */
67
68 CURSOR cur_old_ref_values IS
69 SELECT *
70 FROM IGS_PE_HZ_PARTIES
71 WHERE rowid = x_rowid;
72
73 BEGIN
74
75 l_rowid := x_rowid;
76
77 -- Code for setting the Old and New Reference Values.
78 -- Populate Old Values.
79 OPEN cur_old_ref_values;
80 FETCH cur_old_ref_values INTO old_references;
81 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
82 CLOSE cur_old_ref_values;
83 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
84
85 igs_ge_msg_stack.add;
86 app_exception.raise_exception;
87 RETURN;
88 END IF;
89 CLOSE cur_old_ref_values;
90
91 -- Populate New Values.
92 new_references.party_id := x_party_id;
93 new_references.deceased_ind := x_deceased_ind;
94 new_references.archive_exclusion_ind := x_archive_exclusion_ind;
95 new_references.archive_dt := x_archive_dt;
96 new_references.purge_exclusion_ind := x_purge_exclusion_ind;
97 new_references.purge_dt := x_purge_dt;
98 new_references.oracle_username := x_oracle_username;
99 new_references.proof_of_ins := x_proof_of_ins;
100 new_references.proof_of_immu := x_proof_of_immu;
101 new_references.level_of_qual := x_level_of_qual;
102 new_references.military_service_reg := x_military_service_reg;
103 new_references.veteran := x_veteran;
104 new_references.institution_cd := x_institution_cd;
105 new_references.oi_local_institution_ind := x_oi_local_institution_ind;
106 new_references.oi_os_ind := x_oi_os_ind;
107 new_references.oi_govt_institution_cd := x_oi_govt_institution_cd;
108 new_references.oi_inst_control_type := x_oi_inst_control_type;
109 new_references.oi_institution_type := x_oi_institution_type;
110 new_references.oi_institution_status := x_oi_institution_status;
111 new_references.ou_start_dt := x_ou_start_dt;
112 new_references.ou_end_dt := x_ou_end_dt;
113 new_references.ou_member_type := x_ou_member_type;
114 new_references.ou_org_status := x_ou_org_status;
115 new_references.ou_org_type := x_ou_org_type;
116 new_references.inst_org_ind := x_inst_org_ind;
117 new_references.inst_priority_cd := x_inst_priority_cd;
118 new_references.inst_eps_code := x_inst_eps_code;
119 new_references.inst_phone_country_code := x_inst_phone_country_code;
120 new_references.inst_phone_area_code := x_inst_phone_area_code;
121 new_references.inst_phone_number := x_inst_phone_number;
122 new_references.adv_studies_classes := x_adv_studies_classes;
123 new_references.honors_classes := x_honors_classes;
124 new_references.class_size := x_class_size;
125 new_references.sec_school_location_id := x_sec_school_location_id;
126 new_references.percent_plan_higher_edu := x_percent_plan_higher_edu;
127 new_references.fund_authorization := x_fund_authorization;
128 new_references.pe_info_verify_time := x_pe_info_verify_time;
129 new_references.birth_city := x_birth_city;
130 new_references.birth_country := x_birth_country;
131 new_references.oss_org_unit_cd := x_oss_org_unit_cd;
132 new_references.felony_convicted_flag := x_felony_convicted_flag;
133
134 IF (p_action = 'UPDATE') THEN
135 new_references.creation_date := old_references.creation_date;
136 new_references.created_by := old_references.created_by;
137 ELSE
138 new_references.creation_date := x_creation_date;
139 new_references.created_by := x_created_by;
140 END IF;
141
142 new_references.last_update_date := x_last_update_date;
143 new_references.last_updated_by := x_last_updated_by;
144 new_references.last_update_login := x_last_update_login;
145
146 END set_column_values;
147
148
149 FUNCTION get_pk_for_validation (
150 x_party_id IN NUMBER
151 ) RETURN BOOLEAN AS
152 /*
153 || Created By : [email protected]
154 || Created On : 28-AUG-2000
155 || Purpose : Validates the Primary Key of the table.
156 || Known limitations, enhancements or remarks :
157 || Change History :
158 || Who When What
159 || (reverse chronological order - newest change first)
160 */
161 CURSOR cur_rowid IS
162 SELECT rowid
163 FROM igs_pe_hz_parties
164 WHERE party_id = x_party_id
165 FOR UPDATE NOWAIT;
166
167 lv_rowid cur_rowid%RowType;
168
169 BEGIN
170
171 OPEN cur_rowid;
172 FETCH cur_rowid INTO lv_rowid;
173 IF (cur_rowid%FOUND) THEN
174 CLOSE cur_rowid;
175 RETURN(TRUE);
176 ELSE
177 CLOSE cur_rowid;
178 RETURN(FALSE);
179 END IF;
180
181 END get_pk_for_validation;
182
183 PROCEDURE get_uk_for_validation (
184 x_oss_org_unit_cd IN VARCHAR2 ,
185 x_inst_org_ind IN VARCHAR2
186 )AS
187 /*
188 || Created By : [email protected]
189 || Created On : 19-AUG-2005
190 || Purpose :
191 || Known limitations, enhancements or remarks :
192 || Change History :
193 || Who When What
194 || (reverse chronological order - newest change first)
195 */
196 CURSOR cur_org_unit_exists(cp_oss_org_unit_cd VARCHAR2) IS
197 SELECT 'X'
198 FROM igs_pe_hz_parties
199 WHERE oss_org_unit_cd = cp_oss_org_unit_cd;
200
201 l_exists VARCHAR2(1);
202 BEGIN
203 OPEN cur_org_unit_exists(x_oss_org_unit_cd);
204 FETCH cur_org_unit_exists INTO l_exists;
205 IF cur_org_unit_exists%FOUND THEN
206 CLOSE cur_org_unit_exists;
207 FND_MESSAGE.Set_Name('IGS','IGS_OR_INST_UNIQUE');
208 IF x_inst_org_ind = 'O' THEN
209 FND_MESSAGE.SET_TOKEN('ORG_INST_CD',FND_MESSAGE.GET_STRING('IGS','IGS_ORG_UNIT_CD'));
210 ELSIF x_inst_org_ind = 'I' THEN
211 FND_MESSAGE.SET_TOKEN('ORG_INST_CD',FND_MESSAGE.GET_STRING('IGS','IGS_OR_INSTITUTION_CODE'));
212 END IF;
213 IGS_GE_MSG_STACK.Add;
214 APP_EXCEPTION.Raise_Exception;
215 RETURN;
216 END IF;
217
218 CLOSE cur_org_unit_exists;
219 END get_uk_for_validation;
220
221 PROCEDURE get_fk_igs_ad_code_classes1(x_code_id NUMBER) AS
222 /*
223 || Created By : [email protected]
224 || Created On : 09-AUG-2001
225 || Purpose :
226 || Known limitations, enhancements or remarks :
227 || Change History :
228 || Who When What
229 || (reverse chronological order - newest change first)
230 */
231 CURSOR cur_rowid IS
232 SELECT rowid
233 FROM igs_pe_hz_parties
234 WHERE ((sec_school_location_id = x_code_id));
235
236 lv_rowid cur_rowid%ROWTYPE;
237 BEGIN
238 OPEN cur_rowid;
239 FETCH cur_rowid INTO lv_rowid;
240 IF cur_rowid%FOUND THEN
241 CLOSE cur_rowid;
242 FND_MESSAGE.Set_Name('IGS','IGS_OR_PHP1_ACC');
243 IGS_GE_MSG_STACK.Add;
244 APP_EXCEPTION.Raise_Exception;
245 RETURN;
246 END IF;
247 CLOSE cur_rowid;
248
249 END get_fk_igs_ad_code_classes1;
250
251 PROCEDURE check_parent_existance AS
252 /*
253 || Created By : [email protected]
254 || Created On : 28-AUG-2000
255 || Purpose : To check for the existence of parent records
256 || Known limitations, enhancements or remarks :
257 || Change History :
258 || Who When What
259 || (reverse chronological order - newest change first)
260 || ssawhney 6FEB2002 veteran is now a lookup, so get pk from IGS_LOOKUPS_VIEW_PKG
261 */
262
263 FUNCTION validate_pk(x_party_id NUMBER) RETURN BOOLEAN IS
264 CURSOR cur_rowid IS
265 SELECT rowid
266 FROM HZ_PARTIES
267 WHERE party_id = x_party_id
268 FOR UPDATE NOWAIT;
269 lv_rowid cur_rowid%RowType;
270 BEGIN
271 Open cur_rowid;
272 Fetch cur_rowid INTO lv_rowid;
273
274 IF (cur_rowid%FOUND) THEN
275 Close cur_rowid;
276 RETURN(TRUE);
277 ELSE
278 Close cur_rowid;
279 RETURN(FALSE);
280 END IF;
281
282 END validate_pk;
283
284 BEGIN
285 IF (((old_references.party_id = new_references.party_id)) OR
286 ((new_references.party_id IS NULL))) THEN
287 NULL;
288 ELSE
289 if Not validate_pk ( new_references.party_id ) then
290 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 end if;
294 END IF;
295
296 -- added by ssawhney 2203778. veteran is now a lookup so validate.
297
298 IF (((old_references.veteran = new_references.veteran)) OR
299 ((new_references.veteran IS NULL))) THEN
300 NULL;
301 ELSE
302 IF NOT IGS_LOOKUPS_VIEW_PKG.get_pk_for_validation('VETERAN_STATUS',new_references.veteran) THEN
303 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
304 IGS_GE_MSG_STACK.ADD;
305 APP_EXCEPTION.RAISE_EXCEPTION;
306 END IF;
307 END IF;
308
309 IF (((old_references.inst_priority_cd = new_references.inst_priority_cd)) OR
310 ((new_references.inst_priority_cd IS NULL))) THEN
311 NULL;
312 ELSE
313 IF NOT IGS_LOOKUPS_VIEW_PKG.get_pk_for_validation('OR_INST_PRIORITY_CD',new_references.inst_priority_cd) THEN
314 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
315 IGS_GE_MSG_STACK.ADD;
316 APP_EXCEPTION.RAISE_EXCEPTION;
317 END IF;
318 END IF;
319
320 END check_parent_existance;
321
322 PROCEDURE validate_local_ind
323 IS
324 /*
325 || Created By : kumma
326 || Created On : 01-JUL-2002
327 || Purpose : validation for local indicator for institutions
328 || Known limitations, enhancements or remarks :
329 || Change History :
330 || Who When What
331 || (reverse chronological order - newest change first)
332 || ssawhney 30-sep institution_cd is only for ORG, so new cursor introduced to get party_number
333 || gmaheswa 11-sep-2003 local indicator is set only for a single institution based on the profile value.
334 || pkpatel 11-DEC-2003 Bug 2863933 (Modified the update statement and called it conditionally)
335 || gmaheswa 07-01-2003 Bug 3354341 (Loop is kept to make local instituion ind to 'N' for all institutions whose
336 || local instituion indicator is set to 'Y'. This is useful when there is corrupt data i.e
337 || having more than one local instituion.
338 */
339 e_resource_busy EXCEPTION;
340 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
341
342 CURSOR local_inst_cur IS
343 SELECT party_id
344 FROM igs_pe_hz_parties
345 WHERE oi_local_institution_ind ='Y'
346 FOR UPDATE NOWAIT;
347
348 l_inst_rec local_inst_cur%ROWTYPE;
349 BEGIN
350 -- set old records local institution indicator to N to make sure that the local indicator is set only
351 -- for a single institution.
352
353 --explicitly lock the record.
354 -- #3354341 gmaheswa : Sets Local institution indicator to Y for a single record.
355 OPEN local_inst_cur;
356 LOOP
357 FETCH local_inst_cur INTO l_inst_rec;
358 EXIT WHEN local_inst_cur%NOTFOUND;
359 UPDATE igs_pe_hz_parties
360 SET oi_local_institution_ind = 'N'
361 WHERE party_id = l_inst_rec.party_id;
362 END LOOP;
363 CLOSE local_inst_cur;
364
365 EXCEPTION
366 WHEN e_resource_busy THEN
367 -- ssawhney just raise the exception.
368 APP_EXCEPTION.RAISE_EXCEPTION;
369
370 END validate_local_ind ;
371
372
373 PROCEDURE before_dml (
374 p_action IN VARCHAR2,
375 x_rowid IN VARCHAR2 ,
376 x_party_id IN NUMBER ,
377 x_deceased_ind IN VARCHAR2 ,
378 x_archive_exclusion_ind IN VARCHAR2 ,
379 x_archive_dt IN DATE ,
380 x_purge_exclusion_ind IN VARCHAR2 ,
381 x_purge_dt IN DATE ,
382 x_oracle_username IN VARCHAR2 ,
383 x_proof_of_ins IN VARCHAR2 ,
384 x_proof_of_immu IN VARCHAR2 ,
385 x_level_of_qual IN NUMBER ,
386 x_military_service_reg IN VARCHAR2 ,
387 x_veteran IN VARCHAR2 ,
388 x_institution_cd IN VARCHAR2 ,
389 x_oi_local_institution_ind IN VARCHAR2 ,
390 x_oi_os_ind IN VARCHAR2 ,
391 x_oi_govt_institution_cd IN VARCHAR2 ,
392 x_oi_inst_control_type IN VARCHAR2 ,
393 x_oi_institution_type IN VARCHAR2 ,
394 x_oi_institution_status IN VARCHAR2 ,
395 x_ou_start_dt IN DATE ,
396 x_ou_end_dt IN DATE ,
397 x_ou_member_type IN VARCHAR2 ,
398 x_ou_org_status IN VARCHAR2 ,
399 x_ou_org_type IN VARCHAR2 ,
400 x_inst_org_ind IN VARCHAR2 ,
401 x_inst_priority_cd IN VARCHAR2 ,
402 x_inst_eps_code IN VARCHAR2 ,
403 x_inst_phone_country_code IN VARCHAR2 ,
404 x_inst_phone_area_code IN VARCHAR2 ,
405 x_inst_phone_number IN VARCHAR2 ,
406 x_adv_studies_classes IN NUMBER ,
407 x_honors_classes IN NUMBER ,
408 x_class_size IN NUMBER ,
409 x_sec_school_location_id IN NUMBER ,
410 x_percent_plan_higher_edu IN NUMBER ,
411 x_fund_authorization IN VARCHAR2 ,
412 x_pe_info_verify_time IN DATE ,
413 x_birth_city IN VARCHAR2 ,
414 x_birth_country IN VARCHAR2 ,
415 x_oss_org_unit_cd IN VARCHAR2,
416 x_felony_convicted_flag IN VARCHAR2,
417 x_creation_date IN DATE ,
418 x_created_by IN NUMBER ,
419 x_last_update_date IN DATE ,
420 x_last_updated_by IN NUMBER ,
421 x_last_update_login IN NUMBER
422 ) AS
423 /*
424 || Created By : [email protected]
425 || Created On : 28-AUG-2000
426 || Purpose : Initialises the columns, Checks Constraints, Calls the
427 || Trigger Handlers for the table, before any DML operation.
428 || Known limitations, enhancements or remarks :
429 || Change History :
430 || Who When What
431 || kumma 28-JUN-2002 Added validations to make sure that only one
432 || institution should be marked local, 2425349
433 || ssawhney x_institution_cd changed to x_party_id
434 || gmaheswa 15-sep-2003 validation to check only one institution should be
435 || marked as local is deleted in before insert, as it is taken
436 || care by other modules,2863933
437 || (reverse chronological order - newest change first)
438 */
439
440
441 BEGIN
442
443 set_column_values (
444 p_action,
445 x_rowid,
446 x_party_id,
447 x_deceased_ind,
448 x_archive_exclusion_ind,
449 x_archive_dt,
450 x_purge_exclusion_ind,
451 x_purge_dt,
452 x_oracle_username,
453 x_proof_of_ins,
454 x_proof_of_immu,
455 x_level_of_qual,
456 x_military_service_reg,
457 x_veteran,
458 x_institution_cd,
459 x_oi_local_institution_ind,
460 x_oi_os_ind,
461 x_oi_govt_institution_cd,
462 x_oi_inst_control_type,
463 x_oi_institution_type,
464 x_oi_institution_status,
465 x_ou_start_dt,
466 x_ou_end_dt,
467 x_ou_member_type,
468 x_ou_org_status,
469 x_ou_org_type,
470 x_inst_org_ind,
471 x_inst_priority_cd,
472 x_inst_eps_code ,
473 x_inst_phone_country_code,
474 x_inst_phone_area_code,
475 x_inst_phone_number,
476 x_adv_studies_classes,
477 x_honors_classes,
478 x_class_size,
479 x_sec_school_location_id,
480 x_percent_plan_higher_edu,
481 x_fund_authorization,
482 x_pe_info_verify_time,
483 x_birth_city ,
484 x_birth_country,
485 x_oss_org_unit_cd,
486 x_felony_convicted_flag,
487 x_creation_date,
488 x_created_by,
489 x_last_update_date,
490 x_last_updated_by,
491 x_last_update_login
492 );
493
494 IF (p_action = 'INSERT') THEN
495
496 -- Call all the procedures related to Before Insert.
497 IF ( get_pk_for_validation(
498 new_references.party_id
499 )
500 ) THEN
501 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
502 igs_ge_msg_stack.add;
503 app_exception.raise_exception;
504 END IF;
505
506 IF new_references.oss_org_unit_cd IS NOT NULL THEN
507 get_uk_for_validation(new_references.oss_org_unit_cd,new_references.inst_org_ind);
508 END IF;
509
510 check_parent_existance;
511 ELSIF (p_action = 'UPDATE') THEN
512 -- validate the local indicator
513 -- Bug #3354341:gmaheswa:NVL check is introduced for old_references.oi_local_institution_ind
514 IF new_references.inst_org_ind = 'I' AND
515 (new_references.oi_local_institution_ind = 'Y' AND NVL(old_references.oi_local_institution_ind,'N') = 'N') THEN
516
517 validate_local_ind;
518
519 END IF;
520
521 -- Call all the procedures related to Before Update.
522 check_parent_existance;
523 ELSIF (p_action = 'VALIDATE_INSERT') THEN
524 -- Call all the procedures related to Before Insert.
525 IF ( get_pk_for_validation (
526 new_references.party_id
527 )
528 ) THEN
529 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
530 igs_ge_msg_stack.add;
531 app_exception.raise_exception;
532 END IF;
533
534 IF new_references.oss_org_unit_cd IS NOT NULL THEN
535 get_uk_for_validation(new_references.oss_org_unit_cd,new_references.inst_org_ind);
536 END IF;
537
538 END IF;
539
540 END before_dml;
541
542
543
544 PROCEDURE insert_row (
545 x_rowid IN OUT NOCOPY VARCHAR2,
546 x_party_id IN NUMBER,
547 x_deceased_ind IN VARCHAR2,
548 x_archive_exclusion_ind IN VARCHAR2,
549 x_archive_dt IN DATE,
550 x_purge_exclusion_ind IN VARCHAR2,
551 x_purge_dt IN DATE,
552 x_oracle_username IN VARCHAR2,
553 x_proof_of_ins IN VARCHAR2,
554 x_proof_of_immu IN VARCHAR2,
555 x_level_of_qual IN NUMBER,
556 x_military_service_reg IN VARCHAR2,
557 x_veteran IN VARCHAR2,
558 x_institution_cd IN VARCHAR2,
559 x_oi_local_institution_ind IN VARCHAR2,
560 x_oi_os_ind IN VARCHAR2,
561 x_oi_govt_institution_cd IN VARCHAR2,
562 x_oi_inst_control_type IN VARCHAR2,
563 x_oi_institution_type IN VARCHAR2,
564 x_oi_institution_status IN VARCHAR2,
565 x_ou_start_dt IN DATE,
566 x_ou_end_dt IN DATE,
567 x_ou_member_type IN VARCHAR2,
568 x_ou_org_status IN VARCHAR2,
569 x_ou_org_type IN VARCHAR2,
570 x_inst_org_ind IN VARCHAR2,
571 x_inst_priority_cd IN VARCHAR2 ,
572 x_inst_eps_code IN VARCHAR2 ,
573 x_inst_phone_country_code IN VARCHAR2 ,
574 x_inst_phone_area_code IN VARCHAR2 ,
575 x_inst_phone_number IN VARCHAR2 ,
576 x_adv_studies_classes IN NUMBER ,
577 x_honors_classes IN NUMBER ,
578 x_class_size IN NUMBER ,
579 x_sec_school_location_id IN NUMBER ,
580 x_percent_plan_higher_edu IN NUMBER ,
581 x_fund_authorization IN VARCHAR2 ,
582 x_pe_info_verify_time IN DATE ,
583 x_birth_city IN VARCHAR2 ,
584 x_birth_country IN VARCHAR2 ,
585 x_oss_org_unit_cd IN VARCHAR2,
586 x_felony_convicted_flag IN VARCHAR2,
587 x_mode IN VARCHAR2
588 ) AS
589 /*
590 || Created By : [email protected]
591 || Created On : 28-AUG-2000
592 || Purpose : Handles the INSERT DML logic for the table.
593 || Known limitations, enhancements or remarks :
594 || Change History :
595 || Who When What
596 || (reverse chronological order - newest change first)
597 */
598 CURSOR c IS
599 SELECT rowid
600 FROM igs_pe_hz_parties
601 WHERE party_id = x_party_id;
602
603 x_last_update_date DATE;
604 x_last_updated_by NUMBER;
605 x_last_update_login NUMBER;
606 x_request_id NUMBER;
607 x_program_id NUMBER;
608 x_program_application_id NUMBER;
609 x_program_update_date DATE;
610
611 BEGIN
612
613 x_last_update_date := SYSDATE;
614 IF (x_mode = 'I') THEN
615 x_last_updated_by := 1;
616 x_last_update_login := 0;
617 ELSIF (X_MODE IN ('R', 'S')) THEN
618 x_last_updated_by := fnd_global.user_id;
619 IF (x_last_updated_by IS NULL) THEN
620 x_last_updated_by := -1;
621 END IF;
622 x_last_update_login := fnd_global.login_id;
623 IF (x_last_update_login IS NULL) THEN
624 x_last_update_login := -1;
625 END IF;
626 x_request_id := fnd_global.conc_request_id;
627 x_program_id := fnd_global.conc_program_id;
628 x_program_application_id := fnd_global.prog_appl_id;
629
630 IF (x_request_id = -1) THEN
631 x_request_id := NULL;
632 x_program_id := NULL;
633 x_program_application_id := NULL;
634 x_program_update_date := NULL;
635 ELSE
636 x_program_update_date := SYSDATE;
637 END IF;
638 ELSE
639 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
640 igs_ge_msg_stack.add;
641 app_exception.raise_exception;
642 END IF;
643
644
645 before_dml(
646 p_action => 'INSERT',
647 x_rowid => x_rowid,
648 x_party_id => x_party_id,
649 x_deceased_ind => x_deceased_ind,
650 x_archive_exclusion_ind => x_archive_exclusion_ind,
651 x_archive_dt => x_archive_dt,
652 x_purge_exclusion_ind => x_purge_exclusion_ind,
653 x_purge_dt => x_purge_dt,
654 x_oracle_username => x_oracle_username,
655 x_proof_of_ins => x_proof_of_ins,
656 x_proof_of_immu => x_proof_of_immu,
657 x_level_of_qual => x_level_of_qual,
658 x_military_service_reg => x_military_service_reg,
659 x_veteran => x_veteran,
660 x_institution_cd => x_institution_cd,
661 x_oi_local_institution_ind => x_oi_local_institution_ind,
662 x_oi_os_ind => x_oi_os_ind,
663 x_oi_govt_institution_cd => x_oi_govt_institution_cd,
664 x_oi_inst_control_type => x_oi_inst_control_type,
665 x_oi_institution_type => x_oi_institution_type,
666 x_oi_institution_status => x_oi_institution_status,
667 x_ou_start_dt => x_ou_start_dt,
668 x_ou_end_dt => x_ou_end_dt,
669 x_ou_member_type => x_ou_member_type,
670 x_ou_org_status => x_ou_org_status,
671 x_ou_org_type => x_ou_org_type,
672 x_inst_org_ind => x_inst_org_ind,
673 x_inst_priority_cd => x_inst_priority_cd,
674 x_inst_eps_code => x_inst_eps_code,
675 x_inst_phone_country_code => x_inst_phone_country_code,
676 x_inst_phone_area_code => x_inst_phone_area_code,
677 x_inst_phone_number => x_inst_phone_number,
678 x_adv_studies_classes => x_adv_studies_classes,
679 x_honors_classes => x_honors_classes,
680 x_class_size => x_class_size,
681 x_sec_school_location_id => x_sec_school_location_id,
682 x_percent_plan_higher_edu => x_percent_plan_higher_edu,
683 x_fund_authorization => x_fund_authorization,
684 x_pe_info_verify_time => x_pe_info_verify_time,
685 x_birth_city => x_birth_city,
686 x_birth_country => x_birth_country,
687 x_oss_org_unit_cd => x_oss_org_unit_cd,
688 x_creation_date => x_last_update_date,
689 x_created_by => x_last_updated_by,
690 x_last_update_date => x_last_update_date,
691 x_last_updated_by => x_last_updated_by,
692 x_last_update_login => x_last_update_login,
693 x_felony_convicted_flag => x_felony_convicted_flag
694 );
695 IF (x_mode = 'S') THEN
696 igs_sc_gen_001.set_ctx('R');
697 END IF;
698
699
700 INSERT INTO igs_pe_hz_parties (
701 party_id,
702 deceased_ind,
703 archive_exclusion_ind,
704 archive_dt,
705 purge_exclusion_ind,
706 purge_dt,
707 oracle_username,
708 proof_of_ins,
709 proof_of_immu,
710 level_of_qual,
711 military_service_reg,
712 veteran,
713 institution_cd,
714 oi_local_institution_ind,
715 oi_os_ind,
716 oi_govt_institution_cd,
717 oi_inst_control_type,
718 oi_institution_type,
719 oi_institution_status,
720 ou_start_dt,
721 ou_end_dt,
722 ou_member_type,
723 ou_org_status,
724 ou_org_type,
725 inst_org_ind,
726 inst_priority_cd,
727 inst_eps_code ,
728 inst_phone_country_code,
729 inst_phone_area_code,
730 inst_phone_number,
731 adv_studies_classes,
732 honors_classes,
733 class_size,
734 sec_school_location_id,
735 percent_plan_higher_edu,
736 fund_authorization,
737 pe_info_verify_time,
738 birth_city ,
739 birth_country,
740 creation_date,
741 created_by,
742 last_update_date,
743 last_updated_by,
744 last_update_login,
745 request_id,
746 program_id,
747 program_application_id,
748 program_update_date ,
749 oss_org_unit_cd,
750 felony_convicted_flag
751 ) VALUES (
752 new_references.party_id,
753 new_references.deceased_ind,
754 new_references.archive_exclusion_ind,
755 new_references.archive_dt,
756 new_references.purge_exclusion_ind,
757 new_references.purge_dt,
758 new_references.oracle_username,
759 new_references.proof_of_ins,
760 new_references.proof_of_immu,
761 new_references.level_of_qual,
762 new_references.military_service_reg,
763 new_references.veteran,
764 new_references.institution_cd,
765 new_references.oi_local_institution_ind,
766 new_references.oi_os_ind,
767 new_references.oi_govt_institution_cd,
768 new_references.oi_inst_control_type,
769 new_references.oi_institution_type,
770 new_references.oi_institution_status,
771 new_references.ou_start_dt,
772 new_references.ou_end_dt,
773 new_references.ou_member_type,
774 new_references.ou_org_status,
775 new_references.ou_org_type,
776 new_references.inst_org_ind,
777 new_references.inst_priority_cd,
778 new_references.inst_eps_code,
779 new_references.inst_phone_country_code,
780 new_references.inst_phone_area_code,
781 new_references.inst_phone_number,
782 new_references.adv_studies_classes,
783 new_references.honors_classes,
784 new_references.class_size,
785 new_references.sec_school_location_id,
786 new_references.percent_plan_higher_edu,
787 new_references.fund_authorization,
788 new_references.pe_info_verify_time,
789 new_references.birth_city ,
790 new_references.birth_country,
791 x_last_update_date,
792 x_last_updated_by,
793 x_last_update_date,
794 x_last_updated_by,
795 x_last_update_login ,
796 x_request_id,
797 x_program_id,
798 x_program_application_id,
799 x_program_update_date ,
800 new_references.oss_org_unit_cd,
801 new_references.felony_convicted_flag
802 );
803 IF (x_mode = 'S') THEN
804 igs_sc_gen_001.unset_ctx('R');
805 END IF;
806
807 OPEN c;
808 FETCH c INTO x_rowid;
809 IF (c%NOTFOUND) THEN
810 CLOSE c;
811 RAISE NO_DATA_FOUND;
812 END IF;
813 CLOSE c;
814
815
816 EXCEPTION
817 WHEN OTHERS THEN
818 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
819 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
820 fnd_message.set_token ('ERR_CD', SQLCODE);
821 igs_ge_msg_stack.add;
822 igs_sc_gen_001.unset_ctx('R');
823 app_exception.raise_exception;
824 ELSE
825 igs_sc_gen_001.unset_ctx('R');
826 RAISE;
827 END IF;
828 END insert_row;
829
830
831 PROCEDURE lock_row (
832 x_rowid IN VARCHAR2,
833 x_party_id IN NUMBER,
834 x_deceased_ind IN VARCHAR2,
835 x_archive_exclusion_ind IN VARCHAR2,
836 x_archive_dt IN DATE,
837 x_purge_exclusion_ind IN VARCHAR2,
838 x_purge_dt IN DATE,
839 x_oracle_username IN VARCHAR2,
840 x_proof_of_ins IN VARCHAR2,
841 x_proof_of_immu IN VARCHAR2,
842 x_level_of_qual IN NUMBER,
843 x_military_service_reg IN VARCHAR2,
844 x_veteran IN VARCHAR2,
845 x_institution_cd IN VARCHAR2,
846 x_oi_local_institution_ind IN VARCHAR2,
847 x_oi_os_ind IN VARCHAR2,
848 x_oi_govt_institution_cd IN VARCHAR2,
849 x_oi_inst_control_type IN VARCHAR2,
850 x_oi_institution_type IN VARCHAR2,
851 x_oi_institution_status IN VARCHAR2,
852 x_ou_start_dt IN DATE,
853 x_ou_end_dt IN DATE,
854 x_ou_member_type IN VARCHAR2,
855 x_ou_org_status IN VARCHAR2,
856 x_ou_org_type IN VARCHAR2,
857 x_inst_org_ind IN VARCHAR2,
858 x_inst_priority_cd IN VARCHAR2 ,
859 x_inst_eps_code IN VARCHAR2 ,
860 x_inst_phone_country_code IN VARCHAR2 ,
861 x_inst_phone_area_code IN VARCHAR2 ,
862 x_inst_phone_number IN VARCHAR2 ,
863 x_adv_studies_classes IN NUMBER ,
864 x_honors_classes IN NUMBER ,
865 x_class_size IN NUMBER ,
866 x_sec_school_location_id IN NUMBER ,
867 x_percent_plan_higher_edu IN NUMBER ,
868 x_fund_authorization IN VARCHAR2 ,
869 x_pe_info_verify_time IN DATE ,
870 x_birth_city IN VARCHAR2 ,
871 x_birth_country IN VARCHAR2 ,
872 x_oss_org_unit_cd IN VARCHAR2,
873 x_felony_convicted_flag IN VARCHAR2
874 ) AS
875 /*
876 || Created By : [email protected]
877 || Created On : 28-AUG-2000
878 || Purpose : Handles the LOCK mechanism for the table.
879 || Known limitations, enhancements or remarks :
880 || Change History :
881 || Who When What
882 || (reverse chronological order - newest change first)
883 */
884 CURSOR c1 IS
885 SELECT
886 deceased_ind,
887 archive_exclusion_ind,
888 archive_dt,
889 purge_exclusion_ind,
890 purge_dt,
891 oracle_username,
892 proof_of_ins,
893 proof_of_immu,
894 level_of_qual,
895 military_service_reg,
896 veteran,
897 institution_cd,
898 oi_local_institution_ind,
899 oi_os_ind,
900 oi_govt_institution_cd,
901 oi_inst_control_type,
902 oi_institution_type,
903 oi_institution_status,
904 ou_start_dt,
905 ou_end_dt,
906 ou_member_type,
907 ou_org_status,
908 ou_org_type,
909 inst_org_ind,
910 inst_priority_cd,
911 inst_eps_code ,
912 inst_phone_country_code,
913 inst_phone_area_code,
914 inst_phone_number,
915 adv_studies_classes,
916 honors_classes,
917 class_size,
918 sec_school_location_id,
919 percent_plan_higher_edu,
920 fund_authorization,
921 pe_info_verify_time,
922 birth_city ,
923 birth_country,
924 oss_org_unit_cd,
925 felony_convicted_flag
926 FROM igs_pe_hz_parties
927 WHERE rowid = x_rowid
928 FOR UPDATE NOWAIT;
929
930 tlinfo c1%ROWTYPE;
931
932 BEGIN
933
934 OPEN c1;
935 FETCH c1 INTO tlinfo;
936 IF (c1%notfound) THEN
937 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
938 igs_ge_msg_stack.add;
939 CLOSE c1;
940 app_exception.raise_exception;
941 RETURN;
942 END IF;
943 CLOSE c1;
944
945 IF (
946 ((tlinfo.deceased_ind = x_deceased_ind) OR ((tlinfo.deceased_ind IS NULL) AND (X_deceased_ind IS NULL)))
947 AND ((tlinfo.archive_exclusion_ind = x_archive_exclusion_ind) OR ((tlinfo.archive_exclusion_ind IS NULL) AND (X_archive_exclusion_ind IS NULL)))
948 AND ((tlinfo.archive_dt = x_archive_dt) OR ((tlinfo.archive_dt IS NULL) AND (X_archive_dt IS NULL)))
949 AND ((tlinfo.purge_exclusion_ind = x_purge_exclusion_ind) OR ((tlinfo.purge_exclusion_ind IS NULL) AND (X_purge_exclusion_ind IS NULL)))
950 AND ((tlinfo.purge_dt = x_purge_dt) OR ((tlinfo.purge_dt IS NULL) AND (X_purge_dt IS NULL)))
951 AND ((tlinfo.oracle_username = x_oracle_username) OR ((tlinfo.oracle_username IS NULL) AND (X_oracle_username IS NULL)))
952 AND ((tlinfo.proof_of_ins = x_proof_of_ins) OR ((tlinfo.proof_of_ins IS NULL) AND (X_proof_of_ins IS NULL)))
953 AND ((tlinfo.proof_of_immu = x_proof_of_immu) OR ((tlinfo.proof_of_immu IS NULL) AND (X_proof_of_immu IS NULL)))
954 AND ((tlinfo.level_of_qual = x_level_of_qual) OR ((tlinfo.level_of_qual IS NULL) AND (X_level_of_qual IS NULL)))
955 AND ((tlinfo.military_service_reg = x_military_service_reg) OR ((tlinfo.military_service_reg IS NULL) AND (X_military_service_reg IS NULL)))
956 AND ((tlinfo.veteran = x_veteran) OR ((tlinfo.veteran IS NULL) AND (X_veteran IS NULL)))
957 AND ((tlinfo.institution_cd = x_institution_cd) OR ((tlinfo.institution_cd IS NULL) AND (X_institution_cd IS NULL)))
958 AND ((tlinfo.oi_local_institution_ind = x_oi_local_institution_ind) OR ((tlinfo.oi_local_institution_ind IS NULL) AND (X_oi_local_institution_ind IS NULL)))
959 AND ((tlinfo.oi_os_ind = x_oi_os_ind) OR ((tlinfo.oi_os_ind IS NULL) AND (X_oi_os_ind IS NULL)))
960 AND ((tlinfo.oi_govt_institution_cd = x_oi_govt_institution_cd) OR ((tlinfo.oi_govt_institution_cd IS NULL) AND (X_oi_govt_institution_cd IS NULL)))
961 AND ((tlinfo.oi_inst_control_type = x_oi_inst_control_type) OR ((tlinfo.oi_inst_control_type IS NULL) AND (X_oi_inst_control_type IS NULL)))
962 AND ((tlinfo.oi_institution_type = x_oi_institution_type) OR ((tlinfo.oi_institution_type IS NULL) AND (X_oi_institution_type IS NULL)))
963 AND ((tlinfo.oi_institution_status = x_oi_institution_status) OR ((tlinfo.oi_institution_status IS NULL) AND (X_oi_institution_status IS NULL)))
964 AND ((tlinfo.ou_start_dt = x_ou_start_dt) OR ((tlinfo.ou_start_dt IS NULL) AND (X_ou_start_dt IS NULL)))
965 AND ((tlinfo.ou_end_dt = x_ou_end_dt) OR ((tlinfo.ou_end_dt IS NULL) AND (X_ou_end_dt IS NULL)))
966 AND ((tlinfo.ou_member_type = x_ou_member_type) OR ((tlinfo.ou_member_type IS NULL) AND (X_ou_member_type IS NULL)))
967 AND ((tlinfo.ou_org_status = x_ou_org_status) OR ((tlinfo.ou_org_status IS NULL) AND (X_ou_org_status IS NULL)))
968 AND ((tlinfo.ou_org_type = x_ou_org_type) OR ((tlinfo.ou_org_type IS NULL) AND (X_ou_org_type IS NULL)))
969 AND ((tlinfo.inst_org_ind = x_inst_org_ind) OR ((tlinfo.inst_org_ind IS NULL) AND (X_inst_org_ind IS NULL)))
970 AND ((tlinfo.inst_priority_cd = x_inst_priority_cd) OR ((tlinfo.inst_priority_cd IS NULL) AND (x_inst_priority_cd IS NULL)))
971 AND ((tlinfo.inst_eps_code = x_inst_eps_code) OR ((tlinfo.inst_eps_code IS NULL) AND (x_inst_eps_code IS NULL)))
972 AND ((tlinfo.inst_phone_country_code = x_inst_phone_country_code) OR ((tlinfo.inst_phone_country_code IS NULL) AND (x_inst_phone_country_code IS NULL)))
973 AND ((tlinfo.inst_phone_area_code = x_inst_phone_area_code) OR ((tlinfo.inst_phone_area_code IS NULL) AND (x_inst_phone_area_code IS NULL)))
974 AND ((tlinfo.inst_phone_number = x_inst_phone_number) OR ((tlinfo.inst_phone_number IS NULL) AND (x_inst_phone_number IS NULL)))
975 AND ((tlinfo.adv_studies_classes = x_adv_studies_classes) OR ((tlinfo.adv_studies_classes IS NULL) AND (x_adv_studies_classes IS NULL)))
976 AND ((tlinfo.honors_classes = x_honors_classes) OR ((tlinfo.honors_classes IS NULL) AND (x_honors_classes IS NULL)))
977 AND ((tlinfo.class_size = x_class_size) OR ((tlinfo.class_size IS NULL) AND (x_class_size IS NULL)))
978 AND ((tlinfo.sec_school_location_id = x_sec_school_location_id) OR ((tlinfo.sec_school_location_id IS NULL) AND (x_sec_school_location_id IS NULL)))
979 AND ((tlinfo.percent_plan_higher_edu = x_percent_plan_higher_edu) OR ((tlinfo.percent_plan_higher_edu IS NULL) AND (x_percent_plan_higher_edu IS NULL)))
980 AND ((tlinfo.fund_authorization = x_fund_authorization) OR ((tlinfo.fund_authorization IS NULL) AND (x_fund_authorization IS NULL)))
981 AND ((tlinfo.pe_info_verify_time = x_pe_info_verify_time) OR ((tlinfo.pe_info_verify_time IS NULL) AND (x_pe_info_verify_time IS NULL)))
982 AND ((tlinfo.birth_city = x_birth_city) OR ((tlinfo.birth_city IS NULL) AND (x_birth_city IS NULL)))
983 AND ((tlinfo.birth_country = x_birth_country) OR ((tlinfo.birth_country IS NULL) AND (x_birth_country IS NULL)))
984 AND ((tlinfo.oss_org_unit_cd = x_oss_org_unit_cd) OR ((tlinfo.oss_org_unit_cd IS NULL) AND (x_oss_org_unit_cd IS NULL)))
985 AND ((tlinfo.felony_convicted_flag = x_felony_convicted_flag) OR ((tlinfo.felony_convicted_flag IS NULL) AND (x_felony_convicted_flag IS NULL)))
986 ) THEN
987 NULL;
988 ELSE
989 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
990 igs_ge_msg_stack.add;
991 app_exception.raise_exception;
992 END IF;
993
994 RETURN;
995
996 END lock_row;
997
998
999 PROCEDURE update_row (
1000 x_rowid IN VARCHAR2,
1001 x_party_id IN NUMBER,
1002 x_deceased_ind IN VARCHAR2,
1003 x_archive_exclusion_ind IN VARCHAR2,
1004 x_archive_dt IN DATE,
1005 x_purge_exclusion_ind IN VARCHAR2,
1006 x_purge_dt IN DATE,
1007 x_oracle_username IN VARCHAR2,
1008 x_proof_of_ins IN VARCHAR2,
1009 x_proof_of_immu IN VARCHAR2,
1010 x_level_of_qual IN NUMBER,
1011 x_military_service_reg IN VARCHAR2,
1012 x_veteran IN VARCHAR2,
1013 x_institution_cd IN VARCHAR2,
1014 x_oi_local_institution_ind IN VARCHAR2,
1015 x_oi_os_ind IN VARCHAR2,
1016 x_oi_govt_institution_cd IN VARCHAR2,
1017 x_oi_inst_control_type IN VARCHAR2,
1018 x_oi_institution_type IN VARCHAR2,
1019 x_oi_institution_status IN VARCHAR2,
1020 x_ou_start_dt IN DATE,
1021 x_ou_end_dt IN DATE,
1022 x_ou_member_type IN VARCHAR2,
1023 x_ou_org_status IN VARCHAR2,
1024 x_ou_org_type IN VARCHAR2,
1025 x_inst_org_ind IN VARCHAR2,
1026 x_inst_priority_cd IN VARCHAR2 ,
1027 x_inst_eps_code IN VARCHAR2 ,
1028 x_inst_phone_country_code IN VARCHAR2 ,
1029 x_inst_phone_area_code IN VARCHAR2 ,
1030 x_inst_phone_number IN VARCHAR2 ,
1031 x_adv_studies_classes IN NUMBER ,
1032 x_honors_classes IN NUMBER ,
1033 x_class_size IN NUMBER ,
1034 x_sec_school_location_id IN NUMBER ,
1035 x_percent_plan_higher_edu IN NUMBER ,
1036 x_fund_authorization IN VARCHAR2 ,
1037 x_pe_info_verify_time IN DATE ,
1038 x_birth_city IN VARCHAR2 ,
1039 x_birth_country IN VARCHAR2 ,
1040 x_oss_org_unit_cd IN VARCHAR2,
1041 x_felony_convicted_flag IN VARCHAR2,
1042 x_mode IN VARCHAR2
1043 ) AS
1044 /*
1045 || Created By : [email protected]
1046 || Created On : 28-AUG-2000
1047 || Purpose : Handles the UPDATE DML logic for the table.
1048 || Known limitations, enhancements or remarks :
1049 || Change History :
1050 || Who When What
1051 || (reverse chronological order - newest change first)
1052 */
1053 x_last_update_date DATE ;
1054 x_last_updated_by NUMBER;
1055 x_last_update_login NUMBER;
1056 x_request_id NUMBER;
1057 x_program_id NUMBER;
1058 x_program_application_id NUMBER;
1059 x_program_update_date DATE;
1060
1061 BEGIN
1062
1063 x_last_update_date := SYSDATE;
1064 IF (X_MODE = 'I') THEN
1065 x_last_updated_by := 1;
1066 x_last_update_login := 0;
1067 ELSIF (X_MODE IN ('R', 'S')) THEN
1068 x_last_updated_by := fnd_global.user_id;
1069 IF x_last_updated_by IS NULL THEN
1070 x_last_updated_by := -1;
1071 END IF;
1072 x_last_update_login := fnd_global.login_id;
1073 IF (x_last_update_login IS NULL) THEN
1074 x_last_update_login := -1;
1075 END IF;
1076 ELSE
1077 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1078 igs_ge_msg_stack.add;
1079 app_exception.raise_exception;
1080 END IF;
1081
1082 before_dml(
1083 p_action => 'UPDATE',
1084 x_rowid => x_rowid,
1085 x_party_id => x_party_id,
1086 x_deceased_ind => x_deceased_ind,
1087 x_archive_exclusion_ind => x_archive_exclusion_ind,
1088 x_archive_dt => x_archive_dt,
1089 x_purge_exclusion_ind => x_purge_exclusion_ind,
1090 x_purge_dt => x_purge_dt,
1091 x_oracle_username => x_oracle_username,
1092 x_proof_of_ins => x_proof_of_ins,
1093 x_proof_of_immu => x_proof_of_immu,
1094 x_level_of_qual => x_level_of_qual,
1095 x_military_service_reg => x_military_service_reg,
1096 x_veteran => x_veteran,
1097 x_institution_cd => x_institution_cd,
1098 x_oi_local_institution_ind => x_oi_local_institution_ind,
1099 x_oi_os_ind => x_oi_os_ind,
1100 x_oi_govt_institution_cd => x_oi_govt_institution_cd,
1101 x_oi_inst_control_type => x_oi_inst_control_type,
1102 x_oi_institution_type => x_oi_institution_type,
1103 x_oi_institution_status => x_oi_institution_status,
1104 x_ou_start_dt => x_ou_start_dt,
1105 x_ou_end_dt => x_ou_end_dt,
1106 x_ou_member_type => x_ou_member_type,
1107 x_ou_org_status => x_ou_org_status,
1108 x_ou_org_type => x_ou_org_type,
1109 x_inst_org_ind => x_inst_org_ind,
1110 x_inst_priority_cd => x_inst_priority_cd,
1111 x_inst_eps_code => x_inst_eps_code,
1112 x_inst_phone_country_code => x_inst_phone_country_code,
1113 x_inst_phone_area_code => x_inst_phone_area_code,
1114 x_inst_phone_number => x_inst_phone_number,
1115 x_adv_studies_classes => x_adv_studies_classes,
1116 x_honors_classes => x_honors_classes,
1117 x_class_size => x_class_size,
1118 x_sec_school_location_id => x_sec_school_location_id,
1119 x_percent_plan_higher_edu => x_percent_plan_higher_edu,
1120 x_fund_authorization => x_fund_authorization,
1121 x_birth_city => x_birth_city,
1122 x_birth_country => x_birth_country,
1123 x_oss_org_unit_cd => x_oss_org_unit_cd,
1124 x_felony_convicted_flag => x_felony_convicted_flag,
1125 x_creation_date => x_last_update_date,
1126 x_created_by => x_last_updated_by,
1127 x_last_update_date => x_last_update_date,
1128 x_last_updated_by => x_last_updated_by,
1129 x_last_update_login => x_last_update_login ,
1130 x_pe_info_verify_time => x_pe_info_verify_time
1131 );
1132
1133 IF (X_MODE IN ('R', 'S')) THEN
1134 x_request_id := fnd_global.conc_request_id;
1135 x_program_id := fnd_global.conc_program_id;
1136 x_program_application_id := fnd_global.prog_appl_id;
1137 IF (x_request_id = -1) THEN
1138 x_request_id := old_references.request_id;
1139 x_program_id := old_references.program_id;
1140 x_program_application_id := old_references.program_application_id;
1141 x_program_update_date := old_references.program_update_date;
1142 ELSE
1143 x_program_update_date := SYSDATE;
1144 END IF;
1145 END IF;
1146
1147 IF (x_mode = 'S') THEN
1148 igs_sc_gen_001.set_ctx('R');
1149 END IF;
1150 UPDATE igs_pe_hz_parties
1151 SET
1152 deceased_ind = new_references.deceased_ind,
1153 archive_exclusion_ind = new_references.archive_exclusion_ind,
1154 archive_dt = new_references.archive_dt,
1155 purge_exclusion_ind = new_references.purge_exclusion_ind,
1156 purge_dt = new_references.purge_dt,
1157 oracle_username = new_references.oracle_username,
1158 proof_of_ins = new_references.proof_of_ins,
1159 proof_of_immu = new_references.proof_of_immu,
1160 level_of_qual = new_references.level_of_qual,
1161 military_service_reg = new_references.military_service_reg,
1162 veteran = new_references.veteran,
1163 institution_cd = new_references.institution_cd,
1164 oi_local_institution_ind = new_references.oi_local_institution_ind,
1165 oi_os_ind = new_references.oi_os_ind,
1166 oi_govt_institution_cd = new_references.oi_govt_institution_cd,
1167 oi_inst_control_type = new_references.oi_inst_control_type,
1168 oi_institution_type = new_references.oi_institution_type,
1169 oi_institution_status = new_references.oi_institution_status,
1170 ou_start_dt = new_references.ou_start_dt,
1171 ou_end_dt = new_references.ou_end_dt,
1172 ou_member_type = new_references.ou_member_type,
1173 ou_org_status = new_references.ou_org_status,
1174 ou_org_type = new_references.ou_org_type,
1175 inst_org_ind = new_references.inst_org_ind,
1176 inst_priority_cd = new_references.inst_priority_cd,
1177 inst_eps_code = new_references.inst_eps_code,
1178 inst_phone_country_code = new_references.inst_phone_country_code,
1179 inst_phone_area_code = new_references.inst_phone_area_code,
1180 inst_phone_number = new_references.inst_phone_number,
1181 adv_studies_classes = new_references.adv_studies_classes,
1182 honors_classes = new_references.honors_classes,
1183 class_size = new_references.class_size,
1184 sec_school_location_id = new_references.sec_school_location_id,
1185 percent_plan_higher_edu = new_references.percent_plan_higher_edu,
1186 fund_authorization = new_references.fund_authorization,
1187 pe_info_verify_time = new_references.pe_info_verify_time,
1188 birth_city = new_references.birth_city,
1189 birth_country = new_references.birth_country,
1190 oss_org_unit_cd = new_references.oss_org_unit_cd,
1191 felony_convicted_flag = new_references.felony_convicted_flag,
1192 last_update_date = x_last_update_date,
1193 last_updated_by = x_last_updated_by,
1194 last_update_login = x_last_update_login ,
1195 request_id = x_request_id,
1196 program_id = x_program_id,
1197 program_application_id = x_program_application_id,
1198 program_update_date = x_program_update_date
1199 WHERE rowid = x_rowid;
1200
1201 IF (SQL%NOTFOUND) THEN
1202 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1203 igs_ge_msg_stack.add;
1204 igs_sc_gen_001.unset_ctx('R');
1205 app_exception.raise_exception;
1206 END IF;
1207 IF (x_mode = 'S') THEN
1208 igs_sc_gen_001.unset_ctx('R');
1209 END IF;
1210
1211
1212
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 IF (SQLCODE = (-28115)) THEN
1216 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1217 fnd_message.set_token ('ERR_CD', SQLCODE);
1218 igs_ge_msg_stack.add;
1219 igs_sc_gen_001.unset_ctx('R');
1220 app_exception.raise_exception;
1221 ELSE
1222 igs_sc_gen_001.unset_ctx('R');
1223 RAISE;
1224 END IF;
1225 END update_row;
1226
1227
1228 PROCEDURE add_row (
1229 x_rowid IN OUT NOCOPY VARCHAR2,
1230 x_party_id IN NUMBER,
1231 x_deceased_ind IN VARCHAR2,
1232 x_archive_exclusion_ind IN VARCHAR2,
1233 x_archive_dt IN DATE,
1234 x_purge_exclusion_ind IN VARCHAR2,
1235 x_purge_dt IN DATE,
1236 x_oracle_username IN VARCHAR2,
1237 x_proof_of_ins IN VARCHAR2,
1238 x_proof_of_immu IN VARCHAR2,
1239 x_level_of_qual IN NUMBER,
1240 x_military_service_reg IN VARCHAR2,
1241 x_veteran IN VARCHAR2,
1242 x_institution_cd IN VARCHAR2,
1243 x_oi_local_institution_ind IN VARCHAR2,
1244 x_oi_os_ind IN VARCHAR2,
1245 x_oi_govt_institution_cd IN VARCHAR2,
1246 x_oi_inst_control_type IN VARCHAR2,
1247 x_oi_institution_type IN VARCHAR2,
1248 x_oi_institution_status IN VARCHAR2,
1249 x_ou_start_dt IN DATE,
1250 x_ou_end_dt IN DATE,
1251 x_ou_member_type IN VARCHAR2,
1252 x_ou_org_status IN VARCHAR2,
1253 x_ou_org_type IN VARCHAR2,
1254 x_inst_org_ind IN VARCHAR2,
1255 x_inst_priority_cd IN VARCHAR2 ,
1256 x_inst_eps_code IN VARCHAR2 ,
1257 x_inst_phone_country_code IN VARCHAR2 ,
1258 x_inst_phone_area_code IN VARCHAR2 ,
1259 x_inst_phone_number IN VARCHAR2 ,
1260 x_adv_studies_classes IN NUMBER ,
1261 x_honors_classes IN NUMBER ,
1262 x_class_size IN NUMBER ,
1263 x_sec_school_location_id IN NUMBER ,
1264 x_percent_plan_higher_edu IN NUMBER ,
1265 x_fund_authorization IN VARCHAR2 ,
1266 x_pe_info_verify_time IN DATE ,
1267 x_birth_city IN VARCHAR2 ,
1268 x_birth_country IN VARCHAR2 ,
1269 x_oss_org_unit_cd IN VARCHAR2,
1270 x_felony_convicted_flag IN VARCHAR2,
1271 x_mode IN VARCHAR2
1272 ) AS
1273 /*
1274 || Created By : [email protected]
1275 || Created On : 28-AUG-2000
1276 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1277 || Known limitations, enhancements or remarks :
1278 || Change History :
1279 || Who When What
1280 || (reverse chronological order - newest change first)
1281 */
1282 CURSOR c1 IS
1283 SELECT rowid
1284 FROM igs_pe_hz_parties
1285 WHERE party_id = x_party_id;
1286
1287 BEGIN
1288
1289 OPEN c1;
1290 FETCH c1 INTO x_rowid;
1291 IF (c1%NOTFOUND) THEN
1292 CLOSE c1;
1293
1294 insert_row (
1295 x_rowid,
1296 x_party_id,
1297 x_deceased_ind,
1298 x_archive_exclusion_ind,
1299 x_archive_dt,
1300 x_purge_exclusion_ind,
1301 x_purge_dt,
1302 x_oracle_username,
1303 x_proof_of_ins,
1304 x_proof_of_immu,
1305 x_level_of_qual,
1306 x_military_service_reg,
1307 x_veteran,
1308 x_institution_cd,
1309 x_oi_local_institution_ind,
1310 x_oi_os_ind,
1311 x_oi_govt_institution_cd,
1312 x_oi_inst_control_type,
1313 x_oi_institution_type,
1314 x_oi_institution_status,
1315 x_ou_start_dt,
1316 x_ou_end_dt,
1317 x_ou_member_type,
1318 x_ou_org_status,
1319 x_ou_org_type,
1320 x_inst_org_ind,
1321 x_inst_priority_cd,
1322 x_inst_eps_code ,
1323 x_inst_phone_country_code,
1324 x_inst_phone_area_code,
1325 x_inst_phone_number,
1326 x_adv_studies_classes,
1327 x_honors_classes,
1328 x_class_size,
1329 x_sec_school_location_id,
1330 x_percent_plan_higher_edu,
1331 x_fund_authorization,
1332 x_pe_info_verify_time,
1333 x_birth_city ,
1334 x_birth_country,
1335 x_oss_org_unit_cd,
1336 x_felony_convicted_flag,
1337 x_mode
1338 );
1339 RETURN;
1340 END IF;
1341 CLOSE c1;
1342
1343 update_row (
1344 x_rowid,
1345 x_party_id,
1346 x_deceased_ind,
1347 x_archive_exclusion_ind,
1348 x_archive_dt,
1349 x_purge_exclusion_ind,
1350 x_purge_dt,
1351 x_oracle_username,
1352 x_proof_of_ins,
1353 x_proof_of_immu,
1354 x_level_of_qual,
1355 x_military_service_reg,
1356 x_veteran,
1357 x_institution_cd,
1358 x_oi_local_institution_ind,
1359 x_oi_os_ind,
1360 x_oi_govt_institution_cd,
1361 x_oi_inst_control_type,
1362 x_oi_institution_type,
1363 x_oi_institution_status,
1364 x_ou_start_dt,
1365 x_ou_end_dt,
1366 x_ou_member_type,
1367 x_ou_org_status,
1368 x_ou_org_type,
1369 x_inst_org_ind,
1370 x_inst_priority_cd,
1371 x_inst_eps_code ,
1372 x_inst_phone_country_code,
1373 x_inst_phone_area_code,
1374 x_inst_phone_number,
1375 x_adv_studies_classes,
1376 x_honors_classes,
1377 x_class_size,
1378 x_sec_school_location_id,
1379 x_percent_plan_higher_edu,
1380 x_fund_authorization,
1381 x_pe_info_verify_time,
1382 x_birth_city ,
1383 x_birth_country,
1384 x_oss_org_unit_cd,
1385 x_felony_convicted_flag,
1386 x_mode
1387 );
1388
1389 END add_row;
1390
1391
1392 PROCEDURE delete_row (
1393 x_rowid IN VARCHAR2,
1394 x_mode IN VARCHAR2
1395 ) AS
1396 /*
1397 || Created By : [email protected]
1398 || Created On : 28-AUG-2000
1399 || Purpose : Handles the DELETE DML logic for the table.
1400 || Known limitations, enhancements or remarks :
1401 || Change History :
1402 || Who When What
1403 || (reverse chronological order - newest change first)
1404 */
1405 BEGIN
1406
1407 before_dml (
1408 p_action => 'DELETE',
1409 x_rowid => x_rowid
1410 );
1411
1412 IF (x_mode = 'S') THEN
1413 igs_sc_gen_001.set_ctx('R');
1414 END IF;
1415 DELETE FROM igs_pe_hz_parties
1416 WHERE rowid = x_rowid;
1417
1418 IF (SQL%NOTFOUND) THEN
1419 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1420 igs_ge_msg_stack.add;
1421 igs_sc_gen_001.unset_ctx('R');
1422 app_exception.raise_exception;
1423 END IF;
1424 IF (x_mode = 'S') THEN
1425 igs_sc_gen_001.unset_ctx('R');
1426 END IF;
1427
1428
1429 END delete_row;
1430
1431 END igs_pe_hz_parties_pkg;
[Home] [Help]