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