1 PACKAGE BODY OKL_STREAMS_RECON_PVT AS
2 /* $Header: OKLRSCRB.pls 120.10 2007/06/21 14:34:24 varangan noship $ */
3
4 SUBTYPE error_message_type IS Okl_Accounting_Util.ERROR_MESSAGE_TYPE;
5
6 --------------------------------------------------------------------------------
7 -- Start of Commnets
8 -- Badrinath Kuchibhotla
9 -- Procedure Name : recon_qry
10 -- Description : Generates the streams reconciliation report
11 -- Business Rules : we need to reconcile the streams amount
12 -- We reconcile the Total streams, Billed streams
13 -- cancled streams , Unbilled streams and
14 -- then get the differences
15 -- If the there is value for unbilled streams
16 -- Send we show the amount for each deal type and
17 -- associated products for the same.
18 -- If the there is value for difference streams
19 -- Send we show the amount for total billed amount
20 -- unbilled amounts, canceled amount and then difference
21 -- Difference amout = total_billed_amount - billed streams
22 -- - Canceled streams - Unbilled streams.
23 -- Parameters : p_contract_number, p_end_date
24 -- Version : 1.0
25 -- History : BAKUCHIB 20-JAN-2004 - 3344086 created
26 -- End of Commnets
27 --------------------------------------------------------------------------------
28 PROCEDURE recon_qry (p_errbuf OUT NOCOPY VARCHAR2,
29 p_retcode OUT NOCOPY NUMBER,
30 p_contract_number IN okc_k_headers_b.contract_number%TYPE DEFAULT NULL,
31 p_end_date IN VARCHAR2 DEFAULT NULL)
32 IS
33 l_api_name CONSTANT VARCHAR2(40):= 'OKL_STREAMS_RECON_REPORT';
34 l_api_version CONSTANT NUMBER := 1.0;
35 p_api_version CONSTANT NUMBER := 1.0;
36 l_init_msg_list VARCHAR2(3) := okl_api.g_true;
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(2000);
39 x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
40 lp_end_date VARCHAR2(200);
41 lp_contract_number VARCHAR2(200);
42 ln_org_id okc_k_headers_b.authoring_org_id%TYPE := 0;
43 lv_org_name mtl_organizations.organization_name%TYPE := NULL;
44 lv_sum_curr_code VARCHAR2(2000);
45 lv_curr_code VARCHAR2(20) := 'XXX';
46 lv_clb_display VARCHAR2(3) := 'N';
47 lv_dif_display VARCHAR2(3) := 'N';
48 lv_curr_dif_code VARCHAR2(20) := 'XXX';
49 lv_dlts_curr_code VARCHAR2(20) := 'XXX';
50 lv_book_class VARCHAR2(100) := 'XXX';
51 lv_contract_number VARCHAR2(150) := 'XXXXXXXXXX';
52 lv_dlt_curr_code VARCHAR2(2000);
53 lv_tot_amt VARCHAR2(2000);
54 lv_diff_dlts VARCHAR2(2000);
55 lv_diff_dlts_dash VARCHAR2(2000);
56 lv_diff_dlts_khr VARCHAR2(2000);
57 lv_dlt_book VARCHAR2(2000);
58 lv_dlt_pdt VARCHAR2(2000);
59 lv_bil_amt VARCHAR2(2000);
60 lv_can_amt VARCHAR2(2000);
61 lv_clb_amt VARCHAR2(2000);
62 lv_dif_amt VARCHAR2(2000);
63 lv_dif_amt1 VARCHAR2(2000);
64 lv_dif_amt2 VARCHAR2(2000);
65 lv_dlt_tot_amt VARCHAR2(2000);
66 lv_dlt_amt VARCHAR2(2000);
67 lv_diff_dlts_amt VARCHAR2(2000);
68 lv_sum_bil_dash VARCHAR2(2000);
69 lv_sum_can_dash VARCHAR2(2000);
70 lv_sum_clb_dash VARCHAR2(2000);
71 lv_value_clb VARCHAR2(3) := 'N';
72 lv_value_dif VARCHAR2(3) := 'N';
73 lv_sum_dif_dash VARCHAR2(2000);
74 lv_end_date DATE;
75 delimit VARCHAR2(10) := ' ';
76 lv_dash VARCHAR2(10) := '-';
77 lv_frm_amt VARCHAR2(30) := '999,999,999,990.00';
78 i NUMBER := 0;
79 c NUMBER := 0;
80 k NUMBER := 0;
81 h NUMBER := 0;
82 m NUMBER := 0;
83 a NUMBER := 0;
84 ln_tot_curr_amt NUMBER := 0;
85 ln_bil_curr_amt NUMBER := 0;
86 ln_can_curr_amt NUMBER := 0;
87 ln_clb_curr_amt NUMBER := 0;
88 ln_dif_curr_amt NUMBER := 0;
89 ln_tot_amt NUMBER := 0;
90 ln_bil_amt NUMBER := 0;
91 ln_can_amt NUMBER := 0;
92 ln_clb_amt NUMBER := 0;
93 ln_cnt_tot NUMBER := 0;
94 ln_cnt_bil NUMBER := 0;
95 ln_cnt_can NUMBER := 0;
96 ln_cnt_clb NUMBER := 0;
97 ln_dif_amt NUMBER := 0;
98 ln_dlt_tot_amt NUMBER := 0;
99 ln_diff_dlts_amt NUMBER := 0;
100 ln_dlt_amt NUMBER := 0;
101 ln_dif_amt1 NUMBER := 0;
102 ln_dif_amt2 NUMBER := 0;
103 TYPE unbill_rec_type IS RECORD (
104 book_class VARCHAR2(2000) := NULL,
105 currency_code VARCHAR2(2000) := NULL,
106 product_name VARCHAR2(2000) := NULL,
107 amount NUMBER := 0);
108 TYPE value_rec_type IS RECORD (
109 clb_amt NUMBER := 0,
110 dif_amt NUMBER := 0,
111 curr_code okc_k_headers_b.currency_code%TYPE := NULL);
112 TYPE diff_rec_type IS RECORD (
113 contract_number okc_k_headers_b.contract_number%TYPE := NULL,
114 currency_code okc_k_headers_b.currency_code%TYPE := NULL,
115 total_billable_streams VARCHAR2(2000) := NULL,
116 billed_streams VARCHAR2(2000) := NULL,
117 cancelled_streams VARCHAR2(2000) := NULL,
118 unbilled_streams VARCHAR2(2000) := NULL,
119 amount NUMBER := 0,
120 diff_amount NUMBER := 0);
121 TYPE unbill_tbl_type IS TABLE OF unbill_rec_type
122 INDEX BY BINARY_INTEGER;
123 TYPE value_tbl_type IS TABLE of value_rec_type
124 INDEX BY BINARY_INTEGER;
125 TYPE curr_tbl_type IS TABLE OF okc_k_headers_b.currency_code%TYPE
126 INDEX BY BINARY_INTEGER;
127 TYPE book_tbl_type IS TABLE OF VARCHAR2(2000)
128 INDEX BY BINARY_INTEGER;
129 TYPE diff_tbl_type IS TABLE OF diff_rec_type
130 INDEX BY BINARY_INTEGER;
131 lt_unbill_tbl unbill_tbl_type;
132 lt_value_tbl value_tbl_type;
133 lt_diff_tbl diff_tbl_type;
134 lt_diff_tbl_1 diff_tbl_type;
135 lt_curr_tbl curr_tbl_type;
136 lt_curr_tbl_1 curr_tbl_type;
137 lt_book_tbl book_tbl_type;
138 l_error_msg_rec error_message_type;
139 -- To get authoring org id
140 CURSOR get_org_id
141 IS
142 SELECT name,
143 organization_id
144 FROM hr_operating_units
145 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; --MOAC- Concurrent request
146
147 -- To get different currency code in the system
148 CURSOR get_currency_code(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
149 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
150 p_end_date IN okc_k_headers_b.end_date%TYPE)
151 IS
152 SELECT DISTINCT chrb.currency_code
153 FROM okl_strm_elements ele,
154 okl_streams stm,
155 okl_strm_type_b sty,
156 okl_k_headers khr,
157 okc_k_headers_b chrb,
158 okc_k_lines_b kle,
159 okc_statuses_b khs,
160 okc_statuses_b kls
161 WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
162 AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
163 AND chrb.authoring_org_id = p_org_id
164 AND chrb.id = stm.khr_id
165 AND chrb.scs_code IN ('LEASE', 'LOAN')
166 AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
167 AND chrb.id = khr.id
168 AND khr.deal_type IS NOT NULL
169 AND khs.code = chrb.sts_code
170 AND khs.ste_code = 'ACTIVE'
171 AND kle.id(+) = stm.kle_id
172 AND kls.code(+) = kle.sts_code
173 AND stm.sty_id = sty.id
174 AND stm.id = ele.stm_id
175 AND sty.billable_yn = 'Y'
176 AND stm.say_code <> 'WORK'
177 AND stm.purpose_code IS NULL
178 ORDER BY chrb.currency_code DESC;
179 -- To get total streams total
180 CURSOR get_total_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
181 p_curr_code IN okc_k_headers_b.currency_code%TYPE,
182 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
183 p_end_date IN okc_k_headers_b.end_date%TYPE)
184 IS
185 SELECT SUM(ele.amount) amount
186 FROM okl_strm_elements ele,
187 okl_streams stm,
188 okl_strm_type_b sty,
189 okl_k_headers khr,
190 okc_k_headers_b chrb,
191 okc_k_lines_b kle,
192 okc_statuses_b khs,
193 okc_statuses_b kls
194 WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
195 AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
196 AND chrb.authoring_org_id = p_org_id
197 AND chrb.currency_code = p_curr_code
198 AND chrb.id = stm.khr_id
199 AND chrb.scs_code IN ('LEASE', 'LOAN')
200 AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
201 AND chrb.id = khr.id
202 AND khr.deal_type IS NOT NULL
203 AND khs.code = chrb.sts_code
204 AND khs.ste_code = 'ACTIVE'
205 AND kle.id(+) = stm.kle_id
206 AND kls.code(+) = kle.sts_code
207 AND stm.sty_id = sty.id
208 AND stm.id = ele.stm_id
209 AND sty.billable_yn = 'Y'
210 AND stm.say_code <> 'WORK'
211 AND stm.purpose_code IS NULL;
212 -- To get billed streams total
213 CURSOR get_billed_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
214 p_curr_code IN okc_k_headers_b.currency_code%TYPE,
215 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
216 p_end_date IN okc_k_headers_b.end_date%TYPE)
217 IS
218 SELECT SUM(ste.amount) amount
219 FROM okl_strm_type_v sty,
220 okl_strm_elements ste,
221 okl_streams stm,
222 okc_statuses_b khs,
223 okc_statuses_b kls,
224 okl_k_headers khl,
225 okc_k_lines_b kle,
226 okc_k_headers_b khr
227 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
228 AND khr.end_date <= NVL(p_end_date,khr.end_date)
229 AND khr.authoring_org_id = p_org_id
230 AND khr.currency_code = p_curr_code
231 AND khr.id = stm.khr_id
232 AND khr.scs_code IN ('LEASE', 'LOAN')
233 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
234 AND khl.id = stm.khr_id
235 AND khl.deal_type IS NOT NULL
236 AND khs.code = khr.sts_code
237 AND khs.ste_code = 'ACTIVE'
238 AND kle.id(+) = stm.kle_id
239 AND kls.code(+) = kle.sts_code
240 AND ste.amount <> 0
241 AND stm.id = ste.stm_id
242 AND ste.date_billed IS NOT NULL
243 AND stm.say_code IN ('CURR','HIST')
244 AND sty.id = stm.sty_id
245 AND sty.billable_yn = 'Y';
246 -- To get cancelled streams total
247 CURSOR get_cancel_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
248 p_curr_code IN okc_k_headers_b.currency_code%TYPE,
249
250 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
251 p_end_date IN okc_k_headers_b.end_date%TYPE)
252 IS
253 SELECT SUM(ste.amount) amount
254 FROM okl_strm_type_v sty,
255 okl_strm_elements ste,
256 okl_streams stm,
257 okc_statuses_b khs,
258 okc_statuses_b kls,
259 okl_k_headers khl,
260 okc_k_lines_b kle,
261 okc_k_headers_b khr
262 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
263 AND khr.end_date <= NVL(p_end_date,khr.end_date)
264 AND khr.authoring_org_id = p_org_id
265 AND khr.currency_code = p_curr_code
266 AND khr.id = stm.khr_id
267 AND khr.scs_code IN ('LEASE', 'LOAN')
268 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
269 AND khl.id = stm.khr_id
270 AND khl.deal_type IS NOT NULL
271 AND khs.code = khr.sts_code
272 AND khs.ste_code = 'ACTIVE'
273 AND kle.id(+) = stm.kle_id
274 AND kls.code(+) = kle.sts_code
275 AND ste.amount <> 0
276 AND stm.id = ste.stm_id
277 AND ste.date_billed IS NULL
278 AND stm.active_yn = 'N'
279 AND stm.say_code IN ('HIST')
280 AND stm.purpose_code IS NULL
281 AND sty.id = stm.sty_id
282 AND sty.billable_yn = 'Y';
283 -- To get Closing balance streams total
284 CURSOR get_clobal_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
285 p_curr_code IN okc_k_headers_b.currency_code%TYPE,
286 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
287 p_end_date IN okc_k_headers_b.end_date%TYPE)
288 IS
289 SELECT SUM(ste.amount) amount
290 FROM okl_strm_type_v sty,
291 okl_strm_elements ste,
292 okl_streams stm,
293 okc_statuses_b khs,
294 okc_statuses_b kls,
295 okl_k_headers khl,
296 okc_k_lines_b kle,
297 okc_k_headers_b khr
298 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
299 AND khr.end_date <= NVL(p_end_date,khr.end_date)
300 AND khr.authoring_org_id = p_org_id
301 AND khr.currency_code = p_curr_code
302 AND khr.id = stm.khr_id
303 AND khr.scs_code IN ('LEASE', 'LOAN')
304 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
305 AND khl.id = stm.khr_id
306 AND khl.deal_type IS NOT NULL
307 AND khs.code = khr.sts_code
308 AND khs.ste_code = 'ACTIVE'
309 AND kle.id(+) = stm.kle_id
310 AND kls.code(+) = kle.sts_code
311 AND stm.khr_id = khr.id
312 AND stm.id = ste.stm_id
313 AND ste.amount <> 0
314 AND ste.date_billed IS NULL
315 AND stm.active_yn = 'Y'
316 AND stm.say_code = 'CURR'
317 AND stm.purpose_code IS NULL
318 AND sty.id = stm.sty_id
319 AND sty.billable_yn = 'Y';
320 -- To get details of Closing balance based on the deal type and
321 -- Product associated to the same
322 CURSOR get_dlts_clobal_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
323 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
324 p_end_date IN okc_k_headers_b.end_date%TYPE)
325 IS
326 SELECT khr.currency_code currency_code,
327 fnd.meaning book_class,
328 pdt.name product_name,
329 SUM(ste.amount) amount
330 FROM okl_strm_type_v sty,
331 okl_strm_elements ste,
332 okl_streams stm,
333 okc_statuses_b khs,
334 okc_statuses_b kls,
335 okl_products pdt,
336 fnd_lookup_values fnd,
337 okl_k_headers khl,
338 okc_k_lines_b kle,
339 okc_k_headers_b khr
340 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
341 AND khr.end_date <= NVL(p_end_date,khr.end_date)
342 AND khr.authoring_org_id = p_org_id
343 AND khr.id = stm.khr_id
344 AND khr.scs_code IN ('LEASE', 'LOAN')
345 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
346 AND khl.id = stm.khr_id
347 AND khl.deal_type IS NOT NULL
348 AND khs.code = khr.sts_code
349 AND khs.ste_code = 'ACTIVE'
350 AND kle.id(+) = stm.kle_id
351 AND kls.code(+) = kle.sts_code
352 AND stm.khr_id = khr.id
353 AND stm.id = ste.stm_id
354 AND ste.amount <> 0
355 AND ste.date_billed IS NULL
356 AND stm.active_yn = 'Y'
357 AND stm.say_code = 'CURR'
358 AND stm.purpose_code IS NULL
359 AND sty.id = stm.sty_id
360 AND sty.billable_yn = 'Y'
361 AND fnd.lookup_code = khl.deal_type
362 AND fnd.lookup_type = 'OKL_BOOK_CLASS'
363 AND fnd.LANGUAGE = USERENV('LANG')
364 AND khl.pdt_id = pdt.id(+)
365 GROUP BY khr.currency_code,
366 fnd.meaning,
367 pdt.name
368 ORDER BY 1 DESC ;
369 -- To get Difference contracts streams
370 CURSOR get_diff_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
371 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
372 p_end_date IN okc_k_headers_b.end_date%TYPE)
373 IS
374 SELECT chrb.currency_code currency_code,
375 chrb.contract_number contract_number,
376 'Y' total_billable_streams,
377 'N' billed_streams,
378 'N' cancelled_streams,
379 'N' unbilled_streams,
380 SUM(ele.amount) amount
381 FROM okl_strm_elements ele,
382 okl_streams stm,
383 okl_strm_type_b sty,
384 okl_k_headers khr,
385 okc_k_headers_b chrb,
386 okc_k_lines_b kle,
387 okc_statuses_b khs,
388 okc_statuses_b kls
389 WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
390 AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
391 AND chrb.authoring_org_id = p_org_id
392 AND chrb.id = stm.khr_id
393 AND chrb.scs_code IN ('LEASE', 'LOAN')
394 AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
395 AND chrb.id = khr.id
396 AND khr.deal_type IS NOT NULL
397 AND khs.code = chrb.sts_code
398 AND khs.ste_code = 'ACTIVE'
399 AND kle.id(+) = stm.kle_id
400 AND kls.code(+) = kle.sts_code
401 AND stm.sty_id = sty.id
402 AND stm.id = ele.stm_id
403 AND sty.billable_yn = 'Y'
404 AND stm.say_code <> 'WORK'
405 AND stm.purpose_code IS NULL
406 GROUP BY chrb.contract_number,
407 chrb.currency_code
408 UNION
409 SELECT khr.currency_code currency_code,
410 khr.contract_number contract_number,
411 'N' total_billable_streams,
412 'Y' billed_streams,
413 'N' cancelled_streams,
414 'N' unbilled_streams,
415 SUM(ste.amount) billed_streams
416 FROM okl_strm_type_v sty,
417 okl_strm_elements ste,
418 okl_streams stm,
419 okc_statuses_b khs,
420 okc_statuses_b kls,
421 okl_k_headers khl,
422 okc_k_lines_b kle,
423 okc_k_headers_b khr
424 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
425 AND khr.end_date <= NVL(p_end_date,khr.end_date)
426 AND khr.authoring_org_id = p_org_id
427 AND khr.id = stm.khr_id
428 AND khr.scs_code IN ('LEASE', 'LOAN')
429 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
430 AND khl.id = stm.khr_id
431 AND khl.deal_type IS NOT NULL
432 AND khs.code = khr.sts_code
433 AND khs.ste_code = 'ACTIVE'
434 AND kle.id(+) = stm.kle_id
435 AND kls.code(+) = kle.sts_code
436 AND ste.amount <> 0
437 AND stm.id = ste.stm_id
438 AND ste.date_billed IS NOT NULL
439 AND stm.say_code IN ('CURR','HIST')
440 AND sty.id = stm.sty_id
441 AND sty.billable_yn = 'Y'
442 GROUP BY khr.contract_number,
443 khr.currency_code
444 UNION
445 SELECT khr.currency_code currency_code,
446 khr.contract_number contract_number,
447 'N' total_billable_streams,
448 'N' billed_streams,
449 'Y' cancelled_streams,
450 'N' unbilled_streams,
451 SUM(ste.amount)
452 FROM okl_strm_type_v sty,
453 okl_strm_elements ste,
454 okl_streams stm,
455 okc_statuses_b khs,
456 okc_statuses_b kls,
457 okl_k_headers khl,
458 okc_k_lines_b kle,
459 okc_k_headers_b khr
460 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
461 AND khr.end_date <= NVL(p_end_date,khr.end_date)
462 AND khr.authoring_org_id = p_org_id
463 AND khr.id = stm.khr_id
464 AND khr.scs_code IN ('LEASE', 'LOAN')
465 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
466 AND khl.id = stm.khr_id
467 AND khl.deal_type IS NOT NULL
468 AND khs.code = khr.sts_code
469 AND khs.ste_code = 'ACTIVE'
470 AND kle.id(+) = stm.kle_id
471 AND kls.code(+) = kle.sts_code
472 AND ste.amount <> 0
473 AND stm.id = ste.stm_id
474 AND ste.date_billed IS NULL
475 AND stm.active_yn = 'N'
476 AND stm.say_code IN ('HIST')
477 AND stm.purpose_code IS NULL
478 AND sty.id = stm.sty_id
479 AND sty.billable_yn = 'Y'
480 GROUP BY khr.contract_number,
481 khr.currency_code
482 UNION
483 SELECT khr.currency_code currency_code,
484 khr.contract_number contract_number,
485 'N' total_billable_streams,
486 'N' billed_streams,
487 'N' cancelled_streams,
488 'Y' unbilled_streams,
489 SUM(ste.amount)
490 FROM okl_strm_type_v sty,
491 okl_strm_elements ste,
492 okl_streams stm,
493 okc_statuses_b khs,
494 okc_statuses_b kls,
495 okl_k_headers khl,
496 okc_k_lines_b kle,
497 okc_k_headers_b khr
498 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
499 AND khr.end_date <= NVL(p_end_date,khr.end_date)
500 AND khr.authoring_org_id = p_org_id
501 AND khr.id = stm.khr_id
502 AND khr.scs_code IN ('LEASE', 'LOAN')
503 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
504 AND khl.id = stm.khr_id
505 AND khl.deal_type IS NOT NULL
506 AND khs.code = khr.sts_code
507 AND khs.ste_code = 'ACTIVE'
508 AND kle.id(+) = stm.kle_id
509 AND kls.code(+) = kle.sts_code
510 AND stm.khr_id = khr.id
511 AND stm.id = ste.stm_id
512 AND ste.amount <> 0
513 AND ste.date_billed IS NULL
514 AND stm.active_yn = 'Y'
515 AND stm.say_code = 'CURR'
516 AND stm.purpose_code IS NULL
517 AND sty.id = stm.sty_id
518 AND sty.billable_yn = 'Y'
519 GROUP BY khr.contract_number,
520 khr.currency_code
521 ORDER BY 1 DESC;
522 BEGIN
523 x_return_status := OKL_API.G_RET_STS_SUCCESS;
524 -- Call start_activity to create savepoint, check compatibility
525 -- and initialize message list
526 p_retcode := 0;
527 x_return_status := okl_api.start_activity(
528 l_api_name,
529 G_PKG_NAME,
530 l_init_msg_list,
531 l_api_version,
532 p_api_version,
533 '_PVT',
534 x_return_status);
535 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
536 fnd_file.put_line(fnd_file.log,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_ERROR_ACTIVITY'),1,30));
537 RAISE okl_api.g_exception_unexpected_error;
538 ELSIF (x_return_Status = okl_api.g_ret_sts_error) THEN
539 fnd_file.put_line(fnd_file.log,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_ERROR_ACTIVITY'),1,30));
540 RAISE okl_api.g_exception_error;
541 END IF;
542 fnd_file.put_line(fnd_file.log,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_START_ACTIVITY'),1,34));
543 fnd_file.put_line(fnd_file.log,delimit);
544 -- we need to convert the date from varchar2 to date format
545 -- Since the parametr we use in the concurrent program is fnd_standard_date
546 IF p_end_date IS NOT NULL THEN
547 lv_end_date := fnd_date.canonical_to_date(p_end_date);
548 END IF;
549 -- To get authoring org id
550 OPEN get_org_id;
551 FETCH get_org_id INTO lv_org_name,
552 ln_org_id;
553 IF get_org_id%NOTFOUND THEN
554 fnd_file.put_line(fnd_file.log,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_ERROR_ORG'),1,36));
555 raise okl_api.g_exception_error;
556 END IF;
557 CLOSE get_org_id;
558 -- Filling in the header section of the report file
559 fnd_file.put_line(fnd_file.log, SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_GEN_SUM'),1,52));
560 fnd_file.put_line(fnd_file.log, ' ');
561 fnd_file.put_line(fnd_file.output,RPAD(delimit,55)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_ACCT_LEASE_MANAGEMENT'),1,30)||LPAD(delimit,55));
562 fnd_file.put_line(fnd_file.output,RPAD(delimit,47)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_RECON_PROCESS_REPORT'),1,50)||LPAD(delimit,47));
563 fnd_file.put_line(fnd_file.output,RPAD(delimit,47)||RPAD(lv_dash,38,lv_dash)||LPAD(delimit,47));
564 fnd_file.put_line(fnd_file.output,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_RUN_DATE'),1,12)||RPAD(delimit,8)||': '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
565 fnd_file.put_line(fnd_file.output,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_OPERUNIT'),1,20)||RPAD(delimit,2)||': '||lv_org_name);
566 fnd_file.put_line(fnd_file.output,delimit);
567 fnd_file.put_line(fnd_file.output,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_PARAMETERS'),1,13));
568 fnd_file.put_line(fnd_file.output,RPAD(lv_dash,13,lv_dash));
569 lp_contract_number := SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_CONTRACT_NUMBER'),1,20)||RPAD(delimit,1)||': ';
570 fnd_file.put_line(fnd_file.output,lp_contract_number||NVL(p_contract_number,okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_NOT_SUPPLIED')));
571 lp_end_date := SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_END_DATE'),1,12)||RPAD(delimit,8)||': ';
572 fnd_file.put_line(fnd_file.output,lp_end_date||NVL(p_end_date,okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_NOT_SUPPLIED')));
573 fnd_file.put_line(fnd_file.output,delimit);
574 -- Filling in the summary section of the report file
575 -- Now we are populating the FND_FILE to display the above resultant record
576 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_SUMMARY'),1,10));
577 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||RPAD(lv_dash,7,lv_dash));
578 fnd_file.put_line(fnd_file.output,delimit);
579 lv_sum_bil_dash := RPAD(delimit,45);
580 lv_tot_amt := RPAD(delimit,15)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_TOT_BILL_STREAM'),1,29)||RPAD(delimit,7)||':';
581 lv_bil_amt := RPAD(delimit,15)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_BILL_STREAM'),1,25)||RPAD(delimit,10)||':';
582 lv_can_amt := RPAD(delimit,15)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_CANCEL_STREAM'),1,29)||RPAD(delimit,7)||':';
583 lv_clb_amt := RPAD(delimit,15)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_UNBILL_STREAM'),1,28)||RPAD(delimit,8)||':';
584 lv_dif_amt := RPAD(delimit,15)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_DIFF_STREAM'),1,13)||RPAD(delimit,19)||':';
585 lv_dif_amt1 := RPAD(delimit,45);
586 lv_dif_amt2 := RPAD(delimit,45);
587 lv_sum_bil_dash := lv_sum_bil_dash||RPAD(lv_dash,22,lv_dash)||RPAD(delimit,1);
588 FOR get_currency_code_rec IN get_currency_code(p_org_id => ln_org_id,
589 p_contract_number => p_contract_number,
590 p_end_date => lv_end_date) LOOP
591 fnd_file.put_line(fnd_file.output,delimit||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_CURRENCY'),1,11)||' : '||get_currency_code_rec.currency_code);
592 fnd_file.put_line(fnd_file.output,delimit);
593 -- To get total streams total
594 OPEN get_total_csr(p_org_id => ln_org_id,
595 p_curr_code => get_currency_code_rec.currency_code,
596 p_contract_number => p_contract_number,
597 p_end_date => lv_end_date);
598 FETCH get_total_csr INTO ln_tot_curr_amt;
599 IF get_total_csr%NOTFOUND THEN
600 ln_tot_curr_amt := 0;
601 END IF;
602 CLOSE get_total_csr;
603 -- To get billed streams total
604 OPEN get_billed_csr(p_org_id => ln_org_id,
605 p_curr_code => get_currency_code_rec.currency_code,
606 p_contract_number => p_contract_number,
607 p_end_date => lv_end_date);
608 FETCH get_billed_csr INTO ln_bil_curr_amt;
609 IF get_billed_csr%NOTFOUND THEN
610 ln_bil_curr_amt := 0;
611 END IF;
612 CLOSE get_billed_csr;
613 fnd_file.put_line(fnd_file.output,lv_tot_amt||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_tot_curr_amt,0),lv_frm_amt))),1,22),22));
614 fnd_file.put_line(fnd_file.output,lv_bil_amt||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_bil_curr_amt,0),lv_frm_amt))),1,22),22));
615 fnd_file.put_line(fnd_file.output,lv_sum_bil_dash);
616 -- To get differnece in the stream amount
617 -- to show the difference of Total billable streams and billed streams we do the below
618 ln_dif_amt1 := ln_tot_curr_amt - ln_bil_curr_amt;
619 fnd_file.put_line(fnd_file.output,lv_dif_amt1||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_dif_amt1,0),lv_frm_amt))),1,22),22));
620 -- To get cancelled streams total
621 OPEN get_cancel_csr(p_org_id => ln_org_id,
622 p_curr_code => get_currency_code_rec.currency_code,
623 p_contract_number => p_contract_number,
624 p_end_date => lv_end_date);
625 FETCH get_cancel_csr INTO ln_can_curr_amt;
626 IF get_cancel_csr%NOTFOUND THEN
627 ln_can_curr_amt := 0;
628 END IF;
629 CLOSE get_cancel_csr;
630 fnd_file.put_line(fnd_file.output,lv_can_amt||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_can_curr_amt,0),lv_frm_amt))),1,22),22));
631 fnd_file.put_line(fnd_file.output,lv_sum_bil_dash);
632 -- to show the difference of 1 difference amount and the cancelled amounts
633 ln_dif_amt2 := ln_dif_amt1 - ln_can_curr_amt;
634 fnd_file.put_line(fnd_file.output,lv_dif_amt2||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_dif_amt2,0),lv_frm_amt))),1,22),22));
635 -- To get Closing balance streams total
636 OPEN get_clobal_csr(p_org_id => ln_org_id,
637 p_curr_code => get_currency_code_rec.currency_code,
638 p_contract_number => p_contract_number,
639 p_end_date => lv_end_date);
640 FETCH get_clobal_csr INTO ln_clb_curr_amt;
641 IF get_clobal_csr%NOTFOUND THEN
642 ln_clb_curr_amt := 0;
643 END IF;
644 CLOSE get_clobal_csr;
645 -- To let know the further process that
646 -- there was value for un billed streams
647 IF ln_clb_curr_amt <> 0 THEN
648 lt_value_tbl(a).clb_amt := ln_clb_curr_amt;
649 END IF;
650 fnd_file.put_line(fnd_file.output,lv_clb_amt||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_clb_curr_amt,0),lv_frm_amt))),1,22),22));
651 fnd_file.put_line(fnd_file.output,lv_sum_bil_dash);
652 -- To get differnece in the stream amount
653 -- the components of these are the total Billable streasm less billed streams
654 -- To get differnece in the stream amount
655 -- less cancelled streams and less unbilled streams
656 -- To get differnece in the stream amount
657 ln_dif_amt := NVL(ln_tot_curr_amt,0) - NVL(ln_bil_curr_amt,0) - NVL(ln_can_curr_amt,0) - NVL(ln_clb_curr_amt,0);
658 -- To let know the further process that
659 -- there was value for Difference streams
660 IF ln_dif_amt <> 0 THEN
661 lt_value_tbl(a).dif_amt := ln_dif_amt;
662 lt_value_tbl(a).curr_code := get_currency_code_rec.currency_code;
663 ELSIF ln_dif_amt = 0 THEN
664 lt_value_tbl(a).dif_amt := ln_dif_amt;
665 lt_value_tbl(a).curr_code := get_currency_code_rec.currency_code;
666 END IF;
667 fnd_file.put_line(fnd_file.output,lv_dif_amt||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_dif_amt,0),lv_frm_amt))),1,22),22));
668 fnd_file.put_line(fnd_file.output,lv_sum_bil_dash);
669 fnd_file.put_line(fnd_file.output,delimit);
670 fnd_file.put_line(fnd_file.output,delimit);
671 a := a + 1;
672 END LOOP;
673 -- Filling in the Details section of the unbilled Streams report file
674 fnd_file.put_line(fnd_file.log, SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_GEN_DETAIL_UNBILL'),1,64));
675 fnd_file.put_line(fnd_file.log, ' ');
676 IF lt_value_tbl.COUNT > 0 THEN
677 FOR i IN lt_value_tbl.FIRST..lt_value_tbl.LAST LOOP
678 IF lt_value_tbl(i).clb_amt <> 0 THEN
679 lv_value_clb := 'Y';
680 EXIT WHEN (lv_value_clb = 'Y');
681 ELSIF lt_value_tbl(i).clb_amt = 0 THEN
682 lv_value_clb := 'N';
683 END IF;
684 END LOOP;
685 END IF;
686 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_DLTS_UNBILLED_STREAM'),1,36));
687 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||RPAD(lv_dash,27,lv_dash));
688 fnd_file.put_line(fnd_file.output,delimit);
689 IF lv_value_clb = 'Y' THEN
690 -- To get details of Closing balance based on the deal type and
691 -- Product associated to the same
692 FOR get_dlts_clobal_rec IN get_dlts_clobal_csr(p_org_id => ln_org_id,
693 p_contract_number => p_contract_number,
694 p_end_date => lv_end_date) LOOP
695 lt_unbill_tbl(i).book_class := get_dlts_clobal_rec.book_class;
696 lt_unbill_tbl(i).currency_code := get_dlts_clobal_rec.currency_code;
697 lt_unbill_tbl(i).product_name := get_dlts_clobal_rec.product_name;
698 lt_unbill_tbl(i).amount := get_dlts_clobal_rec.amount;
699 i := i + 1;
700 END LOOP;
701 i := 0;
702 IF lt_unbill_tbl.COUNT > 0 THEN
703 FOR j IN lt_unbill_tbl.FIRST..lt_unbill_tbl.LAST LOOP
704 IF lv_curr_code <> lt_unbill_tbl(j).currency_code THEN
705 lt_curr_tbl(k) := lt_unbill_tbl(j).currency_code;
706 lv_curr_code := lt_curr_tbl(k);
707 k := k + 1;
708 END IF;
709 END LOOP;
710 FOR m IN lt_curr_tbl.FIRST..lt_curr_tbl.LAST LOOP
711 lv_dlt_curr_code := delimit||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_CURRENCY'),1,11)||' : '||lt_curr_tbl(m);
712 fnd_file.put_line(fnd_file.output,lv_dlt_curr_code);
713 fnd_file.put_line(fnd_file.output,delimit);
714 lv_dlt_tot_amt := NULL;
715 lv_dlt_amt := NULL;
716 ln_dlt_tot_amt := 0;
717 ln_dlt_amt := 0;
718 lv_dlt_book := delimit||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_BOOK_CLASSIFICATION'),1,25)||delimit||lv_dash||delimit;
719 lv_dlt_pdt := RPAD(delimit,10)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_FINANCIAL_PRODUCT'),1,23)||delimit;
720 FOR j IN lt_unbill_tbl.FIRST..lt_unbill_tbl.LAST LOOP
721 IF lv_book_class <> lt_unbill_tbl(j).book_class AND
722 lt_unbill_tbl(j).currency_code = lt_curr_tbl(m) THEN
723 fnd_file.put_line(fnd_file.output,lv_dlt_book||lt_unbill_tbl(j).book_class);
724 lv_book_class := lt_unbill_tbl(j).book_class;
725 fnd_file.put_line(fnd_file.output,delimit);
726 fnd_file.put_line(fnd_file.output,lv_dlt_pdt);
727 ln_dlt_amt := 0;
728 FOR h IN lt_unbill_tbl.FIRST..lt_unbill_tbl.LAST LOOP
729 IF lt_unbill_tbl(j).book_class = lt_unbill_tbl(h).book_class AND
730 lt_unbill_tbl(j).currency_code = lt_curr_tbl(m) THEN
731 fnd_file.put_line(fnd_file.output,RPAD(delimit,15)||RPAD(substr(lt_unbill_tbl(h).product_name,1,20),20,delimit)||RPAD(delimit,9)||':'||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(lt_unbill_tbl(h).amount,0),lv_frm_amt))),1,22),22));
732 ln_dlt_amt := ln_dlt_amt + lt_unbill_tbl(h).amount;
733 ln_dlt_tot_amt := ln_dlt_tot_amt + lt_unbill_tbl(h).amount;
734 END IF;
735 END LOOP;
736 fnd_file.put_line(fnd_file.output,RPAD(delimit,45)||RPAD(lv_dash,22,lv_dash));
737 lv_dlt_amt := RPAD(delimit,15)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_TOTAL'),1,7)||RPAD(delimit,24)||':';
738 lv_dlt_amt := lv_dlt_amt||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_dlt_amt,0),lv_frm_amt))),1,22),22);
739 fnd_file.put_line(fnd_file.output,lv_dlt_amt);
740 END IF;
741 END LOOP;
742 fnd_file.put_line(fnd_file.output,RPAD(delimit,45)||RPAD(lv_dash,22,lv_dash));
743 lv_dlt_tot_amt := RPAD(delimit,15)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_TOTAL_UNBILL_STREAM'),1,29)||RPAD(delimit,7)||':';
744 lv_dlt_tot_amt := lv_dlt_tot_amt||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_dlt_tot_amt,0),lv_frm_amt))),1,22),22);
745 fnd_file.put_line(fnd_file.output,lv_dlt_tot_amt);
746 fnd_file.put_line(fnd_file.output,RPAD(delimit,45)||RPAD(lv_dash,22,lv_dash));
747 END LOOP;
748 END IF;
749 ELSIF lv_value_clb = 'N' THEN
750 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_NO_RECORDS'),1,21));
751 END IF;
752 -- Filling in the Details section of the Difference report file
753 fnd_file.put_line(fnd_file.log, SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_GEN_DETAIL_DIFF'),1,67));
754 fnd_file.put_line(fnd_file.log, ' ');
755 fnd_file.put_line(fnd_file.output,delimit);
756 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_DLTS_DIFF_STREAM'),1,38));
757 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||RPAD(lv_dash,29,lv_dash));
758 fnd_file.put_line(fnd_file.output,delimit);
759 FOR get_diff_rec IN get_diff_csr(p_org_id => ln_org_id,
760 p_contract_number => p_contract_number,
761 p_end_date => lv_end_date) LOOP
762 lt_diff_tbl(h).contract_number := get_diff_rec.contract_number;
763 lt_diff_tbl(h).currency_code := get_diff_rec.currency_code;
764 lt_diff_tbl(h).total_billable_streams := get_diff_rec.total_billable_streams;
765 lt_diff_tbl(h).billed_streams := get_diff_rec.billed_streams;
766 lt_diff_tbl(h).cancelled_streams := get_diff_rec.cancelled_streams;
767 lt_diff_tbl(h).unbilled_streams := get_diff_rec.unbilled_streams;
768 lt_diff_tbl(h).amount := get_diff_rec.amount;
769 h := h + 1;
770 END LOOP;
771 IF lt_diff_tbl.COUNT > 0 THEN
772 lv_contract_number := lt_diff_tbl(lt_diff_tbl.FIRST).contract_number;
773 FOR i IN lt_diff_tbl.FIRST..lt_diff_tbl.LAST LOOP
774 IF lv_contract_number = lt_diff_tbl(i).contract_number THEN
775 lt_diff_tbl_1(m).contract_number := lt_diff_tbl(i).contract_number;
776 lt_diff_tbl_1(m).currency_code := lt_diff_tbl(i).currency_code;
777 IF lt_diff_tbl(i).total_billable_streams = 'Y' THEN
778 lt_diff_tbl_1(m).total_billable_streams := TO_CHAR(lt_diff_tbl(i).amount);
779 END IF;
780 IF lt_diff_tbl(i).billed_streams = 'Y' THEN
781 lt_diff_tbl_1(m).billed_streams := TO_CHAR(lt_diff_tbl(i).amount);
782 END IF;
783 IF lt_diff_tbl(i).cancelled_streams = 'Y' THEN
784 lt_diff_tbl_1(m).cancelled_streams := TO_CHAR(lt_diff_tbl(i).amount);
785 END IF;
786 IF lt_diff_tbl(i).unbilled_streams = 'Y' THEN
787 lt_diff_tbl_1(m).unbilled_streams := TO_CHAR(lt_diff_tbl(i).amount);
788 END IF;
789 ELSIF lv_contract_number <> lt_diff_tbl(i).contract_number THEN
790 m := m + 1;
791 lv_contract_number := lt_diff_tbl(i).contract_number;
792 lt_diff_tbl_1(m).contract_number := lt_diff_tbl(i).contract_number;
793 lt_diff_tbl_1(m).currency_code := lt_diff_tbl(i).currency_code;
794 IF lt_diff_tbl(i).total_billable_streams = 'Y' THEN
795 lt_diff_tbl_1(m).total_billable_streams := TO_CHAR(lt_diff_tbl(i).amount);
796 END IF;
797 IF lt_diff_tbl(i).billed_streams = 'Y' THEN
798 lt_diff_tbl_1(m).billed_streams := TO_CHAR(lt_diff_tbl(i).amount);
799 END IF;
800 IF lt_diff_tbl(i).cancelled_streams = 'Y' THEN
801 lt_diff_tbl_1(m).cancelled_streams := TO_CHAR(lt_diff_tbl(i).amount);
802 END IF;
803 IF lt_diff_tbl(i).unbilled_streams = 'Y' THEN
804 lt_diff_tbl_1(m).unbilled_streams := TO_CHAR(lt_diff_tbl(i).amount);
805 END IF;
806 END IF;
807 END LOOP;
808 lv_diff_dlts := delimit;
809 lv_diff_dlts := lv_diff_dlts||okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_CONTRACT_NUMBER')||RPAD(delimit,1);
810 lv_diff_dlts := lv_diff_dlts||okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_TOT_BILL_STREAM')||RPAD(delimit,1);
811 lv_diff_dlts := lv_diff_dlts||LPAD(SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_BILL_STREAM'),5),22)||RPAD(delimit,1);
812 lv_diff_dlts := lv_diff_dlts||LPAD(SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_CANCEL_STREAM'),5),22)||RPAD(delimit,1);
813 lv_diff_dlts := lv_diff_dlts||LPAD(SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_UNBILL_STREAM'),5),22)||RPAD(delimit,1);
814 lv_diff_dlts := lv_diff_dlts||LPAD(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_DIFF_STREAM'),22)||RPAD(delimit,1);
815 lv_diff_dlts_dash := delimit;
816 lv_diff_dlts_dash := lv_diff_dlts_dash||RPAD(lv_dash,15,lv_dash)||RPAD(delimit,1);
817 lv_diff_dlts_dash := lv_diff_dlts_dash||RPAD(lv_dash,22,lv_dash)||RPAD(delimit,1);
818 lv_diff_dlts_dash := lv_diff_dlts_dash||RPAD(lv_dash,22,lv_dash)||RPAD(delimit,1);
819 lv_diff_dlts_dash := lv_diff_dlts_dash||RPAD(lv_dash,22,lv_dash)||RPAD(delimit,1);
820 lv_diff_dlts_dash := lv_diff_dlts_dash||RPAD(lv_dash,22,lv_dash)||RPAD(delimit,1);
821 lv_diff_dlts_dash := lv_diff_dlts_dash||RPAD(lv_dash,22,lv_dash)||RPAD(delimit,1);
822 FOR j IN lt_diff_tbl_1.FIRST..lt_diff_tbl_1.LAST LOOP
823 IF lv_curr_dif_code <> lt_diff_tbl_1(j).currency_code THEN
824 lt_curr_tbl_1(c) := lt_diff_tbl_1(j).currency_code;
825 lv_curr_dif_code := lt_curr_tbl_1(c);
826 c := c + 1;
827 END IF;
828 END LOOP;
829 FOR j IN lt_curr_tbl_1.FIRST..lt_curr_tbl_1.LAST LOOP
830 ln_diff_dlts_amt := 0;
831 lv_diff_dlts_amt := NULL;
832 lv_value_dif := NULL;
833 IF lt_value_tbl.COUNT > 0 THEN
834 FOR i IN lt_value_tbl.FIRST..lt_value_tbl.LAST LOOP
835 IF lt_value_tbl(i).dif_amt <> 0 AND
836 lt_value_tbl(i).curr_code = lt_curr_tbl_1(j) THEN
837 lv_value_dif := 'Y';
838 EXIT WHEN (lv_value_dif = 'Y');
839 ELSIF lt_value_tbl(i).dif_amt = 0 AND
840 lt_value_tbl(i).curr_code = lt_curr_tbl_1(j) THEN
841 lv_value_dif := 'N';
842 END IF;
843 END LOOP;
844 ELSIF NVL(lv_value_dif,'N') = 'N' THEN
845 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_NO_RECORDS'),1,21));
846 END IF;
847 IF lv_value_dif = 'Y' THEN
848 fnd_file.put_line(fnd_file.output,delimit||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_CURRENCY'),1,11)||' : '||lt_curr_tbl_1(j));
849
850 fnd_file.put_line(fnd_file.output,delimit||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_REMARKS'),1,11)||' : ');
851 fnd_file.put_line(fnd_file.output,lv_diff_dlts);
852 fnd_file.put_line(fnd_file.output,lv_diff_dlts_dash);
853 FOR i IN lt_diff_tbl_1.FIRST..lt_diff_tbl_1.LAST LOOP
854 IF lt_diff_tbl_1(i).currency_code = lt_curr_tbl_1(j) THEN
855 lt_diff_tbl_1(i).diff_amount := NVL(TO_NUMBER(lt_diff_tbl_1(i).total_billable_streams),0) -
856 (NVL(TO_NUMBER(lt_diff_tbl_1(i).billed_streams),0) +
857 NVL(TO_NUMBER(lt_diff_tbl_1(i).cancelled_streams),0) +
858 NVL(TO_NUMBER(lt_diff_tbl_1(i).unbilled_streams),0));
859 IF lt_diff_tbl_1(i).diff_amount <> 0 THEN
860 lv_diff_dlts_khr := delimit;
861 lv_diff_dlts_khr := lv_diff_dlts_khr||RPAD(SUBSTR(lt_diff_tbl_1(i).contract_number,1,15),15)||RPAD(delimit,1);
862 lv_diff_dlts_khr := lv_diff_dlts_khr||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(TO_NUMBER(lt_diff_tbl_1(i).total_billable_streams),0),lv_frm_amt))),1,22),22)||RPAD(delimit,1);
863 lv_diff_dlts_khr := lv_diff_dlts_khr||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(TO_NUMBER(lt_diff_tbl_1(i).billed_streams),0),lv_frm_amt))),1,22),22)||RPAD(delimit,1);
864 lv_diff_dlts_khr := lv_diff_dlts_khr||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(TO_NUMBER(lt_diff_tbl_1(i).cancelled_streams),0),lv_frm_amt))),1,22),22)||RPAD(delimit,1);
865 lv_diff_dlts_khr := lv_diff_dlts_khr||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(TO_NUMBER(lt_diff_tbl_1(i).unbilled_streams),0),lv_frm_amt))),1,22),22)||RPAD(delimit,1);
866 lv_diff_dlts_khr := lv_diff_dlts_khr||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(lt_diff_tbl_1(i).diff_amount,0),lv_frm_amt))),1,22),22)||RPAD(delimit,1);
867 fnd_file.puT_line(fnd_file.output,lv_diff_dlts_khr);
868 ln_diff_dlts_amt := ln_diff_dlts_amt + lt_diff_tbl_1(i).diff_amount;
869 END IF;
870 END IF;
871 END LOOP;
872 lv_diff_dlts_amt := RPAD(delimit,112)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_TOTAL'),1,7)||delimit;
873 lv_diff_dlts_amt := lv_diff_dlts_amt||LPAD(SUBSTR(LTRIM(RTRIM(TO_CHAR(NVL(ln_diff_dlts_amt,0),lv_frm_amt))),1,13),13);
874 fnd_file.put_line(fnd_file.output,RPAD(delimit,118)||RPAD(lv_dash,13,lv_dash));
875 fnd_file.put_line(fnd_file.output,lv_diff_dlts_amt);
876 fnd_file.put_line(fnd_file.output,RPAD(delimit,118)||RPAD(lv_dash,13,lv_dash));
877 ELSIF NVL(lv_value_dif,'N') = 'N' THEN
878 fnd_file.put_line(fnd_file.output,RPAD(delimit,12)||SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_NO_RECORDS'),1,21));
879 END IF;
880 END LOOP;
881 END IF;
882 fnd_file.put_line(fnd_file.output,delimit);
883 fnd_file.put_line(fnd_file.output,delimit);
884 fnd_file.put_line(fnd_file.output,'Copyright (c) 1979, Oracle Corporation. All rights reserved.');
885 okl_api.end_activity(l_msg_count, l_msg_data);
886 fnd_file.put_line(fnd_file.log,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_END_ACTIVITY'),1,32));
887 p_retcode := 0;
888 EXCEPTION
889 WHEN okl_api.g_exception_error THEN
890 p_retcode := 2;
891 IF get_org_id%ISOPEN THEN
892 CLOSE get_org_id;
893 END IF;
894 IF get_currency_code%ISOPEN THEN
895 CLOSE get_currency_code;
896 END IF;
897 IF get_total_csr%ISOPEN THEN
898 CLOSE get_total_csr;
899 END IF;
900 IF get_billed_csr%ISOPEN THEN
901 CLOSE get_billed_csr;
902 END IF;
903 IF get_cancel_csr%ISOPEN THEN
904 CLOSE get_cancel_csr;
905 END IF;
906 IF get_clobal_csr%ISOPEN THEN
907 CLOSE get_clobal_csr;
908 END IF;
909 IF get_dlts_clobal_csr%ISOPEN THEN
910 CLOSE get_dlts_clobal_csr;
911 END IF;
912 IF get_diff_csr%ISOPEN THEN
913 CLOSE get_diff_csr;
914 END IF;
915 x_return_status := okl_api.handle_exceptions(
916 l_api_name,
917 g_pkg_name,
918 'okl_api.g_ret_sts_error',
919 l_msg_count,
920 l_msg_data,
921 '_PVT');
922 -- print the error message in the log file
923 okl_accounting_util.get_error_message(l_error_msg_rec);
924 IF (l_error_msg_rec.COUNT > 0) THEN
925 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
926 fnd_file.put_line(fnd_file.output, l_error_msg_rec(i));
927 END LOOP;
928 END IF;
929 WHEN okl_api.g_exception_unexpected_error THEN
930 IF get_org_id%ISOPEN THEN
931 CLOSE get_org_id;
932 END IF;
933 IF get_currency_code%ISOPEN THEN
934 CLOSE get_currency_code;
935 END IF;
936 IF get_total_csr%ISOPEN THEN
937 CLOSE get_total_csr;
938 END IF;
939 IF get_billed_csr%ISOPEN THEN
940 CLOSE get_billed_csr;
941 END IF;
942 IF get_cancel_csr%ISOPEN THEN
943 CLOSE get_cancel_csr;
944 END IF;
945 IF get_clobal_csr%ISOPEN THEN
946 CLOSE get_clobal_csr;
947 END IF;
948 IF get_dlts_clobal_csr%ISOPEN THEN
949 CLOSE get_dlts_clobal_csr;
950 END IF;
951
952 IF get_diff_csr%ISOPEN THEN
953 CLOSE get_diff_csr;
954 END IF;
955 x_return_status := okl_api.handle_exceptions(
956 l_api_name,
957 g_pkg_name,
958 'okl_api.g_ret_sts_unexp_error',
959 l_msg_count,
960 l_msg_data,
961 '_PVT');
962 -- print the error message in the log file
963 okl_accounting_util.get_error_message(l_error_msg_rec);
964 IF (l_error_msg_rec.COUNT > 0) THEN
965 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
966 fnd_file.put_line(fnd_file.output, l_error_msg_rec(i));
967 END LOOP;
968 END IF;
969 WHEN OTHERS THEN
970 p_errbuf := SQLERRM;
971 p_retcode := 2;
972 IF get_org_id%ISOPEN THEN
973 CLOSE get_org_id;
974 END IF;
975 IF get_currency_code%ISOPEN THEN
976 CLOSE get_currency_code;
977 END IF;
978 IF get_total_csr%ISOPEN THEN
979 CLOSE get_total_csr;
980 END IF;
981 IF get_billed_csr%ISOPEN THEN
982 CLOSE get_billed_csr;
983 END IF;
984 IF get_cancel_csr%ISOPEN THEN
985 CLOSE get_cancel_csr;
986 END IF;
987 IF get_clobal_csr%ISOPEN THEN
988 CLOSE get_clobal_csr;
989 END IF;
990 IF get_dlts_clobal_csr%ISOPEN THEN
991 CLOSE get_dlts_clobal_csr;
992 END IF;
993 IF get_diff_csr%ISOPEN THEN
994 CLOSE get_diff_csr;
995 END IF;
996 x_return_status := okl_api.handle_exceptions(
997 l_api_name,
998 g_pkg_name,
999 'OTHERS',
1000 l_msg_count,
1001 l_msg_data,
1002 '_PVT');
1003 -- print the error message in the log file
1004 okl_accounting_util.get_error_message(l_error_msg_rec);
1005 IF (l_error_msg_rec.COUNT > 0) THEN
1006 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
1007 fnd_file.put_line(fnd_file.output, l_error_msg_rec(i));
1008 END LOOP;
1009 END IF;
1010 fnd_file.put_line(fnd_file.log, SQLERRM);
1011 END recon_qry;
1012
1013 -------------------------------------------------------------------------------
1014 -- Function xml_recon_qry
1015 -------------------------------------------------------------------------------
1016 -- Start of comments
1017 --
1018 -- Procedure Name : xml_recon_qry
1019 -- Description : Function for Billable Streams reconciliation Report Generation
1020 -- in XML Publisher
1021 -- Business Rules :
1022 -- Parameters : p_contract_number, p_end_date
1023 -- Version : 1.0
1024 -- History : 03-Jan-2007 UDHENUKO created.
1025 -- End of comments
1026 -------------------------------------------------------------------------------
1027 FUNCTION xml_recon_qry RETURN BOOLEAN
1028 IS
1029 p_errbuf VARCHAR2(1000) := NULL;
1030 p_retcode NUMBER;
1031 l_api_name CONSTANT VARCHAR2(40):= 'OKL_STREAMS_RECON_REPORT';
1032 l_api_version CONSTANT NUMBER := 1.0;
1033 p_api_version CONSTANT NUMBER := 1.0;
1034 l_init_msg_list VARCHAR2(3) := okl_api.g_true;
1035 l_msg_count NUMBER;
1036 l_msg_data VARCHAR2(2000);
1037 x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1038 lp_end_date VARCHAR2(200);
1039 lp_contract_number VARCHAR2(200);
1040 ln_org_id okc_k_headers_b.authoring_org_id%TYPE := 0;
1041 lv_org_name mtl_organizations.organization_name%TYPE := NULL;
1042 lv_sum_curr_code VARCHAR2(2000);
1043 lv_curr_code VARCHAR2(20) := 'XXX';
1044 lv_clb_display VARCHAR2(3) := 'N';
1045 lv_dif_display VARCHAR2(3) := 'N';
1046 lv_curr_dif_code VARCHAR2(20) := 'XXX';
1047 lv_dlts_curr_code VARCHAR2(20) := 'XXX';
1048 lv_book_class VARCHAR2(100) := 'XXX';
1049 lv_contract_number VARCHAR2(150) := 'XXXXXXXXXX';
1050 lv_dlt_curr_code VARCHAR2(2000);
1051 lv_tot_amt VARCHAR2(2000);
1052 lv_diff_dlts VARCHAR2(2000);
1053 lv_diff_dlts_dash VARCHAR2(2000);
1054 lv_diff_dlts_khr VARCHAR2(2000);
1055 lv_dlt_book VARCHAR2(2000);
1056 lv_dlt_pdt VARCHAR2(2000);
1057 lv_bil_amt VARCHAR2(2000);
1058 lv_can_amt VARCHAR2(2000);
1059 lv_clb_amt VARCHAR2(2000);
1060 lv_dif_amt VARCHAR2(2000);
1061 lv_dif_amt1 VARCHAR2(2000);
1062 lv_dif_amt2 VARCHAR2(2000);
1063 lv_dlt_tot_amt VARCHAR2(2000);
1064 lv_dlt_amt VARCHAR2(2000);
1065 lv_diff_dlts_amt VARCHAR2(2000);
1066 lv_sum_bil_dash VARCHAR2(2000);
1067 lv_sum_can_dash VARCHAR2(2000);
1068 lv_sum_clb_dash VARCHAR2(2000);
1069 lv_value_clb VARCHAR2(3) := 'N';
1070 lv_value_dif VARCHAR2(3) := 'N';
1071 lv_sum_dif_dash VARCHAR2(2000);
1072 lv_end_date DATE;
1073 delimit VARCHAR2(10) := ' ';
1074 lv_dash VARCHAR2(10) := '-';
1075 lv_frm_amt VARCHAR2(30) := '999,999,999,990.00';
1076 i NUMBER := 0;
1077 c NUMBER := 0;
1078 k NUMBER := 0;
1079 h NUMBER := 0;
1080 m NUMBER := 0;
1081 a NUMBER := 0;
1082 ln_tot_curr_amt NUMBER := 0;
1083 ln_bil_curr_amt NUMBER := 0;
1084 ln_can_curr_amt NUMBER := 0;
1085 ln_clb_curr_amt NUMBER := 0;
1086 ln_dif_curr_amt NUMBER := 0;
1087 ln_tot_amt NUMBER := 0;
1088 ln_bil_amt NUMBER := 0;
1089 ln_can_amt NUMBER := 0;
1090 ln_clb_amt NUMBER := 0;
1091 ln_cnt_tot NUMBER := 0;
1092 ln_cnt_bil NUMBER := 0;
1093 ln_cnt_can NUMBER := 0;
1094 ln_cnt_clb NUMBER := 0;
1095 ln_dif_amt NUMBER := 0;
1096 ln_dlt_tot_amt NUMBER := 0;
1097 ln_diff_dlts_amt NUMBER := 0;
1098 ln_dlt_amt NUMBER := 0;
1099 ln_dif_amt1 NUMBER := 0;
1100 ln_dif_amt2 NUMBER := 0;
1101 smry_cnt NUMBER := 0;
1102 ubil_cnt NUMBER := 0;
1103 diff_cnt NUMBER := 0;
1104 TYPE unbill_rec_type IS RECORD (
1105 book_class VARCHAR2(2000) := NULL,
1106 currency_code VARCHAR2(2000) := NULL,
1107 product_name VARCHAR2(2000) := NULL,
1108 amount NUMBER := 0);
1109 TYPE value_rec_type IS RECORD (
1110 clb_amt NUMBER := 0,
1111 dif_amt NUMBER := 0,
1112 curr_code okc_k_headers_b.currency_code%TYPE := NULL);
1113 TYPE diff_rec_type IS RECORD (
1114 contract_number okc_k_headers_b.contract_number%TYPE := NULL,
1115 currency_code okc_k_headers_b.currency_code%TYPE := NULL,
1116 total_billable_streams VARCHAR2(2000) := NULL,
1117 billed_streams VARCHAR2(2000) := NULL,
1118 cancelled_streams VARCHAR2(2000) := NULL,
1119 unbilled_streams VARCHAR2(2000) := NULL,
1120 amount NUMBER := 0,
1121 diff_amount NUMBER := 0);
1122 TYPE gt_unbill_rec_type IS RECORD (
1123 book_class VARCHAR2(2000) := NULL,
1124 currency_code VARCHAR2(2000) := NULL,
1125 product_name VARCHAR2(2000) := NULL,
1126 amount NUMBER := 0);
1127 TYPE gt_diff_rec_type IS RECORD (
1128 contract_number okc_k_headers_b.contract_number%TYPE := NULL,
1129 currency_code okc_k_headers_b.currency_code%TYPE := NULL,
1130 total_billable_streams NUMBER := 0,
1131 billed_streams NUMBER := 0,
1132 cancelled_streams NUMBER := 0,
1133 unbilled_streams NUMBER := 0,
1134 diff_amount NUMBER := 0);
1135 TYPE bill_smry_rec_type IS RECORD (
1136 currency_code VARCHAR2(2000) := NULL,
1137 total_strm VARCHAR2(2000) := NULL,
1138 bill_total_strm VARCHAR2(2000) := NULL,
1139 diff1_total_strm VARCHAR2(2000) := NULL,
1140 cancel_total_strm VARCHAR2(2000) := NULL,
1141 diff2_total_strm VARCHAR2(2000) := NULL,
1142 clobal_total_strm VARCHAR2(2000) := NULL,
1143 main_diff_total VARCHAR2(2000) := NULL);
1144 TYPE unbill_tbl_type IS TABLE OF unbill_rec_type
1145 INDEX BY BINARY_INTEGER;
1146 TYPE value_tbl_type IS TABLE of value_rec_type
1147 INDEX BY BINARY_INTEGER;
1148 TYPE curr_tbl_type IS TABLE OF okc_k_headers_b.currency_code%TYPE
1149 INDEX BY BINARY_INTEGER;
1150 TYPE book_tbl_type IS TABLE OF VARCHAR2(2000)
1151 INDEX BY BINARY_INTEGER;
1152 TYPE diff_tbl_type IS TABLE OF diff_rec_type
1153 INDEX BY BINARY_INTEGER;
1154 TYPE gt_unbill_tbl_type IS TABLE OF gt_unbill_rec_type
1155 INDEX BY BINARY_INTEGER;
1156 TYPE gt_diff_tbl_type IS TABLE OF gt_diff_rec_type
1157 INDEX BY BINARY_INTEGER;
1158 TYPE bill_smry_tbl_type IS TABLE OF bill_smry_rec_type
1159 INDEX BY BINARY_INTEGER;
1160
1161 lt_unbill_tbl unbill_tbl_type;
1162 lt_unbill_gt_tbl gt_unbill_tbl_type;
1163 lt_value_tbl value_tbl_type;
1164 lt_diff_tbl diff_tbl_type;
1165 lt_diff_tbl_1 diff_tbl_type;
1166 lt_diff_gt_tbl gt_diff_tbl_type;
1167 lt_curr_tbl curr_tbl_type;
1168 lt_curr_tbl_1 curr_tbl_type;
1169 lt_book_tbl book_tbl_type;
1170 lt_bill_smry_gt_tbl bill_smry_tbl_type;
1171 l_error_msg_rec error_message_type;
1172 -- To get authoring org id
1173 CURSOR get_org_id
1174 IS
1175 SELECT name,
1176 organization_id
1177 FROM hr_operating_units
1178 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; --MOAC- Concurrent request
1179
1180 -- To get different currency code in the system
1181 CURSOR get_currency_code(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
1182 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
1183 p_end_date IN okc_k_headers_b.end_date%TYPE)
1184 IS
1185 SELECT DISTINCT chrb.currency_code
1186 FROM okl_strm_elements ele,
1187 okl_streams stm,
1188 okl_strm_type_b sty,
1189 okl_k_headers khr,
1190 okc_k_headers_b chrb,
1191 okc_k_lines_b kle,
1192 okc_statuses_b khs,
1193 okc_statuses_b kls
1194 WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
1195 AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
1196 AND chrb.authoring_org_id = p_org_id
1197 AND chrb.id = stm.khr_id
1198 AND chrb.scs_code IN ('LEASE', 'LOAN')
1199 AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
1200 AND chrb.id = khr.id
1201 AND khr.deal_type IS NOT NULL
1202 AND khs.code = chrb.sts_code
1203 AND khs.ste_code = 'ACTIVE'
1204 AND kle.id(+) = stm.kle_id
1205 AND kls.code(+) = kle.sts_code
1206 AND stm.sty_id = sty.id
1207 AND stm.id = ele.stm_id
1208 AND sty.billable_yn = 'Y'
1209 AND stm.say_code <> 'WORK'
1210 AND stm.purpose_code IS NULL
1211 ORDER BY chrb.currency_code DESC;
1212 -- To get total streams total
1213 CURSOR get_total_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
1214 p_curr_code IN okc_k_headers_b.currency_code%TYPE,
1215 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
1216 p_end_date IN okc_k_headers_b.end_date%TYPE)
1217 IS
1218 SELECT SUM(ele.amount) amount
1219 FROM okl_strm_elements ele,
1220 okl_streams stm,
1221 okl_strm_type_b sty,
1222 okl_k_headers khr,
1223 okc_k_headers_b chrb,
1224 okc_k_lines_b kle,
1225 okc_statuses_b khs,
1226 okc_statuses_b kls
1227 WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
1228 AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
1229 AND chrb.authoring_org_id = p_org_id
1230 AND chrb.currency_code = p_curr_code
1231 AND chrb.id = stm.khr_id
1232 AND chrb.scs_code IN ('LEASE', 'LOAN')
1233 AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
1234 AND chrb.id = khr.id
1235 AND khr.deal_type IS NOT NULL
1236 AND khs.code = chrb.sts_code
1237 AND khs.ste_code = 'ACTIVE'
1238 AND kle.id(+) = stm.kle_id
1239 AND kls.code(+) = kle.sts_code
1240 AND stm.sty_id = sty.id
1241 AND stm.id = ele.stm_id
1242 AND sty.billable_yn = 'Y'
1243 AND stm.say_code <> 'WORK'
1244 AND stm.purpose_code IS NULL;
1245 -- To get billed streams total
1246 CURSOR get_billed_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
1247 p_curr_code IN okc_k_headers_b.currency_code%TYPE,
1248 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
1249 p_end_date IN okc_k_headers_b.end_date%TYPE)
1250 IS
1251 SELECT SUM(ste.amount) amount
1252 FROM okl_strm_type_v sty,
1253 okl_strm_elements ste,
1254 okl_streams stm,
1255 okc_statuses_b khs,
1256 okc_statuses_b kls,
1257 okl_k_headers khl,
1258 okc_k_lines_b kle,
1259 okc_k_headers_b khr
1260 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
1261 AND khr.end_date <= NVL(p_end_date,khr.end_date)
1262 AND khr.authoring_org_id = p_org_id
1263 AND khr.currency_code = p_curr_code
1264 AND khr.id = stm.khr_id
1265 AND khr.scs_code IN ('LEASE', 'LOAN')
1266 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
1267 AND khl.id = stm.khr_id
1268 AND khl.deal_type IS NOT NULL
1269 AND khs.code = khr.sts_code
1270 AND khs.ste_code = 'ACTIVE'
1271 AND kle.id(+) = stm.kle_id
1272 AND kls.code(+) = kle.sts_code
1273 AND ste.amount <> 0
1274 AND stm.id = ste.stm_id
1275 AND ste.date_billed IS NOT NULL
1276 AND stm.say_code IN ('CURR','HIST')
1277 AND sty.id = stm.sty_id
1278 AND sty.billable_yn = 'Y';
1279 -- To get cancelled streams total
1280 CURSOR get_cancel_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
1281 p_curr_code IN okc_k_headers_b.currency_code%TYPE,
1282
1283 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
1284 p_end_date IN okc_k_headers_b.end_date%TYPE)
1285 IS
1286 SELECT SUM(ste.amount) amount
1287 FROM okl_strm_type_v sty,
1288 okl_strm_elements ste,
1289 okl_streams stm,
1290 okc_statuses_b khs,
1291 okc_statuses_b kls,
1292 okl_k_headers khl,
1293 okc_k_lines_b kle,
1294 okc_k_headers_b khr
1295 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
1296 AND khr.end_date <= NVL(p_end_date,khr.end_date)
1297 AND khr.authoring_org_id = p_org_id
1298 AND khr.currency_code = p_curr_code
1299 AND khr.id = stm.khr_id
1300 AND khr.scs_code IN ('LEASE', 'LOAN')
1301 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
1302 AND khl.id = stm.khr_id
1303 AND khl.deal_type IS NOT NULL
1304 AND khs.code = khr.sts_code
1305 AND khs.ste_code = 'ACTIVE'
1306 AND kle.id(+) = stm.kle_id
1307 AND kls.code(+) = kle.sts_code
1308 AND ste.amount <> 0
1309 AND stm.id = ste.stm_id
1310 AND ste.date_billed IS NULL
1311 AND stm.active_yn = 'N'
1312 AND stm.say_code IN ('HIST')
1313 AND stm.purpose_code IS NULL
1314 AND sty.id = stm.sty_id
1315 AND sty.billable_yn = 'Y';
1316 -- To get Closing balance streams total
1317 CURSOR get_clobal_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
1318 p_curr_code IN okc_k_headers_b.currency_code%TYPE,
1319 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
1320 p_end_date IN okc_k_headers_b.end_date%TYPE)
1321 IS
1322 SELECT SUM(ste.amount) amount
1323 FROM okl_strm_type_v sty,
1324 okl_strm_elements ste,
1325 okl_streams stm,
1326 okc_statuses_b khs,
1327 okc_statuses_b kls,
1328 okl_k_headers khl,
1329 okc_k_lines_b kle,
1330 okc_k_headers_b khr
1331 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
1332 AND khr.end_date <= NVL(p_end_date,khr.end_date)
1333 AND khr.authoring_org_id = p_org_id
1334 AND khr.currency_code = p_curr_code
1335 AND khr.id = stm.khr_id
1336 AND khr.scs_code IN ('LEASE', 'LOAN')
1337 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
1338 AND khl.id = stm.khr_id
1339 AND khl.deal_type IS NOT NULL
1340 AND khs.code = khr.sts_code
1341 AND khs.ste_code = 'ACTIVE'
1342 AND kle.id(+) = stm.kle_id
1343 AND kls.code(+) = kle.sts_code
1344 AND stm.khr_id = khr.id
1345 AND stm.id = ste.stm_id
1346 AND ste.amount <> 0
1347 AND ste.date_billed IS NULL
1348 AND stm.active_yn = 'Y'
1349 AND stm.say_code = 'CURR'
1350 AND stm.purpose_code IS NULL
1351 AND sty.id = stm.sty_id
1352 AND sty.billable_yn = 'Y';
1353 -- To get details of Closing balance based on the deal type and
1354 -- Product associated to the same
1355 CURSOR get_dlts_clobal_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
1356 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
1357 p_end_date IN okc_k_headers_b.end_date%TYPE)
1358 IS
1359 SELECT khr.currency_code currency_code,
1360 fnd.meaning book_class,
1361 pdt.name product_name,
1362 SUM(ste.amount) amount
1363 FROM okl_strm_type_v sty,
1364 okl_strm_elements ste,
1365 okl_streams stm,
1366 okc_statuses_b khs,
1367 okc_statuses_b kls,
1368 okl_products pdt,
1369 fnd_lookup_values fnd,
1370 okl_k_headers khl,
1371 okc_k_lines_b kle,
1372 okc_k_headers_b khr
1373 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
1374 AND khr.end_date <= NVL(p_end_date,khr.end_date)
1375 AND khr.authoring_org_id = p_org_id
1376 AND khr.id = stm.khr_id
1377 AND khr.scs_code IN ('LEASE', 'LOAN')
1378 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
1379 AND khl.id = stm.khr_id
1380 AND khl.deal_type IS NOT NULL
1381 AND khs.code = khr.sts_code
1382 AND khs.ste_code = 'ACTIVE'
1383 AND kle.id(+) = stm.kle_id
1384 AND kls.code(+) = kle.sts_code
1385 AND stm.khr_id = khr.id
1386 AND stm.id = ste.stm_id
1387 AND ste.amount <> 0
1388 AND ste.date_billed IS NULL
1389 AND stm.active_yn = 'Y'
1390 AND stm.say_code = 'CURR'
1391 AND stm.purpose_code IS NULL
1392 AND sty.id = stm.sty_id
1393 AND sty.billable_yn = 'Y'
1394 AND fnd.lookup_code = khl.deal_type
1395 AND fnd.lookup_type = 'OKL_BOOK_CLASS'
1396 AND fnd.LANGUAGE = USERENV('LANG')
1397 AND khl.pdt_id = pdt.id(+)
1398 GROUP BY khr.currency_code,
1399 fnd.meaning,
1400 pdt.name
1401 ORDER BY 1 DESC ;
1402 -- To get Difference contracts streams
1403 CURSOR get_diff_csr(p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
1404 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
1405 p_end_date IN okc_k_headers_b.end_date%TYPE)
1406 IS
1407 SELECT chrb.currency_code currency_code,
1408 chrb.contract_number contract_number,
1409 'Y' total_billable_streams,
1410 'N' billed_streams,
1411 'N' cancelled_streams,
1412 'N' unbilled_streams,
1413 SUM(ele.amount) amount
1414 FROM okl_strm_elements ele,
1415 okl_streams stm,
1416 okl_strm_type_b sty,
1417 okl_k_headers khr,
1418 okc_k_headers_b chrb,
1419 okc_k_lines_b kle,
1420 okc_statuses_b khs,
1421 okc_statuses_b kls
1422 WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
1423 AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
1424 AND chrb.authoring_org_id = p_org_id
1425 AND chrb.id = stm.khr_id
1426 AND chrb.scs_code IN ('LEASE', 'LOAN')
1427 AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
1428 AND chrb.id = khr.id
1429 AND khr.deal_type IS NOT NULL
1430 AND khs.code = chrb.sts_code
1431 AND khs.ste_code = 'ACTIVE'
1432 AND kle.id(+) = stm.kle_id
1433 AND kls.code(+) = kle.sts_code
1434 AND stm.sty_id = sty.id
1435 AND stm.id = ele.stm_id
1436 AND sty.billable_yn = 'Y'
1437 AND stm.say_code <> 'WORK'
1438 AND stm.purpose_code IS NULL
1439 GROUP BY chrb.contract_number,
1440 chrb.currency_code
1441 UNION
1442 SELECT khr.currency_code currency_code,
1443 khr.contract_number contract_number,
1444 'N' total_billable_streams,
1445 'Y' billed_streams,
1446 'N' cancelled_streams,
1447 'N' unbilled_streams,
1448 SUM(ste.amount) billed_streams
1449 FROM okl_strm_type_v sty,
1450 okl_strm_elements ste,
1451 okl_streams stm,
1452 okc_statuses_b khs,
1453 okc_statuses_b kls,
1454 okl_k_headers khl,
1455 okc_k_lines_b kle,
1456 okc_k_headers_b khr
1457 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
1458 AND khr.end_date <= NVL(p_end_date,khr.end_date)
1459 AND khr.authoring_org_id = p_org_id
1460 AND khr.id = stm.khr_id
1461 AND khr.scs_code IN ('LEASE', 'LOAN')
1462 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
1463 AND khl.id = stm.khr_id
1464 AND khl.deal_type IS NOT NULL
1465 AND khs.code = khr.sts_code
1466 AND khs.ste_code = 'ACTIVE'
1467 AND kle.id(+) = stm.kle_id
1468 AND kls.code(+) = kle.sts_code
1469 AND ste.amount <> 0
1470 AND stm.id = ste.stm_id
1471 AND ste.date_billed IS NOT NULL
1472 AND stm.say_code IN ('CURR','HIST')
1473 AND sty.id = stm.sty_id
1474 AND sty.billable_yn = 'Y'
1475 GROUP BY khr.contract_number,
1476 khr.currency_code
1477 UNION
1478 SELECT khr.currency_code currency_code,
1479 khr.contract_number contract_number,
1480 'N' total_billable_streams,
1481 'N' billed_streams,
1482 'Y' cancelled_streams,
1483 'N' unbilled_streams,
1484 SUM(ste.amount)
1485 FROM okl_strm_type_v sty,
1486 okl_strm_elements ste,
1487 okl_streams stm,
1488 okc_statuses_b khs,
1489 okc_statuses_b kls,
1490 okl_k_headers khl,
1491 okc_k_lines_b kle,
1492 okc_k_headers_b khr
1493 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
1494 AND khr.end_date <= NVL(p_end_date,khr.end_date)
1495 AND khr.authoring_org_id = p_org_id
1496 AND khr.id = stm.khr_id
1497 AND khr.scs_code IN ('LEASE', 'LOAN')
1498 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
1499 AND khl.id = stm.khr_id
1500 AND khl.deal_type IS NOT NULL
1501 AND khs.code = khr.sts_code
1502 AND khs.ste_code = 'ACTIVE'
1503 AND kle.id(+) = stm.kle_id
1504 AND kls.code(+) = kle.sts_code
1505 AND ste.amount <> 0
1506 AND stm.id = ste.stm_id
1507 AND ste.date_billed IS NULL
1508 AND stm.active_yn = 'N'
1509 AND stm.say_code IN ('HIST')
1510 AND stm.purpose_code IS NULL
1511 AND sty.id = stm.sty_id
1512 AND sty.billable_yn = 'Y'
1513 GROUP BY khr.contract_number,
1514 khr.currency_code
1515 UNION
1516 SELECT khr.currency_code currency_code,
1517 khr.contract_number contract_number,
1518 'N' total_billable_streams,
1519 'N' billed_streams,
1520 'N' cancelled_streams,
1521 'Y' unbilled_streams,
1522 SUM(ste.amount)
1523 FROM okl_strm_type_v sty,
1524 okl_strm_elements ste,
1525 okl_streams stm,
1526 okc_statuses_b khs,
1527 okc_statuses_b kls,
1528 okl_k_headers khl,
1529 okc_k_lines_b kle,
1530 okc_k_headers_b khr
1531 WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
1532 AND khr.end_date <= NVL(p_end_date,khr.end_date)
1533 AND khr.authoring_org_id = p_org_id
1534 AND khr.id = stm.khr_id
1535 AND khr.scs_code IN ('LEASE', 'LOAN')
1536 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
1537 AND khl.id = stm.khr_id
1538 AND khl.deal_type IS NOT NULL
1539 AND khs.code = khr.sts_code
1540 AND khs.ste_code = 'ACTIVE'
1541 AND kle.id(+) = stm.kle_id
1542 AND kls.code(+) = kle.sts_code
1543 AND stm.khr_id = khr.id
1544 AND stm.id = ste.stm_id
1545 AND ste.amount <> 0
1546 AND ste.date_billed IS NULL
1547 AND stm.active_yn = 'Y'
1548 AND stm.say_code = 'CURR'
1549 AND stm.purpose_code IS NULL
1550 AND sty.id = stm.sty_id
1551 AND sty.billable_yn = 'Y'
1552 GROUP BY khr.contract_number,
1553 khr.currency_code
1554 ORDER BY 1 DESC;
1555 BEGIN
1556 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1557 -- Call start_activity to create savepoint, check compatibility
1558 -- and initialize message list
1559 p_retcode := 0;
1560 x_return_status := okl_api.start_activity(
1561 l_api_name,
1562 G_PKG_NAME,
1563 l_init_msg_list,
1564 l_api_version,
1565 p_api_version,
1566 '_PVT',
1567 x_return_status);
1568 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1569 fnd_file.put_line(fnd_file.log,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_ERROR_ACTIVITY'),1,30));
1570 RAISE okl_api.g_exception_unexpected_error;
1571 ELSIF (x_return_Status = okl_api.g_ret_sts_error) THEN
1572 fnd_file.put_line(fnd_file.log,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_ERROR_ACTIVITY'),1,30));
1573 RAISE okl_api.g_exception_error;
1574 END IF;
1575 fnd_file.put_line(fnd_file.log,SUBSTR(okl_accounting_util.get_message_token('OKL_LP_CONCURRENT_PROCESS','OKL_START_ACTIVITY'),1,34));
1576 fnd_file.put_line(fnd_file.log,delimit);
1577 -- we need to convert the date from varchar2 to date format
1578 -- Since the parametr we use in the concurrent program is fnd_standard_date
1579 IF P_END_DATE IS NOT NULL THEN
1580 lv_end_date := fnd_date.canonical_to_date(P_END_DATE);
1581 END IF;
1582 -- To get authoring org id
1583 OPEN get_org_id;
1584 FETCH get_org_id INTO lv_org_name,
1585 ln_org_id;
1586 CLOSE get_org_id;
1587 FOR get_currency_code_rec IN get_currency_code(p_org_id => ln_org_id,
1588 p_contract_number => P_CONTRACT_NUMBER,
1589 p_end_date => lv_end_date) LOOP
1590 lt_bill_smry_gt_tbl(smry_cnt).currency_code := get_currency_code_rec.currency_code;
1591 -- To get total streams total
1592 OPEN get_total_csr(p_org_id => ln_org_id,
1593 p_curr_code => get_currency_code_rec.currency_code,
1594 p_contract_number => P_CONTRACT_NUMBER,
1595 p_end_date => lv_end_date);
1596 FETCH get_total_csr INTO ln_tot_curr_amt;
1597 IF get_total_csr%NOTFOUND THEN
1598 ln_tot_curr_amt := 0;
1599 END IF;
1600 CLOSE get_total_csr;
1601 -- To get billed streams total
1602 OPEN get_billed_csr(p_org_id => ln_org_id,
1603 p_curr_code => get_currency_code_rec.currency_code,
1604 p_contract_number => P_CONTRACT_NUMBER,
1605 p_end_date => lv_end_date);
1606 FETCH get_billed_csr INTO ln_bil_curr_amt;
1607 IF get_billed_csr%NOTFOUND THEN
1608 ln_bil_curr_amt := 0;
1609 END IF;
1610 CLOSE get_billed_csr;
1611 lt_bill_smry_gt_tbl(smry_cnt).total_strm := okl_accounting_util.format_amount(NVL(ln_tot_curr_amt,0),lt_bill_smry_gt_tbl(smry_cnt).currency_code);
1612 lt_bill_smry_gt_tbl(smry_cnt).bill_total_strm := okl_accounting_util.format_amount(NVL(ln_bil_curr_amt,0),lt_bill_smry_gt_tbl(smry_cnt).currency_code);
1613 -- To get differnece in the stream amount
1614 -- to show the difference of Total billable streams and billed streams we do the below
1615 ln_dif_amt1 := ln_tot_curr_amt - ln_bil_curr_amt;
1616 lt_bill_smry_gt_tbl(smry_cnt).diff1_total_strm := okl_accounting_util.format_amount(NVL(ln_dif_amt1,0),lt_bill_smry_gt_tbl(smry_cnt).currency_code);
1617 -- To get cancelled streams total
1618 OPEN get_cancel_csr(p_org_id => ln_org_id,
1619 p_curr_code => get_currency_code_rec.currency_code,
1620 p_contract_number => P_CONTRACT_NUMBER,
1621 p_end_date => lv_end_date);
1622 FETCH get_cancel_csr INTO ln_can_curr_amt;
1623 IF get_cancel_csr%NOTFOUND THEN
1624 ln_can_curr_amt := 0;
1625 END IF;
1626 CLOSE get_cancel_csr;
1627 lt_bill_smry_gt_tbl(smry_cnt).cancel_total_strm := okl_accounting_util.format_amount(NVL(ln_can_curr_amt,0),lt_bill_smry_gt_tbl(smry_cnt).currency_code);
1628 -- to show the difference of 1 difference amount and the cancelled amounts
1629 ln_dif_amt2 := ln_dif_amt1 - ln_can_curr_amt;
1630 lt_bill_smry_gt_tbl(smry_cnt).diff2_total_strm := okl_accounting_util.format_amount(NVL(ln_dif_amt2,0),lt_bill_smry_gt_tbl(smry_cnt).currency_code);
1631 -- To get Closing balance streams total
1632 OPEN get_clobal_csr(p_org_id => ln_org_id,
1633 p_curr_code => get_currency_code_rec.currency_code,
1634 p_contract_number => P_CONTRACT_NUMBER,
1635 p_end_date => lv_end_date);
1636 FETCH get_clobal_csr INTO ln_clb_curr_amt;
1637 IF get_clobal_csr%NOTFOUND THEN
1638 ln_clb_curr_amt := 0;
1639 END IF;
1640 CLOSE get_clobal_csr;
1641 -- To let know the further process that
1642 -- there was value for un billed streams
1643 IF ln_clb_curr_amt <> 0 THEN
1644 lt_value_tbl(a).clb_amt := ln_clb_curr_amt;
1645 END IF;
1646 lt_bill_smry_gt_tbl(smry_cnt).clobal_total_strm := okl_accounting_util.format_amount(NVL(ln_clb_curr_amt,0),lt_bill_smry_gt_tbl(smry_cnt).currency_code);
1647 -- To get differnece in the stream amount
1648 -- the components of these are the total Billable streasm less billed streams
1649 -- To get differnece in the stream amount
1650 -- less cancelled streams and less unbilled streams
1651 -- To get differnece in the stream amount
1652 ln_dif_amt := NVL(ln_tot_curr_amt,0) - NVL(ln_bil_curr_amt,0) - NVL(ln_can_curr_amt,0) - NVL(ln_clb_curr_amt,0);
1653 -- To let know the further process that
1654 -- there was value for Difference streams
1655 IF ln_dif_amt <> 0 THEN
1656 lt_value_tbl(a).dif_amt := ln_dif_amt;
1657 lt_value_tbl(a).curr_code := get_currency_code_rec.currency_code;
1658 ELSIF ln_dif_amt = 0 THEN
1659 lt_value_tbl(a).dif_amt := ln_dif_amt;
1660 lt_value_tbl(a).curr_code := get_currency_code_rec.currency_code;
1661 END IF;
1662 lt_bill_smry_gt_tbl(smry_cnt).main_diff_total := okl_accounting_util.format_amount(NVL(ln_dif_amt,0),lt_bill_smry_gt_tbl(smry_cnt).currency_code);
1663 a := a + 1;
1664 smry_cnt := smry_cnt+1;
1665 END LOOP;
1666 -- Filling in the Details section of the unbilled Streams report file
1667 IF lt_value_tbl.COUNT > 0 THEN
1668 FOR i IN lt_value_tbl.FIRST..lt_value_tbl.LAST LOOP
1669 IF lt_value_tbl(i).clb_amt <> 0 THEN
1670 lv_value_clb := 'Y';
1671 EXIT WHEN (lv_value_clb = 'Y');
1672 ELSIF lt_value_tbl(i).clb_amt = 0 THEN
1673 lv_value_clb := 'N';
1674 END IF;
1675 END LOOP;
1676 END IF;
1677 IF lv_value_clb = 'Y' THEN
1678 -- To get details of Closing balance based on the deal type and
1679 -- Product associated to the same
1680 FOR get_dlts_clobal_rec IN get_dlts_clobal_csr(p_org_id => ln_org_id,
1681 p_contract_number => P_CONTRACT_NUMBER,
1682 p_end_date => lv_end_date) LOOP
1683 lt_unbill_tbl(i).book_class := get_dlts_clobal_rec.book_class;
1684 lt_unbill_tbl(i).currency_code := get_dlts_clobal_rec.currency_code;
1685 lt_unbill_tbl(i).product_name := get_dlts_clobal_rec.product_name;
1686 lt_unbill_tbl(i).amount := get_dlts_clobal_rec.amount;
1687 i := i + 1;
1688 END LOOP;
1689 i := 0;
1690 IF lt_unbill_tbl.COUNT > 0 THEN
1691 FOR j IN lt_unbill_tbl.FIRST..lt_unbill_tbl.LAST LOOP
1692 IF lv_curr_code <> lt_unbill_tbl(j).currency_code THEN
1693 lt_curr_tbl(k) := lt_unbill_tbl(j).currency_code;
1694 lv_curr_code := lt_curr_tbl(k);
1695 k := k + 1;
1696 END IF;
1697 END LOOP;
1698 FOR m IN lt_curr_tbl.FIRST..lt_curr_tbl.LAST LOOP
1699
1700 lv_dlt_tot_amt := NULL;
1701 lv_dlt_amt := NULL;
1702 ln_dlt_tot_amt := 0;
1703 ln_dlt_amt := 0;
1704 FOR j IN lt_unbill_tbl.FIRST..lt_unbill_tbl.LAST LOOP
1705 IF lv_book_class <> lt_unbill_tbl(j).book_class AND
1706 lt_unbill_tbl(j).currency_code = lt_curr_tbl(m) THEN
1707
1708 lv_book_class := lt_unbill_tbl(j).book_class;
1709 ln_dlt_amt := 0;
1710 FOR h IN lt_unbill_tbl.FIRST..lt_unbill_tbl.LAST LOOP
1711 IF lt_unbill_tbl(j).book_class = lt_unbill_tbl(h).book_class AND
1712 lt_unbill_tbl(j).currency_code = lt_curr_tbl(m) THEN
1713
1714 lt_unbill_gt_tbl(ubil_cnt).currency_code := lt_curr_tbl(m);
1715 lt_unbill_gt_tbl(ubil_cnt).book_class := lt_unbill_tbl(j).book_class;
1716 lt_unbill_gt_tbl(ubil_cnt).product_name := substr(lt_unbill_tbl(h).product_name,1,20);
1717 lt_unbill_gt_tbl(ubil_cnt).amount := NVL(lt_unbill_tbl(h).amount,0);
1718 ln_dlt_amt := ln_dlt_amt + lt_unbill_tbl(h).amount;
1719 ln_dlt_tot_amt := ln_dlt_tot_amt + lt_unbill_tbl(h).amount;
1720 ubil_cnt := ubil_cnt + 1;
1721 END IF;
1722 END LOOP;
1723 END IF;
1724 END LOOP;
1725 END LOOP;
1726 END IF;
1727 END IF;
1728 FOR get_diff_rec IN get_diff_csr(p_org_id => ln_org_id,
1729 p_contract_number => P_CONTRACT_NUMBER,
1730 p_end_date => lv_end_date) LOOP
1731 lt_diff_tbl(h).contract_number := get_diff_rec.contract_number;
1732 lt_diff_tbl(h).currency_code := get_diff_rec.currency_code;
1733 lt_diff_tbl(h).total_billable_streams := get_diff_rec.total_billable_streams;
1734 lt_diff_tbl(h).billed_streams := get_diff_rec.billed_streams;
1735 lt_diff_tbl(h).cancelled_streams := get_diff_rec.cancelled_streams;
1736 lt_diff_tbl(h).unbilled_streams := get_diff_rec.unbilled_streams;
1737 lt_diff_tbl(h).amount := get_diff_rec.amount;
1738 h := h + 1;
1739 END LOOP;
1740 IF lt_diff_tbl.COUNT > 0 THEN
1741 lv_contract_number := lt_diff_tbl(lt_diff_tbl.FIRST).contract_number;
1742 FOR i IN lt_diff_tbl.FIRST..lt_diff_tbl.LAST LOOP
1743 IF lv_contract_number = lt_diff_tbl(i).contract_number THEN
1744 lt_diff_tbl_1(m).contract_number := lt_diff_tbl(i).contract_number;
1745 lt_diff_tbl_1(m).currency_code := lt_diff_tbl(i).currency_code;
1746 IF lt_diff_tbl(i).total_billable_streams = 'Y' THEN
1747 lt_diff_tbl_1(m).total_billable_streams := TO_CHAR(lt_diff_tbl(i).amount);
1748 END IF;
1749 IF lt_diff_tbl(i).billed_streams = 'Y' THEN
1750 lt_diff_tbl_1(m).billed_streams := TO_CHAR(lt_diff_tbl(i).amount);
1751 END IF;
1752 IF lt_diff_tbl(i).cancelled_streams = 'Y' THEN
1753 lt_diff_tbl_1(m).cancelled_streams := TO_CHAR(lt_diff_tbl(i).amount);
1754 END IF;
1755 IF lt_diff_tbl(i).unbilled_streams = 'Y' THEN
1756 lt_diff_tbl_1(m).unbilled_streams := TO_CHAR(lt_diff_tbl(i).amount);
1757 END IF;
1758 ELSIF lv_contract_number <> lt_diff_tbl(i).contract_number THEN
1759 m := m + 1;
1760 lv_contract_number := lt_diff_tbl(i).contract_number;
1761 lt_diff_tbl_1(m).contract_number := lt_diff_tbl(i).contract_number;
1762 lt_diff_tbl_1(m).currency_code := lt_diff_tbl(i).currency_code;
1763 IF lt_diff_tbl(i).total_billable_streams = 'Y' THEN
1764 lt_diff_tbl_1(m).total_billable_streams := TO_CHAR(lt_diff_tbl(i).amount);
1765 END IF;
1766 IF lt_diff_tbl(i).billed_streams = 'Y' THEN
1767 lt_diff_tbl_1(m).billed_streams := TO_CHAR(lt_diff_tbl(i).amount);
1768 END IF;
1769 IF lt_diff_tbl(i).cancelled_streams = 'Y' THEN
1770 lt_diff_tbl_1(m).cancelled_streams := TO_CHAR(lt_diff_tbl(i).amount);
1771 END IF;
1772 IF lt_diff_tbl(i).unbilled_streams = 'Y' THEN
1773 lt_diff_tbl_1(m).unbilled_streams := TO_CHAR(lt_diff_tbl(i).amount);
1774 END IF;
1775 END IF;
1776 END LOOP;
1777 FOR j IN lt_diff_tbl_1.FIRST..lt_diff_tbl_1.LAST LOOP
1778 IF lv_curr_dif_code <> lt_diff_tbl_1(j).currency_code THEN
1779 lt_curr_tbl_1(c) := lt_diff_tbl_1(j).currency_code;
1780 lv_curr_dif_code := lt_curr_tbl_1(c);
1781 c := c + 1;
1782 END IF;
1783 END LOOP;
1784 FOR j IN lt_curr_tbl_1.FIRST..lt_curr_tbl_1.LAST LOOP
1785 ln_diff_dlts_amt := 0;
1786 lv_diff_dlts_amt := NULL;
1787 lv_value_dif := NULL;
1788 IF lt_value_tbl.COUNT > 0 THEN
1789 FOR i IN lt_value_tbl.FIRST..lt_value_tbl.LAST LOOP
1790 IF lt_value_tbl(i).dif_amt <> 0 AND
1791 lt_value_tbl(i).curr_code = lt_curr_tbl_1(j) THEN
1792 lv_value_dif := 'Y';
1793 EXIT WHEN (lv_value_dif = 'Y');
1794 ELSIF lt_value_tbl(i).dif_amt = 0 AND
1795 lt_value_tbl(i).curr_code = lt_curr_tbl_1(j) THEN
1796 lv_value_dif := 'N';
1797 END IF;
1798 END LOOP;
1799 END IF;
1800 IF lv_value_dif = 'Y' THEN
1801
1802 FOR i IN lt_diff_tbl_1.FIRST..lt_diff_tbl_1.LAST LOOP
1803 IF lt_diff_tbl_1(i).currency_code = lt_curr_tbl_1(j) THEN
1804 lt_diff_tbl_1(i).diff_amount := NVL(TO_NUMBER(lt_diff_tbl_1(i).total_billable_streams),0) -
1805 (NVL(TO_NUMBER(lt_diff_tbl_1(i).billed_streams),0) +
1806 NVL(TO_NUMBER(lt_diff_tbl_1(i).cancelled_streams),0) +
1807 NVL(TO_NUMBER(lt_diff_tbl_1(i).unbilled_streams),0));
1808 IF lt_diff_tbl_1(i).diff_amount <> 0 THEN
1809 lt_diff_gt_tbl(diff_cnt).currency_code := lt_curr_tbl_1(j);
1810 lt_diff_gt_tbl(diff_cnt).contract_number := lt_diff_tbl_1(i).contract_number;
1811 lt_diff_gt_tbl(diff_cnt).total_billable_streams := NVL(TO_NUMBER(lt_diff_tbl_1(i).total_billable_streams),0);
1812 lt_diff_gt_tbl(diff_cnt).billed_streams := NVL(TO_NUMBER(lt_diff_tbl_1(i).billed_streams),0);
1813 lt_diff_gt_tbl(diff_cnt).cancelled_streams := NVL(TO_NUMBER(lt_diff_tbl_1(i).cancelled_streams),0);
1814 lt_diff_gt_tbl(diff_cnt).unbilled_streams := NVL(TO_NUMBER(lt_diff_tbl_1(i).unbilled_streams),0);
1815 lt_diff_gt_tbl(diff_cnt).diff_amount := NVL(lt_diff_tbl_1(i).diff_amount,0);
1816 ln_diff_dlts_amt := ln_diff_dlts_amt + lt_diff_tbl_1(i).diff_amount;
1817 diff_cnt := diff_cnt + 1;
1818 END IF;
1819 END IF;
1820 END LOOP;
1821 END IF;
1822 END LOOP;
1823 END IF;
1824 IF lt_bill_smry_gt_tbl.COUNT > 0 THEN --Condition Added by varangan for bug# 5738018
1825 FOR i IN lt_bill_smry_gt_tbl.FIRST..lt_bill_smry_gt_tbl.LAST LOOP
1826 INSERT INTO
1827 OKL_G_REPORTS_GT(VALUE1_TEXT,
1828 VALUE2_TEXT,
1829 VALUE3_TEXT,
1830 VALUE4_TEXT,
1831 VALUE5_TEXT,
1832 VALUE6_TEXT,
1833 VALUE7_TEXT,
1834 VALUE8_TEXT,
1835 VALUE9_TEXT)
1836 VALUES
1837 ('SUMMARY',
1838 lt_bill_smry_gt_tbl(i).currency_code,
1839 lt_bill_smry_gt_tbl(i).total_strm,
1840 lt_bill_smry_gt_tbl(i).bill_total_strm,
1841 lt_bill_smry_gt_tbl(i).diff1_total_strm,
1842 lt_bill_smry_gt_tbl(i).cancel_total_strm,
1843 lt_bill_smry_gt_tbl(i).diff2_total_strm,
1844 lt_bill_smry_gt_tbl(i).clobal_total_strm,
1845 lt_bill_smry_gt_tbl(i).main_diff_total
1846 );
1847 END LOOP;
1848 END IF;
1849 IF lt_unbill_gt_tbl.COUNT > 0 THEN -- Condition Added by varangan for bug# 5738018
1850 FOR i IN lt_unbill_gt_tbl.FIRST..lt_unbill_gt_tbl.LAST LOOP
1851 INSERT INTO
1852 OKL_G_REPORTS_GT(VALUE1_TEXT,
1853 VALUE2_TEXT,
1854 VALUE3_TEXT,
1855 VALUE4_TEXT,
1856 VALUE5_TEXT,
1857 VALUE1_NUM)
1858 VALUES
1859 ('UNBILLED_DTLS',
1860 lt_unbill_gt_tbl(i).currency_code,
1861 lt_unbill_gt_tbl(i).book_class,
1862 lt_unbill_gt_tbl(i).product_name,
1863 okl_accounting_util.format_amount(lt_unbill_gt_tbl(i).amount,lt_unbill_gt_tbl(i).currency_code),
1864 lt_unbill_gt_tbl(i).amount
1865 );
1866 END LOOP;
1867 END IF;
1868 IF lt_diff_gt_tbl.COUNT > 0 THEN -- Condition Added by varangan for bug# 5738018
1869 FOR i IN lt_diff_gt_tbl.FIRST..lt_diff_gt_tbl.LAST LOOP
1870 INSERT INTO
1871 OKL_G_REPORTS_GT(VALUE1_TEXT,
1872 VALUE2_TEXT,
1873 VALUE3_TEXT,
1874 VALUE4_TEXT,
1875 VALUE5_TEXT,
1876 VALUE6_TEXT,
1877 VALUE7_TEXT,
1878 VALUE8_TEXT,
1879 VALUE1_NUM,
1880 VALUE2_NUM,
1881 VALUE3_NUM,
1882 VALUE4_NUM,
1883 VALUE5_NUM)
1884 VALUES
1885 ('DIFF_DTLS',
1886 lt_diff_gt_tbl(i).currency_code,
1887 lt_diff_gt_tbl(i).contract_number,
1888 okl_accounting_util.format_amount(lt_diff_gt_tbl(i).total_billable_streams,lt_diff_gt_tbl(i).currency_code),
1889 okl_accounting_util.format_amount(lt_diff_gt_tbl(i).billed_streams,lt_diff_gt_tbl(i).currency_code),
1890 okl_accounting_util.format_amount(lt_diff_gt_tbl(i).cancelled_streams,lt_diff_gt_tbl(i).currency_code),
1891 okl_accounting_util.format_amount(lt_diff_gt_tbl(i).unbilled_streams,lt_diff_gt_tbl(i).currency_code),
1892 okl_accounting_util.format_amount(lt_diff_gt_tbl(i).diff_amount,lt_diff_gt_tbl(i).currency_code),
1893 lt_diff_gt_tbl(i).total_billable_streams,
1894 lt_diff_gt_tbl(i).billed_streams,
1895 lt_diff_gt_tbl(i).cancelled_streams,
1896 lt_diff_gt_tbl(i).unbilled_streams,
1897 lt_diff_gt_tbl(i).diff_amount
1898 );
1899 END LOOP;
1900 END IF;
1901 okl_api.end_activity(l_msg_count, l_msg_data);
1902 p_retcode := 0;
1903 RETURN TRUE;
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 p_errbuf := SQLERRM;
1907 p_retcode := 2;
1908 IF get_org_id%ISOPEN THEN
1909 CLOSE get_org_id;
1910 END IF;
1911 IF get_currency_code%ISOPEN THEN
1912 CLOSE get_currency_code;
1913 END IF;
1914 IF get_total_csr%ISOPEN THEN
1915 CLOSE get_total_csr;
1916 END IF;
1917 IF get_billed_csr%ISOPEN THEN
1918 CLOSE get_billed_csr;
1919 END IF;
1920 IF get_cancel_csr%ISOPEN THEN
1921 CLOSE get_cancel_csr;
1922 END IF;
1923 IF get_clobal_csr%ISOPEN THEN
1924 CLOSE get_clobal_csr;
1925 END IF;
1926 IF get_dlts_clobal_csr%ISOPEN THEN
1927 CLOSE get_dlts_clobal_csr;
1928 END IF;
1929 IF get_diff_csr%ISOPEN THEN
1930 CLOSE get_diff_csr;
1931 END IF;
1932 x_return_status := okl_api.handle_exceptions(
1933 l_api_name,
1934 g_pkg_name,
1935 'OTHERS',
1936 l_msg_count,
1937 l_msg_data,
1938 '_PVT');
1939 -- print the error message in the log file
1940 okl_accounting_util.get_error_message(l_error_msg_rec);
1941 IF (l_error_msg_rec.COUNT > 0) THEN
1942 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST LOOP
1943 fnd_file.put_line(fnd_file.log, l_error_msg_rec(i));
1944 END LOOP;
1945 END IF;
1946 fnd_file.put_line(fnd_file.log, SQLERRM);
1947 END xml_recon_qry;
1948 END OKL_STREAMS_RECON_PVT;
[Home] [Help]