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.

APPS.MSD_DEM_COLLECT_RETURN_HISTORY dependencies on MSD_DEM_COMMON_UTILITIES

Line 73: msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_return_history.collect_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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

Line 77: IF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_RETURN_HISTORY

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 */

Line 78: OR p_series_id = MSD_DEM_COMMON_UTILITIES.C_RETURN_HISTORY) --jarora

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;

Line 86: ELSIF ( p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_DR)

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;

Line 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'));

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:

Line 109: msd_dem_common_utilities.log_message (errbuf);

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 */

Line 127: msd_dem_common_utilities.log_debug ('Begin collect discrete history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); --jarora

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

Line 129: IF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_RETURN_HISTORY

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

Line 130: OR p_series_id = MSD_DEM_COMMON_UTILITIES.C_RETURN_HISTORY) --jarora

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 ||')';

Line 139: ELSIF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_DR)

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 ||')';

Line 162: || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');

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'') '

Line 176: IF (p_series_id IN (MSD_DEM_COMMON_UTILITIES.C_ON_HAND_INVENTORY,

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))

Line 177: MSD_DEM_COMMON_UTILITIES.C_TOTAL_BACKLOG,

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

Line 178: MSD_DEM_COMMON_UTILITIES.C_PAST_DUE_BACKLOG,

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');

Line 179: MSD_DEM_COMMON_UTILITIES.C_ACTUAL_PRODUCTION,

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;

Line 180: MSD_DEM_COMMON_UTILITIES.C_INSTALL_BASE_HISTORY))

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:

Line 194: || '$C_MASTER_ORG#' || msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');

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'') '

Line 209: IF (p_series_id IN (MSD_DEM_COMMON_UTILITIES.C_ON_HAND_INVENTORY,

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';

Line 210: MSD_DEM_COMMON_UTILITIES.C_TOTAL_BACKLOG,

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;

Line 211: MSD_DEM_COMMON_UTILITIES.C_PAST_DUE_BACKLOG))

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:

Line 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'));

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:

Line 234: msd_dem_common_utilities.log_message (errbuf);

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:

Line 239: msd_dem_common_utilities.log_debug ('Query - ');

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:

Line 240: msd_dem_common_utilities.log_debug (x_large_sql);

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

Line 242: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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;

Line 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'));

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;

Line 252: msd_dem_common_utilities.log_message (errbuf);

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:

Line 253: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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'));

Line 257: msd_dem_common_utilities.log_debug ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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,

Line 265: IF ( p_series_id = MSD_DEM_COMMON_UTILITIES.C_RETURN_HISTORY

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'));

Line 266: AND msd_dem_common_utilities.is_use_new_site_format = 0)

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:

