1 PACKAGE BODY OKS_RENEW_UTIL_PVT AS
2 /* $Header: OKSRRUTB.pls 120.17 2008/03/03 05:31:35 cgopinee ship $*/
3
4 FUNCTION CHECK_TEMPLATE_SET_VALIDITY (p_template_set_id IN NUMBER)
5 RETURN VARCHAR2 IS
6
7 CURSOR l_template_set_cur (p_template_set_id IN NUMBER, p_profile IN VARCHAR2) IS
8 SELECT 'Y'
9 FROM OKS_TEMPLATE_SET
10 WHERE id = p_template_set_id
11 AND template_source = p_profile;
12
13 l_profile VARCHAR2(10);
14 l_return VARCHAR2(1) := 'N';
15
16 BEGIN
17 l_profile := NVL(FND_PROFILE.VALUE('OKS_LAYOUT_TEMPLATE_SOURCE'), 'OKC');
18
19 OPEN l_template_set_cur(p_template_set_id, l_profile);
20 FETCH l_template_set_cur INTO l_return;
21
22 IF l_template_set_cur%NOTFOUND THEN
23 l_return := 'N';
24 END IF;
25
26 CLOSE l_template_set_cur;
27
28 RETURN l_return;
29 EXCEPTION
30 WHEN OTHERS THEN
31 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
32 p_msg_name => g_unexpected_error,
33 p_token1 => g_sqlcode_token,
34 p_token1_value => SQLCODE,
35 p_token2 => g_sqlerrm_token,
36 p_token2_value => SQLERRM);
37 END;
38
39
40 --======================================================================
41
42 PROCEDURE UPDATE_RENEWAL_STATUS (
43 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
44 P_CHR_ID IN NUMBER,
45 P_RENEW_STATUS IN VARCHAR2,
46 P_CHR_STATUS IN VARCHAR2
47 ) IS
48
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
51 l_return_status VARCHAR2(1);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(2000) := NULL;
54
55 l_khdr_rec_in OKS_CONTRACT_HDR_PUB.khrv_rec_type;
56 l_khdr_rec_out OKS_CONTRACT_HDR_PUB.khrv_rec_type;
57
58 l_okc_hdr_rec_in OKC_CONTRACT_PUB.chrv_rec_type;
59 l_okc_hdr_rec_out OKC_CONTRACT_PUB.chrv_rec_type;
60
61 e_error EXCEPTION;
62
63 BEGIN
64
65 l_return_status := OKC_API.G_RET_STS_SUCCESS;
66
67 l_khdr_rec_in.chr_id := p_chr_id;
68 l_khdr_rec_in.renewal_status := p_renew_status;
69
70 oks_contract_hdr_pub.update_header(
71 p_api_version => l_api_version,
72 p_init_msg_list => l_init_msg_list,
73 x_return_status => l_return_status,
74 x_msg_count => l_msg_count,
75 x_msg_data => l_msg_data,
76 p_khrv_rec => l_khdr_rec_in,
77 x_khrv_rec => l_khdr_rec_out,
78 p_validate_yn => 'N'
79 );
80
81 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
82 RAISE e_Error;
83 END IF;
84
85 l_okc_hdr_rec_in.id := p_chr_id;
86 l_okc_hdr_rec_in.sts_code := p_chr_status;
87
88 okc_contract_pub.update_contract_header(
89 p_api_version => l_api_version,
90 p_init_msg_list => l_init_msg_list,
91 x_return_status => l_return_status,
92 x_msg_count => l_msg_count,
93 x_msg_data => l_msg_data,
94 p_restricted_update => 'F',
95 p_chrv_rec => l_okc_hdr_rec_in,
96 x_chrv_rec => l_okc_hdr_rec_out
97 );
98
99 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
100 RAISE e_Error;
101 END IF;
102
103 EXCEPTION
104 WHEN E_Error THEN
105 x_Return_status := l_Return_status;
106 WHEN OTHERS THEN
107 -- store SQL error message on message stack for caller
108 OKC_API.SET_MESSAGE(
109 p_app_name => g_app_name,
110 p_msg_name => g_unexpected_error,
111 p_token1 => g_sqlcode_token,
112 p_token1_value => SQLCODE,
113 p_token2 => g_sqlerrm_token,
114 p_token2_value => SQLERRM
115 );
116
117 -- notify caller of an UNEXPECTED error
118 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
119 END;
120
121 PROCEDURE get_payment_terms (
122 p_chr_id IN NUMBER,
123 p_party_id IN NUMBER,
124 p_org_id IN NUMBER,
125 p_effective_date IN DATE,
126 x_pay_term_id1 OUT NOCOPY VARCHAR2,
127 x_pay_term_id2 OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130 x_return_status OUT NOCOPY VARCHAR2) IS
131
132 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
133 l_payment_terms_id1 VARCHAR2(40);
134 l_payment_terms_id2 VARCHAR2(40);
135 l_payment_term_flag VARCHAR2(1) := 'N';
136 l_msg_count NUMBER;
137 l_msg_data VARCHAR2(2000);
138 l_procedure_name VARCHAR2(80);
139 l_party NUMBER;
140 l_org NUMBER;
141
142 CURSOR l_party_cur IS
143 SELECT object1_id1 FROM OKC_K_PARTY_ROLES_V
144 WHERE dnz_chr_id = p_chr_id
145 AND cle_id IS NULL
146 AND jtot_object1_code = 'OKX_PARTY'
147 AND rle_code = 'CUSTOMER';
148
149 CURSOR l_org_cur IS
150 SELECT authoring_org_id
151 FROM OKC_K_HEADERS_V
152 WHERE id = p_chr_id;
153
154 -- Cursor to fetch values defined at Party/Org level in Global Defaults.
155 CURSOR l_party_org_level_cur(p_id1 IN VARCHAR2,
156 p_id2 IN VARCHAR2,
157 p_object_code IN VARCHAR2,
158 p_date IN DATE
159 ) IS
160 SELECT payment_terms_id1, payment_terms_id2
161 FROM OKS_K_DEFAULTS_V
162 WHERE SEGMENT_ID1 = p_id1
163 AND SEGMENT_ID2 = p_id2
164 AND JTOT_OBJECT_CODE = p_object_code
165 AND CDT_TYPE = 'SDT'
166 AND nvl(p_date, SYSDATE) BETWEEN
167 nvl(START_DATE, SYSDATE) AND nvl(END_DATE, SYSDATE);
168
169 -- Cursor to fetch renewal rules defined at Global level in Global Defaults.
170 CURSOR l_global_level_cur IS
171 SELECT payment_terms_id1, payment_terms_id2
172 FROM OKS_K_DEFAULTS_V
173 WHERE cdt_type = 'MDT'
174 AND segment_id1 IS NULL
175 AND segment_id2 IS NULL
176 AND jtot_object_code IS NULL;
177
178 BEGIN
179
180 IF (p_chr_id IS NULL OR p_chr_id = FND_API.G_MISS_NUM ) AND
181 (
182 (p_party_id IS NULL OR p_party_id = FND_API.G_MISS_NUM ) OR
183 (p_org_id IS NULL OR p_org_id = FND_API.G_MISS_NUM)
184 ) THEN
185
186 IF p_chr_id IS NULL OR p_chr_id = FND_API.G_MISS_NUM THEN
187 OKC_API.SET_MESSAGE(G_APP_NAME, G_REQUIRED_PARAM,
188 'TOKEN1', 'CHR_ID',
189 'TOKEN2', 'GET_PAYMENT_TERMS');
190 END IF;
191
192 IF p_party_id IS NULL OR p_party_id = FND_API.G_MISS_NUM THEN
193 OKC_API.SET_MESSAGE(G_APP_NAME, G_REQUIRED_PARAM,
194 'TOKEN1', 'PARTY_ID',
195 'TOKEN2', 'GET_PAYMENT_TERMS');
196 END IF;
197
198 IF p_org_id IS NULL OR p_org_id = FND_API.G_MISS_NUM THEN
199 OKC_API.SET_MESSAGE(G_APP_NAME, G_REQUIRED_PARAM,
200 'TOKEN1', 'ORG_ID',
201 'TOKEN2', 'GET_PAYMENT_TERMS');
202 END IF;
203
204 l_Return_Status := OKC_API.G_RET_STS_ERROR;
205 RAISE G_EXCEPTION_HALT_VALIDATION;
206
207 END IF;
208
209 -- Check for party_id, if not present get party_id
210 IF p_party_id IS NULL OR p_party_id = FND_API.G_MISS_NUM THEN
211 OPEN l_party_cur;
212 FETCH l_party_cur INTO l_party;
213 CLOSE l_party_cur;
214 ELSE
215 l_party := p_party_id;
216 END IF;
217
218 -- Fetch renewal rules for this party as there are set in
219 -- Global Defaults
220 IF l_party IS NOT NULL THEN
221 OPEN l_party_org_level_cur(to_char(l_party), '#', 'OKX_PARTY', p_effective_date);
222 FETCH l_party_org_level_cur INTO l_payment_terms_id1, l_payment_terms_id2;
223 CLOSE l_party_org_level_cur;
224
225 IF l_payment_terms_id1 IS NOT NULL AND l_payment_terms_id2 IS NOT NULL THEN
226 l_payment_term_flag := 'Y';
227 END IF;
228 END IF;
229
230 IF l_payment_term_flag = 'N' THEN
231
232 -- Check for org_id, if not present get Org_id
233 IF p_org_id IS NULL OR p_org_id = FND_API.G_MISS_NUM THEN
234 OPEN l_org_cur;
235 FETCH l_org_cur INTO l_org;
236 CLOSE l_org_cur;
237 ELSE
238 l_org := p_org_id;
239 END IF;
240
241 -- Fetch payment terms for this org as set in Global Defaults
242 IF l_org IS NOT NULL THEN
243 OPEN l_party_org_level_cur(to_char(l_org), '#', 'OKX_OPERUNIT', p_effective_date);
244 FETCH l_party_org_level_cur INTO l_payment_terms_id1, l_payment_terms_id2;
245 CLOSE l_party_org_level_cur;
246
247 IF l_payment_terms_id1 IS NOT NULL AND l_payment_terms_id2 IS NOT NULL THEN
248 l_payment_term_flag := 'Y';
249 END IF;
250 END IF;
251 END IF;
252
253 IF l_payment_term_flag = 'N' THEN
254
255 -- Fetch payment terms as set at global level in Global Defaults
256 OPEN l_global_level_cur;
257 FETCH l_global_level_cur INTO l_payment_terms_id1, l_payment_terms_id2;
258
259 -- As there should be always values under globals in
260 -- global defaults setup, if nothing is found, raise error
261 IF l_global_level_cur%NOTFOUND THEN
262 CLOSE l_global_level_cur;
263 OKC_API.set_message(G_APP_NAME, 'OKS_GLOBAL_DEFAULTS_NOT_FOUND');
264 l_return_status := OKC_API.G_RET_STS_ERROR;
265 RAISE G_EXCEPTION_HALT_VALIDATION;
266 END IF;
267 CLOSE l_global_level_cur;
268 END IF;
269
270 -- Populate the out parameters with the renewal fields
271 x_pay_term_id1 := l_payment_terms_id1;
272 x_pay_term_id2 := l_payment_terms_id2;
273 x_return_status := l_return_status;
274
275 EXCEPTION
276 WHEN G_EXCEPTION_HALT_VALIDATION THEN
277 x_return_status := l_return_status;
278 WHEN OTHERS THEN
279 -- store SQL error message on message stack for caller
280 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
281 p_msg_name => g_unexpected_error,
282 p_token1 => g_sqlcode_token,
283 p_token1_value => SQLCODE,
284 p_token2 => g_sqlerrm_token,
285 p_token2_value => SQLERRM);
286
287 -- notify caller of an UNEXPECTED error
288 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
289 END get_payment_terms;
290
291 PROCEDURE Can_Update_Contract(
292 p_chr_id IN NUMBER,
293 x_can_update_yn OUT NOCOPY VARCHAR2,
294 x_can_submit_yn OUT NOCOPY VARCHAR2,
295 x_msg_count OUT NOCOPY NUMBER,
296 x_msg_data OUT NOCOPY VARCHAR2,
297 x_return_status OUT NOCOPY VARCHAR2
298 ) IS
299
300 BEGIN
301 x_return_status := OKC_API.G_RET_STS_SUCCESS;
302 x_can_update_yn := 'Y';
303 x_can_submit_yn := 'Y';
304 END Can_Update_Contract;
305
306 -------------------------------------------------------------------------------
307 /* This procedure gets the values of period_type,period_start and price_uom from GCD
308 if contract header id is null else gets the values from contract header record.
309 */
310 PROCEDURE get_period_defaults(p_hdr_id IN NUMBER DEFAULT NULL,
311 p_org_id IN VARCHAR2 DEFAULT NULL,
312 x_period_type OUT NOCOPY VARCHAR2,
313 x_period_start OUT NOCOPY VARCHAR2,
314 x_price_uom OUT NOCOPY VARCHAR2,
315 x_return_status OUT NOCOPY VARCHAR2)
316 IS
317
318 CURSOR get_hdr_period_val(p_hdr_id IN NUMBER)
319 IS
320 SELECT period_type, period_start, price_uom
321 FROM oks_k_headers_b
322 WHERE chr_id = p_hdr_id;
323
324 CURSOR get_gcd_org_period_val (p_org_id IN VARCHAR2)
325 IS
326 SELECT period_type, period_start, price_uom
327 FROM oks_k_defaults
328 WHERE cdt_type = 'SDT'
329 AND segment_id1 = p_org_id;
330
331 CURSOR get_gcd_glob_period_val
332 IS
333 SELECT period_type, period_start, price_uom
334 FROM oks_k_defaults
335 WHERE cdt_type = 'MDT'
336 AND segment_id1 IS NULL;
337
338 l_period_type oks_k_defaults.period_type%TYPE;
339 l_period_start oks_k_defaults.period_start%TYPE;
340 l_price_uom oks_k_defaults.price_uom%TYPE;
341
342 BEGIN
343 x_return_status := OKC_API.G_RET_STS_SUCCESS;
344 IF p_hdr_id IS NOT NULL
345 THEN
346 OPEN get_hdr_period_val(p_hdr_id);
347 FETCH get_hdr_period_val INTO l_period_type, l_period_start, l_price_uom;
348 CLOSE get_hdr_period_val;
349 ELSE
350 -- If hdr_id is null, then values are taken from GCD
351 IF p_org_id IS NOT NULL
352 THEN
353 OPEN get_gcd_org_period_val(p_org_id);
354 FETCH get_gcd_org_period_val INTO l_period_type, l_period_start, l_price_uom;
355 CLOSE get_gcd_org_period_val;
356
357 --anjkumar PP CR002, both period type and period start should be defined
358 --at any level for it to be considered
359
360 --IF (l_period_type IS NULL) AND (l_period_start IS NULL)
361 IF (l_period_type IS NULL) OR (l_period_start IS NULL)
362 THEN
363 OPEN get_gcd_glob_period_val;
364 FETCH get_gcd_glob_period_val INTO l_period_type, l_period_start, l_price_uom;
365 CLOSE get_gcd_glob_period_val;
366 END IF;
367 ELSE
368 OPEN get_gcd_glob_period_val;
369 FETCH get_gcd_glob_period_val INTO l_period_type, l_period_start, l_price_uom;
370 CLOSE get_gcd_glob_period_val;
371 END IF;
372 END IF;
373
374 --if both period type and period start have to be not null to consider it
375 IF (l_period_type IS NULL) OR (l_period_start IS NULL) THEN
376 x_period_type := NULL;
377 x_period_start := NULL;
378 x_price_uom := NULL;
379 ELSE
380 x_period_type := l_period_type;
381 x_period_start := l_period_start;
382 x_price_uom := l_price_uom;
383 END IF;
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 x_return_status := OKC_API.G_RET_STS_ERROR;
388
389 END get_period_defaults;
390
391 /*
392 -------------------------------------------------------------------------
393 rewritten R12 traversal api that fetches all the rules from gcd
394 following the hierarchy - input->contract->party->org->global
395
396 it recoginzes the new attributes introduced forRr12, groups
397 interdependent attributes from the same level and improves
398 performance and logging.
399 -------------------------------------------------------------------------
400 */
401 PROCEDURE get_renew_rules(x_return_status OUT NOCOPY VARCHAR2,
402 p_api_version IN NUMBER DEFAULT 1,
403 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false,
404 p_chr_id IN NUMBER,
405 p_party_id IN NUMBER,
406 p_org_id IN NUMBER,
407 p_date IN DATE DEFAULT SYSDATE,
408 p_rnrl_rec IN rnrl_rec_type,
409 x_rnrl_rec OUT NOCOPY rnrl_rec_type,
410 x_msg_count OUT NOCOPY NUMBER,
411 x_msg_data OUT NOCOPY VARCHAR2)
412 IS
413
414 TYPE rnrl_rec_tbl_type IS TABLE OF rnrl_rec_type INDEX BY BINARY_INTEGER;
415
416 l_api_name CONSTANT VARCHAR2(30) := 'GET_RENEW_RULES';
417 l_api_version CONSTANT NUMBER := 1;
418 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || g_pkg_name || '.' || l_api_name;
419
420 l_party_id NUMBER;
421 l_org_id NUMBER;
422 l_k_org_id NUMBER; --to store the contract org id, if p_org_id not passed
423
424 lk_rnrl_rec rnrl_rec_type;
425 l_rules_tbl_tmp rnrl_rec_tbl_type; -- temp table to store output of bulk collect
426 l_rules_tbl rnrl_rec_tbl_type; -- table to store all the rules in the correct order -> input, contract, party, org, global
427
428 l_effective_base_currency VARCHAR2(30);
429 l_error_text VARCHAR2(512);
430
431 CURSOR c_k_party(cp_chr_id IN NUMBER) IS
432 SELECT object1_id1 FROM okc_k_party_roles_b
433 WHERE dnz_chr_id = cp_chr_id
434 AND cle_id IS NULL
435 AND jtot_object1_code = 'OKX_PARTY'
436 AND rle_code IN ('CUSTOMER', 'SUBSCRIBER');
437
438 CURSOR c_k_rules(cp_chr_id IN NUMBER) IS
439 SELECT a.renewal_type_code,
440 b.renewal_pricing_type, b.renewal_markup_percent, b.renewal_price_list,
441 b.renewal_po_required,
442 b.renewal_est_rev_percent, b.renewal_est_rev_duration, b.renewal_est_rev_period,
443 b.billing_profile_id, b.renewal_grace_duration, b.renewal_grace_period,
444 a.currency_code, a.approval_type, nvl(a.org_id, a.authoring_org_id)
445 FROM okc_k_headers_all_b a, oks_k_headers_b b
446 WHERE a.id = cp_chr_id AND a.id = b.chr_id;
447
448 --cp_org_id the org for which the rules need to be obtained, can be null
449 --cp_party_id the party for which the rules need to be obtained, can be null
450 --cp_date the date on which the rule should be effective, should not be null
451 --cursor will always return 1 record and at max return 3 records
452 CURSOR c_cgd_rules(cp_org_id IN NUMBER, cp_party_id IN NUMBER, cp_date IN DATE) IS
453 SELECT
454 cdt_type,
455 jtot_object_code,
456 renewal_type,
457 renewal_pricing_type,
458 markup_percent,
459 price_list_id1,
460 price_list_id2,
461 pdf_id,
462 qcl_id,
463 cgp_new_id,
464 cgp_renew_id,
465 po_required_yn,
466 credit_amount,
467 rle_code,
468 revenue_estimated_percent,
469 revenue_estimated_duration,
470 revenue_estimated_period,
471 NULL, --function_name,
472 NULL, -- salesrep_name
473 template_set_id,
474 base_currency, --threshold_currency, col obsolete
475 threshold_amount,
476 email_address,
477 billing_profile_id,
478 user_id,
479 threshold_enabled_yn,
480 grace_period,
481 grace_duration,
482 payment_terms_id1,
483 payment_terms_id2,
484 base_currency, --evergreen_threshold_curr, col obsolete
485 evergreen_threshold_amt,
486 payment_method,
487 base_currency, --payment_threshold_curr, col obsolete
488 payment_threshold_amt,
489 interface_price_break,
490 base_currency,
491 approval_type,
492 evergreen_approval_type,
493 online_approval_type,
494 purchase_order_flag,
495 credit_card_flag,
496 wire_flag,
497 commitment_number_flag,
498 check_flag,
499 period_type,
500 period_start,
501 price_uom,
502 template_language
503 FROM oks_k_defaults
504 WHERE
505 (cdt_type = 'MDT' AND jtot_object_code IS NULL AND segment_id1 IS NULL AND segment_id2 IS NULL)
506 OR
507 (cdt_type = 'SDT' AND jtot_object_code = 'OKX_OPERUNIT' AND segment_id1 = to_char(cp_org_id) AND segment_id2 = '#'
508 AND trunc(cp_date) BETWEEN start_date AND nvl(end_date, greatest(start_date, trunc(cp_date))))
509 OR
510 (cdt_type = 'SDT' AND jtot_object_code = 'OKX_PARTY' AND segment_id1 = to_char(cp_party_id) AND segment_id2 = '#'
511 AND trunc(cp_date) BETWEEN start_date AND nvl(end_date, greatest(start_date, trunc(cp_date))));
512
513
514 --this procedure sets the interdependent and dependent attributes.
515 --using the hierarchy - input->contract->party->org->global. for
516 --interdependent attributes all rules are fetched from the same level
517 --for e.g., if grace period is set at party level
518 --we should get the grace duration from the same level and not the org level
519 --even if party level grace duration is null
520 PROCEDURE set_attributes
521 IS
522 BEGIN
523 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
524 IF (FND_LOG.test(FND_LOG.level_procedure, l_mod_name)) THEN
525 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.set_attributes.begin', 'begin');
526 END IF;
527 END IF;
528
529 -- assume that l_rules_tbl(0) has input rules, l_rules_tbl(1) has contract rules, l_rules_tbl(2) has party rules etc
530 FOR i IN l_rules_tbl.first..l_rules_tbl.last LOOP
531
532 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
533 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
534 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.set_attributes.for_loop', 'i=' || i);
535 END IF;
536 END IF;
537
538 --first set the interdependent attributes
539 --renewal type and approval type
540 IF (x_rnrl_rec.renewal_type IS NULL) THEN
541 x_rnrl_rec.renewal_type := l_rules_tbl(i).renewal_type;
542 IF (x_rnrl_rec.renewal_type IS NOT NULL) THEN
543 x_rnrl_rec.approval_type := l_rules_tbl(i).approval_type;
544 END IF;
545 END IF;
546
547 --renewal pricing_type, markup percent, price list
548 IF (x_rnrl_rec.renewal_pricing_type IS NULL) THEN
549 x_rnrl_rec.renewal_pricing_type := l_rules_tbl(i).renewal_pricing_type;
550 IF (x_rnrl_rec.renewal_pricing_type IS NOT NULL) THEN
551 x_rnrl_rec.markup_percent := l_rules_tbl(i).markup_percent;
552 x_rnrl_rec.price_list_id1 := l_rules_tbl(i).price_list_id1;
553 x_rnrl_rec.price_list_id2 := l_rules_tbl(i).price_list_id2;
554 END IF;
555 END IF;
556
557 --revenue estimated percent, duration and period
558 IF (x_rnrl_rec.revenue_estimated_percent IS NULL) THEN
559 x_rnrl_rec.revenue_estimated_percent := l_rules_tbl(i).revenue_estimated_percent;
560 IF (x_rnrl_rec.revenue_estimated_percent IS NOT NULL) THEN
561 x_rnrl_rec.revenue_estimated_duration := l_rules_tbl(i).revenue_estimated_duration;
562 x_rnrl_rec.revenue_estimated_period := l_rules_tbl(i).revenue_estimated_period;
563 END IF;
564 END IF;
565
566 --grace period and duration
567 IF (x_rnrl_rec.grace_period IS NULL) THEN
568 x_rnrl_rec.grace_period := l_rules_tbl(i).grace_period;
569 IF (x_rnrl_rec.grace_period IS NOT NULL) THEN
570 x_rnrl_rec.grace_duration := l_rules_tbl(i).grace_duration;
571 END IF;
572 END IF;
573
574 --partial period type, start and uom
575 IF (x_rnrl_rec.period_type IS NULL) THEN
576 x_rnrl_rec.period_type := l_rules_tbl(i).period_type;
577 IF (x_rnrl_rec.period_type IS NOT NULL) THEN
578 x_rnrl_rec.period_start := l_rules_tbl(i).period_start;
579 x_rnrl_rec.price_uom := l_rules_tbl(i).price_uom;
580
581 --anjkumar both period type and period start have to be not null, otherwise we should ignore
582 --the values at this level, Partial Period Change request CR 002
583 IF (x_rnrl_rec.period_start IS NULL) THEN
584 x_rnrl_rec.period_type := NULL;
585 x_rnrl_rec.period_start := NULL;
586 x_rnrl_rec.price_uom := NULL;
587 END IF;
588
589 END IF;
590 END IF;
591
592 --payment terms id1 and id2
593 IF (x_rnrl_rec.payment_terms_id1 IS NULL) THEN
594 x_rnrl_rec.payment_terms_id1 := l_rules_tbl(i).payment_terms_id1;
595 IF (x_rnrl_rec.payment_terms_id1 IS NOT NULL) THEN
596 x_rnrl_rec.payment_terms_id2 := l_rules_tbl(i).payment_terms_id2;
597 END IF;
598 END IF;
599
600 --helpdesk user id and email
601 IF (x_rnrl_rec.user_id IS NULL) THEN
602 x_rnrl_rec.user_id := l_rules_tbl(i).user_id;
603 IF (x_rnrl_rec.user_id IS NOT NULL) THEN
604 x_rnrl_rec.email_address := l_rules_tbl(i).email_address;
605 END IF;
606 END IF;
607
608 --get the base currency level
609 IF (x_rnrl_rec.base_currency IS NULL) THEN
610 x_rnrl_rec.base_currency := l_rules_tbl(i).base_currency;
611 IF (x_rnrl_rec.base_currency IS NOT NULL) THEN
612 l_effective_base_currency := x_rnrl_rec.base_currency;
613 END IF;
614 END IF;
615
616 --set the currency dependent attributes
617 --special handling for curremcy dependent attributes
618 --for e.g., if contract currency is usd, and party level currency is eur and org level currency
619 --is usd, we should get the threshold amounts from org level and not party level
620
621 --evergreen threshold amt, evergreen threshold curr, evergreen approval type
622 IF (x_rnrl_rec.evergreen_threshold_amt IS NULL) THEN
623
624 IF(l_rules_tbl(i).evergreen_threshold_amt IS NOT NULL AND
625 nvl(l_rules_tbl(i).base_currency, 'X') = l_effective_base_currency) THEN
626 x_rnrl_rec.evergreen_threshold_amt := l_rules_tbl(i).evergreen_threshold_amt;
627 x_rnrl_rec.evergreen_threshold_curr := l_rules_tbl(i).base_currency;
628 x_rnrl_rec.evergreen_approval_type := l_rules_tbl(i).evergreen_approval_type;
629 END IF;
630
631 END IF;
632
633 --online threshold amt, online threshold curr, online approval type
634 IF (x_rnrl_rec.threshold_amount IS NULL) THEN
635
636 IF(l_rules_tbl(i).threshold_amount IS NOT NULL AND
637 nvl(l_rules_tbl(i).base_currency, 'X') = l_effective_base_currency) THEN
638 x_rnrl_rec.threshold_amount := l_rules_tbl(i).threshold_amount;
639 x_rnrl_rec.threshold_currency := l_rules_tbl(i).base_currency;
640 x_rnrl_rec.online_approval_type := l_rules_tbl(i).online_approval_type;
641 END IF;
642
643 END IF;
644
645 --payment threshold amt, paymnet threshold curr
646 IF (x_rnrl_rec.payment_threshold_amt IS NULL) THEN
647
648 IF(l_rules_tbl(i).payment_threshold_amt IS NOT NULL AND
649 nvl(l_rules_tbl(i).base_currency, 'X') = l_effective_base_currency) THEN
650 x_rnrl_rec.payment_threshold_amt := l_rules_tbl(i).payment_threshold_amt;
651 x_rnrl_rec.payment_threshold_curr := l_rules_tbl(i).base_currency;
652 END IF;
653
654 END IF;
655
656 --set the remaining independent attributes
657 IF (x_rnrl_rec.pdf_id IS NULL) THEN
658 x_rnrl_rec.pdf_id := l_rules_tbl(i).pdf_id;
659 END IF;
660
661 IF (x_rnrl_rec.qcl_id IS NULL) THEN
662 x_rnrl_rec.qcl_id := l_rules_tbl(i).qcl_id;
663 END IF;
664
665 IF (x_rnrl_rec.cgp_new_id IS NULL) THEN
666 x_rnrl_rec.cgp_new_id := l_rules_tbl(i).cgp_new_id;
667 END IF;
668
669 IF (x_rnrl_rec.cgp_renew_id IS NULL) THEN
670 x_rnrl_rec.cgp_renew_id := l_rules_tbl(i).cgp_renew_id;
671 END IF;
672
673 IF (x_rnrl_rec.po_required_yn IS NULL) THEN
674 x_rnrl_rec.po_required_yn := l_rules_tbl(i).po_required_yn;
675 END IF;
676
677 IF (x_rnrl_rec.credit_amount IS NULL) THEN
678 x_rnrl_rec.credit_amount := l_rules_tbl(i).credit_amount;
679 END IF;
680
681 IF (x_rnrl_rec.rle_code IS NULL) THEN
682 x_rnrl_rec.rle_code := l_rules_tbl(i).rle_code;
683 END IF;
684
685 IF (x_rnrl_rec.template_set_id IS NULL) THEN
686 x_rnrl_rec.template_set_id := l_rules_tbl(i).template_set_id;
687 END IF;
688
689 IF (x_rnrl_rec.billing_profile_id IS NULL) THEN
690 x_rnrl_rec.billing_profile_id := l_rules_tbl(i).billing_profile_id;
691 END IF;
692
693 IF (x_rnrl_rec.threshold_enabled_yn IS NULL) THEN
694 x_rnrl_rec.threshold_enabled_yn := l_rules_tbl(i).threshold_enabled_yn;
695 END IF;
696
697 IF (x_rnrl_rec.payment_method IS NULL) THEN
698 x_rnrl_rec.payment_method := l_rules_tbl(i).payment_method;
699 END IF;
700
701 IF (x_rnrl_rec.interface_price_break IS NULL) THEN
702 x_rnrl_rec.interface_price_break := l_rules_tbl(i).interface_price_break;
703 END IF;
704
705 IF (x_rnrl_rec.template_language IS NULL) THEN
706 x_rnrl_rec.template_language := l_rules_tbl(i).template_language;
707 END IF;
708
709 IF (x_rnrl_rec.purchase_order_flag IS NULL) THEN
710 x_rnrl_rec.purchase_order_flag := l_rules_tbl(i).purchase_order_flag;
711 END IF;
712
713 IF (x_rnrl_rec.credit_card_flag IS NULL) THEN
714 x_rnrl_rec.credit_card_flag := l_rules_tbl(i).credit_card_flag;
715 END IF;
716
717 IF (x_rnrl_rec.wire_flag IS NULL) THEN
718 x_rnrl_rec.wire_flag := l_rules_tbl(i).wire_flag;
719 END IF;
720
721 IF (x_rnrl_rec.wire_flag IS NULL) THEN
722 x_rnrl_rec.wire_flag := l_rules_tbl(i).wire_flag;
723 END IF;
724
725 IF (x_rnrl_rec.commitment_number_flag IS NULL) THEN
726 x_rnrl_rec.commitment_number_flag := l_rules_tbl(i).commitment_number_flag;
727 END IF;
728
729 IF (x_rnrl_rec.check_flag IS NULL) THEN
730 x_rnrl_rec.check_flag := l_rules_tbl(i).check_flag;
731 END IF;
732
733 IF (x_rnrl_rec.function_name IS NULL) THEN
734 x_rnrl_rec.function_name := l_rules_tbl(i).function_name;
735 END IF;
736
737 IF (x_rnrl_rec.salesrep_name IS NULL) THEN
738 x_rnrl_rec.salesrep_name := l_rules_tbl(i).salesrep_name;
739 END IF;
740
741 END LOOP;
742
743 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
744 IF (FND_LOG.test(FND_LOG.level_procedure, l_mod_name)) THEN
745 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.set_attributes.end', 'end');
746 END IF;
747 END IF;
748
749 END set_attributes;
750
751 BEGIN
752 --main procedure begins here
753
754 --log key input parameters
755 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
756 IF (FND_LOG.test(FND_LOG.level_procedure, l_mod_name)) THEN
757 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_chr_id=' || p_chr_id ||' ,p_party_id='|| p_party_id ||' ,p_org_id='|| p_org_id || ',p_date=' || p_date);
758 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.input_rules', 'see following log');
759 log_rules(l_mod_name || '.input_rules', p_rnrl_rec);
760 END IF;
761 END IF;
762
763 --standard api initilization and checks
764 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767 IF FND_API.to_boolean(p_init_msg_list ) THEN
768 FND_MSG_PUB.initialize;
769 END IF;
770 x_return_status := FND_API.G_RET_STS_SUCCESS;
771
772 --basic parameter validation
773 IF ((p_chr_id IS NULL OR p_chr_id = FND_API.G_MISS_NUM) AND
774 (p_party_id IS NULL OR p_party_id = FND_API.G_MISS_NUM) AND
775 (p_org_id IS NULL OR p_org_id = FND_API.G_MISS_NUM)) THEN
776
777 FND_MESSAGE.set_name(G_OKS_APP_NAME, 'OKS_REN_RUL_INV_ARG');
778 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
779 FND_LOG.message(FND_LOG.level_error, l_mod_name || '.basic_validation', FALSE);
780 END IF;
781 FND_MSG_PUB.ADD;
782
783 RAISE FND_API.g_exc_error;
784 END IF;
785
786 --initialize the rules table
787 l_rules_tbl(G_INPUT_LEVEL) := p_rnrl_rec;
788 l_rules_tbl(G_CONTRACT_LEVEL) := NULL;
789 l_rules_tbl(G_PARTY_LEVEL) := NULL;
790 l_rules_tbl(G_ORG_LEVEL) := NULL;
791 l_rules_tbl(G_GLOBAL_LEVEL) := NULL;
792
793 --get the party id and org id from the input or from the contract
794 l_party_id := p_party_id;
795 l_org_id := p_org_id;
796
797
798 --get the contract party, org and the contract level rules if p_chr_id is passed
799 IF(p_chr_id IS NOT NULL) THEN
800
801 -- if contract party id is not passed get it from okc_k_party_roles_b
802 IF (l_party_id IS NULL) THEN
803 OPEN c_k_party(p_chr_id);
804 FETCH c_k_party INTO l_party_id;
805 CLOSE c_k_party;
806
807 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
808 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
809 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.get_party', 'l_party_id=' || l_party_id);
810 END IF;
811 END IF;
812
813 END IF;
814
815 --get the contract attributes from okc_k_headers_all_b and oks_k_headers_b
816 --also get the contract org id, use this if p_org_id is not passed
817 OPEN c_k_rules(p_chr_id);
818 FETCH c_k_rules INTO lk_rnrl_rec.renewal_type,
819 lk_rnrl_rec.renewal_pricing_type, lk_rnrl_rec.markup_percent, lk_rnrl_rec.price_list_id1,
820 lk_rnrl_rec.po_required_yn,
821 lk_rnrl_rec.revenue_estimated_percent, lk_rnrl_rec.revenue_estimated_duration, lk_rnrl_rec.revenue_estimated_period,
822 lk_rnrl_rec.billing_profile_id, lk_rnrl_rec.grace_duration, lk_rnrl_rec.grace_period,
823 lk_rnrl_rec.base_currency, lk_rnrl_rec.approval_type, l_k_org_id;
824
825 IF (c_k_rules%notfound) THEN
826 FND_MESSAGE.set_name(G_OKS_APP_NAME, 'OKS_INV_CONTRACT');
827 FND_MESSAGE.set_token('CONTRACT_ID', p_chr_id);
828 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
829 FND_LOG.message(FND_LOG.level_error, l_mod_name || '.get_k_rules', FALSE);
830 END IF;
831 FND_MSG_PUB.ADD;
832 CLOSE c_k_rules;
833 RAISE FND_API.g_exc_error;
834 END IF;
835 CLOSE c_k_rules;
836
837 --if p_org_id is not passed use the contract org id
838 IF (l_org_id IS NULL) THEN
839 l_org_id := l_k_org_id;
840 END IF;
841
842 IF (lk_rnrl_rec.price_list_id1 IS NOT NULL) THEN
843 lk_rnrl_rec.price_list_id2 := '#';
844 END IF;
845 l_rules_tbl(G_CONTRACT_LEVEL) := lk_rnrl_rec;
846
847 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
848 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
849 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.get_org', 'l_org_id=' || l_org_id);
850 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.contract_level_rules', 'see following log');
851 log_rules(l_mod_name || '.contract_level_rules', l_rules_tbl(G_CONTRACT_LEVEL));
852 END IF;
853 END IF;
854
855 END IF; -- of if(p_chr_id is not null) then
856
857 --get the party, org and global level rules
858 --this cursor will fetch at max 3 records
859 OPEN c_cgd_rules(l_org_id, l_party_id, nvl(p_date, SYSDATE));
860 FETCH c_cgd_rules BULK COLLECT INTO l_rules_tbl_tmp;
861 CLOSE c_cgd_rules;
862
863 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
864 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
865 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.get_gcd_rules', 'gcd_record_count=' || l_rules_tbl_tmp.COUNT);
866 END IF;
867 END IF;
868
869 --assign the rules table
870 FOR i IN l_rules_tbl_tmp.first..l_rules_tbl_tmp.last LOOP
871 IF (l_rules_tbl_tmp(i).cdt_type = 'MDT') THEN
872 l_rules_tbl(G_GLOBAL_LEVEL) := l_rules_tbl_tmp(i);
873 ELSIF (l_rules_tbl_tmp(i).cdt_type = 'SDT' AND l_rules_tbl_tmp(i).jtot_object_code = 'OKX_OPERUNIT') THEN
874 l_rules_tbl(G_ORG_LEVEL) := l_rules_tbl_tmp(i);
875 ELSIF (l_rules_tbl_tmp(i).cdt_type = 'SDT' AND l_rules_tbl_tmp(i).jtot_object_code = 'OKX_PARTY') THEN
876 l_rules_tbl(G_PARTY_LEVEL) := l_rules_tbl_tmp(i);
877 END IF;
878 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
879 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
880 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.gcd_loop', 'cdt_type=' || l_rules_tbl_tmp(i).cdt_type ||' , jtot_object_code'|| l_rules_tbl_tmp(i).jtot_object_code);
881 END IF;
882 END IF;
883 END LOOP;
884 l_rules_tbl_tmp.DELETE;
885
886 --log all the rules fetched if debug logging enabled
887 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
888 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
889 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.party_level_rules', 'see following log');
890 log_rules(l_mod_name || '.party_level_rules', l_rules_tbl(G_PARTY_LEVEL));
891 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.org_level_rules', 'see following log');
892 log_rules(l_mod_name || '.org_level_rules', l_rules_tbl(G_ORG_LEVEL));
893 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.global_level_rules', 'see following log');
894 log_rules(l_mod_name || '.global_level_rules', l_rules_tbl(G_GLOBAL_LEVEL));
895 END IF;
896 END IF;
897
898 --set the attributes
899 set_attributes;
900
901 l_rules_tbl.DELETE;
902
903
904 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
905 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
906 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.effective_rules', 'see following log');
907 log_rules(l_mod_name || '.effective_rules', x_rnrl_rec);
908 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.end', 'x_return_status=' || x_return_status);
909 END IF;
910 END IF;
911 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
912
913 EXCEPTION
914 WHEN FND_API.g_exc_error THEN
915 x_return_status := FND_API.g_ret_sts_error ;
916
917 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
918 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
919 END IF;
920 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
921
922 IF (c_k_party%isopen) THEN
923 CLOSE c_k_party;
924 END IF;
925 IF (c_k_rules%isopen) THEN
926 CLOSE c_k_rules;
927 END IF;
928 IF (c_cgd_rules%isopen) THEN
929 CLOSE c_cgd_rules;
930 END IF;
931
932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
934
935 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
936 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
937 END IF;
938 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
939
940 IF (c_k_party%isopen) THEN
941 CLOSE c_k_party;
942 END IF;
943 IF (c_k_rules%isopen) THEN
944 CLOSE c_k_rules;
945 END IF;
946 IF (c_cgd_rules%isopen) THEN
947 CLOSE c_cgd_rules;
948 END IF;
949
950 WHEN OTHERS THEN
951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
952
953 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
954 --first log the sqlerrm
955 l_error_text := substr (SQLERRM, 1, 240);
956 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text );
957 --then add it to the message api list
958 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
959 END IF;
960 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
961
962 IF (c_k_party%isopen) THEN
963 CLOSE c_k_party;
964 END IF;
965 IF (c_k_rules%isopen) THEN
966 CLOSE c_k_rules;
967 END IF;
968 IF (c_cgd_rules%isopen) THEN
969 CLOSE c_cgd_rules;
970 END IF;
971
972 END get_renew_rules;
973
974 -- this procedures logs all the rule attributes in the FND_LOG_messages table
975 -- use only after checking the log level
976 PROCEDURE log_rules(p_module IN VARCHAR2,
977 p_rnrl_rec IN rnrl_rec_type)
978 IS
979 l_api_name VARCHAR2(30) := 'LOG_RULES';
980 l_mod_name VARCHAR2(256) := nvl(p_module, lower(G_OKS_APP_NAME) || '.plsql.' || g_pkg_name || '.' || l_api_name);
981 BEGIN
982 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
983 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'renewal_type=' || p_rnrl_rec.renewal_type ||' ,approval_type='|| p_rnrl_rec.approval_type);
984 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'period_type='|| p_rnrl_rec.period_type ||' ,period_start='|| p_rnrl_rec.period_start ||' ,price_uom='|| p_rnrl_rec.price_uom);
985 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'base_currency=' || p_rnrl_rec.base_currency ||' ,evergreen_threshold_amt='|| p_rnrl_rec.evergreen_threshold_amt ||' ,evergreen_approval_type='|| p_rnrl_rec.evergreen_approval_type);
986 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'threshold(online)_amount='|| p_rnrl_rec.threshold_amount ||' ,online_approval_type='|| p_rnrl_rec.online_approval_type ||' ,payment_threshold_amt='|| p_rnrl_rec.payment_threshold_amt);
987 FND_LOG.string(FND_LOG.level_statement,l_mod_name,'evn_threshold_curr='|| p_rnrl_rec.evergreen_threshold_curr||' ,online_curr='|| p_rnrl_rec.threshold_currency ||' ,payment_threshold_curr='|| p_rnrl_rec.payment_threshold_curr||
988 ' ,threshold(online)_enabled_yn='||p_rnrl_rec.threshold_enabled_yn);
989 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'ren_pricing_type=' || p_rnrl_rec.renewal_pricing_type ||' , markup_pct='|| p_rnrl_rec.markup_percent ||' ,pl_id1='|| p_rnrl_rec.price_list_id1 ||' ,pl_id2='|| p_rnrl_rec.price_list_id2);
990 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'rev_est_pct=' || p_rnrl_rec.revenue_estimated_percent ||' ,rev_est_duration='|| p_rnrl_rec.revenue_estimated_duration ||' ,rev_est_period='|| p_rnrl_rec.revenue_estimated_period);
991 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'grace_period='|| p_rnrl_rec.grace_period ||' ,grace_duration='|| p_rnrl_rec.grace_duration);
992 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'pdf_id=' || p_rnrl_rec.pdf_id ||' ,qcl_id='|| p_rnrl_rec.qcl_id ||' ,cgp_new_id='|| p_rnrl_rec.cgp_new_id ||' ,cgp_renew_id='|| p_rnrl_rec.cgp_renew_id);
993 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'credit_amt='|| p_rnrl_rec.credit_amount ||' ,rle_code='|| p_rnrl_rec.rle_code ||' template_set_id='|| p_rnrl_rec.template_set_id ||' ,template_language='|| p_rnrl_rec.template_language);
994 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'user_id=' || p_rnrl_rec.user_id ||' ,email_address='|| p_rnrl_rec.email_address);
995 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'billing_profile_id='|| p_rnrl_rec.billing_profile_id ||' ,interface_price_break='|| p_rnrl_rec.interface_price_break);
996 FND_LOG.string(FND_LOG.level_statement, l_mod_name, 'po_required_yn=' || p_rnrl_rec.po_required_yn||' ,payment_terms_id1='|| p_rnrl_rec.payment_terms_id1 ||' ,payment_terms_id2='|| p_rnrl_rec.payment_terms_id2);
997 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'po_flag='|| p_rnrl_rec.purchase_order_flag ||' ,cc_flag='|| p_rnrl_rec.credit_card_flag);
998 FND_LOG.string(FND_LOG.level_statement, l_mod_name,'wire_flag='|| p_rnrl_rec.wire_flag ||' ,com_flag='|| p_rnrl_rec.commitment_number_flag ||' ,check_flag='|| p_rnrl_rec.check_flag);
999 END IF;
1000 END log_rules;
1001
1002
1003 /* stripped down version of get_renew_rules, only gets the template set id and template lang */
1004 PROCEDURE get_template_set(p_api_version IN NUMBER DEFAULT 1,
1005 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false,
1006 p_chr_id IN NUMBER,
1007 x_template_set_id OUT NOCOPY NUMBER,
1008 x_template_lang OUT NOCOPY VARCHAR2,
1009 x_return_status OUT NOCOPY VARCHAR2,
1010 x_msg_count OUT NOCOPY NUMBER,
1011 x_msg_data OUT NOCOPY VARCHAR2)
1012 IS
1013
1014 TYPE l_tset_rec IS RECORD(
1015 cdt_type oks_k_defaults.cdt_type%TYPE,
1016 jtot_object_code oks_k_defaults.jtot_object_code%TYPE,
1017 template_set_id oks_k_defaults.template_set_id%TYPE,
1018 template_language oks_k_defaults.template_language%TYPE);
1019
1020 TYPE l_tset_tbl_type IS TABLE OF l_tset_rec INDEX BY BINARY_INTEGER;
1021
1022 l_api_name CONSTANT VARCHAR2(30) := 'GET_TEMPLATE_SET';
1023 l_api_version CONSTANT NUMBER := 1;
1024 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || g_pkg_name || '.' || l_api_name;
1025
1026 l_party_id NUMBER;
1027 l_org_id NUMBER;
1028 l_tset_tbl_tmp l_tset_tbl_type;
1029 l_tset_tbl l_tset_tbl_type;
1030
1031 l_error_text VARCHAR2(512);
1032
1033 --should be outer join with party roles, so that
1034 --if we don't get a party we atleast get an org
1035 CURSOR c_k_party_org(cp_chr_id IN NUMBER) IS
1036 SELECT b.object1_id1, a.authoring_org_id
1037 FROM okc_k_headers_all_b a LEFT OUTER JOIN okc_k_party_roles_b b
1038 ON a.id = b.dnz_chr_id AND b.cle_id IS NULL AND b.jtot_object1_code = 'OKX_PARTY'
1039 AND b.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
1040 WHERE a.id = cp_chr_id;
1041
1042
1043 --cp_org_id the org for which the rules need to be obtained, can be null
1044 --cp_party_id the party for which the rules need to be obtained, can be null
1045 --cp_date the date on which the rule should be effective, should not be null
1046 -- cursor will always return 1 record and at max return 3 records
1047 CURSOR c_cgd_template_set(cp_org_id IN NUMBER, cp_party_id IN NUMBER, cp_date IN DATE) IS
1048 SELECT
1049 cdt_type, jtot_object_code, template_set_id, template_language
1050 FROM oks_k_defaults
1051 WHERE
1052 (cdt_type = 'MDT' AND jtot_object_code IS NULL AND segment_id1 IS NULL AND segment_id2 IS NULL)
1053 OR
1054 (cdt_type = 'SDT' AND jtot_object_code = 'OKX_OPERUNIT' AND segment_id1 = to_char(cp_org_id) AND segment_id2 = '#'
1055 AND trunc(cp_date) BETWEEN start_date AND nvl(end_date, greatest(start_date, trunc(cp_date))))
1056 OR
1057 (cdt_type = 'SDT' AND jtot_object_code = 'OKX_PARTY' AND segment_id1 = to_char(cp_party_id) AND segment_id2 = '#'
1058 AND trunc(cp_date) BETWEEN start_date AND nvl(end_date, greatest(start_date, trunc(cp_date))));
1059
1060
1061 BEGIN
1062 --log key input parameters
1063 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1064 IF (FND_LOG.test(FND_LOG.level_procedure, l_mod_name)) THEN
1065 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_chr_id=' || p_chr_id);
1066 END IF;
1067 END IF;
1068
1069 --standard api initilization and checks
1070 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
1071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1072 END IF;
1073 IF FND_API.to_boolean(p_init_msg_list ) THEN
1074 FND_MSG_PUB.initialize;
1075 END IF;
1076 x_return_status := FND_API.G_RET_STS_SUCCESS;
1077 x_template_set_id := NULL;
1078 x_template_lang := NULL;
1079
1080 OPEN c_k_party_org(p_chr_id);
1081 FETCH c_k_party_org INTO l_party_id, l_org_id;
1082 IF (c_k_party_org%notfound) THEN
1083 FND_MESSAGE.set_name(G_OKS_APP_NAME, 'OKS_INV_CONTRACT');
1084 FND_MESSAGE.set_token('CONTRACT_ID', p_chr_id);
1085 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1086 FND_LOG.message(FND_LOG.level_error, l_mod_name || '.get_k_org', FALSE);
1087 END IF;
1088 FND_MSG_PUB.ADD;
1089 CLOSE c_k_party_org;
1090 RAISE FND_API.g_exc_error;
1091 END IF;
1092 CLOSE c_k_party_org;
1093
1094 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1095 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
1096 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.party_org', 'l_party_id=' || l_party_id ||' , l_org_id'|| l_org_id);
1097 END IF;
1098 END IF;
1099
1100 --get the party, org and global level rules
1101 --this cursor will fetch at max 3 records
1102 OPEN c_cgd_template_set(l_org_id, l_party_id, SYSDATE);
1103 FETCH c_cgd_template_set BULK COLLECT INTO l_tset_tbl_tmp;
1104 CLOSE c_cgd_template_set;
1105
1106 --assign the rules table
1107 FOR i IN l_tset_tbl_tmp.first..l_tset_tbl_tmp.last LOOP
1108 IF (l_tset_tbl_tmp(i).cdt_type = 'MDT') THEN
1109 l_tset_tbl(G_GLOBAL_LEVEL) := l_tset_tbl_tmp(i);
1110 ELSIF (l_tset_tbl_tmp(i).cdt_type = 'SDT' AND l_tset_tbl_tmp(i).jtot_object_code = 'OKX_OPERUNIT') THEN
1111 l_tset_tbl(G_ORG_LEVEL) := l_tset_tbl_tmp(i);
1112 ELSIF (l_tset_tbl_tmp(i).cdt_type = 'SDT' AND l_tset_tbl_tmp(i).jtot_object_code = 'OKX_PARTY') THEN
1113 l_tset_tbl(G_PARTY_LEVEL) := l_tset_tbl_tmp(i);
1114 END IF;
1115
1116 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1117 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
1118 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.gcd_loop', 'cdt_type=' || l_tset_tbl_tmp(i).cdt_type ||' , jtot_object_code'|| l_tset_tbl_tmp(i).jtot_object_code);
1119 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.gcd_loop','template_set_id='|| l_tset_tbl_tmp(i).template_set_id ||' ,template_language='|| l_tset_tbl_tmp(i).template_language);
1120 END IF;
1121 END IF;
1122 END LOOP;
1123 l_tset_tbl_tmp.DELETE;
1124
1125 FOR i IN l_tset_tbl.first..l_tset_tbl.last LOOP
1126
1127 IF(x_template_set_id IS NULL) THEN
1128 x_template_set_id := l_tset_tbl(i).template_set_id;
1129 END IF;
1130
1131 IF(x_template_lang IS NULL) THEN
1132 x_template_lang := l_tset_tbl(i).template_language;
1133 END IF;
1134
1135 END LOOP;
1136 l_tset_tbl.DELETE;
1137
1138 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1139 IF (FND_LOG.test(FND_LOG.level_procedure, l_mod_name)) THEN
1140 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end','x_template_set_id ='|| x_template_set_id ||', x_return_status='|| x_return_status);
1141 END IF;
1142 END IF;
1143 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
1144
1145 EXCEPTION
1146
1147 WHEN FND_API.g_exc_error THEN
1148 x_return_status := FND_API.g_ret_sts_error ;
1149
1150 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1151 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
1152 END IF;
1153 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
1154
1155 IF (c_k_party_org%isopen) THEN
1156 CLOSE c_k_party_org;
1157 END IF;
1158 IF (c_cgd_template_set%isopen) THEN
1159 CLOSE c_cgd_template_set;
1160 END IF;
1161
1162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1164
1165 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1166 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
1167 END IF;
1168 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
1169
1170 IF (c_k_party_org%isopen) THEN
1171 CLOSE c_k_party_org;
1172 END IF;
1173 IF (c_cgd_template_set%isopen) THEN
1174 CLOSE c_cgd_template_set;
1175 END IF;
1176
1177 WHEN OTHERS THEN
1178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1179
1180 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1181 --first log the sqlerrm
1182 l_error_text := substr (SQLERRM, 1, 240);
1183 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1184 --then add it to the message api list
1185 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1186 END IF;
1187 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
1188
1189 IF (c_k_party_org%isopen) THEN
1190 CLOSE c_k_party_org;
1191 END IF;
1192 IF (c_cgd_template_set%isopen) THEN
1193 CLOSE c_cgd_template_set;
1194 END IF;
1195
1196 END get_template_set;
1197
1198
1199 /* utility function to get template set id */
1200 FUNCTION get_template_set_id(p_chr_id IN NUMBER
1201 ) RETURN NUMBER
1202 IS
1203
1204 l_template_set_id NUMBER := NULL;
1205 l_template_lang VARCHAR2(5) := NULL;
1206 l_return_status VARCHAR2(1);
1207 l_msg_count NUMBER;
1208 l_msg_data VARCHAR2(4000);
1209
1210 BEGIN
1211 get_template_set(p_api_version => 1,
1212 p_init_msg_list => FND_API.G_FALSE,
1213 p_chr_id => p_chr_id,
1214 x_template_set_id => l_template_set_id,
1215 x_template_lang => l_template_lang,
1216 x_return_status => l_return_status,
1217 x_msg_count => l_msg_count,
1218 x_msg_data => l_msg_data);
1219 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1220 RAISE FND_API.g_exc_unexpected_error;
1221 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1222 RAISE FND_API.g_exc_error;
1223 END IF;
1224
1225 RETURN l_template_set_id;
1226
1227 END get_template_set_id;
1228
1229 /* utility function to get template set lang */
1230 FUNCTION get_template_lang(p_chr_id IN NUMBER
1231 ) RETURN VARCHAR2
1232 IS
1233
1234 l_template_set_id NUMBER := NULL;
1235 l_template_lang VARCHAR2(5) := NULL;
1236 l_return_status VARCHAR2(1);
1237 l_msg_count NUMBER;
1238 l_msg_data VARCHAR2(4000);
1239
1240 BEGIN
1241 get_template_set(p_api_version => 1,
1242 p_init_msg_list => FND_API.G_FALSE,
1243 p_chr_id => p_chr_id,
1244 x_template_set_id => l_template_set_id,
1245 x_template_lang => l_template_lang,
1246 x_return_status => l_return_status,
1247 x_msg_count => l_msg_count,
1248 x_msg_data => l_msg_data);
1249
1250 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1251 RAISE FND_API.g_exc_unexpected_error;
1252 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1253 RAISE FND_API.g_exc_error;
1254 END IF;
1255
1256 RETURN l_template_lang;
1257
1258 END get_template_lang;
1259
1260 /*
1261 Procedure evaluates the renewal rules setup in Contract or GCD to determine
1262 the effective renewal type for a contract.
1263
1264 Parameters
1265 p_chr_id : id of the contract whose renewal type needs to be determined, mandatory
1266 p_amount : contract amount, optional, if not passed derived from p_chr_id
1267 p_currency_code : contract currency, optional, if not passed derived from p_chr_id
1268 p_rnrl_rec : record containing the effective renewal rules for the contract,
1269 optional, if not populated, derived from p_chr_id
1270 x_renewal_type : renewal type as determined
1271 x_approval_type : approval type associated with the renewal type
1272 x_threshold_used : Y|N flag indicating, if GCD thresholds were used to determine the renewal type
1273 */
1274 PROCEDURE GET_RENEWAL_TYPE
1275 (
1276 p_api_version IN NUMBER,
1277 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1278 x_return_status OUT NOCOPY VARCHAR2,
1279 x_msg_count OUT NOCOPY NUMBER,
1280 x_msg_data OUT NOCOPY VARCHAR2,
1281 p_chr_id IN NUMBER,
1282 p_amount IN NUMBER DEFAULT NULL,
1283 p_currency_code IN VARCHAR2 DEFAULT NULL,
1284 p_rnrl_rec IN rnrl_rec_type DEFAULT NULL,
1285 x_renewal_type OUT NOCOPY VARCHAR2,
1286 x_approval_type OUT NOCOPY VARCHAR2,
1287 x_threshold_used OUT NOCOPY VARCHAR2
1288 )
1289 IS
1290
1291 l_api_name CONSTANT VARCHAR2(30) := 'GET_RENEWAL_TYPE';
1292 l_api_version CONSTANT NUMBER := 1;
1293 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
1294 l_error_text VARCHAR2(512);
1295
1296 CURSOR c_k_hdr(cp_chr_id IN NUMBER) IS
1297 SELECT estimated_amount, currency_code, start_date
1298 FROM okc_k_headers_all_b
1299 WHERE id = cp_chr_id;
1300
1301 l_amount NUMBER;
1302 l_currency_code VARCHAR2(15);
1303 l_start_date DATE;
1304 l_rnrl_rec OKS_RENEW_UTIL_PVT.rnrl_rec_type;
1305
1306 BEGIN
1307 --log key input parameters
1308 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1309 IF (FND_LOG.test(FND_LOG.level_procedure, l_mod_name)) THEN
1310 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_api_version=' || p_api_version ||' ,p_chr_id='|| p_chr_id||' ,p_amount='||p_amount||
1311 ' ,p_currency_code='||p_currency_code||' ,p_rnrl_rec.base_currency='||p_rnrl_rec.base_currency);
1312 END IF;
1313 END IF;
1314
1315 --standard api initilization and checks
1316 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318 END IF;
1319 IF FND_API.to_boolean(p_init_msg_list ) THEN
1320 FND_MSG_PUB.initialize;
1321 END IF;
1322 x_return_status := FND_API.G_RET_STS_SUCCESS;
1323
1324 --basic input validation
1325 IF(p_chr_id IS NULL) THEN
1326 FND_MESSAGE.set_name(G_OKS_APP_NAME, 'OKS_MANDATORY_ARG');
1327 FND_MESSAGE.set_token('ARG_NAME', 'p_chr_id');
1328 FND_MESSAGE.set_token('PROG_NAME', G_PKG_NAME || '.' || l_api_name);
1329 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1330 FND_LOG.message(FND_LOG.level_error, l_mod_name || '.input_validation', FALSE);
1331 END IF;
1332 FND_MSG_PUB.ADD;
1333 RAISE FND_API.g_exc_error;
1334 END IF;
1335
1336 --get contract details if they are not passed
1337 IF ((p_amount IS NULL) OR (p_currency_code IS NULL) OR (p_rnrl_rec.base_currency IS NULL)) THEN
1338
1339 OPEN c_k_hdr(p_chr_id);
1340 FETCH c_k_hdr INTO l_amount, l_currency_code, l_start_date;
1341 CLOSE c_k_hdr;
1342
1343 --if invalid contract
1344 IF (l_start_date IS NULL) THEN
1345 FND_MESSAGE.set_name(G_OKS_APP_NAME, 'OKS_INV_CONTRACT');
1346 FND_MESSAGE.set_token('CONTRACT_ID', p_chr_id);
1347 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1348 FND_LOG.message(FND_LOG.level_error, l_mod_name || '.get_k_details', FALSE);
1349 END IF;
1350 FND_MSG_PUB.ADD;
1351 RAISE FND_API.g_exc_error;
1352 END IF;
1353
1354 --means the renewal rules are not passed, get them
1355 IF (p_rnrl_rec.base_currency IS NULL) THEN
1356
1357 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1358 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.calling_get_renew_rules', 'p_chr_id=' || p_chr_id ||', p_date='|| l_start_date);
1359 END IF;
1360
1361 OKS_RENEW_UTIL_PVT.get_renew_rules(
1362 x_return_status => x_return_status,
1363 p_api_version => 1.0,
1364 p_init_msg_list => FND_API.G_FALSE,
1365 p_chr_id => p_chr_id,
1366 p_party_id => NULL,
1367 p_org_id => NULL,
1368 p_date => l_start_date,
1369 p_rnrl_rec => p_rnrl_rec,
1370 x_rnrl_rec => l_rnrl_rec,
1371 x_msg_count => x_msg_count,
1372 x_msg_data => x_msg_data);
1373
1374 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1375 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.after_get_renew_rules', 'x_return_status=' || x_return_status);
1376 END IF;
1377 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1378 RAISE FND_API.g_exc_unexpected_error;
1379 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1380 RAISE FND_API.g_exc_error;
1381 END IF;
1382
1383 END IF; --of IF (p_rnrl_rec.base_currency IS NULL) THEN
1384
1385 END IF; --of IF ((p_amount IS NULL) OR (p_currency_code IS NULL) OR (p_rnrl_rec.base_currency IS NULL)) THEN
1386
1387 l_amount := nvl(p_amount, l_amount);
1388 l_currency_code := nvl(p_currency_code, l_currency_code);
1389 IF(p_rnrl_rec.base_currency IS NULL) THEN
1390 NULL;
1391 ELSE
1392 l_rnrl_rec := p_rnrl_rec;
1393 END IF;
1394
1395 --now determine the effective renewal type
1396 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1397 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.renewal_rules', 'l_rnrl_rec.renewal_type='||l_rnrl_rec.renewal_type||' ,l_rnrl_rec.approval_type='||l_rnrl_rec.approval_type||
1398 ' ,l_rnrl_rec.base_currency='||l_rnrl_rec.base_currency||' ,l_rnrl_rec.evergreen_threshold_amt='||l_rnrl_rec.evergreen_threshold_amt||' ,l_rnrl_rec.evergreen_approval_type='||l_rnrl_rec.evergreen_approval_type||
1399 ' ,l_rnrl_rec.threshold_amount='||l_rnrl_rec.threshold_amount||' ,l_rnrl_rec.online_approval_type='||l_rnrl_rec.online_approval_type||' ,l_amount='||l_amount||' ,l_currency_code='||l_currency_code);
1400 END IF;
1401
1402 --initialize to default value, if GCD not setup
1403 l_rnrl_rec.renewal_type := nvl(l_rnrl_rec.renewal_type, 'NSR');
1404
1405 --first check if renewal_type = 'DNR';
1406 IF(l_rnrl_rec.renewal_type = 'DNR') THEN
1407 x_renewal_type := 'DNR';
1408 x_approval_type := NULL;
1409 x_threshold_used := NULL;
1410 ELSE
1411
1412 --first priority given to EVN : evergreen, then ERN : online, then NSR: manual
1413 --first check if renewal type is EVN
1414 -- Bug 5859046, don't change renewal type to EVN if evergreen_threshold_amt IS NULL
1415 IF(l_rnrl_rec.renewal_type = 'EVN') THEN
1416 x_renewal_type := 'EVN';
1417 x_approval_type := nvl(l_rnrl_rec.approval_type, 'Y'); --Required
1418 x_threshold_used := 'N';
1419 ELSE
1420
1421 --then check if amount < evergreen threshold
1422 IF ( (l_currency_code = l_rnrl_rec.base_currency) AND
1423 (l_amount <= l_rnrl_rec.evergreen_threshold_amt) ) THEN -- Bug 5859046
1424 x_renewal_type := 'EVN';
1425 x_approval_type := nvl(l_rnrl_rec.evergreen_approval_type, 'Y'); --Required
1426 x_threshold_used := 'Y';
1427 ELSE
1428
1429 --if EVN fails, check if renewal type is ERN
1430 -- Bug 5859046, don't change renewal type to ERN if threshold_amount IS NULL
1431 IF(l_rnrl_rec.renewal_type = 'ERN') THEN
1432 x_renewal_type := 'ERN';
1433 x_approval_type := nvl(l_rnrl_rec.approval_type, 'M'); --Manual
1434 x_threshold_used := 'N';
1435 ELSE
1436 --if online renewal threhold is enabled and amount < online threshold
1437 IF ((nvl(l_rnrl_rec.threshold_enabled_yn, 'N') = 'Y') AND
1438 (l_currency_code = l_rnrl_rec.base_currency) AND
1439 (l_amount <= l_rnrl_rec.threshold_amount) ) THEN -- Bug 5859046
1440 x_renewal_type := 'ERN';
1441 x_approval_type := nvl(l_rnrl_rec.online_approval_type, 'M'); --Manual
1442 x_threshold_used := 'Y';
1443 ELSE
1444 --if both EVN and ERN fail
1445 x_renewal_type := 'NSR';
1446 x_approval_type := nvl(l_rnrl_rec.approval_type, 'Y'); --Required
1447 x_threshold_used := 'N';
1448 END IF;
1449 END IF;
1450 END IF;
1451 END IF; --IF(l_rnrl_rec.renewal_type = 'EVN') THEN
1452 END IF; --of IF(l_rnrl_rec.renewal_type = 'DNR') THEN
1453
1454 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1455 IF (FND_LOG.test(FND_LOG.level_procedure, l_mod_name)) THEN
1456 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', ' x_return_status='|| x_return_status||', x_renewal_type='||x_renewal_type||' ,x_approval_type='||x_approval_type||' ,x_threshold_used='||x_threshold_used);
1457 END IF;
1458 END IF;
1459 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
1460
1461 EXCEPTION
1462 WHEN FND_API.g_exc_error THEN
1463 x_return_status := FND_API.g_ret_sts_error ;
1464
1465 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
1466 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
1467 END IF;
1468 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
1469
1470 IF (c_k_hdr%isopen) THEN
1471 CLOSE c_k_hdr;
1472 END IF;
1473
1474 WHEN FND_API.g_exc_unexpected_error THEN
1475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1476
1477 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1478 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
1479 END IF;
1480 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
1481
1482 IF (c_k_hdr%isopen) THEN
1483 CLOSE c_k_hdr;
1484 END IF;
1485
1486 WHEN OTHERS THEN
1487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1488
1489 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
1490 --first log the sqlerrm
1491 l_error_text := substr (SQLERRM, 1, 240);
1492 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
1493 --then add it to the message api list
1494 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
1495 END IF;
1496 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
1497
1498 IF (c_k_hdr%isopen) THEN
1499 CLOSE c_k_hdr;
1500 END IF;
1501
1502 END GET_RENEWAL_TYPE;
1503
1504 -- This API checks if function is accessible under current responsibility.
1505 -- by calling fnd_function.test. This is a wrapper on fnd_function.test
1506 -- parameters
1507 -- function_name - function to test
1508 -- RETURNS
1509 -- Y if function is accessible else N
1510
1511 FUNCTION get_function_access (p_function_name VARCHAR2)
1512 return VARCHAR2
1513 IS
1514 l_return_val VARCHAR2(1);
1515
1516 BEGIN
1517 IF fnd_function.test(p_function_name, 'Y') THEN
1518 l_return_val := 'Y';
1519 ELSE
1520 l_return_val := 'N';
1521 END IF;
1522 return(l_return_val);
1523 EXCEPTION
1524 WHEN others THEN
1525 l_return_val := 'N';
1526 return(l_return_val);
1527 END get_function_access;
1528
1529 /*=========================================================================
1530 API name : get_language_info
1531 Type : Private.
1532 Function : This procedure derives the language and territory that
1533 will be used to generate message or documents to be sent
1534 to customer.
1535 Pre-reqs : None.
1536 Parameters :
1537 IN : p_api_version IN NUMBER Required
1538 Api version
1539 : p_init_msg_list IN VARCHAR2 Required
1540 Initialize message stack parameter
1541 : p_contract_id IN NUMBER Required
1542 Contract header Id
1543 : p_document_type IN VARCHAR2 Required
1544 Type of the layout template like Quote, Activation etc
1545 : p_template_id IN NUMBER Required
1546 Message layout template id can be passed if available
1547 so that external api call is avoided.
1548 : p_template_language IN VARCHAR2 Required
1549 Language defined in GCD if availble to avoid api call
1550 OUT : x_fnd_language OUT VARCHAR2
1551 Returns fnd_languages's language code.
1552 : x_fnd_iso_language OUT VARCHAR2
1553 Returns fnd_language's iso_language
1554 : x_fnd_iso_territory OUT VARCHAR2
1555 Returns fnd_language's iso_territory
1556 : x_gcd_template_lang OUT NOCOPY VARCHAR2
1557 Returns language defined in GCD
1558 : x_return_status OUT VARCHAR2
1559 Api return status
1560 : x_msg_count OUT NUMBER
1561 Count of message on error stack
1562 : x_msg_data OUT VARCHAR2
1563 Actual error messages on error stack
1564 Note :
1565 =========================================================================*/
1566 PROCEDURE get_language_info
1567 (
1568 p_api_version IN NUMBER,
1569 p_init_msg_list IN VARCHAR2,
1570 p_contract_id IN NUMBER,
1571 p_document_type IN VARCHAR2 DEFAULT 'QUOTE',
1572 p_template_id IN NUMBER DEFAULT NULL,
1573 p_template_language IN VARCHAR2 DEFAULT NULL,
1574 x_fnd_language OUT NOCOPY VARCHAR2,
1575 x_fnd_iso_language OUT NOCOPY VARCHAR2,
1576 x_fnd_iso_territory OUT NOCOPY VARCHAR2,
1577 x_gcd_template_lang OUT NOCOPY VARCHAR2,
1578 x_return_status OUT NOCOPY VARCHAR2,
1579 x_msg_count OUT NOCOPY NUMBER,
1580 x_msg_data OUT NOCOPY VARCHAR2
1581 ) IS
1582
1583 l_api_version CONSTANT NUMBER := 1.0;
1584 l_api_name CONSTANT VARCHAR2(50) := 'get_language_info';
1585
1586 CURSOR l_fnd_lang_terr_csr (p_lang VARCHAR2) IS
1587 SELECT lower(iso_language), iso_territory
1588 FROM fnd_languages
1589 WHERE language_code = p_lang;
1590
1591 --cgopinee bugfix for 6802038
1592 CURSOR l_template_def_lang_csr (p_template_id NUMBER) IS
1593 SELECT CASE WHEN a.installed_flag = 'D' THEN 'US' ELSE a.language_code END language_code,
1594 CASE WHEN a.installed_flag = 'D' THEN 'en' ELSE b.default_language END default_language,
1595 CASE WHEN a.installed_flag = 'D' THEN '00' ELSE b.default_territory END default_territory
1596 FROM fnd_languages a, xdo_templates_b b
1597 WHERE a.iso_language = upper(b.default_language)
1598 AND b.template_id = p_template_id
1599 AND (a.iso_territory = b.default_territory
1600 OR (b.default_territory = '00' and a.installed_flag IN ('B','I','D')))
1601 ORDER BY a.installed_flag ASC;
1602
1603 l_message_template_id NUMBER;
1604 l_attachment_template_id NUMBER;
1605 l_attachment_name VARCHAR2(150);
1606 l_k_status VARCHAR2(30);
1607
1608 BEGIN
1609
1610 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1611 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1612 'Entered '||G_PKG_NAME ||'.'||l_api_name||
1613 '(p_contract_id=>'||p_contract_id||
1614 ',p_document_type=>'||p_document_type||
1615 ',p_template_id=>'||p_template_id||
1616 ',p_template_language=>'||p_template_language||');');
1617 END IF;
1618
1619 -- Standard call to check for call compatibility.
1620 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1621 x_return_status := FND_API.G_RET_STS_ERROR;
1622 RAISE FND_API.G_EXC_ERROR;
1623 END IF;
1624
1625 -- Initialize message list if p_init_msg_list is set to TRUE.
1626 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1627 FND_MSG_PUB.initialize;
1628 END IF;
1629
1630 -- Initialize API return status to success
1631 x_return_status := FND_API.G_RET_STS_SUCCESS;
1632
1633 IF p_contract_id IS NULL THEN
1634 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_INVD_CONTRACT_ID');
1635 FND_MESSAGE.SET_TOKEN('HDR_ID',p_contract_id);
1636 FND_MSG_PUB.add;
1637 RAISE FND_API.G_EXC_ERROR;
1638 END IF;
1639
1640 -- If language is known already, use it to get iso_language and territory
1641 IF p_template_language IS NOT NULL THEN
1642 x_fnd_language := p_template_language;
1643 ELSE
1644 x_fnd_language := OKS_RENEW_UTIL_PVT.get_template_lang(p_chr_id => p_contract_id);
1645 END IF;
1646
1647 -- This is used to get character set in which an email has to be delivered
1648 x_gcd_template_lang := x_fnd_language;
1649
1650 -- If you got the language from GCD get iso_language and territory
1651 IF x_fnd_language IS NOT NULL THEN
1652 OPEN l_fnd_lang_terr_csr(x_fnd_language);
1653 FETCH l_fnd_lang_terr_csr INTO x_fnd_iso_language, x_fnd_iso_territory;
1654 CLOSE l_fnd_lang_terr_csr;
1655 -- If language is not found in GCD, get layout template to get default language, territory
1656 ELSE
1657 IF p_template_id IS NOT NULL THEN
1658 l_message_template_id := p_template_id;
1659 ELSE
1660 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1661 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
1662 'OKS_TEMPLATE_SET_PUB.get_template_set_dtls(p_contract_id= '
1663 ||p_contract_id||' p_document_type ='||p_document_type||')');
1664 END IF;
1665 OKS_TEMPLATE_SET_PUB.get_template_set_dtls
1666 (
1667 p_api_version => p_api_version,
1668 p_init_msg_list => p_init_msg_list,
1669 p_contract_id => p_contract_id,
1670 p_document_type => p_document_type,
1671 x_template_language => x_fnd_language,
1672 x_message_template_id => l_message_template_id,
1673 x_attachment_template_id => l_attachment_template_id,
1674 x_attachment_name => l_attachment_name,
1675 x_contract_update_status => l_k_status,
1676 x_return_status => x_return_status,
1677 x_msg_data => x_msg_data,
1678 x_msg_count => x_msg_count
1679 );
1680 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1681 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
1682 'OKS_TEMPLATE_SET_PUB.get_template_set_dtls(x_return_status= '||x_return_status||
1683 ' x_msg_count ='||x_msg_count||')');
1684 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
1685 ' x_template_language ='||x_fnd_language||
1686 ' x_message_template_id ='||l_message_template_id||
1687 ' x_attachment_template_id ='||l_attachment_template_id||
1688 ' x_attachment_name ='||l_attachment_name||
1689 ' x_contract_update_status ='||l_k_status);
1690 END IF;
1691 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1692 RAISE FND_API.G_EXC_ERROR;
1693 END IF;
1694 END IF;
1695 -- Get layout template's default language to get installed /
1696 -- base language's iso language and territory
1697 IF l_message_template_id IS NOT NULL THEN
1698 OPEN l_template_def_lang_csr(l_message_template_id);
1699 FETCH l_template_def_lang_csr INTO x_fnd_language,x_fnd_iso_language,x_fnd_iso_territory;
1700 CLOSE l_template_def_lang_csr;
1701 ELSE
1702 x_fnd_language := NULL;
1703 x_fnd_iso_language := NULL;
1704 x_fnd_iso_territory := NULL;
1705 -- skip raising an error instead return null values
1706 -- FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_NOLAYOUT_TEMPLATE');
1707 -- FND_MSG_PUB.add;
1708 -- RAISE FND_API.G_EXC_ERROR;
1709 END IF;
1710 END IF;
1711
1712 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1713 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1714 'x_fnd_language '||x_fnd_language||
1715 ' x_fnd_iso_language '||x_fnd_iso_language||
1716 ' x_fnd_iso_territory ' ||x_fnd_iso_territory);
1717 END IF;
1718 -- Standard call to get message count and if count is 1, get message info.
1719 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count=>x_msg_count, p_data=>x_msg_data);
1720
1721 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1722 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,G_MODULE||l_api_name,
1723 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
1724 END IF;
1725
1726 EXCEPTION
1727 WHEN FND_API.G_EXC_ERROR THEN
1728 x_return_status := FND_API.G_RET_STS_ERROR;
1729 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1730 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1731 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE||l_api_name,
1732 'Leaving '||G_PKG_NAME ||'.'||l_api_name||'.ERROR');
1733 END IF;
1734 WHEN OTHERS THEN
1735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1736 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1737 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE||l_api_name,
1738 'Leaving '||G_PKG_NAME ||'.'||l_api_name||'.Others sqlcode = '
1739 ||SQLCODE||', sqlerrm = '||SQLERRM);
1740 END IF;
1741 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1742 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1743 END get_language_info;
1744
1745 END OKS_RENEW_UTIL_PVT;
[Home] [Help]