1 PACKAGE BODY OKC_QA_PRICE_PVT AS
2 /* $Header: OKCRQARB.pls 120.0 2005/05/25 18:25:56 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 TYPE NUM_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7 TYPE CHAR_TBL_TYPE IS TABLE OF VARCHAR2(450) INDEX BY BINARY_INTEGER;
8
9
10 Procedure Check_Price(
11 x_return_status OUT NOCOPY VARCHAR2,
12 p_chr_id IN NUMBER) Is
13
14 l_cle_price_tbl OKC_PRICE_PVT.CLE_PRICE_TBL_TYPE;
15 l_control_rec OKC_PRICE_PUB.PRICE_CONTROL_REC_TYPE;
16 l_msg_count NUMBER;
17 l_msg_data VARCHAR2(240);
18 l_return_status VARCHAR2(3);
19 l_application_id okc_k_headers_b.application_id%TYPE;
20 l_estimated_price okc_k_headers_b.estimated_amount%TYPE;
21 l_chr_net_price NUMBER;
22 l_buy_or_sell VARCHAR2(1);
23 l_orig_system_source_code VARCHAR2(30);
24 l_price_qa_check_yn VARCHAR2(1):= 'N' ;
25 --
26 cursor k_csr is
27 select application_id,
28 estimated_amount,
29 buy_or_sell,
30 orig_system_source_code
31 from okc_k_headers_b
32 where id = p_chr_id;
33
34 -- execute price QA check for KOL (hdr.orig_system_source_code = 'KSSA_HDR)If there is
35 -- at least one one line(top or sub line).
36 -- If there is NO top or sub line exists , skip price QA check for KOL.
37 ---
38 CURSOR orig_sys_sourc_csr IS
39 SELECT hdr.orig_system_source_code, cle.ID
40 FROM OKC_K_LINES_B cle,
41 OKC_K_HEADERS_B hdr
42 WHERE hdr.id = p_chr_id
43 AND hdr.id = cle.dnz_chr_id (+)
44 AND ROWNUM < 2;
45 rec_orig_sys_sourc_csr orig_sys_sourc_csr%ROWTYPE ;
46
47
48 l_cle_id_tbl num_tbl_type;
49 l_id_tbl num_tbl_type;
50 l_line_number_tbl char_tbl_type;
51 l_lse_id_tbl num_tbl_type;
52
53
54 l_qa_covered_line_qty_mismatch BOOLEAN := FALSE;
55 i PLS_INTEGER;
56
57
58
59 Begin
60 IF (l_debug = 'Y') THEN
61 okc_debug.Set_Indentation('Check_Price');
62 okc_debug.Log('1000: Entering Check_Price',2);
63 END IF;
64 --
65 x_return_status := okc_api.g_ret_sts_success;
66 If Nvl(fnd_profile.value('OKC_ADVANCED_PRICING'), 'N') <> 'Y' Then
67 IF (l_debug = 'Y') THEN
68 okc_debug.log('1010: Profile OKC_ADVANCED_PRICING - ' || fnd_profile.value('OKC_ADVANCED_PRICING'));
69 END IF;
70 -- No need to set the return status here otherwise a blank message
71 -- will show up in the QA window with error/warning status
72 Raise G_EXCEPTION_HALT_VALIDATION;
73 End If;
74 --
75 IF (l_debug = 'Y') THEN
76 okc_debug.log('1020: Before opening cursor k_csr');
77 END IF;
78 Open k_csr;
79 Fetch k_csr
80 Into l_application_id,
81 l_estimated_price,
82 l_buy_or_sell,
83 l_orig_system_source_code;
84 Close k_csr;
85 IF (l_debug = 'Y') THEN
86 okc_debug.log('1030: After closing cursor k_csr');
87 END IF;
88 --
89 -- Price check is to be done only for OKC and OKO Contracts
90 IF (l_debug = 'Y') THEN
91 okc_debug.log('1040: Application_id - ' || To_Char(l_application_id));
92 END IF;
93 If l_application_id Not in (510, 871) Then
94 -- No need to set the return status here
95 Raise G_EXCEPTION_HALT_VALIDATION;
96 End If;
97
98 -- NO Price check for buy contracts - Advanced pricing is not used
99 If l_buy_or_sell='B' Then
100 IF (l_debug = 'Y') THEN
101 okc_debug.log('1040: Intent - ' ||l_buy_or_sell);
102 END IF;
103 Raise G_EXCEPTION_HALT_VALIDATION;
104 End If;
105 --
106 -- NO Price check for Contracts created from quote/istore, always accept price from quote
107 If l_orig_system_source_code = 'ASO_HDR' Then
108 IF (l_debug = 'Y') THEN
109 okc_debug.log('1040: Intent - ' ||l_orig_system_source_code);
110 END IF;
111
112 OKC_API.set_message(
113 p_app_name => G_APP_NAME,
114 p_msg_name => 'OKC_NO_PRICE_CHECK');
115 x_return_status := OKC_API.G_RET_STS_SUCCESS;
116
117 Raise G_EXCEPTION_HALT_VALIDATION;
118 End If;
119
120 -- Set the p_level to QA to prevent all the updates in the pricing api
121 l_control_rec.p_level := 'QA';
122
123 OPEN orig_sys_sourc_csr;
124 FETCH orig_sys_sourc_csr INTO rec_orig_sys_sourc_csr ;
125
126 IF NVL(rec_orig_sys_sourc_csr.orig_system_source_code,'*') = 'KSSA_HDR' Then
127 IF rec_orig_sys_sourc_csr.ID IS NOT NULL THEN
128 l_price_qa_check_yn := 'Y';
129 END IF;
130 ELSE
131 l_price_qa_check_yn := 'Y';
132 END IF;
133 CLOSE orig_sys_sourc_csr;
134
135 IF l_price_qa_check_yn = 'N' THEN
136 -- dont execute price_qa_check.
137 -- return success and do not execute price_qa_check.
138 x_return_status := okc_api.g_ret_sts_success;
139 RETURN;
140 ELSE
141 --execute price_qa_check as earlier .
142 --
143 IF (l_debug = 'Y') THEN
144 okc_debug.log('1050: Before calling okc_price_pub');
145 END IF;
146 OKC_Price_Pub.Update_Contract_Price(
147 p_api_version => 1.0,
148 p_init_msg_list => OKC_API.G_FALSE,
149 p_commit => OKC_API.G_FALSE,
150 p_chr_id => p_chr_id,
151 px_control_rec => l_control_rec,
152 x_cle_price_tbl => l_cle_price_tbl,
153 x_chr_net_price => l_chr_net_price,
154 x_return_status => l_return_status,
155 x_msg_count => l_msg_count,
156 x_msg_data => l_msg_data);
157 END IF; ---IF l_price_qa_check_yn = 'N' THEN
158 --
159 IF (l_debug = 'Y') THEN
160 okc_debug.log('1060: After calling okc_price_pub');
161 okc_debug.log('1070: Return status from okc_price_pub - ' || l_return_status);
162 END IF;
163 If (l_return_status <> 'S') Then
164 -- In case of Unexpected error, return error so that it can be shown
165 -- as an Error in QA window. For normal error, return Success.
166 -- However in this case, clear the message stack otherwise the
167 -- messages will be displayed in the QA window.
168 -- Not any more, just return an error
169 x_return_status := OKC_API.G_RET_STS_ERROR;
170 /* If l_return_status = 'U' Then
171 x_return_status := OKC_API.G_RET_STS_ERROR;
172 Else
173 x_return_status := OKC_API.G_RET_STS_SUCCESS;
174 OKC_API.Init_Msg_List(OKC_API.G_TRUE);
175 OKC_API.set_message(
176 p_app_name => G_APP_NAME,
177 p_msg_name => G_QA_SUCCESS);
178 End If; */
179 Raise G_EXCEPTION_HALT_VALIDATION;
180 End If;
181 --
182 If nvl(l_chr_net_price, 0) <> nvl(l_estimated_price, 0) Then
183 IF (l_debug = 'Y') THEN
184 okc_debug.log('1080: Price stored at the header level - ' || To_Char(l_estimated_price));
185 END IF;
186 IF (l_debug = 'Y') THEN
187 okc_debug.log('1090: Price returned from okc_price_pub - ' || To_Char(l_chr_net_price));
188 END IF;
189 OKC_API.set_message(
190 p_app_name => G_APP_NAME,
191 p_msg_name => 'OKC_QA_INVALID_PRICE');
192 x_return_status := OKC_API.G_RET_STS_ERROR;
193 Raise G_EXCEPTION_HALT_VALIDATION;
194 End If;
195 -- notify caller of success
196 OKC_API.set_message(
197 p_app_name => G_APP_NAME,
198 p_msg_name => G_QA_SUCCESS);
199 --
200 IF (l_debug = 'Y') THEN
201 okc_debug.Log('1100: Exiting Check_Price', 2);
202 okc_debug.Reset_Indentation;
203 END IF;
204 --
205 Exception
206 When G_EXCEPTION_HALT_VALIDATION Then
207 IF (l_debug = 'Y') THEN
208 okc_debug.Log('1110: Exiting Check_Price', 2);
209 okc_debug.Reset_Indentation;
210 END IF;
211 When Others Then
212 IF (l_debug = 'Y') THEN
213 okc_debug.Log('1120: Exiting Check_Price', 2);
214 okc_debug.Reset_Indentation;
215 END IF;
216 -- store SQL error message on message stack
217 OKC_API.SET_MESSAGE(
218 p_app_name => G_APP_NAME,
219 p_msg_name => G_UNEXPECTED_ERROR,
220 p_token1 => G_SQLCODE_TOKEN,
221 p_token1_value => SQLCODE,
222 p_token2 => G_SQLERRM_TOKEN,
223 p_token2_value => SQLERRM);
224 -- notify caller of an error as UNEXPETED error
225 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
226 End Check_Price;
227 --
228
229 PROCEDURE check_covered_line_qty (
230 p_chr_id IN okc_k_headers_b.ID%TYPE,
231 x_return_status OUT NOCOPY VARCHAR2) IS
232
233
234
235 l_cle_price_tbl OKC_PRICE_PVT.CLE_PRICE_TBL_TYPE;
236 l_msg_count NUMBER;
237 l_msg_data VARCHAR2(240);
238 l_return_status VARCHAR2(3);
239 l_application_id okc_k_headers_b.application_id%TYPE;
240 l_buy_or_sell VARCHAR2(1);
241 l_orig_system_source_code VARCHAR2(30);
242 l_new_qa_check_yn VARCHAR2(1):= 'N' ;
243 --
244 cursor k_csr is
245 select application_id,
246 buy_or_sell,
247 orig_system_source_code
248 from okc_k_headers_b
249 where id = p_chr_id;
250
251 -- execute new QA check for KOL (hdr.orig_system_source_code = 'KSSA_HDR)If there is
252 -- at least one one line(top or sub line).
253 -- If there is NO top or sub line exists , skip new QA check for KOL.
254 ---
255 CURSOR orig_sys_sourc_csr IS
256 SELECT hdr.orig_system_source_code, cle.ID
257 FROM OKC_K_LINES_B cle,
258 OKC_K_HEADERS_B hdr
259 WHERE hdr.id = p_chr_id
260 AND hdr.id = cle.dnz_chr_id (+)
261 AND ROWNUM < 2;
262 rec_orig_sys_sourc_csr orig_sys_sourc_csr%ROWTYPE ;
263
264
265 l_cle_id_tbl num_tbl_type;
266 l_id_tbl num_tbl_type;
267 l_line_number_tbl char_tbl_type;
268 l_lse_id_tbl num_tbl_type;
269
270
271 l_qa_covered_line_qty_mismatch BOOLEAN := FALSE;
272 i PLS_INTEGER;
273
274
275
276 -------------------------------------------------------------------------------
277 -- Procedure: validate_covered_line_qty
278 -- Version: 1.0
279 -- Purpose: check to ensure that the quantity of a (sub) line of linestyle 'covered line' matches
280 -- that of the (top) line to which it points.
281 -- This check is done regardless of whether Advanced Pricing is enabled
282 -- In Parameters: p_cle_id the (sub) line of linestyle 'covered line'
283 --
284
285 -- Out Parameters: x_return_status
286 --
287 -- Comments: This procedure is to be called only by QA
288
289 PROCEDURE validate_covered_line_qty
290 (p_chr_id IN okc_k_headers_b.ID%TYPE,
291 p_cle_id IN okc_k_lines_b.ID%TYPE,
292 p_l_id_tbl IN num_tbl_type,
293 p_l_cle_id_tbl IN num_tbl_type,
294 p_l_line_number_tbl IN char_tbl_type,
295 x_return_status OUT NOCOPY VARCHAR2
296 ) IS
297
298
299 --gets quantity of (sub) line of linestyle 'covered line'
300 --also gets the id (object1_id1) of the parent line which is being pointed to
301 --NOTE: validate_covered_line_qty is to be called ONLY for (sub) lines with lse_id = 41 as we support only
302 -- this particular linestyle for 'OKX_COVLINE' object code
303 CURSOR c_get_quantity1 (b_cle_id NUMBER) is
304 SELECT object1_id1, number_of_items qty
305 FROM okc_k_items
306 WHERE cle_id = b_cle_id
307 AND dnz_chr_id = p_chr_id
308 AND jtot_object1_code = 'OKX_COVLINE';
309
310
311 --gets the quantity of the parent non-service item line being pointed to
312 CURSOR c_get_quantity2 (b_cle_id NUMBER) is
313 SELECT number_of_items qty
314 FROM okc_k_items
315 WHERE cle_id = b_cle_id
316 AND dnz_chr_id = p_chr_id;
317
318
319 l_return_status VARCHAR2(1);
320
321 l_qty1 NUMBER := OKC_API.G_MISS_NUM;
322 l_qty2 NUMBER := OKC_API.G_MISS_NUM;
323 l_parent_cle_id OKC_K_LINES_B.ID%TYPE;
324 l_line_number OKC_K_LINES_B.LINE_NUMBER%TYPE := '0';
325 l_parent_line_number OKC_K_LINES_B.LINE_NUMBER%TYPE := '0';
326 i PLS_INTEGER := 0;
327 l_top_line_id NUMBER := OKC_API.G_MISS_NUM;
328
329 BEGIN
330 x_return_status := okc_api.g_ret_sts_success;
331 IF (l_debug = 'Y') THEN
332 okc_debug.Set_Indentation('validate_covered_line_qty');
333 END IF;
334
335 IF (l_debug = 'Y') THEN
336 okc_debug.Log('Start : okc_price_pvt.validate_covered_line_qty ',3);
337 END IF;
338 /* Note: we already perform validation to ensure that the contract is for intent of sale and for OKC, OKO
339 in OKC_QA_PRICE_PVT.Check_Price */
340
341
342 /* Get quantity of (sub) line of linestyle 'covered line' */
343 IF (l_debug = 'Y') THEN
344 okc_debug.Log('Get quantity of line with id: ' || p_cle_id, 5);
345 okc_debug.Log('Get quantity of line p_cle_id = ' || p_cle_id || 'with jtot_object=''OKX_COVLINE'' and lse_id=41',5);
346 END IF;
347 IF c_get_quantity1%ISOPEN THEN
348 CLOSE c_get_quantity1;
349 END IF;
350 OPEN c_get_quantity1 (b_cle_id => p_cle_id);
351 FETCH c_get_quantity1 INTO l_parent_cle_id, l_qty1;
352 CLOSE c_get_quantity1;
353 IF (l_debug = 'Y') THEN
354 okc_debug.Log('l_qty1: ' || l_qty1, 5);
355 END IF;
356
357
358 /* Get quantity of parent (top) line being pointed to */
359 IF (l_debug = 'Y') THEN
360 okc_debug.Log('Get quantity of parent (top) line being pointed to: '||l_parent_cle_id, 5);
361 END IF;
362 IF c_get_quantity2%ISOPEN THEN
363 CLOSE c_get_quantity2;
364 END IF;
365 OPEN c_get_quantity2 (b_cle_id => l_parent_cle_id);
366 FETCH c_get_quantity2 INTO l_qty2;
367 CLOSE c_get_quantity2;
368 IF (l_debug = 'Y') THEN
369 okc_debug.Log('l_qty2: ' || l_qty2, 5);
370 END IF;
371
372
373 IF l_qty1 <> OKC_API.G_MISS_NUM AND l_qty2 <> OKC_API.G_MISS_NUM
374 AND l_qty1 <> l_qty2
375 THEN
376 IF (l_debug = 'Y') THEN
377 okc_debug.Log('l_qty1 and l_qty2 are not the same so setting error message on stack...',5);
378 END IF;
379
380 /* get the line number of the immediate (top) line */
381 i:= p_l_id_tbl.first;
382 WHILE i IS NOT NULL LOOP
383 IF p_l_id_tbl(i) = p_cle_id THEN
384 l_top_line_id := p_l_cle_id_tbl(i);
385 EXIT;
386 END IF;
387 i := p_l_id_tbl.next(i);
388 END LOOP;
389
390 i:= p_l_id_tbl.first;
391 WHILE i IS NOT NULL LOOP
392 IF p_l_id_tbl(i) = l_top_line_id THEN
393 l_line_number := p_l_line_number_tbl(i);
394 EXIT;
395 END IF;
396 i := p_l_id_tbl.next(i);
397 END LOOP;
398
399
400 --now concatenate it with the line number of the (sub) line of linestyle 'covered line'
401 i:= p_l_id_tbl.first;
402 WHILE i IS NOT NULL LOOP
403 IF p_l_id_tbl(i) = p_cle_id THEN
404 l_line_number := l_line_number || '.' || p_l_line_number_tbl(i);
405 EXIT;
406 END IF;
407 i := p_l_id_tbl.next(i);
408 END LOOP;
409
410 /* finally get the line number of the parent (top) line which is being pointed to */
411 i:= p_l_id_tbl.first;
412 WHILE i IS NOT NULL LOOP
413 IF p_l_id_tbl(i) = l_parent_cle_id THEN
414 l_parent_line_number := p_l_line_number_tbl(i);
415 EXIT;
416 END IF;
417 i := p_l_id_tbl.next(i);
418 END LOOP;
419
420
421 OKC_API.set_message(p_app_name => G_APP_NAME,
422 p_msg_name => 'OKC_QA_MISMATCH_QTY',
423 p_token1 => 'LNUMB1',
424 p_token1_value => l_line_number,
425 p_token2 => 'LNUMB2',
426 p_token2_value => l_parent_line_number);
427 IF (l_debug = 'Y') THEN
428 okc_debug.Log('Covered line number ' || l_line_number || 'and id: ' || p_cle_id || 'serviceable product have a quantity mismatch.',5);
429 okc_debug.Log('l_qty1: ' || l_qty1,5);
430 okc_debug.Log('l_qty2: ' || l_qty2,5);
431 END IF;
432 x_return_status := OKC_API.G_RET_STS_ERROR;
433
434 END IF;
435
436
437 If x_return_status <> OKC_API.G_RET_STS_ERROR then
438 IF (l_debug = 'Y') THEN
439 okc_debug.Log('passed okc_price_pvt.validate_covered_line_qty');
440 END IF;
441 End if;
442
443 IF (l_debug = 'Y') THEN
444 okc_debug.Log('End : okc_price_pvt.validate_covered_line_qty ',3);
445 END IF;
446
447 EXCEPTION
448 WHEN OTHERS THEN
449 IF (l_debug = 'Y') THEN
450 okc_debug.Log('Error : unexpected error in okc_price_pvt.validate_covered_line_qty ',3);
451 okc_debug.Log('Error : '|| sqlerrm, 3);
452 END IF;
453
454 IF c_get_quantity1%ISOPEN THEN
455 CLOSE c_get_quantity1;
456 END IF;
457 IF c_get_quantity2%ISOPEN THEN
458 CLOSE c_get_quantity2;
459 END IF;
460 OKC_API.set_message(G_APP_NAME,
461 G_UNEXPECTED_ERROR,
462 G_SQLCODE_TOKEN,
463 SQLCODE,
464 G_SQLERRM_TOKEN,
465 SQLERRM);
466 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
467 END validate_covered_line_qty;
468
469
470
471 BEGIN
472 IF (l_debug = 'Y') THEN
473 okc_debug.Set_Indentation('check_covered_line_qty');
474 okc_debug.Log('1000: Entering check_covered_line_qty',2);
475 END IF;
476 --
477 x_return_status := okc_api.g_ret_sts_success;
478
479 /*******************************************************************
480 We carry out nocopy the check regardless of whether Advanced Pricing is enabled
481
482 IF Nvl(fnd_profile.value('OKC_ADVANCED_PRICING'), 'N') = 'Y' THEN
483 don't run this QA check if advanced pricing is turned on because the
484 fix for Bug 2386767 made in OKC_PRICE_PVT corrects a mismatch between
485 the covered line quantity and the non service line quantity.
486 If advanced pricing is off, we need to run this QA check to raise a QA error
487 in case the covered line quantity and non service quantity don't match.
488
489 IF (l_debug = 'Y') THEN
490 okc_debug.log('1010: Profile OKC_ADVANCED_PRICING - ' || fnd_profile.value('OKC_ADVANCED_PRICING'));
491 END IF;
492 -- No need to set the return status here otherwise a blank message
493 -- will show up in the QA window with error/warning status
494 RAISE G_EXCEPTION_HALT_VALIDATION;
495 END IF;
496 *****************************************************************/
497 --
498 IF (l_debug = 'Y') THEN
499 okc_debug.log('1020: Before opening cursor k_csr');
500 END IF;
501 OPEN k_csr;
502 FETCH k_csr
503 INTO l_application_id,
504 l_buy_or_sell,
505 l_orig_system_source_code;
506 CLOSE k_csr;
507 IF (l_debug = 'Y') THEN
508 okc_debug.log('1030: After closing cursor k_csr');
509 END IF;
510
511 --
512 /* covered line/non-service qty's check is to be done only for OKC
513 and OKO Contracts */
514 IF (l_debug = 'Y') THEN
515 okc_debug.log('1040: Application_id - ' || To_Char(l_application_id));
516 END IF;
517 If l_application_id Not in (510, 871) Then
518 -- No need to set the return status here
519 Raise G_EXCEPTION_HALT_VALIDATION;
520 End If;
521
522 -- perform only for sell contracts
523 IF l_buy_or_sell='B' THEN
524 IF (l_debug = 'Y') THEN
525 okc_debug.log('1045: Intent - ' ||l_buy_or_sell);
526 END IF;
527 RAISE G_EXCEPTION_HALT_VALIDATION;
528 END IF;
529 --
530
531 -- NO Price check for Contracts created from quote/istore, always accept price from quote
532 If l_orig_system_source_code = 'ASO_HDR' Then
533 IF (l_debug = 'Y') THEN
534 okc_debug.log('1050: Intent - ' ||l_orig_system_source_code);
535 END IF;
536
537 OKC_API.set_message(
538 p_app_name => G_APP_NAME,
539 p_msg_name => 'OKC_NO_PRICE_CHECK');
540 x_return_status := OKC_API.G_RET_STS_SUCCESS;
541
542 Raise G_EXCEPTION_HALT_VALIDATION;
543 End If;
544
545
546 /* execute new QA check for KOL only if there is at least one
547 one line(top or sub line). */
548 OPEN orig_sys_sourc_csr;
549 FETCH orig_sys_sourc_csr INTO rec_orig_sys_sourc_csr ;
550
551 IF NVL(rec_orig_sys_sourc_csr.orig_system_source_code,'*') = 'KSSA_HDR' Then
552 IF rec_orig_sys_sourc_csr.ID IS NOT NULL THEN
553 l_new_qa_check_yn := 'Y';
554 END IF;
555 ELSE
556 l_new_qa_check_yn := 'Y';
557 END IF;
558 CLOSE orig_sys_sourc_csr;
559
560
561 IF l_new_qa_check_yn = 'N' THEN
562 -- do not execute new_qa_check.
563 -- return success and do not execute new_qa_check.
564 x_return_status := okc_api.g_ret_sts_success;
565 RETURN;
566 ELSE
567 --execute new_qa_check as earlier .
568 --
569 IF (l_debug = 'Y') THEN
570 okc_debug.log('1055: Before looping thorough all lines/subines for contract');
571 END IF;
572
573 SELECT cle_id, id, line_number, lse_id
574 BULK COLLECT INTO l_cle_id_tbl,l_id_tbl, l_line_number_tbl, l_lse_id_tbl
575 FROM okc_k_lines_b
576 CONNECT BY PRIOR id = cle_id
577 START WITH chr_id = p_chr_id;
578 IF (l_debug = 'Y') THEN
579 okc_debug.Log('1060 : select rowcount'||SQL%ROWCOUNT, 1);
580 END IF;
581
582 i:= l_id_tbl.first;
583 IF (l_debug = 'Y') THEN
584 okc_debug.Log('1065 :starting out nocopy loop to check non-service/covered line quantities', 1);
585 END IF;
586 WHILE i is not null LOOP
587 IF l_lse_id_tbl(i) = 41 THEN
588 /** perform validation to ensure that the quantity of a (sub) line of linestyle 'covered line'
589 matches that of the (top) line to which it points. **/
590
591 IF (l_debug = 'Y') THEN
592 okc_debug.Log('1070 : before calling validate_covered_line_qty ', 3);
593 okc_debug.Log('1075 : p_chr_id: ' || to_char(p_chr_id), 3);
594 okc_debug.Log('1080 : p_cle_id: ' || to_char(l_id_tbl(i)), 3);
595 END IF;
596 validate_covered_line_qty (p_chr_id => p_CHR_ID,
597 p_cle_id => l_id_tbl(i),
598 p_l_id_tbl => l_id_tbl,
599 p_l_cle_id_tbl => l_cle_id_tbl,
600 p_l_line_number_tbl => l_line_number_tbl,
601 x_return_status => l_return_status
602 );
603 IF (l_debug = 'Y') THEN
604 okc_debug.Log('1085 : after calling validate_covered_line_qty ' || 'l_return_status: ' || l_return_status, 3);
605 END IF;
606
607 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
608 --we want QA to pick up all the error messages on the stack
609 l_qa_covered_line_qty_mismatch := TRUE;
610 END IF;
611
612 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
613 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
614 END IF;
615 End If;
616
617 i:=l_id_tbl.NEXT(i);
618 END LOOP;
619
620 END IF; ---IF l_new_qa_check_yn = 'N' THEN
621 --
622 IF (l_debug = 'Y') THEN
623 okc_debug.Log('1090 : after loop to check non-service/covered line quantities', 1);
624 END IF;
625
626 IF l_qa_covered_line_qty_mismatch = TRUE THEN
627 /* this means that validate_covered_line_qty() has put some error
628 messages on the error stack which we now want QA to display in the
629 QA results */
630 l_return_status := OKC_API.G_RET_STS_ERROR;
631 IF (l_debug = 'Y') THEN
632 okc_debug.Log('1095 : l_qa_covered_line_qty_mismatch: TRUE', 1);
633 END IF;
634 ELSE
635 IF (l_debug = 'Y') THEN
636 okc_debug.Log('1100 : l_qa_covered_line_qty_mismatch: FALSE', 1);
637 END IF;
638 END IF;
639
640
641 IF (l_return_status <> 'S') THEN
642 x_return_status := OKC_API.G_RET_STS_ERROR;
643 RAISE G_EXCEPTION_HALT_VALIDATION;
644 END IF;
645 --
646
647 -- notify caller of success
648 OKC_API.set_message(
649 p_app_name => G_APP_NAME,
650 p_msg_name => G_QA_SUCCESS);
651 --
652 IF (l_debug = 'Y') THEN
653 okc_debug.Log('1105: Exiting check_covered_line_qty', 2);
654 okc_debug.Reset_Indentation;
655 END IF;
656 --
657 EXCEPTION
658 WHEN G_EXCEPTION_HALT_VALIDATION THEN
659 IF (l_debug = 'Y') THEN
660 okc_debug.Log('1110: Exiting check_covered_line_qty', 2);
661 okc_debug.Reset_Indentation;
662 END IF;
663 WHEN Others THEN
664 IF (l_debug = 'Y') THEN
665 okc_debug.Log('1120: Exiting check_covered_line_qty', 2);
666 okc_debug.Reset_Indentation;
667 END IF;
668 -- store SQL error message on message stack
669 OKC_API.SET_MESSAGE(
670 p_app_name => G_APP_NAME,
671 p_msg_name => G_UNEXPECTED_ERROR,
672 p_token1 => G_SQLCODE_TOKEN,
673 p_token1_value => SQLCODE,
674 p_token2 => G_SQLERRM_TOKEN,
675 p_token2_value => SQLERRM);
676 -- notify caller of an error as UNEXPECTED error
677 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
678 END check_covered_line_qty;
679 --
680
681 END OKC_QA_PRICE_PVT;
[Home] [Help]