[Home] [Help]
1: PACKAGE BODY IGS_CA_INST_PKG AS
2: /* $Header: IGSCI12B.pls 120.0 2005/06/01 22:19:16 appldev noship $ */
3:
4: l_rowid VARCHAR2(25);
5: old_references IGS_CA_INST_ALL%ROWTYPE;
1: PACKAGE BODY IGS_CA_INST_PKG AS
2: /* $Header: IGSCI12B.pls 120.0 2005/06/01 22:19:16 appldev noship $ */
3:
4: l_rowid VARCHAR2(25);
5: old_references IGS_CA_INST_ALL%ROWTYPE;
6: new_references IGS_CA_INST_ALL%ROWTYPE;
7:
8: -- Forward declaring the procedure beforerowdelete, beforerowupdate
9: PROCEDURE beforerowdelete;
2: /* $Header: IGSCI12B.pls 120.0 2005/06/01 22:19:16 appldev noship $ */
3:
4: l_rowid VARCHAR2(25);
5: old_references IGS_CA_INST_ALL%ROWTYPE;
6: new_references IGS_CA_INST_ALL%ROWTYPE;
7:
8: -- Forward declaring the procedure beforerowdelete, beforerowupdate
9: PROCEDURE beforerowdelete;
10: PROCEDURE beforerowupdate;
36: ) AS
37:
38: CURSOR cur_old_ref_values IS
39: SELECT *
40: FROM IGS_CA_INST_ALL
41: WHERE ROWID = x_rowid;
42:
43: BEGIN
44:
111: -- CURSOR added to check uniqueness for alternate code for calendar categories
112: -- load , academic and teaching
113: CURSOR alt_code_unique IS
114: SELECT count(*)
115: FROM IGS_CA_INST CI , IGS_CA_TYPE CAT
116: WHERE CAT.CAL_TYPE = CI.CAL_TYPE
117: AND CAT.S_CAL_CAT IN ('LOAD','TEACHING','ACADEMIC')
118: AND NEW_REFERENCES.ALTERNATE_CODE = CI.ALTERNATE_CODE
119: AND ((l_rowid IS NULL) OR (CI.ROW_ID <> l_rowid)) ;
344: (old_references.prior_ci_sequence_number = new_references.prior_ci_sequence_number)) OR
345: ((new_references.cal_type IS NULL) OR
346: (new_references.prior_ci_sequence_number IS NULL))) THEN
347: NULL;
348: ELSIF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
349: new_references.cal_type,
350: new_references.prior_ci_sequence_number
351: ) THEN
352: Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
360: ------------------------------------------------------------------
361: --Change History:
362: --Who When What
363: --rmaddipa 14-sep-2004 Enh # 3316063 Reverted back the changes made in the earlier version of the file.
364: --rmaddipa 07-Sep-2004 Enh # 3316063 Added call to igs_fi_tp_ret_schd_pkg.get_fk_igs_ca_inst
365: --smvk 26-Aug-03 Enh # 3045007, Added igs_fi_pp_std_attrs_pkg
366: --shtatiko 10-JUN-2003 Enh# 2831582, Added call to igs_fi_lb_fcis_pkg
367: --sbaliga 18-Apr-2002 Bug 2278825, modified check child
368: --vchappid 02-Apr-2002 Enh# bug2293676, modified check child
368: --vchappid 02-Apr-2002 Enh# bug2293676, modified check child
369: --schodava 06-FEB-2002 Enh # 2187247
370: -- SFCR021 : FCI-LCI Relation
371: -- Removed the references to igs_fi_chg_mth_app_pkg
372: --smvk 04-feb-2002 added igs_fi_credits_pkg.get_fk_igs_ca_inst_1
373: -- added igs_fi_credits_pkg.get_fk_igs_ca_inst_2
374: -- call
375: --smadathi 04-feb-2002 added igf_sp_stdnt_rel_pkg.get_fk_igs_ca_inst
376: -- call
369: --schodava 06-FEB-2002 Enh # 2187247
370: -- SFCR021 : FCI-LCI Relation
371: -- Removed the references to igs_fi_chg_mth_app_pkg
372: --smvk 04-feb-2002 added igs_fi_credits_pkg.get_fk_igs_ca_inst_1
373: -- added igs_fi_credits_pkg.get_fk_igs_ca_inst_2
374: -- call
375: --smadathi 04-feb-2002 added igf_sp_stdnt_rel_pkg.get_fk_igs_ca_inst
376: -- call
377:
371: -- Removed the references to igs_fi_chg_mth_app_pkg
372: --smvk 04-feb-2002 added igs_fi_credits_pkg.get_fk_igs_ca_inst_1
373: -- added igs_fi_credits_pkg.get_fk_igs_ca_inst_2
374: -- call
375: --smadathi 04-feb-2002 added igf_sp_stdnt_rel_pkg.get_fk_igs_ca_inst
376: -- call
377:
378: --ckasu 04-Dec-2003 Added IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_CA_INST
379: -- for Term Records Build
374: -- call
375: --smadathi 04-feb-2002 added igf_sp_stdnt_rel_pkg.get_fk_igs_ca_inst
376: -- call
377:
378: --ckasu 04-Dec-2003 Added IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_CA_INST
379: -- for Term Records Build
380: -------------------------------------------------------------------
381:
382: BEGIN
382: BEGIN
383:
384:
385:
386: IGS_AD_PERD_AD_CAT_PKG.GET_UFK_IGS_CA_INST (
387: old_references.cal_type,
388: old_references.sequence_number,
389: old_references.start_dt,
390: old_references.end_dt
389: old_references.start_dt,
390: old_references.end_dt
391: );
392:
393: IGS_EN_SU_ATTEMPT_PKG.GET_UFK_IGS_CA_INST (
394: old_references.cal_type,
395: old_references.sequence_number,
396: old_references.start_dt,
397: old_references.end_dt
396: old_references.start_dt,
397: old_references.end_dt
398: );
399:
400: IGS_AS_SU_STMPTOUT_PKG.GET_UFK_IGS_CA_INST (
401: old_references.cal_type,
402: old_references.sequence_number,
403: old_references.start_dt,
404: old_references.end_dt
403: old_references.start_dt,
404: old_references.end_dt
405: );
406:
407: IGS_PS_OFR_INST_PKG.GET_UFK_IGS_CA_INST (
408: old_references.cal_type,
409: old_references.sequence_number,
410: old_references.start_dt,
411: old_references.end_dt
410: old_references.start_dt,
411: old_references.end_dt
412: );
413:
414: IGS_PS_UNIT_OFR_PAT_PKG.GET_UFK_IGS_CA_INST (
415: old_references.cal_type,
416: old_references.sequence_number,
417: old_references.start_dt,
418: old_references.end_dt
417: old_references.start_dt,
418: old_references.end_dt
419: );
420:
421: IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_CA_INST (
422: old_references.cal_type,
423: old_references.sequence_number
424: );
425:
422: old_references.cal_type,
423: old_references.sequence_number
424: );
425:
426: IGS_CA_INST_PKG.GET_FK_IGS_CA_INST (
427: old_references.cal_type,
428: old_references.sequence_number
429: );
430:
427: old_references.cal_type,
428: old_references.sequence_number
429: );
430:
431: IGS_CA_INST_REL_PKG.GET_FK_IGS_CA_INST (
432: old_references.cal_type,
433: old_references.sequence_number
434: );
435:
433: old_references.sequence_number
434: );
435:
436:
437: IGS_GR_CRMN_ROUND_PKG.GET_FK_IGS_CA_INST (
438: old_references.cal_type,
439: old_references.sequence_number
440: );
441:
438: old_references.cal_type,
439: old_references.sequence_number
440: );
441:
442: IGS_CO_ITM_PKG.GET_FK_IGS_CA_INST (
443: old_references.cal_type,
444: old_references.sequence_number
445: );
446:
443: old_references.cal_type,
444: old_references.sequence_number
445: );
446:
447: IGS_ST_DFT_LOAD_APPO_PKG.GET_FK_IGS_CA_INST (
448: old_references.cal_type,
449: old_references.sequence_number
450: );
451:
448: old_references.cal_type,
449: old_references.sequence_number
450: );
451:
452: IGS_CA_DA_INST_PKG.GET_FK_IGS_CA_INST (
453: old_references.cal_type,
454: old_references.sequence_number
455: );
456:
453: old_references.cal_type,
454: old_references.sequence_number
455: );
456:
457: IGS_AS_EXAM_SESSION_PKG.GET_FK_IGS_CA_INST (
458: old_references.cal_type,
459: old_references.sequence_number
460: );
461:
458: old_references.cal_type,
459: old_references.sequence_number
460: );
461:
462: IGS_FI_F_CAT_CA_INST_PKG.GET_FK_IGS_CA_INST (
463: old_references.cal_type,
464: old_references.sequence_number
465: );
466:
463: old_references.cal_type,
464: old_references.sequence_number
465: );
466:
467: IGS_FI_F_TYP_CA_INST_PKG.GET_FK_IGS_CA_INST (
468: old_references.cal_type,
469: old_references.sequence_number
470: );
471:
468: old_references.cal_type,
469: old_references.sequence_number
470: );
471:
472: IGS_ST_GVTSEMLOAD_CA_PKG.GET_FK_IGS_CA_INST (
473: old_references.cal_type,
474: old_references.sequence_number
475: );
476:
473: old_references.cal_type,
474: old_references.sequence_number
475: );
476:
477: IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_CA_INST (
478: old_references.cal_type,
479: old_references.sequence_number
480: );
481:
478: old_references.cal_type,
479: old_references.sequence_number
480: );
481:
482: /*IGS_CO_OU_CO_REF_PKG.GET_FK_IGS_CA_INST (
483: old_references.cal_type,
484: old_references.sequence_number
485: ); */
486:
483: old_references.cal_type,
484: old_references.sequence_number
485: ); */
486:
487: IGS_PS_PAT_OF_STUDY_PKG.GET_FK_IGS_CA_INST (
488: old_references.cal_type,
489: old_references.sequence_number
490: );
491:
488: old_references.cal_type,
489: old_references.sequence_number
490: );
491:
492: IGS_PR_RU_CA_TYPE_PKG.GET_FK_IGS_CA_INST (
493: old_references.cal_type,
494: old_references.sequence_number
495: );
496:
493: old_references.cal_type,
494: old_references.sequence_number
495: );
496:
497: IGS_AS_SC_ATMPT_ENR_PKG.GET_FK_IGS_CA_INST (
498: old_references.cal_type,
499: old_references.sequence_number
500: );
501:
498: old_references.cal_type,
499: old_references.sequence_number
500: );
501:
502: IGS_PR_SDT_PS_PR_MSR_PKG.GET_FK_IGS_CA_INST (
503: old_references.cal_type,
504: old_references.sequence_number
505: );
506:
503: old_references.cal_type,
504: old_references.sequence_number
505: );
506:
507: IGS_PR_STDNT_PR_CK_PKG.GET_FK_IGS_CA_INST (
508: old_references.cal_type,
509: old_references.sequence_number
510: );
511:
508: old_references.cal_type,
509: old_references.sequence_number
510: );
511:
512: IGS_AS_UNITASS_ITEM_PKG.GET_FK_IGS_CA_INST (
513: old_references.cal_type,
514: old_references.sequence_number
515: );
516:
513: old_references.cal_type,
514: old_references.sequence_number
515: );
516:
517: IGS_FI_UNIT_FEE_TRG_PKG.GET_FK_IGS_CA_INST (
518: old_references.cal_type,
519: old_references.sequence_number
520: );
521:
518: old_references.cal_type,
519: old_references.sequence_number
520: );
521:
522: IGS_EN_INST_WLST_OPT_PKG.GET_FK_IGS_CA_INST (
523: old_references.cal_type,
524: old_references.sequence_number
525: );
526:
523: old_references.cal_type,
524: old_references.sequence_number
525: );
526:
527: igs_ps_uso_cm_grp_pkg.get_fk_igs_ca_inst (
528: old_references.cal_type,
529: old_references.sequence_number
530: );
531:
528: old_references.cal_type,
529: old_references.sequence_number
530: );
531:
532: igs_ps_us_em_grp_pkg.get_fk_igs_ca_inst (
533: old_references.cal_type,
534: old_references.sequence_number
535: );
536:
533: old_references.cal_type,
534: old_references.sequence_number
535: );
536:
537: igs_ps_usec_x_grp_pkg.get_fk_igs_ca_inst (
538: old_references.cal_type,
539: old_references.sequence_number
540: );
541: igs_ps_unit_ver_pkg.get_fk_igs_ca_inst_all(
537: igs_ps_usec_x_grp_pkg.get_fk_igs_ca_inst (
538: old_references.cal_type,
539: old_references.sequence_number
540: );
541: igs_ps_unit_ver_pkg.get_fk_igs_ca_inst_all(
542: old_references.cal_type,
543: old_references.sequence_number
544: );
545: igs_ps_unit_ver_pkg.get_fk_igs_ca_inst_all1(
541: igs_ps_unit_ver_pkg.get_fk_igs_ca_inst_all(
542: old_references.cal_type,
543: old_references.sequence_number
544: );
545: igs_ps_unit_ver_pkg.get_fk_igs_ca_inst_all1(
546: old_references.cal_type,
547: old_references.sequence_number
548: );
549: igs_en_elgb_ovr_pkg.get_fk_igs_ca_inst(
545: igs_ps_unit_ver_pkg.get_fk_igs_ca_inst_all1(
546: old_references.cal_type,
547: old_references.sequence_number
548: );
549: igs_en_elgb_ovr_pkg.get_fk_igs_ca_inst(
550: old_references.cal_type,
551: old_references.sequence_number
552: );
553: -- Start of addition for Bug no. 1960126
550: old_references.cal_type,
551: old_references.sequence_number
552: );
553: -- Start of addition for Bug no. 1960126
554: igs_av_stnd_unit_pkg.get_fk_igs_ca_inst(
555: old_references.cal_type,
556: old_references.sequence_number
557: );
558:
555: old_references.cal_type,
556: old_references.sequence_number
557: );
558:
559: igs_av_stnd_unit_lvl_pkg.get_fk_igs_ca_inst(
560: old_references.cal_type,
561: old_references.sequence_number
562: );
563: -- End of addition for Bug no. 1960126
561: old_references.sequence_number
562: );
563: -- End of addition for Bug no. 1960126
564:
565: igs_pe_stat_details_pkg.get_fk_igs_ca_inst(
566: old_references.cal_type,
567: old_references.sequence_number
568: );
569:
566: old_references.cal_type,
567: old_references.sequence_number
568: );
569:
570: IGS_PE_PERS_ENCUMB_PKG.get_fk_igs_ca_inst(
571: old_references.cal_type,
572: old_references.sequence_number
573: );
574:
571: old_references.cal_type,
572: old_references.sequence_number
573: );
574:
575: IGS_EN_STDNT_PS_ATT_PKG.get_fk_igs_ca_inst(
576: old_references.cal_type,
577: old_references.sequence_number
578: );
579:
576: old_references.cal_type,
577: old_references.sequence_number
578: );
579:
580: IGS_AS_SU_SETATMPT_PKG.get_fk_igs_ca_inst(
581: old_references.cal_type,
582: old_references.sequence_number
583: );
584:
582: old_references.sequence_number
583: );
584:
585: --added by nalkumar, Bug:2126091
586: IGS_FI_PERSON_HOLDS_PKG.get_fk_igs_ca_inst(
587: old_references.cal_type,
588: old_references.sequence_number
589: );
590:
587: old_references.cal_type,
588: old_references.sequence_number
589: );
590:
591: IGS_AS_ANON_ID_US_PKG.get_fk_igs_ca_inst(
592: old_references.cal_type,
593: old_references.sequence_number
594: );
595:
592: old_references.cal_type,
593: old_references.sequence_number
594: );
595:
596: IGS_AS_ANON_ID_ASS_PKG.get_fk_igs_ca_inst(
597: old_references.cal_type,
598: old_references.sequence_number
599: );
600:
597: old_references.cal_type,
598: old_references.sequence_number
599: );
600:
601: IGS_PS_FAC_WL_PKG.get_fk_igs_ca_inst(
602: old_references.cal_type,
603: old_references.sequence_number
604: );
605:
602: old_references.cal_type,
603: old_references.sequence_number
604: );
605:
606: igf_sp_stdnt_rel_pkg.get_fk_igs_ca_inst(
607: old_references.cal_type,
608: old_references.sequence_number
609: );
610: -- ADDED FOR 2191470 STARTS HERE
607: old_references.cal_type,
608: old_references.sequence_number
609: );
610: -- ADDED FOR 2191470 STARTS HERE
611: igs_fi_credits_pkg.get_fk_igs_ca_inst_1(
612: old_references.cal_type,
613: old_references.sequence_number
614: );
615:
612: old_references.cal_type,
613: old_references.sequence_number
614: );
615:
616: igs_fi_credits_pkg.get_fk_igs_ca_inst_2(
617: old_references.cal_type,
618: old_references.sequence_number
619: );
620: -- ADDED FOR 2191470 ENDS HERE
618: old_references.sequence_number
619: );
620: -- ADDED FOR 2191470 ENDS HERE
621:
622: igs_fi_bill_pln_crd_pkg.get_fk_igs_ca_inst(
623: old_references.cal_type,
624: old_references.sequence_number
625: );
626:
623: old_references.cal_type,
624: old_references.sequence_number
625: );
626:
627: igs_he_fte_cal_prd_pkg.get_fk_igs_ca_inst1(
628: old_references.cal_type,
629: old_references.sequence_number
630: );
631:
628: old_references.cal_type,
629: old_references.sequence_number
630: );
631:
632: igs_he_fte_cal_prd_pkg.get_fk_igs_ca_inst2(
633: old_references.cal_type,
634: old_references.sequence_number
635: );
636:
633: old_references.cal_type,
634: old_references.sequence_number
635: );
636:
637: igs_pr_stu_acad_stat_pkg.get_fk_igs_ca_inst(
638: old_references.cal_type,
639: old_references.sequence_number
640: );
641:
638: old_references.cal_type,
639: old_references.sequence_number
640: );
641:
642: igs_pr_cohort_inst_pkg.get_fk_igs_ca_inst(
643: old_references.cal_type,
644: old_references.sequence_number
645: );
646: -- DA UI 2829285 addition
645: );
646: -- DA UI 2829285 addition
647: -- commented code for resolving depndency in the bug 2981279
648:
649: igs_da_cnfg_req_typ_pkg.get_fk_igs_ca_inst (
650: x_cal_type => old_references.cal_type,
651: x_sequence_number => old_references.sequence_number
652: );
653: igs_da_req_wif_pkg.get_fk_igs_ca_inst (
649: igs_da_cnfg_req_typ_pkg.get_fk_igs_ca_inst (
650: x_cal_type => old_references.cal_type,
651: x_sequence_number => old_references.sequence_number
652: );
653: igs_da_req_wif_pkg.get_fk_igs_ca_inst (
654: x_cal_type => old_references.cal_type,
655: x_sequence_number => old_references.sequence_number
656: );
657: -- DA UI 2829285 addition ends
656: );
657: -- DA UI 2829285 addition ends
658:
659: -- Enh# 2831582, Added the following call.
660: igs_fi_lb_fcis_pkg.get_fk_igs_ca_inst(
661: old_references.cal_type,
662: old_references.sequence_number
663: );
664:
662: old_references.sequence_number
663: );
664:
665: -- Enh # 3045007
666: igs_fi_pp_std_attrs_pkg.get_fk_igs_ca_inst (
667: x_cal_type => old_references.cal_type,
668: x_sequence_number => old_references.sequence_number
669: );
670:
667: x_cal_type => old_references.cal_type,
668: x_sequence_number => old_references.sequence_number
669: );
670:
671: igs_en_psv_term_it_pkg.get_fk_igs_ca_inst (
672: x_cal_type => old_references.cal_type,
673: x_sequence_number => old_references.sequence_number
674: );
675:
686: (old_references.START_DT IS NULL)AND
687: (old_references.END_DT IS NULL))) THEN
688: NULL;
689: ELSE
690: IGS_AD_PERD_AD_CAT_PKG.GET_UFK_IGS_CA_INST(
691: old_references.CAL_TYPE,
692: old_references.SEQUENCE_NUMBER,
693: old_references.START_DT,
694: old_references.END_DT);
691: old_references.CAL_TYPE,
692: old_references.SEQUENCE_NUMBER,
693: old_references.START_DT,
694: old_references.END_DT);
695: IGS_PS_OFR_INST_PKG.GET_UFK_IGS_CA_INST(
696: old_references.CAL_TYPE,
697: old_references.SEQUENCE_NUMBER,
698: old_references.START_DT,
699: old_references.END_DT);
696: old_references.CAL_TYPE,
697: old_references.SEQUENCE_NUMBER,
698: old_references.START_DT,
699: old_references.END_DT);
700: IGS_AS_SU_STMPTOUT_PKG.GET_UFK_IGS_CA_INST(
701: old_references.CAL_TYPE,
702: old_references.SEQUENCE_NUMBER,
703: old_references.START_DT,
704: old_references.END_DT);
701: old_references.CAL_TYPE,
702: old_references.SEQUENCE_NUMBER,
703: old_references.START_DT,
704: old_references.END_DT);
705: IGS_EN_SU_ATTEMPT_PKG.GET_UFK_IGS_CA_INST(
706: old_references.CAL_TYPE,
707: old_references.SEQUENCE_NUMBER,
708: old_references.START_DT,
709: old_references.END_DT);
706: old_references.CAL_TYPE,
707: old_references.SEQUENCE_NUMBER,
708: old_references.START_DT,
709: old_references.END_DT);
710: IGS_PS_UNIT_OFR_PAT_PKG.GET_UFK_IGS_CA_INST(
711: old_references.CAL_TYPE,
712: old_references.SEQUENCE_NUMBER,
713: old_references.START_DT,
714: old_references.END_DT);
728: CURSOR cur_get_status
729: IS
730: SELECT s.s_cal_status
731: FROM igs_ca_stat s,
732: igs_ca_inst_all ci
733: WHERE ci.cal_status = s.cal_status
734: AND ci.cal_type = x_cal_type
735: AND ci.sequence_number = x_sequence_number;
736: l_s_cal_status igs_ca_stat.s_cal_status%TYPE;
736: l_s_cal_status igs_ca_stat.s_cal_status%TYPE;
737:
738: CURSOR cur_rowid_planned IS
739: SELECT ROWID
740: FROM igs_ca_inst_all
741: WHERE cal_type = x_cal_type
742: AND sequence_number = x_sequence_number
743: FOR UPDATE NOWAIT;
744:
743: FOR UPDATE NOWAIT;
744:
745: CURSOR cur_rowid_act_inact IS
746: SELECT ROWID
747: FROM igs_ca_inst_all
748: WHERE cal_type = x_cal_type
749: AND sequence_number = x_sequence_number;
750:
751: lv_rowid cur_rowid_planned%ROWTYPE;
792: )RETURN BOOLEAN AS
793:
794: CURSOR cur_rowid IS
795: SELECT ROWID
796: FROM IGS_CA_INST_ALL
797: WHERE cal_type = x_cal_type
798: AND sequence_number = x_sequence_number
799: AND start_dt = x_start_dt
800: AND end_dt = x_end_dt
823: )RETURN BOOLEAN AS
824:
825: CURSOR cur_rowid IS
826: SELECT ROWID
827: FROM IGS_CA_INST_ALL
828: WHERE cal_type = x_cal_type
829: AND start_dt = x_start_dt
830: AND end_dt = x_end_dt
831: AND ((l_rowid IS NULL) OR (ROWID <> l_rowid));
851: ) AS
852:
853: CURSOR cur_rowid IS
854: SELECT ROWID
855: FROM IGS_CA_INST_ALL
856: WHERE cal_type = x_cal_type ;
857:
858: lv_rowid cur_rowid%ROWTYPE;
859:
877: ) AS
878:
879: CURSOR cur_rowid IS
880: SELECT ROWID
881: FROM IGS_CA_INST_ALL
882: WHERE cal_status = x_cal_status ;
883:
884: lv_rowid cur_rowid%ROWTYPE;
885:
897: CLOSE cur_rowid;
898:
899: END GET_FK_IGS_CA_STAT;
900:
901: PROCEDURE GET_FK_IGS_CA_INST (
902: x_cal_type IN VARCHAR2,
903: x_sequence_number IN NUMBER
904: ) AS
905:
904: ) AS
905:
906: CURSOR cur_rowid IS
907: SELECT ROWID
908: FROM IGS_CA_INST_ALL
909: WHERE cal_type = x_cal_type
910: AND prior_ci_sequence_number = x_sequence_number ;
911:
912: lv_rowid cur_rowid%ROWTYPE;
923: RETURN;
924: END IF;
925: CLOSE cur_rowid;
926:
927: END GET_FK_IGS_CA_INST;
928:
929: PROCEDURE Before_DML (
930: p_action IN VARCHAR2,
931: x_rowid IN VARCHAR2 ,
1067: X_PLANNING_FLAG IN VARCHAR2,
1068: X_SCHEDULE_FLAG IN VARCHAR2,
1069: X_ADMIN_FLAG IN VARCHAR2
1070: ) AS
1071: CURSOR C IS SELECT ROWID FROM IGS_CA_INST_ALL
1072: WHERE CAL_TYPE = X_CAL_TYPE
1073: AND SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
1074: X_LAST_UPDATE_DATE DATE;
1075: X_LAST_UPDATED_BY NUMBER;
1118: X_SCHEDULE_FLAG => X_SCHEDULE_FLAG,
1119: X_ADMIN_FLAG => X_ADMIN_FLAG
1120:
1121: );
1122: INSERT INTO IGS_CA_INST_ALL (
1123: CAL_TYPE,
1124: SEQUENCE_NUMBER,
1125: START_DT,
1126: END_DT,
1208: TERM_INSTRUCTION_TIME,
1209: PLANNING_FLAG,
1210: SCHEDULE_FLAG,
1211: ADMIN_FLAG
1212: FROM IGS_CA_INST_ALL
1213: WHERE ROWID=X_ROWID
1214: FOR UPDATE NOWAIT;
1215: tlinfo c1%ROWTYPE;
1216:
1358: l_msg :=LTRIM(RTRIM(x_description));
1359:
1360: END IF;
1361:
1362: UPDATE IGS_CA_INST_ALL SET
1363: START_DT = NEW_REFERENCES.START_DT,
1364: END_DT = NEW_REFERENCES.END_DT,
1365: CAL_STATUS = NEW_REFERENCES.CAL_STATUS,
1366: ALTERNATE_CODE = NEW_REFERENCES.ALTERNATE_CODE,
1406: X_PLANNING_FLAG IN VARCHAR2,
1407: X_SCHEDULE_FLAG IN VARCHAR2,
1408: X_ADMIN_FLAG IN VARCHAR2
1409: ) AS
1410: CURSOR c1 IS SELECT ROWID FROM IGS_CA_INST_ALL
1411: WHERE CAL_TYPE = X_CAL_TYPE
1412: AND SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1413: ;
1414: BEGIN
1467: Before_DML(
1468: p_action =>'DELETE',
1469: x_rowid =>X_ROWID
1470: );
1471: DELETE FROM IGS_CA_INST_ALL
1472: WHERE ROWID=X_ROWID;
1473: IF (SQL%NOTFOUND) THEN
1474: RAISE NO_DATA_FOUND;
1475: END IF;
1492: --Change History:
1493: --Who When What
1494: -------------------------------------------------------------------
1495:
1496: CURSOR cur_delete (cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_number igs_ca_inst.sequence_number%TYPE)
1497: IS
1498: SELECT 'x'
1499: FROM igs_ca_inst i, igs_ca_stat s
1500: WHERE i.cal_status = s.cal_status
1495:
1496: CURSOR cur_delete (cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_number igs_ca_inst.sequence_number%TYPE)
1497: IS
1498: SELECT 'x'
1499: FROM igs_ca_inst i, igs_ca_stat s
1500: WHERE i.cal_status = s.cal_status
1501: AND s.s_cal_status = 'PLANNED'
1502: AND i.cal_type = cp_cal_type
1503: AND i.sequence_number = cp_seq_number;
1529: --Change History:
1530: --Who When What
1531: --kpadiyar 06-May-2003 Added the validations for bug 2885873
1532: -------------------------------------------------------------------
1533: CURSOR cur_get_status (cp_cal_status igs_ca_inst.cal_status%TYPE)
1534: IS
1535: SELECT s_cal_status
1536: FROM igs_ca_stat
1537: WHERE cal_status = cp_cal_status;
1536: FROM igs_ca_stat
1537: WHERE cal_status = cp_cal_status;
1538: l_s_cal_status igs_ca_stat.s_cal_status%TYPE;
1539:
1540: CURSOR cur_check_update (cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_number igs_ca_inst.sequence_number%TYPE)
1541: IS
1542: SELECT 'x'
1543: FROM igs_ca_inst i, igs_ca_stat s
1544: WHERE i.cal_status = s.cal_status
1539:
1540: CURSOR cur_check_update (cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_number igs_ca_inst.sequence_number%TYPE)
1541: IS
1542: SELECT 'x'
1543: FROM igs_ca_inst i, igs_ca_stat s
1544: WHERE i.cal_status = s.cal_status
1545: AND s.s_cal_status = 'ACTIVE'
1546: AND i.cal_type = cp_cal_type
1547: AND i.sequence_number = cp_seq_number;
1653: END;
1654:
1655: END beforerowupdate;
1656:
1657: END igs_ca_inst_pkg;