[Home] [Help]
PACKAGE BODY: APPS.GCS_XML_DYNAMIC_PKG
Source
1 PACKAGE BODY GCS_XML_DYNAMIC_PKG AS
2 /* $Header: gcsxmldynb.pls 120.24 2006/08/17 16:44:34 hakumar noship $ */
3 --Global Variables
4 g_api VARCHAR2(50) := 'gcs.plsql.GCS_XML_DYNAMIC_PKG';
5 g_nl VARCHAR2(1) := '''';
6 PROCEDURE add_order_clause_to_list(p_dimension_required IN VARCHAR2,
7 p_display_col_name IN VARCHAR2,
8 p_table_alias IN VARCHAR2,
9 p_rownum IN OUT NOCOPY NUMBER,
10 p_xml_file_type IN VARCHAR2) IS
11 BEGIN
12 IF (p_dimension_required = 'Y') THEN
13 IF (p_xml_file_type = 'RULES') THEN
14 ad_ddl.build_statement(' '' ' || p_table_alias || '1.' ||
15 p_display_col_name || ', ''|| g_nl||',
16 p_rownum);
17 p_rownum := p_rownum + 1;
18 ELSE
19 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
20 p_display_col_name || ', ''|| g_nl||',
21 p_rownum);
22 p_rownum := p_rownum + 1;
23 END IF;
24 END IF;
25 END add_order_clause_to_list;
26 PROCEDURE build_order_clause_list(p_rownum IN OUT NOCOPY NUMBER,
27 p_xml_file_type IN VARCHAR2) IS
28 l_dim_info gcs_utility_pkg.t_hash_gcs_dimension_info := gcs_utility_pkg.g_gcs_dimension_info;
29 BEGIN
30 -- Santosh 5234796
31 IF (p_xml_file_type = 'DSLOAD') THEN
32 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('LINE_ITEM_ID'),
33 'LINE_ITEM_DISPLAY_CODE',
34 'gbit',
35 p_rownum,
36 p_xml_file_type);
37 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
38 'CHANNEL_DISPLAY_CODE',
39 'gbit',
40 p_rownum,
41 p_xml_file_type);
42 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
43 'CUSTOMER_DISPLAY_CODE',
44 'gbit',
45 p_rownum,
46 p_xml_file_type);
47 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'),
48 'FINANCIAL_ELEM_DISPLAY_CODE',
49 'gbit',
50 p_rownum,
51 p_xml_file_type);
52 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
53 'NATURAL_ACCOUNT_DISPLAY_CODE',
54 'gbit',
55 p_rownum,
56 p_xml_file_type);
57 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
58 'PRODUCT_DISPLAY_CODE',
59 'gbit',
60 p_rownum,
61 p_xml_file_type);
62 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
63 'PROJECT_DISPLAY_CODE',
64 'gbit',
65 p_rownum,
66 p_xml_file_type);
67 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
68 'TASK_DISPLAY_CODE',
69 'gbit',
70 p_rownum,
71 p_xml_file_type);
72 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
73 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
74 'USER_DIM10_DISPLAY_CODE',
75 'gbit',
76 p_rownum,
77 p_xml_file_type);
78 END IF;
79 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
80 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
81 'USER_DIM1_DISPLAY_CODE',
82 'gbit',
83 p_rownum,
84 p_xml_file_type);
85 END IF;
86 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
87 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
88 'USER_DIM2_DISPLAY_CODE',
89 'gbit',
90 p_rownum,
91 p_xml_file_type);
92 END IF;
93 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
94 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
95 'USER_DIM3_DISPLAY_CODE',
96 'gbit',
97 p_rownum,
98 p_xml_file_type);
99 END IF;
100 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
101 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
102 'USER_DIM4_DISPLAY_CODE',
103 'gbit',
104 p_rownum,
105 p_xml_file_type);
106 END IF;
107 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
108 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
109 'USER_DIM5_DISPLAY_CODE',
110 'gbit',
111 p_rownum,
112 p_xml_file_type);
113 END IF;
114 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
115 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
116 'USER_DIM6_DISPLAY_CODE',
117 'gbit',
118 p_rownum,
119 p_xml_file_type);
120 END IF;
121 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
122 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
123 'USER_DIM7_DISPLAY_CODE',
124 'gbit',
125 p_rownum,
126 p_xml_file_type);
127 END IF;
128 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
129 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
130 'USER_DIM8_DISPLAY_CODE',
131 'gbit',
132 p_rownum,
133 p_xml_file_type);
134 END IF;
135 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
136 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
137 'USER_DIM9_DISPLAY_CODE',
138 'gbit',
139 p_rownum,
140 p_xml_file_type);
141 END IF;
142 ELSIF (p_xml_file_type = 'DS') THEN
143 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('LINE_ITEM_ID'),
144 'line_item_name',
145 'flib',
146 p_rownum,
147 p_xml_file_type);
148 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
149 'channel_name',
150 'fchb',
151 p_rownum,
152 p_xml_file_type);
153 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
154 'customer_name',
155 'fcb',
156 p_rownum,
157 p_xml_file_type);
158 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'),
159 'financial_elem_name',
160 'ffeb',
161 p_rownum,
162 p_xml_file_type);
163 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
164 'natural_account_name',
165 'fnab',
166 p_rownum,
167 p_xml_file_type);
168 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
169 'product_name',
170 'fpb',
171 p_rownum,
172 p_xml_file_type);
173 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
174 'project_name',
175 'fpjb',
176 p_rownum,
177 p_xml_file_type);
178 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
179 'task_name',
180 'ftb',
181 p_rownum,
182 p_xml_file_type);
183 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
184 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
185 'user_dim10_name',
186 'fud10',
187 p_rownum,
188 p_xml_file_type);
189 END IF;
190 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
191 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
192 'user_dim1_name',
193 'fud1',
194 p_rownum,
195 p_xml_file_type);
196 END IF;
197 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
198 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
199 'user_dim2_name',
200 'fud2',
201 p_rownum,
202 p_xml_file_type);
203 END IF;
204 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
205 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
206 'user_dim3_name',
207 'fud3',
208 p_rownum,
209 p_xml_file_type);
210 END IF;
211 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
212 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
213 'user_dim4_name',
214 'fud4',
215 p_rownum,
216 p_xml_file_type);
217 END IF;
218 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
219 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
220 'user_dim5_name',
221 'fud5',
222 p_rownum,
223 p_xml_file_type);
224 END IF;
225 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
226 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
227 'user_dim6_name',
228 'fud6',
229 p_rownum,
230 p_xml_file_type);
231 END IF;
232 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
233 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
234 'user_dim7_name',
235 'fud7',
236 p_rownum,
237 p_xml_file_type);
238 END IF;
239 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
240 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
241 'user_dim8_name',
242 'fud8',
243 p_rownum,
244 p_xml_file_type);
245 END IF;
246 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
247 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
248 'user_dim9_name',
249 'fud9',
250 p_rownum,
251 p_xml_file_type);
252 END IF;
253 ELSIF (p_xml_file_type = 'OGL') THEN
254 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
255 'channel_id',
256 'fb',
257 p_rownum,
258 p_xml_file_type);
259 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
260 'customer_id',
261 'fb',
262 p_rownum,
263 p_xml_file_type);
264 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'),
265 'financial_elem_id',
266 'fb',
267 p_rownum,
268 p_xml_file_type);
269 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
270 'natural_account_id',
271 'fb',
272 p_rownum,
273 p_xml_file_type);
274 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
275 'product_id',
276 'fb',
277 p_rownum,
278 p_xml_file_type);
279 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
280 'project_id',
281 'fb',
282 p_rownum,
283 p_xml_file_type);
284 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
285 'task_id',
286 'fb',
287 p_rownum,
288 p_xml_file_type);
289 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
290 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
291 'user_dim10_id',
292 'fb',
293 p_rownum,
294 p_xml_file_type);
295 END IF;
296 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
297 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
298 'user_dim1_id',
299 'fb',
300 p_rownum,
301 p_xml_file_type);
302 END IF;
303 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
304 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
305 'user_dim2_id',
306 'fb',
307 p_rownum,
308 p_xml_file_type);
309 END IF;
310 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
311 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
312 'user_dim3_id',
313 'fb',
314 p_rownum,
315 p_xml_file_type);
316 END IF;
317 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
318 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
319 'user_dim4_id',
320 'fb',
321 p_rownum,
322 p_xml_file_type);
323 END IF;
324 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
325 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
326 'user_dim5_id',
327 'fb',
328 p_rownum,
329 p_xml_file_type);
330 END IF;
331 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
332 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
333 'user_dim6_id',
334 'fb',
335 p_rownum,
336 p_xml_file_type);
337 END IF;
338 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
339 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
340 'user_dim7_id',
341 'fb',
342 p_rownum,
343 p_xml_file_type);
344 END IF;
345 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
346 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
347 'user_dim8_id',
348 'fb',
349 p_rownum,
350 p_xml_file_type);
351 END IF;
352 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
353 add_order_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
354 'user_dim9_id',
355 'fb',
356 p_rownum,
357 p_xml_file_type);
358 END IF;
359 ELSE
360 -- FOR 'NOT_RULES'
361 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('CHANNEL_ID'),
362 'channel_name',
363 'fchb',
364 p_rownum,
365 p_xml_file_type);
366 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('CUSTOMER_ID'),
367 'customer_name',
368 'fcb',
369 p_rownum,
370 p_xml_file_type);
371 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID'),
372 'financial_elem_name',
373 'ffeb',
374 p_rownum,
375 p_xml_file_type);
376 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID'),
377 'natural_account_name',
378 'fnab',
379 p_rownum,
380 p_xml_file_type);
381 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('PRODUCT_ID'),
382 'product_name',
383 'fpb',
384 p_rownum,
385 p_xml_file_type);
386 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('PROJECT_ID'),
387 'project_name',
388 'fpjb',
389 p_rownum,
390 p_xml_file_type);
391 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('TASK_ID'),
392 'task_name',
393 'ftb',
394 p_rownum,
395 p_xml_file_type);
396 IF (gcs_utility_pkg.get_dimension_required('USER_DIM10_ID') = 'Y') THEN
397 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM10_ID'),
398 'user_dim10_name',
399 'fud10',
400 p_rownum,
401 p_xml_file_type);
402 END IF;
403 IF (gcs_utility_pkg.get_dimension_required('USER_DIM1_ID') = 'Y') THEN
404 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM1_ID'),
405 'user_dim1_name',
406 'fud1',
407 p_rownum,
408 p_xml_file_type);
409 END IF;
410 IF (gcs_utility_pkg.get_dimension_required('USER_DIM2_ID') = 'Y') THEN
411 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM2_ID'),
412 'user_dim2_name',
413 'fud2',
414 p_rownum,
415 p_xml_file_type);
416 END IF;
417 IF (gcs_utility_pkg.get_dimension_required('USER_DIM3_ID') = 'Y') THEN
418 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM3_ID'),
419 'user_dim3_name',
420 'fud3',
421 p_rownum,
422 p_xml_file_type);
423 END IF;
424 IF (gcs_utility_pkg.get_dimension_required('USER_DIM4_ID') = 'Y') THEN
425 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM4_ID'),
426 'user_dim4_name',
427 'fud4',
428 p_rownum,
429 p_xml_file_type);
430 END IF;
431 IF (gcs_utility_pkg.get_dimension_required('USER_DIM5_ID') = 'Y') THEN
432 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM5_ID'),
433 'user_dim5_name',
434 'fud5',
435 p_rownum,
436 p_xml_file_type);
437 END IF;
438 IF (gcs_utility_pkg.get_dimension_required('USER_DIM6_ID') = 'Y') THEN
439 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM6_ID'),
440 'user_dim6_name',
441 'fud6',
442 p_rownum,
443 p_xml_file_type);
444 END IF;
445 IF (gcs_utility_pkg.get_dimension_required('USER_DIM7_ID') = 'Y') THEN
446 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM7_ID'),
447 'user_dim7_name',
448 'fud7',
449 p_rownum,
450 p_xml_file_type);
451 END IF;
452 IF (gcs_utility_pkg.get_dimension_required('USER_DIM8_ID') = 'Y') THEN
453 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM8_ID'),
454 'user_dim8_name',
455 'fud8',
456 p_rownum,
457 p_xml_file_type);
458 END IF;
459 IF (gcs_utility_pkg.get_dimension_required('USER_DIM9_ID') = 'Y') THEN
460 add_order_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM9_ID'),
461 'user_dim9_name',
462 'fud9',
463 p_rownum,
464 p_xml_file_type);
465 END IF;
466 END IF;
467 END build_order_clause_list;
468 PROCEDURE add_select_clause_dsload_list(p_dimension_required IN VARCHAR2,
469 p_display_col_name IN VARCHAR2,
470 p_display_col_alias IN VARCHAR2,
471 p_table_alias IN VARCHAR2,
472 p_rownum IN OUT NOCOPY NUMBER,
473 p_xml_file_type IN VARCHAR2) IS
474 BEGIN
475 IF (p_dimension_required = 'Y') THEN
476 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
477 p_display_col_name || ' ' ||
478 p_display_col_alias || ' , ''|| g_nl||',
479 p_rownum);
480 p_rownum := p_rownum + 1;
481 END IF;
482 END add_select_clause_dsload_list;
483 PROCEDURE add_select_clause_to_list(p_dimension_required IN VARCHAR2,
484 p_display_col_name IN VARCHAR2,
485 p_table_alias IN VARCHAR2,
486 p_rownum IN OUT NOCOPY NUMBER,
487 p_xml_file_type IN VARCHAR2) IS
488 DecodeStatement varchar2(1000);
489 BEGIN
490 IF (p_dimension_required = 'Y') THEN
491 IF (p_xml_file_type = 'ENTRY') THEN
492 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
493 p_display_col_name || ' , ''|| g_nl||',
494 p_rownum);
495 p_rownum := p_rownum + 1;
496 ELSIF (p_xml_file_type = 'DSTB') THEN
497 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
498 p_display_col_name || ' , ''|| g_nl||',
499 p_rownum);
500 p_rownum := p_rownum + 1;
501 -- Santosh 5234796
502 ELSIF (p_xml_file_type = 'DSLOAD') THEN
503 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
504 p_display_col_name || ' , ''|| g_nl||',
505 p_rownum);
506 p_rownum := p_rownum + 1;
507 ELSIF (p_xml_file_type = 'RULES') THEN
508 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
509 p_display_col_name || ' , ''|| g_nl||',
510 p_rownum);
511 p_rownum := p_rownum + 1;
512 ad_ddl.build_statement(' '' ' || p_table_alias || '1.' ||
513 p_display_col_name || ', ''|| g_nl||',
514 p_rownum);
515 p_rownum := p_rownum + 1;
516 ELSIF (p_xml_file_type = 'DSTBID') THEN
517 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
518 p_display_col_name || ', ''|| g_nl||',
519 p_rownum);
520 p_rownum := p_rownum + 1;
521 ELSIF (p_xml_file_type = 'CCIDHASH') THEN
522 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
523 p_display_col_name || '||' || ' ''''.'''' ' ||
524 '|| ''|| g_nl||',
525 p_rownum);
526 p_rownum := p_rownum + 1;
527 ELSIF (p_xml_file_type = 'ENTRY_XML' or p_xml_file_type = 'DSTB_XML') THEN
528 ad_ddl.build_statement(' '' <element name="NAME" value="' ||
529 p_display_col_name || '"/> ''|| g_nl||',
530 p_rownum);
531 p_rownum := p_rownum + 1;
532 --fix 5351083
533 /*ELSIF (p_xml_file_type = 'DSTB_XML_ID') THEN
534 ad_ddl.build_statement(' '' <element name="' ||
535 replace(p_display_col_name, '_name', '_id') ||
536 '" value="' ||
537 replace(p_display_col_name, '_name', '_id') ||
538 '" /> ''|| g_nl||',
539 p_rownum);
540 p_rownum := p_rownum + 1;*/
541 ELSIF (p_xml_file_type = 'VSMPID') THEN
542 ad_ddl.build_statement(' '' <group name="header" source="' ||
543 p_display_col_name || '"> ''||g_nl|| ',
544 p_rownum);
545 p_rownum := p_rownum + 1;
546 ad_ddl.build_statement(' '' <element name="dimensionname" value="dimension_name"/> ''|| g_nl||',
547 p_rownum);
548 p_rownum := p_rownum + 1;
549 ad_ddl.build_statement(' '' <element name="valuesetname" value="value_set_name"/> ''|| g_nl||',
550 p_rownum);
551 p_rownum := p_rownum + 1;
552 ad_ddl.build_statement(' '' <group name="details" source="' ||
553 p_display_col_name || '"> ''|| g_nl||',
554 p_rownum);
555 p_rownum := p_rownum + 1;
556 ad_ddl.build_statement(' '' <element name="name" value="dim_member_name"/> ''|| g_nl||',
557 p_rownum);
558 p_rownum := p_rownum + 1;
559 ad_ddl.build_statement(' '' <element name="description" value="description" /> ''|| g_nl||',
560 p_rownum);
561 p_rownum := p_rownum + 1;
562 ad_ddl.build_statement(' '' </group> ''|| g_nl||', p_rownum);
563 p_rownum := p_rownum + 1;
564 ad_ddl.build_statement(' '' </group> ''||g_nl||', p_rownum);
565 p_rownum := p_rownum + 1;
566 -- BUG 5147886
567 ELSIF (p_xml_file_type = 'NONPOSTED_SELECT' OR
568 p_xml_file_type = 'NONPOSTED_GROUP') THEN
569 DecodeStatement := 'decode (EXTENDED_ACCOUNT_TYPE,' ||
570 '''''REVENUE'''',455,' ||
571 '''''EXPENSE'''',457,100) ';
572
573 IF (p_display_col_name = 'financial_elem_name') THEN
574 IF (p_xml_file_type = 'NONPOSTED_GROUP') THEN
575 ad_ddl.build_statement(' '' ' || DecodeStatement || ',' ||
576 ' ''||g_nl||',
577 p_rownum);
578 p_rownum := p_rownum + 1;
579 ELSE
580 ad_ddl.build_statement(' '' ' || DecodeStatement ||
581 'financial_elem_id,' || ' ''||g_nl||',
582 p_rownum);
583 p_rownum := p_rownum + 1;
584 end IF;
585 ELSE
586 ad_ddl.build_statement(' '' ' || 'fiocm.' ||
587 replace(p_display_col_name,
588 '_name',
589 '_id') || ' , ''|| g_nl||',
590 p_rownum);
591 p_rownum := p_rownum + 1;
592 END IF;
593 ELSE
594 ad_ddl.build_statement(' '' ' || p_table_alias || '.' ||
595 p_display_col_name ||
596 ' dimension_data, ''|| g_nl||',
597 p_rownum);
598 p_rownum := p_rownum + 1;
599 END IF;
600 END IF;
601 END add_select_clause_to_list;
602 PROCEDURE add_clause_to_list(p_dimension_required IN VARCHAR2,
603 p_column_id IN VARCHAR2,
604 p_table_alias IN VARCHAR2,
605 p_rownum IN OUT NOCOPY NUMBER,
606 p_xml_file_type IN VARCHAR2) IS
607 BEGIN
608 IF (p_dimension_required = 'Y') THEN
609 IF (p_xml_file_type = 'ENTRY') THEN
610 ad_ddl.build_statement(' '' AND ' || p_table_alias || '.' ||
611 p_column_id || ' = gel.' || p_column_id ||
612 ' '' || g_nl|| ',
613 p_rownum);
614 p_rownum := p_rownum + 1;
615 ad_ddl.build_statement(' '' AND ' || p_table_alias ||
616 '.language = :pLanguageCode' ||
617 ''' || g_nl||',
618 p_rownum);
619 p_rownum := p_rownum + 1;
620 -- Santosh 5234796
621 /*ELSIF (p_xml_file_type = 'DSLOAD') THEN
622 ad_ddl.build_statement(' '' AND ' || p_table_alias || '.' || p_column_id || 'display_code = gbit.' || p_column_id || 'display_code '' || g_nl||', p_rownum); p_rownum := p_rownum+1;
623 ad_ddl.build_statement(' '' AND ' || p_table_alias || '.value_set_id = :p'||p_table_alias || 'ValueSetId'' || g_nl||', p_rownum); p_rownum := p_rownum+1;
624 ad_ddl.build_statement(' '' AND ' || p_table_alias || 't.' || p_column_id || 'id = ' || p_table_alias || '.' || p_column_id || 'id '' || g_nl||', p_rownum); p_rownum := p_rownum+1;
625 ad_ddl.build_statement(' '' AND ' || p_table_alias || 't.value_set_id = '||p_table_alias || '.value_set_id '' || g_nl||', p_rownum); p_rownum := p_rownum+1;
626 ad_ddl.build_statement(' '' AND ' || p_table_alias || 't.language = :pLanguageCode' || ''' || g_nl||', p_rownum); p_rownum := p_rownum+1;
627 */
628 ELSIF (p_xml_file_type <> 'RULES') THEN
629 ad_ddl.build_statement(' '' AND ' || p_table_alias || '.' ||
630 p_column_id || ' = fb.' || p_column_id ||
631 ' '' || g_nl||',
632 p_rownum);
633 p_rownum := p_rownum + 1;
634 ad_ddl.build_statement(' '' AND ' || p_table_alias ||
635 '.language = :pLanguageCode' ||
636 ''' || g_nl||',
637 p_rownum);
638 p_rownum := p_rownum + 1;
639 END IF;
640 IF (p_xml_file_type = 'RULES') THEN
641 ad_ddl.build_statement(' '' AND ' || p_table_alias || '.' ||
642 p_column_id || ' = gel.src_' || p_column_id ||
643 ' '' || g_nl||',
644 p_rownum);
645 p_rownum := p_rownum + 1;
646 ad_ddl.build_statement(' '' AND ' || p_table_alias || '1.' ||
647 p_column_id || ' = gel.tgt_' || p_column_id ||
648 ' '' || g_nl||',
649 p_rownum);
650 p_rownum := p_rownum + 1;
651 END IF;
652 END IF;
653 END add_clause_to_list;
654 PROCEDURE build_select_clause_list(p_rownum IN OUT NOCOPY NUMBER,
655 p_xml_file_type IN VARCHAR2) IS
656 l_dim_info gcs_utility_pkg.t_hash_gcs_dimension_info := gcs_utility_pkg.g_gcs_dimension_info;
657 BEGIN
658 --BUG 5147886
659 -- Santosh 5234796
660 IF (p_xml_file_type = 'DSLOAD') THEN
661 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
662 'CHANNEL_DISPLAY_CODE',
663 'channel_name',
664 'gbit',
665 p_rownum,
666 p_xml_file_type);
667 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
668 'CUSTOMER_DISPLAY_CODE',
669 'customer_name',
670 'gbit',
671 p_rownum,
672 p_xml_file_type);
673
674 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'),
675 'FINANCIAL_ELEM_DISPLAY_CODE',
676 'financial_elem_name',
677 'gbit',
678 p_rownum,
679 p_xml_file_type);
680
681 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
682 'NATURAL_ACCOUNT_DISPLAY_CODE',
683 'natural_account_name',
684 'gbit',
685 p_rownum,
686 p_xml_file_type);
687 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
688 'PRODUCT_DISPLAY_CODE',
689 'product_name',
690 'gbit',
691 p_rownum,
692 p_xml_file_type);
693 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
694 'PROJECT_DISPLAY_CODE',
695 'project_name',
696 'gbit',
697 p_rownum,
698 p_xml_file_type);
699 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
700 'TASK_DISPLAY_CODE',
701 'task_name',
702 'gbit',
703 p_rownum,
704 p_xml_file_type);
705 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
706 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
707 'USER_DIM1_DISPLAY_CODE',
708 'user_dim1_name',
709 'gbit',
710 p_rownum,
711 p_xml_file_type);
712 END IF;
713 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
714 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
715 'USER_DIM2_DISPLAY_CODE',
716 'user_dim2_name',
717 'gbit',
718 p_rownum,
719 p_xml_file_type);
720 END IF;
721 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
722 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
723 'USER_DIM3_DISPLAY_CODE',
724 'user_dim3_name',
725 'gbit',
726 p_rownum,
727 p_xml_file_type);
728 END IF;
729 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
730 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
731 'USER_DIM4_DISPLAY_CODE',
732 'user_dim4_name',
733 'gbit',
734 p_rownum,
735 p_xml_file_type);
736 END IF;
737 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
738 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
739 'USER_DIM5_DISPLAY_CODE',
740 'user_dim5_name',
741 'gbit',
742 p_rownum,
743 p_xml_file_type);
744 END IF;
745 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
746 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
747 'USER_DIM6_DISPLAY_CODE',
748 'user_dim6_name',
749 'gbit',
750 p_rownum,
751 p_xml_file_type);
752 END IF;
753 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
754 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
755 'USER_DIM7_DISPLAY_CODE',
756 'user_dim7_name',
757 'gbit',
758 p_rownum,
759 p_xml_file_type);
760 END IF;
761 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
762 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
763 'USER_DIM8_DISPLAY_CODE',
764 'user_dim8_name',
765 'gbit',
766 p_rownum,
767 p_xml_file_type);
768 END IF;
769 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
770 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
771 'USER_DIM9_DISPLAY_CODE',
772 'user_dim9_name',
773 'gbit',
774 p_rownum,
775 p_xml_file_type);
776 END IF;
777 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
778 add_select_clause_dsload_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
779 'USER_DIM10_DISPLAY_CODE',
780 'user_dim10_name',
781 'gbit',
782 p_rownum,
783 p_xml_file_type);
784 END IF;
785 ELSIF (p_xml_file_type = 'NONPOSTED_SELECT' OR
786 p_xml_file_type = 'NONPOSTED_GROUP' OR p_xml_file_type = 'DSTB' OR
787 p_xml_file_type = 'DSTB_XML' /*OR p_xml_file_type = 'DSTB_XML_ID'*/) THEN --fix 5351083
788 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
789 'channel_name',
790 'fchb',
791 p_rownum,
792 p_xml_file_type);
793 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
794 'customer_name',
795 'fcb',
796 p_rownum,
797 p_xml_file_type);
798
799 --BUG 5147886
800 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'),
801 'financial_elem_name',
802 'ffeb',
803 p_rownum,
804 p_xml_file_type);
805
806 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
807 'natural_account_name',
808 'fnab',
809 p_rownum,
810 p_xml_file_type);
811 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
812 'product_name',
813 'fpb',
814 p_rownum,
815 p_xml_file_type);
816 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
817 'project_name',
818 'fpjb',
819 p_rownum,
820 p_xml_file_type);
821 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
822 'task_name',
823 'ftb',
824 p_rownum,
825 p_xml_file_type);
826 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
827 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
828 'user_dim1_name',
829 'fud1',
830 p_rownum,
831 p_xml_file_type);
832 END IF;
833 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
834 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
835 'user_dim2_name',
836 'fud2',
837 p_rownum,
838 p_xml_file_type);
839 END IF;
840 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
841 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
842 'user_dim3_name',
843 'fud3',
844 p_rownum,
845 p_xml_file_type);
846 END IF;
847 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
848 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
849 'user_dim4_name',
850 'fud4',
851 p_rownum,
852 p_xml_file_type);
853 END IF;
854 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
855 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
856 'user_dim5_name',
857 'fud5',
858 p_rownum,
859 p_xml_file_type);
860 END IF;
861 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
862 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
863 'user_dim6_name',
864 'fud6',
865 p_rownum,
866 p_xml_file_type);
867 END IF;
868 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
869 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
870 'user_dim7_name',
871 'fud7',
872 p_rownum,
873 p_xml_file_type);
874 END IF;
875 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
876 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
877 'user_dim8_name',
878 'fud8',
879 p_rownum,
880 p_xml_file_type);
881 END IF;
882 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
883 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
884 'user_dim9_name',
885 'fud9',
886 p_rownum,
887 p_xml_file_type);
888 END IF;
889 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
890 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
891 'user_dim10_name',
892 'fud10',
893 p_rownum,
894 p_xml_file_type);
895 END IF;
896 ELSIF (p_xml_file_type = 'DSTBID') THEN
897 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('LINE_ITEM_ID'),
898 'line_item_id',
899 'fb',
900 p_rownum,
901 p_xml_file_type);
902 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
903 'channel_id',
904 'fb',
905 p_rownum,
906 p_xml_file_type);
907 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
908 'customer_id',
909 'fb',
910 p_rownum,
911 p_xml_file_type);
912
913 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'),
914 'financial_elem_id',
915 'fb',
916 p_rownum,
917 p_xml_file_type);
918
919 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
920 'natural_account_id',
921 'fb',
922 p_rownum,
923 p_xml_file_type);
924 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
925 'product_id',
926 'fb',
927 p_rownum,
928 p_xml_file_type);
929 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
930 'project_id',
931 'fb',
932 p_rownum,
933 p_xml_file_type);
934 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
935 'task_id',
936 'fb',
937 p_rownum,
938 p_xml_file_type);
939 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
940 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
941 'user_dim1_id',
942 'fb',
943 p_rownum,
944 p_xml_file_type);
945 END IF;
946 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
947 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
948 'user_dim2_id',
949 'fb',
950 p_rownum,
951 p_xml_file_type);
952 END IF;
953 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
954 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
955 'user_dim3_id',
956 'fb',
957 p_rownum,
958 p_xml_file_type);
959 END IF;
960 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
961 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
962 'user_dim4_id',
963 'fb',
964 p_rownum,
965 p_xml_file_type);
966 END IF;
967 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
968 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
969 'user_dim5_id',
970 'fb',
971 p_rownum,
972 p_xml_file_type);
973 END IF;
974 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
975 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
976 'user_dim6_id',
977 'fb',
978 p_rownum,
979 p_xml_file_type);
980 END IF;
981 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
982 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
983 'user_dim7_id',
984 'fb',
985 p_rownum,
986 p_xml_file_type);
987 END IF;
988 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
989 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
990 'user_dim8_id',
991 'fb',
992 p_rownum,
993 p_xml_file_type);
994 END IF;
995 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
996 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
997 'user_dim9_id',
998 'fb',
999 p_rownum,
1000 p_xml_file_type);
1001 END IF;
1002 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
1003 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
1004 'user_dim10_id',
1005 'fb',
1006 p_rownum,
1007 p_xml_file_type);
1008 END IF;
1009 ELSIF (p_xml_file_type = 'VSMPID') THEN
1010 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('LINE_ITEM_ID'),
1011 'line_item_id',
1012 'gel',
1013 p_rownum,
1014 p_xml_file_type);
1015 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('CHANNEL_ID'),
1016 'channel_id',
1017 'gel',
1018 p_rownum,
1019 p_xml_file_type);
1020 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('CUSTOMER_ID'),
1021 'customer_id',
1022 'gel',
1023 p_rownum,
1024 p_xml_file_type);
1025 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('COMPANY_COST_CENTER_ORG_ID'),
1026 'company_cost_center_org_id',
1027 'gel',
1028 p_rownum,
1029 p_xml_file_type);
1030 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID'),
1031 'natural_account_id',
1032 'gel',
1033 p_rownum,
1034 p_xml_file_type);
1035 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('PRODUCT_ID'),
1036 'product_id',
1037 'gel',
1038 p_rownum,
1039 p_xml_file_type);
1040 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('PROJECT_ID'),
1041 'project_id',
1042 'gel',
1043 p_rownum,
1044 p_xml_file_type);
1045 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('TASK_ID'),
1046 'task_id',
1047 'gel',
1048 p_rownum,
1049 p_xml_file_type);
1050 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
1051 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM1_ID'),
1052 'user_dim1_id',
1053 'gel',
1054 p_rownum,
1055 p_xml_file_type);
1056 END IF;
1057 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
1058 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM2_ID'),
1059 'user_dim2_id',
1060 'gel',
1061 p_rownum,
1062 p_xml_file_type);
1063 END IF;
1064 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
1065 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM3_ID'),
1066 'user_dim3_id',
1067 'gel',
1068 p_rownum,
1069 p_xml_file_type);
1070 END IF;
1071 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
1072 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM4_ID'),
1073 'user_dim4_id',
1074 'gel',
1075 p_rownum,
1076 p_xml_file_type);
1077 END IF;
1078 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
1079 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM5_ID'),
1080 'user_dim5_id',
1081 'gel',
1082 p_rownum,
1083 p_xml_file_type);
1084 END IF;
1085 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
1086 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM6_ID'),
1087 'user_dim6_id',
1088 'gel',
1089 p_rownum,
1090 p_xml_file_type);
1091 END IF;
1092 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
1093 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM7_ID'),
1094 'user_dim7_id',
1095 'gel',
1096 p_rownum,
1097 p_xml_file_type);
1098 END IF;
1099 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
1100 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM8_ID'),
1101 'user_dim8_id',
1102 'gel',
1103 p_rownum,
1104 p_xml_file_type);
1105 END IF;
1106 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
1107 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM9_ID'),
1108 'user_dim9_id',
1109 'gel',
1110 p_rownum,
1111 p_xml_file_type);
1112 END IF;
1113 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
1114 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM10_ID'),
1115 'user_dim10_id',
1116 'gel',
1117 p_rownum,
1118 p_xml_file_type);
1119 END IF;
1120 ELSIF (p_xml_file_type = 'CCIDHASH') THEN
1121 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('COMPANY_COST_CENTER_ORG_ID'),
1122 'company_cost_center_org_id',
1123 'fb',
1124 p_rownum,
1125 p_xml_file_type);
1126 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('LINE_ITEM_ID'),
1127 'line_item_id',
1128 'fb',
1129 p_rownum,
1130 p_xml_file_type);
1131 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
1132 'channel_id',
1133 'fb',
1134 p_rownum,
1135 p_xml_file_type);
1136 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
1137 'customer_id',
1138 'fb',
1139 p_rownum,
1140 p_xml_file_type);
1141 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
1142 'natural_account_id',
1143 'fb',
1144 p_rownum,
1145 p_xml_file_type);
1146 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
1147 'product_id',
1148 'fb',
1149 p_rownum,
1150 p_xml_file_type);
1151 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
1152 'project_id',
1153 'fb',
1154 p_rownum,
1155 p_xml_file_type);
1156 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
1157 'task_id',
1158 'fb',
1159 p_rownum,
1160 p_xml_file_type);
1161 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
1162 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
1163 'user_dim1_id',
1164 'fb',
1165 p_rownum,
1166 p_xml_file_type);
1167 END IF;
1168 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
1169 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
1170 'user_dim2_id',
1171 'fb',
1172 p_rownum,
1173 p_xml_file_type);
1174 END IF;
1175 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
1176 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
1177 'user_dim3_id',
1178 'fb',
1179 p_rownum,
1180 p_xml_file_type);
1181 END IF;
1182 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
1183 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
1184 'user_dim4_id',
1185 'fb',
1186 p_rownum,
1187 p_xml_file_type);
1188 END IF;
1189 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
1190 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
1191 'user_dim5_id',
1192 'fb',
1193 p_rownum,
1194 p_xml_file_type);
1195 END IF;
1196 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
1197 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
1198 'user_dim6_id',
1199 'fb',
1200 p_rownum,
1201 p_xml_file_type);
1202 END IF;
1203 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
1204 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
1205 'user_dim7_id',
1206 'fb',
1207 p_rownum,
1208 p_xml_file_type);
1209 END IF;
1210 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
1211 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
1212 'user_dim8_id',
1213 'fb',
1214 p_rownum,
1215 p_xml_file_type);
1216 END IF;
1217 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
1218 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
1219 'user_dim9_id',
1220 'fb',
1221 p_rownum,
1222 p_xml_file_type);
1223 END IF;
1224 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
1225 add_select_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
1226 'user_dim10_id',
1227 'fb',
1228 p_rownum,
1229 p_xml_file_type);
1230 END IF;
1231 ELSE
1232 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('CHANNEL_ID'),
1233 'channel_name',
1234 'fchb',
1235 p_rownum,
1236 p_xml_file_type);
1237 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('CUSTOMER_ID'),
1238 'customer_name',
1239 'fcb',
1240 p_rownum,
1241 p_xml_file_type);
1242 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID'),
1243 'financial_elem_name',
1244 'ffeb',
1245 p_rownum,
1246 p_xml_file_type);
1247 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID'),
1248 'natural_account_name',
1249 'fnab',
1250 p_rownum,
1251 p_xml_file_type);
1252 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('PRODUCT_ID'),
1253 'product_name',
1254 'fpb',
1255 p_rownum,
1256 p_xml_file_type);
1257 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('PROJECT_ID'),
1258 'project_name',
1259 'fpjb',
1260 p_rownum,
1261 p_xml_file_type);
1262 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('TASK_ID'),
1263 'task_name',
1264 'ftb',
1265 p_rownum,
1266 p_xml_file_type);
1267 IF (gcs_utility_pkg.get_dimension_required('USER_DIM1_ID') = 'Y') THEN
1268 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM1_ID'),
1269 'user_dim1_name',
1270 'fud1',
1271 p_rownum,
1272 p_xml_file_type);
1273 END IF;
1274 IF (gcs_utility_pkg.get_dimension_required('USER_DIM2_ID') = 'Y') THEN
1275 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM2_ID'),
1276 'user_dim2_name',
1277 'fud2',
1278 p_rownum,
1279 p_xml_file_type);
1280 END IF;
1281 IF (gcs_utility_pkg.get_dimension_required('USER_DIM3_ID') = 'Y') THEN
1282 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM3_ID'),
1283 'user_dim3_name',
1284 'fud3',
1285 p_rownum,
1286 p_xml_file_type);
1287 END IF;
1288 IF (gcs_utility_pkg.get_dimension_required('USER_DIM4_ID') = 'Y') THEN
1289 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM4_ID'),
1290 'user_dim4_name',
1291 'fud4',
1292 p_rownum,
1293 p_xml_file_type);
1294 END IF;
1295 IF (gcs_utility_pkg.get_dimension_required('USER_DIM5_ID') = 'Y') THEN
1296 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM5_ID'),
1297 'user_dim5_name',
1298 'fud5',
1299 p_rownum,
1300 p_xml_file_type);
1301 END IF;
1302 IF (gcs_utility_pkg.get_dimension_required('USER_DIM6_ID') = 'Y') THEN
1303 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM6_ID'),
1304 'user_dim6_name',
1305 'fud6',
1306 p_rownum,
1307 p_xml_file_type);
1308 END IF;
1309 IF (gcs_utility_pkg.get_dimension_required('USER_DIM7_ID') = 'Y') THEN
1310 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM7_ID'),
1311 'user_dim7_name',
1312 'fud7',
1313 p_rownum,
1314 p_xml_file_type);
1315 END IF;
1316 IF (gcs_utility_pkg.get_dimension_required('USER_DIM8_ID') = 'Y') THEN
1317 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM8_ID'),
1318 'user_dim8_name',
1319 'fud8',
1320 p_rownum,
1321 p_xml_file_type);
1322 END IF;
1323 IF (gcs_utility_pkg.get_dimension_required('USER_DIM9_ID') = 'Y') THEN
1324 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM9_ID'),
1325 'user_dim9_name',
1326 'fud9',
1327 p_rownum,
1328 p_xml_file_type);
1329 END IF;
1330 IF (gcs_utility_pkg.get_dimension_required('USER_DIM10_ID') = 'Y') THEN
1331 add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM10_ID'),
1332 'user_dim10_name',
1333 'fud10',
1334 p_rownum,
1335 p_xml_file_type);
1336 END IF;
1337 END IF;
1338 END build_select_clause_list;
1339 PROCEDURE build_where_clause_list(p_rownum IN OUT NOCOPY NUMBER,
1340 p_xml_file_type IN VARCHAR2) IS
1341 l_dim_info gcs_utility_pkg.t_hash_gcs_dimension_info := gcs_utility_pkg.g_gcs_dimension_info;
1342 BEGIN
1343 -- Santosh 5234796
1344 /*IF p_xml_file_type = 'DSLOAD' THEN
1345 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'), 'channel_',
1346 'fchb', p_rownum, p_xml_file_type);
1347 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'), 'customer_',
1348 'fcb', p_rownum, p_xml_file_type);
1349 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'), 'financial_elem_',
1350 'ffeb', p_rownum, p_xml_file_type);
1351 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'), 'natural_account_',
1352 'fnab', p_rownum, p_xml_file_type);
1353 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'), 'product_',
1354 'fpb',
1355 p_rownum, p_xml_file_type);
1356 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'), 'project_',
1357 'fpjb', p_rownum, p_xml_file_type);
1358 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'), 'task_',
1359 'ftb', p_rownum, p_xml_file_type);
1360 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
1361 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),'user_dim10_',
1362 'fud10', p_rownum, p_xml_file_type);
1363 END IF;
1364 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
1365 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'), 'user_dim1_',
1366 'fud1', p_rownum, p_xml_file_type);
1367 END IF;
1368 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
1369 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'), 'user_dim2_',
1370 'fud2', p_rownum, p_xml_file_type);
1371 END IF;
1372 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
1373 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'), 'user_dim3_',
1374 'fud3', p_rownum, p_xml_file_type);
1375 END IF;
1376 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
1377 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'), 'user_dim4_',
1378 'fud4', p_rownum, p_xml_file_type);
1379 END IF;
1380 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
1381 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'), 'user_dim5_',
1382 'fud5', p_rownum, p_xml_file_type);
1383 END IF;
1384 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
1385 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'), 'user_dim6_',
1386 'fud6', p_rownum, p_xml_file_type);
1387 END IF;
1388 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
1389 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'), 'user_dim7_',
1390 'fud7', p_rownum, p_xml_file_type);
1391 END IF;
1392 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
1393 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'), 'user_dim8_',
1394 'fud8', p_rownum, p_xml_file_type);
1395 END IF;
1396 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
1397 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'), 'user_dim9_',
1398 'fud9', p_rownum, p_xml_file_type);
1399 END IF;
1400 ELS*/
1401 IF p_xml_file_type = 'DSTB' THEN
1402 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
1403 'channel_id',
1404 'fchb',
1405 p_rownum,
1406 p_xml_file_type);
1407 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
1408 'customer_id',
1409 'fcb',
1410 p_rownum,
1411 p_xml_file_type);
1412 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'),
1413 'financial_elem_id',
1414 'ffeb',
1415 p_rownum,
1416 p_xml_file_type);
1417 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
1418 'natural_account_id',
1419 'fnab',
1420 p_rownum,
1421 p_xml_file_type);
1422 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
1423 'product_id',
1424 'fpb',
1425 p_rownum,
1426 p_xml_file_type);
1427 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
1428 'project_id',
1429 'fpjb',
1430 p_rownum,
1431 p_xml_file_type);
1432 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
1433 'task_id',
1434 'ftb',
1435 p_rownum,
1436 p_xml_file_type);
1437 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
1438 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
1439 'user_dim10_id',
1440 'fud10',
1441 p_rownum,
1442 p_xml_file_type);
1443 END IF;
1444 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
1445 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
1446 'user_dim1_id',
1447 'fud1',
1448 p_rownum,
1449 p_xml_file_type);
1450 END IF;
1451 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
1452 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
1453 'user_dim2_id',
1454 'fud2',
1455 p_rownum,
1456 p_xml_file_type);
1457 END IF;
1458 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
1459 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
1460 'user_dim3_id',
1461 'fud3',
1462 p_rownum,
1463 p_xml_file_type);
1464 END IF;
1465 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
1466 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
1467 'user_dim4_id',
1468 'fud4',
1469 p_rownum,
1470 p_xml_file_type);
1471 END IF;
1472 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
1473 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
1474 'user_dim5_id',
1475 'fud5',
1476 p_rownum,
1477 p_xml_file_type);
1478 END IF;
1479 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
1480 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
1481 'user_dim6_id',
1482 'fud6',
1483 p_rownum,
1484 p_xml_file_type);
1485 END IF;
1486 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
1487 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
1488 'user_dim7_id',
1489 'fud7',
1490 p_rownum,
1491 p_xml_file_type);
1492 END IF;
1493 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
1494 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
1495 'user_dim8_id',
1496 'fud8',
1497 p_rownum,
1498 p_xml_file_type);
1499 END IF;
1500 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
1501 add_clause_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
1502 'user_dim9_id',
1503 'fud9',
1504 p_rownum,
1505 p_xml_file_type);
1506 END IF;
1507 ELSE
1508 add_clause_to_list(gcs_utility_pkg.get_dimension_required('CHANNEL_ID'),
1509 'channel_id',
1510 'fchb',
1511 p_rownum,
1512 p_xml_file_type);
1513 add_clause_to_list(gcs_utility_pkg.get_dimension_required('CUSTOMER_ID'),
1514 'customer_id',
1515 'fcb',
1516 p_rownum,
1517 p_xml_file_type);
1518 add_clause_to_list(gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID'),
1519 'financial_elem_id',
1520 'ffeb',
1521 p_rownum,
1522 p_xml_file_type);
1523 add_clause_to_list(gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID'),
1524 'natural_account_id',
1525 'fnab',
1526 p_rownum,
1527 p_xml_file_type);
1528 add_clause_to_list(gcs_utility_pkg.get_dimension_required('PRODUCT_ID'),
1529 'product_id',
1530 'fpb',
1531 p_rownum,
1532 p_xml_file_type);
1533 add_clause_to_list(gcs_utility_pkg.get_dimension_required('PROJECT_ID'),
1534 'project_id',
1535 'fpjb',
1536 p_rownum,
1537 p_xml_file_type);
1538 add_clause_to_list(gcs_utility_pkg.get_dimension_required('TASK_ID'),
1539 'task_id',
1540 'ftb',
1541 p_rownum,
1542 p_xml_file_type);
1543 IF (gcs_utility_pkg.get_dimension_required('USER_DIM10_ID') = 'Y') THEN
1544 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM10_ID'),
1545 l_dim_info('USER_DIM10_ID').dim_member_col,
1546 'fud10',
1547 p_rownum,
1548 p_xml_file_type);
1549 END IF;
1550 IF (gcs_utility_pkg.get_dimension_required('USER_DIM1_ID') = 'Y') THEN
1551 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM1_ID'),
1552 l_dim_info('USER_DIM1_ID').dim_member_col,
1553 'fud1',
1554 p_rownum,
1555 p_xml_file_type);
1556 END IF;
1557 IF (gcs_utility_pkg.get_dimension_required('USER_DIM2_ID') = 'Y') THEN
1558 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM2_ID'),
1559 l_dim_info('USER_DIM2_ID').dim_member_col,
1560 'fud2',
1561 p_rownum,
1562 p_xml_file_type);
1563 END IF;
1564 IF (gcs_utility_pkg.get_dimension_required('USER_DIM3_ID') = 'Y') THEN
1565 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM3_ID'),
1566 l_dim_info('USER_DIM3_ID').dim_member_col,
1567 'fud3',
1568 p_rownum,
1569 p_xml_file_type);
1570 END IF;
1571 IF (gcs_utility_pkg.get_dimension_required('USER_DIM4_ID') = 'Y') THEN
1572 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM4_ID'),
1573 l_dim_info('USER_DIM4_ID').dim_member_col,
1574 'fud4',
1575 p_rownum,
1576 p_xml_file_type);
1577 END IF;
1578 IF (gcs_utility_pkg.get_dimension_required('USER_DIM5_ID') = 'Y') THEN
1579 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM5_ID'),
1580 l_dim_info('USER_DIM5_ID').dim_member_col,
1581 'fud5',
1582 p_rownum,
1583 p_xml_file_type);
1584 END IF;
1585 IF (gcs_utility_pkg.get_dimension_required('USER_DIM6_ID') = 'Y') THEN
1586 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM6_ID'),
1587 l_dim_info('USER_DIM6_ID').dim_member_col,
1588 'fud6',
1589 p_rownum,
1590 p_xml_file_type);
1591 END IF;
1592 IF (gcs_utility_pkg.get_dimension_required('USER_DIM7_ID') = 'Y') THEN
1593 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM7_ID'),
1594 l_dim_info('USER_DIM7_ID').dim_member_col,
1595 'fud7',
1596 p_rownum,
1597 p_xml_file_type);
1598 END IF;
1599 IF (gcs_utility_pkg.get_dimension_required('USER_DIM8_ID') = 'Y') THEN
1600 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM8_ID'),
1601 l_dim_info('USER_DIM8_ID').dim_member_col,
1602 'fud8',
1603 p_rownum,
1604 p_xml_file_type);
1605 END IF;
1606 IF (gcs_utility_pkg.get_dimension_required('USER_DIM9_ID') = 'Y') THEN
1607 add_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM9_ID'),
1608 l_dim_info('USER_DIM9_ID').dim_member_col,
1609 'fud9',
1610 p_rownum,
1611 p_xml_file_type);
1612 END IF;
1613 END IF;
1614 END build_where_clause_list;
1615 PROCEDURE add_table_to_list(p_dimension_required IN VARCHAR2,
1616 p_table_name IN VARCHAR2,
1617 p_table_alias IN VARCHAR2,
1618 p_rownum IN OUT NOCOPY NUMBER,
1619 p_xml_file_type IN VARCHAR2) IS
1620 BEGIN
1621 IF (p_dimension_required = 'Y') THEN
1622 ad_ddl.build_statement(''' ' || p_table_name || ' ' ||
1623 p_table_alias || ', '' ||g_nl||',
1624 p_rownum);
1625 p_rownum := p_rownum + 1;
1626 IF (p_xml_file_type = 'RULES') THEN
1627 ad_ddl.build_statement(''' ' || p_table_name || ' ' ||
1628 p_table_alias || '1, '' ||g_nl||',
1629 p_rownum);
1630 p_rownum := p_rownum + 1;
1631 END IF;
1632 END IF;
1633 END add_table_to_list;
1634 PROCEDURE build_table_list(p_rownum IN OUT NOCOPY NUMBER,
1635 p_xml_file_type IN VARCHAR2) IS
1636 l_dim_info gcs_utility_pkg.t_hash_gcs_dimension_info := gcs_utility_pkg.g_gcs_dimension_info;
1637 BEGIN
1638 IF (p_xml_file_type = 'DSTB') THEN
1639 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'),
1640 'fem_channels_tl',
1641 'fchb',
1642 p_rownum,
1643 p_xml_file_type);
1644 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'),
1645 'fem_customers_tl',
1646 'fcb',
1647 p_rownum,
1648 p_xml_file_type);
1649 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'),
1650 'fem_fin_elems_tl',
1651 'ffeb',
1652 p_rownum,
1653 p_xml_file_type);
1654 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'),
1655 'fem_nat_accts_tl',
1656 'fnab',
1657 p_rownum,
1658 p_xml_file_type);
1659 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'),
1660 'fem_products_tl',
1661 'fpb',
1662 p_rownum,
1663 p_xml_file_type);
1664 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'),
1665 'fem_projects_tl',
1666 'fpjb',
1667 p_rownum,
1668 p_xml_file_type);
1669 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'),
1670 'fem_tasks_tl',
1671 'ftb',
1672 p_rownum,
1673 p_xml_file_type);
1674 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
1675 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),
1676 'FEM_USER_DIM10_TL',
1677 'fud10',
1678 p_rownum,
1679 p_xml_file_type);
1680 END IF;
1681 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
1682 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'),
1683 'FEM_USER_DIM1_TL',
1684 'fud1',
1685 p_rownum,
1686 p_xml_file_type);
1687 END IF;
1688 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
1689 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'),
1690 'FEM_USER_DIM2_TL',
1691 'fud2',
1692 p_rownum,
1693 p_xml_file_type);
1694 END IF;
1695 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
1696 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'),
1697 'FEM_USER_DIM3_TL',
1698 'fud3',
1699 p_rownum,
1700 p_xml_file_type);
1701 END IF;
1702 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
1703 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'),
1704 'FEM_USER_DIM4_TL',
1705 'fud4',
1706 p_rownum,
1707 p_xml_file_type);
1708 END IF;
1709 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
1710 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'),
1711 'FEM_USER_DIM5_TL',
1712 'fud5',
1713 p_rownum,
1714 p_xml_file_type);
1715 END IF;
1716 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
1717 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'),
1718 'FEM_USER_DIM6_TL',
1719 'fud6',
1720 p_rownum,
1721 p_xml_file_type);
1722 END IF;
1723 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
1724 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'),
1725 'FEM_USER_DIM7_TL',
1726 'fud7',
1727 p_rownum,
1728 p_xml_file_type);
1729 END IF;
1730 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
1731 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'),
1732 'FEM_USER_DIM8_TL',
1733 'fud8',
1734 p_rownum,
1735 p_xml_file_type);
1736 END IF;
1737 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
1738 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'),
1739 'FEM_USER_DIM9_TL',
1740 'fud9',
1741 p_rownum,
1742 p_xml_file_type);
1743 END IF;
1744 -- Santosh 5234796
1745 /*ELSIF (p_xml_file_type = 'DSLOAD') THEN
1746 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'), 'fem_channels_b', 'fchb', p_rownum, p_xml_file_type);
1747 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID'), 'fem_channels_tl', 'fchbt', p_rownum, p_xml_file_type);
1748 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'), 'fem_customers_b', 'fcb', p_rownum, p_xml_file_type);
1749 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID'), 'fem_customers_tl', 'fcbt', p_rownum, p_xml_file_type);
1750 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'), 'fem_fin_elems_b', 'ffeb', p_rownum, p_xml_file_type);
1751 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID'), 'fem_fin_elems_tl', 'ffebt', p_rownum, p_xml_file_type);
1752 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'), 'fem_nat_accts_b', 'fnab', p_rownum, p_xml_file_type);
1753 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID'), 'fem_nat_accts_tl', 'fnabt', p_rownum, p_xml_file_type);
1754 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'), 'fem_products_b', 'fpb', p_rownum, p_xml_file_type);
1755 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID'), 'fem_products_tl', 'fpbt', p_rownum, p_xml_file_type);
1756 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'), 'fem_projects_b', 'fpjb', p_rownum, p_xml_file_type);
1757 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('PROJECT_ID'), 'fem_projects_tl', 'fpjbt', p_rownum, p_xml_file_type);
1758 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'), 'fem_tasks_b', 'ftb', p_rownum, p_xml_file_type);
1759 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('TASK_ID'), 'fem_tasks_tl', 'ftbt', p_rownum, p_xml_file_type);
1760 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
1761 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),'FEM_USER_DIM10_B',
1762 'fud10', p_rownum, p_xml_file_type);
1763 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID'),'FEM_USER_DIM10_TL',
1764 'fud10T', p_rownum, p_xml_file_type);
1765 END IF;
1766 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
1767 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'), 'FEM_USER_DIM1_B',
1768 'fud1', p_rownum, p_xml_file_type);
1769 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID'), 'FEM_USER_DIM1_TL',
1770 'fud1T', p_rownum, p_xml_file_type);
1771 END IF;
1772 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
1773 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'), 'FEM_USER_DIM2_B',
1774 'fud2', p_rownum, p_xml_file_type);
1775 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID'), 'FEM_USER_DIM2_TL',
1776 'fud2T', p_rownum, p_xml_file_type);
1777 END IF;
1778 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
1779 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'), 'FEM_USER_DIM3_B',
1780 'fud3', p_rownum, p_xml_file_type);
1781 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID'), 'FEM_USER_DIM3_TL',
1782 'fud3T', p_rownum, p_xml_file_type);
1783 END IF;
1784 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
1785 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'), 'FEM_USER_DIM4_B',
1786 'fud4', p_rownum, p_xml_file_type);
1787 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID'), 'FEM_USER_DIM4_TL',
1788 'fud4T', p_rownum, p_xml_file_type);
1789 END IF;
1790 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
1791 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'), 'FEM_USER_DIM5_B',
1792 'fud5', p_rownum, p_xml_file_type);
1793 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID'), 'FEM_USER_DIM5_TL',
1794 'fud5T', p_rownum, p_xml_file_type);
1795 END IF;
1796 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
1797 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'), 'FEM_USER_DIM6_B',
1798 'fud6', p_rownum, p_xml_file_type);
1799 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID'), 'FEM_USER_DIM6_TL',
1800 'fud6T', p_rownum, p_xml_file_type);
1801 END IF;
1802 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
1803 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'), 'FEM_USER_DIM7_B',
1804 'fud7', p_rownum, p_xml_file_type);
1805 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID'), 'FEM_USER_DIM7_TL',
1806 'fud7T', p_rownum, p_xml_file_type);
1807 END IF;
1808 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
1809 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'), 'FEM_USER_DIM8_B',
1810 'fud8', p_rownum, p_xml_file_type);
1811 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID'), 'FEM_USER_DIM8_TL',
1812 'fud8T', p_rownum, p_xml_file_type);
1813 END IF;
1814 IF (gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
1815 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'), 'FEM_USER_DIM9_B',
1816 'fud9', p_rownum, p_xml_file_type);
1817 add_table_to_list(gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID'), 'FEM_USER_DIM9_TL',
1818 'fud9T', p_rownum, p_xml_file_type);
1819 END IF;
1820 */
1821 ELSIF (p_xml_file_type = 'ADTB') THEN
1822 add_table_to_list(gcs_utility_pkg.get_dimension_required('CHANNEL_ID'),
1823 'fem_channels_tl',
1824 'fchb',
1825 p_rownum,
1826 p_xml_file_type);
1827 add_table_to_list(gcs_utility_pkg.get_dimension_required('CUSTOMER_ID'),
1828 'fem_customers_tl',
1829 'fcb',
1830 p_rownum,
1831 p_xml_file_type);
1832 add_table_to_list(gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID'),
1833 'fem_fin_elems_tl',
1834 'ffeb',
1835 p_rownum,
1836 p_xml_file_type);
1837 add_table_to_list(gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID'),
1838 'fem_nat_accts_tl',
1839 'fnab',
1840 p_rownum,
1841 p_xml_file_type);
1842 add_table_to_list(gcs_utility_pkg.get_dimension_required('PRODUCT_ID'),
1843 'fem_products_tl',
1844 'fpb',
1845 p_rownum,
1846 p_xml_file_type);
1847 add_table_to_list(gcs_utility_pkg.get_dimension_required('PROJECT_ID'),
1848 'fem_projects_tl',
1849 'fpjb',
1850 p_rownum,
1851 p_xml_file_type);
1852 add_table_to_list(gcs_utility_pkg.get_dimension_required('TASK_ID'),
1853 'fem_tasks_tl',
1854 'ftb',
1855 p_rownum,
1856 p_xml_file_type);
1857 IF (gcs_utility_pkg.get_dimension_required('USER_DIM10_ID') = 'Y') THEN
1858 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM10_ID'),
1859 'FEM_USER_DIM10_TL',
1860 'fud10',
1861 p_rownum,
1862 p_xml_file_type);
1863 END IF;
1864 IF (gcs_utility_pkg.get_dimension_required('USER_DIM1_ID') = 'Y') THEN
1865 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM1_ID'),
1866 'FEM_USER_DIM1_TL',
1867 'fud1',
1868 p_rownum,
1869 p_xml_file_type);
1870 END IF;
1871 IF (gcs_utility_pkg.get_dimension_required('USER_DIM2_ID') = 'Y') THEN
1872 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM2_ID'),
1873 'FEM_USER_DIM2_TL',
1874 'fud2',
1875 p_rownum,
1876 p_xml_file_type);
1877 END IF;
1878 IF (gcs_utility_pkg.get_dimension_required('USER_DIM3_ID') = 'Y') THEN
1879 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM3_ID'),
1880 'FEM_USER_DIM3_TL',
1881 'fud3',
1882 p_rownum,
1883 p_xml_file_type);
1884 END IF;
1885 IF (gcs_utility_pkg.get_dimension_required('USER_DIM4_ID') = 'Y') THEN
1886 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM4_ID'),
1887 'FEM_USER_DIM4_TL',
1888 'fud4',
1889 p_rownum,
1890 p_xml_file_type);
1891 END IF;
1892 IF (gcs_utility_pkg.get_dimension_required('USER_DIM5_ID') = 'Y') THEN
1893 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM5_ID'),
1894 'FEM_USER_DIM5_TL',
1895 'fud5',
1896 p_rownum,
1897 p_xml_file_type);
1898 END IF;
1899 IF (gcs_utility_pkg.get_dimension_required('USER_DIM6_ID') = 'Y') THEN
1900 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM6_ID'),
1901 'FEM_USER_DIM6_TL',
1902 'fud6',
1903 p_rownum,
1904 p_xml_file_type);
1905 END IF;
1906 IF (gcs_utility_pkg.get_dimension_required('USER_DIM7_ID') = 'Y') THEN
1907 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM7_ID'),
1908 'FEM_USER_DIM7_TL',
1909 'fud7',
1910 p_rownum,
1911 p_xml_file_type);
1912 END IF;
1913 IF (gcs_utility_pkg.get_dimension_required('USER_DIM8_ID') = 'Y') THEN
1914 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM8_ID'),
1915 'FEM_USER_DIM8_TL',
1916 'fud8',
1917 p_rownum,
1918 p_xml_file_type);
1919 END IF;
1920 IF (gcs_utility_pkg.get_dimension_required('USER_DIM9_ID') = 'Y') THEN
1921 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM9_ID'),
1922 'FEM_USER_DIM9_TL',
1923 'fud9',
1924 p_rownum,
1925 p_xml_file_type);
1926 END IF;
1927 ELSE
1928 add_table_to_list(gcs_utility_pkg.get_dimension_required('CHANNEL_ID'),
1929 'fem_channels_tl',
1930 'fchb',
1931 p_rownum,
1932 p_xml_file_type);
1933 add_table_to_list(gcs_utility_pkg.get_dimension_required('CUSTOMER_ID'),
1934 'fem_customers_tl',
1935 'fcb',
1936 p_rownum,
1937 p_xml_file_type);
1938 add_table_to_list(gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID'),
1939 'fem_fin_elems_tl',
1940 'ffeb',
1941 p_rownum,
1942 p_xml_file_type);
1943 add_table_to_list(gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID'),
1944 'fem_nat_accts_tl',
1945 'fnab',
1946 p_rownum,
1947 p_xml_file_type);
1948 add_table_to_list(gcs_utility_pkg.get_dimension_required('PRODUCT_ID'),
1949 'fem_products_tl',
1950 'fpb',
1951 p_rownum,
1952 p_xml_file_type);
1953 add_table_to_list(gcs_utility_pkg.get_dimension_required('PROJECT_ID'),
1954 'fem_projects_tl',
1955 'fpjb',
1956 p_rownum,
1957 p_xml_file_type);
1958 add_table_to_list(gcs_utility_pkg.get_dimension_required('TASK_ID'),
1959 'fem_tasks_tl',
1960 'ftb',
1961 p_rownum,
1962 p_xml_file_type);
1963 IF (gcs_utility_pkg.get_dimension_required('USER_DIM10_ID') = 'Y') THEN
1964 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM10_ID'),
1965 'FEM_USER_DIM10_TL',
1966 'fud10',
1967 p_rownum,
1968 p_xml_file_type);
1969 END IF;
1970 IF (gcs_utility_pkg.get_dimension_required('USER_DIM1_ID') = 'Y') THEN
1971 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM1_ID'),
1972 'FEM_USER_DIM1_TL',
1973 'fud1',
1974 p_rownum,
1975 p_xml_file_type);
1976 END IF;
1977 IF (gcs_utility_pkg.get_dimension_required('USER_DIM2_ID') = 'Y') THEN
1978 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM2_ID'),
1979 'FEM_USER_DIM2_TL',
1980 'fud2',
1981 p_rownum,
1982 p_xml_file_type);
1983 END IF;
1984 IF (gcs_utility_pkg.get_dimension_required('USER_DIM3_ID') = 'Y') THEN
1985 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM3_ID'),
1986 'FEM_USER_DIM3_TL',
1987 'fud3',
1988 p_rownum,
1989 p_xml_file_type);
1990 END IF;
1991 IF (gcs_utility_pkg.get_dimension_required('USER_DIM4_ID') = 'Y') THEN
1992 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM4_ID'),
1993 'FEM_USER_DIM4_TL',
1994 'fud4',
1995 p_rownum,
1996 p_xml_file_type);
1997 END IF;
1998 IF (gcs_utility_pkg.get_dimension_required('USER_DIM5_ID') = 'Y') THEN
1999 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM5_ID'),
2000 'FEM_USER_DIM5_TL',
2001 'fud5',
2002 p_rownum,
2003 p_xml_file_type);
2004 END IF;
2005 IF (gcs_utility_pkg.get_dimension_required('USER_DIM6_ID') = 'Y') THEN
2006 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM6_ID'),
2007 'FEM_USER_DIM6_TL',
2008 'fud6',
2009 p_rownum,
2010 p_xml_file_type);
2011 END IF;
2012 IF (gcs_utility_pkg.get_dimension_required('USER_DIM7_ID') = 'Y') THEN
2013 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM7_ID'),
2014 'FEM_USER_DIM7_TL',
2015 'fud7',
2016 p_rownum,
2017 p_xml_file_type);
2018 END IF;
2019 IF (gcs_utility_pkg.get_dimension_required('USER_DIM8_ID') = 'Y') THEN
2020 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM8_ID'),
2021 'FEM_USER_DIM8_TL',
2022 'fud8',
2023 p_rownum,
2024 p_xml_file_type);
2025 END IF;
2026 IF (gcs_utility_pkg.get_dimension_required('USER_DIM9_ID') = 'Y') THEN
2027 add_table_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM9_ID'),
2028 'FEM_USER_DIM9_TL',
2029 'fud9',
2030 p_rownum,
2031 p_xml_file_type);
2032 END IF;
2033 END IF;
2034 END build_table_list;
2035 PROCEDURE create_xml_utility_PKG(p_retcode NUMBER, p_errbuf VARCHAR2) IS
2036 r NUMBER(15) := 1;
2037 comp_err VARCHAR2(200) := NULL;
2038 BEGIN
2039 ad_ddl.build_statement('CREATE OR REPLACE PACKAGE GCS_XML_UTILITY_PKG AS',
2040 r);
2041 r := r + 1;
2042 ad_ddl.build_statement(' ', r);
2043 r := r + 1;
2044 ad_ddl.build_statement('--API Name', r);
2045 r := r + 1;
2046 ad_ddl.build_statement(' g_api VARCHAR2(50) := ''gcs.plsql.GCS_XML_UTILITY_PKG'';',
2047 r);
2048 r := r + 1;
2049 ad_ddl.build_statement(' g_nl VARCHAR2(1) := ''
2050 '';',
2051 r);
2052 r := r + 1;
2053 ad_ddl.build_statement(' ', r);
2054 r := r + 1;
2055 ad_ddl.build_statement(' -- Action types for writing module information to the log file. Used for',
2056 r);
2057 r := r + 1;
2058 ad_ddl.build_statement(' -- the procedure log_file_module_write.', r);
2059 r := r + 1;
2060 ad_ddl.build_statement(' g_module_enter VARCHAR2(2) := ''>>'';', r);
2061 r := r + 1;
2062 ad_ddl.build_statement(' g_module_success VARCHAR2(2) := ''<<'';', r);
2063 r := r + 1;
2064 ad_ddl.build_statement(' g_module_failure VARCHAR2(2) := ''<x'';', r);
2065 r := r + 1;
2066 ad_ddl.build_statement(' ', r);
2067 r := r + 1;
2068 ad_ddl.build_statement('-- Beginning of private procedures ', r);
2069 r := r + 1;
2070 ad_ddl.build_statement(' ', r);
2071 r := r + 1;
2072 -- Create select list for GCS Active Dims
2073 ad_ddl.build_statement(' g_gcs_dims_select_list VARCHAR2(2000) := ',
2074 r);
2075 r := r + 1;
2076 build_select_clause_list(r, 'ENTRY');
2077 ad_ddl.build_statement(' '' ''; ', r);
2078 r := r + 1;
2079 ad_ddl.build_statement(' ', r);
2080 r := r + 1;
2081 ad_ddl.build_statement(' g_gcs_dims_table_list VARCHAR2(2000) := ', r);
2082 r := r + 1;
2083 build_table_list(r, 'ENTRY');
2084 ad_ddl.build_statement(' '' ''; ', r);
2085 r := r + 1;
2086 ad_ddl.build_statement(' ', r);
2087 r := r + 1;
2088 ad_ddl.build_statement(' g_gcs_dims_where_clause VARCHAR2(10000) := ',
2089 r);
2090 r := r + 1;
2091 build_where_clause_list(r, 'ENTRY');
2092 ad_ddl.build_statement(' '' ''; ', r);
2093 r := r + 1;
2094 ad_ddl.build_statement(' ', r);
2095 r := r + 1;
2096 ad_ddl.build_statement(' g_gcs_dims_xml_elem VARCHAR2(2000) := ', r);
2097 r := r + 1;
2098 build_select_clause_list(r, 'ENTRY_XML');
2099 ad_ddl.build_statement(' '' ''; ', r);
2100 r := r + 1;
2101 ad_ddl.build_statement(' ', r);
2102 r := r + 1;
2103 ad_ddl.build_statement(' g_gcs_vsmp_xml_elem VARCHAR2(10000) := ', r);
2104 r := r + 1;
2105 build_select_clause_list(r, 'VSMPID');
2106 ad_ddl.build_statement(' '' ''; ', r);
2107 r := r + 1;
2108 ad_ddl.build_statement(' ', r);
2109 r := r + 1;
2110 -- Create select list for FEM Active Dims
2111 ad_ddl.build_statement(' g_fem_dims_select_list_dsload VARCHAR2(2000) := ',
2112 r);
2113 r := r + 1;
2114 build_select_clause_list(r, 'DSLOAD');
2115 ad_ddl.build_statement(' '' ''; ', r);
2116 r := r + 1;
2117 ad_ddl.build_statement(' ', r);
2118 r := r + 1;
2119 ad_ddl.build_statement(' g_fem_dims_select_list_dstb VARCHAR2(2000) := ',
2120 r);
2121 r := r + 1;
2122 build_select_clause_list(r, 'DSTB');
2123 ad_ddl.build_statement(' '' ''; ', r);
2124 r := r + 1;
2125 ad_ddl.build_statement(' ', r);
2126 r := r + 1;
2127 --fix 5351083
2128 --ad_ddl.build_statement(' g_fem_dims_table_list_dsload VARCHAR2(2000) := ', r);
2129 --r := r + 1;
2130 --build_table_list(r, 'DSLOAD');
2131 --ad_ddl.build_statement(' '' ''; ', r);
2132 --r := r + 1;
2133 ad_ddl.build_statement(' ', r);
2134 r := r + 1;
2135 ad_ddl.build_statement(' g_fem_dims_table_list_dstb VARCHAR2(2000) := ',
2136 r);
2137 r := r + 1;
2138 build_table_list(r, 'DSTB');
2139 ad_ddl.build_statement(' '' ''; ', r);
2140 r := r + 1;
2141 ad_ddl.build_statement(' ', r);
2142 r := r + 1;
2143 ad_ddl.build_statement(' g_fem_dims_dstb_where_clause VARCHAR2(2000) := ',
2144 r);
2145 r := r + 1;
2146 build_where_clause_list(r, 'DSTB');
2147 ad_ddl.build_statement(' '' ''; ', r);
2148 r := r + 1;
2149 ad_ddl.build_statement(' ', r);
2150 r := r + 1;
2151 --fix 5351083
2152 --ad_ddl.build_statement(' g_fem_dims_dsload_where_clause VARCHAR2(10000) := ', r);
2153 --r := r + 1;
2154 --build_where_clause_list(r, 'DSLOAD');
2155 --ad_ddl.build_statement(' '' ''; ', r);
2156 --r := r + 1;
2157 ad_ddl.build_statement(' ', r);
2158 r := r + 1;
2159 ad_ddl.build_statement(' g_fem_dims_xml_elem VARCHAR2(2000) := ', r);
2160 r := r + 1;
2161 build_select_clause_list(r, 'DSTB_XML');
2162 ad_ddl.build_statement(' '' ''; ', r);
2163 r := r + 1;
2164 --fix 5351083
2165 --ad_ddl.build_statement(' p_element_list_dstb VARCHAR2(2000) := ', r);
2166 --r := r + 1;
2167 --build_select_clause_list(r, 'DSTB_XML_ID');
2168 --ad_ddl.build_statement(' '' ''; ', r);
2169 --r := r + 1;
2170 ad_ddl.build_statement(' g_group_by_stmnt VARCHAR2(1000) := ', r);
2171 r := r + 1;
2172 build_order_clause_list(r, 'OGL');
2173 ad_ddl.build_statement(' '' ''; ', r);
2174 r := r + 1;
2175 ad_ddl.build_statement(' ', r);
2176 r := r + 1;
2177
2178 --BUG 5147886
2179 ad_ddl.build_statement(' g_fem_nonposted_select_stmnt VARCHAR2(5000) := ',
2180 r);
2181 r := r + 1;
2182 build_select_clause_list(r, 'NONPOSTED_SELECT');
2183 ad_ddl.build_statement(' '' ''; ', r);
2184 r := r + 1;
2185 ad_ddl.build_statement(' ', r);
2186 r := r + 1;
2187
2188 ad_ddl.build_statement(' g_fem_nonposted_group_stmnt VARCHAR2(5000) := ',
2189 r);
2190 r := r + 1;
2191 build_select_clause_list(r, 'NONPOSTED_GROUP');
2192 ad_ddl.build_statement(' '' ''; ', r);
2193 r := r + 1;
2194 ad_ddl.build_statement(' ', r);
2195 r := r + 1;
2196
2197 --Santosh
2198 ad_ddl.build_statement(' g_fem_dims_dsload_order_clause VARCHAR2(1000) := ', r);
2199 r := r + 1;
2200 build_order_clause_list(r, 'DSLOAD');
2201 ad_ddl.build_statement(' '' ''; ', r);
2202 r := r + 1;
2203 ad_ddl.build_statement(' ', r);
2204 r := r + 1;
2205
2206 ad_ddl.build_statement('END GCS_XML_UTILITY_PKG;', r);
2207 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
2208 'GCS',
2209 'GCS_XML_UTILITY_PKG',
2210 1,
2211 r,
2212 'TRUE',
2213 comp_err);
2214 END create_xml_utility_PKG;
2215 END GCS_XML_DYNAMIC_PKG;