1 PACKAGE BODY QA_ERES_UTIL AS
2 /* $Header: qaedrutb.pls 120.7.12010000.2 2009/12/29 10:27:00 ntungare ship $ */
3
4
5 ---------------------------------------------------------------------
6 PROCEDURE find_topmost_parent
7 (p_child_occ IN NUMBER,
8 p_child_coll_id IN NUMBER,
9 p_child_plan_id IN NUMBER,
10 p_parent_occ OUT NOCOPY NUMBER,
11 p_parent_coll_id OUT NOCOPY NUMBER,
12 p_parent_plan_id OUT NOCOPY NUMBER) IS
13 ---------------------------------------------------------------------
14
15
16 CURSOR C1 (l_occ NUMBER) IS
17 SELECT parent_occurrence, parent_collection_id, parent_plan_id
18 FROM qa_pc_results_relationship qprr
19 CONNECT BY prior qprr.parent_occurrence = qprr.child_occurrence
20 START WITH qprr.child_occurrence = l_occ
21 ORDER BY level desc;
22
23 BEGIN
24
25 OPEN C1(p_child_occ);
26 FETCH C1 INTO p_parent_occ, p_parent_coll_id, p_parent_plan_id;
27
28 IF C1%NOTFOUND THEN
29 CLOSE C1;
30
31 p_parent_occ := p_child_occ;
32 p_parent_coll_id := p_child_coll_id;
33 p_parent_plan_id := p_child_plan_id;
34
35 END IF;
36
37 --
38 -- bug 7478786
39 -- Added a check before closing the cursor
40 -- ntungare
41 --
42 IF C1%ISOPEN THEN
43 CLOSE C1;
44 END IF;
45
46 END find_topmost_parent;
47
48
49 ---------------------------------------------------------------------
50 FUNCTION get_result_esig_status (p_occurrence IN NUMBER,
51 p_coll_id IN NUMBER,
52 p_plan_id IN NUMBER,
53 p_char_id IN NUMBER)
54 RETURN VARCHAR2 IS
55 ---------------------------------------------------------------------
56
57 l_res_col VARCHAR2(30);
58 l_sql_string VARCHAR2(1000);
59 l_status VARCHAR2(80);
60
61 BEGIN
62
63 l_res_col := QA_FLEX_UTIL.qpc_result_column_name(p_plan_id, p_char_id);
64
65 IF l_res_col IS NULL THEN
66 return NULL;
67 END IF;
68
69
70 l_sql_string := 'SELECT '||l_res_col ||' FROM QA_RESULTS '
71 || 'WHERE plan_id = :1'
72 || ' AND collection_id = :2'
73 || ' AND occurrence = :3';
74
75 EXECUTE IMMEDIATE l_sql_string INTO l_status
76 USING p_plan_id, p_coll_id, p_occurrence;
77
78 return l_status;
79
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN --needed for bug 3225461
82 return NULL;
83
84 END get_result_esig_status;
85
86
87 ---------------------------------------------------------------------
88 FUNCTION get_mfg_lookups_meaning (p_lookup_type IN VARCHAR2,
89 p_lookup_code IN NUMBER)
90 RETURN VARCHAR2 IS
91 ---------------------------------------------------------------------
92
93 l_meaning VARCHAR2(80);
94
95 CURSOR C1 IS
96 SELECT meaning
97 FROM mfg_lookups
98 WHERE lookup_code = p_lookup_code
99 AND lookup_type = p_lookup_type;
100
101 BEGIN
102
103 OPEN C1;
104 FETCH C1 INTO l_meaning;
105
106 IF C1%NOTFOUND THEN
107 CLOSE C1;
108 RETURN 'NO_CODE_FOUND';
109
110 END IF;
111
112 --
113 -- bug 7478786
114 -- Closing the cursor before returning
115 -- ntungare
116 --
117 IF C1%ISOPEN THEN
118 CLOSE C1;
119 END IF;
120
121 RETURN l_meaning;
122
123 END get_mfg_lookups_meaning;
124
125 -- R12 ERES Support in Service Family. Bug 4345768
126 -- START
127 -- This function returns if a given plan is
128 -- enabled for deferred eSignatures. Returns Y or N
129
130 FUNCTION is_def_sig_enabled (p_plan_id IN NUMBER)
131 RETURN VARCHAR2
132 IS
133 CURSOR check_plan_element( c_plan_id NUMBER ) IS
134 SELECT 'Y'
135 FROM QA_PLAN_CHARS
136 WHERE char_id = 2147483572
137 AND enabled_flag = 1
138 AND plan_id = c_plan_id;
139
140 l_return_status VARCHAR2(1);
141
142 BEGIN
143 l_return_status := 'N';
144
145 OPEN check_plan_element( p_plan_id );
146 FETCH check_plan_element INTO l_return_status;
147 CLOSE check_plan_element;
148
149 RETURN l_return_status;
150
151 END is_def_sig_enabled;
152
153 -- This procedure enables a given collection plan for Deferred
154 -- Esignatures by adding the 'eSignature Status' element to the plan.
155 PROCEDURE add_esig_status ( p_plan_id IN NUMBER )
156 IS
157 l_prompt QA_CHARS.prompt%TYPE;
158 l_prompt_seq QA_PLAN_CHARS.prompt_sequence%TYPE;
159 l_result_seq NUMBER;
160 l_result_column QA_PLAN_CHARS.result_column_name%TYPE;
161 l_char_id QA_CHARS.char_id%TYPE;
162
163 l_message QA_PLAN_CHAR_ACTIONS.message%TYPE;
164 l_user_id NUMBER;
165 l_qpcat_id QA_PLAN_CHAR_ACTION_TRIGGERS.plan_char_action_trigger_id%TYPE;
166 l_qpca_id QA_PLAN_CHAR_ACTIONS.plan_char_action_id%TYPE;
167
168 CURSOR get_prompt_seq( c_plan_id NUMBER ) IS
169 SELECT MAX( prompt_sequence ) + 10
170 FROM QA_PLAN_CHARS
171 WHERE plan_id = c_plan_id;
172
173 -- Bug 4958731: SQL Repository Fix
174
175 -- Bug 5218065. SHKALYAN 10-May-2006.
176 -- When No Softcoded or user defined elements with result column
177 -- of type CHARACTERXXare present in the plan, this SQL returns NULL
178 -- thereby leading to undesirable results. Added the NVL so that
179 -- this SQL always returns a value ( 0 in such cases ).
180 CURSOR get_result_column( c_plan_id NUMBER ) IS
181 SELECT
182 nvl( max( to_number(substr(result_column_name,10,3)) ), 0 )
183 FROM qa_plan_chars qpc,
184 qa_chars qc
185 WHERE qpc.plan_id = c_plan_id
186 AND qc.char_id = qpc.char_id
187 AND qc.hardcoded_column is null
188 AND qc.datatype in (1,2,3,6);
189
190 BEGIN
191
192 -- Get the Char ID of eSignature Status element
193 l_char_id := QA_SS_CONST.esignature_status;
194
195 -- Get the Prompt for the eSignature Status element
196 l_prompt := QA_CHARS_API.prompt( l_char_id );
197
198 -- Get the Next Prompt Sequence for the Given Plan
199 OPEN get_prompt_seq( p_plan_id );
200 FETCH get_prompt_seq INTO l_prompt_seq;
201 CLOSE get_prompt_seq;
202
203 -- Get the Max Result Column for the Given Plan
204 OPEN get_result_column( p_plan_id );
205 FETCH get_result_column INTO l_result_seq;
206 CLOSE get_result_column;
207
208 -- Derive the Result Column Name
209 l_result_column := 'CHARACTER' || TO_CHAR( l_result_seq + 1 );
210
211 l_user_id := FND_GLOBAL.user_id;
212
213 -- Insert the eSignature status plan element
214 INSERT INTO QA_PLAN_CHARS
215 (
216 plan_id,
217 char_id,
218 last_update_date,
219 last_updated_by,
220 creation_date,
221 created_by,
222 prompt_sequence,
223 prompt,
224 enabled_flag,
225 mandatory_flag,
226 read_only_flag,
227 ss_poplist_flag,
228 information_flag,
229 values_exist_flag,
230 displayed_flag,
231 result_column_name
232 )
233 VALUES
234 (
235 p_plan_id,
236 l_char_id,
237 SYSDATE,
238 l_user_id,
239 SYSDATE,
240 l_user_id,
241 l_prompt_seq,
242 l_prompt,
243 1,
244 2,
245 1,
246 2,
247 2,
248 2,
249 1,
250 l_result_column
251 );
252
253 -- Need to Insert the 'Reject the Input' action to prevent
254 -- the user from updating a Qa Result when the eSignature
255 -- status element is PENDING.
256
257 -- Get the Plan Element Action Trigger ID
258 SELECT qa_plan_char_action_triggers_s.nextval
259 INTO l_qpcat_id
260 FROM dual;
261
262 -- Create a new Plan Element Action Trigger
263 INSERT INTO qa_plan_char_action_triggers
264 (
265 plan_char_action_trigger_id,
266 last_update_date,
267 last_updated_by,
268 creation_date,
269 created_by,
270 trigger_sequence,
271 plan_id,
272 char_id,
273 operator,
274 low_value_lookup,
275 high_value_lookup,
276 low_value_other,
277 high_value_other,
278 low_value_other_id,
279 high_value_other_id
280 )
281 VALUES
282 (
283 l_qpcat_id,
284 sysdate,
285 l_user_id,
286 sysdate,
287 l_user_id,
288 10,
289 p_plan_id,
290 l_char_id,
291 1,
292 NULL,
293 NULL,
294 'PENDING',
295 NULL,
296 NULL,
297 NULL
298 );
299
300 -- Generate the Plan Element Action ID
301 SELECT qa_plan_char_actions_s.nextval
302 INTO l_qpca_id
303 FROM dual;
304
305 -- Get the Rejection Message
306 l_message := FND_MESSAGE.get_string( 'QA', 'QA_ERES_CANNOT_UPDATE_RESULT' );
307
308 INSERT INTO qa_plan_char_actions
309 (
310 plan_char_action_id,
311 last_update_date,
312 last_updated_by,
313 creation_date,
314 created_by,
315 plan_char_action_trigger_id,
316 action_id,
317 message,
318 assign_type
319 )
320 VALUES
321 (
322 l_qpca_id,
323 sysdate,
324 l_user_id,
325 sysdate,
326 l_user_id,
327 l_qpcat_id,
328 2,
329 l_message,
330 --'Results may not be entered while the eSignature Status is Pending.',
331 'F'
332 );
333
334 END add_esig_status;
335
336 -- END
337 -- R12 ERES Support in Service Family. Bug 4345768
338
339
340 -- Bug 4502450. R12 Esig Status support in Multirow UQR
341 -- saugupta Wed, 24 Aug 2005 08:37:40 -0700 PDT
342
343 -- For a row in a plan Function return T if eSign
344 -- Status is PENDING else returns F
345 FUNCTION is_esig_status_pending(p_plan_id IN NUMBER,
346 p_collection_id IN NUMBER,
347 p_occurrence IN NUMBER)
348 RETURN VARCHAR2
349 IS
350 -- eSignature Status is seeded as varchar(20)
351 l_status VARCHAR2(20);
352
353 BEGIN
354 -- check for null parameters
355 IF( p_plan_id IS NULL OR
356 p_occurrence IS NULL OR
357 p_collection_id IS NULL) THEN
358 -- return False
359 RETURN 'F';
360 END IF;
361
362 -- get result esig status
363 l_status :=
364 QA_ERES_UTIL.get_result_esig_status
365 (p_occurrence => p_occurrence,
366 p_coll_id => p_collection_id,
367 p_plan_id => p_plan_id,
368 p_char_id => qa_ss_const.esignature_status); -- check if this API checks for Eres Profile
369
370 -- check if the eSignature Status is PENDING.
371 -- We can safely compare it with Harcoded PENDING String
372 -- as Description is different in for transalation issues
373 IF ( l_status = 'PENDING') THEN
374 return 'T';
375 ELSE
376 return 'F';
377 END IF;
378
379 END is_esig_status_pending;
380
381 -- R12.1 MES ERES Integration with Quality Start
382 -- This procedure takes in the collection id for a transaction
383 -- and generates the XML CLOB object for that transaction.
384 PROCEDURE generate_xml(p_collection_id IN varchar2,
385 x_xml_result OUT NOCOPY CLOB) IS
386 l_temp_xml CLOB;
387
388 CURSOR c(coll_id IN varchar2) is SELECT distinct plan_id
389 FROM qa_results
390 WHERE plan_id || '-' || collection_id || '-' || occurrence NOT IN
391 (SELECT child_plan_id || '-' || child_collection_id || '-' || child_occurrence
392 FROM qa_pc_results_relationship
393 WHERE parent_collection_id = coll_id)
394 AND collection_id = coll_id;
395
396 BEGIN
397 dbms_lob.createtemporary(x_xml_result,true);
398 dbms_lob.append(x_xml_result,'<QA_RESULTS>' );
399 FOR l_plan IN c(p_collection_id)
400 LOOP
401 generate_xml_for_plan(p_collection_id,l_plan.plan_id,l_temp_xml);
402 dbms_lob.append(x_xml_result,'<QA_ERES_INTEGRATION_PLANS>');
403 dbms_lob.append(x_xml_result,l_temp_xml);
404 dbms_lob.append(x_xml_result,'</QA_ERES_INTEGRATION_PLANS>');
405 END LOOP;
406 dbms_lob.append(x_xml_result,'</QA_RESULTS>');
407
408 END generate_xml;
409
410 -- This procedure takes in the collection id, plan_id
411 -- and generates the XML CLOB object for that plan in the transaction
412 PROCEDURE generate_xml_for_plan(p_collection_id varchar2,
413 p_plan_id IN varchar2,
414 x_xml_result_plan OUT NOCOPY CLOB) IS
415 l_temp_xml CLOB;
416 CURSOR c(coll_id IN varchar2, l_plan_id IN varchar2) is SELECT plan_id || '-' || collection_id || '-' || occurrence doc_id
417 FROM qa_results
418 WHERE plan_id = l_plan_id
419 AND collection_id = coll_id;
420 BEGIN
421 dbms_lob.createtemporary(x_xml_result_plan,true);
422 FOR l_doc_id IN c(p_collection_id, p_plan_id)
423 LOOP
424 get_xml_for_row(l_doc_id.doc_id,l_temp_xml);
425 dbms_lob.append(x_xml_result_plan,l_temp_xml);
426 END LOOP;
427 END generate_xml_for_plan;
428
429 -- This procedure generates the XML CLOB object for for a particular
430 -- result row identified by plan_id, collection_id and occurrence
431 PROCEDURE get_xml_for_row(p_document_id IN varchar2,
432 x_xml_result_row OUT NOCOPY CLOB) IS
433 err_code NUMBER;
434 err_msg VARCHAR2(4000);
435 log_file VARCHAR2(4000);
436 BEGIN
437 EDR_UTILITIES.GENERATE_XML( P_MAP_CODE => 'qa_results',
438 P_DOCUMENT_ID => p_document_id,
439 P_XML => x_xml_result_row,
440 P_ERROR_CODE => err_code,
441 P_ERROR_MSG => err_msg,
442 P_LOG_FILE => log_file );
443 END get_xml_for_row;
444 -- R12.1 MES ERES Integration with Quality End
445
446 END QA_ERES_UTIL;
447
[Home] [Help]