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