[Home] [Help]
PACKAGE BODY: APPS.OKL_BPD_TERMINATION_ADJ_PVT
Source
1 PACKAGE BODY OKL_BPD_TERMINATION_ADJ_PVT AS
2 /* $Header: OKLRBAJB.pls 120.17 2007/04/03 23:16:53 rmunjulu noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7 ---------------------------------------------------------------------------
8 -- Procedures and Functions
9 ---------------------------------------------------------------------------
10
11
12 PROCEDURE create_debit_memo(
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
15 p_sel_id IN NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2) IS
19
20 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
21 l_api_version CONSTANT NUMBER := 1;
22 l_api_name CONSTANT VARCHAR2(30) := 'create_debit_memo';
23
24 -----------------------------------------------------------------
25 -- Declare Process Variable
26 -----------------------------------------------------------------
27 l_okl_application_id NUMBER(3) := 540;
28 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
29 lx_dbseqnm VARCHAR2(2000):= '';
30 lx_dbseqid NUMBER(38):= NULL;
31
32 ------------------------------------------------------------
33 -- Declare records: Payable Invoice Headers, Lines and Distributions
34 ------------------------------------------------------------
35 l_tapv_rec okl_tap_pvt.tapv_rec_type;
36 lx_tapv_rec okl_tap_pvt.tapv_rec_type;
37 l_tplv_rec okl_tpl_pvt.tplv_rec_type;
38 lx_tplv_rec okl_tpl_pvt.tplv_rec_type;
39
40 /* ankushar 22-JAN-2007
41 added table definitions
42 start changes
43 */
44 l_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
45 lx_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
46 /* ankushar end changes*/
47
48 l_tmpl_identify_rec Okl_Account_Dist_Pvt.TMPL_IDENTIFY_REC_TYPE;
49 l_dist_info_rec Okl_Account_Dist_Pvt.dist_info_REC_TYPE;
50 l_ctxt_val_tbl okl_execute_formula_pvt.ctxt_val_tbl_type;
51 l_acc_gen_primary_key_tbl Okl_Account_Generator_Pvt.primary_key_tbl;
52 l_template_tbl OKL_TMPT_SET_PUB.avlv_tbl_type;
53 l_amount_tbl Okl_Account_Dist_Pvt.AMOUNT_TBL_TYPE;
54
55 lu_tapv_rec Okl_tap_pvt.tapv_rec_type;
56 lux_tapv_rec Okl_tap_pvt.tapv_rec_type;
57
58 -- sjalasut, commented the subtype declaration. changed the usages to point
59 -- to the table.column names.
60 -- SUBTYPE khr_id_type IS okl_k_headers_full_v.id%type;
61 l_khr_id okc_k_headers_b.id%TYPE;
62 l_currency_code okl_k_headers_full_v.currency_code%type;
63 l_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%type;
64 l_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%type;
65 l_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%type;
66
67 l_created_tap BOOLEAN := TRUE;
68 l_created_tpl BOOLEAN := TRUE;
69 l_created_dist BOOLEAN := TRUE;
70
71 -- Update Pay Status
72 -- u_lsmv_rec Okl_Cnsld_Ar_Strms_Pub.lsmv_rec_type; -- rmunjulu R12 Billing Fixes: commented
73 -- r_lsmv_rec Okl_Cnsld_Ar_Strms_Pub.lsmv_rec_type; -- rmunjulu R12 Billing Fixes: commented
74
75 -- rmunjulu R12 Billing Fixes: added new variables
76 l_tldv_rec okl_txd_ar_ln_dtls_pub.tldv_rec_type;
77 lx_tldv_rec okl_txd_ar_ln_dtls_pub.tldv_rec_type;
78
79 CURSOR pdt_id_csr (p_khr_id NUMBER) IS
80 SELECT khr.pdt_id
81 FROM okl_k_headers khr
82 WHERE khr.id = p_khr_id;
83
84 -- sjalasut, modified the below cursor to have khr_id be picked up from
85 -- okl_txl_ap_inv_lns_v instead of okl_trx_ap_invoices_v
86 -- changes made as part of OKLR12B disbursements project.
87 CURSOR l_ap_inv_csr(cp_sel_id IN NUMBER) IS
88 select tap.id tap_id
89 ,tap.nettable_yn
90 ,tap.wait_vendor_invoice_yn
91 ,tap.ipvs_id
92 ,tap.payment_method_code
93 ,tap.ippt_id
94 ,tap.pay_group_lookup_code
95 ,tap.try_id
96 ,tap.set_of_books_id
97 ,tap.org_id tap_org
98 ,tpl.khr_id
99 ,tap.currency_code
100 ,tap.currency_conversion_date
101 ,tap.currency_conversion_rate
102 ,tap.currency_conversion_type
103 ,tap.trx_status_code
104 ,tap.date_invoiced
105 ,tap.date_entered
106 ,tap.workflow_yn
107 ,tap.invoice_type
108 ,tap.amount tap_amount
109 ,tap.invoice_number
110 ,tap.invoice_category_code
111 ,tap.vendor_invoice_number
112 ,tap.date_gl
113 ,tpl.id
114 ,tpl.disbursement_basis_code
115 ,tpl.amount
116 ,tpl.org_id
117 ,tpl.kle_id
118 ,tpl.lsm_id
119 ,tpl.sty_id
120 ,tpl.line_number
121 ,tpl.inv_distr_line_code
122 ,tld.id tld_id -- rmunjulu R12 Billing Fixes: added
123 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
124 ,tap.legal_entity_id
125 from -- okl_cnsld_ar_strms_v lsm -- rmunjulu R12 Billing Fixes: commented
126 okl_trx_ap_invoices_v tap
127 , okl_txl_ap_inv_lns_v tpl
128 , okl_txd_ar_ln_dtls_b tld -- rmunjulu R12 Billing Fixes: added
129 where tap.id = tpl.tap_id
130 -- and tpl.lsm_id = lsm.id -- rmunjulu R12 Billing Fixes: commented
131 and tpl.tld_id = tld.id -- rmunjulu R12 Billing Fixes: added
132 and tld.sel_id = cp_sel_id -- rmunjulu R12 Billing Fixes: changed to use TLD
133 and tld.pay_status_code = 'PROCESSED' -- rmunjulu R12 Billing Fixes: changed to use TLD
134 and tld.amount > 0 -- rmunjulu R12 Billing Fixes: changed to use TLD
135 and tap.trx_status_code in ('ENTERED', 'APPROVED', 'PROCESSED');
136
137 CURSOR try_id_csr IS
138 SELECT id
139 FROM okl_trx_types_tl
140 WHERE name = 'Debit Memo'
141 AND language= 'US';
142
143 --Get currency conversion attributes for a contract
144 CURSOR l_curr_conv_csr(cp_khr_id IN okc_k_headers_b.id%TYPE) IS
145 SELECT currency_code
146 ,currency_conversion_type
147 ,currency_conversion_rate
148 ,currency_conversion_date
149 FROM okl_k_headers_full_v
150 WHERE id = cp_khr_id;
151
152 cnt NUMBER;
153 BEGIN
154 IF (G_DEBUG_ENABLED = 'Y') THEN
155 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
156 END IF;
157 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
158 G_PKG_NAME,
159 p_init_msg_list,
160 l_api_version,
161 p_api_version,
162 '_PVT',
163 l_return_status);
164
165 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
166 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
167 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
168 RAISE OKL_API.G_EXCEPTION_ERROR;
169 END IF;
170
171 -- Processing starts
172
173 FOR cur IN l_ap_inv_csr(p_sel_id) LOOP
174 -- sjalasut, added code to store khr_id in a local variable so that
175 -- this local variable could be used in cursor and other assignments.
176 l_khr_id := cur.khr_id ;
177
178 ------------------------------------------------------------
179 -- Insert Invoice Header
180 ------------------------------------------------------------
181 l_tapv_rec.tap_id_reverses := cur.tap_id;
182 l_tapv_rec.nettable_yn := cur.nettable_yn;
183 l_tapv_rec.ipvs_id := cur.ipvs_id;
184 l_tapv_rec.payment_method_code := cur.payment_method_code;
185 l_tapv_rec.ippt_id := cur.ippt_id;
186 l_tapv_rec.pay_group_lookup_code := cur.pay_group_lookup_code;
187
188 OPEN try_id_csr;
189 FETCH try_id_csr INTO l_tapv_rec.try_id;
190 CLOSE try_id_csr;
191
192 l_tapv_rec.set_of_books_id := cur.set_of_books_id;
193 l_tapv_rec.org_id := cur.tap_org;
194 -- sjalasut, commenting the below assignment because khr_id would now be
195 -- captured at the tplv_rec (internal disbursement transaction lines entity)
196 -- changes made as part of OKLR12B disbursements project
197 -- l_tapv_rec.khr_id := cur.khr_id;
198 l_tapv_rec.khr_id := NULL;
199 l_tapv_rec.currency_code := cur.currency_code;
200 l_tapv_rec.currency_conversion_date := cur.currency_conversion_date;
201 l_tapv_rec.currency_conversion_rate := cur.currency_conversion_rate;
202 l_tapv_rec.currency_conversion_type := cur.currency_conversion_type;
203 l_tapv_rec.trx_status_code := 'ENTERED';
204 l_tapv_rec.date_invoiced := trunc(SYSDATE);
205 l_tapv_rec.date_entered := trunc(SYSDATE);
206 l_tapv_rec.invoice_type := 'CREDIT';
207 l_tapv_rec.amount := cur.tap_amount;
208 l_tapv_rec.workflow_yn := cur.workflow_yn;
209 l_tapv_rec.wait_vendor_invoice_yn := cur.wait_vendor_invoice_yn;
210 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
211 l_tapv_rec.legal_entity_id := cur.legal_entity_id;
212
213 l_tapv_rec.invoice_number := fnd_seqnum.get_next_sequence
214 (appid => l_okl_application_id,
215 cat_code => l_document_category,
216 sobid => l_tapv_rec.set_of_books_id,
217 met_code => 'A',
218 trx_date => SYSDATE,
219 dbseqnm => lx_dbseqnm,
220 dbseqid => lx_dbseqid);
221
222 l_tapv_rec.vendor_invoice_number := l_tapv_rec.invoice_number;
223
224 l_tapv_rec.invoice_category_code := cur.invoice_category_code;
225 l_tapv_rec.date_gl := l_tapv_rec.date_invoiced;
226
227 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
228 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'before create disbursement transactions');
229 END IF;
230
231 ------------------------------------------------------------
232 -- Insert Invoice Line
233 ------------------------------------------------------------
234 l_tplv_rec.amount := cur.amount;
235 l_tplv_rec.org_id := cur.org_id;
236 l_tplv_rec.kle_id := cur.kle_id;
237 l_tplv_rec.sty_id := cur.sty_id;
238 l_tplv_rec.line_number := 1;
239 -- sjalasut, added khr_id assignment to l_tplv_rec.
240 -- changes made as part of OKLR12B disbursements project
241 l_tplv_rec.khr_id := l_khr_id;
242
243 /* ankushar 23-JAN-2007
244 Call to the common Disbursement API
245 start changes */
246
247 -- Add tpl_rec to table
248 l_tplv_tbl(1) := l_tplv_rec;
249
250 --Call the commong disbursement API to create transactions
251 Okl_Create_Disb_Trans_Pvt.create_disb_trx(
252 p_api_version => p_api_version
253 ,p_init_msg_list => p_init_msg_list
254 ,x_return_status => x_return_status
255 ,x_msg_count => x_msg_count
256 ,x_msg_data => x_msg_data
257 ,p_tapv_rec => l_tapv_rec
258 ,p_tplv_tbl => l_tplv_tbl
259 ,x_tapv_rec => lx_tapv_rec
260 ,x_tplv_tbl => lx_tplv_tbl);
261
262 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
263 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
264 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
265 l_created_tpl := FALSE;
266 END IF;
267
268 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
269 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'after create disbursement transaction, ap invoice header id : ' || lx_tapv_rec.id);
270 END IF;
271 /* ankushar end changes */
272
273 --update lsm.pay_status_code with a status of REVERSED
274 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
275 -- OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'evaluating whether to update lsm with Reversed status'); -- rmunjulu R12 Billing Fixes: commented
276 -- rmunjulu R12 Billing Fixes
277 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'evaluating whether to update TLD with Reversed status');
278 END IF;
279 IF (l_created_tap AND l_created_tpl AND l_created_dist) THEN
280 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
281 -- OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'before updating lsm with Reversed status'); -- rmunjulu R12 Billing Fixes: commented
282 -- rmunjulu R12 Billing Fixes
283 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'before updating TLD with Reversed status');
284 END IF;
285
286 /* rmunjulu R12 Billing Fixes -- commented below code
287 u_lsmv_rec.id := cur.lsm_id;
288 u_lsmv_rec.pay_status_code := 'REVERSED';
289
290 Okl_Cnsld_Ar_Strms_Pub.update_cnsld_ar_strms
291 (p_api_version
292 ,p_init_msg_list
293 ,l_return_status
294 ,x_msg_count
295 ,x_msg_data
296 ,u_lsmv_rec
297 ,r_lsmv_rec);
298 */
299 -- rmunjulu R12 Billing Fixes : start
300 l_tldv_rec.id := cur.tld_id;
301 l_tldv_rec.pay_status_code := 'REVERSED'; -- set the tld record to REVERSED status
302
303 okl_txd_ar_ln_dtls_pub.update_txd_ar_ln_dtls
304 (p_api_version => p_api_version
305 ,p_init_msg_list => p_init_msg_list
306 ,x_return_status => l_return_status
307 ,x_msg_count => x_msg_count
308 ,x_msg_data => x_msg_data
309 ,p_tldv_rec => l_tldv_rec
310 ,x_tldv_rec => lx_tldv_rec);
311
312 -- rmunjulu R12 Billing Fixes : end
313 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
314 -- OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'after updating lsm with Reversed status, Status : ' || l_return_status); -- rmunjulu R12 Billing Fixes: commented
315 -- rmunjulu R12 Billing Fixes
316 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'after updating TLD with Reversed status, Status : ' || l_return_status);
317 END IF;
318 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
319 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
320 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
321 RAISE OKL_API.G_EXCEPTION_ERROR;
322 END IF;
323 END IF;
324 END LOOP;
325
326 -- Processing ends
327
328 x_return_status := l_return_status;
329 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
330 EXCEPTION
331 WHEN OKL_API.G_EXCEPTION_ERROR THEN
332 x_return_status := OKL_API.G_RET_STS_ERROR;
333 WHEN OTHERS THEN
334 x_return_status := OKL_API.HANDLE_EXCEPTIONS
335 (
336 l_api_name,
337 G_PKG_NAME,
338 'OTHERS',
339 x_msg_count,
340 x_msg_data,
341 '_PVT'
342 );
343 END create_debit_memo;
344
345 ---------------------------------------------------------------------------
346
347 FUNCTION get_kle_status_code(p_kle_id IN NUMBER) RETURN VARCHAR2 IS
348
349 l_status_code OKC_STATUSES_B.CODE%TYPE := NULL;
350
351 cursor l_kle_status_code_csr(cp_kle_id IN NUMBER) IS SELECT ste_code
352 FROM okc_k_lines_b kle,
353 okc_statuses_b kls
354 WHERE kle.id = cp_kle_id
355 AND kle.sts_code = kls.code;
356 BEGIN
357 if (p_kle_id IS NOT NULL) THEN
358 open l_kle_status_code_csr(p_kle_id);
359 fetch l_kle_status_code_csr INTO l_status_code;
360 close l_kle_status_code_csr;
361 else
362 l_status_code := 'HEADER_LEVEL';
363 end if;
364
365 RETURN l_status_code;
366 EXCEPTION
367 WHEN OTHERS THEN
368 RETURN l_status_code;
369 END get_kle_status_code;
370
371 ---------------------------------------------------------------------------
372
373 PROCEDURE get_billing_adjust(
374 p_api_version IN NUMBER,
375 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
376 p_input_tbl IN input_tbl_type,
377 x_baj_tbl OUT NOCOPY baj_tbl_type,
378 x_return_status OUT NOCOPY VARCHAR2,
379 x_msg_count OUT NOCOPY NUMBER,
380 x_msg_data OUT NOCOPY VARCHAR2) IS
381
382 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
383 l_api_version CONSTANT NUMBER := 1;
384 l_api_name CONSTANT VARCHAR2(30) := 'get_billing_adjust';
385
386 l_input_rec input_rec_type;
387 l_input_tbl input_tbl_type;
388
389 l_baj_rec baj_rec_type;
390 lx_baj_tbl baj_tbl_type;
391
392 input_cnt NUMBER;
393 baj_cnt NUMBER := 0;
394
395 ------------------------------------------------
396 --Billed Receivables for Credit Memo Application
397 --For prior dated termination
398 ------------------------------------------------
399 cursor l_bill_adj_csr(cp_khr_id IN NUMBER, cp_kle_id IN NUMBER, cp_term_date_from IN DATE, cp_term_date_to IN DATE) IS
400 SELECT stm.khr_id khr_id,
401 stm.kle_id kle_id,
402 TRUNC (ste.stream_element_date) stream_element_date,
403 ste.id sel_id,
404 stm.id stm_id,
405 stm.sty_id sty_id,
406 sty.name sty_name,
407 ste.amount,
408 ste.se_line_number,
409 ste.source_id,
410 ste.source_table
411 FROM okl_strm_elements ste,
412 okl_streams stm,
413 okl_strm_type_v sty,
414 okc_k_headers_b khr,
415 okl_k_headers khl,
416 okc_k_lines_b kle,
417 okc_statuses_b khs
418 WHERE trunc(ste.stream_element_date) > trunc(nvl(cp_term_date_from, ste.stream_element_date))
419 AND trunc(ste.stream_element_date) <= trunc(nvl(cp_term_date_to, ste.stream_element_date))
420 AND ste.amount <> 0
421 AND stm.id = ste.stm_id
422 AND ste.date_billed IS NOT NULL
423 AND stm.active_yn = 'Y'
424 AND stm.say_code = 'CURR'
425 AND sty.id = stm.sty_id
426 AND sty.billable_yn = 'Y'
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 khr.id = cp_khr_id
431 AND khl.id = stm.khr_id
432 AND khl.deal_type IS NOT NULL
433 AND khs.code = khr.sts_code
434 AND khs.ste_code = 'ACTIVE'
435 AND stm.kle_id = kle.id(+)
436 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, nvl(stm.kle_id, -99))
437 AND OKL_BPD_TERMINATION_ADJ_PVT.get_kle_status_code(stm.kle_id) IN ('ACTIVE', 'TERMINATED', 'HEADER_LEVEL')
438 ORDER BY 1, 2, 3;
439 -- akrangan bug 5655680 -- start
440 --cursor to check if financial asset appears as linked asset
441 CURSOR l_lnk_ast_csr (p_line_id OKC_K_LINES_B.ID%TYPE) IS
442 Select lnk.id link_kle_id
443 --lnk.cle_id link_kle_id
444 From okc_k_lines_b lnk,
445 okc_line_styles_b lnk_lse,
446 okc_statuses_b sts,
447 okc_k_items cim
448 Where lnk.id = cim.cle_id
449 and lnk.dnz_chr_id = cim.dnz_chr_id
450 and lnk.lse_id = lnk_lse.id
451 and lnk_lse.lty_code in ('LINK_FEE_ASSET','LINK_SERV_ASSET')
452 and sts.code = lnk.sts_code
453 and sts.ste_code not in ('EXPIRED','TERMINATED','CANCELLED')
454 and cim.jtot_object1_code = 'OKX_COVASST'
455 and cim.object1_id1 = to_char(p_line_id)
456 and cim.object1_id2 = '#';
457 --akrangan bug 5655680 -- end
458 BEGIN
459 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
460 G_PKG_NAME,
461 p_init_msg_list,
462 l_api_version,
463 p_api_version,
464 '_PVT',
465 l_return_status);
466
467 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
468 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
469 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
470 RAISE OKL_API.G_EXCEPTION_ERROR;
471 END IF;
472
473 -- Processing starts
474 l_input_tbl := p_input_tbl;
475
476 input_cnt := l_input_tbl.FIRST;
477 WHILE (input_cnt IS NOT NULL)
478 LOOP
479 l_input_rec := l_input_tbl(input_cnt);
480
481 FOR cur_bill_adj IN l_bill_adj_csr(l_input_rec.khr_id, l_input_rec.kle_id, l_input_rec.term_date_from, l_input_rec.term_date_to) LOOP
482 baj_cnt := baj_cnt + 1;
483 lx_baj_tbl(baj_cnt) := cur_bill_adj;
484 END LOOP;
485 --akrangan Bug 5655680 start --
486 FOR l_lnk_ast IN l_lnk_ast_csr(l_input_rec.kle_id) LOOP
487 FOR cur_bill_adj IN l_bill_adj_csr(l_input_rec.khr_id, l_lnk_ast.link_kle_id, l_input_rec.term_date_from, l_input_rec.term_date_to) LOOP
488 baj_cnt := baj_cnt + 1;
489 lx_baj_tbl(baj_cnt) := cur_bill_adj;
490 END LOOP;
491 END LOOP;
492 --akrangan Bug 5655680 end --
493 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
494 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
495 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
496 RAISE okl_api.G_EXCEPTION_ERROR;
497 END IF;
498
499 input_cnt := l_input_tbl.NEXT(input_cnt);
500 END LOOP;
501
502 -- Processing ends
503 x_baj_tbl := lx_baj_tbl;
504 x_return_status := l_return_status;
505
506 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
507 EXCEPTION
508 WHEN OKL_API.G_EXCEPTION_ERROR THEN
509 x_return_status := OKL_API.G_RET_STS_ERROR;
510 WHEN OTHERS THEN
511 x_return_status := OKL_API.HANDLE_EXCEPTIONS
512 (
513 l_api_name,
514 G_PKG_NAME,
515 'OTHERS',
516 x_msg_count,
517 x_msg_data,
518 '_PVT'
519 );
520 END get_billing_adjust;
521
522 ---------------------------------------------------------------------------
523
524 PROCEDURE get_unbilled_recvbl(
525 p_api_version IN NUMBER,
526 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
527 p_input_tbl IN input_tbl_type,
528 x_baj_tbl OUT NOCOPY baj_tbl_type,
529 x_return_status OUT NOCOPY VARCHAR2,
530 x_msg_count OUT NOCOPY NUMBER,
531 x_msg_data OUT NOCOPY VARCHAR2) IS
532
533 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
534 l_api_version CONSTANT NUMBER := 1;
535 l_api_name CONSTANT VARCHAR2(30) := 'get_unbilled_recvbl';
536
537 l_input_rec input_rec_type;
538 l_input_tbl input_tbl_type;
539
540 l_baj_rec baj_rec_type;
541 lx_baj_tbl baj_tbl_type;
542
543 input_cnt NUMBER;
544 baj_cnt NUMBER := 0;
545
546 ------------------------------------------------
547 --Un-Billed Receivables for invoices
548 --For future dated termination
549 ------------------------------------------------
550 cursor l_bill_adj_csr(cp_khr_id IN NUMBER, cp_kle_id IN NUMBER, cp_term_date_from IN DATE, cp_term_date_to IN DATE) IS
551 SELECT stm.khr_id khr_id,
552 stm.kle_id kle_id,
553 TRUNC (ste.stream_element_date) stream_element_date,
554 ste.id sel_id,
555 stm.id stm_id,
556 stm.sty_id sty_id,
557 sty.name sty_name,
558 ste.amount,
559 ste.se_line_number,
560 ste.source_id,
561 ste.source_table
562 FROM okl_strm_elements ste,
563 okl_streams stm,
564 okl_strm_type_v sty,
565 okc_k_headers_b khr,
566 okl_k_headers khl,
567 okc_k_lines_b kle,
568 okc_statuses_b khs
569 WHERE trunc(ste.stream_element_date) >= trunc(nvl(cp_term_date_from, ste.stream_element_date))
570 AND trunc(ste.stream_element_date) <= trunc(nvl(cp_term_date_to, ste.stream_element_date))
571 AND ste.amount <> 0
572 AND stm.id = ste.stm_id
573 AND ste.date_billed IS NULL
574 AND stm.active_yn = 'Y'
575 AND stm.say_code = 'CURR'
576 AND sty.id = stm.sty_id
577 AND sty.billable_yn = 'Y'
578 AND khr.id = stm.khr_id
579 AND khr.scs_code IN ('LEASE', 'LOAN')
580 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
581 AND khr.id = cp_khr_id
582 AND khl.id = stm.khr_id
583 AND khl.deal_type IS NOT NULL
584 AND khs.code = khr.sts_code
585 AND khs.ste_code = 'ACTIVE'
586 AND stm.kle_id = kle.id(+)
587 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, nvl(stm.kle_id, -99))
588 AND OKL_BPD_TERMINATION_ADJ_PVT.get_kle_status_code(stm.kle_id) IN ('ACTIVE', 'TERMINATED', 'HEADER_LEVEL')
589 ORDER BY 1, 2, 3;
590 BEGIN
591 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
592 G_PKG_NAME,
593 p_init_msg_list,
594 l_api_version,
595 p_api_version,
596 '_PVT',
597 l_return_status);
598
599 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
600 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
601 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
602 RAISE OKL_API.G_EXCEPTION_ERROR;
603 END IF;
604
605 -- Processing starts
606 l_input_tbl := p_input_tbl;
607
608 input_cnt := l_input_tbl.FIRST;
609 WHILE (input_cnt IS NOT NULL)
610 LOOP
611 l_input_rec := l_input_tbl(input_cnt);
612
613 FOR cur_bill_adj IN l_bill_adj_csr(l_input_rec.khr_id, l_input_rec.kle_id, l_input_rec.term_date_from, l_input_rec.term_date_to) LOOP
614 baj_cnt := baj_cnt + 1;
615 lx_baj_tbl(baj_cnt) := cur_bill_adj;
616 END LOOP;
617
618 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
619 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
620 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
621 RAISE okl_api.G_EXCEPTION_ERROR;
622 END IF;
623
624 input_cnt := l_input_tbl.NEXT(input_cnt);
625 END LOOP;
626
627 -- Processing ends
628 x_baj_tbl := lx_baj_tbl;
629 x_return_status := l_return_status;
630
631 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
632 EXCEPTION
633 WHEN OKL_API.G_EXCEPTION_ERROR THEN
634 x_return_status := OKL_API.G_RET_STS_ERROR;
635 WHEN OTHERS THEN
636 x_return_status := OKL_API.HANDLE_EXCEPTIONS
637 (
638 l_api_name,
639 G_PKG_NAME,
640 'OTHERS',
641 x_msg_count,
642 x_msg_data,
643 '_PVT'
644 );
645 END get_unbilled_recvbl;
646
647 ---------------------------------------------------------------------------
648
649 PROCEDURE create_passthru_adj(
650 p_api_version IN NUMBER,
651 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
652 p_baj_tbl IN baj_tbl_type,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_msg_count OUT NOCOPY NUMBER,
655 x_msg_data OUT NOCOPY VARCHAR2) IS
656
657 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
658 l_api_version CONSTANT NUMBER := 1;
659 l_api_name CONSTANT VARCHAR2(30) := 'create_passthru_adj';
660 l_ableto_complete BOOLEAN := TRUE;
661
662 l_baj_rec baj_rec_type;
663 l_baj_tbl baj_tbl_type;
664
665 cnt NUMBER;
666 BEGIN
667 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
668 G_PKG_NAME,
669 p_init_msg_list,
670 l_api_version,
671 p_api_version,
672 '_PVT',
673 l_return_status);
674
675 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
676 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
677 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
678 RAISE OKL_API.G_EXCEPTION_ERROR;
679 END IF;
680
681 -- Processing starts
682 l_baj_tbl := p_baj_tbl;
683
684 cnt := l_baj_tbl.FIRST;
685 WHILE (cnt IS NOT NULL)
686 LOOP
687 l_baj_rec := l_baj_tbl(cnt);
688
689 create_debit_memo(
690 p_api_version => l_api_version,
691 p_init_msg_list => p_init_msg_list,
692 p_sel_id => l_baj_rec.sel_id,
693 x_return_status => l_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data);
696
697 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
698 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
699 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
700 l_ableto_complete := FALSE;
701 END IF;
702
703 cnt := l_baj_tbl.NEXT(cnt);
704 END LOOP;
705
706 -- Processing ends
707 IF NOT(l_ableto_complete) THEN
708 x_return_status := OKL_API.G_RET_STS_ERROR;
709 ELSE
710 x_return_status := OKL_API.G_RET_STS_SUCCESS;
711 END IF;
712
713 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
714 EXCEPTION
715 WHEN OKL_API.G_EXCEPTION_ERROR THEN
716 x_return_status := OKL_API.G_RET_STS_ERROR;
717 WHEN OTHERS THEN
718 x_return_status := OKL_API.HANDLE_EXCEPTIONS
719 (
720 l_api_name,
721 G_PKG_NAME,
722 'OTHERS',
723 x_msg_count,
724 x_msg_data,
725 '_PVT'
726 );
727 END create_passthru_adj;
728
729 ---------------------------------------------------------------------------
730
731 PROCEDURE get_unbilled_prop_tax(
732 p_api_version IN NUMBER,
733 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
734 p_input_tbl IN input_tbl_type,
735 x_baj_tbl OUT NOCOPY baj_tbl_type,
736 x_return_status OUT NOCOPY VARCHAR2,
737 x_msg_count OUT NOCOPY NUMBER,
738 x_msg_data OUT NOCOPY VARCHAR2) IS
739
740 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
741 l_api_version CONSTANT NUMBER := 1;
742 l_api_name CONSTANT VARCHAR2(30) := 'get_unbilled_prop_tax';
743
744 l_input_rec input_rec_type;
745 l_input_tbl input_tbl_type;
746
747 l_baj_rec baj_rec_type;
748 lx_baj_tbl baj_tbl_type;
749
750 input_cnt NUMBER;
751 baj_cnt NUMBER := 0;
752
753 ------------------------------------------------
754 --Un-Billed Property Tax Receivables for invoices
755 --For future dated termination
756 ------------------------------------------------
757 cursor l_bill_adj_csr(cp_khr_id IN NUMBER, cp_kle_id IN NUMBER, cp_term_date_from IN DATE, cp_term_date_to IN DATE) IS
758 SELECT stm.khr_id khr_id,
759 stm.kle_id kle_id,
760 TRUNC (ste.stream_element_date) stream_element_date,
761 ste.id sel_id,
762 stm.id stm_id,
763 stm.sty_id sty_id,
764 sty.name sty_name,
765 ste.amount amount,
766 ste.se_line_number,
767 ste.source_id,
768 ste.source_table
769 FROM okl_strm_elements ste,
770 okl_streams stm,
771 okl_strm_type_v sty,
772 okc_k_headers_b khr,
773 okl_k_headers khl,
774 okc_k_lines_b kle,
775 okc_statuses_b khs
776 WHERE trunc(ste.stream_element_date) >= trunc(nvl(cp_term_date_from, ste.stream_element_date))
777 AND trunc(ste.stream_element_date) <= trunc(nvl(cp_term_date_to, ste.stream_element_date))
778 AND ste.amount <> 0
779 AND stm.id = ste.stm_id
780 AND ste.date_billed IS NULL
781 AND stm.active_yn = 'Y'
782 AND stm.say_code = 'CURR'
783 AND sty.id = stm.sty_id
784 AND sty.billable_yn = 'Y'
785 AND khr.id = stm.khr_id
786 AND khr.scs_code IN ('LEASE', 'LOAN')
787 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
788 AND khr.id = cp_khr_id
789 AND khl.id = stm.khr_id
790 AND khl.deal_type IS NOT NULL
791 AND khs.code = khr.sts_code
792 AND khs.ste_code = 'ACTIVE'
793 AND stm.kle_id = kle.id(+)
794 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, nvl(stm.kle_id, -99))
795 AND OKL_BPD_TERMINATION_ADJ_PVT.get_kle_status_code(stm.kle_id) IN ('ACTIVE', 'TERMINATED', 'HEADER_LEVEL')
796 AND exists (select 1 from okc_rule_groups_b rgp
797 , okc_rules_b rul
798 where rgp.dnz_chr_id = kle.dnz_chr_id
799 and rgp.cle_id = kle.id
800 and rgp.rgd_code = 'LAASTX'
801 and rgp.id = rul.rgp_id
802 and rul.rule_information_category = 'LAPRTX'
803 and rul.rule_information1 = 'Y'
804 and (rul.rule_information3 = 'ESTIMATED' or rul.rule_information3 = 'ESTIMATED_AND_ACTUAL')
805 )
806 AND sty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
807 ORDER BY 1, 2, 3;
808
809 BEGIN
810 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
811 G_PKG_NAME,
812 p_init_msg_list,
813 l_api_version,
814 p_api_version,
815 '_PVT',
816 l_return_status);
817
818 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
819 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
820 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
821 RAISE OKL_API.G_EXCEPTION_ERROR;
822 END IF;
823
824 -- Processing starts
825 l_input_tbl := p_input_tbl;
826
827 input_cnt := l_input_tbl.FIRST;
828 WHILE (input_cnt IS NOT NULL)
829 LOOP
830 l_input_rec := l_input_tbl(input_cnt);
831
832 FOR cur_bill_adj IN l_bill_adj_csr(l_input_rec.khr_id, l_input_rec.kle_id, l_input_rec.term_date_from, l_input_rec.term_date_to) LOOP
833 baj_cnt := baj_cnt + 1;
834 lx_baj_tbl(baj_cnt) := cur_bill_adj;
835 END LOOP;
836
837 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
838 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
839 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
840 RAISE okl_api.G_EXCEPTION_ERROR;
841 END IF;
842
843 input_cnt := l_input_tbl.NEXT(input_cnt);
844 END LOOP;
845
846 -- Processing ends
847 x_baj_tbl := lx_baj_tbl;
848 x_return_status := l_return_status;
849
850 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
851 EXCEPTION
852 WHEN OKL_API.G_EXCEPTION_ERROR THEN
853 x_return_status := OKL_API.G_RET_STS_ERROR;
854 WHEN OTHERS THEN
855 x_return_status := OKL_API.HANDLE_EXCEPTIONS
856 (
857 l_api_name,
858 G_PKG_NAME,
859 'OTHERS',
860 x_msg_count,
861 x_msg_data,
862 '_PVT'
863 );
864 END get_unbilled_prop_tax;
865
866 ------------------------------------------------------------------
867 --interface between rebook api and bpd processing apis by fmiao
868 ------------------------------------------------------------------
869 PROCEDURE create_rbk_passthru_adj(
870 p_api_version IN NUMBER,
871 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
872 p_rebook_adj_tbl IN rebook_adj_tbl,
873 x_disb_rec OUT NOCOPY disb_rec_type,
874 x_return_status OUT NOCOPY VARCHAR2,
875 x_msg_count OUT NOCOPY NUMBER,
876 x_msg_data OUT NOCOPY VARCHAR2) IS
877
878 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
879 l_api_version CONSTANT NUMBER := 1;
880 l_api_name CONSTANT VARCHAR2(30) := 'create_rbk_passthru_adj';
881
882 l_disb_rec disb_rec_type;
883 i NUMBER;
884
885 CURSOR get_contract_csr (p_chr_id NUMBER) IS
886 SELECT chr.authoring_org_id,
887 chr.currency_code,
888 hr.set_of_books_id
889 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
890 ,khr.legal_entity_id
891 FROM okc_k_headers_b chr,hr_operating_units hr
892 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
893 ,OKL_K_HEADERS khr
894 WHERE chr.authoring_org_id = hr.organization_id
895 AND chr.id = p_chr_id
896 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
897 AND khr.id = chr.id;
898
899 l_authoring_org_id okc_k_headers_b.authoring_org_id%TYPE;
900 l_currency_code okc_k_headers_b.currency_code%TYPE;
901 l_set_of_books_id hr_operating_units.set_of_books_id%TYPE;
902 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
903 l_legal_entity_id okl_k_headers.legal_entity_id%TYPE;
904
905 CURSOR get_passthru_csr (p_chr_id NUMBER, p_cle_id NUMBER) IS
906 SELECT id,
907 passthru_stream_type_id,
908 payout_basis,
909 payout_basis_formula
910 FROM okl_party_payment_hdr
911 WHERE dnz_chr_id = p_chr_id
912 AND cle_id = p_cle_id
913 --Bug# 4884423
914 AND passthru_term = 'BASE';
915 l_pph_id okl_party_payment_hdr.id%TYPE;
916 l_passthru_stream_type_id okl_party_payment_hdr.passthru_stream_type_id%TYPE;
917 l_payout_basis okl_party_payment_hdr.payout_basis%TYPE;
918 l_payout_basis_formula okl_party_payment_hdr.payout_basis_formula%TYPE;
919
920 BEGIN
921 --Bug# 4884423
922 x_return_status := OKC_API.G_RET_STS_SUCCESS;
923
924 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
925 G_PKG_NAME,
926 p_init_msg_list,
927 l_api_version,
928 p_api_version,
929 '_PVT',
930 l_return_status);
931
932 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
933 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
934 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
935 RAISE OKL_API.G_EXCEPTION_ERROR;
936 END IF;
937
938 IF p_rebook_adj_tbl.COUNT > 0 THEN
939 i := p_rebook_adj_tbl.FIRST;
940 LOOP
941 OPEN get_contract_csr (p_rebook_adj_tbl(i).khr_id);
942 FETCH get_contract_csr INTO l_authoring_org_id,
943 l_currency_code,
944 l_set_of_books_id,
945 l_legal_entity_id;
946 CLOSE get_contract_csr;
947 OPEN get_passthru_csr(p_rebook_adj_tbl(i).khr_id,
948 p_rebook_adj_tbl(i).kle_id);
949 FETCH get_passthru_csr INTO l_pph_id,
950 l_passthru_stream_type_id,
951 l_payout_basis,
952 l_payout_basis_formula;
953 CLOSE get_passthru_csr;
954
955 IF (p_rebook_adj_tbl(i).kle_id IS NOT NULL AND
956 l_payout_basis = 'DUE_DATE')
957 -- Bug# 4884423
958 -- Disbursement Adjustment should be processed for all amounts
959 -- AND p_rebook_adj_tbl(i).adjusted_amount < 0)
960 THEN
961
962 l_disb_rec.set_of_books_id := l_set_of_books_id;
963 l_disb_rec.org_id := l_authoring_org_id;
964 l_disb_rec.currency_code := l_currency_code;
965 l_disb_rec.khr_id := p_rebook_adj_tbl(i).khr_id;
966 l_disb_rec.kle_id := p_rebook_adj_tbl(i).kle_id;
967 l_disb_rec.amount := p_rebook_adj_tbl(i).adjusted_amount;
968 l_disb_rec.sty_id := p_rebook_adj_tbl(i).sty_id;
969 l_disb_rec.transaction_date := p_rebook_adj_tbl(i).date_invoiced;
970 l_disb_rec.pph_id := l_pph_id;
971 l_disb_rec.passthru_stream_type_id := l_passthru_stream_type_id;
972 l_disb_rec.payout_basis := l_payout_basis;
973 l_disb_rec.payout_basis_formula := l_payout_basis_formula;
974 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
975 l_disb_rec.legal_entity_id := l_legal_entity_id;
976
977 OKL_PAY_INVOICES_DISB_PVT.INVOICE_DISBURSEMENT(
978 p_api_version => p_api_version,
979 p_init_msg_list => p_init_msg_list,
980 x_return_status => x_return_status,
981 x_msg_count => x_msg_count,
982 x_msg_data => x_msg_data,
983 p_disb_rec => l_disb_rec);
984
985 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
986 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
987 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
988 RAISE OKL_API.G_EXCEPTION_ERROR;
989 END IF;
990 END IF;
991
992 EXIT WHEN (i = p_rebook_adj_tbl.LAST);
993 i := p_rebook_adj_tbl.NEXT(i);
994 END LOOP;
995 END IF;
996
997 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
998 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
999 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1000 RAISE OKL_API.G_EXCEPTION_ERROR;
1001 END IF;
1002
1003 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1004 EXCEPTION
1005 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1006 x_return_status := OKL_API.G_RET_STS_ERROR;
1007 WHEN OTHERS THEN
1008 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1009 (
1010 l_api_name,
1011 G_PKG_NAME,
1012 'OTHERS',
1013 x_msg_count,
1014 x_msg_data,
1015 '_PVT'
1016 );
1017 END create_rbk_passthru_adj;
1018
1019 ---------------------------------------------------------------------------
1020 END OKL_BPD_TERMINATION_ADJ_PVT;