[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_COM_SCH_PKG
Source
1 PACKAGE BODY igs_uc_com_sch_pkg AS
2 /* $Header: IGSXI10B.pls 115.9 2003/08/13 09:51:51 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_com_sch%ROWTYPE;
6 new_references igs_uc_com_sch%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_school IN NUMBER ,
12 x_school_name IN VARCHAR2,
13 x_name_change_date IN DATE ,
14 x_former_name IN VARCHAR2,
15 x_ncn IN VARCHAR2,
16 x_edexcel_ncn IN VARCHAR2,
17 x_dfee_code IN VARCHAR2,
18 x_country IN NUMBER ,
19 x_lea IN VARCHAR2,
20 x_ucas_status IN VARCHAR2,
21 x_estab_group IN VARCHAR2,
22 x_school_type IN VARCHAR2,
23 x_stats_date IN DATE ,
24 x_number_on_roll IN NUMBER ,
25 x_number_in_5_form IN NUMBER ,
26 x_number_in_6_form IN NUMBER ,
27 x_number_to_he IN NUMBER ,
28 x_imported IN VARCHAR2,
29 x_creation_date IN DATE ,
30 x_created_by IN NUMBER ,
31 x_last_update_date IN DATE ,
32 x_last_updated_by IN NUMBER ,
33 x_last_update_login IN NUMBER
34 ) AS
35 /*
36 || Created By : rgopalan
37 || Created On : 01-OCT-2001
38 || Purpose : Initialises the Old and New references for the columns of the table.
39 || Known limitations, enhancements or remarks :
40 || Change History :
41 || Who When What
42 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
43 || (reverse chronological order - newest change first)
44 */
45
46 CURSOR cur_old_ref_values IS
47 SELECT *
48 FROM IGS_UC_COM_SCH
49 WHERE rowid = x_rowid;
50
51 BEGIN
52
53 l_rowid := x_rowid;
54
55 -- Code for setting the Old and New Reference Values.
56 -- Populate Old Values.
57 OPEN cur_old_ref_values;
58 FETCH cur_old_ref_values INTO old_references;
59 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
60 CLOSE cur_old_ref_values;
61 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
62 igs_ge_msg_stack.add;
63 app_exception.raise_exception;
64 RETURN;
65 END IF;
66 CLOSE cur_old_ref_values;
67
68 -- Populate New Values.
69 new_references.school := x_school;
70 new_references.school_name := x_school_name;
71 new_references.name_change_date := x_name_change_date;
72 new_references.former_name := x_former_name;
73 new_references.ncn := x_ncn;
74 new_references.edexcel_ncn := x_edexcel_ncn;
75 new_references.dfee_code := x_dfee_code;
76 new_references.country := x_country;
77 new_references.lea := x_lea;
78 new_references.ucas_status := x_ucas_status;
79 new_references.estab_group := x_estab_group;
80 new_references.school_type := x_school_type;
81 new_references.stats_date := x_stats_date;
82 new_references.number_on_roll := x_number_on_roll;
83 new_references.number_in_5_form := x_number_in_5_form;
84 new_references.number_in_6_form := x_number_in_6_form;
85 new_references.number_to_he := x_number_to_he;
86 new_references.imported := x_imported;
87
88 IF (p_action = 'UPDATE') THEN
89 new_references.creation_date := old_references.creation_date;
90 new_references.created_by := old_references.created_by;
91 ELSE
92 new_references.creation_date := x_creation_date;
93 new_references.created_by := x_created_by;
94 END IF;
95
96 new_references.last_update_date := x_last_update_date;
97 new_references.last_updated_by := x_last_updated_by;
98 new_references.last_update_login := x_last_update_login;
99
100 END set_column_values;
101
102
103 PROCEDURE check_child_existance IS
104 /*
105 || Created By : rgopalan
106 || Created On : 01-OCT-2001
107 || Purpose : Checks for the existance of Child records.
108 || Known limitations, enhancements or remarks :
109 || Change History :
110 || Who When What
111 || (reverse chronological order - newest change first)
112 */
113 BEGIN
114
115 igs_uc_com_schsites_pkg.get_fk_igs_uc_com_sch (
116 old_references.school
117 );
118
119 END check_child_existance;
120
121
122 FUNCTION get_pk_for_validation (
123 x_school IN NUMBER
124 ) RETURN BOOLEAN AS
125 /*
126 || Created By : rgopalan
127 || Created On : 01-OCT-2001
128 || Purpose : Validates the Primary Key of the table.
129 || Known limitations, enhancements or remarks :
130 || Change History :
131 || Who When What
132 || (reverse chronological order - newest change first)
133 */
134 CURSOR cur_rowid IS
135 SELECT rowid
136 FROM igs_uc_com_sch
137 WHERE school = x_school ;
138
139 lv_rowid cur_rowid%RowType;
140
141 BEGIN
142
143 OPEN cur_rowid;
144 FETCH cur_rowid INTO lv_rowid;
145 IF (cur_rowid%FOUND) THEN
146 CLOSE cur_rowid;
147 RETURN(TRUE);
148 ELSE
149 CLOSE cur_rowid;
150 RETURN(FALSE);
151 END IF;
152
153 END get_pk_for_validation;
154
155 PROCEDURE check_parent_existance AS
156 /*
157 || Created By : rbezawad
158 || Created On : 17-DEC-2002
159 || Purpose : Checks for the existance of Parent records.
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || (reverse chronological order - newest change first)
164 */
165 BEGIN
166
167 IF (((old_references.country = new_references.country)) OR
168 ((new_references.country IS NULL))) THEN
169 NULL;
170 ELSIF NOT igs_uc_ref_apr_pkg.get_pk_for_validation (
171 new_references.country
172 ) THEN
173 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
174 igs_ge_msg_stack.add;
175 app_exception.raise_exception;
176 END IF;
177
178 END check_parent_existance;
179
180
181 PROCEDURE get_fk_igs_uc_ref_apr (
182 x_country IN NUMBER
183 ) AS
184 /*
185 || Created By : RBEZAWAD
186 || Created On : 17-DEC-2002
187 || Purpose : Validates the Foreign Keys for the table.
188 || Known limitations, enhancements or remarks :
189 || Change History :
190 || Who When What
191 || (reverse chronological order - newest change first)
192 */
193 CURSOR cur_rowid IS
194 SELECT rowid
195 FROM igs_uc_com_sch
196 WHERE ((country = x_country));
197
198 lv_rowid cur_rowid%RowType;
199
200 BEGIN
201
202 OPEN cur_rowid;
203 FETCH cur_rowid INTO lv_rowid;
204 IF (cur_rowid%FOUND) THEN
205 CLOSE cur_rowid;
206 fnd_message.set_name ('IGS', 'IGS_UC_UCCH_UCRA_FK');
207 igs_ge_msg_stack.add;
208 app_exception.raise_exception;
209 RETURN;
210 END IF;
211 CLOSE cur_rowid;
212
213 END get_fk_igs_uc_ref_apr;
214
215 PROCEDURE before_dml (
216 p_action IN VARCHAR2,
217 x_rowid IN VARCHAR2,
218 x_school IN NUMBER ,
219 x_school_name IN VARCHAR2,
220 x_name_change_date IN DATE ,
221 x_former_name IN VARCHAR2,
222 x_ncn IN VARCHAR2,
223 x_edexcel_ncn IN VARCHAR2,
224 x_dfee_code IN VARCHAR2,
225 x_country IN NUMBER ,
226 x_lea IN VARCHAR2,
227 x_ucas_status IN VARCHAR2,
228 x_estab_group IN VARCHAR2,
229 x_school_type IN VARCHAR2,
230 x_stats_date IN DATE ,
231 x_number_on_roll IN NUMBER ,
232 x_number_in_5_form IN NUMBER ,
233 x_number_in_6_form IN NUMBER ,
234 x_number_to_he IN NUMBER ,
235 x_imported IN VARCHAR2,
236 x_creation_date IN DATE ,
237 x_created_by IN NUMBER ,
238 x_last_update_date IN DATE ,
239 x_last_updated_by IN NUMBER ,
240 x_last_update_login IN NUMBER
241 ) AS
242 /*
243 || Created By : rgopalan
244 || Created On : 01-OCT-2001
245 || Purpose : Initialises the columns, Checks Constraints, Calls the
246 || Trigger Handlers for the table, before any DML operation.
247 || Known limitations, enhancements or remarks :
248 || Change History :
249 || Who When What
250 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
251 || (reverse chronological order - newest change first)
252 */
253 BEGIN
254
255 set_column_values (
256 p_action,
257 x_rowid,
258 x_school,
259 x_school_name,
260 x_name_change_date,
261 x_former_name,
262 x_ncn,
263 x_edexcel_ncn,
264 x_dfee_code,
265 x_country,
266 x_lea,
267 x_ucas_status,
268 x_estab_group,
269 x_school_type,
270 x_stats_date,
271 x_number_on_roll,
272 x_number_in_5_form,
273 x_number_in_6_form,
274 x_number_to_he,
275 x_imported,
276 x_creation_date,
277 x_created_by,
278 x_last_update_date,
279 x_last_updated_by,
280 x_last_update_login
281 );
282
283 IF (p_action = 'INSERT') THEN
284 -- Call all the procedures related to Before Insert.
285 IF ( get_pk_for_validation(
286 new_references.school
287 )
288 ) THEN
289 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
290 igs_ge_msg_stack.add;
291 app_exception.raise_exception;
292 END IF;
293 check_parent_existance;
294 ELSIF (p_action = 'UPDATE') THEN
295 -- Call all the procedures related to Before Update
296 check_parent_existance;
297 ELSIF (p_action = 'DELETE') THEN
298 -- Call all the procedures related to Before Delete.
299 check_child_existance;
300 ELSIF (p_action = 'VALIDATE_INSERT') THEN
301 -- Call all the procedures related to Before Insert.
302 IF ( get_pk_for_validation (
303 new_references.school
304 )
305 ) THEN
306 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
307 igs_ge_msg_stack.add;
308 app_exception.raise_exception;
309 END IF;
310 ELSIF (p_action = 'VALIDATE_DELETE') THEN
311 check_child_existance;
312 END IF;
313
314 END before_dml;
315
316
317 PROCEDURE insert_row (
318 x_rowid IN OUT NOCOPY VARCHAR2,
319 x_school IN OUT NOCOPY NUMBER,
320 x_school_name IN VARCHAR2,
321 x_name_change_date IN DATE,
322 x_former_name IN VARCHAR2,
323 x_ncn IN VARCHAR2,
324 x_edexcel_ncn IN VARCHAR2,
325 x_dfee_code IN VARCHAR2,
326 x_country IN NUMBER,
327 x_lea IN VARCHAR2,
328 x_ucas_status IN VARCHAR2,
329 x_estab_group IN VARCHAR2,
330 x_school_type IN VARCHAR2,
331 x_stats_date IN DATE,
332 x_number_on_roll IN NUMBER,
333 x_number_in_5_form IN NUMBER,
334 x_number_in_6_form IN NUMBER,
335 x_number_to_he IN NUMBER,
336 x_imported IN VARCHAR2,
337 x_mode IN VARCHAR2
338 ) AS
339 /*
340 || Created By : rgopalan
341 || Created On : 01-OCT-2001 || Purpose : Handles the INSERT DML logic for the table.
342 || Known limitations, enhancements or remarks :
343 || Change History :
344 || Who When What
345 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
346 || (reverse chronological order - newest change first)
347 */
348 CURSOR c IS
349 SELECT rowid
350 FROM igs_uc_com_sch
351 WHERE school = x_school;
352
353 x_last_update_date DATE;
354 x_last_updated_by NUMBER;
355 x_last_update_login NUMBER;
356
357 BEGIN
358
359 x_last_update_date := SYSDATE;
360 IF (x_mode = 'I') THEN
361 x_last_updated_by := 1;
362 x_last_update_login := 0;
363 ELSIF (x_mode = 'R') THEN
364 x_last_updated_by := fnd_global.user_id;
365 IF (x_last_updated_by IS NULL) THEN
366 x_last_updated_by := -1;
367 END IF;
368 x_last_update_login := fnd_global.login_id;
369 IF (x_last_update_login IS NULL) THEN
370 x_last_update_login := -1;
371 END IF;
372 ELSE
373 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
374 igs_ge_msg_stack.add;
375 app_exception.raise_exception;
376 END IF;
377
378 before_dml(
379 p_action => 'INSERT',
380 x_rowid => x_rowid,
381 x_school => x_school,
382 x_school_name => x_school_name,
383 x_name_change_date => x_name_change_date,
384 x_former_name => x_former_name,
385 x_ncn => x_ncn,
386 x_edexcel_ncn => x_edexcel_ncn,
387 x_dfee_code => x_dfee_code,
388 x_country => x_country,
389 x_lea => x_lea,
390 x_ucas_status => x_ucas_status,
391 x_estab_group => x_estab_group,
392 x_school_type => x_school_type,
393 x_stats_date => x_stats_date,
394 x_number_on_roll => x_number_on_roll,
395 x_number_in_5_form => x_number_in_5_form,
396 x_number_in_6_form => x_number_in_6_form,
397 x_number_to_he => x_number_to_he,
398 x_imported => x_imported,
399 x_creation_date => x_last_update_date,
400 x_created_by => x_last_updated_by,
401 x_last_update_date => x_last_update_date,
402 x_last_updated_by => x_last_updated_by,
403 x_last_update_login => x_last_update_login
404 );
405
406 INSERT INTO igs_uc_com_sch (
407 school,
408 school_name,
409 name_change_date,
410 former_name,
411 ncn,
412 edexcel_ncn,
413 dfee_code,
414 country,
415 lea,
416 ucas_status,
417 estab_group,
418 school_type,
419 stats_date,
420 number_on_roll,
421 number_in_5_form,
422 number_in_6_form,
423 number_to_he,
424 imported,
425 creation_date,
426 created_by,
427 last_update_date,
428 last_updated_by,
429 last_update_login
430 ) VALUES (
431 new_references.school,
432 new_references.school_name,
433 new_references.name_change_date,
434 new_references.former_name,
435 new_references.ncn,
436 new_references.edexcel_ncn,
437 new_references.dfee_code,
438 new_references.country,
439 new_references.lea,
440 new_references.ucas_status,
441 new_references.estab_group,
442 new_references.school_type,
443 new_references.stats_date,
444 new_references.number_on_roll,
445 new_references.number_in_5_form,
446 new_references.number_in_6_form,
447 new_references.number_to_he,
448 new_references.imported,
449 x_last_update_date,
450 x_last_updated_by,
451 x_last_update_date,
452 x_last_updated_by,
453 x_last_update_login
454 );
455
456 OPEN c;
457 FETCH c INTO x_rowid;
458 IF (c%NOTFOUND) THEN
459 CLOSE c;
460 RAISE NO_DATA_FOUND;
461 END IF;
462 CLOSE c;
463
464 END insert_row;
465
466
467 PROCEDURE lock_row (
468 x_rowid IN VARCHAR2,
469 x_school IN NUMBER,
470 x_school_name IN VARCHAR2,
471 x_name_change_date IN DATE,
472 x_former_name IN VARCHAR2,
473 x_ncn IN VARCHAR2,
474 x_edexcel_ncn IN VARCHAR2,
475 x_dfee_code IN VARCHAR2,
476 x_country IN NUMBER,
477 x_lea IN VARCHAR2,
478 x_ucas_status IN VARCHAR2,
479 x_estab_group IN VARCHAR2,
480 x_school_type IN VARCHAR2,
481 x_stats_date IN DATE,
482 x_number_on_roll IN NUMBER,
483 x_number_in_5_form IN NUMBER,
484 x_number_in_6_form IN NUMBER,
485 x_number_to_he IN NUMBER,
486 x_imported IN VARCHAR2
487 ) AS
488 /*
489 || Created By : rgopalan
490 || Created On : 01-OCT-2001
491 || Purpose : Handles the LOCK mechanism for the table.
492 || Known limitations, enhancements or remarks :
493 || Change History :
494 || (reverse chronological order - newest change first)
495 || Who When What
496 || rbezawad 26-Dec-2002 Modified Lock_row() procedure as the 5 columns STATS_DATE,NUMBER_ON_ROLL, NUMBER_IN_5_FORM,
497 || NUMBER_IN_6_FORM, NUMBER_TO_HE are made as Non-Mandatory w.r.t. Bug 2708703.
498 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
499 || smaddali 13-aug-03 Modified copmarision of old and new values for stats_date for bug#3091973
500 */
501 CURSOR c1 IS
502 SELECT
503 school_name,
504 name_change_date,
505 former_name,
506 ncn,
507 edexcel_ncn,
508 dfee_code,
509 country,
510 lea,
511 ucas_status,
512 estab_group,
513 school_type,
514 stats_date,
515 number_on_roll,
516 number_in_5_form,
517 number_in_6_form,
518 number_to_he,
519 imported
520 FROM igs_uc_com_sch
521 WHERE rowid = x_rowid
522 FOR UPDATE NOWAIT;
523
524 tlinfo c1%ROWTYPE;
525
526 BEGIN
527
528 OPEN c1;
529 FETCH c1 INTO tlinfo;
530 IF (c1%notfound) THEN
531 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
532 igs_ge_msg_stack.add;
533 CLOSE c1;
534 app_exception.raise_exception;
535 RETURN;
536 END IF;
537 CLOSE c1;
538 -- smaddali corrected typo in comparision of stats_date , it was comparing with ucas_status instead of stats_date, bug#3091973
539 IF (
540 ((tlinfo.school_name = x_school_name) OR ((tlinfo.school_name IS NULL) AND (X_school_name IS NULL)))
541 AND ((tlinfo.name_change_date = x_name_change_date) OR ((tlinfo.name_change_date IS NULL) AND (X_name_change_date IS NULL)))
542 AND ((tlinfo.former_name = x_former_name) OR ((tlinfo.former_name IS NULL) AND (X_former_name IS NULL)))
543 AND ((tlinfo.ncn = x_ncn) OR ((tlinfo.ncn IS NULL) AND (X_ncn IS NULL)))
544 AND ((tlinfo.edexcel_ncn = x_edexcel_ncn) OR ((tlinfo.edexcel_ncn IS NULL) AND (X_edexcel_ncn IS NULL)))
545 AND ((tlinfo.dfee_code = x_dfee_code) OR ((tlinfo.dfee_code IS NULL) AND (X_dfee_code IS NULL)))
546 AND ((tlinfo.country = x_country) OR ((tlinfo.country IS NULL) AND (X_country IS NULL)))
547 AND ((tlinfo.lea = x_lea) OR ((tlinfo.lea IS NULL) AND (X_lea IS NULL)))
548 AND ((tlinfo.ucas_status = x_ucas_status) OR ((tlinfo.ucas_status IS NULL) AND (X_ucas_status IS NULL)))
549 AND (tlinfo.estab_group = x_estab_group)
550 AND (tlinfo.school_type = x_school_type)
551 AND ((tlinfo.stats_date = x_stats_date) OR ((tlinfo.stats_date IS NULL) AND (x_stats_date IS NULL)))
552 AND ((tlinfo.number_on_roll = x_number_on_roll) OR ((tlinfo.number_on_roll IS NULL) AND (x_number_on_roll IS NULL)))
553 AND ((tlinfo.number_in_5_form = x_number_in_5_form) OR ((tlinfo.number_in_5_form IS NULL) AND (x_number_in_5_form IS NULL)))
554 AND ((tlinfo.number_in_6_form = x_number_in_6_form) OR ((tlinfo.number_in_6_form IS NULL) AND (x_number_in_6_form IS NULL)))
555 AND ((tlinfo.number_to_he = x_number_to_he) OR ((tlinfo.number_to_he IS NULL) AND (x_number_to_he IS NULL)))
556 AND (tlinfo.imported = x_imported)
557 ) THEN
558 NULL;
559 ELSE
560 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
561 igs_ge_msg_stack.add;
562 app_exception.raise_exception;
563 END IF;
564
565 RETURN;
566
567 END lock_row;
568
569
570 PROCEDURE update_row (
571 x_rowid IN VARCHAR2,
572 x_school IN NUMBER,
573 x_school_name IN VARCHAR2,
574 x_name_change_date IN DATE,
575 x_former_name IN VARCHAR2,
576 x_ncn IN VARCHAR2,
577 x_edexcel_ncn IN VARCHAR2,
578 x_dfee_code IN VARCHAR2,
579 x_country IN NUMBER,
580 x_lea IN VARCHAR2,
581 x_ucas_status IN VARCHAR2,
582 x_estab_group IN VARCHAR2,
583 x_school_type IN VARCHAR2,
584 x_stats_date IN DATE,
585 x_number_on_roll IN NUMBER,
586 x_number_in_5_form IN NUMBER,
587 x_number_in_6_form IN NUMBER,
588 x_number_to_he IN NUMBER,
589 x_imported IN VARCHAR2,
590 x_mode IN VARCHAR2
591 ) AS
592 /*
593 || Created By : rgopalan
594 || Created On : 01-OCT-2001 || Purpose : Handles the UPDATE DML logic for the table.
595 || Known limitations, enhancements or remarks :
596 || Change History :
597 || Who When What
598 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
599 || (reverse chronological order - newest change first)
600 */
601 x_last_update_date DATE ;
602 x_last_updated_by NUMBER;
603 x_last_update_login NUMBER;
604
605 BEGIN
606
607 x_last_update_date := SYSDATE;
608 IF (X_MODE = 'I') THEN
609 x_last_updated_by := 1;
610 x_last_update_login := 0;
611 ELSIF (x_mode = 'R') THEN
612 x_last_updated_by := fnd_global.user_id;
613 IF x_last_updated_by IS NULL THEN
614 x_last_updated_by := -1;
615 END IF;
616 x_last_update_login := fnd_global.login_id;
617 IF (x_last_update_login IS NULL) THEN
618 x_last_update_login := -1;
619 END IF;
620 ELSE
621 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
622 igs_ge_msg_stack.add;
623 app_exception.raise_exception;
624 END IF;
625
626 before_dml(
627 p_action => 'UPDATE',
628 x_rowid => x_rowid,
629 x_school => x_school,
630 x_school_name => x_school_name,
631 x_name_change_date => x_name_change_date,
632 x_former_name => x_former_name,
633 x_ncn => x_ncn,
634 x_edexcel_ncn => x_edexcel_ncn,
635 x_dfee_code => x_dfee_code,
636 x_country => x_country,
637 x_lea => x_lea,
638 x_ucas_status => x_ucas_status,
639 x_estab_group => x_estab_group,
640 x_school_type => x_school_type,
641 x_stats_date => x_stats_date,
642 x_number_on_roll => x_number_on_roll,
643 x_number_in_5_form => x_number_in_5_form,
644 x_number_in_6_form => x_number_in_6_form,
645 x_number_to_he => x_number_to_he,
646 x_imported => x_imported,
647 x_creation_date => x_last_update_date,
648 x_created_by => x_last_updated_by,
649 x_last_update_date => x_last_update_date,
650 x_last_updated_by => x_last_updated_by,
651 x_last_update_login => x_last_update_login
652 );
653
654 UPDATE igs_uc_com_sch
655 SET
656 school_name = new_references.school_name,
657 name_change_date = new_references.name_change_date,
658 former_name = new_references.former_name,
659 ncn = new_references.ncn,
660 edexcel_ncn = new_references.edexcel_ncn,
661 dfee_code = new_references.dfee_code,
662 country = new_references.country,
663 lea = new_references.lea,
664 ucas_status = new_references.ucas_status,
665 estab_group = new_references.estab_group,
666 school_type = new_references.school_type,
667 stats_date = new_references.stats_date,
668 number_on_roll = new_references.number_on_roll,
669 number_in_5_form = new_references.number_in_5_form,
670 number_in_6_form = new_references.number_in_6_form,
671 number_to_he = new_references.number_to_he,
672 imported = new_references.imported,
673 last_update_date = x_last_update_date,
674 last_updated_by = x_last_updated_by,
675 last_update_login = x_last_update_login
676 WHERE rowid = x_rowid;
677
678 IF (SQL%NOTFOUND) THEN
679 RAISE NO_DATA_FOUND;
680 END IF;
681
682 END update_row;
683
684
685 PROCEDURE add_row (
686 x_rowid IN OUT NOCOPY VARCHAR2,
687 x_school IN OUT NOCOPY NUMBER,
688 x_school_name IN VARCHAR2,
689 x_name_change_date IN DATE,
690 x_former_name IN VARCHAR2,
691 x_ncn IN VARCHAR2,
692 x_edexcel_ncn IN VARCHAR2,
693 x_dfee_code IN VARCHAR2,
694 x_country IN NUMBER,
695 x_lea IN VARCHAR2,
696 x_ucas_status IN VARCHAR2,
697 x_estab_group IN VARCHAR2,
698 x_school_type IN VARCHAR2,
699 x_stats_date IN DATE,
700 x_number_on_roll IN NUMBER,
701 x_number_in_5_form IN NUMBER,
702 x_number_in_6_form IN NUMBER,
703 x_number_to_he IN NUMBER,
704 x_imported IN VARCHAR2,
705 x_mode IN VARCHAR2
706 ) AS
707 /*
708 || Created By : rgopalan
709 || Created On : 01-OCT-2001 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
710 || Known limitations, enhancements or remarks :
711 || Change History :
712 || Who When What
713 || smaddali 10-jun-03 obsoleting timestamp column for ucfd203 - multiple cycles build , bug#2669208
714 || (reverse chronological order - newest change first)
715 */
716 CURSOR c1 IS
717 SELECT rowid
718 FROM igs_uc_com_sch
719 WHERE school = x_school;
720
721 BEGIN
722
723 OPEN c1;
724 FETCH c1 INTO x_rowid;
725 IF (c1%NOTFOUND) THEN
726 CLOSE c1;
727
728 insert_row (
729 x_rowid,
730 x_school,
731 x_school_name,
732 x_name_change_date,
733 x_former_name,
734 x_ncn,
735 x_edexcel_ncn,
736 x_dfee_code,
737 x_country,
738 x_lea,
739 x_ucas_status,
740 x_estab_group,
741 x_school_type,
742 x_stats_date,
743 x_number_on_roll,
744 x_number_in_5_form,
745 x_number_in_6_form,
746 x_number_to_he,
747 x_imported,
748 x_mode
749 );
750 RETURN;
751 END IF;
752 CLOSE c1;
753
754 update_row (
755 x_rowid,
756 x_school,
757 x_school_name,
758 x_name_change_date,
759 x_former_name,
760 x_ncn,
761 x_edexcel_ncn,
762 x_dfee_code,
763 x_country,
764 x_lea,
765 x_ucas_status,
766 x_estab_group,
767 x_school_type,
768 x_stats_date,
769 x_number_on_roll,
770 x_number_in_5_form,
771 x_number_in_6_form,
772 x_number_to_he,
773 x_imported,
774 x_mode
775 );
776
777 END add_row;
778
779
780 PROCEDURE delete_row (
781 x_rowid IN VARCHAR2
782 ) AS
783 /*
784 || Created By : rgopalan
785 || Created On : 01-OCT-2001
786 || Purpose : Handles the DELETE DML logic for the table.
787 || Known limitations, enhancements or remarks :
788 || Change History :
789 || Who When What
790 || (reverse chronological order - newest change first)
791 */
792 BEGIN
793
794 before_dml (
795 p_action => 'DELETE',
796 x_rowid => x_rowid
797 );
798
799 DELETE FROM igs_uc_com_sch
800 WHERE rowid = x_rowid;
801
802 IF (SQL%NOTFOUND) THEN
803 RAISE NO_DATA_FOUND;
804 END IF;
805
806 END delete_row;
807
808
809 END igs_uc_com_sch_pkg;