1 PACKAGE BODY gl_mc_info AS
2 /* $Header: glmcinfb.pls 120.29.12010000.3 2009/01/05 12:02:14 paragond ship $ */
3
4 -- Procedure
5 -- get_ledger_currency
6 -- Purpose
7 -- Get ledger currency code of the passed ledger ID
8 -- History
9 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
10 PROCEDURE get_ledger_currency (n_ledger_id IN NUMBER,
11 n_ledger_currency OUT NOCOPY VARCHAR2) IS
12 BEGIN
13 IF pg_ledger_currency_rec.EXISTS(n_ledger_id) THEN
14 n_ledger_currency := pg_ledger_currency_rec(n_ledger_id);
15 ELSE
16 SELECT currency_code
17 INTO n_ledger_currency
18 FROM gl_ledgers
19 WHERE ledger_id = n_ledger_id;
20
21 pg_ledger_currency_rec(n_ledger_id) := n_ledger_currency;
22 END IF;
23 EXCEPTION
24 WHEN others THEN
25 n_ledger_currency := NULL;
26 END;
27
28
29 -- Procedure
30 -- get_alc_ledger_type
31 -- Purpose
32 -- Gets ALC ledger type code of the passed ledger ID
33 -- History
34 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
35 PROCEDURE get_alc_ledger_type ( n_ledger_id IN NUMBER,
36 n_alc_ledger_type OUT NOCOPY VARCHAR2) IS
37 BEGIN
38 IF pg_alc_ledger_type_rec.EXISTS(n_ledger_id) THEN
39 n_alc_ledger_type := pg_alc_ledger_type_rec(n_ledger_id);
40 ELSE
44 WHERE ledger_id = n_ledger_id;
41 SELECT alc_ledger_type_code
42 INTO n_alc_ledger_type
43 FROM gl_ledgers
45
46 pg_alc_ledger_type_rec(n_ledger_id) := n_alc_ledger_type;
47 END IF;
48 EXCEPTION
49 WHEN others THEN
50 n_alc_ledger_type := NULL;
51 END;
52
53 -- Function
54 -- get_alc_ledger_type
55 -- Purpose
56 -- Returns ALC ledger type code of the passed ledger ID
57 -- History
58 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
59 FUNCTION get_alc_ledger_type ( n_ledger_id IN NUMBER) RETURN VARCHAR2 IS
60 l_alc_ledger_type VARCHAR2(30);
61 BEGIN
62 IF pg_alc_ledger_type_rec.EXISTS(n_ledger_id) THEN
63 RETURN pg_alc_ledger_type_rec(n_ledger_id);
64 ELSE
65 SELECT alc_ledger_type_code
66 INTO l_alc_ledger_type
67 FROM gl_ledgers
68 WHERE ledger_id = n_ledger_id;
69
70 pg_alc_ledger_type_rec(n_ledger_id) := l_alc_ledger_type;
71 RETURN l_alc_ledger_type;
72 END IF;
73 EXCEPTION
74 WHEN others THEN
75 RETURN NULL;
76 END;
77
78 -- Procedure
79 -- get_sob_type
80 -- *Should call get_alc_ledger_type() instead and this is for backward
81 -- compatible
82 -- Purpose
83 -- Gets the type of set of books
84 -- History
85 -- 21-JAN-99 Ramana Yella Created.
86 -- 12-AUG-02 MRAMANAT Fixed bug 2498090.
87 -- 25-FEB-03 LPOON R11i.X changes
88 PROCEDURE get_sob_type ( n_sob_id IN NUMBER,
89 n_sob_type OUT NOCOPY VARCHAR2) IS
90 l_alc_ledger_type VARCHAR2(30);
91 BEGIN
92
93 gl_mc_info.get_alc_ledger_type(n_sob_id, l_alc_ledger_type);
94
95 IF l_alc_ledger_type = 'SOURCE'
96 THEN
97 n_sob_type := 'P';
98 ELSIF l_alc_ledger_type = 'TARGET'
99 THEN
100 n_sob_type := 'R';
101 ELSIF l_alc_ledger_type = 'NONE'
102 THEN
103 n_sob_type := 'N';
104 ELSE
105 n_sob_type := NULL;
106 END IF;
107 END;
108
109 -- Procedure
110 -- get_ledger_category
111 -- Purpose
112 -- Gets ledger category of the passed ledger ID
113 -- History
114 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
115 PROCEDURE get_ledger_category ( n_ledger_id IN NUMBER,
116 n_ledger_category OUT NOCOPY VARCHAR2) IS
117 BEGIN
118 IF pg_ledger_category_rec.EXISTS(n_ledger_id) THEN
119 n_ledger_category := pg_ledger_category_rec(n_ledger_id);
120 ELSE
121 SELECT ledger_category_code
122 INTO n_ledger_category
123 FROM gl_ledgers
124 WHERE ledger_id = n_ledger_id;
125
126 pg_ledger_category_rec(n_ledger_id) := n_ledger_category;
127 END IF;
128 EXCEPTION
129 WHEN others THEN
130 n_ledger_category := NULL;
131 END;
132
133 -- Function
134 -- get_ledger_category
135 -- Purpose
136 -- return ledger category of the passed ledger ID
137 -- History
138 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
139 FUNCTION get_ledger_category ( n_ledger_id IN NUMBER) RETURN VARCHAR2 IS
140 l_ledger_category VARCHAR2(30);
141 BEGIN
142 IF pg_ledger_category_rec.EXISTS(n_ledger_id) THEN
143 RETURN pg_ledger_category_rec(n_ledger_id);
144 ELSE
145 SELECT ledger_category_code
146 INTO l_ledger_category
147 FROM gl_ledgers
148 WHERE ledger_id = n_ledger_id;
149
150 pg_ledger_category_rec(n_ledger_id) := l_ledger_category;
151 RETURN l_ledger_category;
152 END IF;
153 EXCEPTION
154 WHEN others THEN
155 RETURN NULL;
156 END;
157
158 -- Function
159 -- get_source_ledger_id
160 -- Purpose
161 -- Return the ALC source ledger ID of the particular ALC target ledger
162 -- per application/OU
163 -- History
164 -- 25-FEB-03 LPOON Created (New R11i.X function)
165 FUNCTION get_source_ledger_id (n_ledger_id IN NUMBER,
166 n_appl_id IN NUMBER,
167 n_org_id IN NUMBER,
168 n_fa_book_code IN VARCHAR2) RETURN NUMBER IS
169 l_src_ledger_id gl_ledgers.ledger_id%TYPE;
170 l_ledger_category gl_ledgers.ledger_category_code%TYPE;
171 BEGIN
172 l_ledger_category := gl_mc_info.get_ledger_category(n_ledger_id);
173
174 IF (l_ledger_category = 'PRIMARY')
175 THEN
176 l_src_ledger_id := n_ledger_id;
177
178 ELSIF (l_ledger_category = 'ALC')
179 THEN
180 -- In case if one ALC target ledger is attached to multiple ALC source
181 -- ledgers (which is generally not the case), we will return the first row
182 -- found.
183 SELECT source_ledger_id
184 INTO l_src_ledger_id
185 FROM GL_LEDGER_RELATIONSHIPS GLR
186 WHERE GLR.target_ledger_id = n_ledger_id
187 AND GLR.target_ledger_category_code = 'ALC'
188 AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
189 AND GLR.application_id = n_appl_id
190 AND GLR.relationship_enabled_flag = 'Y'
191 AND (n_org_id IS NULL
192 OR GLR.org_id = -99
193 OR GLR.org_id = NVL(n_org_id,-99))
194 AND (NVL(n_fa_book_code, '-99') = '-99'
195 OR EXISTS
196 (SELECT 'FA book type is enabled'
197 FROM FA_MC_BOOK_CONTROLS MC
198 WHERE MC.set_of_books_id = GLR.target_ledger_id
199 AND MC.book_type_code = n_fa_book_code
200 AND MC.primary_set_of_books_id = GLR.source_ledger_id
201 AND MC.enabled_flag = 'Y'))
202 AND rownum = 1;
206 -- In case if one Secondary ledger is attached to multiple Primary
203
204 ELSIF (l_ledger_category = 'SECONDARY')
205 THEN
207 -- ledgers (which is generally not the case), we will return the first row
208 -- found.
209 SELECT source_ledger_id
210 INTO l_src_ledger_id
211 FROM GL_LEDGER_RELATIONSHIPS GLR,
212 gl_ledgers lgr_c
213 WHERE GLR.target_ledger_id = n_ledger_id
214 AND GLR.target_ledger_category_code = 'SECONDARY'
215 AND GLR.relationship_type_code <> 'NONE'
216 AND GLR.application_id = n_appl_id
217 AND glr.target_ledger_id = lgr_c.ledger_id
218 AND nvl(lgr_c.complete_flag,'Y') = 'Y'
219 AND GLR.relationship_enabled_flag = 'Y'
220 AND (n_org_id IS NULL
221 OR GLR.org_id = -99
222 OR GLR.org_id = NVL(n_org_id,-99))
223 AND rownum = 1;
224 ELSE
225 RETURN NULL;
226
227 END IF;
228
229 RETURN(l_src_ledger_id);
230 EXCEPTION
231 WHEN OTHERS THEN
232 RETURN NULL;
233 END;
234
235 -- Function
236 -- get_source_ledger_id
237 -- Purpose
238 -- Return the ALC source ledger ID of the particular ALC target ledger
239 -- History
240 -- 25-FEB-03 LPOON Created (New R11i.X function)
241 FUNCTION get_source_ledger_id (n_ledger_id IN NUMBER) RETURN NUMBER IS
242 l_src_ledger_id gl_ledgers.ledger_id%TYPE;
243 l_ledger_category gl_ledgers.ledger_category_code%TYPE;
244 BEGIN
245 l_ledger_category := gl_mc_info.get_ledger_category(n_ledger_id);
246
247 IF (l_ledger_category = 'PRIMARY')
248 THEN
249 l_src_ledger_id := n_ledger_id;
250
251 ELSIF (l_ledger_category = 'ALC')
252 THEN
253 -- In case if one ALC target ledger is attached to multiple ALC source
254 -- ledgers (which is generally not the case), we will return the first row
255 -- found.
256 SELECT source_ledger_id
257 INTO l_src_ledger_id
258 FROM GL_LEDGER_RELATIONSHIPS GLR
259 WHERE GLR.target_ledger_id = n_ledger_id
260 AND GLR.target_ledger_category_code = 'ALC'
261 AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
262 AND GLR.relationship_enabled_flag = 'Y'
263 AND rownum = 1;
264
265 ELSIF (l_ledger_category = 'SECONDARY')
266 THEN
267 -- In case if one Secondary ledger is attached to multiple Primary
268 -- ledgers (which is generally not the case), we will return the first row
269 -- found.
270 SELECT source_ledger_id
271 INTO l_src_ledger_id
272 FROM GL_LEDGER_RELATIONSHIPS GLR,
273 GL_LEDGERS LGR_C
274 WHERE GLR.target_ledger_id = n_ledger_id
275 AND GLR.target_ledger_category_code = 'SECONDARY'
276 AND GLR.relationship_type_code <> 'NONE'
277 AND GLR.relationship_enabled_flag = 'Y'
278 AND glr.target_ledger_id = lgr_c.ledger_id
279 AND nvl(lgr_c.complete_flag,'Y') = 'Y'
280 AND rownum = 1;
281 ELSE
282 RETURN NULL;
283
284 END IF;
285
286 RETURN(l_src_ledger_id);
287 EXCEPTION
288 WHEN OTHERS THEN
289 RETURN NULL;
290 END;
291
292 -- Function
293 -- get_primary_set_of_books_id
294 -- *Should call get_source_ledger_id() instead and this is for backward
295 -- compatible
296 -- Purpose
297 -- Fetches the primary set of books ID for the reporting set of books ID
298 -- passed to the function
299 -- History
300 -- 31-JAN-01 MGOWDA Created
301 -- 25-FEB-03 LPOON R11i.X changes
302 FUNCTION get_primary_set_of_books_id (n_rsob_id IN NUMBER) RETURN NUMBER IS
303 BEGIN
304 RETURN gl_mc_info.get_source_ledger_id (n_rsob_id);
305 END;
306
307 -- Function
308 -- get_primary_ledger_id
309 -- Purpose
310 -- Return the Primary ledger ID of the particular Secondary ledger ID
311 -- per application/OU
312 -- History
313 -- 25-FEB-03 LPOON Created (New R11i.X function)
314 FUNCTION get_primary_ledger_id (n_ledger_id IN NUMBER,
315 n_appl_id IN NUMBER,
316 n_org_id IN NUMBER) RETURN NUMBER IS
317 l_pri_ledger_id gl_ledgers.ledger_id%TYPE;
318 l_ledger_category gl_ledgers.ledger_category_code%TYPE;
319 BEGIN
320 l_ledger_category := gl_mc_info.get_ledger_category(n_ledger_id);
321
322 IF (l_ledger_category = 'PRIMARY')
323 THEN
324 l_pri_ledger_id := n_ledger_id;
325
326 ELSIF (l_ledger_category = 'ALC')
327 THEN
328 -- In case if one ALC ledger is attached to multiple primary ledgers
329 -- (which is generally not the case), we will return the first row found.
330 SELECT primary_ledger_id
331 INTO l_pri_ledger_id
332 FROM GL_LEDGER_RELATIONSHIPS
333 WHERE target_ledger_id = n_ledger_id
334 AND target_ledger_category_code = 'ALC'
335 AND relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
336 AND application_id = n_appl_id
337 AND (n_org_id IS NULL OR org_id = -99 OR org_id = NVL(n_org_id, -99))
338 AND relationship_enabled_flag = 'Y' -- Should we check if it is enabled???
339 AND rownum = 1;
340
341 ELSIF (l_ledger_category = 'SECONDARY')
342 THEN
343 -- In case if one secondary ledger is attached to multiple primary ledgers
344 -- (which is generally not the case), we will return the first row found.
345 SELECT primary_ledger_id
346 INTO l_pri_ledger_id
347 FROM GL_LEDGER_RELATIONSHIPS GLR,
348 GL_LEDGERS lgr_c
349 WHERE target_ledger_id = n_ledger_id
350 AND target_ledger_category_code = 'SECONDARY'
351 AND relationship_type_code <> 'NONE'
352 AND application_id = n_appl_id
353 AND (n_org_id IS NULL OR org_id = -99 OR org_id = NVL(n_org_id, -99))
354 AND relationship_enabled_flag = 'Y' -- Should we check if it is enabled???
355 AND glr.target_ledger_id = lgr_c.ledger_id
356 AND nvl(lgr_c.complete_flag,'Y') = 'Y'
357 AND rownum = 1;
358
359 ELSE
360 RETURN NULL;
361
362 END IF;
363
364 RETURN(l_pri_ledger_id);
365 EXCEPTION
366 WHEN OTHERS THEN
367 RETURN NULL;
368 END;
369
370 -- Function
371 -- get_primary_ledger_id
372 -- Purpose
373 -- Return the Primary ledger ID of the particular Secondary ledger ID
374 -- History
375 -- 25-FEB-03 LPOON Created (New R11i.X function)
376 FUNCTION get_primary_ledger_id (n_ledger_id IN NUMBER) RETURN NUMBER IS
377 l_pri_ledger_id gl_ledgers.ledger_id%TYPE;
378 l_ledger_category gl_ledgers.ledger_category_code%TYPE;
379 BEGIN
380 l_ledger_category := gl_mc_info.get_ledger_category(n_ledger_id);
381
382 IF (l_ledger_category = 'PRIMARY')
383 THEN
384 l_pri_ledger_id := n_ledger_id;
385
386 ELSIF (l_ledger_category = 'ALC')
387 THEN
388 -- In case if one ALC ledger is attached to multiple primary ledgers
389 -- (which is generally not the case), we will return the first row found.
390 SELECT primary_ledger_id
391 INTO l_pri_ledger_id
392 FROM GL_LEDGER_RELATIONSHIPS
393 WHERE target_ledger_id = n_ledger_id
394 AND target_ledger_category_code = 'ALC'
395 AND relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
396 AND relationship_enabled_flag = 'Y' -- Should we check if it is enabled???
397 AND rownum = 1;
398
399 ELSIF (l_ledger_category = 'SECONDARY')
400 THEN
401 -- In case if one secondary ledger is attached to multiple primary ledgers
402 -- (which is generally not the case), we will return the first row found.
403 SELECT primary_ledger_id
404 INTO l_pri_ledger_id
405 FROM GL_LEDGER_RELATIONSHIPS GLR,
406 GL_LEDGERS lgr_c
407 WHERE target_ledger_id = n_ledger_id
408 AND target_ledger_category_code = 'SECONDARY'
409 AND relationship_type_code <> 'NONE'
410 AND relationship_enabled_flag = 'Y' -- Should we check if it is enabled???
411 AND glr.target_ledger_id = lgr_c.ledger_id
412 AND nvl(lgr_c.complete_flag,'Y') = 'Y'
413 AND rownum = 1;
414
415 ELSE
416 RETURN NULL;
417
418 END IF;
419
420 RETURN(l_pri_ledger_id);
421 EXCEPTION
422 WHEN OTHERS THEN
423 RETURN NULL;
424 END;
425
426 -- Function
427 -- init_ledger_le_bsv_gt
428 -- Purpose
429 -- Initialize the global temporary table, GL_LEDGER_LE_BSV_GT for a specific
430 -- ledger and its associated ALC ledgers, if any
431 -- History
432 -- 05-JUN-03 LPOON Created (New R11i.X function)
433 -- 19-FEB-04 LPOON Modified the SQL to insert rows for specific BSV opt
434 FUNCTION init_ledger_le_bsv_gt (p_ledger_id IN NUMBER) RETURN VARCHAR2 IS
435 l_ledger_category VARCHAr2(30);
436 l_bsv_option VARCHAR2(1);
437 l_bsv_vset_id NUMBER;
438
439 l_fv_table FND_FLEX_VALIDATION_TABLES.application_table_name%TYPE;
440 l_fv_col FND_FLEX_VALIDATION_TABLES.value_column_name%TYPE;
441 l_fv_type FND_FLEX_VALUE_SETS.validation_type%TYPE;
442
443 l_insertSQL DBMS_SQL.VARCHAR2S;
444 l_line_no NUMBER := 0;
445 l_cursorID INTEGER;
446 l_return_no NUMBER;
447
448 BEGIN
449 IF (p_ledger_id IS NULL)
450 THEN
451 -- Ledger ID is not passed, so return F (i.e. FAIL)
452 RETURN 'F';
453
454 END IF;
455
456 --
457 -- Initialization and verificiation variables
458 --
459
463 bal_seg_value_set_id
460 -- First, get its ledger category code and BSV option code
461 SELECT ledger_category_code,
462 NVL(bal_seg_value_option_code, 'A'),
464 INTO l_ledger_category,
465 l_bsv_option,
466 l_bsv_vset_id
467 FROM GL_LEDGERS
468 WHERE ledger_id = p_ledger_id;
469
470 IF (l_ledger_category <> 'PRIMARY'
471 AND l_ledger_category <> 'SECONDARY'
472 AND l_ledger_category <> 'ALC')
473 THEN
474 -- We don't handle NONE ledgers, which haven't been set up properly yet.
475 -- Or, invalid ledger cateogry codes of the passed ledger.
476 RETURN 'F';
477
478 END IF; -- IF (l_ledger_category <> 'PRIMARY' ...
479
480 --
481 -- Delete rows from GL_LEDGER_LE_BSV_GT for the passed ledger
482 --
483
484 -- Delete the rows for the passed ledger and its associated ALC Ledgers
485 /* DELETE FROM GL_LEDGER_LE_BSV_GT
486 WHERE ledger_id = p_ledger_id
487 OR ledger_id IN (
488 SELECT ledger_id FROM GL_ALC_LEDGER_RSHIPS_V
489 WHERE application_id = 101
490 AND source_ledger_id = p_ledger_id); */
491
492 -- Delete the all the rows from Previous Ledger contexts
493 DELETE FROM GL_LEDGER_LE_BSV_GT;
494
495 --
496 -- Insert segment values from GL_LEDGER_NORM_SEG_VALS if the BSV option is
497 -- Specific (i.e. I)
498 --
499 IF (l_bsv_option = 'I')
500 THEN
501 -- Insert rows for the passed ledger and its associated ALC Ledgers
502 INSERT INTO GL_LEDGER_LE_BSV_GT
503 (LEDGER_ID, LEDGER_NAME, LEDGER_SHORT_NAME, LEDGER_CATEGORY_CODE,
504 CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE, BAL_SEG_VALUE_SET_ID,
505 BAL_SEG_COLUMN_NAME, BAL_SEG_VALUE, LEGAL_ENTITY_ID, LEGAL_ENTITY_NAME,
506 START_DATE, END_DATE, RELATIONSHIP_ENABLED_FLAG
507 -- , SLA_SEQUENCING_FLAG
508 )
509 -- XLE uptake: Changed to get the LE name from the new XLE tables
510 SELECT lg.LEDGER_ID, lg.NAME, lg.SHORT_NAME, lg.LEDGER_CATEGORY_CODE,
511 lg.CHART_OF_ACCOUNTS_ID, lg.BAL_SEG_VALUE_OPTION_CODE,
512 lg.BAL_SEG_VALUE_SET_ID, lg.BAL_SEG_COLUMN_NAME, bsv.SEGMENT_VALUE,
513 bsv.LEGAL_ENTITY_ID, le.NAME, bsv.START_DATE,
514 bsv.END_DATE, DECODE(lg.LEDGER_CATEGORY_CODE, 'PRIMARY', 'Y', 'N')
515 -- , bsv.SLA_SEQUENCING_FLAG
516 FROM GL_LEDGERS lg
517 , GL_LEDGER_RELATIONSHIPS rs
518 , GL_LEDGER_NORM_SEG_VALS bsv
519 , XLE_ENTITY_PROFILES le
520 , GL_LEDGERS lgr_c
521 WHERE ((rs.relationship_type_code = 'NONE'
522 AND rs.target_ledger_id = p_ledger_id)
523 OR
524 (rs.target_ledger_category_code = 'ALC'
525 AND rs.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
526 AND rs.source_ledger_id = p_ledger_id))
527 AND rs.application_id = 101
528 AND lg.ledger_id = rs.target_ledger_id
529 --Bug 4887990 Avoided the merge join
530 AND bsv.ledger_id = Decode(rs.relationship_type_code,
531 'NONE',rs.target_ledger_id,
532 rs.source_ledger_id)--p_ledger_id
533 AND rs.target_ledger_id = lgr_c.ledger_id
534 AND nvl(lgr_c.complete_flag,'Y') = 'Y'
535 AND bsv.segment_type_code = 'B'
536 -- We should exclude segment values with status code = 'D' since they
537 -- will be deleted by the flatten program when config is confirmed
538 -- AND bsv.status_code IS NULL
539 AND NVL(bsv.status_code, 'I') <> 'D'
540 AND le.legal_entity_id(+) = bsv.legal_entity_id;
541
542 ELSIF (l_bsv_option = 'A')
543 THEN
544 --
545 -- Insert segment values from the balancing flex value set if the BSV option is
546 -- All (i.e. A)
547 --
548
549 -- Build INSERT statement of the dynamic INSERT SQL
550 l_line_no := l_line_no + 1;
551 l_insertSQL(l_line_no) :=
552 'INSERT INTO GL_LEDGER_LE_BSV_GT';
553 l_line_no := l_line_no + 1;
554 l_insertSQL(l_line_no) :=
555 '(LEDGER_ID, LEDGER_NAME, LEDGER_SHORT_NAME, LEDGER_CATEGORY_CODE, ';
556 l_line_no := l_line_no + 1;
557 l_insertSQL(l_line_no) :=
558 ' CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE, BAL_SEG_VALUE_SET_ID, ';
559 l_line_no := l_line_no + 1;
560 l_insertSQL(l_line_no) :=
561 ' BAL_SEG_COLUMN_NAME, BAL_SEG_VALUE, LEGAL_ENTITY_ID, LEGAL_ENTITY_NAME, ';
562 l_line_no := l_line_no + 1;
563 l_insertSQL(l_line_no) :=
564 ' START_DATE, END_DATE, RELATIONSHIP_ENABLED_FLAG) ';
565
566 -- Call the get_fv_tagble to get the flex value table name and its
567 -- flex value column name for the processed segment
568 SELECT nvl(fvt.application_table_name, 'FND_FLEX_VALUES')
569 , nvl(fvt.value_column_name, 'FLEX_VALUE')
570 , fvs.validation_type
571 INTO l_fv_table
572 , l_fv_col
573 , l_fv_type
574 FROM fnd_flex_value_sets fvs
575 , fnd_flex_validation_tables fvt
576 WHERE fvs.flex_value_set_id = l_bsv_vset_id
577 AND fvt.flex_value_set_id(+) = fvs.flex_value_set_id;
578
579 -- Build SELECT statement of the dynamic INSERT SQL
580
581 -- Columns: LEDGER_ID, LEDGER_NAME, LEDGER_SHORT_NAME, LEDGER_CATEGORY_CODE
582 l_line_no := l_line_no + 1;
583 l_insertSQL(l_line_no) :=
584 'SELECT lg.LEDGER_ID, lg.NAME, lg.SHORT_NAME, lg.LEDGER_CATEGORY_CODE, ';
585
586 -- Columns: CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE
587 l_line_no := l_line_no + 1;
588 l_insertSQL(l_line_no) :=
589 ' lg.CHART_OF_ACCOUNTS_ID, lg.BAL_SEG_VALUE_OPTION_CODE, ';
590
594 ' lg.BAL_SEG_VALUE_SET_ID, lg.BAL_SEG_COLUMN_NAME, bsv.'
591 -- Columns: BAL_SEG_VALUE_SET_ID, BAL_SEG_COLUMNE_NAME, BAL_SEG_VALUE
592 l_line_no := l_line_no + 1;
593 l_insertSQL(l_line_no) :=
595 || l_fv_col || ', ';
596
597 -- Columns: LEGAL_ENTITY_ID, LEGAL_ENTITY_NAME, START_DATE, END_DATE
598 -- Note: LE ID and Name are always NULL for ALL BSV option.
599 l_line_no := l_line_no + 1;
600 IF (l_fv_type <> 'F')
601 THEN
602 l_insertSQL(l_line_no) :=
603 ' NULL, NULL, bsv.START_DATE_ACTIVE, bsv.END_DATE_ACTIVE, ';
604 ELSE
605 l_insertSQL(l_line_no) :=
606 ' NULL, NULL, NULL, NULL, ';
607 END IF;
608
609 -- Column: RELATIONSHIP_ENABLED_FLAG
610 l_line_no := l_line_no + 1;
611 l_insertSQL(l_line_no) :=
612 ' DECODE(lg.LEDGER_CATEGORY_CODE, ''PRIMARY'', ''Y'', ''N'') ';
613
614 -- Build FROM statement of the dynamic INSERT SQL
615 l_line_no := l_line_no + 1;
616 l_insertSQL(l_line_no) :=
617 'FROM GL_LEDGERS lg, '|| l_fv_table || ' bsv ';
618
619 -- Build WHERE statement of the dynamic INSERT SQL
620 l_line_no := l_line_no + 1;
621 l_insertSQL(l_line_no) :=
622 'WHERE (lg.ledger_id = :lg_id1 ';
623 l_line_no := l_line_no + 1;
624 l_insertSQL(l_line_no) :=
625 ' OR lg.ledger_id IN ( ';
626 l_line_no := l_line_no + 1;
627 l_insertSQL(l_line_no) :=
628 ' SELECT ledger_id FROM GL_ALC_LEDGER_RSHIPS_V ';
629 l_line_no := l_line_no + 1;
630 l_insertSQL(l_line_no) :=
631 ' WHERE application_id = 101 ';
632 l_line_no := l_line_no + 1;
633 l_insertSQL(l_line_no) :=
634 ' AND source_ledger_id = :lg_id2)) ';
635
636 IF (l_fv_type <> 'F')
637 THEN
638 l_line_no := l_line_no + 1;
639 l_insertSQL(l_line_no) :=
640 'AND bsv.flex_value_set_id = lg.bal_seg_value_set_id ';
641 l_line_no := l_line_no + 1;
642 l_insertSQL(l_line_no) := 'AND bsv.summary_flag = ''N'' ';
643 END IF;
644
645 -- Open cursor
646 l_cursorID := DBMS_SQL.OPEN_CURSOR;
647 DBMS_SQL.PARSE(l_cursorID, l_insertSQL, 1, l_line_no, TRUE, dbms_sql.native);
648
649 -- Bind variables
650 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':lg_id1', p_ledger_id);
651 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':lg_id2', p_ledger_id);
652
653 -- Execute INSERT SQL
654 l_return_no := DBMS_SQL.EXECUTE(l_cursorID);
655
656 -- Close cursor
657 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
658
659 ELSE
660 -- Invalid BSV option code for the passed ledger
661 RETURN 'F';
662
663 END IF; -- IF (l_bsv_option = 'I')
664
665 -- Update RELATIONSHIP_ENABLED_FLAG to 'Y' for ALC/secondary ledgers
666 -- if they have at least one enabled ALC/secondary relationship
667 UPDATE GL_LEDGER_LE_BSV_GT gt
668 SET gt.RELATIONSHIP_ENABLED_FLAG = 'Y'
669 WHERE (gt.LEDGER_CATEGORY_CODE = 'SECONDARY'
670 AND EXISTS (
671 SELECT 'Enabled RS exists' FROM GL_SECONDARY_LEDGER_RSHIPS_V rs
672 WHERE rs.ledger_id = gt.ledger_id
673 AND rs.relationship_enabled_flag = 'Y'))
674 OR (gt.LEDGER_CATEGORY_CODE = 'ALC'
675 AND EXISTS (
676 SELECT 'Enabled RS exists' FROM GL_ALC_LEDGER_RSHIPS_V rs
677 WHERE rs.ledger_id = gt.ledger_id
678 AND rs.application_id = 101
679 AND rs.relationship_enabled_flag = 'Y'));
680
681 RETURN 'S';
682 EXCEPTION
683 WHEN OTHERS THEN
684 RETURN 'F';
685 END;
686
687 -- Function
688 -- get_le_ledgers
689 -- Purpose
690 -- Return the ledgers associatd with a specific legal entity
691 -- History
692 -- 21-MAY-03 LPOON Created (New R11i.X function)
693 FUNCTION get_le_ledgers (
694 p_legal_entity_id IN NUMBER,
695 p_get_primary_flag IN VARCHAR2,
696 p_get_secondary_flag IN VARCHAR2,
697 p_get_alc_flag IN VARCHAR2,
698 x_ledger_list IN OUT NOCOPY ledger_tbl_type) RETURN BOOLEAN IS
699 l_rec_col ledger_rec_col; -- To store the values retrieved by BULK COLLECT
700 l_num_rec NUMBER;
701 i NUMBER;
702 BEGIN
703 IF (p_legal_entity_id IS NULL)
704 THEN
705 -- Legal entity ID is not passed, so return FALSE
706 RETURN FALSE;
707
708 END IF;
709
710 SELECT LEDGER_ID
711 , LEDGER_NAME
712 , LEDGER_SHORT_NAME
713 , CURRENCY_CODE
714 , LEDGER_CATEGORY_CODE
715 BULK COLLECT INTO
716 l_rec_col.ledger_id,
717 l_rec_col.ledger_name,
718 l_rec_col.ledger_short_name,
719 l_rec_col.ledger_currency,
720 l_rec_col.ledger_category
721 FROM GL_LEDGER_LE_V
722 WHERE ledger_category_code IN (
723 DECODE(UPPER(NVL(p_get_primary_flag, 'Y')), 'Y', 'PRIMARY', 'NOT_INCLUDED'),
724 DECODE(UPPER(NVL(p_get_secondary_flag, 'N')), 'Y', 'SECONDARY', 'NOT_INCLUDED'),
725 DECODE(UPPER(NVL(p_get_alc_flag, 'N')), 'Y', 'ALC', 'NOT_INCLUDED'))
726 AND legal_entity_id = p_legal_entity_id
727 AND relationship_enabled_flag = 'Y'
728 ORDER BY DECODE(ledger_category_code, 'PRIMARY', 1, 2), ledger_id;
729
730 -- Get the number of ledgers retrieved and extend x_ledger_list (table)
731 l_num_rec := l_rec_col.ledger_id.count;
732 x_ledger_list.extend(l_num_rec);
733
734 -- Try to store all records from l_rec_col to x_ledger_list
735 FOR i IN 1..l_num_rec LOOP
736 SELECT l_rec_col.ledger_id(i),
740 l_rec_col.ledger_category(i)
737 l_rec_col.ledger_name(i),
738 l_rec_col.ledger_short_name(i),
739 l_rec_col.ledger_currency(i),
741 INTO x_ledger_list(i).ledger_id,
742 x_ledger_list(i).ledger_name,
743 x_ledger_list(i).ledger_short_name,
744 x_ledger_list(i).ledger_currency,
745 x_ledger_list(i).ledger_category
746 FROM dual;
747 END LOOP; -- FOR LOOP
748
749 RETURN TRUE;
750
751 EXCEPTION
752 WHEN OTHERS THEN
753 RETURN FALSE;
754 END;
755
756 -- Function
757 -- get_legal_entities
758 -- Purpose
759 -- Return the legal entities assigned to a specific ledger
760 -- History
761 -- 21-MAY-03 LPOON Created (New R11i.X function)
762 FUNCTION get_legal_entities (
763 p_ledger_id IN NUMBER,
764 x_le_list IN OUT NOCOPY le_bsv_tbl_type) RETURN BOOLEAN IS
765 l_rec_col le_bsv_rec_col; -- To store the values retrieved by BULK COLLECT
766 l_num_rec NUMBER;
767 i NUMBER;
768 BEGIN
769 IF (p_ledger_id IS NULL)
770 THEN
771 -- Ledger ID is not passed, so return FALSE
772 RETURN FALSE;
773
774 END IF;
775
776 SELECT legal_entity_id
777 , legal_entity_name
778 BULK COLLECT INTO
779 l_rec_col.legal_entity_id,
780 l_rec_col.legal_entity_name
781 FROM GL_LEDGER_LE_V
782 WHERE ledger_id = p_ledger_id
783 AND legal_entity_id IS NOT NULL
784 AND relationship_enabled_flag = 'Y'
785 ORDER BY legal_entity_id;
786
787 -- Get the number of legal entities retrieved and extend x_le_list (table)
788 l_num_rec := l_rec_col.legal_entity_id.count;
789 x_le_list.extend(l_num_rec);
790
791 -- Try to store all records from l_rec_col to x_le_list
792 FOR i IN 1..l_num_rec LOOP
793 SELECT l_rec_col.legal_entity_id(i),
794 l_rec_col.legal_entity_name(i)
795 INTO x_le_list(i).legal_entity_id,
796 x_le_list(i).legal_entity_name
797 FROM dual;
798 END LOOP; -- FOR LOOP
799
800 RETURN TRUE;
801
802 EXCEPTION
803 WHEN OTHERS THEN
804 RETURN FALSE;
805 END;
806
807 -- Function
808 -- get_legal_entities
809 -- Purpose
810 -- Return the legal entities assigned to a specific ledger/BSV
811 -- History
812 -- 21-MAY-03 LPOON Created (New R11i.X function)
813 FUNCTION get_legal_entities (
814 p_ledger_id IN NUMBER,
815 p_bal_seg_value IN VARCHAR2,
816 p_bsv_eff_date IN DATE,
817 x_le_list IN OUT NOCOPY le_bsv_tbl_type) RETURN BOOLEAN IS
818 l_ret_value VARCHAR2(1);
819 l_rec_col le_bsv_rec_col; -- To store the values retrieved by BULK COLLECT
820 l_bal_seg_opt VARCHAR2(1);
821 l_bsv_assigned VARCHAR2(1);
822 l_num_rec NUMBER;
823 i NUMBER;
824 BEGIN
825 -- Bug fix 3975695: Moved the codes to assign default values from
826 -- declaration to here
827 l_bsv_assigned := 'N';
828
829 IF (p_ledger_id IS NULL)
830 THEN
831 -- Ledger ID is not passed, so return FALSE
832 RETURN FALSE;
833
834 END IF;
835
836 SELECT NVL(bal_seg_value_option_code,'A')
837 INTO l_bal_seg_opt
838 FROM GL_LEDGERS
839 WHERE ledger_id = p_ledger_id;
840
841 /*
842 * Bug 6810738
843 * No need to populate temporary table if BSV option is All
844 * values
845 */
846 IF l_bal_seg_opt = 'A'
847 THEN
848 l_ret_value := 'S'; -- Just assigning success status
849 ELSE
850 l_ret_value := GL_MC_INFO.init_ledger_le_bsv_gt(p_ledger_id);
851 END IF;
852
853 IF (l_ret_value = 'S')
854 THEN
855 -- Sucessful initialization, so select BSV from the global table
856 IF (l_bal_seg_opt = 'I')
857 THEN
858 IF (p_bal_seg_value IS NOT NULL)
859 THEN
860 -- CASE 1: Specific BSV option and BSV is passed
861 -- We can get LEs assigned to this ledger/BSV from GL_LEDGER_LE_BSV_GT.
862 SELECT legal_entity_id
863 , legal_entity_name
864 BULK COLLECT INTO
865 l_rec_col.legal_entity_id,
866 l_rec_col.legal_entity_name
867 FROM GL_LEDGER_LE_BSV_GT
868 WHERE ledger_id = p_ledger_id
869 AND bal_seg_value = p_bal_seg_value
870 AND ((p_bsv_eff_date IS NULL)
871 OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
872 AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
873 AND legal_entity_id IS NOT NULL
874 ORDER BY legal_entity_id;
875
876 ELSE
877 -- CASE 2: Specific BSV option and BSV is NOT passed
878 -- We can get LEs assigned to this ledger from GL_LEDGER_LE_BSV_GT.
879 SELECT DISTINCT legal_entity_id
880 , legal_entity_name
881 BULK COLLECT INTO
882 l_rec_col.legal_entity_id,
883 l_rec_col.legal_entity_name
884 FROM GL_LEDGER_LE_BSV_GT
885 WHERE ledger_id = p_ledger_id
886 AND ((p_bsv_eff_date IS NULL)
887 OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
888 AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
889 AND legal_entity_id IS NOT NULL
890 ORDER BY legal_entity_id;
891
892 END IF; -- IF (p_bal_seg_value IS NOT NULL)
893
894 -- Get the number of legal entities retrieved and extend x_le_list (table)
898 -- Try to store all records from l_rec_col to x_le_list
895 l_num_rec := l_rec_col.legal_entity_id.count;
896 x_le_list.extend(l_num_rec);
897
899 FOR i IN 1..l_num_rec LOOP
900 SELECT l_rec_col.legal_entity_id(i),
901 l_rec_col.legal_entity_name(i)
902 INTO x_le_list(i).legal_entity_id,
903 x_le_list(i).legal_entity_name
904 FROM dual;
905 END LOOP; -- FOR LOOP
906
907 RETURN TRUE;
908
909 ELSIF (l_bal_seg_opt = 'A')
910 THEN
911 /*
912 * Commenting this code per bug 6810738
913 * when the BSV option is All values ignore if BSV is passed
914 * we assume that BSV is validated (to be a proper value in valueset)
915 * prior to calling this API
916 */
917 /******************************************************
918 ** IF (p_bal_seg_value IS NOT NULL)
919 ** THEN
920 ** -- CASE 3: All BSV option and BSV is passed
921 ** -- First, we need to check whether the passed BSV is assigned to
922 ** -- this edger.
923 ** -- => If yes, it can proceed to get LEs assigned to this ledger
924 ** -- from GL_LEDGER_LE_V
925 ** -- => If no, it will return TRUE and null LE list.
926 ** BEGIN
927 ** SELECT 'Y'
928 ** INTO l_bsv_assigned
929 ** FROM GL_LEDGER_LE_BSV_GT
930 ** WHERE ledger_id = p_ledger_id
931 ** AND bal_seg_value = p_bal_seg_value
932 ** AND ((p_bsv_eff_date IS NULL)
933 ** OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
934 ** AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)));
935 ** EXCEPTION
936 ** WHEN NO_DATA_FOUND THEN
937 ** RETURN TRUE;
938 ** END;
939 **
940 ** END IF; -- IF (p_bal_seg_value IS NOT NULL)
941 *********************************************************/
942
943 -- CASE 4: All BSV option and BSV is not passed (*Also for CASE 3)
944 -- We can call another API to get LEs assigned this ledger from
945 -- GL_LEDGER_LE_V
946 RETURN GL_MC_INFO.get_legal_entities(p_ledger_id, x_le_list);
947
948 ELSE
949 -- Invalid BSV option code
950 RETURN FALSE;
951
952 END IF; -- IF (l_bal_seg_opt = 'I')
953 END IF; -- IF (l_ret_value = 'S')
954
955 -- Fail to retrieve LE so return FALSE
956 RETURN FALSE;
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 RETURN FALSE;
961 END;
962
963 -- Function
964 -- get_bal_seg_values
965 -- Purpose
966 -- Return the balancing segment values (BSV) assigned to a specific ledger/LE
967 -- History
968 -- 21-MAY-03 LPOON Created (New R11i.X function)
969 FUNCTION get_bal_seg_values (
970 p_ledger_id IN NUMBER,
971 p_legal_entity_id IN NUMBER,
972 p_bsv_eff_date IN DATE,
973 x_allow_all_bsv_flag OUT NOCOPY VARCHAR2,
974 x_bsv_list IN OUT NOCOPY le_bsv_tbl_type) RETURN BOOLEAN IS
975 l_ledger_id NUMBER;
976 l_le_name VARCHAR2(60);
977 l_ret_value VARCHAR2(1);
978 l_le_assigned VARCHAR2(1);
979 l_rec_col le_bsv_rec_col; -- To store the values retrieved by BULK COLLECT
980 l_num_rec NUMBER;
981 i NUMBER;
982 BEGIN
983
984 -- Bug fix 3975695: Moved the codes to assign default values from
985 -- declaration to here
986 l_le_assigned := 'N';
987
988 IF (p_ledger_id IS NULL)
989 THEN
990 IF (p_legal_entity_id IS NOT NULL)
991 THEN
992 -- Bug 4006758: If the ledger ID is not passed, default to use the primary
993 -- ledger ID
994 SELECT lg.LEDGER_ID
995 INTO l_ledger_id
996 FROM GL_LEDGER_CONFIG_DETAILS cfDet,
997 GL_LEDGERS lg
998 WHERE cfDet.OBJECT_ID = p_legal_entity_id
999 AND cfDet.OBJECT_TYPE_CODE = 'LEGAL_ENTITY'
1000 AND lg.CONFIGURATION_ID = cfDet.CONFIGURATION_ID
1001 AND lg.LEDGER_CATEGORY_CODE = 'PRIMARY';
1002 ELSE
1003 -- Both p_ledger_id and p_legal_entity_id are NULL
1004 RETURN FALSE;
1005 END IF;
1006 ELSE
1007 l_ledger_id := p_ledger_id;
1008 END IF;
1009
1010 -- Check if it allows all BSV or specific BSV
1011 SELECT DECODE(bal_seg_value_option_code, 'I', 'N', 'Y')
1012 INTO x_allow_all_bsv_flag
1013 FROM GL_LEDGERS
1014 WHERE ledger_id = l_ledger_id;
1015
1016 -- Initialize GL_LEGDER_LE_BSV_GT before getting its assigned BSV
1017 l_ret_value := GL_MC_INFO.init_ledger_le_bsv_gt(l_ledger_id);
1018
1019 IF (l_ret_value = 'S')
1020 THEN
1021 -- Sucessful initialization, so select BSV from the global table
1022
1023 IF (x_allow_all_bsv_flag = 'N' AND p_legal_entity_id IS NOT NULL)
1024 THEN
1025 -- CASE 1: Specific BSV option and LE ID is passed
1026 -- We can just get BSV assigned to this ledger/LE from GL_LEDGER_LE_BSV_GT
1027 SELECT bal_seg_value
1028 , legal_entity_id
1029 , legal_entity_name
1030 BULK COLLECT INTO
1031 l_rec_col.bal_seg_value,
1032 l_rec_col.legal_entity_id,
1033 l_rec_col.legal_entity_name
1034 FROM GL_LEDGER_LE_BSV_GT
1035 WHERE ledger_id = l_ledger_id
1036 AND legal_entity_id = p_legal_entity_id
1037 AND ((p_bsv_eff_date IS NULL)
1038 OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
1042 ELSE
1039 AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
1040 ORDER BY bal_seg_value, legal_entity_id;
1041
1043 IF (x_allow_all_bsv_flag = 'Y' AND p_legal_entity_id IS NOT NULL)
1044 THEN
1045 -- CASE 2: All BSV option and LE ID is passed
1046 -- First, we need to check if the LE is assigned to this ledger.
1047 -- => If yes, it can proceed to get all BSV assigned to this ledger from
1048 -- GL_LEDGER_LE_BSV_GT.
1049 -- => If no, it will return TRUE and null BSV list
1050 BEGIN
1051 SELECT legal_entity_name
1052 INTO l_le_name
1053 FROM GL_LEDGER_LE_V
1054 WHERE ledger_id = l_ledger_id
1055 AND legal_entity_id = p_legal_entity_id
1056 AND relationship_enabled_flag = 'Y';
1057 EXCEPTION
1058 WHEN NO_DATA_FOUND THEN
1059 RETURN TRUE;
1060 END;
1061
1062 END IF; -- IF (x_allow_all_bsv_flag = 'Y' AND ...
1063
1064 -- CASE 3: LE ID is NOT passed (*Also for CASE 2)
1065 -- We can get BSV assigned to this ledger from GL_LEDGER_LE_BSV_GT
1066 SELECT bal_seg_value
1067 , NVL(legal_entity_id, p_legal_entity_id)
1068 , NVL(legal_entity_name, l_le_name)
1069 BULK COLLECT INTO
1070 l_rec_col.bal_seg_value,
1071 l_rec_col.legal_entity_id,
1072 l_rec_col.legal_entity_name
1073 FROM GL_LEDGER_LE_BSV_GT
1074 WHERE ledger_id = l_ledger_id
1075 AND ((p_bsv_eff_date IS NULL)
1076 OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
1077 AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
1078 ORDER BY bal_seg_value, legal_entity_id;
1079
1080 END IF; -- IF (x_allow_all_bsv_flag = 'N' AND ...
1081
1082 -- Get the number of BSV retrieved and extend x_bsv_list (table)
1083 l_num_rec := l_rec_col.bal_seg_value.count;
1084 x_bsv_list.extend(l_num_rec);
1085
1086 -- Try to store all records from l_rec_col to x_bsv_list
1087 FOR i IN 1..l_num_rec LOOP
1088 SELECT l_rec_col.bal_seg_value(i),
1089 l_rec_col.legal_entity_id(i),
1090 l_rec_col.legal_entity_name(i)
1091 INTO x_bsv_list(i).bal_seg_value,
1092 x_bsv_list(i).legal_entity_id,
1093 x_bsv_list(i).legal_entity_name
1094 FROM dual;
1095 END LOOP; -- FOR LOOP
1096
1097 RETURN TRUE;
1098
1099 END IF; -- IF (l_ret_value = 'S')
1100
1101 -- Fail to retrieve BSV so return FALSE
1102 RETURN FALSE;
1103
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 RETURN FALSE;
1107 END;
1108
1109 -- Function
1110 -- get_bal_seg_values
1111 -- Purpose
1112 -- Return the balancing segment values (BSV) assigned to a specific ledger
1113 -- History
1114 -- 21-MAY-03 LPOON Created (New R11i.X function)
1115 FUNCTION get_bal_seg_values (
1116 p_ledger_id IN NUMBER,
1117 p_bsv_eff_date IN DATE,
1118 x_allow_all_bsv_flag OUT NOCOPY VARCHAR2,
1119 x_bsv_list IN OUT NOCOPY le_bsv_tbl_type) RETURN BOOLEAN IS
1120 BEGIN
1121 RETURN GL_MC_INFO.get_bal_seg_values (p_ledger_id,
1122 NULL,
1123 p_bsv_eff_date,
1124 x_allow_all_bsv_flag,
1125 x_bsv_list);
1126 END;
1127
1128 -- Procedure
1129 -- set_ledger
1130 -- Purpose
1131 -- Sets the client info for the passed ledger ID
1132 -- History
1133 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
1134 PROCEDURE set_ledger (n_ledger_id IN NUMBER) IS
1135 l_char_ledger_id VARCHAR2(10);
1136 l_old_client_info VARCHAR2(64);
1137 l_new_client_info VARCHAR2(64);
1138 BEGIN
1139 l_char_ledger_id := RPAD(to_char(n_ledger_id),10);
1140
1141 dbms_application_info.read_client_info(l_old_client_info);
1142 l_old_client_info := RPAD(NVL(l_old_client_info,' '),64);
1143 l_new_client_info := substr(l_old_client_info,1,44)||l_char_ledger_id
1144 ||substr(l_old_client_info,55);
1145 dbms_application_info.set_client_info(l_new_client_info);
1146 END;
1147
1148 -- Procedure
1149 -- set_org_id
1150 -- Purpose
1151 -- Sets the client info for the passed org ID
1152 -- History
1153 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
1154 PROCEDURE set_org_id (n_org_id IN NUMBER) IS
1155 l_char_org_id VARCHAR2(10);
1156 l_old_client_info VARCHAR2(64);
1157 l_new_client_info VARCHAR2(64);
1158 BEGIN
1159 l_char_org_id := RPAD(to_char(n_org_id),10);
1160
1161 dbms_application_info.read_client_info(l_old_client_info);
1162 l_old_client_info := RPAD(NVL(l_old_client_info,' '),64);
1163 l_new_client_info := l_char_org_id || substr(l_old_client_info, 11);
1164 dbms_application_info.set_client_info(l_new_client_info);
1165 END;
1166
1167 -- Procedure
1168 -- set_rsob
1169 -- Purpose
1170 -- Sets the client info if the type of set of books is Reporting
1171 -- History
1172 -- 26-JAN-99 Ramana Yella Created
1173 -- 25-FEB-03 Li Wing Poon R11i.X changes
1174 PROCEDURE set_rsob (n_sob_id IN NUMBER) IS
1175 l_alc_ledger_type VARCHAR2(30);
1176 BEGIN
1177 /* Get the ALC ledger type */
1178 gl_mc_info.get_alc_ledger_type(n_sob_id, l_alc_ledger_type);
1179
1180 IF l_alc_ledger_type = 'TARGET' THEN
1181 /* Set client info if it is a ALC Target ledger */
1182 gl_mc_info.set_ledger(n_sob_id);
1183 END IF;
1184 END;
1185
1186 -- Procedure
1187 -- mrc_installed
1188 -- Purpose
1189 -- Determines if MRC is installed or not
1190 -- History
1191 -- 02-FEB-99 Ramana Yella Created
1192 PROCEDURE mrc_installed ( mrc_install OUT NOCOPY VARCHAR2) IS
1193
1194 BEGIN
1195 SELECT multi_currency_flag
1196 INTO mrc_install
1197 FROM fnd_product_groups
1198 WHERE product_group_id = 1;
1199 END;
1200
1201 -- Procedure
1202 -- alc_enabled
1203 -- Purpose
1204 -- Determines whether ALC is enabled
1205 -- History
1206 -- 25-FEB-03 Li Wing Poon Created (New R11i.X procedure)
1207 PROCEDURE alc_enabled ( n_ledger_id IN NUMBER,
1208 n_appl_id IN NUMBER,
1209 n_org_id IN NUMBER,
1210 n_fa_book_code IN VARCHAR2,
1211 n_alc_enabled OUT NOCOPY VARCHAR2) IS
1212 /* This procedure determines whether MRC is enabled for the
1213 particular Application,Organization and Set of books */
1214 l_alc_ledger_type VARCHAR2(30);
1215 l_count NUMBER;
1216
1217 BEGIN
1218 /* Get the type of set of books for the particular SOB_ID */
1219 gl_mc_info.get_alc_ledger_type(n_ledger_id, l_alc_ledger_type);
1220
1221 IF l_alc_ledger_type = 'SOURCE' THEN
1222 BEGIN
1223 /* It is ALC Source (i.e. ALC_LEDGER_TYPE_CODE = SOURCE) */
1224
1225 /* If the application is FA (140), check based on FA_MC_BOOK_CONTROLS;
1226 else, based on ORG_ID */
1227 SELECT count(*)
1228 INTO l_count
1229 FROM GL_LEDGER_RELATIONSHIPS GLR
1230 WHERE GLR.source_ledger_id = n_ledger_id
1231 AND GLR.target_ledger_category_code = 'ALC'
1232 AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
1233 AND GLR.application_id = n_appl_id
1234 AND GLR.relationship_enabled_flag = 'Y'
1235 AND (n_org_id IS NULL
1236 OR GLR.org_id = -99
1237 OR GLR.org_id = NVL(n_org_id,-99))
1238 AND (NVL(n_fa_book_code, '-99') = '-99'
1239 OR EXISTS
1240 (SELECT 'FA book type is enabled'
1241 FROM FA_MC_BOOK_CONTROLS MC
1242 WHERE MC.set_of_books_id = GLR.target_ledger_id
1243 AND MC.book_type_code = n_fa_book_code
1244 AND MC.primary_set_of_books_id = GLR.source_ledger_id
1245 AND MC.enabled_flag = 'Y'));
1246
1247 IF l_count >= 1 THEN
1248 n_alc_enabled := 'Y';
1249 ELSE
1250 n_alc_enabled := 'N';
1251 END IF;
1252 END;
1253 ELSIF l_alc_ledger_type = 'TARGET' THEN
1254 BEGIN
1255 /* It is ALC Target (i.e. ALC_LEDGER_TYPE_CODE = TARGET) */
1256
1257 /* If the application is FA (140), check based on FA_MC_BOOK_CONTROLS;
1258 else, based on ORG_ID */
1259 SELECT count(*)
1260 INTO l_count
1261 FROM GL_LEDGER_RELATIONSHIPS GLR
1262 WHERE GLR.target_ledger_id = n_ledger_id
1263 AND GLR.target_ledger_category_code = 'ALC'
1264 AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
1265 AND GLR.application_id = n_appl_id
1266 AND GLR.relationship_enabled_flag = 'Y'
1267 AND (n_org_id IS NULL
1268 OR GLR.org_id = -99
1269 OR GLR.org_id = NVL(n_org_id,-99))
1270 AND (NVL(n_fa_book_code, '-99') = '-99'
1271 OR EXISTS
1272 (SELECT 'FA book type is enabled'
1273 FROM FA_MC_BOOK_CONTROLS MC
1274 WHERE MC.set_of_books_id = GLR.target_ledger_id
1275 AND MC.book_type_code = n_fa_book_code
1276 AND MC.primary_set_of_books_id = GLR.source_ledger_id
1277 AND MC.enabled_flag = 'Y'));
1278
1279 IF l_count >= 1 THEN
1280 n_alc_enabled := 'Y';
1281 ELSE
1282 n_alc_enabled := 'N';
1283 END IF;
1284 END;
1285 ELSIF l_alc_ledger_type = 'NONE' THEN
1286 /* It is neither ALC Source nor Target (i.e. ALC_LEDGER_TYPE_CODE = NONE) */
1287 n_alc_enabled := 'N';
1288 END IF;
1289 EXCEPTION
1290 WHEN others THEN
1291 n_alc_enabled := NULL;
1292 END;
1293
1294 -- Function
1295 -- alc_enabled
1296 -- Purpose
1297 -- Return TRUE if ALC is enabled; else FALSE
1298 -- History
1299 -- 25-FEB-03 Li Wing Poon Created (New R11i.X procedure)
1300 FUNCTION alc_enabled (n_ledger_id IN NUMBER,
1301 n_appl_id IN NUMBER,
1302 n_org_id IN NUMBER,
1303 n_fa_book_code IN VARCHAR2) RETURN BOOLEAN IS
1304 l_alc_enabled VARCHAR2(1);
1305 BEGIN
1306 gl_mc_info.alc_enabled( n_ledger_id
1307 , n_appl_id
1308 , n_org_id
1309 , n_fa_book_code
1310 , l_alc_enabled);
1311 IF l_alc_enabled = 'Y' THEN
1312 RETURN TRUE;
1313 ELSIF l_alc_enabled = 'N' THEN
1314 RETURN FALSE;
1315 ELSE
1316 RETURN NULL;
1317 END IF;
1318 END;
1319
1320 -- Function
1321 -- alc_enabled
1322 -- Purpose
1323 -- Return TRUE if ALC is enabled; else FALSE
1324 -- History
1325 -- 02-JUN-05 Li Wing Poon Created (New R11i.X procedure)
1326 FUNCTION alc_enabled (n_appl_id IN NUMBER) RETURN BOOLEAN IS
1327 l_count NUMBER;
1328 BEGIN
1329 SELECT count(*)
1330 INTO l_count
1331 FROM GL_LEDGER_RELATIONSHIPS GLR
1332 WHERE GLR.target_ledger_category_code = 'ALC'
1333 AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
1334 AND GLR.application_id = n_appl_id
1335 AND GLR.relationship_enabled_flag = 'Y';
1336
1337 IF l_count >= 1 THEN
1338 RETURN TRUE;
1339 ELSE
1340 RETURN FALSE;
1341 END IF;
1342 END;
1343
1344 -- Procedure
1345 -- mrc_enabled
1346 -- *Should call alc_enabled() instead and this is for backward compatible
1347 -- Purpose
1348 -- Determines whether MRC is enabled for the particular
1349 -- appplication/OU/SOB
1350 -- History
1351 -- 21-JAN-99 Ramana Yella Created
1352 -- 01-FEB-99 Ramana Yella Modified the procedure
1353 -- 25-FEB-03 Li Wing Poon R11i.X changes
1354 PROCEDURE mrc_enabled ( n_sob_id IN NUMBER,
1355 n_appl_id IN NUMBER,
1356 n_org_id IN NUMBER,
1357 n_fa_book_code IN VARCHAR2,
1358 n_mrc_enabled OUT NOCOPY VARCHAR2) IS
1359 BEGIN
1360 gl_mc_info.alc_enabled( n_sob_id
1361 , n_appl_id
1362 , n_org_id
1363 , n_fa_book_code
1364 , n_mrc_enabled);
1365 END;
1366
1367 -- Procedure
1368 -- get_alc_ledger_id
1369 -- Purpose
1370 -- Fetches all ALC ledger IDs in a list of IDs
1371 -- History
1372 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
1373 PROCEDURE get_alc_ledger_id
1374 (n_src_ledger_id IN NUMBER,
1375 n_alc_id_list IN OUT NOCOPY id_arr) IS
1376 BEGIN
1377
1378 SELECT distinct g.target_ledger_id
1379 BULK COLLECT INTO n_alc_id_list
1380 FROM gl_ledger_relationships g
1381 WHERE g.source_ledger_id = n_src_ledger_id
1382 AND g.target_ledger_category_code = 'ALC'
1383 AND g.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
1384 AND g.relationship_enabled_flag = 'Y';
1385
1386 EXCEPTION
1387 WHEN others THEN
1388 NULL;
1389 END;
1390
1391 -- Procedure
1392 -- get_reporting_set_of_books_id
1393 -- *Should call get_alc_ledger_id() and this is for backward compatible
1394 -- Purpose
1395 -- Fetches all the reporting sets of books ids into eight different
1396 -- variables
1397 -- History
1398 -- 01-APR-99 MGOWDA Created (Copied from AP/AR utilities PKG)
1399 -- 25-FEB-03 LPOON R11i.X changes
1400 PROCEDURE get_reporting_set_of_books_id
1401 (n_psob_id IN NUMBER,
1402 n_rsob_id1 OUT NOCOPY NUMBER,
1403 n_rsob_id2 OUT NOCOPY NUMBER,
1404 n_rsob_id3 OUT NOCOPY NUMBER,
1405 n_rsob_id4 OUT NOCOPY NUMBER,
1406 n_rsob_id5 OUT NOCOPY NUMBER,
1407 n_rsob_id6 OUT NOCOPY NUMBER,
1408 n_rsob_id7 OUT NOCOPY NUMBER,
1409 n_rsob_id8 OUT NOCOPY NUMBER) IS
1410
1411 l_rsob_id_list id_arr;
1412 i number := 1;
1413 BEGIN
1414 gl_mc_info.get_alc_ledger_id(n_psob_id, l_rsob_id_list);
1415
1416 n_rsob_id1 := -1;
1417 n_rsob_id2 := -1;
1418 n_rsob_id3 := -1;
1419 n_rsob_id4 := -1;
1420 n_rsob_id5 := -1;
1421 n_rsob_id6 := -1;
1422 n_rsob_id7 := -1;
1423 n_rsob_id8 := -1;
1424
1425 IF (l_rsob_id_list.count >= 1) THEN
1426 n_rsob_id1 := l_rsob_id_list(1);
1427 END IF;
1428
1429 IF (l_rsob_id_list.count >= 2) THEN
1430 n_rsob_id2 := l_rsob_id_list(2);
1431 END IF;
1432
1433 IF (l_rsob_id_list.count >= 3) THEN
1434 n_rsob_id3 := l_rsob_id_list(3);
1435 END IF;
1436
1437 IF (l_rsob_id_list.count >= 4) THEN
1438 n_rsob_id4 := l_rsob_id_list(4);
1442 n_rsob_id5 := l_rsob_id_list(5);
1439 END IF;
1440
1441 IF (l_rsob_id_list.count >= 5) THEN
1443 END IF;
1444
1445 IF (l_rsob_id_list.count >= 6) THEN
1446 n_rsob_id6 := l_rsob_id_list(6);
1447 END IF;
1448
1449 IF (l_rsob_id_list.count >= 7) THEN
1450 n_rsob_id7 := l_rsob_id_list(7);
1451 END IF;
1452
1453 IF (l_rsob_id_list.count >= 8) THEN
1454 n_rsob_id8 := l_rsob_id_list(8);
1455 END IF;
1456 END;
1457
1458 -- Procedure
1459 -- get_alc_associated_ledgers
1460 -- Purpose
1461 -- Gets the ALC Source and Target ledgers info
1462 -- History
1463 -- 25-FEB-03 Li Wing Poon Created (New R11i.X procedure)
1464 PROCEDURE get_alc_associated_ledgers
1465 (n_ledger_id IN NUMBER,
1466 n_appl_id IN NUMBER,
1467 n_org_id IN NUMBER,
1468 n_fa_book_code IN VARCHAR2,
1469 n_include_source_ledger IN VARCHAR2,
1470 n_ledger_list IN OUT NOCOPY r_sob_list) IS
1471 l_ledger_rec_col r_sob_rec_col; /* To store the values retrieved by BULK COLLECT */
1472 l_alc_ledger_type VARCHAR2(30);
1473 l_alc_enabled VARCHAR2(1);
1474 l_src_ledger_id NUMBER; /* Variable to store source ledger ID */
1475 l_num_rec NUMBER;
1476 i NUMBER;
1477 BEGIN
1478
1479 /* Get the ALC ledger type of the passed ledger ID */
1480 gl_mc_info.get_alc_ledger_type(n_ledger_id, l_alc_ledger_type);
1481
1482 IF l_alc_ledger_type = 'NONE' THEN
1483 /* If its ALC ledger type is 'NONE', return the ledger list as NULL */
1484 n_ledger_list.extend;
1485 ELSE
1486 /* Otherwise, check if ALC is enabled for the passed application/OU/Ledger */
1487 gl_mc_info.alc_enabled( n_ledger_id
1488 , n_appl_id
1489 , n_org_id
1490 , n_fa_book_code
1491 , l_alc_enabled);
1492
1493 IF l_alc_enabled = 'N' THEN
1494 /* If ALC is not enabled, return the ledger list as NULL */
1495 n_ledger_list.extend;
1496 ELSE
1497 /* If ALC is enabled, get the ledger info based on its ALC ledger type:
1498 - If its ALC ledger type is 'SOURCE', get the ledger info for the passed
1499 source ledger and its associated enabled ALC target ledgers, if any.
1500 - If its ALC ledger type is 'TARGET', get its source ledger ID which is
1501 enabled for the passed target ledger and get all its associated enabled
1502 ALC target ledgers */
1503 IF l_alc_ledger_type = 'SOURCE' THEN
1504 l_src_ledger_id := n_ledger_id;
1505 ELSIF l_alc_ledger_type = 'TARGET' THEN
1506 /* Get source ledger ID of the passed ALC target ledger/application/OU */
1507 l_src_ledger_id := gl_mc_info.get_source_ledger_id( n_ledger_id
1508 , n_appl_id
1509 , n_org_id
1510 , n_fa_book_code);
1511 END IF; -- IF l_alc_ledger_type = 'SOURCE' THEN
1512
1513 /* Get the source and target ledgers info, if any and store them into
1514 l_ledger_rec_col by using BULK COLLECT. We cannot store them directly
1515 into n_ledger_list since it is table of records (composite) while BULK
1516 COLLECT can just apply to table of scalar type. */
1517 SELECT g.ledger_id,
1518 g.name,
1519 g.short_name,
1520 g.currency_code,
1521 g.alc_ledger_type_code,
1522 g.ledger_category_code,
1523 g.sla_accounting_method_code,
1524 f.precision,
1525 f.minimum_accountable_unit,
1526 DECODE(g.alc_ledger_type_code, 'SOURCE', 'P'
1527 , 'TARGET', 'R'
1528 , 'N'),
1529 DECODE(g.alc_ledger_type_code, 'SOURCE', 'Primary'
1530 , 'TARGET', 'Reporting'
1531 , 'Notassigned')
1532 BULK COLLECT INTO
1533 l_ledger_rec_col.r_sob_id,
1534 l_ledger_rec_col.r_sob_name,
1535 l_ledger_rec_col.r_sob_short_name,
1536 l_ledger_rec_col.r_sob_curr,
1537 l_ledger_rec_col.r_alc_type,
1538 l_ledger_rec_col.r_category,
1539 l_ledger_rec_col.r_acct_method_code,
1540 l_ledger_rec_col.r_precision,
1541 l_ledger_rec_col.r_mau,
1542 l_ledger_rec_col.r_sob_type,
1543 l_ledger_rec_col.r_sob_user_type
1544 FROM gl_ledgers g,
1545 fnd_currencies f
1546 -- Include ALC source ledger if n_include_source_ledger = 'Y' or NULL
1547 WHERE ((upper(NVL(n_include_source_ledger, 'Y')) = 'Y'
1548 AND g.ledger_id = l_src_ledger_id)
1549 OR g.ledger_id IN (
1550 SELECT glr.target_ledger_id -- ALC target ledgers
1551 FROM gl_ledger_relationships glr
1552 WHERE glr.source_ledger_id = l_src_ledger_id
1553 AND glr.target_ledger_category_code = 'ALC'
1554 AND glr.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
1555 AND glr.application_id = n_appl_id
1556 AND (n_org_id IS NULL
1557 OR glr.org_id = -99
1558 OR glr.org_id = NVL(n_org_id,-99))
1559 AND (NVL(n_fa_book_code, '-99') = '-99'
1560 OR EXISTS
1561 (SELECT 'FA book type is enabled'
1562 FROM FA_MC_BOOK_CONTROLS MC
1563 WHERE MC.set_of_books_id = glr.target_ledger_id
1564 AND MC.book_type_code = n_fa_book_code
1565 AND MC.primary_set_of_books_id = glr.source_ledger_id
1566 AND MC.enabled_flag = 'Y'))
1567 AND glr.relationship_enabled_flag = 'Y'))
1568 AND g.currency_code = f.currency_code
1569 ORDER BY DECODE(g.ledger_category_code
1570 , 'Primary' , 1
1571 , 'Secondary', 2
1572 , 3)
1573 , g.ledger_id;
1574
1575 /* Get the number of records retrieved and extend the
1576 n_ledger_list (table) - allocate spaces */
1577 l_num_rec := l_ledger_rec_col.r_sob_id.count;
1578 n_ledger_list.extend(l_num_rec);
1579
1580 /* Try to store all records from l_ledger_rec_col to n_ledger_list */
1581 FOR i IN 1..l_num_rec LOOP
1582 SELECT l_ledger_rec_col.r_sob_id(i),
1583 l_ledger_rec_col.r_sob_name(i),
1584 l_ledger_rec_col.r_sob_short_name(i),
1585 l_ledger_rec_col.r_sob_curr(i),
1586 l_ledger_rec_col.r_alc_type(i),
1587 l_ledger_rec_col.r_category(i),
1588 l_ledger_rec_col.r_acct_method_code(i),
1589 l_ledger_rec_col.r_precision(i),
1590 l_ledger_rec_col.r_mau(i),
1591 l_ledger_rec_col.r_sob_type(i),
1592 l_ledger_rec_col.r_sob_user_type(i)
1593 INTO n_ledger_list(i).r_sob_id,
1594 n_ledger_list(i).r_sob_name,
1595 n_ledger_list(i).r_sob_short_name,
1596 n_ledger_list(i).r_sob_curr,
1597 n_ledger_list(i).r_alc_type,
1598 n_ledger_list(i).r_category,
1599 n_ledger_list(i).r_acct_method_code,
1600 n_ledger_list(i).r_precision,
1601 n_ledger_list(i).r_mau,
1602 n_ledger_list(i).r_sob_type,
1603 n_ledger_list(i).r_sob_user_type
1604 FROM dual;
1605
1606 END LOOP; /* FOR LOOP */
1607 END IF; -- IF l_alc_enabled = 'N' THEN
1608 END IF; -- IF l_alc_ledger_type = 'NONE'
1609 END;
1610
1611 -- Procedure
1612 -- get_associated_sobs
1613 -- *Should call get_alc_associated_ledgers() instead and this is for backward
1614 -- compatible
1615 -- Purpose
1616 -- Gets the Primary and Reporting set of books info
1617 -- History
1618 -- 21-JAN-99 Ramana Yella Created
1619 -- 01-FEB-99 Ramana Yella Modified the procedure
1620 -- 25-JUN-99 Li Wing Poon Modified to use BULK COLLECT
1621 -- 07-MAR-00 Li Wing Poon Fixed bug 1229907
1622 -- 25-FEB-03 Li Wing Poon R11i.X Changes
1623 PROCEDURE get_associated_sobs ( n_sob_id IN NUMBER,
1624 n_appl_id IN NUMBER,
1625 n_org_id IN NUMBER,
1626 n_fa_book_code IN VARCHAR2,
1627 n_sob_list IN OUT NOCOPY r_sob_list) IS
1628 BEGIN
1629 -- This procedure used to include primary SOB, so we put 'Y' to include
1630 -- ALC source ledger
1631 gl_mc_info.get_alc_associated_ledgers( n_sob_id
1632 , n_appl_id
1633 , n_org_id
1634 , n_fa_book_code
1635 , 'Y'
1636 , n_sob_list);
1637 END;
1638
1639 -- Procedure
1640 -- get_alc_associated_ledgers_scalar
1641 -- Purpose
1642 -- Gets the ALC source and target ledgers info
1643 -- History
1644 -- 25-FEB-03 LPOON Created (New R11i.X procedure)
1645 PROCEDURE get_alc_ledgers_scalar
1646 (n_ledger_id IN NUMBER,
1647 n_appl_id IN NUMBER,
1648 n_org_id IN NUMBER,
1649 n_fa_book_code IN VARCHAR2,
1650 n_ledger_id_1 OUT NOCOPY NUMBER,
1651 n_ledger_name_1 OUT NOCOPY VARCHAR2,
1652 n_alc_ledger_type_1 OUT NOCOPY VARCHAR2,
1653 n_ledger_currency_1 OUT NOCOPY VARCHAR2,
1654 n_ledger_category_1 OUT NOCOPY VARCHAR2,
1655 n_ledger_short_name_1 OUT NOCOPY VARCHAR2,
1656 n_acct_method_code_1 OUT NOCOPY VARCHAR2,
1657 n_ledger_id_2 OUT NOCOPY NUMBER,
1658 n_ledger_name_2 OUT NOCOPY VARCHAR2,
1659 n_alc_ledger_type_2 OUT NOCOPY VARCHAR2,
1660 n_ledger_currency_2 OUT NOCOPY VARCHAR2,
1661 n_ledger_category_2 OUT NOCOPY VARCHAR2,
1662 n_ledger_short_name_2 OUT NOCOPY VARCHAR2,
1663 n_acct_method_code_2 OUT NOCOPY VARCHAR2,
1664 n_ledger_id_3 OUT NOCOPY NUMBER,
1665 n_ledger_name_3 OUT NOCOPY VARCHAR2,
1666 n_alc_ledger_type_3 OUT NOCOPY VARCHAR2,
1667 n_ledger_currency_3 OUT NOCOPY VARCHAR2,
1668 n_ledger_category_3 OUT NOCOPY VARCHAR2,
1669 n_ledger_short_name_3 OUT NOCOPY VARCHAR2,
1670 n_acct_method_code_3 OUT NOCOPY VARCHAR2,
1671 n_ledger_id_4 OUT NOCOPY NUMBER,
1672 n_ledger_name_4 OUT NOCOPY VARCHAR2,
1673 n_alc_ledger_type_4 OUT NOCOPY VARCHAR2,
1674 n_ledger_currency_4 OUT NOCOPY VARCHAR2,
1675 n_ledger_category_4 OUT NOCOPY VARCHAR2,
1676 n_ledger_short_name_4 OUT NOCOPY VARCHAR2,
1677 n_acct_method_code_4 OUT NOCOPY VARCHAR2,
1678 n_ledger_id_5 OUT NOCOPY NUMBER,
1679 n_ledger_name_5 OUT NOCOPY VARCHAR2,
1680 n_alc_ledger_type_5 OUT NOCOPY VARCHAR2,
1681 n_ledger_currency_5 OUT NOCOPY VARCHAR2,
1682 n_ledger_category_5 OUT NOCOPY VARCHAR2,
1683 n_ledger_short_name_5 OUT NOCOPY VARCHAR2,
1684 n_acct_method_code_5 OUT NOCOPY VARCHAR2,
1685 n_ledger_id_6 OUT NOCOPY NUMBER,
1686 n_ledger_name_6 OUT NOCOPY VARCHAR2,
1687 n_alc_ledger_type_6 OUT NOCOPY VARCHAR2,
1688 n_ledger_currency_6 OUT NOCOPY VARCHAR2,
1689 n_ledger_category_6 OUT NOCOPY VARCHAR2,
1690 n_ledger_short_name_6 OUT NOCOPY VARCHAR2,
1691 n_acct_method_code_6 OUT NOCOPY VARCHAR2,
1692 n_ledger_id_7 OUT NOCOPY NUMBER,
1693 n_ledger_name_7 OUT NOCOPY VARCHAR2,
1694 n_alc_ledger_type_7 OUT NOCOPY VARCHAR2,
1695 n_ledger_currency_7 OUT NOCOPY VARCHAR2,
1696 n_ledger_category_7 OUT NOCOPY VARCHAR2,
1697 n_ledger_short_name_7 OUT NOCOPY VARCHAR2,
1698 n_acct_method_code_7 OUT NOCOPY VARCHAR2,
1699 n_ledger_id_8 OUT NOCOPY NUMBER,
1700 n_ledger_name_8 OUT NOCOPY VARCHAR2,
1701 n_alc_ledger_type_8 OUT NOCOPY VARCHAR2,
1702 n_ledger_currency_8 OUT NOCOPY VARCHAR2,
1703 n_ledger_category_8 OUT NOCOPY VARCHAR2,
1704 n_ledger_short_name_8 OUT NOCOPY VARCHAR2,
1705 n_acct_method_code_8 OUT NOCOPY VARCHAR2) IS
1706 l_ledger_list gl_mc_info.r_sob_list := gl_mc_info.r_sob_list();
1707 l_ledger_list_count NUMBER;
1708 BEGIN
1709 gl_mc_info.get_alc_associated_ledgers( n_ledger_id
1710 , n_appl_id
1711 , n_org_id
1712 , n_fa_book_code
1713 , 'Y' -- Include ALC source ledger
1714 , l_ledger_list);
1715
1716 l_ledger_list_count := l_ledger_list.COUNT;
1717
1718 IF (l_ledger_list_count >= 1) THEN
1719 n_ledger_id_1 := l_ledger_list(1).r_sob_id;
1720 n_ledger_name_1 := l_ledger_list(1).r_sob_name;
1721 n_ledger_short_name_1 := l_ledger_list(1).r_sob_short_name;
1722 n_ledger_currency_1 := l_ledger_list(1).r_sob_curr;
1723 n_alc_ledger_type_1 := l_ledger_list(1).r_alc_type;
1724 n_ledger_category_1 := l_ledger_list(1).r_category;
1725 n_acct_method_code_1 := l_ledger_list(1).r_acct_method_code;
1726 END IF;
1727
1728 IF (l_ledger_list_count >= 2) THEN
1729 n_ledger_id_2 := l_ledger_list(2).r_sob_id;
1730 n_ledger_name_2 := l_ledger_list(2).r_sob_name;
1731 n_ledger_short_name_2 := l_ledger_list(2).r_sob_short_name;
1732 n_ledger_currency_2 := l_ledger_list(2).r_sob_curr;
1733 n_alc_ledger_type_2 := l_ledger_list(2).r_alc_type;
1734 n_ledger_category_2 := l_ledger_list(2).r_category;
1735 n_acct_method_code_2 := l_ledger_list(1).r_acct_method_code;
1736 END IF;
1737
1738 IF (l_ledger_list_count >= 3) THEN
1739 n_ledger_id_3 := l_ledger_list(3).r_sob_id;
1740 n_ledger_name_3 := l_ledger_list(3).r_sob_name;
1741 n_ledger_short_name_3 := l_ledger_list(3).r_sob_short_name;
1742 n_ledger_currency_3 := l_ledger_list(3).r_sob_curr;
1743 n_alc_ledger_type_3 := l_ledger_list(3).r_alc_type;
1744 n_ledger_category_3 := l_ledger_list(3).r_category;
1745 n_acct_method_code_3 := l_ledger_list(1).r_acct_method_code;
1746 END IF;
1747
1748 IF (l_ledger_list_count >= 4) THEN
1749 n_ledger_id_4 := l_ledger_list(4).r_sob_id;
1750 n_ledger_name_4 := l_ledger_list(4).r_sob_name;
1751 n_ledger_short_name_4 := l_ledger_list(4).r_sob_short_name;
1752 n_ledger_currency_4 := l_ledger_list(4).r_sob_curr;
1753 n_alc_ledger_type_4 := l_ledger_list(4).r_alc_type;
1754 n_ledger_category_4 := l_ledger_list(4).r_category;
1755 n_acct_method_code_4 := l_ledger_list(1).r_acct_method_code;
1756 END IF;
1757
1758 IF (l_ledger_list_count >= 5) THEN
1759 n_ledger_id_5 := l_ledger_list(5).r_sob_id;
1760 n_ledger_name_5 := l_ledger_list(5).r_sob_name;
1761 n_ledger_short_name_5 := l_ledger_list(5).r_sob_short_name;
1762 n_ledger_currency_5 := l_ledger_list(5).r_sob_curr;
1763 n_alc_ledger_type_5 := l_ledger_list(5).r_alc_type;
1764 n_ledger_category_5 := l_ledger_list(5).r_category;
1765 n_acct_method_code_5 := l_ledger_list(1).r_acct_method_code;
1766 END IF;
1767
1768 IF (l_ledger_list_count >= 6) THEN
1769 n_ledger_id_6 := l_ledger_list(6).r_sob_id;
1770 n_ledger_name_6 := l_ledger_list(6).r_sob_name;
1771 n_ledger_short_name_6 := l_ledger_list(6).r_sob_short_name;
1772 n_ledger_currency_6 := l_ledger_list(6).r_sob_curr;
1773 n_alc_ledger_type_6 := l_ledger_list(6).r_alc_type;
1774 n_ledger_category_6 := l_ledger_list(6).r_category;
1775 n_acct_method_code_6 := l_ledger_list(1).r_acct_method_code;
1776 END IF;
1777
1778 IF (l_ledger_list_count >= 7) THEN
1779 n_ledger_id_7 := l_ledger_list(7).r_sob_id;
1780 n_ledger_name_7 := l_ledger_list(7).r_sob_name;
1781 n_ledger_short_name_7 := l_ledger_list(7).r_sob_short_name;
1782 n_ledger_currency_7 := l_ledger_list(7).r_sob_curr;
1783 n_alc_ledger_type_7 := l_ledger_list(7).r_alc_type;
1784 n_ledger_category_7 := l_ledger_list(7).r_category;
1785 n_acct_method_code_7 := l_ledger_list(1).r_acct_method_code;
1786 END IF;
1787
1788 IF (l_ledger_list_count >= 8) THEN
1789 n_ledger_id_8 := l_ledger_list(8).r_sob_id;
1790 n_ledger_name_8 := l_ledger_list(8).r_sob_name;
1791 n_ledger_short_name_8 := l_ledger_list(8).r_sob_short_name;
1792 n_ledger_currency_8 := l_ledger_list(8).r_sob_curr;
1793 n_alc_ledger_type_8 := l_ledger_list(8).r_alc_type;
1794 n_ledger_category_8 := l_ledger_list(8).r_category;
1795 n_acct_method_code_8 := l_ledger_list(1).r_acct_method_code;
1796 END IF;
1797 END;
1798
1799 -- Procedure
1800 -- get_associated_sobs_scalar
1801 -- *Should call get_alc_associated_ledgers_scalar() instead and this is for
1802 -- backward compatible
1803 -- Purpose
1804 -- Gets the Primary and Reporting set of books info
1805 -- History
1806 -- 02-AUG-99 SSIVASUB Created
1807 -- 25-FEB-03 LPOON R11i.X changes
1808 PROCEDURE get_associated_sobs_scalar
1809 (p_sob_id IN NUMBER,
1810 p_appl_id IN NUMBER,
1811 p_org_id IN NUMBER,
1812 p_fa_book_code IN VARCHAR2,
1813 p_sob_id_1 OUT NOCOPY NUMBER,
1814 p_sob_name_1 OUT NOCOPY VARCHAR2,
1815 p_sob_type_1 OUT NOCOPY VARCHAR2,
1816 p_sob_curr_1 OUT NOCOPY VARCHAR2,
1817 p_sob_user_type_1 OUT NOCOPY VARCHAR2,
1818 p_sob_short_name_1 OUT NOCOPY VARCHAR2,
1819 p_sob_id_2 OUT NOCOPY NUMBER,
1823 p_sob_user_type_2 OUT NOCOPY VARCHAR2,
1820 p_sob_name_2 OUT NOCOPY VARCHAR2,
1821 p_sob_type_2 OUT NOCOPY VARCHAR2,
1822 p_sob_curr_2 OUT NOCOPY VARCHAR2,
1824 p_sob_short_name_2 OUT NOCOPY VARCHAR2,
1825 p_sob_id_3 OUT NOCOPY NUMBER,
1826 p_sob_name_3 OUT NOCOPY VARCHAR2,
1827 p_sob_type_3 OUT NOCOPY VARCHAR2,
1828 p_sob_curr_3 OUT NOCOPY VARCHAR2,
1829 p_sob_user_type_3 OUT NOCOPY VARCHAR2,
1830 p_sob_short_name_3 OUT NOCOPY VARCHAR2,
1831 p_sob_id_4 OUT NOCOPY NUMBER,
1832 p_sob_name_4 OUT NOCOPY VARCHAR2,
1833 p_sob_type_4 OUT NOCOPY VARCHAR2,
1834 p_sob_curr_4 OUT NOCOPY VARCHAR2,
1835 p_sob_user_type_4 OUT NOCOPY VARCHAR2,
1836 p_sob_short_name_4 OUT NOCOPY VARCHAR2,
1837 p_sob_id_5 OUT NOCOPY NUMBER,
1838 p_sob_name_5 OUT NOCOPY VARCHAR2,
1839 p_sob_type_5 OUT NOCOPY VARCHAR2,
1840 p_sob_curr_5 OUT NOCOPY VARCHAR2,
1841 p_sob_user_type_5 OUT NOCOPY VARCHAR2,
1842 p_sob_short_name_5 OUT NOCOPY VARCHAR2,
1843 p_sob_id_6 OUT NOCOPY NUMBER,
1844 p_sob_name_6 OUT NOCOPY VARCHAR2,
1845 p_sob_type_6 OUT NOCOPY VARCHAR2,
1846 p_sob_curr_6 OUT NOCOPY VARCHAR2,
1847 p_sob_user_type_6 OUT NOCOPY VARCHAR2,
1848 p_sob_short_name_6 OUT NOCOPY VARCHAR2,
1849 p_sob_id_7 OUT NOCOPY NUMBER,
1850 p_sob_name_7 OUT NOCOPY VARCHAR2,
1851 p_sob_type_7 OUT NOCOPY VARCHAR2,
1852 p_sob_curr_7 OUT NOCOPY VARCHAR2,
1853 p_sob_user_type_7 OUT NOCOPY VARCHAR2,
1854 p_sob_short_name_7 OUT NOCOPY VARCHAR2,
1855 p_sob_id_8 OUT NOCOPY NUMBER,
1856 p_sob_name_8 OUT NOCOPY VARCHAR2,
1857 p_sob_type_8 OUT NOCOPY VARCHAR2,
1858 p_sob_curr_8 OUT NOCOPY VARCHAR2,
1859 p_sob_user_type_8 OUT NOCOPY VARCHAR2,
1860 p_sob_short_name_8 OUT NOCOPY VARCHAR2) IS
1861 l_sob_list gl_mc_info.r_sob_list := gl_mc_info.r_sob_list();
1862 l_sob_list_count NUMBER;
1863 BEGIN
1864 -- This procedure used to include primary SOB, so we put 'Y' to include
1865 -- ALC source ledger
1866 gl_mc_info.get_alc_associated_ledgers( p_sob_id
1867 , p_appl_id
1868 , p_org_id
1869 , p_fa_book_code
1870 , 'Y'
1871 , l_sob_list);
1872
1873 l_sob_list_count := l_sob_list.COUNT;
1874
1875 IF (l_sob_list_count >= 1) THEN
1876 p_sob_id_1 := l_sob_list(1).r_sob_id;
1877 p_sob_name_1 := l_sob_list(1).r_sob_name;
1878 p_sob_type_1 := l_sob_list(1).r_sob_type;
1879 p_sob_curr_1 := l_sob_list(1).r_sob_curr;
1880 p_sob_user_type_1 := l_sob_list(1).r_sob_user_type;
1881 p_sob_short_name_1 := l_sob_list(1).r_sob_short_name;
1882 END IF;
1883
1884 IF (l_sob_list_count >= 2) THEN
1885 p_sob_id_2 := l_sob_list(2).r_sob_id;
1886 p_sob_name_2 := l_sob_list(2).r_sob_name;
1887 p_sob_type_2 := l_sob_list(2).r_sob_type;
1888 p_sob_curr_2 := l_sob_list(2).r_sob_curr;
1889 p_sob_user_type_2 := l_sob_list(2).r_sob_user_type;
1890 p_sob_short_name_2 := l_sob_list(2).r_sob_short_name;
1891 END IF;
1892
1893 IF (l_sob_list_count >= 3) THEN
1894 p_sob_id_3 := l_sob_list(3).r_sob_id;
1895 p_sob_name_3 := l_sob_list(3).r_sob_name;
1896 p_sob_type_3 := l_sob_list(3).r_sob_type;
1897 p_sob_curr_3 := l_sob_list(3).r_sob_curr;
1898 p_sob_user_type_3 := l_sob_list(3).r_sob_user_type;
1899 p_sob_short_name_3 := l_sob_list(3).r_sob_short_name;
1900 END IF;
1901
1902 IF (l_sob_list_count >= 4) THEN
1903 p_sob_id_4 := l_sob_list(4).r_sob_id;
1904 p_sob_name_4 := l_sob_list(4).r_sob_name;
1905 p_sob_type_4 := l_sob_list(4).r_sob_type;
1906 p_sob_curr_4 := l_sob_list(4).r_sob_curr;
1907 p_sob_user_type_4 := l_sob_list(4).r_sob_user_type;
1908 p_sob_short_name_4 := l_sob_list(4).r_sob_short_name;
1909 END IF;
1910
1911 IF (l_sob_list_count >= 5) THEN
1912 p_sob_id_5 := l_sob_list(5).r_sob_id;
1913 p_sob_name_5 := l_sob_list(5).r_sob_name;
1914 p_sob_type_5 := l_sob_list(5).r_sob_type;
1915 p_sob_curr_5 := l_sob_list(5).r_sob_curr;
1916 p_sob_user_type_5 := l_sob_list(5).r_sob_user_type;
1917 p_sob_short_name_5 := l_sob_list(5).r_sob_short_name;
1918 END IF;
1919
1920 IF (l_sob_list_count >= 6) THEN
1921 p_sob_id_6 := l_sob_list(6).r_sob_id;
1922 p_sob_name_6 := l_sob_list(6).r_sob_name;
1923 p_sob_type_6 := l_sob_list(6).r_sob_type;
1924 p_sob_curr_6 := l_sob_list(6).r_sob_curr;
1925 p_sob_user_type_6 := l_sob_list(6).r_sob_user_type;
1926 p_sob_short_name_6 := l_sob_list(6).r_sob_short_name;
1927 END IF;
1928
1929 IF (l_sob_list_count >= 7) THEN
1930 p_sob_id_7 := l_sob_list(7).r_sob_id;
1931 p_sob_name_7 := l_sob_list(7).r_sob_name;
1932 p_sob_type_7 := l_sob_list(7).r_sob_type;
1933 p_sob_curr_7 := l_sob_list(7).r_sob_curr;
1934 p_sob_user_type_7 := l_sob_list(7).r_sob_user_type;
1935 p_sob_short_name_7 := l_sob_list(7).r_sob_short_name;
1936 END IF;
1937
1938 IF (l_sob_list_count >= 8) THEN
1939 p_sob_id_8 := l_sob_list(8).r_sob_id;
1940 p_sob_name_8 := l_sob_list(8).r_sob_name;
1941 p_sob_type_8 := l_sob_list(8).r_sob_type;
1942 p_sob_curr_8 := l_sob_list(8).r_sob_curr;
1943 p_sob_user_type_8 := l_sob_list(8).r_sob_user_type;
1944 p_sob_short_name_8 := l_sob_list(8).r_sob_short_name;
1945 END IF;
1946
1947 END;
1948
1949 -- Procedure
1950 -- get_sec_associated_ledgers
1951 -- Purpose
1952 -- Gets the primary and all its secondary ledgers
1953 -- History
1954 -- 25-FEB-03 Li Wing Poon Created (New R11i.X procedure)
1955 PROCEDURE get_sec_associated_ledgers
1956 (n_ledger_id IN NUMBER,
1957 n_appl_id IN NUMBER,
1958 n_org_id IN NUMBER,
1959 n_include_primary_ledger IN VARCHAR2,
1960 n_ledger_list IN OUT NOCOPY r_sob_list) IS
1961 l_ledger_rec_col r_sob_rec_col; -- To store the values retrieved by BULK COLLECT
1962 l_ledger_category VARCHAR2(30);
1963 l_pri_ledger_id NUMBER; -- Variable to store source ledger ID
1964 l_num_rec NUMBER;
1965 i NUMBER;
1966 BEGIN
1967
1968 -- Get the ledger category of the passed ledger ID
1969 gl_mc_info.get_ledger_category(n_ledger_id, l_ledger_category);
1970
1971 IF l_ledger_category = 'ALC' OR l_ledger_category = 'NONE' THEN
1972 -- If it is ALC or NONE ledger, return the ledger list as NULL
1973 n_ledger_list.extend;
1974 ELSE
1975 IF l_ledger_category = 'PRIMARY' THEN
1976 l_pri_ledger_id := n_ledger_id;
1977 ELSIF l_ledger_category = 'SECONDARY' THEN
1978 -- Get primary ledger ID of the passed secondary ledger/applciation/OU
1979 l_pri_ledger_id := gl_mc_info.get_primary_ledger_id( n_ledger_id
1980 , n_appl_id
1981 , n_org_id);
1982 END IF; -- IF l_ledger_category = 'PRIMARY' THEN
1983
1984 -- Get the source and target ledgers info, if any and store them into
1985 -- l_ledger_rec_col by using BULK COLLECT. We cannot store them directly
1986 -- into n_ledger_list since it is table of records (composite) while BULK
1987 -- COLLECT can just apply to table of scalar type.
1988 SELECT g.ledger_id,
1989 g.name,
1990 g.short_name,
1991 g.currency_code,
1992 g.alc_ledger_type_code,
1993 g.ledger_category_code,
1994 g.sla_accounting_method_code,
1995 f.precision,
1996 f.minimum_accountable_unit,
1997 DECODE(g.alc_ledger_type_code, 'SOURCE', 'P'
1998 , 'TARGET', 'R'
1999 , 'N'),
2000 DECODE(g.alc_ledger_type_code, 'SOURCE', 'Primary'
2001 , 'TARGET', 'Reporting'
2002 , 'Notassigned')
2003 BULK COLLECT INTO
2004 l_ledger_rec_col.r_sob_id,
2005 l_ledger_rec_col.r_sob_name,
2006 l_ledger_rec_col.r_sob_short_name,
2007 l_ledger_rec_col.r_sob_curr,
2008 l_ledger_rec_col.r_alc_type,
2009 l_ledger_rec_col.r_category,
2010 l_ledger_rec_col.r_acct_method_code,
2011 l_ledger_rec_col.r_precision,
2012 l_ledger_rec_col.r_mau,
2013 l_ledger_rec_col.r_sob_type,
2014 l_ledger_rec_col.r_sob_user_type
2015 FROM gl_ledgers g,
2016 fnd_currencies f
2017 -- Include primary ledger only if n_include_primary_ledger is Y or NULL
2018 WHERE ((upper(NVL(n_include_primary_ledger, 'Y')) = 'Y'
2019 AND g.ledger_id = l_pri_ledger_id)
2020 OR g.ledger_id IN (
2021 SELECT glr.target_ledger_id -- Secondary Ledgers
2022 FROM gl_ledger_relationships glr, gl_ledgers lgr_c
2023 WHERE glr.primary_ledger_id = l_pri_ledger_id
2024 AND glr.target_ledger_category_code = 'SECONDARY'
2025 AND glr.relationship_type_code <> 'NONE'
2026 AND glr.target_ledger_id = lgr_c.ledger_id
2027 AND NVL(lgr_c.complete_flag,'Y') = 'Y'
2028 AND glr.application_id = n_appl_id
2029 AND (n_org_id IS NULL
2030 OR glr.org_id = -99
2031 OR glr.org_id = NVL(n_org_id,-99))
2032 AND glr.relationship_enabled_flag = 'Y'))
2033 AND g.currency_code = f.currency_code
2034 ORDER BY DECODE(g.ledger_category_code
2035 , 'Primary' , 1
2036 , 'Secondary', 2
2037 , 3)
2038 , g.ledger_id;
2039
2040 -- Get the number of records retrieved and extend the
2041 -- n_ledger_list (table) - allocate spaces
2042 l_num_rec := l_ledger_rec_col.r_sob_id.count;
2043 n_ledger_list.extend(l_num_rec);
2044
2045 -- If no records are fetched, return the ledger list as NULL
2046 IF (l_num_rec = 0) THEN
2047 n_ledger_list.extend;
2048 ELSE
2049 -- Try to store all records from l_ledger_rec_col to n_ledger_list
2050 FOR i IN 1..l_num_rec LOOP
2051 SELECT l_ledger_rec_col.r_sob_id(i),
2052 l_ledger_rec_col.r_sob_name(i),
2053 l_ledger_rec_col.r_sob_short_name(i),
2054 l_ledger_rec_col.r_sob_curr(i),
2055 l_ledger_rec_col.r_alc_type(i),
2056 l_ledger_rec_col.r_category(i),
2057 l_ledger_rec_col.r_acct_method_code(i),
2058 l_ledger_rec_col.r_precision(i),
2059 l_ledger_rec_col.r_mau(i),
2060 l_ledger_rec_col.r_sob_type(i),
2061 l_ledger_rec_col.r_sob_user_type(i)
2062 INTO n_ledger_list(i).r_sob_id,
2063 n_ledger_list(i).r_sob_name,
2064 n_ledger_list(i).r_sob_short_name,
2065 n_ledger_list(i).r_sob_curr,
2066 n_ledger_list(i).r_alc_type,
2067 n_ledger_list(i).r_category,
2068 n_ledger_list(i).r_acct_method_code,
2069 n_ledger_list(i).r_precision,
2070 n_ledger_list(i).r_mau,
2071 n_ledger_list(i).r_sob_type,
2072 n_ledger_list(i).r_sob_user_type
2073 FROM dual;
2074 END LOOP; -- FOR LOOP
2075 END IF; -- IF (l_num_rec = 0) THEN
2076 END IF; -- IF l_ledger_category = 'ALC' OR l_ledger_category = 'NONE' THEN
2077 END;
2078
2079 -- Procedure
2080 -- ap_ael_sobs
2081 -- *Should call get_alc_associated_ledgers() or get_sec_associated_ledgers()
2082 -- instead and this is for backward compatible
2083 -- **After AP uptake SLA, this API could be deleted.
2084 -- Purpose
2085 -- This api takes the PL/SQL table with AP primary book info and returns the
2086 -- PL/SQL table with additional rows containing any associated reporting SOBS
2087 -- History
2088 -- 03-MAR-99 Ramana Yella Created
2089 -- 16-MAR-00 MGOWDA Fixed bug 1238127
2090 -- 22-MAY-00 LPOON Set encumb_flag = 'N' for RSOBs only
2091 -- 25-FEB-03 LPOON R11i.X Changes
2092 PROCEDURE ap_ael_sobs (ael_sob_info IN OUT NOCOPY t_ael_sob_info) IS
2093
2094 l_aa gl_mc_info.t_ael_sob_info;
2095 l_sob_list gl_mc_info.r_sob_list := gl_mc_info.r_sob_list();
2096 l_cnt NUMBER := 0;
2097 i NUMBER := 0;
2098 j NUMBER := 0;
2099 l_client_info VARCHAR2(64);
2100 l_org_id NUMBER(15);
2101 l_pri_curr VARCHAR(15);
2102 BEGIN
2103 l_aa := ael_sob_info;
2104 ael_sob_info.delete;
2105
2106 dbms_application_info.read_client_info(l_client_info);
2107 --
2108 -- Bug 1238127, not able to convert to number when customer is not multiorg.
2109 -- replaced spaces with null.
2110 --
2111 l_client_info := REPLACE(SUBSTR(l_client_info,1,10),' ',null);
2112 l_org_id := to_number(l_client_info);
2113
2114 FOR rec in 1..l_aa.count LOOP
2115 l_cnt := l_cnt + 1;
2116 IF l_aa(l_cnt).sob_id IS NOT NULL THEN
2117 -- Insert itself into the result list
2118 j := j + 1;
2119 BEGIN
2120 SELECT currency_code, name
2121 INTO ael_sob_info(j).currency_code,
2122 ael_sob_info(j).sob_name
2123 FROM gl_ledgers
2124 WHERE ledger_id = l_aa(l_cnt).sob_id;
2125 EXCEPTION
2126 WHEN OTHERS THEN
2127 fnd_message.set_name('SQLGL','MRC_TABLE_ERROR');
2128 fnd_message.set_token('MODULE','GLMCINFB');
2129 fnd_message.set_token('TABLE','GL_LEDGERS');
2130 RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
2131 END;
2132
2133 ael_sob_info(j).sob_id := l_aa(l_cnt).sob_id;
2134 ael_sob_info(j).accounting_method := l_aa(l_cnt).accounting_method;
2135 ael_sob_info(j).encumb_flag := l_aa(l_cnt).encumb_flag;
2136 ael_sob_info(j).sob_type := l_aa(l_cnt).sob_type;
2137
2138 IF (l_aa(l_cnt).sob_type = 'P') THEN
2139 -- Primary SOB => Get its associated reporting SOBs and reporting
2140 -- secondary SOBs
2141 l_pri_curr := ael_sob_info(j).currency_code;
2142
2143 -- Get all the associated reporting secondary SOBs which are
2144 -- converted to secondary ledgers with currency different with
2145 -- primary currency (*exclude primary and will get it later)
2146 gl_mc_info.get_sec_associated_ledgers( l_aa(l_cnt).sob_id
2147 , 200 -- AP
2148 , l_org_id
2149 , 'N' -- Exclude primary ledger
2150 , l_sob_list);
2151
2152 IF (l_sob_list.count > 0 AND l_sob_list(1).r_sob_id IS NOT NULL) THEN
2153 FOR rec1 in 1..l_sob_list.count LOOP
2154 -- Only process if its currency is different with the primary
2155 -- currency as other secondary SOBs with same currency are
2156 -- passed in already
2157 IF (l_sob_list(i).r_sob_curr <> l_pri_curr) THEN
2158 i := i + 1;
2159 j := j + 1;
2160
2161 ael_sob_info(j).sob_id := l_sob_list(i).r_sob_id;
2162 ael_sob_info(j).currency_code := l_sob_list(i).r_sob_curr;
2163 ael_sob_info(j).sob_name := l_sob_list(i).r_sob_name;
2164
2165 IF (l_sob_list(i).r_acct_method_code = 'STANDARD_CASH') THEN
2166 ael_sob_info(j).accounting_method := 'Cash';
2167 ELSE
2168 ael_sob_info(j).accounting_method := 'Accrual';
2169 END IF;
2170
2171 IF l_sob_list(i).r_sob_type = 'R' THEN
2172 ael_sob_info(j).sob_type := 'R';
2173 -- Set encumb_flag to 'N' for RSOBs
2174 ael_sob_info(j).encumb_flag := 'N';
2175 ELSE
2176 ael_sob_info(j).sob_type := NULL;
2177 -- Set encumb_flag as original value for others
2178 ael_sob_info(j).encumb_flag := l_aa(l_cnt).encumb_flag;
2179 END IF;
2180 END IF; -- IF (l_sob_list(i).r_sob_curr <> l_pri_curr) THEN
2181 END LOOP; -- FOR LOOP rec1
2182 END IF; -- IF (l_sob_list.count > 0 AND l_sob_list(1).r_sob_id ...
2183
2184 -- Reset counter, i and clean up l_sob_list before getting associated
2185 -- ALC ledgers
2186 i := 0;
2187 l_sob_list.delete;
2188
2189 -- Get all the associated ALC target ledgers (i.e. reporting SOBs)
2190 gl_mc_info.get_alc_associated_ledgers( l_aa(l_cnt).sob_id
2191 , 200 -- AP
2192 , l_org_id
2193 , NULL
2194 , 'N' -- Exclude ALC source ledger
2195 , l_sob_list);
2196
2197 IF (l_sob_list.count > 0 AND l_sob_list(1).r_sob_id IS NOT NULL) THEN
2198 FOR rec1 in 1..l_sob_list.count LOOP
2199 i := i + 1;
2200 j := j + 1;
2201
2202 ael_sob_info(j).sob_id := l_sob_list(i).r_sob_id;
2203 ael_sob_info(j).currency_code := l_sob_list(i).r_sob_curr;
2204 ael_sob_info(j).accounting_method := l_aa(l_cnt).accounting_method;
2205 ael_sob_info(j).sob_name := l_sob_list(i).r_sob_name;
2206 ael_sob_info(j).sob_type := 'R';
2207 -- Set encumb_flag to 'N' for RSOBs
2208 ael_sob_info(j).encumb_flag := 'N';
2209 END LOOP; -- FOR LOOP rec1
2210 END IF; -- IF (l_sob_list.count > 0 AND l_sob_list(1).r_sob_id ...
2211
2212 -- Reset counter, i and cleanup l_sob_list before handling next sob
2213 i := 0;
2214 l_sob_list.delete;
2215 END IF; -- IF (l_aa(l_cnt).sob_type = 'P') THEN
2216 END IF; -- IF l_aa(l_cnt).sob_id IS NOT NULL THEN
2217 END LOOP; -- FOR LOOP rec
2218 END;
2219
2220 -- Function
2221 -- get_conversion_type
2222 -- History
2223 -- 01-MAR-99 Ramana Yella Created
2224 -- 25-FEB-03 Li Wing Poon R11i.X changes
2225 -- 18-Apr-05 Li Wing Poon SLA uptake - we don't need this
2226 -- function anymore
2227 FUNCTION get_conversion_type (pk_id IN NUMBER,
2228 sob_id IN NUMBER,
2229 source IN VARCHAR2,
2230 ptype IN VARCHAR2) RETURN VARCHAR2 IS
2231 BEGIN
2232 -- RETURN ap_mc_info.get_conversion_type(pk_id, sob_id, source, ptype);
2233 RETURN NULL;
2234 END;
2235
2236 -- Function
2237 -- get_conversion_date
2238 -- History
2239 -- 02-MAR-99 Ramana Yella Created
2240 -- 25-FEB-03 Li Wing Poon R11i.X Changes
2241 -- 18-Apr-05 Li Wing Poon SLA uptake - we don't need this
2242 -- function anymore
2243 FUNCTION get_conversion_date (pk_id IN NUMBER,
2244 sob_id IN NUMBER,
2245 source IN VARCHAR2,
2246 ptype IN VARCHAR2) RETURN DATE IS
2247 BEGIN
2248 -- RETURN ap_mc_info.get_conversion_date(pk_id, sob_id, source, ptype);
2249 RETURN NULL;
2250 END;
2251
2252 -- Function
2253 -- get_conversion_rate
2254 -- History
2255 -- 02-MAR-99 Ramana Yella Created
2256 -- 25-FEB-03 Li Wing Poon R11i.X changes
2257 -- 18-Apr-05 Li Wing Poon SLA uptake - we don't need this
2258 -- function anymore
2259 FUNCTION get_conversion_rate (pk_id IN NUMBER,
2260 sob_id IN NUMBER,
2261 source IN VARCHAR2,
2262 ptype IN VARCHAR2) RETURN NUMBER IS
2263 BEGIN
2264 -- RETURN ap_mc_info.get_conversion_rate(pk_id, sob_id, source, ptype);
2265 RETURN NULL;
2266 END;
2267
2268 -- Function
2269 -- get_acctd_amount
2270 -- History
2271 -- 28-APR-98 MGOWDA Created
2272 -- 25-FEB-03 LPOON R11i.X changes
2273 -- 18-Apr-05 LPOON SLA uptake - we don't need this function anymore
2274 FUNCTION get_acctd_amount( pk_id IN NUMBER,
2275 sob_id IN NUMBER,
2276 source IN VARCHAR2,
2277 amount_type IN VARCHAR2) RETURN NUMBER IS
2278 BEGIN
2279 -- RETURN ap_mc_info.get_acctd_amount(pk_id, sob_id, source, amount_type);
2280 RETURN NULL;
2281 END;
2282
2283 -- Function
2284 -- get_ccid
2285 -- History
2286 -- 21-MAY-01 LPOON Created
2287 -- 25-FEB-03 LPOON R11i.X changes
2288 -- 18-Apr-05 LPOON SLA uptake - we don't need this function anymore
2289 FUNCTION get_ccid ( pk_id IN NUMBER,
2290 sob_id IN NUMBER,
2291 source IN VARCHAR2,
2292 ccid_type IN VARCHAR2) RETURN NUMBER IS
2293 BEGIN
2294 -- RETURN ap_mc_info.get_ccid(pk_id, sob_id, source, ccid_type);
2295 RETURN NULL;
2296 END;
2297
2298 -- Procedure
2299 -- populate_ledger_bsv_gt
2300 -- Purpose
2301 -- This api populates the table with flex values which will be used during
2302 -- Accounting setup flow BSV assignments. The table is populated with flex
2303 -- values from the standard FND tables or custom tables depending on the
2304 -- flex value set id.
2305 -- History
2306 -- 16-JUL-03 MGOWDA Created
2307
2308 PROCEDURE populate_ledger_bsv_gt (n_ledger_id IN NUMBER)
2309 IS
2310 l_insert_statement DBMS_SQL.VARCHAR2S;
2311 l_line_num NUMBER := 1;
2312 l_cursor integer;
2313 l_fv_table varchar2(30);
2314 l_fv_col varchar2(30);
2315 l_fv_type fnd_flex_value_sets.validation_type%TYPE;
2316 l_fv_description fnd_flex_validation_tables.meaning_column_name%TYPE;
2317 rows_processed number;
2318 l_status varchar2(30);
2319 BEGIN
2320
2321 -- Bug fix 3975695: Moved the codes to assign default values from
2322 -- declaration to here
2323 l_status := 'Initialize';
2324
2325 --
2326 -- It is possible that during working on the same configuration BSV
2327 -- assignment page could be accessed multiple times so check to see if
2328 -- the BSV values already exists in temporary table
2329
2330 BEGIN
2331 SELECT 'Already Populated'
2332 INTO l_status
2333 FROM dual
2334 WHERE EXISTS
2335 (SELECT 'x'
2336 FROM gl_ledger_bsv_gt
2337 WHERE ledger_id = n_ledger_id);
2338 EXCEPTION
2339 WHEN NO_DATA_FOUND THEN
2340 l_status := 'Initialize';
2341 END;
2342
2343 IF l_status = 'Initialize'
2344 THEN
2345 BEGIN
2346 SELECT nvl(fvt.application_table_name, 'FND_FLEX_VALUES')
2347 , nvl(fvt.value_column_name, 'FLEX_VALUE')
2348 , fvs.validation_type
2349 , nvl(fvt.meaning_column_name, 'DESCRIPTION')
2350 INTO l_fv_table
2351 , l_fv_col
2352 , l_fv_type
2353 , l_fv_description
2354 FROM fnd_flex_value_sets fvs
2355 , fnd_flex_validation_tables fvt
2356 , gl_ledgers gl
2357 WHERE fvs.flex_value_set_id = gl.bal_seg_value_set_id
2358 AND gl.ledger_id = n_ledger_id
2359 AND fvt.flex_value_set_id = fvs.flex_value_set_id;
2360 EXCEPTION
2361 WHEN NO_DATA_FOUND THEN
2362 l_fv_table := 'FND_FLEX_VALUES';
2363 l_fv_col := 'FLEX_VALUE';
2364 l_fv_type := NULL;
2365 l_fv_description := 'DESCRIPTION';
2366 END;
2367
2368 IF (nvl(l_fv_type,'X') <> 'F')
2369 THEN
2370
2371 INSERT INTO GL_LEDGER_BSV_GT
2372 ( FLEX_VALUE
2373 ,DESCRIPTION
2374 ,LEDGER_ID
2375 )
2376 SELECT FlexValues.FLEX_VALUE,
2377 FlexValues.DESCRIPTION,
2378 n_ledger_id
2379 FROM GL_LEDGERS Ledgers,
2380 FND_FLEX_VALUES_VL FlexValues
2381 WHERE FlexValues.FLEX_VALUE_SET_ID = Ledgers.bal_seg_value_set_id
2382 AND Ledgers.ledger_id = n_ledger_id
2383 AND FlexValues.SUMMARY_FLAG = 'N';
2384 ELSE
2385 l_insert_statement(l_line_num) := 'INSERT INTO gl_ledger_bsv_gt ';
2386 l_line_num := l_line_num + 1;
2387 l_insert_statement(l_line_num) := '(FLEX_VALUE,DESCRIPTION,LEDGER_ID)';
2388 l_line_num := l_line_num + 1;
2389 l_insert_statement(l_line_num) := 'SELECT '||l_fv_col||',';
2390 l_line_num := l_line_num + 1;
2391 l_insert_statement(l_line_num) := l_fv_description||','||n_ledger_id;
2392 l_line_num := l_line_num + 1;
2393 l_insert_statement(l_line_num) := 'FROM '||l_fv_table;
2394 l_cursor := dbms_sql.open_cursor;
2395 dbms_sql.parse(l_cursor, l_insert_statement,1,
2396 l_line_Num, true, dbms_sql.native);
2397 rows_processed := dbms_sql.execute(l_cursor);
2398 dbms_sql.close_cursor(l_cursor);
2399 END IF;
2400 END IF;
2401 END;
2402
2403
2404 END gl_mc_info;
[Home] [Help]