[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_CONTRACT_PRTFL_PVT
Source
1 PACKAGE BODY OKL_AM_CONTRACT_PRTFL_PVT AS
2 /* $Header: OKLRPTFB.pls 120.2 2006/07/12 09:03:58 dpsingh noship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : create_cntrct_prtfl
7 -- Description : procedure used to create portfolio management strategy profile.
8 -- Business Rules :
9 -- Parameters : p_contract_id : Contract Id
10 -- Version : 1.0
11 -- History : SECHAWLA 24-DEC-02 : Bug # 2726739
12 -- Added logic to store currency codes and conversion factors
13 -- : SECHAWLA 21-AUG-03 : Bug # 108113
14 -- Perform explicit conversion for rule_information fields
15 -- End of comments
16
17
18 PROCEDURE create_cntrct_prtfl(
19 p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 p_contract_id IN NUMBER) IS
25
26 SUBTYPE pfcv_rec_type IS okl_prtfl_contracts_pub.pfcv_rec_type;
27 SUBTYPE pflv_rec_type IS okl_prtfl_lines_pub.pflv_rec_type;
28
29 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
30
31
32 l_api_version CONSTANT NUMBER := 1;
33 l_api_name CONSTANT VARCHAR2(30) := 'create_cntrct_prtfl';
34
35 lp_pfcv_rec pfcv_rec_type;
36 lx_pfcv_rec pfcv_rec_type;
37
38 lp_pflv_rec pflv_rec_type;
39 lx_pflv_rec pflv_rec_type;
40 l_rulv_rec okl_rule_pub.rulv_rec_type;
41
42 l_contract_number VARCHAR2(120);
43 l_budget_amount NUMBER;
44 l_strategy VARCHAR2(30);
45 l_assignment_grp_id NUMBER;
46 l_formulae_id NUMBER;
47 l_end_date DATE;
48 l_approval_required VARCHAR2(1);
49 l_dummy VARCHAR2(1);
50 budget_amount_error EXCEPTION;
51 strategy_error EXCEPTION;
52 assignment_group_error EXCEPTION;
53 execution_date_error EXCEPTION;
54
55 --SECHAWLA Bug # 2726739 : new declarations
56 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
57 l_contract_curr_code okc_k_headers_b.currency_code%TYPE;
58 lx_contract_currency okl_k_headers_full_v.currency_code%TYPE;
59 lx_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%TYPE;
60 lx_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
61 lx_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%TYPE;
62 lx_converted_amount NUMBER;
63 l_sysdate DATE;
64
65
66 -- This cursor is used to get the formulae id
67 CURSOR l_formulae_csr(p_name VARCHAR2) IS
68 SELECT id
69 FROM okl_formulae_b
70 WHERE name = p_name;
71
72 -- This cursor is used to get the contract end date
73 CURSOR l_okcheaders_csr IS
74 SELECT end_date, contract_number
75 FROM okc_k_headers_b
76 WHERE id = p_contract_id;
77
78 -- TAPI validation ??
79 -- This cursor is used to validate the contract ID
80 CURSOR l_oklheaders_csr IS
81 SELECT 'x'
82 FROM Okl_K_Headers_V
83 WHERE id = p_contract_id;
84
85 -- This cursor is used to make sure that the profile does not already exist for a contract.
86 CURSOR l_oklprtfl_csr IS
87 SELECT 'x'
88 FROM okl_prtfl_cntrcts_b
89 WHERE khr_id = p_contract_id;
90
91 BEGIN
92
93 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
94 G_PKG_NAME,
95 p_init_msg_list,
96 l_api_version,
97 p_api_version,
98 '_PVT',
99 x_return_status);
100
101
102
103 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
104 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
105 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
106 RAISE OKL_API.G_EXCEPTION_ERROR;
107 END IF;
108
109 -- SECHAWLA Bug # 2726739 : using sysdate as transaction date for currency conversion routines
110 SELECT SYSDATE INTO l_sysdate FROM DUAL;
111
112 IF p_contract_id IS NULL OR p_contract_id = OKL_API.G_MISS_NUM THEN
113 x_return_status := OKL_API.G_RET_STS_ERROR;
114 -- Contarct ID is required
115 OKL_API.set_message( p_app_name => 'OKC',
116 p_msg_name => G_REQUIRED_VALUE,
117 p_token1 => G_COL_NAME_TOKEN,
118 p_token1_value => 'CONTRACT_ID');
119 RAISE OKL_API.G_EXCEPTION_ERROR;
120 END IF;
121
122 -- Validate contarct ID
123 OPEN l_oklheaders_csr;
124 FETCH l_oklheaders_csr INTO l_dummy;
125 IF l_oklheaders_csr%NOTFOUND THEN
126 x_return_status := OKL_API.G_RET_STS_ERROR;
127 -- Contarct ID is invalid
128 OKL_API.set_message( p_app_name => 'OKC',
129 p_msg_name => G_INVALID_VALUE,
130 p_token1 => G_COL_NAME_TOKEN,
131 p_token1_value => 'CONTRACT_ID');
132 RAISE OKL_API.G_EXCEPTION_ERROR;
133 END IF;
134 CLOSE l_oklheaders_csr;
135
136 -- get the contarct number and end date
137 OPEN l_okcheaders_csr;
138 FETCH l_okcheaders_csr INTO l_end_date, l_contract_number;
139 IF l_okcheaders_csr%NOTFOUND THEN
140 x_return_status := OKL_API.G_RET_STS_ERROR;
141 -- Contarct ID is invalid
142 OKL_API.set_message( p_app_name => 'OKC',
143 p_msg_name => G_INVALID_VALUE,
144 p_token1 => G_COL_NAME_TOKEN,
145 p_token1_value => 'Contract Id');
146 RAISE OKL_API.G_EXCEPTION_ERROR;
147 END IF;
148 CLOSE l_okcheaders_csr;
149
150 -- Check if profile already exists
151 OPEN l_oklprtfl_csr;
152 FETCH l_oklprtfl_csr INTO l_dummy;
153 IF l_oklprtfl_csr%FOUND THEN
154 x_return_status := OKL_API.G_RET_STS_ERROR;
155 -- Portfolio Management Strategy Profile already exists for contract CONTRACT_NUMBER.
156 OKL_API.set_message( p_app_name => 'OKL',
157 p_msg_name => 'OKL_AM_PROFILE_EXISTS',
158 p_token1 => 'CONTRACT_NUMBER',
159 p_token1_value => l_contract_number);
160 RAISE OKL_API.G_EXCEPTION_ERROR;
161 END IF;
162 CLOSE l_oklprtfl_csr;
163
164 -- create portfolio header
165 lp_pfcv_rec.khr_id := p_contract_id;
166 lp_pfcv_rec.line_level_yn := 'N';
167 okl_prtfl_contracts_pub.insert_prtfl_contracts(
168 p_api_version => p_api_version
169 ,p_init_msg_list => OKL_API.G_FALSE
170 ,x_return_status => x_return_status
171 ,x_msg_count => x_msg_count
172 ,x_msg_data => x_msg_data
173 ,p_pfcv_rec => lp_pfcv_rec
174 ,x_pfcv_rec => lx_pfcv_rec);
175
176 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
177 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
178 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
179 RAISE OKL_API.G_EXCEPTION_ERROR;
180 END IF;
181
182
183 -- create portfolio line
184
185 -- get budget amount
186 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMCOPO'
187 ,p_rdf_code => 'AMPRBA'
188 ,p_chr_id => p_contract_id
189 ,p_cle_id => NULL
190 ,p_message_yn => TRUE
191 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
192 ,x_return_status => x_return_status
193 ,x_msg_count => x_msg_count
194 ,x_msg_data => x_msg_data);
195
196 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
197 -- Rule instance is found, but formula not found
198 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
199
200 x_return_status := OKL_API.G_RET_STS_ERROR;
201 -- Portfolio Management DATA is not defined.
202 OKL_API.set_message( p_app_name => 'OKL',
203 p_msg_name => 'OKL_AM_PRTFL_MISSING_DATA',
204 p_token1 => 'DATA',
205 p_token1_value => 'Budget Amount Option');
206 RAISE budget_amount_error;
207
208 END IF;
209 ELSE
210
211 RAISE budget_amount_error;
212 END IF;
213
214 IF l_rulv_rec.rule_information1 = 'NOT_APPLICABLE' THEN
215 l_budget_amount := NULL;
216
217 ELSIF l_rulv_rec.rule_information1 = 'USE_FIXED_AMOUNT' THEN
218 -- SECHAWLA 21-AUG-03 3108113: Changed G_MISS_NUM to G_MISS_CHAR
219 IF l_rulv_rec.rule_information2 IS NULL OR l_rulv_rec.rule_information2 = OKL_API.G_MISS_CHAR THEN
220 x_return_status := OKL_API.G_RET_STS_ERROR;
221 -- -- Portfolio Management DATA is not defined.
222 OKL_API.set_message( p_app_name => 'OKL',
223 p_msg_name => 'OKL_AM_PRTFL_MISSING_DATA',
224 p_token1 => 'DATA',
225 p_token1_value => 'Budget Amount');
226 RAISE OKL_API.G_EXCEPTION_ERROR;
227 ELSE
228 -- SECHAWLA 21-AUG-03 3108113: perform explicit conversion
229 l_budget_amount := to_number(l_rulv_rec.rule_information2);
230 END IF;
231
232 ELSIF l_rulv_rec.rule_information1 = 'USE_FORMULA' THEN
233
234 IF l_rulv_rec.rule_information3 IS NULL OR l_rulv_rec.rule_information3 = OKL_API.G_MISS_CHAR THEN
235 x_return_status := OKL_API.G_RET_STS_ERROR;
236 -- Unable to create portfolio management strategy profile because of the missing budget amount formula.
237 OKL_API.set_message( p_app_name => 'OKL',
238 p_msg_name => 'OKL_AM_MISSING_BA_FORMULA');
239 RAISE budget_amount_error;
240 END IF;
241
242 OPEN l_formulae_csr(l_rulv_rec.rule_information3);
243 FETCH l_formulae_csr INTO l_formulae_id;
244 IF l_formulae_csr%NOTFOUND THEN
245 x_return_status := OKL_API.G_RET_STS_ERROR;
246 -- Budget Amount Formula Name is invalid
247 OKL_API.set_message( p_app_name => 'OKC',
248 p_msg_name => G_INVALID_VALUE,
249 p_token1 => G_COL_NAME_TOKEN,
250 p_token1_value => 'Budget Amount Formula Name');
251 RAISE OKL_API.G_EXCEPTION_ERROR;
252 END IF;
253 CLOSE l_formulae_csr;
254
255 okl_am_util_pvt.get_formula_value(
256 p_formula_name => l_rulv_rec.rule_information3,
257 p_chr_id => p_contract_id,
258 p_cle_id => NULL,
259 x_formula_value => l_budget_amount,
260 x_return_status => x_return_status);
261
262 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
263 --Unable to calculate the budget amount because the budget amount formula returned an error.
264
265 OKL_API.set_message(
266 p_app_name => 'OKL',
267 p_msg_name => 'OKL_AM_INVALID_BUDGET_AMT',
268 p_token1 => 'FORMULA_NAME',
269 p_token1_value => l_rulv_rec.rule_information3
270 );
271 RAISE budget_amount_error;
272 END IF;
273 ELSE
274 x_return_status := OKL_API.G_RET_STS_ERROR;
275 -- Budget Amount Option has an invalid value
276 OKL_API.set_message( p_app_name => 'OKC',
277 p_msg_name => G_INVALID_VALUE,
278 p_token1 => G_COL_NAME_TOKEN,
279 p_token1_value => 'Budget Amount Option');
280 RAISE OKL_API.G_EXCEPTION_ERROR;
281
282 END IF;
283
284 -------------end get budget amount ------------------------------------
285
286 ----------- get strategy ----------------------------------------------
287
288 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMCOPO'
289 ,p_rdf_code => 'AMPRST'
290 ,p_chr_id => p_contract_id
291 ,p_cle_id => NULL
292 ,p_message_yn => TRUE
293 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
294 ,x_return_status => x_return_status
295 ,x_msg_count => x_msg_count
296 ,x_msg_data => x_msg_data);
297
298 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
299 -- Rule instance is found, but formula not found
300 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
301
302 x_return_status := OKL_API.G_RET_STS_ERROR;
303 -- Portfolio Management DATA is not defined.
304 OKL_API.set_message( p_app_name => 'OKL',
305 p_msg_name => 'OKL_AM_PRTFL_MISSING_DATA',
306 p_token1 => 'DATA',
307 p_token1_value => 'Strategy');
308 RAISE strategy_error;
309
310 END IF;
311 l_strategy := l_rulv_rec.rule_information1;
312 ELSE
313
314 RAISE strategy_error;
315 END IF;
316
317
318
319 -------------end get strategy ------------------------------------
320
321
322 ----------- get assignment group ----------------------------------------------
323
324 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMCOPO'
325 ,p_rdf_code => 'AMPRAG'
326 ,p_chr_id => p_contract_id
327 ,p_cle_id => NULL
328 ,p_message_yn => TRUE
329 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
330 ,x_return_status => x_return_status
331 ,x_msg_count => x_msg_count
332 ,x_msg_data => x_msg_data);
333
334 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
335 -- Rule instance is found, but formula not found
336 -- SECHAWLA 21-AUG-03 3108113: Changed G_MISS_NUM to G_MISS_CHAR
337 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
338
339 x_return_status := OKL_API.G_RET_STS_ERROR;
340 -- Portfolio management DATA is not defined.
341 OKL_API.set_message( p_app_name => 'OKL',
342 p_msg_name => 'OKL_AM_PRTFL_MISSING_DATA',
343 p_token1 => 'DATA',
344 p_token1_value => 'Assignment Group');
345 RAISE assignment_group_error;
346
347 END IF;
348 -- SECHAWLA 21-AUG-03 3108113: perform explicit conversion
349 l_assignment_grp_id := to_number(l_rulv_rec.rule_information1);
350 ELSE
351
352 RAISE assignment_group_error;
353 END IF;
354
355
356
357 -------------end get assignment group ------------------------------------
358
359
360 ----------- get execution due date ----------------------------------------------
361
362 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMCOPO'
363 ,p_rdf_code => 'AMPRED'
364 ,p_chr_id => p_contract_id
365 ,p_cle_id => NULL
366 ,p_message_yn => TRUE
367 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
368 ,x_return_status => x_return_status
369 ,x_msg_count => x_msg_count
370 ,x_msg_data => x_msg_data);
371
372 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
373 -- Rule instance is found, but formula not found
374 -- SECHAWLA 21-AUG-03 3108113: Changed G_MISS_NUM to G_MISS_CHAR
375 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
376
377 x_return_status := OKL_API.G_RET_STS_ERROR;
378 -- Portfolio management DATA is not defined.
379 OKL_API.set_message( p_app_name => 'OKL',
380 p_msg_name => 'OKL_AM_PRTFL_MISSING_DATA',
381 p_token1 => 'DATA',
382 p_token1_value => 'Execution Due Date');
383 RAISE execution_date_error;
384
385 END IF;
386
387
388 -- SECHAWLA 21-AUG-03 3108113: perform explicit conversion
389 l_end_date := l_end_date - to_number(l_rulv_rec.rule_information1);
390
391 ELSE
392
393 RAISE execution_date_error;
394 END IF;
395
396
397
398 -------------end get execution due date ------------------------------------
399
400
401 ----------- get approval requirement ----------------------------------------------
402
403 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMCOPO'
404 ,p_rdf_code => 'AMAPRE'
405 ,p_chr_id => p_contract_id
406 ,p_cle_id => NULL
407 ,p_message_yn => TRUE
408 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
409 ,x_return_status => x_return_status
410 ,x_msg_count => x_msg_count
411 ,x_msg_data => x_msg_data);
412
413 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
414 -- Rule instance is found, but has a null value
415 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
416 l_approval_required := 'N';
417 ELSE
418 l_approval_required := l_rulv_rec.rule_information1;
419 END IF;
420 ELSE
421 l_approval_required := 'N';
422 END IF;
423
424
425 -- SECHAWLA Bug # 2726739 : Added the following piece of code
426 -- get the functional currency
427 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
428 -- get the contract currency
429 l_contract_curr_code := okl_am_util_pvt.get_chr_currency( p_chr_id => p_contract_id);
430
431 lp_pflv_rec.currency_code := l_contract_curr_code;
432 lp_pflv_rec.currency_conversion_code := l_func_curr_code;
433
434 IF l_contract_curr_code <> l_func_curr_code THEN
435 -- get the conversion factors from accounting util. No conversion is required here. We use
436 -- convert_to_functional_currency procedure just to get the conversion factors
437
438 okl_accounting_util.convert_to_functional_currency(
439 p_khr_id => p_contract_id,
440 p_to_currency => l_func_curr_code,
441 p_transaction_date => l_sysdate ,
442 p_amount => l_budget_amount,
443 x_return_status => x_return_status,
444 x_contract_currency => lx_contract_currency,
445 x_currency_conversion_type => lx_currency_conversion_type,
446 x_currency_conversion_rate => lx_currency_conversion_rate,
447 x_currency_conversion_date => lx_currency_conversion_date,
448 x_converted_amount => lx_converted_amount );
449
450 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
451 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
452 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
453 RAISE OKL_API.G_EXCEPTION_ERROR;
454 END IF;
455
456 lp_pflv_rec.currency_conversion_type := lx_currency_conversion_type;
457 lp_pflv_rec.currency_conversion_rate := lx_currency_conversion_rate;
458 lp_pflv_rec.currency_conversion_date := lx_currency_conversion_date;
459 END IF;
460 --- SECHAWLA Bug # 2726739 : end new code -----
461
462
463
464 -------------end get approval requirement ------------------------------------
465
466 lp_pflv_rec.budget_amount := l_budget_amount;
467 lp_pflv_rec.date_strategy_execution_due := l_end_date;
468 lp_pflv_rec.trx_status_code := 'ENTERED';
469 lp_pflv_rec.asset_track_strategy_code := l_strategy;
470 lp_pflv_rec.pfc_id := lx_pfcv_rec.id;
471 lp_pflv_rec.tmb_id := l_assignment_grp_id;
472 lp_pflv_rec.fma_id := l_formulae_id;
473
474 okl_prtfl_lines_pub.insert_prtfl_lines(
475 p_api_version => p_api_version
476 ,p_init_msg_list => OKL_API.G_FALSE
477 ,x_return_status => x_return_status
478 ,x_msg_count => x_msg_count
479 ,x_msg_data => x_msg_data
480 ,p_pflv_rec => lp_pflv_rec
481 ,x_pflv_rec => lx_pflv_rec);
482
483 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
484 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
485 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
486 RAISE OKL_API.G_EXCEPTION_ERROR;
487 END IF;
488
489
490 IF upper(l_approval_required) = 'Y' THEN
491 okl_am_wf.raise_business_event(p_contract_id,'oracle.apps.okl.am.approvecontportfolio');
492 END IF;
493
494 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
495
496 EXCEPTION
497
498 WHEN budget_amount_error THEN
499 IF l_formulae_csr%ISOPEN THEN
500 CLOSE l_formulae_csr;
501 END IF;
502 IF l_okcheaders_csr%ISOPEN THEN
503 CLOSE l_okcheaders_csr;
504 END IF;
505 IF l_oklheaders_csr%ISOPEN THEN
506 CLOSE l_oklheaders_csr;
507 END IF;
508 IF l_oklprtfl_csr%ISOPEN THEN
509 CLOSE l_oklprtfl_csr;
510 END IF;
511 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || '_PVT');
512
513 WHEN strategy_error THEN
514 IF l_formulae_csr%ISOPEN THEN
515 CLOSE l_formulae_csr;
516 END IF;
517 IF l_okcheaders_csr%ISOPEN THEN
518 CLOSE l_okcheaders_csr;
519 END IF;
520 IF l_oklheaders_csr%ISOPEN THEN
521 CLOSE l_oklheaders_csr;
522 END IF;
523 IF l_oklprtfl_csr%ISOPEN THEN
524 CLOSE l_oklprtfl_csr;
525 END IF;
526 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || '_PVT');
527
528 WHEN assignment_group_error THEN
529 IF l_formulae_csr%ISOPEN THEN
530 CLOSE l_formulae_csr;
531 END IF;
532 IF l_okcheaders_csr%ISOPEN THEN
533 CLOSE l_okcheaders_csr;
534 END IF;
535 IF l_oklheaders_csr%ISOPEN THEN
536 CLOSE l_oklheaders_csr;
537 END IF;
538 IF l_oklprtfl_csr%ISOPEN THEN
539 CLOSE l_oklprtfl_csr;
540 END IF;
541 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || '_PVT');
542
543 WHEN execution_date_error THEN
544 IF l_formulae_csr%ISOPEN THEN
545 CLOSE l_formulae_csr;
546 END IF;
547 IF l_okcheaders_csr%ISOPEN THEN
548 CLOSE l_okcheaders_csr;
549 END IF;
550 IF l_oklheaders_csr%ISOPEN THEN
551 CLOSE l_oklheaders_csr;
552 END IF;
553 IF l_oklprtfl_csr%ISOPEN THEN
554 CLOSE l_oklprtfl_csr;
555 END IF;
556 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || '_PVT');
557
558
559 WHEN OKL_API.G_EXCEPTION_ERROR THEN
560 IF l_formulae_csr%ISOPEN THEN
561 CLOSE l_formulae_csr;
562 END IF;
563 IF l_okcheaders_csr%ISOPEN THEN
564 CLOSE l_okcheaders_csr;
565 END IF;
566 IF l_oklheaders_csr%ISOPEN THEN
567 CLOSE l_oklheaders_csr;
568 END IF;
569 IF l_oklprtfl_csr%ISOPEN THEN
570 CLOSE l_oklprtfl_csr;
571 END IF;
572 x_return_status := OKL_API.HANDLE_EXCEPTIONS
573 (
574 l_api_name,
575 G_PKG_NAME,
576 'OKL_API.G_RET_STS_ERROR',
577 x_msg_count,
578 x_msg_data,
579 '_PVT'
580 );
581 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
582 IF l_formulae_csr%ISOPEN THEN
583 CLOSE l_formulae_csr;
584 END IF;
585 IF l_okcheaders_csr%ISOPEN THEN
586 CLOSE l_okcheaders_csr;
587 END IF;
588 IF l_oklheaders_csr%ISOPEN THEN
589 CLOSE l_oklheaders_csr;
590 END IF;
591 IF l_oklprtfl_csr%ISOPEN THEN
592 CLOSE l_oklprtfl_csr;
593 END IF;
594 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
595 (
596 l_api_name,
597 G_PKG_NAME,
598 'OKL_API.G_RET_STS_UNEXP_ERROR',
599 x_msg_count,
600 x_msg_data,
601 '_PVT'
602 );
603 WHEN OTHERS THEN
604 IF l_formulae_csr%ISOPEN THEN
605 CLOSE l_formulae_csr;
606 END IF;
607 IF l_okcheaders_csr%ISOPEN THEN
608 CLOSE l_okcheaders_csr;
609 END IF;
610 IF l_oklheaders_csr%ISOPEN THEN
611 CLOSE l_oklheaders_csr;
612 END IF;
613 IF l_oklprtfl_csr%ISOPEN THEN
614 CLOSE l_oklprtfl_csr;
615 END IF;
616 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
617 (
618 l_api_name,
619 G_PKG_NAME,
620 'OTHERS',
621 x_msg_count,
622 x_msg_data,
623 '_PVT'
624 );
625 END create_cntrct_prtfl;
626
627
628
629 -- Start of comments
630 --
631 -- Procedure Name : batch_upd_cntrct_prtfl
632 -- Description : This procedure is used to execute update_cntrct_prtfl procedure
633 -- as a concurrent program. It has all the input parameters for
634 -- update_cntrct_prtfl and 2 standard OUT parameters - ERRBUF and RETCODE
635 -- Business Rules :
636 -- Parameters : p_contract_id - contract id
637
638 --
639 --
640 -- Version : 1.0
641 -- History : SECHAWLA 16-JAN-03 Bug # 2754280
642 -- Changed the app name from OKL to OKC for g_unexpected_error
643 -- End of comments
644
645 PROCEDURE batch_upd_cntrct_prtfl( ERRBUF OUT NOCOPY VARCHAR2,
646 RETCODE OUT NOCOPY VARCHAR2 ,
647 p_api_version IN NUMBER,
648 p_init_msg_list IN VARCHAR2,
649 p_contract_id IN NUMBER
650 ) IS
651
652
653 l_return_status VARCHAR2(1);
654 l_msg_count NUMBER;
655 l_msg_data VARCHAR2(2000);
656 l_transaction_status VARCHAR2(1);
657 lx_error_rec OKL_API.error_rec_type;
658 l_msg_idx INTEGER := FND_MSG_PUB.G_FIRST;
659 l_api_name CONSTANT VARCHAR2(30) := 'batch_upd_cntrct_prtfl';
660 l_total_count NUMBER;
661 l_processed_count NUMBER;
662 l_error_count NUMBER;
663 l_unchanged_txn_count NUMBER;
664
665 BEGIN
666
667 update_cntrct_prtfl(
668 p_api_version => p_api_version,
669 p_init_msg_list => p_init_msg_list ,
670 x_return_status => l_return_status,
671 x_msg_count => l_msg_count,
672 x_msg_data => l_msg_data,
673 p_contract_id => p_contract_id ,
674 x_total_count => l_total_count,
675 x_processed_count => l_processed_count,
676 x_error_count => l_error_count);
677
678
679 -- Add couple of blank lines
680 fnd_file.new_line(fnd_file.log,2);
681 fnd_file.new_line(fnd_file.output,2);
682
683 -- Get the messages in the log
684 LOOP
685
686 fnd_msg_pub.get(
687 p_msg_index => l_msg_idx,
688 p_encoded => FND_API.G_FALSE,
689 p_data => lx_error_rec.msg_data,
690 p_msg_index_out => lx_error_rec.msg_count);
691
692 IF (lx_error_rec.msg_count IS NOT NULL) THEN
693
694 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
695 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
696
697 END IF;
698
699 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
700 OR (lx_error_rec.msg_count IS NULL));
701
702 l_msg_idx := FND_MSG_PUB.G_NEXT;
703 END LOOP;
704
705
706 fnd_file.new_line(fnd_file.log,2);
707 fnd_file.new_line(fnd_file.output,2);
708
709 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
710 fnd_file.put_line(fnd_file.log, 'Portfolio Management Strategy Profile Update Failed, None of the transactions got processed.');
711 fnd_file.put_line(fnd_file.output, 'Portfolio Management Strategy Profile Update Failed, None of the transactions got processed.');
712 END IF;
713
714 IF l_total_count = 0 THEN
715 fnd_file.put_line(fnd_file.log, 'There were no portfolio management strategy profile transactions to process.');
716 fnd_file.put_line(fnd_file.output,'There were no portfolio management strategy profile transactions to process.');
717 ELSE
718
719 fnd_file.put_line(fnd_file.log, 'Total Transactions : '||l_total_count);
720 fnd_file.put_line(fnd_file.log, 'Transactions Processed Successfully : '||l_processed_count);
721 fnd_file.put_line(fnd_file.log, 'Transactions Failed : '||l_error_count);
722
723 l_unchanged_txn_count := (l_total_count - (l_processed_count + l_error_count )) ;
724
725 fnd_file.put_line(fnd_file.output, 'Total Transactions : '||l_total_count);
726 fnd_file.put_line(fnd_file.output, 'Transactions Processed Successfully : '||l_processed_count);
727 fnd_file.put_line(fnd_file.output, 'Transactions Failed : '||l_error_count);
728
729 IF l_unchanged_txn_count > 0 THEN
730 fnd_file.new_line(fnd_file.log,1);
731 fnd_file.new_line(fnd_file.output,1);
732
733 fnd_file.put_line(fnd_file.log, l_unchanged_txn_count||' transactions were not processed as there is no change in the budget amount.');
734 fnd_file.put_line(fnd_file.output, l_unchanged_txn_count||' transactions were not processed as there is no change in the budget amount.');
735 END IF;
736
737 END IF;
738
739
740 EXCEPTION
741 WHEN OTHERS THEN
742 -- unexpected error
743 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
744 OKL_API.set_message(p_app_name => 'OKC',
745 p_msg_name => g_unexpected_error,
746 p_token1 => g_sqlcode_token,
747 p_token1_value => sqlcode,
748 p_token2 => g_sqlerrm_token,
749 p_token2_value => sqlerrm);
750
751 END batch_upd_cntrct_prtfl;
752
753
754
755 -- Start of comments
756 --
757 -- Procedure Name : update_cntrct_prtfl
758 -- Description : procdure used to update portfolio management strategy profile.
759 -- Business Rules :
760 -- parameters : p_contract_id : Contract ID
761 -- Version : 1.0
762 -- End of comments
763
764 PROCEDURE update_cntrct_prtfl(
765 p_api_version IN NUMBER,
766 p_init_msg_list IN VARCHAR2,
767 x_return_status OUT NOCOPY VARCHAR2,
768 x_msg_count OUT NOCOPY NUMBER,
769 x_msg_data OUT NOCOPY VARCHAR2,
770 p_contract_id IN NUMBER ,
771 x_total_count OUT NOCOPY NUMBER,
772 x_processed_count OUT NOCOPY NUMBER,
773 x_error_count OUT NOCOPY NUMBER) IS
774
775 SUBTYPE pfcv_rec_type IS okl_prtfl_contracts_pub.pfcv_rec_type;
776 SUBTYPE pflv_rec_type IS okl_prtfl_lines_pub.pflv_rec_type;
777
778 lp_pflv_rec pflv_rec_type;
779 lx_pflv_rec pflv_rec_type;
780
781 l_budget_amount NUMBER;
782 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
783 l_contract_number VARCHAR2(120);
784 l_total_count NUMBER := 0;
785 l_process_count NUMBER := 0;
786 l_error_count NUMBER := 0;
787
788
789 l_api_version CONSTANT NUMBER := 1;
790 l_api_name CONSTANT VARCHAR2(30) := 'update_cntrct_prtfl';
791 l_rulv_rec okl_rule_pub.rulv_rec_type;
792 l_formulae_id NUMBER;
793 budget_amount_error EXCEPTION;
794 l_name VARCHAR2(150);
795 l_error_txn VARCHAR2(1);
796
797 -- This cursor is used to get the contracts that need to be updated.
798 CURSOR l_cntrctprtfl_csr IS
799 SELECT h.id header_id, l.id line_id , l.fma_id fma_id, h.khr_id khr_id, l.budget_amount
800 FROM okl_prtfl_cntrcts_b h, okl_prtfl_lines_b l
801 WHERE h.id = l.pfc_id
802 AND l.fma_id IS NOT NULL -- profiles that use budget amount formula
803 AND ((p_contract_id IS NOT NULL AND h.khr_id = p_contract_id) OR (p_contract_id IS NULL));
804
805 -- This cursor is used to get the contract number for a given contract ID
806 CURSOR l_okcheaders_csr(p_id NUMBER) IS
807 SELECT contract_number
808 FROM okc_k_headers_b
809 WHERE id = p_id;
810
811 -- This cursor is used to get the formulae id
812 CURSOR l_formulae_csr(p_id NUMBER) IS
813 SELECT name
814 FROM okl_formulae_b
815 WHERE id = p_id;
816
817 BEGIN
818
819 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
820 G_PKG_NAME,
821 p_init_msg_list,
822 l_api_version,
823 p_api_version,
824 '_PVT',
825 x_return_status);
826
827
828
829 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
831 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
832 RAISE OKL_API.G_EXCEPTION_ERROR;
833 END IF;
834
835
836
837 l_total_count := 0;
838 l_process_count := 0;
839
840 --loop thru all the contracts thet need to be updated and update the budget amount
841 FOR l_cntrctprtfl_rec IN l_cntrctprtfl_csr LOOP
842
843
844 l_total_count := l_total_count + 1;
845 l_error_txn := 'N';
846
847
848 OPEN l_okcheaders_csr(l_cntrctprtfl_rec.khr_id);
849 FETCH l_okcheaders_csr INTO l_contract_number;
850 IF l_okcheaders_csr%NOTFOUND THEN
851 -- Contract Id is invalid
852 OKL_API.set_message( p_app_name => 'OKC',
853 p_msg_name => G_INVALID_VALUE,
854 p_token1 => G_COL_NAME_TOKEN,
855 p_token1_value => 'Contract Id');
856 l_error_txn := 'Y';
857
858 ELSE
859
860 OPEN l_formulae_csr(l_cntrctprtfl_rec.fma_id);
861 FETCH l_formulae_csr INTO l_name;
862 IF l_formulae_csr%NOTFOUND THEN
863
864 -- Budget Amount Formula Name is invalid
865 OKL_API.set_message(
866 p_app_name => 'OKL',
867 p_msg_name => 'OKL_AM_INVALID_FORMULA',
868 p_token1 => 'CONTRACT_NUMBER',
869 p_token1_value => l_contract_number);
870
871 l_error_txn := 'Y';
872
873 ELSE
874 okl_am_util_pvt.get_formula_value(
875 p_formula_name => l_name,
876 p_chr_id => l_cntrctprtfl_rec.khr_id,
877 p_cle_id => NULL,
878 x_formula_value => l_budget_amount,
879 x_return_status => l_return_status);
880
881 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
882 --Can not process strategy profile update transaction for contract CONTARCT_NUMBER because the budget amount formula returned error.
883 OKL_API.set_message(
884 p_app_name => 'OKL',
885 p_msg_name => 'OKL_AM_UPD_BA_FORMULA_ERR',
886 p_token1 => 'CONTRACT_NUMBER',
887 p_token1_value => l_contract_number,
888 p_token2 => 'FORMULA_NAME',
889 p_token2_value => l_rulv_rec.rule_information3
890 );
891 l_error_txn := 'Y';
892
893 ELSE
894
895 IF l_cntrctprtfl_rec.budget_amount <> l_budget_amount THEN
896
897 lp_pflv_rec.id := l_cntrctprtfl_rec.line_id;
898 lp_pflv_rec.budget_amount := l_budget_amount;
899
900 okl_prtfl_lines_pub.update_prtfl_lines(
901 p_api_version => p_api_version
902 ,p_init_msg_list => OKL_API.G_FALSE
903 ,x_return_status => l_return_status
904 ,x_msg_count => x_msg_count
905 ,x_msg_data => x_msg_data
906 ,p_pflv_rec => lp_pflv_rec
907 ,x_pflv_rec => lx_pflv_rec);
908
909
910 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
911
912 -- Portfolio Management Strategy Profile Update transaction failed for contract CONTRACT_NUMBER.
913 OKC_API.set_message( p_app_name => 'OKL',
914 p_msg_name => 'OKL_AM_PRTFL_TRANS_FAILED',
915 p_token1 => 'CONTRACT_NUMBER',
916 p_token1_value => l_contract_number);
917 l_error_txn := 'Y';
918 ELSE
919 l_process_count := l_process_count + 1;
920
921 -- Budget amount updated successfully for contract CONTRACT_NUMBER
922 OKC_API.set_message(
923 p_app_name => 'OKL',
924 p_msg_name => 'OKL_AM_PRTFL_UPD_PROCESSED',
925 p_token1 => 'CONTRACT_NUMBER',
926 p_token1_value => l_contract_number);
927
928 -- Old Budget Amount :
929 OKC_API.set_message(
930 p_app_name => 'OKL',
931 p_msg_name => 'OKL_AM_OLD_BUDGET_AMT',
932 p_token1 => 'OLD_AMT',
933 p_token1_value => l_cntrctprtfl_rec.budget_amount);
934
935 -- New Budget Amount :
936 OKC_API.set_message(
937 p_app_name => 'OKL',
938 p_msg_name => 'OKL_AM_NEW_BUDGET_AMT',
939 p_token1 => 'NEW_AMT',
940 p_token1_value => l_budget_amount);
941 END IF;
942 ELSE
943 -- Budget amount not updated for contract CONTRACT_NUMBER as the new budget amount is same as the old budget amount.
944 OKC_API.set_message(
945 p_app_name => 'OKL',
946 p_msg_name => 'OKL_AM_PRTFL_NO_UPD',
947 p_token1 => 'CONTRACT_NUMBER',
948 p_token1_value => l_contract_number);
949 END IF;
950
951 END IF;
952
953 END IF;
954 CLOSE l_formulae_csr;
955
956 END IF;
957 CLOSE l_okcheaders_csr;
958
959 IF l_error_txn = 'Y' THEN
960 l_error_count:= l_error_count + 1;
961 END IF;
962 END LOOP;
963
964
965
966
967 x_total_count := l_total_count;
968 x_processed_count := l_process_count;
969 x_error_count := l_error_count;
970
971 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
972
973 EXCEPTION
974
975 WHEN budget_amount_error THEN
976 IF l_cntrctprtfl_csr%ISOPEN THEN
977 CLOSE l_cntrctprtfl_csr;
978 END IF;
979 IF l_okcheaders_csr%ISOPEN THEN
980 CLOSE l_okcheaders_csr;
981 END IF;
982 IF l_formulae_csr%ISOPEN THEN
983 CLOSE l_formulae_csr;
984 END IF;
985 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || '_PVT');
986
987
988
989 WHEN OKL_API.G_EXCEPTION_ERROR THEN
990 IF l_cntrctprtfl_csr%ISOPEN THEN
991 CLOSE l_cntrctprtfl_csr;
992 END IF;
993 IF l_okcheaders_csr%ISOPEN THEN
994 CLOSE l_okcheaders_csr;
995 END IF;
996 IF l_formulae_csr%ISOPEN THEN
997 CLOSE l_formulae_csr;
998 END IF;
999 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1000 (
1001 l_api_name,
1002 G_PKG_NAME,
1003 'OKL_API.G_RET_STS_ERROR',
1004 x_msg_count,
1005 x_msg_data,
1006 '_PVT'
1007 );
1008 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1009 IF l_cntrctprtfl_csr%ISOPEN THEN
1010 CLOSE l_cntrctprtfl_csr;
1011 END IF;
1012 IF l_okcheaders_csr%ISOPEN THEN
1013 CLOSE l_okcheaders_csr;
1014 END IF;
1015 IF l_formulae_csr%ISOPEN THEN
1016 CLOSE l_formulae_csr;
1017 END IF;
1018 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1019 (
1020 l_api_name,
1021 G_PKG_NAME,
1022 'OKL_API.G_RET_STS_UNEXP_ERROR',
1023 x_msg_count,
1024 x_msg_data,
1025 '_PVT'
1026 );
1027 WHEN OTHERS THEN
1028 IF l_cntrctprtfl_csr%ISOPEN THEN
1029 CLOSE l_cntrctprtfl_csr;
1030 END IF;
1031 IF l_okcheaders_csr%ISOPEN THEN
1032 CLOSE l_okcheaders_csr;
1033 END IF;
1034 IF l_formulae_csr%ISOPEN THEN
1035 CLOSE l_formulae_csr;
1036 END IF;
1037 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1038 (
1039 l_api_name,
1040 G_PKG_NAME,
1041 'OTHERS',
1042 x_msg_count,
1043 x_msg_data,
1044 '_PVT'
1045 );
1046 END update_cntrct_prtfl;
1047
1048
1049
1050
1051 -- Start of comments
1052 --
1053 -- Procedure Name : batch_exe_cntrct_prtfl
1054 -- Description : This procedure is used to execute execute_cntrct_prtfl procedure
1055 -- as a concurrent program. It has all the input parameters for
1056 -- execute_cntrct_prtfl and 2 standard OUT parameters - ERRBUF and RETCODE
1057 -- Business Rules :
1058 --
1059 --
1060 -- Version : 1.0
1061 -- History : SECHAWLA 16-JAN-03 Bug # 2754280
1062 -- Changed the app name from OKL to OKC for g_unexpected_error
1063 -- End of comments
1064 PROCEDURE batch_exe_cntrct_prtfl( ERRBUF OUT NOCOPY VARCHAR2,
1065 RETCODE OUT NOCOPY VARCHAR2 ,
1066 p_api_version IN NUMBER,
1067 p_init_msg_list IN VARCHAR2
1068 ) IS
1069
1070
1071 l_return_status VARCHAR2(1);
1072 l_msg_count NUMBER;
1073 l_msg_data VARCHAR2(2000);
1074
1075 lx_error_rec OKL_API.error_rec_type;
1076 l_msg_idx INTEGER := FND_MSG_PUB.G_FIRST;
1077 l_api_name CONSTANT VARCHAR2(30) := 'batch_exe_cntrct_prtfl';
1078 l_total_count NUMBER;
1079 l_processed_count NUMBER;
1080 l_error_count NUMBER;
1081
1082 BEGIN
1083
1084 execute_cntrct_prtfl(
1085 p_api_version => p_api_version,
1086 p_init_msg_list => p_init_msg_list ,
1087 x_return_status => l_return_status,
1088 x_msg_count => l_msg_count,
1089 x_msg_data => l_msg_data,
1090 x_total_count => l_total_count,
1091 x_processed_count => l_processed_count,
1092 x_error_count => l_error_count);
1093
1094
1095 -- Add couple of blank lines
1096 fnd_file.new_line(fnd_file.log,2);
1097 fnd_file.new_line(fnd_file.output,2);
1098
1099 -- Get the messages in the log
1100 LOOP
1101
1102 fnd_msg_pub.get(
1103 p_msg_index => l_msg_idx,
1104 p_encoded => FND_API.G_FALSE,
1105 p_data => lx_error_rec.msg_data,
1106 p_msg_index_out => lx_error_rec.msg_count);
1107
1108 IF (lx_error_rec.msg_count IS NOT NULL) THEN
1109
1110 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
1111 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
1112
1113 END IF;
1114
1115 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
1116 OR (lx_error_rec.msg_count IS NULL));
1117
1118 l_msg_idx := FND_MSG_PUB.G_NEXT;
1119 END LOOP;
1120
1121
1122 fnd_file.new_line(fnd_file.log,2);
1123 fnd_file.new_line(fnd_file.output,2);
1124
1125 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1126 fnd_file.put_line(fnd_file.log, 'Portfolio Management Strategy Profile Execution Failed, None of the transactions got processed.');
1127 fnd_file.put_line(fnd_file.output, 'Portfolio Management Strategy Profile Execution Failed, None of the transactions got processed.');
1128 END IF;
1129
1130 IF l_total_count = 0 THEN
1131 fnd_file.put_line(fnd_file.log, 'There were no portfolio management strategy profile transactions to process.');
1132 fnd_file.put_line(fnd_file.output,'There were no portfolio management strategy profile transactions to process.');
1133 ELSE
1134
1135 fnd_file.put_line(fnd_file.log, 'Total Transactions : '||l_total_count);
1136 fnd_file.put_line(fnd_file.log, 'Transactions Processed Successfully : '||l_processed_count);
1137 fnd_file.put_line(fnd_file.log, 'Transactions Failed : '||l_error_count);
1138
1139 fnd_file.put_line(fnd_file.output, 'Total Transactions : '||l_total_count);
1140 fnd_file.put_line(fnd_file.output, 'Transactions Processed Successfully : '||l_processed_count);
1141 fnd_file.put_line(fnd_file.output, 'Transactions Failed : '||l_error_count);
1142
1143 END IF;
1144
1145
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 -- unexpected error
1149 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
1150 OKL_API.set_message(p_app_name => 'OKC',
1151 p_msg_name => g_unexpected_error,
1152 p_token1 => g_sqlcode_token,
1153 p_token1_value => sqlcode,
1154 p_token2 => g_sqlerrm_token,
1155 p_token2_value => sqlerrm);
1156
1157 END batch_exe_cntrct_prtfl;
1158
1159
1160
1161 -- Start of comments
1162 --
1163 -- Procedure Name : execute_cntrct_prtfl
1164 -- Description : procdure used to execute portfolio management strategy profile on the execution due date
1165 -- Business Rules :
1166 -- parameters :
1167 -- Version : 1.0
1168 -- End of comments
1169
1170 PROCEDURE execute_cntrct_prtfl(
1171 p_api_version IN NUMBER,
1172 p_init_msg_list IN VARCHAR2,
1173 x_return_status OUT NOCOPY VARCHAR2,
1174 x_msg_count OUT NOCOPY NUMBER,
1175 x_msg_data OUT NOCOPY VARCHAR2,
1176 x_total_count OUT NOCOPY NUMBER,
1177 x_processed_count OUT NOCOPY NUMBER,
1178 x_error_count OUT NOCOPY NUMBER) IS
1179
1180 SUBTYPE pflv_rec_type IS okl_prtfl_lines_pub.pflv_rec_type;
1181
1182 lp_pflv_rec pflv_rec_type;
1183 lx_pflv_rec pflv_rec_type;
1184
1185 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1186 l_contract_number VARCHAR2(120);
1187 l_total_count NUMBER := 0;
1188 l_process_count NUMBER := 0;
1189 l_error_count NUMBER := 0;
1190
1191
1192 l_api_version CONSTANT NUMBER := 1;
1193 l_api_name CONSTANT VARCHAR2(30) := 'execute_cntrct_prtfl';
1194 l_sysdate DATE;
1195 l_team_name VARCHAR2(30);
1196
1197 -- This cursor is used to get the portfolios that need to be executed.
1198 CURSOR l_cntrctprtfl_csr(p_date DATE) IS
1199 SELECT h.id header_id, l.id line_id , h.khr_id khr_id, khr.contract_number contract_number, l.tmb_id
1200 FROM okl_prtfl_cntrcts_b h, okl_prtfl_lines_b l, okc_k_headers_b khr
1201 WHERE h.id = l.pfc_id
1202 AND h.khr_id = khr.id
1203 AND l.date_strategy_executed IS NULL
1204 AND khr.sts_code = 'BOOKED'
1205 AND l.date_strategy_execution_due <= p_date;
1206
1207 -- This cursor is used to get the assignment group name for a given id
1208 CURSOR l_jtfteams_csr(p_team_id NUMBER) IS
1209 SELECT team_name
1210 FROM jtf_rs_teams_vl
1211 WHERE team_id = p_team_id;
1212
1213 BEGIN
1214
1215 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1216 G_PKG_NAME,
1217 p_init_msg_list,
1218 l_api_version,
1219 p_api_version,
1220 '_PVT',
1221 x_return_status);
1222
1223
1224
1225 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1226 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1227 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1228 RAISE OKL_API.G_EXCEPTION_ERROR;
1229 END IF;
1230
1231 SELECT SYSDATE INTO l_sysdate FROM dual;
1232
1233 l_total_count := 0;
1234 l_process_count := 0;
1235
1236 --loop thru all the portfolios that need to be executed and call the notify assignment group workflow
1237 FOR l_cntrctprtfl_rec IN l_cntrctprtfl_csr(l_sysdate) LOOP
1238
1239 l_total_count := l_total_count + 1;
1240
1241
1242 -- call the notify assignment group workflow
1243 okl_am_wf.raise_business_event(l_cntrctprtfl_rec.khr_id,'oracle.apps.okl.am.notifyportexe');
1244
1245 OPEN l_jtfteams_csr(l_cntrctprtfl_rec.tmb_id);
1246 FETCH l_jtfteams_csr INTO l_team_name;
1247 IF l_jtfteams_csr%NOTFOUND THEN
1248 -- Assignment group ID is invalid
1249 OKL_API.set_message(
1250 p_app_name => 'OKC',
1251 p_msg_name => G_INVALID_VALUE,
1252 p_token1 => G_COL_NAME_TOKEN,
1253 p_token1_value => 'TMB_ID');
1254 ELSE
1255 -- Notifications have been sent to ASSIGNMENT_GROUP for executing the contract portfolio for contract CONTRACT_NUMBER
1256 OKC_API.set_message( p_app_name => 'OKL',
1257 p_msg_name => 'OKL_AM_PRTFL_NOTF_SENT',
1258 p_token1 => 'ASSIGNMENT_GROUP',
1259 p_token1_value => l_team_name,
1260 p_token2 => 'CONTRACT_NUMBER',
1261 p_token2_value => l_cntrctprtfl_rec.contract_number);
1262
1263 -- update date_strategy_executed field
1264 lp_pflv_rec.id := l_cntrctprtfl_rec.line_id;
1265 lp_pflv_rec.date_strategy_executed := l_sysdate;
1266
1267 okl_prtfl_lines_pub.update_prtfl_lines(
1268 p_api_version => p_api_version
1269 ,p_init_msg_list => OKL_API.G_FALSE
1270 ,x_return_status => l_return_status
1271 ,x_msg_count => x_msg_count
1272 ,x_msg_data => x_msg_data
1273 ,p_pflv_rec => lp_pflv_rec
1274 ,x_pflv_rec => lx_pflv_rec);
1275
1276
1277 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1278
1279 -- Strategy execution date could not be set for Portfolio Management Strategy Profile for contract CONTRACT_NUMBER.
1280 OKC_API.set_message( p_app_name => 'OKL',
1281 p_msg_name => 'OKL_AM_STRTG_DT_UPD_FAILED',
1282 p_token1 => 'CONTRACT_NUMBER',
1283 p_token1_value => l_cntrctprtfl_rec.contract_number);
1284
1285
1286 ELSE
1287 l_process_count := l_process_count + 1;
1288 END IF;
1289 END IF;
1290 CLOSE l_jtfteams_csr;
1291
1292
1293 END LOOP;
1294
1295
1296
1297 x_total_count := l_total_count;
1298 x_processed_count := l_process_count;
1299 x_error_count := l_total_count - l_process_count;
1300
1301 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1302
1303 EXCEPTION
1304
1305 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1306 IF l_cntrctprtfl_csr%ISOPEN THEN
1307 CLOSE l_cntrctprtfl_csr;
1308 END IF;
1309 IF l_jtfteams_csr%ISOPEN THEN
1310 CLOSE l_jtfteams_csr;
1311 END IF;
1312
1313 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1314 (
1315 l_api_name,
1316 G_PKG_NAME,
1317 'OKL_API.G_RET_STS_ERROR',
1318 x_msg_count,
1319 x_msg_data,
1320 '_PVT'
1321 );
1322 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1323 IF l_cntrctprtfl_csr%ISOPEN THEN
1324 CLOSE l_cntrctprtfl_csr;
1325 END IF;
1326 IF l_jtfteams_csr%ISOPEN THEN
1327 CLOSE l_jtfteams_csr;
1328 END IF;
1329
1330 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1331 (
1332 l_api_name,
1333 G_PKG_NAME,
1334 'OKL_API.G_RET_STS_UNEXP_ERROR',
1335 x_msg_count,
1336 x_msg_data,
1337 '_PVT'
1338 );
1339 WHEN OTHERS THEN
1340 IF l_cntrctprtfl_csr%ISOPEN THEN
1341 CLOSE l_cntrctprtfl_csr;
1342 END IF;
1343 IF l_jtfteams_csr%ISOPEN THEN
1344 CLOSE l_jtfteams_csr;
1345 END IF;
1346
1347 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1348 (
1349 l_api_name,
1350 G_PKG_NAME,
1351 'OTHERS',
1352 x_msg_count,
1353 x_msg_data,
1354 '_PVT'
1355 );
1356 END execute_cntrct_prtfl;
1357
1358
1359 END OKL_AM_CONTRACT_PRTFL_PVT;