[Home] [Help]
PACKAGE BODY: APPS.OKL_ACCOUNT_DIST_MISC_PVT
Source
1 PACKAGE BODY OKL_ACCOUNT_DIST_MISC_PVT AS
2 /* $Header: OKLRTDSB.pls 120.2 2006/07/11 10:04:37 dkagrawa noship $ */
3
4 -- Start of wraper code generated automatically by Debug code generator
5 L_MODULE VARCHAR2(40) := 'LEASE.ACCOUNTING.TRANSACTIONS';
6 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
7 L_LEVEL_PROCEDURE NUMBER;
8 IS_DEBUG_PROCEDURE_ON BOOLEAN;
9 -- End of wraper code generated automatically by Debug code generator
10
11 PROCEDURE POPULATE_DSTR_REC(p_tabv_rec IN TABV_REC_TYPE,
12 x_tabv_rec OUT NOCOPY TABV_REC_TYPE,
13 p_mode IN VARCHAR2)
14
15 IS
16
17 l_curr_code OKL_TRNS_ACC_DSTRS.CURRENCY_CODE%TYPE;
18 l_currency_conversion_date OKL_TRNS_ACC_DSTRS.currency_conversion_date%TYPE;
19 l_currency_conversion_rate OKL_TRNS_ACC_DSTRS.currency_conversion_rate%TYPE;
20 l_currency_conversion_type OKL_TRNS_ACC_DSTRS.currency_conversion_type%TYPE;
21
22
23 l_date_transaction_occurred DATE;
24
25 -- Chnaged by Santonyr on 25-Nov-2002
26 -- Multi-Currency Changes. Get the currency conversion
27 -- factors from transaction table.
28
29 CURSOR tcn_csr(v_source_id NUMBER) IS
30 SELECT trunc(date_transaction_occurred),
31 currency_code,
32 currency_conversion_date,
33 currency_conversion_rate,
34 currency_conversion_type
35 FROM OKL_TRX_CONTRACTS
36 WHERE ID IN (SELECT TCN_ID
37 FROM OKL_TXL_CNTRCT_LNS
38 WHERE ID = v_source_id);
39
40 BEGIN
41
42 -- Commented out by Santonyr on 25-Nov-2002 for MUlti Currency Changes
43 -- Get the currency conversion factors from transaction table.
44
45 /*
46
47 -- since we are only supporting One Currency this phase,
48 -- Equate the Accounted Amount to Amount
49 -- Equate the currency code to the functional Currency
50 -- Wiil need change when multiple currencies are to be supported
51
52
53 l_curr_code := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
54 */
55
56 x_tabv_rec := p_tabv_rec;
57
58 OPEN tcn_csr(p_tabv_rec.source_id);
59 FETCH tcn_csr INTO
60 l_date_transaction_occurred,
61 l_curr_code,
62 l_currency_conversion_date,
63 l_currency_conversion_rate,
64 l_currency_conversion_type;
65 CLOSE tcn_csr;
66
67 IF p_mode = 'I' THEN
68
69 x_tabv_rec.amount := Okl_Accounting_Util.ROUND_AMOUNT
70 (p_amount => p_tabv_rec.amount,
71 p_currency_code => l_curr_code);
72
73 x_tabv_rec.accounted_amount := Okl_Accounting_Util.ROUND_AMOUNT
74 (p_amount => (p_tabv_rec.amount * NVL(l_currency_conversion_rate, 1)),
75 p_currency_code => l_curr_code);
76
77 -- Fixed bug 2559862
78
79 IF (x_tabv_rec.cr_dr_flag = 'D') THEN
80 x_tabv_rec.AE_LINE_TYPE := 'LEASE_DEBIT';
81 END IF;
82 IF (x_tabv_rec.cr_dr_flag = 'C') THEN
83 x_tabv_rec.AE_LINE_TYPE := 'LEASE_CREDIT';
84 END IF;
85
86 x_tabv_rec.currency_code := l_curr_code;
87 x_tabv_rec.post_to_gl := 'Y';
88 x_tabv_rec.original_dist_id := NULL;
89 x_tabv_rec.reverse_event_flag := 'N';
90 x_tabv_rec.gl_reversal_flag := 'N';
91 x_tabv_rec.posted_yn := 'N';
92 x_tabv_rec.percentage := NULL;
93 x_tabv_rec.currency_conversion_date := l_currency_conversion_date;
94 x_tabv_rec.currency_conversion_rate := l_currency_conversion_rate;
95 x_tabv_rec.currency_conversion_type := l_currency_conversion_type;
96
97 -- Added by HKPATEL for default date for bug 3254298
98 l_date_transaction_occurred := OKL_ACCOUNTING_UTIL.get_valid_gl_date(l_date_transaction_occurred);
99
100 IF l_date_transaction_occurred IS NULL THEN
101 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
102 p_msg_name => 'OKL_INVALID_GL_DATE');
103 RAISE OKL_API.G_EXCEPTION_ERROR;
104
105 END IF;
106 -- Added code ends here
107
108 x_tabv_rec.gl_date := l_date_transaction_occurred;
109
110 ELSE
111
112 -- Fixed bug 2559862
113
114 IF (x_tabv_rec.cr_dr_flag = 'D') THEN
115 x_tabv_rec.AE_LINE_TYPE := 'LEASE_DEBIT';
116 END IF;
117 IF (x_tabv_rec.cr_dr_flag = 'C') THEN
118 x_tabv_rec.AE_LINE_TYPE := 'LEASE_CREDIT';
119 END IF;
120
121 x_tabv_rec.amount := Okl_Accounting_Util.ROUND_AMOUNT
122 (p_amount => p_tabv_rec.amount,
123 p_currency_code => l_curr_code);
124
125 x_tabv_rec.accounted_amount := Okl_Accounting_Util.ROUND_AMOUNT
126 (p_amount => (p_tabv_rec.amount * NVL(l_currency_conversion_rate, 1)),
127 p_currency_code => l_curr_code);
128
129 END IF;
130
131 END POPULATE_DSTR_REC;
132
133
134
135 -- Function to check if the distributions have gone to Accounting SubLedger or GL
136
137 FUNCTION CHECK_DIST(p_source_id IN NUMBER,
138 p_source_table IN VARCHAR2)
139
140 RETURN NUMBER
141
142
143 IS
144
145 l_posted_yn VARCHAR2(1);
146 l_gl_transfer_flag VARCHAR2(1);
147
148 CURSOR dist_csr(v_source_id NUMBER,
149 v_source_table VARCHAR2) IS
150 SELECT POSTED_YN
151 FROM OKL_TRNS_ACC_DSTRS
152 WHERE source_id = v_source_id
153 AND source_table = v_source_table;
154
155
156 CURSOR aeh_csr(v_source_id NUMBER,
157 v_source_table VARCHAR2) IS
158
159 SELECT gl_transfer_flag
160 FROM OKL_AE_HEADERS aeh, OKL_ACCOUNTING_EVENTS aet
161 WHERE aeh.accounting_event_id = aet.accounting_event_id
162 AND aet.source_id = v_source_id
163 AND aet.source_table = v_source_table;
164
165 BEGIN
166
167 OPEN dist_csr(p_source_id, p_source_table);
168 FETCH dist_csr INTO l_posted_yn;
169 IF (dist_csr%NOTFOUND) THEN -- Distributions do not exist
170 CLOSE dist_csr;
171 RETURN 0;
172 END IF;
173 CLOSE dist_csr;
174
175 IF (l_posted_yn = 'N') THEN -- Distributions exist and are not posted to AE
176 RETURN 1;
177 ELSIF (l_posted_yn = 'Y') THEN -- Posted
178 OPEN aeh_csr(p_source_id, p_source_table);
179 FETCH aeh_csr INTO l_gl_transfer_flag;
180 CLOSE aeh_csr;
181 IF (l_gl_transfer_flag <> 'N') THEN
182 RETURN 3; -- Gone to GL
183 ELSE
184 RETURN 2; -- Not Gone to GL
185 END IF;
186 END IF;
187
188 END CHECK_DIST;
189
190
191
192 --Procedure to delete the lines from the Distribution Table and the AE tables in case of an updation
193
194 PROCEDURE DELETE_DIST_AE(p_flag IN VARCHAR2,
195 p_source_id IN NUMBER,
196 p_source_table IN VARCHAR2,
197 x_return_status OUT NOCOPY VARCHAR2)
198
199 IS
200
201 CURSOR dist_csr(v_source_id NUMBER, v_source_table VARCHAR2) IS
202 SELECT ID
203 FROM OKL_TRNS_ACC_DSTRS
204 WHERE source_id = v_source_id
205 AND source_table = v_source_table;
206
207 CURSOR aet_csr(v_source_id NUMBER, v_source_table VARCHAR2) IS
208 SELECT accounting_event_id
209 FROM OKL_ACCOUNTING_EVENTS
210 WHERE source_id = v_source_id
211 AND source_table = v_source_table;
212
213 l_dist_tbl TABV_TBL_TYPE;
214 l_aetv_rec OKL_ACCT_EVENT_PUB.AETV_REC_TYPE;
215 i NUMBER := 0;
216 l_api_version NUMBER := 1.0;
217 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
218 l_msg_count NUMBER := 0;
219 l_msg_data VARCHAR2(2000);
220 l_id NUMBER;
221 l_aet_id NUMBER;
222
223 BEGIN
224
225 x_return_status := OKL_API.G_RET_STS_SUCCESS;
226
227 IF (p_flag = 'DIST') THEN
228
229 OPEN dist_csr(p_source_id, p_source_table);
230 LOOP
231 FETCH dist_csr INTO l_id;
232 EXIT WHEN dist_csr%NOTFOUND;
233 i := i + 1;
234 l_dist_tbl(i).ID := l_id;
235 END LOOP;
236
237 CLOSE dist_csr;
238
239 IF (l_dist_tbl.COUNT > 0) THEN
240
241 -- Start of wraper code generated automatically by Debug code generator for Okl_Trns_Acc_Dstrs_Pub.delete_trns_acc_dstrs
242 IF(L_DEBUG_ENABLED='Y') THEN
243 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
244 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
245 END IF;
246 IF(IS_DEBUG_PROCEDURE_ON) THEN
247 BEGIN
248 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRMSCB.pls call Okl_Trns_Acc_Dstrs_Pub.delete_trns_acc_dstrs ');
249 END;
250 END IF;
251 Okl_Trns_Acc_Dstrs_Pub.delete_trns_acc_dstrs(p_api_version => l_api_version
252 ,p_init_msg_list => l_init_msg_list
253 ,x_return_status => x_return_status
254 ,x_msg_count => l_msg_count
255 ,x_msg_data => l_msg_data
256 ,p_tabv_tbl => l_dist_tbl);
257 IF(IS_DEBUG_PROCEDURE_ON) THEN
258 BEGIN
259 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRMSCB.pls call Okl_Trns_Acc_Dstrs_Pub.delete_trns_acc_dstrs ');
260 END;
261 END IF;
262 -- End of wraper code generated automatically by Debug code generator for Okl_Trns_Acc_Dstrs_Pub.delete_trns_acc_dstrs
263
264 END IF;
265
266
267 END IF;
268
269
270 IF (p_flag = 'AE') THEN
271
272 OPEN aet_csr(p_source_id, p_source_table);
273 FETCH aet_csr INTO l_aet_id;
274 CLOSE aet_csr;
275
276 l_aetv_rec.accounting_event_ID := l_aet_id;
277
278 -- Start of wraper code generated automatically by Debug code generator for Okl_Acct_Event_Pub.delete_acct_event
279 IF(IS_DEBUG_PROCEDURE_ON) THEN
280 BEGIN
281 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRMSCB.pls call Okl_Acct_Event_Pub.delete_acct_event ');
282 END;
283 END IF;
284 Okl_Acct_Event_Pub.delete_acct_event(p_api_version => l_api_version
285 ,p_init_msg_list => l_init_msg_list
286 ,x_return_status => x_return_status
287 ,x_msg_count => l_msg_count
288 ,x_msg_data => l_msg_data
289 ,p_aetv_rec => l_aetv_rec);
290 IF(IS_DEBUG_PROCEDURE_ON) THEN
291 BEGIN
292 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRMSCB.pls call Okl_Acct_Event_Pub.delete_acct_event ');
293 END;
294 END IF;
295 -- End of wraper code generated automatically by Debug code generator for Okl_Acct_Event_Pub.delete_acct_event
296
297
298 END IF;
299
300 END DELETE_DIST_AE;
301
302
303
304 -- Added by Santonyr to fix the bug 2557421 on 17th Jan, 2003
305
306 PROCEDURE validate_line_amount(p_tabv_tbl IN TABV_TBL_TYPE,
307 x_return_status OUT NOCOPY VARCHAR2)
308
309 IS
310
311 l_total_cr_amount NUMBER := 0;
312 l_total_dr_amount NUMBER := 0;
313 l_line_amount OKL_TXL_CNTRCT_LNS.AMOUNT%TYPE;
314 i NUMBER := 0;
315
316 CURSOR lineAmount_csr(v_source_id NUMBER) IS
317 SELECT amount
318 FROM OKL_TXL_CNTRCT_LNS
319 WHERE ID = v_source_id;
320
321 BEGIN
322
323 x_return_status := OKL_API.G_RET_STS_SUCCESS;
324
325 FOR i IN 1..p_tabv_tbl.COUNT
326 LOOP
327 IF (p_tabv_tbl(i).CR_DR_FLAG = 'C') THEN
328 l_total_cr_amount := l_total_cr_amount + NVL(p_tabv_tbl(i).AMOUNT, 0);
329 ELSE
330 l_total_dr_amount := l_total_dr_amount + NVL(p_tabv_tbl(i).AMOUNT, 0);
331 END IF;
332 END LOOP;
333
334
335 IF (NVL(l_total_cr_amount, 0) <> NVL(l_total_dr_amount, 0)) THEN
336 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
337 ,p_msg_name => 'OKL_AMT_DR_CR_UNEQUAL');
338 x_return_status := Okc_Api.G_RET_STS_ERROR;
339
340 END IF;
341
342
343 OPEN lineAmount_csr(p_tabv_tbl(1).source_id);
344 FETCH lineAmount_csr INTO
345 l_line_amount;
346 CLOSE lineAmount_csr;
347
348
349
350 IF ( NVL(l_line_amount, 0) <> NVL(l_total_cr_amount,0)) THEN
351 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
352 ,p_msg_name => 'OKL_TXL_AMT_NE_DSTR_AMT');
353 x_return_status := Okc_Api.G_RET_STS_ERROR;
354
355 END IF;
356
357
358 END VALIDATE_LINE_AMOUNT;
359
360
361
362 PROCEDURE insert_updt_dstrs(p_api_version IN NUMBER,
363 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2,
367 p_tabv_tbl IN tabv_tbl_type,
368 x_tabv_tbl OUT NOCOPY tabv_tbl_type)
369
370 IS
371
372 l_api_version NUMBER := 1.0;
373 l_api_name VARCHAR2(30) := 'INSERT_UPDT_DSTRS';
374 l_total_cr_amount NUMBER := 0;
375 l_total_acc_cr_amount NUMBER := 0;
376 l_total_dr_amount NUMBER := 0;
377 l_total_acc_dr_amount NUMBER := 0;
378 l_tabv_rec_out TABV_REC_TYPE;
379 l_tabv_rec TABV_REC_TYPE;
380 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
381 l_check_status NUMBER;
382 l_source_table OKL_TRNS_ACC_DSTRS.source_table%TYPE := 'OKL_TXL_CNTRCT_LNS';
383
384 -- Added by Santonyr to fix the bug 3089327 on 07th Aug, 2003
385
386 l_tcn_id OKL_TRX_CONTRACTS.ID%TYPE;
387 l_tsu_code OKL_TRX_CONTRACTS.TSU_CODE%TYPE;
388
389 CURSOR tcl_csr(v_source_id NUMBER) IS
390 SELECT tcn_id
391 FROM OKL_TXL_CNTRCT_LNS
392 WHERE ID = v_source_id;
393
394 CURSOR tcn_csr(v_tcn_id NUMBER) IS
395 SELECT tsu_code
396 FROM OKL_TRX_CONTRACTS
397 WHERE ID = v_tcn_id;
398
399
400 BEGIN
401
402 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
403 G_PKG_NAME,
404 p_init_msg_list,
405 l_api_version,
406 p_api_version,
407 '_PVT',
408 x_return_status);
409 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
410 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
411 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
412 RAISE OKL_API.G_EXCEPTION_ERROR;
413 END IF;
414
415 -- Added by Santonyr to fix the bug 3089327 on 07th Aug, 2003
416
417 OPEN tcl_csr(p_tabv_tbl(1).source_id);
418 FETCH tcl_csr INTO l_tcn_id;
419 CLOSE tcl_csr;
420
421 OPEN tcn_csr(l_tcn_id);
422 FETCH tcn_csr INTO l_tsu_code;
423 CLOSE tcn_csr;
424
425 IF (l_tsu_code = 'CANCELED') THEN
426
427 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
428 p_msg_name => 'OKL_TRX_CANCELED');
429
430 RAISE OKL_API.G_EXCEPTION_ERROR;
431
432 END IF;
433
434
435 -- Added by Santonyr to fix the bug 2557421 on 17th Jan, 2003
436
437 validate_line_amount(p_tabv_tbl => p_tabv_tbl,
438 x_return_status => l_return_status);
439
440 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
441 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
442 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
443 RAISE OKL_API.G_EXCEPTION_ERROR;
444 END IF;
445
446 --Validation to check if the transactions have been transferred to GL.If so they cannot be updated
447
448 l_check_status := CHECK_DIST(p_source_id => p_tabv_tbl(1).source_id,
449 p_source_table => l_source_table);
450
451 IF (l_check_status = 1) OR (l_check_status = 2) THEN
452 -- Delete from Distributions
453 DELETE_DIST_AE(p_flag => 'DIST',
454 p_source_id => p_tabv_tbl(1).source_id,
455 p_source_table => l_source_table,
456 x_return_status => l_return_status);
457
458
459
460 END IF;
461
462 IF (l_check_status = 2) THEN
463 -- delete from AE
464 DELETE_DIST_AE(p_flag => 'AE',
465 p_source_id => p_tabv_tbl(1).source_id,
466 p_source_table => l_source_table,
467 x_return_status => l_return_status);
468
469 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
470 RAISE OKL_API.G_EXCEPTION_ERROR;
471 END IF;
472
473 END IF;
474
475
476 IF (l_check_status = 3) THEN
477 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
478 p_msg_name => 'OKL_AE_GONE_TO_GL');
479 RAISE OKL_API.G_EXCEPTION_ERROR;
480 END IF;
481
482
483
484 FOR i IN 1..p_tabv_tbl.COUNT
485
486 LOOP
487 POPULATE_DSTR_REC(p_tabv_rec => p_tabv_tbl(i),
488 x_tabv_rec => l_tabv_rec_out,
489 p_mode => 'I');
490
491 OKL_TRNS_ACC_DSTRS_PUB.insert_trns_acc_dstrs(p_api_version => l_api_version,
492 p_init_msg_list => p_init_msg_list,
493 x_return_status => l_return_status,
494 x_msg_count => x_msg_count,
495 x_msg_data => x_msg_data,
496 p_tabv_rec => l_tabv_rec_out,
497 x_tabv_rec => x_tabv_tbl(i) );
498
499 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
500 RAISE OKL_API.G_EXCEPTION_ERROR;
501 END IF;
502
503 END LOOP;
504
505
506 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
507
508 x_return_status := l_return_status;
509
510 EXCEPTION
511 WHEN OKL_API.G_EXCEPTION_ERROR THEN
512 x_return_status := OKL_API.HANDLE_EXCEPTIONS
513 (
514 l_api_name,
515 G_PKG_NAME,
516 'OKL_API.G_RET_STS_ERROR',
517 x_msg_count,
518 x_msg_data,
519 '_PVT'
520 );
521 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
522 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
523 (
524 l_api_name,
525 G_PKG_NAME,
526 'OKL_API.G_RET_STS_UNEXP_ERROR',
527 x_msg_count,
528 x_msg_data,
529 '_PVT'
530 );
531 WHEN OTHERS THEN
532 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
533 (
534 l_api_name,
535 G_PKG_NAME,
536 'OTHERS',
537 x_msg_count,
538 x_msg_data,
539 '_PVT'
540 );
541
542 END insert_updt_dstrs;
543
544
545
546 END OKL_ACCOUNT_DIST_MISC_PVT;
547