1 PACKAGE BODY AP_WEB_AUDIT_LIST_VAL_PVT AS
2 /* $Header: apwvalvb.pls 115.4 2004/06/30 14:43:48 jrautiai noship $ */
3
4 PROCEDURE Validate_Employee(p_emp_rec IN OUT NOCOPY AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
5 x_return_status OUT NOCOPY VARCHAR2);
6
7 PROCEDURE Find_Employee(p_emp_rec IN OUT NOCOPY AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
8 x_return_status OUT NOCOPY VARCHAR2);
9
10 PROCEDURE Validate_Audit_dates(p_emp_rec IN AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
11 p_audit_rec IN OUT NOCOPY AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type,
12 x_return_status OUT NOCOPY VARCHAR2);
13
14 /*========================================================================
15 | PUBLIC PROCEDUDE Validate_Employee_Info
16 |
17 | DESCRIPTION
18 | This procedure validates that a single employee exists for the given
19 | parameter and returns the identifier for the match.
20 |
21 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
22 | AP_WEB_AUDIT_LIST_PUB.Audit_Employee
23 |
24 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
25 |
26 | RETURNS
27 | Employee identifier matching the given parameters.
28 | Status whether the validation was succesful or not
29 |
30 | PARAMETERS
31 | p_emp_rec IN OUT Employee record containg criteria used to find a given employee
32 | x_return_status OUT Status whether the validation was succesful or not
33 |
34 | MODIFICATION HISTORY
35 | Date Author Description of Changes
36 | 05-Dec-2002 J Rautiainen Created
37 |
38 *=======================================================================*/
39 PROCEDURE Validate_Employee_Info(p_emp_rec IN OUT NOCOPY AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
40 x_return_status OUT NOCOPY VARCHAR2) IS
41
42 l_required_return_status VARCHAR2(1);
43 l_emp_return_status VARCHAR2(1);
44
45 BEGIN
46
47 -- Initialize API return status to success
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49
50 -- Validate employee info
51 Validate_Employee(p_emp_rec,
52 l_emp_return_status);
53
54 IF l_required_return_status <> FND_API.G_RET_STS_SUCCESS OR
55 l_emp_return_status <> FND_API.G_RET_STS_SUCCESS THEN
56
57 x_return_status := FND_API.G_RET_STS_ERROR;
58 END IF;
59
60 END Validate_Employee_Info;
61
62 /*========================================================================
63 | PUBLIC PROCEDUDE Validate_Required_Input
64 |
65 | DESCRIPTION
66 | This procedure validates that the required parameters are passed to the api.
67 |
68 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
69 | AP_WEB_AUDIT_LIST_PUB.Audit_Employee
70 |
71 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
72 |
73 | RETURNS
74 | Status whether the validation was succesful or not
75 |
76 | PARAMETERS
77 | p_emp_rec IN Employee record containg criteria used to find a given employee
78 | p_audit_rec IN Audit record containg information about the record to be created
79 | x_return_status OUT Status whether the validation was succesful or not
80 |
81 | MODIFICATION HISTORY
82 | Date Author Description of Changes
83 | 05-Dec-2002 J Rautiainen Created
84 |
85 *=======================================================================*/
86 PROCEDURE Validate_Required_Input(p_emp_rec IN AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
87 p_audit_rec IN AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type,
88 x_return_status OUT NOCOPY VARCHAR2) IS
89
90 BEGIN
91
92 -- Initialize API return status to success
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 -- Business group id is always required
96 IF p_emp_rec.business_group_id IS NULL THEN
97
98 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_BG_NULL');
99 FND_MSG_PUB.Add;
100 x_return_status := FND_API.G_RET_STS_ERROR;
101
102 END IF;
103
104 -- Audit reason is always required
105 IF p_audit_rec.audit_reason_code IS NULL THEN
106
107 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_AR_NULL');
108 FND_MSG_PUB.Add;
109 x_return_status := FND_API.G_RET_STS_ERROR;
110
111 END IF;
112
113 /*=======================================================================*
114 | Either start date or end date must be provided |
115 *=======================================================================*/
116 IF p_audit_rec.start_date IS NULL and p_audit_rec.end_date IS NULL THEN
117 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_TED_NULL');
118 FND_MSG_PUB.Add;
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 END IF;
121
122 /*======================================================================*
123 | If all required parameters were provided, then at least one employee |
124 | criteria is required. |
125 *======================================================================*/
126 IF x_return_status = FND_API.G_RET_STS_SUCCESS
127 AND p_emp_rec.person_id IS NULL
128 AND p_emp_rec.employee_number IS NULL
129 AND p_emp_rec.national_identifier IS NULL
130 AND p_emp_rec.email_address IS NULL THEN
131
132 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_NO_EMP_INFO');
133 FND_MSG_PUB.Add;
134 x_return_status := FND_API.G_RET_STS_ERROR;
135
136 END IF;
137
138 END Validate_Required_Input;
139
140 /*========================================================================
141 | PRIVATE PROCEDUDE Validate_Employee
142 |
143 | DESCRIPTION
144 | This procedure validates that a single employee exists for the given
145 | parameter and returns the identifier for the match.
146 |
147 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
148 | Validate_Employee_Info
149 |
150 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
151 |
152 | RETURNS
153 | Employee identifier matching the given parameters.
154 | Status whether the validation was succesful or not
155 |
156 | PARAMETERS
157 | p_emp_rec IN OUT Employee record containg criteria used to find a given employee
158 | x_return_status OUT Status whether the validation was succesful or not
159 |
160 | MODIFICATION HISTORY
161 | Date Author Description of Changes
162 | 05-Dec-2002 J Rautiainen Created
163 |
164 *=======================================================================*/
165 PROCEDURE Validate_Employee(p_emp_rec IN OUT NOCOPY AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
166 x_return_status OUT NOCOPY VARCHAR2) IS
167 l_person_id NUMBER;
168 l_emp_return_status VARCHAR2(1);
169 BEGIN
170
171 -- Initialize API return status to success
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 -- Try to match the criteria to a single employee
175 Find_Employee(p_emp_rec, l_emp_return_status);
176
177 x_return_status := l_emp_return_status;
178
179 END Validate_Employee;
180
181 /*========================================================================
182 | PRIVATE PROCEDUDE Find_Employee
183 |
184 | DESCRIPTION
185 | This procedure tries to find a single employee for the criteria given
186 | as parameters and returns the identifier for the match.
187 |
188 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
189 | Validate_Employee
190 |
191 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
192 |
193 | RETURNS
194 | Employee identifier matching the given parameters.
195 | Status whether the validation was succesful or not
196 |
197 | PARAMETERS
198 | p_emp_rec IN OUT Employee record containg criteria used to find a given employee
199 | x_return_status OUT Status whether the validation was succesful or not
200 |
201 | MODIFICATION HISTORY
202 | Date Author Description of Changes
203 | 05-Dec-2002 J Rautiainen Created
204 |
205 *=======================================================================*/
206 PROCEDURE Find_Employee(p_emp_rec IN OUT NOCOPY AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
207 x_return_status OUT NOCOPY VARCHAR2) IS
208
209 l_query_stmt VARCHAR2(4000) := to_char(null);
210 l_where_clause VARCHAR2(4000) := to_char(null);
211 l_counter NUMBER := 0;
212
213 cur_hdl INTEGER;
214 rows_processed BINARY_INTEGER;
215 l_person_id NUMBER;
216 BEGIN
217
218 /*======================================================================*
219 | Business group is always required, so it is always part of the query.|
220 | Other criteria is only added is it was provided. |
221 *======================================================================*/
222 l_where_clause := 'WHERE business_group_id = :business_group_id';
223
224 IF p_emp_rec.person_id IS NOT NULL THEN
225 l_where_clause := l_where_clause || ' AND person_id = :person_id';
226 END IF;
227
228 IF p_emp_rec.employee_number IS NOT NULL THEN
229 l_where_clause := l_where_clause || ' AND employee_number = :employee_number';
230 END IF;
231
232 IF p_emp_rec.national_identifier IS NOT NULL THEN
233 l_where_clause := l_where_clause || ' AND national_identifier = :national_identifier';
234 END IF;
235
236 IF p_emp_rec.email_address IS NOT NULL THEN
237 l_where_clause := l_where_clause || ' AND email_address = :email_address';
238 END IF;
239
240 /* 2-Oct-2003 J Rautiainen Contingent project changes
241 * This procedure is used to fetch the id a employee to be added to audit list.
242 * Since person can be added to audit list regardless of current status we
243 * should use per_workforce_x, however that view does not contain the national
244 * identifier, so no change here.
245 */
246 /*===================================================================================*
247 | Select statement using the given employee matching criteria in the where clause. |
248 *===================================================================================*/
249 l_query_stmt := 'select distinct(person_id) from PER_ALL_PEOPLE_F '||l_where_clause;
250
251 -- open cursor
252 cur_hdl := dbms_sql.open_cursor;
253
254 -- parse cursor
255 dbms_sql.parse(cur_hdl, l_query_stmt,dbms_sql.native);
256
257 dbms_sql.bind_variable(cur_hdl, ':business_group_id', p_emp_rec.business_group_id);
258
259 IF p_emp_rec.person_id IS NOT NULL THEN
260 dbms_sql.bind_variable(cur_hdl, ':person_id', p_emp_rec.person_id);
261 END IF;
262
263 IF p_emp_rec.employee_number IS NOT NULL THEN
264 dbms_sql.bind_variable(cur_hdl, ':employee_number', p_emp_rec.employee_number);
265 END IF;
266
267 IF p_emp_rec.national_identifier IS NOT NULL THEN
268 dbms_sql.bind_variable(cur_hdl, ':national_identifier', p_emp_rec.national_identifier);
269 END IF;
270
271 IF p_emp_rec.email_address IS NOT NULL THEN
272 dbms_sql.bind_variable(cur_hdl, ':email_address', p_emp_rec.email_address);
273 END IF;
274
275 dbms_sql.define_column(cur_hdl, 1, l_person_id);
276
277 -- execute cursor
278 rows_processed := dbms_sql.execute(cur_hdl);
279
280
281 /*==========================================================================*
282 | Loop through the results to find out whether multiple matches were found.|
283 *==========================================================================*/
284 LOOP
285 -- fetch a row
286 IF dbms_sql.fetch_rows(cur_hdl) > 0 then
287
288 -- fetch columns from the row
289 dbms_sql.column_value(cur_hdl, 1, l_person_id);
290
291 l_counter := l_counter+1;
292
293 IF l_counter >= 2 THEN
294 EXIT;
295 END IF;
296
297 ELSE
298 EXIT;
299 END IF;
300
301 END LOOP;
302
303 -- close cursor
304 dbms_sql.close_cursor(cur_hdl);
305
306 IF l_counter >= 2 THEN
307 /*======================================================================*
308 | Employee must be uniquelly identified, several matches were found. |
309 *======================================================================*/
310 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_MULTIPLE_EMP');
311 FND_MSG_PUB.Add;
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 p_emp_rec.person_id := to_number(null);
314 ELSIF l_counter = 0 THEN
315 /*======================================================================*
316 | Employee must be uniquelly identified, no matches were found. |
317 *======================================================================*/
318 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_NO_EMP_MATCH');
319 FND_MSG_PUB.Add;
320 x_return_status := FND_API.G_RET_STS_ERROR;
321 p_emp_rec.person_id := to_number(null);
322 ELSE
323 p_emp_rec.person_id := l_person_id;
324 END IF;
325 END Find_Employee;
326
327 /*========================================================================
328 | PUBLIC PROCEDUDE Validate_Audit_Info
329 |
330 | DESCRIPTION
331 | This procedure validates that the audit information used to create
332 | the new record is valid.
333 |
334 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
335 | AP_WEB_AUDIT_LIST_PUB.Audit_Employee
336 |
337 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
338 |
339 | RETURNS
340 | Status whether the validation was succesful or not
341 |
342 | PARAMETERS
343 | p_emp_rec IN Employee record containg criteria used to find a given employee
344 | p_audit_rec IN OUT Audit record containg information about the record to be created
345 | x_return_status OUT Status whether the validation was succesful or not
346 |
347 | MODIFICATION HISTORY
348 | Date Author Description of Changes
349 | 05-Dec-2002 J Rautiainen Created
350 |
351 *=======================================================================*/
352 PROCEDURE Validate_Audit_Info(p_emp_rec IN AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
353 p_audit_rec IN OUT NOCOPY AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type,
354 x_return_status OUT NOCOPY VARCHAR2) IS
355
356 /*===================================================================*
357 | Cursor to verify that the given audit reason is valid and active. |
358 *===================================================================*/
359 CURSOR reason_cur(p_lookup_code VARCHAR2) IS
360 select lookup_code
361 from ap_lookup_codes lu
362 where lu.lookup_type = 'OIE_AUTO_AUDIT_REASONS'
363 and lu.lookup_code = p_lookup_code
364 and NVL(lu.enabled_flag,'Y') = 'Y'
365 AND TRUNC(SYSDATE)
366 BETWEEN TRUNC(NVL(lu.START_DATE_ACTIVE,SYSDATE))
367 AND TRUNC(NVL(lu.INACTIVE_DATE,SYSDATE));
368
369 reason_rec reason_cur%ROWTYPE;
370 l_return_status1 VARCHAR2(1);
371 l_return_status2 VARCHAR2(1);
372 BEGIN
373
374 OPEN reason_cur(p_audit_rec.audit_reason_code);
375 FETCH reason_cur INTO reason_rec;
376
377 IF reason_cur%NOTFOUND THEN
378
379 /*====================================================*
380 | Given audit reason is not available or not active. |
381 *====================================================*/
382 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_NO_AC_MATCH');
383 FND_MSG_PUB.Add;
384 l_return_status1 := FND_API.G_RET_STS_ERROR;
385
386 END IF;
387
388 CLOSE reason_cur;
389
390 Validate_Audit_dates(p_emp_rec, p_audit_rec, l_return_status2);
391
392 IF (l_return_status1 = FND_API.G_RET_STS_ERROR OR l_return_status2 = FND_API.G_RET_STS_ERROR) THEN
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 END IF;
395
396 END Validate_Audit_Info;
397
398 /*========================================================================
399 | PRIVATE PROCEDUDE Validate_Audit_dates
400 |
401 | DESCRIPTION
402 | This procedure validates the audit dates given as parameter.
403 |
404 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
405 | Validate_Audit_Info
406 |
407 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
408 |
409 | RETURNS
410 | Status whether the validation was succesful or not
411 |
412 | PARAMETERS
413 | p_emp_rec IN Employee record containg criteria used to find a given employee
414 | p_audit_rec IN OUT Audit record containg information about the record to be created
415 | x_return_status OUT Status whether the validation was succesful or not
416 |
417 | MODIFICATION HISTORY
418 | Date Author Description of Changes
419 | 05-Dec-2002 J Rautiainen Created
420 |
421 *=======================================================================*/
422 PROCEDURE Validate_Audit_dates(p_emp_rec IN AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
423 p_audit_rec IN OUT NOCOPY AP_WEB_AUDIT_LIST_PUB.Audit_Rec_Type,
424 x_return_status OUT NOCOPY VARCHAR2) IS
425
426 /*============================================*
427 | Cursor to find existing open ended record. |
428 *============================================*/
429 CURSOR open_cur IS
430 SELECT auto_audit_id
431 FROM ap_aud_auto_audits
432 WHERE employee_id = p_emp_rec.person_id
433 AND audit_reason_code = p_audit_rec.audit_reason_code
434 AND end_date is NULL;
435
436 open_rec open_cur%ROWTYPE;
437
438 BEGIN
439
440 IF ( p_audit_rec.end_date is not null
441 AND p_audit_rec.start_date is null ) THEN
442
443 OPEN open_cur;
444 FETCH open_cur INTO open_rec;
445
446 IF open_cur%NOTFOUND THEN
447 /*=======================================================================*
448 | For record with only end date provided, there must exist a open ended |
449 | record with same reason_code. This is used when an employee returns |
450 *=======================================================================*/
451 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_MISSING_ED_REC');
452 FND_MSG_PUB.Add;
453 x_return_status := FND_API.G_RET_STS_ERROR;
454
455 END IF;
456
457 CLOSE open_cur;
458
459 END IF;
460
461 END Validate_Audit_dates;
462
463 /*========================================================================
464 | PUBLIC PROCEDUDE Validate_Required_Input
465 |
466 | DESCRIPTION
467 | This procedure validates that the required parameters are passed to the api.
468 |
469 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
470 | AP_WEB_AUDIT_LIST_PUB.Deaudit_Employee
471 |
472 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
473 |
474 | RETURNS
475 | Status whether the validation was succesful or not
476 |
477 | PARAMETERS
478 | p_emp_rec IN Employee record containg criteria used to find a given employee
479 | p_date_range_rec IN Record containg date range
480 | x_return_status OUT Status whether the validation was succesful or not
481 |
482 | MODIFICATION HISTORY
483 | Date Author Description of Changes
484 | 29-Jun-2002 J Rautiainen Created
485 |
486 *=======================================================================*/
487 PROCEDURE Validate_Required_Input(p_emp_rec IN AP_WEB_AUDIT_LIST_PUB.Employee_Rec_Type,
488 p_date_range_rec IN AP_WEB_AUDIT_LIST_PUB.Date_Range_Type,
489 x_return_status OUT NOCOPY VARCHAR2) IS
490
491 BEGIN
492
493 -- Initialize API return status to success
494 x_return_status := FND_API.G_RET_STS_SUCCESS;
495
496 -- Business group id is always required
497 IF p_emp_rec.business_group_id IS NULL THEN
498
499 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_BG_NULL');
500 FND_MSG_PUB.Add;
501 x_return_status := FND_API.G_RET_STS_ERROR;
502
503 END IF;
504
505 /*======================================================================*
506 | If all required parameters were provided, then at least one employee |
507 | criteria is required. |
508 *======================================================================*/
509 IF x_return_status = FND_API.G_RET_STS_SUCCESS
510 AND p_emp_rec.person_id IS NULL
511 AND p_emp_rec.employee_number IS NULL
512 AND p_emp_rec.national_identifier IS NULL
513 AND p_emp_rec.email_address IS NULL THEN
514
515 FND_MESSAGE.SET_NAME('SQLAP','OIE_AUD_ALAPI_NO_EMP_INFO');
516 FND_MSG_PUB.Add;
517 x_return_status := FND_API.G_RET_STS_ERROR;
518
519 END IF;
520
521 END Validate_Required_Input;
522
523 END AP_WEB_AUDIT_LIST_VAL_PVT;
[Home] [Help]