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