[Home] [Help]
PACKAGE BODY: APPS.OKL_MULTIGAAP_ENGINE_PVT
Source
1 PACKAGE BODY OKL_MULTIGAAP_ENGINE_PVT AS
2 /* $Header: OKLRMGEB.pls 120.4.12020000.3 2012/08/07 12:16:34 bkatraga ship $ */
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 --Bug# 8969376
545 CURSOR get_primary_sty_id_csr
546 (p_pdt_id number,
547 p_strm_purpose okl_strm_type_v.stream_type_purpose%type,
548 p_contract_start_date date) IS
549 SELECT PRIMARY_STY_ID
550 FROM OKL_STRM_TMPT_LINES_UV STL
551 WHERE --STL.PRIMARY_YN = 'Y' AND
552 STL.PDT_ID = p_pdt_id
553 AND (STL.START_DATE <= p_contract_start_date)
554 AND (STL.END_DATE >= p_contract_start_date OR STL.END_DATE IS NULL)
555 AND PRIMARY_STY_PURPOSE = p_strm_purpose;
556
557 CURSOR get_dependent_sty_id_csr
558 (p_pdt_id number,
559 p_strm_purpose okl_strm_type_v.stream_type_purpose%type,
560 p_contract_start_date date) IS
561 SELECT DEPENDENT_STY_ID
562 FROM OKL_STRM_TMPT_LINES_UV STL
563 WHERE --STL.PRIMARY_YN = 'Y' AND
564 STL.PDT_ID = p_pdt_id
565 AND (STL.START_DATE <= p_contract_start_date)
566 AND (STL.END_DATE >= p_contract_start_date OR STL.END_DATE IS NULL)
567 AND DEPENDENT_STY_PURPOSE = p_strm_purpose;
568 --Bug# 8969376
569
570
571 BEGIN
572
573 IF (G_DEBUG_ENABLED = 'Y') THEN
574 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
575 END IF;
576
577 x_return_status := OKL_API.START_ACTIVITY(
578 p_api_name => l_api_name,
579 p_pkg_name => g_pkg_name,
580 p_init_msg_list => p_init_msg_list,
581 l_api_version => l_api_version,
582 p_api_version => p_api_version,
583 p_api_type => G_API_TYPE,
584 x_return_status => x_return_status);
585
586 -- check if activity started successfully
587 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
588 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
589 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
590 raise OKL_API.G_EXCEPTION_ERROR;
591 End If;
592
593
594 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
595 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to is_srm_automated');
596 END IF;
597
598 IF NOT (is_srm_automated()) THEN
599 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Secondary representation method is not automated, returning...');
600 RETURN;
601 END IF;
602
603 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
604 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_srm_automated');
605 END IF;
606
607 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
608 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));
609 END IF;
610
611 IF NOT (is_mg_enabled(l_pri_trx_tcnv_rec.khr_id)) THEN
612 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Multi gaap is not enabled, returning...');
613 RETURN;
614 END IF;
615
616 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
617 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_mg_enabled');
618 END IF;
619
620
621 -- null out the primary trx attributes which are not applicable to secondary trx
622 l_pri_trx_tcnv_rec := null_out_primary_trx_defaults (l_pri_trx_tcnv_rec);
623
624
625 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
626 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to set_secondary_trx_attribs');
627 END IF;
628
629
630 -- set secondary transaction attributes
631 set_secondary_trx_attribs( p_api_version => l_api_version,
632 p_init_msg_list => p_init_msg_list,
633 x_return_status => x_return_status,
634 x_msg_count => x_msg_count,
635 x_msg_data => x_msg_data,
636 p_tcnv_rec => l_pri_trx_tcnv_rec,
637 x_tcnv_tbl => l_sec_trx_tcnv_tbl);
638
639 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
640 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);
641 END IF;
642
643 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
645 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
646 RAISE OKL_API.G_EXCEPTION_ERROR;
647 END IF;
648
649
650 -- create the secondary transaction header
651 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
652 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.create_trx_contracts');
653 END IF;
654
655
656 OKL_TRX_CONTRACTS_PUB.create_trx_contracts(p_api_version => l_api_version,
657 p_init_msg_list => p_init_msg_list,
658 x_return_status => x_return_status,
659 x_msg_count => x_msg_count,
660 x_msg_data => x_msg_data,
661 p_tcnv_rec => l_sec_trx_tcnv_tbl(1),
662 x_tcnv_rec => x_sec_trx_tcnv_tbl(1));
663
664 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
665 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);
666 END IF;
667
668 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
669 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
670 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
671 RAISE OKL_API.G_EXCEPTION_ERROR;
672 END IF;
673
674 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
675 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to okl_accounting_util.get_valid_gl_date');
676 END IF;
677
678 -- SGIYER.12/08/08.Get valid GL date for template info and AE
679 l_valid_gl_date := okl_accounting_util.get_valid_gl_date
680 (p_gl_date => l_sec_trx_tcnv_tbl(1).date_transaction_occurred,
681 p_ledger_id => l_sec_trx_tcnv_tbl(1).set_of_books_id);
682
683 IF (l_valid_gl_date is null) THEN
684 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
685 p_msg_name => 'OKL_INVALID_GL_DATE');
686 RAISE OKL_API.G_EXCEPTION_ERROR;
687 END IF;
688
689 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
690 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));
691 END IF;
692
693 -- FOR m IN 1.. x_sec_trx_tcnv_tbl.COUNT -- this for loop is to handle multiple secondary trxs
694 -- LOOP
695 -- is_formula_based
696 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
697 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to is_formula_based');
698 END IF;
699
700 l_formula_yn := is_formula_based (l_pri_trx_id);
701
702 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
703 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_formula_based');
704 END IF;
705
706 IF(l_formula_yn) THEN -- get the amount from acc engine and update the secondary trx hdr and lines
707
708 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
709 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_formula_based TRUE');
710 END IF;
711
712 l_tmpl_identify_rec.product_id := x_sec_trx_tcnv_tbl(1).pdt_id;
713 l_tmpl_identify_rec.transaction_type_id := x_sec_trx_tcnv_tbl(1).try_id;
714 l_tmpl_identify_rec.memo_yn := 'N';
715 l_tmpl_identify_rec.prior_year_yn := 'N';
716
717 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
718 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_ACCOUNT_DIST_PVT.GET_TEMPLATE_INFO');
719 END IF;
720
721 OKL_ACCOUNT_DIST_PVT.GET_TEMPLATE_INFO(p_api_version => l_api_version,
722 p_init_msg_list => p_init_msg_list,
723 x_return_status => l_return_status,
724 x_msg_count => x_msg_count,
725 x_msg_data => x_msg_data,
726 p_tmpl_identify_rec => l_tmpl_identify_rec,
727 x_template_tbl => l_template_tbl,
728 p_validity_date => l_valid_gl_date); -- SGIYER-12/08/08.
729
730 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
731 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);
732 END IF;
733 -- Raise an error if template is not found.
734
735 IF (l_template_tbl.COUNT = 0) THEN
736 FOR trx_type_rec IN trx_type_csr (l_tmpl_identify_rec.transaction_type_id) LOOP
737 l_trx_type_name := trx_type_rec.name;
738 END LOOP;
739 Okl_Api.set_message(p_app_name => g_app_name,
740 p_msg_name => 'OKL_LA_NO_ACCOUNTING_TMPLTS',
741 p_token1 => 'TRANSACTION_TYPE',
742 p_token1_value => l_trx_type_name);
743 RAISE OKL_API.G_EXCEPTION_ERROR;
744 END IF;
745
746 -- chekc if the transaction line or contract based
747 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
748 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to is_line_based');
749 END IF;
750 l_is_line_based := is_line_based(l_pri_trx_id);
751 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
752 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_line_based');
753 END IF;
754
755 IF( l_is_line_based ) THEN -- that is trans type termination, split, evergreen
756
757 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
758 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to is_line_based TRUE');
759 END IF;
760 -- read all the distinct kle ids of primary trx lines and create kle ids * acc tmpl
761 FOR get_kle_ids_rec IN get_kle_ids_csr( l_pri_trx_id ) LOOP
762
763 -- read all the accounting template lines and assign to secondary trx lines
764 FOR k IN l_template_tbl.first..l_template_tbl.LAST LOOP
765
766 -- bug 7625968. SGIYER - 12/09/2008
767 -- Changed variable from 'k' to 'j' in the tbl l_sec_trx_tclv_tbl
768 j := j + 1;
769 l_sec_trx_tclv_tbl(j).khr_id := l_pri_trx_tcnv_rec.khr_id;
770 l_sec_trx_tclv_tbl(j).tcl_type := l_pri_trx_tclv_rec.tcl_type;
771 l_sec_trx_tclv_tbl(j).kle_id := get_kle_ids_rec.kle_id;
772 l_sec_trx_tclv_tbl(j).tcn_id := x_sec_trx_tcnv_tbl(1).id;
773 l_sec_trx_tclv_tbl(j).id := null;
774 l_sec_trx_tclv_tbl(j).line_number := j;
775 l_sec_trx_tclv_tbl(j).OBJECT_VERSION_NUMBER := null;
776 l_sec_trx_tclv_tbl(j).amount := null;
777 l_sec_trx_tclv_tbl(j).STY_ID := l_template_tbl(k).sty_id;
778 l_sec_trx_tclv_tbl(j).currency_code := x_sec_trx_tcnv_tbl(1).currency_code;
779
780 --Bug 7625968. brough the below inside the loop.
781 l_tmpl_identify_tbl(j).product_id := l_tmpl_identify_rec.product_id;
782 l_tmpl_identify_tbl(j).transaction_type_id := l_tmpl_identify_rec.transaction_type_id;
783 l_tmpl_identify_tbl(j).memo_yn := l_tmpl_identify_rec.memo_yn;
784 l_tmpl_identify_tbl(j).prior_year_yn := l_tmpl_identify_rec.prior_year_yn;
785 l_tmpl_identify_tbl(j).stream_type_id := l_template_tbl(k).sty_id;
786 l_tmpl_identify_tbl(j).advance_arrears := l_template_tbl(k).advance_arrears;
787 l_tmpl_identify_tbl(j).factoring_synd_flag := l_template_tbl(k).factoring_synd_flag;
788 l_tmpl_identify_tbl(j).investor_code := l_template_tbl(k).inv_code;
789 l_tmpl_identify_tbl(j).syndication_code := l_template_tbl(k).syt_code;
790 l_tmpl_identify_tbl(j).factoring_code := l_template_tbl(k).fac_code;
791
792 END LOOP;
793
794 END LOOP;
795
796 ELSE -- for all the other trx types
797 j := 0;
798 -- read all the accounting template lines and assign to secondary trx lines
799 -- we do not need
800 FOR k IN l_template_tbl.FIRST..l_template_tbl.LAST LOOP
801 j := j + 1;
802 l_sec_trx_tclv_tbl(j) := l_pri_trx_tclv_rec;
803 l_sec_trx_tclv_tbl(j).id := null;
804 l_sec_trx_tclv_tbl(j).line_number := k;
805 l_sec_trx_tclv_tbl(j).OBJECT_VERSION_NUMBER := null;
806 l_sec_trx_tclv_tbl(j).amount := null;
807 l_sec_trx_tclv_tbl(j).sty_id := l_template_tbl(k).sty_id;
808 l_sec_trx_tclv_tbl(j).tcn_id := x_sec_trx_tcnv_tbl(1).id;
809 l_sec_trx_tclv_tbl(j).currency_code := x_sec_trx_tcnv_tbl(1).currency_code;
810
811 --Bug 7625968. brough the below inside the loop.
812 l_tmpl_identify_tbl(j).product_id := l_tmpl_identify_rec.product_id;
813 l_tmpl_identify_tbl(j).transaction_type_id := l_tmpl_identify_rec.transaction_type_id;
814 l_tmpl_identify_tbl(j).memo_yn := l_tmpl_identify_rec.memo_yn;
815 l_tmpl_identify_tbl(j).prior_year_yn := l_tmpl_identify_rec.prior_year_yn;
816 l_tmpl_identify_tbl(j).stream_type_id := l_template_tbl(k).sty_id;
817 l_tmpl_identify_tbl(j).advance_arrears := l_template_tbl(k).advance_arrears;
818 l_tmpl_identify_tbl(j).factoring_synd_flag := l_template_tbl(k).factoring_synd_flag;
819 l_tmpl_identify_tbl(j).investor_code := l_template_tbl(k).inv_code;
820 l_tmpl_identify_tbl(j).syndication_code := l_template_tbl(k).syt_code;
821 l_tmpl_identify_tbl(j).factoring_code := l_template_tbl(k).fac_code;
822
823 END LOOP;
824
825 END IF;
826
827 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
828 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines');
829 END IF;
830
831 -- create the secondary transaction lines for each acc tmpl
832 OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines
833 (p_api_version => l_api_version,
834 p_init_msg_list => p_init_msg_list,
835 x_return_status => l_return_status,
836 x_msg_count => x_msg_count,
837 x_msg_data => x_msg_data,
838 p_tclv_tbl => l_sec_trx_tclv_tbl,
839 x_tclv_tbl => x_sec_trx_tclv_tbl);
840
841 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
842 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
843 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
844 RAISE OKL_API.G_EXCEPTION_ERROR;
845 END IF;
846
847 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
848 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);
849 END IF;
850
851 -- accounting distributiions
852
853 OPEN l_contract_currency_csr;
854 FETCH l_contract_currency_csr INTO l_curr_code, l_currency_conversion_rate,l_currency_conversion_type, l_currency_conversion_date ;
855 CLOSE l_contract_currency_csr;
856
857 IF( l_curr_code IS NULL ) THEN
858 OKL_API.set_message( p_app_name => 'OKL',
859 p_msg_name => G_REQUIRED_VALUE,
860 p_token1 => g_col_name_token,
861 p_token1_value => 'CURRENCY_CODE');
862 RAISE OKL_API.G_EXCEPTION_ERROR;
863 END IF;
864
865 FOR i IN x_sec_trx_tclv_tbl.FIRST..x_sec_trx_tclv_tbl.LAST LOOP
866
867 l_dist_info_tbl(i).SOURCE_ID := x_sec_trx_tclv_tbl(i).ID;
868 l_dist_info_tbl(i).SOURCE_TABLE := 'OKL_TXL_CNTRCT_LNS';
869 l_dist_info_tbl(i).GL_REVERSAL_FLAG := 'N';
870 l_dist_info_tbl(i).POST_TO_GL := 'Y';
871 l_dist_info_tbl(i).CONTRACT_ID := l_pri_trx_tcnv_rec.KHR_ID;
872 l_dist_info_tbl(i).CONTRACT_LINE_ID := x_sec_trx_tclv_tbl(i).KLE_ID; -- Bug 7626121
873 l_dist_info_tbl(i).CURRENCY_CONVERSION_RATE := l_currency_conversion_rate;
874 l_dist_info_tbl(i).CURRENCY_CONVERSION_TYPE := l_currency_conversion_type;
875 l_dist_info_tbl(i).CURRENCY_CONVERSION_DATE := l_currency_conversion_date;
876 l_dist_info_tbl(i).CURRENCY_CODE := l_curr_code;
877 l_dist_info_tbl(i).ACCOUNTING_DATE := l_valid_gl_date;
878 l_dist_info_tbl(i).amount := x_sec_trx_tclv_tbl(i).amount;
879
880 --Assigning the account generator table.SGIYER
881 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
882 l_acc_gen_tbl(i).source_id := x_sec_trx_tclv_tbl(i).ID;
883
884 IF (l_ctxt_val_tbl.COUNT > 0) THEN
885 l_ctxt_tbl(i).source_id := x_sec_trx_tclv_tbl(i).id;
886 END IF;
887
888 END LOOP;
889
890 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
891 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST');
892 END IF;
893
894 OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST(
895 p_api_version => l_api_version,
896 p_init_msg_list => p_init_msg_list,
897 x_return_status => l_return_status,
898 x_msg_count => x_msg_count,
899 x_msg_data => x_msg_data,
900 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
901 p_dist_info_tbl => l_dist_info_tbl,
902 p_ctxt_val_tbl => l_ctxt_tbl,
903 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
904 x_template_tbl => x_template_tbl,
905 x_amount_tbl => x_amount_out_tbl,
906 p_trx_header_id => x_sec_trx_tcnv_tbl(1).id);
907
908 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
909 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);
910 END IF;
911
912 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
913 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
914 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
915 RAISE OKL_API.G_EXCEPTION_ERROR;
916 END IF;
917
918 If x_sec_trx_tclv_tbl.COUNT > 0 then
919 FOR i in x_sec_trx_tclv_tbl.FIRST..x_sec_trx_tclv_tbl.LAST LOOP
920 l_amount_tbl.delete;
921 If x_amount_out_tbl.COUNT > 0 then
922 For k in x_amount_out_tbl.FIRST..x_amount_out_tbl.LAST LOOP
923 IF x_sec_trx_tclv_tbl(i).id = x_amount_out_tbl(k).source_id THEN
924 l_amount_tbl := x_amount_out_tbl(k).amount_tbl;
925 x_sec_trx_tclv_tbl(i).currency_code := l_curr_code;
926 IF l_amount_tbl.COUNT > 0 THEN
927 FOR j in l_amount_tbl.FIRST..l_amount_tbl.LAST LOOP
928 x_sec_trx_tclv_tbl(i).amount := nvl(x_sec_trx_tclv_tbl(i).amount,0) + l_amount_tbl(j);
929 END LOOP; -- for j in
930 END IF;-- If l_amount_tbl.COUNT
931 END IF; ---- IF x_sec_trx_tclv_tbl(i).id
932 END LOOP; -- For k in
933 END IF; -- If l_amount_out_tbl.COUNT
934 x_sec_trx_tcnv_tbl(1).amount := nvl(x_sec_trx_tcnv_tbl(1).amount,0) + x_sec_trx_tclv_tbl(i).amount;
935 -- l_tcnv_rec.currency_code := l_currency_code;
936 -- l_tcnv_rec.tsu_code := 'PROCESSED';
937 END LOOP; -- For i in
938 End If; -- If l_tclv_tbl.COUNT
939
940 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
941 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.update_trx_cntrct_lines');
942 END IF;
943 --Update the lines with the amount
944 OKL_TRX_CONTRACTS_PUB.update_trx_cntrct_lines
945 (p_api_version => l_api_version,
946 p_init_msg_list => p_init_msg_list,
947 x_return_status => x_return_status,
948 x_msg_count => x_msg_count,
949 x_msg_data => x_msg_data,
950 p_tclv_tbl => x_sec_trx_tclv_tbl,
951 x_tclv_tbl => l_sec_trx_tclv_tbl);
952
953 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
954 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);
955 END IF;
956
957 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
958 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
959 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
960 RAISE OKL_API.G_EXCEPTION_ERROR;
961 END IF;
962
963 --Update the header with the amount
964 x_sec_trx_tcnv_tbl(1).tsu_code := 'PROCESSED';
965 x_sec_trx_tcnv_tbl(1).amount := x_sec_trx_tcnv_tbl(1).amount;
966
967 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
968 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.update_trx_contracts');
969 END IF;
970
971 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
972 p_init_msg_list => p_init_msg_list,
973 x_return_status => x_return_status,
974 x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data,
976 p_tcnv_rec => x_sec_trx_tcnv_tbl(1),
977 x_tcnv_rec => x_sec_trx_tcnv_tbl(1));
978
979
980 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
981 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);
982 END IF;
983
984 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
985 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
986 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
987 RAISE OKL_API.G_EXCEPTION_ERROR;
988 END IF;
989
990 ELSE
991
992 -- if not formula then do not null out the amt and
993 -- copy the lines as is from the primary trx hdr onto secondary trx lines
994 -- first get sty id, get the purpose code of the primary, then query
995
996 l_sec_trx_tclv_tbl := p_tclv_tbl;
997
998 FOR i IN l_sec_trx_tclv_tbl.FIRST..l_sec_trx_tclv_tbl.LAST LOOP
999
1000 l_sec_trx_tclv_tbl(i).id := null;
1001 l_sec_trx_tclv_tbl(i).tcn_id := x_sec_trx_tcnv_tbl(1).id;
1002 l_sec_trx_tclv_tbl(i).object_version_number := null;
1003
1004
1005 l_sty_id := null;
1006 l_contract_start_date := null;
1007
1008 OPEN cntrct_csr (l_sec_trx_tcnv_tbl(1).khr_id);
1009 FETCH cntrct_csr INTO l_contract_start_date;
1010 CLOSE cntrct_csr;
1011
1012 IF l_contract_start_date IS NULL THEN
1013 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1014 p_msg_name => g_required_value,
1015 p_token1 => g_col_name_token,
1016 p_token1_value => 'CONTRACT_START_DATE');
1017
1018 RAISE OKL_API.G_EXCEPTION_ERROR;
1019 END IF;
1020
1021 --Bug# 8969376
1022 open get_primary_sty_id_csr(l_sec_trx_tcnv_tbl(1).pdt_id, l_sec_trx_tclv_tbl(i).stream_type_purpose, l_contract_start_date);
1023 fetch get_primary_sty_id_csr into l_sty_id;
1024 close get_primary_sty_id_csr;
1025
1026 IF( l_sty_id IS NULL ) THEN
1027 open get_dependent_sty_id_csr(l_sec_trx_tcnv_tbl(1).pdt_id, l_sec_trx_tclv_tbl(i).stream_type_purpose, l_contract_start_date);
1028 fetch get_dependent_sty_id_csr into l_sty_id;
1029 close get_dependent_sty_id_csr;
1030 END IF;
1031 --Bug# 8969376
1032
1033 IF( l_sty_id IS NULL ) THEN
1034 Okl_Api.set_message(p_app_name => g_app_name,
1035 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1036 p_token1 => g_stream_name_token,
1037 p_token1_value => l_sec_trx_tclv_tbl(i).stream_type_purpose);
1038 RAISE OKL_API.G_EXCEPTION_ERROR;
1039 END IF;
1040 l_sec_trx_tclv_tbl(i).sty_id := l_sty_id;
1041
1042 END LOOP;
1043
1044 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1045 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines');
1046 END IF;
1047
1048 -- create the secondary transaction lines for each acc tmpl
1049 OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines
1050 (p_api_version => l_api_version,
1051 p_init_msg_list => p_init_msg_list,
1052 x_return_status => x_return_status,
1053 x_msg_count => x_msg_count,
1054 x_msg_data => x_msg_data,
1055 p_tclv_tbl => l_sec_trx_tclv_tbl,
1056 x_tclv_tbl => x_sec_trx_tclv_tbl);
1057
1058 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1059 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);
1060 END IF;
1061
1062 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1063 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1064 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1065 RAISE OKL_API.G_EXCEPTION_ERROR;
1066 END IF;
1067
1068 -- make the call to accounting distributions
1069 OPEN l_contract_currency_csr;
1070 FETCH l_contract_currency_csr INTO l_curr_code, l_currency_conversion_rate,l_currency_conversion_type, l_currency_conversion_date ;
1071 CLOSE l_contract_currency_csr;
1072
1073 IF( l_curr_code IS NULL ) THEN
1074 OKL_API.set_message( p_app_name => 'OKL',
1075 p_msg_name => G_REQUIRED_VALUE,
1076 p_token1 => g_col_name_token,
1077 p_token1_value => 'CURRENCY_CODE');
1078 RAISE OKL_API.G_EXCEPTION_ERROR;
1079 END IF;
1080
1081
1082 FOR i IN x_sec_trx_tclv_tbl.FIRST..x_sec_trx_tclv_tbl.LAST LOOP
1083
1084 l_tmpl_identify_tbl(i).product_id := x_sec_trx_tcnv_tbl(1).pdt_id;
1085 l_tmpl_identify_tbl(i).stream_type_id := x_sec_trx_tclv_tbl(i).sty_id;
1086 l_tmpl_identify_tbl(i).transaction_type_id := x_sec_trx_tcnv_tbl(1).try_id;
1087 l_tmpl_identify_tbl(i).advance_arrears := NULL;
1088 l_tmpl_identify_tbl(i).prior_year_yn := NULL;
1089 l_tmpl_identify_tbl(i).memo_yn := NULL;
1090 l_tmpl_identify_tbl(i).investor_code := NULL;
1091 l_tmpl_identify_tbl(i).SYNDICATION_CODE := NULL;
1092 l_tmpl_identify_tbl(i).FACTORING_CODE := NULL;
1093 l_tmpl_identify_tbl(i).rev_rec_flag := NULL;
1094 l_tmpl_identify_tbl(i).factoring_synd_flag := NULL;
1095
1096 l_dist_info_tbl(i).SOURCE_ID := x_sec_trx_tclv_tbl(i).ID;
1097 l_dist_info_tbl(i).SOURCE_TABLE := 'OKL_TXL_CNTRCT_LNS';
1098 l_dist_info_tbl(i).GL_REVERSAL_FLAG := 'N';
1099 l_dist_info_tbl(i).POST_TO_GL := 'Y';
1100 l_dist_info_tbl(i).CONTRACT_ID := l_pri_trx_tcnv_rec.KHR_ID;
1101 l_dist_info_tbl(i).CONTRACT_LINE_ID := x_sec_trx_tclv_tbl(i).KLE_ID; -- Bug 7626121
1102 l_dist_info_tbl(i).CURRENCY_CONVERSION_RATE := l_currency_conversion_rate;
1103 l_dist_info_tbl(i).CURRENCY_CONVERSION_TYPE := l_currency_conversion_type;
1104 l_dist_info_tbl(i).CURRENCY_CONVERSION_DATE := l_currency_conversion_date;
1105 l_dist_info_tbl(i).CURRENCY_CODE := l_curr_code;
1106 l_dist_info_tbl(i).ACCOUNTING_DATE := x_sec_trx_tcnv_tbl(1).date_transaction_occurred;
1107 l_dist_info_tbl(i).amount := x_sec_trx_tclv_tbl(i).amount;
1108
1109 --Assigning the account generator table.SGIYER
1110 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
1111 l_acc_gen_tbl(i).source_id := x_sec_trx_tclv_tbl(i).ID;
1112
1113
1114 END LOOP;
1115
1116 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1117 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST');
1118 END IF;
1119
1120 OKL_ACCOUNT_DIST_PVT.CREATE_ACCOUNTING_DIST(
1121 p_api_version => l_api_version,
1122 p_init_msg_list => p_init_msg_list,
1123 x_return_status => l_return_status,
1124 x_msg_count => x_msg_count,
1125 x_msg_data => x_msg_data,
1126 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
1127 p_dist_info_tbl => l_dist_info_tbl,
1128 p_ctxt_val_tbl => l_ctxt_tbl,
1129 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
1130 x_template_tbl => x_template_tbl,
1131 x_amount_tbl => x_amount_out_tbl,
1132 p_trx_header_id => x_sec_trx_tcnv_tbl(1).id);
1133
1134 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1135 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);
1136 END IF;
1137
1138 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1139 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1140 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1141 RAISE OKL_API.G_EXCEPTION_ERROR;
1142 END IF;
1143
1144 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1145 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_TRX_CONTRACTS_PUB.update_trx_contracts');
1146 END IF;
1147
1148 --Update the header with the amount
1149 x_sec_trx_tcnv_tbl(1).tsu_code := 'PROCESSED';
1150 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
1151 p_init_msg_list => p_init_msg_list,
1152 x_return_status => x_return_status,
1153 x_msg_count => x_msg_count,
1154 x_msg_data => x_msg_data,
1155 p_tcnv_rec => x_sec_trx_tcnv_tbl(1),
1156 x_tcnv_rec => x_sec_trx_tcnv_tbl(1));
1157
1158
1159 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1160 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);
1161 END IF;
1162
1163 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1164 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1165 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1166 RAISE OKL_API.G_EXCEPTION_ERROR;
1167 END IF;
1168
1169 END IF;
1170 x_return_status := okl_api.g_ret_sts_success;
1171 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1172
1173 EXCEPTION
1174 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1175 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1176 (
1177 l_api_name,
1178 G_PKG_NAME,
1179 'OKL_API.G_RET_STS_ERROR',
1180 x_msg_count,
1181 x_msg_data,
1182 '_PVT'
1183 );
1184 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1185 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1186 (
1187 l_api_name,
1188 G_PKG_NAME,
1189 'OKL_API.G_RET_STS_UNEXP_ERROR',
1190 x_msg_count,
1191 x_msg_data,
1192 '_PVT'
1193 );
1194 WHEN OTHERS THEN
1195 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1196 (
1197 l_api_name,
1198 G_PKG_NAME,
1199 'OTHERS',
1200 x_msg_count,
1201 x_msg_data,
1202 '_PVT');
1203
1204 END CREATE_SEC_REP_TRX;
1205
1206 PROCEDURE REVERSE_SEC_REP_TRX (
1207 P_API_VERSION IN NUMBER,
1208 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1209 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1210 X_MSG_COUNT OUT NOCOPY NUMBER,
1211 X_MSG_DATA OUT NOCOPY VARCHAR2,
1212 P_TCNV_REC OKL_TCN_PVT.TCNV_REC_TYPE
1213 ) IS
1214
1215 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1216
1217 /* variables */
1218 l_api_name CONSTANT VARCHAR2(40) := 'REVERSE_SEC_REP_TRX';
1219 l_api_version CONSTANT NUMBER := 1.0;
1220 l_source_table CONSTANT OKL_TRNS_ACC_DSTRS.SOURCE_TABLE%TYPE := 'OKL_TXL_CNTRCT_LNS';
1221 l_cntrct_id OKL_K_HEADERS_FULL_V.ID%TYPE;
1222 l_sysdate DATE := SYSDATE;
1223 l_reversal_date DATE;
1224 l_COUNT NUMBER :=0;
1225 /* record and table structure variables */
1226 l_pri_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type := P_TCNV_REC;
1227 l_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
1228 x_tclv_tbl OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
1229 l_tcnv_tbl OKL_TRX_CONTRACTS_PUB.tcnv_tbl_type;
1230 x_tcnv_tbl OKL_TRX_CONTRACTS_PUB.tcnv_tbl_type;
1231 l_source_id_tbl OKL_REVERSAL_PUB.source_id_tbl_type;
1232 --
1233 TYPE t_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1234 l_trx_date_tbl t_date;
1235 --
1236 -- Cursor to select transaction headers for reversal
1237 CURSOR reverse_trx_csr(p_id NUMBER) IS
1238 SELECT id, date_transaction_occurred, transaction_date
1239 FROM OKL_TRX_CONTRACTS trx
1240 WHERE trx.primary_rep_trx_id = p_id
1241 AND trx.tsu_code = 'PROCESSED'
1242 AND trx.representation_type = G_SECONDARY;
1243
1244 -- Cursor to select transaction lines for reversal
1245 CURSOR reverse_txl_csr(p_tcn_id NUMBER) IS
1246 SELECT txl.id, txl.amount, txl.currency_code
1247 FROM OKL_TXL_CNTRCT_LNS txl,
1248 OKL_TRX_CONTRACTS trx
1249 WHERE txl.tcn_id = trx.id
1250 AND txl.tcn_id = p_tcn_id
1251 AND trx.tsu_code = 'PROCESSED'
1252 AND trx.representation_type = G_SECONDARY
1253 AND EXISTS (select 1 /* bug 14381856 */
1254 from okl_trns_acc_dstrs_all dst
1255 where dst.source_id = txl.id
1256 and dst.source_table = 'OKL_TXL_CNTRCT_LNS');
1257
1258 BEGIN
1259
1260 IF (G_DEBUG_ENABLED = 'Y') THEN
1261 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1262 END IF;
1263
1264
1265 l_return_status := OKL_API.START_ACTIVITY(l_api_name
1266 ,G_PKG_NAME
1267 ,p_init_msg_list
1268 ,l_api_version
1269 ,p_api_version
1270 ,'_PVT'
1271 ,l_return_status);
1272
1273 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1274 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1275 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1276 RAISE OKL_API.G_EXCEPTION_ERROR;
1277 END IF;
1278
1279 -- Open reverse trx csr for update of transaction header
1280 FOR l_reverse_trx_csr IN reverse_trx_csr(l_pri_tcnv_rec.ID)
1281 LOOP
1282 l_tcnv_tbl(l_COUNT).id := l_reverse_trx_csr.id;
1283 l_COUNT := l_COUNT+1;
1284 END LOOP;
1285
1286 l_COUNT :=0;
1287
1288 IF l_tcnv_tbl.COUNT > 0 THEN
1289 -- proceed only if records found for reversal
1290
1291 -- Build the transaction record for update
1292 FOR i IN l_tcnv_tbl.FIRST..l_tcnv_tbl.LAST
1293 LOOP
1294
1295 l_tcnv_tbl(i).tsu_code := 'CANCELED';
1296 l_tcnv_tbl(i).canceled_date := l_pri_tcnv_rec.canceled_date;
1297
1298 END LOOP;
1299
1300 l_COUNT :=0;
1301
1302 -- New code to process reversals by tcn_id .. bugs 6194225 and 6194204
1303 FOR i IN l_tcnv_tbl.FIRST..l_tcnv_tbl.LAST LOOP
1304
1305 -- Open reverse txl cursor to find out transaction line id's for reversal
1306 FOR l_reverse_txl_csr IN reverse_txl_csr(l_tcnv_tbl(i).id)
1307 LOOP
1308 l_source_id_tbl(l_COUNT) := l_reverse_txl_csr.id;
1309 l_COUNT := l_COUNT+1;
1310 END LOOP;
1311
1312 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1313 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to Okl_Reversal_Pub.REVERSE_ENTRIES');
1314 END IF;
1315
1316 /* bug 14381856: call the reversal api conditionally */
1317 if l_source_id_tbl.count > 0 then
1318 -- reverse accounting entries
1319 Okl_Reversal_Pub.REVERSE_ENTRIES(
1320 p_api_version => p_api_version,
1321 p_init_msg_list => p_init_msg_list,
1322 x_return_status => l_return_status,
1323 x_msg_count => x_msg_count,
1324 x_msg_data => x_msg_data,
1325 p_source_table => l_source_table,
1326 p_acct_date => l_tcnv_tbl(i).canceled_date, -- l_reversal_date,
1327 p_source_id_tbl => l_source_id_tbl);
1328 end if;
1329
1330 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1331 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Reversal_Pub.REVERSE_ENTRIES :'||l_return_status);
1332 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);
1333 END IF;
1334
1335 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1336 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1337 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1338 RAISE OKL_API.G_EXCEPTION_ERROR;
1339 END IF;
1340
1341 END LOOP; -- new logic for reversing by tcn_id.
1342
1343 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1344 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to Okl_Trx_Contracts_Pub.update_trx_contracts');
1345 END IF;
1346 --Call the transaction public api to update tsu_code
1347 Okl_Trx_Contracts_Pub.update_trx_contracts
1348 (p_api_version => p_api_version,
1349 p_init_msg_list => p_init_msg_list,
1350 x_return_status => l_return_status,
1351 x_msg_count => x_msg_count,
1352 x_msg_data => x_msg_data,
1353 p_tcnv_tbl => l_tcnv_tbl,
1354 x_tcnv_tbl => x_tcnv_tbl);
1355
1356 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1357 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);
1358 END IF;
1359
1360 -- store the highest degree of error
1361 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1362 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1363 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1364 RAISE OKL_API.G_EXCEPTION_ERROR;
1365 END IF;
1366
1367
1368 END IF; -- for if tcnv_tbl.count > 0 condition
1369
1370 -- set the return status
1371 x_return_status := l_return_status;
1372
1373 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1374
1375 EXCEPTION
1376 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1377 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1378 ,g_pkg_name
1379 ,'OKL_API.G_RET_STS_ERROR'
1380 ,x_msg_count
1381 ,x_msg_data
1382 ,'_PVT');
1383
1384 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1385 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1386 ,g_pkg_name
1387 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1388 ,x_msg_count
1389 ,x_msg_data
1390 ,'_PVT');
1391
1392 WHEN OTHERS THEN
1393 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1394 (l_api_name,
1395 G_PKG_NAME,
1396 'OTHERS',
1397 x_msg_count,
1398 x_msg_data,
1399 '_PVT');
1400 END REVERSE_SEC_REP_TRX;
1401
1402 PROCEDURE REVERSE_SEC_REP_TRX (
1403 P_API_VERSION IN NUMBER,
1404 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1405 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1406 X_MSG_COUNT OUT NOCOPY NUMBER,
1407 X_MSG_DATA OUT NOCOPY VARCHAR2,
1408 P_TCNV_TBL tcnv_tbl_type)
1409 IS
1410
1411 /* variables */
1412 l_api_name CONSTANT VARCHAR2(40) := 'REVERSE_SEC_REP_TRX';
1413 l_api_version CONSTANT NUMBER := 1.0;
1414 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1415
1416 BEGIN
1417
1418 l_return_status := OKL_API.START_ACTIVITY(l_api_name
1419 ,G_PKG_NAME
1420 ,p_init_msg_list
1421 ,l_api_version
1422 ,p_api_version
1423 ,'_PVT'
1424 ,l_return_status);
1425
1426 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1427 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1428 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1429 RAISE OKL_API.G_EXCEPTION_ERROR;
1430 END IF;
1431
1432 IF (p_tcnv_tbl.COUNT > 0) THEN
1433
1434 -- call recrod level implementation in loop
1435 FOR i in p_tcnv_tbl.FIRST..p_tcnv_tbl.LAST LOOP
1436 REVERSE_SEC_REP_TRX (
1437 p_api_version => p_api_version
1438 ,p_init_msg_list => p_init_msg_list
1439 ,x_return_status => l_return_status
1440 ,x_msg_count => x_msg_count
1441 ,x_msg_data => x_msg_data
1442 ,p_tcnv_rec => p_tcnv_tbl(i));
1443
1444 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1445 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1446 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1447 RAISE OKL_API.G_EXCEPTION_ERROR;
1448 END IF;
1449
1450 END LOOP;
1451
1452 END IF;
1453
1454 -- set the overall return status
1455 x_return_status := l_return_status;
1456
1457 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1458
1459 EXCEPTION
1460 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1461 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1462 ,g_pkg_name
1463 ,'OKL_API.G_RET_STS_ERROR'
1464 ,x_msg_count
1465 ,x_msg_data
1466 ,'_PVT');
1467
1468 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1469 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
1470 ,g_pkg_name
1471 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1472 ,x_msg_count
1473 ,x_msg_data
1474 ,'_PVT');
1475
1476 WHEN OTHERS THEN
1477 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1478 (l_api_name,
1479 G_PKG_NAME,
1480 'OTHERS',
1481 x_msg_count,
1482 x_msg_data,
1483 '_PVT');
1484
1485 END REVERSE_SEC_REP_TRX;
1486
1487 END OKL_MULTIGAAP_ENGINE_PVT;