[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_COLLECT_HISTORY_DATA
Source
1 PACKAGE BODY MSD_DEM_COLLECT_HISTORY_DATA AS
2 /* $Header: msddemchdb.pls 120.5 2008/04/14 08:24:44 vrepaka noship $ */
3
4
5 /*** CUSTOM DATA TYPES ***/
6
7 TYPE ORDER_TYPE_TABLE_TYPE IS TABLE OF VARCHAR2(100);
8 TYPE ORDER_TYPE_ID_TABLE_TYPE IS TABLE OF NUMBER;
9
10 /*** CONSTANTS ***/
11 C_ALL CONSTANT NUMBER := 1;
12 C_INCLUDE CONSTANT NUMBER := 2;
13 C_EXCLUDE CONSTANT NUMBER := 3;
14
15 VS_MSG_SALES_TABLE CONSTANT VARCHAR2(16) := 'LOAD SALES TABLE';
16 VS_MSG_ITEMS_TABLE CONSTANT VARCHAR2(16) := 'LOAD ITEMS TABLE';
17 VS_MSG_LOCATION_TABLE CONSTANT VARCHAR2(19) := 'LOAD LOCATION TABLE';
18
19 VS_MSG_LOADING CONSTANT VARCHAR2(8) := 'Loading ';
20 VS_MSG_LOADED CONSTANT VARCHAR2(7) := 'Loaded ';
21 VS_MSG_STARTED CONSTANT VARCHAR2(7) := 'Started';
22 VS_MSG_SUCCEEDED CONSTANT VARCHAR2(9) := 'Succeeded';
23 VS_MSG_LOADE_ERROR CONSTANT VARCHAR2(12) := 'Load error: ';
24 VS_MSG_ITEMS CONSTANT VARCHAR2(12) := 'Items';
25 VS_MSG_LOCATIONS CONSTANT VARCHAR2(12) := 'Locations';
26 VS_MSG_SALES CONSTANT VARCHAR2(12) := 'Sales';
27
28 /*** GLOBAL VARIABLES ***/
29 g_dblink VARCHAR2(50) := NULL;
30 g_collection_method NUMBER := NULL;
31
32 /*** PRIVATE FUNCTIONS ***/
33
34 /*
35 * This function validates the order types given
36 * by the user.
37 * This function returns the number of invalid
38 * order types found in the user input.
39 * Returns '-1' incase of ERROR.
40 */
41 FUNCTION VALIDATE_ORDER_TYPES (
42 errbuf OUT NOCOPY VARCHAR2,
43 retcode OUT NOCOPY VARCHAR2,
44 p_order_type_flag OUT NOCOPY NUMBER,
45 p_order_type_ids OUT NOCOPY VARCHAR2,
46 p_collect_all_order_types IN NUMBER,
47 p_include_order_types IN VARCHAR2,
48 p_exclude_order_types IN VARCHAR2)
49 RETURN NUMBER
50 IS
51
52 l_order_type_table ORDER_TYPE_TABLE_TYPE;
53 l_order_category_code_table ORDER_TYPE_TABLE_TYPE;
54 l_order_type_id_table ORDER_TYPE_ID_TABLE_TYPE;
55 l_valid_order_type_table ORDER_TYPE_TABLE_TYPE;
56 l_invalid_order_type_table ORDER_TYPE_TABLE_TYPE;
57
58 l_sql_stmt VARCHAR2(2000);
59 l_order_types VARCHAR2(2000);
60 l_original_order_types VARCHAR2(2000);
61 l_order_type_ids VARCHAR2(2000);
62 l_token VARCHAR2(100);
63 l_original_token VARCHAR2(100);
64
65 l_order_type_flag NUMBER;
66 l_start NUMBER := 1;
67 l_position NUMBER := -1;
68 l_valid_count NUMBER := 0;
69 l_invalid_count NUMBER := 0;
70
71 l_found BOOLEAN;
72
73 BEGIN
74
75 /* If collect all order types is yes, then ignore other fields */
76 IF (p_collect_all_order_types = G_YES)
77 THEN
78
79 IF ( p_include_order_types IS NOT NULL
80 OR p_exclude_order_types IS NOT NULL)
81 THEN
82 retcode := 1;
83 errbuf := 'The parameters Include Order Types and Exclude Order Types are ignored, if Collect All Order Types is Yes.';
84 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
85 msd_dem_common_utilities.log_message (errbuf);
86 END IF;
87
88 p_order_type_flag := C_ALL;
89 p_order_type_ids := '';
90 RETURN 0;
91
92 END IF;
93
94
95 /* Get all the valid order types from the source*/
96 l_sql_stmt := 'SELECT ' ||
97 'B.TRANSACTION_TYPE_ID ORDER_TYPE_ID, ' ||
98 'UPPER(B.ORDER_CATEGORY_CODE) ORDER_CATEGORY_CODE, ' ||
99 'UPPER(T.NAME) NAME ' ||
100 'FROM ' ||
101 'OE_TRANSACTION_TYPES_TL' || g_dblink || ' T, ' ||
102 'OE_TRANSACTION_TYPES_ALL' || g_dblink || ' B '||
103 'WHERE ' ||
104 'B.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID AND ' ||
105 'B.Transaction_type_code = ''ORDER'' AND ' ||
106 'nvl(B.SALES_DOCUMENT_TYPE_CODE,''O'') <> ''B'' AND ' ||
107 'T.LANGUAGE = userenv(''LANG'') ';
108
109 EXECUTE IMMEDIATE l_sql_stmt
110 BULK COLLECT INTO l_order_type_id_table,
111 l_order_category_code_table,
112 l_order_type_table;
113
114 IF (l_order_type_table.COUNT = 0)
115 THEN
116 retcode := -1;
117 errbuf := 'No order types found in the source';
118 msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
119 msd_dem_common_utilities.log_message (errbuf);
120 RETURN -1;
121 END IF;
122
123 IF (p_collect_all_order_types = G_NO)
124 THEN
125
126 IF ( p_include_order_types IS NULL
127 AND p_exclude_order_types IS NULL)
128 THEN
129 retcode := -1;
130 errbuf := 'Exactly one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
131 msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
132 msd_dem_common_utilities.log_message (errbuf);
133 RETURN -1;
134 ELSIF ( p_include_order_types IS NOT NULL
135 AND p_exclude_order_types IS NOT NULL)
136 THEN
137 retcode := -1;
138 errbuf := 'Only one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
139 msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
140 msd_dem_common_utilities.log_message (errbuf);
141 RETURN -1;
142 ELSIF (p_include_order_types IS NOT NULL)
143 THEN
144 l_order_type_flag := C_INCLUDE;
145 l_order_types := UPPER(p_include_order_types);
146 l_original_order_types := p_include_order_types;
147 ELSE
148 l_order_type_flag := C_EXCLUDE;
149 l_order_types := UPPER(p_exclude_order_types);
150 l_original_order_types := p_exclude_order_types;
151 END IF;
152
153 l_valid_order_type_table := ORDER_TYPE_TABLE_TYPE();
154 l_invalid_order_type_table := ORDER_TYPE_TABLE_TYPE();
155
156 /* Get the valid and invalid order types given by the user */
157 LOOP
158
159 l_position := INSTR( l_order_types, ',', l_start, 1);
160
161 /* Get the token (order type)*/
162 IF (l_position <> 0)
163 THEN
164 l_token := SUBSTR( l_order_types, l_start, l_position - l_start);
165 l_original_token := SUBSTR( l_original_order_types, l_start, l_position - l_start);
166 ELSE
167 l_token := SUBSTR( l_order_types, l_start);
168 l_original_token := SUBSTR( l_original_order_types, l_start);
169 END IF;
170
171 /* Validate the order type*/
172 l_found := FALSE;
173 FOR i IN l_order_type_table.FIRST..l_order_type_table.LAST
174 LOOP
175
176 /* Valid order type */
177 IF ( l_order_category_code_table(i) <> 'RETURN'
178 AND l_token = l_order_type_table(i))
179 THEN
180
181 l_found := TRUE;
182 l_valid_count := l_valid_count + 1;
183 l_valid_order_type_table.EXTEND;
184 l_valid_order_type_table(l_valid_count) := l_original_token;
185
186 IF (l_valid_count = 1)
187 THEN
188 l_order_type_ids := l_order_type_ids || to_char(l_order_type_id_table(i));
189 ELSE
190 l_order_type_ids := l_order_type_ids || ',' || to_char(l_order_type_id_table(i));
191 END IF;
192
193 EXIT;
194
195 /* Invalid order type since order category code is 'RETURN' */
196 ELSIF ( l_order_category_code_table(i) = 'RETURN'
197 AND l_token = l_order_type_table(i))
198 THEN
199
200 l_found := TRUE;
201 l_invalid_count := l_invalid_count + 1;
202 l_invalid_order_type_table.EXTEND;
203 l_invalid_order_type_table(l_invalid_count) := l_original_token || ' (Order Type is RETURN)';
204
205 EXIT;
206
207 END IF;
208
209 END LOOP;
210
211 /* Invalid order type */
212 IF (l_found = FALSE)
213 THEN
214 l_invalid_count := l_invalid_count + 1;
215 l_invalid_order_type_table.EXTEND;
216 l_invalid_order_type_table(l_invalid_count) := l_original_token;
217 END IF;
218
219 EXIT WHEN l_position = 0;
220 l_start := l_position + 1;
221
222 END LOOP;
223
224 msd_dem_common_utilities.log_message (' Order Types');
225 msd_dem_common_utilities.log_message (' -------------');
226
227 msd_dem_common_utilities.log_message (' Valid Order Types');
228 msd_dem_common_utilities.log_message (' -------------------');
229
230 IF (l_valid_count <> 0)
231 THEN
232 FOR i in l_valid_order_type_table.FIRST..l_valid_order_type_table.LAST
233 LOOP
234 msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_valid_order_type_table(i));
235 END LOOP;
236 ELSE
237 msd_dem_common_utilities.log_message ('No valid order types found in user input');
238 END IF;
239
240 msd_dem_common_utilities.log_message (' ');
241 msd_dem_common_utilities.log_message (' Invalid Order Types');
242 msd_dem_common_utilities.log_message (' ---------------------');
243
244 IF (l_invalid_count <> 0)
245 THEN
246 FOR i in l_invalid_order_type_table.FIRST..l_invalid_order_type_table.LAST
247 LOOP
248 msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_invalid_order_type_table(i));
249 END LOOP;
250 END IF;
251
252 IF (l_valid_count = 0)
253 THEN
254 retcode := -1;
255 errbuf := 'No valid order types found in user input';
256 msd_dem_common_utilities.log_message ('Error(4): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
257 RETURN -1;
258 END IF;
259
260 IF (l_invalid_count <> 0)
261 THEN
262 retcode := 1;
263 errbuf := 'Invalid order types found in user input';
264 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.validate_order_types - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
265 END IF;
266
267 END IF;
268
269 p_order_type_flag := l_order_type_flag;
270 p_order_type_ids := l_order_type_ids;
271 RETURN l_invalid_count;
272
273 END VALIDATE_ORDER_TYPES;
274
275
276
277 /*** PRIVATE PROCEDURES ***/
278
279
280 /*
281 * This procedure given the series id, gets the
282 * data from the source instance and upserts into the
283 * sales staging table.
284 */
285 PROCEDURE COLLECT_SERIES_DATA (
286 errbuf OUT NOCOPY VARCHAR2,
287 retcode OUT NOCOPY VARCHAR2,
288 p_series_id IN NUMBER,
289 p_dest_table IN VARCHAR2,
290 p_dm_time_level IN NUMBER,
291 p_sr_instance_id IN NUMBER,
292 p_apps_ver IN NUMBER,
293 p_instance_type IN NUMBER,
294 p_collection_group IN VARCHAR2,
295 p_collection_method IN NUMBER,
296 p_from_date IN DATE,
297 p_to_date IN DATE,
298 p_collect_iso IN NUMBER,
299 p_order_type_flag IN NUMBER,
300 p_order_type_ids IN VARCHAR2)
301 IS
302
303 /*** CURSORS ***/
304
305 CURSOR c_get_series_info
306 IS
307 SELECT
308 identifier, STG_SERIES_COL_NAME, MSD_SR_ITEM_PK_COL, MSD_SOURCE_DATE_COL, GMP_SR_ITEM_PK_COL, GMP_SOURCE_DATE_COL, CUSTOM_VIEW_NAME, GMP_CUSTOM_VIEW_NAME
309 FROM
310 msd_dem_series
311 WHERE
312 series_id = p_series_id
313 AND series_type = 1;
314
315 /*** LOCAL VARIABLES ***/
316 x_errbuf VARCHAR2(200) := NULL;
317 x_errbuf1 VARCHAR2(200) := NULL;
318 x_retcode VARCHAR2(100) := NULL;
319 x_retcode1 VARCHAR2(100) := NULL;
320 XDBLINK VARCHAR2(100) := NULL;
321
322 x_large_sql VARCHAR2(32000) := NULL;
323 x_add_where_clause VARCHAR2(3000) := NULL;
324 x_key_values VARCHAR2(4000) := NULL;
325 x_is_custom NUMBER := NULL;
326 x_gmp_is_custom NUMBER := NULL;
327
328 x_dquery_identifier VARCHAR2(30) := NULL;
329 x_pquery_identifier VARCHAR2(30) := NULL;
330
331 l_identifier varchar2(30) := NULL;
332 l_STG_SERIES_COL_NAME varchar2(100) := NULL;
333 l_MSD_SR_ITEM_PK_COL varchar2(500) := NULL;
334 l_MSD_SOURCE_DATE_COL varchar2(500) := NULL;
335 l_GMP_SR_ITEM_PK_COL varchar2(500) := NULL;
336 l_GMP_SOURCE_DATE_COL varchar2(500) := NULL;
337 l_custom_view_name varchar2(100) := NULL;
338 l_gmp_custom_view_name varchar2(100) := NULL;
339
340 BEGIN
341
342 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
343
344 OPEN c_get_series_info;
345 FETCH c_get_series_info INTO l_identifier, l_STG_SERIES_COL_NAME, l_MSD_SR_ITEM_PK_COL, l_MSD_SOURCE_DATE_COL,l_GMP_SR_ITEM_PK_COL, l_GMP_SOURCE_DATE_COL, l_custom_view_name, l_gmp_custom_view_name;
346 CLOSE c_get_series_info;
347
348 IF (l_identifier IS NULL)
349 THEN
350 retcode := -1;
351 errbuf := 'Unable to get the query identifier.';
352 msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
353 msd_dem_common_utilities.log_message (errbuf);
354 RETURN;
355 END IF;
356
357 /* Check if custom view for Discrete */
358 IF (l_custom_view_name IS NULL)
359 THEN
360 x_is_custom := 0;
361 ELSE
362 x_is_custom := 1;
366 IF (l_gmp_custom_view_name IS NULL)
363 END IF;
364
365 /* Check if custom view for Process */
367 THEN
368 x_gmp_is_custom := 0;
369 ELSE
370 x_gmp_is_custom := 1;
371 END IF;
372
373 /* For Discrete */
374 /* 11i instance where instance type in not 'PROCESS' OR R12 Instance of any type */
375 IF ( p_instance_type <> 2
376 OR p_apps_ver = 4)
377 THEN
378
379 msd_dem_common_utilities.log_debug ('Begin collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
380
381 x_add_where_clause := ' 1 = 1 ';
382
383 /* If p_collect_iso = No, then include an additional condition to filter out Internal Sales Orders */
384 IF (p_collect_ISO = G_NO)
385 THEN
386 x_add_where_clause := x_add_where_clause || ' AND nvl(h.order_source_id, 0) <> 10 ';
387 END IF;
388
389 /* Include an additional condition to filter data based on order types specified by the user */
390 IF (p_order_type_flag = C_INCLUDE)
391 THEN
392 x_add_where_clause := x_add_where_clause || ' AND h.order_type_id IN (' || p_order_type_ids || ') ';
393 ELSIF (p_order_type_flag = C_EXCLUDE)
394 THEN
395 x_add_where_clause := x_add_where_clause || ' AND h.order_type_id NOT IN (' || p_order_type_ids || ') ';
396 END IF;
397
398
399 IF(p_dm_time_level = 1) then
400
401 x_key_values := '$C_DEST_TABLE#' || p_dest_table
402 || '$C_SERIES_QTY#' || l_STG_SERIES_COL_NAME
403 || '$C_DEST_DATE_GROUP#' || 'MDBR.SDATE'
404 || '$C_DEST_DATE#' || 'MDBR.SDATE'
405 || '$C_SR_ITEM_PK#' || l_MSD_SR_ITEM_PK_COL
406 || '$C_SOURCE_DATE#' || nvl(substr(l_msd_source_date_col, 0, instr(upper(l_msd_source_date_col), 'SDATE')-1), to_date('01/01/1000', 'DD/MM/YYYY')) || ' SDATE'
407 || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
408 || '$C_ITEM_PK_JOIN#' || substr(l_MSD_SR_ITEM_PK_COL, 0, instr(upper(l_MSD_SR_ITEM_PK_COL), 'SR_ITEM_PK')-1)
409 || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
410 || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
411
412 IF (g_collection_method <> 1) THEN
413 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' WHERE SDATE BETWEEN '
414 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
415 || ' AND '
416 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') ';
417 ELSE
418 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' ';
419 END IF;
420
421 x_key_values := x_key_values || '$';
422
423 ELSE
424
425 x_key_values := '$C_DEST_TABLE#' || p_dest_table
426 || '$C_SERIES_QTY#' || l_STG_SERIES_COL_NAME
427 || '$C_DEST_DATE_GROUP#' || 'INP.DATET'
428 || '$C_DEST_DATE#' || 'INP.DATET SDATE'
429 || '$C_SR_ITEM_PK#' || l_MSD_SR_ITEM_PK_COL
430 || '$C_SOURCE_DATE#' || SUBSTR(L_MSD_SOURCE_DATE_COL, 1, instr(L_MSD_SOURCE_DATE_COL, 'SDATE')-1) ||' PDATE'
431 || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
432 || '$C_ITEM_PK_JOIN#' || substr(l_MSD_SR_ITEM_PK_COL, 0, instr(upper(l_MSD_SR_ITEM_PK_COL), 'SR_ITEM_PK')-1)
433 || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
434 || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
435
436 IF (g_collection_method <> 1) THEN
437 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN '
438 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
439 || ' AND '
440 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
441 || ' AND MDBR.PDATE BETWEEN inp.start_date AND inp.end_date ';
442 ELSE
443 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN inp.start_date AND inp.end_date ';
444 END IF;
445
446 x_key_values := x_key_values || '$';
447
448 END IF;
449
450 /* Get the query */
451 msd_dem_query_utilities.get_query2 (
452 x_retcode1,
453 x_large_sql,
454 l_identifier,
455 p_sr_instance_id,
456 x_key_values,
457 x_is_custom,
458 l_custom_view_name);
459
460 IF ( x_retcode1 = '-1'
461 OR x_large_sql IS NULL)
462 THEN
466 msd_dem_common_utilities.log_message (errbuf);
463 retcode := -1;
464 errbuf := 'Unable to get the query.';
465 msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
467 RETURN;
468 END IF;
469
470
471 msd_dem_common_utilities.log_debug ('Query - ');
472 msd_dem_common_utilities.log_debug (x_large_sql);
473
474 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
475
476 BEGIN
477 /* Upsert history data into sales staging table */
478 EXECUTE IMMEDIATE x_large_sql;
479 COMMIT;
480 EXCEPTION
481 WHEN OTHERS THEN
482 retcode := -1 ;
483 errbuf := substr(SQLERRM,1,150);
484 msd_dem_common_utilities.log_message ('Exception(1): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
485 msd_dem_common_utilities.log_message (errbuf);
486 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
487 RETURN;
488 END;
489
490 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
491
492 msd_dem_common_utilities.log_debug ('End collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
493
494 END IF;
495
496 x_large_sql := NULL;
497 x_key_values := NULL;
498
499 /* For Process */
500 IF ( p_instance_type IN (2, 4)
501 AND p_apps_ver = 3)
502 THEN
503
504 msd_dem_common_utilities.log_debug ('Begin collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
505
506 x_pquery_identifier := replace(l_identifier , 'MSD','GMP') ;
507
508 x_add_where_clause := ' 1 = 1 ';
509
510 /* If p_collect_iso = No, then include an additional condition to filter out Internal Sales Orders */
511 IF (p_collect_ISO = G_NO)
512 THEN
513 x_add_where_clause := x_add_where_clause || ' AND decode(ool.to_whse, NULL, 10, 0) <> 10 ';
514 END IF;
515
516 /*** ORDER TYPES Filters are not supported for process sales data ***/
517
518
519 IF(p_dm_time_level = 1) then
520
521 x_key_values := '$C_DEST_TABLE#' || p_dest_table
522 || '$C_SERIES_QTY#' || l_STG_SERIES_COL_NAME
523 || '$C_DEST_DATE_GROUP#' || 'MDBR.SDATE'
524 || '$C_DEST_DATE#' || 'MDBR.SDATE'
525 || '$C_SOURCE_DATE#' || NVL(substr(l_gmp_source_date_col, 0, instr(upper(l_gmp_source_date_col), 'SDATE')-1), TO_DATE('01/01/1000', 'DD/MM/YYYY')) || ' SDATE'
526 || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause;
527
528 IF (g_collection_method <> 1) THEN
529 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' WHERE SDATE BETWEEN '
530 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
531 || ' AND '
532 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') ';
533 ELSE
534 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' ';
535 END IF;
536
537 x_key_values := x_key_values || '$';
538
539 ELSE
540
541 x_key_values := '$C_DEST_TABLE#' || p_dest_table
542 || '$C_SERIES_QTY#' || l_STG_SERIES_COL_NAME
543 || '$C_DEST_DATE_GROUP#' || 'INP.DATET'
544 || '$C_DEST_DATE#' || 'INP.DATET SDATE'
545 || '$C_SOURCE_DATE#' || SUBSTR(L_GMP_SOURCE_DATE_COL, 1, instr(L_GMP_SOURCE_DATE_COL, 'SDATE')-1)||' PDATE'
546 || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause;
547
548 IF (g_collection_method <> 1) THEN
549 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN '
550 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
551 || ' AND '
552 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
553 || ' AND MDBR.PDATE BETWEEN inp.start_date AND inp.end_date ';
554 ELSE
555 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN inp.start_date AND inp.end_date ';
556 END IF;
557
558 x_key_values := x_key_values || '$';
559
560 END IF;
561
562 /* Get the query */
563 msd_dem_query_utilities.get_query2 (
564 x_retcode1,
565 x_large_sql,
566 x_pquery_identifier,
567 p_sr_instance_id,
571
568 x_key_values,
569 x_gmp_is_custom,
570 l_gmp_custom_view_name);
572 IF ( x_retcode1 = '-1'
573 OR x_large_sql IS NULL)
574 THEN
575 retcode := -1;
576 errbuf := 'Unable to get the query.';
577 msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
578 msd_dem_common_utilities.log_message (errbuf);
579 RETURN;
580 END IF;
581
582
583 msd_dem_common_utilities.log_debug ('Query - ');
584 msd_dem_common_utilities.log_debug (x_large_sql);
585
586 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
587
588 BEGIN
589 /* Upsert history data into sales staging table */
590 EXECUTE IMMEDIATE x_large_sql;
591 COMMIT;
592 EXCEPTION
593 WHEN OTHERS THEN
594 retcode := -1 ;
595 errbuf := substr(SQLERRM,1,150);
596 msd_dem_common_utilities.log_message ('Exception(2): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
597 msd_dem_common_utilities.log_message (errbuf);
598 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
599 RETURN;
600 END;
601
602 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
603 msd_dem_common_utilities.log_debug ('End collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
604
605 END IF;
606
607 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
608
609 EXCEPTION
610 WHEN OTHERS THEN
611 retcode := -1 ;
612 errbuf := substr(SQLERRM,1,150);
613 msd_dem_common_utilities.log_message ('Exception(3): msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
614 msd_dem_common_utilities.log_message (errbuf);
615 RETURN;
616
617 END COLLECT_SERIES_DATA;
618
619
620
621 /*
622 * This procedure inserts dummy rows into the sales staging tables for new items
623 */
624 PROCEDURE INSERT_DUMMY_ROWS (
625 errbuf OUT NOCOPY VARCHAR2,
626 retcode OUT NOCOPY VARCHAR2,
627 p_dest_table IN VARCHAR2,
628 p_sr_instance_id IN NUMBER)
629 IS
630
631 /*** CURSORS ***/
632
633 CURSOR c_check_new_items
634 IS
635 SELECT 1
636 FROM dual
637 WHERE EXISTS (SELECT 1
638 FROM msd_dem_new_items
639 WHERE sr_instance_id = p_sr_instance_id
640 AND process_flag = 2);
641
642 /*** LOCAL VARIABLES ***/
643 x_retcode VARCHAR2(100) := NULL;
644
645 x_new_items_present NUMBER := NULL;
646 x_sql VARCHAR2(32000) := NULL;
647 BEGIN
648
649 msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
650
651 /* Check if there are any yet to be processed NPIs */
652 OPEN c_check_new_items;
653 FETCH c_check_new_items INTO x_new_items_present;
654 CLOSE c_check_new_items;
655
656 IF (x_new_items_present = 1)
657 THEN
658 msd_dem_common_utilities.log_message ('Found new items for processing');
659
660 msd_dem_query_utilities.get_query(
661 x_retcode,
662 x_sql,
663 'DUMMY_ROWS_FOR_NEW_ITEMS',
664 p_sr_instance_id,
665 p_dest_table);
666
667 IF (x_retcode = -1)
668 THEN
669 retcode := 1;
670 errbuf := 'Unable to get the query for inserting dummy rows for new items into sales staging table';
671 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.insert_dummy_rows');
672 msd_dem_common_utilities.log_message (errbuf);
673 RETURN;
674 END IF;
675
676 msd_dem_common_utilities.log_debug ('Query - ');
677 msd_dem_common_utilities.log_debug ('Bind Variables - ');
678 msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
679 msd_dem_common_utilities.log_debug (x_sql);
680
681 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682 EXECUTE IMMEDIATE x_sql USING p_sr_instance_id, p_sr_instance_id;
683 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
684
685 /* Set the process_flag */
686 UPDATE msd_dem_new_items
690
687 SET process_flag = 1
688 WHERE sr_instance_id = p_sr_instance_id
689 AND process_flag = 2;
691 COMMIT;
692
693 END IF;
694
695 msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
696
697 EXCEPTION
698 WHEN OTHERS THEN
699 retcode := 1 ;
700 errbuf := substr(SQLERRM,1,150);
701 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
702 msd_dem_common_utilities.log_message (errbuf);
703 RETURN;
704 END INSERT_DUMMY_ROWS;
705
706
707 /*
708 * This procedure analyzes the given table
709 */
710 PROCEDURE ANALYZE_TABLE (
711 errbuf OUT NOCOPY VARCHAR2,
712 retcode OUT NOCOPY VARCHAR2,
713 p_table_name IN VARCHAR2)
714 IS
715
716 /*** LOCAL VARIABLES ***/
717
718 x_schema_name VARCHAR2(30) := NULL;
719 x_table_name VARCHAR2(30) := NULL;
720
721 x_pos NUMBER := NULL;
722
723 BEGIN
724
725 msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
726
727 x_pos := instr( p_table_name, '.', 1, 1);
728
729 IF (x_pos = 0)
730 THEN
731 x_schema_name := 'MSD';
732 x_table_name := p_table_name;
733 ELSE
734 x_schema_name := substr (p_table_name, 1, x_pos - 1);
735 x_table_name := substr (p_table_name, x_pos +1);
736 END IF;
737
738 msd_dem_common_utilities.log_message ('Analyzing Table - ' || x_schema_name || '.' || x_table_name);
739 fnd_stats.gather_table_stats(x_schema_name, x_table_name, 10, 4);
740
741 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
742
743
744 EXCEPTION
745 WHEN OTHERS THEN
746 retcode := 1 ;
747 errbuf := substr(SQLERRM,1,150);
748 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
749 msd_dem_common_utilities.log_message (errbuf);
750 RETURN;
751 END ANALYZE_TABLE;
752
753 /*** PUBLIC PROCEDURES ***/
754
755
756 PROCEDURE COLLECT_HISTORY_DATA (
757 errbuf OUT NOCOPY VARCHAR2,
758 retcode OUT NOCOPY VARCHAR2,
759 p_sr_instance_id IN NUMBER,
760 p_collection_group IN VARCHAR2,
761 p_collection_method IN NUMBER,
762 p_hidden_param1 IN VARCHAR2,
763 p_date_range_type IN NUMBER,
764 p_collection_window IN NUMBER,
765 p_from_date IN VARCHAR2,
766 p_to_date IN VARCHAR2,
767 p_bh_bi_bd IN NUMBER,
768 p_bh_bi_rd IN NUMBER,
769 p_bh_ri_bd IN NUMBER,
770 p_bh_ri_rd IN NUMBER,
771 p_sh_si_sd IN NUMBER,
772 p_sh_si_rd IN NUMBER,
773 p_sh_ri_sd IN NUMBER,
774 p_sh_ri_rd IN NUMBER,
775 p_collect_iso IN NUMBER DEFAULT G_NO,
776 p_collect_all_order_types IN NUMBER DEFAULT G_YES,
777 p_include_order_types IN VARCHAR2 DEFAULT NULL,
778 p_exclude_order_types IN VARCHAR2 DEFAULT NULL,
779 p_auto_run_download IN NUMBER )
780 IS
781
782 /*** LOCAL VARIABLES ****/
783
784 x_errbuf VARCHAR2(200) := NULL;
785 x_errbuf1 VARCHAR2(200) := NULL;
786 x_retcode VARCHAR2(100) := NULL;
787 x_retcode1 VARCHAR2(100) := NULL;
788
789 x_order_type_ids VARCHAR2(2000);
790 x_order_type_flag NUMBER;
791 x_invalid_count NUMBER := 0;
792 x_dest_table VARCHAR2(100) := NULL;
793
794 x_sql VARCHAR2(1000) := NULL;
795
796 x_from_date DATE := NULL;
797 x_to_date DATE := NULL;
798
799 x_instance_code VARCHAR2(30) := NULL;
800 x_apps_ver NUMBER := NULL;
801 x_dgmt NUMBER := NULL;
802 x_instance_type NUMBER := NULL;
803 x_dm_time_level NUMBER := NULL;
804 x_dm_time_bucket VARCHAR2(30) := NULL;
805
806 g_schema VARCHAR2(50) := NULL;
807
808 l_sql VARCHAR2(1000) := NULL;
809 l_profile_id NUMBER := NULL;
810 l_bh_bi_bd_id NUMBER := NULL;
811 l_bh_bi_rd_id NUMBER := NULL;
812 l_bh_ri_bd_id NUMBER := NULL;
813 l_bh_ri_rd_id NUMBER := NULL;
814 l_sh_si_sd_id NUMBER := NULL;
815 l_sh_si_rd_id NUMBER := NULL;
816 l_sh_ri_sd_id NUMBER := NULL;
817 l_sh_ri_rd_id NUMBER := NULL;
818
819 l_table_name varchar2(240) := NULL;
820 l_start_date date := NULL;
824 CURSOR c_get_dm_schema --jarora
821 l_until_date date := NULL;
822
823
825 IS
826 SELECT owner
827 FROM dba_objects
828 WHERE owner = owner
829 AND object_type = 'TABLE'
830 AND object_name = 'MDP_MATRIX'
831 ORDER BY created desc;
832
833 BEGIN
834
835 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
836
837 /* Get the db link to the source instance */
838 msd_dem_common_utilities.get_dblink (
839 x_errbuf,
840 x_retcode,
841 p_sr_instance_id,
842 g_dblink);
843
844 IF (x_retcode = '-1')
845 THEN
846 retcode := -1;
847 errbuf := x_errbuf;
848 msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
849 RETURN;
850 END IF;
851
852 g_collection_method := p_collection_method;
853
854 /* VALIDATION OF INPUT PARAMETERS - BEGIN */
855
856 msd_dem_common_utilities.log_debug ('Begin validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
857
858 /* Atleast one parameter must be selected */
859 IF ( p_bh_bi_bd = G_NO
860 AND p_bh_bi_rd = G_NO
861 AND p_bh_ri_bd = G_NO
862 AND p_bh_ri_rd = G_NO
863 AND p_sh_si_sd = G_NO
864 AND p_sh_si_rd = G_NO
865 AND p_sh_ri_sd = G_NO
866 AND p_sh_ri_rd = G_NO)
867 THEN
868 retcode := -1;
869 errbuf := 'No series selected for collection';
870 msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
871 msd_dem_common_utilities.log_message (errbuf);
872 RETURN;
873 END IF;
874
875
876 /* Show Warning if collection method is Refresh and a date range filter is specified */
877 IF ( p_collection_method = 1
878 AND ( p_from_date IS NOT NULL
879 OR p_to_date IS NOT NULL
880 OR p_collection_window IS NOT NULL))
881 THEN
882 x_retcode := 1;
883 x_errbuf := 'Date Range filters are ignored in ''Refresh'' collections';
884 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
885 msd_dem_common_utilities.log_message (x_errbuf);
886 END IF;
887
888
889 /* Show Warning if collection method is net change, date range type is Rolling and from date and to date are specified */
890 IF ( p_collection_method = 2
891 AND p_date_range_type = 2
892 AND ( p_from_date IS NOT NULL
893 OR p_to_date IS NOT NULL))
894 THEN
895 x_retcode := 1;
896 x_errbuf := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date range type is selected.';
897 msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
898 msd_dem_common_utilities.log_message (x_errbuf);
899 END IF;
900
901
902 /* Show Warning if collection method is net change, date range type is Absolute and history collection window is specified */
903 IF ( p_collection_method = 2
904 AND p_date_range_type = 1
905 AND p_collection_window IS NOT NULL)
906 THEN
907 x_retcode := 1;
908 x_errbuf := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
909 msd_dem_common_utilities.log_message ('Warning(3): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
910 msd_dem_common_utilities.log_message (x_errbuf);
911 END IF;
912
913
914 /* Error if collection method is net change, date range type is Rolling and history collection window is not specified */
915 IF ( p_collection_method = 2
916 AND p_date_range_type = 2
917 AND p_collection_window IS NULL)
918 THEN
919 retcode := -1;
920 errbuf := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
921 msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
922 msd_dem_common_utilities.log_message (errbuf);
923 RETURN;
924 END IF;
925
926
927 /* Error if collection method is net change, date range type is Absolute and from date and to date are not specified */
928 IF ( p_collection_method = 2
929 AND p_date_range_type = 1
930 AND ( p_from_date IS NULL
931 OR p_to_date IS NULL))
932 THEN
933 retcode := -1;
937 RETURN;
934 errbuf := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
935 msd_dem_common_utilities.log_message ('Error(4): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
936 msd_dem_common_utilities.log_message (errbuf);
938 END IF;
939
940 /* Validate the order types specified by the user */
941 x_invalid_count := validate_order_types (
942 x_errbuf1,
943 x_retcode1,
944 x_order_type_flag,
945 x_order_type_ids,
946 p_collect_all_order_types,
947 p_include_order_types,
948 p_exclude_order_types );
949 IF (x_retcode1 = -1)
950 THEN
951 retcode := -1;
952 errbuf := 'No valid order types found';
953 msd_dem_common_utilities.log_message ('Error(5): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
954 msd_dem_common_utilities.log_message (errbuf);
955 RETURN;
956 ELSIF (x_invalid_count > 0)
957 THEN
958 x_retcode := 1;
959 x_errbuf := 'Invalid order types found';
960 msd_dem_common_utilities.log_message ('Warning(4): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
961 msd_dem_common_utilities.log_message (x_errbuf);
962 ELSIF (x_retcode1 = 1)
963 THEN
964 x_retcode := 1;
965 x_errbuf := x_errbuf1;
966 msd_dem_common_utilities.log_message ('Warning(5): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
967 END IF;
968
969 msd_dem_common_utilities.log_debug ('End validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
970
971 /* VALIDATION OF INPUT PARAMETERS - END */
972
973
974 /* Get the start date and end dates for collection */
975
976 msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
977
978 IF (p_collection_method = 1) /* Refresh*/
979 THEN
980 x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
981 x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
982 ELSE /* Net Change */
983 IF (p_date_range_type = 1) /* Absolute*/
984 THEN
985
986 IF (p_from_date IS NULL)
987 THEN
988 x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
989 ELSE
990 x_from_date := fnd_date.canonical_to_date (p_from_date);
991 END IF;
992
993 IF (p_to_date IS NULL)
994 THEN
995 x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
996 ELSE
997 x_to_date := fnd_date.canonical_to_date (p_to_date);
998 END IF;
999
1000 /* Error if p_from_date is greater than p_to_date */
1001 IF (x_from_date > x_to_date)
1002 THEN
1003 retcode := -1;
1004 errbuf := 'From Date should not be greater than To Date.';
1005 msd_dem_common_utilities.log_message ('Error(6): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1006 msd_dem_common_utilities.log_message (errbuf);
1007 RETURN;
1008 END IF;
1009
1010 ELSE /* Rolling */
1011
1012 IF (p_collection_window < 0)
1013 THEN
1014 retcode := -1;
1015 errbuf := 'History Collection Window must be a positive number.';
1016 msd_dem_common_utilities.log_message ('Error(7): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1017 msd_dem_common_utilities.log_message (errbuf);
1018 RETURN;
1019 ELSE
1020 x_to_date := trunc(sysdate);
1021 x_from_date := x_to_date - p_collection_window + 1;
1022 END IF;
1023 END IF;
1024 END IF;
1025
1026 msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1027 msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1028
1029 /* Get the instance info */
1030 msd_dem_common_utilities.get_instance_info (
1031 x_errbuf1,
1032 x_retcode1,
1033 x_instance_code,
1034 x_apps_ver,
1035 x_dgmt,
1036 x_instance_type,
1037 p_sr_instance_id);
1038
1039 IF (x_retcode1 = '-1')
1040 THEN
1041 retcode := -1;
1042 errbuf := x_errbuf1;
1043 msd_dem_common_utilities.log_message ('Error(8): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1044 msd_dem_common_utilities.log_message ('Unable to get instance info.');
1048 msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1045 RETURN;
1046 END IF;
1047
1049
1050 /* Get the sales staging table name */
1051 x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
1052
1053 IF (x_dest_table is NULL)
1054 THEN
1055 retcode := -1;
1056 errbuf := 'Unable to find the sales staging tables.';
1057 msd_dem_common_utilities.log_message ('Error(9): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1058 msd_dem_common_utilities.log_message (errbuf);
1059 RETURN;
1060 END IF;
1061
1062 msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1063 msd_dem_common_utilities.log_message ('--------------------------------');
1064 msd_dem_common_utilities.log_message (' ');
1065
1066 msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1067 msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1068
1069 msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1070
1071 /* Truncate the sales staging table */
1072 msd_dem_common_utilities.log_message ('Deleting data from sales staging table - ' || x_dest_table);
1073
1074 if p_collection_method = 1 then
1075 x_sql := 'TRUNCATE TABLE ' || x_dest_table;
1076 else
1077 x_sql := 'DELETE FROM '|| x_dest_table || ' where sales_date between ''' || x_from_date || ''' AND ''' || x_to_date || '''';
1078 end if;
1079
1080 EXECUTE IMMEDIATE x_sql;
1081
1082 msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1083
1084 msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1085
1086 /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1087 msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1088 x_sql := 'TRUNCATE TABLE ' || x_dest_table ||'_err';
1089 EXECUTE IMMEDIATE x_sql;
1090
1091 msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1092 --saravan
1093 msd_dem_common_utilities.log_debug ('Begin get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1094
1095 OPEN c_get_dm_schema; --jarora
1096 FETCH c_get_dm_schema INTO g_schema;
1097 CLOSE c_get_dm_schema;
1098
1099 /* Get the lowest time bucket */
1100 /* Demantra is Installed */
1101 IF (g_schema IS NOT NULL) --jarora
1102 THEN
1103 x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
1104 ELSE
1105 x_dm_time_bucket := 'DAY';
1106 END IF;
1107
1108 IF (x_dm_time_bucket IS NULL)
1109 THEN
1110 retcode := -1;
1111 errbuf := 'Unable to get lowest time bucket';
1112 msd_dem_common_utilities.log_message ('Error(10): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1113 msd_dem_common_utilities.log_message (errbuf);
1114 RETURN;
1115 ELSIF (upper(x_dm_time_bucket) = 'DAY')
1116 THEN
1117 x_dm_time_level := 1;
1118 ELSE
1119 x_dm_time_level := 2;
1120 END IF;
1121
1122 msd_dem_common_utilities.log_debug ('End get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1123
1124 /* Collect each series selected by the user */
1125
1126 /* Booking History - Booked Items - Booked Date */
1127 msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1128 IF (p_bh_bi_bd = G_YES)
1129 THEN
1130 collect_series_data (
1131 x_errbuf1,
1132 x_retcode1,
1133 MSD_DEM_COMMON_UTILITIES.C_BH_BI_BD,
1134 x_dest_table,
1135 x_dm_time_level,
1136 p_sr_instance_id,
1137 x_apps_ver,
1138 x_instance_type,
1139 p_collection_group,
1140 p_collection_method,
1141 x_from_date,
1142 x_to_date,
1143 p_collect_iso,
1144 x_order_type_flag,
1145 x_order_type_ids);
1146
1147 IF (x_retcode1 = -1)
1148 THEN
1149 retcode := -1;
1150 errbuf := x_errbuf1;
1151 msd_dem_common_utilities.log_message ('Error(11): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1152 msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Booked Date');
1153 RETURN;
1154 END IF;
1155 END IF;
1156 msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1157
1158
1162 THEN
1159 /* Booking History - Booked Items - Requested Date */
1160 msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1161 IF (p_bh_bi_rd = G_YES)
1163 collect_series_data (
1164 x_errbuf1,
1165 x_retcode1,
1166 MSD_DEM_COMMON_UTILITIES.C_BH_BI_RD,
1167 x_dest_table,
1168 x_dm_time_level,
1169 p_sr_instance_id,
1170 x_apps_ver,
1171 x_instance_type,
1172 p_collection_group,
1173 p_collection_method,
1174 x_from_date,
1175 x_to_date,
1176 p_collect_iso,
1177 x_order_type_flag,
1178 x_order_type_ids);
1179
1180 IF (x_retcode1 = -1)
1181 THEN
1182 retcode := -1;
1183 errbuf := x_errbuf1;
1184 msd_dem_common_utilities.log_message ('Error(12): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1185 msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Requested Date');
1186 RETURN;
1187 END IF;
1188 END IF;
1189 msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1190
1191 /* Booking History - Requested Items - Booked Date */
1192 msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1193 IF (p_bh_ri_bd = G_YES)
1194 THEN
1195 collect_series_data (
1196 x_errbuf1,
1197 x_retcode1,
1198 MSD_DEM_COMMON_UTILITIES.C_BH_RI_BD,
1199 x_dest_table,
1200 x_dm_time_level,
1201 p_sr_instance_id,
1202 x_apps_ver,
1203 x_instance_type,
1204 p_collection_group,
1205 p_collection_method,
1206 x_from_date,
1207 x_to_date,
1208 p_collect_iso,
1209 x_order_type_flag,
1210 x_order_type_ids);
1211
1212 IF (x_retcode1 = -1)
1213 THEN
1214 retcode := -1;
1215 errbuf := x_errbuf1;
1216 msd_dem_common_utilities.log_message ('Error(13): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1217 msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Booked Date');
1218 RETURN;
1219 END IF;
1220 END IF;
1221 msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1222
1223 /* Booking History - Requested Items - Requested Date */
1224 msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1225 IF (p_bh_ri_rd = G_YES)
1226 THEN
1227 collect_series_data (
1228 x_errbuf1,
1229 x_retcode1,
1230 MSD_DEM_COMMON_UTILITIES.C_BH_RI_RD,
1231 x_dest_table,
1232 x_dm_time_level,
1233 p_sr_instance_id,
1234 x_apps_ver,
1235 x_instance_type,
1236 p_collection_group,
1237 p_collection_method,
1238 x_from_date,
1239 x_to_date,
1240 p_collect_iso,
1241 x_order_type_flag,
1242 x_order_type_ids);
1243
1244 IF (x_retcode1 = -1)
1245 THEN
1246 retcode := -1;
1247 errbuf := x_errbuf1;
1248 msd_dem_common_utilities.log_message ('Error(14): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1249 msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Requested Date');
1250 RETURN;
1251 END IF;
1252 END IF;
1253 msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1254
1255 /* Shipment History - Shipped Items - Shipped Date */
1256 msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1257 IF (p_sh_si_sd = G_YES)
1258 THEN
1259 collect_series_data (
1260 x_errbuf1,
1261 x_retcode1,
1262 MSD_DEM_COMMON_UTILITIES.C_SH_SI_SD,
1263 x_dest_table,
1264 x_dm_time_level,
1265 p_sr_instance_id,
1266 x_apps_ver,
1267 x_instance_type,
1268 p_collection_group,
1269 p_collection_method,
1270 x_from_date,
1271 x_to_date,
1272 p_collect_iso,
1273 x_order_type_flag,
1274 x_order_type_ids);
1275
1276 IF (x_retcode1 = -1)
1277 THEN
1278 retcode := -1;
1279 errbuf := x_errbuf1;
1283 END IF;
1280 msd_dem_common_utilities.log_message ('Error(15): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1281 msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Shipped Date');
1282 RETURN;
1284 END IF;
1285 msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1286
1287 /* Shipment History - Shipped Items - Requested Date */
1288 msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1289 IF (p_sh_si_rd = G_YES)
1290 THEN
1291 collect_series_data (
1292 x_errbuf1,
1293 x_retcode1,
1294 MSD_DEM_COMMON_UTILITIES.C_SH_SI_RD,
1295 x_dest_table,
1296 x_dm_time_level,
1297 p_sr_instance_id,
1298 x_apps_ver,
1299 x_instance_type,
1300 p_collection_group,
1301 p_collection_method,
1302 x_from_date,
1303 x_to_date,
1304 p_collect_iso,
1305 x_order_type_flag,
1306 x_order_type_ids);
1307
1308 IF (x_retcode1 = -1)
1309 THEN
1310 retcode := -1;
1311 errbuf := x_errbuf1;
1312 msd_dem_common_utilities.log_message ('Error(16): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1313 msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Requested Date');
1314 RETURN;
1315 END IF;
1316 END IF;
1317 msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1318
1319 /* Shipment History - Requested Items - Shipped Date */
1320 msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1321 IF (p_sh_ri_sd = G_YES)
1322 THEN
1323 collect_series_data (
1324 x_errbuf1,
1325 x_retcode1,
1326 MSD_DEM_COMMON_UTILITIES.C_SH_RI_SD,
1327 x_dest_table,
1328 x_dm_time_level,
1329 p_sr_instance_id,
1330 x_apps_ver,
1331 x_instance_type,
1332 p_collection_group,
1333 p_collection_method,
1334 x_from_date,
1335 x_to_date,
1336 p_collect_iso,
1337 x_order_type_flag,
1338 x_order_type_ids);
1339
1340 IF (x_retcode1 = -1)
1341 THEN
1342 retcode := -1;
1343 errbuf := x_errbuf1;
1344 msd_dem_common_utilities.log_message ('Error(17): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1348 END IF;
1345 msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Shipped Date');
1346 RETURN;
1347 END IF;
1349 msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1350
1351 /* Shipment History - Requested Items - Requested Date */
1352 msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1353 IF (p_sh_ri_rd = G_YES)
1354 THEN
1355 collect_series_data (
1356 x_errbuf1,
1357 x_retcode1,
1358 MSD_DEM_COMMON_UTILITIES.C_SH_RI_RD,
1359 x_dest_table,
1360 x_dm_time_level,
1361 p_sr_instance_id,
1362 x_apps_ver,
1363 x_instance_type,
1364 p_collection_group,
1365 p_collection_method,
1366 x_from_date,
1367 x_to_date,
1368 p_collect_iso,
1369 x_order_type_flag,
1370 x_order_type_ids);
1371
1372
1373
1374 IF (x_retcode1 = -1)
1375 THEN
1376 retcode := -1;
1377 errbuf := x_errbuf1;
1378 msd_dem_common_utilities.log_message ('Error(18): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1379 msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Requested Date');
1380 RETURN;
1381 END IF;
1382 END IF;
1383 msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1384
1385 /* Bug# 5869314 - Insert dummy rows in the staging table for new items */
1386 msd_dem_common_utilities.log_debug ('Begin Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1387 insert_dummy_rows (
1388 x_errbuf1,
1389 x_retcode1,
1390 x_dest_table,
1391 p_sr_instance_id);
1392
1393 IF (x_retcode1 = 1)
1394 THEN
1395 retcode := 1;
1396 errbuf := x_errbuf1;
1397 msd_dem_common_utilities.log_message ('Warning(6): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1398 msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
1399 END IF;
1400 msd_dem_common_utilities.log_debug ('End Insert dummy rows for new items into the staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1401
1402 COMMIT;
1403
1404 /* Call Custom Hook for History */
1405
1406 msd_dem_common_utilities.log_debug ('Begin Call Custom Hook msd_dem_custom_hooks.history_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1407
1408 msd_dem_custom_hooks.history_hook (
1409 x_errbuf1,
1410 x_retcode1);
1411
1412 msd_dem_common_utilities.log_debug ('End Call Custom Hook msd_dem_custom_hooks.history_hook - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1413
1414 IF (x_retcode1 = -1)
1415 THEN
1416 retcode := -1;
1417 errbuf := x_errbuf1;
1418 msd_dem_common_utilities.log_message ('Error(19): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1419 msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.history_hook ');
1420 RETURN;
1421 END IF;
1422
1423 /* Analyze Sales Staging Table */
1424
1425 msd_dem_common_utilities.log_debug ('Begin Analyze sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1426
1427 analyze_table (
1428 x_errbuf1,
1429 x_retcode1,
1430 x_dest_table);
1431
1432 msd_dem_common_utilities.log_debug ('End Analyze sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1433
1434 IF (x_retcode1 = 1)
1435 THEN
1436 retcode := 1;
1437 errbuf := x_errbuf1;
1438 msd_dem_common_utilities.log_message ('Warning(7): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1439 msd_dem_common_utilities.log_message ('Error while analyzing sales staging table. ');
1440 END IF;
1441
1442 /*
1443 *Order Realignment
1444 */
1445 Begin
1446 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1447
1448 if (g_schema is not null)
1449 then
1450
1451
1452 l_sql := 'select id, table_name, from_date, until_date from '|| g_schema || '.transfer_query where query_name = ''Purge History Data''';
1453 execute immediate l_sql into l_profile_id, l_table_name, l_start_date, l_until_date;
1454
1455 /* Refreshing the Purge Series Data profile to the default value ie No load and No Purge option */
1456 msd_dem_common_utilities.REFRESH_PURGE_SERIES(x_errbuf1, x_retcode1, l_profile_id, g_schema);
1457
1458 IF (x_retcode1 = -1)
1459 THEN
1460 retcode := -1;
1464 END IF;
1461 errbuf := x_errbuf1;
1462
1463 msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
1465
1466 /* Calling API to modify the data profile to purge selected series */
1467 msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_ATTR - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1468
1469
1470
1471 if p_bh_bi_bd = G_YES
1472 then
1473 l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_bh_book_qty_bd''';
1474 execute immediate l_sql into l_bh_bi_bd_id;
1475
1476 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_bh_bi_bd_id||', 0, 2); end;';
1477 execute immediate l_sql;
1478 end if;
1479
1480 if p_bh_bi_rd = G_YES
1481 then
1482 l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_bh_book_qty_rd''';
1483 execute immediate l_sql into l_bh_bi_rd_id;
1484
1485 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_bh_bi_rd_id||', 0, 2); end;';
1486 execute immediate l_sql;
1487 end if;
1488
1489 if p_bh_ri_bd = G_YES
1490 then
1491 l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_bh_req_qty_bd''';
1492 execute immediate l_sql into l_bh_ri_bd_id;
1493
1494 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_bh_ri_bd_id||', 0, 2); end;';
1495 execute immediate l_sql;
1496 end if;
1497
1498 if p_bh_ri_rd = G_YES
1499 then
1500 l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_bh_req_qty_rd''';
1501 execute immediate l_sql into l_bh_ri_rd_id;
1502
1503 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_bh_ri_rd_id||', 0, 2); end;';
1504 execute immediate l_sql;
1505 end if;
1506
1507 if p_sh_si_sd = G_YES
1508 then
1509 l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_sh_ship_qty_sd''';
1510 execute immediate l_sql into l_sh_si_sd_id;
1511
1512 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_sh_si_sd_id||', 0, 2); end;';
1513 execute immediate l_sql;
1514 end if;
1515
1516 if p_sh_si_rd = G_YES
1517 then
1518 l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_sh_ship_qty_rd''';
1519 execute immediate l_sql into l_sh_si_rd_id;
1520
1521 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_sh_si_rd_id||', 0, 2); end;';
1522 execute immediate l_sql;
1523 end if;
1524
1525 if p_sh_ri_sd = G_YES
1526 then
1527 l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''sales''';
1528 execute immediate l_sql into l_sh_ri_sd_id;
1529
1530 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_sh_ri_sd_id||', 0, 2); end;';
1531 execute immediate l_sql;
1532 end if;
1533
1534 if p_sh_ri_rd = G_YES
1535 then
1536 l_sql := 'select forecast_type_id from '|| g_schema || '.computed_fields where computed_name = ''ebs_sh_req_qty_rd''';
1537 execute immediate l_sql into l_sh_ri_rd_id;
1538
1539 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_sh_ri_rd_id||', 0, 2); end;';
1540 execute immediate l_sql;
1541 end if;
1542
1543
1544
1545 /* Calling API to modify the data profile date range */
1546 msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_FDATE - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1547
1548 l_sql := 'select datet from '|| g_schema ||'.inputs where datet >= '''||x_from_date||''' and rownum = 1 order by datet asc';
1549 execute immediate l_sql into x_from_date;
1550
1551 l_sql := 'select datet from '|| g_schema ||'.inputs where datet <= '''||x_to_date||''' and rownum = 1 order by datet desc';
1552 execute immediate l_sql into x_to_date;
1553
1554 if (x_from_date > x_to_date) then
1555 x_to_date := x_from_date;
1556 end if;
1557
1558 msd_dem_common_utilities.log_message ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
1559
1560 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id||', '''|| x_from_date||''' , '''||x_to_date||'''); end;';
1561 execute immediate l_sql;
1562
1563
1564 /* Calling API to notify the application server to refresh its engine */
1568 execute immediate l_sql;
1565 msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1566
1567 l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id ||'); end;';
1569
1570 l_sql := 'truncate table '|| g_schema ||'.'||l_table_name ;
1571 execute immediate l_sql;
1572
1573 l_sql := 'insert into '|| g_schema ||'.'||l_table_name||'(sdate, level1)'||
1574 'select '''||x_from_date||''', teo.organization from '||g_schema||'.t_ep_organization teo '||
1575 'where teo.organization in
1576 (SELECT mtp.organization_code
1577 FROM msc_instance_orgs mio,
1578 msc_trading_partners mtp
1579 WHERE mio.sr_instance_id = '||p_sr_instance_id||
1580 ' AND nvl(mio.org_group, ''-888'') = decode('''||p_collection_group||''', ''-999'', nvl(mio.org_group, ''-888''), '''||p_collection_group||''')'||
1581 ' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 '||
1582 ' AND mtp.sr_tp_id = mio.organization_id '||
1583 ' AND mtp.partner_type = 3) ';
1584
1585 execute immediate l_sql;
1586
1587 else
1588 msd_dem_common_utilities.log_message('Demantra Schema not set');
1589 end if;
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 retcode := 1 ;
1593 errbuf := substr(SQLERRM,1,150);
1594 msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1595 msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1596 msd_dem_common_utilities.log_debug (errbuf);
1597 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1598
1599 RETURN;
1600 End;
1601
1602 retcode := x_retcode;
1603 errbuf := x_errbuf;
1604 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1605
1606 EXCEPTION
1607 WHEN OTHERS THEN
1608 retcode := -1 ;
1609 errbuf := substr(SQLERRM,1,150);
1610 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1611 msd_dem_common_utilities.log_message (errbuf);
1612 RETURN;
1613
1614 END COLLECT_HISTORY_DATA;
1615
1616 PROCEDURE RUN_LOAD (
1617 errbuf OUT NOCOPY VARCHAR2,
1618 retcode OUT NOCOPY VARCHAR2,
1619 p_auto_run_download IN NUMBER )
1620 IS
1621
1622 l_sql varchar2(1000);
1623 DEM_SCHEMA varchar2(100);
1624 l_url varchar2(1000);
1625 l_dummy varchar2(100);
1626 l_user_id number;
1627 l_user_name varchar2(30);
1628 l_password varchar2(30);
1629
1630
1631
1632
1633 BEGIN
1634 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1635
1636 DEM_SCHEMA := fnd_profile.value('MSD_DEM_SCHEMA');
1637
1638 IF (p_auto_run_download = G_YES)
1639 THEN
1640 if fnd_profile.value('MSD_DEM_SCHEMA') is not null then
1641
1642
1643
1644 /*
1645
1646 l_stmt := 'alter session set current_schema=' || DEM_SCHEMA;
1647 execute immediate l_stmt;
1648
1649 msd_dem_common_utilities.log_message ('Begin - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1650 msd_dem_common_utilities.log_message ('Please check the *_ERR tables for any errors during Data Load');
1651
1652 msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1653 l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1654 execute immediate l_stmt;
1655
1656 msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_ITEMS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1657 l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_ITEMS; end;';
1658 execute immediate l_stmt;
1659
1660 msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_LOCATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1661 l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_LOCATION; end;';
1662 execute immediate l_stmt;
1663
1664 msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_SALES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1665 l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_SALES; end;';
1666
1667 execute immediate l_stmt;
1668
1669 msd_dem_common_utilities.log_message ('End - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1670
1671 commit;
1672
1673 l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.LOG_EP_LOAD_SUCCESS; end;';
1674 execute immediate l_stmt;
1675
1676 l_stmt := 'alter session set current_schema=APPS';
1677 execute immediate l_stmt;
1681
1678
1679 */
1680
1682 l_sql := 'select user_id from ' ||msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT') || '''';
1683 execute immediate l_sql into l_user_id;
1684
1685 IF l_user_id is not null
1686 then
1687 l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
1688 execute immediate l_sql into l_user_name, l_password;
1689
1690 ELSE
1691 l_sql := 'select user_id from ' ||msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'SOP') || '''';
1692 execute immediate l_sql into l_user_id;
1693
1694 If l_user_id is not null
1695 then
1696 l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
1697 execute immediate l_sql into l_user_name, l_password;
1698 else
1699 msd_dem_common_utilities.log_message('Component is not found.');
1700 end if;
1701 END IF;
1702
1703
1704 if l_user_name is not null
1705 then
1706 l_url := fnd_profile.value('MSD_DEM_HOST_URL');
1707
1708 l_sql := 'SELECT
1709 utl_http.request('''||l_url||'/WorkflowServer?action=run_proc&user='||l_user_name||'&password='||l_password||'&schema=EBS%20Full%20Download&sync=no'') FROM dual';
1710 execute immediate l_sql into l_dummy;
1711 else
1712 msd_dem_common_utilities.log_message('Error in launching the download workflow.');
1713 retcode := -1;
1714 Return;
1715 end if;
1716
1717 else
1718 msd_dem_common_utilities.log_message('Demantra Schema not set');
1719 end if;
1720 ELSE
1721 msd_dem_common_utilities.log_message ('Auto Run Download - No ');
1722 msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
1723 END IF;
1724
1725 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1726
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 errbuf := substr(SQLERRM,1,150);
1730 retcode := -1 ;
1731 -- l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.LOG_EP_LOAD_FAILURE; end;';
1732 -- execute immediate l_stmt;
1733 -- l_stmt := 'alter session set current_schema=APPS';
1734 -- execute immediate l_stmt;
1735
1736 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1737 msd_dem_common_utilities.log_message (errbuf);
1738 RETURN;
1739
1740 END RUN_LOAD;
1741
1742 END MSD_DEM_COLLECT_HISTORY_DATA;