88: IGS_GE_MSG_STACK.ADD;
89: App_Exception.Raise_Exception;
90: END log_error ;
91:
92: FUNCTION deleted_alt_id_exists(p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
93: p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
94: p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE,
95: p_start_dt igs_pe_alt_pers_id.start_dt%TYPE) RETURN BOOLEAN AS
96:
89: App_Exception.Raise_Exception;
90: END log_error ;
91:
92: FUNCTION deleted_alt_id_exists(p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
93: p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
94: p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE,
95: p_start_dt igs_pe_alt_pers_id.start_dt%TYPE) RETURN BOOLEAN AS
96:
97: /******************************************************************
90: END log_error ;
91:
92: FUNCTION deleted_alt_id_exists(p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
93: p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
94: p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE,
95: p_start_dt igs_pe_alt_pers_id.start_dt%TYPE) RETURN BOOLEAN AS
96:
97: /******************************************************************
98: Created By : sjlaport
91:
92: FUNCTION deleted_alt_id_exists(p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
93: p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
94: p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE,
95: p_start_dt igs_pe_alt_pers_id.start_dt%TYPE) RETURN BOOLEAN AS
96:
97: /******************************************************************
98: Created By : sjlaport
99: Date Created By : 07-February-2005
107:
108:
109: CURSOR c_pe_alt_pers_del IS
110: SELECT *
111: FROM igs_pe_alt_pers_id
112: WHERE pe_person_id = p_person_id
113: AND person_id_type= p_alt_person_type
114: AND api_person_id = p_alt_pers_id
115: AND TRUNC(start_dt) = TRUNC(p_start_dt);
153: PROCEDURE import_alternate_person_id(p_interface_hesa_id igs_he_ucas_imp_int.interface_hesa_id%TYPE,
154: p_interface_id NUMBER,
155: p_status VARCHAR,
156: p_orgid NUMBER,
157: p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
158: p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
159: p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE) AS
160:
161: /******************************************************************
154: p_interface_id NUMBER,
155: p_status VARCHAR,
156: p_orgid NUMBER,
157: p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
158: p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
159: p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE) AS
160:
161: /******************************************************************
162: Created By : sjlaport
155: p_status VARCHAR,
156: p_orgid NUMBER,
157: p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
158: p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
159: p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE) AS
160:
161: /******************************************************************
162: Created By : sjlaport
163: Date Created By : 24-September-2004
179: l_insert_record BOOLEAN;
180:
181: -- sjlaport HEFD350 Process 4
182: -- Select the latest alternate person id details for the specified person
183: CURSOR c_pe_alt_pers(cp_person_id_type igs_pe_alt_pers_id.person_id_type%TYPE) IS
184: SELECT *
185: FROM igs_pe_alt_pers_id
186: WHERE pe_person_id = p_person_id
187: AND person_id_type= cp_person_id_type
181: -- sjlaport HEFD350 Process 4
182: -- Select the latest alternate person id details for the specified person
183: CURSOR c_pe_alt_pers(cp_person_id_type igs_pe_alt_pers_id.person_id_type%TYPE) IS
184: SELECT *
185: FROM igs_pe_alt_pers_id
186: WHERE pe_person_id = p_person_id
187: AND person_id_type= cp_person_id_type
188: AND (end_dt IS NULL OR start_dt <> end_dt)
189: ORDER BY start_dt DESC;
1165: c_interface_rec c_interface%ROWTYPE ;
1166:
1167:
1168: -- Get the person id for the passed alternate person id
1169: CURSOR c_alt_persid( cp_persid_type igs_pe_alt_pers_id.person_id_type%TYPE,
1170: cp_person_id igs_pe_alt_pers_id.api_person_id%TYPE ) IS
1171: SELECT party_number person_number
1172: FROM igs_pe_alt_pers_id , hz_parties
1173: WHERE person_id_type = cp_persid_type AND
1166:
1167:
1168: -- Get the person id for the passed alternate person id
1169: CURSOR c_alt_persid( cp_persid_type igs_pe_alt_pers_id.person_id_type%TYPE,
1170: cp_person_id igs_pe_alt_pers_id.api_person_id%TYPE ) IS
1171: SELECT party_number person_number
1172: FROM igs_pe_alt_pers_id , hz_parties
1173: WHERE person_id_type = cp_persid_type AND
1174: api_person_id = cp_person_id AND
1168: -- Get the person id for the passed alternate person id
1169: CURSOR c_alt_persid( cp_persid_type igs_pe_alt_pers_id.person_id_type%TYPE,
1170: cp_person_id igs_pe_alt_pers_id.api_person_id%TYPE ) IS
1171: SELECT party_number person_number
1172: FROM igs_pe_alt_pers_id , hz_parties
1173: WHERE person_id_type = cp_persid_type AND
1174: api_person_id = cp_person_id AND
1175: NVL(start_dt,SYSDATE) <= SYSDATE AND
1176: end_dt IS NULL AND