[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_CALC_QUOTE_STREAM_PVT
Source
1 PACKAGE BODY OKL_AM_CALC_QUOTE_STREAM_PVT AS
2 /* $Header: OKLRCQSB.pls 120.15.12010000.2 2008/10/16 11:29:49 rpillay ship $ */
3
4 -- GLOBAL VARIABLES
5 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_MODULE_NAME CONSTANT VARCHAR2(500) := 'okl.am.plsql.okl_am_calc_quote_stream_pvt.';
9
10 --G_OUTSTANDING_BAL_DONE VARCHAR2(3); -- RMUNJULU 4691487 -- rmunjulu 4996136 remv from here and declare in spec
11
12 -- Start of comments
13 --
14 -- Procedure Name : add_element
15 -- Description : Save a quote line
16 -- Business Rules :
17 -- Parameters : contract line, stream type,
18 -- table of quote line records,
19 -- Version : 1.0
20 -- History : SECHAWLA 02-DEC-02 - Bug 2680542
21 -- Added NOCOPY for IN OUT parameters
22 -- : rmunjulu 3797384 Added code for passing quote_eff_from date
23 -- and quote_id to formula engine
24 -- rmunjulu EDAT 29-Dec-04 did to_char to convert to right format
25 -- End of comments
26
27 PROCEDURE add_element (
28 p_qtev_rec IN qtev_rec_type,
29 p_cle_id IN NUMBER,
30 p_sty_id IN NUMBER,
31 p_formula_name IN VARCHAR2,
32 p_prorate_ratio IN NUMBER,
33 p_asset_cle_id IN NUMBER,
34 px_seq_num IN OUT NOCOPY NUMBER,
35 px_total IN OUT NOCOPY NUMBER,
36 px_tqlv_tbl IN OUT NOCOPY tqlv_tbl_type,
37 x_return_status OUT NOCOPY VARCHAR2) IS
38
39 l_tqlv_rec tqlv_rec_type;
40 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
41 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
42 l_formula_name VARCHAR2(150);
43 l_rule_value NUMBER;
44 l_params okl_execute_formula_pub.ctxt_val_tbl_type;
45
46 -- for debug logging
47 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'add_element';
48 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
49 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
50 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
51
52 BEGIN
53
54 IF (is_debug_procedure_on) THEN
55 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
56 END IF;
57
58 IF p_formula_name IS NOT NULL
59 AND p_formula_name <> G_MISS_CHAR THEN
60 l_formula_name := p_formula_name;
61 ELSE
62 l_formula_name := G_DEFAULT_FORMULA;
63 END IF;
64
65 l_params(1).name := G_FORMULA_PARAM_1;
66 l_params(1).value := p_sty_id;
67
68 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- Start ++++++++++++++++
69
70 -- set the operands for formula engine with quote_effective_from date
71 l_params(2).name := 'quote_effective_from_date';
72 l_params(2).value := to_char(p_qtev_rec.date_effective_from,'MM/DD/YYYY'); -- rmunjulu EDAT 29-Dec-04 did to_char to convert to right format
73
74 -- set the operands for formula engine with quote_id
75 l_params(3).name := 'quote_id';
76 l_params(3).value := to_char(p_qtev_rec.id);
77
78 --+++++++++ rmunjulu 3797384 Future Dated Term Qte -- End ++++++++++++++++
79
80
81 okl_am_util_pvt.get_formula_value (
82 p_formula_name => l_formula_name,
83 p_chr_id => p_qtev_rec.khr_id,
84 p_cle_id => p_cle_id,
85 p_additional_parameters => l_params,
86 x_formula_value => l_rule_value,
87 x_return_status => l_return_status);
88
89 IF (is_debug_statement_on) THEN
90 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
91 'after call to okl_am_util_pvt.get_formula_value :'||l_return_status);
92 END IF;
93
94 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
95 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
96 l_overall_status := l_return_status;
97 END IF;
98 END IF;
99
100 --okl_execute_formula_pub.g_additional_parameters(1).name := G_FORMULA_PARAM_1;
101 --okl_execute_formula_pub.g_additional_parameters(1).value := p_sty_id;
102 --l_tqlv_rec.amount := okl_seeded_functions_pvt.line_unbilled_streams
103 -- (p_qtev_rec.khr_id, p_cle_id);
104
105 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
106 AND l_rule_value IS NOT NULL THEN
107
108 l_tqlv_rec.kle_id := NVL (p_asset_cle_id, p_cle_id);
109 l_tqlv_rec.sty_id := p_sty_id;
110 l_tqlv_rec.amount := l_rule_value * p_prorate_ratio;
111
112 px_seq_num := px_seq_num + 1;
113 px_tqlv_tbl(px_seq_num) := l_tqlv_rec;
114 px_total := px_total + l_tqlv_rec.amount;
115
116 END IF;
117
118 x_return_status := l_overall_status;
119
120 IF (is_debug_procedure_on) THEN
121 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
122 END IF;
123
124 EXCEPTION
125
126 WHEN OTHERS THEN
127
128 IF (is_debug_exception_on) THEN
129 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
130 || sqlcode || ' , SQLERRM : ' || sqlerrm);
131 END IF;
132 -- store SQL error message on message stack for caller
133 OKL_API.SET_MESSAGE (
134 p_app_name => G_APP_NAME
135 ,p_msg_name => G_UNEXPECTED_ERROR
136 ,p_token1 => G_SQLCODE_TOKEN
137 ,p_token1_value => sqlcode
138 ,p_token2 => G_SQLERRM_TOKEN
139 ,p_token2_value => sqlerrm);
140
141 -- notify caller of an UNEXPECTED error
142 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
143
144 END add_element;
145
146
147 -- Start of comments
148 --
149 -- Procedure Name : process_specific_line_style
150 -- Description : Calculate unbilled streams for specific line styles
151 -- Business Rules :
152 -- Parameters : operand, quote header record, contract line,
153 -- table of quote line records,
154 -- top line st
155 --History : SECHAWLA 27-JAN-03 Bug # 2759726
156 -- Changed the last group by column in the cursor l_linked_unbilled_streams_csr to capital_amount
157 -- : SECHAWLA 04-MAR-03
158 -- Commented out the condition sty.capitalize_yn = 'N' from the cursor selects
159 -- : SECHAWLA 21-APR-03 2925120
160 -- Modified procedure to get only future Service and Fee amounts. Replaced the use of hard coded
161 -- value '1' for the prorate ratio of service and fee lines with global G_PRORATE_RATIO
162 -- : rmunjulu EDAT Modified to get stream > quote eff date
163 -- : rmunjulu 09-Dec-2004 bug 4056186 Modified cursor to get correct streams and modified NVL func
164 -- : akrangan 12-Mar-2007 bug 5495474, check global flags to prevent duplicate for 'AMYFEE'
165 -- End of comments
166
167 PROCEDURE process_specific_line_style (
168 p_operand IN VARCHAR2,
169 p_qtev_rec IN qtev_rec_type,
170 p_cle_id IN NUMBER,
171 p_top_style IN VARCHAR2,
172 p_link_style IN VARCHAR2,
173 p_formula_name IN VARCHAR2,
174 px_tqlv_tbl IN OUT NOCOPY tqlv_tbl_type,
175 x_operand_total OUT NOCOPY NUMBER,
176 x_return_status OUT NOCOPY VARCHAR2) IS
177
178 -- Select all unbilled streams for all contract lines of
179 -- particular style and their linked sublines
180
181 -- SECHAWLA 21-APR-03 2925120 : Added quote_eff_date parameter to the following cursor to get only future Service amd Maintenance abd Fee amounts
182 CURSOR l_style_unbilled_streams_csr (
183 cp_chr_id NUMBER,
184 cp_top_style VARCHAR2,
185 cp_link_style VARCHAR2,
186 cp_quote_eff_date DATE) IS
187 SELECT stm.kle_id contract_line_id,
188 stm.sty_id stream_type_id
189 FROM okl_streams stm,
190 okl_strm_type_b sty,
191 okc_k_lines_b kle,
192 okc_statuses_b kls,
193 okc_line_styles_b lse,
194 okl_strm_elements ste -- SECHAWLA 21-APR-03 2925120 :Added this table
195 WHERE stm.khr_id = cp_chr_id
196 AND stm.active_yn = 'Y'
197 AND stm.say_code = 'CURR'
198 AND ste.stm_id = stm.id -- SECHAWLA 21-APR-03 2925120 : Added this condition
199 AND ste.stream_element_date > cp_quote_eff_date -- SECHAWLA 21-APR-03 2925120 : Added this condition -- rmunjulu EDAT
200 AND sty.id = stm.sty_id
201 AND sty.billable_yn = 'Y'
202 --AND sty.capitalize_yn = 'N' -- SECHAWLA 04-MAR-03
203 AND kle.id = stm.kle_id
204 AND kls.code = kle.sts_code
205 AND kls.ste_code = 'ACTIVE'
206 AND lse.id = kle.lse_id
207 AND lse.lty_code IN (cp_top_style, cp_link_style)
208 GROUP BY stm.kle_id,
209 stm.sty_id;
210
211 -- Select unbilled streams linked to an asset
212 --SECHAWLA 27-JAN-03 Bug # 2759726 : Changed the last column in the group by clause to capital_amount
213
214 -- SECHAWLA 21-APR-03 2925120 : Added quote_eff_date parameter to the following cursor to get only future Service amd Maintenance abd Fee amounts
215 CURSOR l_linked_unbilled_streams_csr (
216 cp_chr_id NUMBER,
217 cp_cle_id NUMBER,
218 cp_link_style VARCHAR2,
219 cp_quote_eff_date DATE) IS
220 SELECT stm.kle_id contract_line_id,
221 stm.sty_id stream_type_id,
222 kle.capital_amount line_payment
223 FROM okc_k_items ite,
224 okc_k_lines_b cle,
225 okl_k_lines_v kle,
226 okc_line_styles_b lse,
227 okl_streams stm,
228 okl_strm_type_b sty,
229 okl_strm_elements ste -- SECHAWLA 21-APR-03 2925120 :Added this table
230 WHERE ite.object1_id1 = cp_cle_id
231 AND cle.id = ite.cle_id
232 AND lse.id = cle.lse_id
233 AND lse.lty_code = cp_link_style
234 AND kle.id = cle.id
235 AND stm.kle_id = cle.id -- rmunjulu bug 4056186 Check with Cle.id NOT cle.cle_id
236 AND stm.khr_id = cp_chr_id
237 AND stm.active_yn = 'Y'
238 AND stm.say_code = 'CURR'
239 AND ste.stm_id = stm.id -- SECHAWLA 21-APR-03 2925120 : Added this condition
240 AND ste.stream_element_date > cp_quote_eff_date -- SECHAWLA 21-APR-03 2925120 : Added this condition -- rmunjulu EDAT
241 AND sty.id = stm.sty_id
242 AND sty.billable_yn = 'Y'
243 -- AND sty.capitalize_yn = 'N' -- SECHAWLA 04-MAR-03
244 GROUP BY stm.kle_id,
245 stm.sty_id,
246 kle.capital_amount;
247
248 -- Select total payments
249 CURSOR l_total_payments_csr (
250 cp_cle_id NUMBER,
251 cp_link_style VARCHAR2) IS
252 SELECT sum (kle.capital_amount) total_payment
253 FROM okc_k_lines_b cle,
254 okl_k_lines_v kle,
255 okc_line_styles_b lse
256 WHERE cle.cle_id = cp_cle_id
257 AND lse.id = cle.lse_id
258 AND lse.lty_code = cp_link_style
259 AND kle.id = cle.id;
260
261 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
262 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
263 l_total NUMBER := 0;
264 l_seq NUMBER := NVL (px_tqlv_tbl.LAST, 0);
265 l_total_payment NUMBER;
266 l_prorate_ratio NUMBER;
267
268 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'process_specific_line_style';
269 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
270 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
271 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
272
273
274 BEGIN
275
276 IF (is_debug_procedure_on) THEN
277 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
278 END IF;
279
280
281 IF NVL (p_qtev_rec.partial_yn, 'N') <> 'Y'
282 AND p_cle_id IS NULL
283 --akrangan Bug 5495474 start
284 AND ((p_top_style <> G_SERVICE_STYLE AND NVL(G_CONTRACTUAL_FEE_DONE,'N') <> 'Y')
285 OR (p_top_style = G_SERVICE_STYLE AND NVL(G_SERVICE_BAL_DONE,'N') <> 'Y')
286 )
287 THEN
288 --akrangan Bug 5495474 end
289
290 -- **********************************************
291 -- Get unbilled streams for all contract lines of
292 -- particular style and their linked sublines
293 -- **********************************************
294 --SECHAWLA 21-APR-03 2925120 : Added date_effective_from parameter to the following cursor call
295 FOR l_cont_str_rec IN l_style_unbilled_streams_csr
296 (p_qtev_rec.khr_id, p_top_style, p_link_style, p_qtev_rec.date_effective_from ) LOOP
297
298 add_element (
299 p_qtev_rec => p_qtev_rec,
300 p_cle_id => l_cont_str_rec.contract_line_id,
301 p_sty_id => l_cont_str_rec.stream_type_id,
302 p_formula_name => p_formula_name,
303 p_prorate_ratio => G_PRORATE_RATIO, -- SECHAWLA 21-APR-03 2925120 : Use a global instead of hardcoded value 1
304 p_asset_cle_id => NULL,
305 px_seq_num => l_seq,
306 px_total => l_total,
307 px_tqlv_tbl => px_tqlv_tbl,
308 x_return_status => l_return_status);
309
310 IF (is_debug_statement_on) THEN
311 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
312 'after call to add_element :'||l_return_status);
313 END IF;
314
315
316
317 END LOOP;
318 --akrangan Bug 5495474 start
319 --G_SERVICE_BAL_DONE := 'Y'; -- rmunjulu 5066471
320 IF (p_top_style = G_SERVICE_STYLE) THEN
321 G_SERVICE_BAL_DONE := 'Y';
322 ELSE
323 G_CONTRACTUAL_FEE_DONE := 'Y';
324 END IF;
325 --akrangan Bug 5495474 end
326 ELSIF p_cle_id IS NOT NULL THEN
327
328 -- ***************************************
329 -- Get unbilled streams linked to an asset
330 -- ***************************************
331
332 --SECHAWLA 21-APR-03 2925120 : Added date_effective_from parameter to the following cursor call
333 FOR l_link_str_rec IN l_linked_unbilled_streams_csr
334 (p_qtev_rec.khr_id, p_cle_id, p_link_style, p_qtev_rec.date_effective_from) LOOP
335
336 l_total_payment := 0;
337 OPEN l_total_payments_csr
338 (l_link_str_rec.contract_line_id, p_link_style);
339 FETCH l_total_payments_csr INTO l_total_payment;
340 CLOSE l_total_payments_csr;
341
342 IF NVL (l_total_payment, 0) <> 0 THEN -- rmunjulu bug 4056186 check NVL with 0 NOT 1
343 l_prorate_ratio := NVL (l_link_str_rec.line_payment, 1) /
344 NVL (l_total_payment, 1);
345 ELSE
346 l_prorate_ratio := G_PRORATE_RATIO; -- SECHAWLA 21-APR-03 2925120 : Use a global instead of hardcoded value 1;
347 END IF;
348
349 add_element (
350 p_qtev_rec => p_qtev_rec,
351 p_cle_id => l_link_str_rec.contract_line_id,
352 p_sty_id => l_link_str_rec.stream_type_id,
353 p_formula_name => p_formula_name,
354 p_prorate_ratio => l_prorate_ratio,
355 p_asset_cle_id => p_cle_id,
356 px_seq_num => l_seq,
357 px_total => l_total,
358 px_tqlv_tbl => px_tqlv_tbl,
359 x_return_status => l_return_status);
360
361 IF (is_debug_statement_on) THEN
362 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
363 'after call to add_element :'||l_return_status);
364 END IF;
365
366 END LOOP;
367
368 END IF;
369
370 x_operand_total := l_total;
371 x_return_status := l_overall_status;
372
373 IF (is_debug_procedure_on) THEN
374 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
375 END IF;
376
377 EXCEPTION
378
379 WHEN OTHERS THEN
380
381 IF (is_debug_exception_on) THEN
382 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
383 || sqlcode || ' , SQLERRM : ' || sqlerrm);
384 END IF;
385
386
387 -- store SQL error message on message stack for caller
388 OKL_API.SET_MESSAGE (
389 p_app_name => G_APP_NAME
390 ,p_msg_name => G_UNEXPECTED_ERROR
391 ,p_token1 => G_SQLCODE_TOKEN
392 ,p_token1_value => sqlcode
393 ,p_token2 => G_SQLERRM_TOKEN
394 ,p_token2_value => sqlerrm);
395
396 -- notify caller of an UNEXPECTED error
397 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
398
399 END process_specific_line_style;
400
401
402 -- Start of comments
403 --
404 -- Procedure Name : process_unbilled_receivables
405 -- Description : Calculate unbilled receivables
406 -- Business Rules :
407 -- Parameters : operand, quote header record, contract line,
408 -- table of quote line records,
409 -- list of styles to be excluded
410 -- Version : 1.0
411 -- : SECHAWLA 04-MAR-03
412 -- Commented out the condition sty.capitalize_yn = 'N' from the cursor selects
413 -- : SECHAWLA 21-APR-03 2925120
414 -- Modified procedure to calculate only future Unbilled Receivables
415 -- : rmunjulu EDAT Modified to get stream > quote eff date
416 -- : rmunjulu 5005225 do not calc unbilled for evergreen contracts
417 -- : akrangan 12-Mar-2007 bug 5495474, check global flags to prevent duplicate
418 -- End of comments
419
420 PROCEDURE process_unbilled_receivables (
421 p_operand IN VARCHAR2,
422 p_qtev_rec IN qtev_rec_type,
423 p_cle_id IN NUMBER,
424 p_exclude_all_styles IN VARCHAR2,
425 p_exclude_link_styles IN VARCHAR2,
426 px_tqlv_tbl IN OUT NOCOPY tqlv_tbl_type,
427 x_operand_total OUT NOCOPY NUMBER,
428 x_return_status OUT NOCOPY VARCHAR2) IS
429
430 -- Note on cursor below:
431 -- When this package is created, this cursor should always
432 -- return zero rows, but it may change in future releases.
433 -- Currently all billable streams are assigned to lines
434 -- and therefore there are no streams attached to a contract
435
436 -- Select all unbilled streams for a contract
437 -- SECHAWLA 21-APR-03 2925120 : Added cp_quote_eff_date parameter to this cursor to get only future Unbilled Streams
438 CURSOR l_contract_unbill_rcvbl_csr (
439 cp_chr_id NUMBER,
440 cp_quote_eff_date DATE) IS
441 SELECT stm.kle_id contract_line_id,
442 stm.sty_id stream_type_id,
443 SUM (ste.amount) amount_due
444 FROM okl_streams stm,
445 okl_strm_elements ste,
446 okl_strm_type_b sty
447 WHERE stm.khr_id = cp_chr_id
448 AND stm.kle_id IS NULL
449 AND stm.active_yn = 'Y'
450 AND stm.say_code = 'CURR'
451 AND ste.stm_id = stm.id
452 -- AND ste.date_billed IS NULL -- rmunjulu EDAT FIXES Removed date billed as future bills are adjusted.
453 AND ste.stream_element_date > cp_quote_eff_date -- SECHAWLA 21-APR-03 2925120 :Added this condition -- rmunjulu EDAT
454 AND NVL (ste.amount, 0) <> 0
455 AND sty.id = stm.sty_id
456 AND sty.billable_yn = 'Y'
457 --AND sty.capitalize_yn = 'N' -- SECHAWLA 04-MAR-03
458 GROUP BY stm.kle_id,
459 stm.sty_id;
460
461 -- Select all unbilled streams for all contract lines
462 -- of all styles except designated excluded styles
463
464 -- SECHAWLA 21-APR-03 2925120 : Added cp_quote_eff_date parameter to this cursor to get only future Unbilled Streams
465 CURSOR l_all_lines_unbill_rcvbl_csr (
466 cp_chr_id NUMBER,
467 cp_exclude_styles VARCHAR2,
468 cp_quote_eff_date DATE) IS
469 SELECT stm.kle_id contract_line_id,
470 stm.sty_id stream_type_id,
471 SUM (ste.amount) amount_due
472 FROM okl_streams stm,
473 okl_strm_elements ste,
474 okl_strm_type_b sty,
475 okc_k_lines_b kle,
476 okc_statuses_b kls,
477 okc_line_styles_b lse
478 WHERE stm.khr_id = cp_chr_id
479 AND stm.active_yn = 'Y'
480 AND stm.say_code = 'CURR'
481 AND ste.stm_id = stm.id
482 -- AND ste.date_billed IS NULL -- rmunjulu EDAT FIXES Removed date billed as future bills are adjusted.
483 AND ste.stream_element_date > cp_quote_eff_date -- SECHAWLA 21-APR-03 2925120 :Added this condition -- rmunjulu EDAT
484 AND NVL (ste.amount, 0) <> 0
485 AND sty.id = stm.sty_id
486 AND sty.billable_yn = 'Y'
487 --AND sty.capitalize_yn = 'N' -- SECHAWLA 04-MAR-03
488 AND kle.id = stm.kle_id
489 AND kls.code = kle.sts_code
490 AND kls.ste_code = 'ACTIVE'
491 AND lse.id = kle.lse_id
492 AND cp_exclude_styles NOT LIKE
493 '%' || G_SEP || lse.lty_code || G_SEP || '%'
494 GROUP BY stm.kle_id,
495 stm.sty_id;
496
497 -- Select all unbilled streams for a line
498 -- SECHAWLA 21-APR-03 2925120 : Added cp_quote_eff_date parameter to this cursor to get only future Unbilled Streams
499 CURSOR l_line_unbill_rcvbl_csr (
500 cp_chr_id NUMBER,
501 cp_cle_id NUMBER,
502 cp_quote_eff_date DATE) IS
503 SELECT stm.kle_id contract_line_id,
504 stm.sty_id stream_type_id,
505 SUM (ste.amount) amount_due
506 FROM okl_streams stm,
507 okl_strm_elements ste,
508 okl_strm_type_b sty
509 WHERE stm.khr_id = cp_chr_id
510 AND stm.kle_id = cp_cle_id
511 AND stm.active_yn = 'Y'
512 AND stm.say_code = 'CURR'
513 AND ste.stm_id = stm.id
514 -- AND ste.date_billed IS NULL -- rmunjulu EDAT FIXES Removed date billed as future bills are adjusted.
515 AND ste.stream_element_date > cp_quote_eff_date -- SECHAWLA 21-APR-03 2925120 :Added this condition -- rmunjulu EDAT
516 AND NVL (ste.amount, 0) <> 0
517 AND sty.id = stm.sty_id
518 AND sty.billable_yn = 'Y'
519 -- AND sty.capitalize_yn = 'N' -- SECHAWLA 04-MAR-03
520 GROUP BY stm.kle_id,
521 stm.sty_id;
522
523 -- Note on cursor below:
524 -- When this package is created, this cursor should always
525 -- return zero rows, but it may change in future releases.
526 -- Currently all linked lines are handled in specific routines
527 -- and therefore are included into list of excluded styles
528
529 -- Select unbilled streams linked to an asset
530 -- except designated excluded styles
531 -- SECHAWLA 21-APR-03 2925120 : Added cp_quote_eff_date parameter to this cursor to get only future Unbilled Streams
532 CURSOR l_linked_line_unbill_rcvbl_csr (
533 cp_chr_id NUMBER,
534 cp_cle_id NUMBER,
535 cp_exclude_styles VARCHAR2,
536 cp_quote_eff_date DATE) IS
537 SELECT stm.kle_id contract_line_id,
538 stm.sty_id stream_type_id,
539 SUM (ste.amount) amount_due
540 FROM okc_k_items ite,
541 okc_k_lines_b kle,
542 okc_line_styles_b lse,
543 okl_streams stm,
544 okl_strm_type_b sty,
545 okl_strm_elements ste
546 WHERE ite.object1_id1 = to_char(cp_cle_id) -- rmunjulu bug 5129653 need to_char as object1_id1 can be alphanumeric
547 AND kle.id = ite.cle_id
548 AND lse.id = kle.lse_id
549 AND cp_exclude_styles NOT LIKE
550 '%' || G_SEP || lse.lty_code || G_SEP || '%'
551 AND stm.kle_id = kle.id
552 AND stm.khr_id = cp_chr_id
553 AND stm.active_yn = 'Y'
554 AND stm.say_code = 'CURR'
555 AND sty.id = stm.sty_id
556 AND sty.billable_yn = 'Y'
557 --AND sty.capitalize_yn = 'N' -- SECHAWLA 04-MAR-03
558 AND ste.stm_id = stm.id
559 -- AND ste.date_billed IS NULL -- rmunjulu EDAT FIXES Removed date billed as future bills are adjusted.
560 AND ste.stream_element_date > cp_quote_eff_date -- SECHAWLA 21-APR-03 2925120 Added this condition -- rmunjulu EDAT
561 AND NVL (ste.amount, 0) <> 0
562 GROUP BY stm.kle_id,
563 stm.sty_id;
564
565 l_tqlv_rec tqlv_rec_type;
566 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
567 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
568 l_total NUMBER := 0;
569 l_seq NUMBER := NVL (px_tqlv_tbl.LAST, 0);
570
571 -- rmunjulu bug 5005225
572 CURSOR get_k_sts_csr (p_khr_id IN NUMBER) IS
573 SELECT chr.sts_code
574 FROM OKC_K_HEADERS_B chr
575 WHERE chr.id = p_khr_id;
576
577 -- rmunjulu bug 5005225
578 l_sts_code OKC_K_HEADERS_B.sts_code%TYPE;
579
580 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'process_unbilled_receivables';
581 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
582 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
583 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
584
585 BEGIN
586
587 IF (is_debug_procedure_on) THEN
588 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
589 END IF;
590
591 -- rmunjulu bug 5005225
592 OPEN get_k_sts_csr (p_qtev_rec.khr_id);
593 FETCH get_k_sts_csr INTO l_sts_code;
594 CLOSE get_k_sts_csr;
595
596 IF NVL (p_qtev_rec.partial_yn, 'N') <> 'Y'
597 AND p_cle_id IS NULL
598 AND NVL(G_UNBILLED_RECEIVABLES_DONE,'N') <> 'Y' --akrangan bug 5495474
599 AND l_sts_code <> 'EVERGREEN' THEN -- rmunjulu bug 5005225
600
601 -- ***********************************
602 -- Get unbilled streams for a contract
603 -- ***********************************
604 -- SECHAWLA 21-APR-03 2925120 : Added date_effective_from parameter to the cursor call
605 FOR l_cont_rcv_rec IN l_contract_unbill_rcvbl_csr
606 (p_qtev_rec.khr_id, p_qtev_rec.date_effective_from ) LOOP
607
608 l_tqlv_rec.amount := l_cont_rcv_rec.amount_due;
609 l_tqlv_rec.kle_id := l_cont_rcv_rec.contract_line_id;
610 l_tqlv_rec.sty_id := l_cont_rcv_rec.stream_type_id;
611
612 l_seq := l_seq + 1;
613 px_tqlv_tbl(l_seq) := l_tqlv_rec;
614 l_total := l_total + l_cont_rcv_rec.amount_due;
615
616 END LOOP;
617
618 -- **********************************************
619 -- Get unbilled streams for all contract lines of
620 -- all styles except designated excluded styles
621 -- **********************************************
622
623 -- SECHAWLA 21-APR-03 2925120 : Added date_effective_from parameter to the cursor call
624 FOR l_all_rcv_rec IN l_all_lines_unbill_rcvbl_csr
625 (p_qtev_rec.khr_id, p_exclude_all_styles, p_qtev_rec.date_effective_from) LOOP
626
627
628 l_tqlv_rec.amount := l_all_rcv_rec.amount_due;
629 l_tqlv_rec.kle_id := l_all_rcv_rec.contract_line_id;
630 l_tqlv_rec.sty_id := l_all_rcv_rec.stream_type_id;
631
632 l_seq := l_seq + 1;
633 px_tqlv_tbl(l_seq) := l_tqlv_rec;
634 l_total := l_total + l_all_rcv_rec.amount_due;
635
636 END LOOP;
637 G_UNBILLED_RECEIVABLES_DONE := 'Y'; -- akrangan bug 5495474
638 ELSIF p_cle_id IS NOT NULL
639 AND l_sts_code <> 'EVERGREEN' THEN -- rmunjulu bug 5005225
640
641 -- *******************************
642 -- Get unbilled streams for a line
643 -- *******************************
644
645 -- SECHAWLA 21-APR-03 2925120 : Added date_effective_from parameter to the cursor call
646 FOR l_line_rcv_rec IN l_line_unbill_rcvbl_csr
647 (p_qtev_rec.khr_id, p_cle_id, p_qtev_rec.date_effective_from) LOOP
648
649 l_tqlv_rec.amount := l_line_rcv_rec.amount_due;
650 l_tqlv_rec.kle_id := l_line_rcv_rec.contract_line_id;
651 l_tqlv_rec.sty_id := l_line_rcv_rec.stream_type_id;
652
653 l_seq := l_seq + 1;
654 px_tqlv_tbl(l_seq) := l_tqlv_rec;
655 l_total := l_total + l_line_rcv_rec.amount_due;
656
657 END LOOP;
658
659 -- ***************************************
660 -- Get unbilled streams linked to an asset
661 -- except designated excluded styles
662 -- ***************************************
663
664 -- SECHAWLA 21-APR-03 2925120 : Added date_effective_from parameter to the cursor call
665 FOR l_link_rcv_rec IN l_linked_line_unbill_rcvbl_csr
666 (p_qtev_rec.khr_id, p_cle_id, p_exclude_link_styles, p_qtev_rec.date_effective_from) LOOP
667
668 l_tqlv_rec.amount := l_link_rcv_rec.amount_due;
669 l_tqlv_rec.kle_id := l_link_rcv_rec.contract_line_id;
670 l_tqlv_rec.sty_id := l_link_rcv_rec.stream_type_id;
671
672 l_seq := l_seq + 1;
673 px_tqlv_tbl(l_seq) := l_tqlv_rec;
674 l_total := l_total + l_link_rcv_rec.amount_due;
675
676 END LOOP;
677
678 END IF;
679
680 x_operand_total := l_total;
681 x_return_status := l_overall_status;
682
683 IF (is_debug_procedure_on) THEN
684 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
685 END IF;
686
687 EXCEPTION
688
689 WHEN OTHERS THEN
690
691 IF (is_debug_exception_on) THEN
692 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
693 || sqlcode || ' , SQLERRM : ' || sqlerrm);
694 END IF;
695
696
697 -- store SQL error message on message stack for caller
698 OKL_API.SET_MESSAGE (
699 p_app_name => G_APP_NAME
700 ,p_msg_name => G_UNEXPECTED_ERROR
701 ,p_token1 => G_SQLCODE_TOKEN
702 ,p_token1_value => sqlcode
703 ,p_token2 => G_SQLERRM_TOKEN
704 ,p_token2_value => sqlerrm);
705
706 -- notify caller of an UNEXPECTED error
707 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
708
709 END process_unbilled_receivables;
710
711
712 -- Start of comments
713 --
714 -- Procedure Name : process_outstanding_balances
715 -- Description : Calculate outstanding balances
716 -- Business Rules :
717 -- Parameters : operand, quote header record, contract line,
718 -- table of quote line records
719 -- Version : 1.0
720 -- End of comments
721
722 PROCEDURE process_outstanding_balances (
723 p_operand IN VARCHAR2,
724 p_qtev_rec IN qtev_rec_type,
725 p_cle_id IN NUMBER,
726 px_tqlv_tbl IN OUT NOCOPY tqlv_tbl_type,
727 x_operand_total OUT NOCOPY NUMBER,
728 x_return_status OUT NOCOPY VARCHAR2) IS
729
730 --ansethur 01-MAR-2007 Added for R12 B Billing Architecture Start Changes
731 -- Changed the cursors to select data from the view(okl_bpd_ar_inv_lines_v)
732 -- provided for the Enhanced Billing architecture
733 -- Select all CONTRACT invoices which have not been fully paid
734 /*
735 CURSOR l_contract_outst_bal_csr (cp_chr_id NUMBER) IS
736 SELECT l.contract_line_id contract_line_id,
737 l.stream_type_id stream_type_id,
738 SUM (l.amount_due_remaining) amount_due
739 FROM okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
740 -- FROM okl_bpd_leasing_payment_trx_v l --ansethur 01-MAR-2007 commented for R12 B Billing Architecture
741 WHERE l.CONTRACT_ID = cp_chr_id
742 AND NVL (l.amount_due_remaining, 0) <> 0
743 GROUP BY l.contract_line_id,
744 l.stream_type_id; */
745 --modified as part of bug 7303686 to include OKC_K_HEADERS_B
746 CURSOR l_contract_outst_bal_csr (
747 cp_chr_id NUMBER) IS
748 SELECT l.contract_line_id contract_line_id,
749 l.stream_type_id stream_type_id,
750 SUM (l.amount_due_remaining) amount_due
751 FROM okl_bpd_ar_inv_lines_v l
752 , OKC_K_HEADERS_B CHR
753 WHERE l.contract_id = cp_chr_id
754 AND NVL (l.amount_due_remaining, 0) <> 0
755 AND CHR.ID = l.contract_id
756 AND CHR.CUST_ACCT_ID = l.IXX_ID
757 GROUP BY l.contract_line_id,
758 l.stream_type_id;
759 -- Select all contract LINE invoices which have not been fully paid
760 /*
761 CURSOR l_line_outst_bal_csr (cp_chr_id NUMBER,cp_cle_id NUMBER) IS
762 SELECT l.contract_line_id contract_line_id,
763 l.stream_type_id stream_type_id,
764 SUM (l.amount_due_remaining) amount_due
765 FROM okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
766 -- FROM okl_bpd_leasing_payment_trx_v l --ansethur 01-MAR-2007 commented for R12 B Billing Architecture
767 WHERE l.CONTRACT_ID = cp_chr_id
768 AND l.contract_line_id = cp_cle_id
769 AND NVL (l.amount_due_remaining, 0) <> 0
770 GROUP BY l.contract_line_id,l.stream_type_id;
771 */
772 --modified as part of bug 7303686 to include OKC_K_HEADERS_B
773 CURSOR l_line_outst_bal_csr (
774 cp_chr_id NUMBER,
775 cp_cle_id NUMBER) IS
776 SELECT l.contract_line_id contract_line_id,
777 l.stream_type_id stream_type_id,
778 SUM (l.amount_due_remaining) amount_due
779 FROM okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
780 , OKC_K_HEADERS_B CHR
781 WHERE l.contract_id = cp_chr_id
782 AND l.contract_line_id = cp_cle_id
783 AND NVL (l.amount_due_remaining, 0) <> 0
784 AND CHR.ID = l.contract_id
785 AND CHR.CUST_ACCT_ID = l.IXX_ID
786 GROUP BY l.contract_line_id,
787 l.stream_type_id;
788
789 -- Select all contract SUBLINE invoices which have not been fully paid
790 /*
791 CURSOR l_subline_outst_bal_csr (cp_chr_id NUMBER,cp_cle_id NUMBER) IS
792 SELECT l.contract_line_id contract_line_id,
793 l.stream_type_id stream_type_id,
794 SUM (l.amount_due_remaining) amount_due
795 FROM okc_k_items i,
796 okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
797 -- okl_bpd_leasing_payment_trx_v l --ansethur 01-MAR-2007 commented for R12 B Billing Architecture
798 WHERE i.object1_id1 = cp_cle_id
799 AND l.CONTRACT_ID = cp_chr_id
800 AND l.contract_line_id = i.cle_id
801 AND NVL (l.amount_due_remaining, 0) <> 0
802 GROUP BY l.contract_line_id,l.stream_type_id;
803 */
804 --modified as part of bug 7303686 to include OKC_K_HEADERS_B
805 CURSOR l_subline_outst_bal_csr (
806 cp_chr_id NUMBER,
807 cp_cle_id NUMBER) IS
808 SELECT l.contract_line_id contract_line_id,
809 l.stream_type_id stream_type_id,
810 SUM (l.amount_due_remaining) amount_due
811 FROM okc_k_items i,
812 okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
813 , OKC_K_HEADERS_B CHR
814 WHERE i.object1_id1 = cp_cle_id
815 AND l.contract_id = cp_chr_id
816 AND l.contract_line_id = i.cle_id
817 AND NVL (l.amount_due_remaining, 0) <> 0
818 AND CHR.ID = l.contract_id
819 AND CHR.CUST_ACCT_ID = l.IXX_ID
820 GROUP BY l.contract_line_id,
821 l.stream_type_id;
822 --ansethur 01-MAR-2007 Added for R12 B Billing Architecture End Changes
823 l_tqlv_rec tqlv_rec_type;
824 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
825 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
826 l_total NUMBER := 0;
827 l_seq NUMBER := NVL (px_tqlv_tbl.LAST, 0);
828
829 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'process_outstanding_balances';
830 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
831 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
832 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
833
834 BEGIN
835
836 IF (is_debug_procedure_on) THEN
837 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
838 END IF;
839
840 IF NVL (p_qtev_rec.partial_yn, 'N') <> 'Y'
841 AND p_cle_id IS NULL
842 AND NVL(G_OUTSTANDING_BAL_DONE,'N')='N' THEN -- RMUNJULU 4691487 ADD CHECK TO SEE IF OUTSTANDING BAL CALCULATED ALREADY
843
844 -- ************************************
845 -- Get all CONTRACT outstanding amounts
846 -- ************************************
847
848 FOR l_cont_bal_rec IN l_contract_outst_bal_csr
849 (p_qtev_rec.khr_id) LOOP
850
851 l_tqlv_rec.amount := l_cont_bal_rec.amount_due;
852 l_tqlv_rec.kle_id := l_cont_bal_rec.contract_line_id;
853 l_tqlv_rec.sty_id := l_cont_bal_rec.stream_type_id;
854
855 l_seq := l_seq + 1;
856 px_tqlv_tbl(l_seq) := l_tqlv_rec;
857 l_total := l_total + l_cont_bal_rec.amount_due;
858
859 END LOOP;
860
861 G_OUTSTANDING_BAL_DONE := 'Y'; -- RMUNJULU 4691487 SET GLOBAL VALUE TO Y
862
863 ELSIF p_cle_id IS NOT NULL THEN
864
865 -- ********************************
866 -- Get all LINE outstanding amounts
867 -- ********************************
868
869 FOR l_line_bal_rec IN l_line_outst_bal_csr
870 (p_qtev_rec.khr_id, p_cle_id) LOOP
871
872 l_tqlv_rec.amount := l_line_bal_rec.amount_due;
873 l_tqlv_rec.kle_id := l_line_bal_rec.contract_line_id;
874 l_tqlv_rec.sty_id := l_line_bal_rec.stream_type_id;
875
876 l_seq := l_seq + 1;
877 px_tqlv_tbl(l_seq) := l_tqlv_rec;
878 l_total := l_total + l_line_bal_rec.amount_due;
879
880 END LOOP;
881
882 -- ***********************************
883 -- Get all SUBLINE outstanding amounts
884 -- ***********************************
885
886 FOR l_sub_bal_rec IN l_subline_outst_bal_csr
887 (p_qtev_rec.khr_id, p_cle_id) LOOP
888
889 l_tqlv_rec.amount := l_sub_bal_rec.amount_due;
890 l_tqlv_rec.kle_id := l_sub_bal_rec.contract_line_id;
891 l_tqlv_rec.sty_id := l_sub_bal_rec.stream_type_id;
892
893 l_seq := l_seq + 1;
894 px_tqlv_tbl(l_seq) := l_tqlv_rec;
895 l_total := l_total + l_sub_bal_rec.amount_due;
896
897 END LOOP;
898
899 END IF;
900
901 x_operand_total := l_total;
902 x_return_status := l_overall_status;
903
904 IF (is_debug_procedure_on) THEN
905 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
906 END IF;
907
908 EXCEPTION
909
910 WHEN OTHERS THEN
911
912 IF (is_debug_exception_on) THEN
913 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
914 || sqlcode || ' , SQLERRM : ' || sqlerrm);
915 END IF;
916
917 -- store SQL error message on message stack for caller
918 OKL_API.SET_MESSAGE (
919 p_app_name => G_APP_NAME
920 ,p_msg_name => G_UNEXPECTED_ERROR
921 ,p_token1 => G_SQLCODE_TOKEN
922 ,p_token1_value => sqlcode
923 ,p_token2 => G_SQLERRM_TOKEN
924 ,p_token2_value => sqlerrm);
925
926 -- notify caller of an UNEXPECTED error
927 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
928
929 END process_outstanding_balances;
930
931
932 -- Start of comments
933 --
934 -- Procedure Name : calc_stream_type_operand
935 -- Description : Calculate an operand based on stream type
936 -- Business Rules :
937 -- Parameters : operand, quote header record, contract line,
938 -- table of quote line records
939 -- Version : 1.0
940 -- End of comments
941
942 PROCEDURE calc_stream_type_operand (
943 p_operand IN VARCHAR2,
944 p_qtev_rec IN qtev_rec_type,
945 p_cle_id IN NUMBER, -- if null, calculate for contract
946 p_formula_name IN VARCHAR2, -- DEFAULT NULL in specs
947 px_tqlv_tbl IN OUT NOCOPY tqlv_tbl_type,
948 x_operand_total OUT NOCOPY NUMBER,
949 x_return_status OUT NOCOPY VARCHAR2) IS
950
951 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
952 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
953 l_operand_total NUMBER;
954
955 -- Some styles are processed by a designated routine.
956 -- All other styles are included into Unbilled Receivables
957 l_processed_all_styles VARCHAR2(1000);
958 l_processed_link_styles VARCHAR2(1000);
959
960 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'calc_stream_type_operand';
961 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
962 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
963 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
964
965 BEGIN
966
967 IF (is_debug_procedure_on) THEN
968 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
969 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_operand : '||p_operand);
970 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_formula_name : '||p_formula_name);
971 END IF;
972
973 IF p_operand = 'AMYSAM' THEN
974
975 process_specific_line_style (
976 p_operand => p_operand,
977 p_qtev_rec => p_qtev_rec,
978 p_cle_id => p_cle_id,
979 p_top_style => G_SERVICE_STYLE,
980 p_link_style => G_SERVICE_LINK_STYLE,
981 p_formula_name => p_formula_name,
982 px_tqlv_tbl => px_tqlv_tbl,
983 x_operand_total => l_operand_total,
984 x_return_status => l_return_status);
985
986 ELSIF p_operand = 'AMYFEE' THEN
987
988 process_specific_line_style (
989 p_operand => p_operand,
990 p_qtev_rec => p_qtev_rec,
991 p_cle_id => p_cle_id,
992 p_top_style => G_FEE_STYLE,
993 p_link_style => G_FEE_LINK_STYLE,
994 p_formula_name => p_formula_name,
995 px_tqlv_tbl => px_tqlv_tbl,
996 x_operand_total => l_operand_total,
997 x_return_status => l_return_status);
998
999 ELSIF p_operand = 'AMYOUB' THEN
1000
1001 process_outstanding_balances (
1002 p_operand => p_operand,
1003 p_qtev_rec => p_qtev_rec,
1004 p_cle_id => p_cle_id,
1005 px_tqlv_tbl => px_tqlv_tbl,
1006 x_operand_total => l_operand_total,
1007 x_return_status => l_return_status);
1008
1009 ELSIF p_operand = 'AMCTUR' THEN
1010
1011 l_processed_all_styles := G_SEP ||
1012 G_SERVICE_STYLE || G_SEP ||
1013 G_SERVICE_LINK_STYLE || G_SEP ||
1014 G_FEE_STYLE || G_SEP ||
1015 G_FEE_LINK_STYLE || G_SEP;
1016
1017 l_processed_link_styles := G_SEP ||
1018 G_SERVICE_LINK_STYLE || G_SEP ||
1019 G_FEE_LINK_STYLE || G_SEP;
1020
1021 process_unbilled_receivables (
1022 p_operand => p_operand,
1023 p_qtev_rec => p_qtev_rec,
1024 p_cle_id => p_cle_id,
1025 p_exclude_all_styles => l_processed_all_styles,
1026 p_exclude_link_styles => l_processed_link_styles,
1027 px_tqlv_tbl => px_tqlv_tbl,
1028 x_operand_total => l_operand_total,
1029 x_return_status => l_return_status);
1030
1031 END IF;
1032
1033 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1034 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1035 l_overall_status := l_return_status;
1036 END IF;
1037 END IF;
1038
1039 x_operand_total := l_operand_total;
1040 x_return_status := l_overall_status;
1041
1042 IF (is_debug_procedure_on) THEN
1043 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1044 END IF;
1045
1046 EXCEPTION
1047
1048 WHEN OTHERS THEN
1049
1050 IF (is_debug_exception_on) THEN
1051 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1052 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1053 END IF;
1054 -- store SQL error message on message stack for caller
1055 OKL_API.SET_MESSAGE (
1056 p_app_name => G_APP_NAME
1057 ,p_msg_name => G_UNEXPECTED_ERROR
1058 ,p_token1 => G_SQLCODE_TOKEN
1059 ,p_token1_value => sqlcode
1060 ,p_token2 => G_SQLERRM_TOKEN
1061 ,p_token2_value => sqlerrm);
1062
1063 -- notify caller of an UNEXPECTED error
1064 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1065
1066 END calc_stream_type_operand;
1067
1068
1069 END OKL_AM_CALC_QUOTE_STREAM_PVT;