[Home] [Help]
PACKAGE BODY: APPS.OKL_CREATE_ADJST_PVT
Source
1 PACKAGE BODY okl_create_adjst_pvt AS
2 /* $Header: OKLROCAB.pls 120.10.12010000.5 2009/08/06 08:44:21 nikshah ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.ADJUSTMENTS';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 IS_DEBUG_STATEMENT_ON BOOLEAN;
9 IS_DEBUG_EXCEPTION_ON BOOLEAN;
10
11 TYPE l_adj_lines_rec_type IS RECORD
12 ( APPLY_DATE okl_trx_ar_adjsts_b.APPLY_DATE%type,
13 GL_DATE okl_trx_ar_adjsts_b.GL_DATE%type,
14 ADJUSTMENT_REASON_CODE okl_trx_ar_adjsts_b.ADJUSTMENT_REASON_CODE%type,
15 COMMENTS okl_trx_ar_adjsts_tl.COMMENTS%type,
16 adjsts_lns_id okl_txl_adjsts_lns_b.ID%type,
17 payment_schedule_id okl_txl_adjsts_lns_b.PSL_ID%type,
18 AMOUNT okl_txl_adjsts_lns_b.AMOUNT%type,
19 CODE_COMBINATION_ID okl_txl_adjsts_lns_b.CODE_COMBINATION_ID%type,
20 customer_trx_id ra_customer_trx_all.customer_trx_id%type,
21 customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%type
22 );
23 TYPE l_adj_lines_tbl_type IS TABLE OF l_adj_lines_rec_type INDEX BY BINARY_INTEGER;
24
25 TYPE l_adj_hdr_rec_type IS RECORD
26 ( lines l_adj_lines_tbl_type,
27 cons_invoice VARCHAR2(1)
28 );
29 TYPE l_adj_hdr_tbl_type IS TABLE OF l_adj_hdr_rec_type INDEX BY BINARY_INTEGER;
30
31 -- End of wraper code generated automatically by Debug code generator
32
33 ---------------------------------------------------------------------------
34 -- PROCEDURE get_adj_header_and_lines
35 ---------------------------------------------------------------------------
36 /*===========================================================================+
37 | PROCEDURE |
38 | get_adj_header_and_lines |
39 | |
40 | DESCRIPTION |
41 | This will return adjustment header and lines table to calling API |
42 | |
43 | SCOPE - PRIVATE |
44 | |
45 | NOTES |
46 | |
47 | MODIFICATION HISTORY |
48 | NIKSHAH 03-AUG-09 Created |
49 +===========================================================================*/
50 PROCEDURE get_adj_header_and_lines
51 (p_api_version IN NUMBER
52 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
53 ,p_psl_id IN NUMBER DEFAULT NULL
54 ,x_return_status OUT NOCOPY VARCHAR2
55 ,x_msg_count OUT NOCOPY NUMBER
56 ,x_msg_data OUT NOCOPY VARCHAR2
57 ,x_adj_hdr_tbl OUT NOCOPY l_adj_hdr_tbl_type
58 ,x_adj_lines_tbl OUT NOCOPY l_adj_lines_tbl_type)
59 IS
60
61 l_apply_date AR_ADJUSTMENTS.APPLY_DATE%TYPE;
62 l_gl_date AR_ADJUSTMENTS.GL_DATE%TYPE;
63 l_adjustment_reason_code AR_ADJUSTMENTS.REASON_CODE%TYPE;
64 l_comments AR_ADJUSTMENTS.COMMENTS%TYPE;
65 l_adjsts_lns_id OKL_TXL_ADJSTS_LNS_B.ID%TYPE;
66 l_payment_schedule_id AR_ADJUSTMENTS.PAYMENT_SCHEDULE_ID%TYPE;
67 l_code_combination_id AR_ADJUSTMENTS.CODE_COMBINATION_ID%TYPE;
68 l_amount AR_ADJUSTMENTS.AMOUNT%TYPE;
69
70 CURSOR c_get_adjustments IS
71 SELECT a.APPLY_DATE
72 ,a.GL_DATE
73 ,a.ADJUSTMENT_REASON_CODE
74 ,a.COMMENTS
75 ,b.ID
76 ,b.PSL_ID
77 ,b.AMOUNT
78 ,b.CODE_COMBINATION_ID
79 ,ln.customer_trx_id
80 ,ln.customer_trx_line_id
81 FROM okl_trx_ar_adjsts_v a,
82 okl_txl_adjsts_lns_v b,
83 ra_customer_trx_lines_all ln,
84 ar_payment_schedules_all ps,
85 okc_k_headers_b khr,
86 okc_k_lines_v kle,
87 okl_strm_type_v sty
88 WHERE a.id = b.adj_id
89 AND b.receivables_adjustment_id IS NULL
90 AND b.psl_id = ps.payment_schedule_id
91 AND ln.customer_trx_id = ps.customer_trx_id
92 AND khr.contract_number = ln.interface_line_attribute6
93 AND khr.id = kle.chr_id
94 AND kle.name = ln.interface_line_attribute7
95 AND sty.name = ln.interface_line_attribute9
96 AND sty.id = b.sty_id
97 AND khr.id = b.khr_id
98 AND kle.id = b.kle_id
99 AND ps.payment_schedule_id = NVL(p_psl_id, ps.payment_schedule_id)
100 ORDER BY ln.customer_trx_id, b.PSL_ID;
101
102 CURSOR c_is_consolidated_invoice(cp_psl_id IN NUMBER) IS
103 SELECT 'Y'
104 FROM AR_PAYMENT_SCHEDULES_ALL APS,
105 OKL_CNSLD_AR_STRMS_B CAS
106 WHERE APS.CUSTOMER_TRX_ID = CAS.RECEIVABLES_INVOICE_ID
107 AND APS.PAYMENT_SCHEDULE_ID = cp_psl_id;
108
109 l_is_cons_inv VARCHAR2(1) := 'N';
110
111 l_adj_hdr_tbl l_adj_hdr_tbl_type;
112 l_adj_lines_tbl l_adj_lines_tbl_type;
113
114 l_first_record BOOLEAN := TRUE;
115 cl NUMBER;
116 ch NUMBER;
117 l_last_psl_id NUMBER;
118 l_last_trx_id NUMBER;
119 l_customer_trx_id ra_customer_trx_all.customer_trx_id%type;
120 l_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%type;
121
122 BEGIN
123 IF(IS_DEBUG_PROCEDURE_ON) THEN
124 BEGIN
125 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'OKL_CREATE_ADJST_PVT.get_adjustment_header_and_lines BEGIN(+) ');
126 END;
127 END IF;
128
129 --Table initialization
130 l_adj_lines_tbl.delete;
131 l_adj_hdr_tbl.delete;
132 l_first_record := TRUE;
133 cl := 1;
134 ch := 1;
135 l_last_psl_id := -1;
136 l_last_trx_id := -1;
137
138 OPEN c_get_adjustments;
139 LOOP
140 FETCH c_get_adjustments INTO l_apply_date
141 ,l_gl_date
142 ,l_adjustment_reason_code
143 ,l_comments
144 ,l_adjsts_lns_id
145 ,l_payment_schedule_id
146 ,l_amount
147 ,l_code_combination_id
148 ,l_customer_trx_id
149 ,l_customer_trx_line_id;
150 EXIT WHEN c_get_adjustments%NOTFOUND;
151 IF l_first_record = TRUE THEN
152 l_adj_lines_tbl(cl).apply_date := l_apply_date;
153 l_adj_lines_tbl(cl).gl_date := l_gl_date;
154 l_adj_lines_tbl(cl).adjustment_reason_code := l_adjustment_reason_code;
155 l_adj_lines_tbl(cl).comments := l_comments;
156 l_adj_lines_tbl(cl).adjsts_lns_id := l_adjsts_lns_id;
157 l_adj_lines_tbl(cl).payment_schedule_id := l_payment_schedule_id;
158 l_adj_lines_tbl(cl).amount := l_amount;
159 l_adj_lines_tbl(cl).code_combination_id := l_code_combination_id;
160 l_adj_lines_tbl(cl).customer_trx_id := l_customer_trx_id;
161 l_adj_lines_tbl(cl).customer_trx_line_id := l_customer_trx_line_id;
162
163 l_last_psl_id := l_payment_schedule_id;
164 l_last_trx_id := l_customer_trx_id;
165 cl := cl + 1;
166 l_first_record := FALSE;
167 ELSE
168 IF l_payment_schedule_id = l_last_psl_id AND l_customer_trx_id = l_customer_trx_id THEN
169 l_adj_lines_tbl(cl).apply_date := l_apply_date;
170 l_adj_lines_tbl(cl).gl_date := l_gl_date;
171 l_adj_lines_tbl(cl).adjustment_reason_code := l_adjustment_reason_code;
172 l_adj_lines_tbl(cl).comments := l_comments;
173 l_adj_lines_tbl(cl).adjsts_lns_id := l_adjsts_lns_id;
174 l_adj_lines_tbl(cl).payment_schedule_id := l_payment_schedule_id;
175 l_adj_lines_tbl(cl).amount := l_amount;
176 l_adj_lines_tbl(cl).code_combination_id := l_code_combination_id;
177 l_adj_lines_tbl(cl).customer_trx_id := l_customer_trx_id;
178 l_adj_lines_tbl(cl).customer_trx_line_id := l_customer_trx_line_id;
179
180 l_last_psl_id := l_payment_schedule_id;
181 l_last_trx_id := l_customer_trx_id;
182 cl := cl + 1;
183 ELSE
184 --Wrap up last lines table into one header and decide whether that was
185 --for cons invoice or regular R12 invoice
186 OPEN c_is_consolidated_invoice(l_last_psl_id);
187 FETCH c_is_consolidated_invoice INTO l_is_cons_inv;
188 IF c_is_consolidated_invoice%NOTFOUND THEN
189 l_is_cons_inv := 'N';
190 END IF;
191 CLOSE c_is_consolidated_invoice;
192 l_adj_hdr_tbl(ch).lines := l_adj_lines_tbl;
193 l_adj_hdr_tbl(ch).cons_invoice := l_is_cons_inv;
194 cl := 1;
195 l_adj_lines_tbl.delete;
196 ch := ch + 1;
197
198 --Now create new header with all lines information
199 l_adj_lines_tbl(cl).apply_date := l_apply_date;
200 l_adj_lines_tbl(cl).gl_date := l_gl_date;
201 l_adj_lines_tbl(cl).adjustment_reason_code := l_adjustment_reason_code;
202 l_adj_lines_tbl(cl).comments := l_comments;
203 l_adj_lines_tbl(cl).adjsts_lns_id := l_adjsts_lns_id;
204 l_adj_lines_tbl(cl).payment_schedule_id := l_payment_schedule_id;
205 l_adj_lines_tbl(cl).amount := l_amount;
206 l_adj_lines_tbl(cl).code_combination_id := l_code_combination_id;
207 l_adj_lines_tbl(cl).customer_trx_id := l_customer_trx_id;
208 l_adj_lines_tbl(cl).customer_trx_line_id := l_customer_trx_line_id;
209
210 l_last_psl_id := l_payment_schedule_id;
211 l_last_trx_id := l_customer_trx_id;
212 cl := cl + 1;
213 END IF;
214 END IF;
215 END LOOP;
216 CLOSE c_get_adjustments;
217 --Wrap up last lines table into one header and decide whether that was
218 --for cons invoice or regular R12 invoice
219 OPEN c_is_consolidated_invoice(l_last_psl_id);
220 FETCH c_is_consolidated_invoice INTO l_is_cons_inv;
221 IF c_is_consolidated_invoice%NOTFOUND THEN
222 l_is_cons_inv := 'N';
223 END IF;
224 CLOSE c_is_consolidated_invoice;
225 l_adj_hdr_tbl(ch).lines := l_adj_lines_tbl;
226 l_adj_hdr_tbl(ch).cons_invoice := l_is_cons_inv;
227 l_adj_lines_tbl.delete;
228
229 IF(IS_DEBUG_STATEMENT_ON) THEN
230 BEGIN
231 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,L_MODULE,' COUNT OF ADJUSTMENT HEADER : ' || l_adj_hdr_tbl.COUNT);
232 END;
233 END IF;
234
235 x_adj_hdr_tbl := l_adj_hdr_tbl;
236 x_adj_lines_tbl := l_adj_lines_tbl;
237 x_return_status := OKL_API.G_RET_STS_SUCCESS;
238
239 IF(IS_DEBUG_PROCEDURE_ON) THEN
240 BEGIN
241 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'OKL_CREATE_ADJST_PVT.get_adjustment_header_and_lines END(-) ');
242 END;
243 END IF;
244 EXCEPTION
245 WHEN OTHERS THEN
246 IF(IS_DEBUG_EXCEPTION_ON) THEN
247 BEGIN
248 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_EXCEPTION,L_MODULE,'OKL_CREATE_ADJST_PVT.get_adjustment_header_and_lines EXCEPTION : ' || sqlerrm );
249 END;
250 END IF;
251 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
252 x_msg_data := sqlerrm ;
253 END get_adj_header_and_lines;
254
255 ---------------------------------------------------------------------------
256 -- PROCEDURE create_adjustments
257 ---------------------------------------------------------------------------
258 /*===========================================================================+
259 | PROCEDURE |
260 | create_adjustments |
261 | |
262 | DESCRIPTION |
263 | This is the main routine to create an adjustment in AR |
264 | |
265 | SCOPE - PRIVATE |
266 | |
267 | NOTES |
268 | |
269 | MODIFICATION HISTORY |
270 | Bruno Vaghela 16-AUG-02 Created |
271 | 18-Jul-2006 dkagrawa Bug 5378114 MOAC changes |
272 +===========================================================================*/
273
274 PROCEDURE create_adjustments ( p_api_version IN NUMBER
275 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
276 ,p_psl_id IN NUMBER DEFAULT NULL
277 ,p_commit_flag IN VARCHAR2 DEFAULT OKL_API.G_FALSE
278 ,p_chk_approval_limits IN VARCHAR2 DEFAULT OKL_API.G_FALSE
279 ,x_return_status OUT NOCOPY VARCHAR2
280 ,x_msg_count OUT NOCOPY NUMBER
281 ,x_msg_data OUT NOCOPY VARCHAR2
282 ,x_new_adj_id OUT NOCOPY NUMBER --Will be used only for IEX call
283 )IS
284
285 ------------------------------
286 -- DECLARE Local variables
287 ------------------------------
288
289 l_api_version NUMBER DEFAULT 1.0;
290 l_init_msg_list VARCHAR2(1) ;
291 l_return_status VARCHAR2(1);
292 l_msg_count NUMBER;
293 l_msg_data VARCHAR2(2000);
294
295 l_type CONSTANT AR_ADJUSTMENTS.TYPE%TYPE := 'INVOICE';
296 l_created_from CONSTANT AR_ADJUSTMENTS.CREATED_FROM%TYPE := 'ADJ-API';
297
298 l_adjsts_lns_id OKL_TXL_ADJSTS_LNS_B.ID%TYPE;
299 l_new_adj_id OKL_TXL_ADJSTS_LNS_B.RECEIVABLES_ADJUSTMENT_ID%TYPE;
300
301 l_payment_schedule_id AR_ADJUSTMENTS.PAYMENT_SCHEDULE_ID%TYPE;
302 l_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL;
303 l_amount AR_ADJUSTMENTS.AMOUNT%TYPE;
304
305 l_receivables_trx_id AR_ADJUSTMENTS.RECEIVABLES_TRX_ID%TYPE;
306 l_receivables_trx_name AR_RECEIVABLES_TRX_ALL.NAME%TYPE;
307
308 l_code_combination_id AR_ADJUSTMENTS.CODE_COMBINATION_ID%TYPE;
309 l_apply_date AR_ADJUSTMENTS.APPLY_DATE%TYPE;
310 l_gl_date AR_ADJUSTMENTS.GL_DATE%TYPE;
311 l_adjustment_reason_code AR_ADJUSTMENTS.REASON_CODE%TYPE;
312 l_comments AR_ADJUSTMENTS.COMMENTS%TYPE;
313
314 l_set_of_books_id AR_ADJUSTMENTS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL;
315
316 x_new_adj_number AR_ADJUSTMENTS.ADJUSTMENT_NUMBER%TYPE;
317
318 l_org_id NUMBER DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
319
320 ------------------------------
321 -- DECLARE Record/Table Types
322 ------------------------------
323
324 l_adj_rec ar_adjustments%rowtype;
325 l_ajlv_rec ajlv_rec_type;
326 x_ajlv_rec ajlv_rec_type;
327
328 l_ajlv_tbl okl_txl_adjsts_lns_pub.ajlv_tbl_type;
329 x_ajlv_tbl okl_txl_adjsts_lns_pub.ajlv_tbl_type;
330 l_counter NUMBER := 1;
331
332 ------------------------------
333 -- DECLARE Exceptions
334 ------------------------------
335
336
337 ------------------------------
338 -- DECLARE Cursors
339 ------------------------------
340
341 CURSOR c_get_receivables_trx_id (cp_set_of_books_id IN NUMBER) IS
342 SELECT RECEIVABLES_TRX_ID, NAME
343 FROM ar_receivables_trx_all
344 WHERE set_of_books_id = cp_set_of_books_id
345 AND name = 'OKL Adjustment';
346
347
348 ------------------------------
349
350 CURSOR c_get_cont_num(cp_psl_id IN NUMBER) IS
351 /*SELECT DISTINCT(contract_number)
352 FROM okl_bpd_leasing_payment_trx_v
353 WHERE payment_schedule_id = cp_psl_id; */ -- Bug 6358836
354 select distinct ractrl.interface_line_attribute6
355 from ra_customer_trx_lines_all ractrl,
356 ar_payment_schedules_all aps
357 where aps.customer_trx_id = ractrl.customer_trx_id
358 and ractrl.line_type = 'LINE'
359 and aps.payment_schedule_id = cp_psl_id;
360
361 ------------------------------
362
363 l_is_cons_inv VARCHAR2(1) := 'N';
364 l_psl_id NUMBER;
365 l_is_cons_invoice VARCHAR2(1);
366 l_commit_flag VARCHAR2(3) := p_commit_flag;
367 l_chk_approval_limits VARCHAR2(3) := p_chk_approval_limits;
368 l_llca_adj_trx_lines_tbl ar_adjust_pub.llca_adj_trx_line_tbl_type;
369 l_llca_adj_create_tbl_type ar_adjust_pub.llca_adj_create_tbl_type;
370
371 l_adj_hdr_tbl l_adj_hdr_tbl_type;
372 l_adj_lines_tbl l_adj_lines_tbl_type;
373 l_customer_trx_id ra_customer_trx_all.customer_trx_id%type;
374 l_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%type;
375
376 BEGIN
377 l_set_of_books_id := Okl_Accounting_Util.GET_SET_OF_BOOKS_ID;
378 l_psl_id := p_psl_id;
379
380 OPEN c_get_receivables_trx_id(l_set_of_books_id);
381 FETCH c_get_receivables_trx_id INTO l_receivables_trx_id, l_receivables_trx_name;
382 CLOSE c_get_receivables_trx_id;
383
384 IF l_receivables_trx_id IS NULL THEN
385
386 -- Message Text: Invalid receivables transaction
387 x_return_status := OKC_API.G_RET_STS_ERROR;
388 OKC_API.set_message( p_app_name => G_APP_NAME
389 ,p_msg_name =>'OKL_BPD_RECV_ACTVTY_NOT_SET'
390 );
391
392 --RAISE G_EXCEPTION_HALT_VALIDATION; --bug 6727171
393 RAISE OKL_API.G_EXCEPTION_ERROR; -- bug 6727171
394 END IF;
395
396 get_adj_header_and_lines (p_api_version => l_api_version
397 ,p_init_msg_list => l_init_msg_list
398 ,p_psl_id => l_psl_id
399 ,x_return_status => l_return_status
400 ,x_msg_count => l_msg_count
401 ,x_msg_data => l_msg_data
402 ,x_adj_hdr_tbl => l_adj_hdr_tbl
403 ,x_adj_lines_tbl => l_adj_lines_tbl);
404
405 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
406 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
407 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
408 RAISE OKL_API.G_EXCEPTION_ERROR;
409 END IF;
410
411 l_is_cons_invoice := 'N';
412 IF l_adj_hdr_tbl.COUNT > 0 THEN
413 FOR i IN l_adj_hdr_tbl.FIRST..l_adj_hdr_tbl.LAST
414 LOOP
415 l_adj_lines_tbl := l_adj_hdr_tbl(i).lines;
416 l_is_cons_invoice := l_adj_hdr_tbl(i).cons_invoice;
417
418 IF l_adj_lines_tbl.COUNT > 0 THEN
419 IF l_is_cons_invoice = 'Y' THEN
420 l_apply_date := l_adj_lines_tbl(1).apply_date;
421 l_gl_date := l_adj_lines_tbl(1).gl_date;
422 l_adjustment_reason_code := l_adj_lines_tbl(1).adjustment_reason_code;
423 l_comments := l_adj_lines_tbl(1).comments;
424 l_adjsts_lns_id := l_adj_lines_tbl(1).adjsts_lns_id;
425 l_payment_schedule_id := l_adj_lines_tbl(1).payment_schedule_id;
426 l_amount := l_adj_lines_tbl(1).amount;
427 l_code_combination_id := l_adj_lines_tbl(1).code_combination_id;
428
429 l_adj_rec.type := l_type;
430 l_adj_rec.created_from := l_created_from;
431 l_adj_rec.apply_date := l_apply_date;
432 l_adj_rec.gl_date := l_gl_date;
433 l_adj_rec.reason_code := l_adjustment_reason_code;
434 l_adj_rec.comments := l_comments;
435 l_adj_rec.payment_schedule_id := l_payment_schedule_id;
436 l_adj_rec.amount := (l_amount * -1);
437 l_adj_rec.code_combination_id := l_code_combination_id;
438 l_adj_rec.receivables_trx_id := l_receivables_trx_id;
439 ELSE --if it is regular R12 AR invoice
440 l_apply_date := l_adj_lines_tbl(1).apply_date;
441 l_gl_date := l_adj_lines_tbl(1).gl_date;
442 l_adjustment_reason_code := l_adj_lines_tbl(1).adjustment_reason_code;
443 l_comments := l_adj_lines_tbl(1).comments;
444 l_payment_schedule_id := l_adj_lines_tbl(1).payment_schedule_id;
445 l_code_combination_id := l_adj_lines_tbl(1).code_combination_id;
446 l_customer_trx_id := l_adj_lines_tbl(1).customer_trx_id;
447 l_customer_trx_line_id := l_adj_lines_tbl(1).customer_trx_line_id;
448
449 l_adj_rec.type := 'LINE';
450 l_adj_rec.created_from := l_created_from;
451 l_adj_rec.apply_date := l_apply_date;
452 l_adj_rec.gl_date := l_gl_date;
453 l_adj_rec.reason_code := l_adjustment_reason_code;
454 l_adj_rec.comments := l_comments;
455 l_adj_rec.payment_schedule_id := l_payment_schedule_id;
456 l_adj_rec.customer_trx_id := l_customer_trx_id;
457 l_adj_rec.ussgl_transaction_code := NULL;
458 l_adj_rec.code_combination_id := l_code_combination_id;
459 l_adj_rec.receivables_trx_id := null;
460
461 l_llca_adj_trx_lines_tbl.delete;
462 l_amount := 0;
463
464 For j in l_adj_lines_tbl.FIRST..l_adj_lines_tbl.LAST
465 LOOP
466 l_llca_adj_trx_lines_tbl(j).customer_trx_line_id:=l_adj_lines_tbl(j).customer_trx_line_id;
467 l_llca_adj_trx_lines_tbl(j).line_amount:= (-1)*l_adj_lines_tbl(j).amount;
468 l_llca_adj_trx_lines_tbl(j).receivables_trx_id:=l_receivables_trx_id;
469 l_amount := l_amount + l_adj_lines_tbl(j).amount;
470
471 l_ajlv_tbl(l_counter).id := l_adj_lines_tbl(j).adjsts_lns_id;
472 l_counter := l_counter + 1;
473 END LOOP;
474 END IF;
475 END IF;
476
477 LOOP
478 IF l_gl_date IS NULL THEN
479 l_gl_date := SYSDATE;
480 END IF;
481
482 IF l_apply_date IS NULL OR l_adjustment_reason_code IS NULL OR
483 l_payment_schedule_id IS NULL OR l_amount IS NULL THEN
484
485 -- Missing mandatory fields for cash application process
486 OKC_API.set_message( p_app_name => G_APP_NAME
487 ,p_msg_name => 'OKL_BPD_ADJUST_MAN'
488 ,p_token1 => 'APPLY_DATE'
489 ,p_token1_value => l_apply_date
490 ,p_token2 => 'ADJUSTMENT_REASON_CODE'
491 ,p_token2_value => l_adjustment_reason_code
492 ,p_token3 => 'PAYMENT_SCHEDULE_ID'
493 ,p_token3_value => l_payment_schedule_id
494 ,p_token4 => 'AMOUNT'
495 ,p_token4_value => l_amount
496 ,p_token5 => 'RECEIVABLES_TRX_ID'
497 ,p_token5_value => l_receivables_trx_id
498 );
499 EXIT; -- move to next record.
500 END IF;
501
502 -- Start of wraper code generated automatically
503 -- by Debug code generator for AR_ADJUST_PUB.CREATE_ADJUSTMENT
504 IF(L_DEBUG_ENABLED='Y') THEN
505 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
506 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
507 END IF;
508 IF(IS_DEBUG_PROCEDURE_ON) THEN
509 BEGIN
510 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLROCAB.pls call AR_ADJUST_PUB.CREATE_ADJUSTMENT ');
511 END;
512 END IF;
513
514 IF l_is_cons_invoice = 'Y' THEN
515 AR_ADJUST_PUB.CREATE_ADJUSTMENT ( p_api_name => 'AR_ADJUST_PUB'
516 ,p_api_version => 1.0
517 ,p_msg_count => l_msg_count
518 ,p_msg_data => l_msg_data
519 ,p_return_status => l_return_status
520 ,p_adj_rec => l_adj_rec
521 ,p_commit_flag => l_commit_flag
522 ,p_chk_approval_limits => l_chk_approval_limits
523 ,p_new_adjust_number => x_new_adj_number
524 ,p_new_adjust_id => x_new_adj_id
525 );
526 ELSE
527 AR_ADJUST_PUB.CREATE_LINELEVEL_ADJUSTMENT
528 ( p_api_name => 'AR_ADJUST_PUB'
529 ,p_api_version => 1.0
530 ,p_msg_count => l_msg_count
531 ,p_msg_data => l_msg_data
532 ,p_return_status => l_return_status
533 ,p_adj_rec => l_adj_rec
534 ,p_commit_flag => l_commit_flag
535 ,p_chk_approval_limits => l_chk_approval_limits
536 ,p_check_amount => 'F'
537 ,p_llca_adj_trx_lines_tbl => l_llca_adj_trx_lines_tbl
538 ,p_move_deferred_tax => 'Y'
539 ,p_llca_adj_create_tbl_type => l_llca_adj_create_tbl_type
540 ,p_called_from => 'OKL-ADJ'
541 ,p_old_adjust_id => null
542 );
543 IF l_llca_adj_create_tbl_type.COUNT > 0 THEN
544 IF l_llca_adj_create_tbl_type.exists(l_llca_adj_create_tbl_type.LAST) THEN
545 x_new_adj_id := l_llca_adj_create_tbl_type(l_llca_adj_create_tbl_type.LAST).adjustment_id;
546 END IF;
547 END IF;
548 END IF;
549 IF(IS_DEBUG_PROCEDURE_ON) THEN
550 BEGIN
551 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLROCAB.pls call AR_ADJUST_PUB.CREATE_ADJUSTMENT ');
552 END;
553 END IF;
554 -- End of wraper code generated automatically by Debug code generator for AR_ADJUST_PUB.CREATE_ADJUSTMENT
555
556 OPEN c_get_cont_num(l_payment_schedule_id);
557 FETCH c_get_cont_num INTO l_contract_number;
558 CLOSE c_get_cont_num;
559
560 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) OR
561 (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
562
563 OKC_API.set_message( p_app_name => G_APP_NAME
564 ,p_msg_name => 'OKL_BPD_NO_ADJUST_CREATED'
565 ,p_token1 => 'CONTRACT_NUMBER'
566 ,p_token1_value => l_contract_number
567 ,p_token2 => 'AMOUNT'
568 ,p_token2_value => l_amount
569 );
570 EXIT;
571 ELSE
572 OKC_API.set_message( p_app_name => G_APP_NAME
573 ,p_msg_name => 'OKL_BPD_ADJUST_CREATED'
574 ,p_token1 => 'CONTRACT_NUMBER'
575 ,p_token1_value => l_contract_number
576 ,p_token2 => 'AMOUNT'
577 ,p_token2_value => l_amount
578 );
579 END IF;
580
581 For j in l_ajlv_tbl.FIRST..l_ajlv_tbl.LAST
582 LOOP
583 l_new_adj_id := x_new_adj_id;
584 l_ajlv_tbl(j).receivables_adjustment_id := l_new_adj_id;
585 END LOOP;
586
587 -- Start of wraper code generated automatically by Debug code generator for OKL_TXL_ADJSTS_LNS_PUB.UPDATE_TXL_ADJSTS_LNS
588 IF(IS_DEBUG_PROCEDURE_ON) THEN
589 BEGIN
590 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLROCAB.pls call OKL_TXL_ADJSTS_LNS_PUB.UPDATE_TXL_ADJSTS_LNS ');
591 END;
592 END IF;
593 OKL_TXL_ADJSTS_LNS_PUB.UPDATE_TXL_ADJSTS_LNS(l_api_version
594 ,l_init_msg_list
595 ,l_return_status
596 ,l_msg_count
597 ,l_msg_data
598 ,l_ajlv_tbl
599 ,x_ajlv_tbl);
600 IF(IS_DEBUG_PROCEDURE_ON) THEN
601 BEGIN
602 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLROCAB.pls call OKL_TXL_ADJSTS_LNS_PUB.UPDATE_TXL_ADJSTS_LNS ');
603 END;
604 END IF;
605 -- End of wraper code generated automatically by Debug code generator for OKL_TXL_ADJSTS_LNS_PUB.UPDATE_TXL_ADJSTS_LNS
606
607 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
608 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
609 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
610 RAISE OKL_API.G_EXCEPTION_ERROR;
611 END IF;
612 EXIT;
613 END LOOP;
614 END LOOP;
615 END IF;
616
617 x_return_status := l_return_status;
618
619 EXCEPTION
620
621 WHEN OTHERS THEN
622 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
623 x_msg_count := l_msg_count ;
624 x_msg_data := sqlerrm ;
625
626 END create_adjustments;
627
628 /*===========================================================================+
629 | PROCEDURE |
630 | iex_create_adjustments |
631 | |
632 | DESCRIPTION |
633 | This is the main routine to create an adjustment in AR |
634 | specifically for IEX guys. |
635 | |
636 | ARGUMENTS : IN: |
637 | p_api_name |
638 | p_api_version |
639 | p_init_msg_list |
640 | p_commit_flag |
641 | p_psl_id |
642 | p_chk_approval_limits |
643 | : OUT: |
644 | x_new_adj_id |
645 | x_return_status |
646 | x_msg_count |
647 | x_msg_data |
648 | |
649 | SCOPE - PRIVATE |
650 | |
651 | NOTES |
652 | |
653 | MODIFICATION HISTORY |
654 | Bruno Vaghela 09-OCT-02 Created |
655 | varao 19-SEP-05 Addressed bug 4505226 |
656 | varao 11-NOV-05 Addressed bug 4728481 |
657 | varao 17-NOV-05 Addressed bug 4622198 |
658 +===========================================================================*/
659
660
661 PROCEDURE iex_create_adjustments ( p_api_version IN NUMBER
662 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
663 ,p_commit_flag IN VARCHAR2 DEFAULT OKL_API.G_TRUE
664 ,p_psl_id IN NUMBER
665 ,p_chk_approval_limits IN VARCHAR2 DEFAULT OKL_API.G_TRUE
666 ,x_new_adj_id OUT NOCOPY NUMBER
667 ,x_return_status OUT NOCOPY VARCHAR2
668 ,x_msg_count OUT NOCOPY NUMBER
669 ,x_msg_data OUT NOCOPY VARCHAR2
670 )IS
671
672 ------------------------------
673 -- DECLARE Local variables
674 ------------------------------
675
676 l_api_version NUMBER DEFAULT 1.0;
677 l_init_msg_list VARCHAR2(1) ;
678 l_return_status VARCHAR2(1);
679 l_msg_count NUMBER;
680 l_msg_data VARCHAR2(2000);
681
682 l_type CONSTANT AR_ADJUSTMENTS.TYPE%TYPE := 'INVOICE';
683 l_created_from CONSTANT AR_ADJUSTMENTS.CREATED_FROM%TYPE := 'ADJ-API';
684
685 l_adjsts_lns_id OKL_TXL_ADJSTS_LNS_B.ID%TYPE;
686 l_new_adj_id OKL_TXL_ADJSTS_LNS_B.RECEIVABLES_ADJUSTMENT_ID%TYPE;
687
688 l_payment_schedule_id AR_ADJUSTMENTS.PAYMENT_SCHEDULE_ID%TYPE;
689 l_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL;
690 l_amount AR_ADJUSTMENTS.AMOUNT%TYPE;
691
692 l_receivables_trx_id AR_ADJUSTMENTS.RECEIVABLES_TRX_ID%TYPE;
693 l_receivables_trx_name AR_RECEIVABLES_TRX_ALL.NAME%TYPE;
694
695 l_code_combination_id AR_ADJUSTMENTS.CODE_COMBINATION_ID%TYPE;
696 l_apply_date AR_ADJUSTMENTS.APPLY_DATE%TYPE;
697 l_gl_date AR_ADJUSTMENTS.GL_DATE%TYPE;
698 l_adjustment_reason_code AR_ADJUSTMENTS.REASON_CODE%TYPE;
699 l_comments AR_ADJUSTMENTS.COMMENTS%TYPE;
700
701 l_chk_approval_limits VARCHAR2(3);
702 l_commit_flag VARCHAR2(3);
703
704 l_set_of_books_id AR_ADJUSTMENTS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL;
705
706 l_org_id NUMBER DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
707 -- 19-Sep-05 varao bug 4505226 Start
708 l_pdt_id NUMBER := 0;
709 l_try_id NUMBER := NULL;
710
711 l_acc_gen_primary_key_tbl OKL_ACCOUNT_DIST_PUB.acc_gen_primary_key;
712
713 -- 19-Sep-05 varao bug 4505226 End
714
715 ------------------------------
716 -- DECLARE Record/Table Types
717 ------------------------------
718
719 l_adjv_rec adjv_rec_type;
720 x_adjv_rec adjv_rec_type;
721 l_ajlv_rec ajlv_rec_type;
722 x_ajlv_rec ajlv_rec_type;
723 l_ajlv_new_rec ajlv_rec_type;
724
725 ------------------------------
726 -- DECLARE Exceptions
727 ------------------------------
728
729
730 ------------------------------
731 -- DECLARE Cursors
732 ------------------------------
733
734 CURSOR c_get_receivables_trx_id (cp_set_of_books_id IN NUMBER) IS
735 SELECT RECEIVABLES_TRX_ID, NAME
736 FROM ar_receivables_trx_all
737 WHERE set_of_books_id = cp_set_of_books_id
738 AND name = 'OKL Adjustment';
739
740
741 ------------------------------
742
743 -- 19-Sep-05 varao bug 4505226 Start
744
745 /* Commented since this cursor is not used now
746 CURSOR c_get_cont_num(cp_psl_id IN NUMBER) IS
747 SELECT DISTINCT(contract_number)
748 FROM okl_bpd_leasing_payment_trx_v
749 WHERE payment_schedule_id = cp_psl_id;
750 */
751 ------------------------------
752
753 -- Cursor to get the details for a payment schedule id
754 /* Bug 6727171 Start
755 CURSOR c_pmnt_schedule_dtls(cp_psl_id IN NUMBER) IS
756 SELECT OBLP.amount_due_remaining AMOUNT_DUE_REMAINING,
757 OBLP.stream_type_id STREAM_TYPE_ID,
758 OBLP.contract_line_id CONTRACT_LINE_ID,
759 OBLP.stream_name STREAM_NAME,
760 OBLP.contract_id CONTRACT_ID,
761 OBLP.contract_number CONTRACT_NUMBER,
762 OBLP.receivables_invoice_number AR_INVOICE_NUMBER,
763 OBLP.currency_code CURRENCY_CODE,
764 OTIL.id TIL_ID,
765 -999 TLD_ID
766 FROM OKL_BPD_LEASING_PAYMENT_TRX_V OBLP,
767 OKL_TXL_AR_INV_LNS_B OTIL
768 WHERE OBLP.payment_schedule_id = cp_psl_id
769 AND OBLP.receivables_invoice_id = OTIL.receivables_invoice_id
770 AND oblp.stream_type_id = otil.sty_id --added by dkagrawa for performance
771 AND OBLP.amount_due_remaining > 0
772 UNION
773 SELECT OBLP.amount_due_remaining AMOUNT_DUE_REMAINING,
774 OBLP.stream_type_id STREAM_TYPE_ID,
775 OBLP.contract_line_id CONTRACT_LINE_ID,
776 OBLP.stream_name STREAM_NAME,
777 OBLP.contract_id CONTRACT_ID,
778 OBLP.contract_number CONTRACT_NUMBER,
779 OBLP.receivables_invoice_number AR_INVOICE_NUMBER,
780 OBLP.currency_code CURRENCY_CODE,
781 OTAI.til_id_details TIL_ID,
782 OTAI.id TLD_ID
783 FROM OKL_BPD_LEASING_PAYMENT_TRX_V OBLP,
784 OKL_TXD_AR_LN_DTLS_B OTAI
785 WHERE OBLP.payment_schedule_id = cp_psl_id
786 AND OBLP.receivables_invoice_id = OTAI.receivables_invoice_id
787 AND oblp.stream_type_id = otai.sty_id --added by dkagrawa for performance
788 AND OBLP.amount_due_remaining > 0;
789
790 Bug 6727171 End */
791
792 --Bug 6727171 Start
793
794 CURSOR c_pmnt_schedule_dtls(cp_psl_id IN NUMBER) IS
795 SELECT RACTRL.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING,
796 RACTRL.STY_ID STREAM_TYPE_ID,
797 RACTRL.KLE_ID CONTRACT_LINE_ID,
798 RACTRL.KHR_ID CONTRACT_ID,
799 RACTRL.CONTRACT_NUMBER CONTRACT_NUMBER,
800 RACTRL.CUSTOMER_TRX_ID AR_INVOICE_NUMBER,
801 RACTRL.STREAM_TYPE STREAM_NAME,
802 OKL_AM_UTIL_PVT.get_chr_currency(RACTRL.KHR_ID) CURRENCY_CODE,
803 RACTRL.til_id_details TIL_ID,
804 RACTRL.TLD_ID TLD_ID
805 FROM OKL_BPD_TLD_AR_LINES_V RACTRL,
806 AR_PAYMENT_SCHEDULES_ALL APS
807 WHERE APS.PAYMENT_SCHEDULE_ID = cp_psl_id
808 AND RACTRL.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
809 AND RACTRL.amount_due_remaining > 0;
810
811 --Bug 6727171 End
812
813 CURSOR c_pmnt_schedule_summary(cp_psl_id IN NUMBER) IS
814 SELECT SUM(RACTRL.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
815 FROM OKL_BPD_TLD_AR_LINES_V RACTRL,
816 AR_PAYMENT_SCHEDULES_ALL APS
817 WHERE APS.PAYMENT_SCHEDULE_ID = cp_psl_id
818 AND RACTRL.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
819 AND RACTRL.amount_due_remaining > 0;
820
821
822 c_pmnt_schedule_dtls_rec c_pmnt_schedule_dtls%ROWTYPE;
823
824 l_total_amt_due_remaining NUMBER;
825
826 ------------------------------
827
828 -- Cursor to get the product of the contract
829 CURSOR c_prod_id(cp_khr_id IN NUMBER) IS
830 SELECT KHR.pdt_id
831 FROM OKL_K_HEADERS_V KHR
832 WHERE KHR.id = cp_khr_id;
833
834 ------------------------------
835
836 -- Cursor to get the distribution for the transaction id and
837 -- transaction table
838 -- Make sure we get the debit distribution and also it is 100percent
839 CURSOR c_code_combination_id(cp_source_id IN NUMBER,
840 cp_source_table IN VARCHAR2) IS
841 SELECT DST.code_combination_id
842 FROM OKL_TRNS_ACC_DSTRS DST
843 WHERE DST.source_id = cp_source_id
844 AND DST.source_table = cp_source_table
845 AND DST.cr_dr_flag = 'D'
846 AND DST.percentage = 100;
847
848 ------------------------------
849
850 -- Get transaction id for 'Adjustments' trx type
851 CURSOR c_try_id (cp_try_name IN VARCHAR2) IS
852 SELECT id
853 FROM okl_trx_types_tl t
854 WHERE UPPER (t.name)= UPPER (cp_try_name);
855
856 ------------------------------
857 -- 19-Sep-05 varao bug 4505226 End
858
859 -- Code added by varao for bug #4728481 - START
860 CURSOR get_psl_context IS
861 SELECT org_id
862 FROM ar_payment_schedules_all
863 WHERE payment_schedule_id = p_psl_id;
864 -- Code added by varao for bug #4728481 - END
865
866 --Bug 6316320 dpsingh start
867 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.tmpl_identify_tbl_type;
868 l_dist_info_tbl Okl_Account_Dist_Pvt.dist_info_tbl_type;
869 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
870 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
871 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
872 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
873 l_account_derivation OKL_SYS_ACCT_OPTS.ACCOUNT_DERIVATION%TYPE;
874
875 CURSOR get_account_derivation_meth IS
876 SELECT ACCOUNT_DERIVATION
877 FROM OKL_SYS_ACCT_OPTS;
878 --Bug 6316320 dpsingh end
879
880 -- Bug 7138249 start
881 l_functional_currency_code VARCHAR2(15);
882 l_currency_code VARCHAR2(200);
883 l_contract_currency_code VARCHAR2(15);
884 l_currency_conversion_type VARCHAR2(30);
885 l_currency_conversion_rate NUMBER;
886 l_currency_conversion_date DATE;
887 l_converted_amount NUMBER;
888 l_trans_meaning VARCHAR2(200);
889 -- Bug 7138249 end
890
891 BEGIN
892
893 -- Code added by varao for bug #4728481 - START
894 OPEN get_psl_context;
895 FETCH get_psl_context INTO l_org_id;
896 CLOSE get_psl_context;
897 -- Code added by varao for bug #4728481 - END
898
899 l_set_of_books_id := Okl_Accounting_Util.GET_SET_OF_BOOKS_ID;
900
901 l_commit_flag := p_commit_flag;
902 l_payment_schedule_id := p_psl_id;
903 l_chk_approval_limits := p_chk_approval_limits;
904
905 OPEN c_get_receivables_trx_id(l_set_of_books_id);
906 FETCH c_get_receivables_trx_id INTO l_receivables_trx_id, l_receivables_trx_name;
907 CLOSE c_get_receivables_trx_id;
908
909 IF l_receivables_trx_id IS NULL THEN
910 -- Message Text: Invalid receivables transaction
911 x_return_status := OKC_API.G_RET_STS_ERROR;
912 OKC_API.set_message( p_app_name => G_APP_NAME
913 ,p_msg_name =>'OKL_BPD_RECV_ACTVTY_NOT_SET'
914 );
915
916 --RAISE G_EXCEPTION_HALT_VALIDATION; -- bug 6727171
917 RAISE OKL_API.G_EXCEPTION_ERROR; -- bug 6727171
918 END IF;
919
920 -- 19-Sep-05 varao bug 4505226 Start
921 --Get amount due remaining for payment schedule
922 OPEN c_pmnt_schedule_summary(p_psl_id);
923 FETCH c_pmnt_schedule_summary INTO l_total_amt_due_remaining;
924 CLOSE c_pmnt_schedule_summary;
925
926 IF l_total_amt_due_remaining IS NOT NULL AND l_total_amt_due_remaining <> 0 THEN
927
928 -- Create adjusment header record in okl_trx_ar_adjsts_b
929 l_adjv_rec.adjustment_reason_code:= 'WRITE OFF';
930 l_adjv_rec.apply_date := SYSDATE;
931 l_adjv_rec.gl_date := SYSDATE;
932 l_adjv_rec.trx_status_code := 'WORKING';
933 --Bug 6316320 dpsingh start
934 -- Get the transaction id for 'Adjustments' trx type
935 OPEN c_try_id('Adjustments');
936 FETCH c_try_id INTO l_try_id;
937 CLOSE c_try_id;
938
939 IF l_try_id IS NULL THEN
940 OKL_API.set_message(p_app_name => G_APP_NAME,
941 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
942 p_token1 => 'TRY_NAME',
943 p_token1_value => 'Adjustments');
944 RAISE OKL_API.G_EXCEPTION_ERROR;
945 END IF;
946 l_adjv_rec.try_id :=l_try_id;
947 --Bug 6316320 dpsingh end
948 okl_trx_ar_adjsts_pub.insert_trx_ar_adjsts(
949 p_api_version => l_api_version
950 ,p_init_msg_list => l_init_msg_list
951 ,x_return_status => l_return_status
952 ,x_msg_count => l_msg_count
953 ,x_msg_data => l_msg_data
954 ,p_adjv_rec => l_adjv_rec
955 ,x_adjv_rec => x_adjv_rec);
956
957 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
958 OKL_API.set_message( p_app_name => G_APP_NAME,
959 p_msg_name => 'OKL_AM_ERR_ADJST_BAL');
960 END IF;
961
962 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
963 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
964 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
965 RAISE OKL_API.G_EXCEPTION_ERROR;
966 END IF;
967
968 -- Get the payment schedule details
969 OPEN c_pmnt_schedule_dtls(p_psl_id);
970 LOOP
971 FETCH c_pmnt_schedule_dtls INTO c_pmnt_schedule_dtls_rec;
972 EXIT WHEN c_pmnt_schedule_dtls%NOTFOUND;
973
974 -- Create adjusment line record in okl_txl_adjsts_lns_b
975 l_ajlv_rec.adj_id := x_adjv_rec.id;
976 l_ajlv_rec.psl_id := p_psl_id;
977 l_ajlv_rec.amount := c_pmnt_schedule_dtls_rec.amount_due_remaining;
978 l_ajlv_rec.til_id := c_pmnt_schedule_dtls_rec.til_id;
979
980 IF c_pmnt_schedule_dtls_rec.tld_id <> -999
981 AND c_pmnt_schedule_dtls_rec.tld_id IS NOT NULL
982 AND c_pmnt_schedule_dtls_rec.tld_id <> OKL_API.G_MISS_NUM THEN
983 l_ajlv_rec.tld_id := c_pmnt_schedule_dtls_rec.tld_id;
984 END IF;
985
986 -- Bug 7138249 start
987 -- Get the functional currency from AM_Util
988 l_functional_currency_code := OKL_AM_UTIL_PVT.get_functional_currency;
989
990 -- Get the contract currency code
991 l_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(
992 c_pmnt_schedule_dtls_rec.contract_id);
993
994 l_trans_meaning := OKL_AM_UTIL_PVT.get_lookup_meaning(
995 p_lookup_type => 'OKL_ACCOUNTING_EVENT_TYPE',
996 p_lookup_code=> 'ADJUSTMENTS',
997 p_validate_yn => 'Y');
998 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
999 OKL_API.set_message(
1000 p_app_name => G_APP_NAME,
1001 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
1002 p_token1 => 'TRY_NAME',
1003 p_token1_value => l_trans_meaning);
1004
1005 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1006 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1007 'OKL_AM_BAL_WRITEOFF_PVT.write_off_balances.',
1008 'balance_writeoff_trn_error = '||l_return_status );
1009 END IF;
1010
1011 END IF;
1012
1013 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
1014 p_khr_id => c_pmnt_schedule_dtls_rec.contract_id,
1015 p_to_currency => l_functional_currency_code,
1016 p_transaction_date => SYSDATE,
1017 p_amount => c_pmnt_schedule_dtls_rec.amount_due_remaining,
1018 x_return_status => l_return_status,
1019 x_contract_currency => l_contract_currency_code,
1020 x_currency_conversion_type => l_currency_conversion_type,
1021 x_currency_conversion_rate => l_currency_conversion_rate,
1022 x_currency_conversion_date => l_currency_conversion_date,
1023 x_converted_amount => l_converted_amount);
1024 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1025 OKL_API.set_message(
1026 p_app_name => G_APP_NAME,
1027 p_msg_name => 'OKL_AM_ERR_ACC_ENT',
1028 p_token1 => 'TRX_TYPE',
1029 p_token1_value => l_trans_meaning);
1030
1031 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1032 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1033 'OKL_CREATE_ADJST_PVT.write_off_balances.',
1034 'currency_conv_error = '||l_return_status );
1035 END IF;
1036
1037 RAISE OKL_API.G_EXCEPTION_ERROR;
1038
1039 END IF;
1040
1041 -- Bug 7138249 end
1042
1043 --Bug 6316320 dpsingh start
1044 l_ajlv_rec.khr_id := c_pmnt_schedule_dtls_rec.contract_id;
1045 l_ajlv_rec.sty_id := c_pmnt_schedule_dtls_rec.stream_type_id;
1046 l_ajlv_rec.kle_id := c_pmnt_schedule_dtls_rec.contract_line_id ;
1047 --Bug 6316320 dpsingh end
1048 okl_txl_adjsts_lns_pub.insert_txl_adjsts_lns(
1049 p_api_version => l_api_version
1050 ,p_init_msg_list => l_init_msg_list
1051 ,x_return_status => l_return_status
1052 ,x_msg_count => l_msg_count
1053 ,x_msg_data => l_msg_data
1054 ,p_ajlv_rec => l_ajlv_rec
1055 ,x_ajlv_rec => x_ajlv_rec);
1056
1057 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1058 OKL_API.set_message( p_app_name => G_APP_NAME,
1059 p_msg_name => 'OKL_AM_ERR_ADJST_BAL');
1060 END IF;
1061
1062 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1063 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1064 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1065 RAISE OKL_API.G_EXCEPTION_ERROR;
1066 END IF;
1067
1068 -- Get the product id
1069 OPEN c_prod_id(c_pmnt_schedule_dtls_rec.contract_id);
1070 FETCH c_prod_id INTO l_pdt_id;
1071 CLOSE c_prod_id;
1072
1073 l_contract_number := c_pmnt_schedule_dtls_rec.contract_number;
1074
1075 IF l_pdt_id IS NULL OR l_pdt_id = 0 THEN
1076 OKL_API.set_message(p_app_name => G_APP_NAME,
1077 p_msg_name => 'OKL_AM_PRODUCT_ID_ERROR',
1078 p_token1 => 'CONTRACT_NUMBER',
1079 p_token1_value => l_contract_number);
1080 RAISE OKL_API.G_EXCEPTION_ERROR;
1081 END IF;
1082
1083 -- Do accounting entries to get code_combination_id
1084 -- Set the tmpl_identify_rec in parameter
1085 l_tmpl_identify_tbl(1).product_id := l_pdt_id;
1086 l_tmpl_identify_tbl(1).transaction_type_id := l_try_id;
1087 l_tmpl_identify_tbl(1).memo_yn := G_NO;
1088 l_tmpl_identify_tbl(1).prior_year_yn := G_NO;
1089 l_tmpl_identify_tbl(1).stream_type_id := c_pmnt_schedule_dtls_rec.stream_type_id;
1090
1091 -- Set the dist_info_rec in parameter
1092 l_dist_info_tbl(1).source_id := x_ajlv_rec.id;
1093 l_dist_info_tbl(1).source_table := 'OKL_TXL_ADJSTS_LNS_B';
1094 l_dist_info_tbl(1).accounting_date := SYSDATE;
1095 l_dist_info_tbl(1).gl_reversal_flag := G_NO;
1096 l_dist_info_tbl(1).post_to_gl := G_NO;
1097 l_dist_info_tbl(1).contract_id := c_pmnt_schedule_dtls_rec.contract_id;
1098 l_dist_info_tbl(1).amount := c_pmnt_schedule_dtls_rec.amount_due_remaining;
1099 l_dist_info_tbl(1).currency_code := c_pmnt_schedule_dtls_rec.currency_code;
1100 -- Bug 7138249 start
1101 IF l_functional_currency_code <> l_contract_currency_code THEN
1102 l_dist_info_tbl(1).currency_conversion_type := l_currency_conversion_type;
1103 l_dist_info_tbl(1).currency_conversion_rate := l_currency_conversion_rate;
1104 l_dist_info_tbl(1).currency_conversion_date := l_currency_conversion_date;
1105 END IF;
1106 -- Bug 7138249 end
1107
1108
1109 OKL_ACC_CALL_PVT.okl_populate_acc_gen (
1110 p_contract_id => c_pmnt_schedule_dtls_rec.contract_id,
1111 p_contract_line_id => NULL,
1112 x_acc_gen_tbl => l_acc_gen_primary_key_tbl,
1113 x_return_status => l_return_status);
1114
1115 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1116 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1117 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1118 RAISE OKL_API.G_EXCEPTION_ERROR;
1119 END IF;
1120
1121 l_acc_gen_tbl(1).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
1122 l_acc_gen_tbl(1).source_id := x_ajlv_rec.id;
1123
1124 -- 19-Sep-05 varao bug 4622198 Start
1125 Okl_Securitization_Pvt.check_khr_ia_associated(
1126 p_api_version => l_api_version
1127 ,p_init_msg_list => l_init_msg_list
1128 ,x_return_status => l_return_status
1129 ,x_msg_count => l_msg_count
1130 ,x_msg_data => l_msg_data
1131 ,p_khr_id => c_pmnt_schedule_dtls_rec.contract_id
1132 ,p_scs_code => NULL
1133 ,p_trx_date => sysdate
1134 ,x_fact_synd_code => l_tmpl_identify_tbl(1).FACTORING_SYND_FLAG
1135 ,x_inv_acct_code => l_tmpl_identify_tbl(1).INVESTOR_CODE);
1136
1137 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1138 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1139 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1140 RAISE OKL_API.G_EXCEPTION_ERROR;
1141 END IF;
1142 -- 19-Sep-05 varao bug 4622198 End
1143
1144 -- Call accounting engine
1145 -- This will calculate the adjstmnts and generate accounting entries
1146 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
1147 p_api_version => l_api_version,
1148 p_init_msg_list => l_init_msg_list,
1149 x_return_status => l_return_status,
1150 x_msg_count => l_msg_count,
1151 x_msg_data => l_msg_data,
1152 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
1153 p_dist_info_tbl => l_dist_info_tbl,
1154 p_ctxt_val_tbl => l_ctxt_tbl,
1155 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
1156 x_template_tbl => l_template_out_tbl,
1157 x_amount_tbl => l_amount_out_tbl,
1158 p_trx_header_id => x_adjv_rec.id,
1159 p_trx_header_table =>'OKL_TRX_AR_ADJSTS_B');
1160
1161 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1162 OKL_API.set_message( p_app_name => G_APP_NAME,
1163 p_msg_name => 'OKL_AM_ERR_ACC_ENT_MSG',
1164 p_token1 => 'TRX_TYPE',
1165 p_token1_value => 'Adjustments',
1166 p_token2 => 'STREAM_TYPE',
1167 p_token2_value => c_pmnt_schedule_dtls_rec.stream_name);
1168 END IF;
1169
1170 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1171 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1172 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1173 RAISE OKL_API.G_EXCEPTION_ERROR;
1174 END IF;
1175
1176 OPEN get_account_derivation_meth;
1177 FETCH get_account_derivation_meth INTO l_account_derivation;
1178 CLOSE get_account_derivation_meth;
1179
1180 IF l_account_derivation = 'ATS' THEN
1181 -- Get the code_combination_id for the transaction
1182 OPEN c_code_combination_id(x_ajlv_rec.id, 'OKL_TXL_ADJSTS_LNS_B');
1183 FETCH c_code_combination_id INTO l_code_combination_id;
1184 CLOSE c_code_combination_id;
1185
1186 IF l_code_combination_id = -1 OR l_code_combination_id IS NULL THEN
1187 OKL_API.set_message( p_app_name => G_APP_NAME,
1188 p_msg_name => 'OKL_AM_CODE_CMB_ERROR',
1189 p_token1 => 'CONTRACT_NUMBER',
1190 p_token1_value => l_contract_number);
1191 RAISE OKL_API.G_EXCEPTION_ERROR;
1192 END IF;
1193
1194 -- Update adjusment line record in okl_txl_adjsts_lns_b with CCID
1195 l_ajlv_rec := l_ajlv_new_rec; -- Empty the rec
1196
1197 -- Set the rec with CCID got from accounting distibutions
1198 l_ajlv_rec.id := x_ajlv_rec.id;
1199 l_ajlv_rec.code_combination_id := l_code_combination_id;
1200
1201 x_ajlv_rec := l_ajlv_new_rec; -- Empty the rec
1202
1203 OKL_TXL_ADJSTS_LNS_PUB.update_txl_adjsts_lns(
1204 p_api_version => l_api_version,
1205 p_init_msg_list => l_init_msg_list,
1206 x_return_status => l_return_status,
1207 x_msg_count => l_msg_count,
1208 x_msg_data => l_msg_data,
1209 p_ajlv_rec => l_ajlv_rec,
1210 x_ajlv_rec => x_ajlv_rec);
1211
1212 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1213 OKL_API.set_message( p_app_name => G_APP_NAME,
1214 p_msg_name => 'OKL_AM_ERR_ADJST_BAL');
1215 END IF;
1216
1217 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1218 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1219 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1220 RAISE OKL_API.G_EXCEPTION_ERROR;
1221 END IF;
1222 END IF;
1223 -- 19-Sep-05 varao bug 4505226 End
1224 END LOOP;
1225
1226 --Now create adjustments through AR
1227 --Call create_adjustments API with l_payment_schedule_id as parameter
1228 create_adjustments ( p_api_version => l_api_version
1229 ,p_init_msg_list => l_init_msg_list
1230 ,p_psl_id => l_payment_schedule_id
1231 ,p_commit_flag => l_commit_flag
1232 ,p_chk_approval_limits => l_chk_approval_limits
1233 ,x_new_adj_id => l_new_adj_id
1234 ,x_return_status => l_return_status
1235 ,x_msg_count => l_msg_count
1236 ,x_msg_data => l_msg_data
1237 );
1238
1239 x_return_status := l_return_status;
1240 x_new_adj_id := l_new_adj_id;
1241
1242 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1243 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1244 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1245 RAISE OKL_API.G_EXCEPTION_ERROR;
1246 END IF;
1247
1248 ELSE
1249
1250 Okl_api.set_message( p_app_name => g_app_name
1251 , p_msg_name => 'OKL_NO_RECORD' ) ;
1252 RAISE OKL_API.G_EXCEPTION_ERROR;
1253
1254 END IF;
1255
1256 EXCEPTION
1257
1258 --Added snizam
1259 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1260 x_return_status := Okl_Api.G_RET_STS_ERROR;
1261 x_msg_count := l_msg_count ;
1262 x_msg_data := l_msg_data ;
1263
1264 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1265 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1266 x_msg_count := l_msg_count ;
1267 x_msg_data := l_msg_data ;
1268 --End snizam
1269
1270 WHEN OTHERS THEN
1271 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1272 x_msg_count := l_msg_count ;
1273 x_msg_data := l_msg_data ;
1274
1275 END iex_create_adjustments;
1276
1277 END OKL_CREATE_ADJST_PVT;