[Home] [Help]
PACKAGE BODY: APPS.OKL_ACCOUNT_GENERATOR_PVT
Source
1 PACKAGE BODY OKL_ACCOUNT_GENERATOR_PVT AS
2 /* $Header: OKLRAGTB.pls 120.9 2006/07/13 12:24:10 adagur noship $ */
3
4 /*-------------------------------------------------------------------------------
5 PACKAGE LEVEL VERIABLES
6 -------------------------------------------------------------------------------*/
7
8 G_ACC_STRUCTURE_NUMBER NUMBER := OKL_ACCOUNTING_UTIL.GET_CHART_OF_ACCOUNTS_ID;
9 G_USE_DEFAULT_ACCOUNT VARCHAR2(30) := 'N';
10
11
12 /*-------------------------------------------------------------------------------
13 Procedure to get the account generator rule form okl_acc_gen_rule and
14 okl_acc_gen_rul_lns table based on set of books id and org id
15 -------------------------------------------------------------------------------*/
16
17 PROCEDURE Get_Acc_Gen_Rules(p_ae_line_type IN VARCHAR2
18 ,x_return_status OUT NOCOPY VARCHAR2
19 ,x_acc_rul_lns_tbl_type OUT NOCOPY acc_rul_lns_tbl_type)
20 AS
21
22 l_acc_rul_lns_tbl_type acc_rul_lns_tbl_type;
23 l_RowCount NUMBER := 0;
24 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
25 l_ae_line_type_meaning VARCHAR2(80);
26
27 -- Cursor which selects all the account generator rule lines for a ae line type.
28
29 -- Santonyr Bug 4134700
30 -- Changed tables to org striped views
31
32
33 CURSOR acc_gen_rule_cur IS
34 SELECT oagrl.id, oagrl.segment, oagrl.segment_number,
35 oagrl.agr_id, oagrl.source, oagrl.constants
36 FROM okl_acc_gen_rules_v oagr, okl_acc_gen_rul_lns_v oagrl
37 WHERE oagr.id = oagrl.agr_id
38 AND ae_line_type = p_ae_line_type
39 ORDER BY segment_number ASC;
40
41 acc_gen_rule_rec acc_gen_rule_cur%ROWTYPE;
42 BEGIN
43
44 -- Fetch the account generator rule lines
45
46 OPEN acc_gen_rule_cur;
47 LOOP
48 FETCH acc_gen_rule_cur INTO acc_gen_rule_rec;
49 l_RowCount := acc_gen_rule_cur%ROWCOUNT;
50 IF acc_gen_rule_cur%NOTFOUND THEN
51 IF l_RowCount = 0 THEN
52
53 -- Added by Santonyr on 18-Feb-2003. To fix the bug 2761958
54
55 l_ae_line_type_meaning := okl_accounting_util.get_lookup_meaning
56 (p_lookup_type => 'OKL_AE_LINE_TYPE',
57 p_lookup_code => p_ae_line_type);
58
59 Okl_Api.set_message(p_app_name => g_app_name,
60 p_msg_name => 'OKL_NO_RULE_LINES_SETUP',
61 p_token1 => 'AE_LINE_TYPE',
62 p_token1_value => NVL(l_ae_line_type_meaning, p_ae_line_type));
63
64 RAISE G_EXCEPTION_ERROR;
65 ELSE -- (acc_gen_rule_cur%ROWCOUNT)
66 EXIT;
67 END IF; -- (acc_gen_rule_cur%ROWCOUNT)
68 END IF; -- (acc_gen_rule_cur%NOTFOUND)
69
70 -- Populate the l_acc_rul_lns_tbl_type table type
71
72 l_acc_rul_lns_tbl_type(l_RowCount).id := acc_gen_rule_rec.id;
73 l_acc_rul_lns_tbl_type(l_RowCount).agr_id := acc_gen_rule_rec.agr_id;
74 l_acc_rul_lns_tbl_type(l_RowCount).segment := acc_gen_rule_rec.segment;
75 l_acc_rul_lns_tbl_type(l_RowCount).segment_number := acc_gen_rule_rec.segment_number;
76 l_acc_rul_lns_tbl_type(l_RowCount).constants := acc_gen_rule_rec.constants;
77 l_acc_rul_lns_tbl_type(l_RowCount).source := acc_gen_rule_rec.source;
78 END LOOP;
79 CLOSE acc_gen_rule_cur;
80
81
82 x_acc_rul_lns_tbl_type := l_acc_rul_lns_tbl_type;
83 x_return_status := l_return_status;
84
85 EXCEPTION
86 WHEN G_EXCEPTION_ERROR THEN
87 x_return_status := G_RET_STS_ERROR;
88 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
89 x_return_status := G_RET_STS_UNEXP_ERROR;
90 WHEN OTHERS THEN
91 -- store SQL error message on message stack for caller
92 x_return_status := G_RET_STS_UNEXP_ERROR;
93 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
94 p_msg_name => g_unexpected_error,
95 p_token1 => g_sqlcode_token,
96 p_token1_value => SQLCODE,
97 p_token2 => g_sqlerrm_token,
98 p_token2_value => SQLERRM);
99 END Get_Acc_Gen_Rules;
100
101
102 /*
103 -------------------------------------------------------------------------------
104 function to get the segment value if account genertor rule is based on table
105 -------------------------------------------------------------------------------
106 */
107
108 FUNCTION Get_Segment_Value(p_primary_key IN VARCHAR2
109 ,p_ae_line_type IN VARCHAR2
110 ,p_source_table IN VARCHAR2
111 ,p_segment IN VARCHAR2
112 ,x_return_status OUT NOCOPY VARCHAR2
113 )
114 RETURN VARCHAR2
115 AS
116 l_select_string VARCHAR2(4000);
117 l_primary_key_col OKL_AG_SOURCE_MAPS.PRIMARY_KEY_COLUMN%TYPE;
118 l_column_name OKL_AG_SOURCE_MAPS.SELECT_COLUMN%TYPE;
119 l_source_ccid NUMBER;
120 l_segment_value VARCHAR2(50) := NULL;
121 l_select_clause VARCHAR2(1000) := ' SELECT ';
122 l_from_clause VARCHAR2(1000) := ' FROM ';
123 l_where_clause VARCHAR2(1000) := ' WHERE ';
124 l_equal_clause VARCHAR2(10) := ' = ';
125 l_gl_table VARCHAR2(50) := ' GL_CODE_COMBINATIONS ' ;
126 l_ccid_column VARCHAR2(50) := ' CODE_COMBINATION_ID ';
127 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
128 l_primary_key_msg VARCHAR2(100);
129 l_ae_line_type_meaning VARCHAR2(80);
130 l_source_table_meaning VARCHAR2(80);
131 l_exec_mode VARCHAR2(1) := NULL;
132
133
134 -- Cursor which selects the source mapping values
135
136 CURSOR pk_cur IS
137 SELECT select_column
138 FROM OKL_AG_SOURCE_MAPS
139 WHERE ae_line_type = p_ae_line_type
140 AND source = p_source_table;
141
142 pk_rec pk_cur%ROWTYPE;
143
144 TYPE source_csr IS REF CURSOR;
145 source_rec source_csr;
146
147 BEGIN
148 -- Validate if the primary key column value is null
149 IF p_source_table IN ('FA_CATEGORY_BOOKS', 'MTL_SYSTEM_ITEMS_VL') THEN
150 IF TRIM(SUBSTR(p_primary_key, 1, 50))IS NULL OR TRIM(SUBSTR(p_primary_key, 51, 100)) IS NULL THEN
151
152 -- Commented out by Santonyr on 22-Sep-2004 to fix bug 3901209.
153 /*
154 Okl_Api.set_message(p_app_name => g_app_name,
155 p_msg_name => 'OKL_INV_SOURCE_OR_PK_IS_NULL'
156 );
157
158 */
159 G_USE_DEFAULT_ACCOUNT := 'Y';
160 l_return_status := G_RET_STS_ERROR;
161 RAISE G_EXCEPTION_ERROR;
162 END IF;
163
164 ELSE
165
166 IF p_primary_key IS NULL THEN
167
168 -- Commented out by Santonyr on 22-Sep-2004 to fix bug 3901209.
169 /*
170 Okl_Api.set_message(p_app_name => g_app_name,
171 p_msg_name => 'OKL_INV_SOURCE_OR_PK_IS_NULL'
172 );
173 */
174 G_USE_DEFAULT_ACCOUNT := 'Y';
175 l_return_status := G_RET_STS_ERROR;
176 RAISE G_EXCEPTION_ERROR;
177 END IF;
178
179 END IF;
180
181 -- Fetch the source mappings
182
183 OPEN pk_cur;
184 FETCH pk_cur INTO l_column_name;
185 IF pk_cur%NOTFOUND THEN
186
187 -- Raise error if the source mapping has not been setup.
188
189 -- Added by Santonyr on 18-Feb-2003. To fix the bug 2761958
190
191 l_ae_line_type_meaning := okl_accounting_util.get_lookup_meaning
192 (p_lookup_type => 'OKL_AE_LINE_TYPE',
193 p_lookup_code => p_ae_line_type);
194
195 l_source_table_meaning := okl_accounting_util.get_lookup_meaning
196 (p_lookup_type => 'OKL_ACC_GEN_SOURCE_TABLE',
197 p_lookup_code => p_source_table);
198
199 Okl_Api.set_message(p_app_name => g_app_name,
200 p_msg_name => 'OKL_NO_SOURCES_SETUP',
201 p_token1 => 'AE_LINE_TYPE',
202 p_token1_value => NVL(l_ae_line_type_meaning, p_ae_line_type),
203 p_token2 => 'SOURCE',
204 p_token2_value => NVL(l_source_table_meaning, p_source_table)
205 );
206 l_return_status := G_RET_STS_ERROR;
207 RAISE G_EXCEPTION_ERROR;
208 END IF;
209 CLOSE pk_cur;
210
211 -- Form the select statement to fetch the source ccid
212 IF p_source_table = 'AP_VENDOR_SITES_V' THEN
213 -- l_where_clause := l_where_clause || ' VENDOR_SITE_ID = ' || TRIM(p_primary_key) ;
214 l_where_clause := l_where_clause || ' VENDOR_SITE_ID = ' || ' TRIM(:1) ';
215 l_exec_mode := '1';
216
217
218 ELSIF p_source_table = 'AR_SITE_USES_V' THEN
219 -- l_where_clause := l_where_clause || ' SITE_USE_ID = ' || TRIM(p_primary_key) ;
220 l_where_clause := l_where_clause || ' SITE_USE_ID = ' || ' TRIM(:1) ';
221 l_exec_mode := '1';
222
223
224 ELSIF p_source_table = 'FA_CATEGORY_BOOKS' THEN
225 -- l_where_clause := l_where_clause || ' CATEGORY_ID = ' || TRIM(SUBSTR(p_primary_key, 1, 50)) ||
226 -- ' AND UPPER(BOOK_TYPE_CODE) = ''' || UPPER(TRIM(SUBSTR(p_primary_key, 51, 100))) || '''';
227 l_where_clause := l_where_clause || ' CATEGORY_ID = ' || ' TRIM(SUBSTR(:1, 1, 50)) ' ||
228 ' AND UPPER(BOOK_TYPE_CODE) = ' || ' UPPER(TRIM(SUBSTR(:1, 51, 100)))' ;
229 l_exec_mode := '2';
230
231
232 ELSIF p_source_table = 'FINANCIALS_SYSTEM_PARAMETERS' THEN
233 l_where_clause := l_where_clause || ' ORG_ID = ' || ' TRIM(:1) ' ; -- TRIM(p_primary_key) ;
234 -- l_where_clause := l_where_clause || ' 1 = 1 ' ;
235 l_exec_mode := '1';
236
237 ELSIF p_source_table = 'JTF_RS_SALESREPS_MO_V' THEN
238 -- l_where_clause := l_where_clause || ' SALESREP_ID = ' || TRIM(p_primary_key) ;
239 l_where_clause := l_where_clause || ' SALESREP_ID = ' || ' TRIM(:1) ' ;
240 l_exec_mode := '1';
241
242
243 ELSIF p_source_table = 'MTL_SYSTEM_ITEMS_VL' THEN
244 -- l_where_clause := l_where_clause || ' INVENTORY_ITEM_ID = ' || TRIM(SUBSTR(p_primary_key, 1, 50)) ||
245 -- ' AND ORGANIZATION_ID = ' || TRIM(SUBSTR(p_primary_key, 51, 100)) ;
246
247 l_where_clause := l_where_clause || ' INVENTORY_ITEM_ID = ' ||' TRIM(SUBSTR(:1, 1, 50)) ' ||
248 ' AND ORGANIZATION_ID = ' || ' TRIM(SUBSTR(:1, 51, 100)) ' ;
249 l_exec_mode := '2';
250
251
252
253 ELSIF p_source_table = 'RA_CUST_TRX_TYPES' THEN
254 -- l_where_clause := l_where_clause || ' CUST_TRX_TYPE_ID = ' || TRIM(p_primary_key) ;
255 l_where_clause := l_where_clause || ' CUST_TRX_TYPE_ID = ' || ' TRIM(:1) ';
256 l_exec_mode := '1';
257
258 ELSE
259
260 l_source_table_meaning := okl_accounting_util.get_lookup_meaning
261 (p_lookup_type => 'OKL_ACC_GEN_SOURCE_TABLE',
262 p_lookup_code => p_source_table);
263
264 Okl_Api.set_message(p_app_name => g_app_name,
265 p_msg_name => 'OKL_INVALID_SOURCE',
266 p_token1 => 'SOURCE',
267 p_token1_value => NVL(l_source_table_meaning, p_source_table)
268 );
269 RAISE G_EXCEPTION_ERROR;
270 END IF;
271
272
273 l_select_string := l_select_clause || l_column_name || l_from_clause || p_source_table || l_where_clause ;
274
275
276 -- OPEN source_rec FOR l_select_string;
277 IF l_exec_mode = '1' THEN
278 OPEN source_rec FOR l_select_string USING p_primary_key;
279 ELSIF l_exec_mode = '2' THEN
280 OPEN source_rec FOR l_select_string USING p_primary_key, p_primary_key;
281 END IF;
282
283 FETCH source_rec INTO l_source_ccid;
284 IF source_rec%NOTFOUND THEN
285 IF TRIM(SUBSTR(p_primary_key, 51)) IS NOT NULL THEN
286 l_primary_key_msg := TRIM(SUBSTR(p_primary_key, 1, 50)) || ' , ' || TRIM(SUBSTR(p_primary_key, 51));
287 ELSE
288 l_primary_key_msg := TRIM(SUBSTR(p_primary_key, 1, 50)) ;
289 END IF;
290
291
292 -- Added by Santonyr on 18-Feb-2003. To fix the bug 2761958
293
294 l_ae_line_type_meaning := okl_accounting_util.get_lookup_meaning
295 (p_lookup_type => 'OKL_AE_LINE_TYPE',
296 p_lookup_code => p_ae_line_type);
297
298 l_source_table_meaning := okl_accounting_util.get_lookup_meaning
299 (p_lookup_type => 'OKL_ACC_GEN_SOURCE_TABLE',
300 p_lookup_code => p_source_table);
301
302 Okl_Api.set_message(p_app_name => g_app_name,
303 p_msg_name => 'OKL_NO_SOURCE_EXISTS',
304 p_token1 => 'SOURCE',
305 p_token1_value => NVL(l_source_table_meaning, p_source_table),
306 p_token2 => 'PRIMARY_KEY',
307 p_token2_value => l_primary_key_msg,
308 p_token3 => 'AE_LINE_TYPE',
309 p_token3_value => NVL(l_ae_line_type_meaning, p_ae_line_type)
310 );
311 RAISE G_EXCEPTION_ERROR;
312 ELSE
313 IF l_source_ccid IS NULL THEN
314 l_primary_key_msg := TRIM(SUBSTR(p_primary_key, 1, 50)) || ' , ' || TRIM(SUBSTR(p_primary_key, 51));
315
316 -- Added by Santonyr on 18-Feb-2003. To fix the bug 2761958
317
318 l_ae_line_type_meaning := okl_accounting_util.get_lookup_meaning
319 (p_lookup_type => 'OKL_AE_LINE_TYPE',
320 p_lookup_code => p_ae_line_type);
321
322 l_source_table_meaning := okl_accounting_util.get_lookup_meaning
323 (p_lookup_type => 'OKL_ACC_GEN_SOURCE_TABLE',
324 p_lookup_code => p_source_table);
325
326
327 Okl_Api.set_message(p_app_name => g_app_name,
328 p_msg_name => 'OKL_NO_SOURCE_EXISTS',
329 p_token1 => 'SOURCE',
330 p_token1_value => NVL(l_source_table_meaning, p_source_table),
331 p_token2 => 'PRIMARY_KEY',
332 p_token2_value => l_primary_key_msg,
333 p_token3 => 'AE_LINE_TYPE',
334 p_token3_value => NVL(l_ae_line_type_meaning, p_ae_line_type)
335 );
336 RAISE G_EXCEPTION_ERROR;
337 END IF;
338 END IF;
339 CLOSE source_rec;
340
341
342 -- Form the select statement to fetch the segment value for the source ccid
343
344 --l_select_string := l_select_clause || p_segment || l_from_clause || l_gl_table ||
345 -- ' WHERE ' || l_ccid_column || l_equal_clause || l_source_ccid;
346
347 l_select_string := l_select_clause || p_segment || l_from_clause || l_gl_table ||
348 ' WHERE ' || l_ccid_column || l_equal_clause || ':1';
349
350
351 --OPEN source_rec FOR l_select_string;
352 OPEN source_rec FOR l_select_string USING l_source_ccid;
353
354 FETCH source_rec INTO l_segment_value;
355 CLOSE source_rec;
356
357 x_return_status := l_return_status;
358 RETURN l_segment_value;
359
360 EXCEPTION
361 WHEN G_EXCEPTION_ERROR THEN
362 x_return_status := G_RET_STS_ERROR;
363 RETURN l_segment_value;
364 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
365 x_return_status := G_RET_STS_UNEXP_ERROR;
366 RETURN l_segment_value;
367 WHEN OTHERS THEN
368 -- store SQL error message on message stack for caller
369 x_return_status := G_RET_STS_UNEXP_ERROR;
370 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
371 p_msg_name => g_unexpected_error,
372 p_token1 => g_sqlcode_token,
373 p_token1_value => SQLCODE,
374 p_token2 => g_sqlerrm_token,
375 p_token2_value => SQLERRM);
376 RETURN l_segment_value;
377
378 END get_segment_value;
379
380
381 /*-------------------------------------------------------------------------------
382 get concatenated segments based on rule
383 -------------------------------------------------------------------------------*/
384 FUNCTION concat_segments(p_ae_line_type IN VARCHAR2
385 ,p_set_of_books_id IN NUMBER
386 ,p_acc_rul_lns_tbl_type IN acc_rul_lns_tbl_type
387 ,p_primary_key_tbl IN primary_key_tbl
388 ,x_return_status OUT NOCOPY VARCHAR2)
389 RETURN VARCHAR2
390 AS
391 l_segment_tbl Fnd_Flex_Ext.segmentarray;
392 l_number_of_segments NUMBER;
393 l_segment_seprator VARCHAR2(1);
394 l_concat_segments VARCHAR2(1500) := NULL;
395 l_primary_key VARCHAR2(100);
396 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
397 i NUMBER;
398
399 BEGIN
400 l_number_of_segments := p_acc_rul_lns_tbl_type.COUNT;
401
402 -- get the primary key column which is used to get the source value
403
404 FOR i IN 1..l_number_of_segments LOOP
405 IF p_acc_rul_lns_tbl_type(i).source IS NOT NULL THEN
406
407
408 FOR j IN p_primary_key_tbl.first..p_primary_key_tbl.last LOOP
409 IF UPPER(p_primary_key_tbl(j).source_table) = p_acc_rul_lns_tbl_type(i).source THEN
410 l_primary_key := p_primary_key_tbl(j).primary_key_column;
411 END IF;
412 END LOOP;
413
414 -- get all the segments in an array
415
416 l_segment_tbl(i) := get_segment_value(p_primary_key => l_primary_key
417 ,p_ae_line_type => p_ae_line_type
418 ,p_source_table => p_acc_rul_lns_tbl_type(i).source
419 ,p_segment => p_acc_rul_lns_tbl_type(i).segment
420 ,x_return_status => l_return_status );
421 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
422 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
423 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
424 RAISE G_EXCEPTION_ERROR;
425 END IF;
426
427
428 ELSE
429 l_segment_tbl(i) := p_acc_rul_lns_tbl_type(i).constants;
430 END IF;
431 END LOOP;
432
433 -- Get the segments separator
434
435 l_segment_seprator := Fnd_Flex_Ext.get_delimiter(application_short_name => g_gl_app_short_name
436 ,key_flex_code => g_acc_key_flex_code
437 ,structure_number => g_acc_structure_number);
438
439 -- Get the segments concatenated
440
441 l_concat_segments := Fnd_Flex_Ext.concatenate_segments(n_segments => l_number_of_segments
442 ,segments => l_segment_tbl
443 ,delimiter => l_segment_seprator);
444
445 x_return_status := l_return_status;
446 RETURN l_concat_segments;
447 EXCEPTION
448 WHEN G_EXCEPTION_ERROR THEN
449 x_return_status := G_RET_STS_ERROR;
450 RETURN l_concat_segments;
451 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
452 x_return_status := G_RET_STS_UNEXP_ERROR;
453 RETURN l_concat_segments;
454 WHEN OTHERS THEN
455 -- store SQL error message on message stack for caller
456 x_return_status := G_RET_STS_UNEXP_ERROR;
457 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
458 p_msg_name => g_unexpected_error,
459 p_token1 => g_sqlcode_token,
460 p_token1_value => SQLCODE,
461 p_token2 => g_sqlerrm_token,
462 p_token2_value => SQLERRM);
463 RETURN l_concat_segments;
464
465 END concat_segments;
466
467 /*-------------------------------------------------------------------------------
468 validate / create / get code combination id based on concatenated segment return
469 0 if creation / validation fails.
470 -------------------------------------------------------------------------------*/
471
472 FUNCTION get_code_combination_id(p_concate_segments IN VARCHAR2,
473 x_return_status OUT NOCOPY VARCHAR2)
474 RETURN NUMBER
475 AS
476 l_ccid NUMBER := -1;
477 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
478 BEGIN
479
480 -- get the ccid
481
482 l_ccid := Fnd_Flex_Ext.get_ccid(application_short_name => g_gl_app_short_name
483 ,key_flex_code => g_acc_key_flex_code
484 ,structure_number => g_acc_structure_number
485 ,validation_date => FND_DATE.DATE_TO_CANONICAL(SYSDATE)
486 ,concatenated_segments => p_concate_segments);
487
488 x_return_status := l_return_status;
489 RETURN l_ccid;
490
491 EXCEPTION
492 WHEN G_EXCEPTION_ERROR THEN
493 x_return_status := G_RET_STS_ERROR;
494 RETURN l_ccid;
495 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
496 x_return_status := G_RET_STS_UNEXP_ERROR;
497 RETURN l_ccid;
498 WHEN OTHERS THEN
499 -- store SQL error message on message stack for caller
500 x_return_status := G_RET_STS_UNEXP_ERROR;
501 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
502 p_msg_name => g_unexpected_error,
503 p_token1 => g_sqlcode_token,
504 p_token1_value => SQLCODE,
505 p_token2 => g_sqlerrm_token,
506 p_token2_value => SQLERRM);
507 RETURN l_ccid;
508
509 END get_code_combination_id;
510
511 /*-------------------------------------------------------------------------------
512 get ccid main function to get the ccid.
513 -------------------------------------------------------------------------------*/
514
515 -- Changed by Santonyr on 22-Sep-2004 to fix bug 3901209.
516 -- Added a new parameter 'p_ae_tmpt_line_id'.
517 -- If Account Generator fails due to lack of sources, it picks up the
518 -- default account code for the passed account template line and returns.
519
520 -- Changed the signature for bug 4157521
521
522 FUNCTION GET_CCID
523 (
524 p_api_version IN NUMBER,
525 p_init_msg_list IN VARCHAR2,
526 x_return_status OUT NOCOPY VARCHAR2,
527 x_msg_count OUT NOCOPY NUMBER,
528 x_msg_data OUT NOCOPY VARCHAR2,
529 p_acc_gen_wf_sources_rec IN acc_gen_wf_sources_rec,
530 p_ae_line_type IN okl_acc_gen_rules.ae_line_type%TYPE,
531 p_primary_key_tbl IN primary_key_tbl,
532 p_ae_tmpt_line_id IN NUMBER DEFAULT NULL
533 )
534 RETURN NUMBER
535 AS
536 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
537 l_api_name CONSTANT VARCHAR2(40) := 'GENERATE_ACCOUNT';
538 l_api_version CONSTANT NUMBER := 1.0;
539 l_init_msg_list VARCHAR2(1);
540 l_msg_count NUMBER;
541 l_msg_data VARCHAR2(2000);
542 l_acc_rul_lns_tbl_type acc_rul_lns_tbl_type;
543 l_org_id NUMBER;
544 l_set_of_books_id NUMBER;
545 l_concat_segments VARCHAR2(1500);
546 l_ccid NUMBER;
547 l_acct_gen_use_workflow VARCHAR2(10);
548 l_error_msg VARCHAR2(1500);
549 l_error_text VARCHAR2(2000);
550
551
552 -- Added by Santonyr on 22-Sep-2204 to fix bug 3901209.
553 -- Cursor to get code combination id for a template line.
554
555 CURSOR atl_csr(p_atl_id NUMBER) IS
556 SELECT ID,
557 CODE_COMBINATION_ID
558 FROM OKL_AE_TMPT_LNES
559 WHERE id = p_atl_id;
560
561
562
563 BEGIN
564
565 -- Set save point
566 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
567 ,G_PKG_NAME
568 ,l_init_msg_list
569 ,l_api_version
570 ,p_api_version
571 ,'_PVT'
572 ,l_return_status);
573 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
574 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
575 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
576 RAISE G_EXCEPTION_ERROR;
577 END IF;
578
579 -- get the profile value to decide whether to use the workflow for generating the CCID
580
581 l_acct_gen_use_workflow := NVL(FND_PROFILE.VALUE ('OKL_ACCT_GEN_USE_WORKFLOW'), 'N');
582
583 -- If the l_acct_gen_use_workflow is 'No' then use the APIs to gnerate the CCID
584
585 IF l_acct_gen_use_workflow = 'N' THEN
586
587 -- Get the Org ID
588
589 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
590
591 -- Get the Set Of Books ID
592 l_set_of_books_id := Okl_Accounting_Util.get_set_of_books_id;
593
594 -- Get the Account Genartor rules setup for the accounting line type
595
596 Get_Acc_Gen_Rules(p_ae_line_type => p_ae_line_type
597 ,x_return_status => l_return_status
598 ,x_acc_rul_lns_tbl_type => l_acc_rul_lns_tbl_type);
599
600
601 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
602 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
603 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
604 RAISE G_EXCEPTION_ERROR;
605 END IF;
606
607
608 -- Get the segments concatenated
609
610 -- Changed by Santonyr on 22-Sep-2004 to fix bug 3901209.
611
612 -- Set the global variable to N before calling the API.
613
614 G_USE_DEFAULT_ACCOUNT := 'N';
615
616 l_concat_segments := concat_segments(p_ae_line_type => p_ae_line_type
617 ,p_set_of_books_id => l_set_of_books_id
618 ,p_acc_rul_lns_tbl_type => l_acc_rul_lns_tbl_type
619 ,p_primary_key_tbl => p_primary_key_tbl
620 ,x_return_status => l_return_status );
621
622 IF (l_return_status <> G_RET_STS_SUCCESS) AND
623 (G_USE_DEFAULT_ACCOUNT = 'Y') THEN
624
625 G_USE_DEFAULT_ACCOUNT := 'N';
626
627 FOR atl_rec IN atl_csr (p_ae_tmpt_line_id) LOOP
628 l_ccid := atl_rec.code_combination_id;
629 END LOOP;
630
631 IF l_ccid IS NULL THEN
632 Okl_Api.set_message(p_app_name => g_app_name,
633 p_msg_name => 'OKL_TMPT_LN_CCID_REQD' );
634 l_return_status := G_RET_STS_ERROR;
635 RAISE G_EXCEPTION_ERROR;
636 ELSE
637 x_return_status := G_RET_STS_SUCCESS;
638 Okl_Api.end_activity(x_msg_count, x_msg_data);
639 RETURN l_ccid;
640 END IF;
641
642 END IF;
643
644
645 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
646 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
647 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
648 RAISE G_EXCEPTION_ERROR;
649 END IF;
650
651 -- get the code combination id using the concatednated segmeg
652
653 l_ccid := get_code_combination_id(p_concate_segments => l_concat_segments
654 ,x_return_status => l_return_status );
655
656 IF (l_ccid = -1 OR l_ccid IS NULL ) THEN
657 Okl_Api.set_message(p_app_name => g_app_name,
658 p_msg_name => 'OKL_ERROR_GEN_CCID' );
659 l_return_status := G_RET_STS_ERROR;
660 RAISE G_EXCEPTION_ERROR;
661
662 ELSIF l_ccid = 0 THEN
663
664
665 l_error_msg := fnd_message.get;
666 IF l_error_msg IS NOT NULL THEN
667 l_error_text := l_error_msg || ' ' || l_concat_segments;
668 ELSE
669 l_error_text := l_concat_segments;
670 END IF;
671
672
673 Okl_Api.set_message(p_app_name => g_app_name,
674 p_msg_name => 'OKL_CODE_COMB_NOT_EXISTS',
675 p_token1 => 'CONCATENATED_SEGMENTS',
676 p_token1_value => l_error_text );
677 l_return_status := G_RET_STS_ERROR;
678 RAISE G_EXCEPTION_ERROR;
679 END IF;
680
681
682 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
683 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
684 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
685 RAISE G_EXCEPTION_ERROR;
686 END IF;
687
688 ELSE -- (If the l_acct_gen_use_workflow is 'Yes')
689 -- If the l_acct_gen_use_workflow is 'Yes then use the workflow process to gnerate the CCID
690
691 -- Changed the signature for bug 4157521
692
693 l_ccid := OKL_ACC_GEN_WF_PVT.start_process
694 (p_acc_gen_wf_sources_rec => p_acc_gen_wf_sources_rec,
695 p_ae_line_type => p_ae_line_type,
696 p_primary_key_tbl => p_primary_key_tbl,
697 p_ae_tmpt_line_id => p_ae_tmpt_line_id);
698
699 IF (l_ccid = -1 OR l_ccid IS NULL ) THEN
700 Okl_Api.set_message(p_app_name => g_app_name,
701 p_msg_name => 'OKL_ERROR_GEN_CCID_WF' );
702 l_return_status := G_RET_STS_ERROR;
703 RAISE G_EXCEPTION_ERROR;
704 END IF;
705
706 END IF;
707
708 x_return_status := l_return_status;
709 Okl_Api.end_activity(x_msg_count, x_msg_data);
710
711 RETURN l_ccid;
712
713
714 EXCEPTION
715 WHEN G_EXCEPTION_ERROR THEN
716 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
717 ,g_pkg_name
718 ,'OKL_API.G_RET_STS_ERROR'
719 ,x_msg_count
720 ,x_msg_data
721 ,'_PVT');
722 RETURN l_ccid;
723 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
724 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
725 ,g_pkg_name
726 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
727 ,x_msg_count
728 ,x_msg_data
729 ,'_PVT');
730 RETURN l_ccid;
731 WHEN OTHERS THEN
732 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
733 ,g_pkg_name
734 ,'OTHERS'
735 ,x_msg_count
736 ,x_msg_data
737 ,'_PVT');
738 RETURN l_ccid;
739 END get_ccid;
740
741
742 END Okl_Account_Generator_Pvt;