[Home] [Help]
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));
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));
36: msd_dem_common_utilities.log_debug (' For CTO - ' || to_char(p_for_cto));
37:
38: msd_dem_common_utilities.log_debug('Verify the Entity Name is available in MSD_DEM_ENTITY_QUERIES');
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));
36: msd_dem_common_utilities.log_debug (' For CTO - ' || to_char(p_for_cto));
37:
38: msd_dem_common_utilities.log_debug('Verify the Entity Name is available in MSD_DEM_ENTITY_QUERIES');
39: SELECT 1
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));
36: msd_dem_common_utilities.log_debug (' For CTO - ' || to_char(p_for_cto));
37:
38: msd_dem_common_utilities.log_debug('Verify the Entity Name is available in MSD_DEM_ENTITY_QUERIES');
39: SELECT 1
40: INTO x_is_present
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));
36: msd_dem_common_utilities.log_debug (' For CTO - ' || to_char(p_for_cto));
37:
38: msd_dem_common_utilities.log_debug('Verify the Entity Name is available in MSD_DEM_ENTITY_QUERIES');
39: SELECT 1
40: INTO x_is_present
41: FROM MSD_DEM_ENTITY_QUERIES
42: WHERE entity_name = p_entity_name;
41: FROM MSD_DEM_ENTITY_QUERIES
42: WHERE entity_name = p_entity_name;
43:
44:
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 - '
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;
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;
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;
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
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 - '
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 - '
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 - '
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 - '
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 - '
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 - '
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 - '
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 - '
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 - '
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:
125:
126: ELSIF (p_entity_name = 'EQ_SALES_TMPL_ITEM_OPTIONS')
127: THEN
128:
129: msd_dem_common_utilities.log_message ('Skipping EQ_SALES_TMPL_ITEM_OPTIONS as a separate concurrent request set.');
130: msd_dem_common_utilities.log_message ('EQ_SALES_TMPL_ITEM_OPTIONS will be a pre-process to EQ_BIIO_CTO_DATA_EPP');
131:
132: retcode := 0;
133: RETURN;
126: ELSIF (p_entity_name = 'EQ_SALES_TMPL_ITEM_OPTIONS')
127: THEN
128:
129: msd_dem_common_utilities.log_message ('Skipping EQ_SALES_TMPL_ITEM_OPTIONS as a separate concurrent request set.');
130: msd_dem_common_utilities.log_message ('EQ_SALES_TMPL_ITEM_OPTIONS will be a pre-process to EQ_BIIO_CTO_DATA_EPP');
131:
132: retcode := 0;
133: RETURN;
134:
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:
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:
143: msd_dem_query_utilities.execute_query (
144: errbuf,
145: retcode,
146: 'EQ_SALES_TMPL_ITEM_OPTIONS',
147: p_sr_instance_id,
148: null);
149:
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 - '
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
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
159: msd_dem_common_utilities.log_message ('Query EQ_SALES_TMPL_ITEM_OPTIONS executed successfully.');
160: END IF;
155: || 'Error in call to msd_dem_query_utilities.execute_query');
156: msd_dem_common_utilities.log_message(errbuf);
157: RETURN;
158: ELSE
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 - '
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 - '
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');
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');
172: msd_dem_query_utilities.execute_query (
173: errbuf,
174: retcode,
175: p_entity_name,
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
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
185: msd_dem_common_utilities.log_message ('Query ' || p_entity_name || ' executed successfully.');
186: END IF;
181: || 'Error in call to msd_dem_query_utilities.execute_query');
182: msd_dem_common_utilities.log_message(errbuf);
183: RETURN;
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'));
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
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 ';
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 - '
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,
208: 'EQ_SALES_TMPL_ITEM',
209: p_sr_instance_id,
210: null);
211:
212: IF (msd_dem_common_utilities.is_use_new_site_format = 0)
213: THEN
214:
215: msd_dem_common_utilities.log_message ('Update the site codes to descriptive format');
216:
211:
212: IF (msd_dem_common_utilities.is_use_new_site_format = 0)
213: THEN
214:
215: msd_dem_common_utilities.log_message ('Update the site codes to descriptive format');
216:
217: msd_dem_common_utilities.log_debug ('Start Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
218: msd_dem_update_level_codes.update_code(errbuf ,
219: retcode,
213: THEN
214:
215: msd_dem_common_utilities.log_message ('Update the site codes to descriptive format');
216:
217: msd_dem_common_utilities.log_debug ('Start Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
218: msd_dem_update_level_codes.update_code(errbuf ,
219: retcode,
220: p_sr_instance_id,
221: 'SITE',
218: msd_dem_update_level_codes.update_code(errbuf ,
219: retcode,
220: p_sr_instance_id,
221: 'SITE',
222: msd_dem_common_utilities.get_lookup_value ('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE'),
223: 'DM_SITE_CODE',
224: 'EBS_SITE_SR_PK');
225: msd_dem_common_utilities.log_debug ('End Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
226:
221: 'SITE',
222: msd_dem_common_utilities.get_lookup_value ('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE'),
223: 'DM_SITE_CODE',
224: 'EBS_SITE_SR_PK');
225: msd_dem_common_utilities.log_debug ('End Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
226:
227: END IF;
228:
229: ELSIF (p_entity_name = 'EQ_BIIO_CTO_DATA_EPP')
228:
229: ELSIF (p_entity_name = 'EQ_BIIO_CTO_DATA_EPP')
230: THEN
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'));
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(
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(
240: errbuf,
241: retcode,
242: p_sr_instance_id,
243: 'SITE',
244: x_dem_schema || '.BIIO_CTO_DATA',
245: 'LEVEL5',
246: 1);
247: msd_dem_common_utilities.log_debug ('End Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
248:
249: END IF;
250:
251: ELSE
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 - '
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:
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;
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;
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'));
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');
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');
288: msd_dem_query_utilities.execute_query (
289: errbuf,
290: retcode,
291: p_entity_name,
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
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
301: msd_dem_common_utilities.log_message ('Query ' || p_entity_name || ' executed successfully.');
302: END IF;
297: || 'Error in call to msd_dem_query_utilities.execute_query');
298: msd_dem_common_utilities.log_message(errbuf);
299: RETURN;
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'));
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)
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)
309: THEN
310:
311: msd_dem_common_utilities.log_message ('Update the site codes to descriptive format');
312:
307: IF ( p_entity_name = 'EQ_SALES_TMPL_ITEM'
308: AND msd_dem_common_utilities.is_use_new_site_format = 0)
309: THEN
310:
311: msd_dem_common_utilities.log_message ('Update the site codes to descriptive format');
312:
313: msd_dem_common_utilities.log_debug ('Start Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
314: msd_dem_update_level_codes.update_code(errbuf ,
315: retcode,
309: THEN
310:
311: msd_dem_common_utilities.log_message ('Update the site codes to descriptive format');
312:
313: msd_dem_common_utilities.log_debug ('Start Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
314: msd_dem_update_level_codes.update_code(errbuf ,
315: retcode,
316: p_sr_instance_id,
317: 'SITE',
314: msd_dem_update_level_codes.update_code(errbuf ,
315: retcode,
316: p_sr_instance_id,
317: 'SITE',
318: msd_dem_common_utilities.get_lookup_value ('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE'),
319: 'DM_SITE_CODE',
320: 'EBS_SITE_SR_PK');
321: msd_dem_common_utilities.log_debug ('End Updating Site codes - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
322:
317: 'SITE',
318: msd_dem_common_utilities.get_lookup_value ('MSD_DEM_DM_STAGING_TABLES', 'SALES_STAGING_TABLE'),
319: 'DM_SITE_CODE',
320: 'EBS_SITE_SR_PK');
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 - '
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:
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
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;
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;
344:
365: x_sql VARCHAR2(32000) := NULL;
366: x_sql1 VARCHAR2(4000) := NULL;
367: x_sql2 VARCHAR2(4000) := NULL;
368: x_sql3 VARCHAR2(1000) := NULL;
369: x_least_eff_date date := msd_dem_common_utilities.get_cto_effective_date(null, 1);
370:
371: x_iterator NUMBER := 1;
372: x_num_rows NUMBER := 0;
373: x_total_num_rows NUMBER := 0;
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:
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:
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
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;
391: retcode := 0;
392: RETURN;
393: END IF;
394:
395: msd_dem_common_utilities.log_message ('Use Organization Specific BOM - ' || to_char(x_is_local));
396:
397: SELECT USER INTO x_curr_user FROM DUAL;
398: msd_dem_common_utilities.log_message ('Current User - ' || x_curr_user);
399:
394:
395: msd_dem_common_utilities.log_message ('Use Organization Specific BOM - ' || to_char(x_is_local));
396:
397: SELECT USER INTO x_curr_user FROM DUAL;
398: msd_dem_common_utilities.log_message ('Current User - ' || x_curr_user);
399:
400: msd_dem_common_utilities.log_debug ('Truncate table MSD_DEM_MODEL_BOM_COMPONENTS');
401: msd_dem_query_utilities.truncate_table (
402: errbuf,
396:
397: SELECT USER INTO x_curr_user FROM DUAL;
398: msd_dem_common_utilities.log_message ('Current User - ' || x_curr_user);
399:
400: msd_dem_common_utilities.log_debug ('Truncate table MSD_DEM_MODEL_BOM_COMPONENTS');
401: msd_dem_query_utilities.truncate_table (
402: errbuf,
403: retcode,
404: 'MSD_DEM_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;
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;
414:
415: msd_dem_common_utilities.log_debug ('Inserting base models in T_SRC_SALES_TMPL to MSD_DEM_MODEL_BOM_COMPONENTS');
411: msd_dem_common_utilities.log_message(errbuf);
412: RETURN;
413: END IF;
414:
415: msd_dem_common_utilities.log_debug ('Inserting base models in T_SRC_SALES_TMPL to MSD_DEM_MODEL_BOM_COMPONENTS');
416: x_sql := 'INSERT /*+ APPEND NOLOGGING */ INTO MSD_DEM_MODEL_BOM_COMPONENTS '
417: || ' ( ID, SR_INSTANCE_ID, SR_ORGANIZATION_ID, BASE_MODEL_ID, PARENT_ITEM_ID, COMPONENT_ITEM_ID, IS_BASE_MODEL, OPTIONAL_FLAG, '
418: || ' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, COMPONENT_CODE, PARENT_COMPONENT_CODE ';
419:
514: || ' AND msi.item_name = tecbm.t_ep_cto_base_model_code '
515: || ' AND tec.t_ep_cto_code = ''' || to_char(p_sr_instance_id) || ''' || ''::'' || msi.organization_id '
516: || ' || ''::'' || msi.sr_inventory_item_id ';
517:
518: msd_dem_common_utilities.log_debug ('The query is - ');
519: msd_dem_common_utilities.log_debug (x_sql);
520:
521: msd_dem_common_utilities.log_debug ('Iterator Value is - ' || to_char(x_iterator));
522: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
515: || ' AND tec.t_ep_cto_code = ''' || to_char(p_sr_instance_id) || ''' || ''::'' || msi.organization_id '
516: || ' || ''::'' || msi.sr_inventory_item_id ';
517:
518: msd_dem_common_utilities.log_debug ('The query is - ');
519: msd_dem_common_utilities.log_debug (x_sql);
520:
521: msd_dem_common_utilities.log_debug ('Iterator Value is - ' || to_char(x_iterator));
522: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
523:
517:
518: msd_dem_common_utilities.log_debug ('The query is - ');
519: msd_dem_common_utilities.log_debug (x_sql);
520:
521: msd_dem_common_utilities.log_debug ('Iterator Value is - ' || to_char(x_iterator));
522: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
523:
524: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
525: EXECUTE IMMEDIATE x_sql;
518: msd_dem_common_utilities.log_debug ('The query is - ');
519: msd_dem_common_utilities.log_debug (x_sql);
520:
521: msd_dem_common_utilities.log_debug ('Iterator Value is - ' || to_char(x_iterator));
522: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
523:
524: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
525: EXECUTE IMMEDIATE x_sql;
526: x_total_num_rows := x_total_num_rows + SQL%ROWCOUNT;
520:
521: msd_dem_common_utilities.log_debug ('Iterator Value is - ' || to_char(x_iterator));
522: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
523:
524: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
525: EXECUTE IMMEDIATE x_sql;
526: x_total_num_rows := x_total_num_rows + SQL%ROWCOUNT;
527: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_total_num_rows));
528: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
523:
524: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
525: EXECUTE IMMEDIATE x_sql;
526: x_total_num_rows := x_total_num_rows + SQL%ROWCOUNT;
527: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_total_num_rows));
528: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
529: COMMIT;
530: msd_dem_common_utilities.log_debug ('Query End Time Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
531:
524: msd_dem_common_utilities.log_debug ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
525: EXECUTE IMMEDIATE x_sql;
526: x_total_num_rows := x_total_num_rows + SQL%ROWCOUNT;
527: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_total_num_rows));
528: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
529: COMMIT;
530: msd_dem_common_utilities.log_debug ('Query End Time Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
531:
532:
526: x_total_num_rows := x_total_num_rows + SQL%ROWCOUNT;
527: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_total_num_rows));
528: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
529: COMMIT;
530: msd_dem_common_utilities.log_debug ('Query End Time Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
531:
532:
533: msd_dem_common_utilities.log_debug ('Build Query to populate the parent child recursively');
534: x_sql := NULL;
529: COMMIT;
530: msd_dem_common_utilities.log_debug ('Query End Time Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
531:
532:
533: msd_dem_common_utilities.log_debug ('Build Query to populate the parent child recursively');
534: x_sql := NULL;
535: x_sql := 'INSERT /*+ APPEND NOLOGGING */ INTO MSD_DEM_MODEL_BOM_COMPONENTS '
536: || ' ( ID, SR_INSTANCE_ID, SR_ORGANIZATION_ID, BASE_MODEL_ID, TOP_ATO_MODEL_ID, PARENT_ITEM_ID, COMPONENT_ITEM_ID, IS_BASE_MODEL, '
537: || ' EFFECTIVITY_DATE, DISABLE_DATE, PLANNING_FACTOR, PLNG_PCT_EXISTING, OPTIONAL_FLAG, '
602: || ' abs(mbc.usage_quantity)), '
603: || ' 1, '
604: || ' (mbc.usage_quantity * mbc.Component_Yield_Factor), '
605: || ' (mbc.usage_quantity / mbc.Component_Yield_Factor)) '
606: || ' * msd_dem_common_utilities.uom_conv(citem.sr_instance_id,citem.uom_code,citem.inventory_item_id) plng_pct_existing, '
607: || ' mbc.optional_component optional_flag, '
608: || ' dem.component_code || ''-'' || citem.sr_inventory_item_id component_code ';
609:
610: IF (x_cpp = 2)
626: || ' abs(mbc.usage_quantity)), '
627: || ' 1, '
628: || ' (mbc.usage_quantity * mbc.Component_Yield_Factor), '
629: || ' (mbc.usage_quantity / mbc.Component_Yield_Factor)) '
630: || ' * msd_dem_common_utilities.uom_conv(citem.sr_instance_id,citem.uom_code,citem.inventory_item_id) plng_pct_existing_s '
631: || ' , decode (pitem.bom_item_type, 2,dem.planning_factor_s /100, 1) * mbc.planning_factor planning_factor_s '
632: || ' , decode (mbc.optional_component * dem.optional_flag_s, 4, 2, 1) optional_flag_s '
633: || ' , dem.component_code_s || decode (citem.bom_item_type, 2, '''', ''-'' || citem.sr_inventory_item_id) component_code_s '
634: || ' , decode (pitem.bom_item_type, 2, dem.parent_item_id_s, pitem.inventory_item_id) parent_item_id_s ';
657: IF (x_is_local = 1)
658: THEN
659: x_sql := x_sql || ' AND mb.organization_id = dem.sr_organization_id ';
660: ELSE
661: msd_dem_common_utilities.log_debug ('Get the validation org id for the instance');
662: SELECT validation_org_id INTO x_validation_org_id
663: FROM msc_apps_instances
664: WHERE instance_id = p_sr_instance_id;
665:
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:
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:
674: x_sql := x_sql || ' AND mb.organization_id = ' || to_char(x_validation_org_id);
720: if(p_flat_file_load = 1) then
721: x_sql := x_sql || ', COMPONENT_CODE_LEGACY ';
722: end if;
723:
724: msd_dem_common_utilities.log_debug('The Query is - ');
725: msd_dem_common_utilities.log_debug(x_sql);
726:
727: msd_dem_common_utilities.log_debug ('Build Query to mark duplication Option Class as Option');
728: x_sql1 := 'UPDATE MSD_DEM_MODEL_BOM_COMPONENTS a'
721: x_sql := x_sql || ', COMPONENT_CODE_LEGACY ';
722: end if;
723:
724: msd_dem_common_utilities.log_debug('The Query is - ');
725: msd_dem_common_utilities.log_debug(x_sql);
726:
727: msd_dem_common_utilities.log_debug ('Build Query to mark duplication Option Class as Option');
728: x_sql1 := 'UPDATE MSD_DEM_MODEL_BOM_COMPONENTS a'
729: || ' SET is_base_model = 3 '
723:
724: msd_dem_common_utilities.log_debug('The Query is - ');
725: msd_dem_common_utilities.log_debug(x_sql);
726:
727: msd_dem_common_utilities.log_debug ('Build Query to mark duplication Option Class as Option');
728: x_sql1 := 'UPDATE MSD_DEM_MODEL_BOM_COMPONENTS a'
729: || ' SET is_base_model = 3 '
730: || ' WHERE EXISTS ( SELECT 1 FROM MSD_DEM_MODEL_BOM_COMPONENTS b '
731: || ' WHERE b.id < :bvar1 '
734: || ' AND b.base_model_id = a.base_model_id '
735: || ' AND b.component_item_id = a.component_item_id ) '
736: || ' AND a.id = :bvar2 ';
737:
738: msd_dem_common_utilities.log_debug('The Query is - ');
739: msd_dem_common_utilities.log_debug(x_sql1);
740:
741: msd_dem_common_utilities.log_debug ('Build Query to delete duplicate Option Class-Option');
742: x_sql2 := 'DELETE FROM MSD_DEM_MODEL_BOM_COMPONENTS a'
735: || ' AND b.component_item_id = a.component_item_id ) '
736: || ' AND a.id = :bvar2 ';
737:
738: msd_dem_common_utilities.log_debug('The Query is - ');
739: msd_dem_common_utilities.log_debug(x_sql1);
740:
741: msd_dem_common_utilities.log_debug ('Build Query to delete duplicate Option Class-Option');
742: x_sql2 := 'DELETE FROM MSD_DEM_MODEL_BOM_COMPONENTS a'
743: || ' WHERE EXISTS ( SELECT 1 FROM MSD_DEM_MODEL_BOM_COMPONENTS b '
737:
738: msd_dem_common_utilities.log_debug('The Query is - ');
739: msd_dem_common_utilities.log_debug(x_sql1);
740:
741: msd_dem_common_utilities.log_debug ('Build Query to delete duplicate Option Class-Option');
742: x_sql2 := 'DELETE FROM MSD_DEM_MODEL_BOM_COMPONENTS a'
743: || ' WHERE EXISTS ( SELECT 1 FROM MSD_DEM_MODEL_BOM_COMPONENTS b '
744: || ' WHERE b.id < :bvar1 '
745: || ' AND b.sr_instance_id = a.sr_instance_id '
748: || ' AND b.parent_item_id = a.parent_item_id '
749: || ' AND b.component_item_id = a.component_item_id ) '
750: || ' AND a.id = :bvar2 ';
751:
752: msd_dem_common_utilities.log_debug('The Query is - ');
753: msd_dem_common_utilities.log_debug(x_sql2);
754:
755: msd_dem_common_utilities.log_debug ('Entering Loop');
756: LOOP
749: || ' AND b.component_item_id = a.component_item_id ) '
750: || ' AND a.id = :bvar2 ';
751:
752: msd_dem_common_utilities.log_debug('The Query is - ');
753: msd_dem_common_utilities.log_debug(x_sql2);
754:
755: msd_dem_common_utilities.log_debug ('Entering Loop');
756: LOOP
757:
751:
752: msd_dem_common_utilities.log_debug('The Query is - ');
753: msd_dem_common_utilities.log_debug(x_sql2);
754:
755: msd_dem_common_utilities.log_debug ('Entering Loop');
756: LOOP
757:
758: x_iterator := x_iterator + 1;
759:
756: LOOP
757:
758: x_iterator := x_iterator + 1;
759:
760: msd_dem_common_utilities.log_debug ('Iterator Value is - ' || to_char(x_iterator));
761:
762: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
763: EXECUTE IMMEDIATE x_sql USING x_iterator, x_iterator - 1, x_least_eff_date;
764: x_num_rows := SQL%ROWCOUNT;
758: x_iterator := x_iterator + 1;
759:
760: msd_dem_common_utilities.log_debug ('Iterator Value is - ' || to_char(x_iterator));
761:
762: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
763: EXECUTE IMMEDIATE x_sql USING x_iterator, x_iterator - 1, x_least_eff_date;
764: x_num_rows := SQL%ROWCOUNT;
765: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
766: x_total_num_rows := x_total_num_rows + x_num_rows;
761:
762: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
763: EXECUTE IMMEDIATE x_sql USING x_iterator, x_iterator - 1, x_least_eff_date;
764: x_num_rows := SQL%ROWCOUNT;
765: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
766: x_total_num_rows := x_total_num_rows + x_num_rows;
767: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
768: COMMIT;
769: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
763: EXECUTE IMMEDIATE x_sql USING x_iterator, x_iterator - 1, x_least_eff_date;
764: x_num_rows := SQL%ROWCOUNT;
765: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
766: x_total_num_rows := x_total_num_rows + x_num_rows;
767: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
768: COMMIT;
769: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
770:
771: IF (x_num_rows = 0)
765: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
766: x_total_num_rows := x_total_num_rows + x_num_rows;
767: msd_dem_common_utilities.log_debug ('Total Number of rows is - ' || to_char(x_total_num_rows));
768: COMMIT;
769: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
770:
771: IF (x_num_rows = 0)
772: THEN
773: COMMIT;
770:
771: IF (x_num_rows = 0)
772: THEN
773: COMMIT;
774: msd_dem_common_utilities.log_debug ('Exiting Loop - as reached the bottom of the tree');
775: EXIT;
776: END IF;
777:
778: /*
775: EXIT;
776: END IF;
777:
778: /*
779: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
780: EXECUTE IMMEDIATE x_sql2 USING x_iterator, x_iterator;
781: x_num_rows := SQL%ROWCOUNT;
782: msd_dem_common_utilities.log_debug ('Number of rows deleted - ' || to_char(x_num_rows));
783: COMMIT;
778: /*
779: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
780: EXECUTE IMMEDIATE x_sql2 USING x_iterator, x_iterator;
781: x_num_rows := SQL%ROWCOUNT;
782: msd_dem_common_utilities.log_debug ('Number of rows deleted - ' || to_char(x_num_rows));
783: COMMIT;
784: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
785:
786: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
780: EXECUTE IMMEDIATE x_sql2 USING x_iterator, x_iterator;
781: x_num_rows := SQL%ROWCOUNT;
782: msd_dem_common_utilities.log_debug ('Number of rows deleted - ' || to_char(x_num_rows));
783: COMMIT;
784: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
785:
786: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
787: EXECUTE IMMEDIATE x_sql1 USING x_iterator, x_iterator;
788: x_num_rows := SQL%ROWCOUNT;
782: msd_dem_common_utilities.log_debug ('Number of rows deleted - ' || to_char(x_num_rows));
783: COMMIT;
784: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
785:
786: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
787: EXECUTE IMMEDIATE x_sql1 USING x_iterator, x_iterator;
788: x_num_rows := SQL%ROWCOUNT;
789: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
790: COMMIT;
785:
786: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
787: EXECUTE IMMEDIATE x_sql1 USING x_iterator, x_iterator;
788: x_num_rows := SQL%ROWCOUNT;
789: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
790: COMMIT;
791: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
792: */
793:
787: EXECUTE IMMEDIATE x_sql1 USING x_iterator, x_iterator;
788: x_num_rows := SQL%ROWCOUNT;
789: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
790: COMMIT;
791: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
792: */
793:
794: msd_dem_common_utilities.log_debug(' ');
795:
790: COMMIT;
791: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
792: */
793:
794: msd_dem_common_utilities.log_debug(' ');
795:
796: IF (x_iterator = 500)
797: THEN
798: COMMIT;
795:
796: IF (x_iterator = 500)
797: THEN
798: COMMIT;
799: msd_dem_common_utilities.log_debug ('Exiting Loop - as reached max iterator');
800: EXIT;
801: END IF;
802:
803: END LOOP;
840: x_num_rows := 0;
841: IF (x_sql1 IS NOT NULL)
842: THEN
843:
844: msd_dem_common_utilities.log_debug('Updating component_code in T_SRC_SALES_TMPL');
845: msd_dem_common_utilities.log_debug('The Query is - ');
846: msd_dem_common_utilities.log_debug(x_sql1);
847: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
848: EXECUTE IMMEDIATE x_sql1;
841: IF (x_sql1 IS NOT NULL)
842: THEN
843:
844: msd_dem_common_utilities.log_debug('Updating component_code in T_SRC_SALES_TMPL');
845: msd_dem_common_utilities.log_debug('The Query is - ');
846: msd_dem_common_utilities.log_debug(x_sql1);
847: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
848: EXECUTE IMMEDIATE x_sql1;
849: x_num_rows := SQL%ROWCOUNT;
842: THEN
843:
844: msd_dem_common_utilities.log_debug('Updating component_code in T_SRC_SALES_TMPL');
845: msd_dem_common_utilities.log_debug('The Query is - ');
846: msd_dem_common_utilities.log_debug(x_sql1);
847: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
848: EXECUTE IMMEDIATE x_sql1;
849: x_num_rows := SQL%ROWCOUNT;
850: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
843:
844: msd_dem_common_utilities.log_debug('Updating component_code in T_SRC_SALES_TMPL');
845: msd_dem_common_utilities.log_debug('The Query is - ');
846: msd_dem_common_utilities.log_debug(x_sql1);
847: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
848: EXECUTE IMMEDIATE x_sql1;
849: x_num_rows := SQL%ROWCOUNT;
850: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
851: COMMIT;
846: msd_dem_common_utilities.log_debug(x_sql1);
847: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
848: EXECUTE IMMEDIATE x_sql1;
849: x_num_rows := SQL%ROWCOUNT;
850: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
851: COMMIT;
852: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
853:
854: END IF;
848: EXECUTE IMMEDIATE x_sql1;
849: x_num_rows := SQL%ROWCOUNT;
850: msd_dem_common_utilities.log_debug ('Number of rows updated - ' || to_char(x_num_rows));
851: COMMIT;
852: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
853:
854: END IF;
855:
856: IF (p_flat_file_load = 1 ) THEN
858: /* Move all INVALID dependent demand records into t_src_sales_tmpl_err */ -- Bug#10374484
859: x_sql3 := 'Insert into ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR (select tsst.*, 6, sysdate, ''INVALID dependent demand record. NOT a VALID item under the BOM.'' from '
860: || x_dem_schema || '.T_SRC_SALES_TMPL tsst where tsst.component_code is null and tsst.component_code_legacy is not null) ' ;
861:
862: msd_dem_common_utilities.log_debug('Inserting INVALID dependent demand records into T_SRC_SALES_TMPL_ERR');
863: msd_dem_common_utilities.log_debug('The Query is - ');
864: msd_dem_common_utilities.log_debug(x_sql3);
865: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
866: EXECUTE IMMEDIATE x_sql3;
859: x_sql3 := 'Insert into ' || x_dem_schema || '.T_SRC_SALES_TMPL_ERR (select tsst.*, 6, sysdate, ''INVALID dependent demand record. NOT a VALID item under the BOM.'' from '
860: || x_dem_schema || '.T_SRC_SALES_TMPL tsst where tsst.component_code is null and tsst.component_code_legacy is not null) ' ;
861:
862: msd_dem_common_utilities.log_debug('Inserting INVALID dependent demand records into T_SRC_SALES_TMPL_ERR');
863: msd_dem_common_utilities.log_debug('The Query is - ');
864: msd_dem_common_utilities.log_debug(x_sql3);
865: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
866: EXECUTE IMMEDIATE x_sql3;
867: x_num_rows := SQL%ROWCOUNT;
860: || x_dem_schema || '.T_SRC_SALES_TMPL tsst where tsst.component_code is null and tsst.component_code_legacy is not null) ' ;
861:
862: msd_dem_common_utilities.log_debug('Inserting INVALID dependent demand records into T_SRC_SALES_TMPL_ERR');
863: msd_dem_common_utilities.log_debug('The Query is - ');
864: msd_dem_common_utilities.log_debug(x_sql3);
865: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
866: EXECUTE IMMEDIATE x_sql3;
867: x_num_rows := SQL%ROWCOUNT;
868: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
861:
862: msd_dem_common_utilities.log_debug('Inserting INVALID dependent demand records into T_SRC_SALES_TMPL_ERR');
863: msd_dem_common_utilities.log_debug('The Query is - ');
864: msd_dem_common_utilities.log_debug(x_sql3);
865: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
866: EXECUTE IMMEDIATE x_sql3;
867: x_num_rows := SQL%ROWCOUNT;
868: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
869: COMMIT;
864: msd_dem_common_utilities.log_debug(x_sql3);
865: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
866: EXECUTE IMMEDIATE x_sql3;
867: x_num_rows := SQL%ROWCOUNT;
868: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
869: COMMIT;
870: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
871:
872:
866: EXECUTE IMMEDIATE x_sql3;
867: x_num_rows := SQL%ROWCOUNT;
868: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
869: COMMIT;
870: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
871:
872:
873: /* Delete all INVALID dependent demand records from t_src_sales_tmpl */ -- Bug#10374484
874: x_sql3 := 'Delete from ' || x_dem_schema || '.T_SRC_SALES_TMPL where component_code is null and component_code_legacy is not null' ;
872:
873: /* Delete all INVALID dependent demand records from t_src_sales_tmpl */ -- Bug#10374484
874: x_sql3 := 'Delete from ' || x_dem_schema || '.T_SRC_SALES_TMPL where component_code is null and component_code_legacy is not null' ;
875:
876: msd_dem_common_utilities.log_debug('Deleting INVALID dependent demand records from T_SRC_SALES_TMPL');
877: msd_dem_common_utilities.log_debug('The Query is - ');
878: msd_dem_common_utilities.log_debug(x_sql3);
879: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
880: EXECUTE IMMEDIATE x_sql3;
873: /* Delete all INVALID dependent demand records from t_src_sales_tmpl */ -- Bug#10374484
874: x_sql3 := 'Delete from ' || x_dem_schema || '.T_SRC_SALES_TMPL where component_code is null and component_code_legacy is not null' ;
875:
876: msd_dem_common_utilities.log_debug('Deleting INVALID dependent demand records from T_SRC_SALES_TMPL');
877: msd_dem_common_utilities.log_debug('The Query is - ');
878: msd_dem_common_utilities.log_debug(x_sql3);
879: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
880: EXECUTE IMMEDIATE x_sql3;
881: x_num_rows := SQL%ROWCOUNT;
874: x_sql3 := 'Delete from ' || x_dem_schema || '.T_SRC_SALES_TMPL where component_code is null and component_code_legacy is not null' ;
875:
876: msd_dem_common_utilities.log_debug('Deleting INVALID dependent demand records from T_SRC_SALES_TMPL');
877: msd_dem_common_utilities.log_debug('The Query is - ');
878: msd_dem_common_utilities.log_debug(x_sql3);
879: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
880: EXECUTE IMMEDIATE x_sql3;
881: x_num_rows := SQL%ROWCOUNT;
882: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
875:
876: msd_dem_common_utilities.log_debug('Deleting INVALID dependent demand records from T_SRC_SALES_TMPL');
877: msd_dem_common_utilities.log_debug('The Query is - ');
878: msd_dem_common_utilities.log_debug(x_sql3);
879: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
880: EXECUTE IMMEDIATE x_sql3;
881: x_num_rows := SQL%ROWCOUNT;
882: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
883: COMMIT;
878: msd_dem_common_utilities.log_debug(x_sql3);
879: msd_dem_common_utilities.log_debug('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
880: EXECUTE IMMEDIATE x_sql3;
881: x_num_rows := SQL%ROWCOUNT;
882: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
883: COMMIT;
884: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
885:
886: END IF;
880: EXECUTE IMMEDIATE x_sql3;
881: x_num_rows := SQL%ROWCOUNT;
882: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
883: COMMIT;
884: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
885:
886: END IF;
887:
888: /* CTO Performance Fix - Store the Model Bom Components Information in a table */
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:
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;
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:
902: END IF;
903:
904: -- Bug#12315455
905: -- In a weekly system, effective to date should be different for collection runs on different days of the bucket
906: x_dm_time_level := lower(msd_dem_common_utilities.dm_time_level);
907: IF x_dm_time_level = 'week' THEN
908: x_add_days := to_number(to_char(sysdate, 'D')) - 1;
909: END IF;
910:
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
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
918: INSERT /*+ APPEND NOLOGGING */ INTO MSD_DEM_CTO_BOM
927: EFFECTIVITY_DATE, DISABLE_DATE, PLNG_PCT_EXISTING, CTO_TYPE,
928: BASE_MODEL_ID, TOP_ATO_MODEL_ID, PARENT_ITEM_ID, OPTION_ID,
929: BASE_MODEL_SR_ID, PARENT_ITEM_SR_ID, OPTION_SR_ID,
930: COMPONENT_QTY, OPTIONAL_FLAG, ATO_FORECAST_CONTROL,
931: msd_dem_common_utilities.get_cto_effective_date(effectivity_date, 1) CTO_START_DATE,
932: msd_dem_common_utilities.get_cto_effective_date(disable_date, 2) + x_add_days CTO_END_DATE
933: FROM msd_dem_model_bom_components_v;
934:
935: x_num_rows := SQL%ROWCOUNT;
928: BASE_MODEL_ID, TOP_ATO_MODEL_ID, PARENT_ITEM_ID, OPTION_ID,
929: BASE_MODEL_SR_ID, PARENT_ITEM_SR_ID, OPTION_SR_ID,
930: COMPONENT_QTY, OPTIONAL_FLAG, ATO_FORECAST_CONTROL,
931: msd_dem_common_utilities.get_cto_effective_date(effectivity_date, 1) CTO_START_DATE,
932: msd_dem_common_utilities.get_cto_effective_date(disable_date, 2) + x_add_days CTO_END_DATE
933: FROM msd_dem_model_bom_components_v;
934:
935: x_num_rows := SQL%ROWCOUNT;
936: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
932: msd_dem_common_utilities.get_cto_effective_date(disable_date, 2) + x_add_days CTO_END_DATE
933: FROM msd_dem_model_bom_components_v;
934:
935: x_num_rows := SQL%ROWCOUNT;
936: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
937: COMMIT;
938: msd_dem_common_utilities.log_debug('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
939:
940: -- Bug#12315455
934:
935: x_num_rows := SQL%ROWCOUNT;
936: msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
937: COMMIT;
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 ' ||
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;
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;
959: x_num_rows := SQL%ROWCOUNT;
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;
959: x_num_rows := SQL%ROWCOUNT;
960: msd_dem_common_utilities.log_debug ('Number of rows merged - ' || to_char(x_num_rows));
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;
959: x_num_rows := SQL%ROWCOUNT;
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 */
958: execute immediate x_sql;
959: x_num_rows := SQL%ROWCOUNT;
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');
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:
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
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;
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;
982:
1004: x_sql VARCHAR2(500) := NULL;
1005:
1006: BEGIN
1007:
1008: msd_dem_common_utilities.log_debug ('Entering: msd_dem_query_utilities.purge_cto_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1009:
1010: /* Log the parameters */
1011: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO');
1012: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_BASE_MODEL');
1007:
1008: msd_dem_common_utilities.log_debug ('Entering: msd_dem_query_utilities.purge_cto_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1009:
1010: /* Log the parameters */
1011: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO');
1012: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_BASE_MODEL');
1013: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_CHILD');
1014: msd_dem_common_utilities.log_message ('Complete Refresh - ' || to_number(p_complete_refresh));
1015:
1008: msd_dem_common_utilities.log_debug ('Entering: msd_dem_query_utilities.purge_cto_gl_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1009:
1010: /* Log the parameters */
1011: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO');
1012: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_BASE_MODEL');
1013: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_CHILD');
1014: msd_dem_common_utilities.log_message ('Complete Refresh - ' || to_number(p_complete_refresh));
1015:
1016: IF (p_complete_refresh = 1)
1009:
1010: /* Log the parameters */
1011: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO');
1012: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_BASE_MODEL');
1013: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_CHILD');
1014: msd_dem_common_utilities.log_message ('Complete Refresh - ' || to_number(p_complete_refresh));
1015:
1016: IF (p_complete_refresh = 1)
1017: THEN
1010: /* Log the parameters */
1011: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO');
1012: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_BASE_MODEL');
1013: msd_dem_common_utilities.log_message ('Table to be deleted - T_EP_CTO_CHILD');
1014: msd_dem_common_utilities.log_message ('Complete Refresh - ' || to_number(p_complete_refresh));
1015:
1016: IF (p_complete_refresh = 1)
1017: THEN
1018:
1019: x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
1020:
1021: /*Deleting records from T_EP_CTO table */
1022: x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO' || ' WHERE t_ep_cto_id <> 0 ';
1023: msd_dem_common_utilities.log_message ('The SQL is - ' || x_sql);
1024:
1025: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1026: EXECUTE IMMEDIATE x_sql;
1027: COMMIT;
1021: /*Deleting records from T_EP_CTO table */
1022: x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO' || ' WHERE t_ep_cto_id <> 0 ';
1023: msd_dem_common_utilities.log_message ('The SQL is - ' || x_sql);
1024:
1025: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1026: EXECUTE IMMEDIATE x_sql;
1027: COMMIT;
1028: msd_dem_common_utilities.log_message ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1029:
1024:
1025: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1026: EXECUTE IMMEDIATE x_sql;
1027: COMMIT;
1028: msd_dem_common_utilities.log_message ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1029:
1030:
1031: /*Deleting records from T_EP_CTO_BASE_MODEL table */
1032: x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO_BASE_MODEL' || ' WHERE t_ep_cto_base_model_id <> 0 ';
1029:
1030:
1031: /*Deleting records from T_EP_CTO_BASE_MODEL table */
1032: x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO_BASE_MODEL' || ' WHERE t_ep_cto_base_model_id <> 0 ';
1033: msd_dem_common_utilities.log_message ('The SQL is - ' || x_sql);
1034:
1035: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1036: EXECUTE IMMEDIATE x_sql;
1037: COMMIT;
1031: /*Deleting records from T_EP_CTO_BASE_MODEL table */
1032: x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO_BASE_MODEL' || ' WHERE t_ep_cto_base_model_id <> 0 ';
1033: msd_dem_common_utilities.log_message ('The SQL is - ' || x_sql);
1034:
1035: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1036: EXECUTE IMMEDIATE x_sql;
1037: COMMIT;
1038: msd_dem_common_utilities.log_message ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1039:
1034:
1035: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1036: EXECUTE IMMEDIATE x_sql;
1037: COMMIT;
1038: msd_dem_common_utilities.log_message ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1039:
1040:
1041: /*Deleting records from T_EP_CTO_CHILD table */
1042: x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO_CHILD' || ' WHERE t_ep_cto_child_id <> 0 ';
1039:
1040:
1041: /*Deleting records from T_EP_CTO_CHILD table */
1042: x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO_CHILD' || ' WHERE t_ep_cto_child_id <> 0 ';
1043: msd_dem_common_utilities.log_message ('The SQL is - ' || x_sql);
1044:
1045: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1046: EXECUTE IMMEDIATE x_sql;
1047: COMMIT;
1041: /*Deleting records from T_EP_CTO_CHILD table */
1042: x_sql := 'DELETE FROM ' || x_schema || '.' || 'T_EP_CTO_CHILD' || ' WHERE t_ep_cto_child_id <> 0 ';
1043: msd_dem_common_utilities.log_message ('The SQL is - ' || x_sql);
1044:
1045: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1046: EXECUTE IMMEDIATE x_sql;
1047: COMMIT;
1048: msd_dem_common_utilities.log_message ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1049:
1044:
1045: msd_dem_common_utilities.log_message ('Query Start Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1046: EXECUTE IMMEDIATE x_sql;
1047: COMMIT;
1048: msd_dem_common_utilities.log_message ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1049:
1050: msd_dem_common_utilities.log_message ('CTO Data deleted successfully.');
1051:
1052: ELSE
1046: EXECUTE IMMEDIATE x_sql;
1047: COMMIT;
1048: msd_dem_common_utilities.log_message ('Query End Time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1049:
1050: msd_dem_common_utilities.log_message ('CTO Data deleted successfully.');
1051:
1052: ELSE
1053: msd_dem_common_utilities.log_message ('Complete Refresh is not set to 1. Hence exiting normally without deleting.');
1054: END IF;
1049:
1050: msd_dem_common_utilities.log_message ('CTO Data deleted successfully.');
1051:
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:
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
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;
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;
1070: