[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.12010000.5 2010/03/17 12:04:28 nikshah ship $ */
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 ((khr.sts_code = 'EVERGREEN' AND sty.STREAM_TYPE_PURPOSE <> 'ACTUAL_PROPERTY_TAX')
437 OR khr.sts_code <> 'EVERGREEN') -- Bug 9306259
438 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, nvl(stm.kle_id, -99))
439 AND OKL_BPD_TERMINATION_ADJ_PVT.get_kle_status_code(stm.kle_id) IN ('ACTIVE', 'TERMINATED', 'HEADER_LEVEL')
440 ORDER BY 1, 2, 3;
441 -- akrangan bug 5655680 -- start
442 --cursor to check if financial asset appears as linked asset
443 CURSOR l_lnk_ast_csr (p_line_id OKC_K_LINES_B.ID%TYPE) IS
444 Select lnk.id link_kle_id
445 --lnk.cle_id link_kle_id
446 From okc_k_lines_b lnk,
447 okc_line_styles_b lnk_lse,
448 okc_statuses_b sts,
449 okc_k_items cim
450 Where lnk.id = cim.cle_id
451 and lnk.dnz_chr_id = cim.dnz_chr_id
452 and lnk.lse_id = lnk_lse.id
453 and lnk_lse.lty_code in ('LINK_FEE_ASSET','LINK_SERV_ASSET')
454 and sts.code = lnk.sts_code
455 and sts.ste_code not in ('EXPIRED','TERMINATED','CANCELLED')
456 and cim.jtot_object1_code = 'OKX_COVASST'
457 and cim.object1_id1 = to_char(p_line_id)
458 and cim.object1_id2 = '#';
459 --akrangan bug 5655680 -- end
460
461 --Bug 7456516
462 CURSOR GET_PRICING_DET_CSR(P_KHR_ID IN NUMBER) IS
463 SELECT GTS.PRICING_ENGINE,
464 GTS.ISG_ARREARS_PAY_DATES_OPTION
465 FROM OKL_K_HEADERS KHR,
466 OKL_PRODUCTS PDT,
467 OKL_AE_TMPT_SETS_ALL AES,
468 OKL_ST_GEN_TMPT_SETS_ALL GTS
469 WHERE KHR.PDT_ID = PDT.ID
470 AND PDT.AES_ID = AES.ID
471 AND AES.GTS_ID = GTS.ID
472 AND KHR.ID = P_KHR_ID;
473
474 CURSOR GET_PMNT_ARREAR_FLAG(P_KHR_ID IN NUMBER, P_KLE_ID IN NUMBER) IS
475 SELECT RL.RULE_INFORMATION10
476 FROM OKC_RULES_B RL,
477 OKC_RULE_GROUPS_B RGP
478 WHERE RL.RULE_INFORMATION_CATEGORY = 'LASLL'
479 AND RL.RGP_ID = RGP.ID
480 AND RGP.RGD_CODE = 'LALEVL'
481 AND RGP.CLE_ID = P_KLE_ID
482 AND RGP.DNZ_CHR_ID = P_KHR_ID
483 AND ROWNUM < 2;
484
485 l_pmnt_arrear_flag VARCHAR2(1);
486 l_term_date_flag VARCHAR2(1);
487 l_pricing_engine OKL_ST_GEN_TMPT_SETS.PRICING_ENGINE%TYPE;
488 l_int_arrears_pay_option OKL_ST_GEN_TMPT_SETS.ISG_ARREARS_PAY_DATES_OPTION%TYPE;
489 --end Bug 7456516
490
491 BEGIN
492 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
493 G_PKG_NAME,
494 p_init_msg_list,
495 l_api_version,
496 p_api_version,
497 '_PVT',
498 l_return_status);
499
500 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
501 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
502 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
503 RAISE OKL_API.G_EXCEPTION_ERROR;
504 END IF;
505
506 -- Processing starts
507 l_input_tbl := p_input_tbl;
508
509 input_cnt := l_input_tbl.FIRST;
510 WHILE (input_cnt IS NOT NULL)
511 LOOP
512 l_input_rec := l_input_tbl(input_cnt);
513
514 --Bug 7456516
515 OPEN GET_PRICING_DET_CSR(l_input_rec.khr_id);
516 FETCH GET_PRICING_DET_CSR INTO l_pricing_engine, l_int_arrears_pay_option;
517 CLOSE GET_PRICING_DET_CSR;
518
519 l_term_date_flag := 'N';
520 IF((l_pricing_engine = 'EXTERNAL') OR
521 ((l_pricing_engine = 'INTERNAL') AND (l_int_arrears_pay_option = 'FIRST_DAY_OF_NEXT_PERIOD'))
522 ) THEN
523 l_term_date_flag := 'Y';
524 END IF;
525 --end Bug 7456516
526
527 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
528 --Bug 7456516
529 l_pmnt_arrear_flag := 'N';
530 OPEN GET_PMNT_ARREAR_FLAG(cur_bill_adj.khr_id, cur_bill_adj.kle_id);
531 FETCH GET_PMNT_ARREAR_FLAG INTO l_pmnt_arrear_flag;
532 CLOSE GET_PMNT_ARREAR_FLAG;
533
534 IF((l_pmnt_arrear_flag = 'Y') AND (l_term_date_flag = 'Y')) THEN
535 --Add +1 day to Termination Date in case of Arrears payment
536 IF(TRUNC(cur_bill_adj.stream_element_date) > TRUNC(l_input_rec.term_date_from + 1)) THEN
537 baj_cnt := baj_cnt + 1;
538 lx_baj_tbl(baj_cnt) := cur_bill_adj;
539 END IF;
540 ELSE
541 baj_cnt := baj_cnt + 1;
542 lx_baj_tbl(baj_cnt) := cur_bill_adj;
543 END IF;
544 --end Bug 7456516
545 END LOOP;
546 --akrangan Bug 5655680 start --
547 FOR l_lnk_ast IN l_lnk_ast_csr(l_input_rec.kle_id) LOOP
548 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
549 --Bug 7456516
550 l_pmnt_arrear_flag := 'N';
551 OPEN GET_PMNT_ARREAR_FLAG(cur_bill_adj.khr_id, cur_bill_adj.kle_id);
552 FETCH GET_PMNT_ARREAR_FLAG INTO l_pmnt_arrear_flag;
553 CLOSE GET_PMNT_ARREAR_FLAG;
554
555 IF((l_pmnt_arrear_flag = 'Y') AND (l_term_date_flag = 'Y')) THEN
556 --Add +1 day to Termination Date in case of Arrears payment
557 IF(TRUNC(cur_bill_adj.stream_element_date) > TRUNC(l_input_rec.term_date_from + 1)) THEN
558 baj_cnt := baj_cnt + 1;
559 lx_baj_tbl(baj_cnt) := cur_bill_adj;
560 END IF;
561 ELSE
562 baj_cnt := baj_cnt + 1;
563 lx_baj_tbl(baj_cnt) := cur_bill_adj;
564 END IF;
565 --end Bug 7456516
566 END LOOP;
567 END LOOP;
568 --akrangan Bug 5655680 end --
569 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
570 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
571 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
572 RAISE okl_api.G_EXCEPTION_ERROR;
573 END IF;
574
575 input_cnt := l_input_tbl.NEXT(input_cnt);
576 END LOOP;
577
578 -- Processing ends
579 x_baj_tbl := lx_baj_tbl;
580 x_return_status := l_return_status;
581
582 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
583 EXCEPTION
584 WHEN OKL_API.G_EXCEPTION_ERROR THEN
585 x_return_status := OKL_API.G_RET_STS_ERROR;
586 WHEN OTHERS THEN
587 x_return_status := OKL_API.HANDLE_EXCEPTIONS
588 (
589 l_api_name,
590 G_PKG_NAME,
591 'OTHERS',
592 x_msg_count,
593 x_msg_data,
594 '_PVT'
595 );
596 END get_billing_adjust;
597
598 ---------------------------------------------------------------------------
599
600 PROCEDURE get_unbilled_recvbl(
601 p_api_version IN NUMBER,
602 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
603 p_input_tbl IN input_tbl_type,
604 x_baj_tbl OUT NOCOPY baj_tbl_type,
605 x_return_status OUT NOCOPY VARCHAR2,
606 x_msg_count OUT NOCOPY NUMBER,
607 x_msg_data OUT NOCOPY VARCHAR2) IS
608
609 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
610 l_api_version CONSTANT NUMBER := 1;
611 l_api_name CONSTANT VARCHAR2(30) := 'get_unbilled_recvbl';
612
613 l_input_rec input_rec_type;
614 l_input_tbl input_tbl_type;
615
616 l_baj_rec baj_rec_type;
617 lx_baj_tbl baj_tbl_type;
618
619 input_cnt NUMBER;
620 baj_cnt NUMBER := 0;
621
622 ------------------------------------------------
623 --Un-Billed Receivables for invoices
624 --For future dated termination
625 ------------------------------------------------
626 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
627 SELECT stm.khr_id khr_id,
628 stm.kle_id kle_id,
629 TRUNC (ste.stream_element_date) stream_element_date,
630 ste.id sel_id,
631 stm.id stm_id,
632 stm.sty_id sty_id,
633 sty.name sty_name,
634 ste.amount,
635 ste.se_line_number,
636 ste.source_id,
637 ste.source_table
638 FROM okl_strm_elements ste,
639 okl_streams stm,
640 okl_strm_type_v sty,
641 okc_k_headers_b khr,
642 okl_k_headers khl,
643 okc_k_lines_b kle,
644 okc_statuses_b khs
645 WHERE trunc(ste.stream_element_date) >= trunc(nvl(cp_term_date_from, ste.stream_element_date))
646 AND trunc(ste.stream_element_date) <= trunc(nvl(cp_term_date_to, ste.stream_element_date))
647 AND ste.amount <> 0
648 AND stm.id = ste.stm_id
649 AND ste.date_billed IS NULL
650 AND stm.active_yn = 'Y'
651 AND stm.say_code = 'CURR'
652 AND sty.id = stm.sty_id
653 AND sty.billable_yn = 'Y'
654 AND khr.id = stm.khr_id
655 AND khr.scs_code IN ('LEASE', 'LOAN')
656 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
657 AND khr.id = cp_khr_id
658 AND khl.id = stm.khr_id
659 AND khl.deal_type IS NOT NULL
660 AND khs.code = khr.sts_code
661 AND khs.ste_code = 'ACTIVE'
662 AND stm.kle_id = kle.id(+)
663 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, nvl(stm.kle_id, -99))
664 AND OKL_BPD_TERMINATION_ADJ_PVT.get_kle_status_code(stm.kle_id) IN ('ACTIVE', 'TERMINATED', 'HEADER_LEVEL')
665 ORDER BY 1, 2, 3;
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_input_tbl := p_input_tbl;
683
684 input_cnt := l_input_tbl.FIRST;
685 WHILE (input_cnt IS NOT NULL)
686 LOOP
687 l_input_rec := l_input_tbl(input_cnt);
688
689 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
690 baj_cnt := baj_cnt + 1;
691 lx_baj_tbl(baj_cnt) := cur_bill_adj;
692 END LOOP;
693
694 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
695 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
696 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
697 RAISE okl_api.G_EXCEPTION_ERROR;
698 END IF;
699
700 input_cnt := l_input_tbl.NEXT(input_cnt);
701 END LOOP;
702
703 -- Processing ends
704 x_baj_tbl := lx_baj_tbl;
705 x_return_status := l_return_status;
706
707 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
708 EXCEPTION
709 WHEN OKL_API.G_EXCEPTION_ERROR THEN
710 x_return_status := OKL_API.G_RET_STS_ERROR;
711 WHEN OTHERS THEN
712 x_return_status := OKL_API.HANDLE_EXCEPTIONS
713 (
714 l_api_name,
715 G_PKG_NAME,
716 'OTHERS',
717 x_msg_count,
718 x_msg_data,
719 '_PVT'
720 );
721 END get_unbilled_recvbl;
722
723 ---------------------------------------------------------------------------
724
725 PROCEDURE create_passthru_adj(
726 p_api_version IN NUMBER,
727 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
728 p_baj_tbl IN baj_tbl_type,
729 x_return_status OUT NOCOPY VARCHAR2,
730 x_msg_count OUT NOCOPY NUMBER,
731 x_msg_data OUT NOCOPY VARCHAR2) IS
732
733 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
734 l_api_version CONSTANT NUMBER := 1;
735 l_api_name CONSTANT VARCHAR2(30) := 'create_passthru_adj';
736 l_ableto_complete BOOLEAN := TRUE;
737
738 l_baj_rec baj_rec_type;
739 l_baj_tbl baj_tbl_type;
740
741 cnt NUMBER;
742 BEGIN
743 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
744 G_PKG_NAME,
745 p_init_msg_list,
746 l_api_version,
747 p_api_version,
748 '_PVT',
749 l_return_status);
750
751 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
752 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
753 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
754 RAISE OKL_API.G_EXCEPTION_ERROR;
755 END IF;
756
757 -- Processing starts
758 l_baj_tbl := p_baj_tbl;
759
760 cnt := l_baj_tbl.FIRST;
761 WHILE (cnt IS NOT NULL)
762 LOOP
763 l_baj_rec := l_baj_tbl(cnt);
764
765 create_debit_memo(
766 p_api_version => l_api_version,
767 p_init_msg_list => p_init_msg_list,
768 p_sel_id => l_baj_rec.sel_id,
769 x_return_status => l_return_status,
770 x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data);
772
773 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
774 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
775 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
776 l_ableto_complete := FALSE;
777 END IF;
778
779 cnt := l_baj_tbl.NEXT(cnt);
780 END LOOP;
781
782 -- Processing ends
783 IF NOT(l_ableto_complete) THEN
784 x_return_status := OKL_API.G_RET_STS_ERROR;
785 ELSE
786 x_return_status := OKL_API.G_RET_STS_SUCCESS;
787 END IF;
788
789 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
790 EXCEPTION
791 WHEN OKL_API.G_EXCEPTION_ERROR THEN
792 x_return_status := OKL_API.G_RET_STS_ERROR;
793 WHEN OTHERS THEN
794 x_return_status := OKL_API.HANDLE_EXCEPTIONS
795 (
796 l_api_name,
797 G_PKG_NAME,
798 'OTHERS',
799 x_msg_count,
800 x_msg_data,
801 '_PVT'
802 );
803 END create_passthru_adj;
804
805 ---------------------------------------------------------------------------
806
807 PROCEDURE get_unbilled_prop_tax(
808 p_api_version IN NUMBER,
809 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
810 p_input_tbl IN input_tbl_type,
811 x_baj_tbl OUT NOCOPY baj_tbl_type,
812 x_return_status OUT NOCOPY VARCHAR2,
813 x_msg_count OUT NOCOPY NUMBER,
814 x_msg_data OUT NOCOPY VARCHAR2) IS
815
816 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
817 l_api_version CONSTANT NUMBER := 1;
818 l_api_name CONSTANT VARCHAR2(30) := 'get_unbilled_prop_tax';
819
820 l_input_rec input_rec_type;
821 l_input_tbl input_tbl_type;
822
823 l_baj_rec baj_rec_type;
824 lx_baj_tbl baj_tbl_type;
825
826 input_cnt NUMBER;
827 baj_cnt NUMBER := 0;
828
829 ------------------------------------------------
830 --Un-Billed Property Tax Receivables for invoices
831 --For future dated termination
832 ------------------------------------------------
833 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
834 SELECT stm.khr_id khr_id,
835 stm.kle_id kle_id,
836 TRUNC (ste.stream_element_date) stream_element_date,
837 ste.id sel_id,
838 stm.id stm_id,
839 stm.sty_id sty_id,
840 sty.name sty_name,
841 ste.amount amount,
842 ste.se_line_number,
843 ste.source_id,
844 ste.source_table
845 FROM okl_strm_elements ste,
846 okl_streams stm,
847 okl_strm_type_v sty,
848 okc_k_headers_b khr,
849 okl_k_headers khl,
850 okc_k_lines_b kle,
851 okc_statuses_b khs
852 WHERE trunc(ste.stream_element_date) >= trunc(nvl(cp_term_date_from, ste.stream_element_date))
853 AND trunc(ste.stream_element_date) <= trunc(nvl(cp_term_date_to, ste.stream_element_date))
854 AND ste.amount <> 0
855 AND stm.id = ste.stm_id
856 AND ste.date_billed IS NULL
857 AND stm.active_yn = 'Y'
858 AND stm.say_code = 'CURR'
859 AND sty.id = stm.sty_id
860 AND sty.billable_yn = 'Y'
861 AND khr.id = stm.khr_id
862 AND khr.scs_code IN ('LEASE', 'LOAN')
863 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
864 AND khr.id = cp_khr_id
865 AND khl.id = stm.khr_id
866 AND khl.deal_type IS NOT NULL
867 AND khs.code = khr.sts_code
868 AND khs.ste_code = 'ACTIVE'
869 AND stm.kle_id = kle.id(+)
870 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, nvl(stm.kle_id, -99))
871 AND OKL_BPD_TERMINATION_ADJ_PVT.get_kle_status_code(stm.kle_id) IN ('ACTIVE', 'TERMINATED', 'HEADER_LEVEL')
872 AND exists (select 1 from okc_rule_groups_b rgp
873 , okc_rules_b rul
874 where rgp.dnz_chr_id = kle.dnz_chr_id
875 and rgp.cle_id = kle.id
876 and rgp.rgd_code = 'LAASTX'
877 and rgp.id = rul.rgp_id
878 and rul.rule_information_category = 'LAPRTX'
879 and rul.rule_information1 = 'Y'
880 and (rul.rule_information3 = 'ESTIMATED' or rul.rule_information3 = 'ESTIMATED_AND_ACTUAL')
881 )
882 AND sty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
883 ORDER BY 1, 2, 3;
884
885 BEGIN
886 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
887 G_PKG_NAME,
888 p_init_msg_list,
889 l_api_version,
890 p_api_version,
891 '_PVT',
892 l_return_status);
893
894 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
895 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
896 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
897 RAISE OKL_API.G_EXCEPTION_ERROR;
898 END IF;
899
900 -- Processing starts
901 l_input_tbl := p_input_tbl;
902
903 input_cnt := l_input_tbl.FIRST;
904 WHILE (input_cnt IS NOT NULL)
905 LOOP
906 l_input_rec := l_input_tbl(input_cnt);
907
908 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
909 baj_cnt := baj_cnt + 1;
910 lx_baj_tbl(baj_cnt) := cur_bill_adj;
911 END LOOP;
912
913 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
914 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
915 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
916 RAISE okl_api.G_EXCEPTION_ERROR;
917 END IF;
918
919 input_cnt := l_input_tbl.NEXT(input_cnt);
920 END LOOP;
921
922 -- Processing ends
923 x_baj_tbl := lx_baj_tbl;
924 x_return_status := l_return_status;
925
926 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
927 EXCEPTION
928 WHEN OKL_API.G_EXCEPTION_ERROR THEN
929 x_return_status := OKL_API.G_RET_STS_ERROR;
930 WHEN OTHERS THEN
931 x_return_status := OKL_API.HANDLE_EXCEPTIONS
932 (
933 l_api_name,
934 G_PKG_NAME,
935 'OTHERS',
936 x_msg_count,
937 x_msg_data,
938 '_PVT'
939 );
940 END get_unbilled_prop_tax;
941
942 ------------------------------------------------------------------
943 --interface between rebook api and bpd processing apis by fmiao
944 ------------------------------------------------------------------
945 PROCEDURE create_rbk_passthru_adj(
946 p_api_version IN NUMBER,
947 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
948 p_rebook_adj_tbl IN rebook_adj_tbl,
949 x_disb_rec OUT NOCOPY disb_rec_type,
950 x_return_status OUT NOCOPY VARCHAR2,
951 x_msg_count OUT NOCOPY NUMBER,
952 x_msg_data OUT NOCOPY VARCHAR2) IS
953
954 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
955 l_api_version CONSTANT NUMBER := 1;
956 l_api_name CONSTANT VARCHAR2(30) := 'create_rbk_passthru_adj';
957
958 l_disb_rec disb_rec_type;
959 i NUMBER;
960
961 CURSOR get_contract_csr (p_chr_id NUMBER) IS
962 SELECT chr.authoring_org_id,
963 chr.currency_code,
964 hr.set_of_books_id
965 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
966 ,khr.legal_entity_id
967 FROM okc_k_headers_b chr,hr_operating_units hr
968 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
969 ,OKL_K_HEADERS khr
970 WHERE chr.authoring_org_id = hr.organization_id
971 AND chr.id = p_chr_id
972 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
973 AND khr.id = chr.id;
974
975 l_authoring_org_id okc_k_headers_b.authoring_org_id%TYPE;
976 l_currency_code okc_k_headers_b.currency_code%TYPE;
977 l_set_of_books_id hr_operating_units.set_of_books_id%TYPE;
978 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
979 l_legal_entity_id okl_k_headers.legal_entity_id%TYPE;
980
981 CURSOR get_passthru_csr (p_chr_id NUMBER, p_cle_id NUMBER) IS
982 SELECT id,
983 passthru_stream_type_id,
984 payout_basis,
985 payout_basis_formula
986 FROM okl_party_payment_hdr
987 WHERE dnz_chr_id = p_chr_id
988 AND cle_id = p_cle_id
989 --Bug# 4884423
990 AND passthru_term = 'BASE';
991 l_pph_id okl_party_payment_hdr.id%TYPE;
992 l_passthru_stream_type_id okl_party_payment_hdr.passthru_stream_type_id%TYPE;
993 l_payout_basis okl_party_payment_hdr.payout_basis%TYPE;
994 l_payout_basis_formula okl_party_payment_hdr.payout_basis_formula%TYPE;
995
996 BEGIN
997 --Bug# 4884423
998 x_return_status := OKC_API.G_RET_STS_SUCCESS;
999
1000 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1001 G_PKG_NAME,
1002 p_init_msg_list,
1003 l_api_version,
1004 p_api_version,
1005 '_PVT',
1006 l_return_status);
1007
1008 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1009 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1010 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1011 RAISE OKL_API.G_EXCEPTION_ERROR;
1012 END IF;
1013
1014 IF p_rebook_adj_tbl.COUNT > 0 THEN
1015 i := p_rebook_adj_tbl.FIRST;
1016 LOOP
1017 OPEN get_contract_csr (p_rebook_adj_tbl(i).khr_id);
1018 FETCH get_contract_csr INTO l_authoring_org_id,
1019 l_currency_code,
1020 l_set_of_books_id,
1021 l_legal_entity_id;
1022 CLOSE get_contract_csr;
1023 OPEN get_passthru_csr(p_rebook_adj_tbl(i).khr_id,
1024 p_rebook_adj_tbl(i).kle_id);
1025 FETCH get_passthru_csr INTO l_pph_id,
1026 l_passthru_stream_type_id,
1027 l_payout_basis,
1028 l_payout_basis_formula;
1029 CLOSE get_passthru_csr;
1030
1031 IF (p_rebook_adj_tbl(i).kle_id IS NOT NULL AND
1032 l_payout_basis = 'DUE_DATE')
1033 -- Bug# 4884423
1034 -- Disbursement Adjustment should be processed for all amounts
1035 -- AND p_rebook_adj_tbl(i).adjusted_amount < 0)
1036 THEN
1037
1038 l_disb_rec.set_of_books_id := l_set_of_books_id;
1039 l_disb_rec.org_id := l_authoring_org_id;
1040 l_disb_rec.currency_code := l_currency_code;
1041 l_disb_rec.khr_id := p_rebook_adj_tbl(i).khr_id;
1042 l_disb_rec.kle_id := p_rebook_adj_tbl(i).kle_id;
1043 l_disb_rec.amount := p_rebook_adj_tbl(i).adjusted_amount;
1044 l_disb_rec.sty_id := p_rebook_adj_tbl(i).sty_id;
1045 l_disb_rec.transaction_date := p_rebook_adj_tbl(i).date_invoiced;
1046 l_disb_rec.pph_id := l_pph_id;
1047 l_disb_rec.passthru_stream_type_id := l_passthru_stream_type_id;
1048 l_disb_rec.payout_basis := l_payout_basis;
1049 l_disb_rec.payout_basis_formula := l_payout_basis_formula;
1050 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
1051 l_disb_rec.legal_entity_id := l_legal_entity_id;
1052
1053 OKL_PAY_INVOICES_DISB_PVT.INVOICE_DISBURSEMENT(
1054 p_api_version => p_api_version,
1055 p_init_msg_list => p_init_msg_list,
1056 x_return_status => x_return_status,
1057 x_msg_count => x_msg_count,
1058 x_msg_data => x_msg_data,
1059 p_disb_rec => l_disb_rec);
1060
1061 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1062 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1063 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1064 RAISE OKL_API.G_EXCEPTION_ERROR;
1065 END IF;
1066 END IF;
1067
1068 EXIT WHEN (i = p_rebook_adj_tbl.LAST);
1069 i := p_rebook_adj_tbl.NEXT(i);
1070 END LOOP;
1071 END IF;
1072
1073 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1074 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1075 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1076 RAISE OKL_API.G_EXCEPTION_ERROR;
1077 END IF;
1078
1079 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1080 EXCEPTION
1081 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1082 x_return_status := OKL_API.G_RET_STS_ERROR;
1083 WHEN OTHERS THEN
1084 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1085 (
1086 l_api_name,
1087 G_PKG_NAME,
1088 'OTHERS',
1089 x_msg_count,
1090 x_msg_data,
1091 '_PVT'
1092 );
1093 END create_rbk_passthru_adj;
1094
1095 ---------------------------------------------------------------------------
1096 END OKL_BPD_TERMINATION_ADJ_PVT;