[Home] [Help]
PACKAGE BODY: APPS.AK_DEFAULT_VALIDATE
Source
1 PACKAGE BODY ak_default_validate AS
2 /* $Header: akdefvlb.pls 120.2 2005/09/29 13:59:39 tshort ship $ */
3 --****************************************************************
4 --CREATE PACKAGES
5 --****************************************************************
6 PROCEDURE create_packages(
7 P_package_type IN VARCHAR2,
8 P_database_object_name IN VARCHAR2,
9 P_region_application_id IN NUMBER,
10 P_region_code IN VARCHAR2)
11 IS
12 l_database_object_name VARCHAR2(30);
13 l_number_found INTEGER;
14
15 -- The following SQL is used to determine if any APIs are defined for object. If
16 -- no APIs then exit.
17 CURSOR l_object_api_csr(db_obj_name VARCHAR2) IS
18 select count(*)
19 from
20 ak_objects ao,
21 ak_object_attributes aoa
22 where
23 ao.database_object_name = db_obj_name and
24 aoa.database_object_name = db_obj_name and
25 ((ao.defaulting_api_pkg is not null and
26 ao.defaulting_api_proc is not null)
27 or
28 (ao.validation_api_pkg is not null and
29 ao.validation_api_proc is not null)
30 or
31 (aoa.defaulting_api_pkg is not null and
32 aoa.defaulting_api_proc is not null)
33 or
34 (aoa.validation_api_pkg is not null and
35 aoa.validation_api_proc is not null));
36
37
38 -- The following SQL is used to determine if any APIs are defined for region. If
39 -- no APIs then exit.
40 CURSOR l_region_api_csr(db_reg_id NUMBER,
41 db_reg_code VARCHAR2) IS
42 select count(*), max(ar.database_object_name)
43 from
44 ak_regions ar,
45 ak_region_items ari
46 where
47 ar.region_application_id = db_reg_id and
48 ar.region_code = db_reg_code and
49 ari.region_application_id = db_reg_id and
50 ari.region_code = db_reg_code and
51 ((ar.region_defaulting_api_pkg is not null and
52 ar.region_defaulting_api_proc is not null)
53 or
54 (ar.region_validation_api_pkg is not null and
55 ar.region_validation_api_proc is not null)
56 or
57 (ari.region_defaulting_api_pkg is not null and
58 ari.region_defaulting_api_proc is not null)
59 or
60 (ari.region_validation_api_pkg is not null and
61 ari.region_validation_api_proc is not null));
62
63 BEGIN
64 -- Check if there are any APIs defined. If not then exit immediately.
65 -- We must handle the following special cases:
66 -- 1) Only the object has API's defined, none for regions. Thus
67 -- we must create a package for the object but not the region.
68 -- 2) Only region has API's defined, none for object. But we
69 -- must create packages for BOTH the object and the region. To
70 -- be safe we will recreate the object package since it may not
71 -- have been created when the object was defined.
72 IF P_package_type = 'OBJECT' THEN
73 OPEN l_object_api_csr(P_database_object_name);
74 FETCH l_object_api_csr INTO l_number_found;
75 CLOSE l_object_api_csr;
76 IF l_number_found > 0 THEN
77 create_record_package(
78 'OBJECT',
79 P_database_object_name,
80 NULL,
81 NULL);
82 END IF;
83 ELSE
84 OPEN l_region_api_csr(P_region_application_id,
85 P_region_code);
86 FETCH l_region_api_csr INTO l_number_found, l_database_object_name;
87 CLOSE l_region_api_csr;
88 IF l_number_found > 0 THEN
89 create_record_package(
90 'OBJECT',
91 l_database_object_name,
92 NULL,
93 NULL);
94 create_record_package(
95 'REGION',
96 NULL,
97 P_region_application_id,
98 P_region_code);
99 END IF;
100 END IF;
101
102 END create_packages;
103
104
105 --****************************************************************
106 --CREATE RECORD PACKAGE
107 --****************************************************************
108 PROCEDURE create_record_package(
109 P_package_type IN VARCHAR2,
110 P_database_object_name IN VARCHAR2,
111 P_region_application_id IN NUMBER,
112 P_region_code IN VARCHAR2)
113 IS
114 l_create_csr INTEGER;
115 l_rows_processed INTEGER;
116 l_database_object_name VARCHAR2(30);
117 l_region_application_id NUMBER;
118 l_region_code VARCHAR2(30);
119 l_attribute_name VARCHAR2(60);
120 l_data_type VARCHAR2(30);
121 l_missing_const VARCHAR2(30);
122 l_package_name VARCHAR2(90);
123 l_region_note VARCHAR2(240);
124 l_sql_string1 LONG(32000);
125 l_sql_string2 LONG(32000);
126 l_first_attribute BOOLEAN := TRUE;
127 l_one_attribute_found BOOLEAN := FALSE;
128
129 -- Yes, Yes, it's a kludge -- but it works well thank you. Based upon the
130 -- above input parameters, either the top or bottom select statement will
131 -- return rows, but not both. If the P_package_type is OBJECT then we get
132 -- attributes from ak_object_attributes. If REGION then we get the attributes
133 -- from ak_region_items. This allows the following program to reference
134 -- only one cursor for both types.
135 CURSOR l_csr( db_obj_name VARCHAR2,
136 db_reg_id NUMBER,
137 db_reg_code VARCHAR2) IS
138 select aa.attribute_application_id,
139 aa.attribute_code,
140 aa.data_type,
141 aa.attribute_value_length
142 from ak_object_attributes aoa,
143 ak_attributes aa
144 where aoa.database_object_name = db_obj_name and
145 aoa.attribute_application_id = aa.attribute_application_id and
146 aoa.attribute_code = aa.attribute_code
147 union all
148 select aa.attribute_application_id,
149 aa.attribute_code,
150 aa.data_type,
151 aa.attribute_value_length
152 from ak_region_items ari,
153 ak_attributes aa
154 where ari.region_application_id = db_reg_id and
155 ari.region_code = db_reg_code and
156 ari.attribute_application_id = aa.attribute_application_id and
157 ari.attribute_code = aa.attribute_code and
158 upper(ari.object_attribute_flag) = 'N';
159
160 BEGIN
161 -- Define if we are creating a package for an object or a region. If object, then
162 -- only the parameter P_database_object_name is allowed. If region then
163 -- P_database_object_name must be NULL. Likewise for region.
164 IF P_package_type = 'OBJECT' THEN
165 l_package_name := '"' || trunc_name('AK$' || P_database_object_name, 0, 'F') || '"';
166 l_database_object_name := P_database_object_name;
167 l_region_code := NULL;
168 l_region_application_id := NULL;
169 ELSE
170 l_package_name := '"' || trunc_name('AK$' || P_region_code,
171 P_region_application_id,
172 'T') || '"';
173 l_database_object_name := NULL;
174 l_region_code := P_region_code;
175 l_region_application_id := P_region_application_id;
176 END IF;
177
178 -- Add a note for the region record structre. It will only contain attributes that are
179 -- mark with the flag "object_attribute_flag=Y".
180 IF P_package_type = 'REGION' THEN
181 l_region_note := '
182 '||'/*Note: The region record structure will only' ||'
183 '||' contain attributes that have been marked' ||'
184 '||' object_attribute_flag = ''N''. Use the object' ||'
185 '||' record structure for all other attributes.*/';
186 END IF;
187
188 -- Define package spec and record name.
189 l_sql_string1 := 'CREATE OR REPLACE PACKAGE ' || l_package_name ||' AS ' || l_region_note ||'
190 '||' TYPE REC IS RECORD (' ||'
191 ';
192
193 -- Define package body.
194 l_sql_string2 := 'CREATE OR REPLACE PACKAGE BODY ' || l_package_name ||' AS '|| l_region_note ||'
195 '||' PROCEDURE DEFAULT_MISSING('||'
196 '||' P_REC IN OUT REC)'||'
197 '||' IS' ||'
198 '||' BEGIN'||'
199 ';
200
201 -- Query database for attributes.
202 FOR l_rec IN l_csr(l_database_object_name, l_region_application_id, l_region_code) LOOP
203 l_attribute_name := '"' || trunc_name(l_rec.attribute_code, l_rec.attribute_application_id, 'T') || '"';
204 l_data_type := upper(rtrim(l_rec.data_type));
205
206
207 IF l_data_type <> 'URL' THEN
208 l_one_attribute_found := TRUE;
209 -- Define each element in record structure.
210 IF l_first_attribute THEN
211 l_first_attribute := false;
212 ELSE
213 l_sql_string1 := l_sql_string1 || ', ' ||'
214 ';
215 END IF;
216 l_sql_string1 := l_sql_string1 || ' ' || l_attribute_name || ' ';
217 IF l_data_type = 'VARCHAR2' THEN
218 l_sql_string1 := l_sql_string1 || 'VARCHAR(' || to_char(l_rec.attribute_value_length) || ')';
219 ELSIF l_data_type = 'DATETIME' THEN
220 l_sql_string1 := l_sql_string1 || 'DATE';
221 ELSIF l_data_type = 'BOOLEAN' THEN
222 l_sql_string1 := l_sql_string1 || 'VARCHAR2(1)';
223 ELSE
224 l_sql_string1 := l_sql_string1 || l_data_type;
225 END IF;
226
227
228 -- Define each statement in the procedure "DEFAULT_MISSING".
229 IF instr(l_data_type, 'CHAR') > 0 THEN
230 l_missing_const := 'G_MISS_CHAR';
231 ELSIF l_data_type = 'NUMBER' THEN
232 l_missing_const := 'G_MISS_NUM';
233 ELSIF l_data_type = 'DATE' THEN
234 l_missing_const := 'G_MISS_DATE';
235 END IF;
236 l_sql_string2 := l_sql_string2 ||
237 ' P_REC.' || l_attribute_name ||
238 ' := FND_API.' || l_missing_const || ';' ||'
239 ';
240 END IF;
241
242 END LOOP;
243
244 -- If strings are blank, then no attributes were found for object/region. This may or
245 -- or may not be OK. For the object, this doesn't make sense, but region may make
246 -- sense if there are no attributes marked as "object_attribute_flag=N". In either case
247 -- add comments to inform user and a dummy variable so package will compile.
248 IF not l_one_attribute_found THEN
249 IF P_package_type = 'OBJECT' THEN
250 l_sql_string1 := l_sql_string1||' /* WARNING: No object attributes were found.*/ '||'
251 ';
252 l_sql_string2 := l_sql_string2||' /* WARNING: No object attributes were found.*/ '||'
253 ';
254 ELSE
255 l_sql_string1 := l_sql_string1||
256 ' /* NOTE: No region items were found with object_attribute_flag = ''N''.*/'||'
257 ';
258 l_sql_string2 := l_sql_string2||
259 ' /* NOTE: No region items were found with object_attribute_flag = ''N''.*/'||'
260 ';
261 END IF;
262 l_sql_string1 := l_sql_string1 ||
263 ' DUMMY VARCHAR2(1)';
264 l_sql_string2 := l_sql_string2||
265 ' P_REC.DUMMY := FND_API.G_MISS_CHAR;'||'
266 ';
267 END IF;
268
269
270 -- Complete package spec.
271 l_sql_string1 := l_sql_string1||');'||'
272 '|| ' PROCEDURE DEFAULT_MISSING (P_REC IN OUT REC);'||'
273 '||'END;';
274
275 -- Complete package body.
276 l_sql_string2 := l_sql_string2|| ' END;' ||'
277 '||'END;';
278
279
280 --update ldh set s=l_sql_string1;
281 --commit;
282
283 l_create_csr := dbms_sql.open_cursor;
284
285 -- Issue a dynamic SQL statement to create/recreate spec.
286 dbms_sql.parse(l_create_csr, l_sql_string1, dbms_sql.v7);
287 l_rows_processed := dbms_sql.execute(l_create_csr);
288
289 -- Issue a dynamic SQL statement to create/recreate body.
290 dbms_sql.parse(l_create_csr, l_sql_string2, dbms_sql.v7);
291 l_rows_processed := dbms_sql.execute(l_create_csr);
292
293 dbms_sql.close_cursor(l_create_csr);
294
295 -- Generic exception trap.
296 exception
297 when others then
298 dbms_sql.close_cursor(l_create_csr);
299 raise;
300
301 END create_record_package;
302
303
304 --****************************************************************
305 --API SHELL
306 --****************************************************************
307 PROCEDURE api_shell (
308 p_source_type IN VARCHAR2,
309 p_cur_attribute_appl_id IN NUMBER,
310 p_cur_attribute_code IN VARCHAR2,
311 p_object_validation_api IN VARCHAR2,
312 p_object_defaulting_api IN VARCHAR2,
313 p_object_name IN VARCHAR2,
314 p_region_validation_api IN VARCHAR2,
315 p_region_defaulting_api IN VARCHAR2,
316 p_region_appl_id IN NUMBER,
317 p_region_code IN VARCHAR2,
318 p_structure IN OUT NOCOPY VARCHAR2,
319 p_data IN OUT NOCOPY VARCHAR2,
320 p_attr_num IN NUMBER,
321 p_status OUT NOCOPY VARCHAR2,
322 p_message OUT NOCOPY VARCHAR2)
323 IS
324 ATTR_VALUE_LEN CONSTANT INTEGER := 240;
325 ATTR_DATATYPE_LEN CONSTANT INTEGER := 4;
326 ATTR_SOURCE_LEN CONSTANT INTEGER := 1;
327 ATTR_CODE_LEN CONSTANT INTEGER := 30;
328 ATTR_ID_LEN CONSTANT INTEGER := 10;
329 ATTR_SIZE_LEN CONSTANT INTEGER := 5;
330 ATTR_DATE_LEN CONSTANT INTEGER := 14;
331 ATTR_NUMBER_LEN CONSTANT INTEGER := 20;
332 ATTR_CODE_OFFSET CONSTANT INTEGER := ATTR_ID_LEN;
333 ATTR_DATATYPE_OFFSET CONSTANT INTEGER := ATTR_CODE_OFFSET + ATTR_CODE_LEN;
334 ATTR_SOURCE_OFFSET CONSTANT INTEGER := ATTR_DATATYPE_OFFSET + ATTR_DATATYPE_LEN;
335 ATTR_DATALEN_OFFSET CONSTANT INTEGER := ATTR_SOURCE_OFFSET + ATTR_SOURCE_LEN;
336 ATTR_DATAPOS_OFFSET CONSTANT INTEGER := ATTR_DATALEN_OFFSET + ATTR_SIZE_LEN;
337 ATTR_TOTAL_SIZE CONSTANT INTEGER := ATTR_DATAPOS_OFFSET + ATTR_SIZE_LEN;
338
339 TYPE Attr_Rec_Type IS RECORD
340 (value VARCHAR2(240),
341 data_type VARCHAR2(4),
342 attribute_source VARCHAR2(1),
343 attribute_id NUMBER,
344 attribute_code VARCHAR2(30)
345 );
346 TYPE Attr_Tbl_Type IS TABLE OF Attr_Rec_Type
347 INDEX BY BINARY_INTEGER;
348
349 l_attr_tbl Attr_Tbl_Type;
350
351 l_call_api_csr INTEGER;
352 l_rows_processed INTEGER;
353 l_temp_number NUMBER(20,6);
354 l_temp_char VARCHAR2(240);
355 l_temp_date DATE;
356 l_data_len INTEGER;
357 l_data_value VARCHAR2(240);
358 l_data_type VARCHAR2(4);
359 l_data_pos INTEGER;
360 l_value_number NUMBER(20);
361 l_value_date DATE;
362 l_value_char VARCHAR2(240);
363 l_value_len INTEGER;
364 l_value_counter INTEGER;
365 l_max_bound_values INTEGER;
366 l_attr_id NUMBER;
367 l_attr_begin INTEGER;
368 l_attr_code_len INTEGER;
369 l_attr_code VARCHAR2(30);
370 l_attr_code_w_quotes VARCHAR2(32);
371 l_attr_code_source VARCHAR2(30);
372 l_attr_structure VARCHAR2(90);
373 l_obj_rec_name VARCHAR2(40);
374 l_obj_package_name VARCHAR2(60);
378 l_reg_package_name VARCHAR2(60);
375 l_obj_declaration VARCHAR2(240);
376 l_obj_call_default_missing VARCHAR2(240);
377 l_reg_rec_name VARCHAR2(40);
379 l_rec_name_p VARCHAR2(30);
380 l_reg_declaration VARCHAR2(240);
381 l_reg_param VARCHAR2(240);
382 l_reg_call_default_missing VARCHAR2(240);
383 l_attr_params VARCHAR2(240);
384 l_validation_api VARCHAR2(240);
385 l_defaulting_api VARCHAR2(240);
386 l_bind_variable VARCHAR2(10);
387 l_sql_call_string1 VARCHAR2(2000);
388 l_sql_call_string2 VARCHAR2(2000);
389 l_sql_string LONG(32000);
390 l_sql_rec_before LONG(20000);
391 l_sql_rec_after LONG(20000);
392 l_status VARCHAR2(1);
393 l_message VARCHAR2(2000);
394 l_status_msg_declaration VARCHAR2(240);
395 l_status_msg_return VARCHAR2(240);
396
397 BEGIN
398 --update ldh set s=p_structure, s2=p_data;
399 --commit;
400 -- The following are examples of 'structure' and 'object'
401 -- parameters.
402 --p_structure :=
403 -- ' 708ORG_ID NUM O 1' ||
404 -- ' 708ORG_NAME CHAR O10 21' ||
405 -- ' 708CZ5 DATE O 31';
406 --p_data :=
407 -- '123456 ' ||
408 -- 'Barry Lind' ||
409 -- '19580910';
410
411
412 -- Parse object record structure into sql statement, and
413 -- load data into l_attr_tbl table. Then bind the sql
414 -- statement to the individual data elements of l_attr_tbl.
415 l_obj_rec_name := trunc_name('rec' || p_object_name, 0, 'F');
416 l_reg_rec_name := trunc_name('rec' || p_region_code, p_region_appl_id, 'T');
417 l_attr_begin := 1;
418 l_value_counter := 1;
419
420
421 -- Loop through each attribute defined in structure.
422 FOR i IN 1..p_attr_num LOOP
423 l_attr_id := to_number(substr(p_structure, l_attr_begin,
424 ATTR_ID_LEN));
425 l_attr_code := rtrim(substr(p_structure, l_attr_begin + ATTR_CODE_OFFSET,
426 ATTR_CODE_LEN));
427 l_attr_code_len := length(l_attr_code);
428 l_attr_code_w_quotes := '"' || trunc_name(l_attr_code, l_attr_id, 'T') || '"';
429 l_attr_code_source := substr(p_structure, l_attr_begin + ATTR_SOURCE_OFFSET,
430 ATTR_SOURCE_LEN);
431 l_data_type := substr(p_structure, l_attr_begin + ATTR_DATATYPE_OFFSET,
432 ATTR_DATATYPE_LEN);
433 l_data_pos := to_number(substr(p_structure, l_attr_begin + ATTR_DATAPOS_OFFSET,
434 ATTR_SIZE_LEN));
435 l_bind_variable := ':v' || to_char(l_value_counter);
436
437
438 -- Calculate size of data.
439 IF l_data_type = 'NUM ' THEN
440 l_value_len := ATTR_NUMBER_LEN;
441 ELSIF l_data_type = 'DATE' THEN
442 l_value_len := ATTR_DATE_LEN;
443 ELSIF l_data_type = 'CHAR' THEN
444 l_value_len := to_number(
445 substr(p_structure, l_attr_begin + ATTR_DATALEN_OFFSET, ATTR_SIZE_LEN));
446 END IF;
447
448
449 -- A value of 'O' is for object and 'R' is for region. This is passed in the structure
450 -- from calling program. It determines whether the attribute is to be found on the
451 -- object or region record.
452 IF l_attr_code_source = 'O' THEN
453 l_rec_name_p := l_obj_rec_name || '.';
454 ELSE
455 l_rec_name_p := l_reg_rec_name || '.';
456 END IF;
457
458
459 -- Build SQL statements that contain references to variables.
460 -- First, build SQL statements that assigns values into record before calling API.
461 -- Second, build SQL statements that extracts values from record after calling API.
462 l_sql_rec_before := l_sql_rec_before ||
463 l_rec_name_p ||
464 l_attr_code_w_quotes || ':=' ||
465 l_bind_variable || '; ';
466 l_sql_rec_after := l_sql_rec_after ||
467 l_bind_variable || ':=' ||
468 l_rec_name_p ||
469 l_attr_code_w_quotes || '; ';
470
471
472 -- Assign values and datatype into a local table so we will have
473 -- a memory structure to 'bind' to, and later know how to build a
474 -- set of return parameter strings.
475 l_attr_tbl(i).data_type := l_data_type;
476 l_attr_tbl(i).attribute_source := l_attr_code_source;
477 l_attr_tbl(i).attribute_id := l_attr_id;
478 l_attr_tbl(i).attribute_code := l_attr_code;
479 l_attr_tbl(i).value := rtrim(substr(p_data, l_data_pos, l_value_len));
480
481
482 -- Prepare for next attribute.
483 l_value_counter := l_value_counter + 1;
487
484 l_attr_begin := l_attr_begin + ATTR_TOTAL_SIZE;
485
486 END LOOP;
488
489 -- Setup SQL string fragments that will be used in following SQL
490 -- statement definitions.
491 l_status_msg_declaration := 'l_status VARCHAR2(1):=''T''; l_message VARCHAR2(2000); ';
492 l_status_msg_return := ':v_status:=l_status; :v_message:=l_message; ';
493 l_obj_package_name := trunc_name('AK$' || p_object_name, 0, 'F');
494 l_obj_declaration := l_obj_rec_name || ' ' || l_obj_package_name || '.REC; ';
495 l_obj_call_default_missing := l_obj_package_name || '.DEFAULT_MISSING(' ||
496 l_obj_rec_name || '); ';
497 IF (p_region_validation_api IS NOT NULL) OR (p_region_defaulting_api IS NOT NULL) THEN
498 l_reg_package_name := trunc_name('AK$' || p_region_code, p_region_appl_id, 'T');
499 l_reg_declaration := l_reg_rec_name || ' ' || l_reg_package_name || '.REC; ';
500 l_reg_call_default_missing := l_reg_package_name || '.DEFAULT_MISSING(' ||
501 l_reg_rec_name || '); ';
502 END IF;
503
504 -- Build optional attribute parameters. These are used if the
505 -- validation and defaulting is called at the attribute or item level.
506 IF p_cur_attribute_code IS NOT NULL THEN
507 l_attr_params := p_cur_attribute_appl_id || ',''' || p_cur_attribute_code || '''';
508 ELSE
509 l_attr_params := '';
510 END IF;
511
512
513 -- Build call interfaces to API. Either API routine is optional so
514 -- we must skip the call if NULL.
515
516 IF p_source_type = 'REGION/OBJECT DEFAULTING' THEN
517 IF p_object_defaulting_api IS NOT NULL THEN
518 l_sql_call_string1 :=
519 p_object_defaulting_api || '(' || l_obj_rec_name || '); ';
520 END IF;
521 IF p_region_defaulting_api IS NOT NULL THEN
522 l_sql_call_string2 :=
523 p_region_defaulting_api || '(' || l_obj_rec_name || ', ' ||
524 l_reg_rec_name || '); ';
525 END IF;
526
527 ELSIF p_source_type = 'OBJECT ATTRIBUTE VALIDATION/DEFAULTING' THEN
528 IF p_object_validation_api IS NOT NULL THEN
529 l_sql_call_string1 :=
530 p_object_validation_api || '(' || l_obj_rec_name || ', ' ||
531 l_attr_params || ',' ||
532 'l_status, l_message); ';
533 END IF;
534 IF p_object_defaulting_api IS NOT NULL THEN
535 l_sql_call_string2 :=
536 'IF l_status = ''T'' THEN ' ||
537 p_object_defaulting_api || '(' || l_obj_rec_name || ', ' ||
538 l_attr_params || '); ' ||
539 'END IF; ';
540 END IF;
541
542 ELSIF p_source_type = 'REGION ITEM VALIDATION/DEFAULTING' THEN
543 IF p_region_validation_api IS NOT NULL THEN
544 l_sql_call_string1 :=
545 p_region_validation_api || '(' || l_obj_rec_name || ', ' ||
546 l_reg_rec_name || ', ' ||
547 l_attr_params || ',' ||
548 'l_status, l_message); ';
549 END IF;
550 IF p_region_defaulting_api IS NOT NULL THEN
551 l_sql_call_string2 :=
552 'IF l_status = ''T'' THEN ' ||
553 p_region_defaulting_api || '(' || l_obj_rec_name || ', ' ||
554 l_reg_rec_name || ', ' ||
555 l_attr_params || '); ' ||
556 'END IF; ';
557 END IF;
558
559 ELSIF p_source_type = 'REGION/OBJECT VALIDATION' THEN
560 IF p_object_validation_api IS NOT NULL THEN
561 l_sql_call_string1 :=
562 p_object_validation_api || '(' || l_obj_rec_name || ', ' ||
563 'l_status, l_message); ';
564 END IF;
565 IF p_region_validation_api IS NOT NULL THEN
566 l_sql_call_string2 :=
567 'IF l_status = ''T'' THEN ' ||
568 p_region_validation_api || '(' || l_obj_rec_name || ', ' ||
569 l_reg_rec_name || ', ' ||
570 'l_status, l_message); ' ||
571 'END IF; ';
572 END IF;
573
574 END IF;
575
576
577
578 -- Build dynamic sql statement to call the APIs.
579 l_sql_string :=
580 'DECLARE ' ||
581 l_status_msg_declaration ||
582 l_reg_declaration ||
583 l_obj_declaration ||
584 'BEGIN ' ||
585 l_obj_call_default_missing ||
586 l_reg_call_default_missing ||
587 l_sql_rec_before ||
591 l_status_msg_return ||
588 l_sql_call_string1 ||
589 l_sql_call_string2 ||
590 l_sql_rec_after ||
592 'END;';
593
594 --update ldh set s=l_sql_string;
595 --commit;
596
597 l_call_api_csr := dbms_sql.open_cursor;
598 dbms_sql.parse(l_call_api_csr, l_sql_string, dbms_sql.v7);
599
600 -- Bind the variables v1..v'99' to sql string.
601 FOR i IN 1..p_attr_num LOOP
602 IF l_attr_tbl(i).data_type = 'NUM ' THEN
603 dbms_sql.bind_variable(l_call_api_csr,
604 'v'||to_char(i),
605 to_number(l_attr_tbl(i).value));
606 ELSIF l_attr_tbl(i).data_type = 'CHAR' THEN
607 dbms_sql.bind_variable(l_call_api_csr,
608 'v'||to_char(i),
609 l_attr_tbl(i).value,
610 ATTR_VALUE_LEN);
611 ELSIF l_attr_tbl(i).data_type = 'DATE' THEN
612 dbms_sql.bind_variable(l_call_api_csr,
613 'v'||to_char(i),
614 to_date(l_attr_tbl(i).value, 'YYYYMMDDHH24MISS'));
615 END IF;
616 END LOOP;
617
618 -- Bind the variables v_status and v_message to sql string.
619 dbms_sql.bind_variable(l_call_api_csr, 'v_status', l_status, 1);
620 dbms_sql.bind_variable(l_call_api_csr, 'v_message', l_message, 2000);
621
622 -- Call APIs.
623 l_rows_processed := dbms_sql.execute(l_call_api_csr);
624
625 -- Return variables v_status and v_message.
626 dbms_sql.variable_value(l_call_api_csr, 'v_status', l_status);
627 dbms_sql.variable_value(l_call_api_csr, 'v_message', l_message);
628 p_status := l_status;
629 p_message := l_message;
630
631
632 -- Build return structure and data parameters.
633 IF (l_status = 'T') AND
634 (p_source_type <> 'REGION/OBJECT VALIDATION') THEN
635 p_structure := '';
636 p_data := '';
637 l_data_pos := 1;
638 FOR i IN 1..p_attr_num LOOP
639 l_attr_structure := rpad(to_char(l_attr_tbl(i).attribute_id), ATTR_ID_LEN) ||
640 rpad(l_attr_tbl(i).attribute_code, ATTR_CODE_LEN) ||
641 l_attr_tbl(i).data_type ||
642 l_attr_tbl(i).attribute_source;
643 IF l_attr_tbl(i).data_type = 'NUM ' THEN
644 dbms_sql.variable_value(l_call_api_csr, 'v'||to_char(i), l_temp_number);
645 l_attr_structure := l_attr_structure || ' ';
646 l_data_len := ATTR_NUMBER_LEN;
647 l_data_value := rpad(nvl(to_char(l_temp_number),' '), ATTR_NUMBER_LEN);
648 ELSIF l_attr_tbl(i).data_type = 'CHAR' THEN
649 dbms_sql.variable_value(l_call_api_csr, 'v'||to_char(i), l_temp_char);
650 l_temp_char := rtrim(ltrim(l_temp_char));
651 l_data_len := nvl(length(l_temp_char), 0);
652 l_attr_structure := l_attr_structure || lpad(to_char(l_data_len), ATTR_SIZE_LEN);
653 l_data_value := l_temp_char;
654 ELSIF l_attr_tbl(i).data_type = 'DATE' THEN
655 dbms_sql.variable_value(l_call_api_csr, 'v'||to_char(i), l_temp_date);
656 l_attr_structure := l_attr_structure || ' ';
657 l_data_len := ATTR_DATE_LEN;
658 l_data_value := rpad(nvl(to_char(l_temp_date, 'YYYYMMDDHH24MISS'),' '), ATTR_DATE_LEN);
659 END IF;
660 l_attr_structure := l_attr_structure || lpad(to_char(l_data_pos), ATTR_SIZE_LEN);
661 l_data_pos := l_data_pos + l_data_len;
662 p_structure := p_structure || l_attr_structure;
663 p_data := p_data || l_data_value;
664 END LOOP;
665 END IF;
666
667 dbms_sql.close_cursor(l_call_api_csr);
668
669 -- Generic exception trap.
670 exception
671 when others then
672 l_message :=
673 'ERROR
674
675 Error = ' || sqlerrm || '
676 Call Type = ' || p_source_type || '
677 Attribute Code = ' || p_cur_attribute_code || '
678 Attr Appl Id = ' || to_char(p_cur_attribute_appl_id) || '
679 Object Record = ' || l_obj_rec_name || '
680 Region Record = ' || l_reg_rec_name || '
681 API Call 1 = ' || l_sql_call_string1 || '
682 API Call 2 = ' || l_sql_call_string2;
683 p_message := l_message;
684
685 p_status := 'E';
686 dbms_sql.close_cursor(l_call_api_csr);
687
688 END api_shell;
689
690
691 --****************************************************************
692 --TRUNC NAME
693 --****************************************************************
694 FUNCTION trunc_name (
695 p_name IN VARCHAR2,
696 p_id IN NUMBER,
697 p_include_number IN VARCHAR2)
698 RETURN VARCHAR2 IS
699 l_id_char VARCHAR2(30);
700 l_id_size NUMBER;
701 l_max_name_size NUMBER;
702 l_result VARCHAR2(60);
703 BEGIN
704 l_id_char := to_char(p_id);
705 l_id_size := length(l_id_char);
706 IF p_include_number = 'T' THEN
707 l_max_name_size := 30 - l_id_size - 1;
708 l_result := substr(rtrim(ltrim(p_name)), 1, l_max_name_size) || '$' || l_id_char;
709 ELSE
710 l_max_name_size := 30;
711 l_result := substr(rtrim(ltrim(p_name)), 1, l_max_name_size);
712 END IF;
713 RETURN l_result;
714 END trunc_name;
715
716 END ak_default_validate;