[Home] [Help]
PACKAGE BODY: APPS.GCS_XML_GEN_PKG
Source
1 PACKAGE BODY GCS_XML_GEN_PKG AS
2 /* $Header: gcsxmlgenb.pls 120.14 2005/12/23 11:57:59 hakumar noship $ */
3 new_line VARCHAR2(1) := '
4 ';
5 g_api VARCHAR2(80) := 'gcs.plsql.GCS_XML_GEN_PKG';
6 /*
7 l_ledger_vs_combo_attr NUMBER :=
8 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO').attribute_id;
9 l_ledger_vs_combo_version NUMBER :=
10 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO').version_id;
11 l_entity_ledger_attr NUMBER :=
12 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID').attribute_id;
13 l_entity_ledger_version NUMBER :=
14 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID').version_id;
15 l_ldg_curr_attr NUMBER :=
16 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE').attribute_id;
17 l_ldg_curr_version NUMBER :=
18 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE').version_id;
19 l_entity_srcsys_attr NUMBER :=
20 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-SOURCE_SYSTEM_CODE').attribute_id;
21 l_entity_srcsys_version NUMBER :=
22 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-SOURCE_SYSTEM_CODE').version_id;
23 l_entity_type_attr NUMBER :=
24 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').attribute_id;
25 l_entity_type_version NUMBER :=
26 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').version_id;
27
28 PROCEDURE append_value_set_query ( p_entity_id IN NUMBER,
29 p_value_set_clause OUT NOCOPY VARCHAR2)
30
31
32 IS
33 TYPE r_dimension_vs_id IS RECORD
34 (dimension_id NUMBER(15),
35 value_set_id NUMBER);
36
37 TYPE t_dimension_vs_id IS TABLE OF r_dimension_vs_id;
38
39 l_dimension_vs_id t_dimension_vs_id;
40
41 BEGIN
42
43 SELECT gvcd.dimension_id,
44 gvcd.value_set_id
45 BULK COLLECT INTO
46 l_dimension_vs_id
47 FROM fem_global_vs_combo_defs gvcd,
48 fem_ledgers_attr fla,
49 fem_entities_attr fea,
50 fem_tab_column_prop ftcp,
51 fem_tab_columns_b ftcb
52 WHERE fla.ledger_id = fea.dim_attribute_numeric_member
53 AND fla.attribute_id = l_ledger_vs_combo_attr
54 AND fla.version_id = l_ledger_vs_combo_version
55 AND fea.entity_id = p_entity_id
56 AND fea.attribute_id = l_entity_ledger_attr
57 AND fea.version_id = l_entity_ledger_version
58 AND ftcb.table_name = 'FEM_BALANCES'
59 AND ftcb.dimension_id = gvcd.dimension_id
60 AND ftcb.column_name = ftcp.column_name
61 AND ftcb.column_name <> 'INTERCOMPANY_ID'
62 AND ftcp.column_property_code = 'PROCESSING_KEY'
63 AND ftcp.table_name = ftcb.table_name
64 AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member;
65
66 FOR l_counter IN l_dimension_vs_id.FIRST..l_dimension_vs_id.LAST LOOP
67
68 IF (l_dimension_vs_id(l_counter).dimension_id = 8) THEN
69 p_value_set_clause := p_value_set_clause ||
70 ' AND fcob.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ||
71 ' AND fcib.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
72 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 3) THEN
73 p_value_set_clause := p_value_set_clause ||
74 ' AND fpb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
75 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 2) THEN
76 p_value_set_clause := p_value_set_clause ||
77 ' AND fnab.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
78 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 13) THEN
79 p_value_set_clause := p_value_set_clause ||
80 ' AND fchb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
81 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 14) THEN
82 p_value_set_clause := p_value_set_clause ||
83 ' AND flib.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
84 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 15) THEN
85 p_value_set_clause := p_value_set_clause ||
86 ' AND fpjb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
87 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 16) THEN
88 p_value_set_clause := p_value_set_clause ||
89 ' AND fcb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
90 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 30) THEN
91 p_value_set_clause := p_value_set_clause ||
92 ' AND ftb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
93 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 19) THEN
94 p_value_set_clause := p_value_set_clause ||
95 ' AND fud1.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
96 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 20) THEN
97 p_value_set_clause := p_value_set_clause ||
98 ' AND fud2.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
99 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 21) THEN
100 p_value_set_clause := p_value_set_clause ||
101 ' AND fud3.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
102 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 22) THEN
103 p_value_set_clause := p_value_set_clause ||
104 ' AND fud4.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
105 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 23) THEN
106 p_value_set_clause := p_value_set_clause ||
107 ' AND fud5.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
108 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 24) THEN
109 p_value_set_clause := p_value_set_clause ||
110 ' AND fud6.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
111 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 25) THEN
112 p_value_set_clause := p_value_set_clause ||
113 ' AND fud7.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
114 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 26) THEN
115 p_value_set_clause := p_value_set_clause ||
116 ' AND fud8.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
117 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 27) THEN
118 p_value_set_clause := p_value_set_clause ||
119 ' AND fud9.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
120 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 28) THEN
121 p_value_set_clause := p_value_set_clause ||
122 ' AND fud10.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
123 END IF;
124
125 END LOOP;
126
127 END;
128
129 PROCEDURE generate_dimension_name_xml ( p_language_code IN VARCHAR2,
130 p_fem_dim_y_n IN VARCHAR2,
131 p_xml_file_type IN VARCHAR2,
132 p_dimension_name_xml OUT NOCOPY CLOB )
133
134 IS
135 l_index NUMBER;
136
137 TYPE DIM_XML_TAG_LIST IS TABLE OF VARCHAR2(200);
138 L_DIM_XML_TAG_LIST DIM_XML_TAG_LIST;
139 TYPE DIM_COL_NAMES_LIST IS TABLE OF VARCHAR2(200);
140 L_DIM_COL_NAMES_LIST DIM_COL_NAMES_LIST;
141 TYPE DIM_COL_NUM_LIST IS TABLE OF NUMBER;
142 L_DIM_COL_NUM_LIST DIM_COL_NUM_LIST;
143
144 CURSOR c_dimension_name (p_language_code VARCHAR2)
145 IS
146 SELECT ' <Name><![CDATA[' ||
147 ftcb.display_name || ']]></Name>' xml_tags,
148 DECODE(ftcb.column_name, 'COMPANY_COST_CENTER_ORG_ID', 1,
149 'INTERCOMPANY_ID', 4,
150 'LINE_ITEM_ID', 2,
151 3),
152 ftcb.column_name
153 FROM fem_tab_columns_tl ftcb,
154 fem_tab_column_prop ftcp
155 WHERE ftcb.table_name = 'FEM_BALANCES'
156 AND ftcb.table_name = ftcp.table_name
157 AND ftcb.language = p_language_code
158 AND ftcb.column_name = ftcp.column_name
159 AND ftcp.column_property_code = 'PROCESSING_KEY'
160 AND NOT EXISTS ( SELECT 'X'
161 FROM fem_app_grp_col_exclsns col_exclsns
162 WHERE col_exclsns.application_group_id = 266
163 AND col_exclsns.table_name = ftcb.table_name
164 AND col_exclsns.column_name = ftcb.column_name)
165 AND ftcb.column_name IN ( 'CHANNEL_ID',
166 'COMPANY_COST_CENTER_ORG_ID',
167 'CUSTOMER_ID',
168 'FINANCIAL_ELEM_ID',
169 'INTERCOMPANY_ID',
170 'LINE_ITEM_ID',
171 'NATURAL_ACCOUNT_ID',
172 'PRODUCT_ID',
173 'PROJECT_ID',
174 'TASK_ID',
175 'USER_DIM10_ID',
176 'USER_DIM1_ID',
177 'USER_DIM2_ID',
178 'USER_DIM3_ID',
179 'USER_DIM4_ID',
180 'USER_DIM5_ID',
181 'USER_DIM6_ID',
182 'USER_DIM7_ID',
183 'USER_DIM8_ID',
184 'USER_DIM9_ID')
185 ORDER BY 2,3;
186
187 CURSOR c_fem_dimension_name ( p_language_code VARCHAR2 )
188 IS
189 SELECT ' <Name><![CDATA[' || ftcb.display_name ||
190 ']]></Name>' xml_tags,
191 DECODE(ftcb.column_name, 'COMPANY_COST_CENTER_ORG_ID', 1,
192 'INTERCOMPANY_ID', 4,
193 'LINE_ITEM_ID', 2,
194 3),
195 ftcb.column_name
196 FROM fem_tab_columns_tl ftcb,
197 fem_tab_column_prop ftcp
198 WHERE ftcb.table_name = 'FEM_BALANCES'
199 AND ftcb.table_name = ftcp.table_name
200 AND ftcb.language = p_language_code
201 AND ftcb.column_name = ftcp.column_name
202 AND ftcp.column_property_code = 'PROCESSING_KEY'
203 AND ftcb.column_name IN ( 'CHANNEL_ID',
204 'COMPANY_COST_CENTER_ORG_ID',
205 'CUSTOMER_ID',
206 'FINANCIAL_ELEM_ID',
207 'INTERCOMPANY_ID',
208 'LINE_ITEM_ID',
209 'NATURAL_ACCOUNT_ID',
210 'PRODUCT_ID',
211 'PROJECT_ID',
212 'TASK_ID',
213 'USER_DIM10_ID',
214 'USER_DIM1_ID',
215 'USER_DIM2_ID',
216 'USER_DIM3_ID',
217 'USER_DIM4_ID',
218 'USER_DIM5_ID',
219 'USER_DIM6_ID',
220 'USER_DIM7_ID',
221 'USER_DIM8_ID',
222 'USER_DIM9_ID')
223 ORDER BY 2,3;
224
225 BEGIN
226
227 p_dimension_name_xml := ' <DimensionInfo>'|| new_line;
228
229 IF p_fem_dim_y_n = 'Y' THEN
230
231 OPEN c_fem_dimension_name(p_language_code);
232 FETCH c_fem_dimension_name BULK COLLECT INTO L_DIM_XML_TAG_LIST, L_DIM_COL_NUM_LIST, L_DIM_COL_NAMES_LIST;
233 FOR l_index IN L_DIM_XML_TAG_LIST.FIRST .. L_DIM_XML_TAG_LIST.LAST LOOP
234 p_dimension_name_xml := p_dimension_name_xml || L_DIM_XML_TAG_LIST(l_index) || new_line;
235 END LOOP;
236 CLOSE c_fem_dimension_name;
237
238 p_dimension_name_xml := p_dimension_name_xml || '</DimensionInfo>' || new_line;
239
240 ELSE
241
242 OPEN c_dimension_name(p_language_code);
243 FETCH c_dimension_name BULK COLLECT INTO L_DIM_XML_TAG_LIST, L_DIM_COL_NUM_LIST, L_DIM_COL_NAMES_LIST;
244 FOR l_index IN L_DIM_XML_TAG_LIST.FIRST .. L_DIM_XML_TAG_LIST.LAST LOOP
245 p_dimension_name_xml := p_dimension_name_xml || L_DIM_XML_TAG_LIST(l_index) || new_line;
246 END LOOP;
247 CLOSE c_dimension_name;
248
249 p_dimension_name_xml := p_dimension_name_xml || '</DimensionInfo>' || new_line;
250
251
252 END IF;
253
254 END generate_dimension_name_xml;
255
256
257 PROCEDURE generate_cmtb_header_xml ( p_cmtb_header_data IN r_cmtb_header_data,
258 p_cmtb_header_xml OUT NOCOPY CLOB )
259 IS
260
261 BEGIN
262
263 p_cmtb_header_xml := ' <TB_HEADER>' || new_line ||
264 ' <RUN_NAME><![CDATA[' || p_cmtb_header_data.run_name || ']]></RUN_NAME>' || new_line ||
265 ' <PERIOD><![CDATA[' || p_cmtb_header_data.cal_period_name || ']]></PERIOD>' || new_line ||
266 ' <BALANCE_TYPE><![CDATA[' || p_cmtb_header_data.balance_type || ']]></BALANCE_TYPE>' || new_line ||
267 ' <START_TIME><![CDATA[' || p_cmtb_header_data.start_time || ']]></START_TIME>' || new_line ||
268 ' <END_TIME><![CDATA[' || p_cmtb_header_data.end_time || ']]></END_TIME>' || new_line ||
269 ' <CURRENCY_NAME><![CDATA[' || p_cmtb_header_data.currency_name || ']]></CURRENCY_NAME>' || new_line ||
270 ' <HIERARCHY_NAME><![CDATA[' || p_cmtb_header_data.hierarchy_name || ']]></HIERARCHY_NAME>'|| new_line ||
271 ' <HIERARCHY_ID>' || p_cmtb_header_data.hierarchy_id || '</HIERARCHY_ID>' || new_line ||
272 ' <CAL_PERIOD_ID>' || p_cmtb_header_data.cal_period_id || '</CAL_PERIOD_ID>' || new_line ||
273 ' </TB_HEADER>' || new_line;
274
275 END generate_cmtb_header_xml;
276
277 PROCEDURE generate_cmtb_entity_name_xml ( p_language_code IN VARCHAR2,
278 p_hierarchy_id IN NUMBER,
279 p_entity_id IN NUMBER,
280 p_cal_period_id IN NUMBER,
281 p_balance_type_code IN VARCHAR2,
282 p_currency_code IN VARCHAR2,
283 p_cmtb_col_xml OUT NOCOPY CLOB,
284 p_cmtb_lines_xml OUT NOCOPY CLOB )
285 IS
286 TYPE entity_name_list IS TABLE OF VARCHAR2(450);
287 EntityList entity_name_list := entity_name_list();
288 TYPE cmtb_line_item_list IS TABLE OF VARCHAR2(150);
289 l_cmtb_line_item_list cmtb_line_item_list;
290 TYPE cmtb_entity_name_list IS TABLE OF VARCHAR2(450);
291 l_cmtb_entity_name_list cmtb_entity_name_list;
292 TYPE cmtb_balance_list IS TABLE OF NUMBER;
293 l_cmtb_balance_list cmtb_balance_list;
294 TYPE cmtb_entity_type_list IS TABLE OF NUMBER;
295 l_cmtb_entity_type_list cmtb_entity_type_list;
296 TYPE cmtb_line_item_id_list IS TABLE OF NUMBER;
297 l_cmtb_line_item_id_list cmtb_line_item_id_list;
298 TYPE cmtb_entity_id_list IS TABLE OF NUMBER;
299 l_cmtb_entity_id_list cmtb_entity_id_list;
300 TYPE cmtb_is_drillable_list IS TABLE OF VARCHAR2(1);
301 l_cmtb_is_drillable_list cmtb_is_drillable_list;
302
303 l_entity_type_attr_id NUMBER;
304 l_cal_period_attr_id NUMBER;
305 l_entity_type_version_id NUMBER;
306 l_cal_period_version_id NUMBER;
307 l_maxnum_entities NUMBER(15) := 0;
308 l_item_list_first NUMBER(15);
309 l_item_list_last NUMBER(15);
310 l_item_list_current NUMBER(15);
311 l_entity_index NUMBER(15);
312 l_cmtb_lines_xml CLOB;
313 l_cal_period_end_date DATE;
314
315 CURSOR c_entity_name ( p_language_code VARCHAR2,
316 p_hierarchy_id NUMBER,
317 p_entity_id NUMBER,
318 p_cal_period_end_date DATE)
319 IS
320 SELECT entity_name,
321 MIN(DECODE(fea.dim_attribute_varchar_member,
322 'O', 1,
323 'C', DECODE(fev.entity_id, gcs.parent_entity_id, 4, 2),
324 'E', 3)) entity_type
325 FROM gcs_cons_relationships gcs,
326 fem_entities_tl fev,
327 fem_entities_attr fea
328 WHERE hierarchy_id = p_hierarchy_id
329 AND child_entity_id = fev.entity_id
330 --Begin Fix#4198102
331 AND dominant_parent_flag = 'Y'
332 AND p_cal_period_end_date BETWEEN gcs.start_date AND NVL(gcs.end_date, p_cal_period_end_date)
333 --End Fix#4198102
334 AND fea.attribute_id = l_entity_type_attr_id
335 AND fea.version_id = l_entity_type_version_id
336 AND fev.entity_id = fea.entity_id
337 AND fev.language = p_language_code
338 AND gcs.parent_entity_id = p_entity_id
339 GROUP BY entity_name
340 UNION ALL
341 SELECT entity_name, 4
342 FROM fem_entities_tl fev
343 WHERE entity_id = p_entity_id
344 AND fev.language = p_language_code
345 ORDER BY entity_type, entity_name;
346
347 CURSOR c_tb_data ( p_language_code VARCHAR2,
348 p_hierarchy_id NUMBER,
349 p_entity_id NUMBER,
350 p_cal_period_id NUMBER,
351 p_currency_code VARCHAR2,
352 p_cal_period_end_date DATE)
353 IS
354 SELECT flit.line_item_name,
355 fet.entity_name,
356 SUM (fb.ytd_balance_e) balance,
357 MIN (DECODE (fea.dim_attribute_varchar_member,
358 'O', 1,
359 'C', DECODE (fet.entity_id, geca.entity_id, 4, 2),
360 'E', 3)) entity_type,
361 flit.line_item_id,
362 fet.entity_id,
363 decode(fea.dim_attribute_varchar_member,
364 'O', decode(fli_parents.line_item_id,
365 null, 'Y',
366 'N'),
367 'N') is_drillable
368 FROM fem_balances fb,
369 gcs_dataset_codes gdc,
370 fem_ln_items_tl flit,
371 (select flib.line_item_id
372 from fem_ln_items_b flib
373 where exists
374 (select 1
375 from fem_ln_items_hier flih,
376 gcs_system_options gso,
377 fem_object_definition_b odb,
378 fem_global_vs_combo_defs gvscd
379 where odb.object_id = gso.ln_item_hierarchy_obj_id
380 and p_cal_period_end_date BETWEEN odb.effective_start_date and odb.effective_end_date
381 and gvscd.global_vs_combo_id = gso.fch_global_vs_combo_id
382 and gvscd.dimension_id = 14
383 and flih.hierarchy_obj_def_id = odb.object_definition_id
384 and flih.parent_id = flib.line_item_id
385 and flih.child_id <> flih.parent_id
386 and flih.parent_value_set_id = gvscd.value_set_id
387 and flih.child_value_set_id = gvscd.value_set_id
388 )
389 ) fli_parents,
390 gcs_entity_cons_attrs geca,
391 fem_entities_tl fet,
392 fem_entities_attr fea
393 WHERE fb.source_system_code = 70
394 AND fb.dataset_code = gdc.dataset_code
395 AND gdc.balance_type_code = p_balance_type_code
396 AND fb.line_item_id = flit.line_item_id
397 AND flit.language = p_language_code
398 AND geca.hierarchy_id = p_hierarchy_id
399 AND geca.hierarchy_id = gdc.hierarchy_id
400 AND fb.currency_code = p_currency_code
401 AND fb.currency_code = geca.currency_code
402 AND geca.entity_id = p_entity_id
403 AND fb.cal_period_id = p_cal_period_id
404 AND fb.entity_id = fet.entity_id
405 AND fet.language = p_language_code
406 AND fb.entity_id = fea.entity_id
407 AND fea.attribute_id = l_entity_type_attr_id
408 AND fea.version_id = l_entity_type_version_id
409 AND ( fb.entity_id = geca.entity_id
410 OR
411 EXISTS ( SELECT 1
412 FROM gcs_cons_relationships gcr
413 WHERE gcr.parent_entity_id = geca.entity_id
414 AND gcr.hierarchy_id = geca.hierarchy_id
415 AND gcr.dominant_parent_flag = 'Y'
416 AND p_cal_period_end_date BETWEEN gcr.start_date AND NVL(gcr.end_date, p_cal_period_end_date)
417 AND gcr.child_entity_id = fb.entity_id)
418 )
419 AND fli_parents.line_item_id (+)= flit.line_item_id
420 GROUP BY fet.entity_name, flit.line_item_name,
421 fet.entity_id, flit.line_item_id,
422 fea.dim_attribute_varchar_member, fli_parents.line_item_id
423 ORDER BY line_item_name, entity_type, entity_name;
424
425 BEGIN
426 l_entity_type_attr_id := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').attribute_id;
427 l_cal_period_attr_id := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id;
428 l_entity_type_version_id := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').version_id;
429 l_cal_period_version_id := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
430
431 SELECT date_assign_value
432 INTO l_cal_period_end_date
433 FROM fem_cal_periods_attr
434 WHERE cal_period_id = p_cal_period_id
435 AND attribute_id = l_cal_period_attr_id
436 AND version_id = l_cal_period_version_id;
437
438 p_cmtb_col_xml := ' <TB_COL_DATA>' || new_line;
439 FOR v_entity_name IN c_entity_name( p_language_code,
440 p_hierarchy_id,
441 p_entity_id,
442 l_cal_period_end_date)
443 LOOP
444 p_cmtb_col_xml := p_cmtb_col_xml ||
445 ' <DimensionInfo><COL_NAME><![CDATA[' ||
446 v_entity_name.entity_name ||
447 ']]></COL_NAME></DimensionInfo>' || new_line;
448 EntityList.EXTEND;
449 l_maxnum_entities := l_maxnum_entities + 1;
450 EntityList(l_maxnum_entities) := v_entity_name.entity_name;
451 END LOOP;
452
453 --fnd_file.put_line(fnd_file.log, 'Begin CMTB XML generation');
454 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
455 THEN
456 fnd_log.STRING
457 (fnd_log.level_statement,
458 g_api, 'Begin CMTB XML generation'
459 );
460 END IF;
461
462 p_cmtb_col_xml := p_cmtb_col_xml ||
463 '</TB_COL_DATA>' || new_line;
464 -- Create a temporary CLOB to hold XML (because it may be of size > 32767 bytes)
465 DBMS_LOB.CREATETEMPORARY(l_cmtb_lines_xml, TRUE);
466 DBMS_LOB.WRITEAPPEND(l_cmtb_lines_xml ,
467 length(' <TB_LINES_DATA><TB_LINE_ROWS>'),
468 ' <TB_LINES_DATA><TB_LINE_ROWS>');
469 --fnd_file.put_line(fnd_file.log, 'Collect Data');
470 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
471 THEN
472 fnd_log.STRING
473 (fnd_log.level_statement,
474 g_api, 'Collect Data'
475 );
476 END IF;
477 OPEN c_tb_data( p_language_code,
478 p_hierarchy_id,
479 p_entity_id,
480 p_cal_period_id,
481 p_currency_code,
482 l_cal_period_end_date);
483 FETCH c_tb_data
484 BULK COLLECT INTO
485 l_cmtb_line_item_list,
486 l_cmtb_entity_name_list,
487 l_cmtb_balance_list,
488 l_cmtb_entity_type_list,
489 l_cmtb_line_item_id_list,
490 l_cmtb_entity_id_list,
491 l_cmtb_is_drillable_list;
492 CLOSE c_tb_data;
493
494 --fnd_file.put_line(fnd_file.log, 'End of Data Collection');
495 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
496 THEN
497 fnd_log.STRING
498 (fnd_log.level_statement,
499 g_api, 'End of Data Collection'
500 );
501 END IF;
502 l_item_list_first := l_cmtb_line_item_list.FIRST;
503 l_item_list_last := l_cmtb_line_item_list.LAST;
504 l_item_list_current := 1;
505 l_entity_index := EntityList.FIRST;
506 -- Evaluate all the reported entities
507 LOOP
508 --And check for balance availability against each reported line item
509 LOOP
510 --fnd_file.put_line(fnd_file.log, 'Processing Entity : ' || EntityList(l_entity_index));
511 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
512 THEN
513 fnd_log.STRING
514 (fnd_log.level_statement,
515 g_api, 'Processing Entity : ' || EntityList(l_entity_index)
516 );
517 END IF;
518 --fnd_file.put_line(fnd_file.log, 'for line item : ' || l_cmtb_line_item_list(l_item_list_current));
519 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
520 THEN
521 fnd_log.STRING
522 (fnd_log.level_statement,
523 g_api, 'for line item : ' || l_cmtb_line_item_list(l_item_list_current)
524 );
525 END IF;
526 --If the line item list contains reported entiry, then generate not for the actual balance
527 IF EntityList(l_entity_index) = l_cmtb_entity_name_list(l_item_list_current) THEN
528 BEGIN
529 --fnd_file.put_line(fnd_file.log, 'Processing Entity (with balance): ' ||
530 -- l_cmtb_entity_name_list(l_item_list_current));
531 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
532 THEN
533 fnd_log.STRING
534 (fnd_log.level_statement,
535 g_api, 'Processing Entity (with balance): ' ||
536 l_cmtb_entity_name_list(l_item_list_current)
537 );
538 END IF;
539 DBMS_LOB.WRITEAPPEND(l_cmtb_lines_xml,
540 length('<TB_LINE><LINE_ITEM_NAME><![CDATA[' ||
541 l_cmtb_line_item_list(l_item_list_current)||
542 ']]></LINE_ITEM_NAME>' || new_line ||
543 ' <ENTITY_NAME><![CDATA[' ||
544 l_cmtb_entity_name_list(l_item_list_current)||
545 ']]></ENTITY_NAME>' || new_line ||
546 ' <LINE_ITEM_ID>' ||
547 l_cmtb_line_item_id_list(l_item_list_current)||
548 '</LINE_ITEM_ID>' || new_line ||
549 ' <ENTITY_ID>' ||
550 l_cmtb_entity_id_list(l_item_list_current)||
551 '</ENTITY_ID>' || new_line ||
552 ' <IS_DRILLABLE><![CDATA[' ||
553 l_cmtb_is_drillable_list(l_item_list_current)||
554 ']]></IS_DRILLABLE>' || new_line ||
555 ' <BALANCE>' ||
556 l_cmtb_balance_list(l_item_list_current)||
557 '</BALANCE></TB_LINE>' || new_line),
558
559 '<TB_LINE><LINE_ITEM_NAME><![CDATA[' ||
560 l_cmtb_line_item_list(l_item_list_current)||
561 ']]></LINE_ITEM_NAME>' || new_line ||
562 ' <ENTITY_NAME><![CDATA[' ||
563 l_cmtb_entity_name_list(l_item_list_current)||
564 ']]></ENTITY_NAME>' || new_line ||
565 ' <LINE_ITEM_ID>' ||
566 l_cmtb_line_item_id_list(l_item_list_current)||
567 '</LINE_ITEM_ID>' || new_line ||
568 ' <ENTITY_ID>' ||
569 l_cmtb_entity_id_list(l_item_list_current)||
570 '</ENTITY_ID>' || new_line ||
571 ' <IS_DRILLABLE><![CDATA[' ||
572 l_cmtb_is_drillable_list(l_item_list_current)||
573 ']]></IS_DRILLABLE>' || new_line ||
574 ' <BALANCE>' ||
575 l_cmtb_balance_list(l_item_list_current)||
576 '</BALANCE></TB_LINE>' || new_line);
577
578 IF l_entity_index <= l_maxnum_entities THEN
579 l_item_list_current := l_item_list_current + 1;
580 END IF;
581 END;
582
583 ELSE
584
585 BEGIN
586 --fnd_file.put_line(fnd_file.log, 'Processing Entity (without balance): ' ||
587 -- EntityList(l_entity_index));
588 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
589 THEN
590 fnd_log.STRING
591 (fnd_log.level_statement,
592 g_api, 'Processing Entity (without balance): ' ||
593 EntityList(l_entity_index)
594 );
595 END IF;
596 DBMS_LOB.WRITEAPPEND(l_cmtb_lines_xml,
597 length('<TB_LINE><LINE_ITEM_NAME><![CDATA[' ||
598 l_cmtb_line_item_list(l_item_list_current)||
599 ']]></LINE_ITEM_NAME>' || new_line ||
600 ' <ENTITY_NAME><![CDATA[' ||
601 EntityList(l_entity_index)||
602 ']]></ENTITY_NAME>' || new_line ||
603 ' <LINE_ITEM_ID>' ||
604 l_cmtb_line_item_id_list(l_item_list_current)||
605 '</LINE_ITEM_ID>' || new_line ||
606 ' <ENTITY_ID>' ||
607 l_cmtb_entity_id_list(l_item_list_current)||
608 '</ENTITY_ID>' || new_line ||
609 ' <IS_DRILLABLE><![CDATA[' ||
610 'N'||
611 ']]></IS_DRILLABLE>' || new_line ||
612 ' <BALANCE>' ||
613 '</BALANCE></TB_LINE>' || new_line),
614
615 '<TB_LINE><LINE_ITEM_NAME><![CDATA[' ||
616 l_cmtb_line_item_list(l_item_list_current)||
617 ']]></LINE_ITEM_NAME>' || new_line ||
618 ' <ENTITY_NAME><![CDATA[' ||
619 EntityList(l_entity_index)||
620 ']]></ENTITY_NAME>' || new_line ||
621 ' <LINE_ITEM_ID>' ||
622 l_cmtb_line_item_id_list(l_item_list_current)||
623 '</LINE_ITEM_ID>' || new_line ||
624 ' <ENTITY_ID>' ||
625 l_cmtb_entity_id_list(l_item_list_current)||
626 '</ENTITY_ID>' || new_line ||
627 ' <IS_DRILLABLE><![CDATA[' ||
628 'N'||
629 ']]></IS_DRILLABLE>' || new_line ||
630 ' <BALANCE>' ||
631 '</BALANCE></TB_LINE>' || new_line);
632
633 --fnd_file.put_line(fnd_file.log, 'Line item context: '||l_item_list_current ||length(p_cmtb_lines_xml));
634 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
635 THEN
636 fnd_log.STRING
637 (fnd_log.level_statement,
638 g_api, 'Line item context: '||l_item_list_current ||length(p_cmtb_lines_xml)
639 );
640 END IF;
641 END;
642 END IF;
643 IF l_entity_index = l_maxnum_entities THEN
644 --fnd_file.put_line(fnd_file.log, 'Exit inner loop when: l_entity_index('||l_entity_index||
645 -- ') = l_maxnum_entities('||l_maxnum_entities||')');
646 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
647 THEN
648 fnd_log.STRING
649 (fnd_log.level_statement,
650 g_api, 'Exit inner loop when: l_entity_index('||l_entity_index||
651 ') = l_maxnum_entities('||l_maxnum_entities||')'
652 );
653 END IF;
654 EXIT;
655 END IF;
656 l_entity_index := l_entity_index + 1;
657 END LOOP;
658
659 IF l_item_list_current > l_item_list_last THEN
660 --fnd_file.put_line(fnd_file.log, 'Exit outer loop when: l_item_list_current('||l_item_list_current||
661 -- ') = l_item_list_last('||l_item_list_last||')');
662 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
663 THEN
664 fnd_log.STRING
665 (fnd_log.level_statement,
666 g_api, 'Exit outer loop when: l_item_list_current('||l_item_list_current||
667 ') = l_item_list_last('||l_item_list_last||')'
668 );
669 END IF;
670 DBMS_LOB.WRITEAPPEND(l_cmtb_lines_xml, length('</TB_LINE_ROWS></TB_LINES_DATA>' || new_line),
671 '</TB_LINE_ROWS></TB_LINES_DATA>' || new_line);
672
673 EXIT;
674 ELSIF l_entity_index = l_maxnum_entities THEN
675 --fnd_file.put_line(fnd_file.log, 'Reset entity context when: l_entity_index('||l_entity_index||
676 -- ') = l_maxnum_entities('||l_maxnum_entities||')');
677 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
678 THEN
679 fnd_log.STRING
680 (fnd_log.level_statement,
681 g_api, 'Reset entity context when: l_entity_index('||l_entity_index||
682 ') = l_maxnum_entities('||l_maxnum_entities||')'
683 );
684 END IF;
685 l_entity_index := EntityList.FIRST;
686 DBMS_LOB.WRITEAPPEND(l_cmtb_lines_xml, length('</TB_LINE_ROWS><TB_LINE_ROWS>'),
687 '</TB_LINE_ROWS><TB_LINE_ROWS>');
688 END IF;
689
690 END LOOP;
691 p_cmtb_lines_xml := l_cmtb_lines_xml;
692
693 END generate_cmtb_entity_name_xml;
694
695 PROCEDURE generate_cmtb_xml ( p_entry_id IN NUMBER,
696 p_entity_id IN NUMBER,
697 p_hierarchy_id IN NUMBER,
698 p_cal_period_id IN NUMBER,
699 p_balance_type_code IN VARCHAR2,
700 p_currency_code IN VARCHAR2)
701 IS
702
703 l_cmtb_header_data r_cmtb_header_data;
704 l_cmtb_header_xml CLOB;
705 l_cmtb_col_xml CLOB;
706 l_cmtb_lines_xml CLOB;
707 l_cmtb_xml CLOB;
708 file_exist NUMBER;
709 p_xml_file_type VARCHAR2(10) := 'CMTB';
710 doc_in xmldom.DOMDocument;
711 retval xmldom.domnodelist;
712 prsr xmlparser.parser;
713 l_node xmldom.domnode;
714
715 CURSOR c_languages
716 IS
717 SELECT language_code
718 FROM fnd_languages
719 WHERE installed_flag IN ('I','B');
720
721 CURSOR c_xml_id ( p_language_code VARCHAR2,
722 p_entry_id NUMBER )
723 IS
724 SELECT 1
725 FROM gcs_xml_files
726 WHERE XML_FILE_ID = p_entry_id
727 AND XML_FILE_TYPE = p_xml_file_type
728 AND LANGUAGE = p_language_code;
729
730 BEGIN
731
732 FOR v_languages IN c_languages
733 LOOP
734 BEGIN
735 -- Get the Entity Header Info for the entry_id
736 SELECT gcer.run_name,
737 fcpt.cal_period_name,
738 flv.meaning balance_type,
739 gcer.start_time,
740 gcer.end_time,
741 fct.name currency_name,
742 ght.hierarchy_name,
743 ght.hierarchy_id,
744 fcpt.cal_period_id
745 INTO l_cmtb_header_data.run_name,
746 l_cmtb_header_data.cal_period_name,
747 l_cmtb_header_data.balance_type,
748 l_cmtb_header_data.start_time,
749 l_cmtb_header_data.end_time,
750 l_cmtb_header_data.currency_name,
751 l_cmtb_header_data.hierarchy_name,
752 l_cmtb_header_data.hierarchy_id,
753 l_cmtb_header_data.cal_period_id
754 FROM gcs_cons_eng_runs gcer,
755 gcs_hierarchies_tl ght,
756 fem_cal_periods_tl fcpt,
757 fnd_currencies_tl fct,
758 fnd_lookup_values flv
759 WHERE gcer.cal_period_id = fcpt.cal_period_id
760 AND fct.currency_code = p_currency_code
761 AND fct.language = v_languages.language_code
762 AND gcer.balance_type_code = flv.lookup_code
763 AND flv.lookup_type = 'BALANCE_TYPE_CODE'
764 AND flv.language = v_languages.language_code
765 AND fcpt.language = v_languages.language_code
766 AND gcer.hierarchy_id = p_hierarchy_id
767 AND gcer.hierarchy_id = ght.hierarchy_id
768 AND ght.language = v_languages.language_code
769 AND gcer.RUN_ENTITY_ID = p_entity_id
770 AND gcer.BALANCE_TYPE_CODE = p_balance_type_code
771 AND gcer.CAL_PERIOD_ID = p_cal_period_id
772 AND gcer.MOST_RECENT_FLAG = 'Y'
773 AND rownum <= 1;
774
775 --fnd_file.put_line(fnd_file.log, 'Start CMTB XML for entry_id: ' || p_entry_id);
776 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
777 THEN
778 fnd_log.STRING
779 (fnd_log.level_statement,
780 g_api, 'Start CMTB XML for entry_id: '||p_entry_id
781 );
782 END IF;
783
784 -- Generate the header level XML
785 generate_cmtb_header_xml ( p_cmtb_header_data => l_cmtb_header_data,
786 p_cmtb_header_xml => l_cmtb_header_xml);
787
788 -- Generate the entity name XML
789 generate_cmtb_entity_name_xml ( p_language_code => v_languages.language_code,
790 p_hierarchy_id => p_hierarchy_id,
791 p_entity_id => p_entity_id,
792 p_cal_period_id => p_cal_period_id,
793 p_balance_type_code => p_balance_type_code,
794 p_currency_code => p_currency_code,
795 p_cmtb_col_xml => l_cmtb_col_xml,
796 p_cmtb_lines_xml => l_cmtb_lines_xml);
797
798 l_cmtb_xml := '<CMTB_DATA>' || l_cmtb_header_xml || l_cmtb_col_xml || l_cmtb_lines_xml ||'</CMTB_DATA>';
799
800 OPEN c_xml_id(v_languages.language_code, p_entry_id);
801 FETCH c_xml_id INTO file_exist;
802 IF c_xml_id%NOTFOUND THEN
803 insert into gcs_xml_files ( xml_file_id,
804 xml_file_type,
805 language,
806 xml_data,
807 last_update_date,
808 last_updated_by,
809 creation_date,
810 created_by,
811 object_version_number)
812 values
813 ( p_entry_id,
814 p_xml_file_type,
815 v_languages.language_code,
816 '<?xml version="1.0"?>'||new_line||l_cmtb_xml,
817 sysdate,
818 0,
819 sysdate,
820 0,
821 0);
822 CLOSE c_xml_id;
823
824 --fnd_file.put_line(fnd_file.log, 'Inserted CMTB XML for entry_id: ' || p_entry_id);
825 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
826 THEN
827 fnd_log.STRING
828 (fnd_log.level_statement,
829 g_api, 'Inserted CMTB XML for entry_id: '||p_entry_id
830 );
831 END IF;
832
833 ELSE
834 update gcs_xml_files
835 set xml_data = '<?xml version="1.0"?>'||new_line||l_cmtb_xml,
836 last_update_date = sysdate,
837 last_updated_by = 0,
838 object_version_number = object_version_number + 1
839 where xml_file_id = p_entry_id
840 and xml_file_type = p_xml_file_type
841 and language = v_languages.language_code;
842
843 CLOSE c_xml_id;
844
845 --fnd_file.put_line(fnd_file.log, 'Updated CMTB XML for entry_id: ' || p_entry_id);
846 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
847 THEN
848 fnd_log.STRING
849 (fnd_log.level_statement,
850 g_api, 'Updated CMTB XML for entry_id: '||p_entry_id
851 );
852 END IF;
853
854 END IF;
855 COMMIT;
856
857
858 EXCEPTION
859 WHEN others THEN
860 BEGIN
861 --fnd_file.put_line(fnd_file.log, 'Exception CMTB XML for entry_id: ' || p_entry_id||' - ' || SQLERRM);
862 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
863 THEN
864 fnd_log.STRING
865 (fnd_log.level_statement,
866 g_api, 'Exception CMTB XML for entry_id: '||p_entry_id ||' - '||SQLERRM
867 );
868 END IF;
869 END;
870 END;
871 END LOOP;
872 END generate_cmtb_xml;
873
874
875 PROCEDURE generate_entry_header_xml ( p_entry_header_data IN r_entry_header_data,
876 p_entry_header_xml OUT NOCOPY CLOB )
877
878 IS
879
880 BEGIN
881
882 p_entry_header_xml := ' <ENTRY_HEADER>' || new_line ||
883 ' <ENTRY_NAME><![CDATA[' || p_entry_header_data.entry_name || ']]></ENTRY_NAME>' || new_line ||
884 ' <DESCRIPTION><![CDATA[' || p_entry_header_data.description || ']]></DESCRIPTION>' || new_line ||
885 ' <HIERARCHY_NAME><![CDATA[' || p_entry_header_data.hierarchy_name || ']]></HIERARCHY_NAME>'|| new_line ||
886 ' <ENTITY_NAME><![CDATA[' || p_entry_header_data.entity_name || ']]></ENTITY_NAME>' || new_line ||
887 ' <CURRENCY_NAME><![CDATA[' || p_entry_header_data.currency_name || ']]></CURRENCY_NAME>' || new_line ||
888 ' <CAL_PERIOD_NAME><![CDATA['|| p_entry_header_data.cal_period_name||']]></CAL_PERIOD_NAME>'|| new_line ||
889 ' <SUSPENSE_FLAG><![CDATA[' || p_entry_header_data.suspense_flag || ']]></SUSPENSE_FLAG>' || new_line ||
890 ' <HIERARCHY_ID>' || p_entry_header_data.hierarchy_id || '</HIERARCHY_ID>' || new_line ||
891 ' <ENTITY_ID>' || p_entry_header_data.entity_id || '</ENTITY_ID>' || new_line ||
892 ' <CAL_PERIOD_ID>' || p_entry_header_data.cal_period_id || '</CAL_PERIOD_ID>' || new_line ||
893 ' <IS_DRILLABLE><![CDATA[';
894
895 --Bugfix 4725916: Modifed is drillable to check the category code rather than the entity type
896 IF p_entry_header_data.category_code IN ('DATAPREPARATION', 'INTRACOMPANY', 'TRANSLATION', 'INTERCOMPANY') THEN
897 p_entry_header_xml := p_entry_header_xml || 'Y';
898 ELSE
899 p_entry_header_xml := p_entry_header_xml || 'N';
900 END IF;
901
902 p_entry_header_xml := p_entry_header_xml || ']]></IS_DRILLABLE>' || new_line ||
903 ' </ENTRY_HEADER>' || new_line;
904
905 END generate_entry_header_xml;
906
907
908 PROCEDURE generate_entry_xml ( p_entry_id IN NUMBER,
909 p_category_code IN VARCHAR2,
910 p_cons_rule_flag IN VARCHAR2 )
911 IS
912 l_entry_header_data r_entry_header_data;
913 l_entry_header_xml CLOB;
914 l_dimension_name_xml CLOB;
915 l_entry_lines_xml CLOB;
916 l_entry_xml CLOB;
917 l_entry_lines_sql VARCHAR2(10000);
918 l_rp_lines_xml CLOB;
919 l_rp_lines_sql VARCHAR2(10000);
920 l_qryCtx DBMS_XMLGEN.ctxHandle;
921 file_exist NUMBER;
922 doc_in xmldom.DOMDocument;
923 retval xmldom.domnodelist;
924 prsr xmlparser.parser;
925 l_node xmldom.domnode;
926 doc_in1 xmldom.DOMDocument;
927 retval1 xmldom.domnodelist;
928 prsr1 xmlparser.parser;
929 l_node1 xmldom.domnode;
930 p_xml_file_type VARCHAR2(10) := 'ENTRY';
931 CURSOR c_languages
932 IS
933 SELECT language_code
934 FROM fnd_languages
935 WHERE installed_flag IN ('I','B');
936
937 CURSOR c_xml_id ( p_language_code VARCHAR2,
938 p_entry_id NUMBER )
939 IS
940 SELECT 1
941 FROM gcs_xml_files
942 WHERE xml_file_id = p_entry_id
943 AND xml_file_type = p_xml_file_type
944 AND language = p_language_code;
945
946 BEGIN
947
948 --fnd_file.put_line(fnd_file.log, 'Enter: gcs_xml_gen_pkg.generate_entry_xml(p_entry_id =>'||p_entry_id||
949 -- ', p_category_code =>'||p_category_code||', p_cons_rule_flag =>' || p_cons_rule_flag ||' )');
950 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
951 THEN
952 fnd_log.STRING
953 (fnd_log.level_statement,
954 g_api,
955 'Enter: gcs_xml_gen_pkg.generate_entry_xml(p_entry_id =>'||p_entry_id||
956 ', p_category_code =>'||p_category_code||', p_cons_rule_flag =>' || p_cons_rule_flag ||' )'
957 );
958 END IF;
959
960 FOR v_languages IN c_languages
961 LOOP
962 BEGIN
963 -- Get the Entry Header Info for the entry_id
964 SELECT geh.entry_name,
965 geh.description,
966 ghtl.hierarchy_name,
967 fetl.entity_name,
968 fctl.name currency_name,
969 fcptl_start.cal_period_name,
970 geh.suspense_exceeded_flag,
971 geh.rule_id,
972 geh.entity_id,
973 geh.hierarchy_id,
974 geh.start_cal_period_id,
975 geh.balance_type_code,
976 geh.currency_code,
977 fea_type.dim_attribute_varchar_member,
978 geh.category_code
979 INTO l_entry_header_data.entry_name,
980 l_entry_header_data.description,
981 l_entry_header_data.hierarchy_name,
982 l_entry_header_data.entity_name,
983 l_entry_header_data.currency_name,
984 l_entry_header_data.cal_period_name,
985 l_entry_header_data.suspense_flag,
986 l_entry_header_data.rule_id,
987 l_entry_header_data.entity_id,
988 l_entry_header_data.hierarchy_id,
989 l_entry_header_data.cal_period_id,
990 l_entry_header_data.balance_type_code,
991 l_entry_header_data.currency_code,
992 l_entry_header_data.entity_type_code,
993 l_entry_header_data.category_code
994 FROM gcs_entry_headers geh,
995 gcs_hierarchies_tl ghtl,
996 fem_entities_tl fetl,
997 fnd_currencies_tl fctl,
998 fem_cal_periods_tl fcptl_start,
999 fem_cal_periods_tl fcptl_end,
1000 fem_entities_attr fea_type
1001 WHERE geh.entry_id = p_entry_id
1002 AND geh.hierarchy_id = ghtl.hierarchy_id
1003 AND ghtl.language = v_languages.language_code
1004 AND geh.entity_id = fetl.entity_id
1005 AND fctl.language = v_languages.language_code
1006 AND geh.currency_code = fctl.currency_code
1007 AND fetl.language = v_languages.language_code
1008 AND geh.start_cal_period_id = fcptl_start.cal_period_id
1009 AND fcptl_start.language = v_languages.language_code
1010 AND geh.end_cal_period_id = fcptl_end.cal_period_id(+)
1011 AND fcptl_end.language(+) = v_languages.language_code
1012 AND fea_type.entity_id = geh.entity_id
1013 AND fea_type.attribute_id = l_entity_type_attr
1014 AND fea_type.version_id = l_entity_type_version
1015 AND ROWNUM <= 1;
1016 --fnd_file.put_line(fnd_file.log, ' Start Header XML generation ');
1017 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1018 THEN
1019 fnd_log.STRING
1020 (fnd_log.level_statement,
1021 g_api, ' Select statement results: '||
1022 'l_entry_header_data.entry_name =>'||l_entry_header_data.entry_name||
1023 'l_entry_header_data.description =>'||l_entry_header_data.description||
1024 'l_entry_header_data.hierarchy_name =>'||l_entry_header_data.hierarchy_name||
1025 'l_entry_header_data.entity_name =>'||l_entry_header_data.entity_name||
1026 'l_entry_header_data.currency_name =>'||l_entry_header_data.currency_name||
1027 'l_entry_header_data.cal_period_name =>'||l_entry_header_data.cal_period_name||
1028 'l_entry_header_data.suspense_flag =>'||l_entry_header_data.suspense_flag||
1029 'l_entry_header_data.rule_id =>'||l_entry_header_data.rule_id||
1030 'l_entry_header_data.entity_id =>'||l_entry_header_data.entity_id||
1031 'l_entry_header_data.hierarchy_id =>'||l_entry_header_data.hierarchy_id||
1032 'l_entry_header_data.cal_period_id =>'||l_entry_header_data.cal_period_id||
1033 'l_entry_header_data.balance_type_code =>'||l_entry_header_data.balance_type_code||
1034 'l_entry_header_data.currency_code =>'||l_entry_header_data.currency_code
1035 );
1036 END IF;
1037
1038 -- Generate the header level XML
1039 generate_entry_header_xml( p_entry_header_data => l_entry_header_data,
1040 p_entry_header_xml => l_entry_header_xml );
1041
1042 --fnd_file.put_line(fnd_file.log, ' Generated Header XML Successfully ');
1043 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1044 THEN
1045 fnd_log.STRING
1046 (fnd_log.level_statement,
1047 g_api, ' Generated Header XML Successfully '
1048 );
1049 END IF;
1050
1051 IF (p_category_code <> 'AGGREGATION') THEN
1052
1053 --fnd_file.put_line(fnd_file.log, 'Start: generate_dimension_name_xml ( p_language_code => '||
1054 -- v_languages.language_code||
1055 -- 'p_fem_dim_y_n => N'||
1056 -- 'p_xml_file_type => ENTRY'||
1057 -- 'p_dimension_name_xml => ');
1058 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1059 THEN
1060 fnd_log.STRING
1061 (fnd_log.level_statement,
1062 g_api, 'Start: generate_dimension_name_xml ( p_language_code => '||v_languages.language_code||
1063 'p_fem_dim_y_n => N'||
1064 'p_xml_file_type => ENTRY'||
1065 'p_dimension_name_xml => '
1066 );
1067 END IF;
1068 -- Generate the dimension name XML
1069 generate_dimension_name_xml ( p_language_code => v_languages.language_code,
1070 p_fem_dim_y_n => 'N',
1071 p_xml_file_type => 'ENTRY',
1072 p_dimension_name_xml => l_dimension_name_xml );
1073
1074 --fnd_file.put_line(fnd_file.log, 'End: generate_dimension_name_xml ( p_language_code => '||
1075 -- v_languages.language_code||
1076 -- 'p_fem_dim_y_n => N'||
1077 -- 'p_xml_file_type => ENTRY'||
1078 -- 'p_dimension_name_xml => ');
1079 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1080 THEN
1081 fnd_log.STRING
1082 (fnd_log.level_statement,
1083 g_api, 'End: generate_dimension_name_xml ( p_language_code => '||v_languages.language_code||
1084 'p_fem_dim_y_n => N'||
1085 'p_xml_file_type => ENTRY'||
1086 'p_dimension_name_xml => '
1087 );
1088 END IF;
1089 -- take the sql query from gcs_xml_utility_pkg.g_entry_lines_select_stmnt
1090 -- add the clause for gel.entry_id and lang.language_code
1091
1092 l_entry_lines_sql:= gcs_xml_utility_pkg.g_entry_lines_select_stmnt||
1093 ' and lang.language_code = '''||
1094 v_languages.language_code||
1095 ''' and gel.entry_id = '||
1096 p_entry_id|| ' ORDER BY ' ||
1097 gcs_xml_utility_pkg.g_order_by_stmnt;
1098
1099 --fnd_file.put_line(fnd_file.log, 'l_entry_lines_sql ' || l_entry_lines_sql);
1100 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1101 THEN
1102 fnd_log.STRING
1103 (fnd_log.level_statement,
1104 g_api, 'l_entry_lines_sql '||l_entry_lines_sql
1105 );
1106 END IF;
1107 -- Generate new query context and set desired Row Set and Row Tag and
1108 -- generate XML data
1109 l_qryCtx := DBMS_XMLGEN.newContext(l_entry_lines_sql);
1110 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'ENTRY_LINES');
1111 DBMS_XMLGEN.setRowTag(l_qryCtx,'ENTRY_LINES_ROW');
1112 DBMS_XMLGEN.setConvertSpecialChars(l_qryCtx,FALSE);
1113 l_entry_lines_xml := DBMS_XMLGEN.getXML(l_qryCtx);
1114
1115 -- Remove xml version header tag from XML data
1116 IF (l_entry_lines_xml IS NOT NULL) THEN
1117 prsr := xmlparser.newparser;
1118 xmlparser.parseclob (prsr, l_entry_lines_xml);
1119 doc_in := xmlparser.getdocument (prsr);
1120 xmlparser.freeparser (prsr);
1121 retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'ENTRY_LINES' );
1122 l_node := xmldom.item(retval , 0);
1123 xmldom.writetoclob(l_node, l_entry_lines_xml);
1124 END IF;
1125 l_entry_xml := '<ENTRY_DATA> '|| new_line ||
1126 l_entry_header_xml|| l_dimension_name_xml|| l_entry_lines_xml||
1127 '</ENTRY_DATA> ';
1128
1129 --fnd_file.put_line(fnd_file.log, 'Entry XML generated for entry id: ' || p_entry_id);
1130 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1131 THEN
1132 fnd_log.STRING
1133 (fnd_log.level_statement,
1134 g_api, 'Entry XML generated for entry id: '||p_entry_id
1135 );
1136 END IF;
1137
1138 ELSE
1139
1140 generate_cmtb_xml ( p_entry_id => p_entry_id,
1141 p_entity_id => l_entry_header_data.entity_id,
1142 p_hierarchy_id => l_entry_header_data.hierarchy_id,
1143 p_cal_period_id => l_entry_header_data.cal_period_id,
1144 p_balance_type_code => l_entry_header_data.balance_type_code,
1145 p_currency_code => l_entry_header_data.currency_code);
1146
1147 END IF;
1148
1149 IF (p_cons_rule_flag = 'Y') THEN
1150
1151 l_rp_lines_sql:= gcs_xml_utility_pkg.g_rp_select_stmnt||
1152 ' and lang.language_code = '''||
1153 v_languages.language_code||
1154 ''' and gel.rule_id = '||
1155 l_entry_header_data.rule_id|| ' ORDER BY ' ||
1156 gcs_xml_utility_pkg.g_order_by_stmnt||
1157 gcs_xml_utility_pkg.g_order_by_stmnt1;
1158
1159 -- Generate new query context and set desired Row Set and Row Tag and
1160 -- generate XML data
1161 l_qryCtx := DBMS_XMLGEN.newContext(l_rp_lines_sql);
1162 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'RP_LINES');
1163 DBMS_XMLGEN.setRowTag(l_qryCtx,'RP_LINES_ROW');
1164 l_rp_lines_xml := DBMS_XMLGEN.getXML(l_qryCtx);
1165
1166 -- Remove xml version header tag from XML data
1167 IF (l_rp_lines_xml IS NOT NULL) THEN
1168 prsr := xmlparser.newparser;
1169 xmlparser.parseclob (prsr, l_rp_lines_xml);
1170 doc_in := xmlparser.getdocument (prsr);
1171 xmlparser.freeparser (prsr);
1172 retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'RP_LINES' );
1173 l_node := xmldom.item(retval , 0);
1174 xmldom.writetoclob(l_node, l_rp_lines_xml);
1175 END IF;
1176 END IF;
1177
1178 IF (p_category_code <> 'AGGREGATION') THEN
1179
1180 OPEN c_xml_id(v_languages.language_code, p_entry_id);
1181 FETCH c_xml_id INTO file_exist;
1182 IF c_xml_id%NOTFOUND THEN
1183 insert into gcs_xml_files
1184 ( xml_file_id,
1185 xml_file_type,
1186 language,
1187 xml_data,
1188 xml_execution_data,
1189 last_update_date,
1190 last_updated_by,
1191 creation_date,
1192 created_by,
1193 object_version_number)
1194 values ( p_entry_id,
1195 p_xml_file_type,
1196 v_languages.language_code,
1197 '<?xml version="1.0"?>'||new_line||l_entry_xml,
1198 '<?xml version="1.0"?>'||new_line||l_rp_lines_xml,
1199 sysdate,
1200 0,
1201 sysdate,
1202 0,
1203 0 );
1204
1205 CLOSE c_xml_id;
1206
1207 --fnd_file.put_line(fnd_file.log, 'Inserted Entry XML for entry_id: ' || p_entry_id);
1208 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1209 THEN
1210 fnd_log.STRING
1211 (fnd_log.level_statement,
1212 g_api, 'Inserted Entry XML for entry_id: '||p_entry_id
1213 );
1214 END IF;
1215
1216 IF p_cons_rule_flag = 'Y' THEN
1217 --fnd_file.put_line(fnd_file.log, 'Inserted RP XML for entry_id: ' || p_entry_id);
1218 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1219 THEN
1220 fnd_log.STRING
1221 (fnd_log.level_statement,
1222 g_api, 'Inserted RP XML for entry_id: '||p_entry_id );
1223 END IF;
1224 END IF;
1225
1226 ELSE
1227 update gcs_xml_files
1228 set xml_data = '<?xml version="1.0"?>'||new_line||l_entry_xml,
1229 xml_execution_data = '<?xml version="1.0"?>'||new_line||l_rp_lines_xml,
1230 last_update_date = sysdate,
1231 last_updated_by = 0,
1232 object_version_number = object_version_number + 1
1233 where xml_file_id = p_entry_id
1234 and xml_file_type = p_xml_file_type
1235 and language = v_languages.language_code;
1236
1237 CLOSE c_xml_id;
1238
1239 --fnd_file.put_line(fnd_file.log, 'Updated Entry XML for entry_id: ' || p_entry_id);
1240 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1241 THEN
1242 fnd_log.STRING
1243 (fnd_log.level_statement,
1244 g_api, 'Updated Entry XML for entry_id: '||p_entry_id
1245 );
1246 END IF;
1247
1248 IF p_cons_rule_flag = 'Y' THEN
1249 --fnd_file.put_line(fnd_file.log, 'Updated RP XML for entry_id: ' || p_entry_id);
1250 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1251 THEN
1252 fnd_log.STRING
1253 (fnd_log.level_statement,
1254 g_api, 'Updated RP XML for entry_id: '||p_entry_id );
1255 END IF;
1256 END IF;
1257
1258 END IF;
1259 COMMIT;
1260 END IF;
1261 exception
1262 when others then
1263 begin
1264 DBMS_XMLGEN.closeContext(l_qryCtx);
1265 --fnd_file.put_line(fnd_file.log, 'Exception: Others - entry_id: ' || p_entry_id||' - '|| SQLERRM);
1266 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1267 THEN
1268 fnd_log.STRING
1269 (fnd_log.level_error,
1270 g_api, 'Exception: Others - entry_id: '||p_entry_id ||' - '||SQLERRM);
1271 END IF;
1272 end;
1273 END;
1274 END LOOP;
1275 END generate_entry_xml;
1276
1277 PROCEDURE generate_ds_header_xml ( p_ds_header_data IN r_ds_header_data,
1278 p_dataset_code IN NUMBER,
1279 p_ds_header_xml OUT NOCOPY CLOB )
1280
1281 IS
1282
1283 BEGIN
1284
1285 p_ds_header_xml:= ' <Header>' || new_line ||
1286 ' <LoadId>' || p_ds_header_data.load_id || '</LoadId>' || new_line ||
1287 ' <LoadName><![CDATA[' || p_ds_header_data.load_name || ']]></LoadName>' || new_line ||
1288 ' <EntityName><![CDATA[' || p_ds_header_data.entity_name || ']]></EntityName>' || new_line ||
1289 ' <BalanceTypeName><![CDATA[' || p_ds_header_data.balance_type || ']]></BalanceTypeName>' || new_line ||
1290 ' <AmountTypeCode><![CDATA[' || p_ds_header_data.amount_type_code || ']]></AmountTypeCode>' || new_line ||
1291 ' <AmountTypeName><![CDATA[' || p_ds_header_data.amount_type_name || ']]></AmountTypeName>' || new_line ||
1292 ' <CurrencyTypeCode><![CDATA[' || p_ds_header_data.curr_type_code || ']]></CurrencyTypeCode>' || new_line ||
1293 ' <CurrencyTypeName><![CDATA[' || p_ds_header_data.curr_type_name || ']]></CurrencyTypeName>' || new_line ||
1294 ' <CurrencyCode><![CDATA[' || p_ds_header_data.currency_code || ']]></CurrencyCode>' || new_line ||
1295 ' <EntityCurrencyCode><![CDATA['||p_ds_header_data.entity_currency_code||']]></EntityCurrencyCode>'||new_line||
1296 ' <CurrencyName><![CDATA[' || p_ds_header_data.currency_name || ']]></CurrencyName>' || new_line ||
1297 ' <CalPeriodId>' || p_ds_header_data.cal_period_id || '</CalPeriodId>' || new_line ||
1298 ' <CalPeriodName><![CDATA[' || p_ds_header_data.cal_period_name || ']]></CalPeriodName>' || new_line ||
1299 ' <MeasureTypeCode><![CDATA[' || p_ds_header_data.measure_type_code || ']]></MeasureTypeCode>' || new_line ||
1300 ' <MeasureType><![CDATA[' || p_ds_header_data.measure_type_name || ']]></MeasureType>' || new_line ||
1301 ' <StartTime><![CDATA[' || p_ds_header_data.start_time || ']]></StartTime>' || new_line ||
1302 ' <EndTime><![CDATA[' || p_ds_header_data.end_time || ']]></EndTime>' || new_line ||
1303 ' <StatusCode><![CDATA[' || p_ds_header_data.status_code || ']]></StatusCode>' || new_line ||
1304 ' <SourceSystemCode><![CDATA[' || p_ds_header_data.source_system_code || ']]></SourceSystemCode>' || new_line ||
1305 ' <LedgerId>' || p_ds_header_data.ledger_id || '</LedgerId>' || new_line ||
1306 ' <DatasetCode>' || p_dataset_code || '</DatasetCode>' || new_line ||
1307 '</Header>' || new_line;
1308
1309 END generate_ds_header_xml;
1310
1311 PROCEDURE generate_ds_xml ( p_load_id IN NUMBER)
1312 IS
1313
1314 l_ds_header_data r_ds_header_data;
1315 l_ds_header_xml CLOB;
1316 l_dimension_name_xml CLOB;
1317 l_ds_lines_xml CLOB;
1318 l_dstb_lines_xml CLOB;
1319 l_ds_xml CLOB;
1320 l_dstb_xml CLOB;
1321 l_ds_lines_sql VARCHAR2(10000);
1322 l_dstb_lines_sql VARCHAR2(10000);
1323 l_dataset_code NUMBER;
1324 l_qryCtx DBMS_XMLGEN.ctxHandle;
1325 file_exist NUMBER;
1326 doc_in xmldom.DOMDocument;
1327 retval xmldom.domnodelist;
1328 prsr xmlparser.parser;
1329 l_node xmldom.domnode;
1330 doc_in1 xmldom.DOMDocument;
1331 retval1 xmldom.domnodelist;
1332 prsr1 xmlparser.parser;
1333 l_node1 xmldom.domnode;
1334 doc_in2 xmldom.DOMDocument;
1335 retval2 xmldom.domnodelist;
1336 prsr2 xmlparser.parser;
1337 l_node2 xmldom.domnode;
1338 l_xml_file_type VARCHAR2(10);
1339 l_value_set_where_clause VARCHAR2(10000);
1340 l_rowseq_hash_sql VARCHAR2(2000);
1341
1342 TYPE r_rowseq_hash_table IS TABLE OF r_rowseq_hash_data;
1343 l_rowseq_hash_table r_rowseq_hash_table;
1344
1345 CURSOR c_languages
1346 IS
1347 SELECT language_code
1348 FROM fnd_languages
1349 WHERE installed_flag IN ('I','B');
1350
1351 CURSOR c_xml_id (
1352 p_language_code VARCHAR2,
1353 p_load_id NUMBER,
1354 l_xml_file_type VARCHAR2 )
1355 IS
1356 SELECT 1
1357 FROM gcs_xml_files
1358 WHERE xml_file_id = p_load_id
1359 AND xml_file_type = l_xml_file_type
1360 AND language = p_language_code;
1361
1362
1363 BEGIN
1364
1365 FOR v_languages IN c_languages
1366 LOOP
1367 BEGIN
1368 -- Get the DS Header Info for the load_id
1369
1370 SELECT gdsd.load_id,
1371 gdsd.load_name,
1372 gdsd.entity_id,
1373 fet.entity_name,
1374 flv.meaning balance_type,
1375 gdsd.amount_type_code,
1376 amt.meaning amount_type_name,
1377 gdsd.currency_type_code,
1378 curr.meaning curr_type_name,
1379 gdsd.currency_code,
1380 fct.name currency_name,
1381 gdsd.cal_period_id,
1382 fcpt.cal_period_name,
1383 gdsd.measure_type_code,
1384 measure.meaning measure_type_name,
1385 gdsd.start_time,
1386 gdsd.end_time,
1387 gdsd.status_code,
1388 gdsd.balance_type_code,
1389 gdsd.balances_rule_id,
1390 foct.object_name balances_rule_name,
1391 flt.ledger_id,
1392 flt.ledger_name,
1393 fea_srcsys.dim_attribute_numeric_member source_system_code,
1394 fla_comp.dim_attribute_varchar_member entity_currency_code,
1395 fla_gvsc.dim_attribute_numeric_member sub_global_vs_combo_id
1396 INTO l_ds_header_data.load_id,
1397 l_ds_header_data.load_name,
1398 l_ds_header_data.entity_id,
1399 l_ds_header_data.entity_name,
1400 l_ds_header_data.balance_type,
1401 l_ds_header_data.amount_type_code,
1402 l_ds_header_data.amount_type_name,
1403 l_ds_header_data.curr_type_code,
1404 l_ds_header_data.curr_type_name,
1405 l_ds_header_data.currency_code,
1406 l_ds_header_data.currency_name,
1407 l_ds_header_data.cal_period_id,
1408 l_ds_header_data.cal_period_name,
1409 l_ds_header_data.measure_type_code,
1410 l_ds_header_data.measure_type_name,
1411 l_ds_header_data.start_time,
1412 l_ds_header_data.end_time,
1413 l_ds_header_data.status_code,
1414 l_ds_header_data.balance_type_code,
1415 l_ds_header_data.balances_rule_id,
1416 l_ds_header_data.balances_rule_name,
1417 l_ds_header_data.ledger_id,
1418 l_ds_header_data.ledger_name,
1419 l_ds_header_data.source_system_code,
1420 l_ds_header_data.entity_currency_code,
1421 l_ds_header_data.sub_global_vs_combo_id
1422 FROM gcs_data_sub_dtls gdsd,
1423 fem_entities_tl fet,
1424 fnd_currencies_tl fct,
1425 fem_cal_periods_tl fcpt,
1426 fnd_lookup_values flv,
1427 fnd_lookup_values amt,
1428 fnd_lookup_values curr,
1429 fnd_lookup_values measure,
1430 fem_ledgers_tl flt,
1431 fem_entities_attr fea_ledger,
1432 fem_entities_attr fea_srcsys,
1433 fem_object_catalog_tl foct,
1434 fem_ledgers_attr fla_comp,
1435 fem_ledgers_attr fla_gvsc
1436 WHERE gdsd.load_id = p_load_id
1437 AND gdsd.entity_id = fet.entity_id
1438 AND fet.language = v_languages.language_code
1439 AND gdsd.currency_code = fct.currency_code(+)
1440 AND fct.language (+) = v_languages.language_code
1441 AND gdsd.cal_period_id = fcpt.cal_period_id
1442 AND fcpt.language = v_languages.language_code
1443 AND gdsd.balance_type_code = flv.lookup_code
1444 AND flv.language = v_languages.language_code
1445 AND flv.lookup_type = 'GCS_BALANCE_TYPE_CODES'
1446 AND flv.view_application_id = 266
1447 AND gdsd.amount_type_code = amt.lookup_code
1448 AND amt.lookup_type = 'GCS_AMOUNT_TYPE_CODES'
1449 AND amt.LANGUAGE = v_languages.language_code
1450 AND amt.view_application_id = 266
1451 AND gdsd.currency_type_code = curr.lookup_code
1452 AND curr.lookup_type = 'GCS_CURRENCY_TYPES'
1453 AND curr.LANGUAGE = v_languages.language_code
1454 AND curr.view_application_id = 266
1455 AND gdsd.measure_type_code = measure.lookup_code
1456 AND measure.lookup_type = 'GCS_MEASURE_TYPE_CODES'
1457 AND measure.LANGUAGE = v_languages.language_code
1458 AND measure.view_application_id = 266
1459 AND fea_ledger.entity_id = gdsd.entity_id
1460 AND fea_ledger.attribute_id = l_entity_ledger_attr
1461 AND fea_ledger.version_id = l_entity_ledger_version
1462 AND flt.ledger_id = fea_ledger.dim_attribute_numeric_member
1463 AND flt.language = v_languages.language_code
1464 AND fea_srcsys.entity_id = gdsd.entity_id
1465 AND fea_srcsys.attribute_id = l_entity_srcsys_attr
1466 AND fea_srcsys.version_id = l_entity_srcsys_version
1467 AND foct.object_id (+) = gdsd.balances_rule_id
1468 AND foct.language (+) = v_languages.language_code
1469 AND fla_comp.ledger_id = fea_ledger.dim_attribute_numeric_member
1470 AND fla_comp.attribute_id = l_ldg_curr_attr
1471 AND fla_comp.version_id = l_ldg_curr_version
1472 AND fla_gvsc.ledger_id = fea_ledger.dim_attribute_numeric_member
1473 AND fla_gvsc.attribute_id = l_ledger_vs_combo_attr
1474 AND fla_gvsc.version_id = l_ledger_vs_combo_version
1475 AND ROWNUM < 2;
1476
1477 IF (l_ds_header_data.source_system_code = 10) THEN
1478 SELECT actual_output_dataset_code
1479 INTO l_dataset_code
1480 FROM fem_object_definition_b fodb,
1481 fem_cal_periods_attr fcpa_enddate,
1482 fem_dim_attr_versions_b fdavb_enddate,
1483 fem_intg_bal_rule_defs fibrd
1484 WHERE fodb.object_id = l_ds_header_data.balances_rule_id
1485 AND fcpa_enddate.date_assign_value BETWEEN fodb.effective_start_date AND fodb.effective_end_date
1486 AND fcpa_enddate.attribute_id = gcs_utility_pkg.get_dimension_attribute('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1487 AND fcpa_enddate.attribute_id = fdavb_enddate.attribute_id
1488 AND fcpa_enddate.version_id = fdavb_enddate.version_id
1489 AND fdavb_enddate.default_version_flag = 'Y'
1490 AND fodb.object_definition_id = fibrd.bal_rule_obj_def_id
1491 AND ROWNUM < 2;
1492 END IF;
1493
1494 --fnd_file.put_line(fnd_file.log, 'start DS XML for Load Id : ' || p_load_id);
1495 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1496 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.begin', 'Start DS XML for Load Id : '||p_load_id);
1497 END IF;
1498
1499 -- Generate the header level XML
1500 generate_ds_header_xml( p_ds_header_data => l_ds_header_data,
1501 p_dataset_code => l_dataset_code,
1502 p_ds_header_xml => l_ds_header_xml );
1503
1504 IF l_ds_header_data.source_system_code <> 10 THEN
1505 -- Generate the dimension name XML
1506 generate_dimension_name_xml ( p_language_code => v_languages.language_code,
1507 p_fem_dim_y_n => 'Y',
1508 p_xml_file_type => 'DSLOAD',
1509 p_dimension_name_xml => l_dimension_name_xml );
1510
1511 append_value_set_query ( p_entity_id => l_ds_header_data.entity_id,
1512 p_value_set_clause => l_value_set_where_clause);
1513
1514 -- take the sql query from gcs_xml_utility_pkg.g_datasub_lines_select_stmnt
1515 -- add the clause for gel.load_id and lang.language_code
1516 l_xml_file_type := 'DSLOAD';
1517 l_ds_lines_sql:= gcs_xml_utility_pkg.g_datasub_lines_select_stmnt ||
1518 ' AND load_id = '|| p_load_id ||
1519 l_value_set_where_clause ||
1520 ' ORDER BY ' || gcs_xml_utility_pkg.g_ds_order_by_stmnt;
1521
1522 -- Generate new query context and set desired Row Set and Row Tag and
1523 -- generate XML data
1524 l_qryCtx := DBMS_XMLGEN.newContext(l_ds_lines_sql);
1525 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'Lines');
1526 DBMS_XMLGEN.setRowTag(l_qryCtx,'Row');
1527 DBMS_XMLGEN.setConvertSpecialChars(l_qryCtx,FALSE);
1528 l_ds_lines_xml := DBMS_XMLGEN.getXML(l_qryCtx);
1529
1530 -- Remove xml version header tag from XML data
1531 IF (l_ds_lines_xml IS NOT NULL) THEN
1532 prsr := xmlparser.newparser;
1533 xmlparser.parseclob (prsr, l_ds_lines_xml);
1534 l_ds_lines_xml := ' ';
1535 doc_in := xmlparser.getdocument (prsr);
1536 xmlparser.freeparser (prsr);
1537 retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'Lines' );
1538 l_node := xmldom.item(retval , 0);
1539 xmldom.writetoclob(l_node, l_ds_lines_xml);
1540 END IF;
1541
1542 l_ds_xml := '<DataSubRoot> '||new_line||
1543 l_ds_header_xml||l_dimension_name_xml|| l_ds_lines_xml||
1544 '</DataSubRoot> ';
1545
1546 OPEN c_xml_id(v_languages.language_code, p_load_id, l_xml_file_type);
1547 FETCH c_xml_id INTO file_exist;
1548
1549 IF c_xml_id%NOTFOUND THEN
1550 insert into gcs_xml_files
1551 ( xml_file_id,
1552 xml_file_type,
1553 language,
1554 xml_data,
1555 last_update_date,
1556 last_updated_by,
1557 creation_date,
1558 created_by,
1559 object_version_number)
1560 values ( p_load_id,
1561 l_xml_file_type,
1562 v_languages.language_code,
1563 '<?xml version="1.0"?>'||new_line||l_ds_xml,
1564 sysdate,
1565 0,
1566 sysdate,
1567 0,
1568 0 );
1569 CLOSE c_xml_id;
1570
1571 --fnd_file.put_line(fnd_file.log, 'Inserted DS LOAD XML for Load Id : ' || p_load_id);
1572 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1573 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.INSERT', 'Inserted DS LOAD XML for Load Id : '||p_load_id);
1574 END IF;
1575
1576 ELSE
1577 update gcs_xml_files
1578 set xml_data = '<?xml version="1.0"?>'||new_line||l_ds_xml,
1579 last_update_date = sysdate,
1580 last_updated_by = 0,
1581 object_version_number = object_version_number + 1
1582 where xml_file_id = p_load_id
1583 and xml_file_type = l_xml_file_type
1584 and language = v_languages.language_code;
1585
1586 CLOSE c_xml_id;
1587
1588 --fnd_file.put_line(fnd_file.log, 'Updated DS LOAD XML for Load Id : ' || p_load_id);
1589 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.UPDATE', 'Updated DS LOAD XML for Load Id : '||p_load_id);
1591 END IF;
1592
1593 END IF;
1594 COMMIT;
1595
1596 END IF;
1597 -- Generate the dimension name XML
1598 generate_dimension_name_xml ( p_language_code => v_languages.language_code,
1599 p_fem_dim_y_n => 'Y',
1600 p_xml_file_type => 'DSTB',
1601 p_dimension_name_xml => l_dimension_name_xml );
1602
1603 -- take the sql query from gcs_xml_utility_pkg.g_dstb_lines_select_stmnt
1604 l_xml_file_type := 'DSTB';
1605
1606 IF l_ds_header_data.source_system_code = 10 THEN
1607
1608 l_dstb_lines_sql:= gcs_xml_utility_pkg.g_ogl_dstb_lines_select_stmnt||
1609 l_ds_header_data.sub_global_vs_combo_id||
1610 gcs_xml_utility_pkg.g_ogl_dstb_lines_select_stmnt1||
1611 ' AND fea_balrule.ENTITY_ID = '||
1612 l_ds_header_data.entity_id ||
1613 ' AND gel.SOURCE_SYSTEM_CODE = '||
1614 l_ds_header_data.source_system_code ||
1615 ' AND fct.language(+) = '''||
1616 v_languages.language_code ||
1617 ''' AND gel.CAL_PERIOD_ID = '||
1618 l_ds_header_data.cal_period_id;
1619 ELSE
1620 l_dstb_lines_sql:= gcs_xml_utility_pkg.g_dstb_lines_select_stmnt||
1621 ' AND fem_entities_source_system.ENTITY_ID = '||
1622 l_ds_header_data.entity_id ||
1623 ' AND fct.language(+) = '''||
1624 v_languages.language_code ||
1625 ''' AND gel.CAL_PERIOD_ID = '||
1626 l_ds_header_data.cal_period_id;
1627 END IF;
1628
1629 IF (l_ds_header_data.balance_type_code = 'ACTUAL') THEN
1630
1631 l_dstb_lines_sql := l_dstb_lines_sql|| ' AND gel.FINANCIAL_ELEM_ID <> 140';
1632
1633 ELSE
1634
1635 l_dstb_lines_sql := l_dstb_lines_sql|| ' AND gel.FINANCIAL_ELEM_ID = 140';
1636
1637 END IF;
1638
1639 IF (l_ds_header_data.curr_type_code = 'BASE_CURRENCY'
1640 AND l_ds_header_data.source_system_code <> 10) THEN
1641
1642 l_dstb_lines_sql := l_dstb_lines_sql|| ' AND gel.CURRENCY_CODE = '||
1643 ''''||l_ds_header_data.currency_code||'''';
1644
1645 ELSIF (l_ds_header_data.currency_code = l_ds_header_data.entity_currency_code
1646 AND l_ds_header_data.source_system_code = 10) THEN
1647
1648 l_dstb_lines_sql := l_dstb_lines_sql||'AND gel.currency_type_code = ''ENTERED'' ';
1649
1650 ELSIF (l_ds_header_data.currency_code <> l_ds_header_data.entity_currency_code
1651 AND l_ds_header_data.source_system_code = 10) THEN
1652
1653 l_dstb_lines_sql := l_dstb_lines_sql|| 'AND gel.currency_type_code = ''TRANSLATED'' ';
1654
1655 END IF;
1656
1657 l_dstb_lines_sql := l_dstb_lines_sql || ' GROUP BY ' ||
1658 gcs_xml_utility_pkg.g_ds_order_by_stmnt ;
1659
1660 IF l_ds_header_data.source_system_code <> 10 THEN
1661
1662 l_dstb_lines_sql := l_dstb_lines_sql ||', fct.name';
1663 ELSE
1664
1665 l_dstb_lines_sql := l_dstb_lines_sql ||', '||
1666 gcs_xml_utility_pkg.g_group_by_stmnt;
1667
1668 l_rowseq_hash_sql := gcs_xml_utility_pkg.g_ogl_hash_select_stmnt;
1669
1670 -- Generate ccid hash keys table using dynamic query string created earlier
1671 EXECUTE IMMEDIATE l_rowseq_hash_sql
1672 BULK COLLECT INTO l_rowseq_hash_table
1673 USING l_dataset_code, l_ds_header_data.cal_period_id,
1674 l_ds_header_data.source_system_code, l_ds_header_data.ledger_id,
1675 l_ds_header_data.currency_code;
1676 END IF;
1677
1678 l_dstb_lines_sql := l_dstb_lines_sql || ' ORDER BY ' ||
1679 gcs_xml_utility_pkg.g_ds_order_by_stmnt;
1680
1681 IF (l_ds_header_data.source_system_code <> 10) THEN
1682 l_dstb_lines_sql := l_dstb_lines_sql ||', fct.name';
1683 END IF;
1684 -- Generate new query context and set desired Row Set and Row Tag and
1685 -- generate XML data
1686 l_qryCtx := DBMS_XMLGEN.newContext(l_dstb_lines_sql);
1687 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'Lines');
1688 DBMS_XMLGEN.setRowTag(l_qryCtx,'Row');
1689 DBMS_XMLGEN.setConvertSpecialChars(l_qryCtx,FALSE);
1690 l_dstb_lines_xml := DBMS_XMLGEN.getXML(l_qryCtx);
1691
1692 -- Remove xml version header tag from XML data
1693 IF (l_dstb_lines_xml IS NOT NULL) THEN
1694 prsr := xmlparser.newparser;
1695 xmlparser.parseclob (prsr, l_dstb_lines_xml);
1696 l_dstb_lines_xml := ' ';
1697 doc_in := xmlparser.getdocument (prsr);
1698 xmlparser.freeparser (prsr);
1699 retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'Lines' );
1700 l_node := xmldom.item(retval , 0);
1701 xmldom.writetoclob(l_node, l_dstb_lines_xml);
1702 -- Manipulate dstb xml and update creation row sequence node
1703 IF l_ds_header_data.source_system_code = 10 THEN
1704 IF (l_rowseq_hash_table.FIRST IS NOT NULL AND
1705 l_rowseq_hash_table.LAST IS NOT NULL) THEN
1706 FOR i IN l_rowseq_hash_table.FIRST..l_rowseq_hash_table.LAST
1707 LOOP
1708 l_dstb_lines_xml := replace(l_dstb_lines_xml, '<CREATION_ROW_SEQUENCE>-1111111111</CREATION_ROW_SEQUENCE>'|| new_line ||
1709 ' <HASHKEY>'||l_rowseq_hash_table(i).rowseq_hash_key||'</HASHKEY>',
1710 '<CREATION_ROW_SEQUENCE>'||l_rowseq_hash_table(i).creation_row_sequence||'</CREATION_ROW_SEQUENCE>' || new_line ||
1711 ' <HASHKEY>'||l_rowseq_hash_table(i).rowseq_hash_key||'</HASHKEY>');
1712 END LOOP;
1713 END IF;
1714 END IF;
1715 END IF;
1716 l_dstb_xml := '<DataSubRoot> '||new_line||
1717 l_ds_header_xml||l_dimension_name_xml|| l_dstb_lines_xml||
1718 '</DataSubRoot> ';
1719
1720 OPEN c_xml_id(v_languages.language_code, p_load_id, l_xml_file_type);
1721 FETCH c_xml_id INTO file_exist;
1722
1723 IF c_xml_id%NOTFOUND THEN
1724 insert into gcs_xml_files
1725 ( xml_file_id,
1726 xml_file_type,
1727 language,
1728 xml_data,
1729 last_update_date,
1730 last_updated_by,
1731 creation_date,
1732 created_by,
1733 object_version_number)
1734 values ( p_load_id,
1735 l_xml_file_type,
1736 v_languages.language_code,
1737 '<?xml version="1.0"?>'||new_line||l_dstb_xml,
1738 sysdate,
1739 0,
1740 sysdate,
1741 0,
1742 0 );
1743 CLOSE c_xml_id;
1744
1745 --fnd_file.put_line(fnd_file.log, 'Inserted DSTB XML for Load Id : ' || p_load_id);
1746 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1747 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.INSERT', 'Inserted DSTB XML for Load Id : '||p_load_id);
1748 END IF;
1749
1750 ELSE
1751 update gcs_xml_files
1752 set xml_data = '<?xml version="1.0"?>'||new_line||l_dstb_xml,
1753 last_update_date = sysdate,
1754 last_updated_by = 0,
1755 object_version_number = object_version_number + 1
1756 where xml_file_id = p_load_id
1757 and xml_file_type = l_xml_file_type
1758 and language = v_languages.language_code;
1759
1760 CLOSE c_xml_id;
1761 --fnd_file.put_line(fnd_file.log, 'Updated DSTB XML for Load Id : ' || p_load_id);
1762 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1763 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.UPDATE', 'Updated DSTB XML for Load Id : '||p_load_id);
1764 END IF;
1765
1766 END IF;
1767 COMMIT;
1768
1769 exception
1770 when others then
1771 begin
1772 DBMS_XMLGEN.closeContext(l_qryCtx);
1773 --fnd_file.put_line(fnd_file.log, 'Error DS XML for Load Id : ' || p_load_id||' - '|| SQLERRM);
1774 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1775 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.UPDATE', 'Error DS XML for Load Id : '||p_load_id||' - '||SQLERRM);
1776 END IF;
1777 end;
1778 END;
1779 END LOOP;
1780 END generate_ds_xml;
1781
1782
1783 PROCEDURE generate_ad_header_xml ( p_ad_header_data IN r_ad_header_data,
1784 p_ad_header_xml OUT NOCOPY CLOB )
1785
1786 IS
1787
1788 BEGIN
1789
1790 p_ad_header_xml:= ' <AD_HEADER>' || new_line ||
1791 ' <ENTRY_NAME><![CDATA[' || p_ad_header_data.entry_name || ']]></ENTRY_NAME>' || new_line ||
1792 ' <DESCRIPTION><![CDATA[' || p_ad_header_data.description || ']]></DESCRIPTION>' || new_line ||
1793 ' <CONSIDERATION><![CDATA[' || p_ad_header_data.total_consideration || ']]></CONSIDERATION>' || new_line ||
1794 ' <CATEGORY_NAME><![CDATA[' || p_ad_header_data.category_name || ']]></CATEGORY_NAME>' || new_line ||
1795 ' <TRANSACTION_DATE><![CDATA[' || p_ad_header_data.transaction_date || ']]></TRANSACTION_DATE>' || new_line ||
1796 ' <HIERARCHY_NAME><![CDATA[' || p_ad_header_data.hierarchy_name || ']]></HIERARCHY_NAME>' || new_line ||
1797 ' <CONS_ENTITY_NAME><![CDATA[' || p_ad_header_data.cons_entity_name || ']]></CONS_ENTITY_NAME>' || new_line ||
1798 ' <CHILD_ENTITY_NAME><![CDATA['|| p_ad_header_data.child_entity_name || ']]></CHILD_ENTITY_NAME>'|| new_line ||
1799 ' <CURRENCY_NAME><![CDATA[' || p_ad_header_data.currency_name || ']]></CURRENCY_NAME>' || new_line ||
1800 '</AD_HEADER>' || new_line;
1801 END generate_ad_header_xml;
1802
1803 PROCEDURE generate_ad_xml ( p_ad_transaction_id IN NUMBER )
1804 IS
1805
1806 l_ad_header_data r_ad_header_data;
1807 l_ad_header_xml CLOB;
1808 l_dimension_name_xml CLOB;
1809 l_ad_lines_xml CLOB;
1810 l_ad_xml CLOB;
1811 l_ad_lines_sql VARCHAR2(10000);
1812 l_qryCtx DBMS_XMLGEN.ctxHandle;
1813 file_exist NUMBER;
1814 doc_in xmldom.DOMDocument;
1815 retval xmldom.domnodelist;
1816 prsr xmlparser.parser;
1817 l_node xmldom.domnode;
1818 doc_in1 xmldom.DOMDocument;
1819 retval1 xmldom.domnodelist;
1820 prsr1 xmlparser.parser;
1821 l_node1 xmldom.domnode;
1822 p_xml_file_type VARCHAR2(10) := 'ADTB';
1823
1824 CURSOR c_languages
1825 IS
1826 SELECT language_code
1827 FROM fnd_languages
1828 WHERE installed_flag IN ('I','B');
1829
1830 CURSOR c_xml_id ( p_language_code VARCHAR2,
1831 p_ad_transaction_id NUMBER )
1832 IS
1833 SELECT 1
1834 FROM gcs_xml_files
1835 WHERE xml_file_id = p_ad_transaction_id
1836 AND xml_file_type = p_xml_file_type
1837 AND language = p_language_code;
1838
1839 BEGIN
1840
1841 FOR v_languages IN c_languages
1842 LOOP
1843 BEGIN
1844 -- Get the ad Header Info for the ad_transaction_id
1845 SELECT
1846 flv.meaning category_name,
1847 treatments.transaction_date,
1848 ght.hierarchy_name hierarchy_name,
1849 fet1.entity_name consolidation_entity_name,
1850 fet2.entity_name child_entity_name,
1851 treatments.total_consideration||' '||fct.name total_consideration,
1852 treatments.entry_name
1853 INTO
1854 l_ad_header_data.category_name,
1855 l_ad_header_data.transaction_date,
1856 l_ad_header_data.hierarchy_name,
1857 l_ad_header_data.cons_entity_name,
1858 l_ad_header_data.child_entity_name,
1859 l_ad_header_data.total_consideration,
1860 l_ad_header_data.entry_name
1861 FROM
1862 fnd_lookup_values flv,
1863 fem_entities_tl fet1,
1864 fem_entities_tl fet2,
1865 gcs_entity_cons_attrs geca,
1866 gcs_hierarchies_tl ght,
1867 fnd_currencies_tl fct,
1868 fnd_currencies fcb,
1869 (select gtt_to.treatment_id to_treatment_id,
1870 gtt_from.treatment_id from_treatment_id,
1871 gtt_to.treatment_name to_treatment,
1872 gtt_from.treatment_name from_treatment,
1873 fcr.status_code,
1874 gat.post_cons_relationship_id,
1875 gat.pre_cons_relationship_id,
1876 gat.intermediate_treatment_id,
1877 gat.transaction_type_code,
1878 gat.total_consideration,
1879 fcr.request_id,
1880 gat.ad_transaction_id,
1881 gat.transaction_date,
1882 nvl(gcr.parent_entity_id, gcr_pre.parent_entity_id) parent_entity_id,
1883 nvl(gcr.child_entity_id, gcr_pre.child_entity_id) child_entity_id,
1884 nvl(gcr.hierarchy_id, gcr_pre.hierarchy_id) hierarchy_id,
1885 NVL (gcr.ownership_percent, 0) to_percent,
1886 NVL (gcr_pre.ownership_percent, 0) from_percent,
1887 geh.entry_name entry_name
1888 from gcs_treatments_tl gtt_from,
1889 gcs_treatments_tl gtt_to,
1890 gcs_ad_transactions gat,
1891 gcs_entry_headers geh,
1892 fnd_concurrent_requests fcr,
1893 gcs_cons_relationships gcr,
1894 gcs_cons_relationships gcr_pre
1895 where
1896 gtt_from.LANGUAGE = v_languages.language_code AND
1897 gtt_to.LANGUAGE = v_languages.language_code AND
1898 gat.assoc_entry_id = geh.entry_id AND
1899 gat.request_id = fcr.request_id (+) AND
1900 gat.ad_transaction_id = p_ad_transaction_id AND
1901 gat.post_cons_relationship_id = gcr.cons_relationship_id (+) AND
1902 gat.pre_cons_relationship_id = gcr_pre.cons_relationship_id(+) AND
1903 nvl(gcr.treatment_id, gat.intermediate_treatment_id) = gtt_to.treatment_id AND
1904 nvl(gcr_pre.treatment_id, gat.intermediate_treatment_id) = gtt_from.treatment_id
1905 ) treatments,
1906 (select grb.to_treatment_id,
1907 grb.from_treatment_id,
1908 grb.rule_id, grt.rule_name,
1909 grb.transaction_type_code
1910 from gcs_elim_rules_tl grt,
1911 gcs_elim_rules_b grb
1912 where grt.LANGUAGE = v_languages.language_code AND
1913 grb.rule_id = grt.rule_id
1914 ) rules
1915 WHERE treatments.to_treatment_id = rules.to_treatment_id (+) AND
1916 treatments.from_treatment_id = rules.from_treatment_id (+) AND
1917 rules.transaction_type_code (+)= treatments.transaction_type_code AND
1918 fet1.entity_id = treatments.parent_entity_id AND
1919 fet1.LANGUAGE = v_languages.language_code AND
1920 fet2.entity_id = treatments.child_entity_id AND
1921 fet2.LANGUAGE = v_languages.language_code AND
1922 geca.entity_id = treatments.parent_entity_id AND
1923 geca.hierarchy_id = ght.hierarchy_id AND
1924 ght.language = v_languages.language_code AND
1925 geca.currency_code = fcb.currency_code AND
1926 treatments.hierarchy_id = ght.hierarchy_id AND
1927 treatments.transaction_type_code = flv.lookup_code AND
1928 flv.lookup_type = 'TRANSACTION_TYPE_CODE' AND
1929 flv.LANGUAGE = v_languages.language_code AND
1930 flv.view_application_id = 266 AND
1931 fct.currency_code = fcb.currency_code AND
1932 fct.LANGUAGE = v_languages.language_code AND
1933 ROWNUM <= 1;
1934
1935 --fnd_file.put_line(fnd_file.log, 'Start AD XML for AD Transaction Id : ' || p_ad_transaction_id);
1936 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1937 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_AD_XML.begin', 'Start AD XML for AD Transaction Id : '||p_ad_transaction_id);
1938 END IF;
1939
1940 -- Generate the header level XML
1941 generate_ad_header_xml( p_ad_header_data => l_ad_header_data,
1942 p_ad_header_xml => l_ad_header_xml );
1943
1944 -- Generate the dimension name XML
1945 generate_dimension_name_xml ( p_language_code => v_languages.language_code,
1946 p_fem_dim_y_n => 'N',
1947 p_xml_file_type => 'ADTB',
1948 p_dimension_name_xml => l_dimension_name_xml );
1949
1950 -- take the sql query from gcs_xml_utility_pkg.g_datasub_lines_select_stmnt
1951 -- add the clause for gel.ad_transaction_id and lang.language_code
1952 l_ad_lines_sql:= gcs_xml_utility_pkg.g_ad_lines_select_stmnt ||
1953 ' AND ad_transaction_id = ' ||
1954 p_ad_transaction_id ||
1955 ' ORDER BY TRIAL_BALANCE_SEQ, '||
1956 gcs_xml_utility_pkg.g_order_by_stmnt;
1957
1958 -- Generate new query context and set desired Row Set and Row Tag
1959 -- and generate XML data
1960 l_qryCtx := DBMS_XMLGEN.newContext(l_ad_lines_sql);
1961 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'AD_LINES');
1962 DBMS_XMLGEN.setRowTag(l_qryCtx,'AD_LINES_ROW');
1963 DBMS_XMLGEN.setConvertSpecialChars(l_qryCtx,FALSE);
1964 l_ad_lines_xml := DBMS_XMLGEN.getXML(l_qryCtx);
1965
1966 -- Remove xml version header tag from XML data
1967 IF (l_ad_lines_xml IS NOT NULL) THEN
1968 prsr := xmlparser.newparser;
1969 xmlparser.parseclob (prsr, l_ad_lines_xml);
1970 doc_in := xmlparser.getdocument (prsr);
1971 xmlparser.freeparser (prsr);
1972 retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'AD_LINES' );
1973 l_node := xmldom.item(retval , 0);
1974 xmldom.writetoclob(l_node, l_ad_lines_xml);
1975 END IF;
1976 l_ad_xml := '<AD_DATA> '||new_line||
1977 l_ad_header_xml|| l_dimension_name_xml|| l_ad_lines_xml||
1978 '</AD_DATA> ';
1979
1980 OPEN c_xml_id(v_languages.language_code, p_ad_transaction_id);
1981 FETCH c_xml_id INTO file_exist;
1982
1983 IF c_xml_id%NOTFOUND THEN
1984 insert into gcs_xml_files
1985 (xml_file_id,
1986 xml_file_type,
1987 language,
1988 xml_data,
1989 last_update_date,
1990 last_updated_by,
1991 creation_date,
1992 created_by,
1993 object_version_number)
1994 values (p_ad_transaction_id,
1995 p_xml_file_type,
1996 v_languages.language_code,
1997 '<?xml version="1.0"?>'||new_line||l_ad_xml,
1998 sysdate,
1999 0,
2000 sysdate,
2001 0,
2002 0);
2003 CLOSE c_xml_id;
2004
2005 --fnd_file.put_line(fnd_file.log, 'Inserted AD XML for AD Transaction Id : ' || p_ad_transaction_id);
2006 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2007 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_AD_XML.INSERT', 'Inserted AD XML for AD Transaction Id : '||p_ad_transaction_id);
2008 END IF;
2009
2010 ELSE
2011 update gcs_xml_files
2012 set xml_data = '<?xml version="1.0"?>'||new_line||l_ad_xml,
2013 last_update_date = sysdate,
2014 last_updated_by = 0,
2015 object_version_number = object_version_number + 1
2016 where xml_file_id = p_ad_transaction_id
2017 and xml_file_type = p_xml_file_type
2018 and language = v_languages.language_code;
2019
2020 CLOSE c_xml_id;
2021
2022 --fnd_file.put_line(fnd_file.log, 'Updated AD XML for AD Transaction Id : ' || p_ad_transaction_id);
2023 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2024 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_AD_XML.UPDATE', 'Updated AD XML for AD Transaction Id : '||p_ad_transaction_id);
2025 END IF;
2026
2027 END IF;
2028 COMMIt;
2029
2030 exception
2031 when others then
2032 begin
2033 DBMS_XMLGEN.closeContext(l_qryCtx);
2034 --fnd_file.put_line(fnd_file.log, 'Error occurred : ' || SQLERRM);
2035 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2036 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_AD_XML.EXCEPTION', '<<Error>>'||SQLERRM);
2037 END IF;
2038 end;
2039 END;
2040 END LOOP;
2041 END generate_ad_xml;
2042 */
2043 PROCEDURE submit_entry_xml_gen ( x_errbuf OUT NOCOPY VARCHAR2,
2044 x_retcode OUT NOCOPY VARCHAR2,
2045 p_run_name IN VARCHAR2,
2046 p_cons_entity_id IN NUMBER,
2047 p_category_code IN VARCHAR2,
2048 p_child_entity_id IN NUMBER,
2049 p_run_detail_id IN NUMBER)
2050
2051 IS
2052
2053 l_errbuf VARCHAR2(200);
2054 l_retcode VARCHAR2(200);
2055
2056 TYPE entry_list IS TABLE OF NUMBER(15);
2057 l_entry_list entry_list;
2058 TYPE stat_entry_list IS TABLE OF NUMBER(15);
2059 l_stat_entry_list stat_entry_list;
2060 TYPE run_detail_list IS TABLE OF NUMBER(15);
2061 l_run_detail_list run_detail_list;
2062 TYPE request_error_list IS TABLE OF VARCHAR2(400);
2063 l_request_error_list request_error_list;
2064
2065 l_entry_id NUMBER(15);
2066 l_stat_entry_id NUMBER(15);
2067 l_request_error_code VARCHAR2(400);
2068
2069 CURSOR c_intracomp_info IS
2070 SELECT run_detail_id,
2071 entry_id,
2072 stat_entry_id,
2073 request_error_code
2074 FROM gcs_cons_eng_run_dtls
2075 WHERE run_name = p_run_name
2076 AND consolidation_entity_id = p_cons_entity_id
2077 AND child_entity_id = p_child_entity_id
2078 AND category_code = p_category_code;
2079
2080 CURSOR c_intercomp_info IS
2081 SELECT run_detail_id,
2082 entry_id,
2083 stat_entry_id,
2084 request_error_code
2085 FROM gcs_cons_eng_run_dtls
2086 WHERE run_name = p_run_name
2087 AND consolidation_entity_id = p_cons_entity_id
2088 AND child_entity_id IS NOT NULL
2089 AND category_code = p_category_code;
2090
2091 CURSOR c_operational_consol_rules IS
2092 SELECT run_detail_id,
2093 entry_id,
2094 stat_entry_id,
2095 request_error_code
2096 FROM gcs_cons_eng_run_dtls
2097 WHERE run_name = p_run_name
2098 AND consolidation_entity_id = p_cons_entity_id
2099 AND category_code = p_category_code
2100 AND child_entity_id = p_child_entity_id;
2101
2102 CURSOR c_consol_rules IS
2103 SELECT run_detail_id,
2104 entry_id,
2105 stat_entry_id,
2106 request_error_code
2107 FROM gcs_cons_eng_run_dtls
2108 WHERE run_name = p_run_name
2109 AND consolidation_entity_id = p_cons_entity_id
2110 AND child_entity_id IS NOT NULL
2111 AND category_code = p_category_code;
2112
2113 BEGIN
2114 --fnd_file.put_line(fnd_file.log, 'Beginning XML generation');
2115 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2116 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.begin', '<<Enter>>');
2117 END IF;
2118
2119 IF p_category_code = 'DATAPREPARATION' THEN
2120 BEGIN
2121 SELECT entry_id,
2122 stat_entry_id,
2123 request_error_code
2124 INTO l_entry_id,
2125 l_stat_entry_id,
2126 l_request_error_code
2127 FROM gcs_cons_eng_run_dtls
2128 WHERE run_detail_id = p_run_detail_id;
2129
2130 EXCEPTION
2131 WHEN OTHERS THEN
2132 BEGIN
2133 --fnd_file.put_line(fnd_file.log, 'Error occurred : ' || SQLERRM);
2134 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2135 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.DATAPREPARATION', 'Error:' ||SQLERRM);
2136 END IF;
2137 END;
2138 END;
2139 ELSIF p_category_code = 'TRANSLATION' OR p_category_code = 'AGGREGATION' THEN
2140 BEGIN
2141 SELECT entry_id,
2142 request_error_code
2143 INTO l_entry_id,
2144 l_request_error_code
2145 FROM gcs_cons_eng_run_dtls
2146 WHERE run_detail_id = p_run_detail_id;
2147 --fnd_file.put_line(fnd_file.log, 'Entry Id'|| l_entry_id);
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 BEGIN
2151 --fnd_file.put_line(fnd_file.log, 'Error occurred : ' || SQLERRM);
2152 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2153 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.'||p_category_code, 'Error:' ||SQLERRM);
2154 END IF;
2155 END;
2156 END;
2157 ELSIF p_category_code = 'INTRACOMPANY' THEN
2158 OPEN c_intracomp_info;
2159 FETCH c_intracomp_info BULK COLLECT INTO l_run_detail_list, l_entry_list, l_stat_entry_list, l_request_error_list;
2160 CLOSE c_intracomp_info;
2161 ELSIF p_category_code = 'INTERCOMPANY' THEN
2162 OPEN c_intercomp_info;
2163 FETCH c_intercomp_info BULK COLLECT INTO l_run_detail_list, l_entry_list, l_stat_entry_list, l_request_error_list;
2164 CLOSE c_intercomp_info;
2165 ELSIF p_child_entity_id IS NOT NULL THEN
2166 OPEN c_operational_consol_rules;
2167 FETCH c_operational_consol_rules BULK COLLECT INTO l_run_detail_list, l_entry_list, l_stat_entry_list, l_request_error_list;
2168 CLOSE c_operational_consol_rules;
2169 ELSE
2170 OPEN c_consol_rules;
2171 FETCH c_consol_rules BULK COLLECT INTO l_run_detail_list, l_entry_list, l_stat_entry_list, l_request_error_list;
2172 CLOSE c_consol_rules;
2173 END IF;
2174
2175 IF ( p_category_code = 'DATAPREPARATION') THEN
2176 /*IF l_entry_id IS NOT NULL THEN
2177 generate_entry_xml( p_entry_id => l_entry_id,
2178 p_category_code => p_category_code,
2179 p_cons_rule_flag => 'N' );
2180 --fnd_file.put_line(fnd_file.log, 'generate_entry_xml Entry_id ='|| l_entry_id || ' category= ' ||p_category_code);
2181 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2182 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.'||p_category_code,
2183 'generate_entry_xml Entry_id ='|| l_entry_id || ' category= ' ||p_category_code);
2184 END IF;
2185 END IF;
2186 IF l_stat_entry_id IS NOT NULL THEN
2187 generate_entry_xml( p_entry_id => l_stat_entry_id,
2188 p_category_code => p_category_code,
2189 p_cons_rule_flag => 'N' );
2190 --fnd_file.put_line(fnd_file.log, 'generate_entry_xml Entry_id ='|| l_stat_entry_id || ' category= ' ||p_category_code);
2191 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2192 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.'||p_category_code,
2193 'generate_entry_xml Entry_id ='|| l_stat_entry_id || ' category= ' ||p_category_code);
2194 END IF;
2195 END IF; */
2196 IF (l_request_error_code NOT IN ('COMPLETED', 'N/A')) THEN
2197 gcs_wf_ntf_pkg.raise_status_notification(
2198 p_cons_detail_id => p_run_detail_id);
2199 END IF;
2200 /*ELSIF (p_category_code = 'AGGREGATION') THEN
2201 IF l_entry_id IS NOT NULL THEN
2202 generate_entry_xml( p_entry_id => l_entry_id,
2203 p_category_code => p_category_code,
2204 p_cons_rule_flag => 'N' );
2205 --fnd_file.put_line(fnd_file.log, 'generate_entry_xml Entry_id ='|| l_entry_id || ' category= ' ||p_category_code);
2206 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2207 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.'||p_category_code,
2208 'generate_entry_xml Entry_id ='|| l_entry_id || ' category= ' ||p_category_code);
2209 END IF;
2210 END IF;*/
2211 ELSIF (p_category_code = 'TRANSLATION') THEN
2212 /*IF l_entry_id IS NOT NULL THEN
2213 generate_entry_xml( p_entry_id => l_entry_id,
2214 p_category_code => p_category_code,
2215 p_cons_rule_flag => 'N' );
2216 --fnd_file.put_line(fnd_file.log, 'generate_entry_xml Entry_id ='|| l_entry_id || ' category= ' ||p_category_code);
2217 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2218 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.'||p_category_code,
2219 'generate_entry_xml Entry_id ='|| l_entry_id || ' category= ' ||p_category_code);
2220 END IF;
2221 END IF;*/
2222 IF (l_request_error_code NOT IN ('COMPLETED', 'N/A')) THEN
2223 gcs_wf_ntf_pkg.raise_status_notification(
2224 p_cons_detail_id => p_run_detail_id);
2225 END IF;
2226 ELSE
2227 IF (l_entry_list.FIRST IS NOT NULL AND l_entry_list.LAST IS NOT NULL) THEN
2228 /*FOR l_index IN l_entry_list.FIRST..l_entry_list.LAST LOOP
2229 IF l_entry_list(l_index) IS NOT NULL THEN
2230 generate_entry_xml( p_entry_id => l_entry_list(l_index),
2231 p_category_code => p_category_code,
2232 p_cons_rule_flag => 'N' );
2233 END IF;
2234 END LOOP;
2235 FOR l_index IN l_stat_entry_list.FIRST..l_stat_entry_list.LAST LOOP
2236 IF l_stat_entry_list(l_index) IS NOT NULL THEN
2237 generate_entry_xml( p_entry_id => l_stat_entry_list(l_index),
2238 p_category_code => p_category_code,
2239 p_cons_rule_flag => 'N' );
2240 END IF;
2241 END LOOP;*/
2242 FOR l_index IN l_entry_list.FIRST..l_entry_list.LAST LOOP
2243 IF (l_request_error_list(l_index) NOT IN ('COMPLETED', 'N/A')) THEN
2244 gcs_wf_ntf_pkg.raise_status_notification(
2245 p_cons_detail_id => l_run_detail_list(l_index));
2246 END IF;
2247 END LOOP;
2248 END IF;
2249 END IF;
2250
2251 --fnd_file.put_line(fnd_file.log, 'Completed XML generation');
2252 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2253 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.end', '<<Exit>>');
2254 END IF;
2255
2256 EXCEPTION
2257 WHEN OTHERS THEN
2258 BEGIN
2259 --fnd_file.put_line(fnd_file.log, 'Error occurred : ' || SQLERRM);
2260 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2261 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_ENTRY_XML_GEN.EXCEPTION', '<<Error>>'||SQLERRM);
2262 END IF;
2263 END;
2264 END submit_entry_xml_gen;
2265
2266 END GCS_XML_GEN_PKG;