[Home] [Help]
PACKAGE BODY: APPS.OKL_CS_TRANSACTIONS_PVT
Source
1 PACKAGE BODY okl_cs_transactions_pvt AS
2 /* $Header: OKLRBFNB.pls 120.9 2008/02/22 12:04:13 dkagrawa noship $ */
3
4 ------------------------------------------------------------------------------
5 -- PROCEDURE get_totals
6 ------------------------------------------------------------------------------
7 -- Created by : RFEDANE
8 --
9 -- Purpose:
10 -- Return Trasaction, Receipt, and Disbursement Totals
11 --
12 -- Known limitations/enhancements and/or remarks:
13 --
14 ------------------------------------------------------------------------------
15 PROCEDURE get_totals (p_select IN VARCHAR2,
16 p_from IN VARCHAR2,
17 p_where IN VARCHAR2,
18 x_inv_total OUT NOCOPY NUMBER,
19 x_rec_total OUT NOCOPY NUMBER,
20 x_due_total OUT NOCOPY NUMBER,
21 x_credit_total OUT NOCOPY NUMBER,
22 x_adjust_total OUT NOCOPY NUMBER,
23 x_row_count OUT NOCOPY NUMBER,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2) IS
27
28 l_sql VARCHAR2(1000);
29 l_cursor INTEGER;
30 l_rows NUMBER;
31
32 BEGIN
33
34 IF p_where IS NOT NULL THEN
35 l_sql := ' SELECT '||p_select||' FROM '||p_from||' WHERE '||p_where;
36 ELSE
37 l_sql := ' SELECT '||p_select||' FROM '||p_from;
38 END IF;
39
40 l_cursor := DBMS_SQL.OPEN_CURSOR;
41 DBMS_SQL.PARSE(l_cursor, l_sql , DBMS_SQL.V7);
42
43 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, x_inv_total);
44 DBMS_SQL.DEFINE_COLUMN(l_cursor, 2, x_rec_total);
45 DBMS_SQL.DEFINE_COLUMN(l_cursor, 3, x_due_total);
46 IF p_from IN ('OKL_CS_BILLINGTRX_UV','OKL_CS_ACCOUNT_CONT_INV_UV','OKL_CS_ACCOUNT_INV_UV') THEN
47 DBMS_SQL.DEFINE_COLUMN(l_cursor, 4, x_credit_total);
48 DBMS_SQL.DEFINE_COLUMN(l_cursor, 5, x_adjust_total);
49 END IF;
50
51 l_rows := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor);
52
53 IF l_rows = 1 THEN
54
55 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, x_inv_total);
56 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, x_rec_total);
57 DBMS_SQL.COLUMN_VALUE(l_cursor, 3, x_due_total);
58 IF p_from IN ('OKL_CS_BILLINGTRX_UV','OKL_CS_ACCOUNT_CONT_INV_UV','OKL_CS_ACCOUNT_INV_UV') THEN
59 DBMS_SQL.COLUMN_VALUE(l_cursor, 4, x_credit_total);
60 DBMS_SQL.COLUMN_VALUE(l_cursor, 5, x_adjust_total);
61 END IF;
62
63 x_return_status := OKL_API.G_RET_STS_SUCCESS;
64
65 ELSIF l_rows = 0 THEN
66
67 x_return_status := OKL_API.G_RET_STS_ERROR;
68
69 END IF;
70
71 x_row_count := l_rows;
72 DBMS_SQL.CLOSE_CURSOR(l_cursor);
73
74 EXCEPTION
75
76 WHEN OTHERS THEN
77
78 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
79 p_msg_name => G_UNEXPECTED_ERROR,
80 p_token1 => G_SQLCODE_TOKEN,
81 p_token1_value => SQLCODE,
82 p_token2 => G_SQLERRM_TOKEN,
83 p_token2_value => SQLERRM);
84
85 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
86 DBMS_SQL.CLOSE_CURSOR(l_cursor);
87
88 END get_totals;
89
90
91 ------------------------------------------------------------------------------
92 -- PROCEDURE get_svf_info
93 ------------------------------------------------------------------------------
94 -- Created by : RFEDANE
95 --
96 -- Purpose:
97 -- Check whether Rule exists for the Service Fee Code first
98 -- If Rule exists
99 -- Known limitations/enhancements and/or remarks:
100 --
101 ------------------------------------------------------------------------------
102
103 PROCEDURE get_svf_info (p_khr_id IN NUMBER,
104 p_svf_code IN VARCHAR2,
105 x_svf_info_rec OUT NOCOPY svf_info_rec,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2) IS
109
110 CURSOR c_svf_applicable IS
111 SELECT rul.rule_information1 svf_applicability
112 FROM okc_rules_b rul
113 WHERE rul.dnz_chr_id = p_khr_id
114 AND rul.rule_information_category = p_svf_code;
115
116 CURSOR c_svf_info IS
117 SELECT svf.id svf_id,
118 fnd.meaning svf_name,
119 svf.amount svf_amount,
120 fnd.description svf_desc
121 FROM fnd_lookups fnd,
122 okl_service_fees_b svf
123 WHERE svf.srv_code = p_svf_code
124 AND NVL(svf.organization_id, -99) = NVL(mo_global.get_current_org_id(), -99)
125 AND svf.srv_code = fnd.lookup_code
126 AND lookup_type = 'OKL_SERVICE_FEES';
127
128 i BINARY_INTEGER := 0;
129 l_svf_applicable VARCHAR2(1) := '?';
130
131
132 BEGIN
133
134 OPEN c_svf_applicable;
135 FETCH c_svf_applicable INTO l_svf_applicable;
136 CLOSE c_svf_applicable;
137
138 IF (l_svf_applicable = '?') OR (l_svf_applicable = 'N') THEN
139 RETURN;
140 ELSIF (l_svf_applicable = 'Y') THEN
141 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
142 END IF;
143
144 OPEN c_svf_info;
145 FETCH c_svf_info INTO x_svf_info_rec;
146 CLOSE c_svf_info;
147 IF x_svf_info_rec.SVF_ID IS NOT NULL THEN
148 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
149 END IF;
150
151 x_return_status := OKL_API.G_RET_STS_SUCCESS;
152
153 EXCEPTION
154
155 WHEN OTHERS THEN
156
157 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
158 p_msg_name => G_UNEXPECTED_ERROR,
159 p_token1 => G_SQLCODE_TOKEN,
160 p_token1_value => SQLCODE,
161 p_token2 => G_SQLERRM_TOKEN,
162 p_token2_value => SQLERRM);
163
164 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
165
166 END get_svf_info;
167
168
169 ------------------------------------------------------------------------------
170 -- PROCEDURE get_credit_memo_info
171 ------------------------------------------------------------------------------
172 -- Created by : SMODUGA
173 --
174 -- Purpose:
175 -- Get Credit Memo Data for Interaction
176 -- If credit memo exists
177 -- Known limitations/enhancements and/or remarks:
178 --
179 ------------------------------------------------------------------------------
180
181 PROCEDURE get_credit_memo_info (p_khr_id IN NUMBER,
182 p_tai_id IN NUMBER,
183 x_trx_type OUT NOCOPY VARCHAR2,
184 x_inv_num OUT NOCOPY NUMBER,
185 x_trx_date OUT NOCOPY DATE,
186 x_trx_amount OUT NOCOPY NUMBER,
187 x_amnt_app OUT NOCOPY NUMBER,
188 x_amnt_due OUT NOCOPY NUMBER,
189 x_crd_amnt OUT NOCOPY NUMBER,
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_msg_count OUT NOCOPY NUMBER,
192 x_msg_data OUT NOCOPY VARCHAR2) IS
193
194 CURSOR c_credit_memo_tld IS
195 select STYT.NAME,CNR.CONSOLIDATED_INVOICE_NUMBER
196 ,APS.TRX_DATE,APS.AMOUNT_DUE_ORIGINAL,
197 APS.AMOUNT_APPLIED,APS.AMOUNT_DUE_REMAINING
198 ,TIL.Amount
199 from OKL_TRX_AR_INVOICES_B TAI
200 ,OKL_TXL_AR_INV_LNS_B TIL
201 ,OKL_TXD_AR_LN_DTLS_B TLD2
202 ,OKL_TXD_AR_LN_DTLS_B TLD
203 ,OKL_STRM_TYPE_TL STYT
204 ,OKL_XTL_SELL_INVS_V XLS
205 ,OKC_K_HEADERS_V CHR
206 ,OKL_CNSLD_AR_STRMS_B LSM
207 ,OKL_CNSLD_AR_LINES_B LLN
208 ,AR_PAYMENT_SCHEDULES_ALL APS
209 ,OKL_CNSLD_AR_HDRS_B CNR
210 where TAI.ID = p_tai_id
211 AND CHR.ID = p_khr_id
212 AND TIL.TAI_ID =TAI.ID
213 AND TIL.ID = TLD2.TIL_ID_DETAILS
214 AND TLD2.TLD_ID_REVERSES =TLD.ID
215 AND TLD.STY_ID = STYT.ID
216 AND STYT.LANGUAGE = USERENV('LANG')
217 AND XLS.TLD_ID = TLD.ID
218 AND XLS.LSM_ID = LSM.ID
219 AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
220 AND LSM.LLN_ID = LLN.ID
221 AND LLN.CNR_ID = CNR.ID
222 UNION
223 select STYT.NAME,CNR.CONSOLIDATED_INVOICE_NUMBER
224 ,APS.TRX_DATE,APS.AMOUNT_DUE_ORIGINAL,
225 APS.AMOUNT_APPLIED,APS.AMOUNT_DUE_REMAINING
226 ,TIL.Amount
227 from OKL_TRX_AR_INVOICES_B TAI
228 ,OKL_TXL_AR_INV_LNS_B TIL2
229 ,OKL_TXL_AR_INV_LNS_B TIL
230 ,OKL_STRM_TYPE_TL STYT
231 ,OKL_XTL_SELL_INVS_V XLS
232 ,OKC_K_HEADERS_V CHR
233 ,OKL_CNSLD_AR_STRMS_B LSM
234 ,OKL_CNSLD_AR_LINES_B LLN
235 ,AR_PAYMENT_SCHEDULES_ALL APS
236 ,OKL_CNSLD_AR_HDRS_B CNR
237 where TAI.ID = p_tai_id
238 AND CHR.ID = p_khr_id
239 AND TIL.TAI_ID =TAI.ID
240 AND TIL2.TIL_ID_REVERSES = TIL.ID
241 AND TIL.STY_ID = STYT.ID
242 AND STYT.LANGUAGE = USERENV('LANG')
243 AND XLS.TIL_ID = TIL.ID
244 AND XLS.LSM_ID = LSM.ID
245 AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
246 AND LSM.LLN_ID = LLN.ID
247 AND LLN.CNR_ID = CNR.ID
248 UNION --Added following union for new invoices --dkagrawa
249 SELECT DISTINCT STYT.NAME
250 ,RACTRX.TRX_NUMBER CONSOLIDATED_INVOICE_NUMBER
251 ,APS.TRX_DATE
252 ,RACTRL.AMOUNT_DUE_ORIGINAL
253 ,OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(RACTRX.customer_trx_id, RACTRL.customer_trx_line_id) AMOUNT_APPLIED
254 ,RACTRL.AMOUNT_DUE_REMAINING
255 ,TIL.Amount
256 from OKL_TRX_AR_INVOICES_B TAI
257 ,OKL_TXL_AR_INV_LNS_B TIL
258 ,OKL_TXD_AR_LN_DTLS_B TLD2
259 ,OKL_TXD_AR_LN_DTLS_B TLD
260 ,OKL_STRM_TYPE_TL STYT
261 ,OKC_K_HEADERS_V CHR
262 ,AR_PAYMENT_SCHEDULES_ALL APS
263 ,RA_CUSTOMER_TRX_ALL RACTRX
264 ,RA_CUSTOMER_TRX_LINES_ALL RACTRL
265 where TAI.ID = p_tai_id
266 AND CHR.ID = p_khr_id
267 AND TIL.TAI_ID =TAI.ID
268 AND TIL.ID = TLD2.TIL_ID_DETAILS
269 AND TLD2.TLD_ID_REVERSES =TLD.ID
270 AND TLD.khr_id = CHR.ID
271 AND TLD.STY_ID = STYT.ID
272 AND STYT.LANGUAGE = USERENV('LANG')
273 AND TLD.ID = RACTRL.INTERFACE_LINE_ATTRIBUTE14
274 AND APS.CUSTOMER_TRX_ID = RACTRL.CUSTOMER_TRX_ID
275 AND RACTRL.CUSTOMER_TRX_ID = RACTRX.CUSTOMER_TRX_ID
276 AND RACTRL.INTERFACE_LINE_ATTRIBUTE1 IS NULL;
277
278 /*select STYT.NAME,CNR.CONSOLIDATED_INVOICE_NUMBER
279 ,APS.TRX_DATE,APS.AMOUNT_DUE_ORIGINAL,
280 APS.AMOUNT_APPLIED,APS.AMOUNT_DUE_REMAINING
281 ,TIL.Amount
282 from OKL_TRX_AR_INVOICES_B TAI
283 ,OKL_TXL_AR_INV_LNS_B TIL
284 ,OKL_TXD_AR_LN_DTLS_B TLD
285 ,OKL_STRM_TYPE_TL STYT
286 ,OKL_XTL_SELL_INVS_V XLS
287 ,OKC_K_HEADERS_V CHR
288 ,OKL_CNSLD_AR_STRMS_B LSM
289 ,OKL_CNSLD_AR_LINES_B LLN
290 ,AR_PAYMENT_SCHEDULES_ALL APS
291 ,OKL_CNSLD_AR_HDRS_B CNR
292 ,OKC_K_LINES_B CLE
293 ,OKC_LINE_STYLES_B LSE
294 ,OKC_K_ITEMS CIM
295 ,FA_ADDITIONS_B FAA
296 where TAI.ID = p_lsm_id
297 AND CHR.ID = p_khr_id
298 AND TIL.TAI_ID =TAI.ID
299 AND TIL.TIL_ID_REVERSES = TLD.TIL_ID_DETAILS
300 AND TLD.STY_ID = STYT.ID
301 AND STYT.LANGUAGE = USERENV('LANG')
302 AND XLS.TLD_ID = TLD.ID
303 AND XLS.XTRX_CONTRACT = CHR.CONTRACT_NUMBER
304 AND XLS.LSM_ID = LSM.ID
305 AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
306 AND LSM.LLN_ID = LLN.ID
307 AND LLN.CNR_ID = CNR.ID
308 AND LSM.KHR_ID = CHR.ID
309 AND LSM.KLE_ID = CLE.CLE_ID
310 AND CLE.LSE_ID = LSE.ID
311 AND LSE.LTY_CODE = 'FIXED_ASSET'
312 AND CLE.ID = CIM.CLE_ID
313 AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
314 AND CIM.OBJECT1_ID1 = FAA.ASSET_ID;
315
316
317 CURSOR c_credit_memo_til IS
318 select STYT.NAME,CNR.CONSOLIDATED_INVOICE_NUMBER
319 ,APS.TRX_DATE,APS.AMOUNT_DUE_ORIGINAL,
320 APS.AMOUNT_APPLIED,APS.AMOUNT_DUE_REMAINING
321 ,TIL.Amount
322 from OKL_TRX_AR_INVOICES_B TAI
323 ,OKL_TXL_AR_INV_LNS_B TIL
324 ,OKL_TXD_AR_LN_DTLS_B TLD
325 ,OKL_STRM_TYPE_TL STYT
326 ,OKL_XTL_SELL_INVS_V XLS
327 ,OKC_K_HEADERS_V CHR
328 ,OKL_CNSLD_AR_STRMS_B LSM
329 ,OKL_CNSLD_AR_LINES_B LLN
330 ,AR_PAYMENT_SCHEDULES_ALL APS
331 ,OKL_CNSLD_AR_HDRS_B CNR
332 ,OKC_K_LINES_B CLE
333 ,OKC_LINE_STYLES_B LSE
334 ,OKC_K_ITEMS CIM
335 ,FA_ADDITIONS_B FAA
336 where TAI.ID = p_lsm_id
337 AND CHR.ID = p_khr_id
338 AND TIL.TAI_ID =TAI.ID
339 AND TIL.TIL_ID_REVERSES = TLD.TIL_ID_DETAILS
340 AND TLD.STY_ID = STYT.ID
341 AND STYT.LANGUAGE = USERENV('LANG')
342 AND XLS.TIL_ID = TIL.ID
343 AND XLS.XTRX_CONTRACT = CHR.CONTRACT_NUMBER
344 AND XLS.LSM_ID = LSM.ID
345 AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
346 AND LSM.LLN_ID = LLN.ID
347 AND LLN.CNR_ID = CNR.ID
348 AND LSM.KHR_ID = CHR.ID
349 AND LSM.KLE_ID = CLE.CLE_ID
350 AND CLE.LSE_ID = LSE.ID
351 AND LSE.LTY_CODE = 'FIXED_ASSET'
352 AND CLE.ID = CIM.CLE_ID
353 AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
354 AND CIM.OBJECT1_ID1 = FAA.ASSET_ID;*/
355
356
357
358 i BINARY_INTEGER := 0;
359 l_credit_memo VARCHAR2(1) := '?';
360 l_row_notfound BOOLEAN := TRUE;
361 l_no_data_found BOOLEAN := TRUE;
362
363 BEGIN
364
365
366 OPEN c_credit_memo_tld;
367 FETCH c_credit_memo_tld INTO x_trx_type,x_inv_num,x_trx_date,x_trx_amount,x_amnt_app,x_amnt_due,x_crd_amnt;
368 l_row_notfound := c_credit_memo_tld%NOTFOUND;
369 CLOSE c_credit_memo_tld;
370
371 /*IF(l_row_notfound) THEN
372 OPEN c_credit_memo_til;
373 FETCH c_credit_memo_til INTO x_trx_type,x_inv_num,x_trx_date,x_trx_amount,x_amnt_app,x_amnt_due,x_crd_amnt;
374 l_no_data_found := c_credit_memo_til%NOTFOUND;
375 CLOSE c_credit_memo_til;*/
376 IF(l_row_notfound) THEN
377 RETURN;
378 END IF;
379 --END IF;
380
381
382
383 x_return_status := OKL_API.G_RET_STS_SUCCESS;
384
385 EXCEPTION
386
387 WHEN OTHERS THEN
388
389 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
390 p_msg_name => G_UNEXPECTED_ERROR,
391 p_token1 => G_SQLCODE_TOKEN,
392 p_token1_value => SQLCODE,
393 p_token2 => G_SQLERRM_TOKEN,
394 p_token2_value => SQLERRM);
395
396 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
397
398 END get_credit_memo_info;
399
400
401 ------------------------------------------------------------------------------
402 -- PROCEDURE check_process_template
403 ------------------------------------------------------------------------------
404 -- Created by : RFEDANE
405 --
406 -- Purpose:
407 -- Check for existance of at least one valid Process Template record
408 --
409 -- Known limitations/enhancements and/or remarks:
410 --
411 ------------------------------------------------------------------------------
412
413 PROCEDURE check_process_template (p_ptm_code IN VARCHAR2,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2) IS
417
418 CURSOR c_ptm IS SELECT 1
419 FROM okl_process_tmplts_b
420 WHERE NVL(org_id, -99) = NVL(mo_global.get_current_org_id(), -99)
421 AND ptm_code = p_ptm_code
422 AND start_date <= TRUNC(SYSDATE)
423 AND NVL(end_date, TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
424
425 l_dummy VARCHAR2(1);
426
427 BEGIN
428
429 OPEN c_ptm;
430 FETCH c_ptm INTO l_dummy;
431 IF c_ptm%FOUND THEN
432 x_return_status := OKL_API.G_RET_STS_SUCCESS;
433 ELSE
434 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
435 END IF;
436
437 CLOSE c_ptm;
438
439 EXCEPTION
440
441 WHEN OTHERS THEN
442
443 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
444 p_msg_name => G_UNEXPECTED_ERROR,
445 p_token1 => G_SQLCODE_TOKEN,
446 p_token1_value => SQLCODE,
447 p_token2 => G_SQLERRM_TOKEN,
448 p_token2_value => SQLERRM);
449
450 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
451
452 END check_process_template;
453
454
455 ------------------------------------------------------------------------------
456 -- PROCEDURE get_pvt_label_email
457 ------------------------------------------------------------------------------
458 -- Created by : RFEDANE
459 --
460 -- Purpose:
461 -- Retrieve Email Address of the Lessors Private Label
462 --
463 -- Known limitations/enhancements and/or remarks:
464 --
465 ------------------------------------------------------------------------------
466
467 PROCEDURE get_pvt_label_email (p_khr_id IN NUMBER,
468 x_email OUT NOCOPY VARCHAR2,
469 x_return_status OUT NOCOPY VARCHAR2,
470 x_msg_count OUT NOCOPY NUMBER,
471 x_msg_data OUT NOCOPY VARCHAR2) IS
472
473 CURSOR c_email IS SELECT email_address
474 FROM hz_parties hzp, okc_k_party_roles_b cpl
475 WHERE cpl.dnz_chr_id = p_khr_id
476 AND cpl.jtot_object1_code = 'OKX_PARTY'
477 AND cpl.rle_code = 'PRIVATE_LABEL'
478 AND cpl.object1_id1 = hzp.party_id;
479
480 BEGIN
481
482 OPEN c_email;
483 FETCH c_email INTO x_email;
484 IF c_email%NOTFOUND THEN
485 x_email := -1;
486 END IF;
487 CLOSE c_email;
488
489 x_return_status := OKL_API.G_RET_STS_SUCCESS;
490
491 EXCEPTION
492
493 WHEN OTHERS THEN
494
495 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
496 p_msg_name => G_UNEXPECTED_ERROR,
497 p_token1 => G_SQLCODE_TOKEN,
498 p_token1_value => SQLCODE,
499 p_token2 => G_SQLERRM_TOKEN,
500 p_token2_value => SQLERRM);
501
502 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
503
504 END get_pvt_label_email;
505
506
507 ------------------------------------------------------------------------------
508 -- FUNCTION get_try_id
509 ------------------------------------------------------------------------------
510 -- Created by : RFEDANE
511 --
512 -- Purpose:
513 -- Private Procedure to retrieve ID of a given Transaction Type
514 --
515 -- Known limitations/enhancements and/or remarks:
516 --
517 ------------------------------------------------------------------------------
518
519 FUNCTION get_try_id (p_try_name IN VARCHAR2) RETURN NUMBER IS
520
521 CURSOR c_try IS
522 SELECT id
523 FROM okl_trx_types_tl
524 WHERE name = p_try_name
525 AND language = 'US';
526
527 l_try_id NUMBER;
528
529 BEGIN
530
531 OPEN c_try;
532 FETCH c_try INTO l_try_id;
533 CLOSE c_try;
534
535 RETURN l_try_id;
536
537 EXCEPTION
538
539 WHEN OTHERS THEN
540
541 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
542 p_msg_name => G_UNEXPECTED_ERROR,
543 p_token1 => G_SQLCODE_TOKEN,
544 p_token1_value => SQLCODE,
545 p_token2 => G_SQLERRM_TOKEN,
546 p_token2_value => SQLERRM);
547
548 END get_try_id;
549
550
551 ------------------------------------------------------------------------------
552 -- FUNCTION get_sty_id
553 ------------------------------------------------------------------------------
554 -- Created by : RFEDANE
555 --
556 -- Purpose:
557 -- Private Procedure to retrieve ID of a given Stream Type
558 --
559 -- Known limitations/enhancements and/or remarks:
560 --
561 ------------------------------------------------------------------------------
562
563 FUNCTION get_sty_id (p_sty_purpose IN VARCHAR2, p_khr_id in NUMBER) RETURN NUMBER IS
564
565 /*CURSOR c_sty IS
566 SELECT sty.id
567 FROM okl_strm_type_tl styt, okl_strm_type_b sty
568 WHERE styt.name = p_sty_name
569 AND styt.language = 'US'
570 AND sty.id = styt.id
571 AND sty.start_date <= TRUNC(SYSDATE)
572 AND NVL(sty.end_date, SYSDATE) >= TRUNC(SYSDATE);*/
573
574 l_sty_id NUMBER;
575 l_return_status varchar2(10);
576
577 BEGIN
578
579 /*OPEN c_sty;
580 FETCH c_sty INTO l_sty_id;
581 CLOSE c_sty;*/
582
583 -- Stream id got from Streams util API passing the purpose and contract id
584 -- changes done for user defined streams, bug 3924303
585 OKL_STREAMS_UTIL.get_primary_stream_type(p_khr_id,
586 p_sty_purpose,
587 l_return_status,
588 l_sty_id);
589
590 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
591 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
592 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
593 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
594 p_msg_name => 'OKL_NO_STREAM_TYPE',
595 p_token1 => OKL_API.G_COL_NAME_TOKEN,
596 p_token1_value => p_sty_purpose);
597
598 RAISE OKC_API.G_EXCEPTION_ERROR;
599 END IF;
600
601 RETURN l_sty_id;
602
603 EXCEPTION
604
605 WHEN OTHERS THEN
606
607 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
608 p_msg_name => G_UNEXPECTED_ERROR,
609 p_token1 => G_SQLCODE_TOKEN,
610 p_token1_value => SQLCODE,
611 p_token2 => G_SQLERRM_TOKEN,
612 p_token2_value => SQLERRM);
613
614 END get_sty_id;
615
616
617 ------------------------------------------------------------------------------
618 -- FUNCTION get_svf_id
619 ------------------------------------------------------------------------------
620 -- Created by : RFEDANE
621 --
622 -- Purpose:
623 -- Private Procedure to retrieve ID of a given Service Fee Code
624 --
625 -- Known limitations/enhancements and/or remarks:
626 --
627 ------------------------------------------------------------------------------
628
629 FUNCTION get_svf_id (p_svf_code IN VARCHAR2) RETURN NUMBER IS
630
631 CURSOR c_svf IS
632 SELECT id
633 FROM okl_service_fees_b
634 WHERE srv_code = p_svf_code
635 AND start_date <= TRUNC(SYSDATE)
636 AND NVL(end_date, SYSDATE) >= TRUNC(SYSDATE);
637
638 l_svf_id NUMBER;
639
640 BEGIN
641
642 OPEN c_svf;
643 FETCH c_svf INTO l_svf_id;
644 CLOSE c_svf;
645
646 RETURN l_svf_id;
647
648 EXCEPTION
649
650 WHEN OTHERS THEN
651
652 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
653 p_msg_name => G_UNEXPECTED_ERROR,
654 p_token1 => G_SQLCODE_TOKEN,
655 p_token1_value => SQLCODE,
656 p_token2 => G_SQLERRM_TOKEN,
657 p_token2_value => SQLERRM);
658
659 END get_svf_id;
660
661
662 ------------------------------------------------------------------------------
663 -- FUNCTION get_pdt_id
664 ------------------------------------------------------------------------------
665 -- Created by : RFEDANE
666 --
667 -- Purpose:
668 -- Private Procedure to retrieve Product ID of a given Contract
669 --
670 -- Known limitations/enhancements and/or remarks:
671 --
672 ------------------------------------------------------------------------------
673
674 FUNCTION get_pdt_id (p_khr_id IN VARCHAR2) RETURN NUMBER IS
675
676 CURSOR c_pdt IS
677 SELECT pdt_id
678 FROM okl_k_headers
679 WHERE id = p_khr_id;
680
681 l_pdt_id NUMBER;
682
683 BEGIN
684
685 OPEN c_pdt;
686 FETCH c_pdt INTO l_pdt_id;
687 CLOSE c_pdt;
688
689 RETURN l_pdt_id;
690
691 EXCEPTION
692
693 WHEN OTHERS THEN
694
695 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
696 p_msg_name => G_UNEXPECTED_ERROR,
697 p_token1 => G_SQLCODE_TOKEN,
698 p_token1_value => SQLCODE,
699 p_token2 => G_SQLERRM_TOKEN,
700 p_token2_value => SQLERRM);
701
702 END get_pdt_id;
703
704
705 ------------------------------------------------------------------------------
706 -- FUNCTION get_factor_synd
707 ------------------------------------------------------------------------------
708 -- Created by : RFEDANE
709 --
710 -- Purpose:
711 -- Returns NULL if Contract is neither syndicated nor factored. Ortherwise returns
712 -- the appropriate value.
713 --
714 -- Known limitations/enhancements and/or remarks:
715 --
716 ------------------------------------------------------------------------------
717
718 FUNCTION get_factor_synd(p_khr_id IN NUMBER) RETURN VARCHAR2 IS
719
720 CURSOR c_synd IS
721 SELECT scs_code
722 FROM okc_k_headers_b
723 WHERE scs_code = 'SYNDICATION'
724 AND id = p_khr_id;
725
726 CURSOR c_fact IS
727 SELECT 1
728 FROM okc_rules_b
729 WHERE dnz_chr_id = p_khr_id
730 AND rule_information_category = 'LAFCTG';
731
732 l_contract_type VARCHAR2(30);
733
734 BEGIN
735
736 OPEN c_synd;
737 FETCH c_synd INTO l_contract_type;
738 CLOSE c_synd;
739
740 IF l_contract_type IS NOT NULL THEN
741 RETURN l_contract_type;
742 END IF;
743
744 OPEN c_fact;
745 FETCH c_fact INTO l_contract_type;
746 CLOSE c_fact;
747
748 IF l_contract_type IS NOT NULL THEN
749 l_contract_type := 'FACTORING';
750 RETURN l_contract_type;
751 END IF;
752
753 RETURN NULL;
754
755 EXCEPTION
756
757 WHEN OTHERS THEN
758
759 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
760 p_msg_name => G_UNEXPECTED_ERROR,
761 p_token1 => G_SQLCODE_TOKEN,
762 p_token1_value => SQLCODE,
763 p_token2 => G_SQLERRM_TOKEN,
764 p_token2_value => SQLERRM);
765
766 END get_factor_synd;
767
768
769 ------------------------------------------------------------------------------
770 -- PROCEDURE create_svf_invoice
771 ------------------------------------------------------------------------------
772 -- Created by : RFEDANE
773 --
774 -- Purpose:
775 -- Create an Invoice Entry in the Internal OKL Invoice tables
776 --
777 -- Known limitations/enhancements and/or remarks:
778 -- Changes
779 -- 29-Oct-2004 kpvs Bug 3924303, User defined streams changes
780 -- stream type purpose is passed into the parameter
781 -- p_sty_name instead of stream type name
782 ------------------------------------------------------------------------------
783
784 PROCEDURE create_svf_invoice (p_khr_id IN NUMBER,
785 p_sty_name IN VARCHAR2,
786 p_svf_code IN VARCHAR2,
787 p_svf_amount IN NUMBER,
788 p_svf_desc IN VARCHAR2,
789 p_syndication_code IN VARCHAR2,
790 p_factoring_code IN VARCHAR2,
791 x_tai_id OUT NOCOPY NUMBER,
792 x_return_status OUT NOCOPY VARCHAR2,
793 x_msg_count OUT NOCOPY NUMBER,
794 x_msg_data OUT NOCOPY VARCHAR2) IS
795
796 l_sysdate DATE;
797 l_khr_id NUMBER;
798 l_sty_name VARCHAR2(150);
799 l_sty_purpose VARCHAR2(150);
800 l_svf_code VARCHAR2(30);
801 l_svf_amount NUMBER;
802 l_svf_desc VARCHAR2(4000);
803
804 l_try_id NUMBER;
805 l_sty_id NUMBER;
806 l_svf_id NUMBER;
807 l_pdt_id NUMBER;
808 l_factoring_synd VARCHAR2(30);
809 l_syndication_code VARCHAR2(30);
810 l_factoring_code VARCHAR2(30);
811
812 l_api_version CONSTANT NUMBER := 1;
813 l_init_msg_list CONSTANT CHAR := 'F';
814 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
815 l_line_number CONSTANT NUMBER := 1;
816
817 -- Invoice Header
818 i_taiv_rec okl_trx_ar_invoices_pub.taiv_rec_type;
819 r_taiv_rec okl_trx_ar_invoices_pub.taiv_rec_type;
820
821 -- Invoice Line
822 i_tilv_rec okl_txl_ar_inv_lns_pub.tilv_rec_type;
823 r_tilv_rec okl_txl_ar_inv_lns_pub.tilv_rec_type;
824
825 -- Accouting Engine (AE) Records
826 l_tmpl_identify_rec OKL_ACCOUNT_DIST_PUB.TMPL_IDENTIFY_REC_TYPE;
827 l_dist_info_rec OKL_ACCOUNT_DIST_PUB.DIST_INFO_REC_TYPE;
828 l_ctxt_val_tbl OKL_ACCOUNT_DIST_PUB.CTXT_VAL_TBL_TYPE;
829 l_acc_gen_primary_key_tbl OKL_ACCOUNT_DIST_PUB.ACC_GEN_PRIMARY_KEY;
830 lx_template_tbl OKL_ACCOUNT_DIST_PUB.AVLV_TBL_TYPE;
831 lx_amount_tbl OKL_ACCOUNT_DIST_PUB.AMOUNT_TBL_TYPE;
832
833 --Added for bug 4122293
834 l_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
835
836 BEGIN
837
838 l_syndication_code := p_syndication_code;
839 l_factoring_code := p_factoring_code;
840
841 IF (l_syndication_code IS NOT NULL) AND (l_factoring_code IS NOT NULL) THEN
842 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
843 p_msg_name => 'OKL_SYND_FACTOR_EXCLUSIVE');
844 RAISE OKL_API.G_EXCEPTION_ERROR;
845 END IF;
846
847 l_sysdate := TRUNC(SYSDATE);
848 l_khr_id := p_khr_id;
849 l_sty_purpose := p_sty_name;
850 l_svf_code := p_svf_code;
851 l_svf_amount := p_svf_amount;
852 l_svf_desc := p_svf_desc;
853
854 l_try_id := get_try_id ('Billing');
855 l_sty_id := get_sty_id (l_sty_purpose,l_khr_id);
856 l_svf_id := get_svf_id (l_svf_code);
857 l_pdt_id := get_pdt_id (l_khr_id);
858
859 IF l_pdt_id IS NULL THEN
860 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
861 p_msg_name => 'OKL_NO_PRODUCT_FOUND');
862 RAISE OKL_API.G_EXCEPTION_ERROR;
863 END IF;
864
865 --l_factoring_synd := get_factor_synd(l_khr_id);
866 l_factoring_synd := NULL; -- BUG 2451833
867 ----------------------------------------------------------------------------------
868 -- Preparing Invoice Header. Assumption: Charge will be to Primary Leasee
869 ----------------------------------------------------------------------------------
870 i_taiv_rec.try_id := l_try_id;
871 i_taiv_rec.khr_id := l_khr_id;
872 i_taiv_rec.date_entered := l_sysdate;
873 i_taiv_rec.date_invoiced := l_sysdate;
874 i_taiv_rec.description := l_svf_desc;
875 i_taiv_rec.amount := l_svf_amount;
876 i_taiv_rec.trx_status_code := 'SUBMITTED';
877 i_taiv_rec.svf_id := l_svf_id;
878 i_taiv_rec.legal_entity_id := okl_legal_entity_util.get_khr_le_id(l_khr_id); --dkagrawa populated le_id
879
880 ----------------------------------------------------------------------------------
881 -- May be useful to other functional areas. Not populated for now.
882 ----------------------------------------------------------------------------------
883 i_taiv_rec.cra_id := NULL; -- OKL_CURE_REP_AMTS_V
884 i_taiv_rec.qte_id := NULL; -- OKL_TRX_QUOTES_V
885 i_taiv_rec.tcn_id := NULL; -- OKL_TRX_CONTRACTS
886 i_taiv_rec.ipy_id := NULL; -- OKL_INS_POLICIES_V
887 i_taiv_rec.tap_id := NULL; -- OKL_TRX_AP_INVOICES_V
888
889 ----------------------------------------------------------------------------------
890 -- Insert Invoice Header record
891 ----------------------------------------------------------------------------------
892 okl_trx_ar_invoices_pub.insert_trx_ar_invoices(p_api_version => l_api_version,
893 p_init_msg_list => l_init_msg_list,
894 x_return_status => l_return_status,
895 x_msg_count => x_msg_count,
896 x_msg_data => x_msg_data,
897 p_taiv_rec => i_taiv_rec,
898 x_taiv_rec => r_taiv_rec);
899
900 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
901 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
902 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
903 RAISE OKL_API.G_EXCEPTION_ERROR;
904 END IF;
905
906 ----------------------------------------------------------------------------------
907 -- Prepare Invoice Line
908 ----------------------------------------------------------------------------------
909 i_tilv_rec.line_number := l_line_number;
910 i_tilv_rec.tai_id := r_taiv_rec.id;
911 i_tilv_rec.description := l_svf_desc;
912 i_tilv_rec.amount := r_taiv_rec.amount;
913 i_tilv_rec.sty_id := l_sty_id;
914
915 -- this field is passed as invoice description in AR
916 -- you can actually put 'LINE' or 'CHARGE'
917 -- 'CHARGE' is used for financial charges and has some accounting
918 -- implications in AR; till further notice please always use LINE
919
920 i_tilv_rec.inv_receiv_line_code := 'LINE';
921
922 ----------------------------------------------------------------------------------
923 -- Insert transaction line record
924 ----------------------------------------------------------------------------------
925
926 okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (p_api_version => l_api_version,
927 p_init_msg_list => l_init_msg_list,
928 x_return_status => l_return_status,
929 x_msg_count => x_msg_count,
930 x_msg_data => x_msg_data,
931 p_tilv_rec => i_tilv_rec,
932 x_tilv_rec => r_tilv_rec);
933
934 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
935 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
936 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
937 RAISE OKL_API.G_EXCEPTION_ERROR;
938 END IF;
939
940 --Commented the following lines to call the Accounting Wrapper API.
941 --The commented call does not take into account the dynamically
942 --generated Accounts. it works only if the Static accounts are specified.
943 --The new call handles this case.
944 --This was fixed for Credit memo via bug fix 3843941
945 --The same code is being fixed in this API via bug 4122293
946
947 /*
948 ----------------------------------------------------------------------------------
949 -- Prepare OKL Accouting Engine parameters
950 ----------------------------------------------------------------------------------
951
952 l_tmpl_identify_rec.PRODUCT_ID := l_pdt_id;
953 l_tmpl_identify_rec.TRANSACTION_TYPE_ID := l_try_id;
954 l_tmpl_identify_rec.STREAM_TYPE_ID := l_sty_id;
955 l_tmpl_identify_rec.ADVANCE_ARREARS := NULL;
956 l_tmpl_identify_rec.FACTORING_SYND_FLAG := l_factoring_synd;
957 l_tmpl_identify_rec.SYNDICATION_CODE := l_syndication_code;
958 l_tmpl_identify_rec.FACTORING_CODE := l_factoring_code;
959 l_tmpl_identify_rec.MEMO_YN := 'N';
960 l_tmpl_identify_rec.PRIOR_YEAR_YN := 'N';
961
962 l_dist_info_rec.SOURCE_ID := r_tilv_rec.id;
963 l_dist_info_rec.SOURCE_TABLE := 'OKL_TXL_AR_INV_LNS_B';
964 l_dist_info_rec.ACCOUNTING_DATE := l_sysdate;
965 l_dist_info_rec.GL_REVERSAL_FLAG := 'N';
966 l_dist_info_rec.POST_TO_GL := 'N';
967 l_dist_info_rec.AMOUNT := l_svf_amount;
968 l_dist_info_rec.CURRENCY_CODE := NULL;
969 l_dist_info_rec.CURRENCY_CONVERSION_TYPE := NULL;
970 l_dist_info_rec.CURRENCY_CONVERSION_DATE := NULL;
971 l_dist_info_rec.CONTRACT_ID := l_khr_id;
972 l_dist_info_rec.CONTRACT_LINE_ID := NULL;
973
974 l_ctxt_val_tbl(1).name := NULL;
975 l_ctxt_val_tbl(1).value := NULL;
976
977 l_acc_gen_primary_key_tbl(1).source_table := 'RA_CUST_TRX_TYPES';
978 l_acc_gen_primary_key_tbl(1).primary_key_column := 'CUSTOMER_TRX_ID';
979
980 okl_account_dist_pub.create_accounting_dist(p_api_version => l_api_version,
981 p_init_msg_list => l_init_msg_list,
982 x_return_status => l_return_status,
983 x_msg_count => x_msg_count,
984 x_msg_data => x_msg_data,
985 p_tmpl_identify_rec => l_tmpl_identify_rec,
986 p_dist_info_rec => l_dist_info_rec,
987 p_ctxt_val_tbl => l_ctxt_val_tbl,
988 p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl,
989 x_template_tbl => lx_template_tbl,
990 x_amount_tbl => lx_amount_tbl);
991
992 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
993 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
994 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
995 RAISE OKL_API.G_EXCEPTION_ERROR;
996 END IF;
997
998 */
999
1000 --New call to call the BPD wrapper API.
1001 ------------------------------------------------------
1002 -- Create distributions using Accounting Engine
1003 ------------------------------------------------------
1004 l_bpd_acc_rec.id := r_tilv_rec.id;
1005 l_bpd_acc_rec.source_table := 'OKL_TXL_AR_INV_LNS_B';
1006 ----------------------------------------------------
1007 -- Create Accounting Distributions
1008 ----------------------------------------------------
1009 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(
1010 p_api_version => l_api_version
1011 ,p_init_msg_list => l_init_msg_list
1012 ,x_return_status => l_return_status
1013 ,x_msg_count => x_msg_count
1014 ,x_msg_data => x_msg_data
1015 ,p_bpd_acc_rec => l_bpd_acc_rec);
1016
1017
1018 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1019 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1020 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1021 RAISE OKL_API.G_EXCEPTION_ERROR;
1022 END IF;
1023
1024
1025 x_tai_id := r_taiv_rec.id;
1026 x_return_status := l_return_status;
1027
1028
1029 EXCEPTION
1030
1031 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1032
1033 x_return_status := OKL_API.G_RET_STS_ERROR;
1034
1035 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1036
1037 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1038
1039 WHEN OTHERS THEN
1040
1041 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1042 p_msg_name => G_UNEXPECTED_ERROR,
1043 p_token1 => G_SQLCODE_TOKEN,
1044 p_token1_value => SQLCODE,
1045 p_token2 => G_SQLERRM_TOKEN,
1046 p_token2_value => SQLERRM);
1047
1048 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1049
1050 END create_svf_invoice;
1051
1052 END okl_cs_transactions_pvt;