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_CTO dependencies on MSD_DEM_CTO

Line 1: PACKAGE BODY MSD_DEM_CTO AS

1: PACKAGE BODY MSD_DEM_CTO AS
2: /* $Header: msddemctob.pls 120.18 2012/02/22 07:15:32 nallkuma noship $ */
3:
4:
5: /*** PUBLIC PROCEDURES ***

Line 31: msd_dem_common_utilities.log_debug ('Entering: msd_dem_cto.populate_staging_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

27:
28: x_dem_schema VARCHAR2(100) := NULL;
29: BEGIN
30:
31: msd_dem_common_utilities.log_debug ('Entering: msd_dem_cto.populate_staging_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
32:
33: /* Log the parameters */
34: msd_dem_common_utilities.log_debug (' Entity Name - ' || p_entity_name);
35: msd_dem_common_utilities.log_debug (' Instance ID - ' || to_char(p_sr_instance_id));

Line 49: msd_dem_common_utilities.log_message ('Error(1) in msd_dem_cto.populate_staging_table - '

45: msd_dem_common_utilities.log_debug('Get the Demantra Schema Name');
46: x_dem_schema := fnd_profile.value('MSD_DEM_SCHEMA');
47: IF (x_dem_schema IS NULL)
48: THEN
49: msd_dem_common_utilities.log_message ('Error(1) in msd_dem_cto.populate_staging_table - '
50: || 'Unable to get value for Profile MSD_DEM: Schema');
51: retcode := -1;
52: RETURN;
53: END IF;

Line 65: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

61: x_include_dependent_demand := fnd_profile.value ('MSD_DEM_INCLUDE_DEPENDENT_DEMAND');
62:
63: IF (x_include_dependent_demand = 2) /* Profile is set to No */
64: THEN
65: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
66: || 'Profile MSD_DEM: Include Dependent Demand is set to No. '
67: || 'Hence no action taken. Exiting Normally.');
68: retcode := 0;
69: RETURN;

