[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.16.12020000.8 2013/02/12 10:08:08 kkhatri ship $ */
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_sr_instance_id IN NUMBER,
47 p_collect_all_order_types IN NUMBER,
48 p_type_selection_method IN NUMBER,
49 p_include_order_types IN VARCHAR2,
50 p_exclude_order_types IN VARCHAR2)
51 RETURN NUMBER
52 IS
53
54 l_order_type_table ORDER_TYPE_TABLE_TYPE;
55 l_order_category_code_table ORDER_TYPE_TABLE_TYPE;
56 l_order_type_id_table ORDER_TYPE_ID_TABLE_TYPE;
57 l_valid_order_type_table ORDER_TYPE_TABLE_TYPE;
58 l_invalid_order_type_table ORDER_TYPE_TABLE_TYPE;
59
60 l_sql_stmt VARCHAR2(2000);
61 l_order_types VARCHAR2(2000);
62 l_original_order_types VARCHAR2(2000);
63 l_order_type_ids VARCHAR2(2000);
64 l_token VARCHAR2(100);
65 l_original_token VARCHAR2(100);
66
67 l_order_type_flag NUMBER;
68 l_start NUMBER := 1;
69 l_position NUMBER := -1;
70 l_valid_count NUMBER := 0;
71 l_invalid_count NUMBER := 0;
72
73 l_found BOOLEAN;
74 x_retcode VARCHAR2(100) := NULL;
75
76 BEGIN
77
78 /* If collect all order types is yes, then ignore other fields */
79 IF (p_collect_all_order_types = G_YES)
80 THEN
81
82 IF ( p_include_order_types IS NOT NULL
83 OR p_exclude_order_types IS NOT NULL)
84 THEN
85 retcode := 1;
86 errbuf := 'The parameters Include Order Types and Exclude Order Types are ignored, if Collect All Order Types is Yes.';
87 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'));
88 msd_dem_common_utilities.log_message (errbuf);
89 END IF;
90
91 p_order_type_flag := C_ALL;
92 p_order_type_ids := '';
93 RETURN 0;
94
95 END IF;
96
97
98
99 IF (p_collect_all_order_types = G_NO)
100 THEN
101
102 IF ( p_include_order_types IS NULL
103 AND p_exclude_order_types IS NULL)
104 THEN
105 retcode := -1;
106 errbuf := 'Exactly one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
107 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'));
108 msd_dem_common_utilities.log_message (errbuf);
109 RETURN -1;
110 ELSIF ( p_include_order_types IS NOT NULL
111 AND p_exclude_order_types IS NOT NULL)
112 THEN
113 retcode := -1;
114 errbuf := 'Only one of the parameters Include Order Types or Exclude Order Types must be specified, when Collect All Order Types is No.';
115 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'));
116 msd_dem_common_utilities.log_message (errbuf);
117 RETURN -1;
118 ELSIF (p_include_order_types IS NOT NULL)
119 THEN
120 l_order_type_flag := C_INCLUDE;
121 l_order_types := UPPER(p_include_order_types);
122 l_original_order_types := p_include_order_types;
123 ELSE
124 l_order_type_flag := C_EXCLUDE;
125 l_order_types := UPPER(p_exclude_order_types);
126 l_original_order_types := p_exclude_order_types;
127 END IF;
128 --Bug 14689626 --kkhatri--12.3
129 --BUG 13943119--kkhatri--12.2.1
130 IF (p_type_selection_method = 1) THEN /*Comma(,) separated values*/
131
132 /* Get all the valid order types from the source*/
133 l_sql_stmt := 'SELECT ' ||
134 'B.TRANSACTION_TYPE_ID ORDER_TYPE_ID, ' ||
135 'UPPER(B.ORDER_CATEGORY_CODE) ORDER_CATEGORY_CODE, ' ||
136 'UPPER(T.NAME) NAME ' ||
137 'FROM ' ||
138 'OE_TRANSACTION_TYPES_TL' || g_dblink || ' T, ' ||
139 'OE_TRANSACTION_TYPES_ALL' || g_dblink || ' B '||
140 'WHERE ' ||
141 'B.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID AND ' ||
142 'B.Transaction_type_code = ''ORDER'' AND ' ||
143 'nvl(B.SALES_DOCUMENT_TYPE_CODE,''O'') <> ''B'' AND ' ||
144 'T.LANGUAGE = userenv(''LANG'') ';
145
146 EXECUTE IMMEDIATE l_sql_stmt
147 BULK COLLECT INTO l_order_type_id_table,
148 l_order_category_code_table,
149 l_order_type_table;
150 IF (l_order_type_table.COUNT = 0)
151 THEN
152 retcode := -1;
153 errbuf := 'No order types found in the source';
154 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'));
155 msd_dem_common_utilities.log_message (errbuf);
156 RETURN -1;
157 END IF;
158
159
160 l_valid_order_type_table := ORDER_TYPE_TABLE_TYPE();
161 l_invalid_order_type_table := ORDER_TYPE_TABLE_TYPE();
162
163 /* Get the valid and invalid order types given by the user */
164 LOOP
165
166 l_position := INSTR( l_order_types, ',', l_start, 1);
167
168 /* Get the token (order type)*/
169 IF (l_position <> 0)
170 THEN
171 l_token := SUBSTR( l_order_types, l_start, l_position - l_start);
172 l_original_token := SUBSTR( l_original_order_types, l_start, l_position - l_start);
173 ELSE
174 l_token := SUBSTR( l_order_types, l_start);
175 l_original_token := SUBSTR( l_original_order_types, l_start);
176 END IF;
177
178 /* Validate the order type*/
179 l_found := FALSE;
180 FOR i IN l_order_type_table.FIRST..l_order_type_table.LAST
181 LOOP
182
183 /* Valid order type */
184 IF ( l_order_category_code_table(i) <> 'RETURN'
185 AND l_token = l_order_type_table(i))
186 THEN
187
188 l_found := TRUE;
189 l_valid_count := l_valid_count + 1;
190 l_valid_order_type_table.EXTEND;
191 l_valid_order_type_table(l_valid_count) := l_original_token;
192
193 IF (l_valid_count = 1)
194 THEN
195 l_order_type_ids := l_order_type_ids || to_char(l_order_type_id_table(i));
196 ELSE
197 l_order_type_ids := l_order_type_ids || ',' || to_char(l_order_type_id_table(i));
198 END IF;
199
200 EXIT;
201
202 /* Invalid order type since order category code is 'RETURN' */
203 ELSIF ( l_order_category_code_table(i) = 'RETURN'
204 AND l_token = l_order_type_table(i))
205 THEN
206
207 l_found := TRUE;
208 l_invalid_count := l_invalid_count + 1;
209 l_invalid_order_type_table.EXTEND;
210 l_invalid_order_type_table(l_invalid_count) := l_original_token || ' (Order Type is RETURN)';
211
212 EXIT;
213
214 END IF;
215
216 END LOOP;
217
218 /* Invalid order type */
219 IF (l_found = FALSE)
220 THEN
221 l_invalid_count := l_invalid_count + 1;
222 l_invalid_order_type_table.EXTEND;
223 l_invalid_order_type_table(l_invalid_count) := l_original_token;
224 END IF;
225
226 EXIT WHEN l_position = 0;
227 l_start := l_position + 1;
228
229 END LOOP;
230
231 msd_dem_common_utilities.log_message (' Order Types');
232 msd_dem_common_utilities.log_message (' -------------');
233
234 msd_dem_common_utilities.log_message (' Valid Order Types');
235 msd_dem_common_utilities.log_message (' -------------------');
236
237 IF (l_valid_count <> 0)
238 THEN
239 FOR i in l_valid_order_type_table.FIRST..l_valid_order_type_table.LAST
240 LOOP
241 msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_valid_order_type_table(i));
242 END LOOP;
243 ELSE
244 msd_dem_common_utilities.log_message ('No valid order types found in user input');
245 END IF;
246
247 msd_dem_common_utilities.log_message (' ');
248 msd_dem_common_utilities.log_message (' Invalid Order Types');
249 msd_dem_common_utilities.log_message (' ---------------------');
250
251 IF (l_invalid_count <> 0)
252 THEN
253 FOR i in l_invalid_order_type_table.FIRST..l_invalid_order_type_table.LAST
254 LOOP
255 msd_dem_common_utilities.log_message (to_char(i) || ') ' || l_invalid_order_type_table(i));
256 END LOOP;
257 END IF;
258
259 IF (l_valid_count = 0)
260 THEN
261 retcode := -1;
262 errbuf := 'No valid order types found in user input';
263 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'));
264 RETURN -1;
265 END IF;
266
267 IF (l_invalid_count <> 0)
268 THEN
269 retcode := 1;
270 errbuf := 'Invalid order types found in user input';
271 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'));
272 END IF;
273
274
275 p_order_type_flag := l_order_type_flag;
276 p_order_type_ids := l_order_type_ids;
277 RETURN l_invalid_count;
278
279 --Bug 14689626--kkhatri--12.3
280 --BUG 13943119--kkhatri--12.2.1
281 ELSIF (p_type_selection_method = 2) THEN /* Entity Name Sql stmt*/
282
283 p_order_type_flag := l_order_type_flag;
284 l_order_type_ids := l_order_types;
285 /*Get the sql query */
286 msd_dem_query_utilities.get_query(
287 x_retcode ,
288 p_order_type_ids, --query
289 l_order_type_ids, --p_entity_name
290 p_sr_instance_id );
291
292 msd_dem_common_utilities.log_debug('SQL statement used for selecting Order Types is :- ' || p_order_type_ids );
293
294 If(p_order_type_ids is null) then
295 msd_dem_common_utilities.log_message(l_order_type_ids || ' entity_name NOT found. Please specify the correct Entity_Name.');
296 retcode := -1;
297 End if;
298
299 l_invalid_count := 0 ;
300 RETURN l_invalid_count ;
301
302 ELSIF (p_type_selection_method = 3) THEN /*Valueset*/
303
304 p_order_type_flag := l_order_type_flag;
305 l_order_type_ids := 'SELECT ' ||
306 'B.TRANSACTION_TYPE_ID ORDER_TYPE_ID ' ||
307 'FROM ' ||
308 'OE_TRANSACTION_TYPES_TL T, ' ||
309 'OE_TRANSACTION_TYPES_ALL B, '||
310 'FND_FLEX_VALUES FV, ' ||
311 'FND_FLEX_VALUES_TL FVTL, ' ||
312 'FND_FLEX_VALUE_SETS FVS '||
313 'WHERE ' ||
314 'B.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID AND ' ||
315 'B.Transaction_type_code = ''ORDER'' AND ' ||
316 'nvl(B.SALES_DOCUMENT_TYPE_CODE,''O'') <> ''B'' AND ' ||
317 'T.LANGUAGE = userenv(''LANG'') AND ' ||
318 'fvtl.LANGUAGE = userenv(''LANG'') AND ' ||
319 'FV.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID AND ' ||
320 'FVS.FLEX_VALUE_SET_NAME = ''' || l_order_types ||''' '||
321 'AND UPPER(T.NAME) = UPPER(FVTL.FLEX_VALUE_MEANING) AND ' ||
322 'fvtl.flex_value_id = fv.flex_value_id and ' ||
323 'fv.enabled_flag = ''Y'' ';
324
325 p_order_type_ids := l_order_type_ids;
326 msd_dem_common_utilities.log_debug('ValueSet used for selecting Order Types is :- ' || l_order_types );
327 l_invalid_count := 0 ;
328 RETURN l_invalid_count ;
329
330 END IF;
331 END IF;
332
333 END VALIDATE_ORDER_TYPES;
334
335
336
337 /*** PRIVATE PROCEDURES ***/
338
339 /* THIS PROCEDURE DELETES THE INTERNAL SALES ODERS IN THE SAME LINE OF BUSINESS */
340
341 PROCEDURE DELETE_INTERNAL_SALES_ORDERS(
342 errbuf OUT NOCOPY VARCHAR2,
343 retcode OUT NOCOPY VARCHAR2,
344 p_instance_id IN NUMBER )
345 IS
346
347 delete_sql varchar2(1000);
348 x_dest_table varchar2(300);
349 x_dem_schema varchar2(100);
350
351 CURSOR c_get_dm_schema
352 IS
353 SELECT owner
354 FROM dba_objects
355 WHERE owner = owner
356 AND object_type = 'TABLE'
357 AND object_name = 'MDP_MATRIX'
358 ORDER BY created desc;
359
360 BEGIN
361
362 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
363
364 x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE');
365
366 open c_get_dm_schema;
367 fetch c_get_dm_schema into x_dem_schema;
368 close c_get_dm_schema;
369
370 if( x_dem_schema is not null) then
371 x_dem_schema := fnd_profile.value('MSD_DEM_SCHEMA');
372 end if;
373
374 if( x_dem_schema is not null) then
375 delete_sql := 'DELETE FROM ' || x_dest_table || ' sales '
376 || ' WHERE EXISTS '
377 || ' (SELECT 1 '
378 || ' FROM msc_location_associations mla, '
379 || ' msc_tp_site_id_lid mtsil, '
380 || ' msc_trading_partners orgs, '
381 || x_dem_schema || '.t_ep_organization orgs1, '
382 || x_dem_schema || '.t_ep_organization orgs2 '
383 || ' WHERE sales.ebs_site_sr_pk = mtsil.sr_tp_site_id '
384 || ' AND sales.dm_org_code = orgs1.organization '
385 || ' AND mla.partner_site_id = mtsil.tp_site_id '
386 || ' AND mla.sr_instance_id = :instance_id '
387 || ' AND mla.sr_instance_id = mtsil.sr_instance_id '
388 || ' AND mla.sr_instance_id = orgs.sr_instance_id '
389 || ' AND mla.organization_id = orgs.sr_tp_id '
390 || ' AND orgs.partner_type = 3 '
391 || ' AND orgs.organization_code = orgs2.organization '
392 || ' AND orgs1.t_ep_lob_id = orgs2.t_ep_lob_id '
393 || ' AND orgs1.t_ep_lob_id > 0)';
394
395 msd_dem_common_utilities.log_debug (delete_sql);
396 execute immediate delete_sql using p_instance_id;
397 commit;
398 else
399 msd_dem_common_utilities.log_message('Demantra not installed. Not deleting Internal Sales Orders.');
400 msd_dem_common_utilities.log_debug('Demantra not installed. Not deleting Internal Sales Orders.');
401 end if;
402
403 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
404
405 retcode := 1;
406 return;
407
408 EXCEPTION
409 WHEN OTHERS THEN
410 errbuf := substr(SQLERRM,1,150);
411 retcode := -1 ;
412 msd_dem_common_utilities.log_debug ('Exception:
413 msd_dem_collect_history_data.delete_internal_sales_orders - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
414 msd_dem_common_utilities.log_debug (errbuf);
415 RETURN;
416
417
418 END DELETE_INTERNAL_SALES_ORDERS;
419
420
421
422 /*
423 * This procedure given the series id, gets the
424 * data from the source instance and upserts into the
425 * sales staging table.
426 */
427 PROCEDURE COLLECT_SERIES_DATA (
428 errbuf OUT NOCOPY VARCHAR2,
429 retcode OUT NOCOPY VARCHAR2,
430 p_series_id IN NUMBER,
431 p_dest_table IN VARCHAR2,
432 p_dm_time_level IN NUMBER,
433 p_sr_instance_id IN NUMBER,
434 p_apps_ver IN NUMBER,
435 p_instance_type IN NUMBER,
436 p_collection_group IN VARCHAR2,
437 p_collection_method IN NUMBER,
438 p_from_date IN DATE,
439 p_to_date IN DATE,
440 p_collect_iso IN NUMBER,
441 p_order_type_flag IN NUMBER,
442 p_order_type_ids IN VARCHAR2)
443 IS
444
445 /*** CURSORS ***/
446
447 CURSOR c_get_series_info
448 IS
449 SELECT
450 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,
451 SOURCE_VIEW_HINT,SOURCE_VIEW_HINT2, EXTRA_WHERE
452 FROM
453 msd_dem_series
454 WHERE
455 series_id = p_series_id
456 AND series_type = 1;
457
458 /*** LOCAL VARIABLES ***/
459 x_errbuf VARCHAR2(200) := NULL;
460 x_errbuf1 VARCHAR2(200) := NULL;
461 x_retcode VARCHAR2(100) := NULL;
462 x_retcode1 VARCHAR2(100) := NULL;
463 XDBLINK VARCHAR2(100) := NULL;
464 x_profile_val NUMBER := NULL;
465 x_large_sql VARCHAR2(32000) := NULL;
466 x_add_where_clause VARCHAR2(3000) := NULL;
467 x_key_values VARCHAR2(4000) := NULL;
468 x_is_custom NUMBER := NULL;
469 x_gmp_is_custom NUMBER := NULL;
470
471 x_dquery_identifier VARCHAR2(30) := NULL;
472 x_pquery_identifier VARCHAR2(30) := NULL;
473
474 l_identifier varchar2(30) := NULL;
475 l_STG_SERIES_COL_NAME varchar2(100) := NULL;
476 l_MSD_SR_ITEM_PK_COL varchar2(500) := NULL;
477 l_MSD_SOURCE_DATE_COL varchar2(500) := NULL;
478 l_GMP_SR_ITEM_PK_COL varchar2(500) := NULL;
479 l_GMP_SOURCE_DATE_COL varchar2(500) := NULL;
480 l_custom_view_name varchar2(100) := NULL;
481 l_gmp_custom_view_name varchar2(100) := NULL;
482 l_source_view_hint varchar2(255) := NULL;
483 l_source_view_hint2 varchar2(255) := NULL;
484 l_extra_where varchar2(500) := NULL;
485
486 x_dem_version VARCHAR2(10) := msd_dem_common_utilities.get_demantra_version;
487
488
489 BEGIN
490
491 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
492
493 OPEN c_get_series_info;
494 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,
495 l_source_view_hint,l_source_view_hint2,l_extra_where;
496 CLOSE c_get_series_info;
497
498 IF (l_identifier IS NULL)
499 THEN
500 retcode := -1;
501 errbuf := 'Unable to get the query identifier.';
502 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'));
503 msd_dem_common_utilities.log_message (errbuf);
504 RETURN;
505 END IF;
506
507 /* Check if custom view for Discrete */
508 IF (l_custom_view_name IS NULL)
509 THEN
510 x_is_custom := 0;
511 ELSE
512 x_is_custom := 1;
513 END IF;
514
515 /* Check if custom view for Process */
516 IF (l_gmp_custom_view_name IS NULL)
517 THEN
518 x_gmp_is_custom := 0;
519 ELSE
520 x_gmp_is_custom := 1;
521 END IF;
522
523 /* For Discrete */
524 /* 11i instance where instance type in not 'PROCESS' OR R12 Instance of any type */
525 IF ( p_instance_type <> 2
526 OR p_apps_ver = 4)
527 THEN
528
529 msd_dem_common_utilities.log_debug ('Begin collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
530
531 x_add_where_clause := ' 1 = 1 ';
532
533 /* If p_collect_iso = No, then include an additional condition to filter out Internal Sales Orders */
534 IF (p_collect_ISO = G_NO)
535 THEN
536 x_add_where_clause := x_add_where_clause || ' AND nvl(ooha.order_source_id, 0) <> 10 ';
537 ELSIF (p_collect_ISO = G_YES) THEN -- BUG#13716734 --kkhatri--12.3--BUG#14683310 --kkhatri--12.2.1
538
539 MSD_DEM_COMMON_UTILITIES.GET_DBLINK(ERRBUF,RETCODE,P_SR_INSTANCE_ID,XDBLINK);
540 execute immediate 'select fnd_profile.value' || XDBLINK ||'(''MSC_SUBINVENTORY_PART_CONDITION'') from dual'
541 into x_profile_val;
542
543 IF (x_profile_val is not null) THEN
544 x_add_where_clause := x_add_where_clause
545 || ' AND 1 = DECODE(NVL(ooha.order_source_id, 0)'
546 || ' ,10, msd_dem_sr_util.get_sub_inv_type(oola.line_id,''msi.attribute' || x_profile_val || ''')'
547 || ' ,1)' ;
548 END IF;
549 END IF;
550
551 /* Include an additional condition to filter data based on order types specified by the user */
552 IF (p_order_type_flag = C_INCLUDE)
553 THEN
554 x_add_where_clause := x_add_where_clause || ' AND ooha.order_type_id IN (' || p_order_type_ids || ') ';
555 ELSIF (p_order_type_flag = C_EXCLUDE)
556 THEN
557 x_add_where_clause := x_add_where_clause || ' AND ooha.order_type_id NOT IN (' || p_order_type_ids || ') ';
558 END IF;
559 IF (l_extra_where IS NOT NULL)
560 THEN
561 x_add_where_clause := x_add_where_clause || ' ' || l_extra_where || ' ';
562 END IF;
563
564
565 IF(p_dm_time_level = 1) then
566
567 x_key_values := '$C_DEST_TABLE#' || p_dest_table
568 || '$C_SERIES_QTY#' || l_STG_SERIES_COL_NAME
569 || '$C_DEST_DATE_GROUP#' || 'MDBR.SDATE'
570 || '$C_DEST_DATE#' || 'MDBR.SDATE'
571 || '$C_VIEW_HINT2#' || nvl(l_source_view_hint2, ' ')
572 || '$C_VIEW_HINT#' || nvl(l_source_view_hint, ' ')
573 || '$C_SR_ITEM_PK#' || l_MSD_SR_ITEM_PK_COL
574 || '$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'
575 || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
576 || '$C_ITEM_PK_JOIN#' || substr(l_MSD_SR_ITEM_PK_COL, 0, instr(upper(l_MSD_SR_ITEM_PK_COL), 'SR_ITEM_PK')-1)
577 || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
578 || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
579
580 IF (g_collection_method <> 1) THEN
581 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' WHERE SDATE BETWEEN '
582 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
583 || ' AND '
584 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') ';
585 ELSE
586 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' ';
587 END IF;
588
589 --x_key_values := x_key_values || '$';
590
591 ELSE
592
593 x_key_values := '$C_DEST_TABLE#' || p_dest_table
594 || '$C_SERIES_QTY#' || l_STG_SERIES_COL_NAME
595 || '$C_DEST_DATE_GROUP#' || 'INP.DATET'
596 || '$C_DEST_DATE#' || 'INP.DATET SDATE'
597 || '$C_VIEW_HINT2#' || nvl(l_source_view_hint2, ' ')
598 || '$C_VIEW_HINT#' || nvl(l_source_view_hint, ' ')
599 || '$C_SR_ITEM_PK#' || l_MSD_SR_ITEM_PK_COL
600 || '$C_SOURCE_DATE#' || SUBSTR(L_MSD_SOURCE_DATE_COL, 1, instr(L_MSD_SOURCE_DATE_COL, 'SDATE')-1) ||' PDATE'
601 || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
602 || '$C_ITEM_PK_JOIN#' || substr(l_MSD_SR_ITEM_PK_COL, 0, instr(upper(l_MSD_SR_ITEM_PK_COL), 'SR_ITEM_PK')-1)
603 || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
604 || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
605
606 IF (g_collection_method <> 1) THEN
607 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN '
608 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
609 || ' AND '
610 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
611 || ' AND MDBR.PDATE BETWEEN inp.start_date AND inp.end_date ';
612 ELSE
613 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN inp.start_date AND inp.end_date ';
614 END IF;
615
616 --x_key_values := x_key_values || '$';
617
618 END IF;
619
620 -- syenamar
621 /* Bug#7673154
622 * In case custom view is used for shipment booking history collection and net change collection method is specified
623 * its not possible for the custom view to filter out sales records for unwanted dates, as the existing view is used and not built dynamically.
624 * Adding a time clause to the merge query in this case to bring in data for the specified date range.
625 */
626 if( x_is_custom = 1 and g_collection_method <> 1) then
627 x_key_values := x_key_values || '$C_MERGE_TIME_CLAUSE#' || ' AND SDATE BETWEEN '
628 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
629 || ' AND '
630 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') ';
631 else
632 x_key_values := x_key_values || '$C_MERGE_TIME_CLAUSE#' || '';
633 end if;
634
635 x_key_values := x_key_values || '$';
636
637 -- bug 9341065 nallkuma
638 IF (x_dem_version = '7.2')
639 THEN
640 x_dquery_identifier := l_identifier;
641 ELSE
642 x_dquery_identifier := l_identifier || '_730';
643 END IF;
644
645 /* Get the query */
646 msd_dem_query_utilities.get_query2 (
647 x_retcode1,
648 x_large_sql,
649 x_dquery_identifier,
650 p_sr_instance_id,
651 x_key_values,
652 x_is_custom,
653 l_custom_view_name);
654
655 IF ( x_retcode1 = '-1'
656 OR x_large_sql IS NULL)
657 THEN
658 retcode := -1;
659 errbuf := 'Unable to get the query.';
660 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'));
661 msd_dem_common_utilities.log_message (errbuf);
662 RETURN;
663 END IF;
664
665
666 msd_dem_common_utilities.log_debug ('Query - ');
667 msd_dem_common_utilities.log_debug (x_large_sql);
668
669 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
670
671 BEGIN
672 /* Upsert history data into sales staging table */
673 EXECUTE IMMEDIATE x_large_sql;
674 COMMIT;
675 EXCEPTION
676 WHEN OTHERS THEN
677 retcode := -1 ;
678 errbuf := substr(SQLERRM,1,150);
679 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'));
680 msd_dem_common_utilities.log_message (errbuf);
681 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
682 RETURN;
683 END;
684
685 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
686
687 msd_dem_common_utilities.log_debug ('End collect discrete sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
688
689 END IF;
690
691 x_large_sql := NULL;
692 x_key_values := NULL;
693
694 /* For Process */
695 IF ( p_instance_type IN (2, 4)
696 AND p_apps_ver = 3)
697 THEN
698
699 msd_dem_common_utilities.log_debug ('Begin collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
700
701 x_pquery_identifier := replace(l_identifier , 'MSD','GMP') ;
702
703 x_add_where_clause := ' 1 = 1 ';
704
705 /* If p_collect_iso = No, then include an additional condition to filter out Internal Sales Orders */
706 IF (p_collect_ISO = G_NO)
707 THEN
708 x_add_where_clause := x_add_where_clause || ' AND decode(ool.to_whse, NULL, 10, 0) <> 10 ';
709 -- BUG#13716734 kkhatri 12.3 BUG#14683310 --kkhatri --12.2.1
710 ELSIF (p_collect_ISO = G_YES) THEN
711 MSD_DEM_COMMON_UTILITIES.GET_DBLINK(ERRBUF,RETCODE,P_SR_INSTANCE_ID,XDBLINK);
712 execute immediate 'select fnd_profile.value' || XDBLINK ||'(''MSC_SUBINVENTORY_PART_CONDITION'') from dual'
713 into x_profile_val;
714
715 IF (x_profile_val is not null) THEN
716
717 x_add_where_clause := x_add_where_clause
718 || ' AND 1 = DECODE(ool.to_whse '
719 || ' ,NULL, msd_dem_sr_util.get_sub_inv_type(ool.line_id,''MSI.attribute' || x_profile_val || ''')'
720 || ' ,1)' ;
721 END IF;
722 END IF;
723
724 /*** ORDER TYPES Filters are not supported for process sales data ***/
725
726
727 IF(p_dm_time_level = 1) then
728
729 x_key_values := '$C_DEST_TABLE#' || p_dest_table
730 || '$C_SERIES_QTY#' || l_STG_SERIES_COL_NAME
731 || '$C_DEST_DATE_GROUP#' || 'MDBR.SDATE'
732 || '$C_DEST_DATE#' || 'MDBR.SDATE'
733 || '$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'
734 || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause;
735
736 IF (g_collection_method <> 1) THEN
737 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' WHERE SDATE BETWEEN '
738 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
739 || ' AND '
740 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') ';
741 ELSE
742 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' ';
743 END IF;
744
745 x_key_values := x_key_values || '$';
746
747 ELSE
748
749 x_key_values := '$C_DEST_TABLE#' || p_dest_table
750 || '$C_SERIES_QTY#' || l_STG_SERIES_COL_NAME
751 || '$C_DEST_DATE_GROUP#' || 'INP.DATET'
752 || '$C_DEST_DATE#' || 'INP.DATET SDATE'
753 || '$C_SOURCE_DATE#' || SUBSTR(L_GMP_SOURCE_DATE_COL, 1, instr(L_GMP_SOURCE_DATE_COL, 'SDATE')-1)||' PDATE'
754 || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause;
755
756 IF (g_collection_method <> 1) THEN
757 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN '
758 || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
759 || ' AND '
760 || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
761 || ' AND MDBR.PDATE BETWEEN inp.start_date AND inp.end_date ';
762 ELSE
763 x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN inp.start_date AND inp.end_date ';
764 END IF;
765
766 x_key_values := x_key_values || '$';
767
768 END IF;
769
770 /* Get the query */
771 msd_dem_query_utilities.get_query2 (
772 x_retcode1,
773 x_large_sql,
774 x_pquery_identifier,
775 p_sr_instance_id,
776 x_key_values,
777 x_gmp_is_custom,
778 l_gmp_custom_view_name);
779
780 IF ( x_retcode1 = '-1'
781 OR x_large_sql IS NULL)
782 THEN
783 retcode := -1;
784 errbuf := 'Unable to get the query.';
785 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'));
786 msd_dem_common_utilities.log_message (errbuf);
787 RETURN;
788 END IF;
789
790
791 msd_dem_common_utilities.log_debug ('Query - ');
792 msd_dem_common_utilities.log_debug (x_large_sql);
793
794 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
795
796 BEGIN
797 /* Upsert history data into sales staging table */
798 EXECUTE IMMEDIATE x_large_sql;
799 COMMIT;
800 EXCEPTION
801 WHEN OTHERS THEN
802 retcode := -1 ;
803 errbuf := substr(SQLERRM,1,150);
804 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'));
805 msd_dem_common_utilities.log_message (errbuf);
806 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
807 RETURN;
808 END;
809
810 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
811 msd_dem_common_utilities.log_debug ('End collect process sales history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
812
813 END IF;
814
815 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_series_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 retcode := -1 ;
820 errbuf := substr(SQLERRM,1,150);
821 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'));
822 msd_dem_common_utilities.log_message (errbuf);
823 RETURN;
824
825 END COLLECT_SERIES_DATA;
826
827
828
829 /*
830 * This procedure inserts dummy rows into the sales staging tables for new items
831 */
832 PROCEDURE INSERT_DUMMY_ROWS (
833 errbuf OUT NOCOPY VARCHAR2,
834 retcode OUT NOCOPY VARCHAR2,
835 p_dest_table IN VARCHAR2,
836 p_sr_instance_id IN NUMBER)
837 IS
838
839 /*** CURSORS ***/
840
841 CURSOR c_check_new_items
842 IS
843 SELECT 1
844 FROM dual
845 WHERE EXISTS (SELECT 1
846 FROM msd_dem_new_items
847 WHERE sr_instance_id = p_sr_instance_id
848 AND process_flag = 2);
849
850 /*** LOCAL VARIABLES ***/
851 x_retcode VARCHAR2(100) := NULL;
852
853 x_new_items_present NUMBER := NULL;
854 x_sql VARCHAR2(32000) := NULL;
855 BEGIN
856
857 msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
858
859 /* Check if there are any yet to be processed NPIs */
860 OPEN c_check_new_items;
861 FETCH c_check_new_items INTO x_new_items_present;
862 CLOSE c_check_new_items;
863
864 IF (x_new_items_present = 1)
865 THEN
866 msd_dem_common_utilities.log_message ('Found new items for processing');
867
868 msd_dem_query_utilities.get_query(
869 x_retcode,
870 x_sql,
871 'DUMMY_ROWS_FOR_NEW_ITEMS',
872 p_sr_instance_id,
873 p_dest_table);
874
875 IF (x_retcode = -1)
876 THEN
877 retcode := 1;
878 errbuf := 'Unable to get the query for inserting dummy rows for new items into sales staging table';
879 msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_history_data.insert_dummy_rows');
880 msd_dem_common_utilities.log_message (errbuf);
881 RETURN;
882 END IF;
883
884 msd_dem_common_utilities.log_debug ('Query - ');
885 msd_dem_common_utilities.log_debug ('Bind Variables - ');
886 msd_dem_common_utilities.log_debug ('Source Instance Id - ' || to_char(p_sr_instance_id));
887 msd_dem_common_utilities.log_debug (x_sql);
888
889 msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
890 EXECUTE IMMEDIATE x_sql USING p_sr_instance_id;
891 msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
892
893 /* Set the process_flag */
894 UPDATE msd_dem_new_items
895 SET process_flag = 1
896 WHERE sr_instance_id = p_sr_instance_id
897 AND process_flag = 2;
898
899 COMMIT;
900
901 END IF;
902
903 msd_dem_common_utilities.log_debug ('Exiting msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 retcode := 1 ;
908 errbuf := substr(SQLERRM,1,150);
909 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.insert_dummy_rows - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
910 msd_dem_common_utilities.log_message (errbuf);
911 RETURN;
912 END INSERT_DUMMY_ROWS;
913
914
915 /*
916 * This procedure analyzes the given table
917 */
918 PROCEDURE ANALYZE_TABLE (
919 errbuf OUT NOCOPY VARCHAR2,
920 retcode OUT NOCOPY VARCHAR2,
921 p_table_name IN VARCHAR2)
922 IS
923
924 /*** LOCAL VARIABLES ***/
925
926 x_schema_name VARCHAR2(30) := NULL;
927 x_table_name VARCHAR2(30) := NULL;
928
929 x_pos NUMBER := NULL;
930
931 BEGIN
932
933 msd_dem_common_utilities.log_debug ('Entering msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
934
935 x_pos := instr( p_table_name, '.', 1, 1);
936
937 IF (x_pos = 0)
938 THEN
939 x_schema_name := 'MSD';
940 x_table_name := p_table_name;
941 ELSE
942 x_schema_name := substr (p_table_name, 1, x_pos - 1);
943 x_table_name := substr (p_table_name, x_pos +1);
944 END IF;
945
946 msd_dem_common_utilities.log_message ('Analyzing Table - ' || x_schema_name || '.' || x_table_name);
947 fnd_stats.gather_table_stats(x_schema_name, x_table_name, 10, 4);
948
949 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
950
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 retcode := 1 ;
955 errbuf := substr(SQLERRM,1,150);
956 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.analyze_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
957 msd_dem_common_utilities.log_message (errbuf);
958 RETURN;
959 END ANALYZE_TABLE;
960
961 /*** PUBLIC PROCEDURES ***/
962
963
964 PROCEDURE COLLECT_HISTORY_DATA (
965 errbuf OUT NOCOPY VARCHAR2,
966 retcode OUT NOCOPY VARCHAR2,
967 p_sr_instance_id IN NUMBER,
968 p_collection_group IN VARCHAR2,
969 p_collection_method IN NUMBER,
970 p_hidden_param1 IN VARCHAR2,
971 p_date_range_type IN NUMBER,
972 p_collection_window IN NUMBER,
973 p_from_date IN VARCHAR2,
974 p_to_date IN VARCHAR2,
975 p_bh_bi_bd IN NUMBER,
976 p_bh_bi_rd IN NUMBER,
977 p_bh_ri_bd IN NUMBER,
978 p_bh_ri_rd IN NUMBER,
979 p_sh_si_sd IN NUMBER,
980 p_sh_si_rd IN NUMBER,
981 p_sh_ri_sd IN NUMBER,
982 p_sh_ri_rd IN NUMBER,
983 p_collect_iso IN NUMBER DEFAULT G_NO,
984 p_collect_all_order_types IN NUMBER DEFAULT G_YES,
985 p_hidden_param2 IN VARCHAR2,
986 p_type_selection_method IN NUMBER DEFAULT G_COMMA,
987 p_include_order_types IN VARCHAR2 DEFAULT NULL,
988 p_exclude_order_types IN VARCHAR2 DEFAULT NULL,
989 p_auto_run_download IN NUMBER,
990 p_for_spf IN NUMBER DEFAULT G_NO )
991 IS
992
993 /*** LOCAL VARIABLES ****/
994
995 x_errbuf VARCHAR2(200) := NULL;
996 x_errbuf1 VARCHAR2(200) := NULL;
997 x_retcode VARCHAR2(100) := NULL;
998 x_retcode1 VARCHAR2(100) := NULL;
999
1000 x_order_type_ids VARCHAR2(2000);
1001 x_order_type_flag NUMBER;
1002 x_invalid_count NUMBER := 0;
1003 x_dest_table VARCHAR2(100) := NULL;
1004
1005 x_sql VARCHAR2(1000) := NULL;
1006
1007 x_from_date DATE := NULL;
1008 x_to_date DATE := NULL;
1009
1010 x_instance_code VARCHAR2(30) := NULL;
1011 x_apps_ver NUMBER := NULL;
1012 x_dgmt NUMBER := NULL;
1013 x_instance_type NUMBER := NULL;
1014 x_dm_time_level NUMBER := NULL;
1015 x_dm_time_bucket VARCHAR2(30) := NULL;
1016
1017 g_schema VARCHAR2(50) := NULL;
1018
1019 l_sql VARCHAR2(1000) := NULL;
1020 l_profile_id NUMBER := NULL;
1021 l_bh_bi_bd_id NUMBER := NULL;
1022 l_bh_bi_rd_id NUMBER := NULL;
1023 l_bh_ri_bd_id NUMBER := NULL;
1024 l_bh_ri_rd_id NUMBER := NULL;
1025 l_sh_si_sd_id NUMBER := NULL;
1026 l_sh_si_rd_id NUMBER := NULL;
1027 l_sh_ri_sd_id NUMBER := NULL;
1028 l_sh_ri_rd_id NUMBER := NULL;
1029
1030 l_table_name varchar2(240) := NULL;
1031 l_start_date date := NULL;
1032 l_until_date date := NULL;
1033 l_schema_name VARCHAR2(100) := NULL;
1034
1035
1036 CURSOR c_get_dm_schema --jarora
1037 IS
1038 SELECT owner
1039 FROM dba_objects
1040 WHERE owner = owner
1041 AND object_type = 'TABLE'
1042 AND object_name = 'MDP_MATRIX'
1043 ORDER BY created desc;
1044
1045 BEGIN
1046
1047 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1048
1049 /* Get the db link to the source instance */
1050 msd_dem_common_utilities.get_dblink (
1051 x_errbuf,
1052 x_retcode,
1053 p_sr_instance_id,
1054 g_dblink);
1055
1056 IF (x_retcode = '-1')
1057 THEN
1058 retcode := -1;
1059 errbuf := x_errbuf;
1060 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'));
1061 RETURN;
1062 END IF;
1063
1064 g_collection_method := p_collection_method;
1065
1066 /* VALIDATION OF INPUT PARAMETERS - BEGIN */
1067
1068 msd_dem_common_utilities.log_debug ('Begin validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1069
1070 /* Atleast one parameter must be selected */
1071 IF ( p_bh_bi_bd = G_NO
1072 AND p_bh_bi_rd = G_NO
1073 AND p_bh_ri_bd = G_NO
1074 AND p_bh_ri_rd = G_NO
1075 AND p_sh_si_sd = G_NO
1076 AND p_sh_si_rd = G_NO
1077 AND p_sh_ri_sd = G_NO
1078 AND p_sh_ri_rd = G_NO)
1079 THEN
1080 retcode := -1;
1081 errbuf := 'No series selected for collection';
1082 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'));
1083 msd_dem_common_utilities.log_message (errbuf);
1084 RETURN;
1085 END IF;
1086
1087
1088 /* Show Warning if collection method is Refresh and a date range filter is specified */
1089 IF ( p_collection_method = 1
1090 AND ( p_from_date IS NOT NULL
1091 OR p_to_date IS NOT NULL
1092 OR p_collection_window IS NOT NULL))
1093 THEN
1094 x_retcode := 1;
1095 x_errbuf := 'Date Range filters are ignored in ''Refresh'' collections';
1096 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'));
1097 msd_dem_common_utilities.log_message (x_errbuf);
1098 END IF;
1099
1100
1101 /* Show Warning if collection method is net change, date range type is Rolling and from date and to date are specified */
1102 IF ( p_collection_method = 2
1103 AND p_date_range_type = 2
1104 AND ( p_from_date IS NOT NULL
1105 OR p_to_date IS NOT NULL))
1106 THEN
1107 x_retcode := 1;
1108 x_errbuf := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date range type is selected.';
1109 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'));
1110 msd_dem_common_utilities.log_message (x_errbuf);
1111 END IF;
1112
1113
1114 /* Show Warning if collection method is net change, date range type is Absolute and history collection window is specified */
1115 IF ( p_collection_method = 2
1116 AND p_date_range_type = 1
1117 AND p_collection_window IS NOT NULL)
1118 THEN
1119 x_retcode := 1;
1120 x_errbuf := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
1121 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'));
1122 msd_dem_common_utilities.log_message (x_errbuf);
1123 END IF;
1124
1125
1126 /* Error if collection method is net change, date range type is Rolling and history collection window is not specified */
1127 IF ( p_collection_method = 2
1128 AND p_date_range_type = 2
1129 AND p_collection_window IS NULL)
1130 THEN
1131 retcode := -1;
1132 errbuf := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
1133 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'));
1134 msd_dem_common_utilities.log_message (errbuf);
1135 RETURN;
1136 END IF;
1137
1138
1139 /* Error if collection method is net change, date range type is Absolute and from date and to date are not specified */
1140 IF ( p_collection_method = 2
1141 AND p_date_range_type = 1
1142 AND ( p_from_date IS NULL
1143 OR p_to_date IS NULL))
1144 THEN
1145 retcode := -1;
1146 errbuf := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
1147 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'));
1148 msd_dem_common_utilities.log_message (errbuf);
1149 RETURN;
1150 END IF;
1151
1152 /* Validate the order types specified by the user */
1153 x_invalid_count := validate_order_types (
1154 x_errbuf1,
1155 x_retcode1,
1156 x_order_type_flag,
1157 x_order_type_ids,
1158 p_sr_instance_id,
1159 p_collect_all_order_types,
1160 p_type_selection_method,
1161 p_include_order_types,
1162 p_exclude_order_types );
1163 IF (x_retcode1 = -1)
1164 THEN
1165 retcode := -1;
1166 errbuf := 'No valid order types found';
1167 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'));
1168 msd_dem_common_utilities.log_message (errbuf);
1169 RETURN;
1170 ELSIF (x_invalid_count > 0)
1171 THEN
1172 x_retcode := 1;
1173 x_errbuf := 'Invalid order types found';
1174 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'));
1175 msd_dem_common_utilities.log_message (x_errbuf);
1176 ELSIF (x_retcode1 = 1)
1177 THEN
1178 x_retcode := 1;
1179 x_errbuf := x_errbuf1;
1180 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'));
1181 END IF;
1182
1183 msd_dem_common_utilities.log_debug ('End validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1184
1185 /* VALIDATION OF INPUT PARAMETERS - END */
1186
1187
1188 /* Get the start date and end dates for collection */
1189
1190 msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1191
1192 IF (p_collection_method = 1) /* Refresh*/
1193 THEN
1194 x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
1195 x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
1196 ELSE /* Net Change */
1197 IF (p_date_range_type = 1) /* Absolute*/
1198 THEN
1199
1200 IF (p_from_date IS NULL)
1201 THEN
1202 x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
1203 ELSE
1204 x_from_date := fnd_date.canonical_to_date (p_from_date);
1205 END IF;
1206
1207 IF (p_to_date IS NULL)
1208 THEN
1209 x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
1210 ELSE
1211 x_to_date := fnd_date.canonical_to_date (p_to_date);
1212 END IF;
1213
1214 /* Error if p_from_date is greater than p_to_date */
1215 IF (x_from_date > x_to_date)
1216 THEN
1217 retcode := -1;
1218 errbuf := 'From Date should not be greater than To Date.';
1219 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'));
1220 msd_dem_common_utilities.log_message (errbuf);
1221 RETURN;
1222 END IF;
1223
1224 ELSE /* Rolling */
1225
1226 IF (p_collection_window < 0)
1227 THEN
1228 retcode := -1;
1229 errbuf := 'History Collection Window must be a positive number.';
1230 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'));
1231 msd_dem_common_utilities.log_message (errbuf);
1232 RETURN;
1233 ELSE
1234 x_to_date := trunc(sysdate);
1235 x_from_date := x_to_date - p_collection_window + 1;
1236 END IF;
1237 END IF;
1238 END IF;
1239
1240 msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1241 msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1242
1243 /* Get the instance info */
1244 msd_dem_common_utilities.get_instance_info (
1245 x_errbuf1,
1246 x_retcode1,
1247 x_instance_code,
1248 x_apps_ver,
1249 x_dgmt,
1250 x_instance_type,
1251 p_sr_instance_id);
1252
1253 IF (x_retcode1 = '-1')
1254 THEN
1255 retcode := -1;
1256 errbuf := x_errbuf1;
1257 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'));
1258 msd_dem_common_utilities.log_message ('Unable to get instance info.');
1259 RETURN;
1260 END IF;
1261
1262 msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1263
1264 /* Get the sales staging table name */
1265 x_dest_table := msd_dem_common_utilities.get_lookup_value('MSD_DEM_DM_STAGING_TABLES','SALES_STAGING_TABLE');
1266
1267 IF (x_dest_table is NULL)
1268 THEN
1269 retcode := -1;
1270 errbuf := 'Unable to find the sales staging tables.';
1271 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'));
1272 msd_dem_common_utilities.log_message (errbuf);
1273 RETURN;
1274 END IF;
1275
1276 msd_dem_common_utilities.log_message (' Collect History Data - Actions');
1277 msd_dem_common_utilities.log_message ('--------------------------------');
1278 msd_dem_common_utilities.log_message (' ');
1279
1280 msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
1281 msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));
1282
1283 msd_dem_common_utilities.log_debug ('Begin delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1284
1285 /* Truncate the sales staging table */
1286 msd_dem_common_utilities.log_message ('Deleting data from sales staging table - ' || x_dest_table);
1287
1288 if p_collection_method = 1 then
1289 x_sql := 'TRUNCATE TABLE ' || x_dest_table;
1290 else
1291 IF (nvl( fnd_profile.value( 'MSD_DEM_TRUNCATE_STG_TABLE'), 'N') = 'Y')
1292 THEN
1293 x_sql := 'TRUNCATE TABLE ' || x_dest_table;
1294 ELSE
1295 x_sql := 'DELETE FROM '|| x_dest_table || ' where sales_date between ''' || x_from_date || ''' AND ''' || x_to_date || '''';
1296 END IF;
1297 end if;
1298
1299 EXECUTE IMMEDIATE x_sql;
1300
1301 IF (p_collection_method <> 1
1302 AND nvl( fnd_profile.value( 'MSD_DEM_TRUNCATE_STG_TABLE'), 'N') <> 'Y')
1303 THEN
1304
1305 x_sql := 'DELETE FROM ' || x_dest_table || ' t1 '
1306 || ' WHERE ebs_parent_item_sr_pk is not null '
1307 || ' AND actual_qty = 0 '
1308 || ' AND ebs_base_model_sr_pk is not null ';
1309 EXECUTE IMMEDIATE x_sql;
1310
1311 x_sql := 'UPDATE ' || x_dest_table || ' t1 '
1312 || ' SET ebs_base_model_sr_pk = null '
1313 || ' WHERE ebs_base_model_sr_pk is not null ';
1314 EXECUTE IMMEDIATE x_sql;
1315
1316 END IF;
1317
1318 msd_dem_common_utilities.log_debug ('End delete from sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1319
1320 -- bug#9327919 nallkuma
1321 l_schema_name := substr(x_dest_table , 1 , instr(x_dest_table, '.')-1) ;
1322 msd_dem_common_utilities.log_message('Fetched the schema name as : '||l_schema_name);
1323
1324 IF (l_schema_name <> 'MSD' ) then -- Bug#8721519 -- nallkuma
1325 msd_dem_common_utilities.log_message ('Begin Delete data from ERR table - ' || x_dest_table ||'_err');
1326 msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1327
1328 /* Truncate the ERR tables */ -- Saravan -> Bug# 6357056
1329 msd_dem_common_utilities.log_debug ('Deleting data from ERR table - ' || x_dest_table ||'_err');
1330 x_sql := 'TRUNCATE TABLE ' || x_dest_table ||'_err';
1331 EXECUTE IMMEDIATE x_sql;
1332 msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1333 END IF;
1334
1335 msd_dem_common_utilities.log_debug ('End delete from ERR table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1336 --saravan
1337 msd_dem_common_utilities.log_debug ('Begin get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1338
1339 OPEN c_get_dm_schema; --jarora
1340 FETCH c_get_dm_schema INTO g_schema;
1341 CLOSE c_get_dm_schema;
1342
1343 /* Get the lowest time bucket */
1344 /* Demantra is Installed */
1345 IF (g_schema IS NOT NULL) --jarora
1346 THEN
1347 x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
1348 ELSE
1349 x_dm_time_bucket := 'DAY';
1350 END IF;
1351
1352 IF (x_dm_time_bucket IS NULL)
1353 THEN
1354 retcode := -1;
1355 errbuf := 'Unable to get lowest time bucket';
1356 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'));
1357 msd_dem_common_utilities.log_message (errbuf);
1358 RETURN;
1359 ELSIF (upper(x_dm_time_bucket) = 'DAY')
1360 THEN
1361 x_dm_time_level := 1;
1362 ELSE
1363 x_dm_time_level := 2;
1364 END IF;
1365
1366 msd_dem_common_utilities.log_debug ('End get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1367
1368 /* Collect each series selected by the user */
1369
1370 /* Booking History - Booked Items - Booked Date */
1371 msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1372 IF (p_bh_bi_bd = G_YES)
1373 THEN
1374 collect_series_data (
1375 x_errbuf1,
1376 x_retcode1,
1377 MSD_DEM_COMMON_UTILITIES.C_BH_BI_BD,
1378 x_dest_table,
1379 x_dm_time_level,
1380 p_sr_instance_id,
1381 x_apps_ver,
1382 x_instance_type,
1383 p_collection_group,
1384 p_collection_method,
1385 x_from_date,
1386 x_to_date,
1387 p_collect_iso,
1388 x_order_type_flag,
1389 x_order_type_ids);
1390
1391 IF (x_retcode1 = -1)
1392 THEN
1393 retcode := -1;
1394 errbuf := x_errbuf1;
1395 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'));
1396 msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Booked Date');
1397 RETURN;
1398 END IF;
1399 END IF;
1400 msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1401
1402
1403 /* Booking History - Booked Items - Requested Date */
1404 msd_dem_common_utilities.log_debug ('Begin collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1405 IF (p_bh_bi_rd = G_YES)
1406 THEN
1407 collect_series_data (
1408 x_errbuf1,
1409 x_retcode1,
1410 MSD_DEM_COMMON_UTILITIES.C_BH_BI_RD,
1411 x_dest_table,
1412 x_dm_time_level,
1413 p_sr_instance_id,
1414 x_apps_ver,
1415 x_instance_type,
1416 p_collection_group,
1417 p_collection_method,
1418 x_from_date,
1419 x_to_date,
1420 p_collect_iso,
1421 x_order_type_flag,
1422 x_order_type_ids);
1423
1424 IF (x_retcode1 = -1)
1425 THEN
1426 retcode := -1;
1427 errbuf := x_errbuf1;
1428 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'));
1429 msd_dem_common_utilities.log_message ('Error while collecting Booking History - Booked Items - Requested Date');
1430 RETURN;
1431 END IF;
1432 END IF;
1433 msd_dem_common_utilities.log_debug ('End collect Booking History - Booked Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1434
1435 /* Booking History - Requested Items - Booked Date */
1436 msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1437 IF (p_bh_ri_bd = G_YES)
1438 THEN
1439 collect_series_data (
1440 x_errbuf1,
1441 x_retcode1,
1442 MSD_DEM_COMMON_UTILITIES.C_BH_RI_BD,
1443 x_dest_table,
1444 x_dm_time_level,
1445 p_sr_instance_id,
1446 x_apps_ver,
1447 x_instance_type,
1448 p_collection_group,
1449 p_collection_method,
1450 x_from_date,
1451 x_to_date,
1452 p_collect_iso,
1453 x_order_type_flag,
1454 x_order_type_ids);
1455
1456 IF (x_retcode1 = -1)
1457 THEN
1458 retcode := -1;
1459 errbuf := x_errbuf1;
1460 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'));
1461 msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Booked Date');
1462 RETURN;
1463 END IF;
1464 END IF;
1465 msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Booked Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1466
1467 /* Booking History - Requested Items - Requested Date */
1468 msd_dem_common_utilities.log_debug ('Begin collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1469 IF (p_bh_ri_rd = G_YES)
1470 THEN
1471 collect_series_data (
1472 x_errbuf1,
1473 x_retcode1,
1474 MSD_DEM_COMMON_UTILITIES.C_BH_RI_RD,
1475 x_dest_table,
1476 x_dm_time_level,
1477 p_sr_instance_id,
1478 x_apps_ver,
1479 x_instance_type,
1480 p_collection_group,
1481 p_collection_method,
1482 x_from_date,
1483 x_to_date,
1484 p_collect_iso,
1485 x_order_type_flag,
1486 x_order_type_ids);
1487
1488 IF (x_retcode1 = -1)
1489 THEN
1490 retcode := -1;
1491 errbuf := x_errbuf1;
1492 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'));
1493 msd_dem_common_utilities.log_message ('Error while collecting Booking History - Requested Items - Requested Date');
1494 RETURN;
1495 END IF;
1496 END IF;
1497 msd_dem_common_utilities.log_debug ('End collect Booking History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1498
1499 /* Shipment History - Shipped Items - Shipped Date */
1500 msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1501 IF (p_sh_si_sd = G_YES)
1502 THEN
1503 collect_series_data (
1504 x_errbuf1,
1505 x_retcode1,
1506 MSD_DEM_COMMON_UTILITIES.C_SH_SI_SD,
1507 x_dest_table,
1508 x_dm_time_level,
1509 p_sr_instance_id,
1510 x_apps_ver,
1511 x_instance_type,
1512 p_collection_group,
1513 p_collection_method,
1514 x_from_date,
1515 x_to_date,
1516 p_collect_iso,
1517 x_order_type_flag,
1518 x_order_type_ids);
1519
1520 IF (x_retcode1 = -1)
1521 THEN
1522 retcode := -1;
1523 errbuf := x_errbuf1;
1524 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'));
1525 msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Shipped Date');
1526 RETURN;
1527 END IF;
1528 END IF;
1529 msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1530
1531 /* Shipment History - Shipped Items - Requested Date */
1532 msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1533 IF (p_sh_si_rd = G_YES)
1534 THEN
1535 collect_series_data (
1536 x_errbuf1,
1537 x_retcode1,
1538 MSD_DEM_COMMON_UTILITIES.C_SH_SI_RD,
1539 x_dest_table,
1540 x_dm_time_level,
1541 p_sr_instance_id,
1542 x_apps_ver,
1543 x_instance_type,
1544 p_collection_group,
1545 p_collection_method,
1546 x_from_date,
1547 x_to_date,
1548 p_collect_iso,
1549 x_order_type_flag,
1550 x_order_type_ids);
1551
1552 IF (x_retcode1 = -1)
1553 THEN
1554 retcode := -1;
1555 errbuf := x_errbuf1;
1556 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'));
1557 msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Shipped Items - Requested Date');
1558 RETURN;
1559 END IF;
1560 END IF;
1561 msd_dem_common_utilities.log_debug ('End collect Shipment History - Shipped Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1562
1563 /* Shipment History - Requested Items - Shipped Date */
1564 msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1565 IF (p_sh_ri_sd = G_YES)
1566 THEN
1567 collect_series_data (
1568 x_errbuf1,
1569 x_retcode1,
1570 MSD_DEM_COMMON_UTILITIES.C_SH_RI_SD,
1571 x_dest_table,
1572 x_dm_time_level,
1573 p_sr_instance_id,
1574 x_apps_ver,
1575 x_instance_type,
1576 p_collection_group,
1577 p_collection_method,
1578 x_from_date,
1579 x_to_date,
1580 p_collect_iso,
1581 x_order_type_flag,
1582 x_order_type_ids);
1583
1584 IF (x_retcode1 = -1)
1585 THEN
1586 retcode := -1;
1587 errbuf := x_errbuf1;
1588 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'));
1589 msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Shipped Date');
1590 RETURN;
1591 END IF;
1592 END IF;
1593 msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Shipped Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1594
1595 /* Shipment History - Requested Items - Requested Date */
1596 msd_dem_common_utilities.log_debug ('Begin collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1597 IF (p_sh_ri_rd = G_YES)
1598 THEN
1599 collect_series_data (
1600 x_errbuf1,
1601 x_retcode1,
1602 MSD_DEM_COMMON_UTILITIES.C_SH_RI_RD,
1603 x_dest_table,
1604 x_dm_time_level,
1605 p_sr_instance_id,
1606 x_apps_ver,
1607 x_instance_type,
1608 p_collection_group,
1609 p_collection_method,
1610 x_from_date,
1611 x_to_date,
1612 p_collect_iso,
1613 x_order_type_flag,
1614 x_order_type_ids);
1615
1616
1617
1618 IF (x_retcode1 = -1)
1619 THEN
1620 retcode := -1;
1621 errbuf := x_errbuf1;
1622 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'));
1623 msd_dem_common_utilities.log_message ('Error while collecting Shipment History - Requested Items - Requested Date');
1624 RETURN;
1625 END IF;
1626 END IF;
1627 msd_dem_common_utilities.log_debug ('End collect Shipment History - Requested Items - Requested Date - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1628
1629 /* Bug# 5869314 - Insert dummy rows in the staging table for new items */
1630 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'));
1631 insert_dummy_rows (
1632 x_errbuf1,
1633 x_retcode1,
1634 x_dest_table,
1635 p_sr_instance_id);
1636
1637 IF (x_retcode1 = 1)
1638 THEN
1639 retcode := 1;
1640 errbuf := x_errbuf1;
1641 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'));
1642 msd_dem_common_utilities.log_message ('Error while inserting dummy rows into the sales staging table for new items. ');
1643 END IF;
1644 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'));
1645
1646 COMMIT;
1647
1648 /* Delete Internal Sales Orders in the same Line of Business */
1649
1650 if p_collect_iso = 1 then
1651 msd_dem_common_utilities.log_debug ('Begin Delete Internal Sales Orders in the same Line of Business - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1652 delete_internal_sales_orders(x_errbuf1,x_retcode1,p_sr_instance_id);
1653
1654 IF (x_retcode1 = -1)
1655 THEN
1656 retcode := -1;
1657 errbuf := x_errbuf1;
1658 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'));
1659 msd_dem_common_utilities.log_message ('Error while deleting Internal Sales Orders in the same Line of Business');
1660 RETURN;
1661 END IF;
1662
1663 msd_dem_common_utilities.log_debug ('End Delete Internal Sales Orders in the same Line of Business - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1664 end if;
1665
1666 /* For SPF, move the shipments data from T_SRC_SALES_TMPL to BIIO_SPF_SPARES_HIST_DATA */
1667 IF (p_for_spf = G_YES)
1668 THEN
1669
1670 msd_dem_common_utilities.log_debug ('Begin processing for SPF - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1671 msd_dem_common_utilities.log_message ('Moving shipments data from T_SRC_SALES_TMPL to BIIO_SPF_SPARES_HIST_DATA...');
1672
1673 msd_spf_collect_history_data.populate_staging_table (x_errbuf1, x_retcode1, 'EQ_SPF_SHIPMENTS', p_sr_instance_id);
1674 IF (x_retcode1 = -1)
1675 THEN
1676 retcode := -1;
1677 errbuf := x_errbuf1;
1678 msd_dem_common_utilities.log_message ('Error(21): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1679 msd_dem_common_utilities.log_message ('Error in call to msd_spf_collect_history_data.populate_staging_table ');
1680 RETURN;
1681 END IF;
1682
1683 msd_dem_common_utilities.log_message ('Truncating staging table T_SRC_SALES_TMPL...');
1684 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dest_table;
1685
1686 msd_dem_common_utilities.log_debug ('End processing for SPF - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1687
1688 END IF;
1689
1690 /* Call Custom Hook for History */
1691
1692 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'));
1693
1694 msd_dem_custom_hooks.history_hook (
1695 x_errbuf1,
1696 x_retcode1);
1697
1698 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'));
1699
1700 IF (x_retcode1 = -1)
1701 THEN
1702 retcode := -1;
1703 errbuf := x_errbuf1;
1704 msd_dem_common_utilities.log_message ('Error(20): msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1705 msd_dem_common_utilities.log_message ('Error in call to custom hook msd_dem_custom_hooks.history_hook ');
1706 RETURN;
1707 END IF;
1708
1709 /* Analyze Sales Staging Table */
1710
1711 msd_dem_common_utilities.log_debug ('Begin Analyze sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1712
1713 analyze_table (
1714 x_errbuf1,
1715 x_retcode1,
1716 x_dest_table);
1717
1718 msd_dem_common_utilities.log_debug ('End Analyze sales staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1719
1720 IF (x_retcode1 = 1)
1721 THEN
1722 retcode := 1;
1723 errbuf := x_errbuf1;
1724 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'));
1725 msd_dem_common_utilities.log_message ('Error while analyzing sales staging table. ');
1726 END IF;
1727
1728 /*
1729 *Order Realignment
1730 */
1731 Begin
1732
1733 if (g_schema is not null) then
1734 g_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1735 end if;
1736
1737 if (g_schema is not null)
1738 then
1739
1740 -- Bug#8224935 - APP ID
1741 l_profile_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1742 'PROFILE_PURGE_HISTORY_DATA',
1743 1,
1744 'id'));
1745
1746 l_sql := 'select table_name, from_date, until_date from '|| g_schema || '.transfer_query where id = ' || l_profile_id;
1747 execute immediate l_sql into l_table_name, l_start_date, l_until_date;
1748 -- syenamar
1749
1750 /* Refreshing the Purge Series Data profile to the default value ie No load and No Purge option */
1751 msd_dem_common_utilities.REFRESH_PURGE_SERIES(x_errbuf1, x_retcode1, l_profile_id, g_schema);
1752
1753 IF (x_retcode1 = -1)
1754 THEN
1755 retcode := -1;
1756 errbuf := x_errbuf1;
1757
1758 msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
1759 END IF;
1760
1761 /* Calling API to modify the data profile to purge selected series */
1762 msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_ATTR - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1763
1764
1765
1766 if p_bh_bi_bd = G_YES
1767 then
1768 /* Bug#8224935 - APP ID */
1769 l_bh_bi_bd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1770 'SERIES_EBS_BH_BOOK_QTY_BD',
1771 1,
1772 'forecast_type_id'));
1773
1774 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_bh_bi_bd_id||', 0, 2); end;';
1775 execute immediate l_sql;
1776 end if;
1777
1778 if p_bh_bi_rd = G_YES
1779 then
1780 /* Bug#8224935 - APP ID */
1781 l_bh_bi_rd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1782 'SERIES_EBS_BH_BOOK_QTY_RD',
1783 1,
1784 'forecast_type_id'));
1785
1786 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_bh_bi_rd_id||', 0, 2); end;';
1787 execute immediate l_sql;
1788 end if;
1789
1790 if p_bh_ri_bd = G_YES
1791 then
1792 /* Bug#8224935 - APP ID */
1793 l_bh_ri_bd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1794 'SERIES_EBS_BH_REQ_QTY_BD',
1795 1,
1796 'forecast_type_id'));
1797
1798 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_bh_ri_bd_id||', 0, 2); end;';
1799 execute immediate l_sql;
1800 end if;
1801
1802 if p_bh_ri_rd = G_YES
1803 then
1804 /* Bug#8224935 - APP ID */
1805 l_bh_ri_rd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1806 'SERIES_EBS_BH_REQ_QTY_RD',
1807 1,
1808 'forecast_type_id'));
1809
1810 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_bh_ri_rd_id||', 0, 2); end;';
1811 execute immediate l_sql;
1812 end if;
1813
1814 if p_sh_si_sd = G_YES
1815 then
1816 /* Bug#8224935 - APP ID */
1817 l_sh_si_sd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1818 'SERIES_EBS_SH_SHIP_QTY_SD',
1819 1,
1820 'forecast_type_id'));
1821
1822 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_sh_si_sd_id||', 0, 2); end;';
1823 execute immediate l_sql;
1824 end if;
1825
1826 if p_sh_si_rd = G_YES
1827 then
1828 /* Bug#8224935 - APP ID */
1829 l_sh_si_rd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1830 'SERIES_EBS_SH_SHIP_QTY_RD',
1831 1,
1832 'forecast_type_id'));
1833
1834 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_sh_si_rd_id||', 0, 2); end;';
1835 execute immediate l_sql;
1836 end if;
1837
1838 if p_sh_ri_sd = G_YES
1839 then
1840 /* Bug#8224935 - APP ID */
1841 l_sh_ri_sd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1842 'SERIES_SALES',
1843 1,
1844 'forecast_type_id'));
1845
1846 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_sh_ri_sd_id||', 0, 2); end;';
1847 execute immediate l_sql;
1848 end if;
1849
1850 if p_sh_ri_rd = G_YES
1851 then
1852 /* Bug#8224935 - APP ID */
1853 l_sh_ri_rd_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
1854 'SERIES_EBS_SH_REQ_QTY_RD',
1855 1,
1856 'forecast_type_id'));
1857
1858 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_sh_ri_rd_id||', 0, 2); end;';
1859 execute immediate l_sql;
1860 end if;
1861
1862
1863
1864 /* Calling API to modify the data profile date range */
1865 msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_FDATE - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1866
1867 l_sql := 'select datet from '|| g_schema ||'.inputs where datet >= '''||x_from_date||''' and rownum = 1 order by datet asc';
1868 execute immediate l_sql into x_from_date;
1869
1870 l_sql := 'select datet from '|| g_schema ||'.inputs where datet <= '''||x_to_date||''' and rownum = 1 order by datet desc';
1871 execute immediate l_sql into x_to_date;
1872
1873 if (x_from_date > x_to_date) then
1874 x_to_date := x_from_date;
1875 end if;
1876
1877 msd_dem_common_utilities.log_message ('For the selected series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
1878
1879 l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id||', '''|| x_from_date||''' , '''||x_to_date||'''); end;';
1880 execute immediate l_sql;
1881
1882
1883 /* Calling API to notify the application server to refresh its engine */
1884 msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1885
1886 l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id ||'); end;';
1887 msd_dem_common_utilities.log_debug (l_sql);
1888 execute immediate l_sql;
1889
1890 l_sql := 'truncate table '|| g_schema ||'.'||l_table_name ;
1891 msd_dem_common_utilities.log_debug (l_sql);
1892 execute immediate l_sql;
1893
1894 l_sql := 'insert into '|| g_schema ||'.'||l_table_name||'(sdate, level1)'||
1895 'select '''||x_from_date||''', teo.organization from '||g_schema||'.t_ep_organization teo '||
1896 'where teo.organization in
1897 (SELECT mtp.organization_code
1898 FROM msc_instance_orgs mio,
1899 msc_trading_partners mtp
1900 WHERE mio.sr_instance_id = '||p_sr_instance_id||
1901 ' AND nvl(mio.org_group, ''-888'') = decode('''||p_collection_group||''', ''-999'', nvl(mio.org_group, ''-888''), '''||p_collection_group||''')'||
1902 ' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 '||
1903 ' AND mtp.sr_instance_id = mio.sr_instance_id ' ||
1904 ' AND mtp.sr_tp_id = mio.organization_id '||
1905 ' AND mtp.partner_type = 3) ';
1906
1907 msd_dem_common_utilities.log_debug (l_sql);
1908
1909 execute immediate l_sql;
1910
1911 else
1912 msd_dem_common_utilities.log_message('Demantra Schema not set');
1913 end if;
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 retcode := 1 ;
1917 errbuf := substr(SQLERRM,1,150);
1918 msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
1919 msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
1920 msd_dem_common_utilities.log_debug (errbuf);
1921 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1922
1923 RETURN;
1924 End;
1925
1926 retcode := x_retcode;
1927 errbuf := x_errbuf;
1928 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1929
1930 EXCEPTION
1931 WHEN OTHERS THEN
1932 retcode := -1 ;
1933 errbuf := substr(SQLERRM,1,150);
1934 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1935 msd_dem_common_utilities.log_message (errbuf);
1936 RETURN;
1937
1938 END COLLECT_HISTORY_DATA;
1939
1940 PROCEDURE RUN_LOAD (
1941 errbuf OUT NOCOPY VARCHAR2,
1942 retcode OUT NOCOPY VARCHAR2,
1943 p_auto_run_download IN NUMBER,
1944 p_synchronous IN NUMBER DEFAULT G_NO,
1945 p_check_finish IN NUMBER DEFAULT 60,
1946 p_time_out IN NUMBER DEFAULT 60 )
1947 IS
1948
1949 l_sql varchar2(1000);
1950 DEM_SCHEMA varchar2(100);
1951 l_url varchar2(1000);
1952
1953 l_user_id number;
1954 l_user_name varchar2(30);
1955 l_password varchar2(80);
1956 -- Bug#7199587 syenamar
1957 l_schema_name varchar2(255);
1958 l_schema_id number;
1959 /* BUG#6393839 */
1960 ret_process_id varchar2(100);
1961
1962
1963 BEGIN
1964 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1965
1966 DEM_SCHEMA := fnd_profile.value('MSD_DEM_SCHEMA');
1967
1968 IF (p_auto_run_download = G_YES)
1969 THEN
1970 if fnd_profile.value('MSD_DEM_SCHEMA') is not null then
1971
1972
1973
1974 /*
1975
1976 l_stmt := 'alter session set current_schema=' || DEM_SCHEMA;
1977 execute immediate l_stmt;
1978
1979 msd_dem_common_utilities.log_message ('Begin - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1980 msd_dem_common_utilities.log_message ('Please check the *_ERR tables for any errors during Data Load');
1981
1982 msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_PREPARE_DATA - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1983 l_stmt := 'begin ' || DEM_SCHEMA|| '.DATA_LOAD.EP_PREPARE_DATA; end;';
1984 execute immediate l_stmt;
1985
1986 msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_ITEMS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1987 l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_ITEMS; end;';
1988 execute immediate l_stmt;
1989
1990 msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_LOCATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1991 l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_LOCATION; end;';
1992 execute immediate l_stmt;
1993
1994 msd_dem_common_utilities.log_message ('Calling DATA_LOAD.EP_LOAD_SALES - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1995 l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.EP_LOAD_SALES; end;';
1996
1997 execute immediate l_stmt;
1998
1999 msd_dem_common_utilities.log_message ('End - Call DATA_LOAD procedures - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2000
2001 commit;
2002
2003 l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.LOG_EP_LOAD_SUCCESS; end;';
2004 execute immediate l_stmt;
2005
2006 l_stmt := 'alter session set current_schema=APPS';
2007 execute immediate l_stmt;
2008
2009 */
2010
2011
2012 /* Bug#8224935 - APP ID */
2013 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
2014 'COMP_DM',
2015 1,
2016 'user_id'));
2017
2018 IF l_user_id is not null
2019 then
2020 l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
2021 execute immediate l_sql into l_user_name, l_password;
2022
2023 ELSE
2024 /* Bug#8224935 - APP ID */
2025 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
2026 'COMP_SOP',
2027 1,
2028 'user_id'));
2029
2030 If l_user_id is not null
2031 then
2032 l_sql := 'select user_name, password from '||dem_schema||'.user_id where user_id = '||l_user_id;
2033 execute immediate l_sql into l_user_name, l_password;
2034 else
2035 msd_dem_common_utilities.log_message('Component is not found.');
2036 end if;
2037 END IF;
2038
2039
2040 if l_user_name is not null
2041 then
2042 l_url := fnd_profile.value('MSD_DEM_HOST_URL');
2043
2044 -- Bug#7199587 syenamar
2045 -- Do not hard-code 'EBS Full Download' workflow name here. Get its ID from lookup, get its name from demantra schema using the ID.
2046
2047 /* Bug#8224935 - APP ID */
2048 l_schema_name := msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
2049 'WF_EBS_FULL_DOWNLOAD',
2050 1,
2051 'schema_name');
2052
2053 l_schema_name := trim(l_schema_name);
2054 l_sql := null;
2055 l_sql := 'SELECT
2056 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';
2057
2058 msd_dem_common_utilities.log_debug (l_sql);
2059 execute immediate l_sql into ret_process_id;
2060 msd_dem_common_utilities.log_message(' workflow process_id - ' || ret_process_id);
2061
2062 /* BUG#6393839 */
2063 -- Calls only if workflow status_check option is set to 'synchronous'
2064 IF (p_synchronous = 1) THEN
2065
2066 retcode := msd_dem_common_utilities.check_wf_status(ret_process_id,
2067 p_check_finish,p_time_out,dem_schema);
2068 END IF;
2069 /*------------------------*/
2070 else
2071 msd_dem_common_utilities.log_message('Error in launching the download workflow.');
2072 retcode := -1;
2073 Return;
2074 end if;
2075
2076 else
2077 msd_dem_common_utilities.log_message('Demantra Schema not set');
2078 end if;
2079 ELSE
2080 msd_dem_common_utilities.log_message ('Auto Run Download - No ');
2081 msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
2082 END IF;
2083
2084 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2085
2086 EXCEPTION
2087 WHEN OTHERS THEN
2088 errbuf := substr(SQLERRM,1,150);
2089 retcode := -1 ;
2090 -- l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.LOG_EP_LOAD_FAILURE; end;';
2091 -- execute immediate l_stmt;
2092 -- l_stmt := 'alter session set current_schema=APPS';
2093 -- execute immediate l_stmt;
2094
2095 msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_history_data.run_load - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2096 msd_dem_common_utilities.log_message (errbuf);
2097 RETURN;
2098
2099 END RUN_LOAD;
2100
2101 END MSD_DEM_COLLECT_HISTORY_DATA;