1 package body AK_FLOW3_PVT as
2 /* $Header: akdvfl3b.pls 120.3 2005/09/15 22:49:53 tshort ship $ */
3
4 --
5 -- global constants
6 --
7 -- These values are used as the page and region codes to
8 -- indicate that there is no primary page or region assigned.
9 -- These values should be consistent to the ones used in Forms.
10 --
11 G_NO_PRIMARY_PAGE_CODE CONSTANT VARCHAR2(30) := '-1';
12 G_NO_PRIMARY_REGION_CODE CONSTANT VARCHAR2(30) := '-1';
13
14 --=======================================================
15 -- Function VALIDATE_FLOW
16 --
17 -- Usage Private API for validating a flow. This
18 -- API should only be called by other APIs that are
19 -- owned by the Core Modules Team (AK).
20 --
21 -- Desc Perform validation on a flow record.
22 --
23 -- Results The API returns the standard p_return_status parameter
24 -- indicating one of the standard return statuses :
25 -- * Unexpected error
26 -- * Error
27 -- * Success
28 -- In addition, this function returns TRUE if all
29 -- validation tests are passed, or FALSE otherwise.
30 -- Parameters Flow columns
31 -- p_caller : IN required
32 -- Must be one of the following values defined
33 -- in package AK_ON_OBJECTS_PVT:
34 -- - G_CREATE (if calling from the Create API)
35 -- - G_DOWNLOAD (if calling from the Download API)
36 -- - G_UPDATE (if calling from the Update API)
37 --
38 -- Note This API is intended for performing record-level
39 -- validation. It is not designed for item-level
40 -- validation.
41 --
42 -- Version Initial version number = 1.0
43 -- History Current version number = 1.0
44 --=======================================================
45 function VALIDATE_FLOW (
46 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
47 p_api_version_number IN NUMBER,
48 p_return_status OUT NOCOPY VARCHAR2,
49 p_flow_application_id IN NUMBER := FND_API.G_MISS_NUM,
50 p_flow_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
51 p_primary_page_appl_id IN NUMBER := FND_API.G_MISS_NUM,
52 p_primary_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
53 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
54 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
55 p_caller IN VARCHAR2,
56 p_pass IN NUMBER := 2
57 ) return BOOLEAN is
58 cursor l_check_no_page_csr is
59 select 1
60 from AK_FLOW_PAGES
61 where flow_application_id = p_flow_application_id
62 and flow_code = p_flow_code;
63 l_api_version_number CONSTANT number := 1.0;
64 l_api_name CONSTANT varchar2(30) := 'Validate_Flow';
65 l_dummy NUMBER;
66 l_error BOOLEAN;
67 l_return_status VARCHAR2(1);
68 begin
69
70 IF NOT FND_API.Compatible_API_Call (
71 l_api_version_number, p_api_version_number, l_api_name,
72 G_PKG_NAME) then
73 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
74 return FALSE;
75 END IF;
76
77 l_error := FALSE;
78
79 --** if validation level is none, no validation is necessary
80 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
81 p_return_status := FND_API.G_RET_STS_SUCCESS;
82 return TRUE;
83 end if;
84
85 --** check that key columns are not null and not missing **
86 if ((p_flow_application_id is null) or
87 (p_flow_application_id = FND_API.G_MISS_NUM)) then
88 l_error := TRUE;
89 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
90 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
91 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_APPLICATION_ID');
92 FND_MSG_PUB.Add;
93 end if;
94 end if;
95
96 if ((p_flow_code is null) or
97 (p_flow_code = FND_API.G_MISS_CHAR)) then
98 l_error := TRUE;
99 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
100 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
101 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_CODE');
102 FND_MSG_PUB.Add;
103 end if;
104 end if;
105
106 --** check that required columns are not null and, unless calling **
107 --** from UPDATE procedure, the columns are not missing **
108 if ((p_primary_page_appl_id is null) or
109 (p_primary_page_appl_id = FND_API.G_MISS_NUM and
110 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
111 l_error := TRUE;
112 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
113 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
114 FND_MESSAGE.SET_TOKEN('COLUMN', 'PRIMARY_PAGE_APPL_ID');
115 FND_MSG_PUB.Add;
116 end if;
117 end if;
118
119 if ((p_primary_page_code is null) or
120 (p_primary_page_code = FND_API.G_MISS_CHAR and
121 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
122 l_error := TRUE;
123 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
124 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
125 FND_MESSAGE.SET_TOKEN('COLUMN', 'PRIMARY_PAGE_CODE');
126 FND_MSG_PUB.Add;
127 end if;
128 end if;
129
130 if ((p_name is null) or
131 (p_name = FND_API.G_MISS_CHAR and
132 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
133 l_error := TRUE;
134 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
135 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
136 FND_MESSAGE.SET_TOKEN('COLUMN', 'NAME');
137 FND_MSG_PUB.Add;
138 end if;
139 end if;
140
141
142 -- === Validate columns ===
143 --
144 -- primary_page_appl_id and primary_page_code
145 --
146 -- Check that the primary page exists, or if the primary page code
147 -- is G_NO_PRIMARY_PAGE_CODE (as in the case when a flow is created
148 -- and before any pages have been added for that flow),
149 -- check that no pages exist for the flow.
150 --
151 if (p_primary_page_appl_id <> FND_API.G_MISS_NUM) and
152 (p_primary_page_code <> FND_API.G_MISS_CHAR) then
153 if (p_primary_page_code = G_NO_PRIMARY_PAGE_CODE) then
154 open l_check_no_page_csr;
155 fetch l_check_no_page_csr into l_dummy;
156 if (l_check_no_page_csr%found) then
157 l_error := TRUE;
158 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
159 FND_MESSAGE.SET_NAME('AK','NO_FLOW_DEFAULT_PAGE');
160 FND_MSG_PUB.Add;
161 end if;
162 end if; /* if l_check_no_page_found */
163 close l_check_no_page_csr;
164 else
165 -- do not check references if inserting records
166 --
167 if NOT AK_FLOW_PVT.PAGE_EXISTS (
168 p_api_version_number => 1.0,
169 p_return_status => l_return_status,
170 p_flow_application_id => p_flow_application_id,
171 p_flow_code => p_flow_code,
172 p_page_application_id => p_primary_page_appl_id,
173 p_page_code => p_primary_page_code) then
174 l_error := TRUE;
175 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
176 FND_MESSAGE.SET_NAME('AK','AK_INVALID_FLOW_PG_REFERENCE');
177 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
178 ' ' || p_flow_code ||
179 ' ' || to_char(p_primary_page_appl_id) ||
180 ' ' || p_primary_page_code);
181 FND_MSG_PUB.Add;
182 end if;
183 end if; /* if PAGE_EXISTS */
184 end if; -- /* if p_primary_page_code */
185 end if;
186 -- return true if no error, false otherwise
187 p_return_status := FND_API.G_RET_STS_SUCCESS;
188 return (not l_error);
189
190 EXCEPTION
191 WHEN FND_API.G_EXC_ERROR THEN
192 p_return_status := FND_API.G_RET_STS_ERROR;
193 return FALSE;
194 WHEN OTHERS THEN
195 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
197 SUBSTR (SQLERRM, 1, 240) );
198 FND_MSG_PUB.Add;
199 return FALSE;
200 end VALIDATE_FLOW;
201
202 --=======================================================
203 -- Function VALIDATE_PAGE
204 --
205 -- Usage Private API for validating a flow page. This
206 -- API should only be called by other APIs that are
207 -- owned by the Core Modules Team (AK).
208 --
209 -- Desc Perform validation on a flow page record.
210 --
211 -- Results The API returns the standard p_return_status parameter
212 -- indicating one of the standard return statuses :
213 -- * Unexpected error
214 -- * Error
215 -- * Success
216 -- In addition, this function returns TRUE if all
217 -- validation tests are passed, or FALSE otherwise.
218 -- Parameters Flow Page columns
219 -- p_caller : IN required
220 -- Must be one of the following values defined
221 -- in package AK_ON_OBJECTS_PVT:
222 -- - G_CREATE (if calling from the Create API)
223 -- - G_DOWNLOAD (if calling from the Download API)
224 -- - G_UPDATE (if calling from the Update API)
225 --
226 -- Note This API is intended for performing record-level
227 -- validation. It is not designed for item-level
228 -- validation.
229 --
230 -- Version Initial version number = 1.0
231 -- History Current version number = 1.0
232 --=======================================================
233 function VALIDATE_PAGE (
234 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
235 p_api_version_number IN NUMBER,
236 p_return_status OUT NOCOPY VARCHAR2,
237 p_flow_application_id IN NUMBER := FND_API.G_MISS_NUM,
238 p_flow_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
239 p_page_application_id IN NUMBER := FND_API.G_MISS_NUM,
240 p_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
241 p_primary_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
242 p_primary_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
243 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
244 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
245 p_caller IN VARCHAR2,
246 p_pass IN NUMBER := 2
247 ) return BOOLEAN is
248 cursor l_check_no_region_csr is
249 select 1
250 from AK_FLOW_PAGE_REGIONS
251 where flow_application_id = p_flow_application_id
252 and flow_code = p_flow_code
253 and page_application_id = p_page_application_id
254 and page_code = p_page_code;
255 l_api_version_number CONSTANT number := 1.0;
256 l_api_name CONSTANT varchar2(30) := 'Validate_Page';
257 l_dummy NUMBER;
258 l_error BOOLEAN;
259 l_return_status varchar2(1);
260 begin
261
262 IF NOT FND_API.Compatible_API_Call (
263 l_api_version_number, p_api_version_number, l_api_name,
264 G_PKG_NAME) then
265 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 return FALSE;
267 END IF;
268
269 l_error := FALSE;
270
271 --** if validation level is none, no validation is necessary
272 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
273 p_return_status := FND_API.G_RET_STS_SUCCESS;
274 return TRUE;
275 end if;
276
277 --** check that key columns are not null and not missing **
278 if ((p_flow_application_id is null) or
279 (p_flow_application_id = FND_API.G_MISS_NUM)) then
280 l_error := TRUE;
281 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
282 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
283 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_APPLICATION_ID');
284 FND_MSG_PUB.Add;
285 end if;
286 end if;
287
288 if ((p_flow_code is null) or
289 (p_flow_code = FND_API.G_MISS_CHAR)) then
290 l_error := TRUE;
291 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
292 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
293 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_CODE');
294 FND_MSG_PUB.Add;
295 end if;
296 end if;
297
298 if ((p_page_application_id is null) or
299 (p_page_application_id = FND_API.G_MISS_NUM)) then
300 l_error := TRUE;
301 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
302 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
303 FND_MESSAGE.SET_TOKEN('COLUMN', 'PAGE_APPLICATION_ID');
304 FND_MSG_PUB.Add;
305 end if;
306 end if;
307
308 if ((p_page_code is null) or
309 (p_page_code = FND_API.G_MISS_CHAR)) then
310 l_error := TRUE;
311 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
312 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
313 FND_MESSAGE.SET_TOKEN('COLUMN', 'PAGE_CODE');
314 FND_MSG_PUB.Add;
315 end if;
316 end if;
317
318 --
319 -- Check that the parent flow exists
320 --
321 if (NOT AK_FLOW_PVT.FLOW_EXISTS (
322 p_api_version_number => 1.0,
323 p_return_status => l_return_status,
324 p_flow_application_id => p_flow_application_id,
325 p_flow_code => p_flow_code) ) then
326 l_error := TRUE;
327 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
328 FND_MESSAGE.SET_NAME('AK','AK_INVALID_FLOW_REFERENCE');
329 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
330 ' ' || p_flow_code );
331 FND_MSG_PUB.Add;
332 end if;
333 --dbms_output.put_line('Parent flow does not exist!');
334 end if;
335
336 --** check that required columns are not null and, unless calling **
337 --** from UPDATE procedure, the columns are not missing **
338 if ((p_primary_region_appl_id is null) or
339 (p_primary_region_appl_id = FND_API.G_MISS_NUM and
340 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
341 l_error := TRUE;
342 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
343 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
344 FND_MESSAGE.SET_TOKEN('COLUMN', 'PRIMARY_REGION_APPL_ID');
345 FND_MSG_PUB.Add;
346 end if;
347 end if;
348
349 if ((p_primary_region_code is null) or
350 (p_primary_region_code = FND_API.G_MISS_CHAR and
351 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
352 l_error := TRUE;
353 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
354 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
355 FND_MESSAGE.SET_TOKEN('COLUMN', 'PRIMARY_REGION_CODE');
356 FND_MSG_PUB.Add;
357 end if;
358 end if;
359
360 if ((p_name is null) or
361 (p_name = FND_API.G_MISS_CHAR and
362 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
363 l_error := TRUE;
364 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
365 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
366 FND_MESSAGE.SET_TOKEN('COLUMN', 'NAME');
367 FND_MSG_PUB.Add;
368 end if;
369 end if;
370
371 --** Validate columns **
372 --
373 -- primary_region_appl_id and primary_region_code
374 --
375 -- Check that the primary region exists, or if the primary region code
376 -- is G_NO_PRIMARY_REGION_CODE (as in the case when a page is created
377 -- and before any regions have been added for that flow),
378 -- check that no regions exist for the flow page.
382 if (p_primary_region_code = G_NO_PRIMARY_REGION_CODE) then
379 --
380 if (p_primary_region_appl_id <> FND_API.G_MISS_NUM) and
381 (p_primary_region_code <> FND_API.G_MISS_CHAR) then
383 open l_check_no_region_csr;
384 fetch l_check_no_region_csr into l_dummy;
385 if (l_check_no_region_csr%found) then
386 l_error := TRUE;
387 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
388 FND_MESSAGE.SET_NAME('AK','NO_PAGE_ROOT_REGION');
389 FND_MSG_PUB.Add;
390 end if;
391 end if; /* if l_check_no_region_csr%found */
392 close l_check_no_region_csr;
393 else
394 if NOT AK_FLOW_PVT.PAGE_REGION_EXISTS (
395 p_api_version_number => 1.0,
396 p_return_status => l_return_status,
397 p_flow_application_id => p_flow_application_id,
398 p_flow_code => p_flow_code,
399 p_page_application_id => p_page_application_id,
400 p_page_code => p_page_code,
401 p_region_application_id => p_primary_region_appl_id,
402 p_region_code => p_primary_region_code) then
403 l_error := TRUE;
404 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
405 FND_MESSAGE.SET_NAME('AK','AK_INVALID_PG_REGION_REFERENCE');
406 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
407 ' ' || p_flow_code ||
408 ' ' || to_char(p_page_application_id) ||
409 ' ' || p_page_code ||
410 ' ' || to_char(p_primary_region_appl_id) ||
411 ' ' || p_primary_region_code);
412 FND_MSG_PUB.Add;
413 end if;
414 end if; /* if PAGE_REGION_EXISTS */
415 end if;
416 end if;
417
418 -- return true if no error, false otherwise
419 p_return_status := FND_API.G_RET_STS_SUCCESS;
420 return (not l_error);
421
422 EXCEPTION
423 WHEN FND_API.G_EXC_ERROR THEN
424 p_return_status := FND_API.G_RET_STS_ERROR;
425 return FALSE;
426 WHEN OTHERS THEN
427 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
429 SUBSTR (SQLERRM, 1, 240) );
430 FND_MSG_PUB.Add;
431 return FALSE;
432
433 end VALIDATE_PAGE;
434
435 --=======================================================
436 -- Function VALIDATE_PAGE_REGION
437 --
438 -- Usage Private API for validating a flow page region. This
439 -- API should only be called by other APIs that are
440 -- owned by the Core Modules Team (AK).
441 --
442 -- Desc Perform validation on a flow page region record.
443 --
444 -- Results The API returns the standard p_return_status parameter
445 -- indicating one of the standard return statuses :
446 -- * Unexpected error
447 -- * Error
448 -- * Success
449 -- In addition, this function returns TRUE if all
450 -- validation tests are passed, or FALSE otherwise.
451 -- Parameters Flow Page Region columns
452 -- p_foreign_key_name : IN optional
453 -- The foreign key name used in the flow region
454 -- relation record connecting this flow page region
455 -- and its parent region, if there is one.
456 -- p_caller : IN required
457 -- Must be one of the following values defined
458 -- in package AK_ON_OBJECTS_PVT:
459 -- - G_CREATE (if calling from the Create API)
460 -- - G_DOWNLOAD (if calling from the Download API)
461 -- - G_UPDATE (if calling from the Update API)
462 --
463 -- Note This API is intended for performing record-level
464 -- validation. It is not designed for item-level
465 -- validation.
466 --
467 -- Version Initial version number = 1.0
468 -- History Current version number = 1.0
469 --=======================================================
470 function VALIDATE_PAGE_REGION (
471 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
472 p_api_version_number IN NUMBER,
473 p_return_status OUT NOCOPY VARCHAR2,
474 p_flow_application_id IN NUMBER := FND_API.G_MISS_NUM,
475 p_flow_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
476 p_page_application_id IN NUMBER := FND_API.G_MISS_NUM,
477 p_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
478 p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
479 p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
480 p_display_sequence IN NUMBER := FND_API.G_MISS_NUM,
481 p_region_style IN VARCHAR2 := FND_API.G_MISS_CHAR,
482 p_num_columns IN NUMBER := FND_API.G_MISS_NUM,
483 p_icx_custom_call IN VARCHAR2 := FND_API.G_MISS_CHAR,
484 p_parent_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
485 p_parent_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
486 p_foreign_key_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
487 p_set_primary_region IN VARCHAR2 := FND_API.G_MISS_CHAR,
491 cursor l_check_seq_csr is
488 p_caller IN VARCHAR2,
489 p_pass IN NUMBER := 2
490 ) return BOOLEAN is
492 select 1
493 from AK_FLOW_PAGE_REGIONS
494 where flow_application_id = p_flow_application_id
495 and flow_code = p_flow_code
496 and page_application_id = p_page_application_id
497 and page_code = p_page_code
498 and display_sequence = p_display_sequence
499 and ( (region_application_id <> p_region_application_id) or
500 (region_code <> p_region_code) );
501 cursor l_get_primary_region_csr is
502 select primary_region_appl_id, primary_region_code
503 from AK_FLOW_PAGES
504 where flow_application_id = p_flow_application_id
505 and flow_code = p_flow_code
506 and page_application_id = p_page_application_id
507 and page_code = p_page_code;
508 l_api_version_number CONSTANT number := 1.0;
509 l_api_name CONSTANT varchar2(30) := 'Validate_Page_Region';
510 l_dummy NUMBER;
511 l_error BOOLEAN;
512 l_primary_region_appl_id NUMBER;
513 l_primary_region_code VARCHAR2(30);
514 l_return_status varchar2(1);
515 begin
516
517 IF NOT FND_API.Compatible_API_Call (
518 l_api_version_number, p_api_version_number, l_api_name,
519 G_PKG_NAME) then
520 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521 return FALSE;
522 END IF;
523
524 l_error := FALSE;
525
526 --** if validation level is none, no validation is necessary
527 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
528 p_return_status := FND_API.G_RET_STS_SUCCESS;
529 return TRUE;
530 end if;
531
532 --** check that key columns are not null and not missing **
533 if ((p_flow_application_id is null) or
534 (p_flow_application_id = FND_API.G_MISS_NUM)) then
535 l_error := TRUE;
536 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
537 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
538 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_APPLICATION_ID');
539 FND_MSG_PUB.Add;
540 end if;
541 end if;
542
543 if ((p_flow_code is null) or
544 (p_flow_code = FND_API.G_MISS_CHAR)) then
545 l_error := TRUE;
546 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
547 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
548 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_CODE');
549 FND_MSG_PUB.Add;
550 end if;
551 end if;
552
553 if ((p_page_application_id is null) or
554 (p_page_application_id = FND_API.G_MISS_NUM)) then
555 l_error := TRUE;
556 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
557 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
558 FND_MESSAGE.SET_TOKEN('COLUMN', 'PAGE_APPLICATION_ID');
559 FND_MSG_PUB.Add;
560 end if;
561 end if;
562
563 if ((p_page_code is null) or
564 (p_page_code = FND_API.G_MISS_CHAR)) then
565 l_error := TRUE;
566 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
567 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
568 FND_MESSAGE.SET_TOKEN('COLUMN', 'PAGE_CODE');
569 FND_MSG_PUB.Add;
570 end if;
571 end if;
572
573 if ((p_region_application_id is null) or
574 (p_region_application_id = FND_API.G_MISS_NUM)) then
575 l_error := TRUE;
576 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
577 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
578 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
579 FND_MSG_PUB.Add;
580 end if;
581 end if;
582
583 if ((p_region_code is null) or
584 (p_region_code = FND_API.G_MISS_CHAR)) then
585 l_error := TRUE;
586 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
587 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
588 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
589 FND_MSG_PUB.Add;
590 end if;
591 end if;
592
593 --
594 -- Check that the parent flow page exists
595 --
596 if (NOT AK_FLOW_PVT.PAGE_EXISTS (
597 p_api_version_number => 1.0,
598 p_return_status => l_return_status,
599 p_flow_application_id => p_flow_application_id,
600 p_flow_code => p_flow_code,
601 p_page_application_id => p_page_application_id,
602 p_page_code => p_page_code) ) then
603 l_error := TRUE;
604 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
605 FND_MESSAGE.SET_NAME('AK','AK_INVALID_FLOW_PG_REFERENCE');
606 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
607 ' ' || p_flow_code ||
608 ' ' || to_char(p_page_application_id) ||
609 ' ' || p_page_code);
610 FND_MSG_PUB.Add;
611 end if;
612 end if;
613
614 --
615 -- Check that the region exists in AK_REGIONS
616 --
617 if (NOT AK_REGION_PVT.REGION_EXISTS (
618 p_api_version_number => 1.0,
622 l_error := TRUE;
619 p_return_status => l_return_status,
620 p_region_application_id => p_region_application_id,
621 p_region_code => p_region_code) ) then
623 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
624 FND_MESSAGE.SET_NAME('AK','AK_INVALID_REGION_REFERENCE');
625 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
626 ' ' || p_region_code);
627 FND_MSG_PUB.Add;
628 end if;
629 end if;
630
631 --
632 --** check that required columns are not null and, unless calling **
633 --** from UPDATE procedure, the columns are not missing **
634 --
635 if (p_region_style is null) or
636 ((p_region_style = FND_API.G_MISS_CHAR) and
637 (p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
638 l_error := TRUE;
639 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
640 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
641 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_STYLE');
642 FND_MSG_PUB.Add;
643 end if;
644 end if;
645
646 --** Validate columns **
647 -- - Region style
648 if (p_region_style <> FND_API.G_MISS_CHAR) then
649 if (NOT AK_ON_OBJECTS_PVT.VALID_LOOKUP_CODE (
650 p_api_version_number => 1.0,
651 p_return_status => l_return_status,
652 p_lookup_type => 'REGION_STYLE',
653 p_lookup_code => p_region_style)) then
654 l_error := TRUE;
655 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
656 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
657 FND_MESSAGE.SET_TOKEN('COLUMN','REGION_STYLE');
658 FND_MSG_PUB.Add;
659 end if;
660 --dbms_output.put_line(l_api_name || ' Invalid region style');
661 end if;
662 end if;
663
664 --
665 -- - Display sequence (must be unique within page)
666 --
667 if (p_display_sequence <> FND_API.G_MISS_NUM) and
668 (p_display_sequence is not null) then
669 open l_check_seq_csr;
670 fetch l_check_seq_csr into l_dummy;
671 if (l_check_seq_csr%found) then
672 l_error := TRUE;
673 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
674 FND_MESSAGE.SET_NAME('AK','AK_UNIQUE_DISPLAY_SEQUENCE');
675 FND_MSG_PUB.Add;
676 end if;
677 end if;
678 close l_check_seq_csr;
679 end if;
680
681 --
682 -- - Checks for parent_region_application_id and parent_region_code
683 -- (do not perform check if both parms are missing or null)
684 --
685 if ( ( (p_parent_region_application_id <> FND_API.G_MISS_NUM) and
686 (p_parent_region_application_id is not null) )
687 or
688 ( (p_parent_region_code <> FND_API.G_MISS_CHAR) and
689 (p_parent_region_code is not null) ) )then
690 -- dbms_output.put_line('parent region in validate_page_region = '||to_char(p_parent_region_application_id)
691 -- ||' '||p_parent_region_code);
692 --
693 -- 1. the current region must not be the primary region for the page
694 --
695 open l_get_primary_region_csr;
696 fetch l_get_primary_region_csr into l_primary_region_appl_id,
697 l_primary_region_code;
698 if (l_get_primary_region_csr%found) then
699 if ( (l_primary_region_appl_id = p_region_application_id) and
700 (l_primary_region_code = p_region_code) ) or
701 (p_set_primary_region = 'Y') then
702 l_error := TRUE;
703 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
704 FND_MESSAGE.SET_NAME('AK','AK_PARENT_REGION_DISALLOWED');
705 FND_MSG_PUB.Add;
706 end if;
707 end if;
708 end if;
709 close l_get_primary_region_csr;
710 --
711 -- 2. the parent region must not be the same region as the current
712 -- region
713 --
714 if (p_parent_region_application_id = p_region_application_id) and
715 (p_parent_region_code = p_region_code) then
716 l_error := TRUE;
717 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
718 FND_MESSAGE.SET_NAME('AK','EC_DISTINCT_REGIONS');
719 FND_MSG_PUB.Add;
720 end if;
721 end if;
722
723 --
724 -- 3. the parent region must exist
725 --
726 if NOT AK_FLOW_PVT.PAGE_REGION_EXISTS (
727 p_api_version_number => 1.0,
728 p_return_status => l_return_status,
729 p_flow_application_id => p_flow_application_id,
730 p_flow_code => p_flow_code,
731 p_page_application_id => p_page_application_id,
732 p_page_code => p_page_code,
733 p_region_application_id => p_parent_region_application_id,
734 p_region_code => p_parent_region_code) then
735 l_error := TRUE;
736 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
737 FND_MESSAGE.SET_NAME('AK','AK_INVALID_PG_REGION_REFERENCE');
738 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
739 ' ' || p_flow_code ||
740 ' ' || to_char(p_page_application_id) ||
744 FND_MSG_PUB.Add;
741 ' ' || p_page_code ||
742 ' ' || to_char(p_parent_region_application_id) ||
743 ' ' || p_parent_region_code);
745 end if;
746 end if; /* if PAGE_REGION_EXISTS */
747 --
748 -- 4. if the foreign key name is missing (or null), and the
749 -- current action is to create a new page region, check
750 -- that an intrapage relation connecting the current region and
751 -- its parent region exists.
752 --
753 -- if the current action is to download a page region, the
754 -- foreign key name cannot be missing.
755 --
756 if ( (p_foreign_key_name = FND_API.G_MISS_CHAR) or
757 (p_foreign_key_name is null) ) then
758 -- dbms_output.put_line('p_foreign_key_name is null: '||p_flow_code||' '||p_page_code||' '||
759 -- p_parent_region_code);
760 if (p_caller <> AK_ON_OBJECTS_PVT.G_DOWNLOAD) then
761 -- dbms_output.put_line('p_caller = '||p_caller||' p_pass = '||to_char(p_pass));
762 if NOT AK_FLOW_PVT.REGION_RELATION_EXISTS (
763 p_api_version_number => 1.0,
764 p_return_status => l_return_status,
765 p_flow_application_id => p_flow_application_id,
766 p_flow_code => p_flow_code,
767 p_foreign_key_name => p_foreign_key_name,
768 p_from_page_appl_id => p_page_application_id,
769 p_from_page_code => p_page_code,
770 p_from_region_appl_id => p_parent_region_application_id,
771 p_from_region_code => p_parent_region_code,
772 p_to_page_appl_id => p_page_application_id,
773 p_to_page_code => p_page_code,
774 p_to_region_appl_id => p_region_application_id,
775 p_to_region_code => p_region_code) then
776 l_error := TRUE;
777 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
778 FND_MESSAGE.SET_NAME('AK','AK_NO_INTRAPAGE_RELATION');
779 FND_MSG_PUB.Add;
780 FND_MESSAGE.SET_NAME('AK','AK_INVALID_RELATION_REFERENCE');
781 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
782 ' ' || p_flow_code ||
783 ' ' || p_foreign_key_name ||
784 ' ' || to_char(p_page_application_id) ||
785 ' ' || p_page_code ||
786 ' ' || to_char(p_parent_region_application_id) ||
787 ' ' || p_parent_region_code ||
788 ' ' || p_region_code);
789 FND_MSG_PUB.Add;
790 end if;
791 end if; /* if not region_relation_exists */
792 elsif (p_caller = AK_ON_OBJECTS_PVT.G_DOWNLOAD) then
793 l_error := TRUE;
794 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
795 FND_MESSAGE.SET_NAME('AK','AK_NO_INTRAPAGE_RELATION');
796 FND_MSG_PUB.Add;
797 FND_MESSAGE.SET_NAME('AK','AK_INVALID_RELATION_REFERENCE');
798 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
799 ' ' || p_flow_code ||
800 ' ' || p_foreign_key_name ||
801 ' ' || to_char(p_page_application_id) ||
802 ' ' || p_page_code ||
803 ' ' || to_char(p_parent_region_application_id) ||
804 ' ' || p_parent_region_code ||
805 ' ' || p_region_code);
806 FND_MSG_PUB.Add;
807 end if;
808 end if; /* if caller is create */
809 end if; /* if p_foreign_key_name is null or missing */
810 end if; /* p_parent_region_application_id */
811 -- return true if no error, false otherwise
812 p_return_status := FND_API.G_RET_STS_SUCCESS;
813 return (not l_error);
814
815 EXCEPTION
816 WHEN FND_API.G_EXC_ERROR THEN
817 p_return_status := FND_API.G_RET_STS_ERROR;
818 return FALSE;
819 WHEN OTHERS THEN
820 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
821 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
822 SUBSTR (SQLERRM, 1, 240) );
823 FND_MSG_PUB.Add;
824 return FALSE;
825
826 end VALIDATE_PAGE_REGION;
827
828 --=======================================================
829 -- Function VALIDATE_PAGE_REGION_ITEM
830 --
831 -- Usage Private API for validating a flow page region item.
832 -- This API should only be called by other APIs that are
833 -- owned by the Core Modules Team (AK).
834 --
835 -- Desc Perform validation on a flow page region item record.
836 --
837 -- Results The API returns the standard p_return_status parameter
838 -- indicating one of the standard return statuses :
839 -- * Unexpected error
840 -- * Error
841 -- * Success
842 -- In addition, this function returns TRUE if all
843 -- validation tests are passed, or FALSE otherwise.
844 -- Parameters Flow Page Region Item columns
845 -- p_caller : IN required
846 -- Must be one of the following values defined
847 -- in package AK_ON_OBJECTS_PVT:
848 -- - G_CREATE (if calling from the Create API)
849 -- - G_DOWNLOAD (if calling from the Download API)
853 -- validation. It is not designed for item-level
850 -- - G_UPDATE (if calling from the Update API)
851 --
852 -- Note This API is intended for performing record-level
854 -- validation.
855 --
856 -- Version Initial version number = 1.0
857 -- History Current version number = 1.0
858 --=======================================================
859 function VALIDATE_PAGE_REGION_ITEM (
860 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
861 p_api_version_number IN NUMBER,
862 p_return_status OUT NOCOPY VARCHAR2,
863 p_flow_application_id IN NUMBER := FND_API.G_MISS_NUM,
864 p_flow_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
865 p_page_application_id IN NUMBER := FND_API.G_MISS_NUM,
866 p_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
867 p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
868 p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
869 p_attribute_application_id IN NUMBER := FND_API.G_MISS_NUM,
870 p_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
871 p_to_page_appl_id IN NUMBER := FND_API.G_MISS_NUM,
872 p_to_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
873 p_to_url_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
874 p_to_url_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
875 p_caller IN VARCHAR2,
876 p_pass IN NUMBER := 2
877 ) return BOOLEAN is
878 cursor l_check_to_url_csr is
879 select 1
880 from AK_ATTRIBUTES
881 where attribute_application_id = p_to_url_attribute_appl_id
882 and attribute_code = p_to_url_attribute_code
883 and upper(data_type) = 'URL';
884 cursor l_check_to_page_csr is
885 select 1
886 from AK_FLOW_REGION_RELATIONS
887 where flow_application_id = p_flow_application_id
888 and flow_code = p_flow_code
889 and from_page_appl_id = p_page_application_id
890 and from_page_code = p_page_code
891 and from_region_appl_id = p_region_application_id
892 and from_region_code = p_region_code
893 and to_page_appl_id = p_to_page_appl_id
894 and to_page_code = p_to_page_code;
895 l_api_version_number CONSTANT number := 1.0;
896 l_api_name CONSTANT varchar2(30) := 'Validate_Page_Region_Item';
897 l_database_object_name VARCHAR2(30);
898 l_dummy NUMBER;
899 l_error BOOLEAN;
900 l_return_status varchar2(1);
901 begin
902
903 IF NOT FND_API.Compatible_API_Call (
904 l_api_version_number, p_api_version_number, l_api_name,
905 G_PKG_NAME) then
906 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
907 return FALSE;
908 END IF;
909
910 l_error := FALSE;
911
912 --** if validation level is none, no validation is necessary
913 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
914 p_return_status := FND_API.G_RET_STS_SUCCESS;
915 return TRUE;
916 end if;
917
918 --** check that key columns are not null and not missing **
919 if ((p_flow_application_id is null) or
920 (p_flow_application_id = FND_API.G_MISS_NUM)) then
921 l_error := TRUE;
922 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
923 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
924 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_APPLICATION_ID');
925 FND_MSG_PUB.Add;
926 end if;
927 end if;
928
929 if ((p_flow_code is null) or
930 (p_flow_code = FND_API.G_MISS_CHAR)) then
931 l_error := TRUE;
932 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
933 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
934 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_CODE');
935 FND_MSG_PUB.Add;
936 end if;
937 end if;
938
939 if ((p_page_application_id is null) or
940 (p_page_application_id = FND_API.G_MISS_NUM)) then
941 l_error := TRUE;
942 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
943 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
944 FND_MESSAGE.SET_TOKEN('COLUMN', 'PAGE_APPLICATION_ID');
945 FND_MSG_PUB.Add;
946 end if;
947 end if;
948
949 if ((p_page_code is null) or
950 (p_page_code = FND_API.G_MISS_CHAR)) then
951 l_error := TRUE;
952 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
953 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
954 FND_MESSAGE.SET_TOKEN('COLUMN', 'PAGE_CODE');
955 FND_MSG_PUB.Add;
956 end if;
957 end if;
958
959 if ((p_region_application_id is null) or
960 (p_region_application_id = FND_API.G_MISS_NUM)) then
961 l_error := TRUE;
962 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
963 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
964 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
965 FND_MSG_PUB.Add;
966 end if;
967 end if;
968
969 if ((p_region_code is null) or
970 (p_region_code = FND_API.G_MISS_CHAR)) then
971 l_error := TRUE;
975 FND_MSG_PUB.Add;
972 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
973 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
974 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
976 end if;
977 end if;
978
979 if ((p_attribute_application_id is null) or
980 (p_attribute_application_id = FND_API.G_MISS_NUM)) then
981 l_error := TRUE;
982 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
983 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
984 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
985 FND_MSG_PUB.Add;
986 end if;
987 end if;
988
989 if ((p_attribute_code is null) or
990 (p_attribute_code = FND_API.G_MISS_CHAR)) then
991 l_error := TRUE;
992 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
993 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
994 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
995 FND_MSG_PUB.Add;
996 end if;
997 end if;
998
999 --
1000 -- Check that the parent flow page region exists
1001 --
1002 if (NOT AK_FLOW_PVT.PAGE_REGION_EXISTS (
1003 p_api_version_number => 1.0,
1004 p_return_status => l_return_status,
1005 p_flow_application_id => p_flow_application_id,
1006 p_flow_code => p_flow_code,
1007 p_page_application_id => p_page_application_id,
1008 p_page_code => p_page_code,
1009 p_region_application_id => p_region_application_id,
1010 p_region_code => p_region_code) ) then
1011 l_error := TRUE;
1012 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1013 FND_MESSAGE.SET_NAME('AK','AK_INVALID_PG_REGION_REFERENCE');
1014 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1015 ' ' || p_flow_code ||
1016 ' ' || to_char(p_page_application_id) ||
1017 ' ' || p_page_code ||
1018 ' ' || to_char(p_region_application_id) ||
1019 ' ' || p_region_code);
1020 FND_MSG_PUB.Add;
1021 end if;
1022 end if;
1023
1024 --
1025 -- Check that the region item exists in AK_REGION_ITEMS
1026 --
1027 if (NOT AK_REGION_PVT.ITEM_EXISTS (
1028 p_api_version_number => 1.0,
1029 p_return_status => l_return_status,
1030 p_region_application_id => p_region_application_id,
1031 p_region_code => p_region_code,
1032 p_attribute_application_id => p_attribute_application_id,
1033 p_attribute_code => p_attribute_code) ) then
1034 l_error := TRUE;
1035 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1036 FND_MESSAGE.SET_NAME('AK','AK_INVALID_REG_ITEM_REFERENCE');
1037 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
1038 ' ' || p_region_code ||
1039 ' ' || to_char(p_attribute_application_id) ||
1040 ' ' || p_attribute_code);
1041 FND_MSG_PUB.Add;
1042 end if;
1043 end if;
1044
1045 --
1046 --** check that required columns are not null and, unless calling **
1047 --** from UPDATE procedure, the columns are not missing **
1048 --
1049 -- - if to_page_appl_id is given, to_page_code must also be given, and
1050 -- vice versa.
1051 --
1052 if (p_to_page_appl_id is not null) and
1053 (p_to_page_appl_id <> FND_API.G_MISS_NUM) and
1054 ((p_to_page_code is null) or (p_to_page_code = FND_API.G_MISS_CHAR)) then
1055 l_error := TRUE;
1056 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1057 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1058 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_PAGE_CODE');
1059 FND_MSG_PUB.Add;
1060 end if;
1061 end if;
1062
1063 if (p_to_page_code is not null) and
1064 (p_to_page_code <> FND_API.G_MISS_CHAR) and
1065 ((p_to_page_appl_id is null) or
1066 (p_to_page_appl_id = FND_API.G_MISS_NUM)) then
1067 l_error := TRUE;
1068 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1069 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1070 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_PAGE_APPL_ID');
1071 FND_MSG_PUB.Add;
1072 end if;
1073 end if;
1074
1075 --
1076 -- - if to_url_attribute_appl_id is given, to_url_attribute_code must
1077 -- also be given, and vice versa.
1078 --
1079 if (p_to_url_attribute_appl_id is not null) and
1080 (p_to_url_attribute_appl_id <> FND_API.G_MISS_NUM) and
1081 ((p_to_url_attribute_code is null) or
1082 (p_to_url_attribute_code = FND_API.G_MISS_CHAR)) then
1083 l_error := TRUE;
1084 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1085 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1086 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_URL_ATTRIBUTE_CODE');
1087 FND_MSG_PUB.Add;
1088 end if;
1089 end if;
1090
1091 if (p_to_url_attribute_code is not null) and
1092 (p_to_url_attribute_code <> FND_API.G_MISS_CHAR) and
1093 ((p_to_url_attribute_appl_id is null) or
1094 (p_to_url_attribute_appl_id = FND_API.G_MISS_NUM)) then
1098 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_URL_ATTRIBUTE_APPL_ID');
1095 l_error := TRUE;
1096 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1097 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1099 FND_MSG_PUB.Add;
1100 end if;
1101 end if;
1102
1103 --
1104 -- - either to_page or to_url_attribute must be specified,
1105 -- unless calling from update.
1106 --
1107 if ( (p_to_page_code is null) or
1108 (p_to_page_code = FND_API.G_MISS_CHAR) ) and
1109 ( (p_to_url_attribute_code is null) or
1110 (p_to_url_attribute_code = FND_API.G_MISS_CHAR) ) then
1111 l_error := TRUE;
1112 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1113 FND_MESSAGE.SET_NAME('AK','AK_NO_LINK_SELECTED');
1114 FND_MSG_PUB.Add;
1115 end if;
1116 end if;
1117
1118 --
1119 -- - cannot specify both to_page and to_url_attribute
1120 --
1121 if ( (p_to_page_code is not null) and
1122 (p_to_page_code <> FND_API.G_MISS_CHAR) ) and
1123 ( (p_to_url_attribute_code is not null) and
1124 (p_to_url_attribute_code <> FND_API.G_MISS_CHAR) ) then
1125 l_error := TRUE;
1126 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1127 FND_MESSAGE.SET_NAME('AK','AK_TWO_LINK_SELECTED');
1128 FND_MSG_PUB.Add;
1129 end if;
1130 end if;
1131
1132 --
1133 --** Validate columns **
1134 --
1135 -- to_page_appl_id and to_page_code
1136 --
1137 if not ( ( (p_to_page_appl_id = FND_API.G_MISS_NUM) or
1138 (p_to_page_appl_id is null) )
1139 or
1140 ( (p_to_page_code = FND_API.G_MISS_CHAR) or
1141 (p_to_page_code is null) ) ) then
1142 --
1143 -- 1. The target page must exist
1144 --
1145 if (NOT AK_FLOW_PVT.PAGE_EXISTS (
1146 p_api_version_number => 1.0,
1147 p_return_status => l_return_status,
1148 p_flow_application_id => p_flow_application_id,
1149 p_flow_code => p_flow_code,
1150 p_page_application_id => p_to_page_appl_id,
1151 p_page_code => p_to_page_code) ) then
1152 l_error := TRUE;
1153 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1154 FND_MESSAGE.SET_NAME('AK','AK_NO_REGION_RELATION');
1155 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1156 ' ' || p_flow_code ||
1157 ' ' || to_char(p_to_page_appl_id) ||
1158 ' ' || p_to_page_code);
1159 FND_MSG_PUB.Add;
1160 end if;
1161 end if; /* if PAGE_EXISTS */
1162 --
1163 -- 2. There must be a region relation linking this region and the
1164 -- target page
1165 --
1166 open l_check_to_page_csr;
1167 fetch l_check_to_page_csr into l_dummy;
1168 if (l_check_to_page_csr%notfound) then
1169 l_error := TRUE;
1170 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1171 FND_MESSAGE.SET_NAME('AK','AK_INVALID_TO_PAGE_REFERENCE');
1172 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1173 ' ' || p_flow_code ||
1174 ' ' || to_char(p_to_page_appl_id) ||
1175 ' ' || p_to_page_code);
1176 FND_MSG_PUB.Add;
1177 end if;
1178 end if; /* if l_check_to_page */
1179 close l_check_to_page_csr;
1180
1181 end if; /* if to_page_appl_id and to_page_attribute_code */
1182
1183 --
1184 -- to_url_attribute_appl_id and to_url_attribute_code
1185 --
1186 -- - To URL attribute must be a 'URL' type attribute
1187 --
1188 if (p_to_url_attribute_appl_id <> FND_API.G_MISS_NUM and
1189 p_to_url_attribute_appl_id is not null) OR
1190 (p_to_url_attribute_code <> FND_API.G_MISS_CHAR and
1191 p_to_url_attribute_code is not null) then
1192 open l_check_to_url_csr;
1193 fetch l_check_to_url_csr into l_dummy;
1194 if (l_check_to_url_csr%notfound) then
1195 l_error := TRUE;
1196 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1197 FND_MESSAGE.SET_NAME('AK','AK_INVALID_TO_URL_ATTRIBUTE');
1198 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_to_url_attribute_appl_id) ||
1199 ' ' || p_to_url_attribute_code);
1200 FND_MSG_PUB.Add;
1201 end if;
1202 end if;
1203 close l_check_to_url_csr;
1204 end if;
1205
1206 -- return true if no error, false otherwise
1207 p_return_status := FND_API.G_RET_STS_SUCCESS;
1208 return (not l_error);
1209
1210 EXCEPTION
1211 WHEN FND_API.G_EXC_ERROR THEN
1212 p_return_status := FND_API.G_RET_STS_ERROR;
1213 return FALSE;
1214 WHEN OTHERS THEN
1215 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1216 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1217 SUBSTR (SQLERRM, 1, 240) );
1218 FND_MSG_PUB.Add;
1219 return FALSE;
1220
1221 end VALIDATE_PAGE_REGION_ITEM;
1222
1223 --=======================================================
1224 -- Function VALIDATE_REGION_RELATION
1225 --
1226 -- Usage Private API for validating a flow region relation.
1230 -- Desc Perform validation on a flow region relation record.
1227 -- This API should only be called by other APIs that are
1228 -- owned by the Core Modules Team (AK).
1229 --
1231 --
1232 -- Results The API returns the standard p_return_status parameter
1233 -- indicating one of the standard return statuses :
1234 -- * Unexpected error
1235 -- * Error
1236 -- * Success
1237 -- In addition, this function returns TRUE if all
1238 -- validation tests are passed, or FALSE otherwise.
1239 -- Parameters Flow Region Relation columns
1240 -- p_caller : IN required
1241 -- Must be one of the following values defined
1242 -- in package AK_ON_OBJECTS_PVT:
1243 -- - G_CREATE (if calling from the Create API)
1244 -- - G_DOWNLOAD (if calling from the Download API)
1245 -- - G_UPDATE (if calling from the Update API)
1246 --
1247 -- Note This API is intended for performing record-level
1248 -- validation. It is not designed for item-level
1249 -- validation.
1250 --
1251 -- Version Initial version number = 1.0
1252 -- History Current version number = 1.0
1253 --=======================================================
1254 function VALIDATE_REGION_RELATION (
1255 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1256 p_api_version_number IN NUMBER,
1257 p_return_status OUT NOCOPY VARCHAR2,
1258 p_flow_application_id IN NUMBER := FND_API.G_MISS_NUM,
1259 p_flow_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1260 p_foreign_key_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1261 p_from_page_appl_id IN NUMBER := FND_API.G_MISS_NUM,
1262 p_from_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1263 p_from_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
1264 p_from_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1265 p_to_page_appl_id IN NUMBER := FND_API.G_MISS_NUM,
1266 p_to_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1267 p_to_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
1268 p_to_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1269 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
1270 p_caller IN VARCHAR2,
1271 p_pass IN NUMBER := 2
1272 ) return BOOLEAN is
1273 cursor l_check_fk_connect_csr is
1274 select 1
1275 from ak_foreign_keys fk, ak_unique_keys uk,
1276 ak_regions ar1, ak_regions ar2
1277 where fk.database_object_name = ar1.database_object_name
1278 and fk.unique_key_name = uk.unique_key_name
1279 and uk.database_object_name = ar2.database_object_name
1280 and ar1.region_application_id = p_from_region_appl_id
1281 and ar1.region_code = p_from_region_code
1282 and ar2.region_application_id = p_to_region_appl_id
1283 and ar2.region_code = p_to_region_code
1284 and fk.foreign_key_name = p_foreign_key_name
1285 UNION
1286 select 1
1287 from ak_foreign_keys fk, ak_unique_keys uk,
1288 ak_regions ar1, ak_regions ar2
1289 where fk.database_object_name = ar2.database_object_name
1290 and fk.unique_key_name = uk.unique_key_name
1291 and uk.database_object_name = ar1.database_object_name
1292 and ar1.region_application_id = p_from_region_appl_id
1293 and ar1.region_code = p_from_region_code
1294 and ar2.region_application_id = p_to_region_appl_id
1295 and ar2.region_code = p_to_region_code
1296 and fk.foreign_key_name = p_foreign_key_name;
1297 l_api_version_number CONSTANT number := 1.0;
1298 l_api_name CONSTANT varchar2(30) := 'Validate_Region_Relation';
1299 l_dummy NUMBER;
1300 l_error BOOLEAN;
1301 l_return_status varchar2(1);
1302 begin
1303
1304 IF NOT FND_API.Compatible_API_Call (
1305 l_api_version_number, p_api_version_number, l_api_name,
1306 G_PKG_NAME) then
1307 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1308 return FALSE;
1309 END IF;
1310
1311 l_error := FALSE;
1312
1313 --** if validation level is none, no validation is necessary
1314 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
1315 p_return_status := FND_API.G_RET_STS_SUCCESS;
1316 return TRUE;
1317 end if;
1318
1319 --** check that key columns are not null and not missing **
1320 if ((p_flow_application_id is null) or
1321 (p_flow_application_id = FND_API.G_MISS_NUM)) then
1322 l_error := TRUE;
1323 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1324 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1325 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_APPLICATION_ID');
1326 FND_MSG_PUB.Add;
1327 end if;
1328 end if;
1329
1330 if ((p_flow_code is null) or
1331 (p_flow_code = FND_API.G_MISS_CHAR)) then
1332 l_error := TRUE;
1333 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1334 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1335 FND_MESSAGE.SET_TOKEN('COLUMN', 'FLOW_CODE');
1336 FND_MSG_PUB.Add;
1337 end if;
1338 end if;
1339
1340 if ((p_foreign_key_name is null) or
1344 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1341 (p_foreign_key_name = FND_API.G_MISS_CHAR)) then
1342 l_error := TRUE;
1343 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1345 FND_MESSAGE.SET_TOKEN('COLUMN', 'FOREIGN_KEY_NAME');
1346 FND_MSG_PUB.Add;
1347 end if;
1348 end if;
1349
1350 if ((p_from_page_appl_id is null) or
1351 (p_from_page_appl_id = FND_API.G_MISS_NUM)) then
1352 l_error := TRUE;
1353 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1354 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1355 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_PAGE_APPL_ID');
1356 FND_MSG_PUB.Add;
1357 end if;
1358 end if;
1359
1360 if ((p_from_page_code is null) or
1361 (p_from_page_code = FND_API.G_MISS_CHAR)) then
1362 l_error := TRUE;
1363 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1364 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1365 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_PAGE_CODE');
1366 FND_MSG_PUB.Add;
1367 end if;
1368 end if;
1369
1370 if ((p_from_region_appl_id is null) or
1371 (p_from_region_appl_id = FND_API.G_MISS_NUM)) then
1372 l_error := TRUE;
1373 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1374 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1375 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_REGION_APPL_ID');
1376 FND_MSG_PUB.Add;
1377 end if;
1378 end if;
1379
1380 if ((p_from_region_code is null) or
1381 (p_from_region_code = FND_API.G_MISS_CHAR)) then
1382 l_error := TRUE;
1383 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1384 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1385 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_REGION_CODE');
1386 FND_MSG_PUB.Add;
1387 end if;
1388 end if;
1389
1390 if ((p_to_page_appl_id is null) or
1391 (p_to_page_appl_id = FND_API.G_MISS_NUM)) then
1392 l_error := TRUE;
1393 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1394 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1395 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_PAGE_APPL_ID');
1396 FND_MSG_PUB.Add;
1397 end if;
1398 end if;
1399
1400 if ((p_to_page_code is null) or
1401 (p_to_page_code = FND_API.G_MISS_CHAR)) then
1402 l_error := TRUE;
1403 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1404 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1405 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_PAGE_CODE');
1406 FND_MSG_PUB.Add;
1407 end if;
1408 end if;
1409
1410 if ((p_to_region_appl_id is null) or
1411 (p_to_region_appl_id = FND_API.G_MISS_NUM)) then
1412 l_error := TRUE;
1413 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1414 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1415 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_REGION_APPL_ID');
1416 FND_MSG_PUB.Add;
1417 end if;
1418 end if;
1419
1420 if ((p_to_region_code is null) or
1421 (p_to_region_code = FND_API.G_MISS_CHAR)) then
1422 l_error := TRUE;
1423 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1424 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1425 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_REGION_CODE');
1426 FND_MSG_PUB.Add;
1427 end if;
1428 end if;
1429
1430 --
1431 -- Check that the parent flow exists
1432 --
1433 if (NOT AK_FLOW_PVT.FLOW_EXISTS (
1434 p_api_version_number => 1.0,
1435 p_return_status => l_return_status,
1436 p_flow_application_id => p_flow_application_id,
1437 p_flow_code => p_flow_code) ) then
1438 l_error := TRUE;
1439 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1440 FND_MESSAGE.SET_NAME('AK','AK_INVALID_FLOW_REFERENCE');
1441 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1442 ' ' || p_flow_code);
1443 FND_MSG_PUB.Add;
1444 end if;
1445 end if;
1446
1447 --
1448 -- Check that the foreign key exists
1449 --
1450 if (NOT AK_KEY_PVT.FOREIGN_KEY_EXISTS (
1451 p_api_version_number => 1.0,
1452 p_return_status => l_return_status,
1453 p_foreign_key_name => p_foreign_key_name) ) then
1454 l_error := TRUE;
1455 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1456 FND_MESSAGE.SET_NAME('AK','AK_INVALID_FK_REFERENCE');
1457 FND_MESSAGE.SET_TOKEN('KEY', p_foreign_key_name);
1458 FND_MSG_PUB.Add;
1459 end if;
1460 end if;
1461
1462 --
1463 -- Check that the from page region exists
1464 --
1465 if NOT AK_FLOW_PVT.PAGE_REGION_EXISTS (
1466 p_api_version_number => 1.0,
1467 p_return_status => l_return_status,
1468 p_flow_application_id => p_flow_application_id,
1469 p_flow_code => p_flow_code,
1470 p_page_application_id => p_from_page_appl_id,
1471 p_page_code => p_from_page_code,
1472 p_region_application_id => p_from_region_appl_id,
1473 p_region_code => p_from_region_code) then
1474 l_error := TRUE;
1475 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1476 FND_MESSAGE.SET_NAME('AK','AK_INVALID_PG_REGION_REFERENCE');
1477 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1478 ' ' || p_flow_code ||
1479 ' ' || to_char(p_from_page_appl_id) ||
1480 ' ' || p_from_page_code ||
1481 ' ' || to_char(p_from_region_appl_id) ||
1482 ' ' || p_from_region_code);
1483 FND_MSG_PUB.Add;
1484 end if;
1485 end if;
1486
1487 --
1488 -- Check that the to page region exists
1489 --
1490 if NOT AK_FLOW_PVT.PAGE_REGION_EXISTS (
1491 p_api_version_number => 1.0,
1492 p_return_status => l_return_status,
1493 p_flow_application_id => p_flow_application_id,
1494 p_flow_code => p_flow_code,
1495 p_page_application_id => p_to_page_appl_id,
1496 p_page_code => p_to_page_code,
1497 p_region_application_id => p_to_region_appl_id,
1498 p_region_code => p_to_region_code) then
1499 l_error := TRUE;
1500 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1501 FND_MESSAGE.SET_NAME('AK','AK_INVALID_PG_REGION_REFERENCE');
1502 FND_MESSAGE.SET_TOKEN('REF_OBJECT','AK_FLOW_PAGE_REGION', TRUE);
1503 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1504 ' ' || p_flow_code ||
1505 ' ' || to_char(p_to_page_appl_id) ||
1506 ' ' || p_to_page_code ||
1507 ' ' || to_char(p_to_region_appl_id) ||
1508 ' ' || p_to_region_code);
1509 FND_MSG_PUB.Add;
1510 end if;
1511 end if;
1512
1513 --
1514 --** check that required columns are not null and, unless calling **
1515 --** from UPDATE procedure, the columns are not missing **
1516 --
1517 if (p_application_id is null) or
1518 ((p_application_id = FND_API.G_MISS_NUM) and
1519 (p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
1520 l_error := TRUE;
1521 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1522 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1523 FND_MESSAGE.SET_TOKEN('COLUMN', 'APPLICATION_ID');
1524 FND_MSG_PUB.Add;
1525 end if;
1526 end if;
1527
1528 --
1529 -- Validate columns
1530 --
1531 -- - application ID
1532 --
1533 if (p_application_id <> FND_API.G_MISS_NUM) then
1534 if (NOT AK_ON_OBJECTS_PVT.VALID_APPLICATION_ID (
1535 p_api_version_number => 1.0,
1536 p_return_status => l_return_status,
1537 p_application_id => p_application_id)
1538 ) then
1539 l_error := TRUE;
1540 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1541 FND_MESSAGE.SET_NAME('AK','AK_INVALID_COLUMN_VALUE');
1542 FND_MESSAGE.SET_TOKEN('COLUMN','APPLICATION_ID');
1543 FND_MSG_PUB.Add;
1544 end if;
1545 end if;
1546 end if;
1547
1548 --
1549 -- - from page region must be different than target page region
1550 --
1551 if (p_from_page_appl_id = p_to_page_appl_id) and
1552 (p_from_page_code = p_to_page_code) and
1553 (p_from_region_appl_id = p_to_region_appl_id) and
1554 (p_from_region_code = p_to_region_code) then
1555 l_error := TRUE;
1556 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1557 FND_MESSAGE.SET_NAME('AK','AK_INVALID_TARGET_REGION');
1558 FND_MSG_PUB.Add;
1559 end if;
1560 end if;
1561
1562 --
1563 -- - foreign key name must provide connection between the
1564 -- from page region and the to page region
1565 --
1566 open l_check_fk_connect_csr;
1567 fetch l_check_fk_connect_csr into l_dummy;
1568 if (l_check_fk_connect_csr%notfound) then
1569 l_error := TRUE;
1570 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1571 FND_MESSAGE.SET_NAME('AK','AK_INVALID_FOREIGN_KEY');
1572 FND_MSG_PUB.Add;
1573 end if;
1574 end if;
1575 close l_check_fk_connect_csr;
1576
1577 -- return true if no error, false otherwise
1578 p_return_status := FND_API.G_RET_STS_SUCCESS;
1579 return (not l_error);
1580
1581 EXCEPTION
1582 WHEN FND_API.G_EXC_ERROR THEN
1583 p_return_status := FND_API.G_RET_STS_ERROR;
1584 return FALSE;
1585 WHEN OTHERS THEN
1586 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1587 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1588 SUBSTR (SQLERRM, 1, 240) );
1589 FND_MSG_PUB.Add;
1590 return FALSE;
1591
1592 end VALIDATE_REGION_RELATION;
1593
1594 end AK_FLOW3_PVT;
[Home] [Help]