[Home] [Help]
1: PACKAGE BODY igs_ps_unit_lgcy_pkg AS
2: /* $Header: IGSPS85B.pls 120.15 2006/08/02 11:47:44 sommukhe ship $ */
3:
4: /***********************************************************************************************
5: Created By : Sanjeeb Rakshit, Shirish Tatiko, Saravana Kumar
50: Modified the code to use messages rather than lookup codes mentioned in TD, due to
51: Non Backward compatible changes in igslkups.ldt.
52: ********************************************************************************************** */
53:
54: g_n_user_id igs_ps_unit_ver_all.created_by%TYPE := NVL(fnd_global.user_id,-1); -- Stores the User Id
55: g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1); -- Stores the Login Id
56: TYPE uso_tbl_type IS TABLE OF igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE INDEX BY BINARY_INTEGER;
57: l_tbl_uso uso_tbl_type;
58:
51: Non Backward compatible changes in igslkups.ldt.
52: ********************************************************************************************** */
53:
54: g_n_user_id igs_ps_unit_ver_all.created_by%TYPE := NVL(fnd_global.user_id,-1); -- Stores the User Id
55: g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1); -- Stores the Login Id
56: TYPE uso_tbl_type IS TABLE OF igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE INDEX BY BINARY_INTEGER;
57: l_tbl_uso uso_tbl_type;
58:
59:
75: sarakshi 04-May-2004 Enh#3568858,columns ovrd_wkld_val_flag, workload_val_code has been trimed and also included in the insert statement.
76: sarakshi 10-Nov-2003 Enh#3116171, added logic related to the newly introduced field BILLING_CREDIT_POINTS, modified trim values procedure and insert_row
77: ***********************************************************************************************/
78:
79: CURSOR cur_check (cp_unit_cd igs_ps_unit.unit_cd%TYPE) IS
80: SELECT 'X'
81: FROM igs_ps_unit
82: WHERE unit_cd=cp_unit_cd;
83: l_c_var VARCHAR2(1);
77: ***********************************************************************************************/
78:
79: CURSOR cur_check (cp_unit_cd igs_ps_unit.unit_cd%TYPE) IS
80: SELECT 'X'
81: FROM igs_ps_unit
82: WHERE unit_cd=cp_unit_cd;
83: l_c_var VARCHAR2(1);
84:
85: l_n_coord_person_id igs_ps_unit_ver_all.coord_person_id%TYPE;
81: FROM igs_ps_unit
82: WHERE unit_cd=cp_unit_cd;
83: l_c_var VARCHAR2(1);
84:
85: l_n_coord_person_id igs_ps_unit_ver_all.coord_person_id%TYPE;
86: l_d_owner_ou_start_dt igs_ps_unit_ver_all.owner_ou_start_dt%TYPE;
87: l_n_subtitle_id igs_ps_unit_ver_all.subtitle_id%TYPE;
88: l_n_rpt_fmly_id igs_ps_rpt_fmly.rpt_fmly_id%TYPE;
89: l_n_unit_type_id igs_ps_unit_type_lvl.unit_type_id%TYPE;
82: WHERE unit_cd=cp_unit_cd;
83: l_c_var VARCHAR2(1);
84:
85: l_n_coord_person_id igs_ps_unit_ver_all.coord_person_id%TYPE;
86: l_d_owner_ou_start_dt igs_ps_unit_ver_all.owner_ou_start_dt%TYPE;
87: l_n_subtitle_id igs_ps_unit_ver_all.subtitle_id%TYPE;
88: l_n_rpt_fmly_id igs_ps_rpt_fmly.rpt_fmly_id%TYPE;
89: l_n_unit_type_id igs_ps_unit_type_lvl.unit_type_id%TYPE;
90: l_c_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE;
83: l_c_var VARCHAR2(1);
84:
85: l_n_coord_person_id igs_ps_unit_ver_all.coord_person_id%TYPE;
86: l_d_owner_ou_start_dt igs_ps_unit_ver_all.owner_ou_start_dt%TYPE;
87: l_n_subtitle_id igs_ps_unit_ver_all.subtitle_id%TYPE;
88: l_n_rpt_fmly_id igs_ps_rpt_fmly.rpt_fmly_id%TYPE;
89: l_n_unit_type_id igs_ps_unit_type_lvl.unit_type_id%TYPE;
90: l_c_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE;
91: l_n_seq_num_enrol_load_cal igs_ps_unit_ver_all.sequence_num_enrol_load_cal%TYPE;
85: l_n_coord_person_id igs_ps_unit_ver_all.coord_person_id%TYPE;
86: l_d_owner_ou_start_dt igs_ps_unit_ver_all.owner_ou_start_dt%TYPE;
87: l_n_subtitle_id igs_ps_unit_ver_all.subtitle_id%TYPE;
88: l_n_rpt_fmly_id igs_ps_rpt_fmly.rpt_fmly_id%TYPE;
89: l_n_unit_type_id igs_ps_unit_type_lvl.unit_type_id%TYPE;
90: l_c_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE;
91: l_n_seq_num_enrol_load_cal igs_ps_unit_ver_all.sequence_num_enrol_load_cal%TYPE;
92: l_c_cal_type_offer_load_cal igs_ps_unit_ver_all.cal_type_offer_load_cal%TYPE;
93: l_n_seq_num_offer_load_cal igs_ps_unit_ver_all.sequence_num_offer_load_cal%TYPE;
86: l_d_owner_ou_start_dt igs_ps_unit_ver_all.owner_ou_start_dt%TYPE;
87: l_n_subtitle_id igs_ps_unit_ver_all.subtitle_id%TYPE;
88: l_n_rpt_fmly_id igs_ps_rpt_fmly.rpt_fmly_id%TYPE;
89: l_n_unit_type_id igs_ps_unit_type_lvl.unit_type_id%TYPE;
90: l_c_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE;
91: l_n_seq_num_enrol_load_cal igs_ps_unit_ver_all.sequence_num_enrol_load_cal%TYPE;
92: l_c_cal_type_offer_load_cal igs_ps_unit_ver_all.cal_type_offer_load_cal%TYPE;
93: l_n_seq_num_offer_load_cal igs_ps_unit_ver_all.sequence_num_offer_load_cal%TYPE;
94:
87: l_n_subtitle_id igs_ps_unit_ver_all.subtitle_id%TYPE;
88: l_n_rpt_fmly_id igs_ps_rpt_fmly.rpt_fmly_id%TYPE;
89: l_n_unit_type_id igs_ps_unit_type_lvl.unit_type_id%TYPE;
90: l_c_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE;
91: l_n_seq_num_enrol_load_cal igs_ps_unit_ver_all.sequence_num_enrol_load_cal%TYPE;
92: l_c_cal_type_offer_load_cal igs_ps_unit_ver_all.cal_type_offer_load_cal%TYPE;
93: l_n_seq_num_offer_load_cal igs_ps_unit_ver_all.sequence_num_offer_load_cal%TYPE;
94:
95: PROCEDURE trim_values (p_unit_ver_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ver_rec_type) AS
88: l_n_rpt_fmly_id igs_ps_rpt_fmly.rpt_fmly_id%TYPE;
89: l_n_unit_type_id igs_ps_unit_type_lvl.unit_type_id%TYPE;
90: l_c_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE;
91: l_n_seq_num_enrol_load_cal igs_ps_unit_ver_all.sequence_num_enrol_load_cal%TYPE;
92: l_c_cal_type_offer_load_cal igs_ps_unit_ver_all.cal_type_offer_load_cal%TYPE;
93: l_n_seq_num_offer_load_cal igs_ps_unit_ver_all.sequence_num_offer_load_cal%TYPE;
94:
95: PROCEDURE trim_values (p_unit_ver_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ver_rec_type) AS
96: BEGIN
89: l_n_unit_type_id igs_ps_unit_type_lvl.unit_type_id%TYPE;
90: l_c_cal_type_enrol_load_cal igs_ps_unit_ver_all.cal_type_enrol_load_cal%TYPE;
91: l_n_seq_num_enrol_load_cal igs_ps_unit_ver_all.sequence_num_enrol_load_cal%TYPE;
92: l_c_cal_type_offer_load_cal igs_ps_unit_ver_all.cal_type_offer_load_cal%TYPE;
93: l_n_seq_num_offer_load_cal igs_ps_unit_ver_all.sequence_num_offer_load_cal%TYPE;
94:
95: PROCEDURE trim_values (p_unit_ver_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ver_rec_type) AS
96: BEGIN
97: p_unit_ver_rec.unit_cd := TRIM(p_unit_ver_rec.unit_cd);
307: record_exclusion_flag,ss_display_ind,special_permission_ind
308: smvk 26-Dec-2002 Bug # 2721495. Calling the procedure igs_ps_validate_lgcy_pkg.get_party_id
309: instead of function igs_ge_gen_003.get_person_id to derive the person id.
310: ***********************************************************************************************/
311: CURSOR c_unit_lvl(cp_level_code igs_ps_unit_type_lvl.level_code%TYPE) IS
312: SELECT unit_type_id
313: FROM igs_ps_unit_type_lvl
314: WHERE level_code=cp_level_code;
315:
309: instead of function igs_ge_gen_003.get_person_id to derive the person id.
310: ***********************************************************************************************/
311: CURSOR c_unit_lvl(cp_level_code igs_ps_unit_type_lvl.level_code%TYPE) IS
312: SELECT unit_type_id
313: FROM igs_ps_unit_type_lvl
314: WHERE level_code=cp_level_code;
315:
316: CURSOR c_repeat_code(cp_repeat_code igs_ps_rpt_fmly.repeat_code%TYPE) IS
317: SELECT rpt_fmly_id
544: institutional_financial_aid,same_teaching_period,exclude_from_cp_limit,
545: record_exclusion_flag,ss_display_ind,special_permission_ind,IVR_enrol_ind,
546: ss_enrol_ind,anon_unit_grading_ind,anon_assess_grading_ind,auditable_ind,
547: audit_permission_ind
548: smvk 20-Dec-2002 Removed the IGS_PS_UNIT_VER_PKG.check_constraints call of owner_org_unit_cd.
549: As owner org unit code can have value in mixed case. Bug 2487149.
550: ***********************************************************************************************/
551:
552: CURSOR cur_credit_point_des(cp_credit_point_descriptor igs_lookup_values.lookup_code%TYPE) IS
557: l_var VARCHAR2(1);
558:
559: BEGIN
560: --Check for Pk validations
561: IF igs_ps_unit_ver_pkg.get_pk_for_validation(p_unit_ver_rec.unit_cd,
562: p_unit_ver_rec.version_number) THEN
563: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS','UNIT_VERSION','LEGACY_TOKENS',FALSE);
564: p_unit_ver_rec.status:='W';
565: RETURN;
573: END IF;
574: END IF;
575:
576: --Check UK for validations
577: IF igs_ps_unit_subtitle_pkg.get_uk_for_validation(p_unit_ver_rec.unit_cd,
578: p_unit_ver_rec.version_number,
579: p_unit_ver_rec.subtitle) THEN
580: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS','SUBTITLE','LEGACY_TOKENS',FALSE);
581: p_unit_ver_rec.status:='W';
583: END IF;
584:
585: --Check constraint
586: BEGIN
587: igs_ps_unit_ver_pkg.check_constraints('UNIT_CD',p_unit_ver_rec.unit_cd);
588: EXCEPTION
589: WHEN OTHERS THEN
590: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE','UNIT_CD','LEGACY_TOKENS',TRUE);
591: p_unit_ver_rec.status :='E';
591: p_unit_ver_rec.status :='E';
592: END;
593:
594: BEGIN
595: igs_ps_unit_ver_pkg.check_constraints('ABBREVIATION',p_unit_ver_rec.abbreviation);
596: EXCEPTION
597: WHEN OTHERS THEN
598: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE','ABBREVIATION','LEGACY_TOKENS',TRUE);
599: p_unit_ver_rec.status :='E';
599: p_unit_ver_rec.status :='E';
600: END;
601:
602: BEGIN
603: igs_ps_unit_ver_pkg.check_constraints('ASSESSABLE_IND',p_unit_ver_rec.assessable_ind);
604: EXCEPTION
605: WHEN OTHERS THEN
606: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','ASSESSABLE_IND','LEGACY_TOKENS',TRUE);
607: p_unit_ver_rec.status :='E';
607: p_unit_ver_rec.status :='E';
608: END;
609:
610: BEGIN
611: igs_ps_unit_ver_pkg.check_constraints('AWARD_COURSE_ONLY_IND',p_unit_ver_rec.award_course_only_ind);
612: EXCEPTION
613: WHEN OTHERS THEN
614: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','AWARD_COURSE_ONLY_IND','LEGACY_TOKENS',TRUE);
615: p_unit_ver_rec.status :='E';
615: p_unit_ver_rec.status :='E';
616: END;
617:
618: BEGIN
619: igs_ps_unit_ver_pkg.check_constraints('CREDIT_POINT_DESCRIPTOR',p_unit_ver_rec.credit_point_descriptor);
620: EXCEPTION
621: WHEN OTHERS THEN
622: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE','CREDIT_POINT_DESCRIPTOR','LEGACY_TOKENS',TRUE);
623: p_unit_ver_rec.status :='E';
623: p_unit_ver_rec.status :='E';
624: END;
625:
626: BEGIN
627: igs_ps_unit_ver_pkg.check_constraints('INDUSTRIAL_IND',p_unit_ver_rec.industrial_ind);
628: EXCEPTION
629: WHEN OTHERS THEN
630: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','INDUSTRIAL_IND','LEGACY_TOKENS',TRUE);
631: p_unit_ver_rec.status :='E';
631: p_unit_ver_rec.status :='E';
632: END;
633:
634: BEGIN
635: igs_ps_unit_ver_pkg.check_constraints('POINTS_OVERRIDE_IND',p_unit_ver_rec.points_override_ind);
636: EXCEPTION
637: WHEN OTHERS THEN
638: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','POINTS_OVERRIDE_IND','LEGACY_TOKENS',TRUE);
639: p_unit_ver_rec.status :='E';
639: p_unit_ver_rec.status :='E';
640: END;
641:
642: BEGIN
643: igs_ps_unit_ver_pkg.check_constraints('PRACTICAL_IND',p_unit_ver_rec.practical_ind);
644: EXCEPTION
645: WHEN OTHERS THEN
646: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','PRACTICAL_IND','LEGACY_TOKENS',TRUE);
647: p_unit_ver_rec.status :='E';
647: p_unit_ver_rec.status :='E';
648: END;
649:
650: BEGIN
651: igs_ps_unit_ver_pkg.check_constraints('REPEATABLE_IND',p_unit_ver_rec.repeatable_ind);
652: EXCEPTION
653: WHEN OTHERS THEN
654: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','REPEATABLE_IND','LEGACY_TOKENS',TRUE);
655: p_unit_ver_rec.status :='E';
655: p_unit_ver_rec.status :='E';
656: END;
657:
658: BEGIN
659: igs_ps_unit_ver_pkg.check_constraints('RESEARCH_UNIT_IND',p_unit_ver_rec.research_unit_ind);
660: EXCEPTION
661: WHEN OTHERS THEN
662: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','RESEARCH_UNIT_IND','LEGACY_TOKENS',TRUE);
663: p_unit_ver_rec.status :='E';
663: p_unit_ver_rec.status :='E';
664: END;
665:
666: BEGIN
667: igs_ps_unit_ver_pkg.check_constraints('SUPP_EXAM_PERMITTED_IND',p_unit_ver_rec.supp_exam_permitted_ind);
668: EXCEPTION
669: WHEN OTHERS THEN
670: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','SUPP_EXAM_PERMITTED_IND','LEGACY_TOKENS',TRUE);
671: p_unit_ver_rec.status :='E';
671: p_unit_ver_rec.status :='E';
672: END;
673:
674: BEGIN
675: igs_ps_unit_ver_pkg.check_constraints('TITLE_OVERRIDE_IND',p_unit_ver_rec.title_override_ind);
676: EXCEPTION
677: WHEN OTHERS THEN
678: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','TITLE_OVERRIDE_IND','LEGACY_TOKENS',TRUE);
679: p_unit_ver_rec.status :='E';
681:
682: -----------------------------------Following checks are done as a part of bug#2702240---------------
683:
684: BEGIN
685: igs_ps_unit_ver_pkg.check_constraints('STATE_FINANCIAL_AID',p_unit_ver_rec.state_financial_aid);
686: EXCEPTION
687: WHEN OTHERS THEN
688: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','STATE_FINANCIAL_AID','LEGACY_TOKENS',TRUE);
689: p_unit_ver_rec.status :='E';
689: p_unit_ver_rec.status :='E';
690: END;
691:
692: BEGIN
693: igs_ps_unit_ver_pkg.check_constraints('FEDERAL_FINANCIAL_AID',p_unit_ver_rec.federal_financial_aid);
694: EXCEPTION
695: WHEN OTHERS THEN
696: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','FEDERAL_FINANCIAL_AID','LEGACY_TOKENS',TRUE);
697: p_unit_ver_rec.status :='E';
697: p_unit_ver_rec.status :='E';
698: END;
699:
700: BEGIN
701: igs_ps_unit_ver_pkg.check_constraints('INSTITUTIONAL_FINANCIAL_AID',p_unit_ver_rec.institutional_financial_aid);
702: EXCEPTION
703: WHEN OTHERS THEN
704: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','INSTITUTIONAL_FINANCIAL_AID','LEGACY_TOKENS',TRUE);
705: p_unit_ver_rec.status :='E';
705: p_unit_ver_rec.status :='E';
706: END;
707:
708: BEGIN
709: igs_ps_unit_ver_pkg.check_constraints('SAME_TEACHING_PERIOD',p_unit_ver_rec.same_teaching_period);
710: EXCEPTION
711: WHEN OTHERS THEN
712: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','SAME_TEACHING_PERIOD','LEGACY_TOKENS',TRUE);
713: p_unit_ver_rec.status :='E';
713: p_unit_ver_rec.status :='E';
714: END;
715:
716: BEGIN
717: igs_ps_unit_ver_pkg.check_constraints('EXCLUDE_FROM_MAX_CP_LIMIT',p_unit_ver_rec.exclude_from_max_cp_limit);
718: EXCEPTION
719: WHEN OTHERS THEN
720: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','EXCLUDE_FROM_MAX_CP_LIMIT','LEGACY_TOKENS',TRUE);
721: p_unit_ver_rec.status :='E';
721: p_unit_ver_rec.status :='E';
722: END;
723:
724: BEGIN
725: igs_ps_unit_ver_pkg.check_constraints('RECORD_EXCLUSION_FLAG',p_unit_ver_rec.record_exclusion_flag);
726: EXCEPTION
727: WHEN OTHERS THEN
728: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','RECORD_EXCLUSION_FLAG','LEGACY_TOKENS',TRUE);
729: p_unit_ver_rec.status :='E';
729: p_unit_ver_rec.status :='E';
730: END;
731:
732: BEGIN
733: igs_ps_unit_ver_pkg.check_constraints('SS_DISPLAY_IND',p_unit_ver_rec.ss_display_ind);
734: EXCEPTION
735: WHEN OTHERS THEN
736: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','SS_DISPLAY_IND','LEGACY_TOKENS',TRUE);
737: p_unit_ver_rec.status :='E';
737: p_unit_ver_rec.status :='E';
738: END;
739:
740: BEGIN
741: igs_ps_unit_ver_pkg.check_constraints('SPECIAL_PERMISSION_IND',p_unit_ver_rec.special_permission_ind);
742: EXCEPTION
743: WHEN OTHERS THEN
744: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','SPECIAL_PERMISSION_IND','LEGACY_TOKENS',TRUE);
745: p_unit_ver_rec.status :='E';
773: END IF;
774:
775:
776: BEGIN
777: igs_ps_unit_ver_pkg.check_constraints('ANON_UNIT_GRADING_IND',p_unit_ver_rec.anon_unit_grading_ind);
778: EXCEPTION
779: WHEN OTHERS THEN
780: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','ANON_UNIT_GRADING_IND','LEGACY_TOKENS',TRUE);
781: p_unit_ver_rec.status :='E';
781: p_unit_ver_rec.status :='E';
782: END;
783:
784: BEGIN
785: igs_ps_unit_ver_pkg.check_constraints('ANON_ASSESS_GRADING_IND',p_unit_ver_rec.anon_assess_grading_ind);
786: EXCEPTION
787: WHEN OTHERS THEN
788: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','ANON_ASSESS_GRADING_IND','LEGACY_TOKENS',TRUE);
789: p_unit_ver_rec.status :='E';
789: p_unit_ver_rec.status :='E';
790: END;
791:
792: BEGIN
793: igs_ps_unit_ver_pkg.check_constraints('AUDITABLE_IND',p_unit_ver_rec.auditable_ind);
794: EXCEPTION
795: WHEN OTHERS THEN
796: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','AUDITABLE_IND','LEGACY_TOKENS',TRUE);
797: p_unit_ver_rec.status :='E';
797: p_unit_ver_rec.status :='E';
798: END;
799:
800: BEGIN
801: igs_ps_unit_ver_pkg.check_constraints('AUDIT_PERMISSION_IND',p_unit_ver_rec.audit_permission_ind);
802: EXCEPTION
803: WHEN OTHERS THEN
804: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','AUDIT_PERMISSION_IND','LEGACY_TOKENS',TRUE);
805: p_unit_ver_rec.status :='E';
806: END;
807: -----------------------------------------------------------------------------------------------------
808:
809: BEGIN
810: igs_ps_unit_ver_pkg.check_constraints('UNIT_INT_COURSE_LEVEL_CD',p_unit_ver_rec.unit_int_course_level_cd);
811: EXCEPTION
812: WHEN OTHERS THEN
813: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE','UNIT_INT_COURSE_LEVEL_CD','LEGACY_TOKENS',TRUE);
814: p_unit_ver_rec.status :='E';
814: p_unit_ver_rec.status :='E';
815: END;
816:
817: BEGIN
818: igs_ps_unit_ver_pkg.check_constraints('UNIT_STATUS',p_unit_ver_rec.unit_status);
819: EXCEPTION
820: WHEN OTHERS THEN
821: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE','UNIT_STATUS','LEGACY_TOKENS',TRUE);
822: p_unit_ver_rec.status :='E';
822: p_unit_ver_rec.status :='E';
823: END;
824:
825: BEGIN
826: igs_ps_unit_ver_pkg.check_constraints('UNIT_LEVEL',p_unit_ver_rec.unit_level);
827: EXCEPTION
828: WHEN OTHERS THEN
829: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE','UNIT_LEVEL','LEGACY_TOKENS',TRUE);
830: p_unit_ver_rec.status :='E';
830: p_unit_ver_rec.status :='E';
831: END;
832:
833: BEGIN
834: igs_ps_unit_ver_pkg.check_constraints('ACHIEVABLE_CREDIT_POINTS',p_unit_ver_rec.achievable_credit_points);
835: EXCEPTION
836: WHEN OTHERS THEN
837: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_PTS_RANGE_0_999','ACHIEVABLE_CREDIT_POINTS','LEGACY_TOKENS',TRUE);
838: p_unit_ver_rec.status :='E';
838: p_unit_ver_rec.status :='E';
839: END;
840:
841: BEGIN
842: igs_ps_unit_ver_pkg.check_constraints('POINTS_INCREMENT',p_unit_ver_rec.points_increment);
843: EXCEPTION
844: WHEN OTHERS THEN
845: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_PTS_RANGE_0_999','POINTS_INCREMENT','LEGACY_TOKENS',TRUE);
846: p_unit_ver_rec.status :='E';
846: p_unit_ver_rec.status :='E';
847: END;
848:
849: BEGIN
850: igs_ps_unit_ver_pkg.check_constraints('POINTS_MIN',p_unit_ver_rec.points_min);
851: EXCEPTION
852: WHEN OTHERS THEN
853: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_PTS_RANGE_0_999','POINTS_MIN','LEGACY_TOKENS',TRUE);
854: p_unit_ver_rec.status :='E';
854: p_unit_ver_rec.status :='E';
855: END;
856:
857: BEGIN
858: igs_ps_unit_ver_pkg.check_constraints('POINTS_MAX',p_unit_ver_rec.points_max);
859: EXCEPTION
860: WHEN OTHERS THEN
861: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_PTS_RANGE_0_999','POINTS_MAX','LEGACY_TOKENS',TRUE);
862: p_unit_ver_rec.status :='E';
862: p_unit_ver_rec.status :='E';
863: END;
864:
865: BEGIN
866: igs_ps_unit_ver_pkg.check_constraints('ENROLLED_CREDIT_POINTS',p_unit_ver_rec.enrolled_credit_points);
867: EXCEPTION
868: WHEN OTHERS THEN
869: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_PTS_RANGE_0_999','ENROLLED_CREDIT_POINTS','LEGACY_TOKENS',TRUE);
870: p_unit_ver_rec.status :='E';
870: p_unit_ver_rec.status :='E';
871: END;
872:
873: BEGIN
874: igs_ps_unit_ver_pkg.check_constraints('VERSION_NUMBER',p_unit_ver_rec.version_number);
875: EXCEPTION
876: WHEN OTHERS THEN
877: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VER_NUM_1_999',NULL,NULL,TRUE);
878: p_unit_ver_rec.status :='E';
879: END;
880:
881: IF p_unit_ver_rec.billing_credit_points IS NOT NULL THEN
882: BEGIN
883: igs_ps_unit_ver_pkg.check_constraints ( 'BILLING_CREDIT_POINTS', p_unit_ver_rec.billing_credit_points);
884: EXCEPTION
885: WHEN OTHERS THEN
886: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999', 'BILLING_CREDIT_POINTS', 'LEGACY_TOKENS', TRUE);
887: p_unit_ver_rec.status := 'E';
889: END IF;
890:
891:
892: --Fk validations
893: IF (NOT igs_ps_unit_stat_pkg.get_pk_for_validation(p_unit_ver_rec.unit_status)) THEN
894: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS','UNIT_STATUS','LEGACY_TOKENS',FALSE);
895: p_unit_ver_rec.status:='E';
896: END IF;
897:
901: p_unit_ver_rec.status:='E';
902: END IF;
903: END IF;
904:
905: IF (NOT igs_ps_unit_level_pkg.get_pk_for_validation(p_unit_ver_rec.unit_level)) THEN
906: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS','UNIT_LEVEL','LEGACY_TOKENS',FALSE);
907: p_unit_ver_rec.status:='E';
908: END IF;
909:
907: p_unit_ver_rec.status:='E';
908: END IF;
909:
910: IF p_unit_ver_rec.unit_int_course_level_cd IS NOT NULL THEN
911: IF (NOT igs_ps_unit_int_lvl_pkg.get_pk_for_validation(p_unit_ver_rec.unit_int_course_level_cd)) THEN
912: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS','UNIT_INT_COURSE_LEVEL_CD','LEGACY_TOKENS',FALSE);
913: p_unit_ver_rec.status:='E';
914: END IF;
915: END IF;
937:
938: -- Added as a part of 2702263
939: IF p_unit_ver_rec.claimable_hours IS NOT NULL THEN
940: BEGIN
941: igs_ps_unit_ver_pkg.check_constraints('CLAIMABLE_HOURS',p_unit_ver_rec.claimable_hours);
942: EXCEPTION
943: WHEN OTHERS THEN
944: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_PTS_RANGE_0_999','CLAIMABLE_HOURS','LEGACY_TOKENS',TRUE);
945: p_unit_ver_rec.status :='E';
947: END IF;
948:
949: IF p_unit_ver_rec.lecture_credit_points IS NOT NULL THEN
950: BEGIN
951: igs_ps_unit_ver_pkg.check_constraints('LECTURE_CREDIT_POINTS',p_unit_ver_rec.lecture_credit_points);
952: EXCEPTION
953: WHEN OTHERS THEN
954: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999','LECTURE_CREDIT_POINTS','LEGACY_TOKENS',TRUE);
955: p_unit_ver_rec.status :='E';
957: END IF;
958:
959: IF p_unit_ver_rec.lab_credit_points IS NOT NULL THEN
960: BEGIN
961: igs_ps_unit_ver_pkg.check_constraints('LAB_CREDIT_POINTS',p_unit_ver_rec.lab_credit_points);
962: EXCEPTION
963: WHEN OTHERS THEN
964: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999','LAB_CREDIT_POINTS','LEGACY_TOKENS',TRUE);
965: p_unit_ver_rec.status :='E';
967: END IF;
968:
969: IF p_unit_ver_rec.other_credit_points IS NOT NULL THEN
970: BEGIN
971: igs_ps_unit_ver_pkg.check_constraints('OTHER_CREDIT_POINTS',p_unit_ver_rec.other_credit_points);
972: EXCEPTION
973: WHEN OTHERS THEN
974: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999','OTHER_CREDIT_POINTS','LEGACY_TOKENS',TRUE);
975: p_unit_ver_rec.status :='E';
977: END IF;
978:
979: IF p_unit_ver_rec.clock_hours IS NOT NULL THEN
980: BEGIN
981: igs_ps_unit_ver_pkg.check_constraints('CLOCK_HOURS',p_unit_ver_rec.clock_hours);
982: EXCEPTION
983: WHEN OTHERS THEN
984: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999','CLOCK_HOURS','LEGACY_TOKENS',TRUE);
985: p_unit_ver_rec.status :='E';
987: END IF;
988:
989: IF p_unit_ver_rec.continuing_education_units IS NOT NULL THEN
990: BEGIN
991: igs_ps_unit_ver_pkg.check_constraints('CONTINUING_EDUCATION_UNITS',p_unit_ver_rec.continuing_education_units);
992: EXCEPTION
993: WHEN OTHERS THEN
994: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999','CONTINUING_EDUCATION_UNITS','LEGACY_TOKENS',TRUE);
995: p_unit_ver_rec.status :='E';
997: END IF;
998:
999: IF p_unit_ver_rec.advance_maximum IS NOT NULL THEN
1000: BEGIN
1001: igs_ps_unit_ver_pkg.check_constraints('ADVANCE_MAXIMUM',p_unit_ver_rec.advance_maximum);
1002: EXCEPTION
1003: WHEN OTHERS THEN
1004: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','ADVANCE_MAXIMUM','LEGACY_TOKENS',TRUE);
1005: p_unit_ver_rec.status :='E';
1007: END IF;
1008:
1009: IF p_unit_ver_rec.enrollment_expected IS NOT NULL THEN
1010: BEGIN
1011: igs_ps_unit_ver_pkg.check_constraints('ENROLLMENT_EXPECTED',p_unit_ver_rec.enrollment_expected);
1012: EXCEPTION
1013: WHEN OTHERS THEN
1014: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','ENROLLMENT_EXPECTED','LEGACY_TOKENS',TRUE);
1015: p_unit_ver_rec.status :='E';
1017: END IF;
1018:
1019: IF p_unit_ver_rec.enrollment_minimum IS NOT NULL THEN
1020: BEGIN
1021: igs_ps_unit_ver_pkg.check_constraints('ENROLLMENT_MINIMUM',p_unit_ver_rec.enrollment_minimum);
1022: EXCEPTION
1023: WHEN OTHERS THEN
1024: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','ENROLLMENT_MINIMUM','LEGACY_TOKENS',TRUE);
1025: p_unit_ver_rec.status :='E';
1027: END IF;
1028:
1029: IF p_unit_ver_rec.enrollment_maximum IS NOT NULL THEN
1030: BEGIN
1031: igs_ps_unit_ver_pkg.check_constraints('ENROLLMENT_MAXIMUM',p_unit_ver_rec.enrollment_maximum);
1032: EXCEPTION
1033: WHEN OTHERS THEN
1034: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','ENROLLMENT_MAXIMUM','LEGACY_TOKENS',TRUE);
1035: p_unit_ver_rec.status :='E';
1037: END IF;
1038:
1039: IF p_unit_ver_rec.override_enrollment_max IS NOT NULL THEN
1040: BEGIN
1041: igs_ps_unit_ver_pkg.check_constraints('OVERRIDE_ENROLLMENT_MAX',p_unit_ver_rec.override_enrollment_max);
1042: EXCEPTION
1043: WHEN OTHERS THEN
1044: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','OVERRIDE_ENROLLMENT_MAX','LEGACY_TOKENS',TRUE);
1045: p_unit_ver_rec.status :='E';
1047: END IF;
1048:
1049: IF p_unit_ver_rec.max_auditors_allowed IS NOT NULL THEN
1050: BEGIN
1051: igs_ps_unit_ver_pkg.check_constraints('MAX_AUDITORS_ALLOWED',p_unit_ver_rec.max_auditors_allowed);
1052: EXCEPTION
1053: WHEN OTHERS THEN
1054: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_1_999999','MAX_AUDITORS_ALLOWED','LEGACY_TOKENS',TRUE);
1055: p_unit_ver_rec.status :='E';
1057: END IF;
1058:
1059: IF p_unit_ver_rec.work_load_cp_lecture IS NOT NULL THEN
1060: BEGIN
1061: igs_ps_unit_ver_pkg.check_constraints('WORK_LOAD_CP_LECTURE',p_unit_ver_rec.work_load_cp_lecture);
1062: EXCEPTION
1063: WHEN OTHERS THEN
1064: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D99','WORK_LOAD_CP_LECTURE','LEGACY_TOKENS',TRUE);
1065: p_unit_ver_rec.status :='E';
1068:
1069:
1070: IF p_unit_ver_rec.work_load_cp_lab IS NOT NULL THEN
1071: BEGIN
1072: igs_ps_unit_ver_pkg.check_constraints('WORK_LOAD_CP_LAB',p_unit_ver_rec.work_load_cp_lab);
1073: EXCEPTION
1074: WHEN OTHERS THEN
1075: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D99','WORK_LOAD_CP_LAB','LEGACY_TOKENS',TRUE);
1076: p_unit_ver_rec.status :='E';
1078: END IF;
1079:
1080: IF p_unit_ver_rec.max_repeat_credit_points IS NOT NULL THEN
1081: BEGIN
1082: igs_ps_unit_ver_pkg.check_constraints('MAX_REPEAT_CREDIT_POINTS',p_unit_ver_rec.max_repeat_credit_points);
1083: EXCEPTION
1084: WHEN OTHERS THEN
1085: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999','MAX_REPEAT_CREDIT_POINTS','LEGACY_TOKENS',TRUE);
1086: p_unit_ver_rec.status :='E';
1088: END IF;
1089:
1090: IF p_unit_ver_rec.same_teach_period_repeats_cp IS NOT NULL THEN
1091: BEGIN
1092: igs_ps_unit_ver_pkg.check_constraints('SAME_TEACH_PERIOD_REPEATS_CP',p_unit_ver_rec.same_teach_period_repeats_cp);
1093: EXCEPTION
1094: WHEN OTHERS THEN
1095: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999','SAME_TEACH_PERIOD_REPEATS_CP','LEGACY_TOKENS',TRUE);
1096: p_unit_ver_rec.status :='E';
1098: END IF;
1099:
1100: IF p_unit_ver_rec.work_load_other IS NOT NULL THEN
1101: BEGIN
1102: igs_ps_unit_ver_pkg.check_constraints('WORK_LOAD_OTHER',p_unit_ver_rec.work_load_other);
1103: EXCEPTION
1104: WHEN OTHERS THEN
1105: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D99','WORK_LOAD_OTHER','LEGACY_TOKENS',TRUE);
1106: p_unit_ver_rec.status :='E';
1108: END IF;
1109:
1110: IF p_unit_ver_rec.contact_hrs_lecture IS NOT NULL THEN
1111: BEGIN
1112: igs_ps_unit_ver_pkg.check_constraints('CONTACT_HRS_LECTURE',p_unit_ver_rec.contact_hrs_lecture);
1113: EXCEPTION
1114: WHEN OTHERS THEN
1115: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D99','CONTACT_HRS_LECTURE','LEGACY_TOKENS',TRUE);
1116: p_unit_ver_rec.status :='E';
1118: END IF;
1119:
1120: IF p_unit_ver_rec.contact_hrs_lab IS NOT NULL THEN
1121: BEGIN
1122: igs_ps_unit_ver_pkg.check_constraints('CONTACT_HRS_LAB',p_unit_ver_rec.contact_hrs_lab);
1123: EXCEPTION
1124: WHEN OTHERS THEN
1125: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D99','CONTACT_HRS_LAB','LEGACY_TOKENS',TRUE);
1126: p_unit_ver_rec.status :='E';
1128: END IF;
1129:
1130: IF p_unit_ver_rec.contact_hrs_other IS NOT NULL THEN
1131: BEGIN
1132: igs_ps_unit_ver_pkg.check_constraints('CONTACT_HRS_OTHER',p_unit_ver_rec.contact_hrs_other);
1133: EXCEPTION
1134: WHEN OTHERS THEN
1135: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D99','CONTACT_HRS_OTHER','LEGACY_TOKENS',TRUE);
1136: p_unit_ver_rec.status :='E';
1138: END IF;
1139:
1140: IF p_unit_ver_rec.non_schd_required_hrs IS NOT NULL THEN
1141: BEGIN
1142: igs_ps_unit_ver_pkg.check_constraints('NON_SCHD_REQUIRED_HRS',p_unit_ver_rec.non_schd_required_hrs);
1143: EXCEPTION
1144: WHEN OTHERS THEN
1145: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_PTS_RANGE_0_999','NON_SCHD_REQUIRED_HRS','LEGACY_TOKENS',TRUE);
1146: p_unit_ver_rec.status :='E';
1148: END IF;
1149:
1150: IF p_unit_ver_rec.max_repeats_for_credit IS NOT NULL THEN
1151: BEGIN
1152: igs_ps_unit_ver_pkg.check_constraints('MAX_REPEATS_FOR_CREDIT',p_unit_ver_rec.max_repeats_for_credit);
1153: EXCEPTION
1154: WHEN OTHERS THEN
1155: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999','MAX_REPEATS_FOR_CREDIT','LEGACY_TOKENS',TRUE);
1156: p_unit_ver_rec.status :='E';
1158: END IF;
1159:
1160: IF p_unit_ver_rec.max_repeats_for_funding IS NOT NULL THEN
1161: BEGIN
1162: igs_ps_unit_ver_pkg.check_constraints('MAX_REPEATS_FOR_FUNDING',p_unit_ver_rec.max_repeats_for_funding);
1163: EXCEPTION
1164: WHEN OTHERS THEN
1165: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999','MAX_REPEATS_FOR_FUNDING','LEGACY_TOKENS',TRUE);
1166: p_unit_ver_rec.status :='E';
1168: END IF;
1169:
1170: IF p_unit_ver_rec.same_teach_period_repeats IS NOT NULL THEN
1171: BEGIN
1172: igs_ps_unit_ver_pkg.check_constraints('SAME_TEACH_PERIOD_REPEATS',p_unit_ver_rec.same_teach_period_repeats);
1173: EXCEPTION
1174: WHEN OTHERS THEN
1175: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999','SAME_TEACH_PERIOD_REPEATS','LEGACY_TOKENS',TRUE);
1176: p_unit_ver_rec.status :='E';
1178: END IF;
1179:
1180:
1181: BEGIN
1182: igs_ps_unit_ver_pkg.check_constraints('OVRD_WKLD_VAL_FLAG',p_unit_ver_rec.ovrd_wkld_val_flag);
1183: EXCEPTION
1184: WHEN OTHERS THEN
1185: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','OVRD_WKLD_VAL_FLAG','LEGACY_TOKENS',TRUE);
1186: p_unit_ver_rec.status :='E';
1194: END IF;
1195:
1196: IF p_unit_ver_rec.billing_hrs IS NOT NULL THEN
1197: BEGIN
1198: igs_ps_unit_ver_pkg.check_constraints('BILLING_HRS',p_unit_ver_rec.billing_hrs);
1199: EXCEPTION
1200: WHEN OTHERS THEN
1201: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999D999','BILLING_HRS','LEGACY_TOKENS',TRUE);
1202: p_unit_ver_rec.status :='E';
1208:
1209: BEGIN
1210:
1211: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1212: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.start_logging_for','Unit Versions');
1213: END IF;
1214:
1215: p_rec_status :='S';
1216: p_unit_ver_rec.msg_from := FND_MSG_PUB.COUNT_MSG;
1217: trim_values(p_unit_ver_rec);
1218: validate_parameters(p_unit_ver_rec);
1219:
1220: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1221: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.status_after_validate_parameters',
1222: 'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number||' '||
1223: 'Status:'||p_unit_ver_rec.status);
1224: END IF;
1225:
1226: IF p_unit_ver_rec.status = 'S' THEN
1227: validate_derivations(p_unit_ver_rec);
1228:
1229: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1230: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.status_after_validate_derivations',
1231: 'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number||' '||
1232: 'Status:'||p_unit_ver_rec.status);
1233: END IF;
1234:
1237: IF p_unit_ver_rec.status = 'S' THEN
1238: validate_db_cons(p_unit_ver_rec);
1239:
1240: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1241: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.status_after_validate_db_cons',
1242: 'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number||' '||
1243: 'Status:'||p_unit_ver_rec.status);
1244: END IF;
1245:
1248: IF p_unit_ver_rec.status = 'S' THEN
1249: igs_ps_validate_lgcy_pkg.unit_version(p_unit_ver_rec,l_n_coord_person_id);
1250:
1251: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1252: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.status_after_Business_validation',
1253: 'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number||' '||
1254: 'Status:'||p_unit_ver_rec.status);
1255: END IF;
1256:
1257: END IF;
1258:
1259: IF p_unit_ver_rec.status ='S' THEN
1260:
1261: --If for this unit entry is not there in igs_ps_unit then insert it
1262: OPEN cur_check(p_unit_ver_rec.unit_cd);
1263: FETCH cur_check INTO l_c_var;
1264: IF cur_check%NOTFOUND THEN
1265: INSERT INTO igs_ps_unit
1261: --If for this unit entry is not there in igs_ps_unit then insert it
1262: OPEN cur_check(p_unit_ver_rec.unit_cd);
1263: FETCH cur_check INTO l_c_var;
1264: IF cur_check%NOTFOUND THEN
1265: INSERT INTO igs_ps_unit
1266: (
1267: unit_cd,
1268: creation_date,
1269: created_by,
1281: g_n_login_id
1282: );
1283:
1284: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1285: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.Record_created_igs_ps_unit',
1286: 'Unit code:'||p_unit_ver_rec.unit_cd);
1287: END IF;
1288: END IF;
1289:
1288: END IF;
1289:
1290: CLOSE cur_check;
1291:
1292: --If subtitle is not null then insert a record into igs_ps_unit_subtitle
1293: IF p_unit_ver_rec.subtitle IS NOT NULL THEN
1294: INSERT INTO igs_ps_unit_subtitle
1295: (
1296: subtitle_id,
1290: CLOSE cur_check;
1291:
1292: --If subtitle is not null then insert a record into igs_ps_unit_subtitle
1293: IF p_unit_ver_rec.subtitle IS NOT NULL THEN
1294: INSERT INTO igs_ps_unit_subtitle
1295: (
1296: subtitle_id,
1297: unit_cd,
1298: version_number,
1306: last_update_login
1307: )
1308: VALUES
1309: (
1310: igs_ps_unit_subtitle_s.NEXTVAL,
1311: p_unit_ver_rec.unit_cd,
1312: p_unit_ver_rec.version_number,
1313: p_unit_ver_rec.subtitle,
1314: p_unit_ver_rec.subtitle_approved_ind,
1320: g_n_login_id
1321: ) RETURNING subtitle_id INTO l_n_subtitle_id;
1322:
1323: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1324: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.rec_inserted_igs_ps_unit_subtitle',
1325: 'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number||' '||
1326: 'Subtitle_id:'||l_n_subtitle_id||' '||'Status:'||p_unit_ver_rec.status);
1327: END IF;
1328:
1356: g_n_login_id
1357: );
1358:
1359: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1360: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.rec_inserted_igs_ps_unt_crclm_all',
1361: 'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number||' '||
1362: 'curriculum_id:'||p_unit_ver_rec.curriculum_id||' '||'Status:'||p_unit_ver_rec.status);
1363: END IF;
1364: END IF;
1362: 'curriculum_id:'||p_unit_ver_rec.curriculum_id||' '||'Status:'||p_unit_ver_rec.status);
1363: END IF;
1364: END IF;
1365:
1366: --Enter the unit version record in igs_ps_unit_ver_all
1367: INSERT INTO igs_ps_unit_ver_all
1368: (
1369: unit_cd,
1370: version_number,
1363: END IF;
1364: END IF;
1365:
1366: --Enter the unit version record in igs_ps_unit_ver_all
1367: INSERT INTO igs_ps_unit_ver_all
1368: (
1369: unit_cd,
1370: version_number,
1371: start_dt,
1591: g_n_login_id
1592: );
1593:
1594: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1595: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.Record_Inserted',
1596: 'Unit code:'||p_unit_ver_rec.unit_cd||' '||'Version number:'||p_unit_ver_rec.version_number);
1597: END IF;
1598:
1599: p_unit_ver_rec.msg_from := NULL;
1604: END IF;
1605: p_rec_status:= p_unit_ver_rec.status;
1606:
1607: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1608: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_version.after_import_status',p_rec_status);
1609: END IF;
1610:
1611: END create_unit_version;
1612:
1626: smvk 20-Dec-2002 Removed the IGS_PS_TCH_RESP_PKG.check_constraints for org_unit_cd,
1627: as the org unit code can have value in mixed case. Bug # 2487149.
1628: ********************************************************************************************** */
1629:
1630: l_d_ou_start_dt igs_ps_unit_ver_all.owner_ou_start_dt%TYPE;
1631: l_c_message_name VARCHAR2 (30);
1632: CURSOR c_org(cp_org_unit_cd igs_ps_tch_resp.org_unit_cd%TYPE) IS
1633: SELECT 'X'
1634: FROM igs_or_inst_org_base_v a, igs_or_status b
1715: l_d_ou_start_dt ) THEN
1716: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'ORG_UNIT_CD', 'LEGACY_TOKENS', FALSE);
1717: p_teach_resp_rec.status := 'E';
1718: END IF;
1719: IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
1720: p_teach_resp_rec.unit_cd,
1721: p_teach_resp_rec.version_number ) THEN
1722: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_VERSION', 'LEGACY_TOKENS', FALSE);
1723: p_teach_resp_rec.status := 'E';
1727:
1728: /* Main Teaching Responsibility Sub Process */
1729: BEGIN
1730: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1731: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.start_logging_for','Teaching Responsibility');
1732: END IF;
1733: p_c_rec_status := 'S';
1734:
1735: FOR I in 1..p_tab_teach_resp.LAST LOOP
1739: trim_values(p_tab_teach_resp(I) );
1740: validate_parameters ( p_tab_teach_resp(I) );
1741:
1742: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1743: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.status_after_validate_parameters',
1744: 'Unit code:'||p_tab_teach_resp(I).unit_cd||' '||'Version number:'||p_tab_teach_resp(I).version_number||' '||
1745: 'org_unit_cd:'||p_tab_teach_resp(I).org_unit_cd||' '||'Status:'||p_tab_teach_resp(I).status);
1746: END IF;
1747:
1748: IF p_tab_teach_resp(I).status = 'S' THEN
1749: validate_derivations ( p_tab_teach_resp(I) );
1750:
1751: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1752: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.status_after_validate_derivations',
1753: 'Unit code:'||p_tab_teach_resp(I).unit_cd||' '||'Version number:'||p_tab_teach_resp(I).version_number||' '||
1754: 'org_unit_cd:'||p_tab_teach_resp(I).org_unit_cd||' '||'ou_start_dt:'||l_d_ou_start_dt||' '||
1755: 'Status:'||p_tab_teach_resp(I).status);
1756: END IF;
1760: IF p_tab_teach_resp(I).status = 'S' THEN
1761: validate_db_cons ( p_tab_teach_resp(I) );
1762:
1763: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1764: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.status_after_validate_db_cons',
1765: 'Unit code:'||p_tab_teach_resp(I).unit_cd||' '||'Version number:'||p_tab_teach_resp(I).version_number||' '||
1766: 'org_unit_cd:'||p_tab_teach_resp(I).org_unit_cd||' '||'ou_start_dt:'||l_d_ou_start_dt||' '||
1767: 'Status:'||p_tab_teach_resp(I).status);
1768: END IF;
1793: p_tab_teach_resp(I).status := 'W' ;
1794: END IF;
1795:
1796: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1797: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.status_after_Business_validation',
1798: 'Unit code:'||p_tab_teach_resp(I).unit_cd||' '||'Version number:'||p_tab_teach_resp(I).version_number||' '||
1799: 'org_unit_cd:'||p_tab_teach_resp(I).org_unit_cd||' '||'ou_start_dt:'||l_d_ou_start_dt||' '||
1800: 'Status:'||p_tab_teach_resp(I).status);
1801: END IF;
1829: g_n_login_id
1830: );
1831:
1832: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1833: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.Record_Inserted',
1834: 'Unit code:'||p_tab_teach_resp(I).unit_cd||' '||'Version number:'||p_tab_teach_resp(I).version_number||' '||
1835: 'org_unit_cd:'||p_tab_teach_resp(I).org_unit_cd||' '||'ou_start_dt:'||l_d_ou_start_dt);
1836: END IF;
1837:
1855: END IF;
1856:
1857: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1858: fnd_log.string( fnd_log.level_statement,
1859: 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.status_after_Post_insert_validation','Status:'||p_c_rec_status);
1860: END IF;
1861:
1862: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1863: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.after_import_status',p_c_rec_status);
1859: 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.status_after_Post_insert_validation','Status:'||p_c_rec_status);
1860: END IF;
1861:
1862: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1863: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_teach_resp.after_import_status',p_c_rec_status);
1864: END IF;
1865:
1866: END create_teach_resp;
1867:
1918: PROCEDURE validate_db_cons ( p_unit_dscp_rec IN OUT NOCOPY igs_ps_generic_pub.unit_dscp_rec_type ) AS
1919: BEGIN
1920:
1921: /* Validate PK Constraints */
1922: IF igs_ps_unit_dscp_pkg.get_pk_for_validation (
1923: x_unit_cd => p_unit_dscp_rec.unit_cd,
1924: x_version_number => p_unit_dscp_rec.version_number,
1925: x_discipline_group_cd => p_unit_dscp_rec.discipline_group_cd ) THEN
1926: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'UNIT_DISCIPLINE', 'LEGACY_TOKENS', FALSE);
1929: END IF;
1930:
1931: /* Validate Check Constraints */
1932: BEGIN
1933: igs_ps_unit_dscp_pkg.check_constraints ( 'UNIT_CD', p_unit_dscp_rec.unit_cd );
1934: EXCEPTION
1935: WHEN OTHERS THEN
1936: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'UNIT_CD', 'LEGACY_TOKENS', TRUE);
1937: p_unit_dscp_rec.status := 'E';
1937: p_unit_dscp_rec.status := 'E';
1938: END;
1939:
1940: BEGIN
1941: igs_ps_unit_dscp_pkg.check_constraints ( 'DISCIPLINE_GROUP_CD', p_unit_dscp_rec.discipline_group_cd );
1942: EXCEPTION
1943: WHEN OTHERS THEN
1944: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'DISCIPLINE_GROUP_CD', 'LEGACY_TOKENS', TRUE);
1945: p_unit_dscp_rec.status := 'E';
1945: p_unit_dscp_rec.status := 'E';
1946: END;
1947:
1948: BEGIN
1949: igs_ps_unit_dscp_pkg.check_constraints ( 'PERCENTAGE', p_unit_dscp_rec.percentage );
1950: EXCEPTION
1951: WHEN OTHERS THEN
1952: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_PER_0_100', null, null, TRUE);
1953: p_unit_dscp_rec.status := 'E';
1958: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'DISCIPLINE_GROUP_CD', 'LEGACY_TOKENS', FALSE);
1959: p_unit_dscp_rec.status := 'E';
1960: END IF;
1961:
1962: IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (p_unit_dscp_rec.unit_cd, p_unit_dscp_rec.version_number) THEN
1963: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_VERSION', 'LEGACY_TOKENS', FALSE);
1964: p_unit_dscp_rec.status := 'E';
1965: END IF;
1966:
1969: /* Main Unit Discipline Sub Process */
1970: BEGIN
1971:
1972: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1973: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.start_logging_for','Unit Discipline');
1974: END IF;
1975:
1976: p_c_rec_status := 'S';
1977: FOR I in 1..p_tab_unit_dscp.LAST LOOP
1981: trim_values(p_tab_unit_dscp(I) );
1982: validate_parameters ( p_tab_unit_dscp(I) );
1983:
1984: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1985: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.status_after_validate_parameters',
1986: 'Unit code:'||p_tab_unit_dscp(I).unit_cd||' '||'Version number:'||p_tab_unit_dscp(I).version_number||' '||
1987: 'discipline_group_cd:'||p_tab_unit_dscp(I).discipline_group_cd||' '||'Status:'||p_tab_unit_dscp(I).status);
1988: END IF;
1989:
1990: IF p_tab_unit_dscp(I).status = 'S' THEN
1991: validate_db_cons ( p_tab_unit_dscp(I) );
1992:
1993: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1994: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.status_after_validate_db_cons',
1995: 'Unit code:'||p_tab_unit_dscp(I).unit_cd||' '||'Version number:'||p_tab_unit_dscp(I).version_number||' '||
1996: 'discipline_group_cd:'||p_tab_unit_dscp(I).discipline_group_cd||' '||'Status:'||p_tab_unit_dscp(I).status);
1997: END IF;
1998:
2008: p_tab_unit_dscp(I).status := 'W';
2009: END IF;
2010:
2011: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2012: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.status_after_Business_validation',
2013: 'Unit code:'||p_tab_unit_dscp(I).unit_cd||' '||'Version number:'||p_tab_unit_dscp(I).version_number||' '||
2014: 'discipline_group_cd:'||p_tab_unit_dscp(I).discipline_group_cd||' '||'Status:'||p_tab_unit_dscp(I).status);
2015: END IF;
2016:
2017: END IF;
2018:
2019: IF p_tab_unit_dscp(I).status = 'S' THEN
2020: /* Insert record */
2021: INSERT INTO igs_ps_unit_dscp
2022: (unit_cd,
2023: version_number,
2024: discipline_group_cd,
2025: percentage,
2040: SYSDATE,
2041: g_n_login_id
2042: );
2043: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2044: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.Record_Inserted',
2045: 'Unit code:'||p_tab_unit_dscp(I).unit_cd||' '||'Version number:'||p_tab_unit_dscp(I).version_number||' '||
2046: 'discipline_group_cd:'||p_tab_unit_dscp(I).discipline_group_cd);
2047: END IF;
2048:
2064: p_c_rec_status := 'E';
2065: END IF;
2066:
2067: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2068: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.Status_after_Post_insert_check',
2069: 'Status:'||p_c_rec_status);
2070: END IF;
2071:
2072: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2069: 'Status:'||p_c_rec_status);
2070: END IF;
2071:
2072: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2073: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_discip.after_import_status',p_c_rec_status);
2074: END IF;
2075:
2076: END create_unit_discip;
2077:
2130: PROCEDURE validate_db_cons ( p_grd_sch_rec IN OUT NOCOPY igs_ps_generic_pub.unit_gs_rec_type ) AS
2131: BEGIN
2132:
2133: /* Unique Key Validation */
2134: IF igs_ps_unit_grd_schm_pkg.get_uk_for_validation (
2135: x_unit_version_number => p_grd_sch_rec.version_number,
2136: x_grading_schema_code => p_grd_sch_rec.grading_schema_code,
2137: x_grd_schm_version_number => p_grd_sch_rec.grd_schm_version_number,
2138: x_unit_code => p_grd_sch_rec.unit_cd ) THEN
2142: END IF;
2143:
2144: /* Validate Check Constraints */
2145: BEGIN
2146: igs_ps_unit_grd_schm_pkg.check_constraints ( 'DEFAULT_FLAG', p_grd_sch_rec.default_flag );
2147: EXCEPTION
2148: WHEN OTHERS THEN
2149: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'DEFAULT_FLAG', 'LEGACY_TOKENS', TRUE);
2150: p_grd_sch_rec.status := 'E';
2155: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_GRADING_SCHM', 'LEGACY_TOKENS', FALSE);
2156: p_grd_sch_rec.status := 'E';
2157: END IF;
2158:
2159: IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation ( p_grd_sch_rec.unit_cd, p_grd_sch_rec.version_number ) THEN
2160: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_VERSION', 'LEGACY_TOKENS', FALSE);
2161: p_grd_sch_rec.status := 'E';
2162: END IF;
2163: END validate_db_cons;
2165: /* Main Grading Schema Sub Process */
2166: BEGIN
2167:
2168: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2169: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_grd_sch.start_logging_for','Unit Grading Schema');
2170: END IF;
2171:
2172: p_c_rec_status := 'S';
2173: FOR I in 1..p_tab_grd_sch.LAST LOOP
2177: trim_values(p_tab_grd_sch(I));
2178: validate_parameters ( p_tab_grd_sch(I) );
2179:
2180: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2181: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_grd_sch.status_after_validate_parameters',
2182: 'Unit code:'||p_tab_grd_sch(I).unit_cd||' '||'Version number:'||p_tab_grd_sch(I).version_number||' '||
2183: 'grading_schema_code:'||p_tab_grd_sch(I).grading_schema_code||' '||'grd_schm_version_number:'||
2184: p_tab_grd_sch(I).grd_schm_version_number||' '||'Status:'||p_tab_grd_sch(I).status);
2185: END IF;
2187: IF p_tab_grd_sch(I).status = 'S' THEN
2188: validate_db_cons ( p_tab_grd_sch(I) );
2189:
2190: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2191: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_grd_sch.status_after_validate_db_cons',
2192: 'Unit code:'||p_tab_grd_sch(I).unit_cd||' '||'Version number:'||p_tab_grd_sch(I).version_number||' '||
2193: 'grading_schema_code:'||p_tab_grd_sch(I).grading_schema_code||' '||'grd_schm_version_number:'||
2194: p_tab_grd_sch(I).grd_schm_version_number||' '||'Status:'||p_tab_grd_sch(I).status);
2195: END IF;
2201: IF p_tab_grd_sch(I).status = 'S' THEN
2202: igs_ps_validate_lgcy_pkg.validate_unit_grd_sch ( p_tab_grd_sch(I) );
2203:
2204: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2205: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_grd_sch.status_after_Business_Validation',
2206: 'Unit code:'||p_tab_grd_sch(I).unit_cd||' '||'Version number:'||p_tab_grd_sch(I).version_number||' '||
2207: 'grading_schema_code:'||p_tab_grd_sch(I).grading_schema_code||' '||'grd_schm_version_number:'||
2208: p_tab_grd_sch(I).grd_schm_version_number||' '||'Status:'||p_tab_grd_sch(I).status);
2209: END IF;
2211: END IF;
2212:
2213: IF p_tab_grd_sch(I).status = 'S' THEN
2214: /* Insert Record */
2215: INSERT INTO igs_ps_unit_grd_schm
2216: (unit_grading_schema_id,
2217: unit_code,
2218: unit_version_number,
2219: grading_schema_code,
2225: last_update_date,
2226: last_update_login
2227: )
2228: VALUES
2229: (igs_ps_unit_grd_schm_s.NEXTVAL,
2230: p_tab_grd_sch(I).unit_cd,
2231: p_tab_grd_sch(I).version_number,
2232: p_tab_grd_sch(I).grading_schema_code,
2233: p_tab_grd_sch(I).grd_schm_version_number,
2239: g_n_login_id
2240: );
2241:
2242: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2243: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_grd_sch.record_inserted',
2244: 'Unit code:'||p_tab_grd_sch(I).unit_cd||' '||'Version number:'||p_tab_grd_sch(I).version_number||' '||
2245: 'grading_schema_code:'||p_tab_grd_sch(I).grading_schema_code||' '||'grd_schm_version_number:'||
2246: p_tab_grd_sch(I).grd_schm_version_number);
2247: END IF;
2264: p_c_rec_status := 'E';
2265: END IF;
2266:
2267: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2268: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_grd_sch.after_import_status',p_c_rec_status);
2269: END IF;
2270:
2271:
2272: END create_unit_grd_sch;
2287: Change History (in reverse chronological order)
2288: Who When What
2289: ********************************************************************************************* */
2290:
2291: CURSOR c_unit_status (cp_c_unit_status IN igs_ps_unit_stat.unit_status%TYPE) IS
2292: SELECT s_unit_status
2293: FROM igs_ps_unit_stat
2294: WHERE unit_status = cp_c_unit_status;
2295:
2289: ********************************************************************************************* */
2290:
2291: CURSOR c_unit_status (cp_c_unit_status IN igs_ps_unit_stat.unit_status%TYPE) IS
2292: SELECT s_unit_status
2293: FROM igs_ps_unit_stat
2294: WHERE unit_status = cp_c_unit_status;
2295:
2296: rec_unit_status c_unit_status%ROWTYPE;
2297: l_c_message VARCHAR2(30);
2298:
2299: BEGIN
2300:
2301: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2302: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.validate_unit_dtls.start_logging_for','Unit Details Validations');
2303: END IF;
2304:
2305: OPEN c_unit_status (p_unit_ver_rec.unit_status);
2306: FETCH c_unit_status INTO rec_unit_status;
2335: END IF;
2336:
2337: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2338: fnd_log.string( fnd_log.level_procedure,
2339: 'igs.plsql.igs_ps_unit_lgcy_pkg.validate_unit_dtls.record_status_after_validation_of_unit_details',
2340: p_unit_ver_rec.status);
2341: END IF;
2342:
2343:
2353: l_conc_flag BOOLEAN;
2354: l_request_id NUMBER;
2355: l_message_name VARCHAR2(30);
2356:
2357: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2358: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2359: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2354: l_request_id NUMBER;
2355: l_message_name VARCHAR2(30);
2356:
2357: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2358: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2359: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2357: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2358: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2359: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2363: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2364: l_n_sup_uoo_id igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE;
2365: l_c_relation_type igs_ps_unit_ofr_opt_all.relation_type%TYPE;
2359: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2363: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2364: l_n_sup_uoo_id igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE;
2365: l_c_relation_type igs_ps_unit_ofr_opt_all.relation_type%TYPE;
2366: l_n_subtitle_id igs_ps_unit_subtitle.subtitle_id%TYPE;
2367: l_b_uop_deleted BOOLEAN;
2360: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2363: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2364: l_n_sup_uoo_id igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE;
2365: l_c_relation_type igs_ps_unit_ofr_opt_all.relation_type%TYPE;
2366: l_n_subtitle_id igs_ps_unit_subtitle.subtitle_id%TYPE;
2367: l_b_uop_deleted BOOLEAN;
2368:
2361: l_c_org_unit_cd igs_ps_unit_ver_all.owner_org_unit_cd%TYPE;
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2363: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2364: l_n_sup_uoo_id igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE;
2365: l_c_relation_type igs_ps_unit_ofr_opt_all.relation_type%TYPE;
2366: l_n_subtitle_id igs_ps_unit_subtitle.subtitle_id%TYPE;
2367: l_b_uop_deleted BOOLEAN;
2368:
2369:
2362: l_n_unit_contact_id igs_pe_person_base_v.person_id%TYPE;
2363: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
2364: l_n_sup_uoo_id igs_ps_unit_ofr_opt_all.sup_uoo_id%TYPE;
2365: l_c_relation_type igs_ps_unit_ofr_opt_all.relation_type%TYPE;
2366: l_n_subtitle_id igs_ps_unit_subtitle.subtitle_id%TYPE;
2367: l_b_uop_deleted BOOLEAN;
2368:
2369:
2370: /* Private Procedures for create_unit_section */
2528: ********************************************************************************************* */
2529: BEGIN
2530:
2531: /* Primary Key Validation */
2532: IF igs_ps_unit_ofr_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
2533: x_version_number => p_usec_rec.version_number,
2534: x_cal_type => l_c_cal_type ) THEN
2535: p_usec_rec.status := 'K';
2536: RETURN;
2537: END IF;
2538:
2539: /* Validate Check Constraints */
2540: BEGIN
2541: igs_ps_unit_ofr_pkg.check_constraints ( 'UNIT_CD', p_usec_rec.unit_cd );
2542: EXCEPTION
2543: WHEN OTHERS THEN
2544: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'UNIT_CD', 'LEGACY_TOKENS', TRUE);
2545: p_usec_rec.status := 'E';
2545: p_usec_rec.status := 'E';
2546: END;
2547:
2548: BEGIN
2549: igs_ps_unit_ofr_pkg.check_constraints ( 'CAL_TYPE', l_c_cal_type );
2550: EXCEPTION
2551: WHEN OTHERS THEN
2552: igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'ALT_CODE', 'LEGACY_TOKENS', TRUE);
2553: p_usec_rec.status := 'E';
2561: END IF;
2562: END;
2563:
2564: BEGIN
2565: IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation ( p_usec_rec.unit_cd, p_usec_rec.version_number ) THEN
2566: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_VERSION', 'LEGACY_TOKENS', FALSE);
2567: p_usec_rec.status := 'E';
2568: END IF;
2569: END;
2579: Purpose :
2580: Known limitations,enhancements,remarks:
2581: Change History (in reverse chronological order)
2582: Who When What
2583: sommukhe 14-NOV-2005 Bug # 4730169 addded column abort_flag in the insert to igs_ps_unit_ofr
2584: ********************************************************************************************* */
2585: BEGIN
2586:
2587: validate_uo_parameters ( p_usec_rec );
2585: BEGIN
2586:
2587: validate_uo_parameters ( p_usec_rec );
2588: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2589: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.status_after_validate_uo_parameters',
2590: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2591: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2592: END IF;
2593:
2593:
2594: IF ( p_usec_rec.status = 'S' ) THEN
2595: validate_uo_derivations ( p_usec_rec );
2596: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2597: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.status_after_validate_uo_derivations',
2598: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2599: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2600: END IF;
2601: END IF;
2602:
2603: IF ( p_usec_rec.status = 'S' ) THEN
2604: validate_uo_db_cons ( p_usec_rec );
2605: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2606: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.status_after_validate_uo_db_cons',
2607: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2608: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2609: END IF;
2610: END IF;
2611:
2612: IF ( p_usec_rec.status = 'K' ) THEN
2613: -- If the record is already existing update status as 'Success' and return
2614: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2615: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.record is already existing update status as Success and return',
2616: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2617: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2618: END IF;
2619: p_usec_rec.status := 'S';
2620: RETURN;
2621: END IF;
2622: IF p_calling_context IN ('G','S') THEN
2623: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2624: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.calling_context_G_or_S_return',
2625: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2626: ||p_usec_rec.teach_cal_alternate_code);
2627: END IF;
2628: RETURN ;
2635: fnd_msg_pub.add;
2636: p_usec_rec.status := 'E';
2637: END IF;
2638: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2639: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.status_after_Business_validation',
2640: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2641: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2642: END IF;
2643: END IF;
2642: END IF;
2643: END IF;
2644:
2645: IF ( p_usec_rec.status = 'S' ) THEN
2646: INSERT INTO igs_ps_unit_ofr
2647: (unit_cd,
2648: version_number,
2649: cal_type,
2650: created_by,
2664: g_n_login_id
2665: );
2666: p_usec_rec.status := 'S';
2667: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2668: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.Record_Inserted',
2669: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2670: ||p_usec_rec.teach_cal_alternate_code);
2671: END IF;
2672: END IF;
2693:
2694: -- Procedure to validate Database constraints for Unit Offering Pattern Records.
2695: PROCEDURE validate_uop_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type ) AS
2696:
2697: CURSOR cur_rowid ( cp_c_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2694: -- Procedure to validate Database constraints for Unit Offering Pattern Records.
2695: PROCEDURE validate_uop_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type ) AS
2696:
2697: CURSOR cur_rowid ( cp_c_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2702: FROM igs_ps_unit_ofr_pat_all
2695: PROCEDURE validate_uop_db_cons ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type ) AS
2696:
2697: CURSOR cur_rowid ( cp_c_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2702: FROM igs_ps_unit_ofr_pat_all
2703: WHERE unit_cd = cp_c_unit_cd AND
2696:
2697: CURSOR cur_rowid ( cp_c_unit_cd IN igs_ps_unit_ofr_pat.unit_cd%TYPE,
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2702: FROM igs_ps_unit_ofr_pat_all
2703: WHERE unit_cd = cp_c_unit_cd AND
2704: version_number = cp_n_ver_num AND
2698: cp_n_ver_num IN igs_ps_unit_ofr_pat.version_number%TYPE,
2699: cp_c_cal_type IN igs_ps_unit_ofr_pat.cal_type%TYPE,
2700: cp_n_ci_seq_num IN igs_ps_unit_ofr_pat.ci_sequence_number%TYPE) IS
2701: SELECT delete_flag
2702: FROM igs_ps_unit_ofr_pat_all
2703: WHERE unit_cd = cp_c_unit_cd AND
2704: version_number = cp_n_ver_num AND
2705: cal_type = cp_c_cal_type AND
2706: ci_sequence_number = cp_n_ci_seq_num;
2707: rec_rowid cur_rowid%ROWTYPE;
2708:
2709: BEGIN
2710:
2711: /* Pk validation for IGS_PS_UNIT_OFR_PAT , having delete_flag implementation*/
2712: l_b_uop_deleted := FALSE;
2713: OPEN cur_rowid( p_usec_rec.unit_cd,p_usec_rec.version_number,l_c_cal_type,l_n_seq_num);
2714: FETCH cur_rowid INTO rec_rowid;
2715: IF cur_rowid%FOUND THEN
2734: p_usec_rec.status := 'E';
2735: END IF;
2736:
2737: -- Check for existence of Unit Offering
2738: IF NOT igs_ps_unit_ofr_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
2739: x_version_number => p_usec_rec.version_number,
2740: x_cal_type => l_c_cal_type ) THEN
2741: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_OFR_PAT', 'LEGACY_TOKENS', FALSE);
2742: p_usec_rec.status := 'E';
2744:
2745: -- Check Constraints checking.
2746: IF p_usec_rec.waitlist_allowed IS NOT NULL THEN
2747: BEGIN
2748: igs_ps_unit_ofr_pat_pkg.check_constraints ( 'WAITLIST_ALLOWED', p_usec_rec.waitlist_allowed);
2749: EXCEPTION
2750: WHEN OTHERS THEN
2751: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'WAITLIST_ALLOWED', 'LEGACY_TOKENS', TRUE);
2752: p_usec_rec.status := 'E';
2754: END IF;
2755:
2756: IF p_usec_rec.max_students_per_waitlist IS NOT NULL THEN
2757: BEGIN
2758: igs_ps_unit_ofr_pat_pkg.check_constraints ( 'MAX_STUDENTS_PER_WAITLIST', p_usec_rec.max_students_per_waitlist);
2759: EXCEPTION
2760: WHEN OTHERS THEN
2761: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999', 'MAX_STUDENTS_PER_WAITLIST', 'LEGACY_TOKENS', TRUE);
2762: p_usec_rec.status := 'E';
2772: BEGIN
2773:
2774: validate_uop_derivations ( p_usec_rec );
2775: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2776: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.status_after_validate_uop_parameters',
2777: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2778: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2779: END IF;
2780:
2780:
2781: IF ( p_usec_rec.status = 'S' ) THEN
2782: validate_uop_db_cons ( p_usec_rec );
2783: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2784: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.status_after_validate_uop_db_cons',
2785: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2786: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2787: END IF;
2788: END IF;
2790:
2791: IF ( p_usec_rec.status = 'K' ) THEN
2792: -- If the record is already existing update status as 'Success' and return
2793: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2794: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.record is already existing update status as Success and return',
2795: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2796: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2797: END IF;
2798: p_usec_rec.status := 'S';
2799: RETURN;
2800: END IF;
2801: IF p_calling_context IN ('G','S') THEN
2802: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2803: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.calling_context_G_or_S_return',
2804: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2805: ||p_usec_rec.teach_cal_alternate_code);
2806: END IF;
2807: RETURN ;
2828: p_usec_rec.status := 'W';
2829: END IF;
2830:
2831: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2832: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.status_after_Business_validation',
2833: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2834: ||p_usec_rec.teach_cal_alternate_code||' '||'Status:'||p_usec_rec.status);
2835: END IF;
2836:
2837: END IF;
2838:
2839: IF ( p_usec_rec.status = 'S' ) THEN
2840: IF l_b_uop_deleted THEN
2841: UPDATE igs_ps_unit_ofr_pat_all
2842: SET
2843: waitlist_allowed = p_usec_rec.waitlist_allowed,
2844: max_students_per_waitlist = p_usec_rec.max_students_per_waitlist,
2845: delete_flag = 'N',
2852: AND version_number = p_usec_rec.version_number
2853: AND cal_type = l_c_cal_type
2854: AND ci_sequence_number = l_n_seq_num;
2855: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2856: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.Record_updated_when_uop_deleted',
2857: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2858: ||p_usec_rec.teach_cal_alternate_code);
2859: END IF;
2860: ELSE
2857: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2858: ||p_usec_rec.teach_cal_alternate_code);
2859: END IF;
2860: ELSE
2861: INSERT INTO igs_ps_unit_ofr_pat_all
2862: (unit_cd,
2863: version_number,
2864: cal_type,
2865: ci_sequence_number,
2893: g_n_login_id
2894: );
2895:
2896: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2897: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.Record_Inserted',
2898: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
2899: ||p_usec_rec.teach_cal_alternate_code);
2900: END IF;
2901: END IF;
2934: Who When What
2935: sarakshi 11-Sep-2003 Enh#3052452,dervied teh value of sup_uoo_id.
2936: sarakshi 22-Aug-2003 Bug#304509, defaulting the value of Not Multiple Unit Section Flag
2937: ********************************************************************************************* */
2938: l_c_sup_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2939: l_n_sup_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2940: l_d_sup_start_dt igs_ca_inst_all.start_dt%TYPE;
2941: l_d_sup_end_dt igs_ca_inst_all.end_dt%TYPE;
2942: l_c_message VARCHAR2(30);
2935: sarakshi 11-Sep-2003 Enh#3052452,dervied teh value of sup_uoo_id.
2936: sarakshi 22-Aug-2003 Bug#304509, defaulting the value of Not Multiple Unit Section Flag
2937: ********************************************************************************************* */
2938: l_c_sup_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
2939: l_n_sup_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
2940: l_d_sup_start_dt igs_ca_inst_all.start_dt%TYPE;
2941: l_d_sup_end_dt igs_ca_inst_all.end_dt%TYPE;
2942: l_c_message VARCHAR2(30);
2943:
3001: Purpose :
3002: Known limitations,enhancements,remarks:
3003: Change History (in reverse chronological order)
3004: Who When What
3005: sarakshi 11-Sep-2003 Enh#3052452, Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
3006: for validating default_enroll_flag.
3007: vvutukur 19-Aug-2003 Enh#3045069.PSP Enh Build. Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
3008: for validating not_multiple_section_flag.
3009: ********************************************************************************************* */
3003: Change History (in reverse chronological order)
3004: Who When What
3005: sarakshi 11-Sep-2003 Enh#3052452, Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
3006: for validating default_enroll_flag.
3007: vvutukur 19-Aug-2003 Enh#3045069.PSP Enh Build. Added call to igs_ps_unit_ofr_opt_pkg.check_constraints
3008: for validating not_multiple_section_flag.
3009: ********************************************************************************************* */
3010: BEGIN
3011:
3010: BEGIN
3011:
3012: IF p_insert_update = 'I' THEN
3013: /* Check for Unique Key Validation */
3014: IF igs_ps_unit_ofr_opt_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
3015: x_version_number => p_usec_rec.version_number,
3016: x_cal_type => l_c_cal_type,
3017: x_ci_sequence_number => l_n_seq_num,
3018: x_location_cd => p_usec_rec.location_cd,
3026: /* Check for Check Constraints */
3027:
3028: --Validate whether the column not_multiple_section_flag has value other than 'Y' or 'N'.
3029: BEGIN
3030: igs_ps_unit_ofr_opt_pkg.check_constraints('NOT_MULTIPLE_SECTION_FLAG',p_usec_rec.not_multiple_section_flag);
3031: EXCEPTION
3032: WHEN OTHERS THEN
3033: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N',
3034: 'NOT_MULTIPLE_SECTION_FLAG',
3038: END;
3039:
3040: -- Unit Class should be in Upper Case
3041: BEGIN
3042: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'UNIT_CLASS', p_usec_rec.unit_class );
3043: EXCEPTION
3044: WHEN OTHERS THEN
3045: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'UNIT_CLASS', 'LEGACY_TOKENS', TRUE);
3046: p_usec_rec.status := 'E';
3052: p_usec_rec.status := 'E';
3053: END IF;
3054:
3055: BEGIN
3056: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'GRADING_SCHEMA_CD', p_usec_rec.grading_schema_cd );
3057: EXCEPTION
3058: WHEN OTHERS THEN
3059: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'USEC_GRADING_SCHM_CD', 'LEGACY_TOKENS', TRUE);
3060: p_usec_rec.status := 'E';
3060: p_usec_rec.status := 'E';
3061: END;
3062:
3063: BEGIN
3064: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'LOCATION_CD', p_usec_rec.location_cd );
3065: EXCEPTION
3066: WHEN OTHERS THEN
3067: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'LOCATION_CD', 'LEGACY_TOKENS', TRUE);
3068: p_usec_rec.status := 'E';
3068: p_usec_rec.status := 'E';
3069: END;
3070:
3071: BEGIN
3072: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'UNIT_QUOTA', p_usec_rec.unit_quota );
3073: EXCEPTION
3074: WHEN OTHERS THEN
3075: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999', 'UNIT_QUOTA', 'LEGACY_TOKENS', TRUE);
3076: p_usec_rec.status := 'E';
3077: END;
3078:
3079: IF p_usec_rec.unit_quota_reserved_places IS NOT NULL THEN
3080: BEGIN
3081: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'UNIT_QUOTA_RESERVED_PLACES', p_usec_rec.unit_quota_reserved_places );
3082: EXCEPTION
3083: WHEN OTHERS THEN
3084: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999', 'UNIT_QUOTA_RESERVED_PLACES', 'LEGACY_TOKENS', TRUE);
3085: p_usec_rec.status := 'E';
3086: END;
3087: END IF;
3088:
3089: BEGIN
3090: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'CI_SEQUENCE_NUMBER', l_n_seq_num );
3091: EXCEPTION
3092: WHEN OTHERS THEN
3093: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999', 'CAL_SEQ_NUM', 'LEGACY_TOKENS', TRUE);
3094: p_usec_rec.status := 'E';
3094: p_usec_rec.status := 'E';
3095: END;
3096:
3097: BEGIN
3098: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'GRADING_SCHEMA_PRCDNCE_IND', p_usec_rec.grading_schema_prcdnce_ind );
3099: EXCEPTION
3100: WHEN OTHERS THEN
3101: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'GRADING_SCHEMA_PRCDNCE_IND', 'LEGACY_TOKENS', TRUE);
3102: p_usec_rec.status := 'E';
3102: p_usec_rec.status := 'E';
3103: END;
3104:
3105: BEGIN
3106: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'IVRS_AVAILABLE_IND', p_usec_rec.ivrs_available_ind );
3107: EXCEPTION
3108: WHEN OTHERS THEN
3109: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'IVRS_AVAILABLE_IND', 'LEGACY_TOKENS', TRUE);
3110: p_usec_rec.status := 'E';
3110: p_usec_rec.status := 'E';
3111: END;
3112:
3113: BEGIN
3114: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'OFFERED_IND', p_usec_rec.offered_ind );
3115: EXCEPTION
3116: WHEN OTHERS THEN
3117: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'OFFERED_IND', 'LEGACY_TOKENS', TRUE);
3118: p_usec_rec.status := 'E';
3118: p_usec_rec.status := 'E';
3119: END;
3120:
3121: BEGIN
3122: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'NON_STD_USEC_IND', p_usec_rec.non_std_usec_ind );
3123: EXCEPTION
3124: WHEN OTHERS THEN
3125: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'NON_STD_USEC_IND', 'LEGACY_TOKENS', TRUE);
3126: p_usec_rec.status := 'E';
3126: p_usec_rec.status := 'E';
3127: END;
3128:
3129: BEGIN
3130: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'AUDITABLE_IND', p_usec_rec.auditable_ind );
3131: EXCEPTION
3132: WHEN OTHERS THEN
3133: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'AUDITABLE_IND', 'LEGACY_TOKENS', TRUE);
3134: p_usec_rec.status := 'E';
3134: p_usec_rec.status := 'E';
3135: END;
3136:
3137: BEGIN
3138: igs_ps_unit_ofr_opt_pkg.check_constraints ( 'AUDIT_PERMISSION_IND', p_usec_rec.audit_permission_ind );
3139: EXCEPTION
3140: WHEN OTHERS THEN
3141: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N', 'AUDIT_PERMISSION_IND', 'LEGACY_TOKENS', TRUE);
3142: p_usec_rec.status := 'E';
3160:
3161: /* Check for Foreign Key Validations */
3162:
3163: -- Check for existence of Unit Offering Pattern
3164: IF NOT igs_ps_unit_ofr_pat_pkg.get_pk_for_validation ( x_unit_cd => p_usec_rec.unit_cd,
3165: x_version_number => p_usec_rec.version_number,
3166: x_cal_type => l_c_cal_type,
3167: x_ci_sequence_number => l_n_seq_num ) THEN
3168: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_OFR_PAT', 'LEGACY_TOKENS', FALSE);
3202: END IF;
3203:
3204: --Validate default enroll flag
3205: BEGIN
3206: igs_ps_unit_ofr_opt_pkg.check_constraints('DEFAULT_ENROLL_FLAG',p_usec_rec.default_enroll_flag);
3207: EXCEPTION
3208: WHEN OTHERS THEN
3209: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','DEFAULT_ENROLL_FLAG','LEGACY_TOKENS',TRUE);
3210: p_usec_rec.status :='E';
3233: vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Added new column not_multiple_section_flag while insertion.
3234: ********************************************************************************************* */
3235: AS
3236:
3237: CURSOR c_usec_check(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
3238: SELECT 'X'
3239: FROM igs_ps_unit_ofr_opt_all
3240: WHERE uoo_id=cp_uoo_id
3241: AND relation_type='SUPERIOR';
3235: AS
3236:
3237: CURSOR c_usec_check(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
3238: SELECT 'X'
3239: FROM igs_ps_unit_ofr_opt_all
3240: WHERE uoo_id=cp_uoo_id
3241: AND relation_type='SUPERIOR';
3242: l_c_var VARCHAR2(1);
3243:
3272: l_cur_int_usec cur_int_usec%ROWTYPE;
3273:
3274:
3275: FUNCTION check_insert_update ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type) RETURN VARCHAR2 IS
3276: CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
3277: SELECT 'X'
3278: FROM igs_ps_unit_ofr_opt_all
3279: WHERE unit_cd = p_usec_rec.unit_cd
3280: AND version_number = p_usec_rec.version_number
3274:
3275: FUNCTION check_insert_update ( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type) RETURN VARCHAR2 IS
3276: CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
3277: SELECT 'X'
3278: FROM igs_ps_unit_ofr_opt_all
3279: WHERE unit_cd = p_usec_rec.unit_cd
3280: AND version_number = p_usec_rec.version_number
3281: AND ci_sequence_number =cp_seq_num
3282: AND unit_class = p_usec_rec.unit_class
3298: END check_insert_update;
3299:
3300: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
3301:
3302: CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
3303: SELECT *
3304: FROM igs_ps_unit_ofr_opt_all
3305: WHERE unit_cd = p_usec_rec.unit_cd
3306: AND version_number = p_usec_rec.version_number
3300: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,p_insert_update VARCHAR2 ) AS
3301:
3302: CURSOR c_usec(cp_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE,cp_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE) IS
3303: SELECT *
3304: FROM igs_ps_unit_ofr_opt_all
3305: WHERE unit_cd = p_usec_rec.unit_cd
3306: AND version_number = p_usec_rec.version_number
3307: AND ci_sequence_number =cp_seq_num
3308: AND unit_class = p_usec_rec.unit_class
3308: AND unit_class = p_usec_rec.unit_class
3309: AND location_cd = p_usec_rec.location_cd
3310: AND cal_type = cp_cal_type ;
3311:
3312: CURSOR c_audit_info ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3313: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3314: SELECT auditable_ind, audit_permission_ind
3315: FROM igs_ps_unit_ver_all
3316: WHERE unit_cd = cp_unit_cd
3309: AND location_cd = p_usec_rec.location_cd
3310: AND cal_type = cp_cal_type ;
3311:
3312: CURSOR c_audit_info ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3313: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3314: SELECT auditable_ind, audit_permission_ind
3315: FROM igs_ps_unit_ver_all
3316: WHERE unit_cd = cp_unit_cd
3317: AND version_number = cp_version_number;
3311:
3312: CURSOR c_audit_info ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3313: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3314: SELECT auditable_ind, audit_permission_ind
3315: FROM igs_ps_unit_ver_all
3316: WHERE unit_cd = cp_unit_cd
3317: AND version_number = cp_version_number;
3318:
3319: CURSOR c_grd_sch ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3315: FROM igs_ps_unit_ver_all
3316: WHERE unit_cd = cp_unit_cd
3317: AND version_number = cp_version_number;
3318:
3319: CURSOR c_grd_sch ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3320: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3321: SELECT grading_schema_code, grd_schm_version_number
3322: FROM igs_ps_unit_grd_schm
3323: WHERE unit_code = cp_unit_cd
3316: WHERE unit_cd = cp_unit_cd
3317: AND version_number = cp_version_number;
3318:
3319: CURSOR c_grd_sch ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3320: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3321: SELECT grading_schema_code, grd_schm_version_number
3322: FROM igs_ps_unit_grd_schm
3323: WHERE unit_code = cp_unit_cd
3324: AND unit_version_number = cp_version_number
3318:
3319: CURSOR c_grd_sch ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3320: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3321: SELECT grading_schema_code, grd_schm_version_number
3322: FROM igs_ps_unit_grd_schm
3323: WHERE unit_code = cp_unit_cd
3324: AND unit_version_number = cp_version_number
3325: AND default_flag = 'Y';
3326:
3323: WHERE unit_code = cp_unit_cd
3324: AND unit_version_number = cp_version_number
3325: AND default_flag = 'Y';
3326:
3327: CURSOR c_ivr_enrol_ind ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3328: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3329: SELECT ivr_enrol_ind
3330: FROM igs_ps_unit_ver_all
3331: WHERE unit_cd = cp_unit_cd
3324: AND unit_version_number = cp_version_number
3325: AND default_flag = 'Y';
3326:
3327: CURSOR c_ivr_enrol_ind ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3328: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3329: SELECT ivr_enrol_ind
3330: FROM igs_ps_unit_ver_all
3331: WHERE unit_cd = cp_unit_cd
3332: AND version_number = cp_version_number;
3326:
3327: CURSOR c_ivr_enrol_ind ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3328: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3329: SELECT ivr_enrol_ind
3330: FROM igs_ps_unit_ver_all
3331: WHERE unit_cd = cp_unit_cd
3332: AND version_number = cp_version_number;
3333:
3334: CURSOR c_ss_enrol_ind ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3330: FROM igs_ps_unit_ver_all
3331: WHERE unit_cd = cp_unit_cd
3332: AND version_number = cp_version_number;
3333:
3334: CURSOR c_ss_enrol_ind ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3335: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3336: SELECT ss_enrol_ind
3337: FROM igs_ps_unit_ver_all
3338: WHERE unit_cd = cp_unit_cd
3331: WHERE unit_cd = cp_unit_cd
3332: AND version_number = cp_version_number;
3333:
3334: CURSOR c_ss_enrol_ind ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3335: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3336: SELECT ss_enrol_ind
3337: FROM igs_ps_unit_ver_all
3338: WHERE unit_cd = cp_unit_cd
3339: AND version_number = cp_version_number;
3333:
3334: CURSOR c_ss_enrol_ind ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3335: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3336: SELECT ss_enrol_ind
3337: FROM igs_ps_unit_ver_all
3338: WHERE unit_cd = cp_unit_cd
3339: AND version_number = cp_version_number;
3340:
3341: CURSOR c_muiltiple_section_flag ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3337: FROM igs_ps_unit_ver_all
3338: WHERE unit_cd = cp_unit_cd
3339: AND version_number = cp_version_number;
3340:
3341: CURSOR c_muiltiple_section_flag ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3342: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3343: SELECT same_teaching_period
3344: FROM igs_ps_unit_ver_all
3345: WHERE unit_cd = cp_unit_cd
3338: WHERE unit_cd = cp_unit_cd
3339: AND version_number = cp_version_number;
3340:
3341: CURSOR c_muiltiple_section_flag ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3342: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3343: SELECT same_teaching_period
3344: FROM igs_ps_unit_ver_all
3345: WHERE unit_cd = cp_unit_cd
3346: AND version_number = cp_version_number;
3340:
3341: CURSOR c_muiltiple_section_flag ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3342: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3343: SELECT same_teaching_period
3344: FROM igs_ps_unit_ver_all
3345: WHERE unit_cd = cp_unit_cd
3346: AND version_number = cp_version_number;
3347:
3348: CURSOR c_org_unit_cd ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3344: FROM igs_ps_unit_ver_all
3345: WHERE unit_cd = cp_unit_cd
3346: AND version_number = cp_version_number;
3347:
3348: CURSOR c_org_unit_cd ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3349: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3350: SELECT owner_org_unit_cd
3351: FROM igs_ps_unit_ver_all
3352: WHERE unit_cd = cp_unit_cd
3345: WHERE unit_cd = cp_unit_cd
3346: AND version_number = cp_version_number;
3347:
3348: CURSOR c_org_unit_cd ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3349: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3350: SELECT owner_org_unit_cd
3351: FROM igs_ps_unit_ver_all
3352: WHERE unit_cd = cp_unit_cd
3353: AND version_number = cp_version_number;
3347:
3348: CURSOR c_org_unit_cd ( cp_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,
3349: cp_version_number igs_ps_unit_ver_all.version_number%TYPE )IS
3350: SELECT owner_org_unit_cd
3351: FROM igs_ps_unit_ver_all
3352: WHERE unit_cd = cp_unit_cd
3353: AND version_number = cp_version_number;
3354:
3355: c_usec_rec c_usec%ROWTYPE;
3352: WHERE unit_cd = cp_unit_cd
3353: AND version_number = cp_version_number;
3354:
3355: c_usec_rec c_usec%ROWTYPE;
3356: l_same_teaching_period igs_ps_unit_ver_all.same_teaching_period%TYPE;
3357:
3358: BEGIN
3359:
3360: IF p_insert_update = 'I' THEN
3672:
3673: validate_uoo_parameters ( p_usec_rec );
3674:
3675: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3676: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_validate_uoo_parameters',
3677: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3678: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3679: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
3680: END IF;
3683: validate_uoo_derivations ( p_usec_rec );
3684:
3685:
3686: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3687: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_validate_uoo_derivations',
3688: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3689: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3690: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
3691: END IF;
3697: IF p_usec_rec.status = 'S' AND p_calling_context IN ('G', 'S') THEN
3698: l_insert_update:= check_insert_update(p_usec_rec);
3699:
3700: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3701: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_check_insert_update',
3702: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3703: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3704: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
3705: END IF;
3718: p_usec_rec.status := 'A';
3719: END IF;
3720:
3721: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3722: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_check_import_allowed',
3723: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3724: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3725: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
3726: END IF;
3730: IF p_usec_rec.status = 'S' THEN
3731: Assign_default(p_usec_rec,l_insert_update);
3732:
3733: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3734: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_Assign_default',
3735: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3736: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3737: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
3738: END IF;
3742: IF ( p_usec_rec.status = 'S' ) THEN
3743: validate_uoo_db_cons ( p_usec_rec,l_insert_update );
3744:
3745: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3746: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_validate_uoo_db_cons',
3747: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3748: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3749: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
3750: END IF;
3758: -- Check for validation by calling validate_uoo
3759: igs_ps_validate_lgcy_pkg.validate_uoo ( p_usec_rec, l_c_cal_type, l_n_seq_num,l_n_sup_uoo_id,l_insert_update,l_conc_flag ) ;
3760:
3761: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3762: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_Business validations',
3763: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3764: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3765: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
3766: END IF;
3775: IF l_n_sup_uoo_id IS NOT NULL THEN
3776: OPEN c_usec_check(l_n_sup_uoo_id);
3777: FETCH c_usec_check INTO l_c_var;
3778: IF c_usec_check%NOTFOUND THEN
3779: UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'SUPERIOR'
3780: WHERE uoo_id = l_n_sup_uoo_id;
3781: END IF;
3782: CLOSE c_usec_check;
3783: l_c_relation_type := 'SUBORDINATE';
3793: cp_location_cd IN VARCHAR2,
3794: cp_unit_class IN VARCHAR2
3795: ) IS
3796: SELECT relation_type,sup_uoo_id
3797: FROM igs_ps_unit_ofr_opt_all
3798: WHERE unit_cd = cp_unit_cd
3799: AND version_number = cp_version_number
3800: AND ci_sequence_number =cp_seq_num
3801: AND unit_class = cp_unit_class
3804: l_cur_usec cur_usec%ROWTYPE;
3805:
3806: CURSOR c_count_sup(cp_uoo_id NUMBER) IS
3807: SELECT COUNT(*)
3808: FROM igs_ps_unit_ofr_opt_all
3809: WHERE sup_uoo_id= cp_uoo_id;
3810: l_c_count NUMBER;
3811: BEGIN
3812:
3818: IF l_n_sup_uoo_id IS NOT NULL THEN
3819: OPEN c_usec_check(l_n_sup_uoo_id);
3820: FETCH c_usec_check INTO l_c_var;
3821: IF c_usec_check%NOTFOUND THEN
3822: UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'SUPERIOR'
3823: WHERE uoo_id = l_n_sup_uoo_id;
3824: END IF;
3825: CLOSE c_usec_check;
3826: l_c_relation_type := 'SUBORDINATE';
3831: OPEN c_count_sup(l_cur_usec.sup_uoo_id);
3832: FETCH c_count_sup INTO l_c_count;
3833: IF c_count_sup%FOUND THEN
3834: IF l_c_count < 2 THEN
3835: UPDATE igs_ps_unit_ofr_opt_all SET relation_type = 'NONE'
3836: WHERE uoo_id = l_cur_usec.sup_uoo_id;
3837: END IF;
3838: END IF;
3839: CLOSE c_count_sup;
3850: --Set the value of the call_number if the profile option is AUTO
3851:
3852: IF l_insert_update = 'I' THEN
3853: IF FND_PROFILE.VALUE('IGS_PS_CALL_NUMBER') = 'AUTO' THEN
3854: p_usec_rec.call_number := igs_ps_unit_ofr_opt_pkg.get_call_number( l_c_cal_type, l_n_seq_num);
3855: END IF;
3856:
3857: INSERT INTO igs_ps_unit_ofr_opt_all
3858: (unit_cd,
3853: IF FND_PROFILE.VALUE('IGS_PS_CALL_NUMBER') = 'AUTO' THEN
3854: p_usec_rec.call_number := igs_ps_unit_ofr_opt_pkg.get_call_number( l_c_cal_type, l_n_seq_num);
3855: END IF;
3856:
3857: INSERT INTO igs_ps_unit_ofr_opt_all
3858: (unit_cd,
3859: version_number,
3860: cal_type,
3861: ci_sequence_number,
3906: l_c_cal_type,
3907: l_n_seq_num,
3908: p_usec_rec.location_cd,
3909: p_usec_rec.unit_class,
3910: igs_ps_unit_ofr_opt_uoo_id_s.NEXTVAL,
3911: p_usec_rec.ivrs_available_ind,
3912: p_usec_rec.call_number,
3913: p_usec_rec.unit_section_status,
3914: p_usec_rec.unit_section_start_date,
3947: 'N'
3948: );
3949:
3950: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3951: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.record_inserted',
3952: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3953: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3954: p_usec_rec.unit_class);
3955: END IF;
3963: END IF;
3964: CLOSE cur_int_pat;
3965:
3966: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3967: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.interface_table_updated',
3968: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
3969: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
3970: p_usec_rec.unit_class||' '||'int_pat_id:'||l_cur_int_pat.int_pat_id);
3971: END IF;
3972:
3973: END IF;
3974:
3975: ELSE --update
3976: UPDATE igs_ps_unit_ofr_opt_all SET
3977: ivrs_available_ind = p_usec_rec.ivrs_available_ind,
3978: call_number=p_usec_rec.call_number,
3979: unit_section_status=p_usec_rec.unit_section_status,
3980: unit_section_start_date=p_usec_rec.unit_section_start_date,
4015: AND location_cd = p_usec_rec.location_cd
4016: AND cal_type = l_c_cal_type ;
4017:
4018: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4019: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.record_updated',
4020: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4021: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4022: p_usec_rec.unit_class);
4023: END IF;
4031: UPDATE igs_ps_sch_pat_int set import_done_flag='Y' WHERE int_pat_id = l_cur_int_usec.int_pat_id;
4032: END IF;
4033:
4034: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4035: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.interface_table_updated',
4036: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4037: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4038: p_usec_rec.unit_class||' '||'int_pat_id:'||l_cur_int_pat.int_pat_id);
4039: END IF;
4083: END IF;
4084: END IF;
4085:
4086: /* Check for Foreign Key Constraints */
4087: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
4088: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
4089: p_usec_rec.status := 'E';
4090: END IF;
4091:
4482: IF p_usec_rec.status = 'S' THEN
4483: get_uoo_id ( p_usec_rec);
4484:
4485: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4486: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.status_after_get_uoo_id',
4487: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4488: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4489: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4490: END IF;
4496: IF p_usec_rec.status = 'S' AND p_calling_context IN ('G', 'S') THEN
4497: l_insert_update:= check_insert_update(p_usec_rec);
4498:
4499: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4500: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.status_after_check_insert_update',
4501: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4502: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4503: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4504: END IF;
4508: IF p_usec_rec.status = 'S' THEN
4509: Assign_default(p_usec_rec,l_insert_update);
4510:
4511: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4512: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.status_after_Assign_default',
4513: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4514: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4515: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4516: END IF;
4520: IF ( p_usec_rec.status = 'S' ) THEN
4521: validate_cp_db_cons ( p_usec_rec,l_insert_update );
4522:
4523: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4524: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.status_after_validate_cp_db_cons',
4525: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4526: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4527: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4528: END IF;
4534: -- Check for validation by calling validate_cps
4535: igs_ps_validate_lgcy_pkg.validate_cps ( p_usec_rec,l_n_uoo_id,l_insert_update ) ;
4536:
4537: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4538: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.status_after_Business_validation',
4539: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4540: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4541: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4542: END IF;
4607: p_usec_rec.claimable_hours
4608: );
4609:
4610: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4611: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.record_inserted',
4612: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4613: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4614: p_usec_rec.unit_class);
4615: END IF;
4642: claimable_hours =p_usec_rec.claimable_hours
4643: WHERE uoo_id = l_n_uoo_id;
4644:
4645: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4646: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.record_updated',
4647: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4648: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4649: p_usec_rec.unit_class);
4650: END IF;
4668: END IF;
4669: END IF;
4670:
4671: /* Check for Foreign Key Constraints */
4672: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
4673: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
4674: p_usec_rec.status := 'E';
4675: END IF;
4676:
4906: IF p_usec_rec.status = 'S' THEN
4907: get_uoo_id ( p_usec_rec);
4908:
4909: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4910: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.status_after_get_uoo_id',
4911: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4912: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4913: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4914: END IF;
4920: IF p_usec_rec.status = 'S' AND p_calling_context IN ('G', 'S') THEN
4921: l_insert_update:= check_insert_update(p_usec_rec);
4922:
4923: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4924: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.status_after_check_insert_update',
4925: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4926: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4927: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4928: END IF;
4932: IF p_usec_rec.status = 'S' THEN
4933: Assign_default(p_usec_rec,l_insert_update);
4934:
4935: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4936: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.status_after_Assign_default',
4937: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4938: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4939: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4940: END IF;
4944: IF ( p_usec_rec.status = 'S' ) THEN
4945: validate_ref_db_cons ( p_usec_rec,l_insert_update );
4946:
4947: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4948: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.status_after_validate_ref_db_cons',
4949: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4950: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4951: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4952: END IF;
4958: l_n_subtitle_id := NULL;
4959: igs_ps_validate_lgcy_pkg.validate_ref ( p_usec_rec, l_n_subtitle_id ,l_n_uoo_id,l_insert_update);
4960:
4961: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4962: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.status_after_Business_validation',
4963: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
4964: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
4965: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
4966: END IF;
5047: l_n_subtitle_id
5048: );
5049:
5050: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5051: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.record_inserted',
5052: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5053: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5054: p_usec_rec.unit_class);
5055: END IF;
5089: subtitle_id=l_n_subtitle_id
5090: WHERE uoo_id = l_n_uoo_id;
5091:
5092: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5093: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.record_updated',
5094: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5095: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5096: p_usec_rec.unit_class);
5097: END IF;
5137: END check_insert_update;
5138:
5139: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
5140: p_insert_update VARCHAR2 ) AS
5141: CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5138:
5139: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
5140: p_insert_update VARCHAR2 ) AS
5141: CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5146: FROM igs_ps_unit_ofr_pat_all
5139: PROCEDURE Assign_default( p_usec_rec IN OUT NOCOPY igs_ps_generic_pub.usec_rec_type,
5140: p_insert_update VARCHAR2 ) AS
5141: CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5146: FROM igs_ps_unit_ofr_pat_all
5147: WHERE unit_cd = cp_c_unit_cd
5140: p_insert_update VARCHAR2 ) AS
5141: CURSOR c_uop(cp_c_unit_cd IN igs_ps_unit_ofr_pat_all.unit_cd%TYPE,
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5146: FROM igs_ps_unit_ofr_pat_all
5147: WHERE unit_cd = cp_c_unit_cd
5148: AND version_number = cp_n_ver_num
5142: cp_n_ver_num IN igs_ps_unit_ofr_pat_all.version_number%TYPE,
5143: cp_c_cal_type IN igs_ps_unit_ofr_pat_all.cal_type%TYPE,
5144: cp_n_seq_num IN igs_ps_unit_ofr_pat_all.ci_sequence_number%TYPE ) IS
5145: SELECT waitlist_allowed, max_students_per_waitlist
5146: FROM igs_ps_unit_ofr_pat_all
5147: WHERE unit_cd = cp_c_unit_cd
5148: AND version_number = cp_n_ver_num
5149: AND cal_type = cp_c_cal_type
5150: AND ci_sequence_number = cp_n_seq_num ;
5239: END IF;
5240:
5241: IF p_usec_rec.enrollment_expected IS NOT NULL THEN
5242: BEGIN
5243: igs_ps_unit_ver_pkg.check_constraints('ENROLLMENT_EXPECTED',p_usec_rec.enrollment_expected);
5244: EXCEPTION
5245: WHEN OTHERS THEN
5246: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','ENROLLMENT_EXPECTED','LEGACY_TOKENS',TRUE);
5247: p_usec_rec.status :='E';
5249: END IF;
5250:
5251: IF p_usec_rec.enrollment_minimum IS NOT NULL THEN
5252: BEGIN
5253: igs_ps_unit_ver_pkg.check_constraints('ENROLLMENT_MINIMUM',p_usec_rec.enrollment_minimum);
5254: EXCEPTION
5255: WHEN OTHERS THEN
5256: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','ENROLLMENT_MINIMUM','LEGACY_TOKENS',TRUE);
5257: p_usec_rec.status :='E';
5259: END IF;
5260:
5261: IF p_usec_rec.enrollment_maximum IS NOT NULL THEN
5262: BEGIN
5263: igs_ps_unit_ver_pkg.check_constraints('ENROLLMENT_MAXIMUM',p_usec_rec.enrollment_maximum);
5264: EXCEPTION
5265: WHEN OTHERS THEN
5266: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','ENROLLMENT_MAXIMUM','LEGACY_TOKENS',TRUE);
5267: p_usec_rec.status :='E';
5269: END IF;
5270:
5271: IF p_usec_rec.advance_maximum IS NOT NULL THEN
5272: BEGIN
5273: igs_ps_unit_ver_pkg.check_constraints('ADVANCE_MAXIMUM',p_usec_rec.advance_maximum);
5274: EXCEPTION
5275: WHEN OTHERS THEN
5276: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','ADVANCE_MAXIMUM','LEGACY_TOKENS',TRUE);
5277: p_usec_rec.status :='E';
5279: END IF;
5280:
5281: IF p_usec_rec.override_enrollment_maximum IS NOT NULL THEN
5282: BEGIN
5283: igs_ps_unit_ver_pkg.check_constraints('OVERRIDE_ENROLLMENT_MAX',p_usec_rec.override_enrollment_maximum);
5284: EXCEPTION
5285: WHEN OTHERS THEN
5286: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','OVERRIDE_ENROLLMENT_MAX','LEGACY_TOKENS',TRUE);
5287: p_usec_rec.status :='E';
5289: END IF;
5290:
5291: IF p_usec_rec.max_auditors_allowed IS NOT NULL THEN
5292: BEGIN
5293: igs_ps_unit_ver_pkg.check_constraints('MAX_AUDITORS_ALLOWED',p_usec_rec. max_auditors_allowed);
5294: EXCEPTION
5295: WHEN OTHERS THEN
5296: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','MAX_AUDITORS_ALLOWED','LEGACY_TOKENS',TRUE);
5297: p_usec_rec.status :='E';
5299: END IF;
5300:
5301: IF p_usec_rec.usec_waitlist_allowed IS NOT NULL THEN
5302: BEGIN
5303: igs_ps_unit_ofr_pat_pkg.check_constraints ('WAITLIST_ALLOWED', p_usec_rec.usec_waitlist_allowed);
5304: EXCEPTION
5305: WHEN OTHERS THEN
5306: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_Y_OR_N','WAITLIST_ALLOWED', 'LEGACY_TOKENS', TRUE);
5307: p_usec_rec.status := 'E';
5309: END IF;
5310:
5311: IF p_usec_rec.usec_max_students_per_waitlist IS NOT NULL THEN
5312: BEGIN
5313: igs_ps_unit_ofr_pat_pkg.check_constraints('MAX_STUDENTS_PER_WAITLIST',p_usec_rec.usec_max_students_per_waitlist);
5314: EXCEPTION
5315: WHEN OTHERS THEN
5316: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VAL_0_999999','MAX_STUDENTS_PER_WAITLIST','LEGACY_TOKENS',TRUE);
5317: p_usec_rec.status :='E';
5317: p_usec_rec.status :='E';
5318: END;
5319: END IF;
5320:
5321: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
5322: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
5323: p_usec_rec.status := 'E';
5324: END IF;
5325:
5343: IF p_usec_rec.status = 'S' THEN
5344: get_uoo_id ( p_usec_rec);
5345:
5346: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5347: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.status_after_get_uoo_id',
5348: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5349: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5350: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
5351: END IF;
5357: IF p_usec_rec.status = 'S' AND p_calling_context IN ('G', 'S') THEN
5358: l_insert_update:= check_insert_update(p_usec_rec);
5359:
5360: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5361: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.status_after_check_insert_update',
5362: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5363: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5364: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
5365: END IF;
5369: IF p_usec_rec.status = 'S' THEN
5370: Assign_default(p_usec_rec,l_insert_update);
5371:
5372: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5373: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.status_after_Assign_default',
5374: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5375: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5376: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
5377: END IF;
5381: IF p_usec_rec.status = 'S' THEN
5382: validate_db_cons(p_usec_rec,l_insert_update);
5383:
5384: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5385: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.status_after_validate_db_cons',
5386: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5387: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5388: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
5389: END IF;
5393: IF p_usec_rec.status = 'S' THEN
5394: igs_ps_validate_lgcy_pkg.validate_usec_el (p_usec_rec,l_n_uoo_id,l_insert_update);
5395:
5396: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5397: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.status_after_business_validations',
5398: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5399: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5400: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
5401: END IF;
5441: g_n_login_id
5442: );
5443:
5444: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5445: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.record_inserted',
5446: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5447: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5448: p_usec_rec.unit_class);
5449: END IF;
5463: last_update_login= g_n_login_id
5464: WHERE uoo_id =l_n_uoo_id;
5465:
5466: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5467: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.record_updated',
5468: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5469: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5470: p_usec_rec.unit_class);
5471: END IF;
5474:
5475: igs_ps_validate_lgcy_pkg.post_usec_limits(p_usec_rec,p_calling_context,l_n_uoo_id,l_insert_update);
5476:
5477: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5478: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.status_after_post_usec_limits',
5479: 'Unit code:'||p_usec_rec.unit_cd||' '||'Version number:'||p_usec_rec.version_number||' '||'teach_cal_alternate_code:'
5480: ||p_usec_rec.teach_cal_alternate_code||' '||'Location_cd:'||p_usec_rec.location_cd||' '||'Unit Class:'||
5481: p_usec_rec.unit_class||' '||'Status:'||p_usec_rec.status);
5482: END IF;
5490:
5491: BEGIN
5492:
5493: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5494: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.start_logging_for','Unit Sections');
5495: END IF;
5496:
5497: p_c_rec_status := 'S';
5498: FOR I in 1..p_usec_tbl.LAST LOOP
5504: trim_values(p_usec_tbl(I));
5505: -- Create Unit Offering
5506:
5507: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5508: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.call',
5509: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5510: ||p_usec_tbl(I).teach_cal_alternate_code);
5511: END IF;
5512:
5512:
5513: create_uo ( p_usec_rec => p_usec_tbl(I) );
5514:
5515: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5516: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uo.status_after_record_creation',
5517: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5518: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Status:'||p_usec_tbl(I).status);
5519: END IF;
5520:
5522: IF p_usec_tbl(I).status = 'S' THEN
5523: -- Create Unit Offering Pattern
5524:
5525: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5526: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.call',
5527: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5528: ||p_usec_tbl(I).teach_cal_alternate_code);
5529: END IF;
5530:
5530:
5531: create_uop ( p_usec_tbl(I) );
5532:
5533: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5534: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uop.status_after_record_creation',
5535: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5536: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Status:'||p_usec_tbl(I).status);
5537: END IF;
5538:
5540:
5541: IF p_usec_tbl(I).status = 'S' THEN
5542: -- Create Unit Offering Option
5543: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5544: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.call',
5545: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5546: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_tbl(I).location_cd||' '||'Unit Class:'||
5547: p_usec_tbl(I).unit_class);
5548: END IF;
5549:
5550: create_uoo ( p_usec_tbl(I) );
5551:
5552: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5553: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_uoo.status_after_record_creation',
5554: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5555: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_tbl(I).location_cd||' '||'Unit Class:'||
5556: p_usec_tbl(I).unit_class||' '||'Status:'||p_usec_tbl(I).status);
5557: END IF;
5558: END IF;
5559: IF p_usec_tbl(I).status = 'S' THEN
5560: -- Create Unit Section Credit Points
5561: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5562: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.call',
5563: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5564: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_tbl(I).location_cd||' '||'Unit Class:'||
5565: p_usec_tbl(I).unit_class);
5566: END IF;
5567:
5568: create_cp ( p_usec_tbl(I) );
5569:
5570: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5571: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_cp.status_after_record_creation',
5572: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5573: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_tbl(I).location_cd||' '||'Unit Class:'||
5574: p_usec_tbl(I).unit_class||' '||'Status:'||p_usec_tbl(I).status);
5575: END IF;
5577: END IF;
5578: IF p_usec_tbl(I).status = 'S' THEN
5579: -- Create Unit Section Reference
5580: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5581: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.call',
5582: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5583: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_tbl(I).location_cd||' '||'Unit Class:'||
5584: p_usec_tbl(I).unit_class);
5585: END IF;
5586:
5587: create_ref ( p_usec_tbl(I) );
5588:
5589: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5590: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_ref.status_after_record_creation',
5591: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5592: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_tbl(I).location_cd||' '||'Unit Class:'||
5593: p_usec_tbl(I).unit_class||' '||'Status:'||p_usec_tbl(I).status);
5594: END IF;
5596: END IF;
5597: IF p_usec_tbl(I).status = 'S' THEN
5598: -- Create Unit Section Enrollment Limits
5599: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5600: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.call',
5601: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5602: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_tbl(I).location_cd||' '||'Unit Class:'||
5603: p_usec_tbl(I).unit_class);
5604: END IF;
5605:
5606: create_usec_el ( p_usec_tbl(I) );
5607:
5608: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5609: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.create_usec_el.status_after_record_creation',
5610: 'Unit code:'||p_usec_tbl(I).unit_cd||' '||'Version number:'||p_usec_tbl(I).version_number||' '||'teach_cal_alternate_code:'
5611: ||p_usec_tbl(I).teach_cal_alternate_code||' '||'Location_cd:'||p_usec_tbl(I).location_cd||' '||'Unit Class:'||
5612: p_usec_tbl(I).unit_class||' '||'Status:'||p_usec_tbl(I).status);
5613: END IF;
5646: END IF;--exists
5647: END LOOP;
5648:
5649: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5650: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_section.after_import_status',p_c_rec_status);
5651: END IF;
5652:
5653: END create_unit_section;
5654:
5667: Change History (in reverse chronological order)
5668: Who When What
5669: ********************************************************************************************** */
5670: l_insert_update VARCHAR2(1);
5671: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
5672: l_tbl_uoo igs_ps_create_generic_pkg.uoo_tbl_type;
5673:
5674: /* Private Procedures for create_usec_grd_sch */
5675: PROCEDURE trim_values ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type ) AS
5748: END check_insert_update;
5749:
5750: -- Carry out derivations and validate them
5751: PROCEDURE validate_derivations ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type ) AS
5752: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
5753: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
5754: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
5755: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
5756: l_c_message VARCHAR2(30);
5749:
5750: -- Carry out derivations and validate them
5751: PROCEDURE validate_derivations ( p_usec_gs_rec IN OUT NOCOPY igs_ps_generic_pub.usec_gs_rec_type ) AS
5752: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
5753: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
5754: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
5755: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
5756: l_c_message VARCHAR2(30);
5757: BEGIN
5815: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'USEC_GRADING_SCHM', 'LEGACY_TOKENS', FALSE);
5816: p_usec_gs_rec.status := 'E';
5817: END IF;
5818:
5819: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
5820: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
5821: p_usec_gs_rec.status := 'E';
5822: END IF;
5823: END validate_db_cons;
5826: /* Main Unit Section Grading Schema Sub Process */
5827: BEGIN
5828:
5829: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5830: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.start_logging_for','Unit Section Grading Schema');
5831: END IF;
5832:
5833: p_c_rec_status := 'S';
5834: FOR I in 1..p_tab_usec_gs.LAST LOOP
5840: trim_values(p_tab_usec_gs(I) );
5841: validate_parameters ( p_tab_usec_gs(I) );
5842:
5843: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5844: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.status_after_validate_parameters',
5845: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5846: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5847: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5848: ||p_tab_usec_gs(I).grd_schm_version_number||' '||'Status:'||p_tab_usec_gs(I).status);
5851: IF p_tab_usec_gs(I).status = 'S' THEN
5852: validate_derivations ( p_tab_usec_gs(I));
5853:
5854: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5855: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.status_after_validate_derivations',
5856: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5857: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5858: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5859: ||p_tab_usec_gs(I).grd_schm_version_number||' '||'Status:'||p_tab_usec_gs(I).status);
5866: IF p_tab_usec_gs(I).status = 'S' AND p_calling_context IN ('G','S') THEN
5867: l_insert_update:= check_insert_update(p_tab_usec_gs(I));
5868:
5869: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5870: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.status_after_check_insert_update',
5871: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5872: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5873: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5874: ||p_tab_usec_gs(I).grd_schm_version_number||' '||'Status:'||p_tab_usec_gs(I).status);
5885: p_tab_usec_gs(I).status := 'A';
5886: END IF;
5887:
5888: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5889: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.status_after_check_import_allowed',
5890: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5891: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5892: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5893: ||p_tab_usec_gs(I).grd_schm_version_number||' '||'Status:'||p_tab_usec_gs(I).status);
5903: END IF;
5904: END IF;
5905:
5906: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5907: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.count_unique_uoo_ids',
5908: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5909: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5910: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5911: ||p_tab_usec_gs(I).grd_schm_version_number||' '||'Count:'||l_tbl_uoo.count);
5914: IF p_tab_usec_gs(I).status = 'S' THEN
5915: validate_db_cons ( p_tab_usec_gs(I),l_insert_update );
5916:
5917: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5918: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.status_after_validate_db_cons',
5919: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5920: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5921: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5922: ||p_tab_usec_gs(I).grd_schm_version_number||' '||'Status:'||p_tab_usec_gs(I).status);
5931: IF p_tab_usec_gs(I).status = 'S' THEN
5932: igs_ps_validate_lgcy_pkg.validate_usec_grd_sch ( p_tab_usec_gs(I),l_n_uoo_id );
5933:
5934: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5935: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.status_after_Business_validation',
5936: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5937: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5938: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5939: ||p_tab_usec_gs(I).grd_schm_version_number||' '||'Status:'||p_tab_usec_gs(I).status);
5969: g_n_login_id
5970: );
5971:
5972: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5973: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.record_inserted',
5974: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5975: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5976: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5977: ||p_tab_usec_gs(I).grd_schm_version_number);
5989: AND grd_schm_version_number = p_tab_usec_gs(I).grd_schm_version_number
5990: AND uoo_id = l_n_uoo_id;
5991:
5992: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5993: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.record_updated',
5994: 'Unit code:'||p_tab_usec_gs(I).unit_cd||' '||'Version number:'||p_tab_usec_gs(I).version_number||' '||'teach_cal_alternate_code:'
5995: ||p_tab_usec_gs(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_gs(I).location_cd||' '||'Unit Class:'||
5996: p_tab_usec_gs(I).unit_class||' '||'Grading Schema Code:'||p_tab_usec_gs(I).grading_schema_code||' '||'Grading Schema Version Number:'
5997: ||p_tab_usec_gs(I).grd_schm_version_number);
6024: END IF;
6025: l_tbl_uoo.DELETE;
6026:
6027: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6028: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_grd_sch.after_import_status',p_c_rec_status);
6029: END IF;
6030:
6031:
6032: END create_usec_grd_sch;
6052: For PSP Scheduling Enhancements TD:
6053: Modified the local procedure's validate_derivation,validate_db_cons.
6054: ********************************************************************************************** */
6055:
6056: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
6057: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
6058: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
6059: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
6060: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
6053: Modified the local procedure's validate_derivation,validate_db_cons.
6054: ********************************************************************************************** */
6055:
6056: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
6057: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
6058: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
6059: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
6060: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
6061: l_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE;
6056: l_c_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE;
6057: l_n_seq_num igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE;
6058: l_d_start_dt igs_ca_inst_all.start_dt%TYPE;
6059: l_d_end_dt igs_ca_inst_all.end_dt%TYPE;
6060: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
6061: l_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE;
6062: l_n_usec_occurs_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE;
6063:
6064: l_n_building_code igs_ps_usec_occurs_all.building_code%TYPE;
6382:
6383: /* Validate FK Constraints */
6384:
6385: -- Check for the existence of Unit Section
6386: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation ( l_n_uoo_id ) THEN
6387: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
6388: p_uso_rec.status := 'E';
6389: END IF;
6390:
6862: /* Main Unit Section Occurrence Sub Process */
6863: BEGIN
6864:
6865: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6866: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.start_logging_for','Unit Section Occurrence');
6867: END IF;
6868:
6869: p_c_rec_status := 'S';
6870: FOR I in 1..p_tab_usec_occur.LAST LOOP
6891: trim_values(p_tab_usec_occur(I) );
6892: validate_parameters ( p_tab_usec_occur(I) );
6893:
6894: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6895: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.status_after_validate_parameters',
6896: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
6897: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
6898: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier||' '||
6899: 'Status:'||p_tab_usec_occur(I).status);
6903: IF p_tab_usec_occur(I).status = 'S' THEN
6904: validate_derivations ( p_tab_usec_occur(I));
6905:
6906: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6907: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.status_after_validate_derivations',
6908: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
6909: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
6910: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier||' '||
6911: 'Status:'||p_tab_usec_occur(I).status);
6918: IF p_tab_usec_occur(I).status = 'S' AND p_calling_context IN ('G','S') THEN
6919: l_insert_update:= check_insert_update(p_tab_usec_occur(I));
6920:
6921: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6922: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.status_after_check_insert_update',
6923: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
6924: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
6925: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier||' '||
6926: 'Status:'||p_tab_usec_occur(I).status);
6936: p_tab_usec_occur(I).status := 'A';
6937: END IF;
6938:
6939: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6940: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.status_after_check_import_allowed',
6941: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
6942: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
6943: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier||' '||
6944: 'Status:'||p_tab_usec_occur(I).status);
6950: IF p_tab_usec_occur(I).status = 'S' THEN
6951: assign_defaults(p_tab_usec_occur(I),l_insert_update);
6952:
6953: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6954: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.status_after_assign_defaults',
6955: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
6956: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
6957: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier||' '||
6958: 'Status:'||p_tab_usec_occur(I).status);
6963: IF p_tab_usec_occur(I).status = 'S' THEN
6964: validate_db_cons ( p_tab_usec_occur(I),l_insert_update);
6965:
6966: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6967: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.status_after_validate_db_cons',
6968: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
6969: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
6970: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier||' '||
6971: 'Status:'||p_tab_usec_occur(I).status);
6983: l_insert_update,p_calling_context,l_notify_status,l_c_schedule_status
6984: );
6985:
6986: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6987: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.status_after_Business_validation',
6988: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
6989: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
6990: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier||' '||
6991: 'Status:'||p_tab_usec_occur(I).status);
7111: l_tbl_uso(l_n_tbl_cnt):=l_n_usec_occurs_id ;
7112:
7113:
7114: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7115: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.record_inserted',
7116: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
7117: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
7118: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier);
7119: END IF;
7278: no_set_day_ind=p_tab_usec_occur(I).no_set_day_ind
7279: WHERE unit_section_occurrence_id = l_n_uso_id ;
7280:
7281: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7282: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.record_updated',
7283: 'Unit code:'||p_tab_usec_occur(I).unit_cd||' '||'Version number:'||p_tab_usec_occur(I).version_number||' '||'teach_cal_alternate_code:'
7284: ||p_tab_usec_occur(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_usec_occur(I).location_cd||' '||'Unit Class:'||
7285: p_tab_usec_occur(I).unit_class||'Occurrence Identifier:'|| p_tab_usec_occur(I).occurrence_identifier);
7286: END IF;
7303: END IF;
7304: END LOOP;
7305:
7306: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
7307: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_usec_occur.after_import_status',p_c_rec_status);
7308: END IF;
7309:
7310: END create_usec_occur;
7311:
7337:
7338:
7339: l_c_cal_type igs_ca_inst_all.cal_type%TYPE; -- Holds Calendar Type
7340: l_n_seq_num igs_ca_inst_all.sequence_number%TYPE; -- Holds Calendar Instance Sequence Number
7341: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE; -- Holds Unit Offering Options Identifier
7342: l_n_usec_ref_id igs_ps_usec_ref.unit_section_reference_id%TYPE; -- Holds Unit Section Reference Identifier
7343: l_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE; -- Holds the Unit Section Occurrence Identifier
7344:
7345: --
7417: p_ref_cd_rec.status := 'E';
7418: END IF;
7419:
7420: IF p_calling_context IN ('G','S') AND p_ref_cd_rec.data_type = 'UNIT' THEN
7421: fnd_message.set_name ( 'IGS', 'IGS_PS_UNIT_REF_N_ALLW_GEN_SCH' );
7422: fnd_msg_pub.add;
7423: p_ref_cd_rec.status := 'E';
7424: END IF;
7425:
7445:
7446: -- Derivation of values required to create Unit Section Referrence.
7447: PROCEDURE validate_usec_derivations( p_ref_cd_rec IN OUT NOCOPY igs_ps_generic_pub.unit_ref_rec_type) AS
7448:
7449: CURSOR c_usec_ref_id (cp_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
7450: SELECT A.unit_section_reference_id
7451: FROM igs_ps_usec_ref A
7452: WHERE A.uoo_id = cp_uoo_id;
7453:
7600:
7601: BEGIN
7602: -- Unique Key Validation
7603: IF p_ref_cd_rec.gen_ref_flag = 'Y' THEN -- if generic reference code
7604: IF igs_ps_unit_ref_cd_pkg.get_uk_for_validation(p_ref_cd_rec.unit_cd,
7605: p_ref_cd_rec.version_number,
7606: p_ref_cd_rec.reference_cd_type) THEN
7607: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_EXISTS', 'UNIT_REFERENCE_CD','LEGACY_TOKENS', FALSE);
7608: p_ref_cd_rec.status := 'W';
7608: p_ref_cd_rec.status := 'W';
7609: RETURN;
7610: END IF;
7611: ELSE -- else Unit requirement reference code
7612: IF igs_ps_unitreqref_cd_pkg.get_uk_for_validation(p_ref_cd_rec.unit_cd,
7613: p_ref_cd_rec.version_number,
7614: p_ref_cd_rec.reference_cd_type,
7615: p_ref_cd_rec.reference_cd
7616: ) THEN
7622: END IF;
7623:
7624: -- Check Constraints: Unit Code Should be in Upper Case
7625: BEGIN
7626: igs_ps_unit_ref_cd_pkg.check_constraints('UNIT_CD', p_ref_cd_rec.unit_cd);
7627: EXCEPTION
7628: WHEN OTHERS THEN
7629: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'UNIT_CD','LEGACY_TOKENS', TRUE);
7630: p_ref_cd_rec.status := 'E';
7631: END;
7632:
7633: -- Check Constraints: Reference Code Should be in Upper Case
7634: BEGIN
7635: igs_ps_unit_ref_cd_pkg.check_constraints('REFERENCE_CD', p_ref_cd_rec.reference_cd);
7636: EXCEPTION
7637: WHEN OTHERS THEN
7638: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'REFERENCE_CD','LEGACY_TOKENS', TRUE);
7639: p_ref_cd_rec.status := 'E';
7640: END;
7641:
7642: -- Check Constraints: Reference Code Type Should be in Upper Case
7643: BEGIN
7644: igs_ps_unit_ref_cd_pkg.check_constraints('REFERENCE_CD_TYPE', p_ref_cd_rec.reference_cd_type);
7645: EXCEPTION
7646: WHEN OTHERS THEN
7647: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE', 'REFERENCE_CD_TYPE','LEGACY_TOKENS', TRUE);
7648: p_ref_cd_rec.status := 'E';
7665: END IF;
7666: CLOSE c_res_flag;
7667:
7668: -- Foreign Key Validations : Unit Version doesn't exists
7669: IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation(p_ref_cd_rec.unit_cd, p_ref_cd_rec.version_number) THEN
7670: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_VERSION','LEGACY_TOKENS', FALSE);
7671: p_ref_cd_rec.status := 'E';
7672: END IF;
7673:
7887:
7888: BEGIN
7889:
7890: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
7891: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.start_logging_for','Reference: Unit,Unit Sections,Unit section Occurrence');
7892: END IF;
7893:
7894: p_c_rec_status := 'S';
7895:
7910:
7911: validate_parameters(p_tab_ref_cd(i));
7912:
7913: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7914: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.status_after_validate_parameters',
7915: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
7916: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
7917: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
7918: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
7924: IF p_tab_ref_cd(i).status = 'S' THEN
7925: validate_derivation(p_tab_ref_cd(i));
7926:
7927: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7928: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.status_after_validate_derivation',
7929: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
7930: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
7931: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
7932: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
7941: IF p_tab_ref_cd(i).status = 'S' AND p_calling_context IN ('G','S') THEN
7942: l_insert_update:= check_insert_update(p_tab_ref_cd(I));
7943:
7944: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7945: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.status_after_check_insert_update',
7946: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
7947: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
7948: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
7949: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
7961: p_tab_ref_cd(i).status := 'A';
7962: END IF;
7963:
7964: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7965: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.status_after_check_import_allowed',
7966: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
7967: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
7968: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
7969: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
7977: IF p_tab_ref_cd(i).status = 'S' THEN
7978: validate_db_cons(p_tab_ref_cd(i),l_insert_update);
7979:
7980: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7981: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.status_after_validate_db_cons',
7982: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
7983: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
7984: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
7985: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
7992: IF p_tab_ref_cd(i).status = 'S' THEN
7993: igs_ps_validate_lgcy_pkg.validate_unit_reference(p_tab_ref_cd(i),l_n_uoo_id,l_n_uso_id,p_calling_context);
7994:
7995: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
7996: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.status_after_Business_Validation',
7997: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
7998: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
7999: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
8000: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
8008: IF p_tab_ref_cd(i).status = 'S' THEN
8009: IF p_tab_ref_cd(i).data_type = 'UNIT' THEN
8010: IF p_tab_ref_cd(i).gen_ref_flag = 'Y' THEN -- if generic reference code
8011: -- Creating the unit refernce code if the datatype is UNIT.
8012: INSERT INTO igs_ps_unit_ref_cd (
8013: UNIT_CD,
8014: VERSION_NUMBER,
8015: REFERENCE_CD_TYPE,
8016: REFERENCE_CD,
8032: g_n_user_id,
8033: g_n_login_id
8034: );
8035: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8036: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Inserted_igs_ps_unit_ref_cd',
8037: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
8038: ||p_tab_ref_cd(i).version_number||' '||'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||
8039: 'reference_cd:'||p_tab_ref_cd(i).reference_cd||' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
8040: END IF;
8038: ||p_tab_ref_cd(i).version_number||' '||'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||
8039: 'reference_cd:'||p_tab_ref_cd(i).reference_cd||' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
8040: END IF;
8041: ELSE -- else unit requirements reference code
8042: INSERT INTO igs_ps_unitreqref_cd (
8043: unit_req_ref_cd_id,
8044: unit_cd,
8045: version_number,
8046: reference_cd_type,
8051: last_update_login,
8052: reference_code,
8053: reference_code_desc
8054: ) VALUES (
8055: igs_ps_unitreqref_cd_s.NEXTVAL,
8056: p_tab_ref_cd(i).unit_cd,
8057: p_tab_ref_cd(i).version_number,
8058: p_tab_ref_cd(i).reference_cd_type,
8059: SYSDATE,
8064: p_tab_ref_cd(i).reference_cd,
8065: p_tab_ref_cd(i).description
8066: );
8067: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8068: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Inserted_igs_ps_unitreqref_cd',
8069: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
8070: ||p_tab_ref_cd(i).version_number||' '||'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||
8071: 'reference_cd:'||p_tab_ref_cd(i).reference_cd||' '||'gen_ref_flag:'||p_tab_ref_cd(i).gen_ref_flag);
8072: END IF;
8108: p_tab_ref_cd(i).reference_cd,
8109: p_tab_ref_cd(i).description
8110: );
8111: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8112: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Inserted_igs_ps_usec_ref_cd',
8113: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
8114: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
8115: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
8116: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
8122: last_update_date=SYSDATE WHERE unit_section_reference_id=l_n_usec_ref_id AND
8123: reference_code_type=p_tab_ref_cd(i).reference_cd_type AND reference_code=p_tab_ref_cd(i).reference_cd;
8124:
8125: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8126: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Updated_igs_ps_usec_ref_cd',
8127: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
8128: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
8129: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
8130: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
8158: p_tab_ref_cd(i).reference_cd,
8159: p_tab_ref_cd(i).description
8160: );
8161: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8162: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_Inserted_igs_ps_us_req_ref_cd',
8163: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
8164: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
8165: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
8166: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
8172: last_update_date=SYSDATE WHERE unit_section_reference_id=l_n_usec_ref_id AND
8173: reference_cd_type=p_tab_ref_cd(i).reference_cd_type AND reference_code=p_tab_ref_cd(i).reference_cd;
8174:
8175: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8176: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_updated_igs_ps_us_req_ref_cd',
8177: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
8178: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
8179: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
8180: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
8216: g_n_login_id,
8217: p_tab_ref_cd(i).description
8218: );
8219: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8220: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_inserted_igs_ps_usec_ocur_ref',
8221: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
8222: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
8223: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
8224: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
8231: last_update_date=SYSDATE WHERE unit_section_occurrence_id=l_n_uso_id AND
8232: reference_code_type=p_tab_ref_cd(i).reference_cd_type AND reference_code=p_tab_ref_cd(i).reference_cd;
8233:
8234: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8235: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.Record_updated_igs_ps_usec_ocur_ref',
8236: 'Data Type:'||p_tab_ref_cd(i).data_type||' '||'Unit code:'||p_tab_ref_cd(i).unit_cd||' '||'Version number:'
8237: ||p_tab_ref_cd(i).version_number||' '||'teach_cal_alternate_code:'||p_tab_ref_cd(i).teach_cal_alternate_code
8238: ||' '||'Location_cd:'||p_tab_ref_cd(i).location_cd||' '||'Unit Class:'||p_tab_ref_cd(i).unit_class||' '||
8239: 'reference_cd_type:'||p_tab_ref_cd(i).reference_cd_type||' '||'reference_cd:'||p_tab_ref_cd(i).reference_cd||
8259: END IF; -- end of exist(i) if
8260: END LOOP;
8261:
8262: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
8263: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_unit_ref_code.after_import_status',p_c_rec_status);
8264: END IF;
8265:
8266:
8267: END create_unit_ref_code;
8290: ***********************************************************************************************/
8291:
8292: l_n_ins_id igs_ps_uso_instrctrs.instructor_id%TYPE;
8293: l_n_uso_id igs_ps_uso_instrctrs.unit_section_occurrence_id%TYPE;
8294: l_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE;
8295: l_d_start_dt igs_ps_usec_occurs_all.start_date%TYPE;
8296: l_d_end_dt igs_ps_usec_occurs_all.end_date%TYPE;
8297:
8298: CURSOR c_occurs(cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
8503:
8504: PROCEDURE validate_db_cons(p_uso_ins_rec IN OUT NOCOPY igs_ps_generic_pub.uso_ins_rec_type) AS
8505: CURSOR c_unit_ver (cp_n_uso_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
8506: SELECT a.unit_cd, a.version_number
8507: FROM igs_ps_unit_ofr_opt_all a, igs_ps_usec_occurs_all b
8508: WHERE a.uoo_id = b.uoo_id
8509: AND b.unit_section_occurrence_id = cp_n_uso_id;
8510:
8511: rec_unit_ver c_unit_ver%ROWTYPE;
8527: END IF;
8528:
8529: -- Check Constraints
8530: BEGIN
8531: igs_ps_unit_ver_pkg.check_constraints( 'UNIT_CD',p_uso_ins_rec.unit_cd);
8532: EXCEPTION
8533: WHEN OTHERS THEN
8534: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_UPPER_CASE','UNIT_CD','LEGACY_TOKENS',TRUE);
8535: p_uso_ins_rec.status :='E';
8535: p_uso_ins_rec.status :='E';
8536: END;
8537:
8538: BEGIN
8539: igs_ps_unit_ver_pkg.check_constraints('VERSION_NUMBER',p_uso_ins_rec.version_number);
8540: EXCEPTION
8541: WHEN OTHERS THEN
8542: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_VER_NUM_1_999',NULL,NULL,TRUE);
8543: p_uso_ins_rec.status :='E';
8548: fnd_message.set_name('IGS','IGS_PS_INS_PERSON_NUMBER');
8549: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', fnd_message.get, NULL, FALSE);
8550: p_uso_ins_rec.status := 'E';
8551: END IF;
8552: IF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation (l_n_uoo_id ) THEN
8553: igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_REC_NOT_EXISTS', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
8554: p_uso_ins_rec.status := 'E';
8555: END IF;
8556:
8591:
8592: BEGIN
8593:
8594: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
8595: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_uso_ins.start_logging_for','Unit Section Occurrence Instructors');
8596: END IF;
8597:
8598: p_c_rec_status := 'S';
8599: FOR I in 1..p_tab_uso_ins.LAST LOOP
8603: trim_values(p_tab_uso_ins(I));
8604: validate_parameters(p_tab_uso_ins(I));
8605:
8606: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8607: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_uso_ins.status_after_validate_parameters',
8608: 'Unit code:'||p_tab_uso_ins(I).unit_cd||' '||'Version number:'||p_tab_uso_ins(I).version_number||' '||
8609: 'teach_cal_alternate_code:'||p_tab_uso_ins(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_uso_ins(I).location_cd
8610: ||' '||'Unit Class:'||p_tab_uso_ins(I).unit_class||' '||'instructor_person_number:'||p_tab_uso_ins(I).instructor_person_number
8611: ||' '||'occurrence_identifier'||p_tab_uso_ins(I).occurrence_identifier
8615: IF p_tab_uso_ins(I).status = 'S' THEN
8616: validate_derivation(p_tab_uso_ins(I));
8617:
8618: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8619: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_uso_ins.status_after_validate_derivation',
8620: 'Unit code:'||p_tab_uso_ins(I).unit_cd||' '||'Version number:'||p_tab_uso_ins(I).version_number||' '||
8621: 'teach_cal_alternate_code:'||p_tab_uso_ins(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_uso_ins(I).location_cd
8622: ||' '||'Unit Class:'||p_tab_uso_ins(I).unit_class||' '||'instructor_person_number:'||p_tab_uso_ins(I).instructor_person_number
8623: ||' '||'occurrence_identifier'||p_tab_uso_ins(I).occurrence_identifier
8628:
8629: IF p_tab_uso_ins(I).status = 'S' THEN
8630: validate_db_cons ( p_tab_uso_ins(I) );
8631: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8632: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_uso_ins.status_after_validate_db_cons',
8633: 'Unit code:'||p_tab_uso_ins(I).unit_cd||' '||'Version number:'||p_tab_uso_ins(I).version_number||' '||
8634: 'teach_cal_alternate_code:'||p_tab_uso_ins(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_uso_ins(I).location_cd
8635: ||' '||'Unit Class:'||p_tab_uso_ins(I).unit_class||' '||'instructor_person_number:'||p_tab_uso_ins(I).instructor_person_number
8636: ||' '||'occurrence_identifier'||p_tab_uso_ins(I).occurrence_identifier
8657: sysdate,
8658: g_n_login_id
8659: );
8660: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8661: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_uso_ins.record_inserted',
8662: 'Unit code:'||p_tab_uso_ins(I).unit_cd||' '||'Version number:'||p_tab_uso_ins(I).version_number||' '||
8663: 'teach_cal_alternate_code:'||p_tab_uso_ins(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_uso_ins(I).location_cd
8664: ||' '||'Unit Class:'||p_tab_uso_ins(I).unit_class||' '||'instructor_person_number:'||p_tab_uso_ins(I).instructor_person_number
8665: ||' '||'occurrence_identifier'||p_tab_uso_ins(I).occurrence_identifier
8669:
8670: IF p_tab_uso_ins(I).status = 'S' THEN
8671: igs_ps_validate_lgcy_pkg.post_uso_ins(l_n_ins_id, l_n_uoo_id, p_tab_uso_ins(I),I);
8672: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
8673: fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_uso_ins.status_after_post_uso_ins',
8674: 'Unit code:'||p_tab_uso_ins(I).unit_cd||' '||'Version number:'||p_tab_uso_ins(I).version_number||' '||
8675: 'teach_cal_alternate_code:'||p_tab_uso_ins(I).teach_cal_alternate_code||' '||'Location_cd:'||p_tab_uso_ins(I).location_cd
8676: ||' '||'Unit Class:'||p_tab_uso_ins(I).unit_class||' '||'instructor_person_number:'||p_tab_uso_ins(I).instructor_person_number
8677: ||' '||'occurrence_identifier'||p_tab_uso_ins(I).occurrence_identifier
8707: p_c_rec_status := 'E';
8708: END IF;
8709:
8710: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
8711: fnd_log.string( fnd_log.level_procedure, 'igs.plsql.igs_ps_unit_lgcy_pkg.create_uso_ins.after_import_status',p_c_rec_status);
8712: END IF;
8713:
8714: l_tbl_uso.DELETE;
8715:
8715:
8716: END create_uso_ins;
8717:
8718:
8719: END igs_ps_unit_lgcy_pkg;