[Home] [Help]
PACKAGE BODY: APPS.GL_DRM_INTEGRATION_PKG
Source
1 PACKAGE BODY gl_drm_integration_pkg AS
2 /* $Header: gldrmpkb.pls 120.9 2011/04/21 07:42:56 vnetan noship $ */
3 -- global variables
4 g_debug_mode BOOLEAN DEFAULT TRUE;
5 g_value_set_name FND_FLEX_VALUE_SETS.flex_value_set_name%TYPE;
6 g_value_set_id FND_FLEX_VALUE_SETS.flex_value_set_id%TYPE;
7
8 -- table types to pass values to GL API
9 g_flex_values_tbl GL_COA_SEG_VAL_IMP_PUB.gl_flex_values_tbl_type;
10 g_norm_hierarchy_tbl GL_COA_SEG_VAL_IMP_PUB.gl_flex_values_nh_tbl_type;
11
12 -- type and table used for validating qualifiers
13 TYPE flex_vl_attr_tbl_type IS TABLE OF fnd_flex_validation_qualifiers%ROWTYPE INDEX BY BINARY_INTEGER;
14 g_list_flex_vl_attr flex_vl_attr_tbl_type;
15
16 -- Constants used for writing XML to ouput file
17 XML_TAG_OPEN CONSTANT NUMBER(1) := 1;
18 XML_TAG_CLOSE CONSTANT NUMBER(1) := 2;
19 XML_TEXT_YES CONSTANT VARCHAR2(20) := 'Y';
20 XML_TEXT_NO CONSTANT VARCHAR2(20) := 'N';
21 XT_docRoot CONSTANT VARCHAR2(20) := 'drmLoader';
22 XT_exception CONSTANT VARCHAR2(20) := 'exception';
23 XT_loadDone CONSTANT VARCHAR2(20) := 'loadDone';
24 XT_numOfValImported CONSTANT VARCHAR2(20) := 'numOfValImported';
25 XT_numOfRelImported CONSTANT VARCHAR2(20) := 'numOfRelImported';
26 XT_relationValidation CONSTANT VARCHAR2(20) := 'relationValidation';
27 XT_loadError CONSTANT VARCHAR2(20) := 'loadError';
28 XT_errorMsg CONSTANT VARCHAR2(20) := 'errorMsg';
29 XT_relInError CONSTANT VARCHAR2(20) := 'relInError';
30 XT_parent CONSTANT VARCHAR2(20) := 'parent';
31 XT_child CONSTANT VARCHAR2(20) := 'child';
32 XT_valInError CONSTANT VARCHAR2(20) := 'valInError';
33 XT_valuesValidation CONSTANT VARCHAR2(20) := 'valuesValidation';
34 XT_description CONSTANT VARCHAR2(20) := 'description';
35 XT_value CONSTANT VARCHAR2(20) := 'value';
36
37
38 /* +=======================================================================+
39 * PRIVATE PROCEDURES
40 * write_xml_tag
41 * write_xml_element
42 * write_text
43 * log_message
44 * PURPOSE
45 * To write to concurrent program output and log files.
46 * CHANGE HISTORY
47 * Who When What
48 * vnetan 08/30/2010 Created
49 * +=======================================================================+*/
50 /* Write an xml tag to concurrent program output file */
51 PROCEDURE write_xml_tag (
52 p_tag VARCHAR2,
53 p_type NUMBER
54 ) AS
55 BEGIN
56 IF p_type = XML_TAG_OPEN THEN
57 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<'||p_tag||'>');
58 ELSIF p_type = XML_TAG_CLOSE THEN
59 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</'||p_tag||'>');
60 END IF;
61 END write_xml_tag;
62
63 /* Write an xml text element with delimiter tags to concurrent program output file */
64 PROCEDURE write_xml_element (
65 p_tag VARCHAR2,
66 p_text VARCHAR2
67 ) AS
68 BEGIN
69 IF p_text IS NULL OR p_text = '' THEN
70 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<'||p_tag||' />');
71 ELSE
72 -- replace XML reserved characters before writing XML text node
73 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<'||p_tag||'>'||
74 replace(replace(p_text,'&','&'||'amp;'),'<','&'||'lt;')||
75 '</'||p_tag||'>');
76 END IF;
77 END write_xml_element;
78
79 /* Write log message to concurrent program log file. */
80 PROCEDURE log_message(
81 p_text VARCHAR2
82 ) AS
83
84 BEGIN
85 IF g_debug_mode THEN
86 FND_FILE.PUT_LINE(FND_FILE.Log,To_Char(SYSDATE,'dd-Mon-yy hh24:mi:ss :- ')||p_text);
87 END IF;
88 END log_message;
89
90 /* 11937027: Write plain text to concurrent program output file */
91 /* 12300052: Moved procedure under GL_DRM_EXPORT_PROCESS */
92
93 /* +=======================================================================+
94 * PRIVATE PROCEDURE
95 * list_gl_api_errors
96 * PURPOSE
97 * Lists any errors GL API could have encountered when inserting data
98 * into FND Flex tables
99 * CHANGE HISTORY
100 * Who When What
101 * vnetan 08/30/2010 Created
102 * +=======================================================================+*/
103 PROCEDURE list_gl_api_errors
104 AS
105 l_n_msg_num fnd_new_messages.message_number%TYPE;
106 l_s_msg_txt fnd_new_messages.message_text%TYPE;
107 l_s_msg_name fnd_new_messages.message_name%TYPE;
108 --
109 -- Function to get message from Message Stack
110 --
111 FUNCTION get_msg_from_stack(l_n_msg_count NUMBER)
112 RETURN VARCHAR2 AS
113 l_c_msg VARCHAR2(3000);
114 l_s_msg_name fnd_new_messages.message_name%TYPE;
115 l_appl_name VARCHAR2(30);
116 BEGIN
117 l_c_msg := FND_MSG_PUB.GET(p_msg_index => l_n_msg_count, p_encoded => 'T');
118 FND_MESSAGE.SET_ENCODED (l_c_msg);
119 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_appl_name, l_s_msg_name);
120 RETURN l_s_msg_name;
121 END get_msg_from_stack;
122
123 --
124 -- Procedure to get seeded message text
125 --
126 PROCEDURE get_message(
127 p_c_msg_name VARCHAR2,
128 p_n_msg_num OUT NOCOPY NUMBER,
129 p_c_msg_txt OUT NOCOPY VARCHAR2)
130 AS
131
132 CURSOR c_msg(cp_c_msg_name fnd_new_messages.message_name%TYPE ) IS
133 SELECT
134 message_number,
135 message_text
136 FROM fnd_new_messages
137 WHERE application_id=101
138 AND language_code = UserEnv('LANG')
139 AND message_name=cp_c_msg_name;
140
141 rec_c_msg c_msg%ROWTYPE;
142 BEGIN
143 OPEN c_msg(p_c_msg_name);
144 FETCH c_msg INTO rec_c_msg;
145 IF c_msg%FOUND THEN
146 p_n_msg_num := rec_c_msg.message_number;
147 p_c_msg_txt := rec_c_msg.message_text;
148 ELSE
149 p_c_msg_txt := p_c_msg_name;
150 END IF;
151 CLOSE c_msg;
152 END get_message;
153
154 BEGIN
155 -- Print Flex value status in log file
156 IF g_flex_values_tbl.EXISTS(1)
157 THEN
158 FOR i IN 1..g_flex_values_tbl.LAST
159 LOOP
160 IF g_flex_values_tbl(i).status = 'E'
161 THEN
162 log_message(g_flex_values_tbl(i).flex_value||' -> ');
163 -- Write error into log file
164 FOR l_curr_num IN g_flex_values_tbl(i).msg_from..g_flex_values_tbl(i).msg_to
165 LOOP
166 l_s_msg_name := get_msg_from_stack(l_curr_num);
167 get_message(l_s_msg_name,l_n_msg_num,l_s_msg_txt);
168 log_message(l_n_msg_num||', '||l_s_msg_txt);
169 END LOOP; /* Messages loop */
170 END IF;
171 END LOOP; /* PL/SQL table loop */
172 END IF;
173
174 -- Flex hierarchy status
175 IF g_norm_hierarchy_tbl.EXISTS(1)
176 THEN
177 FOR i IN 1..g_norm_hierarchy_tbl.LAST
178 LOOP
179 IF g_norm_hierarchy_tbl(i).status = 'E'
180 THEN
181 log_message(g_norm_hierarchy_tbl(i).child_flex_value_high||' ->');
182 -- Write error into log file
183 FOR l_curr_num IN g_norm_hierarchy_tbl(i).msg_from..g_norm_hierarchy_tbl(i).msg_to
184 LOOP
185 l_s_msg_name := get_msg_from_stack(l_curr_num);
186 get_message(l_s_msg_name,l_n_msg_num,l_s_msg_txt);
187 log_message(l_n_msg_num||', '||l_s_msg_txt);
188 END LOOP; /* Messages loop */
189 END IF;
190 END LOOP; /* PL/SQL table loop */
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 log_message('>> GL_DRM_INTEGRATION_PKG.list_gl_api_errors --> Unexpected EXCEPTION');
195 RAISE;
196 END list_gl_api_errors;
197
198 /* +=======================================================================+
199 * PRIVATE PROCEDURE
200 * validate_segvalue_attributes
201 * PURPOSE
202 * Checks whether a given valueset has an account type attribute defined
203 * and fetches the position for the account type in compiled value
204 * attribute string.
205 * CHANGE HISTORY
206 * Who When What
207 * vnetan 08/27/2010 Created
208 * +=======================================================================+*/
209 PROCEDURE validate_segvalue_attributes (
210 p_seg_value IN VARCHAR2,
211 p_start_date IN DATE,
212 p_end_date IN DATE,
213 p_enabled_flag IN VARCHAR2,
214 p_summary_flag IN VARCHAR2,
215 p_allow_budgeting IN VARCHAR2,
216 p_allow_posting IN VARCHAR2,
217 p_account_type IN VARCHAR2,
218 p_third_party_ctrl_acct IN VARCHAR2,
219 p_reconcile IN VARCHAR2,
220 p_rollup_group IN VARCHAR2,
221 p_hierarchy_level IN VARCHAR2,
222 x_success OUT NOCOPY BOOLEAN
223 ) AS
224
225 -- Cursor to fetch existing value properties
226 CURSOR c_fnd_flex_values(p_value VARCHAR2) IS
227 SELECT ffv.ROWID, ffv.*
228 FROM fnd_flex_values ffv
229 WHERE ffv.flex_value_set_id = g_value_set_id
230 AND ffv.flex_value = p_value;
231
232 -- Local variables
233 rec_existing_value c_fnd_flex_values%ROWTYPE;
234 l_rollup_id FND_FLEX_HIERARCHIES.hierarchy_id%TYPE;
235 l_old_account_type FND_FLEX_VALUES.compiled_value_attributes%TYPE;
236 l_val_exists BOOLEAN;
237
238 BEGIN
239 x_success := false;
240
241 -- Check if value already exists and fetch properties
242 OPEN c_fnd_flex_values(p_seg_value);
243 FETCH c_fnd_flex_values INTO rec_existing_value;
244 IF c_fnd_flex_values%NOTFOUND
245 THEN
246 l_val_exists := FALSE;
247 ELSE
248 l_val_exists := TRUE;
249 END IF;
250
251 -- Check for enabled flag
252 IF Nvl(p_enabled_flag,'X') NOT IN ('Y','N')
253 THEN
254 IF p_enabled_flag IS NULL THEN
255 log_message('Enabled Flag is not set');
256 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_NOT_SET');
257 ELSE
258 log_message('Invalid Enabled Flag='||p_enabled_flag);
259 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_INVALID');
260 END IF;
261 FND_MESSAGE.set_token('TOKEN_VALUE', 'Enabled Flag');
262 RETURN;
263 END IF;
264
265 -- Check for summary flag
266 IF Nvl(p_summary_flag,'X') NOT IN ('Y','N')
267 THEN
268 IF p_summary_flag IS NULL THEN
269 log_message('Summary Flag is not set');
270 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_NOT_SET');
271 ELSE
272 log_message('Invalid Summary Flag='||p_summary_flag);
273 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_INVALID');
274 END IF;
275 FND_MESSAGE.set_token('TOKEN_VALUE', 'Summary Flag');
276 RETURN;
277 ELSE
278 -- for an existing value check that summary flag does not change
279 IF l_val_exists AND p_summary_flag <> rec_existing_value.SUMMARY_FLAG
280 THEN
281 log_message('Summary differs: Old Value='||rec_existing_value.SUMMARY_FLAG
282 ||', New Value='||p_summary_flag);
283 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_CHANGED');
284 FND_MESSAGE.set_token('TOKEN_VALUE', 'Summary Flag');
285 RETURN;
286 END IF;
287 END IF;
288
289 -- Check rollup_group
290 IF p_rollup_group IS NOT NULL
291 THEN
292 BEGIN
293 SELECT hierarchy_id
294 INTO l_rollup_id
295 FROM fnd_flex_hierarchies ffh
296 WHERE ffh.flex_value_set_id = g_value_set_id
297 AND ffh.hierarchy_code = p_rollup_group;
298 EXCEPTION
299 WHEN No_Data_Found THEN
300 log_message('Invalid Summary Flag='||p_summary_flag);
301 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_INVALID');
302 FND_MESSAGE.set_token('TOKEN_VALUE', 'Roll Up Group');
303 x_success := FALSE;
304 RETURN;
305 END;
306 END IF;
307
308 -- check dates are valid
309 IF Nvl(p_start_date,Nvl(p_end_date,Trunc(SYSDATE))) > Nvl(p_end_date,Trunc(SYSDATE))
310 THEN
311 log_message('Date range is not proper. Start_date='||to_char(p_start_date)
312 ||'End_date='||to_char(p_end_date));
313 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_DATES_INVALID');
314 RETURN;
315 END IF;
316
317 -- Check for GL attributes of a value
318 FOR curr_pos IN 1..g_list_flex_vl_attr.Count
319 LOOP
320 -- check for allow budgeting
321 IF g_list_flex_vl_attr(curr_pos).value_attribute_type = 'DETAIL_BUDGETING_ALLOWED'
322 THEN
323 IF Nvl(p_allow_budgeting,'X') NOT IN ('Y','N')
324 THEN
325 IF p_allow_budgeting IS NOT NULL
326 THEN
327 log_message('Allow_Budgeting is not set');
328 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_INVALID');
329 ELSE
330 log_message('Invalid Allow_Budgeting ='||p_allow_budgeting);
331 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_NOT_SET');
332 END IF;
333 FND_MESSAGE.set_token('TOKEN_VALUE', 'Allow Budgeting');
334 RETURN;
335 END IF; /* end of check for p_allow_budgeting */
336
337 -- check for allow posting
338 ELSIF g_list_flex_vl_attr(curr_pos).value_attribute_type = 'DETAIL_POSTING_ALLOWED'
339 THEN
340 IF Nvl(p_allow_posting,'X') NOT IN ('Y','N')
341 THEN
342 IF p_allow_posting IS NOT NULL
343 THEN
344 log_message('Allow_Posting is not set');
345 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_INVALID');
346 ELSE
347 log_message('Invalid Allow_Posting ='||p_allow_posting);
348 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_NOT_SET');
349 END IF;
350 FND_MESSAGE.set_token('TOKEN_VALUE', 'Allow Posting');
351 RETURN;
352 END IF; /* end of check for p_allow_posting */
353
354 -- check for account type
355 ELSIF g_list_flex_vl_attr(curr_pos).value_attribute_type = 'GL_ACCOUNT_TYPE'
356 THEN
357 IF Nvl(p_account_type,'X') NOT IN ('A','E','L','R','O','C','D')
358 THEN
359 IF p_account_type IS NOT NULL
360 THEN
361 log_message('Invalid account_type ='||p_account_type);
362 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_INVALID');
363 ELSE
364 log_message('account_type is not set');
365 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_NOT_SET');
366 END IF;
367 FND_MESSAGE.set_token('TOKEN_VALUE', 'Account Type');
368 RETURN;
369 END IF; /* end of check for p_account_type */
370
371 -- For an existing value ensure the account type does not change
372 IF l_val_exists
373 THEN
374 l_old_account_type := get_value_attribute(rec_existing_value.compiled_value_attributes,curr_pos);
375
376 IF l_old_account_type <> p_account_type
377 THEN
378 log_message('account_type differs: Old Value='||l_old_account_type
379 ||', New Value='||p_account_type);
380 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_CHANGED');
381 FND_MESSAGE.set_token('TOKEN_VALUE', 'Account Type');
382 RETURN;
383 END IF;
384 END IF; /* end of check for existing_account_type */
385
386 -- check for third party control account
387 ELSIF g_list_flex_vl_attr(curr_pos).value_attribute_type = 'GL_CONTROL_ACCOUNT'
388 THEN
389 IF Nvl(p_third_party_ctrl_acct,'X') NOT IN ('Y','N','R','CUSTOMER','SUPPLIER')
390 THEN
391 IF p_third_party_ctrl_acct IS NOT NULL
392 THEN
393 log_message('Invalid third_party_ctrl_acct ='||p_third_party_ctrl_acct);
394 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_INVALID');
395 ELSE
396 log_message('third_party_ctrl_acct is not set');
397 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_NOT_SET');
398 END IF;
399 FND_MESSAGE.set_token('TOKEN_VALUE', 'Third Party Control Account');
400 RETURN;
401 END IF; /* end of check for p_third_party_ctrl_acct */
402
403 -- check for recon flag
404 ELSIF g_list_flex_vl_attr(curr_pos).value_attribute_type = 'RECONCILIATION FLAG'
405 THEN
406 IF p_reconcile NOT IN ('Y','N')
407 THEN
408 IF p_reconcile IS NOT NULL THEN
409 log_message('Invalid reconcile ='||p_reconcile);
410 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_INVALID');
411 ELSE
412 log_message('reconcile is not set');
413 FND_MESSAGE.set_name('SQLGL', 'GL_DRM_VALUE_NOT_SET');
414 END IF;
415 FND_MESSAGE.set_token('TOKEN_VALUE', 'Reconcile');
416 RETURN;
417 END IF; /* end of check for p_reconcile */
418 END IF; /* end of check for value_attribute_type */
419 END LOOP;
420
421 x_success := true;
422 EXCEPTION
423 WHEN OTHERS THEN
424 log_message('>> GL_DRM_INTEGRATION_PKG.validate_segvalue_attributes --> Unexpected EXCEPTION');
425 RAISE;
426
427 END validate_segvalue_attributes;
428
429 /* +=======================================================================+
430 * PRIVATE PROCEDURE
431 * validate_hierarchies
432 * PURPOSE
433 * Validations for hierarchy data exported by DRM
434 * CHANGE HISTORY
435 * Who When What
436 * vnetan 08/26/2010 Created
437 * +=======================================================================+*/
438 PROCEDURE validate_hierarchies (
439 p_child_value IN VARCHAR2,
440 p_parent_value IN VARCHAR2,
441 p_parent_summary IN VARCHAR2,
442 p_range_attribute IN VARCHAR2,
443 x_success OUT NOCOPY BOOLEAN
444 ) AS
445 BEGIN
446 x_success := TRUE;
447
448 IF p_parent_summary <> 'Y'
449 THEN
450 log_message('Parent Summary_flag = '||p_parent_summary);
451 FND_MESSAGE.set_name('SQLGL','GL_DRM_PARENT_NO_SUMM');
452 FND_MESSAGE.set_token('TOKEN_VALUE',p_parent_value);
453 x_success := FALSE;
454 END IF;
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 log_message('>> GL_DRM_INTEGRATION_PKG.validate_hierarchies --> Unexpected EXCEPTION');
459 RAISE;
460
461 END validate_hierarchies;
462
463 /* +=======================================================================+
464 * PRIVATE PROCEDURE
465 * populate_plsql_tables
466 * PURPOSE
467 * Validate the data exported by DRM into the interface tables and populate
468 * PL/SQL tables which are to be passed to GL_COA_SEGMENT_VAL_PVT api
469 * CHANGE HISTORY
470 * Who When What
471 * vnetan 08/26/2010 Created
472 * +=======================================================================+*/
473 PROCEDURE populate_plsql_tables (
474 x_retcode OUT NOCOPY VARCHAR2)
475 AS
476 -- cursor to fetch segment values from interface table
477 CURSOR c_drm_segvalues_interface(p_lang_code VARCHAR2)
478 IS
479 SELECT
480 sv.Value FLEX_VALUE,
481 Decode(p_lang_code,
482 'AR' ,DESC_AR, 'CS' ,DESC_CS, 'D' ,DESC_D, 'DK' ,DESC_DK,
483 'E' ,DESC_E, 'EL' ,DESC_EL, 'ESA',DESC_ESA, 'F' ,DESC_F,
484 'FRC',DESC_FRC, 'HR' ,DESC_HR, 'HU' ,DESC_HU, 'I' ,DESC_I,
485 'IS' ,DESC_IS, 'IW' ,DESC_IW, 'JA' ,DESC_JA, 'KO' ,DESC_KO,
486 'LT' ,DESC_LT, 'N' ,DESC_N, 'NL' ,DESC_NL, 'PL' ,DESC_PL,
487 'PT' ,DESC_PT, 'PTB',DESC_PTB, 'RO' ,DESC_RO, 'RU' ,DESC_RU,
488 'S' ,DESC_S, 'SF' ,DESC_SF, 'SK' ,DESC_SK, 'SL' ,DESC_SL,
489 'TH' ,DESC_TH, 'TR' ,DESC_TR, 'US' ,DESC_US, 'ZHS',DESC_ZHS,
490 'ZHT',DESC_ZHT
491 ) FLEX_DESC,
492 Decode(Upper(sv.enabled_flag),'TRUE','Y','FALSE','N',sv.enabled_flag) ENABLED_FLAG,
493 Decode(Upper(sv.summary_flag),'YES','Y','NO','N',sv.summary_flag) SUMMARY_FLAG,
494 Decode(Upper(sv.allow_budgeting),'TRUE','Y','FALSE','N',sv.allow_budgeting) ALLOW_BUDGETING,
495 Decode(Upper(sv.allow_posting),'TRUE','Y','FALSE','N',sv.allow_posting) ALLOW_POSTING,
496 Decode(Upper(sv.account_type),
497 'ASSET', 'A', 'LIABILITY', 'L', 'REVENUE', 'R', 'EXPENSE', 'E',
498 'BUDGETARY (CR)' ,'C', 'BUDGETARY (DR)', 'D', 'OWNER''S EQUITY', 'O',
499 sv.account_type) ACCOUNT_TYPE,
500 Decode(Upper(sv.reconcile),'YES','Y','NO','N',sv.reconcile) RECONCILE,
501 Decode(Upper(sv.third_party_ctrl_acct),'CUSTOMER', 'CUSTOMER',
502 'SUPPLIER', 'SUPPLIER',
503 'RESTRICT MANUAL JOURNALS','R',
504 'YES','Y','NO','N',
505 sv.third_party_ctrl_acct) THIRD_PARTY_CTRL_ACCT,
506 sv.start_date_active EFFECTIVE_FROM,
507 sv.end_date_active EFFECTIVE_TO,
508 sv.rollup_group ROLLUP_GROUP,
509 sv.hierarchy_level HIERARCHY_LEVEL
510 FROM gl_drm_segvalues_interface sv;
511
512 -- cursor to fetch parent-child relationships from interface table
513 CURSOR c_drm_hierarchy_interface
514 IS
515 SELECT DISTINCT
516 h.Value flex_value,
517 h.parent_value parent_flex_value,
518 Decode(Upper(sv.summary_flag), 'YES','P','NO','C',sv.summary_flag) range_attribute,
519 Decode(Upper(svp.summary_flag), 'YES','Y','NO','N',svp.summary_flag) parent_summary
520 FROM
521 gl_drm_hierarchy_interface h,
522 gl_drm_segvalues_interface sv,
523 gl_drm_segvalues_interface svp
524 WHERE
525 h.depth > 1 AND /* 11843300 : Top Node depth is 1 and should be ignored */
526 h.value = sv.Value(+) AND
527 h.parent_value = svp.Value(+)
528 ORDER BY parent_flex_value;
529
530 -- cursor to fetch attribute properties for a value set
531 CURSOR c_flex_val_attributes(p_value_set_id NUMBER)
532 IS
533 SELECT fvq.*
534 FROM fnd_flex_validation_qualifiers fvq
535 WHERE flex_value_set_id = p_value_set_id
536 ORDER BY assignment_date, value_attribute_type;
537
538
539 -- variables for value set attributes
540 l_vset_format FND_FLEX_VALUE_SETS.format_type%TYPE;
541 l_max_length FND_FLEX_VALUE_SETS.maximum_size%TYPE;
542 l_precision FND_FLEX_VALUE_SETS.number_precision%TYPE;
543 l_alpha_allowed FND_FLEX_VALUE_SETS.alphanumeric_allowed_flag%TYPE;
544 l_uppercase_only FND_FLEX_VALUE_SETS.uppercase_only_flag%TYPE;
545 l_zero_fill FND_FLEX_VALUE_SETS.numeric_mode_enabled_flag%TYPE;
546 l_min_value FND_FLEX_VALUE_SETS.minimum_value%TYPE;
547 l_max_value FND_FLEX_VALUE_SETS.maximum_value%TYPE;
548 l_storage_value FND_FLEX_VALUES.flex_value%TYPE;
549 l_display_value FND_FLEX_VALUES.flex_value%TYPE;
550
551 -- counters
552 l_cntr NUMBER;
553 l_err_count NUMBER;
554
555 -- variables for validations
556 l_valid_segval BOOLEAN;
557 l_val_valid BOOLEAN;
558 l_val_attr_valid BOOLEAN;
559 l_valid_hier BOOLEAN;
560 l_rec_valid BOOLEAN;
561 l_rec_err FND_NEW_MESSAGES.message_text%TYPE;
562
563 -- variables for output
564 l_open_tag VARCHAR2(20);
565
566 BEGIN
567 l_open_tag := NULL;
568
569 log_message('Fetching value_set properties...');
570 --fetch attributes of the value set
571 BEGIN
572 SELECT
573 flex_value_set_id,
574 format_type,
575 maximum_size,
576 number_precision,
577 alphanumeric_allowed_flag,
578 uppercase_only_flag,
579 numeric_mode_enabled_flag,
580 minimum_value,
581 maximum_value
582 INTO
583 g_value_set_id,
584 l_vset_format,
585 l_max_length,
586 l_precision,
587 l_alpha_allowed,
588 l_uppercase_only,
589 l_zero_fill,
590 l_min_value,
591 l_max_value
592 FROM fnd_flex_value_sets
593 WHERE flex_value_set_name = g_value_set_name;
594
595 EXCEPTION
596 WHEN NO_DATA_FOUND THEN
597 log_message('ERROR: Value set query failed for value_set_name='||g_value_set_name);
598 RAISE;
599 END;
600
601 -- fetch list of attributes for value
602 l_cntr := 1;
603 g_list_flex_vl_attr.DELETE;
604 FOR rec_flex_val_attributes IN c_flex_val_attributes(g_value_set_id)
605 LOOP
606 g_list_flex_vl_attr(l_cntr) := rec_flex_val_attributes;
607 l_cntr := l_cntr+1;
608 END LOOP;
609
610 IF g_list_flex_vl_attr.Count = 0 THEN
611 log_message('No attributes for value.');
612 raise_application_error(-20102,'No attributes defined for values in set='||g_value_set_name);
613 END IF;
614
615 log_message('Valueset properties:-');
616 log_message('ID = '||g_value_set_id);
617 log_message('l_vset_format = '||l_vset_format);
618 log_message('l_max_length = '||l_max_length);
619 log_message('l_precision = '||l_precision);
620 log_message('l_alpha_allowed = '||l_alpha_allowed);
621 log_message('l_uppercase_only = '||l_uppercase_only);
622 log_message('l_zero_fill = '||l_zero_fill);
623 log_message('l_min_value = '||l_min_value);
624 log_message('l_max_value = '||l_max_value);
625 log_message('num_of_attributes = '||g_list_flex_vl_attr.Count);
626
627 log_message('Validating segment values exported from DRM...');
628
629 l_valid_segval := TRUE;
630 l_err_count := 0;
631 l_cntr := 1;
632 g_flex_values_tbl.DELETE;
633
634 write_xml_tag(XT_valuesValidation,XML_TAG_OPEN);
635 l_open_tag := XT_valuesValidation;
636
637 FOR rec_segval IN c_drm_segvalues_interface(UserEnv('LANG'))
638 LOOP
639 -- validate value
640 log_message('==>validating value='||REC_SEGVAL.flex_value);
641
642 l_rec_err := NULL;
643 l_storage_value := NULL;
644 l_display_value := NULL;
645 l_val_valid := FALSE;
646 l_val_attr_valid := FALSE;
647
648 -- FND_API to validate FLEX_VALUE
649 FND_FLEX_VAL_UTIL.validate_value (
650 p_value => REC_SEGVAL.flex_value,
651 p_is_displayed => TRUE,
652 p_vset_name => g_value_set_name,
653 p_vset_format => l_vset_format,
654 p_max_length => l_max_length,
655 p_precision => l_precision,
656 p_alpha_allowed => l_alpha_allowed,
657 p_uppercase_only => l_uppercase_only,
658 p_zero_fill => l_zero_fill,
659 p_min_value => l_min_value,
660 p_max_value => l_max_value,
661 x_storage_value => l_storage_value,
662 x_display_value => l_display_value,
663 x_success => l_val_valid);
664
665 -- If value is valid then validate attributes of that value
666 IF l_val_valid
667 THEN
668 validate_segvalue_attributes (
669 p_seg_value => REC_SEGVAL.flex_value,
670 p_start_date => REC_SEGVAL.effective_from,
671 p_end_date => REC_SEGVAL.effective_to,
672 p_enabled_flag => REC_SEGVAL.enabled_flag,
673 p_summary_flag => REC_SEGVAL.summary_flag,
674 p_allow_budgeting => REC_SEGVAL.allow_budgeting,
675 p_allow_posting => REC_SEGVAL.allow_posting,
676 p_account_type => REC_SEGVAL.account_type,
677 p_third_party_ctrl_acct => REC_SEGVAL.third_party_ctrl_acct,
678 p_reconcile => REC_SEGVAL.reconcile,
679 p_rollup_group => REC_SEGVAL.rollup_group,
680 p_hierarchy_level => REC_SEGVAL.hierarchy_level,
681 x_success => l_val_attr_valid);
682 END IF;
683
684 -- check if all validations are OK
685 IF l_val_valid AND l_val_attr_valid
686 THEN
687 log_message('----->Value is OK.');
688 ELSE
689 l_rec_err := FND_MESSAGE.get;
690 log_message('----->'||l_rec_err);
691 l_err_count := l_err_count + 1;
692 l_valid_segval := FALSE;
693
694 -- Write segment value error details in outputfile
695 write_xml_tag(XT_valInError, XML_TAG_OPEN);
696 write_xml_element(XT_value,REC_SEGVAL.flex_value);
697 write_xml_element(XT_description,REC_SEGVAL.flex_desc);
698 write_xml_element(XT_errorMsg,l_rec_err);
699 write_xml_tag(XT_valInError, XML_TAG_CLOSE);
700 END IF;
701
702 -- populate PL/SQL table
703 -- l_storage_value is populated by FND_FLEX_VAL_UTIL.validate_value API
704 g_flex_values_tbl(l_cntr).value_set_name := g_value_set_name;
705 g_flex_values_tbl(l_cntr).flex_value := Nvl(l_storage_value,REC_SEGVAL.flex_value);
706 g_flex_values_tbl(l_cntr).flex_desc := REC_SEGVAL.flex_desc;
707 g_flex_values_tbl(l_cntr).parent_flex_value := NULL;
708 g_flex_values_tbl(l_cntr).summary_flag := REC_SEGVAL.summary_flag;
709 g_flex_values_tbl(l_cntr).roll_up_group := REC_SEGVAL.rollup_group;
710 g_flex_values_tbl(l_cntr).hierarchy_level := REC_SEGVAL.hierarchy_level;
711 g_flex_values_tbl(l_cntr).allow_budgeting := REC_SEGVAL.allow_budgeting;
712 g_flex_values_tbl(l_cntr).allow_posting := REC_SEGVAL.allow_posting;
713 g_flex_values_tbl(l_cntr).account_type := REC_SEGVAL.account_type;
714 g_flex_values_tbl(l_cntr).reconcile := REC_SEGVAL.reconcile;
715 g_flex_values_tbl(l_cntr).third_party_control_account := REC_SEGVAL.third_party_ctrl_acct;
716 g_flex_values_tbl(l_cntr).enabled_flag := REC_SEGVAL.enabled_flag;
717 g_flex_values_tbl(l_cntr).effective_from := REC_SEGVAL.effective_from;
718 g_flex_values_tbl(l_cntr).effective_to := REC_SEGVAL.effective_to;
719 g_flex_values_tbl(l_cntr).interface_id := NULL;
720 l_cntr := l_cntr + 1;
721 END LOOP;
722 write_xml_tag(XT_valuesValidation,XML_TAG_CLOSE);
723 l_open_tag := NULL;
724
725
726 -- check if any values were exported
727 IF NOT g_flex_values_tbl.EXISTS(1)
728 THEN
729 log_message('No data in interface table: GL_DRM_SEGVALUES_INTERFACE');
730 raise_application_error(-20103,'No Segment Values were exported from Hyperion DRM');
731 END IF;
732
733 log_message('Number of segment values : '||g_flex_values_tbl.COUNT);
734 log_message('Number of segment values in error : '||l_err_count);
735
736 -- validate hierarchy relationships
737 log_message('Validating exported hierarchy relationships...');
738
739 l_valid_hier := TRUE;
740 l_cntr := 1;
741 l_err_count := 0;
742
743 write_xml_tag(XT_relationValidation,XML_TAG_OPEN);
744 l_open_tag := XT_relationValidation;
745
746 FOR rec_hier IN c_drm_hierarchy_interface
747 LOOP
748 l_rec_valid := FALSE;
749 l_rec_err := NULL;
750
751 log_message('==>Validating child='||REC_HIER.flex_value||
752 ', parent='||REC_HIER.parent_flex_value||
753 ', range='||REC_HIER.range_attribute);
754
755 validate_hierarchies (
756 p_child_value => REC_HIER.flex_value,
757 p_parent_value => REC_HIER.parent_flex_value,
758 p_parent_summary => REC_HIER.parent_summary,
759 p_range_attribute => REC_HIER.range_attribute,
760 x_success => l_rec_valid);
761
762 IF l_rec_valid THEN
763 log_message('----->Relationship is OK.');
764 ELSE
765 l_rec_err := FND_MESSAGE.get;
766 log_message('----->'||l_rec_err);
767 l_err_count := l_err_count + 1;
768 l_valid_hier := FALSE;
769
770 -- Write hierarchy relationship error details in outputfile
771 write_xml_tag(XT_relInError, XML_TAG_OPEN);
772 write_xml_element(XT_parent,REC_HIER.parent_flex_value);
773 write_xml_element(XT_child,REC_HIER.flex_value);
774 write_xml_element(XT_errorMsg,l_rec_err);
775 write_xml_tag(XT_relInError, XML_TAG_CLOSE);
776 END IF;
777
778 -- populate PL/SQL table
779 g_norm_hierarchy_tbl(l_cntr).value_set_name := g_value_set_name;
780 g_norm_hierarchy_tbl(l_cntr).parent_flex_value := REC_HIER.parent_flex_value;
781 g_norm_hierarchy_tbl(l_cntr).range_attribute := REC_HIER.range_attribute;
782 g_norm_hierarchy_tbl(l_cntr).child_flex_value_low := REC_HIER.flex_value;
783 g_norm_hierarchy_tbl(l_cntr).child_flex_value_high := REC_HIER.flex_value;
784 g_norm_hierarchy_tbl(l_cntr).interface_id := NULL;
785 l_cntr := l_cntr + 1;
786
787 END LOOP;
788 write_xml_tag(XT_relationValidation,XML_TAG_CLOSE);
789 l_open_tag := NULL;
790
791 log_message('Number of hierarchy relationships : '||g_norm_hierarchy_tbl.COUNT);
792 log_message('Number of relationships in error : '||l_err_count);
793
794 -- Summarize validations
795 x_retcode := 'S';
796 IF l_valid_segval
797 THEN
798 log_message('Segment values are all valid.');
799 ELSE
800 log_message('Errors in the exported segment values.');
801 x_retcode := 'E';
802 END IF;
803 IF l_valid_hier THEN
804 log_message ('Hierarchy relationships are all valid');
805 ELSE
806 log_message('Errors in the exported hierarchy relationships.');
807 x_retcode := 'E';
808 END IF;
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 log_message('>> GL_DRM_INTEGRATION_PKG.populate_plsql_tables --> Unexpected EXCEPTION');
813 -- close any open tags in output
814 IF (l_open_tag IS NOT NULL) THEN
815 write_xml_tag(XT_relationValidation,XML_TAG_CLOSE);
816 END IF;
817
818 RAISE;
819 END populate_plsql_tables;
820
821 /* +=======================================================================+
822 * PRIVATE PROCEDURE
823 * gl_translate_segvalues
824 * PURPOSE
825 * Insert translations for Flex Value descriptions for all installed
826 * languages
827 * CHANGE HISTORY
828 * Who When What
829 * vnetan 07/31/2010 Created
830 * +=======================================================================+*/
831 PROCEDURE gl_translate_segvalues
832 AS
833 CURSOR c_installed_languages IS
834 SELECT l.language_code
835 FROM fnd_languages l
836 WHERE
837 l.installed_flag IN ('B','I') AND
838 l.language_code <> UserEnv('LANG');
839
840 l_s_flex_desc_tl FND_FLEX_VALUES_TL.description%TYPE;
841 l_n_flex_value_id FND_FLEX_VALUES.flex_value_id%TYPE;
842 l_n_translate_count NUMBER;
843 l_n_notrans_count NUMBER;
844
845 BEGIN
846 log_message('>>GL_DRM_INTEGRATION_PKG.gl_translate_segvalues');
847 FOR rec_c_installed_languages IN c_installed_languages
848 LOOP
849 log_message('Translating for installed language: '||rec_c_installed_languages.language_code);
850 l_n_translate_count := 0;
851 l_n_notrans_count := 0;
852
853 FOR i IN 1..g_flex_values_tbl.LAST
854 LOOP
855 -- Only translate for values which were imported
856 IF g_flex_values_tbl(i).status = 'P'
857 THEN
858 l_s_flex_desc_tl := NULL;
859 l_n_flex_value_id := NULL;
860
861 -- Fetch translated description from interface tables
862 SELECT Decode(rec_c_installed_languages.language_code,
863 'AR' ,DESC_AR, 'CS' ,DESC_CS, 'D' ,DESC_D, 'DK' ,DESC_DK,
864 'E' ,DESC_E, 'EL' ,DESC_EL, 'ESA',DESC_ESA, 'F' ,DESC_F,
865 'FRC',DESC_FRC, 'HR' ,DESC_HR, 'HU' ,DESC_HU, 'I' ,DESC_I,
866 'IS' ,DESC_IS, 'IW' ,DESC_IW, 'JA' ,DESC_JA, 'KO' ,DESC_KO,
867 'LT' ,DESC_LT, 'N' ,DESC_N, 'NL' ,DESC_NL, 'PL' ,DESC_PL,
868 'PT' ,DESC_PT, 'PTB',DESC_PTB, 'RO' ,DESC_RO, 'RU' ,DESC_RU,
869 'S' ,DESC_S, 'SF' ,DESC_SF, 'SK' ,DESC_SK, 'SL' ,DESC_SL,
870 'TH' ,DESC_TH, 'TR' ,DESC_TR, 'US' ,DESC_US, 'ZHS',DESC_ZHS,
871 'ZHT',DESC_ZHT)
872 INTO l_s_flex_desc_tl
873 FROM gl_drm_segvalues_interface sv
874 WHERE sv.value = g_flex_values_tbl(i).flex_value;
875
876 -- Check that translation was given.
877 IF l_s_flex_desc_tl IS NOT NULL
878 THEN
879 -- get flex_value_id
880 SELECT vl.flex_value_id
881 INTO l_n_flex_value_id
882 FROM
883 fnd_flex_values vl
884 WHERE
885 vl.flex_value_set_id = g_value_set_id AND
886 vl.flex_value = g_flex_values_tbl(i).flex_value;
887
888 -- update description in MLS table
889 UPDATE fnd_flex_values_tl
890 SET
891 description = l_s_flex_desc_tl,
892 source_lang = rec_c_installed_languages.language_code,
893 last_update_date = Trunc(SYSDATE)
894 WHERE
895 flex_value_id = l_n_flex_value_id AND
896 language = rec_c_installed_languages.language_code;
897
898 l_n_translate_count := l_n_translate_count + 1;
899 ELSE
900 l_n_notrans_count := l_n_notrans_count + 1;
901 END IF;
902 END IF;
903 END LOOP;
904 log_message(To_Char(l_n_notrans_count)||' value(s) do not have a description in language '||rec_c_installed_languages.language_code);
905 log_message(To_Char(l_n_translate_count)||' value description(s) were translated.');
906 END LOOP;
907 log_message('<<GL_DRM_INTEGRATION_PKG.gl_translate_segvalues');
908 EXCEPTION
909 WHEN OTHERS THEN
910 log_message('GL_DRM_INTEGRATION_PKG.gl_translate_segvalues >> Unexpected EXCEPTION');
911 RAISE;
912 END gl_translate_segvalues;
913
914 /* +=======================================================================+
915 * PUBLIC PROCEDURE
916 * gl_drm_import_process
917 * PURPOSE
918 * Controlling procedure for DRM import process
919 * CHANGE HISTORY
920 * Who When What
921 * vnetan 07/26/2010 Created
922 * +=======================================================================+*/
923 PROCEDURE gl_drm_import_process (
924 p_value_set_name IN VARCHAR2,
925 x_retcode OUT NOCOPY VARCHAR2)
926 AS
927 l_retcode VARCHAR2(10);
928 l_c_return_status VARCHAR2(1);
929 l_n_msg_count NUMBER(10);
930 l_s_msg_data VARCHAR2(2000);
931 l_c_msg_name fnd_new_messages.message_name%TYPE;
932 l_c_msg VARCHAR2(3000);
933 l_appl_name VARCHAR2(30);
934 l_gl_flex_values_status VARCHAR2(1);
935 l_gl_flex_values_nh_status VARCHAR2(1);
936
937 BEGIN
938 log_message('>>GL_DRM_INTEGRATION_PKG.gl_drm_import_process');
939
940 g_value_set_name := p_value_set_name;
941 x_retcode := 'S';
942
943 -- validate exported values/hierarchies and populate pl/sql tables
944 log_message('Validating segment values and hierarchies...');
945 populate_plsql_tables(l_retcode);
946
947 -- if data in interface table is valid call GL API
948 IF l_retcode = 'E'
949 THEN
950 log_message('The segment values and hierarchies data has errors.');
951 ROLLBACK;
952 x_retcode := 'E';
953
954 ELSIF l_retcode = 'S'
955 THEN
956 log_message('Calling GL_COA_SEGMENT_VAL_PVT.COA_SEGMENT_VAL_IMP...');
957 gl_coa_segment_val_pvt.coa_segment_val_imp(
958 p_api_version => 1.0,
959 p_init_msg_list => FND_API.G_TRUE,
960 p_commit => FND_API.G_TRUE,
961 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
962 x_return_status => l_c_return_status,
963 x_msg_count => l_n_msg_count,
964 x_msg_data => l_s_msg_data,
965 p_gl_flex_values_tbl => g_flex_values_tbl,
966 p_gl_flex_values_nh_tbl => g_norm_hierarchy_tbl,
967 p_gl_flex_values_status => l_gl_flex_values_status,
968 p_gl_flex_values_nh_status => l_gl_flex_values_nh_status);
969
970 -- check return status from GL_COA_SEGMENT_VAL_PVT API
971 IF l_c_return_status = 'S'
972 THEN
973 -- translate segment value descriptions
974 gl_translate_segvalues;
975 COMMIT WORK;
976 x_retcode := 'S';
977 ELSE
978 log_message('l_n_msg_count='||l_n_msg_count);
979 IF l_n_msg_count = 1
980 THEN
981 l_c_msg := FND_MSG_PUB.GET(p_msg_index => l_n_msg_count, p_encoded => 'T');
982 log_message(l_c_msg);
983
984 END IF;
985 list_gl_api_errors;
986 raise_application_error(-20101,'Error in the GL_COA_SEGMENT_VAL_PVT API');
987 END IF;
988 END IF;
989
990 -- close out output xml
991 IF x_retcode = 'S' THEN
992 write_xml_element(XT_numOfValImported, g_flex_values_tbl.COUNT);
993 write_xml_element(XT_numOfRelImported, g_norm_hierarchy_tbl.COUNT);
994 write_xml_element(XT_loadDone, XML_TEXT_YES);
995 ELSIF x_retcode = 'E' THEN
996 FND_MESSAGE.set_name('SQLGL','GL_DRM_SEGVAL_HIER_ERROR');
997 write_xml_tag(XT_loadError,XML_TAG_OPEN);
998 write_xml_element(XT_errorMsg, FND_MESSAGE.get);
999 write_xml_tag(XT_loadError,XML_TAG_CLOSE);
1000 write_xml_element(XT_loadDone, XML_TEXT_NO);
1001 END IF;
1002 write_xml_tag(XT_docRoot,XML_TAG_CLOSE);
1003
1004 -- empty interface tables
1005 DELETE gl_drm_segvalues_interface;
1006 DELETE gl_drm_hierarchy_interface;
1007 COMMIT;
1008
1009 log_message('>>gl_drm_integration_pkg.gl_drm_import_process');
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 log_message('Handling exception in GL_DRM_INTEGRATION_PKG.');
1014 log_message(SQLERRM);
1015 ROLLBACK;
1016 x_retcode := 'X';
1017
1018 -- write exception details to output
1019 FND_MESSAGE.set_name('SQLGL','GL_DRM_EXCEPTION');
1020 write_xml_tag(XT_loadError,XML_TAG_OPEN);
1021 write_xml_element(XT_errorMsg, FND_MESSAGE.get);
1022 write_xml_tag(XT_loadError,XML_TAG_CLOSE);
1023 write_xml_element(XT_exception,SQLERRM);
1024
1025 -- close out output xml
1026 write_xml_element(XT_loadDone,XML_TEXT_NO);
1027 write_xml_tag(XT_docRoot,XML_TAG_CLOSE);
1028
1029 -- empty interface tables
1030 DELETE gl_drm_segvalues_interface;
1031 DELETE gl_drm_hierarchy_interface;
1032 COMMIT;
1033 END gl_drm_import_process;
1034
1035 /* +=======================================================================+
1036 * PUBLIC PROCEDURE
1037 * get_message_text
1038 * PURPOSE
1039 * Fetches the message text in local language.
1040 * CHANGE HISTORY
1041 * Who When What
1042 * vnetan 01/31/2011 Created
1043 * +=======================================================================+*/
1044 PROCEDURE get_message_text (
1045 p_message_name IN VARCHAR2,
1046 p_message_token IN VARCHAR2 DEFAULT NULL,
1047 x_message_text OUT NOCOPY VARCHAR2)
1048 AS
1049 l_message_text FND_NEW_MESSAGES.message_text%TYPE;
1050 BEGIN
1051 FND_MESSAGE.set_name('SQLGL', p_message_name);
1052 IF p_message_token IS NOT NULL THEN
1053 FND_MESSAGE.set_token('TOKEN_VALUE', p_message_token);
1054 END IF;
1055 x_message_text := FND_MESSAGE.get;
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 log_message(sqlerrm);
1059 RAISE;
1060 END get_message_text;
1061
1062 /* +=======================================================================+
1063 * PUBLIC PROCEDURE
1064 * get_profile_name
1065 * PURPOSE
1066 * Fetches the profile option name in local language.
1067 * CHANGE HISTORY
1068 * Who When What
1069 * vnetan 01/31/2011 Created
1070 * +=======================================================================+*/
1071 PROCEDURE get_profile_name (
1072 p_prof_code IN VARCHAR2,
1073 x_profile_name OUT NOCOPY VARCHAR2)
1074 AS
1075 CURSOR c_fetch_name (p_prof_option VARCHAR2) IS
1076 SELECT user_profile_option_name
1077 FROM fnd_profile_options_tl
1078 WHERE LANGUAGE = UserEnv('LANG')
1079 AND profile_option_name = p_prof_option;
1080
1081 l_prof_name FND_PROFILE_OPTIONS_TL.user_profile_option_name%TYPE;
1082 BEGIN
1083 OPEN c_fetch_name (p_prof_code);
1084 FETCH c_fetch_name INTO l_prof_name;
1085 IF c_fetch_name%NOTFOUND THEN
1086 l_prof_name := p_prof_code;
1087 END IF;
1088 CLOSE c_fetch_name;
1089
1090 x_profile_name := l_prof_name;
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 log_message(sqlerrm);
1094 RAISE;
1095 END get_profile_name;
1096
1097 /* +=======================================================================+
1098 * PUBLIC FUNCTION
1099 * get_value_attribute
1100 * PURPOSE
1101 * Fetches the property value at a given postion in the field
1102 * FND_FLEX_VALUES.compiled_value_attributes
1103 * CHANGE HISTORY
1104 * Who When What
1105 * vnetan 09/10/2010 Created
1106 * +=======================================================================+*/
1107 FUNCTION get_value_attribute (
1108 p_compiled_attr FND_FLEX_VALUES.compiled_value_attributes%TYPE,
1109 p_attribute_pos NUMBER)
1110 RETURN VARCHAR2 AS
1111 l_current FND_FLEX_VALUES.compiled_value_attributes%TYPE;
1112 l_current_pos NUMBER(2);
1113 l_new FND_FLEX_VALUES.compiled_value_attributes%TYPE;
1114 BEGIN
1115 l_current_pos := 1;
1116 l_current := p_compiled_attr||FND_GLOBAL.newline;
1117
1118 WHILE l_current IS NOT NULL
1119 LOOP
1120 l_new := RTrim(SubStr(l_current,1,InStr(l_current,FND_GLOBAL.newline)),FND_GLOBAL.newline);
1121 l_current := LTrim(SubStr(l_current,InStr(l_current,FND_GLOBAL.newline)),FND_GLOBAL.newline);
1122
1123 IF l_current_pos = p_attribute_pos
1124 THEN
1125 RETURN l_new;
1126 ELSE
1127 l_current_pos := l_current_pos + 1;
1128 END IF;
1129 END LOOP;
1130 RETURN NULL;
1131 END get_value_attribute;
1132
1133 /* +=======================================================================+
1134 * PUBLIC PROCEDURE
1135 * gl_drm_export_process
1136 * PURPOSE
1137 * Concurrent executable for 'Initialize Segment Values and Hierarchies'
1138 * program.
1139 * CHANGE HISTORY
1140 * Who When What
1141 * vnetan 09/10/2010 Created
1142 * +=======================================================================+*/
1143 PROCEDURE gl_drm_export_process (
1144 errbuf OUT NOCOPY VARCHAR2,
1145 retcode OUT NOCOPY NUMBER,
1146 p_value_set_name IN VARCHAR2)
1147 AS
1148
1149 --===========================--
1150 -- Cursors
1151 --===========================--
1152 /* Cursor to fetch top-level parent nodes for a given value set */
1153 /* 12350022: Added changes to ensure hierarchies are only created for those
1154 top-level nodes which have at least one child */
1155 CURSOR c_hierarchy_section (p_value_set_id IN NUMBER, p_language IN VARCHAR2)
1156 IS
1157 SELECT
1158 FFV.flex_value TOP_NODE,
1159 FFV.description
1160 FROM FND_FLEX_VALUES_VL ffv
1161 WHERE FFV.flex_value_set_id = p_value_set_id
1162 AND FFV.summary_flag = 'Y'
1163 AND NOT EXISTS
1164 (SELECT 'X'
1165 FROM FND_FLEX_VALUE_NORM_HIERARCHY fnh
1166 WHERE FNH.flex_value_set_id = FFV.flex_value_set_id
1167 AND FNH.range_attribute = 'P'
1168 AND FFV.flex_value BETWEEN FNH.child_flex_value_low AND FNH.child_flex_value_high)
1169 -- 1235002: first check if ranges are defined in the norm hierarchy table for this parent
1170 AND EXISTS
1171 (SELECT 'X'
1172 FROM FND_FLEX_VALUE_NORM_HIERARCHY fnh1
1173 WHERE FNH1.flex_value_set_id = FFV.flex_value_set_id
1174 AND FNH1.parent_flex_value = FFV.flex_value
1175 -- confirm that the ranges have at least one child node defined
1176 AND EXISTS
1177 (SELECT 'X'
1178 FROM FND_FLEX_VALUES ffv1
1179 WHERE FNH1.flex_value_set_id = FFV1.flex_value_set_id
1180 AND (FFV1.summary_flag = 'N' AND FNH1.range_attribute = 'C' OR
1181 FFV1.summary_flag = 'Y' AND FNH1.range_attribute = 'P')
1182 AND FFV1.flex_value BETWEEN FNH1.child_flex_value_low AND FNH1.child_flex_value_high)
1183 )
1184 ORDER BY FFV.flex_value ASC;
1185
1186 /* Cursor to fetch all parent child relations for a given value set */
1187 CURSOR c_relation_section (p_value_set_id IN NUMBER)
1188 IS
1189 SELECT
1190 nh.parent_flex_value parent,
1191 cv.flex_value child
1192 FROM Fnd_Flex_Value_Norm_Hierarchy nh,
1193 Fnd_Flex_Values cv
1194 WHERE nh.flex_value_set_id = p_value_set_id
1195 AND nh.flex_value_set_Id = cv.flex_value_set_id
1196 AND cv.flex_value <> nh.parent_flex_value
1197 AND(( nh.range_attribute = 'P' AND cv.summary_flag = 'Y')
1198 OR( nh.range_attribute = 'C' AND cv.summary_flag = 'N'))
1199 AND cv.flex_value BETWEEN nh.child_flex_value_low AND nh.child_flex_value_high
1200 ORDER BY 1,2;
1201
1202 /* Cursor to fetch attributes of all segement values for a given value set */
1203 /* 12350022: Changed for translation support */
1204 CURSOR c_node_section (p_value_set_id IN NUMBER)
1205 IS
1206 SELECT
1207 ffv.flex_value_id,
1208 ffv.flex_value,
1209 ffv.enabled_flag,
1210 ffv.summary_flag,
1211 ffv.start_date_active,
1212 ffv.end_date_active,
1213 ffv.compiled_value_attributes,
1214 ffh.hierarchy_code rollup_group,
1215 ffv.hierarchy_level
1216 FROM fnd_flex_values_vl ffv,
1217 fnd_flex_hierarchies ffh
1218 WHERE ffv.flex_value_set_id = p_value_set_id
1219 AND ffv.flex_value_set_id = ffh.flex_value_set_id(+)
1220 AND ffv.structured_hierarchy_level = ffh.hierarchy_id(+);
1221
1222 /* cursor to fetch assigned segment qualifiers for a given value set */
1223 CURSOR c_flex_val_qual (
1224 cp_flex_value_set_id IN fnd_flex_hierarchies.flex_value_set_id%TYPE,
1225 cp_id_flex_code IN fnd_flex_validation_qualifiers.id_flex_code%TYPE,
1226 cp_id_flex_application_id IN fnd_flex_validation_qualifiers.id_flex_application_id%TYPE)
1227 IS
1228 SELECT
1229 segment_attribute_type,
1230 value_attribute_type
1231 FROM fnd_flex_validation_qualifiers
1232 WHERE id_flex_code = cp_id_flex_code
1233 AND id_flex_application_id = cp_id_flex_application_id
1234 AND flex_value_set_id = cp_flex_value_set_id
1235 ORDER BY assignment_date, value_attribute_type;
1236
1237 /* 12350022: Added cursor to fetch all installed languages */
1238 CURSOR c_installed_languages IS
1239 SELECT language_code
1240 FROM fnd_languages
1241 WHERE installed_flag IN ('B','I');
1242
1243 --===========================--
1244 -- Local Variables
1245 --===========================--
1246 l_request_id NUMBER(15,0);
1247 l_prog_appl_id NUMBER(15,0);
1248 l_prog_id NUMBER(15,0);
1249 l_prog_upd_dt DATE;
1250
1251 -- output control text
1252 c_col_delim CONSTANT VARCHAR2(1) := '|';
1253 c_version_tag CONSTANT VARCHAR2(20) := '[version]';
1254 c_hierarchy_tag CONSTANT VARCHAR2(20) := '[hierarchy]';
1255 c_relation_tag CONSTANT VARCHAR2(20) := '[relation]';
1256 c_node_tag CONSTANT VARCHAR2(20) := '[node]';
1257
1258 -- DRM Valid values
1259 c_drm_no CONSTANT VARCHAR2(10) := 'No';
1260 c_drm_yes CONSTANT VARCHAR2(10) := 'Yes';
1261 c_drm_true CONSTANT VARCHAR2(10) := 'True';
1262 c_drm_false CONSTANT VARCHAR2(10) := 'False';
1263 c_drm_node_type CONSTANT VARCHAR2(20) := 'OracleGL.NodeType';
1264
1265 -- version level properties
1266 l_value_set_details fnd_flex_value_sets%ROWTYPE;
1267 l_natural_account VARCHAR2(10);
1268
1269 -- node level properties
1270 l_flex_value_id FND_FLEX_VALUES.flex_value_id%TYPE;
1271 l_node FND_FLEX_VALUES.flex_value%TYPE;
1272 l_leaf VARCHAR2(10);
1273 l_enabled VARCHAR2(10);
1274 l_from VARCHAR2(240);
1275 l_to VARCHAR2(240);
1276 l_rollup FND_FLEX_HIERARCHIES.hierarchy_code%TYPE;
1277 l_level FND_FLEX_VALUES.hierarchy_level%TYPE;
1278 l_budgeting VARCHAR2(240);
1279 l_posting VARCHAR2(240);
1280 l_accttype VARCHAR2(240);
1281 l_thirdparty VARCHAR2(240);
1282 l_reconcile VARCHAR2(240);
1283
1284 -- for decoding compiled_value_attributes
1285 l_attribute VARCHAR2(240);
1286 l_position NUMBER;
1287
1288 -- 1235002: for translated descriptions
1289 l_DESC_AR FND_FLEX_VALUES_TL.description%TYPE;
1290 l_DESC_CS FND_FLEX_VALUES_TL.description%TYPE;
1291 l_DESC_D FND_FLEX_VALUES_TL.description%TYPE;
1292 l_DESC_DK FND_FLEX_VALUES_TL.description%TYPE;
1293 l_DESC_E FND_FLEX_VALUES_TL.description%TYPE;
1294 l_DESC_EL FND_FLEX_VALUES_TL.description%TYPE;
1295 l_DESC_ESA FND_FLEX_VALUES_TL.description%TYPE;
1296 l_DESC_F FND_FLEX_VALUES_TL.description%TYPE;
1297 l_DESC_FRC FND_FLEX_VALUES_TL.description%TYPE;
1298 l_DESC_HR FND_FLEX_VALUES_TL.description%TYPE;
1299 l_DESC_HU FND_FLEX_VALUES_TL.description%TYPE;
1300 l_DESC_I FND_FLEX_VALUES_TL.description%TYPE;
1301 l_DESC_IS FND_FLEX_VALUES_TL.description%TYPE;
1302 l_DESC_IW FND_FLEX_VALUES_TL.description%TYPE;
1303 l_DESC_JA FND_FLEX_VALUES_TL.description%TYPE;
1304 l_DESC_KO FND_FLEX_VALUES_TL.description%TYPE;
1305 l_DESC_LT FND_FLEX_VALUES_TL.description%TYPE;
1306 l_DESC_N FND_FLEX_VALUES_TL.description%TYPE;
1307 l_DESC_NL FND_FLEX_VALUES_TL.description%TYPE;
1308 l_DESC_PL FND_FLEX_VALUES_TL.description%TYPE;
1309 l_DESC_PT FND_FLEX_VALUES_TL.description%TYPE;
1310 l_DESC_PTB FND_FLEX_VALUES_TL.description%TYPE;
1311 l_DESC_RO FND_FLEX_VALUES_TL.description%TYPE;
1312 l_DESC_RU FND_FLEX_VALUES_TL.description%TYPE;
1313 l_DESC_S FND_FLEX_VALUES_TL.description%TYPE;
1314 l_DESC_SF FND_FLEX_VALUES_TL.description%TYPE;
1315 l_DESC_SK FND_FLEX_VALUES_TL.description%TYPE;
1316 l_DESC_SL FND_FLEX_VALUES_TL.description%TYPE;
1317 l_DESC_TH FND_FLEX_VALUES_TL.description%TYPE;
1318 l_DESC_TR FND_FLEX_VALUES_TL.description%TYPE;
1319 l_DESC_US FND_FLEX_VALUES_TL.description%TYPE;
1320 l_DESC_ZHS FND_FLEX_VALUES_TL.description%TYPE;
1321 l_DESC_ZHT FND_FLEX_VALUES_TL.description%TYPE;
1322
1323 -- 1235002: For fetching translations
1324 TYPE arrayLangType IS VARRAY(50) OF VARCHAR2(3);
1325 l_installed_languages ARRAYLANGTYPE;
1326 l_lang_count NUMBER;
1327 l_translated_desc FND_FLEX_VALUES_TL.description%TYPE;
1328
1329 --===========================--
1330 -- Sub-Routines
1331 --===========================--
1332 -- write text to output
1333 PROCEDURE write_text (
1334 p_text VARCHAR2,
1335 p_type VARCHAR2
1336 ) AS
1337 BEGIN
1338 IF p_type = 'P' THEN
1339 FND_FILE.PUT(FND_FILE.OUTPUT,p_text);
1340 ELSIF p_type = 'L' THEN
1341 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_text);
1342 ELSIF p_type = 'N' THEN
1343 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
1344 END IF;
1345 END write_text;
1346
1347 -- fetch description for a p_value_id in p_language
1348 FUNCTION get_description(p_language VARCHAR2, p_flex_value_id NUMBER)
1349 RETURN VARCHAR2 AS
1350 l_description FND_FLEX_VALUES_TL.description%TYPE;
1351 BEGIN
1352 SELECT flv.description
1353 INTO l_description
1354 FROM fnd_flex_values_tl flv
1355 WHERE flv.flex_value_id = p_flex_value_id
1356 AND flv.language = p_language
1357 AND flv.source_lang = p_language;
1358
1359 RETURN l_description;
1360 EXCEPTION
1361 WHEN NO_DATA_FOUND THEN
1362 l_description := NULL;
1363 RETURN l_description;
1364 WHEN OTHERS THEN
1365 log_message('Error fetching translated description');
1366 log_message('flex_value_id='||p_flex_value_id||'language='||p_language);
1367 RAISE;
1368 END get_description;
1369
1370 --===========================--
1371 -- Main Procedure Body
1372 --===========================--
1373 BEGIN
1374 retcode := 0; -- set success
1375
1376 /* Setting concurrent program values */
1377 l_request_id := fnd_global.conc_request_id;
1378 l_prog_appl_id := fnd_global.prog_appl_id;
1379 l_prog_id := fnd_global.conc_program_id;
1380 l_prog_upd_dt := SYSDATE;
1381 IF l_request_id = -1 THEN
1382 l_request_id := NULL;
1383 l_prog_appl_id := NULL;
1384 l_prog_id := NULL;
1385 l_prog_upd_dt := NULL;
1386 END IF;
1387
1388 log_message('Getting Value Set details');
1389 SELECT *
1390 INTO l_value_set_details
1391 FROM fnd_flex_value_sets
1392 WHERE flex_value_set_name = p_value_set_name;
1393
1394 -- check for natural account
1395 BEGIN
1396 SELECT c_drm_true
1397 INTO l_natural_account
1398 FROM fnd_flex_value_sets vs
1399 WHERE vs.flex_value_set_id = l_value_set_details.flex_value_set_id
1400 AND EXISTS
1401 (SELECT 'X'
1402 FROM fnd_flex_validation_qualifiers vq
1403 WHERE vq.id_flex_code = 'GL#'
1404 AND vq.id_flex_application_id = 101
1405 AND vq.flex_value_set_id = vs.flex_value_set_id
1406 AND vq.segment_attribute_type = 'GL_ACCOUNT');
1407 EXCEPTION
1408 WHEN NO_DATA_FOUND THEN
1409 l_natural_account := c_drm_false;
1410 END;
1411
1412 log_message('Writing version section');
1413 write_text(c_version_tag,'L');
1414 write_text(l_value_set_details.flex_value_set_name ||c_col_delim,'P'); -- version name
1415 write_text(l_value_set_details.description ||c_col_delim,'P'); -- descr
1416 write_text(l_value_set_details.flex_value_set_name ||c_col_delim,'P'); -- value set
1417 write_text(c_drm_yes ||c_col_delim,'P'); -- value set master
1418 write_text(c_drm_false ||c_col_delim,'P'); -- allow export
1419 write_text(l_natural_account ||c_col_delim,'P'); -- natural account
1420 write_text(TO_CHAR(l_value_set_details.maximum_size) ,'L'); -- node name max length
1421
1422 log_message('Writing hierarchy section');
1423 write_text(c_hierarchy_tag,'L');
1424 FOR rec_hier IN c_hierarchy_section(l_value_set_details.flex_value_set_id, 'US')
1425 LOOP
1426 write_text(rec_hier.description ||c_col_delim,'P'); -- hier name
1427 write_text(rec_hier.description ||c_col_delim,'P'); -- hier description
1428 write_text(rec_hier.top_node ||c_col_delim,'P'); -- top node
1429 write_text(c_drm_true ||c_col_delim,'P'); -- allow hierExport
1430 write_text(c_drm_node_type ,'L'); -- 12350022: HierarchyNodeType Property
1431 END LOOP;
1432
1433 log_message('Writing node section');
1434 write_text(c_node_tag,'L');
1435
1436 -- 1235002: Fetch and store list of installed languages
1437 l_lang_count := 1;
1438 l_installed_languages := arrayLangType();
1439 FOR rec_installed_languages IN c_installed_languages
1440 LOOP
1441 l_installed_languages.EXTEND;
1442 l_installed_languages(l_lang_count) := rec_installed_languages.language_code;
1443 l_lang_count := l_lang_count + 1;
1444 END LOOP;
1445 log_message('Translations will be queried for '||l_installed_languages.Count||' installed languages');
1446
1447 -- Loop through all segment values of the value set
1448 FOR rec_node IN c_node_section(l_value_set_details.flex_value_set_id)
1449 LOOP
1450 l_flex_value_id := rec_node.flex_value_id;
1451 l_node := rec_node.flex_value;
1452 l_from := To_Char(rec_node.start_date_active,'dd-Mon-yyyy hh24:mi:ss');
1453 l_to := To_Char(rec_node.end_date_active,'dd-Mon-yyyy hh24:mi:ss');
1454 l_rollup := rec_node.rollup_group;
1455 l_level := rec_node.hierarchy_level;
1456
1457 CASE rec_node.summary_flag
1458 WHEN 'Y' THEN l_leaf := c_drm_false;
1459 ELSE l_leaf := c_drm_true;
1460 END CASE;
1461
1462 CASE rec_node.enabled_flag
1463 WHEN 'Y' THEN l_enabled := c_drm_true;
1464 ELSE l_enabled := c_drm_false;
1465 END CASE;
1466
1467 l_position := 1;
1468 l_budgeting := NULL;
1469 l_posting := NULL;
1470 l_accttype := NULL;
1471 l_thirdparty := NULL;
1472 l_reconcile := NULL;
1473
1474 log_message('Fetching flex value qualifiers...');
1475 FOR rec_c_flex_val_qual IN c_flex_val_qual(l_value_set_details.flex_value_set_id,'GL#',101)
1476 LOOP
1477 l_attribute := get_value_attribute(rec_node.compiled_value_attributes,l_position);
1478
1479 IF rec_c_flex_val_qual.value_attribute_type = 'DETAIL_BUDGETING_ALLOWED' THEN
1480 CASE l_attribute
1481 WHEN 'Y' THEN l_budgeting := c_drm_true;
1482 ELSE l_budgeting := c_drm_false;
1483 END CASE;
1484 END IF;
1485
1486 IF rec_c_flex_val_qual.value_attribute_type = 'DETAIL_POSTING_ALLOWED' THEN
1487 CASE l_attribute
1488 WHEN 'Y' THEN l_posting := c_drm_true;
1489 ELSE l_posting := c_drm_false;
1490 END CASE;
1491 END IF;
1492
1493 IF rec_c_flex_val_qual.value_attribute_type = 'GL_ACCOUNT_TYPE' THEN
1494 CASE l_attribute
1495 WHEN 'A' THEN l_accttype := 'Asset';
1496 WHEN 'L' THEN l_accttype := 'Liability';
1497 WHEN 'R' THEN l_accttype := 'Revenue';
1498 WHEN 'E' THEN l_accttype := 'Expense';
1499 WHEN 'O' THEN l_accttype := 'Owner''s Equity';
1500 WHEN 'C' THEN l_accttype := 'Budgetary (CR)';
1501 WHEN 'D' THEN l_accttype := 'Budgetary (DR)';
1502 ELSE l_accttype := NULL;
1503 END CASE;
1504 END IF;
1505
1506 IF rec_c_flex_val_qual.value_attribute_type = 'RECONCILIATION FLAG' THEN
1507 CASE l_attribute
1508 WHEN 'Y' THEN l_reconcile := c_drm_yes;
1509 ELSE l_reconcile := c_drm_no;
1510 END CASE;
1511 END IF;
1512
1513 IF rec_c_flex_val_qual.value_attribute_type = 'GL_CONTROL_ACCOUNT' THEN
1514 CASE l_attribute
1515 WHEN 'Y' THEN l_thirdparty := c_drm_yes;
1516 WHEN 'N' THEN l_thirdparty := c_drm_no;
1517 WHEN 'R' THEN l_thirdparty := 'Restrict Manual Journals';
1518 WHEN 'CUSTOMER' THEN l_thirdparty := 'Customer';
1519 WHEN 'SUPPLIER' THEN l_thirdparty := 'Supplier';
1520 ELSE l_thirdparty := c_drm_no;
1521 END CASE;
1522 END IF;
1523
1524 l_position := l_position + 1;
1525 END LOOP;
1526
1527 -- 12350022: Fetch translated descriptions for the segment value
1528 -- Init variables to NULL
1529 l_DESC_AR := NULL;
1530 l_DESC_CS := NULL;
1531 l_DESC_D := NULL;
1532 l_DESC_DK := NULL;
1533 l_DESC_E := NULL;
1534 l_DESC_EL := NULL;
1535 l_DESC_ESA := NULL;
1536 l_DESC_F := NULL;
1537 l_DESC_FRC := NULL;
1538 l_DESC_HR := NULL;
1539 l_DESC_HU := NULL;
1540 l_DESC_I := NULL;
1541 l_DESC_IS := NULL;
1542 l_DESC_IW := NULL;
1543 l_DESC_JA := NULL;
1544 l_DESC_KO := NULL;
1545 l_DESC_LT := NULL;
1546 l_DESC_N := NULL;
1547 l_DESC_NL := NULL;
1548 l_DESC_PL := NULL;
1549 l_DESC_PT := NULL;
1550 l_DESC_PTB := NULL;
1551 l_DESC_RO := NULL;
1552 l_DESC_RU := NULL;
1553 l_DESC_S := NULL;
1554 l_DESC_SF := NULL;
1555 l_DESC_SK := NULL;
1556 l_DESC_SL := NULL;
1557 l_DESC_TH := NULL;
1558 l_DESC_TR := NULL;
1559 l_DESC_US := NULL;
1560 l_DESC_ZHS := NULL;
1561 l_DESC_ZHT := NULL;
1562
1563 -- Loop through installed languages to fetch descriptions
1564 FOR i IN 1..l_installed_languages.Count
1565 LOOP
1566 l_translated_desc := get_description(
1567 p_language => l_installed_languages(i) ,
1568 p_flex_value_id => l_flex_value_id);
1569
1570 CASE l_installed_languages(i)
1571 WHEN 'AR' THEN l_DESC_AR := l_translated_desc;
1572 WHEN 'CS' THEN l_DESC_CS := l_translated_desc;
1573 WHEN 'D' THEN l_DESC_D := l_translated_desc;
1574 WHEN 'DK' THEN l_DESC_DK := l_translated_desc;
1575 WHEN 'E' THEN l_DESC_E := l_translated_desc;
1576 WHEN 'EL' THEN l_DESC_EL := l_translated_desc;
1577 WHEN 'ESA' THEN l_DESC_ESA := l_translated_desc;
1578 WHEN 'F' THEN l_DESC_F := l_translated_desc;
1579 WHEN 'FRC' THEN l_DESC_FRC := l_translated_desc;
1580 WHEN 'HR' THEN l_DESC_HR := l_translated_desc;
1581 WHEN 'HU' THEN l_DESC_HU := l_translated_desc;
1582 WHEN 'I' THEN l_DESC_I := l_translated_desc;
1583 WHEN 'IS' THEN l_DESC_IS := l_translated_desc;
1584 WHEN 'IW' THEN l_DESC_IW := l_translated_desc;
1585 WHEN 'JA' THEN l_DESC_JA := l_translated_desc;
1586 WHEN 'KO' THEN l_DESC_KO := l_translated_desc;
1587 WHEN 'LT' THEN l_DESC_LT := l_translated_desc;
1588 WHEN 'N' THEN l_DESC_N := l_translated_desc;
1589 WHEN 'NL' THEN l_DESC_NL := l_translated_desc;
1590 WHEN 'PL' THEN l_DESC_PL := l_translated_desc;
1591 WHEN 'PT' THEN l_DESC_PT := l_translated_desc;
1592 WHEN 'PTB' THEN l_DESC_PTB := l_translated_desc;
1593 WHEN 'RO' THEN l_DESC_RO := l_translated_desc;
1594 WHEN 'RU' THEN l_DESC_RU := l_translated_desc;
1595 WHEN 'S' THEN l_DESC_S := l_translated_desc;
1596 WHEN 'SF' THEN l_DESC_SF := l_translated_desc;
1597 WHEN 'SK' THEN l_DESC_SK := l_translated_desc;
1598 WHEN 'SL' THEN l_DESC_SL := l_translated_desc;
1599 WHEN 'TH' THEN l_DESC_TH := l_translated_desc;
1600 WHEN 'TR' THEN l_DESC_TR := l_translated_desc;
1601 WHEN 'US' THEN l_DESC_US := l_translated_desc;
1602 WHEN 'ZHS' THEN l_DESC_ZHS := l_translated_desc;
1603 WHEN 'ZHT' THEN l_DESC_ZHT := l_translated_desc;
1604 END CASE;
1605 END LOOP;
1606
1607 -- write node details to text file
1608 write_text(l_node ||c_col_delim,'P'); -- node
1609 write_text(l_leaf ||c_col_delim,'P'); -- leaf
1610 write_text(l_enabled ||c_col_delim,'P'); -- enabled
1611 write_text(l_from ||c_col_delim,'P'); -- start
1612 write_text(l_to ||c_col_delim,'P'); -- end
1613 write_text(l_rollup ||c_col_delim,'P'); -- rollup
1614 write_text(l_level ||c_col_delim,'P'); -- level
1615 write_text(l_budgeting ||c_col_delim,'P'); -- allow budgeting
1616 write_text(l_posting ||c_col_delim,'P'); -- allow posting
1617 write_text(l_accttype ||c_col_delim,'P'); -- account type
1618 write_text(l_reconcile ||c_col_delim,'P'); -- reconcile
1619 write_text(l_thirdparty ||c_col_delim,'P'); -- third party
1620
1621 -- 12350022: Write description properties
1622 write_text(l_DESC_AR ||c_col_delim, 'P'); -- Arabic Description
1623 write_text(l_DESC_CS ||c_col_delim, 'P'); -- Czech Description
1624 write_text(l_DESC_D ||c_col_delim, 'P'); -- German Description
1625 write_text(l_DESC_DK ||c_col_delim, 'P'); -- Danish Description
1626 write_text(l_DESC_E ||c_col_delim, 'P'); -- Spanish Description
1627 write_text(l_DESC_EL ||c_col_delim, 'P'); -- Greek Description
1628 write_text(l_DESC_ESA ||c_col_delim, 'P'); -- LA Spanish Description
1629 write_text(l_DESC_F ||c_col_delim, 'P'); -- French Description
1630 write_text(l_DESC_FRC ||c_col_delim, 'P'); -- CAD French Description
1631 write_text(l_DESC_HR ||c_col_delim, 'P'); -- Croatian Description
1632 write_text(l_DESC_HU ||c_col_delim, 'P'); -- Hungarian Description
1633 write_text(l_DESC_I ||c_col_delim, 'P'); -- Italian Description
1634 write_text(l_DESC_IS ||c_col_delim, 'P'); -- Icelandic Description
1635 write_text(l_DESC_IW ||c_col_delim, 'P'); -- Hebrew Description
1636 write_text(l_DESC_JA ||c_col_delim, 'P'); -- Japanese Description
1637 write_text(l_DESC_KO ||c_col_delim, 'P'); -- Korean Description
1638 write_text(l_DESC_LT ||c_col_delim, 'P'); -- Dutch Description
1639 write_text(l_DESC_N ||c_col_delim, 'P'); -- Lithuanian Description
1640 write_text(l_DESC_NL ||c_col_delim, 'P'); -- Polish Description
1641 write_text(l_DESC_PL ||c_col_delim, 'P'); -- Portuguese Description
1642 write_text(l_DESC_PT ||c_col_delim, 'P'); -- BR Portuguese Description
1643 write_text(l_DESC_PTB ||c_col_delim, 'P'); -- Norwegian Description
1644 write_text(l_DESC_RO ||c_col_delim, 'P'); -- Romanian Description
1645 write_text(l_DESC_RU ||c_col_delim, 'P'); -- Russian Description
1646 write_text(l_DESC_S ||c_col_delim, 'P'); -- Swedish Description
1647 write_text(l_DESC_SF ||c_col_delim, 'P'); -- Finnish Description
1648 write_text(l_DESC_SK ||c_col_delim, 'P'); -- Slovak Description
1649 write_text(l_DESC_SL ||c_col_delim, 'P'); -- Slovenian Description
1650 write_text(l_DESC_TH ||c_col_delim, 'P'); -- Thai Description
1651 write_text(l_DESC_TR ||c_col_delim, 'P'); -- Turkish Description
1652 write_text(l_DESC_US ||c_col_delim, 'P'); -- English Description
1653 write_text(l_DESC_ZHS ||c_col_delim, 'P'); -- Simplified Chinese Description
1654 write_text(l_DESC_ZHT , 'L'); -- Trad. Chinese Description
1655
1656 END LOOP; -- End of cursor loop :- c_node_section
1657
1658 log_message('Writing relation section');
1659 write_text(c_relation_tag,'L');
1660 FOR rec_relation IN c_relation_section(l_value_set_details.flex_value_set_id)
1661 LOOP
1662 write_text(rec_relation.parent ||c_col_delim,'P'); -- parent
1663 write_text(rec_relation.child ,'L'); -- child
1664 END LOOP;
1665
1666 log_message('Finished');
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 ROLLBACK;
1670 retcode := 2;
1671 log_message(SQLERRM);
1672 errbuf := fnd_message.get_string('SQLGL','GL_DRM_EXPORT_ERROR');
1673 END gl_drm_export_process;
1674
1675 END gl_drm_integration_pkg;