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