1 PACKAGE BODY OKC_QA_DATA_INTEGRITY AS
2 /* $Header: OKCRQADB.pls 120.3 2006/08/09 22:31:42 abkumar noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 -- G_DESCRIPTIVE_FLEXFIELD_NAME CONSTANT VARCHAR2(200) := 'OKC Rule Developer DF'; -- /striping/
7 G_DF_COUNT CONSTANT NUMBER(2) := 15;
8 --
9 G_PACKAGE Varchar2(33) := ' OKC_QA_DATA_INTEGRITY.';
10 G_MSG_NAME Varchar2(30);
11 G_LINE Varchar2(4) := 'LINE';
12 G_TOKEN Varchar2(30);
13 G_BULK_FETCH_LIMIT CONSTANT NUMBER := 1000;
14 --
15 -- /striping/
16 p_rule_code OKC_RULE_DEFS_B.rule_code%TYPE;
17 p_appl_id OKC_RULE_DEFS_B.application_id%TYPE;
18 p_dff_name OKC_RULE_DEFS_B.descriptive_flexfield_name%TYPE;
19
20 PROCEDURE Set_QA_Message(p_chr_id IN OKC_K_LINES_V.chr_id%TYPE,
21 p_cle_id IN OKC_K_LINES_V.id%TYPE,
22 p_msg_name IN VARCHAR2,
23 p_token1 IN VARCHAR2 DEFAULT NULL,
24 p_token1_value IN VARCHAR2 DEFAULT NULL,
25 p_token2 IN VARCHAR2 DEFAULT NULL,
26 p_token2_value IN VARCHAR2 DEFAULT NULL,
27 p_token3 IN VARCHAR2 DEFAULT NULL,
28 p_token3_value IN VARCHAR2 DEFAULT NULL,
29 p_token4 IN VARCHAR2 DEFAULT NULL,
30 p_token4_value IN VARCHAR2 DEFAULT NULL) IS
31 l_line Varchar2(200);
32 l_token1_value Varchar2(200) := p_token1_value;
33 l_token2_value Varchar2(200) := p_token2_value;
34 l_token3_value Varchar2(200) := p_token3_value;
35 l_token4_value Varchar2(200) := p_token4_value;
36 l_return_status Varchar2(3);
37 BEGIN
38 If p_cle_id Is Not Null Then
39 l_line := okc_contract_pub.get_concat_line_no(p_cle_id,
40 l_return_status);
41 If p_token1 = g_line Then
42 l_token1_value := l_line;
43 Elsif p_token2 = g_line Then
44 l_token2_value := l_line;
45 Elsif p_token3 = g_line Then
46 l_token3_value := l_line;
47 Elsif p_token4 = g_line Then
48 l_token4_value := l_line;
49 End If;
50 End If;
51
52 OKC_API.set_message(
53 p_app_name => G_APP_NAME,
54 p_msg_name => p_msg_name,
55 p_token1 => p_token1,
56 p_token1_value => l_token1_value,
57 p_token2 => p_token2,
58 p_token2_value => l_token2_value,
59 p_token3 => p_token3,
60 p_token3_value => l_token3_value,
61 p_token4 => p_token4,
62 p_token4_value => l_token4_value);
63 END;
64 --
65 -- Start of comments
66 --
67 -- Procedure Name : check_art_compatible
68 -- Description :
69 -- Business Rules :
70 -- Parameters :
71 -- Version : 1.0
72 -- End of comments
73
74 PROCEDURE check_art_compatible(
75 x_return_status OUT NOCOPY VARCHAR2,
76 p_chr_id IN NUMBER
77 ) IS
78
79 cursor c1 is
80 select ID,SAV_SAE_ID,NAME
81 from okc_k_articles_v
82 where dnz_chr_id = p_chr_id
83 and chr_id = p_chr_id
84 and cat_type='STA';
85
86 r1 c1%ROWTYPE;
87
88 l_id number;
89 p_sae_id number;
90 l_name varchar2(150);
91
92 Cursor l_catv_csr Is
93 SELECT 'E'
94 FROM okc_k_articles_b
95 WHERE SAV_SAE_ID in (
96 SELECT SAE_ID from OKC_STD_ART_INCMPTS_V
97 WHERE SAE_ID_FOR = p_sae_id
98 UNION
99 SELECT SAE_ID_FOR from OKC_STD_ART_INCMPTS_V
100 WHERE SAE_ID = p_sae_id
101 )
102 AND DNZ_CHR_ID = p_chr_id
103 AND CHR_ID = p_chr_id
104 AND ID <> l_id;
105 l_return_status varchar2(1):='S';
106 --
107 l_proc varchar2(72) := g_package||'check_art_compatible';
108 --
109 BEGIN
110
111 IF (l_debug = 'Y') THEN
112 okc_debug.Set_Indentation(l_proc);
113 okc_debug.Log('10: Entering ',2);
114 END IF;
115
116 x_return_status := 'S';
117 FOR r1 IN c1 LOOP
118 l_id := r1.ID;
119 p_sae_id := r1.SAV_SAE_ID;
120 l_name := r1.NAME;
121 --
122 Open l_catv_csr;
123 Fetch l_catv_csr Into l_return_status;
124 Close l_catv_csr;
125 if (l_return_status<>'S') then
126 x_return_status := l_return_status;
127 OKC_API.set_message(
128 p_app_name => G_APP_NAME,
129 p_msg_name => 'OKC_INCOMP_ARTICLE_EXISTS',
130 p_token1 => 'VALUE1',
131 p_token1_value => l_name);
132 end if;
133 END LOOP;
134 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
135 OKC_API.set_message(
136 p_app_name => G_APP_NAME,
137 p_msg_name => G_QA_SUCCESS);
138 END IF;
139
140 IF (l_debug = 'Y') THEN
141 okc_debug.Log('1000: Leaving ',2);
142 okc_debug.Reset_Indentation;
143 END IF;
144 exception
145 WHEN OTHERS THEN
146 IF (l_debug = 'Y') THEN
147 okc_debug.Log('2000: Leaving ',2);
148 okc_debug.Reset_Indentation;
149 END IF;
150 if c1%ISOPEN then
151 close c1;
152 end if;
153 OKC_API.SET_MESSAGE(
154 p_app_name => G_APP_NAME,
155 p_msg_name => G_UNEXPECTED_ERROR,
156 p_token1 => G_SQLCODE_TOKEN,
157 p_token1_value => SQLCODE,
158 p_token2 => G_SQLERRM_TOKEN,
159 p_token2_value => SQLERRM);
160 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
161 end check_art_compatible;
162
163 -- Start of comments
164 --
165 -- Procedure Name : check_required_values
166 -- Description :
167 -- Business Rules :
168 -- Parameters :
169 -- Version : 1.0
170 -- End of comments
171
172 PROCEDURE check_required_values(
173 x_return_status OUT NOCOPY VARCHAR2,
174 p_chr_id IN NUMBER
175 ) IS
176
177 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
178 l_dummy VARCHAR2(1) := '?';
179 l_count NUMBER := 0;
180 l_row_notfound BOOLEAN;
181 l_token VARCHAR2(2000);
182
183 CURSOR l_clev_csr IS
184 SELECT RTRIM(RTRIM(line_number) || ', ' || RTRIM(lsev.name) || ' ' ||
185 RTRIM(clev.name)) "LINE_NAME", currency_code,
186 clev.lse_id, --bug 2398639
187 clev.cle_id --bug 2398639
188 FROM OKC_LINE_STYLES_V lsev,
189 OKC_K_LINES_V clev
190 WHERE lsev.id = clev.lse_id
191 AND clev.dnz_chr_id = p_chr_id
192 AND clev.date_cancelled is NULL; --changes [llc]--
193
194
195 --l_clev_rec l_clev_csr%ROWTYPE;
196
197 TYPE chr15_tbl_type IS TABLE OF okc_k_lines_v.currency_code%TYPE INDEX BY BINARY_INTEGER;
198 TYPE chr450_tbl_type IS TABLE OF VARCHAR2(450) INDEX BY BINARY_INTEGER;
199 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
200
201 l_line_name_tbl chr450_tbl_type;
202 l_currency_code_tbl chr15_tbl_type;
203 l_lse_id_tbl num_tbl_type;
204 l_cle_id_tbl num_tbl_type;
205
206
207 CURSOR l_ctc_csr (p_cpl_id NUMBER) IS
208 SELECT ctc.JTOT_OBJECT1_CODE, ctc.OBJECT1_ID1, ctc.OBJECT1_ID2,
209 fnd.MEANING
210 FROM FND_LOOKUPS fnd,
211 OKC_CONTACTS ctc
212 WHERE fnd.LOOKUP_CODE = ctc.cro_code
213 AND fnd.LOOKUP_TYPE = 'OKC_CONTACT_ROLE'
214 AND ctc.cpl_id = p_cpl_id;
215 l_ctc_rec l_ctc_csr%ROWTYPE;
216
217 CURSOR l_cpl1_csr IS
218 SELECT cpl.ID, cpl.JTOT_OBJECT1_CODE, cpl.OBJECT1_ID1, cpl.OBJECT1_ID2,
219 fnd.MEANING, cpl.rle_code, cpl.chr_id, cpl.cle_id
220 FROM FND_LOOKUPS fnd,
221 OKC_K_PARTY_ROLES_B cpl
222 WHERE fnd.LOOKUP_CODE = cpl.rle_code
223 AND fnd.LOOKUP_TYPE = 'OKC_ROLE'
224 AND cpl.dnz_chr_id = p_chr_id;
225 l_cpl1_rec l_cpl1_csr%ROWTYPE;
226
227 -- Do not use chr_id in the where clause, this column is not indexed.
228 CURSOR l_cpl_csr IS
229 SELECT count(distinct RLE_CODE)
230 FROM OKC_K_PARTY_ROLES_B cpl
231 WHERE cpl.dnz_chr_id = p_chr_id
232 AND cpl.cle_id is NULL;
233
234
235 CURSOR l_chrv_csr IS
236 SELECT chrv.*, sub.cls_code
237 FROM OKC_K_HEADERS_B chrv,
238 OKC_SUBCLASSES_B sub
239 WHERE chrv.id = p_chr_id
240 AND sub.code = chrv.scs_code;
241 l_chrv_rec l_chrv_csr%ROWTYPE;
242
243 CURSOR l_cle_csr(p_id okc_k_lines_b.id%TYPE) IS
244 SELECT cle.chr_id
245 FROM OKC_K_LINES_B cle
246 WHERE cle.id = p_id;
247 l_cle_rec l_cle_csr%ROWTYPE;
248
249 CURSOR l_chr_csr IS
250 SELECT 'x'
251 FROM OKC_K_PARTY_ROLES_B
252 WHERE dnz_chr_id = p_chr_id
253 AND chr_id = p_chr_id
254 AND rle_code = l_cpl1_rec.rle_code
255 AND jtot_object1_code = l_cpl1_rec.jtot_object1_code
256 AND object1_id1 = l_cpl1_rec.object1_id1
257 AND object1_id2 = l_cpl1_rec.object1_id2;
258 --bug 2398639
259 CURSOR l_ph_can_rule(b_chr_id number) IS
260 SELECT 'Y'
261 FROM okc_rule_groups_b rgp
262 ,okc_rules_b rul
263 WHERE rgp.dnz_chr_id = b_chr_id
264 AND rgp.cle_id IS NULL
265 AND rul.rgp_id = rgp.id
266 AND rul.rule_information_category = 'CAN';
267 --end bug 2398639
268 --
269 l_proc varchar2(72) := g_package||'check_required_values';
270 l_line Varchar2(200);
271 l_can_found varchar2(1) :='N'; -- bug 2398639
272 --/rules migration/
273 l_func_curr_code VARCHAR2(30);
274 --
275 BEGIN
276
277 IF (l_debug = 'Y') THEN
278 okc_debug.Set_Indentation(l_proc);
279 okc_debug.Log('10: Entering ',2);
280 END IF;
281
282 -- initialize return status
283 x_return_status := OKC_API.G_RET_STS_SUCCESS;
284
285 -- fetch the contract header information
286 OPEN l_chrv_csr;
287 FETCH l_chrv_csr INTO l_chrv_rec;
288 CLOSE l_chrv_csr;
289
290 -- check required data for contract header
291 IF (l_chrv_rec.BUY_OR_SELL IS NULL) THEN
292 OKC_API.set_message(
293 p_app_name => G_APP_NAME,
294 p_msg_name => 'OKC_QA_INTENT_REQUIRED');
295
296 -- notify caller of an error
297 x_return_status := OKC_API.G_RET_STS_ERROR;
298 END IF;
299 IF (l_chrv_rec.CURRENCY_CODE IS NULL) THEN
300 OKC_API.set_message(
301 p_app_name => G_APP_NAME,
302 p_msg_name => 'OKC_QA_CURRENCY_REQUIRED');
303
304 -- notify caller of an error
305 x_return_status := OKC_API.G_RET_STS_ERROR;
306 END IF;
307 IF (l_chrv_rec.ISSUE_OR_RECEIVE IS NULL) THEN
308 OKC_API.set_message(
309 p_app_name => G_APP_NAME,
310 p_msg_name => 'OKC_QA_PERSPECTIVE_REQUIRED');
311
312 -- notify caller of an error
313 x_return_status := OKC_API.G_RET_STS_ERROR;
314 END IF;
315
316 -- Check that at least 2 different parties have been attached
317 -- to the contract header.
318 -- get party count
319 OPEN l_cpl_csr;
320 FETCH l_cpl_csr INTO l_count;
321 CLOSE l_cpl_csr;
322
323 -- There must be 2 distinct party roles defined at the header level
324 IF (l_count < 2) THEN
325 OKC_API.set_message(
326 p_app_name => G_APP_NAME,
327 p_msg_name => 'OKC_QA_PARTY_COUNT');
328 -- notify caller of an error
329 x_return_status := OKC_API.G_RET_STS_ERROR;
330 END IF;
331 IF (l_chrv_rec.TEMPLATE_YN = 'N') THEN
332 -- Check all of the parties attached to the contract and contract
333 -- lines to make sure that all integration information is supplied
334 OPEN l_cpl1_csr;
335 LOOP
336 FETCH l_cpl1_csr INTO l_cpl1_rec;
337 EXIT WHEN l_cpl1_csr%NOTFOUND;
338 IF (l_cpl1_rec.JTOT_OBJECT1_CODE IS NULL OR
339 l_cpl1_rec.OBJECT1_ID1 IS NULL OR
340 l_cpl1_rec.OBJECT1_ID2 IS NULL) THEN
341
342 If l_cpl1_rec.chr_id Is Not Null Then
343 g_msg_name := 'OKC_QA_K_CPL_MISSING';
344 g_token := Null;
345 Else
346 g_msg_name := 'OKC_QA_KL_CPL_MISSING';
347 g_token := g_line;
348 End If;
349 Set_QA_Message(p_chr_id => l_cpl1_rec.chr_id,
350 p_cle_id => l_cpl1_rec.cle_id,
351 p_msg_name => g_msg_name,
352 p_token1 => 'PARTY',
353 p_token1_value => l_cpl1_rec.meaning,
354 p_token2 => g_token);
355
356 -- notify caller of an error
357 x_return_status := OKC_API.G_RET_STS_ERROR;
358 END IF;
359 OPEN l_ctc_csr (l_cpl1_rec.id);
360 LOOP
361 FETCH l_ctc_csr INTO l_ctc_rec;
362 EXIT WHEN l_ctc_csr%NOTFOUND;
363 IF (l_ctc_rec.JTOT_OBJECT1_CODE IS NULL OR
364 l_ctc_rec.OBJECT1_ID1 IS NULL OR
365 l_ctc_rec.OBJECT1_ID2 IS NULL) THEN
366 If l_cpl1_rec.chr_id Is Not Null Then
367 g_msg_name := 'OKC_QA_K_CTC_MISSING';
368 g_token := Null;
369 Else
370 g_msg_name := 'OKC_QA_KL_CTC_MISSING';
371 g_token := g_line;
372 End If;
373 Set_QA_Message(p_chr_id => l_cpl1_rec.chr_id,
374 p_cle_id => l_cpl1_rec.cle_id,
375 p_msg_name => g_msg_name,
376 p_token1 => 'CONTACT',
377 p_token1_value => l_ctc_rec.meaning,
378 p_token2 => 'PARTY',
379 p_token2_value => l_cpl1_rec.meaning,
380 p_token3 => g_token);
381
382 -- notify caller of an error
383 x_return_status := OKC_API.G_RET_STS_ERROR;
384 END IF;
385 END LOOP;
386 CLOSE l_ctc_csr;
387 END LOOP;
388 CLOSE l_cpl1_csr;
389 END IF; -- template_yn = 'N'
390
391 -- Check that the currency on the Contract Line
392 -- is the same as the contract header.
393 /*******
394 OPEN l_clev_csr;
395 LOOP
396 FETCH l_clev_csr INTO l_clev_rec;
397 EXIT WHEN l_clev_csr%NOTFOUND;
398 IF (l_clev_rec.CURRENCY_CODE IS NULL) THEN
399 OKC_API.set_message(
400 p_app_name => G_APP_NAME,
401 p_msg_name => 'OKC_REQUIRED_LINE_CURRENCY',
402 p_token1 => 'LINE_NAME',
403 p_token1_value => l_clev_rec.line_name);
404
405 -- notify caller of an error
406 x_return_status := OKC_API.G_RET_STS_ERROR;
407 ELSIF l_clev_rec.currency_code <> l_chrv_rec.currency_code THEN
408 OKC_API.set_message(
409 p_app_name => G_APP_NAME,
410 p_msg_name => G_INVALID_LINE_CURRENCY,
411 p_token1 => 'LINE_NAME',
412 p_token1_value => l_clev_rec.line_name);
413 -- notify caller of an error
414 x_return_status := OKC_API.G_RET_STS_ERROR;
415 END IF;
416 --bug 2398639 Check if Its price hold top-line then Contract header must have a CAN rule.
417 IF l_clev_rec.lse_id=61 and l_clev_rec.cle_id is null and l_chrv_rec.BUY_OR_SELL='S' THEN
418
419 OPEN l_ph_can_rule(p_chr_id);
420 FETCH l_ph_can_rule into l_can_found;
421 IF l_ph_can_rule%NOTFOUND THEN
422 OKC_API.set_message(p_app_name => g_app_name, --OKC
423 p_msg_name => 'OKC_NO_PRICE_HOLD_CAN');
424 x_return_status := OKC_API.G_RET_STS_ERROR;
425
426 END IF;
427 CLOSE l_ph_can_rule;
428 END IF;
429 -- bug 2398639
430 END LOOP;
431 CLOSE l_clev_csr;
432 *******/
433
434 --bug 5442886
435 OPEN l_clev_csr;
436 LOOP
437 FETCH l_clev_csr BULK COLLECT INTO l_line_name_tbl, l_currency_code_tbl,
438 l_lse_id_tbl, l_cle_id_tbl LIMIT G_BULK_FETCH_LIMIT;
439
440 EXIT WHEN l_line_name_tbl.COUNT = 0;
441
442 FOR i IN l_line_name_tbl.FIRST..l_line_name_tbl.LAST LOOP
443
444 IF (l_currency_code_tbl(i) IS NULL) THEN
445 OKC_API.set_message(
446 p_app_name => G_APP_NAME,
447 p_msg_name => 'OKC_REQUIRED_LINE_CURRENCY',
448 p_token1 => 'LINE_NAME',
449 p_token1_value => l_line_name_tbl(i));
450
451 -- notify caller of an error
452 x_return_status := OKC_API.G_RET_STS_ERROR;
453 ELSIF l_currency_code_tbl(i) <> l_chrv_rec.currency_code THEN
454 OKC_API.set_message(
455 p_app_name => G_APP_NAME,
456 p_msg_name => G_INVALID_LINE_CURRENCY,
457 p_token1 => 'LINE_NAME',
458 p_token1_value => l_line_name_tbl(i));
459 -- notify caller of an error
460 x_return_status := OKC_API.G_RET_STS_ERROR;
461 END IF;
462 --bug 2398639 Check if Its price hold top-line then Contract header must have a CAN rule.
463 IF l_lse_id_tbl(i)=61 and l_cle_id_tbl(i) is null and l_chrv_rec.BUY_OR_SELL='S' THEN
464
465 OPEN l_ph_can_rule(p_chr_id);
466 FETCH l_ph_can_rule into l_can_found;
467 IF l_ph_can_rule%NOTFOUND THEN
468 OKC_API.set_message(p_app_name => g_app_name, --OKC
469 p_msg_name => 'OKC_NO_PRICE_HOLD_CAN');
470 x_return_status := OKC_API.G_RET_STS_ERROR;
471
472 END IF;
473 CLOSE l_ph_can_rule;
474 END IF;
475 -- bug 2398639
476 END LOOP;
477
478 END LOOP;
479 CLOSE l_clev_csr;
480
481
482 --/Rules Migration/
483 --For non-okc,oko contracts check conversion data here
484 If l_chrv_rec.application_id not in (510,871) Then
485 l_func_curr_code := OKC_CURRENCY_API.GET_OU_CURRENCY(l_chrv_rec.authoring_org_id);
486 ---
487 IF l_chrv_rec.currency_code <> l_func_curr_code Then
488 If (l_chrv_rec.conversion_type is null or
489 l_chrv_rec.conversion_rate is null or
490 l_chrv_rec.conversion_rate_date is null)
491 Then
492 OKC_API.set_message(
493 p_app_name => G_APP_NAME,
494 p_msg_name => 'OKC_QA_CONVERSION_DATA');
495
496 --raise error message
497 x_return_status := OKC_API.G_RET_STS_ERROR;
498 End If;
499 End If;
500 End If;
501 ---/Rules Migration/
502
503 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
504 OKC_API.set_message(
505 p_app_name => G_APP_NAME,
506 p_msg_name => G_QA_SUCCESS);
507 END IF;
508
509 IF (l_debug = 'Y') THEN
510 okc_debug.Log('1000: Leaving ',2);
511 okc_debug.Reset_Indentation;
512 END IF;
513 EXCEPTION
514 WHEN G_EXCEPTION_HALT_VALIDATION THEN
515 IF (l_debug = 'Y') THEN
516 okc_debug.Log('2000: Leaving ',2);
517 okc_debug.Reset_Indentation;
518 END IF;
519 -- no processing necessary; validation can continue with next column
520 NULL;
521 WHEN OTHERS THEN
522 IF (l_debug = 'Y') THEN
523 okc_debug.Log('3000: Leaving ',2);
524 okc_debug.Reset_Indentation;
525 END IF;
526 -- store SQL error message on message stack
527 OKC_API.SET_MESSAGE(
528 p_app_name => G_APP_NAME,
529 p_msg_name => G_UNEXPECTED_ERROR,
530 p_token1 => G_SQLCODE_TOKEN,
531 p_token1_value => SQLCODE,
532 p_token2 => G_SQLERRM_TOKEN,
533 p_token2_value => SQLERRM);
534 -- notify caller of an error as UNEXPETED error
535 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
536 -- verify that cursor was closed
537 IF l_chrv_csr%ISOPEN THEN
538 CLOSE l_chrv_csr;
539 END IF;
540 IF l_cpl_csr%ISOPEN THEN
541 CLOSE l_cpl_csr;
542 END IF;
543 IF l_cpl1_csr%ISOPEN THEN
544 CLOSE l_cpl1_csr;
545 END IF;
546 IF l_ctc_csr%ISOPEN THEN
547 CLOSE l_ctc_csr;
548 END IF;
549 IF l_clev_csr%ISOPEN THEN
550 CLOSE l_clev_csr;
551 END IF;
552 END check_required_values;
553 --
554 -- Start of comments
555 --
556 -- Procedure Name : check_rule_values
557 -- Description :
558 -- Business Rules :
559 -- Parameters :
560 -- Version : 1.0
561 -- End of comments
562
563 PROCEDURE check_rule_values(
564 x_return_status OUT NOCOPY VARCHAR2,
565 p_rul_rec IN OKC_RULES_B%ROWTYPE,
566 p_rgd_code IN OKC_RULE_GROUPS_B.rgd_code%TYPE,
567 p_rgp_id IN OKC_RULE_GROUPS_B.id%TYPE,
568 p_chr_id IN OKC_RULE_GROUPS_B.chr_id%TYPE,
569 p_cle_id IN OKC_RULE_GROUPS_B.cle_id%TYPE,
570 p_cls_code IN OKC_SUBCLASSES_B.CLS_CODE%TYPE
571 ) IS
572
573 CURSOR l_cti_csr IS
574 SELECT 'x'
575 FROM OKC_COVER_TIMES cti
576 WHERE cti.rul_id = p_rul_rec.id;
577
578 CURSOR l_ril_csr IS
579 SELECT 'x'
580 FROM OKC_REACT_INTERVALS ril
581 WHERE ril.rul_id = p_rul_rec.id;
582
583 CURSOR l_rgr_csr IS
584 -- SELECT NVL(rgr.optional_yn, 'N')
585 SELECT decode(sign(nvl(rgr.min_cardinality, 0)), 1, 'N', 'Y')
586 FROM OKC_RG_DEF_RULES rgr
587 WHERE rgr.rdf_code = p_rul_rec.rule_information_category
588 AND rgr.rgd_code = p_rgd_code;
589
590 /* -- /striping/
591 CURSOR l_rul_csr IS
592 SELECT fnd.MEANING
593 FROM FND_LOOKUPS fnd
594 WHERE fnd.LOOKUP_CODE = p_rul_rec.RULE_INFORMATION_CATEGORY
595 AND fnd.LOOKUP_TYPE = 'OKC_RULE_DEF';
596 */
597 -- /striping/
598 CURSOR l_rul_csr IS
599 SELECT MEANING
600 FROM okc_rule_defs_v
601 WHERE RULE_CODE = p_rul_rec.RULE_INFORMATION_CATEGORY;
602
603 -- l_rule_def FND_LOOKUPS.MEANING%TYPE; -- /striping/
604 l_rule_def okc_rule_defs_v.MEANING%TYPE;
605
606
607 l_dummy VARCHAR2(1);
608 l_optional_yn OKC_RG_DEF_RULES.OPTIONAL_YN%TYPE;
609
610 l_token VARCHAR2(2000);
611 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
612 TYPE l_info_type IS REF CURSOR;
613 l_info_csr l_info_type;
614 sql_stmt VARCHAR2(2000);
615 l_end_user_column_name FND_DESCR_FLEX_COL_USAGE_VL.END_USER_COLUMN_NAME%TYPE;
616 l_rule_information OKC_RULES_V.RULE_INFORMATION1%TYPE;
617 l_flex_value_set_id FND_DESCR_FLEX_COL_USAGE_VL.FLEX_VALUE_SET_ID%TYPE;
618 l_required_flag FND_DESCR_FLEX_COL_USAGE_VL.REQUIRED_FLAG%TYPE;
619
620 l_missing_values VARCHAR2(1) := 'N';
621 l_all_must_be_present BOOLEAN := False;
622
623 l_adv_pricing_profile VARCHAR2(10) := 'N';
624 l_adv_pricing_warn VARCHAR2(10) := 'N';
625
626 -- skekkar # 1586976
627 -- cursor l_flex_csr(p_rule_cat varchar2, p_attribute varchar2) is -- /striping/
628 cursor l_flex_csr(p_rule_cat varchar2, p_attribute varchar2, appl_id number, dff_name varchar2) is
629 SELECT END_USER_COLUMN_NAME, FLEX_VALUE_SET_ID, REQUIRED_FLAG
630 FROM FND_DESCR_FLEX_COLUMN_USAGES dfcu
631 -- WHERE dfcu.descriptive_flexfield_name = 'OKC Rule Developer DF' -- /striping/
632 WHERE dfcu.descriptive_flexfield_name = dff_name
633 AND dfcu.descriptive_flex_context_code = p_rule_cat
634 AND dfcu.application_column_name = p_attribute
635 -- AND dfcu.application_id =510; -- /striping/
636 AND dfcu.application_id = appl_id;
637 -- skekkar
638
639 --
640 l_proc varchar2(72) := g_package||'check_rule_values';
641 --
642 BEGIN
643
644 IF (l_debug = 'Y') THEN
645 okc_debug.Set_Indentation(l_proc);
646 okc_debug.Log('10: Entering ',2);
647 END IF;
648
649 -- initialize return status
650 x_return_status := OKC_API.G_RET_STS_SUCCESS;
651
652 -- check if the rule is a required rule
653 OPEN l_rgr_csr;
654 FETCH l_rgr_csr INTO l_optional_yn;
655 CLOSE l_rgr_csr;
656
657 -- bug 1965956
658 -- Added By skekkar
659 -- If the Rule Group is 'PRICING' and if the Advanced Pricing Profile is enabled then we will
660 -- ignore all Rules under 'PRICING' rule group and issue a warning to the user
661 IF NVL(p_rgd_code,'XYZ') = 'PRICING' THEN
662 IF (l_debug = 'Y') THEN
663 okc_debug.Log('20: Pricing Rule Group '||p_rgd_code,2);
664 END IF;
665 -- check the Advanced Pricing Profile value
666 l_adv_pricing_profile := nvl(fnd_profile.value('OKC_ADVANCED_PRICING'), 'N');
667 IF (l_debug = 'Y') THEN
668 okc_debug.Log('30: Advance Pricing Profile : '||l_adv_pricing_profile,2);
669 END IF;
670 IF l_adv_pricing_profile = 'Y' THEN
671 -- ignore this Rule and make it optional
672 IF (l_debug = 'Y') THEN
673 okc_debug.Log('40: Setting l_optional_yn to Y ',2);
674 END IF;
675 l_optional_yn := 'Y' ;
676 -- warn the user , done at Rule Group Level
677 IF (l_debug = 'Y') THEN
678 okc_debug.Log('50: Issuing Warning To User ',2);
679 END IF;
680 l_adv_pricing_warn := 'Y';
681 END IF; -- AP is 'Y'
682 END IF; -- Rule Group is PRICING
683
684
685 -- Loop through the rule definitions,
686 -- the +3 is for the integration objects.
687 FOR i IN 1..G_DF_COUNT+3 LOOP
688 IF i = 1 THEN
689 l_rule_information := p_rul_rec.RULE_INFORMATION1;
690 ELSIF i = 2 THEN
691 l_rule_information := p_rul_rec.RULE_INFORMATION2;
692 ELSIF i = 3 THEN
693 l_rule_information := p_rul_rec.RULE_INFORMATION3;
694 ELSIF i = 4 THEN
695 l_rule_information := p_rul_rec.RULE_INFORMATION4;
696 ELSIF i = 5 THEN
697 l_rule_information := p_rul_rec.RULE_INFORMATION5;
698 ELSIF i = 6 THEN
699 l_rule_information := p_rul_rec.RULE_INFORMATION6;
700 ELSIF i = 7 THEN
701 l_rule_information := p_rul_rec.RULE_INFORMATION7;
702 ELSIF i = 8 THEN
703 l_rule_information := p_rul_rec.RULE_INFORMATION8;
704 ELSIF i = 9 THEN
705 l_rule_information := p_rul_rec.RULE_INFORMATION9;
706 ELSIF i = 10 THEN
707 l_rule_information := p_rul_rec.RULE_INFORMATION10;
708 ELSIF i = 11 THEN
709 l_rule_information := p_rul_rec.RULE_INFORMATION11;
710 ELSIF i = 12 THEN
711 l_rule_information := p_rul_rec.RULE_INFORMATION12;
712 ELSIF i = 13 THEN
713 l_rule_information := p_rul_rec.RULE_INFORMATION13;
714 ELSIF i = 14 THEN
715 l_rule_information := p_rul_rec.RULE_INFORMATION14;
716 ELSIF i = 15 THEN
717 l_rule_information := p_rul_rec.RULE_INFORMATION15;
718 ELSIF i = G_DF_COUNT+1 THEN
719 l_rule_information := p_rul_rec.JTOT_OBJECT1_CODE;
720 ELSIF i = G_DF_COUNT+2 THEN
721 l_rule_information := p_rul_rec.JTOT_OBJECT2_CODE;
722 ELSIF i = G_DF_COUNT+3 THEN
723 l_rule_information := p_rul_rec.JTOT_OBJECT3_CODE;
724 END IF;
725
726 /*
727 -- SQL statement to retrieve the developer descriptive flex field information
728 sql_stmt := 'SELECT END_USER_COLUMN_NAME, FLEX_VALUE_SET_ID, REQUIRED_FLAG ' ||
729 ' FROM FND_DESCR_FLEX_COL_USAGE_VL dfcu ' ||
730 ' WHERE dfcu.application_id=510 and dfcu.descriptive_flexfield_name = ' ||
731 ''''||G_DESCRIPTIVE_FLEXFIELD_NAME||'''' ||
732 ' AND dfcu.descriptive_flex_context_code = :rule_cat ' ||
733 ' AND dfcu.application_column_name = :attribute' ;
734 */
735 -- /striping/
736 p_appl_id := okc_rld_pvt.get_appl_id(p_rul_rec.rule_information_category);
737 p_dff_name := okc_rld_pvt.get_dff_name(p_rul_rec.rule_information_category);
738
739 IF i <= G_DF_COUNT THEN
740 -- skekkar # 1586976
741 OPEN l_flex_csr(p_rul_rec.rule_information_category,
742 -- 'RULE_INFORMATION'||LTRIM(TO_CHAR(i)) ); -- /striping/
743 'RULE_INFORMATION'||LTRIM(TO_CHAR(i)), p_appl_id, p_dff_name );
744 FETCH l_flex_csr INTO l_end_user_column_name, l_flex_value_set_id,
745 l_required_flag;
746 -- skekkar
747 /*
748 OPEN l_info_csr
749 FOR sql_stmt
750 USING p_rul_rec.rule_information_category,
751 'RULE_INFORMATION'||LTRIM(TO_CHAR(i));
752 FETCH l_info_csr INTO l_end_user_column_name, l_flex_value_set_id,
753 l_required_flag;
754 */
755 ELSE
756 -- skekkar # 1586976
757 OPEN l_flex_csr(p_rul_rec.rule_information_category,
758 -- 'JTOT_OBJECT'||LTRIM(TO_CHAR(i - G_DF_COUNT))||'_CODE'); -- /striping/
759 'JTOT_OBJECT'||LTRIM(TO_CHAR(i - G_DF_COUNT))||'_CODE', p_appl_id, p_dff_name);
760 FETCH l_flex_csr INTO l_end_user_column_name, l_flex_value_set_id,
761 l_required_flag;
762 -- skekkar
763 /*
764 OPEN l_info_csr
765 FOR sql_stmt
766 USING p_rul_rec.rule_information_category,
767 'JTOT_OBJECT'||LTRIM(TO_CHAR(i - G_DF_COUNT))||'_CODE';
768 FETCH l_info_csr INTO l_end_user_column_name, l_flex_value_set_id,
769 l_required_flag;
770 */
771 END IF;
772
773 IF l_flex_csr%NOTFOUND THEN
774 l_end_user_column_name := NULL;
775 l_flex_value_set_id := NULL;
776 END IF;
777 CLOSE l_flex_csr;
778
779 /*
780 IF l_info_csr%NOTFOUND THEN
781 l_end_user_column_name := NULL;
782 l_flex_value_set_id := NULL;
783 END IF;
784 CLOSE l_info_csr;
785 */
786
787 -- bug 1965956
788 -- Added By skekkar
789 -- If the Rule Group is 'PRICING' and if the Advanced Pricing Profile is enabled then we will
790 -- ignore all Rules under 'PRICING' rule group and issue a warning to the user
791 -- If the Rule Segments have Required values , we set it to Not Required
792
793 IF NVL(p_rgd_code,'XYZ') = 'PRICING' THEN
794 IF (l_debug = 'Y') THEN
795 okc_debug.Log('100: Pricing Rule Group '||p_rgd_code,2);
796 END IF;
797 -- check the Advanced Pricing Profile value
798 l_adv_pricing_profile := nvl(fnd_profile.value('OKC_ADVANCED_PRICING'), 'N');
799 IF (l_debug = 'Y') THEN
800 okc_debug.Log('110: Advance Pricing Profile : '||l_adv_pricing_profile,2);
801 END IF;
802 IF l_adv_pricing_profile = 'Y' THEN
803 -- ignore this Rule and make it optional
804 IF (l_debug = 'Y') THEN
805 okc_debug.Log('120: Setting l_required_flag to N ',2);
806 END IF;
807 l_required_flag := 'N' ;
808 END IF; -- AP is 'Y'
809 END IF; -- Rule Group is PRICING
810
811
812 -- if the attribute has been defined
813 IF l_end_user_column_name IS NOT NULL THEN
814 IF l_optional_yn = 'Y' AND
815 l_rule_information IS NOT NULL THEN
816 l_all_must_be_present := True;
817 END IF;
818 -- check if data is required
819 IF l_required_flag = 'Y' THEN
820 -- data is required
821 IF (l_rule_information = OKC_API.G_MISS_CHAR OR
822 l_rule_information IS NULL) THEN
823 l_missing_values := 'Y';
824 END IF;
825 END IF;
826 END IF;
827 END LOOP;
828
829 -- get the rule meaning for the error messages
830 OPEN l_rul_csr;
831 FETCH l_rul_csr INTO l_rule_def;
832 CLOSE l_rul_csr;
833
834 -- check if required data is missing for a required rule
835 -- or a rule that has some information entered.
836 IF l_missing_values = 'Y' THEN
837 IF ((l_optional_yn = 'N') OR
838 (l_optional_yn = 'Y' AND l_all_must_be_present)) THEN
839 If p_chr_id Is Not Null Then
840 g_msg_name := 'OKC_QA_K_RULE_VAL_MISSING';
841 g_token := Null;
842 Else
843 g_msg_name := 'OKC_QA_KL_RULE_VAL_MISSING';
844 g_token := g_line;
845 End If;
846 Set_QA_Message(p_chr_id => p_chr_id,
847 p_cle_id => p_cle_id,
848 p_msg_name => g_msg_name,
849 p_token1 => 'RULE',
850 p_token1_value => l_rule_def,
851 p_token2 => g_token);
852 -- notify caller of an error
853 x_return_status := OKC_API.G_RET_STS_ERROR;
854 END IF;
855 END IF;
856
857 /*
858 Warning given at Rule Group Level
859 -- skekkar, Bug 1965956
860 -- issue warning if Pricing Rule used and Advanced Pricing Profile is set to Y
861 --
862 IF l_adv_pricing_warn = 'Y' THEN
863 OKC_API.set_message(
864 p_app_name => G_APP_NAME,
865 p_msg_name => 'OKC_PRICING_RULE_WARN');
866 END IF;
867
868 */
869
870 -- There must be at least on child record in OKC_COVER_TIMES for a CVR rule
871 IF (p_rul_rec.rule_information_category = 'CVR') THEN
872 l_dummy := '?';
873 -- check for record in cover times
874 OPEN l_cti_csr;
875 FETCH l_cti_csr INTO l_dummy;
876 CLOSE l_cti_csr;
877
878 IF (l_dummy = '?') THEN
879 If p_chr_id Is Not Null Then
880 g_msg_name := 'OKC_QA_K_CVR_VALUE_MISSING';
881 g_token := Null;
882 Else
883 g_msg_name := 'OKC_QA_KL_CVR_VALUE_MISSING';
884 g_token := g_line;
885 End If;
886 Set_QA_Message(p_chr_id => p_chr_id,
887 p_cle_id => p_cle_id,
888 p_msg_name => g_msg_name,
889 p_token1 => 'RULE',
890 p_token1_value => l_rule_def,
891 p_token2 => g_token);
892 -- notify caller of an error
893 x_return_status := OKC_API.G_RET_STS_ERROR;
894 END IF;
895 END IF;
896
897 -- There must be at least on child record in OKC_REACT_INTERVALS for a rule
898 IF (p_rul_rec.rule_information_category = 'RCN') THEN
899 l_dummy := '?';
900 -- check for record in cover times
901 OPEN l_ril_csr;
902 FETCH l_ril_csr INTO l_dummy;
903 CLOSE l_ril_csr;
904
905 IF (l_dummy = '?') THEN
906 If p_chr_id Is Not Null Then
907 g_msg_name := 'OKC_QA_K_RCN_VALUE_MISSING';
908 g_token := Null;
909 Else
910 g_msg_name := 'OKC_QA_KL_RCN_VALUE_MISSING';
911 g_token := g_line;
912 End If;
913 Set_QA_Message(p_chr_id => p_chr_id,
914 p_cle_id => p_cle_id,
915 p_msg_name => g_msg_name,
916 p_token1 => 'RULE',
917 p_token1_value => l_rule_def,
918 p_token2 => g_token);
919 -- notify caller of an error
920 x_return_status := OKC_API.G_RET_STS_ERROR;
921 END IF;
922 END IF;
923
924 -- Bug 1496111. For Non-Service Contracts, check that for the renewal type of
925 -- Notify Sales Rep 'NSR', the Contact information has been entered.
926 -- Bug 1630898. Contacts must be defined for EVN/SFA also.
927 If p_cls_code <> 'SERVICE' Then
928 IF (p_rul_rec.rule_information_category = 'REN') And
929 (p_rul_rec.rule_information1 IN ('NSR', 'SFA', 'EVN')) And
930 ((p_rul_rec.rule_information2 Is Null) Or
931 (p_rul_rec.rule_information2 = OKC_API.G_MISS_CHAR)) Then
932
933 OKC_API.set_message(
934 p_app_name => G_APP_NAME,
935 p_msg_name => 'OKC_NO_CONTACT_FOR_NSR');
936
937 -- notify caller of an error
938 x_return_status := OKC_API.G_RET_STS_ERROR;
939 END IF;
940 END IF;
941
942
943 IF (l_debug = 'Y') THEN
944 okc_debug.Log('1000: Leaving ',2);
945 okc_debug.Reset_Indentation;
946 END IF;
947 EXCEPTION
948 WHEN G_EXCEPTION_HALT_VALIDATION THEN
949 IF (l_debug = 'Y') THEN
950 okc_debug.Log('2000: Leaving ',2);
951 okc_debug.Reset_Indentation;
952 END IF;
953 -- no processing necessary; validation can continue with next column
954 NULL;
955 WHEN OTHERS THEN
956 IF (l_debug = 'Y') THEN
957 okc_debug.Log('3000: Leaving ',2);
958 okc_debug.Reset_Indentation;
959 END IF;
960 -- store SQL error message on message stack
961 OKC_API.SET_MESSAGE(
962 p_app_name => G_APP_NAME,
963 p_msg_name => G_UNEXPECTED_ERROR,
964 p_token1 => G_SQLCODE_TOKEN,
965 p_token1_value => SQLCODE,
966 p_token2 => G_SQLERRM_TOKEN,
967 p_token2_value => SQLERRM);
968 -- notify caller of an error as UNEXPETED error
969 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
970 -- verify that cursor was closed
971 IF l_rgr_csr%ISOPEN THEN
972 CLOSE l_rgr_csr;
973 END IF;
974 IF l_flex_csr%ISOPEN THEN
975 CLOSE l_flex_csr;
976 END IF;
977 IF l_info_csr%ISOPEN THEN
978 CLOSE l_info_csr;
979 END IF;
980 IF l_cti_csr%ISOPEN THEN
981 CLOSE l_cti_csr;
982 END IF;
983 IF l_rul_csr%ISOPEN THEN
984 CLOSE l_rul_csr;
985 END IF;
986 END check_rule_values;
987 --
988 -- Start of comments
989 --
990 -- Procedure Name : check_rule_groups
991 -- Description :
992 -- Business Rules :
993 -- Parameters :
994 -- Version : 1.0
995 -- End of comments
996
997 PROCEDURE check_rule_groups(
998 x_return_status OUT NOCOPY VARCHAR2,
999 p_chr_id IN NUMBER
1000 ) IS
1001
1002 CURSOR l_chr_csr IS
1003 SELECT chr.*, sub.cls_code
1004 FROM OKC_K_HEADERS_B chr,
1005 OKC_SUBCLASSES_B sub
1006 WHERE chr.id = p_chr_id
1007 AND sub.code = chr.scs_code;
1008 l_chr_rec l_chr_csr%ROWTYPE;
1009
1010 -- use only for getting info about OKC_RULE_GROUP_DEF, don't use OKC_RULE_DEF -- /striping/
1011 CURSOR l_rgpm_csr IS
1012 SELECT fnd.MEANING
1013 FROM FND_LOOKUPS fnd
1014 WHERE fnd.LOOKUP_CODE = 'CURRENCY'
1015 AND fnd.LOOKUP_TYPE = 'OKC_RULE_GROUP_DEF';
1016 -- WHERE fnd.LOOKUP_CODE = 'CVN'
1017 -- AND fnd.LOOKUP_TYPE = 'OKC_RULE_DEF';
1018 l_rgpm_meaning FND_LOOKUPS.MEANING%TYPE;
1019
1020 --
1021 -- modified as part of bug 2155930 (jkodiyan - 26/12/2001)
1022 -- Cursor to check Conversion rule at the header level with
1023 -- all required values
1024 -- p_rgd_code = 'SVC_K' for service contracts and
1025 -- = 'CURRENCY' for core contracts
1026 --
1027 CURSOR l_cvn_csr(p_rgd_code VARCHAR2) IS
1028 SELECT 'x'
1029 FROM OKC_RULE_GROUPS_B rgp, OKC_RULES_B rul
1030 WHERE rgp.rgd_code = p_rgd_code
1031 AND rgp.dnz_chr_id = p_chr_id
1032 AND rul.dnz_chr_id = rgp.dnz_chr_id
1033 AND rgp.cle_id is null
1034 AND rul.rgp_id = rgp.id
1035 AND rul.rule_information_category = 'CVN'
1036 AND rul.rule_information1 is not null
1037 AND rul.rule_information2 is not null
1038 AND rul.object1_id1 is not null;
1039 /* -- /striping/
1040 CURSOR l_rgp_csr IS
1041 SELECT rgp.id, rgp.rgd_code, fnd.meaning rule_group_name,
1042 rgp.chr_id, rgp.cle_id,
1043 rgr.rdf_code, rgr.min_cardinality, rgr.max_cardinality,
1044 fnd1.meaning rule_name
1045 FROM FND_LOOKUPS fnd,
1046 FND_LOOKUPS fnd1,
1047 OKC_RG_DEF_RULES rgr,
1048 OKC_RULE_GROUPS_B rgp
1049 WHERE rgp.dnz_chr_id = p_chr_id
1050 AND fnd.lookup_code = rgp.rgd_code
1051 AND fnd.lookup_type = 'OKC_RULE_GROUP_DEF'
1052 AND rgr.rgd_code = rgp.rgd_code
1053 AND rgr.rdf_code = fnd1.lookup_code
1054 AND fnd1.lookup_type = 'OKC_RULE_DEF'
1055 ORDER BY rgp.id;
1056 */
1057 -- /striping/
1058 CURSOR l_rgp_csr IS
1059 SELECT rgp.id, rgp.rgd_code, fnd.meaning rule_group_name,
1060 rgp.chr_id, rgp.cle_id,
1061 rgr.rdf_code, rgr.min_cardinality, rgr.max_cardinality,
1062 fnd1.meaning rule_name
1063 FROM FND_LOOKUPS fnd,
1064 okc_rule_defs_v fnd1,
1065 OKC_RG_DEF_RULES rgr,
1066 OKC_RULE_GROUPS_B rgp
1067 WHERE rgp.dnz_chr_id = p_chr_id
1068 AND fnd.lookup_code = rgp.rgd_code
1069 AND fnd.lookup_type = 'OKC_RULE_GROUP_DEF'
1070 AND rgr.rgd_code = rgp.rgd_code
1071 AND rgr.rdf_code = fnd1.rule_code
1072 ORDER BY rgp.id;
1073
1074 l_rgp_rec l_rgp_csr%ROWTYPE;
1075
1076 /* CURSOR l_rgr_csr(p_rgd_code IN okc_rg_def_rules.rgd_code%TYPE) IS
1077 SELECT rgr.rdf_code, rgr.min_cardinality, rgr.max_cardinality,
1078 fnd.meaning
1079 FROM OKC_RG_DEF_RULES rgr,
1080 FND_LOOKUPS fnd
1081 WHERE rgr.rgd_code = p_rgd_code
1082 AND rgr.rdf_code = fnd.lookup_code
1083 AND fnd.lookup_type = 'OKC_RULE_DEF';
1084 l_rgr_rec l_rgr_csr%ROWTYPE; */
1085
1086 CURSOR l_rul_cnt_csr(p_rgp_id IN okc_rules_b.rgp_id%TYPE,
1087 p_rdf_code IN okc_rules_b.rule_information_category%TYPE) IS
1088 SELECT count(*)
1089 FROM OKC_RULES_B rul
1090 WHERE rul.rule_information_category = p_rdf_code
1091 AND rul.rgp_id = p_rgp_id;
1092 l_rule_count Number;
1093
1094 CURSOR l_rul_csr IS
1095 SELECT rul.*
1096 FROM OKC_RULES_B rul
1097 WHERE rgp_id = l_rgp_rec.id;
1098
1099 l_rul_rec OKC_RULES_B%ROWTYPE;
1100
1101 CURSOR l_cle_csr(p_id okc_k_lines_b.id%TYPE) IS
1102 SELECT cle.chr_id
1103 FROM OKC_K_LINES_B cle
1104 WHERE cle.id = p_id;
1105 l_cle_rec l_cle_csr%ROWTYPE;
1106
1107 l_prev_rgp_rec_id Number := -1;
1108 l_token VARCHAR2(2000);
1109 l_dummy VARCHAR2(1);
1110 l_optional_yn OKC_RG_DEF_RULES.OPTIONAL_YN%TYPE;
1111 l_func_curr_code OKC_K_HEADERS_B.CURRENCY_CODE%TYPE;
1112 l_return_status VARCHAR2(1);
1113
1114 l_adv_pricing_profile VARCHAR2(10) := 'N';
1115 l_adv_pricing_warn VARCHAR2(10) := 'N';
1116
1117 --
1118 l_proc varchar2(72) := g_package||'check_rule_groups';
1119 --
1120 BEGIN
1121
1122 IF (l_debug = 'Y') THEN
1123 okc_debug.Set_Indentation(l_proc);
1124 okc_debug.Log('10: Entering ',2);
1125 END IF;
1126
1127 -- initialize return status
1128 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1129
1130 -- Get Contract Header info
1131 OPEN l_chr_csr;
1132 FETCH l_chr_csr INTO l_chr_rec;
1133 CLOSE l_chr_csr;
1134
1135 --/Rules migration/
1136 --Rule group check should not be performed for other contract catregories
1137 --apart from OKC/OKO/OKL
1138
1139 If l_chr_rec.application_id not in (510,871,540) Then
1140 RAISE G_EXCEPTION_HALT_VALIDATION;
1141 End If;
1142 --/Rules migration/
1143
1144 -- get the rules attached to the contract
1145 OPEN l_rgp_csr;
1146 LOOP
1147 FETCH l_rgp_csr INTO l_rgp_rec;
1148 EXIT WHEN l_rgp_csr%NOTFOUND;
1149 --
1150 -- Check that all required rules for the rule group
1151 -- have been associated.
1152 -- l_dummy := '?';
1153 /* For l_rgr_rec in l_rgr_csr(l_rgp_rec.rgd_code) Loop */
1154 Open l_rul_cnt_csr(l_rgp_rec.id, l_rgp_rec.rdf_code);
1155 Fetch l_rul_cnt_csr Into l_rule_count;
1156 Close l_rul_cnt_csr;
1157
1158 -- bug 1965956
1159 -- Added By skekkar
1160 -- If the Rule Group is 'PRICING' and if the Advanced Pricing Profile is enabled then we will
1161 -- ignore all Rules under 'PRICING' rule group and issue a warning to the user
1162 -- set the l_rule_count to min_cardinality
1163
1164 IF NVL(l_rgp_rec.rgd_code,'XYZ') = 'PRICING' THEN
1165 IF (l_debug = 'Y') THEN
1166 okc_debug.Log('200: Pricing Rule Group '||l_rgp_rec.rgd_code,2);
1167 END IF;
1168 -- check the Advanced Pricing Profile value
1169 l_adv_pricing_profile := nvl(fnd_profile.value('OKC_ADVANCED_PRICING'), 'N');
1170 IF (l_debug = 'Y') THEN
1171 okc_debug.Log('210: Advance Pricing Profile : '||l_adv_pricing_profile,2);
1172 END IF;
1173 IF l_adv_pricing_profile = 'Y' THEN
1174 -- ignore this Rule and make it optional
1175 IF (l_debug = 'Y') THEN
1176 okc_debug.Log('220: Setting l_rule_count to min_cardinality ',2);
1177 END IF;
1178 l_rule_count := nvl(l_rgp_rec.min_cardinality, 0);
1179 -- issue Warning to the user
1180 l_adv_pricing_warn := 'Y';
1181 END IF; -- AP is 'Y'
1182 END IF; -- Rule Group is PRICING
1183
1184 IF l_rule_count < l_rgp_rec.min_cardinality Then
1185 If l_rgp_rec.chr_id Is Not Null Then
1186 g_msg_name := 'OKC_QA_K_RULE_MIN';
1187 g_token := Null;
1188 Else
1189 g_msg_name := 'OKC_QA_KL_RULE_MIN';
1190 g_token := g_line;
1191 End If;
1192 Set_QA_Message(p_chr_id => l_rgp_rec.chr_id,
1193 p_cle_id => l_rgp_rec.cle_id,
1194 p_msg_name => g_msg_name,
1195 p_token1 => 'NUM_OF_RULE',
1196 p_token1_value => l_rgp_rec.min_cardinality,
1197 p_token2 => 'RULE',
1198 p_token2_value => l_rgp_rec.rule_name,
1199 p_token3 => g_token);
1200 -- notify caller of an error
1201 x_return_status := OKC_API.G_RET_STS_ERROR;
1202 END IF;
1203 --
1204 IF l_rule_count > l_rgp_rec.max_cardinality Then
1205 If l_rgp_rec.chr_id Is Not Null Then
1206 g_msg_name := 'OKC_QA_K_RULE_MAX';
1207 g_token := Null;
1208 Else
1209 g_msg_name := 'OKC_QA_KL_RULE_MAX';
1210 g_token := g_line;
1211 End If;
1212 Set_QA_Message(p_chr_id => l_rgp_rec.chr_id,
1213 p_cle_id => l_rgp_rec.cle_id,
1214 p_msg_name => g_msg_name,
1215 p_token1 => 'NUM_OF_RULE',
1216 p_token1_value => l_rgp_rec.max_cardinality,
1217 p_token2 => 'RULE',
1218 p_token2_value => l_rgp_rec.rule_name,
1219 p_token3 => g_token);
1220 -- notify caller of an error
1221 x_return_status := OKC_API.G_RET_STS_ERROR;
1222 END IF;
1223 -- End Loop; -- l_rgr_csr
1224
1225 If l_rgp_rec.id <> l_prev_rgp_rec_id Then
1226 OPEN l_rul_csr;
1227 LOOP
1228 FETCH l_rul_csr INTO l_rul_rec;
1229 EXIT WHEN l_rul_csr%NOTFOUND;
1230 check_rule_values(l_return_status, l_rul_rec,
1231 l_rgp_rec.rgd_code, l_rgp_rec.id,
1232 l_rgp_rec.chr_id, l_rgp_rec.cle_id,
1233 l_chr_rec.cls_code);
1234 IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1235 -- notify caller of an error
1236 x_return_status := OKC_API.G_RET_STS_ERROR;
1237 END IF;
1238 END LOOP; -- l_rul_csr;
1239 CLOSE l_rul_csr;
1240 l_prev_rgp_rec_id := l_rgp_rec.id;
1241 End If;
1242 END LOOP; -- l_rgp_csr
1243 CLOSE l_rgp_csr;
1244
1245 -- Bug 2357950
1246 --l_func_curr_code := OKC_CURRENCY_API.GET_OU_CURRENCY();
1247 l_func_curr_code := OKC_CURRENCY_API.GET_OU_CURRENCY(l_chr_rec.authoring_org_id);
1248 ---
1249 IF l_chr_rec.currency_code <> l_func_curr_code AND
1250 --/rules migration/
1251 --Don't check conversion rule fro Non OKC/OKO contracts
1252 l_chr_rec.application_id in (510,871) THEN
1253 -- Check for conversion rule, which is required if
1254 -- currency code is different from the functional currency
1255 l_dummy := '?';
1256
1257 -- modified for bug# 1413682 - tsaifee 09/21/00
1258 -- For service Ks use l_cvn_csr which uses SVC_K rule grp.
1259 -- modified for bug# 2155930 - jkodiyan 26/12/01
1260 --
1261 IF l_chr_rec.cls_code = 'SERVICE' then -- if SERVICE, then use
1262 OPEN l_cvn_csr('SVC_K');
1263 ELSE
1264 OPEN l_cvn_csr('CURRENCY');
1265 END IF;
1266
1267 FETCH l_cvn_csr INTO l_dummy;
1268 CLOSE l_cvn_csr;
1269
1270 IF (l_dummy <> 'x') THEN
1271 -- get the rule group meaning for the error message
1272 OPEN l_rgpm_csr;
1273 FETCH l_rgpm_csr INTO l_rgpm_meaning;
1274 CLOSE l_rgpm_csr;
1275
1276 OKC_API.set_message(
1277 p_app_name => G_APP_NAME,
1278 p_msg_name => G_REQUIRED_RULE_GROUP,
1279 p_token1 => 'RULE_GROUP',
1280 p_token1_value => l_rgpm_meaning);
1281 -- notify caller of an error
1282 x_return_status := OKC_API.G_RET_STS_ERROR;
1283 END IF;
1284 END IF;
1285
1286
1287 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1288
1289 -- since there were no errors , we will issue warning depending on l_adv_pricing_warn flag
1290 -- Bug 1965956
1291 -- skekkar
1292 -- issue warning if Pricing Rule Group used and Advanced Pricing Profile is set to Y
1293 --
1294 IF l_adv_pricing_warn = 'Y' THEN
1295 OKC_API.set_message(
1296 p_app_name => G_APP_NAME,
1297 p_msg_name => 'OKC_PRICING_RULE_WARN');
1298 -- notify caller of an warning
1299 x_return_status := OKC_API.G_RET_STS_WARNING;
1300 ELSE
1301 OKC_API.set_message(
1302 p_app_name => G_APP_NAME,
1303 p_msg_name => G_QA_SUCCESS);
1304 END IF; -- l_adv_pricing_warn = 'Y'
1305
1306 END IF; -- x_return_status is SUCCESS
1307
1308
1309 IF (l_debug = 'Y') THEN
1310 okc_debug.Log('1000: Leaving ',2);
1311 okc_debug.Reset_Indentation;
1312 END IF;
1313 EXCEPTION
1314 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1315 IF (l_debug = 'Y') THEN
1316 okc_debug.Log('2000: Leaving ',2);
1317 okc_debug.Reset_Indentation;
1318 END IF;
1319 -- no processing necessary; validation can continue with next column
1320 NULL;
1321 WHEN OTHERS THEN
1322 IF (l_debug = 'Y') THEN
1323 okc_debug.Log('3000: Leaving ',2);
1324 okc_debug.Reset_Indentation;
1325 END IF;
1326 -- store SQL error message on message stack
1327 OKC_API.SET_MESSAGE(
1328 p_app_name => G_APP_NAME,
1329 p_msg_name => G_UNEXPECTED_ERROR,
1330 p_token1 => G_SQLCODE_TOKEN,
1331 p_token1_value => SQLCODE,
1332 p_token2 => G_SQLERRM_TOKEN,
1333 p_token2_value => SQLERRM);
1334 -- notify caller of an error as UNEXPETED error
1335 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1336 -- verify that cursor was closed
1337 /* IF l_rgr_csr%ISOPEN THEN
1338 CLOSE l_rgr_csr;
1339 END IF; */
1340 IF l_rgp_csr%ISOPEN THEN
1341 CLOSE l_rgp_csr;
1342 END IF;
1343 IF l_rul_csr%ISOPEN THEN
1344 CLOSE l_rgp_csr;
1345 END IF;
1346 IF l_chr_csr%ISOPEN THEN
1347 CLOSE l_chr_csr;
1348 END IF;
1349 IF l_rgpm_csr%ISOPEN THEN
1350 CLOSE l_rgpm_csr;
1351 END IF;
1352 END check_rule_groups;
1353 --
1354 -- Start of comments
1355 --
1356 -- Procedure Name : check_rule_group_parties
1357 -- Description :
1358 -- Business Rules :
1359 -- Parameters :
1360 -- Version : 1.0
1361 -- End of comments
1362
1363 PROCEDURE check_rule_group_parties(
1364 x_return_status OUT NOCOPY VARCHAR2,
1365 p_chr_id IN NUMBER
1366 ) IS
1367
1368 CURSOR l_chr_csr IS
1369 SELECT chr.template_yn,chr.application_id
1370 FROM OKC_K_HEADERS_B chr
1371 WHERE chr.id = p_chr_id;
1372 l_template_yn OKC_K_HEADERS_B.TEMPLATE_YN%TYPE;
1373
1374 CURSOR l_rrd_csr IS
1375 SELECT rgp.id "RGP_ID", rrd.id "RRD_ID", rgp.rgd_code , sre.rle_code,
1376 fndrgd.meaning "RGD_MEANING", fndrle.meaning "RLE_MEANING",
1377 rgp.chr_id, rgp.cle_id
1378 FROM FND_LOOKUPS fndrle,
1379 FND_LOOKUPS fndrgd,
1380 OKC_SUBCLASS_ROLES sre,
1381 OKC_RG_ROLE_DEFS rrd,
1382 OKC_SUBCLASS_RG_DEFS srd,
1383 OKC_RULE_GROUPS_B rgp,
1384 OKC_K_HEADERS_B chr
1385 WHERE fndrle.LOOKUP_CODE = sre.rle_code
1386 AND fndrle.LOOKUP_TYPE = 'OKC_ROLE'
1387 AND fndrgd.LOOKUP_CODE = srd.rgd_code
1388 AND fndrgd.LOOKUP_TYPE = 'OKC_RULE_GROUP_DEF'
1389 AND sre.id = rrd.sre_id
1390 AND NVL(rrd.optional_yn, 'N') = 'N'
1391 AND rrd.srd_id = srd.id
1392 AND srd.rgd_code = rgp.rgd_code
1393 AND srd.scs_code = chr.scs_code
1394 AND rgp.dnz_chr_id = chr.id
1395 AND chr.id = p_chr_id;
1396 l_rrd_rec l_rrd_csr%ROWTYPE;
1397
1398 CURSOR l_rpr_csr IS
1399 SELECT rpr.cpl_id
1400 FROM OKC_RG_PARTY_ROLES rpr
1401 WHERE rpr.rgp_id = l_rrd_rec.rgp_id
1402 AND rpr.rrd_id = l_rrd_rec.rrd_id;
1403 -- WHERE (rpr.cpl_id IS NOT NULL or l_template_yn = 'Y')
1404
1405 l_cpl_id OKC_RG_PARTY_ROLES.CPL_ID%TYPE;
1406
1407 CURSOR l_kpr_csr(p_id OKC_K_PARTY_ROLES_B.ID%TYPE) IS
1408 SELECT kpr.rle_code, fl.meaning
1409 FROM OKC_K_PARTY_ROLES_B kpr,
1410 FND_LOOKUPS fl
1411 WHERE kpr.id = p_id
1412 AND fl.lookup_type = 'OKC_ROLE'
1413 AND fl.lookup_code = kpr.rle_code;
1414
1415 l_kpr_rec l_kpr_csr%ROWTYPE;
1416 l_row_notfound BOOLEAN;
1417 l_token VARCHAR2(2000);
1418
1419 l_adv_pricing_profile VARCHAR2(10) := 'N';
1420 l_appl_id NUMBER;
1421 --
1422 l_proc varchar2(72) := g_package||'check_rule_group_parties';
1423 --
1424
1425 PROCEDURE Set_Rule_Party_Message IS
1426 l_token VARCHAR2(2000);
1427 --
1428 l_proc varchar2(72) := g_package||'Set_Rule_Party_Message';
1429 --
1430 BEGIN
1431
1432 IF (l_debug = 'Y') THEN
1433 okc_debug.Set_Indentation(l_proc);
1434 okc_debug.Log('10: Entering ',2);
1435 END IF;
1436 If l_rrd_rec.chr_id Is Not Null Then
1437 g_msg_name := 'OKC_QA_K_RGP_ROLE_MISSING';
1438 g_token := Null;
1439 Else
1440 g_msg_name := 'OKC_QA_KL_RGP_ROLE_MISSING';
1441 g_token := g_line;
1442 End If;
1443 Set_QA_Message(p_chr_id => l_rrd_rec.chr_id,
1444 p_cle_id => l_rrd_rec.cle_id,
1445 p_msg_name => g_msg_name,
1446 p_token1 => 'ROLE',
1447 p_token1_value => l_rrd_rec.rle_meaning,
1448 p_token2 => 'RULE_GROUP',
1449 p_token2_value => l_rrd_rec.rgd_meaning,
1450 p_token3 => g_token);
1451
1452 IF (l_debug = 'Y') THEN
1453 okc_debug.Log('1000: Leaving ',2);
1454 okc_debug.Reset_Indentation;
1455 END IF;
1456 END;
1457
1458 BEGIN
1459
1460 IF (l_debug = 'Y') THEN
1461 okc_debug.Set_Indentation(l_proc);
1462 okc_debug.Log('10: Entering ',2);
1463 END IF;
1464
1465 -- initialize return status
1466 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1467
1468 -- get template info from contract header
1469 OPEN l_chr_csr;
1470 FETCH l_chr_csr INTO l_template_yn,l_appl_id;
1471 CLOSE l_chr_csr;
1472
1473 --/Rules migration/
1474 --Rule party check should not be performed for other contract catregories
1475 --apart from OKC/OKO/OKL
1476
1477 If l_appl_id not in (510,871,540) Then
1478 RAISE G_EXCEPTION_HALT_VALIDATION;
1479 End If;
1480 --/Rules migration/
1481
1482 --
1483 -- For all of the rules attached to a contract
1484 -- check that all required rule party roles have
1485 -- been assigned.
1486 OPEN l_rrd_csr;
1487 LOOP
1488 FETCH l_rrd_csr INTO l_rrd_rec;
1489 EXIT WHEN l_rrd_csr%NOTFOUND;
1490 --
1491 -- Check that all required party roles have been
1492 -- attached to a rule group.
1493 OPEN l_rpr_csr;
1494 LOOP
1495 FETCH l_rpr_csr INTO l_cpl_id;
1496 EXIT WHEN l_rpr_csr%NOTFOUND;
1497 IF l_template_yn = 'N' THEN
1498 IF l_cpl_id IS NULL THEN
1499 Set_Rule_Party_Message;
1500 x_return_status := OKC_API.G_RET_STS_ERROR;
1501 ELSE
1502 OPEN l_kpr_csr(l_cpl_id);
1503 FETCH l_kpr_csr INTO l_kpr_rec;
1504 l_row_notfound := l_kpr_csr%NOTFOUND;
1505 CLOSE l_kpr_csr;
1506 IF l_row_notfound THEN
1507 If l_rrd_rec.chr_id Is Not Null Then
1508 g_msg_name := 'OKC_QA_K_RULE_ROLE_DELETED';
1509 g_token := Null;
1510 Else
1511 g_msg_name := 'OKC_QA_KL_RULE_ROLE_DELETED';
1512 g_token := g_line;
1513 End If;
1514 Set_QA_Message(p_chr_id => l_rrd_rec.chr_id,
1515 p_cle_id => l_rrd_rec.cle_id,
1516 p_msg_name => g_msg_name,
1517 p_token1 => 'ROLE',
1518 p_token1_value => l_rrd_rec.rle_meaning,
1519 p_token2 => 'RULE_GROUP',
1520 p_token2_value => l_rrd_rec.rgd_meaning,
1521 p_token3 => g_token);
1522 x_return_status := OKC_API.G_RET_STS_ERROR;
1523 ELSIF l_kpr_rec.rle_code <> l_rrd_rec.rle_code THEN
1524 If l_rrd_rec.chr_id Is Not Null Then
1525 g_msg_name := 'OKC_QA_K_RULE_ROLE_CHANGED';
1526 g_token := Null;
1527 Else
1528 g_msg_name := 'OKC_QA_KL_RULE_ROLE_CHANGED';
1529 g_token := g_line;
1530 End If;
1531 Set_QA_Message(p_chr_id => l_rrd_rec.chr_id,
1532 p_cle_id => l_rrd_rec.cle_id,
1533 p_msg_name => g_msg_name,
1534 p_token1 => 'ROLE1',
1535 p_token1_value => l_rrd_rec.rle_meaning,
1536 p_token2 => 'RULE_GROUP',
1537 p_token2_value => l_rrd_rec.rgd_meaning,
1538 p_token3 => 'ROLE2',
1539 p_token3_value => l_kpr_rec.meaning,
1540 p_token4 => g_token);
1541 x_return_status := OKC_API.G_RET_STS_ERROR;
1542 END IF;
1543 END IF;
1544 END IF;
1545 END LOOP; --l_rpr_csr
1546
1547 IF l_rpr_csr%ROWCOUNT <= 0 THEN
1548 -- Bug 1965956
1549 -- skekkar
1550 -- check if the Rule Group is Pricing
1551 IF (l_debug = 'Y') THEN
1552 okc_debug.Log('20:Rowcount is 0 , rgd_code is : '||l_rrd_rec.rgd_code,2);
1553 END IF;
1554 IF NVL(l_rrd_rec.rgd_code,'XYZ') = 'PRICING' THEN
1555 -- check the Advanced Pricing Profile value
1556 l_adv_pricing_profile := nvl(fnd_profile.value('OKC_ADVANCED_PRICING'), 'N');
1557 IF (l_debug = 'Y') THEN
1558 okc_debug.Log('40: Advance Pricing Profile : '||l_adv_pricing_profile,2);
1559 END IF;
1560
1561 IF l_adv_pricing_profile = 'N' THEN
1562 -- Rule Group Party Role is missing.
1563 -- notify caller of an error
1564 IF (l_debug = 'Y') THEN
1565 okc_debug.Log('100: Rule Group Party Role is missing ',2);
1566 END IF;
1567 Set_Rule_Party_Message;
1568 x_return_status := OKC_API.G_RET_STS_ERROR;
1569 END IF; -- l_adv_pricing_profile is N
1570
1571 ELSE
1572 -- rule group is NOT Pricing, error
1573 IF (l_debug = 'Y') THEN
1574 okc_debug.Log('50: rule group is NOT Pricing , error ',2);
1575 END IF;
1576 -- Rule Group Party Role is missing.
1577 -- notify caller of an error
1578 IF (l_debug = 'Y') THEN
1579 okc_debug.Log('200: Rule Group Party Role is missing ',2);
1580 END IF;
1581 Set_Rule_Party_Message;
1582 x_return_status := OKC_API.G_RET_STS_ERROR;
1583 END IF; -- rule group is Pricing
1584
1585 END IF; -- rowcount is 0
1586 CLOSE l_rpr_csr;
1587 END LOOP; --l_rrd_csr
1588 CLOSE l_rrd_csr;
1589
1590 -- notify caller of success
1591 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1592 OKC_API.set_message(
1593 p_app_name => G_APP_NAME,
1594 p_msg_name => G_QA_SUCCESS);
1595 END IF;
1596
1597
1598 IF (l_debug = 'Y') THEN
1599 okc_debug.Log('1000: Leaving ',2);
1600 okc_debug.Reset_Indentation;
1601 END IF;
1602 EXCEPTION
1603 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1604 IF (l_debug = 'Y') THEN
1605 okc_debug.Log('2000: Leaving ',2);
1606 okc_debug.Reset_Indentation;
1607 END IF;
1608 -- no processing necessary; validation can continue with next column
1609 NULL;
1610 WHEN OTHERS THEN
1611 IF (l_debug = 'Y') THEN
1612 okc_debug.Log('3000: Leaving ',2);
1613 okc_debug.Reset_Indentation;
1614 END IF;
1615 -- store SQL error message on message stack
1616 OKC_API.SET_MESSAGE(
1617 p_app_name => G_APP_NAME,
1618 p_msg_name => G_UNEXPECTED_ERROR,
1619 p_token1 => G_SQLCODE_TOKEN,
1620 p_token1_value => SQLCODE,
1621 p_token2 => G_SQLERRM_TOKEN,
1622 p_token2_value => SQLERRM);
1623 -- notify caller of an error as UNEXPETED error
1624 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1625 -- verify that cursor was closed
1626 IF l_chr_csr%ISOPEN THEN
1627 CLOSE l_chr_csr;
1628 END IF;
1629 IF l_rrd_csr%ISOPEN THEN
1630 CLOSE l_rrd_csr;
1631 END IF;
1632 IF l_rpr_csr%ISOPEN THEN
1633 CLOSE l_rpr_csr;
1634 END IF;
1635 END check_rule_group_parties;
1636 --
1637 -- Start of comments
1638 --
1639 -- Procedure Name : check_effectivity_dates
1640 -- Description :
1641 -- Business Rules :
1642 -- Parameters :
1643 -- Version : 1.0
1644 -- End of comments
1645
1646 PROCEDURE check_effectivity_dates(
1647 x_return_status OUT NOCOPY VARCHAR2,
1648 p_chr_id IN NUMBER) IS
1649
1650 CURSOR l_chrv_csr IS
1651 SELECT start_date, end_date
1652 FROM OKC_K_HEADERS_B chrv
1653 WHERE chrv.id = p_chr_id;
1654
1655 CURSOR l_cle_csr IS
1656 /**
1657 SELECT level, start_date, end_date, line_number,
1658 id, chr_id, cle_id,lse_id
1659 FROM OKC_K_LINES_B
1660 WHERE date_cancelled is null --changes [llc]-- bug #4727744 -- to ignore cancelled lines
1661 START WITH chr_id = p_chr_id
1662 CONNECT BY PRIOR id = cle_id
1663 AND date_cancelled is NULL; --changes [llc]-- to ignore cancelled sublines
1664 **/
1665 --bug 5442886
1666 SELECT kla.start_date, kla.end_date,
1667 kla.id, kla.chr_id, kla.lse_id,
1668 --
1669 klb.start_date parent_start_date, klb.end_date parent_end_date
1670 FROM okc_k_lines_b kla,
1671 okc_k_lines_b klb
1672 WHERE kla.dnz_chr_id = p_chr_id
1673 AND kla.cle_id = klb.id (+)
1674 AND kla.date_cancelled is NULL
1675 AND klb.date_cancelled is NULL;
1676
1677
1678 CURSOR l_clev_csr (p_cle_id NUMBER) IS
1679 SELECT Rtrim(Rtrim(cle.line_number) || ', ' || lse.name ||
1680 ' ' || cle.name) "LINE_NAME"
1681 FROM OKC_LINE_STYLES_TL lse,
1682 OKC_K_LINES_V cle
1683 WHERE cle.id = p_cle_id
1684 and lse.id = cle.lse_id
1685 and lse.language = userenv('LANG');
1686
1687 l_chrv_rec l_chrv_csr%ROWTYPE;
1688 l_line_name Varchar2(2000);
1689
1690
1691 -- l_cle_rec l_cle_csr%ROWTYPE;
1692 --TYPE l_cle_tbl_type is table of l_cle_csr%ROWTYPE INDEX BY BINARY_INTEGER;
1693 --l_cle_tbl l_cle_tbl_type;
1694
1695
1696 TYPE dte_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1697 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1698
1699 l_start_date_tbl dte_tbl_type;
1700 l_end_date_tbl dte_tbl_type;
1701 l_id_tbl num_tbl_type;
1702 l_chr_id_tbl num_tbl_type;
1703 l_lse_id_tbl num_tbl_type;
1704 l_parent_start_date_tbl dte_tbl_type;
1705 l_parent_end_date_tbl dte_tbl_type;
1706
1707
1708 l_clev_rec l_clev_csr%ROWTYPE;
1709 l_index Number;
1710 l_prev_level Number := 0;
1711 l_parent_start_date DATE;
1712 l_parent_end_date DATE;
1713 l_parent_found Boolean;
1714 l_row_notfound Boolean;
1715 l_error_number Varchar2(4);
1716 l_return_status Varchar2(1);
1717
1718 --
1719 l_proc varchar2(72) := g_package||'check_effectivity_dates';
1720 --
1721 BEGIN
1722
1723 IF (l_debug = 'Y') THEN
1724 okc_debug.Set_Indentation(l_proc);
1725 okc_debug.Log('10: Entering ',2);
1726 END IF;
1727
1728 -- initialize return status
1729 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1730
1731 -- check that start date is less than end date
1732 OPEN l_chrv_csr;
1733 FETCH l_chrv_csr INTO l_chrv_rec;
1734 CLOSE l_chrv_csr;
1735
1736 -- data is required
1737 IF (l_chrv_rec.start_date IS NULL) THEN
1738 OKC_API.set_message(
1739 p_app_name => G_APP_NAME,
1740 p_msg_name => 'OKC_QA_START_DATE_REQUIRED');
1741
1742 -- notify caller of an error
1743 x_return_status := OKC_API.G_RET_STS_ERROR;
1744
1745 -- halt validation
1746 RAISE G_EXCEPTION_HALT_VALIDATION;
1747 END IF;
1748
1749 IF (l_chrv_rec.start_date > NVL(l_chrv_rec.end_date, l_chrv_rec.start_date)) THEN
1750 OKC_API.set_message(
1751 p_app_name => G_APP_NAME,
1752 p_msg_name => G_INVALID_END_DATE);
1753 -- notify caller of an error
1754 x_return_status := OKC_API.G_RET_STS_ERROR;
1755
1756 -- halt validation
1757 RAISE G_EXCEPTION_HALT_VALIDATION;
1758 END IF;
1759
1760 -- Loop thru the contract sub lines
1761
1762 /***********************************************
1763 FOR l_cle_rec IN l_cle_csr LOOP
1764 If l_cle_rec.chr_id Is Not Null Then
1765 l_parent_start_date := l_chrv_rec.start_date;
1766 l_parent_end_date := l_chrv_rec.end_date;
1767 l_cle_tbl.delete;
1768 l_index := 1;
1769 Else
1770 If l_cle_rec.level <> l_prev_level Then
1771 l_parent_found := False;
1772 If l_cle_tbl.COUNT > 0 Then
1773 FOR i IN l_cle_tbl.FIRST .. l_cle_tbl.LAST LOOP
1774 If l_cle_tbl(i).id = l_cle_rec.cle_id Then
1775 l_parent_start_date := l_cle_tbl(i).start_date;
1776 l_parent_end_date := l_cle_tbl(i).end_date;
1777 l_parent_found := True;
1778 Exit;
1779 End If;
1780 End Loop;
1781 End If;
1782 If Not l_parent_found Then
1783 -- Control should never reach here. It means something
1784 -- wrong with the data.
1785 l_parent_start_date := l_chrv_rec.start_date;
1786 l_parent_end_date := l_chrv_rec.end_date;
1787 End If;
1788 End If;
1789 End If;
1790 l_cle_tbl(l_index).id := l_cle_rec.id;
1791 l_cle_tbl(l_index).start_date := l_cle_rec.start_date;
1792 l_cle_tbl(l_index).end_date := l_cle_rec.end_date;
1793 l_index := l_index + 1;
1794 l_prev_level := l_cle_rec.level;
1795 l_error_number := '0000';
1796
1797 -- data is required
1798 IF (l_cle_rec.start_date IS NULL) THEN
1799 l_error_number := '1000';
1800 END IF;
1801
1802 IF (l_cle_rec.start_date > NVL(l_cle_rec.end_date, l_cle_rec.start_date)) THEN
1803 l_error_number := substr(l_error_number, 1, 1) || '100';
1804 END IF;
1805
1806 IF (l_cle_rec.end_date IS NULL AND
1807 l_parent_end_date IS NOT NULL) THEN
1808 l_error_number := substr(l_error_number, 1, 2) || '10';
1809 END IF;
1810
1811 IF ((l_cle_rec.start_date NOT BETWEEN l_parent_start_date AND
1812 NVL(l_parent_end_date, l_cle_rec.start_date)) OR
1813 (l_cle_rec.end_date IS NOT NULL AND l_cle_rec.end_date NOT BETWEEN
1814 l_parent_start_date AND NVL(l_parent_end_date, l_cle_rec.end_date))) THEN
1815 --below if condition added for bug#3339185
1816 if ((l_cle_rec.lse_id >=2 and l_cle_rec.lse_id <=6) or (l_cle_rec.lse_id=15)or (l_cle_rec.lse_id=16) or (l_cle_rec.lse_id=17)
1817 or (l_cle_rec.lse_id>=21 and l_cle_rec.lse_id <=24)) then
1818 l_error_number := substr(l_error_number, 1, 3) || '2'; --coverage lines
1819 else --non coverage lines
1820 l_error_number := substr(l_error_number, 1, 3) || '1';
1821 end if;
1822 --l_error_number := substr(l_error_number, 1, 3) || '1';
1823 END IF;
1824 --at below if statement,added second condition for bug#3339185
1825 If (Instr(l_error_number, '1') > 0 or Instr(l_error_number, '2') > 0) Then
1826 x_return_status := OKC_API.G_RET_STS_ERROR;
1827 l_line_name := okc_contract_pub.get_concat_line_no(l_cle_rec.id, l_return_status);
1828 IF l_return_status <> okc_api.g_ret_sts_success THEN
1829 l_line_name := 'Unknown';
1830 END IF;
1831
1832 If Substr(l_error_number, 1, 1) = '1' Then
1833 OKC_API.set_message(
1834 p_app_name => G_APP_NAME,
1835 p_msg_name => 'OKC_QA_LINE_SDATE_REQUIRED',
1836 p_token1 => 'LINE_NAME',
1837 p_token1_value => l_line_name);
1838 End If;
1839
1840 If Substr(l_error_number, 2, 1) = '1' Then
1841 OKC_API.set_message(
1842 p_app_name => G_APP_NAME,
1843 p_msg_name => G_INVALID_LINE_DATES,
1844 p_token1 => 'LINE_NAME',
1845 p_token1_value => l_line_name);
1846 End If;
1847
1848 If Substr(l_error_number, 3, 1) = '1' Then
1849 OKC_API.set_message(
1850 p_app_name => G_APP_NAME,
1851 p_msg_name => 'OKC_QA_LINE_EDATE_REQUIRED',
1852 p_token1 => 'LINE_NAME',
1853 p_token1_value => l_line_name);
1854 End If;
1855
1856 If Substr(l_error_number, 4, 1) = '1' Then
1857 OKC_API.set_message(
1858 p_app_name => G_APP_NAME,
1859 p_msg_name => G_INVALID_LINE_DATES,
1860 p_token1 => 'LINE_NAME',
1861 p_token1_value => l_line_name);
1862 End If;
1863 --below condition added for bug#3339185
1864 If Substr(l_error_number, 4, 1) = '2' Then
1865 OKC_API.set_message(
1866 p_app_name => G_APP_NAME,
1867 p_msg_name => G_INVALID_COVERAGELINE_DATES,
1868 p_token1 => 'LINE_NAME',
1869 p_token1_value => l_line_name);
1870 End If;
1871 End If;
1872
1873 END LOOP; -- Lines attached to header
1874 ***********************************************/
1875
1876
1877 --added for bug 5442886
1878 OPEN l_cle_csr;
1879 LOOP
1880
1881 FETCH l_cle_csr BULK COLLECT INTO l_start_date_tbl, l_end_date_tbl, l_id_tbl,
1882 l_chr_id_tbl, l_lse_id_tbl, l_parent_start_date_tbl,
1883 l_parent_end_date_tbl LIMIT G_BULK_FETCH_LIMIT;
1884
1885 EXIT WHEN l_id_tbl.COUNT = 0;
1886
1887 FOR i IN l_id_tbl.FIRST..l_id_tbl.LAST LOOP
1888
1889 If l_chr_id_tbl(i) Is Not Null Then
1890 l_parent_start_date := l_chrv_rec.start_date;
1891 l_parent_end_date := l_chrv_rec.end_date;
1892 Else
1893 l_parent_start_date := l_parent_start_date_tbl(i);
1894 l_parent_end_date := l_parent_end_date_tbl(i);
1895 End if;
1896
1897 l_error_number := '0000';
1898
1899 -- data is required
1900 IF (l_start_date_tbl(i) IS NULL) THEN
1901 l_error_number := '1000';
1902 END IF;
1903
1904 IF (l_start_date_tbl(i) > NVL(l_end_date_tbl(i), l_start_date_tbl(i))) THEN
1905 l_error_number := substr(l_error_number, 1, 1) || '100';
1906 END IF;
1907
1908 IF (l_end_date_tbl(i) IS NULL AND
1909 l_parent_end_date IS NOT NULL) THEN
1910 l_error_number := substr(l_error_number, 1, 2) || '10';
1911 END IF;
1912
1913 IF ((l_start_date_tbl(i) NOT BETWEEN l_parent_start_date AND
1914 NVL(l_parent_end_date, l_start_date_tbl(i))) OR
1915 (l_end_date_tbl(i) IS NOT NULL AND l_end_date_tbl(i) NOT BETWEEN
1916 l_parent_start_date AND NVL(l_parent_end_date, l_end_date_tbl(i)))) THEN
1917 --below if condition added for bug#3339185
1918 if ((l_lse_id_tbl(i) >=2 and l_lse_id_tbl(i) <=6) or (l_lse_id_tbl(i)=15)or (l_lse_id_tbl(i)=16) or (l_lse_id_tbl(i)=17)
1919 or (l_lse_id_tbl(i)>=21 and l_lse_id_tbl(i) <=24)) then
1920 l_error_number := substr(l_error_number, 1, 3) || '2'; --coverage lines
1921 else --non coverage lines
1922 l_error_number := substr(l_error_number, 1, 3) || '1';
1923 end if;
1924 --l_error_number := substr(l_error_number, 1, 3) || '1';
1925 END IF;
1926 --at below if statement,added second condition for bug#3339185
1927 If (Instr(l_error_number, '1') > 0 or Instr(l_error_number, '2') > 0) Then
1928 x_return_status := OKC_API.G_RET_STS_ERROR;
1929 l_line_name := okc_contract_pub.get_concat_line_no(l_id_tbl(i), l_return_status);
1930 IF l_return_status <> okc_api.g_ret_sts_success THEN
1931 l_line_name := 'Unknown';
1932 END IF;
1933
1934 If Substr(l_error_number, 1, 1) = '1' Then
1935 OKC_API.set_message(
1936 p_app_name => G_APP_NAME,
1937 p_msg_name => 'OKC_QA_LINE_SDATE_REQUIRED',
1938 p_token1 => 'LINE_NAME',
1939 p_token1_value => l_line_name);
1940 End If;
1941
1942 If Substr(l_error_number, 2, 1) = '1' Then
1943 OKC_API.set_message(
1944 p_app_name => G_APP_NAME,
1945 p_msg_name => G_INVALID_LINE_DATES,
1946 p_token1 => 'LINE_NAME',
1947 p_token1_value => l_line_name);
1948 End If;
1949
1950 If Substr(l_error_number, 3, 1) = '1' Then
1951 OKC_API.set_message(
1952 p_app_name => G_APP_NAME,
1953 p_msg_name => 'OKC_QA_LINE_EDATE_REQUIRED',
1954 p_token1 => 'LINE_NAME',
1955 p_token1_value => l_line_name);
1956 End If;
1957
1958 If Substr(l_error_number, 4, 1) = '1' Then
1959 OKC_API.set_message(
1960 p_app_name => G_APP_NAME,
1961 p_msg_name => G_INVALID_LINE_DATES,
1962 p_token1 => 'LINE_NAME',
1963 p_token1_value => l_line_name);
1964 End If;
1965 --below condition added for bug#3339185
1966 If Substr(l_error_number, 4, 1) = '2' Then
1967 OKC_API.set_message(
1968 p_app_name => G_APP_NAME,
1969 p_msg_name => G_INVALID_COVERAGELINE_DATES,
1970 p_token1 => 'LINE_NAME',
1971 p_token1_value => l_line_name);
1972 End If;
1973 End If;
1974
1975
1976 END LOOP;
1977
1978 END LOOP; -- Lines attached to header
1979
1980
1981
1982
1983 -- notify caller of success
1984 IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1985 OKC_API.set_message(
1986 p_app_name => G_APP_NAME,
1987 p_msg_name => G_QA_SUCCESS);
1988 END IF;
1989
1990 IF (l_debug = 'Y') THEN
1991 okc_debug.Log('1000: Leaving ',2);
1992 okc_debug.Reset_Indentation;
1993 END IF;
1994 EXCEPTION
1995 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1996 IF (l_debug = 'Y') THEN
1997 okc_debug.Log('2000: Leaving ',2);
1998 okc_debug.Reset_Indentation;
1999 END IF;
2000 -- no processing necessary; validation can continue with next column
2001 NULL;
2002 WHEN OTHERS THEN
2003 IF (l_debug = 'Y') THEN
2004 okc_debug.Log('3000: Leaving ',2);
2005 okc_debug.Reset_Indentation;
2006 END IF;
2007 -- store SQL error message on message stack
2008 OKC_API.SET_MESSAGE(
2009 p_app_name => G_APP_NAME,
2010 p_msg_name => G_UNEXPECTED_ERROR,
2011 p_token1 => G_SQLCODE_TOKEN,
2012 p_token1_value => SQLCODE,
2013 p_token2 => G_SQLERRM_TOKEN,
2014 p_token2_value => SQLERRM);
2015 -- notify caller of an error as UNEXPETED error
2016 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2017 -- verify that cursor was closed
2018 IF l_chrv_csr%ISOPEN THEN
2019 CLOSE l_chrv_csr;
2020 END IF;
2021 IF l_cle_csr%ISOPEN THEN
2022 CLOSE l_cle_csr;
2023 END IF;
2024 IF l_clev_csr%ISOPEN THEN
2025 CLOSE l_clev_csr;
2026 END IF;
2027 END check_effectivity_dates;
2028 --
2029
2030 END OKC_QA_DATA_INTEGRITY;
[Home] [Help]