1 PACKAGE BODY IGS_EN_VAL_API AS
2 /* $Header: IGSEN23B.pls 120.1 2005/08/29 08:01:12 appldev ship $ */
3
4 -------------------------------------------------------------------------------------------
5 --Change History:
6 --Who When What
7 --npalanis 06-JAN-2002 BUG NO. 2170429 .The cursor c_sfs removed
8 --smadathi 29-AUG-2001 Bug No. 1956374 .The function GENP_VAL_STRT_END_DT removed
9 --smadathi 24-AUG-2001 Bug No. 1956374 .The function GENP_VAL_SDTT_SESS removed
10 --msrinivi 24-AUG-2001 Bug No. 1956374 .The function GENP_SET_ROWID removed
11 --vrathi 18-may-2003 Bug No. 2928745 : end date check cursor modified to include person_id_type
12 -- ssaleem 17-Sept-2004 Bug No. 3787210 : Added Closed Ind in table IGS_PE_PERSON_ID_TYP
13 -------------------------------------------------------------------------------------------
14
15
16 --
17 -- Validate that an entry exist in s_disable_table_trigger.
18 --
19 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
20 PROCEDURE genp_prc_clear_rowid
21 AS
22 BEGIN
23 -- initialise
24 gt_rowid_table := gt_empty_table;
25 gv_table_index := 1;
26 END genp_prc_clear_rowid;
27 --
28 -- Routine to process api rowids in a PL/SQL TABLE for the current commit
29 FUNCTION enrp_prc_api_rowids(
30 p_inserting IN BOOLEAN ,
31 p_updating IN BOOLEAN ,
32 p_deleting IN BOOLEAN ,
33 p_message_name OUT NOCOPY VARCHAR2)
34 RETURN BOOLEAN AS
35 v_index BINARY_INTEGER;
36
37 r_alternate_person_id IGS_PE_ALT_PERS_ID%ROWTYPE;
38 cst_pay_adv_no CONSTANT VARCHAR2(10) := 'PAY_ADV_NO';
39 v_dummy VARCHAR2(1);
40 CURSOR c_pit (cp_person_id_type IGS_PE_PERSON_ID_TYP.person_id_type%TYPE) IS
41 SELECT 'x'
42 FROM IGS_PE_PERSON_ID_TYP pit
43 WHERE pit.person_id_type = cp_person_id_type AND
44 pit.s_person_id_type = cst_pay_adv_no AND
45 pit.closed_ind = 'N' ;
46 BEGIN
47 -- Process saved rows.
48 FOR v_index IN 1..gv_table_index - 1
49 LOOP
50 BEGIN
51 SELECT *
52 INTO r_alternate_person_id
53 FROM IGS_PE_ALT_PERS_ID
54 WHERE rowid = gt_rowid_table(v_index);
55 EXCEPTION
56 WHEN OTHERS THEN
57 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
58 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_API.enrp_prc_api_rowids');
59 IGS_GE_MSG_STACK.ADD;
60 App_Exception.Raise_Exception;
61
62 END;
63 -- Validate the alternate person id when a 'PAY_ADV_NO' is unique.
64 OPEN c_pit (r_alternate_person_id.person_id_type);
65 FETCH c_pit INTO v_dummy;
66 IF (c_pit%FOUND) THEN
67 CLOSE c_pit;
68 IF IGS_EN_VAL_API.enrp_val_api_pan (
69 r_alternate_person_id.pe_person_id,
70 r_alternate_person_id.api_person_id,
71 p_message_name) = FALSE THEN
72 RETURN FALSE;
73 END IF;
74 ELSE
75 CLOSE c_pit;
76 END IF;
77 END LOOP;
78 RETURN TRUE;
79 END enrp_prc_api_rowids;
80 --
81 -- Validate the payment advice number is unique.
82 FUNCTION enrp_val_api_pan(
83 p_person_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE ,
84 p_pay_advice_number IGS_PE_ALT_PERS_ID.api_person_id%TYPE ,
85 p_message_name OUT NOCOPY varchar2)
86 RETURN BOOLEAN AS
87
88 BEGIN -- enrp_val_api_pan
89 -- Validate that IGS_PE_ALT_PERS_ID where s_person_id_type = 'PAY_ADV_NO' is
90 -- unique to the person and that it does not clash with any payment_advice
91 -- number in IGS_FI_STDNT_FEE_SPN
92 DECLARE
93 cst_pay_adv_no CONSTANT VARCHAR2(10) := 'PAY_ADV_NO';
94 v_dummy VARCHAR2(1);
95 CURSOR c_api_pit IS
96 SELECT 'x'
97 FROM IGS_PE_ALT_PERS_ID api,
98 IGS_PE_PERSON_ID_TYP pit
99 WHERE api.api_person_id = p_pay_advice_number AND
100 api.pe_person_id <> p_person_id AND
101 api.person_id_type = pit.person_id_type AND
102 pit.s_person_id_type = cst_pay_adv_no;
103
104 BEGIN
105 -- validate IGS_PE_ALT_PERS_ID where
106 -- s_person_id_type = 'PAY_ADV_NO' is unique to this IGS_PE_PERSON
107 OPEN c_api_pit;
108 FETCH c_api_pit INTO v_dummy;
109 IF c_api_pit%FOUND THEN
110 CLOSE c_api_pit;
111 p_message_name := 'IGS_FI_PYMT_ADVICE_NUM_ALLOCA';
112 RETURN FALSE;
113 END IF;
114 CLOSE c_api_pit;
115
116 -- pay advise number is valid
117 p_message_name := null;
118 RETURN TRUE;
119 EXCEPTION
120 WHEN OTHERS THEN
121 IF (c_api_pit%ISOPEN) THEN
122 CLOSE c_api_pit;
123 END IF;
124
125 RAISE;
126 END;
127 EXCEPTION
128 WHEN OTHERS THEN
129 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
130 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_API.enrp_val_api_pan');
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133
134 END enrp_val_api_pan;
135
136 FUNCTION val_overlap_api(
137 p_person_id IGS_PE_ALT_PERS_ID_V.PE_PERSON_ID%TYPE)
138 RETURN BOOLEAN AS
139 ------------------------------------------------------------------------------------------
140 --Created by : pkpatel
141 --Date created: 8-JUN-2002
142 -- bug no: Bug No: 2402077
143 --Purpose:To check Overlapping period for Person Id Types, if more than exists it returns FALSE
144 -- else TRUE.
145 --Known limitations/enhancements and/or remarks:
146 --
147 --Change History:
148 --Who When What
149 --kpadiyar 27-JAN-2003 Changed >= B.START_DT to > B.sTART_DT for bug 2726415
150 --ssawhney end date overlapp modified to include <=
151 -- vrathi 18-may-2003 Bug 2928745 : end date check cursor modified to include person_id_type
152 --vrathi 20-may-2003 Changed NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) <=
153 -- NVL(B.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) comparison to <= from <
154 --askapoor 31-jan-05 Added NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) <> A.START_DT
155 -- in val_overlap_api
156 ----------------------------------------------------------------------------------------------
157
158 CURSOR c_validate_overlap_dates ( cp_person_id IGS_PE_ALT_PERS_ID_V.PE_PERSON_ID%TYPE)
159 IS
160 SELECT COUNT(1)
161 FROM IGS_PE_ALT_PERS_ID A,
162 IGS_PE_ALT_PERS_ID B
163 WHERE A.pe_person_id = cp_person_id
164 AND A.pe_person_id = B.pe_person_id
165 AND A.person_id_type = B.person_id_type
166 AND A.ROWID <> B.ROWID
167 AND A.START_DT < NVL(B.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD'))
168 AND NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) <> A.START_DT
169 AND NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) > B.START_DT
170 AND (
171 NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) <= NVL(B.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD'))
172 OR
173 A.END_DT <= B.END_DT
174 );
175
176
177
178 ln_count NUMBER(10);
179
180 CURSOR c_end_chk (cp_person_id IGS_PE_ALT_PERS_ID_V.PE_PERSON_ID%TYPE)
181 IS
182 SELECT count('x')
183 FROM igs_pe_alt_pers_id
184 WHERE pe_person_id = cp_person_id
185 AND end_dt IS NULL
186 GROUP BY pe_person_id,PERSON_ID_TYPE
187 HAVING count('x') >1;
188
189 BEGIN
190 OPEN c_end_chk ( p_person_id );
191 FETCH c_end_chk INTO ln_count;
192 CLOSE c_end_chk;
193
194 IF ln_count > 1 THEN
195 ln_count := 0;
196 RETURN FALSE;
197 END IF;
198
199
200 OPEN c_validate_overlap_dates ( p_person_id );
201 FETCH c_validate_overlap_dates INTO ln_count;
202 CLOSE c_validate_overlap_dates;
203
204 -- If ln_count is greater than then it indicates that records with overlapping dates exist in the database
205 -- In such a situation display an error message
206 IF (ln_count > 0) THEN
207 RETURN FALSE;
208 END IF;
209
210 RETURN TRUE;
211
212 END val_overlap_api;
213
214 FUNCTION val_ssn_overlap_api(
215 p_person_id IGS_PE_ALT_PERS_ID_V.PE_PERSON_ID%TYPE)
216 RETURN BOOLEAN AS
217 ------------------------------------------------------------------------------------------
218 --Created by : pkpatel
219 --Date created: 8-JUN-2002
220 -- bug no: Bug No: 2402077
221 --Purpose:To check Overlapping period for Person Id Types associated with System Person ID type SSN, if more than exists it returns FALSE
222 -- else FALSE.
223 --Known limitations/enhancements and/or remarks:
224 --
225 --Change History:
226 --Who When What
227 --kpadiyar 27-JAN-2003 Changed >= B.START_DT to > B.START_DT for bug 2726415
228 -- ssawhney end date voerlap modified to have <=
229 -- vrathi 18-may-2003 Bug 2928745 : end date check cursor modified to include person_id_type
230 --askapoor 31-jan-05 Added NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) <> A.START_DT
231 -- in val_ssn_overlap_api
232 ----------------------------------------------------------------------------------------------
233 CURSOR val_overlap_dt_ssn_cur ( cp_person_id IGS_PE_ALT_PERS_ID_V.PE_PERSON_ID%TYPE)
234 IS
235 SELECT COUNT(1)
236 FROM IGS_PE_ALT_PERS_ID A,
237 IGS_PE_ALT_PERS_ID B
238 WHERE A.pe_person_id = cp_person_id
239 AND A.pe_person_id = B.pe_person_id
240 AND A.person_id_type IN (SELECT person_id_type FROM IGS_PE_PERSON_ID_TYP WHERE s_person_id_type = 'SSN')
241 AND B.person_id_type IN (SELECT person_id_type FROM IGS_PE_PERSON_ID_TYP WHERE s_person_id_type = 'SSN')
242 AND A.ROWID <> B.ROWID
243 AND A.START_DT < NVL(B.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD'))
244 AND NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) <> A.START_DT
245 AND NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) > B.START_DT
246 AND (
247 NVL(A.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD')) <= NVL(B.END_DT,TO_DATE('4712/12/31','YYYY/MM/DD'))
248 OR
249 A.END_DT <= B.END_DT
250 );
251
252 l_count NUMBER := 0;
253
254 CURSOR c_end_chk (cp_person_id IGS_PE_ALT_PERS_ID_V.PE_PERSON_ID%TYPE)
255 IS
256 SELECT count('x')
257 FROM igs_pe_alt_pers_id
258 WHERE pe_person_id = cp_person_id
259 AND PERSON_ID_TYPE IN ( select person_id_type from igs_pe_person_id_typ
260 where S_PERSON_ID_TYPE='SSN' )
261 AND end_dt IS NULL
262 GROUP BY pe_person_id,PERSON_ID_TYPE;
263
264 BEGIN
265
266 OPEN c_end_chk ( p_person_id );
267 FETCH c_end_chk INTO l_count;
268 CLOSE c_end_chk;
269
270 IF l_count > 1 THEN
271 l_count := 0;
272 RETURN FALSE;
273 END IF;
274
275 OPEN val_overlap_dt_ssn_cur ( p_person_id);
276 FETCH val_overlap_dt_ssn_cur INTO l_count;
277 CLOSE val_overlap_dt_ssn_cur;
278
279 -- If l_count is greater than 0 then it indicates that records with overlapping dates exist in the database
280 -- In such a situation display an error message
281 IF (l_count > 0) THEN
282 RETURN FALSE;
283 END IF;
284
285 RETURN TRUE;
286
287 END val_ssn_overlap_api;
288
289 FUNCTION fm_equal(
290 p_format_mask IN igs_pe_person_id_typ.format_mask%TYPE,
291 p_frmt_msk_copy IN igs_pe_person_id_typ.format_mask%TYPE)
292 RETURN BOOLEAN AS
293 ------------------------------------------------------------------------------------------
294 --Created by : sarakshi
295 --Date created: 21-SEP-2001
296 -- bug no:2000408
297 --Purpose:To check that the format mask field is 9 or X or any of the special char
298 -- ('-','_','+','=',')','(','*','&','^','%','$','#','@','!','`','~','/','\',' '),if it is
299 -- then this function returns true else false.
300 --Known limitations/enhancements and/or remarks:
301 --
302 --Change History:
303 --Who When What
304 ----------------------------------------------------------------------------------------------
305 l_str_len NUMBER := LENGTH(p_format_mask);
306 l_trans_s IGS_PE_PERSON_ID_TYP.FORMAT_MASK%TYPE := NULL;
307 l_chr VARCHAR2(1);
308 BEGIN
309 FOR i IN 1..l_str_len LOOP
310 l_chr := SUBSTR(p_format_mask,i,1);
311 IF l_chr IN ('0','1','2','3','4','5','6','7','8','9') THEN
312 l_trans_s :=l_trans_s||'9';
313 ELSIF l_chr IN ('-','_','+','=',')','(','*','&','^','%','$','#','@','!','`','~','/','\',' ') THEN
314 l_trans_s :=l_trans_s||l_chr;
315 ELSE
316 l_trans_s:=l_trans_s||'X';
317 END IF;
318 END LOOP;
319
320 IF p_frmt_msk_copy = l_trans_s THEN
321 RETURN TRUE;
322 ELSE
323 RETURN FALSE;
324 END IF;
325 END fm_equal;
326
327 FUNCTION unformat_api(
328 p_api_pers_id IN igs_pe_alt_pers_id.api_person_id%TYPE)
329 RETURN VARCHAR2 AS
330 ------------------------------------------------------------------------------------------
331 --Created by : pkpatel
332 --Date created: 06-JUN-2002
333 --
334 --Purpose: Bug No: 2402077. To unformat the formatted Alternate Person ID field from the special char
335 -- ('-','_','+','=',')','(','*','&','^','%','$','#','@','!','`','~','/','\',' '). It returns the Unformatted
336 -- person ID Type.
337 --Known limitations/enhancements and/or remarks:
338 --
339 --Change History:
340 --Who When What
341 ----------------------------------------------------------------------------------------------
342
343 l_str_len NUMBER := LENGTH(p_api_pers_id);
344 l_api_pers_id_gen igs_pe_alt_pers_id.api_person_id%TYPE := NULL;
345 l_chr VARCHAR2(1);
346
347 BEGIN
348 FOR i IN 1..l_str_len LOOP
349 l_chr := SUBSTR(p_api_pers_id,i,1);
350 IF l_chr IN ('0','1','2','3','4','5','6','7','8','9') THEN
351 l_api_pers_id_gen := l_api_pers_id_gen||l_chr;
352 ELSIF l_chr IN ('-','_','+','=',')','(','*','&','^','%','$','#','@','!','`','~','/','\',' ') THEN
353 NULL;
354 ELSE
355 l_api_pers_id_gen := l_api_pers_id_gen||l_chr;
356 END IF;
357 END LOOP;
358
359 RETURN l_api_pers_id_gen;
360
361 END unformat_api;
362
363 FUNCTION fm_equal_wrap(
364 p_format_mask IN igs_pe_person_id_typ.format_mask%TYPE,
365 p_frmt_msk_copy IN igs_pe_person_id_typ.format_mask%TYPE)
366 RETURN NUMBER AS
367 ------------------------------------------------------------------------------------------
368 --Created by : pkpatel
369 --Date created: 29-Aug-2005
370 --Purpose: Wrapper method to be called from Self -Service
371 --Known limitations/enhancements and/or remarks:
372 --
373 --Change History:
374 --Who When What
375 ----------------------------------------------------------------------------------------------
376 l_result boolean;
377 BEGIN
378 l_result := fm_equal(p_format_mask,p_frmt_msk_copy);
379 if l_result then
380 return 1;
381 else
382 return 0;
383 end if;
384 END fm_equal_wrap;
385
386 END igs_en_val_api;
[Home] [Help]