1 Package body per_us_validate_pei as
2 /* $Header: peuspeiv.pkb 120.0 2005/05/31 22:43:55 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_us_validate_pei.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |-----------------------< chk_us_visa_rows >-------------------------|
13 -- ----------------------------------------------------------------------------
14 -- Description:
15 -- Record level validation for US Visa PEI types.
16 -- Detail validation rules is documented in
17 -- $DOCS_TOP/per/projects/visa/visahld.doc
18 --
19 -- Pre Conditions:
20 --
21 --
22 -- In Parameters:
23 -- person_id, information_type, pei_information5, pei_information7,
24 -- pei_information8, pei_information10, pei_information11
25 --
26 -- Post Success:
27 -- Processing continues.
28 --
29 -- Post Failure:
30 -- An application error will be raised and processing is terminated.
31 --
32 -- Access Status:
33 -- Internal Table Handler Use Only.
34 -- ----------------------------------------------------------------------------
35 Procedure chk_us_visa_rows ( p_person_id number,
36 p_information_type varchar2,
37 p_pei_information5 varchar2,
38 p_pei_information6 varchar2,
39 p_pei_information7 varchar2,
40 p_pei_information8 varchar2,
41 p_pei_information9 varchar2,
42 p_pei_information10 varchar2,
43 p_pei_information11 varchar2) is
44 --
45 l_proc varchar2(100) := g_package||'chk_us_visa_rows';
46 l_counter number := 0;
47 a_end_date date;
48
49 --
50 -- Validation of START_DATE and END_DATE for PER_US_PASSPORT_DETAILS,
51 -- PER_US_VISA_DETAILS, and PER_US_VISIT_HISTORY
52 -- WWBUG 2097669
53 -- Add fnd_date.date_to_canonial to pei_informatin7 and 8
54 CURSOR c1 (p_person_id number,
55 p_information_type varchar2) is
56 SELECT 1
57 FROM per_people_extra_info
58 WHERE person_id = p_person_id
59 AND information_type = p_information_type
60 AND fnd_date.canonical_to_date(pei_information7) >
61 fnd_date.canonical_to_date(nvl(pei_information8,'4712/12/31 00:00:00'));
62 --
63 -- Non-duplicate rows on INCOME_CODE of PER_US_PAYROLL_DETAILS
64 CURSOR c2 (p_person_id number,
65 p_information_type varchar2,
66 p_pei_information5 varchar2) is
67 SELECT count(*)
68 FROM per_people_extra_info
69 WHERE person_id = p_person_id
70 AND information_type = p_information_type
71 AND pei_information5 = p_pei_information5
72 HAVING count(*) > 1;
73 --
74 -- Unique CURRENT Visa row of PER_US_VISA_DETAILS
75 CURSOR c3 (p_person_id number,
76 p_information_type varchar2) is
77 SELECT count(*)
78 FROM per_people_extra_info
79 WHERE person_id = p_person_id
80 AND information_type = p_information_type
81 AND pei_information10 = 'Y'
82 HAVING count(*) > 1;
83 --
84 -- Non-duplicate rows on PER_US_PASSPORT_DETAILS,PER_US_VISA_DETAILS
85 CURSOR c4 (p_person_id number,
86 p_information_type varchar2,
87 p_pei_information5 varchar2,
88 p_pei_information6 varchar2) is
89 SELECT count(*)
90 FROM per_people_extra_info
91 WHERE person_id = p_person_id
92 AND information_type = p_information_type
93 AND pei_information5 = p_pei_information5
94 AND pei_information6 = p_pei_information6
95 HAVING count(*) > 1;
96 --
97 -- Non-overlap Validation for START_DATE and END_DATE of PER_US_VISIT_HISTORY
98 CURSOR next_visit (p_person_id number,
99 p_information_type varchar2) is
100 SELECT fnd_date.canonical_to_date(pei_information7) start_date,
101 fnd_date.canonical_to_date(nvl(pei_information8,'4712/12/31 00:00:00'))
102 end_date
103 FROM per_people_extra_info
104 WHERE person_id = p_person_id
105 AND information_type = p_information_type
106 ORDER BY start_date asc;
107 --
108
109 Begin
110 --
111 hr_utility.set_location('Entering:'||l_proc, 10);
112 --
113 if p_information_type IN ('PER_US_PASSPORT_DETAILS',
114 'PER_US_VISA_DETAILS', 'PER_US_VISIT_HISTORY') then
115 --
116 -- check for end date later than start date
117 hr_utility.set_location(l_proc, 15);
118 for c1_rec in c1 (p_person_id,
119 p_information_type) loop
120
121 --
122 -- raise error if start date later than end date
123 hr_utility.set_message(800, 'PER_INCORRECT_START_END_DATES');
124 hr_utility.raise_error;
125 end loop;
126 end if;
127
128 hr_utility.set_location(l_proc, 20);
129
130 if p_information_type IN ('PER_US_PASSPORT_DETAILS',
131 'PER_US_VISA_DETAILS') then
132 --
133 -- check for duplicate record
134 hr_utility.set_location(l_proc, 25);
135 for c4_rec in c4 (p_person_id,
136 p_information_type,
137 p_pei_information5,
138 p_pei_information6 ) loop
139 --
140 -- raise error if a duplicate record exists
141 hr_utility.set_message(800, 'PER_PEI_VISA_DUP_RECORD');
142 hr_utility.raise_error;
143 end loop;
144 end if;
145
146 hr_utility.set_location(l_proc, 30);
147
148 if p_information_type = 'PER_US_PAYROLL_DETAILS' then
149 --
150 -- check for INCOME_CODE duplicate record
151 hr_utility.set_location(l_proc, 35);
152 for c2_rec in c2 (p_person_id,
153 p_information_type,
154 p_pei_information5 ) loop
155 --
156 -- raise error if the record exists for the same INCOME_CODE
157 hr_utility.set_message(800, 'PER_PEI_VISA_DUP_RECORD');
158 hr_utility.raise_error;
159 end loop;
160 end if;
161 --
162 hr_utility.set_location(l_proc, 40);
163
164 if p_information_type = 'PER_US_INCOME_FORECAST' then
165 --
166 -- check for INCOME_CODE duplicate record
167 hr_utility.set_location(l_proc, 45);
168 for c2_rec in c2 (p_person_id,
169 p_information_type,
170 p_pei_information5 ) loop
171 --
172 -- raise error if the record exists for the same INCOME_CODE
173 hr_utility.set_message(800, 'PER_PEI_VISA_DUP_RECORD');
174 hr_utility.raise_error;
175 end loop;
176 end if;
177 --
178
179 hr_utility.set_location(l_proc, 50);
180
181 if p_information_type = 'PER_US_VISA_DETAILS' then
182 --
183 -- check for J Visa Category
184 hr_utility.set_location(l_proc, 55);
185 if (p_pei_information5 = 'J-1' or p_pei_information5 = 'J-2') and
186 p_pei_information9 is NULL then
187 -- raise error if J Visa Category is not entered for J visa
188 hr_utility.set_message(800, 'PER_PEI_J_VISA_CATEGORY');
189 hr_utility.raise_error;
190 end if;
191 --
192 -- check for more than one CURRENT Visa record
193 hr_utility.set_location(l_proc, 60);
194 for c3_rec in c3 (p_person_id, p_information_type) loop
195 --
196 -- raise error if more than one record has CURRENT set to Y
197 hr_utility.set_message(800, 'PER_PEI_MULTI_CURRENT_VISA');
198 hr_utility.raise_error;
199 end loop;
200 end if;
201 --
202 hr_utility.set_location(l_proc, 65);
203
204 if p_information_type = 'PER_US_VISIT_HISTORY' then
205 --
206 -- check for overlap visit period
207 hr_utility.set_location(l_proc, 70);
208 for c_rec in next_visit (p_person_id, p_information_type) loop
209 l_counter := l_counter + 1;
210 --
211 hr_utility.set_location('End Date: '||
212 to_char(c_rec.end_date,'YYYY/MM/DD')||' : ', 75);
213 if l_counter > 1 then
214 if c_rec.start_date <= a_end_date then
215 --
216 -- raise error if there is a visit date overlap
217 hr_utility.set_message(800, 'PER_PEI_VISIT_OVERLAP');
218 hr_utility.raise_error;
219 end if;
220 end if;
221 -- Save the end date of previous visit
222 a_end_date := c_rec.end_date;
223
224 end loop;
225 end if;
226 --
227 hr_utility.set_location(l_proc, 80);
228
229 if p_information_type = 'PER_US_ADDITIONAL_DETAILS' then
230 --
231 -- check if DEP_CHILDREN_IN_CNTRY < DEP_CHILDREN_TOTAL
232 hr_utility.set_location(l_proc, 85);
233 if to_number(p_pei_information11) > to_number(p_pei_information10) then
234 --
235 hr_utility.set_message(800, 'PER_PEI_WRONG_DEP_CHILD_NUM');
236 hr_utility.raise_error;
237 end if;
238 end if;
239 --
240 --
241 hr_utility.set_location(' Leaving:'||l_proc, 90);
242 --
243 End chk_us_visa_rows;
244 --
245 --
246 End per_us_validate_pei;
[Home] [Help]