[Home] [Help]
PACKAGE BODY: APPS.AK_FLOW_PVT
Source
1 package body AK_FLOW_PVT as
2 /* $Header: akdvflob.pls 120.3 2005/09/15 22:18:27 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 FLOW_EXISTS
16 --
17 -- Usage Private API for checking for the existence of
18 -- a flow with the given key values. This
19 -- API should only be called by other APIs that are
20 -- owned by the Core Modules Team (AK).
21 --
22 -- Desc This API check to see if a flow record
23 -- exists with the given key values.
24 --
25 -- Results The API returns the standard p_return_status parameter
26 -- indicating one of the standard return statuses :
27 -- * Unexpected error
28 -- * Error
29 -- * Success
30 -- This function will return TRUE if such a flow
31 -- exists, or FALSE otherwise.
32 -- Parameters Flow key columns
33 --
34 -- Version Initial version number = 1.0
35 -- History Current version number = 1.0
36 --=======================================================
37 function FLOW_EXISTS (
38 p_api_version_number IN NUMBER,
39 p_return_status OUT NOCOPY VARCHAR2,
40 p_flow_application_id IN NUMBER,
41 p_flow_code IN VARCHAR2
42 ) return BOOLEAN is
43 cursor l_check_csr is
44 select 1
45 from AK_FLOWS
46 where flow_application_id = p_flow_application_id
47 and flow_code = p_flow_code;
48 l_api_version_number CONSTANT number := 1.0;
49 l_api_name CONSTANT varchar2(30) := 'Flow_Exists';
50 l_dummy number;
51 begin
52 IF NOT FND_API.Compatible_API_Call (
53 l_api_version_number, p_api_version_number, l_api_name,
54 G_PKG_NAME) then
55 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
56 return FALSE;
57 END IF;
58
59 open l_check_csr;
60 fetch l_check_csr into l_dummy;
61 if (l_check_csr%notfound) then
62 close l_check_csr;
63 p_return_status := FND_API.G_RET_STS_SUCCESS;
64 return FALSE;
65 else
66 close l_check_csr;
67 p_return_status := FND_API.G_RET_STS_SUCCESS;
68 return TRUE;
69 end if;
70
71 EXCEPTION
72 WHEN FND_API.G_EXC_ERROR THEN
73 p_return_status := FND_API.G_RET_STS_ERROR;
74 return FALSE;
75 WHEN OTHERS THEN
76 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
78 SUBSTR (SQLERRM, 1, 240) );
79 FND_MSG_PUB.Add;
80 return FALSE;
81 end FLOW_EXISTS;
82
83 --=======================================================
84 -- Function PAGE_EXISTS
85 --
86 -- Usage Private API for checking for the existence of
87 -- a flow page with the given key values. This
88 -- API should only be called by other APIs that are
89 -- owned by the Core Modules Team (AK).
90 --
91 -- Desc This API check to see if a flow page record
92 -- exists with the given key values.
93 --
94 -- Results The API returns the standard p_return_status parameter
95 -- indicating one of the standard return statuses :
96 -- * Unexpected error
97 -- * Error
98 -- * Success
99 -- This function will return TRUE if such a flow
100 -- exists, or FALSE otherwise.
101 -- Parameters Flow Page key columns
102 --
103 -- Version Initial version number = 1.0
104 -- History Current version number = 1.0
105 --=======================================================
106 function PAGE_EXISTS (
107 p_api_version_number IN NUMBER,
108 p_return_status OUT NOCOPY VARCHAR2,
109 p_flow_application_id IN NUMBER,
110 p_flow_code IN VARCHAR2,
111 p_page_application_id IN NUMBER,
112 p_page_code IN VARCHAR2
113 ) return BOOLEAN is
114 cursor l_check_csr is
115 select 1
116 from AK_FLOW_PAGES
117 where flow_application_id = p_flow_application_id
118 and flow_code = p_flow_code
119 and page_application_id = p_page_application_id
120 and page_code = p_page_code;
121 l_api_version_number CONSTANT number := 1.0;
122 l_api_name CONSTANT varchar2(30) := 'Page_Exists';
123 l_dummy number;
124 begin
125 IF NOT FND_API.Compatible_API_Call (
126 l_api_version_number, p_api_version_number, l_api_name,
127 G_PKG_NAME) then
128 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129 return FALSE;
130 END IF;
131
132 open l_check_csr;
133 fetch l_check_csr into l_dummy;
134 if (l_check_csr%notfound) then
135 close l_check_csr;
136 p_return_status := FND_API.G_RET_STS_SUCCESS;
137 return FALSE;
138 else
139 close l_check_csr;
140 p_return_status := FND_API.G_RET_STS_SUCCESS;
141 return TRUE;
142 end if;
143
144 EXCEPTION
145 WHEN FND_API.G_EXC_ERROR THEN
146 p_return_status := FND_API.G_RET_STS_ERROR;
147 return FALSE;
148 WHEN OTHERS THEN
149 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
151 SUBSTR (SQLERRM, 1, 240) );
152 FND_MSG_PUB.Add;
153 return FALSE;
154 end PAGE_EXISTS;
155
156 --=======================================================
157 -- Function PAGE_REGION_EXISTS
158 --
159 -- Usage Private API for checking for the existence of
160 -- a flow page region with the given key values. This
161 -- API should only be called by other APIs that are
162 -- owned by the Core Modules Team (AK).
163 --
164 -- Desc This API check to see if a flow page region record
165 -- exists with the given key values.
166 --
167 -- Results The API returns the standard p_return_status parameter
168 -- indicating one of the standard return statuses :
169 -- * Unexpected error
170 -- * Error
171 -- * Success
172 -- This function will return TRUE if such a flow
173 -- exists, or FALSE otherwise.
174 -- Parameters Flow Page Region key columns
175 --
176 -- Version Initial version number = 1.0
177 -- History Current version number = 1.0
178 --=======================================================
179 function PAGE_REGION_EXISTS (
180 p_api_version_number IN NUMBER,
181 p_return_status OUT NOCOPY VARCHAR2,
182 p_flow_application_id IN NUMBER,
183 p_flow_code IN VARCHAR2,
184 p_page_application_id IN NUMBER,
185 p_page_code IN VARCHAR2,
186 p_region_application_id IN NUMBER,
187 p_region_code IN VARCHAR2
188 ) return BOOLEAN is
189 cursor l_check_csr is
190 select 1
191 from AK_FLOW_PAGE_REGIONS
192 where flow_application_id = p_flow_application_id
193 and flow_code = p_flow_code
194 and page_application_id = p_page_application_id
195 and page_code = p_page_code
196 and region_application_id = p_region_application_id
197 and region_code = p_region_code;
198 l_api_version_number CONSTANT number := 1.0;
199 l_api_name CONSTANT varchar2(30) := 'Page_Region_Exists';
200 l_dummy number;
201 begin
202 IF NOT FND_API.Compatible_API_Call (
203 l_api_version_number, p_api_version_number, l_api_name,
204 G_PKG_NAME) then
205 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 return FALSE;
207 END IF;
208
209 open l_check_csr;
210 fetch l_check_csr into l_dummy;
211 if (l_check_csr%notfound) then
212 close l_check_csr;
213 p_return_status := FND_API.G_RET_STS_SUCCESS;
214 return FALSE;
215 else
216 close l_check_csr;
217 p_return_status := FND_API.G_RET_STS_SUCCESS;
218 return TRUE;
219 end if;
220
221 EXCEPTION
222 WHEN FND_API.G_EXC_ERROR THEN
223 p_return_status := FND_API.G_RET_STS_ERROR;
224 return FALSE;
225 WHEN OTHERS THEN
226 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
228 SUBSTR (SQLERRM, 1, 240) );
229 FND_MSG_PUB.Add;
230 return FALSE;
231 end PAGE_REGION_EXISTS;
232
233 --=======================================================
234 -- Function PAGE_REGION_ITEM_EXISTS
235 --
236 -- Usage Private API for checking for the existence of
237 -- a flow page region item with the given key values.
238 -- This API should only be called by other APIs that are
239 -- owned by the Core Modules Team (AK).
240 --
241 -- Desc This API check to see if a flow page region item record
242 -- exists with the given key values.
243 --
244 -- Results The API returns the standard p_return_status parameter
245 -- indicating one of the standard return statuses :
246 -- * Unexpected error
247 -- * Error
248 -- * Success
249 -- This function will return TRUE if such a flow
250 -- exists, or FALSE otherwise.
251 -- Parameters Flow Page Region Item key columns
252 --
253 -- Version Initial version number = 1.0
254 -- History Current version number = 1.0
255 --=======================================================
256 function PAGE_REGION_ITEM_EXISTS (
257 p_api_version_number IN NUMBER,
258 p_return_status OUT NOCOPY VARCHAR2,
259 p_flow_application_id IN NUMBER,
260 p_flow_code IN VARCHAR2,
261 p_page_application_id IN NUMBER,
262 p_page_code IN VARCHAR2,
263 p_region_application_id IN NUMBER,
264 p_region_code IN VARCHAR2,
265 p_attribute_application_id IN NUMBER,
266 p_attribute_code IN VARCHAR2
267 ) return BOOLEAN is
268 cursor l_check_csr is
269 select 1
270 from AK_FLOW_PAGE_REGION_ITEMS
271 where flow_application_id = p_flow_application_id
272 and flow_code = p_flow_code
273 and page_application_id = p_page_application_id
274 and page_code = p_page_code
275 and region_application_id = p_region_application_id
276 and region_code = p_region_code
277 and attribute_application_id = p_attribute_application_id
278 and attribute_code = p_attribute_code;
279 l_api_version_number CONSTANT number := 1.0;
280 l_api_name CONSTANT varchar2(30) := 'Page_Region_Item_Exists';
281 l_dummy number;
282 begin
283 IF NOT FND_API.Compatible_API_Call (
284 l_api_version_number, p_api_version_number, l_api_name,
285 G_PKG_NAME) then
286 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287 return FALSE;
288 END IF;
289
290 open l_check_csr;
291 fetch l_check_csr into l_dummy;
292 if (l_check_csr%notfound) then
293 close l_check_csr;
294 p_return_status := FND_API.G_RET_STS_SUCCESS;
295 return FALSE;
296 else
297 close l_check_csr;
298 p_return_status := FND_API.G_RET_STS_SUCCESS;
299 return TRUE;
300 end if;
301
302 EXCEPTION
303 WHEN FND_API.G_EXC_ERROR THEN
304 p_return_status := FND_API.G_RET_STS_ERROR;
305 return FALSE;
306 WHEN OTHERS THEN
307 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
308 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
309 SUBSTR (SQLERRM, 1, 240) );
310 FND_MSG_PUB.Add;
311 return FALSE;
312 end PAGE_REGION_ITEM_EXISTS;
313
314 --=======================================================
315 -- Function REGION_RELATION_EXISTS
316 --
317 -- Usage Private API for checking for the existence of
318 -- a flow region relation with the given key values.
319 -- This API should only be called by other APIs that are
320 -- owned by the Core Modules Team (AK).
321 --
322 -- Desc This API check to see if a flow region relation record
323 -- exists with the given key values.
324 --
325 -- Results The API returns the standard p_return_status parameter
326 -- indicating one of the standard return statuses :
327 -- * Unexpected error
328 -- * Error
329 -- * Success
330 -- This function will return TRUE if such a flow
331 -- exists, or FALSE otherwise.
332 -- Parameters Flow Region Relation key columns
333 --
334 -- Version Initial version number = 1.0
335 -- History Current version number = 1.0
336 --=======================================================
337 function REGION_RELATION_EXISTS (
338 p_api_version_number IN NUMBER,
339 p_return_status OUT NOCOPY VARCHAR2,
340 p_flow_application_id IN NUMBER,
341 p_flow_code IN VARCHAR2,
342 p_foreign_key_name IN VARCHAR2,
343 p_from_page_appl_id IN NUMBER,
344 p_from_page_code IN VARCHAR2,
345 p_from_region_appl_id IN NUMBER,
346 p_from_region_code IN VARCHAR2,
347 p_to_page_appl_id IN NUMBER,
348 p_to_page_code IN VARCHAR2,
349 p_to_region_appl_id IN NUMBER,
350 p_to_region_code IN VARCHAR2
351 ) return BOOLEAN is
352 cursor l_check_csr is
353 select 1
354 from AK_FLOW_REGION_RELATIONS
355 where flow_application_id = p_flow_application_id
356 and flow_code = p_flow_code
357 and foreign_key_name = p_foreign_key_name
358 and from_page_appl_id = p_from_page_appl_id
359 and from_page_code = p_from_page_code
360 and from_region_appl_id = p_from_region_appl_id
361 and from_region_code = p_from_region_code
362 and to_page_appl_id = p_to_page_appl_id
363 and to_page_code = p_to_page_code
364 and to_region_appl_id = p_to_region_appl_id
365 and to_region_code = p_to_region_code;
366 l_api_version_number CONSTANT number := 1.0;
367 l_api_name CONSTANT varchar2(30) := 'Region_Relation_Exists';
368 l_dummy number;
369 begin
370 IF NOT FND_API.Compatible_API_Call (
371 l_api_version_number, p_api_version_number, l_api_name,
372 G_PKG_NAME) then
373 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 return FALSE;
375 END IF;
376
377 open l_check_csr;
378 fetch l_check_csr into l_dummy;
379 if (l_check_csr%notfound) then
380 close l_check_csr;
381 p_return_status := FND_API.G_RET_STS_SUCCESS;
382 return FALSE;
383 else
384 close l_check_csr;
385 p_return_status := FND_API.G_RET_STS_SUCCESS;
386 return TRUE;
387 end if;
388
389 EXCEPTION
390 WHEN FND_API.G_EXC_ERROR THEN
391 p_return_status := FND_API.G_RET_STS_ERROR;
392 return FALSE;
393 WHEN OTHERS THEN
394 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
396 SUBSTR (SQLERRM, 1, 240) );
397 FND_MSG_PUB.Add;
398 return FALSE;
399 end REGION_RELATION_EXISTS;
400
401 --=======================================================
402 -- Procedure CREATE_FLOW
403 --
404 -- Usage Private API for creating a flow. This
405 -- API should only be called by other APIs that are
406 -- owned by the Core Modules Team (AK).
407 --
408 -- Desc Creates a flow using the given info. This
409 -- API should only be called by other APIs that are
410 -- owned by the Core Modules Team (AK).
411 --
412 -- Results The API returns the standard p_return_status parameter
416 -- * Success
413 -- indicating one of the standard return statuses :
414 -- * Unexpected error
415 -- * Error
417 -- Parameters Flow columns except primary_page_appl_id and
418 -- primary_page_code since there are no
419 -- flow pages for this flow at this time.
420 -- p_loader_timestamp : IN optional
421 -- If a timestamp is passed, the API will create the
422 -- record using this timestamp. Only the upload API
423 -- should call with this parameter loaded.
424 --
425 -- Version Initial version number = 1.0
426 -- History Current version number = 1.0
427 --=======================================================
428 procedure CREATE_FLOW (
429 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
430 p_api_version_number IN NUMBER,
431 p_init_msg_tbl IN BOOLEAN := FALSE,
432 p_msg_count OUT NOCOPY NUMBER,
433 p_msg_data OUT NOCOPY VARCHAR2,
434 p_return_status OUT NOCOPY VARCHAR2,
435 p_flow_application_id IN NUMBER,
436 p_flow_code IN VARCHAR2,
437 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
438 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
439 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
440 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
441 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
442 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
443 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
444 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
445 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
446 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
447 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
448 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
449 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
450 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
451 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
452 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
453 p_name IN VARCHAR2,
454 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
455 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
456 p_creation_date IN DATE := FND_API.G_MISS_DATE,
457 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
458 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
459 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
460 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
461 p_pass IN NUMBER,
462 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
463 ) is
464 l_api_version_number CONSTANT number := 1.0;
465 l_api_name CONSTANT varchar2(30) := 'Create_Flow';
466 l_created_by number;
467 l_creation_date date;
468 l_description VARCHAR2(2000) := null;
469 l_error boolean;
470 l_lang varchar2(30);
471 l_last_update_date date;
472 l_last_update_login number;
473 l_last_updated_by number;
474 l_primary_page_appl_id NUMBER := null;
475 l_primary_page_code VARCHAR2(30) := null;
476 l_attribute_category VARCHAR2(30);
477 l_attribute1 VARCHAR2(150);
478 l_attribute2 VARCHAR2(150);
479 l_attribute3 VARCHAR2(150);
480 l_attribute4 VARCHAR2(150);
481 l_attribute5 VARCHAR2(150);
482 l_attribute6 VARCHAR2(150);
483 l_attribute7 VARCHAR2(150);
484 l_attribute8 VARCHAR2(150);
485 l_attribute9 VARCHAR2(150);
486 l_attribute10 VARCHAR2(150);
487 l_attribute11 VARCHAR2(150);
488 l_attribute12 VARCHAR2(150);
489 l_attribute13 VARCHAR2(150);
490 l_attribute14 VARCHAR2(150);
491 l_attribute15 VARCHAR2(150);
492 l_return_status varchar2(1);
493 begin
494
495 IF NOT FND_API.Compatible_API_Call (
496 l_api_version_number, p_api_version_number, l_api_name,
497 G_PKG_NAME) then
498 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
499 return;
500 END IF;
501
502 -- Initialize the message table if requested.
503
504 if p_init_msg_tbl then
505 FND_MSG_PUB.initialize;
506 end if;
507
508 savepoint start_create_flow;
509
510 --
511 --** check to see if row already exists **
512 --
513 if AK_FLOW_PVT.FLOW_EXISTS (
514 p_api_version_number => 1.0,
515 p_return_status => l_return_status,
516 p_flow_application_id => p_flow_application_id,
517 p_flow_code => p_flow_code) then
518 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
519 FND_MESSAGE.SET_NAME('AK','AK_FLOW_EXISTS');
520 FND_MSG_PUB.Add;
521 end if;
522 raise FND_API.G_EXC_ERROR;
523 end if;
524
525 --
526 -- Since there would not be any flow pages for this flow at this
527 -- point, create the flow with a primary page code of
528 -- G_NO_PRIMARY_PAGE_CODE
529 --
530 l_primary_page_appl_id := p_flow_application_id;
531 l_primary_page_code := G_NO_PRIMARY_PAGE_CODE;
532
533 --** validate table columns passed in **
537 p_api_version_number => 1.0,
534 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
535 if not AK_FLOW3_PVT.VALIDATE_FLOW (
536 p_validation_level => p_validation_level,
538 p_return_status => l_return_status,
539 p_flow_application_id => p_flow_application_id,
540 p_flow_code => p_flow_code,
541 p_primary_page_appl_id => l_primary_page_appl_id,
542 p_primary_page_code => l_primary_page_code,
543 p_name => p_name,
544 p_description => p_description,
545 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
546 p_pass => p_pass
547 ) then
548 -- Do not raise an error if it's the first pass
549 if (p_pass = 1) then
550 p_copy_redo_flag := TRUE;
551 else
552 raise FND_API.G_EXC_ERROR;
553 end if;
554 end if;
555 end if;
556
557 --** Load non-required columns if their values are given **
558 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
559 l_attribute_category := p_attribute_category;
560 end if;
561
562 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
563 l_attribute1 := p_attribute1;
564 end if;
565
566 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
567 l_attribute2 := p_attribute2;
568 end if;
569
570 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
571 l_attribute3 := p_attribute3;
572 end if;
573
574 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
575 l_attribute4 := p_attribute4;
576 end if;
577
578 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
579 l_attribute5 := p_attribute5;
580 end if;
581
582 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
583 l_attribute6 := p_attribute6;
584 end if;
585
586 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
587 l_attribute7:= p_attribute7;
588 end if;
589
590 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
591 l_attribute8 := p_attribute8;
592 end if;
593
594 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
595 l_attribute9 := p_attribute9;
596 end if;
597
598 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
599 l_attribute10 := p_attribute10;
600 end if;
601
602 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
603 l_attribute11 := p_attribute11;
604 end if;
605
606 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
607 l_attribute12 := p_attribute12;
608 end if;
609
610 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
611 l_attribute13 := p_attribute13;
612 end if;
613
614 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
615 l_attribute14 := p_attribute14;
616 end if;
617
618 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
619 l_attribute15 := p_attribute15;
620 end if;
621
622 if (p_description <> FND_API.G_MISS_CHAR) then
623 l_description := p_description;
624 end if;
625
626 if (p_created_by <> FND_API.G_MISS_NUM) then
627 l_created_by := p_created_by;
628 end if;
629
630 if (p_creation_date <> FND_API.G_MISS_DATE) then
631 l_creation_date := p_creation_date;
632 end if;
633
634 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
635 l_last_updated_by := p_last_updated_by;
636 end if;
637
638 if (p_last_update_date <> FND_API.G_MISS_DATE) then
639 l_last_update_date := p_last_update_date;
640 end if;
641
642 if (p_last_update_login <> FND_API.G_MISS_NUM) then
643 l_last_update_login := p_last_update_login;
644 end if;
645
646 -- Create record if no validation error was found
647 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
648 -- old jlt files didn't have who columns and IS_UPDATEABLE
649 -- calls SET_WHO which populates those columns, for later
650 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
651
652 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
653 p_loader_timestamp => p_loader_timestamp,
654 p_created_by => l_created_by,
655 p_creation_date => l_creation_date,
656 p_last_updated_by => l_last_updated_by,
657 p_db_last_updated_by => null,
658 p_last_update_date => l_last_update_date,
659 p_db_last_update_date => null,
660 p_last_update_login => l_last_update_login,
661 p_create_or_update => 'CREATE') then
662 null;
663 end if;
664
665 select userenv('LANG') into l_lang
666 from dual;
667
668 insert into AK_FLOWS (
669 FLOW_APPLICATION_ID,
670 FLOW_CODE,
671 PRIMARY_PAGE_APPL_ID,
672 PRIMARY_PAGE_CODE,
673 ATTRIBUTE_CATEGORY,
674 ATTRIBUTE1,
675 ATTRIBUTE2,
676 ATTRIBUTE3,
677 ATTRIBUTE4,
678 ATTRIBUTE5,
679 ATTRIBUTE6,
680 ATTRIBUTE7,
681 ATTRIBUTE8,
682 ATTRIBUTE9,
683 ATTRIBUTE10,
684 ATTRIBUTE11,
685 ATTRIBUTE12,
686 ATTRIBUTE13,
687 ATTRIBUTE14,
688 ATTRIBUTE15,
689 CREATION_DATE,
690 CREATED_BY,
691 LAST_UPDATE_DATE,
692 LAST_UPDATED_BY,
693 LAST_UPDATE_LOGIN
694 ) values (
695 p_flow_application_id,
696 p_flow_code,
697 l_primary_page_appl_id,
698 l_primary_page_code,
699 l_attribute_category,
700 l_attribute1,
701 l_attribute2,
702 l_attribute3,
703 l_attribute4,
704 l_attribute5,
705 l_attribute6,
706 l_attribute7,
707 l_attribute8,
711 l_attribute12,
708 l_attribute9,
709 l_attribute10,
710 l_attribute11,
712 l_attribute13,
713 l_attribute14,
714 l_attribute15,
715 l_creation_date,
716 l_created_by,
717 l_last_update_date,
718 l_last_updated_by,
719 l_last_update_login
720 );
721
722 --** row should exists before inserting rows for other languages **
723 if NOT AK_FLOW_PVT.FLOW_EXISTS (
724 p_api_version_number => 1.0,
725 p_return_status => l_return_status,
726 p_flow_application_id => p_flow_application_id,
727 p_flow_code => p_flow_code) then
728 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
729 FND_MESSAGE.SET_NAME('AK','AK_INSERT_FLOW_FAILED');
730 FND_MSG_PUB.Add;
731 end if;
732 raise FND_API.G_EXC_ERROR;
733 end if;
734
735 insert into AK_FLOWS_TL (
736 FLOW_APPLICATION_ID,
737 FLOW_CODE,
738 LANGUAGE,
739 NAME,
740 DESCRIPTION,
741 SOURCE_LANG,
742 CREATED_BY,
743 CREATION_DATE,
744 LAST_UPDATED_BY,
745 LAST_UPDATE_DATE,
746 LAST_UPDATE_LOGIN
747 ) select
748 p_flow_application_id,
749 p_flow_code,
750 L.LANGUAGE_CODE,
751 p_name,
752 l_description,
753 decode(L.LANGUAGE_CODE, l_lang, L.LANGUAGE_CODE, l_lang),
754 l_created_by,
755 l_creation_date,
756 l_last_updated_by,
757 l_last_update_date,
758 l_last_update_login
759 from FND_LANGUAGES L
760 where L.INSTALLED_FLAG in ('I', 'B')
761 and not exists
762 (select NULL
763 from AK_FLOWS_TL T
764 where T.FLOW_APPLICATION_ID = p_flow_application_id
765 and T.FLOW_CODE = p_flow_code
766 and T.LANGUAGE = L.LANGUAGE_CODE);
767
768 -- /** commit the insert **/
769 -- commit;
770
771 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
772 FND_MESSAGE.SET_NAME('AK','AK_FLOW_CREATED');
773 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
774 ' ' || p_flow_code);
775 FND_MSG_PUB.Add;
776 end if;
777
778 p_return_status := FND_API.G_RET_STS_SUCCESS;
779
780 FND_MSG_PUB.Count_And_Get (
781 p_count => p_msg_count,
782 p_data => p_msg_data);
783
784
785 EXCEPTION
786 WHEN VALUE_ERROR THEN
787 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
788 FND_MESSAGE.SET_NAME('AK','AK_FLOW_VALUE_ERROR');
789 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
790 ' ' || p_flow_code);
791 FND_MSG_PUB.Add;
792 end if;
793 p_return_status := FND_API.G_RET_STS_ERROR;
794 rollback to start_create_flow;
795 FND_MSG_PUB.Count_And_Get (
796 p_count => p_msg_count,
797 p_data => p_msg_data);
798 WHEN FND_API.G_EXC_ERROR THEN
799 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
800 FND_MESSAGE.SET_NAME('AK','AK_FLOW_NOT_CREATED');
801 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
802 ' ' || p_flow_code);
803 FND_MSG_PUB.Add;
804 end if;
805 p_return_status := FND_API.G_RET_STS_ERROR;
806 rollback to start_create_flow;
807 FND_MSG_PUB.Count_And_Get (
808 p_count => p_msg_count,
809 p_data => p_msg_data);
810
811 WHEN OTHERS THEN
812 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
813 rollback to start_create_flow;
814 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
815 SUBSTR (SQLERRM, 1, 240) );
816 FND_MSG_PUB.Add;
817 FND_MSG_PUB.Count_And_Get (
818 p_count => p_msg_count,
819 p_data => p_msg_data);
820 end CREATE_FLOW;
821
822 --=======================================================
823 -- Procedure CREATE_PAGE
824 --
825 -- Usage Private API for creating a flow page. This
826 -- API should only be called by other APIs that are
827 -- owned by the Core Modules Team (AK).
828 --
829 -- Desc Creates a flow page using the given info. This
830 -- API should only be called by other APIs that are
831 -- owned by the Core Modules Team (AK).
832 --
833 -- Results The API returns the standard p_return_status parameter
834 -- indicating one of the standard return statuses :
835 -- * Unexpected error
836 -- * Error
837 -- * Success
838 -- Parameters Flow Page columns except primary_region_appl_id and
839 -- primary_region_code since there are no
840 -- flow page regions for this flow page at this time.
841 -- p_set_primary_page : IN optional
842 -- Set the current page as the primary page of
843 -- the flow if this flag is 'Y'.
844 -- p_loader_timestamp : IN optional
845 -- If a timestamp is passed, the API will create the
846 -- record using this timestamp. Only the upload API
847 -- should call with this parameter loaded.
848 --
849 -- Version Initial version number = 1.0
850 -- History Current version number = 1.0
851 --=======================================================
852 procedure CREATE_PAGE (
853 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
854 p_api_version_number IN NUMBER,
855 p_init_msg_tbl IN BOOLEAN := FALSE,
859 p_flow_application_id IN NUMBER,
856 p_msg_count OUT NOCOPY NUMBER,
857 p_msg_data OUT NOCOPY VARCHAR2,
858 p_return_status OUT NOCOPY VARCHAR2,
860 p_flow_code IN VARCHAR2,
861 p_page_application_id IN NUMBER,
862 p_page_code IN VARCHAR2,
863 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
864 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
865 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
866 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
867 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
868 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
869 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
870 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
871 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
872 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
873 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
874 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
875 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
876 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
877 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
878 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
879 p_name IN VARCHAR2,
880 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
881 p_set_primary_page IN VARCHAR2 := FND_API.G_MISS_CHAR,
882 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
883 p_creation_date IN DATE := FND_API.G_MISS_DATE,
884 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
885 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
886 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
887 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
888 p_pass IN NUMBER,
889 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
890 ) is
891 l_api_version_number CONSTANT number := 1.0;
892 l_api_name CONSTANT varchar2(30) := 'Create_Page';
893 l_created_by number;
894 l_creation_date date;
895 l_msg_count NUMBER;
896 l_msg_data VARCHAR2(2000);
897 l_description VARCHAR2(2000) := null;
898 l_error boolean;
899 l_lang varchar2(30);
900 l_last_update_date date;
901 l_last_update_login number;
902 l_last_updated_by number;
903 l_primary_region_appl_id NUMBER := null;
904 l_primary_region_code VARCHAR2(30) := null;
905 l_attribute_category VARCHAR2(30);
906 l_attribute1 VARCHAR2(150);
907 l_attribute2 VARCHAR2(150);
908 l_attribute3 VARCHAR2(150);
909 l_attribute4 VARCHAR2(150);
910 l_attribute5 VARCHAR2(150);
911 l_attribute6 VARCHAR2(150);
912 l_attribute7 VARCHAR2(150);
913 l_attribute8 VARCHAR2(150);
914 l_attribute9 VARCHAR2(150);
915 l_attribute10 VARCHAR2(150);
916 l_attribute11 VARCHAR2(150);
917 l_attribute12 VARCHAR2(150);
918 l_attribute13 VARCHAR2(150);
919 l_attribute14 VARCHAR2(150);
920 l_attribute15 VARCHAR2(150);
921 l_return_status varchar2(1);
922 begin
923
924 IF NOT FND_API.Compatible_API_Call (
925 l_api_version_number, p_api_version_number, l_api_name,
926 G_PKG_NAME) then
927 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
928 return;
929 END IF;
930
931 -- Initialize the message table if requested.
932
933 if p_init_msg_tbl then
934 FND_MSG_PUB.initialize;
935 end if;
936
937 savepoint start_create_page;
938
939 --
940 --** check to see if row already exists **
941 --
942 if AK_FLOW_PVT.PAGE_EXISTS (
943 p_api_version_number => 1.0,
944 p_return_status => l_return_status,
945 p_flow_application_id => p_flow_application_id,
946 p_flow_code => p_flow_code,
947 p_page_application_id => p_page_application_id,
948 p_page_code => p_page_code) then
949 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
950 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_EXISTS');
951 FND_MSG_PUB.Add;
952 end if;
953 raise FND_API.G_EXC_ERROR;
954 end if;
955
956 --
957 -- Since there would not be any flow page regions for this page region
958 -- at this time, create the flow page with its primary region set to
959 -- G_NO_PRIMARY_REGION_CODE
960 --
961 l_primary_region_appl_id := p_flow_application_id;
962 l_primary_region_code := G_NO_PRIMARY_REGION_CODE;
963
964 --** validate table columns passed in **
965 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
966 if not AK_FLOW3_PVT.VALIDATE_PAGE (
967 p_validation_level => p_validation_level,
968 p_api_version_number => 1.0,
969 p_return_status => l_return_status,
970 p_flow_application_id => p_flow_application_id,
971 p_flow_code => p_flow_code,
972 p_page_application_id => p_page_application_id,
973 p_page_code => p_page_code,
974 p_primary_region_appl_id => l_primary_region_appl_id,
975 p_primary_region_code => l_primary_region_code,
976 p_name => p_name,
977 p_description => p_description,
978 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
982 if (p_pass = 1) then
979 p_pass => p_pass
980 ) then
981 -- Do not raise an error if it's the first pass
983 p_copy_redo_flag := TRUE;
984 else
985 raise FND_API.G_EXC_ERROR;
986 end if;
987 end if;
988 end if;
989
990 --** Load non-required columns if their values are given **
991 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
992 l_attribute_category := p_attribute_category;
993 end if;
994
995 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
996 l_attribute1 := p_attribute1;
997 end if;
998
999 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
1000 l_attribute2 := p_attribute2;
1001 end if;
1002
1003 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
1004 l_attribute3 := p_attribute3;
1005 end if;
1006
1007 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
1008 l_attribute4 := p_attribute4;
1009 end if;
1010
1011 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
1012 l_attribute5 := p_attribute5;
1013 end if;
1014
1015 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
1016 l_attribute6 := p_attribute6;
1017 end if;
1018
1019 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
1020 l_attribute7:= p_attribute7;
1021 end if;
1022
1023 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
1024 l_attribute8 := p_attribute8;
1025 end if;
1026
1027 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
1028 l_attribute9 := p_attribute9;
1029 end if;
1030
1031 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
1032 l_attribute10 := p_attribute10;
1033 end if;
1034
1035 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
1036 l_attribute11 := p_attribute11;
1037 end if;
1038
1039 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
1040 l_attribute12 := p_attribute12;
1041 end if;
1042
1043 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
1044 l_attribute13 := p_attribute13;
1045 end if;
1046
1047 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
1048 l_attribute14 := p_attribute14;
1049 end if;
1050
1051 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
1052 l_attribute15 := p_attribute15;
1053 end if;
1054
1055 if (p_description <> FND_API.G_MISS_CHAR) then
1056 l_description := p_description;
1057 end if;
1058
1059 if (p_created_by <> FND_API.G_MISS_NUM) then
1060 l_created_by := p_created_by;
1061 end if;
1062
1063 if (p_creation_date <> FND_API.G_MISS_DATE) then
1064 l_creation_date := p_creation_date;
1065 end if;
1066
1067 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
1068 l_last_updated_by := p_last_updated_by;
1069 end if;
1070
1071 if (p_last_update_date <> FND_API.G_MISS_DATE) then
1072 l_last_update_date := p_last_update_date;
1073 end if;
1074
1075 if (p_last_update_login <> FND_API.G_MISS_NUM) then
1076 l_last_update_login := p_last_update_login;
1077 end if;
1078
1079 -- Create record if no validation error was found
1080 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
1081 -- old jlt files didn't have who columns and IS_UPDATEABLE
1082 -- calls SET_WHO which populates those columns, for later
1083 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
1084
1085 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1086 p_loader_timestamp => p_loader_timestamp,
1087 p_created_by => l_created_by,
1088 p_creation_date => l_creation_date,
1089 p_last_updated_by => l_last_updated_by,
1090 p_db_last_updated_by => null,
1091 p_last_update_date => l_last_update_date,
1092 p_db_last_update_date => null,
1093 p_last_update_login => l_last_update_login,
1094 p_create_or_update => 'CREATE') then
1095 null;
1096 end if;
1097
1098 select userenv('LANG') into l_lang
1099 from dual;
1100
1101 insert into AK_FLOW_PAGES (
1102 FLOW_APPLICATION_ID,
1103 FLOW_CODE,
1104 PAGE_APPLICATION_ID,
1105 PAGE_CODE,
1106 PRIMARY_REGION_APPL_ID,
1107 PRIMARY_REGION_CODE,
1108 ATTRIBUTE_CATEGORY,
1109 ATTRIBUTE1,
1110 ATTRIBUTE2,
1111 ATTRIBUTE3,
1112 ATTRIBUTE4,
1113 ATTRIBUTE5,
1114 ATTRIBUTE6,
1115 ATTRIBUTE7,
1116 ATTRIBUTE8,
1117 ATTRIBUTE9,
1118 ATTRIBUTE10,
1119 ATTRIBUTE11,
1120 ATTRIBUTE12,
1121 ATTRIBUTE13,
1122 ATTRIBUTE14,
1123 ATTRIBUTE15,
1124 CREATION_DATE,
1125 CREATED_BY,
1126 LAST_UPDATE_DATE,
1127 LAST_UPDATED_BY,
1128 LAST_UPDATE_LOGIN
1129 ) values (
1130 p_flow_application_id,
1131 p_flow_code,
1132 p_page_application_id,
1133 p_page_code,
1134 l_primary_region_appl_id,
1135 l_primary_region_code,
1136 l_attribute_category,
1137 l_attribute1,
1138 l_attribute2,
1139 l_attribute3,
1140 l_attribute4,
1141 l_attribute5,
1142 l_attribute6,
1143 l_attribute7,
1144 l_attribute8,
1145 l_attribute9,
1146 l_attribute10,
1147 l_attribute11,
1148 l_attribute12,
1149 l_attribute13,
1150 l_attribute14,
1151 l_attribute15,
1152 l_creation_date,
1153 l_created_by,
1154 l_last_update_date,
1155 l_last_updated_by,
1156 l_last_update_login
1157 );
1158
1162 p_return_status => l_return_status,
1159 --** row should exists before inserting rows for other languages **
1160 if NOT AK_FLOW_PVT.PAGE_EXISTS (
1161 p_api_version_number => 1.0,
1163 p_flow_application_id => p_flow_application_id,
1164 p_flow_code => p_flow_code,
1165 p_page_application_id => p_page_application_id,
1166 p_page_code => p_page_code) then
1167 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1168 FND_MESSAGE.SET_NAME('AK','AK_INSERT_FLOW_PAGE_FAILED');
1169 FND_MSG_PUB.Add;
1170 end if;
1171 raise FND_API.G_EXC_ERROR;
1172 end if;
1173
1174 insert into AK_FLOW_PAGES_TL (
1175 FLOW_APPLICATION_ID,
1176 FLOW_CODE,
1177 PAGE_APPLICATION_ID,
1178 PAGE_CODE,
1179 LANGUAGE,
1180 NAME,
1181 DESCRIPTION,
1182 SOURCE_LANG,
1183 CREATED_BY,
1184 CREATION_DATE,
1185 LAST_UPDATED_BY,
1186 LAST_UPDATE_DATE,
1187 LAST_UPDATE_LOGIN
1188 ) select
1189 p_flow_application_id,
1190 p_flow_code,
1191 p_page_application_id,
1192 p_page_code,
1193 L.LANGUAGE_CODE,
1194 p_name,
1195 l_description,
1196 decode(L.NLS_LANGUAGE, l_lang, L.NLS_LANGUAGE, l_lang),
1197 l_created_by,
1198 l_creation_date,
1199 l_last_updated_by,
1200 l_last_update_date,
1201 l_last_update_login
1202 from FND_LANGUAGES L
1203 where L.INSTALLED_FLAG in ('I', 'B')
1204 and not exists
1205 (select NULL
1206 from AK_FLOW_PAGES_TL T
1207 where T.FLOW_APPLICATION_ID = p_flow_application_id
1208 and T.FLOW_CODE = p_flow_code
1209 and T.PAGE_APPLICATION_ID = p_page_application_id
1210 and T.PAGE_CODE = p_page_code
1211 and T.LANGUAGE = L.LANGUAGE_CODE);
1212
1213 -- /** commit the insert **/
1214 -- commit;
1215
1216 --
1217 -- Set current page as the primary page of the flow if
1218 -- p_set_primary_page is 'Y'.
1219 --
1220 if (p_set_primary_page = 'Y') then
1221 AK_FLOW_PVT.UPDATE_FLOW (
1222 p_validation_level => p_validation_level,
1223 p_api_version_number => 1.0,
1224 p_msg_count => l_msg_count,
1225 p_msg_data => l_msg_data,
1226 p_return_status => l_return_status,
1227 p_flow_application_id => p_flow_application_id,
1228 p_flow_code => p_flow_code,
1229 p_primary_page_appl_id => p_page_application_id,
1230 p_primary_page_code => p_page_code,
1231 p_attribute_category => l_attribute_category,
1232 p_attribute1 => l_attribute1,
1233 p_attribute2 => l_attribute2,
1234 p_attribute3 => l_attribute3,
1235 p_attribute4 => l_attribute4,
1236 p_attribute5 => l_attribute5,
1237 p_attribute6 => l_attribute6,
1238 p_attribute7 => l_attribute7,
1239 p_attribute8 => l_attribute8,
1240 p_attribute9 => l_attribute9,
1241 p_attribute10 => l_attribute10,
1242 p_attribute11 => l_attribute11,
1243 p_attribute12 => l_attribute12,
1244 p_attribute13 => l_attribute13,
1245 p_attribute14 => l_attribute14,
1246 p_attribute15 => l_attribute15,
1247 p_created_by => l_created_by,
1248 p_creation_date => l_creation_date,
1249 p_last_updated_by => l_last_updated_by,
1250 p_last_update_date => l_last_update_date,
1251 p_last_update_login => l_last_update_login,
1252 p_pass => p_pass,
1253 p_copy_redo_flag => p_copy_redo_flag
1254 );
1255 if (l_return_status = FND_API.G_RET_STS_ERROR) or
1256 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1257 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1258 raise FND_API.G_EXC_ERROR;
1259 end if;
1260 end if;
1261 end if;
1262
1263 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1264 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_CREATED');
1265 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1266 ' ' || p_flow_code ||
1267 ' ' || to_char(p_page_application_id) ||
1268 ' ' || p_page_code);
1269 FND_MSG_PUB.Add;
1270 end if;
1271
1272 p_return_status := FND_API.G_RET_STS_SUCCESS;
1273
1274 FND_MSG_PUB.Count_And_Get (
1275 p_count => p_msg_count,
1276 p_data => p_msg_data);
1277
1278 EXCEPTION
1279 WHEN VALUE_ERROR THEN
1280 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1281 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_VALUE_ERROR');
1282 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1283 ' ' || p_flow_code ||
1284 ' ' || to_char(p_page_application_id) ||
1285 ' ' || p_page_code);
1286 FND_MSG_PUB.Add;
1287 end if;
1288 p_return_status := FND_API.G_RET_STS_ERROR;
1289 rollback to start_create_page;
1290 FND_MSG_PUB.Count_And_Get (
1291 p_count => p_msg_count,
1292 p_data => p_msg_data);
1293 WHEN FND_API.G_EXC_ERROR THEN
1294 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1295 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_NOT_CREATED');
1296 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1297 ' ' || p_flow_code ||
1298 ' ' || to_char(p_page_application_id) ||
1299 ' ' || p_page_code);
1300 FND_MSG_PUB.Add;
1301 end if;
1302 p_return_status := FND_API.G_RET_STS_ERROR;
1303 rollback to start_create_page;
1304 FND_MSG_PUB.Count_And_Get (
1305 p_count => p_msg_count,
1306 p_data => p_msg_data);
1307 WHEN OTHERS THEN
1308 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1309 rollback to start_create_page;
1310 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1311 SUBSTR (SQLERRM, 1, 240) );
1312 FND_MSG_PUB.Add;
1313 FND_MSG_PUB.Count_And_Get (
1314 p_count => p_msg_count,
1318 --=======================================================
1315 p_data => p_msg_data);
1316 end CREATE_PAGE;
1317
1319 -- Procedure CREATE_PAGE_REGION
1320 --
1321 -- Usage Private API for creating a flow page region. This
1322 -- API should only be called by other APIs that are
1323 -- owned by the Core Modules Team (AK).
1324 --
1325 -- Desc Creates a flow page region using the given info. This
1326 -- API should only be called by other APIs that are
1327 -- owned by the Core Modules Team (AK).
1328 --
1329 -- Results The API returns the standard p_return_status parameter
1330 -- indicating one of the standard return statuses :
1331 -- * Unexpected error
1332 -- * Error
1333 -- * Success
1334 -- Parameters Flow Page Region columns
1335 -- p_set_primary_region : IN optional
1336 -- Set the current page region as the primary region of
1337 -- the flow page if this flag is 'Y'.
1338 -- p_foreign_key_name : IN optional
1339 -- If a foreign key name is passed, and that this page
1340 -- region has a parent region, then this API will
1341 -- create an intrapage flow region relation connecting
1342 -- this page region with the parent region using the
1343 -- foreign key name. If there is already an intrapage
1344 -- flow region relation exists connecting these two
1345 -- page regions, it will be replaced by a new one using
1346 -- this foreign key.
1347 -- p_loader_timestamp : IN optional
1348 -- If a timestamp is passed, the API will create the
1349 -- record using this timestamp. Only the upload API
1350 -- should call with this parameter loaded.
1351 --
1352 -- Version Initial version number = 1.0
1353 -- History Current version number = 1.0
1354 --=======================================================
1355 procedure CREATE_PAGE_REGION (
1356 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1357 p_api_version_number IN NUMBER,
1358 p_init_msg_tbl IN BOOLEAN := FALSE,
1359 p_msg_count OUT NOCOPY NUMBER,
1360 p_msg_data OUT NOCOPY VARCHAR2,
1361 p_return_status OUT NOCOPY VARCHAR2,
1362 p_flow_application_id IN NUMBER,
1363 p_flow_code IN VARCHAR2,
1364 p_page_application_id IN NUMBER,
1365 p_page_code IN VARCHAR2,
1366 p_region_application_id IN NUMBER,
1367 p_region_code IN VARCHAR2,
1368 p_display_sequence IN NUMBER := FND_API.G_MISS_NUM,
1369 p_region_style IN VARCHAR2,
1370 p_num_columns IN NUMBER := FND_API.G_MISS_NUM,
1371 p_icx_custom_call IN VARCHAR2 := FND_API.G_MISS_CHAR,
1372 p_parent_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
1373 p_parent_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1374 p_foreign_key_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1375 p_set_primary_region IN VARCHAR2 := FND_API.G_MISS_CHAR,
1376 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
1377 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1378 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1379 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1380 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1381 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1382 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1383 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1384 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1385 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1386 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1387 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1388 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1389 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1390 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1391 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1392 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
1393 p_creation_date IN DATE := FND_API.G_MISS_DATE,
1394 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
1395 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
1396 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
1397 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
1398 p_pass IN NUMBER,
1399 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
1400 ) is
1401 l_api_version_number CONSTANT number := 1.0;
1402 l_api_name CONSTANT varchar2(30) := 'Create_Page_Region';
1403 l_created_by number;
1404 l_creation_date date;
1405 l_display_sequence NUMBER := null;
1406 l_icx_custom_call VARCHAR2(80) := null;
1407 l_msg_count NUMBER;
1408 l_msg_data VARCHAR2(2000);
1409 l_error boolean;
1410 l_lang varchar2(30);
1411 l_last_update_date date;
1412 l_last_update_login number;
1413 l_last_updated_by number;
1417 l_attribute_category VARCHAR2(30);
1414 l_num_columns NUMBER := null;
1415 l_parent_region_appl_id NUMBER := null;
1416 l_parent_region_code VARCHAR2(30) := null;
1418 l_attribute1 VARCHAR2(150);
1419 l_attribute2 VARCHAR2(150);
1420 l_attribute3 VARCHAR2(150);
1421 l_attribute4 VARCHAR2(150);
1422 l_attribute5 VARCHAR2(150);
1423 l_attribute6 VARCHAR2(150);
1424 l_attribute7 VARCHAR2(150);
1425 l_attribute8 VARCHAR2(150);
1426 l_attribute9 VARCHAR2(150);
1427 l_attribute10 VARCHAR2(150);
1428 l_attribute11 VARCHAR2(150);
1429 l_attribute12 VARCHAR2(150);
1430 l_attribute13 VARCHAR2(150);
1431 l_attribute14 VARCHAR2(150);
1432 l_attribute15 VARCHAR2(150);
1433 l_return_status varchar2(1);
1434 begin
1435
1436 IF NOT FND_API.Compatible_API_Call (
1437 l_api_version_number, p_api_version_number, l_api_name,
1438 G_PKG_NAME) then
1439 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1440 return;
1441 END IF;
1442
1443 -- Initialize the message table if requested.
1444
1445 if p_init_msg_tbl then
1446 FND_MSG_PUB.initialize;
1447 end if;
1448
1449 savepoint start_create_page_region;
1450
1451 --
1452 --** check to see if row already exists **
1453 --
1454 if AK_FLOW_PVT.PAGE_REGION_EXISTS (
1455 p_api_version_number => 1.0,
1456 p_return_status => l_return_status,
1457 p_flow_application_id => p_flow_application_id,
1458 p_flow_code => p_flow_code,
1459 p_page_application_id => p_page_application_id,
1460 p_page_code => p_page_code,
1461 p_region_application_id => p_region_application_id,
1462 p_region_code => p_region_code) then
1463 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1464 FND_MESSAGE.SET_NAME('AK','AK_PAGE_REGION_EXISTS');
1465 FND_MSG_PUB.Add;
1466 end if;
1467 raise FND_API.G_EXC_ERROR;
1468 end if;
1469
1470 if (p_display_sequence IS NOT NULL) and
1471 (p_display_sequence <> FND_API.G_MISS_NUM) then
1472 --** Check the given display sequence number
1473 CHECK_DISPLAY_SEQUENCE ( p_validation_level => p_validation_level,
1474 p_flow_code => p_flow_code,
1475 p_flow_application_id => p_flow_application_id,
1476 p_page_code => p_page_code,
1477 p_page_application_id => p_page_application_id,
1478 p_region_code => p_region_code,
1479 p_region_application_id => p_region_application_id,
1480 p_display_sequence => p_display_sequence,
1481 p_return_status => l_return_status,
1482 p_msg_count => p_msg_count,
1483 p_msg_data => p_msg_data,
1484 p_pass => p_pass,
1485 p_copy_redo_flag => p_copy_redo_flag);
1486 end if;
1487
1488 --** validate table columns passed in **
1489 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
1490 if not AK_FLOW3_PVT.VALIDATE_PAGE_REGION (
1491 p_validation_level => p_validation_level,
1492 p_api_version_number => 1.0,
1493 p_return_status => l_return_status,
1494 p_flow_application_id => p_flow_application_id,
1495 p_flow_code => p_flow_code,
1496 p_page_application_id => p_page_application_id,
1497 p_page_code => p_page_code,
1498 p_region_application_id => p_region_application_id,
1499 p_region_code => p_region_code,
1500 p_display_sequence => p_display_sequence,
1501 p_region_style => p_region_style,
1502 p_num_columns => p_num_columns,
1503 p_icx_custom_call => p_icx_custom_call,
1504 p_parent_region_application_id => p_parent_region_application_id,
1505 p_parent_region_code => p_parent_region_code,
1506 p_foreign_key_name => p_foreign_key_name,
1507 p_set_primary_region => p_set_primary_region,
1508 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
1509 p_pass => p_pass
1510 ) then
1511 -- Do not raise an error if it's the first pass
1512 if (p_pass = 1) then
1513 p_copy_redo_flag := TRUE;
1514 else
1515 raise FND_API.G_EXC_ERROR;
1516 end if;
1517 end if;
1518 end if;
1519
1520 --** Load non-required columns if their values are given **
1521 if (p_display_sequence <> FND_API.G_MISS_NUM) then
1522 l_display_sequence := p_display_sequence;
1523 end if;
1524
1525 if (p_num_columns <> FND_API.G_MISS_NUM) then
1526 l_num_columns := p_num_columns;
1527 end if;
1528
1529 if (p_icx_custom_call <> FND_API.G_MISS_CHAR) then
1530 l_icx_custom_call := p_icx_custom_call;
1531 end if;
1532
1533 if (p_parent_region_application_id <> FND_API.G_MISS_NUM) then
1534 l_parent_region_appl_id := p_parent_region_application_id;
1535 end if;
1536
1537 if (p_parent_region_code <> FND_API.G_MISS_CHAR) then
1538 l_parent_region_code := p_parent_region_code;
1539 end if;
1540
1541 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
1542 l_attribute_category := p_attribute_category;
1543 end if;
1544
1545 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
1546 l_attribute1 := p_attribute1;
1547 end if;
1548
1549 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
1550 l_attribute2 := p_attribute2;
1551 end if;
1552
1553 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
1554 l_attribute3 := p_attribute3;
1555 end if;
1556
1557 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
1558 l_attribute4 := p_attribute4;
1559 end if;
1560
1561 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
1562 l_attribute5 := p_attribute5;
1566 l_attribute6 := p_attribute6;
1563 end if;
1564
1565 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
1567 end if;
1568
1569 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
1570 l_attribute7:= p_attribute7;
1571 end if;
1572
1573 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
1574 l_attribute8 := p_attribute8;
1575 end if;
1576
1577 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
1578 l_attribute9 := p_attribute9;
1579 end if;
1580
1581 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
1582 l_attribute10 := p_attribute10;
1583 end if;
1584
1585 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
1586 l_attribute11 := p_attribute11;
1587 end if;
1588
1589 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
1590 l_attribute12 := p_attribute12;
1591 end if;
1592
1593 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
1594 l_attribute13 := p_attribute13;
1595 end if;
1596
1597 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
1598 l_attribute14 := p_attribute14;
1599 end if;
1600
1601 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
1602 l_attribute15 := p_attribute15;
1603 end if;
1604
1605 if (p_created_by <> FND_API.G_MISS_NUM) then
1606 l_created_by := p_created_by;
1607 end if;
1608
1609 if (p_creation_date <> FND_API.G_MISS_DATE) then
1610 l_creation_date := p_creation_date;
1611 end if;
1612
1613 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
1614 l_last_updated_by := p_last_updated_by;
1615 end if;
1616
1617 if (p_last_update_date <> FND_API.G_MISS_DATE) then
1618 l_last_update_date := p_last_update_date;
1619 end if;
1620
1621 if (p_last_update_login <> FND_API.G_MISS_NUM) then
1622 l_last_update_login := p_last_update_login;
1623 end if;
1624
1625 -- Create record if no validation error was found
1626 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
1627 -- old jlt files didn't have who columns and IS_UPDATEABLE
1628 -- calls SET_WHO which populates those columns, for later
1629 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
1630
1631 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
1632 p_loader_timestamp => p_loader_timestamp,
1633 p_created_by => l_created_by,
1634 p_creation_date => l_creation_date,
1635 p_last_updated_by => l_last_updated_by,
1636 p_db_last_updated_by => null,
1637 p_last_update_date => l_last_update_date,
1638 p_db_last_update_date => null,
1639 p_last_update_login => l_last_update_login,
1640 p_create_or_update => 'CREATE') then
1641 null;
1642 end if;
1643
1644 select userenv('LANG') into l_lang
1645 from dual;
1646
1647 insert into AK_FLOW_PAGE_REGIONS (
1648 FLOW_APPLICATION_ID,
1649 FLOW_CODE,
1650 PAGE_APPLICATION_ID,
1651 PAGE_CODE,
1652 REGION_APPLICATION_ID,
1653 REGION_CODE,
1654 DISPLAY_SEQUENCE,
1655 REGION_STYLE,
1656 NUM_COLUMNS,
1657 ICX_CUSTOM_CALL,
1658 PARENT_REGION_APPLICATION_ID,
1659 PARENT_REGION_CODE,
1660 ATTRIBUTE_CATEGORY,
1661 ATTRIBUTE1,
1662 ATTRIBUTE2,
1663 ATTRIBUTE3,
1664 ATTRIBUTE4,
1665 ATTRIBUTE5,
1666 ATTRIBUTE6,
1667 ATTRIBUTE7,
1668 ATTRIBUTE8,
1669 ATTRIBUTE9,
1670 ATTRIBUTE10,
1671 ATTRIBUTE11,
1672 ATTRIBUTE12,
1673 ATTRIBUTE13,
1674 ATTRIBUTE14,
1675 ATTRIBUTE15,
1676 CREATION_DATE,
1677 CREATED_BY,
1678 LAST_UPDATE_DATE,
1679 LAST_UPDATED_BY,
1680 LAST_UPDATE_LOGIN
1681 ) values (
1682 p_flow_application_id,
1683 p_flow_code,
1684 p_page_application_id,
1685 p_page_code,
1686 p_region_application_id,
1687 p_region_code,
1688 l_display_sequence,
1689 p_region_style,
1690 l_num_columns,
1691 l_icx_custom_call,
1692 l_parent_region_appl_id,
1693 l_parent_region_code,
1694 l_attribute_category,
1695 l_attribute1,
1696 l_attribute2,
1697 l_attribute3,
1698 l_attribute4,
1699 l_attribute5,
1700 l_attribute6,
1701 l_attribute7,
1702 l_attribute8,
1703 l_attribute9,
1704 l_attribute10,
1705 l_attribute11,
1706 l_attribute12,
1707 l_attribute13,
1708 l_attribute14,
1709 l_attribute15,
1710 l_creation_date,
1711 l_created_by,
1712 l_last_update_date,
1713 l_last_updated_by,
1714 l_last_update_login
1715 );
1716
1717 -- /** commit the insert **/
1718 -- commit;
1719
1720 --
1721 -- Set current region as the primary region of the flow page if
1722 -- p_set_primary_region is 'Y'.
1723 --
1724 if (p_set_primary_region = 'Y') then
1725 AK_FLOW_PVT.UPDATE_PAGE (
1726 p_validation_level => p_validation_level,
1727 p_api_version_number => 1.0,
1728 p_msg_count => l_msg_count,
1729 p_msg_data => l_msg_data,
1730 p_return_status => l_return_status,
1731 p_flow_application_id => p_flow_application_id,
1732 p_flow_code => p_flow_code,
1733 p_page_application_id => p_page_application_id,
1734 p_page_code => p_page_code,
1735 p_primary_region_appl_id => p_region_application_id,
1736 p_primary_region_code => p_region_code,
1737 p_attribute1 => l_attribute1,
1738 p_attribute2 => l_attribute2,
1739 p_attribute3 => l_attribute3,
1740 p_attribute4 => l_attribute4,
1741 p_attribute5 => l_attribute5,
1742 p_attribute6 => l_attribute6,
1743 p_attribute7 => l_attribute7,
1747 p_attribute11 => l_attribute11,
1744 p_attribute8 => l_attribute8,
1745 p_attribute9 => l_attribute9,
1746 p_attribute10 => l_attribute10,
1748 p_attribute12 => l_attribute12,
1749 p_attribute13 => l_attribute13,
1750 p_attribute14 => l_attribute14,
1751 p_attribute15 => l_attribute15,
1752 p_created_by => l_created_by,
1753 p_creation_date => l_creation_date,
1754 p_last_updated_by => l_last_updated_by,
1755 p_last_update_date => l_last_update_date,
1756 p_last_update_login => l_last_update_login,
1757 p_pass => p_pass,
1758 p_copy_redo_flag => p_copy_redo_flag
1759 );
1760 if (l_return_status = FND_API.G_RET_STS_ERROR) or
1761 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1762 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1763 raise FND_API.G_EXC_ERROR;
1764 end if;
1765 end if;
1766 end if;
1767
1768 --
1769 -- if a foreign key name is specified, and there is a parent region for
1770 -- this new page region, create an intrapage relation record.
1771 --
1772 if (p_foreign_key_name <> FND_API.G_MISS_CHAR) and
1773 (p_foreign_key_name is not null) and
1774 (p_parent_region_application_id <> FND_API.G_MISS_NUM) and
1775 (p_parent_region_application_id is not null) and
1776 (p_parent_region_code <> FND_API.G_MISS_CHAR) and
1777 (p_parent_region_code is not null) then
1778 AK_FLOW_PVT.CREATE_REGION_RELATION (
1779 p_validation_level => p_validation_level,
1780 p_api_version_number => 1.0,
1781 p_msg_count => p_msg_count,
1782 p_msg_data => p_msg_data,
1783 p_return_status => l_return_status,
1784 p_flow_application_id => p_flow_application_id,
1785 p_flow_code => p_flow_code,
1786 p_foreign_key_name => p_foreign_key_name,
1787 p_from_page_appl_id => p_page_application_id,
1788 p_from_page_code => p_page_code,
1789 p_from_region_appl_id => p_parent_region_application_id,
1790 p_from_region_code => p_parent_region_code,
1791 p_to_page_appl_id => p_page_application_id,
1792 p_to_page_code => p_page_code,
1793 p_to_region_appl_id => p_region_application_id,
1794 p_to_region_code => p_region_code,
1795 p_application_id => p_flow_application_id,
1796 p_created_by => l_created_by,
1797 p_creation_date => l_creation_date,
1798 p_last_updated_by => l_last_updated_by,
1799 p_last_update_date => l_last_update_date,
1800 p_last_update_login => l_last_update_login,
1801 p_pass => p_pass,
1802 p_copy_redo_flag => p_copy_redo_flag
1803 );
1804 if (l_return_status = FND_API.G_RET_STS_ERROR) or
1805 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1806 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1807 raise FND_API.G_EXC_ERROR;
1808 end if;
1809 end if;
1810 end if;
1811
1812 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1813 FND_MESSAGE.SET_NAME('AK','AK_OBJECT_CREATED');
1814 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1815 ' ' || p_flow_code ||
1816 ' ' || to_char(p_page_application_id) ||
1817 ' ' || p_page_code ||
1818 ' ' || to_char(p_region_application_id) ||
1819 ' ' || p_region_code);
1820 FND_MSG_PUB.Add;
1821 end if;
1822
1823 p_return_status := FND_API.G_RET_STS_SUCCESS;
1824
1825 FND_MSG_PUB.Count_And_Get (
1826 p_count => p_msg_count,
1827 p_data => p_msg_data);
1828
1829 EXCEPTION
1830 WHEN VALUE_ERROR THEN
1831 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1832 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_VALUE_ERROR');
1833 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1834 ' ' || p_flow_code ||
1835 ' ' || to_char(p_page_application_id) ||
1836 ' ' || p_page_code ||
1837 ' ' || to_char(p_region_application_id) ||
1838 ' ' || p_region_code);
1839 FND_MSG_PUB.Add;
1840 end if;
1841 p_return_status := FND_API.G_RET_STS_ERROR;
1842 rollback to start_create_page_region;
1843 FND_MSG_PUB.Count_And_Get (
1844 p_count => p_msg_count,
1845 p_data => p_msg_data);
1846 WHEN FND_API.G_EXC_ERROR THEN
1847 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1848 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_NOT_CREATED');
1849 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
1850 ' ' || p_flow_code ||
1851 ' ' || to_char(p_page_application_id) ||
1852 ' ' || p_page_code ||
1853 ' ' || to_char(p_region_application_id) ||
1854 ' ' || p_region_code);
1855 FND_MSG_PUB.Add;
1856 end if;
1857 p_return_status := FND_API.G_RET_STS_ERROR;
1858 rollback to start_create_page_region;
1859 FND_MSG_PUB.Count_And_Get (
1860 p_count => p_msg_count,
1861 p_data => p_msg_data);
1862 WHEN OTHERS THEN
1863 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1864 rollback to start_create_page_region;
1865 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1866 SUBSTR (SQLERRM, 1, 240) );
1867 FND_MSG_PUB.Add;
1868 FND_MSG_PUB.Count_And_Get (
1869 p_count => p_msg_count,
1870 p_data => p_msg_data);
1871 end CREATE_PAGE_REGION;
1872
1873 --=======================================================
1874 -- Procedure CREATE_PAGE_REGION_ITEM
1875 --
1876 -- Usage Private API for creating a flow page region item. This
1877 -- API should only be called by other APIs that are
1878 -- owned by the Core Modules Team (AK).
1879 --
1880 -- Desc Creates a flow page region item using the given info.
1881 -- This API should only be called by other APIs that are
1885 -- indicating one of the standard return statuses :
1882 -- owned by the Core Modules Team (AK).
1883 --
1884 -- Results The API returns the standard p_return_status parameter
1886 -- * Unexpected error
1887 -- * Error
1888 -- * Success
1889 -- Parameters Flow Page Region Item columns
1890 -- p_loader_timestamp : IN optional
1891 -- If a timestamp is passed, the API will create the
1892 -- record using this timestamp. Only the upload API
1893 -- should call with this parameter loaded.
1894 --
1895 -- Version Initial version number = 1.0
1896 -- History Current version number = 1.0
1897 --=======================================================
1898 procedure CREATE_PAGE_REGION_ITEM (
1899 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1900 p_api_version_number IN NUMBER,
1901 p_init_msg_tbl IN BOOLEAN := FALSE,
1902 p_msg_count OUT NOCOPY NUMBER,
1903 p_msg_data OUT NOCOPY VARCHAR2,
1904 p_return_status OUT NOCOPY VARCHAR2,
1905 p_flow_application_id IN NUMBER,
1906 p_flow_code IN VARCHAR2,
1907 p_page_application_id IN NUMBER,
1908 p_page_code IN VARCHAR2,
1909 p_region_application_id IN NUMBER,
1910 p_region_code IN VARCHAR2,
1911 p_attribute_application_id IN NUMBER,
1912 p_attribute_code IN VARCHAR2,
1913 p_to_page_appl_id IN NUMBER := FND_API.G_MISS_NUM,
1914 p_to_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1915 p_to_url_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
1916 p_to_url_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1917 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
1918 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1919 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1920 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1921 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1922 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1923 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1924 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1925 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1926 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1927 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1928 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1929 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1930 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1931 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1932 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1933 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
1934 p_creation_date IN DATE := FND_API.G_MISS_DATE,
1935 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
1936 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
1937 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
1938 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
1939 p_pass IN NUMBER,
1940 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
1941 ) is
1942 l_api_version_number CONSTANT number := 1.0;
1943 l_api_name CONSTANT varchar2(30) := 'Create_Page_Region_Item';
1944 l_created_by number;
1945 l_creation_date date;
1946 l_msg_count NUMBER;
1947 l_msg_data VARCHAR2(2000);
1948 l_error boolean;
1949 l_lang varchar2(30);
1950 l_last_update_date date;
1951 l_last_update_login number;
1952 l_last_updated_by number;
1953 l_return_status varchar2(1);
1954 l_to_page_appl_id NUMBER :=null;
1955 l_to_page_code VARCHAR2(30) := null;
1956 l_to_url_attribute_appl_id NUMBER :=null;
1957 l_to_url_attribute_code VARCHAR2(30) := null;
1958 l_attribute_category VARCHAR2(30);
1959 l_attribute1 VARCHAR2(150);
1960 l_attribute2 VARCHAR2(150);
1961 l_attribute3 VARCHAR2(150);
1962 l_attribute4 VARCHAR2(150);
1963 l_attribute5 VARCHAR2(150);
1964 l_attribute6 VARCHAR2(150);
1965 l_attribute7 VARCHAR2(150);
1966 l_attribute8 VARCHAR2(150);
1967 l_attribute9 VARCHAR2(150);
1968 l_attribute10 VARCHAR2(150);
1969 l_attribute11 VARCHAR2(150);
1970 l_attribute12 VARCHAR2(150);
1971 l_attribute13 VARCHAR2(150);
1972 l_attribute14 VARCHAR2(150);
1973 l_attribute15 VARCHAR2(150);
1974 begin
1975
1976 IF NOT FND_API.Compatible_API_Call (
1977 l_api_version_number, p_api_version_number, l_api_name,
1978 G_PKG_NAME) then
1979 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1980 return;
1981 END IF;
1982
1983 -- Initialize the message table if requested.
1984
1985 if p_init_msg_tbl then
1986 FND_MSG_PUB.initialize;
1987 end if;
1988
1989 savepoint start_create_link;
1990
1991 --
1992 --** check to see if row already exists **
1993 --
1994 if AK_FLOW_PVT.PAGE_REGION_ITEM_EXISTS (
1995 p_api_version_number => 1.0,
1996 p_return_status => l_return_status,
2000 p_page_code => p_page_code,
1997 p_flow_application_id => p_flow_application_id,
1998 p_flow_code => p_flow_code,
1999 p_page_application_id => p_page_application_id,
2001 p_region_application_id => p_region_application_id,
2002 p_region_code => p_region_code,
2003 p_attribute_application_id => p_attribute_application_id,
2004 p_attribute_code => p_attribute_code) then
2005 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2006 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_EXISTS');
2007 FND_MSG_PUB.Add;
2008 end if;
2009 raise FND_API.G_EXC_ERROR;
2010 end if;
2011
2012 --** validate table columns passed in **
2013 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
2014 if not AK_FLOW3_PVT.VALIDATE_PAGE_REGION_ITEM (
2015 p_validation_level => p_validation_level,
2016 p_api_version_number => 1.0,
2017 p_return_status => l_return_status,
2018 p_flow_application_id => p_flow_application_id,
2019 p_flow_code => p_flow_code,
2020 p_page_application_id => p_page_application_id,
2021 p_page_code => p_page_code,
2022 p_region_application_id => p_region_application_id,
2023 p_region_code => p_region_code,
2024 p_attribute_application_id => p_attribute_application_id,
2025 p_attribute_code => p_attribute_code,
2026 p_to_page_appl_id => p_to_page_appl_id,
2027 p_to_page_code => p_to_page_code,
2028 p_to_url_attribute_appl_id => p_to_url_attribute_appl_id,
2029 p_to_url_attribute_code => p_to_url_attribute_code,
2030 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
2031 p_pass => p_pass
2032 ) then
2033 -- Do not raise an error if it's the first pass
2034 if (p_pass = 1) then
2035 p_copy_redo_flag := TRUE;
2036 else
2037 raise FND_API.G_EXC_ERROR;
2038 end if;
2039 end if;
2040 end if;
2041
2042 --** Load non-required columns if their values are given **
2043 if (p_to_page_appl_id <> FND_API.G_MISS_NUM) then
2044 l_to_page_appl_id := p_to_page_appl_id;
2045 end if;
2046
2047 if (p_to_page_code <> FND_API.G_MISS_CHAR) then
2048 l_to_page_code := p_to_page_code;
2049 end if;
2050
2051 if (p_to_url_attribute_appl_id <> FND_API.G_MISS_NUM) then
2052 l_to_url_attribute_appl_id := p_to_url_attribute_appl_id;
2053 end if;
2054
2055 if (p_to_url_attribute_code <> FND_API.G_MISS_CHAR) then
2056 l_to_url_attribute_code := p_to_url_attribute_code;
2057 end if;
2058
2059 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
2060 l_attribute_category := p_attribute_category;
2061 end if;
2062
2063 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
2064 l_attribute1 := p_attribute1;
2065 end if;
2066
2067 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
2068 l_attribute2 := p_attribute2;
2069 end if;
2070
2071 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
2072 l_attribute3 := p_attribute3;
2073 end if;
2074
2075 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
2076 l_attribute4 := p_attribute4;
2077 end if;
2078
2079 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
2080 l_attribute5 := p_attribute5;
2081 end if;
2082
2083 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
2084 l_attribute6 := p_attribute6;
2085 end if;
2086
2087 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
2088 l_attribute7:= p_attribute7;
2089 end if;
2090
2091 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
2092 l_attribute8 := p_attribute8;
2093 end if;
2094
2095 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
2096 l_attribute9 := p_attribute9;
2097 end if;
2098
2099 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
2100 l_attribute10 := p_attribute10;
2101 end if;
2102
2103 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
2104 l_attribute11 := p_attribute11;
2105 end if;
2106
2107 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
2108 l_attribute12 := p_attribute12;
2109 end if;
2110
2111 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
2112 l_attribute13 := p_attribute13;
2113 end if;
2114
2115 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
2116 l_attribute14 := p_attribute14;
2117 end if;
2118
2119 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
2120 l_attribute15 := p_attribute15;
2121 end if;
2122
2123 if (p_created_by <> FND_API.G_MISS_NUM) then
2124 l_created_by := p_created_by;
2125 end if;
2126
2127 if (p_creation_date <> FND_API.G_MISS_DATE) then
2128 l_creation_date := p_creation_date;
2129 end if;
2130
2131 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
2132 l_last_updated_by := p_last_updated_by;
2133 end if;
2134
2135 if (p_last_update_date <> FND_API.G_MISS_DATE) then
2136 l_last_update_date := p_last_update_date;
2137 end if;
2138
2139 if (p_last_update_login <> FND_API.G_MISS_NUM) then
2140 l_last_update_login := p_last_update_login;
2141 end if;
2142
2143 -- Create record if no validation error was found
2144 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
2145 -- old jlt files didn't have who columns and IS_UPDATEABLE
2146 -- calls SET_WHO which populates those columns, for later
2147 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
2148
2149 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
2150 p_loader_timestamp => p_loader_timestamp,
2151 p_created_by => l_created_by,
2152 p_creation_date => l_creation_date,
2153 p_last_updated_by => l_last_updated_by,
2157 p_last_update_login => l_last_update_login,
2154 p_db_last_updated_by => null,
2155 p_last_update_date => l_last_update_date,
2156 p_db_last_update_date => null,
2158 p_create_or_update => 'CREATE') then
2159 null;
2160 end if;
2161
2162 select userenv('LANG') into l_lang
2163 from dual;
2164
2165 insert into AK_FLOW_PAGE_REGION_ITEMS (
2166 FLOW_APPLICATION_ID,
2167 FLOW_CODE,
2168 PAGE_APPLICATION_ID,
2169 PAGE_CODE,
2170 REGION_APPLICATION_ID,
2171 REGION_CODE,
2172 ATTRIBUTE_APPLICATION_ID,
2173 ATTRIBUTE_CODE,
2174 TO_PAGE_APPL_ID,
2175 TO_PAGE_CODE,
2176 TO_URL_ATTRIBUTE_APPL_ID,
2177 TO_URL_ATTRIBUTE_CODE,
2178 ATTRIBUTE_CATEGORY,
2179 ATTRIBUTE1,
2180 ATTRIBUTE2,
2181 ATTRIBUTE3,
2182 ATTRIBUTE4,
2183 ATTRIBUTE5,
2184 ATTRIBUTE6,
2185 ATTRIBUTE7,
2186 ATTRIBUTE8,
2187 ATTRIBUTE9,
2188 ATTRIBUTE10,
2189 ATTRIBUTE11,
2190 ATTRIBUTE12,
2191 ATTRIBUTE13,
2192 ATTRIBUTE14,
2193 ATTRIBUTE15,
2194 CREATION_DATE,
2195 CREATED_BY,
2196 LAST_UPDATE_DATE,
2197 LAST_UPDATED_BY,
2198 LAST_UPDATE_LOGIN
2199 ) values (
2200 p_flow_application_id,
2201 p_flow_code,
2202 p_page_application_id,
2203 p_page_code,
2204 p_region_application_id,
2205 p_region_code,
2206 p_attribute_application_id,
2207 p_attribute_code,
2208 l_to_page_appl_id,
2209 l_to_page_code,
2210 l_to_url_attribute_appl_id,
2211 l_to_url_attribute_code,
2212 l_attribute_category,
2213 l_attribute1,
2214 l_attribute2,
2215 l_attribute3,
2216 l_attribute4,
2217 l_attribute5,
2218 l_attribute6,
2219 l_attribute7,
2220 l_attribute8,
2221 l_attribute9,
2222 l_attribute10,
2223 l_attribute11,
2224 l_attribute12,
2225 l_attribute13,
2226 l_attribute14,
2227 l_attribute15,
2228 l_creation_date,
2229 l_created_by,
2230 l_last_update_date,
2231 l_last_updated_by,
2232 l_last_update_login
2233 );
2234
2235 -- /** commit the insert **/
2236 -- commit;
2237
2238 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
2239 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_CREATED');
2240 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
2241 ' ' || p_flow_code ||
2242 ' ' || to_char(p_page_application_id) ||
2243 ' ' || p_page_code ||
2244 ' ' || to_char(p_region_application_id) ||
2245 ' ' || p_region_code ||
2246 ' ' || to_char(p_attribute_application_id)||
2247 ' ' || p_attribute_code);
2248 FND_MSG_PUB.Add;
2249 end if;
2250
2251 p_return_status := FND_API.G_RET_STS_SUCCESS;
2252
2253 FND_MSG_PUB.Count_And_Get (
2254 p_count => p_msg_count,
2255 p_data => p_msg_data);
2256
2257 EXCEPTION
2258 WHEN VALUE_ERROR THEN
2259 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2260 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_VALUE_ERROR');
2261 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
2262 ' ' || p_flow_code ||
2263 ' ' || to_char(p_page_application_id) ||
2264 ' ' || p_page_code ||
2265 ' ' || to_char(p_region_application_id) ||
2266 ' ' || p_region_code ||
2267 ' ' || to_char(p_attribute_application_id)||
2268 ' ' || p_attribute_code);
2269 FND_MSG_PUB.Add;
2270 end if;
2271 p_return_status := FND_API.G_RET_STS_ERROR;
2272 rollback to start_create_link;
2273 FND_MSG_PUB.Count_And_Get (
2274 p_count => p_msg_count,
2275 p_data => p_msg_data);
2276 WHEN FND_API.G_EXC_ERROR THEN
2277 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2278 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_NOT_CREATED');
2279 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
2280 ' ' || p_flow_code ||
2281 ' ' || to_char(p_page_application_id) ||
2282 ' ' || p_page_code ||
2283 ' ' || to_char(p_region_application_id) ||
2284 ' ' || p_region_code ||
2285 ' ' || to_char(p_attribute_application_id)||
2286 ' ' || p_attribute_code);
2287 FND_MSG_PUB.Add;
2288 end if;
2289 p_return_status := FND_API.G_RET_STS_ERROR;
2290 rollback to start_create_link;
2291 FND_MSG_PUB.Count_And_Get (
2292 p_count => p_msg_count,
2293 p_data => p_msg_data);
2294 WHEN OTHERS THEN
2295 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2296 rollback to start_create_link;
2297 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2298 SUBSTR (SQLERRM, 1, 240) );
2299 FND_MSG_PUB.Add;
2300 FND_MSG_PUB.Count_And_Get (
2301 p_count => p_msg_count,
2302 p_data => p_msg_data);
2303 end CREATE_PAGE_REGION_ITEM;
2304
2305 --=======================================================
2306 -- Procedure CREATE_REGION_RELATIONS
2307 --
2308 -- Usage Private API for creating a flow region relation. This
2309 -- API should only be called by other APIs that are
2310 -- owned by the Core Modules Team (AK).
2311 --
2312 -- Desc Creates a flow region relation using the given info.
2313 -- This API should only be called by other APIs that are
2314 -- owned by the Core Modules Team (AK).
2315 --
2316 -- Results The API returns the standard p_return_status parameter
2317 -- indicating one of the standard return statuses :
2318 -- * Unexpected error
2319 -- * Error
2323 -- If a timestamp is passed, the API will create the
2320 -- * Success
2321 -- Parameters Flow Region Relation columns
2322 -- p_loader_timestamp : IN optional
2324 -- record using this timestamp. Only the upload API
2325 -- should call with this parameter loaded.
2326 --
2327 -- Version Initial version number = 1.0
2328 -- History Current version number = 1.0
2329 --=======================================================
2330 procedure CREATE_REGION_RELATION (
2331 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2332 p_api_version_number IN NUMBER,
2333 p_init_msg_tbl IN BOOLEAN := FALSE,
2334 p_msg_count OUT NOCOPY NUMBER,
2335 p_msg_data OUT NOCOPY VARCHAR2,
2336 p_return_status OUT NOCOPY VARCHAR2,
2337 p_flow_application_id IN NUMBER,
2338 p_flow_code IN VARCHAR2,
2339 p_foreign_key_name IN VARCHAR2,
2340 p_from_page_appl_id IN NUMBER,
2341 p_from_page_code IN VARCHAR2,
2342 p_from_region_appl_id IN NUMBER,
2343 p_from_region_code IN VARCHAR2,
2344 p_to_page_appl_id IN NUMBER,
2345 p_to_page_code IN VARCHAR2,
2346 p_to_region_appl_id IN NUMBER,
2347 p_to_region_code IN VARCHAR2,
2348 p_application_id IN NUMBER,
2349 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
2350 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2351 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2352 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2353 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2354 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2355 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2356 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2357 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2358 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2359 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2360 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2361 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2362 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2363 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2364 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2365 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
2366 p_creation_date IN DATE := FND_API.G_MISS_DATE,
2367 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
2368 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
2369 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
2370 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
2371 p_pass IN NUMBER,
2372 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
2373 ) is
2374 l_api_version_number CONSTANT number := 1.0;
2375 l_api_name CONSTANT varchar2(30) := 'Create_Region_Relation';
2376 l_created_by number;
2377 l_creation_date date;
2378 l_lang varchar2(30);
2379 l_last_update_date date;
2380 l_last_update_login number;
2381 l_last_updated_by number;
2382 l_attribute_category VARCHAR2(30);
2383 l_attribute1 VARCHAR2(150);
2384 l_attribute2 VARCHAR2(150);
2385 l_attribute3 VARCHAR2(150);
2386 l_attribute4 VARCHAR2(150);
2387 l_attribute5 VARCHAR2(150);
2388 l_attribute6 VARCHAR2(150);
2389 l_attribute7 VARCHAR2(150);
2390 l_attribute8 VARCHAR2(150);
2391 l_attribute9 VARCHAR2(150);
2392 l_attribute10 VARCHAR2(150);
2393 l_attribute11 VARCHAR2(150);
2394 l_attribute12 VARCHAR2(150);
2395 l_attribute13 VARCHAR2(150);
2396 l_attribute14 VARCHAR2(150);
2397 l_attribute15 VARCHAR2(150);
2398 l_return_status varchar2(1);
2399 begin
2400
2401 IF NOT FND_API.Compatible_API_Call (
2402 l_api_version_number, p_api_version_number, l_api_name,
2403 G_PKG_NAME) then
2404 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2405 return;
2406 END IF;
2407
2408 -- Initialize the message table if requested.
2409
2410 if p_init_msg_tbl then
2411 FND_MSG_PUB.initialize;
2412 end if;
2413
2414 savepoint start_create_relation;
2415
2416 --** check to see if row already exists **
2417 if AK_FLOW_PVT.REGION_RELATION_EXISTS (
2418 p_api_version_number => 1.0,
2419 p_return_status => l_return_status,
2420 p_flow_application_id => p_flow_application_id,
2421 p_flow_code => p_flow_code,
2422 p_foreign_key_name => p_foreign_key_name,
2423 p_from_page_appl_id => p_from_page_appl_id,
2424 p_from_page_code => p_from_page_code,
2425 p_from_region_appl_id => p_from_region_appl_id,
2426 p_from_region_code => p_from_region_code,
2427 p_to_page_appl_id => p_to_page_appl_id,
2428 p_to_page_code => p_to_page_code,
2429 p_to_region_appl_id => p_to_region_appl_id,
2430 p_to_region_code => p_to_region_code) then
2431 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2432 FND_MESSAGE.SET_NAME('AK','AK_RELATION_EXISTS');
2433 FND_MSG_PUB.Add;
2434 end if;
2435 raise FND_API.G_EXC_ERROR;
2436 end if;
2437
2441 p_validation_level => p_validation_level,
2438 --** validate table columns passed in **
2439 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
2440 if not AK_FLOW3_PVT.VALIDATE_REGION_RELATION (
2442 p_api_version_number => 1.0,
2443 p_return_status => l_return_status,
2444 p_flow_application_id => p_flow_application_id,
2445 p_flow_code => p_flow_code,
2446 p_foreign_key_name => p_foreign_key_name,
2447 p_from_page_appl_id => p_from_page_appl_id,
2448 p_from_page_code => p_from_page_code,
2449 p_from_region_appl_id => p_from_region_appl_id,
2450 p_from_region_code => p_from_region_code,
2451 p_to_page_appl_id => p_to_page_appl_id,
2452 p_to_page_code => p_to_page_code,
2453 p_to_region_appl_id => p_to_region_appl_id,
2454 p_to_region_code => p_to_region_code,
2455 p_application_id => p_application_id,
2456 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
2457 p_pass => p_pass
2458 ) then
2459 -- Do not raise an error if it's the first pass
2460 if (p_pass = 1) then
2461 p_copy_redo_flag := TRUE;
2462 else
2463 raise FND_API.G_EXC_ERROR;
2464 end if;
2465 end if;
2466 end if;
2467
2468 --** Load non-required columns if their values are given **
2469 --
2470 if (p_attribute_category <> FND_API.G_MISS_CHAR) then
2471 l_attribute_category := p_attribute_category;
2472 end if;
2473
2474 if (p_attribute1 <> FND_API.G_MISS_CHAR) then
2475 l_attribute1 := p_attribute1;
2476 end if;
2477
2478 if (p_attribute2 <> FND_API.G_MISS_CHAR) then
2479 l_attribute2 := p_attribute2;
2480 end if;
2481
2482 if (p_attribute3 <> FND_API.G_MISS_CHAR) then
2483 l_attribute3 := p_attribute3;
2484 end if;
2485
2486 if (p_attribute4 <> FND_API.G_MISS_CHAR) then
2487 l_attribute4 := p_attribute4;
2488 end if;
2489
2490 if (p_attribute5 <> FND_API.G_MISS_CHAR) then
2491 l_attribute5 := p_attribute5;
2492 end if;
2493
2494 if (p_attribute6 <> FND_API.G_MISS_CHAR) then
2495 l_attribute6 := p_attribute6;
2496 end if;
2497
2498 if (p_attribute7 <> FND_API.G_MISS_CHAR) then
2499 l_attribute7:= p_attribute7;
2500 end if;
2501
2502 if (p_attribute8 <> FND_API.G_MISS_CHAR) then
2503 l_attribute8 := p_attribute8;
2504 end if;
2505
2506 if (p_attribute9 <> FND_API.G_MISS_CHAR) then
2507 l_attribute9 := p_attribute9;
2508 end if;
2509
2510 if (p_attribute10 <> FND_API.G_MISS_CHAR) then
2511 l_attribute10 := p_attribute10;
2512 end if;
2513
2514 if (p_attribute11 <> FND_API.G_MISS_CHAR) then
2515 l_attribute11 := p_attribute11;
2516 end if;
2517
2518 if (p_attribute12 <> FND_API.G_MISS_CHAR) then
2519 l_attribute12 := p_attribute12;
2520 end if;
2521
2522 if (p_attribute13 <> FND_API.G_MISS_CHAR) then
2523 l_attribute13 := p_attribute13;
2524 end if;
2525
2526 if (p_attribute14 <> FND_API.G_MISS_CHAR) then
2527 l_attribute14 := p_attribute14;
2528 end if;
2529
2530 if (p_attribute15 <> FND_API.G_MISS_CHAR) then
2531 l_attribute15 := p_attribute15;
2532 end if;
2533
2534 if (p_created_by <> FND_API.G_MISS_NUM) then
2535 l_created_by := p_created_by;
2536 end if;
2537
2538 if (p_creation_date <> FND_API.G_MISS_DATE) then
2539 l_creation_date := p_creation_date;
2540 end if;
2541
2542 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
2543 l_last_updated_by := p_last_updated_by;
2544 end if;
2545
2546 if (p_last_update_date <> FND_API.G_MISS_DATE) then
2547 l_last_update_date := p_last_update_date;
2548 end if;
2549
2550 if (p_last_update_login <> FND_API.G_MISS_NUM) then
2551 l_last_update_login := p_last_update_login;
2552 end if;
2553
2554 -- Create record if no validation error was found
2555 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
2556 -- old jlt files didn't have who columns and IS_UPDATEABLE
2557 -- calls SET_WHO which populates those columns, for later
2558 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
2559
2560 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
2561 p_loader_timestamp => p_loader_timestamp,
2562 p_created_by => l_created_by,
2563 p_creation_date => l_creation_date,
2564 p_last_updated_by => l_last_updated_by,
2565 p_db_last_updated_by => null,
2566 p_last_update_date => l_last_update_date,
2567 p_db_last_update_date => null,
2568 p_last_update_login => l_last_update_login,
2569 p_create_or_update => 'CREATE') then
2570 null;
2571 end if;
2572
2573 select userenv('LANG') into l_lang
2574 from dual;
2575
2576 insert into AK_FLOW_REGION_RELATIONS (
2577 FLOW_APPLICATION_ID,
2578 FLOW_CODE,
2579 FOREIGN_KEY_NAME,
2580 FROM_PAGE_APPL_ID,
2581 FROM_PAGE_CODE,
2582 FROM_REGION_APPL_ID,
2583 FROM_REGION_CODE,
2584 TO_PAGE_APPL_ID,
2585 TO_PAGE_CODE,
2586 TO_REGION_APPL_ID,
2587 TO_REGION_CODE,
2588 APPLICATION_ID,
2589 ATTRIBUTE_CATEGORY,
2590 ATTRIBUTE1,
2591 ATTRIBUTE2,
2592 ATTRIBUTE3,
2593 ATTRIBUTE4,
2594 ATTRIBUTE5,
2595 ATTRIBUTE6,
2596 ATTRIBUTE7,
2597 ATTRIBUTE8,
2598 ATTRIBUTE9,
2599 ATTRIBUTE10,
2600 ATTRIBUTE11,
2601 ATTRIBUTE12,
2602 ATTRIBUTE13,
2603 ATTRIBUTE14,
2604 ATTRIBUTE15,
2605 CREATION_DATE,
2606 CREATED_BY,
2607 LAST_UPDATE_DATE,
2611 p_flow_application_id,
2608 LAST_UPDATED_BY,
2609 LAST_UPDATE_LOGIN
2610 ) values (
2612 p_flow_code,
2613 p_foreign_key_name,
2614 p_from_page_appl_id,
2615 p_from_page_code,
2616 p_from_region_appl_id,
2617 p_from_region_code,
2618 p_to_page_appl_id,
2619 p_to_page_code,
2620 p_to_region_appl_id,
2621 p_to_region_code,
2622 p_application_id,
2623 l_attribute_category,
2624 l_attribute1,
2625 l_attribute2,
2626 l_attribute3,
2627 l_attribute4,
2628 l_attribute5,
2629 l_attribute6,
2630 l_attribute7,
2631 l_attribute8,
2632 l_attribute9,
2633 l_attribute10,
2634 l_attribute11,
2635 l_attribute12,
2636 l_attribute13,
2637 l_attribute14,
2638 l_attribute15,
2639 l_creation_date,
2640 l_created_by,
2641 l_last_update_date,
2642 l_last_updated_by,
2643 l_last_update_login
2644 );
2645
2646 -- /** commit the insert **/
2647 -- commit;
2648
2649 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
2650 FND_MESSAGE.SET_NAME('AK','AK_RELATION_CREATED');
2651 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
2652 ' ' || p_flow_code ||
2653 ' ' || p_foreign_key_name ||
2654 ' ' || to_char(p_from_page_appl_id) ||
2655 ' ' || p_from_page_code ||
2656 ' ' || to_char(p_from_region_appl_id) ||
2657 ' ' || p_from_region_code ||
2658 ' ' || to_char(p_to_page_appl_id) ||
2659 ' ' || p_to_page_code ||
2660 ' ' || to_char(p_to_region_appl_id) ||
2661 ' ' || p_to_region_code);
2662 FND_MSG_PUB.Add;
2663 end if;
2664
2665 p_return_status := FND_API.G_RET_STS_SUCCESS;
2666
2667 FND_MSG_PUB.Count_And_Get (
2668 p_count => p_msg_count,
2669 p_data => p_msg_data);
2670
2671 EXCEPTION
2672 WHEN VALUE_ERROR THEN
2673 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2674 FND_MESSAGE.SET_NAME('AK','AK_RELATION_VALUE_ERROR');
2675 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
2676 ' ' || p_flow_code ||
2677 ' ' || p_foreign_key_name ||
2678 ' ' || to_char(p_from_page_appl_id) ||
2679 ' ' || p_from_page_code ||
2680 ' ' || to_char(p_from_region_appl_id) ||
2681 ' ' || p_from_region_code ||
2682 ' ' || to_char(p_to_page_appl_id) ||
2683 ' ' || p_to_page_code ||
2684 ' ' || to_char(p_to_region_appl_id) ||
2685 ' ' || p_to_region_code);
2686 FND_MSG_PUB.Add;
2687 end if;
2688 p_return_status := FND_API.G_RET_STS_ERROR;
2689 rollback to start_create_relation;
2690 FND_MSG_PUB.Count_And_Get (
2691 p_count => p_msg_count,
2692 p_data => p_msg_data);
2693 WHEN FND_API.G_EXC_ERROR THEN
2694 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2695 FND_MESSAGE.SET_NAME('AK','AK_RELATION_NOT_CREATED');
2696 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
2697 ' ' || p_flow_code ||
2698 ' ' || p_foreign_key_name ||
2699 ' ' || to_char(p_from_page_appl_id) ||
2700 ' ' || p_from_page_code ||
2701 ' ' || to_char(p_from_region_appl_id) ||
2702 ' ' || p_from_region_code ||
2703 ' ' || to_char(p_to_page_appl_id) ||
2704 ' ' || p_to_page_code ||
2705 ' ' || to_char(p_to_region_appl_id) ||
2706 ' ' || p_to_region_code);
2707 FND_MSG_PUB.Add;
2708 end if;
2709 p_return_status := FND_API.G_RET_STS_ERROR;
2710 rollback to start_create_relation;
2711 FND_MSG_PUB.Count_And_Get (
2712 p_count => p_msg_count,
2713 p_data => p_msg_data);
2714 WHEN OTHERS THEN
2715 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2716 rollback to start_create_relation;
2717 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2718 SUBSTR (SQLERRM, 1, 240) );
2719 FND_MSG_PUB.Add;
2720 FND_MSG_PUB.Count_And_Get (
2721 p_count => p_msg_count,
2722 p_data => p_msg_data);
2723 end CREATE_REGION_RELATION;
2724
2725 --=======================================================
2726 -- Procedure DELETE_FLOW
2727 --
2728 -- Usage Private API for deleting a flow. This
2729 -- API should only be called by other APIs that are
2730 -- owned by the Core Modules Team (AK).
2731 --
2732 -- Desc Deletes a flow with the given key value.
2733 --
2734 -- Results The API returns the standard p_return_status parameter
2735 -- indicating one of the standard return statuses :
2736 -- * Unexpected error
2737 -- * Error
2738 -- * Success
2739 -- Parameters p_flow_application_id : IN required
2740 -- p_flow_code : IN required
2741 -- Key value of the flow to be deleted.
2742 -- p_delete_cascade : IN required
2743 -- If p_delete_cascade flag is 'Y', also delete all
2744 -- rows in other tables that references this record.
2745 -- Otherwise, this record will not be deleted if there
2746 -- are any other rows referencing it.
2747 --
2748 -- Version Initial version number = 1.0
2749 -- History Current version number = 1.0
2750 --=======================================================
2751 procedure DELETE_FLOW (
2752 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2753 p_api_version_number IN NUMBER,
2754 p_init_msg_tbl IN BOOLEAN := FALSE,
2755 p_msg_count OUT NOCOPY NUMBER,
2756 p_msg_data OUT NOCOPY VARCHAR2,
2760 p_delete_cascade IN VARCHAR2
2757 p_return_status OUT NOCOPY VARCHAR2,
2758 p_flow_application_id IN NUMBER,
2759 p_flow_code IN VARCHAR2,
2761 ) is
2762 cursor l_get_pages_csr is
2763 select PAGE_APPLICATION_ID, PAGE_CODE
2764 from AK_FLOW_PAGES
2765 where flow_application_id = p_flow_application_id
2766 and flow_code = p_flow_code;
2767 l_api_version_number CONSTANT number := 1.0;
2768 l_api_name CONSTANT varchar2(30):= 'Delete_Flow';
2769 l_msg_count NUMBER;
2770 l_msg_data VARCHAR2(2000);
2771 l_page_application_id NUMBER;
2772 l_page_code VARCHAR2(30);
2773 l_return_status varchar2(1);
2774 begin
2775 IF NOT FND_API.Compatible_API_Call (
2776 l_api_version_number, p_api_version_number, l_api_name,
2777 G_PKG_NAME) then
2778 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2779 return;
2780 END IF;
2781
2782 -- Initialize the message table if requested.
2783
2784 if p_init_msg_tbl then
2785 FND_MSG_PUB.initialize;
2786 end if;
2787
2788 savepoint start_delete_flow;
2789
2790 --
2791 -- error if flow to be deleted does not exists
2792 --
2793 if NOT AK_FLOW_PVT.FLOW_EXISTS (
2794 p_api_version_number => 1.0,
2795 p_return_status => l_return_status,
2796 p_flow_application_id => p_flow_application_id,
2797 p_flow_code => p_flow_code) then
2798 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2799 FND_MESSAGE.SET_NAME('AK','AK_FLOW_DOES_NOT_EXIST');
2800 FND_MSG_PUB.Add;
2801 end if;
2802 raise FND_API.G_EXC_ERROR;
2803 end if;
2804
2805 if (p_delete_cascade = 'N') then
2806 --
2807 -- If we are not deleting any referencing records, we cannot
2808 -- delete the flow if it is being referenced in any of
2809 -- following tables.
2810 --
2811 -- AK_FLOW_PAGES
2812 --
2813 open l_get_pages_csr;
2814 fetch l_get_pages_csr into l_page_application_id, l_page_code;
2815 if l_get_pages_csr%found then
2816 close l_get_pages_csr;
2817 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2818 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_FLOW_PG');
2819 FND_MSG_PUB.Add;
2820 end if;
2821 raise FND_API.G_EXC_ERROR;
2822 end if;
2823 close l_get_pages_csr;
2824 else
2825 --
2826 -- Otherwise, delete all referencing rows in other tables
2827 --
2828 -- AK_FLOW_PAGES
2829 --
2830 open l_get_pages_csr;
2831 loop
2832 fetch l_get_pages_csr into l_page_application_id, l_page_code;
2833 exit when l_get_pages_csr%notfound;
2834 AK_FLOW_PVT.DELETE_PAGE (
2835 p_validation_level => p_validation_level,
2836 p_api_version_number => 1.0,
2837 p_msg_count => l_msg_count,
2838 p_msg_data => l_msg_data,
2839 p_return_status => l_return_status,
2840 p_flow_application_id => p_flow_application_id,
2841 p_flow_code => p_flow_code,
2842 p_page_application_id => l_page_application_id,
2843 p_page_code => l_page_code,
2844 p_delete_cascade => p_delete_cascade
2845 );
2846 if (l_return_status = FND_API.G_RET_STS_ERROR) or
2847 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
2848 close l_get_pages_csr;
2849 raise FND_API.G_EXC_ERROR;
2850 end if;
2851 end loop;
2852 close l_get_pages_csr;
2853 end if;
2854
2855 --
2856 -- delete flow once we checked that there are no references
2857 -- to it, or all references have been deleted.
2858 --
2859 delete from ak_flows
2860 where flow_application_id = p_flow_application_id
2861 and flow_code = p_flow_code;
2862
2863 if (sql%notfound) then
2864 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2865 FND_MESSAGE.SET_NAME('AK','AK_FLOW_DOES_NOT_EXIST');
2866 FND_MSG_PUB.Add;
2867 end if;
2868 raise FND_API.G_EXC_ERROR;
2869 end if;
2870
2871 delete from ak_flows_tl
2872 where flow_application_id = p_flow_application_id
2873 and flow_code = p_flow_code;
2874
2875 if (sql%notfound) then
2876 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2877 FND_MESSAGE.SET_NAME('AK','AK_FLOW_DOES_NOT_EXIST');
2878 FND_MSG_PUB.Add;
2879 end if;
2880 raise FND_API.G_EXC_ERROR;
2881 end if;
2882
2883 --
2884 -- Load success message
2885 --
2886 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
2887 FND_MESSAGE.SET_NAME('AK','AK_FLOW_DELETED');
2888 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
2889 ' ' || p_flow_code);
2890 FND_MSG_PUB.Add;
2891 end if;
2892
2893 p_return_status := FND_API.G_RET_STS_SUCCESS;
2894
2895 FND_MSG_PUB.Count_And_Get (
2896 p_count => p_msg_count,
2897 p_data => p_msg_data);
2898
2899 EXCEPTION
2900 WHEN FND_API.G_EXC_ERROR THEN
2901 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2902 FND_MESSAGE.SET_NAME('AK','AK_FLOW_NOT_DELETED');
2903 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
2904 ' ' || p_flow_code);
2905 FND_MSG_PUB.Add;
2906 end if;
2907 p_return_status := FND_API.G_RET_STS_ERROR;
2908 rollback to start_delete_flow;
2909 FND_MSG_PUB.Count_And_Get (
2910 p_count => p_msg_count,
2911 p_data => p_msg_data);
2912 WHEN OTHERS THEN
2913 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2914 rollback to start_delete_flow;
2915 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2919 p_count => p_msg_count,
2916 SUBSTR (SQLERRM, 1, 240) );
2917 FND_MSG_PUB.Add;
2918 FND_MSG_PUB.Count_And_Get (
2920 p_data => p_msg_data);
2921 end DELETE_FLOW;
2922
2923 --=======================================================
2924 -- Procedure DELETE_PAGE
2925 --
2926 -- Usage Private API for deleting a flow page. This
2927 -- API should only be called by other APIs that are
2928 -- owned by the Core Modules Team (AK).
2929 --
2930 -- Desc Deletes a flow page with the given key value.
2931 --
2932 -- Results The API returns the standard p_return_status parameter
2933 -- indicating one of the standard return statuses :
2934 -- * Unexpected error
2935 -- * Error
2936 -- * Success
2937 -- Parameters p_flow_application_id : IN required
2938 -- p_flow_code : IN required
2939 -- p_page_application_id : IN required
2940 -- p_page_code : IN required
2941 -- Key value of the flow page to be deleted.
2942 -- p_delete_cascade : IN required
2943 -- If p_delete_cascade flag is 'Y', also delete all
2944 -- rows in other tables that references this record.
2945 -- Otherwise, this record will not be deleted if there
2946 -- are any other rows referencing it.
2947 --
2948 -- Version Initial version number = 1.0
2949 -- History Current version number = 1.0
2950 --=======================================================
2951 procedure DELETE_PAGE (
2952 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2953 p_api_version_number IN NUMBER,
2954 p_init_msg_tbl IN BOOLEAN := FALSE,
2955 p_msg_count OUT NOCOPY NUMBER,
2956 p_msg_data OUT NOCOPY VARCHAR2,
2957 p_return_status OUT NOCOPY VARCHAR2,
2958 p_flow_application_id IN NUMBER,
2959 p_flow_code IN VARCHAR2,
2960 p_page_application_id IN NUMBER,
2961 p_page_code IN VARCHAR2,
2962 p_delete_cascade IN VARCHAR2
2963 ) is
2964 cursor l_get_page_regions_csr is
2965 select REGION_APPLICATION_ID, REGION_CODE
2966 from AK_FLOW_PAGE_REGIONS
2967 where flow_application_id = p_flow_application_id
2968 and flow_code = p_flow_code
2969 and page_application_id = p_page_application_id
2970 and page_code = p_page_code;
2971 cursor l_check_primary_csr is
2972 select 1
2973 from AK_FLOWS
2974 where flow_application_id = p_flow_application_id
2975 and flow_code = p_flow_code
2976 and primary_page_appl_id = p_page_application_id
2977 and primary_page_code = p_page_code;
2978 l_api_version_number CONSTANT number := 1.0;
2979 l_api_name CONSTANT varchar2(30):= 'Delete_Page';
2980 l_dummy NUMBER;
2981 l_msg_count NUMBER;
2982 l_msg_data VARCHAR2(2000);
2983 l_region_application_id NUMBER;
2984 l_region_code VARCHAR2(30);
2985 l_return_status varchar2(1);
2986 l_pass NUMBER := 2;
2987 l_copy_redo_flag BOOLEAN := FALSE;
2988 begin
2989 IF NOT FND_API.Compatible_API_Call (
2990 l_api_version_number, p_api_version_number, l_api_name,
2991 G_PKG_NAME) then
2992 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2993 return;
2994 END IF;
2995
2996 -- Initialize the message table if requested.
2997
2998 if p_init_msg_tbl then
2999 FND_MSG_PUB.initialize;
3000 end if;
3001
3002 savepoint start_delete_page;
3003
3004 --
3005 -- error if flow page to be deleted does not exists
3006 --
3007 if NOT AK_FLOW_PVT.PAGE_EXISTS (
3008 p_api_version_number => 1.0,
3009 p_return_status => l_return_status,
3010 p_flow_application_id => p_flow_application_id,
3011 p_flow_code => p_flow_code,
3012 p_page_application_id => p_page_application_id,
3013 p_page_code => p_page_code) then
3014 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3015 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_DOES_NOT_EXIST');
3016 FND_MSG_PUB.Add;
3017 end if;
3018 raise FND_API.G_EXC_ERROR;
3019 end if;
3020
3021 if (p_delete_cascade = 'N') then
3022 --
3023 -- If we are not deleting any referencing records, we cannot
3024 -- delete the flow page if it is being referenced in any of
3025 -- following tables.
3026 --
3027 -- AK_FLOW_PAGE_REGIONS
3028 --
3029 open l_get_page_regions_csr;
3030 fetch l_get_page_regions_csr into l_region_application_id, l_region_code;
3031 if l_get_page_regions_csr%found then
3032 close l_get_page_regions_csr;
3033 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3034 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_PG_PGREG');
3035 FND_MSG_PUB.Add;
3036 end if;
3037 raise FND_API.G_EXC_ERROR;
3038 end if;
3039 close l_get_page_regions_csr;
3040 --
3041 -- AK_FLOWS (primary page of a flow)
3042 --
3043 open l_check_primary_csr;
3044 fetch l_check_primary_csr into l_dummy;
3045 if l_check_primary_csr%found then
3046 close l_check_primary_csr;
3047 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3048 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_PPG_FLOW');
3049 FND_MSG_PUB.Add;
3050 end if;
3051 raise FND_API.G_EXC_ERROR;
3055 --
3052 end if;
3053 close l_check_primary_csr;
3054 else
3056 -- Otherwise, delete all referencing rows in other tables
3057 --
3058 -- AK_FLOW_PAGE_REGIONS
3059 --
3060 open l_get_page_regions_csr;
3061 loop
3062 fetch l_get_page_regions_csr into l_region_application_id, l_region_code;
3063 exit when l_get_page_regions_csr%notfound;
3064 AK_FLOW_PVT.DELETE_PAGE_REGION (
3065 p_validation_level => p_validation_level,
3066 p_api_version_number => 1.0,
3067 p_msg_count => l_msg_count,
3068 p_msg_data => l_msg_data,
3069 p_return_status => l_return_status,
3070 p_flow_application_id => p_flow_application_id,
3071 p_flow_code => p_flow_code,
3072 p_page_application_id => p_page_application_id,
3073 p_page_code => p_page_code,
3074 p_region_application_id => l_region_application_id,
3075 p_region_code => l_region_code,
3076 p_delete_cascade => p_delete_cascade
3077 );
3078 if (l_return_status = FND_API.G_RET_STS_ERROR) or
3079 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
3080 close l_get_page_regions_csr;
3081 raise FND_API.G_EXC_ERROR;
3082 end if;
3083 end loop;
3084 close l_get_page_regions_csr;
3085 --
3086 -- AK_FLOWS (primary page of a flow)
3087 --
3088 -- - invalidates flow's primary page
3089 --
3090 open l_check_primary_csr;
3091 fetch l_check_primary_csr into l_dummy;
3092 if l_check_primary_csr%found then
3093 AK_FLOW_PVT.UPDATE_FLOW (
3094 p_validation_level => p_validation_level,
3095 p_api_version_number => 1.0,
3096 p_msg_count => l_msg_count,
3097 p_msg_data => l_msg_data,
3098 p_return_status => l_return_status,
3099 p_flow_application_id => p_flow_application_id,
3100 p_flow_code => p_flow_code,
3101 p_primary_page_code => G_NO_PRIMARY_PAGE_CODE,
3102 p_pass => l_pass,
3103 p_copy_redo_flag => l_copy_redo_flag
3104 );
3105 if (l_return_status = FND_API.G_RET_STS_ERROR) or
3106 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
3107 close l_check_primary_csr;
3108 raise FND_API.G_EXC_ERROR;
3109 end if;
3110 --
3111 -- issue a warning asking the user to re-assign a primary page
3112 -- to this flow
3113 --
3114 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3115 FND_MESSAGE.SET_NAME('AK','AK_ASSIGN_PRIMARY_PG_FLOW');
3116 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
3117 ' ' || p_flow_code);
3118 FND_MSG_PUB.Add;
3119 end if;
3120 end if;
3121 close l_check_primary_csr;
3122 end if;
3123
3124 --
3125 -- delete flow page once we checked that there are no references
3126 -- to it, or all references have been deleted.
3127 --
3128 delete from ak_flow_pages
3129 where flow_application_id = p_flow_application_id
3130 and flow_code = p_flow_code
3131 and page_application_id = p_page_application_id
3132 and page_code = p_page_code;
3133
3134 if (sql%notfound) then
3135 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3136 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_DOES_NOT_EXIST');
3137 FND_MSG_PUB.Add;
3138 end if;
3139 raise FND_API.G_EXC_ERROR;
3140 end if;
3141
3142 delete from ak_flow_pages_tl
3143 where flow_application_id = p_flow_application_id
3144 and flow_code = p_flow_code
3145 and page_application_id = p_page_application_id
3146 and page_code = p_page_code;
3147
3148 if (sql%notfound) then
3149 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3150 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_DOES_NOT_EXIST');
3151 FND_MSG_PUB.Add;
3152 end if;
3153 raise FND_API.G_EXC_ERROR;
3154 end if;
3155
3156 --
3157 -- Load success message
3158 --
3159 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
3160 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_DELETED');
3161 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
3162 ' ' || p_flow_code ||
3163 ' ' || to_char(p_page_application_id) ||
3164 ' ' || p_page_code);
3165 FND_MSG_PUB.Add;
3166 end if;
3167
3168 p_return_status := FND_API.G_RET_STS_SUCCESS;
3169
3170 FND_MSG_PUB.Count_And_Get (
3171 p_count => p_msg_count,
3172 p_data => p_msg_data);
3173
3174 EXCEPTION
3175 WHEN FND_API.G_EXC_ERROR THEN
3176 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3177 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_NOT_DELETED');
3178 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
3179 ' ' || p_flow_code ||
3180 ' ' || to_char(p_page_application_id) ||
3181 ' ' || p_page_code);
3182 FND_MSG_PUB.Add;
3183 end if;
3184 p_return_status := FND_API.G_RET_STS_ERROR;
3185 rollback to start_delete_page;
3186 FND_MSG_PUB.Count_And_Get (
3187 p_count => p_msg_count,
3188 p_data => p_msg_data);
3189 WHEN OTHERS THEN
3190 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3191 rollback to start_delete_page;
3192 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3193 SUBSTR (SQLERRM, 1, 240) );
3194 FND_MSG_PUB.Add;
3195 FND_MSG_PUB.Count_And_Get (
3196 p_count => p_msg_count,
3197 p_data => p_msg_data);
3198 end DELETE_PAGE;
3199
3200 --=======================================================
3201 -- Procedure DELETE_PAGE_REGION
3202 --
3203 -- Usage Private API for deleting a flow page region. This
3204 -- API should only be called by other APIs that are
3208 --
3205 -- owned by the Core Modules Team (AK).
3206 --
3207 -- Desc Deletes a flow page region with the given key value.
3209 -- Results The API returns the standard p_return_status parameter
3210 -- indicating one of the standard return statuses :
3211 -- * Unexpected error
3212 -- * Error
3213 -- * Success
3214 -- Parameters p_flow_application_id : IN required
3215 -- p_flow_code : IN required
3216 -- p_page_application_id : IN required
3217 -- p_page_code : IN required
3218 -- p_region_application_id : IN required
3219 -- p_region_code : IN required
3220 -- Key value of the flow page region to be deleted.
3221 -- p_delete_cascade : IN required
3222 -- If p_delete_cascade flag is 'Y', also delete all
3223 -- rows in other tables that references this record.
3224 -- Otherwise, this record will not be deleted if there
3225 -- are any other rows referencing it.
3226 --
3227 -- Version Initial version number = 1.0
3228 -- History Current version number = 1.0
3229 --=======================================================
3230 procedure DELETE_PAGE_REGION (
3231 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3232 p_api_version_number IN NUMBER,
3233 p_init_msg_tbl IN BOOLEAN := FALSE,
3234 p_msg_count OUT NOCOPY NUMBER,
3235 p_msg_data OUT NOCOPY VARCHAR2,
3236 p_return_status OUT NOCOPY VARCHAR2,
3237 p_flow_application_id IN NUMBER,
3238 p_flow_code IN VARCHAR2,
3239 p_page_application_id IN NUMBER,
3240 p_page_code IN VARCHAR2,
3241 p_region_application_id IN NUMBER,
3242 p_region_code IN VARCHAR2,
3243 p_delete_cascade IN VARCHAR2
3244 ) is
3245 cursor l_get_items_csr is
3246 select ATTRIBUTE_APPLICATION_ID, ATTRIBUTE_CODE
3247 from AK_FLOW_PAGE_REGION_ITEMS
3248 where flow_application_id = p_flow_application_id
3249 and flow_code = p_flow_code
3250 and page_application_id = p_page_application_id
3251 and page_code = p_page_code
3252 and region_application_id = p_region_application_id
3253 and region_code = p_region_code;
3254 cursor l_get_from_relations_csr is
3255 select FOREIGN_KEY_NAME, TO_PAGE_APPL_ID, TO_PAGE_CODE,
3256 TO_REGION_APPL_ID, TO_REGION_CODE
3257 from AK_FLOW_REGION_RELATIONS
3258 where flow_application_id = p_flow_application_id
3259 and flow_code = p_flow_code
3260 and from_page_appl_id = p_page_application_id
3261 and from_page_code = p_page_code
3262 and from_region_appl_id = p_region_application_id
3263 and from_region_code = p_region_code;
3264 cursor l_get_to_relations_csr is
3265 select FOREIGN_KEY_NAME, FROM_PAGE_APPL_ID, FROM_PAGE_CODE,
3266 FROM_REGION_APPL_ID, FROM_REGION_CODE
3267 from AK_FLOW_REGION_RELATIONS
3268 where flow_application_id = p_flow_application_id
3269 and flow_code = p_flow_code
3270 and to_page_appl_id = p_page_application_id
3271 and to_page_code = p_page_code
3272 and to_region_appl_id = p_region_application_id
3273 and to_region_code = p_region_code;
3274 cursor l_check_primary_csr is
3275 select 1
3276 from AK_FLOW_PAGES
3277 where flow_application_id = p_flow_application_id
3278 and flow_code = p_flow_code
3279 and page_application_id = p_page_application_id
3280 and page_code = p_page_code
3281 and primary_region_appl_id = p_region_application_id
3282 and primary_region_code = p_region_code;
3283 cursor l_get_child_regions_csr is
3284 select region_application_id, region_code
3285 from AK_FLOW_PAGE_REGIONS
3286 where flow_application_id = p_flow_application_id
3287 and flow_code = p_flow_code
3288 and page_application_id = p_page_application_id
3289 and page_code = p_page_code
3290 and parent_region_application_id = p_region_application_id
3291 and parent_region_code = p_region_code;
3292 l_api_version_number CONSTANT number := 1.0;
3293 l_api_name CONSTANT varchar2(30):= 'Delete_Page_Region';
3294 l_attribute_appl_id NUMBER;
3295 l_attribute_code VARCHAR2(30);
3296 l_dummy NUMBER;
3297 l_foreign_key_name VARCHAR2(30);
3298 l_msg_count NUMBER;
3299 l_msg_data VARCHAR2(2000);
3300 l_page_appl_id NUMBER;
3301 l_page_code VARCHAR2(30);
3302 l_region_appl_id NUMBER;
3303 l_region_code VARCHAR2(30);
3304 l_return_status varchar2(1);
3305 l_copy_redo_flag BOOLEAN := FALSE;
3306 l_pass NUMBER := 2;
3307 begin
3308 IF NOT FND_API.Compatible_API_Call (
3309 l_api_version_number, p_api_version_number, l_api_name,
3310 G_PKG_NAME) then
3311 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3312 return;
3313 END IF;
3314
3315 -- Initialize the message table if requested.
3316
3317 if p_init_msg_tbl then
3318 FND_MSG_PUB.initialize;
3319 end if;
3320
3321 savepoint start_delete_page_region;
3322
3323 --
3324 -- error if page region to be deleted does not exists
3325 --
3326 if NOT AK_FLOW_PVT.PAGE_REGION_EXISTS (
3327 p_api_version_number => 1.0,
3328 p_return_status => l_return_status,
3332 p_page_code => p_page_code,
3329 p_flow_application_id => p_flow_application_id,
3330 p_flow_code => p_flow_code,
3331 p_page_application_id => p_page_application_id,
3333 p_region_application_id => p_region_application_id,
3334 p_region_code => p_region_code) then
3335 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3336 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_DOES_NOT_EXIST');
3337 FND_MSG_PUB.Add;
3338 end if;
3339 raise FND_API.G_EXC_ERROR;
3340 end if;
3341
3342 if (p_delete_cascade = 'N') then
3343 --
3344 -- If we are not deleting any referencing records, we cannot
3345 -- delete the flow page region if it is being referenced in any of
3346 -- following tables.
3347 --
3348 -- AK_FLOW_PAGE_REGION_ITEMS
3349 --
3350 open l_get_items_csr;
3351 fetch l_get_items_csr into l_attribute_appl_id, l_attribute_code;
3352 if l_get_items_csr%found then
3353 close l_get_items_csr;
3354 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3355 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_PGREG_ITEM');
3356 FND_MSG_PUB.Add;
3357 end if;
3358 raise FND_API.G_EXC_ERROR;
3359 end if;
3360 close l_get_items_csr;
3361 --
3362 -- AK_FLOW_REGION_RELATIONS (as from page region)
3363 --
3364 open l_get_from_relations_csr;
3365 fetch l_get_from_relations_csr into l_foreign_key_name,
3366 l_page_appl_id, l_page_code, l_region_appl_id, l_region_code;
3367 if l_get_from_relations_csr%found then
3368 close l_get_from_relations_csr;
3369 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3370 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_PGREG_REL');
3371 FND_MSG_PUB.Add;
3372 end if;
3373 raise FND_API.G_EXC_ERROR;
3374 end if;
3375 close l_get_from_relations_csr;
3376 --
3377 -- AK_FLOW_REGION_RELATIONS (as to page region)
3378 --
3379 open l_get_to_relations_csr;
3380 fetch l_get_to_relations_csr into l_foreign_key_name,
3381 l_page_appl_id, l_page_code, l_region_appl_id, l_region_code;
3382 if l_get_to_relations_csr%found then
3383 close l_get_to_relations_csr;
3384 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3385 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_PGREG_REL');
3386 FND_MSG_PUB.Add;
3387 end if;
3388 raise FND_API.G_EXC_ERROR;
3389 end if;
3390 close l_get_to_relations_csr;
3391 --
3392 -- AK_FLOW_PAGE_REGIONS (parent region of another region)
3393 --
3394 open l_get_child_regions_csr;
3395 fetch l_get_child_regions_csr into l_region_appl_id, l_region_code;
3396 if l_get_child_regions_csr%found then
3397 close l_get_child_regions_csr;
3398 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3399 FND_MESSAGE.SET_NAME('AK','EXISTING_CHILDREN_PAGE_REGIONS');
3400 FND_MSG_PUB.Add;
3401 end if;
3402 raise FND_API.G_EXC_ERROR;
3403 end if;
3404 close l_get_child_regions_csr;
3405 --
3406 -- AK_FLOW_PAGES (primary region of a page)
3407 --
3408 open l_check_primary_csr;
3409 fetch l_check_primary_csr into l_dummy;
3410 if l_check_primary_csr%found then
3411 close l_check_primary_csr;
3412 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3413 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_PPGREG_PG');
3414 FND_MSG_PUB.Add;
3415 end if;
3416 raise FND_API.G_EXC_ERROR;
3417 end if;
3418 close l_check_primary_csr;
3419 else
3420 --
3421 -- Otherwise, delete all referencing rows in other tables
3422 --
3423 -- AK_FLOW_PAGE_REGION_ITEMS
3424 --
3425 open l_get_items_csr;
3426 loop
3427 fetch l_get_items_csr into l_attribute_appl_id, l_attribute_code;
3428 exit when l_get_items_csr%notfound;
3429 AK_FLOW_PVT.DELETE_PAGE_REGION_ITEM (
3430 p_validation_level => p_validation_level,
3431 p_api_version_number => 1.0,
3432 p_msg_count => l_msg_count,
3433 p_msg_data => l_msg_data,
3434 p_return_status => l_return_status,
3435 p_flow_application_id => p_flow_application_id,
3436 p_flow_code => p_flow_code,
3437 p_page_application_id => p_page_application_id,
3438 p_page_code => p_page_code,
3439 p_region_application_id => p_region_application_id,
3440 p_region_code => p_region_code,
3441 p_attribute_application_id => l_attribute_appl_id,
3442 p_attribute_code => l_attribute_code,
3443 p_delete_cascade => p_delete_cascade
3444 );
3445 if (l_return_status = FND_API.G_RET_STS_ERROR) or
3446 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
3447 close l_get_items_csr;
3448 raise FND_API.G_EXC_ERROR;
3449 end if;
3450 end loop;
3451 close l_get_items_csr;
3452 --
3453 -- AK_FLOW_REGION_RELATIONS (as from page region)
3454 --
3455 open l_get_from_relations_csr;
3456 loop
3457 fetch l_get_from_relations_csr into l_foreign_key_name,
3458 l_page_appl_id, l_page_code, l_region_appl_id, l_region_code;
3459 exit when l_get_from_relations_csr%notfound;
3460 AK_FLOW_PVT.DELETE_REGION_RELATION (
3461 p_validation_level => p_validation_level,
3462 p_api_version_number => 1.0,
3463 p_msg_count => l_msg_count,
3464 p_msg_data => l_msg_data,
3465 p_return_status => l_return_status,
3466 p_flow_application_id => p_flow_application_id,
3467 p_flow_code => p_flow_code,
3468 p_foreign_key_name => l_foreign_key_name,
3469 p_from_page_appl_id => p_page_application_id,
3470 p_from_page_code => p_page_code,
3471 p_from_region_appl_id => p_region_application_id,
3472 p_from_region_code => p_region_code,
3476 p_to_region_code => l_region_code,
3473 p_to_page_appl_id => l_page_appl_id,
3474 p_to_page_code => l_page_code,
3475 p_to_region_appl_id => l_region_appl_id,
3477 p_delete_cascade => p_delete_cascade
3478 );
3479 if (l_return_status = FND_API.G_RET_STS_ERROR) or
3480 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
3481 close l_get_from_relations_csr;
3482 raise FND_API.G_EXC_ERROR;
3483 end if;
3484 end loop;
3485 close l_get_from_relations_csr;
3486 --
3487 -- AK_FLOW_REGION_RELATIONS (as to page region)
3488 --
3489 open l_get_to_relations_csr;
3490 loop
3491 fetch l_get_to_relations_csr into l_foreign_key_name,
3492 l_page_appl_id, l_page_code, l_region_appl_id, l_region_code;
3493 exit when l_get_to_relations_csr%notfound;
3494 AK_FLOW_PVT.DELETE_REGION_RELATION (
3495 p_validation_level => p_validation_level,
3496 p_api_version_number => 1.0,
3497 p_msg_count => l_msg_count,
3498 p_msg_data => l_msg_data,
3499 p_return_status => l_return_status,
3500 p_flow_application_id => p_flow_application_id,
3501 p_flow_code => p_flow_code,
3502 p_foreign_key_name => l_foreign_key_name,
3503 p_from_page_appl_id => l_page_appl_id,
3504 p_from_page_code => l_page_code,
3505 p_from_region_appl_id => l_region_appl_id,
3506 p_from_region_code => l_region_code,
3507 p_to_page_appl_id => p_page_application_id,
3508 p_to_page_code => p_page_code,
3509 p_to_region_appl_id => p_region_application_id,
3510 p_to_region_code => p_region_code,
3511 p_delete_cascade => p_delete_cascade
3512 );
3513 if (l_return_status = FND_API.G_RET_STS_ERROR) or
3514 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
3515 close l_get_from_relations_csr;
3516 raise FND_API.G_EXC_ERROR;
3517 end if;
3518 end loop;
3519 close l_get_to_relations_csr;
3520 --
3521 -- AK_FLOW_PAGE_REGIONS (parent region of another region)
3522 --
3523 -- -blank out the parent region columns of the child regions.
3524 --
3525 open l_get_child_regions_csr;
3526 loop
3527 fetch l_get_child_regions_csr into l_region_appl_id, l_region_code;
3528 exit when l_get_child_regions_csr%notfound;
3529 AK_FLOW_PVT.UPDATE_PAGE_REGION (
3530 p_validation_level => p_validation_level,
3531 p_api_version_number => 1.0,
3532 p_msg_count => l_msg_count,
3533 p_msg_data => l_msg_data,
3534 p_return_status => l_return_status,
3535 p_flow_application_id => p_flow_application_id,
3536 p_flow_code => p_flow_code,
3537 p_page_application_id => p_page_application_id,
3538 p_page_code => p_page_code,
3539 p_region_application_id => l_region_appl_id,
3540 p_region_code => l_region_code,
3541 p_parent_region_application_id => null,
3542 p_parent_region_code => null,
3543 p_pass => l_pass,
3544 p_copy_redo_flag => l_copy_redo_flag
3545 );
3546 if (l_return_status = FND_API.G_RET_STS_ERROR) or
3547 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
3548 close l_get_child_regions_csr;
3549 raise FND_API.G_EXC_ERROR;
3550 end if;
3551 end loop;
3552 close l_get_child_regions_csr;
3553 --
3554 -- AK_FLOW_PAGES (primary region of a page)
3555 --
3556 -- - invalidates flow page's primary region
3557 --
3558 open l_check_primary_csr;
3559 fetch l_check_primary_csr into l_dummy;
3560 if l_check_primary_csr%found then
3561 AK_FLOW_PVT.UPDATE_PAGE (
3562 p_validation_level => p_validation_level,
3563 p_api_version_number => 1.0,
3564 p_msg_count => l_msg_count,
3565 p_msg_data => l_msg_data,
3566 p_return_status => l_return_status,
3567 p_flow_application_id => p_flow_application_id,
3568 p_flow_code => p_flow_code,
3569 p_page_application_id => p_page_application_id,
3570 p_page_code => p_page_code,
3571 p_primary_region_code => G_NO_PRIMARY_REGION_CODE,
3572 p_pass => l_pass,
3573 p_copy_redo_flag => l_copy_redo_flag
3574 );
3575 if (l_return_status = FND_API.G_RET_STS_ERROR) or
3576 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
3577 close l_check_primary_csr;
3578 raise FND_API.G_EXC_ERROR;
3579 end if;
3580 --
3581 -- issue a warning asking the user to re-assign a primary page
3582 -- to this flow
3583 --
3584 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3585 FND_MESSAGE.SET_NAME('AK','AK_ASSIGN_PRIMARY_PGREG_PG');
3586 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
3587 ' ' || p_flow_code ||
3588 ' ' || to_char(p_page_application_id) ||
3589 ' ' || p_page_code);
3590 FND_MSG_PUB.Add;
3591 end if;
3592 end if;
3593 close l_check_primary_csr;
3594 end if;
3595
3596 --
3597 -- delete flow page region once we checked that there are no references
3598 -- to it, or all references have been deleted.
3599 --
3600 delete from ak_flow_page_regions
3601 where flow_application_id = p_flow_application_id
3602 and flow_code = p_flow_code
3603 and page_application_id = p_page_application_id
3604 and page_code = p_page_code
3605 and region_application_id = p_region_application_id
3606 and region_code = p_region_code;
3607
3608 if (sql%notfound) then
3609 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3610 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_DOES_NOT_EXIST');
3611 FND_MSG_PUB.Add;
3612 end if;
3613 raise FND_API.G_EXC_ERROR;
3614 end if;
3615
3616 --
3617 -- Load success message
3618 --
3619 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
3620 FND_MESSAGE.SET_NAME('AK','AK_PAGE_REGION_DELETED');
3624 ' ' || p_page_code ||
3621 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
3622 ' ' || p_flow_code ||
3623 ' ' || to_char(p_page_application_id) ||
3625 ' ' || to_char(p_region_application_id) ||
3626 ' ' || p_region_code);
3627 FND_MSG_PUB.Add;
3628 end if;
3629
3630 p_return_status := FND_API.G_RET_STS_SUCCESS;
3631
3632 FND_MSG_PUB.Count_And_Get (
3633 p_count => p_msg_count,
3634 p_data => p_msg_data);
3635
3636 EXCEPTION
3637 WHEN FND_API.G_EXC_ERROR THEN
3638 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3639 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_NOT_DELETED');
3640 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
3641 ' ' || p_flow_code ||
3642 ' ' || to_char(p_page_application_id) ||
3643 ' ' || p_page_code ||
3644 ' ' || to_char(p_region_application_id) ||
3645 ' ' || p_region_code);
3646 FND_MSG_PUB.Add;
3647 end if;
3648 p_return_status := FND_API.G_RET_STS_ERROR;
3649 rollback to start_delete_page_region;
3650 FND_MSG_PUB.Count_And_Get (
3651 p_count => p_msg_count,
3652 p_data => p_msg_data);
3653 WHEN OTHERS THEN
3654 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3655 rollback to start_delete_page_region;
3656 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3657 SUBSTR (SQLERRM, 1, 240) );
3658 FND_MSG_PUB.Add;
3659 FND_MSG_PUB.Count_And_Get (
3660 p_count => p_msg_count,
3661 p_data => p_msg_data);
3662 end DELETE_PAGE_REGION;
3663
3664 --=======================================================
3665 -- Procedure DELETE_PAGE_REGION_ITEM
3666 --
3667 -- Usage Private API for deleting a flow page region item.
3668 -- This API should only be called by other APIs that are
3669 -- owned by the Core Modules Team (AK).
3670 --
3671 -- Desc Deletes a flow page region item with the given key value.
3672 --
3673 -- Results The API returns the standard p_return_status parameter
3674 -- indicating one of the standard return statuses :
3675 -- * Unexpected error
3676 -- * Error
3677 -- * Success
3678 -- Parameters p_flow_application_id : IN required
3679 -- p_flow_code : IN required
3680 -- p_page_application_id : IN required
3681 -- p_page_code : IN required
3682 -- p_region_application_id : IN required
3683 -- p_region_code : IN required
3684 -- p_attribute_application_id : IN required
3685 -- p_attribute_code : IN required
3686 -- Key value of the flow page region item to be deleted.
3687 -- p_delete_cascade : IN required
3688 -- If p_delete_cascade flag is 'Y', also delete all
3689 -- rows in other tables that references this record.
3690 -- Otherwise, this record will not be deleted if there
3691 -- are any other rows referencing it.
3692 --
3693 -- Version Initial version number = 1.0
3694 -- History Current version number = 1.0
3695 --=======================================================
3696 procedure DELETE_PAGE_REGION_ITEM (
3697 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3698 p_api_version_number IN NUMBER,
3699 p_init_msg_tbl IN BOOLEAN := FALSE,
3700 p_msg_count OUT NOCOPY NUMBER,
3701 p_msg_data OUT NOCOPY VARCHAR2,
3702 p_return_status OUT NOCOPY VARCHAR2,
3703 p_flow_application_id IN NUMBER,
3704 p_flow_code IN VARCHAR2,
3705 p_page_application_id IN NUMBER,
3706 p_page_code IN VARCHAR2,
3707 p_region_application_id IN NUMBER,
3708 p_region_code IN VARCHAR2,
3709 p_attribute_application_id IN NUMBER,
3710 p_attribute_code IN VARCHAR2,
3711 p_delete_cascade IN VARCHAR2
3712 ) is
3713 l_api_version_number CONSTANT number := 1.0;
3714 l_api_name CONSTANT varchar2(30):= 'Delete_Page_Region_Item';
3715 l_return_status varchar2(1);
3716 begin
3717 IF NOT FND_API.Compatible_API_Call (
3718 l_api_version_number, p_api_version_number, l_api_name,
3719 G_PKG_NAME) then
3720 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3721 return;
3722 END IF;
3723
3724 -- Initialize the message table if requested.
3725
3726 if p_init_msg_tbl then
3727 FND_MSG_PUB.initialize;
3728 end if;
3729
3730 savepoint start_delete_region_item;
3731
3732 --
3733 -- error if page region item to be deleted does not exists
3734 --
3735 if NOT AK_FLOW_PVT.PAGE_REGION_ITEM_EXISTS (
3736 p_api_version_number => 1.0,
3737 p_return_status => l_return_status,
3738 p_flow_application_id => p_flow_application_id,
3739 p_flow_code => p_flow_code,
3740 p_page_application_id => p_page_application_id,
3741 p_page_code => p_page_code,
3742 p_region_application_id => p_region_application_id,
3743 p_region_code => p_region_code,
3744 p_attribute_application_id => p_attribute_application_id,
3745 p_attribute_code => p_attribute_code) then
3746 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3747 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_DOES_NOT_EXIST');
3748 FND_MSG_PUB.Add;
3749 end if;
3750 raise FND_API.G_EXC_ERROR;
3751 end if;
3755 -- If we are not deleting any referencing records, we cannot
3752
3753 if (p_delete_cascade = 'N') then
3754 --
3756 -- delete the page region item if it is being referenced in any of
3757 -- following tables.
3758 --
3759 -- none
3760 --
3761 null;
3762 else
3763 --
3764 -- Otherwise, delete all referencing rows in other tables
3765 --
3766 -- none
3767 --
3768 null;
3769 end if;
3770
3771 --
3772 -- delete page region item once we checked that there are no references
3773 -- to it, or all references have been deleted.
3774 --
3775 delete from ak_flow_page_region_items
3776 where flow_application_id = p_flow_application_id
3777 and flow_code = p_flow_code
3778 and page_application_id = p_page_application_id
3779 and page_code = p_page_code
3780 and region_application_id = p_region_application_id
3781 and region_code = p_region_code
3782 and attribute_application_id = p_attribute_application_id
3783 and attribute_code = p_attribute_code;
3784
3785 if (sql%notfound) then
3786 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3787 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_DOES_NOT_EXIST');
3788 FND_MSG_PUB.Add;
3789 end if;
3790 raise FND_API.G_EXC_ERROR;
3791 end if;
3792
3793 --
3794 -- Load success message
3795 --
3796 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
3797 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_DELETED');
3798 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
3799 ' ' || p_flow_code ||
3800 ' ' || to_char(p_page_application_id) ||
3801 ' ' || p_page_code ||
3802 ' ' || to_char(p_region_application_id) ||
3803 ' ' || p_region_code ||
3804 ' ' || to_char(p_attribute_application_id)||
3805 ' ' || p_attribute_code);
3806 FND_MSG_PUB.Add;
3807 end if;
3808
3809 p_return_status := FND_API.G_RET_STS_SUCCESS;
3810
3811 FND_MSG_PUB.Count_And_Get (
3812 p_count => p_msg_count,
3813 p_data => p_msg_data);
3814
3815 EXCEPTION
3816 WHEN FND_API.G_EXC_ERROR THEN
3817 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3818 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_NOT_DELETED');
3819 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
3820 ' ' || p_flow_code ||
3821 ' ' || to_char(p_page_application_id) ||
3822 ' ' || p_page_code ||
3823 ' ' || to_char(p_region_application_id) ||
3824 ' ' || p_region_code ||
3825 ' ' || to_char(p_attribute_application_id)||
3826 ' ' || p_attribute_code);
3827 FND_MSG_PUB.Add;
3828 end if;
3829 p_return_status := FND_API.G_RET_STS_ERROR;
3830 rollback to start_delete_region_item;
3831 FND_MSG_PUB.Count_And_Get (
3832 p_count => p_msg_count,
3833 p_data => p_msg_data);
3834 WHEN OTHERS THEN
3835 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3836 rollback to start_delete_region_item;
3837 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3838 SUBSTR (SQLERRM, 1, 240) );
3839 FND_MSG_PUB.Add;
3840 FND_MSG_PUB.Count_And_Get (
3841 p_count => p_msg_count,
3842 p_data => p_msg_data);
3843 end DELETE_PAGE_REGION_ITEM;
3844
3845 --=======================================================
3846 -- Procedure DELETE_REGION_RELATION
3847 --
3848 -- Usage Private API for deleting a flow region relation.
3849 -- This API should only be called by other APIs that are
3850 -- owned by the Core Modules Team (AK).
3851 --
3852 -- Desc Deletes a flow region relation with the given key value.
3853 --
3854 -- Results The API returns the standard p_return_status parameter
3855 -- indicating one of the standard return statuses :
3856 -- * Unexpected error
3857 -- * Error
3858 -- * Success
3859 -- Parameters p_flow_application_id : IN required
3860 -- p_flow_code : IN required
3861 -- p_foreign_key_name : IN required
3862 -- p_from_page_appl_id : IN required
3863 -- p_from_page_code : IN required
3864 -- p_from_region_appl_id : IN required
3865 -- p_from_region_code : IN required
3866 -- p_to_page_appl_id : IN required
3867 -- p_to_page_code : IN required
3868 -- p_to_region_appl_id : IN required
3869 -- p_to_region_code : IN required
3870 -- Key value of the flow region relation to be deleted.
3871 -- p_delete_cascade : IN required
3872 -- If p_delete_cascade flag is 'Y', also delete all
3873 -- rows in other tables that references this record.
3874 -- Otherwise, this record will not be deleted if there
3875 -- are any other rows referencing it.
3876 --
3877 -- Version Initial version number = 1.0
3878 -- History Current version number = 1.0
3879 --=======================================================
3880 procedure DELETE_REGION_RELATION (
3881 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3882 p_api_version_number IN NUMBER,
3883 p_init_msg_tbl IN BOOLEAN := FALSE,
3884 p_msg_count OUT NOCOPY NUMBER,
3885 p_msg_data OUT NOCOPY VARCHAR2,
3886 p_return_status OUT NOCOPY VARCHAR2,
3887 p_flow_application_id IN NUMBER,
3891 p_from_page_code IN VARCHAR2,
3888 p_flow_code IN VARCHAR2,
3889 p_foreign_key_name IN VARCHAR2,
3890 p_from_page_appl_id IN NUMBER,
3892 p_from_region_appl_id IN NUMBER,
3893 p_from_region_code IN VARCHAR2,
3894 p_to_page_appl_id IN NUMBER,
3895 p_to_page_code IN VARCHAR2,
3896 p_to_region_appl_id IN NUMBER,
3897 p_to_region_code IN VARCHAR2,
3898 p_delete_cascade IN VARCHAR2
3899 ) is
3900 cursor l_get_items_csr is
3901 select ATTRIBUTE_APPLICATION_ID, ATTRIBUTE_CODE
3902 from AK_FLOW_PAGE_REGION_ITEMS
3903 where flow_application_id = p_flow_application_id
3904 and flow_code = p_flow_code
3905 and page_application_id = p_from_page_appl_id
3906 and page_code = p_from_page_code
3907 and region_application_id = p_from_region_appl_id
3908 and region_code = p_from_region_code
3909 and to_page_appl_id = p_to_page_appl_id
3910 and to_page_code = p_to_page_code;
3911 l_api_version_number CONSTANT number := 1.0;
3912 l_api_name CONSTANT varchar2(30):= 'Delete_Region_Relation';
3913 l_attribute_application_id NUMBER;
3914 l_attribute_code VARCHAR2(30);
3915 l_msg_count NUMBER;
3916 l_msg_data VARCHAR2(2000);
3917 l_return_status varchar2(1);
3918 begin
3919 IF NOT FND_API.Compatible_API_Call (
3920 l_api_version_number, p_api_version_number, l_api_name,
3921 G_PKG_NAME) then
3922 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3923 return;
3924 END IF;
3925
3926 -- Initialize the message table if requested.
3927
3928 if p_init_msg_tbl then
3929 FND_MSG_PUB.initialize;
3930 end if;
3931
3932 savepoint start_delete_relation;
3933
3934 --
3935 -- error if region relation to be deleted does not exists
3936 --
3937 if NOT AK_FLOW_PVT.REGION_RELATION_EXISTS (
3938 p_api_version_number => 1.0,
3939 p_return_status => l_return_status,
3940 p_flow_application_id => p_flow_application_id,
3941 p_flow_code => p_flow_code,
3942 p_foreign_key_name => p_foreign_key_name,
3943 p_from_page_appl_id => p_from_page_appl_id,
3944 p_from_page_code => p_from_page_code,
3945 p_from_region_appl_id => p_from_region_appl_id,
3946 p_from_region_code => p_from_region_code,
3947 p_to_page_appl_id => p_to_page_appl_id,
3948 p_to_page_code => p_to_page_code,
3949 p_to_region_appl_id => p_to_region_appl_id,
3950 p_to_region_code => p_to_region_code) then
3951 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3952 FND_MESSAGE.SET_NAME('AK','AK_RELATION_DOES_NOT_EXIST');
3953 FND_MSG_PUB.Add;
3954 end if;
3955 raise FND_API.G_EXC_ERROR;
3956 end if;
3957
3958 if (p_delete_cascade = 'N') then
3959 --
3960 -- If we are not deleting any referencing records, we cannot
3961 -- delete the region relation if it is being referenced in any of
3962 -- following tables.
3963 --
3964 -- AK_FLOW_PAGE_REGION_ITEMS
3965 --
3966 open l_get_items_csr;
3967 fetch l_get_items_csr into l_attribute_application_id, l_attribute_code;
3968 if l_get_items_csr%found then
3969 close l_get_items_csr;
3970 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3971 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_DEL_REF_REL_ITEM');
3972 FND_MSG_PUB.Add;
3973 end if;
3974 raise FND_API.G_EXC_ERROR;
3975 end if;
3976 close l_get_items_csr;
3977 else
3978 --
3979 -- Otherwise, delete all referencing rows in other tables
3980 --
3981 -- AK_REGION_TIEMS
3982 --
3983 open l_get_items_csr;
3984 loop
3985 fetch l_get_items_csr into l_attribute_application_id, l_attribute_code;
3986 exit when l_get_items_csr%notfound;
3987 AK_FLOW_PVT.DELETE_PAGE_REGION_ITEM (
3988 p_validation_level => p_validation_level,
3989 p_api_version_number => 1.0,
3990 p_msg_count => l_msg_count,
3991 p_msg_data => l_msg_data,
3992 p_return_status => l_return_status,
3993 p_flow_application_id => p_flow_application_id,
3994 p_flow_code => p_flow_code,
3995 p_page_application_id => p_from_page_appl_id,
3996 p_page_code => p_from_page_code,
3997 p_region_application_id => p_from_region_appl_id,
3998 p_region_code => p_from_region_code,
3999 p_attribute_application_id => l_attribute_application_id,
4000 p_attribute_code => l_attribute_code,
4001 p_delete_cascade => p_delete_cascade
4002 );
4003 if (l_return_status = FND_API.G_RET_STS_ERROR) or
4004 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
4005 close l_get_items_csr;
4006 raise FND_API.G_EXC_ERROR;
4007 end if;
4008 end loop;
4009 close l_get_items_csr;
4010 end if;
4011
4012 --
4013 -- delete region relation once we checked that there are no references
4014 -- to it, or all references have been deleted.
4015 --
4016 delete from ak_flow_region_relations
4017 where flow_application_id = p_flow_application_id
4018 and flow_code = p_flow_code
4019 and foreign_key_name = p_foreign_key_name
4020 and from_page_appl_id = p_from_page_appl_id
4021 and from_page_code = p_from_page_code
4022 and from_region_appl_id = p_from_region_appl_id
4023 and from_region_code = p_from_region_code
4024 and to_page_appl_id = p_to_page_appl_id
4025 and to_page_code = p_to_page_code
4026 and to_region_appl_id = p_to_region_appl_id
4027 and to_region_code = p_to_region_code;
4028
4032 FND_MSG_PUB.Add;
4029 if (sql%notfound) then
4030 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4031 FND_MESSAGE.SET_NAME('AK','AK_RELATION_DOES_NOT_EXIST');
4033 end if;
4034 raise FND_API.G_EXC_ERROR;
4035 end if;
4036
4037 --
4038 -- Load success message
4039 --
4040 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) then
4041 FND_MESSAGE.SET_NAME('AK','AK_RELATION_DELETED');
4042 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
4043 ' ' || p_flow_code ||
4044 ' ' || p_foreign_key_name ||
4045 ' ' || to_char(p_from_page_appl_id) ||
4046 ' ' || p_from_page_code ||
4047 ' ' || to_char(p_from_region_appl_id) ||
4048 ' ' || p_from_region_code ||
4049 ' ' || to_char(p_to_page_appl_id) ||
4050 ' ' || p_to_page_code ||
4051 ' ' || to_char(p_to_region_appl_id) ||
4052 ' ' || p_to_region_code);
4053 FND_MSG_PUB.Add;
4054 end if;
4055
4056 p_return_status := FND_API.G_RET_STS_SUCCESS;
4057
4058 FND_MSG_PUB.Count_And_Get (
4059 p_count => p_msg_count,
4060 p_data => p_msg_data);
4061
4062 EXCEPTION
4063 WHEN FND_API.G_EXC_ERROR THEN
4064 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4065 FND_MESSAGE.SET_NAME('AK','AK_RELATION_NOT_DELETED');
4066 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
4067 ' ' || p_flow_code ||
4068 ' ' || p_foreign_key_name ||
4069 ' ' || to_char(p_from_page_appl_id) ||
4070 ' ' || p_from_page_code ||
4071 ' ' || to_char(p_from_region_appl_id) ||
4072 ' ' || p_from_region_code ||
4073 ' ' || to_char(p_to_page_appl_id) ||
4074 ' ' || p_to_page_code ||
4075 ' ' || to_char(p_to_region_appl_id) ||
4076 ' ' || p_to_region_code);
4077 FND_MSG_PUB.Add;
4078 end if;
4079 p_return_status := FND_API.G_RET_STS_ERROR;
4080 rollback to start_delete_relation;
4081 FND_MSG_PUB.Count_And_Get (
4082 p_count => p_msg_count,
4083 p_data => p_msg_data);
4084 WHEN OTHERS THEN
4085 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4086 rollback to start_delete_relation;
4087 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4088 SUBSTR (SQLERRM, 1, 240) );
4089 FND_MSG_PUB.Add;
4090 FND_MSG_PUB.Count_And_Get (
4091 p_count => p_msg_count,
4092 p_data => p_msg_data);
4093 end DELETE_REGION_RELATION;
4094
4095 --=======================================================
4096 -- Procedure UPDATE_FLOW
4097 --
4098 -- Usage Private API for updating a flow.
4099 -- This API should only be called by other APIs
4100 -- that are owned by the Core Modules Team (AK).
4101 --
4102 -- Desc This API updates a flow using the given info
4103 --
4104 -- Results The API returns the standard p_return_status parameter
4105 -- indicating one of the standard return statuses :
4106 -- * Unexpected error
4107 -- * Error
4108 -- * Success
4109 -- Parameters Flow columns
4110 -- p_loader_timestamp : IN optional
4111 -- If a timestamp is passed, the API will update the
4112 -- record using this timestamp. Only the upload API
4113 -- should call with this parameter loaded.
4114 --
4115 -- Version Initial version number = 1.0
4116 -- History Current version number = 1.0
4117 --=======================================================
4118 procedure UPDATE_FLOW (
4119 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4120 p_api_version_number IN NUMBER,
4121 p_init_msg_tbl IN BOOLEAN := FALSE,
4122 p_msg_count OUT NOCOPY NUMBER,
4123 p_msg_data OUT NOCOPY VARCHAR2,
4124 p_return_status OUT NOCOPY VARCHAR2,
4125 p_flow_application_id IN NUMBER,
4126 p_flow_code IN VARCHAR2,
4127 p_primary_page_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4128 p_primary_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4129 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
4130 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4131 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4132 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4133 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4134 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4135 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4136 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4137 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4138 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4139 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4140 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4141 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4142 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4143 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4144 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4145 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4146 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
4147 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4151 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4148 p_creation_date IN DATE := FND_API.G_MISS_DATE,
4149 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4150 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4152 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4153 p_pass IN NUMBER,
4154 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4155 ) is
4156 cursor l_get_row_csr is
4157 select *
4158 from AK_FLOWS
4159 where FLOW_APPLICATION_ID = p_flow_application_id
4160 and FLOW_CODE = p_flow_code
4161 for update of primary_page_appl_id;
4162 cursor l_get_tl_row_csr (lang_parm varchar2) is
4163 select *
4164 from AK_FLOWS_TL
4165 where FLOW_APPLICATION_ID = p_flow_application_id
4166 and FLOW_CODE = p_flow_code
4167 and LANGUAGE = lang_parm
4168 for update of name;
4169 l_api_version_number CONSTANT number := 1.0;
4170 l_api_name CONSTANT varchar2(30) := 'Update_Flow';
4171 l_created_by number;
4172 l_creation_date date;
4173 l_flows_rec ak_flows%ROWTYPE;
4174 l_flows_tl_rec ak_flows_tl%ROWTYPE;
4175 l_error boolean;
4176 l_lang varchar2(30);
4177 l_last_update_date date;
4178 l_last_update_login number;
4179 l_last_updated_by number;
4180 l_return_status varchar2(1);
4181 l_file_version number;
4182 begin
4183 IF NOT FND_API.Compatible_API_Call (
4184 l_api_version_number, p_api_version_number, l_api_name,
4185 G_PKG_NAME) then
4186 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4187 return;
4188 END IF;
4189
4190 -- Initialize the message table if requested.
4191
4192 if p_init_msg_tbl then
4193 FND_MSG_PUB.initialize;
4194 end if;
4195
4196 savepoint start_update_flow;
4197
4198 select userenv('LANG') into l_lang
4199 from dual;
4200
4201 --
4202 -- retrieve ak_flows row if it exists
4203 --
4204 open l_get_row_csr;
4205 fetch l_get_row_csr into l_flows_rec;
4206 if (l_get_row_csr%notfound) then
4207 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4208 FND_MESSAGE.SET_NAME('AK','AK_FLOW_DOES_NOT_EXIST');
4209 FND_MSG_PUB.Add;
4210 end if;
4211 close l_get_row_csr;
4212 raise FND_API.G_EXC_ERROR;
4213 end if;
4214 close l_get_row_csr;
4215
4216 --
4217 -- retrieve ak_flows_tl row if it exists
4218 --
4219 open l_get_tl_row_csr(l_lang);
4220 fetch l_get_tl_row_csr into l_flows_tl_rec;
4221 if (l_get_tl_row_csr%notfound) then
4222 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4223 FND_MESSAGE.SET_NAME('AK','AK_FLOW_DOES_NOT_EXIST');
4224 FND_MSG_PUB.Add;
4225 end if;
4226 close l_get_tl_row_csr;
4227 raise FND_API.G_EXC_ERROR;
4228 end if;
4229 close l_get_tl_row_csr;
4230
4231 --
4232 -- validate table columns passed in
4233 --
4234 -- (A null primary page code means that the user wants to invalidate
4235 -- the primary page selection. It should be updated with
4236 -- G_NO_PRIMARY_PAGE_CODE)
4237 --
4238 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4239 if not AK_FLOW3_PVT.VALIDATE_FLOW (
4240 p_validation_level => p_validation_level,
4241 p_api_version_number => 1.0,
4242 p_return_status => l_return_status,
4243 p_flow_application_id => p_flow_application_id,
4244 p_flow_code => p_flow_code,
4245 p_primary_page_appl_id =>
4246 nvl(p_primary_page_appl_id,p_flow_application_id),
4247 p_primary_page_code =>
4248 nvl(p_primary_page_code,G_NO_PRIMARY_PAGE_CODE),
4249 p_name => p_name,
4250 p_description => p_description,
4251 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
4252 p_pass => p_pass
4253 ) then
4254 -- Do not raise an error if it's the first pass
4255 if (p_pass = 1) then
4256 p_copy_redo_flag := TRUE;
4257 else
4258 raise FND_API.G_EXC_ERROR;
4259 end if;
4260 end if;
4261 end if;
4262
4263 --** Load record to be updated to the database **
4264 --** - first load nullable columns **
4265 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
4266 (p_attribute_category is null) then
4267 l_flows_rec.attribute_category := p_attribute_category;
4268 end if;
4269 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
4270 (p_attribute1 is null) then
4271 l_flows_rec.attribute1 := p_attribute1;
4272 end if;
4273 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
4274 (p_attribute2 is null) then
4275 l_flows_rec.attribute2 := p_attribute2;
4276 end if;
4277 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
4278 (p_attribute3 is null) then
4279 l_flows_rec.attribute3 := p_attribute3;
4280 end if;
4281 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
4282 (p_attribute4 is null) then
4283 l_flows_rec.attribute4 := p_attribute4;
4284 end if;
4285 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
4286 (p_attribute5 is null) then
4287 l_flows_rec.attribute5 := p_attribute5;
4288 end if;
4289 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
4290 (p_attribute6 is null) then
4291 l_flows_rec.attribute6 := p_attribute6;
4292 end if;
4293 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
4294 (p_attribute7 is null) then
4295 l_flows_rec.attribute7 := p_attribute7;
4296 end if;
4297 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
4301 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
4298 (p_attribute8 is null) then
4299 l_flows_rec.attribute8 := p_attribute8;
4300 end if;
4302 (p_attribute9 is null) then
4303 l_flows_rec.attribute9 := p_attribute9;
4304 end if;
4305 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
4306 (p_attribute10 is null) then
4307 l_flows_rec.attribute10 := p_attribute10;
4308 end if;
4309 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
4310 (p_attribute11 is null) then
4311 l_flows_rec.attribute11 := p_attribute11;
4312 end if;
4313 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
4314 (p_attribute12 is null) then
4315 l_flows_rec.attribute12 := p_attribute12;
4316 end if;
4317 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
4318 (p_attribute13 is null) then
4319 l_flows_rec.attribute13 := p_attribute13;
4320 end if;
4321 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
4322 (p_attribute14 is null) then
4323 l_flows_rec.attribute14 := p_attribute14;
4324 end if;
4325 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
4326 (p_attribute15 is null) then
4327 l_flows_rec.attribute15 := p_attribute15;
4328 end if;
4329
4330 if (p_description <> FND_API.G_MISS_CHAR) or
4331 (p_description is null) then
4332 l_flows_tl_rec.description := p_description;
4333 end if;
4334
4335 --** - next, load non-null columns **
4336
4337 -- primary page code should be loaded with G_NO_PRIMARY_PAGE_CODE
4338 -- if user wants to invalidate the primary page selection
4339 --
4340 if (p_primary_page_code <> FND_API.G_MISS_CHAR) then
4341 l_flows_rec.primary_page_code := p_primary_page_code;
4342 elsif (p_primary_page_code is null) then
4343 l_flows_rec.primary_page_code := G_NO_PRIMARY_PAGE_CODE;
4344 end if;
4345
4346 if (p_primary_page_appl_id <> FND_API.G_MISS_NUM) then
4347 l_flows_rec.primary_page_appl_id := p_primary_page_appl_id;
4348 end if;
4349 if (p_name <> FND_API.G_MISS_CHAR) then
4350 l_flows_tl_rec.name := p_name;
4351 end if;
4352
4353 if (p_created_by <> FND_API.G_MISS_NUM) then
4354 l_created_by := p_created_by;
4355 end if;
4356 if (p_creation_date <> FND_API.G_MISS_DATE) then
4357 l_creation_date := p_creation_date;
4358 end if;
4359 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4360 l_last_updated_by := p_last_updated_by;
4361 end if;
4362 if (p_last_update_date <> FND_API.G_MISS_DATE) then
4363 l_last_update_date := p_last_update_date;
4364 end if;
4365 if (p_last_update_login <> FND_API.G_MISS_NUM) then
4366 l_last_update_login := p_last_update_login;
4367 end if;
4368
4369 -- Set WHO columns
4370 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
4371 p_loader_timestamp => p_loader_timestamp,
4372 p_created_by => l_created_by,
4373 p_creation_date => l_creation_date,
4374 p_last_updated_by => l_last_updated_by,
4375 p_db_last_updated_by => l_flows_rec.last_updated_by,
4376 p_last_update_date => l_last_update_date,
4377 p_db_last_update_date => l_flows_rec.last_update_date,
4378 p_last_update_login => l_last_update_login,
4379 p_create_or_update => 'UPDATE') then
4380
4381 update AK_FLOWS set
4382 PRIMARY_PAGE_APPL_ID = l_flows_rec.primary_page_appl_id,
4383 PRIMARY_PAGE_CODE = l_flows_rec.primary_page_code,
4384 ATTRIBUTE_CATEGORY = l_flows_rec.attribute_category,
4385 ATTRIBUTE1 = l_flows_rec.attribute1,
4386 ATTRIBUTE2 = l_flows_rec.attribute2,
4387 ATTRIBUTE3 = l_flows_rec.attribute3,
4388 ATTRIBUTE4 = l_flows_rec.attribute4,
4389 ATTRIBUTE5 = l_flows_rec.attribute5,
4390 ATTRIBUTE6 = l_flows_rec.attribute6,
4391 ATTRIBUTE7 = l_flows_rec.attribute7,
4392 ATTRIBUTE8 = l_flows_rec.attribute8,
4393 ATTRIBUTE9 = l_flows_rec.attribute9,
4394 ATTRIBUTE10 = l_flows_rec.attribute10,
4395 ATTRIBUTE11 = l_flows_rec.attribute11,
4396 ATTRIBUTE12 = l_flows_rec.attribute12,
4397 ATTRIBUTE13 = l_flows_rec.attribute13,
4398 ATTRIBUTE14 = l_flows_rec.attribute14,
4399 ATTRIBUTE15 = l_flows_rec.attribute15,
4400 LAST_UPDATE_DATE = l_last_update_date,
4401 LAST_UPDATED_BY = l_last_updated_by,
4402 LAST_UPDATE_LOGIN = l_last_update_login
4403 where FLOW_APPLICATION_ID = p_flow_application_id
4404 and FLOW_CODE = p_flow_code;
4405 if (sql%notfound) then
4406 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4407 FND_MESSAGE.SET_NAME('AK','AK_FLOW_UPDATE_FAILED');
4408 FND_MSG_PUB.Add;
4409 end if;
4410 raise FND_API.G_EXC_ERROR;
4411 end if;
4412
4413 update AK_FLOWS_TL set
4414 NAME = l_flows_tl_rec.name,
4415 DESCRIPTION = l_flows_tl_rec.description,
4416 LAST_UPDATED_BY = l_last_updated_by,
4417 LAST_UPDATE_DATE = l_last_update_date,
4418 LAST_UPDATE_LOGIN = l_last_update_login,
4419 SOURCE_LANG = l_lang
4420 where FLOW_APPLICATION_ID = p_flow_application_id
4421 and FLOW_CODE = p_flow_code
4422 and l_lang in (LANGUAGE, SOURCE_LANG);
4423 if (sql%notfound) then
4424 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4425 FND_MESSAGE.SET_NAME('AK','AK_FLOW_UPDATE_FAILED');
4426 FND_MSG_PUB.Add;
4427 end if;
4428 raise FND_API.G_EXC_ERROR;
4429 end if;
4430
4431 -- /** commit the update **/
4432 -- commit;
4433
4434 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
4435 FND_MESSAGE.SET_NAME('AK','AK_FLOW_UPDATED');
4436 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
4437 ' ' || p_flow_code);
4438 FND_MSG_PUB.Add;
4439 end if;
4440
4441 end if;
4445 p_count => p_msg_count,
4442 p_return_status := FND_API.G_RET_STS_SUCCESS;
4443
4444 FND_MSG_PUB.Count_And_Get (
4446 p_data => p_msg_data);
4447
4448 EXCEPTION
4449 WHEN VALUE_ERROR THEN
4450 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4451 FND_MESSAGE.SET_NAME('AK','AK_FLOW_VALUE_ERROR');
4452 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
4453 ' ' || p_flow_code);
4454 FND_MSG_PUB.Add;
4455 end if;
4456 rollback to start_update_flow;
4457 p_return_status := FND_API.G_RET_STS_ERROR;
4458 FND_MSG_PUB.Count_And_Get (
4459 p_count => p_msg_count,
4460 p_data => p_msg_data);
4461 WHEN FND_API.G_EXC_ERROR THEN
4462 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4463 FND_MESSAGE.SET_NAME('AK','AK_FLOW_NOT_UPDATED');
4464 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
4465 ' ' || p_flow_code);
4466 FND_MSG_PUB.Add;
4467 end if;
4468 p_return_status := FND_API.G_RET_STS_ERROR;
4469 rollback to start_update_flow;
4470 FND_MSG_PUB.Count_And_Get (
4471 p_count => p_msg_count,
4472 p_data => p_msg_data);
4473 WHEN OTHERS THEN
4474 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4475 rollback to start_update_flow;
4476 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4477 SUBSTR (SQLERRM, 1, 240) );
4478 FND_MSG_PUB.Add;
4479 FND_MSG_PUB.Count_And_Get (
4480 p_count => p_msg_count,
4481 p_data => p_msg_data);
4482 end UPDATE_FLOW;
4483
4484 --=======================================================
4485 -- Procedure UPDATE_PAGE
4486 --
4487 -- Usage Private API for updating a flow page.
4488 -- This API should only be called by other APIs
4489 -- that are owned by the Core Modules Team (AK).
4490 --
4491 -- Desc This API updates a flow page using the given info
4492 --
4493 -- Results The API returns the standard p_return_status parameter
4494 -- indicating one of the standard return statuses :
4495 -- * Unexpected error
4496 -- * Error
4497 -- * Success
4498 -- Parameters Flow Page columns
4499 -- p_set_primary_page : IN optional
4500 -- Set the current page as the primary page of
4501 -- the flow if this flag is 'Y'.
4502 -- p_loader_timestamp : IN optional
4503 -- If a timestamp is passed, the API will update the
4504 -- record using this timestamp. Only the upload API
4505 -- should call with this parameter loaded.
4506 --
4507 -- Version Initial version number = 1.0
4508 -- History Current version number = 1.0
4509 --=======================================================
4510 procedure UPDATE_PAGE (
4511 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4512 p_api_version_number IN NUMBER,
4513 p_init_msg_tbl IN BOOLEAN := FALSE,
4514 p_msg_count OUT NOCOPY NUMBER,
4515 p_msg_data OUT NOCOPY VARCHAR2,
4516 p_return_status OUT NOCOPY VARCHAR2,
4517 p_flow_application_id IN NUMBER,
4518 p_flow_code IN VARCHAR2,
4519 p_page_application_id IN NUMBER,
4520 p_page_code IN VARCHAR2,
4521 p_primary_region_appl_id IN NUMBER := FND_API.G_MISS_NUM,
4522 p_primary_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4523 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
4524 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4525 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4526 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4527 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4528 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4529 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4530 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4531 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4532 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4533 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4534 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4535 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4536 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4537 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4538 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4539 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4540 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
4541 p_set_primary_page IN VARCHAR2 := FND_API.G_MISS_CHAR,
4542 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4543 p_creation_date IN DATE := FND_API.G_MISS_DATE,
4544 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4545 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4546 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4547 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4548 p_pass IN NUMBER,
4549 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4550 ) is
4551 cursor l_get_row_csr is
4552 select *
4556 and page_application_id = p_page_application_id
4553 from AK_FLOW_PAGES
4554 where FLOW_APPLICATION_ID = p_flow_application_id
4555 and FLOW_CODE = p_flow_code
4557 and page_code = p_page_code
4558 for update of primary_region_appl_id;
4559 cursor l_get_tl_row_csr (lang_parm varchar2) is
4560 select *
4561 from AK_FLOW_PAGES_TL
4562 where FLOW_APPLICATION_ID = p_flow_application_id
4563 and FLOW_CODE = p_flow_code
4564 and page_application_id = p_page_application_id
4565 and page_code = p_page_code
4566 and LANGUAGE = lang_parm
4567 for update of name;
4568 l_api_version_number CONSTANT number := 1.0;
4569 l_api_name CONSTANT varchar2(30) := 'Update_Page';
4570 l_created_by number;
4571 l_creation_date date;
4572 l_msg_count NUMBER;
4573 l_msg_data VARCHAR2(2000);
4574 l_error boolean;
4575 l_lang varchar2(30);
4576 l_last_update_date date;
4577 l_last_update_login number;
4578 l_last_updated_by number;
4579 l_pages_rec ak_flow_pages%ROWTYPE;
4580 l_pages_tl_rec ak_flow_pages_tl%ROWTYPE;
4581 l_return_status varchar2(1);
4582 l_file_version number;
4583 begin
4584 IF NOT FND_API.Compatible_API_Call (
4585 l_api_version_number, p_api_version_number, l_api_name,
4586 G_PKG_NAME) then
4587 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4588 return;
4589 END IF;
4590
4591 -- Initialize the message table if requested.
4592
4593 if p_init_msg_tbl then
4594 FND_MSG_PUB.initialize;
4595 end if;
4596
4597 savepoint start_update_page;
4598
4599 select userenv('LANG') into l_lang
4600 from dual;
4601
4602 --
4603 -- retrieve ak_flow_pages row if it exists
4604 --
4605 open l_get_row_csr;
4606 fetch l_get_row_csr into l_pages_rec;
4607 if (l_get_row_csr%notfound) then
4608 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4609 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_DOES_NOT_EXIST');
4610 FND_MSG_PUB.Add;
4611 end if;
4612 close l_get_row_csr;
4613 raise FND_API.G_EXC_ERROR;
4614 end if;
4615 close l_get_row_csr;
4616
4617 --
4618 -- retrieve ak_pages_tl row if it exists
4619 --
4620 open l_get_tl_row_csr(l_lang);
4621 fetch l_get_tl_row_csr into l_pages_tl_rec;
4622 if (l_get_tl_row_csr%notfound) then
4623 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4624 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_DOES_NOT_EXIST');
4625 FND_MSG_PUB.Add;
4626 end if;
4627 close l_get_tl_row_csr;
4628 raise FND_API.G_EXC_ERROR;
4629 end if;
4630 close l_get_tl_row_csr;
4631
4632 --
4633 -- validate table columns passed in
4634 --
4635 -- (A null primary region code means that the user wants to invalidate
4636 -- the primary region selection. It should be updated with
4637 -- G_NO_PRIMARY_REGION_CODE)
4638 --
4639 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4640 if not AK_FLOW3_PVT.VALIDATE_PAGE (
4641 p_validation_level => p_validation_level,
4642 p_api_version_number => 1.0,
4643 p_return_status => l_return_status,
4644 p_flow_application_id => p_flow_application_id,
4645 p_flow_code => p_flow_code,
4646 p_page_application_id => p_page_application_id,
4647 p_page_code => p_page_code,
4648 p_primary_region_appl_id =>
4649 nvl(p_primary_region_appl_id,p_flow_application_id),
4650 p_primary_region_code =>
4651 nvl(p_primary_region_code,G_NO_PRIMARY_REGION_CODE),
4652 p_name => p_name,
4653 p_description => p_description,
4654 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
4655 p_pass => p_pass
4656 ) then
4657 -- Do not raise an error if it's the first pass
4658 if (p_pass = 1) then
4659 p_copy_redo_flag := TRUE;
4660 else
4661 raise FND_API.G_EXC_ERROR;
4662 end if;
4663 end if;
4664 end if;
4665
4666 --** Load record to be updated to the database **
4667 --** - first load nullable columns **
4668
4669 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
4670 (p_attribute_category is null) then
4671 l_pages_rec.attribute_category := p_attribute_category;
4672 end if;
4673 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
4674 (p_attribute1 is null) then
4675 l_pages_rec.attribute1 := p_attribute1;
4676 end if;
4677 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
4678 (p_attribute2 is null) then
4679 l_pages_rec.attribute2 := p_attribute2;
4680 end if;
4681 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
4682 (p_attribute3 is null) then
4683 l_pages_rec.attribute3 := p_attribute3;
4684 end if;
4685 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
4686 (p_attribute4 is null) then
4687 l_pages_rec.attribute4 := p_attribute4;
4688 end if;
4689 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
4690 (p_attribute5 is null) then
4691 l_pages_rec.attribute5 := p_attribute5;
4692 end if;
4693 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
4694 (p_attribute6 is null) then
4695 l_pages_rec.attribute6 := p_attribute6;
4696 end if;
4697 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
4698 (p_attribute7 is null) then
4699 l_pages_rec.attribute7 := p_attribute7;
4700 end if;
4701 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
4702 (p_attribute8 is null) then
4703 l_pages_rec.attribute8 := p_attribute8;
4704 end if;
4705 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
4709 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
4706 (p_attribute9 is null) then
4707 l_pages_rec.attribute9 := p_attribute9;
4708 end if;
4710 (p_attribute10 is null) then
4711 l_pages_rec.attribute10 := p_attribute10;
4712 end if;
4713 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
4714 (p_attribute11 is null) then
4715 l_pages_rec.attribute11 := p_attribute11;
4716 end if;
4717 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
4718 (p_attribute12 is null) then
4719 l_pages_rec.attribute12 := p_attribute12;
4720 end if;
4721 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
4722 (p_attribute13 is null) then
4723 l_pages_rec.attribute13 := p_attribute13;
4724 end if;
4725 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
4726 (p_attribute14 is null) then
4727 l_pages_rec.attribute14 := p_attribute14;
4728 end if;
4729 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
4730 (p_attribute15 is null) then
4731 l_pages_rec.attribute15 := p_attribute15;
4732 end if;
4733 if (p_description <> FND_API.G_MISS_CHAR) or
4734 (p_description is null) then
4735 l_pages_tl_rec.description := p_description;
4736 end if;
4737
4738 --** - next, load non-null columns **
4739
4740 -- primary region code should be loaded with G_NO_PRIMARY_REGION_CODE
4741 -- if user wants to invalidate the primary region selection
4742 --
4743 if (p_primary_region_code <> FND_API.G_MISS_CHAR) then
4744 l_pages_rec.primary_region_code := p_primary_region_code;
4745 elsif (p_primary_region_code is null) then
4746 l_pages_rec.primary_region_code := G_NO_PRIMARY_REGION_CODE;
4747 end if;
4748
4749 if (p_primary_region_appl_id <> FND_API.G_MISS_NUM) then
4750 l_pages_rec.primary_region_appl_id := p_primary_region_appl_id;
4751 end if;
4752 if (p_name <> FND_API.G_MISS_CHAR) then
4753 l_pages_tl_rec.name := p_name;
4754 end if;
4755
4756 if (p_created_by <> FND_API.G_MISS_NUM) then
4757 l_created_by := p_created_by;
4758 end if;
4759 if (p_creation_date <> FND_API.G_MISS_DATE) then
4760 l_creation_date := p_creation_date;
4761 end if;
4762 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4763 l_last_updated_by := p_last_updated_by;
4764 end if;
4765 if (p_last_update_date <> FND_API.G_MISS_DATE) then
4766 l_last_update_date := p_last_update_date;
4767 end if;
4768 if (p_last_update_login <> FND_API.G_MISS_NUM) then
4769 l_last_update_login := p_last_update_login;
4770 end if;
4771
4772 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
4773 p_loader_timestamp => p_loader_timestamp,
4774 p_created_by => l_created_by,
4775 p_creation_date => l_creation_date,
4776 p_last_updated_by => l_last_updated_by,
4777 p_db_last_updated_by => l_pages_rec.last_updated_by,
4778 p_last_update_date => l_last_update_date,
4779 p_db_last_update_date => l_pages_rec.last_update_date,
4780 p_last_update_login => l_last_update_login,
4781 p_create_or_update => 'UPDATE') then
4782
4783 update AK_FLOW_PAGES set
4784 PRIMARY_REGION_APPL_ID = l_pages_rec.primary_region_appl_id,
4785 PRIMARY_REGION_CODE = l_pages_rec.primary_region_code,
4786 ATTRIBUTE_CATEGORY = l_pages_rec.attribute_category,
4787 ATTRIBUTE1 = l_pages_rec.attribute1,
4788 ATTRIBUTE2 = l_pages_rec.attribute2,
4789 ATTRIBUTE3 = l_pages_rec.attribute3,
4790 ATTRIBUTE4 = l_pages_rec.attribute4,
4791 ATTRIBUTE5 = l_pages_rec.attribute5,
4792 ATTRIBUTE6 = l_pages_rec.attribute6,
4793 ATTRIBUTE7 = l_pages_rec.attribute7,
4794 ATTRIBUTE8 = l_pages_rec.attribute8,
4795 ATTRIBUTE9 = l_pages_rec.attribute9,
4796 ATTRIBUTE10 = l_pages_rec.attribute10,
4797 ATTRIBUTE11 = l_pages_rec.attribute11,
4798 ATTRIBUTE12 = l_pages_rec.attribute12,
4799 ATTRIBUTE13 = l_pages_rec.attribute13,
4800 ATTRIBUTE14 = l_pages_rec.attribute14,
4801 ATTRIBUTE15 = l_pages_rec.attribute15,
4802 LAST_UPDATE_DATE = l_last_update_date,
4803 LAST_UPDATED_BY = l_last_updated_by,
4804 LAST_UPDATE_LOGIN = l_last_update_login
4805 where FLOW_APPLICATION_ID = p_flow_application_id
4806 and FLOW_CODE = p_flow_code
4807 and PAGE_APPLICATION_ID = p_page_application_id
4808 and PAGE_CODE = p_page_code;
4809 if (sql%notfound) then
4810 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4811 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_UPDATE_FAILED');
4812 FND_MSG_PUB.Add;
4813 end if;
4814 raise FND_API.G_EXC_ERROR;
4815 end if;
4816
4817 update AK_FLOW_PAGES_TL set
4818 NAME = l_pages_tl_rec.name,
4819 DESCRIPTION = l_pages_tl_rec.description,
4820 LAST_UPDATED_BY = l_last_updated_by,
4821 LAST_UPDATE_DATE = l_last_update_date,
4822 LAST_UPDATE_LOGIN = l_last_update_login,
4823 SOURCE_LANG = l_lang
4824 where FLOW_APPLICATION_ID = p_flow_application_id
4825 and FLOW_CODE = p_flow_code
4826 and PAGE_APPLICATION_ID = p_page_application_id
4827 and PAGE_CODE = p_page_code
4828 and l_lang in (LANGUAGE, SOURCE_LANG);
4829 if (sql%notfound) then
4830 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4831 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_UPDATE_FAILED');
4832 FND_MSG_PUB.Add;
4833 end if;
4834 raise FND_API.G_EXC_ERROR;
4835 end if;
4836
4837 -- /** commit the update **/
4838 -- commit;
4839
4840 --
4841 -- Set current page as the primary page of the flow if
4842 -- p_set_primary_page is 'Y'.
4843 --
4844 if (p_set_primary_page = 'Y') then
4845 AK_FLOW_PVT.UPDATE_FLOW (
4849 p_msg_data => l_msg_data,
4846 p_validation_level => p_validation_level,
4847 p_api_version_number => 1.0,
4848 p_msg_count => l_msg_count,
4850 p_return_status => l_return_status,
4851 p_flow_application_id => p_flow_application_id,
4852 p_flow_code => p_flow_code,
4853 p_primary_page_appl_id => p_page_application_id,
4854 p_primary_page_code => p_page_code,
4855 p_created_by => l_created_by,
4856 p_creation_date => l_creation_date,
4857 p_last_updated_by => l_last_updated_by,
4858 p_last_update_date => l_last_update_date,
4859 p_last_update_login => l_last_update_login,
4860 p_pass => p_pass,
4861 p_copy_redo_flag => p_copy_redo_flag);
4862 if (l_return_status = FND_API.G_RET_STS_ERROR) or
4863 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
4864 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4865 raise FND_API.G_EXC_ERROR;
4866 end if;
4867 end if;
4868 end if;
4869
4870 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
4871 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_UPDATED');
4872 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
4873 ' ' || p_flow_code ||
4874 ' ' || to_char(p_page_application_id) ||
4875 ' ' || p_page_code);
4876 FND_MSG_PUB.Add;
4877 end if;
4878
4879 end if;
4880 p_return_status := FND_API.G_RET_STS_SUCCESS;
4881
4882 FND_MSG_PUB.Count_And_Get (
4883 p_count => p_msg_count,
4884 p_data => p_msg_data);
4885
4886 EXCEPTION
4887 WHEN VALUE_ERROR THEN
4888 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4889 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_VALUE_ERROR');
4890 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
4891 ' ' || p_flow_code ||
4892 ' ' || to_char(p_page_application_id) ||
4893 ' ' || p_page_code);
4894 FND_MSG_PUB.Add;
4895 end if;
4896 rollback to start_update_page;
4897 p_return_status := FND_API.G_RET_STS_ERROR;
4898 FND_MSG_PUB.Count_And_Get (
4899 p_count => p_msg_count,
4900 p_data => p_msg_data);
4901 WHEN FND_API.G_EXC_ERROR THEN
4902 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4903 FND_MESSAGE.SET_NAME('AK','AK_FLOW_PAGE_NOT_UPDATED');
4904 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
4905 ' ' || p_flow_code ||
4906 ' ' || to_char(p_page_application_id) ||
4907 ' ' || p_page_code);
4908 FND_MSG_PUB.Add;
4909 end if;
4910 p_return_status := FND_API.G_RET_STS_ERROR;
4911 rollback to start_update_page;
4912 FND_MSG_PUB.Count_And_Get (
4913 p_count => p_msg_count,
4914 p_data => p_msg_data);
4915 WHEN OTHERS THEN
4916 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4917 rollback to start_update_page;
4918 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4919 SUBSTR (SQLERRM, 1, 240) );
4920 FND_MSG_PUB.Add;
4921 FND_MSG_PUB.Count_And_Get (
4922 p_count => p_msg_count,
4923 p_data => p_msg_data);
4924 end UPDATE_PAGE;
4925
4926 --=======================================================
4927 -- Procedure UPDATE_PAGE_REGION
4928 --
4929 -- Usage Private API for updating a flow page region.
4930 -- This API should only be called by other APIs
4931 -- that are owned by the Core Modules Team (AK).
4932 --
4933 -- Desc This API updates a flow page region using the given info
4934 --
4935 -- Results The API returns the standard p_return_status parameter
4936 -- indicating one of the standard return statuses :
4937 -- * Unexpected error
4938 -- * Error
4939 -- * Success
4940 -- Parameters Flow Page Region columns
4941 -- p_foreign_key_name : IN optional
4942 -- If a foreign key name is passed, and that this page
4943 -- region has a parent region, then this API will
4944 -- create an intrapage flow region relation connecting
4945 -- this page region with the parent region using the
4946 -- foreign key name. If there is already an intrapage
4947 -- flow region relation exists connecting these two
4948 -- page regions, it will be replaced by a new one using
4949 -- this foreign key.
4950 -- p_set_primary_region : IN optional
4951 -- Set the current page region as the primary region of
4952 -- the flow page if this flag is 'Y'.
4953 -- p_loader_timestamp : IN optional
4954 -- If a timestamp is passed, the API will update the
4955 -- record using this timestamp. Only the upload API
4956 -- should call with this parameter loaded.
4957 --
4958 -- Version Initial version number = 1.0
4959 -- History Current version number = 1.0
4960 --=======================================================
4961 procedure UPDATE_PAGE_REGION (
4962 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4963 p_api_version_number IN NUMBER,
4964 p_init_msg_tbl IN BOOLEAN := FALSE,
4965 p_msg_count OUT NOCOPY NUMBER,
4966 p_msg_data OUT NOCOPY VARCHAR2,
4967 p_return_status OUT NOCOPY VARCHAR2,
4968 p_flow_application_id IN NUMBER,
4969 p_flow_code IN VARCHAR2,
4970 p_page_application_id IN NUMBER,
4974 p_display_sequence IN NUMBER := FND_API.G_MISS_NUM,
4971 p_page_code IN VARCHAR2,
4972 p_region_application_id IN NUMBER,
4973 p_region_code IN VARCHAR2,
4975 p_region_style IN VARCHAR2 := FND_API.G_MISS_CHAR,
4976 p_num_columns IN NUMBER := FND_API.G_MISS_NUM,
4977 p_icx_custom_call IN VARCHAR2 := FND_API.G_MISS_CHAR,
4978 p_parent_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
4979 p_parent_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4980 p_foreign_key_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4981 p_set_primary_region IN VARCHAR2 := FND_API.G_MISS_CHAR,
4982 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
4983 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4984 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4985 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4986 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4987 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4988 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4989 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4990 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4991 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4992 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4993 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4994 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4995 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4996 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4997 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
4998 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4999 p_creation_date IN DATE := FND_API.G_MISS_DATE,
5000 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5001 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5002 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5003 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5004 p_pass IN NUMBER,
5005 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5006 ) is
5007 cursor l_get_row_csr is
5008 select *
5009 from AK_FLOW_PAGE_REGIONS
5010 where FLOW_APPLICATION_ID = p_flow_application_id
5011 and FLOW_CODE = p_flow_code
5012 and page_application_id = p_page_application_id
5013 and page_code = p_page_code
5014 and region_application_id = p_region_application_id
5015 and region_code = p_region_code
5016 for update of display_sequence;
5017 cursor l_get_old_fk_csr (parent_region_appl_id_param NUMBER,
5018 parent_region_code_param varchar2) is
5019 select foreign_key_name
5020 from AK_FLOW_REGION_RELATIONS
5021 where flow_application_id = p_flow_application_id
5022 and flow_code = p_flow_code
5023 and from_page_appl_id = p_page_application_id
5024 and from_page_code = p_page_code
5025 and from_region_appl_id = parent_region_appl_id_param
5026 and from_region_code = parent_region_code_param
5027 and to_page_appl_id = p_page_application_id
5028 and to_page_code = p_page_code
5029 and to_region_appl_id = p_region_application_id
5030 and to_region_code = p_region_code;
5031 l_api_version_number CONSTANT number := 1.0;
5032 l_api_name CONSTANT varchar2(30) := 'Update_Page_Region';
5033 l_dummy CONSTANT varchar2(30) := 'DUM_OLD_KEY_ZZZ';
5034 l_created_by number;
5035 l_creation_date date;
5036 l_foreign_key_name_old VARCHAR2(30);
5037 l_msg_count NUMBER;
5038 l_msg_data VARCHAR2(2000);
5039 l_error boolean;
5040 l_lang varchar2(30);
5041 l_last_update_date date;
5042 l_last_update_login number;
5043 l_last_updated_by number;
5044 l_parent_region_changed varchar2(1);
5045 l_regions_rec ak_flow_page_regions%ROWTYPE;
5046 l_return_status varchar2(1);
5047 l_file_version number;
5048 begin
5049 IF NOT FND_API.Compatible_API_Call (
5050 l_api_version_number, p_api_version_number, l_api_name,
5051 G_PKG_NAME) then
5052 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5053 return;
5054 END IF;
5055
5056 -- Initialize the message table if requested.
5057
5058 if p_init_msg_tbl then
5059 FND_MSG_PUB.initialize;
5060 end if;
5061
5062 savepoint start_update_page_region;
5063
5064 select userenv('LANG') into l_lang
5065 from dual;
5066
5067 --
5068 -- retrieve ak_flow_page_regions row if it exists
5069 --
5070 open l_get_row_csr;
5071 fetch l_get_row_csr into l_regions_rec;
5072 if (l_get_row_csr%notfound) then
5073 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
5074 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_DOES_NOT_EXIST');
5075 FND_MSG_PUB.Add;
5076 end if;
5077 close l_get_row_csr;
5078 raise FND_API.G_EXC_ERROR;
5079 end if;
5080 close l_get_row_csr;
5081
5082 if (p_display_sequence IS NOT NULL) and
5083 (p_display_sequence <> FND_API.G_MISS_NUM) then
5084 --** Check the given display sequence number
5085 CHECK_DISPLAY_SEQUENCE ( p_validation_level => p_validation_level,
5086 p_flow_code => p_flow_code,
5090 p_region_code => p_region_code,
5087 p_flow_application_id => p_flow_application_id,
5088 p_page_code => p_page_code,
5089 p_page_application_id => p_page_application_id,
5091 p_region_application_id => p_region_application_id,
5092 p_display_sequence => p_display_sequence,
5093 p_return_status => l_return_status,
5094 p_msg_count => p_msg_count,
5095 p_msg_data => p_msg_data,
5096 p_pass => p_pass,
5097 p_copy_redo_flag => p_copy_redo_flag);
5098 end if;
5099
5100 --
5101 -- validate table columns passed in
5102 --
5103 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5104 if not AK_FLOW3_PVT.VALIDATE_PAGE_REGION (
5105 p_validation_level => p_validation_level,
5106 p_api_version_number => 1.0,
5107 p_return_status => l_return_status,
5108 p_flow_application_id => p_flow_application_id,
5109 p_flow_code => p_flow_code,
5110 p_page_application_id => p_page_application_id,
5111 p_page_code => p_page_code,
5112 p_region_application_id => p_region_application_id,
5113 p_region_code => p_region_code,
5114 p_display_sequence => p_display_sequence,
5115 p_region_style => p_region_style,
5116 p_num_columns => p_num_columns,
5117 p_icx_custom_call => p_icx_custom_call,
5118 p_parent_region_application_id => p_parent_region_application_id,
5119 p_parent_region_code => p_parent_region_code,
5120 p_foreign_key_name => p_foreign_key_name,
5121 p_set_primary_region => p_set_primary_region,
5122 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
5123 p_pass => p_pass
5124 ) then
5125 -- Do not raise an error if it's the first pass
5126 if (p_pass = 1) then
5127 p_copy_redo_flag := TRUE;
5128 else
5129 raise FND_API.G_EXC_ERROR;
5130 end if;
5131 end if;
5132 end if;
5133
5134 --
5135 -- retrieve the foreign key name of the original intrapage relation.
5136 --
5137 open l_get_old_fk_csr (l_regions_rec.parent_region_application_id,
5138 l_regions_rec.parent_region_code);
5139 fetch l_get_old_fk_csr into l_foreign_key_name_old;
5140 if (l_get_old_fk_csr%notfound) then
5141 l_foreign_key_name_old := null;
5142 end if;
5143 close l_get_old_fk_csr;
5144
5145 --
5146 -- set flag indicating whether there is a change in parent region
5147 --
5148 if (p_parent_region_application_id =
5149 l_regions_rec.parent_region_application_id) and
5150 (p_parent_region_code = l_regions_rec.parent_region_code) then
5151 l_parent_region_changed := 'N';
5152 else
5153 l_parent_region_changed := 'Y';
5154 end if;
5155
5156 --
5157 -- if changing the foreign key name, or removing or changing parent region,
5158 -- we need to delete the existing intrapage relation with the
5159 -- old foreign key.
5160 --
5161 if ( (p_foreign_key_name <> FND_API.G_MISS_CHAR) and
5162 (p_foreign_key_name is not null) and
5163 (p_foreign_key_name <> NVL(l_foreign_key_name_old, l_dummy) ) ) or
5164 (l_parent_region_changed = 'Y') then
5165 --
5166 -- delete only if such a relation exists
5167 --
5168 if (l_foreign_key_name_old is not null) then
5169 AK_FLOW_PVT.DELETE_REGION_RELATION (
5170 p_validation_level => p_validation_level,
5171 p_api_version_number => 1.0,
5172 p_msg_count => p_msg_count,
5173 p_msg_data => p_msg_data,
5174 p_return_status => l_return_status,
5175 p_flow_application_id => l_regions_rec.flow_application_id,
5176 p_flow_code => l_regions_rec.flow_code,
5177 p_foreign_key_name => l_foreign_key_name_old,
5178 p_from_page_appl_id => l_regions_rec.page_application_id,
5179 p_from_page_code => l_regions_rec.page_code,
5180 p_from_region_appl_id => l_regions_rec.parent_region_application_id,
5181 p_from_region_code => l_regions_rec.parent_region_code,
5182 p_to_page_appl_id => l_regions_rec.page_application_id,
5183 p_to_page_code => l_regions_rec.page_code,
5184 p_to_region_appl_id => l_regions_rec.region_application_id,
5185 p_to_region_code => l_regions_rec.region_code,
5186 p_delete_cascade => 'Y'
5187 );
5188 end if;
5189 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
5190 (l_return_status = FND_API.G_RET_STS_ERROR) then
5191 RAISE FND_API.G_EXC_ERROR;
5192 end if;
5193 end if;
5194
5195 --** Load record to be updated to the database **
5196 --** - first load nullable columns **
5197
5198 if (p_display_sequence <> FND_API.G_MISS_NUM) or
5199 (p_display_sequence is null) then
5200 l_regions_rec.display_sequence := p_display_sequence;
5201 end if;
5202 if (p_num_columns <> FND_API.G_MISS_NUM) or
5203 (p_num_columns is null) then
5204 l_regions_rec.num_columns := p_num_columns;
5205 end if;
5206 if (p_parent_region_application_id <> FND_API.G_MISS_NUM) or
5207 (p_parent_region_application_id is null) then
5208 l_regions_rec.parent_region_application_id :=
5209 p_parent_region_application_id;
5210 end if;
5211 if (p_parent_region_code <> FND_API.G_MISS_CHAR) or
5212 (p_parent_region_code is null) then
5213 l_regions_rec.parent_region_code := p_parent_region_code;
5214 end if;
5215 if (p_icx_custom_call <> FND_API.G_MISS_CHAR) or
5216 (p_icx_custom_call is null) then
5217 l_regions_rec.icx_custom_call := p_icx_custom_call;
5218 end if;
5219 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
5220 (p_attribute_category is null) then
5221 l_regions_rec.attribute_category := p_attribute_category;
5222 end if;
5223 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
5224 (p_attribute1 is null) then
5225 l_regions_rec.attribute1 := p_attribute1;
5226 end if;
5227 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
5228 (p_attribute2 is null) then
5232 (p_attribute3 is null) then
5229 l_regions_rec.attribute2 := p_attribute2;
5230 end if;
5231 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
5233 l_regions_rec.attribute3 := p_attribute3;
5234 end if;
5235 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
5236 (p_attribute4 is null) then
5237 l_regions_rec.attribute4 := p_attribute4;
5238 end if;
5239 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
5240 (p_attribute5 is null) then
5241 l_regions_rec.attribute5 := p_attribute5;
5242 end if;
5243 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
5244 (p_attribute6 is null) then
5245 l_regions_rec.attribute6 := p_attribute6;
5246 end if;
5247 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
5248 (p_attribute7 is null) then
5249 l_regions_rec.attribute7 := p_attribute7;
5250 end if;
5251 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
5252 (p_attribute8 is null) then
5253 l_regions_rec.attribute8 := p_attribute8;
5254 end if;
5255 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
5256 (p_attribute9 is null) then
5257 l_regions_rec.attribute9 := p_attribute9;
5258 end if;
5259 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
5260 (p_attribute10 is null) then
5261 l_regions_rec.attribute10 := p_attribute10;
5262 end if;
5263 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
5264 (p_attribute11 is null) then
5265 l_regions_rec.attribute11 := p_attribute11;
5266 end if;
5267 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
5268 (p_attribute12 is null) then
5269 l_regions_rec.attribute12 := p_attribute12;
5270 end if;
5271 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
5272 (p_attribute13 is null) then
5273 l_regions_rec.attribute13 := p_attribute13;
5274 end if;
5275 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
5276 (p_attribute14 is null) then
5277 l_regions_rec.attribute14 := p_attribute14;
5278 end if;
5279 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
5280 (p_attribute15 is null) then
5281 l_regions_rec.attribute15 := p_attribute15;
5282 end if;
5283
5284 --** - next, load non-null columns **
5285 if (p_region_style <> FND_API.G_MISS_CHAR) then
5286 l_regions_rec.region_style := p_region_style;
5287 end if;
5288
5289 if (p_created_by <> FND_API.G_MISS_NUM) then
5290 l_created_by := p_created_by;
5291 end if;
5292 if (p_creation_date <> FND_API.G_MISS_DATE) then
5293 l_creation_date := p_creation_date;
5294 end if;
5295 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5296 l_last_updated_by := p_last_updated_by;
5297 end if;
5298 if (p_last_update_date <> FND_API.G_MISS_DATE) then
5299 l_last_update_date := p_last_update_date;
5300 end if;
5301 if (p_last_update_login <> FND_API.G_MISS_NUM) then
5302 l_last_update_login := p_last_update_login;
5303 end if;
5304
5305 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
5306 p_loader_timestamp => p_loader_timestamp,
5307 p_created_by => l_created_by,
5308 p_creation_date => l_creation_date,
5309 p_last_updated_by => l_last_updated_by,
5310 p_db_last_updated_by => l_regions_rec.last_updated_by,
5311 p_last_update_date => l_last_update_date,
5312 p_db_last_update_date => l_regions_rec.last_update_date,
5313 p_last_update_login => l_last_update_login,
5314 p_create_or_update => 'UPDATE') then
5315
5316 --
5317 -- Create a new intrapage relation if the foreign key name is changed,
5318 -- or a different (non-null) parent region is specified.
5319 --
5320 if ( (p_foreign_key_name <> FND_API.G_MISS_CHAR) and
5321 (p_foreign_key_name is not null) and
5322 (p_foreign_key_name <> NVL(l_foreign_key_name_old, l_dummy) ) ) or
5323 (l_parent_region_changed = 'Y') then
5324 --
5325 -- Create region relation only if it doesn't already exists,
5326 -- and a parent region is given.
5327 --
5328 if NOT AK_FLOW_PVT.REGION_RELATION_EXISTS (
5329 p_api_version_number => 1.0,
5330 p_return_status => l_return_status,
5331 p_flow_application_id => l_regions_rec.flow_application_id,
5332 p_flow_code => l_regions_rec.flow_code,
5333 p_foreign_key_name => p_foreign_key_name,
5334 p_from_page_appl_id => l_regions_rec.page_application_id,
5335 p_from_page_code => l_regions_rec.page_code,
5336 p_from_region_appl_id => l_regions_rec.parent_region_application_id,
5337 p_from_region_code => l_regions_rec.parent_region_code,
5338 p_to_page_appl_id => l_regions_rec.page_application_id,
5339 p_to_page_code => l_regions_rec.page_code,
5340 p_to_region_appl_id => l_regions_rec.region_application_id,
5341 p_to_region_code => l_regions_rec.region_code
5342 ) and
5343 (l_regions_rec.parent_region_application_id is not null) and
5344 (l_regions_rec.parent_region_code is not null) then
5345 AK_FLOW_PVT.CREATE_REGION_RELATION (
5346 p_validation_level => p_validation_level,
5347 p_api_version_number => 1.0,
5348 p_msg_count => p_msg_count,
5349 p_msg_data => p_msg_data,
5350 p_return_status => l_return_status,
5351 p_flow_application_id => l_regions_rec.flow_application_id,
5352 p_flow_code => l_regions_rec.flow_code,
5353 p_foreign_key_name => p_foreign_key_name,
5354 p_from_page_appl_id => l_regions_rec.page_application_id,
5355 p_from_page_code => l_regions_rec.page_code,
5356 p_from_region_appl_id => l_regions_rec.parent_region_application_id,
5357 p_from_region_code => l_regions_rec.parent_region_code,
5358 p_to_page_appl_id => l_regions_rec.page_application_id,
5359 p_to_page_code => l_regions_rec.page_code,
5363 p_created_by => l_created_by,
5360 p_to_region_appl_id => l_regions_rec.region_application_id,
5361 p_to_region_code => l_regions_rec.region_code,
5362 p_application_id => l_regions_rec.flow_application_id,
5364 p_creation_date => l_creation_date,
5365 p_last_updated_by => l_last_updated_by,
5366 p_last_update_date => l_last_update_date,
5367 p_last_update_login => l_last_update_login,
5368 p_pass => p_pass,
5369 p_copy_redo_flag => p_copy_redo_flag
5370 );
5371 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
5372 (l_return_status = FND_API.G_RET_STS_ERROR) then
5373 if (p_pass = 1) then
5374 p_copy_redo_flag := TRUE;
5375 else
5376 RAISE FND_API.G_EXC_ERROR;
5377 end if;
5378 end if; --/* if l_return_status */
5379 end if;
5380 end if;
5381
5382 update AK_FLOW_PAGE_REGIONS set
5383 DISPLAY_SEQUENCE = l_regions_rec.display_sequence,
5384 REGION_STYLE = l_regions_rec.region_style,
5385 NUM_COLUMNS = l_regions_rec.num_columns,
5386 PARENT_REGION_APPLICATION_ID =l_regions_rec.parent_region_application_id,
5387 PARENT_REGION_CODE = l_regions_rec.parent_region_code,
5388 ICX_CUSTOM_CALL = l_regions_rec.icx_custom_call,
5389 ATTRIBUTE_CATEGORY = l_regions_rec.attribute_category,
5390 ATTRIBUTE1 = l_regions_rec.attribute1,
5391 ATTRIBUTE2 = l_regions_rec.attribute2,
5392 ATTRIBUTE3 = l_regions_rec.attribute3,
5393 ATTRIBUTE4 = l_regions_rec.attribute4,
5394 ATTRIBUTE5 = l_regions_rec.attribute5,
5395 ATTRIBUTE6 = l_regions_rec.attribute6,
5396 ATTRIBUTE7 = l_regions_rec.attribute7,
5397 ATTRIBUTE8 = l_regions_rec.attribute8,
5398 ATTRIBUTE9 = l_regions_rec.attribute9,
5399 ATTRIBUTE10 = l_regions_rec.attribute10,
5400 ATTRIBUTE11 = l_regions_rec.attribute11,
5401 ATTRIBUTE12 = l_regions_rec.attribute12,
5402 ATTRIBUTE13 = l_regions_rec.attribute13,
5403 ATTRIBUTE14 = l_regions_rec.attribute14,
5404 ATTRIBUTE15 = l_regions_rec.attribute15,
5405 LAST_UPDATE_DATE = l_last_update_date,
5406 LAST_UPDATED_BY = l_last_updated_by,
5407 LAST_UPDATE_LOGIN = l_last_update_login
5408 where FLOW_APPLICATION_ID = p_flow_application_id
5409 and FLOW_CODE = p_flow_code
5410 and PAGE_APPLICATION_ID = p_page_application_id
5411 and PAGE_CODE = p_page_code
5412 and REGION_APPLICATION_ID = p_region_application_id
5413 and REGION_CODE = p_region_code;
5414 if (sql%notfound) then
5415 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5416 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_UPDATE_FAILED');
5417 FND_MSG_PUB.Add;
5418 end if;
5419 raise FND_API.G_EXC_ERROR;
5420 end if;
5421
5422 -- /** commit the update **/
5423 -- commit;
5424
5425
5426 --
5427 -- Set current region as the primary region of the flow page if
5428 -- p_set_primary_region is 'Y'.
5429 --
5430 if (p_set_primary_region = 'Y') then
5431 AK_FLOW_PVT.UPDATE_PAGE (
5432 p_validation_level => p_validation_level,
5433 p_api_version_number => 1.0,
5434 p_msg_count => l_msg_count,
5435 p_msg_data => l_msg_data,
5436 p_return_status => l_return_status,
5437 p_flow_application_id => p_flow_application_id,
5438 p_flow_code => p_flow_code,
5439 p_page_application_id => p_page_application_id,
5440 p_page_code => p_page_code,
5441 p_primary_region_appl_id => p_region_application_id,
5442 p_primary_region_code => p_region_code,
5443 p_created_by => l_created_by,
5444 p_creation_date => l_creation_date,
5445 p_last_updated_by => l_last_updated_by,
5446 p_last_update_date => l_last_update_date,
5447 p_last_update_login => l_last_update_login,
5448 p_pass => p_pass,
5449 p_copy_redo_flag => p_copy_redo_flag
5450 );
5451 if (l_return_status = FND_API.G_RET_STS_ERROR) or
5452 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
5453 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5454 raise FND_API.G_EXC_ERROR;
5455 end if;
5456 end if;
5457 end if;
5458
5459 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
5460 FND_MESSAGE.SET_NAME('AK','AK_PAGE_REGION_UPDATED');
5461 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
5462 ' ' || p_flow_code ||
5463 ' ' || to_char(p_page_application_id) ||
5464 ' ' || p_page_code ||
5465 ' ' || to_char(p_region_application_id) ||
5466 ' ' || p_region_code);
5467 FND_MSG_PUB.Add;
5468 end if;
5469
5470 end if;
5471 p_return_status := FND_API.G_RET_STS_SUCCESS;
5472
5473 FND_MSG_PUB.Count_And_Get (
5474 p_count => p_msg_count,
5475 p_data => p_msg_data);
5476
5477 EXCEPTION
5478 WHEN VALUE_ERROR THEN
5479 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5480 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_VALUE_ERROR');
5481 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
5482 ' ' || p_flow_code ||
5483 ' ' || to_char(p_page_application_id) ||
5484 ' ' || p_page_code ||
5485 ' ' || to_char(p_region_application_id) ||
5486 ' ' || p_region_code);
5487 FND_MSG_PUB.Add;
5488 end if;
5489 rollback to start_update_page_region;
5490 p_return_status := FND_API.G_RET_STS_ERROR;
5491 FND_MSG_PUB.Count_And_Get (
5492 p_count => p_msg_count,
5493 p_data => p_msg_data);
5494 WHEN FND_API.G_EXC_ERROR THEN
5495 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5499 ' ' || to_char(p_page_application_id) ||
5496 FND_MESSAGE.SET_NAME('AK','AK_PG_REGION_NOT_UPDATED');
5497 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
5498 ' ' || p_flow_code ||
5500 ' ' || p_page_code ||
5501 ' ' || to_char(p_region_application_id) ||
5502 ' ' || p_region_code);
5503 FND_MSG_PUB.Add;
5504 end if;
5505 p_return_status := FND_API.G_RET_STS_ERROR;
5506 rollback to start_update_page_region;
5507 FND_MSG_PUB.Count_And_Get (
5508 p_count => p_msg_count,
5509 p_data => p_msg_data);
5510 WHEN OTHERS THEN
5511 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5512 rollback to start_update_page_region;
5513 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5514 SUBSTR (SQLERRM, 1, 240) );
5515 FND_MSG_PUB.Add;
5516 FND_MSG_PUB.Count_And_Get (
5517 p_count => p_msg_count,
5518 p_data => p_msg_data);
5519 end UPDATE_PAGE_REGION;
5520
5521 --=======================================================
5522 -- Procedure UPDATE_PAGE_REGION_ITEM
5523 --
5524 -- Usage Private API for updating a flow page region item.
5525 -- This API should only be called by other APIs
5526 -- that are owned by the Core Modules Team (AK).
5527 --
5528 -- Desc This API updates a flow page region item using the
5529 -- given info
5530 --
5531 -- Results The API returns the standard p_return_status parameter
5532 -- indicating one of the standard return statuses :
5533 -- * Unexpected error
5534 -- * Error
5535 -- * Success
5536 -- Parameters Flow Page Region Item columns
5537 -- p_loader_timestamp : IN optional
5538 -- If a timestamp is passed, the API will update the
5539 -- record using this timestamp. Only the upload API
5540 -- should call with this parameter loaded.
5541 --
5542 -- Version Initial version number = 1.0
5543 -- History Current version number = 1.0
5544 --=======================================================
5545 procedure UPDATE_PAGE_REGION_ITEM (
5546 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5547 p_api_version_number IN NUMBER,
5548 p_init_msg_tbl IN BOOLEAN := FALSE,
5549 p_msg_count OUT NOCOPY NUMBER,
5550 p_msg_data OUT NOCOPY VARCHAR2,
5551 p_return_status OUT NOCOPY VARCHAR2,
5552 p_flow_application_id IN NUMBER,
5553 p_flow_code IN VARCHAR2,
5554 p_page_application_id IN NUMBER,
5555 p_page_code IN VARCHAR2,
5556 p_region_application_id IN NUMBER,
5557 p_region_code IN VARCHAR2,
5558 p_attribute_application_id IN NUMBER,
5559 p_attribute_code IN VARCHAR2,
5560 p_to_page_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5561 p_to_page_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5562 p_to_url_attribute_appl_id IN NUMBER := FND_API.G_MISS_NUM,
5563 p_to_url_attribute_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
5564 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
5565 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5566 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5567 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5568 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5569 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5570 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5571 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5572 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5573 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5574 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5575 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5576 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5577 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5578 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5579 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5580 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5581 p_creation_date IN DATE := FND_API.G_MISS_DATE,
5582 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5583 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5584 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5585 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5586 p_pass IN NUMBER,
5587 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5588 ) is
5589 cursor l_get_row_csr is
5590 select *
5591 from AK_FLOW_PAGE_REGION_ITEMS
5592 where FLOW_APPLICATION_ID = p_flow_application_id
5593 and FLOW_CODE = p_flow_code
5594 and page_application_id = p_page_application_id
5595 and page_code = p_page_code
5596 and region_application_id = p_region_application_id
5597 and region_code = p_region_code
5598 and attribute_application_id = p_attribute_application_id
5599 and attribute_code = p_attribute_code
5600 for update of to_page_appl_id;
5601 l_api_version_number CONSTANT number := 1.0;
5602 l_api_name CONSTANT varchar2(30) := 'Update_Page_Region_Item';
5603 l_created_by number;
5604 l_creation_date date;
5605 l_error boolean;
5609 l_last_updated_by number;
5606 l_lang varchar2(30);
5607 l_last_update_date date;
5608 l_last_update_login number;
5610 l_items_rec ak_flow_page_region_items%ROWTYPE;
5611 l_return_status varchar2(1);
5612 l_file_version number;
5613 begin
5614 IF NOT FND_API.Compatible_API_Call (
5615 l_api_version_number, p_api_version_number, l_api_name,
5616 G_PKG_NAME) then
5617 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5618 return;
5619 END IF;
5620
5621 -- Initialize the message table if requested.
5622
5623 if p_init_msg_tbl then
5624 FND_MSG_PUB.initialize;
5625 end if;
5626
5627 savepoint start_update_item;
5628
5629 select userenv('LANG') into l_lang
5630 from dual;
5631
5632 --
5633 -- retrieve ak_flow_page_region_items row if it exists
5634 --
5635 open l_get_row_csr;
5636 fetch l_get_row_csr into l_items_rec;
5637 if (l_get_row_csr%notfound) then
5638 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
5639 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_DOES_NOT_EXIST');
5640 FND_MSG_PUB.Add;
5641 end if;
5642 close l_get_row_csr;
5643 raise FND_API.G_EXC_ERROR;
5644 end if;
5645 close l_get_row_csr;
5646
5647 --
5648 -- validate table columns passed in
5649 --
5650 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5651 if not AK_FLOW3_PVT.VALIDATE_PAGE_REGION_ITEM (
5652 p_validation_level => p_validation_level,
5653 p_api_version_number => 1.0,
5654 p_return_status => l_return_status,
5655 p_flow_application_id => p_flow_application_id,
5656 p_flow_code => p_flow_code,
5657 p_page_application_id => p_page_application_id,
5658 p_page_code => p_page_code,
5659 p_region_application_id => p_region_application_id,
5660 p_region_code => p_region_code,
5661 p_attribute_application_id => p_attribute_application_id,
5662 p_attribute_code => p_attribute_code,
5663 p_to_page_appl_id => p_to_page_appl_id,
5664 p_to_page_code => p_to_page_code,
5665 p_to_url_attribute_appl_id => p_to_url_attribute_appl_id,
5666 p_to_url_attribute_code => p_to_url_attribute_code,
5667 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
5668 p_pass => p_pass
5669 ) then
5670 -- Do not raise an error if it's the first pass
5671 if (p_pass = 1) then
5672 p_copy_redo_flag := TRUE;
5673 else
5674 raise FND_API.G_EXC_ERROR;
5675 end if;
5676 end if;
5677 end if;
5678
5679 --** Load record to be updated to the database **
5680 --** - first load nullable columns **
5681
5682 if (p_to_page_appl_id <> FND_API.G_MISS_NUM) or
5683 (p_to_page_appl_id is null) then
5684 l_items_rec.to_page_appl_id := p_to_page_appl_id;
5685 end if;
5686 if (p_to_page_code <> FND_API.G_MISS_CHAR) or
5687 (p_to_page_code is null) then
5688 l_items_rec.to_page_code := p_to_page_code;
5689 end if;
5690 if (p_to_url_attribute_appl_id <> FND_API.G_MISS_NUM) or
5691 (p_to_url_attribute_appl_id is null) then
5692 l_items_rec.to_url_attribute_appl_id := p_to_url_attribute_appl_id;
5693 end if;
5694 if (p_to_page_code <> FND_API.G_MISS_CHAR) or
5695 (p_to_page_code is null) then
5696 l_items_rec.to_url_attribute_code := p_to_url_attribute_code;
5697 end if;
5698 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
5699 (p_attribute_category is null) then
5700 l_items_rec.attribute_category := p_attribute_category;
5701 end if;
5702 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
5703 (p_attribute1 is null) then
5704 l_items_rec.attribute1 := p_attribute1;
5705 end if;
5706 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
5707 (p_attribute2 is null) then
5708 l_items_rec.attribute2 := p_attribute2;
5709 end if;
5710 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
5711 (p_attribute3 is null) then
5712 l_items_rec.attribute3 := p_attribute3;
5713 end if;
5714 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
5715 (p_attribute4 is null) then
5716 l_items_rec.attribute4 := p_attribute4;
5717 end if;
5718 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
5719 (p_attribute5 is null) then
5720 l_items_rec.attribute5 := p_attribute5;
5721 end if;
5722 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
5723 (p_attribute6 is null) then
5724 l_items_rec.attribute6 := p_attribute6;
5725 end if;
5726 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
5727 (p_attribute7 is null) then
5728 l_items_rec.attribute7 := p_attribute7;
5729 end if;
5730 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
5731 (p_attribute8 is null) then
5732 l_items_rec.attribute8 := p_attribute8;
5733 end if;
5734 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
5735 (p_attribute9 is null) then
5736 l_items_rec.attribute9 := p_attribute9;
5737 end if;
5738 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
5739 (p_attribute10 is null) then
5740 l_items_rec.attribute10 := p_attribute10;
5741 end if;
5742 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
5743 (p_attribute11 is null) then
5744 l_items_rec.attribute11 := p_attribute11;
5745 end if;
5746 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
5747 (p_attribute12 is null) then
5748 l_items_rec.attribute12 := p_attribute12;
5749 end if;
5750 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
5751 (p_attribute13 is null) then
5752 l_items_rec.attribute13 := p_attribute13;
5753 end if;
5754 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
5758 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
5755 (p_attribute14 is null) then
5756 l_items_rec.attribute14 := p_attribute14;
5757 end if;
5759 (p_attribute15 is null) then
5760 l_items_rec.attribute15 := p_attribute15;
5761 end if;
5762
5763 --** - next, load non-null columns **
5764 --
5765 -- none
5766
5767 --
5768 -- - either to_page or to_url_attribute must be specified,
5769 --
5770 if (l_items_rec.to_page_code is null) and
5771 (l_items_rec.to_url_attribute_code is null) then
5772 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
5773 FND_MESSAGE.SET_NAME('AK','AK_NO_LINK_SELECTED');
5774 FND_MSG_PUB.Add;
5775 end if;
5776 raise FND_API.G_EXC_ERROR;
5777 end if;
5778
5779 --
5780 -- - cannot specify both to_page and to_url_attribute
5781 --
5782 if ( ( (l_items_rec.to_page_code is not null) and
5783 (l_items_rec.to_page_code <> FND_API.G_MISS_CHAR) ) or
5784 ( (l_items_rec.to_page_appl_id is not null) and
5785 (l_items_rec.to_page_appl_id <> FND_API.G_MISS_NUM) ) ) and
5786 ( ( (l_items_rec.to_url_attribute_code is not null) and
5787 (l_items_rec.to_url_attribute_code <> FND_API.G_MISS_CHAR) ) or
5788 ( (l_items_rec.to_url_attribute_appl_id is not null) and
5789 (l_items_rec.to_url_attribute_appl_id <> FND_API.G_MISS_NUM) ) ) then
5790 l_error := TRUE;
5791 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
5792 FND_MESSAGE.SET_NAME('AK','AK_TWO_LINK_SELECTED');
5793 FND_MSG_PUB.Add;
5794 end if;
5795 raise FND_API.G_EXC_ERROR;
5796 end if;
5797
5798 if (p_created_by <> FND_API.G_MISS_NUM) then
5799 l_created_by := p_created_by;
5800 end if;
5801 if (p_creation_date <> FND_API.G_MISS_DATE) then
5802 l_creation_date := p_creation_date;
5803 end if;
5804 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5805 l_last_updated_by := p_last_updated_by;
5806 end if;
5807 if (p_last_update_date <> FND_API.G_MISS_DATE) then
5808 l_last_update_date := p_last_update_date;
5809 end if;
5810 if (p_last_update_login <> FND_API.G_MISS_NUM) then
5811 l_last_update_login := p_last_update_login;
5812 end if;
5813
5814 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
5815 p_loader_timestamp => p_loader_timestamp,
5816 p_created_by => l_created_by,
5817 p_creation_date => l_creation_date,
5818 p_last_updated_by => l_last_updated_by,
5819 p_db_last_updated_by => l_items_rec.last_updated_by,
5820 p_last_update_date => l_last_update_date,
5821 p_db_last_update_date => l_items_rec.last_update_date,
5822 p_last_update_login => l_last_update_login,
5823 p_create_or_update => 'UPDATE') then
5824
5825 update AK_FLOW_PAGE_REGION_ITEMS set
5826 TO_PAGE_APPL_ID = l_items_rec.to_page_appl_id,
5827 TO_PAGE_CODE = l_items_rec.to_page_code,
5828 TO_URL_ATTRIBUTE_APPL_ID = l_items_rec.to_url_attribute_appl_id,
5829 TO_URL_ATTRIBUTE_CODE = l_items_rec.to_url_attribute_code,
5830 ATTRIBUTE_CATEGORY = l_items_rec.attribute_category,
5831 ATTRIBUTE1 = l_items_rec.attribute1,
5832 ATTRIBUTE2 = l_items_rec.attribute2,
5833 ATTRIBUTE3 = l_items_rec.attribute3,
5834 ATTRIBUTE4 = l_items_rec.attribute4,
5835 ATTRIBUTE5 = l_items_rec.attribute5,
5836 ATTRIBUTE6 = l_items_rec.attribute6,
5837 ATTRIBUTE7 = l_items_rec.attribute7,
5838 ATTRIBUTE8 = l_items_rec.attribute8,
5839 ATTRIBUTE9 = l_items_rec.attribute9,
5840 ATTRIBUTE10 = l_items_rec.attribute10,
5841 ATTRIBUTE11 = l_items_rec.attribute11,
5842 ATTRIBUTE12 = l_items_rec.attribute12,
5843 ATTRIBUTE13 = l_items_rec.attribute13,
5844 ATTRIBUTE14 = l_items_rec.attribute14,
5845 ATTRIBUTE15 = l_items_rec.attribute15,
5846 LAST_UPDATE_DATE = l_last_update_date,
5847 LAST_UPDATED_BY = l_last_updated_by,
5848 LAST_UPDATE_LOGIN = l_last_update_login
5849 where FLOW_APPLICATION_ID = p_flow_application_id
5850 and FLOW_CODE = p_flow_code
5851 and PAGE_APPLICATION_ID = p_page_application_id
5852 and PAGE_CODE = p_page_code
5853 and REGION_APPLICATION_ID = p_region_application_id
5854 and REGION_CODE = p_region_code
5855 and ATTRIBUTE_APPLICATION_ID = p_attribute_application_id
5856 and ATTRIBUTE_CODE = p_attribute_code;
5857 if (sql%notfound) then
5858 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5859 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_UPDATE_FAILED');
5860 FND_MSG_PUB.Add;
5861 end if;
5862 raise FND_API.G_EXC_ERROR;
5863 end if;
5864
5865 -- /** commit the update **/
5866 -- commit;
5867
5868 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
5869 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_UPDATED');
5870 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
5871 ' ' || p_flow_code ||
5872 ' ' || to_char(p_page_application_id) ||
5873 ' ' || p_page_code ||
5874 ' ' || to_char(p_region_application_id) ||
5875 ' ' || p_region_code ||
5876 ' ' || to_char(p_attribute_application_id)||
5877 ' ' || p_attribute_code);
5878 FND_MSG_PUB.Add;
5879 end if;
5880
5881 end if;
5882 p_return_status := FND_API.G_RET_STS_SUCCESS;
5883
5884 FND_MSG_PUB.Count_And_Get (
5885 p_count => p_msg_count,
5886 p_data => p_msg_data);
5887
5888 EXCEPTION
5889 WHEN VALUE_ERROR THEN
5893 ' ' || p_flow_code ||
5890 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5891 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_VALUE_ERROR');
5892 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
5894 ' ' || to_char(p_page_application_id) ||
5895 ' ' || p_page_code ||
5896 ' ' || to_char(p_region_application_id) ||
5897 ' ' || p_region_code ||
5898 ' ' || to_char(p_attribute_application_id)||
5899 ' ' || p_attribute_code);
5900 FND_MSG_PUB.Add;
5901 end if;
5902 rollback to start_update_item;
5903 p_return_status := FND_API.G_RET_STS_ERROR;
5904 FND_MSG_PUB.Count_And_Get (
5905 p_count => p_msg_count,
5906 p_data => p_msg_data);
5907 WHEN FND_API.G_EXC_ERROR THEN
5908 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5909 FND_MESSAGE.SET_NAME('AK','AK_PG_REG_ITEM_NOT_UPDATED');
5910 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
5911 ' ' || p_flow_code ||
5912 ' ' || to_char(p_page_application_id) ||
5913 ' ' || p_page_code ||
5914 ' ' || to_char(p_region_application_id) ||
5915 ' ' || p_region_code ||
5916 ' ' || to_char(p_attribute_application_id)||
5917 ' ' || p_attribute_code ||
5918 ' ' || p_to_page_code ||
5919 ' ' || p_to_url_attribute_code);
5920 FND_MSG_PUB.Add;
5921 end if;
5922 p_return_status := FND_API.G_RET_STS_ERROR;
5923 rollback to start_update_item;
5924 FND_MSG_PUB.Count_And_Get (
5925 p_count => p_msg_count,
5926 p_data => p_msg_data);
5927 WHEN OTHERS THEN
5928 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5929 rollback to start_update_item;
5930 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5931 SUBSTR (SQLERRM, 1, 240) );
5932 FND_MSG_PUB.Add;
5933 FND_MSG_PUB.Count_And_Get (
5934 p_count => p_msg_count,
5935 p_data => p_msg_data);
5936 end UPDATE_PAGE_REGION_ITEM;
5937
5938 --=======================================================
5939 -- Procedure UPDATE_REGION_RELATION
5940 --
5941 -- Usage Private API for updating a flow region relation.
5942 -- This API should only be called by other APIs
5943 -- that are owned by the Core Modules Team (AK).
5944 --
5945 -- Desc This API updates a flow region relation using the
5946 -- given info
5947 --
5948 -- Results The API returns the standard p_return_status parameter
5949 -- indicating one of the standard return statuses :
5950 -- * Unexpected error
5951 -- * Error
5952 -- * Success
5953 -- Parameters Flow Region Relation columns
5954 -- p_loader_timestamp : IN optional
5955 -- If a timestamp is passed, the API will update the
5956 -- record using this timestamp. Only the upload API
5957 -- should call with this parameter loaded.
5958 --
5959 -- Version Initial version number = 1.0
5960 -- History Current version number = 1.0
5961 --=======================================================
5962 procedure UPDATE_REGION_RELATION (
5963 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5964 p_api_version_number IN NUMBER,
5965 p_init_msg_tbl IN BOOLEAN := FALSE,
5966 p_msg_count OUT NOCOPY NUMBER,
5967 p_msg_data OUT NOCOPY VARCHAR2,
5968 p_return_status OUT NOCOPY VARCHAR2,
5969 p_flow_application_id IN NUMBER,
5970 p_flow_code IN VARCHAR2,
5971 p_foreign_key_name IN VARCHAR2,
5972 p_from_page_appl_id IN NUMBER,
5973 p_from_page_code IN VARCHAR2,
5974 p_from_region_appl_id IN NUMBER,
5975 p_from_region_code IN VARCHAR2,
5976 p_to_page_appl_id IN NUMBER,
5977 p_to_page_code IN VARCHAR2,
5978 p_to_region_appl_id IN NUMBER,
5979 p_to_region_code IN VARCHAR2,
5980 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
5981 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
5982 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5983 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5984 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5985 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5986 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5987 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5988 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5989 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5990 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5991 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5992 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5993 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5994 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5995 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5996 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5997 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5998 p_creation_date IN DATE := FND_API.G_MISS_DATE,
5999 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
6000 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
6001 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
6002 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
6003 p_pass IN NUMBER,
6007 select *
6004 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
6005 ) is
6006 cursor l_get_row_csr is
6008 from AK_FLOW_REGION_RELATIONS
6009 where flow_application_id = p_flow_application_id
6010 and flow_code = p_flow_code
6011 and foreign_key_name = p_foreign_key_name
6012 and from_page_appl_id = p_from_page_appl_id
6013 and from_page_code = p_from_page_code
6014 and from_region_appl_id = p_from_region_appl_id
6015 and from_region_code = p_from_region_code
6016 and to_page_appl_id = p_to_page_appl_id
6017 and to_page_code = p_to_page_code
6018 and to_region_appl_id = p_to_region_appl_id
6019 and to_region_code = p_to_region_code
6020 for update of application_id;
6021 l_api_version_number CONSTANT number := 1.0;
6022 l_api_name CONSTANT varchar2(30) := 'Update_Region_Relation';
6023 l_created_by number;
6024 l_creation_date date;
6025 l_relations_rec AK_FLOW_REGION_RELATIONS%ROWTYPE;
6026 l_lang varchar2(30);
6027 l_last_update_date date;
6028 l_last_update_login number;
6029 l_last_updated_by number;
6030 l_return_status varchar2(1);
6031 l_file_version number;
6032 begin
6033 IF NOT FND_API.Compatible_API_Call (
6034 l_api_version_number, p_api_version_number, l_api_name,
6035 G_PKG_NAME) then
6036 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6037 return;
6038 END IF;
6039
6040 -- Initialize the message table if requested.
6041
6042 if p_init_msg_tbl then
6043 FND_MSG_PUB.initialize;
6044 end if;
6045
6046 savepoint start_update_relation;
6047
6048 select userenv('LANG') into l_lang
6049 from dual;
6050
6051 --** retrieve ak_flow_region_relations row if it exists **
6052 open l_get_row_csr;
6053 fetch l_get_row_csr into l_relations_rec;
6054 if (l_get_row_csr%notfound) then
6055 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
6056 FND_MESSAGE.SET_NAME('AK','AK_RELATION_DOES_NOT_EXIST');
6057 FND_MSG_PUB.Add;
6058 end if;
6059 close l_get_row_csr;
6060 raise FND_API.G_EXC_ERROR;
6061 end if;
6062 close l_get_row_csr;
6063
6064 --** validate table columns passed in **
6065 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
6066 if not AK_FLOW3_PVT.VALIDATE_REGION_RELATION (
6067 p_validation_level => p_validation_level,
6068 p_api_version_number => 1.0,
6069 p_return_status => l_return_status,
6070 p_flow_application_id => p_flow_application_id,
6071 p_flow_code => p_flow_code,
6072 p_foreign_key_name => p_foreign_key_name,
6073 p_from_page_appl_id => p_from_page_appl_id,
6074 p_from_page_code => p_from_page_code,
6075 p_from_region_appl_id => p_from_region_appl_id,
6076 p_from_region_code => p_from_region_code,
6077 p_to_page_appl_id => p_to_page_appl_id,
6078 p_to_page_code => p_to_page_code,
6079 p_to_region_appl_id => p_to_region_appl_id,
6080 p_to_region_code => p_to_region_code,
6081 p_application_id => p_application_id,
6082 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
6083 p_pass => p_pass
6084 ) then
6085 -- Do not raise an error if it's the first pass
6086 if (p_pass = 1) then
6087 p_copy_redo_flag := TRUE;
6088 else
6089 raise FND_API.G_EXC_ERROR;
6090 end if;
6091 end if;
6092 end if;
6093
6094 -- Load record to be updated to the database
6095 -- - first load nullable columns
6096 --
6097 if (p_attribute_category <> FND_API.G_MISS_CHAR) or
6098 (p_attribute_category is null) then
6099 l_relations_rec.attribute_category := p_attribute_category;
6100 end if;
6101 if (p_attribute1 <> FND_API.G_MISS_CHAR) or
6102 (p_attribute1 is null) then
6103 l_relations_rec.attribute1 := p_attribute1;
6104 end if;
6105 if (p_attribute2 <> FND_API.G_MISS_CHAR) or
6106 (p_attribute2 is null) then
6107 l_relations_rec.attribute2 := p_attribute2;
6108 end if;
6109 if (p_attribute3 <> FND_API.G_MISS_CHAR) or
6110 (p_attribute3 is null) then
6111 l_relations_rec.attribute3 := p_attribute3;
6112 end if;
6113 if (p_attribute4 <> FND_API.G_MISS_CHAR) or
6114 (p_attribute4 is null) then
6115 l_relations_rec.attribute4 := p_attribute4;
6116 end if;
6117 if (p_attribute5 <> FND_API.G_MISS_CHAR) or
6118 (p_attribute5 is null) then
6119 l_relations_rec.attribute5 := p_attribute5;
6120 end if;
6121 if (p_attribute6 <> FND_API.G_MISS_CHAR) or
6122 (p_attribute6 is null) then
6123 l_relations_rec.attribute6 := p_attribute6;
6124 end if;
6125 if (p_attribute7 <> FND_API.G_MISS_CHAR) or
6126 (p_attribute7 is null) then
6127 l_relations_rec.attribute7 := p_attribute7;
6128 end if;
6129 if (p_attribute8 <> FND_API.G_MISS_CHAR) or
6130 (p_attribute8 is null) then
6131 l_relations_rec.attribute8 := p_attribute8;
6132 end if;
6133 if (p_attribute9 <> FND_API.G_MISS_CHAR) or
6134 (p_attribute9 is null) then
6135 l_relations_rec.attribute9 := p_attribute9;
6136 end if;
6137 if (p_attribute10 <> FND_API.G_MISS_CHAR) or
6138 (p_attribute10 is null) then
6139 l_relations_rec.attribute10 := p_attribute10;
6140 end if;
6141 if (p_attribute11 <> FND_API.G_MISS_CHAR) or
6142 (p_attribute11 is null) then
6143 l_relations_rec.attribute11 := p_attribute11;
6144 end if;
6145 if (p_attribute12 <> FND_API.G_MISS_CHAR) or
6146 (p_attribute12 is null) then
6147 l_relations_rec.attribute12 := p_attribute12;
6148 end if;
6152 end if;
6149 if (p_attribute13 <> FND_API.G_MISS_CHAR) or
6150 (p_attribute13 is null) then
6151 l_relations_rec.attribute13 := p_attribute13;
6153 if (p_attribute14 <> FND_API.G_MISS_CHAR) or
6154 (p_attribute14 is null) then
6155 l_relations_rec.attribute14 := p_attribute14;
6156 end if;
6157 if (p_attribute15 <> FND_API.G_MISS_CHAR) or
6158 (p_attribute15 is null) then
6159 l_relations_rec.attribute15 := p_attribute15;
6160 end if;
6161
6162 -- next, load non-null, non-key columns
6163
6164 if (p_application_id <> FND_API.G_MISS_NUM) then
6165 l_relations_rec.application_id := p_application_id;
6166 end if;
6167
6168 if (p_created_by <> FND_API.G_MISS_NUM) then
6169 l_created_by := p_created_by;
6170 end if;
6171 if (p_creation_date <> FND_API.G_MISS_DATE) then
6172 l_creation_date := p_creation_date;
6173 end if;
6174 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
6175 l_last_updated_by := p_last_updated_by;
6176 end if;
6177 if (p_last_update_date <> FND_API.G_MISS_DATE) then
6178 l_last_update_date := p_last_update_date;
6179 end if;
6180 if (p_last_update_login <> FND_API.G_MISS_NUM) then
6181 l_last_update_login := p_last_update_login;
6182 end if;
6183
6184 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
6185 p_loader_timestamp => p_loader_timestamp,
6186 p_created_by => l_created_by,
6187 p_creation_date => l_creation_date,
6188 p_last_updated_by => l_last_updated_by,
6189 p_db_last_updated_by => l_relations_rec.last_updated_by,
6190 p_last_update_date => l_last_update_date,
6191 p_db_last_update_date => l_relations_rec.last_update_date,
6192 p_last_update_login => l_last_update_login,
6193 p_create_or_update => 'UPDATE') then
6194
6195 update AK_FLOW_REGION_RELATIONS set
6196 APPLICATION_ID = l_relations_rec.application_id,
6197 ATTRIBUTE_CATEGORY = l_relations_rec.attribute_category,
6198 ATTRIBUTE1 = l_relations_rec.attribute1,
6199 ATTRIBUTE2 = l_relations_rec.attribute2,
6200 ATTRIBUTE3 = l_relations_rec.attribute3,
6201 ATTRIBUTE4 = l_relations_rec.attribute4,
6202 ATTRIBUTE5 = l_relations_rec.attribute5,
6203 ATTRIBUTE6 = l_relations_rec.attribute6,
6204 ATTRIBUTE7 = l_relations_rec.attribute7,
6205 ATTRIBUTE8 = l_relations_rec.attribute8,
6206 ATTRIBUTE9 = l_relations_rec.attribute9,
6207 ATTRIBUTE10 = l_relations_rec.attribute10,
6208 ATTRIBUTE11 = l_relations_rec.attribute11,
6209 ATTRIBUTE12 = l_relations_rec.attribute12,
6210 ATTRIBUTE13 = l_relations_rec.attribute13,
6211 ATTRIBUTE14 = l_relations_rec.attribute14,
6212 ATTRIBUTE15 = l_relations_rec.attribute15,
6213 LAST_UPDATE_DATE = l_last_update_date,
6214 LAST_UPDATED_BY = l_last_updated_by,
6215 LAST_UPDATE_LOGIN = l_last_update_login
6216 where flow_application_id = p_flow_application_id
6217 and flow_code = p_flow_code
6218 and foreign_key_name = p_foreign_key_name
6219 and from_page_appl_id = p_from_page_appl_id
6220 and from_page_code = p_from_page_code
6221 and from_region_appl_id = p_from_region_appl_id
6222 and from_region_code = p_from_region_code
6223 and to_page_appl_id = p_to_page_appl_id
6224 and to_page_code = p_to_page_code
6225 and to_region_appl_id = p_to_region_appl_id
6226 and to_region_code = p_to_region_code;
6227 if (sql%notfound) then
6228 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
6229 FND_MESSAGE.SET_NAME('AK','AK_RELATION_DOES_NOT_EXIST');
6230 FND_MESSAGE.SET_TOKEN('OBJECT','EC_FLOW_REGION_RELATIONSHIP', TRUE);
6231 FND_MSG_PUB.Add;
6232 end if;
6233 raise FND_API.G_EXC_ERROR;
6234 end if;
6235
6236 -- /** commit the update **/
6237 -- commit;
6238
6239 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
6240 FND_MESSAGE.SET_NAME('AK','AK_RELATION_UPDATED');
6241 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
6242 ' ' || p_flow_code ||
6243 ' ' || p_foreign_key_name ||
6244 ' ' || to_char(p_from_page_appl_id) ||
6245 ' ' || p_from_page_code ||
6246 ' ' || to_char(p_from_region_appl_id) ||
6247 ' ' || p_from_region_code ||
6248 ' ' || to_char(p_to_page_appl_id) ||
6249 ' ' || p_to_page_code ||
6250 ' ' || to_char(p_to_region_appl_id) ||
6251 ' ' || p_to_region_code);
6252 FND_MSG_PUB.Add;
6253 end if;
6254
6255 end if;
6256 p_return_status := FND_API.G_RET_STS_SUCCESS;
6257
6258 FND_MSG_PUB.Count_And_Get (
6259 p_count => p_msg_count,
6260 p_data => p_msg_data);
6261
6262 EXCEPTION
6263 WHEN VALUE_ERROR THEN
6264 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6265 FND_MESSAGE.SET_NAME('AK','AK_RELATION_VALUE_ERROR');
6266 FND_MESSAGE.SET_TOKEN('OBJECT', 'EC_FLOW_REGION_RELATIONSHIP',TRUE);
6267 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
6268 ' ' || p_flow_code ||
6269 ' ' || p_foreign_key_name ||
6270 ' ' || to_char(p_from_page_appl_id) ||
6271 ' ' || p_from_page_code ||
6272 ' ' || to_char(p_from_region_appl_id) ||
6273 ' ' || p_from_region_code ||
6274 ' ' || to_char(p_to_page_appl_id) ||
6275 ' ' || p_to_page_code ||
6276 ' ' || to_char(p_to_region_appl_id) ||
6277 ' ' || p_to_region_code);
6278 FND_MSG_PUB.Add;
6279 end if;
6280 rollback to start_update_relation;
6281 p_return_status := FND_API.G_RET_STS_ERROR;
6282 FND_MSG_PUB.Count_And_Get (
6283 p_count => p_msg_count,
6284 p_data => p_msg_data);
6288 FND_MESSAGE.SET_TOKEN('OBJECT', 'EC_FLOW_REGION_RELATIONSHIP',TRUE);
6285 WHEN FND_API.G_EXC_ERROR THEN
6286 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
6287 FND_MESSAGE.SET_NAME('AK','AK_RELATION_NOT_UPDATED');
6289 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_flow_application_id) ||
6290 ' ' || p_flow_code ||
6291 ' ' || p_foreign_key_name ||
6292 ' ' || to_char(p_from_page_appl_id) ||
6293 ' ' || p_from_page_code ||
6294 ' ' || to_char(p_from_region_appl_id) ||
6295 ' ' || p_from_region_code ||
6296 ' ' || to_char(p_to_page_appl_id) ||
6297 ' ' || p_to_page_code ||
6298 ' ' || to_char(p_to_region_appl_id) ||
6299 ' ' || p_to_region_code);
6300 FND_MSG_PUB.Add;
6301 end if;
6302 p_return_status := FND_API.G_RET_STS_ERROR;
6303 rollback to start_update_relation;
6304 FND_MSG_PUB.Count_And_Get (
6305 p_count => p_msg_count,
6306 p_data => p_msg_data);
6307 WHEN OTHERS THEN
6308 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6309 rollback to start_update_relation;
6310 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
6311 SUBSTR (SQLERRM, 1, 240) );
6312 FND_MSG_PUB.Add;
6313 FND_MSG_PUB.Count_And_Get (
6314 p_count => p_msg_count,
6315 p_data => p_msg_data);
6316 FND_MSG_PUB.Count_And_Get (
6317 p_count => p_msg_count,
6318 p_data => p_msg_data);
6319 end UPDATE_REGION_RELATION;
6320
6321 --=======================================================
6322 -- Procedure CHECK_DISPLAY_SEQUENCE
6323 --
6324 -- Usage Private API for making sure that the
6325 -- display sequence is unique for a given flow code.
6326 --
6327 -- Desc This API updates a page region, if necessary
6328 --
6329 -- Results The API returns the standard p_return_status parameter
6330 -- indicating one of the standard return statuses :
6331 -- * Unexpected error
6332 -- * Error
6333 -- * Success
6334 -- Parameters Page Region columns
6335 --
6336 -- Version Initial version number = 1.0
6337 -- History Current version number = 1.0
6338 --=======================================================
6339 procedure CHECK_DISPLAY_SEQUENCE ( p_validation_level IN NUMBER,
6340 p_flow_code IN VARCHAR2,
6341 p_flow_application_id IN NUMBER,
6342 p_page_code IN VARCHAR2,
6343 p_page_application_id IN NUMBER,
6344 p_region_code IN VARCHAR2,
6345 p_region_application_id IN NUMBER,
6346 p_display_sequence IN NUMBER,
6347 p_return_status OUT NOCOPY VARCHAR2,
6348 p_msg_count OUT NOCOPY NUMBER,
6349 p_msg_data OUT NOCOPY VARCHAR2,
6350 p_pass IN NUMBER,
6351 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
6352 ) is
6353 cursor l_fpr_csr( flow_code_param IN VARCHAR2,
6354 flow_application_id_param IN NUMBER,
6355 page_code_param IN VARCHAR2,
6356 page_application_id_param IN NUMBER,
6357 display_sequence_param IN NUMBER) is
6358 select *
6359 from ak_flow_page_regions
6360 where flow_code = flow_code_param
6361 and flow_application_id = flow_application_id_param
6362 and page_code = page_code_param
6363 and page_application_id = page_application_id_param
6364 and display_sequence = display_sequence_param;
6365
6366 l_api_name CONSTANT varchar2(30) := 'Check_Display_Sequence';
6367 l_new_display_sequence NUMBER;
6368 l_return_status VARCHAR2(1);
6369 l_fpr_rec ak_flow_page_regions%ROWTYPE;
6370 l_orig_fpr_rec ak_flow_page_regions%ROWTYPE;
6371
6372 begin
6373 l_return_status := FND_API.G_RET_STS_SUCCESS;
6374 open l_fpr_csr( p_flow_code,
6375 p_flow_application_id,
6376 p_page_code,
6377 p_page_application_id,
6378 p_display_sequence);
6379 fetch l_fpr_csr into l_fpr_rec;
6380
6381 --** Does it exists?
6382 if (l_fpr_csr%found) then
6383 if ((l_fpr_rec.region_code <> p_region_code) or
6384 (l_fpr_rec.region_application_id <> p_region_application_id)) then
6385
6386 --** Save it.
6387 l_orig_fpr_rec := l_fpr_rec;
6388
6389 --** Bump up the display sequence value of the page regions record
6390 l_new_display_sequence := p_display_sequence + 1000000;
6391 close l_fpr_csr;
6392 open l_fpr_csr( p_flow_code,
6393 p_flow_application_id,
6394 p_page_code,
6395 p_page_application_id,
6396 l_new_display_sequence);
6397 fetch l_fpr_csr into l_fpr_rec;
6398
6399 --** Keep looping until you can't find a record.
6400 while (l_fpr_csr%found) loop
6401 close l_fpr_csr;
6402 l_new_display_sequence := l_new_display_sequence + 1;
6403 open l_fpr_csr( p_flow_code,
6404 p_flow_application_id,
6405 p_page_code,
6406 p_page_application_id,
6407 l_new_display_sequence);
6408 fetch l_fpr_csr into l_fpr_rec;
6409 end loop;
6410
6411 --** ASSUMPTION: You have found a unique sequence number for this flow_code + page_code combination.
6412 AK_FLOW_PVT.UPDATE_PAGE_REGION (
6413 p_validation_level => p_validation_level,
6414 p_api_version_number => 1.0,
6415 p_msg_count => p_msg_count,
6416 p_msg_data => p_msg_data,
6417 p_return_status => l_return_status,
6418 p_flow_application_id => l_orig_fpr_rec.flow_application_id,
6419 p_flow_code => l_orig_fpr_rec.flow_code,
6420 p_page_application_id => l_orig_fpr_rec.page_application_id,
6421 p_page_code => l_orig_fpr_rec.page_code,
6422 p_region_application_id => l_orig_fpr_rec.region_application_id,
6423 p_region_code => l_orig_fpr_rec.region_code,
6424 p_display_sequence => l_new_display_sequence,
6425 p_region_style => l_orig_fpr_rec.region_style,
6426 p_num_columns => l_orig_fpr_rec.num_columns,
6427 p_parent_region_application_id => l_orig_fpr_rec.parent_region_application_id,
6428 p_parent_region_code => l_orig_fpr_rec.parent_region_code,
6429 p_icx_custom_call => l_orig_fpr_rec.icx_custom_call,
6430
6431 p_pass => p_pass,
6432 p_copy_redo_flag => p_copy_redo_flag);
6433 end if;
6434 end if;
6435
6436 p_return_status := l_return_status;
6437 close l_fpr_csr;
6438
6439 EXCEPTION
6440 WHEN FND_API.G_EXC_ERROR THEN
6441 p_return_status := FND_API.G_RET_STS_ERROR;
6442 WHEN OTHERS THEN
6443 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6444 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
6445 SUBSTR (SQLERRM, 1, 240) );
6446 FND_MSG_PUB.Add;
6447
6448 end CHECK_DISPLAY_SEQUENCE;
6449
6450 end AK_FLOW_PVT;