[Home] [Help]
79: l_prog_label VARCHAR2(100);
80: l_label VARCHAR2(100);
81: l_debug_str VARCHAR2(2000);
82: l_enable_log VARCHAR2(1);
83: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
84: -- Cursor to select all the pending records.
85: CURSOR per_type_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86: SELECT pty.*, i.person_id
87: FROM igs_pe_type_int pty,
81: l_debug_str VARCHAR2(2000);
82: l_enable_log VARCHAR2(1);
83: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
84: -- Cursor to select all the pending records.
85: CURSOR per_type_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86: SELECT pty.*, i.person_id
87: FROM igs_pe_type_int pty,
88: igs_ad_interface_all i
89: WHERE pty.interface_id = i.interface_id AND
84: -- Cursor to select all the pending records.
85: CURSOR per_type_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86: SELECT pty.*, i.person_id
87: FROM igs_pe_type_int pty,
88: igs_ad_interface_all i
89: WHERE pty.interface_id = i.interface_id AND
90: pty.status = '2' AND
91: pty.interface_run_id = cp_interface_run_id AND
92: i.interface_run_id = cp_interface_run_id;
91: pty.interface_run_id = cp_interface_run_id AND
92: i.interface_run_id = cp_interface_run_id;
93:
94: -- Cursor to check for duplicate record
95: CURSOR dup_per_type_cur(cp_person_id igs_ad_interface_all.person_id%TYPE,
96: cp_person_type_code igs_pe_type_int.person_type_code%TYPE,
97: cp_start_date igs_pe_type_int.start_date%TYPE) IS
98: SELECT rowid,type_instance_id, end_date,emplmnt_category_code
99: FROM igs_pe_typ_instances_all
111: WHERE system_type = cp_system_type;
112:
113: -- Cursor to check whether any Staff/Faculty records are present
114: CURSOR type_exist_cur(cp_person_type_code igs_pe_person_types.person_type_code%TYPE,
115: cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
116: SELECT 'X'
117: FROM igs_pe_type_int pty
118: WHERE pty.status = '2' AND
119: pty.person_type_code = cp_person_type_code AND
140: SELECT user_name
141: FROM fnd_user
142: WHERE user_id = cp_user_id;
143:
144: CURSOR date_overlap(cp_person_id igs_ad_interface.person_id%TYPE,
145: cp_person_type_code igs_pe_type_int.person_type_code%TYPE,
146: cp_start_date igs_pe_type_int.start_date%TYPE,
147: cp_end_date igs_pe_type_int.end_date%TYPE,
148: cp_default_date DATE) IS
154: OR cp_start_date BETWEEN start_date AND NVL(end_date,cp_default_date)
155: OR ( cp_start_date < start_date AND
156: NVL(end_date,cp_default_date) < NVL(cp_end_date,cp_default_date)));
157:
158: CURSOR emp_cat_status(cp_person_id igs_ad_interface.person_id%TYPE,
159: cp_start_date igs_pe_type_int.start_date%TYPE,
160: cp_end_date igs_pe_type_int.end_date%TYPE,
161: cp_default_date DATE) IS
162: SELECT NULL
314: SELECT user_name
315: FROM fnd_user
316: WHERE user_id = cp_user_id;
317:
318: CURSOR date_overlap(cp_person_id igs_ad_interface.person_id%TYPE,
319: cp_person_type_code igs_pe_type_int.person_type_code%TYPE,
320: cp_start_date igs_pe_type_int.start_date%TYPE,
321: cp_end_date igs_pe_type_int.end_date%TYPE,
322: cp_default_date DATE) IS
329: OR cp_start_date BETWEEN start_date AND NVL(end_date,cp_default_date)
330: OR ( cp_start_date < start_date AND
331: NVL(end_date,cp_default_date) < NVL(cp_end_date,cp_default_date)));
332:
333: CURSOR emp_cat_status(cp_person_id igs_ad_interface.person_id%TYPE,
334: cp_start_date igs_pe_type_int.start_date%TYPE,
335: cp_end_date igs_pe_type_int.end_date%TYPE,
336: cp_default_date DATE) IS
337: SELECT null FROM igs_pe_typ_instances_all typ,igs_pe_person_types sys
678: pti.interface_run_id = l_interface_run_id AND
679: pti.match_ind IS NULL AND
680: EXISTS (SELECT 1
681: FROM igs_pe_typ_instances_all pi,
682: igs_ad_interface_all ai
683: WHERE pti.interface_id = ai.interface_id AND
684: ai.interface_run_id = l_interface_run_id AND
685: ai.person_id = pi.person_id AND
686: UPPER(pti.person_type_code) = UPPER(pi.person_type_code) AND
721: pti.match_ind IS NULL AND
722: EXISTS
723: (SELECT 1
724: FROM igs_pe_typ_instances_all pi,
725: igs_ad_interface_all ai
726: WHERE pti.interface_id = ai.interface_id AND
727: ai.interface_run_id = l_interface_run_id AND
728: ai.person_id = pi.person_id AND
729: NVL(UPPER(pti.emplmnt_category_code),'*!*') = NVL(UPPER(pi.emplmnt_category_code),'*!*') AND
741: SET status='3',
742: match_ind='20',
743: dup_type_instance_id=(SELECT pi.type_instance_id
744: FROM igs_pe_typ_instances_all pi,
745: igs_ad_interface_all ai
746: WHERE pti.interface_id = ai.interface_id AND
747: ai.interface_run_id = l_interface_run_id AND
748: ai.person_id = pi.person_id AND
749: UPPER(pti.person_type_code)=UPPER(pi.person_type_code) AND
753: pti.match_ind IS NULL AND
754: EXISTS
755: (SELECT 1
756: FROM igs_pe_typ_instances_all pi,
757: igs_ad_interface_all ai
758: WHERE pti.interface_id=ai.interface_id AND
759: ai.interface_run_id = l_interface_run_id AND
760: ai.person_id = pi.person_id AND
761: UPPER(pti.person_type_code) = UPPER(pi.person_type_code) AND
862:
863:
864:
865: PROCEDURE PRC_PE_ACAD_HIST (
866: p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
867: p_rule VARCHAR2,
868: p_enable_log VARCHAR2
869: ) AS
870:
2047:
2048:
2049:
2050: PROCEDURE prc_pe_cred_details (
2051: p_interface_run_id igs_ad_interface_all.interface_run_id%TYPE,
2052: p_rule VARCHAR2,
2053: p_enable_log VARCHAR2
2054: ) AS
2055:
2066:
2067:
2068: CURSOR c_pe_cr_cur IS
2069: SELECT cst_insert dmlmode, cred.rowid, ad.person_id, cred.*
2070: FROM IGS_AD_INTERFACE_ALL ad , IGS_PE_CRED_INT cred
2071: WHERE cred.interface_run_id = p_interface_run_id
2072: AND ad.status IN ('1', '4')
2073: AND cred.interface_id = ad.interface_id
2074: AND cred.status = '2'
2080: OR ( p_rule = 'R' AND cred.match_ind IN ('16', '25') )
2081: )
2082: UNION ALL
2083: SELECT cst_update dmlmode, cred.rowid, ad.person_id, cred.*
2084: FROM IGS_AD_INTERFACE_ALL ad , IGS_PE_CRED_INT cred
2085: WHERE cred.interface_run_id = p_interface_run_id
2086: AND ad.status IN ('1', '4')
2087: AND cred.interface_id = ad.interface_id
2088: AND cred.status = '2'
2516: WHERE interface_run_id = p_interface_run_id
2517: AND status = '2'
2518: AND 1 < ( SELECT COUNT(*)
2519: FROM igs_pe_credentials cred_oss
2520: WHERE person_id = (SELECT person_id FROM igs_ad_interface_all
2521: WHERE interface_id = cred.interface_id)
2522: AND credential_type_id = cred.credential_type_id
2523: AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2524: TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
2537: AND status = '2'
2538: AND NVL(match_ind, '15') IN ('15', '21')
2539: AND 1 < ( SELECT COUNT(*)
2540: FROM igs_pe_credentials cred_oss
2541: WHERE person_id = (SELECT person_id FROM igs_ad_interface_all
2542: WHERE interface_id = cred.interface_id)
2543: AND credential_type_id = cred.credential_type_id
2544: AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2545: TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
2555: , match_ind = '19'
2556: WHERE interface_run_id = p_interface_run_id
2557: AND status = '2'
2558: AND EXISTS ( SELECT 1 FROM IGS_PE_CREDENTIALS
2559: WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
2560: WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
2561: AND credential_type_id = cred.credential_type_id
2562: AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2563: TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01')))
2612: AND NVL (match_ind, '15') = '15'
2613: AND EXISTS ( SELECT 'x'
2614: FROM
2615: igs_pe_credentials
2616: WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
2617: WHERE interface_id = cred.interface_id
2618: AND interface_run_id = p_interface_run_id)
2619: AND credential_type_id = cred.credential_type_id
2620: AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2636: SET
2637: status = '3'
2638: , match_ind = '20'
2639: , dup_credential_id= (SELECT credential_id FROM igs_pe_credentials
2640: WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
2641: WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
2642: AND credential_type_id = cred.credential_type_id
2643: AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2644: TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))))
2645: WHERE interface_run_id = p_interface_run_id
2646: AND status = '2'
2647: AND NVL (match_ind, '15') = '15'
2648: AND EXISTS (SELECT credential_id FROM igs_pe_credentials
2649: WHERE person_id IN (SELECT PERSON_ID FROM IGS_AD_INTERFACE_ALL
2650: WHERE interface_id = cred.interface_id AND interface_run_id = p_interface_run_id)
2651: AND credential_type_id = cred.credential_type_id
2652: AND TRUNC(NVL(date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))) =
2653: TRUNC(NVL(cred.date_received, IGS_GE_DATE.IGSDATE('1700/01/01'))));