[Home] [Help]
24: --sarakshi 23-Feb-2003 Enh#2797116,modified cursor gc_coo_rec in crsp_ins_crs_ver procedure.Also modified
25: -- cursor c_coo_new in crsp_ins_coi_rec procedure.
26: --vvutukur 01-Nov-2002 Enh#2636716.Modifications done in crsp_ins_unit_section.
27: --vvutukur 28-Oct-2002 Enh#2613933.Modifications done in crsp_ins_unit_section.
28: --shtatiko 21-OCT-2002 Added two parameters, program_length and program_length_measurement, to insert_row call of IGS_PS_OFR_OPT_PKG as per bug# 2608227.
29: --amuthu 24-Sep-02 added core_ind column to the cursor c_posu and also
30: -- added it to the insert row call of igs_ps_pat_study_unt_pkg
31: --jbegum 11-Sep-02 1)As part of bug#2563596 modified CURSOR us_req_refcd.
32: -- 2)Also added a for loop which inserts into the igs_ps_usec_ref_cd
71: cst_ret_message_name CONSTANT VARCHAR2(30) := 'IGS_PS_FAIL_COPY_PRGVER_DETAI';
72: cst_max_error_range CONSTANT NUMBER := -20999;
73: cst_min_error_range CONSTANT NUMBER := -20000;
74: gv_cv_rec IGS_PS_VER%ROWTYPE;
75: gv_con_rec IGS_PS_OFR_NOTE%ROWTYPE;
76: gv_coon_rec IGS_PS_OFR_OPT_NOTE%ROWTYPE;
77: gv_co_rec IGS_PS_OFR%ROWTYPE;
78: gv_coo_rec IGS_PS_OFR_OPT%ROWTYPE;
79: gv_ceprcd_rec IGS_PS_ENT_PT_REF_CD%ROWTYPE;
72: cst_max_error_range CONSTANT NUMBER := -20999;
73: cst_min_error_range CONSTANT NUMBER := -20000;
74: gv_cv_rec IGS_PS_VER%ROWTYPE;
75: gv_con_rec IGS_PS_OFR_NOTE%ROWTYPE;
76: gv_coon_rec IGS_PS_OFR_OPT_NOTE%ROWTYPE;
77: gv_co_rec IGS_PS_OFR%ROWTYPE;
78: gv_coo_rec IGS_PS_OFR_OPT%ROWTYPE;
79: gv_ceprcd_rec IGS_PS_ENT_PT_REF_CD%ROWTYPE;
80: gv_calulink_rec IGS_PS_ANL_LOAD_U_LN%ROWTYPE;
73: cst_min_error_range CONSTANT NUMBER := -20000;
74: gv_cv_rec IGS_PS_VER%ROWTYPE;
75: gv_con_rec IGS_PS_OFR_NOTE%ROWTYPE;
76: gv_coon_rec IGS_PS_OFR_OPT_NOTE%ROWTYPE;
77: gv_co_rec IGS_PS_OFR%ROWTYPE;
78: gv_coo_rec IGS_PS_OFR_OPT%ROWTYPE;
79: gv_ceprcd_rec IGS_PS_ENT_PT_REF_CD%ROWTYPE;
80: gv_calulink_rec IGS_PS_ANL_LOAD_U_LN%ROWTYPE;
81: gv_coi_rec IGS_PS_OFR_INST%ROWTYPE;
74: gv_cv_rec IGS_PS_VER%ROWTYPE;
75: gv_con_rec IGS_PS_OFR_NOTE%ROWTYPE;
76: gv_coon_rec IGS_PS_OFR_OPT_NOTE%ROWTYPE;
77: gv_co_rec IGS_PS_OFR%ROWTYPE;
78: gv_coo_rec IGS_PS_OFR_OPT%ROWTYPE;
79: gv_ceprcd_rec IGS_PS_ENT_PT_REF_CD%ROWTYPE;
80: gv_calulink_rec IGS_PS_ANL_LOAD_U_LN%ROWTYPE;
81: gv_coi_rec IGS_PS_OFR_INST%ROWTYPE;
82: gv_cop_rec IGS_PS_OFR_PAT%ROWTYPE;
77: gv_co_rec IGS_PS_OFR%ROWTYPE;
78: gv_coo_rec IGS_PS_OFR_OPT%ROWTYPE;
79: gv_ceprcd_rec IGS_PS_ENT_PT_REF_CD%ROWTYPE;
80: gv_calulink_rec IGS_PS_ANL_LOAD_U_LN%ROWTYPE;
81: gv_coi_rec IGS_PS_OFR_INST%ROWTYPE;
82: gv_cop_rec IGS_PS_OFR_PAT%ROWTYPE;
83: gv_copn_rec IGS_PS_OFR_PAT_NOTE%ROWTYPE;
84: gv_ref_num IGS_GE_NOTE.reference_number%TYPE;
85: gv_coo_seq_num IGS_PS_OFR_OPT.coo_id%TYPE;
78: gv_coo_rec IGS_PS_OFR_OPT%ROWTYPE;
79: gv_ceprcd_rec IGS_PS_ENT_PT_REF_CD%ROWTYPE;
80: gv_calulink_rec IGS_PS_ANL_LOAD_U_LN%ROWTYPE;
81: gv_coi_rec IGS_PS_OFR_INST%ROWTYPE;
82: gv_cop_rec IGS_PS_OFR_PAT%ROWTYPE;
83: gv_copn_rec IGS_PS_OFR_PAT_NOTE%ROWTYPE;
84: gv_ref_num IGS_GE_NOTE.reference_number%TYPE;
85: gv_coo_seq_num IGS_PS_OFR_OPT.coo_id%TYPE;
86: gv_cop_seq_num IGS_PS_OFR_PAT.cop_id%TYPE;
79: gv_ceprcd_rec IGS_PS_ENT_PT_REF_CD%ROWTYPE;
80: gv_calulink_rec IGS_PS_ANL_LOAD_U_LN%ROWTYPE;
81: gv_coi_rec IGS_PS_OFR_INST%ROWTYPE;
82: gv_cop_rec IGS_PS_OFR_PAT%ROWTYPE;
83: gv_copn_rec IGS_PS_OFR_PAT_NOTE%ROWTYPE;
84: gv_ref_num IGS_GE_NOTE.reference_number%TYPE;
85: gv_coo_seq_num IGS_PS_OFR_OPT.coo_id%TYPE;
86: gv_cop_seq_num IGS_PS_OFR_PAT.cop_id%TYPE;
87: gv_ca_rec IGS_PS_AWARD%ROWTYPE;
81: gv_coi_rec IGS_PS_OFR_INST%ROWTYPE;
82: gv_cop_rec IGS_PS_OFR_PAT%ROWTYPE;
83: gv_copn_rec IGS_PS_OFR_PAT_NOTE%ROWTYPE;
84: gv_ref_num IGS_GE_NOTE.reference_number%TYPE;
85: gv_coo_seq_num IGS_PS_OFR_OPT.coo_id%TYPE;
86: gv_cop_seq_num IGS_PS_OFR_PAT.cop_id%TYPE;
87: gv_ca_rec IGS_PS_AWARD%ROWTYPE;
88: gv_cao_rec IGS_PS_AWD_OWN%ROWTYPE;
89: gv_cow_rec IGS_PS_OWN%ROWTYPE;
82: gv_cop_rec IGS_PS_OFR_PAT%ROWTYPE;
83: gv_copn_rec IGS_PS_OFR_PAT_NOTE%ROWTYPE;
84: gv_ref_num IGS_GE_NOTE.reference_number%TYPE;
85: gv_coo_seq_num IGS_PS_OFR_OPT.coo_id%TYPE;
86: gv_cop_seq_num IGS_PS_OFR_PAT.cop_id%TYPE;
87: gv_ca_rec IGS_PS_AWARD%ROWTYPE;
88: gv_cao_rec IGS_PS_AWD_OWN%ROWTYPE;
89: gv_cow_rec IGS_PS_OWN%ROWTYPE;
90: gv_cvn_rec IGS_PS_VER_NOTE%ROWTYPE;
197: yr_num = gv_cal_rec.yr_num AND
198: effective_start_dt = gv_cal_rec.effective_start_dt;
199: CURSOR c_co_rec IS
200: SELECT *
201: FROM IGS_PS_OFR
202: WHERE course_cd = p_old_course_cd AND
203: version_number = p_old_version_number;
204: CURSOR gc_coo_rec IS
205: SELECT *
202: WHERE course_cd = p_old_course_cd AND
203: version_number = p_old_version_number;
204: CURSOR gc_coo_rec IS
205: SELECT *
206: FROM IGS_PS_OFR_OPT
207: WHERE course_cd = p_old_course_cd AND
208: version_number = p_old_version_number AND
209: cal_type = gv_co_rec.cal_type
210: AND delete_flag = 'N';
221: -- Only interested in notes that are not OLE type as
222: -- currently unable to copy long raw field within PL/SQL
223: CURSOR gc_con_rec IS
224: SELECT *
225: FROM IGS_PS_OFR_NOTE con
226: WHERE con.course_cd = p_old_course_cd AND
227: con.version_number = p_old_version_number AND
228: cal_type = gv_co_rec.cal_type AND
229: EXISTS (SELECT 1
233: -- Only interested in notes that are not OLE type as
234: -- currently unable to copy long raw field within PL/SQL
235: CURSOR gc_coon_rec IS
236: SELECT *
237: FROM IGS_PS_OFR_OPT_NOTE coon
238: WHERE coon.course_cd = p_old_course_cd AND
239: coon.version_number = p_old_version_number AND
240: coon.cal_type = gv_coo_rec.cal_type AND
241: coon.location_cd = gv_coo_rec.location_cd AND
244: EXISTS (SELECT 1
245: FROM IGS_GE_NOTE nte
246: WHERE nte.reference_number = coon.reference_number AND
247: nte.note_text IS NOT NULL);
248: -- Find the latest IGS_CA_INST of IGS_PS_OFR_INST
249: -- which returns the list in descending order, and the first record
250: -- (ie. the latest instance) will be selected
251: CURSOR gc_coi_rec IS
252: SELECT *
249: -- which returns the list in descending order, and the first record
250: -- (ie. the latest instance) will be selected
251: CURSOR gc_coi_rec IS
252: SELECT *
253: FROM IGS_PS_OFR_INST coi
254: WHERE coi.course_cd = p_old_course_cd AND
255: coi.version_number = p_old_version_number AND
256: coi.cal_type = gv_co_rec.cal_type
257: ORDER BY coi.ci_end_dt DESC, coi.ci_start_dt DESC;
256: coi.cal_type = gv_co_rec.cal_type
257: ORDER BY coi.ci_end_dt DESC, coi.ci_start_dt DESC;
258: CURSOR gc_cop_rec IS
259: SELECT *
260: FROM IGS_PS_OFR_PAT
261: WHERE course_cd = p_old_course_cd AND
262: version_number = p_old_version_number AND
263: cal_type = gv_coi_rec.cal_type AND
264: ci_sequence_number = gv_coi_rec.ci_sequence_number;
265: -- Only interested in notes that are not OLE type as
266: -- currently unable to copy long raw field within PL/SQL
267: CURSOR gc_copn_rec IS
268: SELECT *
269: FROM IGS_PS_OFR_PAT_NOTE cop
270: WHERE cop.cop_id = gv_cop_rec.cop_id AND
271: EXISTS (SELECT 1
272: FROM IGS_GE_NOTE nte
273: WHERE nte.reference_number = cop.reference_number AND
278: CURSOR gc_ref_num IS
279: SELECT IGS_GE_NOTE_RF_NUM_S.NEXTVAL
280: FROM DUAL;
281: CURSOR gc_coo_seq_num IS
282: SELECT IGS_PS_OFR_OPT_COO_ID_S.NEXTVAL
283: FROM DUAL;
284:
285: -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_OPT_NOTE records
286: PROCEDURE crsp_ins_coon_rec (
281: CURSOR gc_coo_seq_num IS
282: SELECT IGS_PS_OFR_OPT_COO_ID_S.NEXTVAL
283: FROM DUAL;
284:
285: -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_OPT_NOTE records
286: PROCEDURE crsp_ins_coon_rec (
287: p_new_course_cd IGS_PS_VER.course_cd%TYPE,
288: p_new_version_number IGS_PS_VER.version_number%TYPE) AS
289:
309: X_MODE =>'R');
310: End Loop;
311:
312:
313: -- inserting IGS_PS_OFR_OPT_NOTE records
314: x_rowid := NULL;
315: IGS_PS_OFR_OPT_NOTE_PKG.INSERT_ROW(
316: X_ROWID => X_ROWID,
317: X_COURSE_CD => p_new_course_cd,
311:
312:
313: -- inserting IGS_PS_OFR_OPT_NOTE records
314: x_rowid := NULL;
315: IGS_PS_OFR_OPT_NOTE_PKG.INSERT_ROW(
316: X_ROWID => X_ROWID,
317: X_COURSE_CD => p_new_course_cd,
318: X_VERSION_NUMBER => p_new_version_number,
319: X_CAL_TYPE => gv_coon_rec.cal_type,
333: ELSE
334: App_Exception.Raise_Exception;
335: END IF;
336: END crsp_ins_coon_rec;
337: -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_PAT_NOTE records
338:
339: PROCEDURE crsp_ins_copn_rec (
340: p_new_course_cd IGS_PS_VER.course_cd%TYPE,
341: p_new_version_number IGS_PS_VER.version_number%TYPE) AS
364: X_MODE => 'R'
365: );
366: END LOOP;
367:
368: -- inserting IGS_PS_OFR_PAT_NOTE records
369: x_rowid := NULL;
370: IGS_PS_OFR_PAT_NOTE_PKG.INSERT_ROW(
371: X_ROWID => x_rowid,
372: X_COURSE_CD => p_new_course_cd,
366: END LOOP;
367:
368: -- inserting IGS_PS_OFR_PAT_NOTE records
369: x_rowid := NULL;
370: IGS_PS_OFR_PAT_NOTE_PKG.INSERT_ROW(
371: X_ROWID => x_rowid,
372: X_COURSE_CD => p_new_course_cd,
373: X_CI_SEQUENCE_NUMBER => gv_copn_rec.ci_sequence_number,
374: X_CAL_TYPE => gv_copn_rec.cal_type,
388: ELSE
389: App_Exception.Raise_Exception;
390: END IF;
391: END crsp_ins_copn_rec;
392: -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_PAT records
393:
394: PROCEDURE crsp_ins_cop_rec (
395: p_new_course_cd IGS_PS_VER.course_cd%TYPE,
396: p_new_version_number IGS_PS_VER.version_number%TYPE) AS
404: FROM IGS_AS_GRD_SCHEMA gs
405: WHERE gs.grading_schema_cd = cp_gs_cd;
406: BEGIN
407: -- select the next sequence_number from the system
408: SELECT IGS_PS_OFR_PAT_COP_ID_S.NEXTVAL
409: INTO gv_cop_seq_num
410: FROM DUAL;
411: -- get the latest grading schema version number
412: OPEN c_latest_gs_version (
433: gv_cop_rec.adm_contact_person_id,
434: v_message_name) = FALSE THEN
435: gv_cop_rec.adm_contact_person_id := NULL;
436: END IF;
437: -- inserting IGS_PS_OFR_PAT records with this next sequence_number
438: x_rowid := NULL;
439: IGS_PS_OFR_PAT_PKG.INSERT_ROW(
440: X_ROWID => X_ROWID,
441: X_COURSE_CD => p_new_course_cd,
435: gv_cop_rec.adm_contact_person_id := NULL;
436: END IF;
437: -- inserting IGS_PS_OFR_PAT records with this next sequence_number
438: x_rowid := NULL;
439: IGS_PS_OFR_PAT_PKG.INSERT_ROW(
440: X_ROWID => X_ROWID,
441: X_COURSE_CD => p_new_course_cd,
442: X_CI_SEQUENCE_NUMBER => gv_cop_rec.ci_sequence_number,
443: X_CAL_TYPE => gv_cop_rec.cal_type,
464: X_GRADING_SCHEMA_CD => gv_cop_rec.grading_schema_cd,
465: X_GS_VERSION_NUMBER => gv_cop_rec.gs_version_number,
466: X_MODE => 'R'
467: );
468: -- calling procedure to insert IGS_PS_OFR_PAT_NOTE records
469: -- associated with each IGS_PS_OFR_PAT record
470: OPEN gc_copn_rec;
471: LOOP
472: FETCH gc_copn_rec INTO gv_copn_rec;
465: X_GS_VERSION_NUMBER => gv_cop_rec.gs_version_number,
466: X_MODE => 'R'
467: );
468: -- calling procedure to insert IGS_PS_OFR_PAT_NOTE records
469: -- associated with each IGS_PS_OFR_PAT record
470: OPEN gc_copn_rec;
471: LOOP
472: FETCH gc_copn_rec INTO gv_copn_rec;
473: IF gc_copn_rec%FOUND THEN
494: ELSE
495: App_Exception.Raise_Exception;
496: END IF;
497: END crsp_ins_cop_rec;
498: -- procedure for inserting new IGS_GE_NOTE and IGS_PS_OFR_NOTE records
499: PROCEDURE crsp_ins_con_rec (
500: p_new_course_cd IGS_PS_VER.course_cd%TYPE,
501: p_new_version_number IGS_PS_VER.version_number%TYPE) AS
502: CURSOR
525: X_NOTE_TEXT => Rec_SGN.note_text,
526: X_MODE => 'R'
527: );
528: END LOOP;
529: -- inserting IGS_PS_OFR_NOTE records
530: x_rowid := NULL;
531: IGS_PS_OFR_NOTE_PKG.INSERT_ROW (
532: X_ROWID => x_rowid,
533: X_COURSE_CD => p_new_course_cd,
527: );
528: END LOOP;
529: -- inserting IGS_PS_OFR_NOTE records
530: x_rowid := NULL;
531: IGS_PS_OFR_NOTE_PKG.INSERT_ROW (
532: X_ROWID => x_rowid,
533: X_COURSE_CD => p_new_course_cd,
534: X_REFERENCE_NUMBER => gv_ref_num,
535: X_CAL_TYPE => gv_con_rec.cal_type,
683: ELSE
684: App_Exception.Raise_Exception;
685: END IF;
686: END crspl_ins_cooac_rec;
687: -- procedure for inserting new IGS_PS_OFR_OPT records
688: --modified by shtatiko on 21-OCT-2002 to incorporate the addition of two new columns to IGS_PS_OFR_OPT viz. program_length, program_length_measurement.
689: --this has been done as per bug# 2608227.
690: PROCEDURE crsp_ins_coo_rec(
691: p_new_course_cd IGS_PS_VER.course_cd%TYPE,
684: App_Exception.Raise_Exception;
685: END IF;
686: END crspl_ins_cooac_rec;
687: -- procedure for inserting new IGS_PS_OFR_OPT records
688: --modified by shtatiko on 21-OCT-2002 to incorporate the addition of two new columns to IGS_PS_OFR_OPT viz. program_length, program_length_measurement.
689: --this has been done as per bug# 2608227.
690: PROCEDURE crsp_ins_coo_rec(
691: p_new_course_cd IGS_PS_VER.course_cd%TYPE,
692: p_new_version_number IGS_PS_VER.version_number%TYPE) AS
706: coous.location_cd = gv_coo_rec.location_cd AND
707: coous.attendance_mode = gv_coo_rec.attendance_mode AND
708: coous.attendance_type = gv_coo_rec.attendance_type AND
709: EXISTS (SELECT 'X'
710: FROM IGS_PS_OFR_UNIT_SET cous
711: WHERE cous.course_cd = p_new_course_cd AND
712: cous.crv_version_number = p_new_version_number AND
713: cous.cal_type = coous.cal_type AND
714: cous.unit_set_cd = coous.unit_set_cd AND
714: cous.unit_set_cd = coous.unit_set_cd AND
715: cous.us_version_number = coous.us_version_number);
716: l_org_id NUMBER(15);
717: BEGIN
718: -- select the next IGS_PS_OFR_OPT reference_number
719: -- from the system
720: OPEN gc_coo_seq_num;
721: FETCH gc_coo_seq_num INTO gv_coo_seq_num;
722: CLOSE gc_coo_seq_num;
721: FETCH gc_coo_seq_num INTO gv_coo_seq_num;
722: CLOSE gc_coo_seq_num;
723: x_rowid := NULL;
724: l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
725: IGS_PS_OFR_OPT_PKG.INSERT_ROW(
726: X_ROWID => x_rowid,
727: X_COURSE_CD => p_new_course_cd,
728: X_VERSION_NUMBER => p_new_version_number,
729: X_CAL_TYPE => gv_coo_rec.cal_type,
761: X_ORG_ID => l_org_id ,
762: x_program_length => gv_coo_rec.program_length, --added as per bug# 2608227
763: x_program_length_measurement => gv_coo_rec.program_length_measurement --added as per bug# 2608227
764: );
765: -- calling procedure to insert IGS_PS_OFR_OPT_NOTE records
766: -- associated with each IGS_PS_OFR_OPT record
767: OPEN gc_coon_rec;
768: LOOP
769: FETCH gc_coon_rec INTO gv_coon_rec;
762: x_program_length => gv_coo_rec.program_length, --added as per bug# 2608227
763: x_program_length_measurement => gv_coo_rec.program_length_measurement --added as per bug# 2608227
764: );
765: -- calling procedure to insert IGS_PS_OFR_OPT_NOTE records
766: -- associated with each IGS_PS_OFR_OPT record
767: OPEN gc_coon_rec;
768: LOOP
769: FETCH gc_coon_rec INTO gv_coon_rec;
770: IF gc_coon_rec%FOUND THEN
774: END IF;
775: END LOOP;
776: CLOSE gc_coon_rec;
777: -- calling procedure to insert IGS_PS_ENT_PT_REF_CD records
778: -- associated with each IGS_PS_OFR instance record
779: OPEN gc_ceprcd_rec;
780: LOOP
781: FETCH gc_ceprcd_rec INTO gv_ceprcd_rec;
782: IF gc_ceprcd_rec%FOUND THEN
828: ELSE
829: App_Exception.Raise_Exception;
830: END IF;
831: END crsp_ins_coo_rec;
832: -- procedure for inserting new IGS_PS_OFR_INST records
833: PROCEDURE crsp_ins_coi_rec (
834: p_new_course_cd IGS_PS_VER.course_cd%TYPE,
835: p_new_version_number IGS_PS_VER.version_number%TYPE)
836: IS
836: IS
837: BEGIN
838: DECLARE
839: CURSOR c_coo_new (
840: cp_cal_type IGS_PS_OFR_OPT.cal_type%TYPE,
841: cp_location_cd IGS_PS_OFR_OPT.location_cd%TYPE,
842: cp_attendance_mode IGS_PS_OFR_OPT.attendance_mode%TYPE,
843: cp_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE) IS
844: SELECT 'x'
837: BEGIN
838: DECLARE
839: CURSOR c_coo_new (
840: cp_cal_type IGS_PS_OFR_OPT.cal_type%TYPE,
841: cp_location_cd IGS_PS_OFR_OPT.location_cd%TYPE,
842: cp_attendance_mode IGS_PS_OFR_OPT.attendance_mode%TYPE,
843: cp_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE) IS
844: SELECT 'x'
845: FROM IGS_PS_OFR_OPT coo
838: DECLARE
839: CURSOR c_coo_new (
840: cp_cal_type IGS_PS_OFR_OPT.cal_type%TYPE,
841: cp_location_cd IGS_PS_OFR_OPT.location_cd%TYPE,
842: cp_attendance_mode IGS_PS_OFR_OPT.attendance_mode%TYPE,
843: cp_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE) IS
844: SELECT 'x'
845: FROM IGS_PS_OFR_OPT coo
846: WHERE coo.course_cd = p_new_course_cd AND
839: CURSOR c_coo_new (
840: cp_cal_type IGS_PS_OFR_OPT.cal_type%TYPE,
841: cp_location_cd IGS_PS_OFR_OPT.location_cd%TYPE,
842: cp_attendance_mode IGS_PS_OFR_OPT.attendance_mode%TYPE,
843: cp_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE) IS
844: SELECT 'x'
845: FROM IGS_PS_OFR_OPT coo
846: WHERE coo.course_cd = p_new_course_cd AND
847: coo.version_number = p_new_version_number AND
841: cp_location_cd IGS_PS_OFR_OPT.location_cd%TYPE,
842: cp_attendance_mode IGS_PS_OFR_OPT.attendance_mode%TYPE,
843: cp_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE) IS
844: SELECT 'x'
845: FROM IGS_PS_OFR_OPT coo
846: WHERE coo.course_cd = p_new_course_cd AND
847: coo.version_number = p_new_version_number AND
848: coo.cal_type = cp_cal_type AND
849: coo.location_cd = cp_location_cd AND
852: coo.delete_flag = 'N';
853: v_dummy VARCHAR2(1);
854: BEGIN
855: x_rowid := NULL;
856: IGS_PS_OFR_INST_PKG.INSERT_ROW(
857: X_ROWID => x_rowid,
858: X_COURSE_CD => p_new_course_cd,
859: X_VERSION_NUMBER => p_new_version_number,
860: X_CAL_TYPE => gv_coi_rec.cal_type,
863: X_CI_END_DT => gv_coi_rec.ci_end_dt,
864: X_MIN_ENTRY_ASS_SCORE => gv_coi_rec.min_entry_ass_score,
865: X_GUARANTEED_ENTRY_ASS_SCR => gv_coi_rec.guaranteed_entry_ass_scr,
866: X_MODE => 'R' );
867: -- calling procedure to insert IGS_PS_OFR_PAT records
868: -- associated with each IGS_PS_OFR instance record
869: OPEN gc_cop_rec;
870: LOOP
871: FETCH gc_cop_rec INTO gv_cop_rec;
864: X_MIN_ENTRY_ASS_SCORE => gv_coi_rec.min_entry_ass_score,
865: X_GUARANTEED_ENTRY_ASS_SCR => gv_coi_rec.guaranteed_entry_ass_scr,
866: X_MODE => 'R' );
867: -- calling procedure to insert IGS_PS_OFR_PAT records
868: -- associated with each IGS_PS_OFR instance record
869: OPEN gc_cop_rec;
870: LOOP
871: FETCH gc_cop_rec INTO gv_cop_rec;
872: IF gc_cop_rec%FOUND THEN
1096: cous.us_version_number,
1097: cous.override_title,
1098: cous.only_as_sub_ind,
1099: cous.show_on_official_ntfctn_ind
1100: FROM IGS_PS_OFR_UNIT_SET cous
1101: WHERE cous.course_cd = p_old_course_cd AND
1102: cous.crv_version_number = p_old_version_number AND
1103: cous.cal_type = gv_co_rec.cal_type;
1104: CURSOR c_cousr IS
1107: cousr.sup_us_version_number,
1108: cousr.sub_unit_set_cd,
1109: cousr.sub_us_version_number
1110: FROM IGS_PS_OF_UNT_SET_RL cousr,
1111: IGS_PS_OFR_UNIT_SET cous_sup,
1112: IGS_PS_OFR_UNIT_SET cous_sub
1113: WHERE cousr.course_cd = p_old_course_cd AND
1114: cousr.crv_version_number = p_old_version_number AND
1115: cousr.cal_type = gv_co_rec.cal_type AND
1108: cousr.sub_unit_set_cd,
1109: cousr.sub_us_version_number
1110: FROM IGS_PS_OF_UNT_SET_RL cousr,
1111: IGS_PS_OFR_UNIT_SET cous_sup,
1112: IGS_PS_OFR_UNIT_SET cous_sub
1113: WHERE cousr.course_cd = p_old_course_cd AND
1114: cousr.crv_version_number = p_old_version_number AND
1115: cousr.cal_type = gv_co_rec.cal_type AND
1116: cousr.cal_type = cous_sub.cal_type AND
1123: cousr.course_cd = cous_sup.course_cd AND
1124: cousr.crv_version_number = cous_sup.crv_version_number AND
1125: cousr.cal_type = cous_sup.cal_type AND
1126: EXISTS (SELECT 'X'
1127: FROM IGS_PS_OFR_UNIT_SET cous
1128: WHERE cous.course_cd = p_new_course_cd AND
1129: cous.crv_version_number = p_new_version_number AND
1130: cous.cal_type = cous_sup.cal_type AND
1131: cous.unit_set_cd = cous_sup.unit_set_cd AND
1130: cous.cal_type = cous_sup.cal_type AND
1131: cous.unit_set_cd = cous_sup.unit_set_cd AND
1132: cous.us_version_number = cous_sup.us_version_number) AND
1133: EXISTS (SELECT 'X'
1134: FROM IGS_PS_OFR_UNIT_SET cous
1135: WHERE cous.course_cd = p_new_course_cd AND
1136: cous.crv_version_number = p_new_version_number AND
1137: cous.cal_type = cous_sub.cal_type AND
1138: cous.unit_set_cd = cous_sub.unit_set_cd AND
1141: BEGIN
1142: FOR v_cous_rec IN c_cous LOOP
1143: BEGIN
1144: x_rowid := NULL;
1145: IGS_PS_OFR_UNIT_SET_PKG.INSERT_ROW(
1146: X_ROWID => x_rowid,
1147: X_COURSE_CD => p_new_course_cd,
1148: X_CRV_VERSION_NUMBER => p_new_version_number,
1149: X_CAL_TYPE => v_cous_rec.cal_type,
1196: ELSE
1197: App_Exception.Raise_Exception;
1198: END IF;
1199: END crspl_ins_cous_rec;
1200: -- procedure for inserting new IGS_PS_OFR records
1201: PROCEDURE crsp_ins_co_rec (
1202: p_new_course_cd IGS_PS_VER.course_cd%TYPE,
1203: p_new_version_number IGS_PS_VER.version_number%TYPE) AS
1204:
1205: l_org_id NUMBER(15);
1206: BEGIN
1207: X_ROWID := NULL;
1208: l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
1209: IGS_PS_OFR_PKG.INSERT_ROW(
1210: X_ROWID => x_rowid,
1211: X_COURSE_CD => p_new_course_cd,
1212: X_VERSION_NUMBER => p_new_version_number,
1213: X_CAL_TYPE => gv_co_rec.cal_type,
1234: X_ATTRIBUTE20 => gv_co_rec.attribute20,
1235: X_MODE => 'R' ,
1236: X_ORG_ID => l_org_id
1237: );
1238: -- calling procedure to insert IGS_PS_OFR_UNIT_SET records
1239: -- and its child tables IGS_PS_OF_OPT_UNT_ST and
1240: -- IGS_PS_OF_UNT_SET_RL
1241: crspl_ins_cous_rec;
1242: -- calling procedure to insert IGS_PS_OFR_OPT records
1238: -- calling procedure to insert IGS_PS_OFR_UNIT_SET records
1239: -- and its child tables IGS_PS_OF_OPT_UNT_ST and
1240: -- IGS_PS_OF_UNT_SET_RL
1241: crspl_ins_cous_rec;
1242: -- calling procedure to insert IGS_PS_OFR_OPT records
1243: -- associated with each IGS_PS_OFR record
1244: OPEN gc_coo_rec;
1245: LOOP
1246: FETCH gc_coo_rec INTO gv_coo_rec;
1239: -- and its child tables IGS_PS_OF_OPT_UNT_ST and
1240: -- IGS_PS_OF_UNT_SET_RL
1241: crspl_ins_cous_rec;
1242: -- calling procedure to insert IGS_PS_OFR_OPT records
1243: -- associated with each IGS_PS_OFR record
1244: OPEN gc_coo_rec;
1245: LOOP
1246: FETCH gc_coo_rec INTO gv_coo_rec;
1247: IF gc_coo_rec%FOUND THEN
1250: EXIT;
1251: END IF;
1252: END LOOP;
1253: CLOSE gc_coo_rec;
1254: -- calling procedure to insert IGS_PS_OFR_INST records
1255: -- associated with each IGS_PS_OFR record
1256: OPEN gc_coi_rec;
1257: FETCH gc_coi_rec INTO gv_coi_rec;
1258: IF gc_coi_rec%FOUND THEN
1251: END IF;
1252: END LOOP;
1253: CLOSE gc_coo_rec;
1254: -- calling procedure to insert IGS_PS_OFR_INST records
1255: -- associated with each IGS_PS_OFR record
1256: OPEN gc_coi_rec;
1257: FETCH gc_coi_rec INTO gv_coi_rec;
1258: IF gc_coi_rec%FOUND THEN
1259: -- if found, only the latest one needs to be copied over.
1259: -- if found, only the latest one needs to be copied over.
1260: crsp_ins_coi_rec(p_new_course_cd, p_new_version_number);
1261: END IF;
1262: CLOSE gc_coi_rec;
1263: -- calling procedure to insert IGS_PS_OFR_NOTE records
1264: -- associated with each IGS_PS_OFR record
1265: OPEN gc_con_rec;
1266: LOOP
1267: FETCH gc_con_rec INTO gv_con_rec;
1260: crsp_ins_coi_rec(p_new_course_cd, p_new_version_number);
1261: END IF;
1262: CLOSE gc_coi_rec;
1263: -- calling procedure to insert IGS_PS_OFR_NOTE records
1264: -- associated with each IGS_PS_OFR record
1265: OPEN gc_con_rec;
1266: LOOP
1267: FETCH gc_con_rec INTO gv_con_rec;
1268: IF gc_con_rec%FOUND THEN
1879: -- If the insertion of a IGS_PS_COURSE IGS_GE_NOTE fails, the associated IGS_GE_NOTE is removed.
1880: -- IGS_GE_NOTE: If any tables to be added, be careful to ensure relational integrity
1881: -- is preserved. Please check that all foreign keys are catered for.
1882: -- (For example, IGS_PS_OF_OPT_UNT_ST relies on
1883: -- IGS_PS_OFR_UNIT_SET and IGS_PS_OFR_OPT populated first.
1884: -- Only then can the records be created for the new version provided
1885: -- the parent records exist.)
1886: -- This checks if the specified new IGS_PS_COURSE version exists
1887:
2015: EXIT;
2016: END IF;
2017: END LOOP;
2018: CLOSE gc_cvn_rec;
2019: -- calling procedure to insert IGS_PS_OFR records
2020: OPEN c_co_rec;
2021: LOOP
2022: FETCH c_co_rec INTO gv_co_rec;
2023: IF c_co_rec%FOUND THEN