Line 269: msd_dem_common_utilities.log_debug ('Start Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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,

Line 279: msd_dem_common_utilities.log_debug ('End Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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

Line 283: msd_dem_common_utilities.log_debug ('End collect discrete history - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); --jarora

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

Line 287: IF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_RETURN_HISTORY

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;

Line 288: OR p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_RETURN_HISTORY)

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:

Line 293: ELSIF (p_series_id = MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_DR)

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

Line 301: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_return_history.collect_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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 ;

Line 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'));

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;

Line 308: msd_dem_common_utilities.log_message (errbuf);

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:

Line 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'));

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,

Line 392: msd_dem_common_utilities.get_dblink (

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);

Line 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'));

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

Line 417: msd_dem_common_utilities.log_debug ('Begin validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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

Line 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'));

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:

Line 429: msd_dem_common_utilities.log_message (x_errbuf);

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 */

Line 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'));

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:

Line 442: msd_dem_common_utilities.log_message (x_errbuf);

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 */

Line 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'));

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:

Line 454: msd_dem_common_utilities.log_message (x_errbuf);

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 */

Line 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'));

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:

Line 466: msd_dem_common_utilities.log_message (errbuf);

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:

Line 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'));

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:

Line 480: msd_dem_common_utilities.log_message (errbuf);

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:

Line 485: msd_dem_common_utilities.log_debug ('End validation of inputs parameters - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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:

Line 492: msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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');

Line 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'));

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:

Line 522: msd_dem_common_utilities.log_message (errbuf);

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 */

Line 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'));

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);

Line 533: msd_dem_common_utilities.log_message (errbuf);

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;

Line 542: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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 (

Line 543: msd_dem_common_utilities.log_debug ('Begin get instance info - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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,

Line 546: msd_dem_common_utilities.get_instance_info (

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,

Line 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'));

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:

Line 560: msd_dem_common_utilities.log_message ('Unable to get instance info.');

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'));

Line 564: msd_dem_common_utilities.log_debug ('End get instance info - ' || 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;

Line 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'));

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:

Line 599: msd_dem_common_utilities.log_message (errbuf);

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 */

Line 624: msd_dem_common_utilities.log_message (' Collect History Data - Actions');

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'));

Line 625: msd_dem_common_utilities.log_message ('--------------------------------');

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'));

Line 626: msd_dem_common_utilities.log_message (' ');

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:

Line 628: msd_dem_common_utilities.log_message ('Date From (DD/MM/RRRR) - ' || to_char(x_from_date, 'DD/MM/RRRR'));

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:

Line 629: msd_dem_common_utilities.log_message ('Date To (DD/MM/RRRR) - ' || to_char(x_to_date, 'DD/MM/RRRR'));

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 */

Line 631: msd_dem_common_utilities.log_debug ('Begin delete from history staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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

Line 647: msd_dem_common_utilities.log_debug ('Del/Trn sql - ' || x_sql);

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

Line 655: msd_dem_common_utilities.log_debug ('Trn sql - ' || x_sql);

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'));

Line 659: msd_dem_common_utilities.log_debug ('End delete from history staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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:

Line 662: msd_dem_common_utilities.log_debug ('Begin get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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

Line 667: x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;

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:

Line 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'));

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

Line 677: msd_dem_common_utilities.log_message (errbuf);

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;

Line 686: msd_dem_common_utilities.log_debug ('End get dm time level - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

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:

Line 689: msd_dem_common_utilities.log_debug ('Begin collect History - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); --jarora

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;

Line 693: x_series_id := MSD_DEM_COMMON_UTILITIES.C_RETURN_HISTORY;

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;

Line 697: x_series_id := MSD_DEM_COMMON_UTILITIES.C_SRP_RETURN_HISTORY;

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;

Line 701: x_series_id := MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_DR;

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;

Line 705: x_series_id := MSD_DEM_COMMON_UTILITIES.C_SRP_USG_HISTORY_FS;

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;

Line 709: x_series_id := MSD_DEM_COMMON_UTILITIES.C_INSTALL_BASE_HISTORY;

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;

Line 713: x_series_id := MSD_DEM_COMMON_UTILITIES.C_TOTAL_BACKLOG;

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;

Line 717: x_series_id := MSD_DEM_COMMON_UTILITIES.C_PAST_DUE_BACKLOG;

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;

Line 721: x_series_id := MSD_DEM_COMMON_UTILITIES.C_ON_HAND_INVENTORY;

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;

Line 725: x_series_id := MSD_DEM_COMMON_UTILITIES.C_ACTUAL_PRODUCTION;

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'));

Line 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'));

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

Line 730: msd_dem_common_utilities.log_message ('Error - invalid history series');

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:

Line 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'));

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:

Line 754: msd_dem_common_utilities.log_message ('Error while collecting History'); --jarora

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

Line 758: msd_dem_common_utilities.log_debug ('End collect History - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); --jarora

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

Line 762: msd_dem_common_utilities.log_debug ('Begin Analyze history staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); --jarora

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,

Line 769: msd_dem_common_utilities.log_debug ('End Analyze history staging table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS')); --jarora

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;

Line 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'));

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:

Line 776: msd_dem_common_utilities.log_message ('Error while analyzing history staging table. '); --jarora

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;

Line 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'));

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 ;

Line 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'));

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;

Line 789: msd_dem_common_utilities.log_message (errbuf);

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: