eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_RE_VAL_RSUP dependencies on IGS_RE_SPRVSR

Line 31: -- * At least one IGS_RE_SPRVSR with

27: BEGIN -- resp_val_rsup_comm
28: -- This module validates that a principal supervisor exists
29: -- on the commencement date of the research IGS_RE_CANDIDATURE.
30: -- Validatios are:
31: -- * At least one IGS_RE_SPRVSR with
32: -- IGS_RE_SPRVSR_TYPE checked as a principal
33: -- must exist fir an offer where research details
34: -- are mandatory or where the IGS_PS_COURSE is defined as
35: -- a research supervisor.

Line 32: -- IGS_RE_SPRVSR_TYPE checked as a principal

28: -- This module validates that a principal supervisor exists
29: -- on the commencement date of the research IGS_RE_CANDIDATURE.
30: -- Validatios are:
31: -- * At least one IGS_RE_SPRVSR with
32: -- IGS_RE_SPRVSR_TYPE checked as a principal
33: -- must exist fir an offer where research details
34: -- are mandatory or where the IGS_PS_COURSE is defined as
35: -- a research supervisor.
36: DECLARE

Line 190: FROM IGS_RE_SPRVSR rsup

186: v_enddt_total_sup_per NUMBER;
187:
188: CURSOR c_rsup IS
189: SELECT rsup.start_dt, rsup.end_dt
190: FROM IGS_RE_SPRVSR rsup
191: WHERE rsup.ca_person_id = p_ca_person_id AND
192: rsup.ca_sequence_number = p_ca_sequence_number
193: ORDER BY
194: rsup.start_dt DESC;

Line 198: cp_date IGS_RE_SPRVSR.start_dt%TYPE) IS

194: rsup.start_dt DESC;
195:
196: -- cursor to find the sum of supervision percentage on the passed date
197: CURSOR c_rsup_per (
198: cp_date IGS_RE_SPRVSR.start_dt%TYPE) IS
199: SELECT NVL(SUM(rsup.supervision_percentage),0)
200: FROM IGS_RE_SPRVSR rsup
201: WHERE rsup.ca_person_id = p_ca_person_id AND
202: rsup.ca_sequence_number = p_ca_sequence_number AND

Line 200: FROM IGS_RE_SPRVSR rsup

196: -- cursor to find the sum of supervision percentage on the passed date
197: CURSOR c_rsup_per (
198: cp_date IGS_RE_SPRVSR.start_dt%TYPE) IS
199: SELECT NVL(SUM(rsup.supervision_percentage),0)
200: FROM IGS_RE_SPRVSR rsup
201: WHERE rsup.ca_person_id = p_ca_person_id AND
202: rsup.ca_sequence_number = p_ca_sequence_number AND
203: rsup.start_dt <= cp_date AND
204: (rsup.end_dt IS NULL OR

Line 209: FROM IGS_RE_SPRVSR rsup,

205: rsup.end_dt >= cp_date);
206:
207: CURSOR c_funding IS
208: SELECT NVL(SUM(rsup.funding_percentage),0),count(*)
209: FROM IGS_RE_SPRVSR rsup,
210: igs_pe_person_types pt,
211: igs_pe_typ_instances pti
212: WHERE rsup.ca_person_id = p_ca_person_id
213: AND rsup.ca_sequence_number = p_ca_sequence_number

Line 220: cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS

