[Home] [Help]
PACKAGE BODY: APPS.MSD_TRANSLATE_FACT_DATA
Source
1 PACKAGE BODY MSD_TRANSLATE_FACT_DATA AS
2 /* $Header: msdtfctb.pls 120.6 2007/06/11 08:48:06 vrepaka ship $ */
3
4 --Constants
5 C_DAILY_BUCKET CONSTANT VARCHAR2(10) := '9';
6 C_WEEKLY_BUCKET CONSTANT VARCHAR2(10) := '1';
7 C_MONTHLY_BUCKET CONSTANT VARCHAR2(10) := '2';
8 C_MONTH_TO_ADD CONSTANT NUMBER := 4;
9 C_FROM_DATE CONSTANT DATE := to_date('01-01-1000','DD-MM-YYYY');
10 C_TO_DATE CONSTANT DATE := to_date('01-01-4000','DD-MM-YYYY');
11
12
13 /* Bug# 4747555 */
14 C_INCLUDE CONSTANT NUMBER := 2;
15 C_EXCLUDE CONSTANT NUMBER := 3;
16
17 PROCEDURE populate_calendar_dates(p_cal_code VARCHAR2,
18 p_min_date DATE,
19 p_max_date DATE,
20 p_dblink VARCHAR2 );
21
22 PROCEDURE populate_cs_data_header(p_instance in number,
23 p_name IN VARCHAR2,
24 p_ref_num in number);
25
26 procedure translate_shipment_data(
27 errbuf OUT NOCOPY VARCHAR2,
28 retcode OUT NOCOPY VARCHAR2,
29 p_source_table IN VARCHAR2,
30 p_dest_table IN VARCHAR2,
31 p_instance_id IN NUMBER,
32 p_from_date IN DATE,
33 p_to_date IN DATE,
34 p_new_refresh_num IN NUMBER,
35 p_delete_flag IN VARCHAR2,
36 p_collect_ISO IN NUMBER DEFAULT SYS_NO, /* Bug# 4615390 ISO, Bug# 4865396 */
37 p_order_type_flag IN NUMBER DEFAULT C_ALL, /* Bug# 4747555*/
38 p_order_type_ids IN VARCHAR2 DEFAULT NULL) IS
39
40
41 v_instance_id varchar2(40);
42 v_dblink varchar2(128);
43 v_retcode number;
44 v_sql_stmt varchar2(4000);
45 v_date_range varchar2(2000);
46 v_ref_num number;
47
48 /* Bug# 4615390 ISO , additional filter condition if p_collect_ISO = SYS_NO */
49 v_exclude_ISO varchar2(255);
50
51 v_order_type_condition varchar2(2000); /* Bug# 4747555 */
52
53 Begin
54
55
56 /**************************************************
57 - 1. Get the instance id from MSC_APP_INSTANCE
58 - 2. Get the Profile Value for MSD_DIRECT_LOAD
59 - to identify whether we need to insert the
60 - data into the staging tables or the
61 - fact tables.
62 - 3. Check for the Data Duplication, we should
63 - use the shipped_date for this
64 fact data.
65 - 4. Insert the Data accordingly into the
66 - Staging or the Fact table based on the
67 - MSD_SR_SHIPMENT_DATA_V.
68 - 5. Commit
69 ****************************************************/
70
71
72 retcode :=0;
73
74 /* Always populate date range even though the range is null
75 In case of null, we will use extremely small and large date
76 for the from date and to date */
77
78 v_date_range := v_date_range ||
79 ' and trunc(shipped_date) between '||
80 ' to_date(to_char(:p_from_date, ''dd-mon-yyyy''), ''DD-MON-RRRR'') ' ||
81 ' AND to_date(to_char(:p_to_date,''dd-mon-yyyy''),''DD-MON-RRRR'') ' ;
82
83 /* Bug# 4615390 ISO , If p_collect_ISO = SYS_NO, then
84 * include an additional condition to filter out Internal Sales Orders
85 */
86 v_exclude_ISO := v_exclude_ISO ||
87 ' AND nvl(ORDER_SOURCE_ID, 0) <> 10 ';
88
89 /* Check dest_table */
90 IF p_dest_table = MSD_COMMON_UTILITIES.SHIPMENT_STAGING_TABLE then
91
92 /* Physically delete existing data before inserting new rows*/
93 v_sql_stmt := 'DELETE FROM msd_st_shipment_data '||
94 ' WHERE instance = ''' || p_instance_id || '''' ;
95 v_sql_stmt := v_sql_stmt || v_date_range;
96
97 if p_delete_flag = 'Y' then
98 EXECUTE IMMEDIATE v_sql_stmt
99 USING nvl(p_from_date, C_FROM_DATE),
100 nvl(p_to_date, C_TO_DATE);
101 end if;
102
103 /* Bug# 4747555 */
104 IF p_order_type_flag = C_INCLUDE THEN
105 v_order_type_condition := ' AND ORDER_TYPE_ID IN (' || p_order_type_ids || ') ';
106 ELSIF p_order_type_flag = C_EXCLUDE THEN
107 v_order_type_condition := ' AND ORDER_TYPE_ID NOT IN (' || p_order_type_ids || ') ';
108 ELSE
109 v_order_type_condition := NULL;
110 END IF;
111
112 /* DWK Added sr_original_item_pk to insert stmt */
113 /* Bug# 4615390 ISO , added ORDER_SOURCE_ID to the insert stmt */
114 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
115 ' (instance, inv_org, item, customer, sales_channel, '||
116 'sales_rep, ship_to_loc, user_defined1, user_defined2, '||
117 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
118 'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
119 'booked_date, requested_date, promised_date, shipped_date, ' ||
120 'scheduled_ship_date, scheduled_arrival_date, amount, qty_shipped, creation_date, created_by, ' ||
121 'last_update_date, last_updated_by, last_update_login, '||
122 'sr_original_item_pk, sr_parent_item_pk, sr_demand_class_pk, ORDER_SOURCE_ID ) '||
123 'SELECT '''||p_instance_id ||
124 ''', inv_org, item, customer, sales_channel, sales_rep, '||
125 'ship_to_loc, user_defined1, user_defined2, '||
126 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
127 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
128 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
129 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
130 'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
131 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
132 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
133 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
134 'booked_date,'||
135 'requested_date, promised_date, shipped_date, scheduled_ship_date, scheduled_arrival_date, amount, '||
136 'qty_shipped, sysdate, '|| FND_GLOBAL.USER_ID ||
137 ', sysdate, '|| FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||
138 ', to_char(sr_original_item_pk), to_char(sr_parent_item_pk), to_char(sr_demand_class_pk) ' ||
139 ', ORDER_SOURCE_ID ' ||
140 'FROM ' || p_source_table ||' where 1 = 1';
141
142 v_sql_stmt := v_sql_stmt || v_date_range;
143
144 /* Bug# 4615390 ISO , If p_collect_ISO = SYS_NO, then
145 * include an additional condition to filter out Internal Sales Orders
146 */
147 IF p_collect_ISO = SYS_NO THEN
148 v_sql_stmt := v_sql_stmt || v_exclude_ISO;
149 END IF;
150
151 /* Bug# 4747555 */
152 v_sql_stmt := v_sql_stmt || v_order_type_condition;
153
154 ELSIF p_dest_table = MSD_COMMON_UTILITIES.SHIPMENT_FACT_TABLE then
155 /* Mark delete for overlapping rows and Update its last_refresh_num */
156
157 v_sql_stmt := ' UPDATE msd_shipment_data ' ||
158 ' SET last_refresh_num = ' || p_new_refresh_num ||
159 ', Action_code = ' || '''D''' ||
160 ' WHERE Action_code = ''I'' and instance = '||p_instance_id || v_date_range;
161
162 EXECUTE IMMEDIATE v_sql_stmt
163 USING nvl(p_from_date, C_FROM_DATE),
164 nvl(p_to_date, C_TO_DATE);
165
166
167 /* DWK Added sr_original_item_pk to insert stmt */
168 /* Bug# 4615390 ISO , added ORDER_SOURCE_ID to the insert stmt */
169 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
170 ' (instance, inv_org, item, customer, sales_channel, '||
171 'sales_rep, ship_to_loc, user_defined1, user_defined2, '||
172 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
173 'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
174 'booked_date, requested_date, promised_date, shipped_date, ' ||
175 'scheduled_ship_date, scheduled_arrival_date, amount, ' ||
176 'qty_shipped, creation_date, created_by, ' ||
177 'last_update_date, last_updated_by, last_update_login, '||
178 'last_refresh_num, created_by_refresh_num, action_code, '||
179 'sr_original_item_pk, sr_parent_item_pk, sr_demand_class_pk, ORDER_SOURCE_ID ) '||
180 'SELECT '''||p_instance_id ||
181 ''', inv_org, item, customer, sales_channel, sales_rep, '||
182 'ship_to_loc, user_defined1, user_defined2, '||
183 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
184 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
185 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
186 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
187 'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
188 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
189 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
190 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
191 'booked_date,'||
192 'requested_date, promised_date, shipped_date, ' ||
193 'scheduled_ship_date, scheduled_arrival_date, ' ||
194 'amount, qty_shipped, sysdate, '|| FND_GLOBAL.USER_ID ||
195 ', sysdate, '|| FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||
196 ', '|| p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' ||
197 ', to_char(sr_original_item_pk), to_char(sr_parent_item_pk), to_char(sr_demand_class_pk) ' ||
198 ', ORDER_SOURCE_ID ' ||
199 'FROM ' || p_source_table ||' where 1 = 1';
200
201 v_sql_stmt := v_sql_stmt || v_date_range;
202
203
204 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
205 rows into fact table*/
206 if (p_source_table = MSD_COMMON_UTILITIES.SHIPMENT_STAGING_TABLE) then
207 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
208 end if;
209
210 END IF;
211
212 EXECUTE IMMEDIATE v_sql_stmt
213 USING nvl(p_from_date, C_FROM_DATE),
214 nvl(p_to_date, C_TO_DATE);
215
216 IF p_dest_table = MSD_COMMON_UTILITIES.SHIPMENT_FACT_TABLE then
217
218 Begin
219
220 select 1 into v_ref_num
221 from msd_cs_data_headers
222 where cs_definition_id in (select cs_definition_id
223 from msd_cs_definitions
224 where name = 'MSD_SHIPMENT_HISTORY');
225
226 update msd_cs_data_headers
227 set last_refresh_num = p_new_refresh_num
228 where cs_definition_id in (select cs_definition_id
229 from msd_cs_definitions
230 where name in ('MSD_SHIPMENT_HISTORY','MSD_SHIPMENT_ORIG_HISTORY'));
231
232 Exception When No_Data_Found Then
233 populate_cs_data_header(p_instance_id,
234 'MSD_SHIPMENT_HISTORY',
235 p_new_refresh_num);
236
237 populate_cs_data_header(p_instance_id,
238 'MSD_SHIPMENT_ORIG_HISTORY',
239 p_new_refresh_num);
240
241
242 END;
243 END IF;
244
245
246 retcode := 0;
247
248 EXCEPTION
249 when others then
250 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
251 errbuf := substr(SQLERRM,1,150);
252 retcode := -1 ;
253 raise;
254
255 End translate_shipment_data ;
256
257
258 procedure translate_booking_data(
259 errbuf OUT NOCOPY VARCHAR2,
260 retcode OUT NOCOPY VARCHAR2,
261 p_source_table IN VARCHAR2,
262 p_dest_table IN VARCHAR2,
263 p_instance_id IN NUMBER,
264 p_from_date IN DATE,
265 p_to_date IN DATE,
266 p_new_refresh_num IN NUMBER,
267 p_delete_flag IN VARCHAR2,
268 p_collect_ISO IN NUMBER DEFAULT SYS_NO, /* Bug# 4615390 ISO, Bug# 4865396 */
269 p_order_type_flag IN NUMBER DEFAULT C_ALL, /* Bug# 4747555*/
270 p_order_type_ids IN VARCHAR2 DEFAULT NULL) IS
271
272
273 v_instance_id varchar2(40);
274 v_dblink varchar2(128);
275 v_retcode number;
276 v_sql_stmt varchar2(4000);
277 v_date_range varchar2(2000);
278 v_ref_num number;
279
280 /* Bug# 4615390 ISO , additional filter condition if p_collect_ISO = SYS_NO */
281 v_exclude_ISO varchar2(255);
282
283 v_order_type_condition varchar2(2000); /* Bug# 4747555 */
284
285 Begin
286
287
288 /**************************************************
289 - 1. Get the instance id from MSC_APP_INSTANCE
290 - 2. Get the Profile Value for MSD_DIRECT_LOAD
291 - to identify whether we need to insert the
292 - data into the staging tables or the
293 - fact tables.
294 - 3. Check for the Data Duplication, we should
295 - use the shipped_date for this fact data.
296 - 4. Insert the Data accordingly into the
297 - Staging or the Fact table based on the
298 - MSD_SR_BOOKING_DATA_V.
299 - 5. Commit
300 ****************************************************/
301
302 retcode :=0;
303 /* Always populate date range even though the range is null
304 In case of null, we will use extremely small and large date
305 for the from date and to date */
306
307 v_date_range := v_date_range ||
308 ' and trunc(booked_date) between '||
309 ' to_date(to_char(:p_from_date, ''dd-mon-yyyy''), ''DD-MON-RRRR'') ' ||
310 ' AND to_date(to_char(:p_to_date, ''dd-mon-yyyy''),''DD-MON-RRRR'') ' ;
311
312 /* Bug# 4615390 ISO , If p_collect_ISO = SYS_NO, then
313 * include an additional condition to filter out Internal Sales Orders
314 */
315 v_exclude_ISO := v_exclude_ISO ||
316 ' AND nvl(ORDER_SOURCE_ID, 0) <> 10 ';
317
318 /* Check dest_table */
319 IF p_dest_table = MSD_COMMON_UTILITIES.BOOKING_STAGING_TABLE then
320
321 /* Physically delete existing data before inserting new rows*/
322 v_sql_stmt := 'DELETE FROM msd_st_booking_data '||
323 ' WHERE instance = ''' || p_instance_id || '''' ;
324 v_sql_stmt := v_sql_stmt || v_date_range;
325
326 if p_delete_flag = 'Y' then
327 EXECUTE IMMEDIATE v_sql_stmt
328 USING nvl(p_from_date, C_FROM_DATE ),
329 nvl(p_to_date, C_TO_DATE );
330 end if;
331
332 /* Bug# 4747555 */
333 IF p_order_type_flag = C_INCLUDE THEN
334 v_order_type_condition := ' AND ORDER_TYPE_ID IN (' || p_order_type_ids || ') ';
335 ELSIF p_order_type_flag = C_EXCLUDE THEN
336 v_order_type_condition := ' AND ORDER_TYPE_ID NOT IN (' || p_order_type_ids || ') ';
337 ELSE
338 v_order_type_condition := NULL;
339 END IF;
340
341 /* DWK Added sr_original_item_pk to insert stmt */
342 /* Bug# 4615390 ISO , added ORDER_SOURCE_ID to the insert stmt */
343 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
344 ' (instance, inv_org, item, customer, sales_channel, '||
345 'sales_rep, ship_to_loc, user_defined1, user_defined2, '||
346 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
347 'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
348 'booked_date, requested_date, promised_date, '||
349 'scheduled_date, scheduled_arrival_date, ' ||
350 'amount, qty_ordered, creation_date, created_by, ' ||
351 'last_update_date, last_updated_by, last_update_login, '||
352 'sr_original_item_pk, sr_parent_item_pk, sr_demand_class_pk, ORDER_SOURCE_ID) '||
353 'SELECT '''||p_instance_id ||
354 ''', inv_org, item, customer, sales_channel, sales_rep, '||
355 'ship_to_loc, user_defined1, user_defined2, '||
356 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
357 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
358 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
359 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
360 'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
361 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
362 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
363 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
364 'booked_date,'||
365 'requested_date, promised_date, scheduled_ship_date, scheduled_arrival_date , '||
366 'amount, qty_ordered, sysdate, '|| FND_GLOBAL.USER_ID ||
367 ', sysdate, '|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||
368 ', to_char(sr_original_item_pk), ' ||
369 ' to_char(sr_parent_item_pk), to_char(sr_demand_class_pk), ORDER_SOURCE_ID ' ||
370 'from ' || p_source_table ||' where 1 = 1';
371
372 v_sql_stmt := v_sql_stmt || v_date_range;
373
374 /* Bug# 4615390 ISO , If p_collect_ISO = SYS_NO, then
375 * include an additional condition to filter out Internal Sales Orders
376 */
377 IF p_collect_ISO = SYS_NO THEN
378 v_sql_stmt := v_sql_stmt || v_exclude_ISO;
379 END IF;
380
381 /* Bug# 4747555 */
382 v_sql_stmt := v_sql_stmt || v_order_type_condition;
383
384 ELSIF p_dest_table = MSD_COMMON_UTILITIES.BOOKING_FACT_TABLE then
385
386 /* Mark delete for overlapping rows and Update its last_refresh_num */
387
388 v_sql_stmt := ' UPDATE msd_booking_data ' ||
389 ' SET last_refresh_num = ' || p_new_refresh_num ||
390 ', Action_code = ' || '''D''' ||
391 ' WHERE Action_code = ''I'' and instance = '||p_instance_id || v_date_range;
392
393 EXECUTE IMMEDIATE v_sql_stmt
394 USING nvl(p_from_date, C_FROM_DATE ),
395 nvl(p_to_date, C_TO_DATE );
396
397 /* Bug# 4615390 ISO , added ORDER_SOURCE_ID to the insert stmt */
398 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
399 ' (instance, inv_org, item, customer, sales_channel, '||
400 'sales_rep, ship_to_loc, user_defined1, user_defined2, '||
401 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
402 'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
403 'booked_date, requested_date, promised_date, '||
404 'scheduled_date, scheduled_arrival_date, ' ||
405 'amount, qty_ordered, creation_date, created_by, ' ||
406 'last_update_date, last_updated_by, last_update_login, '||
407 'last_refresh_num, created_by_refresh_num, action_code, '||
408 'sr_original_item_pk, sr_parent_item_pk, sr_demand_class_pk, ORDER_SOURCE_ID ) '||
409 'SELECT '''||p_instance_id ||
410 ''', inv_org, item, customer, sales_channel, sales_rep, '||
411 'ship_to_loc, user_defined1, user_defined2, '||
412 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
413 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
414 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
415 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
416 'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
417 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
418 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
419 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
420 'booked_date,'||
421 'requested_date, promised_date, scheduled_date, scheduled_arrival_date, '||
422 'amount, qty_ordered, sysdate, '|| FND_GLOBAL.USER_ID ||
423 ', sysdate, '|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||
424 ', '|| p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' ||
425 ', to_char(sr_original_item_pk), to_char(sr_parent_item_pk), to_char(sr_demand_class_pk) ' ||
426 ', ORDER_SOURCE_ID ' ||
427 'from ' || p_source_table ||' where 1 = 1';
428
429 v_sql_stmt := v_sql_stmt || v_date_range;
430
431
432 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
433 rows into fact table*/
434 if (p_source_table = MSD_COMMON_UTILITIES.BOOKING_STAGING_TABLE) then
435 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
436 end if;
437
438 END IF;
439
440 EXECUTE IMMEDIATE v_sql_stmt
441 USING nvl(p_from_date, C_FROM_DATE ),
442 nvl(p_to_date, C_TO_DATE );
443
444 IF p_dest_table = MSD_COMMON_UTILITIES.BOOKING_FACT_TABLE then
445
446 Begin
447
448 select 1 into v_ref_num
449 from msd_cs_data_headers
450 where cs_definition_id in (select cs_definition_id
451 from msd_cs_definitions
452 where name = 'MSD_BOOKING_HISTORY');
453
454 update msd_cs_data_headers
455 set last_refresh_num = p_new_refresh_num
456 where cs_definition_id in (select cs_definition_id
457 from msd_cs_definitions
458 where name in ('MSD_BOOKING_HISTORY','MSD_BOOKING_ORIG_HISTORY'));
459
460 Exception When No_Data_Found Then
461 populate_cs_data_header(p_instance_id,
462 'MSD_BOOKING_HISTORY',
463 p_new_refresh_num);
464 populate_cs_data_header(p_instance_id,
465 'MSD_BOOKING_ORIG_HISTORY',
466 p_new_refresh_num);
467
468 END;
469 END IF;
470
471 retcode := 0;
472
473 EXCEPTION
474 when others then
475 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
476 errbuf := substr(SQLERRM,1,150);
477 retcode := -1 ;
478 raise;
479
480 End translate_booking_data ;
481
482
483
484 procedure translate_uom_conversion(
485 errbuf OUT NOCOPY VARCHAR2,
486 retcode OUT NOCOPY VARCHAR2,
487 p_source_table IN VARCHAR2,
488 p_dest_table IN VARCHAR2,
489 p_instance_id IN NUMBER,
490 p_new_refresh_num IN NUMBER) IS
491
492 v_instance_id varchar2(40);
493 v_dblink varchar2(128);
494 v_retcode number;
495 v_sql_stmt varchar2(4000);
496
497 /* Cursor for delete */
498 /* Changed for bug # 3752937. Only those records should be deleted which are in staging */
499 CURSOR c_delete IS
500 SELECT from_uom_class, to_uom_class,
501 from_uom_code, to_uom_code,
502 base_uom_flag, sr_item_pk
503 FROM msd_uom_conversions
504 WHERE instance = p_instance_id
505 INTERSECT
506 SELECT from_uom_class, to_uom_class,
507 from_uom_code, to_uom_code,
508 base_uom_flag, sr_item_pk
509 FROM msd_st_uom_conversions
510 WHERE instance = p_instance_id;
511
512 /* Cursor for insert */
513 CURSOR c_insert IS
514 SELECT from_uom_class, to_uom_class,
515 from_uom_code, to_uom_code,
516 base_uom_flag, conversion_rate, sr_item_pk, item
517 FROM msd_st_uom_conversions
518 WHERE instance = p_instance_id and
519 nvl(instance,-999) <> 0
520 MINUS
521 SELECT from_uom_class, to_uom_class,
522 from_uom_code, to_uom_code,
523 base_uom_flag, conversion_rate, sr_item_pk, item
524 FROM msd_uom_conversions
525 WHERE instance = p_instance_id and
526 nvl(instance,-999) <> 0;
527
528
529 TYPE from_uom_class_tab IS TABLE OF msd_uom_conversions.from_uom_class%TYPE;
530 TYPE from_uom_code_tab IS TABLE OF msd_uom_conversions.from_uom_code%TYPE;
531 TYPE base_uom_flag_tab IS TABLE OF msd_uom_conversions.base_uom_flag%TYPE;
532 TYPE conversion_rate_tab IS TABLE OF msd_uom_conversions.conversion_rate%TYPE;
533 TYPE sr_item_pk_tab IS TABLE OF msd_uom_conversions.sr_item_pk%TYPE;
534 TYPE item_tab IS TABLE OF msd_uom_conversions.item%TYPE;
535
536 a_from_uom_class from_uom_class_tab;
537 a_to_uom_class from_uom_class_tab;
538 a_from_uom_code from_uom_code_tab;
539 a_to_uom_code from_uom_code_tab;
540 a_base_uom_flag base_uom_flag_tab;
541 a_conversion_rate conversion_rate_tab;
542 a_sr_item_pk sr_item_pk_tab;
543 a_item item_tab;
544
545 Begin
546
547
548 /**************************************************
549 - 1. Get the instance id from MSC_APP_INSTANCE
550 - 2. Get the Profile Value for MSD_DIRECT_LOAD
551 - to identify whether we need to insert the
552 - data into the staging tables or the
553 - fact tables.
554 - 3. Do a complete refresh for this instance,
555 - hence delete all the underlying values.
556 - 4. Insert the Data accordingly into the
557 - Staging or the Fact table based on the
558 - MSD_SR_UOM_CONVERSION_V
559 - 5. Commit
560 ****************************************************/
561
562 v_instance_id := p_instance_id;
563 retcode :=0;
564
565 IF p_dest_table = MSD_COMMON_UTILITIES.UOM_STAGING_TABLE THEN
566 DELETE FROM msd_st_uom_conversions
567 WHERE instance = p_instance_id;
568
569 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
570 ' ( INSTANCE, SR_ITEM_PK, ITEM, FROM_UOM_CLASS, TO_UOM_CLASS, ' ||
571 ' FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG, ' ||
572 ' CONVERSION_RATE, CREATION_DATE, CREATED_BY, ' ||
573 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )'||
574 ' SELECT ' || NVL(v_instance_id, 'INSTANCE') || ', ' ||
575 ' decode(SR_ITEM_PK, ''0'', null, SR_ITEM_PK), ITEM, FROM_UOM_CLASS, TO_UOM_CLASS, ' ||
576 ' FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG, ' ||
577 ' CONVERSION_RATE, sysdate, '|| FND_GLOBAL.USER_ID ||
578 ', sysdate, '|| FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
579 ' from ' || p_source_table || ' where 1 = 1';
580 --dbms_output.put_line(v_sql_stmt);
581 EXECUTE IMMEDIATE v_sql_stmt;
582 ELSIF p_dest_table = MSD_COMMON_UTILITIES.UOM_FACT_TABLE THEN
583 OPEN c_delete;
584 FETCH c_delete BULK COLLECT INTO a_from_uom_class, a_to_uom_class, a_from_uom_code,
585 a_to_uom_code, a_base_uom_flag, a_sr_item_pk;
586 CLOSE c_delete;
587 IF (a_from_uom_class.exists(1)) THEN
588 FORALL i IN a_from_uom_class.FIRST..a_from_uom_class.LAST
589 DELETE FROM msd_uom_conversions
590 WHERE instance = p_instance_id and
591 nvl(sr_item_pk,'NULL') = nvl(a_sr_item_pk(i), 'NULL') and
592 from_uom_class = a_from_uom_class(i) and
593 to_uom_class = a_to_uom_class(i) and
594 from_uom_code = a_from_uom_code(i) and
595 to_uom_code = a_to_uom_code(i) and
596 base_uom_flag = a_base_uom_flag(i);
597 END IF;
598
599 OPEN c_insert;
600 FETCH c_insert BULK COLLECT INTO a_from_uom_class, a_to_uom_class, a_from_uom_code,
601 a_to_uom_code, a_base_uom_flag, a_conversion_rate,
602 a_sr_item_pk, a_item;
603 CLOSE c_insert;
604
605 IF (a_from_uom_class.exists(1)) THEN
606 FORALL i IN a_from_uom_class.FIRST..a_from_uom_class.LAST
607 INSERT INTO msd_uom_conversions(
608 INSTANCE, SR_ITEM_PK, ITEM, FROM_UOM_CLASS, TO_UOM_CLASS,
609 FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG,
610 CONVERSION_RATE, CREATION_DATE, CREATED_BY,
611 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
612 last_refresh_num, created_by_refresh_num, action_code)
613 VALUES ( p_instance_id, a_sr_item_pk(i), a_item(i),
614 a_from_uom_class(i), a_to_uom_class(i),
615 a_from_uom_code(i), a_to_uom_code(i), a_base_uom_flag(i),
616 a_conversion_rate(i), sysdate, FND_GLOBAL.USER_ID,
617 sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
618 p_new_refresh_num, p_new_refresh_num, 'I');
619 END IF;
620 END IF; /* End of ELSIF */
621 retcode := 0;
622
623 exception
624
625 when others then
626
627 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
628 errbuf := substr(SQLERRM,1,150);
629 retcode := -1 ;
630 raise;
631
632
633
634 End translate_uom_conversion ;
635
636 procedure translate_currency_conversion(
637 errbuf OUT NOCOPY VARCHAR2,
638 retcode OUT NOCOPY VARCHAR2,
639 p_source_table IN VARCHAR2,
640 p_dest_table IN VARCHAR2,
641 p_instance_id IN NUMBER,
642 p_from_date IN DATE,
643 p_to_date IN DATE) IS
644 v_instance_id varchar2(40);
645 v_dblink varchar2(128);
646 v_retcode number;
647 v_sql_stmt varchar2(4000);
648 v_date_range varchar2(2000);
649
650 Begin
651
652
653 /**************************************************
654 - 1. Get the instance id from MSC_APP_INSTANCE
655 - 2. Get the Profile Value for MSD_DIRECT_LOAD
656 - to identify whether we need to insert the
657 - data into the staging tables or the
658 - fact tables.
659 - 3. Do a complete refresh for this instance,
660 - hence delete all the underlying values.
661 - 4. Insert the Data accordingly into the
662 - Staging or the Fact table based on the
663 - MSD_SR_CURRENCY_CONVERSION_V
664 - 5. Commit
665 ****************************************************/
666
667 retcode :=0;
668
669 v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
670 ' where 1 = 1';
671
672 /* DWK. If dest_table is staging table, then we shouldn't delete
673 instance = '0' row */
674 IF (p_dest_table = MSD_COMMON_UTILITIES.CURRENCY_STAGING_TABLE) THEN
675 v_sql_stmt := v_sql_stmt || ' and nvl(instance, ''888'') <> '||'''0''';
676 END IF;
677
678 v_date_range := v_date_range ||
679 ' and trunc(conversion_date) between '||
680 ' to_date(to_char(:p_from_date, ''dd-mon-yyyy''), ''DD-MON-RRRR'') ' ||
681 ' AND to_date(to_char(:p_to_date, ''dd-mon-yyyy''),''DD-MON-RRRR'') ' ;
682
683 v_sql_stmt := v_sql_stmt || v_date_range;
684
685 -- dbms_output.put_line(v_sql_stmt);
686 -- insert into msd_test values(v_sql_stmt) ;
687 EXECUTE IMMEDIATE v_sql_stmt
688 USING nvl(p_from_date, C_FROM_DATE ),
689 nvl(p_to_date, C_TO_DATE );
690
691
692 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
693 ' ( FROM_CURRENCY, TO_CURRENCY, ' ||
694 ' CONVERSION_DATE, CONVERSION_RATE, ' ||
695 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, ' ||
696 ' LAST_UPDATED_BY, LAST_UPDATE_LOGIN )'||
697 ' SELECT ' ||
698 ' FROM_CURRENCY, TO_CURRENCY, ' ||
699 ' CONVERSION_DATE, CONVERSION_RATE, ' ||
700 ' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
701 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
702 'from ' || p_source_table || ' where 1 = 1';
703
704 v_sql_stmt := v_sql_stmt || v_date_range;
705
706 /* VM Staging currency conversion table does not have conversion type column. So following
707 where condition should not be included for pull program 07/28/2000
708 this statement is added by mostrovs on 02/24/2000 to take into account
709 the MSD_CONVERSION_TYPE profile */
710
711 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
712 rows into fact table*/
713
714 if p_source_table = MSD_COMMON_UTILITIES.CURRENCY_STAGING_TABLE then
715 v_sql_stmt := v_sql_stmt || ' and nvl(instance, ''888'') <> '||'''0''';
716 else
717 if p_from_date is not null or p_to_date is not null then
718 v_sql_stmt := v_sql_stmt ||
719 ' AND conversion_type = nvl(fnd_profile.value(''MSD_CONVERSION_TYPE''), ''Spot'') ' ;
720 else
721 v_sql_stmt := v_sql_stmt ||
722 ' AND conversion_type = nvl(fnd_profile.value(''MSD_CONVERSION_TYPE''), ''Spot'') ' ;
723 end if;
724 end if;
725
726
727 --insert into msd_test values(v_sql_stmt) ;
728 EXECUTE IMMEDIATE v_sql_stmt
729 USING nvl(p_from_date, C_FROM_DATE ),
730 nvl(p_to_date, C_TO_DATE );
731
732 retcode := 0;
733
734 exception
735
736 when others then
737
738 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
739 errbuf := substr(SQLERRM,1,150);
740 retcode := -1 ;
741 raise;
742
743
744 End translate_currency_conversion ;
745
746
747 procedure translate_opportunities_data(
748 errbuf OUT NOCOPY VARCHAR2,
749 retcode OUT NOCOPY VARCHAR2,
750 p_source_table IN VARCHAR2,
751 p_dest_table IN VARCHAR2,
752 p_instance_id IN NUMBER,
753 p_from_date IN DATE,
754 p_to_date IN DATE) IS
755 v_instance_id varchar2(40);
756 v_dblink varchar2(128);
757 v_retcode number;
758 v_sql_stmt varchar2(4000);
759 v_date_range varchar2(2000);
760
761 Begin
762
763
764 /**************************************************
765 - 1. Get the instance id from MSC_APP_INSTANCE
766 - 2. Get the Profile Value for MSD_DIRECT_LOAD
767 - to identify whether we need to insert the
768 - data into the staging tables or the
769 - fact tables.
770 - 3. Check for the Data Duplication, we should
771 - use the ship_date for this fact.
772 - 4. Insert the Data accordingly into the
773 - Staging or the Fact table based on the
774 - MSD_SR_OPPORTUNITIES_DATA_V
775 - 5. Commit
776 ****************************************************/
777
778 v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
779 ' where instance = ''' || p_instance_id || '''' ;
780 v_date_range := v_date_range ||
781 ' and trunc(ship_date) between '||
782 ' to_date(to_char(:p_from_date, ''dd-mon-yyyy''), ''DD-MON-RRRR'') ' ||
783 ' AND to_date(to_char(:p_to_date,''dd-mon-yyyy''),''DD-MON-RRRR'') ' ;
784
785 v_sql_stmt := v_sql_stmt || v_date_range;
786
787 -- insert into msd_test values(v_sql_stmt) ;
788 -- dbms_output.put_line(v_sql_stmt);
789 EXECUTE IMMEDIATE v_sql_stmt
790 USING nvl(p_from_date, C_FROM_DATE ),
791 nvl(p_to_date, C_TO_DATE );
792
793 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
794 ' (instance, lead_number, interest_type, primary_interest_code, ' ||
795 ' secondary_interest_code, item, inv_org, quantity, amount, '||
796 ' customer, ship_to_loc, sales_channel, sales_rep, '||
797 ' user_defined1, user_defined2, ship_date, win_probability, '||
798 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
799 'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
800 ' status, creation_date, created_by, last_update_date, '||
801 ' last_updated_by, last_update_login ) '||
802 'select '''||p_instance_id ||
803 ''', lead_number, interest_type, primary_interest_code, ' ||
804 ' secondary_interest_code, item, inv_org, quantity, amount, '||
805 ' customer, ship_to_loc, sales_channel, sales_rep, '||
806 ' user_defined1, user_defined2, nvl(ship_date, sysdate), win_probability, '||
807 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
808 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
809 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
810 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
811 'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
812 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
813 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
814 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
815 ' status, sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
816 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
817 'from ' || p_source_table || ' where 1 = 1';
818
819 v_sql_stmt := v_sql_stmt || v_date_range;
820
821 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
822 rows into fact table*/
823
824 if p_source_table = MSD_COMMON_UTILITIES.OPPORTUNITY_STAGING_TABLE then
825 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
826 end if;
827
828 -- insert into msd_test values(v_sql_stmt) ;
829 EXECUTE IMMEDIATE v_sql_stmt
830 USING nvl(p_from_date, C_FROM_DATE ),
831 nvl(p_to_date, C_TO_DATE );
832
833 retcode := 0;
834
835 exception
836
837 when others then
838
839 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
840 errbuf := substr(SQLERRM,1,150);
841 retcode := -1 ;
842 raise;
843
844
845 End translate_opportunities_data ;
846
847
848 /* This is obsoleted procedure */
849 procedure translate_sales_forecast(
850 errbuf OUT NOCOPY VARCHAR2,
851 retcode OUT NOCOPY VARCHAR2,
852 p_source_table IN VARCHAR2,
853 p_dest_table IN VARCHAR2,
854 p_instance_id IN NUMBER,
855 p_fcst_desg IN VARCHAR2,
856 p_from_date IN DATE,
857 p_to_date IN DATE) IS
858 v_instance_id varchar2(40);
859 v_dblink varchar2(128);
860 v_retcode number;
861 v_sql_stmt varchar2(4000);
862 Begin
863
864
865 /**************************************************
866 - 1. Get the instance id from MSC_APP_INSTANCE
867 - 2. Get the Profile Value for MSD_DIRECT_LOAD
868 - to identify whether we need to insert the
869 - data into the staging tables or the
870 - fact tables.
871 - 3. Check for the Data Duplication, we should
872 - use the from_date, to_date and period_name
873 - for this fact.
874 - 4. Insert the Data accordingly into the
875 - Staging or the Fact table based on the
876 - MSD_SR_SALES_FCST_V
877 - 5. Commit
878 ****************************************************/
879
880
881 retcode :=0;
882
883
884
885 v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
886 ' where instance = ''' || p_instance_id ||'''' ;
887
888 if p_from_date is not null and p_to_date is not null then
889 v_sql_stmt := v_sql_stmt ||
890 'and ( ( to_date(period_start_date,''DD-MON-RRRR'') ' ||
891 ' between to_date(''' || to_char(p_from_date, 'dd-mon-yyyy') ||
892 ' '',''DD-MON-RRRR'') AND to_date(''' || to_char(p_to_date, 'dd-mon-yyyy') ||
893 ' '',''DD-MON-RRRR'') ' ||
894 ' ) ' ||
895 ' OR ( to_date(period_end_date,''DD-MON-RRRR'') ' ||
896 ' between to_date(''' || to_char(p_from_date, 'dd-mon-yyyy') ||
897 ' '',''DD-MON-RRRR'') AND to_date(''' || to_char(p_to_date, 'dd-mon-yyyy') ||
898 ' '',''DD-MON-RRRR'') ' ||
899 ' ) ' ||
900 ' ) ' ;
901
902 elsif p_to_date is not null then
903 v_sql_stmt := v_sql_stmt ||
904 'and to_date(period_end_date,''DD-MON-RRRR'') <= to_date(''' ||
905 to_char(p_to_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
906 elsif p_from_date is not null then
907 v_sql_stmt := v_sql_stmt ||
908 'and to_date(period_start_date,''DD-MON-RRRR'') >= to_date(''' ||
909 to_char(p_from_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
910 end if ;
911
912
913 EXECUTE IMMEDIATE v_sql_stmt;
914
915 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
916 ' (instance, forecast_designator, inv_org, interest_type, ' ||
917 ' PRIMARY_INTEREST_CODE, SECONDARY_INTEREST_CODE, item, ' ||
918 ' CUSTOMER, SALES_CHANNEL, SALES_REP, SALES_GROUP, SHIP_TO_LOC, ' ||
919 ' USER_DEFINED1, USER_DEFINED2, PERIOD_NAME, PERIOD_START_DATE, ' ||
920 ' PERIOD_END_DATE, FORECAST_AMOUNT, UPSIDE_AMOUNT, QUOTA_AMOUNT, ' ||
921 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
922 'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
923 ' creation_date, created_by, last_update_date, '||
924 ' last_updated_by, last_update_login ) '||
925 'select '''||p_instance_id ||
926 ''', NULL, inv_org, interest_type, ' ||
927 ' PRIMARY_INTEREST_CODE, SECONDARY_INTEREST_CODE, item, ' ||
928 ' CUSTOMER, SALES_CHANNEL, SALES_REP, SALES_GROUP, SHIP_TO_LOC, ' ||
929 ' USER_DEFINED1, USER_DEFINED2, PERIOD_NAME, nvl(PERIOD_START_DATE, sysdate), ' ||
930 ' nvl(PERIOD_END_DATE, sysdate), FORECAST_AMOUNT, UPSIDE_AMOUNT, QUOTA_AMOUNT, ' ||
931 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
932 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
933 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
934 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
935 'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
936 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
937 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
938 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
939 ' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
940 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
941 ' from ' || p_source_table || ' where 1 = 1';
942
943
944 if p_from_date is not null and p_to_date is not null then
945 v_sql_stmt := v_sql_stmt ||
946 ' and ( ( to_date(nvl(period_start_date, trunc(sysdate)),''DD-MON-RRRR'') ' ||
947 ' between to_date(''' || to_char(p_from_date, 'dd-mon-yyyy') ||
948 ' '',''DD-MON-RRRR'') AND to_date(''' || to_char(p_to_date, 'dd-mon-yyyy') ||
949 ' '',''DD-MON-RRRR'') ' ||
950 ' ) ' ||
951 ' OR ( to_date(nvl(period_end_date, trunc(sysdate)), ''DD-MON-RRRR'') ' ||
952 ' between to_date(''' || to_char(p_from_date, 'dd-mon-yyyy') ||
953 ' '',''DD-MON-RRRR'') AND to_date(''' || to_char(p_to_date, 'dd-mon-yyyy') ||
954 ' '',''DD-MON-RRRR'') ' ||
955 ' ) ' ||
956 ' ) ' ;
957
958 elsif p_to_date is not null then
959 v_sql_stmt := v_sql_stmt ||
960 ' and to_date(nvl(period_end_date, trunc(sysdate)),''DD-MON-RRRR'') <= to_date(''' ||
961 to_char(p_to_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
962 elsif p_from_date is not null then
963 v_sql_stmt := v_sql_stmt ||
964 ' and to_date(nvl(period_start_date, trunc(sysdate)),''DD-MON-RRRR'') >= to_date(''' ||
965 to_char(p_from_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
966 end if ;
967
968 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
969 rows into fact table*/
970
971 IF p_source_table = MSD_COMMON_UTILITIES.SALES_FCST_STAGING_TABLE then
972 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
973 END IF;
974
975
976 -- insert into msd_test values(v_sql_stmt) ;
977 EXECUTE IMMEDIATE v_sql_stmt;
978
979 retcode := 0;
980
981 exception
982
983 when others then
984
985 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
986 errbuf := substr(SQLERRM,1,150);
987 retcode := -1 ;
988 raise;
989
990
991 End translate_sales_forecast ;
992
993
994
995 procedure translate_mfg_forecast(
996 errbuf OUT NOCOPY VARCHAR2,
997 retcode OUT NOCOPY VARCHAR2,
998 p_source_table IN VARCHAR2,
999 p_dest_table IN VARCHAR2,
1000 p_instance_id IN NUMBER,
1001 p_fcst_desg IN VARCHAR2,
1002 p_new_refresh_num IN NUMBER,
1003 p_delete_flag IN VARCHAR2) IS
1004
1005 v_instance_id varchar2(40);
1006 v_retcode number;
1007 v_sql_stmt varchar2(4000);
1008 v_bucket_sql varchar2(200);
1009 l_num1 number;
1010 x_cs_id number;
1011
1012 CURSOR get_cs_id(p_cs_name in VARCHAR2) IS
1013 SELECT cs_definition_id
1014 FROM msd_cs_definitions
1015 WHERE name = p_cs_name;
1016
1017
1018 Begin
1019
1020
1021 /**************************************************
1022 - 1. Get the instance id from MSC_APP_INSTANCE
1023 - 2. Get the Profile Value for MSD_DIRECT_LOAD
1024 - to identify whether we need to insert the
1025 - data into the staging tables or the
1026 - fact tables.
1027 - 3. Check for the Data Duplication, we should
1028 - use the forecast_designator for this fact.
1029 - 4. Insert the Data accordingly into the
1030 - Staging or the Fact table based on the
1031 - MSD_SR_MFG_FCST_V
1032 - 5. Commit
1033 ****************************************************/
1034
1035
1036 retcode :=0;
1037
1038 open get_cs_id('MSD_MANUFACTURING_FORECAST');
1039 fetch get_cs_id into x_cs_id;
1040 close get_cs_id;
1041
1042 /* Check dest_table */
1043 IF p_dest_table = MSD_COMMON_UTILITIES.MFG_FCST_STAGING_TABLE THEN
1044 v_sql_stmt := ' DELETE FROM ' || p_dest_table ||
1045 ' where instance = ''' || p_instance_id || '''' ||
1046 ' and forecast_designator = nvl(:p_fcst_desg, forecast_designator) ' ;
1047
1048
1049 /* OPM Comment By Rajesh Patangya */
1050 if p_delete_flag = 'Y' then
1051 EXECUTE IMMEDIATE v_sql_stmt USING p_fcst_desg ;
1052 end if;
1053
1054
1055 v_bucket_sql := ' decode(BUCKET_TYPE, 1, 9, 2, 1, 3, 2, null) bucket_type, ';
1056
1057 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
1058 ' (instance, forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
1059 ' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
1060 ' BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE, ' ||
1061 ' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
1062 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
1063 'sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
1064 ' creation_date, created_by, last_update_date, '||
1065 ' last_updated_by, last_update_login, sr_demand_class_pk ) '||
1066 'SELECT '''||p_instance_id ||
1067 ''', forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
1068 ' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
1069 v_bucket_sql ||
1070 ' FORECAST_DATE, RATE_END_DATE, ' ||
1071 ' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
1072 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
1073 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
1074 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
1075 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
1076 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
1077 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
1078 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
1079 ' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
1080 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||', ' ||
1081 'to_char(sr_demand_class_pk) ' ||
1082 ' FROM ' || p_source_table ||
1083 ' WHERE forecast_designator = nvl(:p_fcst_desg, forecast_designator) ';
1084
1085 ELSIF p_dest_table = MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE THEN
1086
1087 v_sql_stmt := ' UPDATE msd_mfg_forecast ' ||
1088 ' SET last_refresh_num = ' || p_new_refresh_num ||
1089 ', Action_code = ' || '''D''' ||
1090 ' WHERE Action_code = ''I'' and instance = ' ||
1091 p_instance_id || ' and forecast_designator = ' ||
1092 ' nvl(:p_fcst_desg, forecast_designator) ' ;
1093 EXECUTE IMMEDIATE v_sql_stmt USING p_fcst_desg ;
1094
1095 /* Delete Cs Data Headers */
1096 delete from msd_cs_data_headers
1097 where cs_definition_id = x_cs_id
1098 and instance = p_instance_id
1099 and cs_name = p_fcst_desg;
1100
1101
1102 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
1103 ' ( instance, forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
1104 ' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
1105 ' BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE, ' ||
1106 ' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
1107 ' sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
1108 ' sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
1109 ' last_refresh_num, created_by_refresh_num, action_code, '||
1110 ' creation_date, created_by, last_update_date, '||
1111 ' last_updated_by, last_update_login, sr_demand_class_pk ) '||
1112 'SELECT '''||p_instance_id ||
1113 ''', forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
1114 ' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
1115 ' bucket_type, FORECAST_DATE, RATE_END_DATE, ' ||
1116 ' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
1117 ' nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
1118 ' nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
1119 ' nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
1120 ' nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
1121 ' nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
1122 ' nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
1123 ' nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
1124 p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' ||
1125 ', sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
1126 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||', ' ||
1127 ' to_char(sr_demand_class_pk) ' ||
1128 ' FROM ' || p_source_table ||
1129 ' WHERE forecast_designator = nvl(:p_fcst_desg, forecast_designator) ';
1130
1131 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
1132 rows into fact table*/
1133 IF p_source_table = MSD_COMMON_UTILITIES.MFG_FCST_STAGING_TABLE then
1134 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
1135 END IF;
1136
1137 END IF;
1138
1139 EXECUTE IMMEDIATE v_sql_stmt USING p_fcst_desg ;
1140
1141 if (p_dest_table = MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE) then
1142 INSERT INTO msd_cs_data_headers (
1143 CS_DATA_HEADER_ID,
1144 INSTANCE,
1145 CS_DEFINITION_ID,
1146 CS_NAME,
1147 LAST_UPDATE_DATE,
1148 LAST_UPDATED_BY,
1149 CREATION_DATE,
1150 CREATED_BY,
1151 LAST_UPDATE_LOGIN,
1152 LAST_REFRESH_NUM
1153 )
1154 VALUES ( msd_cs_data_headers_s.nextval,
1155 p_instance_id,
1156 x_cs_id,
1157 p_fcst_desg,
1158 sysdate,
1159 fnd_global.user_id,
1160 sysdate,
1161 fnd_global.user_id,
1162 fnd_global.login_id,
1163 p_new_refresh_num
1164 );
1165 end if;
1166
1167 retcode := 0;
1168
1169 EXCEPTION
1170 when others then
1171 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1172 errbuf := substr(SQLERRM,1,150);
1173 retcode := -1 ;
1174 raise;
1175
1176 End translate_mfg_forecast ;
1177
1178
1179 procedure translate_pricing_data(
1180 errbuf OUT NOCOPY VARCHAR2,
1181 retcode OUT NOCOPY VARCHAR2,
1182 p_source_table IN VARCHAR2,
1183 p_dest_table IN VARCHAR2,
1184 p_instance_id IN NUMBER,
1185 p_price_list IN VARCHAR2,
1186 p_new_refresh_num IN NUMBER) IS
1187
1188 v_instance_id varchar2(40);
1189 v_dblink varchar2(128);
1190 v_retcode number;
1191 v_sql_stmt varchar2(4000);
1192 Begin
1193
1194
1195 /**************************************************
1196 - 1. Get the instance id from MSC_APP_INSTANCE
1197 - 2. Get the Profile Value for MSD_DIRECT_LOAD
1198 - to identify whether we need to insert the
1199 - data into the staging tables or the
1200 - fact tables.
1201 - 3. Check for the Data Duplication, we should
1202 - use the shipped_date for this fact data.
1203 - 4. Insert the Data accordingly into the
1204 - Staging or the Fact table based on the
1205 - MSD_SR_PRICE_LIST_V.
1206 - 5. Commit
1207 ****************************************************/
1208
1209 retcode :=0;
1210
1211 /* Check dest_table */
1212 IF p_dest_table = MSD_COMMON_UTILITIES.PRICING_STAGING_TABLE THEN
1213 v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
1214 ' where instance = ''' || p_instance_id || '''' ||
1215 ' and price_list_name like nvl(:p_price_list, price_list_name) ';
1216 EXECUTE IMMEDIATE v_sql_stmt USING p_price_list;
1217
1218 v_sql_stmt :=
1219 'INSERT INTO ' || p_dest_table ||
1220 ' ( INSTANCE, ' ||
1221 ' ORGANIZATION_LVL_ID, SR_ORGANIZATION_LVL_PK, ' ||
1222 ' PRODUCT_LVL_ID, SR_PRODUCT_LVL_PK, ' ||
1223 ' SALESCHANNEL_LVL_ID, SR_SALESCHANNEL_LVL_PK, ' ||
1224 ' SALES_REP_LVL_ID, SR_SALES_REP_LVL_PK, ' ||
1225 ' GEOGRAPHY_LVL_ID, SR_GEOGRAPHY_LVL_PK, ' ||
1226 ' USER_DEFINED1_LVL_ID,SR_USER_DEFINED1_LVL_PK,' ||
1227 ' USER_DEFINED2_LVL_ID,SR_USER_DEFINED2_LVL_PK,' ||
1228 ' DEMAND_CLASS_LVL_ID, SR_DEMAND_CLASS_LVL_PK,' ||
1229 ' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
1230 ' PRICE, PRIORITY, ' ||
1231 ' PRIMARY_UOM_FLAG, PRICE_LIST_UOM, ' ||
1232 ' CREATION_DATE, CREATED_BY, ' ||
1233 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) ' ||
1234 'SELECT ''' || p_instance_id ||
1235 ''', nvl(ORGANIZATION_LVL_ID, 29) , nvl(SR_ORGANIZATION_LVL_PK,msd_sr_util.get_all_org_pk), ' ||
1236 ' nvl(PRODUCT_LVL_ID, 28) , nvl(SR_PRODUCT_LVL_PK,msd_sr_util.get_all_prd_pk), ' ||
1237 ' nvl(SALESCHANNEL_LVL_ID, 33) , nvl(SR_SALESCHANNEL_LVL_PK,msd_sr_util.get_all_scs_pk), ' ||
1238 ' nvl(SALES_REP_LVL_ID, 32) , nvl(SR_SALES_REP_LVL_PK,msd_sr_util.get_all_rep_pk), ' ||
1239 ' nvl(GEOGRAPHY_LVL_ID, 30) , nvl(SR_GEOGRAPHY_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
1240 ' USER_DEFINED1_LVL_ID,nvl(SR_USER_DEFINED1_LVL_PK,msd_sr_util.get_null_pk),' ||
1241 ' USER_DEFINED2_LVL_ID,nvl(SR_USER_DEFINED2_LVL_PK,msd_sr_util.get_null_pk),' ||
1242 ' nvl(DEMAND_CLASS_LVL_ID, 40), nvl(SR_DEMAND_CLASS_LVL_PK,msd_sr_util.get_null_pk),' ||
1243 ' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
1244 ' PRICE, PRIORITY, ' ||
1245 ' PRIMARY_UOM_FLAG, PRICE_LIST_UOM, ' ||
1246 ' SYSDATE,'|| FND_GLOBAL.USER_ID || ', ' ||
1247 ' SYSDATE,'|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||' '||
1248 'FROM ' || p_source_table || ' ' ||
1249 'WHERE PRICE_LIST_NAME like NVL(:p_price_list, PRICE_LIST_NAME) ';
1250
1251 ELSIF p_dest_table = MSD_COMMON_UTILITIES.PRICING_FACT_TABLE THEN
1252 IF (p_price_list is not NULL) THEN
1253 UPDATE msd_price_list
1254 SET Action_code = 'D', last_refresh_num = p_new_refresh_num
1255 WHERE Action_code = 'I' and instance = p_instance_id and
1256 price_list_name like p_price_list;
1257 ELSE
1258 UPDATE msd_price_list
1259 SET Action_code = 'D', last_refresh_num = p_new_refresh_num
1260 WHERE Action_code = 'I' and instance = p_instance_id;
1261
1262 END IF;
1263
1264 v_sql_stmt :=
1265 'INSERT INTO ' || p_dest_table ||
1266 ' ( INSTANCE, ' ||
1267 ' ORGANIZATION_LVL_ID, SR_ORGANIZATION_LVL_PK, ' ||
1268 ' PRODUCT_LVL_ID, SR_PRODUCT_LVL_PK, ' ||
1269 ' SALESCHANNEL_LVL_ID, SR_SALESCHANNEL_LVL_PK, ' ||
1270 ' SALES_REP_LVL_ID, SR_SALES_REP_LVL_PK, ' ||
1271 ' GEOGRAPHY_LVL_ID, SR_GEOGRAPHY_LVL_PK, ' ||
1272 ' USER_DEFINED1_LVL_ID,SR_USER_DEFINED1_LVL_PK,' ||
1273 ' USER_DEFINED2_LVL_ID,SR_USER_DEFINED2_LVL_PK,' ||
1274 ' DEMAND_CLASS_LVL_ID, SR_DEMAND_CLASS_LVL_PK,' ||
1275 ' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
1276 ' PRICE, PRIORITY, ' ||
1277 ' last_refresh_num, created_by_refresh_num, action_code, '||
1278 ' CREATION_DATE, CREATED_BY, ' ||
1279 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) ' ||
1280 'SELECT ''' || p_instance_id ||
1281 ''', nvl(ORGANIZATION_LVL_ID, 29) , nvl(SR_ORGANIZATION_LVL_PK,msd_sr_util.get_all_org_pk), ' ||
1282 ' nvl(PRODUCT_LVL_ID, 28) , nvl(SR_PRODUCT_LVL_PK,msd_sr_util.get_all_prd_pk), ' ||
1283 ' nvl(SALESCHANNEL_LVL_ID, 33) , nvl(SR_SALESCHANNEL_LVL_PK,msd_sr_util.get_all_scs_pk), ' ||
1284 ' nvl(SALES_REP_LVL_ID, 32) , nvl(SR_SALES_REP_LVL_PK,msd_sr_util.get_all_rep_pk), ' ||
1285 ' nvl(GEOGRAPHY_LVL_ID, 30) , nvl(SR_GEOGRAPHY_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
1286 ' USER_DEFINED1_LVL_ID,nvl(SR_USER_DEFINED1_LVL_PK,msd_sr_util.get_null_pk),' ||
1287 ' USER_DEFINED2_LVL_ID,nvl(SR_USER_DEFINED2_LVL_PK,msd_sr_util.get_null_pk),' ||
1288 ' nvl(DEMAND_CLASS_LVL_ID, 40) , nvl(SR_DEMAND_CLASS_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
1289 ' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
1290 ' PRICE, PRIORITY, ' ||
1291 p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' || ', ' ||
1292 ' SYSDATE,'|| FND_GLOBAL.USER_ID || ', ' ||
1293 ' SYSDATE,'|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||' '||
1294 'FROM ' || p_source_table || ' ' ||
1295 'WHERE PRICE_LIST_NAME like NVL(:p_price_list, PRICE_LIST_NAME) ';
1296 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
1297 rows into fact table*/
1298 IF p_source_table = MSD_COMMON_UTILITIES.PRICING_STAGING_TABLE then
1299 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
1300 END IF;
1301 END IF;
1302
1303 EXECUTE IMMEDIATE v_sql_stmt USING p_price_list;
1304
1305 retcode := 0;
1306
1307 exception
1308
1309 when others then
1310
1311 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1312 errbuf := substr(SQLERRM,1,150);
1313 retcode := -1 ;
1314 raise;
1315
1316 End translate_pricing_data ;
1317
1318
1319
1320 /******************* PROCEDURE *************************************************/
1321 /* The mfg_post_process first populate calendar dates into msd_st_time table with
1322 given instance. This populate_calendar_date procedure only populates working
1323 dates according to the calendar. We split raw entity by inserting all the sub
1324 entities into msd_mfg_forecast talbe and delete the original raw entity from
1325 that table. When we insert sub entities into msd_mfg_forecast table, we join
1326 this table with msd_st_time table and only select days which is in between
1327 forecast_date and rate_end_date of individual raw entity. This INSERT
1328 statement contains 3 SELECT statement according to raw entities bucket_type.
1329 We connect the results with UNION. Only one SELECT statement will be
1330 executed due to different bucket_type condition.
1331 Once we insert all sub entity, we delte all raw entities, and delete
1332 all calendar dates we populated at the beginning.
1333
1334 1) mfg_post_process will UPDATE the following rows
1335 -----------------------------------------------------------
1336 BUCKET_TYPE ACTION
1337 -----------------------------------------------------------
1338 DAY NONE
1339 WEEK YES only if rate_end_date is NULL
1340 MONTH YES only if rate_end_date is NULL
1341
1342 2) mfg_post_process will EXPLODE the following rows
1343 -----------------------------------------------------------
1344 BUCKET_TYPE ACTION
1345 -----------------------------------------------------------
1346 DAY YES only if rate_end_date IS NOT NULL and
1347 rate_end_date <> forecast_date.
1348 WEEK YES only if rate_end_date IS NOT NULL.
1349 MONTH YES only if rate_end_date IS NOT NULL.
1350
1351 3) mfg_post_process will DELETE the following rows
1352 -----------------------------------------------------------
1353 BUCKET_TYPE ACTION
1354 -----------------------------------------------------------
1355 DAY YES rate_end_date IS NOT NULL and
1356 rate_end_date <> forecast_date
1357 WEEK YES rate_end_date IS NOT NULL
1358 MONTH YES rate_end_date IS NOT NULL
1359 */
1360 PROCEDURE mfg_post_process( errbuf OUT NOCOPY VARCHAR2,
1361 retcode OUT NOCOPY VARCHAR2,
1362 p_instance IN VARCHAR2,
1363 p_designator IN VARCHAR2,
1364 p_new_refresh_num IN NUMBER) IS
1365 p_min_date DATE;
1366 p_max_date DATE;
1367 p_org_id Varchar2(40);
1368 p_dblink Varchar2(20);
1369 p_cal_code Varchar2(30);
1370 p_str Varchar2(5000);
1371
1372 l_count NUMBER := 0;
1373
1374 l_day_a NUMBER;
1375 l_day_b NUMBER;
1376 l_week_a NUMBER;
1377 l_week_b NUMBER;
1378 l_month_a NUMBER;
1379 l_month_b NUMBER;
1380
1381
1382 /* DWK Test */
1383 a_FORECAST_DESIGNATOR a_forecast_designator_type;
1384 a_ITEM a_item_type;
1385 a_INV_ORG a_inv_org_type;
1386 a_CUSTOMER a_customer_type;
1387 a_SALES_CHANNEL a_sales_channel_type;
1388 a_SHIP_TO_LOC a_ship_to_loc_type;
1389 a_USER_DEFINED1 a_user_defined1_type;
1390 a_USER_DEFINED2 a_user_defined2_type;
1391 a_BUCKET_TYPE a_bucket_type_type;
1392 a_FORECAST_DATE a_forecast_date_type;
1393 a_RATE_END_DATE a_rate_end_date_type;
1394 a_ORIGINAL_QUANTITY a_original_quantity_type;
1395 a_CURRENT_QUANTITY a_current_quantity_type;
1396 a_sr_inv_org_pk a_sr_inv_org_pk_type;
1397 a_sr_item_pk a_sr_item_pk_type;
1398 a_sr_customer_pk a_sr_customer_pk_type;
1399 a_sr_sales_channel_pk a_sr_sales_channel_pk_type;
1400 a_sr_ship_to_loc_pk a_sr_ship_to_loc_pk_type;
1401 a_sr_user_defined1_pk a_sr_user_defined1_pk_type;
1402 a_sr_user_defined2_pk a_sr_user_defined2_pk_type;
1403 a_sr_demand_class_pk a_sr_user_defined2_pk_type;
1404 a_prd_level_id a_prd_level_id_type;
1405
1406
1407
1408 /* DWK For the performance tunning */
1409 p_temp_start_date DATE;
1410 p_temp_end_date DATE;
1411
1412 CURSOR c_temp_end_date IS
1413 select month_end_date from msd_st_time
1414 where instance = '-999' and day <= p_temp_end_date
1415 order by day desc;
1416
1417
1418 BEGIN
1419 /* Select min and max date for the forecast_date
1420 and rate_end_date. This will reduce the number of
1421 dates populate_calendar_dates will populate */
1422
1423 SELECT min(forecast_date), max( nvl(rate_end_date, forecast_date)) INTO p_min_date, p_max_date
1424 FROM msd_mfg_forecast
1425 WHERE instance = p_instance AND
1426 created_by_refresh_num = p_new_refresh_num AND
1427 forecast_designator = nvl(p_designator,forecast_designator);
1428
1429 /************************** Update forecast_date *********************************/
1430
1431 /* First, Update forecast_date to bucket_end_date only if, it has NULL
1432 for rate_end_date. Since we are not exploding any rows with NULL
1433 value in rate_end_date, we have to update forecast_date separately */
1434
1435 /* First check if there is any row to be updated or not */
1436 select count(*) into l_count
1437 from msd_mfg_forecast
1438 where rate_end_date is null and
1439 bucket_type = '1' and
1440 instance = p_instance and
1441 forecast_designator = nvl(p_designator, forecast_designator) and
1442 created_by_refresh_num = p_new_refresh_num and
1443 rownum < 2;
1444
1445
1446 IF (l_count > 0) THEN
1447 p_str := 'UPDATE msd_mfg_forecast f ' ||
1448 ' SET forecast_date = ' ||
1449 ' nvl((SELECT t.week_end_date FROM msd_st_time t ' ||
1450 ' WHERE f.forecast_date = t.day and t.instance = '||
1451 ''''|| -999 ||'''' ||'), f.forecast_date)
1452 WHERE f.rate_end_date is NULL and
1453 f.bucket_type = ' || '''' || 1 ||''''|| ' and
1454 f.created_by_refresh_num = ' || p_new_refresh_num || ' and
1455 f.instance = ' || ''''||p_instance ||'''' ||
1456 ' and f.forecast_designator = nvl(:p_designator, f.forecast_designator) ';
1457 EXECUTE IMMEDIATE p_str USING p_designator ;
1458
1459 END IF;
1460
1461 /* First check if there is any row to be updated or not */
1462 select count(*) into l_count
1463 from msd_mfg_forecast
1464 where rate_end_date is null and
1465 bucket_type = '2' and
1466 instance = p_instance and
1467 forecast_designator = nvl(p_designator, forecast_designator) and
1468 rownum < 2;
1469
1470 IF (l_count > 0) THEN
1471 /* Monthly Bucket */
1472 p_str := ' UPDATE msd_mfg_forecast f ' ||
1473 ' SET forecast_date = ' ||
1474 ' nvl((SELECT t.month_end_date FROM msd_st_time t
1475 WHERE f.forecast_date = t.day and t.instance = '||
1476 '''' || -999 ||'''' ||'), f.forecast_date)
1477 WHERE f.rate_end_date is NULL and
1478 f.bucket_type = ' || '''' || 2 ||''''|| ' and
1479 f.created_by_refresh_num = ' || p_new_refresh_num || ' and
1480 f.instance = ' || ''''||p_instance ||'''' ||
1481 ' and f.forecast_designator = nvl(:p_designator,f.forecast_designator) ';
1482
1483 EXECUTE IMMEDIATE p_str USING p_designator;
1484 END IF;
1485
1486 /******************************* Insert ****************************************/
1487
1488 /* Find If there is any row needs to be exploded with Daily bucket.
1489 If so, delete those rows from fact table and cache them into arrary */
1490
1491 DELETE FROM msd_mfg_forecast
1492 WHERE instance = p_instance AND
1493 forecast_designator = nvl(p_designator,forecast_designator) AND
1494 bucket_type = C_DAILY_BUCKET AND
1495 created_by_refresh_num = p_new_refresh_num AND
1496 forecast_date <> nvl(rate_end_date, forecast_date)
1497 RETURNING
1498 FORECAST_DESIGNATOR, PRD_LEVEL_ID, ITEM, INV_ORG, CUSTOMER,
1499 SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
1500 BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
1501 ORIGINAL_QUANTITY, CURRENT_QUANTITY,
1502 sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
1503 sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
1504 sr_demand_class_pk
1505 BULK COLLECT INTO
1506 a_FORECAST_DESIGNATOR, a_prd_level_id, a_ITEM, a_INV_ORG, a_CUSTOMER,
1507 a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
1508 a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
1509 a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
1510 a_sr_inv_org_pk, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
1511 a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
1512 a_sr_demand_class_pk;
1513
1514 /* Bulk INSERT cached rows with explosion, For Daily Bucket */
1515 IF (a_sr_item_pk.exists(1)) THEN
1516 FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1517 INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1518 forecast_date, rate_end_date, original_quantity,
1519 current_quantity, creation_date, created_by,
1520 last_update_date, last_updated_by, last_update_login,
1521 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1522 sr_sales_channel_pk, sr_ship_to_loc_pk,
1523 sr_user_defined1_pk, sr_user_defined2_pk,
1524 sr_demand_class_pk,
1525 created_by_refresh_num, last_refresh_num, action_code)
1526 SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
1527 t.day, NULL, a_original_quantity(i),
1528 a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
1529 SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
1530 a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
1531 a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
1532 a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
1533 a_sr_demand_class_pk(i),
1534 p_new_refresh_num, p_new_refresh_num, 'I'
1535 FROM
1536 msd_st_time t
1537 WHERE
1538 t.day between a_forecast_date(i) and a_rate_end_date(i) and
1539 t.instance = '-999';
1540
1541 END IF;
1542
1543
1544
1545 /* Weekly Bucket */
1546 /* Find If there is any row needs to be exploded with Weekly bucket.
1547 If so, delete those rows from fact table and cache them into arrary */
1548 /* Also, rate_end_date can equal to forecast_date
1549 since we already update it */
1550 DELETE FROM msd_mfg_forecast
1551 WHERE instance = p_instance AND
1552 forecast_designator = nvl(p_designator,forecast_designator) AND
1553 bucket_type = C_WEEKLY_BUCKET AND
1554 created_by_refresh_num = p_new_refresh_num AND
1555 rate_end_date IS NOT NULL
1556 RETURNING
1557 FORECAST_DESIGNATOR, ITEM, INV_ORG, CUSTOMER,
1558 SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
1559 BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
1560 ORIGINAL_QUANTITY, CURRENT_QUANTITY,
1561 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
1562 sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
1563 sr_demand_class_pk
1564 BULK COLLECT INTO
1565 a_FORECAST_DESIGNATOR, a_ITEM, a_INV_ORG, a_CUSTOMER,
1566 a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
1567 a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
1568 a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
1569 a_sr_inv_org_pk, a_prd_level_id, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
1570 a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
1571 a_sr_demand_class_pk;
1572
1573 /* Bulk INSERT cached rows with explosion, For Weekly Bucket */
1574 IF (a_sr_item_pk.exists(1)) THEN
1575 FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1576 INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1577 forecast_date, rate_end_date, original_quantity,
1578 current_quantity, creation_date, created_by,
1579 last_update_date, last_updated_by, last_update_login,
1580 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1581 sr_sales_channel_pk, sr_ship_to_loc_pk,
1582 sr_user_defined1_pk, sr_user_defined2_pk,
1583 sr_demand_class_pk,
1584 created_by_refresh_num, last_refresh_num, action_code)
1585 SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
1586 t.week_end_date, NULL, a_original_quantity(i),
1587 a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
1588 SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
1589 a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
1590 a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
1591 a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
1592 a_sr_demand_class_pk(i),
1593 p_new_refresh_num, p_new_refresh_num, 'I'
1594 FROM
1595 (select distinct week_start_date, week_end_date
1596 from msd_st_time
1597 where instance = '-999') t
1598 WHERE
1599 t.week_start_date between a_forecast_date(i) and a_rate_end_date(i) or t.week_end_date between a_forecast_date(i) and a_rate_end_date(i);
1600 END IF;
1601
1602
1603
1604 /* Monthly Bucket */
1605 /* Find If there is any row needs to be exploded with Monthly bucket.
1606 If so, delete those rows from fact table and cache them into arrary */
1607 /* Also, rate_end_date can equal to forecast_date
1608 since we already update it */
1609 DELETE FROM msd_mfg_forecast
1610 WHERE instance = p_instance AND
1611 forecast_designator = nvl(p_designator,forecast_designator) AND
1612 bucket_type = C_MONTHLY_BUCKET AND
1613 created_by_refresh_num = p_new_refresh_num AND
1614 rate_end_date IS NOT NULL
1615 RETURNING
1616 FORECAST_DESIGNATOR, ITEM, INV_ORG, CUSTOMER,
1617 SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
1618 BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
1619 ORIGINAL_QUANTITY, CURRENT_QUANTITY,
1620 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
1621 sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
1622 sr_demand_class_pk
1623 BULK COLLECT INTO
1624 a_FORECAST_DESIGNATOR, a_ITEM, a_INV_ORG, a_CUSTOMER,
1625 a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
1626 a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
1627 a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
1628 a_sr_inv_org_pk, a_prd_level_id, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
1629 a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
1630 a_sr_demand_class_pk;
1631
1632 /* Bulk INSERT cached rows with explosion, For Monthly Bucket */
1633 IF (a_sr_item_pk.exists(1)) THEN
1634 FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1635 INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1636 forecast_date, rate_end_date, original_quantity,
1637 current_quantity, creation_date, created_by,
1638 last_update_date, last_updated_by, last_update_login,
1639 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1640 sr_sales_channel_pk, sr_ship_to_loc_pk,
1641 sr_user_defined1_pk, sr_user_defined2_pk,
1642 sr_demand_class_pk,
1643 created_by_refresh_num, last_refresh_num, action_code)
1644 SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
1645 t.month_end_date, NULL, a_original_quantity(i),
1646 a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
1647 SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
1648 a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
1649 a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
1650 a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
1651 a_sr_demand_class_pk(i),
1652 p_new_refresh_num, p_new_refresh_num, 'I'
1653 FROM
1654 (select distinct month_start_date, month_end_date
1655 from msd_st_time
1656 where instance = '-999') t
1657 WHERE
1658 t.month_start_date between a_forecast_date(i) and a_rate_end_date(i);
1659 END IF;
1660
1661 /* Bug 4729883 - Bring Weekly Manufacturing Forecast as Daily.
1662 For All rows with Bucket Type = 1 (weekly), set Bucket Type to 9 (daily) */
1663 update msd_mfg_forecast
1664 set bucket_type = C_DAILY_BUCKET
1665 WHERE instance = p_instance AND
1666 forecast_designator = nvl(p_designator,forecast_designator) AND
1667 bucket_type = C_WEEKLY_BUCKET AND
1668 created_by_refresh_num = p_new_refresh_num;
1669
1670 exception
1671
1672 WHEN others THEN
1673 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1674 errbuf := substr(SQLERRM,1,150);
1675 retcode := -1 ;
1676 raise;
1677
1678 END MFG_POST_PROCESS;
1679
1680
1681 /************************************ PROCEDURE ****************************************/
1682 /* This procedure will populate all the working dates into msd_st_time table.
1683 From msd_sr_mfg_time_v,
1684 In WHERE clause, the condition, bcd.seq_num IS NOT NULL, guaranteed the we
1685 only insert working date into the table, since holiday and weekends will have
1686 NULL for seq_num.
1687 */
1688 PROCEDURE populate_calendar_dates(p_cal_code VARCHAR2,
1689 p_min_date DATE,
1690 p_max_date DATE,
1691 p_dblink VARCHAR2 ) IS
1692
1693 p_cal_type NUMBER;
1694 p_str VARCHAR2(4000);
1695
1696 BEGIN
1697 p_cal_type := 10;
1698
1699 p_str := 'INSERT INTO msd_st_time(instance, calendar_code,
1700 calendar_type,seq_num, month_start_date,
1701 month_end_date, week_start_date,
1702 week_end_date, day,last_update_date,
1703 last_updated_by, creation_date, created_by,
1704 last_update_login)
1705 SELECT ' || '''-999''' || ', calendar_code, 1,
1706 seq_num, month_start_date,month_end_date, week_start_date, week_end_date, day,
1707 SYSDATE, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID
1708 FROM msd_sr_mfg_time_v' || p_dblink ||
1709 ' WHERE seq_num <> -1 and calendar_code = :p_cal_code '||
1710 ' AND day BETWEEN :p_min_date AND :p_max_date ';
1711
1712 EXECUTE IMMEDIATE p_str USING p_cal_code, p_min_date, p_max_date;
1713
1714 exception
1715 WHEN others THEN
1716 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1717 raise;
1718
1719 END populate_calendar_dates;
1720
1721
1722
1723 /************************************ Procedure ****************************************/
1724 PROCEDURE populate_calendar( errbuf OUT NOCOPY VARCHAR2,
1725 retcode OUT NOCOPY VARCHAR2,
1726 p_instance IN VARCHAR2,
1727 p_new_refresh_num IN NUMBER,
1728 p_table_name IN VARCHAR2) IS
1729
1730 /* DWK To Populate calendar */
1731 l_min_date DATE;
1732 l_max_date DATE;
1733 l_org_id Varchar2(40);
1734 l_dblink Varchar2(20);
1735 l_cal_code Varchar2(30);
1736 l_str Varchar2(5000);
1737
1738 l_where NUMBER;
1739
1740
1741 BEGIN
1742
1743 /* Popoulate Calendar per INSTANCE. This will
1744 reduce the number of populating calendar for the same
1745 instance, but different forecast_designator */
1746
1747 /* Select min and max date for the forecast_date and rate_end_date. */
1748 l_str := 'SELECT min(forecast_date), max( nvl(rate_end_date, forecast_date))'||
1749 ' FROM '||p_table_name|| ' WHERE instance = '||''''||p_instance||'''';
1750
1751 IF ( p_table_name = MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE) THEN
1752 l_str := l_str || ' and created_by_refresh_num = ' || p_new_refresh_num;
1753 END IF;
1754 EXECUTE IMMEDIATE l_str INTO l_min_date, l_max_date;
1755
1756 --fnd_file.put_line(fnd_file.log, l_min_date || ' ' || l_max_date );
1757
1758 -- Get Database Link
1759 msd_common_utilities.get_db_link(p_instance, l_dblink, retcode);
1760
1761 -- Get Master OrganizationID
1762 l_str := 'SELECT parameter_value FROM msd_setup_parameters'||
1763 l_dblink || ' WHERE parameter_name = '|| '''MSD_MASTER_ORG''';
1764 l_where := 1;
1765 EXECUTE IMMEDIATE l_str INTO l_org_id;
1766
1767 -- Get Calendar Code
1768 l_str := 'SELECT calendar_code FROM mtl_parameters' ||
1769 l_dblink || ' WHERE organization_id = :l_org_id ';
1770 l_where := 2;
1771 EXECUTE IMMEDIATE l_str INTO l_cal_code USING l_org_id;
1772
1773 /* DWK Delete existing calendar before populating the current one */
1774 DELETE msd_st_time WHERE instance = '-999';
1775
1776 /* Populate calendar dates. We will delete all these data
1777 after we finish exploiting entities */
1778 populate_calendar_dates(l_cal_code, l_min_date, l_max_date, l_dblink);
1779
1780 EXCEPTION
1781 WHEN no_data_found THEN
1782 IF (l_where = 1 ) THEN
1783 fnd_file.put_line(fnd_file.log, 'Master ORG has not been defined for instance_id : '
1784 || p_instance );
1785 errbuf := 'Master ORG has not been defined for instance_id : '|| p_instance;
1786 ELSIF (l_where = 2) THEN
1787 fnd_file.put_line(fnd_file.log, 'A calendar has not been defined ' ||
1788 'for master org ' || l_org_id ||' for instance_id : ' || p_instance);
1789 errbuf := 'A calendar has not been defined for master org';
1790 END IF;
1791 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1792 errbuf := substr(SQLERRM,1,150);
1793 retcode := -1;
1794 raise;
1795 WHEN OTHERS THEN
1796 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1797 errbuf := substr(SQLERRM,1,150);
1798 retcode := -1;
1799 raise;
1800
1801 END populate_calendar;
1802
1803 /************************************ FUNCTION ****************************************/
1804 FUNCTION Is_Post_Process_Required( errbuf OUT NOCOPY VARCHAR2,
1805 retcode OUT NOCOPY VARCHAR2,
1806 p_instance IN VARCHAR2,
1807 p_designator IN VARCHAR2 ) return BOOLEAN Is
1808
1809
1810 CURSOR c_instance_info IS
1811 SELECT instance_type FROM msc_apps_instances
1812 WHERE to_char(instance_id) = p_instance;
1813
1814 l_instance_type NUMBER(2);
1815 l_is_required BOOLEAN := TRUE;
1816 l_count NUMBER := 0;
1817
1818 BEGIN
1819
1820 OPEN c_instance_info;
1821 FETCH c_instance_info INTO l_instance_type;
1822 /* IF no instance info is found */
1823 IF ( (c_instance_info%NOTFOUND) OR (l_instance_type = NULL) ) THEN
1824 l_is_required := FALSE;
1825 /* Instance Info is found, but type is OTHERS */
1826 ELSIF ( l_instance_type = 3 ) THEN
1827 l_is_required := FALSE;
1828 END IF;
1829 CLOSE c_instance_info;
1830
1831 /* Check whether there is anything to explode or not */
1832 SELECT count(*) INTO l_count FROM msd_mfg_forecast
1833 WHERE instance = p_instance AND
1834 forecast_designator = nvl(p_designator,forecast_designator) AND
1835 (bucket_type <> C_DAILY_BUCKET OR
1836 forecast_date <> nvl(rate_end_date, forecast_date)) and
1837 -- VM created_by_refresh .....
1838 rownum < 2;
1839
1840 IF (l_count = 0) THEN
1841 l_is_required := FALSE;
1842 END IF;
1843
1844 return l_is_required;
1845
1846 EXCEPTION
1847 when others then
1848 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1849 errbuf := substr(SQLERRM,1,150);
1850 retcode := -1;
1851 return NULL;
1852 END;
1853
1854
1855 PROCEDURE CLEAN_FACT_DATA( errbuf OUT NOCOPY VARCHAR2,
1856 retcode OUT NOCOPY VARCHAR2,
1857 p_table_name IN VARCHAR2) IS
1858
1859
1860 l_least_refresh_num NUMBER := 0;
1861 l_sql_stmt varchar2(4000);
1862
1863 BEGIN
1864
1865 /* Find the least refresh number for existing demand plan */
1866 SELECT nvl(min(scn_build_refresh_num), 0) INTO l_least_refresh_num
1867 FROM msd_dp_parameters;
1868
1869
1870 l_sql_stmt := ' DELETE FROM ' || p_table_name ||
1871 ' WHERE ACTION_CODE = ' || '''D''' ||
1872 ' and LAST_REFRESH_NUM <= ' || l_least_refresh_num;
1873
1874 EXECUTE IMMEDIATE l_sql_stmt;
1875
1876
1877 EXCEPTION
1878 when others then
1879 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1880 errbuf := substr(SQLERRM,1,150);
1881 retcode := -1;
1882 raise;
1883
1884 END CLEAN_FACT_DATA;
1885
1886 PROCEDURE populate_cs_data_header(p_instance in number,
1887 p_name IN VARCHAR2,
1888 p_ref_num in number) is
1889
1890 cursor get_cs_id is
1891 select cs_definition_id
1892 from msd_cs_definitions
1893 where name = p_name;
1894
1895 x_cs_id number;
1896
1897 begin
1898
1899 open get_cs_id;
1900 fetch get_cs_id into x_cs_id;
1901 close get_cs_id;
1902
1903 if (x_cs_id is not null) then
1904
1905 insert into msd_cs_data_headers
1906 ( cs_data_header_id,
1907 instance,
1908 cs_definition_id,
1909 cs_name,
1910 last_update_date,
1911 last_updated_by,
1912 creation_date,
1913 created_by,
1914 last_update_login,
1915 last_refresh_num
1916 )
1917 select msd_cs_data_headers_s.nextval,
1918 p_instance,
1919 x_cs_id,
1920 'SINGLE_STREAM',
1921 sysdate,
1922 fnd_global.user_id,
1923 sysdate,
1924 fnd_global.user_id,
1925 fnd_global.login_id,
1926 p_ref_num from dual;
1927
1928 end if;
1929
1930 end populate_cs_data_header;
1931
1932
1933
1934
1935
1936
1937
1938
1939 END MSD_TRANSLATE_FACT_DATA;