[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_PUSH_SETUP_PARAMETERS
Source
1 PACKAGE BODY MSD_DEM_PUSH_SETUP_PARAMETERS AS
2 /* $Header: msddempspb.pls 120.1.12010000.2 2008/09/05 09:50:38 nallkuma ship $ */
3
4 /*** CUSTOM DATA TYPES ***/
5
6 TYPE PROFILE_REC IS RECORD (
7 profile_code VARCHAR2(30),
8 profile_value VARCHAR2(255),
9 function_name VARCHAR2(100),
10 destination_flag VARCHAR2(1),
11 function_profile_code VARCHAR2(1));
12
13 TYPE PROFILE_TAB IS TABLE OF PROFILE_REC INDEX BY BINARY_INTEGER;
14
15 /*** GLOBAL VARIABLES ***/
16 g_dblink VARCHAR2(50) := NULL;
17 g_profile_list PROFILE_TAB;
18 g_num_profiles NUMBER := -1;
19 g_msd_schema_name VARCHAR2(50) := NULL;
20
21 g_user_id NUMBER := NULL;
22 g_login_id NUMBER := NULL;
23
24
25 /*** PRIVATE FUNCTIONS ***
26 * GET_PROFILE_VALUE
27 * GET_FUNCTION_VALUE
28 * GET_MULTI_ORG_FLAG
29 * DECODE_PROFILE_FUNCTION
30 */
31
32
33 FUNCTION GET_PROFILE_VALUE (
34 p_profile_code IN VARCHAR2,
35 p_destination_flag IN VARCHAR2)
36 RETURN VARCHAR2
37 IS
38 x_return_value VARCHAR2(255);
39 x_sql VARCHAR2(100);
40 BEGIN
41
42 IF (p_destination_flag = 'Y')
43 THEN
44 x_return_value := fnd_profile.value (p_profile_code);
45 ELSE
46 x_sql := 'BEGIN :x_ret1 := fnd_profile.value' || g_dblink ||
47 '(''' || p_profile_code || '''); END;';
48 EXECUTE IMMEDIATE x_sql USING OUT x_return_value;
49 END IF;
50
51 RETURN x_return_value;
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.get_profile_value - ' || sysdate);
56 RETURN NULL;
57
58 END GET_PROFILE_VALUE;
59
60
61 FUNCTION GET_FUNCTION_VALUE (
62 p_function_name IN VARCHAR2,
63 p_destination_flag IN VARCHAR2)
64 RETURN VARCHAR2
65 IS
66 x_return_value VARCHAR2(255);
67 x_sql VARCHAR2(100);
68 BEGIN
69
70 IF (p_destination_flag = 'Y')
71 THEN
72 x_sql := 'BEGIN :x_ou1 := ' || p_function_name || '; END;';
73 EXECUTE IMMEDIATE x_sql USING OUT x_return_value;
74 ELSE
75 x_sql := 'BEGIN :x_ou1 := ' || p_function_name || g_dblink || '; END;';
76 EXECUTE IMMEDIATE x_sql USING OUT x_return_value;
77 END IF;
78
79 RETURN x_return_value;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.get_function_value - ' || sysdate);
84 RETURN NULL;
85
86 END GET_FUNCTION_VALUE;
87
88
89 FUNCTION GET_MULTI_ORG_FLAG
90 RETURN VARCHAR2
91 IS
92 x_return_value VARCHAR2(1);
93 x_sql VARCHAR2(100);
94 BEGIN
95
96 x_sql := 'SELECT multi_org_flag FROM fnd_product_groups' || g_dblink ||
97 ' WHERE product_group_type = ''Standard''';
98 EXECUTE IMMEDIATE x_sql INTO x_return_value;
99 RETURN x_return_value;
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.get_multi_org_flag - ' || sysdate);
104 RETURN NULL;
105
106 END GET_MULTI_ORG_FLAG;
107
108
109 FUNCTION DECODE_PROFILE_FUNCTION (p_profile_rec IN PROFILE_REC)
110 RETURN VARCHAR2
111 IS
112 x_return_value VARCHAR2(255);
113 BEGIN
114
115 IF (p_profile_rec.function_profile_code = 'P')
116 THEN
117 x_return_value := to_char(get_profile_value (p_profile_rec.profile_code, p_profile_rec.destination_flag));
118 ELSE
119 x_return_value := to_char(get_function_value (p_profile_rec.function_name, p_profile_rec.destination_flag));
120 END IF;
121
122 RETURN x_return_value;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.decode_profile_function - ' || sysdate);
127 RETURN NULL;
128
129 END DECODE_PROFILE_FUNCTION;
130
131
132
133 /*** PRIVATE PROCEDURES ***
134 * CHECK_CUSTOMER_ATTRIBUTE
135 * INIT
136 * PUSH_PROFILES
137 * PUSH_ORGANIZATIONS
138 * PUSH_TIME_DATA
139 */
140
141
142 /*
143 * Usability Enhancements. Bug # 3509147.
144 * This procedure sets the value of the profile MSD_DEM_CUSTOMER_ATTRIBUTE to NONE
145 * if collecting for the first time.
146 */
147 PROCEDURE CHECK_CUSTOMER_ATTRIBUTE (
148 errbuf OUT NOCOPY VARCHAR2,
149 retcode OUT NOCOPY VARCHAR2)
150 IS
151
152 /*** LOCAL VARIABLES ***/
153
154 x_errbuf VARCHAR2(200) := NULL;
155 x_retcode VARCHAR2(100) := NULL;
156
157 x_check_customer_attr_log_msg VARCHAR2(1000) := 'Warning - The collection of customers, ship to locations (customer sites), regions,
158 and other level values in the geography dimension from e-business source
159 instance to Demand Planning depends on the profile, MSD:Customer Attribute.
160 It is recommended to set the profile to selectively collect these level values.
161 To collect all the available geography dimension level values, please clear
162 out the dummy profile value. Until the profile value is set appropriately or
163 cleared out, only the dummy level value (other) will be collected into
164 Demand Management for geography dimension.';
165
166 x_is_table_not_empty NUMBER := -1;
167 x_set_profile NUMBER := -1;
168 x_sql VARCHAR2(1000) := NULL;
169
170 BEGIN
171
172 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.check_customer_attribute - ' || sysdate);
173
174 /*
175 * If collecting for the first time, then the table msd_dem_setup_parameters
176 * will be empty in the source instance
177 */
178 x_sql := 'SELECT count(*) FROM msd_dem_setup_parameters' || g_dblink;
179 EXECUTE IMMEDIATE x_sql INTO x_is_table_not_empty;
180
181 IF (x_is_table_not_empty = 0)
182 THEN
183
184 x_sql := 'BEGIN :x_out1 := msd_dem_sr_util.set_customer_attribute' || g_dblink ||
185 ' (''MSD_DEM_CUSTOMER_ATTRIBUTE'', ''NONE'', ''SITE''); END;';
186 EXECUTE IMMEDIATE x_sql USING OUT x_set_profile;
187 msd_dem_common_utilities.log_message (x_check_customer_attr_log_msg);
188
189 IF (x_set_profile = 2)
190 THEN
191 retcode := -1;
192 errbuf := 'Error while Setting Value for Profile MSD_CUSTOMER_ATTRIBUTE';
193 msd_dem_common_utilities.log_message ('Error: msd_dem_push_setup_parameters.check_customer_attribute - ' || sysdate);
194 msd_dem_common_utilities.log_message ('Error while Setting Value for Profile MSD_CUSTOMER_ATTRIBUTE');
195 RETURN;
196 END IF;
197
198 COMMIT;
199 retcode := 1;
200 END IF;
201 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.check_customer_attribute - ' || sysdate);
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 retcode := -1 ;
206 errbuf := substr(SQLERRM,1,150);
207 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.check_customer_attribute - ' || sysdate);
208 msd_dem_common_utilities.log_message (errbuf);
209 RETURN;
210
211 END CHECK_CUSTOMER_ATTRIBUTE;
212
213
214 /*
215 * This procedure initializes the profiles nested table.
216 */
217 PROCEDURE INIT (
218 errbuf OUT NOCOPY VARCHAR2,
219 retcode OUT NOCOPY VARCHAR2)
220 IS
221
222 /*** LOCAL VARIABLES ***/
223
224 x_errbuf VARCHAR2(200) := NULL;
225 x_retcode VARCHAR2(100) := NULL;
226
227 BEGIN
228
229 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.init - ' || sysdate);
230
231 g_profile_list(1).profile_code := 'MSD_DEM_CATEGORY_SET_NAME';
232 g_profile_list(1).function_name := 'MSD_DEM_SR_UTIL.GET_CATEGORY_SET_ID';
233 g_profile_list(1).destination_flag := 'N';
234 g_profile_list(1).function_profile_code := 'F';
235
236 g_profile_list(2).profile_code := 'MSD_DEM_CONVERSION_TYPE';
237 g_profile_list(2).function_name := 'MSD_DEM_SR_UTIL.GET_CONVERSION_TYPE';
238 g_profile_list(2).destination_flag := 'N';
239 g_profile_list(2).function_profile_code := 'F';
240
241 g_profile_list(3).profile_code := 'MSD_DEM_CURRENCY_CODE';
242 g_profile_list(3).destination_flag := 'Y';
243 g_profile_list(3).function_profile_code := 'P';
244
245 g_profile_list(4).profile_code := 'MSD_DEM_MASTER_ORG';
246 g_profile_list(4).function_name := 'MSD_DEM_SR_UTIL.GET_MASTER_ORGANIZATION';
247 g_profile_list(4).destination_flag := 'N';
248 g_profile_list(4).function_profile_code := 'F';
249
250 g_profile_list(5).profile_code := 'MSD_DEM_CUSTOMER_ATTRIBUTE';
251 g_profile_list(5).function_name := 'MSD_DEM_SR_UTIL.GET_CUSTOMER_ATTRIBUTE';
252 g_profile_list(5).destination_flag := 'N';
253 g_profile_list(5).function_profile_code := 'F';
254
255 g_profile_list(6).profile_code := 'MSD_DEM_TWO_LEVEL_PLANNING';
256 g_profile_list(6).destination_flag := 'Y';
257 g_profile_list(6).function_profile_code := 'P';
258
259 g_profile_list(7).profile_code := 'MSD_DEM_SCHEMA';
260 g_profile_list(7).destination_flag := 'Y';
261 g_profile_list(7).function_profile_code := 'P';
262
263 g_num_profiles := g_profile_list.LAST;
264
265 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.init - ' || sysdate);
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 retcode := -1 ;
270 errbuf := substr(SQLERRM,1,150);
271 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.init - ' || sysdate);
272 msd_dem_common_utilities.log_message (errbuf);
273 RETURN;
274
275 END INIT;
276
277
278 /*
279 * This procedure pushes the profiles and their values to the source instance
280 * in MSD_DEM_SETUP_PARAMETERS.
281 */
282 PROCEDURE PUSH_PROFILES (
283 errbuf OUT NOCOPY VARCHAR2,
284 retcode OUT NOCOPY VARCHAR2,
285 p_sr_instance_id IN NUMBER,
286 p_dblink IN VARCHAR2)
287 IS
288
289 /*** LOCAL VARIABLES ***/
290
291 x_errbuf VARCHAR2(200) := NULL;
292 x_retcode VARCHAR2(100) := NULL;
293
294 x_error NUMBER := -1;
295 x_warning NUMBER := -1;
296 x_master_org_prf_value VARCHAR2(100) := NULL;
297 x_multi_org_flag VARCHAR2(1) := NULL;
298
299 x_sql VARCHAR2(1000) := NULL;
300
301 BEGIN
302
303 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_profiles - ' || sysdate);
304
305 x_sql := 'alter session set session_cached_cursors = 0';
306 execute immediate x_sql;
307
308
309 msd_dem_common_utilities.log_message (' Push Profiles ');
310 msd_dem_common_utilities.log_message ('---------------');
311 msd_dem_common_utilities.log_message (' ');
312 msd_dem_common_utilities.log_message ('-------------------------------------------------------------');
313 msd_dem_common_utilities.log_message (' Profile Name - Value');
314 msd_dem_common_utilities.log_message ('-------------------------------------------------------------');
315
316 /* Initializing profiles */
317 init (x_errbuf,
318 x_retcode);
319
320 /* Get the Profile values */
321 FOR i IN g_profile_list.FIRST..g_profile_list.LAST
322 LOOP
323 g_profile_list(i).profile_value := decode_profile_function (g_profile_list(i));
324 msd_dem_common_utilities.log_message (rpad('Profile ' || g_profile_list(i).profile_code,40)
325 || '- ' || g_profile_list(i).profile_value);
326 END LOOP;
327
328 msd_dem_common_utilities.log_message ('-------------------------------------------------------------');
329 msd_dem_common_utilities.log_message (' ');
330
331 /* Check if all the mandatory profiles are defined */
332 IF ( (g_profile_list(2).profile_value IS NULL)
333 OR (g_profile_list(3).profile_value IS NULL)
334 OR (g_profile_list(4).profile_value IS NULL)
335 OR (g_profile_list(7).profile_value IS NULL))
336 THEN
337 x_error := 1;
338 END IF;
339
340 /* In case of multi org, l_para_prof(4) will have master org id
344 * master_organization function call, not from the source profile value
341 * through master_organization function call even though
342 * profile value is not specified. In this case, give warning to user
343 * to confirm that master_org_id for the source will be the org_id from
345 */
346 IF (g_profile_list(4).profile_value IS NOT NULL)
347 THEN
348 x_master_org_prf_value := get_profile_value ('MSD_DEM_MASTER_ORG', 'N');
349 x_multi_org_flag := get_multi_org_flag;
350 IF ( x_master_org_prf_value IS NULL
351 AND x_multi_org_flag = 'Y')
352 THEN
353 x_warning := 1;
354 END IF;
355 END IF;
356
357 /* If Two-Level Planning has not been set, then default it to NO
358 */
359 IF (g_profile_list(6).profile_value IS NULL)
360 THEN
361 msd_dem_common_utilities.log_message ('Profile ' ||
362 g_profile_list(6).profile_code ||
363 ' is not defined. Defaulting this profile to - ');
364 msd_dem_common_utilities.log_message ('''Exclude family members with forecast control NONE''');
365 g_profile_list(6).profile_value := 2;
366
367 END IF;
368
369 IF ( (x_error <> 1) AND (x_warning = 1) )
370 THEN
371 msd_dem_common_utilities.log_message ('Profile ' || g_profile_list(4).profile_code ||
372 ' in the Source instance NOT SET !!!');
373 msd_dem_common_utilities.log_message ('The system has determined to use Organization Id = ' ||
374 g_profile_list(4).profile_value ||
375 ' as the master org.');
376 msd_dem_common_utilities.log_message ('If this is not the master org, please update the MSD_DEM_MASTER_ORG profile on the Source');
377 msd_dem_common_utilities.log_message ('and rerun collections.');
378 END IF;
379
380 IF (x_error = 1)
381 THEN
382 msd_dem_common_utilities.log_message ('Please make sure that profiles ' ||
383 'MSD_DEM_CONVERSION_TYPE and MSD_DEM_MASTER_ORG are set in Source instance');
384 msd_dem_common_utilities.log_message (' and MSD_DEM_CURRENCY_CODE and MSD_DEM_SCHEMA profiles in the Planning Server are set.');
385
386 retcode := -1;
387 errbuf := 'Profiles not set';
388 RETURN;
389 ELSE
390
391 msd_dem_common_utilities.log_message (' Actions');
392 msd_dem_common_utilities.log_message ('---------');
393
394 msd_dem_common_utilities.log_message ('Deleting records from msd_dem_setup_parameters in the Source instance');
395 /* x_sql := 'TRUNCATE TABLE ' || g_msd_schema_name || '.msd_dem_setup_parameters' || g_dblink;
396 */
397 x_sql := 'DELETE FROM msd_dem_setup_parameters' || g_dblink;
398
399 EXECUTE IMMEDIATE x_sql;
400
401 msd_dem_common_utilities.log_message ('Inserting profiles into source msd_dem_setup_parameters');
402 x_sql := 'INSERT INTO msd_dem_setup_parameters' || g_dblink ||
403 ' (parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
404 ' created_by, last_update_login) values (:1, :2, sysdate, :3, sysdate, ' ||
405 ' :4, :5)';
406
407 FOR j IN g_profile_list.FIRST..g_profile_list.LAST
408 LOOP
409 EXECUTE IMMEDIATE x_sql USING g_profile_list(j).profile_code, g_profile_list(j).profile_value, g_user_id, g_user_id, g_login_id;
410 END LOOP;
411
412 COMMIT;
413
414 msd_dem_common_utilities.log_message (' ');
415
416 END IF;
417
418
419 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_profiles - ' || sysdate);
420
421 EXCEPTION
422 WHEN OTHERS THEN
423 retcode := -1 ;
424 errbuf := substr(SQLERRM,1,150);
425 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_profiles - ' || sysdate);
426 msd_dem_common_utilities.log_message (errbuf);
427 RETURN;
428
429 END PUSH_PROFILES;
430
431
432 /*
433 * This procedure pushes the collection enabled orgs to the source instance
434 * in MSD_DEM_APP_INSTANCE_ORGS.
435 */
436 PROCEDURE PUSH_ORGANIZATIONS (
437 errbuf OUT NOCOPY VARCHAR2,
438 retcode OUT NOCOPY VARCHAR2,
439 p_sr_instance_id IN NUMBER,
440 p_collection_group IN VARCHAR2,
441 p_dblink IN VARCHAR2)
442 IS
443
444 /*** LOCAL VARIABLES ***/
445
446 x_errbuf VARCHAR2(200) := NULL;
447 x_retcode VARCHAR2(100) := NULL;
448
449 x_sql VARCHAR2(1000) := NULL;
450
451 BEGIN
452
453 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_organizations - ' || sysdate);
454
455 msd_dem_common_utilities.log_message (' Push Organizations - Actions');
456 msd_dem_common_utilities.log_message ('------------------------------');
460 /* x_sql := 'TRUNCATE TABLE ' || g_msd_schema_name || '.msd_dem_app_instance_orgs' || g_dblink;
457 msd_dem_common_utilities.log_message (' ');
458
459 msd_dem_common_utilities.log_message ('Deleting Organizations from source msd_dem_app_instance_orgs');
461 */
462 x_sql := 'DELETE FROM msd_dem_app_instance_orgs' || g_dblink;
463
464 EXECUTE IMMEDIATE x_sql;
465
466 msd_dem_common_utilities.log_message ('Inserting Organizations into source msd_dem_app_instance_orgs');
467 x_sql := 'INSERT INTO msd_dem_app_instance_orgs' || g_dblink ||
468 ' ( organization_id, organization_code, last_update_date, last_updated_by, creation_date, ' ||
469 ' created_by, last_update_login) ' ||
470 ' SELECT mtp.sr_tp_id, mtp.organization_code, sysdate, :a1, sysdate, ' ||
471 ' :a2, :a3 ' ||
472 ' FROM msc_instance_orgs mio, ' ||
473 ' msc_trading_partners mtp ' ||
474 ' WHERE mio.sr_instance_id = :1 ' ||
475 ' AND nvl(mio.org_group, ''-888'') = decode( :2, ''-999'', nvl(mio.org_group, ''-888''), :3) ' ||
476 ' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 ' ||
477 ' AND mtp.sr_instance_id = mio.sr_instance_id ' ||
478 ' AND mtp.sr_tp_id = mio.organization_id ' ||
479 ' AND mtp.partner_type = 3';
480 EXECUTE IMMEDIATE x_sql USING g_user_id, g_user_id, g_login_id, p_sr_instance_id, p_collection_group, p_collection_group;
481
482 COMMIT;
483
484 msd_dem_common_utilities.log_message (' ');
485 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_organizations - ' || sysdate);
486
487 EXCEPTION
488 WHEN OTHERS THEN
489 retcode := -1 ;
490 errbuf := substr(SQLERRM,1,150);
491 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_organizations - ' || sysdate);
492 msd_dem_common_utilities.log_message (errbuf);
493 RETURN;
494
495 END PUSH_ORGANIZATIONS;
496
497
498 /*
499 * This procedure pushes the time data to the source instance
500 * in MSD_DEM_INPUTS
501 */
502 PROCEDURE PUSH_TIME_DATA (
503 errbuf OUT NOCOPY VARCHAR2,
504 retcode OUT NOCOPY VARCHAR2,
505 p_sr_instance_id IN NUMBER,
506 p_dblink IN VARCHAR2)
507 IS
508
509 CURSOR c_get_table (p_lookup_code VARCHAR2)
510 IS
511 SELECT meaning
512 FROM fnd_lookup_values_vl
513 WHERE lookup_type = 'MSD_DEM_TABLES'
514 AND lookup_code = p_lookup_code;
515
516 /*** LOCAL VARIABLES ***/
517
518 x_errbuf VARCHAR2(200) := NULL;
519 x_retcode VARCHAR2(100) := NULL;
520
521 x_sql VARCHAR2(1000) := NULL;
522
523 x_dm_table VARCHAR2(100) := NULL;
524 x_source_time_table VARCHAR2(100) := NULL;
525 x_start_date VARCHAR2(100) := NULL;
526 x_end_date VARCHAR2(100) := NULL;
527
528 x_time_bucket VARCHAR2(30) := NULL;
529 x_first_day_of_week VARCHAR2(30) := NULL;
530 x_aggregation_method NUMBER(1) := NULL;
531 x_actual_agg_method NUMBER(1) := NULL;
532
533 BEGIN
534
535 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_time_data - ' || sysdate);
536
537
538 msd_dem_common_utilities.log_message (' Push Time Data - Actions');
539 msd_dem_common_utilities.log_message ('------------------------------');
540 msd_dem_common_utilities.log_message (' ');
541
542 msd_dem_common_utilities.log_message ('Deleting time data from source msd_dem_dates');
543 /* x_sql := 'TRUNCATE TABLE ' || g_msd_schema_name || '.' || 'msd_dem_dates' || g_dblink;
544 */
545 x_sql := 'DELETE FROM msd_dem_dates' || g_dblink;
546
547 EXECUTE IMMEDIATE x_sql;
548
549 /*
550 OPEN c_get_table ('DM_WIZ_DM_DEF');
551 FETCH c_get_table INTO x_dm_table;
552 CLOSE c_get_table;
553 */
554
555 x_dm_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
556
557 /* Get the time level info for the active data model */
558 x_sql := 'SELECT time_bucket, first_day_of_week, aggregation_method ' ||
559 ' FROM ' || x_dm_table ||
560 ' WHERE dm_or_template = 2 ' ||
561 ' AND is_active = 1 ';
562
563 EXECUTE IMMEDIATE x_sql INTO x_time_bucket, x_first_day_of_week, x_aggregation_method;
564
565 IF (upper(x_time_bucket) = 'DAY')
566 THEN
567 msd_dem_common_utilities.log_message ('Lowest Time Bucket - Day : Time data not inserted into source msd_dem_dates');
568 RETURN;
569 ELSIF (upper(x_time_bucket) = 'WEEK')
570 THEN
571 x_actual_agg_method := x_aggregation_method;
572 ELSIF (upper(x_time_bucket) = 'MONTH')
573 THEN
574 /* Aggregate backwards */
575 x_actual_agg_method := 2;
579 msd_dem_common_utilities.log_message ('Error(1): msd_dem_push_setup_parameters.push_time_data - ' || sysdate);
576 ELSE
577 retcode := -1;
578 errbuf := 'Invalid time bucket';
580 msd_dem_common_utilities.log_message ('Invalid time bucket');
581 RETURN;
582 END IF;
583
584 IF (x_actual_agg_method = 1) /* Forward */
585 THEN
586 x_start_date := ' datet - num_of_days + 1, ';
587 x_end_date := ' datet, ';
588 ELSE
589 x_start_date := ' datet, ';
590 x_end_date := ' datet + num_of_days - 1, ';
591 END IF;
592
593 /*
594 OPEN c_get_table ('INPUTS');
595 FETCH c_get_table INTO x_source_time_table;
596 CLOSE c_get_table;
597 */
598
599 x_source_time_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS');
600
601 msd_dem_common_utilities.log_message ('Inserting time data into source msd_dem_dates');
602 x_sql := 'INSERT INTO msd_dem_dates' || g_dblink ||
603 ' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
604 ' SELECT datet, num_of_days, ' || x_start_date || x_end_date ||
605 ' sysdate, :1, sysdate, :2, :3 ' ||
606 ' FROM ' || x_source_time_table;
607 EXECUTE IMMEDIATE x_sql USING g_user_id, g_user_id, g_login_id;
608
609 COMMIT;
610
611 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_time_data - ' || sysdate);
612
613 EXCEPTION
614 WHEN OTHERS THEN
615 retcode := -1 ;
616 errbuf := substr(SQLERRM,1,150);
617 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_time_data - ' || sysdate);
618 msd_dem_common_utilities.log_message (errbuf);
619 RETURN;
620
621 END PUSH_TIME_DATA;
622
623
624
625 /*** PUBLIC PROCEDURES ***/
626
627 /*
628 * This procedure pushes the profile values, collection enabled orgs and
629 * the time data in the source instance, which will be used in the source
630 * views.
631 */
632 PROCEDURE PUSH_SETUP_PARAMETERS (
633 errbuf OUT NOCOPY VARCHAR2,
634 retcode OUT NOCOPY VARCHAR2,
635 p_sr_instance_id IN NUMBER,
636 p_collection_group IN VARCHAR2)
637 IS
638
639 /*** LOCAL VARIABLES ***/
640
641 x_errbuf VARCHAR2(200) := NULL;
642 x_retcode VARCHAR2(100) := NULL;
643
644 x_sql VARCHAR2(500) := NULL;
645
646 x_dem_schema VARCHAR2(50) := NULL; --jarora
647
648 CURSOR c_get_dm_schema --jarora
649 IS
650 SELECT owner
651 FROM dba_objects
652 WHERE owner = owner
653 AND object_type = 'TABLE'
654 AND object_name = 'MDP_MATRIX'
655 ORDER BY created desc;
656
657 BEGIN
658
659 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
660
661 /* Get the db link to the source instance */
662 msd_dem_common_utilities.get_dblink (
663 x_errbuf,
664 x_retcode,
665 p_sr_instance_id,
666 g_dblink);
667
668 IF (x_retcode = '-1')
669 THEN
670 retcode := -1;
671 errbuf := x_errbuf;
672 msd_dem_common_utilities.log_message ('Error(1): msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
673 RETURN;
674 END IF;
675
676 /* Get the msd schema name */
677 /* x_sql := 'DECLARE x_retval BOOLEAN; x_dummy1 VARCHAR2(50); x_dummy2 VARCHAR2(50); BEGIN x_retval := fnd_installation.get_app_info' || g_dblink || ' ( ''MSD'', x_dummy1, x_dummy2, :x_out1); END;';
678 EXECUTE IMMEDIATE x_sql USING OUT g_msd_schema_name;
679
680 msd_dem_common_utilities.log_debug ('MSD Schema: ' || g_msd_schema_name);
681 */
682 msd_dem_common_utilities.log_message (' Push Setup Parameters Program');
683 msd_dem_common_utilities.log_message (' -----------------------------');
684 msd_dem_common_utilities.log_message (' Source Instance ID : ' || p_sr_instance_id);
685 msd_dem_common_utilities.log_message (' DB Link: ' || g_dblink);
686 msd_dem_common_utilities.log_message (' ');
687
688 g_user_id := fnd_global.user_id;
689 g_login_id := fnd_global.login_id;
690
691 /* Set the profile MSD_DEM_CUSTOMER_ATTRIBUTE to 'NONE' if collecting for the first time */
692 check_customer_attribute (
693 x_errbuf,
694 x_retcode);
695
696 IF (x_retcode = '-1')
697 THEN
698 retcode := -1;
699 errbuf := x_errbuf;
700 msd_dem_common_utilities.log_message ('Error(2): msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
701 RETURN;
702 ELSE
703 retcode := x_retcode;
704 END IF;
705
706 /* Push the profile values to the source instance */
707 push_profiles (
708 x_errbuf,
709 x_retcode,
710 p_sr_instance_id,
711 g_dblink);
712
713 IF (x_retcode = '-1')
714 THEN
715 retcode := -1;
716 errbuf := x_errbuf;
717 msd_dem_common_utilities.log_message ('Error(3): msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
718 RETURN;
719 END IF;
720
721 /* Push the profile values to the source instance */
722 push_organizations (
723 x_errbuf,
724 x_retcode,
725 p_sr_instance_id,
726 p_collection_group,
727 g_dblink);
728
729 IF (x_retcode = '-1')
730 THEN
731 retcode := -1;
732 errbuf := x_errbuf;
733 msd_dem_common_utilities.log_message ('Error(4): msd_dem_push_setup_parameters.push_organizations - ' || sysdate);
734 RETURN;
735 END IF;
736
737 OPEN c_get_dm_schema; --jarora
738 FETCH c_get_dm_schema INTO x_dem_schema;
739 CLOSE c_get_dm_schema;
740
741 /* Demantra is Installed */
742 IF (x_dem_schema is not NULL) --jarora
743 THEN
744
745 /* Push the time data to the source instance */
746 push_time_data (
747 x_errbuf,
748 x_retcode,
749 p_sr_instance_id,
750 g_dblink);
751
752 IF (x_retcode = '-1')
753 THEN
754 retcode := -1;
755 errbuf := x_errbuf;
756 msd_dem_common_utilities.log_message ('Error(5): msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
757 RETURN;
758 END IF;
759
760 ELSE
761 NULL;
762 END IF; --jarora
763
764 COMMIT;
765
766 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
767
768 EXCEPTION
769 WHEN OTHERS THEN
770 retcode := -1 ;
771 errbuf := substr(SQLERRM,1,150);
772 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
773 msd_dem_common_utilities.log_message (errbuf);
774 RETURN;
775
776 END PUSH_SETUP_PARAMETERS;
777
778
779 END MSD_DEM_PUSH_SETUP_PARAMETERS;