[Home] [Help]
PACKAGE BODY: APPS.OKL_CREDIT_MEMO_PVT
Source
1 PACKAGE BODY OKL_CREDIT_MEMO_PVT AS
2 /* $Header: OKLRCRMB.pls 120.15 2008/02/27 09:29:25 veramach noship $ */
3
4 ------------------------------------------------------------------------------
5 -- FUNCTION get_try_id
6 ------------------------------------------------------------------------------
7 -- Created by : RFEDANE
8 --
9 -- Purpose:
10 -- Private Procedure to retrieve ID of a given Transaction Type
11 --
12 -- Known limitations/enhancements and/or remarks:
13 --
14 ------------------------------------------------------------------------------
15 --cust_acct_site_id okx_cust_site_uses_v.cust_acct_site_id%TYPE;
16
17
18 CURSOR line_bill_to_csr(p_khr_id NUMBER, p_kle_id NUMBER) IS
19 SELECT cs.cust_acct_site_id --, cp.standard_terms payment_term_id
20 FROM okc_k_headers_b khr
21 , okx_cust_site_uses_v cs
22 , okc_k_lines_b cle
23 , hz_customer_profiles cp
24 WHERE khr.id = p_khr_id
25 AND cle.dnz_chr_id = khr.id
26 AND cle.chr_id IS NOT NULL
27 AND cle.id = p_kle_id
28 AND cle.BILL_TO_SITE_USE_ID = cs.id1
29 AND khr.bill_to_site_use_id = cp.site_use_id(+)
30 UNION
31 SELECT cs.cust_acct_site_id --, cp.standard_terms payment_term_id
32 FROM okc_k_headers_b khr
33 , okc_k_lines_b cle
34 , okc_k_items item
35 , okc_k_lines_b linked_asset
36 , okx_cust_site_uses_v cs
37 , hz_customer_profiles cp
38 WHERE khr.id = p_khr_id
39 AND cle.dnz_chr_id = khr.id
40 AND cle.id = p_kle_id
41 AND cle.chr_id IS NULL
42 AND cle.id = item.cle_id
43 AND item.object1_id1 = linked_asset.id
44 AND linked_asset.BILL_TO_SITE_USE_ID = cs.id1
45 AND khr.bill_to_site_use_id = cp.site_use_id(+);
46
47 CURSOR cust_acct_csr (p_khr_id NUMBER) IS
48 SELECT cs.cust_acct_site_id
49 -- , cp.standard_terms payment_term_id
50 FROM okc_k_headers_v khr
51 , okx_cust_site_uses_v cs
52 , hz_customer_profiles cp
53 WHERE khr.id = p_khr_id
54 AND khr.bill_to_site_use_id = cs.id1
55 AND khr.bill_to_site_use_id = cp.site_use_id(+);
56
57
58
59 FUNCTION get_try_id (p_try_name IN VARCHAR2) RETURN NUMBER IS
60
61 CURSOR c_try IS
62 SELECT id
63 FROM okl_trx_types_tl
64 WHERE name = p_try_name
65 AND language = 'US';
66
67 l_try_id NUMBER;
68
69 BEGIN
70
71 OPEN c_try;
72 FETCH c_try INTO l_try_id;
73 CLOSE c_try;
74
75 RETURN l_try_id;
76
77 EXCEPTION
78
79 WHEN OTHERS THEN
80
81 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
82 p_msg_name => G_UNEXPECTED_ERROR,
83 p_token1 => G_SQLCODE_TOKEN,
84 p_token1_value => SQLCODE,
85 p_token2 => G_SQLERRM_TOKEN,
86 p_token2_value => SQLERRM);
87
88 END get_try_id;
89
90
91 ------------------------------------------------------------------------------
92 -- FUNCTION get_pdt_id
93 ------------------------------------------------------------------------------
94 -- Created by : RFEDANE
95 --
96 -- Purpose:
97 -- Private Procedure to retrieve Product ID of a given Contract
98 --
99 -- Known limitations/enhancements and/or remarks:
100 --
101 ------------------------------------------------------------------------------
102
103 FUNCTION get_pdt_id (p_khr_id IN VARCHAR2) RETURN NUMBER IS
104
105 CURSOR c_pdt IS
106 SELECT pdt_id
107 FROM okl_k_headers
108 WHERE id = p_khr_id;
109
110 l_pdt_id NUMBER;
111
112 BEGIN
113
114 OPEN c_pdt;
115 FETCH c_pdt INTO l_pdt_id;
116 CLOSE c_pdt;
117
118 RETURN l_pdt_id;
119
120 EXCEPTION
121
122 WHEN OTHERS THEN
123
124 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
125 p_msg_name => G_UNEXPECTED_ERROR,
126 p_token1 => G_SQLCODE_TOKEN,
127 p_token1_value => SQLCODE,
128 p_token2 => G_SQLERRM_TOKEN,
129 p_token2_value => SQLERRM);
130
131 END get_pdt_id;
132
133
134 ------------------------------------------------------------------------------
135 -- FUNCTION get_factor_synd
136 ------------------------------------------------------------------------------
137 -- Created by : RFEDANE
138 --
139 -- Purpose:
140 -- Returns NULL if Contract is neither syndicated nor factored. Ortherwise returns
141 -- the appropriate value.
142 --
143 -- Known limitations/enhancements and/or remarks:
144 --
145 ------------------------------------------------------------------------------
146
147 FUNCTION get_factor_synd(p_khr_id IN NUMBER) RETURN VARCHAR2 IS
148 CURSOR c_synd IS
149 SELECT
150 'SYNDICATION'
151 FROM
152 okc_k_headers_b chr
153 WHERE
154 EXISTS ( SELECT 'x' FROM okc_k_items cim
155 WHERE cim.object1_id1 = to_char(chr.id) AND
156 EXISTS ( SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
157 WHERE cle.lse_id = lse.id AND
158 lse.lty_code = 'SHARED' AND cle.id = cim.cle_id ) AND
159 EXISTS ( SELECT 'x' FROM okc_k_headers_b chr2
160 WHERE chr2.id = cim.dnz_chr_id AND
161 chr2.scs_code = 'SYNDICATION' AND
162 chr2.sts_code not in ('TERMINATED','ABANDONED') ) ) AND
163 chr.scs_code = 'LEASE' AND
164 id = p_khr_id;
165
166 CURSOR c_fact IS
167 select '1'
168 from okc_rules_b
169 where dnz_chr_id = p_khr_id
170 and rule_information_category = 'LAFCTG'
171 and (rule_information1 is not null
172 or rule_information2 is not null
173 or rule_information3 is not null);
174 /*
175 -- This check for Factoring and Syndication is not correct.
176 -- so changed the queries as above.
177 -- rvaduri
178 CURSOR c_synd IS
179 SELECT scs_code
180 FROM okc_k_headers_b
181 WHERE scs_code = 'SYNDICATION'
182 AND id = p_khr_id;
183
184 CURSOR c_fact IS
185 SELECT '1'
186 FROM okc_rules_b
187 WHERE dnz_chr_id = p_khr_id
188 AND rule_information_category = 'LAFCTG';
189 */
190 l_contract_type VARCHAR2(30);
191
192 BEGIN
193
194 OPEN c_synd;
195 FETCH c_synd INTO l_contract_type;
196 CLOSE c_synd;
197
198 IF l_contract_type IS NOT NULL THEN
199 RETURN l_contract_type;
200 END IF;
201
202 OPEN c_fact;
203 FETCH c_fact INTO l_contract_type;
204 CLOSE c_fact;
205
206 IF l_contract_type IS NOT NULL THEN
207 l_contract_type := 'FACTORING';
208 RETURN l_contract_type;
209 END IF;
210
211 RETURN NULL;
212
213 EXCEPTION
214
215 WHEN OTHERS THEN
216
217 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
218 p_msg_name => G_UNEXPECTED_ERROR,
219 p_token1 => G_SQLCODE_TOKEN,
220 p_token1_value => SQLCODE,
221 p_token2 => G_SQLERRM_TOKEN,
222 p_token2_value => SQLERRM);
223
224 END get_factor_synd;
225
226 -----------------------------------------------------------------------------------------------
227 -- Procedure insert_request to insert a credit memo request
228 -----------------------------------------------------------------------------------------------
229 PROCEDURE insert_request(p_api_version IN NUMBER,
230 p_init_msg_list IN VARCHAR2,
231 --p_lsm_id IN NUMBER, -- Bug 5897792
232 p_tld_id IN NUMBER,
233 p_credit_amount IN NUMBER,
234 p_credit_sty_id IN NUMBER,
235 p_credit_desc IN VARCHAR2,
236 p_credit_date IN DATE,
237 p_try_id IN NUMBER,
238 -- Bug 5897792
239 p_transaction_source IN VARCHAR2 DEFAULT NULL,
240 p_source_trx_number IN VARCHAR2 DEFAULT NULL,
241 -- Bug 5897792
242
243 x_tai_id OUT NOCOPY NUMBER,
244 x_taiv_rec OUT NOCOPY taiv_rec_type,
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2) IS
248
249 CURSOR c_3LevelInvoice IS
250
251 SELECT tai.set_of_books_id set_of_books_id,
252 tai.currency_code currency_code,
253 tai.ixx_id ixx_id,
254 --tai.customer_address_id ibt_id,
255 tai.irm_id irm_id,
256 tai.irt_id irt_id,
257 tai.org_id org_id,
258 tai.id tai_id_reverses,
259 tai.khr_id khr_id,
260 tai.legal_entity_id legal_entity_id ,
261 tai.date_invoiced date_invoiced,
262 til.id til_id_reverses,
263 til.kle_id kle_id,
264 tld.id tld_id_reverses,
265 tld.sty_id sty_id,
266 tld.til_id_details til_id_details,
267 tld.sel_id sel_id -- 6237730
268 FROM
269 okl_txd_ar_ln_dtls_b tld,
270 okl_txl_ar_inv_lns_b til,
271 okl_trx_ar_invoices_b tai
272 WHERE tai.id = til.tai_id
273 AND til.id = tld.TIL_ID_DETAILS
274 --AND tld.lsm_id = p_tld_id;
275 AND ( tld.lsm_id = p_tld_id
276 OR tld.id = p_tld_id);
277
278 CURSOR c_2LevelInvoice IS
279
280 SELECT tai.set_of_books_id set_of_books_id,
281 tai.currency_code currency_code,
282 tai.ixx_id ixx_id,
283 -- tai.customer_address_id ibt_id,
284 tai.irm_id irm_id,
285 tai.irt_id irt_id,
286 tai.org_id org_id,
287 tai.id tai_id_reverses,
288 tai.khr_id khr_id,
289 tai.legal_entity_id legal_entity_id ,
290 tai.date_invoiced date_invoiced,
291 til.id til_id_reverses,
292 til.kle_id kle_id,
293 til.sty_id sty_id
294 FROM
295 okl_txd_ar_ln_dtls_b tld,
296 okl_txl_ar_inv_lns_b til,
297 okl_trx_ar_invoices_b tai
298 WHERE tai.id = til.tai_id
299 AND til.id = tld.TIL_ID_DETAILS
300 --AND tld.lsm_id = p_tld_id;
301 AND ( tld.lsm_id = p_tld_id
302 OR tld.id = p_tld_id);
303
304
305 --dkagrawa added cursor to derive le_id
306 /*CURSOR c_legal_entity_id IS
307 SELECT cnr.legal_entity_id
308 FROM okl_cnsld_ar_strms_b lsm,
309 okl_cnsld_ar_lines_b lln,
310 okl_cnsld_ar_hdrs_b cnr
311 WHERE lsm.lln_id = lln.id
312 AND lln.cnr_id = cnr.id
313 AND lsm.id = p_lsm_id;*/ -- Bug 5897792
314 CURSOR c_legal_entity_id IS
315 SELECT trx.legal_entity_id
316 FROM okl_txd_ar_ln_dtls_b tld,
317 okl_txl_ar_inv_lns_b til,
318 okl_trx_ar_invoices_b trx
319 WHERE tld.til_id_details = til.id
320 AND til.tai_id = trx.id
321 AND tld.id = p_tld_id;
322
323 v_3LevelInvoiceRec c_3LevelInvoice%ROWTYPE;
324 v_2LevelInvoiceRec c_2LevelInvoice%ROWTYPE;
325
326 -- Transaction headers
327 i_taiv_rec okl_trx_ar_invoices_pub.taiv_rec_type;
328 r_taiv_rec okl_trx_ar_invoices_pub.taiv_rec_type;
329
330 -- Transaction lines
331 i_tilv_rec okl_txl_ar_inv_lns_pub.tilv_rec_type;
332 --r_tilv_rec okl_txl_ar_inv_lns_pub.tilv_rec_type;
333
334
335 -- Transaction line details
336 i_tldv_rec okl_txd_ar_ln_dtls_pub.tldv_rec_type;
337 --r_tldv_rec okl_txd_ar_ln_dtls_pub.tldv_rec_type;
338
339 lp_tilv_tbl okl_til_pvt.tilv_tbl_type;
340 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
341 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type;
342 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
343
344 /* --Accounting engine wrapper records.
345 l_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
346 -- OKL Accouting Engine Records
347 l_tmpl_identify_rec OKL_ACCOUNT_DIST_PUB.TMPL_IDENTIFY_REC_TYPE;
348 l_dist_info_rec OKL_ACCOUNT_DIST_PUB.DIST_INFO_REC_TYPE;
349 l_ctxt_val_tbl OKL_ACCOUNT_DIST_PUB.CTXT_VAL_TBL_TYPE;
350 l_acc_gen_primary_key_tbl OKL_ACCOUNT_DIST_PUB.ACC_GEN_PRIMARY_KEY;
351 lx_template_tbl OKL_ACCOUNT_DIST_PUB.AVLV_TBL_TYPE;
352 lx_amount_tbl OKL_ACCOUNT_DIST_PUB.AMOUNT_TBL_TYPE;*/
353
354 l_try_id NUMBER;
355 l_sty_id NUMBER;
356 l_khr_id NUMBER;
357 l_pdt_id NUMBER;
358 l_factoring_synd VARCHAR2(30);
359 l_source_id NUMBER;
360 l_source_table VARCHAR2(30);
361
362 -- local parameters
363 l_api_version CONSTANT NUMBER := 1;
364 l_init_msg_list CONSTANT CHAR := 'F';
365 l_return_status VARCHAR2(1);
366 l_line_number NUMBER := 1;
367
368 BEGIN
369
370 -------------------------------------------------------------------------
371 -- Preparing Transaction Header (one record for each credit memo request)
372 -------------------------------------------------------------------------
373
374 l_try_id := p_try_id;
375
376 IF l_try_id IS NULL THEN
377
378 l_try_id := get_try_id( 'Credit Memo');
379
380 IF l_try_id IS NULL THEN
381
382 OKL_API.set_message(p_app_name => G_APP_NAME,
383 p_msg_name => 'OKL_TRY_ID_NOT_FOUND',
384 p_token1 => 'TRY_NAME',
385 p_token1_value => 'Credit Memo');
386
387 RAISE OKL_API.G_EXCEPTION_ERROR;
388
389 END IF;
390
391 END IF;
392
393 i_taiv_rec.try_id := l_try_id;
394 i_taiv_rec.date_entered := TRUNC(SYSDATE);
395 i_taiv_rec.description := NVL(p_credit_desc, 'OKL Credit Memo');
396
397 -- Only records with status SUBMITTED are processed
398 -- Use ENTERED or WORKING to temporarily prevent processing
399 -- See lookup_type OKL_TRANSACTION_STATUS for the full list of Status Codes
400 -- Status code after processing will either be PROCESSED or ERROR
401
402 i_taiv_rec.trx_status_code := 'SUBMITTED';
403
404 -- Amount must equal to sum of all lines (til records)
405 i_taiv_rec.amount := p_credit_amount;
406
407 --dkagrawa added following code to populate legal_entity_id
408 OPEN c_legal_entity_id;
409 FETCH c_legal_entity_id INTO i_taiv_rec.legal_entity_id;
410 CLOSE c_legal_entity_id;
411
412 OPEN c_3LevelInvoice;
413 FETCH c_3LevelInvoice INTO v_3LevelInvoiceRec;
414
415 IF c_3LevelInvoice%FOUND THEN
416
417 i_taiv_rec.khr_id := v_3LevelInvoiceRec.khr_id;
418
419 -- To populate Customer_Address_Id(IBT_ID) from cursor line_bill_to_csr
420 -- If it returns null then populate from cust_acct_csr.
421 OPEN line_bill_to_csr(i_taiv_rec.khr_id ,v_3LevelInvoiceRec.kle_id );
422 FETCH line_bill_to_csr INTO i_taiv_rec.ibt_id;
423 CLOSE line_bill_to_csr;
424
425 IF (i_taiv_rec.ibt_id IS NULL) THEN
426 OPEN cust_acct_csr(i_taiv_rec.khr_id);
427 FETCH cust_acct_csr INTO i_taiv_rec.ibt_id;
428 CLOSE cust_acct_csr;
429 --gkhuntet 26-07-2007 start.
430 /* ELSE
431 OKL_API.set_message(p_app_name => G_APP_NAME,
432 p_msg_name => 'OKL_TRY_ID_NOT_FOUND',
433 p_token1 => 'TRY_NAME',
434 p_token1_value => 'Credit Memo');
435
436 RAISE OKL_API.G_EXCEPTION_ERROR;*/
437 --gkhuntet 26-07-2007 end.
438 END IF;
439
440 ---------------------------------------------
441 -- These columns need not be provided if khr_id is set
442 -- because they will extracted from contract rules.
443 -- These values must be provided if:
444 -- 1. Credit goes to an account other than the invoiced account
445 -- 2. The invoice being credited was not for any contract
446 ---------------------------------------------
447 i_taiv_rec.set_of_books_id := v_3LevelInvoiceRec.set_of_books_id;
448 i_taiv_rec.currency_code := v_3LevelInvoiceRec.currency_code;
449 i_taiv_rec.ixx_id := v_3LevelInvoiceRec.ixx_id; -- customer
450 i_taiv_rec.irm_id := v_3LevelInvoiceRec.irm_id; -- payment method
451 i_taiv_rec.irt_id := v_3LevelInvoiceRec.irt_id; -- payment term
452 i_taiv_rec.org_id := v_3LevelInvoiceRec.org_id;
453 i_taiv_rec.tai_id_reverses := v_3LevelInvoiceRec.tai_id_reverses;
454 i_taiv_rec.legal_entity_id := v_3LevelInvoiceRec.legal_entity_id;
455 --Done this fix for bug 3816891, Passing the p_credit_date instead
456 -- of date_invoiced to the API
457 --i_taiv_rec.date_invoiced := v_3LevelInvoiceRec.date_invoiced;
458 --i_taiv_rec.date_invoiced := NVL(p_credit_date,trunc(sysdate));
459 --Done this fix for bug:5171202,passing date invoiced if the date invoiced is greater than
460 --sysdate and creates the credit memo form lease center only
461 IF i_taiv_rec.description = 'OKL Credit Memo' AND v_3LevelInvoiceRec.date_invoiced > SYSDATE THEN
462 i_taiv_rec.date_invoiced := v_3LevelInvoiceRec.date_invoiced;
463 ELSE
464 i_taiv_rec.date_invoiced := NVL(p_credit_date,trunc(sysdate));
465 END IF;
466
467 ----------------------------------------------------------------------------------
468 -- Not required for the Credit Process but may be useful to other functional areas
469 ----------------------------------------------------------------------------------
470 i_taiv_rec.cra_id := NULL; -- OKL_CURE_REP_AMTS_V
471 i_taiv_rec.qte_id := NULL; -- OKL_TRX_QUOTES_V
472 i_taiv_rec.tcn_id := NULL; -- OKL_TRX_CONTRACTS
473 i_taiv_rec.svf_id := NULL; -- OKL_SERVICE_FEES_V
474 i_taiv_rec.ipy_id := NULL; -- OKL_INS_POLICIES_V
475 i_taiv_rec.tap_id := NULL; -- OKL_TRX_AP_INVOICES_V -- vendor bills
476
477 --gkhuntet 26-07-07 start .
478 i_taiv_rec.okl_source_billing_trx := p_transaction_source;
479 --gkhuntet 26-07-07 end.
480
481 ---------------------------------------------------------------------
482 -- Prepare Transaction line (one record for each credit memo request)
483 ---------------------------------------------------------------------
484 i_tilv_rec.ibt_id := i_taiv_rec.ibt_id;
485 i_tilv_rec.kle_id := v_3LevelInvoiceRec.kle_id;
486 i_tilv_rec.line_number := l_line_number;
487 --i_tilv_rec.tai_id := r_taiv_rec.id;
488 i_tilv_rec.description := NVL(p_credit_desc, 'OKL Credit Memo');
489 i_tilv_rec.til_id_reverses := v_3LevelInvoiceRec.til_id_reverses;
490 i_taiv_rec.legal_entity_id := v_3LevelInvoiceRec.legal_entity_id;
491
492 -- this field is passed as invoice description in AR
493 -- you can actually put 'LINE' or 'CHARGE'
494 -- 'CHARGE' is used for financial charges and has some accounting
495 -- implications in AR; till further notice please always use LINE
496 i_tilv_rec.inv_receiv_line_code := 'LINE';
497
498 -- Same as Header amount (1 line per header)
499 i_tilv_rec.amount := p_credit_amount;
500
501 -- every billing item must have stream type;
502 -- it will be used for accrual, billing and consolidation;
503 -- make sure Susan has your stream type on the list
504 -- i_tilv_rec.sty_id := your_stream_type_id; -- only needed for 2 level insert
505
506 ----------------------------------------------------------------------------------
507 -- Not required for the Credit Process but may be useful to other functional areas
508 ----------------------------------------------------------------------------------
509 i_tilv_rec.acn_id_cost := NULL; -- OKL_ASSET_CNDTN_LNS_V
510 i_tilv_rec.tpl_id := NULL; -- OKL_TXL_AP_INV_LNS_V - for vendor bills
511 i_tilv_rec.date_bill_period_end := NULL; -- For Variable Interest use only
512 i_tilv_rec.date_bill_period_start := NULL; -- For Variable Interest use only
513 i_tilv_rec.quantity := NULL; -- No need for value since we don't use PO
514 i_tilv_rec.org_id := NULL;
515 i_tilv_rec.TXL_AR_LINE_NUMBER := 1; -- 6237730
516
517 lp_tilv_tbl(0) := i_tilv_rec;
518
519 -- 3rd level insert
520 ----------------------------------------------------
521 -- Populate required columns
522 ----------------------------------------------------
523
524 -- Same as Line amount (1 sub-line per line)
525 i_tldv_rec.amount := p_credit_amount;
526
527 i_tldv_rec.description := NVL(p_credit_desc, 'OKL Credit Memo');
528 -- i_tldv_rec.til_id_details := r_tilv_rec.id;
529 i_tldv_rec.line_detail_number := l_line_number;
530 i_tldv_rec.tld_id_reverses := v_3LevelInvoiceRec.tld_id_reverses;
531
532 -- gkhuntet 26-07-07 start.
533 i_tldv_rec.TXL_AR_LINE_NUMBER := 1;
534 -- gkhuntet 26-07-07 end.
535
536 -- Customer Service will use the original invoice Stream Type.
537 -- Insurance will provide a generic Insurance Credit Stream Type.
538
539 IF p_credit_sty_id IS NULL THEN
540 l_sty_id := v_3LevelInvoiceRec.sty_id;
541 ELSE
542 l_sty_id := p_credit_sty_id;
543 END IF;
544
545 i_tldv_rec.sty_id := l_sty_id;
546 i_tldv_rec.sel_id := v_3LevelInvoiceRec.sel_id; -- 6237730
547
548 -- l_source_id := r_tldv_rec.id;
549 -- l_source_table := 'OKL_TXD_AR_LN_DTLS_B';
550
551 lp_tldv_tbl(0) := i_tldv_rec;
552
553
554 ELSE
555
556 OPEN c_2LevelInvoice;
557 FETCH c_2LevelInvoice INTO v_2LevelInvoiceRec;
558
559 IF c_2LevelInvoice%FOUND THEN
560
561 CLOSE c_2LevelInvoice;
562
563 i_taiv_rec.khr_id := v_2LevelInvoiceRec.khr_id;
564
565 -- To populate Customer_Address_Id(IBT_ID) from cursor line_bill_to_csr
566 -- If it returns null then populate from cust_acct_csr.
567 OPEN line_bill_to_csr(i_taiv_rec.khr_id ,v_3LevelInvoiceRec.kle_id );
568 FETCH line_bill_to_csr INTO i_taiv_rec.ibt_id;
569 CLOSE line_bill_to_csr;
570
571 IF (i_taiv_rec.ibt_id IS NULL) THEN
572 OPEN cust_acct_csr(i_taiv_rec.khr_id);
573 FETCH cust_acct_csr INTO i_taiv_rec.ibt_id;
574 CLOSE cust_acct_csr;
575 END IF;
576
577 i_taiv_rec.set_of_books_id := v_2LevelInvoiceRec.set_of_books_id;
578 i_taiv_rec.currency_code := v_2LevelInvoiceRec.currency_code;
579 i_taiv_rec.ixx_id := v_2LevelInvoiceRec.ixx_id;
580 i_taiv_rec.irm_id := v_2LevelInvoiceRec.irm_id;
581 i_taiv_rec.irt_id := v_2LevelInvoiceRec.irt_id;
582 i_taiv_rec.org_id := v_2LevelInvoiceRec.org_id;
583 i_taiv_rec.tai_id_reverses := v_2LevelInvoiceRec.tai_id_reverses;
584 i_taiv_rec.legal_entity_id := v_3LevelInvoiceRec.legal_entity_id;
585 --Done this fix for bug 3816891, Passing the p_credit_date instead
586 -- of date_invoiced to the API
587 --i_taiv_rec.date_invoiced := v_3LevelInvoiceRec.date_invoiced;
588 --i_taiv_rec.date_invoiced := NVL(p_credit_date,trunc(sysdate));
589 --Done this fix for bug:5171202,passing date invoiced if the date invoiced is greater than
590 --sysdate and creates the credit memo form lease center only
591 IF i_taiv_rec.description = 'OKL Credit Memo' AND v_2LevelInvoiceRec.date_invoiced > SYSDATE THEN
592 i_taiv_rec.date_invoiced := v_2LevelInvoiceRec.date_invoiced;
593 ELSE
594 i_taiv_rec.date_invoiced := NVL(p_credit_date,trunc(sysdate));
595 END IF;
596
597 i_taiv_rec.cra_id := NULL;
598 i_taiv_rec.qte_id := NULL;
599 i_taiv_rec.tcn_id := NULL;
600 i_taiv_rec.svf_id := NULL;
601 i_taiv_rec.ipy_id := NULL;
602 i_taiv_rec.tap_id := NULL;
603
604 --gkhuntet 26-07-07 start.
605 i_taiv_rec.okl_source_billing_trx := p_transaction_source;
606 --gkhuntet 26-07-07 end
607
608 -- Same as Header amount (1 line per header)
609 i_tilv_rec.amount := r_taiv_rec.amount;
610 i_tilv_rec.kle_id := v_2LevelInvoiceRec.kle_id;
611 i_tilv_rec.line_number := l_line_number;
612 -- i_tilv_rec.tai_id := r_taiv_rec.id;
613 i_tilv_rec.description := NVL(p_credit_desc, 'OKL Credit Memo');
614 i_tilv_rec.til_id_reverses := v_2LevelInvoiceRec.til_id_reverses;
615 i_tilv_rec.inv_receiv_line_code := 'LINE';
616 i_tilv_rec.acn_id_cost := NULL;
617 i_tilv_rec.tpl_id := NULL;
618 i_tilv_rec.date_bill_period_end := NULL;
619 i_tilv_rec.date_bill_period_start := NULL;
620 i_tilv_rec.quantity := NULL;
621 i_tilv_rec.org_id := NULL;
622
623
624 -- Customer Service will use the original invoice Stream Type.
625 -- Insurance will provide a generic Insurance Credit Stream Type.
626
627 IF p_credit_sty_id IS NULL THEN
628 l_sty_id := v_2LevelInvoiceRec.sty_id;
629 ELSE
630 l_sty_id := p_credit_sty_id;
631 END IF;
632 i_tilv_rec.sty_id := l_sty_id;
633 i_tilv_rec.TXL_AR_LINE_NUMBER := 1; -- 6237730
634
635 --l_source_id := r_tilv_rec.id;
636 --l_source_table := 'OKL_TXL_AR_INV_LNS_B';
637 lp_tilv_tbl(0) := i_tilv_rec;
638
639 ELSE
640
641 okl_api.set_message(p_app_name => G_APP_NAME,
642 p_msg_name => 'OKL_INVOICE_TRX_NOT_FOUND');
643
644 RAISE OKL_API.G_EXCEPTION_ERROR;
645
646 END IF;
647
648 END IF;
649 CLOSE c_3LevelInvoice;
650
651 -- Bug 5897792
652 OKL_INTERNAL_BILLING_PVT.create_billing_trx(
653 p_api_version => p_api_version,
654 p_init_msg_list => p_init_msg_list,
655 x_return_status => l_return_status,
656 x_msg_count => x_msg_count,
657 x_msg_data => x_msg_data,
658 p_taiv_rec => i_taiv_rec ,
659 p_tilv_tbl => lp_tilv_tbl ,
660 p_tldv_tbl => lp_tldv_tbl ,
661 x_taiv_rec => r_taiv_rec ,
662 x_tilv_tbl => lx_tilv_tbl ,
663 x_tldv_tbl => lx_tldv_tbl );
664
665 --x_tai_id := r_taiv_rec.id;
666 x_taiv_rec := r_taiv_rec;
667 x_return_status := l_return_status;
668
669
670 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
671 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
672 x_return_status := l_return_status;
673 END IF;
674 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
675 END IF;
676
677 EXCEPTION
678
679 WHEN OKL_API.G_EXCEPTION_ERROR THEN
680
681 x_return_status := OKL_API.G_RET_STS_ERROR;
682
683 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
684
685 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
686
687 WHEN OTHERS THEN
688
689 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
690 p_msg_name => G_UNEXPECTED_ERROR,
691 p_token1 => G_SQLCODE_TOKEN,
692 p_token1_value => sqlcode,
693 p_token2 => G_SQLERRM_TOKEN,
694 p_token2_value => sqlerrm);
695
696 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
697
698
699 END insert_request;
700
701
702
703 -----------------------------------------------------------------------------------------------
704 -- Procedure insert_request to insert a credit memo request (TABLE OF CREDIT MEMOS)
705 -----------------------------------------------------------------------------------------------
706
707 PROCEDURE insert_request(p_api_version IN NUMBER,
708 p_init_msg_list IN VARCHAR2,
709 p_credit_list IN credit_tbl,
710 --Bug 5897792
711 p_transaction_source IN VARCHAR2 DEFAULT NULL,
712 p_source_trx_number IN VARCHAR2 DEFAULT NULL,
713 -- Bug 5897792
714 x_taiv_tbl OUT NOCOPY taiv_tbl_type,
715 x_return_status OUT NOCOPY VARCHAR2,
716 x_msg_count OUT NOCOPY NUMBER,
717 x_msg_data OUT NOCOPY VARCHAR2) IS
718
719 l_return_status VARCHAR2(1);
720 l_try_id NUMBER;
721 l_cm_try_id NUMBER;
722 lx_tai_id NUMBER; -- place holder only
723
724 lx_taiv_rec okl_trx_ar_invoices_pub.taiv_rec_type;
725
726
727 BEGIN
728
729 FOR i IN 1..p_credit_list.COUNT LOOP
730
731 IF p_credit_list(i).credit_try_name <> 'Credit Memo' THEN
732
733 l_try_id := get_try_id(p_credit_list(i).credit_try_name);
734
735 IF l_try_id IS NULL THEN
736
737 OKL_API.set_message(p_app_name => G_APP_NAME,
738 p_msg_name => 'OKL_TRY_ID_NOT_FOUND',
739 p_token1 => 'TRY_NAME',
740 p_token1_value => p_credit_list(i).credit_try_name);
741
742 RAISE OKL_API.G_EXCEPTION_ERROR;
743
744 END IF;
745
746 ELSE
747
748 IF l_cm_try_id IS NULL THEN
749
750 l_cm_try_id := get_try_id('Credit Memo');
751
752 IF l_cm_try_id IS NULL THEN
753
754 OKL_API.set_message(p_app_name => G_APP_NAME,
755 p_msg_name => 'OKL_TRY_ID_NOT_FOUND',
756 p_token1 => 'TRY_NAME',
757 p_token1_value => 'Credit Memo');
758
759 RAISE OKL_API.G_EXCEPTION_ERROR;
760
761 END IF;
762
763 ELSE
764
765 l_try_id := l_cm_try_id;
766
767 END IF;
768
769 END IF;
770
771 insert_request(p_api_version => p_api_version,
772 p_init_msg_list => p_init_msg_list,
773 p_tld_id => p_credit_list(i).lsm_id,
774 p_credit_amount => p_credit_list(i).credit_amount,
775 p_credit_sty_id => p_credit_list(i).credit_sty_id,
776 p_credit_desc => p_credit_list(i).credit_desc,
777 p_credit_date => p_credit_list(i).credit_date,
778 p_try_id => l_try_id,
779 --Bug 5897792
780 p_transaction_source => p_credit_list(i).transaction_source,
781 p_source_trx_number => p_credit_list(i).source_trx_number,
782 -- Bug 5897792
783 x_tai_id => lx_tai_id,
784 x_taiv_rec => lx_taiv_rec,
785 x_return_status => l_return_status,
786 x_msg_count => x_msg_count,
787 x_msg_data => x_msg_data);
788
789 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
790 RAISE OKL_API.G_EXCEPTION_ERROR;
791 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
792 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
793 END IF;
794
795 x_taiv_tbl(i) := lx_taiv_rec;
796
797 END LOOP;
798
799 x_return_status := l_return_status;
800
801 EXCEPTION
802
803 WHEN OKL_API.G_EXCEPTION_ERROR THEN
804
805 x_return_status := OKL_API.G_RET_STS_ERROR;
806
807 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
808
809 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
810
811 WHEN OTHERS THEN
812
813 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
814 p_msg_name => G_UNEXPECTED_ERROR,
815 p_token1 => G_SQLCODE_TOKEN,
816 p_token1_value => sqlcode,
817 p_token2 => G_SQLERRM_TOKEN,
818 p_token2_value => sqlerrm);
819
820 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
821
822 END insert_request;
823
824 --rkuttiya added for bug#4341480
825 PROCEDURE insert_on_acc_cm_request(p_api_version IN NUMBER,
826 p_init_msg_list IN VARCHAR2,
827 p_tld_id IN NUMBER,
828 p_credit_amount IN NUMBER,
829 p_credit_sty_id IN NUMBER,
830 p_credit_desc IN VARCHAR2,
831 p_credit_date IN DATE,
832 p_try_id IN NUMBER,
833 --Bug 5897792
834 p_transaction_source IN VARCHAR2 DEFAULT NULL,
835 p_source_trx_number IN VARCHAR2 DEFAULT NULL,
836 -- Bug 5897792
837 x_tai_id OUT NOCOPY NUMBER,
838 x_taiv_rec OUT NOCOPY taiv_rec_type,
839 x_return_status OUT NOCOPY VARCHAR2,
840 x_msg_count OUT NOCOPY NUMBER,
841 x_msg_data OUT NOCOPY VARCHAR2)IS
842 CURSOR c_3LevelInvoice IS
843
844 SELECT tai.set_of_books_id set_of_books_id,
845 tai.currency_code currency_code,
846 tai.ixx_id ixx_id,
847 --tai.customer_address_id ibt_id,
848 tai.irm_id irm_id,
849 tai.irt_id irt_id,
850 tai.org_id org_id,
851 tai.id tai_id_reverses,
852 tai.khr_id khr_id,
853 tai.legal_entity_id legal_entity_id ,
854 tai.date_invoiced date_invoiced,
855 til.id til_id_reverses,
856 til.kle_id kle_id,
857 tld.id tld_id_reverses,
858 tld.sty_id sty_id,
859 tld.til_id_details til_id_details,
860 tld.sel_id sel_id
861 FROM
862 okl_txd_ar_ln_dtls_b tld,
863 okl_txl_ar_inv_lns_b til,
864 okl_trx_ar_invoices_b tai
865 WHERE tai.id = til.tai_id
866 AND til.id = tld.TIL_ID_DETAILS
867 AND tld.id = p_tld_id;
868
869 CURSOR c_2LevelInvoice IS
870
871 SELECT tai.set_of_books_id set_of_books_id,
872 tai.currency_code currency_code,
873 tai.ixx_id ixx_id,
874 -- tai.customer_address_id ibt_id,
875 tai.irm_id irm_id,
876 tai.irt_id irt_id,
877 tai.org_id org_id,
878 tai.id tai_id_reverses,
879 tai.khr_id khr_id,
880 tai.legal_entity_id legal_entity_id ,
881 tai.date_invoiced date_invoiced,
882 til.id til_id_reverses,
883 til.kle_id kle_id,
884 til.sty_id sty_id
885 FROM
886 okl_txd_ar_ln_dtls_b tld,
887 okl_txl_ar_inv_lns_b til,
888 okl_trx_ar_invoices_b tai
889 WHERE tai.id = til.tai_id
890 AND til.id = tld.TIL_ID_DETAILS
891 AND tld.id = p_tld_id;
892
893 v_3LevelInvoiceRec c_3LevelInvoice%ROWTYPE;
894 v_2LevelInvoiceRec c_2LevelInvoice%ROWTYPE;
895
896 -- Transaction headers
897 i_taiv_rec okl_trx_ar_invoices_pub.taiv_rec_type;
898 r_taiv_rec okl_trx_ar_invoices_pub.taiv_rec_type;
899
900 -- Transaction lines
901 i_tilv_rec okl_txl_ar_inv_lns_pub.tilv_rec_type;
902 --r_tilv_rec okl_txl_ar_inv_lns_pub.tilv_rec_type;
903
904
905 -- Transaction line details
906 i_tldv_rec okl_txd_ar_ln_dtls_pub.tldv_rec_type;
907 --r_tldv_rec okl_txd_ar_ln_dtls_pub.tldv_rec_type;
908
909 lp_tilv_tbl okl_til_pvt.tilv_tbl_type;
910 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
911 lp_tldv_tbl okl_tld_pvt.tldv_tbl_type;
912 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
913
914 l_try_id NUMBER;
915 l_sty_id NUMBER;
916 l_khr_id NUMBER;
917 l_pdt_id NUMBER;
918 l_factoring_synd VARCHAR2(30);
919 l_source_id NUMBER;
920 l_source_table VARCHAR2(30);
921
922 -- local parameters
923 l_api_version CONSTANT NUMBER := 1;
924 l_init_msg_list CONSTANT CHAR := 'F';
925 l_return_status VARCHAR2(1);
926 l_line_number NUMBER := 1;
927
928 BEGIN
929
930 -------------------------------------------------------------------------
931 -- Preparing Transaction Header (one record for each credit memo request)
932 -------------------------------------------------------------------------
933
934 l_try_id := p_try_id;
935
936 IF l_try_id IS NULL THEN
937
938 l_try_id := get_try_id( 'Credit Memo');
939
940 IF l_try_id IS NULL THEN
941
942 OKL_API.set_message(p_app_name => G_APP_NAME,
943 p_msg_name => 'OKL_TRY_ID_NOT_FOUND',
944 p_token1 => 'TRY_NAME',
945 p_token1_value => 'Credit Memo');
946
947 RAISE OKL_API.G_EXCEPTION_ERROR;
948
949 END IF;
950
951 END IF;
952 --g_debug_proc('TRY_ID ' || l_try_id);
953
954 i_taiv_rec.try_id := l_try_id;
955 i_taiv_rec.date_entered := TRUNC(SYSDATE);
956 i_taiv_rec.description := NVL(p_credit_desc, 'OKL Credit Memo');
957
958 -- Only records with status SUBMITTED are processed
959 -- Use ENTERED or WORKING to temporarily prevent processing
960 -- See lookup_type OKL_TRANSACTION_STATUS for the full list of Status Codes
961 -- Status code after processing will either be PROCESSED or ERROR
962
963 i_taiv_rec.trx_status_code := 'SUBMITTED';
964
965 -- Amount must equal to sum of all lines (til records)
966 i_taiv_rec.amount := p_credit_amount;
967 i_taiv_rec.okl_source_billing_trx := 'COLLECTION';
968
969 --g_debug_proc('insert_on_acc_cm_request');
970
971 OPEN c_3LevelInvoice;
972 FETCH c_3LevelInvoice INTO v_3LevelInvoiceRec;
973
974 IF c_3LevelInvoice%FOUND THEN
975
976 i_taiv_rec.khr_id := v_3LevelInvoiceRec.khr_id;
977
978 --g_debug_proc('KHR_ID ' || i_taiv_rec.khr_id || 'KLE_ID ' || v_3LevelInvoiceRec.kle_id);
979
980 -- To populate Customer_Address_Id(IBT_ID) from cursor line_bill_to_csr
981 -- If it returns null then populate from cust_acct_csr.
982 OPEN line_bill_to_csr(i_taiv_rec.khr_id ,v_3LevelInvoiceRec.kle_id );
983 FETCH line_bill_to_csr INTO i_taiv_rec.ibt_id;
984 CLOSE line_bill_to_csr;
985
986 --g_debug_proc('i_taiv_rec.ibt_id ' || i_taiv_rec.ibt_id);
987
988 IF (i_taiv_rec.ibt_id IS NULL) THEN
989 OPEN cust_acct_csr(i_taiv_rec.khr_id);
990 FETCH cust_acct_csr INTO i_taiv_rec.ibt_id;
991 CLOSE cust_acct_csr;
992 END IF;
993
994 IF(i_taiv_rec.ibt_id IS NULL) THEN
995 OKL_API.set_message(p_app_name => G_APP_NAME,
996 p_msg_name => 'OKL_IBT_ID_NOT_FOUND',
997 p_token1 => 'TRY_NAME',
998 p_token1_value => 'Credit Memo');
999 RAISE OKL_API.G_EXCEPTION_ERROR;
1000 END IF;
1001
1002
1003 ---------------------------------------------
1004 -- These columns need not be provided if khr_id is set
1005 -- because they will extracted from contract rules.
1006 -- These values must be provided if:
1007 -- 1. Credit goes to an account other than the invoiced account
1008 -- 2. The invoice being credited was not for any contract
1009 ---------------------------------------------
1010 i_taiv_rec.set_of_books_id := v_3LevelInvoiceRec.set_of_books_id;
1011 i_taiv_rec.currency_code := v_3LevelInvoiceRec.currency_code;
1012 i_taiv_rec.ixx_id := v_3LevelInvoiceRec.ixx_id; -- customer
1013 i_taiv_rec.irm_id := v_3LevelInvoiceRec.irm_id; -- payment method
1014 i_taiv_rec.irt_id := v_3LevelInvoiceRec.irt_id; -- payment term
1015 i_taiv_rec.org_id := v_3LevelInvoiceRec.org_id;
1016 --i_taiv_rec.tai_id_reverses := v_3LevelInvoiceRec.tai_id_reverses;
1017 i_taiv_rec.legal_entity_id := v_3LevelInvoiceRec.legal_entity_id;
1018 --Done this fix for bug 3816891, Passing the p_credit_date instead
1019 -- of date_invoiced to the API
1020 --i_taiv_rec.date_invoiced := v_3LevelInvoiceRec.date_invoiced;
1021 --i_taiv_rec.date_invoiced := NVL(p_credit_date,trunc(sysdate));
1022 --Done this fix for bug:5171202,passing date invoiced if the date invoiced is greater than
1023 --sysdate and creates the credit memo form lease center only
1024 IF i_taiv_rec.description = 'OKL Credit Memo' AND v_3LevelInvoiceRec.date_invoiced > SYSDATE THEN
1025 i_taiv_rec.date_invoiced := v_3LevelInvoiceRec.date_invoiced;
1026 ELSE
1027 i_taiv_rec.date_invoiced := NVL(p_credit_date,trunc(sysdate));
1028 END IF;
1029
1030 ----------------------------------------------------------------------------------
1031 -- Not required for the Credit Process but may be useful to other functional areas
1032 ----------------------------------------------------------------------------------
1033 i_taiv_rec.cra_id := NULL; -- OKL_CURE_REP_AMTS_V
1034 i_taiv_rec.qte_id := NULL; -- OKL_TRX_QUOTES_V
1035 i_taiv_rec.tcn_id := NULL; -- OKL_TRX_CONTRACTS
1036 i_taiv_rec.svf_id := NULL; -- OKL_SERVICE_FEES_V
1037 i_taiv_rec.ipy_id := NULL; -- OKL_INS_POLICIES_V
1038 i_taiv_rec.tap_id := NULL; -- OKL_TRX_AP_INVOICES_V -- vendor bills
1039 i_taiv_rec.legal_entity_id := v_3LevelInvoiceRec.legal_entity_id;
1040
1041 i_taiv_rec.okl_source_billing_trx := NVL(p_transaction_source,i_taiv_rec.okl_source_billing_trx); -- vpanwar for bug no 6334774
1042
1043 ---------------------------------------------------------------------
1044 -- Prepare Transaction line (one record for each credit memo request)
1045 ---------------------------------------------------------------------
1046 i_tilv_rec.ibt_id := i_taiv_rec.ibt_id;
1047 i_tilv_rec.kle_id := v_3LevelInvoiceRec.kle_id;
1048 i_tilv_rec.line_number := l_line_number;
1049 --i_tilv_rec.tai_id := r_taiv_rec.id;
1050 i_tilv_rec.description := NVL(p_credit_desc, 'OKL Credit Memo');
1051 --i_tilv_rec.til_id_reverses := v_3LevelInvoiceRec.til_id_reverses;
1052
1053
1054 -- this field is passed as invoice description in AR
1055 -- you can actually put 'LINE' or 'CHARGE'
1056 -- 'CHARGE' is used for financial charges and has some accounting
1057 -- implications in AR; till further notice please always use LINE
1058 i_tilv_rec.inv_receiv_line_code := 'LINE';
1059
1060 -- Same as Header amount (1 line per header)
1061 i_tilv_rec.amount := p_credit_amount;
1062
1063 -- every billing item must have stream type;
1064 -- it will be used for accrual, billing and consolidation;
1065 -- make sure Susan has your stream type on the list
1066 -- i_tilv_rec.sty_id := your_stream_type_id; -- only needed for 2 level insert
1067
1068 ----------------------------------------------------------------------------------
1069 -- Not required for the Credit Process but may be useful to other functional areas
1070 ----------------------------------------------------------------------------------
1071 i_tilv_rec.acn_id_cost := NULL; -- OKL_ASSET_CNDTN_LNS_V
1072 i_tilv_rec.tpl_id := NULL; -- OKL_TXL_AP_INV_LNS_V - for vendor bills
1073 i_tilv_rec.date_bill_period_end := NULL; -- For Variable Interest use only
1074 i_tilv_rec.date_bill_period_start := NULL; -- For Variable Interest use only
1075 i_tilv_rec.quantity := NULL; -- No need for value since we don't use PO
1076 i_tilv_rec.org_id := NULL;
1077 i_tilv_rec.TXL_AR_LINE_NUMBER := 1;
1078
1079 lp_tilv_tbl(0) := i_tilv_rec;
1080
1081 -- 3rd level insert
1082 ----------------------------------------------------
1083 -- Populate required columns
1084 ----------------------------------------------------
1085
1086 -- Same as Line amount (1 sub-line per line)
1087 i_tldv_rec.amount := p_credit_amount;
1088
1089 i_tldv_rec.description := NVL(p_credit_desc, 'OKL Credit Memo');
1090 -- i_tldv_rec.til_id_details := r_tilv_rec.id;
1091 i_tldv_rec.line_detail_number := l_line_number;
1092 --i_tldv_rec.tld_id_reverses := v_3LevelInvoiceRec.tld_id_reverses;
1093
1094
1095 -- Customer Service will use the original invoice Stream Type.
1096 -- Insurance will provide a generic Insurance Credit Stream Type.
1097
1098 IF p_credit_sty_id IS NULL THEN
1099 l_sty_id := v_3LevelInvoiceRec.sty_id;
1100 ELSE
1101 l_sty_id := p_credit_sty_id;
1102 END IF;
1103
1104 i_tldv_rec.sty_id := l_sty_id;
1105 i_tldv_rec.sel_id := v_3LevelInvoiceRec.sel_id;
1106 i_tldv_rec.TXL_AR_LINE_NUMBER := 1; -- 6237730
1107
1108 -- l_source_id := r_tldv_rec.id;
1109 -- l_source_table := 'OKL_TXD_AR_LN_DTLS_B';
1110
1111 lp_tldv_tbl(0) := i_tldv_rec;
1112
1113
1114 ELSE
1115
1116 OPEN c_2LevelInvoice;
1117 FETCH c_2LevelInvoice INTO v_2LevelInvoiceRec;
1118 --g_debug_proc('In else of insert_on_acc_cm_request');
1119 IF c_2LevelInvoice%FOUND THEN
1120
1121 CLOSE c_2LevelInvoice;
1122
1123 i_taiv_rec.khr_id := v_2LevelInvoiceRec.khr_id;
1124
1125 -- To populate Customer_Address_Id(IBT_ID) from cursor line_bill_to_csr
1126 -- If it returns null then populate from cust_acct_csr.
1127 OPEN line_bill_to_csr(i_taiv_rec.khr_id ,v_3LevelInvoiceRec.kle_id );
1128 FETCH line_bill_to_csr INTO i_taiv_rec.ibt_id;
1129 CLOSE line_bill_to_csr;
1130
1131 IF (i_taiv_rec.ibt_id = NULL) THEN
1132 OPEN cust_acct_csr(i_taiv_rec.khr_id);
1133 FETCH cust_acct_csr INTO i_taiv_rec.ibt_id;
1134 CLOSE cust_acct_csr;
1135 END IF;
1136
1137 i_taiv_rec.set_of_books_id := v_2LevelInvoiceRec.set_of_books_id;
1138 i_taiv_rec.currency_code := v_2LevelInvoiceRec.currency_code;
1139 i_taiv_rec.ixx_id := v_2LevelInvoiceRec.ixx_id;
1140 i_taiv_rec.irm_id := v_2LevelInvoiceRec.irm_id;
1141 i_taiv_rec.irt_id := v_2LevelInvoiceRec.irt_id;
1142 i_taiv_rec.org_id := v_2LevelInvoiceRec.org_id;
1143 --i_taiv_rec.tai_id_reverses := v_2LevelInvoiceRec.tai_id_reverses;
1144 i_taiv_rec.legal_entity_id := v_3LevelInvoiceRec.legal_entity_id;
1145 --Done this fix for bug 3816891, Passing the p_credit_date instead
1146 -- of date_invoiced to the API
1147 --i_taiv_rec.date_invoiced := v_3LevelInvoiceRec.date_invoiced;
1148 --i_taiv_rec.date_invoiced := NVL(p_credit_date,trunc(sysdate));
1149 --Done this fix for bug:5171202,passing date invoiced if the date invoiced is greater than
1150 --sysdate and creates the credit memo form lease center only
1151 IF i_taiv_rec.description = 'OKL Credit Memo' AND v_2LevelInvoiceRec.date_invoiced > SYSDATE THEN
1152 i_taiv_rec.date_invoiced := v_2LevelInvoiceRec.date_invoiced;
1153 ELSE
1154 i_taiv_rec.date_invoiced := NVL(p_credit_date,trunc(sysdate));
1155 END IF;
1156
1157 i_taiv_rec.cra_id := NULL;
1158 i_taiv_rec.qte_id := NULL;
1159 i_taiv_rec.tcn_id := NULL;
1160 i_taiv_rec.svf_id := NULL;
1161 i_taiv_rec.ipy_id := NULL;
1162 i_taiv_rec.tap_id := NULL;
1163
1164 i_taiv_rec.okl_source_billing_trx := NVL(p_transaction_source,i_taiv_rec.okl_source_billing_trx); -- vpanwar for bug no 6334774
1165
1166
1167 -- Same as Header amount (1 line per header)
1168 i_tilv_rec.amount := r_taiv_rec.amount;
1169 i_tilv_rec.kle_id := v_2LevelInvoiceRec.kle_id;
1170 i_tilv_rec.line_number := l_line_number;
1171 -- i_tilv_rec.tai_id := r_taiv_rec.id;
1172 i_tilv_rec.description := NVL(p_credit_desc, 'OKL Credit Memo');
1173 -- i_tilv_rec.til_id_reverses := v_2LevelInvoiceRec.til_id_reverses;
1174 i_tilv_rec.inv_receiv_line_code := 'LINE';
1175 i_tilv_rec.acn_id_cost := NULL;
1176 i_tilv_rec.tpl_id := NULL;
1177 i_tilv_rec.date_bill_period_end := NULL;
1178 i_tilv_rec.date_bill_period_start := NULL;
1179 i_tilv_rec.quantity := NULL;
1180 i_tilv_rec.org_id := NULL;
1181
1182
1183 -- Customer Service will use the original invoice Stream Type.
1184 -- Insurance will provide a generic Insurance Credit Stream Type.
1185
1186 IF p_credit_sty_id IS NULL THEN
1187 l_sty_id := v_2LevelInvoiceRec.sty_id;
1188 ELSE
1189 l_sty_id := p_credit_sty_id;
1190 END IF;
1191 i_tilv_rec.sty_id := l_sty_id;
1192 i_tilv_rec.TXL_AR_LINE_NUMBER := 1; -- 6237730
1193
1194 --l_source_id := r_tilv_rec.id;
1195 --l_source_table := 'OKL_TXL_AR_INV_LNS_B';
1196 lp_tilv_tbl(0) := i_tilv_rec;
1197
1198 ELSE
1199
1200 okl_api.set_message(p_app_name => G_APP_NAME,
1201 p_msg_name => 'OKL_INVOICE_TRX_NOT_FOUND');
1202
1203 RAISE OKL_API.G_EXCEPTION_ERROR;
1204
1205 END IF;
1206
1207 END IF;
1208 CLOSE c_3LevelInvoice;
1209
1210 -- Bug 5897792
1211 --g_debug_proc('Before call to create_billing_trx');
1212 OKL_INTERNAL_BILLING_PVT.create_billing_trx(
1213 p_api_version => p_api_version,
1214 p_init_msg_list => p_init_msg_list,
1215 x_return_status => l_return_status,
1216 x_msg_count => x_msg_count,
1217 x_msg_data => x_msg_data,
1218 p_taiv_rec => i_taiv_rec ,
1219 p_tilv_tbl => lp_tilv_tbl ,
1220 p_tldv_tbl => lp_tldv_tbl ,
1221 x_taiv_rec => r_taiv_rec ,
1222 x_tilv_tbl => lx_tilv_tbl ,
1223 x_tldv_tbl => lx_tldv_tbl );
1224
1225 --x_tai_id := r_taiv_rec.id;
1226 x_taiv_rec := r_taiv_rec;
1227 x_return_status := l_return_status;
1228 x_tai_id := r_taiv_rec.id;
1229 --g_debug_proc('After call of create_billing_trx ' || x_tai_id || ' ' || lx_tilv_tbl(lx_tilv_tbl.FIRST).id);
1230
1231 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1232 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1233 x_return_status := l_return_status;
1234 END IF;
1235 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1236 END IF;
1237
1238 EXCEPTION
1239
1240 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1241
1242 x_return_status := OKL_API.G_RET_STS_ERROR;
1243
1244 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1245
1246 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1247
1248 WHEN OTHERS THEN
1249
1250 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1251 p_msg_name => G_UNEXPECTED_ERROR,
1252 p_token1 => G_SQLCODE_TOKEN,
1253 p_token1_value => sqlcode,
1254 p_token2 => G_SQLERRM_TOKEN,
1255 p_token2_value => sqlerrm);
1256
1257 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1258
1259
1260 END insert_on_acc_cm_request;
1261
1262 --end fix for bug #4341480
1263
1264 END okl_credit_memo_pvt;