[Home] [Help]
PACKAGE BODY: APPS.FPA_VALIDATION_PVT
Source
1 package body FPA_VALIDATION_PVT as
2 /* $Header: FPAVVALB.pls 120.6 2006/03/20 19:10:16 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'FPA_VALIDATION_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := FPA_UTILITIES_PVT.G_APP_NAME;
6 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
7 L_API_NAME CONSTANT VARCHAR2(35) := 'VALIDATION';
8
9
10 TYPE Validation_Rec_Type is RECORD
11 (Validation_Type VARCHAR2(30),
12 Severity VARCHAR2(1),
13 Object_Id NUMBER,
14 Object_type VARCHAR2(30)
15 );
16
17 TYPE Validation_Tbl_Type IS TABLE OF Validation_Rec_Type
18 INDEX BY BINARY_INTEGER;
19
20 is_Validation BOOLEAN := FALSE;
21 Validations_Count NUMBER := 0;
22 Validations Validation_Tbl_Type;
23
24 G_VALIDATION_SET VARCHAR2(30);
25 G_HEADER_ID NUMBER;
26
27 PROCEDURE Create_Validation_Line
28 (
29 p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2,
31 p_validation_set IN VARCHAR2,
32 p_validation_lines_rec IN FPA_VALIDATION_LINES_REC,
33 x_validation_id OUT NOCOPY NUMBER,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2
37 ) IS
38
39 -- standard parameters
40 l_return_status VARCHAR2(1);
41 l_api_name CONSTANT VARCHAR2(30) := 'Create_Validation_Line';
42 l_api_version CONSTANT NUMBER := 1.0;
43 l_msg_log VARCHAR2(2000) := null;
44 ----------------------------------------------------------------------------
45 l_flag VARCHAR2(1);
46 l_validation_lines_rec FPA_VALIDATION_LINES_REC := p_validation_lines_rec;
47
48 CURSOR OBJ_VAL_TYPE_CSR (VALIDATION_SET IN VARCHAR2,
49 OBJ_VALIDATION_TYPE IN VARCHAR2) IS
50 SELECT 'T'
51 FROM FPA_LOOKUPS_V
52 WHERE LOOKUP_TYPE = VALIDATION_SET
53 AND LOOKUP_CODE = OBJ_VALIDATION_TYPE;
54
55 CURSOR HDR_VAL_TYPE_CSR (VALIDATION_SET IN VARCHAR2) IS
56 SELECT 'T'
57 FROM FPA_LOOKUPS_V
58 WHERE LOOKUP_TYPE = VALIDATION_SET;
59
60 CURSOR SEV_TYPE_CSR (SEV_CODE IN VARCHAR2) IS
61 SELECT 'T'
62 FROM FPA_LOOKUPS_V
63 WHERE LOOKUP_TYPE = 'FPA_SEVERITY_TYPES'
64 AND LOOKUP_CODE = SEV_CODE;
65 BEGIN
66
67 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
68
69 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
70 p_api_name => l_api_name,
71 p_pkg_name => G_PKG_NAME,
72 p_init_msg_list => p_init_msg_list,
73 l_api_version => l_api_version,
74 p_api_version => p_api_version,
75 p_api_type => G_API_TYPE,
76 p_msg_log => 'Entering Fpa_Validation_Pvt.Create_Validation_Line',
77 x_return_status => x_return_status);
78
79 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
80 l_msg_log := 'start_activity';
81 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
82 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
83 l_msg_log := 'start_activity';
84 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
85 end if;
86
87 open obj_val_type_csr(p_validation_set, l_validation_lines_rec.validation_type);
88 fetch obj_val_type_csr into l_flag;
89 close obj_val_type_csr;
90
91 if(l_flag is null or l_flag <> FND_API.G_TRUE) then
92 open hdr_val_type_csr(p_validation_set);
93 fetch hdr_val_type_csr into l_flag;
94 close hdr_val_type_csr;
95 end if;
96
97 if(l_flag is null or l_flag <> FND_API.G_TRUE) then
98 Fpa_Utilities_Pvt.Set_Message(
99 p_msg_name => 'FPA_INVALID_VALIDATION',
100 p_token1 => 'TYPE',
101 p_token1_value => p_validation_set);
102 raise Fpa_Utilities_Pvt.G_EXCEPTION_ERROR;
103 end if;
104
105 l_flag := null;
106 if(l_validation_lines_rec.severity is not null) then
107 open sev_type_csr(l_validation_lines_rec.severity);
108 fetch sev_type_csr into l_flag;
109 close sev_type_csr;
110 if(l_flag is null or l_flag <> FND_API.G_TRUE) then
111 Fpa_Utilities_Pvt.Set_Message(
112 p_msg_name => 'FPA_INVALID_SEVERITY_TYPE',
113 p_token1 => 'TYPE',
114 p_token1_value => l_validation_lines_rec.severity);
115 raise Fpa_Utilities_Pvt.G_EXCEPTION_ERROR;
116 end if;
117 else
118 l_validation_lines_rec.severity := 'I';
119 end if;
120
121
122 select fpa_validation_lines_s.nextval into
123 l_validation_lines_rec.validation_id from dual;
124 x_validation_id := l_validation_lines_rec.validation_id;
125 l_validation_lines_rec.created_by := FND_GLOBAL.USER_ID;
126 l_validation_lines_rec.creation_date := SYSDATE;
127 l_validation_lines_rec.last_updated_by := FND_GLOBAL.USER_ID;
128 l_validation_lines_rec.last_update_date := SYSDATE;
129 l_validation_lines_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
130
131 INSERT INTO FPA_VALIDATION_LINES(
132 validation_id,
133 header_id,
134 object_id,
135 object_type,
136 validation_type,
137 message_id,
138 severity,
139 created_by,
140 creation_date,
141 last_updated_by,
142 last_update_date,
143 last_update_login)
144 VALUES (
145 l_validation_lines_rec.validation_id,
146 l_validation_lines_rec.header_id,
147 l_validation_lines_rec.object_id,
148 l_validation_lines_rec.object_type,
149 l_validation_lines_rec.validation_type,
150 l_validation_lines_rec.message_id,
151 l_validation_lines_rec.severity,
152 l_validation_lines_rec.created_by,
153 l_validation_lines_rec.creation_date,
154 l_validation_lines_rec.last_updated_by,
155 l_validation_lines_rec.last_update_date,
156 l_validation_lines_rec.last_update_login);
157
158 FPA_UTILITIES_PVT.END_ACTIVITY(
159 p_api_name => l_api_name,
160 p_pkg_name => G_PKG_NAME,
161 p_msg_log => null,
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data);
164
165 EXCEPTION
166 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
167 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
168 p_api_name => l_api_name,
169 p_pkg_name => G_PKG_NAME,
170 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
171 p_msg_log => l_msg_log,
172 x_msg_count => x_msg_count,
173 x_msg_data => x_msg_data,
174 p_api_type => G_API_TYPE);
175
176 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
177 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
178 p_api_name => l_api_name,
179 p_pkg_name => G_PKG_NAME,
180 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
181 p_msg_log => l_msg_log,
182 x_msg_count => x_msg_count,
183 x_msg_data => x_msg_data,
184 p_api_type => G_API_TYPE);
185
186 when OTHERS then
187 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
188 p_api_name => l_api_name,
189 p_pkg_name => G_PKG_NAME,
190 p_exc_name => 'OTHERS',
191 p_msg_log => l_msg_log||SQLERRM,
192 x_msg_count => x_msg_count,
193 x_msg_data => x_msg_data,
194 p_api_type => G_API_TYPE);
195
196 END Create_Validation_line;
197
198
199 FUNCTION Add_Validation
200 (
201 p_validation IN VARCHAR2,
202 p_severity_code IN VARCHAR2,
203 p_object_id IN NUMBER,
204 p_object_type IN VARCHAR2
205 ) RETURN BOOLEAN IS
206
207 BEGIN
208 if(is_Validation) then
209 Validations_Count := Validations_Count + 1;
210 Validations(Validations_Count).validation_type := p_validation;
211 Validations(Validations_Count).object_id := p_object_id;
212 Validations(Validations_Count).object_type := p_object_type;
213 Validations(Validations_Count).severity := p_severity_code;
214 end if;
215 if(Validations_Count >= 10) then
216 Close_Validations;
217 end if;
218 return is_Validation;
219
220 EXCEPTION
221 when OTHERS then
222 return is_Validation;
223 END Add_Validation;
224
225 PROCEDURE Initialize IS
226
227 BEGIN
228
229 is_Validation := TRUE;
230 Validations.DELETE;
231 Validations_Count := 0;
232
233 EXCEPTION
234 when OTHERS then
235 null;
236 END Initialize;
237
238 PROCEDURE UnInitialize IS
239
240 BEGIN
241
242 is_Validation := FALSE;
243 Validations.DELETE;
244 Validations_Count := 0;
245
246 EXCEPTION
247 when OTHERS then
248 null;
249 END UnInitialize;
250
251
252 PROCEDURE Close_Validations IS
253
254 -- standard parameters
255 l_return_status VARCHAR2(1);
256 l_init_msg_list VARCHAR2(1) := 'F';
257 l_api_name CONSTANT VARCHAR2(30) := 'Close_Validations';
258 l_api_version CONSTANT NUMBER := 1.0;
259 l_msg_log VARCHAR2(2000) := null;
260 l_msg_count NUMBER;
261 l_msg_data VARCHAR2(2000);
262 ----------------------------------------------------------------------------
263
264 i INTEGER;
265 l_validation_lines_rec FPA_VALIDATION_LINES_REC;
266 l_validation_id NUMBER;
267 l_exists VARCHAR2(1) := FND_API.G_FALSE;
268
269 CURSOR CHECK_VALIDATION_LINES(
270 P_VALIDATIONS_TYPE IN VARCHAR2,
271 P_OBJECT_HEADER_ID IN NUMBER,
272 P_OBJECT_ID IN NUMBER,
273 P_OBJECT_TYPE IN VARCHAR2) IS
274 SELECT 'T'
275 FROM FPA_VALIDATION_LINES FPA
276 WHERE
277 FPA.VALIDATION_TYPE = P_VALIDATIONS_TYPE
278 AND FPA.HEADER_ID = P_OBJECT_HEADER_ID
279 AND FPA.OBJECT_ID = P_OBJECT_ID
280 AND FPA.OBJECT_TYPE = P_OBJECT_TYPE;
281
282 BEGIN
283
284 if (Validations.count = 0) THEN
285 return;
286 end if;
287
288 FPA_UTILITIES_PVT.START_ACTIVITY(
289 p_api_name => l_api_name,
290 p_pkg_name => G_PKG_NAME,
291 p_init_msg_list => l_init_msg_list,
292 p_msg_log => 'Entering '||G_PKG_NAME||'.'||l_api_name);
293
294 for i in validations.first .. Validations.last
295 loop
296
297 open CHECK_VALIDATION_LINES(
298 P_VALIDATIONS_TYPE => Validations(i).validation_type,
299 P_OBJECT_HEADER_ID => G_HEADER_ID,
300 P_OBJECT_ID => Validations(i).object_id,
301 P_OBJECT_TYPE => Validations(i).object_type);
302 fetch CHECK_VALIDATION_LINES into l_exists;
303 close CHECK_VALIDATION_LINES;
304
305 if(l_exists is not null and l_exists = FND_API.G_TRUE) then
306 return;
307 end if;
308
309 l_validation_lines_rec := null;
310 l_validation_lines_rec.header_id := G_HEADER_ID;
311 l_validation_lines_rec.object_id := Validations(i).object_id;
312 l_validation_lines_rec.object_type := Validations(i).object_type;
313 l_validation_lines_rec.validation_type := Validations(i).validation_type;
314 l_validation_lines_rec.message_id := Validations(i).validation_type;
315 l_validation_lines_rec.severity := Validations(i).severity;
316
317 Create_Validation_Line(
318 p_api_version => l_api_version,
319 p_init_msg_list => l_init_msg_list,
320 p_validation_set => G_VALIDATION_SET,
321 p_validation_lines_rec => l_validation_lines_rec,
322 x_validation_id => l_validation_id,
323 x_return_status => l_return_status,
324 x_msg_count => l_msg_count,
325 x_msg_data => l_msg_data);
326
327 if (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
328 l_msg_log := 'Close_Validations';
329 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
330 elsif (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
331 l_msg_log := 'Close_Validations';
332 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
333 end if;
334
335 end loop;
336
337 Initialize;
338
339 FPA_UTILITIES_PVT.END_ACTIVITY(
340 p_api_name => l_api_name,
341 p_pkg_name => G_PKG_NAME,
342 p_msg_log => null,
343 x_msg_count => l_msg_count,
344 x_msg_data => l_msg_data);
345
346 EXCEPTION
347 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
348 l_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
349 p_api_name => l_api_name,
350 p_pkg_name => G_PKG_NAME,
351 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
352 p_msg_log => l_msg_log,
353 x_msg_count => l_msg_count,
354 x_msg_data => l_msg_data,
355 p_api_type => G_API_TYPE);
356
357 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
358 l_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
359 p_api_name => l_api_name,
360 p_pkg_name => G_PKG_NAME,
361 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
362 p_msg_log => l_msg_log,
363 x_msg_count => l_msg_count,
364 x_msg_data => l_msg_data,
365 p_api_type => G_API_TYPE);
366
367 when OTHERS then
368 l_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
369 p_api_name => l_api_name,
370 p_pkg_name => G_PKG_NAME,
371 p_exc_name => 'OTHERS',
372 p_msg_log => l_msg_log||SQLERRM,
373 x_msg_count => l_msg_count,
374 x_msg_data => l_msg_data,
375 p_api_type => G_API_TYPE);
376 END Close_Validations;
377
378
379
380 FUNCTION Check_Error_Level (p_object_id IN NUMBER,
381 p_object_type IN VARCHAR2,
382 p_error_level IN VARCHAR2)
383 RETURN BOOLEAN
384 IS
385
386 -- standard parameters
387 l_return_status VARCHAR2(1);
388 l_init_msg_list VARCHAR2(1) := 'F';
389 l_api_name CONSTANT VARCHAR2(30) := 'Check_Error_Level';
390 l_msg_log VARCHAR2(2000) := null;
391 l_msg_count NUMBER;
392 l_msg_data VARCHAR2(2000);
393 ----------------------------------------------------------------------------
394
395 l_exists VARCHAR2(1) := FND_API.G_FALSE;
396
397 CURSOR CHECK_SEVERITY(
398 P_OBJECT_ID IN NUMBER,
399 P_OBJECT_TYPE IN VARCHAR2,
400 P_SEVERITY IN VARCHAR2) IS
401 SELECT 'T'
402 FROM FPA_VALIDATION_LINES HDR, FPA_VALIDATION_LINES OBJ
403 WHERE
404 HDR.VALIDATION_TYPE = G_VALIDATION_SET
405 AND HDR.OBJECT_ID = G_HEADER_ID
406 AND OBJ.HEADER_ID = HDR.OBJECT_ID
407 AND OBJ.OBJECT_ID = P_OBJECT_ID
408 AND OBJ.OBJECT_TYPE = P_OBJECT_TYPE
409 AND OBJ.SEVERITY = P_SEVERITY;
410
411 BEGIN
412
413 if (Validations.count > 0) THEN
414 Close_Validations;
415 end if;
416
417 FPA_UTILITIES_PVT.START_ACTIVITY(
418 p_api_name => l_api_name,
419 p_pkg_name => G_PKG_NAME,
420 p_init_msg_list => l_init_msg_list,
421 p_msg_log => 'Entering '||G_PKG_NAME||'.'||l_api_name);
422
423 open CHECK_SEVERITY(
424 P_OBJECT_ID => P_OBJECT_ID,
425 P_OBJECT_TYPE => P_OBJECT_TYPE,
426 P_SEVERITY => P_ERROR_LEVEL);
427 fetch CHECK_SEVERITY into l_exists;
428 close CHECK_SEVERITY;
429
430 if(l_exists is not null and l_exists = FND_API.G_TRUE) then
431 return true;
432 else
433 return false;
434 end if;
435
436 FPA_UTILITIES_PVT.END_ACTIVITY(
437 p_api_name => l_api_name,
438 p_pkg_name => G_PKG_NAME,
439 p_msg_log => null,
440 x_msg_count => l_msg_count,
441 x_msg_data => l_msg_data);
442
443 EXCEPTION
444
445 when OTHERS then
446 l_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
447 p_api_name => l_api_name,
448 p_pkg_name => G_PKG_NAME,
449 p_exc_name => 'OTHERS',
450 p_msg_log => l_msg_log||SQLERRM,
451 x_msg_count => l_msg_count,
452 x_msg_data => l_msg_data,
453 p_api_type => G_API_TYPE);
454 END Check_Error_Level;
455
456
457 FUNCTION Count_Validations RETURN NUMBER
458 IS
459 BEGIN
460
461 RETURN Validations_Count;
462
463 END Count_Validations;
464
465
466 FUNCTION Validation RETURN BOOLEAN
467 IS
468 BEGIN
469
470 RETURN is_Validation;
471
472 END Validation;
473
474
475 PROCEDURE Check_Lock_Resource(
476 p_api_version IN NUMBER,
477 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
478 p_header_object_id IN NUMBER,
479 p_header_object_type IN VARCHAR2,
480 p_validations_type IN FPA_VALIDATION_LINES.VALIDATION_TYPE%TYPE,
481 x_resource_status OUT NOCOPY INTEGER,
482 x_resource_id OUT NOCOPY FPA_VALIDATION_LINES.VALIDATION_ID%TYPE,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_count OUT NOCOPY NUMBER,
485 x_msg_data OUT NOCOPY VARCHAR2)
486 IS
487
488 -- standard parameters
489 l_return_status VARCHAR2(1);
490 l_api_name CONSTANT VARCHAR2(30) := 'Check_Lock_Resource';
491 l_api_version CONSTANT NUMBER := 1.0;
492 l_init_msg_list VARCHAR2(1) := 'F';
493 l_msg_log VARCHAR2(2000) := null;
494
495 ----------------------------------------------------------------------------
496 E_Resource_Busy EXCEPTION;
497 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
498
499 CURSOR lock_csr IS
500 SELECT VALIDATION_ID
501 FROM FPA_VALIDATION_LINES
502 WHERE OBJECT_ID = P_HEADER_OBJECT_ID
503 AND OBJECT_TYPE = P_HEADER_OBJECT_TYPE
504 AND VALIDATION_TYPE = P_VALIDATIONS_TYPE
505 FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
506
507 l_header_id INTEGER := null;
508
509 BEGIN
510
511 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
512
513 FPA_UTILITIES_PVT.START_ACTIVITY(
514 p_api_name => l_api_name,
515 p_pkg_name => G_PKG_NAME,
516 p_init_msg_list => l_init_msg_list,
517 p_msg_log => 'Entering '||G_PKG_NAME||'.'||l_api_name);
518
519 BEGIN
520 OPEN lock_csr;
521 FETCH lock_csr INTO l_header_id;
522 CLOSE lock_csr;
523
524 EXCEPTION
525 WHEN E_Resource_Busy THEN
526 IF (lock_csr%ISOPEN) THEN
527 CLOSE lock_csr;
528 END IF;
529 x_resource_status := G_RESOURCE_BUSY;
530 x_resource_id := l_header_id;
531 FPA_UTILITIES_PVT.END_ACTIVITY(
532 p_api_name => l_api_name,
533 p_pkg_name => G_PKG_NAME,
534 p_msg_log => null,
535 x_msg_count => x_msg_count,
536 x_msg_data => x_msg_data);
537 return;
538 END;
539
540 if(l_header_id is null) then
541 x_resource_status := G_NO_RESOURCE_REC;
542 x_resource_id := l_header_id;
543 FPA_UTILITIES_PVT.END_ACTIVITY(
544 p_api_name => l_api_name,
545 p_pkg_name => G_PKG_NAME,
546 p_msg_log => null,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data);
549 return;
550 end if;
551
552 x_resource_status := -1;
553 x_resource_id := l_header_id;
554 return;
555
556 EXCEPTION
557 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
558 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
559 p_api_name => l_api_name,
560 p_pkg_name => G_PKG_NAME,
561 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
562 p_msg_log => l_msg_log,
563 x_msg_count => x_msg_count,
564 x_msg_data => x_msg_data,
565 p_api_type => G_API_TYPE);
566
567 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
568 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
569 p_api_name => l_api_name,
570 p_pkg_name => G_PKG_NAME,
571 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
572 p_msg_log => l_msg_log,
573 x_msg_count => x_msg_count,
574 x_msg_data => x_msg_data,
575 p_api_type => G_API_TYPE);
576
577 when OTHERS then
578 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
579 p_api_name => l_api_name,
580 p_pkg_name => G_PKG_NAME,
581 p_exc_name => 'OTHERS',
582 p_msg_log => l_msg_log||SQLERRM,
583 x_msg_count => x_msg_count,
584 x_msg_data => x_msg_data,
585 p_api_type => G_API_TYPE);
586
587 END Check_Lock_Resource;
588
589
590 PROCEDURE Validate
591 (
592 p_api_version IN NUMBER,
593 p_init_msg_list IN VARCHAR2,
594 p_validation_set IN VARCHAR2,
595 p_header_object_id IN NUMBER,
596 p_header_object_type IN VARCHAR2,
597 p_line_projects_tbl IN PROJECT_ID_TBL_TYPE,
598 x_return_status OUT NOCOPY VARCHAR2,
599 x_msg_count OUT NOCOPY NUMBER,
600 x_msg_data OUT NOCOPY VARCHAR2
601 ) IS
602
603 -- standard parameters
604 l_return_status VARCHAR2(1);
605 l_api_name CONSTANT VARCHAR2(30) := 'Validate';
606 l_api_version CONSTANT NUMBER := 1.0;
607 l_msg_log VARCHAR2(2000) := null;
608 ----------------------------------------------------------------------------
609
610 l_res_status INTEGER := -1;
611 l_validation_lines_rec FPA_VALIDATION_LINES_REC;
612 l_validation_id NUMBER;
613 l_header_id NUMBER;
614
615 BEGIN
616
617 DBMS_TRANSACTION.SAVEPOINT(L_API_NAME || G_API_TYPE);
618
619 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
620
621 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
622 p_api_name => l_api_name,
623 p_pkg_name => G_PKG_NAME,
624 p_init_msg_list => p_init_msg_list,
625 l_api_version => l_api_version,
626 p_api_version => p_api_version,
627 p_api_type => G_API_TYPE,
628 p_msg_log => 'Entering Fpa_Validation_Pvt.Validate',
629 x_return_status => x_return_status);
630
631 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
632 l_msg_log := 'start_activity';
633 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
634 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
635 l_msg_log := 'start_activity';
636 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
637 end if;
638
639 Initialize;
640
641 G_VALIDATION_SET := p_validation_set;
642 G_HEADER_ID := p_header_object_id;
643 Check_Lock_Resource(
644 p_api_version => p_api_version,
645 p_init_msg_list => p_init_msg_list,
646 p_header_object_id => G_HEADER_ID,
647 p_header_object_type => p_header_object_type,
648 p_validations_type => G_VALIDATION_SET,
649 x_resource_status => l_res_status,
650 x_resource_id => l_header_id,
651 x_return_status => x_return_status,
652 x_msg_count => x_msg_count,
653 x_msg_data => x_msg_data);
654
655 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
656 l_msg_log := 'Validate.Check_Lock_Resource';
657 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
658 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
659 l_msg_log := 'validate.Check_Lock_Resource';
660 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
661 end if;
662
663 if(l_res_status = G_NO_RESOURCE_REC) then
664 -- no header record
665
666 l_validation_lines_rec.header_id := null;
667 l_validation_lines_rec.object_id := G_HEADER_ID;
668 l_validation_lines_rec.object_type := p_header_object_type;
669 l_validation_lines_rec.validation_type := G_VALIDATION_SET;
670
671 Create_Validation_Line(
672 p_api_version => p_api_version,
673 p_init_msg_list => p_init_msg_list,
674 p_validation_set => G_VALIDATION_SET,
675 p_validation_lines_rec => l_validation_lines_rec,
676 x_validation_id => l_validation_id,
677 x_return_status => x_return_status,
678 x_msg_count => x_msg_count,
679 x_msg_data => x_msg_data);
680
681 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
682 l_msg_log := 'Validations';
683 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
684 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
685 l_msg_log := 'Validations';
686 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
687 end if;
688
689 -- following commit and new savepoint not required
690 -- if header record can be created before transaction starts
691 commit;
692 DBMS_TRANSACTION.SAVEPOINT(L_API_NAME || G_API_TYPE);
693
694 Check_Lock_Resource(
695 p_api_version => p_api_version,
696 p_init_msg_list => p_init_msg_list,
697 p_header_object_id => G_HEADER_ID,
698 p_header_object_type => p_header_object_type,
699 p_validations_type => G_VALIDATION_SET,
700 x_resource_status => l_res_status,
701 x_resource_id => l_header_id,
702 x_return_status => x_return_status,
703 x_msg_count => x_msg_count,
704 x_msg_data => x_msg_data);
705
706 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
707 l_msg_log := 'Validate.Check_Lock_Resource';
708 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
709 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
710 l_msg_log := 'validate.Check_Lock_Resource';
711 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
712 end if;
713
714 elsif(l_res_status = G_RESOURCE_BUSY) then
715
716 Fpa_Utilities_Pvt.Set_Message(
717 p_app_name => G_APP_NAME,
718 p_msg_name => 'FPA_USER_VALIDATION_IN_PROGRESS');
719 raise Fpa_Utilities_Pvt.G_EXCEPTION_ERROR;
720
721 end if;
722
723 DELETE FROM FPA_VALIDATION_LINES FL
724 WHERE FL.HEADER_ID IN (
725 SELECT OBJECT_ID FROM FPA_VALIDATION_LINES FH
726 WHERE FH.VALIDATION_TYPE = P_VALIDATION_SET
727 AND FH.OBJECT_TYPE = P_HEADER_OBJECT_TYPE
728 AND FH.OBJECT_ID = P_HEADER_OBJECT_ID);
729
730 Initialize;
731
732 Fpa_Validation_Process_Pvt.Validate(
733 p_api_version => p_api_version,
734 p_init_msg_list => p_init_msg_list,
735 p_validation_set => p_validation_set,
736 p_header_object_id => p_header_object_id,
737 p_header_object_type => p_header_object_type,
738 p_line_projects_tbl => p_line_projects_tbl,
739 x_return_status => x_return_status,
740 x_msg_count => x_msg_count,
741 x_msg_data => x_msg_data);
742
743 Close_Validations;
744 UnInitialize;
745
746 UPDATE FPA_VALIDATION_LINES
747 SET LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
748 LAST_UPDATE_DATE = SYSDATE,
749 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
750 WHERE VALIDATION_ID = L_HEADER_ID;
751
752
753 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
754 l_msg_log := 'Validate';
755 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
756 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
757 l_msg_log := 'Validate';
758 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
759 end if;
760
761 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
762
763 FPA_UTILITIES_PVT.END_ACTIVITY(
764 p_api_name => l_api_name,
765 p_pkg_name => G_PKG_NAME,
766 p_msg_log => null,
767 x_msg_count => x_msg_count,
768 x_msg_data => x_msg_data);
769
770
771 EXCEPTION
772 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
773 UnInitialize;
774 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(L_API_NAME || G_API_TYPE);
775
776 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
777 p_api_name => l_api_name,
778 p_pkg_name => G_PKG_NAME,
779 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
780 p_msg_log => l_msg_log,
781 x_msg_count => x_msg_count,
782 x_msg_data => x_msg_data,
783 p_api_type => G_API_TYPE);
784
785 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
786 UnInitialize;
787 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(L_API_NAME || G_API_TYPE);
788
789 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
790 p_api_name => l_api_name,
791 p_pkg_name => G_PKG_NAME,
792 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
793 p_msg_log => l_msg_log,
794 x_msg_count => x_msg_count,
795 x_msg_data => x_msg_data,
796 p_api_type => G_API_TYPE);
797
798 when OTHERS then
799 UnInitialize;
800 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(L_API_NAME || G_API_TYPE);
801
802 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
803 p_api_name => l_api_name,
804 p_pkg_name => G_PKG_NAME,
805 p_exc_name => 'OTHERS',
806 p_msg_log => l_msg_log||SQLERRM,
807 x_msg_count => x_msg_count,
808 x_msg_data => x_msg_data,
809 p_api_type => G_API_TYPE);
810
811 END Validate;
812
813 PROCEDURE Validate
814 (
815 p_api_version IN NUMBER,
816 p_init_msg_list IN VARCHAR2,
817 p_validation_set IN VARCHAR2,
818 p_header_object_id IN NUMBER,
819 p_header_object_type IN VARCHAR2,
820 x_return_status OUT NOCOPY VARCHAR2,
821 x_msg_count OUT NOCOPY NUMBER,
822 x_msg_data OUT NOCOPY VARCHAR2
823 ) IS
824
825 -- standard parameters
826 l_return_status VARCHAR2(1);
827 l_api_name CONSTANT VARCHAR2(30) := 'Validate';
828 l_api_version CONSTANT NUMBER := 1.0;
829 l_msg_log VARCHAR2(2000) := null;
830 ----------------------------------------------------------------------------
831 l_projects_tbl PROJECT_ID_TBL_TYPE;
832
833 BEGIN
834
835 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
836
837 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
838 p_api_name => l_api_name,
839 p_pkg_name => G_PKG_NAME,
840 p_init_msg_list => p_init_msg_list,
841 l_api_version => l_api_version,
842 p_api_version => p_api_version,
843 p_api_type => G_API_TYPE,
844 p_msg_log => 'Entering Fpa_Validation_Pvt.Validate',
845 x_return_status => x_return_status);
846
847 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
848 l_msg_log := 'start_activity';
849 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
850 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
851 l_msg_log := 'start_activity';
852 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
853 end if;
854
855 Validate(
856 p_api_version => p_api_version,
857 p_init_msg_list => p_init_msg_list,
858 p_validation_set => p_validation_set,
859 p_header_object_id => p_header_object_id,
860 p_header_object_type => p_header_object_type,
861 p_line_projects_tbl => l_projects_tbl,
862 x_return_status => x_return_status,
863 x_msg_count => x_msg_count,
864 x_msg_data => x_msg_data);
865
866 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
867 l_msg_log := 'Validate';
868 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
869 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
870 l_msg_log := 'Validate';
871 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
872 end if;
873
874 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
875
876 FPA_UTILITIES_PVT.END_ACTIVITY(
877 p_api_name => l_api_name,
878 p_pkg_name => G_PKG_NAME,
879 p_msg_log => null,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data);
882
883
884 EXCEPTION
885 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
886
887 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
888 p_api_name => l_api_name,
889 p_pkg_name => G_PKG_NAME,
890 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
891 p_msg_log => l_msg_log,
892 x_msg_count => x_msg_count,
893 x_msg_data => x_msg_data,
894 p_api_type => G_API_TYPE);
895
896 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
897
898 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
899 p_api_name => l_api_name,
900 p_pkg_name => G_PKG_NAME,
901 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
902 p_msg_log => l_msg_log,
903 x_msg_count => x_msg_count,
904 x_msg_data => x_msg_data,
905 p_api_type => G_API_TYPE);
906
907 when OTHERS then
908
909 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
910 p_api_name => l_api_name,
911 p_pkg_name => G_PKG_NAME,
912 p_exc_name => 'OTHERS',
913 p_msg_log => l_msg_log||SQLERRM,
914 x_msg_count => x_msg_count,
915 x_msg_data => x_msg_data,
916 p_api_type => G_API_TYPE);
917
918 END Validate;
919
920
921 END FPA_VALIDATION_PVT;