1 PACKAGE BODY QA_CORE_PKG as
2 /* $Header: qltcoreb.plb 120.0 2005/05/24 17:08:49 appldev noship $ */
3
4 FUNCTION get_result_column_name (ELEMENT_ID IN NUMBER, P_ID IN NUMBER) RETURN VARCHAR2 IS
5 --
6 -- This is a function that returns the unique column name in the table
7 -- qa_results given an element_id, plan_id combination.
8 --
9
10 name VARCHAR2(2400);
11 hardcoded VARCHAR2(240);
12
13
14 /* Bug 3754667. Commenting out the below cursors as it is no longer used.
15
16 CURSOR c1 (P_ID NUMBER) IS
17
18 select hardcoded_column
19 from qa_chars
20 where char_id = P_ID;
21
22 CURSOR c2 (E_ID NUMBER) IS
23
24 select developer_name
25 from qa_chars
26 where char_id = E_ID;
27
28 CURSOR c3 (pl_id NUMBER,
29 ch_id NUMBER) IS
30
31 select result_column_name
32 from qa_plan_chars
33 where plan_id = pl_id
34 and char_id = ch_id;
35 */
36
37 BEGIN
38
39 -- Bug 3754667. We'll use the qa_chars_api and qa_plan_element_api
40 -- functions to use the cache instead of the cursors that fetches
41 -- from the DB each time.
42 -- kabalakr. Wed Jul 28 03:06:38 PDT 2004.
43
44 /* OPEN c1 (ELEMENT_ID);
45 FETCH c1 INTO hardcoded;
46 CLOSE c1;
47 */
48
49 hardcoded := qa_chars_api.hardcoded_column(ELEMENT_ID);
50
51 if (hardcoded is not null) then
52
53 /* OPEN c2 (ELEMENT_ID);
54 FETCH c2 INTO name;
55 CLOSE c2;
56 */
57
58 name := qa_chars_api.developer_name(ELEMENT_ID);
59
60 else
61
62 /* OPEN c3 (P_ID, ELEMENT_ID);
63 FETCH c3 INTO name;
64 CLOSE c3;
65 */
66
67 name := qa_plan_element_api.qpc_result_column_name(P_ID, ELEMENT_ID);
68
69 end if;
70
71 return name;
72
73 EXCEPTION when others then
74 raise;
75
76 END get_result_column_name;
77
78
79 FUNCTION get_element_id (ELEMENT_NAME IN VARCHAR2) RETURN NUMBER IS
80 --
81 -- This is a function that returns the element id (char_id) given
82 -- an element name.
83 --
84
85 ID NUMBER;
86
87 BEGIN
88
89 select char_id into ID
90 from qa_chars
91 where upper(Name) = upper(ELEMENT_NAME);
92
93 return ID;
94
95 EXCEPTION when others then
96 raise;
97
98 END get_element_id;
99
100
101 FUNCTION get_plan_id ( PLAN_NAME IN VARCHAR2) RETURN NUMBER IS
102 --
103 -- This is a function that returns the plan id given a plan name.
104 --
105 --
106
107 ID NUMBER;
108
109 BEGIN
110
111 select plan_id into ID
112 from qa_plans
113 where Name = PLAN_NAME;
114
115 return ID;
116
117
118 EXCEPTION when others then
119 raise;
120
121 END get_plan_id;
122
123
124 FUNCTION get_plan_name (GIVEN_PLAN_ID IN NUMBER) RETURN VARCHAR2 IS
125 --
126 -- This is a function that returns the plan name givn a plan id
127 --
128
129 PLAN_NAME VARCHAR2(240);
130
131 BEGIN
132
133 /* Bug 3754667. We would use the cache implementation in qa_plans_api
134 rather than fetching plan_name from DB each time. kabalakr
135
136 select name into PLAN_NAME
137 from qa_plans
138 where plan_id = GIVEN_PLAN_ID;
139 */
140
141 PLAN_NAME := qa_plans_api.plan_name(GIVEN_PLAN_ID);
142
143 return PLAN_NAME;
144
145 EXCEPTION when others then
146 raise;
147
148 END get_plan_name;
149
150
151 FUNCTION is_mandatory (GIVEN_PLAN_ID IN NUMBER, ELEMENT_ID IN NUMBER) RETURN BOOLEAN IS
152 --
153 -- This is a function that determines if an element is mandatory for a plan.
154 -- Calling program must supply a plan_id and the element_id for the element
155 -- in question.
156 --
157
158 result NUMBER;
159
160 BEGIN
161
162 /* Bug 3754667. We would use the cache implementation in qa_plan_element_api
163 rather than fetching the mandatory flag from the DB each time. kabalakr.
164
165 select mandatory_flag into result
166 from qa_plan_chars
167 where plan_id = GIVEN_PLAN_ID and char_id = ELEMENT_ID;
168
169 */
170
171 result := qa_plan_element_api.qpc_mandatory_flag(GIVEN_PLAN_ID, ELEMENT_ID);
172
173 if (result = 1) then
174 return true;
175 else
176 return false;
177 end if;
178
179
180 EXCEPTION when others then
181 raise;
182
183 END is_mandatory;
184
185
186 FUNCTION get_element_data_type (ELEMENT_ID IN NUMBER) RETURN NUMBER IS
187 --
188 -- This is a function that determines the data type of a collection element.
189 -- This is a overloaded function. This function takes element id as the
190 -- parameter.
191 --
192 -- The possible data type are:
193 --
194 -- datatype 1 is Character
195 -- datatype 2 is Number
196 -- datatype 3 is Date
197
198 atype number;
199
200 BEGIN
201
202 select datatype into atype from qa_chars where char_id = ELEMENT_ID;
203
204 return atype;
205
206 EXCEPTION when others then
207 raise;
208
209
210 END get_element_data_type;
211
212
213 FUNCTION get_element_data_type (ELEMENT_NAME IN VARCHAR2) RETURN NUMBER IS
214 --
215 -- This is a function that determines the data type of a collection element.
216 -- This is a overloaded function. This function takes element name as the
217 -- parameter.
218 --
219 -- The possible data type are:
220 --
221 -- datatype 1 is Character
222 -- datatype 2 is Number
223 -- datatype 3 is Date
224
225 atype number;
226
227 BEGIN
228
229 -- datatype 1 is Character
230 -- datatype 2 is Number
231 -- datatype 3 is Date
232
233 select datatype into atype from qa_chars where name = ELEMENT_NAME;
234
235 return atype;
236
237 EXCEPTION when others then
238 raise;
239
240 END get_element_data_type;
241
242
243 PROCEDURE EXEC_SQL (STRING IN VARCHAR2) IS
244 --
245 -- This is a procedure that executes a sql script. Calling program must
246 -- supply a valid sql statement.
247 --
248 -- This is a duplicate procedure, I will remove it as soon as
249 -- I can get a chance -OB
250
251
252 CUR INTEGER;
253 RET INTEGER;
254
255 BEGIN
256
257 CUR := DBMS_SQL.OPEN_CURSOR;
258 DBMS_SQL.PARSE(CUR, STRING, DBMS_SQL.NATIVE);
259 RET := DBMS_SQL.EXECUTE(CUR);
260 DBMS_SQL.CLOSE_CURSOR(CUR);
261
262 exception when others then
263 IF dbms_sql.is_open(cur) THEN
264 dbms_sql.close_cursor(cur);
265 END IF;
266 raise;
267 END EXEC_SQL;
268
269 --------------------------------------------
270 -- Copying Bryans function from qltvcreb here
271 -- isivakum : this is a useful function in core pkg
272
273 FUNCTION dequote(s1 in varchar2) RETURN varchar2 IS
274 --
275 -- The string s1 may be used in a dynamically constructed SQL
276 -- statement. If s1 contains a single quote, there will be syntax
277 -- error. This function returns a string s2 that is same as s1
278 -- except each single quote is replaced with two single quotes.
279 -- Put in for NLS fix. Previously if plan name or element name
280 -- contains a single quote, that will cause problem when creating
281 -- views.
282 -- bso
283 --
284 BEGIN
285 RETURN replace(s1, '''', '''''');
286 END;
287 --------------------------------------------------------
288
289
290 -- Bug 3777530
291 /* --------------------------------------------------------------- */
292 /* PROC exec_sql_with_bind */
293 /* This is a generic procedure for executing dynamic SQL with */
294 /* dynamic BIND variables. */
295 /* IN parameters - */
296 /* p_sql - Dynamic sql string with bind variables in the string */
297 /* vars_in - bind parameters occuring in the SQL string exactly */
298 /* should be paased in order of occurence in SQL in this */
299 /* table. */
300 /* values_in - Values correcsponding to the bind parameters in */
301 /* same orderand index as bind variables */
302 /* saugupta Wed, 01 Dec 2004 23:03:20 -0800 PDT */
303 /* --------------------------------------------------------------- */
304
305 PROCEDURE exec_sql_with_binds(p_sql in varchar2,
306 vars_in IN var_in_tab,
307 values_in IN value_in_tab) IS
308 cursor_handle INTEGER;
309 status INTEGER;
310 counter INTEGER;
311 count_var INTEGER;
312 count_val INTEGER;
313
314 BEGIN
315
316 IF (vars_in.COUNT() <> values_in.count()) THEN
317 RAISE_APPLICATION_ERROR(-20999,'Bind variables and values does not match');
318 RETURN;
319 END IF ;
320
321 cursor_handle := DBMS_SQL.OPEN_CURSOR;
322 DBMS_SQL.PARSE(cursor_handle,p_sql,dbms_sql.native);
323
324 FOR i IN vars_in.FIRST .. vars_in.LAST
325 LOOP
326 DBMS_SQL.BIND_VARIABLE (cursor_handle, vars_in(i), values_in(i));
327 END LOOP;
328
329 status := DBMS_SQL.EXECUTE(cursor_handle);
330
331 DBMS_SQL.CLOSE_CURSOR (cursor_handle);
332
333 EXCEPTION
334 WHEN OTHERS
335 THEN
336 IF DBMS_SQL.IS_OPEN(cursor_handle) THEN
337 DBMS_SQL.CLOSE_CURSOR (cursor_handle);
338 END IF;
339 RAISE;
340 END exec_sql_with_binds;
341
342
343
344 -- Bug 4270911. CU2 SQL Literal fix.
345 -- Set of procedures to execute a dynamic sql from forms.
346 -- Wrapper for fnd_dsql procedures.
347 -- Use restricted to DDL.
348 -- srhariha. Mon Apr 18 06:11:06 PDT 2005.
349
350 -- Simple Wrappers around fnd_dsql
351
352 PROCEDURE dsql_init IS
353
354 BEGIN
355
356 fnd_dsql.init;
357
358 END dsql_init;
359
360
361
362 PROCEDURE dsql_add_text(p_text IN VARCHAR2) IS
363
364 BEGIN
365
366 fnd_dsql.add_text(p_text);
367 END dsql_add_text;
368
369
370 PROCEDURE dsql_add_bind(p_value IN VARCHAR2) IS
371
372 BEGIN
373
374 fnd_dsql.add_bind(p_value);
375 END dsql_add_bind;
376
377 PROCEDURE dsql_add_bind(p_value IN DATE) IS
378
379 BEGIN
380
381 fnd_dsql.add_bind(p_value);
382 END dsql_add_bind;
383
384 PROCEDURE dsql_add_bind(p_value IN NUMBER) IS
385
386 BEGIN
387
388 fnd_dsql.add_bind(p_value);
389 END dsql_add_bind;
390
391
392
393 -- Execute procedure. Executes the SQL built by the
394 -- add_text and add_bind calls.
395
396 PROCEDURE dsql_execute IS
397
398 cursor_handle NUMBER;
399 sql_text VARCHAR2(32000);
400 ret_value NUMBER;
401
402 BEGIN
403
407
404 cursor_handle := dbms_sql.open_cursor;
405
406 fnd_dsql.set_cursor(cursor_handle);
408 sql_text := fnd_dsql.get_text;
409
410 -- qa_skiplot_utility.insert_error_log(p_module_name => 'cu2.qlttxn.100', p_error_message => sql_text);
411
412 dbms_sql.parse(cursor_handle,sql_text,dbms_sql.NATIVE);
413
414 -- bind the variable
415 fnd_dsql.do_binds;
416
417 ret_value := dbms_sql.execute(cursor_handle);
418
419 dbms_sql.close_cursor(cursor_handle);
420
421 EXCEPTION
422 WHEN OTHERS
423 THEN
424 IF dbms_sql.is_open(cursor_handle) THEN
425 dbms_sql.close_cursor (cursor_handle);
426 END IF;
427 RAISE; -- dont know what to do with exception. So just propagate it.
428
429
430 END dsql_execute;
431
432
433
434 end QA_CORE_PKG;
435
[Home] [Help]