[Home] [Help]
PACKAGE BODY: APPS.AK_DOWNLOAD_GRP
Source
1 package body AK_DOWNLOAD_GRP as
2 /* $Header: akgdlodb.pls 120.3 2006/01/25 15:32:15 tshort ship $ */
3
4 procedure DOWNLOAD (
5 p_business_object IN VARCHAR2,
6 p_appl_short_name IN VARCHAR2,
7 p_primary_key IN VARCHAR2 := FND_API.G_MISS_CHAR,
8 p_return_status OUT NOCOPY VARCHAR2,
9 p_level IN VARCHAR2 := FND_API.G_MISS_CHAR,
10 p_levelpk IN VARCHAR2 := FND_API.G_MISS_CHAR,
11 p_download_attr IN VARCHAR2 := 'Y',
12 p_download_reg IN VARCHAR2 := 'Y'
13 ) is
14 cursor l_get_appl_id_csr (short_name_param varchar2) is
15 select application_id
16 from fnd_application_vl
17 where application_short_name = short_name_param;
18 cursor l_check_table_csr (session_id_param number) is
19 select tbl_index
20 from ak_loader_temp
21 where tbl_index = 1
22 and session_id = session_id_param;
23 cursor l_get_custom_list_csr(appl_id_param number, custom_code_param varchar2) is
24 select ac.customization_application_id, ac.customization_code,
25 ac.region_application_id, ac.region_code
26 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
27 where ac.region_application_id = appl_id_param
28 and ac.region_code like custom_code_param
29 and ac.region_application_id = ar.region_application_id
30 and ac.region_code = ar.region_code;
31 cursor l_get_custom_resp_list_csr(appl_id_param number, custom_code_param varchar2, levelpk number) is
32 select ac.customization_application_id, ac.customization_code,
33 ac.region_application_id, ac.region_code
34 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
35 where ac.region_application_id = appl_id_param
36 and ac.region_code like custom_code_param
37 and ac.region_application_id = ar.region_application_id
38 and ac.region_code = ar.region_code
39 and ac.responsibility_id = levelpk;
40 cursor l_get_custom_resp_list2_csr(appl_id_param number, custom_code_param varchar2) is
41 select ac.customization_application_id, ac.customization_code,
42 ac.region_application_id, ac.region_code
43 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
44 where ac.region_application_id = appl_id_param
45 and ac.region_code like custom_code_param
46 and ac.region_application_id = ar.region_application_id
47 and ac.region_code = ar.region_code
48 and ac.responsibility_id is not null;
49 cursor l_get_custom_org_list_csr(appl_id_param number, custom_code_param varchar2, levelpk number) is
50 select ac.customization_application_id, ac.customization_code,
51 ac.region_application_id, ac.region_code
52 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
53 where ac.region_application_id = appl_id_param
54 and ac.region_code like custom_code_param
55 and ac.region_application_id = ar.region_application_id
56 and ac.region_code = ar.region_code
57 and ac.org_id = levelpk;
58 cursor l_get_custom_org_list2_csr(appl_id_param number, custom_code_param varchar2) is
59 select ac.customization_application_id, ac.customization_code,
60 ac.region_application_id, ac.region_code
61 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
62 where ac.region_application_id = appl_id_param
63 and ac.region_code like custom_code_param
64 and ac.region_application_id = ar.region_application_id
65 and ac.region_code = ar.region_code
66 and ac.org_id is not null;
67 cursor l_get_custom_fun_list_csr(appl_id_param number, custom_code_param varchar2, levelpk varchar2) is
68 select ac.customization_application_id, ac.customization_code,
69 ac.region_application_id, ac.region_code
70 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
71 where ac.region_application_id = appl_id_param
72 and ac.region_code like custom_code_param
73 and ac.region_application_id = ar.region_application_id
74 and ac.region_code = ar.region_code
75 and ac.function_name = levelpk;
76 cursor l_get_custom_fun_list2_csr(appl_id_param number, custom_code_param varchar2) is
77 select ac.customization_application_id, ac.customization_code,
78 ac.region_application_id, ac.region_code
79 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
80 where ac.region_application_id = appl_id_param
81 and ac.region_code like custom_code_param
82 and ac.region_application_id = ar.region_application_id
83 and ac.region_code = ar.region_code
84 and ac.function_name is not null;
85 cursor l_get_custom_local_list_csr(appl_id_param number, custom_code_param varchar2, levelpk varchar2) is
86 select ac.customization_application_id, ac.customization_code,
87 ac.region_application_id, ac.region_code
88 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
89 where ac.region_application_id = appl_id_param
90 and ac.region_code like custom_code_param
91 and ac.region_application_id = ar.region_application_id
92 and ac.region_code = ar.region_code
93 and ac.localization_code = levelpk;
94 cursor l_get_custom_local_list2_csr(appl_id_param number, custom_code_param varchar2) is
95 select ac.customization_application_id, ac.customization_code,
96 ac.region_application_id, ac.region_code
97 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
98 where ac.region_application_id = appl_id_param
99 and ac.region_code like custom_code_param
100 and ac.region_application_id = ar.region_application_id
101 and ac.region_code = ar.region_code
102 and ac.localization_code is not null;
103 cursor l_get_custom_site_list_csr(appl_id_param number, custom_code_param varchar2, levelpk number) is
104 select ac.customization_application_id, ac.customization_code,
105 ac.region_application_id, ac.region_code
106 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
107 where ac.region_application_id = appl_id_param
108 and ac.region_code like custom_code_param
109 and ac.region_application_id = ar.region_application_id
110 and ac.region_code = ar.region_code
111 and ac.site_id = levelpk;
112 cursor l_get_custom_site_list2_csr(appl_id_param number, custom_code_param varchar2) is
113 select ac.customization_application_id, ac.customization_code,
114 ac.region_application_id, ac.region_code
115 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
116 where ac.region_application_id = appl_id_param
117 and ac.region_code like custom_code_param
118 and ac.region_application_id = ar.region_application_id
119 and ac.region_code = ar.region_code
120 and ac.site_id is not null;
121 cursor l_get_org_id(levelpk varchar2) is
122 select organization_id
123 from MTL_PARAMETERS
124 where organization_code = levelpk;
125 cursor l_get_resp_id(levelpk varchar2) is
126 select responsibility_id
127 from FND_RESPONSIBILITY
128 where responsibility_key = levelpk;
129 cursor l_check_percent(objectpk varchar2) is
130 select instr(objectpk,'%')
131 from dual;
132 cursor l_get_like_region_csr (appl_id_param number, region_code_param varchar2) is
133 select region_application_id, region_code
134 from ak_regions
135 where region_application_id = appl_id_param
136 and region_code like region_code_param;
137 cursor l_get_like_flow_csr (appl_id_param number, flow_code_param varchar2) is
138 select flow_application_id, flow_code
139 from ak_flows
140 where flow_application_id = appl_id_param
141 and flow_code like flow_code_param;
142 cursor l_get_like_object_csr (database_obj_param varchar2) is
143 select database_object_name
144 from ak_objects
145 where database_object_name like database_obj_param;
146
147 l_api_name CONSTANT varchar2(30) := 'Download';
148 l_appl_short_name varchar2(30);
149 l_appl_id NUMBER;
150 l_buffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
151 l_by_object varchar2(30);
152 l_index number := 0;
153 l_index2 number;
154 l_msg_count number;
155 l_msg_data varchar2(2000);
156 l_return_boolean BOOLEAN;
157 l_return_status VARCHAR2(1);
158 l_string_pos number;
159 l_table_index number;
160 l_dum NUMBER;
161 l_object_pk varchar2(240) := FND_API.G_MISS_CHAR;
162 l_flow_pk_tbl AK_FLOW_PUB.Flow_PK_Tbl_Type;
163 l_region_pk_tbl AK_REGION_PUB.Region_PK_Tbl_Type;
164 l_custom_pk_tbl AK_CUSTOM_PUB.Custom_PK_Tbl_Type;
165 l_object_pk_tbl AK_OBJECT_PUB.Object_PK_Tbl_Type;
166 l_sec_pk_tbl AK_SECURITY_PUB.Resp_PK_Tbl_Type;
167 l_attr_pk_tbl AK_ATTRIBUTE_PUB.Attribute_PK_Tbl_Type;
168 l_queryobj_pk_tbl AK_QUERYOBJ_PUB.QueryObj_PK_Tbl_Type;
169 l_amparamreg_pk_tbl AK_AMPARAM_REGISTRY_PUB.AmParamReg_Pk_Tbl_Type;
170 l_session_id NUMBER;
171 l_level_pk VARCHAR2(30);
172 l_level_id_pk NUMBER;
173 l_percent NUMBER;
174
175 begin
176 --
177 -- Get object to be downloaded from argument
178 --
179 l_by_object := p_business_object;
180 --
181 -- Get application short name from argument
182 --
183 l_appl_short_name := upper(p_appl_short_name);
184
185 --
186 -- Get Primary key for the object
187 --
188 if (p_primary_key is not null and p_primary_key <> FND_API.G_MISS_CHAR) then
189 l_object_pk := p_primary_key;
190 end if;
191
192 --
193 -- Set what error messages are to be displayed
194 --
195 --FND_MSG_PUB.G_MSG_LEVEL_THRESHOLD := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
196 FND_MSG_PUB.G_MSG_LEVEL_THRESHOLD := FND_MSG_PUB.G_MSG_LVL_ERROR;
197
198 -- Get application id from application short name
199 --
200 open l_get_appl_id_csr(l_appl_short_name);
201 fetch l_get_appl_id_csr into l_appl_id;
202 if (l_get_appl_id_csr%notfound) then
203 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
204 FND_MESSAGE.SET_NAME('AK','AK_APPL_SHORT_NAME_INVALID');
205 FND_MESSAGE.SET_TOKEN('APPL_SHORT_NAME', l_appl_short_name);
206 FND_MSG_PUB.Add;
207 end if;
208 close l_get_appl_id_csr;
209 raise FND_API.G_EXC_ERROR;
210 end if;
211 close l_get_appl_id_csr;
212
213 -- set Loading mode
214 AK_ON_OBJECTS_PUB.G_LOAD_MODE := 'DOWNLOAD';
215
216 /** retreive the sessio id **/
217 select sid into l_session_id
218 from v$session
219 where AUDSID = userenv('SESSIONID');
220
221 AK_ON_OBJECTS_PVT.G_SESSION_ID := l_session_id;
222
223 -- set G_WRITE_HEADER to indicate writing the header or not
224 --
225 open l_check_table_csr(l_session_id);
226 fetch l_check_table_csr into l_dum;
227 if l_check_table_csr%NOTFOUND then
228 G_WRITE_HEADER := TRUE;
229 else
230 G_WRITE_HEADER := FALSE;
231 end if;
232 close l_check_table_csr;
233
234 if p_download_attr = 'Y' then
235 G_DOWNLOAD_ATTR := 'Y';
236 else
237 G_DOWNLOAD_ATTR := 'N';
238 end if;
239
240 if p_download_reg = 'Y' then
241 G_DOWNLOAD_REG := 'Y';
242 else
243 G_DOWNLOAD_REG := 'N';
244 end if;
245 --
246 -- Load buffer table with log file heading info to be written
247 -- to the log file
248 --
249 l_index := 1;
250 l_buffer_tbl(l_index) := '**********';
251
252 l_index := l_index + 1;
253 FND_MESSAGE.SET_NAME('AK','AK_START_DOWNLOAD_SESSION');
254 l_buffer_tbl(l_index) := FND_MESSAGE.GET;
255 l_index := l_index + 1;
256 l_buffer_tbl(l_index) := to_char(sysdate, 'DY MON DD YYYY HH24:MI:SS');
257 l_index := l_index + 1;
258 l_buffer_tbl(l_index) := '**********';
259 l_index := l_index + 1;
260 l_buffer_tbl(l_index) := ' ';
261
262 --dbms_output.put_line('Begin ' || l_by_object || ' at:' ||
263 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
264 --
265 -- Write heading info to a log file
266 --
267 AK_ON_OBJECTS_PVT.WRITE_LOG_FILE (
268 p_return_status => l_return_status,
269 p_buffer_tbl => l_buffer_tbl,
270 p_write_mode => AK_ON_OBJECTS_PUB.G_OVERWRITE
271 );
272
273 --
274 -- Clean up buffer table for use by other messages later
275 --
276 -- l_buffer_tbl.delete;
277
278 -- Initialize loader table index
279 if (G_WRITE_HEADER) then
280 AK_ON_OBJECTS_PVT.G_TBL_INDEX := 0;
281 end if;
282
283 --
284 -- Download data from database
285 --
286 if (upper(l_by_object) = 'FLOW') then
287 if (l_object_pk is not null and l_object_pk <> FND_API.G_MISS_CHAR) then
288 open l_check_percent(l_object_pk);
289 fetch l_check_percent into l_percent;
290 if l_percent <> 0 then
291 l_index := 1;
292 open l_get_like_flow_csr(l_appl_id, l_object_pk);
293 loop
294 fetch l_get_like_flow_csr into
295 l_flow_pk_tbl(l_index).flow_appl_id,
296 l_flow_pk_tbl(l_index).flow_code;
297 if (l_get_like_flow_csr%notfound and l_index = 1) then
298 l_flow_pk_tbl(1).flow_appl_id := l_appl_id;
299 l_flow_pk_tbl(1).flow_code := l_object_pk;
300 end if;
301 l_index := l_index + 1;
302 exit when l_get_like_flow_csr%notfound;
303 end loop;
304 close l_get_like_flow_csr;
305 else
306 l_flow_pk_tbl(1).flow_appl_id := l_appl_id;
307 l_flow_pk_tbl(1).flow_code := l_object_pk;
308 end if;
309 close l_check_percent;
310 end if;
311 AK_FLOW_GRP.DOWNLOAD_FLOW (
312 --p_validation_level => FND_API.G_VALID_LEVEL_NONE,
313 p_api_version_number => 1.0,
314 p_init_msg_tbl => TRUE,
315 p_msg_count => l_msg_count,
316 p_msg_data => l_msg_data,
317 p_return_status => l_return_status,
318 p_application_id => l_appl_id,
319 p_application_short_name => upper(l_appl_short_name),
320 p_flow_pk_tbl => l_flow_pk_tbl
321 );
322
323 elsif (upper(l_by_object) = 'REGION') then
324 if (l_object_pk is not null and l_object_pk <> FND_API.G_MISS_CHAR) then
325 open l_check_percent(l_object_pk);
326 fetch l_check_percent into l_percent;
327 if l_percent <> 0 then
328 l_index := 1;
329 open l_get_like_region_csr(l_appl_id, l_object_pk);
330 loop
331 fetch l_get_like_region_csr into
332 l_region_pk_tbl(l_index).region_appl_id,
333 l_region_pk_tbl(l_index).region_code;
334 if (l_get_like_region_csr%notfound and l_index = 1) then
335 l_region_pk_tbl(1).region_appl_id := l_appl_id;
336 l_region_pk_tbl(1).region_code := l_object_pk;
337 end if;
338 l_index := l_index + 1;
339 exit when l_get_like_region_csr%notfound;
340 end loop;
341 close l_get_like_region_csr;
342 else
343 l_region_pk_tbl(1).region_appl_id := l_appl_id;
344 l_region_pk_tbl(1).region_code := l_object_pk;
345 end if;
346 close l_check_percent;
347 end if;
348 AK_REGION_GRP.DOWNLOAD_REGION (
349 -- p_validation_level => FND_API.G_VALID_LEVEL_NONE,
350 p_api_version_number => 1.0,
351 p_init_msg_tbl => TRUE,
352 p_msg_count => l_msg_count,
353 p_msg_data => l_msg_data,
354 p_return_status => l_return_status,
355 p_application_id => l_appl_id,
356 p_application_short_name => upper(l_appl_short_name),
357 p_region_pk_tbl => l_region_pk_tbl
358 );
359
360 elsif (upper(l_by_object) = 'CUSTOM_REGION') then
361 if (l_object_pk is not null and l_object_pk <> FND_API.G_MISS_CHAR) then
362 l_index := 1;
363 if (p_level is null or p_level = FND_API.G_MISS_CHAR) then
364 open l_get_custom_list_csr(l_appl_id, l_object_pk);
365 loop
366 fetch l_get_custom_list_csr into
367 l_custom_pk_tbl(l_index).custom_appl_id,
368 l_custom_pk_tbl(l_index).custom_code,
369 l_custom_pk_tbl(l_index).region_appl_id,
370 l_custom_pk_tbl(l_index).region_code;
371 if (l_get_custom_list_csr%notfound and l_index = 1) then
372 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
373 l_custom_pk_tbl(1).region_code := l_object_pk;
374 end if;
375 l_index := l_index + 1;
376 exit when l_get_custom_list_csr%notfound;
377 end loop;
378 close l_get_custom_list_csr;
379 elsif (p_level = 'RESPONSIBILITY') then
380 if (p_levelpk is not null and p_levelpk <> FND_API.G_MISS_CHAR) then
381 open l_get_resp_id(p_levelpk);
382 fetch l_get_resp_id into l_level_id_pk;
383 if (l_get_resp_id%notfound) then
384 l_level_id_pk := null;
385 -- if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
386 -- FND_MESSAGE.SET_NAME('AK','AK_RESP_IS_NOT_VALID');
387 -- FND_MSG_PUB.Add;
388 -- end if;
389 -- RAISE FND_API.G_EXC_ERROR;
390 end if;
391 close l_get_resp_id;
392 open l_get_custom_resp_list_csr(l_appl_id, l_object_pk, l_level_id_pk);
393 loop
394 fetch l_get_custom_resp_list_csr into
395 l_custom_pk_tbl(l_index).custom_appl_id,
396 l_custom_pk_tbl(l_index).custom_code,
397 l_custom_pk_tbl(l_index).region_appl_id,
398 l_custom_pk_tbl(l_index).region_code;
399 if (l_get_custom_resp_list_csr%notfound and l_index = 1) then
400 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
401 l_custom_pk_tbl(1).region_code := l_object_pk;
402 end if;
403 l_index := l_index + 1;
404 exit when l_get_custom_resp_list_csr%notfound;
405 end loop;
406 close l_get_custom_resp_list_csr;
407 else
408 open l_get_custom_resp_list2_csr(l_appl_id, l_object_pk);
409 loop
410 fetch l_get_custom_resp_list2_csr into
411 l_custom_pk_tbl(l_index).custom_appl_id,
412 l_custom_pk_tbl(l_index).custom_code,
413 l_custom_pk_tbl(l_index).region_appl_id,
414 l_custom_pk_tbl(l_index).region_code;
415 if (l_get_custom_resp_list2_csr%notfound and l_index = 1) then
416 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
417 l_custom_pk_tbl(1).region_code := l_object_pk;
418 end if;
419 l_index := l_index + 1;
420 exit when l_get_custom_resp_list2_csr%notfound;
421 end loop;
422 close l_get_custom_resp_list2_csr;
423 end if;
424 elsif (p_level = 'ORGANIZATION') then
425 if (p_levelpk is not null and p_levelpk <> FND_API.G_MISS_CHAR) then
426 open l_get_org_id(p_levelpk);
427 fetch l_get_org_id into l_level_id_pk;
428 if (l_get_org_id%notfound) then
429 l_level_id_pk := null;
430 -- if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
431 -- FND_MESSAGE.SET_NAME('AK','AK_ORG_IS_NOT_VALID');
432 -- FND_MSG_PUB.Add;
433 -- end if;
434 -- RAISE FND_API.G_EXC_ERROR;
435 end if;
436 close l_get_org_id;
437 open l_get_custom_org_list_csr(l_appl_id, l_object_pk, l_level_id_pk);
438 loop
439 fetch l_get_custom_org_list_csr into
440 l_custom_pk_tbl(l_index).custom_appl_id,
441 l_custom_pk_tbl(l_index).custom_code,
442 l_custom_pk_tbl(l_index).region_appl_id,
443 l_custom_pk_tbl(l_index).region_code;
444 if (l_get_custom_org_list_csr%notfound and l_index = 1) then
445 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
446 l_custom_pk_tbl(1).region_code := l_object_pk;
447 end if;
448 l_index := l_index + 1;
449 exit when l_get_custom_org_list_csr%notfound;
450 end loop;
451 close l_get_custom_org_list_csr;
452 else
453 open l_get_custom_org_list2_csr(l_appl_id, l_object_pk);
454 loop
455 fetch l_get_custom_org_list2_csr into
456 l_custom_pk_tbl(l_index).custom_appl_id,
457 l_custom_pk_tbl(l_index).custom_code,
458 l_custom_pk_tbl(l_index).region_appl_id,
459 l_custom_pk_tbl(l_index).region_code;
460 if (l_get_custom_org_list2_csr%notfound and l_index = 1) then
461 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
462 l_custom_pk_tbl(1).region_code := l_object_pk;
463 end if;
464 l_index := l_index + 1;
465 exit when l_get_custom_org_list2_csr%notfound;
466 end loop;
467 close l_get_custom_org_list2_csr;
468 end if;
469 elsif (p_level = 'FUNCTION') then
470 if (p_levelpk is not null and p_levelpk <> FND_API.G_MISS_CHAR) then
471 open l_get_custom_fun_list_csr(l_appl_id, l_object_pk, p_levelpk);
472 loop
473 fetch l_get_custom_fun_list_csr into
474 l_custom_pk_tbl(l_index).custom_appl_id,
475 l_custom_pk_tbl(l_index).custom_code,
476 l_custom_pk_tbl(l_index).region_appl_id,
477 l_custom_pk_tbl(l_index).region_code;
478 if (l_get_custom_fun_list_csr%notfound and l_index = 1) then
479 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
480 l_custom_pk_tbl(1).region_code := l_object_pk;
481 end if;
482 l_index := l_index + 1;
483 exit when l_get_custom_fun_list_csr%notfound;
484 end loop;
485 close l_get_custom_fun_list_csr;
486 else
487 open l_get_custom_fun_list2_csr(l_appl_id, l_object_pk);
488 loop
489 fetch l_get_custom_fun_list2_csr into
490 l_custom_pk_tbl(l_index).custom_appl_id,
491 l_custom_pk_tbl(l_index).custom_code,
492 l_custom_pk_tbl(l_index).region_appl_id,
493 l_custom_pk_tbl(l_index).region_code;
494 if (l_get_custom_fun_list2_csr%notfound and l_index = 1) then
495 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
496 l_custom_pk_tbl(1).region_code := l_object_pk;
497 end if;
498 l_index := l_index + 1;
499 exit when l_get_custom_fun_list2_csr%notfound;
500 end loop;
501 close l_get_custom_fun_list2_csr;
502 end if;
503 elsif (p_level = 'LOCALIZATION') then
504 if (p_levelpk is not null and p_levelpk <> FND_API.G_MISS_CHAR) then
505 open l_get_custom_local_list_csr(l_appl_id, l_object_pk, p_levelpk);
506 loop
507 fetch l_get_custom_local_list_csr into
508 l_custom_pk_tbl(l_index).custom_appl_id,
509 l_custom_pk_tbl(l_index).custom_code,
510 l_custom_pk_tbl(l_index).region_appl_id,
511 l_custom_pk_tbl(l_index).region_code;
512 if (l_get_custom_local_list_csr%notfound and l_index = 1) then
513 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
514 l_custom_pk_tbl(1).region_code := l_object_pk;
515 end if;
516 l_index := l_index + 1;
517 exit when l_get_custom_local_list_csr%notfound;
518 end loop;
519 close l_get_custom_local_list_csr;
520 else
521 open l_get_custom_local_list2_csr(l_appl_id, l_object_pk);
522 loop
523 fetch l_get_custom_local_list2_csr into
524 l_custom_pk_tbl(l_index).custom_appl_id,
525 l_custom_pk_tbl(l_index).custom_code,
526 l_custom_pk_tbl(l_index).region_appl_id,
527 l_custom_pk_tbl(l_index).region_code;
528 if (l_get_custom_local_list2_csr%notfound and l_index = 1) then
529 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
530 l_custom_pk_tbl(1).region_code := l_object_pk;
531 end if;
532 l_index := l_index + 1;
533 exit when l_get_custom_local_list2_csr%notfound;
534 end loop;
535 close l_get_custom_local_list2_csr;
536 end if;
537 elsif (p_level = 'SITE') then
538 if (p_levelpk is not null and p_levelpk <> FND_API.G_MISS_CHAR) then
539 open l_get_custom_site_list_csr(l_appl_id, l_object_pk, p_levelpk);
540 loop
541 fetch l_get_custom_site_list_csr into
542 l_custom_pk_tbl(l_index).custom_appl_id,
543 l_custom_pk_tbl(l_index).custom_code,
544 l_custom_pk_tbl(l_index).region_appl_id,
545 l_custom_pk_tbl(l_index).region_code;
546 if (l_get_custom_site_list_csr%notfound and l_index = 1) then
547 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
548 l_custom_pk_tbl(1).region_code := l_object_pk;
549 end if;
550 l_index := l_index + 1;
551 exit when l_get_custom_site_list_csr%notfound;
552 end loop;
553 close l_get_custom_site_list_csr;
554 else
555 open l_get_custom_site_list2_csr(l_appl_id, l_object_pk);
556 loop
557 fetch l_get_custom_site_list2_csr into
558 l_custom_pk_tbl(l_index).custom_appl_id,
559 l_custom_pk_tbl(l_index).custom_code,
560 l_custom_pk_tbl(l_index).region_appl_id,
561 l_custom_pk_tbl(l_index).region_code;
562 if (l_get_custom_site_list2_csr%notfound and l_index = 1) then
563 l_custom_pk_tbl(1).region_appl_id := l_appl_id;
564 l_custom_pk_tbl(1).region_code := l_object_pk;
565 end if;
566 l_index := l_index + 1;
567 exit when l_get_custom_site_list2_csr%notfound;
568 end loop;
569 close l_get_custom_site_list2_csr;
570 end if;
571 end if;
572 end if;
573 if (l_index > 1) then
574 AK_CUSTOM_GRP.DOWNLOAD_CUSTOM (
575 -- p_validation_level => FND_API.G_VALID_LEVEL_NONE,
576 p_api_version_number => 1.0,
577 p_init_msg_tbl => TRUE,
578 p_msg_count => l_msg_count,
579 p_msg_data => l_msg_data,
580 p_return_status => l_return_status,
581 p_application_id => l_appl_id,
582 p_application_short_name => upper(l_appl_short_name),
583 p_custom_pk_tbl => l_custom_pk_tbl,
584 p_level => p_level,
585 p_levelpk => p_levelpk
586 );
587 end if;
588
589 elsif (upper(l_by_object) = 'OBJECT') then
590 if (l_object_pk is not null and l_object_pk <> FND_API.G_MISS_CHAR) then
591 open l_check_percent(l_object_pk);
592 fetch l_check_percent into l_percent;
593 if l_percent <> 0 then
594 l_index := 1;
595 open l_get_like_object_csr(l_object_pk);
596 loop
597 fetch l_get_like_object_csr into
598 l_object_pk_tbl(l_index);
599 if (l_get_like_object_csr%notfound and l_index = 1) then
600 l_object_pk_tbl(1) := l_object_pk;
601 end if;
602 l_index := l_index + 1;
603 exit when l_get_like_object_csr%notfound;
604 end loop;
605 close l_get_like_object_csr;
606 else
607 l_object_pk_tbl(1) := l_object_pk;
608 end if;
609 close l_check_percent;
610 end if;
611
612 AK_OBJECT_GRP.DOWNLOAD_OBJECT (
613 -- p_validation_level => FND_API.G_VALID_LEVEL_NONE,
614 p_api_version_number => 1.0,
615 p_init_msg_tbl => TRUE,
616 p_msg_count => l_msg_count,
617 p_msg_data => l_msg_data,
618 p_return_status => l_return_status,
619 p_application_id => l_appl_id,
620 p_application_short_name => upper(l_appl_short_name),
621 p_object_pk_tbl => l_object_pk_tbl
622 );
623
624 -- percent code didn't work here, it's in akdvatrb.pls
625 elsif (upper(l_by_object) = 'ATTRIBUTE') then
626 if (l_object_pk is not null and l_object_pk <> FND_API.G_MISS_CHAR) then
627 l_attr_pk_tbl(1).attribute_appl_id := l_appl_id;
628 l_attr_pk_tbl(1).attribute_code := l_object_pk;
629 end if;
630
631 AK_ATTRIBUTE_GRP.DOWNLOAD_ATTRIBUTE (
632 -- p_validation_level => FND_API.G_VALID_LEVEL_NONE,
633 p_api_version_number => 1.0,
634 p_init_msg_tbl => TRUE,
635 p_msg_count => l_msg_count,
636 p_msg_data => l_msg_data,
637 p_return_status => l_return_status,
638 p_application_id => l_appl_id,
639 p_application_short_name => upper(l_appl_short_name),
640 p_attribute_pk_tbl => l_attr_pk_tbl
641 );
642
643 elsif (upper(l_by_object) = 'SECURITY') then
644 if (l_object_pk is not null and l_object_pk <> FND_API.G_MISS_CHAR) then
645 l_sec_pk_tbl(1).responsibility_id := to_number(l_object_pk);
646 l_sec_pk_tbl(1).responsibility_appl_id := l_appl_id;
647 end if;
648 AK_SECURITY_GRP.DOWNLOAD_RESP (
649 -- p_validation_level => FND_API.G_VALID_LEVEL_NONE,
650 p_api_version_number => 1.0,
651 p_init_msg_tbl => TRUE,
652 p_msg_count => l_msg_count,
653 p_msg_data => l_msg_data,
654 p_return_status => l_return_status,
655 p_application_id => l_appl_id,
656 p_application_short_name => upper(l_appl_short_name),
657 p_excluded_pk_tbl => l_sec_pk_tbl,
658 p_resp_pk_tbl => l_sec_pk_tbl
659 );
660
661 elsif (upper(l_by_object) = 'QUERYOBJ') then
662 if (l_object_pk is not null and l_object_pk <> FND_API.G_MISS_CHAR) then
663 l_queryobj_pk_tbl(1).query_code := l_object_pk;
664 end if;
665 AK_QUERYOBJ_GRP.DOWNLOAD_QUERY_OBJECT (
666 p_api_version_number => 1.0,
667 p_init_msg_tbl => TRUE,
668 p_msg_count => l_msg_count,
669 p_msg_data => l_msg_data,
670 p_return_status => l_return_status,
671 p_application_id => l_appl_id,
672 p_application_short_name => upper(l_appl_short_name),
673 p_queryobj_pk_tbl => l_queryobj_pk_tbl
674 );
675
676 elsif (upper(l_by_object) = 'AMPARAM_REGISTRY') then
677 if (l_object_pk is not null and l_object_pk <> FND_API.G_MISS_CHAR) then
678 l_amparamreg_pk_tbl(1).applicationmodule_defn_name := l_object_pk;
679 end if;
680 AK_AMPARAM_REGISTRY_GRP.DOWNLOAD_AMPARAM_REGISTRY (
681 p_api_version_number => 1.0,
682 p_init_msg_tbl => TRUE,
683 p_msg_count => l_msg_count,
684 p_msg_data => l_msg_data,
685 p_return_status => l_return_status,
686 p_application_id => l_appl_id,
687 p_application_short_name => upper(l_appl_short_name),
688 p_amparamreg_pk_tbl => l_amparamreg_pk_tbl
689 );
690
691 else
692 --dbms_output.put_line(upper(l_by_object) ||
693 -- ' is invalid - it must be FLOW, REGION, OBJECT, ATTRIBUTE or SECURITY');
694 FND_MESSAGE.SET_NAME('AK','AK_INVALID_BUSINESS_OBJECT');
695 FND_MESSAGE.SET_TOKEN('INVALID',l_by_object);
696 FND_MSG_PUB.Add;
697 end if;
698
699 p_return_status := l_return_status;
700 --dbms_output.put_line('Finish downloading at:' ||
701 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
702
703 --dbms_output.put_line('Return status is: ' || l_return_status);
704 --dbms_output.put_line('Return message: ' || l_msg_data);
705
706 if FND_MSG_PUB.Count_Msg > 0 then
707 FND_MSG_PUB.Reset;
708 --dbms_output.put_line('Messages: ');
709 for i in 1 .. FND_MSG_PUB.Count_Msg loop
710 l_buffer_tbl(i + l_index) := FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
711 end loop;
712 FND_MSG_PUB.Initialize;
713 end if;
714
715 --
716 -- Add ending to log file
717 --
718 l_index := nvl(l_buffer_tbl.last,0) + 1;
719 l_buffer_tbl(l_index) := '**********';
720 l_index := l_index + 1;
721 l_buffer_tbl(l_index) := to_char(sysdate, 'DY MON DD YYYY HH24:MI:SS');
722 l_index := l_index + 1;
723 l_buffer_tbl(l_index) := 'Finished processing application: '||l_appl_short_name;
724 if (p_primary_key is not null and p_primary_key <> FND_API.G_MISS_CHAR) then
725 l_index := l_index + 1;
726 l_buffer_tbl(l_index) := 'Primary key: '||p_primary_key;
727 end if;
728 l_index := l_index + 1;
729 FND_MESSAGE.SET_NAME('AK','AK_END_DOWNLOAD_SESSION');
730 l_buffer_tbl(l_index) := FND_MESSAGE.GET;
731
732 --
733 -- Write all messages and ending to a log file
734 --
735
736 AK_ON_OBJECTS_PVT.WRITE_LOG_FILE (
737 p_return_status => l_return_status,
738 p_buffer_tbl => l_buffer_tbl,
739 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
740 );
741
742 --if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
743 -- dbms_output.put_line('Log file has been printed out to screen');
744 --else
745 -- dbms_output.put_line('Failed to write log file to Global PL/SQL table');
746 --end if;
747
748 EXCEPTION
749 WHEN FND_API.G_EXC_ERROR THEN
750 p_return_status := FND_API.G_RET_STS_ERROR;
751 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
752 SUBSTR (SQLERRM, 1, 240)||' exec error' );
753 WHEN NO_DATA_FOUND THEN
754 p_return_status := FND_API.G_RET_STS_ERROR;
755 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
756 SUBSTR (SQLERRM, 1, 240)||' no data found' );
757 WHEN OTHERS THEN
758 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
759 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
760 SUBSTR (SQLERRM, 1, 240) );
761 if FND_MSG_PUB.Count_Msg > 0 then
762 FND_MSG_PUB.Reset;
763 --dbms_output.put_line('Messages: ');
764 for i in 1 .. FND_MSG_PUB.Count_Msg loop
765 l_buffer_tbl(i + l_index) := FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
766 end loop;
767 FND_MSG_PUB.Initialize;
768 end if;
769
770 --
771 -- Add ending to log file
772 --
773 l_index := nvl(l_buffer_tbl.last,0) + 1;
774 l_buffer_tbl(l_index) := '**********';
775 l_index := l_index + 1;
776 l_buffer_tbl(l_index) := to_char(sysdate, 'DY MON DD YYYY HH24:MI:SS');
777 l_index := l_index + 1;
778 l_buffer_tbl(l_index) := 'Finished processing application: '||l_appl_short_name;
779 if (p_primary_key is not null and p_primary_key <> FND_API.G_MISS_CHAR) then
780 l_index := l_index + 1;
781 l_buffer_tbl(l_index) := 'Primary key: '||p_primary_key;
782 end if;
783 l_index := l_index + 1;
784 FND_MESSAGE.SET_NAME('AK','AK_END_DOWNLOAD_SESSION');
785 l_buffer_tbl(l_index) := FND_MESSAGE.GET;
786 --
787 -- Write all messages and ending to a log file
788 --
789
790 AK_ON_OBJECTS_PVT.WRITE_LOG_FILE (
791 p_return_status => l_return_status,
792 p_buffer_tbl => l_buffer_tbl,
793 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
794 );
795
796 end DOWNLOAD;
797
798 end AK_DOWNLOAD_GRP;