[Home] [Help]
PACKAGE BODY: APPS.OKL_MULTIGAAP_ENGINE_PVT
Source
1 PACKAGE BODY OKL_MULTIGAAP_ENGINE_PVT AS
2 /* $Header: OKLRMGEB.pls 120.0.12010000.10 2009/01/15 23:10:56 smereddy noship $ */
3
4 G_PRIMARY CONSTANT VARCHAR2(200) := 'PRIMARY';
5 G_SECONDARY CONSTANT VARCHAR2(200) := 'SECONDARY';
6 G_SPLIT_ASSET CONSTANT VARCHAR2(200) := 'SPLIT_ASSET';
7 G_TERMINATION CONSTANT VARCHAR2(200) := 'TERMINATION';
8 G_EVERGREEN CONSTANT VARCHAR2(200) := 'EVERGREEN';
9 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
10
11 --Bug# 7698532
12 G_MODULE VARCHAR2(255) := 'LEASE.ACCOUNTING.MGENGINE';
13 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
14 G_IS_DEBUG_STATEMENT_ON BOOLEAN ;
15 --Bug# 7698532
16
17 -- Start of comments
18 --
19 -- Procedure Name : get_reporting_product
20 -- Description : This procedure checks if there is a reporting product attached to the contract and returns
21 -- the deal type of the reporting product and MG reporting book
22 -- Business Rules :
23 -- Parameters : p_contract_id - Contract ID
24 -- Version : 1.0
25 -- History : sechawla 12-Dec-07 - 6671849 Created
26 -- End of comments
27
28 PROCEDURE get_reporting_product(p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2,
33 p_contract_id IN NUMBER,
34 x_rep_product_id OUT NOCOPY VARCHAR2) IS
35
36 -- Get the financial product of the contract
37 CURSOR l_get_fin_product(cp_khr_id IN NUMBER) IS
38 SELECT a.start_date, a.contract_number, b.pdt_id
39 FROM okc_k_headers_b a, okl_k_headers b
40 WHERE a.id = b.id
41 AND a.id = cp_khr_id;
42
43 SUBTYPE pdtv_rec_type IS OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
44 SUBTYPE pdt_parameters_rec_type IS OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
45
46 l_fin_product_id NUMBER;
47 l_start_date DATE;
48 lp_pdtv_rec pdtv_rec_type;
49 lp_empty_pdtv_rec pdtv_rec_type;
50 lx_no_data_found BOOLEAN;
51 lx_pdt_parameter_rec pdt_parameters_rec_type ;
52 l_contract_number VARCHAR2(120);
53
54 --mg_error EXCEPTION;
55 l_reporting_product OKL_PRODUCTS_V.NAME%TYPE;
56 l_reporting_product_id NUMBER;
57
58 BEGIN
59 -- get the financial product of the contract
60 OPEN l_get_fin_product(p_contract_id);
61 FETCH l_get_fin_product INTO l_start_date, l_contract_number, l_fin_product_id;
62 CLOSE l_get_fin_product;
63
64 lp_pdtv_rec.id := l_fin_product_id;
65
66 -- check if the fin product has a reporting product
67 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters( p_api_version => p_api_version,
68 p_init_msg_list => OKL_API.G_FALSE,
69 x_return_status => x_return_status,
70 x_no_data_found => lx_no_data_found,
71 x_msg_count => x_msg_count,
72 x_msg_data => x_msg_data,
73 p_pdtv_rec => lp_pdtv_rec,
74 p_product_date => l_start_date,
75 p_pdt_parameter_rec => lx_pdt_parameter_rec);
76
77 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
78 -- Error getting financial product parameters for contract CONTRACT_NUMBER.
79 OKL_API.set_message( p_app_name => 'OKL',
80 p_msg_name => 'OKL_AM_FIN_PROD_PARAM_ERR',
81 p_token1 => 'CONTRACT_NUMBER',
82 p_token1_value => l_contract_number);
83
84 ELSE
85
86 l_reporting_product := lx_pdt_parameter_rec.reporting_product;
87 l_reporting_product_id := lx_pdt_parameter_rec.reporting_pdt_id;
88
89 IF l_reporting_product IS NOT NULL AND l_reporting_product <> OKL_API.G_MISS_CHAR THEN
90 -- Contract has a reporting product
91 x_rep_product_id := l_reporting_product_id;
92 END IF;
93 END IF;
94 EXCEPTION
95 --WHEN mg_error THEN
96 -- IF l_get_fin_product%ISOPEN THEN
97 -- CLOSE l_get_fin_product;
98 -- END IF;
99 -- x_return_status := OKL_API.G_RET_STS_ERROR;
100 WHEN OTHERS THEN
101 IF l_get_fin_product%ISOPEN THEN
102 CLOSE l_get_fin_product;
103 END IF;
104 OKL_API.set_message(p_app_name => 'OKC',
105 p_msg_name => g_unexpected_error,
106 p_token1 => g_sqlcode_token,
107 p_token1_value => sqlcode,
108 p_token2 => g_sqlerrm_token,
109 p_token2_value => sqlerrm);
110 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
111 END get_reporting_product;
112
113 ---------------------------------------------------------------------------
114 -- FUNCTION is_srm_automated
115 -- checks whether secondary representation method is AUTOMATED
116 ---------------------------------------------------------------------------
117 FUNCTION is_srm_automated RETURN BOOLEAN IS
118 l_is_srm_automated VARCHAR2(1) := 'N';
119
120 CURSOR is_srm_automated IS
121 SELECT 'Y'
122 FROM OKL_SYS_ACCT_OPTS
123 WHERE secondary_rep_method IN ('AUTOMATED');
124
125 BEGIN
126 --get batch id for conc requests from seq
127 OPEN is_srm_automated;
128 FETCH is_srm_automated INTO l_is_srm_automated;
129 CLOSE is_srm_automated;
130
131 IF(l_is_srm_automated = 'Y') THEN
132 RETURN true;
133 END IF;
134
135 RETURN false;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 RETURN false;
140 END is_srm_automated;
141
142 ---------------------------------------------------------------------------
143 -- FUNCTION is_mg_enabled
144 -- checks whether contract is multigaap enabled
145 ---------------------------------------------------------------------------
146 FUNCTION is_mg_enabled(p_khr_id NUMBER) RETURN BOOLEAN IS
147 l_is_mg_enabled VARCHAR2(1) := 'N';
148
149 CURSOR is_mg_enabled IS
150 SELECT NVL(KHR.MULTI_GAAP_YN,'N')
151 FROM OKL_K_HEADERS KHR
152 WHERE ID = p_khr_id;
153
154 BEGIN
155 IF (G_DEBUG_ENABLED = 'Y') THEN
156 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
157 END IF;
158
159 --get batch id for conc requests from seq
160 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'is_mg_enabled begin....');
161
162 OPEN is_mg_enabled;
163 FETCH is_mg_enabled INTO l_is_mg_enabled;
164 CLOSE is_mg_enabled;
165
166 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_is_mg_enabled'|| NVL(l_is_mg_enabled,'X'));
167 IF(l_is_mg_enabled = 'Y') THEN
168 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'inside if condition');
169 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'is_mg_enabled end');
170 RETURN true;
171 END IF;
172
173 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'is_mg_enabled end');
174
175 RETURN false;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'is_mg_enabled Exception block');
180 RETURN false;
181 END is_mg_enabled;
182
183 ---------------------------------------------------------------------------
184 -- FUNCTION get_secondary_representations
185 -- retunrns secondary representation code
186 ---------------------------------------------------------------------------
187 FUNCTION get_secondary_rep_code RETURN VARCHAR2 IS
188 l_representation_code okl_representations_v.representation_code%type := null;
189
190 CURSOR get_secondary_rep_code IS
191 select representation_code
192 from okl_representations_v
193 where representation_type = G_SECONDARY;
194 BEGIN
195
196 OPEN get_secondary_rep_code;
197 FETCH get_secondary_rep_code INTO l_representation_code;
198 CLOSE get_secondary_rep_code;
199
200 RETURN(l_representation_code);
201 EXCEPTION
202 WHEN OTHERS THEN
203 RETURN null;
204 END get_secondary_rep_code;
205
206 FUNCTION is_formula_based(p_id NUMBER) RETURN BOOLEAN IS
207 l_is_formula_based VARCHAR2(1) := 'N';
208
209 CURSOR is_formula_based IS
210 select nvl(typ.FORMULA_YN,'N')
211 from okl_trx_contracts khr,
212 okl_trx_types_b typ
213 where khr.try_id=typ.id
214 and khr.id = p_id;
215 BEGIN
216 -- checks whether transaction type is formala based
217 OPEN is_formula_based;
218 FETCH is_formula_based INTO l_is_formula_based;
219 CLOSE is_formula_based;
220
221 IF(l_is_formula_based = 'Y') THEN
222 RETURN true;
223 END IF;
224
225 RETURN false;
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 RETURN false;
230 END is_formula_based;
231
232 FUNCTION is_line_based(p_id NUMBER) RETURN BOOLEAN IS
233 l_trx_type_class okl_trx_types_b.trx_type_class%type := null;
234
235 CURSOR is_line_based IS
236 select trx_type_class
237 from okl_trx_contracts khr,
238 okl_trx_types_b typ
239 where khr.try_id=typ.id
240 and khr.id = p_id;
241 BEGIN
242 -- checks whether transaction type is contract based or line based.
243 -- transaction types Termination, Evergreen, and Split Asset are line based
244 OPEN is_line_based;
245 FETCH is_line_based INTO l_trx_type_class;
246 CLOSE is_line_based;
247
248 IF( (l_trx_type_class = G_SPLIT_ASSET) OR (l_trx_type_class = G_TERMINATION) OR (l_trx_type_class = G_EVERGREEN ))
249 THEN
250 RETURN true;
251 END IF;
252
253 RETURN false;
254 EXCEPTION
255 WHEN OTHERS THEN
256 RETURN false;
257 END is_line_based;
258
259 -- below function is used for formula
260 FUNCTION null_out_primary_trx_defaults (
261 p_tcnv_rec IN tcnv_rec_type
262 ) RETURN tcnv_rec_type IS
263 l_tcnv_rec tcnv_rec_type := p_tcnv_rec;
264 BEGIN
265
266 l_tcnv_rec.object_version_number := NULL;
267 l_tcnv_rec.pdt_id := NULL;
268 l_tcnv_rec.set_of_books_id := NULL;
269 l_tcnv_rec.REPRESENTATION_CODE := NULL;
270 l_tcnv_rec.REPRESENTATION_NAME := null;
271 l_tcnv_rec.BOOK_CLASSIFICATION_CODE := null;
272 l_tcnv_rec.TAX_OWNER_CODE := null;
273 l_tcnv_rec.PRODUCT_NAME := null;
274
275 RETURN(l_tcnv_rec);
276 END null_out_primary_trx_defaults;
277
278
279 PROCEDURE set_secondary_trx_attribs(
280 p_api_version IN NUMBER,
281 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
282 x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_count OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2,
285 p_tcnv_rec IN tcnv_rec_type,
286 x_tcnv_tbl OUT NOCOPY tcnv_tbl_type)
287 IS
288
289 l_api_version NUMBER := 1.0;
290 l_api_name CONSTANT VARCHAR2(30) := 'set_secondary_trx_attribs';
291 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
292 x_no_data_found BOOLEAN := TRUE;
293
294 CURSOR get_secondary_rep_code_csr IS
295 select representation_code,representation_name
296 from okl_representations_v
297 where representation_type = G_SECONDARY;
298
299 i NUMBER := 0;
300 l_tcnv_rec tcnv_rec_type := p_tcnv_rec;
301 l_tcnv_tbl tcnv_tbl_type ;
302
303 l_rep_product_id okl_product_parameters_v.id%type := null;
304 l_REPRESENTATION_CODE okl_representations_v.representation_code%type :=null;
305 l_REPRESENTATION_name okl_representations_v.representation_name%type :=null;
306 l_set_of_books_id OKL_SYS_ACCT_OPTS.set_of_books_id%type := null;
307 l_formula_yn boolean := false;
308
309 lp_pdtv_rec OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
310 lp_pdt_param_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
311 lx_pdtv_rec OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
312 lx_pdt_param_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
313
314 BEGIN
315
316 get_reporting_product( p_api_version => l_api_version,
317 p_init_msg_list => p_init_msg_list,
318 x_return_status => l_return_status,
319 x_msg_count => x_msg_count,
320 x_msg_data => x_msg_data,
321 p_contract_id => l_tcnv_rec.khr_id,
322 x_rep_product_id => l_rep_product_id);
323
324 IF (l_rep_product_id IS NULL) THEN
325 OKL_Api.SET_MESSAGE(p_app_name => 'OKC'
326 ,p_msg_name => g_required_value
327 ,p_token1 => g_col_name_token
328 ,p_token1_value => 'PDT_ID');
329 x_return_status := OKL_API.G_RET_STS_ERROR;
330 RAISE OKL_API.G_EXCEPTION_ERROR;
331 END IF;
332
333 lp_pdtv_rec.id := l_rep_product_id;
334 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters(
335 p_api_version => p_api_version,
336 p_init_msg_list => p_init_msg_list,
337 x_return_status => l_return_status,
338 x_no_data_found => x_no_data_found,
339 x_msg_count => x_msg_count,
340 x_msg_data => x_msg_data,
341 p_pdtv_rec => lp_pdtv_rec,
342 p_product_date => NULL,
343 p_pdt_parameter_rec => lx_pdt_param_rec);
344
345 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
346 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
347 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
348 RAISE OKL_API.G_EXCEPTION_ERROR;
349 END IF;
350
351
352 l_set_of_books_id := Okl_Accounting_util.get_set_of_books_id(G_SECONDARY);
353 IF (l_set_of_books_id IS NULL) THEN
354 OKL_Api.SET_MESSAGE(p_app_name => 'OKC'
355 ,p_msg_name => g_required_value
356 ,p_token1 => g_col_name_token
357 ,p_token1_value => 'SET_OF_BOOKS_ID');
358 x_return_status := OKL_API.G_RET_STS_ERROR;
359 RAISE OKL_API.G_EXCEPTION_ERROR;
360 END IF;
361
362 l_formula_yn := is_formula_based (l_tcnv_rec.id);
363 IF(l_formula_yn) THEN
364 l_tcnv_rec.amount := NULL;
365 END IF;
366
367 l_tcnv_rec.primary_rep_trx_id := l_tcnv_rec.id;
368 l_tcnv_rec.id := NULL;
369
370 FOR get_secondary_rep_code IN get_secondary_rep_code_csr LOOP
371 i := i + 1;
372 l_representation_code := null;
373 l_representation_name := null;
374 l_representation_code := get_secondary_rep_code.representation_code;
375 l_representation_name := get_secondary_rep_code.representation_name;
376 IF (l_representation_code IS NULL) THEN
377 OKL_Api.SET_MESSAGE(p_app_name => 'OKC'
378 ,p_msg_name => g_required_value
379 ,p_token1 => g_col_name_token
380 ,p_token1_value => 'REPRESENTATION_CODE');
381 x_return_status := OKL_API.G_RET_STS_ERROR;
382 RAISE OKL_API.G_EXCEPTION_ERROR;
383 END IF;
384
385 IF (l_representation_name IS NULL ) THEN
386 OKL_Api.SET_MESSAGE(p_app_name => 'OKC'
387 ,p_msg_name => g_required_value
388 ,p_token1 => g_col_name_token
389 ,p_token1_value => 'REPRESENTATION_NAME');
390 x_return_status := OKL_API.G_RET_STS_ERROR;
391 RAISE OKL_API.G_EXCEPTION_ERROR;
392 END IF;
393
394 l_tcnv_tbl(i) := l_tcnv_rec;
395 --l_tcnv_tbl(i).trx_number := null;
396 l_tcnv_tbl(i).TSU_CODE := 'ENTERED';
397 l_tcnv_tbl(i).pdt_id := l_rep_product_id;
398 l_tcnv_tbl(i).set_of_books_id := l_set_of_books_id;
399 l_tcnv_tbl(i).REPRESENTATION_CODE := l_representation_code;
400 l_tcnv_tbl(i).REPRESENTATION_NAME := l_representation_name;
401 l_tcnv_tbl(i).REPRESENTATION_TYPE := G_SECONDARY;
402 l_tcnv_tbl(i).BOOK_CLASSIFICATION_CODE := lx_pdt_param_rec.Deal_Type;
403 l_tcnv_tbl(i).TAX_OWNER_CODE := lx_pdt_param_rec.tax_owner;
404 l_tcnv_tbl(i).PRODUCT_NAME := lx_pdt_param_rec.name;
405
406 END LOOP;
407
408 x_tcnv_tbl := l_tcnv_tbl;
409 x_return_status := l_return_status;
410
411 EXCEPTION
412 WHEN OKL_API.G_EXCEPTION_ERROR THEN
413 x_return_status := OKL_API.HANDLE_EXCEPTIONS
414 (
415 l_api_name,
416 G_PKG_NAME,
417 'OKL_API.G_RET_STS_ERROR',
418 x_msg_count,
419 x_msg_data,
420 '_PVT'
421 );
422 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
423 x_return_status := OKL_API.HANDLE_EXCEPTIONS
424 (
425 l_api_name,
426 G_PKG_NAME,
427 'OKL_API.G_RET_STS_UNEXP_ERROR',
428 x_msg_count,
429 x_msg_data,
430 '_PVT'
431 );
432 WHEN OTHERS THEN
433 x_return_status := OKL_API.HANDLE_EXCEPTIONS
434 (
435 l_api_name,
436 G_PKG_NAME,
437 'OTHERS',
438 x_msg_count,
439 x_msg_data,
440 '_PVT'
441 );
442
443
444 END set_secondary_trx_attribs;
445
446 PROCEDURE CREATE_SEC_REP_TRX (
447 P_API_VERSION IN NUMBER,
448 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
449 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
450 X_MSG_COUNT OUT NOCOPY NUMBER,
451 X_MSG_DATA OUT NOCOPY VARCHAR2,
452 P_TCNV_REC OKL_TCN_PVT.TCNV_REC_TYPE,
453 P_TCLV_TBL OKL_TCL_PVT.TCLV_TBL_TYPE,
454 p_ctxt_val_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE,
455 p_acc_gen_primary_key_tbl OKL_ACCOUNT_DIST_PVT.acc_gen_primary_key --SGIYER
456 ) IS
457
458 l_api_version NUMBER := 1.0;
459 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SEC_REP_TRX';
460 l_row_notfound BOOLEAN := TRUE;
461 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
462
463 l_try_id NUMBER := 0;
464
465 l_pri_trx_tcnv_rec tcnv_rec_type := p_tcnv_rec;
466 l_pri_trx_id NUMBER := p_tcnv_rec.id;
467
468 l_sec_trx_tcnv_tbl tcnv_tbl_type;
469 x_sec_trx_tcnv_tbl tcnv_tbl_type;
470
471 l_pri_trx_tclv_rec tclv_rec_type := p_tclv_tbl(1);
472
473 l_sec_trx_tclv_tbl tclv_tbl_type; --SGIYER := p_tclv_tbl;
474 x_sec_trx_tclv_tbl tclv_tbl_type ;
475
476 CURSOR prdt_csr (l_pdt_id OKL_PRODUCTS.ID%TYPE) IS
477 SELECT name
478 FROM okl_products
479 WHERE id = l_pdt_id ;
480
481 CURSOR trx_type_csr (l_trx_type_id OKL_TRX_TYPES_TL.ID%TYPE) IS
482 SELECT name
483 FROM okl_trx_types_tl
484 WHERE id = l_trx_type_id ;
485
486 CURSOR sty_type_csr (l_sty_type_id OKL_STRM_TYPE_TL.ID%TYPE) IS
487 SELECT name
488 FROM okl_strm_type_tl
489 WHERE id = l_sty_type_id ;
490
491 CURSOR get_kle_ids_csr (l_id NUMBER) IS
492 select distinct lns.kle_id
493 from okl_trx_contracts khr,
494 OKL_TXL_CNTRCT_LNS lns
495 where khr.id = l_id
496 and khr.id = lns.tcn_id;
497
498 l_formula_yn boolean := false;
499 l_is_line_based boolean := false;
500 l_trx_type_name okl_trx_types_b.trx_type_class%type := null;
501 i NUMBER := 0;
502 k NUMBER := 0;
503 j NUMBER := 0;
504 m NUMBER := 0;
505
506 l_valid_gl_date date;
507
508 -- get template infofor acc dist
509 l_tmpl_identify_rec Okl_Account_Dist_Pvt.tmpl_identify_rec_type;
510 l_template_tbl Okl_Account_Dist_Pvt.AVLV_TBL_TYPE;
511
512 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.tmpl_identify_tbl_type;
513 l_dist_info_tbl Okl_Account_Dist_Pvt.dist_info_tbl_TYPE;
514 l_ctxt_val_tbl OKL_ACCOUNT_DIST_PVT.CTXT_VAL_TBL_TYPE;
515 l_acc_gen_tbl Okl_Account_Dist_Pvt.acc_gen_tbl_type; --SGIYER := p_acc_gen_primary_key_tbl;
516 x_template_tbl Okl_Account_Dist_Pvt.AVLV_OUT_TBL_TYPE;
517 x_amount_out_tbl Okl_Account_Dist_Pvt.AMOUNT_OUT_TBL_TYPE;
518
519 l_acc_gen_primary_key_tbl OKL_ACCOUNT_DIST_PVT.acc_gen_primary_key := p_acc_gen_primary_key_tbl; --SGIYER
520 l_amount_tbl Okl_Account_Dist_Pvt.AMOUNT_TBL_TYPE;
521 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
522
523 CURSOR l_contract_currency_csr IS
524 SELECT currency_code,
525 currency_conversion_rate,
526 currency_conversion_type,
527 currency_conversion_date
528 FROM okl_k_headers_full_v
529 WHERE id = l_pri_trx_tclv_rec.khr_id ;
530
531 l_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%TYPE := null;
532 l_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%TYPE := null;
533 l_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%TYPE := null;
534 l_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE := null;
535
536 l_sty_id number := null;
537 l_contract_start_date date :=null;
538
539 CURSOR cntrct_csr (p_khr_id NUMBER)IS
540 SELECT start_date
541 FROM okl_k_headers_full_v
542 WHERE id = p_khr_id;
543
544 CURSOR get_sty_id_csr
545 (p_pdt_id number,
546 p_strm_purpose okl_strm_type_v.stream_type_purpose%type,
547 p_contract_start_date date) IS
548 SELECT PRIMARY_STY_ID
549 FROM OKL_STRM_TMPT_LINES_UV STL
550 WHERE STL.PRIMARY_YN = 'Y'
551 AND STL.PDT_ID = p_pdt_id
552 AND (STL.START_DATE <= p_contract_start_date)
553 AND (STL.END_DATE >= p_contract_start_date OR STL.END_DATE IS NULL)
554 AND PRIMARY_STY_PURPOSE = p_strm_purpose;
555
556
557 BEGIN
558
559 IF (G_DEBUG_ENABLED = 'Y') THEN
560 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
561 END IF;
562
563 x_return_status := OKL_API.START_ACTIVITY(
564 p_api_name => l_api_name,
565 p_pkg_name => g_pkg_name,
566 p_init_msg_list => p_init_msg_list,
567 l_api_version => l_api_version,
568 p_api_version => p_api_version,
569 p_api_type => G_API_TYPE,
570 x_return_status => x_return_status);
571
572 -- check if activity started successfully
573 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
574 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
575 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
576 raise OKL_API.G_EXCEPTION_ERROR;
577 End If;
578
579
580 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
581 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to is_srm_automated');
582 END IF;
583
584 IF NOT (is_srm_automated()) THEN
585 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Secondary representation method is not automated, returning...');
586 RETURN;
587 END IF;
588
589 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
590 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_srm_automated');
591 END IF;
592
593 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
594 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to is_mg_enabled:'||to_char(l_pri_trx_tcnv_rec.khr_id));
595 END IF;
596
597 IF NOT (is_mg_enabled(l_pri_trx_tcnv_rec.khr_id)) THEN
598 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Multi gaap is not enabled, returning...');
599 RETURN;
600 END IF;
601
602 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
603 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_mg_enabled');
604 END IF;
605
606
607 -- null out the primary trx attributes which are not applicable to secondary trx
608 l_pri_trx_tcnv_rec := null_out_primary_trx_defaults (l_pri_trx_tcnv_rec);
609
610
611 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
612 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to set_secondary_trx_attribs');
613 END IF;
614
615
616 -- set secondary transaction attributes
617 set_secondary_trx_attribs( p_api_version => l_api_version,
618 p_init_msg_list => p_init_msg_list,
619 x_return_status => x_return_status,
620 x_msg_count => x_msg_count,
621 x_msg_data => x_msg_data,
622 p_tcnv_rec => l_pri_trx_tcnv_rec,
623 x_tcnv_tbl => l_sec_trx_tcnv_tbl);
624
625 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
626 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to set_secondary_trx_attribs, the return status is :'||x_return_status);
627 END IF;
628
629 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
630 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
631 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
632 RAISE OKL_API.G_EXCEPTION_ERROR;
633 END IF;
634
635
636 -- create the secondary transaction header
637 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
638 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.create_trx_contracts');
639 END IF;
640
641
642 OKL_TRX_CONTRACTS_PUB.create_trx_contracts(p_api_version => l_api_version,
643 p_init_msg_list => p_init_msg_list,
644 x_return_status => x_return_status,
645 x_msg_count => x_msg_count,
646 x_msg_data => x_msg_data,
647 p_tcnv_rec => l_sec_trx_tcnv_tbl(1),
648 x_tcnv_rec => x_sec_trx_tcnv_tbl(1));
649
650 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
651 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_TRX_CONTRACTS_PUB.create_trx_contracts, the return status is :'||x_return_status);
652 END IF;
653
654 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
655 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
656 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
657 RAISE OKL_API.G_EXCEPTION_ERROR;
658 END IF;
659
660 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
661 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to okl_accounting_util.get_valid_gl_date');
662 END IF;
663
664 -- SGIYER.12/08/08.Get valid GL date for template info and AE
665 l_valid_gl_date := okl_accounting_util.get_valid_gl_date
666 (p_gl_date => l_sec_trx_tcnv_tbl(1).date_transaction_occurred,
667 p_ledger_id => l_sec_trx_tcnv_tbl(1).set_of_books_id);
668
669 IF (l_valid_gl_date is null) THEN
670 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
671 p_msg_name => 'OKL_INVALID_GL_DATE');
672 RAISE OKL_API.G_EXCEPTION_ERROR;
673 END IF;
674
675 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
676 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_TRX_CONTRACTS_PUB.create_trx_contracts'||to_char(l_valid_gl_date));
677 END IF;
678
679 -- FOR m IN 1.. x_sec_trx_tcnv_tbl.COUNT -- this for loop is to handle multiple secondary trxs
680 -- LOOP
681 -- is_formula_based
682 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
683 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to is_formula_based');
684 END IF;
685
686 l_formula_yn := is_formula_based (l_pri_trx_id);
687
688 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
689 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_formula_based');
690 END IF;
691
692 IF(l_formula_yn) THEN -- get the amount from acc engine and update the secondary trx hdr and lines
693
694 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
695 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_formula_based TRUE');
696 END IF;
697
698 l_tmpl_identify_rec.product_id := x_sec_trx_tcnv_tbl(1).pdt_id;
699 l_tmpl_identify_rec.transaction_type_id := x_sec_trx_tcnv_tbl(1).try_id;
700 l_tmpl_identify_rec.memo_yn := 'N';
701 l_tmpl_identify_rec.prior_year_yn := 'N';
702
703 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
704 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_ACCOUNT_DIST_PVT.GET_TEMPLATE_INFO');
705 END IF;
706
707 OKL_ACCOUNT_DIST_PVT.GET_TEMPLATE_INFO(p_api_version => l_api_version,
708 p_init_msg_list => p_init_msg_list,
709 x_return_status => l_return_status,
710 x_msg_count => x_msg_count,
711 x_msg_data => x_msg_data,
712 p_tmpl_identify_rec => l_tmpl_identify_rec,
713 x_template_tbl => l_template_tbl,
714 p_validity_date => l_valid_gl_date); -- SGIYER-12/08/08.
715
716 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
717 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_ACCOUNT_DIST_PVT.GET_TEMPLATE_INFO, the return status is :'||l_return_status);
718 END IF;
719 -- Raise an error if template is not found.
720
721 IF (l_template_tbl.COUNT = 0) THEN
722 FOR trx_type_rec IN trx_type_csr (l_tmpl_identify_rec.transaction_type_id) LOOP
723 l_trx_type_name := trx_type_rec.name;
724 END LOOP;
725 Okl_Api.set_message(p_app_name => g_app_name,
726 p_msg_name => 'OKL_LA_NO_ACCOUNTING_TMPLTS',
727 p_token1 => 'TRANSACTION_TYPE',
728 p_token1_value => l_trx_type_name);
729 RAISE OKL_API.G_EXCEPTION_ERROR;
730 END IF;
731
732 -- chekc if the transaction line or contract based
733 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
734 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to is_line_based');
735 END IF;
736 l_is_line_based := is_line_based(l_pri_trx_id);
737 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
738 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_line_based');
739 END IF;
740
741 IF( l_is_line_based ) THEN -- that is trans type termination, split, evergreen
742
743 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
744 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_line_based TRUE');
745 END IF;
746 -- read all the distinct kle ids of primary trx lines and create kle ids * acc tmpl
747 FOR get_kle_ids_rec IN get_kle_ids_csr( l_pri_trx_id ) LOOP
748
749 -- read all the accounting template lines and assign to secondary trx lines
750 FOR k IN l_template_tbl.first..l_template_tbl.LAST LOOP
751
752 -- bug 7625968. SGIYER - 12/09/2008
753 -- Changed variable from 'k' to 'j' in the tbl l_sec_trx_tclv_tbl
754 j := j + 1;
755 l_sec_trx_tclv_tbl(j).khr_id := l_pri_trx_tcnv_rec.khr_id;
756 l_sec_trx_tclv_tbl(j).tcl_type := l_pri_trx_tclv_rec.tcl_type;
757 l_sec_trx_tclv_tbl(j).kle_id := get_kle_ids_rec.kle_id;
758 l_sec_trx_tclv_tbl(j).tcn_id := x_sec_trx_tcnv_tbl(1).id;
759 l_sec_trx_tclv_tbl(j).id := null;
760 l_sec_trx_tclv_tbl(j).line_number := j;
761 l_sec_trx_tclv_tbl(j).OBJECT_VERSION_NUMBER := null;
762 l_sec_trx_tclv_tbl(j).amount := null;
763 l_sec_trx_tclv_tbl(j).STY_ID := l_template_tbl(k).sty_id;
764 l_sec_trx_tclv_tbl(j).currency_code := x_sec_trx_tcnv_tbl(1).currency_code;
765
766 --Bug 7625968. brough the below inside the loop.
767 l_tmpl_identify_tbl(j).product_id := l_tmpl_identify_rec.product_id;
768 l_tmpl_identify_tbl(j).transaction_type_id := l_tmpl_identify_rec.transaction_type_id;
769 l_tmpl_identify_tbl(j).memo_yn := l_tmpl_identify_rec.memo_yn;
770 l_tmpl_identify_tbl(j).prior_year_yn := l_tmpl_identify_rec.prior_year_yn;
771 l_tmpl_identify_tbl(j).stream_type_id := l_template_tbl(k).sty_id;
772 l_tmpl_identify_tbl(j).advance_arrears := l_template_tbl(k).advance_arrears;
773 l_tmpl_identify_tbl(j).factoring_synd_flag := l_template_tbl(k).factoring_synd_flag;
774 l_tmpl_identify_tbl(j).investor_code := l_template_tbl(k).inv_code;
775 l_tmpl_identify_tbl(j).syndication_code := l_template_tbl(k).syt_code;
776 l_tmpl_identify_tbl(j).factoring_code := l_template_tbl(k).fac_code;
777
778 END LOOP;
779
780 END LOOP;
781
782 ELSE -- for all the other trx types
783 j := 0;
784 -- read all the accounting template lines and assign to secondary trx lines
785 -- we do not need
786 FOR k IN l_template_tbl.FIRST..l_template_tbl.LAST LOOP
787 j := j + 1;
788 l_sec_trx_tclv_tbl(j) := l_pri_trx_tclv_rec;
789 l_sec_trx_tclv_tbl(j).id := null;
790 l_sec_trx_tclv_tbl(j).line_number := k;
791 l_sec_trx_tclv_tbl(j).OBJECT_VERSION_NUMBER := null;
792 l_sec_trx_tclv_tbl(j).amount := null;
793 l_sec_trx_tclv_tbl(j).sty_id := l_template_tbl(k).sty_id;
794 l_sec_trx_tclv_tbl(j).tcn_id := x_sec_trx_tcnv_tbl(1).id;
795 l_sec_trx_tclv_tbl(j).currency_code := x_sec_trx_tcnv_tbl(1).currency_code;
796
797 --Bug 7625968. brough the below inside the loop.
798 l_tmpl_identify_tbl(j).product_id := l_tmpl_identify_rec.product_id;
799 l_tmpl_identify_tbl(j).transaction_type_id := l_tmpl_identify_rec.transaction_type_id;
800 l_tmpl_identify_tbl(j).memo_yn := l_tmpl_identify_rec.memo_yn;
801 l_tmpl_identify_tbl(j).prior_year_yn := l_tmpl_identify_rec.prior_year_yn;
802 l_tmpl_identify_tbl(j).stream_type_id := l_template_tbl(k).sty_id;
803 l_tmpl_identify_tbl(j).advance_arrears := l_template_tbl(k).advance_arrears;
804 l_tmpl_identify_tbl(j).factoring_synd_flag := l_template_tbl(k).factoring_synd_flag;
805 l_tmpl_identify_tbl(j).investor_code := l_template_tbl(k).inv_code;
806 l_tmpl_identify_tbl(j).syndication_code := l_template_tbl(k).syt_code;
807 l_tmpl_identify_tbl(j).factoring_code := l_template_tbl(k).fac_code;
808
809 END LOOP;
810
811 END IF;
812
813 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
814 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines');
815 END IF;
816
817 -- create the secondary transaction lines for each acc tmpl
818 OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines
819 (p_api_version => l_api_version,
820 p_init_msg_list => p_init_msg_list,
821 x_return_status => l_return_status,
822 x_msg_count => x_msg_count,
823 x_msg_data => x_msg_data,
824 p_tclv_tbl => l_sec_trx_tclv_tbl,
825 x_tclv_tbl => x_sec_trx_tclv_tbl);
826
827 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
828 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
829 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
830 RAISE OKL_API.G_EXCEPTION_ERROR;
831 END IF;
832
833 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
834 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines :'||l_return_status);
835 END IF;
836
837 -- accounting distributiions
838
839 OPEN l_contract_currency_csr;
840 FETCH l_contract_currency_csr INTO l_curr_code, l_currency_conversion_rate,l_currency_conversion_type, l_currency_conversion_date ;
841 CLOSE l_contract_currency_csr;
842
843 IF( l_curr_code IS NULL ) THEN
844 OKL_API.set_message( p_app_name => 'OKL',
845 p_msg_name => G_REQUIRED_VALUE,
846 p_token1 => g_col_name_token,
847 p_token1_value => 'CURRENCY_CODE');
848 RAISE OKL_API.G_EXCEPTION_ERROR;
849 END IF;
850
851 FOR i IN x_sec_trx_tclv_tbl.FIRST..x_sec_trx_tclv_tbl.LAST LOOP
852
853 l_dist_info_tbl(i).SOURCE_ID := x_sec_trx_tclv_tbl(i).ID;
854 l_dist_info_tbl(i).SOURCE_TABLE := 'OKL_TXL_CNTRCT_LNS';
855 l_dist_info_tbl(i).GL_REVERSAL_FLAG := 'N';
856 l_dist_info_tbl(i).POST_TO_GL := 'Y';
857 l_dist_info_tbl(i).CONTRACT_ID := l_pri_trx_tcnv_rec.KHR_ID;
858 l_dist_info_tbl(i).CONTRACT_LINE_ID := x_sec_trx_tclv_tbl(i).KLE_ID; -- Bug 7626121
859 l_dist_info_tbl(i).CURRENCY_CONVERSION_RATE := l_currency_conversion_rate;
860 l_dist_info_tbl(i).CURRENCY_CONVERSION_TYPE := l_currency_conversion_type;
861 l_dist_info_tbl(i).CURRENCY_CONVERSION_DATE := l_currency_conversion_date;
862 l_dist_info_tbl(i).CURRENCY_CODE := l_curr_code;
863 l_dist_info_tbl(i).ACCOUNTING_DATE := l_valid_gl_date;
864 l_dist_info_tbl(i).amount := x_sec_trx_tclv_tbl(i).amount;
865
866 --Assigning the account generator table.SGIYER
867 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
868 l_acc_gen_tbl(i).source_id := x_sec_trx_tclv_tbl(i).ID;
869
870 IF (l_ctxt_val_tbl.COUNT > 0) THEN
871 l_ctxt_tbl(i).source_id := x_sec_trx_tclv_tbl(i).id;
872 END IF;
873
874 END LOOP;
875
876 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
877 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST');
878 END IF;
879
880 OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST(
881 p_api_version => l_api_version,
882 p_init_msg_list => p_init_msg_list,
883 x_return_status => l_return_status,
884 x_msg_count => x_msg_count,
885 x_msg_data => x_msg_data,
886 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
887 p_dist_info_tbl => l_dist_info_tbl,
888 p_ctxt_val_tbl => l_ctxt_tbl,
889 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
890 x_template_tbl => x_template_tbl,
891 x_amount_tbl => x_amount_out_tbl,
892 p_trx_header_id => x_sec_trx_tcnv_tbl(1).id);
893
894 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
895 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST :'||l_return_status);
896 END IF;
897
898 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
899 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
900 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
901 RAISE OKL_API.G_EXCEPTION_ERROR;
902 END IF;
903
904 If x_sec_trx_tclv_tbl.COUNT > 0 then
905 FOR i in x_sec_trx_tclv_tbl.FIRST..x_sec_trx_tclv_tbl.LAST LOOP
906 l_amount_tbl.delete;
907 If x_amount_out_tbl.COUNT > 0 then
908 For k in x_amount_out_tbl.FIRST..x_amount_out_tbl.LAST LOOP
909 IF x_sec_trx_tclv_tbl(i).id = x_amount_out_tbl(k).source_id THEN
910 l_amount_tbl := x_amount_out_tbl(k).amount_tbl;
911 x_sec_trx_tclv_tbl(i).currency_code := l_curr_code;
912 IF l_amount_tbl.COUNT > 0 THEN
913 FOR j in l_amount_tbl.FIRST..l_amount_tbl.LAST LOOP
914 x_sec_trx_tclv_tbl(i).amount := nvl(x_sec_trx_tclv_tbl(i).amount,0) + l_amount_tbl(j);
915 END LOOP; -- for j in
916 END IF;-- If l_amount_tbl.COUNT
917 END IF; ---- IF x_sec_trx_tclv_tbl(i).id
918 END LOOP; -- For k in
919 END IF; -- If l_amount_out_tbl.COUNT
920 x_sec_trx_tcnv_tbl(1).amount := nvl(x_sec_trx_tcnv_tbl(1).amount,0) + x_sec_trx_tclv_tbl(i).amount;
921 -- l_tcnv_rec.currency_code := l_currency_code;
922 -- l_tcnv_rec.tsu_code := 'PROCESSED';
923 END LOOP; -- For i in
924 End If; -- If l_tclv_tbl.COUNT
925
926 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
927 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.update_trx_cntrct_lines');
928 END IF;
929 --Update the lines with the amount
930 OKL_TRX_CONTRACTS_PUB.update_trx_cntrct_lines
931 (p_api_version => l_api_version,
932 p_init_msg_list => p_init_msg_list,
933 x_return_status => x_return_status,
934 x_msg_count => x_msg_count,
935 x_msg_data => x_msg_data,
936 p_tclv_tbl => x_sec_trx_tclv_tbl,
937 x_tclv_tbl => l_sec_trx_tclv_tbl);
938
939 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
940 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_TRX_CONTRACTS_PUB.update_trx_cntrct_lines :'||x_return_status);
941 END IF;
942
943 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
944 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
945 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
946 RAISE OKL_API.G_EXCEPTION_ERROR;
947 END IF;
948
949 --Update the header with the amount
950 x_sec_trx_tcnv_tbl(1).tsu_code := 'PROCESSED';
951 x_sec_trx_tcnv_tbl(1).amount := x_sec_trx_tcnv_tbl(1).amount;
952
953 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
954 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.update_trx_contracts');
955 END IF;
956
957 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
958 p_init_msg_list => p_init_msg_list,
959 x_return_status => x_return_status,
960 x_msg_count => x_msg_count,
961 x_msg_data => x_msg_data,
962 p_tcnv_rec => x_sec_trx_tcnv_tbl(1),
963 x_tcnv_rec => x_sec_trx_tcnv_tbl(1));
964
965
966 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
967 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_TRX_CONTRACTS_PUB.update_trx_contracts :'||x_return_status);
968 END IF;
969
970 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
971 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
972 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
973 RAISE OKL_API.G_EXCEPTION_ERROR;
974 END IF;
975
976 ELSE
977
978 -- if not formula then do not null out the amt and
979 -- copy the lines as is from the primary trx hdr onto secondary trx lines
980 -- first get sty id, get the purpose code of the primary, then query
981
982 l_sec_trx_tclv_tbl := p_tclv_tbl;
983
984 FOR i IN l_sec_trx_tclv_tbl.FIRST..l_sec_trx_tclv_tbl.LAST LOOP
985
986 l_sec_trx_tclv_tbl(i).id := null;
987 l_sec_trx_tclv_tbl(i).tcn_id := x_sec_trx_tcnv_tbl(1).id;
988 l_sec_trx_tclv_tbl(i).object_version_number := null;
989
990
991 l_sty_id := null;
992 l_contract_start_date := null;
993
994 OPEN cntrct_csr (l_sec_trx_tcnv_tbl(1).khr_id);
995 FETCH cntrct_csr INTO l_contract_start_date;
996 CLOSE cntrct_csr;
997
998 IF l_contract_start_date IS NULL THEN
999 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1000 p_msg_name => g_required_value,
1001 p_token1 => g_col_name_token,
1002 p_token1_value => 'CONTRACT_START_DATE');
1003
1004 RAISE OKL_API.G_EXCEPTION_ERROR;
1005 END IF;
1006
1007 open get_sty_id_csr(l_sec_trx_tcnv_tbl(1).pdt_id, l_sec_trx_tclv_tbl(i).stream_type_purpose, l_contract_start_date);
1008 fetch get_sty_id_csr into l_sty_id;
1009 close get_sty_id_csr;
1010
1011 IF( l_sty_id IS NULL ) THEN
1012 Okl_Api.set_message(p_app_name => g_app_name,
1013 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1014 p_token1 => g_stream_name_token,
1015 p_token1_value => 'SPECIFIC LOSS PROVISION');
1016 RAISE OKL_API.G_EXCEPTION_ERROR;
1017 END IF;
1018 l_sec_trx_tclv_tbl(i).sty_id := l_sty_id;
1019
1020 END LOOP;
1021
1022 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1023 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines');
1024 END IF;
1025
1026 -- create the secondary transaction lines for each acc tmpl
1027 OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines
1028 (p_api_version => l_api_version,
1029 p_init_msg_list => p_init_msg_list,
1030 x_return_status => x_return_status,
1031 x_msg_count => x_msg_count,
1032 x_msg_data => x_msg_data,
1033 p_tclv_tbl => l_sec_trx_tclv_tbl,
1034 x_tclv_tbl => x_sec_trx_tclv_tbl);
1035
1036 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1037 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_TRX_CONTRACTS_PUB.update_trx_cntrct_lines :'||x_return_status);
1038 END IF;
1039
1040 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1041 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1042 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1043 RAISE OKL_API.G_EXCEPTION_ERROR;
1044 END IF;
1045
1046 -- make the call to accounting distributions
1047 OPEN l_contract_currency_csr;
1048 FETCH l_contract_currency_csr INTO l_curr_code, l_currency_conversion_rate,l_currency_conversion_type, l_currency_conversion_date ;
1049 CLOSE l_contract_currency_csr;
1050
1051 IF( l_curr_code IS NULL ) THEN
1052 OKL_API.set_message( p_app_name => 'OKL',
1053 p_msg_name => G_REQUIRED_VALUE,
1054 p_token1 => g_col_name_token,
1055 p_token1_value => 'CURRENCY_CODE');
1056 RAISE OKL_API.G_EXCEPTION_ERROR;
1057 END IF;
1058
1059
1060 FOR i IN x_sec_trx_tclv_tbl.FIRST..x_sec_trx_tclv_tbl.LAST LOOP
1061
1062 l_tmpl_identify_tbl(i).product_id := x_sec_trx_tcnv_tbl(1).pdt_id;
1063 l_tmpl_identify_tbl(i).stream_type_id := x_sec_trx_tclv_tbl(i).sty_id;
1064 l_tmpl_identify_tbl(i).transaction_type_id := x_sec_trx_tcnv_tbl(1).try_id;
1065 l_tmpl_identify_tbl(i).advance_arrears := NULL;
1066 l_tmpl_identify_tbl(i).prior_year_yn := NULL;
1067 l_tmpl_identify_tbl(i).memo_yn := NULL;
1068 l_tmpl_identify_tbl(i).investor_code := NULL;
1069 l_tmpl_identify_tbl(i).SYNDICATION_CODE := NULL;
1070 l_tmpl_identify_tbl(i).FACTORING_CODE := NULL;
1071 l_tmpl_identify_tbl(i).rev_rec_flag := NULL;
1072 l_tmpl_identify_tbl(i).factoring_synd_flag := NULL;
1073
1074 l_dist_info_tbl(i).SOURCE_ID := x_sec_trx_tclv_tbl(i).ID;
1075 l_dist_info_tbl(i).SOURCE_TABLE := 'OKL_TXL_CNTRCT_LNS';
1076 l_dist_info_tbl(i).GL_REVERSAL_FLAG := 'N';
1077 l_dist_info_tbl(i).POST_TO_GL := 'Y';
1078 l_dist_info_tbl(i).CONTRACT_ID := l_pri_trx_tcnv_rec.KHR_ID;
1079 l_dist_info_tbl(i).CONTRACT_LINE_ID := x_sec_trx_tclv_tbl(i).KLE_ID; -- Bug 7626121
1080 l_dist_info_tbl(i).CURRENCY_CONVERSION_RATE := l_currency_conversion_rate;
1081 l_dist_info_tbl(i).CURRENCY_CONVERSION_TYPE := l_currency_conversion_type;
1082 l_dist_info_tbl(i).CURRENCY_CONVERSION_DATE := l_currency_conversion_date;
1083 l_dist_info_tbl(i).CURRENCY_CODE := l_curr_code;
1084 l_dist_info_tbl(i).ACCOUNTING_DATE := x_sec_trx_tcnv_tbl(1).date_transaction_occurred;
1085 l_dist_info_tbl(i).amount := x_sec_trx_tclv_tbl(i).amount;
1086
1087 --Assigning the account generator table.SGIYER
1088 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
1089 l_acc_gen_tbl(i).source_id := x_sec_trx_tclv_tbl(i).ID;
1090
1091
1092 END LOOP;
1093
1094 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1095 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST');
1096 END IF;
1097
1098 OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST(
1099 p_api_version => l_api_version,
1100 p_init_msg_list => p_init_msg_list,
1101 x_return_status => l_return_status,
1102 x_msg_count => x_msg_count,
1103 x_msg_data => x_msg_data,
1104 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
1105 p_dist_info_tbl => l_dist_info_tbl,
1106 p_ctxt_val_tbl => l_ctxt_tbl,
1107 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
1108 x_template_tbl => x_template_tbl,
1109 x_amount_tbl => x_amount_out_tbl,
1110 p_trx_header_id => x_sec_trx_tcnv_tbl(1).id);
1111
1112 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1113 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST :'||l_return_status);
1114 END IF;
1115
1116 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1117 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1118 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1119 RAISE OKL_API.G_EXCEPTION_ERROR;
1120 END IF;
1121
1122 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1123 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.update_trx_contracts');
1124 END IF;
1125
1126 --Update the header with the amount
1127 x_sec_trx_tcnv_tbl(1).tsu_code := 'PROCESSED';
1128 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
1129 p_init_msg_list => p_init_msg_list,
1130 x_return_status => x_return_status,
1131 x_msg_count => x_msg_count,
1132 x_msg_data => x_msg_data,
1133 p_tcnv_rec => x_sec_trx_tcnv_tbl(1),
1134 x_tcnv_rec => x_sec_trx_tcnv_tbl(1));
1135
1136
1137 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1138 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_TRX_CONTRACTS_PUB.update_trx_contracts :'||x_return_status);
1139 END IF;
1140
1141 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1142 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1143 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1144 RAISE OKL_API.G_EXCEPTION_ERROR;
1145 END IF;
1146
1147 END IF;
1148 x_return_status := okl_api.g_ret_sts_success;
1149 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1150
1151 EXCEPTION
1152 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1153 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1154 (
1155 l_api_name,
1156 G_PKG_NAME,
1157 'OKL_API.G_RET_STS_ERROR',
1158 x_msg_count,
1159 x_msg_data,
1160 '_PVT'
1161 );
1162 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1163 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1164 (
1165 l_api_name,
1166 G_PKG_NAME,
1167 'OKL_API.G_RET_STS_UNEXP_ERROR',
1168 x_msg_count,
1169 x_msg_data,
1170 '_PVT'
1171 );
1172 WHEN OTHERS THEN
1173 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1174 (
1175 l_api_name,
1176 G_PKG_NAME,
1177 'OTHERS',
1178 x_msg_count,
1179 x_msg_data,
1180 '_PVT');
1181
1182 END CREATE_SEC_REP_TRX;
1183
1184 PROCEDURE REVERSE_SEC_REP_TRX (
1185 P_API_VERSION IN NUMBER,
1186 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1187 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1188 X_MSG_COUNT OUT NOCOPY NUMBER,
1189 X_MSG_DATA OUT NOCOPY VARCHAR2,
1190 P_TCNV_REC OKL_TCN_PVT.TCNV_REC_TYPE
1191 ) IS
1192
1193 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1194
1195 /* variables */
1196 l_api_name CONSTANT VARCHAR2(40) := 'REVERSE_SEC_REP_TRX';
1197 l_api_version CONSTANT NUMBER := 1.0;
1198 l_source_table CONSTANT OKL_TRNS_ACC_DSTRS.SOURCE_TABLE%TYPE := 'OKL_TXL_CNTRCT_LNS';
1199 l_cntrct_id OKL_K_HEADERS_FULL_V.ID%TYPE;
1200 l_sysdate DATE := SYSDATE;
1201 l_reversal_date DATE;
1202 l_COUNT NUMBER :=0;
1203 /* record and table structure variables */
1204 l_pri_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type := P_TCNV_REC;
1205 l_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
1206 x_tclv_tbl OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
1207 l_tcnv_tbl OKL_TRX_CONTRACTS_PUB.tcnv_tbl_type;
1208 x_tcnv_tbl OKL_TRX_CONTRACTS_PUB.tcnv_tbl_type;
1209 l_source_id_tbl OKL_REVERSAL_PUB.source_id_tbl_type;
1210 --
1211 TYPE t_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1212 l_trx_date_tbl t_date;
1213 --
1214 -- Cursor to select transaction headers for reversal
1215 CURSOR reverse_trx_csr(p_id NUMBER) IS
1216 SELECT id, date_transaction_occurred, transaction_date
1217 FROM OKL_TRX_CONTRACTS trx
1218 WHERE trx.primary_rep_trx_id = p_id
1219 AND trx.tsu_code = 'PROCESSED'
1220 AND trx.representation_type = G_SECONDARY;
1221
1222 -- Cursor to select transaction lines for reversal
1223 CURSOR reverse_txl_csr(p_tcn_id NUMBER) IS
1224 SELECT txl.id, txl.amount, txl.currency_code
1225 FROM OKL_TXL_CNTRCT_LNS txl,
1226 OKL_TRX_CONTRACTS trx
1227 WHERE txl.tcn_id = trx.id
1228 AND txl.tcn_id = p_tcn_id
1229 AND trx.tsu_code = 'PROCESSED'
1230 AND trx.representation_type = G_SECONDARY;
1231
1232 BEGIN
1233
1234 IF (G_DEBUG_ENABLED = 'Y') THEN
1235 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1236 END IF;
1237
1238
1239 l_return_status := OKL_API.START_ACTIVITY(l_api_name
1240 ,G_PKG_NAME
1241 ,p_init_msg_list
1242 ,l_api_version
1243 ,p_api_version
1244 ,'_PVT'
1245 ,l_return_status);
1246
1247 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1248 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1249 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1250 RAISE OKL_API.G_EXCEPTION_ERROR;
1251 END IF;
1252
1253 -- Open reverse trx csr for update of transaction header
1254 FOR l_reverse_trx_csr IN reverse_trx_csr(l_pri_tcnv_rec.ID)
1255 LOOP
1256 l_tcnv_tbl(l_COUNT).id := l_reverse_trx_csr.id;
1257 l_COUNT := l_COUNT+1;
1258 END LOOP;
1259
1260 l_COUNT :=0;
1261
1262 IF l_tcnv_tbl.COUNT > 0 THEN
1263 -- proceed only if records found for reversal
1264
1265 -- Build the transaction record for update
1266 FOR i IN l_tcnv_tbl.FIRST..l_tcnv_tbl.LAST
1267 LOOP
1268
1269 l_tcnv_tbl(i).tsu_code := 'CANCELED';
1270 l_tcnv_tbl(i).canceled_date := l_pri_tcnv_rec.canceled_date;
1271
1272 END LOOP;
1273
1274 l_COUNT :=0;
1275
1276 -- New code to process reversals by tcn_id .. bugs 6194225 and 6194204
1277 FOR i IN l_tcnv_tbl.FIRST..l_tcnv_tbl.LAST LOOP
1278
1279 -- Open reverse txl cursor to find out transaction line id's for reversal
1280 FOR l_reverse_txl_csr IN reverse_txl_csr(l_tcnv_tbl(i).id)
1281 LOOP
1282 l_source_id_tbl(l_COUNT) := l_reverse_txl_csr.id;
1283 l_COUNT := l_COUNT+1;
1284 END LOOP;
1285
1286 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1287 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to Okl_Reversal_Pub.REVERSE_ENTRIES');
1288 END IF;
1289
1290 -- reverse accounting entries
1291 Okl_Reversal_Pub.REVERSE_ENTRIES(
1292 p_api_version => p_api_version,
1293 p_init_msg_list => p_init_msg_list,
1294 x_return_status => l_return_status,
1295 x_msg_count => x_msg_count,
1296 x_msg_data => x_msg_data,
1297 p_source_table => l_source_table,
1298 p_acct_date => l_tcnv_tbl(i).canceled_date, -- l_reversal_date,
1299 p_source_id_tbl => l_source_id_tbl);
1300
1301 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1302 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Reversal_Pub.REVERSE_ENTRIES :'||l_return_status);
1303 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Reversal_Pub.REVERSE_ENTRIES, l_source_table :'||l_source_table);
1304 END IF;
1305
1306 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1307 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1308 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1309 RAISE OKL_API.G_EXCEPTION_ERROR;
1310 END IF;
1311
1312 END LOOP; -- new logic for reversing by tcn_id.
1313
1314 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1315 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to Okl_Trx_Contracts_Pub.update_trx_contracts');
1316 END IF;
1317 --Call the transaction public api to update tsu_code
1318 Okl_Trx_Contracts_Pub.update_trx_contracts
1319 (p_api_version => p_api_version,
1320 p_init_msg_list => p_init_msg_list,
1321 x_return_status => l_return_status,
1322 x_msg_count => x_msg_count,
1323 x_msg_data => x_msg_data,
1324 p_tcnv_tbl => l_tcnv_tbl,
1325 x_tcnv_tbl => x_tcnv_tbl);
1326
1327 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1328 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Trx_Contracts_Pub.update_trx_contracts :'||l_return_status);
1329 END IF;
1330
1331 -- store the highest degree of error
1332 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1333 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1334 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1335 RAISE OKL_API.G_EXCEPTION_ERROR;
1336 END IF;
1337
1338
1339 END IF; -- for if tcnv_tbl.count > 0 condition
1340
1341 -- set the return status
1342 x_return_status := l_return_status;
1343
1344 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1345
1346 EXCEPTION
1347 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1348 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1349 ,g_pkg_name
1350 ,'OKL_API.G_RET_STS_ERROR'
1351 ,x_msg_count
1352 ,x_msg_data
1353 ,'_PVT');
1354
1355 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1356 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1357 ,g_pkg_name
1358 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1359 ,x_msg_count
1360 ,x_msg_data
1361 ,'_PVT');
1362
1363 WHEN OTHERS THEN
1364 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1365 (l_api_name,
1366 G_PKG_NAME,
1367 'OTHERS',
1368 x_msg_count,
1369 x_msg_data,
1370 '_PVT');
1371 END REVERSE_SEC_REP_TRX;
1372
1373 PROCEDURE REVERSE_SEC_REP_TRX (
1374 P_API_VERSION IN NUMBER,
1375 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1376 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1377 X_MSG_COUNT OUT NOCOPY NUMBER,
1378 X_MSG_DATA OUT NOCOPY VARCHAR2,
1379 P_TCNV_TBL tcnv_tbl_type)
1380 IS
1381
1382 /* variables */
1383 l_api_name CONSTANT VARCHAR2(40) := 'REVERSE_SEC_REP_TRX';
1384 l_api_version CONSTANT NUMBER := 1.0;
1385 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1386
1387 BEGIN
1388
1389 l_return_status := OKL_API.START_ACTIVITY(l_api_name
1390 ,G_PKG_NAME
1391 ,p_init_msg_list
1392 ,l_api_version
1393 ,p_api_version
1394 ,'_PVT'
1395 ,l_return_status);
1396
1397 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1398 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1399 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1400 RAISE OKL_API.G_EXCEPTION_ERROR;
1401 END IF;
1402
1403 IF (p_tcnv_tbl.COUNT > 0) THEN
1404
1405 -- call recrod level implementation in loop
1406 FOR i in p_tcnv_tbl.FIRST..p_tcnv_tbl.LAST LOOP
1407 REVERSE_SEC_REP_TRX (
1408 p_api_version => p_api_version
1409 ,p_init_msg_list => p_init_msg_list
1410 ,x_return_status => l_return_status
1411 ,x_msg_count => x_msg_count
1412 ,x_msg_data => x_msg_data
1413 ,p_tcnv_rec => p_tcnv_tbl(i));
1414
1415 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1416 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1417 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1418 RAISE OKL_API.G_EXCEPTION_ERROR;
1419 END IF;
1420
1421 END LOOP;
1422
1423 END IF;
1424
1425 -- set the overall return status
1426 x_return_status := l_return_status;
1427
1428 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1429
1430 EXCEPTION
1431 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1432 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1433 ,g_pkg_name
1434 ,'OKL_API.G_RET_STS_ERROR'
1435 ,x_msg_count
1436 ,x_msg_data
1437 ,'_PVT');
1438
1439 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1440 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1441 ,g_pkg_name
1442 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1443 ,x_msg_count
1444 ,x_msg_data
1445 ,'_PVT');
1446
1447 WHEN OTHERS THEN
1448 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1449 (l_api_name,
1450 G_PKG_NAME,
1451 'OTHERS',
1452 x_msg_count,
1453 x_msg_data,
1454 '_PVT');
1455
1456 END REVERSE_SEC_REP_TRX;
1457
1458 END OKL_MULTIGAAP_ENGINE_PVT;