[Home] [Help]
PACKAGE BODY: APPS.GCS_BUILD_EPB_DATA_TR_PKG
Source
1 PACKAGE BODY GCS_BUILD_EPB_DATA_TR_PKG AS
2 /* $Header: gcsepbtrdatab.pls 120.10 2007/12/13 11:56:54 cdesouza noship $ */
3 --
4 -- Package
5 -- build_epb_datatr_pkg
6 -- Purpose
7 -- Creates GCS_DYN_FEM_POSTING_PKG
8 -- History
9 -- 12-MAR-04 R Goyal Created
10 --
11 --
12
13 --
14 -- Public procedures
15 --
16 PROCEDURE build_epb_datatr_pkg IS
17
18 -- row number to be used in dynamically creating the package
19 r NUMBER := 1;
20 body VARCHAR2(10000);
21 tempbuf VARCHAR2(1000);
22 from_clause VARCHAR2(1000);
23 where_clause VARCHAR2(1000);
24 groupby_clause VARCHAR2(1000);
25
26 body_len NUMBER;
27 curr_pos NUMBER;
28 line_num NUMBER := 1;
29 err VARCHAR2(2000);
30
31 -- Store the data table
32 l_data_table VARCHAR2(30);
33
34 -- Store whether a dimension is used by GCS
35 l_cctr_req VARCHAR2(1);
36 l_interco_req VARCHAR2(1);
37 l_interco_tab VARCHAR2(30);
38 l_interco_col VARCHAR2(30);
39 l_interco_id VARCHAR2(30);
40 l_felm_req VARCHAR2(1);
41 l_felm_tab VARCHAr2(30);
42 l_felm_col VARCHAR2(30);
43 l_felm_id VARCHAR2(30);
44 l_prd_req VARCHAR2(1);
45 l_na_req VARCHAR2(1);
46 l_na_tab VARCHAR2(30);
47 l_na_col VARCHAR2(30);
48 l_na_id VARCHAR2(30);
49 l_chl_req VARCHAR2(1);
50 l_prj_req VARCHAR2(1);
51 l_cst_req VARCHAR2(1);
52 l_tsk_req VARCHAR2(1);
53 l_ud1_req VARCHAR2(1);
54 l_ud2_req VARCHAR2(1);
55 l_ud3_req VARCHAR2(1);
56 l_ud4_req VARCHAR2(1);
57 l_ud5_req VARCHAR2(1);
58 l_ud6_req VARCHAR2(1);
59 l_ud7_req VARCHAR2(1);
60 l_ud8_req VARCHAR2(1);
61 l_ud9_req VARCHAR2(1);
62 l_ud10_req VARCHAR2(1);
63 l_category_req VARCHAR2(1);
64 l_category_tab VARCHAR2(30);
65 l_category_col VARCHAR2(30);
66 l_category_id VARCHAR2(30);
67
68 BEGIN
69
70 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
71 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'GCS_BUILD_FEM_POSTING_PKG' || '.' || 'BUILD_EPB_DATATR_PKG',
72 GCS_UTILITY_PKG.g_module_enter || 'BUILD_EPB_DATATR_PKG' ||
73 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
74 END IF;
75 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter || 'BUILD_EPB_DATATR_PKG' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
76
77
78 -- Set the epb table name
79 SELECT epb_table_name
80 INTO l_data_table
81 FROM GCS_SYSTEM_OPTIONS;
82
83 -- Set the required flags
84 begin
85 SELECT enabled_flag, 'FEM_' || substr(epb_column,0, 10)
86 INTO l_felm_req, l_felm_tab
87 FROM GCS_EPB_DIM_MAPS
88 WHERE gcs_column = 'FINANCIAL_ELEM_ID';
89 exception
90 when no_data_found then
91 l_felm_req := 'N';
92 end;
93
94 --Bugfix 5308890: Comment out this portion of the code as no mapping is required for financial element
95 -- Set the table names, column names and column id's to be used in the main sql
96 /*
97 IF substr(l_felm_tab,14) <> '0' THEN
98 l_felm_tab := substr(l_felm_tab, 0, 13) || '_B';
99 l_felm_col := substr(l_felm_tab, 5, 9) || '_DISPLAY_CODE';
100 l_felm_id := substr(l_felm_tab, 5, 9) || '_ID';
101 ELSE
102 l_felm_tab := l_felm_tab || '_B';
103 l_felm_col := substr(l_felm_tab, 5, 10) || '_DISPLAY_CODE';
104 l_felm_id := substr(l_felm_tab, 5, 10) || '_ID';
105 END IF;
106 */
107
108 begin
109 SELECT enabled_flag
110 INTO l_cctr_req
111 FROM GCS_EPB_DIM_MAPS
112 WHERE gcs_column = 'COMPANY_COST_CENTER_ORG_ID';
113 exception
114 when no_data_found then
115 l_cctr_req := 'N';
116 end;
117
118 begin
119 SELECT enabled_flag, 'FEM_' || substr(epb_column,0, 10)
120 INTO l_interco_req, l_interco_tab
121 FROM GCS_EPB_DIM_MAPS
122 WHERE gcs_column = 'INTERCOMPANY_ID';
123 exception
124 when no_data_found then
125 l_interco_req := 'N';
126 end;
127
128 --Bugfix 5308890: Comment out this portion of the code as no mapping is required for intercompany
129 /*
130 IF substr(l_interco_tab,14) <> '0' THEN
131 l_interco_tab := substr(l_interco_tab, 0, 13) || '_B';
132 l_interco_col := substr(l_interco_tab, 5, 9) || '_DISPLAY_CODE';
133 l_interco_id := substr(l_interco_tab, 5, 9) || '_ID';
134 ELSE
135 l_interco_tab := l_interco_tab || '_B';
136 l_interco_col := substr(l_interco_tab, 5, 10) || '_DISPLAY_CODE';
137 l_interco_id := substr(l_interco_tab, 5, 10) || '_ID';
138 END IF;
139 */
140
141 begin
142 SELECT enabled_flag
143 INTO l_prd_req
144 FROM GCS_EPB_DIM_MAPS
145 WHERE gcs_column = 'PRODUCT_ID';
146 exception
147 when no_data_found then
148 l_prd_req := 'N' ;
149 end;
150
151 begin
152 SELECT enabled_flag, 'FEM_' || substr(epb_column,0, 10)
153 INTO l_na_req, l_na_tab
154 FROM GCS_EPB_DIM_MAPS
155 WHERE gcs_column = 'NATURAL_ACCOUNT_ID';
156 exception
157 when no_data_found then
158 l_na_req := 'N' ;
159 end;
160
161 --Bugfix 5308890: Comment out this portion of the code as mapping is not required for natural account
162 /*
163 IF substr(l_na_tab,14) <> '0' THEN
164 l_na_tab := substr(l_na_tab, 0, 13) || '_B';
165 l_na_col := substr(l_na_tab, 5, 9) || '_DISPLAY_CODE';
166 l_na_id := substr(l_na_tab, 5, 9) || '_ID';
167 ELSE
168 l_na_tab := l_na_tab || '_B';
169 l_na_col := substr(l_na_tab, 5, 10) || '_DISPLAY_CODE';
170 l_na_id := substr(l_na_tab, 5, 10) || '_ID';
171 END IF;
172 */
173
174 begin
175 SELECT enabled_flag
176 INTO l_chl_req
177 FROM GCS_EPB_DIM_MAPS
178 WHERE gcs_column = 'CHANNEL_ID';
179 exception
180 when no_data_found then
181 l_chl_req := 'N' ;
182 end;
183
184 begin
185 SELECT enabled_flag
186 INTO l_prj_req
187 FROM GCS_EPB_DIM_MAPS
188 WHERE gcs_column = 'PROJECT_ID';
189 exception
190 when no_data_found then
191 l_prj_req := 'N' ;
192 end;
193
194 begin
195 SELECT enabled_flag
196 INTO l_cst_req
197 FROM GCS_EPB_DIM_MAPS
198 WHERE gcs_column = 'CUSTOMER_ID';
199 exception
200 when no_data_found then
201 l_cst_req := 'N' ;
202 end;
203
204 begin
205 SELECT enabled_flag
206 INTO l_tsk_req
207 FROM GCS_EPB_DIM_MAPS
208 WHERE gcs_column = 'TASK_ID';
209 exception
210 when no_data_found then
211 l_tsk_req := 'N' ;
212 end;
213
214 begin
215 SELECT enabled_flag
216 INTO l_ud1_req
217 FROM GCS_EPB_DIM_MAPS
218 WHERE gcs_column = 'USER_DIM1_ID';
219 exception
220 when no_data_found then
221 l_ud1_req := 'N' ;
222 end;
223
224 begin
225 SELECT enabled_flag
226 INTO l_ud2_req
227 FROM GCS_EPB_DIM_MAPS
228 WHERE gcs_column = 'USER_DIM2_ID';
229 exception
230 when no_data_found then
231 l_ud2_req := 'N' ;
232 end;
233
234 begin
235 SELECT enabled_flag
236 INTO l_ud3_req
237 FROM GCS_EPB_DIM_MAPS
238 WHERE gcs_column = 'USER_DIM3_ID';
239 exception
240 when no_data_found then
241 l_ud3_req := 'N' ;
242 end;
243
244 begin
245 SELECT enabled_flag
246 INTO l_ud4_req
247 FROM GCS_EPB_DIM_MAPS
248 WHERE gcs_column = 'USER_DIM4_ID';
249 exception
250 when no_data_found then
251 l_ud4_req := 'N' ;
252 end;
253
254 begin
255 SELECT enabled_flag
256 INTO l_ud5_req
257 FROM GCS_EPB_DIM_MAPS
258 WHERE gcs_column = 'USER_DIM5_ID';
259 exception
260 when no_data_found then
261 l_ud5_req := 'N' ;
262 end;
263
264 begin
265 SELECT enabled_flag
266 INTO l_ud6_req
267 FROM GCS_EPB_DIM_MAPS
268 WHERE gcs_column = 'USER_DIM6_ID';
269 exception
270 when no_data_found then
271 l_ud6_req := 'N' ;
272 end;
273
274 begin
275 SELECT enabled_flag
276 INTO l_ud7_req
277 FROM GCS_EPB_DIM_MAPS
278 WHERE gcs_column = 'USER_DIM7_ID';
279 exception
280 when no_data_found then
281 l_ud7_req := 'N' ;
282 end;
283
284 begin
285 SELECT enabled_flag
286 INTO l_ud8_req
287 FROM GCS_EPB_DIM_MAPS
288 WHERE gcs_column = 'USER_DIM8_ID';
289 exception
290 when no_data_found then
291 l_ud8_req := 'N' ;
292 end;
293
294 begin
295 SELECT enabled_flag
296 INTO l_ud9_req
297 FROM GCS_EPB_DIM_MAPS
298 WHERE gcs_column = 'USER_DIM9_ID';
299 exception
300 when no_data_found then
301 l_ud9_req := 'N' ;
302 end;
303
304 begin
305 SELECT enabled_flag
306 INTO l_ud10_req
307 FROM GCS_EPB_DIM_MAPS
308 WHERE gcs_column = 'USER_DIM10_ID';
309 exception
310 when no_data_found then
311 l_ud10_req := 'N' ;
312 end;
313
314 begin
315 SELECT enabled_flag, 'FEM_' || substr(epb_column,0, 10)
316 INTO l_category_req, l_category_tab
317 FROM GCS_EPB_DIM_MAPS
318 --Bugfix 4291225: The column name is CREATED_BY_OBJECT_ID
319 WHERE gcs_column = 'CREATED_BY_OBJECT_ID';
320 exception
321 when no_data_found then
322 l_category_req := 'N' ;
323 end;
324
325 IF substr(l_category_tab,14) <> '0' THEN
326 l_category_tab := substr(l_category_tab, 0, 13) || '_B';
327 l_category_col := substr(l_category_tab, 5, 9) || '_DISPLAY_CODE';
328 l_category_id := substr(l_category_tab, 5, 9) || '_ID';
329 ELSE
330 l_category_tab := l_category_tab || '_B';
331 l_category_col := substr(l_category_tab, 5, 10) || '_DISPLAY_CODE';
332 l_category_id := substr(l_category_tab, 5, 10) || '_ID';
333 END IF;
334
335
336
337 -- Create the package body
338 body:=
339 'CREATE OR REPLACE PACKAGE BODY GCS_DYN_EPB_DATATR_PKG AS
340
341
342 /* $Header: gcsepbtrdatab.pls 120.10 2007/12/13 11:56:54 cdesouza noship $ */
343 -- Store the log level
344 runtimeLogLevel NUMBER := FND_LOG.g_current_runtime_level;
345 statementLogLevel CONSTANT NUMBER := FND_LOG.level_statement;
346 procedureLogLevel CONSTANT NUMBER := FND_LOG.level_procedure;
347 exceptionLogLevel CONSTANT NUMBER := FND_LOG.level_exception;
348 errorLogLevel CONSTANT NUMBER := FND_LOG.level_error;
349 unexpectedLogLevel CONSTANT NUMBER := FND_LOG.level_unexpected;
350
351 g_src_sys_code NUMBER := GCS_UTILITY_PKG.g_gcs_source_system_code;
352 -- bugfix 5569522: Added for FND logging.
353 g_api VARCHAR2(200) := ''gcs.plsql.GCS_DYN_EPB_DATATR_PKG'';
354
355 -- bugfix 5569522: Added p_analysis_cycle_id parameter for launching business
356 -- process.
357 PROCEDURE Gcs_Epb_Tr_Data (
358 errbuf OUT NOCOPY VARCHAR2,
359 retcode OUT NOCOPY VARCHAR2,
360 p_hierarchy_id NUMBER,
361 p_balance_type_code VARCHAR2,
362 p_cal_period_id NUMBER,
363 p_analysis_cycle_id NUMBER) IS
364
365 l_dataset_code NUMBER;
366 l_target_dataset_code NUMBER := -1;
367 l_ledger_id NUMBER;
368 l_object_id NUMBER;
369 l_object_def_id NUMBER;
370 l_ln_item_hier_id NUMBER;
371 l_ln_item_obj_id NUMBER;
372 l_top_curr VARCHAR2(15);
373 l_dataset_dsp_code VARCHAR2(150);
374
375 errcode NUMBER;
376 msgnum VARCHAR2(1000);
377 return_status VARCHAR2(10);
378
379 l_msg_count NUMBER;
380 l_msg_data VARCHAR2(2000);
381 l_return_status VARCHAR2(1);
382 l_exec_state VARCHAr2(30);
383 l_prev_req_id NUMBER;
384 l_ret_status BOOLEAN;
385
386 l_req_id NUMBER := FND_GLOBAL.conc_request_id;
387 l_login_id NUMBER := FND_GLOBAL.login_id;
388 l_user_id NUMBER := FND_GLOBAL.user_id;
389
390 l_end_date_attribute_id NUMBER := gcs_utility_pkg.g_dimension_attr_info(''CAL_PERIOD_ID-CAL_PERIOD_END_DATE'').attribute_id;
391 l_end_date_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info(''CAL_PERIOD_ID-CAL_PERIOD_END_DATE'').version_id;
392
393
394 l_ext_acct_type_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id;
395 l_ext_acct_type_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id;
396 l_basic_acct_type_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id;
397 l_basic_acct_type_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id;
398
399 l_end_date DATE;
400
401 module VARCHAR2(30) := ''GCS_EPB_TR_DATA'';
402
403 --Exception handlers: everything that can go wrong here
404 NO_DATASET_CREATED EXCEPTION;
405 DIM_TRANSFER_FAILED EXCEPTION;
406
407 --Bugfix 5526501: Added row count variable to store number of rows transferred
408 l_row_count NUMBER(15);
409
410 BEGIN
411
412 runtimeLogLevel := FND_LOG.g_current_runtime_level;
413
414 IF (procedureloglevel >= runtimeloglevel ) THEN
415 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.gcs_epb_data_tr_pkg.gcs_epb_tr_data.begin'' || GCS_UTILITY_PKG.g_module_enter, to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
416 END IF;
417 IF (statementloglevel >= runtimeloglevel ) THEN
418 FND_LOG.STRING(statementloglevel, ''gcs.plsql.gcs_epb_data_tr_pkg.gcs_epb_tr_data'', ''p_hierarchy_id = '' || to_char(p_hierarchy_id));
419 FND_LOG.STRING(statementloglevel, ''gcs.plsql.gcs_epb_data_tr_pkg.gcs_epb_tr_data'', ''p_balance_type_code = '' || p_balance_type_code);
420 FND_LOG.STRING(statementloglevel, ''gcs.plsql.gcs_epb_data_tr_pkg.gcs_epb_tr_data'', ''p_cal_period_id = '' || to_char(p_cal_period_id));
421 FND_LOG.STRING(statementloglevel, ''gcs.plsql.gcs_epb_data_tr_pkg.gcs_epb_tr_data'', ''p_analysis_cycle_id = '' || to_char(p_analysis_cycle_id));
422 END IF;
423
424 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter || ''Gcs_Epb_Tr_Data'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
425 FND_FILE.PUT_LINE(FND_FILE.LOG, '' p_hierarchy_id = '' || to_char(p_hierarchy_id));
426 FND_FILE.PUT_LINE(FND_FILE.LOG, '' p_balance_type = '' || p_balance_type_code );
427 FND_FILE.PUT_LINE(FND_FILE.LOG, '' p_cal_period_id = '' || to_char(p_cal_period_id) );
428 FND_FILE.PUT_LINE(FND_FILE.LOG, '' p_analysis_cycle_id = '' || to_char(p_analysis_cycle_id) );
429 ';
430
431 curr_pos := 1;
432 body_len := LENGTH(body);
436 r := r + 1;
433 WHILE curr_pos <= body_len LOOP
434 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
435 curr_pos := curr_pos + g_line_size;
437 END LOOP;
438
439 body:=
440 ' -- Call Dimension transfer program
441 GCS_DYN_EPB_DIMTR_PKG.Gcs_Epb_Tr_Dim(errbuf, retcode);
442
443 FND_FILE.PUT_LINE(FND_FILE.LOG, '' Call Dimension Transfer '');
444
445 IF (statementloglevel >= runtimeloglevel ) THEN
446 FND_LOG.STRING(statementloglevel, ''gcs.plsql.gcs_epb_data_tr_pkg.gcs_epb_tr_data'', ''Called Dimension Transfer '');
447 END IF;
448
449 IF retcode = ''0'' THEN
450 RAISE DIM_TRANSFER_FAILED;
451 END IF;
452
453 --Bugfix 5111721: Removed code to get the dataset code
454
455 -- get the top entity and currency
456 SELECT currency_code
457 INTO l_top_curr
458 FROM gcs_hierarchies_b hier,
459 gcs_entity_cons_attrs attr
460 WHERE hier.hierarchy_id = p_hierarchy_id
461 AND attr.hierarchy_id = hier.hierarchy_id
462 AND attr.entity_id = hier.top_entity_id;
463
464 --Bugfix 4655571: The top currency is no longer required since EPB should support reporting on currency as a dimension
465
466 FND_FILE.PUT_LINE(FND_FILE.LOG, '' Top Currency = '' || l_top_curr );
467
468 -- get line item hierarchy id
469 SELECT ln_item_hierarchy_obj_id
470 INTO l_ln_item_obj_id
471 FROM GCS_SYSTEM_OPTIONS;
472
473
474 --Bugfix 5111721: Removed the concatenation of PTD with dataset code
475 --Also modified the select statement
476
477 begin
478 SELECT dataset_code
479 INTO l_dataset_code
480 FROM gcs_dataset_codes
481 WHERE hierarchy_id = p_hierarchy_id
482 AND balance_type_code = p_balance_type_code;
483
484 SELECT dataset_code
485 INTO l_target_dataset_code
486 FROM gcs_dataset_codes
487 WHERE hierarchy_id = p_hierarchy_id
488 AND balance_type_code = ''ANALYZE_'' || p_balance_type_code;
489 exception
490 WHEN NO_DATA_FOUND THEN
491 l_target_dataset_code := -1;
492 end;
493
494 /*
495 -- If dataset for EPB does not exist, create a the target dataset
496 IF (l_target_dataset_code = -1) THEN
497 FEM_DIMENSION_UTIL_PKG.new_dataset(
498 p_display_code => l_dataset_dsp_code,
499 p_dataset_name => l_dataset_dsp_code,
500 p_bal_type_cd => ''ACTUAL'',
501 p_api_version => 1,
502 P_INIT_MSG_LIST => ''F'',
503 P_COMMIT => ''F'',
504 P_ENCODED => ''F'',
505 p_source_cd => g_src_sys_code,
506 p_pft_w_flg => ''Y'',
507 p_prod_flg => ''Y'',
508 p_budget_id => NULL,
509 p_enc_type_id => NULL,
510 p_ver_name => ''Default'',
511 p_ver_disp_cd => ''Default'',
512 p_dataset_desc => l_dataset_dsp_code,
513 x_msg_count => errcode,
514 x_msg_data => msgnum,
515 x_return_status => return_status);
516 SELECT dataset_code
517 INTO l_target_dataset_code
518 FROM FEM_DATASETS_B
519 WHERE DATASET_DISPLAY_CODE = l_dataset_dsp_code;
520 END IF;
521 */
522
523 IF l_target_dataset_code = -1 THEN
524 RAISE NO_DATASET_CREATED;
525 END IF;
526
527 -- get ledger_id
528 SELECT fem_ledger_id
529 INTO l_ledger_id
530 FROM GCS_HIERARCHIES_B
531 WHERE hierarchy_id = p_hierarchy_id;
532
533 -- Get the end date of the period
534 SELECT date_assign_value
535 INTO l_end_date
536 FROM fem_cal_periods_attr
537 WHERE cal_period_id = p_cal_period_id
538 AND attribute_id = l_end_date_attribute_id
539 AND version_id = l_end_date_version_id;
540
541 -- Get the Line Item hierarchy id based on the object id
542 -- Bugfix: 4655571: Commenting out selection of the hierarchy since EPBv2 supports Hierarchial Total
543 -- SELECT object_definition_id
544 -- INTO l_ln_item_hier_id
545 -- FROM FEM_OBJECT_DEFINITION_B
546 -- WHERE object_id = l_ln_item_obj_id
547 -- AND l_end_date BETWEEN effective_start_date and effective_end_date;
548
549 FND_FILE.PUT_LINE(FND_FILE.LOG, '' cal_period_id = '' || to_char(p_cal_period_id));
550
551 -- Get object_id
552 SELECT associated_object_id
553 INTO l_object_id
554 FROM GCS_CATEGORIES_B
555 WHERE category_code = ''AGGREGATION'';
556 ';
557 curr_pos := 1;
558 body_len := LENGTH(body);
559 WHILE curr_pos <= body_len LOOP
563 END LOOP;
560 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
561 curr_pos := curr_pos + g_line_size;
562 r := r + 1;
564
565 body:=
566 '-- Get object definition id
567 SELECT object_definition_id
568 INTO l_object_def_id
569 FROM fem_object_definition_b
570 WHERE object_id = l_object_id;
571
572 -- Delete data from FEM_DATA11 before inserting
573 -- Bugfix 4286024 : Added table name dynamically
574 DELETE FROM ' || l_data_table || '
575 WHERE dataset_code = l_target_dataset_code
576 AND cal_period_id = p_cal_period_id;
577 ';
578 curr_pos := 1;
579 body_len := LENGTH(body);
580 WHILE curr_pos <= body_len LOOP
581 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
582 curr_pos := curr_pos + g_line_size;
583 r := r + 1;
584 END LOOP;
585
586 body:='
587 INSERT INTO ' || l_data_table;
588
589 tempbuf :=
590 ' (
591 DATASET_CODE,
592 CAL_PERIOD_ID,
593 SOURCE_SYSTEM_CODE,
594 LEDGER_ID,
595 CURRENCY_CODE,
596 LINE_ITEM_ID,
597 ENTITY_ID,
598 CREATED_BY_OBJECT_ID,
599 ';
600
601 body := body || tempbuf;
602
603 IF (l_felm_req = 'Y') THEN
604 --Bugfix 5308890: Removing the variable assignment and hard-coding FINANCIAL_ELEM_ID
605 body := body || 'FINANCIAL_ELEM_ID ,' ;
606 END IF;
607 IF (l_prd_req = 'Y') THEN
608 body := body || 'PRODUCT_ID, ';
609 END IF;
610 IF (l_cctr_req = 'Y') THEN
611 body := body || 'COMPANY_COST_CENTER_ORG_ID, ';
612 END IF;
613 IF (l_interco_req = 'Y') THEN
614 --Bugfix 5308890: Removing the variable assignment and hard-coding INTERCOMPANY_ID
615 body := body || 'INTERCOMPANY_ID ,' ;
616 END IF;
617 IF (l_na_req = 'Y') THEN
618 --Bugfix 5308890: Removing the variable assignment and hard-coding NATURAL_ACCOUNT_ID
619 body := body || 'NATURAL_ACCOUNT_ID ,' ;
620 END IF;
621 IF (l_chl_req = 'Y') THEN
622 body := body || 'CHANNEL_ID, ';
623 END IF;
624 IF (l_prj_req = 'Y') THEN
625 body := body || 'PROJECT_ID, ';
626 END IF;
627 IF (l_cst_req = 'Y') THEN
628 body := body || 'CUSTOMER_ID, ';
629 END IF;
630 IF (l_tsk_req = 'Y') THEN
631 body := body || 'TASK_ID, ';
632 END IF;
633 IF (l_ud1_req = 'Y') THEN
634 body := body || 'USER_DIM1_ID, ';
635 END IF;
636 IF (l_ud2_req = 'Y') THEN
637 body := body || 'USER_DIM2_ID, ';
638 END IF;
639 IF (l_ud3_req = 'Y') THEN
640 body := body || 'USER_DIM3_ID, ';
641 END IF;
642 IF (l_ud4_req = 'Y') THEN
643 body := body || 'USER_DIM4_ID, ';
644 END IF;
645 IF (l_ud5_req = 'Y') THEN
646 body := body || 'USER_DIM5_ID, ';
647 END IF;
648 IF (l_ud6_req = 'Y') THEN
649 body := body || 'USER_DIM6_ID, ';
650 END IF;
651 IF (l_ud7_req = 'Y') THEN
652 body := body || 'USER_DIM7_ID, ';
653 END IF;
654 IF (l_ud8_req = 'Y') THEN
655 body := body || 'USER_DIM8_ID, ';
656 END IF;
657 IF (l_ud9_req = 'Y') THEN
658 body := body || 'USER_DIM9_ID, ';
662 END IF;
659 END IF;
660 IF (l_ud10_req = 'Y') THEN
661 body := body || 'USER_DIM10_ID, ';
663 IF (l_category_req = 'Y') THEN
664 body := body || l_category_id || ',' ;
665 END IF;
666
667 tempbuf :=
668 '
669 CREATED_BY_REQUEST_ID ,
670 LAST_UPDATED_BY_REQUEST_ID,
671 LAST_UPDATED_BY_OBJECT_ID,
672 NUMERIC_MEASURE)
673 SELECT
674 l_target_dataset_code,
675 p_cal_period_id,
676 g_src_sys_code,
677 LEDGER_ID,
678 CURRENCY_CODE,
679 LINE_ITEM_ID,
680 ENTITY_ID,
681 ';
682
683 body := body || tempbuf;
684
685 IF ( l_category_req = 'Y') THEN
686 body := body || 'CREATED_BY_OBJECT_ID, ';
687 ELSE
688 body := body || 'max(CREATED_BY_OBJECT_ID), ';
689 END IF;
690 IF (l_felm_req = 'Y') THEN
691 --Bugfix 5308890: Removing variable assignment and hard-coding column name
692 body := body || 'FINANCIAL_ELEM_ID ,' ;
693 END IF;
694 IF (l_prd_req = 'Y') THEN
695 body := body || 'PRODUCT_ID, ';
696 END IF;
697 IF (l_cctr_req = 'Y') THEN
698 body := body || 'FB.COMPANY_COST_CENTER_ORG_ID, ';
699 END IF;
700 IF (l_interco_req = 'Y') THEN
701 --Bugfix 5308890: Removing variable assignment and hard-coding column name
702 body := body || 'INTERCOMPANY_ID , ' ;
703 END IF;
704 IF (l_na_req = 'Y') THEN
705 --Bugfix 5308890: Removing variable assignment and hard-coding column_name
706 body := body || 'NATURAL_ACCOUNT_ID, ' ;
707 END IF;
708 IF (l_chl_req = 'Y') THEN
709 body := body || 'CHANNEL_ID, ';
710 END IF;
711 IF (l_prj_req = 'Y') THEN
712 body := body || 'PROJECT_ID, ';
713 END IF;
714 IF (l_cst_req = 'Y') THEN
715 body := body || 'CUSTOMER_ID, ';
716 END IF;
717 IF (l_tsk_req = 'Y') THEN
718 body := body || 'TASK_ID, ';
719 END IF;
720 IF (l_ud1_req = 'Y') THEN
721 body := body || 'USER_DIM1_ID, ';
722 END IF;
723 IF (l_ud2_req = 'Y') THEN
724 body := body || 'USER_DIM2_ID, ';
725 END IF;
726 IF (l_ud3_req = 'Y') THEN
727 body := body || 'USER_DIM3_ID, ';
728 END IF;
729 IF (l_ud4_req = 'Y') THEN
730 body := body || 'USER_DIM4_ID, ';
731 END IF;
732 IF (l_ud5_req = 'Y') THEN
733 body := body || 'USER_DIM5_ID, ';
734 END IF;
735 IF (l_ud6_req = 'Y') THEN
736 body := body || 'USER_DIM6_ID, ';
737 END IF;
738 IF (l_ud7_req = 'Y') THEN
739 body := body || 'USER_DIM7_ID, ';
740 END IF;
741 IF (l_ud8_req = 'Y') THEN
742 body := body || 'USER_DIM8_ID, ';
743 END IF;
744 IF (l_ud9_req = 'Y') THEN
745 body := body || 'USER_DIM9_ID, ';
746 END IF;
747 IF (l_ud10_req = 'Y') THEN
748 body := body || 'USER_DIM10_ID, ';
749 END IF;
750 IF (l_category_req = 'Y') THEN
751 body := body || 'CATDIM.' || l_category_id || ',' ;
752 END IF;
753
754 tempbuf:=
755 '
756 max(CREATED_BY_REQUEST_ID) ,
757 max(LAST_UPDATED_BY_REQUEST_ID),
758 max(LAST_UPDATED_BY_OBJECT_ID),
759 sum(xtd_balance_e)
760 FROM FEM_BALANCES FB ';
761
762 body := body || tempbuf;
763
764 where_clause := '
765 -- Bugfix 4655571: Modifying the where clause to remove the currency code condition since EPBv2 should support multiple currencies
766 WHERE /* FB.currency_code = l_top_curr AND */ FB.source_system_code = g_src_sys_code AND ';
767
768 IF ( l_category_req = 'Y') THEN
769 from_clause := from_clause || ',' || l_category_tab || ' CATDIM, GCS_CATEGORIES_B CATB';
770 where_clause := where_clause || 'CATDIM.' || l_category_col || '= CATB.category_code AND FB.created_by_object_id = CATB.associated_object_id AND ';
771 END IF;
772
773 --Bugfix 5308890: The next three where clauses are no longer required since EPB supports those dimensions natively now
774 /*
775 IF ( l_interco_req = 'Y') THEN
776 from_clause := from_clause || ',' || l_interco_tab || ' INTERCODIM, FEM_CCTR_ORGS_B ORG';
777 where_clause := where_clause || 'INTERCODIM.' || l_interco_col || '= ORG.cctr_org_display_code AND FB.intercompany_id = ORG.company_cost_center_org_id AND ';
778 END IF;
779
780 IF ( l_felm_req = 'Y') THEN
781 from_clause := from_clause || ',' || l_felm_tab || ' FELMDIM, FEM_FIN_ELEMS_B FE';
782 where_clause := where_clause || 'FELMDIM.' || l_felm_col || '= FE.financial_elem_display_code AND FB.financial_elem_id = FE.financial_elem_id AND ';
783 END IF;
784
785 IF ( l_na_req = 'Y') THEN
786 from_clause := from_clause || ',' || l_na_tab || ' NADIM, FEM_NAT_ACCTS_B ACCT';
787 where_clause := where_clause || 'NADIM.' || l_na_col || '= ACCT.natural_account_display_code AND FB.natural_account_id = ACCT.natural_account_id AND ';
788 END IF;
789 */
790
791 where_clause := where_clause || 'FB.dataset_code = l_dataset_code AND FB.cal_period_id = p_cal_period_id ';
792
793 body := body || from_clause;
794
795 body := body || where_clause;
796
797 groupby_clause := '
798 GROUP BY FB.currency_code, FB.entity_id, FB.line_item_id, FB.ledger_id ';
799
800 --Bugfix 5308890: Remove variable assignment and hard-code column name
801 IF (l_felm_req = 'Y') THEN
802 groupby_clause := groupby_clause || ', FINANCIAL_ELEM_ID' ;
803 END IF;
804 IF (l_category_req = 'Y') THEN
808 groupby_clause := groupby_clause || ', PRODUCT_ID' ;
805 groupby_clause := groupby_clause || ', FB.created_by_object_id' ;
806 END IF;
807 IF (l_prd_req = 'Y') THEN
809 END IF;
810 IF (l_cctr_req = 'Y') THEN
811 groupby_clause := groupby_clause || ', FB.COMPANY_COST_CENTER_ORG_ID' ;
812 END IF;
813 --Bugfix 5308890: Remove variable assignment and hard-code column name
814 IF (l_interco_req = 'Y') THEN
815 groupby_clause := groupby_clause || ', INTERCOMPANY_ID' ;
816 END IF;
817 --Bugfix 5308890: Remove variable assignment and hard-code column name
818 IF (l_na_req = 'Y') THEN
819 groupby_clause := groupby_clause || ', NATURAL_ACCOUNT_ID' ;
820 END IF;
821 IF (l_chl_req = 'Y') THEN
822 groupby_clause := groupby_clause || ', CHANNEL_ID ' ;
823 END IF;
824 IF (l_prj_req = 'Y') THEN
825 groupby_clause := groupby_clause || ', PROJECT_ID ';
826 END IF;
827 IF (l_cst_req = 'Y') THEN
828 groupby_clause := groupby_clause || ', CUSTOMER_ID ';
829 END IF;
830 IF (l_tsk_req = 'Y') THEN
831 groupby_clause := groupby_clause || ', TASK_ID ';
832 END IF;
833 IF (l_ud1_req = 'Y') THEN
834 groupby_clause := groupby_clause || ', USER_DIM1_ID ';
835 END IF;
836 IF (l_ud2_req = 'Y') THEN
837 groupby_clause := groupby_clause || ', USER_DIM2_ID ';
838 END IF;
839 IF (l_ud3_req = 'Y') THEN
840 groupby_clause := groupby_clause || ', USER_DIM3_ID ';
841 END IF;
842 IF (l_ud4_req = 'Y') THEN
843 groupby_clause := groupby_clause || ', USER_DIM4_ID ';
844 END IF;
845 IF (l_ud5_req = 'Y') THEN
846 groupby_clause := groupby_clause || ', USER_DIM5_ID ';
847 END IF;
848 IF (l_ud6_req = 'Y') THEN
849 groupby_clause := groupby_clause || ', USER_DIM6_ID ';
850 END IF;
851 IF (l_ud7_req = 'Y') THEN
852 groupby_clause := groupby_clause || ', USER_DIM7_ID ';
853 END IF;
854 IF (l_ud8_req = 'Y') THEN
855 groupby_clause := groupby_clause || ', USER_DIM8_ID ';
856 END IF;
857 IF (l_ud9_req = 'Y') THEN
858 groupby_clause := groupby_clause || ', USER_DIM9_ID ';
859 END IF;
860 IF (l_ud10_req = 'Y') THEN
861 groupby_clause := groupby_clause || ', USER_DIM10_ID ';
862 END IF;
863 IF (l_category_req = 'Y') THEN
864 groupby_clause := groupby_clause || ', CATDIM.' || l_category_id ;
865 END IF;
866
867
868 body := body || groupby_clause || ';' ;
869
870
871 curr_pos := 1;
872 body_len := LENGTH(body);
873 WHILE curr_pos <= body_len LOOP
874 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
875 curr_pos := curr_pos + g_line_size;
876 r := r + 1;
877 END LOOP;
878
879 body:=
880 '
881 l_row_count := SQL%ROWCOUNT;
882
883 FND_FILE.PUT_LINE(FND_FILE.LOG, '' Rows inserted into data table = '' || l_row_count);
884
885
886 IF (l_row_count <> 0) THEN
887 -- Write to FEM_DATA_LOCATIONS
888 FEM_DIMENSION_UTIL_PKG.Register_Data_Location
889 (P_REQUEST_ID => -1,
890 P_OBJECT_ID => l_object_id,
891 --Bugfix 4286024: Fixed hardcoding of FEM_DATA11
892 P_TABLE_NAME => ''' || l_data_table || ''',
893 P_LEDGER_ID => l_ledger_id,
894 P_CAL_PER_ID => p_cal_period_id,
895 P_DATASET_CD => l_target_dataset_code,
896 P_SOURCE_CD => g_src_sys_code,
897 P_LOAD_STATUS => ''COMPLETE'');
898
899 --Bugfix 5526501: Removing all commented code for table registration
900 --Putting update statement within if..then statement
901 ';
902
903 curr_pos := 1;
904 body_len := LENGTH(body);
905 WHILE curr_pos <= body_len LOOP
906 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
907 curr_pos := curr_pos + g_line_size;
908 r := r + 1;
909 END LOOP;
910
911 body := '--Bugfix 4655571: Removing code block to aggregate up a hierarchy since it is no longer useful with EPBv2';
912
913 curr_pos := 1;
914 body_len := LENGTH(body);
915 WHILE curr_pos <= body_len LOOP
916 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
917 curr_pos := curr_pos + g_line_size;
918 r := r + 1;
919 END LOOP;
920
921
922 body:=
923 '
924 UPDATE ' || l_data_table || ' data_table
925 SET data_table.numeric_measure = numeric_measure * -1
926 WHERE data_table.dataset_code = l_target_dataset_code
927 AND data_table.cal_period_id = p_cal_period_id
928 AND EXISTS ( SELECT ''X''
929 FROM fem_ln_items_attr flia,
930 fem_ext_acct_types_attr feata
931 WHERE data_table.line_item_id = flia.line_item_id
932 AND flia.attribute_id = l_ext_acct_type_attr_id
933 AND flia.version_id = l_ext_acct_type_version_id
934 AND feata.attribute_id = l_basic_acct_type_attr_id
935 AND feata.version_id = l_basic_acct_type_version_id
936 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
937 AND feata.dim_attribute_varchar_member IN (''LIABILITY'', ''EQUITY'', ''REVENUE''));
938 ELSE
939 --Bugfix 5526501: Zero rows are inserted so we should set the completion status to warning
943 fnd_file.put_line(fnd_file.log, ''<<<<<<<<<<<<<<<<<<<<<<End of Warning>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'');
940 fnd_file.put_line(fnd_file.log, ''<<<<<<<<<<<<<<<<<<<<<Beginning of Warning>>>>>>>>>>>>>>>>>>>>>>>>>>'');
941 fnd_file.put_line(fnd_file.log, ''No data was transferred to the data table'');
942 fnd_file.put_line(fnd_file.log, ''Please ensure the analytical report step in foundation was completed, and the consolidation process generated results.'');
944 l_ret_status := fnd_concurrent.set_completion_status(
945 status => ''WARNING'',
946 message => NULL);
947 END IF;
948
949
950 -- Bugfix 5569522: If the user had selected a business process, call the
951 -- submit_business_process procedure to launch the business process.
952
953 IF (p_analysis_cycle_id <> -1) THEN
954 submit_business_process
955 (
956 errbuf => errbuf,
957 retcode => retcode,
958 p_analysis_cycle_id => p_analysis_cycle_id,
959 p_cal_period_id => p_cal_period_id
960 );
961 END IF;
962
963
964
965 EXCEPTION
966 WHEN NO_DATASET_CREATED THEN
967 --An error msg is placed on the stack at the exception raise point
968 --A logString call is made at the exception raise point
969 FND_MESSAGE.set_name( ''GCS'', ''GCS_EPB_NO_DATASET'' );
970 errbuf := FND_MESSAGE.get;
971 retcode := ''0'';
972 IF (unexpectedloglevel >= runtimeloglevel ) THEN
973 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_EPB_DATA_TR_PKG.GCS_EPB_TR_DATA'', ''NO_DATASET_CREATED'');
974 END IF;
975 fnd_file.put_line(fnd_file.log, ''Fatal Error Occurred : '' || SQLERRM);
976 l_ret_status := fnd_concurrent.set_completion_status(
977 status => ''ERROR'',
978 message => NULL);
979 RAISE;
980
981 WHEN DIM_TRANSFER_FAILED THEN
982 --An error msg is placed on the stack at the exception raise point
983 --A logString call is made at the exception raise point
984 FND_MESSAGE.set_name( ''GCS'', ''GCS_EPB_FAIL_DIM_TRANSFER'' );
985 errbuf := FND_MESSAGE.get;
986 retcode := ''0'';
987 IF (unexpectedloglevel >= runtimeloglevel ) THEN
988 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_EPB_DATA_TR_PKG.GCS_EPB_TR_DATA'', ''DIM_TRANSFER_FAILED'');
989 END IF;
990 fnd_file.put_line(fnd_file.log, ''Fatal Error Occurred : '' || SQLERRM);
991 l_ret_status := fnd_concurrent.set_completion_status(
992 status => ''ERROR'',
993 message => NULL);
994 RAISE;
995
996 WHEN NO_DATA_FOUND THEN
997 IF (unexpectedloglevel >= runtimeloglevel ) THEN
998 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_EPB_DATA_TR_PKG.GCS_EPB_TR_DATA'', ''GCS_NO_DATA_FOUND'');
999 END IF;
1000 retcode := ''0'';
1001 errbuf := ''GCS_NO_DATA_FOUND'';
1002 fnd_file.put_line(fnd_file.log, ''Fatal Error Occurred : '' || SQLERRM);
1003 l_ret_status := fnd_concurrent.set_completion_status(
1004 status => ''ERROR'',
1005 message => NULL);
1006 RAISE NO_DATA_FOUND;
1007
1008 WHEN OTHERS THEN
1009 errbuf := substr( SQLERRM, 1, 2000);
1010 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1011 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_EPB_DATA_TR_PKG.GCS_EPB_TR_DATA'', errbuf);
1012 END IF;
1013 retcode := ''0'';
1014 fnd_file.put_line(fnd_file.log, ''Fatal Error Occurred : '' || SQLERRM);
1015 l_ret_status := fnd_concurrent.set_completion_status(
1016 status => ''ERROR'',
1017 message => NULL);
1018 RAISE;
1019
1020
1021 END Gcs_Epb_Tr_Data;';
1022
1023 -- bugfix 5646254: Since the text exceeded the max size of varchar2, the code was running
1024 -- into errors. Splitted the big String into two.
1025
1026 curr_pos := 1;
1027 body_len := LENGTH(body);
1028 WHILE curr_pos <= body_len LOOP
1029 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1030 curr_pos := curr_pos + g_line_size;
1031 r := r + 1;
1032 END LOOP;
1033
1034
1035 body:=
1036 '
1037 -- bugfix 5569522: If the user has selected to run the business process
1038 -- and has access to it, the business process is launched and then the
1039 -- Workflow Background Process is launched.
1040 PROCEDURE submit_business_process (
1041 errbuf OUT NOCOPY VARCHAR2,
1042 retcode OUT NOCOPY VARCHAR2,
1043 p_analysis_cycle_id IN NUMBER,
1044 p_cal_period_id IN VARCHAR2)
1045 IS
1046 l_business_area_name VARCHAR2(60);
1047 l_business_process_name VARCHAR(300);
1048 l_horizon_start DATE;
1049 l_horizon_end DATE;
1050 l_business_process_access VARCHAR2(100) := ''N'';
1051 l_bp_user_id NUMBER;
1052 l_start VARCHAR2(1000);
1053 l_end VARCHAR2(1000);
1054 l_key VARCHAR2(1000);
1055 l_msg_count NUMBER;
1056 l_msg_data VARCHAR2(1000);
1057 l_return_status VARCHAR2(10);
1058 l_end_date_attribute_id NUMBER :=
1059 gcs_utility_pkg.g_dimension_attr_info(''CAL_PERIOD_ID-CAL_PERIOD_END_DATE'').attribute_id;
1060 l_end_date_version_id NUMBER :=
1064 l_start_date_version_id NUMBER :=
1061 gcs_utility_pkg.g_dimension_attr_info(''CAL_PERIOD_ID-CAL_PERIOD_END_DATE'').version_id;
1062 l_start_date_attribute_id NUMBER :=
1063 gcs_utility_pkg.g_dimension_attr_info(''CAL_PERIOD_ID-CAL_PERIOD_START_DATE'').attribute_id;
1065 gcs_utility_pkg.g_dimension_attr_info(''CAL_PERIOD_ID-CAL_PERIOD_START_DATE'').version_id;
1066 l_request_id NUMBER;
1067 l_conc_req_status BOOLEAN;
1068
1069 BEGIN
1070
1071 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1072 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.SUBMIT_BUSINESS_PROCESS.begin'', ''<<Enter>>'');
1073 END IF;
1074
1075 fnd_file.put_line(fnd_file.log, ''<<Submit business process Parameter Listings>>'');
1076 fnd_file.put_line(fnd_file.log, ''Calendar Period : '' || p_cal_period_id);
1077 fnd_file.put_line(fnd_file.log, ''Analysis Cycle Id : '' || p_analysis_cycle_id);
1078 fnd_file.put_line(fnd_file.log, ''<<End of Parameter Listings>>'');
1079
1080 -- bugfix 5569522: If the user had selected a business process, check whether
1081 -- the user has the access to launch the business process.
1082 SELECT value
1083 INTO l_business_process_access
1084 FROM zpb_ac_param_values
1085 WHERE analysis_cycle_id = p_analysis_cycle_id
1086 AND param_id = (SELECT tag
1087 FROM fnd_lookup_values_vl
1088 WHERE LOOKUP_TYPE = ''ZPB_PARAMS''
1089 AND lookup_code = ''OVERRIDE_EXTERNAL_USER_CHECK'');
1090
1091
1092 IF (l_business_process_access = ''N'') THEN
1093 SELECT user_id
1094 INTO l_bp_user_id
1095 FROM zpb_bp_external_users
1096 WHERE analysis_cycle_id = p_analysis_cycle_id;
1097
1098 IF l_bp_user_id IS NOT NULL THEN
1099 l_business_process_access := ''Y'';
1100 END IF;
1101 END IF;
1102 fnd_file.put_line(fnd_file.log, ''SUBMIT_BUSINESS_PROCESS:Business Process access:'' || l_business_process_access);
1103
1104
1105 IF (l_business_process_access = ''N'') THEN
1106 fnd_file.put_line(fnd_file.log, ''SUBMIT_BUSINESS_PROCESS: The Business Process cannot be kicked off because the user does not have access to submit the Business Process'');
1107 l_conc_req_status := FND_CONCURRENT.set_completion_status(''WARNING'', ''The Business Process cannot be kicked off because the user does not have access to submit the Business Process'');
1108 ELSE
1109 fnd_file.put_line(fnd_file.log, ''SUBMIT_BUSINESS_PROCESS: The user has access to submit the Business Process'');
1110 END IF;
1111
1112
1113 -- bugfix 5569522: if the user has access, launch the business process. If the
1114 -- business process was successful then log a success message and launch the
1115 -- workflow background process.
1116 IF (l_business_process_access = ''Y'') THEN
1117 SELECT zac.name, zbav.name
1118 INTO l_business_process_name, l_business_area_name
1119 FROM zpb_business_areas_vl zbav, zpb_analysis_cycles zac
1120 WHERE zbav.business_area_id = zac.business_area_id
1121 AND zac.analysis_cycle_id = p_analysis_cycle_id;
1122
1123
1124 SELECT start_fcpa.date_assign_value
1125 INTO l_horizon_start
1126 FROM fem_cal_periods_attr start_fcpa
1127 WHERE start_fcpa.cal_period_id = p_cal_period_id
1128 AND start_fcpa.attribute_id = l_start_date_attribute_id
1129 AND start_fcpa.version_id = l_start_date_version_id;
1130
1131
1132 SELECT end_fcpa.date_assign_value
1133 INTO l_horizon_end
1134 FROM fem_cal_periods_attr end_fcpa
1135 WHERE end_fcpa.cal_period_id = p_cal_period_id
1136 AND end_fcpa.attribute_id = l_end_date_attribute_id
1137 AND end_fcpa.version_id = l_end_date_version_id;
1138
1139 fnd_file.put_line(fnd_file.log, ''Business process name: '' || l_business_process_name);
1140 fnd_file.put_line(fnd_file.log, ''Business Area name : '' || l_business_area_name);
1141 fnd_file.put_line(fnd_file.log, ''Horizon start date : '' || l_horizon_start);
1142 fnd_file.put_line(fnd_file.log, ''Horizon end date : '' || l_horizon_end);
1143
1144
1145 ZPB_EXTERNAL_BP_PUBLISH.START_BUSINESS_PROCESS
1146 (
1147 P_api_version => 1,
1148 P_init_msg_list => ''Y'',
1149 P_validation_level => 1,
1150 P_bp_name => l_business_process_name,
1151 P_ba_name => l_business_area_name,
1152 P_horizon_start => l_horizon_start,
1153 P_horizon_end => l_horizon_end,
1154 P_send_date => NULL,
1155 x_start_member => l_start,
1156 x_end_member => l_end,
1157 X_item_key => l_key,
1158 X_msg_count => l_msg_count,
1159 X_msg_data => l_msg_data,
1160 X_return_status => l_return_status
1161 );
1162
1163 fnd_file.put_line(fnd_file.log, '' l_start= '' || l_start);
1164 fnd_file.put_line(fnd_file.log, '' l_end= '' || l_end);
1165 fnd_file.put_line(fnd_file.log, '' l_key= '' || l_key);
1166 fnd_file.put_line(fnd_file.log, '' l_msg_count= '' || l_msg_count);
1167 fnd_file.put_line(fnd_file.log, '' l_msg_data = '' || l_msg_data );
1168 fnd_file.put_line(fnd_file.log, '' l_return_status = '' || l_return_status );
1169
1170 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1171 fnd_file.put_line(fnd_file.log, ''SUBMIT_BUSINESS_PROCESS: The Business Process has been launched successfully'');
1172
1173 -- run the workflow background concurrent program.
1174 l_request_id := fnd_request.submit_request(
1175 application => ''FND'',
1176 program => ''FNDWFBG'',
1177 sub_request => FALSE,
1178 argument1 => null,
1179 argument2 => null,
1180 argument3 => null,
1181 argument4 => ''Y'',
1182 argument5 => ''Y'',
1183 argument6 => ''Y'');
1184
1185 END IF;
1186
1187 END IF;
1188
1189 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1190 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.SUBMIT_BUSINESS_PROCESS.end'', ''<<Exit>>'');
1191 END IF;
1192 END;
1193
1194
1195
1196 END GCS_DYN_EPB_DATATR_PKG;
1197 ';
1198 curr_pos := 1;
1199 body_len := LENGTH(body);
1200 WHILE curr_pos <= body_len LOOP
1201 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1202 curr_pos := curr_pos + g_line_size;
1203 r := r + 1;
1204 END LOOP;
1205
1206 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', 'GCS_DYN_EPB_DATATR_PKG',1, r - 1, 'FALSE', err);
1207
1208 -- dbms_output.put_line('Error' || AD_DDL.error_buf);
1209
1210 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1211 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'GCS_BUILD_EPB_DATATR_PKG' || '.' || 'BUILD_EPB_DATATR_PKG',
1212 GCS_UTILITY_PKG.g_module_success || 'BUILD_EPB_DATATR_PKG' ||
1213 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1214 END IF;
1215 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success || 'BUILD_EPB_DATATR_PKG' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1216
1217 EXCEPTION
1218 WHEN OTHERS THEN
1219 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1220 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1221 'GCS_BUILD_EPB_DATA_TR_PKG' || '.' || 'BUILD_EPB_DATATR_PKG',
1222 SUBSTR(SQLERRM, 1, 255));
1223 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1224 'GCS_BUILD_EPB_DATA_TR_PKG' || '.' || 'BUILD_EPB_DATATR_PKG',
1225 GCS_UTILITY_PKG.g_module_failure || 'BUILD_EPB_DATATR_PKG' ||
1226 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1227 END IF;
1228 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1229 'BUILD_EPB_DATATR_PKG' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1230 END build_epb_datatr_pkg;
1231
1232 END GCS_BUILD_EPB_DATA_TR_PKG;