eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GCS_CREATE_DYN_INDEX_PKG

Source


1 PACKAGE BODY GCS_CREATE_DYN_INDEX_PKG AS
2 /* $Header: gcsdynidxb.pls 120.11 2007/06/28 12:22:21 vkosuri noship $ */
3 
4 
5   -- Private procedure
6 
7   PROCEDURE	Generate_Data_Sub_Index(l_column_list OUT NOCOPY VARCHAR2) IS
8 
9     CURSOR v_active_dims IS
10 			SELECT  DECODE(ftcp.column_name, 'INTERCOMPANY_ID', 'INTERCOMPANY_DISPLAY_CODE',
11       				                               dtc.column_name) active_columns
12 			FROM    fem_tab_column_prop ftcp,
13             			fem_tab_columns_b   ftcb,
14             			fem_xdim_dimensions fxd,
15             			dba_tab_columns     dtc
16 			WHERE   ftcp.table_name             =   'FEM_BALANCES'
17 			AND     ftcp.column_name            =   ftcb.column_name
18 			AND     ftcp.column_property_code   =   'PROCESSING_KEY'
19 			AND     ftcb.table_name             =   ftcp.table_name
20 			AND     ftcb.dimension_id           =   fxd.dimension_id
21 			AND     dtc.column_name             =   fxd.member_display_code_col
22 			AND     dtc.table_name              =   'GCS_BAL_INTERFACE_T'
23 			AND     dtc.owner                   =   'GCS';
24 
25   BEGIN
26     l_column_list		:=	'LOAD_ID ';
27     FOR c_active_dims in v_active_dims LOOP
28       l_column_list	:=	l_column_list 	||	' , ' || c_active_dims.active_columns;
29     END LOOP;
30   END;
31 
32   PROCEDURE Drop_Index ( x_errbuf	OUT NOCOPY VARCHAR2,
33 			 x_retcode	OUT NOCOPY VARCHAR2,
34                          index_name     VARCHAR2)IS
35     body                VARCHAR2(5000);
36     NO_INDEX            exception;
37     PRAGMA              EXCEPTION_INIT (NO_INDEX, -1418);
38   BEGIN
39     body := 'DROP INDEX '||index_name;
40     ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', ad_ddl.drop_index, body, index_name);
41 
42   EXCEPTION
43     WHEN NO_INDEX THEN
44       fnd_file.put_line(fnd_file.log, 'Index to be dropped not found');
45     WHEN OTHERS THEN
46       x_errbuf := substr( SQLERRM, 1, 2000);
47       x_retcode := '2';
48   END Drop_Index;
49 
50   FUNCTION generate_epb_index RETURN VARCHAR2 IS
51 
52    l_data_table     VARCHAR2(30);
53    l_index          VARCHAR2(30);
54    len              NUMBER;
55    body             VARCHAR2(5000);
56    rowcount         NUMBER;
57    l_table_owner    VARCHAR2(30);
58 
59    CURSOR epb_dims IS
60       SELECT epb_column colname
61       FROM GCS_EPB_DIM_MAPS
62       WHERE enabled_flag = 'Y';
63 
64    CURSOR c_index_names (p_data_table VARCHAR2,
65                          p_index_owner VARCHAR2) IS
66       SELECT owner,
67              index_name
68       FROM   dba_indexes
69       WHERE  table_name  = p_data_table
70       AND    owner       = p_index_owner;
71 
72   BEGIN
73 
74       --Bugfix 5498824: Adding additional logging information
75       fnd_file.put_line(fnd_file.log, 'Beginning of Generate EPB Index');
76 
77       SELECT epb_table_name
78       INTO l_data_table
79       FROM gcs_system_options;
80 
81       SELECT oracle_username
82       INTO   l_table_owner
83       FROM   fnd_oracle_userid
84       WHERE  oracle_id     =    274;
85 
86       fnd_file.put_line(fnd_file.log, 'Data will be written to table: ' || l_data_table);
87 
88       --Bugfix 5498824: Remove the index initialization and search in data directionary
89       --l_index := l_data_table || '_U1';
90 
91       --Bugfix 5498824: No longer check if processing key has been set. Check if rows exist in the data table
92       /*
93       SELECT  count(*)
94       INTO rowcount
95       FROM FEM_TAB_COLUMN_PROP
96       WHERE table_name = l_data_table
97       AND column_property_code = 'PROCESSING_KEY';
98       */
99       EXECUTE IMMEDIATE 'select count(1) from ' || l_data_table INTO rowcount;
100 
101       fnd_file.put_line(fnd_file.log, 'Number of rows in data table: ' || rowcount);
102 
103       --Bugfix 5498824- No longer check if processing key has been setup. If there are no rows in the table always re-initialize
104       IF (rowcount = 0) THEN
105 
106         --Bugfix 5498824: Clean up formatting and drop all indices in data directionary
107         -- generate the processing key
108         DELETE FROM fem_tab_column_prop
109         WHERE       table_name = l_data_table
110         AND         column_property_code = 'PROCESSING_KEY';
111 
112         INSERT INTO FEM_TAB_COLUMN_PROP
113         ( table_name,
114           column_name,
115           column_property_code,
116           creation_date, created_by,
117           last_updated_by,
118           last_update_date,
119           last_update_login,
120           object_version_number)
121         (
122           SELECT l_data_table,
123                  epb_column,
124                  'PROCESSING_KEY',
125                  sysdate,
126                  FND_GLOBAL.user_id,
127                  FND_GLOBAL.user_id,
128                  sysdate,
129                  FND_GLOBAL.login_id,
130                  1
131           FROM   GCS_EPB_DIM_MAPS
132           WHERE  enabled_flag = 'Y'
133          );
134 
135          fnd_file.put_line(fnd_file.log, 'Starting to Drop Indices');
136          --Bugfix 5499824:  Using cursor to determine indices to drop
137          FOR v_indices in c_index_names(l_data_table,
138                                         l_table_owner) LOOP
139 
140            fnd_file.put_line(fnd_file.log, 'Starting to drop index: ' || v_indices.index_name);
141 
142            ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
143                          v_indices.owner,
144                          ad_ddl.drop_index,
145                          'DROP INDEX ' || v_indices.index_name,
146                          v_indices.index_name);
147 
148            fnd_file.put_line(fnd_file.log, 'Completed dropping of index');
149 
150          END LOOP;
151          fnd_file.put_line(fnd_file.log, 'Completed Dropping Indices');
152          -- generate the index
153          body:= ' CREATE UNIQUE INDEX ' || l_data_table || '_P ON ' || l_data_table || ' ( ';
154          FOR active_dims IN epb_dims LOOP
155            body := body || active_dims.colname || ', ';
156          END LOOP;
157          len := length(body);
158          body := substr(body, 1, len-2);
159          body:= body || ' )';
160          ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, l_table_owner, ad_ddl.create_index, body, l_data_table || '_P');
161       ELSE
162          fnd_file.put_line(fnd_file.log, '<<<<<<<<<<Warning during EPB Code Generation>>>>>>>>>>>>>>>>>>>>>');
163          fnd_file.put_line(fnd_file.log, 'The indices and processing key on ' || l_data_table || ' were not regenerated since data already exists in the table.');
164          fnd_file.put_line(fnd_file.log, 'If you are ok with the analytical reporting setup, please ignore this warning. Otherwise, please undo the data in the tables');
165          fnd_file.put_line(fnd_file.log, 'and re-run Module Initialization.');
166          fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<End of Warning>>>>>>>>>>>>>>>>>>>>>>>>>>>>');
167          RETURN 'WARNING';
168       END IF;
169 
170     RETURN 'SUCCESS';
171 
172     EXCEPTION
173     WHEN OTHERS THEN
174       fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<Error During EPB Code Generation>>>>>>>>>>>>>>>>>>>>>>>>>>>>');
175       fnd_file.put_line(fnd_file.log, SQLERRM);
176       fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<End of Error Details>>>>>>>>>>>>>>>>>>>>');
177       RETURN 'WARNING';
178   END;
179 
180   PROCEDURE Create_Index(	x_errbuf	OUT NOCOPY VARCHAR2,
181 				x_retcode	OUT NOCOPY VARCHAR2) IS
182     collist             VARCHAR2(2000);
183     index_list          VARCHAR2(4000);
184     body                VARCHAR2(5000);
185     retwebadi           VARCHAR2(100);
186     -- subscription_guid RAW := null;
187     event             wf_event_t := null;
188     l_ret_status       BOOLEAN;
189     l_sys_option       NUMBER;
190     l_epb_table        VARCHAR2(30);
191 
192      -- Store the log level
193      runtimeLogLevel     NUMBER := FND_LOG.g_current_runtime_level;
194      procedureLogLevel   CONSTANT NUMBER := FND_LOG.level_procedure;
195      statementLogLevel   CONSTANT NUMBER := FND_LOG.level_statement;
196 
197      -- Bugfix 5498824: Status of EPB Code Generation
198      l_status_code       VARCHAR2(30);
199    BEGIN
200 
201     fnd_file.put_line(fnd_file.log, 'Starting to Drop Indices');
202     -- Drop indexes before we recreate them
203     GCS_CREATE_DYN_INDEX_PKG.Drop_Index(x_errbuf, x_retcode, 'GCS_INTERCO_ELM_TRX_U1');
204     GCS_CREATE_DYN_INDEX_PKG.Drop_Index(x_errbuf, x_retcode, 'GCS_HISTORICAL_RATES_U1');
205     GCS_CREATE_DYN_INDEX_PKG.Drop_Index(x_errbuf, x_retcode, 'GCS_TRANSLATION_GT_U1');
206     GCS_CREATE_DYN_INDEX_PKG.Drop_Index(x_errbuf, x_retcode, 'GCS_ENTRY_LINES_U1');
207     GCS_CREATE_DYN_INDEX_PKG.Drop_Index(x_errbuf, x_retcode, 'GCS_AD_TRIAL_BALANCES_U1');
208     -- Bugfix 4281391 : Added dropping of index GCS_BAL_INTERFACE_T_U1
209     GCS_CREATE_DYN_INDEX_PKG.Drop_Index(x_errbuf, x_retcode, 'GCS_BAL_INTERFACE_T_U1');
210     fnd_file.put_line(fnd_file.log, 'Completed Dropping Indices');
211 
212     index_list := rtrim(GCS_DYNAMIC_UTIL_PKG.index_col_list(collist), ', ');
213     IF index_list IS NOT NULL THEN
214       index_list := ', ' || index_list;
215     END IF;
216 
217     fnd_file.put_line(fnd_file.log, 'Starting to Create Indices');
218     fnd_file.put_line(fnd_file.log, 'Generating GCS_INTERCO_ELM_TRX_U1');
219     -- Recreate the indices
220     body:= ' CREATE UNIQUE INDEX GCS_INTERCO_ELM_TRX_U1 ON GCS_INTERCO_ELM_TRX (hierarchy_id, src_entity_id, target_entity_id,';
221     body:= body || 'cal_period_id, company_cost_center_org_id, intercompany_id, line_item_id';
222     body := body || index_list || ')';
223     ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', ad_ddl.create_index, body, 'GCS_INTERCO_ELM_TRX_U1');
224 
225     fnd_file.put_line(fnd_file.log, 'Generating GCS_HISTORICAL_RATES_U1');
226     body:= ' CREATE UNIQUE INDEX GCS_HISTORICAL_RATES_U1 ON GCS_HISTORICAL_RATES (hierarchy_id, entity_id, from_currency, to_currency, update_flag, ';
227     body:= body || 'cal_period_id, company_cost_center_org_id, intercompany_id, line_item_id';
228     body := body || index_list || ')';
229     ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', ad_ddl.create_index, body, 'GCS_HISTORICAL_RATES_U1');
230 
231     fnd_file.put_line(fnd_file.log, 'Generating GCS_TRANSLATION_GT_U1');
232     body:= ' CREATE UNIQUE INDEX GCS_TRANSLATION_GT_U1 ON GCS_TRANSLATION_GT (';
233     body:= body || 'company_cost_center_org_id, intercompany_id, line_item_id';
234     body := body || index_list || ')';
235     ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', ad_ddl.create_index, body, 'GCS_TRANSLATION_GT_U1');
236 
237     fnd_file.put_line(fnd_file.log, 'Generating GCS_ENTRY_LINES_U1');
238     body:= ' CREATE UNIQUE INDEX GCS_ENTRY_LINES_U1 ON GCS_ENTRY_LINES (entry_id, ';
239     body:= body || 'company_cost_center_org_id, intercompany_id, line_item_id';
240     --Bugfix 5532657: Added entry_line_number to the unique index
241     body := body || index_list || ', entry_line_number)';
242     ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', ad_ddl.create_index, body, 'GCS_ENTRY_LINES_U1');
243 
244     fnd_file.put_line(fnd_file.log, 'Generating GCS_AD_TRIAL_BALANCES_U1');
245     body:= ' CREATE UNIQUE INDEX GCS_AD_TRIAL_BALANCES_U1 ON GCS_AD_TRIAL_BALANCES (ad_transaction_id, trial_balance_seq, ';
246     body:= body || 'company_cost_center_org_id, intercompany_id, line_item_id';
247     body := body || index_list || ')';
248     ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', ad_ddl.create_index, body, 'GCS_AD_TRIAL_BALANCES_U1');
249 
250     fnd_file.put_line(fnd_file.log, 'Generating GCS_BAL_INTERFACE_T_U1');
251     Generate_Data_Sub_Index(collist);
252     body := ' CREATE UNIQUE INDEX GCS_BAL_INTERFACE_T_U1 ON GCS_BAL_INTERFACE_T ( ' || collist || ') ';
253     ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', ad_ddl.create_index, body, 'GCS_BAL_INTERFACE_T_U1');
254 
255     fnd_file.put_line(fnd_file.log, 'Completed Creation of Indices');
256 
257     fnd_file.put_line(fnd_file.log, 'Starting to Generate PL/SQL Packages');
258 
259     -- Check if system options is set
260     SELECT nvl(fch_global_vs_combo_id, -1)
261      INTO  l_sys_option
262      FROM gcs_system_options;
263 
264     IF l_sys_option <> -1 THEN
265       fnd_file.put_line(fnd_file.log, 'Generating Dimension Transfer');
266       GCS_BUILD_EPB_DIM_TR_PKG.build_epb_dimtr_pkg;
267     END IF;
268 
269     --Bugfix 5498824: Removing the reference to ln_item_hierarchy_obj_id
270     SELECT nvl(epb_table_name, 'INVALID')
271       INTO l_epb_table
272       FROM gcs_system_options;
273 
274     IF ( l_epb_table <> 'INVALID' ) THEN
275       fnd_file.put_line(fnd_file.log, 'Generating EPB/FCH Data Transfer');
276       GCS_BUILD_EPB_DATA_TR_PKG.build_epb_datatr_pkg;
277       fnd_file.put_line(fnd_file.log, 'Generating Index and Processing key for data table');
278       l_status_code := generate_epb_index;
279     ELSE
280       fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<Warning Message>>>>>>>>>>>>>>>>>>>>>>>>>>');
281       fnd_file.put_line(fnd_file.log, 'Please make sure you complete the analytical reporting step in Foundation prior to running Consolidation');
282       fnd_file.put_line(fnd_file.log, '<<<<<<<<<<<<<<<<<<<<<End of Warning>>>>>>>>>>>>>>>>>>>>>>>>>>');
283       l_status_code := 'WARNING';
284     END IF;
285 
286     fnd_file.put_line(fnd_file.log, 'Generating Aggregation');
287     GCS_AGGREGATION_DYN_BUILD_PKG.create_package;
288 
289     fnd_file.put_line(fnd_file.log, 'Generating Data Preparation');
290     GCS_DATA_PREP_PKG.create_process(x_errbuf, x_retcode);
291 
292     fnd_file.put_line(fnd_file.log, 'Generating Balances Processor');
293     GCS_BUILD_FEM_POSTING_PKG.create_package;
294 
295     fnd_file.put_line(fnd_file.log, 'Generating Intercompany');
296     GCS_INTERCO_DYN_BUILD_PKG.Interco_Create_Package(x_errbuf, x_retcode);
297 
298     fnd_file.put_line(fnd_file.log, 'Generating Period Initialization');
299     GCS_PERIOD_INIT_DYN_BUILD_PKG.create_package;
300 
301     fnd_file.put_line(fnd_file.log, 'Generating Balancing Routine');
302     GCS_TEMPLATES_PKG.create_dynamic_pkg(x_errbuf, x_retcode);
303 
304     -- Bugfix 5707630: Start
305     fnd_file.put_line(fnd_file.log, 'Generating Translation');
306     GCS_TRANS_HRATES_DYN_BUILD_PKG.create_package(x_errbuf, x_retcode);
307 
308     fnd_file.put_line(fnd_file.log, 'Generating Translation for Historical Rates');
309     GCS_TRANS_RE_DYN_BUILD_PKG.create_package(x_errbuf, x_retcode);
310     -- Bugfix 5707630: End
311 
312     fnd_file.put_line(fnd_file.log, 'Generating Translation for Retained Earnings');
313     GCS_TRANS_DYN_BUILD_PKG.create_package(x_errbuf, x_retcode);
314 
315     fnd_file.put_line(fnd_file.log, 'Generating Data Submission');
316     GCS_DATASUB_DYNAMIC_PKG.create_datasub_utility_pkg(x_errbuf, x_retcode);
317 
318     fnd_file.put_line(fnd_file.log, 'Generating XML Generation Package');
319     GCS_XML_DYNAMIC_PKG.create_xml_utility_pkg(x_errbuf, x_retcode);
320 
321     fnd_file.put_line(fnd_file.log, 'Manipulating XML Publisher Data Templates');
322     GCS_DATA_TEMPLATE_UTIL_PKG.gcs_replace_dt_proc(x_errbuf, x_retcode);
323 
324     fnd_file.put_line(fnd_file.log, 'Generating Web-ADI');
325     retwebadi := GCS_WEBADI_PKG.execute_event(NULL, event);
326 
327     --Bugfix 5190565: Calling Rules Processor Utility to Inser Data
328     fnd_file.put_line(fnd_file.log, 'Generating Rules Process Utility');
329     gcs_rp_util_build_pkg.create_rp_utility_pkg( p_errbuf  =>    x_errbuf , p_retcode =>   x_retcode);
330 
331     fnd_file.put_line(fnd_file.log, 'Completed Generation of PL/SQL Packages');
332 
333 
334     fnd_file.put_line(fnd_file.log, 'Generating Data Submission Trial Balance View');
335     GCS_DYN_TB_VIEW_PKG.create_view(x_errbuf, x_retcode);
336     fnd_file.put_line(fnd_file.log, 'Completed Generation of Views');
337 
338 
339     x_retcode := '0';
340 
341     IF (l_status_code = 'WARNING') THEN
342       x_retcode := '1';
343       l_ret_status := fnd_concurrent.set_completion_status(
344                                                 status => 'WARNING',
345                                                 message => 'NULL');
346     END IF;
347 
348 
349   EXCEPTION
350      WHEN OTHERS THEN
351       x_errbuf := substr( SQLERRM, 1, 2000);
352       x_retcode := '2';
353       fnd_file.put_line(fnd_file.log, 'Fatal Error Occurred : ' || SQLERRM);
354       l_ret_status         :=      fnd_concurrent.set_completion_status(
355                                                 status  =>      'ERROR',
356                                                 message =>      NULL);
357 
358   END Create_Index;
359 
360  -- Bug fix : 5289002
361   PROCEDURE submit_request (p_request_id OUT NOCOPY NUMBER) IS
362 
363   BEGIN
364 
365     IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL         <=      FND_LOG.LEVEL_PROCEDURE) THEN
366       FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.GCS_CREATE_DYN_INDEX_PKG.submit_request.begin', '<<Enter>>');
367     END IF;
368 
369     p_request_id :=     fnd_request.submit_request(
370                                         application     => 'GCS',
371                                         program         => 'GCS_DYNAMIC_INDEX',
372                                         sub_request     => FALSE);
373 
374     --Bugfix 4333250: Add commit in order for request to be submitted
375 
376     COMMIT;
377 
378     IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL         <=      FND_LOG.LEVEL_PROCEDURE) THEN
379       FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.GCS_CREATE_DYN_INDEX_PKG.submit_request', 'Submitted Request ID : '  || p_request_id);
380     END IF;
381 
382     IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL         <=      FND_LOG.LEVEL_PROCEDURE) THEN
383       FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.GCS_CREATE_DYN_INDEX_PKG.submit_request.end', '<<Exit>>');
384     END IF;
385 
386   END;
387 
388 END GCS_CREATE_DYN_INDEX_PKG;