[Home] [Help]
PACKAGE BODY: APPS.AHL_QA_RESULTS_PVT
Source
1 PACKAGE BODY AHL_QA_RESULTS_PVT AS
2 /* $Header: AHLVQARB.pls 120.6.12020000.2 2012/12/07 13:31:53 sareepar ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_QA_RESULTS_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 -- Added by Arvind for 11.5.10
8 G_MR_TXN_NO NUMBER := TO_NUMBER( FND_PROFILE.value( 'AHL_MR_QA_TXN_NO' ) );
9 G_JOB_TXN_NO NUMBER := 2001;
10 G_OP_TXN_NO NUMBER := 2002;
11 G_MRB_TXN_NO NUMBER := 2004;
12
13 FUNCTION validate_gqp_inputs
14 (
15 p_organization_id IN NUMBER,
16 p_transaction_number IN NUMBER,
17 p_col_trigger_value IN VARCHAR2
18 ) RETURN VARCHAR2
19 IS
20 BEGIN
21 IF ( p_organization_id IS NULL OR
22 p_organization_id = FND_API.G_MISS_NUM ) THEN
23 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_ORG_ID_NULL' );
24 FND_MSG_PUB.add;
25 RETURN FND_API.G_RET_STS_ERROR;
26 END IF;
27
28 IF ( p_transaction_number IS NULL OR
29 p_transaction_number = FND_API.G_MISS_NUM ) THEN
30 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_TXN_NO_NULL' );
31 FND_MSG_PUB.add;
32 RETURN FND_API.G_RET_STS_ERROR;
33 END IF;
34
35 IF ( p_col_trigger_value IS NULL OR
36 p_col_trigger_value = FND_API.G_MISS_NUM ) THEN
37 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_INSPECTION_TYPE_NULL' );
38 FND_MSG_PUB.add;
39 RETURN FND_API.G_RET_STS_ERROR;
40 END IF;
41
42 END validate_gqp_inputs;
43
44 FUNCTION validate_sqar_inputs
45 (
46 p_plan_id IN NUMBER,
47 p_organization_id IN NUMBER,
48 p_transaction_no IN NUMBER,
49 p_specification_id IN NUMBER,
50 p_results_tbl IN qa_results_tbl_type,
51 p_hidden_results_tbl IN qa_results_tbl_type,
52 p_context_tbl IN qa_context_tbl_type,
53 p_result_commit_flag IN NUMBER,
54 p_collection_id IN NUMBER
55 ) RETURN VARCHAR2
56 IS
57
58 TYPE chars_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
59
60 l_plan_chars chars_tbl_type;
61 l_organization_id NUMBER;
62 l_element_count NUMBER := 1;
63 L_MATCH_FOUND BOOLEAN := FALSE;
64 --l_mr_title VARCHAR2(80);
65 --rroy
66 -- ACL Changes
67 --l_return_status VARCHAR2(1);
68 l_workorder_id NUMBER;
69 l_unit_effectivity_id NUMBER;
70 L_WORKORDER_OPERATION_ID NUMBER;
71 --l_wo_name VARCHAR2(80);
72 --rroy
73 -- ACL Changes
74
75 L_TITLE VARCHAR2(240);
76 L_STATUS_CODE VARCHAR2(30);
77 l_unit_locked VARCHAR2(1);
78
79 CURSOR get_plan_from_results( c_collection_id NUMBER )
80 IS
81 SELECT plan_id
82 FROM QA_RESULTS
83 WHERE collection_id = c_collection_id;
84
85 CURSOR get_chars_for_plan( c_plan_id NUMBER )
86 IS
87 SELECT char_id
88 FROM QA_PLAN_CHARS
89 WHERE plan_id = c_plan_id;
90
91 --rroy
92 -- ACL Changes
93 CURSOR get_wo_details(c_wo_id NUMBER)
94 IS
95 SELECT WO.job_number,WO.job_status_code status_code,AHL_PRD_UTIL_PKG.Is_Unit_Locked(WO.workorder_id,null,null,null) UNIT_LOCKED
96 FROM AHL_SEARCH_WORKORDERS_V WO
97 WHERE WO.workorder_id = c_wo_id;
98
99 CURSOR get_op_details(c_op_id NUMBER)
100 IS
101 SELECT to_char(OPERATION_SEQUENCE_NUM) title,STATUS_CODE,AHL_PRD_UTIL_PKG.IS_UNIT_LOCKED(WOP.WORKORDER_ID,NULL,NULL,NULL) UNIT_LOCKED
102 FROM ahl_workorder_operations_v WOP
103 WHERE WORKORDER_OPERATION_ID = c_op_id;
104 --rroy
105 -- ACL Changes
106
107 --Changes by nsikka for Bug 5324101
108 --Cursor added to fetch UE Title to be passed as token
109
110 CURSOR ue_details_csr( p_unit_effectivity_id IN NUMBER )
111 IS
112 SELECT MR.MR_TITLE TITLE, MR.STATUS_CODE,AHL_PRD_UTIL_PKG.IS_UNIT_LOCKED(NULL,MR.UNIT_EFFECTIVITY_ID,NULL,NULL) UNIT_LOCKED
113 FROM AHL_MR_INSTANCES_V MR
114 WHERE MR.UNIT_EFFECTIVITY_ID = p_unit_effectivity_id;
115
116
117
118 BEGIN
119
120 IF ( p_plan_id IS NULL OR
121 p_plan_id = FND_API.G_MISS_NUM ) THEN
122 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_PLAN_ID_NULL' );
123 FND_MSG_PUB.add;
124 RETURN FND_API.G_RET_STS_ERROR;
125 END IF;
126
127 IF ( p_organization_id IS NULL OR
128 p_organization_id = FND_API.G_MISS_NUM ) THEN
129 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_ORG_ID_NULL' );
130 FND_MSG_PUB.add;
131 RETURN FND_API.G_RET_STS_ERROR;
132 END IF;
133
134 IF ( p_results_tbl.COUNT = 0 ) THEN
135 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_ONE_RESULT_REQD' );
136 FND_MSG_PUB.add;
137 RETURN FND_API.G_RET_STS_ERROR;
138 END IF;
139
140 BEGIN
141 SELECT organization_id
142 INTO l_organization_id
143 FROM QA_PLANS
144 WHERE plan_id = p_plan_id;
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_PLAN_NOT_FOUND' );
149 FND_MSG_PUB.add;
150 RETURN FND_API.G_RET_STS_ERROR;
151 END;
152
153 -- rroy
154 -- ACL Changes
155 FOR i IN 1..p_context_tbl.COUNT LOOP
156 IF(p_context_tbl(i).name = 'workorder_id') THEN
157 l_workorder_id := p_context_tbl(i).value;
158 ELSIF(p_context_tbl(i).name = 'unit_effectivity_id') THEN
159 l_unit_effectivity_id := p_context_tbl(i).value;
160 ELSIF(p_context_tbl(i).name = 'operation_id') THEN
161 l_workorder_operation_id := p_context_tbl(i).value;
162 END IF;
163 END LOOP;
164
165 IF p_transaction_no = G_MR_TXN_NO THEN
166 OPEN ue_details_csr(L_UNIT_EFFECTIVITY_ID);
167 FETCH ue_details_csr INTO L_TITLE, L_status_code, l_unit_locked;
168 CLOSE ue_details_csr;
169
170
171 IF l_unit_locked = FND_API.G_TRUE THEN
172 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_MR_QA_UNTLCKD');
173 FND_MESSAGE.Set_Token('MR_TITLE', L_TITLE);
174 FND_MSG_PUB.ADD;
175 RETURN FND_API.G_RET_STS_ERROR;
176 END IF;
177
178 IF(L_STATUS_CODE IN ('ACCOMPLISHED','DEFERRED','UNRELEASED','TERMINATED','CANCELLED','DEFERRAL_PENDING'))THEN
179 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_MR_QA_CAP_NALLWD');
180 FND_MESSAGE.Set_Token('TITLE', L_TITLE);
181 FND_MSG_PUB.ADD;
182 RETURN FND_API.G_RET_STS_ERROR;
183 END IF;
184 ELSIF p_transaction_no = G_JOB_TXN_NO THEN
185 OPEN get_wo_details(l_workorder_id);
186 FETCH get_wo_details INTO L_TITLE, L_status_code, l_unit_locked;
187 CLOSE get_wo_details;
188 IF l_unit_locked = FND_API.G_TRUE THEN
189
190 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_WO_QA_UNTLCKD');
191 FND_MSG_PUB.ADD;
192 RETURN FND_API.G_RET_STS_ERROR;
193 END IF;
194 IF(L_STATUS_CODE IN ('1','4','5','7','12','21'))THEN
195 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_WO_QA_CAP_NALLWD');
196 FND_MESSAGE.Set_Token('TITLE', L_TITLE);
197 FND_MSG_PUB.ADD;
198 RETURN FND_API.G_RET_STS_ERROR;
199 END IF;
200
201 ELSIF p_transaction_no = G_OP_TXN_NO THEN
202 OPEN get_op_details(l_workorder_operation_id);
203 FETCH get_op_details INTO L_TITLE, L_status_code, l_unit_locked;
204 CLOSE get_op_details;
205 IF l_unit_locked = FND_API.G_TRUE THEN
206
207 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_OP_QA_UNTLCKD');
208 FND_MSG_PUB.ADD;
209 RETURN FND_API.G_RET_STS_ERROR;
210 END IF;
211 IF(L_STATUS_CODE IN ('1'))THEN
212 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_OP_QA_CAP_NALLWD');
213 FND_MESSAGE.Set_Token('TITLE', L_TITLE);
214 FND_MSG_PUB.ADD;
215 RETURN FND_API.G_RET_STS_ERROR;
216 END IF;
217 END IF;
218
219
220
221 IF ( l_organization_id <> p_organization_id ) THEN
222 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_ORG_MISMATCH' );
223 FND_MSG_PUB.add;
224 RETURN FND_API.G_RET_STS_ERROR;
225 END IF;
226
227 IF ( p_collection_id IS NOT NULL AND
228 p_collection_id <> FND_API.G_MISS_NUM ) THEN
229 FOR results_cursor IN get_plan_from_results( p_collection_id ) LOOP
230 IF ( results_cursor.plan_id <> p_plan_id ) THEN
231 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_COLL_MANY_PLANS' );
232 FND_MSG_PUB.add;
233 CLOSE get_plan_from_results;
234 RETURN FND_API.G_RET_STS_ERROR;
235 END IF;
236
237 l_element_count := l_element_count + 1;
238 END LOOP;
239
240 IF ( l_element_count = 1 ) THEN
241 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_COLL_NOT_FOUND' );
242 FND_MSG_PUB.add;
243 RETURN FND_API.G_RET_STS_ERROR;
244 END IF;
245
246 END IF;
247
248 l_element_count := 1;
249
250 FOR chars_cursor IN get_chars_for_plan( p_plan_id ) LOOP
251 l_plan_chars( l_element_count ) := chars_cursor.char_id;
252 l_element_count := l_element_count + 1;
253 END LOOP;
254
255 FOR i IN 1..p_results_tbl.COUNT LOOP
256
257 FOR j IN 1..l_plan_chars.COUNT LOOP
258 IF ( l_plan_chars(j) = p_results_tbl(i).char_id ) THEN
259 l_match_found := TRUE;
260 --EXIT;
261 END IF;
262 END LOOP;
263
264 IF ( l_match_found = FALSE ) THEN
265 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_RESULTS_NO_PLAN_CHAR' );
266 FND_MSG_PUB.add;
267 RETURN FND_API.G_RET_STS_ERROR;
268 END IF;
269
270 l_match_found := FALSE;
271
272 END LOOP;
273
274 FOR i IN 1..p_hidden_results_tbl.COUNT LOOP
275
276 FOR j IN 1..l_plan_chars.COUNT LOOP
277 IF ( l_plan_chars(j) = p_hidden_results_tbl(i).char_id ) THEN
278 l_match_found := TRUE;
279 --EXIT;
280 END IF;
281 END LOOP;
282
283 IF ( l_match_found = FALSE ) THEN
284 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_HIDDEN_NO_PLAN_CHAR' );
285 FND_MSG_PUB.add;
286 RETURN FND_API.G_RET_STS_ERROR;
287 END IF;
288
289 l_match_found := FALSE;
290
291 END LOOP;
292
293 RETURN FND_API.G_RET_STS_SUCCESS;
294 END validate_sqar_inputs;
295
296 FUNCTION fire_ahl_actions
297 (
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2,
301 p_plan_id IN NUMBER,
302 p_transaction_no IN NUMBER,
303 p_results_tbl IN qa_results_tbl_type,
304 p_context_tbl IN qa_context_tbl_type,
305 p_collection_id IN NUMBER
306 ) RETURN VARCHAR2
307 IS
308 l_msg_data VARCHAR2(2000);
309 l_return_status VARCHAR2(1);
310 l_msg_count NUMBER;
311 l_unit_effectivity_id NUMBER := NULL;
312 l_workorder_id NUMBER := NULL;
313 l_object_version_number NUMBER := NULL;
314 l_workorder_operation_id NUMBER := NULL;
315
316 BEGIN
317
318 FOR i IN 1..p_context_tbl.COUNT LOOP
319 IF ( p_context_tbl(i).name = 'workorder_id' ) THEN
320 l_workorder_id := p_context_tbl(i).value;
321 ELSIF ( p_context_tbl(i).name = 'unit_effectivity_id' ) THEN
322 l_unit_effectivity_id := p_context_tbl(i).value;
323 ELSIF ( p_context_tbl(i).name = 'operation_id' ) THEN
324 l_workorder_operation_id := p_context_tbl(i).value;
325 ELSIF ( p_context_tbl(i).name = 'object_version_no' ) THEN
326 l_object_version_number := p_context_tbl(i).value;
327 END IF;
328 END LOOP;
329
330 -- Added by Arvind for 11.5.10
331 IF ( p_transaction_no = G_MR_TXN_NO ) THEN
332
333 IF ( l_unit_effectivity_id IS NULL OR
334 l_object_version_number IS NULL ) THEN
335 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_INVALID_UE_INPUTS' );
336 FND_MSG_PUB.add;
337 RETURN FND_API.G_RET_STS_ERROR;
338 END IF;
339
340 -- Change to call capture_mr_updates
341 UPDATE AHL_UNIT_EFFECTIVITIES_B
342 SET qa_collection_id = p_collection_id,
343 object_version_number = object_version_number + 1,
344 last_update_date = SYSDATE,
345 last_updated_by = FND_GLOBAL.user_id,
346 last_update_login = FND_GLOBAL.login_id
347 WHERE unit_effectivity_id = l_unit_effectivity_id
348 AND object_version_number = l_object_version_number;
349
350 IF ( SQL%ROWCOUNT = 0 ) THEN
351 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_UE_NOT_FOUND' );
352 FND_MSG_PUB.add;
353 RETURN FND_API.G_RET_STS_ERROR;
354 END IF;
355
356 ELSIF ( p_transaction_no = G_JOB_TXN_NO ) THEN
357
358 IF ( l_workorder_id IS NULL OR
359 l_object_version_number IS NULL ) THEN
360 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_INVALID_WO_INPUTS' );
361 FND_MSG_PUB.add;
362 RETURN FND_API.G_RET_STS_ERROR;
363 END IF;
364
365 UPDATE AHL_WORKORDERS
366 SET collection_id = p_collection_id,
367 object_version_number = object_version_number + 1,
368 last_update_date = SYSDATE,
369 last_updated_by = FND_GLOBAL.user_id,
370 last_update_login = FND_GLOBAL.login_id
371 WHERE workorder_id = l_workorder_id
372 AND object_version_number = l_object_version_number;
373
374 IF ( SQL%ROWCOUNT = 0 ) THEN
375 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_WO_NOT_FOUND' );
376 FND_MSG_PUB.add;
377 RETURN FND_API.G_RET_STS_ERROR;
378 END IF;
379
380 ELSIF ( p_transaction_no = G_OP_TXN_NO ) THEN
381
382 IF ( l_workorder_operation_id IS NULL OR
383 l_object_version_number IS NULL ) THEN
384 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_INVALID_COP_INPUTS' );
385 FND_MSG_PUB.add;
386 RETURN FND_API.G_RET_STS_ERROR;
387 END IF;
388
389 UPDATE AHL_WORKORDER_OPERATIONS
390 SET collection_id = p_collection_id,
391 object_version_number = object_version_number + 1,
392 last_update_date = SYSDATE,
393 last_updated_by = FND_GLOBAL.user_id,
394 last_update_login = FND_GLOBAL.login_id
395 WHERE workorder_operation_id = l_workorder_operation_id
396 AND object_version_number = l_object_version_number;
397
398 IF ( SQL%ROWCOUNT = 0 ) THEN
399 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_WO_OP_NOT_FOUND' );
400 FND_MSG_PUB.add;
401 RETURN FND_API.G_RET_STS_ERROR;
402 END IF;
403
404 ELSIF ( p_transaction_no = G_MRB_TXN_NO ) THEN
405 NULL;
406 ELSE
407 NULL;
408 END IF;
409
410 RETURN FND_API.G_RET_STS_SUCCESS;
411
412 END fire_ahl_actions;
413
414 FUNCTION form_hidden_qa_result_string
415 (
416 p_hidden_results_tbl IN qa_results_tbl_type,
417 p_results_tbl IN qa_results_tbl_type,
418 p_search_end_index IN NUMBER,
419 p_id_or_value IN VARCHAR2,
420 x_result_string OUT NOCOPY VARCHAR2
421 ) RETURN VARCHAR2
422 IS
423
424 l_end_index NUMBER;
425 l_skip_element BOOLEAN := FALSE;
426
427 BEGIN
428
429 l_end_index := p_hidden_results_tbl.COUNT;
430
431 FOR i IN 1..l_end_index LOOP
432 FOR j IN 1..p_search_end_index LOOP
433 IF ( p_hidden_results_tbl(i).char_id = p_results_tbl(j).char_id ) THEN
434 l_skip_element := TRUE;
435 EXIT;
436 END IF;
437 END LOOP;
438
439 IF ( l_skip_element = FALSE ) THEN
440 IF ( p_id_or_value = 'ID' ) THEN
441 x_result_string := x_result_string || p_hidden_results_tbl(i).char_id || '=' || p_hidden_results_tbl(i).result_id;
442 ELSE
443 x_result_string := x_result_string || p_hidden_results_tbl(i).char_id || '=' || p_hidden_results_tbl(i).result_value;
444 END IF;
445
446 IF ( i < l_end_index ) THEN
447 x_result_string := x_result_string || '@';
448 END IF;
449 ELSE
450 l_skip_element := FALSE;
451 END IF;
452
453 END LOOP;
454
455 RETURN FND_API.G_RET_STS_SUCCESS;
456 END form_hidden_qa_result_string;
457
458 FUNCTION form_qa_result_string
459 (
460 p_results_tbl IN qa_results_tbl_type,
461 p_start_index IN NUMBER,
462 p_end_index IN NUMBER,
463 p_id_or_value IN VARCHAR2,
464 x_result_string OUT NOCOPY VARCHAR2
465 ) RETURN VARCHAR2
466 IS
467
468 BEGIN
469
470 FOR i IN p_start_index..p_end_index LOOP
471 IF ( p_id_or_value = 'ID' ) THEN
472 x_result_string := x_result_string || p_results_tbl(i).char_id || '=' || p_results_tbl(i).result_id;
473 ELSE
474 x_result_string := x_result_string || p_results_tbl(i).char_id || '=' || p_results_tbl(i).result_value;
475 END IF;
476
477 IF ( i < p_end_index ) THEN
478 x_result_string := x_result_string || '@';
479 END IF;
480
481 END LOOP;
482
483 RETURN FND_API.G_RET_STS_SUCCESS;
484 END form_qa_result_string;
485
486 PROCEDURE submit_qa_results
487 (
488 p_api_version IN NUMBER := 1.0,
489 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
490 p_commit IN VARCHAR2 := FND_API.G_FALSE,
491 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
492 p_default IN VARCHAR2 := FND_API.G_FALSE,
493 p_module_type IN VARCHAR2 := NULL,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 p_plan_id IN NUMBER,
498 p_organization_id IN NUMBER,
499 p_transaction_no IN NUMBER,
500 p_specification_id IN NUMBER := NULL,
501 p_results_tbl IN qa_results_tbl_type,
502 p_hidden_results_tbl IN qa_results_tbl_type,
503 p_context_tbl IN qa_context_tbl_type,
504 p_result_commit_flag IN NUMBER,
505 p_id_or_value IN VARCHAR2 := 'VALUE',
506 p_x_collection_id IN OUT NOCOPY NUMBER,
507 p_x_occurrence_tbl IN OUT NOCOPY occurrence_tbl_type
508 )
509 IS
510 l_api_name VARCHAR2(30) := 'submit_qa_results';
511 l_msg_data VARCHAR2(2000);
512 l_return_status VARCHAR2(1);
513 l_msg_count NUMBER;
514 l_occurrence NUMBER := NULL;
515 l_qa_post_result INTEGER := -1;
516 l_result_string VARCHAR2(5000) := NULL;
517 l_hidden_result_string VARCHAR2(5000) := NULL;
518 l_enabled NUMBER;
519 l_committed NUMBER;
520 l_start_index NUMBER := 1;
521 l_transaction_no NUMBER;
522 BEGIN
523 -- Enable Debug (optional)
524 IF ( G_DEBUG = 'Y' ) THEN
525 AHL_DEBUG_PUB.enable_debug;
526 END IF;
527
528 -- Initialize API return status to success
529 x_return_status := FND_API.G_RET_STS_SUCCESS;
530
531 -- Standard Start of API savepoint
532 SAVEPOINT submit_qa_results_PVT;
533
534 -- Initialize message list if p_init_msg_list is set to TRUE.
535 IF FND_API.to_boolean( p_init_msg_list ) THEN
536 FND_MSG_PUB.initialize;
537 END IF;
538
539 l_return_status :=
540 validate_sqar_inputs
541 (
542 p_plan_id => p_plan_id,
543 p_organization_id => p_organization_id,
544 p_transaction_no => p_transaction_no,
545 p_specification_id => p_specification_id,
546 p_results_tbl => p_results_tbl,
547 p_hidden_results_tbl => p_hidden_results_tbl,
548 p_context_tbl => p_context_tbl,
549 p_result_commit_flag => p_result_commit_flag,
550 p_collection_id => p_x_collection_id
551 );
552
553 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
554 RAISE FND_API.G_EXC_ERROR;
555 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 END IF;
558
559 IF G_DEBUG = 'Y' THEN
560 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : After validating Inputs ' );
561 END IF;
562
563 IF ( p_x_collection_id IS NULL ) THEN
564 SELECT QA_COLLECTION_ID_S.NEXTVAL INTO p_x_collection_id FROM DUAL;
565
566 IF G_DEBUG = 'Y' THEN
567 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : After Getting Collection ID ' );
568 END IF;
569
570 END IF;
571
572 IF ( p_result_commit_flag = 1 ) THEN
573 l_enabled := 2;
574 l_committed := 1;
575 ELSE
576 l_enabled := 1;
577 l_committed := 2;
578 END IF;
579
580 l_return_status :=
581 form_hidden_qa_result_string
582 (
583 p_hidden_results_tbl => p_hidden_results_tbl,
584 p_results_tbl => p_results_tbl,
585 p_search_end_index => p_x_occurrence_tbl(1).element_count,
586 p_id_or_value => p_id_or_value,
587 x_result_string => l_hidden_result_string
588 );
589
590 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
591 RAISE FND_API.G_EXC_ERROR;
592 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596 IF G_DEBUG = 'Y' THEN
597 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : After Forming Hidden Result String : ' || l_hidden_result_string );
598 END IF;
599
600 FOR i IN 1..p_x_occurrence_tbl.COUNT LOOP
601
602 l_return_status :=
603 form_qa_result_string
604 (
605 p_results_tbl => p_results_tbl,
606 p_start_index => l_start_index,
607 p_end_index => l_start_index + p_x_occurrence_tbl(i).element_count -1,
608 p_id_or_value => p_id_or_value,
609 x_result_string => l_result_string
610 );
611
612 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
613 RAISE FND_API.G_EXC_ERROR;
614 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
616 END IF;
617
618 IF G_DEBUG = 'Y' THEN
619 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : After Forming Result String : ' || l_result_string || ' for row : ' || i );
620 END IF;
621
622 l_start_index := l_start_index + p_x_occurrence_tbl(i).element_count;
623
624 IF ( l_hidden_result_string IS NOT NULL ) THEN
625 l_result_string := l_result_string || '@' || l_hidden_result_string;
626 END IF;
627
628 IF ( p_transaction_no IS NULL OR
629 p_transaction_no = FND_API.G_MISS_NUM ) THEN
630 l_qa_post_result :=
631 QA_SS_RESULTS.nontxn_post_result
632 (
633 x_occurrence => l_occurrence,
634 x_org_id => p_organization_id,
635 x_plan_id => p_plan_id,
636 x_spec_id => p_specification_id,
637 x_collection_id => p_x_collection_id,
638 x_result => NULL,
639 x_result1 => l_result_string,
640 x_result2 => NULL,
641 x_enabled => l_enabled,
642 x_committed => l_committed,
643 x_messages => l_msg_data
644 );
645
646 IF G_DEBUG = 'Y' THEN
647 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : After Invoking nontxnt_post_result ' );
648 END IF;
649 IF(l_msg_data IS NOT NULL)THEN
650 x_msg_data := l_msg_data || x_msg_data;
651 x_msg_count := 1;
652 END IF;
653
654 ELSE
655 -- Added by Arvind for 11.5.10
656 -- Remove once MR Txn is provided by QA
657 IF ( p_transaction_no = G_MR_TXN_NO ) THEN
658 l_transaction_no := G_JOB_TXN_NO;
659 ELSE
660 l_transaction_no := p_transaction_no;
661 END IF;
662
663 l_qa_post_result :=
664 QA_SS_RESULTS.post_result
665 (
666 x_occurrence => l_occurrence,
667 x_org_id => p_organization_id,
668 x_plan_id => p_plan_id,
669 x_spec_id => p_specification_id,
670 x_collection_id => p_x_collection_id,
671 x_result => l_result_string,
672 x_result1 => NULL,
673 x_result2 => NULL,
674 x_enabled => l_enabled,
675 x_committed => l_committed,
676 x_transaction_number => l_transaction_no,
677 x_messages => l_msg_data
678 );
679
680 IF G_DEBUG = 'Y' THEN
681 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : After Invoking post_result ' || l_msg_data);
682 END IF;
683 IF(l_msg_data IS NOT NULL)THEN
684 x_msg_data := l_msg_data || x_msg_data;
685 x_msg_count := 1;
686 END IF;
687 END IF;
688
689 IF ( l_qa_post_result = -1 ) THEN
690
691 IF G_DEBUG = 'Y' THEN
692 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Post Result Failed ' );
693 END IF;
694
695 IF ( l_msg_data IS NULL ) THEN
696 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_QA_POST_UNEXP_ERROR' );
697 FND_MSG_PUB.add;
698 RAISE FND_API.G_EXC_ERROR;
699 ELSE
700 x_return_status := FND_API.G_RET_STS_ERROR;
701 x_msg_data := l_msg_data;
702 x_msg_count := 1;
703 RETURN;
704 END IF;
705 END IF;
706
707 p_x_occurrence_tbl(i).occurrence := l_occurrence;
708 l_occurrence := NULL;
709 l_result_string := NULL;
710
711 IF ( l_committed = 1 ) THEN
712 -- Reset Save Point because a Commit Occurs
713 SAVEPOINT submit_qa_results_PVT;
714 END IF;
715
716 END LOOP;
717
718 IF ( p_transaction_no IS NOT NULL ) THEN
719 l_return_status :=
720 fire_ahl_actions
721 (
722 x_return_status => l_return_status,
723 x_msg_count => l_msg_count,
724 x_msg_data => l_msg_data,
725 p_plan_id => p_plan_id,
726 p_transaction_no => p_transaction_no,
727 p_results_tbl => p_results_tbl,
728 p_context_tbl => p_context_tbl,
729 p_collection_id => p_x_collection_id
730 );
731
732 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
733 IF ( l_msg_data IS NOT NULL ) THEN
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 x_msg_data := l_msg_data;
736 x_msg_count := 1;
737 RETURN;
738 ELSE
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
743 END IF;
744
745 IF G_DEBUG = 'Y' THEN
746 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : After Firing AHL Actions ' );
747 END IF;
748 END IF;
749
750 -- Fix for bug# 5501482.
751 --IF ( l_committed = 1 ) THEN
752 IF ( l_committed = 2 ) THEN
753 --IF ( l_committed = 2 AND FND_API.to_boolean( p_commit ) ) THEN
754 QA_SEQUENCE_API.generate_seq_for_Txn
755 (
756 p_collection_id => p_x_collection_id,
757 p_return_status => l_return_status
758 );
759 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
760 RAISE FND_API.G_EXC_ERROR;
761 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
763 END IF;
764 QA_SS_RESULTS.wrapper_fire_action
765 (
766 q_collection_id => p_x_collection_id,
767 q_return_status => l_return_status,
768 q_msg_count => l_msg_count,
769 q_msg_data => l_msg_data
770 );
771
772 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
773 IF ( l_msg_data IS NULL ) THEN
774 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_QA_ACTION_UNEXP_ERROR' );
775 FND_MSG_PUB.add;
776 RAISE FND_API.G_EXC_ERROR;
777 ELSE
778 x_return_status := FND_API.G_RET_STS_ERROR;
779 x_msg_data := l_msg_data;
780 x_msg_count := 1;
781 RETURN;
782 END IF;
783 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
785 END IF;
786
787 IF G_DEBUG = 'Y' THEN
788 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : After Firing QA Actions ' || l_msg_data );
789 END IF;
790 END IF;
791 -- Fix for bug#5501482
792
793 IF G_DEBUG = 'Y' THEN
794 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : returned message: ' || x_msg_data );
795 END IF;
796
797 -- Perform the Commit (if requested)
798 IF ( FND_API.to_boolean( p_commit ) ) THEN
799 COMMIT WORK;
800 END IF;
801
802
803 IF ( G_DEBUG = 'Y' ) THEN
804 AHL_DEBUG_PUB.disable_debug;
805 END IF;
806
807 EXCEPTION
808
809 WHEN FND_API.G_EXC_ERROR THEN
810 ROLLBACK TO submit_qa_results_PVT;
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 FND_MSG_PUB.count_and_get
813 (
814 p_encoded => FND_API.G_FALSE,
815 p_count => x_msg_count,
816 p_data => x_msg_data
817 );
818
819 IF ( G_DEBUG = 'Y' ) THEN
820 AHL_DEBUG_PUB.disable_debug;
821 END IF;
822
823 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
824 ROLLBACK TO submit_qa_results_PVT;
825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
826 FND_MSG_PUB.count_and_get
827 (
828 p_encoded => FND_API.G_FALSE,
829 p_count => x_msg_count,
830 p_data => x_msg_data
831 );
832
833 IF ( G_DEBUG = 'Y' ) THEN
834 AHL_DEBUG_PUB.disable_debug;
835 END IF;
836
837 WHEN OTHERS THEN
838 ROLLBACK TO submit_qa_results_PVT;
839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
840 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
841 THEN
842 FND_MSG_PUB.add_exc_msg
843 (
844 p_pkg_name => G_PKG_NAME,
845 p_procedure_name => l_api_name,
846 p_error_text => SUBSTRB(SQLERRM,1,240)
847 );
848 END IF;
849 FND_MSG_PUB.count_and_get
850 (
851 p_encoded => FND_API.G_FALSE,
852 p_count => x_msg_count,
853 p_data => x_msg_data
854 );
855
856 IF ( G_DEBUG = 'Y' ) THEN
857 AHL_DEBUG_PUB.disable_debug;
858 END IF;
859
860 END submit_qa_results;
861
862 PROCEDURE get_char_lov_sql
863 (
864 p_api_version IN NUMBER := 1.0,
865 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
866 p_commit IN VARCHAR2 := FND_API.G_FALSE,
867 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
868 p_default IN VARCHAR2 := FND_API.G_FALSE,
869 p_module_type IN VARCHAR2 := NULL,
870 x_return_status OUT NOCOPY VARCHAR2,
871 x_msg_count OUT NOCOPY NUMBER,
872 x_msg_data OUT NOCOPY VARCHAR2,
873 p_plan_id IN NUMBER,
874 p_char_id IN NUMBER,
875 p_organization_id IN NUMBER,
876 p_user_id IN NUMBER := NULL,
877 p_depen1 IN VARCHAR2 := NULL,
878 p_depen2 IN VARCHAR2 := NULL,
879 p_depen3 IN VARCHAR2 := NULL,
880 p_value IN VARCHAR2 := NULL,
881 x_char_lov_sql OUT NOCOPY VARCHAR2
882 )
883 IS
884 l_api_name VARCHAR2(30) := 'get_char_lov_sql';
885 BEGIN
886
887 -- Enable Debug (optional)
888 IF ( G_DEBUG = 'Y' ) THEN
889 AHL_DEBUG_PUB.enable_debug;
890 END IF;
891
892 -- Initialize API return status to success
893 x_return_status := FND_API.G_RET_STS_SUCCESS;
894
895 -- Initialize message list if p_init_msg_list is set to TRUE.
896 IF FND_API.to_boolean( p_init_msg_list ) THEN
897 FND_MSG_PUB.initialize;
898 END IF;
899
900 x_char_lov_sql :=
901 QA_SS_LOV_API.get_lov_sql
902 (
903 plan_id => p_plan_id,
904 char_id => p_char_id,
905 org_id => p_organization_id,
906 user_id => p_user_id,
907 depen1 => p_depen1,
908 depen2 => p_depen2,
909 depen3 => p_depen3,
910 value => p_value
911 ) || '::' ||
912 QA_SS_LOV_API.get_lov_bind_values
913 (
914 plan_id => p_plan_id,
915 char_id => p_char_id,
916 org_id => p_organization_id,
917 user_id => p_user_id,
918 depen1 => p_depen1,
919 depen2 => p_depen2,
920 depen3 => p_depen3,
921 value => p_value
922 );
923
924 -- Error handling code added by balaji for bug # 4091726
925 IF FND_MSG_PUB.count_msg > 0 THEN
926 RAISE FND_API.G_EXC_ERROR;
927 END IF;
928
929 IF ( x_char_lov_sql IS NULL ) THEN
930 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_QA_CHAR_LOV_NULL' );
931 FND_MSG_PUB.add;
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934
935 IF ( G_DEBUG = 'Y' ) THEN
936 AHL_DEBUG_PUB.disable_debug;
937 END IF;
938
939 EXCEPTION
940
941 WHEN FND_API.G_EXC_ERROR THEN
942 x_return_status := FND_API.G_RET_STS_ERROR;
943 FND_MSG_PUB.count_and_get
944 (
945 p_encoded => FND_API.G_FALSE,
946 p_count => x_msg_count,
947 p_data => x_msg_data
948 );
949
950 IF ( G_DEBUG = 'Y' ) THEN
951 AHL_DEBUG_PUB.disable_debug;
952 END IF;
953
954 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
956 FND_MSG_PUB.count_and_get
957 (
958 p_encoded => FND_API.G_FALSE,
959 p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962
963 IF ( G_DEBUG = 'Y' ) THEN
964 AHL_DEBUG_PUB.disable_debug;
965 END IF;
966
967 WHEN OTHERS THEN
968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
969 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
970 THEN
971 FND_MSG_PUB.add_exc_msg
972 (
973 p_pkg_name => G_PKG_NAME,
974 p_procedure_name => l_api_name,
975 p_error_text => SUBSTRB(SQLERRM,1,240)
976 );
977 END IF;
978 FND_MSG_PUB.count_and_get
979 (
980 p_encoded => FND_API.G_FALSE,
981 p_count => x_msg_count,
982 p_data => x_msg_data
983 );
984
985 IF ( G_DEBUG = 'Y' ) THEN
986 AHL_DEBUG_PUB.disable_debug;
987 END IF;
988 END get_char_lov_sql;
989
990 PROCEDURE get_qa_plan
991 (
992 p_api_version IN NUMBER := 1.0,
993 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
994 p_commit IN VARCHAR2 := FND_API.G_FALSE,
995 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
996 p_default IN VARCHAR2 := FND_API.G_FALSE,
997 p_module_type IN VARCHAR2 := NULL,
998 x_return_status OUT NOCOPY VARCHAR2,
999 x_msg_count OUT NOCOPY NUMBER,
1000 x_msg_data OUT NOCOPY VARCHAR2,
1001 p_organization_id IN NUMBER,
1002 p_transaction_number IN NUMBER,
1003 p_col_trigger_value IN VARCHAR2,
1004 x_plan_id OUT NOCOPY NUMBER
1005 )
1006 IS
1007 l_api_name VARCHAR2(30) := 'get_qa_plan';
1008 BEGIN
1009
1010 -- Enable Debug (optional)
1011 IF ( G_DEBUG = 'Y' ) THEN
1012 AHL_DEBUG_PUB.enable_debug;
1013 END IF;
1014
1015 -- Initialize API return status to success
1016 x_return_status := FND_API.G_RET_STS_SUCCESS;
1017
1018 -- Initialize message list if p_init_msg_list is set to TRUE.
1019 IF FND_API.to_boolean( p_init_msg_list ) THEN
1020 FND_MSG_PUB.initialize;
1021 END IF;
1022
1023 BEGIN
1024
1025 SELECT QP.plan_id
1026 INTO x_plan_id
1027 FROM QA_PLANS_VAL_V QP,
1028 QA_PLAN_TRANSACTIONS QPT,
1029 QA_PLAN_COLLECTION_TRIGGERS QPCT
1030 WHERE QP.plan_id = QPT.plan_id
1031 AND QPT.plan_transaction_id = QPCT.plan_transaction_id
1032 AND QP.organization_id = p_organization_id
1033 AND QPT.transaction_number = p_transaction_number
1034 AND QPCT.collection_trigger_id = 87
1035 AND QPCT.low_value = p_col_trigger_value;
1036
1037 EXCEPTION
1038 WHEN NO_DATA_FOUND THEN
1039 x_plan_id := NULL;
1040 IF ( p_module_type = 'JSP' ) THEN
1041 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_QA_PLAN_NOT_FOUND' );
1042 FND_MSG_PUB.add;
1043 RAISE FND_API.G_EXC_ERROR;
1044 ELSE
1045 RETURN;
1046 END IF;
1047 WHEN OTHERS THEN
1048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049 END;
1050
1051 IF ( G_DEBUG = 'Y' ) THEN
1052 AHL_DEBUG_PUB.disable_debug;
1053 END IF;
1054
1055 EXCEPTION
1056
1057 WHEN FND_API.G_EXC_ERROR THEN
1058 x_return_status := FND_API.G_RET_STS_ERROR;
1059 FND_MSG_PUB.count_and_get
1060 (
1061 p_encoded => FND_API.G_FALSE,
1062 p_count => x_msg_count,
1063 p_data => x_msg_data
1064 );
1065
1066 IF ( G_DEBUG = 'Y' ) THEN
1067 AHL_DEBUG_PUB.disable_debug;
1068 END IF;
1069
1070 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1072 FND_MSG_PUB.count_and_get
1073 (
1074 p_encoded => FND_API.G_FALSE,
1075 p_count => x_msg_count,
1076 p_data => x_msg_data
1077 );
1078
1079 IF ( G_DEBUG = 'Y' ) THEN
1080 AHL_DEBUG_PUB.disable_debug;
1081 END IF;
1082
1083 WHEN OTHERS THEN
1084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1085 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1086 THEN
1087 FND_MSG_PUB.add_exc_msg
1088 (
1089 p_pkg_name => G_PKG_NAME,
1090 p_procedure_name => l_api_name,
1091 p_error_text => SUBSTRB(SQLERRM,1,240)
1092 );
1093 END IF;
1094 FND_MSG_PUB.count_and_get
1095 (
1096 p_encoded => FND_API.G_FALSE,
1097 p_count => x_msg_count,
1098 p_data => x_msg_data
1099 );
1100
1101 IF ( G_DEBUG = 'Y' ) THEN
1102 AHL_DEBUG_PUB.disable_debug;
1103 END IF;
1104 END get_qa_plan;
1105
1106 END AHL_QA_RESULTS_PVT;