[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_COMMON_UTILITIES
Source
1 PACKAGE BODY MSD_DEM_COMMON_UTILITIES AS
2 /* $Header: msddemcub.pls 120.60.12020000.6 2013/01/22 10:17:01 kkhatri ship $ */
3
4
5 LG_VAR_SITE_CODE_FORMAT NUMBER := NULL;
6
7
8
9 /*** PRIVATE PROCEDURES ***
10 * MSD_UOM_CONVERSION
11 */
12
13 PROCEDURE APPS_INITIALIZE( p_user_id in number,
14 p_resp_id in number,
15 p_appl_id in number
16 ) IS
17 PRAGMA AUTONOMOUS_TRANSACTION;
18 BEGIN
19 fnd_global.apps_initialize(p_user_id, p_resp_id, p_appl_id);
20 commit;
21 END APPS_INITIALIZE;
22
23 PROCEDURE msd_uom_conversion (from_unit varchar2,
24 to_unit varchar2,
25 item_id number,
26 uom_rate OUT NOCOPY number ) IS
27
28 from_class varchar2(10);
29 to_class varchar2(10);
30
31 CURSOR standard_conversions IS
32 select t.conversion_rate std_to_rate,
33 t.uom_class std_to_class,
34 f.conversion_rate std_from_rate,
35 f.uom_class std_from_class
36 from msc_uom_conversions t,
37 msc_uom_conversions f
38 where t.inventory_item_id in (item_id, 0) and
39 t.uom_code = to_unit and
40 nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
41 f.inventory_item_id in (item_id, 0) and
42 f.uom_code = from_unit and
43 nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
44 order by t.inventory_item_id desc, f.inventory_item_id desc;
45
46
47 std_rec standard_conversions%rowtype;
48
49
50 CURSOR interclass_conversions(p_from_class VARCHAR2, p_to_class VARCHAR2) IS
51 select decode(from_uom_class, p_from_class, 1, 2) from_flag,
52 decode(to_uom_class, p_to_class, 1, 2) to_flag,
53 conversion_rate rate
54 from msc_uom_class_conversions
55 where inventory_item_id = item_id and
56 nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
57 ( (from_uom_class = p_from_class and to_uom_class = p_to_class) or
58 (from_uom_class = p_to_class and to_uom_class = p_from_class) );
59
60 class_rec interclass_conversions%rowtype;
61
62 invalid_conversion exception;
63
64 type conv_tab is table of number index by binary_integer;
65 type class_tab is table of varchar2(10) index by binary_integer;
66
67 interclass_rate_tab conv_tab;
68 from_class_flag_tab conv_tab;
69 to_class_flag_tab conv_tab;
70 from_rate_tab conv_tab;
71 to_rate_tab conv_tab;
72 from_class_tab class_tab;
73 to_class_tab class_tab;
74
75 std_index number;
76 class_index number;
77
78 from_rate number := 1;
79 to_rate number := 1;
80 interclass_rate number := 1;
81 to_class_rate number := 1;
82 from_class_rate number := 1;
83 msgbuf varchar2(500);
84
85 begin
86
87 /*
88 ** Conversion between between two UOMS.
89 **
90 ** 1. The conversion always starts from the conversion defined, if exists,
91 ** for an specified item.
92 ** 2. If the conversion id not defined for that specific item, then the
93 ** standard conversion, which is defined for all items, is used.
94 ** 3. When the conversion involves two different classes, then
95 ** interclass conversion is activated.
96 */
97
98 /* If from and to units are the same, conversion rate is 1.
99 Go immediately to the end of the procedure to exit.*/
100
101 if (from_unit = to_unit) then
102 uom_rate := 1;
103 goto procedure_end;
104 end if;
105
106
107 /* Get item specific or standard conversions */
108 open standard_conversions;
109 std_index := 0;
110 loop
111
112 std_index := std_index + 1;
113
114 fetch standard_conversions into std_rec;
115 exit when standard_conversions%notfound;
116
117 from_rate_tab(std_index) := std_rec.std_from_rate;
118 from_class_tab(std_index) := std_rec.std_from_class;
119 to_rate_tab(std_index) := std_rec.std_to_rate;
120 to_class_tab(std_index) := std_rec.std_to_class;
121
122 end loop;
123
124 close standard_conversions;
125
126 if (std_index = 0) then /* No conversions defined */
127 msgbuf := msgbuf||'Invalid standard conversion : ';
128 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
129 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
130 raise invalid_conversion;
131
132 else
133 /* Conversions are ordered.
134 Item specific conversions will be returned first. */
135
136 from_class := from_class_tab(1);
137 to_class := to_class_tab(1);
138 from_rate := from_rate_tab(1);
139 to_rate := to_rate_tab(1);
140
141 end if;
142
143
144 /* Load interclass conversion tables */
145 if (from_class <> to_class) then
146 class_index := 0;
147 open interclass_conversions (from_class, to_class);
148 loop
149
150 fetch interclass_conversions into class_rec;
151 exit when interclass_conversions%notfound;
152
153 class_index := class_index + 1;
154
155 to_class_flag_tab(class_index) := class_rec.to_flag;
156 from_class_flag_tab(class_index) := class_rec.from_flag;
157 interclass_rate_tab(class_index) := class_rec.rate;
158
159 end loop;
160 close interclass_conversions;
161
162 /* No interclass conversion is defined */
163 if (class_index = 0 ) then
164 msgbuf := msgbuf||'Invalid Interclass conversion : ';
165 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
166 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
167 raise invalid_conversion;
168 else
169 if ( to_class_flag_tab(1) = 1 and from_class_flag_tab(1) = 1 ) then
170 to_class_rate := interclass_rate_tab(1);
171 from_class_rate := 1;
172 else
173 from_class_rate := interclass_rate_tab(1);
174 to_class_rate := 1;
175 end if;
176 interclass_rate := from_class_rate/to_class_rate;
177 end if;
178 end if; /* End of from_class <> to_class */
179
180 /*
181 ** conversion rates are defaulted to '1' at the start of the procedure
182 ** so seperate calculations are not required for standard/interclass
183 ** conversions
184 */
185
186 if (to_rate <> 0 ) then
187 uom_rate := (from_rate * interclass_rate) / to_rate;
188 else
189 uom_rate := 1;
190 end if;
191
192
193 /* Put a label and a null statement over here so that you can
194 the goto statements can branch here */
195 <<procedure_end>>
196
197 null;
198
199 exception
200
201 when others then
202 uom_rate := 1;
203
204 END msd_uom_conversion;
205
206
207 /*** PUBLIC PROCEDURES ***
208 * LOG_MESSAGE
209 * LOG_DEBUG
210 * GET_DBLINK
211 * GET_INSTANCE_INFO
212 */
213
214
215 /*
216 * This procedure logs a given message text in the concurrent request log file.
217 * param: p_buff - message text to be logged.
218 */
219 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2)
220 IS
221 BEGIN
222 fnd_file.put_line (fnd_file.log, p_buff);
223 END LOG_MESSAGE;
224
225
226 /*
227 * This procedure logs a given debug message text in the concurrent request log file
228 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
229 * param: p_buff - debug message text to be logged.
230 */
231 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2)
232 IS
233 BEGIN
234 IF (C_MSD_DEM_DEBUG = 'Y') THEN
235 fnd_file.put_line (fnd_file.output, p_buff);
236 END IF;
237 END LOG_DEBUG;
238
239
240 /*
241 * This procedure gets the db link to the given source instance
242 */
243 PROCEDURE GET_DBLINK (
244 errbuf OUT NOCOPY VARCHAR2,
245 retcode OUT NOCOPY VARCHAR2,
246 p_sr_instance_id IN NUMBER,
247 p_dblink OUT NOCOPY VARCHAR2)
248 IS
249 BEGIN
250 SELECT decode ( m2a_dblink, null, '', '@' || m2a_dblink)
251 INTO p_dblink
252 FROM msc_apps_instances
253 WHERE instance_id = p_sr_instance_id;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 retcode := -1 ;
258 errbuf := substr(SQLERRM,1,150);
259 RETURN;
260 END GET_DBLINK;
261
262 /* This procedure gives schema name for a application */
263
264 PROCEDURE GET_SCHEMA_NAME (
265 errbuf OUT NOCOPY VARCHAR2,
266 retcode OUT NOCOPY VARCHAR2,
267 schema_name OUT NOCOPY VARCHAR2,
268 appl_short_name IN VARCHAR2 )
269
270
271 IS
272
273 x_boolean BOOLEAN;
274 x_dummy1 VARCHAR2(100);
275 x_dummy2 VARCHAR2(100);
276 BEGIN
277 x_boolean := fnd_installation.get_app_info (appl_short_name,x_dummy1,x_dummy2,schema_name);
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 retcode := -1 ;
282 errbuf := substr(SQLERRM,1,150);
283 RETURN;
284 END GET_SCHEMA_NAME;
285
286 /*
287 * This procedure gets the db link to the destination from given source instance
288 */
289 PROCEDURE GET_DEST_DBLINK (
290 errbuf OUT NOCOPY VARCHAR2,
291 retcode OUT NOCOPY VARCHAR2,
292 p_sr_instance_id IN NUMBER,
293 p_dblink OUT NOCOPY VARCHAR2)
294 IS
295 BEGIN
296 SELECT decode ( a2m_dblink, null, '', '@' || a2m_dblink)
297 INTO p_dblink
298 FROM msc_apps_instances
299 WHERE instance_id = p_sr_instance_id;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 retcode := -1 ;
304 errbuf := substr(SQLERRM,1,150);
305 RETURN;
306 END GET_DEST_DBLINK;
307
308 /*
309 * This procedure gets the instance info given the source instance id
310 */
311 PROCEDURE GET_INSTANCE_INFO (
312 errbuf OUT NOCOPY VARCHAR2,
313 retcode OUT NOCOPY VARCHAR2,
314 p_instance_code OUT NOCOPY VARCHAR2,
315 p_apps_ver OUT NOCOPY NUMBER,
316 p_dgmt OUT NOCOPY NUMBER,
317 p_instance_type OUT NOCOPY NUMBER,
318 p_sr_instance_id IN NUMBER)
319 IS
320 BEGIN
321
322 SELECT
323 instance_code,
324 apps_ver,
325 gmt_difference/24.0,
326 instance_type
327 INTO
328 p_instance_code,
329 p_apps_ver,
330 p_dgmt,
331 p_instance_type
332 FROM msc_apps_instances
333 WHERE instance_id= p_sr_instance_id;
334 EXCEPTION
335 WHEN OTHERS THEN
336 retcode := -1 ;
337 errbuf := substr(SQLERRM,1,150);
338 RETURN;
339 END GET_INSTANCE_INFO;
340
341
342 /*
343 * This procedure will refresh Purge Series Data data profile to its defualt value
344 * i.e. it will set the data profile option to No Load and No Purge for all series
345 * included in the profile.
346 */
347
348 PROCEDURE REFRESH_PURGE_SERIES (
349 errbuf OUT NOCOPY VARCHAR2,
350 retcode OUT NOCOPY VARCHAR2,
351 p_profile_id IN NUMBER,
352 p_schema IN VARCHAR2)
353 IS
354
355 TYPE REF_CURSOR_TYPE IS REF CURSOR;
356
357 c_ref_cursor REF_CURSOR_TYPE;
358
359 x_sql varchar2(500);
360 l_sql varchar2(500);
361 x_series_id number;
362
363 BEGIN
364
365 x_sql := 'select series_id from ' ||p_schema ||'.transfer_query_series where id = ' || p_profile_id;
366
367 OPEN c_ref_cursor FOR x_sql;
368
369 LOOP
370 FETCH c_ref_cursor INTO x_series_id;
371 EXIT WHEN c_ref_cursor%NOTFOUND;
372
373 l_sql := 'begin ' || p_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||p_profile_id||', '|| x_series_id||', 2, 0); end;';
374 execute immediate l_sql;
375 end loop;
376
377 close c_ref_cursor;
378
379 END;
380
381 /*
382 * Update the synonyms MSD_DEM_TRANSFER_LIST and MSD_DEM_TRANSFER_QUERY
383 * to point to the Demantra's tables TRANSFER_LIST and TRANSFER_QUERY
384 * if Demantra is installed.
385 * Sets the profile MSD_DEM_SCHEMA to the Demantra Schema Name
386 * The checks if the table MDP_MATRIX exists in the Demantra Schema
387 */
388
389 PROCEDURE UPDATE_SYNONYMS (
390 errbuf OUT NOCOPY VARCHAR2,
391 retcode OUT NOCOPY VARCHAR2,
392 p_demantra_schema IN VARCHAR2 DEFAULT NULL)
393
394 IS
395
396 CURSOR c_get_dm_schema
397 IS
398 SELECT owner
399 FROM dba_objects
400 WHERE owner = owner
401 AND object_type = 'TABLE'
402 AND object_name = 'MDP_MATRIX'
403 ORDER BY created desc;
404
405 CURSOR c_is_cols_present (p_owner VARCHAR2,
406 p_table_name VARCHAR2,
407 p_column_name VARCHAR2,
408 p_data_type VARCHAR2)
409 IS
410 SELECT count(1)
411 FROM dba_tab_columns
412 WHERE owner = p_owner
413 AND table_name = p_table_name
414 AND column_name = p_column_name
415 AND data_type = p_data_type;
416
417 CURSOR c_is_table_present (p_owner VARCHAR2,
418 p_table_name VARCHAR2)
419 IS
420 SELECT count(1)
421 FROM dba_tables
422 WHERE owner = p_owner
423 AND table_name = p_table_name;
424
425 x_dem_schema VARCHAR2(50) := NULL;
426 x_create_synonym_sql VARCHAR2(200) := NULL;
427 x_grant_sql VARCHAR2(200) := NULL;
428 x_get_dem_ver_sql VARCHAR2(200) := NULL;
429 x_dem_version VARCHAR2(20) := NULL;
430 x_appl_home_page_mode VARCHAR2(20) := NULL;
431 x_appl_home_page_url VARCHAR2(200) := NULL;
432 x_ext_logout_url_sql VARCHAR2(200) := NULL;
433 x_sql VARCHAR2(1000) := NULL;
434 x_curr_val VARCHAR2(50) := NULL;
435 x_success BOOLEAN := NULL;
436 x_count1 NUMBER := NULL;
437 x_count2 NUMBER := NULL;
438 x_count3 NUMBER := NULL;
439 x_count4 NUMBER := NULL;
440
441 x_col_present_flag NUMBER := NULL;
442 x_boolean BOOLEAN := NULL;
443 x_dummy1 VARCHAR2(100) := NULL;
444 x_dummy2 VARCHAR2(100) := NULL;
445 x_msc_schema VARCHAR2(30) := NULL;
446 x_msd_schema VARCHAR2(30) := NULL;
447
448 BEGIN
449
450 IF (p_demantra_schema IS NULL)
451 THEN
452 OPEN c_get_dm_schema;
453 FETCH c_get_dm_schema INTO x_dem_schema;
454 CLOSE c_get_dm_schema;
455 ELSE
456 x_dem_schema := p_demantra_schema;
457 END IF;
458
459 log_message ('The Demantra Schema Name is - ' || x_dem_schema);
460
461 X_boolean := fnd_installation.get_app_info ('MSD', X_dummy1, X_dummy2, X_msd_schema);
462 X_boolean := fnd_installation.get_app_info ('MSC', X_dummy1, X_dummy2, X_msc_schema);
463
464 /* Demantra is Installed */
465 IF (x_dem_schema IS NOT NULL)
466 THEN
467
468
469 /* Update synonym MSD_DEM_TRANSFER_LIST to point to Demantra table TRANSFER_LIST */
470 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_LIST FOR ' ||
471 x_dem_schema || '.TRANSFER_LIST';
472
473 EXECUTE IMMEDIATE x_create_synonym_sql;
474 log_message ('Updated synonym MSD_DEM_TRANSFER_LIST');
475
476 /* Update synonym MSD_DEM_TRANSFER_QUERY to point to Demantra table TRANSFER_QUERY */
477 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY FOR ' ||
478 x_dem_schema || '.TRANSFER_QUERY';
479
480 EXECUTE IMMEDIATE x_create_synonym_sql;
481 log_message ('Updated synonym MSD_DEM_TRANSFER_QUERY');
482
483
484 /* Update synonym MSD_DEM_TRANSFER_QUERY_LEVELS to point to Demantra table TRANSFER_QUERY_LEVELS */
485 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY_LEVELS FOR ' ||
486 x_dem_schema || '.TRANSFER_QUERY_LEVELS';
487
488 EXECUTE IMMEDIATE x_create_synonym_sql;
489 log_message ('Updated synonym MSD_DEM_TRANSFER_QUERY_LEVELS');
490
491
492 /* Update synonym MSD_DEM_GROUP_TABLES to point to Demantra table GROUP_TABLES */
493 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_GROUP_TABLES FOR ' ||
494 x_dem_schema || '.GROUP_TABLES';
495
496 EXECUTE IMMEDIATE x_create_synonym_sql;
497 log_message ('Updated synonym MSD_DEM_GROUP_TABLES');
498
499
500 /* Update synonym T_SRC_SALES_TMPL to point to Demantra table T_SRC_SALES_TMPL */
501 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM T_SRC_SALES_TMPL FOR '||
502 x_dem_schema || '.T_SRC_SALES_TMPL';
503
504 EXECUTE IMMEDIATE x_create_synonym_sql;
505 log_message ('Updated synonym T_SRC_SALES_TMPL');
506
507
508 /* Update synonym MSD_DEM_RETURN_HISTORY to point to Demantra table MSD_DEM_RETURN_HISTORY */
509 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_RETURN_HISTORY FOR ' ||
510 x_dem_schema || '.MSD_DEM_RETURN_HISTORY';
511
512 EXECUTE IMMEDIATE x_create_synonym_sql;
513 log_message ('Updated synonym MSD_DEM_RETURN_HISTORY');
514
515 /* Grant execute permissions to Demantra Schema on pakcage MSD_DEM_UPLOAD_FORECAST */
516 x_grant_sql := 'GRANT EXECUTE ON MSD_DEM_UPLOAD_FORECAST TO ' || x_dem_schema;
517 EXECUTE IMMEDIATE x_grant_sql;
518 log_message ('Execute privilege granted on package MSD_DEM_UPLOAD FORECAST to ' || x_dem_schema || ' schema.');
519
520 x_grant_sql := 'GRANT EXECUTE ON MSD_DEM_SOP TO ' || x_dem_schema;
521 EXECUTE IMMEDIATE x_grant_sql;
522 log_message ('Execute privilege granted on package MSD_DEM_SOP to ' || x_dem_schema || ' schema.');
523
524 x_grant_sql := 'GRANT EXECUTE ON MSD_DEM_COMMON_UTILITIES TO ' || x_dem_schema;
525 EXECUTE IMMEDIATE x_grant_sql;
526 log_message ('Execute privilege granted on package MSD_DEM_COMMON_UTILITIES to ' || x_dem_schema || ' schema.');
527
528 x_grant_sql := 'GRANT EXECUTE ON MSC_PHUB_PKG TO ' || x_dem_schema;
529 EXECUTE IMMEDIATE x_grant_sql;
530 log_message ('Execute privilege granted on package MSC_PHUB_PKG to ' || x_dem_schema || ' schema.');
531
532 /* grant select on required tables */
533 log_message ('Granting SELECT privilege on following tables to ' || x_dem_schema || ' schema.');
534
535 log_message('MSC.MSC_PLANS');
536 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_PLANS TO ' || x_dem_schema;
537 execute immediate x_grant_sql;
538
539 log_message ('MSC.MSC_INT_PUBLISH_DATA');
540 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_INT_PUBLISH_DATA TO ' || x_dem_schema;
541 execute immediate x_grant_sql;
542
543 log_message('MSC.MSC_INT_SUPPORTED_API');
544 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_INT_SUPPORTED_API TO ' || x_dem_schema;
545 execute immediate x_grant_sql;
546
547 log_message('MSC.MSC_BIS_INV_DETAIL');
548 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_BIS_INV_DETAIL TO ' || x_dem_schema;
549 execute immediate x_grant_sql;
550
551 log_message('MSC.MSC_SYSTEM_ITEMS');
552 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_SYSTEM_ITEMS TO ' || x_dem_schema;
553 execute immediate x_grant_sql;
554
555 log_message('MSC.MSC_PLAN_ORGANIZATIONS');
556 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_PLAN_ORGANIZATIONS TO ' || x_dem_schema;
557 execute immediate x_grant_sql;
558
559 log_message('MSC.MSC_TRADING_PARTNERS');
560 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TRADING_PARTNERS TO ' || x_dem_schema;
561 execute immediate x_grant_sql;
562
563 log_message('MSC.MSC_TP_ID_LID');
564 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TP_ID_LID TO ' || x_dem_schema;
565 execute immediate x_grant_sql;
566
567 log_message('MSC.MSC_TRADING_PARTNER_SITES');
568 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TRADING_PARTNER_SITES TO ' || x_dem_schema;
569 execute immediate x_grant_sql;
570
571 log_message('MSC.MSC_TP_SITE_ID_LID');
572 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_TP_SITE_ID_LID TO ' || x_dem_schema;
573 execute immediate x_grant_sql;
574
575 log_message('MSC.MSC_REGIONS');
576 x_grant_sql := 'GRANT SELECT ON ' || x_msc_schema || '.MSC_REGIONS TO ' || x_dem_schema;
577 execute immediate x_grant_sql;
578
579 log_message('MSD.MSD_DEM_TIME');
580 x_grant_sql := 'GRANT SELECT ON ' || x_msd_schema || '.MSD_DEM_TIME TO ' || x_dem_schema;
581 execute immediate x_grant_sql;
582
583 log_message('MSD.MSD_DEM_CTO_BOM');
584 x_grant_sql := 'GRANT SELECT ON ' || x_msd_schema || '.MSD_DEM_CTO_BOM TO ' || x_dem_schema;
585 execute immediate x_grant_sql;
586
587 /* Set the profile MSD_DEM_SCHEMA if not set */
588 x_curr_val := fnd_profile.value('MSD_DEM_SCHEMA');
589
590 IF (nvl(x_curr_val, '$$$') <> x_dem_schema)
591 THEN
592 x_success := fnd_profile.save ('MSD_DEM_SCHEMA', x_dem_schema, 'SITE');
593 log_message ('Profile MSD_DEM: Schema has been set to ''' || x_dem_schema || ''' at the SITE level');
594
595 /*Setting global parameter */
596 C_MSD_DEM_SCHEMA := x_dem_schema ;
597 log_message ('Global Parameter C_MSD_DEM_SCHEMA has been set to ' || x_dem_schema );
598
599 MSD_DEM_SOP.C_MSD_DEM_SCHEMA := x_dem_schema;
600
601 MSD_DEM_CTO.CS_DEM_SYSDATE := get_demantra_date(sysdate, 1);
602 MSD_DEM_CTO.CS_DEM_CTO_BASE_MODEL := get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 1);
603 MSD_DEM_CTO.CS_DEM_CTO_OPTION_CLASS := get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 2);
604 MSD_DEM_CTO.CS_DEM_CTO_OPTION := get_lookup_code ('T_EP_CTO_DEMAND_TYPE', 3);
605 MSD_DEM_CTO.CS_DEM_LEVEL_ITEM_LABEL := get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ITEM', 1, 'table_label');
606 MSD_DEM_CTO.CS_DEM_LEVEL_ORG_LABEL := get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ORGANIZATION', 1, 'table_label');
607 MSD_DEM_CTO.CS_DEM_LEVEL_SITE_LABEL := get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SITE', 1, 'table_label');
608 MSD_DEM_CTO.CS_DEM_LEVEL_DC_LABEL := get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_DEMAND_CLASS', 1, 'table_label');
609 MSD_DEM_CTO.CS_DEM_LEVEL_SC_LABEL := get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SALES_CHANNEL', 1, 'table_label');
610 MSD_DEM_CTO.CS_DEM_LEVEL_ITEM_ID := to_number(get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ITEM', 1, 'group_table_id'));
611 MSD_DEM_CTO.CS_DEM_LEVEL_ORG_ID := to_number(get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ORGANIZATION', 1, 'group_table_id'));
612 MSD_DEM_CTO.CS_DEM_LEVEL_SITE_ID := to_number(get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SITE', 1, 'group_table_id'));
613 MSD_DEM_CTO.CS_DEM_LEVEL_DC_ID := to_number(get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_DEMAND_CLASS', 1, 'group_table_id'));
614 MSD_DEM_CTO.CS_DEM_LEVEL_SC_ID := to_number(get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_SALES_CHANNEL', 1, 'group_table_id'));
615
616 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_BASE_MODEL := get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 1);
617 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_OPTION_CLASS := get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 2);
618 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_OPTION := get_lookup_code ('T_EP_SPF_DEMAND_TYPE', 3);
619
620 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_ASSET_GROUP_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ASSET_GROUP', 1, 'table_label');
621 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_CLASS_CODE_LABEL := msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_CLASS_CODE', 1, 'table_label');
622
623 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_ASSET_GROUP_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_ASSET_GROUP', 1, 'group_table_id'));
624 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_CLASS_CODE_ID := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'LEVEL_CLASS_CODE', 1, 'group_table_id'));
625 -- bug#14694463 kkhatri
626 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_MATERIAL := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 1);
627 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_PRODUCT := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 2);
628 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_RESOURCE := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 3);
629 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_WORK_ORDER := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 4);
630 MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_WORKORDER_ITEM := msd_dem_common_utilities.get_lookup_code ('T_EP_I_ATT_10', 4);
631
632 END IF;
633
634 /* Set the profile MSD_DEM_VERSION */ -- nallkuma
635 x_get_dem_ver_sql := 'select version from ' || x_dem_schema || '.version_details' ;
636 EXECUTE IMMEDIATE x_get_dem_ver_sql into x_dem_version ;
637
638 x_dem_version := SUBSTR(x_dem_version, 1, INSTR(x_dem_version, '.', 1, 2)-1) ;
639
640 x_success := fnd_profile.save ('MSD_DEM_VERSION', x_dem_version, 'SITE');
641 log_message ('Profile MSD_DEM: Version has been set to ''' || x_dem_version || ''' at the SITE level');
642
643 /* Set the ExternalLogoutUrl parameter in demantra schema to the applications home page */ -- nallkuma 16-feb-2009
644 /* 1st IF condtion :- This is only for demantra 7.3.X & above versions */ -- bug#7458724
645 /* 2nd IF condtion :- Sets the ExternalLogoutUrl parameter to appl home page only if the appl home page mode is set "FWD" or else it will be null */
646
647 IF (TO_NUMBER(x_dem_version) >= 7.3)
648 THEN
649 x_appl_home_page_mode := fnd_profile.value('APPLICATIONS_HOME_PAGE') ;
650 IF (x_appl_home_page_mode = 'FWK' OR x_appl_home_page_mode is null)
651 THEN
652 x_appl_home_page_url := fnd_profile.value('APPS_FRAMEWORK_AGENT') ;
653 x_appl_home_page_url := trim(x_appl_home_page_url) || '/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE';
654 x_ext_logout_url_sql := ' Update '|| x_dem_schema || '.sys_params' ||
655 ' Set pval = ''' || x_appl_home_page_url ||
656 ''' Where pname like ''ExternalLogoutUrl'' ' ;
657 EXECUTE IMMEDIATE x_ext_logout_url_sql ;
658 commit;
659 log_message ('Updated ExternalLogoutUrl parameter in sys_params table to :- ' ||x_appl_home_page_url);
660 END IF;
661
662 -- BUG#9211268 syenamar
663 --Add new plan type lookup for RP in demantra (7.3)
664 log_message('Checking for plan type lookup ''Rapid Plan'' in demantra');
665
666 x_sql := 'select count(1) from ' || x_dem_schema || '.plan_type_lookup where type_id = 2';
667 execute immediate x_sql into x_count1;
668
669 IF (x_count1 = 0) THEN
670 BEGIN
671 log_message('Adding plan type lookup ''Rapid Plan'' in demantra');
672 x_sql := 'INSERT INTO ' || x_dem_schema || '.plan_type_lookup (type_id, type_desc) values (2, ''Rapid Plan'')';
673 execute immediate x_sql;
674 commit;
675 EXCEPTION
676 WHEN OTHERS THEN
677 log_message('Error when adding plan type lookup ''Rapid Plan'' in demantra - ' || substr(sqlerrm, 1, 150));
678 END;
679 END IF;
680
681 x_count1 := NULL;
682 --syenamar
683
684 END IF;
685
686 /*
687 * Set the system parameter Integration1CalendarLoad to query to table directly instead of synonym. EBR Impact.
688 */
689 EXECUTE IMMEDIATE ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || x_msd_schema || '.MSD_DEM_TIME'' ' ||
690 ' where pname = ''Integration1CalendarLoad''';
691 COMMIT;
692 log_message ('Updated Integration1CalendarLoad parameter in sys_params table to :- ' || x_msd_schema || '.MSD_DEM_TIME');
693
694 /* In case of Demantra 7.2.X, add the following columns
695 * Table - T_SRC_SALES_TMPL, Columns to be added - COMPONENT_CODE, EBS_BASE_MODEL_SR_PK, COMPONENT_CODE_LEGACY and EBS_BASE_MODEL_CODE
696 * Table - T_SRC_SALES_TMPL_ERR, Columns to be added - COMPONENT_CODE, EBS_BASE_MODEL_SR_PK, COMPONENT_CODE_LEGACY and EBS_BASE_MODEL_CODE
697 */
698 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_SALES_TMPL', 'COMPONENT_CODE', 'VARCHAR2');
699 FETCH c_is_cols_present INTO x_count1;
700 CLOSE c_is_cols_present;
701
702 IF (x_count1 = 0)
703 THEN
704
705 /* Start with dropping the standard error columns in the ERR table */
706 x_sql := 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
707 || ' DROP (ERROR_CODE_RECORD, LOAD_DATE, ERROR_MESSAGE_RECORD) ';
708 EXECUTE IMMEDIATE x_sql;
709 log_message ('Dropping columns ERROR_CODE_RECORD, LOAD_DATE and ERROR_MESSAGE_RECORD from Demantra table T_SRC_SALES_TMPL_ERR');
710
711
712 /* Column - COMPONENT_CODE */
713 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL '
714 || ' ADD (COMPONENT_CODE VARCHAR2(2000)) ';
715 log_message ('Adding column COMPONENT_CODE to T_SRC_SALES_TMPL');
716
717 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
718 || ' ADD (COMPONENT_CODE VARCHAR2(2000)) ';
719 log_message ('Adding column COMPONENT_CODE to T_SRC_SALES_TMPL_ERR');
720
721
722 /* Column - EBS_BASE_MODEL_SR_PK */
723 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_SALES_TMPL', 'EBS_BASE_MODEL_SR_PK', 'NUMBER');
724 FETCH c_is_cols_present INTO x_count2;
725 CLOSE c_is_cols_present;
726
727 IF (x_count2 = 0)
728 THEN
729
730 /* Column - EBS_BASE_MODEL_SR_PK */
731 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL '
732 || ' ADD (EBS_BASE_MODEL_SR_PK NUMBER) ';
733 log_message ('Adding column EBS_BASE_MODEL_SR_PK to T_SRC_SALES_TMPL');
734
735 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
736 || ' ADD (EBS_BASE_MODEL_SR_PK NUMBER) ';
737 log_message ('Adding column EBS_BASE_MODEL_SR_PK to T_SRC_SALES_TMPL_ERR');
738
739 END IF;
740
741
742 /* Column - COMPONENT_CODE_LEGACY */
743 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_SALES_TMPL', 'COMPONENT_CODE_LEGACY', 'VARCHAR2');
744 FETCH c_is_cols_present INTO x_count2;
745 CLOSE c_is_cols_present;
746
747 IF (x_count2 = 0)
748 THEN
749
750 /* Column - COMPONENT_CODE_LEGACY */
751 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL '
752 || ' ADD (COMPONENT_CODE_LEGACY VARCHAR2(4000)) ';
753 log_message ('Adding column COMPONENT_CODE_LEGACY to T_SRC_SALES_TMPL');
754
755 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
756 || ' ADD (COMPONENT_CODE_LEGACY VARCHAR2(4000)) ';
757 log_message ('Adding column COMPONENT_CODE_LEGACY to T_SRC_SALES_TMPL_ERR');
758
759 END IF;
760
761
762 /* Column - EBS_BASE_MODEL_CODE */
763 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_SALES_TMPL', 'EBS_BASE_MODEL_CODE', 'VARCHAR2');
764 FETCH c_is_cols_present INTO x_count2;
765 CLOSE c_is_cols_present;
766
767 IF (x_count2 = 0)
768 THEN
769
770 /* Column - EBS_BASE_MODEL_CODE */
771 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL '
772 || ' ADD (EBS_BASE_MODEL_CODE VARCHAR2(240)) ';
773 log_message ('Adding column EBS_BASE_MODEL_CODE to T_SRC_SALES_TMPL');
774
775 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
776 || ' ADD (EBS_BASE_MODEL_CODE VARCHAR2(240)) ';
777 log_message ('Adding column EBS_BASE_MODEL_CODE to T_SRC_SALES_TMPL_ERR');
778
779 END IF;
780
781 /* Add back the standard error columns in the ERR table */
782 x_sql := 'ALTER TABLE ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR '
783 || ' ADD (ERROR_CODE_RECORD NUMBER(2), LOAD_DATE DATE, ERROR_MESSAGE_RECORD VARCHAR2(2000)) ';
784 EXECUTE IMMEDIATE x_sql;
785 log_message ('Adding columns ERROR_CODE_RECORD, LOAD_DATE and ERROR_MESSAGE_RECORD to Demantra table T_SRC_SALES_TMPL_ERR');
786
787 END IF;
788
789
790 log_message('Check if the table EP_T_SRC_SALES_TMPL_LD is present -');
791
792 x_count3 :=0;
793
794 SELECT count(1)
795 INTO x_count3
796 FROM dba_tables
797 WHERE owner = x_dem_schema
798 AND table_name = 'EP_T_SRC_SALES_TMPL_LD';
799
800 IF (x_count3 = 1)
801 THEN
802 log_message ('Check, drop and recreate the table EP_T_SRC_SALES_TMPL_LD - ');
803 x_count2 := -1;
804 x_sql := 'SELECT COUNT(1) FROM ( SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE owner = ''' || x_dem_schema || ''' and table_name = ''T_SRC_SALES_TMPL'' '
805 || ' MINUS '
806 || ' SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE owner = ''' || x_dem_schema || ''' and table_name = ''EP_T_SRC_SALES_TMPL_LD'' ) ';
807 log_debug (x_sql);
808 EXECUTE IMMEDIATE x_sql INTO x_count2;
809
810 IF (x_count2 <> 0)
811 THEN
812
813 log_message ('Dropping table EP_T_SRC_SALES_TMPL_LD');
814 EXECUTE IMMEDIATE 'DROP TABLE ' || x_dem_schema || '.EP_T_SRC_SALES_TMPL_LD';
815
816 log_message ('Creating table EP_T_SRC_SALES_TMPL_LD');
817 x_sql := 'CREATE TABLE ' || x_dem_schema || '.EP_T_SRC_SALES_TMPL_LD '
818 || ' AS '
819 || ' SELECT tsst.*, TRUNC(tsst.sales_date) AGGRE_SD '
820 || ' FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
821 || ' WHERE 1 = 2 ';
822 log_debug (x_sql);
823 EXECUTE IMMEDIATE x_sql;
824
825 END IF;
826 END IF;
827
828 -- Bug#13854593
829 IF (TO_NUMBER(x_dem_version) >= 12.2) THEN
830
831 SELECT count(1)
832 INTO x_count4
833 FROM dba_tables
834 WHERE owner = x_dem_schema
835 AND table_name = 'T_SRC_SALES_TMPL_TMP';
836
837 IF (x_count4 <> 0) THEN
838
839 log_message ('Calling CALL_DM_BUILD_PROCEDURES.');
840 x_sql := 'BEGIN ' || x_dem_schema || '.CALL_DM_BUILD_PROCEDURES; END;' ;
841
842 log_debug (x_sql);
843 EXECUTE IMMEDIATE x_sql;
844
845 END IF;
846
847 END IF;
848
849 /* In case of Demantra 7.2.X, add the following column
850 * Table - T_SRC_LOC_TMPL, Column to be added - T_EP_LR2A_DESC
851 * Table - T_SRC_LOC_TMPL_ERR, Column to be added - T_EP_LR2A_DESC
852 * Table - T_SRC_LOC_TMPL_ERR, Columns dropped/added - ERROR_CODE_RECORD , LOAD_DATE & ERROR_MESSAGE_RECORD
853 * Bug#8367471 - nallkuma
854 */
855
856 OPEN c_is_cols_present (x_dem_schema, 'T_SRC_LOC_TMPL', 'T_EP_LR2A_DESC', 'VARCHAR2');
857 FETCH c_is_cols_present INTO x_col_present_flag;
858 CLOSE c_is_cols_present;
859
860 IF( x_col_present_flag = 0 ) THEN
861
862 /* Add T_EP_LR2A_DESC column to the T_SRC_LOC_TMPL table*/
863 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_LOC_TMPL '
864 || ' ADD (T_EP_LR2A_DESC VARCHAR2(100)) ';
865 log_message ('Adding column T_EP_LR2A_DESC to T_SRC_LOC_TMPL');
866
867 /* Dropping the standard error columns in the ERR table */
868 x_sql := 'ALTER TABLE ' || x_dem_schema || '.T_SRC_LOC_TMPL_ERR '
869 || ' DROP (ERROR_CODE_RECORD, LOAD_DATE, ERROR_MESSAGE_RECORD) ';
870 EXECUTE IMMEDIATE x_sql;
871 log_message ('Dropping columns ERROR_CODE_RECORD, LOAD_DATE and ERROR_MESSAGE_RECORD from Demantra table T_SRC_LOC_TMPL_ERR');
872
873 /* Add T_EP_LR2A_DESC column to the T_SRC_LOC_TMPL _ERR table*/
874 EXECUTE IMMEDIATE 'ALTER TABLE ' || x_dem_schema || '.T_SRC_LOC_TMPL_ERR '
875 || ' ADD (T_EP_LR2A_DESC VARCHAR2(100)) ';
876 log_message ('Adding column T_EP_LR2A_DESC to T_SRC_LOC_TMPL_ERR');
877
878 /* Add back the standard error columns in the ERR table */
879 x_sql := 'ALTER TABLE ' || x_dem_schema || '.T_SRC_LOC_TMPL_ERR '
880 || ' ADD (ERROR_CODE_RECORD NUMBER(2), LOAD_DATE DATE, ERROR_MESSAGE_RECORD VARCHAR2(2000)) ';
881 EXECUTE IMMEDIATE x_sql;
882 log_message ('Adding columns ERROR_CODE_RECORD, LOAD_DATE and ERROR_MESSAGE_RECORD to Demantra table T_SRC_LOC_TMPL_ERR');
883
884 END IF;
885
886 /* In case of Demantra 7.3, create the synonym BIIO_DSR_SALES_DATA in the apps schema */
887 x_count1 := 0;
888 OPEN c_is_table_present (x_dem_schema, 'BIIO_DSR_SALES_DATA');
889 FETCH c_is_table_present INTO x_count1;
890 CLOSE c_is_table_present;
891
892 IF (x_count1 <> 0)
893 THEN
894
895 /* Create synonym BIIO_DSR_SALES_DATA to point to Demantra table BIIO_DSR_SALES_DATA */
896 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM BIIO_DSR_SALES_DATA FOR ' ||
897 x_dem_schema || '.BIIO_DSR_SALES_DATA';
898
899 EXECUTE IMMEDIATE x_create_synonym_sql;
900 log_message ('Created synonym BIIO_DSR_SALES_DATA');
901
902 END IF;
903 END IF;
904
905 update_dem_apcc_synonym(errbuf,retcode);
906 COMMIT;
907
908 EXCEPTION
909 WHEN OTHERS THEN
910 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
911 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
912 retcode := -1;
913 END;
914
915 /* Deletes the msd_dem_entities_inuse table if the new demantra schema is intstalled
916 * this will ensure that there will be no mapping between the seeded units in APPS and
917 * the (display uints,exchange rate,indexes) in Demantra */
918 PROCEDURE cleanup_entities_inuse(errbuf out nocopy varchar2, retcode out nocopy varchar2)
919 as
920 /*Deletes the msd_dem_entities_inuse table */
921 BEGIN
922
923 delete msd_dem_entities_inuse;
924 commit;
925 EXCEPTION
926 when others then
927 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
928 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
929 retcode := -1;
930 END;
931
932
933
934 /*** PUBLIC FUNCTIONS ***
935 * GET_ALL_ORGS
936 * DM_TIME_LEVEL
937 * GET_PARAMETER_VALUE
938 * GET_LOOKUP_VALUE
939 * GET_UOM_CODE
940 * GET_SR_INSTANCE_ID_FOR_ZONE
941 * UOM_CONVERT
942 * IS_PF_FCSTABLE_FOR_ITEM
943 * IS_PRODUCT_FAMILY_FORECASTABLE
944 * GET_SUPPLIER_CALENDAR
945 * GET_SAFETY_STOCK_ENDDATE
946 * GET_PERIOD_DATE_FOR_DUMMY
947 * GET_SITE_FOR_CSF
948 * IS_LAST_DATE_IN_BUCKET
949 * GET_SNO_PLAN_CUTOFF_DATE
950 * IS_SUPPLIER_CALENDAR_PRESENT
951 * UOM_CONV
952 * GET_LOOKUP_CODE
953 * GET_LEVEL_NAME
954 * GET_DEMANTRA_DATE
955 * IS_USE_NEW_SITE_FORMAT
956 * GET_DEMANTRA_VERSION
957 * GET_APP_ID_TEXT
958 * UPDATE_DEM_APCC_SYNONYM
959 * GET_CTO_EFFECTIVE_DATE
960 * GET_DEM_SYSDATE
961 * GET_DEM_CTO_BASE_MODEL
962 * GET_DEM_CTO_OPTION_CLASS
963 * GET_DEM_CTO_OPTION
964 * GET_DEM_SPF_BASE_MODEL
965 * GET_DEM_SPF_OPTION_CLASS
966 * GET_DEM_SPF_OPTION
967 * GET_ITEM_LABEL
968 * GET_ORG_LABEL
969 * GET_SITE_LABEL
970 * GET_DC_LABEL
971 * GET_SC_LABEL
972 * GET_ITEM_ID
973 * GET_ORG_ID
974 * GET_SITE_ID
975 * GET_DC_ID
976 * GET_SC_ID
977 * GET_ASSET_GROUP_LABEL
978 * GET_CLASS_CODE_LABEL
979 * GET_WORKORDER_ITEM
980 * GET_ASSET_GROUP_ID
981 * GET_CLASS_CODE_ID
982 * GET_SPF_SR_CAT_SET_ID
983 */
984
985 /*
986 * This function returns the comma(,) separated list of demand management enabled orgs
987 * belonging to the given org group.
988 */
989 FUNCTION GET_ALL_ORGS (
990 p_org_group IN VARCHAR2,
991 p_sr_instance_id IN NUMBER)
992 RETURN VARCHAR2
993 IS
994
995 TYPE REF_CURSOR_TYPE IS REF CURSOR;
996
997 c_ref_cursor REF_CURSOR_TYPE;
998
999 x_errbuf VARCHAR2(200) := NULL;
1000 x_retcode VARCHAR2(100) := NULL;
1001
1002 x_dblink VARCHAR2(50) := NULL;
1003 x_sql VARCHAR2(1000) := NULL;
1004 x_org VARCHAR2(10) := NULL;
1005 x_org_string VARCHAR2(1000) := NULL;
1006
1007 BEGIN
1008
1009 /* Get the db link to the source instance */
1010 msd_dem_common_utilities.get_dblink (
1011 x_errbuf,
1012 x_retcode,
1013 p_sr_instance_id,
1014 x_dblink);
1015
1016 IF (x_retcode = '-1')
1017 THEN
1018 RETURN NULL;
1019 END IF;
1020
1021 x_sql := 'SELECT mp.organization_code org_code ' ||
1022 ' FROM msc_instance_orgs mio, mtl_parameters' || x_dblink || ' mp ' ||
1023 ' WHERE mio.organization_id = mp.organization_id ' ||
1024 ' AND mio.sr_instance_id = :b_sr_instance_id ' ||
1025 ' AND mio.org_group = :b_org_group ' ||
1026 ' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 ';
1027
1028 OPEN c_ref_cursor FOR x_sql USING p_sr_instance_id, p_org_group;
1029
1030 LOOP
1031
1032 FETCH c_ref_cursor INTO x_org;
1033 EXIT WHEN c_ref_cursor%NOTFOUND;
1034
1035 IF (c_ref_cursor%ROWCOUNT = 1)
1036 THEN
1037 x_org_string := x_org;
1038 ELSE
1039 x_org_string := x_org_string || ',' || x_org;
1040 END IF;
1041
1042 END LOOP;
1043
1044 CLOSE c_ref_cursor;
1045
1046 RETURN x_org_string;
1047
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 RETURN NULL;
1051
1052 END GET_ALL_ORGS;
1053
1054
1055 /* This function returns the Active Demantra Data Model time level (Day/Month/week) */
1056
1057 FUNCTION DM_TIME_LEVEL RETURN VARCHAR2 IS
1058
1059 CURSOR C1 IS
1060 select MEANING
1061 from fnd_lookup_values_vl
1062 where lookup_type = 'MSD_DEM_TABLES'
1063 AND LOOKUP_CODE = 'DM_WIZ_DM_DEF';
1064
1065
1066
1067 L_STMT VARCHAR2(10000);
1068
1069 L_DM VARCHAR2(240);
1070
1071 L_TIM_LEVEL VARCHAR2(240);
1072
1073
1074 BEGIN
1075
1076 /*
1077 OPEN C1;
1078 FETCH C1 INTO L_DM;
1079 CLOSE C1;
1080 */
1081
1082 L_DM := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
1083
1084 L_STMT := 'SELECT TIME_BUCKET FROM '||
1085 L_DM||
1086 ' WHERE IS_ACTIVE=1 ';
1087
1088 EXECUTE IMMEDIATE L_STMT INTO L_TIM_LEVEL;
1089
1090 RETURN L_TIM_LEVEL;
1091
1092 END DM_TIME_LEVEL;
1093
1094
1095
1096 /*
1097 * This function returns the parameter_value in msd_dem_setup_parameters
1098 * given the parameter_name
1099 */
1100 FUNCTION GET_PARAMETER_VALUE (
1101 p_sr_instance_id NUMBER,
1102 p_parameter_name VARCHAR2)
1103 RETURN VARCHAR2
1104 IS
1105 x_errbuf VARCHAR2(200) := NULL;
1106 x_retcode VARCHAR2(100) := NULL;
1107
1108 x_dblink VARCHAR2(50) := NULL;
1109 x_parameter_value VARCHAR2(255) := NULL;
1110
1111 x_sr_category_set_id NUMBER := NULL;
1112
1113 BEGIN
1114
1115 get_dblink (
1116 x_errbuf,
1117 x_retcode,
1118 p_sr_instance_id,
1119 x_dblink);
1120
1121 IF (x_retcode = -1)
1122 THEN
1123 RETURN NULL;
1124 END IF;
1125
1126 EXECUTE IMMEDIATE 'SELECT parameter_value FROM msd_dem_setup_parameters' || x_dblink ||
1127 ' WHERE parameter_name = ''' || p_parameter_name || ''''
1128 INTO x_parameter_value;
1129
1130 /* Get the destination category set id for parameter = MSD_DEM_CATEGORY_SET_NAME */
1131 IF (p_parameter_name = 'MSD_DEM_CATEGORY_SET_NAME')
1132 THEN
1133 x_sr_category_set_id := to_number(x_parameter_value);
1134
1135 SELECT category_set_id
1136 INTO x_parameter_value
1137 FROM msc_category_set_id_lid
1138 WHERE sr_instance_id = p_sr_instance_id
1139 AND sr_category_set_id = x_sr_category_set_id;
1140
1141 END IF;
1142
1143 RETURN x_parameter_value;
1144
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 RETURN NULL;
1148
1149 END GET_PARAMETER_VALUE;
1150
1151
1152
1153 /*
1154 * This function returns the lookup_value given the lookup_type
1155 * and lookup_code
1156 */
1157 function get_lookup_value(p_lookup_type IN VARCHAR2,
1158 p_lookup_code IN VARCHAR2)
1159 return VARCHAR2
1160
1161 as
1162
1163 cursor get_lookup_value is
1164 select meaning
1165 from fnd_lookup_values
1166 where lookup_type = p_lookup_type
1167 and lookup_code = p_lookup_code
1168 and language = 'US';
1169
1170 cursor get_schema_name is
1171 select fnd_profile.value('MSD_DEM_SCHEMA')
1172 from dual;
1173
1174 CURSOR c_is_mdp_matrix_present (p_schema_name VARCHAR2)
1175 IS
1176 SELECT table_name
1177 FROM all_tables
1178 WHERE owner = upper(p_schema_name)
1179 AND table_name = 'MDP_MATRIX';
1180
1181 l_lookup_value varchar2(200);
1182 l_schema_name varchar2(200);
1183
1184 x_retval BOOLEAN := NULL;
1185 x_table_name VARCHAR2(50) := NULL;
1186 x_msd_schema_name VARCHAR2(50) := NULL;
1187 x_dummy1 VARCHAR2(50) := NULL;
1188 x_dummy2 VARCHAR2(50) := NULL;
1189
1190 begin
1191
1192 open get_lookup_value;
1193 fetch get_lookup_value into l_lookup_value;
1194 close get_lookup_value;
1195
1196 if p_lookup_type = 'MSD_DEM_TABLES' then
1197
1198 open get_schema_name;
1199 fetch get_schema_name into l_schema_name;
1200 close get_schema_name;
1201
1202 if l_schema_name is not null then
1203 l_lookup_value := l_schema_name || '.' || l_lookup_value;
1204 else
1205 return null;
1206 end if;
1207
1208 end if;
1209
1210 IF (p_lookup_type = 'MSD_DEM_DM_STAGING_TABLES')
1211 THEN
1212
1213 open get_schema_name;
1214 fetch get_schema_name into l_schema_name;
1215 close get_schema_name;
1216
1217 IF (l_schema_name IS NULL)
1218 THEN
1219 RETURN NULL;
1220 END IF;
1221
1222 OPEN c_is_mdp_matrix_present (l_schema_name);
1223 FETCH c_is_mdp_matrix_present INTO x_table_name;
1224 CLOSE c_is_mdp_matrix_present;
1225
1226 IF (x_table_name IS NOT NULL)
1227 THEN
1228 l_lookup_value := l_schema_name || '.' || l_lookup_value;
1229 ELSE
1230 x_retval := fnd_installation.get_app_info (
1231 'MSD',
1232 x_dummy1,
1233 x_dummy2,
1234 x_msd_schema_name);
1235
1236 l_lookup_value := x_msd_schema_name || '.' || l_lookup_value;
1237 END IF;
1238
1239 END IF;
1240
1241 return l_lookup_value;
1242
1243 end;
1244
1245
1246 /*
1247 * This function returns the UOM code given the display unit id
1248 */
1249 FUNCTION GET_UOM_CODE (
1250 p_unit_id IN NUMBER)
1251 RETURN VARCHAR2
1252 IS
1253 x_uom_code VARCHAR2(100) := NULL;
1254 BEGIN
1255 EXECUTE IMMEDIATE 'SELECT display_units FROM ' ||
1256 get_lookup_value ('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
1257 ' WHERE display_units_id = ' || p_unit_id
1258 INTO x_uom_code;
1259 RETURN x_uom_code;
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 RETURN NULL;
1263 END GET_UOM_CODE;
1264
1265
1266 /*
1267 * This function returns a sr_instance_id in which the zone is defined
1268 */
1269 FUNCTION GET_SR_INSTANCE_ID_FOR_ZONE (
1270 p_zone IN VARCHAR2)
1271 RETURN NUMBER
1272 IS
1273 x_sr_instance_id NUMBER := NULL;
1274 BEGIN
1275 SELECT sr_instance_id
1276 INTO x_sr_instance_id
1277 FROM msc_regions
1278 WHERE zone = p_zone
1279 AND rownum < 2;
1280
1281 RETURN x_sr_instance_id;
1282 EXCEPTION
1283 WHEN OTHERS THEN
1284 RETURN NULL;
1285 END GET_SR_INSTANCE_ID_FOR_ZONE;
1286
1287
1288 /*
1289 * This function returns the conversion rate for the given item, From UOM and To UOM
1290 */
1291 FUNCTION UOM_CONVERT (
1292 p_inventory_item_id IN NUMBER,
1293 p_precision IN NUMBER,
1294 p_from_unit IN VARCHAR2,
1295 p_to_unit IN VARCHAR2)
1296 RETURN NUMBER
1297 IS
1298
1299 x_uom_rate NUMBER := NULL;
1300
1301 BEGIN
1302
1303 IF ( p_from_unit IS NULL
1304 OR p_to_unit IS NULL)
1305 THEN
1306 RETURN 1;
1307 END IF;
1308
1309 msd_uom_conversion (
1310 p_from_unit,
1311 p_to_unit,
1312 p_inventory_item_id,
1313 x_uom_rate);
1314
1315 IF (x_uom_rate = -99999)
1316 THEN
1317 RETURN 1;
1318 END IF;
1319
1320 IF (p_precision IS NULL)
1321 THEN
1322 RETURN x_uom_rate;
1323 ELSE
1324 RETURN round (x_uom_rate, p_precision);
1325 END IF;
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 RETURN 1;
1330 END UOM_CONVERT;
1331
1332
1333
1334 /* This function returns 1 if the product family's forecast control is set
1335 * for the given item in the master org, else returns 2
1336 */
1337 FUNCTION IS_PF_FCSTABLE_FOR_ITEM (
1338 p_sr_inventory_item_id IN NUMBER,
1339 p_sr_instance_id IN NUMBER,
1340 p_master_org_id IN NUMBER)
1341 RETURN NUMBER
1342 IS
1343
1344 x_product_family_id NUMBER := NULL;
1345 x_is_fcstable NUMBER := NULL;
1346
1347 BEGIN
1348
1349 /* First get the product family id */
1350 SELECT msi.product_family_id
1351 INTO x_product_family_id
1352 FROM msc_system_items msi
1353 WHERE
1354 msi.plan_id = -1
1355 AND msi.sr_instance_id = p_sr_instance_id
1356 AND msi.organization_id = p_master_org_id
1357 AND msi.sr_inventory_item_id = p_sr_inventory_item_id;
1358
1359 IF (x_product_family_id IS NULL)
1360 THEN
1361 RETURN 2;
1362 END IF;
1363
1364 SELECT nvl(msi.ato_forecast_control, 3)
1365 INTO x_is_fcstable
1366 FROM msc_system_items msi
1367 WHERE msi.plan_id = -1
1368 AND msi.sr_instance_id = p_sr_instance_id
1369 AND msi.organization_id = p_master_org_id
1370 AND msi.inventory_item_id = x_product_family_id;
1371
1372 IF (x_is_fcstable = 3)
1373 THEN
1374 RETURN 2;
1375 END IF;
1376
1377 RETURN 1;
1378
1379 EXCEPTION
1380 WHEN OTHERS THEN
1381 RETURN 2;
1382 END IS_PF_FCSTABLE_FOR_ITEM;
1383
1384
1385
1386 /* This function returns 1 if the product family forecast control flag is set,
1387 * else returns 2
1388 */
1389 FUNCTION IS_PRODUCT_FAMILY_FORECASTABLE (
1390 p_inventory_item_id IN NUMBER,
1391 p_sr_instance_id IN NUMBER)
1392 RETURN NUMBER
1393 IS
1394
1395 x_errbuf VARCHAR2(200) := NULL;
1396 x_retcode VARCHAR2(100) := NULL;
1397
1398 x_dblink VARCHAR2(50) := NULL;
1399 x_sql VARCHAR2(255) := NULL;
1400
1401 x_return_value NUMBER := NULL;
1402 x_is_fcstable NUMBER := NULL;
1403
1404 x_instance_type NUMBER := NULL;
1405
1406 BEGIN
1407
1408 get_dblink (
1409 x_errbuf,
1410 x_retcode,
1411 p_sr_instance_id,
1412 x_dblink);
1413
1414 IF (x_retcode = -1)
1415 THEN
1416 RETURN 2;
1417 END IF;
1418
1419 EXECUTE IMMEDIATE 'select instance_type from msc_apps_instances where instance_id = :1'
1420 INTO x_instance_type
1421 USING p_sr_instance_id;
1422
1423 IF (x_instance_type IN (1,2,4))
1424 THEN
1425
1426 x_sql := 'BEGIN :x_ou1 := MSD_DEM_SR_UTIL.GET_MASTER_ORGANIZATION' || x_dblink || '; END;';
1427 EXECUTE IMMEDIATE x_sql USING OUT x_return_value;
1428
1429 ELSE
1430
1431 x_sql := 'SELECT TO_NUMBER(PARAMETER_VALUE) FROM MSD_DEM_SETUP_PARAMETERS WHERE PARAMETER_NAME = ''MSD_DEM_MASTER_ORG''';
1432 EXECUTE IMMEDIATE x_sql INTO x_return_value;
1433
1434 END IF;
1435
1436 SELECT nvl(msi.ato_forecast_control, 3)
1437 INTO x_is_fcstable
1438 FROM msc_system_items msi
1439 WHERE msi.plan_id = -1
1440 AND msi.sr_instance_id = p_sr_instance_id
1441 AND msi.organization_id = x_return_value
1442 AND msi.inventory_item_id = p_inventory_item_id;
1443
1444 IF (x_is_fcstable = 3)
1445 THEN
1446 RETURN 2;
1447 END IF;
1448
1449 RETURN 1;
1450
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 RETURN 2;
1454 END IS_PRODUCT_FAMILY_FORECASTABLE;
1455
1456
1457 /*
1458 * This function gets the calendar code
1459 */
1460 FUNCTION GET_SUPPLIER_CALENDAR (
1461 p_plan_id IN NUMBER,
1462 p_sr_instance_id IN NUMBER,
1463 p_organization_id IN NUMBER,
1464 p_inventory_item_id IN NUMBER,
1465 p_supplier_id IN NUMBER,
1466 p_supplier_site_id IN NUMBER,
1467 p_using_organization_id IN NUMBER)
1468 RETURN VARCHAR2
1469 IS
1470
1471 cursor c1 (p_plan_id in number, p_sr_instance_id IN NUMBER, p_organization_id IN number, p_inventory_item_id IN NUMBER,
1472 p_supplier_id in number, p_supplier_site_id in number, p_using_organization_id in number) IS
1473 select DELIVERY_CALENDAR_CODE
1474 from msc_item_suppliers
1475 where plan_id = p_plan_id
1476 and sr_instance_id = p_sr_instance_id
1477 and organization_id = p_organization_id
1478 and inventory_item_id = p_inventory_item_id
1479 and supplier_id = p_supplier_id
1480 and supplier_site_id = p_supplier_site_id
1481 and using_organization_id = p_using_organization_id;
1482
1483 cursor c2 (p_sr_instance_id IN NUMBER, p_organization_id IN number) IS
1484 select calendar_code
1485 from msc_trading_partners
1486 where partner_type = 3
1487 and sr_tp_id = p_organization_id
1488 and sr_instance_id = p_sr_instance_id;
1489
1490 l_ret varchar2(30) := null;
1491 Begin
1492
1493 open c1 (p_plan_id, p_sr_instance_id, p_organization_id, p_inventory_item_id,
1494 p_supplier_id, p_supplier_site_id, p_using_organization_id);
1495 fetch c1 into l_ret;
1496 close c1;
1497
1498 if l_ret is null then
1499 open c2 (p_sr_instance_id, p_organization_id);
1500 fetch c2 into l_ret;
1501 close c2;
1502 end if;
1503
1504 return l_ret;
1505 EXCEPTION when others then return NULL;
1506
1507 End get_supplier_calendar;
1508
1509 /*
1510 * This function gets the period end date
1511 */
1512 FUNCTION GET_SAFETY_STOCK_ENDDATE (
1513 p_plan_id IN NUMBER,
1514 p_sr_instance_id IN NUMBER,
1515 p_organization_id IN NUMBER,
1516 p_inventory_item_id IN NUMBER,
1517 p_period_start_date IN DATE)
1518 RETURN DATE
1519 IS
1520 cursor c1 (p_plan_id in number, p_sr_instance_id IN NUMBER, p_organization_id IN number,
1521 p_inventory_item_id IN NUMBER, p_period_start_date IN DATE) IS
1522 select min(period_start_date) -1 period_end_date
1523 from msc_safety_stocks
1524 where plan_id = p_plan_id
1525 and sr_instance_id = p_sr_instance_id
1526 and organization_id = p_organization_id
1527 and inventory_item_id = p_inventory_item_id
1528 and period_start_date > p_period_start_date;
1529
1530 cursor c2 (p_plan_id in number) IS
1531 select CURR_CUTOFF_DATE
1532 from msc_plans
1533 where plan_id = p_plan_id;
1534
1535 CURSOR c3 (p_date IN DATE) IS
1536 SELECT end_date
1537 FROM msd_dem_dates
1538 WHERE p_date BETWEEN start_date AND end_date;
1539
1540 l_ret date := null;
1541 Begin
1542
1543 open c1 (p_plan_id, p_sr_instance_id, p_organization_id, p_inventory_item_id, p_period_start_date);
1544 fetch c1 into l_ret;
1545 close c1;
1546
1547 if l_ret is null then
1548 open c2 (p_plan_id);
1549 fetch c2 into l_ret;
1550 close c2;
1551
1552 if (upper(msd_dem_common_utilities.dm_time_level) <> 'DAY') then
1553 open c3(l_ret);
1554 fetch c3 into l_ret;
1555 close c3;
1556 end if;
1557
1558 end if;
1559
1560 return l_ret;
1561 EXCEPTION when others then return NULL;
1562
1563 End get_safety_stock_enddate;
1564
1565
1566 /*
1567 * Returns a valid date from the table INPUTS in Demantra
1568 */
1569 FUNCTION GET_PERIOD_DATE_FOR_DUMMY
1570 RETURN DATE
1571 IS
1572 x_dummy_date DATE := NULL;
1573 BEGIN
1574
1575 EXECUTE IMMEDIATE 'SELECT datet FROM ( '
1576 || ' SELECT datet FROM '
1577 || msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS')
1578 || ' WHERE datet > sysdate '
1579 || ' ORDER BY datet ) '
1580 || ' WHERE rownum < 2 '
1581 INTO x_dummy_date;
1582
1583 RETURN x_dummy_date;
1584
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 RETURN NULL;
1588 END GET_PERIOD_DATE_FOR_DUMMY;
1589
1590
1591
1592 /*
1593 * Given, the instance, customer and/or site, this function returns
1594 * the site level member name. If only the customer is specified then
1595 * then any arbit site belonging to the customer is returned.
1596 */
1597 FUNCTION GET_SITE_FOR_CSF (
1598 p_sr_instance_id IN NUMBER,
1599 p_customer_id IN NUMBER,
1600 p_customer_site_id IN NUMBER)
1601 RETURN VARCHAR2
1602 IS
1603 x_site VARCHAR2(255);
1604 x_dummy_site VARCHAR2(100) := msd_dem_sr_util.get_null_code;
1605 l_sql varchar2(1000) := null ;
1606 BEGIN
1607
1608 IF (LG_VAR_SITE_CODE_FORMAT IS NULL)
1609 THEN
1610 LG_VAR_SITE_CODE_FORMAT := IS_USE_NEW_SITE_FORMAT;
1611 END IF;
1612
1613 IF (LG_VAR_SITE_CODE_FORMAT = 0)
1614 THEN
1615
1616 IF (p_customer_site_id IS NOT NULL)
1617 THEN
1618
1619 SELECT substrb(mtp.partner_name, 1, 50)
1620 || ':' || mtil.sr_cust_account_number
1621 || ':' || mtps.location
1622 || ':' || mtps.operating_unit_name
1623 INTO x_site
1624 FROM msc_trading_partner_sites mtps,
1625 msc_trading_partners mtp,
1626 msc_tp_id_lid mtil,
1627 msc_tp_site_id_lid mtsil
1628 WHERE
1629 mtps.partner_site_id = p_customer_site_id
1630 AND mtp.partner_id = mtps.partner_id
1631 AND mtil.tp_id = mtp.partner_id
1632 AND mtil.sr_instance_id = p_sr_instance_id
1633 AND mtsil.tp_site_id = mtps.partner_site_id
1634 AND mtsil.sr_instance_id = p_sr_instance_id
1635 AND mtsil.sr_cust_acct_id = mtil.sr_tp_id
1636 AND rownum < 2;
1637
1638 ELSIF (p_customer_id IS NOT NULL)
1639 THEN
1640 /* bug#9634704 -- nalkuma*/
1641 l_sql := ' select s.site
1642 FROM msc_trading_partners mtp,
1643 msc_tp_id_lid mtil,
1644 msc_trading_partner_sites mtps,
1645 msc_tp_site_id_lid mtsil, '
1646 || C_MSD_DEM_SCHEMA || '.t_ep_site s
1647 WHERE
1648 mtp.partner_id = ' || p_customer_id ||
1649 ' AND mtil.tp_id = mtp.partner_id
1650 AND mtil.sr_instance_id = ' || p_sr_instance_id ||
1651 ' AND mtps.partner_id = mtp.partner_id
1652 AND mtps.tp_site_code = ''SHIP_TO''
1653 AND mtsil.tp_site_id = mtps.partner_site_id
1654 AND mtsil.sr_instance_id = ' || p_sr_instance_id ||
1655 ' AND mtsil.sr_cust_acct_id = mtil.sr_tp_id -- bug14694419-12.2.1/14694420-12.3 KKHATRI
1656 AND lower(s.site) = lower( substrb(mtp.partner_name, 1, 50)
1657 || '':'' || mtil.sr_cust_account_number
1658 || '':'' || mtps.location
1659 || '':'' || mtps.operating_unit_name )
1660 AND rownum < 2 ';
1661
1662 execute immediate l_sql into x_site ;
1663
1664 ELSE
1665 x_site := x_dummy_site;
1666 END IF;
1667
1668 ELSE
1669
1670 IF (p_customer_site_id IS NOT NULL)
1671 THEN
1672
1673 SELECT /* INDEX(mtpsil MSC_TP_SITE_ID_LID_N1) */
1674 to_char(p_sr_instance_id) || '::' || to_char(mtpsil.sr_tp_site_id)
1675 INTO x_site
1676 FROM msc_tp_site_id_lid mtpsil
1677 WHERE
1678 mtpsil.tp_site_id = p_customer_site_id
1679 AND mtpsil.sr_instance_id = p_sr_instance_id
1680 AND mtpsil.partner_type = 2;
1681
1682 ELSIF (p_customer_id IS NOT NULL)
1683 THEN
1684 /* bug#9634704 -- nalkuma*/
1685 l_sql := ' select /* INDEX(mtps MSC_TRADING_PARTNER_SITES_U3) */
1686 to_char(' || p_sr_instance_id || ') || ''::'' || to_char(mtsil.sr_tp_site_id)
1687 from
1688 msc_trading_partner_sites mtps,
1689 msc_tp_site_id_lid mtsil, '
1690 || C_MSD_DEM_SCHEMA || '.t_ep_site s
1691 WHERE
1692 mtps.partner_id = ' || p_customer_id ||
1693 ' AND mtps.tp_site_code = ''SHIP_TO''
1694 AND mtsil.tp_site_id = mtps.partner_site_id
1695 AND mtsil.sr_instance_id = ' || p_sr_instance_id ||
1696 ' AND lower(s.site) = to_char(' || p_sr_instance_id || ') || ''::'' || to_char(mtsil.sr_tp_site_id)
1697 AND rownum < 2 ';
1698
1699 execute immediate l_sql into x_site ;
1700
1701 ELSE
1702 x_site := x_dummy_site;
1703 END IF;
1704
1705 END IF;
1706
1707 -- bug#12831044
1708 x_site := replace(x_site, '''','');
1709
1710 RETURN x_site;
1711
1712 EXCEPTION
1713 WHEN OTHERS THEN
1714 RETURN x_dummy_site;
1715 END GET_SITE_FOR_CSF;
1716
1717
1718
1719 /*
1720 * Given, the instance, calendar_code, calendar_date, this function
1721 * returns 1 if the date is the last date in its demantra bucket,
1722 * else returns 2.
1723 * Note: This function requires the table msd_dem_dates to be
1724 * populated.
1725 */
1726 FUNCTION IS_LAST_DATE_IN_BUCKET (
1727 p_sr_instance_id IN NUMBER,
1728 p_calendar_code IN VARCHAR2,
1729 p_calendar_date IN DATE)
1730 RETURN NUMBER
1731 IS
1732 x_max_date DATE := NULL;
1733 BEGIN
1734
1735 IF (upper(msd_dem_common_utilities.dm_time_level) = 'DAY')
1736 THEN
1737 RETURN 1;
1738 END IF;
1739
1740 SELECT max(mcd.calendar_date)
1741 INTO x_max_date
1742 FROM msd_dem_dates mdd,
1743 msc_calendar_dates mcd
1744 WHERE
1745 p_calendar_date BETWEEN mdd.start_date AND mdd.end_date
1746 AND mcd.sr_instance_id = p_sr_instance_id
1747 AND mcd.calendar_code = p_calendar_code
1748 AND mcd.exception_set_id = -1
1749 AND mcd.calendar_date BETWEEN mdd.start_date AND mdd.end_date
1750 AND mcd.seq_num IS NOT NULL;
1751
1752 IF (p_calendar_date = x_max_date)
1753 THEN
1754 RETURN 1;
1755 END IF;
1756
1757 RETURN 2;
1758
1759 EXCEPTION
1760 WHEN OTHERS THEN
1761 RETURN 2;
1762 END IS_LAST_DATE_IN_BUCKET;
1763
1764
1765
1766 /*
1767 * Given the plan id of a SNO plan, this function returns
1768 * the cutoff date for the plan.
1769 */
1770 FUNCTION GET_SNO_PLAN_CUTOFF_DATE (
1771 p_plan_id IN NUMBER)
1772 RETURN DATE
1773 IS
1774
1775 x_plan_cutoff_date DATE := NULL;
1776
1777 x_sr_instance_id NUMBER := NULL;
1778 x_organization_id NUMBER := NULL;
1779 x_curr_start_date DATE := NULL;
1780 x_planned_bucket NUMBER := NULL;
1781 x_planned_bucket_type NUMBER := NULL;
1782
1783 x_calendar_code VARCHAR2(100) := NULL;
1784
1785 BEGIN
1786
1787 /* Get Plan Info */
1788 SELECT
1789 sr_instance_id,
1790 organization_id,
1791 curr_start_date,
1792 planned_bucket,
1793 planned_bucket_type
1794 INTO
1795 x_sr_instance_id,
1796 x_organization_id,
1797 x_curr_start_date,
1798 x_planned_bucket,
1799 x_planned_bucket_type
1800 FROM
1801 msc_plans
1802 WHERE
1803 plan_id = p_plan_id;
1804
1805 /* Get calendar for the plan owning org */
1806 SELECT
1807 calendar_code
1808 INTO
1809 x_calendar_code
1810 FROM
1811 msc_trading_partners
1812 WHERE
1813 partner_type = 3
1814 AND sr_tp_id = x_organization_id
1815 AND sr_instance_id = x_sr_instance_id;
1816
1817 /* Get cut-off date */
1818 IF (x_planned_bucket_type = 2) /* WEEK */
1819 THEN
1820
1821 SELECT
1822 max(next_date) - 1
1823 INTO x_plan_cutoff_date
1824 FROM
1825 ( SELECT
1826 next_date
1827 FROM
1828 msc_cal_week_start_dates
1829 WHERE
1830 calendar_code = x_calendar_code
1831 AND sr_instance_id = x_sr_instance_id
1832 AND week_start_date > x_curr_start_date
1833 ORDER BY next_date)
1834 WHERE
1835 rownum < x_planned_bucket + 1;
1836
1837 ELSIF (x_planned_bucket_type = 3) /* PERIOD */
1838 THEN
1839
1840 SELECT
1841 max(next_date) - 1
1842 INTO x_plan_cutoff_date
1843 FROM
1844 ( SELECT
1845 next_date
1846 FROM
1847 msc_period_start_dates
1848 WHERE
1849 calendar_code = x_calendar_code
1850 AND sr_instance_id = x_sr_instance_id
1851 AND period_start_date > x_curr_start_date
1852 ORDER BY next_date)
1853 WHERE
1854 rownum < x_planned_bucket + 1;
1855
1856 ELSE
1857 RETURN NULL;
1858 END IF;
1859
1860 RETURN x_plan_cutoff_date;
1861
1862 EXCEPTION
1863 WHEN OTHERS THEN
1864 RETURN NULL;
1865 END GET_SNO_PLAN_CUTOFF_DATE;
1866
1867
1868
1869 /*
1870 * This function returns 1 if a supplier calendar is present else returns 2.
1871 */
1872 FUNCTION IS_SUPPLIER_CALENDAR_PRESENT (
1873 p_plan_id IN NUMBER,
1874 p_sr_instance_id IN NUMBER,
1875 p_organization_id IN NUMBER,
1876 p_inventory_item_id IN NUMBER,
1877 p_supplier_id IN NUMBER,
1878 p_supplier_site_id IN NUMBER,
1879 p_using_organization_id IN NUMBER)
1880 RETURN NUMBER
1881 IS
1882
1883 cursor c1 (p_plan_id in number, p_sr_instance_id IN NUMBER, p_organization_id IN number, p_inventory_item_id IN NUMBER,
1884 p_supplier_id in number, p_supplier_site_id in number, p_using_organization_id in number) IS
1885 select DELIVERY_CALENDAR_CODE
1886 from msc_item_suppliers
1887 where plan_id = p_plan_id
1888 and sr_instance_id = p_sr_instance_id
1889 and organization_id = p_organization_id
1890 and inventory_item_id = p_inventory_item_id
1891 and supplier_id = p_supplier_id
1892 and supplier_site_id = p_supplier_site_id
1893 and using_organization_id = p_using_organization_id;
1894
1895 l_ret varchar2(30) := null;
1896 l_ret1 number := 2;
1897
1898 BEGIN
1899
1900 open c1 (p_plan_id, p_sr_instance_id, p_organization_id, p_inventory_item_id,
1901 p_supplier_id, p_supplier_site_id, p_using_organization_id);
1902 fetch c1 into l_ret;
1903 close c1;
1904
1905 if l_ret is not null then
1906 l_ret1 := 1;
1907 end if;
1908
1909 RETURN l_ret1;
1910
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 RETURN 2;
1914
1915 END IS_SUPPLIER_CALENDAR_PRESENT;
1916
1917
1918
1919 /*
1920 * Given the item and the uom code, this function gives the conversion factor
1921 * to the base uom of the item.
1922 */
1923 FUNCTION UOM_CONV (
1924 p_sr_instance_id IN NUMBER,
1925 p_uom_code IN VARCHAR2,
1926 p_inventory_item_id IN NUMBER DEFAULT NULL)
1927 RETURN NUMBER
1928 IS
1929
1930 x_base_uom VARCHAR2(3);
1931 x_conv_rate NUMBER :=1;
1932 x_master_org NUMBER;
1933 x_master_uom VARCHAR2(3);
1934
1935 BEGIN
1936
1937 x_master_org := get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
1938
1939 SELECT nvl(uom_code,'Ea')
1940 INTO x_master_uom
1941 FROM msc_system_items
1942 WHERE plan_id = -1
1943 AND sr_instance_id = p_sr_instance_id
1944 AND organization_id = x_master_org
1945 AND inventory_item_id = p_inventory_item_id;
1946
1947 /* Convert to Master org primary uom */
1948
1949 msd_uom_conversion(p_uom_code,
1950 x_master_uom,
1951 p_inventory_item_id,
1952 x_conv_rate);
1953
1954 RETURN x_conv_rate;
1955
1956 EXCEPTION
1957 WHEN OTHERS THEN
1958 RETURN 1;
1959
1960 END UOM_CONV;
1961
1962
1963 /*
1964 * This function given the Demantra lookup table name and lookup ID
1965 * returns the lookup Code
1966 */
1967 FUNCTION GET_LOOKUP_CODE (
1968 p_lookup_table_name IN VARCHAR2,
1969 p_lookup_id IN NUMBER)
1970 RETURN VARCHAR2
1971 IS
1972 x_ret_value VARCHAR2(100) := NULL;
1973 x_col_ep_id_present NUMBER := 0 ;
1974 x_col_id_present NUMBER := 0 ;
1975 x_col_suffix VARCHAR2(10) := NULL;
1976 x_select_col VARCHAR2(100) := NULL;
1977
1978
1979 BEGIN
1980
1981 -- bug#14694463 nallkuma
1982 SELECT nvl(count(1),0)
1983 INTO x_col_ep_id_present
1984 FROM dba_tab_columns
1985 WHERE owner = upper(C_MSD_DEM_SCHEMA)
1986 AND table_name = upper(p_lookup_table_name)
1987 and column_name = upper(p_lookup_table_name)|| '_EP_ID' ;
1988
1989 SELECT nvl(count(1),0)
1990 INTO x_col_id_present
1991 FROM dba_tab_columns
1992 WHERE owner = upper(C_MSD_DEM_SCHEMA)
1993 AND table_name = upper(p_lookup_table_name)
1994 and column_name = upper(p_lookup_table_name)|| '_ID' ;
1995
1996 IF (x_col_ep_id_present = 1 AND x_col_id_present = 0) THEN
1997 x_col_suffix := '_EP_ID' ;
1998 x_select_col := SUBSTR(p_lookup_table_name,6) ;
1999 ELSIF(x_col_ep_id_present = 0 AND x_col_id_present = 1) THEN
2000 x_col_suffix := '_ID' ;
2001 x_select_col := p_lookup_table_name || '_code ' ;
2002 END IF ;
2003
2004 EXECUTE IMMEDIATE 'SELECT ' || x_select_col || ' FROM '
2005 || C_MSD_DEM_SCHEMA || '.' || p_lookup_table_name
2006 || ' where ' || p_lookup_table_name || x_col_suffix || ' = ' || to_char(p_lookup_id)
2007 INTO x_ret_value;
2008
2009 RETURN x_ret_value;
2010
2011 EXCEPTION
2012 WHEN OTHERS THEN
2013 RETURN NULL;
2014
2015 END GET_LOOKUP_CODE;
2016
2017
2018
2019 /*
2020 * This function given the Demantra lookup table name and lookup ID
2021 * returns the lookup Code
2022 */
2023 FUNCTION GET_LEVEL_NAME (
2024 p_it_level_code IN NUMBER)
2025 RETURN VARCHAR2
2026 IS
2027 x_ret_value VARCHAR2(100) := NULL;
2028 BEGIN
2029
2030 EXECUTE IMMEDIATE 'SELECT table_label FROM ' || C_MSD_DEM_SCHEMA || '.group_tables'
2031 || ' WHERE group_table_id = ' || to_char(p_it_level_code)
2032 INTO x_ret_value;
2033
2034 RETURN x_ret_value;
2035
2036 EXCEPTION
2037 WHEN OTHERS THEN
2038 RETURN NULL;
2039
2040 END GET_LEVEL_NAME;
2041
2042
2043
2044
2045 /*
2046 * Given a date, the function returns the the bucket date to which the date belongs.
2047 * If p_date is null, p_from is 1, the the function returns
2048 * max of (min_sales_date, sysdate - 2 years )
2049 * If p_date is null, p_from is 2, the the function returns
2050 * min of (max_fore_sales_date, sysdate + 2 years )
2051 */
2052 FUNCTION GET_DEMANTRA_DATE (
2053 p_date IN DATE,
2054 p_from IN NUMBER)
2055 RETURN DATE
2056 IS
2057 x_dem_nls_date_format VARCHAR2(100) := NULL;
2058 x_dem_min_sales_date VARCHAR2(100) := NULL;
2059 x_dem_min_sales_date_d DATE := NULL;
2060 x_dem_max_fore_sales_date VARCHAR2(100) := NULL;
2061 x_dem_max_fore_sales_date_d DATE := NULL;
2062
2063 x_date DATE := NULL;
2064 BEGIN
2065
2066 IF (p_date IS NULL)
2067 THEN
2068
2069 IF (p_from = 1)
2070 THEN
2071
2072 IF (C_DEM_MIN_SALES_DATE_D IS NULL)
2073 THEN
2074
2075 EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.DB_PARAMS'
2076 || ' WHERE lower(pname) = ''nls_date_format'' '
2077 INTO x_dem_nls_date_format;
2078
2079 EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.SYS_PARAMS'
2080 || ' WHERE lower(pname) = ''min_sales_date'' '
2081 INTO x_dem_min_sales_date;
2082
2083 IF (x_dem_min_sales_date IS NOT NULL)
2084 THEN
2085 x_dem_min_sales_date_d := to_date(x_dem_min_sales_date, x_dem_nls_date_format);
2086 ELSE
2087 x_dem_min_sales_date_d := sysdate - 365*2;
2088 END IF;
2089
2090
2091
2092 SELECT datet
2093 INTO C_DEM_MIN_SALES_DATE_D
2094 FROM msd_dem_dates
2095 WHERE x_dem_min_sales_date_d between start_date and end_date;
2096
2097 END IF;
2098
2099 RETURN C_DEM_MIN_SALES_DATE_D;
2100
2101 ELSE
2102
2103 IF (C_DEM_MAX_FORE_SALES_DATE_D IS NULL)
2104 THEN
2105
2106 EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.DB_PARAMS'
2107 || ' WHERE lower(pname) = ''nls_date_format'' '
2108 INTO x_dem_nls_date_format;
2109
2110 EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.SYS_PARAMS'
2111 || ' WHERE lower(pname) = ''max_fore_sales_date'' '
2112 INTO x_dem_max_fore_sales_date;
2113
2114 IF (x_dem_max_fore_sales_date IS NOT NULL)
2115 THEN
2116 x_dem_max_fore_sales_date_d := to_date(x_dem_max_fore_sales_date, x_dem_nls_date_format);
2117 ELSE
2118 x_dem_max_fore_sales_date_d := sysdate + 365*2;
2119 END IF;
2120
2121 SELECT datet
2122 INTO C_DEM_MAX_FORE_SALES_DATE_D
2123 FROM msd_dem_dates
2124 WHERE x_dem_max_fore_sales_date_d between start_date and end_date;
2125
2126 END IF;
2127
2128 RETURN C_DEM_MAX_FORE_SALES_DATE_D;
2129
2130 END IF;
2131
2132 ELSE
2133
2134 SELECT datet
2135 INTO x_date
2136 FROM msd_dem_dates
2137 WHERE p_date between start_date and end_date;
2138
2139 RETURN x_date;
2140
2141 END IF;
2142
2143 RETURN sysdate;
2144
2145 EXCEPTION
2146 WHEN OTHERS THEN
2147 RETURN sysdate;
2148
2149 END GET_DEMANTRA_DATE;
2150
2151
2152
2153
2154 /*
2155 * The function is used to determine whether to use the new site format or not.
2156 * Returns -
2157 * 1 - use new site format, from 7.3.x onwards
2158 * 0 - use old site format, for 7.2.x release
2159 */
2160 FUNCTION IS_USE_NEW_SITE_FORMAT
2161 RETURN NUMBER
2162 IS
2163
2164 BEGIN
2165
2166 IF (LG_VAR_SITE_CODE_FORMAT IS NULL)
2167 THEN
2168 IF (nvl(fnd_profile.value('MSD_DEM_SITE_CODE_FORMAT'), 2) = 2)
2169 THEN
2170 LG_VAR_SITE_CODE_FORMAT := 0;
2171 ELSE
2172 LG_VAR_SITE_CODE_FORMAT := 1;
2173 END IF;
2174 END IF;
2175
2176 RETURN LG_VAR_SITE_CODE_FORMAT;
2177
2178 EXCEPTION
2179 WHEN OTHERS THEN
2180 RETURN -1;
2181
2182 END IS_USE_NEW_SITE_FORMAT;
2183
2184
2185
2186
2187 /*
2188 * The function returns the Demantra release version.
2189 */
2190 FUNCTION GET_DEMANTRA_VERSION
2191 RETURN VARCHAR2
2192 IS
2193
2194 x_present NUMBER := NULL;
2195
2196 BEGIN
2197
2198 EXECUTE IMMEDIATE 'SELECT count(1) FROM ' || FND_PROFILE.VALUE('MSD_DEM_SCHEMA') ||
2199 '.VERSION_DETAILS ' ||
2200 ' WHERE version LIKE ''7.2%'''
2201 INTO x_present;
2202
2203 IF (x_present = 1)
2204 THEN
2205 RETURN '7.2';
2206 END IF;
2207
2208 RETURN '7.3';
2209
2210 EXCEPTION
2211 WHEN OTHERS THEN
2212 RETURN NULL;
2213
2214 END GET_DEMANTRA_VERSION;
2215
2216
2217
2218
2219 /*
2220 * The function returns the request Demantra value or the join condition
2221 * given the lookup code. This function uses APP ID for Demantra 7.3 release
2222 * and internal ids for Demantra 7.2 release.
2223 */
2224 FUNCTION GET_APP_ID_TEXT (
2225 p_lookup_type IN VARCHAR2,
2226 p_lookup_code IN VARCHAR2,
2227 p_is_select IN NUMBER,
2228 p_column_name IN VARCHAR2)
2229 RETURN VARCHAR2
2230 IS
2231
2232 CURSOR c_get_lookup_value
2233 IS
2234 SELECT meaning,
2235 attribute1,
2236 attribute2,
2237 attribute3,
2238 attribute4
2239 FROM fnd_lookup_values_vl
2240 WHERE lookup_type = p_lookup_type
2241 AND lookup_code = p_lookup_code;
2242
2243 x_dem_version VARCHAR2(10) := GET_DEMANTRA_VERSION;
2244 x_dem_schema VARCHAR2(100) := fnd_profile.value('MSD_DEM_SCHEMA');
2245 x_sql VARCHAR2(2000) := NULL;
2246 x_lk_meaning VARCHAR2(500) := NULL;
2247 x_lk_attribute1 VARCHAR2(500) := NULL;
2248 x_lk_attribute2 VARCHAR2(500) := NULL;
2249 x_lk_attribute3 VARCHAR2(500) := NULL;
2250 x_lk_attribute4 VARCHAR2(500) := NULL;
2251
2252 x_return_value VARCHAR2(500) := NULL;
2253
2254 BEGIN
2255
2256 IF (x_dem_version IS NULL)
2257 THEN
2258 RETURN NULL;
2259 END IF;
2260
2261 OPEN c_get_lookup_value;
2262 FETCH c_get_lookup_value INTO x_lk_meaning,
2263 x_lk_attribute1,
2264 x_lk_attribute2,
2265 x_lk_attribute3,
2266 x_lk_attribute4;
2267 CLOSE c_get_lookup_value;
2268
2269 IF (x_lk_meaning IS NULL)
2270 THEN
2271 RETURN NULL;
2272 END IF;
2273
2274 IF (x_dem_version = '7.2')
2275 THEN
2276
2277 IF (p_is_select = 1)
2278 THEN
2279 x_sql := 'SELECT ' || p_column_name || ' FROM '
2280 || x_dem_schema || '.' || x_lk_attribute3
2281 || ' WHERE ' || x_lk_attribute4 || ' = ''' || x_lk_attribute2 || '''';
2282 EXECUTE IMMEDIATE x_sql INTO x_return_value;
2283
2284 ELSE
2285 x_return_value := x_lk_attribute4 || ' = ''' || x_lk_attribute2 || '''';
2286 END IF;
2287
2288 ELSE
2289
2290 IF (p_is_select = 1)
2291 THEN
2292
2293 x_sql := 'SELECT ' || p_column_name || ' FROM '
2294 || x_dem_schema || '.' || x_lk_attribute3
2295 || ' WHERE lower(application_id) = lower(''' || x_lk_attribute1 || ''')';
2296 EXECUTE IMMEDIATE x_sql INTO x_return_value;
2297
2298 ELSE
2299 x_return_value := 'lower(application_id)' || ' = lower(''' || x_lk_attribute1 || ''')';
2300 END IF;
2301
2302 END IF;
2303
2304 RETURN x_return_value;
2305
2306 EXCEPTION
2307 WHEN OTHERS THEN
2308 RETURN NULL;
2309 END GET_APP_ID_TEXT;
2310
2311
2312 /*
2313 * Procedure Name - UPDATE_DEM_APCC_SYNONYM
2314 * This procedure creates the required dummy objets for APCC
2315 * 1) Checks if demantra is installed and the mview created
2316 * 1.1.a) If mview is available, drop it.
2317 * 1.1.b) Create a new mview with the same name - BIEO_OBI_MV
2318 * 1.2) If demantra is not installed, and dummy table available
2319 * 1.2.a) Drop the dummy table
2320 * 1.2.b) Create the dummy table - MSD_DEM_BIEO_OBI_MV_DUMMY
2321 * 2) Create synonym MSD_DEM_BIEO_OBI_MV_SYN accordingly.
2322 *
2323 */
2324
2325 PROCEDURE UPDATE_DEM_APCC_SYNONYM(
2326 errbuf out NOCOPY varchar2,
2327 retcode out NOCOPY varchar2
2328 )
2329 IS
2330 CURSOR c_check_expview(schema_owner varchar2) IS
2331 SELECT object_name
2332 FROM dba_objects
2333 WHERE owner = upper(schema_owner)
2334 AND object_type = 'MATERIALIZED VIEW'
2335 AND object_name = 'BIEO_OBI_MV'
2336 ORDER BY created DESC;
2337
2338 CURSOR c_check_table IS
2339 SELECT object_name,owner
2340 FROM dba_objects
2341 WHERE owner = owner
2342 AND object_type = 'TABLE'
2343 AND object_name = 'MSD_DEM_BIEO_OBI_MV_DUMMY'
2344 ORDER BY created DESC;
2345
2346 x_dem_schema VARCHAR2(50) := NULL;
2347 x_expview VARCHAR2(50) := NULL;
2348 x_table VARCHAR2(50) := NULL;
2349 x_table_owner VARCHAR2(50) := NULL;
2350 x_create_synonym_sql VARCHAR2(200) := NULL;
2351 x_create_table_sql VARCHAR2(3000) := NULL;
2352 x_create_view_sql VARCHAR2(2000) := NULL;
2353 x_small_sql VARCHAR2(1000) := NULL;
2354 x_dmtra_version number(10) := to_number(fnd_profile.value('MSD_DEM_VERSION')) ;
2355 x_syn_base VARCHAR2(50) := 'MSD_DEM_BIEO_OBI_MV_DUMMY';
2356 var_boolean boolean;
2357 var_dummy1 varchar2(100);
2358 var_dummy2 varchar2(100);
2359 var_msd_schema_name varchar2(50);
2360 /* for bug 13968307 */
2361 x_boolean BOOLEAN := NULL;
2362 x_dummy1 VARCHAR2(100) := NULL;
2363 x_dummy2 VARCHAR2(100) := NULL;
2364 x_msd_schema VARCHAR2(30) := NULL;
2365 /* End for bug 13968307 */
2366
2367 BEGIN
2368
2369 x_dem_schema := msd_dem_demantra_utilities.get_demantra_schema;
2370 /* for bug 13968307 */
2371 X_boolean := fnd_installation.get_app_info ('MSD', X_dummy1, X_dummy2, X_msd_schema);
2372 x_syn_base:= X_msd_schema || '.' || x_syn_base;
2373 /* End for bug 13968307 */
2374
2375 OPEN c_check_expview(x_dem_schema);
2376 FETCH c_check_expview
2377 INTO x_expview;
2378 CLOSE c_check_expview;
2379
2380 OPEN c_check_table;
2381 FETCH c_check_table
2382 INTO x_table,x_table_owner;
2383 CLOSE c_check_table;
2384
2385 /* Demantra is Installed */
2386
2387
2388 IF(x_dem_schema IS NOT NULL) THEN
2389 /* The export profile view created*/
2390 IF(x_expview IS NOT NULL) THEN
2391 /*Create a dummy materialized view with the same definition as that of the export profile mview*/
2392 x_small_sql := 'DROP MATERIALIZED VIEW '||x_dem_schema||'."BIEO_OBI_MV" ';
2393 EXECUTE IMMEDIATE x_small_sql;
2394 END IF;
2395
2396 x_create_view_sql := 'CREATE MATERIALIZED VIEW '||x_dem_schema||'."BIEO_OBI_MV" build deferred as
2397 select datet SDATE
2398 ,1 LEVEL1
2399 ,1 LEVEL2
2400 ,1 LEVEL3
2401 ,1 LEVEL4
2402 ,1 LEVEL5
2403 ,1 EBS_BH_BOOK_QTY_BD
2404 ,1 EBS_SH_SHIP_QTY_SD
2405 ,1 ACRY_MAPE_PCT_ERR
2406 ,1 PRTY_DEMAND
2407 ,1 WEEK4_ABS_PCT_ERR
2408 ,1 WEEK8_ABS_PCT_ERR
2409 ,1 WEEK13_ABS_PCT_ERR
2410 ,1 DKEY_ITEM
2411 ,1 DKEY_SITE
2412 ,1 ACTUAL_PROD
2413 ,1 TOTAL_BACKLOG
2414 ,1 FCST_CONSENSUS
2415 ,1 BUDGET
2416 ,1 SALES_FCST
2417 ,1 MKTG_FCST
2418 ,1 FCST_BOOKING
2419 ,1 FCST_SHIPMENT
2420 ,1 PROJ_BACKLOG
2421 ,1 RECORD_TYPE
2422 ,1 EBS_RETURN_HISTORY
2423 ,1 FCST_HYP_ANNUAL_PLAN
2424 ,1 FCST_HYP_FINANCIAL
2425 ,1 C_PRED
2426 ,1 ACTUAL_ON_HAND
2427 ,1 EBS_BH_BOOK_QTY_RD
2428 from '||x_dem_schema||'.inputs,
2429 dual';
2430 x_syn_base := x_dem_schema||'.BIEO_OBI_MV';
2431 EXECUTE IMMEDIATE x_create_view_sql;
2432
2433
2434
2435 /*If Demantra version is 7.2.0.2 create dummy columns*/
2436 IF (x_dmtra_version = 7.2 ) THEN
2437 x_create_view_sql := 'CREATE OR REPLACE VIEW MSD_DEM_BIEO_OBI_MV_V AS
2438 SELECT SDATE
2439 ,LEVEL1
2440 ,LEVEL2
2441 ,LEVEL3
2442 ,LEVEL4
2443 ,EBS_BH_BOOK_QTY_BD
2444 ,EBS_SH_SHIP_QTY_SD
2445 ,ACRY_MAPE_PCT_ERR
2446 ,PRTY_DEMAND
2447 ,WEEK4_ABS_PCT_ERR
2448 ,WEEK8_ABS_PCT_ERR
2449 ,WEEK13_ABS_PCT_ERR
2450 ,DKEY_ITEM
2451 ,DKEY_SITE
2452 ,ACTUAL_PROD
2453 ,TOTAL_BACKLOG
2454 ,FCST_CONSENSUS
2455 ,BUDGET
2456 ,SALES_FCST
2457 ,MKTG_FCST
2458 ,FCST_BOOKING
2459 ,FCST_SHIPMENT
2460 ,PROJ_BACKLOG
2461 ,RECORD_TYPE
2462 ,NULL EBS_RETURN_HISTORY
2463 ,NULL FCST_HYP_ANNUAL_PLAN
2464 ,NULL FCST_HYP_FINANCIAL
2465 ,NULL C_PRED
2466 ,NULL ACTUAL_ON_HAND
2467 ,NULL EBS_BH_BOOK_QTY_RD
2468 FROM '||x_dem_schema||'.BIEO_OBI_MV,DUAL';
2469 EXECUTE IMMEDIATE x_create_view_sql;
2470 x_syn_base := 'MSD_DEM_BIEO_OBI_MV_V';
2471 ELSIF (x_dmtra_version >= 7.3) THEN
2472 x_create_view_sql:= 'CREATE OR REPLACE VIEW MSD_DEM_BIEO_OBI_MV_V AS
2473 SELECT SDATE
2474 ,LEVEL1
2475 ,LEVEL2
2476 ,LEVEL3
2477 ,LEVEL4
2478 ,EBS_BH_BOOK_QTY_BD
2479 ,EBS_SH_SHIP_QTY_SD
2480 ,ACRY_MAPE_PCT_ERR
2481 ,PRTY_DEMAND
2482 ,WEEK4_ABS_PCT_ERR
2483 ,WEEK8_ABS_PCT_ERR
2484 ,WEEK13_ABS_PCT_ERR
2485 ,DKEY_ITEM
2486 ,DKEY_SITE
2487 ,ACTUAL_PROD
2488 ,TOTAL_BACKLOG
2489 ,FCST_CONSENSUS
2490 ,BUDGET
2491 ,SALES_FCST
2492 ,MKTG_FCST
2493 ,FCST_BOOKING
2494 ,FCST_SHIPMENT
2495 ,PROJ_BACKLOG
2496 ,RECORD_TYPE
2497 ,EBS_RETURN_HISTORY
2498 ,FCST_HYP_ANNUAL_PLAN
2499 ,FCST_HYP_FINANCIAL
2500 ,C_PRED
2501 ,ACTUAL_ON_HAND
2502 ,EBS_BH_BOOK_QTY_RD
2503 FROM '||x_dem_schema||'.BIEO_OBI_MV,DUAL';
2504 EXECUTE IMMEDIATE x_create_view_sql;
2505 x_syn_base := 'MSD_DEM_BIEO_OBI_MV_V';
2506 END IF;
2507
2508 END IF;
2509 /*schema*/
2510
2511 /* Update synonym MSD_DEM_BIEO_OBI_MV_SYN to point to dummy table MSD_DEM_BIEO_OBI_MV_DUMMY */
2512 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_BIEO_OBI_MV_SYN FOR ' || x_syn_base;
2513 EXECUTE IMMEDIATE x_create_synonym_sql;
2514
2515
2516
2517 EXCEPTION
2518 WHEN others THEN
2519 retcode := -1;
2520 RAISE;
2521 END UPDATE_DEM_APCC_SYNONYM;
2522
2523
2524 /*
2525 * Use this function to determine start/end date of a CTO item
2526 * Dates calculated, to be closer to max sales date in demantra, as follows :
2527 * (If 'max_sales_date' sys_param is used the value will be used, else the max date from sales staging table will be considered)
2528 * Start date - bom_effective_date or (max_sales_date - cto_history_periods) whichever is higher
2529 * End date - bom_inactive_date or (max_sales_date + lead) whichever is lower
2530 *
2531 * params :
2532 * p_bom_date - bom_effective_date or bom_inactive_date
2533 * p_min_max - if 1 (date passed is bom_effective_date) else (date passed is bom_inactive_date)
2534 */
2535 FUNCTION GET_CTO_EFFECTIVE_DATE (
2536 p_bom_date IN DATE,
2537 p_min_max IN NUMBER DEFAULT 1)
2538 RETURN DATE
2539 IS
2540 x_dem_nls_date_format VARCHAR2(100) := NULL;
2541 x_dem_max_sales_date VARCHAR2(100) := NULL;
2542 x_dm_time_level VARCHAR2(10) := NULL;
2543 x_stg_max_sales_date_d DATE := NULL;
2544 x_dem_max_sales_date_d DATE := NULL;
2545 x_max_sales_date_d DATE := NULL;
2546 x_bom_date DATE := NULL;
2547 x_num_periods NUMBER := NULL;
2548 BEGIN
2549
2550 /* get the max_sales_date param value */
2551 IF (C_DEM_MAX_SALES_DATE_D IS NULL)
2552 THEN
2553
2554 EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.DB_PARAMS'
2555 || ' WHERE lower(pname) = ''nls_date_format'' '
2556 INTO x_dem_nls_date_format;
2557
2558 EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.SYS_PARAMS'
2559 || ' WHERE lower(pname) = ''max_sales_date'' '
2560 INTO x_dem_max_sales_date;
2561
2562 IF (x_dem_max_sales_date IS NOT NULL)
2563 THEN
2564 x_dem_max_sales_date_d := to_date(x_dem_max_sales_date, x_dem_nls_date_format);
2565 ELSE
2566 x_dem_max_sales_date_d := to_date('01-01-1900 00:00:00', 'mm-dd-yyyy hh24:mi:ss');
2567 END IF;
2568
2569 C_DEM_MAX_SALES_DATE_D := x_dem_max_sales_date_d;
2570
2571 END IF;
2572
2573 x_dem_max_sales_date_d := C_DEM_MAX_SALES_DATE_D;
2574
2575 /* get the max sales date from t_src_sales_tmpl table */
2576 EXECUTE IMMEDIATE 'SELECT max(sales_date) FROM ' || C_MSD_DEM_SCHEMA || '.T_SRC_SALES_TMPL'
2577 INTO x_stg_max_sales_date_d;
2578
2579 /* get the greater of max_sales_date param and max sales date in sales staging table */
2580 IF (x_stg_max_sales_date_d IS NOT NULL)
2581 THEN
2582 x_max_sales_date_d := greatest(x_stg_max_sales_date_d, x_dem_max_sales_date_d);
2583 ELSE
2584 x_max_sales_date_d := x_dem_max_sales_date_d;
2585 END IF;
2586
2587
2588 IF (p_min_max = 1) /* to determine begin date get cto_history_periods param value */
2589 THEN
2590
2591 IF (C_DEM_HISTORY_PERIODS IS NULL)
2592 THEN
2593 EXECUTE IMMEDIATE 'SELECT pval FROM ' || C_MSD_DEM_SCHEMA || '.SYS_PARAMS'
2594 || ' WHERE lower(pname) = ''cto_history_periods'' '
2595 INTO C_DEM_HISTORY_PERIODS;
2596 END IF;
2597
2598 x_num_periods := -C_DEM_HISTORY_PERIODS;
2599
2600 ELSE /* to determine end date get lead param value */
2601
2602 IF (C_DEM_LEAD IS NULL)
2603 THEN
2604 EXECUTE IMMEDIATE 'SELECT value_float FROM ' || C_MSD_DEM_SCHEMA || '.INIT_PARAMS_0'
2605 || ' WHERE lower(pname) = ''lead'' '
2606 INTO C_DEM_LEAD;
2607 END IF;
2608
2609 x_num_periods := C_DEM_LEAD;
2610
2611 END IF;
2612
2613 /* check the time bucket used in demantra */
2614 x_dm_time_level := lower(msd_dem_common_utilities.dm_time_level);
2615 IF x_dm_time_level = 'day'
2616 THEN
2617 x_bom_date := x_max_sales_date_d + x_num_periods;
2618 ELSIF x_dm_time_level = 'week'
2619 THEN
2620 x_bom_date := x_max_sales_date_d + x_num_periods*7;
2621 ELSE
2622 x_bom_date := ADD_MONTHS(x_max_sales_date_d, x_num_periods);
2623 END IF;
2624
2625 IF (p_bom_date IS NOT NULL)
2626 THEN
2627 IF (p_min_max = 1) /* begin date will be greater of p_bom_date and x_bom_date */
2628 THEN
2629 x_bom_date := greatest(p_bom_date, x_bom_date);
2630 ELSE /* end date will be lower of p_bom_date and x_bom_date */
2631 x_bom_date := least(p_bom_date, x_bom_date);
2632 END IF;
2633 END IF;
2634
2635 /* Convert to the nearest bucket date */
2636 SELECT datet
2637 INTO x_bom_date
2638 FROM msd_dem_dates
2639 WHERE trunc(x_bom_date) BETWEEN start_date AND end_date;
2640
2641 RETURN x_bom_date;
2642
2643 EXCEPTION
2644 WHEN OTHERS THEN
2645 RETURN trunc(sysdate);
2646
2647 END GET_CTO_EFFECTIVE_DATE;
2648
2649 /*
2650 * This function returns the value of the constant CS_DEM_SYSDATE
2651 */
2652 FUNCTION GET_DEM_SYSDATE
2653 RETURN DATE
2654 IS
2655 BEGIN
2656 RETURN MSD_DEM_CTO.CS_DEM_SYSDATE;
2657 END;
2658
2659 /*
2660 * This function returns the value of the constant CS_DEM_CTO_BASE_MODEL
2661 */
2662 FUNCTION GET_DEM_CTO_BASE_MODEL
2663 RETURN VARCHAR2
2664 IS
2665 BEGIN
2666 RETURN MSD_DEM_CTO.CS_DEM_CTO_BASE_MODEL;
2667 END;
2668
2669 /*
2670 * This function returns the value of the constant CS_DEM_CTO_OPTION_CLASS
2671 */
2672 FUNCTION GET_DEM_CTO_OPTION_CLASS
2673 RETURN VARCHAR2
2674 IS
2675 BEGIN
2676 RETURN MSD_DEM_CTO.CS_DEM_CTO_OPTION_CLASS;
2677 END;
2678
2679 /*
2680 * This function returns the value of the constant CS_DEM_CTO_OPTION
2681 */
2682 FUNCTION GET_DEM_CTO_OPTION
2683 RETURN VARCHAR2
2684 IS
2685 BEGIN
2686 RETURN MSD_DEM_CTO.CS_DEM_CTO_OPTION;
2687 END;
2688
2689 /*
2690 * This function returns the value of the constant CS_DEM_SPF_BASE_MODEL
2691 */
2692 FUNCTION GET_DEM_SPF_BASE_MODEL
2693 RETURN VARCHAR2
2694 IS
2695 BEGIN
2696 RETURN MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_BASE_MODEL;
2697 END;
2698
2699 /*
2700 * This function returns the value of the constant CS_DEM_SPF_OPTION_CLASS
2701 */
2702 FUNCTION GET_DEM_SPF_OPTION_CLASS
2703 RETURN VARCHAR2
2704 IS
2705 BEGIN
2706 RETURN MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_OPTION_CLASS;
2707 END;
2708
2709 /*
2710 * This function returns the value of the constant CS_DEM_SPF_OPTION
2711 */
2712 FUNCTION GET_DEM_SPF_OPTION
2713 RETURN VARCHAR2
2714 IS
2715 BEGIN
2716 RETURN MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_SPF_OPTION;
2717 END;
2718
2719 /*
2720 * This function returns the value of the constant CS_DEM_LEVEL_ITEM_LABEL
2721 */
2722 FUNCTION GET_ITEM_LABEL
2723 RETURN VARCHAR2
2724 IS
2725 BEGIN
2726 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_ITEM_LABEL;
2727 END;
2728
2729 /*
2730 * This function returns the value of the constant CS_DEM_LEVEL_ORG_LABEL
2731 */
2732 FUNCTION GET_ORG_LABEL
2733 RETURN VARCHAR2
2734 IS
2735 BEGIN
2736 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_ORG_LABEL;
2737 END;
2738
2739 /*
2740 * This function returns the value of the constant CS_DEM_LEVEL_SITE_LABEL
2741 */
2742 FUNCTION GET_SITE_LABEL
2743 RETURN VARCHAR2
2744 IS
2745 BEGIN
2746 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_SITE_LABEL;
2747 END;
2748
2749 /*
2750 * This function returns the value of the constant CS_DEM_LEVEL_DC_LABEL
2751 */
2752 FUNCTION GET_DC_LABEL
2753 RETURN VARCHAR2
2754 IS
2755 BEGIN
2756 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_DC_LABEL;
2757 END;
2758
2759 /*
2760 * This function returns the value of the constant CS_DEM_LEVEL_SC_LABEL
2761 */
2762 FUNCTION GET_SC_LABEL
2763 RETURN VARCHAR2
2764 IS
2765 BEGIN
2766 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_SC_LABEL;
2767 END;
2768
2769 /*
2770 * This function returns the value of the constant CS_DEM_LEVEL_ASSET_GROUP_LABEL
2771 */
2772 FUNCTION GET_ASSET_GROUP_LABEL
2773 RETURN VARCHAR2
2774 IS
2775 BEGIN
2776 RETURN MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_ASSET_GROUP_LABEL;
2777 END;
2778
2779 /*
2780 * This function returns the value of the constant CS_DEM_LEVEL_CLASS_CODE_LABEL
2781 */
2782 FUNCTION GET_CLASS_CODE_LABEL
2783 RETURN VARCHAR2
2784 IS
2785 BEGIN
2786 RETURN MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_CLASS_CODE_LABEL;
2787 END;
2788
2789 /*
2790 * This function returns the value of the constant CS_DEM_WORKORDER_ITEM
2791 */
2792 FUNCTION GET_WORKORDER_ITEM
2793 RETURN VARCHAR2
2794 IS
2795 BEGIN
2796 RETURN MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_WORKORDER_ITEM;
2797 END;
2798
2799 /*
2800 * This function returns the value of the constant CS_DEM_LEVEL_ITEM_ID
2801 */
2802 FUNCTION GET_ITEM_ID
2803 RETURN NUMBER
2804 IS
2805 BEGIN
2806 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_ITEM_ID;
2807 END;
2808
2809 /*
2810 * This function returns the value of the constant CS_DEM_LEVEL_ORG_ID
2811 */
2812 FUNCTION GET_ORG_ID
2813 RETURN NUMBER
2814 IS
2815 BEGIN
2816 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_ORG_ID;
2817 END;
2818
2819 /*
2820 * This function returns the value of the constant CS_DEM_LEVEL_SITE_ID
2821 */
2822 FUNCTION GET_SITE_ID
2823 RETURN NUMBER
2824 IS
2825 BEGIN
2826 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_SITE_ID;
2827 END;
2828
2829 /*
2830 * This function returns the value of the constant CS_DEM_LEVEL_DC_ID
2831 */
2832 FUNCTION GET_DC_ID
2833 RETURN NUMBER
2834 IS
2835 BEGIN
2836 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_DC_ID;
2837 END;
2838
2839 /*
2840 * This function returns the value of the constant CS_DEM_LEVEL_SC_ID
2841 */
2842 FUNCTION GET_SC_ID
2843 RETURN NUMBER
2844 IS
2845 BEGIN
2846 RETURN MSD_DEM_CTO.CS_DEM_LEVEL_SC_ID;
2847 END;
2848
2849 /*
2850 * This function returns the value of the constant CS_DEM_LEVEL_ASSET_GROUP_ID
2851 */
2852 FUNCTION GET_ASSET_GROUP_ID
2853 RETURN NUMBER
2854 IS
2855 BEGIN
2856 RETURN MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_ASSET_GROUP_ID;
2857 END;
2858
2859 /*
2860 * This function returns the value of the constant CS_DEM_LEVEL_CLASS_CODE_ID
2861 */
2862 FUNCTION GET_CLASS_CODE_ID
2863 RETURN NUMBER
2864 IS
2865 BEGIN
2866 RETURN MSD_SPF_COLLECT_HISTORY_DATA.CS_DEM_LEVEL_CLASS_CODE_ID;
2867 END;
2868
2869 /*
2870 * This function given a source instance, will get the source category set id for the category
2871 * set specified in the profile MSC_SERVICE_ITEMS_CATSET
2872 */
2873 FUNCTION GET_SPF_SR_CAT_SET_ID (
2874 p_sr_instance_id IN NUMBER)
2875 RETURN VARCHAR2
2876 IS
2877
2878 var_cat_set_id NUMBER DEFAULT to_number(MSC_UTIL.GET_SERVICE_ITEMS_CATSET_ID);
2879 var_sr_cat_set_id NUMBER DEFAULT NULL;
2880
2881 BEGIN
2882
2883 IF (var_cat_set_id IS NULL)
2884 THEN
2885 RETURN NULL;
2886 END IF;
2887
2888 SELECT mil.sr_category_set_id
2889 INTO var_sr_cat_set_id
2890 FROM msc_category_set_id_lid mil
2891 WHERE mil.category_set_id = var_cat_set_id
2892 AND mil.sr_instance_id = p_sr_instance_id;
2893
2894 RETURN to_char(var_sr_cat_set_id);
2895
2896 EXCEPTION
2897 WHEN OTHERS THEN
2898 RETURN NULL;
2899
2900 END GET_SPF_SR_CAT_SET_ID;
2901
2902 /* Procedure to launch (remote) request on an instance from a different instance.
2903 * To be called over dblink, with parameter string (all parameters to be passed to fnd_request.submit_request including product name, program name etc)
2904 * Will initialize apps session if required, and launch the request
2905 */
2906 PROCEDURE LAUNCH_REMOTE_REQUEST (
2907 errbuf OUT NOCOPY VARCHAR2,
2908 retcode OUT NOCOPY VARCHAR2,
2909 p_user_name IN VARCHAR2,
2910 p_resp_name IN VARCHAR2,
2911 p_appl_name IN VARCHAR2,
2912 p_params_string IN VARCHAR2,
2913 p_request_id OUT NOCOPY NUMBER
2914 ) is
2915 x_sql varchar2(500) := null;
2916 x_user_id number := null;
2917 x_resp_id number := null;
2918 x_appl_id number := null;
2919
2920 BEGIN
2921 IF (fnd_global.user_id = -1) THEN
2922 BEGIN
2923 select user_id into x_user_id from fnd_user where user_name = p_user_name;
2924 select responsibility_id into x_resp_id from fnd_responsibility_vl where responsibility_name = p_resp_name;
2925 select application_id into x_appl_id from fnd_application_vl where application_name = p_appl_name;
2926
2927 apps_initialize(x_user_id, x_resp_id, x_appl_id);
2928 EXCEPTION
2929 WHEN OTHERS THEN
2930 errbuf := 'Error when initializing global security context for db session - ' || substr(sqlerrm,1,200);
2931 retcode := -1;
2932 return;
2933 END;
2934 END IF;
2935
2936 x_sql := 'begin ' ||
2937 ':1 := fnd_request.submit_request(' || p_params_string || ' );' ||
2938 'end;';
2939 execute immediate x_sql using out p_request_id;
2940 EXCEPTION
2941 WHEN OTHERS THEN
2942 errbuf := 'Error when launching concurrent request - ' || substr(sqlerrm,1,200);
2943 retcode := -1;
2944 END LAUNCH_REMOTE_REQUEST;
2945
2946
2947
2948 /*
2949 * This procedure will launch demantra workflow when provided the workflow lookup code.
2950 *
2951 * ------------ PARAMETERS LIST ----------------
2952 * p_workflow_lookup_code : Workflow lookup code
2953 * p_synchronous : Whether CP to in sync with workflow or not
2954 * p_check_interval : Check the status of the workflow for every (in seconds)
2955 * p_time_out : Stop the status check of the workflow after (in minutes)
2956 * --------------------------------------------
2957 *
2958 */
2959
2960 PROCEDURE Launch_Dem_Workflow (
2961 errbuf OUT NOCOPY VARCHAR2,
2962 retcode OUT NOCOPY VARCHAR2,
2963 p_workflow_lookup_code IN VARCHAR2,
2964 p_synchronous IN NUMBER DEFAULT C_NO,
2965 p_check_interval IN NUMBER DEFAULT 60,
2966 p_time_out IN NUMBER DEFAULT 1440 )
2967 IS
2968
2969 l_sql varchar2(1000);
2970 dem_schema varchar2(100);
2971 l_url varchar2(1000);
2972 l_dummy varchar2(100);
2973 l_user_id number;
2974 l_user_name varchar2(30);
2975 l_password varchar2(80);
2976 l_schema_name varchar2(255);
2977 l_schema_id number;
2978 ret_process_id varchar2(100) := null;
2979 workflow_name varchar2(100) := null;
2980 workflow_status number := 0;
2981 x_check_cnt number := 0;
2982 x_time_out_cnt number := 0;
2983
2984
2985 BEGIN
2986
2987 msd_dem_common_utilities.log_debug ('Entering: msd_dem_common_utilities.Launch_Dem_Workflow - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2988
2989 /* Log the input parameters to the log file */
2990 msd_dem_common_utilities.log_message('----------------------------------Input Parameters - Begin------------------------------------------');
2991
2992 msd_dem_common_utilities.log_message(' ' || rpad('Workflow Lookup Code', 30) || ' - ' || to_char(nvl(p_workflow_lookup_code,'No Value')));
2993
2994 msd_dem_common_utilities.log_message('-----------------------------------Input Parameters - End-------------------------------------------');
2995 msd_dem_common_utilities.log_message ('');
2996 msd_dem_common_utilities.log_message ('');
2997
2998 /* Log the optional input parameters to the output file */
2999 msd_dem_common_utilities.log_debug('----------------------------------Input Parameters (optional) - Begin------------------------------------------');
3000
3001 msd_dem_common_utilities.log_debug(' ' || rpad('Synchronous', 30) || ' - ' || to_char(p_synchronous));
3002 msd_dem_common_utilities.log_debug(' ' || rpad('Check Interval(sec)', 30) || ' - ' || to_char(p_check_interval));
3003 msd_dem_common_utilities.log_debug(' ' || rpad('Time Out(min)', 30) || ' - ' || p_time_out);
3004
3005 msd_dem_common_utilities.log_debug('-----------------------------------Input Parameters (optional) - End-------------------------------------------');
3006 msd_dem_common_utilities.log_debug ('');
3007 msd_dem_common_utilities.log_debug ('');
3008
3009 /* START - Parameters Validation */
3010 IF ( p_workflow_lookup_code IS NULL )
3011 THEN
3012 retcode := -1;
3013 errbuf := '''Workflow Lookup Code'' cannot be NULL.';
3014 msd_dem_common_utilities.log_message ('Error(1): msd_dem_common_utilities.Launch_Dem_Workflow - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3015 msd_dem_common_utilities.log_message (errbuf);
3016 RETURN;
3017 END IF;
3018 /* END - Parameters Validation */
3019
3020 dem_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3021
3022 IF fnd_profile.value('MSD_DEM_SCHEMA') IS NOT NULL
3023 THEN
3024
3025 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3026 'COMP_DM',
3027 1,
3028 'user_id'));
3029 IF l_user_id is not null
3030 THEN
3031 l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
3032 EXECUTE IMMEDIATE l_sql INTO l_user_name, l_password;
3033
3034 ELSE
3035
3036 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3037 'COMP_SOP',
3038 1,
3039 'user_id'));
3040
3041 IF l_user_id IS NOT NULL
3042 THEN
3043 l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
3044 EXECUTE IMMEDIATE l_sql INTO l_user_name, l_password;
3045 ELSE
3046 msd_dem_common_utilities.log_message('Component is not found.');
3047 END IF;
3048 END IF;
3049
3050
3051 IF l_user_name IS NOT NULL
3052 THEN
3053 l_url := fnd_profile.value('MSD_DEM_HOST_URL');
3054 l_schema_name := trim(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID', p_workflow_lookup_code, 1, 'schema_name'));
3055 l_sql := null;
3056 l_sql := 'SELECT utl_http.request('''||l_url||'/WorkflowServer?action=run_proc&user='||l_user_name||'&password='||l_password||'&schema='|| replace(l_schema_name, ' ', '%20') ||'&sync=no'') FROM dual';
3057
3058 msd_dem_common_utilities.log_message('Launching Demantra workflow - ' || l_schema_name);
3059 msd_dem_common_utilities.log_debug ( ' ');
3060 msd_dem_common_utilities.log_debug('Workflow Calling sql - ');
3061 msd_dem_common_utilities.log_debug(l_sql);
3062 EXECUTE IMMEDIATE l_sql INTO ret_process_id;
3063
3064 IF (to_number(ret_process_id) < 0)
3065 THEN
3066
3067 retcode := -1;
3068 errbuf := ret_process_id;
3069 msd_dem_common_utilities.log_message ('ERROR(2): msd_dem_common_utilities.Launch_Dem_Workflow - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3070 msd_dem_common_utilities.log_message ('Failed to launch download workflow');
3071 msd_dem_common_utilities.log_message (errbuf);
3072 RETURN;
3073
3074 ELSE
3075
3076 msd_dem_common_utilities.log_message('Launched workflow successfully.');
3077 msd_dem_common_utilities.log_message('Workflow Process Id - ' || to_number(ret_process_id));
3078 msd_dem_common_utilities.log_message('Please check the workflow process status from workflow manager.');
3079
3080 END IF;
3081
3082 /* START - Checking the status of the workflow */
3083
3084 IF (p_synchronous = C_YES) THEN
3085 /* converting into seconds */
3086 x_time_out_cnt := p_time_out * 60;
3087
3088 WHILE x_check_cnt <= x_time_out_cnt LOOP
3089
3090 l_sql := ' select wfpl.status, wfs.schema_name from '
3091 || dem_schema || '.wf_schemas wfs, '
3092 || dem_schema || '.wf_process_log wfpl '
3093 || ' where wfpl.process_id = ' || ret_process_id
3094 || ' and wfpl.schema_id = wfs.schema_id ' ;
3095 EXECUTE IMMEDIATE l_sql INTO workflow_status,workflow_name ;
3096
3097 IF (workflow_status = 1) THEN
3098 DBMS_LOCK.SLEEP( p_check_interval );
3099 ELSIF (workflow_status = 0) then
3100 msd_dem_common_utilities.log_message( workflow_name || ' Workflow completed successfully.');
3101 retcode := 0;
3102 EXIT;
3103 ELSIF (workflow_status = -1) then
3104 msd_dem_common_utilities.log_message( workflow_name || ' Workflow Failed.');
3105 retcode := -1;
3106 EXIT;
3107 ELSE
3108 msd_dem_common_utilities.log_message( workflow_name || ' Workflow Status not defined. ');
3109 retcode := 1;
3110 EXIT;
3111 END IF;
3112
3113 x_check_cnt := x_check_cnt + p_check_interval ;
3114
3115 If (x_check_cnt >= x_time_out_cnt) then
3116 msd_dem_common_utilities.log_message( workflow_name || ' Workflow timed out. Check status of the workflow');
3117 retcode := 1;
3118 End if;
3119
3120 END LOOP;
3121
3122 END IF;
3123 /* END - Checking the status of the workflow */
3124
3125 ELSE
3126 msd_dem_common_utilities.log_message('Error in launching demantra workflow.');
3127 retcode := -1;
3128 Return;
3129 END IF;
3130
3131 ELSE
3132 msd_dem_common_utilities.log_message('Demantra Schema not set');
3133 END IF;
3134
3135 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_common_utilities.Launch_Dem_Workflow - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3136
3137 EXCEPTION
3138 WHEN OTHERS THEN
3139 errbuf := substr(SQLERRM,1,150);
3140 retcode := -1 ;
3141
3142 msd_dem_common_utilities.log_message ('Exception: msd_dem_common_utilities.Launch_Dem_Workflow - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3143 msd_dem_common_utilities.log_message (errbuf);
3144 RETURN;
3145
3146 END Launch_Dem_Workflow;
3147
3148
3149
3150 /*
3151 *
3152 * This procedure is used to check/validate the values given
3153 * for parameters in UI while submitting concurrent programs.
3154 * After successful validation the procedures returns 'from_date' and 'to_date'
3155 *
3156 * ------------ PARAMETERS LIST ----------------
3157 * p_sr_instance_id : Instance Id
3158 * p_collection_group : Organization Group
3159 * p_collection_method : Complete or Net Change
3160 * p_date_range_type : Absolute or Rolling
3161 * p_collection_window : Noof Days in the past for collecting data
3162 * p_future_window : Noof Days in the future for collecting data
3163 * p_from_date : Start date for collecting data
3164 * p_to_date : End date for collecting data
3165 * p_collect_product : Name of the product for which the collection is done
3166 * (EAM/CMRO/NMP/FLEET)
3167 * ---------------------------------------------
3168 */
3169
3170 PROCEDURE UI_params_validation (
3171 errbuf OUT NOCOPY VARCHAR2,
3172 retcode OUT NOCOPY VARCHAR2,
3173 o_from_date OUT NOCOPY DATE,
3174 o_to_date OUT NOCOPY DATE,
3175 p_sr_instance_id IN NUMBER,
3176 p_collection_group IN VARCHAR2,
3177 p_collection_method IN NUMBER,
3178 p_hidden_param1 IN VARCHAR2,
3179 p_date_range_type IN NUMBER,
3180 p_collection_window IN NUMBER,
3181 p_from_date IN VARCHAR2,
3182 p_to_date IN VARCHAR2,
3183 p_collect_product IN VARCHAR2 Default NULL,
3184 p_future_window IN NUMBER Default NULL)
3185 IS
3186
3187 /*** LOCAL VARIABLES ****/
3188
3189 x_errbuf VARCHAR2(200) := NULL;
3190 x_retcode VARCHAR2(100) := NULL;
3191
3192 x_from_date DATE := NULL;
3193 x_to_date DATE := NULL;
3194
3195
3196 BEGIN
3197
3198 msd_dem_common_utilities.log_debug ('Entering: msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3199 msd_dem_common_utilities.log_debug (' ');
3200
3201 /* VALIDATION OF INPUT PARAMETERS - BEGIN */
3202
3203 msd_dem_common_utilities.log_debug ('Begin validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3204
3205 /* Show Warning if collection method is 'Refresh/Complete' and a date range filter is specified */
3206 IF ( p_collection_method = 1
3207 AND ( p_from_date IS NOT NULL
3208 OR p_to_date IS NOT NULL
3209 OR p_collection_window IS NOT NULL
3210 OR p_future_window is not null))
3211 THEN
3212 x_retcode := 1;
3213 x_errbuf := 'Date Range filters are ignored in ''Refresh/Complete'' collections';
3214 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3215 msd_dem_common_utilities.log_message (x_errbuf);
3216 END IF;
3217
3218
3219 /* Show Warning if collection method is 'Net Change', date_range_type is 'Rolling' and from_date and to_date are specified */
3220 IF ( p_collection_method = 2
3221 AND p_date_range_type = 2
3222 AND ( p_from_date IS NOT NULL
3223 OR p_to_date IS NOT NULL))
3224 THEN
3225 x_retcode := 1;
3226 x_errbuf := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date_range_type is selected.';
3227 msd_dem_common_utilities.log_message ('Warning(2): msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3228 msd_dem_common_utilities.log_message (x_errbuf);
3229 END IF;
3230
3231
3232 /* Show Warning if collection method is 'Net Change', date_range_type is 'Absolute' and history collection window is specified */
3233 IF ( p_collection_method = 2
3234 AND p_date_range_type = 1
3235 AND (p_collection_window IS NOT NULL or p_future_window is not null))
3236 THEN
3237 x_retcode := 1;
3238 x_errbuf := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
3239 msd_dem_common_utilities.log_message ('Warning(3): msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3240 msd_dem_common_utilities.log_message (x_errbuf);
3241 END IF;
3242
3243
3244 /* Error if collection method is 'Net Change', date_range_type is 'Rolling' and collection window is not specified */
3245 IF ( p_collection_method = 2
3246 AND p_date_range_type = 2
3247 AND p_collection_window IS NULL and p_future_window is null)
3248 THEN
3249 retcode := -1;
3250 errbuf := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
3251 msd_dem_common_utilities.log_message ('Error(1): msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3252 msd_dem_common_utilities.log_message (errbuf);
3253 RETURN;
3254 END IF;
3255
3256 /* Error if collection method is 'Net Change', date_range_type is 'Absolute' and from_date and to_date are not specified */
3257 IF ( p_collection_method = 2
3258 AND p_date_range_type = 1
3259 AND ( p_from_date IS NULL
3260 OR p_to_date IS NULL))
3261 THEN
3262 retcode := -1;
3263 errbuf := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
3264 msd_dem_common_utilities.log_message ('Error(2): msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3265 msd_dem_common_utilities.log_message (errbuf);
3266 RETURN;
3267 END IF;
3268
3269 /* VALIDATION OF INPUT PARAMETERS - END */
3270
3271
3272 /* Get the start date and end dates for collection */
3273
3274 msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3275
3276 IF (p_collection_method = 1) /* Refresh/Complete */
3277 THEN
3278 x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
3279 x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
3280 ELSE /* Net Change */
3281 IF (p_date_range_type = 1) /* Absolute*/
3282 THEN
3283
3284 IF (p_from_date IS NULL)
3285 THEN
3286 x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
3287 ELSE
3288 x_from_date := fnd_date.canonical_to_date (p_from_date);
3289 END IF;
3290
3291 IF (p_to_date IS NULL)
3292 THEN
3293 x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
3294 ELSE
3295 x_to_date := fnd_date.canonical_to_date (p_to_date);
3296 END IF;
3297
3298
3299 /* Error if p_from_date is greater than p_to_date */
3300 IF (x_from_date > x_to_date)
3301 THEN
3302 retcode := -1;
3303 errbuf := 'From Date should not be greater than To Date.';
3304 msd_dem_common_utilities.log_message ('Error(3): msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3305 msd_dem_common_utilities.log_message (errbuf);
3306 RETURN;
3307 END IF;
3308
3309 ELSE /* Rolling */
3310
3311 IF (p_collection_window <= 0 or p_future_window <= 0)
3312 THEN
3313 retcode := -1;
3314 errbuf := 'Collection Window must be a positive number.';
3315 msd_dem_common_utilities.log_message ('Error(4): msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3316 msd_dem_common_utilities.log_message (errbuf);
3317 RETURN;
3318 ELSE
3319 x_from_date := trunc(sysdate, 'DD') - nvl(p_collection_window,0) + 1;
3320 x_to_date := trunc(sysdate, 'DD') + nvl(p_future_window,0);
3321 END IF;
3322 END IF;
3323 END IF;
3324
3325 o_from_date := x_from_date;
3326 o_to_date := x_to_date;
3327
3328 msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3329
3330 msd_dem_common_utilities.log_debug (' ');
3331
3332 msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(o_from_date, 'DD/MM/RRRR'));
3333 msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(o_to_date, 'DD/MM/RRRR'));
3334
3335 msd_dem_common_utilities.log_message (' ');
3336
3337 msd_dem_common_utilities.log_debug ('Existing: msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3338
3339 EXCEPTION
3340 WHEN OTHERS THEN
3341 errbuf := substr(SQLERRM,1,150);
3342 retcode := -1 ;
3343 msd_dem_common_utilities.log_message ('Exception: msd_dem_common_utilities.UI_params_validation - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3344 msd_dem_common_utilities.log_message (errbuf);
3345
3346 END UI_params_validation;
3347
3348
3349 /*
3350 * This procedure will update parameter's value in demantra
3351 * Can be used only for updating paramters in sys_params table
3352 *
3353 * ------------ PARAMETERS LIST ----------------
3354 * p_start_param : parameter name of start_date
3355 * p_start_bucket : offset value for start_date
3356 * p_end_param : parameter name of end_date
3357 * p_end_bucket : offset value for end_date
3358 * p_pivot_param : base date parameter name which is used for
3359 * setting the above start & end params.
3360 * Above offsets are applied against this param.
3361 * p_param_name : parameter name to be updated
3362 * p_param_value : parameter value to be loaded
3363 *
3364 */
3365 PROCEDURE update_dem_params(
3366 p_start_param IN VARCHAR2 DEFAULT NULL,
3367 p_start_bucket IN VARCHAR2 DEFAULT NULL,
3368 p_end_param IN VARCHAR2 DEFAULT NULL,
3369 p_end_bucket IN VARCHAR2 DEFAULT NULL,
3370 p_pivot_param IN VARCHAR2 DEFAULT NULL,
3371 p_param_name IN VARCHAR2 DEFAULT NULL,
3372 p_param_value IN VARCHAR2 DEFAULT NULL )
3373 IS
3374 /*--- local variables ---*/
3375 x_sql VARCHAR2(500) := NULL;
3376 x_dem_schema VARCHAR2(100) := fnd_profile.value('MSD_DEM_SCHEMA');
3377 x_is_exists NUMBER(1);
3378
3379 vd_synchrangestart DATE;
3380 vd_synchrangeend DATE;
3381 vd_pivot_date DATE;
3382
3383 vs_time_buck VARCHAR2(100);
3384 vs_pivot_date VARCHAR2(100);
3385 vs_date_format VARCHAR2(100);
3386
3387 BEGIN
3388
3389 /*-- START : basic check conditions --*/
3390 IF (x_dem_schema IS NULL) THEN
3391 raise_application_error (-20001, 'Error: msd_dem_common_utilities.update_dem_params - Unable to find schema name.');
3392 END IF;
3393
3394
3395 IF (p_start_param IS NOT NULL AND p_start_bucket IS NULL) THEN
3396 raise_application_error (-20002, 'Error: msd_dem_common_utilities.update_dem_params - Missing value for BucketStart adjustment.');
3397
3398 ELSIF (p_start_param IS NOT NULL AND p_start_bucket IS NOT NULL) THEN
3399 BEGIN
3400 x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_start_param) || '''' ;
3401 EXECUTE IMMEDIATE x_sql INTO x_is_exists ;
3402 EXCEPTION
3403 WHEN OTHERS THEN
3404 raise_application_error (-20003, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_start_param ||' Parameter doesnot exist.');
3405 END;
3406
3407 IF (p_pivot_param IS NULL) THEN
3408 raise_application_error (-20004, 'Error: msd_dem_common_utilities.update_dem_params - Missing BASE parameter name.');
3409 ELSE
3410 BEGIN
3411
3412 x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_pivot_param) || '''' ;
3413 EXECUTE IMMEDIATE x_sql INTO x_is_exists ;
3414 EXCEPTION
3415 WHEN OTHERS THEN
3416 raise_application_error (-20005, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_pivot_param ||' Parameter doesnot exist.');
3417 END;
3418 END IF;
3419
3420 END IF;
3421
3422 IF (p_start_param IS NULL AND p_start_bucket IS NOT NULL) THEN
3423 raise_application_error (-20006, 'Error: msd_dem_common_utilities.update_dem_params - Missing start parameter name.');
3424
3425 END IF;
3426
3427
3428 IF (p_end_param IS NOT NULL AND p_end_bucket IS NULL) THEN
3429 raise_application_error (-20007, 'Error: msd_dem_common_utilities.update_dem_params - Missing value for BucketEnd adjustment.');
3430 ELSIF (p_end_param IS NOT NULL AND p_end_bucket IS NOT NULL) THEN
3431 BEGIN
3432 --x_is_exists := NULL;
3433 x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_end_param) || '''' ;
3434 EXECUTE IMMEDIATE x_sql INTO x_is_exists ;
3435 EXCEPTION
3436 WHEN OTHERS THEN
3437 RAISE_APPLICATION_ERROR (-20008, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_end_param ||' Parameter doesnot exist.');
3438 END;
3439
3440 IF (p_pivot_param IS NULL) THEN
3441 raise_application_error (-20009, 'Error: msd_dem_common_utilities.update_dem_params - Missing BASE parameter name.');
3442 ELSE
3443 BEGIN
3444 x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_pivot_param) || '''' ;
3445 EXECUTE IMMEDIATE x_sql INTO x_is_exists ;
3446 EXCEPTION
3447 WHEN OTHERS THEN
3448 raise_application_error (-200010, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_pivot_param ||' Parameter doesnot exist.');
3449 END;
3450 END IF;
3451
3452 END IF;
3453
3454 IF (p_end_param IS NULL AND p_end_bucket IS NOT NULL) THEN
3455 raise_application_error (-20011, 'Error: msd_dem_common_utilities.update_dem_params - Missing END parameter name.');
3456
3457 END IF;
3458
3459
3460
3461 IF (p_param_name IS NOT NULL AND p_param_value IS NULL) THEN
3462 raise_application_error (-20012, 'Error: msd_dem_common_utilities.update_dem_params - Missing parameter value.');
3463 ELSIF (p_param_name IS NOT NULL AND p_param_value IS NOT NULL) THEN
3464 begin
3465 x_is_exists := NULL;
3466 x_sql := 'SELECT 1 FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname)=''' || LOWER(p_param_name) || '''' ;
3467 EXECUTE IMMEDIATE x_sql INTO x_is_exists ;
3468 EXCEPTION
3469 WHEN OTHERS THEN
3470 RAISE_APPLICATION_ERROR (-20013, 'Error: msd_dem_common_utilities.update_dem_params - ' || p_param_name ||' Parameter doesnot exist.');
3471 END;
3472 END IF;
3473
3474 IF (p_param_name IS NULL AND p_param_value IS NOT NULL) THEN
3475 raise_application_error (-20014, 'Error: msd_dem_common_utilities.update_dem_params - Missing parameter name.');
3476 END IF;
3477
3478 /*-- END : basic check conditions --*/
3479
3480
3481 IF (p_start_param IS NOT NULL AND p_start_bucket IS NOT NULL) THEN
3482
3483 x_sql := 'SELECT pval FROM ' || x_dem_schema || '.db_params WHERE pname = ''nls_date_format''' ;
3484 EXECUTE IMMEDIATE x_sql INTO vs_date_format ;
3485
3486 x_sql := 'SELECT pval FROM ' || x_dem_schema || '.sys_params WHERE LOWER(pname) = ''' || LOWER(p_pivot_param) || '''' ;
3487 EXECUTE IMMEDIATE x_sql INTO vs_pivot_date ;
3488 vd_pivot_date := to_date(vs_pivot_date,vs_date_format);
3489
3490 x_sql := 'SELECT pval FROM ' || x_dem_schema || '.sys_params WHERE pname = ''Timeresolution''' ;
3491 EXECUTE IMMEDIATE x_sql INTO vs_time_buck ;
3492
3493 IF lower(vs_time_buck)='day' THEN vd_synchrangestart := vd_pivot_date + (1 * to_number(p_start_bucket)) ; END IF;
3494 IF lower(vs_time_buck)='week' THEN vd_synchrangestart := vd_pivot_date + (7 * to_number(p_start_bucket)) ; END IF;
3495 IF lower(vs_time_buck)='month' THEN vd_synchrangestart := ADD_MONTHS(vd_pivot_date, to_number(p_start_bucket)) ; END IF;
3496
3497 x_sql := ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || to_char(vd_synchrangestart,'MM/DD/YYYY HH24:MI:SS') || '''' ||
3498 ' WHERE LOWER(pname) = '''|| LOWER(p_start_param) || '''' ;
3499 EXECUTE IMMEDIATE x_sql ;
3500 commit;
3501
3502 END IF;
3503
3504 IF (p_end_param IS NOT NULL AND p_end_bucket IS NOT NULL) THEN
3505
3506 IF lower(vs_time_buck)='day' THEN vd_synchrangeend := vd_pivot_date + (1 * to_number(p_end_bucket)) ; END IF;
3507 IF lower(vs_time_buck)='week' THEN vd_synchrangeend := vd_pivot_date + (7 * to_number(p_end_bucket)) ; END IF;
3508 IF lower(vs_time_buck)='month' THEN vd_synchrangeend := ADD_MONTHS(vd_pivot_date, to_number(p_end_bucket)) ; END IF;
3509
3510 x_sql := ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || to_char(vd_synchrangeend,'MM/DD/YYYY HH24:MI:SS') || '''' ||
3511 ' WHERE LOWER(pname) = ''' || LOWER(p_end_param) || '''' ;
3512 EXECUTE IMMEDIATE x_sql ;
3513 COMMIT;
3514
3515 END IF;
3516
3517 IF (p_param_name IS NOT NULL AND p_param_value IS NOT NULL) THEN
3518 x_sql := ' UPDATE ' || x_dem_schema || '.sys_params set pval = ''' || p_param_value || '''' ||
3519 ' WHERE LOWER(pname) = ''' || LOWER(p_param_name) || '''' ;
3520 EXECUTE IMMEDIATE x_sql ;
3521 COMMIT;
3522
3523 END IF ;
3524
3525 EXCEPTION
3526 WHEN OTHERS THEN
3527 RAISE_APPLICATION_ERROR (-20015, 'Error: msd_dem_common_utilities.update_dem_params - '|| substr(sqlerrm,1,150));
3528
3529 END update_dem_params;
3530
3531 /*
3532 * This procedure updates the series load & purge option for the given data profile.
3533 * p_schema : demantra schema name
3534 * p_dataprofile : Data profile lookup value
3535 * p_series : Series lookup value
3536 * p_load : Load option ( 0 - OVERRIDE / 1 - ACCUMULATE / 2 - No Load )
3537 * p_purge: purge option (0 - No Purge / 1 - Purge All dates without new data / 2 - Purge All dates without new data, within DP time range )
3538 * p_notify : notify application server ( 1 - Yes / 2 - No )
3539 */
3540
3541 PROCEDURE SET_SERIES_OPTIONS(
3542 errbuf OUT NOCOPY VARCHAR2,
3543 retcode OUT NOCOPY VARCHAR2,
3544 p_schema IN VARCHAR2,
3545 p_dataprofile IN VARCHAR2,
3546 p_series IN VARCHAR2,
3547 p_load IN NUMBER,
3548 p_purge IN NUMBER,
3549 p_notify IN NUMBER DEFAULT 2)
3550
3551 IS
3552 x_profile_id number;
3553 x_sql varchar2(500) := null;
3554 x_query_name varchar2(200) := null;
3555 x_srs_id number;
3556 x_errbuf1 varchar2(200) := null;
3557 x_retcode1 varchar2(100) := null;
3558
3559
3560 BEGIN
3561 msd_dem_common_utilities.log_debug ('Entering: MSD_DEM_COMMON_UTILITIES.SET_SERIES_OPTIONS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3562
3563 x_profile_id := to_number(msd_dem_common_utilities.get_app_id_text (
3564 'MSD_DEM_DEMANTRA_OBJECT_ID',
3565 p_dataprofile,
3566 1,
3567 'id'));
3568
3569 /*
3570 x_sql := 'select query_name from '|| p_schema || '.transfer_query where id = ' || x_profile_id;
3571 msd_dem_common_utilities.log_debug(x_sql);
3572 execute immediate x_sql into x_query_name ;
3573 msd_dem_common_utilities.log_debug('Refreshing profile - ' || x_query_name);
3574 msd_dem_common_utilities.REFRESH_PURGE_SERIES(x_errbuf1, x_retcode1, x_profile_id, p_schema);
3575
3576 IF (x_retcode1 = -1) THEN
3577 retcode := 1;
3578 errbuf := 'Error refreshing profile - ' || x_query_name ||'. ';
3579 msd_dem_common_utilities.log_debug('Error Refreshing profile -' || x_query_name || ' : ' || x_errbuf1);
3580 END IF;
3581 */
3582
3583 --set series's Load option & Purge option
3584 msd_dem_common_utilities.log_debug('Set purge for install base');
3585 x_srs_id := to_number(msd_dem_common_utilities.get_app_id_text (
3586 'MSD_DEM_DEMANTRA_OBJECT_ID',
3587 p_series,
3588 1,
3589 'forecast_type_id'));
3590 x_sql := 'begin ' || p_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||x_profile_id||', '|| x_srs_id||', '|| p_load ||', '|| p_purge || '); end;';
3591 msd_dem_common_utilities.log_debug(x_sql);
3592 execute immediate x_sql;
3593
3594 commit;
3595
3596 IF (p_notify = 1 ) THEN
3597 --notify app server
3598 x_sql := 'begin ' || p_schema|| '.API_NOTIFY_APS_INTEGRATION('||x_profile_id ||'); end;';
3599 msd_dem_common_utilities.log_debug(x_sql);
3600 execute immediate x_sql;
3601 END IF;
3602
3603 msd_dem_common_utilities.log_debug ('Exiting: MSD_DEM_COMMON_UTILITIES.SET_SERIES_OPTIONS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3604
3605 EXCEPTION
3606 WHEN OTHERS THEN
3607 retcode := -1;
3608 errbuf := substr(SQLERRM,1,150);
3609 msd_dem_common_utilities.log_message ('EXCEPTION : MSD_DEM_COMMON_UTILITIES.SET_SERIES_OPTIONS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3610 msd_dem_common_utilities.log_message (errbuf);
3611 RETURN;
3612
3613 END SET_SERIES_OPTIONS;
3614 --BUG#13604468 --kkhatri
3615 /*
3616 * This function is used to get the correct organization name
3617 * for the site which is used for booking internal sales order
3618 * p_sr_instance_id - instance id
3619 * p_tp_site_id - trading partner site id
3620 * p_partner_id - trading partner id (org)
3621 * p_location_id - location id (not used)
3622 *
3623 */
3624
3625 FUNCTION get_org_for_internal_site(
3626 p_sr_instance_id number,
3627 p_tp_site_id number,
3628 p_partner_id number,
3629 p_location_id number default null)
3630
3631 RETURN VARCHAR2
3632 IS
3633 x_sql VARCHAR2(1000) := null;
3634 x_org_name VARCHAR2(100) := null;
3635
3636 BEGIN
3637
3638 x_sql := 'select '':'' || mtp_app_org.partner_name '
3639 || 'from msc_location_associations mla,
3640 msc_trading_partners mtp_app_org '
3641 || 'where mla.sr_instance_id = ' || p_sr_instance_id
3642 || 'and mla.partner_id = ' || p_partner_id
3643 || 'and mla.partner_site_id = ' || p_tp_site_id
3644 || 'and mtp_app_org.sr_instance_id = mla.sr_instance_id '
3645 || 'and mtp_app_org.sr_tp_id = mla.organization_id '
3646 || 'and mtp_app_org.partner_type = 3 '
3647 || 'and rownum < 2 '
3648 ;
3649
3650 EXECUTE IMMEDIATE x_sql INTO x_org_name;
3651
3652 RETURN x_org_name;
3653
3654 EXCEPTION
3655 WHEN OTHERS THEN
3656 RETURN NULL;
3657 END get_org_for_internal_site;
3658
3659 --BUG16070532 kkhatri
3660 /*
3661 * This function checks for the demantra workflow status
3662 * whether it is running (or) completed (or) failed.
3663 * Arguments
3664 * p_ret_process_id :- process id of the workflow launched
3665 * p_check_finish :- check status for every specified time in seconds
3666 * p_time_out :- check status till the given time in minutes
3667 * p_dem_schema :- demantra schema name
3668 * Returns
3669 * -1 - workflow failed
3670 * 0 - workflow completed
3671 * 1 - workflow running
3672 */
3673
3674 FUNCTION CHECK_WF_STATUS(
3675 p_ret_process_id IN NUMBER,
3676 p_check_finish IN NUMBER,
3677 p_time_out IN NUMBER,
3678 p_dem_schema IN varchar2)
3679 RETURN NUMBER IS
3680
3681 dem_status number(3);
3682 dem_schema_name varchar2(100);
3683 p_check_cnt number := 0;
3684 p_time_out_cnt number := 0;
3685 retcode number;
3686 l_sql varchar2(500) := null;
3687 BEGIN
3688
3689 -- converting into seconds
3690 p_time_out_cnt := p_time_out * 60;
3691
3692
3693 WHILE p_check_cnt <= p_time_out_cnt LOOP
3694
3695 l_sql := ' select wfpl.status, wfs.schema_name from '
3696 || p_dem_schema || '.wf_schemas wfs, '
3697 || p_dem_schema || '.wf_process_log wfpl '
3698 || ' where wfpl.process_id = ' || p_ret_process_id
3699 || ' and wfpl.schema_id = wfs.schema_id ' ;
3700 EXECUTE immediate l_sql INTO dem_status,dem_schema_name ;
3701
3702 IF (dem_status = 1) then
3703 DBMS_LOCK.SLEEP( p_check_finish );
3704 ElsIf (dem_status = 0) then
3705 msd_dem_common_utilities.log_message( dem_schema_name || ' Workflow completed successfully.');
3706 retcode := 0;
3707 exit;
3708 ElsIf (dem_status = -1) then
3709 msd_dem_common_utilities.log_message( dem_schema_name || ' Workflow Failed.');
3710 retcode := -1;
3711 exit;
3712 Else
3713 msd_dem_common_utilities.log_message( dem_schema_name || ' Workflow Status not defined. ');
3714 retcode := 1;
3715 exit;
3716 End if;
3717
3718 p_check_cnt := p_check_cnt + p_check_finish ;
3719
3720 If (p_check_cnt >= p_time_out_cnt) then
3721 msd_dem_common_utilities.log_message( dem_schema_name || ' Workflow timed out. Check status of the workflow');
3722 retcode := 1;
3723 End if;
3724
3725
3726 END LOOP;
3727
3728 RETURN retcode;
3729
3730 END CHECK_WF_STATUS;
3731
3732 END MSD_DEM_COMMON_UTILITIES;