Line 72: msd_dem_common_utilities.log_message ('Error(2) in msd_dem_cto.populate_staging_table - '

68: retcode := 0;
69: RETURN;
70: ELSIF (x_include_dependent_demand IS NULL)
71: THEN
72: msd_dem_common_utilities.log_message ('Error(2) in msd_dem_cto.populate_staging_table - '
73: || 'Unable to get value for Profile MSD_DEM: Include Dependent Demand');
74: retcode := -1;
75: RETURN;
76: END IF;

Line 79: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

75: RETURN;
76: END IF;
77:
78:
79: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
80: || ' Pre-Process - Start ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
81:
82: IF (p_entity_name = 'EQ_BIIO_CTO_DATA')
83: THEN

Line 85: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

81:
82: IF (p_entity_name = 'EQ_BIIO_CTO_DATA')
83: THEN
84:
85: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
86: || 'Truncating table BIIO_CTO_DATA_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
87: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_DATA_ERR';
88:
89: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 89: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

85: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
86: || 'Truncating table BIIO_CTO_DATA_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
87: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_DATA_ERR';
88:
89: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
90: || 'Truncating table BIIO_CTO_DATA ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
91: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_DATA';
92:
93: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 93: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

89: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
90: || 'Truncating table BIIO_CTO_DATA ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
91: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_DATA';
92:
93: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
94: || 'Truncating table BIIO_CTO_BASE_MODEL_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
95: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_BASE_MODEL_ERR';
96:
97: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 97: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

93: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
94: || 'Truncating table BIIO_CTO_BASE_MODEL_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
95: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_BASE_MODEL_ERR';
96:
97: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
98: || 'Truncating table BIIO_CTO_BASE_MODEL ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
99: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_BASE_MODEL';
100:
101: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 101: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

97: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
98: || 'Truncating table BIIO_CTO_BASE_MODEL ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
99: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_BASE_MODEL';
100:
101: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
102: || 'Truncating table BIIO_CTO_POPULATION_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
103: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_POPULATION_ERR';
104:
105: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 105: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

101: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
102: || 'Truncating table BIIO_CTO_POPULATION_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
103: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_POPULATION_ERR';
104:
105: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
106: || 'Truncating table BIIO_CTO_POPULATION ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
107: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_POPULATION';
108:
109: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 109: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

105: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
106: || 'Truncating table BIIO_CTO_POPULATION ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
107: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_POPULATION';
108:
109: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
110: || 'Truncating table BIIO_CTO_LEVEL_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
111: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_LEVEL_ERR';
112:
113: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 113: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

109: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
110: || 'Truncating table BIIO_CTO_LEVEL_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
111: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_LEVEL_ERR';
112:
113: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
114: || 'Deleting all data from BIIO_CTO_LEVEL ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
115: EXECUTE IMMEDIATE 'DELETE FROM ' || x_dem_schema || '.BIIO_CTO_LEVEL';
116:
117: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 117: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

113: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
114: || 'Deleting all data from BIIO_CTO_LEVEL ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
115: EXECUTE IMMEDIATE 'DELETE FROM ' || x_dem_schema || '.BIIO_CTO_LEVEL';
116:
117: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
118: || 'Truncating table BIIO_CTO_CHILD_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
119: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_CHILD_ERR';
120:
121: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 121: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

117: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
118: || 'Truncating table BIIO_CTO_CHILD_ERR ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
119: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x_dem_schema || '.BIIO_CTO_CHILD_ERR';
120:
121: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
122: || 'Deleting all data from BIIO_CTO_CHILD ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
123: EXECUTE IMMEDIATE 'DELETE FROM ' || x_dem_schema || '.BIIO_CTO_CHILD';
124: COMMIT;
125:

Line 138: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

134:
135: ELSIF (p_entity_name = 'EQ_BIIO_CTO_DATA_EPP')
136: THEN
137:
138: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
139: || 'Running populate staging tables for EQ_SALES_TMPL_ITEM_OPTIONS first.' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
140:
141: msd_dem_common_utilities.log_debug ('Start EQ_SALES_TMPL_ITEM_OPTIONS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
142:

Line 154: msd_dem_common_utilities.log_message ('Error(4) in msd_dem_cto.populate_staging_table - '

150: msd_dem_common_utilities.log_debug ('End EQ_SALES_TMPL_ITEM_OPTIONS - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
151:
152: IF (retcode = -1)
153: THEN
154: msd_dem_common_utilities.log_message ('Error(4) in msd_dem_cto.populate_staging_table - '
155: || 'Error in call to msd_dem_query_utilities.execute_query');
156: msd_dem_common_utilities.log_message(errbuf);
157: RETURN;
158: ELSE

Line 163: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

159: msd_dem_common_utilities.log_message ('Query EQ_SALES_TMPL_ITEM_OPTIONS executed successfully.');
160: END IF;
161:
162: ELSE
163: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
164: || 'No Pre-Process Required.');
165: END IF;
166:
167: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 167: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

163: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
164: || 'No Pre-Process Required.');
165: END IF;
166:
167: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
168: || ' Pre-Process - End ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
169:
170:
171: msd_dem_common_utilities.log_debug('Calling msd_dem_query_utilities.execute_query');

Line 180: msd_dem_common_utilities.log_message ('Error(3) in msd_dem_cto.populate_staging_table - '

176: p_sr_instance_id,
177: NULL );
178: IF (retcode = -1)
179: THEN
180: msd_dem_common_utilities.log_message ('Error(3) in msd_dem_cto.populate_staging_table - '
181: || 'Error in call to msd_dem_query_utilities.execute_query');
182: msd_dem_common_utilities.log_message(errbuf);
183: RETURN;
184: ELSE

Line 188: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

184: ELSE
185: msd_dem_common_utilities.log_message ('Query ' || p_entity_name || ' executed successfully.');
186: END IF;
187:
188: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
189: || ' Post-Process - Start ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
190:
191: IF (p_entity_name = 'EQ_BIIO_CTO_DATA')
192: THEN

Line 194: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

190:
191: IF (p_entity_name = 'EQ_BIIO_CTO_DATA')
192: THEN
193:
194: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
195: || 'Deleting Dependent Demand History from T_SRC_SALES_TMPL ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
196: EXECUTE IMMEDIATE 'DELETE FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL'
197: || ' WHERE ebs_base_model_sr_pk IS NOT NULL '
198: || ' AND to_char(ebs_base_model_sr_pk) <> component_code ';

Line 199: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

195: || 'Deleting Dependent Demand History from T_SRC_SALES_TMPL ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
196: EXECUTE IMMEDIATE 'DELETE FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL'
197: || ' WHERE ebs_base_model_sr_pk IS NOT NULL '
198: || ' AND to_char(ebs_base_model_sr_pk) <> component_code ';
199: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
200: || to_char(SQL%ROWCOUNT) || ' rows deleted from T_SRC_SALES_TMPL');
201: COMMIT;
202:
203: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 203: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

199: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
200: || to_char(SQL%ROWCOUNT) || ' rows deleted from T_SRC_SALES_TMPL');
201: COMMIT;
202:
203: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
204: || 'Updating item code and site code in T_SRC_SALES_TMPL ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
205: msd_dem_query_utilities.execute_query (
206: errbuf,
207: retcode,

Line 235: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

231:
232: IF (msd_dem_common_utilities.is_use_new_site_format = 0)
233: THEN
234:
235: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
236: || 'Updating site codes to descriptive format in BIIO_CTO_DATA ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
237:
238: msd_dem_common_utilities.log_debug ('Start Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
239: msd_dem_update_level_codes.convert_site_code(

Line 252: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

248:
249: END IF;
250:
251: ELSE
252: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
253: || 'No Post-Process Required.');
254: END IF;
255:
256: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

Line 256: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

252: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
253: || 'No Post-Process Required.');
254: END IF;
255:
256: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
257: || ' Post-Process - End ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
258:
259: ELSE
260:

Line 267: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

263: x_include_dependent_demand := fnd_profile.value ('MSD_DEM_INCLUDE_DEPENDENT_DEMAND');
264:
265: IF (x_include_dependent_demand = 1) /* Profile is set to Yes */
266: THEN
267: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
268: || 'Profile MSD_DEM: Include Dependent Demand is set to Yes. '
269: || 'Hence no action taken. Exiting Normally.');
270: retcode := 0;
271: RETURN;

