[Home] [Help]
PACKAGE BODY: APPS.MSD_PUSH_SETUP_DATA
Source
1 package body MSD_PUSH_SETUP_DATA as
2 /* $Header: msdpstpb.pls 120.3 2006/07/12 06:05:23 sjagathe noship $ */
3 --
4 -- Global variables
5 G_DBLINK varchar2(30);
6 l_para_prof para_profile_list;
7 g_already_checked BOOLEAN := FALSE;
8 g_num_profile NUMBER;
9
10
11 /* Debug */
12 C_DEBUG Constant varchar2(1) := 'N';
13
14
15 -- Declaring Function
16 --
17 Function decode_profile_function ( p_profile_rec in para_profile) return varchar2;
18 Function get_multi_org_flag return varchar2;
19 Function get_profile_value ( p_profile_name in varchar2, p_DP_Server_flag in varchar2) return varchar2;
20 Function get_function_value(p_function_name in varchar2,
21 p_DP_Server_flag in varchar2) return varchar2;
22
23 Procedure push_profile (errbuf OUT NOCOPY VARCHAR2,
24 retcode OUT NOCOPY VARCHAR2,
25 p_instance_id in number);
26
27 Procedure push_organization (errbuf OUT NOCOPY VARCHAR2,
28 retcode OUT NOCOPY VARCHAR2,
29 p_instance_id in number);
30
31 Procedure Init;
32
33
34 Procedure show_line(p_sql in varchar2);
35 Procedure debug_line(p_sql in varchar2);
36
37
38 --
39
40 /* Usability Enhancements. Bug # 3509147. This function sets the value of profile MSD_CUSTOMER_ATTRIBUTE to NONE
41 if collecting for the first time */
42 procedure chk_customer_attribute(
43 errbuf OUT NOCOPY VARCHAR2,
44 retcode OUT NOCOPY VARCHAR2,
45 p_instance_id IN NUMBER) IS
46
47 Type c_setup_params is ref cursor;
48 x_cust_attribute varchar2(40);
49 x_dblink varchar2(128);
50 x_retcode number;
51 x_source_table VARCHAR2(50) ;
52 x_sql_stmt varchar2(4000);
53 x_num_params number := 0;
54 l_cur c_setup_params;
55 x_set_profile number;
56 x_profile_name VARCHAR2(50) := '''MSD_CUSTOMER_ATTRIBUTE''';
57 x_profile_value VARCHAR2(50) := '''NONE''';
58 x_profile_level VARCHAR2(50) := '''SITE''';
59
60 chk_customer_attr_log_msg VARCHAR2(4000) := 'The collection of customers, ship to locations (customer sites),
61 regions, and other level values in the geography dimension from
62 e-business source instance to Demand Planning depends on the profile,
63 MSD:Customer Attribute. It is recommended to set the profile to
64 selectively collect these level values. To collect all the available
65 geography dimension level values, please clear out the dummy profile
66 value. Until the profile value is set appropriately or cleared out, only
67 the dummy level value (other) will be collected into Demand Planning for
68 geography dimension.';
69
70 Begin
71
72 msd_common_utilities.get_db_link(p_instance_id, x_dblink, x_retcode);
73 if (x_retcode = -1) then
74 retcode :=-1;
75 errbuf := 'Error while getting db_link';
76 return;
77 end if;
78
79 x_source_table := 'MSD_SETUP_PARAMETERS'||x_dblink;
80 x_sql_stmt := 'SELECT COUNT(*) FROM '||x_source_table||' where instance_id = '||p_instance_id;
81
82 open l_cur for x_sql_stmt;
83 fetch l_cur into x_num_params;
84 close l_cur;
85
86 if x_num_params = 0 then
87 x_sql_stmt := 'Begin :l_out1 := MSD_SR_UTIL.set_customer_attr'||x_dblink||'('||x_profile_name||','
88 ||x_profile_value||','||x_profile_level||'); End;';
89 EXECUTE IMMEDIATE x_sql_stmt using OUT x_set_profile;
90 fnd_file.put_line(fnd_file.log, chk_customer_attr_log_msg);
91 If (x_set_profile=2) then
92 retcode :=-1;
93 errbuf := 'Error while Setting Value for Profile MSD_CUSTOMER_ATTRIBUTE';
94 return;
95 end if;
96 commit;
97 end if;
98
99 End chk_customer_attribute;
100
101 --
102
103 Procedure Init is
104
105 l_count NUMBER := 0;
106
107 BEGIN
108
109
110 -- Initialize parameter array
111 l_para_prof(1).profile_name := 'MSD_CATEGORY_SET_NAME';
112 l_para_prof(1).function_name := 'MSD_SR_UTIL.GET_CATEGORY_SET_ID';
113 l_para_prof(1).DP_Server_Flag := 'N';
114 l_para_prof(1).function_profile_code := 'F';
115
116 l_para_prof(2).profile_name := 'MSD_CONVERSION_TYPE';
117 l_para_prof(2).function_name := 'MSD_SR_UTIL.GET_CONVERSION_TYPE';
118 l_para_prof(2).DP_Server_Flag := 'N';
119 l_para_prof(2).function_profile_code := 'F';
120
121 l_para_prof(3).profile_name := 'MSD_CURRENCY_CODE';
122 l_para_prof(3).DP_Server_Flag := 'Y';
123 l_para_prof(3).function_profile_code := 'P';
124
125 l_para_prof(4).profile_name := 'MSD_MASTER_ORG';
126 l_para_prof(4).function_name := 'MSD_SR_UTIL.MASTER_ORGANIZATION';
127 l_para_prof(4).DP_Server_Flag := 'N';
128 l_para_prof(4).function_profile_code := 'F';
129
130 l_para_prof(5).profile_name := 'MSD_CUSTOMER_ATTRIBUTE';
131 l_para_prof(5).function_name := 'MSD_SR_UTIL.GET_CUSTOMER_ATTR';
132 l_para_prof(5).DP_Server_Flag := 'N';
133 l_para_prof(5).function_profile_code := 'F';
134
135 l_para_prof(6).profile_name := 'MSD_PLANNING_PERCENTAGE';
136 l_para_prof(6).DP_Server_Flag := 'Y';
137 l_para_prof(6).function_profile_code := 'P';
138
139 l_para_prof(7).profile_name := 'AS_FORECAST_CALENDAR';
140 l_para_prof(7).DP_Server_Flag := 'N';
141 l_para_prof(7).function_profile_code := 'P';
142
143 l_para_prof(8).profile_name := 'MSD_TWO_LEVEL_PLANNING';
144 l_para_prof(8).DP_Server_Flag := 'Y';
145 l_para_prof(8).function_profile_code := 'P';
146
147 /* BUG# 5383368 - SOP and EOL code cleanup
148 l_para_prof(9).profile_name := 'MSD_EOL_CATEGORY_SET_NAME';
149 l_para_prof(9).function_name := 'MSD_SR_UTIL.GET_EOL_CATEGORY_SET_ID';
150 l_para_prof(9).DP_Server_Flag := 'N';
151 l_para_prof(9).function_profile_code := 'F';
152 */
153
154 /* Bug# 4157588 */
155 l_para_prof(9).profile_name := 'MSD_ITEM_ORG';
156 l_para_prof(9).function_name := 'MSD_SR_UTIL.ITEM_ORGANIZATION';
157 l_para_prof(9).DP_Server_Flag := 'N';
158 l_para_prof(9).function_profile_code := 'F';
159
160 g_num_profile := l_para_prof.LAST;
161
162 END Init;
163
164
165
166 Procedure Push_data (
167 errbuf OUT NOCOPY varchar2,
168 retcode OUT NOCOPY varchar2,
169 p_instance_id IN number) is
170 --
171 --
172 l_retcode varchar2(10);
173 x_ret_val number;
174 l_count number;
175 l_push_profiles number;
176 l_err number;
177 l_prof_val varchar2(80);
178 l_parameter_value varchar2(80);
179 x_dblink varchar2(128);
180 x_retcode number;
181 v_sql_stmt varchar2(4000);
182 x_source_table varchar2(255);
183
184
185 Begin
186
187 x_ret_val := 0;
188 l_count := 0;
189 l_push_profiles := 0;
190 l_err := 0;
191
192 /* Initialization parameter values */
193 Init;
194
195
196 -- Find out if there are rows in msd_setup_parameters in the source instance
197 --
198 -- Call MSD_CONC_LOG_UTIL.Initialize for log file for concurrent program
199 --
200 msd_conc_log_util.Initilize(msd_conc_log_util.C_OUTPUT_TO_FNDFILE);
201 --
202
203 msd_common_utilities.get_db_link(p_instance_id, g_dblink, l_retcode);
204 if (l_retcode = -1) then
205 msd_conc_log_util.display_message('Instance id : ' || p_instance_id || ' not found ', msd_conc_log_util.C_FATAL_ERROR);
206 return;
207 end if;
208
209
210 -- log details
211 msd_conc_log_util.display_message('Demand Plan Push Setup Parameters', msd_conc_log_util.C_SECTION);
212 msd_conc_log_util.display_message(' ', msd_conc_log_util.C_HEADING);
213 msd_conc_log_util.display_message('Demand Plan Push Setup Program Parameters', msd_conc_log_util.C_HEADING);
214 msd_conc_log_util.display_message('-----------------------------------------', msd_conc_log_util.C_HEADING);
215 -- get instance dblink
216 --
217 msd_common_utilities.get_db_link(p_instance_id, g_dblink, l_retcode);
218 -- Check results
219 if (l_retcode = -1) then
220 -- Log Fatal Error
221 msd_conc_log_util.display_message('Instance id : ' || p_instance_id || ' not found ', msd_conc_log_util.C_FATAL_ERROR);
222 return;
223 else
224 -- Log success details
225 msd_conc_log_util.display_message('Instance ID : ' || p_instance_id, msd_conc_log_util.C_INFORMATION);
226 msd_conc_log_util.display_message('DB Link : ' || g_dblink , msd_conc_log_util.C_INFORMATION);
227 end if;
228
229 --
230 -- Call Push_profile
231 --
232 push_profile( errbuf, retcode, p_instance_id);
233
234 -- Call Push organizations
235 --
236 push_organization (errbuf, retcode, p_instance_id);
237 --
238
239 retcode := msd_conc_log_util.retcode;
240 --
241 msd_conc_log_util.display_message('Exiting with : ', msd_conc_log_util.Result);
242
243
244 --
245 Exception
246 When msd_conc_log_util.EX_FATAL_ERROR then
247 retcode := 2;
248 errbuf := substr( sqlerrm, 1, 80);
249 when others then
250 retcode := 2;
251 errbuf := substr( sqlerrm, 1, 80);
252 End Push_data;
253 --
254 Function decode_profile_function ( p_profile_rec in para_profile) return varchar2 is
255 --
256 l_ret_val varchar2(255);
257 Begin
258 --
259 if p_profile_rec.function_profile_code = 'P' then
260 -- Record is a Profile. Fetch profile value
261 l_ret_val := get_profile_value ( p_profile_rec.profile_name, p_profile_rec.DP_Server_flag);
262 elsif p_profile_rec.function_profile_code = 'F' then
263 -- Record is a function. Fetch return value
264 -- Note* Currently code supports call to function with no parameters
265 l_ret_val := get_function_value(p_profile_rec.function_name, p_profile_rec.DP_Server_flag);
266 end if;
267 --
268 return l_ret_val;
269 End decode_profile_function;
270
271 Function get_profile_value ( p_profile_name in varchar2, p_DP_Server_flag in varchar2) return varchar2 is
272 --
273 l_ret_val varchar2(255);
274 l_sql varchar2(100);
275 Begin
276 --
277 if p_DP_Server_flag = 'Y' then
278 --
279 l_ret_val := fnd_profile.value(p_profile_name);
280 --
281 else
282 --
283 l_sql := 'Begin :l_ret1 := fnd_profile.value' || G_DBLINK || '(''' || p_profile_name || '''); End;';
284 EXECUTE IMMEDIATE l_sql using OUT l_ret_val;
285 end if;
286 --
287 return l_ret_val;
288 --
289 End get_profile_value;
290 --
291 Function get_function_value(p_function_name in varchar2, p_DP_Server_flag in varchar2) return varchar2 is
292 l_ret_val varchar2(255);
293 l_sql varchar2(100);
294 Begin
295 if p_DP_Server_flag = 'Y' then
296 l_sql := 'Begin :l_out1 := ' || p_function_name || '; End;';
297 EXECUTE IMMEDIATE l_sql using OUT l_ret_val;
298 else
299 l_sql := 'Begin :l_out1 := ' || p_function_name || G_DBLINK || '; End;';
300 EXECUTE IMMEDIATE l_sql using OUT l_ret_val;
301 end if;
302 return l_ret_val;
303 End get_function_value;
304
305 Function get_multi_org_flag return varchar2 is
306 l_ret_val varchar2(255);
307 v_sql_stmt varchar2(100);
308 Begin
309
310 v_sql_stmt := 'Select multi_org_flag from fnd_product_groups' || G_DBLINK ||
311 ' where product_group_type = ''Standard''';
312
313 EXECUTE IMMEDIATE v_sql_stmt into l_ret_val;
314 return l_ret_val;
315
316 exception
317 when others then
318
319 l_ret_val := 'Y';
320 return l_ret_val;
321 End get_multi_org_flag;
322
323 --
324 Procedure push_profile ( errbuf OUT NOCOPY VARCHAR2,
325 retcode OUT NOCOPY VARCHAR2,
326 p_instance_id in number) is
327 --
328 --
329 l_retcode varchar2(10);
330 l_cnt BINARY_INTEGER:=0;
331 l_retval varchar2(255);
332 l_sql varchar2(2000);
333 l_err BINARY_INTEGER:=0;
334 l_warning BINARY_INTEGER:=0;
335 l_prof varchar2(255);
336 l_multi_org_flag varchar2(255);
337
338 --
339 --
340 Begin
341 -- Log
342 msd_conc_log_util.display_message('Push Profile', msd_conc_log_util.C_SECTION);
343 msd_conc_log_util.display_message('Action', msd_conc_log_util.C_HEADING);
344 msd_conc_log_util.display_message(rpad('-', 80, '-'), msd_conc_log_util.C_HEADING);
345
346 -- Read profiles/functions from the array and initialise on source instance
347 --
348 l_err := 0;
349
350 /* Get the parameter values */
351 FOR i IN l_para_prof.FIRST..l_para_prof.LAST LOOP
352 l_para_prof(i).parameter_value := decode_profile_function(l_para_prof(i));
353 msd_conc_log_util.display_message('Profile ' || l_para_prof(i).profile_name || ' : ' ||
354 l_para_prof(i).parameter_value, msd_conc_log_util.C_INFORMATION);
355 END LOOP;
356
357 IF ((l_para_prof(2).parameter_value is NULL) or
358 (l_para_prof(3).parameter_value is NULL) or
359 (l_para_prof(4).parameter_value is NULL)) THEN
360 l_err := 1;
361 END IF;
362
363
364 /* In case of multi org, l_para_prof(4) will have master org id
365 through master_organization function call even though
366 profile value is not specified. In this case, give warning to user
367 to confirm that master_org_id for the source will be the org_id from
368 master_organization function call, not from the source profile value */
369 IF (l_para_prof(4).parameter_value is not null) THEN
370 l_prof := get_profile_value('MSD_MASTER_ORG', 'N');
371 l_multi_org_flag := get_multi_org_flag;
372 IF (l_prof is null) THEN
373 IF (l_multi_org_flag = 'Y') THEN
374 l_warning := 1;
375 END IF;
376 END IF;
377 END IF;
378
379
380
381 /* If Two-Level Planning has not been set, then default it to NO */
382 IF (l_para_prof(8).parameter_value is NULL) THEN
383 msd_conc_log_util.display_message('Profile ' || l_para_prof(8).profile_name ||
384 ' is not defined. Defaulting this profile value to ',msd_conc_log_util.C_INFORMATION);
385 msd_conc_log_util.display_message('''Exclude family members with forecast control NONE''',
386 msd_conc_log_util.C_INFORMATION);
387 l_para_prof(8).parameter_value := 2;
388 END IF;
389
390 if ((l_err <> 1) and (l_warning = 1)) then
391
392 msd_conc_log_util.display_message('Profile ' || l_para_prof(4).profile_name ||
393 ' in the Source instance NOT SET !!!', msd_conc_log_util.C_INFORMATION);
394 msd_conc_log_util.display_message('The system has determined to use Organization Id = ' ||
395 l_para_prof(4).parameter_value || ' as the master org', msd_conc_log_util.C_INFORMATION);
396 msd_conc_log_util.display_message('If this is not the master org, please update the MSD_MASTER_ORG profile on the source',
397 msd_conc_log_util.C_WARNING);
398 msd_conc_log_util.display_message(' and rerun the Push Setup Parameters concurrent program', msd_conc_log_util.C_WARNING);
399 end if;
400
401
402 IF (l_err = 1) THEN
403 msd_conc_log_util.display_message('Please make sure that profiles ' ||
404 'MSD_CONVERSION_TYPE and MSD_MASTER_ORG are set in Source instance.', msd_conc_log_util.C_ERROR);
405 msd_conc_log_util.display_message(' and MSD_CURRENCY_CODE profile in the Planning Server are set.', msd_conc_log_util.C_ERROR);
406 ELSE
407 msd_conc_log_util.display_message('Deleting records from msd_setup_parameters in the Source instance', msd_conc_log_util.C_HEADING);
408 /* Truncate Source msd_setup_parameters */
409 l_sql := 'delete from msd_setup_parameters' || g_dblink;
410 EXECUTE IMMEDIATE l_sql;
411
412 msd_conc_log_util.display_message('Inserting profile into source msd_setup_parameters', msd_conc_log_util.C_INFORMATION);
413
414 /* Insert profiles in source msd_setup_parameters */
415 l_sql := 'insert into msd_setup_parameters' || g_dblink ||
416 ' (instance_id, parameter_name, parameter_value) values (:1, :2, :3)';
417
418 FOR j IN l_para_prof.FIRST..l_para_prof.LAST LOOP
419 EXECUTE IMMEDIATE l_sql using p_instance_id, l_para_prof(j).profile_name, l_para_prof(j).parameter_value;
420 END LOOP;
421 END IF;
422
423
424 commit;
425
426 EXCEPTION
427 when others then
428 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
429 errbuf := substr(SQLERRM,1,150);
430 retcode := -1 ;
431
432 END push_profile;
433
434
435
436 Procedure push_organization ( errbuf OUT NOCOPY VARCHAR2,
437 retcode OUT NOCOPY VARCHAR2,
438 p_instance_id in number) is
439 --
440 l_sql varchar2(2000);
441 --
442 Begin
443 -- Log
444 msd_conc_log_util.display_message('Push Organization', msd_conc_log_util.C_SECTION);
445 msd_conc_log_util.display_message('Action', msd_conc_log_util.C_HEADING);
446 msd_conc_log_util.display_message(rpad('-', 80, '-'), msd_conc_log_util.C_HEADING);
447 --
448 msd_conc_log_util.display_message('Deleting Organizations from source MSD_APP_INSTANCE_ORGS', msd_conc_log_util.C_INFORMATION);
449 --
450 -- Delete all organization records from MSD_APP_INSTANCE_ORGS
451 l_sql := 'Delete from MSD_APP_INSTANCE_ORGS' || g_dblink;
452 EXECUTE IMMEDIATE l_sql;
453 --
454 msd_conc_log_util.display_message('Creating Organizations into source MSD_APP_INSTANCE_ORGS', msd_conc_log_util.C_INFORMATION);
455 --
456 -- Create rows in MSD_APP_INSTANCE_ORGS, in source database, for all the enabled
457 -- organization in msc_instance_orgs
458 --
459 /* Bug# 4166487 Use dp_enabled_flag instead of enabled_flag */
460 l_sql := 'insert into MSD_APP_INSTANCE_ORGS' || g_dblink ||
461 '( instance_id, organization_id, last_update_date, last_updated_by, creation_date, ' ||
462 ' created_by, last_update_login, request_id, program_application_id, program_id, ' ||
463 ' program_update_date, attribute_category, attribute1, attribute2, attribute3, ' ||
464 ' attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, ' ||
465 ' attribute10, attribute11, attribute12, attribute13, attribute14, attribute15) ' ||
466 'select sr_instance_id, organization_id, last_update_date, last_updated_by, ' ||
467 ' creation_date, created_by, last_update_login, request_id, program_application_id,' ||
468 ' program_id, program_update_date, attribute_category, attribute1, attribute2, ' ||
469 ' attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,' ||
470 ' attribute10, attribute11, attribute12, attribute13, attribute14, attribute15' ||
471 ' from msc_instance_orgs where sr_instance_id = :id and nvl(dp_enabled_flag, enabled_flag) = ''1''';
472 EXECUTE IMMEDIATE l_sql using p_instance_id;
473 --
474
475 commit;
476
477 EXCEPTION
478 when others then
479 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
480 errbuf := substr(SQLERRM,1,150);
481 retcode := -1 ;
482
483 end push_organization;
484
485
486
487 procedure chk_push_setup( errbuf OUT NOCOPY VARCHAR2,
488 retcode OUT NOCOPY VARCHAR2,
489 p_instance_id IN NUMBER ) IS
490
491 l_count number:= 0;
492 v_sql_stmt varchar2(4000);
493
494 TYPE Source_Profile_Value IS REF CURSOR;
495 c_source_profile Source_Profile_Value;
496
497 TYPE parameter_name_tab IS TABLE OF MSD_SETUP_PARAMETERS.parameter_name%TYPE;
498 TYPE parameter_value_tab IS TABLE OF MSD_SETUP_PARAMETERS.parameter_value%TYPE;
499
500 a_parameter_name parameter_name_tab;
501 a_parameter_value parameter_value_tab;
502
503 x_parameter_name varchar2(200);
504 x_parameter_value varchar2(200);
505 j number := 1;
506
507 b_match BOOLEAN := FALSE;
508 b_need_to_push BOOLEAN := FALSE;
509
510 TYPE org_diff IS REF CURSOR;
511 c_org org_diff;
512
513
514 Begin
515
516 /* Check g_pushed, session variable. If it has been
517 pushed within the session, then don't push it again */
518 IF (g_already_checked = FALSE) THEN
519
520 Init;
521
522 /* Set the value of profile msd_customer_attribute to some dummy value if collecting for the first time */
523 chk_customer_attribute( errbuf,
524 retcode,
525 p_instance_id);
526
527 /* Get db_link */
528 msd_common_utilities.get_db_link(p_instance_id, g_dblink, retcode);
529 IF retcode <> 0 THEN
530 msd_conc_log_util.display_message('Instance id : ' || p_instance_id ||
531 ' not found ', msd_conc_log_util.C_FATAL_ERROR);
532 return;
533 END IF;
534
535 /* First, Check the setup parameters, and then check organization */
536 /* Get the profile values */
537 FOR i IN l_para_prof.FIRST..l_para_prof.LAST LOOP
538 l_para_prof(i).parameter_value := decode_profile_function(l_para_prof(i));
539 END LOOP;
540
541 v_sql_stmt := 'SELECT parameter_name, parameter_value FROM MSD_SETUP_PARAMETERS'||g_dblink ||
542 ' where instance_id = ' || p_instance_id;
543
544 /* Does not work on Oracle 8i
545 * OPEN c_source_profile FOR v_sql_stmt;
546 * FETCH c_source_profile BULK COLLECT INTO a_parameter_name, a_parameter_value;
547 */
548
549 a_parameter_name := parameter_name_tab(null);
550 a_parameter_value := parameter_value_tab(null);
551
552 OPEN c_source_profile FOR v_sql_stmt;
553 LOOP
554 FETCH c_source_profile INTO x_parameter_name, x_parameter_value;
555 EXIT WHEN c_source_profile%NOTFOUND;
556 a_parameter_name.extend;
557 a_parameter_value.extend;
558 a_parameter_name(j) := x_parameter_name;
559 a_parameter_value(j) := x_parameter_value;
560 j := j + 1;
561 END LOOP;
562
563 IF (a_parameter_name.exists(1)) THEN
564 /* Check if all the profile values are matching or not.
565 IF any of them are not matching then exit immediately */
566
567 FOR j IN l_para_prof.FIRST..l_para_prof.LAST LOOP
568 FOR i IN a_parameter_name.FIRST..a_parameter_name.LAST LOOP
569 IF (a_parameter_name(i) = l_para_prof(j).profile_name) THEN
570 IF ( (a_parameter_value(i) = l_para_prof(j).parameter_value) or
571 (a_parameter_value(i) is null and l_para_prof(j).parameter_value is null) ) THEN
572 b_match := TRUE;
573 exit; /* Exit inner loop when there is a maching. Goto
574 the next profile value to compare */
575 END IF;
576 END IF;
577 END LOOP;
578
579 IF (b_match = FALSE) THEN
580 b_need_to_push := TRUE;
581 /* Exit the loop - difference in parameter value found */
582 exit;
583 END IF;
584
585 /* ReInitialize the variable */
586 b_match := FALSE;
587
588 END LOOP;
589 ELSE
590 /* IF there is no profile value exist in the source */
591 b_need_to_push := TRUE;
592
593 END IF; /* IF (a_parameter_name.exists(1)) THEN */
594
595
596 /* Check Organization only if b_need_to_push is false.
597 Otherwise, we need to execute push_setup_parameters anyway.
598 doesn't need to check org */
599 /* Bug# 4166487 Use dp_enabled_flag instead of enabled_flag */
600 IF (b_need_to_push = FALSE) THEN
601 v_sql_stmt := ' SELECT count(*) from ( ' ||
602 ' ( SELECT organization_id FROM msc_instance_orgs ' ||
603 ' WHERE sr_instance_id = ' || p_instance_id ||
604 ' and nvl(dp_enabled_flag, enabled_flag) = ''1''' ||
605 ' MINUS ' ||
606 ' SELECT organization_id FROM msd_app_instance_orgs' || g_dblink ||
607 ' WHERE instance_id = ' || p_instance_id || ') ' ||
608 ' UNION ALL ' ||
609 ' ( SELECT organization_id FROM msd_app_instance_orgs' || g_dblink ||
610 ' WHERE instance_id = ' || p_instance_id ||
611 ' MINUS ' ||
612 ' SELECT organization_id FROM msc_instance_orgs ' ||
613 ' WHERE sr_instance_id = ' || p_instance_id ||
614 ' and nvl(dp_enabled_flag, enabled_flag) = ''1'') ' ||
615 ' )';
616
617
618 OPEN c_org FOR v_sql_stmt;
619 FETCH c_org INTO l_count;
620 CLOSE c_org;
621
622 IF l_count <> 0 THEN
623 b_need_to_push := TRUE;
624 END IF;
625 END IF; /* (b_need_to_push = FALSE) */
626
627 if (b_need_to_push) then
628 push_data(errbuf,retcode,p_instance_id);
629 /* retcode will be 1 in case of WARNING.
630 Do not error out in case of WARNING. Proceed as it was normal */
631 IF nvl(retcode,0) = 1 THEN
632 retcode := 0;
633 END IF;
634 IF nvl(retcode,0) <> 0 THEN
635 show_line('---------------------------------------------------------------------------------------------' );
636 show_line('Profiles not setup properly. Please check Profiles on Planning Server and re-run collections.');
637 show_line('---------------------------------------------------------------------------------------------' );
638 return;
639 end if;
640 end if;
641
642
643 /* Set this session variable to TRUE so that within this sesssion,
644 we don't need to check profile values had been pushed into
645 the source or not */
646
647 g_already_checked := TRUE;
648
649 END IF; /* if g_already_checked = FALSE */
650
651 EXCEPTION
652 when others then
653 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
654 errbuf := substr(SQLERRM,1,150);
655 retcode := -1 ;
656
657 End chk_push_setup;
658
659 /*------------------------- DWK For the debugging purpose ---------------------*/
660 Procedure show_line(p_sql in varchar2) is
661 i number:=1;
662 Begin
663 while i<= length(p_sql)
664 loop
665 -- dbms_output.put_line (substr(p_sql, i, 255));
666 fnd_file.put_line(fnd_file.log,substr(p_sql, i, 255));
667 null;
668 i := i+255;
669 end loop;
670 End;
671
672
673 Procedure debug_line(p_sql in varchar2)is
674 Begin
675 if c_debug = 'Y' then
676 show_line(p_sql);
677 end if;
678 End;
679
680
681 END MSD_PUSH_SETUP_DATA;