216: AND pt.system_type = 'STAFF'
217: AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
218:
219: CURSOR c_rsup2 (
220: cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS
221: SELECT 'x'
222: FROM IGS_RE_SPRVSR rsup
223: WHERE rsup.ca_person_id = p_ca_person_id AND
224: rsup.ca_sequence_number = p_ca_sequence_number AND

Line 222: FROM IGS_RE_SPRVSR rsup

218:
219: CURSOR c_rsup2 (
220: cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS
221: SELECT 'x'
222: FROM IGS_RE_SPRVSR rsup
223: WHERE rsup.ca_person_id = p_ca_person_id AND
224: rsup.ca_sequence_number = p_ca_sequence_number AND
225: rsup.start_dt <= cp_start_dt AND
226: rsup.end_dt IS NOT NULL AND

Line 231: FROM IGS_RE_SPRVSR rsup

227: rsup.end_dt >= cp_start_dt;
228: CURSOR c_rsup3 IS
229: SELECT rsup.start_dt,
230: rsup.end_dt
231: FROM IGS_RE_SPRVSR rsup
232: WHERE rsup.ca_person_id = p_ca_person_id AND
233: rsup.ca_sequence_number = p_ca_sequence_number
234: ORDER BY
235: rsup.start_dt,

Line 241: FROM IGS_RE_SPRVSR rsup

237:
238: -- cursor to get the latest supervsion end date
239: CURSOR c_get_max_end_dt IS
240: SELECT MAX(rsup.end_dt)
241: FROM IGS_RE_SPRVSR rsup
242: WHERE rsup.ca_person_id = p_ca_person_id AND
243: rsup.ca_sequence_number = p_ca_sequence_number;
244:
245: -- cursor to find if a open end dated record exists

Line 248: FROM IGS_RE_SPRVSR rsup

244:
245: -- cursor to find if a open end dated record exists
246: CURSOR c_chk_open_end_dt IS
247: SELECT rsup.end_dt
248: FROM IGS_RE_SPRVSR rsup
249: WHERE rsup.ca_person_id = p_ca_person_id AND
250: rsup.ca_sequence_number = p_ca_sequence_number AND
251: rsup.end_dt IS NULL;
252: BEGIN

Line 418: -- This module validates IGS_RE_SPRVSR.end_dt. Validations are:

414: p_legacy IN VARCHAR2,
415: p_message_name OUT NOCOPY VARCHAR2 )
416: RETURN BOOLEAN AS
417: BEGIN -- resp_val_rsup_person
418: -- This module validates IGS_RE_SPRVSR.end_dt. Validations are:
419: -- Validations are:
420: -- * Cannot be the same as ca_person_id.
421: DECLARE
422: v_deceased_ind VARCHAR2(1);

Line 470: v_start_dt IGS_RE_SPRVSR.start_dt%TYPE;

466: -- This module validates that research IGS_RE_CANDIDATURE has a principal supervisor
467: -- for the required time.
468: DECLARE
469: cst_rsup CONSTANT VARCHAR2(10) := 'RSUP';
470: v_start_dt IGS_RE_SPRVSR.start_dt%TYPE;
471: v_dummy VARCHAR2(1);
472: v_not_found BOOLEAN := FALSE;
473: v_rsup_exists BOOLEAN := FALSE;
474: CURSOR c_rsup IS

Line 476: FROM IGS_RE_SPRVSR rsup

472: v_not_found BOOLEAN := FALSE;
473: v_rsup_exists BOOLEAN := FALSE;
474: CURSOR c_rsup IS
475: SELECT rsup.start_dt
476: FROM IGS_RE_SPRVSR rsup
477: WHERE rsup.ca_person_id = p_ca_person_id AND
478: rsup.ca_sequence_number = p_ca_sequence_number AND
479: (p_end_dt IS NULL OR
480: rsup.start_dt <= p_end_dt) AND

Line 485: cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS

481: (rsup.end_dt IS NULL OR
482: rsup.end_dt >= p_start_dt)
483: ORDER BY rsup.start_dt;
484: CURSOR c_rsup_rst (
485: cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS
486: SELECT 'X'
487: FROM IGS_RE_SPRVSR rsup,
488: IGS_RE_SPRVSR_TYPE rst
489: WHERE rsup.ca_person_id = p_ca_person_id AND

Line 487: FROM IGS_RE_SPRVSR rsup,

483: ORDER BY rsup.start_dt;
484: CURSOR c_rsup_rst (
485: cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS
486: SELECT 'X'
487: FROM IGS_RE_SPRVSR rsup,
488: IGS_RE_SPRVSR_TYPE rst
489: WHERE rsup.ca_person_id = p_ca_person_id AND
490: rsup.ca_sequence_number = p_ca_sequence_number AND
491: rsup.start_dt <= cp_start_dt AND

Line 488: IGS_RE_SPRVSR_TYPE rst

484: CURSOR c_rsup_rst (
485: cp_start_dt IGS_RE_SPRVSR.start_dt%TYPE) IS
486: SELECT 'X'
487: FROM IGS_RE_SPRVSR rsup,
488: IGS_RE_SPRVSR_TYPE rst
489: WHERE rsup.ca_person_id = p_ca_person_id AND
490: rsup.ca_sequence_number = p_ca_sequence_number AND
491: rsup.start_dt <= cp_start_dt AND
492: (rsup.end_dt IS NULL OR

Line 561: -- This module validates IGS_RE_SPRVSR.replaced_person_id

557: p_legacy IN VARCHAR2 ,
558: p_message_name OUT NOCOPY VARCHAR2 )
559: RETURN BOOLEAN AS
560: BEGIN -- resp_val_rsup_repl
561: -- This module validates IGS_RE_SPRVSR.replaced_person_id
562: -- IGS_RE_SPRVSR.replaced_start_dt. Validations are:
563: -- A supervisor cannot be replaced by themselves.
564: -- ie replaced_person_id <> person_id.
565: -- Replaced _person_id/_sequence_number has to be the latest occurrence

Line 562: -- IGS_RE_SPRVSR.replaced_start_dt. Validations are:

558: p_message_name OUT NOCOPY VARCHAR2 )
559: RETURN BOOLEAN AS
560: BEGIN -- resp_val_rsup_repl
561: -- This module validates IGS_RE_SPRVSR.replaced_person_id
562: -- IGS_RE_SPRVSR.replaced_start_dt. Validations are:
563: -- A supervisor cannot be replaced by themselves.
564: -- ie replaced_person_id <> person_id.
565: -- Replaced _person_id/_sequence_number has to be the latest occurrence
566: -- ie latest start_dt.

Line 568: -- IGS_RE_SPRVSR.start_dt.

564: -- ie replaced_person_id <> person_id.
565: -- Replaced _person_id/_sequence_number has to be the latest occurrence
566: -- ie latest start_dt.
567: -- The replaced supervisor has to have an end date prior to
568: -- IGS_RE_SPRVSR.start_dt.
569: DECLARE
570: CURSOR c_rsup IS
571: SELECT rsup.sequence_number,
572: rsup.end_dt

Line 573: FROM IGS_RE_SPRVSR rsup

569: DECLARE
570: CURSOR c_rsup IS
571: SELECT rsup.sequence_number,
572: rsup.end_dt
573: FROM IGS_RE_SPRVSR rsup
574: WHERE rsup.ca_person_id = p_ca_person_id AND
575: rsup.ca_sequence_number = p_ca_sequence_number AND
576: rsup.person_id = p_replaced_person_id
577: ORDER BY rsup.start_dt DESC;

Line 578: v_c_rsup_seq_num IGS_RE_SPRVSR.sequence_number%TYPE;

574: WHERE rsup.ca_person_id = p_ca_person_id AND
575: rsup.ca_sequence_number = p_ca_sequence_number AND
576: rsup.person_id = p_replaced_person_id
577: ORDER BY rsup.start_dt DESC;
578: v_c_rsup_seq_num IGS_RE_SPRVSR.sequence_number%TYPE;
579: v_c_rsup_end_dt IGS_RE_SPRVSR.end_dt%TYPE;
580: BEGIN
581: -- Set the default message number
582: p_message_name := null;

Line 579: v_c_rsup_end_dt IGS_RE_SPRVSR.end_dt%TYPE;

575: rsup.ca_sequence_number = p_ca_sequence_number AND
576: rsup.person_id = p_replaced_person_id
577: ORDER BY rsup.start_dt DESC;
578: v_c_rsup_seq_num IGS_RE_SPRVSR.sequence_number%TYPE;
579: v_c_rsup_end_dt IGS_RE_SPRVSR.end_dt%TYPE;
580: BEGIN
581: -- Set the default message number
582: p_message_name := null;
583: IF p_replaced_person_id IS NOT NULL THEN

Line 669: -- This module validates IGS_RE_SPRVSR.funding_percentage.

665: p_legacy IN VARCHAR2 ,
666: p_message_name OUT NOCOPY VARCHAR2 )
667: RETURN BOOLEAN AS
668: BEGIN -- resp_val_rsup_fund
669: -- This module validates IGS_RE_SPRVSR.funding_percentage.
670: -- Validations are:
671: -- If funding percentage exists, then
672: -- organisational IGS_PS_UNIT must exist and be valid.
673: -- If funding percentage exists, then

Line 761: -- This module validates IGS_RE_SPRVSR.org_unit_cd/ou_start_dt.

757: p_legacy IN VARCHAR2 ,
758: p_message_name OUT NOCOPY VARCHAR2 )
759: RETURN BOOLEAN AS
760: BEGIN -- resp_val_rsup_ou
761: -- This module validates IGS_RE_SPRVSR.org_unit_cd/ou_start_dt.
762: -- Organisational IGS_PS_UNIT must be active.
763: -- Organisational IGS_PS_UNIT must be local is supervisor is a staff member.
764: DECLARE
765: cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';

