[Home] [Help]
PACKAGE BODY: APPS.GCS_DYN_TB_VIEW_PKG
Source
1 PACKAGE BODY GCS_DYN_TB_VIEW_PKG AS
2 /* $Header: gcs_dyn_tb_vb.pls 120.2 2005/07/01 23:15:53 mikeward noship $ */
3
4 --
5 -- Private Exceptions
6 --
7 GCS_DYN_TB_APPLSYS_NOT_FOUND EXCEPTION;
8 GCS_DYN_TB_V_BUILD_ERR EXCEPTION;
9
10 --
11 -- Private Global Variables
12 --
13 -- The API name
14 g_api CONSTANT VARCHAR2(40) := 'gcs.plsql.GCS_DYN_TB_VIEW_PKG';
15
16
17 -- Action types for writing module information to the log file. Used for
18 -- the procedure log_file_module_write.
19 g_module_enter CONSTANT VARCHAR2(2) := '>>';
20 g_module_success CONSTANT VARCHAR2(2) := '<<';
21 g_module_failure CONSTANT VARCHAR2(2) := '<x';
22
23 -- Newline character
24 g_nl CONSTANT VARCHAR2(1) := '
25 ';
26
27 --
28 -- Procedure
29 -- Module_Log_Write
30 -- Purpose
31 -- Write the procedure or function entered or exited, and the time that
32 -- this happened. Write it to the log repository.
33 -- Arguments
34 -- p_module Name of the module
35 -- p_action_type Entered, Exited Successfully, or Exited with Failure
36 -- Example
37 -- GCS_DYN_TB_VIEW_PKG.Module_Log_Write
38 -- Notes
39 --
40 PROCEDURE Module_Log_Write
41 (p_module VARCHAR2,
42 p_action_type VARCHAR2) IS
43 BEGIN
44 -- Only print if the log level is set at the appropriate level
45 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
46 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_api || '.' || p_module,
47 p_action_type || ' ' || p_module || '() ' ||
48 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
49 END IF;
50 FND_FILE.PUT_LINE(FND_FILE.LOG, p_action_type || ' ' || p_module ||
51 '() ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
52 END Module_Log_Write;
53
54 --
55 -- Procedure
56 -- Write_To_Log
57 -- Purpose
58 -- Write the text given to the log in 3500 character increments
59 -- this happened. Write it to the log repository.
60 -- Arguments
61 -- p_module Name of the module
62 -- p_level Logging level
63 -- p_text Text to write
64 -- Example
65 -- GCS_TRANSLATION_PKG.Write_To_Log
66 -- Notes
67 --
68 PROCEDURE Write_To_Log
69 (p_module VARCHAR2,
70 p_level NUMBER,
71 p_text VARCHAR2)
72 IS
73 api_module_concat VARCHAR2(200);
74 text_with_date VARCHAR2(32767);
75 text_with_date_len NUMBER;
76 curr_index NUMBER;
77 BEGIN
78 -- Only print if the log level is set at the appropriate level
79 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
80 api_module_concat := g_api || '.' || p_module;
81 text_with_date := to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||g_nl||p_text;
82 text_with_date_len := length(text_with_date);
83 curr_index := 1;
84 WHILE curr_index <= text_with_date_len LOOP
85 fnd_log.string(p_level, api_module_concat,
86 substr(text_with_date, curr_index, 1500));
87 curr_index := curr_index + 1500;
88 END LOOP;
89 END IF;
90 END Write_To_Log;
91
92 --
93 -- Procedure
94 -- Build_Dimension_Row
95 -- Purpose
96 -- Build one row of the comma or join list in ad_ddl.
97 -- Arguments
98 -- p_item The item to write if the dimension is used
99 -- p_def_item The item to write if the dimension is unused
100 -- p_rownum The row number to use for ad_ddl
101 -- p_dim_req Whether or not the dimension is required
102 -- Example
103 -- GCS_DYN_TB_VIEW_PKG.Build_Dimension_Row
104 -- Notes
105 --
106 PROCEDURE Build_Dimension_Row(p_item VARCHAR2,
107 p_def_item VARCHAR2,
108 p_rownum NUMBER,
109 p_dim_req VARCHAR2) IS
110 BEGIN
111 IF p_dim_req = 'Y' THEN
112 ad_ddl.build_statement(p_item, p_rownum);
113 ELSE
114 ad_ddl.build_statement(p_def_item, p_rownum);
115 END IF;
116 END Build_Dimension_Row;
117
118 --
119 -- Procedure
120 -- Optional_Row
121 -- Purpose
122 -- This will conditionally write a row to ad_ddl.
123 -- Arguments
124 -- p_text The text to be written if the condition passes
125 -- p_dim_req The condition
126 -- p_row_counter The row number
127 -- Example
128 -- GCS_DYN_TB_VIEW_PKG.Optional_Row
129 -- Notes
130 --
131 PROCEDURE Optional_Row( p_text VARCHAR2,
132 p_dim_req VARCHAR2,
133 p_row_counter IN OUT NOCOPY NUMBER) IS
134 BEGIN
135 IF p_dim_req = 'Y' THEN
136 ad_ddl.build_statement(p_text, p_row_counter);
137 p_row_counter := p_row_counter + 1;
138 END IF;
139 END Optional_Row;
140
141
142 --
143 -- Public procedures
144 --
145 PROCEDURE Create_View(
146 x_errbuf OUT NOCOPY VARCHAR2,
147 x_retcode OUT NOCOPY VARCHAR2) IS
148 -- row number to be used in dynamically creating the view
149 r NUMBER;
150
151 status VARCHAR2(1);
152 industry VARCHAR2(1);
153 appl VARCHAR2(30);
154
155 -- Store whether a dimension is used by GCS
156 l_fe_req VARCHAR2(1);
157 l_pd_req VARCHAR2(1);
158 l_na_req VARCHAR2(1);
159 l_ch_req VARCHAR2(1);
160 l_pj_req VARCHAR2(1);
161 l_cu_req VARCHAR2(1);
162 l_ta_req VARCHAR2(1);
163 l_ud1_req VARCHAR2(1);
164 l_ud2_req VARCHAR2(1);
165 l_ud3_req VARCHAR2(1);
166 l_ud4_req VARCHAR2(1);
167 l_ud5_req VARCHAR2(1);
168 l_ud6_req VARCHAR2(1);
169 l_ud7_req VARCHAR2(1);
170 l_ud8_req VARCHAR2(1);
171 l_ud9_req VARCHAR2(1);
172 l_ud10_req VARCHAR2(1);
173
174 l_error_text VARCHAR2(2000);
175
176 module VARCHAR2(30);
177 BEGIN
178 module := 'CREATE_VIEW';
179 module_log_write(module, g_module_enter);
180
181 -- Set the global variables determining which dimensions are used
182 l_fe_req := gcs_utility_pkg.get_fem_dim_required('FINANCIAL_ELEM_ID');
183 l_pd_req := gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID');
184 l_na_req := gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID');
185 l_ch_req := gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID');
186 l_pj_req := gcs_utility_pkg.get_fem_dim_required('PROJECT_ID');
187 l_cu_req := gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID');
188 l_ta_req := gcs_utility_pkg.get_fem_dim_required('TASK_ID');
189 l_ud1_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID');
190 l_ud2_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID');
191 l_ud3_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID');
192 l_ud4_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID');
193 l_ud5_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID');
194 l_ud6_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID');
195 l_ud7_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID');
196 l_ud8_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID');
197 l_ud9_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID');
198 l_ud10_req := gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID');
199
200 -- Get APPLSYS information. Needed for ad_ddl
201 IF NOT fnd_installation.get_app_info('FND', status, industry, appl) THEN
202 raise gcs_dyn_tb_applsys_not_found;
203 END IF;
204
205 r := 1;
206
207 -- Create the package body
208 ad_ddl.build_statement('CREATE OR REPLACE VIEW GCS_DYN_TB_V AS ', r); r:=r+1;
209 ad_ddl.build_statement('SELECT ', r); r:=r+1;
210 ad_ddl.build_statement(' fb.dataset_code, ', r); r:=r+1;
211 ad_ddl.build_statement(' fb.cal_period_id, ', r); r:=r+1;
212 ad_ddl.build_statement(' fb.entity_id, ', r); r:=r+1;
213 ad_ddl.build_statement(' fb.ledger_id, ', r); r:=r+1;
214 ad_ddl.build_statement(' fb.currency_code, ', r); r:=r+1;
215 ad_ddl.build_statement(' fb.currency_type_code, ', r); r:=r+1;
216 ad_ddl.build_statement(' fb.source_system_code, ', r); r:=r+1;
217 ad_ddl.build_statement(' fb.company_cost_center_org_id, ', r); r:=r+1;
218 ad_ddl.build_statement(' fb.intercompany_id, ', r); r:=r+1;
219 ad_ddl.build_statement(' fb.line_item_id, ', r); r:=r+1;
220 ad_ddl.build_statement(' fb.financial_elem_id, ', r); r:=r+1;
221 ad_ddl.build_statement(' fb.product_id, ', r); r:=r+1;
222 ad_ddl.build_statement(' fb.natural_account_id, ', r); r:=r+1;
223 ad_ddl.build_statement(' fb.channel_id, ', r); r:=r+1;
224 ad_ddl.build_statement(' fb.project_id, ', r); r:=r+1;
225 ad_ddl.build_statement(' fb.customer_id, ', r); r:=r+1;
226 ad_ddl.build_statement(' fb.task_id, ', r); r:=r+1;
227 ad_ddl.build_statement(' fb.user_dim1_id, ', r); r:=r+1;
228 ad_ddl.build_statement(' fb.user_dim2_id, ', r); r:=r+1;
229 ad_ddl.build_statement(' fb.user_dim3_id, ', r); r:=r+1;
230 ad_ddl.build_statement(' fb.user_dim4_id, ', r); r:=r+1;
231 ad_ddl.build_statement(' fb.user_dim5_id, ', r); r:=r+1;
232 ad_ddl.build_statement(' fb.user_dim6_id, ', r); r:=r+1;
233 ad_ddl.build_statement(' fb.user_dim7_id, ', r); r:=r+1;
234 ad_ddl.build_statement(' fb.user_dim8_id, ', r); r:=r+1;
235 ad_ddl.build_statement(' fb.user_dim9_id, ', r); r:=r+1;
236 ad_ddl.build_statement(' fb.user_dim10_id, ', r); r:=r+1;
237 ad_ddl.build_statement(' cotl.company_cost_center_org_name, ', r); r:=r+1;
238 ad_ddl.build_statement(' ictl.company_cost_center_org_name intercompany_name, ', r); r:=r+1;
239 ad_ddl.build_statement(' litl.line_item_name, ', r); r:=r+1;
240 build_dimension_row(' fetl.financial_elem_name, ', ' null financial_elem_name, ', r, l_fe_req); r:=r+1;
241 build_dimension_row(' pdtl.product_name, ', ' null product_name, ', r, l_pd_req); r:=r+1;
242 build_dimension_row(' natl.natural_account_name, ', ' null natural_account_name, ', r, l_na_req); r:=r+1;
243 build_dimension_row(' chtl.channel_name, ', ' null channel_name, ', r, l_ch_req); r:=r+1;
244 build_dimension_row(' pjtl.project_name, ', ' null project_name, ', r, l_pj_req); r:=r+1;
245 build_dimension_row(' cutl.customer_name, ', ' null customer_name, ', r, l_cu_req); r:=r+1;
246 build_dimension_row(' tatl.task_name, ', ' null task_name, ', r, l_ta_req); r:=r+1;
247 build_dimension_row(' ud1tl.user_dim1_name, ', ' null user_dim1_name, ', r, l_ud1_req); r:=r+1;
248 build_dimension_row(' ud2tl.user_dim2_name, ', ' null user_dim2_name, ', r, l_ud2_req); r:=r+1;
249 build_dimension_row(' ud3tl.user_dim3_name, ', ' null user_dim3_name, ', r, l_ud3_req); r:=r+1;
250 build_dimension_row(' ud4tl.user_dim4_name, ', ' null user_dim4_name, ', r, l_ud4_req); r:=r+1;
251 build_dimension_row(' ud5tl.user_dim5_name, ', ' null user_dim5_name, ', r, l_ud5_req); r:=r+1;
252 build_dimension_row(' ud6tl.user_dim6_name, ', ' null user_dim6_name, ', r, l_ud6_req); r:=r+1;
253 build_dimension_row(' ud7tl.user_dim7_name, ', ' null user_dim7_name, ', r, l_ud7_req); r:=r+1;
254 build_dimension_row(' ud8tl.user_dim8_name, ', ' null user_dim8_name, ', r, l_ud8_req); r:=r+1;
255 build_dimension_row(' ud9tl.user_dim9_name, ', ' null user_dim9_name, ', r, l_ud9_req); r:=r+1;
256 build_dimension_row(' ud10tl.user_dim10_name, ', ' null user_dim10_name, ', r, l_ud10_req); r:=r+1;
257 ad_ddl.build_statement(' cotl.description company_cost_center_org_desc, ', r); r:=r+1;
258 ad_ddl.build_statement(' ictl.description intercompany_desc, ', r); r:=r+1;
259 ad_ddl.build_statement(' litl.description line_item_desc, ', r); r:=r+1;
260 build_dimension_row(' fetl.description financial_elem_desc, ', ' null financial_elem_desc, ', r, l_fe_req); r:=r+1;
261 build_dimension_row(' pdtl.description product_desc, ', ' null product_desc, ', r, l_pd_req); r:=r+1;
262 build_dimension_row(' natl.description natural_account_desc, ', ' null natural_account_desc, ', r, l_na_req); r:=r+1;
263 build_dimension_row(' chtl.description channel_desc, ', ' null channel_desc, ', r, l_ch_req); r:=r+1;
264 build_dimension_row(' pjtl.description project_desc, ', ' null project_desc, ', r, l_pj_req); r:=r+1;
265 build_dimension_row(' cutl.description customer_desc, ', ' null customer_desc, ', r, l_cu_req); r:=r+1;
266 build_dimension_row(' tatl.description task_desc, ', ' null task_desc, ', r, l_ta_req); r:=r+1;
267 build_dimension_row(' ud1tl.description user_dim1_desc, ', ' null user_dim1_desc, ', r, l_ud1_req); r:=r+1;
268 build_dimension_row(' ud2tl.description user_dim2_desc, ', ' null user_dim2_desc, ', r, l_ud2_req); r:=r+1;
269 build_dimension_row(' ud3tl.description user_dim3_desc, ', ' null user_dim3_desc, ', r, l_ud3_req); r:=r+1;
270 build_dimension_row(' ud4tl.description user_dim4_desc, ', ' null user_dim4_desc, ', r, l_ud4_req); r:=r+1;
271 build_dimension_row(' ud5tl.description user_dim5_desc, ', ' null user_dim5_desc, ', r, l_ud5_req); r:=r+1;
272 build_dimension_row(' ud6tl.description user_dim6_desc, ', ' null user_dim6_desc, ', r, l_ud6_req); r:=r+1;
273 build_dimension_row(' ud7tl.description user_dim7_desc, ', ' null user_dim7_desc, ', r, l_ud7_req); r:=r+1;
274 build_dimension_row(' ud8tl.description user_dim8_desc, ', ' null user_dim8_desc, ', r, l_ud8_req); r:=r+1;
275 build_dimension_row(' ud9tl.description user_dim9_desc, ', ' null user_dim9_desc, ', r, l_ud9_req); r:=r+1;
276 build_dimension_row(' ud10tl.description user_dim10_desc, ', ' null user_dim10_desc, ', r, l_ud10_req); r:=r+1;
277 ad_ddl.build_statement(' fb.xtd_balance_e, ', r); r:=r+1;
278 ad_ddl.build_statement(' fb.xtd_balance_f, ', r); r:=r+1;
279 ad_ddl.build_statement(' fb.ytd_balance_e, ', r); r:=r+1;
283 ad_ddl.build_statement(' FEM_CCTR_ORGS_TL cotl, ', r); r:=r+1;
280 ad_ddl.build_statement(' fb.ytd_balance_f ', r); r:=r+1;
281 ad_ddl.build_statement('FROM ', r); r:=r+1;
282 ad_ddl.build_statement(' FEM_BALANCES fb, ', r); r:=r+1;
284 ad_ddl.build_statement(' FEM_CCTR_ORGS_TL ictl, ', r); r:=r+1;
285 optional_row(' FEM_FIN_ELEMS_TL fetl, ', l_fe_req, r);
286 optional_row(' FEM_PRODUCTS_TL pdtl, ', l_pd_req, r);
287 optional_row(' FEM_NAT_ACCTS_TL natl, ', l_na_req, r);
288 optional_row(' FEM_CHANNELS_TL chtl, ', l_ch_req, r);
289 optional_row(' FEM_PROJECTS_TL pjtl, ', l_pj_req, r);
290 optional_row(' FEM_CUSTOMERS_TL cutl, ', l_cu_req, r);
291 optional_row(' FEM_TASKS_TL tatl, ', l_ta_req, r);
292 optional_row(' FEM_USER_DIM1_TL ud1tl, ', l_ud1_req, r);
293 optional_row(' FEM_USER_DIM2_TL ud2tl, ', l_ud2_req, r);
294 optional_row(' FEM_USER_DIM3_TL ud3tl, ', l_ud3_req, r);
295 optional_row(' FEM_USER_DIM4_TL ud4tl, ', l_ud4_req, r);
296 optional_row(' FEM_USER_DIM5_TL ud5tl, ', l_ud5_req, r);
297 optional_row(' FEM_USER_DIM6_TL ud6tl, ', l_ud6_req, r);
298 optional_row(' FEM_USER_DIM7_TL ud7tl, ', l_ud7_req, r);
299 optional_row(' FEM_USER_DIM8_TL ud8tl, ', l_ud8_req, r);
300 optional_row(' FEM_USER_DIM9_TL ud9tl, ', l_ud9_req, r);
301 optional_row(' FEM_USER_DIM10_TL ud10tl, ', l_ud10_req, r);
302 ad_ddl.build_statement(' FEM_LN_ITEMS_TL litl ', r); r:=r+1;
303 ad_ddl.build_statement('WHERE cotl.company_cost_center_org_id = fb.company_cost_center_org_id ', r); r:=r+1;
304 ad_ddl.build_statement('AND cotl.language = userenv(''LANG'') ', r); r:=r+1;
305 ad_ddl.build_statement('AND ictl.company_cost_center_org_id = fb.intercompany_id ', r); r:=r+1;
306 ad_ddl.build_statement('AND ictl.language = userenv(''LANG'') ', r); r:=r+1;
307 optional_row('AND fetl.financial_elem_id = fb.financial_elem_id ', l_fe_req, r);
308 optional_row('AND fetl.language = userenv(''LANG'') ', l_fe_req, r);
309 optional_row('AND pdtl.product_id = fb.product_id ', l_pd_req, r);
310 optional_row('AND pdtl.language = userenv(''LANG'') ', l_pd_req, r);
311 optional_row('AND natl.natural_account_id = fb.natural_account_id ', l_na_req, r);
312 optional_row('AND natl.language = userenv(''LANG'') ', l_na_req, r);
313 optional_row('AND chtl.channel_id = fb.channel_id ', l_ch_req, r);
314 optional_row('AND chtl.language = userenv(''LANG'') ', l_ch_req, r);
315 optional_row('AND pjtl.project_id = fb.project_id ', l_pj_req, r);
316 optional_row('AND pjtl.language = userenv(''LANG'') ', l_pj_req, r);
317 optional_row('AND cutl.customer_id = fb.customer_id ', l_cu_req, r);
318 optional_row('AND cutl.language = userenv(''LANG'') ', l_cu_req, r);
319 optional_row('AND tatl.task_id = fb.task_id ', l_ta_req, r);
320 optional_row('AND tatl.language = userenv(''LANG'') ', l_ta_req, r);
321 optional_row('AND ud1tl.user_dim1_id = fb.user_dim1_id ', l_ud1_req, r);
322 optional_row('AND ud1tl.language = userenv(''LANG'') ', l_ud1_req, r);
323 optional_row('AND ud2tl.user_dim2_id = fb.user_dim2_id ', l_ud2_req, r);
324 optional_row('AND ud2tl.language = userenv(''LANG'') ', l_ud2_req, r);
325 optional_row('AND ud3tl.user_dim3_id = fb.user_dim3_id ', l_ud3_req, r);
326 optional_row('AND ud3tl.language = userenv(''LANG'') ', l_ud3_req, r);
327 optional_row('AND ud4tl.user_dim4_id = fb.user_dim4_id ', l_ud4_req, r);
328 optional_row('AND ud4tl.language = userenv(''LANG'') ', l_ud4_req, r);
329 optional_row('AND ud5tl.user_dim5_id = fb.user_dim5_id ', l_ud5_req, r);
330 optional_row('AND ud5tl.language = userenv(''LANG'') ', l_ud5_req, r);
331 optional_row('AND ud6tl.user_dim6_id = fb.user_dim6_id ', l_ud6_req, r);
332 optional_row('AND ud6tl.language = userenv(''LANG'') ', l_ud6_req, r);
333 optional_row('AND ud7tl.user_dim7_id = fb.user_dim7_id ', l_ud7_req, r);
334 optional_row('AND ud7tl.language = userenv(''LANG'') ', l_ud7_req, r);
335 optional_row('AND ud8tl.user_dim8_id = fb.user_dim8_id ', l_ud8_req, r);
336 optional_row('AND ud8tl.language = userenv(''LANG'') ', l_ud8_req, r);
337 optional_row('AND ud9tl.user_dim9_id = fb.user_dim9_id ', l_ud9_req, r);
338 optional_row('AND ud9tl.language = userenv(''LANG'') ', l_ud9_req, r);
339 optional_row('AND ud10tl.user_dim10_id = fb.user_dim10_id ', l_ud10_req, r);
340 optional_row('AND ud10tl.language = userenv(''LANG'') ', l_ud10_req, r);
341 ad_ddl.build_statement('AND litl.line_item_id = fb.line_item_id ', r); r:=r+1;
342 ad_ddl.build_statement('AND litl.language = userenv(''LANG'') ', r); r:=r+1;
343 ad_ddl.build_statement('AND fb.currency_type_code IN (''ENTERED'', ''TRANSLATED'') ', r);
344
345 ad_ddl.do_array_ddl(appl, 'APPS', ad_ddl.create_view, 1, r, 'GCS_DYN_TB_V');
346
347 module_log_write(module, g_module_success);
348 EXCEPTION
349 WHEN GCS_DYN_TB_APPLSYS_NOT_FOUND THEN
350 FND_MESSAGE.SET_NAME('GCS', 'GCS_APPLSYS_NOT_FOUND');
351 l_error_text := FND_MESSAGE.get;
352 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, l_error_text);
353 x_errbuf := l_error_text;
354 x_retcode := '2';
355 module_log_write(module, g_module_failure);
356 WHEN OTHERS THEN
357 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
358 x_errbuf := SQLERRM;
359 x_retcode := '2';
360 module_log_write(module, g_module_failure);
361 END Create_View;
362
363 END GCS_DYN_TB_VIEW_PKG;