[Home] [Help]
1: PACKAGE BODY Igs_En_Su_Attempt_Pkg AS
2: /* $Header: IGSEI36B.pls 120.22 2006/03/02 03:30:18 bdeviset ship $ */
3:
4: l_rowid VARCHAR2(25);
5: old_references IGS_EN_SU_ATTEMPT_ALL%ROWTYPE;
1: PACKAGE BODY Igs_En_Su_Attempt_Pkg AS
2: /* $Header: IGSEI36B.pls 120.22 2006/03/02 03:30:18 bdeviset ship $ */
3:
4: l_rowid VARCHAR2(25);
5: old_references IGS_EN_SU_ATTEMPT_ALL%ROWTYPE;
6: new_references IGS_EN_SU_ATTEMPT_ALL%ROWTYPE;
7: l_old_cp NUMBER(10);
8: l_new_cp NUMBER(10);
9: l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
2: /* $Header: IGSEI36B.pls 120.22 2006/03/02 03:30:18 bdeviset ship $ */
3:
4: l_rowid VARCHAR2(25);
5: old_references IGS_EN_SU_ATTEMPT_ALL%ROWTYPE;
6: new_references IGS_EN_SU_ATTEMPT_ALL%ROWTYPE;
7: l_old_cp NUMBER(10);
8: l_new_cp NUMBER(10);
9: l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
10: l_load_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
98: X_SS_SOURCE_IND IN VARCHAR2 DEFAULT 'N'
99: ) AS
100: CURSOR cur_old_ref_values IS
101: SELECT *
102: FROM IGS_EN_SU_ATTEMPT_ALL
103: WHERE ROWID = x_rowid;
104: BEGIN
105: l_rowid := x_rowid;
106: -- initialising the global variables for bug#5020285
296: --
297: -- Cursor to count the actual enrollment - Unit Section Level
298: --
299: -- Bug 19055975
300: -- previous the cursor was doing a select count(*) from igs_en_su_attempt
301: -- now changed it to use the enrollment actual from the unit section table
302: CURSOR usec_enr_act(p_uoo_id NUMBER) IS
303: SELECT ENROLLMENT_ACTUAL
304: FROM igs_ps_unit_ofr_opt
350:
351: -- Trigger description :-
352: -- "OSS_TST".trg_sua_br_iud
353: -- BEFORE INSERT OR DELETE OR UPDATE
354: -- ON IGS_EN_SU_ATTEMPT_ALL
355: -- FOR EACH ROW
356: PROCEDURE BeforeRowInsertUpdateDelete1(
357: p_inserting IN BOOLEAN ,
358: p_updating IN BOOLEAN ,
387:
388: */
389: --Getting the max administrative priority value, bug 2526021
390: CURSOR c_admin_priority (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
391: SELECT max(ADMINISTRATIVE_PRIORITY) FROM igs_en_su_attempt
392: WHERE uoo_id = cp_uoo_id;
393:
394: CURSOR c_unit_opt (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
395: SELECT 1 FROM igs_ps_unit_ofr_opt
417: l_sup_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
418: l_sup_ver igs_ps_unit_ofr_opt.version_number%TYPE;
419: l_placement_unit NUMBER;
420:
421: l_admin_priority igs_en_su_attempt.administrative_priority%TYPE;
422: v_message_name VARCHAR2(30);
423: v_message_token VARCHAR2(2000);
424: v_rule_waived_person_id IGS_EN_SU_ATTEMPT_ALL.rule_waived_person_id%TYPE;
425: v_effective_dt DATE;
420:
421: l_admin_priority igs_en_su_attempt.administrative_priority%TYPE;
422: v_message_name VARCHAR2(30);
423: v_message_token VARCHAR2(2000);
424: v_rule_waived_person_id IGS_EN_SU_ATTEMPT_ALL.rule_waived_person_id%TYPE;
425: v_effective_dt DATE;
426: v_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
427: v_return_val IGS_PE_STD_TODO.sequence_number%TYPE;
428: v_old_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
422: v_message_name VARCHAR2(30);
423: v_message_token VARCHAR2(2000);
424: v_rule_waived_person_id IGS_EN_SU_ATTEMPT_ALL.rule_waived_person_id%TYPE;
425: v_effective_dt DATE;
426: v_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
427: v_return_val IGS_PE_STD_TODO.sequence_number%TYPE;
428: v_old_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
429: v_old_location_cd IGS_EN_SU_ATTEMPT_ALL.location_cd%TYPE;
430: v_old_unit_class IGS_EN_SU_ATTEMPT_ALL.unit_class%TYPE;
424: v_rule_waived_person_id IGS_EN_SU_ATTEMPT_ALL.rule_waived_person_id%TYPE;
425: v_effective_dt DATE;
426: v_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
427: v_return_val IGS_PE_STD_TODO.sequence_number%TYPE;
428: v_old_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
429: v_old_location_cd IGS_EN_SU_ATTEMPT_ALL.location_cd%TYPE;
430: v_old_unit_class IGS_EN_SU_ATTEMPT_ALL.unit_class%TYPE;
431: cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
432: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
425: v_effective_dt DATE;
426: v_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
427: v_return_val IGS_PE_STD_TODO.sequence_number%TYPE;
428: v_old_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
429: v_old_location_cd IGS_EN_SU_ATTEMPT_ALL.location_cd%TYPE;
430: v_old_unit_class IGS_EN_SU_ATTEMPT_ALL.unit_class%TYPE;
431: cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
432: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
433: cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
426: v_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
427: v_return_val IGS_PE_STD_TODO.sequence_number%TYPE;
428: v_old_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE;
429: v_old_location_cd IGS_EN_SU_ATTEMPT_ALL.location_cd%TYPE;
430: v_old_unit_class IGS_EN_SU_ATTEMPT_ALL.unit_class%TYPE;
431: cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
432: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
433: cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
434: p_duplicate_course_cd VARCHAR2(30);
462: END IF;
463: END IF;
464:
465: -- If trigger has not been disabled, perform required processing
466: --IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_SU_ATTEMPT_ALL') THEN
467: -- Set audit details and discontinuation details
468: IF p_inserting THEN
469: -- Validate that the IGS_PS_UNIT offering option is being offered.
470: IF IGS_EN_VAL_SUA.enrp_val_sua_uoo(
488:
489: END IF;
490: IF p_inserting OR p_updating THEN
491:
492: IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
493:
494: IF p_inserting THEN
495:
496: -- Validate that discontinued date is not entered when p_inserting
715: APP_EXCEPTION.RAISE_EXCEPTION;
716: END IF;
717: END IF;
718:
719: END IF; --IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
720:
721: -- Validate that advanced standing does not already exist
722: IF p_inserting OR
723: (p_updating AND
741: APP_EXCEPTION.RAISE_EXCEPTION;
742: END IF;
743: END IF;
744:
745: IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
746:
747: -- Set IGS_PS_UNIT attempt status
748: IF new_references.unit_attempt_status IS NULL THEN
749: v_unit_attempt_status := 'Unknown';
838: -- Validate that teaching period is not prior to commencement date
839: -- with the exception of DUPLICATE IGS_PS_UNIT attempts
840: END IF;
841:
842: END IF; -- IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
843:
844: IF p_inserting THEN
845: IF IGS_EN_VAL_SUA.enrp_val_sua_ci(
846: new_references.person_id,
880: END IF;
881: END IF;
882:
883:
884: IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
885: -- Save rowid and old discontinuation date
886: -- if discontinuation details change
887:
888: IF p_updating THEN
981: IGS_GE_MSG_STACK.ADD;
982: APP_EXCEPTION.RAISE_EXCEPTION;
983: END IF;
984: END IF;
985: END IF; --IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
986:
987: -- Validate override enrolled and achievable credit points
988: IF new_references.no_assessment_ind <> 'Y' THEN
989: --skip validation of override enrolled and achievable credit points for audited record
1050: CLOSE cur_sub_uoo;
1051:
1052: END IF;
1053:
1054: IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
1055:
1056: -- If required add a todo entry to forced the re-checking of the IGS_PS_UNIT IGS_RU_RULEs
1057: -- for the student. This is done in insert of a new ENROLLED/INVALID IGS_PS_UNIT
1058: -- attempt, on the confirmation of a IGS_PS_UNIT attempt or on lifting of a
1165: END IF;
1166: CLOSE cur_placement_unit;
1167: END IF;
1168: END IF;
1169: END IF; -- IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
1170:
1171: END IF; -- IF p_inserting OR p_updating THEN
1172: IF p_deleting THEN
1173:
1258:
1259: -- Trigger description :-
1260: -- "OSS_TST".trg_sua_br_iud_fin
1261: -- BEFORE INSERT OR DELETE OR UPDATE
1262: -- ON IGS_EN_SU_ATTEMPT_ALL
1263: -- FOR EACH ROW
1264: PROCEDURE BeforeRowInsertUpdateDelete2(
1265: p_inserting IN BOOLEAN,
1266: p_updating IN BOOLEAN ,
1387:
1388: -- Trigger description :-
1389: -- "OSS_TST".trg_sua_ar_iud
1390: -- AFTER INSERT OR DELETE OR UPDATE
1391: -- ON IGS_EN_SU_ATTEMPT_ALL
1392: -- FOR EACH ROW
1393: PROCEDURE AfterRowInsertUpdateDelete3(
1394: p_inserting IN BOOLEAN,
1395: p_updating IN BOOLEAN ,
1398: v_message_name VARCHAR2(30);
1399: v_rowid_saved BOOLEAN := FALSE;
1400: BEGIN
1401: -- If trigger has not been disabled, perform required processing
1402: -- IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_SU_ATTEMPT_ALL') THEN
1403: IF(p_updating AND
1404: old_references.unit_attempt_status <> new_references.unit_attempt_status) OR
1405: p_inserting THEN
1406: -- update of student IGS_PS_COURSE attempt after student IGS_PS_UNIT attempt is posted
1425:
1426: -- Trigger description :-
1427: -- "OSS_TST".trg_sua_ar_u_hist
1428: -- AFTER UPDATE
1429: -- ON IGS_EN_SU_ATTEMPT_ALL
1430: -- FOR EACH ROW
1431: PROCEDURE AfterRowUpdate4(
1432: p_inserting IN BOOLEAN,
1433: p_updating IN BOOLEAN ,
1490: END AfterRowUpdate4;
1491: -- Trigger description :-
1492: -- "OSS_TST".trg_sua_as_iud
1493: -- AFTER INSERT OR DELETE OR UPDATE
1494: -- ON IGS_EN_SU_ATTEMPT_ALL
1495:
1496:
1497: PROCEDURE Check_Parent_Existance AS
1498: BEGIN
1498: BEGIN
1499:
1500: /* WHO WHEN WHAT
1501: ckasu 27-NOV-2005 Added logic inorder toby pass validation IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION
1502: when IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop = 'PLAN' since Plaaning Sheet Records
1503: are created in 'PLANNED' Status which is not a looup value as a part of bug#4666102
1504:
1505: */
1506:
1503: are created in 'PLANNED' Status which is not a looup value as a part of bug#4666102
1504:
1505: */
1506:
1507: IF igs_en_su_attempt_pkg.pkg_source_of_drop = 'SWAP' THEN
1508: RETURN;
1509: END IF;
1510: IF (((old_references.administrative_unit_status= new_references.administrative_unit_status)) OR
1511: ((new_references.administrative_unit_status IS NULL))) THEN
1589: IGS_GE_MSG_STACK.ADD;
1590: APP_EXCEPTION.RAISE_EXCEPTION;
1591: END IF;
1592: IF (((old_references.unit_attempt_status = new_references.unit_attempt_status)) OR
1593: ((new_references.unit_attempt_status IS NULL)) OR (IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NOT NULL
1594: AND IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop = 'PLAN') ) THEN
1595: NULL;
1596: ELSIF NOT IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION('UNIT_ATTEMPT_STATUS',
1597: new_references.unit_attempt_status) THEN
1590: APP_EXCEPTION.RAISE_EXCEPTION;
1591: END IF;
1592: IF (((old_references.unit_attempt_status = new_references.unit_attempt_status)) OR
1593: ((new_references.unit_attempt_status IS NULL)) OR (IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NOT NULL
1594: AND IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop = 'PLAN') ) THEN
1595: NULL;
1596: ELSIF NOT IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION('UNIT_ATTEMPT_STATUS',
1597: new_references.unit_attempt_status) THEN
1598: Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1703: WHERE person_id = old_references.person_id
1704: AND course_cd = old_references.course_cd
1705: AND uoo_id = old_references.uoo_id;
1706:
1707: CURSOR CUR_IGS_EN_SU_ATTEMPT_H IS
1708: SELECT ROWID
1709: FROM IGS_EN_SU_ATTEMPT_H_ALL
1710: WHERE person_id = old_references.person_id
1711: AND course_cd = old_references.course_cd
1705: AND uoo_id = old_references.uoo_id;
1706:
1707: CURSOR CUR_IGS_EN_SU_ATTEMPT_H IS
1708: SELECT ROWID
1709: FROM IGS_EN_SU_ATTEMPT_H_ALL
1710: WHERE person_id = old_references.person_id
1711: AND course_cd = old_references.course_cd
1712: AND uoo_id = old_references.uoo_id;
1713: BEGIN
1710: WHERE person_id = old_references.person_id
1711: AND course_cd = old_references.course_cd
1712: AND uoo_id = old_references.uoo_id;
1713: BEGIN
1714: IGS_AS_MSHT_SU_ATMPT_PKG.GET_FK_IGS_EN_SU_ATTEMPT (
1715: old_references.person_id,
1716: old_references.course_cd,
1717: old_references.uoo_id
1718: );
1716: old_references.course_cd,
1717: old_references.uoo_id
1718: );
1719: /*deleting the history record*/
1720: FOR IGS_EN_SU_ATTEMPT_H_REC IN CUR_IGS_EN_SU_ATTEMPT_H LOOP
1721: IGS_EN_SU_ATTEMPT_H_PKG.DELETE_ROW(X_ROWID => IGS_EN_SU_ATTEMPT_H_REC.ROWID);
1722: END LOOP;
1723: FOR IGS_AS_SU_ATMPT_ITM_REC IN CUR_IGS_AS_SU_ATMPT_ITM LOOP
1724: IGS_AS_SU_ATMPT_ITM_PKG.DELETE_ROW(X_ROWID => IGS_AS_SU_ATMPT_ITM_REC.ROWID);
1717: old_references.uoo_id
1718: );
1719: /*deleting the history record*/
1720: FOR IGS_EN_SU_ATTEMPT_H_REC IN CUR_IGS_EN_SU_ATTEMPT_H LOOP
1721: IGS_EN_SU_ATTEMPT_H_PKG.DELETE_ROW(X_ROWID => IGS_EN_SU_ATTEMPT_H_REC.ROWID);
1722: END LOOP;
1723: FOR IGS_AS_SU_ATMPT_ITM_REC IN CUR_IGS_AS_SU_ATMPT_ITM LOOP
1724: IGS_AS_SU_ATMPT_ITM_PKG.DELETE_ROW(X_ROWID => IGS_AS_SU_ATMPT_ITM_REC.ROWID);
1725: END LOOP;
1722: END LOOP;
1723: FOR IGS_AS_SU_ATMPT_ITM_REC IN CUR_IGS_AS_SU_ATMPT_ITM LOOP
1724: IGS_AS_SU_ATMPT_ITM_PKG.DELETE_ROW(X_ROWID => IGS_AS_SU_ATMPT_ITM_REC.ROWID);
1725: END LOOP;
1726: IGS_AS_SU_STMPTOUT_PKG.GET_FK_IGS_EN_SU_ATTEMPT (
1727: old_references.person_id,
1728: old_references.course_cd,
1729: old_references.uoo_id
1730: );
1727: old_references.person_id,
1728: old_references.course_cd,
1729: old_references.uoo_id
1730: );
1731: IGS_PS_STDNT_UNT_TRN_PKG.GET_FK_IGS_EN_SU_ATTEMPT (
1732: old_references.person_id,
1733: old_references.course_cd,
1734: old_references.uoo_id
1735: );
1733: old_references.course_cd,
1734: old_references.uoo_id
1735: );
1736:
1737: IGS_AS_ANON_ID_US_PKG.GET_FK_IGS_EN_SU_ATTEMPT (
1738: old_references.person_id,
1739: old_references.course_cd,
1740: old_references.uoo_id
1741: );
1739: old_references.course_cd,
1740: old_references.uoo_id
1741: );
1742: --Bug 3199686
1743: IGS_AS_SUA_SES_ATTS_pkg.GET_FK_IGS_EN_SU_ATTEMPT(
1744: old_references.person_id,
1745: old_references.course_cd,
1746: old_references.uoo_id
1747: );
1758: x_uoo_id IN NUMBER
1759: ) RETURN BOOLEAN AS
1760: CURSOR cur_rowid IS
1761: SELECT ROWID
1762: FROM IGS_EN_SU_ATTEMPT_ALL
1763: WHERE person_id = x_person_id
1764: AND course_cd = x_course_cd
1765: AND uoo_id = x_uoo_id
1766: FOR UPDATE NOWAIT;
1784: x_end_dt IN DATE
1785: ) AS
1786: CURSOR cur_rowid IS
1787: SELECT ROWID
1788: FROM IGS_EN_SU_ATTEMPT_ALL
1789: WHERE cal_type= x_cal_type
1790: AND ci_sequence_number = x_sequence_number
1791: AND ci_start_dt = x_start_dt
1792: AND ci_end_dt = x_end_dt ;
1808: x_location_cd IN VARCHAR2
1809: ) AS
1810: CURSOR cur_rowid IS
1811: SELECT ROWID
1812: FROM IGS_EN_SU_ATTEMPT_ALL
1813: WHERE exam_location_cd = x_location_cd ;
1814: lv_rowid cur_rowid%ROWTYPE;
1815: BEGIN
1816: OPEN cur_rowid;
1830: x_gs_version_number IN NUMBER
1831: ) AS
1832: CURSOR cur_rowid IS
1833: SELECT ROWID
1834: FROM IGS_EN_SU_ATTEMPT_ALL
1835: WHERE grading_schema_code = x_grading_schema_code
1836: AND
1837: gs_version_number = x_gs_version_number ;
1838:
1854: x_person_id IN NUMBER
1855: ) AS
1856: CURSOR cur_rowid IS
1857: SELECT ROWID
1858: FROM IGS_EN_SU_ATTEMPT_ALL
1859: WHERE rule_waived_person_id = x_person_id ;
1860: lv_rowid cur_rowid%ROWTYPE;
1861: BEGIN
1862: OPEN cur_rowid;
1876: x_course_cd IN VARCHAR2
1877: ) AS
1878: CURSOR cur_rowid IS
1879: SELECT ROWID
1880: FROM IGS_EN_SU_ATTEMPT_ALL
1881: WHERE person_id = x_person_id
1882: AND course_cd = x_course_cd ;
1883: lv_rowid cur_rowid%ROWTYPE;
1884: BEGIN
1898: x_unit_attempt_status IN VARCHAR2
1899: ) AS
1900: CURSOR cur_rowid IS
1901: SELECT ROWID
1902: FROM IGS_EN_SU_ATTEMPT_ALL
1903: WHERE unit_attempt_status = x_unit_attempt_status ;
1904: lv_rowid cur_rowid%ROWTYPE;
1905: BEGIN
1906: OPEN cur_rowid;
1920: x_version_number IN NUMBER
1921: ) AS
1922: CURSOR cur_rowid IS
1923: SELECT ROWID
1924: FROM IGS_EN_SU_ATTEMPT_ALL
1925: WHERE sup_unit_cd = x_unit_cd
1926: AND sup_version_number = x_version_number
1927: OR (unit_cd = x_unit_cd
1928: AND version_number = x_version_number ) ;
1944: x_unit_cd IN VARCHAR2
1945: ) AS
1946: CURSOR cur_rowid IS
1947: SELECT ROWID
1948: FROM IGS_EN_SU_ATTEMPT_ALL
1949: WHERE unit_cd = x_unit_cd ;
1950: lv_rowid cur_rowid%ROWTYPE;
1951: BEGIN
1952: OPEN cur_rowid;
1970: x_unit_class IN VARCHAR2
1971: ) AS
1972: CURSOR cur_rowid IS
1973: SELECT ROWID
1974: FROM IGS_EN_SU_ATTEMPT_ALL
1975: WHERE unit_cd = x_unit_cd
1976: AND version_number = x_version_number
1977: AND cal_type= x_cal_type
1978: AND ci_sequence_number = x_ci_sequence_number
1996: x_uoo_id IN NUMBER
1997: ) AS
1998: CURSOR cur_rowid IS
1999: SELECT ROWID
2000: FROM IGS_EN_SU_ATTEMPT_ALL
2001: WHERE uoo_id = x_uoo_id ;
2002: lv_rowid cur_rowid%ROWTYPE;
2003: BEGIN
2004: OPEN cur_rowid;
2019: x_unit_cd IN VARCHAR2
2020: ) AS
2021: CURSOR cur_rowid IS
2022: SELECT ROWID
2023: FROM IGS_EN_SU_ATTEMPT_ALL
2024: WHERE version_number = x_version_number
2025: AND grading_schema_code = x_grading_schema_code
2026: AND gs_version_number = x_gs_version_number
2027: AND unit_cd = x_unit_cd ;
2046: x_uoo_id IN NUMBER
2047: ) AS
2048: CURSOR cur_rowid IS
2049: SELECT ROWID
2050: FROM IGS_EN_SU_ATTEMPT_ALL
2051: WHERE grading_schema_code = x_grading_schema_code
2052: AND gs_version_number = x_gs_version_number
2053: AND uoo_id = x_uoo_id ;
2054:
2183: -- add_units_api for Calling Object as 'PLAN' as a part of bug#4666102
2184: ------------------------------------------------------------------------------------
2185: CURSOR cur_rowid IS
2186: SELECT ROWID
2187: FROM IGS_EN_SU_ATTEMPT_ALL
2188: WHERE person_id = x_person_id
2189: AND course_cd = x_course_cd
2190: AND uoo_id = x_uoo_id
2191: AND unit_attempt_status = 'DROPPED';
2328: p_course_cd => x_course_cd,
2329: p_uoo_id => x_uoo_id);
2330:
2331:
2332: END IF; -- IF NVL(X_SS_SOURCE_IND,'N') <> 'S' OR (IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NOT NULL AND IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop = 'PLAN') THEN
2333: END IF;
2334:
2335: IF (p_action = 'INSERT') THEN
2336: -- Call all the procedures related to Before Insert.
2338: BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
2339: p_updating => FALSE,
2340: p_deleting => FALSE );
2341:
2342: IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2343:
2344: BeforeRowInsertUpdateDelete2 ( p_inserting => TRUE,
2345: p_updating => FALSE,
2346: p_deleting => FALSE);
2344: BeforeRowInsertUpdateDelete2 ( p_inserting => TRUE,
2345: p_updating => FALSE,
2346: p_deleting => FALSE);
2347:
2348: END IF; --IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2349:
2350: IF Get_PK_For_Validation (
2351: NEW_REFERENCES.person_id,
2352: NEW_REFERENCES.course_cd,
2370: -- Call all the procedures related to Before Update.
2371: BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
2372: p_updating => TRUE,
2373: p_deleting => FALSE);
2374: IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2375:
2376: BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
2377: p_updating => TRUE ,
2378: p_deleting => FALSE);
2375:
2376: BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
2377: p_updating => TRUE ,
2378: p_deleting => FALSE);
2379: END IF;--IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2380:
2381: Check_Constraints;
2382: Check_Parent_Existance;
2383: Check_Uniqueness;
2447: -- ckasu 27-NOV-2005 Added logic inorder to perform/by pass validations when this was called from
2448: -- add_units_api for Calling Object as 'PLAN' as a part of bug#4666102
2449: -- bdeviset 02-Mar-2006 Modifed cursor c_spa_terms for bug# 5073761
2450:
2451: CURSOR c_sua_details(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
2452: SELECT * FROM igs_en_su_attempt
2453: WHERE uoo_id = cp_uoo_id AND
2454: unit_attempt_status = 'WAITLISTED'
2455: ORDER BY administrative_priority ASC;
2448: -- add_units_api for Calling Object as 'PLAN' as a part of bug#4666102
2449: -- bdeviset 02-Mar-2006 Modifed cursor c_spa_terms for bug# 5073761
2450:
2451: CURSOR c_sua_details(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
2452: SELECT * FROM igs_en_su_attempt
2453: WHERE uoo_id = cp_uoo_id AND
2454: unit_attempt_status = 'WAITLISTED'
2455: ORDER BY administrative_priority ASC;
2456:
2466: SELECT uv.repeatable_ind
2467: FROM igs_ps_unit_ver uv
2468: WHERE uv.unit_cd = cp_unit_cd AND
2469: uv.version_number = cp_uv_version_number;
2470: CURSOR c_renrollcheck(cp_person_id igs_en_su_attempt.person_id%TYPE,
2471: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2472: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2473: SELECT 'x'
2474: FROM igs_en_su_attempt sua
2467: FROM igs_ps_unit_ver uv
2468: WHERE uv.unit_cd = cp_unit_cd AND
2469: uv.version_number = cp_uv_version_number;
2470: CURSOR c_renrollcheck(cp_person_id igs_en_su_attempt.person_id%TYPE,
2471: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2472: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2473: SELECT 'x'
2474: FROM igs_en_su_attempt sua
2475: WHERE person_id=cp_person_id
2468: WHERE uv.unit_cd = cp_unit_cd AND
2469: uv.version_number = cp_uv_version_number;
2470: CURSOR c_renrollcheck(cp_person_id igs_en_su_attempt.person_id%TYPE,
2471: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2472: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2473: SELECT 'x'
2474: FROM igs_en_su_attempt sua
2475: WHERE person_id=cp_person_id
2476: AND uoo_id=cp_uoo_id
2470: CURSOR c_renrollcheck(cp_person_id igs_en_su_attempt.person_id%TYPE,
2471: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2472: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2473: SELECT 'x'
2474: FROM igs_en_su_attempt sua
2475: WHERE person_id=cp_person_id
2476: AND uoo_id=cp_uoo_id
2477: AND unit_attempt_status ='COMPLETED'
2478: AND course_cd <> cp_course_cd
2483: AND sut.transfer_course_cd = cp_course_cd
2484: AND sut.course_cd = sua.course_cd
2485: ) ;
2486:
2487: CURSOR c_conflict_suas (cp_person_id igs_en_su_attempt.person_id%TYPE,
2488: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2489: cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
2490: cp_version_number igs_en_su_attempt.version_number%TYPE,
2491: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2484: AND sut.course_cd = sua.course_cd
2485: ) ;
2486:
2487: CURSOR c_conflict_suas (cp_person_id igs_en_su_attempt.person_id%TYPE,
2488: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2489: cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
2490: cp_version_number igs_en_su_attempt.version_number%TYPE,
2491: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2492:
2485: ) ;
2486:
2487: CURSOR c_conflict_suas (cp_person_id igs_en_su_attempt.person_id%TYPE,
2488: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2489: cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
2490: cp_version_number igs_en_su_attempt.version_number%TYPE,
2491: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2492:
2493: SELECT 'x'
2486:
2487: CURSOR c_conflict_suas (cp_person_id igs_en_su_attempt.person_id%TYPE,
2488: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2489: cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
2490: cp_version_number igs_en_su_attempt.version_number%TYPE,
2491: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2492:
2493: SELECT 'x'
2494: FROM igs_en_su_attempt sua
2487: CURSOR c_conflict_suas (cp_person_id igs_en_su_attempt.person_id%TYPE,
2488: cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,
2489: cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
2490: cp_version_number igs_en_su_attempt.version_number%TYPE,
2491: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2492:
2493: SELECT 'x'
2494: FROM igs_en_su_attempt sua
2495: WHERE person_id=cp_person_id
2490: cp_version_number igs_en_su_attempt.version_number%TYPE,
2491: cp_course_cd igs_en_su_attempt.course_cd%TYPE) IS
2492:
2493: SELECT 'x'
2494: FROM igs_en_su_attempt sua
2495: WHERE person_id=cp_person_id
2496: AND unit_cd=cp_unit_cd
2497: AND version_number=cp_version_number
2498: AND unit_attempt_status IN ('ENROLLED','DUPLICATE','COMPLETED','DISCONTIN')
2577: v_dummy c_other_key_recs%ROWTYPE;
2578:
2579:
2580: l_sua_details c_sua_details%ROWTYPE;
2581: l_cnt igs_en_su_attempt.administrative_priority%TYPE;
2582: l_repeatable_unit igs_ps_unit_ver.repeatable_ind%TYPE;
2583: l_act_enr NUMBER;
2584: l_dummy VARCHAR2(10);
2585: l_dummy_value NUMBER;
2583: l_act_enr NUMBER;
2584: l_dummy VARCHAR2(10);
2585: l_dummy_value NUMBER;
2586: l_return_status VARCHAR2(100);
2587: l_sup_sub_unit_status igs_en_su_attempt.unit_attempt_status%TYPE;
2588:
2589: BEGIN
2590:
2591:
2617: END IF; --IF old_references.unit_attempt_status <> new_references.unit_attempt_status THEN
2618:
2619: END IF; -- end of IF (p_action = 'UPDATE') THEN
2620:
2621: IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2622:
2623:
2624: l_rowid := x_rowid;
2625: IF (p_action = 'INSERT') THEN
2669:
2670: -- modified this condition to include DROP also for bug#4864437
2671: -- this code is being commented here but will be replicated in swap page and Drop page
2672: -- SwapAMImpl.java :swapSubmit , StdDropAMImpl.java:dropSubmit
2673: IF NVL(igs_en_su_attempt_pkg.pkg_source_of_drop,'NULL') NOT IN ( 'SWAP','DROP') THEN
2674:
2675: AfterRowInsertUpdateDelete3 ( p_inserting => FALSE,
2676: p_updating => TRUE,
2677: p_deleting => FALSE );
2706: FOR l_sua_details IN c_sua_details(new_references.uoo_id) LOOP
2707: IF l_cnt <> NVL(l_sua_details.administrative_priority,0) THEN
2708: l_sua_details.administrative_priority := l_cnt;
2709: BEGIN
2710: igs_en_su_attempt_pkg.update_row(
2711: X_ROWID => l_sua_details.row_id ,
2712: X_PERSON_ID => l_sua_details.person_id ,
2713: X_COURSE_CD => l_sua_details.course_cd ,
2714: X_UNIT_CD => l_sua_details.unit_cd ,
2801: END IF;
2802:
2803: END IF;
2804:
2805: END IF; --IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2806:
2807: IF (p_action = 'DELETE') THEN
2808: -- Call all the procedures related to After Delete.
2809: AfterRowInsertUpdateDelete3 ( p_inserting => FALSE,
2854: END IF; --renroll check
2855: END IF; --l_repeatable_unit
2856: END IF;
2857:
2858: IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2859:
2860: IF (p_action = 'UPDATE') THEN
2861: IF (old_references.unit_attempt_status <> new_references.unit_attempt_status) AND
2862: (new_references.unit_attempt_status IN ('DROPPED', 'DISCONTIN')) THEN
2970: x_plan_sht_status =>'SKIP');
2971: END LOOP;
2972: END IF;
2973:
2974: END IF; -- IF IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop IS NULL OR IGS_EN_SU_ATTEMPT_PKG.pkg_source_of_drop <> 'PLAN' THEN
2975:
2976: END After_DML;
2977: --
2978: PROCEDURE INSERT_ROW (
3061: ---------------------------------------------------------------------------------------*/
3062: -- Changing the parameters X_UNIT_CD, X_CAL_TYPE, X_CI_SEQUENCE_NUMBER TO NEW_REFERENCES
3063: -- in response to the bug 1766230. For further detils please refer to version 115.19 of IGSEI36B.pls
3064:
3065: CURSOR C IS SELECT ROWID FROM IGS_EN_SU_ATTEMPT_ALL
3066: WHERE PERSON_ID = X_PERSON_ID
3067: AND COURSE_CD = X_COURSE_CD
3068: AND UOO_ID = X_UOO_ID;
3069:
3069:
3070:
3071: CURSOR cur_rowid IS
3072: SELECT ROWID
3073: FROM IGS_EN_SU_ATTEMPT_ALL
3074: WHERE person_id = x_person_id
3075: AND course_cd = x_course_cd
3076: AND unit_attempt_status = 'DROPPED'
3077: AND ( uoo_id = x_uoo_id
3292: --
3293: IF (x_mode = 'S') THEN
3294: igs_sc_gen_001.set_ctx('R');
3295: END IF;
3296: INSERT INTO IGS_EN_SU_ATTEMPT_ALL (
3297: PERSON_ID,
3298: COURSE_CD,
3299: UNIT_CD,
3300: VERSION_NUMBER,
3640: -- CORE_INDICATOR_CODE added by ptandon 30-SEP-2003. Enh Bug# 3052432
3641: CORE_INDICATOR_CODE,
3642: UPD_AUDIT_FLAG,
3643: SS_SOURCE_IND
3644: FROM IGS_EN_SU_ATTEMPT_ALL
3645: WHERE ROWID = X_ROWID FOR UPDATE NOWAIT;
3646: tlinfo c1%ROWTYPE;
3647: BEGIN
3648: OPEN c1;
4054:
4055: IF (x_mode = 'S') THEN
4056: igs_sc_gen_001.set_ctx('R');
4057: END IF;
4058: UPDATE IGS_EN_SU_ATTEMPT_ALL SET
4059: VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
4060: LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
4061: UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
4062: CI_START_DT = NEW_REFERENCES.CI_START_DT,
4241: X_CORE_INDICATOR_CODE IN VARCHAR2,
4242: X_UPD_AUDIT_FLAG IN VARCHAR2 ,
4243: X_SS_SOURCE_IND IN VARCHAR2
4244: ) AS
4245: CURSOR c1 IS SELECT ROWID FROM IGS_EN_SU_ATTEMPT_ALL
4246: WHERE PERSON_ID = X_PERSON_ID
4247: AND COURSE_CD = X_COURSE_CD
4248: AND UOO_ID = X_UOO_ID
4249:
4416:
4417: /* Cursor to get the unit attempt attempts attributes, bug 3000742*/
4418: CURSOR cur_sua (cp_rowid VARCHAR2) IS
4419: SELECT *
4420: FROM igs_en_su_attempt
4421: WHERE row_id = cp_rowid;
4422:
4423: /* Cursor to check whether the unit attempt status before adding to cart is DROPPED, bug 3000742*/
4424: CURSOR cur_sua_hist (cp_person_id NUMBER,cp_course_cd VARCHAR2, cp_uoo_id NUMBER) IS
4422:
4423: /* Cursor to check whether the unit attempt status before adding to cart is DROPPED, bug 3000742*/
4424: CURSOR cur_sua_hist (cp_person_id NUMBER,cp_course_cd VARCHAR2, cp_uoo_id NUMBER) IS
4425: SELECT unit_attempt_status
4426: FROM igs_en_su_attempt_h
4427: WHERE person_id = cp_person_id
4428: AND course_cd = cp_course_cd
4429: AND uoo_id = cp_uoo_id
4430: ORDER BY hist_end_dt DESC;
4429: AND uoo_id = cp_uoo_id
4430: ORDER BY hist_end_dt DESC;
4431:
4432: l_cur_sua_rec cur_sua%ROWTYPE;
4433: l_sua_status igs_en_su_attempt.unit_attempt_status%TYPE;
4434:
4435: BEGIN
4436:
4437: OPEN cur_sua(X_ROWID);
4531:
4532: IF (x_mode = 'S') THEN
4533: igs_sc_gen_001.set_ctx('R');
4534: END IF;
4535: DELETE FROM IGS_EN_SU_ATTEMPT_ALL
4536: WHERE ROWID = X_ROWID;
4537:
4538: IF (SQL%NOTFOUND) THEN
4539: fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
4741: x_version_number IN NUMBER
4742: ) RETURN BOOLEAN AS
4743: CURSOR cur_sua IS
4744: SELECT ROWID
4745: FROM IGS_EN_SU_ATTEMPT_ALL
4746: WHERE unit_cd = x_unit_cd
4747: AND cal_type = x_cal_type
4748: AND ci_sequence_number = x_ci_sequence_number
4749: AND location_cd = x_location_cd
4842: END IF;
4843: END IF;
4844: END update_reserved_seat;
4845:
4846: END Igs_En_Su_Attempt_Pkg;