[Home] [Help]
PACKAGE BODY: APPS.GL_MANAGEMENT_SEGMENT_UPGRADE
Source
1 PACKAGE BODY GL_MANAGEMENT_SEGMENT_UPGRADE AS
2 /* $Header: glumsupb.pls 120.3 2005/05/05 01:41:16 kvora ship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 api_name CONSTANT VARCHAR2(30) := 'GL_MANAGEMENT_SEGMENT_UPGRADE';
8 lock_name_prefix CONSTANT VARCHAR2(15) := 'GL_MGT_SEG_';
9
10 --
11 -- PRIVATE EXCEPTIONS
12 --
13 assign_complete_error EXCEPTION; -- assignment already completed for COA
14 user_lock_error EXCEPTION; -- error related to the user name lock
15 request_error EXCEPTION; -- request submission failed
16
17 --
18 -- PUBLIC FUNCTIONS
19 --
20
21 FUNCTION request_lock(
22 X_Chart_Of_Accounts_Id NUMBER,
23 X_Lock_Mode INTEGER,
24 X_Timeout_Secs INTEGER DEFAULT 1,
25 X_Keep_Trying BOOLEAN DEFAULT FALSE,
26 X_Try_Times NUMBER DEFAULT 1,
27 X_Wait_Secs NUMBER DEFAULT 60) RETURN BOOLEAN
28 IS
29 lock_handle VARCHAR2(128);
30 lock_result INTEGER;
31 BEGIN
32 DBMS_LOCK.allocate_unique(lock_name_prefix ||
33 to_char(X_Chart_Of_Accounts_Id),
34 lock_handle);
35
36 FOR i IN 1..X_Try_Times LOOP
37 lock_result := DBMS_LOCK.request(lock_handle, X_Lock_Mode,
38 X_Timeout_Secs);
39
40 IF (lock_result IN (0, 4)) THEN
41 -- succeeded
42 RETURN TRUE;
43 ELSIF (lock_result = 1) THEN
44 IF (X_Keep_Trying AND i < X_Try_Times) THEN
45 DBMS_LOCK.sleep(X_Wait_Secs);
46 ELSE
47 RETURN FALSE;
48 END IF;
49 ELSE
50 RETURN FALSE;
51 END IF;
52 END LOOP;
53
54 -- Correct call shouldn't ever reach here
55 RETURN FALSE;
56 END request_lock;
57
58
59 FUNCTION release_lock(X_Chart_Of_Accounts_Id NUMBER) RETURN BOOLEAN
60 IS
61 lock_handle VARCHAR2(128);
62 lock_result INTEGER;
63 BEGIN
64 DBMS_LOCK.allocate_unique(lock_name_prefix ||
65 to_char(X_Chart_Of_Accounts_Id),
66 lock_handle);
67 lock_result := DBMS_LOCK.release(lock_handle);
68
69 IF (lock_result IN (0, 4)) THEN
70 RETURN TRUE;
71 ELSE
72 RETURN FALSE;
73 END IF;
74 END release_lock;
75
76
77 PROCEDURE Setup_Upgrade(
78 X_Errbuf OUT NOCOPY VARCHAR2,
79 X_Retcode OUT NOCOPY VARCHAR2,
80 X_Chart_Of_Accounts_Id NUMBER,
81 X_Mgt_Seg_Column_Name VARCHAR2)
82 IS
83 fn_name CONSTANT VARCHAR2(30) := 'Setup_Upgrade';
84
85 status VARCHAR2(1);
86 industry VARCHAR2(1);
87 schema VARCHAR2(30);
88 seq_in_order VARCHAR2(1);
89
90 lock_name VARCHAR2(30);
91 lock_op_succeeded BOOLEAN;
92
93 rerun_flag VARCHAR2(1);
94 l_mgt_seg_column_name VARCHAR2(30);
95 l_assign_complete_flag VARCHAR2(1);
96 new_max_batch_id NUMBER;
97 request_id NUMBER;
98
99 l_user_id NUMBER;
100 l_login_id NUMBER;
101
102 sequence_order_error EXCEPTION;
103 get_sequence_error EXCEPTION;
104 assign_different_error EXCEPTION;
105 BEGIN
106 GL_MESSAGE.func_ent(api_name || '.' || fn_name);
107
108 l_user_id := FND_GLOBAL.user_id;
109 l_login_id := FND_GLOBAL.login_id;
110
111 -- Check je_batch_id in gl_je_batches table in order or not.
112 IF (FND_INSTALLATION.get_app_info('SQLGL', status, industry, schema)) THEN
113
114 SELECT order_flag
115 INTO seq_in_order
116 FROM dba_sequences
117 WHERE sequence_owner = schema
118 AND sequence_name = 'GL_JE_BATCHES_S';
119
120 IF (seq_in_order = 'N') THEN
121 RAISE sequence_order_error;
122 END IF;
123 ELSE
124 RAISE sequence_order_error;
125 END IF;
126
127 -- Acquire user name lock GL_MGT_SEG_<coa_id> in exclusive mode
128 lock_name := lock_name_prefix || to_char(X_Chart_Of_Accounts_Id);
129
130 lock_op_succeeded := request_lock(X_Chart_Of_Accounts_Id, DBMS_LOCK.x_mode,
131 60, TRUE, 10, 120);
132 IF (NOT lock_op_succeeded) THEN
133 RAISE user_lock_error;
134 END IF;
135
136 GL_MESSAGE.write_log('SHRD0209', 1, 'USERLOCK', lock_name);
137
138 -- Check existing record in GL_MGT_SEG_UPGRADE_H
139 SELECT decode(min(chart_of_accounts_id), null, 'N', 'Y'),
140 min(mgt_seg_column_name),
141 min(assign_complete_flag)
142 INTO rerun_flag, l_mgt_seg_column_name, l_assign_complete_flag
143 FROM GL_MGT_SEG_UPGRADE_H
144 WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
145
146 -- If the upgrade is complate for the COA, or the management segment
147 -- selected is different, stop
148 IF (l_assign_complete_flag = 'Y') THEN
149 RAISE assign_complete_error;
150 ELSIF (l_mgt_seg_column_name <> X_Mgt_Seg_Column_Name) THEN
151 RAISE assign_different_error;
152 END IF;
153
154 -- Get a new max_batch_id and insert/update the history record
155 BEGIN
156 SELECT GL_JE_BATCHES_S.nextval
157 INTO new_max_batch_id
158 FROM dual;
159 EXCEPTION
160 WHEN others THEN
161 RAISE get_sequence_error;
162 END;
163
164 IF (rerun_flag = 'N') THEN
165 INSERT INTO GL_MGT_SEG_UPGRADE_H
166 (chart_of_accounts_id,
167 mgt_seg_column_name,
168 assign_complete_flag,
169 max_processed_batch_id,
170 max_batch_id,
171 creation_date,
172 created_by,
173 last_update_date,
174 last_updated_by,
175 last_update_login)
176 VALUES
177 (X_Chart_Of_Accounts_Id,
178 X_Mgt_Seg_Column_Name,
179 'N',
180 null,
181 new_max_batch_id,
182 sysdate,
183 l_user_id,
184 sysdate,
185 l_user_id,
186 l_login_id);
187 ELSE
188 UPDATE GL_MGT_SEG_UPGRADE_H
189 SET max_batch_id = new_max_batch_id
190 WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
191 END IF;
192
193 -- Submit concurrent program
194 request_id := FND_REQUEST.submit_request(
195 application => 'SQLGL',
196 program => 'GLMGT2',
197 argument1 => X_Chart_Of_Accounts_Id);
198
199 IF (request_id = 0) THEN
200 RAISE request_error;
201 ELSE
202 GL_MESSAGE.write_log('SHRD0121', 1, 'REQ_ID', to_char(request_id));
203 END IF;
204
205 COMMIT;
206
207 -- Release user name lock GL_MGT_SEG_<coa_id>
208 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
209
210 GL_MESSAGE.func_succ(api_name || '.' || fn_name);
211
212 EXCEPTION
213 WHEN sequence_order_error THEN
214 GL_MESSAGE.write_log('MGTS0001', 0);
215 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
216 X_Retcode := '2';
217 WHEN get_sequence_error THEN
218 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
219 GL_MESSAGE.write_log('SHRD0050', 2, 'ROUTINE', fn_name,
220 'SEQUENCE', 'GL_JE_BATCHES_S');
221 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
222 X_Retcode := '2';
223 WHEN user_lock_error THEN
224 GL_MESSAGE.write_log('MGTS0005', 1, 'USERLOCK', lock_name);
225 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
226 X_Retcode := '2';
227 WHEN assign_complete_error THEN
228 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
229 GL_MESSAGE.write_log('MGTS0002');
230 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
231 X_Retcode := '1';
232 WHEN assign_different_error THEN
233 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
234 GL_MESSAGE.write_log('MGTS0003');
235 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
236 X_Retcode := '2';
237 WHEN request_error THEN
238 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
239 GL_MESSAGE.write_log('SHRD0055', 1, 'ROUTINE', fn_name);
240 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
241 X_Retcode := '2';
242 WHEN others THEN
243 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
244 FND_FILE.put_line(FND_FILE.LOG, SQLERRM);
245 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
246 X_Retcode := '2';
247 END Setup_Upgrade;
248
249
250
251 PROCEDURE Process_Incremental_Data(
252 X_Errbuf OUT NOCOPY VARCHAR2,
253 X_Retcode OUT NOCOPY VARCHAR2,
254 X_Chart_Of_Accounts_Id NUMBER)
255 IS
256 fn_name CONSTANT VARCHAR2(30) := 'Process_Incremental_Data';
257
258 l_assign_complete_flag VARCHAR2(1);
259 l_mgt_seg_column_name VARCHAR2(30);
260 l_max_proc_batch_id NUMBER;
261 l_max_batch_id NUMBER;
262 low_batch_id NUMBER;
263 high_batch_id NUMBER;
264 batch_size NUMBER := 10000;
265
266 l_user_id NUMBER;
267 l_login_id NUMBER;
268 BEGIN
269 GL_MESSAGE.func_ent(api_name || '.' || fn_name);
270
271 l_user_id := FND_GLOBAL.user_id;
272 l_login_id := FND_GLOBAL.login_id;
273
274 -- Check the record in GL_MGT_SEG_UPGRADE_H
275 SELECT min(assign_complete_flag),
276 min(mgt_seg_column_name),
277 nvl(min(max_processed_batch_id), 0),
278 min(max_batch_id)
279 INTO l_assign_complete_flag, l_mgt_seg_column_name,
280 l_max_proc_batch_id, l_max_batch_id
281 FROM GL_MGT_SEG_UPGRADE_H
282 WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
283
284 IF (l_assign_complete_flag <> 'N') THEN
285 RAISE assign_complete_error;
286 END IF;
287
288 -- Process posted batches that were recorded as unposted in previous run:
289 -- Move posted batch id to _GT
290 INSERT INTO GL_MGT_SEG_UPGRADE_GT
291 (je_batch_id)
292 SELECT msu.je_batch_id
293 FROM GL_MGT_SEG_UPGRADE MSU,
294 GL_JE_BATCHES B
295 WHERE msu.chart_of_accounts_id = X_Chart_Of_Accounts_Id
296 AND b.je_batch_id = msu.je_batch_id
297 AND b.status || '' = 'P';
298
299 -- Process batches in _GT
300 INSERT INTO GL_JE_SEGMENT_VALUES
301 (je_header_id, segment_type_code, segment_value, creation_date,
302 created_by, last_update_date, last_updated_by, last_update_login)
303 SELECT l.je_header_id, 'M',
304 decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
305 'SEGMENT2', cc.segment2,
306 'SEGMENT3', cc.segment3,
307 'SEGMENT4', cc.segment4,
308 'SEGMENT5', cc.segment5,
309 'SEGMENT6', cc.segment6,
310 'SEGMENT7', cc.segment7,
311 'SEGMENT8', cc.segment8,
312 'SEGMENT9', cc.segment9,
313 'SEGMENT10', cc.segment10,
314 'SEGMENT11', cc.segment11,
315 'SEGMENT12', cc.segment12,
316 'SEGMENT13', cc.segment13,
317 'SEGMENT14', cc.segment14,
318 'SEGMENT15', cc.segment15,
319 'SEGMENT16', cc.segment16,
320 'SEGMENT17', cc.segment17,
321 'SEGMENT18', cc.segment18,
322 'SEGMENT19', cc.segment19,
323 'SEGMENT20', cc.segment20,
324 'SEGMENT21', cc.segment21,
325 'SEGMENT22', cc.segment22,
326 'SEGMENT23', cc.segment23,
327 'SEGMENT24', cc.segment24,
328 'SEGMENT25', cc.segment25,
329 'SEGMENT26', cc.segment26,
330 'SEGMENT27', cc.segment27,
331 'SEGMENT28', cc.segment28,
332 'SEGMENT29', cc.segment29,
333 'SEGMENT30', cc.segment30),
334 sysdate, l_user_id, sysdate, l_user_id, l_login_id
335 FROM GL_MGT_SEG_UPGRADE_GT GT,
336 GL_JE_HEADERS H,
337 GL_JE_LINES L,
338 GL_CODE_COMBINATIONS CC
339 WHERE h.je_batch_id = gt.je_batch_id
340 AND l.je_header_id = h.je_header_id
341 AND cc.code_combination_id = l.code_combination_id
342 GROUP BY
343 l.je_header_id,
344 decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
345 'SEGMENT2', cc.segment2,
346 'SEGMENT3', cc.segment3,
347 'SEGMENT4', cc.segment4,
348 'SEGMENT5', cc.segment5,
349 'SEGMENT6', cc.segment6,
350 'SEGMENT7', cc.segment7,
351 'SEGMENT8', cc.segment8,
352 'SEGMENT9', cc.segment9,
353 'SEGMENT10', cc.segment10,
354 'SEGMENT11', cc.segment11,
355 'SEGMENT12', cc.segment12,
356 'SEGMENT13', cc.segment13,
357 'SEGMENT14', cc.segment14,
358 'SEGMENT15', cc.segment15,
359 'SEGMENT16', cc.segment16,
360 'SEGMENT17', cc.segment17,
361 'SEGMENT18', cc.segment18,
362 'SEGMENT19', cc.segment19,
363 'SEGMENT20', cc.segment20,
364 'SEGMENT21', cc.segment21,
365 'SEGMENT22', cc.segment22,
366 'SEGMENT23', cc.segment23,
367 'SEGMENT24', cc.segment24,
368 'SEGMENT25', cc.segment25,
369 'SEGMENT26', cc.segment26,
370 'SEGMENT27', cc.segment27,
371 'SEGMENT28', cc.segment28,
372 'SEGMENT29', cc.segment29,
373 'SEGMENT30', cc.segment30);
374
375 -- Delete from the tracking table the je_batch_id processed
376 DELETE FROM GL_MGT_SEG_UPGRADE MSU
377 WHERE MSU.chart_of_accounts_id = X_Chart_Of_Accounts_Id
378 AND MSU.je_batch_id IN (SELECT gt.je_batch_id
379 FROM GL_MGT_SEG_UPGRADE_GT gt);
380
381 -- Commit
382 COMMIT;
383
384 GL_MESSAGE.write_log('MGTS0006', 1,
385 'BATCHID', to_char(l_max_proc_batch_id));
386
387 -- Batch processing the journals between max_processed_ and max_ batch id:
388 LOOP
389 low_batch_id := l_max_proc_batch_id + 1;
390 high_batch_id := least(l_max_proc_batch_id + batch_size, l_max_batch_id);
391
392 INSERT INTO GL_MGT_SEG_UPGRADE
393 (chart_of_accounts_id, je_batch_id, creation_date,
394 created_by, last_update_date, last_updated_by, last_update_login)
395 SELECT b.chart_of_accounts_id, b.je_batch_id,
396 sysdate, l_user_id, sysdate, l_user_id, l_login_id
397 FROM GL_JE_BATCHES B
398 WHERE b.je_batch_id BETWEEN low_batch_id AND high_batch_id
399 AND b.status <> 'P'
400 AND b.chart_of_accounts_id = X_Chart_Of_Accounts_Id;
401
402 INSERT INTO GL_JE_SEGMENT_VALUES
403 (je_header_id, segment_type_code, segment_value, creation_date,
404 created_by, last_update_date, last_updated_by, last_update_login)
405 SELECT l.je_header_id, 'M',
406 decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
407 'SEGMENT2', cc.segment2,
408 'SEGMENT3', cc.segment3,
409 'SEGMENT4', cc.segment4,
410 'SEGMENT5', cc.segment5,
411 'SEGMENT6', cc.segment6,
412 'SEGMENT7', cc.segment7,
413 'SEGMENT8', cc.segment8,
414 'SEGMENT9', cc.segment9,
415 'SEGMENT10', cc.segment10,
416 'SEGMENT11', cc.segment11,
417 'SEGMENT12', cc.segment12,
418 'SEGMENT13', cc.segment13,
419 'SEGMENT14', cc.segment14,
420 'SEGMENT15', cc.segment15,
421 'SEGMENT16', cc.segment16,
422 'SEGMENT17', cc.segment17,
423 'SEGMENT18', cc.segment18,
424 'SEGMENT19', cc.segment19,
425 'SEGMENT20', cc.segment20,
426 'SEGMENT21', cc.segment21,
427 'SEGMENT22', cc.segment22,
428 'SEGMENT23', cc.segment23,
429 'SEGMENT24', cc.segment24,
430 'SEGMENT25', cc.segment25,
431 'SEGMENT26', cc.segment26,
432 'SEGMENT27', cc.segment27,
433 'SEGMENT28', cc.segment28,
434 'SEGMENT29', cc.segment29,
435 'SEGMENT30', cc.segment30),
436 sysdate, l_user_id, sysdate, l_user_id, l_login_id
437 FROM GL_JE_BATCHES B,
438 GL_JE_HEADERS H,
439 GL_JE_LINES L,
440 GL_CODE_COMBINATIONS CC
441 WHERE b.je_batch_id BETWEEN low_batch_id AND high_batch_id
442 AND b.status || '' = 'P'
443 AND b.chart_of_accounts_id = X_Chart_Of_Accounts_Id
444 AND NOT EXISTS
445 (SELECT msu.je_batch_id
446 FROM GL_MGT_SEG_UPGRADE MSU
447 WHERE msu.chart_of_accounts_id = X_Chart_Of_Accounts_Id
448 AND msu.je_batch_id = b.je_batch_id)
449 AND h.je_batch_id = b.je_batch_id
450 AND l.je_header_id = h.je_header_id
451 AND cc.code_combination_id = l.code_combination_id
452 GROUP BY
453 l.je_header_id,
454 decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
455 'SEGMENT2', cc.segment2,
456 'SEGMENT3', cc.segment3,
457 'SEGMENT4', cc.segment4,
458 'SEGMENT5', cc.segment5,
459 'SEGMENT6', cc.segment6,
460 'SEGMENT7', cc.segment7,
461 'SEGMENT8', cc.segment8,
462 'SEGMENT9', cc.segment9,
463 'SEGMENT10', cc.segment10,
464 'SEGMENT11', cc.segment11,
465 'SEGMENT12', cc.segment12,
466 'SEGMENT13', cc.segment13,
467 'SEGMENT14', cc.segment14,
468 'SEGMENT15', cc.segment15,
469 'SEGMENT16', cc.segment16,
470 'SEGMENT17', cc.segment17,
471 'SEGMENT18', cc.segment18,
472 'SEGMENT19', cc.segment19,
473 'SEGMENT20', cc.segment20,
474 'SEGMENT21', cc.segment21,
475 'SEGMENT22', cc.segment22,
476 'SEGMENT23', cc.segment23,
477 'SEGMENT24', cc.segment24,
478 'SEGMENT25', cc.segment25,
479 'SEGMENT26', cc.segment26,
480 'SEGMENT27', cc.segment27,
481 'SEGMENT28', cc.segment28,
482 'SEGMENT29', cc.segment29,
483 'SEGMENT30', cc.segment30);
484
485 l_max_proc_batch_id := high_batch_id;
486
487 UPDATE GL_MGT_SEG_UPGRADE_H
488 SET max_processed_batch_id = high_batch_id
489 WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
490
491 COMMIT;
492
493 GL_MESSAGE.write_log('MGTS0007', 2,
494 'LOWID', low_batch_id, 'HIGHID', high_batch_id);
495
496 EXIT WHEN (l_max_proc_batch_id = l_max_batch_id);
497 END LOOP;
498
499 GL_MESSAGE.func_succ(api_name || '.' || fn_name);
500
501 EXCEPTION
502 WHEN assign_complete_error THEN
503 GL_MESSAGE.write_log('MGTS0002');
504 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
505 X_Retcode := '1';
506 WHEN others THEN
507 FND_FILE.put_line(FND_FILE.LOG, SQLERRM);
508 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
509 X_Retcode := '2';
510 END Process_Incremental_Data;
511
512
513 PROCEDURE Assign_Management_Segment(
514 X_Errbuf OUT NOCOPY VARCHAR2,
515 X_Retcode OUT NOCOPY VARCHAR2,
516 X_Chart_Of_Accounts_Id NUMBER)
517 IS
518 fn_name CONSTANT VARCHAR2(30) := 'Assign_Management_Segment';
519
520 l_mgt_seg_column_name VARCHAR2(30);
521 l_assign_complete_flag VARCHAR2(1);
522 l_max_proc_batch_id NUMBER;
523
524 l_user_id NUMBER;
525 l_login_id NUMBER;
526
527 lock_name VARCHAR2(30);
528 lock_op_succeeded BOOLEAN;
529
530 struct_code VARCHAR2(30);
531 seg_name VARCHAR2(30);
532 flexfield FND_FLEX_KEY_API.flexfield_type;
533 structure FND_FLEX_KEY_API.structure_type;
534 segment FND_FLEX_KEY_API.segment_type;
535
536 request_id NUMBER;
537
538 max_processed_error EXCEPTION;
539 BEGIN
540 GL_MESSAGE.func_ent(api_name || '.' || fn_name);
541
542 l_user_id := FND_GLOBAL.user_id;
543 l_login_id := FND_GLOBAL.login_id;
544
545 -- Check the record in GL_MGT_SEG_UPGRADE_H
546 SELECT min(mgt_seg_column_name),
547 min(assign_complete_flag),
548 min(max_processed_batch_id)
549 INTO l_mgt_seg_column_name, l_assign_complete_flag, l_max_proc_batch_id
550 FROM GL_MGT_SEG_UPGRADE_H
551 WHERE chart_of_accounts_id = X_Chart_Of_Accounts_id;
552
553 IF (l_assign_complete_flag <> 'N') THEN
554 RAISE assign_complete_error;
555 ELSIF (l_max_proc_batch_id IS NULL) THEN
556 RAISE max_processed_error;
557 END IF;
558
559 -- Acquire user name lock GL_MGT_SEG_<coa_id> in exclusive mode
560 lock_name := lock_name_prefix || to_char(X_Chart_Of_Accounts_Id);
561
562 lock_op_succeeded := request_lock(X_Chart_Of_Accounts_Id, DBMS_LOCK.x_mode,
563 60, TRUE, 10, 120);
564 IF (NOT lock_op_succeeded) THEN
565 RAISE user_lock_error;
566 END IF;
567
568 GL_MESSAGE.write_log('SHRD0209', 1, 'USERLOCK', lock_name);
569
570 -- Process batches in GL_MGT_SEG_UPGRADE
571 INSERT INTO GL_JE_SEGMENT_VALUES
572 (je_header_id, segment_type_code, segment_value, creation_date,
573 created_by, last_update_date, last_updated_by, last_update_login)
574 SELECT l.je_header_id, 'M',
575 decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
576 'SEGMENT2', cc.segment2,
577 'SEGMENT3', cc.segment3,
578 'SEGMENT4', cc.segment4,
579 'SEGMENT5', cc.segment5,
580 'SEGMENT6', cc.segment6,
581 'SEGMENT7', cc.segment7,
582 'SEGMENT8', cc.segment8,
583 'SEGMENT9', cc.segment9,
584 'SEGMENT10', cc.segment10,
585 'SEGMENT11', cc.segment11,
586 'SEGMENT12', cc.segment12,
587 'SEGMENT13', cc.segment13,
588 'SEGMENT14', cc.segment14,
589 'SEGMENT15', cc.segment15,
590 'SEGMENT16', cc.segment16,
591 'SEGMENT17', cc.segment17,
592 'SEGMENT18', cc.segment18,
593 'SEGMENT19', cc.segment19,
594 'SEGMENT20', cc.segment20,
595 'SEGMENT21', cc.segment21,
596 'SEGMENT22', cc.segment22,
597 'SEGMENT23', cc.segment23,
598 'SEGMENT24', cc.segment24,
599 'SEGMENT25', cc.segment25,
600 'SEGMENT26', cc.segment26,
601 'SEGMENT27', cc.segment27,
602 'SEGMENT28', cc.segment28,
603 'SEGMENT29', cc.segment29,
604 'SEGMENT30', cc.segment30),
605 sysdate, l_user_id, sysdate, l_user_id, l_login_id
606 FROM GL_MGT_SEG_UPGRADE MSU,
607 GL_JE_HEADERS H,
608 GL_JE_LINES L,
609 GL_CODE_COMBINATIONS CC
610 WHERE msu.chart_of_accounts_id = X_Chart_Of_Accounts_Id
611 AND h.je_batch_id = msu.je_batch_id
612 AND l.je_header_id = h.je_header_id
613 AND cc.code_combination_id = l.code_combination_id
614 GROUP BY
615 l.je_header_id,
616 decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
617 'SEGMENT2', cc.segment2,
618 'SEGMENT3', cc.segment3,
619 'SEGMENT4', cc.segment4,
620 'SEGMENT5', cc.segment5,
621 'SEGMENT6', cc.segment6,
622 'SEGMENT7', cc.segment7,
623 'SEGMENT8', cc.segment8,
624 'SEGMENT9', cc.segment9,
625 'SEGMENT10', cc.segment10,
626 'SEGMENT11', cc.segment11,
627 'SEGMENT12', cc.segment12,
628 'SEGMENT13', cc.segment13,
629 'SEGMENT14', cc.segment14,
630 'SEGMENT15', cc.segment15,
631 'SEGMENT16', cc.segment16,
632 'SEGMENT17', cc.segment17,
633 'SEGMENT18', cc.segment18,
634 'SEGMENT19', cc.segment19,
635 'SEGMENT20', cc.segment20,
636 'SEGMENT21', cc.segment21,
637 'SEGMENT22', cc.segment22,
638 'SEGMENT23', cc.segment23,
639 'SEGMENT24', cc.segment24,
640 'SEGMENT25', cc.segment25,
641 'SEGMENT26', cc.segment26,
642 'SEGMENT27', cc.segment27,
643 'SEGMENT28', cc.segment28,
644 'SEGMENT29', cc.segment29,
645 'SEGMENT30', cc.segment30);
646
647 GL_MESSAGE.write_log('MGTS0008', 1, 'BATCHID', l_max_proc_batch_id);
648
649 -- Process batches beyond max_processed_batch_id
650 INSERT INTO GL_JE_SEGMENT_VALUES
651 (je_header_id, segment_type_code, segment_value, creation_date,
652 created_by, last_update_date, last_updated_by, last_update_login)
653 SELECT l.je_header_id, 'M',
654 decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
655 'SEGMENT2', cc.segment2,
656 'SEGMENT3', cc.segment3,
657 'SEGMENT4', cc.segment4,
658 'SEGMENT5', cc.segment5,
659 'SEGMENT6', cc.segment6,
660 'SEGMENT7', cc.segment7,
661 'SEGMENT8', cc.segment8,
662 'SEGMENT9', cc.segment9,
663 'SEGMENT10', cc.segment10,
664 'SEGMENT11', cc.segment11,
665 'SEGMENT12', cc.segment12,
666 'SEGMENT13', cc.segment13,
667 'SEGMENT14', cc.segment14,
668 'SEGMENT15', cc.segment15,
669 'SEGMENT16', cc.segment16,
670 'SEGMENT17', cc.segment17,
671 'SEGMENT18', cc.segment18,
672 'SEGMENT19', cc.segment19,
673 'SEGMENT20', cc.segment20,
674 'SEGMENT21', cc.segment21,
675 'SEGMENT22', cc.segment22,
676 'SEGMENT23', cc.segment23,
677 'SEGMENT24', cc.segment24,
678 'SEGMENT25', cc.segment25,
679 'SEGMENT26', cc.segment26,
680 'SEGMENT27', cc.segment27,
681 'SEGMENT28', cc.segment28,
682 'SEGMENT29', cc.segment29,
683 'SEGMENT30', cc.segment30),
684 sysdate, l_user_id, sysdate, l_user_id, l_login_id
685 FROM GL_JE_BATCHES B,
686 GL_JE_HEADERS H,
687 GL_JE_LINES L,
688 GL_CODE_COMBINATIONS CC
689 WHERE b.je_batch_id > l_max_proc_batch_id
690 AND b.chart_of_accounts_id = X_Chart_Of_Accounts_Id
691 AND h.je_batch_id = b.je_batch_id
692 AND l.je_header_id = h.je_header_id
693 AND cc.code_combination_id = l.code_combination_id
694 GROUP BY
695 l.je_header_id,
696 decode(l_mgt_seg_column_name, 'SEGMENT1', cc.segment1,
697 'SEGMENT2', cc.segment2,
698 'SEGMENT3', cc.segment3,
699 'SEGMENT4', cc.segment4,
700 'SEGMENT5', cc.segment5,
701 'SEGMENT6', cc.segment6,
702 'SEGMENT7', cc.segment7,
703 'SEGMENT8', cc.segment8,
704 'SEGMENT9', cc.segment9,
705 'SEGMENT10', cc.segment10,
706 'SEGMENT11', cc.segment11,
707 'SEGMENT12', cc.segment12,
708 'SEGMENT13', cc.segment13,
709 'SEGMENT14', cc.segment14,
710 'SEGMENT15', cc.segment15,
711 'SEGMENT16', cc.segment16,
712 'SEGMENT17', cc.segment17,
713 'SEGMENT18', cc.segment18,
714 'SEGMENT19', cc.segment19,
715 'SEGMENT20', cc.segment20,
716 'SEGMENT21', cc.segment21,
717 'SEGMENT22', cc.segment22,
718 'SEGMENT23', cc.segment23,
719 'SEGMENT24', cc.segment24,
720 'SEGMENT25', cc.segment25,
721 'SEGMENT26', cc.segment26,
722 'SEGMENT27', cc.segment27,
723 'SEGMENT28', cc.segment28,
724 'SEGMENT29', cc.segment29,
725 'SEGMENT30', cc.segment30);
726
727 GL_MESSAGE.write_log('MGTS0009', 1, 'BATCHID', l_max_proc_batch_id);
728
729 -- Get COA structure code and management segment name
730 SELECT st.id_flex_structure_code, sg.segment_name
731 INTO struct_code, seg_name
732 FROM FND_ID_FLEX_STRUCTURES ST,
733 FND_ID_FLEX_SEGMENTS SG
734 WHERE st.application_id = 101
735 AND st.id_flex_code = 'GL#'
736 AND st.id_flex_num = X_Chart_Of_Accounts_Id
737 AND sg.application_id = 101
738 AND sg.id_flex_code = 'GL#'
739 AND sg.id_flex_num = X_Chart_Of_Accounts_Id
740 AND sg.application_column_name = l_mgt_seg_column_name;
741
742 -- Setup FND info and assign the management segment qualifier
743 FND_FLEX_KEY_API.set_session_mode('customer_data');
744 flexfield := FND_FLEX_KEY_API.find_flexfield('SQLGL', 'GL#');
745 structure := FND_FLEX_KEY_API.find_structure(flexfield, struct_code);
746 segment := FND_FLEX_KEY_API.find_segment(flexfield, structure, seg_name);
747 FND_FLEX_KEY_API.assign_qualifier(flexfield, structure, segment,
748 'GL_MANAGEMENT', 'Y');
749
750 -- Update GL_LEDGERS
751 UPDATE GL_LEDGERS
752 SET mgt_seg_column_name = l_mgt_seg_column_name,
753 mgt_seg_value_set_id = segment.value_set_id
754 WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
755
756 -- Assignment completed
757 UPDATE GL_MGT_SEG_UPGRADE_H
758 SET assign_complete_flag = 'Y',
759 max_processed_batch_id = null,
760 max_batch_id = null
761 WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
762
763 COMMIT;
764
765 -- Submit the Compile Flexfield program
766 request_id := FND_REQUEST.submit_request(
767 application => 'FND',
768 program => 'FDFCMPK',
769 argument1 => 'K',
770 argument2 => 'SQLGL',
771 argument3 => 'GL#',
772 argument4 => X_Chart_Of_Accounts_Id);
773
774 IF (request_id = 0) THEN
775 RAISE request_error;
776 ELSE
777 GL_MESSAGE.write_log('SHRD0121', 1, 'REQ_ID', to_char(request_id));
778 END IF;
779
780 -- Release user name lock GL_MGT_SEG_<coa_id>
781 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
782
783 -- Clean up tracking data in GL_MGT_SEG_UPGRADE
784 DELETE FROM GL_MGT_SEG_UPGRADE
785 WHERE chart_of_accounts_id = X_Chart_Of_Accounts_Id;
786
787 GL_MESSAGE.func_succ(api_name || '.' || fn_name);
788 EXCEPTION
789 WHEN max_processed_error THEN
790 GL_MESSAGE.write_log('MGTS0004');
791 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
792 X_Retcode := '2';
793 WHEN assign_complete_error THEN
794 GL_MESSAGE.write_log('MGTS0002');
795 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
796 X_Retcode := '1';
797 WHEN user_lock_error THEN
798 GL_MESSAGE.write_log('MGTS0005', 1, 'USERLOCK', lock_name);
799 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
800 X_Retcode := '2';
801 WHEN request_error THEN
802 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
803 GL_MESSAGE.write_log('SHRD0055', 1, 'ROUTINE', fn_name);
804 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
805 X_Retcode := '2';
806 WHEN others THEN
807 lock_op_succeeded := release_lock(X_Chart_Of_Accounts_Id);
808 FND_FILE.put_line(FND_FILE.LOG, SQLERRM);
809 GL_MESSAGE.func_fail(api_name || '.' || fn_name);
810 X_Retcode := '2';
811 END Assign_Management_Segment;
812
813
814 END GL_MANAGEMENT_SEGMENT_UPGRADE;