eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.MSD_DEM_COLLECT_RETURN_HISTORY

Source


1 PACKAGE BODY MSD_DEM_COLLECT_RETURN_HISTORY AS
2 /* $Header: msddemcrhb.pls 120.9 2007/10/26 11:21:53 vrepaka noship $ */
3 
4     /*** PRIVATE PROCEDURES ***/
5 
6 
7       /*
8        * This procedure given the series id, gets the
9        * data from the source instance and upserts into the
10        * return history staging table.
11        */
12       PROCEDURE COLLECT_DATA (
13       			errbuf				OUT NOCOPY VARCHAR2,
14       			retcode				OUT NOCOPY VARCHAR2,
15       			p_series_id			IN	   NUMBER,
16       			p_dest_table			IN	   VARCHAR2,
17       			p_dm_time_level			IN	   NUMBER,
18       			p_sr_instance_id		IN         NUMBER,
19       			p_apps_ver			IN	   NUMBER,
20       			p_instance_type			IN	   NUMBER,
21       			p_collection_group      	IN         VARCHAR2,
22       			p_collection_method     	IN         NUMBER,
23       			p_from_date			IN	   DATE,
24       			p_to_date			IN	   DATE)
25       IS
26 
27          /*** CURSORS ***/
28 
29          CURSOR c_get_series_info
30          IS
31                SELECT
32                 identifier, MSD_SOURCE_DATE_COL, CUSTOM_VIEW_NAME
33                FROM
34                   msd_dem_series
35                WHERE
36                       series_id = p_series_id
37                   AND series_type = 1;
38 
39          CURSOR c_rma_type
40          is
41          select rma_types
42          from msd_dem_rma_type;
43 
44          CURSOR c_accnt_class  --jarora
45          is
46          select accnt_class_type
47          from msd_dem_accnt_classes;
48 
49 
50          /*** LOCAL VARIABLES ***/
51             x_errbuf		VARCHAR2(200)	:= NULL;
52             x_errbuf1		VARCHAR2(200)	:= NULL;
53             x_retcode		VARCHAR2(100)	:= NULL;
54             x_retcode1		VARCHAR2(100)	:= NULL;
55             XDBLINK             VARCHAR2(100)   := NULL;
56             x_RMA_type          VARCHAR2(5000);
57             x_accnt_class       VARCHAR2(5000);    --jarora
58             x_large_sql		VARCHAR2(32000) := NULL;
59             x_add_where_clause  VARCHAR2(3000)  := NULL;
60 
61             x_dquery_identifier	VARCHAR2(30)	:= NULL;
62             x_pquery_identifier	VARCHAR2(30)	:= NULL;
63 
64             l_identifier           varchar2(30)    := NULL;
65             L_MSD_SOURCE_DATE_COL  VARCHAR2(1000)  := NULL;
66 
67             x_key_values	   VARCHAR2(4000)	:= NULL;
68             x_is_custom            NUMBER          	:= NULL;
69             l_custom_view_name	   VARCHAR2(100)	:= NULL;
70 
71       BEGIN
72 
73          msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_return_history.collect_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
74 
75 
76 
77          IF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_RETURN_HISTORY) --jarora
78          THEN
79 
80          /* RMA types specified by the user */
81          open c_rma_type;
82          fetch c_rma_type into x_rma_type;
83          close c_rma_type;
84 
85          ELSIF ( p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_DR)
86          THEN
87 
88          /* WIP Accounting classes specified by the user */
89          open c_accnt_class;
90          fetch c_accnt_class into x_accnt_class;
91          close c_accnt_class;
92 
93          ELSE
94              NULL;
95          END IF;          --jarora
96 
97 
98          OPEN  c_get_series_info;
99          FETCH c_get_series_info INTO l_identifier, L_MSD_SOURCE_DATE_COL, l_custom_view_name;
100          CLOSE c_get_series_info;
101 
102 
103          IF (l_identifier IS NULL)
104          THEN
105             retcode := -1;
106             errbuf  := 'Unable to get the query identifier.';
107             msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_return_history.collect_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
108             msd_dem_common_utilities.log_message (errbuf);
109             RETURN;
110          END IF;
111 
112          /* Check if custom view for Discrete */
113          IF (l_custom_view_name IS NULL)
114          THEN
115             x_is_custom := 0;
116          ELSE
117             x_is_custom := 1;
118          END IF;
119 
120          /* For Discrete */
121          /* 11i instance where instance type in not 'PROCESS' OR R12 Instance of any type */
122          IF (   p_instance_type <> 2
123              OR p_apps_ver = 4)
124          THEN
125 
126            msd_dem_common_utilities.log_debug ('Begin collect discrete history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); --jarora
127 
128            IF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_RETURN_HISTORY) --jarora
129            THEN
130 
131              If(X_RMA_TYPE <> '''''') then
132              	x_add_where_clause := 'OTTL.NAME IN ('|| X_RMA_TYPE ||')';
133              else
134              	x_add_where_clause := '1=1';
135              end if;
136 
137            ELSIF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_DR)
138            THEN
139 
140              If(X_ACCNT_CLASS <> '''''') then
141              	x_add_where_clause := 'WAC.CLASS_CODE IN ('|| X_ACCNT_CLASS ||')';
142              else
143              	x_add_where_clause := '1=1';
144              end if;
145 
146            ELSE
147                x_add_where_clause := '1=1';
148            END IF;                                 --jarora
149 
150 
151              IF(p_dm_time_level = 1) then
152 
153                x_key_values := '$C_DEST_TABLE#' || p_dest_table
154                                   || '$C_DEST_DATE_GROUP#' || 'MDBR.SDATE'
155                                   || '$C_DEST_DATE#' || 'MDBR.SDATE'
156                                   || '$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'
157                                   || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
158                                   || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
159                                   || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
160 
161                IF (p_collection_method <> 1) THEN
162                   x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ' WHERE SDATE BETWEEN '
163                                      || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
164                                      || ' AND '
165                                      || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') ';
166                ELSE
167                   x_key_values := x_key_values || '$C_TIME_CLAUSE#' || '    ';
168                END IF;
169 
170                x_key_values := x_key_values || '$';
171 
172                /* Bug# 6491059 */
173                IF (p_series_id IN (MSD_DEM_COMMON_UTILITIES.C_ON_HAND_INVENTORY,
174                                    MSD_DEM_COMMON_UTILITIES.C_TOTAL_BACKLOG,
175                                    MSD_DEM_COMMON_UTILITIES.C_PAST_DUE_BACKLOG,
176                                    MSD_DEM_COMMON_UTILITIES.C_ACTUAL_PRODUCTION,
177 								   MSD_DEM_COMMON_UTILITIES.C_INSTALL_BASE_HISTORY))
178                THEN
179                   x_key_values := replace (x_key_values, 'SDATE', 'PDATE');
180                END IF;
181 
182              ELSE
183 
184                x_key_values := '$C_DEST_TABLE#' || p_dest_table
185                                   || '$C_DEST_DATE_GROUP#' || 'INP.DATET'
186                                   || '$C_DEST_DATE#' || 'INP.DATET SDATE'
187                                   || '$C_SOURCE_DATE#' || SUBSTR(L_MSD_SOURCE_DATE_COL, 1, instr(L_MSD_SOURCE_DATE_COL, 'SDATE')-1)||' PDATE'
188                                   || '$C_ADD_WHERE_CLAUSE#' || x_add_where_clause
189                                   || '$C_SR_INSTANCE_ID#' || to_char(p_sr_instance_id)
190                                   || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
191 
192                IF (p_collection_method <> 1) THEN
193                   x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN '
194                                      || 'to_date(''' || to_char(p_from_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
195                                      || ' AND '
196                                      || 'to_date(''' || to_char(p_to_date, 'DD/MM/RRRR') || ''', ''DD/MM/RRRR'') '
197                                      || ' AND MDBR.PDATE BETWEEN inp.start_date AND inp.end_date ';
198                ELSE
199                   x_key_values := x_key_values || '$C_TIME_CLAUSE#' || ', msd_dem_dates inp WHERE mdbr.pdate BETWEEN inp.start_date AND inp.end_date ';
200                END IF;
201 
202                x_key_values := x_key_values || '$';
203 
204                /* Change for streams with LAST aggregation along time */
205                IF (p_series_id IN (MSD_DEM_COMMON_UTILITIES.C_ON_HAND_INVENTORY,
206                                    MSD_DEM_COMMON_UTILITIES.C_TOTAL_BACKLOG,
207                                    MSD_DEM_COMMON_UTILITIES.C_PAST_DUE_BACKLOG))
208                THEN
209                   l_identifier := l_identifier || '_L';
210                END IF;
211 
212               END IF;
213 
214               /* Get the query */
215              msd_dem_query_utilities.get_query2 (
216              			x_retcode1,
217              			x_large_sql,
218              			l_identifier,
219              			p_sr_instance_id,
220              			x_key_values,
221              			x_is_custom,
222              			l_custom_view_name);
223 
224               IF (   x_retcode1 = '-1'
225                  OR x_large_sql IS NULL)
226               THEN
227                 retcode := -1;
228                 errbuf  := 'Unable to get the query.';
229                 msd_dem_common_utilities.log_message ('Error(2): msd_dem_collect_return_history.collect_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
230                 msd_dem_common_utilities.log_message (errbuf);
231                 RETURN;
232               END IF;
233 
234 
235              msd_dem_common_utilities.log_debug ('Query - ');
236              msd_dem_common_utilities.log_debug (x_large_sql);
237 
238              msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
239 
240              BEGIN
241                 /* insert return history data into return history staging table */
242                 EXECUTE IMMEDIATE x_large_sql;
243              EXCEPTION
244                 WHEN OTHERS THEN
245                    retcode := -1 ;
246 	           errbuf  := substr(SQLERRM,1,150);
247 	           msd_dem_common_utilities.log_message ('Exception(1): msd_dem_collect_return_history.collect_data- ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
248 	           msd_dem_common_utilities.log_message (errbuf);
249 	           msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
250 	           RETURN;
251              END;
252 
253              msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
254 
255              COMMIT;
256 
257              IF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_RETURN_HISTORY)
258              THEN
259 
260                 msd_dem_common_utilities.log_debug ('Start Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
261 
262                 MSD_DEM_UPDATE_LEVEL_CODES.update_code(x_errbuf ,
263                       x_retcode,
264                       p_SR_instance_id,
265                       'SITE',
266                       p_dest_table,
267                       'LEVEL4',
268                       'LEVEL4_SR_PK');
269 
270 	        msd_dem_common_utilities.log_debug ('End Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
271 
272              END IF;                 --jarora
273 
274              msd_dem_common_utilities.log_debug ('End collect discrete history  - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));  --jarora
275 
276          END IF;
277 
278       IF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_RETURN_HISTORY)
279       THEN
280           /* Delete the RMA types stored in the DB table */
281           delete from msd_dem_rma_type;
282       ELSIF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_DR)
283       THEN
284           /* Delete the WIP Accounting classes stored in the DB table */
285           delete from msd_dem_accnt_classes;
286       ELSE
287            NULL;
288       END IF;                                             --jarora
289 
290         msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_return_history.collect_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
291 
292       EXCEPTION
293          WHEN OTHERS THEN
294             retcode := -1 ;
295 	    errbuf  := substr(SQLERRM,1,150);
296 	    msd_dem_common_utilities.log_message ('Exception(3): msd_dem_collect_return_history.collect_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
297 	    msd_dem_common_utilities.log_message (errbuf);
298 	    RETURN;
299 
300  END COLLECT_DATA;
301 
302 
303  PROCEDURE COLLECT_RETURN_HISTORY_DATA (
304       			errbuf				OUT NOCOPY VARCHAR2,
305       			retcode				OUT NOCOPY VARCHAR2,
306       			p_sr_instance_id		IN         NUMBER,
307       			p_collection_group      	IN         VARCHAR2 DEFAULT '-999', --jarora
308       			p_collection_method     	IN         NUMBER,
309       			p_date_range_type		IN	   NUMBER,
310       			p_collection_window		IN	   NUMBER,
311       			p_from_date			IN	   VARCHAR2,
312       			p_to_date			IN	   VARCHAR2,
313       			p_entity_name                   IN         VARCHAR2 DEFAULT NULL,    --jarora
314       			p_truncate                      IN         NUMBER DEFAULT 1 --sopjarora
315       			)
316       IS
317 
318          /*** CURSORS ***/
319 
320          CURSOR c_get_table (p_lookup_type	VARCHAR2,
321                              p_lookup_code   	VARCHAR2)
322          IS
323             SELECT meaning                            --jarora
324             FROM fnd_lookup_values_vl
325             WHERE lookup_type = p_lookup_type
326               AND lookup_code = p_lookup_code;
327 
328         CURSOR c_get_dm_schema --jarora
329          IS
330          SELECT owner
331          FROM dba_objects
332          WHERE  owner = owner
333             AND object_type = 'TABLE'
334             AND object_name = 'MDP_MATRIX'
335          ORDER BY created desc;
336 
337         v_applsys_schema    VARCHAR2(32);  --jarora
338 
339 
340          /*** LOCAL VARIABLES ****/
341 
342             x_errbuf		VARCHAR2(200)	:= NULL;
343             x_errbuf1		VARCHAR2(200)	:= NULL;
344             x_retcode		VARCHAR2(100)	:= NULL;
345             x_retcode1		VARCHAR2(100)	:= NULL;
346 
347           --  x_RMA_type          VARCHAR2(5000);
348             x_dest_table	VARCHAR2(100)    := NULL;
349 
350             x_sql		VARCHAR2(1000)  := NULL;
351 
352             x_from_date		DATE 		:= NULL;
353             x_to_date		DATE		:= NULL;
354 
355             x_instance_code     VARCHAR2(30)	:= NULL;
356             x_apps_ver		NUMBER		:= NULL;
357             x_dgmt		NUMBER		:= NULL;
358             x_instance_type     NUMBER		:= NULL;
359             x_dm_time_level	NUMBER		:= NULL;
360             x_dm_time_bucket    VARCHAR2(30)    := NULL;
361             g_dblink            varchar2(30)    := null;
362             x_dem_schema	VARCHAR2(50)	:= NULL; --jarora
363 
364             lv_retval           BOOLEAN;           --jarora
365             lv_dummy1           VARCHAR2(32);
366             lv_dummy2           VARCHAR2(32);
367 
368             x_series_id         NUMBER         := NULL; --jarora
369 
370             l_position          NUMBER         := 0;    --sopjarora
371             l_length            NUMBER         := 0;    --sopjarora
372             l_table_name       VARCHAR2(100)  := NULL; --sopjarora
373             l_series_name       VARCHAR2(100)  := NULL; --sopjarora
374 
375       BEGIN
376 
377          msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
378 
379          /* Get the db link to the source instance */
380          msd_dem_common_utilities.get_dblink (
381          			x_errbuf,
382          			x_retcode,
383          			p_sr_instance_id,
384          			g_dblink);
385 
386          IF (x_retcode = '-1')
387          THEN
388             retcode := -1;
389             errbuf := x_errbuf;
390             msd_dem_common_utilities.log_message ('Error(1): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
391             RETURN;
392          END IF;
393 
394          /* Calling procedure to push the profile values, collection enabled orgs and
395           * the time data in the source instance, which will be used in the source
396           * views.
397          */
398 
399          MSD_DEM_PUSH_SETUP_PARAMETERS.PUSH_SETUP_PARAMETERS(x_errbuf, x_retcode, p_sr_instance_id, p_collection_group);
400 
401 
402 
403          /* VALIDATION OF INPUT PARAMETERS - BEGIN */
404 
405          msd_dem_common_utilities.log_debug ('Begin validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
406 
407 
408          /* Show Warning if collection method is Refresh and a date range filter is specified */
409          IF (    p_collection_method = 1
410              AND (   p_from_date IS NOT NULL
411                   OR p_to_date IS NOT NULL
412                   OR p_collection_window IS NOT NULL))
413          THEN
414             x_retcode := 1;
415             x_errbuf  := 'Date Range filters are ignored in ''Refresh'' collections';
416             msd_dem_common_utilities.log_message ('Warning(1): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
417             msd_dem_common_utilities.log_message (x_errbuf);
418          END IF;
419 
420 
421          /* Show Warning if collection method is net change, date range type is Rolling and from date and to date are specified */
422          IF (    p_collection_method = 2
423              AND p_date_range_type = 2
424              AND (   p_from_date IS NOT NULL
425                   OR p_to_date IS NOT NULL))
426          THEN
427             x_retcode := 1;
428             x_errbuf  := 'The ''Date From'' and ''Date To'' fields are ignored if ''Rolling'' date range type is selected.';
429             msd_dem_common_utilities.log_message ('Warning(2): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
430             msd_dem_common_utilities.log_message (x_errbuf);
431          END IF;
432 
433 
434          /* Show Warning if collection method is net change, date range type is Absolute and history collection window is specified */
435          IF (    p_collection_method = 2
436              AND p_date_range_type = 1
437              AND p_collection_window IS NOT NULL)
438          THEN
439             x_retcode := 1;
440             x_errbuf  := 'The ''History Collection Window'' field is ignored if ''Absolute'' date range type is selected.';
441             msd_dem_common_utilities.log_message ('Warning(3): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
442             msd_dem_common_utilities.log_message (x_errbuf);
443          END IF;
444 
445 
446          /* Error if collection method is net change, date range type is Rolling and history collection window is not specified */
447          IF (    p_collection_method = 2
448              AND p_date_range_type = 2
449              AND p_collection_window IS NULL)
450          THEN
451             retcode := -1;
452             errbuf  := 'The ''History Collection Window'' field cannot be NULL, if ''Rolling'' date range type is selected.';
453             msd_dem_common_utilities.log_message ('Error(3): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
454             msd_dem_common_utilities.log_message (errbuf);
455             RETURN;
456          END IF;
457 
458 
459          /* Error if collection method is net change, date range type is Absolute and from date and to date are not specified */
460          IF (    p_collection_method = 2
461              AND p_date_range_type = 1
462              AND (   p_from_date IS NULL
463                   OR p_to_date IS NULL))
464          THEN
465             retcode := -1;
466             errbuf  := 'The ''Date From'' and ''Date To'' fields cannot be NULL, if ''Absolute'' date range type is selected.';
467             msd_dem_common_utilities.log_message ('Error(4): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
468             msd_dem_common_utilities.log_message (errbuf);
469             RETURN;
470          END IF;
471 
472 
473          msd_dem_common_utilities.log_debug ('End validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
474 
475          /* VALIDATION OF INPUT PARAMETERS - END */
476 
477 
478          /* Get the start date and end dates for collection */
479 
480          msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
481 
482          IF (p_collection_method = 1) /* Refresh*/
483          THEN
484             x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
485             x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
486          ELSE /* Net Change */
487             IF (p_date_range_type = 1) /* Absolute*/
488             THEN
489 
490                IF (p_from_date IS NULL)
491                THEN
492                   x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');
493                ELSE
494                   x_from_date := FND_DATE.canonical_to_date(p_from_date);
495                END IF;
496 
497                IF (p_to_date IS NULL)
498                THEN
499                   x_to_date := to_date('31/12/4000', 'DD/MM/YYYY');
500                ELSE
501                   x_to_date := FND_DATE.canonical_to_date(p_to_date);
502                END IF;
503 
504                /* Error if p_from_date is greater than p_to_date */
505                IF (x_from_date > x_to_date)
506                THEN
507                   retcode := -1;
508                   errbuf  := 'From Date should not be greater than To Date.';
509                   msd_dem_common_utilities.log_message ('Error(6): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
510                   msd_dem_common_utilities.log_message (errbuf);
511                   RETURN;
512                END IF;
513 
514             ELSE /* Rolling */
515 
516                IF (p_collection_window < 0)
517                THEN
518                   retcode := -1;
519                   errbuf  := 'History Collection Window must be a positive number.';
520                   msd_dem_common_utilities.log_message ('Error(7): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
521                   msd_dem_common_utilities.log_message (errbuf);
522                   RETURN;
523                ELSE
524                   x_to_date   := trunc(sysdate);
525                   x_from_date := x_to_date - p_collection_window + 1;
526                END IF;
527             END IF;
528          END IF;
529 
530          msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
531          msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
532 
533          /* Get the instance info */
534          msd_dem_common_utilities.get_instance_info (
535          			x_errbuf1,
536          			x_retcode1,
537          			x_instance_code,
538          			x_apps_ver,
539          			x_dgmt,
540          			x_instance_type,
541          			p_sr_instance_id);
542 
543          IF (x_retcode1 = '-1')
544          THEN
545             retcode := -1;
546             errbuf  := x_errbuf1;
547             msd_dem_common_utilities.log_message ('Error(8): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
548             msd_dem_common_utilities.log_message ('Unable to get instance info.');
549             RETURN;
550          END IF;
551 
552          msd_dem_common_utilities.log_debug ('End get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
553 
554          select instr(p_entity_name,':'),length(p_entity_name)  --sopjarora
555                 INTO l_position,l_length
556          FROM DUAL;
557 
558          IF l_position <> 0                                  --sopjarora
559          THEN
560            select substr(p_entity_name,1,l_position - 1),substr(p_entity_name,l_position + 1,l_length)
561                 INTO l_table_name,l_series_name
562            FROM DUAL;
563          ELSE
564            l_table_name := p_entity_name;
565            l_series_name := p_entity_name;
566          END IF;
567 
568          /* Get the return history staging table name */ --jarora
569          OPEN c_get_table ('MSD_DEM_TABLES', l_table_name); --sopjarora
570          FETCH c_get_table INTO x_dest_table;
571          CLOSE c_get_table;
572 
573          OPEN c_get_dm_schema;                       --jarora
574          FETCH c_get_dm_schema INTO x_dem_schema;
575          CLOSE c_get_dm_schema;
576 
577 
578          IF (x_dest_table is NULL)
579          THEN
580             retcode := -1;
581             errbuf  := 'Unable to find the return history staging tables.';
582             msd_dem_common_utilities.log_message ('Error(9): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
583             msd_dem_common_utilities.log_message (errbuf);
584             RETURN;
585          END IF;
586 
587          /* Demantra is Installed */
588          IF (x_dem_schema IS NOT NULL)
589          THEN
590 
591 		 IF(p_entity_name = 'MSD_DEM_SRP_RETURN_HISTORY' OR
592 		 p_entity_name = 'MSD_DEM_INS_BASE_HISTORY' OR
593 		 p_entity_name = 'MSD_DEM_DPT_REP_USG_HISTORY' OR
594 		 p_entity_name = 'MSD_DEM_FLD_SER_USG_HISTORY')
595 		 THEN
596             lv_retval := fnd_installation.get_app_info('MSD',lv_dummy1,lv_dummy2,v_applsys_schema);
597             x_dest_table := v_applsys_schema || '.' || x_dest_table;
598          ELSE
599             x_dest_table := x_dem_schema || '.' || x_dest_table;
600          END IF;
601 
602          ELSE
603             lv_retval := fnd_installation.get_app_info('MSD',lv_dummy1,lv_dummy2,v_applsys_schema);
604             x_dest_table := v_applsys_schema || '.' || x_dest_table;
605 
606          END IF; --jarora
607 
608          msd_dem_common_utilities.log_message (' Collect History Data - Actions');
609          msd_dem_common_utilities.log_message ('--------------------------------');
610          msd_dem_common_utilities.log_message (' ');
611 
612          msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));
613          msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR)   - ' || to_char(x_to_date, 'DD/MM/RRRR'));
614 
615          msd_dem_common_utilities.log_debug ('Begin delete from history staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));         --jarora
616 
617          IF p_truncate = 1 --sopjarora
618          THEN
619           /* Truncate the history staging table */ --jarora
620           msd_dem_common_utilities.log_message ('Deleting data from history staging table - ' || x_dest_table);   --jarora
621           x_sql := 'DELETE FROM ' || x_dest_table;   --amitku
622 	  EXECUTE IMMEDIATE x_sql;
623 	  msd_dem_common_utilities.log_debug ('End delete from history staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));    --jarora
624 	 END IF;
625 
626 
627          msd_dem_common_utilities.log_debug ('Begin get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
628 
629          /* Get the lowest time bucket */
630          IF (x_dem_schema IS NOT NULL) /* Demantra is Installed */ --jarora
631          THEN
632            x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
633          ELSE
634            x_dm_time_bucket := 'DAY';
635          END IF;                            --jarora
636 
637          IF (x_dm_time_bucket IS NULL)
638          THEN
639             retcode := -1;
640             errbuf  := 'Unable to get lowest time bucket';
641             msd_dem_common_utilities.log_message ('Error(10): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
642             msd_dem_common_utilities.log_message (errbuf);
643             RETURN;
644          ELSIF (upper(x_dm_time_bucket) = 'DAY')
645          THEN
646             x_dm_time_level := 1;
647          ELSE
648             x_dm_time_level := 2;
649          END IF;
650 
651          msd_dem_common_utilities.log_debug ('End get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
652 
653          /* Collect history series */ --jarora
654          msd_dem_common_utilities.log_debug ('Begin collect History - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));  --jarora
655 
656          IF p_entity_name = 'MSD_DEM_RETURN_HISTORY'
657          THEN
658            x_series_id := MSD_DEM_COMMON_UTILITIES.C_RETURN_HISTORY;
659 
660          ELSIF p_entity_name = 'MSD_DEM_SRP_RETURN_HISTORY'
661          THEN
662            x_series_id := MSD_DEM_COMMON_UTILITIES.C_SRP_RETURN_HISTORY;
663 
664          ELSIF p_entity_name = 'MSD_DEM_DPT_REP_USG_HISTORY'
665          THEN
666            x_series_id := MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_DR;
667 
668          ELSIF p_entity_name = 'MSD_DEM_FLD_SER_USG_HISTORY'
669          THEN
670            x_series_id := MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_FS;
671 
672          ELSIF p_entity_name = 'MSD_DEM_INS_BASE_HISTORY'
673          THEN
674            x_series_id := MSD_DEM_COMMON_UTILITIES.C_INSTALL_BASE_HISTORY;
675 
676          ELSIF p_entity_name = 'BIIO_SCI_BACKLOG:MSD_TOTAL_BACKLOG' --sopjarora
677          THEN
678            x_series_id := MSD_DEM_COMMON_UTILITIES.C_TOTAL_BACKLOG;
679 
680          ELSIF p_entity_name = 'BIIO_SCI_BACKLOG:MSD_PAST_DUE_BACKLOG'
681          THEN
682            x_series_id := MSD_DEM_COMMON_UTILITIES.C_PAST_DUE_BACKLOG;
683 
684          ELSIF p_entity_name = 'BIIO_SCI:MSD_ON_HAND_INVENTORY'
685          THEN
686            x_series_id := MSD_DEM_COMMON_UTILITIES.C_ON_HAND_INVENTORY;
687 
688          ELSIF p_entity_name = 'BIIO_SCI:MSD_ACTUAL_PRODUCTION'
689          THEN
690            x_series_id := MSD_DEM_COMMON_UTILITIES.C_ACTUAL_PRODUCTION;
691 
692          ELSE
693            retcode := -1;
694            msd_dem_common_utilities.log_message ('Error(10.1): MSD_DEM_COLLECT_RETURN_HISTORY.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
695            msd_dem_common_utilities.log_message ('Error - invalid history series');
696            RETURN;
697 
698          END IF;                                        --jarora
699 
700             collect_data (
701            		x_errbuf1,
702            		x_retcode1,
703            		x_series_id,                     --jarora
704            		x_dest_table,
705            		x_dm_time_level,
706            		p_sr_instance_id,
707            		x_apps_ver,
708            		x_instance_type,
709            		p_collection_group,
710            		p_collection_method,
711            		x_from_date,
712            		x_to_date);
713 
714             IF (x_retcode1 = -1)
715             THEN
716                retcode := -1;
717                errbuf  := x_errbuf1;
718                msd_dem_common_utilities.log_message ('Error(11): MSD_DEM_COLLECT_RETURN_HISTORY.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
719                msd_dem_common_utilities.log_message ('Error while collecting History');  --jarora
720                RETURN;
721             END IF;
722 
723          msd_dem_common_utilities.log_debug ('End collect History - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); --jarora
724 
725          COMMIT;
726 
727          msd_dem_common_utilities.log_debug ('Begin Analyze history staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));  --jarora
728 
729          msd_dem_collect_history_data.analyze_table (
730            		x_errbuf1,
731            		x_retcode1,
732          	  	x_dest_table);
733 
734          msd_dem_common_utilities.log_debug ('End Analyze history staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));  --jarora
735 
736          IF (x_retcode1 = -1)
737          THEN
738             retcode := -1;
739             errbuf  := x_errbuf1;
740             msd_dem_common_utilities.log_message ('Error(19): msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
741             msd_dem_common_utilities.log_message ('Error while analyzing history staging table. ');    --jarora
742             RETURN;
743          END IF;
744 
745          retcode := x_retcode;
746          errbuf  := x_errbuf;
747          msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
748 
749       EXCEPTION
750          WHEN OTHERS THEN
751             retcode := -1 ;
752 	    errbuf  := substr(SQLERRM,1,150);
753 	    msd_dem_common_utilities.log_message ('Exception: msd_dem_collect_return_history.collect_return_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
754 	    msd_dem_common_utilities.log_message (errbuf);
755 	    RETURN;
756 
757       END COLLECT_RETURN_HISTORY_DATA;
758 
759 END MSD_DEM_COLLECT_RETURN_HISTORY;