1 package body AK_CUSTOM_PVT as
2 /* $Header: akdvcreb.pls 120.5 2006/04/14 13:52:05 tshort noship $ */
3
4 --=======================================================
5 -- Procedure DOWNLOAD_CUSTOM
6 --
7 -- Usage Private API for downloading customizations. This
8 -- API should only be called by other APIs that are
9 -- owned by the Core Modules Team (AK).
10 --
11 -- Desc This API will extract the customizations selected
12 -- by application ID or by key values from the
13 -- database to the output file.
14 -- If a region is selected for writing to the loader
15 -- file, all its children records (including region items)
16 -- will also be written.
17 --
18 -- Results The API returns the standard p_return_status parameter
19 -- indicating one of the standard return statuses :
20 -- * Unexpected error
21 -- * Error
22 -- * Success
23 -- Parameters
24 -- p_nls_language : IN optional
25 -- NLS language for database. If none if given,
26 -- the current NLS language will be used.
27 -- p_get_object_flag : IN required
28 -- Call DOWNLOAD_OBJECT API to extract objects that
29 -- are referenced by the regions that will be extracted
30 -- by this API if this parameter is 'Y'.
31 --
32 -- One of the following parameters must be provided:
33 --
34 -- p_application_id : IN optional
35 -- If given, all attributes for this application ID
36 -- will be written to the output file.
37 -- p_application_id will be ignored if a table is
38 -- given in p_custom_pk_tbl.
39 -- p_custom_pk_tbl : IN optional
40 -- If given, only regions whose key values are
41 -- included in this table will be written to the
42 -- output file.
43 --
44 --
45 -- Version Initial version number = 1.0
46 -- History Current version number = 1.0
47 --=======================================================
48 procedure DOWNLOAD_CUSTOM (
49 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
50 p_api_version_number IN NUMBER,
51 p_return_status OUT NOCOPY VARCHAR2,
52 p_application_id IN NUMBER := FND_API.G_MISS_NUM,
53 p_custom_pk_tbl IN AK_CUSTOM_PUB.Custom_PK_Tbl_Type := AK_CUSTOM_PUB.G_MISS_CUSTOM_PK_TBL,
54 p_nls_language IN VARCHAR2,
55 p_get_object_flag IN VARCHAR2,
56 p_level IN VARCHAR2 := FND_API.G_MISS_CHAR,
57 p_levelpk IN VARCHAR2 := FND_API.G_MISS_CHAR
58 ) is
59 cursor l_get_custom_list_csr (application_id number, p_region_code varchar2) is
60 select ac.customization_application_id, ac.customization_code,
61 ac.region_application_id, ac.region_code
62 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
63 where ac.REGION_APPLICATION_ID = application_id
64 and ac.REGION_CODE = p_region_code
65 and ac.region_application_id = ar.region_application_id
66 and ac.region_code = ar.region_code;
67 cursor l_get_custom_resp_list_csr (application_id number, p_region_code varchar2, levelpk number) 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 = application_id
72 and ac.REGION_CODE = p_region_code
73 and ac.region_application_id = ar.region_application_id
74 and ac.region_code = ar.region_code
75 and ac.responsibility_id = levelpk;
76 cursor l_get_custom_resp_list2_csr (application_id number, p_region_code 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 = application_id
81 and ac.REGION_CODE = p_region_code
82 and ac.region_application_id = ar.region_application_id
83 and ac.region_code = ar.region_code
84 and ac.responsibility_id is not null;
85 cursor l_get_custom_org_list_csr (application_id number, p_region_code varchar2, levelpk number) 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 = application_id
90 and ac.REGION_CODE = p_region_code
91 and ac.region_application_id = ar.region_application_id
92 and ac.region_code = ar.region_code
93 and ac.org_id = levelpk;
94 cursor l_get_custom_org_list2_csr (application_id number, p_region_code 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 = application_id
99 and ac.REGION_CODE = p_region_code
100 and ac.region_application_id = ar.region_application_id
101 and ac.region_code = ar.region_code
102 and ac.org_id is not null;
103 cursor l_get_custom_site_list_csr (application_id number, p_region_code 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 = application_id
108 and ac.REGION_CODE = p_region_code
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 (application_id number, p_region_code 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 = application_id
117 and ac.REGION_CODE = p_region_code
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_custom_fun_list_csr (application_id number, p_region_code varchar2, levelpk varchar2) is
122 select ac.customization_application_id, ac.customization_code,
123 ac.region_application_id, ac.region_code
124 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
125 where ac.REGION_APPLICATION_ID = application_id
126 and ac.REGION_CODE = p_region_code
127 and ac.region_application_id = ar.region_application_id
128 and ac.region_code = ar.region_code
129 and ac.function_name = levelpk;
130 cursor l_get_custom_fun_list2_csr (application_id number, p_region_code varchar2) is
131 select ac.customization_application_id, ac.customization_code,
132 ac.region_application_id, ac.region_code
133 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
134 where ac.REGION_APPLICATION_ID = application_id
135 and ac.REGION_CODE = p_region_code
136 and ac.region_application_id = ar.region_application_id
137 and ac.region_code = ar.region_code
138 and ac.function_name is not null;
139 cursor l_get_custom_local_list_csr (application_id number, p_region_code varchar2, levelpk varchar2) is
140 select ac.customization_application_id, ac.customization_code,
141 ac.region_application_id, ac.region_code
142 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
143 where ac.REGION_APPLICATION_ID = application_id
144 and ac.REGION_CODE = p_region_code
145 and ac.region_application_id = ar.region_application_id
146 and ac.region_code = ar.region_code
147 and ac.localization_code = levelpk;
148 cursor l_get_custom_local_list2_csr (application_id number, p_region_code varchar2) is
149 select ac.customization_application_id, ac.customization_code,
150 ac.region_application_id, ac.region_code
151 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
152 where ac.REGION_APPLICATION_ID = application_id
153 and ac.REGION_CODE = p_region_code
154 and ac.region_application_id = ar.region_application_id
155 and ac.region_code = ar.region_code
156 and ac.localization_code is not null;
157 cursor l_get_region_list_csr (region_appl_id_param number,
158 region_code_param varchar2) is
159 select region_application_id, region_code
160 from AK_REGIONS
161 where region_application_id = region_appl_id_param
162 and region_code = region_code_param;
163 cursor l_get_region_code_csr (application_id number) is
164 select region_application_id, region_code
165 from AK_REGIONS
166 where region_application_id = application_id;
167 cursor l_get_org_id(levelpk varchar2) is
168 select organization_id
169 from MTL_PARAMETERS
170 where organization_code = levelpk;
171 cursor l_get_org_id2 is
172 select 'X'
173 from ak_customizations
174 where org_id is not null
175 and rownum = 1;
176 cursor l_get_resp_id(levelpk varchar2) is
177 select responsibility_id
178 from FND_RESPONSIBILITY
179 where responsibility_key = levelpk;
180 cursor l_get_resp_id2 is
181 select 'X'
182 from ak_customizations
183 where responsibility_id is not null
184 and rownum = 1;
185 cursor l_get_fun_name(levelpk varchar2) is
186 select function_name
187 from fnd_form_functions
188 where function_name = levelpk;
189 cursor l_get_fun_name2 is
190 select 'X'
191 from ak_customizations
192 where function_name is not null
193 and rownum = 1;
194 cursor l_get_local_code(levelpk varchar2) is
195 select territory_code
196 from fnd_territories
197 where territory_code = levelpk;
198 cursor l_get_local_code2 is
199 select 'X'
200 from ak_customizations
201 where localization_code is not null
202 and rownum = 1;
203 l_api_version_number CONSTANT number := 1.0;
204 l_api_name CONSTANT varchar2(30) := 'Download_Custom';
205 l_region_pk_tbl AK_REGION_PUB.Region_PK_Tbl_Type;
206 l_custom_pk_tbl AK_CUSTOM_PUB.Custom_PK_Tbl_Type;
207 l_msg_count NUMBER;
208 l_msg_data VARCHAR2(2000);
209 l_index NUMBER;
210 l_return_status varchar2(1);
211 l_level_id_pk NUMBER;
212 l_resp_id_pk NUMBER;
213 l_org_id_pk NUMBER;
214 l_level_pk VARCHAR2(30);
215 l_fun_pk VARCHAR2(30);
216 begin
217
218 IF NOT FND_API.Compatible_API_Call (
219 l_api_version_number, p_api_version_number, l_api_name,
220 G_PKG_NAME) then
221 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 return;
223 END IF;
224
225 if (p_level = 'RESPONSIBILITY' and p_levelpk is not null and
226 p_levelpk <> FND_API.G_MISS_CHAR) then
227 open l_get_resp_id(p_levelpk);
228 fetch l_get_resp_id into l_resp_id_pk;
229 if (l_get_resp_id%notfound) then
230 close l_get_resp_id;
231 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
232 FND_MESSAGE.SET_NAME('AK','AK_RESP_IS_NOT_VALID');
233 FND_MESSAGE.SET_TOKEN('KEY',p_levelpk);
234 FND_MSG_PUB.Add;
235 end if;
236 RAISE FND_API.G_EXC_ERROR;
237 end if;
238 close l_get_resp_id;
239 elsif (p_level = 'RESPONSIBILITY' and (p_levelpk is null or
240 p_levelpk = FND_API.G_MISS_CHAR)) then
241 open l_get_resp_id2;
242 fetch l_get_resp_id2 into l_level_id_pk;
243 if (l_get_resp_id2%notfound) then
244 close l_get_resp_id2;
245 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
246 FND_MESSAGE.SET_NAME('AK','AK_RESP_IS_NOT_VALID');
247 FND_MESSAGE.SET_TOKEN('KEY',p_levelpk);
248 FND_MSG_PUB.Add;
249 end if;
250 RAISE FND_API.G_EXC_ERROR;
251 end if;
252 close l_get_resp_id2;
253 elsif (p_level = 'ORGANIZATION' and p_levelpk is not null and
254 p_levelpk <> FND_API.G_MISS_CHAR) then
255 open l_get_org_id(p_levelpk);
256 fetch l_get_org_id into l_org_id_pk;
257 if (l_get_org_id%notfound) then
258 close l_get_org_id;
259 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
260 FND_MESSAGE.SET_NAME('AK','AK_ORG_IS_NOT_VALID');
261 FND_MESSAGE.SET_TOKEN('KEY',p_levelpk);
262 FND_MSG_PUB.Add;
263 end if;
264 RAISE FND_API.G_EXC_ERROR;
265 end if;
266 close l_get_org_id;
267 elsif (p_level = 'ORGANIZATION' and (p_levelpk is null or
268 p_levelpk = FND_API.G_MISS_CHAR)) then
269 open l_get_org_id2;
270 fetch l_get_org_id2 into l_level_id_pk;
271 if (l_get_org_id2%notfound) then
272 close l_get_org_id2;
273 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
274 FND_MESSAGE.SET_NAME('AK','AK_ORG_IS_NOT_VALID');
275 FND_MESSAGE.SET_TOKEN('KEY',p_levelpk);
276 FND_MSG_PUB.Add;
277 end if;
278 RAISE FND_API.G_EXC_ERROR;
279 end if;
280 close l_get_org_id2;
281 elsif (p_level = 'FUNCTION' and p_levelpk is not null and
282 p_levelpk <> FND_API.G_MISS_CHAR) then
283 open l_get_fun_name(p_levelpk);
284 fetch l_get_fun_name into l_fun_pk;
285 if (l_get_fun_name%notfound) then
286 close l_get_fun_name;
287 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
288 FND_MESSAGE.SET_NAME('AK','AK_FUN_IS_NOT_VALID');
289 FND_MESSAGE.SET_TOKEN('KEY',p_levelpk);
290 FND_MSG_PUB.Add;
291 end if;
292 RAISE FND_API.G_EXC_ERROR;
293 end if;
294 close l_get_fun_name;
295 elsif (p_level = 'FUNCTION' and (p_levelpk is null or
296 p_levelpk = FND_API.G_MISS_CHAR)) then
297 open l_get_fun_name2;
298 fetch l_get_fun_name2 into l_level_id_pk;
299 if (l_get_fun_name2%notfound) then
300 close l_get_fun_name2;
301 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
302 FND_MESSAGE.SET_NAME('AK','AK_FUN_IS_NOT_VALID');
303 FND_MESSAGE.SET_TOKEN('KEY',p_levelpk);
304 FND_MSG_PUB.Add;
305 end if;
306 RAISE FND_API.G_EXC_ERROR;
307 end if;
308 close l_get_fun_name2;
309 end if;
310 -- Check that one of the following selection criteria is given:
311 -- - p_application_id alone, or
312 -- - a list of region_application_id and region_code in p_object_PK_tbl
313 if (p_application_id = FND_API.G_MISS_NUM) then
314 if (p_custom_PK_tbl.count = 0) then
315 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
316 FND_MESSAGE.SET_NAME('AK','AK_NO_SELECTION');
317 FND_MSG_PUB.Add;
318 end if;
319 raise FND_API.G_EXC_ERROR;
320 end if;
321 else
322 if (p_custom_PK_tbl.count > 0) then
323 -- both application ID and a list of regions to be extracted are
324 -- given, issue a warning that we will ignore the application ID
325 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
326 FND_MESSAGE.SET_NAME('AK','AK_APPL_ID_IGNORED');
327 FND_MSG_PUB.Add;
328 end if;
329 end if;
330 end if;
331
332 -- If selecting by application ID, first load a custom primary key tabl
333 -- with the primary key of all customizations for the given application ID.
334 -- If selecting by a list of customizations, simply copy the custom
335 -- primary key table with the parameter
336 if (p_custom_PK_tbl.count > 0) then
337 l_custom_pk_tbl := p_custom_pk_tbl;
338 else
339 l_index := 1;
340 open l_get_region_code_csr(p_application_id);
341 loop
342 fetch l_get_region_code_csr into
343 l_custom_pk_tbl(l_index).region_appl_id,
344 l_custom_pk_tbl(l_index).region_code;
345 if (l_get_region_code_csr%notfound and l_index = 1) then
346 close l_get_region_code_csr;
347 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
348 FND_MESSAGE.SET_NAME('AK','AK_REGION_DOES_NOT_EXIST');
349 FND_MSG_PUB.Add;
350 end if;
351 raise FND_API.G_EXC_ERROR;
352 end if;
353 exit when l_get_region_code_csr%notfound;
354 l_index := l_index + 1;
355 end loop;
356 close l_get_region_code_csr;
357 end if;
358
359 l_index := l_custom_pk_tbl.FIRST;
360
361 while (l_index is not null) loop
362 --
363 -- Add the region referenced by this customization to the region list
364 --
365 for l_region_rec in l_get_region_list_csr (
366 l_custom_pk_tbl(l_index).region_appl_id,
367 l_custom_pk_tbl(l_index).region_code) LOOP
368 AK_REGION_PVT.INSERT_REGION_PK_TABLE (
369 p_return_status => l_return_status,
370 p_region_application_id => l_region_rec.region_application_id,
371 p_region_code => l_region_rec.region_code,
372 p_region_pk_tbl => l_region_pk_tbl);
373 end loop;
374 l_index := l_custom_pk_tbl.NEXT(l_index);
375 end loop;
376
377 l_index := l_region_pk_tbl.LAST;
378
379 -- if (AK_DOWNLOAD_GRP.G_DOWNLOAD_REG = 'Y') then
380 if (l_region_pk_tbl.count > 0) then
381 AK_REGION_PVT.DOWNLOAD_REGION (
382 p_validation_level => p_validation_level,
383 p_api_version_number => 1.0,
384 p_return_status => l_return_status,
385 p_application_id => p_application_id,
386 p_region_pk_tbl => l_region_pk_tbl,
387 p_nls_language => p_nls_language,
388 p_get_object_flag => 'Y'
389 );
390
391 if (l_return_status = FND_API.G_RET_STS_ERROR) or
392 (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
393 -- dbms_output.put_line(l_api_name || ' Error downloading regions');
394 raise FND_API.G_EXC_ERROR;
395 end if;
396 end if;
397 -- end if;
398
399 l_custom_pk_tbl.DELETE;
400 l_index := l_region_pk_tbl.FIRST;
401
402 while (l_index is not null) loop
403 --
404 -- Add the customizations referenced by this region to the custom list
405 --
406 if (p_level is null or p_level = FND_API.G_MISS_CHAR) then
407 for l_custom_rec in l_get_custom_list_csr (
408 l_region_pk_tbl(l_index).region_appl_id,
409 l_region_pk_tbl(l_index).region_code) LOOP
410 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
411 p_return_status => l_return_status,
412 p_region_application_id => l_custom_rec.region_application_id,
413 p_region_code => l_custom_rec.region_code,
414 p_custom_appl_id => l_custom_rec.customization_application_id,
415 p_custom_code => l_custom_rec.customization_code,
416 p_custom_pk_tbl => l_custom_pk_tbl);
417 end loop;
418 elsif (p_level = 'RESPONSIBILITY' and l_resp_id_pk is not null) then
419 for l_custom_rec in l_get_custom_resp_list_csr (
420 l_region_pk_tbl(l_index).region_appl_id,
421 l_region_pk_tbl(l_index).region_code,
422 l_resp_id_pk) LOOP
423 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
424 p_return_status => l_return_status,
425 p_region_application_id => l_custom_rec.region_application_id,
426 p_region_code => l_custom_rec.region_code,
427 p_custom_appl_id => l_custom_rec.customization_application_id,
428 p_custom_code => l_custom_rec.customization_code,
429 p_custom_pk_tbl => l_custom_pk_tbl);
430 end loop;
431 elsif (p_level = 'RESPONSIBILITY' and l_resp_id_pk is null) then
432 for l_custom_rec in l_get_custom_resp_list2_csr (
433 l_region_pk_tbl(l_index).region_appl_id,
434 l_region_pk_tbl(l_index).region_code) LOOP
435 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
436 p_return_status => l_return_status,
437 p_region_application_id => l_custom_rec.region_application_id,
438 p_region_code => l_custom_rec.region_code,
439 p_custom_appl_id => l_custom_rec.customization_application_id,
440 p_custom_code => l_custom_rec.customization_code,
441 p_custom_pk_tbl => l_custom_pk_tbl);
442 end loop;
443 elsif (p_level = 'ORGANIZATION' and l_org_id_pk is not null) then
444 for l_custom_rec in l_get_custom_org_list_csr (
445 l_region_pk_tbl(l_index).region_appl_id,
446 l_region_pk_tbl(l_index).region_code,
447 l_org_id_pk) LOOP
448 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
449 p_return_status => l_return_status,
450 p_region_application_id => l_custom_rec.region_application_id,
451 p_region_code => l_custom_rec.region_code,
452 p_custom_appl_id => l_custom_rec.customization_application_id,
453 p_custom_code => l_custom_rec.customization_code,
454 p_custom_pk_tbl => l_custom_pk_tbl);
455 end loop;
456 elsif (p_level = 'ORGANIZATION' and l_org_id_pk is null) then
457 for l_custom_rec in l_get_custom_org_list2_csr (
458 l_region_pk_tbl(l_index).region_appl_id,
459 l_region_pk_tbl(l_index).region_code) LOOP
460 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
461 p_return_status => l_return_status,
462 p_region_application_id => l_custom_rec.region_application_id,
463 p_region_code => l_custom_rec.region_code,
464 p_custom_appl_id => l_custom_rec.customization_application_id,
465 p_custom_code => l_custom_rec.customization_code,
466 p_custom_pk_tbl => l_custom_pk_tbl);
467 end loop;
468 elsif (p_level = 'FUNCTION' and l_fun_pk is not null) then
469 for l_custom_rec in l_get_custom_fun_list_csr (
470 l_region_pk_tbl(l_index).region_appl_id,
471 l_region_pk_tbl(l_index).region_code,
472 l_fun_pk) LOOP
473 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
474 p_return_status => l_return_status,
475 p_region_application_id => l_custom_rec.region_application_id,
476 p_region_code => l_custom_rec.region_code,
477 p_custom_appl_id => l_custom_rec.customization_application_id,
478 p_custom_code => l_custom_rec.customization_code,
479 p_custom_pk_tbl => l_custom_pk_tbl);
480 end loop;
481 elsif (p_level = 'FUNCTION' and l_fun_pk is null) then
482 for l_custom_rec in l_get_custom_fun_list2_csr (
483 l_region_pk_tbl(l_index).region_appl_id,
484 l_region_pk_tbl(l_index).region_code) LOOP
485 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
486 p_return_status => l_return_status,
487 p_region_application_id => l_custom_rec.region_application_id,
488 p_region_code => l_custom_rec.region_code,
489 p_custom_appl_id => l_custom_rec.customization_application_id,
490 p_custom_code => l_custom_rec.customization_code,
491 p_custom_pk_tbl => l_custom_pk_tbl);
492 end loop;
493 elsif (p_level = 'SITE' and p_levelpk is not null and
494 p_levelpk <> FND_API.G_MISS_CHAR) then
495 for l_custom_rec in l_get_custom_site_list_csr (
496 l_region_pk_tbl(l_index).region_appl_id,
497 l_region_pk_tbl(l_index).region_code,
498 p_levelpk) LOOP
499 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
500 p_return_status => l_return_status,
501 p_region_application_id => l_custom_rec.region_application_id,
502 p_region_code => l_custom_rec.region_code,
503 p_custom_appl_id => l_custom_rec.customization_application_id,
504 p_custom_code => l_custom_rec.customization_code,
505 p_custom_pk_tbl => l_custom_pk_tbl);
506 end loop;
507 elsif (p_level = 'SITE' and (p_levelpk is null or
508 p_levelpk = FND_API.G_MISS_CHAR)) then
509 for l_custom_rec in l_get_custom_site_list2_csr (
510 l_region_pk_tbl(l_index).region_appl_id,
511 l_region_pk_tbl(l_index).region_code) LOOP
512 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
513 p_return_status => l_return_status,
514 p_region_application_id => l_custom_rec.region_application_id,
515 p_region_code => l_custom_rec.region_code,
516 p_custom_appl_id => l_custom_rec.customization_application_id,
517 p_custom_code => l_custom_rec.customization_code,
518 p_custom_pk_tbl => l_custom_pk_tbl);
519 end loop;
520 elsif (p_level = 'LOCALIZATION' and p_levelpk is not null and
521 p_levelpk <> FND_API.G_MISS_CHAR) then
522 for l_custom_rec in l_get_custom_local_list_csr (
523 l_region_pk_tbl(l_index).region_appl_id,
524 l_region_pk_tbl(l_index).region_code,
525 p_levelpk) LOOP
526 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
527 p_return_status => l_return_status,
528 p_region_application_id => l_custom_rec.region_application_id,
529 p_region_code => l_custom_rec.region_code,
530 p_custom_appl_id => l_custom_rec.customization_application_id,
531 p_custom_code => l_custom_rec.customization_code,
532 p_custom_pk_tbl => l_custom_pk_tbl);
533 end loop;
534 elsif (p_level = 'LOCALIZATION' and (p_levelpk is null or
535 p_levelpk = FND_API.G_MISS_CHAR)) then
536 for l_custom_rec in l_get_custom_local_list2_csr (
537 l_region_pk_tbl(l_index).region_appl_id,
538 l_region_pk_tbl(l_index).region_code) LOOP
539 AK_CUSTOM_PVT.INSERT_CUSTOM_PK_TABLE(
540 p_return_status => l_return_status,
541 p_region_application_id => l_custom_rec.region_application_id,
542 p_region_code => l_custom_rec.region_code,
543 p_custom_appl_id => l_custom_rec.customization_application_id,
544 p_custom_code => l_custom_rec.customization_code,
545 p_custom_pk_tbl => l_custom_pk_tbl);
546 end loop;
547 end if;
548
549 -- Ready to download the next region in the list
550 l_index := l_region_pk_tbl.NEXT(l_index);
551 end loop;
552
553 -- Write details for each selected customization, including its criteria, to
554 -- a buffer to be passed back to the calling procedure.
555 l_index := l_custom_pk_tbl.FIRST;
556 if (l_custom_pk_tbl.LAST > 0) then
557 while (l_index is not null) loop
558 --
559 -- Write custom information from the database
560 --
561 --dbms_output.put_line('writing custom #'||to_char(l_index) || ':' ||
562 -- l_custom_pk_tbl(l_index).region_code);
563
564 if ( (l_custom_pk_tbl(l_index).region_appl_id <> FND_API.G_MISS_NUM) and
565 (l_custom_pk_tbl(l_index).region_appl_id is not null) and
566 (l_custom_pk_tbl(l_index).region_code <> FND_API.G_MISS_CHAR) and
567 (l_custom_pk_tbl(l_index).region_code is not null) ) then
568 WRITE_CUSTOM_TO_BUFFER(
569 p_validation_level => p_validation_level,
570 p_return_status => l_return_status,
571 p_region_application_id => l_custom_pk_tbl(l_index).region_appl_id,
572 p_region_code => l_custom_pk_tbl(l_index).region_code,
573 p_custom_application_id => l_custom_pk_tbl(l_index).custom_appl_id,
574 p_custom_code => l_custom_pk_tbl(l_index).custom_code,
575 p_nls_language => p_nls_language
576 );
577 end if;
578
579 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
580 (l_return_status = FND_API.G_RET_STS_ERROR) then
581 RAISE FND_API.G_EXC_ERROR;
582 end if;
583
584 -- Ready to download the next customization in the list
585 l_index := l_custom_pk_tbl.NEXT(l_index);
586 end loop;
587
588 -- else
589 -- if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
590 -- FND_MESSAGE.SET_NAME('AK','AK_CUST_FOR_REG_DOES_NOT_EXIST');
591 -- FND_MSG_PUB.Add;
592 -- end if;
593 -- raise FND_API.G_EXC_ERROR;
594 end if;
595
596 p_return_status := FND_API.G_RET_STS_SUCCESS;
597
598 EXCEPTION
599 WHEN VALUE_ERROR THEN
600 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
601 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_PK_VALUE_ERROR');
602 FND_MSG_PUB.Add;
603 end if;
604 --dbms_output.put_line('Value error occurred - check your custom list.');
605 p_return_status := FND_API.G_RET_STS_ERROR;
606 WHEN FND_API.G_EXC_ERROR THEN
607 p_return_status := FND_API.G_RET_STS_ERROR;
608 WHEN OTHERS THEN
609 --dbms_output.put_line(SUBSTR(SQLERRM,1,240));
610 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
612 SUBSTR (SQLERRM, 1, 240) );
613 FND_MSG_PUB.Add;
614 end DOWNLOAD_CUSTOM;
615
616 --=======================================================
617 -- Procedure INSERT_CUSTOM_PK_TABLE
618 --
619 -- Usage Private API for inserting the given region's
620 -- primary key value into the given object
621 -- table.
622 -- This API should only be called by other APIs
623 -- that are owned by the Core Modules Team (AK).
624 --
625 -- Desc This API inserts the given region's primary
626 -- key value into a given region table
627 -- (of type Object_PK_Tbl_Type) only if the
628 -- primary key does not already exist in the table.
629 --
630 -- Results The API returns the standard p_return_status parameter
631 -- indicating one of the standard return statuses :
632 -- * Unexpected error
633 -- * Error
634 -- * Success
635 -- Parameters p_region_application_id : IN required
636 -- p_region_code : IN required
637 -- Key value of the region to be inserted to the
638 -- table.
639 -- p_custom_pk_tbl : IN OUT
640 -- Custom Region table to be updated.
641 --
642 -- Version Initial version number = 1.0
643 -- History Current version number = 1.0
644 --=======================================================
645 procedure INSERT_CUSTOM_PK_TABLE (
646 p_return_status OUT NOCOPY VARCHAR2,
647 p_region_application_id IN NUMBER,
648 p_region_code IN VARCHAR2,
649 p_custom_appl_id IN NUMBER,
650 p_custom_code IN VARCHAR2,
651 p_custom_pk_tbl IN OUT NOCOPY AK_CUSTOM_PUB.Custom_PK_Tbl_Type
652 ) is
653 cursor l_get_custom_list_csr (application_id number, application_code varchar2, custom_appl_id number, custom_code varchar2) is
654 select ac.customization_application_id, ac.customization_code,
655 ac.region_application_id, ac.region_code
656 from AK_CUSTOMIZATIONS ac, AK_REGIONS ar
657 where ac.REGION_APPLICATION_ID = application_id
658 and ac.region_code = application_code
659 and ac.customization_application_id = custom_appl_id
660 and ac.customization_code = custom_code
661 and ac.region_application_id = ar.region_application_id
662 and ac.region_code = ar.region_code;
663
664 l_api_version_number CONSTANT number := 1.0;
665 l_api_name CONSTANT varchar2(30) := 'Insert_Custom_PK_Table';
666 l_index NUMBER;
667 begin
668 --
669 -- if table is empty, just insert the region primary key into it
670 --
671 if (p_custom_pk_tbl.count = 0) then
672 --dbms_output.put_line('Inserted region: ' || p_region_code ||
673 -- ' into element #1');
674 open l_get_custom_list_csr(p_region_application_id, p_region_code, p_custom_appl_id, p_custom_code);
675 loop
676 fetch l_get_custom_list_csr into
677 p_custom_pk_tbl(1).custom_appl_id,
678 p_custom_pk_tbl(1).custom_code,
679 p_custom_pk_tbl(1).region_appl_id,
680 p_custom_pk_tbl(1).region_code;
681 exit when l_get_custom_list_csr%notfound;
682 end loop;
683 close l_get_custom_list_csr;
684 return;
685 end if;
686
687 --
688 -- otherwise, insert the region to the end of the table if it is
689 -- not already in the table. If it is already in the table, return
690 -- without changing the table.
691 --
692 for l_custom_rec in l_get_custom_list_csr(p_region_application_id, p_region_code, p_custom_appl_id, p_custom_code) loop
693 for l_index in p_custom_pk_tbl.FIRST .. p_custom_pk_tbl.LAST loop
694 if (p_custom_pk_tbl.exists(l_index)) then
695 if (p_custom_pk_tbl(l_index).region_appl_id = l_custom_rec.region_application_id)
696 and
697 (p_custom_pk_tbl(l_index).region_code = l_custom_rec.region_code)
698 and
699 (p_custom_pk_tbl(l_index).custom_appl_id = l_custom_rec.customization_application_id)
700 and
701 (p_custom_pk_tbl(l_index).custom_code = l_custom_rec.customization_code) then
702 return;
703 end if;
704 end if;
705 end loop;
706
707 --dbms_output.put_line('Inserted region: ' || p_region_code ||
708 -- ' into element #' || to_char(p_region_pk_tbl.LAST + 1));
709 l_index := p_custom_pk_tbl.LAST + 1;
710 p_custom_pk_tbl(l_index).region_appl_id := l_custom_rec.region_application_id;
711 p_custom_pk_tbl(l_index).region_code := l_custom_rec.region_code;
712 p_custom_pk_tbl(l_index).custom_appl_id := l_custom_rec.customization_application_id;
713 p_custom_pk_tbl(l_index).custom_code := l_custom_rec.customization_code;
714 end loop;
715
716 EXCEPTION
717 WHEN FND_API.G_EXC_ERROR THEN
718 p_return_status := FND_API.G_RET_STS_ERROR;
719 WHEN OTHERS THEN
720 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
721 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
722 SUBSTR (SQLERRM, 1, 240) );
723 FND_MSG_PUB.Add;
724 end INSERT_CUSTOM_PK_TABLE;
725
726 --=======================================================
727 -- Procedure WRITE_CUSTOM_TO_BUFFER (local procedure)
728 --
729 -- Usage Local procedure for writing the given customization
730 -- and all its children records to the output file.
731 -- Not designed to be called from outside this package.
732 --
733 -- Desc This procedure first retreives and writes the given
734 -- customization to the loader file. Then it calls other local
735 -- procedure to write all its region items to the same output
736 -- file.
737 --
738 -- Results The API returns the standard p_return_status parameter
739 -- indicating one of the standard return statuses :
740 -- * Unexpected error
741 -- * Error
742 -- * Success
743 -- Parameters
744 -- p_region_application_id : IN required
745 -- p_region_code : IN required
746 -- Key value of the Region to be extracted to the loader
747 -- file.
748 -- p_nls_language : IN required
749 -- The NLS langauge that should be used when
750 -- extracting data from the TL table
751 --=======================================================
752 procedure WRITE_CUSTOM_TO_BUFFER (
753 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
754 p_return_status OUT NOCOPY VARCHAR2,
755 p_region_application_id IN NUMBER,
756 p_region_code IN VARCHAR2,
757 p_custom_application_id IN NUMBER,
758 p_custom_code IN VARCHAR2,
759 p_nls_language IN VARCHAR2
760 ) is
761 cursor l_get_custom_csr is
762 select *
763 from AK_CUSTOMIZATIONS
764 where REGION_APPLICATION_ID = p_region_application_id
765 and REGION_CODE = p_region_code
766 and CUSTOMIZATION_APPLICATION_ID = p_custom_application_id
767 and CUSTOMIZATION_CODE = p_custom_code;
768 cursor l_get_custom_tl_csr is
769 select *
770 from AK_CUSTOMIZATIONS_TL
771 where REGION_APPLICATION_ID = p_region_application_id
772 and REGION_CODE = p_region_code
773 and CUSTOMIZATION_APPLICATION_ID = p_custom_application_id
774 and CUSTOMIZATION_CODE = p_custom_code
775 and LANGUAGE = p_nls_language;
776 l_api_name CONSTANT varchar2(30) := 'Write_Custom_to_buffer';
777 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
778 l_index NUMBER;
779 l_custom_rec AK_CUSTOMIZATIONS%ROWTYPE;
780 l_custom_tl_rec AK_CUSTOMIZATIONS_TL%ROWTYPE;
781 l_return_status varchar2(1);
782 begin
783 -- Retrieve customization information from the database
784
785 open l_get_custom_csr;
786 fetch l_get_custom_csr into l_custom_rec;
787 if (l_get_custom_csr%notfound) then
788 close l_get_custom_csr;
789 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
790 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_DOES_NOT_EXIST');
791 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
792 p_region_code ||' '||
793 to_char(p_custom_application_id) ||' '||
794 p_custom_code);
795 FND_MSG_PUB.Add;
796 end if;
797 -- dbms_output.put_line('Cannot find customization '||p_custom_code);
798 RAISE FND_API.G_EXC_ERROR;
799 end if;
800 close l_get_custom_csr;
801
802 -- Retrieve custom TL information from the database
803
804 open l_get_custom_tl_csr;
805 fetch l_get_custom_tl_csr into l_custom_tl_rec;
806 if (l_get_custom_tl_csr%notfound) then
807 close l_get_custom_tl_csr;
808 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
809 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_TL_DOES_NOT_EXIST');
810 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
811 p_region_code ||' '||
812 to_char(p_custom_application_id) ||' '||
813 p_custom_code);
814 FND_MSG_PUB.Add;
815 end if;
816 --dbms_output.put_line('Cannot find custom TL '||p_custom_code);
817 RAISE FND_API.G_EXC_ERROR;
818 end if;
819 close l_get_custom_tl_csr;
820
821 -- Customization must be validated before it is written to the file
822 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
823 if not AK_CUSTOM_PVT.VALIDATE_CUSTOM (
824 p_validation_level => p_validation_level,
825 p_api_version_number => 1.0,
826 p_return_status => l_return_status,
827 p_region_application_id => l_custom_rec.region_application_id,
828 p_region_code => l_custom_rec.region_code,
829 p_custom_application_id => l_custom_rec.customization_application_id,
830 p_custom_code => l_custom_rec.customization_code,
831 p_verticalization_id => l_custom_rec.verticalization_id,
832 p_localization_code => l_custom_rec.localization_code,
833 p_org_id => l_custom_rec.org_id,
834 p_site_id => l_custom_rec.site_id,
835 p_responsibility_id => l_custom_rec.responsibility_id,
836 p_web_user_id => l_custom_rec.web_user_id,
837 p_default_custom_flag => l_custom_rec.default_customization_flag,
838 p_customization_level_id => l_custom_rec.customization_level_id,
839 p_developer_mode => l_custom_rec.developer_mode,
840 p_reference_path => l_custom_rec.reference_path,
841 p_function_name => l_custom_rec.function_name,
842 p_start_date_active => l_custom_rec.start_date_active,
843 p_end_date_active => l_custom_rec.end_date_active,
844 p_name => l_custom_tl_rec.name,
845 p_description => l_custom_tl_rec.description,
846 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD
847 )
848 then
849 -- dbms_output.put_line('Custom ' || p_custom_code
850 -- || ' not downloaded due to validation error');
851 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
852 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_NOT_DOWNLOADED');
853 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
854 p_region_code ||' '||
855 to_char(p_custom_application_id) ||' '||
856 p_custom_code);
857 FND_MSG_PUB.Add;
858 end if;
859 raise FND_API.G_EXC_ERROR;
860 end if; /* if AK_CUSTOM_PVT.VALIDATE_CUSTOM */
861 end if; /* if p_validation_level */
862
863 -- Write customization into buffer
864 l_index := 1;
865
866 l_databuffer_tbl(l_index) := 'BEGIN CUSTOMIZATION "' ||
867 nvl(to_char(p_custom_application_id), '') || '" "' ||
868 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_custom_code) || '" "'||
869 nvl(to_char(p_region_application_id), '') || '" "' ||
870 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(p_region_code) || '"';
871 if ((l_custom_rec.verticalization_id IS NOT NULL) and
872 (l_custom_rec.verticalization_id <> FND_API.G_MISS_CHAR)) then
873 l_index := l_index + 1;
874 l_databuffer_tbl(l_index) := ' VERTICALIZATION_ID = "' ||
875 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_custom_rec.verticalization_id) || '"';
876 end if;
877 if ((l_custom_rec.localization_code IS NOT NULL) and
878 (l_custom_rec.localization_code <> FND_API.G_MISS_CHAR)) then
879 l_index := l_index + 1;
880 l_databuffer_tbl(l_index) := ' LOCALIZATION_CODE = "' ||
881 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_custom_rec.localization_code) || '"';
882 end if;
883 if ((l_custom_rec.org_id IS NOT NULL) and
884 (l_custom_rec.org_id <> FND_API.G_MISS_NUM)) then
885 l_index := l_index + 1;
886 l_databuffer_tbl(l_index) := ' ORG_ID = "' ||
887 nvl(to_char(l_custom_rec.org_id), '') || '"';
888 end if;
889 if ((l_custom_rec.site_id IS NOT NULL) and
890 (l_custom_rec.site_id <> FND_API.G_MISS_NUM)) then
891 l_index := l_index + 1;
892 l_databuffer_tbl(l_index) := ' SITE_ID = "' ||
893 nvl(to_char(l_custom_rec.site_id), '') || '"';
894 end if;
895 if ((l_custom_rec.responsibility_id IS NOT NULL) and
896 (l_custom_rec.responsibility_id <> FND_API.G_MISS_NUM)) then
897 l_index := l_index + 1;
898 l_databuffer_tbl(l_index) := ' RESPONSIBILITY_ID = "' ||
899 nvl(to_char(l_custom_rec.responsibility_id), '') || '"';
900 end if;
901 if ((l_custom_rec.web_user_id IS NOT NULL) and
902 (l_custom_rec.web_user_id <> FND_API.G_MISS_NUM)) then
903 l_index := l_index + 1;
904 l_databuffer_tbl(l_index) := ' WEB_USER_ID = "' ||
905 nvl(to_char(l_custom_rec.web_user_id), '') || '"';
906 end if;
907 if ((l_custom_rec.default_customization_flag IS NOT NULL) and
908 (l_custom_rec.default_customization_flag <> FND_API.G_MISS_CHAR)) then
909 l_index := l_index + 1;
910 l_databuffer_tbl(l_index) := ' CUSTOMIZATION_FLAG = "' ||
911 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_custom_rec.default_customization_flag) || '"';
912 end if;
913 if ((l_custom_rec.customization_level_id IS NOT NULL) and
914 (l_custom_rec.customization_level_id <> FND_API.G_MISS_NUM)) then
915 l_index := l_index + 1;
916 l_databuffer_tbl(l_index) := ' CUSTOMIZATION_LEVEL_ID = "' ||
917 nvl(to_char(l_custom_rec.customization_level_id), '') || '"';
918 end if;
919 if ((l_custom_rec.developer_mode IS NOT NULL) and
920 (l_custom_rec.developer_mode <> FND_API.G_MISS_CHAR)) then
921 l_index := l_index + 1;
922 l_databuffer_tbl(l_index) := ' DEVELOPER_MODE = "' ||
923 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_custom_rec.developer_mode) || '"';
924 end if;
925 if ((l_custom_rec.reference_path IS NOT NULL) and
926 (l_custom_rec.reference_path <> FND_API.G_MISS_CHAR)) then
927 l_index := l_index + 1;
928 l_databuffer_tbl(l_index) := ' REFERENCE_PATH = "' ||
929 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_custom_rec.reference_path) || '"';
930 end if;
931 if ((l_custom_rec.function_name IS NOT NULL) and
932 (l_custom_rec.function_name <> FND_API.G_MISS_CHAR)) then
933 l_index := l_index + 1;
934 l_databuffer_tbl(l_index) := ' FUNCTION_NAME = "' ||
935 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_custom_rec.function_name) || '"';
936 end if;
937 if ((l_custom_rec.start_date_active IS NOT NULL) and
938 (l_custom_rec.start_date_active <> FND_API.G_MISS_DATE)) then
939 l_index := l_index + 1;
940 l_databuffer_tbl(l_index) := ' START_DATE_ACTIVE = "' ||
941 to_char(l_custom_rec.start_date_active,
942 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
943 end if;
944 if ((l_custom_rec.end_date_active IS NOT NULL) and
945 (l_custom_rec.end_date_active <> FND_API.G_MISS_DATE)) then
946 l_index := l_index + 1;
947 l_databuffer_tbl(l_index) := ' END_DATE_ACTIVE = "' ||
948 to_char(l_custom_rec.end_date_active,
949 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
950 end if;
951 -- - Write out who columns
952 l_index := l_index + 1;
953 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
954 nvl(to_char(l_custom_rec.created_by),'') || '"';
955 l_index := l_index + 1;
956 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
957 to_char(l_custom_rec.creation_date,
958 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
959 l_index := l_index + 1;
960 -- CHANGED TO OWNER FOR R12
961 -- l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
962 -- nvl(to_char(l_custom_rec.last_updated_by),'') || '"';A
963 l_databuffer_tbl(l_index) := ' OWNER = "' ||
964 FND_LOAD_UTIL.OWNER_NAME(l_custom_rec.last_updated_by) || '"';
965 l_index := l_index + 1;
966 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
967 to_char(l_custom_rec.last_update_date,
968 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
969 l_index := l_index + 1;
970 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
971 nvl(to_char(l_custom_rec.last_update_login),'') || '"';
972
973 -- translation columns
974 --
975 if ((l_custom_tl_rec.name IS NOT NULL) and
976 (l_custom_tl_rec.name <> FND_API.G_MISS_CHAR)) then
977 l_index := l_index + 1;
978 l_databuffer_tbl(l_index) := ' NAME = "' ||
979 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_custom_tl_rec.name) || '"';
980 end if;
981 if ((l_custom_tl_rec.description IS NOT NULL) and
982 (l_custom_tl_rec.description <> FND_API.G_MISS_CHAR)) then
983 l_index := l_index + 1;
984 l_databuffer_tbl(l_index) := ' DESCRIPTION = "' ||
985 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_custom_tl_rec.description) || '"';
986 end if;
987
988 -- - Write the custom data to the specified file
989 AK_ON_OBJECTS_PVT.WRITE_FILE (
990 p_return_status => l_return_status,
991 p_buffer_tbl => l_databuffer_tbl,
992 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
993 );
994 -- If API call returns with an error status...
995 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
996 (l_return_status = FND_API.G_RET_STS_ERROR) then
997 RAISE FND_API.G_EXC_ERROR;
998 end if;
999
1000 l_databuffer_tbl.delete;
1001
1002 WRITE_CUST_REGION_TO_BUFFER (
1003 p_validation_level => p_validation_level,
1004 p_return_status => l_return_status,
1005 p_custom_application_id => l_custom_rec.customization_application_id,
1006 p_custom_code => l_custom_rec.customization_code,
1007 p_region_application_id => l_custom_rec.region_application_id,
1008 p_region_code => l_custom_rec.region_code,
1009 p_nls_language => p_nls_language
1010 );
1011 --
1012 -- Download aborts if validation fails in WRITE_CUST_REGION_TO_BUFFER
1013 --
1014 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1015 (l_return_status = FND_API.G_RET_STS_ERROR) then
1016 RAISE FND_API.G_EXC_ERROR;
1017 end if;
1018
1019 WRITE_CUST_REG_ITEM_TO_BUFFER (
1020 p_validation_level => p_validation_level,
1021 p_return_status => l_return_status,
1022 p_custom_application_id => l_custom_rec.customization_application_id,
1023 p_custom_code => l_custom_rec.customization_code,
1024 p_region_application_id => l_custom_rec.region_application_id,
1025 p_region_code => l_custom_rec.region_code,
1026 p_nls_language => p_nls_language
1027 );
1028 --
1029 -- Download aborts if validation fails in WRITE_CUST_REG_ITEM_TO_BUFFER
1030 --
1031 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1032 (l_return_status = FND_API.G_RET_STS_ERROR) then
1033 RAISE FND_API.G_EXC_ERROR;
1034 end if;
1035
1036 WRITE_CRITERIA_TO_BUFFER (
1037 p_validation_level => p_validation_level,
1038 p_return_status => l_return_status,
1039 p_custom_application_id => l_custom_rec.customization_application_id,
1040 p_custom_code => l_custom_rec.customization_code,
1041 p_region_application_id => l_custom_rec.region_application_id,
1042 p_region_code => l_custom_rec.region_code,
1043 p_nls_language => p_nls_language
1044 );
1045 --
1046 -- Download aborts if validation fails in WRITE_CRITERIA_TO_BUFFER
1047 --
1048 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1049 (l_return_status = FND_API.G_RET_STS_ERROR) then
1050 RAISE FND_API.G_EXC_ERROR;
1051 end if;
1052
1053 l_index := 1;
1054 l_databuffer_tbl(l_index) := 'END CUSTOMIZATION';
1055 l_index := l_index + 1;
1056 l_databuffer_tbl(l_index) := ' ';
1057
1058 -- - Write the 'END CUSTOMIZATION' to the specified file
1059 AK_ON_OBJECTS_PVT.WRITE_FILE (
1060 p_return_status => l_return_status,
1061 p_buffer_tbl => l_databuffer_tbl,
1062 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
1063 );
1064 -- If API call returns with an error status...
1065 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1066 (l_return_status = FND_API.G_RET_STS_ERROR) then
1067 RAISE FND_API.G_EXC_ERROR;
1068 end if;
1069
1070 p_return_status := FND_API.G_RET_STS_SUCCESS;
1071
1072 EXCEPTION
1073 WHEN VALUE_ERROR THEN
1074 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1075 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_VALUE_ERROR');
1076 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
1077 ' ' || p_region_code || ' ' ||
1078 to_char(p_custom_application_id) ||
1079 ' ' || p_custom_code);
1080 FND_MSG_PUB.Add;
1081 end if;
1082 p_return_status := FND_API.G_RET_STS_ERROR;
1083 WHEN FND_API.G_EXC_ERROR THEN
1084 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1085 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_NOT_DOWNLOADED');
1086 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
1087 ' ' || p_region_code || ' ' ||
1088 to_char(p_custom_application_id) ||
1089 ' ' || p_custom_code);
1090 FND_MSG_PUB.Add;
1091 end if;
1092 p_return_status := FND_API.G_RET_STS_ERROR;
1093 WHEN OTHERS THEN
1094 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1095 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1096 SUBSTR (SQLERRM, 1, 240) );
1097 FND_MSG_PUB.Add;
1098 end WRITE_CUSTOM_TO_BUFFER;
1099
1100 --=======================================================
1101 -- Procedure WRITE_CUST_REGION_TO_BUFFER (local procedure)
1102 --
1103 -- Usage Local procedure for writing the given customization
1104 -- region to the output file.
1105 -- Not designed to be called from outside this package.
1106 --
1107 -- Desc This procedure first retreives and writes the given
1108 -- customization regions to the loader file. Then it calls other
1109 -- local procedure to write all its region items to the same
1110 -- output file.
1111 --
1112 -- Results The API returns the standard p_return_status parameter
1113 -- indicating one of the standard return statuses :
1114 -- * Unexpected error
1115 -- * Error
1116 -- * Success
1117 -- Parameters
1118 -- p_region_application_id : IN required
1119 -- p_region_code : IN required
1120 -- Key value of the Region to be extracted to the loader
1121 -- file.
1122 -- p_nls_language : IN required
1123 -- The NLS langauge that should be used when
1124 -- extracting data from the TL table
1125 --=======================================================
1126 procedure WRITE_CUST_REGION_TO_BUFFER (
1127 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1128 p_return_status OUT NOCOPY VARCHAR2,
1129 p_region_application_id IN NUMBER,
1130 p_region_code IN VARCHAR2,
1131 p_custom_application_id IN NUMBER,
1132 p_custom_code IN VARCHAR2,
1133 p_nls_language IN VARCHAR2
1134 ) is
1135 cursor l_get_cust_region_csr is
1136 select *
1137 from AK_CUSTOM_REGIONS
1138 where REGION_APPLICATION_ID = p_region_application_id
1139 and REGION_CODE = p_region_code
1140 and CUSTOMIZATION_APPLICATION_ID = p_custom_application_id
1141 and CUSTOMIZATION_CODE = p_custom_code;
1142 cursor l_get_cust_region_tl_csr(property_name_param varchar2) is
1143 select *
1144 from AK_CUSTOM_REGIONS_TL
1145 where REGION_APPLICATION_ID = p_region_application_id
1146 and REGION_CODE = p_region_code
1147 and CUSTOMIZATION_APPLICATION_ID = p_custom_application_id
1148 and CUSTOMIZATION_CODE = p_custom_code
1149 and PROPERTY_NAME = property_name_param
1150 and LANGUAGE = p_nls_language;
1151 l_api_name CONSTANT varchar2(30) := 'Write_Cust_Region_to_buffer';
1152 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
1153 l_index NUMBER;
1154 l_cust_region_rec AK_CUSTOM_REGIONS%ROWTYPE;
1155 l_cust_region_tl_rec AK_CUSTOM_REGIONS_TL%ROWTYPE;
1156 l_return_status varchar2(1);
1157 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
1158 begin
1159 -- Retrieve customization region information from the database
1160
1161 open l_get_cust_region_csr;
1162 loop
1163 fetch l_get_cust_region_csr into l_cust_region_rec;
1164 exit when l_get_cust_region_csr%notfound;
1165 open l_get_cust_region_tl_csr(l_cust_region_rec.property_name);
1166 fetch l_get_cust_region_tl_csr into l_cust_region_tl_rec;
1167 if (l_get_cust_region_tl_csr%notfound) then
1168 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1169 FND_MESSAGE.SET_NAME('AK','AK_CUST_REG_TL_DOES_NOT_EXIST');
1170 FND_MESSAGE.SET_TOKEN('KEY', to_char(l_cust_region_rec.region_application_id) ||' '|| l_cust_region_rec.region_code ||' '|| to_char(l_cust_region_rec.customization_application_id) ||' '|| l_cust_region_rec.customization_code);
1171 FND_MSG_PUB.Add;
1172 end if;
1173 -- dbms_output.put_line('Cannot find customization '||p_custom_code);
1174 close l_get_cust_region_tl_csr;
1175 close l_get_cust_region_csr;
1176 RAISE FND_API.G_EXC_ERROR;
1177 else
1178 -- write this customized region if it is validated
1179 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
1180 not AK_CUSTOM_PVT.VALIDATE_CUST_REGION (
1181 p_validation_level => p_validation_level,
1182 p_api_version_number => 1.0,
1183 p_return_status => l_return_status,
1184 p_region_application_id => l_cust_region_rec.region_application_id,
1185 p_region_code => l_cust_region_rec.region_code,
1186 p_custom_application_id => l_cust_region_rec.customization_application_id,
1187 p_custom_code => l_cust_region_rec.customization_code,
1188 p_property_name => l_cust_region_rec.property_name,
1189 p_property_varchar2_value => l_cust_region_rec.property_varchar2_value,
1190 p_property_number_value => l_cust_region_rec.property_number_value,
1191 p_criteria_join_condition => l_cust_region_rec.criteria_join_condition,
1192 p_property_varchar2_value_tl => l_cust_region_tl_rec.property_varchar2_value,
1193 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD) then
1194 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1195 close l_get_cust_region_tl_csr;
1196 close l_get_cust_region_csr;
1197 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_NOT_DOWNLOADED');
1198 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
1199 p_region_code ||' '||
1200 to_char(p_custom_application_id) ||' '||
1201 p_custom_code);
1202 FND_MSG_PUB.Add;
1203 raise FND_API.G_EXC_ERROR;
1204 end if; /* if AK_CUSTOM_PVT.VALIDATE_CUST_REGION */
1205
1206
1207 else
1208 l_index := 1;
1209 l_databuffer_tbl(l_index) := ' ';
1210 l_index := l_index + 1;
1211 l_databuffer_tbl(l_index) := ' BEGIN CUSTOM_REGION '|| '"' ||
1212 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_cust_region_rec.property_name) || '"';
1213 if ((l_cust_region_rec.property_varchar2_value IS NOT NULL) and
1214 (l_cust_region_rec.property_varchar2_value <> FND_API.G_MISS_CHAR)) then
1215 l_index := l_index + 1;
1216 l_databuffer_tbl(l_index) := ' PROPERTY_VARCHAR2_VALUE = "' ||
1217 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_cust_region_rec.property_varchar2_value) || '"';
1218 end if;
1219 if ((l_cust_region_rec.property_number_value IS NOT NULL) and
1220 (l_cust_region_rec.property_number_value <> FND_API.G_MISS_NUM)) then
1221 l_index := l_index + 1;
1222 l_databuffer_tbl(l_index) := ' PROPERTY_NUMBER_VALUE = "' ||
1223 nvl(to_char(l_cust_region_rec.property_number_value), '') || '"';
1224 end if;
1225 if ((l_cust_region_rec.criteria_join_condition IS NOT NULL) and
1226 (l_cust_region_rec.criteria_join_condition <> FND_API.G_MISS_CHAR)) then
1227 l_index := l_index + 1;
1228 l_databuffer_tbl(l_index) := ' CRITERIA_JOIN_CONDITION = "' ||
1229 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_cust_region_rec.criteria_join_condition) || '"';
1230 end if;
1231 -- - Write out who columns
1232 l_index := l_index + 1;
1233 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
1234 nvl(to_char(l_cust_region_rec.created_by),'') || '"';
1235 l_index := l_index + 1;
1236 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
1237 to_char(l_cust_region_rec.creation_date,
1238 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1239 l_index := l_index + 1;
1240 -- CHANGED TO OWNER FOR R12
1241 -- l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
1242 -- nvl(to_char(l_cust_region_rec.last_updated_by),'') || '"';
1243 l_databuffer_tbl(l_index) := ' OWNER = "' ||
1244 FND_LOAD_UTIL.OWNER_NAME(l_cust_region_rec.last_updated_by) || '"';
1245 l_index := l_index + 1;
1246 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
1247 to_char(l_cust_region_rec.last_update_date,
1248 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1249 l_index := l_index + 1;
1250 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
1251 nvl(to_char(l_cust_region_rec.last_update_login),'') || '"';
1252 -- translation columns
1253 --
1254 if ((l_cust_region_tl_rec.property_varchar2_value IS NOT NULL) and
1255 (l_cust_region_tl_rec.property_varchar2_value <> FND_API.G_MISS_CHAR)) then
1256 l_index := l_index + 1;
1257 l_databuffer_tbl(l_index) := ' PROPERTY_VARCHAR2_VALUE_TL = "' ||
1258 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_cust_region_tl_rec.property_varchar2_value) || '"';
1259 end if;
1260
1261 -- finish up customized regions
1262 l_index := l_index + 1;
1263 l_databuffer_tbl(l_index) := ' END CUSTOM_REGION';
1264 -- l_index := l_index + 1;
1265 -- l_databuffer_tbl(l_index) := ' ';
1266
1267 -- - Write the custom region data to the specified file
1268 AK_ON_OBJECTS_PVT.WRITE_FILE (
1269 p_return_status => l_return_status,
1270 p_buffer_tbl => l_databuffer_tbl,
1271 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
1272 );
1273 -- If API call returns with an error status...
1274 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1275 (l_return_status = FND_API.G_RET_STS_ERROR) then
1276 close l_get_cust_region_tl_csr;
1277 close l_get_cust_region_csr;
1278 RAISE FND_API.G_EXC_ERROR;
1279 end if;
1280
1281 l_databuffer_tbl.delete;
1282 end if; -- validation OK
1283
1284 end if; -- if TL record found
1285 close l_get_cust_region_tl_csr;
1286
1287 end loop;
1288 close l_get_cust_region_csr;
1289
1290 p_return_status := FND_API.G_RET_STS_SUCCESS;
1291 EXCEPTION
1292 WHEN VALUE_ERROR THEN
1293 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1294 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_VALUE_ERROR');
1295 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
1296 ' ' || p_region_code ||
1297 ' ' || to_char(p_custom_application_id) ||
1298 ' ' || p_custom_code || ' ' ||
1299 ' ' || l_cust_region_rec.property_name);
1300 FND_MSG_PUB.Add;
1301 end if;
1302 p_return_status := FND_API.G_RET_STS_ERROR;
1303 WHEN FND_API.G_EXC_ERROR THEN
1304 p_return_status := FND_API.G_RET_STS_ERROR;
1305 WHEN OTHERS THEN
1306 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1307 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1308 SUBSTR (SQLERRM, 1, 240) );
1309 FND_MSG_PUB.Add;
1310 end WRITE_CUST_REGION_TO_BUFFER;
1311
1312 --=======================================================
1313 -- Procedure WRITE_CUST_REG_ITEM_TO_BUFFER (local procedure)
1314 --
1315 -- Usage Local procedure for writing the given customization
1316 -- region item to the output file.
1317 -- Not designed to be called from outside this package.
1318 --
1319 -- Desc This procedure first retreives and writes the given
1320 -- customization regions to the loader file. Then it calls other
1321 -- local procedure to write all its region items to the same
1322 -- output file.
1323 --
1324 -- Results The API returns the standard p_return_status parameter
1325 -- indicating one of the standard return statuses :
1326 -- * Unexpected error
1327 -- * Error
1328 -- * Success
1329 -- Parameters
1330 -- p_region_application_id : IN required
1331 -- p_region_code : IN required
1332 -- Key value of the Region to be extracted to the loader
1333 -- file.
1334 -- p_nls_language : IN required
1335 -- The NLS langauge that should be used when
1336 -- extracting data from the TL table
1337 --=======================================================
1338 procedure WRITE_CUST_REG_ITEM_TO_BUFFER (
1339 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1340 p_return_status OUT NOCOPY VARCHAR2,
1341 p_region_application_id IN NUMBER,
1342 p_region_code IN VARCHAR2,
1343 p_custom_application_id IN NUMBER,
1344 p_custom_code IN VARCHAR2,
1345 p_nls_language IN VARCHAR2
1346 ) is
1347 cursor l_get_cust_region_item_csr is
1348 select *
1349 from AK_CUSTOM_REGION_ITEMS
1350 where REGION_APPLICATION_ID = p_region_application_id
1351 and REGION_CODE = p_region_code
1352 and CUSTOMIZATION_APPLICATION_ID = p_custom_application_id
1353 and CUSTOMIZATION_CODE = p_custom_code;
1354 cursor l_get_cust_region_item_tl_csr(param_attr_appl_id number,
1355 param_attr_code varchar2,
1356 param_property_name varchar2) is
1357 select *
1358 from AK_CUSTOM_REGION_ITEMS_TL
1359 where REGION_APPLICATION_ID = p_region_application_id
1360 and REGION_CODE = p_region_code
1361 and CUSTOMIZATION_APPLICATION_ID = p_custom_application_id
1362 and CUSTOMIZATION_CODE = p_custom_code
1363 and ATTRIBUTE_APPLICATION_ID = param_attr_appl_id
1364 and ATTRIBUTE_CODE = param_attr_code
1365 and PROPERTY_NAME = param_property_name
1366 and LANGUAGE = p_nls_language;
1367 l_api_name CONSTANT varchar2(30) := 'Write_Cust_Reg_Item_to_buffer';
1368 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
1369 l_index NUMBER;
1370 l_cust_region_item_rec AK_CUSTOM_REGION_ITEMS%ROWTYPE;
1371 l_cust_region_item_tl_rec AK_CUSTOM_REGION_ITEMS_TL%ROWTYPE;
1372 l_return_status varchar2(1);
1373 begin
1374 -- Retrieve customization region item information from the database
1375
1376 open l_get_cust_region_item_csr;
1377 loop
1378 fetch l_get_cust_region_item_csr into l_cust_region_item_rec;
1379 exit when l_get_cust_region_item_csr%notfound;
1380 open l_get_cust_region_item_tl_csr(l_cust_region_item_rec.attribute_application_id, l_cust_region_item_rec.attribute_code,
1381 l_cust_region_item_rec.property_name);
1382 fetch l_get_cust_region_item_tl_csr into l_cust_region_item_tl_rec;
1383 if (l_get_cust_region_item_tl_csr%notfound) then
1384 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1385 FND_MESSAGE.SET_NAME('AK','AK_CUST_ITEM_TL_DOES_NOT_EXIST');
1386 FND_MESSAGE.SET_TOKEN('KEY', to_char(l_cust_region_item_rec.region_application_id) ||' '|| l_cust_region_item_rec.region_code
1387 ||' '|| to_char(l_cust_region_item_rec.customization_application_id) ||' '|| l_cust_region_item_rec.customization_code
1388 ||' '|| l_cust_region_item_rec.property_name);
1389 FND_MSG_PUB.Add;
1390 end if;
1391 -- dbms_output.put_line('Cannot find customization '||p_custom_code);
1392 close l_get_cust_region_item_tl_csr;
1393 close l_get_cust_region_item_csr;
1394 RAISE FND_API.G_EXC_ERROR;
1395 else
1396 -- write this customized region item if it is validated
1397 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
1398 not AK_CUSTOM_PVT.VALIDATE_CUST_REGION_ITEM (
1399 p_validation_level => p_validation_level,
1400 p_api_version_number => 1.0,
1401 p_return_status => l_return_status,
1402 p_region_application_id => l_cust_region_item_rec.region_application_id,
1403 p_region_code => l_cust_region_item_rec.region_code,
1404 p_custom_application_id => l_cust_region_item_rec.customization_application_id,
1405 p_custom_code => l_cust_region_item_rec.customization_code,
1406 p_attr_appl_id => l_cust_region_item_rec.attribute_application_id,
1407 p_attr_code => l_cust_region_item_rec.attribute_code,
1408 p_property_name => l_cust_region_item_rec.property_name,
1409 p_property_varchar2_value => l_cust_region_item_rec.property_varchar2_value,
1410 p_property_number_value => l_cust_region_item_rec.property_number_value,
1411 p_property_date_value => to_char(l_cust_region_item_rec.property_date_value),
1412 p_property_varchar2_value_tl => l_cust_region_item_rec.property_varchar2_value,
1413 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD) then
1414 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1415 close l_get_cust_region_item_tl_csr;
1416 close l_get_cust_region_item_csr;
1417 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_NOT_DOWNLOADED');
1418 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
1419 p_region_code ||' '||
1420 to_char(p_custom_application_id) ||' '||
1421 p_custom_code);
1422 FND_MSG_PUB.Add;
1423 raise FND_API.G_EXC_ERROR;
1424 end if; /* if AK_CUSTOM_PVT.VALIDATE_CUST_REGION_ITEM */
1425
1426 else
1427 l_index := 1;
1428 l_databuffer_tbl(l_index) := ' ';
1429 l_index := l_index + 1;
1430 l_databuffer_tbl(l_index) := ' BEGIN CUSTOM_REGION_ITEM "'||
1431 l_cust_region_item_rec.attribute_application_id || '" "' ||
1432 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_cust_region_item_rec.attribute_code) || '" "' ||
1433 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_cust_region_item_rec.property_name) || '"';
1434 if ((l_cust_region_item_rec.property_varchar2_value IS NOT NULL) and
1435 (l_cust_region_item_rec.property_varchar2_value <> FND_API.G_MISS_CHAR)) then
1436 l_index := l_index + 1;
1437 l_databuffer_tbl(l_index) := ' PROPERTY_VARCHAR2_VALUE = "' ||
1438 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_cust_region_item_rec.property_varchar2_value) || '"';
1439 end if;
1440 if ((l_cust_region_item_rec.property_number_value IS NOT NULL) and
1441 (l_cust_region_item_rec.property_number_value <> FND_API.G_MISS_NUM)) then
1442 l_index := l_index + 1;
1443 l_databuffer_tbl(l_index) := ' PROPERTY_NUMBER_VALUE = "' ||
1444 nvl(to_char(l_cust_region_item_rec.property_number_value), '') || '"';
1445 end if;
1446 if ((l_cust_region_item_rec.property_date_value IS NOT NULL) and
1447 (l_cust_region_item_rec.property_date_value <> FND_API.G_MISS_DATE)) then
1448 l_index := l_index + 1;
1449 l_databuffer_tbl(l_index) := ' PROPERTY_DATE_VALUE = "' ||
1450 to_char(l_cust_region_item_rec.property_date_value,
1451 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1452 end if;
1453 -- - Write out who columns
1454 l_index := l_index + 1;
1455 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
1456 nvl(to_char(l_cust_region_item_rec.created_by),'') || '"';
1457 l_index := l_index + 1;
1458 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
1459 to_char(l_cust_region_item_rec.creation_date,
1460 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1461 l_index := l_index + 1;
1462 -- CHANGED TO OWNER FOR R12
1463 -- l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
1464 -- nvl(to_char(l_cust_region_item_rec.last_updated_by),'') || '"';
1465 l_databuffer_tbl(l_index) := ' OWNER = "' ||
1466 FND_LOAD_UTIL.OWNER_NAME(l_cust_region_item_rec.last_updated_by) || '"';
1467 l_index := l_index + 1;
1468 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
1469 to_char(l_cust_region_item_rec.last_update_date,
1470 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1471 l_index := l_index + 1;
1472 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
1473 nvl(to_char(l_cust_region_item_rec.last_update_login),'') || '"';
1474
1475 -- translation columns
1476 --
1477 if ((l_cust_region_item_tl_rec.property_varchar2_value IS NOT NULL) and
1478 (l_cust_region_item_tl_rec.property_varchar2_value <> FND_API.G_MISS_CHAR)) then
1479 l_index := l_index + 1;
1480 l_databuffer_tbl(l_index) := ' PROPERTY_VARCHAR2_VALUE_TL = "' ||
1481 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_cust_region_item_tl_rec.property_varchar2_value) || '"';
1482 end if;
1483
1484 -- finish up customized region items
1485 l_index := l_index + 1;
1486 l_databuffer_tbl(l_index) := ' END CUSTOM_REGION_ITEM';
1487 -- l_index := l_index + 1;
1488 -- l_databuffer_tbl(l_index) := ' ';
1489
1490 -- - Write the custom region item data to the specified file
1491 AK_ON_OBJECTS_PVT.WRITE_FILE (
1492 p_return_status => l_return_status,
1493 p_buffer_tbl => l_databuffer_tbl,
1494 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
1495 );
1496 -- If API call returns with an error status...
1497 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1498 (l_return_status = FND_API.G_RET_STS_ERROR) then
1499 close l_get_cust_region_item_tl_csr;
1500 close l_get_cust_region_item_csr;
1501 RAISE FND_API.G_EXC_ERROR;
1502 end if;
1503
1504 l_databuffer_tbl.delete;
1505 end if; -- validation OK
1506
1507 end if; -- if TL record found
1508 close l_get_cust_region_item_tl_csr;
1509
1510 end loop;
1511 close l_get_cust_region_item_csr;
1512
1513 p_return_status := FND_API.G_RET_STS_SUCCESS;
1514 EXCEPTION
1515 WHEN VALUE_ERROR THEN
1516 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1517 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_VALUE_ERROR');
1518 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
1519 ' ' || p_region_code ||
1520 ' ' || to_char(p_custom_application_id) ||
1521 ' ' || p_custom_code || ' ' ||
1522 ' ' || l_cust_region_item_rec.property_name);
1523 FND_MSG_PUB.Add;
1524 end if;
1525 p_return_status := FND_API.G_RET_STS_ERROR;
1526 WHEN FND_API.G_EXC_ERROR THEN
1527 p_return_status := FND_API.G_RET_STS_ERROR;
1528 WHEN OTHERS THEN
1529 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1531 SUBSTR (SQLERRM, 1, 240) );
1532 FND_MSG_PUB.Add;
1533 end WRITE_CUST_REG_ITEM_TO_BUFFER;
1534
1535 --=======================================================
1536 -- Procedure WRITE_CRITERIA_TO_BUFFER (local procedure)
1537 --
1538 -- Usage Local procedure for writing the given customization
1539 -- criteria to the output file.
1540 -- Not designed to be called from outside this package.
1541 --
1542 -- Desc This procedure first retreives and writes the given
1543 -- customization regions to the loader file. Then it calls other
1544 -- local procedure to write all its region items to the same
1545 -- output file.
1546 --
1547 -- Results The API returns the standard p_return_status parameter
1548 -- indicating one of the standard return statuses :
1549 -- * Unexpected error
1550 -- * Error
1551 -- * Success
1552 -- Parameters
1553 -- p_region_application_id : IN required
1554 -- p_region_code : IN required
1555 -- Key value of the Region to be extracted to the loader
1556 -- file.
1557 -- p_nls_language : IN required
1558 -- The NLS langauge that should be used when
1559 -- extracting data from the TL table
1560 --=======================================================
1561 procedure WRITE_CRITERIA_TO_BUFFER (
1562 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1563 p_return_status OUT NOCOPY VARCHAR2,
1564 p_region_application_id IN NUMBER,
1565 p_region_code IN VARCHAR2,
1566 p_custom_application_id IN NUMBER,
1567 p_custom_code IN VARCHAR2,
1568 p_nls_language IN VARCHAR2
1569 ) is
1570 cursor l_get_criteria_csr is
1571 select *
1572 from AK_CRITERIA
1573 where REGION_APPLICATION_ID = p_region_application_id
1574 and REGION_CODE = p_region_code
1575 and CUSTOMIZATION_APPLICATION_ID = p_custom_application_id
1576 and CUSTOMIZATION_CODE = p_custom_code;
1577 l_api_name CONSTANT varchar2(30) := 'Write_Criteria_to_buffer';
1578 l_databuffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
1579 l_index NUMBER;
1580 l_criteria_rec AK_CRITERIA%ROWTYPE;
1581 l_return_status varchar2(1);
1582 begin
1583 -- Retrieve customization criteria information from the database
1584
1585 open l_get_criteria_csr;
1586 loop
1587 fetch l_get_criteria_csr into l_criteria_rec;
1588 exit when l_get_criteria_csr%notfound;
1589 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) and
1590 not AK_CUSTOM_PVT.VALIDATE_CRITERIA (
1591 p_validation_level => p_validation_level,
1592 p_api_version_number => 1.0,
1593 p_return_status => l_return_status,
1594 p_region_application_id => l_criteria_rec.region_application_id,
1595 p_region_code => l_criteria_rec.region_code,
1596 p_custom_application_id => l_criteria_rec.customization_application_id,
1597 p_custom_code => l_criteria_rec.customization_code,
1598 p_attr_appl_id => l_criteria_rec.attribute_application_id,
1599 p_attr_code => l_criteria_rec.attribute_code,
1600 p_sequence_number => l_criteria_rec.sequence_number,
1601 p_operation => l_criteria_rec.operation,
1602 p_value_varchar2 => l_criteria_rec.value_varchar2,
1603 p_value_number => l_criteria_rec.value_number,
1604 p_value_date => to_char(l_criteria_rec.value_date),
1605 p_start_date_active => l_criteria_rec.start_date_active,
1606 p_end_date_active => l_criteria_rec.end_date_active,
1607 p_caller => AK_ON_OBJECTS_PVT.G_DOWNLOAD) then
1608 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
1609 close l_get_criteria_csr;
1610 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_NOT_DOWNLOADED');
1611 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||' '||
1612 p_region_code ||' '||
1613 to_char(p_custom_application_id) ||' '||
1614 p_custom_code);
1615 FND_MSG_PUB.Add;
1616 raise FND_API.G_EXC_ERROR;
1617 end if; /* if AK_CUSTOM_PVT.VALIDATE_CRITERIA */
1618
1619 else
1620 l_index := 1;
1621 l_databuffer_tbl(l_index) := ' ';
1622 l_index := l_index + 1;
1623 l_databuffer_tbl(l_index) := ' BEGIN CRITERIA "'||
1624 l_criteria_rec.attribute_application_id || '" "' ||
1625 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_criteria_rec.attribute_code) || '" ' ||
1626 l_criteria_rec.sequence_number;
1627 l_index := l_index + 1;
1628 l_databuffer_tbl(l_index) := ' OPERATION = "'||
1629 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_criteria_rec.operation) || '"';
1630 if ((l_criteria_rec.value_varchar2 IS NOT NULL) and
1631 (l_criteria_rec.value_varchar2 <> FND_API.G_MISS_CHAR)) then
1632 l_index := l_index + 1;
1633 l_databuffer_tbl(l_index) := ' VALUE_VARCHAR2 = "' ||
1634 AK_ON_OBJECTS_PVT.REPLACE_SPECIAL_CHAR(l_criteria_rec.value_varchar2) || '"';
1635 end if;
1636 if ((l_criteria_rec.value_number IS NOT NULL) and
1637 (l_criteria_rec.value_number <> FND_API.G_MISS_NUM)) then
1638 l_index := l_index + 1;
1639 l_databuffer_tbl(l_index) := ' VALUE_NUMBER = "' ||
1640 nvl(to_char(l_criteria_rec.value_number), '') || '"';
1641 end if;
1642 if ((l_criteria_rec.value_date IS NOT NULL) and
1643 (l_criteria_rec.value_date <> FND_API.G_MISS_DATE)) then
1644 l_index := l_index + 1;
1645 l_databuffer_tbl(l_index) := ' VALUE_DATE = "' ||
1646 to_char(l_criteria_rec.value_date,
1647 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1648 end if;
1649 if ((l_criteria_rec.start_date_active IS NOT NULL) and
1650 (l_criteria_rec.start_date_Active <> FND_API.G_MISS_DATE)) then
1651 l_index := l_index + 1;
1652 l_databuffer_tbl(l_index) := ' START_DATE_ACTIVE = "' ||
1653 to_char(l_criteria_rec.start_date_active,
1654 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1655 end if;
1656 if ((l_criteria_rec.end_date_active IS NOT NULL) and
1657 (l_criteria_rec.end_date_Active <> FND_API.G_MISS_DATE)) then
1658 l_index := l_index + 1;
1659 l_databuffer_tbl(l_index) := ' END_DATE_ACTIVE = "' ||
1660 to_char(l_criteria_rec.end_date_active,
1661 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1662 end if;
1663 -- - Write out who columns
1664 l_index := l_index + 1;
1665 l_databuffer_tbl(l_index) := ' CREATED_BY = "' ||
1666 nvl(to_char(l_criteria_rec.created_by),'') || '"';
1667 l_index := l_index + 1;
1668 l_databuffer_tbl(l_index) := ' CREATION_DATE = "' ||
1669 to_char(l_criteria_rec.creation_date,
1670 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1671 l_index := l_index + 1;
1672 -- CHANGED TO OWNER FOR R12
1673 -- l_databuffer_tbl(l_index) := ' LAST_UPDATED_BY = "' ||
1674 -- nvl(to_char(l_criteria_rec.last_updated_by),'') || '"';
1675 l_databuffer_tbl(l_index) := ' OWNER = "' ||
1676 FND_LOAD_UTIL.OWNER_NAME(l_criteria_rec.last_updated_by) || '"';
1677 l_index := l_index + 1;
1678 l_databuffer_tbl(l_index) := ' LAST_UPDATE_DATE = "' ||
1679 to_char(l_criteria_rec.last_update_date,
1680 AK_ON_OBJECTS_PUB.G_DATE_FORMAT) || '"';
1681 l_index := l_index + 1;
1682 l_databuffer_tbl(l_index) := ' LAST_UPDATE_LOGIN = "' ||
1683 nvl(to_char(l_criteria_rec.last_update_login),'') || '"';
1684
1685
1686
1687 -- finish up customized criteria
1688 l_index := l_index + 1;
1689 l_databuffer_tbl(l_index) := ' END CRITERIA';
1690 -- l_index := l_index + 1;
1691 -- l_databuffer_tbl(l_index) := ' ';
1692
1693 -- - Write the custom criteria to the specified file
1694 AK_ON_OBJECTS_PVT.WRITE_FILE (
1695 p_return_status => l_return_status,
1696 p_buffer_tbl => l_databuffer_tbl,
1697 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
1698 );
1699 -- If API call returns with an error status...
1700 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1701 (l_return_status = FND_API.G_RET_STS_ERROR) then
1702 close l_get_criteria_csr;
1703 RAISE FND_API.G_EXC_ERROR;
1704 end if;
1705
1706 end if; -- validation OK
1707
1708 end loop;
1709 close l_get_criteria_csr;
1710
1711 p_return_status := FND_API.G_RET_STS_SUCCESS;
1712 EXCEPTION
1713 WHEN VALUE_ERROR THEN
1714 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1715 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_VALUE_ERROR');
1716 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
1717 ' ' || p_region_code ||
1718 ' ' || to_char(p_custom_application_id) ||
1719 ' ' || p_custom_code || ' ' ||
1720 ' ' || to_char(l_criteria_rec.sequence_number));
1721 FND_MSG_PUB.Add;
1722 end if;
1723 p_return_status := FND_API.G_RET_STS_ERROR;
1724 WHEN FND_API.G_EXC_ERROR THEN
1725 p_return_status := FND_API.G_RET_STS_ERROR;
1726 WHEN OTHERS THEN
1727 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1728 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1729 SUBSTR (SQLERRM, 1, 240) );
1730 FND_MSG_PUB.Add;
1731 end WRITE_CRITERIA_TO_BUFFER;
1732
1733 --=======================================================
1734 -- Function VALIDATE_CUSTOM
1735 --
1736 -- Usage Private API for validating a customization. This
1737 -- API should only be called by other APIs that are
1738 -- owned by the Core Modules Team (AK).
1739 --
1740 -- Desc Perform validation on a region record.
1741 --
1742 -- Results The API returns the standard p_return_status parameter
1743 -- indicating one of the standard return statuses :
1744 -- * Unexpected error
1745 -- * Error
1746 -- * Success
1747 -- In addition, this function returns TRUE if all
1748 -- validation tests are passed, or FALSE otherwise.
1749 -- Parameters Customization columns
1750 -- p_caller : IN required
1751 -- Must be one of the following values defined
1752 -- in package AK_ON_OBJECTS_PVT:
1753 -- - G_CREATE (if calling from the Create API)
1754 -- - G_DOWNLOAD (if calling from the Download API)
1755 -- - G_UPDATE (if calling from the Update API)
1756 --
1757 -- Note This API is intended for performing record-level
1758 -- validation. It is not designed for item-level
1759 -- validation.
1760 --
1761 -- Version Initial version number = 1.0
1762 -- History Current version number = 1.0
1763 --=======================================================
1764 function VALIDATE_CUSTOM (
1765 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1766 p_api_version_number IN NUMBER,
1767 p_return_status OUT NOCOPY VARCHAR2,
1768 p_region_application_id IN NUMBER := FND_API.G_MISS_NUM,
1769 p_region_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1770 p_custom_application_id IN NUMBER := FND_API.G_MISS_NUM,
1771 p_custom_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1772 p_verticalization_id IN VARCHAR2 := FND_API.G_MISS_CHAR,
1773 p_localization_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1774 p_org_id IN NUMBER := FND_API.G_MISS_NUM,
1775 p_site_id IN NUMBER := FND_API.G_MISS_NUM,
1776 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
1777 p_web_user_id IN NUMBER := FND_API.G_MISS_NUM,
1778 p_default_custom_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1779 p_customization_level_id IN NUMBER := FND_API.G_MISS_NUM,
1780 p_developer_mode IN VARCHAR2 := FND_API.G_MISS_CHAR,
1781 p_reference_path IN VARCHAR2 := FND_API.G_MISS_CHAR,
1782 p_function_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1783 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
1784 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
1785 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1786 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1787 p_caller IN VARCHAR2,
1788 p_pass IN NUMBER := 2
1789 ) return BOOLEAN is
1790 l_api_version_number CONSTANT number := 1.0;
1791 l_api_name CONSTANT varchar2(30) := 'Validate_Custom';
1792 l_error BOOLEAN;
1793 l_return_status varchar2(1);
1794 begin
1795
1796 IF NOT FND_API.Compatible_API_Call (
1797 l_api_version_number, p_api_version_number, l_api_name,
1798 G_PKG_NAME) then
1799 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1800 return FALSE;
1801 END IF;
1802
1803 l_error := FALSE;
1804
1805 --** if validation level is none, no validation is necessary
1806 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
1807 p_return_status := FND_API.G_RET_STS_SUCCESS;
1808 return TRUE;
1809 end if;
1810
1811 --** check that key columns are not null and not missing **
1812 if ((p_region_application_id is null) or
1813 (p_region_application_id = FND_API.G_MISS_NUM)) then
1814 l_error := TRUE;
1815 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1816 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1817 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
1818 FND_MSG_PUB.Add;
1819 end if;
1820 end if;
1821
1822 if ((p_region_code is null) or
1823 (p_region_code = FND_API.G_MISS_CHAR)) then
1824 l_error := TRUE;
1825 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1826 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1827 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
1828 FND_MSG_PUB.Add;
1829 end if;
1830 end if;
1831
1832 if ((p_custom_application_id is null) or
1833 (p_custom_application_id = FND_API.G_MISS_NUM)) then
1834 l_error := TRUE;
1835 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1836 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1837 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOMIZATION_APPLICATION_ID');
1838 FND_MSG_PUB.Add;
1839 end if;
1840 end if;
1841
1842 if ((p_custom_code is null) or
1843 (p_custom_code = FND_API.G_MISS_CHAR)) then
1844 l_error := TRUE;
1845 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1846 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1847 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOMIZATION_CODE');
1848 FND_MSG_PUB.Add;
1849 end if;
1850 end if;
1851
1852 --** check that required columns are not null and, unless calling **
1853 --** from UPDATE procedure, the columns are not missing **
1854 if ((p_customization_level_id is null) or
1855 (p_customization_level_id = FND_API.G_MISS_NUM and
1856 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
1857 l_error := TRUE;
1858 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1859 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1860 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOMIZATION_LEVEL_ID');
1861 FND_MSG_PUB.Add;
1862 end if;
1863 end if;
1864
1865 if ((p_start_date_active is null) or
1866 (p_start_date_active = FND_API.G_MISS_DATE and
1867 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
1868 l_error := TRUE;
1869 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1870 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1871 FND_MESSAGE.SET_TOKEN('COLUMN', 'START_DATE_ACTIVE');
1872 FND_MSG_PUB.Add;
1873 end if;
1874 end if;
1875
1876 if ((p_name is null) or
1877 (p_name = FND_API.G_MISS_CHAR and
1878 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
1879 l_error := TRUE;
1880 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1881 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
1882 FND_MESSAGE.SET_TOKEN('COLUMN', 'NAME');
1883 FND_MSG_PUB.Add;
1884 end if;
1885 end if;
1886
1887 --** Validate columns **
1888 -- - Region application ID and Region Code
1889 if (NOT AK_REGION_PVT.REGION_EXISTS (
1890 p_api_version_number => 1.0,
1891 p_return_status => l_return_status,
1892 p_region_application_id => p_region_application_id,
1893 p_region_code => p_region_code)) then
1894 l_error := TRUE;
1895 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
1896 FND_MESSAGE.SET_NAME('AK','AK_INVALID_REGION_REFERENCE');
1897 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) || ' ' ||
1898 p_region_code);
1899 FND_MSG_PUB.Add;
1900 end if;
1901 --dbms_output.put_line(l_api_name || ' Invalid region');
1902 end if;
1903
1904 -- return true if no error, false otherwise
1905 p_return_status := FND_API.G_RET_STS_SUCCESS;
1906 return (not l_error);
1907
1908 EXCEPTION
1909 WHEN FND_API.G_EXC_ERROR THEN
1910 p_return_status := FND_API.G_RET_STS_ERROR;
1911 return FALSE;
1912 WHEN OTHERS THEN
1913 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1914 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1915 SUBSTR (SQLERRM, 1, 240) );
1916 FND_MSG_PUB.Add;
1917 return FALSE;
1918
1919 end VALIDATE_CUSTOM;
1920
1921 --=======================================================
1922 -- Function VALIDATE_CUST_REGION
1923 --
1924 -- Usage Private API for validating a custom region. This
1925 -- API should only be called by other APIs that are
1926 -- owned by the Core Modules Team (AK).
1927 --
1928 -- Desc Perform validation on a custom region record.
1929 --
1930 -- Results The API returns the standard p_return_status parameter
1931 -- indicating one of the standard return statuses :
1932 -- * Unexpected error
1933 -- * Error
1934 -- * Success
1935 -- In addition, this function returns TRUE if all
1936 -- validation tests are passed, or FALSE otherwise.
1937 -- Parameters Region graph columns
1938 -- p_caller : IN required
1939 -- Must be one of the following values defined
1940 -- in package AK_ON_OBJECTS_PVT:
1941 -- - G_CREATE (if calling from the Create API)
1942 -- - G_DOWNLOAD (if calling from the Download API)
1943 -- - G_UPDATE (if calling from the Update API)
1944 --
1945 -- Note This API is intended for performing record-level
1946 -- validation. It is not designed for item-level
1947 -- validation.
1948 --
1949 -- Version Initial version number = 1.0
1950 -- History Current version number = 1.0
1951 --=======================================================
1952 function VALIDATE_CUST_REGION (
1953 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1954 p_api_version_number IN NUMBER,
1955 p_return_status OUT NOCOPY VARCHAR2,
1956 p_region_application_id IN NUMBER,
1957 p_region_code IN VARCHAR2,
1958 p_custom_application_id IN NUMBER,
1959 p_custom_code IN VARCHAR2,
1960 p_property_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1961 p_property_varchar2_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
1962 p_property_number_value IN NUMBER := FND_API.G_MISS_NUM,
1963 p_criteria_join_condition IN VARCHAR2 := FND_API.G_MISS_CHAR,
1964 p_property_varchar2_value_tl IN VARCHAR2 := FND_API.G_MISS_CHAR,
1965 p_caller IN VARCHAR2,
1966 p_pass IN NUMBER := 2
1967 ) return BOOLEAN is
1968 cursor l_check_custom_csr is
1969 select 1
1970 from AK_CUSTOMIZATIONS
1971 where region_application_id = p_region_application_id
1972 and region_code = p_region_code
1973 and customization_application_id = p_custom_application_id
1974 and customization_code = p_custom_code;
1975
1976 l_api_version_number CONSTANT number := 1.0;
1977 l_api_name CONSTANT varchar2(30) := 'Validate_Cust_Region';
1978 l_dummy NUMBER;
1979 l_error BOOLEAN;
1980 l_return_status VARCHAR2(1);
1981 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
1982
1983 begin
1984 IF NOT FND_API.Compatible_API_Call (
1985 l_api_version_number, p_api_version_number, l_api_name,
1986 G_PKG_NAME) then
1987 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1988 return FALSE;
1989 END IF;
1990
1991 l_error := FALSE;
1992
1993 --** if validation level is none, no validation is necessary
1994 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
1995 p_return_status := FND_API.G_RET_STS_SUCCESS;
1996 return TRUE;
1997 end if;
1998
1999 --** check that key columns are not null and not missing **
2000 if ((p_region_application_id is null) or
2001 (p_region_application_id = FND_API.G_MISS_NUM)) then
2002 l_error := TRUE;
2003 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2004 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2005 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
2006 FND_MSG_PUB.Add;
2007 end if;
2008 end if;
2009
2010 if ((p_region_code is null) or
2011 (p_region_code = FND_API.G_MISS_CHAR)) then
2012 l_error := TRUE;
2013 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2014 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2015 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
2016 FND_MSG_PUB.Add;
2017 end if;
2018 end if;
2019
2020 if ((p_custom_application_id is null) or
2021 (p_custom_application_id = FND_API.G_MISS_NUM)) then
2022 l_error := TRUE;
2023 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2024 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2025 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOM_APPLICATION_ID');
2026 FND_MSG_PUB.Add;
2027 end if;
2028 end if;
2029
2030 if ((p_custom_code is null) or
2031 (p_custom_code = FND_API.G_MISS_CHAR)) then
2032 l_error := TRUE;
2033 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2034 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2035 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOM_CODE');
2036 FND_MSG_PUB.Add;
2037 end if;
2038 end if;
2039
2040 if ((p_property_name is null) or
2041 (p_property_name = FND_API.G_MISS_CHAR)) then
2042 l_error := TRUE;
2043 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2044 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2045 FND_MESSAGE.SET_TOKEN('COLUMN', 'PROPERTY_NAME');
2046 FND_MSG_PUB.Add;
2047 end if;
2048 end if;
2049
2050 -- - Check that the parent region exists
2051 open l_check_custom_csr;
2052 fetch l_check_custom_csr into l_dummy;
2053 if (l_check_custom_csr%notfound) then
2054 l_error := TRUE;
2055 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2
2056 ) then
2057 FND_MESSAGE.SET_NAME('AK','AK_INVALID_CUSTOM_REFERENCE');
2058 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
2059 ' ' || p_region_code );
2060 FND_MSG_PUB.Add;
2061 end if;
2062 --dbms_output.put_line('Parent region does not exist!');
2063 end if;
2064 close l_check_custom_csr;
2065
2066 -- return true if no error, false otherwise
2067 p_return_status := FND_API.G_RET_STS_SUCCESS;
2068 return (not l_error);
2069
2070 EXCEPTION
2071 WHEN FND_API.G_EXC_ERROR THEN
2072 p_return_status := FND_API.G_RET_STS_ERROR;
2073 return FALSE;
2074 WHEN OTHERS THEN
2075 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2076 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2077 SUBSTR (SQLERRM, 1, 240) );
2078 FND_MSG_PUB.Add;
2079 return FALSE;
2080
2081 end VALIDATE_CUST_REGION;
2082
2083 --=======================================================
2084 -- Function VALIDATE_CUST_REGION_ITEM
2085 --
2086 -- Usage Private API for validating a custom region item. This
2087 -- API should only be called by other APIs that are
2088 -- owned by the Core Modules Team (AK).
2089 --
2090 -- Desc Perform validation on a custom region item record.
2091 --
2092 -- Results The API returns the standard p_return_status parameter
2093 -- indicating one of the standard return statuses :
2094 -- * Unexpected error
2095 -- * Error
2096 -- * Success
2097 -- In addition, this function returns TRUE if all
2098 -- validation tests are passed, or FALSE otherwise.
2099 -- Parameters Custom region item columns
2100 -- p_caller : IN required
2101 -- Must be one of the following values defined
2102 -- in package AK_ON_OBJECTS_PVT:
2103 -- - G_CREATE (if calling from the Create API)
2104 -- - G_DOWNLOAD (if calling from the Download API)
2105 -- - G_UPDATE (if calling from the Update API)
2106 --
2107 -- Note This API is intended for performing record-level
2108 -- validation. It is not designed for item-level
2109 -- validation.
2110 --
2111 -- Version Initial version number = 1.0
2112 -- History Current version number = 1.0
2113 --=======================================================
2114 function VALIDATE_CUST_REGION_ITEM (
2115 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2116 p_api_version_number IN NUMBER,
2117 p_return_status OUT NOCOPY VARCHAR2,
2118 p_region_application_id IN NUMBER,
2119 p_region_code IN VARCHAR2,
2120 p_custom_application_id IN NUMBER,
2121 p_custom_code IN VARCHAR2,
2122 p_attr_appl_id IN NUMBER := FND_API.G_MISS_NUM,
2123 p_attr_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
2124 p_property_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2125 p_property_varchar2_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
2126 p_property_number_value IN NUMBER := FND_API.G_MISS_NUM,
2127 p_property_date_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
2128 p_property_varchar2_value_tl IN VARCHAR2 := FND_API.G_MISS_CHAR,
2129 p_caller IN VARCHAR2,
2130 p_pass IN NUMBER := 2
2131 ) return BOOLEAN is
2132 cursor l_check_custom_csr is
2133 select 1
2134 from AK_CUSTOMIZATIONS
2135 where region_application_id = p_region_application_id
2136 and region_code = p_region_code
2137 and customization_application_id = p_custom_application_id
2138 and customization_code = p_custom_code;
2139
2140 l_api_version_number CONSTANT number := 1.0;
2141 l_api_name CONSTANT varchar2(30) := 'Validate_Cust_Region_Item';
2142 l_dummy NUMBER;
2143 l_error BOOLEAN;
2144 l_return_status VARCHAR2(1);
2145 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
2146
2147 begin
2148 IF NOT FND_API.Compatible_API_Call (
2149 l_api_version_number, p_api_version_number, l_api_name,
2150 G_PKG_NAME) then
2151 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2152 return FALSE;
2153 END IF;
2154
2155 l_error := FALSE;
2156
2157 --** if validation level is none, no validation is necessary
2158 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
2159 p_return_status := FND_API.G_RET_STS_SUCCESS;
2160 return TRUE;
2161 end if;
2162
2163 --** check that key columns are not null and not missing **
2164 if ((p_region_application_id is null) or
2165 (p_region_application_id = FND_API.G_MISS_NUM)) then
2166 l_error := TRUE;
2167 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2168 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2169 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
2170 FND_MSG_PUB.Add;
2171 end if;
2172 end if;
2173
2174 if ((p_region_code is null) or
2175 (p_region_code = FND_API.G_MISS_CHAR)) then
2176 l_error := TRUE;
2177 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2178 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2179 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
2180 FND_MSG_PUB.Add;
2181 end if;
2182 end if;
2183
2184 if ((p_custom_application_id is null) or
2185 (p_custom_application_id = FND_API.G_MISS_NUM)) then
2186 l_error := TRUE;
2187 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2188 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2189 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOM_APPLICATION_ID');
2190 FND_MSG_PUB.Add;
2191 end if;
2192 end if;
2193
2194 if ((p_custom_code is null) or
2195 (p_custom_code = FND_API.G_MISS_CHAR)) then
2196 l_error := TRUE;
2197 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2198 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2199 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOM_CODE');
2200 FND_MSG_PUB.Add;
2201 end if;
2202 end if;
2203
2204 if ((p_property_name is null) or
2205 (p_property_name = FND_API.G_MISS_CHAR)) then
2206 l_error := TRUE;
2207 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2208 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2209 FND_MESSAGE.SET_TOKEN('COLUMN', 'PROPERTY_NAME');
2210 FND_MSG_PUB.Add;
2211 end if;
2212 end if;
2213
2214 if ((p_attr_appl_id is null) or
2215 (p_attr_appl_id = FND_API.G_MISS_NUM)) then
2216 l_error := TRUE;
2217 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2218 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2219 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
2220 FND_MSG_PUB.Add;
2221 end if;
2222 end if;
2223
2224 if ((p_attr_code is null) or
2225 (p_attr_code = FND_API.G_MISS_CHAR)) then
2226 l_error := TRUE;
2227 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2228 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2229 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
2230 FND_MSG_PUB.Add;
2231 end if;
2232 end if;
2233
2234 -- - Check that the parent region exists
2235 open l_check_custom_csr;
2236 fetch l_check_custom_csr into l_dummy;
2237 if (l_check_custom_csr%notfound) then
2238 l_error := TRUE;
2239 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2240 FND_MESSAGE.SET_NAME('AK','AK_INVALID_CUSTOM_REFERENCE');
2241 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
2242 ' ' || p_region_code );
2243 FND_MSG_PUB.Add;
2244 end if;
2245 --dbms_output.put_line('Parent region does not exist!');
2246 end if;
2247 close l_check_custom_csr;
2248
2249 -- return true if no error, false otherwise
2250 p_return_status := FND_API.G_RET_STS_SUCCESS;
2251 return (not l_error);
2252
2253 EXCEPTION
2254 WHEN FND_API.G_EXC_ERROR THEN
2255 p_return_status := FND_API.G_RET_STS_ERROR;
2256 return FALSE;
2257 WHEN OTHERS THEN
2258 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2259 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2260 SUBSTR (SQLERRM, 1, 240) );
2261 FND_MSG_PUB.Add;
2262 return FALSE;
2263
2264 end VALIDATE_CUST_REGION_ITEM;
2265
2266 --=======================================================
2267 -- Function VALIDATE_CRITERIA
2268 --
2269 -- Usage Private API for validating a custom criteria. This
2270 -- API should only be called by other APIs that are
2271 -- owned by the Core Modules Team (AK).
2272 --
2273 -- Desc Perform validation on a custom criteria record.
2274 --
2275 -- Results The API returns the standard p_return_status parameter
2276 -- indicating one of the standard return statuses :
2277 -- * Unexpected error
2278 -- * Error
2279 -- * Success
2280 -- In addition, this function returns TRUE if all
2281 -- validation tests are passed, or FALSE otherwise.
2282 -- Parameters Criteria columns
2283 -- p_caller : IN required
2284 -- Must be one of the following values defined
2285 -- in package AK_ON_OBJECTS_PVT:
2286 -- - G_CREATE (if calling from the Create API)
2287 -- - G_DOWNLOAD (if calling from the Download API)
2288 -- - G_UPDATE (if calling from the Update API)
2289 --
2290 -- Note This API is intended for performing record-level
2291 -- validation. It is not designed for item-level
2292 -- validation.
2293 --
2294 -- Version Initial version number = 1.0
2295 -- History Current version number = 1.0
2296 --=======================================================
2297 function VALIDATE_CRITERIA (
2298 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2299 p_api_version_number IN NUMBER,
2300 p_return_status OUT NOCOPY VARCHAR2,
2301 p_region_application_id IN NUMBER,
2302 p_region_code IN VARCHAR2,
2303 p_custom_application_id IN NUMBER,
2304 p_custom_code IN VARCHAR2,
2305 p_attr_appl_id IN NUMBER := FND_API.G_MISS_NUM,
2306 p_attr_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
2307 p_sequence_number IN NUMBER := FND_API.G_MISS_NUM,
2308 p_operation IN VARCHAR2 := FND_API.G_MISS_CHAR,
2309 p_value_varchar2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
2310 p_value_number IN NUMBER := FND_API.G_MISS_NUM,
2311 p_value_date IN DATE := FND_API.G_MISS_DATE,
2312 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
2313 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
2314 p_caller IN VARCHAR2,
2315 p_pass IN NUMBER := 2
2316 ) return BOOLEAN is
2317 cursor l_check_custom_csr is
2318 select 1
2319 from AK_CUSTOMIZATIONS
2320 where region_application_id = p_region_application_id
2321 and region_code = p_region_code
2322 and customization_application_id = p_custom_application_id
2323 and customization_code = p_custom_code;
2324
2325 l_api_version_number CONSTANT number := 1.0;
2326 l_api_name CONSTANT varchar2(30) := 'Validate_Criteria';
2327 l_dummy NUMBER;
2328 l_error BOOLEAN;
2329 l_return_status VARCHAR2(1);
2330 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
2331
2332 begin
2333 IF NOT FND_API.Compatible_API_Call (
2334 l_api_version_number, p_api_version_number, l_api_name,
2335 G_PKG_NAME) then
2336 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2337 return FALSE;
2338 END IF;
2339
2340 l_error := FALSE;
2341
2342 --** if validation level is none, no validation is necessary
2343 if (p_validation_level = FND_API.G_VALID_LEVEL_NONE) then
2344 p_return_status := FND_API.G_RET_STS_SUCCESS;
2345 return TRUE;
2346 end if;
2347
2348 --** check that key columns are not null and not missing **
2349 if ((p_region_application_id is null) or
2350 (p_region_application_id = FND_API.G_MISS_NUM)) then
2351 l_error := TRUE;
2352 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2353 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2354 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_APPLICATION_ID');
2355 FND_MSG_PUB.Add;
2356 end if;
2357 end if;
2358
2359 if ((p_region_code is null) or
2360 (p_region_code = FND_API.G_MISS_CHAR)) then
2361 l_error := TRUE;
2362 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2363 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2364 FND_MESSAGE.SET_TOKEN('COLUMN', 'REGION_CODE');
2365 FND_MSG_PUB.Add;
2366 end if;
2367 end if;
2368
2369 if ((p_custom_application_id is null) or
2370 (p_custom_application_id = FND_API.G_MISS_NUM)) then
2371 l_error := TRUE;
2372 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2373 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2374 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOM_APPLICATION_ID');
2375 FND_MSG_PUB.Add;
2376 end if;
2377 end if;
2378
2379 if ((p_custom_code is null) or
2380 (p_custom_code = FND_API.G_MISS_CHAR)) then
2381 l_error := TRUE;
2382 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2383 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2384 FND_MESSAGE.SET_TOKEN('COLUMN', 'CUSTOM_CODE');
2385 FND_MSG_PUB.Add;
2386 end if;
2387 end if;
2388
2389 if ((p_attr_appl_id is null) or
2390 (p_attr_appl_id = FND_API.G_MISS_NUM)) then
2391 l_error := TRUE;
2392 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2393 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2394 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_APPLICATION_ID');
2395 FND_MSG_PUB.Add;
2396 end if;
2397 end if;
2398
2399 if ((p_attr_code is null) or
2400 (p_attr_code = FND_API.G_MISS_CHAR)) then
2401 l_error := TRUE;
2402 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2403 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2404 FND_MESSAGE.SET_TOKEN('COLUMN', 'ATTRIBUTE_CODE');
2405 FND_MSG_PUB.Add;
2406 end if;
2407 end if;
2408
2409 --** check that required columns are not null and, unless calling **
2410 --** from UPDATE procedure, the columns are not missing **
2411
2412 if ((p_start_date_active is null) or
2413 (p_start_date_active = FND_API.G_MISS_DATE and
2414 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
2415 l_error := TRUE;
2416 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2417 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2418 FND_MESSAGE.SET_TOKEN('COLUMN', 'START_DATE_ACTIVE');
2419 FND_MSG_PUB.Add;
2420 end if;
2421 end if;
2422
2423 -- - Check that the parent region exists
2424 open l_check_custom_csr;
2425 fetch l_check_custom_csr into l_dummy;
2426 if (l_check_custom_csr%notfound) then
2427 l_error := TRUE;
2428 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2429 FND_MESSAGE.SET_NAME('AK','AK_INVALID_CUSTOM_REFERENCE');
2430 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
2431 ' ' || p_region_code );
2432 FND_MSG_PUB.Add;
2433 end if;
2434 --dbms_output.put_line('Parent region does not exist!');
2435 end if;
2436 close l_check_custom_csr;
2437
2438 --** check that required columns are not null and, unless calling **
2439 --** from UPDATE procedure, the columns are not missing **
2440
2441 if ((p_operation is null) or
2442 (p_operation = FND_API.G_MISS_CHAR and
2443 p_caller <> AK_ON_OBJECTS_PVT.G_UPDATE)) then
2444 l_error := TRUE;
2445 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) and (p_pass = 2) then
2446 FND_MESSAGE.SET_NAME('AK','AK_CANNOT_BE_NULL');
2447 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION');
2448 FND_MSG_PUB.Add;
2449 end if;
2450 end if;
2451
2452 -- return true if no error, false otherwise
2453 p_return_status := FND_API.G_RET_STS_SUCCESS;
2454 return (not l_error);
2455
2456 EXCEPTION
2457 WHEN FND_API.G_EXC_ERROR THEN
2458 p_return_status := FND_API.G_RET_STS_ERROR;
2459 return FALSE;
2460 WHEN OTHERS THEN
2461 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2462 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2463 SUBSTR (SQLERRM, 1, 240) );
2464 FND_MSG_PUB.Add;
2465 return FALSE;
2466
2467 end VALIDATE_CRITERIA;
2468
2469 --=======================================================
2470 -- Procedure CREATE_CUSTOM
2471 --
2472 -- Usage Private API for creating a region graph. This
2473 -- API should only be called by other APIs that are
2474 -- owned by the Core Modules Team (AK).
2475 --
2476 -- Desc Creates a region graph using the given info.
2477 -- This API should only be called by other APIs that are
2478 -- owned by the Core Modules Team (AK).
2479 --
2480 -- Results The API returns the standard p_return_status parameter
2481 -- indicating one of the standard return statuses :
2482 -- * Unexpected error
2483 -- * Error
2484 -- * Success
2485 -- Parameters Region Item columns
2486 -- p_loader_timestamp : IN optional
2487 -- If a timestamp is passed, the API will create the
2488 -- record using this timestamp. Only the upload API
2489 -- should call with this parameter loaded.
2490 --
2491 -- Version Initial version number = 1.0
2492 -- History Current version number = 1.0
2493 --=======================================================
2494 procedure CREATE_CUSTOM (
2495 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2496 p_api_version_number IN NUMBER,
2497 p_init_msg_tbl IN BOOLEAN := FALSE,
2498 p_msg_count OUT NOCOPY NUMBER,
2499 p_msg_data OUT NOCOPY VARCHAR2,
2500 p_return_status OUT NOCOPY VARCHAR2,
2501 p_custom_appl_id IN NUMBER,
2502 p_custom_code IN VARCHAR2,
2503 p_region_appl_id IN NUMBER,
2504 p_region_code IN VARCHAR2,
2505 p_verticalization_id IN VARCHAR2 := FND_API.G_MISS_CHAR,
2506 p_localization_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
2507 p_org_id IN NUMBER := FND_API.G_MISS_NUM,
2508 p_site_id IN NUMBER := FND_API.G_MISS_NUM,
2509 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
2510 p_web_user_id IN NUMBER := FND_API.G_MISS_NUM,
2511 p_default_customization_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
2512 p_customization_level_id IN NUMBER := FND_API.G_MISS_NUM,
2513 p_developer_mode IN VARCHAR2 := FND_API.G_MISS_CHAR,
2514 p_reference_path IN VARCHAR2 := FND_API.G_MISS_CHAR,
2515 p_function_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2516 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
2517 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
2518 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
2519 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
2520 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
2521 p_creation_date IN DATE := FND_API.G_MISS_DATE,
2522 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
2523 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
2524 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
2525 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
2526 p_pass IN NUMBER,
2527 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
2528 ) is
2529 l_api_version_number CONSTANT number := 1.0;
2530 l_api_name CONSTANT varchar2(30) := 'Create_Custom';
2531 l_created_by NUMBER;
2532 l_creation_date DATE;
2533 l_last_update_date DATE;
2534 l_last_update_login NUMBER;
2535 l_last_updated_by NUMBER;
2536 l_description VARCHAR2(2000);
2537 l_name VARCHAR2(80);
2538 l_end_date_active DATE;
2539 l_start_date_active DATE;
2540 l_reference_path VARCHAR2(100);
2541 l_function_name VARCHAR2(30);
2542 l_customization_level_id NUMBER;
2543 l_developer_mode VARCHAR2(1);
2544 l_default_customization_flag VARCHAR2(1);
2545 l_web_user_id NUMBER;
2546 l_responsibility_id NUMBER;
2547 l_site_id NUMBER;
2548 l_org_id NUMBER;
2549 l_localization_code VARCHAR2(150);
2550 l_verticalization_id VARCHAR2(150);
2551 l_return_status VARCHAR2(1);
2552 l_lang VARCHAR2(30);
2553 begin
2554 IF NOT FND_API.Compatible_API_Call (
2555 l_api_version_number, p_api_version_number, l_api_name,
2556 G_PKG_NAME) then
2557 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2558 return;
2559 END IF;
2560
2561 -- Initialize the message table if requested.
2562
2563 if p_init_msg_tbl then
2564 FND_MSG_PUB.initialize;
2565 end if;
2566
2567 savepoint start_create_custom;
2568
2569 --** check to see if row already exists **
2570 if AK_CUSTOM_PVT.CUSTOM_EXISTS (
2571 p_api_version_number => 1.0,
2572 p_return_status => l_return_status,
2573 p_custom_appl_id => p_custom_appl_id,
2574 p_custom_code => p_custom_code,
2575 p_region_application_id => p_region_appl_id,
2576 p_region_code => p_region_code) then
2577 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2578 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_EXISTS');
2579 FND_MSG_PUB.Add;
2580 end if;
2581 --dbms_output.put_line(l_api_name || 'Error - row already exists');
2582 raise FND_API.G_EXC_ERROR;
2583 end if;
2584
2585 --** validate table columns passed in **
2586 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
2587 if not VALIDATE_CUSTOM (
2588 p_validation_level => p_validation_level,
2589 p_api_version_number => p_api_version_number,
2590 p_return_status => p_return_status,
2591 p_region_application_id => p_region_appl_id,
2592 p_region_code => p_region_code,
2593 p_custom_application_id => p_custom_appl_id,
2594 p_custom_code => p_custom_code,
2595 p_verticalization_id => p_verticalization_id,
2596 p_localization_code => p_localization_code,
2597 p_org_id => p_org_id,
2598 p_site_id => p_site_id,
2599 p_responsibility_id => p_responsibility_id,
2600 p_web_user_id => p_web_user_id,
2601 p_default_custom_flag => p_default_customization_flag,
2602 p_customization_level_id => p_customization_level_id,
2603 p_developer_mode => p_developer_mode,
2604 p_reference_path => p_reference_path,
2605 p_function_name => p_function_name,
2606 p_start_date_active => p_start_date_active,
2607 p_end_date_active => p_end_date_active,
2608 p_name => p_name,
2609 p_description => p_description,
2610 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
2611 p_pass => p_pass
2612 ) then
2613 -- Do not raise an error if it's the first pass
2614 if (p_pass = 1) then
2615 p_copy_redo_flag := TRUE;
2616 else
2617 raise FND_API.G_EXC_ERROR;
2618 end if;
2619 end if;
2620 end if;
2621
2622 --** Load non-required columns if their values are given **
2623 if (p_verticalization_id <> FND_API.G_MISS_CHAR) then
2624 l_verticalization_id := p_verticalization_id;
2625 end if;
2626
2627 if (p_localization_code <> FND_API.G_MISS_CHAR) then
2628 l_localization_code := p_localization_code;
2629 end if;
2630
2631 if (p_org_id <> FND_API.G_MISS_NUM) then
2632 l_org_id := p_org_id;
2633 end if;
2634
2635 if (p_site_id <> FND_API.G_MISS_NUM) then
2636 l_site_id := p_site_id;
2637 end if;
2638
2639 if (p_responsibility_id <> FND_API.G_MISS_NUM) then
2640 l_responsibility_id := p_responsibility_id;
2641 end if;
2642
2643 if (p_web_user_id <> FND_API.G_MISS_NUM) then
2644 l_web_user_id := p_web_user_id;
2645 end if;
2646
2647 if (p_default_customization_flag <> FND_API.G_MISS_CHAR) then
2648 l_default_customization_flag := p_default_customization_flag;
2649 end if;
2650
2651 if (p_end_date_active <> FND_API.G_MISS_DATE) then
2652 l_end_date_active := p_end_date_active;
2653 end if;
2654
2655 if (p_reference_path <> FND_API.G_MISS_CHAR) then
2656 l_reference_path := p_reference_path;
2657 end if;
2658
2659 if (p_function_name <> FND_API.G_MISS_CHAR) then
2660 l_function_name := p_function_name;
2661 end if;
2662
2663 if (p_description <> FND_API.G_MISS_CHAR) then
2664 l_description := p_description;
2665 end if;
2666
2667 if (p_created_by <> FND_API.G_MISS_NUM) then
2668 l_created_by := p_created_by;
2669 end if;
2670 if (p_creation_date <> FND_API.G_MISS_DATE) then
2671 l_creation_date := p_creation_date;
2672 end if;
2673 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
2674 l_last_updated_by := p_last_updated_by;
2675 end if;
2676 if (p_last_update_date <> FND_API.G_MISS_DATE) then
2677 l_last_update_date := p_last_update_date;
2678 end if;
2679 if (p_last_update_login <> FND_API.G_MISS_NUM) then
2680 l_last_update_login := p_last_update_login;
2681 end if;
2682
2683 -- Create record if no validation error was found
2684 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
2685 -- old jlt files didn't have who columns and IS_UPDATEABLE
2686 -- calls SET_WHO which populates those columns, for later
2687 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
2688
2689 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
2690 p_loader_timestamp => p_loader_timestamp,
2691 p_created_by => l_created_by,
2692 p_creation_date => l_creation_date,
2693 p_last_updated_by => l_last_updated_by,
2694 p_db_last_updated_by => null,
2695 p_last_update_date => l_last_update_date,
2696 p_db_last_update_date => null,
2697 p_last_update_login => l_last_update_login,
2698 p_create_or_update => 'CREATE') then
2699 null;
2700 end if;
2701
2702 select userenv('LANG') into l_lang
2703 from dual;
2704
2705 insert into AK_CUSTOMIZATIONS (
2706 CUSTOMIZATION_APPLICATION_ID,
2707 CUSTOMIZATION_CODE,
2708 REGION_APPLICATION_ID,
2709 REGION_CODE,
2710 VERTICALIZATION_ID,
2711 LOCALIZATION_CODE,
2712 ORG_ID,
2713 SITE_ID,
2714 RESPONSIBILITY_ID,
2715 WEB_USER_ID,
2716 DEFAULT_CUSTOMIZATION_FLAG,
2717 CUSTOMIZATION_LEVEL_ID,
2718 DEVELOPER_MODE,
2719 REFERENCE_PATH,
2720 FUNCTION_NAME,
2721 CREATED_BY,
2722 CREATION_DATE,
2723 LAST_UPDATE_DATE,
2724 LAST_UPDATED_BY,
2725 LAST_UPDATE_LOGIN,
2726 START_DATE_ACTIVE,
2727 END_DATE_ACTIVE
2728 ) values (
2729 p_custom_appl_id,
2730 p_custom_code,
2731 p_region_appl_id,
2732 p_region_code,
2733 l_verticalization_id,
2734 l_localization_code,
2735 l_org_id,
2736 l_site_id,
2737 l_responsibility_id,
2738 l_web_user_id,
2739 l_default_customization_flag,
2740 p_customization_level_id,
2741 p_developer_mode,
2742 l_reference_path,
2743 l_function_name,
2744 l_created_by,
2745 l_creation_date,
2746 l_last_update_date,
2747 l_last_updated_by,
2748 l_last_update_login,
2749 p_start_date_active,
2750 l_end_date_active);
2751
2752 --** row should exists before inserting rows for other languages **
2753 if NOT AK_CUSTOM_PVT.CUSTOM_EXISTS (
2754 p_api_version_number => 1.0,
2755 p_return_status => l_return_status,
2756 p_custom_appl_id => p_custom_appl_id,
2757 p_custom_code => p_custom_code,
2758 p_region_application_id => p_region_appl_id,
2759 p_region_code => p_region_code) then
2760
2761 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2762 FND_MESSAGE.SET_NAME('AK','AK_INSERT_CUSTOM_FAILED');
2763 FND_MSG_PUB.Add;
2764 end if;
2765 --dbms_output.put_line(l_api_name || 'Error - row already exists');
2766 raise FND_API.G_EXC_ERROR;
2767 end if;
2768
2769 insert into AK_CUSTOMIZATIONS_TL (
2770 CUSTOMIZATION_APPLICATION_ID,
2771 CUSTOMIZATION_CODE,
2772 REGION_APPLICATION_ID,
2773 REGION_CODE,
2774 NAME,
2775 DESCRIPTION,
2776 LANGUAGE,
2777 SOURCE_LANG,
2778 CREATED_BY,
2779 CREATION_DATE,
2780 LAST_UPDATED_BY,
2781 LAST_UPDATE_DATE,
2782 LAST_UPDATE_LOGIN
2783 ) select
2784 p_custom_appl_id,
2785 p_custom_code,
2786 p_region_appl_id,
2787 p_region_code,
2788 p_name,
2789 l_description,
2790 L.LANGUAGE_CODE,
2791 decode(L.LANGUAGE_CODE, l_lang, L.LANGUAGE_CODE, l_lang),
2792 l_created_by,
2793 l_creation_date,
2794 l_last_updated_by,
2795 l_last_update_date,
2796 l_last_update_login
2797 from FND_LANGUAGES L
2798 where L.INSTALLED_FLAG in ('I', 'B')
2799 and not exists
2800 (select NULL
2801 from AK_CUSTOMIZATIONS_TL T
2802 where T.CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
2803 and T.CUSTOMIZATION_CODE = p_custom_code
2804 and T.REGION_APPLICATION_ID = p_region_appl_id
2805 and T.REGION_CODE = p_region_code
2806 and T.LANGUAGE = L.LANGUAGE_CODE);
2807
2808 -- /** commit the insert **/
2809 commit;
2810
2811 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
2812 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_CREATED');
2813 FND_MESSAGE.SET_TOKEN('OBJECT', 'AK_LC_CUSTOM',TRUE);
2814 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
2815 ' ' || p_custom_code || ' ' || to_char(p_region_appl_id) || ' ' || p_region_code || ' ' || p_name);
2816 FND_MSG_PUB.Add;
2817 end if;
2818
2819 p_return_status := FND_API.G_RET_STS_SUCCESS;
2820
2821 FND_MSG_PUB.Count_And_Get (
2822 p_count => p_msg_count,
2823 p_data => p_msg_data);
2824
2825 EXCEPTION
2826 WHEN VALUE_ERROR THEN
2827 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2828 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_VALUE_ERROR');
2829 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
2830 ' ' || p_custom_code || ' ' ||
2831 to_char(p_region_appl_id) || ' ' ||
2832 p_region_code ||
2833 ' ' || p_name);
2834 FND_MSG_PUB.Add;
2835 end if;
2836 p_return_status := FND_API.G_RET_STS_ERROR;
2837 rollback to start_create_custom;
2838 FND_MSG_PUB.Count_And_Get (
2839 p_count => p_msg_count,
2840 p_data => p_msg_data);
2841 WHEN FND_API.G_EXC_ERROR THEN
2842 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2843 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_NOT_CREATED');
2844 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
2845 ' ' || p_custom_code || ' ' ||
2846 to_char(p_region_appl_id) || ' ' ||
2847 p_region_code ||
2848 ' ' || p_name);
2849 FND_MSG_PUB.Add;
2850 end if;
2851 p_return_status := FND_API.G_RET_STS_ERROR;
2852 rollback to start_create_custom;
2853 FND_MSG_PUB.Count_And_Get (
2854 p_count => p_msg_count,
2855 p_data => p_msg_data);
2856 WHEN OTHERS THEN
2857 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2858 rollback to start_create_custom;
2859 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2860 SUBSTR (SQLERRM, 1, 240) );
2861 FND_MSG_PUB.Add;
2862 FND_MSG_PUB.Count_And_Get (
2863 p_count => p_msg_count,
2864 p_data => p_msg_data);
2865 end CREATE_CUSTOM;
2866
2867 --=======================================================
2868 -- Procedure CREATE_CUST_REGION
2869 --
2870 -- Usage Private API for creating a region graph. This
2871 -- API should only be called by other APIs that are
2872 -- owned by the Core Modules Team (AK).
2873 --
2874 -- Desc Creates a region graph using the given info.
2875 -- This API should only be called by other APIs that are
2876 -- owned by the Core Modules Team (AK).
2877 --
2878 -- Results The API returns the standard p_return_status parameter
2879 -- indicating one of the standard return statuses :
2880 -- * Unexpected error
2881 -- * Error
2882 -- * Success
2883 -- Parameters Region Item columns
2884 -- p_loader_timestamp : IN optional
2885 -- If a timestamp is passed, the API will create the
2886 -- record using this timestamp. Only the upload API
2887 -- should call with this parameter loaded.
2888 --
2889 -- Version Initial version number = 1.0
2890 -- History Current version number = 1.0
2891 --=======================================================
2892 procedure CREATE_CUST_REGION (
2893 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2894 p_api_version_number IN NUMBER,
2895 p_init_msg_tbl IN BOOLEAN := FALSE,
2896 p_msg_count OUT NOCOPY NUMBER,
2897 p_msg_data OUT NOCOPY VARCHAR2,
2898 p_return_status OUT NOCOPY VARCHAR2,
2899 p_custom_appl_id IN NUMBER,
2900 p_custom_code IN VARCHAR2,
2901 p_region_appl_id IN NUMBER,
2902 p_region_code IN VARCHAR2,
2903 p_property_name IN VARCHAR2,
2904 p_property_varchar2_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
2905 p_property_number_value IN NUMBER := FND_API.G_MISS_NUM,
2906 p_criteria_join_condition IN VARCHAR2 := FND_API.G_MISS_CHAR,
2907 p_property_varchar2_value_tl IN VARCHAR2 := FND_API.G_MISS_CHAR,
2908 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
2909 p_creation_date IN DATE := FND_API.G_MISS_DATE,
2910 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
2911 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
2912 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
2913 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
2914 p_pass IN NUMBER,
2915 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
2916 ) is
2917 l_api_version_number CONSTANT number := 1.0;
2918 l_api_name CONSTANT varchar2(30) := 'Create_Cust_Region';
2919 l_created_by NUMBER;
2920 l_creation_date DATE;
2921 l_last_update_date DATE;
2922 l_last_update_login NUMBER;
2923 l_last_updated_by NUMBER;
2924 l_property_varchar2_value VARCHAR2(2000);
2925 l_criteria_join_condition VARCHAR2(3);
2926 l_property_number_value NUMBER;
2927 l_property_varchar2_value_tl VARCHAR2(2000);
2928 l_return_status VARCHAR2(1);
2929 l_lang VARCHAR2(30);
2930 begin
2931 IF NOT FND_API.Compatible_API_Call (
2932 l_api_version_number, p_api_version_number, l_api_name,
2933 G_PKG_NAME) then
2934 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2935 return;
2936 END IF;
2937
2938 -- Initialize the message table if requested.
2939
2940 if p_init_msg_tbl then
2941 FND_MSG_PUB.initialize;
2942 end if;
2943
2944 savepoint start_create_cust_region;
2945
2946 --** check to see if row already exists **
2947 if AK_CUSTOM_PVT.CUST_REGION_EXISTS (
2948 p_api_version_number => 1.0,
2949 p_return_status => l_return_status,
2950 p_custom_appl_id => p_custom_appl_id,
2951 p_custom_code => p_custom_code,
2952 p_region_application_id => p_region_appl_id,
2953 p_region_code => p_region_code,
2954 p_property_name => p_property_name) then
2955 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
2956 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_EXISTS');
2957 FND_MSG_PUB.Add;
2958 end if;
2959 --dbms_output.put_line(l_api_name || 'Error - row already exists');
2960 raise FND_API.G_EXC_ERROR;
2961 end if;
2962
2963 --** validate table columns passed in **
2964 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
2965 if not VALIDATE_CUST_REGION (
2966 p_validation_level => p_validation_level,
2967 p_api_version_number => p_api_version_number,
2968 p_return_status => p_return_status,
2969 p_region_application_id => p_region_appl_id,
2970 p_region_code => p_region_code,
2971 p_custom_application_id => p_custom_appl_id,
2972 p_custom_code => p_custom_code,
2973 p_property_name => p_property_name,
2974 p_property_varchar2_value => p_property_varchar2_value,
2975 p_property_number_value => p_property_number_value,
2976 p_criteria_join_condition => p_criteria_join_condition,
2977 p_property_varchar2_value_tl => p_property_varchar2_value,
2978 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
2979 p_pass => p_pass
2980 ) then
2981 -- Do not raise an error if it's the first pass
2982 if (p_pass = 1) then
2983 p_copy_redo_flag := TRUE;
2984 else
2985 raise FND_API.G_EXC_ERROR;
2986 end if;
2987 end if;
2988 end if;
2989
2990 --** Load non-required columns if their values are given **
2991 if (p_property_varchar2_value <> FND_API.G_MISS_CHAR) then
2992 l_property_varchar2_value := p_property_varchar2_value;
2993 end if;
2994
2995 if (p_property_number_value <> FND_API.G_MISS_NUM) then
2996 l_property_number_value := p_property_number_value;
2997 end if;
2998
2999 if (p_criteria_join_condition <> FND_API.G_MISS_CHAR) then
3000 l_criteria_join_condition := p_criteria_join_condition;
3001 end if;
3002
3003 if (p_property_varchar2_value_tl <> FND_API.G_MISS_CHAR) then
3004 l_property_varchar2_value_tl := p_property_varchar2_value_tl;
3005 end if;
3006
3007 if (p_created_by <> FND_API.G_MISS_NUM) then
3008 l_created_by := p_created_by;
3009 end if;
3010 if (p_creation_date <> FND_API.G_MISS_DATE) then
3011 l_creation_date := p_creation_date;
3012 end if;
3013 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
3014 l_last_updated_by := p_last_updated_by;
3015 end if;
3016 if (p_last_update_date <> FND_API.G_MISS_DATE) then
3017 l_last_update_date := p_last_update_date;
3018 end if;
3019 if (p_last_update_login <> FND_API.G_MISS_NUM) then
3020 l_last_update_login := p_last_update_login;
3021 end if;
3022
3023 -- Create record if no validation error was found
3024 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
3025 -- old jlt files didn't have who columns and IS_UPDATEABLE
3026 -- calls SET_WHO which populates those columns, for later
3027 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
3028
3029 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
3030 p_loader_timestamp => p_loader_timestamp,
3031 p_created_by => l_created_by,
3032 p_creation_date => l_creation_date,
3033 p_last_updated_by => l_last_updated_by,
3034 p_db_last_updated_by => null,
3035 p_last_update_date => l_last_update_date,
3036 p_db_last_update_date => null,
3037 p_last_update_login => l_last_update_login,
3038 p_create_or_update => 'CREATE') then
3039 null;
3040 end if;
3041
3042 select userenv('LANG') into l_lang
3043 from dual;
3044
3045 insert into AK_CUSTOM_REGIONS (
3046 CUSTOMIZATION_APPLICATION_ID,
3047 CUSTOMIZATION_CODE,
3048 REGION_APPLICATION_ID,
3049 REGION_CODE,
3050 PROPERTY_NAME,
3051 PROPERTY_VARCHAR2_VALUE,
3052 PROPERTY_NUMBER_VALUE,
3053 CRITERIA_JOIN_CONDITION,
3054 CREATED_BY,
3055 CREATION_DATE,
3056 LAST_UPDATED_BY,
3057 LAST_UPDATE_DATE,
3058 LAST_UPDATE_LOGIN
3059 ) values (
3060 p_custom_appl_id,
3061 p_custom_code,
3062 p_region_appl_id,
3063 p_region_code,
3064 p_property_name,
3065 l_property_varchar2_value,
3066 l_property_number_value,
3067 l_criteria_join_condition,
3068 l_created_by,
3069 l_creation_date,
3070 l_last_updated_by,
3071 l_last_update_date,
3072 l_last_update_login);
3073
3074 --** row should exists before inserting rows for other languages **
3075 if NOT AK_CUSTOM_PVT.CUST_REGION_EXISTS (
3076 p_api_version_number => 1.0,
3077 p_return_status => l_return_status,
3078 p_custom_appl_id => p_custom_appl_id,
3079 p_custom_code => p_custom_code,
3080 p_region_application_id => p_region_appl_id,
3081 p_region_code => p_region_code,
3082 p_property_name => p_property_name) then
3083
3084 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3085 FND_MESSAGE.SET_NAME('AK','AK_INSERT_CUST_REGION_FAILED');
3086 FND_MSG_PUB.Add;
3087 end if;
3088 --dbms_output.put_line(l_api_name || 'Error - row already exists');
3089 raise FND_API.G_EXC_ERROR;
3090 end if;
3091
3092 insert into AK_CUSTOM_REGIONS_TL (
3093 CUSTOMIZATION_APPLICATION_ID,
3094 CUSTOMIZATION_CODE,
3095 REGION_APPLICATION_ID,
3096 REGION_CODE,
3097 PROPERTY_NAME,
3098 PROPERTY_VARCHAR2_VALUE,
3099 LANGUAGE,
3100 SOURCE_LANG,
3101 CREATED_BY,
3102 CREATION_DATE,
3103 LAST_UPDATED_BY,
3104 LAST_UPDATE_DATE,
3105 LAST_UPDATE_LOGIN
3106 ) select
3107 p_custom_appl_id,
3108 p_custom_code,
3109 p_region_appl_id,
3110 p_region_code,
3111 p_property_name,
3112 l_property_varchar2_value,
3113 L.LANGUAGE_CODE,
3114 decode(L.LANGUAGE_CODE, l_lang, L.LANGUAGE_CODE, l_lang),
3115 l.created_by,
3116 l_creation_date,
3117 l_last_updated_by,
3118 l_last_update_date,
3119 l_last_update_login
3120 from FND_LANGUAGES L
3121 where L.INSTALLED_FLAG in ('I', 'B')
3122 and not exists
3123 (select NULL
3124 from AK_CUSTOM_REGIONS_TL T
3125 where T.CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
3126 and T.CUSTOMIZATION_CODE = p_custom_code
3127 and T.REGION_APPLICATION_ID = p_region_appl_id
3128 and T.REGION_CODE = p_region_code
3129 and T.PROPERTY_NAME = p_property_name
3130 and T.LANGUAGE = L.LANGUAGE_CODE);
3131
3132 -- /** commit the insert **/
3133 commit;
3134
3135 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3136 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_CREATED');
3137 FND_MESSAGE.SET_TOKEN('OBJECT', 'AK_LC_CUST_REGION',TRUE);
3138 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3139 ' ' || p_custom_code || ' ' ||
3140 to_char(p_region_appl_id) || ' ' ||
3141 p_region_code ||
3142 ' ' || p_property_name);
3143 FND_MSG_PUB.Add;
3144 end if;
3145 p_return_status := FND_API.G_RET_STS_SUCCESS;
3146
3147 FND_MSG_PUB.Count_And_Get (
3148 p_count => p_msg_count,
3149 p_data => p_msg_data);
3150
3151 EXCEPTION
3152 WHEN VALUE_ERROR THEN
3153 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3154 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_VALUE_ERROR');
3155 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3156 ' ' || p_custom_code || ' ' ||
3157 to_char(p_region_appl_id) || ' ' ||
3158 p_region_code ||
3159 ' ' || p_property_name);
3160 FND_MSG_PUB.Add;
3161 end if;
3162 p_return_status := FND_API.G_RET_STS_ERROR;
3163 rollback to start_create_cust_region;
3164 FND_MSG_PUB.Count_And_Get (
3165 p_count => p_msg_count,
3166 p_data => p_msg_data);
3167 WHEN FND_API.G_EXC_ERROR THEN
3168 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3169 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_NOT_CREATED');
3170 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3171 ' ' || p_custom_code || ' ' ||
3172 to_char(p_region_appl_id) || ' ' ||
3173 p_region_code ||
3174 ' ' || p_property_name);
3175 FND_MSG_PUB.Add;
3176 end if;
3177 p_return_status := FND_API.G_RET_STS_ERROR;
3178 rollback to start_create_cust_region;
3179 FND_MSG_PUB.Count_And_Get (
3180 p_count => p_msg_count,
3181 p_data => p_msg_data);
3182 WHEN OTHERS THEN
3183 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3184 rollback to start_create_cust_region;
3185 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3186 SUBSTR (SQLERRM, 1, 240) );
3187 FND_MSG_PUB.Add;
3188 FND_MSG_PUB.Count_And_Get (
3189 p_count => p_msg_count,
3190 p_data => p_msg_data);
3191 end CREATE_CUST_REGION;
3192
3193 --=======================================================
3194 -- Procedure CREATE_CUST_REG_ITEM
3195 --
3196 -- Usage Private API for creating a region graph. This
3197 -- API should only be called by other APIs that are
3198 -- owned by the Core Modules Team (AK).
3199 --
3200 -- Desc Creates a region graph using the given info.
3201 -- This API should only be called by other APIs that are
3202 -- owned by the Core Modules Team (AK).
3203 --
3204 -- Results The API returns the standard p_return_status parameter
3205 -- indicating one of the standard return statuses :
3206 -- * Unexpected error
3207 -- * Error
3208 -- * Success
3209 -- Parameters Region Item columns
3210 -- p_loader_timestamp : IN optional
3211 -- If a timestamp is passed, the API will create the
3212 -- record using this timestamp. Only the upload API
3213 -- should call with this parameter loaded.
3214 --
3215 -- Version Initial version number = 1.0
3216 -- History Current version number = 1.0
3217 --=======================================================
3218 procedure CREATE_CUST_REG_ITEM (
3219 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3220 p_api_version_number IN NUMBER,
3221 p_init_msg_tbl IN BOOLEAN := FALSE,
3222 p_msg_count OUT NOCOPY NUMBER,
3223 p_msg_data OUT NOCOPY VARCHAR2,
3224 p_return_status OUT NOCOPY VARCHAR2,
3225 p_custom_appl_id IN NUMBER,
3226 p_custom_code IN VARCHAR2,
3227 p_region_appl_id IN NUMBER,
3228 p_region_code IN VARCHAR2,
3229 p_attr_appl_id IN NUMBER,
3230 p_attr_code IN VARCHAR2,
3231 p_property_name IN VARCHAR2,
3232 p_property_varchar2_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
3233 p_property_number_value IN NUMBER := FND_API.G_MISS_NUM,
3234 p_property_date_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
3235 p_property_varchar2_value_tl IN VARCHAR2 := FND_API.G_MISS_CHAR,
3236 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
3237 p_creation_date IN DATE := FND_API.G_MISS_DATE,
3238 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
3239 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
3240 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
3241 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
3242 p_pass IN NUMBER,
3243 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
3244 ) is
3245 l_api_version_number CONSTANT number := 1.0;
3246 l_api_name CONSTANT varchar2(30) := 'Create_Cust_Reg_Item';
3247 l_created_by NUMBER;
3248 l_creation_date DATE;
3249 l_last_update_date DATE;
3250 l_last_update_login NUMBER;
3251 l_last_updated_by NUMBER;
3252 l_property_varchar2_value VARCHAR2(4000);
3253 l_property_date_value DATE;
3254 l_property_number_value NUMBER;
3255 l_property_varchar2_value_tl VARCHAR2(4000);
3256 l_return_status VARCHAR2(1);
3257 l_lang VARCHAR2(30);
3258 begin
3259 IF NOT FND_API.Compatible_API_Call (
3260 l_api_version_number, p_api_version_number, l_api_name,
3261 G_PKG_NAME) then
3262 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3263 return;
3264 END IF;
3265
3266 -- Initialize the message table if requested.
3267
3268 if p_init_msg_tbl then
3269 FND_MSG_PUB.initialize;
3270 end if;
3271
3272 savepoint start_create_cust_reg_item;
3273
3274 --** check to see if row already exists **
3275 if AK_CUSTOM_PVT.CUST_REG_ITEM_EXISTS (
3276 p_api_version_number => 1.0,
3277 p_return_status => l_return_status,
3278 p_custom_appl_id => p_custom_appl_id,
3279 p_custom_code => p_custom_code,
3280 p_region_application_id => p_region_appl_id,
3281 p_region_code => p_region_code,
3282 p_attribute_appl_id => p_attr_appl_id,
3283 p_attribute_code => p_attr_code,
3284 p_property_name => p_property_name) then
3285 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3286 FND_MESSAGE.SET_NAME('AK','AK_CUST_REG_ITEM_EXISTS');
3287 FND_MSG_PUB.Add;
3288 end if;
3289 --dbms_output.put_line(l_api_name || 'Error - row already exists');
3290 raise FND_API.G_EXC_ERROR;
3291 end if;
3292
3293 --** validate table columns passed in **
3294 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
3295 if not VALIDATE_CUST_REGION_ITEM (
3296 p_validation_level => p_validation_level,
3297 p_api_version_number => p_api_version_number,
3298 p_return_status => p_return_status,
3299 p_region_application_id => p_region_appl_id,
3300 p_region_code => p_region_code,
3301 p_custom_application_id => p_custom_appl_id,
3302 p_custom_code => p_custom_code,
3303 p_attr_appl_id => p_attr_appl_id,
3304 p_attr_code => p_attr_code,
3305 p_property_name => p_property_name,
3306 p_property_varchar2_value => p_property_varchar2_value,
3307 p_property_number_value => p_property_number_value,
3308 p_property_date_value => p_property_date_value,
3309 p_property_varchar2_value_tl => p_property_varchar2_value_tl,
3310 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
3311 p_pass => p_pass
3312 ) then
3313 -- Do not raise an error if it's the first pass
3314 if (p_pass = 1) then
3315 p_copy_redo_flag := TRUE;
3316 else
3317 raise FND_API.G_EXC_ERROR;
3318 end if;
3319 end if;
3320 end if;
3321
3322 --** Load non-required columns if their values are given **
3323 if (p_property_varchar2_value <> FND_API.G_MISS_CHAR) then
3324 l_property_varchar2_value := p_property_varchar2_value;
3325 end if;
3326
3327 if (p_property_number_value <> FND_API.G_MISS_NUM) then
3328 l_property_number_value := p_property_number_value;
3329 end if;
3330
3331 if (p_property_date_value <> FND_API.G_MISS_CHAR) then
3332 l_property_date_value := p_property_date_value;
3333 end if;
3334
3335 if (p_property_varchar2_value_tl <> FND_API.G_MISS_CHAR) then
3336 l_property_varchar2_value_tl := p_property_varchar2_value_tl;
3337 end if;
3338
3339 if (p_created_by <> FND_API.G_MISS_NUM) then
3340 l_created_by := p_created_by;
3341 end if;
3342 if (p_creation_date <> FND_API.G_MISS_DATE) then
3343 l_creation_date := p_creation_date;
3344 end if;
3345 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
3346 l_last_updated_by := p_last_updated_by;
3347 end if;
3348 if (p_last_update_date <> FND_API.G_MISS_DATE) then
3349 l_last_update_date := p_last_update_date;
3350 end if;
3351 if (p_last_update_login <> FND_API.G_MISS_NUM) then
3352 l_last_update_login := p_last_update_login;
3353 end if;
3354
3355 -- Create record if no validation error was found
3356 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
3357 -- old jlt files didn't have who columns and IS_UPDATEABLE
3358 -- calls SET_WHO which populates those columns, for later
3359 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
3360
3361 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
3362 p_loader_timestamp => p_loader_timestamp,
3363 p_created_by => l_created_by,
3364 p_creation_date => l_creation_date,
3365 p_last_updated_by => l_last_updated_by,
3366 p_db_last_updated_by => null,
3367 p_last_update_date => l_last_update_date,
3368 p_db_last_update_date => null,
3369 p_last_update_login => l_last_update_login,
3370 p_create_or_update => 'CREATE') then
3371 null;
3372 end if;
3373
3374 select userenv('LANG') into l_lang
3375 from dual;
3376
3377 insert into AK_CUSTOM_REGION_ITEMS (
3378 CUSTOMIZATION_APPLICATION_ID,
3379 CUSTOMIZATION_CODE,
3380 REGION_APPLICATION_ID,
3381 REGION_CODE,
3382 ATTRIBUTE_APPLICATION_ID,
3383 ATTRIBUTE_CODE,
3384 PROPERTY_NAME,
3385 PROPERTY_VARCHAR2_VALUE,
3386 PROPERTY_NUMBER_VALUE,
3387 PROPERTY_DATE_VALUE,
3388 CREATED_BY,
3389 CREATION_DATE,
3390 LAST_UPDATED_BY,
3391 LAST_UPDATE_DATE,
3392 LAST_UPDATE_LOGIN
3393 ) values (
3394 p_custom_appl_id,
3395 p_custom_code,
3396 p_region_appl_id,
3397 p_region_code,
3398 p_attr_appl_id,
3399 p_attr_code,
3400 p_property_name,
3401 l_property_varchar2_value,
3402 l_property_number_value,
3403 l_property_date_value,
3404 l_created_by,
3405 l_creation_date,
3406 l_last_updated_by,
3407 l_last_update_date,
3408 l_last_update_login);
3409
3410 --** row should exists before inserting rows for other languages **
3411 if NOT AK_CUSTOM_PVT.CUST_REG_ITEM_EXISTS (
3412 p_api_version_number => 1.0,
3413 p_return_status => l_return_status,
3414 p_custom_appl_id => p_custom_appl_id,
3415 p_custom_code => p_custom_code,
3416 p_region_application_id => p_region_appl_id,
3417 p_region_code => p_region_code,
3418 p_attribute_appl_id => p_attr_appl_id,
3419 p_attribute_code => p_attr_code,
3420 p_property_name => p_property_name) then
3421
3422 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3423 FND_MESSAGE.SET_NAME('AK','AK_INSERT_CUST_REG_ITEM_FAILED');
3424 FND_MSG_PUB.Add;
3425 end if;
3426 --dbms_output.put_line(l_api_name || 'Error - row already exists');
3427 raise FND_API.G_EXC_ERROR;
3428 end if;
3429
3430 insert into AK_CUSTOM_REGION_ITEMS_TL (
3431 CUSTOMIZATION_APPLICATION_ID,
3432 CUSTOMIZATION_CODE,
3433 REGION_APPLICATION_ID,
3434 REGION_CODE,
3435 ATTRIBUTE_APPLICATION_ID,
3436 ATTRIBUTE_CODE,
3437 PROPERTY_NAME,
3438 PROPERTY_VARCHAR2_VALUE,
3439 LANGUAGE,
3440 SOURCE_LANG,
3441 CREATED_BY,
3442 CREATION_DATE,
3443 LAST_UPDATED_BY,
3444 LAST_UPDATE_DATE,
3445 LAST_UPDATE_LOGIN
3446 ) select
3447 p_custom_appl_id,
3448 p_custom_code,
3449 p_region_appl_id,
3450 p_region_code,
3451 p_attr_appl_id,
3452 p_attr_code,
3453 p_property_name,
3454 l_property_varchar2_value_tl,
3455 L.LANGUAGE_CODE,
3456 decode(L.LANGUAGE_CODE, l_lang, L.LANGUAGE_CODE, l_lang),
3457 l.created_by,
3458 l_creation_date,
3459 l_last_updated_by,
3460 l_last_update_date,
3461 l_last_update_login
3462 from FND_LANGUAGES L
3463 where L.INSTALLED_FLAG in ('I', 'B')
3464 and not exists
3465 (select NULL
3466 from AK_CUSTOM_REGION_ITEMS_TL T
3467 where T.CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
3468 and T.CUSTOMIZATION_CODE = p_custom_code
3469 and T.REGION_APPLICATION_ID = p_region_appl_id
3470 and T.REGION_CODE = p_region_code
3471 and T.ATTRIBUTE_APPLICATION_ID = p_attr_appl_id
3472 and T.ATTRIBUTE_CODE = p_attr_code
3473 and T.PROPERTY_NAME = p_property_name
3474 and T.LANGUAGE = L.LANGUAGE_CODE);
3475
3476 -- /** commit the insert **/
3477 commit;
3478
3479 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3480 FND_MESSAGE.SET_NAME('AK','AK_CUST_REG_ITEM_CREATED');
3481 FND_MESSAGE.SET_TOKEN('OBJECT', 'AK_LC_CUST_REG_ITEM',TRUE);
3482 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3483 ' ' || p_custom_code || ' ' ||
3484 to_char(p_region_appl_id) || ' ' ||
3485 p_region_code || ' ' ||
3486 to_char(p_attr_appl_id) || ' ' ||
3487 p_attr_code || ' ' || p_property_name);
3488 FND_MSG_PUB.Add;
3489 end if;
3490
3491 p_return_status := FND_API.G_RET_STS_SUCCESS;
3492
3493 FND_MSG_PUB.Count_And_Get (
3494 p_count => p_msg_count,
3495 p_data => p_msg_data);
3496
3497 EXCEPTION
3498 WHEN VALUE_ERROR THEN
3499 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3500 FND_MESSAGE.SET_NAME('AK','AK_CUST_REG_ITEM_VALUE_ERROR');
3501 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3502 ' ' || p_custom_code || ' ' ||
3503 to_char(p_region_appl_id) || ' ' ||
3504 p_region_code || ' ' ||
3505 to_char(p_attr_appl_id) || ' ' ||
3506 p_attr_code || ' ' || p_property_name);
3507 FND_MSG_PUB.Add;
3508 end if;
3509 p_return_status := FND_API.G_RET_STS_ERROR;
3510 rollback to start_create_cust_reg_item;
3511 FND_MSG_PUB.Count_And_Get (
3512 p_count => p_msg_count,
3513 p_data => p_msg_data);
3514 WHEN FND_API.G_EXC_ERROR THEN
3515 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3516 FND_MESSAGE.SET_NAME('AK','AK_CUST_REG_ITEM_NOT_CREATED');
3517 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3518 ' ' || p_custom_code || ' ' ||
3519 to_char(p_region_appl_id) || ' ' ||
3520 p_region_code || ' ' ||
3521 to_char(p_attr_appl_id) || ' ' ||
3522 p_attr_code || ' ' || p_property_name);
3523 FND_MSG_PUB.Add;
3524 end if;
3525 p_return_status := FND_API.G_RET_STS_ERROR;
3526 rollback to start_create_cust_reg_item;
3527 FND_MSG_PUB.Count_And_Get (
3528 p_count => p_msg_count,
3529 p_data => p_msg_data);
3530 WHEN OTHERS THEN
3531 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3532 rollback to start_create_cust_reg_item;
3533 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3534 SUBSTR (SQLERRM, 1, 240) );
3535 FND_MSG_PUB.Add;
3536 FND_MSG_PUB.Count_And_Get (
3537 p_count => p_msg_count,
3538 p_data => p_msg_data);
3539 end CREATE_CUST_REG_ITEM;
3540
3541 --=======================================================
3542 -- Procedure CREATE_CRITERIA
3543 --
3544 -- Usage Private API for creating a region graph. This
3545 -- API should only be called by other APIs that are
3546 -- owned by the Core Modules Team (AK).
3547 --
3548 -- Desc Creates a region graph using the given info.
3549 -- This API should only be called by other APIs that are
3550 -- owned by the Core Modules Team (AK).
3551 --
3552 -- Results The API returns the standard p_return_status parameter
3553 -- indicating one of the standard return statuses :
3554 -- * Unexpected error
3555 -- * Error
3556 -- * Success
3557 -- Parameters Region Item columns
3558 -- p_loader_timestamp : IN optional
3559 -- If a timestamp is passed, the API will create the
3560 -- record using this timestamp. Only the upload API
3561 -- should call with this parameter loaded.
3562 --
3563 -- Version Initial version number = 1.0
3564 -- History Current version number = 1.0
3565 --=======================================================
3566 procedure CREATE_CRITERIA (
3567 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3568 p_api_version_number IN NUMBER,
3569 p_init_msg_tbl IN BOOLEAN := FALSE,
3570 p_msg_count OUT NOCOPY NUMBER,
3571 p_msg_data OUT NOCOPY VARCHAR2,
3572 p_return_status OUT NOCOPY VARCHAR2,
3573 p_custom_appl_id IN NUMBER,
3574 p_custom_code IN VARCHAR2,
3575 p_region_appl_id IN NUMBER,
3576 p_region_code IN VARCHAR2,
3577 p_attr_appl_id IN NUMBER,
3578 p_attr_code IN VARCHAR2,
3579 p_sequence_number IN NUMBER,
3580 p_operation IN VARCHAR2 := FND_API.G_MISS_CHAR,
3581 p_value_varchar2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
3582 p_value_number IN NUMBER := FND_API.G_MISS_NUM,
3583 p_value_date IN DATE := FND_API.G_MISS_DATE,
3584 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
3585 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
3586 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
3587 p_creation_date IN DATE := FND_API.G_MISS_DATE,
3588 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
3589 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
3590 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
3591 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
3592 p_pass IN NUMBER,
3593 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
3594 ) is
3595 l_api_version_number CONSTANT number := 1.0;
3596 l_api_name CONSTANT varchar2(30) := 'Create_Criteria';
3597 l_created_by NUMBER;
3598 l_creation_date DATE;
3599 l_last_update_date DATE;
3600 l_last_update_login NUMBER;
3601 l_last_updated_by NUMBER;
3602 l_value_varchar2 VARCHAR2(240);
3603 l_value_number NUMBER;
3604 l_value_date DATE;
3605 l_start_date_active DATE;
3606 l_end_date_active DATE;
3607 l_return_status VARCHAR2(1);
3608 l_lang VARCHAR2(30);
3609 begin
3610 IF NOT FND_API.Compatible_API_Call (
3611 l_api_version_number, p_api_version_number, l_api_name,
3612 G_PKG_NAME) then
3613 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3614 return;
3615 END IF;
3616
3617 -- Initialize the message table if requested.
3618
3619 if p_init_msg_tbl then
3620 FND_MSG_PUB.initialize;
3621 end if;
3622
3623 savepoint start_create_criteria;
3624
3625 --** check to see if row already exists **
3626 if AK_CUSTOM_PVT.CRITERIA_EXISTS (
3627 p_api_version_number => 1.0,
3628 p_return_status => l_return_status,
3629 p_custom_appl_id => p_custom_appl_id,
3630 p_custom_code => p_custom_code,
3631 p_region_application_id => p_region_appl_id,
3632 p_region_code => p_region_code,
3633 p_attribute_appl_id => p_attr_appl_id,
3634 p_attribute_code => p_attr_code,
3635 p_sequence_number => p_sequence_number) then
3636 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
3637 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_EXISTS');
3638 FND_MSG_PUB.Add;
3639 end if;
3640 --dbms_output.put_line(l_api_name || 'Error - row already exists');
3641 raise FND_API.G_EXC_ERROR;
3642 end if;
3643
3644 --** validate table columns passed in **
3645 if (p_validation_level <> FND_API.G_VALID_LEVEL_NONE) then
3646 if not VALIDATE_CRITERIA (
3647 p_validation_level => p_validation_level,
3648 p_api_version_number => p_api_version_number,
3649 p_return_status => p_return_status,
3650 p_region_application_id => p_region_appl_id,
3651 p_region_code => p_region_code,
3652 p_custom_application_id => p_custom_appl_id,
3653 p_custom_code => p_custom_code,
3654 p_attr_appl_id => p_attr_appl_id,
3655 p_attr_code => p_attr_code,
3656 p_sequence_number => p_sequence_number,
3657 p_operation => p_operation,
3658 p_value_varchar2 => p_value_varchar2,
3659 p_value_number => p_value_number,
3660 p_value_date => p_value_date,
3661 p_start_date_active => p_start_date_active,
3662 p_end_date_active => p_end_date_active,
3663 p_caller => AK_ON_OBJECTS_PVT.G_CREATE,
3664 p_pass => p_pass
3665 ) then
3666 -- Do not raise an error if it's the first pass
3667 if (p_pass = 1) then
3668 p_copy_redo_flag := TRUE;
3669 else
3670 raise FND_API.G_EXC_ERROR;
3671 end if;
3672 end if;
3673 end if;
3674
3675 --** Load non-required columns if their values are given **
3676 if (p_value_varchar2 <> FND_API.G_MISS_CHAR) then
3677 l_value_varchar2 := p_value_varchar2;
3678 end if;
3679
3680 if (p_value_number <> FND_API.G_MISS_NUM) then
3681 l_value_number := p_value_number;
3682 end if;
3683
3684 if (p_value_date <> FND_API.G_MISS_DATE) then
3685 l_value_date := p_value_date;
3686 end if;
3687
3688 if (p_end_date_active <> FND_API.G_MISS_DATE) then
3689 l_end_date_active := p_end_date_active;
3690 end if;
3691
3692 if (p_created_by <> FND_API.G_MISS_NUM) then
3693 l_created_by := p_created_by;
3694 end if;
3695
3696 if (p_creation_date <> FND_API.G_MISS_DATE) then
3697 l_creation_date := p_creation_date;
3698 end if;
3699
3700 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
3701 l_last_updated_by := p_last_updated_by;
3702 end if;
3703
3704 if (p_last_update_date <> FND_API.G_MISS_DATE) then
3705 l_last_update_date := p_last_update_date;
3706 end if;
3707
3708 if (p_last_update_login <> FND_API.G_MISS_NUM) then
3709 l_last_update_login := p_last_update_login;
3710 end if;
3711
3712 -- Create record if no validation error was found
3713 -- NOTE - Calling IS_UPDATEABLE for backward compatibility
3714 -- old jlt files didn't have who columns and IS_UPDATEABLE
3715 -- calls SET_WHO which populates those columns, for later
3716 -- jlt files IS_UPDATEABLE will always return TRUE for CREATE
3717
3718 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
3719 p_loader_timestamp => p_loader_timestamp,
3720 p_created_by => l_created_by,
3721 p_creation_date => l_creation_date,
3722 p_last_updated_by => l_last_updated_by,
3723 p_db_last_updated_by => null,
3724 p_last_update_date => l_last_update_date,
3725 p_db_last_update_date => null,
3726 p_last_update_login => l_last_update_login,
3727 p_create_or_update => 'CREATE') then
3728 null;
3729 end if;
3730
3731 select userenv('LANG') into l_lang
3732 from dual;
3733
3734 insert into AK_CRITERIA (
3735 CUSTOMIZATION_APPLICATION_ID,
3736 CUSTOMIZATION_CODE,
3737 REGION_APPLICATION_ID,
3738 REGION_CODE,
3739 ATTRIBUTE_APPLICATION_ID,
3740 ATTRIBUTE_CODE,
3741 SEQUENCE_NUMBER,
3742 OPERATION,
3743 VALUE_VARCHAR2,
3744 VALUE_NUMBER,
3745 VALUE_DATE,
3746 CREATED_BY,
3747 CREATION_DATE,
3748 LAST_UPDATED_BY,
3749 LAST_UPDATE_DATE,
3750 LAST_UPDATE_LOGIN,
3751 START_DATE_ACTIVE,
3752 END_DATE_ACTIVE
3753 ) values (
3754 p_custom_appl_id,
3755 p_custom_code,
3756 p_region_appl_id,
3757 p_region_code,
3758 p_attr_appl_id,
3759 p_attr_code,
3760 p_sequence_number,
3761 p_operation,
3762 l_value_varchar2,
3763 l_value_number,
3764 l_value_date,
3765 l_created_by,
3766 l_creation_date,
3767 l_last_updated_by,
3768 l_last_update_date,
3769 l_last_update_login,
3770 p_start_date_active,
3771 l_end_date_active);
3772
3773 -- /** commit the insert **/
3774 commit;
3775
3776 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
3777 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_CREATED');
3778 FND_MESSAGE.SET_TOKEN('OBJECT', 'AK_LC_CRITERIA',TRUE);
3779 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3780 ' ' || p_custom_code || ' ' ||
3781 to_char(p_region_appl_id) || ' ' ||
3782 p_region_code || ' ' ||
3783 to_char(p_attr_appl_id) || ' ' ||
3784 p_attr_code || ' ' || p_sequence_number);
3785 FND_MSG_PUB.Add;
3786 end if;
3787
3788 p_return_status := FND_API.G_RET_STS_SUCCESS;
3789
3790 FND_MSG_PUB.Count_And_Get (
3791 p_count => p_msg_count,
3792 p_data => p_msg_data);
3793
3794 EXCEPTION
3795 WHEN VALUE_ERROR THEN
3796 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3797 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_VALUE_ERROR');
3798 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3799 ' ' || p_custom_code || ' ' ||
3800 to_char(p_region_appl_id) || ' ' ||
3801 p_region_code || ' ' ||
3802 to_char(p_attr_appl_id) || ' ' ||
3803 p_attr_code || ' ' || p_sequence_number);
3804 FND_MSG_PUB.Add;
3805 end if;
3806 p_return_status := FND_API.G_RET_STS_ERROR;
3807 rollback to start_create_criteria;
3808 FND_MSG_PUB.Count_And_Get (
3809 p_count => p_msg_count,
3810 p_data => p_msg_data);
3811 WHEN FND_API.G_EXC_ERROR THEN
3812 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3813 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_NOT_CREATED');
3814 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_custom_appl_id) ||
3815 ' ' || p_custom_code || ' ' ||
3816 to_char(p_region_appl_id) || ' ' ||
3817 p_region_code || ' ' ||
3818 to_char(p_attr_appl_id) || ' ' ||
3819 p_attr_code || ' ' || p_sequence_number);
3820 FND_MSG_PUB.Add;
3821 end if;
3822 p_return_status := FND_API.G_RET_STS_ERROR;
3823 rollback to start_create_criteria;
3824 FND_MSG_PUB.Count_And_Get (
3825 p_count => p_msg_count,
3826 p_data => p_msg_data);
3827 WHEN OTHERS THEN
3828 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3829 rollback to start_create_criteria;
3830 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3831 SUBSTR (SQLERRM, 1, 240) );
3832 FND_MSG_PUB.Add;
3833 FND_MSG_PUB.Count_And_Get (
3834 p_count => p_msg_count,
3835 p_data => p_msg_data);
3836 end CREATE_CRITERIA;
3837
3838 --=======================================================
3839 -- Function CUSTOM_EXISTS
3840 --
3841 -- Usage Private API for checking for the existence of
3842 -- a region graph with the given key values. This
3843 -- API should only be called by other APIs that are
3844 -- owned by the Core Modules Team (AK).
3845 --
3846 -- Desc This API check to see if a region graph record
3847 -- exists with the given key values.
3848 --
3849 -- Results The API returns the standard p_return_status parameter
3850 -- indicating one of the standard return statuses :
3851 -- * Unexpected error
3852 -- * Error
3853 -- * Success
3854 -- This function will return TRUE if such an object
3855 -- attribute exists, or FALSE otherwise.
3856 -- Parameters Region Graph key columns
3857 --
3858 -- Version Initial version number = 1.0
3859 -- History Current version number = 1.0
3860 --=======================================================
3861 function CUSTOM_EXISTS (
3862 p_api_version_number IN NUMBER,
3863 p_return_status OUT NOCOPY VARCHAR2,
3864 p_custom_appl_id IN NUMBER,
3865 p_custom_code IN VARCHAR2,
3866 p_region_application_id IN NUMBER,
3867 p_region_code IN VARCHAR2
3868 ) return BOOLEAN is
3869 cursor l_check_csr is
3870 select 1
3871 from AK_CUSTOMIZATIONS
3872 where region_application_id = p_region_application_id
3873 and region_code = p_region_code
3874 and customization_application_id = p_custom_appl_id
3875 and customization_code = p_custom_code;
3876 l_api_version_number CONSTANT number := 1.0;
3877 l_api_name CONSTANT varchar2(30) := 'Custom_Exists';
3878 l_dummy number;
3879 begin
3880 IF NOT FND_API.Compatible_API_Call (
3881 l_api_version_number, p_api_version_number, l_api_name,
3882 G_PKG_NAME) then
3883 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3884 return FALSE;
3885 END IF;
3886
3887 open l_check_csr;
3888 fetch l_check_csr into l_dummy;
3889 if (l_check_csr%notfound) then
3890 close l_check_csr;
3891 p_return_status := FND_API.G_RET_STS_SUCCESS;
3892 return FALSE;
3893 else
3894 close l_check_csr;
3895 p_return_status := FND_API.G_RET_STS_SUCCESS;
3896 return TRUE;
3897 end if;
3898
3899 EXCEPTION
3900 WHEN FND_API.G_EXC_ERROR THEN
3901 p_return_status := FND_API.G_RET_STS_ERROR;
3902 return FALSE;
3903 WHEN OTHERS THEN
3904 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3905 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3906 SUBSTR (SQLERRM, 1, 240) );
3907 FND_MSG_PUB.Add;
3908 return FALSE;
3909
3910 end CUSTOM_EXISTS;
3911
3912 --=======================================================
3913 -- Function CUST_REGION_EXISTS
3914 --
3915 -- Usage Private API for checking for the existence of
3916 -- a region graph with the given key values. This
3917 -- API should only be called by other APIs that are
3918 -- owned by the Core Modules Team (AK).
3919 --
3920 -- Desc This API check to see if a region graph record
3921 -- exists with the given key values.
3922 --
3923 -- Results The API returns the standard p_return_status parameter
3924 -- indicating one of the standard return statuses :
3925 -- * Unexpected error
3926 -- * Error
3927 -- * Success
3928 -- This function will return TRUE if such an object
3929 -- attribute exists, or FALSE otherwise.
3930 -- Parameters Region Graph key columns
3931 --
3932 -- Version Initial version number = 1.0
3933 -- History Current version number = 1.0
3934 --=======================================================
3935 function CUST_REGION_EXISTS (
3936 p_api_version_number IN NUMBER,
3937 p_return_status OUT NOCOPY VARCHAR2,
3938 p_custom_appl_id IN NUMBER,
3939 p_custom_code IN VARCHAR2,
3940 p_region_application_id IN NUMBER,
3941 p_region_code IN VARCHAR2,
3942 p_property_name IN VARCHAR2
3943 ) return BOOLEAN is
3944 cursor l_check_csr is
3945 select 1
3946 from AK_CUSTOM_REGIONS
3947 where region_application_id = p_region_application_id
3948 and region_code = p_region_code
3949 and customization_application_id = p_custom_appl_id
3950 and customization_code = p_custom_code
3951 and property_name = p_property_name;
3952 l_api_version_number CONSTANT number := 1.0;
3953 l_api_name CONSTANT varchar2(30) := 'Cust_Region_Exists';
3954 l_dummy number;
3955 begin
3956 IF NOT FND_API.Compatible_API_Call (
3957 l_api_version_number, p_api_version_number, l_api_name,
3958 G_PKG_NAME) then
3959 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3960 return FALSE;
3961 END IF;
3962
3963 open l_check_csr;
3964 fetch l_check_csr into l_dummy;
3965 if (l_check_csr%notfound) then
3966 close l_check_csr;
3967 p_return_status := FND_API.G_RET_STS_SUCCESS;
3968 return FALSE;
3969 else
3970 close l_check_csr;
3971 p_return_status := FND_API.G_RET_STS_SUCCESS;
3972 return TRUE;
3973 end if;
3974
3975 EXCEPTION
3976 WHEN FND_API.G_EXC_ERROR THEN
3977 p_return_status := FND_API.G_RET_STS_ERROR;
3978 return FALSE;
3979 WHEN OTHERS THEN
3980 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3981 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
3982 SUBSTR (SQLERRM, 1, 240) );
3983 FND_MSG_PUB.Add;
3984 return FALSE;
3985
3986 end CUST_REGION_EXISTS;
3987
3988 --=======================================================
3989 -- Function CUST_REG_ITEM_EXISTS
3990 --
3991 -- Usage Private API for checking for the existence of
3992 -- a region graph with the given key values. This
3993 -- API should only be called by other APIs that are
3994 -- owned by the Core Modules Team (AK).
3995 --
3996 -- Desc This API check to see if a region graph record
3997 -- exists with the given key values.
3998 --
3999 -- Results The API returns the standard p_return_status parameter
4000 -- indicating one of the standard return statuses :
4001 -- * Unexpected error
4002 -- * Error
4003 -- * Success
4004 -- This function will return TRUE if such an object
4005 -- attribute exists, or FALSE otherwise.
4006 -- Parameters Region Graph key columns
4007 --
4008 -- Version Initial version number = 1.0
4009 -- History Current version number = 1.0
4010 --=======================================================
4011 function CUST_REG_ITEM_EXISTS (
4012 p_api_version_number IN NUMBER,
4013 p_return_status OUT NOCOPY VARCHAR2,
4014 p_custom_appl_id IN NUMBER,
4015 p_custom_code IN VARCHAR2,
4016 p_region_application_id IN NUMBER,
4017 p_region_code IN VARCHAR2,
4018 p_attribute_appl_id IN NUMBER,
4019 p_attribute_code IN VARCHAR2,
4020 p_property_name IN VARCHAR2
4021 ) return BOOLEAN is
4022 cursor l_check_csr is
4023 select 1
4024 from AK_CUSTOM_REGION_ITEMS
4025 where region_application_id = p_region_application_id
4026 and region_code = p_region_code
4027 and customization_application_id = p_custom_appl_id
4028 and customization_code = p_custom_code
4029 and attribute_application_id = p_attribute_appl_id
4030 and attribute_code = p_attribute_code
4031 and property_name = p_property_name;
4032 l_api_version_number CONSTANT number := 1.0;
4033 l_api_name CONSTANT varchar2(30) := 'Cust_Reg_Item_Exists';
4034 l_dummy number;
4035 begin
4036 IF NOT FND_API.Compatible_API_Call (
4037 l_api_version_number, p_api_version_number, l_api_name,
4038 G_PKG_NAME) then
4039 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4040 return FALSE;
4041 END IF;
4042
4043 open l_check_csr;
4044 fetch l_check_csr into l_dummy;
4045 if (l_check_csr%notfound) then
4046 close l_check_csr;
4047 p_return_status := FND_API.G_RET_STS_SUCCESS;
4048 return FALSE;
4049 else
4050 close l_check_csr;
4051 p_return_status := FND_API.G_RET_STS_SUCCESS;
4052 return TRUE;
4053 end if;
4054
4055 EXCEPTION
4056 WHEN FND_API.G_EXC_ERROR THEN
4057 p_return_status := FND_API.G_RET_STS_ERROR;
4058 return FALSE;
4059 WHEN OTHERS THEN
4060 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4061 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4062 SUBSTR (SQLERRM, 1, 240) );
4063 FND_MSG_PUB.Add;
4064 return FALSE;
4065
4066 end CUST_REG_ITEM_EXISTS;
4067
4068 --=======================================================
4069 -- Function CRITERIA_EXISTS
4070 --
4071 -- Usage Private API for checking for the existence of
4072 -- a region graph with the given key values. This
4073 -- API should only be called by other APIs that are
4074 -- owned by the Core Modules Team (AK).
4075 --
4076 -- Desc This API check to see if a region graph record
4077 -- exists with the given key values.
4078 --
4079 -- Results The API returns the standard p_return_status parameter
4080 -- indicating one of the standard return statuses :
4081 -- * Unexpected error
4082 -- * Error
4083 -- * Success
4084 -- This function will return TRUE if such an object
4085 -- attribute exists, or FALSE otherwise.
4086 -- Parameters Region Graph key columns
4087 --
4088 -- Version Initial version number = 1.0
4089 -- History Current version number = 1.0
4090 --=======================================================
4091 function CRITERIA_EXISTS (
4092 p_api_version_number IN NUMBER,
4093 p_return_status OUT NOCOPY VARCHAR2,
4094 p_custom_appl_id IN NUMBER,
4095 p_custom_code IN VARCHAR2,
4096 p_region_application_id IN NUMBER,
4097 p_region_code IN VARCHAR2,
4098 p_attribute_appl_id IN NUMBER,
4099 p_attribute_code IN VARCHAR2,
4100 p_sequence_number IN NUMBER
4101 ) return BOOLEAN is
4102 cursor l_check_csr is
4103 select 1
4104 from AK_CRITERIA
4105 where region_application_id = p_region_application_id
4106 and region_code = p_region_code
4107 and customization_application_id = p_custom_appl_id
4108 and customization_code = p_custom_code
4109 and attribute_application_id = p_attribute_appl_id
4110 and attribute_code = p_attribute_code
4111 and sequence_number = p_sequence_number;
4112 l_api_version_number CONSTANT number := 1.0;
4113 l_api_name CONSTANT varchar2(30) := 'Criteria_Exists';
4114 l_dummy number;
4115 begin
4116 IF NOT FND_API.Compatible_API_Call (
4117 l_api_version_number, p_api_version_number, l_api_name,
4118 G_PKG_NAME) then
4119 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4120 return FALSE;
4121 END IF;
4122
4123 open l_check_csr;
4124 fetch l_check_csr into l_dummy;
4125 if (l_check_csr%notfound) then
4126 close l_check_csr;
4127 p_return_status := FND_API.G_RET_STS_SUCCESS;
4128 return FALSE;
4129 else
4130 close l_check_csr;
4131 p_return_status := FND_API.G_RET_STS_SUCCESS;
4132 return TRUE;
4133 end if;
4134
4135 EXCEPTION
4136 WHEN FND_API.G_EXC_ERROR THEN
4137 p_return_status := FND_API.G_RET_STS_ERROR;
4138 return FALSE;
4139 WHEN OTHERS THEN
4140 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4141 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4142 SUBSTR (SQLERRM, 1, 240) );
4143 FND_MSG_PUB.Add;
4144 return FALSE;
4145
4146 end CRITERIA_EXISTS;
4147
4148 --=======================================================
4149 -- Procedure UPDATE_CUSTOM
4150 --
4151 -- Usage Private API for updating a region graph.
4152 -- This API should only be called by other APIs
4153 -- that are owned by the Core Modules Team (AK).
4154 --
4155 -- Desc This API updates a region graph using the given info
4156 --
4157 -- Results The API returns the standard p_return_status parameter
4158 -- indicating one of the standard return statuses :
4159 -- * Unexpected error
4160 -- * Error
4161 -- * Success
4162 -- Parameters Region Graph columns
4163 -- p_loader_timestamp : IN optional
4164 -- If a timestamp is passed, the API will update the
4165 -- record using this timestamp. Only the upload API
4166 -- should call with this parameter loaded.
4167 --
4168 -- Version Initial version number = 1.0
4169 -- History Current version number = 1.0
4170 --=======================================================
4171 procedure UPDATE_CUSTOM (
4172 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4173 p_api_version_number IN NUMBER,
4174 p_init_msg_tbl IN BOOLEAN := FALSE,
4175 p_msg_count OUT NOCOPY NUMBER,
4176 p_msg_data OUT NOCOPY VARCHAR2,
4177 p_return_status OUT NOCOPY VARCHAR2,
4178 p_custom_appl_id IN NUMBER,
4179 p_custom_code IN VARCHAR2,
4180 p_region_application_id IN NUMBER,
4181 p_region_code IN VARCHAR2,
4182 p_verticalization_id IN VARCHAR2 := FND_API.G_MISS_CHAR,
4183 p_localization_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4184 p_org_id IN NUMBER := FND_API.G_MISS_NUM,
4185 p_site_id IN NUMBER := FND_API.G_MISS_NUM,
4186 p_responsibility_id IN NUMBER := FND_API.G_MISS_NUM,
4187 p_web_user_id IN NUMBER := FND_API.G_MISS_NUM,
4188 p_default_customization_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
4189 p_customization_level_id IN NUMBER := FND_API.G_MISS_NUM,
4190 p_developer_mode IN VARCHAR2 := FND_API.G_MISS_CHAR,
4191 p_reference_path IN VARCHAR2 := FND_API.G_MISS_CHAR,
4192 p_function_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4193 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
4194 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
4195 p_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
4196 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
4197 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4198 p_creation_date IN DATE := FND_API.G_MISS_DATE,
4199 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4200 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4201 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4202 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4203 p_pass IN NUMBER,
4204 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4205 ) is
4206 cursor l_get_row_csr is
4207 select *
4208 from AK_CUSTOMIZATIONS
4209 where CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4210 and CUSTOMIZATION_CODE = p_custom_code
4211 and REGION_APPLICATION_ID = p_region_application_id
4212 and REGION_CODE = p_region_code
4213 for update of VERTICALIZATION_ID;
4214 cursor l_get_tl_row_csr (lang_parm varchar2) is
4215 select *
4216 from AK_CUSTOMIZATIONS_TL
4217 where CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4218 and CUSTOMIZATION_CODE = p_custom_code
4219 and REGION_APPLICATION_ID = p_region_application_id
4220 and REGION_CODE = p_region_code
4221 and LANGUAGE = lang_parm
4222 for update of name;
4223 l_api_version_number CONSTANT number := 1.0;
4224 l_api_name CONSTANT varchar2(30) := 'Update_Custom';
4225 l_created_by number;
4226 l_creation_date date;
4227 l_custom_rec ak_customizations%ROWTYPE;
4228 l_custom_tl_rec ak_customizations_tl%ROWTYPE;
4229 l_error boolean;
4230 l_lang varchar2(30);
4231 l_last_update_date date;
4232 l_last_update_login number;
4233 l_last_updated_by number;
4234 l_return_status varchar2(1);
4235 l_submit varchar2(1) := 'N';
4236 l_encrypt varchar2(1) := 'N';
4237 begin
4238 IF NOT FND_API.Compatible_API_Call (
4239 l_api_version_number, p_api_version_number, l_api_name,
4240 G_PKG_NAME) then
4241 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4242 return;
4243 END IF;
4244
4245 -- Initialize the message table if requested.
4246
4247 if p_init_msg_tbl then
4248 FND_MSG_PUB.initialize;
4249 end if;
4250
4251 savepoint start_update_custom;
4252
4253 select userenv('LANG') into l_lang
4254 from dual;
4255
4256 --** retrieve ak_customizations row if it exists **
4257 open l_get_row_csr;
4258 fetch l_get_row_csr into l_custom_rec;
4259 if (l_get_row_csr%notfound) then
4260 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4261 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_DOES_NOT_EXIST');
4262 FND_MSG_PUB.Add;
4263 end if;
4264 --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
4265 close l_get_row_csr;
4266 raise FND_API.G_EXC_ERROR;
4267 end if;
4268 close l_get_row_csr;
4269
4270 --** retrieve ak_customizations_tl row if it exists **
4271 open l_get_tl_row_csr(l_lang);
4272 fetch l_get_tl_row_csr into l_custom_tl_rec;
4273 if (l_get_tl_row_csr%notfound) then
4274 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4275 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_NOT_EXIST');
4276 FND_MSG_PUB.Add;
4277 end if;
4278 -- dbms_output.put_line(l_api_name || 'Error - TL Row does not exist');
4279 close l_get_tl_row_csr;
4280 raise FND_API.G_EXC_ERROR;
4281 end if;
4282 close l_get_tl_row_csr;
4283
4284 --
4285 -- validate table columns passed in
4286 --
4287 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4288 if not VALIDATE_CUSTOM (
4289 p_validation_level => p_validation_level,
4290 p_api_version_number => 1.0,
4291 p_return_status => l_return_status,
4292 p_region_application_id => p_region_application_id,
4293 p_region_code => p_region_code,
4294 p_custom_application_id => p_custom_appl_id,
4295 p_custom_code => p_custom_code,
4296 p_verticalization_id => p_verticalization_id,
4297 p_localization_code => p_localization_code,
4298 p_org_id => p_org_id,
4299 p_site_id => p_site_id,
4300 p_responsibility_id => p_responsibility_id,
4301 p_web_user_id => p_web_user_id,
4302 p_default_custom_flag => p_default_customization_flag,
4303 p_customization_level_id => p_customization_level_id,
4304 p_developer_mode => p_developer_mode,
4305 p_reference_path => p_reference_path,
4306 p_function_name => p_function_name,
4307 p_start_date_active => p_start_date_active,
4308 p_end_date_active => p_end_date_active,
4309 p_name => p_name,
4310 p_description => p_description,
4311 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
4312 p_pass => p_pass
4313 ) then
4314 --dbms_output.put_line(l_api_name || ' validation failed');
4315 -- Do not raise an error if it's the first pass
4316 if (p_pass = 1) then
4317 p_copy_redo_flag := TRUE;
4318 else
4319 raise FND_API.G_EXC_ERROR;
4320 end if;
4321 end if;
4322 end if;
4323
4324 --** Load record to be updated to the database **
4325 --** - first load nullable columns **
4326
4327 if (p_verticalization_id <> FND_API.G_MISS_CHAR) or
4328 (p_verticalization_id is null) then
4329 l_custom_rec.verticalization_id := p_verticalization_id;
4330 end if;
4331
4332 if (p_localization_code <> FND_API.G_MISS_CHAR) or
4333 (p_localization_code is null) then
4334 l_custom_rec.localization_code := p_localization_code;
4335 end if;
4336
4337 if (p_org_id <> FND_API.G_MISS_NUM) or
4338 (p_org_id is null) then
4339 l_custom_rec.org_id := p_org_id;
4340 end if;
4341
4342 if (p_site_id <> FND_API.G_MISS_NUM) or
4343 (p_site_id is null) then
4344 l_custom_rec.site_id := p_site_id;
4345 end if;
4346
4347 if (p_responsibility_id <> FND_API.G_MISS_NUM) or
4348 (p_responsibility_id is null) then
4349 l_custom_rec.responsibility_id := p_responsibility_id;
4350 end if;
4351
4352 if (p_web_user_id <> FND_API.G_MISS_NUM) or
4353 (p_web_user_id is null) then
4354 l_custom_rec.web_user_id := p_web_user_id;
4355 end if;
4356
4357 if (p_default_customization_flag <> FND_API.G_MISS_CHAR) or
4358 (p_default_customization_flag is null) then
4359 l_custom_rec.default_customization_flag := p_default_customization_flag;
4360 end if;
4361
4362 if (p_end_date_active <> FND_API.G_MISS_DATE) or
4363 (p_end_date_active is null) then
4364 l_custom_rec.end_date_active := p_end_date_active;
4365 end if;
4366
4367 if (p_description <> FND_API.G_MISS_CHAR) or
4368 (p_description is null) then
4369 l_custom_tl_rec.description := p_description;
4370 end if;
4371
4372 if (p_developer_mode <> FND_API.G_MISS_CHAR) or
4373 (p_developer_mode is null) then
4374 l_custom_rec.developer_mode := p_developer_mode;
4375 end if;
4376
4377 --** - next, load non-null columns **
4378
4379 if (p_customization_level_id <> FND_API.G_MISS_NUM) then
4380 l_custom_rec.customization_level_id := p_customization_level_id;
4381 end if;
4382
4383 if (p_start_date_Active <> FND_API.G_MISS_DATE) then
4384 l_custom_rec.start_date_Active := p_start_date_Active;
4385 end if;
4386
4387 if (p_name <> FND_API.G_MISS_CHAR) then
4388 l_custom_tl_rec.name := p_name;
4389 end if;
4390
4391 if (p_created_by <> FND_API.G_MISS_NUM) then
4392 l_created_by := p_created_by;
4393 end if;
4394
4395 if (p_creation_date <> FND_API.G_MISS_DATE) then
4396 l_creation_date := p_creation_date;
4397 end if;
4398
4399 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4400 l_last_updated_by := p_last_updated_by;
4401 end if;
4402
4403 if (p_last_update_date <> FND_API.G_MISS_DATE) then
4404 l_last_update_date := p_last_update_date;
4405 end if;
4406
4407 if (p_last_update_login <> FND_API.G_MISS_NUM) then
4408 l_last_update_login := p_last_update_login;
4409 end if;
4410
4411 -- THIS UPDATES NO MATTER WHAT - CALLING IS_UPDATEABLE BECAUSE STILL
4412 -- NECESSARY FOR PRE-12 CODE
4413 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
4414 p_loader_timestamp => p_loader_timestamp,
4415 p_created_by => l_created_by,
4416 p_creation_date => l_creation_date,
4417 p_last_updated_by => l_last_updated_by,
4418 p_db_last_updated_by => l_custom_rec.last_updated_by,
4419 p_last_update_date => l_last_update_date,
4420 p_db_last_update_date => l_custom_rec.last_update_date,
4421 p_last_update_login => l_last_update_login,
4422 p_create_or_update => 'UPDATE') then
4423 null;
4424 end if;
4425
4426 -- added deletes for bug 2394151
4427 delete AK_CUSTOM_REGIONS
4428 where REGION_APPLICATION_ID = p_region_application_id
4429 and REGION_CODE = p_region_code
4430 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4431 and CUSTOMIZATION_CODE = p_custom_code;
4432
4433 delete AK_CUSTOM_REGION_ITEMS
4434 where REGION_APPLICATION_ID = p_region_application_id
4435 and REGION_CODE = p_region_code
4436 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4437 and CUSTOMIZATION_CODE = p_custom_code;
4438
4439 update AK_CUSTOMIZATIONS set
4440 VERTICALIZATION_ID = l_custom_rec.verticalization_id,
4441 LOCALIZATION_CODE = l_custom_rec.localization_code,
4442 ORG_ID = l_custom_rec.org_id,
4443 SITE_ID = l_custom_rec.site_id,
4444 RESPONSIBILITY_ID = l_custom_rec.responsibility_id,
4445 WEB_USER_ID = l_custom_rec.web_user_id,
4446 DEFAULT_CUSTOMIZATION_FLAG = l_custom_rec.default_customization_flag,
4447 CUSTOMIZATION_LEVEL_ID = l_custom_rec.customization_level_id,
4448 DEVELOPER_MODE = l_custom_rec.developer_mode,
4449 START_DATE_ACTIVE = l_custom_Rec.start_date_active,
4450 END_DATE_ACTIVE = l_custom_rec.end_date_active,
4451 LAST_UPDATE_DATE = l_last_update_date,
4452 LAST_UPDATED_BY = l_last_updated_by,
4453 LAST_UPDATE_LOGIN = l_last_update_login
4454 where REGION_APPLICATION_ID = p_region_application_id
4455 and REGION_CODE = p_region_code
4456 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4457 and CUSTOMIZATION_CODE = p_custom_code;
4458 if (sql%notfound) then
4459 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4460 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_UPDATE_FAILED');
4461 FND_MSG_PUB.Add;
4462 end if;
4463 -- dbms_output.put_line(l_api_name || 'Row does not exist during update');
4464 raise FND_API.G_EXC_ERROR;
4465 end if;
4466
4467 delete AK_CUSTOM_REGIONS_TL
4468 where REGION_APPLICATION_ID = p_region_application_id
4469 and REGION_CODE = p_region_code
4470 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4471 and CUSTOMIZATION_CODE = p_custom_code;
4472
4473 delete AK_CUSTOM_REGION_ITEMS_TL
4474 where REGION_APPLICATION_ID = p_region_application_id
4475 and REGION_CODE = p_region_code
4476 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4477 and CUSTOMIZATION_CODE = p_custom_code;
4478
4479 update AK_CUSTOMIZATIONS_TL set
4480 NAME = l_custom_tl_rec.name,
4481 DESCRIPTION = l_custom_tl_rec.description,
4482 LAST_UPDATED_BY = l_last_updated_by,
4483 LAST_UPDATE_DATE = l_last_update_date,
4484 LAST_UPDATE_LOGIN = l_last_update_login,
4485 SOURCE_LANG = l_lang
4486 where REGION_APPLICATION_ID = p_region_application_id
4487 and REGION_CODE = p_region_code
4488 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4489 and CUSTOMIZATION_CODE = p_custom_code
4490 and l_lang in (LANGUAGE, SOURCE_LANG);
4491 if (sql%notfound) then
4492 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4493 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_UPDATE_FAILED');
4494 FND_MSG_PUB.Add;
4495 end if;
4496 --dbms_output.put_line(l_api_name || 'TL Row does not exist during update');
4497 raise FND_API.G_EXC_ERROR;
4498 end if;
4499
4500 -- /** commit the update **/
4501 -- commit;
4502
4503 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
4504 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_UPDATED');
4505 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
4506 ' ' || p_region_code ||
4507 ' ' || to_char(p_custom_appl_id) ||
4508 ' ' || p_custom_code );
4509 FND_MSG_PUB.Add;
4510 end if;
4511
4512 p_return_status := FND_API.G_RET_STS_SUCCESS;
4513
4514 FND_MSG_PUB.Count_And_Get (
4515 p_count => p_msg_count,
4516 p_data => p_msg_data);
4517
4518 EXCEPTION
4519 WHEN VALUE_ERROR THEN
4520 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4521 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_VALUE_ERROR');
4522 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
4523 ' ' || p_region_code ||
4524 ' ' || to_char(p_custom_appl_id) ||
4525 ' ' || p_custom_code );
4526 FND_MSG_PUB.Add;
4527 end if;
4528 rollback to start_update_custom;
4529 p_return_status := FND_API.G_RET_STS_ERROR;
4530 FND_MSG_PUB.Count_And_Get (
4531 p_count => p_msg_count,
4532 p_data => p_msg_data);
4533 WHEN FND_API.G_EXC_ERROR THEN
4534 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4535 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_NOT_UPDATED');
4536 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
4537 ' ' || p_region_code ||
4538 ' ' || to_char(p_custom_appl_id) ||
4539 ' ' || p_custom_code );
4540 FND_MSG_PUB.Add;
4541 end if;
4542 p_return_status := FND_API.G_RET_STS_ERROR;
4543 rollback to start_update_custom;
4544 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4545 SUBSTR (SQLERRM, 1, 240) );
4546 FND_MSG_PUB.Add;
4547 FND_MSG_PUB.Count_And_Get (
4548 p_count => p_msg_count,
4549 p_data => p_msg_data);
4550 end UPDATE_CUSTOM;
4551
4552 --=======================================================
4553 -- Procedure UPDATE_CUST_REGION
4554 --
4555 -- Usage Private API for updating a region graph.
4556 -- This API should only be called by other APIs
4557 -- that are owned by the Core Modules Team (AK).
4558 --
4559 -- Desc This API updates a region graph using the given info
4560 --
4561 -- Results The API returns the standard p_return_status parameter
4562 -- indicating one of the standard return statuses :
4563 -- * Unexpected error
4564 -- * Error
4565 -- * Success
4566 -- Parameters Region Graph columns
4567 -- p_loader_timestamp : IN optional
4568 -- If a timestamp is passed, the API will update the
4569 -- record using this timestamp. Only the upload API
4570 -- should call with this parameter loaded.
4571 --
4572 -- Version Initial version number = 1.0
4573 -- History Current version number = 1.0
4574 --=======================================================
4575 procedure UPDATE_CUST_REGION (
4576 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4577 p_api_version_number IN NUMBER,
4578 p_init_msg_tbl IN BOOLEAN := FALSE,
4579 p_msg_count OUT NOCOPY NUMBER,
4580 p_msg_data OUT NOCOPY VARCHAR2,
4581 p_return_status OUT NOCOPY VARCHAR2,
4582 p_custom_appl_id IN NUMBER,
4583 p_custom_code IN VARCHAR2,
4584 p_region_application_id IN NUMBER,
4585 p_region_code IN VARCHAR2,
4586 p_property_name IN VARCHAR2,
4587 p_property_varchar2_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
4588 p_property_number_value IN NUMBER := FND_API.G_MISS_NUM,
4589 p_criteria_join_condition IN VARCHAR2 := FND_API.G_MISS_CHAR,
4590 p_property_varchar2_value_tl IN VARCHAR2 := FND_API.G_MISS_CHAR,
4591 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4592 p_creation_date IN DATE := FND_API.G_MISS_DATE,
4593 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4594 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4595 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4596 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4597 p_pass IN NUMBER,
4598 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4599 ) is
4600 cursor l_get_row_csr is
4601 select *
4602 from AK_CUSTOM_REGIONS
4603 where CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4604 and CUSTOMIZATION_CODE = p_custom_code
4605 and REGION_APPLICATION_ID = p_region_application_id
4606 and REGION_CODE = p_region_code
4607 and PROPERTY_NAME = p_property_name
4608 for update of PROPERTY_VARCHAR2_VALUE;
4609 cursor l_get_tl_row_csr (lang_parm varchar2) is
4610 select *
4611 from AK_CUSTOM_REGIONS_TL
4612 where CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4613 and CUSTOMIZATION_CODE = p_custom_code
4614 and REGION_APPLICATION_ID = p_region_application_id
4615 and REGION_CODE = p_region_code
4616 and PROPERTY_NAME = p_property_name
4617 and LANGUAGE = lang_parm
4618 for update of PROPERTY_VARCHAR2_VALUE;
4619 l_api_version_number CONSTANT number := 1.0;
4620 l_api_name CONSTANT varchar2(30) := 'Update_Custom';
4621 l_created_by number;
4622 l_creation_date date;
4623 l_cust_region_rec ak_custom_regions%ROWTYPE;
4624 l_cust_region_tl_rec ak_custom_regions_tl%ROWTYPE;
4625 l_error boolean;
4626 l_lang varchar2(30);
4627 l_last_update_date date;
4628 l_last_update_login number;
4629 l_last_updated_by number;
4630 l_return_status varchar2(1);
4631 l_submit varchar2(1) := 'N';
4632 l_encrypt varchar2(1) := 'N';
4633 begin
4634 IF NOT FND_API.Compatible_API_Call (
4635 l_api_version_number, p_api_version_number, l_api_name,
4636 G_PKG_NAME) then
4637 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4638 return;
4639 END IF;
4640
4641 -- Initialize the message table if requested.
4642
4643 if p_init_msg_tbl then
4644 FND_MSG_PUB.initialize;
4645 end if;
4646
4647 savepoint start_update_cust_region;
4648
4649 select userenv('LANG') into l_lang
4650 from dual;
4651
4652 --** retrieve ak_custom_regions row if it exists **
4653 open l_get_row_csr;
4654 fetch l_get_row_csr into l_cust_region_rec;
4655 if (l_get_row_csr%notfound) then
4656 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4657 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_DOES_NOT_EXIST');
4658 FND_MSG_PUB.Add;
4659 end if;
4660 --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
4661 close l_get_row_csr;
4662 raise FND_API.G_EXC_ERROR;
4663 end if;
4664 close l_get_row_csr;
4665
4666 --** retrieve ak_custom_regions_tl row if it exists **
4667 open l_get_tl_row_csr(l_lang);
4668 fetch l_get_tl_row_csr into l_cust_region_tl_rec;
4669 if (l_get_tl_row_csr%notfound) then
4670 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4671 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_NOT_EXIST');
4672 FND_MSG_PUB.Add;
4673 end if;
4674 -- dbms_output.put_line(l_api_name || 'Error - TL Row does not exist');
4675 close l_get_tl_row_csr;
4676 raise FND_API.G_EXC_ERROR;
4677 end if;
4678 close l_get_tl_row_csr;
4679
4680 --
4681 -- validate table columns passed in
4682 --
4683 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
4684 if not VALIDATE_CUST_REGION (
4685 p_validation_level => p_validation_level,
4686 p_api_version_number => 1.0,
4687 p_return_status => l_return_status,
4688 p_region_application_id => p_region_application_id,
4689 p_region_code => p_region_code,
4690 p_custom_application_id => p_custom_appl_id,
4691 p_custom_code => p_custom_code,
4692 p_property_name => p_property_name,
4693 p_property_varchar2_value => p_property_varchar2_value,
4694 p_property_number_value => p_property_number_value,
4695 p_criteria_join_condition => p_criteria_join_condition,
4696 p_property_varchar2_value_tl => p_property_varchar2_value_tl,
4697 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
4698 p_pass => p_pass
4699 ) then
4700 --dbms_output.put_line(l_api_name || ' validation failed');
4701 -- Do not raise an error if it's the first pass
4702 if (p_pass = 1) then
4703 p_copy_redo_flag := TRUE;
4704 else
4705 raise FND_API.G_EXC_ERROR;
4706 end if;
4707 end if;
4708 end if;
4709
4710 --** Load record to be updated to the database **
4711 --** - first load nullable columns **
4712
4713 if (p_property_varchar2_value <> FND_API.G_MISS_CHAR) or
4714 (p_property_varchar2_value is null) then
4715 l_cust_region_rec.property_varchar2_value := p_property_varchar2_value;
4716 end if;
4717
4718 if (p_property_number_value <> FND_API.G_MISS_NUM) or
4719 (p_property_number_value is null) then
4720 l_cust_region_rec.property_number_value := p_property_number_value;
4721 end if;
4722
4723 if (p_criteria_join_condition <> FND_API.G_MISS_CHAR) or
4724 (p_criteria_join_condition is null) then
4725 l_cust_region_rec.criteria_join_condition := p_criteria_join_condition;
4726 end if;
4727
4728 if (p_property_varchar2_value_tl <> FND_API.G_MISS_CHAR) or
4729 (p_property_varchar2_value_tl is null) then
4730 l_cust_region_tl_rec.property_varchar2_value := p_property_varchar2_value;
4731 end if;
4732
4733 if (p_created_by <> FND_API.G_MISS_NUM) then
4734 l_created_by := p_created_by;
4735 end if;
4736
4737 if (p_creation_date <> FND_API.G_MISS_DATE) then
4738 l_creation_date := p_creation_date;
4739 end if;
4740
4741 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
4742 l_last_updated_by := p_last_updated_by;
4743 end if;
4744
4745 if (p_last_update_date <> FND_API.G_MISS_DATE) then
4746 l_last_update_date := p_last_update_date;
4747 end if;
4748
4749 if (p_last_update_login <> FND_API.G_MISS_NUM) then
4750 l_last_update_login := p_last_update_login;
4751 end if;
4752
4753 --** - next, load non-null columns **
4754
4755 -- THIS UPDATES NO MATTER WHAT - CALLING IS_UPDATEABLE BECAUSE STILL
4756 -- NECESSARY FOR PRE-12 CODE
4757 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
4758 p_loader_timestamp => p_loader_timestamp,
4759 p_created_by => l_created_by,
4760 p_creation_date => l_creation_date,
4761 p_last_updated_by => l_last_updated_by,
4762 p_db_last_updated_by => l_cust_region_rec.last_updated_by,
4763 p_last_update_date => l_last_update_date,
4764 p_db_last_update_date => l_cust_region_rec.last_update_date,
4765 p_last_update_login => l_last_update_login,
4766 p_create_or_update => 'UPDATE') then
4767 null;
4768 end if;
4769
4770 update AK_CUSTOM_REGIONS set
4771 PROPERTY_VARCHAR2_VALUE = l_cust_region_rec.property_varchar2_value,
4772 PROPERTY_NUMBER_VALUE = l_cust_region_rec.property_number_value,
4773 CRITERIA_JOIN_CONDITION = l_cust_region_rec.criteria_join_condition,
4774 LAST_UPDATE_DATE = l_last_update_date,
4775 LAST_UPDATED_BY = l_last_updated_by,
4776 LAST_UPDATE_LOGIN = l_last_update_login
4777 where REGION_APPLICATION_ID = p_region_application_id
4778 and REGION_CODE = p_region_code
4779 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4780 and CUSTOMIZATION_CODE = p_custom_code
4781 and PROPERTY_NAME = p_property_name;
4782 if (sql%notfound) then
4783 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4784 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_UPDATE_FAILED');
4785 FND_MSG_PUB.Add;
4786 end if;
4787 -- dbms_output.put_line(l_api_name || 'Row does not exist during update');
4788 raise FND_API.G_EXC_ERROR;
4789 end if;
4790
4791 update AK_CUSTOM_REGIONS_TL set
4792 PROPERTY_VARCHAR2_VALUE = l_cust_region_tl_rec.property_varchar2_value,
4793 LAST_UPDATE_DATE = l_last_update_date,
4794 LAST_UPDATED_BY = l_last_updated_by,
4795 LAST_UPDATE_LOGIN = l_last_update_login,
4796 SOURCE_LANG = l_lang
4797 where REGION_APPLICATION_ID = p_region_application_id
4798 and REGION_CODE = p_region_code
4799 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4800 and CUSTOMIZATION_CODE = p_custom_code
4801 and PROPERTY_NAME = p_property_name
4802 and l_lang in (LANGUAGE, SOURCE_LANG);
4803 if (sql%notfound) then
4804 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4805 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_UPDATE_FAILED');
4806 FND_MSG_PUB.Add;
4807 end if;
4808 --dbms_output.put_line(l_api_name || 'TL Row does not exist during update');
4809 raise FND_API.G_EXC_ERROR;
4810 end if;
4811
4812 -- /** commit the update **/
4813 -- commit;
4814
4815 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
4816 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_UPDATED');
4817 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
4818 ' ' || p_region_code ||
4819 ' ' || to_char(p_custom_appl_id) ||
4820 ' ' || p_custom_code || ' ' ||
4821 p_property_name);
4822 FND_MSG_PUB.Add;
4823 end if;
4824
4825 p_return_status := FND_API.G_RET_STS_SUCCESS;
4826
4827 FND_MSG_PUB.Count_And_Get (
4828 p_count => p_msg_count,
4829 p_data => p_msg_data);
4830
4831 EXCEPTION
4832 WHEN VALUE_ERROR THEN
4833 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4834 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_VALUE_ERROR');
4835 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
4836 ' ' || p_region_code ||
4837 ' ' || to_char(p_custom_appl_id) ||
4838 ' ' || p_custom_code || ' ' ||
4839 p_property_name);
4840 FND_MSG_PUB.Add;
4841 end if;
4842 rollback to start_update_cust_region;
4843 p_return_status := FND_API.G_RET_STS_ERROR;
4844 FND_MSG_PUB.Count_And_Get (
4845 p_count => p_msg_count,
4846 p_data => p_msg_data);
4847 WHEN FND_API.G_EXC_ERROR THEN
4848 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4849 FND_MESSAGE.SET_NAME('AK','AK_CUST_REGION_NOT_UPDATED');
4850 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
4851 ' ' || p_region_code ||
4852 ' ' || to_char(p_custom_appl_id) ||
4853 ' ' || p_custom_code || ' ' ||
4854 p_property_name);
4855 FND_MSG_PUB.Add;
4856 end if;
4857 p_return_status := FND_API.G_RET_STS_ERROR;
4858 rollback to start_update_cust_region;
4859 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
4860 SUBSTR (SQLERRM, 1, 240) );
4861 FND_MSG_PUB.Add;
4862 FND_MSG_PUB.Count_And_Get (
4863 p_count => p_msg_count,
4864 p_data => p_msg_data);
4865 end UPDATE_CUST_REGION;
4866
4867 --=======================================================
4868 -- Procedure UPDATE_CUST_REG_ITEM
4869 --
4870 -- Usage Private API for updating a region graph.
4871 -- This API should only be called by other APIs
4872 -- that are owned by the Core Modules Team (AK).
4873 --
4874 -- Desc This API updates a region graph using the given info
4875 --
4876 -- Results The API returns the standard p_return_status parameter
4877 -- indicating one of the standard return statuses :
4878 -- * Unexpected error
4879 -- * Error
4880 -- * Success
4881 -- Parameters Region Graph columns
4882 -- p_loader_timestamp : IN optional
4883 -- If a timestamp is passed, the API will update the
4884 -- record using this timestamp. Only the upload API
4885 -- should call with this parameter loaded.
4886 --
4887 -- Version Initial version number = 1.0
4888 -- History Current version number = 1.0
4889 --=======================================================
4890 procedure UPDATE_CUST_REG_ITEM (
4891 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4892 p_api_version_number IN NUMBER,
4893 p_init_msg_tbl IN BOOLEAN := FALSE,
4894 p_msg_count OUT NOCOPY NUMBER,
4895 p_msg_data OUT NOCOPY VARCHAR2,
4896 p_return_status OUT NOCOPY VARCHAR2,
4897 p_custom_appl_id IN NUMBER,
4898 p_custom_code IN VARCHAR2,
4899 p_region_application_id IN NUMBER,
4900 p_region_code IN VARCHAR2,
4901 p_attribute_appl_id IN NUMBER,
4902 p_attribute_code IN VARCHAR2,
4903 p_property_name IN VARCHAR2,
4904 p_property_varchar2_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
4905 p_property_number_value IN NUMBER := FND_API.G_MISS_NUM,
4906 p_property_date_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
4907 p_property_varchar2_value_tl IN VARCHAR2 := FND_API.G_MISS_CHAR,
4908 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
4909 p_creation_date IN DATE := FND_API.G_MISS_DATE,
4910 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
4911 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
4912 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
4913 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
4914 p_pass IN NUMBER,
4915 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
4916 ) is
4917 cursor l_get_row_csr is
4918 select *
4919 from AK_CUSTOM_REGION_ITEMS
4920 where CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4921 and CUSTOMIZATION_CODE = p_custom_code
4922 and REGION_APPLICATION_ID = p_region_application_id
4923 and REGION_CODE = p_region_code
4924 and ATTRIBUTE_APPLICATION_ID = p_attribute_appl_id
4925 and ATTRIBUTE_CODE = p_attribute_code
4926 and PROPERTY_NAME = p_property_name
4927 for update of PROPERTY_VARCHAR2_VALUE;
4928 cursor l_get_tl_row_csr (lang_parm varchar2) is
4929 select *
4930 from AK_CUSTOM_REGION_ITEMS_TL
4931 where CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
4932 and CUSTOMIZATION_CODE = p_custom_code
4933 and REGION_APPLICATION_ID = p_region_application_id
4934 and REGION_CODE = p_region_code
4935 and ATTRIBUTE_APPLICATION_ID = p_attribute_appl_id
4936 and ATTRIBUTE_CODE = p_attribute_code
4937 and PROPERTY_NAME = p_property_name
4938 and LANGUAGE = lang_parm
4939 for update of PROPERTY_VARCHAR2_VALUE;
4940 l_api_version_number CONSTANT number := 1.0;
4941 l_api_name CONSTANT varchar2(30) := 'Update_Cust_Reg_Item';
4942 l_created_by number;
4943 l_creation_date date;
4944 l_cust_reg_item_rec ak_custom_region_items%ROWTYPE;
4945 l_cust_reg_item_tl_rec ak_custom_region_items_tl%ROWTYPE;
4946 l_error boolean;
4947 l_lang varchar2(30);
4948 l_last_update_date date;
4949 l_last_update_login number;
4950 l_last_updated_by number;
4951 l_return_status varchar2(1);
4952 l_submit varchar2(1) := 'N';
4953 l_encrypt varchar2(1) := 'N';
4954 begin
4955 IF NOT FND_API.Compatible_API_Call (
4956 l_api_version_number, p_api_version_number, l_api_name,
4957 G_PKG_NAME) then
4958 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4959 return;
4960 END IF;
4961
4962 -- Initialize the message table if requested.
4963
4964 if p_init_msg_tbl then
4965 FND_MSG_PUB.initialize;
4966 end if;
4967
4968 savepoint start_update_cust_reg_item;
4969
4970 select userenv('LANG') into l_lang
4971 from dual;
4972
4973 --** retrieve ak_custom_region_items row if it exists **
4974 open l_get_row_csr;
4975 fetch l_get_row_csr into l_cust_reg_item_rec;
4976 if (l_get_row_csr%notfound) then
4977 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4978 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_DOES_NOT_EXIST');
4979 FND_MSG_PUB.Add;
4980 end if;
4981 --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
4982 close l_get_row_csr;
4983 raise FND_API.G_EXC_ERROR;
4984 end if;
4985 close l_get_row_csr;
4986
4987 --** retrieve ak_custom_region_items_tl row if it exists **
4988 open l_get_tl_row_csr(l_lang);
4989 fetch l_get_tl_row_csr into l_cust_reg_item_tl_rec;
4990 if (l_get_tl_row_csr%notfound) then
4991 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
4992 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_DOES_NOT_EXIST');
4993 FND_MSG_PUB.Add;
4994 end if;
4995 -- dbms_output.put_line(l_api_name || 'Error - TL Row does not exist');
4996 close l_get_tl_row_csr;
4997 raise FND_API.G_EXC_ERROR;
4998 end if;
4999 close l_get_tl_row_csr;
5000
5001 --
5002 -- validate table columns passed in
5003 --
5004 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5005 if not VALIDATE_CUST_REGION_ITEM (
5006 p_validation_level => p_validation_level,
5007 p_api_version_number => 1.0,
5008 p_return_status => l_return_status,
5009 p_region_application_id => p_region_application_id,
5010 p_region_code => p_region_code,
5011 p_custom_application_id => p_custom_appl_id,
5012 p_custom_code => p_custom_code,
5013 p_attr_appl_id => p_attribute_appl_id,
5014 p_attr_code => p_attribute_code,
5015 p_property_name => p_property_name,
5016 p_property_varchar2_value => p_property_varchar2_value,
5017 p_property_number_value => p_property_number_value,
5018 p_property_date_value => p_property_date_value,
5019 p_property_varchar2_value_tl => p_property_varchar2_value_tl,
5020 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
5021 p_pass => p_pass
5022 ) then
5023 --dbms_output.put_line(l_api_name || ' validation failed');
5024 -- Do not raise an error if it's the first pass
5025 if (p_pass = 1) then
5026 p_copy_redo_flag := TRUE;
5027 else
5028 raise FND_API.G_EXC_ERROR;
5029 end if;
5030 end if;
5031 end if;
5032
5033 --** Load record to be updated to the database **
5034 --** - first load nullable columns **
5035
5036 if (p_property_varchar2_value <> FND_API.G_MISS_CHAR) or
5037 (p_property_varchar2_value is null) then
5038 l_cust_reg_item_rec.property_varchar2_value := p_property_varchar2_value;
5039 end if;
5040
5041 if (p_property_number_value <> FND_API.G_MISS_NUM) or
5042 (p_property_number_value is null) then
5043 l_cust_reg_item_rec.property_number_value := p_property_number_value;
5044 end if;
5045
5046 if (p_property_date_value <> FND_API.G_MISS_NUM) or
5047 (p_property_date_value is null) then
5048 l_cust_reg_item_rec.property_date_value := p_property_date_value;
5049 end if;
5050
5051 if (p_property_varchar2_value_tl <> FND_API.G_MISS_CHAR) or
5052 (p_property_varchar2_value_tl is null) then
5053 l_cust_reg_item_tl_rec.property_varchar2_value := p_property_varchar2_value_tl;
5054 end if;
5055
5056 if (p_created_by <> FND_API.G_MISS_NUM) then
5057 l_created_by := p_created_by;
5058 end if;
5059
5060 if (p_creation_date <> FND_API.G_MISS_DATE) then
5061 l_creation_date := p_creation_date;
5062 end if;
5063
5064 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5065 l_last_updated_by := p_last_updated_by;
5066 end if;
5067
5068 if (p_last_update_date <> FND_API.G_MISS_DATE) then
5069 l_last_update_date := p_last_update_date;
5070 end if;
5071
5072 if (p_last_update_login <> FND_API.G_MISS_NUM) then
5073 l_last_update_login := p_last_update_login;
5074 end if;
5075
5076 --** - next, load non-null columns **
5077
5078 -- THIS UPDATES NO MATTER WHAT - CALLING IS_UPDATEABLE BECAUSE STILL
5079 -- NECESSARY FOR PRE-12 CODE
5080 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
5081 p_loader_timestamp => p_loader_timestamp,
5082 p_created_by => l_created_by,
5083 p_creation_date => l_creation_date,
5084 p_last_updated_by => l_last_updated_by,
5085 p_db_last_updated_by => l_cust_reg_item_rec.last_updated_by,
5086 p_last_update_date => l_last_update_date,
5087 p_db_last_update_date => l_cust_reg_item_rec.last_update_date,
5088 p_last_update_login => l_last_update_login,
5089 p_create_or_update => 'UPDATE') then
5090 null;
5091 end if;
5092
5093 update AK_CUSTOM_REGION_ITEMS set
5094 PROPERTY_VARCHAR2_VALUE = l_cust_reg_item_rec.property_varchar2_value,
5095 PROPERTY_NUMBER_VALUE = l_cust_reg_item_rec.property_number_value,
5096 PROPERTY_DATE_VALUE = l_cust_reg_item_rec.property_date_value,
5097 LAST_UPDATE_DATE = l_last_update_date,
5098 LAST_UPDATED_BY = l_last_updated_by,
5099 LAST_UPDATE_LOGIN = l_last_update_login
5100 where REGION_APPLICATION_ID = p_region_application_id
5101 and REGION_CODE = p_region_code
5102 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
5103 and CUSTOMIZATION_CODE = p_custom_code
5104 and ATTRIBUTE_APPLICATION_ID = p_attribute_appl_id
5105 and ATTRIBUTE_CODE = p_attribute_code
5106 and PROPERTY_NAME = p_property_name;
5107 if (sql%notfound) then
5108 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5109 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_UPDATE_FAILED');
5110 FND_MSG_PUB.Add;
5111 end if;
5112 -- dbms_output.put_line(l_api_name || 'Row does not exist during update');
5113 raise FND_API.G_EXC_ERROR;
5114 end if;
5115
5116 update AK_CUSTOM_REGION_ITEMS_TL set
5117 PROPERTY_VARCHAR2_VALUE = l_cust_reg_item_tl_rec.property_varchar2_value,
5118 LAST_UPDATE_DATE = l_last_update_date,
5119 LAST_UPDATED_BY = l_last_updated_by,
5120 LAST_UPDATE_LOGIN = l_last_update_login,
5121 SOURCE_LANG = l_lang
5122 where REGION_APPLICATION_ID = p_region_application_id
5123 and REGION_CODE = p_region_code
5124 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
5125 and CUSTOMIZATION_CODE = p_custom_code
5126 and ATTRIBUTE_APPLICATION_ID = p_attribute_appl_id
5127 and ATTRIBUTE_CODE = p_attribute_code
5128 and PROPERTY_NAME = p_property_name
5129 and l_lang in (LANGUAGE, SOURCE_LANG);
5130 if (sql%notfound) then
5131 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5132 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_UPDATE_FAILED');
5133 FND_MSG_PUB.Add;
5134 end if;
5135 --dbms_output.put_line(l_api_name || 'TL Row does not exist during update');
5136 raise FND_API.G_EXC_ERROR;
5137 end if;
5138
5139 -- /** commit the update **/
5140 -- commit;
5141
5142 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
5143 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_UPDATED');
5144 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
5145 ' ' || p_region_code ||
5146 ' ' || to_char(p_custom_appl_id) ||
5147 ' ' || p_custom_code || ' ' ||
5148 p_property_name);
5149 FND_MSG_PUB.Add;
5150 end if;
5151
5152 p_return_status := FND_API.G_RET_STS_SUCCESS;
5153
5154 FND_MSG_PUB.Count_And_Get (
5155 p_count => p_msg_count,
5156 p_data => p_msg_data);
5157
5158 EXCEPTION
5159 WHEN VALUE_ERROR THEN
5160 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5161 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_VALUE_ERROR');
5162 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
5163 ' ' || p_region_code ||
5164 ' ' || to_char(p_custom_appl_id) ||
5165 ' ' || p_custom_code || ' ' ||
5166 p_property_name);
5167 FND_MSG_PUB.Add;
5168 end if;
5169 rollback to start_update_cust_reg_item;
5170 p_return_status := FND_API.G_RET_STS_ERROR;
5171 FND_MSG_PUB.Count_And_Get (
5172 p_count => p_msg_count,
5173 p_data => p_msg_data);
5174 WHEN FND_API.G_EXC_ERROR THEN
5175 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5176 FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_ITEM_NOT_UPDATED');
5177 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
5178 ' ' || p_region_code ||
5179 ' ' || to_char(p_custom_appl_id) ||
5180 ' ' || p_custom_code || ' ' ||
5181 p_property_name);
5182 FND_MSG_PUB.Add;
5183 end if;
5184 p_return_status := FND_API.G_RET_STS_ERROR;
5185 rollback to start_update_cust_reg_item;
5186 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5187 SUBSTR (SQLERRM, 1, 240) );
5188 FND_MSG_PUB.Add;
5189 FND_MSG_PUB.Count_And_Get (
5190 p_count => p_msg_count,
5191 p_data => p_msg_data);
5192 end UPDATE_CUST_REG_ITEM;
5193
5194 --=======================================================
5195 -- Procedure UPDATE_CRITERIA
5196 --
5197 -- Usage Private API for updating a region graph.
5198 -- This API should only be called by other APIs
5199 -- that are owned by the Core Modules Team (AK).
5200 --
5201 -- Desc This API updates a region graph using the given info
5202 --
5203 -- Results The API returns the standard p_return_status parameter
5204 -- indicating one of the standard return statuses :
5205 -- * Unexpected error
5206 -- * Error
5207 -- * Success
5208 -- Parameters Region Graph columns
5209 -- p_loader_timestamp : IN optional
5210 -- If a timestamp is passed, the API will update the
5211 -- record using this timestamp. Only the upload API
5212 -- should call with this parameter loaded.
5213 --
5214 -- Version Initial version number = 1.0
5215 -- History Current version number = 1.0
5216 --=======================================================
5217 procedure UPDATE_CRITERIA (
5218 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5219 p_api_version_number IN NUMBER,
5220 p_init_msg_tbl IN BOOLEAN := FALSE,
5221 p_msg_count OUT NOCOPY NUMBER,
5222 p_msg_data OUT NOCOPY VARCHAR2,
5223 p_return_status OUT NOCOPY VARCHAR2,
5224 p_custom_appl_id IN NUMBER,
5225 p_custom_code IN VARCHAR2,
5226 p_region_application_id IN NUMBER,
5227 p_region_code IN VARCHAR2,
5228 p_attribute_appl_id IN NUMBER,
5229 p_attribute_code IN VARCHAR2,
5230 p_sequence_number IN NUMBER,
5231 p_operation IN VARCHAR2 := FND_API.G_MISS_CHAR,
5232 p_value_varchar2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
5233 p_value_number IN NUMBER := FND_API.G_MISS_NUM,
5234 p_value_date IN DATE := FND_API.G_MISS_DATE,
5235 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
5236 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
5237 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
5238 p_creation_date IN DATE := FND_API.G_MISS_DATE,
5239 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
5240 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
5241 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
5242 p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
5243 p_pass IN NUMBER,
5244 p_copy_redo_flag IN OUT NOCOPY BOOLEAN
5245 ) is
5246 cursor l_get_row_csr is
5247 select *
5248 from AK_CRITERIA
5249 where CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
5250 and CUSTOMIZATION_CODE = p_custom_code
5251 and REGION_APPLICATION_ID = p_region_application_id
5252 and REGION_CODE = p_region_code
5253 and ATTRIBUTE_APPLICATION_ID = p_attribute_appl_id
5254 and ATTRIBUTE_CODE = p_attribute_code
5255 and SEQUENCE_NUMBER = p_sequence_number
5256 for update of OPERATION;
5257 l_api_version_number CONSTANT number := 1.0;
5258 l_api_name CONSTANT varchar2(30) := 'Update_Criteria';
5259 l_created_by number;
5260 l_creation_date date;
5261 l_criteria_rec ak_criteria%ROWTYPE;
5262 l_error boolean;
5263 l_last_update_date date;
5264 l_last_update_login number;
5265 l_last_updated_by number;
5266 l_return_status varchar2(1);
5267 l_submit varchar2(1) := 'N';
5268 l_encrypt varchar2(1) := 'N';
5269 begin
5270 IF NOT FND_API.Compatible_API_Call (
5271 l_api_version_number, p_api_version_number, l_api_name,
5272 G_PKG_NAME) then
5273 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5274 return;
5275 END IF;
5276
5277 -- Initialize the message table if requested.
5278
5279 if p_init_msg_tbl then
5280 FND_MSG_PUB.initialize;
5281 end if;
5282
5283 savepoint start_update_criteria;
5284
5285 --** retrieve ak_criteria row if it exists **
5286 open l_get_row_csr;
5287 fetch l_get_row_csr into l_criteria_rec;
5288 if (l_get_row_csr%notfound) then
5289 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) then
5290 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_DOES_NOT_EXIST');
5291 FND_MSG_PUB.Add;
5292 end if;
5293 --dbms_output.put_line(l_api_name || 'Error - Row does not exist');
5294 close l_get_row_csr;
5295 raise FND_API.G_EXC_ERROR;
5296 end if;
5297 close l_get_row_csr;
5298
5299 --
5300 -- validate table columns passed in
5301 --
5302 if p_validation_level <> FND_API.G_VALID_LEVEL_NONE then
5303 if not VALIDATE_CRITERIA (
5304 p_validation_level => p_validation_level,
5305 p_api_version_number => 1.0,
5306 p_return_status => l_return_status,
5307 p_region_application_id => p_region_application_id,
5308 p_region_code => p_region_code,
5309 p_custom_application_id => p_custom_appl_id,
5310 p_custom_code => p_custom_code,
5311 p_attr_appl_id => p_attribute_appl_id,
5312 p_attr_code => p_attribute_code,
5313 p_sequence_number => p_sequence_number,
5314 p_operation => p_operation,
5315 p_value_varchar2 => p_value_varchar2,
5316 p_value_number => p_value_number,
5317 p_value_date => p_value_date,
5318 p_start_date_Active => p_start_date_active,
5319 p_end_date_active => p_end_date_active,
5320 p_caller => AK_ON_OBJECTS_PVT.G_UPDATE,
5321 p_pass => p_pass
5322 ) then
5323 --dbms_output.put_line(l_api_name || ' validation failed');
5324 -- Do not raise an error if it's the first pass
5325 if (p_pass = 1) then
5326 p_copy_redo_flag := TRUE;
5327 else
5328 raise FND_API.G_EXC_ERROR;
5329 end if;
5330 end if;
5331 end if;
5332
5333 --** Load record to be updated to the database **
5334 --** - first load nullable columns **
5335
5336 if (p_value_varchar2 <> FND_API.G_MISS_CHAR) or
5337 (p_value_varchar2 is null) then
5338 l_criteria_rec.value_varchar2 := p_value_varchar2;
5339 end if;
5340
5341 if (p_value_number <> FND_API.G_MISS_NUM) or
5342 (p_value_number is null) then
5343 l_criteria_rec.value_number := p_value_number;
5344 end if;
5345
5346 if (p_value_date <> FND_API.G_MISS_DATE) or
5347 (p_value_date is null) then
5348 l_criteria_rec.value_date := p_value_date;
5349 end if;
5350
5351 if (p_end_date_active <> FND_API.G_MISS_DATE) or
5352 (p_end_date_active is null) then
5353 l_criteria_rec.end_date_active := p_end_date_active;
5354 end if;
5355
5356 --** - next, load non-null columns **
5357
5358 if (p_operation <> FND_API.G_MISS_CHAR) then
5359 l_criteria_rec.operation := p_operation;
5360 end if;
5361
5362 if (p_start_date_active <> FND_API.G_MISS_DATE) then
5363 l_criteria_rec.start_date_active := p_start_date_active;
5364 end if;
5365
5366 if (p_created_by <> FND_API.G_MISS_NUM) then
5367 l_created_by := p_created_by;
5368 end if;
5369
5370 if (p_creation_date <> FND_API.G_MISS_DATE) then
5371 l_creation_date := p_creation_date;
5372 end if;
5373
5374 if (p_last_updated_by <> FND_API.G_MISS_NUM) then
5375 l_last_updated_by := p_last_updated_by;
5376 end if;
5377
5378 if (p_last_update_date <> FND_API.G_MISS_DATE) then
5379 l_last_update_date := p_last_update_date;
5380 end if;
5381
5382 if (p_last_update_login <> FND_API.G_MISS_NUM) then
5383 l_last_update_login := p_last_update_login;
5384 end if;
5385
5386 -- THIS UPDATES NO MATTER WHAT - CALLING IS_UPDATEABLE BECAUSE STILL
5387 -- NECESSARY FOR PRE-12 CODE
5388 if AK_ON_OBJECTS_PVT.IS_UPDATEABLE(
5389 p_loader_timestamp => p_loader_timestamp,
5390 p_created_by => l_created_by,
5391 p_creation_date => l_creation_date,
5392 p_last_updated_by => l_last_updated_by,
5393 p_db_last_updated_by => l_criteria_rec.last_updated_by,
5394 p_last_update_date => l_last_update_date,
5395 p_db_last_update_date => l_criteria_rec.last_update_date,
5396 p_last_update_login => l_last_update_login,
5397 p_create_or_update => 'UPDATE') then
5398 null;
5399 end if;
5400
5401 update AK_CRITERIA set
5402 OPERATION = l_criteria_rec.operation,
5403 VALUE_VARCHAR2 = l_criteria_rec.value_varchar2,
5404 VALUE_NUMBER = l_criteria_rec.value_number,
5405 VALUE_DATE = l_criteria_rec.value_date,
5406 START_DATE_ACTIVE = l_criteria_rec.start_date_active,
5407 END_DATE_ACTIVE = l_criteria_rec.end_date_active,
5408 LAST_UPDATE_DATE = l_last_update_date,
5409 LAST_UPDATED_BY = l_last_updated_by,
5410 LAST_UPDATE_LOGIN = l_last_update_login
5411 where REGION_APPLICATION_ID = p_region_application_id
5412 and REGION_CODE = p_region_code
5413 and CUSTOMIZATION_APPLICATION_ID = p_custom_appl_id
5414 and CUSTOMIZATION_CODE = p_custom_code
5415 and ATTRIBUTE_APPLICATION_ID = p_attribute_appl_id
5416 and ATTRIBUTE_CODE = p_attribute_code
5417 and SEQUENCE_NUMBER = p_sequence_number;
5418 if (sql%notfound) then
5419 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5420 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_UPDATE_FAILED');
5421 FND_MSG_PUB.Add;
5422 end if;
5423 -- dbms_output.put_line(l_api_name || 'Row does not exist during update');
5424 raise FND_API.G_EXC_ERROR;
5425 end if;
5426
5427 -- /** commit the update **/
5428 -- commit;
5429
5430 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
5431 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_UPDATED');
5432 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
5433 ' ' || p_region_code ||
5434 ' ' || to_char(p_custom_appl_id) ||
5435 ' ' || p_custom_code || ' ' ||
5436 to_char(p_sequence_number));
5437 FND_MSG_PUB.Add;
5438 end if;
5439
5440 p_return_status := FND_API.G_RET_STS_SUCCESS;
5441
5442 FND_MSG_PUB.Count_And_Get (
5443 p_count => p_msg_count,
5444 p_data => p_msg_data);
5445
5446 EXCEPTION
5447 WHEN VALUE_ERROR THEN
5448 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5449 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_VALUE_ERROR');
5450 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
5451 ' ' || p_region_code ||
5452 ' ' || to_char(p_custom_appl_id) ||
5453 ' ' || p_custom_code || ' ' ||
5454 to_char(p_sequence_number));
5455 FND_MSG_PUB.Add;
5456 end if;
5457 rollback to start_update_criteria;
5458 p_return_status := FND_API.G_RET_STS_ERROR;
5459 FND_MSG_PUB.Count_And_Get (
5460 p_count => p_msg_count,
5461 p_data => p_msg_data);
5462 WHEN FND_API.G_EXC_ERROR THEN
5463 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
5464 FND_MESSAGE.SET_NAME('AK','AK_CRITERIA_NOT_UPDATED');
5465 FND_MESSAGE.SET_TOKEN('KEY', to_char(p_region_application_id) ||
5466 ' ' || p_region_code ||
5467 ' ' || to_char(p_custom_appl_id) ||
5468 ' ' || p_custom_code || ' ' ||
5469 to_char(p_sequence_number));
5470 FND_MSG_PUB.Add;
5471 end if;
5472 p_return_status := FND_API.G_RET_STS_ERROR;
5473 rollback to start_update_criteria;
5474 FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
5475 SUBSTR (SQLERRM, 1, 240) );
5476 FND_MSG_PUB.Add;
5477 FND_MSG_PUB.Count_And_Get (
5478 p_count => p_msg_count,
5479 p_data => p_msg_data);
5480 end UPDATE_CRITERIA;
5481
5482 end AK_CUSTOM_PVT;
[Home] [Help]