[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_CREATE_DEM_SEED
Source
1 PACKAGE BODY MSD_DEM_CREATE_DEM_SEED AS
2 /* $Header: msddemcrdemseedb.pls 120.1.12010000.2 2008/11/04 10:20:20 sjagathe ship $ */
3
4 procedure create_dem_seed_data(errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY VARCHAR2,
6 p_start_no in number,
7 p_num_entities in number,
8 P_entity_type in number default 0)
9 as
10 l_index NUMBER;
11 l_ebs_entity_name VARCHAR2(15);
12 l_dem_entity_name VARCHAR2(100);
13 l_stmt VARCHAR2(2000);
14
15 REAL_VALUES_SEQ varchar2(100) := 'real_values_seq';
16 COMPUTED_FIELDS_SEQ varchar2(100) := 'computed_fields_seq';
17
18 DM_COMPONENT varchar2(500);
19
20 type c_get_component is ref cursor;
21 get_component c_get_component;
22 l_stmt_get_component varchar2(1000);
23
24 l_component_id number;
25
26 type c_series_id is ref cursor;
27 get_series_id c_series_id;
28 l_stmt_series_id varchar2(1000);
29
30 l_series_id number;
31
32 type c_get_code is ref cursor;
33 get_code c_get_code;
34 l_stmt_get_code varchar2(1000);
35
36 l_stmt_comp varchar2(4000);
37
38 type c_user_id is ref cursor;
39 get_user_id c_user_id;
40 l_stmt_user_id varchar2(1000);
41
42 l_user_id number;
43
44 type c_query_id is ref cursor;
45 get_query_id c_query_id;
46 l_stmt_query_id varchar2(1000);
47
48 l_query_id number;
49
50 type c_display_unit_id is ref cursor;
51 get_display_unit_id c_display_unit_id;
52 l_stmt_display_unit_id varchar2(1000);
53
54 l_display_unit_id number;
55
56 type c_disp_order_id is ref cursor;
57 get_disp_order_id c_query_id;
58 l_stmt_disp_order_id varchar2(1000);
59
60 l_disp_order_id number;
61
62
63 begin
64
65 DEMANTRA_SCHEMA := fnd_profile.value('MSD_DEM_SCHEMA');
66 /*DEMANTRA_SCHEMA := 'dmtra_template';*/
67
68 if DEMANTRA_SCHEMA is null then
69 dbms_output.put_line('Demantra Schema is not set');
70 return;
71 end if;
72
73 if p_start_no is null or p_start_no < 0 then
74 dbms_output.put_line('Please enter a valid Starting Number');
75 return;
76 end if;
77
78 if p_num_entities <= 0 then
79 dbms_output.put_line('Please enter a valid number for Number of entities');
80 return;
81 end if;
82
83 if p_num_entities > 100 then
84 dbms_output.put_line('Please enter number of entities < 100');
85 return;
86 end if;
87
88 if P_entity_type <> 0 and P_entity_type <> 1 and P_entity_type <> 2 and P_entity_type <> 3 then
89 dbms_output.put_line('Please enter Entity Type as 1 (UOM), 2 (CURRENCY), 3 (PRICE LIST) , 0 (ALL) ');
90 return;
91 end if;
92
93
94 dbms_output.put_line('Start Number = ' || p_start_no || ', Number of entities = ' || p_num_entities);
95
96
97 if p_entity_type = 0 or p_entity_type = 1 then
98
99 dbms_output.put_line('Starting Creation of Display Units for UOMs');
100 l_index := 0;
101 while l_index < p_num_entities
102 loop
103 l_ebs_entity_name := UOM_NAME || (p_start_no + l_index);
104 dbms_output.put_line('Creating Display Unit for UOM, ' || l_ebs_entity_name);
105 l_stmt_get_code := ' select display_units from ' || DEMANTRA_SCHEMA || '.display_units ' ||
106 ' where display_units = ''' || l_ebs_entity_name || '''' ;
107 l_dem_entity_name := '';
108
109 dbms_output.put_line(l_stmt_get_code);
110 open get_code for l_stmt_get_code;
111 fetch get_code into l_dem_entity_name;
112 close get_code;
113
114 if l_dem_entity_name = l_ebs_entity_name then
115 dbms_output.put_line('Display Unit ' || l_ebs_entity_name || ' exists. Skipping....');
116 else
117
118 l_stmt := ' alter table ' || DEMANTRA_SCHEMA || '.t_ep_item ' ||
119 ' add ( ' || l_ebs_entity_name || ' NUMBER default null) ';
120 dbms_output.put_line(l_stmt);
121
122 begin
123 execute immediate l_stmt;
124 exception
125 when others then
126 dbms_output.put_line('Display Unit ' || l_ebs_entity_name || ' failed in alter statement');
127 dbms_output.put_line(substr(SQLERRM,1,250));
128 goto continue;
129 end;
130
131 l_stmt_display_unit_id := ' select max(display_units_id)+1 from ' || DEMANTRA_SCHEMA || '.display_units ';
132
133 dbms_output.put_line(l_stmt_display_unit_id);
134 open get_display_unit_id for l_stmt_display_unit_id;
135 fetch get_display_unit_id into l_display_unit_id;
136 close get_display_unit_id;
137 dbms_output.put_line(l_display_unit_id);
138
139 l_stmt := ' insert into ' || DEMANTRA_SCHEMA || '.display_units ' ||
140 ' (DISPLAY_UNITS_ID, DISPLAY_UNITS, DATA_TABLE, DATA_FIELD, DATA_EXPRESSION) ' ||
141 ' values (:1,:2,''t_ep_item'',:3,NULL) ';
142 dbms_output.put_line(l_stmt);
143 execute immediate l_stmt using l_display_unit_id, l_ebs_entity_name, l_ebs_entity_name;
144
145 dbms_output.put_line('Display Unit ' || l_ebs_entity_name || ' created');
146 <<continue>>
147 null;
148 end if;
149 l_index := l_index + 1;
150 end loop;
151 commit;
152 dbms_output.put_line('Completed Creation of Display Units for UOMs');
153
154 end if;
155
156 if p_entity_type = 0 or p_entity_type = 2 then
157
158 dbms_output.put_line('Starting Creation of Indexes for Currency');
159 l_index := 0;
160 while l_index < p_num_entities
161 loop
162 l_ebs_entity_name := CURRENCY_NAME || (p_start_no + l_index);
163
164 dbms_output.put_line('Creating Index for Currency, ' || l_ebs_entity_name);
165 l_stmt_get_code := ' select real_value from ' || DEMANTRA_SCHEMA || '.real_values ' ||
166 ' where real_value = ''' || l_ebs_entity_name || '''' ;
167
168 l_dem_entity_name := '';
169
170 dbms_output.put_line(l_stmt_get_code);
171
172 open get_code for l_stmt_get_code;
173 fetch get_code into l_dem_entity_name;
174 close get_code;
175
176 if l_dem_entity_name = l_ebs_entity_name then
177 dbms_output.put_line('Index ' || l_ebs_entity_name || ' exists. Skipping....');
178 else
179
180 begin
181 l_stmt := ' create table ' || DEMANTRA_SCHEMA || '.' || l_ebs_entity_name ||
182 ' (INDEX_DATE DATE, INDEX_VALUE NUMBER(10,5)) ';
183 dbms_output.put_line(l_stmt);
184 execute immediate l_stmt;
185 exception
186 when others then
187 dbms_output.put_line('Real Value ' || l_ebs_entity_name || ' failed in create statement');
188 dbms_output.put_line(substr(SQLERRM,1,250));
189 goto continue1;
190 end;
191
192 l_stmt := ' insert into ' || DEMANTRA_SCHEMA || '.real_values ' ||
193 ' (REAL_VALUE_ID, REAL_VALUE, REAL_TABLE, CALCULATION_TYPE, IS_DEFAULT) ' ||
194 ' values (' || DEMANTRA_SCHEMA || '.' || REAL_VALUES_SEQ || '.nextval,:1,:2,2,NULL) ';
195 dbms_output.put_line(l_stmt);
196 execute immediate l_stmt using l_ebs_entity_name, l_ebs_entity_name;
197
198
199 dbms_output.put_line('Index ' || l_ebs_entity_name || ' created');
200
201 <<continue1>>
202 null;
203 end if;
204 l_index := l_index + 1;
205 end loop;
206 commit;
207 dbms_output.put_line('Completed Creation of Indexes for Currency');
208
209 end if;
210
211 if p_entity_type = 0 or p_entity_type = 3 then
212
213 -- Bug#7199587 syenamar
214 -- replacing code using hard coded english names with ids for demantra objects
215
216 /*DM_COMPONENT := msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT');
217
218 DM_COMPONENT := 'Demand Management';
219
220 if DM_COMPONENT is null then
221 dbms_output.put_line('Demand Management component is null');
222 return;
223 end if;
224
225 l_stmt_get_component := 'select dcm_product_id from ' || DEMANTRA_SCHEMA || '.dcm_products where product_name = ''' || DM_COMPONENT || '''';
226 dbms_output.put_line(l_stmt_get_component);
227
228 open get_component for l_stmt_get_component;
229 fetch get_component into l_component_id;
230 close get_component;*/
231
232 l_component_id := to_number(msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT'));
233 dbms_output.put_line(l_component_id);
234
235 l_stmt_user_id := 'select user_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where dcm_product_id = ' || l_component_id;
236 dbms_output.put_line(l_stmt_user_id);
237
238 open get_user_id for l_stmt_user_id;
239 fetch get_user_id into l_user_id;
240 close get_user_id;
241 dbms_output.put_line(l_user_id);
242
243 l_stmt_query_id := 'select tq.id from ' ||
244 -- DEMANTRA_SCHEMA || '.transfer_list tl, ' || --> removing this as integration interface id is obtained from lookup
245 DEMANTRA_SCHEMA || '.transfer_query tq ' ||
246 'where tq.transfer_id = ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST');
247 dbms_output.put_line(l_stmt_query_id);
248
249 open get_query_id for l_stmt_query_id;
250 fetch get_query_id into l_query_id;
251 close get_query_id;
252 dbms_output.put_line(l_query_id);
253 -- syenamar
254
255 dbms_output.put_line('Starting Creation of Display Units for Price Lists');
256 l_index := 0;
257 while l_index < p_num_entities
258 loop
259 l_ebs_entity_name := PRICELIST_NAME || (p_start_no + l_index);
260 dbms_output.put_line('Creating Display Unit for Price List, ' || l_ebs_entity_name);
261 l_stmt_get_code := ' select display_units from ' || DEMANTRA_SCHEMA || '.display_units ' ||
262 ' where display_units = ''' || l_ebs_entity_name || '''' ;
263
264 l_dem_entity_name := '';
265
266 dbms_output.put_line(l_stmt_get_code);
267 open get_code for l_stmt_get_code;
268 fetch get_code into l_dem_entity_name;
269 close get_code;
270
271 if l_dem_entity_name = l_ebs_entity_name then
272 dbms_output.put_line('Display Unit ' || l_ebs_entity_name || ' exists. Skipping....');
273 else
274
275 begin
276 l_stmt := ' alter table ' || DEMANTRA_SCHEMA || '.sales_data ' ||
277 ' add (' || l_ebs_entity_name || ' NUMBER default null) ';
278 dbms_output.put_line(l_stmt);
279 execute immediate l_stmt;
280
281 l_stmt := ' alter table ' || DEMANTRA_SCHEMA || '.' || INTG_TABLE ||
282 ' add (' || l_ebs_entity_name || ' NUMBER default null) ';
283 dbms_output.put_line(l_stmt);
284 execute immediate l_stmt;
285
286 exception
287 when others then
288 dbms_output.put_line('Display Unit for Price List ' || l_ebs_entity_name || ' failed in alter statement');
289 dbms_output.put_line(substr(SQLERRM,1,250));
290 goto continue2;
291 end;
292
293 l_stmt_display_unit_id := ' select max(display_units_id)+1 from ' || DEMANTRA_SCHEMA || '.display_units ';
294
295 dbms_output.put_line(l_stmt_display_unit_id);
296 open get_display_unit_id for l_stmt_display_unit_id;
297 fetch get_display_unit_id into l_display_unit_id;
298 close get_display_unit_id;
299 dbms_output.put_line(l_display_unit_id);
300
301 l_stmt := ' insert into ' || DEMANTRA_SCHEMA || '.display_units ' ||
302 ' (DISPLAY_UNITS_ID, DISPLAY_UNITS, DATA_TABLE, DATA_FIELD, DATA_EXPRESSION) ' ||
303 ' values (:1,:2,''sales_data'',:3,NULL) ';
304 dbms_output.put_line(l_stmt);
305 execute immediate l_stmt using l_display_unit_id, l_ebs_entity_name, l_ebs_entity_name;
306
307 l_stmt_series_id := 'select ' || DEMANTRA_SCHEMA || '.' || COMPUTED_FIELDS_SEQ || '.nextval from dual';
308
309 dbms_output.put_line(l_stmt_series_id);
310 open get_series_id for l_stmt_series_id;
311 fetch get_series_id into l_series_id;
312 close get_series_id;
313 dbms_output.put_line(l_series_id);
314
315 l_stmt_disp_order_id := ' select max(disp_order)+1 from ' || DEMANTRA_SCHEMA || '.computed_fields ';
316
317 dbms_output.put_line(l_stmt_disp_order_id);
318 open get_disp_order_id for l_stmt_disp_order_id;
319 fetch get_disp_order_id into l_disp_order_id;
320 close get_disp_order_id;
321 dbms_output.put_line(l_disp_order_id);
322
323
324 l_stmt_comp := 'INSERT INTO ' || DEMANTRA_SCHEMA || '.COMPUTED_FIELDS("FORECAST_TYPE_ID", "COMPUTED_NAME", "EXP_TEMPLATE", "DISP_COLOR", "DISP_LSTYLE", "DISP_LSYMBOL", ' ||
325 ' "PRINT_COLOR", "PRINT_LSTYLE", "PRINT_LSYMBOL", "DISP_ORDER", "INFO_TYPE", "TABLE_FORMAT", ' ||
326 ' "DO_HAVING", "COMPUTED_TITLE", "FIELD_TYPE", "SUM_FUNC", "MODE_1", "MODE_COLOR", "SCALEBLE", ' ||
327 ' "TIME_AVG", "MODULE_TYPE", "DEPENDANTS", "EDITABLE", "IS_PROPORTION", "NULL_AS_ZERO", "DBNAME", ' ||
328 ' "IS_DDLB", "IS_CHECK", "SERIES_WIDTH", "DROPDOWN_TABLE_NAME", "WEB_FORMULA", "IS_DEFAULT", ' ||
329 ' "HINT_MESSAGE", "COMPUTEDFIELD_EXPRESSION", "CLIENT_EXP_DISP", "HIST_PRED_TYPE", "ITEMCHANGE", ' ||
330 ' "LOCK_EXP", "DATA_TABLE_NAME", "COMP_DEPEND_ORDER", "DEPEND_ON_EXP_SERVER", "BACKGROUND_COLOR_EXP", ' ||
331 ' "SYNCRO_FIELD", "LOCK_EXP_DISP", "BACKGROUND_EXP_DISP", "WEB_LOCK_EXPRESSION", "ATTRIBUTES_DEPENDENTS", ' ||
332 ' "CALCULATION_METHOD", "LOOP_NUMBER", "MOVE_UPD_BET_FORE", "MOVE_FROM_SALES_TO_FOR", "LOOKUP_TYPE", ' ||
333 ' "LOOKUP_TABLE", "LOOKUP_DISPLAY_FIELD", "LOOKUP_DATA_FIELD", "LOOKUP_EXTRA_FROM", "LOOKUP_EXTRA_WHERE", ' ||
334 ' "COL_SERIES_WIDTH", "IS_RANKING", "PROP_CALC_SERIES", "UNLINKED_LEVEL_ID", "UPDATE_BY_SERIES_ID", ' ||
335 ' "EXTRA_FROM", "EXTRA_WHERE", "BASE_LEVEL", "COLOR_DEPENDANTS", "LOCK_EXP_DEPENDANTS", "EXPRESSION_TYPE", ' ||
336 ' "INT_AGGR_FUNC", "WAVG_BY_SERIES", "AGGR_BY", "SUMMARY_LINE_EXP", "SUMMARY_LINE_EXP_DISP", ' ||
337 ' "SUMMARY_LINE_DEPENDENTS", "PRESERVATION_TYPE", "FILTER_EXP", "FILTER_EXP_DISP", "FILTER_EXP_DEPENDANTS", ' ||
338 ' "FILTER_EXP_COL_DEPENDANTS", "IS_EDITABLE_SUMMARY", "MOVE_PRESERVATION_TYPE", "TABLE_ID", "DATA_TYPE", ' ||
339 ' "SAME_VAL_UPDATE") VALUES(' || l_series_id ||
340 ', :1, ''avg(branch_data.' || lower(l_ebs_entity_name) || ')'', 255, 1, 1, 255, 1, 1, :2, 1, NULL, 0, :3, ' ||
341 ' ''1'', ''avg'', NULL, NULL, 0, NULL, 0, NULL, 0, 0, 0, :4, 0, 0, 250, NULL, NULL, 0, NULL, ' ||
342 ' NULL, NULL, 3, NULL, NULL, ''branch_data'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,' ||
343 ' NULL, 0, NULL, NULL, NULL, NULL, NULL, 10, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, 1, ''Avg'', ' ||
344 ' NULL, 0, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, 0, 3, NULL, 1, 0)';
345
346 dbms_output.put_line('Inserting into computed_fields');
347 execute immediate l_stmt_comp using l_ebs_entity_name, l_disp_order_id, l_ebs_entity_name, lower(l_ebs_entity_name) ;
348
349
350 l_stmt := 'insert into ' || DEMANTRA_SCHEMA || '.dcm_products_series' ||
351 ' (dcm_product_id ,series_id) ' ||
352 ' (select :1, :2 from dual)';
353
354 execute immediate l_stmt using l_component_id, l_series_id;
355
356 l_stmt := 'insert into ' || DEMANTRA_SCHEMA || '.user_security_series ' ||
357 ' (user_id, series_id ) ' ||
358 ' values (:1, :2)';
359
360 dbms_output.put_line(l_stmt);
361
362 execute immediate l_stmt using l_user_id, l_series_id;
363
364
365 l_stmt := 'insert into ' || DEMANTRA_SCHEMA || '.transfer_query_series '
366 || '(id, series_id, load_option, purge_option)'
367 || ' values '
368 || ' (:1,:2,2,0)';
369
370 dbms_output.put_line(l_stmt);
371 execute immediate l_stmt using l_query_id, l_series_id;
372
373 dbms_output.put_line('Display Unit for Price List' || l_ebs_entity_name || ' created');
374
375 <<continue2>>
376 null;
377 end if;
378 l_index := l_index + 1;
379 end loop;
380 commit;
381 dbms_output.put_line('Completed Creation of Display Units for Price Lists');
382
383 end if;
384
385 exception
386 when others then
387 dbms_output.put_line(substr(SQLERRM,1,150));
388 retcode := 1;
389
390 retcode := 0;
391
392 end;
393
394 end MSD_DEM_CREATE_DEM_SEED;
395