[Home] [Help]
PACKAGE BODY: APPS.GL_FLATTEN_SETUP_DATA
Source
1 PACKAGE BODY GL_FLATTEN_SETUP_DATA AS
2 /* $Header: gluflsdb.pls 120.17 2005/09/01 00:03:37 spala noship $ */
3
4
5
6 --********************************************************************
7 -- Private function.
8 FUNCTION Check_Seg_val_Hierarchy(x_mode VARCHAR2,
9 x_vs_id NUMBER)
10 RETURN BOOLEAN IS
11 l_cont_processing BOOLEAN;
12 Seg_val_Hier_err EXCEPTION;
13 result_val BOOLEAN;
14 tab_val_vs VARCHAR2(30);
15 tab_val_VS_col VARCHAR2(30);
16
17 BEGIN
18
19
20
21 GL_MESSAGE.Func_Ent(func_name =>
22 'GL_FLATTEN_SETUP_DATA.Check_Seg_val_Hierarchy');
23
24 GLSTFL_VS_ID := x_vs_id ;
25 GL_MESSAGE.Write_Log(msg_name => 'Value Set is '||GLSTFL_VS_ID,
26 token_num => 0);
27
28 -- Call routine to check if the value set is a table
29 -- validated set.
30 result_val := GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info
31 (X_Vs_Id => GLSTFL_VS_ID,
32 Table_Name => tab_val_vs,
33 Column_Name => tab_val_vs_col);
34
35 IF (NOT result_val) THEN
36 RAISE Seg_val_Hier_err;
37 END IF;
38
39 GLSTFL_VS_TAB_NAME := tab_val_vs;
40 GLSTFL_VS_COL_NAME := tab_val_vs_col;
41 -- Request exclusive lock on the value set ID
42 result_val := GL_FLATTEN_SETUP_DATA.Request_Lock
43 (X_Param_Type => 'V',
44 X_Param_Id => GLSTFL_VS_ID,
45 X_Lock_Mode => 6, -- EXCLUSIVE mode
46 X_Keep_Looping => TRUE,
47 X_Max_Trys => 5);
48
49 IF (NOT result_val) THEN
50 RAISE Seg_val_Hier_err;
51 END IF;
52
53 -- Call routine to fix value set and segment value hierarchies first.
54 result_val := GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier
55 (Is_Seg_Hier_Changed => l_cont_processing);
56
57 IF (NOT result_val) THEN
58 RAISE Seg_val_Hier_err;
59 END IF;
60
61 -- Release exclusive lock on the value set ID
62 result_val := GL_FLATTEN_SETUP_DATA.Release_Lock
63 (X_Param_Type => 'V',
64 X_Param_Id => GLSTFL_VS_ID);
65
66 IF (NOT result_val) THEN
67 RAISE Seg_val_Hier_err;
68 END IF;
69
70
71 -- Call routine to clean up value set and
72 -- segment value hierarchies first.
73 result_val := GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up;
74
75 IF (NOT result_val) THEN
76 RAISE Seg_val_Hier_err;
77 END IF;
78
79 GL_MESSAGE.Func_Succ(func_name =>
80 'GL_FLATTEN_SETUP_DATA.Check_Seg_val_Hierarchy');
81
82 Return True;
83
84 EXCEPTION
85 WHEN Seg_val_Hier_err THEn
86 -- Release exclusive lock on the value set ID
87 result_val := GL_FLATTEN_SETUP_DATA.Release_Lock
88 (X_Param_Type => 'V',
89 X_Param_Id => GLSTFL_VS_ID);
90 GL_MESSAGE.Func_Fail(func_name =>
91 'GL_FLATTEN_SETUP_DATA.Check_Seg_val_Hierarchy');
92 Return False;
93
94 END Check_Seg_val_Hierarchy;
95
96 -- ********************************************************************
97
98 PROCEDURE Main(X_Mode VARCHAR2,
99 X_Mode_Parameter VARCHAR2,
100 X_Debug VARCHAR2 DEFAULT NULL) IS
101 GLSTFL_fatal_err EXCEPTION;
102 ret_val BOOLEAN;
103 cont_processing BOOLEAN;
104 is_vs_tab_validated BOOLEAN := FALSE;
105 vs_tab_name VARCHAR2(240) := NULL;
106 vs_col_name VARCHAR2(240) := NULL;
107 row_count NUMBER := 0;
108 l_dmode_profile fnd_profile_option_values.profile_option_value%TYPE;
109 rval BOOLEAN := FALSE;
110 dummy1 VARCHAR2(2) := NULL;
111 dummy2 VARCHAR2(2) := NULL;
112 schema VARCHAR2(30):= NULL;
113
114 BEGIN
115
116 GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.Main');
117
118 -- Obtain user ID, login ID and concurrent request ID and initialize
119
120 -- package variables
121
122 GLSTFL_USER_ID := FND_GLOBAL.User_Id;
123 GLSTFL_LOGIN_ID := FND_GLOBAL.Login_Id;
124 GLSTFL_REQ_ID := FND_GLOBAL.Conc_Request_Id;
125
126 -- If any of the above values is not set, error out
127 IF (GLSTFL_USER_ID is NULL OR
128 GLSTFL_LOGIN_ID is NULL OR
129 GLSTFL_REQ_ID is NULL) THEN
130
131 -- Fail to initialize
132 GL_MESSAGE.Write_Log(msg_name => 'FLAT0011',
133 token_num => 0);
134
135 RAISE GLSTFL_fatal_err;
136 END IF;
137
138 FND_PROFILE.GET('GL_DEBUG_MODE', l_dmode_profile);
139
140 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
141 token_num => 3 ,
142 t1 =>'ROUTINE',
143 v1 =>
144 'GL_FLATTEN_SETUP_DATA.Main',
145 t2 =>'VARIABLE',
146 v2 =>'Application Profile Debug Mode:',
147 t3 =>'VALUE',
148 v3 => l_dmode_profile);
149
150 -- Determine if process will be run in debug mode
151 IF (NVL(X_Debug, 'N') <> 'N') OR (l_dmode_profile = 'Y') THEN
152 GLSTFL_Debug := TRUE;
153 ELSE
154 GLSTFL_Debug := FALSE;
155 END IF;
156
157
158 -- Turn trace on if process is run in debug mode
159 IF (GLSTFL_Debug) THEN
160
161 -- Program running in debug mode, turning trace on
162 GL_MESSAGE.Write_Log(msg_name => 'FLAT0012',
163 token_num => 0);
164
165 EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
166
167 END IF;
168
169 -- Initialize other package variables based on operation mode
170 -- Valid operation modes are:
171 -- SH: Value set and Segment Value Hierarchy Maintenance
172 -- FF: Value Set Maintenance Only
173 -- LV: Ledger Segment Values Maintenance
174 -- LH: Ledger Hierarchy Maintenance -- obsolete
175 -- VH: Ledger Segment Values and Hierarchy Maintenance
176 -- LS: Explicit Ledger Sets Maintenance on ledger assignments
177 -- VS: Explicit Ledger Sets Maintenance on both ledger
178 -- assignments and segment value assignments.
179 -- AS: Explicit Access Sets Maintenance
180
181 IF (X_Mode IN ('SH', 'FF')) THEN
182 GLSTFL_OP_MODE := X_Mode;
183 GLSTFL_VS_ID := TO_NUMBER(X_Mode_Parameter);
184
185 -- Call routine to check if the value set is a table
186 -- validated set.
187 ret_val := GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info
188 (X_Vs_Id => GLSTFL_VS_ID,
189 Table_Name => vs_tab_name,
190 Column_Name => vs_col_name);
191
192 IF (NOT ret_val) THEN
193 RAISE GLSTFL_fatal_err;
194 END IF;
195
196 GLSTFL_VS_TAB_NAME := vs_tab_name;
197 GLSTFL_VS_COL_NAME := vs_col_name;
198
199 ELSIF (X_Mode IN ('LV', 'LH', 'VH', 'LS', 'AS', 'VS')) THEN
200 GLSTFL_OP_MODE := X_Mode;
201 GLSTFL_COA_ID:= TO_NUMBER(X_Mode_Parameter);
202
203 -- Populate the value set IDs of the balancing and management
204 -- segments for this chart of accounts
205 SELECT bal_seg_value_set_id, mgt_seg_value_set_id
206 INTO GLSTFL_BAL_VS_ID, GLSTFL_MGT_VS_ID
207 FROM GL_LEDGERS
208 WHERE chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
209 AND rownum = 1;
210
211 ELSE
212 -- Invalid Operation mode, error out
213 GL_MESSAGE.Write_Log(msg_name => 'FLAT0013',
214 token_num => 0);
215
216 RAISE GLSTFL_fatal_err;
217
218 END IF;
219
220 -- Print out program parameters
221 GL_MESSAGE.Write_Log(msg_name => 'FLAT0014',
222 token_num => 5,
223 t1 => 'REQ_ID',
224 v1 => TO_CHAR(GLSTFL_REQ_ID),
225 t2 => 'OP_MODE',
226 v2 => GLSTFL_OP_MODE,
227 t3 => 'COA_ID',
228 v3 => TO_CHAR(GLSTFL_COA_ID),
229 t4 => 'VS_ID',
230 v4 => TO_CHAR(GLSTFL_VS_ID),
231 t5 => 'VS_TAB_NAME',
232 v5 => GLSTFL_VS_TAB_NAME);
233
234 GL_MESSAGE.Write_Log(msg_name => 'FLAT0018',
235 token_num => 3,
236 t1 => 'BAL_VS_ID',
237 v1 => TO_CHAR(GLSTFL_BAL_VS_ID),
238 t2 => 'MGT_VS_ID',
239 v2 => TO_CHAR(GLSTFL_MGT_VS_ID),
240 t3 => 'DEBUG_MODE',
241 v3 => NVL(X_Debug, 'N'));
242
243 -- Obtain the appropriate locks depending on the operation mode
244 IF (GLSTFL_OP_MODE IN ('SH', 'FF')) THEN
245
246 -- Request exclusive lock on the value set ID
247 ret_val := GL_FLATTEN_SETUP_DATA.Request_Lock
248 (X_Param_Type => 'V',
249 X_Param_Id => GLSTFL_VS_ID,
250 X_Lock_Mode => 6, -- EXCLUSIVE mode
251 X_Keep_Looping => TRUE,
252 X_Max_Trys => 5);
253
254 IF (NOT ret_val) THEN
255 RAISE GLSTFL_fatal_err;
256 END IF;
257
258 ELSIF (GLSTFL_OP_MODE IN ('LV', 'LH', 'VH', 'LS', 'AS', 'VS')) THEN
259
260 -- Request exclusive lock on the chart of accounts ID
261 ret_val := GL_FLATTEN_SETUP_DATA.Request_Lock
262 (X_Param_Type => 'C',
263 X_Param_Id => GLSTFL_COA_ID,
264 X_Lock_Mode => 6, -- EXCLUSIVE mode
265 X_Keep_Looping => TRUE,
266 X_Max_Trys => 5);
267
268 IF (NOT ret_val) THEN
269 RAISE GLSTFL_fatal_err;
270 END IF;
271
272 END IF;
273
274 -- Populate the REQUEST_ID column of the various norm tables based
275 -- on the operation mode.
276 -- 1) GL_LEDGER_NORM_SEG_VALS: LV, VH, VS
277 -- 2) GL_LEDGER___NORM___HIERARCHY: VH, LH
278 -- 3) GL_LEDGER_SET_NORM_ASSIGN: VS, LS
279 -- 4) GL_ACCESS_SET_NORM_ASSIGN: AS
280 IF (GLSTFL_OP_MODE IN ('LV', 'VH', 'VS')) THEN
281 IF (GLSTFL_Debug) THEN
282 GL_MESSAGE.Write_Log
283 (msg_name => 'SHRD0180',
284 token_num => 2,
285 t1 => 'ROUTINE',
286 v1 => 'Main()',
287 t2 => 'ACTION',
288 v2 => 'Locking down changed records in ' ||
289 'GL_LEDGER_NORM_SEG_VALS by populating ' ||
290 'the REQUEST_ID column...');
291 END IF;
292
293 UPDATE GL_LEDGER_NORM_SEG_VALS
294 SET request_id = GLSTFL_REQ_ID
295 WHERE status_code is NOT NULL
296 AND ledger_id IN ( SELECT LEDGER_ID FROM GL_LEDGERS
297 WHERE chart_of_accounts_id =
298 GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);
299
300 IF (GLSTFL_Debug) THEN
301 row_count := SQL%ROWCOUNT;
302 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
303 token_num => 2,
304 t1 => 'NUM',
305 v1 => TO_CHAR(row_count),
306 t2 => 'TABLE',
307 v2 => 'GL_LEDGER_NORM_SEG_VALS');
308
309 row_count := 0;
310 END IF;
311 END IF;
312
313 IF (GLSTFL_OP_MODE IN ('LS', 'VS')) THEN
314 IF (GLSTFL_Debug) THEN
315 GL_MESSAGE.Write_Log
316 (msg_name => 'SHRD0180',
317 token_num => 2,
318 t1 => 'ROUTINE',
319 v1 => 'Main()',
320 t2 => 'ACTION',
321 v2 => 'Locking down changed records in ' ||
322 'GL_LEDGER_SET_NORM_ASSIGN by populating ' ||
323 'the REQUEST_ID column...');
324 END IF;
325
326 UPDATE GL_LEDGER_SET_NORM_ASSIGN
327 SET request_id = GLSTFL_REQ_ID
328 WHERE status_code is NOT NULL;
329
330 IF (GLSTFL_Debug) THEN
331 row_count := SQL%ROWCOUNT;
332 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
333 token_num => 2,
334 t1 => 'NUM',
335 v1 => TO_CHAR(row_count),
336 t2 => 'TABLE',
337 v2 => 'GL_LEDGER_SET_NORM_ASSIGN');
338
339 row_count := 0;
340 END IF;
341 END IF;
342
343 IF (GLSTFL_OP_MODE = 'AS') THEN
344 IF (GLSTFL_Debug) THEN
345 GL_MESSAGE.Write_Log
346 (msg_name => 'SHRD0180',
347 token_num => 2,
348 t1 => 'ROUTINE',
349 v1 => 'Main()',
350 t2 => 'ACTION',
351 v2 => 'Locking down changed records in ' ||
352 'GL_ACCESS_SET_NORM_ASSIGN by populating ' ||
353 'the REQUEST_ID column...');
354 END IF;
355
356 UPDATE GL_ACCESS_SET_NORM_ASSIGN
357 SET request_id = GLSTFL_REQ_ID
358 WHERE status_code is NOT NULL;
359
360 IF (GLSTFL_Debug) THEN
361 row_count := SQL%ROWCOUNT;
362 GL_MESSAGE.Write_Log(msg_name => 'SHRD0118',
363 token_num => 2,
364 t1 => 'NUM',
365 v1 => TO_CHAR(row_count),
366 t2 => 'TABLE',
367 v2 => 'GL_ACCESS_SET_NORM_ASSIGN');
368
369 row_count := 0;
370 END IF;
371 END IF;
372
373 -- Commit all work
374 FND_CONCURRENT.Af_Commit;
375
376 -- Start processing work according to the mode of operation.
377 --
378 -- Here is the list of routines called by each mode:
379 -- 1) Modes SH, FF:
380 -- GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier
381 -- GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set
382 -- GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table
383 -- 2) Mode LV:
384 -- GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa
385 -- 3) Mode LH:
386 -- GL_FLATTEN_LEDGER_HIERARCHIES.Flatten_Ledger_Hier
387 -- GL_FLATTEN_LEDGER_SETS.Fix_Implicit_Sets
388 -- GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets
389 -- 4) Mode LS:
390 -- GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets
391 -- GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table
392 -- 5) Mode VS:
393 -- GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa
394 -- GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets
395 -- GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table
396 -- 6) Mode AS:
397 -- GL_FLATTEN_ACCESS_SETS.Fix_Explicit_Sets
398 -- 7) Mode VH:
399 -- GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa
400 -- GL_FLATTEN_LEDGER_HIERARCHIES.Flatten_Ledger_Hier
401 -- GL_FLATTEN_LEDGER_SETS.Fix_Implicit_Sets
402 -- GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets
403
404 IF (GLSTFL_OP_MODE IN ('SH', 'FF')) THEN
405
406 -- Call routine to fix value set and segment value hierarchies first.
407 ret_val := GL_FLATTEN_SEG_VAL_HIERARCHIES.Flatten_Seg_Val_Hier
408 (Is_Seg_Hier_Changed => cont_processing);
409
410 IF (NOT ret_val) THEN
411 RAISE GLSTFL_fatal_err;
412 END IF;
413
414 -- Call routine to fix ledger/segment value assignments
415 -- only if some changes occurred in the segment value
416 -- hierarchies
417 IF (cont_processing) THEN
418
419 -- print out debug message
420 IF (GLSTFL_Debug) THEN
421 GL_MESSAGE.Write_Log(msg_name => 'FLAT0015',
422 token_num => 0);
423 END IF;
424
425 ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Value_Set;
426
427 IF (NOT ret_val) THEN
428 RAISE GLSTFL_fatal_err;
429 END IF;
430
431 -- Call routine to fix GL_ACCESS_SET_ASSIGNMENTS
432 ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table;
433
434 IF (NOT ret_val) THEN
435 RAISE GLSTFL_fatal_err;
436 END IF;
437 END IF;
438
439 ELSIF (GLSTFL_OP_MODE = 'LV') THEN
440
441 -- Call routine to fix ledger/segment value assignments
442 ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa;
443
444 IF (NOT ret_val) THEN
445 RAISE GLSTFL_fatal_err;
446 END IF;
447
448 -- LH mode is obsolete
449
450
451 -- ELSIF (GLSTFL_OP_MODE = 'LH') THEN
452
453 -- Call routine to fix ledger hierarchies first
454 -- ret_val := GL_FLATTEN_LEDGER_HIERARCHIES.Flatten_Ledger_Hier
455 -- (Is_Ledger_Hier_Changed => cont_processing);
456
457 IF (NOT ret_val) THEN
458 RAISE GLSTFL_fatal_err;
459 END IF;
460 -- Commneted out becuase it is not required to call for Ledger Hierararchy
461 -- IF (cont_processing) THEN
462
463 -- Call routine to fix implicit ledger sets
464 --ret_val := GL_FLATTEN_LEDGER_SETS.Fix_Implicit_Sets;
465
466 -- IF (NOT ret_val) THEN
467 -- RAISE GLSTFL_fatal_err;
468 -- END IF;
469 -- END IF;
470
471 -- Call routine to fix implicit access sets
472 -- ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets
473 -- (Any_Ledger_Hier_Changes => cont_processing);
474
475 -- IF (NOT ret_val) THEN
476 -- RAISE GLSTFL_fatal_err;
477 -- END IF;
478
479 ELSIF (GLSTFL_OP_MODE = 'LS') THEN
480
481 -- Call routine to fix explicit ledger sets
482
483 ret_val := GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets;
484
485 IF (NOT ret_val) THEN
486 RAISE GLSTFL_fatal_err;
487 END IF;
488
489 -- Call routine to fix GL_ACCESS_SET_ASSIGNMENTS
490 ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table;
491
492 IF (NOT ret_val) THEN
493 RAISE GLSTFL_fatal_err;
494 END IF;
495
496 ELSIF (GLSTFL_OP_MODE = 'VS') THEN
497
498 -- Call routine to fix explicit ledger sets
499 ret_val := GL_FLATTEN_LEDGER_SETS.Fix_Explicit_Sets;
500
501 IF (NOT ret_val) THEN
502 RAISE GLSTFL_fatal_err;
503 END IF;
504
505 -- Call routine to fix GL_ACCESS_SET_ASSIGNMENTS
506 ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Flattened_Table;
507
508 IF (NOT ret_val) THEN
509 RAISE GLSTFL_fatal_err;
510 END IF;
511
512 -- Call routine to fix ledger/segment value assignments
513 ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa;
514
515 IF (NOT ret_val) THEN
516 RAISE GLSTFL_fatal_err;
517 END IF;
518
519 ELSIF (GLSTFL_OP_MODE = 'AS') THEN
520
521 -- Call routine to fix explicit access sets
522 ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Explicit_Sets;
523
524 IF (NOT ret_val) THEN
525 RAISE GLSTFL_fatal_err;
526 END IF;
527
528 ELSIF (GLSTFL_OP_MODE = 'VH') THEN
529
530 -- This is the combination of both modes LV and LH.
531 -- So this mode will do the work of those 2 modes combined.
532
533
534 -- The following check is to insure there will be
535 -- rows in GL_SEG_VAL_HIERARCHIES when a newly created chart of
536 -- accounts is used in a Ledger/BSV assignment.
537
538 -- Actually this is a cornor case. A new value set is created and
539 -- included this value set in a new Chart Of Accounts. Immediately
540 -- assigned this COA to a newly created Ledger. At this time there
541 -- will be no rows populated for Balancing segment and management
542 -- segment of this COA in GL_SEG_VAL_HIERARCHIES. Flattening program
543 -- could not be launched until a new value or a hierarchy change
544 -- happens to a value set.
545
546
547 ret_val := Check_Seg_val_Hierarchy(x_mode => 'SH',
548 x_vs_id => GLSTFL_BAL_VS_ID);
549
550 IF (NOT ret_val) THEN
551 RAISE GLSTFL_fatal_err;
552 END IF;
553
554 IF (GLSTFL_BAL_VS_ID <> GLSTFL_MGT_VS_ID) THEN
555 ret_val := Check_Seg_val_Hierarchy(x_mode => 'SH',
556 x_vs_id => GLSTFL_MGT_VS_ID);
557
558 IF (NOT ret_val) THEN
559 RAISE GLSTFL_fatal_err;
560 END IF;
561 END IF;
562
563 -- Call routine to fix ledger hierarchies
564 -- ret_val := GL_FLATTEN_LEDGER_HIERARCHIES.Flatten_Ledger_Hier
565 -- (Is_Ledger_Hier_Changed => cont_processing);
566
567 -- IF (NOT ret_val) THEN
568 -- RAISE GLSTFL_fatal_err;
569 -- END IF;
570
571 -- There are no ledger Hierarchies in the flattening program.
572 -- IF (cont_processing) THEN
573 -- Call routine to fix implicit ledger sets
574
575 -- ret_val := GL_FLATTEN_LEDGER_SETS.Fix_Implicit_Sets;
576 --
577 -- IF (NOT ret_val) THEN
578 -- RAISE GLSTFL_fatal_err;
579 -- END IF;
580 -- END IF;
581
582 /*------------------------------------------------------------------+
583 | Added the following assignment after removing the ledger |
584 | hierarchy calls from the GL_FLATTEN_ACCESS_SETS package |
585 +------------------------------------------------------------------*/
586 cont_processing := TRUE;
587
588 -- Call routine to fix implicit access sets
589 ret_val := GL_FLATTEN_ACCESS_SETS.Fix_Implicit_Sets
590 (Any_Ledger_Hier_Changes => cont_processing);
591
592 IF (NOT ret_val) THEN
593 RAISE GLSTFL_fatal_err;
594 END IF;
595
596 -- Call routine to fix ledger/segment value assignments
597 ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Fix_By_Coa;
598
599 IF (NOT ret_val) THEN
600 RAISE GLSTFL_fatal_err;
601 END IF;
602
603 END IF;
604
605 -- Call Clean_Up
606 IF (NOT Clean_Up) THEN
607 RAISE GLSTFL_fatal_err;
608 END IF;
609
610 -- Perform full refresh on materialized view GL_ACCESS_SET_LEDGERS
611 IF (GLSTFL_OP_MODE NOT IN ('SH', 'FF', 'LV')) THEN
612 GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.MV_Refresh');
613
614 rval := fnd_installation.get_app_info('SQLGL', dummy1, dummy2, schema);
615
616 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
617 token_num => 3 ,
618 t1 =>'ROUTINE',
619 v1 =>
620 'GL_FLATTEN_SETUP_DATA.MV_Refresh',
621 t2 =>'VARIABLE',
622 v2 =>'schema',
623 t3 =>'VALUE',
624 v3 => schema);
625
626 DBMS_MVIEW.Refresh('GL_ACCESS_SET_LEDGERS');
627
628 GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.MV_Refresh');
629
630 END IF;
631
632 -- Release all locks
633 IF (GLSTFL_OP_MODE IN ('SH', 'FF')) THEN
634
635 -- Release exclusive lock on the value set ID
636 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
637 (X_Param_Type => 'V',
638 X_Param_Id => GLSTFL_VS_ID);
639
640 IF (NOT ret_val) THEN
641 RAISE GLSTFL_fatal_err;
642 END IF;
643
644 ELSIF (GLSTFL_OP_MODE IN ('LV', 'LH', 'VH', 'LS', 'AS', 'VS')) THEN
645
646 -- Release exclusive lock on the chart of accounts ID
647 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
648 (X_Param_Type => 'C',
649 X_Param_Id => GLSTFL_COA_ID);
650
651 IF (NOT ret_val) THEN
652 RAISE GLSTFL_fatal_err;
653 END IF;
654
655 -- Also release the shared lock on both balancing and management
656 -- segments
657 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
658 (X_Param_Type => 'V',
659 X_Param_Id => GLSTFL_BAL_VS_ID);
660
661 IF (NOT ret_val) THEN
662 RAISE GLSTFL_fatal_err;
663 END IF;
664
665 -- Relese the second shared lock iff bal_vs_id <> mgt_vs_id
666 -- IF (GLSTFL_BAL_VS_ID <> GLSTFL_MGT_VS_ID) THEN
667
668 /* To support optional management segment value set */
669 IF (GLSTFL_MGT_VS_ID) IS NOT NULL THEN
670 IF (GLSTFL_BAL_VS_ID <> GLSTFL_MGT_VS_ID) THEN
671 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
672 (X_Param_Type => 'V',
673 X_Param_Id => GLSTFL_MGT_VS_ID);
674
675 IF (NOT ret_val) THEN
676 RAISE GLSTFL_fatal_err;
677 END IF;
678 END IF;
679 END IF;
680 END IF;
681
682 GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.Main');
683
684 ret_val := FND_CONCURRENT.Set_Completion_Status
685 (status => 'COMPLETE', message => NULL);
686
687 -- Exception handling
688 EXCEPTION
689 WHEN GLSTFL_fatal_err THEN
690 -- Release locks
691 IF (GLSTFL_OP_MODE IN ('SH', 'FF')) THEN
692 -- Release exclusive lock on the value set ID
693 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
694 (X_Param_Type => 'V',
695 X_Param_Id => GLSTFL_VS_ID);
696 ELSIF (GLSTFL_OP_MODE IN ('LV', 'LH', 'VH', 'LS', 'AS', 'VS')) THEN
697 -- Release exclusive lock on the chart of accounts ID
698 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
699 (X_Param_Type => 'C',
700 X_Param_Id => GLSTFL_COA_ID);
701
702 -- Also release the shared lock on both balancing and management
703 -- segments
704 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
705 (X_Param_Type => 'V',
706 X_Param_Id => GLSTFL_BAL_VS_ID);
707
708 -- Relese the second shared lock iff bal_vs_id <> mgt_vs_id
709 --IF (GLSTFL_BAL_VS_ID <> GLSTFL_MGT_VS_ID) THEN
710 /* To support optional management segment value set */
711 IF (GLSTFL_MGT_VS_ID) IS NOT NULL THEN
712 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
713 (X_Param_Type => 'V',
714 X_Param_Id => GLSTFL_MGT_VS_ID);
715 END IF;
716 END IF;
717
718 GL_MESSAGE.Write_Log
719 (msg_name => 'FLAT0002',
720 token_num => 1,
721 t1 => 'ROUTINE_NAME',
722 v1 => 'GL_FLATTEN_SETUP_DATA.Main()');
723
724 GL_MESSAGE.Func_Fail(func_name => 'GL_FLATTEN_SETUP_DATA.Main');
725
726 ret_val := FND_CONCURRENT.Set_Completion_Status
727 (status => 'ERROR', message => NULL);
728
729 WHEN OTHERS THEN
730 -- Release locks
731 IF (GLSTFL_OP_MODE IN ('SH', 'FF')) THEN
732 -- Release exclusive lock on the value set ID
733 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
734 (X_Param_Type => 'V',
735 X_Param_Id => GLSTFL_VS_ID);
736 ELSIF (GLSTFL_OP_MODE IN ('LV', 'LH', 'VH', 'LS', 'AS', 'VS')) THEN
737 -- Release exclusive lock on the chart of accounts ID
738 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
739 (X_Param_Type => 'C',
740 X_Param_Id => GLSTFL_COA_ID);
741
742 -- Also release the shared lock on both balancing and management
743 -- segments
744 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
745 (X_Param_Type => 'V',
746 X_Param_Id => GLSTFL_BAL_VS_ID);
747
748 -- Relese the second shared lock iff bal_vs_id <> mgt_vs_id
749 --IF (GLSTFL_BAL_VS_ID <> GLSTFL_MGT_VS_ID) THEN
750 /* To support optional management segment value set */
751 IF (GLSTFL_MGT_VS_ID) IS NOT NULL THEN
752 ret_val := GL_FLATTEN_SETUP_DATA.Release_Lock
753 (X_Param_Type => 'V',
754 X_Param_Id => GLSTFL_MGT_VS_ID);
755 END IF;
756 END IF;
757
758 GL_MESSAGE.Write_Log(msg_name => 'SHRD0203',
759 token_num => 2,
760 t1 => 'FUNCTION',
761 v1 => 'GL_FLATTEN_SETUP_DATA.Main()',
762 t2 => 'SQLERRMC',
763 v2 => SQLERRM);
764
765 GL_MESSAGE.Func_Fail(func_name => 'GL_FLATTEN_SETUP_DATA.Main');
766
767 ret_val := FND_CONCURRENT.Set_Completion_Status
768 (status => 'ERROR', message => NULL);
769
770 END Main;
771
772 -- ********************************************************************
773
774 PROCEDURE Main(errbuf OUT NOCOPY VARCHAR2,
775 retcode OUT NOCOPY VARCHAR2,
776 X_Mode VARCHAR2,
777 X_Mode_Parameter VARCHAR2,
778 X_Debug VARCHAR2 DEFAULT NULL) IS
779 BEGIN
780 GL_FLATTEN_SETUP_DATA.Main(X_Mode => X_Mode,
781 X_Mode_Parameter => X_Mode_Parameter,
782 X_Debug => X_Debug);
783 EXCEPTION
784 WHEN OTHERS THEN
785 errbuf := SQLERRM ;
786 retcode := '2';
787 -- l_message := errbuf;
788 -- FND_FILE.put_line(FND_FILE.LOG,l_message);
789 app_exception.raise_exception;
790 END Main;
791
792 -- ******************************************************************
793
794 FUNCTION Clean_Up RETURN BOOLEAN IS
795 ret_val BOOLEAN;
796 GLSTFL_fatal_err EXCEPTION;
797 BEGIN
798
799 GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.Clean_Up');
800
801 -- Start cleaning up according to the mode of operation.
802 --
803 -- Here is the list of routines called by each mode:
804 -- 1) Modes SH, FF:
805 -- GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up
806 -- GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Value_Set
807 -- 2) Mode LV:
808 -- GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa
809 -- 3) Mode LH:
810 -- GL_FLATTEN_LEDGER_HIERARCHIES.Clean_Up
811 -- GL_FLATTEN_LEDGER_SETS.Clean_Up_Implicit_Sets
812 -- GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa
813 -- 4) Mode LS:
814 -- GL_FLATTEN_LEDGER_SETS.Clean_Up_Explicit_Sets
815 -- GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa
816 -- 5) Mode VS:
817 -- GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa
818 -- GL_FLATTEN_LEDGER_SETS.Clean_Up_Explicit_Sets
819 -- GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa
820 -- 6) Mode AS:
821 -- GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa
822 -- 7) Mode VH:
823 -- GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa
824 -- GL_FLATTEN_LEDGER_HIERARCHIES.Clean_Up
825 -- GL_FLATTEN_LEDGER_SETS.Clean_Up_Implicit_Sets
826 -- GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa
827
828 IF (GLSTFL_OP_MODE IN ('SH', 'FF')) THEN
829
830 -- Call routine to clean up value set and
831 -- segment value hierarchies first.
832 ret_val := GL_FLATTEN_SEG_VAL_HIERARCHIES.Clean_Up;
833
834 IF (NOT ret_val) THEN
835 RAISE GLSTFL_fatal_err;
836 END IF;
837
838 -- Call routine to clean up ledger/segment value assignments
839 ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Value_Set;
840
841 IF (NOT ret_val) THEN
842 RAISE GLSTFL_fatal_err;
843 END IF;
844
845 ELSIF (GLSTFL_OP_MODE = 'LV') THEN
846
847 -- Call routine to clean up ledger/segment value assignments
848 ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa;
849
850 IF (NOT ret_val) THEN
851 RAISE GLSTFL_fatal_err;
852 END IF;
853
854 -- ELSIF (GLSTFL_OP_MODE = 'LH') THEN
855
856 -- Call routine to clean up ledger hierarchies first
857 -- ret_val := GL_FLATTEN_LEDGER_HIERARCHIES.Clean_Up;
858
859 -- IF (NOT ret_val) THEN
860 -- RAISE GLSTFL_fatal_err;
861 -- END IF;
862
863 -- Call routine to clean up implicit ledger sets
864 -- ret_val := GL_FLATTEN_LEDGER_SETS.Clean_Up_Implicit_Sets;
865
866 -- IF (NOT ret_val) THEN
867 -- RAISE GLSTFL_fatal_err;
868 -- END IF;
869
870 -- Call routine to clean up access assignments
871 -- ret_val := GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa;
872
873 -- IF (NOT ret_val) THEN
874 -- RAISE GLSTFL_fatal_err;
875 -- END IF;
876
877 ELSIF (GLSTFL_OP_MODE = 'LS') THEN
878
879 -- Call routine to clean up explicit ledger sets
880 ret_val := GL_FLATTEN_LEDGER_SETS.Clean_Up_Explicit_Sets;
881
882 IF (NOT ret_val) THEN
883 RAISE GLSTFL_fatal_err;
884 END IF;
885
886 -- Call routine to clean up access assignments
887 ret_val := GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa;
888
889 IF (NOT ret_val) THEN
890 RAISE GLSTFL_fatal_err;
891 END IF;
892
893 ELSIF (GLSTFL_OP_MODE = 'VS') THEN
894
895 -- Call routine to clean up explicit ledger sets
896 ret_val := GL_FLATTEN_LEDGER_SETS.Clean_Up_Explicit_Sets;
897
898 IF (NOT ret_val) THEN
899 RAISE GLSTFL_fatal_err;
900 END IF;
901
902 -- Call routine to clean up access assignments
903 ret_val := GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa;
904
905 IF (NOT ret_val) THEN
906 RAISE GLSTFL_fatal_err;
907 END IF;
908
909 -- Call routine to clean up ledger/segment value assignments
910 ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa;
911
912 IF (NOT ret_val) THEN
913 RAISE GLSTFL_fatal_err;
914 END IF;
915
916
917 ELSIF (GLSTFL_OP_MODE = 'AS') THEN
918
919 -- Call routine to clean up access assignments
920 ret_val := GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa;
921
922 IF (NOT ret_val) THEN
923 RAISE GLSTFL_fatal_err;
924 END IF;
925
926 ELSIF (GLSTFL_OP_MODE = 'VH') THEN
927
928 -- This is the combination of both modes LV and LH.
929 -- So this mode will do the work of those 2 modes combined.
930
931 -- Call routine to clean up ledger/segment value assignments
932 ret_val := GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa;
933
934 IF (NOT ret_val) THEN
935 RAISE GLSTFL_fatal_err;
936 END IF;
937
938 -- Call routine to clean up ledger hierarchies
939 -- ret_val := GL_FLATTEN_LEDGER_HIERARCHIES.Clean_Up;
940
941 -- IF (NOT ret_val) THEN
942 -- RAISE GLSTFL_fatal_err;
943 -- END IF;
944
945 -- Call routine to clean up implicit ledger sets
946 -- ret_val := GL_FLATTEN_LEDGER_SETS.Clean_Up_Implicit_Sets;
947
948 -- IF (NOT ret_val) THEN
949 -- RAISE GLSTFL_fatal_err;
950 -- END IF;
951
952 -- Call routine to clean up access assignments
953 ret_val := GL_FLATTEN_ACCESS_SETS.Clean_Up_By_Coa;
954
955 IF (NOT ret_val) THEN
956 RAISE GLSTFL_fatal_err;
957 END IF;
958
959 END IF;
960
961 -- Commit all work
962 FND_CONCURRENT.Af_Commit;
963
964 GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.Clean_Up');
965
966 RETURN TRUE;
967
968 EXCEPTION
969 WHEN GLSTFL_fatal_err THEN
970
971 GL_MESSAGE.Write_Log(msg_name => 'FLAT0002',
972 token_num => 1,
973 t1 => 'ROUTINE_NAME',
974 v1 => 'GL_FLATTEN_SETUP_DATA.Clean_Up()');
975
976 -- Rollback
977 FND_CONCURRENT.Af_Rollback;
978
979 GL_MESSAGE.Func_Fail(func_name =>'GL_FLATTEN_SETUP_DATA.Clean_Up');
980
981 RETURN FALSE;
982
983 END Clean_Up;
984
985 -- *****************************************************************
986
987 FUNCTION Get_Value_Set_Info( X_Vs_Id NUMBER,
988 Table_Name OUT NOCOPY VARCHAR2,
989 Column_Name OUT NOCOPY VARCHAR2)
990 RETURN BOOLEAN IS
991 tab_name VARCHAR2(240) := NULL;
992 col_name VARCHAR2(240) := NULL;
993 BEGIN
994
995 GL_MESSAGE.Func_Ent
996 (func_name => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info');
997
998 -- Execute statement to determine if the value set is table validated
999 BEGIN
1000 SELECT fvt.application_table_name,
1001 fvt.value_column_name
1002 INTO tab_name,col_name
1003 FROM fnd_flex_validation_tables fvt,
1004 fnd_flex_value_sets fvs
1005 WHERE fvs.flex_value_set_id = X_vs_id
1006 AND fvs.validation_type = 'F'
1007 AND fvt.flex_value_set_id = fvs.flex_value_set_id;
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND THEN
1010 Table_Name := NULL;
1011 Column_Name:= NULL;
1012 END;
1013
1014 IF (tab_name IS NOT NULL) THEN
1015 Table_Name := tab_name;
1016 Column_Name := col_name;
1017 END IF;
1018
1019 GL_MESSAGE.Func_Succ
1020 (func_name => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info');
1021
1022 RETURN TRUE;
1023
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 GL_MESSAGE.Write_Log
1027 (msg_name => 'SHRD0203',
1028 token_num => 2,
1029 t1 => 'FUNCTION',
1030 v1 => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info()',
1031 t2 => 'SQLERRMC',
1032 v2 => SQLERRM);
1033
1034 GL_MESSAGE.Func_Fail
1035 (func_name => 'GL_FLATTEN_SETUP_DATA.Get_Value_Set_Info');
1036
1037 FND_CONCURRENT.Af_Rollback;
1038
1039 RETURN FALSE;
1040
1041 END Get_Value_Set_Info;
1042
1043 -- ******************************************************************
1044
1045 FUNCTION Request_Lock(X_Param_Type VARCHAR2,
1046 X_Param_Id NUMBER,
1047 X_Lock_Mode INTEGER,
1048 X_Keep_Looping BOOLEAN,
1049 X_Max_Trys NUMBER) RETURN BOOLEAN IS
1050 lkname VARCHAR2(128) := NULL;
1051 lkhandle VARCHAR2(128) := NULL;
1052 exp_secs constant INTEGER := 864000;
1053 waittime constant INTEGER := 120;
1054 sleep_time constant NUMBER := 300;
1055 lkresult INTEGER;
1056 GLSTFL_fatal_err EXCEPTION;
1057 got_lock BOOLEAN := FALSE;
1058 trial_num NUMBER := 0;
1059 BEGIN
1060
1061 GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.Request_Lock');
1062
1063 -- generate name for the user defined lock
1064 IF (X_Param_Type = 'C') THEN
1065 lkname := 'GLSTFL_COA_' || TO_CHAR(X_Param_Id);
1066 ELSIF (X_Param_Type = 'V') THEN
1067 lkname := 'GLSTFL_VS_' || TO_CHAR(X_Param_Id);
1068 ELSE
1069 -- Invalid parameter type, print message and error out
1070
1071 -- PARAM_VALUE is not a valid value for parameter PARAM_NAME
1072 GL_MESSAGE.Write_Log(msg_name => 'FLAT0006',
1073 token_num => 2,
1074 t1 => 'PARAM_NAME',
1075 v1 => 'X_Param_Type',
1076 t2 => 'PARAM_VALUE',
1077 v2 => X_Param_Type);
1078
1079 RAISE GLSTFL_fatal_err;
1080 END IF;
1081
1082 -- get Oracle-assigned lock handle
1083 DBMS_LOCK.Allocate_Unique(lockname => lkname,
1084 lockhandle => lkhandle,
1085 expiration_secs => exp_secs);
1086
1087 -- request the lock in a loop. If timeout and X_Keep_Looping is TRUE,
1088 -- put process to sleep for 2 minutes then try again.
1089 -- If process cannot obtain lock after X_Max_Trys, set X_Time_Out to
1090 -- TRUE and exit from the loop.
1091 WHILE (NOT got_lock AND X_Keep_Looping AND
1092 (trial_num <= X_Max_Trys))
1093 LOOP
1094
1095 -- Try to obtain the lock with max. wait time of 2 minutes
1096 lkresult := DBMS_LOCK.Request(lockhandle => lkhandle,
1097 lockmode => X_Lock_Mode,
1098 timeout => waittime);
1099
1100 IF ((lkresult = 0) OR (lkresult = 4)) THEN
1101 -- locking successful
1102 got_lock := TRUE;
1103 ELSIF (lkresult = 1) THEN
1104 -- Timeout, put process to sleep for 5 minutes, then try
1105 -- again. Increment trial_num to track number of attempts
1106 trial_num := trial_num + 1;
1107
1108 -- Cannot obtain user named lock LOCK_NAME, putting the proccess
1109 -- to sleep for SLEEP_TIME minutes before trying again.
1110 GL_MESSAGE.Write_Log(msg_name => 'FLAT0007',
1111 token_num => 2,
1112 t1 => 'LOCK_NAME',
1113 v1 => lkname,
1114 t2 => 'SLEEP_TIME',
1115 v2 => TO_CHAR(sleep_time/60));
1116
1117 DBMS_LOCK.Sleep(seconds => sleep_time);
1118
1119 ELSE
1120 -- Either encounter deadlock, parameter error or illegal lock handle.
1121 -- Print out appropriate message and error out
1122
1123 -- Fatal error occurred when obtaining user named lock LOCK_NAME
1124 GL_MESSAGE.Write_Log(msg_name => 'FLAT0008',
1125 token_num => 1,
1126 t1 => 'LOCK_NAME',
1127 v1 => lkname);
1128 RAISE GLSTFL_fatal_err;
1129 END IF;
1130 END LOOP;
1131
1132 IF (got_lock) THEN
1133 GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.Request_Lock');
1134
1135 RETURN TRUE;
1136 ELSE
1137 -- Cannot obtain lock after maximum number of attempts.
1138 -- Print out appropriate message and raise exception
1139
1140 -- Program failed to obtain user named lock LOCK_NAME after
1141 -- MAX_ATTEMPTS attempts.
1142 GL_MESSAGE.Write_Log(msg_name => 'FLAT0009',
1143 token_num => 2,
1144 t1 => 'LOCK_NAME',
1145 v1 => lkname,
1146 t2 => 'MAX_ATTEMPTS',
1147 v2 => TO_CHAR(X_Max_Trys));
1148 RAISE GLSTFL_fatal_err;
1149 END IF;
1150
1151 EXCEPTION
1152 WHEN GLSTFL_fatal_err THEN
1153
1154 GL_MESSAGE.Write_Log
1155 (msg_name => 'FLAT0002',
1156 token_num => 1,
1157 t1 => 'ROUTINE_NAME',
1158 v1 => 'GL_FLATTEN_SETUP_DATA.Request_Lock()');
1159
1160 GL_MESSAGE.Func_Fail(func_name => 'GL_FLATTEN_SETUP_DATA.Request_Lock');
1161
1162 FND_CONCURRENT.Af_Rollback;
1163
1164 RETURN FALSE;
1165
1166 WHEN OTHERS THEN
1167 GL_MESSAGE.Write_Log
1168 (msg_name => 'SHRD0203',
1169 token_num => 2,
1170 t1 => 'FUNCTION',
1171 v1 => 'GL_FLATTEN_SETUP_DATA.Request_Lock()',
1172 t2 => 'SQLERRMC',
1173 v2 => SQLERRM);
1174
1175 GL_MESSAGE.Func_Fail
1176 (func_name => 'GL_FLATTEN_SETUP_DATA.Request_Lock');
1177
1178 FND_CONCURRENT.Af_Rollback;
1179
1180 RETURN FALSE;
1181
1182 END Request_Lock;
1183
1184 -- ******************************************************************
1185
1186 FUNCTION Release_Lock(X_Param_Type VARCHAR2,
1187 X_Param_Id NUMBER) RETURN BOOLEAN IS
1188 lkname VARCHAR2(128) := NULL;
1189 lkhandle VARCHAR2(128) := NULL;
1190 exp_secs constant INTEGER := 864000;
1191 lkresult INTEGER;
1192 GLSTFL_fatal_err EXCEPTION;
1193 BEGIN
1194
1195 GL_MESSAGE.Func_Ent(func_name => 'GL_FLATTEN_SETUP_DATA.Release_Lock');
1196
1197
1198 -- generate name for the user defined lock
1199 IF (X_Param_Type = 'C') THEN
1200 lkname := 'GLSTFL_COA_' || TO_CHAR(X_Param_Id);
1201 ELSIF (X_Param_Type = 'V') THEN
1202 lkname := 'GLSTFL_VS_' || TO_CHAR(X_Param_Id);
1203 ELSE
1204 -- Invalid parameter type, print message and error out
1205 GL_MESSAGE.Write_Log(msg_name => 'FLAT0006',
1206 token_num => 2,
1207 t1 => 'PARAM_NAME',
1208 v1 => 'X_Param_Type',
1209 t2 => 'PARAM_VALUE',
1210 v2 => X_Param_Type);
1211
1212 RAISE GLSTFL_fatal_err;
1213 END IF;
1214 IF (GL_FLATTEN_SETUP_DATA.GLSTFL_Debug) THEN
1215 GL_MESSAGE.Write_Log
1216 (msg_name => 'SHRD0180',
1217 token_num => 2,
1218 t1 => 'ROUTINE',
1219 v1 => 'Main()',
1220 t2 => 'ACTION',
1221 v2 => lkname);
1222 END IF;
1223 -- get Oracle-assigned lock handle
1224 DBMS_LOCK.Allocate_Unique(lockname => lkname,
1225 lockhandle => lkhandle,
1226 expiration_secs => exp_secs);
1227
1228 -- release the user named lock
1229 lkresult := DBMS_LOCK.Release(lockhandle => lkhandle);
1230
1231 IF (lkresult = 0) THEN
1232 GL_MESSAGE.Func_Succ(func_name => 'GL_FLATTEN_SETUP_DATA.Release_Lock');
1233
1234 RETURN TRUE;
1235 ELSE
1236 -- Errors encountered when releasing the lock
1237 GL_MESSAGE.Write_Log(msg_name => 'FLAT0010',
1238 token_num => 0);
1239 RAISE GLSTFL_fatal_err;
1240 END IF;
1241
1242 EXCEPTION
1243 WHEN GLSTFL_fatal_err THEN
1244
1245 GL_MESSAGE.Write_Log
1246 (msg_name => 'FLAT0002',
1247 token_num => 1,
1248 t1 => 'ROUTINE_NAME',
1249 v1 => 'GL_FLATTEN_SETUP_DATA.Release_Lock()');
1250
1251 GL_MESSAGE.Func_Fail
1252 (func_name =>'GL_FLATTEN_SETUP_DATA.Release_Lock');
1253
1254 FND_CONCURRENT.Af_Rollback;
1255
1256 RETURN FALSE;
1257
1258 WHEN OTHERS THEN
1259 GL_MESSAGE.Write_Log
1260 (msg_name => 'SHRD0203',
1261 token_num => 2,
1262 t1 => 'FUNCTION',
1263 v1 => 'GL_FLATTEN_SETUP_DATA.Release_Lock()',
1264 t2 => 'SQLERRMC',
1265 v2 => SQLERRM);
1266
1267 GL_MESSAGE.Func_Fail
1268 (func_name => 'GL_FLATTEN_SETUP_DATA.Release_Lock');
1269
1270 FND_CONCURRENT.Af_Rollback;
1271
1272 RETURN FALSE;
1273
1274 END Release_Lock;
1275
1276 -- ******************************************************************
1277
1278 -- ******************************************************************
1279 -- Function
1280 -- GL_Flatten_Rule
1281 -- Purpose
1282 -- This Function will be used as a run function for the new
1283 -- business event oracle.apps.fnd.flex.vst.hierarchy.compiled
1284 -- History
1285 -- 10-Oct-2004 Srini pala Created
1286 -- Arguments
1287 -- p_subscription_guid raw unique subscription id
1288 --
1289 -- p_event wf_event_t workflow business event
1290 --
1291 -- Example
1292 -- ret_status := GL_FLATTEN_SETUP_DATA.GL_Flatten_Rule( );
1293 --
1294
1295 FUNCTION GL_FLATTEN_RULE(
1296 p_subscription_guid in raw,
1297 p_event in out nocopy wf_event_t)
1298 RETURN VARCHAR2 IS
1299
1300 i NUMBER;
1301 parmlist wf_parameter_list_t;
1302 req_id NUMBER;
1303 result BOOLEAN;
1304 vs_id NUMBER;
1305
1306 BEGIN
1307
1308 parmlist := p_event.getParameterList();
1309
1310 IF (parmlist is not null) THEN
1311
1312 i := parmlist.FIRST;
1313
1314 WHILE (i <= parmlist.LAST) LOOP
1315
1316 if (parmlist(i).getName() = 'FLEX_VALUE_SET_ID') THEN
1317 vs_id := parmlist(i).getValue();
1318 result := fnd_request.set_options('NO', 'NO', NULL, NULL,NULL);
1319
1320 req_id := FND_REQUEST.Submit_Request(
1321 'SQLGL', 'GLSTFL', '', '', FALSE,
1322 'SH',TO_CHAR(vs_id), 'N',chr(0),
1323 '', '', '', '', '', '', '', '',
1324 '', '', '', '', '', '', '', '', '', '',
1325 '', '', '', '', '', '', '', '', '', '',
1326 '', '', '', '', '', '', '', '', '', '',
1327 '', '', '', '', '', '', '', '', '', '',
1328 '', '', '', '', '', '', '', '', '', '',
1329 '', '', '', '', '', '', '', '', '', '',
1330 '', '', '', '', '', '', '', '', '', '',
1331 '', '', '', '', '', '', '', '', '', '',
1332 '', '', '', '', '', '', '', '');
1333
1334 IF (req_id = 0) THEN
1335
1336 WF_CORE.CONTEXT('GL_FLATTEN_SETUP_DAT','GL_Flatten_Rule',
1337 p_event.getEventName( ), p_subscription_guid);
1338 WF_EVENT.setErrorInfo(p_event, FND_MESSAGE.get);
1339 return 'WARNING';
1340
1341 END IF;
1342
1343 END If;
1344
1345 i := parmlist.NEXT(i);
1346
1347 END LOOP;
1348 END IF;
1349
1350
1351
1352 RETURN 'SUCCESS';
1353
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 WF_CORE.CONTEXT('GL_FLATTEN_SETUP_DAT','GL_Flatten_Rule',
1357 p_event.getEventName( ), p_subscription_guid);
1358 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1359
1360 return 'ERROR';
1361 END GL_Flatten_Rule;
1362
1363
1364 -- ******************************************************************
1365
1366
1367 END GL_FLATTEN_SETUP_DATA;
1368