Line 274: msd_dem_common_utilities.log_message ('Error(4) in msd_dem_cto.populate_staging_table - '

270: retcode := 0;
271: RETURN;
272: ELSIF (x_include_dependent_demand IS NULL)
273: THEN
274: msd_dem_common_utilities.log_message ('Error(4) in msd_dem_cto.populate_staging_table - '
275: || 'Unable to get value for Profile MSD_DEM: Include Dependent Demand');
276: retcode := -1;
277: RETURN;
278: END IF;

Line 280: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

276: retcode := -1;
277: RETURN;
278: END IF;
279:
280: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
281: || ' Pre-Process - Start ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
282:
283: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
284: || ' Pre-Process - End ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 283: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

279:
280: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
281: || ' Pre-Process - Start ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
282:
283: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
284: || ' Pre-Process - End ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
285:
286:
287: msd_dem_common_utilities.log_debug('Calling msd_dem_query_utilities.execute_query');

Line 296: msd_dem_common_utilities.log_message ('Error(5) in msd_dem_cto.populate_staging_table - '

292: p_sr_instance_id,
293: NULL );
294: IF (retcode = -1)
295: THEN
296: msd_dem_common_utilities.log_message ('Error(5) in msd_dem_cto.populate_staging_table - '
297: || 'Error in call to msd_dem_query_utilities.execute_query');
298: msd_dem_common_utilities.log_message(errbuf);
299: RETURN;
300: ELSE

Line 304: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

300: ELSE
301: msd_dem_common_utilities.log_message ('Query ' || p_entity_name || ' executed successfully.');
302: END IF;
303:
304: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
305: || ' Post-Process - Start ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
306:
307: IF ( p_entity_name = 'EQ_SALES_TMPL_ITEM'
308: AND msd_dem_common_utilities.is_use_new_site_format = 0)

Line 325: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '

321: msd_dem_common_utilities.log_debug ('End Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
322:
323: END IF;
324:
325: msd_dem_common_utilities.log_message ('In msd_dem_cto.populate_staging_table - '
326: || ' Post-Process - End ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
327:
328: END IF;
329:

Line 330: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.populate_staging_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

326: || ' Post-Process - End ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
327:
328: END IF;
329:
330: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.populate_staging_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
331:
332: retcode := 0;
333:
334: EXCEPTION

Line 339: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_cto.populate_staging_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

335: WHEN OTHERS THEN
336: errbuf := substr(SQLERRM,1,150);
337: retcode := -1;
338:
339: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_cto.populate_staging_table - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
340: msd_dem_common_utilities.log_message (errbuf);
341: RETURN;
342:
343: END POPULATE_STAGING_TABLE;

Line 380: msd_dem_common_utilities.log_debug ('Entering: msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

376: x_add_days number := 0;
377:
378: BEGIN
379:
380: msd_dem_common_utilities.log_debug ('Entering: msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
381:
382: /* Log the parameters */
383: msd_dem_common_utilities.log_debug (' Instance ID - ' || to_char(p_sr_instance_id));
384:

Line 388: msd_dem_common_utilities.log_message ('In msd_dem_cto.collect_model_bom_components - '

384:
385: /* The procedure should only execute if profile MSD_DEM: Include Dependent Demand is set to yes. */
386: IF (fnd_profile.value('MSD_DEM_INCLUDE_DEPENDENT_DEMAND') = 2)
387: THEN
388: msd_dem_common_utilities.log_message ('In msd_dem_cto.collect_model_bom_components - '
389: || 'Profile MSD_DEM: Include Dependent Demand is set to No. '
390: || 'Hence no action taken. Exiting Normally.');
391: retcode := 0;
392: RETURN;

Line 409: msd_dem_common_utilities.log_message ('Error(1) in msd_dem_cto.collect_model_bom_components - '

405: 2,
406: 1);
407: IF (retcode = -1)
408: THEN
409: msd_dem_common_utilities.log_message ('Error(1) in msd_dem_cto.collect_model_bom_components - '
410: || 'Error in call to msd_dem_query_utilities.truncate_table');
411: msd_dem_common_utilities.log_message(errbuf);
412: RETURN;
413: END IF;

Line 669: msd_dem_common_utilities.log_message ('Error(2): msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

665:
666: IF (x_validation_org_id IS NULL)
667: THEN
668: retcode := -1;
669: msd_dem_common_utilities.log_message ('Error(2): msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
670: msd_dem_common_utilities.log_message ('Validation Org Id is Null.');
671: RETURN;
672: END IF;
673:

Line 890: /* Truncate the table MSD_DEM_CTO_BOM */

886: END IF;
887:
888: /* CTO Performance Fix - Store the Model Bom Components Information in a table */
889:
890: /* Truncate the table MSD_DEM_CTO_BOM */
891: msd_dem_common_utilities.log_debug ('Truncate table MSD_DEM_CTO_BOM...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
892: msd_dem_query_utilities.truncate_table (errbuf, retcode, 'MSD_DEM_CTO_BOM', 2);
893: IF (retcode = -1)
894: THEN

Line 891: msd_dem_common_utilities.log_debug ('Truncate table MSD_DEM_CTO_BOM...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

887:
888: /* CTO Performance Fix - Store the Model Bom Components Information in a table */
889:
890: /* Truncate the table MSD_DEM_CTO_BOM */
891: msd_dem_common_utilities.log_debug ('Truncate table MSD_DEM_CTO_BOM...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
892: msd_dem_query_utilities.truncate_table (errbuf, retcode, 'MSD_DEM_CTO_BOM', 2);
893: IF (retcode = -1)
894: THEN
895:

Line 892: msd_dem_query_utilities.truncate_table (errbuf, retcode, 'MSD_DEM_CTO_BOM', 2);

888: /* CTO Performance Fix - Store the Model Bom Components Information in a table */
889:
890: /* Truncate the table MSD_DEM_CTO_BOM */
891: msd_dem_common_utilities.log_debug ('Truncate table MSD_DEM_CTO_BOM...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
892: msd_dem_query_utilities.truncate_table (errbuf, retcode, 'MSD_DEM_CTO_BOM', 2);
893: IF (retcode = -1)
894: THEN
895:
896: retcode := -1;

Line 898: msd_dem_common_utilities.log_message ('Error(3): msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

894: THEN
895:
896: retcode := -1;
897: errbuf := substr(SQLERRM,1,150);
898: msd_dem_common_utilities.log_message ('Error(3): msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
899: msd_dem_common_utilities.log_message ('Error in call to msd_dem_query_utilities.truncate_table for truncating MSD_DEM_CTO_BOM');
900: RETURN;
901:
902: END IF;

Line 899: msd_dem_common_utilities.log_message ('Error in call to msd_dem_query_utilities.truncate_table for truncating MSD_DEM_CTO_BOM');

895:
896: retcode := -1;
897: errbuf := substr(SQLERRM,1,150);
898: msd_dem_common_utilities.log_message ('Error(3): msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
899: msd_dem_common_utilities.log_message ('Error in call to msd_dem_query_utilities.truncate_table for truncating MSD_DEM_CTO_BOM');
900: RETURN;
901:
902: END IF;
903:

Line 913: msd_dem_common_utilities.log_debug ('Populating table MSD_DEM_CTO_BOM');

909: END IF;
910:
911:
912: /* Populate the final bom table */
913: msd_dem_common_utilities.log_debug ('Populating table MSD_DEM_CTO_BOM');
914: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
915: x_num_rows := 0;
916:
917: -- Bug#13716090 added new columns top_ato_model_id, ato_forecast_control

Line 918: INSERT /+ APPEND NOLOGGING / INTO MSD_DEM_CTO_BOM

914: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
915: x_num_rows := 0;
916:
917: -- Bug#13716090 added new columns top_ato_model_id, ato_forecast_control
918: INSERT /*+ APPEND NOLOGGING */ INTO MSD_DEM_CTO_BOM
919: (CTO_CODE, CTO_CHILD_CODE, CTO_PARENT_CODE, BASE_MODEL_CODE, PARENT_ITEM_CODE, OPTION_CODE, ORG_CODE,
920: EFFECTIVITY_DATE, DISABLE_DATE, PLNG_PCT_EXISTING, CTO_TYPE,
921: BASE_MODEL_ID, TOP_ATO_MODEL_ID, PARENT_ITEM_ID, OPTION_ID,
922: BASE_MODEL_SR_ID, PARENT_ITEM_SR_ID, OPTION_SR_ID,

Line 942: x_sql := 'MERGE into MSD_DEM_CTO_BOM a ' ||

938: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
939:
940: -- Bug#12315455
941: -- Retain population from date for members already in demantra
942: x_sql := 'MERGE into MSD_DEM_CTO_BOM a ' ||
943: ' using ( ' ||
944: ' select mdcb.cto_code, tecd.from_date ' ||
945: ' from MSD_DEM_CTO_BOM mdcb, '||x_dem_schema|| '.t_ep_cto tec, ' ||x_dem_schema|| '.t_ep_cto_dates tecd ' ||
946: ' where tec.t_ep_cto_code = mdcb.cto_code ' ||

Line 945: ' from MSD_DEM_CTO_BOM mdcb, '||x_dem_schema|| '.t_ep_cto tec, ' ||x_dem_schema|| '.t_ep_cto_dates tecd ' ||

941: -- Retain population from date for members already in demantra
942: x_sql := 'MERGE into MSD_DEM_CTO_BOM a ' ||
943: ' using ( ' ||
944: ' select mdcb.cto_code, tecd.from_date ' ||
945: ' from MSD_DEM_CTO_BOM mdcb, '||x_dem_schema|| '.t_ep_cto tec, ' ||x_dem_schema|| '.t_ep_cto_dates tecd ' ||
946: ' where tec.t_ep_cto_code = mdcb.cto_code ' ||
947: ' and tecd.t_ep_cto_id = tec.t_ep_cto_id ' ||
948: ' and tecd.from_date < mdcb.cto_start_date ) b '||
949: ' on ( ' ||

Line 954: msd_dem_common_utilities.log_debug ('Updating MSD_DEM_CTO_BOM to retain effective from date for members in demantra');

950: ' a.cto_code = b.cto_code ) ' ||
951: ' when matched then ' ||
952: ' update set ' ||
953: ' a.cto_start_date = b.from_date';
954: msd_dem_common_utilities.log_debug ('Updating MSD_DEM_CTO_BOM to retain effective from date for members in demantra');
955: msd_dem_common_utilities.log_debug (x_sql);
956: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
957: x_num_rows := 0;
958: execute immediate x_sql;

Line 964: /* Analyze the table MSD_DEM_CTO_BOM */

960: msd_dem_common_utilities.log_debug ('Number of rows merged - ' || to_char(x_num_rows));
961: COMMIT;
962: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
963:
964: /* Analyze the table MSD_DEM_CTO_BOM */
965: msd_dem_common_utilities.log_debug ('Analyzing table MSD_DEM_CTO_BOM');
966: msd_dem_collect_history_data.analyze_table (errbuf, retcode, 'MSD_DEM_CTO_BOM');
967:
968: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 965: msd_dem_common_utilities.log_debug ('Analyzing table MSD_DEM_CTO_BOM');

961: COMMIT;
962: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
963:
964: /* Analyze the table MSD_DEM_CTO_BOM */
965: msd_dem_common_utilities.log_debug ('Analyzing table MSD_DEM_CTO_BOM');
966: msd_dem_collect_history_data.analyze_table (errbuf, retcode, 'MSD_DEM_CTO_BOM');
967:
968: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
969:

Line 966: msd_dem_collect_history_data.analyze_table (errbuf, retcode, 'MSD_DEM_CTO_BOM');

962: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
963:
964: /* Analyze the table MSD_DEM_CTO_BOM */
965: msd_dem_common_utilities.log_debug ('Analyzing table MSD_DEM_CTO_BOM');
966: msd_dem_collect_history_data.analyze_table (errbuf, retcode, 'MSD_DEM_CTO_BOM');
967:
968: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
969:
970: retcode := 0;

Line 968: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

964: /* Analyze the table MSD_DEM_CTO_BOM */
965: msd_dem_common_utilities.log_debug ('Analyzing table MSD_DEM_CTO_BOM');
966: msd_dem_collect_history_data.analyze_table (errbuf, retcode, 'MSD_DEM_CTO_BOM');
967:
968: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
969:
970: retcode := 0;
971:
972: EXCEPTION

Line 977: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

973: WHEN OTHERS THEN
974: errbuf := substr(SQLERRM,1,150);
975: retcode := -1;
976:
977: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_cto.collect_model_bom_components - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
978: msd_dem_common_utilities.log_message (errbuf);
979: RETURN;
980:
981: END COLLECT_MODEL_BOM_COMPONENTS;

Line 1056: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.purge_cto_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

1052: ELSE
1053: msd_dem_common_utilities.log_message ('Complete Refresh is not set to 1. Hence exiting normally without deleting.');
1054: END IF;
1055:
1056: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_cto.purge_cto_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1057:
1058: retcode := 0;
1059:
1060: EXCEPTION

Line 1065: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_cto.purge_cto_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

1061: WHEN OTHERS THEN
1062: errbuf := substr(SQLERRM,1,150);
1063: retcode := -1;
1064:
1065: msd_dem_common_utilities.log_message ('Exception(1): msd_dem_cto.purge_cto_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1066: msd_dem_common_utilities.log_message (errbuf);
1067: RETURN;
1068:
1069: END PURGE_CTO_GL_DATA;

Line 1073: END MSD_DEM_CTO;

1069: END PURGE_CTO_GL_DATA;
1070:
1071:
1072:
1073: END MSD_DEM_CTO;