[Home] [Help]
PACKAGE BODY: APPS.OKL_INVESTOR_BILLING_PVT
Source
1 PACKAGE BODY Okl_Investor_Billing_Pvt AS
2 /* $Header: OKLRBCAB.pls 120.17 2007/12/26 10:05:15 kthiruva noship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- FUNCTION get_seq_id
7 ---------------------------------------------------------------------------
8 FUNCTION get_seq_id RETURN NUMBER IS
9 BEGIN
10 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
11 END get_seq_id;
12
13
14 ------------------------------------------------------------------
15 -- Procedure create_billing_transaction to bill investor
16 -- transactions
17 ------------------------------------------------------------------
18 PROCEDURE create_investor_bill
19 (p_api_version IN NUMBER
20 ,p_init_msg_list IN VARCHAR2
21 ,x_return_status OUT NOCOPY VARCHAR2
22 ,x_msg_count OUT NOCOPY NUMBER
23 ,x_msg_data OUT NOCOPY VARCHAR2
24 ,p_inv_agr IN NUMBER
25 ,p_investor_line_id IN NUMBER
26 )
27 IS
28
29
30
31 ------------------------------------------------------------
32 -- Declare variables required by APIs
33 ------------------------------------------------------------
34 l_api_version CONSTANT NUMBER := 1;
35 l_api_name CONSTANT VARCHAR2(30) := 'create_investor_bill';
36 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
37
38 -- nikshah -- Bug # 5484903 Fixed,
39 -- Changed bill_invstr_csr ( p_inv_agr NUMBER ) SQL definition
40 ------------------------------------------------------------
41 -- Get Investors and Investment amount to bill
42 ------------------------------------------------------------
43 CURSOR bill_invstr_csr ( p_inv_agr NUMBER ) IS
44 SELECT KH1.contract_number Investor_Agreement,
45 KH1.id Investor_Agreement_id,
46 KH1.pdt_id pdt_id,
47 KH1.currency_code,
48 KH1.currency_conversion_type,
49 KH1.currency_conversion_rate,
50 KH1.currency_conversion_date,
51 KH1.authoring_org_id org_id,
52 PARTY.name Investor_Name,
53 PARTY.description Investor_Description,
54 --PARTY.id1 Investor_Id,
55 TOP_LINE.cust_acct_id Investor_Id,
56 PARTY.id2 Investor_Id2,
57 TOP_LINE.ID TOP_LINE_ID,
58 TOP_KLE.AMOUNT,
59 TOP_LINE.START_DATE,
60 nvl(TOP_KLE.AMOUNT_STAKE,0) AMOUNT_STAKE
61 FROM
62 OKL_K_HEADERS_FULL_V KH1,
63 OKC_K_LINES_B TOP_LINE,
64 OKL_K_LINES TOP_KLE,
65 OKC_K_PARTY_ROLES_B PARTY_ROLE,
66 OKX_PARTIES_V PARTY,
67 OKC_LINE_STYLES_B LSEB,
68 OKC_STATUSES_V STS
69 WHERE
70 KH1.SCS_CODE = 'INVESTOR' AND
71 -- KH1.STS_CODE = 'ACTIVE' AND
72 KH1.id = p_inv_agr AND
73 TOP_LINE.dnz_chr_id = KH1.id AND
74 -- TOP_LINE.CLE_ID IS NULL AND
75 -- TOP_LINE.STS_CODE = 'ACTIVE' AND
76 TOP_KLE.ID = NVL(p_investor_line_id,TOP_KLE.ID) AND
77 TOP_KLE.ID = TOP_LINE.ID AND
78 PARTY_ROLE.cle_id = TOP_LINE.id AND
79 PARTY_ROLE.dnz_chr_id = TOP_LINE.dnz_chr_id AND
80 PARTY_ROLE.rle_code = 'INVESTOR' AND
81 PARTY_ROLE.jtot_object1_code = 'OKX_PARTY' AND
82 PARTY.id1 = PARTY_ROLE.object1_id1 AND
83 PARTY.id2 = PARTY_ROLE.object1_id2 AND
84 LSEB.ID = TOP_LINE.lse_id AND
85 LSEB.lty_code = 'INVESTMENT' AND
86 STS.CODE = TOP_LINE.sts_code;
87
88 ------------------------------------------------------------
89 -- Get Investor bill to Site
90 ------------------------------------------------------------
91 --COMMENTED OUT FOR RULES MIGRATION
92 /*CURSOR bill_site_rul_csr( p_invstr_agr_id NUMBER, p_top_line_id NUMBER ) IS
93 SELECT object1_id1
94 FROM OKC_RULES_B rul,
95 Okc_rule_groups_B rgp
96 WHERE rul.rgp_id = rgp.id AND
97 rgp.rgd_code = 'LABILL' AND
98 rgp.cle_id = p_top_line_id AND
99 rul.rule_information_category = 'BTO' AND
100 rgp.dnz_chr_id = p_invstr_agr_id;*/
101
102 --ADDED FOR RULES MIGRATION
103 CURSOR bill_site_rul_csr( p_invstr_agr_id NUMBER, p_top_line_id NUMBER ) IS
104 SELECT B.cust_acct_site_id
105 FROM okc_k_lines_b A
106 ,okx_cust_site_uses_v B
107 WHERE /*A.chr_id = p_invstr_agr_id
108 and A.cle_id IS NULL
109 and*/ A.id = p_top_line_id
110 and A.bill_to_site_use_id = B.id1;
111
112 --COMMENTED OUT FOR RULES MIGRATION
113 /*CURSOR bill_site_csr(p_id1 NUMBER, p_party_id NUMBER ) IS
114 SELECT cust_acct_site_id
115 FROM okx_cust_site_uses_v
116 WHERE id1 = p_id1 AND
117 PARTY_ID = p_party_id;*/
118
119 -- nikshah -- Bug # 5484903 Fixed,
120 -- Changed CURSOR std_terms_csr SQL definition
121 ------------------------------------------------------------
122 -- Get Term Id
123 ------------------------------------------------------------
124 CURSOR std_terms_csr IS
125 SELECT B.TERM_ID
126 FROM RA_TERMS_TL T, RA_TERMS_B B
127 WHERE B.TERM_ID = T.TERM_ID
128 and T.LANGUAGE = userenv('LANG')
129 and T.name = 'IMMEDIATE';
130
131 ------------------------------------------------------------
132 -- Get Receipt Method Id
133 ------------------------------------------------------------
134 CURSOR rcpt_mthd_rul_csr( p_invstr_agr_id NUMBER, p_top_line_id NUMBER ) IS
135 SELECT object1_id1
136 FROM OKC_RULES_B rul,
137 Okc_rule_groups_B rgp
138 WHERE rul.rgp_id = rgp.id AND
139 rgp.rgd_code = 'LABILL' AND
140 rul.rule_information_category = 'LAPMTH' AND
141 rgp.cle_id = p_top_line_id AND
142 rgp.dnz_chr_id = p_invstr_agr_id;
143
144 CURSOR rcpt_mthd_csr (p_id1 NUMBER, p_cust_id NUMBER) IS
145 SELECT receipt_method_id
146 FROM okx_receipt_methods_v
147 WHERE id1 = p_id1 AND
148 customer_id = p_cust_id;
149
150 CURSOR pass_or_not_csr ( p_rct_method_id NUMBER) IS
151 SELECT C.CREATION_METHOD_CODE
152 FROM AR_RECEIPT_METHODS M,
153 AR_RECEIPT_CLASSES C
154 WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
155 M.receipt_method_id = p_rct_method_id;
156
157 ------------------------------------------------------------
158 -- Get Bank Account Id
159 ------------------------------------------------------------
160 CURSOR bank_acct_rul_csr( p_invstr_agr_id NUMBER, p_top_line_id NUMBER ) IS
161 SELECT object1_id1
162 FROM OKC_RULES_B rul,
163 Okc_rule_groups_B rgp
164 WHERE rul.rgp_id = rgp.id AND
165 rgp.rgd_code = 'LABILL' AND
166 rgp.cle_id = p_top_line_id AND
167 rul.rule_information_category = 'LABACC' AND
168 rgp.dnz_chr_id = p_invstr_agr_id;
169
170 CURSOR bank_acct_id_csr( p_id1 NUMBER ) IS
171 SELECT bank_account_id
172 FROM OKX_RCPT_METHOD_ACCOUNTS_V
173 WHERE id1 = p_id1;
174
175 ------------------------------------------------------------
176 -- Get trx_id
177 ------------------------------------------------------------
178 CURSOR c_trx_id( p_sob_id NUMBER, p_org_id NUMBER ) IS
179 SELECT ID1
180 FROM OKX_CUST_TRX_TYPES_V
181 WHERE name = 'Investor-OKL' AND
182 set_of_books_id = p_sob_id AND
183 org_id = p_org_id;
184
185 ------------------------------------------------------------
186 -- Get trx_type_id
187 ------------------------------------------------------------
188 CURSOR c_trx_type IS
189 SELECT id
190 FROM okl_trx_types_tl
191 WHERE name = 'Billing'
192 AND LANGUAGE = 'US';
193
194 ------------------------------------------------------------
195 -- Get sty_id
196 ------------------------------------------------------------
197 CURSOR c_sty_id(cp_sty_id IN NUMBER) IS
198 --SELECT id
199 SELECT taxable_default_yn
200 FROM okl_strm_type_v
201 WHERE id = cp_sty_id;
202
203 /* ankushar OKL R12B Billing Changes
204 start Code Comment
205 ------------------------------------------------------------
206 -- Create Distributions
207 ------------------------------------------------------------
208 CURSOR dstrs_csr( p_pdt_id NUMBER, p_try_id NUMBER, p_sty_id NUMBER, p_inv_code VARCHAR2) IS
209 SELECT
210 C.CODE_COMBINATION_ID,
211 C.AE_LINE_TYPE,
212 C.CRD_CODE,
213 C.ACCOUNT_BUILDER_YN,
214 C.PERCENTAGE
215 FROM OKL_AE_TEMPLATES A,
216 OKL_PRODUCTS_V B,
217 OKL_AE_TMPT_LNES C
218 WHERE A.aes_id = b.aes_id AND
219 A.start_date <= sysdate AND
220 (A.end_date IS NULL OR A.end_date >= sysdate) AND
221 A.memo_yn = 'N' AND
222 -- #4643924 added filter on special accounting code
223 NVL(A.FACTORING_SYND_FLAG,'INVESTOR') = 'INVESTOR' AND
224 NVL(A.INV_CODE, '-9999') = NVL(p_inv_code,'-9999') AND
225 b.id = p_pdt_id AND
226 a.sty_id = p_sty_id AND
227 a.try_id = p_try_id AND
228 C.avl_id = A.id;
229 -- end Code comment
230 ankushar Billing Changes */
231
232 -- BEGIN bvaghela 032305 bug 4256274 --
233 CURSOR sales_rep_csr IS
234 SELECT SALESREP_ID, SALESREP_NUMBER
235 FROM RA_SALESREPS
236 WHERE NAME = 'No Sales Credit';
237
238
239 CURSOR sales_type_credit_csr IS
240 SELECT SALES_CREDIT_TYPE_ID
241 FROM SO_SALES_CREDIT_TYPES
242 WHERE NAME = 'Quota Sales Credit';
243 -- END bvaghela 032305 bug 4256274 --
244
245 --Added by kthiruva for Bug 6691554
246 CURSOR get_ia_sts_csr(p_khr_id NUMBER)
247 IS
248 SELECT khr.STS_CODE
249 FROM okc_k_headers_all_b khr
250 WHERE khr.ID = p_khr_id
251 AND khr.SCS_CODE = 'INVESTOR';
252
253 /*
254 ankushar code commented, to be moved in the common billing API.
255 ------------------------------------------------------------
256 -- Get special accounting code
257 ------------------------------------------------------------
258 -- bug#4643924 start cursor to fetch the special accounting code
259 CURSOR spl_acct_code_rul_csr( p_invstr_agr_id NUMBER) IS
260 select rul.rule_information1 investor_code
261 from okc_rule_groups_b rgp, okc_rules_b rul
262 where rgp.chr_id = rgp.dnz_chr_id
263 and rgp.dnz_chr_id = p_invstr_agr_id
264 and rgp.rgd_code = 'LASEAC'
265 and rul.dnz_chr_id = rgp.dnz_chr_id
266 and rul.rgp_id = rgp.id
267 and rul.rule_information_category = 'LASEAC';
268 */
269
270 ------------------------------------------------------------
271 -- Local Variables
272 ------------------------------------------------------------
273
274 -- BEGIN bvaghela 032305 bug 4256274 --
275 l_salesrep_id ra_salesreps.SALESREP_ID%TYPE;
276 l_salesrep_number ra_salesreps.SALESREP_NUMBER%TYPE;
277 l_sales_type_credit so_sales_credit_types.sales_credit_type_id%TYPE;
278 l_user_id NUMBER := FND_global.user_id;
279 l_sysdate DATE := sysdate;
280 -- END bvaghela 032305 bug 4256274 --
281
282 l_customer_id NUMBER;
283 l_cust_site_id NUMBER;
284 l_terms NUMBER;
285 l_receipt_method_id NUMBER;
286 l_how_created AR_RECEIPT_CLASSES.creation_method_code%TYPE;
287 l_bank_acct_id NUMBER;
288 l_cust_trx_id NUMBER;
289
290 l_unique_id NUMBER;
291
292 l_site_id1 NUMBER;
293 l_rcpt_id1 NUMBER;
294 l_bank_id1 NUMBER;
295
296 -- Multi Currency Compliance
297 l_currency_code okl_k_headers_full_v.currency_code%type;
298 l_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%type;
299 l_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%type;
300 l_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%type;
301
302 /* ankushar Billing Enhancement changes
303 start code changes */
304 -----------------------------------------------------------
305 -- Variables for billing API call
306 -----------------------------------------------------------
307 lp_taiv_rec okl_tai_pvt.taiv_rec_type;
308 lp_tilv_tbl okl_til_pvt.tilv_tbl_type;
309 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type;
310 lp_tilv_rec okl_til_pvt.tilv_rec_type;
311 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
312 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
313 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
314
315 /* ankushar : end code changes */
316
317 ------------------------------------------------------------
318 -- Variables for accounting Engine
319 ------------------------------------------------------------
320 l_template_tbl OKL_ACCOUNT_DIST_PVT.avlv_tbl_type;
321 l_init_template_tbl OKL_ACCOUNT_DIST_PVT.avlv_tbl_type;
322 l_tmpl_id_rec OKL_ACCOUNT_DIST_PVT.TMPL_IDENTIFY_REC_TYPE;
323 l_init_tmpl_id_rec OKL_ACCOUNT_DIST_PVT.TMPL_IDENTIFY_REC_TYPE;
324
325 l_try_id okl_trx_types_tl.id%TYPE;
326 l_sty_id okl_strm_type_v.id%TYPE;
327 l_taxable_yn okl_strm_type_v.taxable_default_yn%TYPE;
328
329 l_amount NUMBER;
330
331 l_distr_cnt NUMBER;
332 l_distr_err BOOLEAN := FALSE;
333 l_cc_id ra_interface_distributions_all.CODE_COMBINATION_ID%TYPE;
334
335 -------------------------------------------------------------------------
336 -- Account Builder Code
337 -------------------------------------------------------------------------
338 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
339 l_init_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
340
341 l_acc_gen_wf_sources_rec OKL_ACCOUNT_GENERATOR_pvt.acc_gen_wf_sources_rec;
342
343 l_inv_code okc_rules_b.RULE_INFORMATION1%TYPE;
344
345 -------------------------------------------------------------------------
346 -- Legal Entity
347 -------------------------------------------------------------------------
348
349 l_legal_entity_id RA_INTERFACE_LINES.LEGAL_ENTITY_ID%TYPE; -- for LE Uptake project 08-11-2006
350 --Added by kthiruva for bug 6691554
351 l_status_code OKC_K_HEADERS_ALL_B.STS_CODE%TYPE;
352
353 BEGIN
354 ------------------------------------------------------------
355 -- Start processing
356 ------------------------------------------------------------
357 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
358
359 l_return_status := Okl_Api.START_ACTIVITY(
360 p_api_name => l_api_name,
361 p_pkg_name => G_PKG_NAME,
362 p_init_msg_list => p_init_msg_list,
363 l_api_version => l_api_version,
364 p_api_version => p_api_version,
365 p_api_type => '_PVT',
366 x_return_status => l_return_status);
367
368 -------------------------------------------
369 -- Fetch Transaction Type Id
370 -------------------------------------------
371 OPEN c_trx_type;
372 FETCH c_trx_type INTO l_try_id;
373 CLOSE c_trx_type;
374
375 -------------------------------------------
376 -- Fetch Stream Type Id
377 -------------------------------------------
378 OKL_STREAMS_UTIL.get_primary_stream_type(p_khr_id => p_inv_agr
379 ,p_primary_sty_purpose => 'INVESTOR_RECEIVABLE'
380 ,x_return_status => l_return_status
381 ,x_primary_sty_id => l_sty_id);
382
383 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
384 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: INVESTOR_RECEIVABLE.');
385 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
386 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
387 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: INVESTOR_RECEIVABLE.');
388 RAISE Okl_Api.G_EXCEPTION_ERROR;
389 END IF;
390
391 OPEN c_sty_id(cp_sty_id=>l_sty_id);
392 --FETCH c_sty_id INTO l_sty_id;
393 FETCH c_sty_id INTO l_taxable_yn;
394 CLOSE c_sty_id;
395
396 --Fetch the investor agreement status
397 FOR get_ia_sts_rec IN get_ia_sts_csr(p_inv_agr)
398 LOOP
399 l_status_code := get_ia_sts_rec.sts_code;
400 END LOOP;
401
402 FOR invstr_rec IN bill_invstr_csr ( p_inv_agr ) LOOP
403
404 -- Null out variables
405 l_customer_id := NULL;
406 l_cust_site_id := NULL;
407 l_terms := NULL;
408 l_receipt_method_id := NULL;
409 l_how_created := NULL;
410 l_bank_acct_id := NULL;
411 l_cust_trx_id := NULL;
412 l_site_id1 := NULL;
413 l_rcpt_id1 := NULL;
414 l_bank_id1 := NULL;
415 l_unique_id := NULL;
416
417 l_currency_code := NULL;
418 l_currency_conversion_type := NULL;
419 l_currency_conversion_rate := NULL;
420 l_currency_conversion_date := NULL;
421
422 ---------------------------------------------------
423 -- populate variables
424 ---------------------------------------------------
425
426 -- Customer Id
427 l_customer_id := invstr_rec.investor_id;
428
429 -- Customer Bill to Site
430 --COMMENTED OUT FOR RULES MIGRATION
431 /*OPEN bill_site_rul_csr( invstr_rec.Investor_Agreement_id, invstr_rec.TOP_LINE_ID );
432 FETCH bill_site_rul_csr INTO l_site_id1;
433 CLOSE bill_site_rul_csr;
434
435 OPEN bill_site_csr( l_site_id1, l_customer_id );
436 FETCH bill_site_csr INTO l_cust_site_id;
437 CLOSE bill_site_csr; */
438
439 --CHANGED CODE FOR RULES MIGRATION
440 OPEN bill_site_rul_csr( invstr_rec.Investor_Agreement_id, invstr_rec.TOP_LINE_ID );
441 FETCH bill_site_rul_csr INTO l_cust_site_id;
442 CLOSE bill_site_rul_csr;
443
444
445 IF (l_cust_site_id IS NULL) THEN
446 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
447 p_msg_name => 'OKL_BPD_INVEST_BILL_BTOS_ERR');
448
449 l_return_status := Okl_Api.G_RET_STS_ERROR;
450 raise G_EXCEPTION_HALT_VALIDATION;
451 END IF;
452
453 -- Payment Terms
454 OPEN std_terms_csr;
455 FETCH std_terms_csr INTO l_terms;
456 CLOSE std_terms_csr;
457
458 -- Receipt Method
459 OPEN rcpt_mthd_rul_csr( invstr_rec.Investor_Agreement_id, invstr_rec.TOP_LINE_ID );
460 FETCH rcpt_mthd_rul_csr INTO l_rcpt_id1;
461 CLOSE rcpt_mthd_rul_csr;
462
463 OPEN rcpt_mthd_csr ( l_rcpt_id1, l_customer_id );
464 FETCH rcpt_mthd_csr INTO l_receipt_method_id;
465 CLOSE rcpt_mthd_csr;
466
467 -- Bank Account
468 OPEN bank_acct_rul_csr( invstr_rec.Investor_Agreement_id, invstr_rec.TOP_LINE_ID );
469 FETCH bank_acct_rul_csr INTO l_bank_id1;
470 CLOSE bank_acct_rul_csr;
471
472 OPEN bank_acct_id_csr( l_bank_id1 );
473 FETCH bank_acct_id_csr INTO l_bank_acct_id;
474 CLOSE bank_acct_id_csr;
475
476 -- To pass bank account Id or not
477 OPEN pass_or_not_csr ( l_receipt_method_id );
478 FETCH pass_or_not_csr INTO l_how_created;
479 CLOSE pass_or_not_csr;
480
481 -- Get trx_type_id
482 OPEN c_trx_id( Okl_Accounting_Util.GET_SET_OF_BOOKS_ID, invstr_rec.ORG_ID );
483 FETCH c_trx_id INTO l_cust_trx_id;
484 CLOSE c_trx_id;
485
486 IF (l_cust_trx_id IS NULL) THEN
487 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
488 p_msg_name => 'OKL_BPD_INVEST_BILL_TRANS_ERR');
489
490 l_return_status := Okl_Api.G_RET_STS_ERROR;
491 raise G_EXCEPTION_HALT_VALIDATION;
492 END IF;
493
494 -- Multi-Currency parameters
495 l_currency_code := invstr_rec.currency_code;
496 l_currency_conversion_type := invstr_rec.currency_conversion_type;
497 l_currency_conversion_rate := invstr_rec.currency_conversion_rate;
498 l_currency_conversion_date := invstr_rec.currency_conversion_date;
499
500 -- Resolve Currency Convesion Parameters for Multi-Currency
501 IF l_currency_conversion_type IS NULL THEN
502 l_currency_conversion_type := 'User';
503 l_currency_conversion_rate := 1;
504 l_currency_conversion_date := SYSDATE;
505 END IF;
506 -- For date
507 IF l_currency_conversion_date IS NULL THEN
508 l_currency_conversion_date := SYSDATE;
509 END IF;
510
511 -- For rate -- Work out the rate in a Spot or Corporate
512 IF (l_currency_conversion_type = 'User') THEN
513 IF l_currency_conversion_rate IS NULL THEN
514 l_currency_conversion_rate := 1;
515 END IF;
516 END IF;
517 IF (l_currency_conversion_type = 'Spot'
518 OR l_currency_conversion_type = 'Corporate') THEN
519 l_currency_conversion_rate
520 := okl_accounting_util.get_curr_con_rate
521 (p_from_curr_code => l_currency_code,
522 p_to_curr_code => okl_accounting_util.get_func_curr_code,
523 p_con_date => l_currency_conversion_date,
524 p_con_type => l_currency_conversion_type);
525
526 END IF;
527
528 -- Fetch a unique Id
529 l_unique_id := get_seq_id;
530 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_inv_agr); -- for LE Uptake project 08-11-2006
531 /*IF l_legal_entity_id IS NULL THEN
532 Okl_Api.set_message(p_app_name => g_app_name,
533 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
534 p_token1 => 'CONTRACT_ID',
535 p_token1_value => p_inv_agr);
536 RAISE OKL_API.G_EXCEPTION_ERROR;
537 END IF;*/
538
539 BEGIN
540 /* ankushar 05-Feb-2007 Billing R12 project
541 start billing changes
542 */
543 --Assign value to l_currency_conversion_type
544 IF l_currency_conversion_type <> 'User' THEN
545 l_currency_conversion_rate := NULL;
546 END IF;
547
548 -- Populate the header record structure
549 --Added by kthiruva for Bug 6691554
550 --The receivables invoice during an add request should be created for the additional
551 --stake amount stored in amount stake
552 --The status code is used to differenciate if the call is recievables invoice
553 --is being created during activation of the IA or the activation of an add contract request
554 IF l_status_code = 'ACTIVE' THEN
555 lp_taiv_rec.amount := invstr_rec.amount_stake;
556 ELSE
557 lp_taiv_rec.amount := invstr_rec.amount;
558 END IF;
559 lp_taiv_rec.currency_conversion_date := l_currency_conversion_date;
560 lp_taiv_rec.currency_conversion_rate := l_currency_conversion_rate;
561 lp_taiv_rec.currency_conversion_type := l_currency_conversion_type;
562 lp_taiv_rec.currency_code := l_currency_code;
563 --ansethur R12 B Billing
564 -- lp_taiv_rec.try_id := l_cust_trx_id;
565 lp_taiv_rec.try_id := l_try_id;
566 --ansethur R12 B Billing
567 /* ankushar 25-Oct-2007 Bug# 6501426, Transaction Type corrected for Investor
568 start code changes
569 */
570 lp_taiv_rec.cust_trx_type_id := l_cust_trx_id;
571 /* ankushar 25-Oct-2007 Bug# 6501426
572 End Changes
573 */
574 lp_taiv_rec.date_entered := invstr_rec.start_date;
575 lp_taiv_rec.date_invoiced := invstr_rec.start_date;
576 --ansethur R12 B Billing changed investor_agreement used for assiginment into Investor_Agreement_id
577 lp_taiv_rec.khr_id := SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Agreement_id)),1,30);
578 --rkuttiya R12 B Billing changes
579 lp_taiv_rec.investor_agreement_number := invstr_rec.investor_agreement;
580 lp_taiv_rec.investor_name := invstr_rec.investor_name;
581 --
582 lp_taiv_rec.ixx_id := l_customer_id;
583 lp_taiv_rec.ibt_id := l_cust_site_id;
584 lp_taiv_rec.irm_id := l_receipt_method_id;
585 lp_taiv_rec.set_of_books_id := Okl_Accounting_Util.GET_SET_OF_BOOKS_ID;
586 lp_taiv_rec.irt_id := l_terms;
587 lp_taiv_rec.org_id := invstr_rec.org_id;
588 lp_taiv_rec.legal_entity_id := l_legal_entity_id;
589 lp_taiv_rec.okl_source_billing_trx := 'INVESTOR_STAKE';
590 -- Bug#6167215 fix - varangan - Begin
591 lp_taiv_rec.trx_status_code := 'SUBMITTED';
592 -- Bug#6167215 fix - varangan - End
593
594 -- Populate the Line record
595
596 IF l_status_code = 'ACTIVE' THEN
597 lp_tilv_rec.amount := invstr_rec.amount_stake;
598 ELSE
599 lp_tilv_rec.amount := invstr_rec.amount;
600 END IF;
601 lp_tilv_rec.description := SUBSTR(invstr_rec.Investor_Agreement||'-'||invstr_rec.Investor_Name||'-'||invstr_rec.AMOUNT,1,240);
602 lp_tilv_rec.quantity := 1;
603 --ansethur R12 B Billing
604 lp_tilv_rec.line_number := 1;
605 -- Begin - fix for Bug#6208308 - varangan
606 lp_tilv_rec.sty_id := l_sty_id;
607 -- End - fix for Bug#6208308 - varangan
608
609
610 lp_tilv_tbl(1) := lp_tilv_rec;
611
612 --Make the call to create an invoice only if the amount is >0
613 IF lp_taiv_rec.amount > 0 THEN
614 -- Call the Common Billing API to create AR Invoices
615 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version => l_api_version
616 ,p_init_msg_list => p_init_msg_list
617 ,x_return_status => x_return_status -- Bug#6167215 fix - varangan
618 ,x_msg_count => x_msg_count
619 ,x_msg_data => x_msg_data
620 ,p_taiv_rec => lp_taiv_rec
621 ,p_tilv_tbl => lp_tilv_tbl
622 ,p_tldv_tbl => lp_tldv_tbl
623 ,x_taiv_rec => lx_taiv_rec
624 ,x_tilv_tbl => lx_tilv_tbl
625 ,x_tldv_tbl => lx_tldv_tbl);
626 END IF;
627 /* ankushar end billing changes */
628 /*
629 INSERT INTO RA_INTERFACE_LINES (
630 ACCOUNTING_RULE_ID
631 ,ACCOUNTING_RULE_DURATION
632 ,AGREEMENT_ID
633 ,AMOUNT
634 ,BATCH_SOURCE_NAME
635 ,COMMENTS
636 ,CONVERSION_DATE
637 ,CONVERSION_RATE
638 ,CONVERSION_TYPE
639 ,CREATED_BY
640 ,CREATION_DATE
641 ,CREDIT_METHOD_FOR_ACCT_RULE
642 ,CREDIT_METHOD_FOR_INSTALLMENTS
643 ,CURRENCY_CODE
644 ,CUST_TRX_TYPE_ID
645 ,DESCRIPTION
646 ,LAST_UPDATED_BY
647 ,LAST_UPDATE_DATE
648 ,LINE_TYPE
649 ,TRX_NUMBER
650 ,TRX_DATE
651 ,GL_DATE
652 ,PRINTING_OPTION
653 ,CONS_BILLING_NUMBER
654 ,INTERFACE_LINE_ATTRIBUTE1
655 ,INTERFACE_LINE_ATTRIBUTE2
656 ,INTERFACE_LINE_ATTRIBUTE3
657 ,INTERFACE_LINE_ATTRIBUTE4
658 ,INTERFACE_LINE_ATTRIBUTE5
659 ,INTERFACE_LINE_ATTRIBUTE6
660 ,INTERFACE_LINE_ATTRIBUTE7
661 ,INTERFACE_LINE_ATTRIBUTE8
662 ,INTERFACE_LINE_ATTRIBUTE9
663 ,INTERFACE_LINE_ATTRIBUTE10
664 ,INTERFACE_LINE_ATTRIBUTE11
665 ,INTERFACE_LINE_ATTRIBUTE12
666 ,INTERFACE_LINE_ATTRIBUTE13
667 ,INTERFACE_LINE_ATTRIBUTE14
668 ,INTERFACE_LINE_ATTRIBUTE15
669 -- ,INTERFACE_LINE_ID
670 ,INTERFACE_LINE_CONTEXT
671 ,INVENTORY_ITEM_ID
672 ,INVOICING_RULE_ID
673 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
674 ,ORIG_SYSTEM_BILL_ADDRESS_ID
675 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
676 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
677 ,ORIG_SYSTEM_BILL_CONTACT_ID
678 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
679 ,PRIMARY_SALESREP_NUMBER
680 ,PRIMARY_SALESREP_ID
681 ,PURCHASE_ORDER
682 ,PURCHASE_ORDER_REVISION
683 ,PURCHASE_ORDER_DATE
684 ,CUSTOMER_BANK_ACCOUNT_ID
685 ,RECEIPT_METHOD_ID
686 ,RECEIPT_METHOD_NAME
687 ,QUANTITY
688 ,QUANTITY_ORDERED
689 ,REASON_CODE
690 ,REASON_CODE_MEANING
691 ,REFERENCE_LINE_ID
692 ,RULE_START_DATE
693 ,SALES_ORDER
694 ,SALES_ORDER_LINE
695 ,SALES_ORDER_DATE
696 ,SALES_ORDER_SOURCE
697 ,SET_OF_BOOKS_ID
698 ,TAX_EXEMPT_FLAG
699 ,TAX_EXEMPT_NUMBER
700 ,TAX_EXEMPT_REASON_CODE
701 ,TERM_ID
702 ,UNIT_SELLING_PRICE
703 ,UNIT_STANDARD_PRICE
704 ,UOM_CODE
705 ,HEADER_Attribute_CATEGORY
706 ,HEADER_Attribute1
707 ,HEADER_Attribute2
708 ,HEADER_Attribute3
709 ,HEADER_Attribute4
710 ,HEADER_Attribute5
711 ,HEADER_Attribute6
712 ,HEADER_Attribute7
713 ,HEADER_Attribute8
714 ,HEADER_Attribute9
715 ,HEADER_Attribute10
716 ,HEADER_Attribute11
717 ,HEADER_Attribute12
718 ,HEADER_Attribute13
719 ,HEADER_Attribute14
720 ,HEADER_Attribute15
721 ,Attribute_CATEGORY
722 ,Attribute1
723 ,Attribute2
724 ,Attribute3
725 ,Attribute4
726 ,Attribute5
727 ,Attribute6
728 ,Attribute7
729 ,Attribute8
730 ,Attribute9
731 ,Attribute10
732 ,Attribute11
733 ,Attribute12
734 ,Attribute13
735 ,Attribute14
736 ,Attribute15
737 ,ORG_ID
738 ,LEGAL_ENTITY_ID -- for LE Uptake project 08-11-2006
739 )
740 VALUES
741 ( NULL
742 , NULL
743 , NULL
744 , invstr_rec.AMOUNT
745 ,'OKL_INVESTOR'
746 , NULL
747 , l_currency_conversion_date
748 , DECODE(l_currency_conversion_type,'User',l_currency_conversion_rate,NULL)
749 , l_currency_conversion_type
750 , FND_global.user_id
751 , SYSDATE
752 , NULL
753 , NULL
754 , l_currency_code
755 , l_cust_trx_id --CUST_TRX_TYPE_ID
756 , SUBSTR(invstr_rec.Investor_Agreement||'-'||invstr_rec.Investor_Name||'-'||invstr_rec.AMOUNT,1,240)
757 , FND_global.user_id
758 , SYSDATE
759 , 'LINE' --r_ExtLine.LINE_TYPE
760 , NULL --TRX_NUMBER
761 , invstr_rec.START_DATE --TRX_DATE
762 , invstr_rec.START_DATE --TRX_DATE
763 , NULL
764 , NULL --XTRX_CONS_INVOICE_NUMBER
765 , SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Agreement)),1,30)
766 , SUBSTR(LTRIM(RTRIM(l_unique_id)),1,20)
767 , SUBSTR(LTRIM(RTRIM(l_unique_id)),21)
768 , SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Name)),1,30)
769 , 'INVESTOR RECEIVABLE' --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_CONS_LINE_NUMBER)),1,30)
770 , NULL --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_CONTRACT)),1,30)
771 , NULL --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_ASSET)),1,30)
772 , NULL --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_STREAM_GROUP)),1,30)
773 , NULL --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_STREAM_TYPE)),1,30)
774 , NULL --SUBSTR (r_ExtLine.XTRX_CONS_STREAM_ID, 1, 20)
775 , NULL --SUBSTR (r_ExtLine.XTRX_CONS_STREAM_ID, 21)
776 , NULL
777 , NULL
778 , NULL
779 , NULL
780 -- , r_ExtLine.ID
781 , 'OKL_INVESTOR'
782 , NULL
783 , NULL
784 , l_customer_id --CUSTOMER_ID
785 , l_cust_site_id --CUSTOMER_ADDRESS_ID
786 , l_customer_id --CUSTOMER_ID
787 , l_cust_site_id --NVL(l_ship_to , r_ExtHdr.CUSTOMER_ADDRESS_ID)
788 , NULL
789 , NULL
790 -- BEGIN bvaghela 032305 bug 4256274
791 , -3
792 , -3
793 -- END bvaghela 032305 bug 4256274
794 , NULL
795 , NULL
796 , NULL
797 , decode( l_how_created, 'MANUAL',NULL,l_bank_acct_id ) --CUSTOMER_BANK_ACCOUNT_ID
798 , l_receipt_method_id --RECEIPT_METHOD_ID
799 , NULL
800 , 1 --QUANTITY
801 , NULL
802 , NULL
803 , NULL
804 , NULL --REFERENCE_LINE_ID
805 , NULL
806 , NULL
807 , NULL
808 , NULL
809 , NULL
810 , Okl_Accounting_Util.GET_SET_OF_BOOKS_ID
811 , decode(l_taxable_yn, 'Y', 'S', 'N', 'E', 'S')
812 , NULL
813 , decode(l_taxable_yn, 'Y', null, 'N', 'MANUFACTURER', null)
814 , l_terms
815 , NULL
816 , NULL
817 , NULL
818 , NULL
819 , NULL
820 , NULL
821 , NULL
822 , NULL
823 , NULL
824 , NULL
825 , NULL
826 , NULL
827 , NULL
828 , NULL
829 , NULL
830 , NULL
831 , NULL
832 , NULL
833 , NULL
834 , NULL
835 , NULL
836 , NULL
837 , NULL
838 , NULL
839 , NULL
840 , NULL
841 , NULL
842 , NULL
843 , NULL
844 , NULL
845 , NULL
846 , NULL
847 , NULL
848 , NULL
849 , NULL
850 , invstr_rec.ORG_ID
851 ,l_legal_entity_id -- for LE Uptake project 08-11-2006
852 ) ;
853
854
855 -- BEGIN bvaghela 032305 bug 4256274 --
856
857 -- Get Sales Rep Id and Number
858 l_salesrep_id := NULL;
859 l_salesrep_number := NULL;
860
861 OPEN sales_rep_csr;
862 FETCH sales_rep_csr INTO l_salesrep_id,l_salesrep_number;
863 CLOSE sales_rep_csr;
864
865 l_sales_type_credit := NULL;
866 OPEN sales_type_credit_csr;
867 FETCH sales_type_credit_csr INTO l_sales_type_credit;
868 CLOSE sales_type_credit_csr;
869
870 -- Insert into sales credits table
871
872 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL (
873 INTERFACE_LINE_ATTRIBUTE1
874 ,INTERFACE_LINE_ATTRIBUTE2
875 ,INTERFACE_LINE_ATTRIBUTE3
876 ,INTERFACE_LINE_ATTRIBUTE4
877 ,INTERFACE_LINE_ATTRIBUTE5
878 ,INTERFACE_LINE_ATTRIBUTE6
879 ,INTERFACE_LINE_ATTRIBUTE7
880 ,INTERFACE_LINE_ATTRIBUTE8
881 ,INTERFACE_LINE_ATTRIBUTE9
882 ,INTERFACE_LINE_ATTRIBUTE10
883 ,INTERFACE_LINE_ATTRIBUTE11
884 ,INTERFACE_LINE_ATTRIBUTE12
885 ,INTERFACE_LINE_ATTRIBUTE13
886 ,INTERFACE_LINE_ATTRIBUTE14
887 ,INTERFACE_LINE_ATTRIBUTE15
888 ,INTERFACE_LINE_CONTEXT
889 ,SALES_CREDIT_AMOUNT_SPLIT
890 ,SALES_CREDIT_PERCENT_SPLIT
891 ,SALES_CREDIT_TYPE_ID
892 ,SALES_CREDIT_TYPE_NAME
893 ,SALESREP_ID
894 ,SALESREP_NUMBER
895 ,CREATED_BY
896 ,CREATION_DATE
897 ,LAST_UPDATED_BY
898 ,LAST_UPDATE_DATE
899 ,ORG_ID
900 )
901 VALUES (
902 SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Agreement)),1,30)
903 , SUBSTR(LTRIM(RTRIM(l_unique_id)),1,20)
904 , SUBSTR(LTRIM(RTRIM(l_unique_id)),21)
905 , SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Name)),1,30)
906 , NULL
907 , NULL
908 , NULL
909 , NULL
910 , NULL
911 , NULL
912 , NULL
913 , NULL
914 , NULL
915 , NULL
916 , NULL
917 , 'OKL_INVESTOR'
918 , NULL
919 , 100
920 , l_sales_type_credit
921 , 'Quota Sales Credit'
922 , -3
923 , -3
924 ,l_user_id
925 ,l_sysdate
926 ,l_user_id
927 ,l_sysdate
928 ,invstr_rec.ORG_ID
929 );
930
931 -- END bvaghela 032305 bug 4256274 --
932
933 EXCEPTION
934 WHEN OTHERS THEN
935 --modified by pgomes 01-Aug-2003 fix for bug 3078976
936 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
937 p_msg_name => 'OKL_BPD_INVEST_BILL_LINES_ERR');
938
939 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
940 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
941 END;
942
943 -- Create accounting distributions
944 BEGIN
945 -- bug#4643924 fetch the special accounting code associated to the inv agreement
946 l_inv_code := NULL;
947 FOR spl_acct_rec IN spl_acct_code_rul_csr(invstr_rec.Investor_Agreement_id) LOOP
948 l_inv_code := spl_acct_rec.investor_code;
949 END LOOP;
950
951 -- Set Distribution Counter
952 l_distr_cnt := 0;
953 --modified by pgomes 01-Aug-2003 fix for bug 3078976
954 l_distr_err := FALSE;
955 -- BUG#4643924 passing special accounting code as parameter
956 FOR dstrs_rec IN dstrs_csr( invstr_rec.pdt_id, l_try_id, l_sty_id, l_inv_code) LOOP
957
958 l_distr_cnt := l_distr_cnt + 1;
959
960 IF dstrs_rec.ACCOUNT_BUILDER_YN = 'N' THEN
961 l_cc_id := dstrs_rec.CODE_COMBINATION_ID;
962 ELSE
963 l_acc_gen_primary_key_tbl := l_init_acc_gen_primary_key_tbl;
964 OKL_ACC_CALL_PVT.okl_populate_acc_gen
965 (invstr_rec.Investor_Agreement_id,
966 NULL,
967 l_acc_gen_primary_key_tbl,
968 l_return_status);
969
970 IF (l_return_status = 'S' ) THEN
971 --FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Acc Gen Key Tbl populated.');
972 null;
973 ELSE
974 l_distr_err := TRUE;
975 --FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error populating Acc Gen Key Tbl.');
976 END IF;
977
978 l_cc_id := OKL_ACCOUNT_GENERATOR_PUB.GET_CCID
979 (p_api_version => p_api_version,
980 p_init_msg_list => p_init_msg_list,
981 x_return_status => l_return_status,
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data,
984 p_acc_gen_wf_sources_rec => l_acc_gen_wf_sources_rec,
985 p_ae_line_type => dstrs_rec.AE_LINE_TYPE,
986 p_primary_key_tbl => l_acc_gen_primary_key_tbl);
987 IF (l_return_status = 'S' ) THEN
988 --FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Acc Gen Fetched CCID');
989 null;
990 ELSE
991 l_distr_err := TRUE;
992 --FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error Acc Gen Fetching CCID.');
993 END IF;
994 END IF;
995
996 l_amount := okl_accounting_util.cross_currency_round_amount
997 ( (invstr_rec.AMOUNT*dstrs_rec.PERCENTAGE/100)
998 ,l_currency_code);
999
1000 --modified by pgomes 01-Aug-2003 fix for bug 3078976
1001 IF (l_distr_err) THEN
1002 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1003 p_msg_name => 'OKL_BPD_INVEST_BILL_DISTR_ERR');
1004
1005 l_return_status := Okl_Api.G_RET_STS_ERROR;
1006 RAISE G_EXCEPTION_HALT_VALIDATION;
1007 END IF;
1008
1009 INSERT INTO RA_INTERFACE_DISTRIBUTIONS
1010 (ACCOUNT_CLASS
1011 ,AMOUNT
1012 ,PERCENT
1013 ,CODE_COMBINATION_ID
1014 ,INTERFACE_LINE_CONTEXT
1015 ,INTERFACE_LINE_ATTRIBUTE1
1016 ,INTERFACE_LINE_ATTRIBUTE2
1017 ,INTERFACE_LINE_ATTRIBUTE3
1018 ,INTERFACE_LINE_ATTRIBUTE4
1019 ,INTERFACE_LINE_ATTRIBUTE5
1020 ,INTERFACE_LINE_ATTRIBUTE6
1021 ,INTERFACE_LINE_ATTRIBUTE7
1022 ,INTERFACE_LINE_ATTRIBUTE8
1023 ,INTERFACE_LINE_ATTRIBUTE9
1024 ,INTERFACE_LINE_ATTRIBUTE10
1025 ,INTERFACE_LINE_ATTRIBUTE11
1026 ,INTERFACE_LINE_ATTRIBUTE12
1027 ,INTERFACE_LINE_ATTRIBUTE13
1028 ,INTERFACE_LINE_ATTRIBUTE14
1029 ,INTERFACE_LINE_ATTRIBUTE15
1030 ,ORG_ID
1031 )
1032 VALUES
1033 ( decode( dstrs_rec.CRD_CODE,'C','REV','REC') --l_account_class
1034 , l_amount
1035 , dstrs_rec.PERCENTAGE
1036 , l_cc_id --r_ExtDistr.CODE_COMBINATION_ID
1037 , 'OKL_INVESTOR'
1038 , SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Agreement)),1,30) --SUBSTR ( r_ExtHdr.XTRX_INVOICE_PULL_YN,1,30 )
1039 , SUBSTR(LTRIM(RTRIM(l_unique_id)),1,20) --SUBSTR (r_ExtHdr.XTRX_CONS_INVOICE_NUMBER,1,30 )
1040 , SUBSTR(LTRIM(RTRIM(l_unique_id)),21) --SUBSTR ( r_ExtHdr.XTRX_FORMAT_TYPE,1,30 )
1041 , SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Name)),1,30) -- SUBSTR ( r_ExtHdr.XTRX_PRIVATE_LABEL,1,30 )
1042 , NULL
1043 , NULL
1044 , NULL
1045 , NULL
1046 , NULL
1047 , NULL
1048 , NULL
1049 , NULL
1050 , NULL
1051 , NULL
1052 , NULL
1053 , invstr_rec.ORG_ID
1054 ) ;
1055 END LOOP; -- Distribution Loop
1056
1057 IF (nvl(l_distr_cnt, 0) = 0) THEN
1058 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1059 p_msg_name => 'OKL_BPD_INVEST_BILL_DISTR_ERR');
1060
1061 l_return_status := Okl_Api.G_RET_STS_ERROR;
1062 RAISE G_EXCEPTION_HALT_VALIDATION;
1063 END IF;
1064
1065 EXCEPTION
1066 --modified by pgomes 01-Aug-2003 fix for bug 3078976
1067 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1068 x_return_status := l_return_status;
1069 RAISE;
1070 WHEN OTHERS THEN
1071 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1072 p_msg_name => 'OKL_BPD_INVEST_BILL_DISTR_ERR');
1073
1074 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1075 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1076 */
1077
1078 END;
1079
1080 END LOOP; -- Investor Loop
1081
1082
1083
1084 ----------------------------------------------------
1085 -- End activity
1086 ----------------------------------------------------
1087
1088 Okl_Api.END_ACTIVITY (
1089 x_msg_count => x_msg_count,
1090 x_msg_data => x_msg_data);
1091 EXCEPTION
1092 ------------------------------------------------------------
1093 -- Exception handling
1094 ------------------------------------------------------------
1095 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1096 x_return_status := l_return_status;
1097 WHEN OTHERS THEN
1098 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1099 ,p_msg_name => G_UNEXPECTED_ERROR
1100 ,p_token1 => G_SQLCODE_TOKEN
1101 ,p_token1_value => SQLCODE
1102 ,p_token2 => G_SQLERRM_TOKEN
1103 ,p_token2_value => SQLERRM
1104 ,p_token3 => 'Package'
1105 ,p_token3_value => G_PKG_NAME
1106 ,p_token4 => 'Procedure'
1107 ,p_token4_value => l_api_name
1108 );
1109 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1110
1111 END create_investor_bill;
1112
1113
1114 PROCEDURE create_bill_txn_conc
1115 (
1116 errbuf OUT NOCOPY VARCHAR2
1117 ,retcode OUT NOCOPY NUMBER
1118 ,p_inv_agr IN NUMBER
1119 ,p_investor_line_id IN NUMBER
1120 )
1121 IS
1122
1123 -- Local Variables
1124 l_api_version NUMBER := 1;
1125 lx_msg_count NUMBER;
1126 lx_msg_data VARCHAR2(200);
1127 l_msg_index_out NUMBER;
1128 lx_return_status VARCHAR(1);
1129
1130 BEGIN
1131
1132 create_investor_bill
1133 (p_api_version => l_api_version
1134 ,p_init_msg_list => OKC_API.G_FALSE
1135 ,x_return_status => lx_return_status
1136 ,x_msg_count => lx_msg_count
1137 ,x_msg_data => errbuf
1138 ,p_inv_agr => p_inv_agr
1139 ,p_investor_line_id => p_investor_line_id
1140 );
1141
1142 IF lx_msg_count >= 1 THEN
1143 FOR i in 1..lx_msg_count LOOP
1144 fnd_msg_pub.get (
1145 p_msg_index => i,
1146 p_encoded => 'F',
1147 p_data => lx_msg_data,
1148 p_msg_index_out => l_msg_index_out);
1149
1150 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,to_char(i) || ': ' || lx_msg_data);
1151 END LOOP;
1152 END IF;
1153
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>Exception Calling API..'||SQLERRM);
1157 END create_bill_txn_conc;
1158
1159
1160 ------------------------------------------------------------------
1161 -- Procedure create_billing_transaction to bill investor
1162 -- transactions
1163 ------------------------------------------------------------------
1164
1165 PROCEDURE create_billing_transaction
1166 (p_api_version IN NUMBER
1167 ,p_init_msg_list IN VARCHAR2
1168 ,x_return_status OUT NOCOPY VARCHAR2
1169 ,x_msg_count OUT NOCOPY NUMBER
1170 ,x_msg_data OUT NOCOPY VARCHAR2
1171 ,p_tai_rec IN okl_tai_pvt.taiv_rec_type
1172 ,p_til_tbl IN okl_til_pvt.tilv_tbl_type
1173 )
1174 IS
1175
1176 ------------------------------------------------------------
1177 -- Extract all External records to be billed
1178 ------------------------------------------------------------
1179
1180 ------------------------------------------------------------
1181 -- Declare variables required by APIs
1182 ------------------------------------------------------------
1183
1184 l_api_version CONSTANT NUMBER := 1;
1185 l_api_name CONSTANT VARCHAR2(30) := 'Create_Billing_Transaction';
1186 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1187
1188
1189 CURSOR get_try_id_csr IS
1190 SELECT id
1191 FROM okl_trx_types_v
1192 WHERE name = 'Billing';
1193
1194
1195 -- ********************************
1196
1197 -- Transaction Headers
1198 i_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
1199 r_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
1200
1201 -- Transaction Lines
1202 i_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
1203 r_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
1204
1205 ------------------------------------------------------------
1206 -- Declare variables to call Accounting Engine.
1207 ------------------------------------------------------------
1208 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
1209
1210
1211 BEGIN
1212 ------------------------------------------------------------
1213 -- Start processing
1214 ------------------------------------------------------------
1215
1216 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1217
1218 l_return_status := Okl_Api.START_ACTIVITY(
1219 p_api_name => l_api_name,
1220 p_pkg_name => G_PKG_NAME,
1221 p_init_msg_list => p_init_msg_list,
1222 l_api_version => l_api_version,
1223 p_api_version => p_api_version,
1224 p_api_type => '_PVT',
1225 x_return_status => l_return_status);
1226
1227 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1228 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1229 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1230 RAISE Okl_Api.G_EXCEPTION_ERROR;
1231 END IF;
1232
1233 ---------------------------------------------
1234 -- Populate Header record
1235 ---------------------------------------------
1236 i_taiv_rec := p_tai_rec;
1237
1238 --Default trx_status when not supplied
1239 IF i_taiv_rec.trx_status_code IS NULL THEN
1240 i_taiv_rec.trx_status_code := 'SUBMITTED';
1241 END IF;
1242
1243 --Default try_id when not supplied
1244 IF i_taiv_rec.try_id IS NULL THEN
1245 OPEN get_try_id_csr;
1246 FETCH get_try_id_csr INTO i_taiv_rec.try_id;
1247 CLOSE get_try_id_csr;
1248 END IF;
1249
1250 ---------------------------------------------
1251 -- Insert transaction header record
1252 ---------------------------------------------
1253 Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
1254 (p_api_version
1255 ,p_init_msg_list
1256 ,l_return_status
1257 ,x_msg_count
1258 ,x_msg_data
1259 ,i_taiv_rec
1260 ,r_taiv_rec);
1261
1262 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1263 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1264 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1265 RAISE Okl_Api.G_EXCEPTION_ERROR;
1266 END IF;
1267
1268 ---------------------------------------------
1269 -- Create Lines and accounting entries for each
1270 -- billing line
1271 ---------------------------------------------
1272
1273 FOR i IN p_til_tbl.first..p_til_tbl.LAST LOOP
1274 ---------------------------------------------
1275 -- Populate transaction line record
1276 ---------------------------------------------
1277
1278 i_tilv_rec := p_til_tbl(i);
1279 i_tilv_rec.tai_id := r_taiv_rec.id;
1280
1281 --Default Line Number when not supplied
1282 IF i_tilv_rec.LINE_NUMBER IS NULL THEN
1283 i_tilv_rec.LINE_NUMBER := 1;
1284 END IF;
1285
1286 --Default Quantity when not supplied
1287 IF i_tilv_rec.QUANTITY IS NULL THEN
1288 i_tilv_rec.QUANTITY := 1;
1289 END IF;
1290 --Default Line Code when not supplied
1291 IF i_tilv_rec.INV_RECEIV_LINE_CODE IS NULL THEN
1292 i_tilv_rec.INV_RECEIV_LINE_CODE := 'LINE';
1293 END IF;
1294
1295 ---------------------------------------------
1296 -- Insert transaction line record
1297 ---------------------------------------------
1298 Okl_Txl_Ar_Inv_Lns_Pub.insert_txl_ar_inv_lns
1299 (p_api_version
1300 ,p_init_msg_list
1301 ,l_return_status
1302 ,x_msg_count
1303 ,x_msg_data
1304 ,i_tilv_rec
1305 ,r_tilv_rec);
1306
1307 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1308 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1309 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1310 RAISE Okl_Api.G_EXCEPTION_ERROR;
1311 END IF;
1312
1313 ---------------------------------------------
1314 -- Populate Accounting record
1315 ---------------------------------------------
1316 p_bpd_acc_rec.id := r_tilv_rec.id;
1317 p_bpd_acc_rec.source_table := 'OKL_TXL_AR_INV_LNS_B';
1318 ----------------------------------------------------
1319 -- Create Accounting Distributions
1320 ----------------------------------------------------
1321 Okl_Acc_Call_Pub.CREATE_ACC_TRANS(
1322 p_api_version
1323 ,p_init_msg_list
1324 ,x_return_status
1325 ,x_msg_count
1326 ,x_msg_data
1327 ,p_bpd_acc_rec
1328 );
1329
1330 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1331 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1332 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1333 RAISE Okl_Api.G_EXCEPTION_ERROR;
1334 END IF;
1335 END LOOP;
1336
1337 ----------------------------------------------------
1338 -- End activity
1339 ----------------------------------------------------
1340
1341 Okl_Api.END_ACTIVITY (
1342 x_msg_count => x_msg_count,
1343 x_msg_data => x_msg_data);
1344 EXCEPTION
1345 ------------------------------------------------------------
1346 -- Exception handling
1347 ------------------------------------------------------------
1348 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1349
1350 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1351 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1352 p_api_name => l_api_name,
1353 p_pkg_name => G_PKG_NAME,
1354 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
1355 x_msg_count => x_msg_count,
1356 x_msg_data => x_msg_data,
1357 p_api_type => '_PVT');
1358
1359 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1360 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1361 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1362 p_api_name => l_api_name,
1363 p_pkg_name => G_PKG_NAME,
1364 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1365 x_msg_count => x_msg_count,
1366 x_msg_data => x_msg_data,
1367 p_api_type => '_PVT');
1368
1369 WHEN OTHERS THEN
1370 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1371 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1372 p_api_name => l_api_name,
1373 p_pkg_name => G_PKG_NAME,
1374 p_exc_name => 'OTHERS',
1375 x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data,
1377 p_api_type => '_PVT');
1378
1379 END create_billing_transaction;
1380
1381 END Okl_Investor_Billing_Pvt;