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