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