Line 847: -- This module validates IGS_RE_SPRVSR.person_id overlaps.

843: p_legacy IN VARCHAR2 ,
844: p_message_name OUT NOCOPY VARCHAR2 )
845: RETURN BOOLEAN AS
846: BEGIN -- resp_val_rsup_ovrlp
847: -- This module validates IGS_RE_SPRVSR.person_id overlaps.
848: DECLARE
849: CURSOR c_rsup IS
850: SELECT rsup.start_dt,
851: rsup.end_dt

Line 852: FROM IGS_RE_SPRVSR rsup

848: DECLARE
849: CURSOR c_rsup IS
850: SELECT rsup.start_dt,
851: rsup.end_dt
852: FROM IGS_RE_SPRVSR rsup
853: WHERE rsup.ca_person_id = p_ca_person_id AND
854: rsup.ca_sequence_number = p_ca_sequence_number AND
855: rsup.person_id = p_person_id AND
856: (p_sequence_number IS NULL OR

Line 886: END LOOP; --IGS_RE_SPRVSR

882: ELSE
883: RETURN FALSE;
884: END IF;
885: END IF;
886: END LOOP; --IGS_RE_SPRVSR
887: -- no records found.
888: -- Return the default value
889: RETURN TRUE;
890: EXCEPTION

Line 918: -- This module validates IGS_RE_SPRVSR.end_dt. Validations are:

914: p_legacy IN VARCHAR2 ,
915: p_message_name OUT NOCOPY VARCHAR2 )
916: RETURN BOOLEAN AS
917: BEGIN -- resp_val_rsup_end_dt
918: -- This module validates IGS_RE_SPRVSR.end_dt. Validations are:
919: -- end_dt >= start_dt
920: -- end_dt < any replacement supervisors
921: DECLARE
922: v_rsup_exists VARCHAR2(1);

