[Home] [Help]
PACKAGE BODY: APPS.MSC_PHUB_UTIL
Source
1 PACKAGE BODY msc_phub_util AS
2 /* $Header: MSCHBUTB.pls 120.56.12020000.2 2012/10/11 14:01:48 wexia ship $ */
3
4 g_log_dir varchar2(250) := null;
5 g_log_file varchar2(250) := null;
6 g_log_level number := 1;
7
8 function get_conversion_rate(p_func_currency varchar2,p_sr_instance_id number, p_date date) return number is
9 l_currency_rate number;
10 l_reporting_currency varchar2(20) := get_reporting_currency_code;
11 begin
12 /*select CONV_RATE
13 into l_currency_rate
14 from MSC_CURRENCY_CONVERSIONS
15 where FROM_CURRENCY = p_func_currency
16 and TO_CURRENCY = l_reporting_currency
17 and SR_INSTANCE_ID = p_sr_instance_id
18 and CONV_DATE = p_date;*/
19 return 1;--l_currency_rate;
20 end get_conversion_rate;
21
22 function get_conversion_rate(p_sr_instance_id number, p_organization_id number, p_date date) return number is
23 l_func_currency varchar2(20);
24 l_rate number;
25 begin
26 /*select currency_code
27 into l_func_currency
28 from msc_trading_partners
29 where sr_instance_id = p_sr_instance_id
30 and organization_id = p_organization_id
31 and partner_type = 3;*/
32 l_rate := msc_phub_util.get_conversion_rate(l_func_currency, p_sr_instance_id, p_date);
33 return l_rate;
34 end get_conversion_rate;
35
36 function get_planning_hub_message(p_mesg_code varchar2) return varchar2 is
37 l_message varchar2(100);
38 begin
39 FND_MESSAGE.SET_NAME('MSC', p_mesg_code);
40 l_message :=FND_MESSAGE.GET;
41 return l_message;
42 end get_planning_hub_message;
43
44 function get_reporting_currency_code return varchar2 is
45 begin
46 if g_rpt_curr_code is null then
47 g_rpt_curr_code := nvl(FND_PROFILE.VALUE('MSC_HUB_CUR_CODE_RPT'),'USD');
48 end if;
49
50 return g_rpt_curr_code;
51 end get_reporting_currency_code;
52
53 FUNCTION get_exception_group(p_exception_type_id in number) return varchar2 is
54 l_exception_group varchar2(300);
55 l_exception_group_id number;
56
57 CURSOR exception_group_meaning(p_exception_group_id NUMBER) IS
58 select meaning
59 from mfg_lookups
60 where lookup_type = 'MSC_EXCEPTION_GROUP'
61 and lookup_code = p_exception_group_id;
62 BEGIN
63 l_exception_group_id:= case
64 when p_exception_type_id in (11,5,12,105,30,48,84,29) then 1
65 when p_exception_type_id in (31,32,33,34,43,44,49,114) then 2
66 when p_exception_type_id in (2,3,20,115) then 3
67 when p_exception_type_id in (6,7,8,10,9,47,62,63,64,65,66,70,71) then 4
68 when p_exception_type_id in (13,14,113,23,24,25,26,27,35,41,42,15,16,69,52) then 5
69 when p_exception_type_id in (28,112,21,22,36,37,45,46,90,91)then 6
70 when p_exception_type_id in (40,38,39,50,51,61)then 7
71 when p_exception_type_id in (17,18,19)then 8
72 when p_exception_type_id in (53,54,55,56,57,58,67,59,60,72,77)then 11
73 when p_exception_type_id in (85,86)then 12
74 when p_exception_type_id in (92,93)then 13
75 when p_exception_type_id in (87,88)then 14
76 when p_exception_type_id in (150,151,152) then 15
77 when p_exception_type_id in (160,161,162) then 16
78 when p_exception_type_id in (170,171,172,173) then 17
79 when p_exception_type_id in (180,181) then 18
80 when p_exception_type_id in (190,191) then 19
81 when p_exception_type_id in (200,201)then 20
82 else 1
83 end;
84
85 open exception_group_meaning(l_exception_group_id);
86 fetch exception_group_meaning into l_exception_group;
87 close exception_group_meaning;
88
89 return l_exception_group;
90 END get_exception_group;
91
92 FUNCTION get_exception_drp_group(p_exception_type_id in number) return varchar2 is
93 l_exception_group varchar2(300);
94 l_exception_group_id number;
95 CURSOR exception_group_meaning(p_exception_group_id NUMBER) IS
96 select meaning
97 from mfg_lookups
98 where lookup_type = 'MSC_EXCEPTION_GROUP'
99 and lookup_code = p_exception_group_id;
100 BEGIN
101 l_exception_group_id:= case
102 when p_exception_type_id in (11,5,12,105,30,48,84,29) then 1
103 when p_exception_type_id in (31,32,33,34,43,44,49,114,77,71) then 2
104 when p_exception_type_id in (2,3,20,115,73,74,75) then 3
105 when p_exception_type_id in (6,7,8,10,9,47,62,63,64,65,66,70,76,95,13,14) then 4
106 when p_exception_type_id in (113,23,24,25,26,27,35,41,42,15,16,69,52) then 5
107 when p_exception_type_id in (28,112,21,22,36,45,46,90,91)then 6
108 when p_exception_type_id in (40,38,39,50,51,61,78,79,80,81)then 7
109 when p_exception_type_id in (17,18,19)then 8
110 when p_exception_type_id in (37,53,54,55,56,57,58,67,59,60,72,82)then 11
111 when p_exception_type_id in (85,86)then 12
112 when p_exception_type_id in (92,93)then 13
113 when p_exception_type_id in (87,88)then 14
114 when p_exception_type_id in (150,151,152) then 15
115 when p_exception_type_id in (160,161,162) then 16
116 when p_exception_type_id in (170,171,172,173) then 17
117 when p_exception_type_id in (180,181) then 18
118 when p_exception_type_id in (190,191) then 19
119 when p_exception_type_id in (200,201)then 20
120 else 1
121 end;
122 open exception_group_meaning(l_exception_group_id);
123 fetch exception_group_meaning into l_exception_group;
124 close exception_group_meaning;
125 return l_exception_group;
126 END get_exception_drp_group;
127
128 function get_list_price(p_plan_id number,p_inst_id number,p_org_id number, p_item_id number) return number is
129
130 l_list_price number;
131 begin
132 select nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100))
133 into l_list_price
134 from msc_system_items msi
135 where
136 msi.plan_id =p_plan_id
137 and msi.sr_instance_id = p_inst_id
138 and msi.organization_id = p_org_id
139 and msi.inventory_item_id = p_item_id;
140 return l_list_price;
141 end get_list_price;
142
143 function is_plan_constrained (l_daily number,
144 l_weekly number,
145 l_monthly number,
146 l_dailym number,
147 l_weeklym number,
148 l_monthlym number) return number is
149 begin
150
151 if l_daily = 1 or l_weekly =1 or l_monthly =1 or l_dailym = 1 or l_weeklym =1 or l_monthlym =1 then
152 return SYS_YES;
153 else
154 return SYS_NO;
155 end if;
156
157 end is_plan_constrained;
158
159 FUNCTION is_plan_constrained(p_plan_id number) return number is
160 l_plan_constrained number;
161 begin
162 select count(1) into l_plan_constrained
163 from
164 msc_plans mp
165 where
166 mp.plan_id = p_plan_id
167 and( nvl(mp.daily_resource_constraints,0 ) = 1
168 or nvl(mp.weekly_resource_constraints,0) = 1
169 or nvl(mp.period_resource_constraints,0) = 1
170 or nvl(mp.daily_material_constraints,0 ) = 1
171 or nvl(mp.weekly_material_constraints,0) = 1
172 or nvl(mp.period_material_constraints,0) = 1);
173
174 if l_plan_constrained = 0 or p_plan_id = -1 then
175 l_plan_constrained := 2;
176 end if;
177
178 return l_plan_constrained;
179 end is_plan_constrained;
180
181 FUNCTION get_plan_type(p_plan_id number) return number is
182 l_plan_type number;
183 begin
184 select CURR_PLAN_TYPE
185 into l_plan_type
186 from
187 msc_plans
188 where
189 plan_id = p_plan_id;
190 return l_plan_type;
191 end get_plan_type;
192
193 FUNCTION get_user_name(p_user_id number) return varchar2 is
194 l_user_name varchar2(80);
195 begin
196 select distinct u.user_name
197 into l_user_name
198 from fnd_user u, fnd_user_resp_groups g
199 where u.user_id=g.user_id
200 and g.responsibility_application_id=724
201 and sysdate between u.start_date and nvl(u.end_date, sysdate)
202 and u.user_id=p_user_id;
203
204 return l_user_name;
205
206 exception
207 when others then
208 return fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED');
209 end get_user_name;
210
211 procedure validate_icx_session(p_icx_cookie varchar2, p_user varchar2, p_pwd varchar2) is
212 l_retval varchar2(1);
213 SECURITY_CONTEXT_INVALID exception;
214
215 cursor c_user_info (ll_user varchar2) is
216 select furg.user_id, furg.responsibility_id, responsibility_application_id
217 from fnd_user_resp_groups furg,
218 fnd_user fu,
219 fnd_responsibility fr
220 where furg.user_id = fu.user_id
221 and furg.responsibility_id = fr.responsibility_id
222 and furg.responsibility_application_id = fr.application_id
223 --and fu.user_name = 'APCC_ADMIN'
224 and fu.user_name = ll_user
225 and fr.application_id = 724;
226
227 cursor c_admin_info (ll_user varchar2) is
228 select furg.user_id, furg.responsibility_id, responsibility_application_id
229 from fnd_user_resp_groups furg,
230 fnd_user fu,
231 fnd_responsibility fr
232 where furg.user_id = fu.user_id
233 and furg.responsibility_id = fr.responsibility_id
234 and furg.responsibility_application_id = fr.application_id
235 --and fu.user_name = 'APCC_ADMIN'
236 and fu.user_name = ll_user
237 and fr.responsibility_key = 'APS_SCN_PLN';
238
239 l_user_id number;
240 l_resp_id number;
241 l_resp_app_id number;
242
243 procedure println (p_msg varchar2) is
244 begin
245 null;
246 --dbms_output.put_line(p_msg);
247 end println;
248 begin
249 println('icx cookie value is cookie user pwd - '|| p_icx_cookie ||' - '||p_user||' - '||p_pwd); commit;
250
251 --l_retval := fnd_web_sec.validate_login(p_user, p_pwd);
252 l_retval := 'Y';
253 println('icx cookie value is valid_login - '||l_retval); commit;
254
255 if p_icx_cookie <> '-1' then
256 app_session.validate_icx_session(p_icx_cookie);
257 elsif (p_user is not null) then
258 if (p_user in ('weblogic','BISystemUser','APCC_ADMIN','Administrator')) then
259 open c_admin_info('APCC_ADMIN');
260 fetch c_admin_info into l_user_id, l_resp_id, l_resp_app_id;
261 close c_admin_info;
262 else
263 open c_user_info(p_user);
264 fetch c_user_info into l_user_id, l_resp_id, l_resp_app_id;
265 close c_user_info;
266 end if;
267 if (l_user_id is null or l_resp_id is null or l_resp_app_id is null) then
268 raise SECURITY_CONTEXT_INVALID;
269 end if;
270 fnd_global.apps_initialize ( user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_resp_app_id);
271 else
272 raise SECURITY_CONTEXT_INVALID;
273 end if;
274 end validate_icx_session;
275
276 procedure set_log_file(p_log_dir varchar2, p_log_file varchar2) is
277 l_log utl_file.file_type := null;
278 begin
279 if (p_log_dir is null or p_log_file is null) then
280 g_log_dir := null;
281 g_log_file := null;
282 else
283 g_log_dir := p_log_dir;
284 g_log_file := p_log_file;
285 end if;
286 end set_log_file;
287
288 procedure set_log_level(p_level number) is
289 begin
290 g_log_level := p_level;
291 end set_log_level;
292
293 function log_file return varchar2 is
294 begin
295 init_log;
296 if (g_log_dir is null) then
297 return null;
298 else
299 return g_log_dir||'/'||g_log_file;
300 end if;
301 end log_file;
302
303 procedure init_log is
304 l_log_dir varchar2(1024) := null;
305 l_log_file varchar2(20) := 'apcc.log';
306 l_debug number := fnd_profile.value('MSC_APCC_DEBUG_MODE');
307 l_sql varchar2(1000);
308 begin
309 if (l_debug = 1) then
310 l_sql :=
311 ' select dir'||
312 ' from'||
313 ' (select trim(p.value) dir'||
314 ' from v$parameter2 p, dba_directories d'||
315 ' where p.name=''utl_file_dir'''||
316 ' and d.directory_name(+)=''ECX_UTL_LOG_DIR_OBJ'''||
317 ' and d.owner(+)=''SYS'''||
318 ' and trim(p.value)=d.directory_path(+)'||
319 ' order by d.directory_name'||
320 ' )'||
321 ' where rownum=1';
322
323 execute immediate l_sql into l_log_dir;
324 set_log_file(l_log_dir, l_log_file);
325 set_log_level(2);
326 else
327 set_log_file(null, null);
328 set_log_level(1);
329 end if;
330
331 exception
332 when others then null;
333 end init_log;
334
335 procedure delete_log is
336 begin
337 utl_file.fremove(g_log_dir, g_log_file);
338 exception
339 when others then null;
340 end delete_log;
341
342 procedure log(p_message varchar2) is
343 begin
344 log(1, p_message);
345 end log;
346
347 procedure log_sql(p_message varchar2) is
348 begin
349 log(2, p_message);
350 end log_sql;
351
352 procedure log(p_level number, p_message varchar2)
353 is
354 t timestamp;
355 l_log utl_file.file_type := null;
356 begin
357 if (g_log_level < p_level) then
358 return;
359 end if;
360
361 select systimestamp into t from dual;
362 --dbms_output.put_line(to_char(t, 'YYYY-MM-DD HH24:MI:SS')||': '||p_message); --xxx
363 fnd_file.put_line(fnd_file.log, to_char(t, 'YYYY-MM-DD HH24:MI:SS')||': '||p_message);
364
365 if (g_log_file is not null) then
366 l_log := utl_file.fopen(g_log_dir, g_log_file, 'a', 16384);
367 utl_file.put_line(l_log, to_char(t, 'YYYY-MM-DD HH24:MI:SS')||': '||p_message);
368 utl_file.fflush(l_log);
369 utl_file.fclose(l_log);
370 end if;
371 end log;
372
373 function suffix(p_dblink varchar2) return varchar2 is
374 begin
375 if (p_dblink is null) then
376 return null;
377 end if;
378 return '@'||p_dblink;
379 end;
380
381 function report_decode_error(p_staging_table varchar2, p_st_transaction_id number,
382 p_error_code number, p_columns varchar2)
383 return number
384 is
385 l_sql varchar2(1000);
386 c sys_refcursor;
387 s varchar2(200);
388 l_merged_columns varchar2(1000);
389 n number;
390 t dbms_utility.uncl_array;
391 i number;
392 l_result number := 0;
393 l_rowcount number;
394 begin
395 l_sql :=
396 ' update '||p_staging_table||' set error_code=:error_code'||
397 ' where st_transaction_id=:p_st_transaction_id and error_code is null';
398 execute immediate l_sql using p_error_code, p_st_transaction_id;
399 l_rowcount := sql%rowcount;
400 commit;
401
402 if (l_rowcount > 0) then
403 l_result := 1;
404 fnd_message.set_name('MSC', 'MSC_APCC_CONV_E02');
405 fnd_message.set_token('COLUMNS', p_columns);
406 log(fnd_message.get);
407 dbms_utility.comma_to_table(p_columns, n, t);
408 l_merged_columns := t(1); i := 2;
409 while (i <= n) loop
410 l_merged_columns := l_merged_columns||'||'',''||'||t(i);
411 i := i + 1;
412 end loop;
413
414 l_sql :=
415 ' select distinct '||l_merged_columns||' from '||p_staging_table||
416 ' where st_transaction_id=:p_st_transaction_id and error_code=:error_code';
417
418 open c for l_sql using p_st_transaction_id, p_error_code;
419 loop
420 fetch c into s;
421 exit when c%notfound;
422 log(s);
423 end loop;
424 close c;
425 end if;
426 return l_result;
427
428 exception
429 when others then
430 log('msc_phub_util.report_decode_error.exception: '||sqlerrm);
431 return 1;
432 end report_decode_error;
433
434 function decode_organization_key(p_staging_table varchar2, p_st_transaction_id number,
435 p_def_instance_code varchar2,
436 p_sr_instance_id_col varchar2, p_organization_id_col varchar2, p_organization_code_col varchar2)
437 return number
438 is
439 l_sql varchar2(1000);
440 begin
441 l_sql :=
442 ' update '||p_staging_table||' f set (error_code, '||p_sr_instance_id_col||', '||p_organization_id_col||') ='||
443 ' (select 0, d.sr_instance_id, d.sr_tp_id from '||
444 ' (select mtp.sr_instance_id, mtp.sr_tp_id,'||
445 ' mtp.organization_code, mai.instance_code'||
446 ' from msc_trading_partners mtp, msc_apps_instances mai'||
447 ' where mtp.sr_instance_id=mai.instance_id'||
448 ' and mtp.partner_type=3) d'||
449 ' where f.'||p_organization_code_col||'=d.organization_code'||
450 ' and (instr(f.'||p_organization_code_col||', '':'')>0 '||
451 ' or nvl('''||p_def_instance_code||''', d.instance_code)=d.instance_code)'||
452 ' and rownum=1)'||
453 ' where f.st_transaction_id=:p_st_transaction_id'||
454 ' and f.error_code = 0'||
455 ' and f.'||p_organization_code_col||' is not null';
456
457 execute immediate l_sql using p_st_transaction_id;
458 commit;
459 return report_decode_error(p_staging_table, p_st_transaction_id, conv_key_err_organization, p_organization_code_col);
460
461 exception
462 when others then
463 log('msc_phub_util.decode_organization_key.exception: '||sqlerrm);
464 return 1;
465 end decode_organization_key;
466
467 function decode_item_key(p_staging_table varchar2, p_st_transaction_id number,
468 p_item_id_col varchar2, p_item_name_col varchar2)
469 return number
470 is
471 l_sql varchar2(1000);
472 begin
473 l_sql :=
474 ' update '||p_staging_table||' f set (error_code, '||p_item_id_col||') ='||
475 ' (select 0, d.inventory_item_id from msc_items d'||
476 ' where d.item_name=f.'||p_item_name_col||')'||
477 ' where f.st_transaction_id=:p_st_transaction_id'||
478 ' and f.error_code = 0'||
479 ' and f.'||p_item_name_col||' is not null';
480
481 execute immediate l_sql using p_st_transaction_id;
482 commit;
483 return report_decode_error(p_staging_table, p_st_transaction_id, conv_key_err_item, p_item_name_col);
484
485 exception
486 when others then
487 log('msc_phub_util.decode_item_key.exception: '||sqlerrm);
488 return 1;
489 end decode_item_key;
490
491 function decode_item_key2(p_staging_table varchar2, p_st_transaction_id number,
492 p_item_id_col varchar2, p_sr_instance_id_col varchar2,
493 p_category_instance_code_col varchar2,
494 p_category_name_col varchar2, p_item_name_col varchar2)
495 return number
496 is
497 l_sql varchar2(1000);
498 begin
499 l_sql :=
500 ' update '||p_staging_table||' f set (error_code, '||p_sr_instance_id_col||') ='||
501 ' (select 0, d.instance_id from msc_apps_instances d'||
502 ' where d.instance_code=f.'||p_category_instance_code_col||')'||
503 ' where f.st_transaction_id=:p_st_transaction_id'||
504 ' and f.error_code = 0'||
505 ' and f.'||p_category_instance_code_col||' is not null';
506 execute immediate l_sql using p_st_transaction_id;
507 commit;
508
509 l_sql :=
510 ' update '||p_staging_table||' f set (error_code, '||p_item_id_col||') ='||
511 ' (select 0, -sr_category_id from msc_phub_categories_mv d'||
512 ' where d.category_name=f.'||p_category_name_col||' and d.sr_instance_id=f.'||p_sr_instance_id_col||')'||
513 ' where f.st_transaction_id=:p_st_transaction_id'||
514 ' and f.error_code = 0'||
515 ' and f.'||p_item_name_col||' is null and f.'||p_category_name_col||' is not null';
516 execute immediate l_sql using p_st_transaction_id;
517 commit;
518 return report_decode_error(p_staging_table, p_st_transaction_id, conv_key_err_item, p_item_name_col);
519
520 exception
521 when others then
522 log('msc_phub_util.decode_item_key2.exception: '||sqlerrm);
523 return 1;
524 end decode_item_key2;
525
526 function decode_category_key(p_staging_table varchar2, p_st_transaction_id number)
527 return number
528 is
529 l_sql varchar2(1000);
530 l_category_set_id number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
531 begin
532 l_sql :=
533 ' update '||p_staging_table||' f set (error_code, category_set_id, category_instance_id) ='||
534 ' (select 0, '||l_category_set_id||', d.instance_id from msc_apps_instances d'||
535 ' where d.instance_code=f.category_instance_code)'||
536 ' where f.st_transaction_id=:p_st_transaction_id'||
537 ' and f.error_code = 0'||
538 ' and f.category_instance_code is not null';
539 execute immediate l_sql using p_st_transaction_id;
540 commit;
541
542 l_sql :=
543 ' update '||p_staging_table||' f set (error_code, sr_category_id) ='||
544 ' (select 0, d.sr_category_id from msc_phub_categories_mv d'||
545 ' where d.category_set_id=f.category_set_id'||
546 ' and d.sr_instance_id=f.category_instance_id'||
547 ' and d.category_name=f.category_name)'||
548 ' where f.st_transaction_id=:p_st_transaction_id'||
549 ' and f.error_code = 0'||
550 ' and f.category_name is not null';
551
552 execute immediate l_sql using p_st_transaction_id;
553 commit;
554 return report_decode_error(p_staging_table, p_st_transaction_id, conv_key_err_category,
555 'category_set_id,category_instance_id,category_name');
556
557 exception
558 when others then
559 log('msc_phub_util.decode_category_key.exception: '||sqlerrm);
560 return 1;
561 end decode_category_key;
562
563 function decode_customer_key(p_staging_table varchar2, p_st_transaction_id number,
564 p_customer_id_col varchar2,
565 p_customer_site_id_col varchar2,
566 p_sr_instance_id_col varchar2,
567 p_region_id_col varchar2,
568 p_customer_name_col varchar2,
569 p_customer_site_code_col varchar2,
570 p_zone_col varchar2)
571 return number
572 is
573 l_sql varchar2(16384);
574 l_unassigned varchar2(30) := fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED');
575 l_result_cols varchar2(100) := '';
576 l_source_cols varchar2(100) := '';
577 l_report_cols varchar2(100) := '';
578 l_where1 varchar2(1024) := '';
579 l_where2 varchar2(1024) := '';
580 l_where3 varchar2(1024) := '';
581 begin
582 if p_customer_id_col is not null then
583 l_result_cols := l_result_cols||', '||p_customer_id_col||', '||p_customer_site_id_col;
584 l_source_cols := l_source_cols||', d.customer_id, d.customer_site_id';
585 l_report_cols := l_report_cols||', '||p_customer_name_col||','||p_customer_site_code_col;
586 l_where1 := 'nvl(f.'||p_customer_name_col||','''||l_unassigned||''')<>'''||l_unassigned||''''||
587 ' and d.customer_name=f.'||p_customer_name_col||
588 ' and (d.customer_site=f.'||p_customer_site_code_col||
589 ' or (f.'||p_customer_site_code_col||' is null and d.customer_site_id=-23453))';
590
591 l_where3 := ' nvl(f.'||p_customer_name_col||','''||l_unassigned||''')='''||l_unassigned||''''||
592 ' and d.customer_id=-23453';
593 else
594 l_where1 := '1=2';
595 end if;
596
597 if p_region_id_col is not null then
598 l_result_cols := l_result_cols||', '||p_region_id_col;
599 l_source_cols := l_source_cols||', d.region_id';
600 l_report_cols := l_report_cols||', '||p_zone_col;
601
602 l_where2 := 'nvl(f.'||p_zone_col||','''||l_unassigned||''')<>'''||l_unassigned||''''||
603 ' and d.zone=f.'||p_zone_col||
604 ' and d.sr_instance_id=f.'||p_sr_instance_id_col;
605
606 if p_customer_id_col is not null then
607 l_where2 := l_where2||' and nvl(f.'||p_customer_name_col||','''||l_unassigned||''')='''||l_unassigned||'''';
608 l_where3 := l_where3||' and';
609 end if;
610 l_where3 := l_where3||' nvl(f.'||p_zone_col||','''||l_unassigned||''')='''||l_unassigned||''''||
611 ' and d.region_id=-23453';
612 else
613 l_where2 := '1=2';
614 if p_customer_id_col is null then
615 l_where3 := '1=2';
616 end if;
617 end if;
618
619 l_sql :=
620 ' update '||p_staging_table||' f set (error_code, '||substr(l_result_cols,3)||') ='||
621 ' (select 0, '||substr(l_source_cols,3)||
622 ' from msc_phub_customers_mv d'||
623 ' where (('||l_where1||') or ('||l_where2||') or ('||l_where3||'))'||
624 ' and rownum=1)'||
625 ' where f.st_transaction_id=:p_st_transaction_id and f.error_code=0';
626 --log(l_sql);
627 execute immediate l_sql using p_st_transaction_id;
628 commit;
629
630 return report_decode_error(p_staging_table, p_st_transaction_id, conv_key_err_customer, substr(l_report_cols,3));
631
632 exception
633 when others then
634 log('msc_phub_util.decode_customer_key.exception: '||sqlerrm);
635 return 1;
636 end decode_customer_key;
637
638 function decode_supplier_key(p_staging_table varchar2, p_st_transaction_id number,
639 p_supplier_id_col varchar2,
640 p_supplier_site_id_col varchar2,
641 p_supplier_name_col varchar2,
642 p_supplier_site_code_col varchar2)
643 return number
644 is
645 l_sql varchar2(1000);
646 l_unassigned varchar2(30) := fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED');
647 begin
648 l_sql :=
649 ' update '||p_staging_table||' f set (error_code, '||p_supplier_id_col||', '||p_supplier_site_id_col||') ='||
650 ' (select 0, d.supplier_id, d.supplier_site_id'||
651 ' from msc_phub_suppliers_mv d'||
652 ' where nvl(d.supplier_name,'''||l_unassigned||''')=nvl(f.'||p_supplier_name_col||','''||l_unassigned||''')'||
653 ' and (f.'||p_supplier_site_code_col||' is null and d.supplier_site_id=-23453'||
654 ' or d.supplier_site_code=f.'||p_supplier_site_code_col||')'||
655 ' and rownum=1)'||
656 ' where f.st_transaction_id=:p_st_transaction_id'||
657 ' and f.error_code = 0';
658
659 execute immediate l_sql using p_st_transaction_id;
660 commit;
661 return report_decode_error(p_staging_table, p_st_transaction_id, conv_key_err_supplier,
662 p_supplier_name_col||','||p_supplier_site_code_col);
663
664 exception
665 when others then
666 log('msc_phub_util.decode_supplier_key.exception: '||sqlerrm);
667 return 1;
668 end decode_supplier_key;
669
670 function decode_resource_key(p_staging_table varchar2, p_st_transaction_id number)
671 return number
672 is
673 l_sql varchar2(1000);
674 begin
675 l_sql :=
676 ' update '||p_staging_table||' f set (error_code, department_id, resource_id) ='||
677 ' (select 0, d.department_id, d.resource_id'||
678 ' from msc_department_resources d'||
679 ' where d.plan_id=-1'||
680 ' and nvl(d.department_code,0)=nvl(f.department_code,0)'||
681 ' and nvl(d.department_class,0)=nvl(f.department_class,0)'||
682 ' and nvl(d.resource_code,0)=nvl(f.resource_code,0)'||
683 ' and nvl(d.resource_group_name,0)=nvl(f.resource_group_name,0)'||
684 ' and d.sr_instance_id=f.sr_instance_id'||
685 ' and d.organization_id=f.organization_id)'||
686 ' where f.st_transaction_id=:p_st_transaction_id'||
687 ' and f.error_code = 0'||
688 ' and f.resource_code is not null';
689
690 execute immediate l_sql using p_st_transaction_id;
691 commit;
692 return report_decode_error(p_staging_table, p_st_transaction_id, conv_key_err_resource,
693 'department_code,department_class,resource_code,resource_group_name,organization_code');
694
695 exception
696 when others then
697 log('msc_phub_util.decode_resource_key.exception: '||sqlerrm);
698 return 1;
699 end decode_resource_key;
700
701 function decode_project_key(p_staging_table varchar2, p_st_transaction_id number)
702 return number
703 is
704 l_sql varchar2(1000);
705 begin
706 l_sql :=
707 ' update '||p_staging_table||' f set (error_code, project_id, task_id) ='||
708 ' (select 0, d.project_id, d.task_id'||
709 ' from msc_phub_projects_mv d'||
710 ' where d.project_number=f.project_number'||
711 ' and d.task_number=f.task_number'||
712 ' and d.sr_instance_id=f.sr_instance_id'||
713 ' and d.organization_id=f.organization_id)'||
714 ' where f.st_transaction_id=:p_st_transaction_id'||
715 ' and f.error_code = 0'||
716 ' and f.task_number is not null';
717
718 execute immediate l_sql using p_st_transaction_id;
719 commit;
720 return report_decode_error(p_staging_table, p_st_transaction_id, conv_key_err_project,
721 'project_number,task_number,organization_code');
722
723 exception
724 when others then
725 log('msc_phub_util.decode_project_key.exception: '||sqlerrm);
726 return 1;
727 end decode_project_key;
728
729 function prepare_staging_dates(p_staging_table varchar2,
730 date_col varchar2, p_st_transaction_id number,
731 p_upload_mode number, p_overwrite_after_date date,
732 p_plan_start_date date, p_plan_cutoff_date date)
733 return number
734 is
735 l_sql varchar2(1000);
736 l_result number := 0;
737 begin
738 log('msc_phub_util.prepare_staging_dates('||p_staging_table||','||
739 date_col||','||p_st_transaction_id||','||
740 p_upload_mode||','||p_overwrite_after_date||','||
741 p_plan_start_date||','||p_plan_cutoff_date||')');
742
743 if (date_col is null) then
744 return 0;
745 end if;
746
747 /*
748 l_sql :=
749 ' update '||p_staging_table||
750 ' set error_code=:error_code'||
751 ' where st_transaction_id=:p_st_transaction_id'||
752 ' and ('||date_col||'<nvl(:p_plan_start_date,'||date_col||') or '||date_col||'>nvl(:p_plan_cutoff_date,'||date_col||'))';
753 execute immediate l_sql using conv_key_err_date, p_st_transaction_id, p_plan_start_date, p_plan_cutoff_date;
754 log('msc_phub_util.prepare_staging_dates:'||l_sql||', rowcount='||sql%rowcount);
755
756 if (l_result > 0) then
757 l_result := 1;
758 end if;
759 commit;
760 */
761
762 if (p_upload_mode = msc_phub_util.upload_append and
763 p_overwrite_after_date is not null) then
764 l_sql :=
765 ' update '||p_staging_table||
766 ' set error_code=:error_code'||
767 ' where st_transaction_id=:p_st_transaction_id'||
768 ' and '||date_col||'<=:p_overwrite_after_date';
769 execute immediate l_sql using conv_date_filtered, p_st_transaction_id, p_overwrite_after_date;
770 log('msc_phub_util.prepare_staging_dates:'||l_sql||', rowcount='||sql%rowcount);
771 end if;
772 commit;
773 return l_result;
774
775 exception
776 when others then
777 log('msc_phub_util.prepare_staging_dates.exception: '||sqlerrm);
778 return 1;
779 end prepare_staging_dates;
780
781 function prepare_fact_dates(p_fact_table varchar2, p_is_plan_data number,
782 date_col varchar2, p_plan_id number, p_plan_run_id number,
783 p_upload_mode number, p_overwrite_after_date date)
784 return number
785 is
786 l_sql varchar2(1000);
787 l_plan_clause varchar2(100);
788 begin
789 log('msc_phub_util.prepare_fact_dates('||p_fact_table||','||
790 p_is_plan_data||','||date_col||','||
791 p_plan_id||','||p_plan_run_id||','||
792 p_upload_mode||','||p_overwrite_after_date||')');
793
794 if (p_is_plan_data = 1) then
795 l_plan_clause := 'plan_id=:p_plan_id and plan_run_id=:p_plan_run_id';
796 end if;
797
798 if (p_upload_mode = msc_phub_util.upload_append and
799 p_overwrite_after_date is not null and
800 date_col is not null) then
801 l_sql := 'delete from '||p_fact_table||
802 ' where '||date_col||'>:p_overwrite_after_date';
803 if (p_is_plan_data = 1) then
804 l_sql := l_sql||' and '||l_plan_clause;
805 execute immediate l_sql using p_overwrite_after_date, p_plan_id, p_plan_run_id;
806 else
807 execute immediate l_sql using p_overwrite_after_date;
808 end if;
809 log('msc_phub_util.prepare_fact_dates:'||sql%rowcount);
810 end if;
811
812 if (p_upload_mode = msc_phub_util.upload_replace) then
813 l_sql := ' delete from '||p_fact_table;
814 if (p_is_plan_data = 1) then
815 l_sql := l_sql||' where '||l_plan_clause;
816 execute immediate l_sql using p_plan_id, p_plan_run_id;
817 else
818 execute immediate l_sql;
819 end if;
820 log('msc_phub_util.prepare_fact_dates:'||sql%rowcount);
821 end if;
822 commit;
823 return 0;
824
825 exception
826 when others then
827 log('msc_phub_util.prepare_fact_dates.exception: '||sqlerrm);
828 return 1;
829 end prepare_fact_dates;
830
831 function applsys_schema return varchar2
832 is
833 l_schema varchar2(100);
834 dummy1 varchar2(50);
835 dummy2 varchar2(50);
836 begin
837 if (fnd_installation.get_app_info('FND',
838 dummy1, dummy2, l_schema) = false) then
839 return null;
840 end if;
841 return l_schema;
842
843 exception
844 when others then
845 log('msc_phub_util.applsys_schema.exception: '||sqlerrm);
846 return null;
847 end applsys_schema;
848
849 function apps_schema return varchar2
850 is
851 l_apps_schema varchar2(30);
852 begin
853 select oracle_username
854 into l_apps_schema
855 from fnd_oracle_userid
856 where read_only_flag = 'U';
857
858 return l_apps_schema;
859
860 exception
861 when others then
862 return null;
863 end apps_schema;
864
865 function msc_schema return varchar2
866 is
867 l_schema varchar2(100);
868 dummy1 varchar2(50);
869 dummy2 varchar2(50);
870 begin
871 if (fnd_installation.get_app_info('MSC',
872 dummy1, dummy2, l_schema) = false) then
873 return null;
874 end if;
875 return l_schema;
876
877 exception
878 when others then
879 return null;
880 end msc_schema;
881
882 function demantra_schema return varchar2 is
883 begin
884 if (fnd_profile.value('MSD_DEM_VERSION') is not null) then
885 return fnd_profile.value('MSD_DEM_SCHEMA');
886 end if;
887 return null;
888 end demantra_schema;
889
890 function get_resource_rn_qid(p_plan_id number, p_plan_run_id number) return number
891 is
892 l_qid number;
893 begin
894 select msc_hub_query_s.nextval into l_qid from dual;
895 insert into msc_hub_query (
896 query_id,
897 last_update_date,
898 last_updated_by,
899 creation_date,
900 created_by,
901 last_update_login,
902 number1,
903 number2,
904 number3,
905 number4,
906 number5
907 )
908 -- values
909 select distinct l_qid, sysdate, -1, sysdate, -1, -1,
910 mrr.plan_id,
911 mrr.sr_instance_id,
912 mrr.organization_id,
913 mrr.department_id,
914 mrr.resource_id
915 from msc_resource_requirements mrr, msc_plan_runs mpr
916 where mpr.plan_id = p_plan_id
917 and mpr.plan_run_id = p_plan_run_id
918 and mrr.plan_id = p_plan_id
919 and mrr.sr_instance_id = mpr.sr_instance_id
920 and trunc(nvl(mrr.end_date,mrr.start_date)) between mpr.plan_start_date and mpr.plan_cutoff_date
921 and mrr.refresh_number > mpr.lcid
922
923 union all
924 select distinct l_qid, sysdate, -1, sysdate, -1, -1,
925 mra.plan_id,
926 mra.sr_instance_id,
927 mra.organization_id,
928 mra.department_id,
929 mra.resource_id
930 from msc_net_resource_avail mra, msc_plan_runs mpr
931 where mpr.plan_id = p_plan_id
932 and mpr.plan_run_id = p_plan_run_id
933 and mra.plan_id = p_plan_id
934 and mra.sr_instance_id = mpr.sr_instance_id
935 and mra.simulation_set is null
936 and trunc(trunc(mra.shift_date)) between mpr.plan_start_date and mpr.plan_cutoff_date
937 and mra.refresh_number > mpr.lcid;
938
939 log('msc_phub_util.get_resource_rn_qid, l_qid='||l_qid||', count='||sql%rowcount);
940 commit;
941 return l_qid;
942 end get_resource_rn_qid;
943
944 function get_item_rn_qid(p_plan_id number, p_plan_run_id number) return number
945 is
946 l_qid number;
947 begin
948 select msc_hub_query_s.nextval into l_qid from dual;
949 insert into msc_hub_query (
950 query_id,
951 last_update_date,
952 last_updated_by,
953 creation_date,
954 created_by,
955 last_update_login,
956 number1,
957 number2,
958 number3,
959 number4
960 )
961 -- values
962 select distinct l_qid, sysdate, -1, sysdate, -1, -1,
963 ms.plan_id,
964 ms.sr_instance_id,
965 ms.organization_id,
966 ms.inventory_item_id
967 from msc_supplies ms, msc_plan_runs mpr
968 where mpr.plan_id = p_plan_id
969 and mpr.plan_run_id = p_plan_run_id
970 and ms.plan_id = p_plan_id
971 and ms.sr_instance_id = mpr.sr_instance_id
972 and trunc(nvl(ms.firm_date,ms.new_schedule_date)) between mpr.plan_start_date and mpr.plan_cutoff_date
973 and ms.refresh_number > mpr.lcid
974
975 union all
976 select distinct l_qid, sysdate, -1, sysdate, -1, -1,
977 md.plan_id,
978 md.sr_instance_id,
979 md.organization_id,
980 md.inventory_item_id
981 from msc_demands md, msc_plan_runs mpr
982 where mpr.plan_id = p_plan_id
983 and mpr.plan_run_id = p_plan_run_id
984 and md.plan_id = p_plan_id
985 and md.sr_instance_id = mpr.sr_instance_id
986 and trunc(nvl(md.firm_date,md.using_assembly_demand_date)) between mpr.plan_start_date and mpr.plan_cutoff_date
987 and md.refresh_number > mpr.lcid;
988
989 log('msc_phub_util.get_item_rn_qid, l_qid='||l_qid||', count='||sql%rowcount);
990 commit;
991 return l_qid;
992 end get_item_rn_qid;
993
994 function get_owning_currency_code(p_plan_run_id number) return varchar2
995 is
996 l_owning_currency_code varchar2(20);
997 begin
998 select nvl(o.currency_code, 'XXX')
999 into l_owning_currency_code
1000 from msc_trading_partners o, msc_plan_runs r
1001 where o.sr_instance_id(+)=r.sr_instance_id
1002 and o.sr_tp_id(+)=r.organization_id
1003 and o.partner_type(+)=3
1004 and r.plan_run_id=p_plan_run_id;
1005
1006 return l_owning_currency_code;
1007 exception
1008 when others then
1009 return 'XXX';
1010 end;
1011
1012 function get_reporting_dates(p_plan_start_date date, p_plan_cutoff_date date) return number
1013 is
1014 l_qid_last_date number;
1015 begin
1016 select msc_hub_query_s.nextval into l_qid_last_date from dual;
1017 insert into msc_hub_query (
1018 query_id, date1, date2,
1019 created_by, creation_date,
1020 last_update_date, last_updated_by, last_update_login)
1021 select
1022 l_qid_last_date,
1023 nvl(lag(calendar_date) over(order by calendar_date)+1, p_plan_start_date-1),
1024 calendar_date,
1025 fnd_global.user_id, sysdate,
1026 sysdate, fnd_global.user_id, fnd_global.login_id
1027 from msc_phub_dates_mv
1028 where calendar_date between p_plan_start_date and p_plan_cutoff_date
1029 and calendar_date in (mfg_week_end_date, fis_period_end_date, month_end_date, p_plan_cutoff_date);
1030
1031 log('msc_phub_util.get_reporting_dates, l_qid_last_date='||l_qid_last_date||', count='||sql%rowcount);
1032 commit;
1033
1034 return l_qid_last_date;
1035 end get_reporting_dates;
1036
1037 function get_partition_name(p_table varchar2, p_partition_id number) return varchar2
1038 is
1039 l_partition_name varchar2(100);
1040 begin
1041 if (p_partition_id is null) then
1042 return null;
1043 end if;
1044
1045 if (p_partition_id > 0) then
1046 l_partition_name := substr(p_table, 5)||'_'||to_char(p_partition_id);
1047 else
1048 l_partition_name := substr(p_table, 5)||'_0';
1049 end if;
1050 return l_partition_name;
1051 end get_partition_name;
1052
1053 procedure gather_table_stats(
1054 p_table_type varchar2,
1055 p_table varchar2,
1056 p_partition_id number default null,
1057 p_schema varchar2 default null)
1058 is
1059 l_partition_name varchar2(100);
1060 l_schema varchar2(100) := p_schema;
1061 l_profile_value number;
1062 begin
1063 if (l_schema is null) then
1064 l_schema := msc_schema;
1065 end if;
1066
1067 l_profile_value := fnd_profile.value(p_table_type);
1068 if (l_profile_value = 1) then
1069 if (p_partition_id is not null) then
1070 l_partition_name := get_partition_name(p_table, p_partition_id);
1071 fnd_stats.gather_table_stats(
1072 l_schema, p_table,
1073 partname=>l_partition_name,
1074 granularity=>'PARTITION',
1075 percent =>10);
1076 log('msc_phub_util.gather_table_stats: '||l_schema||'.'||p_table||'('||l_partition_name||')');
1077 else
1078 fnd_stats.gather_table_stats(l_schema, p_table,
1079 granularity=>'AUTO');
1080 log('msc_phub_util.gather_table_stats: '||l_schema||'.'||p_table);
1081 end if;
1082 else
1083 log('msc_phub_util.gather_table_stats: '||l_schema||'.'||p_table||
1084 ' skipped('||p_table_type||'='||l_profile_value||')');
1085 end if;
1086
1087 end gather_table_stats;
1088
1089 procedure unusuable_local_index(p_table varchar2, p_partition_id number, p_mode number)
1090 is
1091 cursor c(p_table_owner varchar2, p_table varchar2) is
1092 select owner, index_name
1093 from all_indexes
1094 where table_owner=p_table_owner and table_name=p_table;
1095
1096 l_partition_name varchar2(100);
1097 l_schema varchar2(100) := msc_schema;
1098 l_ddl varchar2(200);
1099 begin
1100 l_partition_name := get_partition_name(p_table, p_partition_id);
1101 for r in c(l_schema, p_table) loop
1102 if (p_mode = 1) then
1103 l_ddl := 'alter index '||r.owner||'.'||r.index_name||' modify partition '||l_partition_name||' unusable';
1104 else
1105 l_ddl := 'alter index '||r.owner||'.'||r.index_name||' rebuild partition '||l_partition_name||' nologging';
1106 end if;
1107
1108 begin
1109 log(l_ddl);
1110 execute immediate l_ddl;
1111 exception
1112 when others then
1113 log('msc_phub_util.unusuable_local_index:'||sqlerrm);
1114 end;
1115 end loop;
1116 end unusuable_local_index;
1117
1118 procedure truncate_table(p_table varchar2)
1119 is
1120 l_ddl varchar2(500);
1121 l_msc_schema varchar2(100) := msc_schema;
1122 l_applsys_schema varchar2(100);
1123 dummy1 varchar2(50);
1124 dummy2 varchar2(50);
1125 e_truncate_tale exception;
1126 begin
1127 log('msc_phub_util.truncate_table('||p_table||')');
1128 if (fnd_installation.get_app_info('FND', dummy1, dummy2, l_applsys_schema) = false) then
1129 log(fnd_message.get_string('MSC', 'MSC_PART_UNDEFINED_SCHEMA'));
1130 raise e_truncate_tale;
1131 end if;
1132
1133 l_ddl := 'truncate table '||p_table;
1134 ad_ddl.do_ddl(l_applsys_schema, l_msc_schema, ad_ddl.truncate_table, l_ddl, p_table);
1135 commit;
1136
1137 exception
1138 when others then
1139 log('msc_phub_util.truncate_table: '||sqlerrm);
1140 raise;
1141 end truncate_table;
1142
1143 END msc_phub_util;