[Home] [Help]
PACKAGE BODY: APPS.PON_TEAM_SCORING_UTIL_PVT
Source
1 PACKAGE BODY PON_TEAM_SCORING_UTIL_PVT AS
2 /*$Header: PONVSTUB.pls 120.7 2007/09/19 08:34:43 mshujath ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_TEAM_SCORING_UTIL_PVT';
5
6 --------------------------------------------------------------------------------
7 -- Private procedure/function definitions --
8 --------------------------------------------------------------------------------
9
10 --------------------------------------------------------------------------------
11 -- Public procedure/function definition --
12 --------------------------------------------------------------------------------
13
14 --------------------------------------------------------------------------------
15 --- lock_scoring --
16 --------------------------------------------------------------------------------
17
18 PROCEDURE lock_scoring(
19 p_api_version IN NUMBER
20 ,p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE
21 ,p_tpc_id IN pon_auction_headers_all.trading_partner_contact_id%TYPE
22 ,x_return_status OUT NOCOPY VARCHAR2
23 ,x_msg_data OUT NOCOPY VARCHAR2
24 ,x_msg_count OUT NOCOPY NUMBER
25 ) IS
26
27
28 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_SCORING';
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_stage VARCHAR2(50);
31
32
33
34 BEGIN
35
36 -- Check for API comptability
37 l_stage := '10: API check';
38
39 IF fnd_api.compatible_api_call(
40 p_current_version_number => l_api_version
41 ,p_caller_version_number => p_api_version
42 ,p_api_name => l_api_name
43 ,p_pkg_name => g_pkg_name)
44 THEN
45 NULL;
46 ELSE
47 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
48 END IF;
49
50 -- update auction header if input parameters are not null
51 l_stage := '20: updates begin here';
52
53 IF (p_auction_header_id IS NULL) OR (p_tpc_id IS NULL) THEN
54 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
55 END IF;
56
57 -- update auction header
58 l_stage := '30: update auction header';
59
60 UPDATE pon_auction_headers_all
61 SET scoring_lock_date = SYSDATE
62 ,scoring_lock_tp_contact_id = p_tpc_id
63 ,last_update_date = SYSDATE
64 ,last_updated_by = fnd_global.user_id
65 WHERE auction_header_id = p_auction_header_id
66 AND scoring_lock_date IS NULL;
67
68 IF SQL%NOTFOUND THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72 l_stage := '35: update the pon bid attribute values table';
73 MERGE
74 INTO pon_bid_attribute_values pbav
75 USING
76 (
77 SELECT
78 ptmas.auction_header_id
79 ,ptmas.bid_number
80 ,paa.attribute_name
81 ,paa.datatype
82 ,null value
83 ,SYSDATE creation_date
84 ,fnd_global.user_id created_by
85 ,SYSDATE last_update_date
86 ,fnd_global.user_id updated_by
87 ,AVG(ptmas.score) score
88 ,ptmas.attribute_sequence_number
89 ,paa.attr_level
90 ,paa.attr_max_score
91 ,paa.weight
92 ,paa.attr_group_seq_number
93 ,paa.attr_disp_seq_number
94 ,null old_value
95 FROM pon_team_member_attr_scores ptmas
96 ,pon_auction_attributes paa
97 ,pon_team_member_bid_scores ptmbs
98 ,pon_bid_headers pbh
99 ,pon_auction_headers_all paha
100 ,pon_auction_sections pas
101 WHERE ptmas.auction_header_id = p_auction_header_id
102 AND ptmas.auction_header_id = paa.auction_header_id
103 AND paa.line_number = -1
104 AND paa.attribute_list_id = -1
105 AND paa.scoring_method = 'MANUAL'
106 AND NVL(paa.display_only_flag, 'N') = 'N'
107 AND ptmas.attribute_sequence_number = paa.sequence_number
108 AND ptmbs.bid_number = ptmas.bid_number
109 AND ptmbs.user_id = ptmas.user_id
110 AND ptmbs.score_status = 'SUBMIT'
111 AND ptmas.score IS NOT NULL
112 AND pbh.auction_header_id = ptmas.auction_header_id
113 AND ptmas.bid_number = pbh.bid_number
114 AND pbh.bid_status = 'ACTIVE'
115 AND paha.auction_header_id = paa.auction_header_id
116 AND pas.auction_header_id = paa.auction_header_id
117 AND pas.attr_group_seq_number = paa.attr_group_seq_number
118 AND pas.section_name = paa.section_name
119 AND (NVL(paha.two_part_flag,'N') = 'N' OR
120 (paha.two_part_flag = 'Y' AND paha.sealed_auction_status = 'LOCKED') OR
121 (paha.two_part_flag = 'Y' AND paha.sealed_auction_status <> 'LOCKED' AND pas.two_part_section_type = 'COMMERCIAL'))
122 GROUP BY ptmas.auction_header_id
123 ,ptmas.bid_number
124 ,ptmas.attribute_sequence_number
125 ,paa.attribute_name
126 ,paa.datatype
127 ,SYSDATE
128 ,fnd_global.user_id
129 ,SYSDATE
130 ,fnd_global.user_id
131 ,paa.attr_level
132 ,paa.attr_max_score
133 ,paa.weight
134 ,paa.attr_group_seq_number
135 ,paa.attr_disp_seq_number
136 )ptmavg
137 ON
138 (ptmavg.auction_header_id = pbav.auction_header_id
139 AND ptmavg.bid_number = pbav.bid_number
140 AND ptmavg.attribute_name = pbav.attribute_name
141 AND pbav.auction_line_number = -1
142 )
143 WHEN MATCHED
144 THEN
145 UPDATE --update score and weighted score on required and optional attributes
146 SET pbav.score = ptmavg.score
147 ,pbav.weighted_score = ((ptmavg.score*ptmavg.weight)/NVL(ptmavg.attr_max_score, 0))
148 WHEN NOT MATCHED
149 THEN
150 INSERT -- internal attributes
151 (
152 pbav.auction_header_id
153 ,pbav.auction_line_number
154 ,pbav.bid_number
155 ,pbav.line_number
156 ,pbav.attribute_name
157 ,pbav.datatype
158 ,pbav.value
159 ,pbav.creation_date
160 ,pbav.created_by
161 ,pbav.last_update_date
162 ,pbav.last_updated_by
163 ,pbav.score
164 ,pbav.sequence_number
165 ,pbav.attr_level
166 ,pbav.weighted_score
167 ,pbav.attr_group_seq_number
168 ,pbav.attr_disp_seq_number
169 ,pbav.old_value
170 )
171 VALUES
172 (
173 ptmavg.auction_header_id
174 ,-1
175 ,ptmavg.bid_number
176 ,-1
177 ,ptmavg.attribute_name
178 ,ptmavg.datatype
179 ,null
180 ,SYSDATE -- creation_date
181 ,fnd_global.user_id -- created_by
182 ,SYSDATE -- last_update_date
183 ,fnd_global.user_id -- updated_by
184 ,ptmavg.score -- Calculated member Average Score
185 ,ptmavg.attribute_sequence_number
186 ,ptmavg.attr_level
187 ,((ptmavg.score*ptmavg.weight)/NVL(ptmavg.attr_max_score, 0)) -- calculated weighted score
188 ,ptmavg.attr_group_seq_number
189 ,ptmavg.attr_disp_seq_number
190 ,null
191 );
192
193
194 x_return_status := fnd_api.G_RET_STS_SUCCESS;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
199 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
200 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
201 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
202 fnd_log.string(log_level => fnd_log.level_unexpected
203 ,module => g_pkg_name ||'.'||l_api_name
204 ,message => l_stage || ': ' || SQLERRM);
205 fnd_log.string(log_level=>fnd_log.level_unexpected
206 ,module =>g_pkg_name ||'.'||l_api_name
207 ,message => 'Input parameter list: ' );
208 fnd_log.string(log_level=>fnd_log.level_unexpected
209 ,module =>g_pkg_name ||'.'||l_api_name
210 ,message => 'Auction Header Id :'||p_auction_header_id);
211 fnd_log.string(log_level=>fnd_log.level_unexpected
212 ,module =>g_pkg_name ||'.'||l_api_name
213 ,message => 'Trading Partner Contact Id'|| p_tpc_id );
214 fnd_log.string(log_level=>fnd_log.level_unexpected
215 ,module =>g_pkg_name ||'.'||l_api_name
216 ,message => 'p_api_version: '||p_api_version );
217 END IF;
218 END IF;
219 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
220 ,p_data => x_msg_data);
221 END;
222
223 --------------------------------------------------------------------------------
224 --- delete_member_scores --
225 --------------------------------------------------------------------------------
226
227 PROCEDURE delete_member_scores(
228 p_api_version IN NUMBER
229 ,p_auction_header_id IN
230 pon_auction_headers_all.auction_header_id%TYPE
231 ,p_team_id IN pon_scoring_teams.team_id%TYPE
232 ,p_user_id IN fnd_user.user_id%TYPE
233 ,x_return_status OUT NOCOPY VARCHAR2
234 ,x_msg_data OUT NOCOPY VARCHAR2
235 ,x_msg_count OUT NOCOPY NUMBER
236 )IS
237
238 l_api_name CONSTANT VARCHAR2(30) := 'DELTE_MEMBER_SCORES';
239 l_api_version CONSTANT NUMBER := 1.0;
240 l_stage VARCHAR2(50);
241
242
243
244 BEGIN
245
246 -- Check for API comptability
247 l_stage := '10: API check';
248
249 IF fnd_api.compatible_api_call(
250 p_current_version_number => l_api_version
251 ,p_caller_version_number => p_api_version
252 ,p_api_name => l_api_name
253 ,p_pkg_name => g_pkg_name)
254 THEN
255 NULL;
256 ELSE
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 END IF;
259
260 -- delete scores if input parameters are not null
261 l_stage := '20: deletes begin here';
262
263 IF (p_auction_header_id IS NULL)
264 OR (p_user_id IS NULL)
265 OR (p_team_id IS NULL) THEN
266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
267 END IF;
268
269
270 -- delete child record
271 DELETE FROM pon_team_member_attr_scores
272 WHERE auction_header_id = p_auction_header_id
273 AND user_id = p_user_id
274 AND attribute_sequence_number IN
275 (SELECT paa.sequence_number
276 FROM pon_auction_attributes paa
277 ,pon_auction_sections pas
278 ,pon_scoring_team_sections psts
279 WHERE paa.auction_header_id = pas.auction_header_id
280 AND paa.attr_group_seq_number = pas.attr_group_seq_number
281 AND pas.auction_header_id = psts.auction_header_id
282 AND pas.section_id = psts.section_id
283 AND psts.team_id = p_team_id);
284
285
286 -- if no rows exist in the pon_team_member_attribute_scores
287 -- for this user
288 -- for a bid
289 -- delete that bid and user from the pon_team_member_bid_scores
290 DELETE FROM pon_team_member_bid_scores ptmbs
291 WHERE ptmbs.auction_header_id = p_auction_header_id
292 AND ptmbs.user_id = p_user_id
293 AND NOT EXISTS
294 (SELECT 'x'
295 FROM pon_team_member_attr_scores ptmas
296 WHERE ptmas.auction_header_id = ptmbs.auction_header_id
297 AND ptmas.user_id = ptmbs.user_id);
298
299 x_return_status := fnd_api.G_RET_STS_SUCCESS;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
304 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
305 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
306 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
307 fnd_log.string(log_level => fnd_log.level_unexpected
308 ,module => g_pkg_name ||'.'||l_api_name
309 ,message => l_stage || ': ' || SQLERRM);
310 fnd_log.string(log_level=>fnd_log.level_unexpected
311 ,module =>g_pkg_name ||'.'||l_api_name
312 ,message => 'Input parameter list: ' );
313 fnd_log.string(log_level=>fnd_log.level_unexpected
314 ,module =>g_pkg_name ||'.'||l_api_name
315 ,message => 'Auction Header Id :'||p_auction_header_id);
316 fnd_log.string(log_level=>fnd_log.level_unexpected
317 ,module =>g_pkg_name ||'.'||l_api_name
318 ,message => 'user_id'|| p_user_id );
319 fnd_log.string(log_level=>fnd_log.level_unexpected
320 ,module =>g_pkg_name ||'.'||l_api_name
321 ,message => 'p_api_version: '||p_api_version );
322 END IF;
323 END IF;
324 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
325 ,p_data => x_msg_data);
326 END;
327
328
329 --------------------------------------------------------------------------------
330 --- delete_team_scores --
331 --------------------------------------------------------------------------------
332
333 PROCEDURE delete_team_scores(
334 p_api_version IN NUMBER
335 ,p_auction_header_id IN
336 pon_auction_headers_all.auction_header_id%TYPE
337 ,p_team_id IN pon_scoring_teams.team_id%TYPE
338 ,x_return_status OUT NOCOPY VARCHAR2
339 ,x_msg_data OUT NOCOPY VARCHAR2
340 ,x_msg_count OUT NOCOPY NUMBER
341 )IS
342
343 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TEAM_SCORES';
344 l_api_version CONSTANT NUMBER := 1.0;
345 l_stage VARCHAR2(50);
346
347
348
349 BEGIN
350
351 -- Check for API comptability
352 l_stage := '10: API check';
353
354 IF fnd_api.compatible_api_call(
355 p_current_version_number => l_api_version
356 ,p_caller_version_number => p_api_version
357 ,p_api_name => l_api_name
358 ,p_pkg_name => g_pkg_name)
359 THEN
360 NULL;
361 ELSE
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
363 END IF;
364
365 -- delete scores if input parameters are not null
366 l_stage := '20: deletes begin here';
367
368 IF (p_auction_header_id IS NULL) OR (p_team_id IS NULL) THEN
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370 END IF;
371
372 -- delete child record
373 DELETE FROM pon_team_member_attr_scores ptmas
374 WHERE ptmas.auction_header_id = p_auction_header_id
375 AND (user_id, attribute_sequence_number) IN
376 (SELECT pstm.user_id, paa.sequence_number
377 FROM pon_auction_attributes paa
378 ,pon_auction_sections pas
379 ,pon_scoring_team_sections psts
380 ,pon_scoring_team_members pstm
381 WHERE paa.auction_header_id = pas.auction_header_id
382 AND paa.attr_group_seq_number = pas.attr_group_seq_number
383 AND paa.attribute_list_id = -1
384 AND paa.line_number = -1
385 AND pas.auction_header_id = psts.auction_header_id
386 AND pas.section_id = psts.section_id
387 AND psts.team_id = p_team_id
388 AND pstm.team_id = psts.team_id
389 AND pstm.auction_header_id = psts.auction_header_id);
390
391
392
393 -- if no rows exist in the pon_team_member_attribute_scores
394 -- for the users of this team and
395 -- for a bid
396 -- delete that bid and user from the pon_team_member_bid_scores
397 DELETE
398 FROM pon_team_member_bid_scores ptmbs
399 WHERE ptmbs.auction_header_id = p_auction_header_id
400 AND ptmbs.user_id IN
401 (
402 SELECT -- for all users of this team
403 user_id
404 FROM pon_scoring_team_members pstm
405 WHERE pstm.auction_header_id = ptmbs.auction_header_id
406 AND pstm.team_id = p_team_id
407 )
408 AND NOT EXISTS -- where there is no row for a bid in the child table
409 (
410 SELECT
411 'x'
412 FROM pon_team_member_attr_scores ptmas
413 WHERE ptmas.auction_header_id = ptmbs.auction_header_id
414 AND ptmas.bid_number = ptmbs.bid_number
415 AND ptmas.user_id = ptmbs.user_id
416 );
417
418 x_return_status := fnd_api.G_RET_STS_SUCCESS;
419
420 EXCEPTION
421 WHEN OTHERS THEN
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
423 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
424 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
425 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
426 fnd_log.string(log_level => fnd_log.level_unexpected
427 ,module => g_pkg_name ||'.'||l_api_name
428 ,message => l_stage || ': ' || SQLERRM);
432 fnd_log.string(log_level=>fnd_log.level_unexpected
429 fnd_log.string(log_level=>fnd_log.level_unexpected
430 ,module =>g_pkg_name ||'.'||l_api_name
431 ,message => 'Input parameter list: ' );
433 ,module =>g_pkg_name ||'.'||l_api_name
434 ,message => 'Auction Header Id :'||p_auction_header_id);
435 fnd_log.string(log_level=>fnd_log.level_unexpected
436 ,module =>g_pkg_name ||'.'||l_api_name
437 ,message => 'team_id'|| p_team_id );
438 fnd_log.string(log_level=>fnd_log.level_unexpected
439 ,module =>g_pkg_name ||'.'||l_api_name
440 ,message => 'p_api_version: '||p_api_version );
441 END IF;
442 END IF;
443 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
444 ,p_data => x_msg_data);
445 END;
446
447
448 --------------------------------------------------------------------------------
449 --- delete_subjective_scores --
450 --------------------------------------------------------------------------------
451
452 PROCEDURE delete_subjective_scores(
453 p_api_version IN NUMBER
454 ,p_auction_header_id IN
455 pon_auction_headers_all.auction_header_id%TYPE
456 ,x_return_status OUT NOCOPY VARCHAR2
457 ,x_msg_data OUT NOCOPY VARCHAR2
458 ,x_msg_count OUT NOCOPY NUMBER
459 )IS
460
461 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SUBJECTIVE_SCORES';
462 l_api_version CONSTANT NUMBER := 1.0;
463 l_stage VARCHAR2(50);
464
465
466
467 BEGIN
468
469 -- Check for API comptability
470 l_stage := '10: API check';
471
472 IF fnd_api.compatible_api_call(
473 p_current_version_number => l_api_version
474 ,p_caller_version_number => p_api_version
475 ,p_api_name => l_api_name
476 ,p_pkg_name => g_pkg_name)
477 THEN
478 NULL;
479 ELSE
480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
481 END IF;
482
483 -- delete scores if input parameters are not null
484 l_stage := '20: deletes begin here';
485
486 IF (p_auction_header_id IS NULL) THEN
487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
488 END IF;
489
490 -- if scores exist for manually scored attributes
491 -- update them to zero for this auction for all active bids
492 -- so that teams can start scoring
493 UPDATE pon_bid_attribute_values pbav
494 SET score = 0,
495 weighted_score = 0
496 WHERE pbav.auction_header_id = p_auction_header_id
497 AND pbav.auction_line_number = -1
498 AND attribute_name IN
499 (SELECT -- only header attributes that are scored manually
500 paa.attribute_name
501 FROM pon_auction_attributes paa
502 WHERE paa.auction_header_id = pbav.auction_header_id
503 AND paa.line_number = -1
504 AND paa.attribute_list_id = -1
505 AND paa.scoring_method = 'MANUAL')
506 AND pbav.bid_number IN
507 (SELECT --only active bids for this auction
508 pbh.bid_number
509 FROM pon_bid_headers pbh
510 WHERE pbh.auction_header_id = pbav.auction_header_id
511 AND pbh.bid_status = 'ACTIVE');
512
513
514
515 x_return_status := fnd_api.G_RET_STS_SUCCESS;
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
521 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
522 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
523 fnd_log.string(log_level => fnd_log.level_unexpected
524 ,module => g_pkg_name ||'.'||l_api_name
525 ,message => l_stage || ': ' || SQLERRM);
526 fnd_log.string(log_level=>fnd_log.level_unexpected
527 ,module =>g_pkg_name ||'.'||l_api_name
528 ,message => 'Input parameter list: ' );
529 fnd_log.string(log_level=>fnd_log.level_unexpected
530 ,module =>g_pkg_name ||'.'||l_api_name
531 ,message => 'Auction Header Id :'||p_auction_header_id);
532 fnd_log.string(log_level=>fnd_log.level_unexpected
533 ,module =>g_pkg_name ||'.'||l_api_name
534 ,message => 'p_api_version: '||p_api_version );
535 END IF;
536 END IF;
537 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
538 ,p_data => x_msg_data);
539 END;
540
541 --------------------------------------------------------------------------------
542 --- delete_section_assignment --
543 --------------------------------------------------------------------------------
544
545 PROCEDURE delete_section_assignment(
546 p_api_version IN NUMBER
547 ,p_auction_header_id IN pon_scoring_team_sections.auction_header_id%TYPE
548 ,p_section_id IN pon_scoring_team_sections.section_id%TYPE
549 ,x_return_status OUT NOCOPY VARCHAR2
550 ,x_msg_data OUT NOCOPY VARCHAR2
551 ,x_msg_count OUT NOCOPY NUMBER
552 )IS
553
554 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SECTION_ASSIGNMENT';
555 l_api_version CONSTANT NUMBER := 1.0;
556 l_stage VARCHAR2(50);
557
558
559
560 BEGIN
561
562 -- Check for API comptability
563 l_stage := '10: API check';
567 ,p_caller_version_number => p_api_version
564
565 IF fnd_api.compatible_api_call(
566 p_current_version_number => l_api_version
568 ,p_api_name => l_api_name
569 ,p_pkg_name => g_pkg_name)
570 THEN
571 NULL;
572 ELSE
573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
574 END IF;
575
576 -- delete scores if input parameters are not null
577 l_stage := '20: delete begins here';
578
579 IF (p_auction_header_id IS NULL) OR (p_section_id IS NULL) THEN
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581 END IF;
582
583 -- delete the section assignment row
584 DELETE FROM pon_scoring_team_sections
585 WHERE auction_header_id = p_auction_header_id
586 AND section_id = p_section_id;
587
588
589 x_return_status := fnd_api.G_RET_STS_SUCCESS;
590
591 EXCEPTION
592 WHEN OTHERS THEN
593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
594 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
595 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
596 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
597 fnd_log.string(log_level => fnd_log.level_unexpected
598 ,module => g_pkg_name ||'.'||l_api_name
599 ,message => l_stage || ': ' || SQLERRM);
600 fnd_log.string(log_level=>fnd_log.level_unexpected
601 ,module =>g_pkg_name ||'.'||l_api_name
602 ,message => 'Input parameter list: ' );
603 fnd_log.string(log_level=>fnd_log.level_unexpected
604 ,module =>g_pkg_name ||'.'||l_api_name
605 ,message => 'Auction Header Id :'||p_auction_header_id);
606 fnd_log.string(log_level=>fnd_log.level_unexpected
607 ,module =>g_pkg_name ||'.'||l_api_name
608 ,message => 'Section Id :'||p_section_id);
609 fnd_log.string(log_level=>fnd_log.level_unexpected
610 ,module =>g_pkg_name ||'.'||l_api_name
611 ,message => 'p_api_version: '||p_api_version );
612 END IF;
613 END IF;
614 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
615 ,p_data => x_msg_data);
616 END;
617
618
619 --------------------------------------------------------------------------------
620 --- unlock_scoring --
621 --------------------------------------------------------------------------------
622 PROCEDURE unlock_scoring(
623 p_api_version IN NUMBER
624 ,p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE
625 ,p_tpc_id IN pon_auction_headers_all.trading_partner_contact_id%TYPE
626 ,x_return_status OUT NOCOPY VARCHAR2
627 ,x_msg_data OUT NOCOPY VARCHAR2
628 ,x_msg_count OUT NOCOPY NUMBER
629 ) IS
630
631
632 l_api_name CONSTANT VARCHAR2(30) := 'UNLOCK_SCORING';
633 l_api_version CONSTANT NUMBER := 1.0;
634 l_stage VARCHAR2(50);
635
636
637
638 BEGIN
639
640 -- Check for API comptability
641 l_stage := '10: API check';
642
643 IF fnd_api.compatible_api_call(
644 p_current_version_number => l_api_version
645 ,p_caller_version_number => p_api_version
646 ,p_api_name => l_api_name
647 ,p_pkg_name => g_pkg_name)
648 THEN
649 NULL;
650 ELSE
651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
652 END IF;
653
654 -- update auction header if input parameters are not null
655 l_stage := '20: updates begin here';
656
657 IF (p_auction_header_id IS NULL) OR (p_tpc_id IS NULL) THEN
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 END IF;
660
661 -- update auction header
662 l_stage := '30: update auction header';
663
664 UPDATE pon_auction_headers_all
665 SET scoring_lock_date = null
666 ,scoring_lock_tp_contact_id = p_tpc_id
667 ,last_update_date = SYSDATE
668 ,last_updated_by = fnd_global.user_id
669 WHERE auction_header_id = p_auction_header_id
670 AND scoring_lock_date IS NOT NULL;
671
672 IF SQL%NOTFOUND THEN
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675
676 l_stage := '35: update the pon bid attribute values table';
677
678 -- Wipe out the score data for all manually scored attributes if
679 -- scoring is unlocked
680
681 UPDATE pon_bid_attribute_values
682 SET score = NULL
683 ,internal_note = NULL
684 ,weighted_score = NULL
685 ,last_update_date = SYSDATE
686 ,last_updated_by = fnd_global.user_id
687 WHERE auction_header_id = p_auction_header_id
688 AND line_number = -1
689 AND sequence_number IN
690 (SELECT paa.sequence_number
691 FROM pon_auction_attributes paa, pon_auction_headers_all paha, pon_auction_sections pas
692 WHERE paa.auction_header_id = p_auction_header_id
693 AND paa.attribute_list_id = -1
694 AND paa.line_number = -1
695 AND paa.scoring_method = 'MANUAL'
696 AND NVL(paa.display_only_flag, 'N') = 'N' -- display only attributes are not scored
697 AND paha.auction_header_id = paa.auction_header_id
698 AND pas.auction_header_id = paa.auction_header_id
699 AND pas.attr_group_seq_number = paa.attr_group_seq_number
700 AND pas.section_name = paa.section_name
701 AND (NVL(paha.two_part_flag,'N') = 'N' OR -- Non 2 Stage negotiations
702 (paha.two_part_flag = 'Y' AND paha.sealed_auction_status = 'LOCKED') OR -- 2 Stage negotiations in technical phase
703 (paha.two_part_flag = 'Y' AND paha.sealed_auction_status <> 'LOCKED' AND pas.two_part_section_type = 'COMMERCIAL'))); --2 stage negotiations in commercial phase will clear only commercial scores
704
705 l_stage := '40: update the pon bid headers table';
706
707 UPDATE pon_bid_headers
708 SET score_overriden_flag = NULL
709 ,score_overriden_date = NULL
710 ,score_override_tp_contact_id = NULL
711 ,last_update_date = SYSDATE
712 ,last_updated_by = fnd_global.user_id
713 WHERE auction_header_id = p_auction_header_id;
714
715 x_return_status := fnd_api.G_RET_STS_SUCCESS;
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
721 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,SQLERRM);
722 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
723 fnd_log.string(log_level => fnd_log.level_unexpected
724 ,module => g_pkg_name ||'.'||l_api_name
725 ,message => l_stage || ': ' || SQLERRM);
726 fnd_log.string(log_level=>fnd_log.level_unexpected
727 ,module =>g_pkg_name ||'.'||l_api_name
728 ,message => 'Input parameter list: ' );
729 fnd_log.string(log_level=>fnd_log.level_unexpected
730 ,module =>g_pkg_name ||'.'||l_api_name
731 ,message => 'Auction Header Id :'||p_auction_header_id);
732 fnd_log.string(log_level=>fnd_log.level_unexpected
733 ,module =>g_pkg_name ||'.'||l_api_name
734 ,message => 'Trading Partner Contact Id'|| p_tpc_id );
735 fnd_log.string(log_level=>fnd_log.level_unexpected
736 ,module =>g_pkg_name ||'.'||l_api_name
737 ,message => 'p_api_version: '||p_api_version );
738 END IF;
739 END IF;
740 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
741 ,p_data => x_msg_data);
742
743 END;
744
745 END PON_TEAM_SCORING_UTIL_PVT;