[Home] [Help]
PACKAGE BODY: APPS.GL_JE_SEGMENT_VALUES_PKG
Source
1 PACKAGE BODY GL_JE_SEGMENT_VALUES_PKG as
2 /* $Header: glijesvb.pls 120.12.12010000.2 2008/08/13 13:41:29 kmotepal ship $ */
3
4 --
5 -- PRIVATE VARIABLES
6 --
7
8 -- Dynamic sql buffers
9 insert_stmt VARCHAR2(2000); -- Buffer for insert dynamic sql
10 delete_bsv_stmt VARCHAR2(2000); -- Buffer for delete bsv dynamic sql
11 delete_msv_stmt VARCHAR2(2000); -- Buffer for delete msv dynamic sql
12
13 -- Column names of bsv and msv columns
14 bsv_colname VARCHAR2(30); -- BSV column name
15 msv_colname VARCHAR2(30); -- MSV column name
16
17
18 --
19 -- PUBLIC FUNCTIONS
20 --
21 FUNCTION insert_segment_values( x_je_header_id NUMBER )
22 RETURN NUMBER
23 IS
24 num_rows NUMBER;
25 user_id NUMBER;
26 login_id NUMBER;
27 BEGIN
28
29 user_id := fnd_profile.value('USER_ID');
30 login_id := fnd_profile.value('LOGIN_ID');
31
32 -- Delete all the existing values
33 DELETE FROM GL_JE_SEGMENT_VALUES
34 WHERE je_header_id = x_je_header_id;
35
36 -- Insert distinct segment values
37 INSERT INTO GL_JE_SEGMENT_VALUES
38 (je_header_id, segment_type_code, segment_value,
39 creation_date, created_by, last_update_date, last_updated_by,
40 last_update_login)
41 SELECT LINE.je_header_id,
42 DECODE(X.multiplier,
43 1,'B',
44 2,'M'),
45 DECODE(DECODE(X.multiplier,
46 1,LDG.bal_seg_column_name,
47 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
48 'SEGMENT2',cc.SEGMENT2,
49 'SEGMENT3',cc.SEGMENT3,
50 'SEGMENT4',cc.SEGMENT4,
51 'SEGMENT5',cc.SEGMENT5,
52 'SEGMENT6',cc.SEGMENT6,
53 'SEGMENT7',cc.SEGMENT7,
54 'SEGMENT8',cc.SEGMENT8,
55 'SEGMENT9',cc.SEGMENT9,
56 'SEGMENT10',cc.SEGMENT10,
57 'SEGMENT11',cc.SEGMENT11,
58 'SEGMENT12',cc.SEGMENT12,
59 'SEGMENT13',cc.SEGMENT13,
60 'SEGMENT14',cc.SEGMENT14,
61 'SEGMENT15',cc.SEGMENT15,
62 'SEGMENT16',cc.SEGMENT16,
63 'SEGMENT17',cc.SEGMENT17,
64 'SEGMENT18',cc.SEGMENT18,
65 'SEGMENT19',cc.SEGMENT19,
66 'SEGMENT20',cc.SEGMENT20,
67 'SEGMENT21',cc.SEGMENT21,
68 'SEGMENT22',cc.SEGMENT22,
69 'SEGMENT23',cc.SEGMENT23,
70 'SEGMENT24',cc.SEGMENT24,
71 'SEGMENT25',cc.SEGMENT25,
72 'SEGMENT26',cc.SEGMENT26,
73 'SEGMENT27',cc.SEGMENT27,
74 'SEGMENT28',cc.SEGMENT28,
75 'SEGMENT29',cc.SEGMENT29,
76 'SEGMENT30',cc.SEGMENT30),
77 sysdate, user_id, sysdate, user_id, login_id
78 FROM gl_code_combinations CC,
79 gl_ledgers LDG,
80 gl_je_lines LINE,
81 gl_row_multipliers X
82 WHERE CC.code_combination_id = LINE.code_combination_id
83 AND LDG.ledger_id = LINE.ledger_id
84 AND LINE.je_header_id = x_je_header_id
85 AND X.multiplier IN (1,Decode(LDG.mgt_seg_column_name,NULL,NULL,2))
86 GROUP by LINE.je_header_id,
87 DECODE(X.multiplier,
88 1,'B',
89 2,'M'),
90 DECODE(DECODE(X.multiplier,
91 1,LDG.bal_seg_column_name,
92 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
93 'SEGMENT2',cc.SEGMENT2,
94 'SEGMENT3',cc.SEGMENT3,
95 'SEGMENT4',cc.SEGMENT4,
96 'SEGMENT5',cc.SEGMENT5,
97 'SEGMENT6',cc.SEGMENT6,
98 'SEGMENT7',cc.SEGMENT7,
99 'SEGMENT8',cc.SEGMENT8,
100 'SEGMENT9',cc.SEGMENT9,
101 'SEGMENT10',cc.SEGMENT10,
102 'SEGMENT11',cc.SEGMENT11,
103 'SEGMENT12',cc.SEGMENT12,
104 'SEGMENT13',cc.SEGMENT13,
105 'SEGMENT14',cc.SEGMENT14,
106 'SEGMENT15',cc.SEGMENT15,
107 'SEGMENT16',cc.SEGMENT16,
108 'SEGMENT17',cc.SEGMENT17,
109 'SEGMENT18',cc.SEGMENT18,
110 'SEGMENT19',cc.SEGMENT19,
111 'SEGMENT20',cc.SEGMENT20,
112 'SEGMENT21',cc.SEGMENT21,
113 'SEGMENT22',cc.SEGMENT22,
114 'SEGMENT23',cc.SEGMENT23,
115 'SEGMENT24',cc.SEGMENT24,
116 'SEGMENT25',cc.SEGMENT25,
117 'SEGMENT26',cc.SEGMENT26,
118 'SEGMENT27',cc.SEGMENT27,
119 'SEGMENT28',cc.SEGMENT28,
120 'SEGMENT29',cc.SEGMENT29,
121 'SEGMENT30',cc.SEGMENT30);
122
123 -- Return the number of distinct balancing and management segment values inserted.
124 num_rows := SQL%ROWCOUNT;
125 RETURN (num_rows);
126
127 EXCEPTION
128 WHEN app_exceptions.application_exception THEN
129 RAISE;
130 WHEN OTHERS THEN
131 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
132 fnd_message.set_token('PROCEDURE',
133 'gl_je_segment_values_pkg.insert_segment_values');
134 RAISE;
135 END insert_segment_values;
136
137 -- **********************************************************************
138
139 FUNCTION insert_segment_values( x_je_header_id NUMBER,
140 x_je_line_num NUMBER,
141 x_user_id NUMBER,
142 x_login_id NUMBER)
143 RETURN NUMBER
144 IS
145 num_rows NUMBER;
146 BEGIN
147 -- Insert new values only if needed
148 INSERT INTO GL_JE_SEGMENT_VALUES
149 (je_header_id, segment_type_code, segment_value,
150 creation_date, created_by, last_update_date, last_updated_by,
151 last_update_login)
152 SELECT LINE.je_header_id,
153 DECODE(X.multiplier,
154 1,'B',
155 2,'M'),
156 DECODE(DECODE(X.multiplier,
157 1,LDG.bal_seg_column_name,
158 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
159 'SEGMENT2',cc.SEGMENT2,
160 'SEGMENT3',cc.SEGMENT3,
161 'SEGMENT4',cc.SEGMENT4,
162 'SEGMENT5',cc.SEGMENT5,
163 'SEGMENT6',cc.SEGMENT6,
164 'SEGMENT7',cc.SEGMENT7,
165 'SEGMENT8',cc.SEGMENT8,
166 'SEGMENT9',cc.SEGMENT9,
167 'SEGMENT10',cc.SEGMENT10,
168 'SEGMENT11',cc.SEGMENT11,
169 'SEGMENT12',cc.SEGMENT12,
170 'SEGMENT13',cc.SEGMENT13,
171 'SEGMENT14',cc.SEGMENT14,
172 'SEGMENT15',cc.SEGMENT15,
173 'SEGMENT16',cc.SEGMENT16,
174 'SEGMENT17',cc.SEGMENT17,
175 'SEGMENT18',cc.SEGMENT18,
176 'SEGMENT19',cc.SEGMENT19,
177 'SEGMENT20',cc.SEGMENT20,
178 'SEGMENT21',cc.SEGMENT21,
179 'SEGMENT22',cc.SEGMENT22,
180 'SEGMENT23',cc.SEGMENT23,
181 'SEGMENT24',cc.SEGMENT24,
182 'SEGMENT25',cc.SEGMENT25,
183 'SEGMENT26',cc.SEGMENT26,
184 'SEGMENT27',cc.SEGMENT27,
185 'SEGMENT28',cc.SEGMENT28,
186 'SEGMENT29',cc.SEGMENT29,
187 'SEGMENT30',cc.SEGMENT30),
188 sysdate, x_user_id, sysdate, x_user_id, x_login_id
189 FROM gl_code_combinations CC,
190 gl_ledgers LDG,
191 gl_je_lines LINE,
192 gl_row_multipliers X
193 WHERE CC.code_combination_id = LINE.code_combination_id
194 AND LDG.ledger_id = LINE.ledger_id
195 AND LINE.je_header_id = x_je_header_id
196 AND LINE.je_line_num = x_je_line_num
197 AND X.multiplier IN (1,Decode(LDG.mgt_seg_column_name,NULL,NULL,2))
198 AND NOT EXISTS (SELECT 'X'
199 FROM GL_JE_SEGMENT_VALUES SV
200 WHERE SV.je_header_id = LINE.je_header_id
201 AND SV.segment_type_code = DECODE(X.multiplier,
202 1,'B',
203 2,'M')
204 AND SV.segment_value = DECODE(DECODE(X.multiplier,
205 1,LDG.bal_seg_column_name,
206 2,LDG.mgt_seg_column_name),
207 'SEGMENT1',cc.SEGMENT1,
208 'SEGMENT2',cc.SEGMENT2,
209 'SEGMENT3',cc.SEGMENT3,
210 'SEGMENT4',cc.SEGMENT4,
211 'SEGMENT5',cc.SEGMENT5,
212 'SEGMENT6',cc.SEGMENT6,
213 'SEGMENT7',cc.SEGMENT7,
214 'SEGMENT8',cc.SEGMENT8,
215 'SEGMENT9',cc.SEGMENT9,
216 'SEGMENT10',cc.SEGMENT10,
217 'SEGMENT11',cc.SEGMENT11,
218 'SEGMENT12',cc.SEGMENT12,
219 'SEGMENT13',cc.SEGMENT13,
220 'SEGMENT14',cc.SEGMENT14,
221 'SEGMENT15',cc.SEGMENT15,
222 'SEGMENT16',cc.SEGMENT16,
223 'SEGMENT17',cc.SEGMENT17,
224 'SEGMENT18',cc.SEGMENT18,
225 'SEGMENT19',cc.SEGMENT19,
226 'SEGMENT20',cc.SEGMENT20,
227 'SEGMENT21',cc.SEGMENT21,
228 'SEGMENT22',cc.SEGMENT22,
229 'SEGMENT23',cc.SEGMENT23,
230 'SEGMENT24',cc.SEGMENT24,
231 'SEGMENT25',cc.SEGMENT25,
232 'SEGMENT26',cc.SEGMENT26,
233 'SEGMENT27',cc.SEGMENT27,
234 'SEGMENT28',cc.SEGMENT28,
235 'SEGMENT29',cc.SEGMENT29,
236 'SEGMENT30',cc.SEGMENT30));
237
238 -- Return the number of distinct balancing and management segment values inserted.
239 num_rows := SQL%ROWCOUNT;
240 RETURN (num_rows);
241
242 EXCEPTION
243 WHEN app_exceptions.application_exception THEN
244 RAISE;
245 WHEN OTHERS THEN
246 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
247 fnd_message.set_token('PROCEDURE',
248 'gl_je_segment_values_pkg.insert_segment_values');
249 RAISE;
250 END insert_segment_values;
251
252 -- **********************************************************************
253
254 FUNCTION insert_batch_segment_values( x_je_batch_id NUMBER)
255 RETURN NUMBER
256 IS
257 num_rows NUMBER;
258 user_id NUMBER;
259 login_id NUMBER;
260 BEGIN
261
262 user_id := fnd_profile.value('USER_ID');
263 login_id := fnd_profile.value('LOGIN_ID');
264
265 -- Delete all the existing values
266 DELETE FROM GL_JE_SEGMENT_VALUES
267 WHERE je_header_id IN (SELECT je_header_id
268 FROM GL_JE_HEADERS
269 WHERE je_batch_id = x_je_batch_id);
270
271 -- Insert distinct segment values
272 INSERT INTO GL_JE_SEGMENT_VALUES
273 (je_header_id, segment_type_code, segment_value,
274 creation_date, created_by, last_update_date, last_updated_by,
275 last_update_login)
276 SELECT LINE.je_header_id,
277 DECODE(X.multiplier,
278 1,'B',
279 2,'M'),
280 DECODE(DECODE(X.multiplier,
281 1,LDG.bal_seg_column_name,
282 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
283 'SEGMENT2',cc.SEGMENT2,
284 'SEGMENT3',cc.SEGMENT3,
285 'SEGMENT4',cc.SEGMENT4,
289 'SEGMENT8',cc.SEGMENT8,
286 'SEGMENT5',cc.SEGMENT5,
287 'SEGMENT6',cc.SEGMENT6,
288 'SEGMENT7',cc.SEGMENT7,
290 'SEGMENT9',cc.SEGMENT9,
291 'SEGMENT10',cc.SEGMENT10,
292 'SEGMENT11',cc.SEGMENT11,
293 'SEGMENT12',cc.SEGMENT12,
294 'SEGMENT13',cc.SEGMENT13,
295 'SEGMENT14',cc.SEGMENT14,
296 'SEGMENT15',cc.SEGMENT15,
297 'SEGMENT16',cc.SEGMENT16,
298 'SEGMENT17',cc.SEGMENT17,
299 'SEGMENT18',cc.SEGMENT18,
300 'SEGMENT19',cc.SEGMENT19,
301 'SEGMENT20',cc.SEGMENT20,
302 'SEGMENT21',cc.SEGMENT21,
303 'SEGMENT22',cc.SEGMENT22,
304 'SEGMENT23',cc.SEGMENT23,
305 'SEGMENT24',cc.SEGMENT24,
306 'SEGMENT25',cc.SEGMENT25,
307 'SEGMENT26',cc.SEGMENT26,
308 'SEGMENT27',cc.SEGMENT27,
309 'SEGMENT28',cc.SEGMENT28,
310 'SEGMENT29',cc.SEGMENT29,
311 'SEGMENT30',cc.SEGMENT30),
312 sysdate, user_id, sysdate, user_id, login_id
313 FROM gl_code_combinations CC,
314 gl_ledgers LDG,
315 gl_je_lines LINE,
316 gl_je_headers JH,
317 gl_row_multipliers X
318 WHERE CC.code_combination_id = LINE.code_combination_id
319 AND LDG.ledger_id = LINE.ledger_id
320 AND LINE.je_header_id = JH.je_header_id
321 AND JH.je_batch_id = x_je_batch_id
322 AND X.multiplier IN (1,Decode(LDG.mgt_seg_column_name,NULL,NULL,2))
323 GROUP by LINE.je_header_id,
324 DECODE(X.multiplier,
325 1,'B',
326 2,'M'),
327 DECODE(DECODE(X.multiplier,
328 1,LDG.bal_seg_column_name,
329 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
330 'SEGMENT2',cc.SEGMENT2,
331 'SEGMENT3',cc.SEGMENT3,
332 'SEGMENT4',cc.SEGMENT4,
333 'SEGMENT5',cc.SEGMENT5,
334 'SEGMENT6',cc.SEGMENT6,
335 'SEGMENT7',cc.SEGMENT7,
336 'SEGMENT8',cc.SEGMENT8,
337 'SEGMENT9',cc.SEGMENT9,
338 'SEGMENT10',cc.SEGMENT10,
339 'SEGMENT11',cc.SEGMENT11,
340 'SEGMENT12',cc.SEGMENT12,
341 'SEGMENT13',cc.SEGMENT13,
342 'SEGMENT14',cc.SEGMENT14,
343 'SEGMENT15',cc.SEGMENT15,
344 'SEGMENT16',cc.SEGMENT16,
345 'SEGMENT17',cc.SEGMENT17,
346 'SEGMENT18',cc.SEGMENT18,
347 'SEGMENT19',cc.SEGMENT19,
348 'SEGMENT20',cc.SEGMENT20,
349 'SEGMENT21',cc.SEGMENT21,
350 'SEGMENT22',cc.SEGMENT22,
351 'SEGMENT23',cc.SEGMENT23,
352 'SEGMENT24',cc.SEGMENT24,
353 'SEGMENT25',cc.SEGMENT25,
354 'SEGMENT26',cc.SEGMENT26,
355 'SEGMENT27',cc.SEGMENT27,
356 'SEGMENT28',cc.SEGMENT28,
357 'SEGMENT29',cc.SEGMENT29,
358 'SEGMENT30',cc.SEGMENT30);
359
360 -- Return the number of distinct balancing and management segment values inserted.
361 num_rows := SQL%ROWCOUNT;
362 RETURN (num_rows);
363
364 EXCEPTION
365 WHEN app_exceptions.application_exception THEN
366 RAISE;
367 WHEN OTHERS THEN
368 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
369 fnd_message.set_token('PROCEDURE',
370 'gl_je_segment_values_pkg.insert_batch_segment_values');
371 RAISE;
372 END insert_batch_segment_values;
373
377 header_id NUMBER,
374 -- **********************************************************************
375
376 PROCEDURE insert_ccid_segment_values(
378 ccid NUMBER,
379 user_id NUMBER,
380 login_id NUMBER) IS
381 temp VARCHAR2(30);
382 asid NUMBER;
383 BEGIN
384
385 -- Build the statements for the current
386 -- chart of accounts
387 IF (insert_stmt IS NULL) THEN
388 fnd_profile.get('GL_ACCESS_SET_ID', temp);
389 asid := to_number(temp);
390
391 SELECT bal_seg_column_name,
392 mgt_seg_column_name
393 INTO bsv_colname,
394 msv_colname
395 FROM gl_access_sets acs, gl_ledgers lgr
396 WHERE acs.access_set_id = asid
397 AND lgr.chart_of_accounts_id = acs.chart_of_accounts_id
398 AND rownum <= 1;
399
400 IF (bsv_colname IS NOT NULL) AND
401 (msv_colname IS NOT NULL) THEN
402 insert_stmt :=
403 'INSERT INTO gl_je_segment_values ' ||
404 '(je_header_id, segment_type_code, ' ||
405 ' segment_value, creation_date, created_by, last_update_date, ' ||
406 ' last_updated_by, last_update_login) ' ||
407 'SELECT :header_id, ' ||
408 'decode(ml.multiplier, ' ||
409 '1, ''B'', ''M''), ' ||
410 'decode(ml.multiplier, ' ||
411 '1, '|| bsv_colname || ',' ||
412 msv_colname ||
413 '), sysdate, :user_id, sysdate, :user_id, :login_id ' ||
414 'FROM gl_code_combinations cc, gl_row_multipliers ml '||
415 'WHERE cc.code_combination_id = :cc ' ||
416 'AND ml.multiplier between 1 and 2' ||
417 'AND NOT EXISTS ' ||
418 '(SELECT 1 ' ||
419 'FROM gl_je_segment_values sv '||
420 'WHERE sv.je_header_id = :header_id '||
421 'AND sv.segment_type_code = ' ||
422 'decode(ml.multiplier, ' ||
423 '1, ''B'', ''M'') ' ||
424 'AND sv.segment_value = ' ||
425 'decode(ml.multiplier, ' ||
426 '1, '|| bsv_colname || ',' ||
427 msv_colname || ')) ';
428
429 ELSIF (bsv_colname IS NOT NULL) AND
430 (msv_colname IS NULL) THEN
431
432 insert_stmt :=
433 'INSERT INTO gl_je_segment_values ' ||
434 '(je_header_id, segment_type_code, ' ||
435 ' segment_value, creation_date, created_by, last_update_date, ' ||
436 ' last_updated_by, last_update_login) ' ||
437 'SELECT :header_id, ' ||'''B'''||', '
438 ||bsv_colname || ' ' ||
439 ', sysdate, :user_id, sysdate, :user_id, :login_id ' ||
440 'FROM gl_code_combinations cc '||
441 'WHERE cc.code_combination_id = :cc ' ||
442 'AND NOT EXISTS ' ||
443 '(SELECT 1 ' ||
444 'FROM gl_je_segment_values sv '||
445 'WHERE sv.je_header_id = :header_id '||
446 'AND sv.segment_type_code = ''B''' ||
447 'AND sv.segment_value = '
448 || bsv_colname || ')';
449
450 END IF;
451 END IF;
452
453 EXECUTE IMMEDIATE insert_stmt USING header_id, user_id, user_id,
454 login_id, ccid, header_id;
455
456 EXCEPTION
457 WHEN app_exceptions.application_exception THEN
458 RAISE;
459 WHEN OTHERS THEN
460 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
461 fnd_message.set_token('PROCEDURE',
462 'gl_je_segment_values.add_segment_value');
463 RAISE;
464 END insert_ccid_segment_values;
465
466 -- **********************************************************************
467
468 FUNCTION delete_segment_values(x_je_header_id NUMBER)
469 RETURN NUMBER
470 IS
471 num_rows NUMBER;
472 BEGIN
473 DELETE FROM GL_JE_SEGMENT_VALUES sv
474 WHERE sv.je_header_id = x_je_header_id;
475
476 -- Return the number of distinct balancing and management segment values deleted
477 num_rows := SQL%ROWCOUNT;
478 RETURN (num_rows);
479
480 EXCEPTION
481 WHEN app_exceptions.application_exception THEN
482 RAISE;
483 WHEN OTHERS THEN
484 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
485 fnd_message.set_token('PROCEDURE',
486 'gl_je_segment_values_pkg.delete_header_segment_values');
487 RAISE;
488 END delete_segment_values;
489
490 -- **********************************************************************
491
492 FUNCTION delete_batch_segment_values(x_je_batch_id NUMBER)
493 RETURN NUMBER
494 IS
495 num_rows NUMBER;
496 BEGIN
497 DELETE FROM GL_JE_SEGMENT_VALUES SV
498 WHERE SV.je_header_id IN (SELECT JH.je_header_id
499 FROM GL_JE_HEADERS JH
500 WHERE JH.je_batch_id = x_je_batch_id);
501
502 -- Return the number of distinct balancing and management segment values deleted
503 num_rows := SQL%ROWCOUNT;
504 RETURN (num_rows);
505
506 EXCEPTION
507 WHEN app_exceptions.application_exception THEN
508 RAISE;
509 WHEN OTHERS THEN
513 RAISE;
510 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
511 fnd_message.set_token('PROCEDURE',
512 'gl_je_segment_values_pkg.delete_batch_segment_values');
514 END delete_batch_segment_values;
515
516 -- **********************************************************************
517
518 PROCEDURE cleanup_segment_values(
519 header_id NUMBER) IS
520 BEGIN
521 -- Build the statements for the current
522 -- chart of accounts
523 IF (delete_bsv_stmt IS NULL) THEN
524 SELECT bal_seg_column_name,
525 mgt_seg_column_name
526 INTO bsv_colname,
527 msv_colname
528 FROM gl_je_headers jeh, gl_ledgers lgr
529 WHERE jeh.je_header_id = header_id
530 AND lgr.ledger_id = jeh.ledger_id;
531
532 -- Bug fix 6761820.
533 -- Modified the statement to handle the balancing segment value ' '.
534 delete_bsv_stmt :=
535 'DELETE FROM gl_je_segment_values sv ' ||
536 'WHERE sv.segment_type_code = ''B'' ' ||
537 'AND sv.je_header_id = :header_id ' ||
538 'AND ((sv.segment_value <> '' '' AND NOT EXISTS ' ||
539 '(SELECT 1 '||
540 'FROM gl_je_lines jel, gl_code_combinations cc '||
541 'WHERE jel.je_header_id = :header_id '||
542 'AND cc.code_combination_id = jel.code_combination_id '||
543 'AND cc.'|| bsv_colname || ' = sv.segment_value)) ' ||
544 'OR (sv.segment_value = '' '' and NOT EXISTS ' ||
545 '(SELECT 1 FROM gl_je_lines jel ' ||
546 'WHERE jel.je_header_id = :header_id ' ||
547 'AND jel.code_combination_id in (-1,-2,-3))))';
548
549 END IF;
550
551 EXECUTE IMMEDIATE delete_bsv_stmt USING header_id, header_id, header_id;
552
553 IF (msv_colname IS NOT NULL) THEN
554 -- Bug fix 6761820.
555 -- Modified the statement to handle the management segment value ' '.
556 delete_msv_stmt :=
557 'DELETE FROM gl_je_segment_values sv ' ||
558 'WHERE sv.segment_type_code = ''M'' ' ||
559 'AND sv.je_header_id = :header_id ' ||
560 'AND ((sv.segment_value <> '' '' AND NOT EXISTS ' ||
561 '(SELECT 1 ' ||
562 'FROM gl_je_lines jel, gl_code_combinations cc ' ||
563 'WHERE jel.je_header_id = :header_id ' ||
564 'AND cc.code_combination_id = jel.code_combination_id ' ||
565 'AND cc.'|| msv_colname || ' = sv.segment_value)) ' ||
566 'OR (sv.segment_value = '' '' and NOT EXISTS ' ||
567 '(SELECT 1 FROM gl_je_lines jel ' ||
568 'WHERE jel.je_header_id = :header_id ' ||
569 'AND jel.code_combination_id in (-1,-2,-3))))';
570
571 EXECUTE IMMEDIATE delete_msv_stmt USING header_id, header_id, header_id;
572
573 END IF;
574
575 EXCEPTION
576 WHEN app_exceptions.application_exception THEN
577 RAISE;
578 WHEN OTHERS THEN
579 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
580 fnd_message.set_token('PROCEDURE',
581 'gl_je_segment_values.add_segment_value');
582 RAISE;
583 END cleanup_segment_values;
584
585 -- **********************************************************************
586
587 FUNCTION insert_alc_segment_values( x_prun_id NUMBER,
588 x_last_updated_by NUMBER,
589 x_last_update_login NUMBER )
590 RETURN NUMBER
591 IS
592 num_rows NUMBER;
593 BEGIN
594
595 -- Insert distinct segment values
596 INSERT INTO GL_JE_SEGMENT_VALUES
597 (je_header_id, segment_type_code, segment_value, creation_date,
598 created_by, last_update_date, last_updated_by, last_update_login)
599 SELECT LINE.je_header_id,
600 DECODE(X.multiplier,
601 1,'B',
602 2,'M'),
603 DECODE(DECODE(X.multiplier,
604 1,LDG.bal_seg_column_name,
605 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
606 'SEGMENT2',cc.SEGMENT2,
607 'SEGMENT3',cc.SEGMENT3,
608 'SEGMENT4',cc.SEGMENT4,
609 'SEGMENT5',cc.SEGMENT5,
610 'SEGMENT6',cc.SEGMENT6,
611 'SEGMENT7',cc.SEGMENT7,
612 'SEGMENT8',cc.SEGMENT8,
613 'SEGMENT9',cc.SEGMENT9,
614 'SEGMENT10',cc.SEGMENT10,
615 'SEGMENT11',cc.SEGMENT11,
616 'SEGMENT12',cc.SEGMENT12,
617 'SEGMENT13',cc.SEGMENT13,
618 'SEGMENT14',cc.SEGMENT14,
619 'SEGMENT15',cc.SEGMENT15,
620 'SEGMENT16',cc.SEGMENT16,
621 'SEGMENT17',cc.SEGMENT17,
622 'SEGMENT18',cc.SEGMENT18,
623 'SEGMENT19',cc.SEGMENT19,
624 'SEGMENT20',cc.SEGMENT20,
625 'SEGMENT21',cc.SEGMENT21,
626 'SEGMENT22',cc.SEGMENT22,
627 'SEGMENT23',cc.SEGMENT23,
628 'SEGMENT24',cc.SEGMENT24,
629 'SEGMENT25',cc.SEGMENT25,
630 'SEGMENT26',cc.SEGMENT26,
631 'SEGMENT27',cc.SEGMENT27,
632 'SEGMENT28',cc.SEGMENT28,
633 'SEGMENT29',cc.SEGMENT29,
634 'SEGMENT30',cc.SEGMENT30),
635 sysdate, x_last_updated_by, sysdate,
636 x_last_updated_by, x_last_update_login
637 FROM gl_je_batches JB,
638 gl_je_headers JH,
639 gl_je_lines LINE,
640 gl_code_combinations CC,
641 gl_ledgers LDG,
642 gl_row_multipliers X
643 WHERE JB.posting_run_id = x_prun_id
644 AND JB.status = 'I'
645 AND JH.je_batch_id = JB.je_batch_id
646 AND JH.display_alc_journal_flag = 'N'
647 AND JH.parent_je_header_id IS NOT NULL
648 AND LINE.je_header_id = JH.je_header_id
649 AND LDG.ledger_id = JH.ledger_id
650 AND CC.code_combination_id = LINE.code_combination_id
651 AND X.multiplier IN (1,Decode(LDG.mgt_seg_column_name,NULL,NULL,2))
652 GROUP by LINE.je_header_id,
653 DECODE(X.multiplier,
654 1,'B',
655 2,'M'),
656 DECODE(DECODE(X.multiplier,
657 1,LDG.bal_seg_column_name,
658 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
659 'SEGMENT2',cc.SEGMENT2,
660 'SEGMENT3',cc.SEGMENT3,
661 'SEGMENT4',cc.SEGMENT4,
662 'SEGMENT5',cc.SEGMENT5,
663 'SEGMENT6',cc.SEGMENT6,
664 'SEGMENT7',cc.SEGMENT7,
665 'SEGMENT8',cc.SEGMENT8,
666 'SEGMENT9',cc.SEGMENT9,
667 'SEGMENT10',cc.SEGMENT10,
668 'SEGMENT11',cc.SEGMENT11,
669 'SEGMENT12',cc.SEGMENT12,
670 'SEGMENT13',cc.SEGMENT13,
671 'SEGMENT14',cc.SEGMENT14,
672 'SEGMENT15',cc.SEGMENT15,
673 'SEGMENT16',cc.SEGMENT16,
674 'SEGMENT17',cc.SEGMENT17,
675 'SEGMENT18',cc.SEGMENT18,
676 'SEGMENT19',cc.SEGMENT19,
677 'SEGMENT20',cc.SEGMENT20,
678 'SEGMENT21',cc.SEGMENT21,
679 'SEGMENT22',cc.SEGMENT22,
680 'SEGMENT23',cc.SEGMENT23,
681 'SEGMENT24',cc.SEGMENT24,
682 'SEGMENT25',cc.SEGMENT25,
683 'SEGMENT26',cc.SEGMENT26,
684 'SEGMENT27',cc.SEGMENT27,
685 'SEGMENT28',cc.SEGMENT28,
686 'SEGMENT29',cc.SEGMENT29,
687 'SEGMENT30',cc.SEGMENT30);
688
689 -- Return the number of distinct balancing and management segment values inserted.
690 num_rows := SQL%ROWCOUNT;
691 RETURN (num_rows);
692
693 EXCEPTION
694 WHEN app_exceptions.application_exception THEN
695 RAISE;
696 WHEN OTHERS THEN
697 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
698 fnd_message.set_token('PROCEDURE',
699 'gl_je_segment_values_pkg.insert_alc_segment_values');
700 RAISE;
701 END insert_alc_segment_values;
702
703 -- **********************************************************************
704
705 FUNCTION insert_gen_line_segment_values( x_je_header_id NUMBER,
706 x_from_je_line_num NUMBER,
707 x_last_updated_by NUMBER,
708 x_last_update_login NUMBER )
709 RETURN NUMBER
710 IS
711 num_rows NUMBER;
712 BEGIN
713 -- Insert new values only if needed
714 INSERT INTO GL_JE_SEGMENT_VALUES
715 (je_header_id, segment_type_code, segment_value, creation_date,
716 created_by, last_update_date, last_updated_by, last_update_login)
717 SELECT LINE.je_header_id,
721 DECODE(DECODE(X.multiplier,
718 DECODE(X.multiplier,
719 1,'B',
720 2,'M'),
722 1,LDG.bal_seg_column_name,
723 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
724 'SEGMENT2',cc.SEGMENT2,
725 'SEGMENT3',cc.SEGMENT3,
726 'SEGMENT4',cc.SEGMENT4,
727 'SEGMENT5',cc.SEGMENT5,
728 'SEGMENT6',cc.SEGMENT6,
729 'SEGMENT7',cc.SEGMENT7,
730 'SEGMENT8',cc.SEGMENT8,
731 'SEGMENT9',cc.SEGMENT9,
732 'SEGMENT10',cc.SEGMENT10,
733 'SEGMENT11',cc.SEGMENT11,
734 'SEGMENT12',cc.SEGMENT12,
735 'SEGMENT13',cc.SEGMENT13,
736 'SEGMENT14',cc.SEGMENT14,
737 'SEGMENT15',cc.SEGMENT15,
738 'SEGMENT16',cc.SEGMENT16,
739 'SEGMENT17',cc.SEGMENT17,
740 'SEGMENT18',cc.SEGMENT18,
741 'SEGMENT19',cc.SEGMENT19,
742 'SEGMENT20',cc.SEGMENT20,
743 'SEGMENT21',cc.SEGMENT21,
744 'SEGMENT22',cc.SEGMENT22,
745 'SEGMENT23',cc.SEGMENT23,
746 'SEGMENT24',cc.SEGMENT24,
747 'SEGMENT25',cc.SEGMENT25,
748 'SEGMENT26',cc.SEGMENT26,
749 'SEGMENT27',cc.SEGMENT27,
750 'SEGMENT28',cc.SEGMENT28,
751 'SEGMENT29',cc.SEGMENT29,
752 'SEGMENT30',cc.SEGMENT30),
753 sysdate, x_last_updated_by, sysdate,
754 x_last_updated_by, x_last_update_login
755 FROM gl_je_headers JH,
756 gl_je_lines LINE,
757 gl_ledgers LDG,
758 gl_code_combinations CC,
759 gl_row_multipliers X
760 WHERE JH.je_header_id = x_je_header_id
761 AND JH.display_alc_journal_flag IS NULL
762 AND LINE.je_header_id = x_je_header_id
763 AND LINE.je_line_num >= x_from_je_line_num
764 AND LDG.ledger_id = LINE.ledger_id
765 AND CC.code_combination_id = LINE.code_combination_id
766 AND X.multiplier IN (1,Decode(LDG.mgt_seg_column_name,NULL,NULL,2))
767 AND NOT EXISTS (SELECT 'X'
768 FROM GL_JE_SEGMENT_VALUES SV
769 WHERE SV.je_header_id = LINE.je_header_id
770 AND SV.segment_type_code = DECODE(X.multiplier,
771 1,'B',
772 2,'M')
773 AND SV.segment_value = DECODE(DECODE(X.multiplier,
774 1,LDG.bal_seg_column_name,
775 2,LDG.mgt_seg_column_name),
776 'SEGMENT1',cc.SEGMENT1,
777 'SEGMENT2',cc.SEGMENT2,
778 'SEGMENT3',cc.SEGMENT3,
779 'SEGMENT4',cc.SEGMENT4,
780 'SEGMENT5',cc.SEGMENT5,
781 'SEGMENT6',cc.SEGMENT6,
782 'SEGMENT7',cc.SEGMENT7,
783 'SEGMENT8',cc.SEGMENT8,
784 'SEGMENT9',cc.SEGMENT9,
785 'SEGMENT10',cc.SEGMENT10,
786 'SEGMENT11',cc.SEGMENT11,
787 'SEGMENT12',cc.SEGMENT12,
788 'SEGMENT13',cc.SEGMENT13,
789 'SEGMENT14',cc.SEGMENT14,
790 'SEGMENT15',cc.SEGMENT15,
791 'SEGMENT16',cc.SEGMENT16,
792 'SEGMENT17',cc.SEGMENT17,
793 'SEGMENT18',cc.SEGMENT18,
794 'SEGMENT19',cc.SEGMENT19,
795 'SEGMENT20',cc.SEGMENT20,
796 'SEGMENT21',cc.SEGMENT21,
797 'SEGMENT22',cc.SEGMENT22,
798 'SEGMENT23',cc.SEGMENT23,
799 'SEGMENT24',cc.SEGMENT24,
800 'SEGMENT25',cc.SEGMENT25,
801 'SEGMENT26',cc.SEGMENT26,
802 'SEGMENT27',cc.SEGMENT27,
803 'SEGMENT28',cc.SEGMENT28,
804 'SEGMENT29',cc.SEGMENT29,
805 'SEGMENT30',cc.SEGMENT30))
806 GROUP by LINE.je_header_id,
807 DECODE(X.multiplier,
808 1,'B',
809 2,'M'),
810 DECODE(DECODE(X.multiplier,
811 1,LDG.bal_seg_column_name,
812 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
813 'SEGMENT2',cc.SEGMENT2,
814 'SEGMENT3',cc.SEGMENT3,
815 'SEGMENT4',cc.SEGMENT4,
816 'SEGMENT5',cc.SEGMENT5,
817 'SEGMENT6',cc.SEGMENT6,
818 'SEGMENT7',cc.SEGMENT7,
819 'SEGMENT8',cc.SEGMENT8,
820 'SEGMENT9',cc.SEGMENT9,
821 'SEGMENT10',cc.SEGMENT10,
822 'SEGMENT11',cc.SEGMENT11,
823 'SEGMENT12',cc.SEGMENT12,
824 'SEGMENT13',cc.SEGMENT13,
825 'SEGMENT14',cc.SEGMENT14,
826 'SEGMENT15',cc.SEGMENT15,
827 'SEGMENT16',cc.SEGMENT16,
828 'SEGMENT17',cc.SEGMENT17,
829 'SEGMENT18',cc.SEGMENT18,
830 'SEGMENT19',cc.SEGMENT19,
831 'SEGMENT20',cc.SEGMENT20,
832 'SEGMENT21',cc.SEGMENT21,
833 'SEGMENT22',cc.SEGMENT22,
834 'SEGMENT23',cc.SEGMENT23,
835 'SEGMENT24',cc.SEGMENT24,
836 'SEGMENT25',cc.SEGMENT25,
837 'SEGMENT26',cc.SEGMENT26,
838 'SEGMENT27',cc.SEGMENT27,
839 'SEGMENT28',cc.SEGMENT28,
840 'SEGMENT29',cc.SEGMENT29,
841 'SEGMENT30',cc.SEGMENT30);
842
843
844 -- Return the number of distinct balancing and management segment values inserted.
845 num_rows := SQL%ROWCOUNT;
846 RETURN (num_rows);
847
848 EXCEPTION
849 WHEN app_exceptions.application_exception THEN
850 RAISE;
851 WHEN OTHERS THEN
852 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
853 fnd_message.set_token('PROCEDURE',
854 'gl_je_segment_values_pkg.insert_gen_line_segment_values');
855 RAISE;
856 END insert_gen_line_segment_values;
857
858 -- **********************************************************************
859
860 FUNCTION insert_sl_segment_values( x_prun_id NUMBER,
861 x_last_updated_by NUMBER,
862 x_last_update_login NUMBER )
863 RETURN NUMBER
864 IS
865 num_rows NUMBER;
866 BEGIN
867
868 -- Insert distinct segment values
869 INSERT INTO GL_JE_SEGMENT_VALUES
870 (je_header_id, segment_type_code, segment_value, creation_date,
871 created_by, last_update_date, last_updated_by, last_update_login)
872 SELECT SLJEL.je_header_id,
873 DECODE(X.multiplier,
874 1,'B',
875 2,'M'),
876 DECODE(DECODE(X.multiplier,
877 1,LDG.bal_seg_column_name,
878 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
879 'SEGMENT2',cc.SEGMENT2,
880 'SEGMENT3',cc.SEGMENT3,
881 'SEGMENT4',cc.SEGMENT4,
882 'SEGMENT5',cc.SEGMENT5,
883 'SEGMENT6',cc.SEGMENT6,
884 'SEGMENT7',cc.SEGMENT7,
885 'SEGMENT8',cc.SEGMENT8,
886 'SEGMENT9',cc.SEGMENT9,
887 'SEGMENT10',cc.SEGMENT10,
888 'SEGMENT11',cc.SEGMENT11,
889 'SEGMENT12',cc.SEGMENT12,
890 'SEGMENT13',cc.SEGMENT13,
891 'SEGMENT14',cc.SEGMENT14,
892 'SEGMENT15',cc.SEGMENT15,
893 'SEGMENT16',cc.SEGMENT16,
894 'SEGMENT17',cc.SEGMENT17,
895 'SEGMENT18',cc.SEGMENT18,
896 'SEGMENT19',cc.SEGMENT19,
897 'SEGMENT20',cc.SEGMENT20,
898 'SEGMENT21',cc.SEGMENT21,
899 'SEGMENT22',cc.SEGMENT22,
900 'SEGMENT23',cc.SEGMENT23,
901 'SEGMENT24',cc.SEGMENT24,
902 'SEGMENT25',cc.SEGMENT25,
903 'SEGMENT26',cc.SEGMENT26,
904 'SEGMENT27',cc.SEGMENT27,
905 'SEGMENT28',cc.SEGMENT28,
906 'SEGMENT29',cc.SEGMENT29,
907 'SEGMENT30',cc.SEGMENT30),
908 sysdate, x_last_updated_by, sysdate,
909 x_last_updated_by, x_last_update_login
910 FROM gl_je_batches JEB,
911 gl_je_headers JEH,
912 gl_je_headers SLJEH,
913 gl_je_lines SLJEL,
914 gl_code_combinations CC,
915 gl_ledgers LDG,
916 gl_row_multipliers X
917 WHERE JEB.posting_run_id = x_prun_id
918 AND JEB.status = 'I'
919 AND JEH.je_batch_id = JEB.je_batch_id
920 AND SLJEH.parent_je_header_id = JEH.je_header_id
921 AND SLJEH.display_alc_journal_flag IS NULL
922 AND SLJEL.je_header_id = SLJEH.je_header_id
923 AND LDG.ledger_id = SLJEH.ledger_id
924 AND CC.code_combination_id = SLJEL.code_combination_id
925 AND X.multiplier IN (1,Decode(LDG.mgt_seg_column_name,NULL,NULL,2))
926 GROUP by SLJEL.je_header_id,
927 DECODE(X.multiplier,
928 1,'B',
929 2,'M'),
930 DECODE(DECODE(X.multiplier,
931 1,LDG.bal_seg_column_name,
932 2,LDG.mgt_seg_column_name), 'SEGMENT1',cc.SEGMENT1,
933 'SEGMENT2',cc.SEGMENT2,
934 'SEGMENT3',cc.SEGMENT3,
935 'SEGMENT4',cc.SEGMENT4,
936 'SEGMENT5',cc.SEGMENT5,
937 'SEGMENT6',cc.SEGMENT6,
938 'SEGMENT7',cc.SEGMENT7,
939 'SEGMENT8',cc.SEGMENT8,
940 'SEGMENT9',cc.SEGMENT9,
941 'SEGMENT10',cc.SEGMENT10,
942 'SEGMENT11',cc.SEGMENT11,
943 'SEGMENT12',cc.SEGMENT12,
944 'SEGMENT13',cc.SEGMENT13,
945 'SEGMENT14',cc.SEGMENT14,
946 'SEGMENT15',cc.SEGMENT15,
947 'SEGMENT16',cc.SEGMENT16,
948 'SEGMENT17',cc.SEGMENT17,
949 'SEGMENT18',cc.SEGMENT18,
950 'SEGMENT19',cc.SEGMENT19,
951 'SEGMENT20',cc.SEGMENT20,
952 'SEGMENT21',cc.SEGMENT21,
953 'SEGMENT22',cc.SEGMENT22,
954 'SEGMENT23',cc.SEGMENT23,
955 'SEGMENT24',cc.SEGMENT24,
956 'SEGMENT25',cc.SEGMENT25,
957 'SEGMENT26',cc.SEGMENT26,
958 'SEGMENT27',cc.SEGMENT27,
959 'SEGMENT28',cc.SEGMENT28,
960 'SEGMENT29',cc.SEGMENT29,
961 'SEGMENT30',cc.SEGMENT30);
962
963
964 -- Return the number of distinct balancing and management segment values inserted.
965 num_rows := SQL%ROWCOUNT;
966 RETURN (num_rows);
967
968 EXCEPTION
969 WHEN app_exceptions.application_exception THEN
970 RAISE;
971 WHEN OTHERS THEN
972 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
973 fnd_message.set_token('PROCEDURE',
974 'gl_je_segment_values_pkg.insert_sl_segment_values');
975 RAISE;
976 END insert_sl_segment_values;
977
978 END GL_JE_SEGMENT_VALUES_PKG;