[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.9.12020000.2 2013/01/08 06:34:42 kkhatri ship $ */
3
4 /*** CUSTOM DATA TYPES ***/
5
6 TYPE PROFILE_REC IS RECORD (
7 profile_code VARCHAR2(50),
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 /* Master Organizatioin parameter name */
21 g_master_org VARCHAR2(50) := 'MSD_DEM_MASTER_ORG';
22 /* Source Category Set parameter name */
23 g_sr_category_set VARCHAR2(50) := 'MSD_DEM_CATEGORY_SET_NAME';
24 g_user_id NUMBER := NULL;
25 g_login_id NUMBER := NULL;
26
27
28
29 /*** PRIVATE FUNCTIONS ***
30 * GET_PROFILE_VALUE
31 * GET_FUNCTION_VALUE
32 * GET_MULTI_ORG_FLAG
33 * DECODE_PROFILE_FUNCTION
34 */
35
36
37 FUNCTION GET_PROFILE_VALUE (
38 p_profile_code IN VARCHAR2,
39 p_destination_flag IN VARCHAR2)
40 RETURN VARCHAR2
41 IS
42 x_return_value VARCHAR2(255);
43 x_sql VARCHAR2(100);
44 BEGIN
45
46 IF (p_destination_flag = 'Y')
47 THEN
48 x_return_value := fnd_profile.value (p_profile_code);
49 ELSE
50 x_sql := 'BEGIN :x_ret1 := fnd_profile.value' || g_dblink ||
51 '(''' || p_profile_code || '''); END;';
52 EXECUTE IMMEDIATE x_sql USING OUT x_return_value;
53 END IF;
54
55 RETURN x_return_value;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.get_profile_value - ' || sysdate);
60 RETURN NULL;
61
62 END GET_PROFILE_VALUE;
63
64
65 FUNCTION GET_FUNCTION_VALUE (
66 p_function_name IN VARCHAR2,
67 p_destination_flag IN VARCHAR2)
68 RETURN VARCHAR2
69 IS
70 x_return_value VARCHAR2(255);
71 x_sql VARCHAR2(100);
72 BEGIN
73
74 IF (p_destination_flag = 'Y')
75 THEN
76 x_sql := 'BEGIN :x_ou1 := ' || p_function_name || '; END;';
77 EXECUTE IMMEDIATE x_sql USING OUT x_return_value;
78 ELSE
79 x_sql := 'BEGIN :x_ou1 := ' || p_function_name || g_dblink || '; END;';
80 EXECUTE IMMEDIATE x_sql USING OUT x_return_value;
81 END IF;
82
83 RETURN x_return_value;
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.get_function_value - ' || sysdate);
88 RETURN NULL;
89
90 END GET_FUNCTION_VALUE;
91
92
93 FUNCTION GET_MULTI_ORG_FLAG
94 RETURN VARCHAR2
95 IS
96 x_return_value VARCHAR2(1);
97 x_sql VARCHAR2(200);
98 BEGIN
99
100 x_sql := 'SELECT multi_org_flag FROM fnd_product_groups' || g_dblink ||
101 ' WHERE product_group_type = ''Standard''';
102 EXECUTE IMMEDIATE x_sql INTO x_return_value;
103 RETURN x_return_value;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.get_multi_org_flag - ' || sysdate);
108 RETURN NULL;
109
110 END GET_MULTI_ORG_FLAG;
111
112
113 FUNCTION DECODE_PROFILE_FUNCTION (p_profile_rec IN PROFILE_REC)
114 RETURN VARCHAR2
115 IS
116 x_return_value VARCHAR2(255);
117 BEGIN
118
119 IF (p_profile_rec.function_profile_code = 'P')
120 THEN
121 x_return_value := to_char(get_profile_value (p_profile_rec.profile_code, p_profile_rec.destination_flag));
122 ELSE
123 x_return_value := to_char(get_function_value (p_profile_rec.function_name, p_profile_rec.destination_flag));
124 END IF;
125
126 RETURN x_return_value;
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.decode_profile_function - ' || sysdate);
131 RETURN NULL;
132
133 END DECODE_PROFILE_FUNCTION;
134
135
136
137 /*** PRIVATE PROCEDURES ***
138 * CHECK_CUSTOMER_ATTRIBUTE
139 * INIT
140 * PUSH_PROFILES
141 * PUSH_ORGANIZATIONS
142 * PUSH_TIME_DATA
143 */
144
145
146 /*
147 * Usability Enhancements. Bug # 3509147.
148 * This procedure sets the value of the profile MSD_DEM_CUSTOMER_ATTRIBUTE to NONE
149 * if collecting for the first time.
150 */
151 PROCEDURE CHECK_CUSTOMER_ATTRIBUTE (
152 errbuf OUT NOCOPY VARCHAR2,
153 retcode OUT NOCOPY VARCHAR2)
154 IS
155
156 /*** LOCAL VARIABLES ***/
157
158 x_errbuf VARCHAR2(200) := NULL;
159 x_retcode VARCHAR2(100) := NULL;
160
161 x_check_customer_attr_log_msg VARCHAR2(1000) := 'Warning - The collection of customers, ship to locations (customer sites), regions,
162 and other level values in the geography dimension from e-business source
163 instance to Demand Planning depends on the profile, MSD:Customer Attribute.
164 It is recommended to set the profile to selectively collect these level values.
165 To collect all the available geography dimension level values, please clear
166 out the dummy profile value. Until the profile value is set appropriately or
167 cleared out, only the dummy level value (other) will be collected into
168 Demand Management for geography dimension.';
169
170 x_is_table_not_empty NUMBER := -1;
171 x_set_profile NUMBER := -1;
172 x_sql VARCHAR2(1000) := NULL;
173
174 BEGIN
175
176 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.check_customer_attribute - ' || sysdate);
177
178 /*
179 * If collecting for the first time, then the table msd_dem_setup_parameters
180 * will be empty in the source instance
181 */
182 x_sql := 'SELECT count(*) FROM msd_dem_setup_parameters' || g_dblink;
183 EXECUTE IMMEDIATE x_sql INTO x_is_table_not_empty;
184
185 IF (x_is_table_not_empty = 0)
186 THEN
187
188 x_sql := 'BEGIN :x_out1 := msd_dem_sr_util.set_customer_attribute' || g_dblink ||
189 ' (''MSD_DEM_CUSTOMER_ATTRIBUTE'', ''NONE'', ''SITE''); END;';
190 EXECUTE IMMEDIATE x_sql USING OUT x_set_profile;
191 msd_dem_common_utilities.log_message (x_check_customer_attr_log_msg);
192
193 IF (x_set_profile = 2)
194 THEN
195 retcode := -1;
196 errbuf := 'Error while Setting Value for Profile MSD_CUSTOMER_ATTRIBUTE';
197 msd_dem_common_utilities.log_message ('Error: msd_dem_push_setup_parameters.check_customer_attribute - ' || sysdate);
198 msd_dem_common_utilities.log_message ('Error while Setting Value for Profile MSD_CUSTOMER_ATTRIBUTE');
199 RETURN;
200 END IF;
201
202 COMMIT;
203 retcode := 1;
204 END IF;
205 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.check_customer_attribute - ' || sysdate);
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 retcode := -1 ;
210 errbuf := substr(SQLERRM,1,150);
211 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.check_customer_attribute - ' || sysdate);
212 msd_dem_common_utilities.log_message (errbuf);
213 RETURN;
214
215 END CHECK_CUSTOMER_ATTRIBUTE;
216
217
218 /*
219 * This procedure initializes the profiles nested table.
220 */
221 PROCEDURE INIT (
222 errbuf OUT NOCOPY VARCHAR2,
223 retcode OUT NOCOPY VARCHAR2,
224 p_sr_instance_id IN NUMBER DEFAULT NULL)
225 IS
226
227 /*** LOCAL VARIABLES ***/
228
229 x_errbuf VARCHAR2(200) := NULL;
230 x_retcode VARCHAR2(100) := NULL;
231
232 BEGIN
233
234 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.init - ' || sysdate);
235
236 g_profile_list(1).profile_code := 'MSD_DEM_CATEGORY_SET_NAME';
237 g_profile_list(1).function_name := 'MSD_DEM_SR_UTIL.GET_CATEGORY_SET_ID';
238 g_profile_list(1).destination_flag := 'N';
239 g_profile_list(1).function_profile_code := 'F';
240
241 g_profile_list(2).profile_code := 'MSD_DEM_CONVERSION_TYPE';
242 g_profile_list(2).function_name := 'MSD_DEM_SR_UTIL.GET_CONVERSION_TYPE';
243 g_profile_list(2).destination_flag := 'N';
244 g_profile_list(2).function_profile_code := 'F';
245
246 g_profile_list(3).profile_code := 'MSD_DEM_CURRENCY_CODE';
247 g_profile_list(3).destination_flag := 'Y';
248 g_profile_list(3).function_profile_code := 'P';
249
250 g_profile_list(4).profile_code := 'MSD_DEM_MASTER_ORG';
251 g_profile_list(4).function_name := 'MSD_DEM_SR_UTIL.GET_MASTER_ORGANIZATION';
252 g_profile_list(4).destination_flag := 'N';
253 g_profile_list(4).function_profile_code := 'F';
254
255 g_profile_list(5).profile_code := 'MSD_DEM_CUSTOMER_ATTRIBUTE';
256 g_profile_list(5).function_name := 'MSD_DEM_SR_UTIL.GET_CUSTOMER_ATTRIBUTE';
257 g_profile_list(5).destination_flag := 'N';
258 g_profile_list(5).function_profile_code := 'F';
259
260 g_profile_list(6).profile_code := 'MSD_DEM_TWO_LEVEL_PLANNING';
261 g_profile_list(6).destination_flag := 'Y';
262 g_profile_list(6).function_profile_code := 'P';
263
264 g_profile_list(7).profile_code := 'MSD_DEM_SCHEMA';
265 g_profile_list(7).destination_flag := 'Y';
266 g_profile_list(7).function_profile_code := 'P';
267
268 g_profile_list(8).profile_code := 'MSD_DEM_PLANNING_PERCENTAGE';
269 g_profile_list(8).destination_flag := 'Y';
270 g_profile_list(8).function_profile_code := 'P';
271
272 g_profile_list(9).profile_code := 'MSD_DEM_INCLUDE_DEPENDENT_DEMAND';
273 g_profile_list(9).destination_flag := 'Y';
274 g_profile_list(9).function_profile_code := 'P';
275
276 g_profile_list(10).profile_code := 'MSD_DEM_EXPLODE_DEMAND_METHOD';
277 g_profile_list(10).destination_flag := 'Y';
278 g_profile_list(10).function_profile_code := 'P';
279
280 g_profile_list(11).profile_code := 'MSC_SERVICE_LEVEL_CATEGORY_SET';
281 g_profile_list(11).function_name := 'MSD_DEM_COMMON_UTILITIES.GET_SPF_SR_CAT_SET_ID(' || to_char(p_sr_instance_id) || ')';
282 g_profile_list(11).destination_flag := 'Y';
283 g_profile_list(11).function_profile_code := 'F';
284
285 g_profile_list(12).profile_code := 'MSD_SPF_FAILURE_RATE_HIST_BASIS';
286 g_profile_list(12).destination_flag := 'Y';
287 g_profile_list(12).function_profile_code := 'P';
288
289 g_profile_list(13).profile_code := 'MSD_DEM_DAY_LEVEL';
290 g_profile_list(13).function_name := 'MSD_DEM_COMMON_UTILITIES.DM_TIME_LEVEL';
291 g_profile_list(13).destination_flag := 'Y';
292 g_profile_list(13).function_profile_code := 'F';
293
294 g_profile_list(14).profile_code := 'MSD_SPF_MASTER_ORG';
295 g_profile_list(14).function_name := 'MSD_DEM_SR_UTIL.GET_SPF_MASTER_ORGANIZATION';
296 g_profile_list(14).destination_flag := 'N';
297 g_profile_list(14).function_profile_code := 'F';
298
299 g_profile_list(15).profile_code := 'MSD_DEM_SIMULATION_SET_NAME';
300 g_profile_list(15).destination_flag := 'Y';
301 g_profile_list(15).function_profile_code := 'P';
302
303 g_profile_list(16).profile_code := 'MSD_DEM_FLEET_FLIGHT_HOURS_UOM';
304 g_profile_list(16).destination_flag := 'N';
305 g_profile_list(16).function_profile_code := 'P';
306
307 g_profile_list(17).profile_code := 'MSD_DEM_INCLUDE_ORG_CMRO_DATA';
308 g_profile_list(17).destination_flag := 'Y';
309 g_profile_list(17).function_profile_code := 'P';
310
311 g_num_profiles := g_profile_list.LAST;
312
313 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.init - ' || sysdate);
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 retcode := -1 ;
318 errbuf := substr(SQLERRM,1,150);
319 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.init - ' || sysdate);
320 msd_dem_common_utilities.log_message (errbuf);
321 RETURN;
322
323 END INIT;
324
325
326 /*
327 * This procedure pushes the profiles and their values to the source instance
328 * in MSD_DEM_SETUP_PARAMETERS.
329 */
330 PROCEDURE PUSH_PROFILES (
331 errbuf OUT NOCOPY VARCHAR2,
332 retcode OUT NOCOPY VARCHAR2,
333 p_sr_instance_id IN NUMBER,
334 p_dblink IN VARCHAR2,
335 p_for_spf IN NUMBER DEFAULT 2)
336 IS
337
338 /*** LOCAL VARIABLES ***/
339
340 x_errbuf VARCHAR2(200) := NULL;
341 x_retcode VARCHAR2(100) := NULL;
342
343 x_error NUMBER := -1;
344 x_warning NUMBER := -1;
345 x_master_org_prf_value VARCHAR2(100) := NULL;
346 x_multi_org_flag VARCHAR2(1) := NULL;
347
348 x_sql VARCHAR2(1000) := NULL;
349
350 BEGIN
351
352 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_profiles - ' || sysdate);
353
354 x_sql := 'alter session set session_cached_cursors = 0';
355 execute immediate x_sql;
356
357
358 msd_dem_common_utilities.log_message (' Push Profiles ');
359 msd_dem_common_utilities.log_message ('---------------');
360 msd_dem_common_utilities.log_message (' ');
361 msd_dem_common_utilities.log_message ('-------------------------------------------------------------');
362 msd_dem_common_utilities.log_message (' Profile Name - Value');
363 msd_dem_common_utilities.log_message ('-------------------------------------------------------------');
364
365 /* Initializing profiles */
366 init (x_errbuf,
367 x_retcode,
368 p_sr_instance_id);
369
370 /* Get the Profile values */
371 FOR i IN g_profile_list.FIRST..g_profile_list.LAST
372 LOOP
373 g_profile_list(i).profile_value := decode_profile_function (g_profile_list(i));
374 msd_dem_common_utilities.log_message (rpad('Profile ' || g_profile_list(i).profile_code,40)
375 || '- ' || g_profile_list(i).profile_value);
376 END LOOP;
377
378 msd_dem_common_utilities.log_message ('-------------------------------------------------------------');
379 msd_dem_common_utilities.log_message (' ');
380
381 /* Check if all the mandatory profiles are defined */
382 IF ( (g_profile_list(2).profile_value IS NULL)
383 OR (g_profile_list(3).profile_value IS NULL)
384 OR (g_profile_list(4).profile_value IS NULL)
385 OR (g_profile_list(7).profile_value IS NULL)
386 OR (g_profile_list(8).profile_value IS NULL)
387 OR (g_profile_list(9).profile_value IS NULL)
388 OR (g_profile_list(10).profile_value IS NULL))
389 THEN
390 x_error := 1;
391 END IF;
392
393 /* Check if all the mandatory profiles for SPF are defined */
394 IF ( (p_for_spf = 1)
395 AND ( g_profile_list(11).profile_value IS NULL
396 OR g_profile_list(12).profile_value IS NULL
397 OR g_profile_list(14).profile_value IS NULL))
398 THEN
399 x_error := 1;
400 END IF;
401
402 /* In case of multi org, l_para_prof(4) will have master org id
403 * through master_organization function call even though
404 * profile value is not specified. In this case, give warning to user
405 * to confirm that master_org_id for the source will be the org_id from
406 * master_organization function call, not from the source profile value
407 */
408 IF (g_profile_list(4).profile_value IS NOT NULL)
409 THEN
410 x_master_org_prf_value := get_profile_value ('MSD_DEM_MASTER_ORG', 'N');
411 x_multi_org_flag := get_multi_org_flag;
412 IF ( x_master_org_prf_value IS NULL
413 AND x_multi_org_flag = 'Y')
414 THEN
415 x_warning := 1;
416 END IF;
417 END IF;
418
419 /* If Two-Level Planning has not been set, then default it to NO
420 */
421 IF (g_profile_list(6).profile_value IS NULL)
422 THEN
423 msd_dem_common_utilities.log_message ('Profile ' ||
424 g_profile_list(6).profile_code ||
425 ' is not defined. Defaulting this profile to - ');
426 msd_dem_common_utilities.log_message ('''Exclude family members with forecast control NONE''');
427 g_profile_list(6).profile_value := 2;
428
429 END IF;
430
431 IF ( (x_error <> 1) AND (x_warning = 1) )
432 THEN
433 msd_dem_common_utilities.log_message ('Profile ' || g_profile_list(4).profile_code ||
434 ' in the Source instance NOT SET !!!');
435 msd_dem_common_utilities.log_message ('The system has determined to use Organization Id = ' ||
436 g_profile_list(4).profile_value ||
437 ' as the master org.');
438 msd_dem_common_utilities.log_message ('If this is not the master org, please update the MSD_DEM_MASTER_ORG profile on the Source');
439 msd_dem_common_utilities.log_message ('and rerun collections.');
440 END IF;
441
442 IF (x_error = 1 AND p_for_spf = 2)
443 THEN
444 msd_dem_common_utilities.log_message ('Please make sure that profiles ' ||
445 'MSD_DEM_CONVERSION_TYPE and MSD_DEM_MASTER_ORG are set in Source instance');
446 msd_dem_common_utilities.log_message (' and MSD_DEM_PLANNING_PERCENTAGE and MSD_DEM_INCLUDE_DEPENDENT_DEMAND and MSD_DEM_EXPLODE_DEMAND_METHOD');
447 msd_dem_common_utilities.log_message (' and MSD_DEM_CURRENCY_CODE and MSD_DEM_SCHEMA profiles in the Planning Server are set.');
448
449 retcode := -1;
450 errbuf := 'Profiles not set';
451 RETURN;
452 ELSIF (x_error = 1 AND p_for_spf = 1)
453 THEN
454 msd_dem_common_utilities.log_message ('Please make sure that profiles ' ||
455 'MSD_DEM_CONVERSION_TYPE and MSD_DEM_MASTER_ORG and MSD_SPF_MASTER_ORG are set in Source instance');
456 msd_dem_common_utilities.log_message (' and MSD_DEM_PLANNING_PERCENTAGE and MSD_DEM_INCLUDE_DEPENDENT_DEMAND and MSD_DEM_EXPLODE_DEMAND_METHOD');
457 msd_dem_common_utilities.log_message (' and MSD_DEM_CURRENCY_CODE and MSD_DEM_SCHEMA and MSC_SERVICE_ITEMS_CATSET and MSD_SPF_FAILURE_RATE_HIST_BASIS profiles in the Planning Server are set.');
458
459 retcode := -1;
460 errbuf := 'Profiles not set';
461 RETURN;
462 ELSE
463
464 msd_dem_common_utilities.log_message (' Actions');
465 msd_dem_common_utilities.log_message ('---------');
466
467 msd_dem_common_utilities.log_message ('Deleting records from msd_dem_setup_parameters in the Source instance');
468 /* x_sql := 'TRUNCATE TABLE ' || g_msd_schema_name || '.msd_dem_setup_parameters' || g_dblink;
469 */
470 x_sql := 'DELETE FROM msd_dem_setup_parameters' || g_dblink;
471
472 EXECUTE IMMEDIATE x_sql;
473
474 msd_dem_common_utilities.log_message ('Inserting profiles into source msd_dem_setup_parameters');
475 x_sql := 'INSERT INTO msd_dem_setup_parameters' || g_dblink ||
476 ' (parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
477 ' created_by, last_update_login) values (:1, :2, sysdate, :3, sysdate, ' ||
478 ' :4, :5)';
479
480 FOR j IN g_profile_list.FIRST..g_profile_list.LAST
481 LOOP
482 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;
483 END LOOP;
484
485 COMMIT;
486
487 IF (g_dblink IS NOT NULL)
488 THEN
489
490 msd_dem_common_utilities.log_message ('Deleting records from msd_dem_setup_parameters in the Destination instance');
491 x_sql := 'DELETE FROM msd_dem_setup_parameters';
492 EXECUTE IMMEDIATE x_sql;
493
494 msd_dem_common_utilities.log_message ('Inserting profiles into destination msd_dem_setup_parameters');
495 x_sql := 'INSERT INTO msd_dem_setup_parameters' ||
496 ' (parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
497 ' created_by, last_update_login) values (:1, :2, sysdate, :3, sysdate, ' ||
498 ' :4, :5)';
499
500 FOR j IN g_profile_list.FIRST..g_profile_list.LAST
501 LOOP
502 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;
503 END LOOP;
504
505 msd_dem_common_utilities.log_message ('Analyzing destination msd_dem_setup_parameters');
506 msd_dem_collect_history_data.analyze_table (x_errbuf, x_retcode, 'MSD_DEM_SETUP_PARAMETERS');
507
508 COMMIT;
509
510 END IF;
511
512 msd_dem_common_utilities.log_message (' ');
513
514 END IF;
515
516
517 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_profiles - ' || sysdate);
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 retcode := -1 ;
522 errbuf := substr(SQLERRM,1,150);
523 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_profiles - ' || sysdate);
524 msd_dem_common_utilities.log_message (errbuf);
525 RETURN;
526
527 END PUSH_PROFILES;
528
529
530 /*
531 * This procedure pushes the collection enabled orgs to the source instance
532 * in MSD_DEM_APP_INSTANCE_ORGS.
533 */
534 PROCEDURE PUSH_ORGANIZATIONS (
535 errbuf OUT NOCOPY VARCHAR2,
536 retcode OUT NOCOPY VARCHAR2,
537 p_sr_instance_id IN NUMBER,
538 p_collection_group IN VARCHAR2,
539 p_dblink IN VARCHAR2,
540 p_for_spf IN NUMBER DEFAULT 2)
541 IS
542
543 /*** LOCAL VARIABLES ***/
544
545 x_errbuf VARCHAR2(200) := NULL;
546 x_retcode VARCHAR2(100) := NULL;
547
548 x_sql VARCHAR2(1000) := NULL;
549
550 BEGIN
551
552 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_organizations - ' || sysdate);
553
554 msd_dem_common_utilities.log_message (' Push Organizations - Actions');
555 msd_dem_common_utilities.log_message ('------------------------------');
556 msd_dem_common_utilities.log_message (' ');
557
558 msd_dem_common_utilities.log_message ('Deleting Organizations from source msd_dem_app_instance_orgs');
559 /* x_sql := 'TRUNCATE TABLE ' || g_msd_schema_name || '.msd_dem_app_instance_orgs' || g_dblink;
560 */
561 x_sql := 'DELETE FROM msd_dem_app_instance_orgs' || g_dblink;
562
563 EXECUTE IMMEDIATE x_sql;
564
565 msd_dem_common_utilities.log_message ('Inserting Organizations into source msd_dem_app_instance_orgs');
566 x_sql := 'INSERT INTO msd_dem_app_instance_orgs' || g_dblink ||
567 ' ( organization_id, organization_code, last_update_date, last_updated_by, creation_date, ' ||
568 ' created_by, last_update_login) ' ||
569 ' SELECT mtp.sr_tp_id, mtp.organization_code, sysdate, :a1, sysdate, ' ||
570 ' :a2, :a3 ' ||
571 ' FROM msc_instance_orgs mio, ' ||
572 ' msc_trading_partners mtp ' ||
573 ' WHERE mio.sr_instance_id = :1 ' ||
574 ' AND nvl(mio.org_group, ''-888'') = decode( :2, ''-999'', nvl(mio.org_group, ''-888''), :3) ' ||
575 ' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 ' ||
576 ' AND mtp.sr_instance_id = mio.sr_instance_id ' ||
577 ' AND mtp.sr_tp_id = mio.organization_id ' ||
578 ' AND mtp.partner_type = 3';
579 EXECUTE IMMEDIATE x_sql USING g_user_id, g_user_id, g_login_id, p_sr_instance_id, p_collection_group, p_collection_group;
580
581 /* For SPF, filter the organizations to only service organizations */
582 IF (p_for_spf = 1)
583 THEN
584
585 x_sql := 'DELETE FROM msd_dem_app_instance_orgs' || g_dblink || ' mdaio ' ||
586 ' WHERE NOT EXISTS (SELECT 1 from hr_organization_information' || g_dblink || ' hoi ' ||
587 ' WHERE hoi.organization_id = mdaio.organization_id ' ||
588 ' AND hoi.org_information_context = ''CLASS'' ' ||
589 ' AND hoi.org_information1 = ''SPF'' ' ||
590 ' AND hoi.org_information2 = ''Y'') ';
591 msd_dem_common_utilities.log_message ('Removing Organizations not enabled for spares forecasting from source msd_dem_app_instance_orgs');
592 EXECUTE IMMEDIATE x_sql;
593
594 END IF;
595
596 /* For DM and SPF, filter out spf organization during DM collections */
597 IF (p_for_spf = 2)
598 THEN
599
600 x_sql := 'DELETE FROM msd_dem_app_instance_orgs' || g_dblink || ' mdaio ' ||
601 ' WHERE EXISTS (SELECT 1 from hr_organization_information' || g_dblink || ' hoi ' ||
602 ' WHERE hoi.organization_id = mdaio.organization_id ' ||
603 ' AND hoi.org_information_context = ''CLASS'' ' ||
604 ' AND hoi.org_information1 = ''SPF'' ' ||
605 ' AND hoi.org_information2 = ''Y'') ';
606 msd_dem_common_utilities.log_message ('Removing Organizations that are enabled for spares forecasting from source msd_dem_app_instance_orgs');
607 EXECUTE IMMEDIATE x_sql;
608
609 END IF;
610
611 COMMIT;
612
613 IF (g_dblink IS NOT NULL)
614 THEN
615
616 msd_dem_common_utilities.log_message ('Deleting Organizations from destination msd_dem_app_instance_orgs');
617 x_sql := 'DELETE FROM msd_dem_app_instance_orgs';
618 EXECUTE IMMEDIATE x_sql;
619
620 msd_dem_common_utilities.log_message ('Inserting Organizations into destination msd_dem_app_instance_orgs');
621 x_sql := 'INSERT INTO msd_dem_app_instance_orgs' ||
622 ' ( organization_id, organization_code, last_update_date, last_updated_by, creation_date, ' ||
623 ' created_by, last_update_login) ' ||
624 ' SELECT organization_id, organization_code, sysdate, :1, sysdate, :2, :3 ' ||
625 ' FROM msd_dem_app_instance_orgs' || g_dblink;
626 EXECUTE IMMEDIATE x_sql USING g_user_id, g_user_id, g_login_id;
627
628 msd_dem_common_utilities.log_message ('Analyzing destination msd_dem_app_instance_orgs');
629 msd_dem_collect_history_data.analyze_table (x_errbuf, x_retcode, 'MSD_DEM_APP_INSTANCE_ORGS');
630
631 COMMIT;
632
633 END IF;
634
635 msd_dem_common_utilities.log_message (' ');
636 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_organizations - ' || sysdate);
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 retcode := -1 ;
641 errbuf := substr(SQLERRM,1,150);
642 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_organizations - ' || sysdate);
643 msd_dem_common_utilities.log_message (errbuf);
644 RETURN;
645
646 END PUSH_ORGANIZATIONS;
647
648
649 /*
650 * This procedure pushes the time data to the source instance
651 * in MSD_DEM_INPUTS
652 */
653 PROCEDURE PUSH_TIME_DATA (
654 errbuf OUT NOCOPY VARCHAR2,
655 retcode OUT NOCOPY VARCHAR2,
656 p_sr_instance_id IN NUMBER,
657 p_dblink IN VARCHAR2)
658 IS
659
660 CURSOR c_get_table (p_lookup_code VARCHAR2)
661 IS
662 SELECT meaning
663 FROM fnd_lookup_values_vl
664 WHERE lookup_type = 'MSD_DEM_TABLES'
665 AND lookup_code = p_lookup_code;
666
667 /*** LOCAL VARIABLES ***/
668
669 x_errbuf VARCHAR2(200) := NULL;
670 x_retcode VARCHAR2(100) := NULL;
671
672 x_sql VARCHAR2(1000) := NULL;
673
674 x_dm_table VARCHAR2(100) := NULL;
675 x_source_time_table VARCHAR2(100) := NULL;
676 x_start_date VARCHAR2(100) := NULL;
677 x_end_date VARCHAR2(100) := NULL;
678
679 x_time_bucket VARCHAR2(30) := NULL;
680 x_first_day_of_week VARCHAR2(30) := NULL;
681 x_aggregation_method NUMBER(1) := NULL;
682 x_actual_agg_method NUMBER(1) := NULL;
683
684 BEGIN
685
686 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_time_data - ' || sysdate);
687
688
689 msd_dem_common_utilities.log_message (' Push Time Data - Actions');
690 msd_dem_common_utilities.log_message ('------------------------------');
691 msd_dem_common_utilities.log_message (' ');
692
693 msd_dem_common_utilities.log_message ('Deleting time data from source msd_dem_dates');
694 x_sql := 'DELETE FROM msd_dem_dates' || g_dblink;
695 EXECUTE IMMEDIATE x_sql;
696
697 x_dm_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
698
699 /* Get the time level info for the active data model */
700 x_sql := 'SELECT time_bucket, first_day_of_week, aggregation_method ' ||
701 ' FROM ' || x_dm_table ||
702 ' WHERE dm_or_template = 2 ' ||
703 ' AND is_active = 1 ';
704
705 EXECUTE IMMEDIATE x_sql INTO x_time_bucket, x_first_day_of_week, x_aggregation_method;
706
707 IF (upper(x_time_bucket) = 'DAY')
708 THEN
709 msd_dem_common_utilities.log_message ('Lowest Time Bucket - Day : Time data not inserted into source msd_dem_dates');
710 --populate msd_dem_day_dates before returning
711 --RETURN;
712 ELSIF (upper(x_time_bucket) = 'WEEK')
713 THEN
714 x_actual_agg_method := x_aggregation_method;
715 ELSIF (upper(x_time_bucket) = 'MONTH')
716 THEN
717 /* Aggregate backwards */
718 x_actual_agg_method := 2;
719 ELSE
720 retcode := -1;
721 errbuf := 'Invalid time bucket';
722 msd_dem_common_utilities.log_message ('Error(1): msd_dem_push_setup_parameters.push_time_data - ' || sysdate);
723 msd_dem_common_utilities.log_message ('Invalid time bucket');
724 RETURN;
725 END IF;
726
727 IF (x_actual_agg_method = 1) /* Forward */
728 THEN
729 x_start_date := ' datet - num_of_days + 1 start_date, ';
730 x_end_date := ' datet ';
731 ELSE
732 x_start_date := ' datet start_date, ';
733 x_end_date := ' datet + num_of_days - 1 ';
734 END IF;
735
736 x_end_date := 'trunc(' || x_end_date || ') + 86399/86400 end_date';
737
738 x_source_time_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS');
739
740 IF (UPPER(X_TIME_BUCKET) <> 'DAY') THEN
741 msd_dem_common_utilities.log_message ('Inserting time data into source msd_dem_dates');
742 x_sql := 'INSERT INTO msd_dem_dates' || g_dblink ||
743 ' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
744 ' SELECT datet, num_of_days, ' || x_start_date || x_end_date || ',' ||
745 ' sysdate, :1, sysdate, :2, :3 ' ||
746 ' FROM ' || x_source_time_table;
747 msd_dem_common_utilities.log_debug('SQL for inserting data into source msd_dem_dates : ');
748 msd_dem_common_utilities.log_debug(x_sql);
749 EXECUTE IMMEDIATE x_sql USING g_user_id, g_user_id, g_login_id;
750 END IF;
751
752 IF (g_dblink IS NOT NULL)
753 THEN
754
755 msd_dem_common_utilities.log_message ('Deleting time data from destination msd_dem_dates');
756 x_sql := 'DELETE FROM msd_dem_dates';
757 EXECUTE IMMEDIATE x_sql;
758
759 IF (UPPER(X_TIME_BUCKET) <> 'DAY') THEN
760 msd_dem_common_utilities.log_message ('Inserting time data into destination msd_dem_dates');
761 x_sql := 'INSERT INTO msd_dem_dates' ||
762 ' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
763 ' SELECT datet, num_of_days, ' || x_start_date || x_end_date || ',' ||
764 ' sysdate, :1, sysdate, :2, :3 ' ||
765 ' FROM ' || x_source_time_table;
766 msd_dem_common_utilities.log_debug('SQL for inserting data into destination msd_dem_dates : ');
767 msd_dem_common_utilities.log_debug(x_sql);
768 EXECUTE IMMEDIATE x_sql USING g_user_id, g_user_id, g_login_id;
769
770 msd_dem_common_utilities.log_message ('Analyzing destination msd_dem_dates');
771 msd_dem_collect_history_data.analyze_table (x_errbuf, x_retcode, 'MSD_DEM_DATES');
772 END IF;
773
774 COMMIT;
775
776 END IF;
777
778 msd_dem_common_utilities.log_message ('Deleting time data from destination msd_dem_day_dates');
779 x_sql := 'DELETE FROM msd_dem_day_dates';
780 EXECUTE IMMEDIATE x_sql;
781
782 msd_dem_common_utilities.log_message ('Inserting time data into destination msd_dem_day_dates');
783 x_sql := 'INSERT INTO msd_dem_day_dates' ||
784 ' (day_date, bucket_date, bucket_start_date, bucket_end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
785 ' with dem_dates as '||
786 ' (SELECT datet, num_of_days, ' || x_start_date || x_end_date ||
787 ' FROM ' || x_source_time_table || ') ' ||
788 ' select start_date + i day_date, datet, start_date, end_date, sysdate, :1, sysdate, :2, :3 ' ||
789 ' from dem_dates, xmltable(''for $i in 0 to xs:int(D)-1 return $i'' passing xmlelement(d, num_of_days)
790 columns i integer path ''.'')' ;
791 msd_dem_common_utilities.log_debug('SQL for inserting data into destination msd_dem_day_dates : ');
792 msd_dem_common_utilities.log_debug(x_sql);
793 EXECUTE IMMEDIATE x_sql USING g_user_id, g_user_id, g_login_id;
794
795 msd_dem_common_utilities.log_message ('Analyzing destination msd_dem_day_dates');
796 msd_dem_collect_history_data.analyze_table (x_errbuf, x_retcode, 'MSD_DEM_DAY_DATES');
797
798 commit;
799
800 IF (g_dblink IS NOT NULL) THEN
801 msd_dem_common_utilities.log_message ('Deleting time data from source msd_dem_day_dates');
802 x_sql := 'DELETE FROM msd_dem_day_dates' || g_dblink;
803 EXECUTE IMMEDIATE x_sql;
804
805 msd_dem_common_utilities.log_message('Inserting data into source msd_dem_day_dates');
806 x_sql := 'INSERT INTO msd_dem_day_dates' || g_dblink ||
807 ' (day_date, bucket_date, bucket_start_date, bucket_end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
808 ' SELECT day_date, bucket_date, bucket_start_date, bucket_end_date, ' ||
809 ' sysdate, :1, sysdate, :2, :3 ' ||
810 ' FROM msd_dem_day_dates' ;
811 msd_dem_common_utilities.log_debug('SQL for inserting data into source msd_dem_day_dates : ');
812 msd_dem_common_utilities.log_debug(x_sql);
813 execute immediate x_sql using g_user_id, g_user_id, g_login_id;
814 COMMIT;
815 END IF;
816
817 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_time_data - ' || sysdate);
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 retcode := -1 ;
822 errbuf := substr(SQLERRM,1,150);
823 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_time_data - ' || sysdate);
824 msd_dem_common_utilities.log_message (errbuf);
825 RETURN;
826
827 END PUSH_TIME_DATA;
828
829
830
831
832 /*** PUBLIC PROCEDURES ***/
833
834 /*
835 * This procedure pushes the profile values, collection enabled orgs and
836 * the time data in the source instance, which will be used in the source
837 * views.
838 */
839 PROCEDURE PUSH_SETUP_PARAMETERS (
840 errbuf OUT NOCOPY VARCHAR2,
841 retcode OUT NOCOPY VARCHAR2,
842 p_sr_instance_id IN NUMBER,
843 p_collection_group IN VARCHAR2,
844 p_for_spf IN NUMBER DEFAULT 2)
845 IS
846
847 /*** LOCAL VARIABLES ***/
848
849 x_errbuf VARCHAR2(200) := NULL;
850 x_retcode VARCHAR2(100) := NULL;
851
852 x_sql VARCHAR2(500) := NULL;
853
854 x_dem_schema VARCHAR2(50) := NULL; --jarora
855
856 CURSOR c_get_dm_schema --jarora
857 IS
858 SELECT owner
859 FROM dba_objects
860 WHERE owner = owner
861 AND object_type = 'TABLE'
862 AND object_name = 'MDP_MATRIX'
863 ORDER BY created desc;
864
865 BEGIN
866
867 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
868
869 /* Get the db link to the source instance */
870 msd_dem_common_utilities.get_dblink (
871 x_errbuf,
872 x_retcode,
873 p_sr_instance_id,
874 g_dblink);
875
876 IF (x_retcode = '-1')
877 THEN
878 retcode := -1;
879 errbuf := x_errbuf;
880 msd_dem_common_utilities.log_message ('Error(1): msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
881 RETURN;
882 END IF;
883
884 /* Get the msd schema name */
885 /* 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;';
886 EXECUTE IMMEDIATE x_sql USING OUT g_msd_schema_name;
887
888 msd_dem_common_utilities.log_debug ('MSD Schema: ' || g_msd_schema_name);
889 */
890 msd_dem_common_utilities.log_message (' Push Setup Parameters Program');
891 msd_dem_common_utilities.log_message (' -----------------------------');
892 msd_dem_common_utilities.log_message (' Source Instance ID : ' || p_sr_instance_id);
893 msd_dem_common_utilities.log_message (' DB Link: ' || g_dblink);
894 msd_dem_common_utilities.log_message (' ');
895
896 g_user_id := fnd_global.user_id;
897 g_login_id := fnd_global.login_id;
898
899 /* Set the profile MSD_DEM_CUSTOMER_ATTRIBUTE to 'NONE' if collecting for the first time
900 check_customer_attribute (
901 x_errbuf,
902 x_retcode); */
903
904 IF (x_retcode = '-1')
905 THEN
906 retcode := -1;
907 errbuf := x_errbuf;
908 msd_dem_common_utilities.log_message ('Error(2): msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
909 RETURN;
910 ELSE
911 retcode := x_retcode;
912 END IF;
913
914 /* Push the profile values to the source instance */
915 push_profiles (
916 x_errbuf,
917 x_retcode,
918 p_sr_instance_id,
919 g_dblink,
920 p_for_spf);
921
922 IF (x_retcode = '-1')
923 THEN
924 retcode := -1;
925 errbuf := x_errbuf;
926 msd_dem_common_utilities.log_message ('Error(3): msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
927 RETURN;
928 END IF;
929
930 /* Push the profile values to the source instance */
931 push_organizations (
932 x_errbuf,
933 x_retcode,
934 p_sr_instance_id,
935 p_collection_group,
936 g_dblink,
937 p_for_spf);
938
939 IF (x_retcode = '-1')
940 THEN
941 retcode := -1;
942 errbuf := x_errbuf;
943 msd_dem_common_utilities.log_message ('Error(4): msd_dem_push_setup_parameters.push_organizations - ' || sysdate);
944 RETURN;
945 END IF;
946
947 OPEN c_get_dm_schema; --jarora
948 FETCH c_get_dm_schema INTO x_dem_schema;
949 CLOSE c_get_dm_schema;
950
951 /* Demantra is Installed */
952 IF (x_dem_schema is not NULL) --jarora
953 THEN
954
955 /* Push the time data to the source instance */
956 push_time_data (
957 x_errbuf,
958 x_retcode,
959 p_sr_instance_id,
960 g_dblink);
961
962 IF (x_retcode = '-1')
963 THEN
964 retcode := -1;
965 errbuf := x_errbuf;
966 msd_dem_common_utilities.log_message ('Error(5): msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
967 RETURN;
968 END IF;
969
970 ELSE
971 NULL;
972 END IF; --jarora
973
974 COMMIT;
975
976 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
977
978 EXCEPTION
979 WHEN OTHERS THEN
980 retcode := -1 ;
981 errbuf := substr(SQLERRM,1,150);
982 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
983 msd_dem_common_utilities.log_message (errbuf);
984 RETURN;
985
986 END PUSH_SETUP_PARAMETERS;
987
988 /*
989 * This procedure updates profiles values configure for a particular legacy instance
990 * to the legacy profiles table - MSD_DEM_LEGACY_SETUP_PARAMS
991 */
992 PROCEDURE CONFIGURE_LEGACY_PROFILES (
993 errbuf OUT NOCOPY VARCHAR2,
994 retcode OUT NOCOPY VARCHAR2,
995 p_legacy_instance_id IN NUMBER,
996 p_master_org IN NUMBER,
997 p_sr_category_set_id IN NUMBER)
998 IS
999 /*** LOCAL VARIABLES ***/
1000 x_sql VARCHAR2(500) := NULL;
1001
1002 BEGIN
1003 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.configure_legacy_profiles - ' || sysdate);
1004
1005 /* check if instance_id or master_org or category_set is null */
1006 IF ( (p_legacy_instance_id IS NULL)
1007 OR (p_master_org IS NULL))
1008 THEN
1009 retcode := -1;
1010 errbuf := 'Legacy Instance ID or Master Organization or Category set ID cannot be null.';
1011 msd_dem_common_utilities.log_message ('Error: msd_dem_push_setup_parameters.push_setup_parameters - ' || sysdate);
1012 RETURN;
1013 END IF;
1014
1015 /* Before inserting parameter values for a legacy instance to the MSD_DEM_LEGACY_SETUP_PARAMS table
1016 * delete any rows which already exist for this instance.
1017 */
1018 msd_dem_common_utilities.log_message ('Deleting records from msd_dem_legacy_setup_params, for instance_id : ' || p_legacy_instance_id );
1019 x_sql := 'DELETE FROM msd_dem_legacy_setup_params where instance_id = ' || p_legacy_instance_id;
1020 EXECUTE IMMEDIATE x_sql;
1021
1022 g_user_id := fnd_global.user_id;
1023 g_login_id := fnd_global.login_id;
1024
1025 /* Insert values for the two parameters
1026 * MSD_DEM_MASTER_ORG and MSD_DEM_CATEGORY_SET_NAME
1027 * into MSD_DEM_LEGACY_SETUP_PARAMS table
1028 */
1029 x_sql := 'INSERT INTO msd_dem_legacy_setup_params' ||
1030 ' (instance_id, parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
1031 ' created_by, last_update_login) values (:1, :2, :3, sysdate, :4, sysdate, ' ||
1032 ' :5, :6)';
1033
1034 msd_dem_common_utilities.log_message ('Inserting profile ' || g_master_org || ' for legacy instance ' || p_legacy_instance_id || ' into msd_dem_legacy_setup_params');
1035 EXECUTE IMMEDIATE x_sql USING p_legacy_instance_id, g_master_org, p_master_org, g_user_id, g_user_id, g_login_id;
1036
1037 msd_dem_common_utilities.log_message ('Inserting profile ' || g_sr_category_set || ' for legacy instance ' || p_legacy_instance_id || ' into msd_dem_legacy_setup_params');
1038 EXECUTE IMMEDIATE x_sql USING p_legacy_instance_id, g_sr_category_set, p_sr_category_set_id, g_user_id, g_user_id, g_login_id;
1039
1040 COMMIT;
1041
1042 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.configure_legacy_profiles - ' || sysdate);
1043
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 retcode := -1 ;
1047 errbuf := substr(SQLERRM,1,150);
1048 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.configure_legacy_profiles - ' || sysdate);
1049 msd_dem_common_utilities.log_message (errbuf);
1050 RETURN;
1051
1052 END CONFIGURE_LEGACY_PROFILES;
1053
1054 /*
1055 * This procedure pushes profiles values for a particular legacy instance
1056 * from legacy profiles table - MSD_DEM_LEGACY_SETUP_PARAMS to setup parameters table - MSD_DEM_SETUP_PARAMETERS
1057 */
1058 PROCEDURE PUSH_LEGACY_SETUP_PARAMETERS (
1059 errbuf OUT NOCOPY VARCHAR2,
1060 retcode OUT NOCOPY VARCHAR2,
1061 p_legacy_instance_id IN NUMBER)
1062 IS
1063
1064 /*** LOCAL VARIABLES ***/
1065 x_sql VARCHAR2(500) := NULL;
1066 x_master_org_value VARCHAR2(240) := NULL;
1067 x_category_set_value VARCHAR2(240) := NULL;
1068
1069 BEGIN
1070
1071
1072 msd_dem_common_utilities.log_debug ('Entering: msd_dem_push_setup_parameters.push_legacy_setup_parameters - ' || sysdate);
1073
1074 msd_dem_common_utilities.log_message (' Push Legacy Setup Parameters Program');
1075 msd_dem_common_utilities.log_message (' ------------------------------------');
1076 msd_dem_common_utilities.log_message (' Legacy Instance ID : ' || p_legacy_instance_id);
1077 msd_dem_common_utilities.log_message (' ');
1078
1079 g_user_id := fnd_global.user_id;
1080 g_login_id := fnd_global.login_id;
1081
1082 -- Bug#12931039 mpmurali
1083 /* Push the time data to the source instance */
1084 msd_dem_common_utilities.log_message ('Pushing time data for Legacy Instance');
1085 push_time_data (
1086 errbuf,
1087 retcode,
1088 p_legacy_instance_id,
1089 g_dblink);
1090
1091 /* Push values for the two profiles MSD_DEM_MASTER_ORG and MSD_DEM_CATEGORY_SET_NAME
1092 * to MSD_DEM_SETUP_PARAMETERS table in the destination
1093 * as source is a legacy instance
1094 */
1095 x_sql := 'SELECT parameter_value FROM msd_dem_legacy_setup_params where instance_id = :1 and parameter_name = :2';
1096
1097 BEGIN
1098 msd_dem_common_utilities.log_message ('Fetching value for parameter - ' || g_master_org || ' from msd_dem_legacy_setup_params');
1099 EXECUTE IMMEDIATE x_sql INTO x_master_org_value USING p_legacy_instance_id, g_master_org;
1100
1101 /* Master Organization should be set*/
1102 IF (x_master_org_value IS NULL)
1103 THEN
1104 retcode := -1;
1105 errbuf := 'Master Organization not set for legacy instance : ' || p_legacy_instance_id;
1106 msd_dem_common_utilities.log_message (errbuf);
1107 RETURN;
1108 END IF;
1109
1110 msd_dem_common_utilities.log_message ('Fetching value for parameter - ' || g_sr_category_set || ' from msd_dem_legacy_setup_params');
1111 EXECUTE IMMEDIATE x_sql INTO x_category_set_value USING p_legacy_instance_id, g_sr_category_set;
1112 -- not checking for value of Category Set as it can be null
1113 EXCEPTION
1114 WHEN NO_DATA_FOUND THEN
1115 retcode := -1;
1116 errbuf := 'No records found for one or more parameters for legacy instance : ' || p_legacy_instance_id;
1117 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_legacy_setup_parameters - ' || sysdate);
1118 msd_dem_common_utilities.log_message (errbuf);
1119 RETURN;
1120 END;
1121
1122 msd_dem_common_utilities.log_message ('Deleting records for profiles ' || g_master_org || ' and ' || g_sr_category_set || ' from msd_dem_setup_parameters in the destination instance');
1123 x_sql := 'DELETE FROM msd_dem_setup_parameters where parameter_name in (''' || g_master_org || ''', ''' || g_sr_category_set || ''')';
1124 EXECUTE IMMEDIATE x_sql;
1125
1126 g_user_id := fnd_global.user_id;
1127 g_login_id := fnd_global.login_id;
1128
1129 /* Insert values for the two profiles
1130 * MSD_DEM_MASTER_ORG and MSD_DEM_CATEGORY_SET_NAME
1131 * into MSD_DEM_SETUP_PARAMETERS table
1132 */
1133 x_sql := 'INSERT INTO msd_dem_setup_parameters' ||
1134 ' (parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
1135 ' created_by, last_update_login) values (:1, :2, sysdate, :3, sysdate, ' ||
1136 ' :4, :5)';
1137
1138 msd_dem_common_utilities.log_message ('Inserting profile ' || g_master_org || ' into msd_dem_setup_parameters');
1139 EXECUTE IMMEDIATE x_sql USING g_master_org, x_master_org_value, g_user_id, g_user_id, g_login_id;
1140
1141 msd_dem_common_utilities.log_message ('Inserting profile ' || g_sr_category_set || ' into msd_dem_setup_parameters');
1142 EXECUTE IMMEDIATE x_sql USING g_sr_category_set, x_category_set_value, g_user_id, g_user_id, g_login_id;
1143
1144 COMMIT;
1145
1146 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_push_setup_parameters.push_legacy_setup_parameters - ' || sysdate);
1147
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 retcode := -1 ;
1151 errbuf := substr(SQLERRM,1,150);
1152 msd_dem_common_utilities.log_message ('Exception: msd_dem_push_setup_parameters.push_legacy_setup_parameters - ' || sysdate);
1153 msd_dem_common_utilities.log_message (errbuf);
1154 RETURN;
1155
1156 END PUSH_LEGACY_SETUP_PARAMETERS;
1157
1158
1159
1160
1161 END MSD_DEM_PUSH_SETUP_PARAMETERS;