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