Line 925: FROM IGS_RE_SPRVSR rsup

921: DECLARE
922: v_rsup_exists VARCHAR2(1);
923: CURSOR c_rsup IS
924: SELECT 'x'
925: FROM IGS_RE_SPRVSR rsup
926: WHERE rsup.ca_person_id = p_ca_person_id AND
927: rsup.ca_sequence_number = p_ca_sequence_number AND
928: rsup.replaced_person_id = p_person_id AND
929: rsup.replaced_sequence_number = p_sequence_number AND

Line 981: -- Validate if IGS_RE_SPRVSR_TYPE.IGS_RE_SPRVSR_TYPE is closed.

977: p_research_supervisor_type IN VARCHAR2 ,
978: p_message_name OUT NOCOPY VARCHAR2 )
979: RETURN BOOLEAN AS
980: BEGIN -- resp_val_rst_closed
981: -- Validate if IGS_RE_SPRVSR_TYPE.IGS_RE_SPRVSR_TYPE is closed.
982: DECLARE
983: CURSOR c_rst IS
984: SELECT 'x'
985: FROM IGS_RE_SPRVSR_TYPE rst

Line 985: FROM IGS_RE_SPRVSR_TYPE rst

981: -- Validate if IGS_RE_SPRVSR_TYPE.IGS_RE_SPRVSR_TYPE is closed.
982: DECLARE
983: CURSOR c_rst IS
984: SELECT 'x'
985: FROM IGS_RE_SPRVSR_TYPE rst
986: WHERE rst.research_supervisor_type = p_research_supervisor_type AND
987: rst.closed_ind = 'Y';
988: v_rst_exists VARCHAR2(1);
989: BEGIN