1 PACKAGE BODY QA_ERES_DEFER AS
2 /* $Header: qaedrdfb.pls 120.1 2006/09/14 23:33:57 shkalyan noship $ */
3
4
5 -- Global Variables for the 'Approval Status' values.
6
7 g_approved CONSTANT VARCHAR2(30) := 'APPROVED';
8 g_rejected CONSTANT VARCHAR2(30) := 'REJECTED';
9
10 -- Global Variable for the 'Approval Required' collection element
11 -- char_id.
12
13 g_esig_status_char_id CONSTANT NUMBER := 2147483572;
14
15
16 ---------------------------------------------------------------------
17 PROCEDURE update_res_col
18 (p_status_code IN VARCHAR2,
19 p_plan_id IN NUMBER,
20 p_collection_id IN NUMBER,
21 p_occurrence IN NUMBER
22 ) IS
23 ---------------------------------------------------------------------
24
25 l_sql_string VARCHAR2(1000);
26 l_res_col VARCHAR2(60);
27
28 BEGIN
29
30 l_res_col := QA_FLEX_UTIL.qpc_result_column_name(p_plan_id, g_esig_status_char_id);
31
32 l_sql_string := 'UPDATE qa_results SET ' || l_res_col ||' = :1'
33 ||' WHERE plan_id = :2'
34 ||' and collection_id = :3'
35 ||' and occurrence = :4';
36
37 BEGIN
38 EXECUTE IMMEDIATE l_sql_string USING p_status_code, p_plan_id,
39 p_collection_id, p_occurrence;
40
41 EXCEPTION
42 WHEN OTHERS THEN raise;
43
44 END;
45
46 END update_res_col;
47
48
49 -- R12 ERES Support in Service Family. Bug 4345768
50 -- START
51 -- New procedure for stamping approval status for a collection
52
53 -- Bug 5508639. SHKALYAN 13-Sep-2006.
54 -- New modularized function to check if per row event exists
55 -- for a collection.
56 FUNCTION per_row_event_exists
57 (p_plan_id IN NUMBER,
58 p_collection_id IN NUMBER,
59 p_occurrence IN NUMBER) RETURN VARCHAR2 IS
60 l_event_key VARCHAR2(1000);
61 l_event_exists VARCHAR2(1) := 'N';
62
63 CURSOR event_exists( c_event_name VARCHAR2, c_event_key VARCHAR2 ) IS
64 SELECT 'Y'
65 FROM EDR_PSIG_DOCUMENTS
66 WHERE event_name = c_event_name
67 AND event_key = c_event_key;
68
69 BEGIN
70 l_event_key := p_plan_id || '-' || p_collection_id || '-' || p_occurrence;
71
72 OPEN event_exists( 'oracle.apps.qa.result.create', l_event_key );
73 FETCH event_exists INTO l_event_exists;
74 CLOSE event_exists;
75
76 RETURN l_event_exists;
77
78 END per_row_event_exists;
79
80 -- Bug 5508639. SHKALYAN 13-Sep-2006.
81 -- Added new input parameter p_txn_header_id
82 PROCEDURE update_collection_col
83 (p_status_code IN VARCHAR2,
84 p_plan_id IN NUMBER,
85 p_collection_id IN NUMBER,
86 p_txn_header_id IN NUMBER
87 ) IS
88
89 -- Bug 5508639. SHKALYAN 13-Sep-2006.
90 -- New cursor to get result occurrences based on txn_header_id
91 -- Modified the name of the old cursor to get_results_col
92 CURSOR get_results_txn( c_txn_header_id NUMBER, c_collection_id NUMBER , c_plan_id NUMBER ) IS
93 SELECT occurrence
94 FROM QA_RESULTS
95 WHERE collection_id = c_collection_id
96 AND txn_header_id = c_txn_header_id
97 AND plan_id = c_plan_id;
98
99 CURSOR get_results_col( c_collection_id NUMBER , c_plan_id NUMBER ) IS
100 SELECT occurrence
101 FROM QA_RESULTS
102 WHERE collection_id = c_collection_id
103 AND plan_id = c_plan_id;
104
105 l_occurrence NUMBER;
106
107 BEGIN
108
109 IF ( p_txn_header_id IS NOT NULL ) THEN
110 FOR res_cur IN get_results_txn( p_txn_header_id, p_collection_id , p_plan_id ) LOOP
111 l_occurrence := res_cur.occurrence;
112
113 -- Check if 'per row' event has been raised for any of the
114 -- occurrences belonging to this collection. If so, do not
115 -- update these rows.
116 IF ( per_row_event_exists( p_plan_id, p_collection_id, l_occurrence ) = 'N' ) THEN
117 update_res_col(p_status_code,
118 p_plan_id,
119 p_collection_id,
120 l_occurrence);
121
122 END IF;
123 END LOOP;
124
125 ELSE
126
127 FOR res_cur IN get_results_col( p_collection_id , p_plan_id ) LOOP
128 l_occurrence := res_cur.occurrence;
129
130 -- Check if 'per row' event has been raised for any of the
131 -- occurrences belonging to this collection. If so, do not
132 -- update these rows.
133 IF ( per_row_event_exists( p_plan_id, p_collection_id, l_occurrence ) = 'N' ) THEN
134 update_res_col(p_status_code,
135 p_plan_id,
136 p_collection_id,
137 l_occurrence);
138
139 END IF;
140
141 END LOOP;
142
143 END IF;
144
145 END update_collection_col;
146
147 -- END
148 -- R12 ERES Support in Service Family. Bug 4345768
149
150 ---------------------------------------------------------------------
151 PROCEDURE spec_status_update
152 (p_spec_id in number
153 ) IS
154 ---------------------------------------------------------------------
155
156 l_status_code NUMBER;
157 l_sql_string VARCHAR2(1000);
158
159 BEGIN
160
161 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
162 l_status_code := 20;
163
164 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
165 l_status_code := 30;
166
167 END IF;
168
169 l_sql_string := 'UPDATE qa_specs SET spec_status = :1'
170 ||' WHERE spec_id = :2';
171
172 BEGIN
173 EXECUTE IMMEDIATE l_sql_string USING l_status_code, p_spec_id;
174
175 EXCEPTION
176 WHEN OTHERS THEN raise;
177
178 END;
179
180 END spec_status_update;
181
182
183 ---------------------------------------------------------------------
184 PROCEDURE ncm_approve
185 (p_plan_id IN NUMBER,
186 p_collection_id IN NUMBER,
187 p_occurrence IN NUMBER
188 ) IS
189 ---------------------------------------------------------------------
190
191 l_status_code VARCHAR2(30);
192
193 BEGIN
194
195 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
196 l_status_code := g_approved;
197
198 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
199 l_status_code := g_rejected;
200
201 END IF;
202
203 update_res_col(l_status_code,
204 p_plan_id,
205 p_collection_id,
206 p_occurrence);
207
208 END ncm_approve;
209
210
211 ---------------------------------------------------------------------
212 PROCEDURE ncm_detail_approve
213 (p_plan_id IN NUMBER,
214 p_collection_id IN NUMBER,
215 p_occurrence IN NUMBER
216 ) IS
217 ---------------------------------------------------------------------
218
219 l_status_code VARCHAR2(30);
220
221 BEGIN
222
223 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
224 l_status_code := g_approved;
225
226 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
227 l_status_code := g_rejected;
228
229 END IF;
230
231 update_res_col(l_status_code,
232 p_plan_id,
233 p_collection_id,
234 p_occurrence);
235
236
237 END ncm_detail_approve;
238
239
240 ---------------------------------------------------------------------
241 PROCEDURE disp_approve
242 (p_plan_id IN NUMBER,
243 p_collection_id IN NUMBER,
244 p_occurrence IN NUMBER
245 ) IS
246 ---------------------------------------------------------------------
247
248 l_status_code VARCHAR2(30);
249
250 BEGIN
251
252 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
253 l_status_code := g_approved;
254
255 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
256 l_status_code := g_rejected;
257
258 END IF;
259
260 update_res_col(l_status_code,
261 p_plan_id,
262 p_collection_id,
263 p_occurrence);
264
265 END disp_approve;
266
267
268 ---------------------------------------------------------------------
269 PROCEDURE disp_detail_approve
270 (p_plan_id IN NUMBER,
271 p_collection_id IN NUMBER,
272 p_occurrence IN NUMBER
273 ) IS
274 ---------------------------------------------------------------------
275
276 l_status_code VARCHAR2(30);
277
278 BEGIN
279
280 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
281 l_status_code := g_approved;
282
283 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
284 l_status_code := g_rejected;
285
286 END IF;
287
288 update_res_col(l_status_code,
289 p_plan_id,
290 p_collection_id,
291 p_occurrence);
292
293 END disp_detail_approve;
294
295
296
297 ---------------------------------------------------------------------
298 PROCEDURE car_review_approve
299 (p_plan_id IN NUMBER,
300 p_collection_id IN NUMBER,
301 p_occurrence IN NUMBER
302 ) IS
303 ---------------------------------------------------------------------
304
305 l_status_code VARCHAR2(30);
306
307 BEGIN
308
309 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
310 l_status_code := g_approved;
311
312 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
313 l_status_code := g_rejected;
314
315 END IF;
316
317 update_res_col(l_status_code,
318 p_plan_id,
319 p_collection_id,
320 p_occurrence);
321
322 END car_review_approve;
323
324
325
326 ---------------------------------------------------------------------
327 PROCEDURE car_impl_approve
328 (p_plan_id IN NUMBER,
329 p_collection_id IN NUMBER,
330 p_occurrence IN NUMBER
331 ) IS
332 ---------------------------------------------------------------------
333
334 l_status_code VARCHAR2(30);
335
336
337 BEGIN
338
339 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
340 l_status_code := g_approved;
341
342 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
343 l_status_code := g_rejected;
344
345 END IF;
346
347 update_res_col(l_status_code,
348 p_plan_id,
349 p_collection_id,
350 p_occurrence);
351
352 END car_impl_approve;
353
354 -- R12 ERES Support in Service Family. Bug 4345768
355 -- START
356
357 -- Post Operation API for updating the Approval
358 -- status of the QA Results Occurrence.
359
360 PROCEDURE qa_occurrence_approve
361 (p_plan_id IN NUMBER,
362 p_collection_id IN NUMBER,
363 p_occurrence IN NUMBER
364 ) IS
365
366 l_status_code VARCHAR2(30);
367
368 BEGIN
369
370 -- Get the Signature status
371 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
372 l_status_code := g_approved;
373
374 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
375 l_status_code := g_rejected;
376
377 END IF;
378
379 -- Update the status returned by EDR
380 update_res_col(l_status_code,
381 p_plan_id,
382 p_collection_id,
383 p_occurrence);
384
385 END qa_occurrence_approve;
386
387 -- Post Operation API for updating the Approval
388 -- status of the QA Results Collection.
389
390 PROCEDURE qa_collection_approve
391 (p_plan_id IN NUMBER,
392 p_collection_id IN NUMBER
393 ) IS
394
395 l_status_code VARCHAR2(30);
396
397 BEGIN
398
399 -- Get the Signature status
400 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
401 l_status_code := g_approved;
402
403 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
404 l_status_code := g_rejected;
405
406 END IF;
407
408 -- Bug 5508639. SHKALYAN 13-Sep-2006.
409 -- Pass NULL as txn_header_id since processing is by collection
410
411 -- Update the status returned by EDR
412 update_collection_col(l_status_code,
413 p_plan_id,
414 p_collection_id,
415 NULL);
416
417 END qa_collection_approve;
418
419
420 -- END
421 -- R12 ERES Support in Service Family. Bug 4345768
422
423 -- Bug 5508639. SHKALYAN 13-Sep-2006.
424 -- Overloaded Post Operation API for updating the Approval
425 -- status of the QA Results Collection for a given txn_header_id.
426 PROCEDURE qa_collection_approve
427 (p_plan_id IN NUMBER,
428 p_collection_id IN NUMBER,
429 p_txn_header_id IN NUMBER
430 ) IS
431
432 l_status_code VARCHAR2(30);
433
434 BEGIN
435
436 -- Get the Signature status
437 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
438 l_status_code := g_approved;
439
440 ELSIF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'REJECTED') THEN
441 l_status_code := g_rejected;
442
443 END IF;
444
445 -- Update the status returned by EDR
446 update_collection_col(l_status_code,
447 p_plan_id,
448 p_collection_id,
449 p_txn_header_id);
450
451 END qa_collection_approve;
452
453
454 END QA_ERES_DEFER;
455
[Home] [Help]