[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.21 2011/09/21 22:40:51 gkadarka 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
747 --sechawla 24-nov-09 9001258 : commented out the cursor
748 /*
749 CURSOR l_contract_outst_bal_csr (
750 cp_chr_id NUMBER) IS
751 SELECT l.contract_line_id contract_line_id,
752 l.stream_type_id stream_type_id,
753 SUM (l.amount_due_remaining) amount_due
754 FROM okl_bpd_ar_inv_lines_v l
755 , OKC_K_HEADERS_B CHR
756 WHERE l.contract_id = cp_chr_id
757 AND NVL (l.amount_due_remaining, 0) <> 0
758 AND CHR.ID = l.contract_id
759 AND CHR.CUST_ACCT_ID = l.IXX_ID
760 GROUP BY l.contract_line_id,
761 l.stream_type_id;
762 */
763 --sechawla 24-nov-09 9001258 : added
764 /*
765 CURSOR l_contract_outst_bal_csr (
766 cp_chr_id NUMBER) IS
767 SELECT l.contract_line_id contract_line_id,
768 l.stream_type_id stream_type_id,
769 SUM (l.amount_due_remaining) amount_due
770 FROM okl_bpd_ar_inv_lines_v l
771 , OKC_K_HEADERS_B CHR
772 ,okc_k_lines_b cle,
773 okc_statuses_b sts
774 WHERE l.contract_id = cp_chr_id
775 and l.contract_line_id = cle.id
776 and sts.code = cle.sts_code
777 and sts.ste_code not in ('EXPIRED','TERMINATED','CANCELLED')
778 AND NVL (l.amount_due_remaining, 0) <> 0
779 AND CHR.ID = l.contract_id
780 -- Bug 9363287
781 AND l.class = 'INV'
782 -- End Bug 9363287
783 AND CHR.CUST_ACCT_ID = l.IXX_ID
784 GROUP BY l.contract_line_id,
785 l.stream_type_id; */
786 -- gkadarka 21-sep-11 for bug 12932148
787 CURSOR l_contract_outst_bal_csr (
788 cp_chr_id NUMBER) IS
789 SELECT l.contract_line_id contract_line_id,
790 l.stream_type_id stream_type_id,
791 SUM (l.amount_due_remaining) amount_due
792 FROM okl_bpd_ar_inv_lines_v l
793 , OKC_K_HEADERS_all_B CHR
794 ,okc_k_lines_b cle
795 WHERE l.contract_id = cp_chr_id
796 and l.contract_line_id = cle.id(+)
797 and ( cle.sts_code not in ('EXPIRED','TERMINATED','CANCELLED')
798 or cle.sts_code is null)
799 AND NVL (l.amount_due_remaining, 0) <> 0
800 AND CHR.ID = l.contract_id
801 AND l.class = 'INV'
802 AND CHR.CUST_ACCT_ID = l.IXX_ID
803 GROUP BY l.contract_line_id, l.stream_type_id;
804
805
806 -- Select all contract LINE invoices which have not been fully paid
807 /*
808 CURSOR l_line_outst_bal_csr (cp_chr_id NUMBER,cp_cle_id NUMBER) IS
809 SELECT l.contract_line_id contract_line_id,
810 l.stream_type_id stream_type_id,
811 SUM (l.amount_due_remaining) amount_due
812 FROM okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
813 -- FROM okl_bpd_leasing_payment_trx_v l --ansethur 01-MAR-2007 commented for R12 B Billing Architecture
814 WHERE l.CONTRACT_ID = cp_chr_id
815 AND l.contract_line_id = cp_cle_id
816 AND NVL (l.amount_due_remaining, 0) <> 0
817 GROUP BY l.contract_line_id,l.stream_type_id;
818 */
819 --modified as part of bug 7303686 to include OKC_K_HEADERS_B
820 CURSOR l_line_outst_bal_csr (
821 cp_chr_id NUMBER,
822 cp_cle_id NUMBER) IS
823 SELECT l.contract_line_id contract_line_id,
824 l.stream_type_id stream_type_id,
825 SUM (l.amount_due_remaining) amount_due
826 FROM okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
827 , OKC_K_HEADERS_B CHR
828 WHERE l.contract_id = cp_chr_id
829 AND l.contract_line_id = cp_cle_id
830 AND NVL (l.amount_due_remaining, 0) <> 0
831 AND CHR.ID = l.contract_id
832 --Bug# 10319122
833 AND l.class = 'INV'
834 AND CHR.CUST_ACCT_ID = l.IXX_ID
835 GROUP BY l.contract_line_id,
836 l.stream_type_id;
837
838 -- Select all contract SUBLINE invoices which have not been fully paid
839 /*
840 CURSOR l_subline_outst_bal_csr (cp_chr_id NUMBER,cp_cle_id NUMBER) IS
841 SELECT l.contract_line_id contract_line_id,
842 l.stream_type_id stream_type_id,
843 SUM (l.amount_due_remaining) amount_due
844 FROM okc_k_items i,
845 okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
846 -- okl_bpd_leasing_payment_trx_v l --ansethur 01-MAR-2007 commented for R12 B Billing Architecture
847 WHERE i.object1_id1 = cp_cle_id
848 AND l.CONTRACT_ID = cp_chr_id
849 AND l.contract_line_id = i.cle_id
850 AND NVL (l.amount_due_remaining, 0) <> 0
851 GROUP BY l.contract_line_id,l.stream_type_id;
852 */
853 --modified as part of bug 7303686 to include OKC_K_HEADERS_B
854 CURSOR l_subline_outst_bal_csr (
855 cp_chr_id NUMBER,
856 cp_cle_id NUMBER) IS
857 SELECT l.contract_line_id contract_line_id,
858 l.stream_type_id stream_type_id,
859 SUM (l.amount_due_remaining) amount_due
860 FROM okc_k_items i,
861 okl_bpd_ar_inv_lines_v l --ansethur 01-MAR-2007 Added for R12 B Billing Architecture
862 , OKC_K_HEADERS_B CHR
863 WHERE i.object1_id1 = cp_cle_id
864 AND l.contract_id = cp_chr_id
865 AND l.contract_line_id = i.cle_id
866 AND NVL (l.amount_due_remaining, 0) <> 0
867 AND CHR.ID = l.contract_id
868 --Bug# 10319122
869 AND l.class = 'INV'
870 AND CHR.CUST_ACCT_ID = l.IXX_ID
871 GROUP BY l.contract_line_id,
872 l.stream_type_id;
873 --ansethur 01-MAR-2007 Added for R12 B Billing Architecture End Changes
874 l_tqlv_rec tqlv_rec_type;
875 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
876 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
877 l_total NUMBER := 0;
878 l_seq NUMBER := NVL (px_tqlv_tbl.LAST, 0);
879
880 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'process_outstanding_balances';
881 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
882 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
883 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
884
885 BEGIN
886
887 IF (is_debug_procedure_on) THEN
888 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
889 END IF;
890
891 IF NVL (p_qtev_rec.partial_yn, 'N') <> 'Y'
892 AND p_cle_id IS NULL
893 AND NVL(G_OUTSTANDING_BAL_DONE,'N')='N' THEN -- RMUNJULU 4691487 ADD CHECK TO SEE IF OUTSTANDING BAL CALCULATED ALREADY
894
895 -- ************************************
896 -- Get all CONTRACT outstanding amounts
897 -- ************************************
898
899 FOR l_cont_bal_rec IN l_contract_outst_bal_csr
900 (p_qtev_rec.khr_id) LOOP
901
902 l_tqlv_rec.amount := l_cont_bal_rec.amount_due;
903 l_tqlv_rec.kle_id := l_cont_bal_rec.contract_line_id;
904 l_tqlv_rec.sty_id := l_cont_bal_rec.stream_type_id;
905
906 l_seq := l_seq + 1;
907 px_tqlv_tbl(l_seq) := l_tqlv_rec;
908 l_total := l_total + l_cont_bal_rec.amount_due;
909
910 END LOOP;
911
912 G_OUTSTANDING_BAL_DONE := 'Y'; -- RMUNJULU 4691487 SET GLOBAL VALUE TO Y
913
914 ELSIF p_cle_id IS NOT NULL THEN
915
916 -- ********************************
917 -- Get all LINE outstanding amounts
918 -- ********************************
919
920 FOR l_line_bal_rec IN l_line_outst_bal_csr
921 (p_qtev_rec.khr_id, p_cle_id) LOOP
922
923 l_tqlv_rec.amount := l_line_bal_rec.amount_due;
924 l_tqlv_rec.kle_id := l_line_bal_rec.contract_line_id;
925 l_tqlv_rec.sty_id := l_line_bal_rec.stream_type_id;
926
927 l_seq := l_seq + 1;
928 px_tqlv_tbl(l_seq) := l_tqlv_rec;
929 l_total := l_total + l_line_bal_rec.amount_due;
930
931 END LOOP;
932
933 -- ***********************************
934 -- Get all SUBLINE outstanding amounts
935 -- ***********************************
936
937 FOR l_sub_bal_rec IN l_subline_outst_bal_csr
938 (p_qtev_rec.khr_id, p_cle_id) LOOP
939
940 l_tqlv_rec.amount := l_sub_bal_rec.amount_due;
941 l_tqlv_rec.kle_id := l_sub_bal_rec.contract_line_id;
942 l_tqlv_rec.sty_id := l_sub_bal_rec.stream_type_id;
943
944 l_seq := l_seq + 1;
945 px_tqlv_tbl(l_seq) := l_tqlv_rec;
946 l_total := l_total + l_sub_bal_rec.amount_due;
947
948 END LOOP;
949
950 END IF;
951
952 x_operand_total := l_total;
953 x_return_status := l_overall_status;
954
955 IF (is_debug_procedure_on) THEN
956 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
957 END IF;
958
959 EXCEPTION
960
961 WHEN OTHERS THEN
962
963 IF (is_debug_exception_on) THEN
964 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
965 || sqlcode || ' , SQLERRM : ' || sqlerrm);
966 END IF;
967
968 -- store SQL error message on message stack for caller
969 OKL_API.SET_MESSAGE (
970 p_app_name => G_APP_NAME
971 ,p_msg_name => G_UNEXPECTED_ERROR
972 ,p_token1 => G_SQLCODE_TOKEN
973 ,p_token1_value => sqlcode
974 ,p_token2 => G_SQLERRM_TOKEN
975 ,p_token2_value => sqlerrm);
976
977 -- notify caller of an UNEXPECTED error
978 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
979
980 END process_outstanding_balances;
981
982
983 -- Start of comments
984 --
985 -- Procedure Name : calc_stream_type_operand
986 -- Description : Calculate an operand based on stream type
987 -- Business Rules :
988 -- Parameters : operand, quote header record, contract line,
989 -- table of quote line records
990 -- Version : 1.0
991 -- End of comments
992
993 PROCEDURE calc_stream_type_operand (
994 p_operand IN VARCHAR2,
995 p_qtev_rec IN qtev_rec_type,
996 p_cle_id IN NUMBER, -- if null, calculate for contract
997 p_formula_name IN VARCHAR2, -- DEFAULT NULL in specs
998 px_tqlv_tbl IN OUT NOCOPY tqlv_tbl_type,
999 x_operand_total OUT NOCOPY NUMBER,
1000 x_return_status OUT NOCOPY VARCHAR2) IS
1001
1002 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1003 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1004 l_operand_total NUMBER;
1005
1006 -- Some styles are processed by a designated routine.
1007 -- All other styles are included into Unbilled Receivables
1008 l_processed_all_styles VARCHAR2(1000);
1009 l_processed_link_styles VARCHAR2(1000);
1010
1011 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'calc_stream_type_operand';
1012 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1013 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1014 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1015
1016 BEGIN
1017
1018 IF (is_debug_procedure_on) THEN
1019 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1020 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_operand : '||p_operand);
1021 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'p_formula_name : '||p_formula_name);
1022 END IF;
1023
1024 IF p_operand = 'AMYSAM' THEN
1025
1026 process_specific_line_style (
1027 p_operand => p_operand,
1028 p_qtev_rec => p_qtev_rec,
1029 p_cle_id => p_cle_id,
1030 p_top_style => G_SERVICE_STYLE,
1031 p_link_style => G_SERVICE_LINK_STYLE,
1032 p_formula_name => p_formula_name,
1033 px_tqlv_tbl => px_tqlv_tbl,
1034 x_operand_total => l_operand_total,
1035 x_return_status => l_return_status);
1036
1037 ELSIF p_operand = 'AMYFEE' THEN
1038
1039 process_specific_line_style (
1040 p_operand => p_operand,
1041 p_qtev_rec => p_qtev_rec,
1042 p_cle_id => p_cle_id,
1043 p_top_style => G_FEE_STYLE,
1044 p_link_style => G_FEE_LINK_STYLE,
1045 p_formula_name => p_formula_name,
1046 px_tqlv_tbl => px_tqlv_tbl,
1047 x_operand_total => l_operand_total,
1048 x_return_status => l_return_status);
1049
1050 ELSIF p_operand = 'AMYOUB' THEN
1051
1052 process_outstanding_balances (
1053 p_operand => p_operand,
1054 p_qtev_rec => p_qtev_rec,
1055 p_cle_id => p_cle_id,
1056 px_tqlv_tbl => px_tqlv_tbl,
1057 x_operand_total => l_operand_total,
1058 x_return_status => l_return_status);
1059
1060 ELSIF p_operand = 'AMCTUR' THEN
1061
1062 l_processed_all_styles := G_SEP ||
1063 G_SERVICE_STYLE || G_SEP ||
1064 G_SERVICE_LINK_STYLE || G_SEP ||
1065 G_FEE_STYLE || G_SEP ||
1066 G_FEE_LINK_STYLE || G_SEP;
1067
1068 l_processed_link_styles := G_SEP ||
1069 G_SERVICE_LINK_STYLE || G_SEP ||
1070 G_FEE_LINK_STYLE || G_SEP;
1071
1072 process_unbilled_receivables (
1073 p_operand => p_operand,
1074 p_qtev_rec => p_qtev_rec,
1075 p_cle_id => p_cle_id,
1076 p_exclude_all_styles => l_processed_all_styles,
1077 p_exclude_link_styles => l_processed_link_styles,
1078 px_tqlv_tbl => px_tqlv_tbl,
1079 x_operand_total => l_operand_total,
1080 x_return_status => l_return_status);
1081
1082 END IF;
1083
1084 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1085 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1086 l_overall_status := l_return_status;
1087 END IF;
1088 END IF;
1089
1090 x_operand_total := l_operand_total;
1091 x_return_status := l_overall_status;
1092
1093 IF (is_debug_procedure_on) THEN
1094 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1095 END IF;
1096
1097 EXCEPTION
1098
1099 WHEN OTHERS THEN
1100
1101 IF (is_debug_exception_on) THEN
1102 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1103 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1104 END IF;
1105 -- store SQL error message on message stack for caller
1106 OKL_API.SET_MESSAGE (
1107 p_app_name => G_APP_NAME
1108 ,p_msg_name => G_UNEXPECTED_ERROR
1109 ,p_token1 => G_SQLCODE_TOKEN
1110 ,p_token1_value => sqlcode
1111 ,p_token2 => G_SQLERRM_TOKEN
1112 ,p_token2_value => sqlerrm);
1113
1114 -- notify caller of an UNEXPECTED error
1115 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1116
1117 END calc_stream_type_operand;
1118
1119
1120 END OKL_AM_CALC_QUOTE_STREAM_PVT;