[Home] [Help]
91: v_enquiry_appl_number igr_i_appl_all.enquiry_appl_number%TYPE;
92: v_process_ind igr_i_appl_all.override_process_ind%TYPE;
93: v_complete_status igr_i_status_v.enquiry_status%TYPE;
94: v_acknowledge_status igr_i_status_v.enquiry_status%TYPE;
95: v_group_id igs_pe_persid_group.group_id%TYPE;
96: v_person_id_group_created VARCHAR2(1);
97: v_process_dt DATE;
98: v_enquiries_processed NUMBER DEFAULT 0;
99: v_enquiries_completed NUMBER DEFAULT 0;
105: l_msg_count NUMBER;
106: l_s_enq_stat igr_i_appl_v.s_enquiry_status%TYPE;
107: PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
108:
109: v_group_cd igs_pe_persid_group_all.group_cd%TYPE;
110:
111: -- Modified the following cursor as part of build of CRM recruitment changes. Bug: 2664699
112: CURSOR c_es (cp_enquiry_status as_statuses_vl.status_code%TYPE) IS
113: SELECT es.enquiry_status
242: (epi.actual_avail_to_date >= SYSDATE) AND
243: NVL(eapmpi.donot_mail_ind,'N') = 'N';
244:
245: --Cursor to get the group code of the Person ID Group for a given group id (rghosh, bug#3973942: APC Integration Build)
246: CURSOR c_get_group_cd(cp_group_id IGS_PE_PERSID_GROUP_ALL.GROUP_ID%TYPE) IS
247: SELECT group_cd
248: FROM igs_pe_persid_group_all
249: WHERE group_id = cp_group_id;
250:
244:
245: --Cursor to get the group code of the Person ID Group for a given group id (rghosh, bug#3973942: APC Integration Build)
246: CURSOR c_get_group_cd(cp_group_id IGS_PE_PERSID_GROUP_ALL.GROUP_ID%TYPE) IS
247: SELECT group_cd
248: FROM igs_pe_persid_group_all
249: WHERE group_id = cp_group_id;
250:
251:
252: /** IDOPA2 New cursors added to take care of package items if p_inq_info_type is not null **/
341: (reverse chronological order - newest change first)
342: *****************************************************************************/
343:
344: BEGIN -- create_person_id_group_secur
345: -- After creating the IGS_PE_PERSID_GROUP record, create a security record giving
346: -- select access on it, to the default user specified in the parameters..
347: DECLARE
348: lv_rowid VARCHAR2(25);
349: BEGIN
382: *****************************************************************************/
383:
384: BEGIN -- create_person_id_group
385: DECLARE
386: l_group_cd igs_pe_persid_group.group_cd%TYPE;
387: l_req_id NUMBER;
388:
389: -- cst_adm_enquiry CONSTANT VARCHAR2(10) := 'ADM_ENQY';
390: -- kamohan Bug 2382418
391: -- Changed from ADMISSION ENQUIRY to ADMISSION INQUIRY
392:
393: cst_admission_enquiry CONSTANT VARCHAR2(20) := 'ADMISSION INQUIRY';
394: v_user_id IGS_PE_PERSON.person_id%TYPE;
395: v_group_id IGS_PE_PERSID_GROUP.group_id%TYPE;
396: lv_rowid VARCHAR2(25);
397: l_org_id NUMBER(15);
398: CURSOR c_get_next_seq IS
399: SELECT IGS_PE_PERSID_GROUP_GP_ID_S.NEXTVAL
395: v_group_id IGS_PE_PERSID_GROUP.group_id%TYPE;
396: lv_rowid VARCHAR2(25);
397: l_org_id NUMBER(15);
398: CURSOR c_get_next_seq IS
399: SELECT IGS_PE_PERSID_GROUP_GP_ID_S.NEXTVAL
400: FROM DUAL;
401: BEGIN
402: l_req_id := FND_GLOBAL.CONC_REQUEST_ID();
403: l_group_cd := igs_ad_gen_012.ret_group_cd();
407: OPEN c_get_next_seq;
408: FETCH c_get_next_seq INTO v_group_id;
409: CLOSE c_get_next_seq;
410: l_org_id := igs_ge_gen_003.get_org_id;
411: igs_pe_persid_group_pkg.Insert_Row (
412: X_Mode => 'R',
413: X_RowId => lv_rowid,
414: X_Group_Id => v_group_id,
415: X_Group_Cd => l_group_cd,
420: X_Comments => Null ,
421: X_Org_Id => l_org_id
422: );
423:
424: -- Since create IGS_PE_PERSID_GROUP is called several times and
425: -- create_person_id_group is used in each case. It made sense to have the
426: -- call actually in the create IGS_PE_PERSID_GROUP procedure.
427: -- After creating the IGS_PE_PERSID_GROUP record, create a security record giving
428: -- select access on it, to the default user specified in the parameters..
422: );
423:
424: -- Since create IGS_PE_PERSID_GROUP is called several times and
425: -- create_person_id_group is used in each case. It made sense to have the
426: -- call actually in the create IGS_PE_PERSID_GROUP procedure.
427: -- After creating the IGS_PE_PERSID_GROUP record, create a security record giving
428: -- select access on it, to the default user specified in the parameters..
429: create_person_id_group_secur(v_group_id);
430: -- Note : p_group_id is an output parameter which returns the group_id so
423:
424: -- Since create IGS_PE_PERSID_GROUP is called several times and
425: -- create_person_id_group is used in each case. It made sense to have the
426: -- call actually in the create IGS_PE_PERSID_GROUP procedure.
427: -- After creating the IGS_PE_PERSID_GROUP record, create a security record giving
428: -- select access on it, to the default user specified in the parameters..
429: create_person_id_group_secur(v_group_id);
430: -- Note : p_group_id is an output parameter which returns the group_id so
431: -- that it can be used to trigger other jobs. This parameter has been
463: *****************************************************************************/
464: BEGIN
465: DECLARE
466: v_user_id igs_pe_person.person_id%TYPE;
467: v_group_id igs_pe_persid_group.group_id%TYPE;
468: lv_rowid VARCHAR2(25);
469: l_org_id NUMBER(15);
470: CURSOR c_pigm (
471: cp_group_id igs_pe_persid_group.group_id%TYPE,
467: v_group_id igs_pe_persid_group.group_id%TYPE;
468: lv_rowid VARCHAR2(25);
469: l_org_id NUMBER(15);
470: CURSOR c_pigm (
471: cp_group_id igs_pe_persid_group.group_id%TYPE,
472: cp_person_id igs_pe_person.person_id%TYPE) IS
473: SELECT 'X'
474: FROM igs_pe_prsid_grp_mem pigm
475: WHERE pigm.group_id = cp_group_id AND