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

Source


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 ***
6     *
7     * POPULATE_STAGING_TABLE
8     * COLLECT_MODEL_BOM_COMPONENTS
9     * PURGE_CTO_GL_DATA
10     *
11     *** PUBLIC PROCEDURES  ***/
12 
13 
14    /*
15     * Given the entity name, this procedure runs the query for the entity name.
16     * Usually this procedure will be used to populate the Demantra CTO staging tables.
17     */
18    PROCEDURE POPULATE_STAGING_TABLE (
19    			errbuf			OUT NOCOPY 	VARCHAR2,
20       			retcode			OUT NOCOPY 	VARCHAR2,
21       			p_entity_name		IN		VARCHAR2,
22       			p_sr_instance_id	IN		NUMBER,
23       			p_for_cto		IN		NUMBER DEFAULT 1)
24    IS
25       x_include_dependent_demand	NUMBER	:= NULL;
26       x_is_present			NUMBER	:= NULL;
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));
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;
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 - '
50                                                   || 'Unable to get value for Profile MSD_DEM: Schema');
51          retcode := -1;
52          RETURN;
53       END IF;
54 
55 
56       IF (p_for_cto = 1)
57       THEN
58 
59          /* If the query is exclusively meant for CTO, then the query should be run only if
60             the profile MSD_DEM: Include Dependent Demand is set to Yes */
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;
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;
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
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 - '
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 - '
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 - '
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 - '
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 - '
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 
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 
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 
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 - '
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 - '
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');
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
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
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 ';
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 
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',
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')
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'));
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
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 
261          /* If the query is exclusively meant for NON-CTO, then the query should be run only if
262             the profile MSD_DEM: Include Dependent Demand is set to No */
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;
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;
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');
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
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)
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',
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 - '
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;
344 
345 
346 
347 
348    /*
349     * This procedure populates the table msd_dem_model_bom_components for the base models
350     * available in the sales staging table.
351     */
352    PROCEDURE COLLECT_MODEL_BOM_COMPONENTS (
353                         errbuf			OUT NOCOPY 	VARCHAR2,
354       			retcode			OUT NOCOPY 	VARCHAR2,
355       			p_sr_instance_id	IN		NUMBER,
356       			p_flat_file_load	IN		NUMBER DEFAULT 2 )
357    IS
358 
359       x_is_local		NUMBER		:= to_number(fnd_profile.value('MSD_DEM_EXPLODE_DEMAND_METHOD'));
360       x_cpp			NUMBER          := to_number(fnd_profile.value('MSD_DEM_PLANNING_PERCENTAGE'));
361       x_dem_schema      	VARCHAR2(100)	:= fnd_profile.value('MSD_DEM_SCHEMA');
362       x_curr_user		VARCHAR2(100)	:= NULL;
363       x_is_seq_present		NUMBER		:= NULL;
364       x_validation_org_id	NUMBER		:= NULL;
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;
374 
375       x_dm_time_level   varchar2(10):= null;
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 
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;
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 
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;
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 
420       IF (x_cpp = 2)
421       THEN
422          x_sql := x_sql || ' , IS_IN, EFFECTIVITY_DATE_S, DISABLE_DATE_S, PLNG_PCT_EXISTING_S, PLANNING_FACTOR_S, '
423                || '   OPTIONAL_FLAG_S, COMPONENT_CODE_S, PARENT_ITEM_ID_S ';
424       END IF;
425 
426 
427       /* If flat file load, then populate the column component_code_legacy with concatenated item names */
428       IF (p_flat_file_load = 1)
429       THEN
430          x_sql := x_sql || ' , COMPONENT_CODE_LEGACY ';
431       END IF;
432 
433       x_sql := x_sql || ' ) SELECT '
434                || to_char(x_iterator) || ' , '
435                || to_char(p_sr_instance_id) || ' , '
436                || ' iv.ebs_org_sr_pk, '
437                || ' msi.inventory_item_id, '
438                || ' msi.inventory_item_id, '
439                || ' msi.inventory_item_id, '
440                || ' 1, '
441                || ' 2, '
442                || 'sysdate, '
443                || to_char(fnd_global.user_id) || ' , '
444                || 'sysdate, '
445                || to_char(fnd_global.user_id) || ' , '
446                || to_char(fnd_global.login_id) || ' , '
447                || ' msi.sr_inventory_item_id ' || ' , '
448                || ' msi.inventory_item_id ';
449 
450      IF (x_cpp = 2)
451      THEN
452          x_sql := x_sql || ' , 1, null, null, null, null, 2, msi.sr_inventory_item_id, msi.inventory_item_id ';
453      END IF;
454 
455       /* If flat file load, then populate the column component_code_legacy with concatenated item names */
456       IF (p_flat_file_load = 1)
457       THEN
458          x_sql := x_sql || ' , msi.item_name ';
459       END IF;
460 
461       x_sql := x_sql || ' FROM (SELECT tsst.ebs_org_sr_pk, tsst.ebs_item_sr_pk '
462                || '          FROM ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
463                || '          WHERE tsst.ebs_base_model_sr_pk IS NOT NULL ';
464 
465       IF (p_flat_file_load <> 1)
466       THEN
467          x_sql := x_sql || '             AND to_char(tsst.ebs_base_model_sr_pk) = tsst.component_code  ';
468       ELSE
469          x_sql := x_sql || '             AND tsst.dm_item_code = tsst.component_code_legacy  ';
470       END IF;
471 
472       x_sql := x_sql || '          GROUP BY tsst.ebs_org_sr_pk, tsst.ebs_item_sr_pk ) iv, '
473                || '    msc_system_items msi '
474                || ' WHERE  msi.sr_instance_id = ' || to_char(p_sr_instance_id)
475                || '    AND msi.sr_inventory_item_id = iv.ebs_item_sr_pk '
476                || '    AND msi.organization_id = iv.ebs_org_sr_pk '
477                || '    AND msi.plan_id = -1 '
478                || ' UNION '
479                || ' SELECT '
480                || to_char(x_iterator) || ' , '
481                || to_char(p_sr_instance_id) || ' , '
482                || ' msi.organization_id, '
483                || ' msi.inventory_item_id, '
484                || ' msi.inventory_item_id, '
485                || ' msi.inventory_item_id, '
486                || ' 1, '
487                || ' 2, '
488                || 'sysdate, '
489                || to_char(fnd_global.user_id) || ' , '
490                || 'sysdate, '
491                || to_char(fnd_global.user_id) || ' , '
492                || to_char(fnd_global.login_id) || ' , '
493                || ' msi.sr_inventory_item_id ' || ' , '
494                || ' msi.inventory_item_id ';
495 
496       IF (x_cpp = 2)
497       THEN
498          x_sql := x_sql || ' , 1, null, null, null, null, 2, msi.sr_inventory_item_id, msi.inventory_item_id ';
499       END IF;
500 
501       /* If flat file load, then populate the column component_code_legacy with concatenated item names */
502       IF (p_flat_file_load = 1)
503       THEN
504          x_sql := x_sql || ' , msi.item_name ';
505       END IF;
506 
507       x_sql := x_sql || '          FROM ' || x_dem_schema || '.t_ep_cto tec, '
508                || '               ' || x_dem_schema || '.t_ep_cto_base_model tecbm, '
509                || '               msc_system_items msi '
510                || '          WHERE tec.t_ep_cto_demand_type_id = 1 '
511                || '             AND tecbm.t_ep_cto_base_model_id = tec.t_ep_cto_base_model_id '
512                || '             AND msi.plan_id = -1 '
513                || '             AND msi.sr_instance_id = ' || to_char(p_sr_instance_id)
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));
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 
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, '
538                || '   CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, COMPONENT_CODE, PARENT_COMPONENT_CODE ';
539 
540       IF (x_cpp = 2)
541       THEN
542          x_sql := x_sql || ' , IS_IN, EFFECTIVITY_DATE_S, DISABLE_DATE_S, PLNG_PCT_EXISTING_S, PLANNING_FACTOR_S, '
543                || '   OPTIONAL_FLAG_S, COMPONENT_CODE_S, PARENT_ITEM_ID_S ';
544       END IF;
545 
546       /* If flat file load, then populate the column component_code_legacy with concatenated item names */
547       IF (p_flat_file_load = 1)
548       THEN
549          x_sql := x_sql || ' , COMPONENT_CODE_LEGACY ';
550       END IF;
551 
552         x_sql := x_sql  || ') SELECT :bvar1, SR_INSTANCE_ID, SR_ORGANIZATION_ID, BASE_MODEL_ID, min(TOP_ATO_MODEL_ID), PARENT_ITEM_ID, COMPONENT_ITEM_ID, '
553                         || 'min(IS_BASE_MODEL), min(EFFECTIVITY_DATE), max(DISABLE_DATE), min(PLANNING_FACTOR), min(PLNG_PCT_EXISTING), min(OPTIONAL_FLAG), '
554                         || 'sysdate CREATION_DATE, '
555                         || to_char(fnd_global.user_id)  || ' CREATED_BY, '
556                         || 'sysdate LAST_UPDATE_DATE, '
557                         || to_char(fnd_global.user_id)  || ' LAST_UPDATED_BY, '
558                         || to_char(fnd_global.login_id) || ' LAST_UPDATE_LOGIN, '
559                         || ' COMPONENT_CODE, PARENT_COMPONENT_CODE ';
560 
561         if(x_cpp = 2) then
562             x_sql := x_sql  || ', min(IS_IN), min(EFFECTIVITY_DATE_S), max(DISABLE_DATE_S), min(PLNG_PCT_EXISTING_S), min(PLANNING_FACTOR_S), min(OPTIONAL_FLAG_S), '
563                             || 'COMPONENT_CODE_S, PARENT_ITEM_ID_S ';
564         end if;
565 
566         if(p_flat_file_load = 1) then
567             x_sql := x_sql || ', COMPONENT_CODE_LEGACY ';
568         end if;
569 
570       x_sql := x_sql || ' FROM ( SELECT  '
571                --|| ' :bvar1, '
572                || ' mbc.sr_instance_id, '
573                || ' dem.sr_organization_id, '
574                || ' dem.base_model_id, '
575                -- Bug#13716090
576                || ' decode (mbc.using_assembly_id, '
577                || '         dem.component_item_id, nvl(dem.top_ato_model_id,  '
578                || '                                    case   '
579                || '                                    when pitem.mrp_planning_code <> 6 '
580                || '                                    and  pitem.bom_item_type           = 1  '
581                || '                                    then dem.component_item_id  '
582                || '                                    else to_number(null)  '
583                || '                                    end '
584                || '                                     ) '
585                || '         ) top_ato_model_id, '
586                || ' mbc.using_assembly_id parent_item_id, '
587                || ' mbc.inventory_item_id component_item_id, '
588                || ' decode (citem.bom_item_type, '
589                || '         4, '
590                || '         decode(citem.pick_components_flag, '
591                || '                ''Y'', '
592                || '                2, '
593                || '                3), '
594                || '         2) is_base_model, '
595                || ' mbc.effectivity_date, '
596                || ' decode (dem.disable_date, null, mbc.disable_date, '
597                || '              decode(mbc.disable_date, null, dem.disable_date, least(dem.disable_date, mbc.disable_date))) disable_date, '
598                || ' mbc.planning_factor, '
599                || ' decode(mbc.usage_quantity/decode(mbc.usage_quantity, '
600                || '                                  null,1, '
601                || '                                  0,1, '
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)
611       THEN
612         x_sql := x_sql || ' , decode (dem.is_base_model, 1, dem.parent_component_code, decode (dem.is_in, 1, dem.parent_component_code || ''-'' || dem.component_item_id, dem.parent_component_code)) parent_component_code ';
613       ELSE
614         x_sql := x_sql || ' , decode (dem.is_base_model, 1, dem.parent_component_code, dem.parent_component_code || ''-'' || dem.component_item_id) parent_component_code ';
615       END IF;
616 
617       IF (x_cpp = 2)
618       THEN
619          x_sql := x_sql || ' , decode (citem.bom_item_type, 2, 2, 1) is_in '
620                         || ' , mbc.effectivity_date effectivity_date_s'
621                         || ' , decode (dem.disable_date_s, null, mbc.disable_date, '
622                         || '           decode(mbc.disable_date, null, dem.disable_date_s, least(dem.disable_date_s, mbc.disable_date))) disable_date_s'
623                         || ' , decode (pitem.bom_item_type, 2, dem.plng_pct_existing_s, 1) * decode(mbc.usage_quantity/decode(mbc.usage_quantity, '
624                         || '              null,1, '
625                         || '              0,1, '
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 ';
635       END IF;
636 
637       /* If flat file load, then populate the column component_code_legacy with concatenated item names */
638       IF (p_flat_file_load = 1)
639       THEN
640          IF (x_cpp = 2)
641          THEN
642             x_sql := x_sql || ' , dem.component_code_legacy || decode (citem.bom_item_type, 2, '''', ''-'' || citem.item_name) component_code_legacy ';
643          ELSE
644             x_sql := x_sql || ' , dem.component_code_legacy || ''-'' || citem.item_name component_code_legacy ';
645          END IF;
646       END IF;
647 
648 
649       x_sql := x_sql || ' FROM msd_dem_model_bom_components dem, '
650                || '      msc_boms mb, '
651                || '      msc_bom_components mbc, '
652                || '      msc_system_items pitem, '
653                || '      msc_system_items citem '
654                || '  WHERE dem.id = :bvar2 '
655                || '     AND mb.plan_id = -1 ';
656 
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 
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);
675 
676       END IF;
677 
678       x_sql := x_sql || '     AND mb.sr_instance_id = ' || to_char(p_sr_instance_id)
679                || '     AND mb.assembly_item_id = dem.component_item_id '
680                || '     AND mb.alternate_bom_designator IS NULL '
681                || '     AND mbc.plan_id = mb.plan_id '
682                || '     AND mbc.sr_instance_id = mb.sr_instance_id '
683                || '     AND mbc.bill_sequence_id = mb.bill_sequence_id '
684                || '     AND mbc.using_assembly_id = mb.assembly_item_id '
685                || '     AND nvl(mbc.disable_date, sysdate + 1 ) > :bvar3 '
686                --|| '     AND nvl(mbc.effectivity_date, sysdate - 1 ) <= sysdate '
687                || '     AND pitem.plan_id = mbc.plan_id '
688                || '     AND pitem.sr_instance_id = mbc.sr_instance_id '
689                || '     AND pitem.organization_id = mbc.organization_id '
690                || '     AND pitem.inventory_item_id = mbc.using_assembly_id '
691                || '     AND (pitem.mrp_planning_code <> 6 '
692                || '       OR (pitem.mrp_planning_code = 6 '
693                || '            AND pitem.pick_components_flag = ''Y'')) '
694                || '     AND (pitem.ato_forecast_control <> 3 '
695                || '       OR (' || to_char(x_cpp) || ' = 3 '
696                || '            AND pitem.bom_item_type = 2)) '
697                || '     AND (pitem.bom_item_type <> 4 '
698                || '       OR (pitem.bom_item_type = 4 '
699                || '          AND pitem.pick_components_flag = ''Y'')) '
700                || '     AND citem.plan_id = mbc.plan_id '
701                || '     AND citem.sr_instance_id = mbc.sr_instance_id '
702                || '     AND citem.organization_id = mbc.organization_id '
703                || '     AND citem.inventory_item_id = mbc.inventory_item_id '
704                || '     AND (citem.mrp_planning_code <> 6 '
705                || '          OR (citem.mrp_planning_code = 6 '
706                || '              AND citem.pick_components_flag = ''Y'')) '
707                || '     AND (citem.ato_forecast_control = 2 '
708                || '           OR (' || to_char(x_cpp) || ' = 3 '
709                || '               AND (citem.bom_item_type = 2 '
710                || '                    OR mbc.optional_component = 1))) ) ';
711 
712 
713         x_sql := x_sql  || ' GROUP BY SR_INSTANCE_ID, SR_ORGANIZATION_ID, BASE_MODEL_ID, PARENT_ITEM_ID, COMPONENT_ITEM_ID, '
714                         || ' COMPONENT_CODE, PARENT_COMPONENT_CODE ';
715 
716         if(x_cpp = 2) then
717             x_sql := x_sql  || ', COMPONENT_CODE_S, PARENT_ITEM_ID_S ';
718         end if;
719 
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'
729                 || ' SET is_base_model = 3 '
730                 || ' WHERE EXISTS ( SELECT 1 FROM MSD_DEM_MODEL_BOM_COMPONENTS b '
731                 || '                WHERE  b.id < :bvar1 '
732                 || '                   AND b.sr_instance_id = a.sr_instance_id '
733                 || '                   AND b.sr_organization_id = a.sr_organization_id '
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'
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 '
746                 || '                   AND b.sr_organization_id = a.sr_organization_id '
747                 || '                   AND b.base_model_id = a.base_model_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
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;
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;
774             msd_dem_common_utilities.log_debug ('Exiting Loop - as reached the bottom of the tree');
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;
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;
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;
799             msd_dem_common_utilities.log_debug ('Exiting Loop - as reached max iterator');
800             EXIT;
801          END IF;
802 
803       END LOOP;
804 
805       x_sql1 := null;
806 
807       /* For ERP Collection with profile option Yes, for Consume & Derive Options only */
808       IF (p_flat_file_load = 2 AND x_cpp = 2)
809       THEN
810 
811          /* Update the component_code column in t_src_sales_tmpl with component_code_s */
812          x_sql1 := 'UPDATE ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
813                    || ' SET component_code = (SELECT component_code_s FROM msd_dem_model_bom_components mbc '
814                    || '                       WHERE mbc.component_code = tsst.component_code AND rownum < 2 ) ';
815 
816       END IF;
817 
818       /* For Flat File Collection with profile option Yes, for Consume & Derive Options only */
819       IF (p_flat_file_load = 1  AND x_cpp = 2)
820       THEN
821 
822          /* Update the component_code column in t_src_sales_tmpl with component_code_s */
823          x_sql1 := 'UPDATE ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
824                    || ' SET component_code = (SELECT component_code_s FROM msd_dem_model_bom_components mbc '
825                    || '                       WHERE mbc.component_code_legacy = tsst.component_code_legacy AND rownum < 2 ) ';
826 
827       END IF;
828 
829       /* For Flat File Collection with profile option not set to Yes, for Consume & Derive Options only */
830       IF (p_flat_file_load = 1  AND x_cpp <> 2)
831       THEN
832 
833          /* Update the component_code column in t_src_sales_tmpl with component_code_s */
834          x_sql1 := 'UPDATE ' || x_dem_schema || '.T_SRC_SALES_TMPL tsst '
835                    || ' SET component_code = (SELECT component_code FROM msd_dem_model_bom_components mbc '
836                    || '                       WHERE mbc.component_code_legacy = tsst.component_code_legacy AND rownum < 2 ) ';
837 
838       END IF;
839 
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;
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
857 
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;
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' ;
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;
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 */
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;
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 
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 
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
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,
923           COMPONENT_QTY, OPTIONAL_FLAG, ATO_FORECAST_CONTROL,
924           CTO_START_DATE, CTO_END_DATE)
925          SELECT
926             CTO_CODE, CTO_CHILD_CODE, CTO_PARENT_CODE, BASE_MODEL_CODE, PARENT_ITEM_CODE, OPTION_CODE, ORG_CODE,
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;
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 ' ||
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 ( ' ||
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;
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');
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;
982 
983 
984 
985 
986    /*
987     * This procedure deletes all data from CTO GL Tables. This should only be run by
988     * an admin user. The user must make sure that the Demantra AS is down before running
989     * the procedure.
990     * The procedure is used when the CTO related profile options have been changed which
991     * result in changes to the bom structure brought into Demantra.
992     *
993     * Parameters -
994     *    p_complete_refresh - If 1, then all data from CTO GL tables are deleted
995     *                       - If 2, do nothing.
996     */
997    PROCEDURE PURGE_CTO_GL_DATA (
998                 errbuf              	OUT NOCOPY 	VARCHAR2,
999                 retcode             	OUT NOCOPY 	VARCHAR2,
1000                 p_complete_refresh	IN		NUMBER )
1001    IS
1002 
1003       x_schema		VARCHAR2(100) := NULL;
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');
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;
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 ';
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 
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;
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;
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;
1070 
1071 
1072 
1073 END MSD_DEM_CTO;