[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.12000000.2 2007/09/25 06:33:49 syenamar noship $ */
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 DM_COMPONENT := msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT');
214
215 /*DM_COMPONENT := 'Demand Management';*/
216
217 if DM_COMPONENT is null then
218 dbms_output.put_line('Demand Management component is null');
219 return;
220 end if;
221
222 l_stmt_get_component := 'select dcm_product_id from ' || DEMANTRA_SCHEMA || '.dcm_products where product_name = ''' || DM_COMPONENT || '''';
223 dbms_output.put_line(l_stmt_get_component);
224
225 open get_component for l_stmt_get_component;
226 fetch get_component into l_component_id;
227 close get_component;
228
229 dbms_output.put_line(l_component_id);
230
231 l_stmt_user_id := 'select user_id from ' || DEMANTRA_SCHEMA || '.user_id where lower(user_name) = ''dm''';
232 dbms_output.put_line(l_stmt_user_id);
233
234 open get_user_id for l_stmt_user_id;
235 fetch get_user_id into l_user_id;
236 close get_user_id;
237 dbms_output.put_line(l_user_id);
238
239 l_stmt_query_id := 'select tq.id from ' ||
240 DEMANTRA_SCHEMA || '.transfer_list tl, ' ||
241 DEMANTRA_SCHEMA || '.transfer_query tq ' ||
242 ' where name like ''EBS Price List''' ||
243 ' and tq.transfer_id = tl.id ' ;
244 dbms_output.put_line(l_stmt_query_id);
245
246 open get_query_id for l_stmt_query_id;
247 fetch get_query_id into l_query_id;
248 close get_query_id;
249 dbms_output.put_line(l_query_id);
250
251
252 dbms_output.put_line('Starting Creation of Display Units for Price Lists');
253 l_index := 0;
254 while l_index < p_num_entities
255 loop
256 l_ebs_entity_name := PRICELIST_NAME || (p_start_no + l_index);
257 dbms_output.put_line('Creating Display Unit for Price List, ' || l_ebs_entity_name);
258 l_stmt_get_code := ' select display_units from ' || DEMANTRA_SCHEMA || '.display_units ' ||
259 ' where display_units = ''' || l_ebs_entity_name || '''' ;
260
261 l_dem_entity_name := '';
262
263 dbms_output.put_line(l_stmt_get_code);
264 open get_code for l_stmt_get_code;
265 fetch get_code into l_dem_entity_name;
266 close get_code;
267
268 if l_dem_entity_name = l_ebs_entity_name then
269 dbms_output.put_line('Display Unit ' || l_ebs_entity_name || ' exists. Skipping....');
270 else
271
272 begin
273 l_stmt := ' alter table ' || DEMANTRA_SCHEMA || '.sales_data ' ||
274 ' add (' || l_ebs_entity_name || ' NUMBER default null) ';
275 dbms_output.put_line(l_stmt);
276 execute immediate l_stmt;
277
278 l_stmt := ' alter table ' || DEMANTRA_SCHEMA || '.' || INTG_TABLE ||
279 ' add (' || l_ebs_entity_name || ' NUMBER default null) ';
280 dbms_output.put_line(l_stmt);
281 execute immediate l_stmt;
282
283 exception
284 when others then
285 dbms_output.put_line('Display Unit for Price List ' || l_ebs_entity_name || ' failed in alter statement');
286 dbms_output.put_line(substr(SQLERRM,1,250));
287 goto continue2;
288 end;
289
290 l_stmt_display_unit_id := ' select max(display_units_id)+1 from ' || DEMANTRA_SCHEMA || '.display_units ';
291
292 dbms_output.put_line(l_stmt_display_unit_id);
293 open get_display_unit_id for l_stmt_display_unit_id;
294 fetch get_display_unit_id into l_display_unit_id;
295 close get_display_unit_id;
296 dbms_output.put_line(l_display_unit_id);
297
298 l_stmt := ' insert into ' || DEMANTRA_SCHEMA || '.display_units ' ||
299 ' (DISPLAY_UNITS_ID, DISPLAY_UNITS, DATA_TABLE, DATA_FIELD, DATA_EXPRESSION) ' ||
300 ' values (:1,:2,''sales_data'',:3,NULL) ';
301 dbms_output.put_line(l_stmt);
302 execute immediate l_stmt using l_display_unit_id, l_ebs_entity_name, l_ebs_entity_name;
303
304 l_stmt_series_id := 'select ' || DEMANTRA_SCHEMA || '.' || COMPUTED_FIELDS_SEQ || '.nextval from dual';
305
306 dbms_output.put_line(l_stmt_series_id);
307 open get_series_id for l_stmt_series_id;
308 fetch get_series_id into l_series_id;
309 close get_series_id;
310 dbms_output.put_line(l_series_id);
311
312 l_stmt_disp_order_id := ' select max(disp_order)+1 from ' || DEMANTRA_SCHEMA || '.computed_fields ';
313
314 dbms_output.put_line(l_stmt_disp_order_id);
315 open get_disp_order_id for l_stmt_disp_order_id;
316 fetch get_disp_order_id into l_disp_order_id;
317 close get_disp_order_id;
318 dbms_output.put_line(l_disp_order_id);
319
320
321 l_stmt_comp := 'INSERT INTO ' || DEMANTRA_SCHEMA || '.COMPUTED_FIELDS("FORECAST_TYPE_ID", "COMPUTED_NAME", "EXP_TEMPLATE", "DISP_COLOR", "DISP_LSTYLE", "DISP_LSYMBOL", ' ||
322 ' "PRINT_COLOR", "PRINT_LSTYLE", "PRINT_LSYMBOL", "DISP_ORDER", "INFO_TYPE", "TABLE_FORMAT", ' ||
323 ' "DO_HAVING", "COMPUTED_TITLE", "FIELD_TYPE", "SUM_FUNC", "MODE_1", "MODE_COLOR", "SCALEBLE", ' ||
324 ' "TIME_AVG", "MODULE_TYPE", "DEPENDANTS", "EDITABLE", "IS_PROPORTION", "NULL_AS_ZERO", "DBNAME", ' ||
325 ' "IS_DDLB", "IS_CHECK", "SERIES_WIDTH", "DROPDOWN_TABLE_NAME", "WEB_FORMULA", "IS_DEFAULT", ' ||
326 ' "HINT_MESSAGE", "COMPUTEDFIELD_EXPRESSION", "CLIENT_EXP_DISP", "HIST_PRED_TYPE", "ITEMCHANGE", ' ||
327 ' "LOCK_EXP", "DATA_TABLE_NAME", "COMP_DEPEND_ORDER", "DEPEND_ON_EXP_SERVER", "BACKGROUND_COLOR_EXP", ' ||
328 ' "SYNCRO_FIELD", "LOCK_EXP_DISP", "BACKGROUND_EXP_DISP", "WEB_LOCK_EXPRESSION", "ATTRIBUTES_DEPENDENTS", ' ||
329 ' "CALCULATION_METHOD", "LOOP_NUMBER", "MOVE_UPD_BET_FORE", "MOVE_FROM_SALES_TO_FOR", "LOOKUP_TYPE", ' ||
330 ' "LOOKUP_TABLE", "LOOKUP_DISPLAY_FIELD", "LOOKUP_DATA_FIELD", "LOOKUP_EXTRA_FROM", "LOOKUP_EXTRA_WHERE", ' ||
331 ' "COL_SERIES_WIDTH", "IS_RANKING", "PROP_CALC_SERIES", "UNLINKED_LEVEL_ID", "UPDATE_BY_SERIES_ID", ' ||
332 ' "EXTRA_FROM", "EXTRA_WHERE", "BASE_LEVEL", "COLOR_DEPENDANTS", "LOCK_EXP_DEPENDANTS", "EXPRESSION_TYPE", ' ||
333 ' "INT_AGGR_FUNC", "WAVG_BY_SERIES", "AGGR_BY", "SUMMARY_LINE_EXP", "SUMMARY_LINE_EXP_DISP", ' ||
334 ' "SUMMARY_LINE_DEPENDENTS", "PRESERVATION_TYPE", "FILTER_EXP", "FILTER_EXP_DISP", "FILTER_EXP_DEPENDANTS", ' ||
335 ' "FILTER_EXP_COL_DEPENDANTS", "IS_EDITABLE_SUMMARY", "MOVE_PRESERVATION_TYPE", "TABLE_ID", "DATA_TYPE", ' ||
336 ' "SAME_VAL_UPDATE") VALUES(' || l_series_id ||
337 ', :1, ''avg(branch_data.' || lower(l_ebs_entity_name) || ')'', 255, 1, 1, 255, 1, 1, :2, 1, NULL, 0, :3, ' ||
338 ' ''1'', ''avg'', NULL, NULL, 0, NULL, 0, NULL, 0, 0, 0, :4, 0, 0, 250, NULL, NULL, 0, NULL, ' ||
339 ' NULL, NULL, 3, NULL, NULL, ''branch_data'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,' ||
340 ' NULL, 0, NULL, NULL, NULL, NULL, NULL, 10, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, 1, ''Avg'', ' ||
341 ' NULL, 0, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, 0, 3, NULL, 1, 0)';
342
343 dbms_output.put_line('Inserting into computed_fields');
344 execute immediate l_stmt_comp using l_ebs_entity_name, l_disp_order_id, l_ebs_entity_name, lower(l_ebs_entity_name) ;
345
346
347 l_stmt := 'insert into ' || DEMANTRA_SCHEMA || '.dcm_products_series' ||
348 ' (dcm_product_id ,series_id) ' ||
349 ' (select :1, :2 from dual)';
350
351 execute immediate l_stmt using l_component_id, l_series_id;
352
353 l_stmt := 'insert into ' || DEMANTRA_SCHEMA || '.user_security_series ' ||
354 ' (user_id, series_id ) ' ||
355 ' values (:1, :2)';
356
357 dbms_output.put_line(l_stmt);
358
359 execute immediate l_stmt using l_user_id, l_series_id;
360
361
362 l_stmt := 'insert into ' || DEMANTRA_SCHEMA || '.transfer_query_series '
363 || '(id, series_id, load_option, purge_option)'
364 || ' values '
365 || ' (:1,:2,2,0)';
366
367 dbms_output.put_line(l_stmt);
368 execute immediate l_stmt using l_query_id, l_series_id;
369
370 dbms_output.put_line('Display Unit for Price List' || l_ebs_entity_name || ' created');
371
372 <<continue2>>
373 null;
374 end if;
375 l_index := l_index + 1;
376 end loop;
377 commit;
378 dbms_output.put_line('Completed Creation of Display Units for Price Lists');
379
380 end if;
381
382 exception
383 when others then
384 dbms_output.put_line(substr(SQLERRM,1,150));
385 retcode := 1;
386
387 retcode := 0;
388
389 end;
390
391 end MSD_DEM_CREATE_DEM_SEED;
392