[Home] [Help]
PACKAGE BODY: APPS.MSD_TRANSLATE_FACT_DATA
Source
1 PACKAGE BODY MSD_TRANSLATE_FACT_DATA AS
2 /* $Header: msdtfctb.pls 120.10 2012/01/13 09:15:53 rissingh 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
498 /* Cursor for delete */
499 /* Changed for bug # 3752937. Only those records should be deleted which are in staging */
500 /* The Bug# is 4235162 -Code redundant*/
501 /* CURSOR c_delete IS
502 SELECT from_uom_class, to_uom_class,
503 from_uom_code, to_uom_code,
504 base_uom_flag, sr_item_pk
505 FROM msd_uom_conversions
506 WHERE instance = p_instance_id
507 INTERSECT
508 SELECT from_uom_class, to_uom_class,
509 from_uom_code, to_uom_code,
510 base_uom_flag, sr_item_pk
511 FROM msd_st_uom_conversions
512 WHERE instance = p_instance_id;
513 */
514
515
516
517 /* Cursor for insert */
518 CURSOR c_insert IS
519 SELECT from_uom_class, to_uom_class,
520 from_uom_code, to_uom_code,
521 base_uom_flag, conversion_rate, sr_item_pk, item
522 FROM msd_st_uom_conversions
523 WHERE instance = p_instance_id and
524 nvl(instance,-999) <> 0
525 MINUS
526 SELECT from_uom_class, to_uom_class,
527 from_uom_code, to_uom_code,
528 base_uom_flag, conversion_rate, sr_item_pk, item
529 FROM msd_uom_conversions
530 WHERE instance = p_instance_id and
531 nvl(instance,-999) <> 0;
532
533
534 TYPE from_uom_class_tab IS TABLE OF msd_uom_conversions.from_uom_class%TYPE;
535 TYPE from_uom_code_tab IS TABLE OF msd_uom_conversions.from_uom_code%TYPE;
536 TYPE base_uom_flag_tab IS TABLE OF msd_uom_conversions.base_uom_flag%TYPE;
537 TYPE conversion_rate_tab IS TABLE OF msd_uom_conversions.conversion_rate%TYPE;
538 TYPE sr_item_pk_tab IS TABLE OF msd_uom_conversions.sr_item_pk%TYPE;
539 TYPE item_tab IS TABLE OF msd_uom_conversions.item%TYPE;
540
541 a_from_uom_class from_uom_class_tab;
542 a_to_uom_class from_uom_class_tab;
543 a_from_uom_code from_uom_code_tab;
544 a_to_uom_code from_uom_code_tab;
545 a_base_uom_flag base_uom_flag_tab;
546 a_conversion_rate conversion_rate_tab;
547 a_sr_item_pk sr_item_pk_tab;
548 a_item item_tab;
549
550 Begin
551
552
553 /**************************************************
554 - 1. Get the instance id from MSC_APP_INSTANCE
555 - 2. Get the Profile Value for MSD_DIRECT_LOAD
556 - to identify whether we need to insert the
557 - data into the staging tables or the
558 - fact tables.
559 - 3. Do a complete refresh for this instance,
560 - hence delete all the underlying values.
561 - 4. Insert the Data accordingly into the
562 - Staging or the Fact table based on the
563 - MSD_SR_UOM_CONVERSION_V
564 - 5. Commit
565 ****************************************************/
566
567 v_instance_id := p_instance_id;
568 retcode :=0;
569
570 IF p_dest_table = MSD_COMMON_UTILITIES.UOM_STAGING_TABLE THEN
571 DELETE FROM msd_st_uom_conversions
572 WHERE instance = p_instance_id;
573
574 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
575 ' ( INSTANCE, SR_ITEM_PK, ITEM, FROM_UOM_CLASS, TO_UOM_CLASS, ' ||
576 ' FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG, ' ||
577 ' CONVERSION_RATE, CREATION_DATE, CREATED_BY, ' ||
578 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )'||
579 ' SELECT ' || NVL(v_instance_id, 'INSTANCE') || ', ' ||
580 ' decode(SR_ITEM_PK, ''0'', null, SR_ITEM_PK), ITEM, FROM_UOM_CLASS, TO_UOM_CLASS, ' ||
581 ' FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG, ' ||
582 ' CONVERSION_RATE, sysdate, '|| FND_GLOBAL.USER_ID ||
583 ', sysdate, '|| FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
584 ' from ' || p_source_table || ' where 1 = 1';
585 --dbms_output.put_line(v_sql_stmt);
586 EXECUTE IMMEDIATE v_sql_stmt;
587 ELSIF p_dest_table = MSD_COMMON_UTILITIES.UOM_FACT_TABLE THEN
588 /* The Bug# is 4235162 -Code redundant*/
589 /*
590 OPEN c_delete;
591 FETCH c_delete BULK COLLECT INTO a_from_uom_class, a_to_uom_class, a_from_uom_code,
592 a_to_uom_code, a_base_uom_flag, a_sr_item_pk;
593 CLOSE c_delete;
594 IF (a_from_uom_class.exists(1)) THEN
595 FORALL i IN a_from_uom_class.FIRST..a_from_uom_class.LAST
596 DELETE FROM msd_uom_conversions
597 WHERE instance = p_instance_id and
598 nvl(sr_item_pk,'NULL') = nvl(a_sr_item_pk(i), 'NULL') and
599 from_uom_class = a_from_uom_class(i) and
600 to_uom_class = a_to_uom_class(i) and
601 from_uom_code = a_from_uom_code(i) and
602 to_uom_code = a_to_uom_code(i) and
603 base_uom_flag = a_base_uom_flag(i);
604 END IF;
605
606
607 OPEN c_insert;
608 FETCH c_insert BULK COLLECT INTO a_from_uom_class, a_to_uom_class, a_from_uom_code,
609 a_to_uom_code, a_base_uom_flag, a_conversion_rate,
610 a_sr_item_pk, a_item;
611 CLOSE c_insert;
612
613 IF (a_from_uom_class.exists(1)) THEN
614 FORALL i IN a_from_uom_class.FIRST..a_from_uom_class.LAST
615 INSERT INTO msd_uom_conversions(
616 INSTANCE, SR_ITEM_PK, ITEM, FROM_UOM_CLASS, TO_UOM_CLASS,
617 FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG,
618 CONVERSION_RATE, CREATION_DATE, CREATED_BY,
619 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
620 last_refresh_num, created_by_refresh_num, action_code)
621 VALUES ( p_instance_id, a_sr_item_pk(i), a_item(i),
622 a_from_uom_class(i), a_to_uom_class(i),
623 a_from_uom_code(i), a_to_uom_code(i), a_base_uom_flag(i),
624 a_conversion_rate(i), sysdate, FND_GLOBAL.USER_ID,
625 sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
626 p_new_refresh_num, p_new_refresh_num, 'I');
627 END IF;
628
629 */
630
631 v_sql_stmt := ' MERGE INTO msd_uom_conversions muc '||
632 ' USING (SELECT from_uom_class, '||
633 ' to_uom_class, '||
634 ' from_uom_code, '||
635 ' to_uom_code, '||
636 ' base_uom_flag, '||
637 ' conversion_rate, '||
638 ' sr_item_pk, '||
639 ' item '||
640 'FROM msd_st_uom_conversions '||
641 ' ) msuc '||
642 ' ON ( muc.from_uom_class = msuc.from_uom_class and '||
643 ' muc.to_uom_class = msuc.to_uom_class and '||
644 'muc.from_uom_code = msuc.from_uom_code and '||
645 'muc.to_uom_code = msuc.to_uom_code and '||
646 ' muc.base_uom_flag = msuc.base_uom_flag and '||
647 'nvl(muc.sr_item_pk,-1) = nvl(msuc.sr_item_pk,-1) '||
648 ' )'||
649 ' WHEN MATCHED THEN UPDATE SET conversion_rate = msuc.conversion_rate , '||
650 ' last_refresh_num = '|| p_new_refresh_num|| ',' ||
651 ' created_by_refresh_num = '|| p_new_refresh_num ||
652 ' WHEN NOT MATCHED THEN '||
653 'INSERT ( '||
654 ' INSTANCE, '||
655 'SR_ITEM_PK, '||
656 'ITEM, '||
657 'FROM_UOM_CLASS, '||
658 'TO_UOM_CLASS, '||
659 'FROM_UOM_CODE , '||
660 'TO_UOM_CODE, '||
661 'BASE_UOM_FLAG, '||
662 'CONVERSION_RATE, '||
663 'CREATION_DATE, '||
664 'CREATED_BY, '||
665 'LAST_UPDATE_DATE, '||
666 'LAST_UPDATED_BY, '||
667 'LAST_UPDATE_LOGIN, '||
668 'last_refresh_num, '||
669 'created_by_refresh_num, '||
670 'action_code) '||
671 ' VALUES ( '||
672 p_instance_id|| ', '||
673 ' msuc.SR_ITEM_PK, '||
674 ' msuc.ITEM, '||
675 ' msuc.FROM_UOM_CLASS, '||
676 ' msuc.TO_UOM_CLASS, '||
677 ' msuc.FROM_UOM_CODE , '||
678 ' msuc.TO_UOM_CODE, '||
679 ' msuc.BASE_UOM_FLAG, '||
680 ' msuc.CONVERSION_RATE, '||
681 ' sysdate, '||
682 ' FND_GLOBAL.USER_ID, '||
683 ' sysdate, '||
684 ' FND_GLOBAL.USER_ID, '||
685 ' FND_GLOBAL.USER_ID, '||
686 p_new_refresh_num ||','||
687 p_new_refresh_num ||','||
688 '''I'''|| ' )' ;
689
690 EXECUTE IMMEDIATE v_sql_stmt;
691 commit ;
692
693
694 END IF; /* End of ELSIF */
695 retcode := 0;
696
697 exception
698
699 when others then
700
701 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
702 errbuf := substr(SQLERRM,1,150);
703 retcode := -1 ;
704 raise;
705
706
707
708 End translate_uom_conversion ;
709
710 procedure translate_currency_conversion(
711 errbuf OUT NOCOPY VARCHAR2,
712 retcode OUT NOCOPY VARCHAR2,
713 p_source_table IN VARCHAR2,
714 p_dest_table IN VARCHAR2,
715 p_instance_id IN NUMBER,
716 p_from_date IN DATE,
717 p_to_date IN DATE) IS
718 v_instance_id varchar2(40);
719 v_dblink varchar2(128);
720 v_retcode number;
721 v_sql_stmt varchar2(4000);
722 v_date_range varchar2(2000);
723
724 Begin
725
726
727 /**************************************************
728 - 1. Get the instance id from MSC_APP_INSTANCE
729 - 2. Get the Profile Value for MSD_DIRECT_LOAD
730 - to identify whether we need to insert the
731 - data into the staging tables or the
732 - fact tables.
733 - 3. Do a complete refresh for this instance,
734 - hence delete all the underlying values.
735 - 4. Insert the Data accordingly into the
736 - Staging or the Fact table based on the
737 - MSD_SR_CURRENCY_CONVERSION_V
738 - 5. Commit
739 ****************************************************/
740
741 retcode :=0;
742
743 v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
744 ' where 1 = 1';
745
746 /* DWK. If dest_table is staging table, then we shouldn't delete
747 instance = '0' row */
748 IF (p_dest_table = MSD_COMMON_UTILITIES.CURRENCY_STAGING_TABLE) THEN
749 v_sql_stmt := v_sql_stmt || ' and nvl(instance, ''888'') <> '||'''0''';
750 END IF;
751
752 v_date_range := v_date_range ||
753 ' and trunc(conversion_date) between '||
754 ' to_date(to_char(:p_from_date, ''dd-mon-yyyy''), ''DD-MON-RRRR'') ' ||
755 ' AND to_date(to_char(:p_to_date, ''dd-mon-yyyy''),''DD-MON-RRRR'') ' ;
756
757 v_sql_stmt := v_sql_stmt || v_date_range;
758
759 -- dbms_output.put_line(v_sql_stmt);
760 -- insert into msd_test values(v_sql_stmt) ;
761 EXECUTE IMMEDIATE v_sql_stmt
762 USING nvl(p_from_date, C_FROM_DATE ),
763 nvl(p_to_date, C_TO_DATE );
764
765
766 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
767 ' ( FROM_CURRENCY, TO_CURRENCY, ' ||
768 ' CONVERSION_DATE, CONVERSION_RATE, ' ||
769 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, ' ||
770 ' LAST_UPDATED_BY, LAST_UPDATE_LOGIN )'||
771 ' SELECT ' ||
772 ' FROM_CURRENCY, TO_CURRENCY, ' ||
773 ' CONVERSION_DATE, CONVERSION_RATE, ' ||
774 ' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
775 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
776 'from ' || p_source_table || ' where 1 = 1';
777
778 v_sql_stmt := v_sql_stmt || v_date_range;
779
780 /* VM Staging currency conversion table does not have conversion type column. So following
781 where condition should not be included for pull program 07/28/2000
782 this statement is added by mostrovs on 02/24/2000 to take into account
783 the MSD_CONVERSION_TYPE profile */
784
785 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
786 rows into fact table*/
787
788 if p_source_table = MSD_COMMON_UTILITIES.CURRENCY_STAGING_TABLE then
789 v_sql_stmt := v_sql_stmt || ' and nvl(instance, ''888'') <> '||'''0''';
790 else
791 if p_from_date is not null or p_to_date is not null then
792 v_sql_stmt := v_sql_stmt ||
793 ' AND conversion_type = nvl(fnd_profile.value(''MSD_CONVERSION_TYPE''), ''Spot'') ' ;
794 else
795 v_sql_stmt := v_sql_stmt ||
796 ' AND conversion_type = nvl(fnd_profile.value(''MSD_CONVERSION_TYPE''), ''Spot'') ' ;
797 end if;
798 end if;
799
800
801 --insert into msd_test values(v_sql_stmt) ;
802 EXECUTE IMMEDIATE v_sql_stmt
803 USING nvl(p_from_date, C_FROM_DATE ),
804 nvl(p_to_date, C_TO_DATE );
805
806 retcode := 0;
807
808 exception
809
810 when others then
811
812 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
813 errbuf := substr(SQLERRM,1,150);
814 retcode := -1 ;
815 raise;
816
817
818 End translate_currency_conversion ;
819
820
821 procedure translate_opportunities_data(
822 errbuf OUT NOCOPY VARCHAR2,
823 retcode OUT NOCOPY VARCHAR2,
824 p_source_table IN VARCHAR2,
825 p_dest_table IN VARCHAR2,
826 p_instance_id IN NUMBER,
827 p_from_date IN DATE,
828 p_to_date IN DATE) IS
829 v_instance_id varchar2(40);
830 v_dblink varchar2(128);
831 v_retcode number;
832 v_sql_stmt varchar2(4000);
833 v_date_range varchar2(2000);
834
835 Begin
836
837
838 /**************************************************
839 - 1. Get the instance id from MSC_APP_INSTANCE
840 - 2. Get the Profile Value for MSD_DIRECT_LOAD
841 - to identify whether we need to insert the
842 - data into the staging tables or the
843 - fact tables.
844 - 3. Check for the Data Duplication, we should
845 - use the ship_date for this fact.
846 - 4. Insert the Data accordingly into the
847 - Staging or the Fact table based on the
848 - MSD_SR_OPPORTUNITIES_DATA_V
849 - 5. Commit
850 ****************************************************/
851
852 v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
853 ' where instance = ''' || p_instance_id || '''' ;
854 v_date_range := v_date_range ||
855 ' and trunc(ship_date) between '||
856 ' to_date(to_char(:p_from_date, ''dd-mon-yyyy''), ''DD-MON-RRRR'') ' ||
857 ' AND to_date(to_char(:p_to_date,''dd-mon-yyyy''),''DD-MON-RRRR'') ' ;
858
859 v_sql_stmt := v_sql_stmt || v_date_range;
860
861 -- insert into msd_test values(v_sql_stmt) ;
862 -- dbms_output.put_line(v_sql_stmt);
863 EXECUTE IMMEDIATE v_sql_stmt
864 USING nvl(p_from_date, C_FROM_DATE ),
865 nvl(p_to_date, C_TO_DATE );
866
867 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
868 ' (instance, lead_number, interest_type, primary_interest_code, ' ||
869 ' secondary_interest_code, item, inv_org, quantity, amount, '||
870 ' customer, ship_to_loc, sales_channel, sales_rep, '||
871 ' user_defined1, user_defined2, ship_date, win_probability, '||
872 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
873 'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
874 ' status, creation_date, created_by, last_update_date, '||
875 ' last_updated_by, last_update_login ) '||
876 'select '''||p_instance_id ||
877 ''', lead_number, interest_type, primary_interest_code, ' ||
878 ' secondary_interest_code, item, inv_org, quantity, amount, '||
879 ' customer, ship_to_loc, sales_channel, sales_rep, '||
880 ' user_defined1, user_defined2, nvl(ship_date, sysdate), win_probability, '||
881 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
882 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
883 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
884 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
885 'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
886 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
887 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
888 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
889 ' status, sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
890 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
891 'from ' || p_source_table || ' where 1 = 1';
892
893 v_sql_stmt := v_sql_stmt || v_date_range;
894
895 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
896 rows into fact table*/
897
898 if p_source_table = MSD_COMMON_UTILITIES.OPPORTUNITY_STAGING_TABLE then
899 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
900 end if;
901
902 -- insert into msd_test values(v_sql_stmt) ;
903 EXECUTE IMMEDIATE v_sql_stmt
904 USING nvl(p_from_date, C_FROM_DATE ),
905 nvl(p_to_date, C_TO_DATE );
906
907 retcode := 0;
908
909 exception
910
911 when others then
912
913 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
914 errbuf := substr(SQLERRM,1,150);
915 retcode := -1 ;
916 raise;
917
918
919 End translate_opportunities_data ;
920
921
922 /* This is obsoleted procedure */
923 procedure translate_sales_forecast(
924 errbuf OUT NOCOPY VARCHAR2,
925 retcode OUT NOCOPY VARCHAR2,
926 p_source_table IN VARCHAR2,
927 p_dest_table IN VARCHAR2,
928 p_instance_id IN NUMBER,
929 p_fcst_desg IN VARCHAR2,
930 p_from_date IN DATE,
931 p_to_date IN DATE) IS
932 v_instance_id varchar2(40);
933 v_dblink varchar2(128);
934 v_retcode number;
935 v_sql_stmt varchar2(4000);
936 Begin
937
938
939 /**************************************************
940 - 1. Get the instance id from MSC_APP_INSTANCE
941 - 2. Get the Profile Value for MSD_DIRECT_LOAD
942 - to identify whether we need to insert the
943 - data into the staging tables or the
944 - fact tables.
945 - 3. Check for the Data Duplication, we should
946 - use the from_date, to_date and period_name
947 - for this fact.
948 - 4. Insert the Data accordingly into the
949 - Staging or the Fact table based on the
950 - MSD_SR_SALES_FCST_V
951 - 5. Commit
952 ****************************************************/
953
954
955 retcode :=0;
956
957
958
959 v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
960 ' where instance = ''' || p_instance_id ||'''' ;
961
962 if p_from_date is not null and p_to_date is not null then
963 v_sql_stmt := v_sql_stmt ||
964 'and ( ( to_date(period_start_date,''DD-MON-RRRR'') ' ||
965 ' between to_date(''' || to_char(p_from_date, 'dd-mon-yyyy') ||
966 ' '',''DD-MON-RRRR'') AND to_date(''' || to_char(p_to_date, 'dd-mon-yyyy') ||
967 ' '',''DD-MON-RRRR'') ' ||
968 ' ) ' ||
969 ' OR ( to_date(period_end_date,''DD-MON-RRRR'') ' ||
970 ' between to_date(''' || to_char(p_from_date, 'dd-mon-yyyy') ||
971 ' '',''DD-MON-RRRR'') AND to_date(''' || to_char(p_to_date, 'dd-mon-yyyy') ||
972 ' '',''DD-MON-RRRR'') ' ||
973 ' ) ' ||
974 ' ) ' ;
975
976 elsif p_to_date is not null then
977 v_sql_stmt := v_sql_stmt ||
978 'and to_date(period_end_date,''DD-MON-RRRR'') <= to_date(''' ||
979 to_char(p_to_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
980 elsif p_from_date is not null then
981 v_sql_stmt := v_sql_stmt ||
982 'and to_date(period_start_date,''DD-MON-RRRR'') >= to_date(''' ||
983 to_char(p_from_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
984 end if ;
985
986
987 EXECUTE IMMEDIATE v_sql_stmt;
988
989 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
990 ' (instance, forecast_designator, inv_org, interest_type, ' ||
991 ' PRIMARY_INTEREST_CODE, SECONDARY_INTEREST_CODE, item, ' ||
992 ' CUSTOMER, SALES_CHANNEL, SALES_REP, SALES_GROUP, SHIP_TO_LOC, ' ||
993 ' USER_DEFINED1, USER_DEFINED2, PERIOD_NAME, PERIOD_START_DATE, ' ||
994 ' PERIOD_END_DATE, FORECAST_AMOUNT, UPSIDE_AMOUNT, QUOTA_AMOUNT, ' ||
995 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
996 'sr_sales_rep_pk, sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
997 ' creation_date, created_by, last_update_date, '||
998 ' last_updated_by, last_update_login ) '||
999 'select '''||p_instance_id ||
1000 ''', NULL, inv_org, interest_type, ' ||
1001 ' PRIMARY_INTEREST_CODE, SECONDARY_INTEREST_CODE, item, ' ||
1002 ' CUSTOMER, SALES_CHANNEL, SALES_REP, SALES_GROUP, SHIP_TO_LOC, ' ||
1003 ' USER_DEFINED1, USER_DEFINED2, PERIOD_NAME, nvl(PERIOD_START_DATE, sysdate), ' ||
1004 ' nvl(PERIOD_END_DATE, sysdate), FORECAST_AMOUNT, UPSIDE_AMOUNT, QUOTA_AMOUNT, ' ||
1005 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
1006 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
1007 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
1008 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
1009 'nvl(sr_sales_rep_pk, msd_sr_util.get_null_pk), ' ||
1010 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
1011 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
1012 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
1013 ' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
1014 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||' ' ||
1015 ' from ' || p_source_table || ' where 1 = 1';
1016
1017
1018 if p_from_date is not null and p_to_date is not null then
1019 v_sql_stmt := v_sql_stmt ||
1020 ' and ( ( to_date(nvl(period_start_date, trunc(sysdate)),''DD-MON-RRRR'') ' ||
1021 ' between to_date(''' || to_char(p_from_date, 'dd-mon-yyyy') ||
1022 ' '',''DD-MON-RRRR'') AND to_date(''' || to_char(p_to_date, 'dd-mon-yyyy') ||
1023 ' '',''DD-MON-RRRR'') ' ||
1024 ' ) ' ||
1025 ' OR ( to_date(nvl(period_end_date, trunc(sysdate)), ''DD-MON-RRRR'') ' ||
1026 ' between to_date(''' || to_char(p_from_date, 'dd-mon-yyyy') ||
1027 ' '',''DD-MON-RRRR'') AND to_date(''' || to_char(p_to_date, 'dd-mon-yyyy') ||
1028 ' '',''DD-MON-RRRR'') ' ||
1029 ' ) ' ||
1030 ' ) ' ;
1031
1032 elsif p_to_date is not null then
1033 v_sql_stmt := v_sql_stmt ||
1034 ' and to_date(nvl(period_end_date, trunc(sysdate)),''DD-MON-RRRR'') <= to_date(''' ||
1035 to_char(p_to_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
1036 elsif p_from_date is not null then
1037 v_sql_stmt := v_sql_stmt ||
1038 ' and to_date(nvl(period_start_date, trunc(sysdate)),''DD-MON-RRRR'') >= to_date(''' ||
1039 to_char(p_from_date, 'dd-mon-yyyy') || ''',''DD-MON-RRRR'') ' ;
1040 end if ;
1041
1042 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
1043 rows into fact table*/
1044
1045 IF p_source_table = MSD_COMMON_UTILITIES.SALES_FCST_STAGING_TABLE then
1046 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
1047 END IF;
1048
1049
1050 -- insert into msd_test values(v_sql_stmt) ;
1051 EXECUTE IMMEDIATE v_sql_stmt;
1052
1053 retcode := 0;
1054
1055 exception
1056
1057 when others then
1058
1059 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1060 errbuf := substr(SQLERRM,1,150);
1061 retcode := -1 ;
1062 raise;
1063
1064
1065 End translate_sales_forecast ;
1066
1067
1068
1069 procedure translate_mfg_forecast(
1070 errbuf OUT NOCOPY VARCHAR2,
1071 retcode OUT NOCOPY VARCHAR2,
1072 p_source_table IN VARCHAR2,
1073 p_dest_table IN VARCHAR2,
1074 p_instance_id IN NUMBER,
1075 p_fcst_desg IN VARCHAR2,
1076 p_new_refresh_num IN NUMBER,
1077 p_delete_flag IN VARCHAR2) IS
1078
1079 v_instance_id varchar2(40);
1080 v_retcode number;
1081 v_sql_stmt varchar2(4000);
1082 v_bucket_sql varchar2(200);
1083 l_num1 number;
1084 x_cs_id number;
1085
1086 CURSOR get_cs_id(p_cs_name in VARCHAR2) IS
1087 SELECT cs_definition_id
1088 FROM msd_cs_definitions
1089 WHERE name = p_cs_name;
1090
1091
1092 Begin
1093
1094
1095 /**************************************************
1096 - 1. Get the instance id from MSC_APP_INSTANCE
1097 - 2. Get the Profile Value for MSD_DIRECT_LOAD
1098 - to identify whether we need to insert the
1099 - data into the staging tables or the
1100 - fact tables.
1101 - 3. Check for the Data Duplication, we should
1102 - use the forecast_designator for this fact.
1103 - 4. Insert the Data accordingly into the
1104 - Staging or the Fact table based on the
1105 - MSD_SR_MFG_FCST_V
1106 - 5. Commit
1107 ****************************************************/
1108
1109
1110 retcode :=0;
1111
1112 open get_cs_id('MSD_MANUFACTURING_FORECAST');
1113 fetch get_cs_id into x_cs_id;
1114 close get_cs_id;
1115
1116 /* Check dest_table */
1117 IF p_dest_table = MSD_COMMON_UTILITIES.MFG_FCST_STAGING_TABLE THEN
1118 v_sql_stmt := ' DELETE FROM ' || p_dest_table ||
1119 ' where instance = ''' || p_instance_id || '''' ||
1120 ' and forecast_designator = nvl(:p_fcst_desg, forecast_designator) ' ;
1121
1122
1123 /* OPM Comment By Rajesh Patangya */
1124 if p_delete_flag = 'Y' then
1125 EXECUTE IMMEDIATE v_sql_stmt USING p_fcst_desg ;
1126 end if;
1127
1128
1129 v_bucket_sql := ' decode(BUCKET_TYPE, 1, 9, 2, 1, 3, 2, null) bucket_type, ';
1130
1131 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
1132 ' (instance, forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
1133 ' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
1134 ' BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE, ' ||
1135 ' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
1136 'sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
1137 'sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
1138 ' creation_date, created_by, last_update_date, '||
1139 ' last_updated_by, last_update_login, sr_demand_class_pk ) '||
1140 'SELECT '''||p_instance_id ||
1141 ''', forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
1142 ' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
1143 v_bucket_sql ||
1144 ' FORECAST_DATE, RATE_END_DATE, ' ||
1145 ' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
1146 'nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
1147 'nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
1148 'nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
1149 'nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
1150 'nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
1151 'nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
1152 'nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
1153 ' sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
1154 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||', ' ||
1155 'to_char(sr_demand_class_pk) ' ||
1156 ' FROM ' || p_source_table ||
1157 ' WHERE forecast_designator = nvl(:p_fcst_desg, forecast_designator) ';
1158
1159 ELSIF p_dest_table = MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE THEN
1160
1161 v_sql_stmt := ' UPDATE msd_mfg_forecast ' ||
1162 ' SET last_refresh_num = ' || p_new_refresh_num ||
1163 ', Action_code = ' || '''D''' ||
1164 ' WHERE Action_code = ''I'' and instance = ' ||
1165 p_instance_id || ' and forecast_designator = ' ||
1166 ' nvl(:p_fcst_desg, forecast_designator) ' ;
1167 EXECUTE IMMEDIATE v_sql_stmt USING p_fcst_desg ;
1168
1169 /* Delete Cs Data Headers */
1170 delete from msd_cs_data_headers
1171 where cs_definition_id = x_cs_id
1172 and instance = p_instance_id
1173 and cs_name = p_fcst_desg;
1174
1175
1176 v_sql_stmt := 'INSERT INTO ' || p_dest_table ||
1177 ' ( instance, forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
1178 ' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
1179 ' BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE, ' ||
1180 ' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
1181 ' sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk, '||
1182 ' sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk, '||
1183 ' last_refresh_num, created_by_refresh_num, action_code, '||
1184 ' creation_date, created_by, last_update_date, '||
1185 ' last_updated_by, last_update_login, sr_demand_class_pk ) '||
1186 'SELECT '''||p_instance_id ||
1187 ''', forecast_designator, prd_level_id, ITEM, INV_ORG, CUSTOMER, ' ||
1188 ' SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2, ' ||
1189 ' bucket_type, FORECAST_DATE, RATE_END_DATE, ' ||
1190 ' ORIGINAL_QUANTITY, CURRENT_QUANTITY, ' ||
1191 ' nvl(sr_inv_org_pk, msd_sr_util.get_null_pk), ' ||
1192 ' nvl(sr_item_pk, msd_sr_util.get_null_pk), ' ||
1193 ' nvl(sr_customer_pk, msd_sr_util.get_null_pk), ' ||
1194 ' nvl(sr_sales_channel_pk, msd_sr_util.get_null_pk), '||
1195 ' nvl(sr_ship_to_loc_pk, msd_sr_util.get_null_pk), ' ||
1196 ' nvl(sr_user_defined1_pk, msd_sr_util.get_null_pk), ' ||
1197 ' nvl(sr_user_defined2_pk, msd_sr_util.get_null_pk), '||
1198 p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' ||
1199 ', sysdate, '|| FND_GLOBAL.USER_ID || ', sysdate, ' ||
1200 FND_GLOBAL.USER_ID ||', '|| FND_GLOBAL.USER_ID ||', ' ||
1201 ' to_char(sr_demand_class_pk) ' ||
1202 ' FROM ' || p_source_table ||
1203 ' WHERE forecast_designator = nvl(:p_fcst_desg, forecast_designator) ';
1204
1205 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
1206 rows into fact table*/
1207 IF p_source_table = MSD_COMMON_UTILITIES.MFG_FCST_STAGING_TABLE then
1208 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
1209 END IF;
1210
1211 END IF;
1212
1213 EXECUTE IMMEDIATE v_sql_stmt USING p_fcst_desg ;
1214
1215 if (p_dest_table = MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE) then
1216 INSERT INTO msd_cs_data_headers (
1217 CS_DATA_HEADER_ID,
1218 INSTANCE,
1219 CS_DEFINITION_ID,
1220 CS_NAME,
1221 LAST_UPDATE_DATE,
1222 LAST_UPDATED_BY,
1223 CREATION_DATE,
1224 CREATED_BY,
1225 LAST_UPDATE_LOGIN,
1226 LAST_REFRESH_NUM
1227 )
1228 VALUES ( msd_cs_data_headers_s.nextval,
1229 p_instance_id,
1230 x_cs_id,
1231 p_fcst_desg,
1232 sysdate,
1233 fnd_global.user_id,
1234 sysdate,
1235 fnd_global.user_id,
1236 fnd_global.login_id,
1237 p_new_refresh_num
1238 );
1239 end if;
1240
1241 retcode := 0;
1242
1243 EXCEPTION
1244 when others then
1245 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1246 errbuf := substr(SQLERRM,1,150);
1247 retcode := -1 ;
1248 raise;
1249
1250 End translate_mfg_forecast ;
1251
1252
1253 procedure translate_pricing_data(
1254 errbuf OUT NOCOPY VARCHAR2,
1255 retcode OUT NOCOPY VARCHAR2,
1256 p_source_table IN VARCHAR2,
1257 p_dest_table IN VARCHAR2,
1258 p_instance_id IN NUMBER,
1259 p_price_list IN VARCHAR2,
1260 p_new_refresh_num IN NUMBER) IS
1261
1262 v_instance_id varchar2(40);
1263 v_dblink varchar2(128);
1264 v_retcode number;
1265 v_sql_stmt varchar2(4000);
1266 Begin
1267
1268
1269 /**************************************************
1270 - 1. Get the instance id from MSC_APP_INSTANCE
1271 - 2. Get the Profile Value for MSD_DIRECT_LOAD
1272 - to identify whether we need to insert the
1273 - data into the staging tables or the
1274 - fact tables.
1275 - 3. Check for the Data Duplication, we should
1276 - use the shipped_date for this fact data.
1277 - 4. Insert the Data accordingly into the
1278 - Staging or the Fact table based on the
1279 - MSD_SR_PRICE_LIST_V.
1280 - 5. Commit
1281 ****************************************************/
1282
1283 retcode :=0;
1284
1285 /* Check dest_table */
1286 IF p_dest_table = MSD_COMMON_UTILITIES.PRICING_STAGING_TABLE THEN
1287 v_sql_stmt := 'DELETE FROM ' || p_dest_table ||
1288 ' where instance = ''' || p_instance_id || '''' ||
1289 ' and price_list_name like nvl(:p_price_list, price_list_name) ';
1290 EXECUTE IMMEDIATE v_sql_stmt USING p_price_list;
1291
1292 v_sql_stmt :=
1293 'INSERT INTO ' || p_dest_table ||
1294 ' ( INSTANCE, ' ||
1295 ' ORGANIZATION_LVL_ID, SR_ORGANIZATION_LVL_PK, ' ||
1296 ' PRODUCT_LVL_ID, SR_PRODUCT_LVL_PK, ' ||
1297 ' SALESCHANNEL_LVL_ID, SR_SALESCHANNEL_LVL_PK, ' ||
1298 ' SALES_REP_LVL_ID, SR_SALES_REP_LVL_PK, ' ||
1299 ' GEOGRAPHY_LVL_ID, SR_GEOGRAPHY_LVL_PK, ' ||
1300 ' USER_DEFINED1_LVL_ID,SR_USER_DEFINED1_LVL_PK,' ||
1301 ' USER_DEFINED2_LVL_ID,SR_USER_DEFINED2_LVL_PK,' ||
1302 ' DEMAND_CLASS_LVL_ID, SR_DEMAND_CLASS_LVL_PK,' ||
1303 ' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
1304 ' PRICE, PRIORITY, ' ||
1305 ' PRIMARY_UOM_FLAG, PRICE_LIST_UOM, ' ||
1306 ' CREATION_DATE, CREATED_BY, ' ||
1307 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) ' ||
1308 'SELECT ''' || p_instance_id ||
1309 ''', nvl(ORGANIZATION_LVL_ID, 29) , nvl(SR_ORGANIZATION_LVL_PK,msd_sr_util.get_all_org_pk), ' ||
1310 ' nvl(PRODUCT_LVL_ID, 28) , nvl(SR_PRODUCT_LVL_PK,msd_sr_util.get_all_prd_pk), ' ||
1311 ' nvl(SALESCHANNEL_LVL_ID, 33) , nvl(SR_SALESCHANNEL_LVL_PK,msd_sr_util.get_all_scs_pk), ' ||
1312 ' nvl(SALES_REP_LVL_ID, 32) , nvl(SR_SALES_REP_LVL_PK,msd_sr_util.get_all_rep_pk), ' ||
1313 ' nvl(GEOGRAPHY_LVL_ID, 30) , nvl(SR_GEOGRAPHY_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
1314 ' USER_DEFINED1_LVL_ID,nvl(SR_USER_DEFINED1_LVL_PK,msd_sr_util.get_null_pk),' ||
1315 ' USER_DEFINED2_LVL_ID,nvl(SR_USER_DEFINED2_LVL_PK,msd_sr_util.get_null_pk),' ||
1316 ' nvl(DEMAND_CLASS_LVL_ID, 40), nvl(SR_DEMAND_CLASS_LVL_PK,msd_sr_util.get_null_pk),' ||
1317 ' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
1318 ' PRICE, PRIORITY, ' ||
1319 ' PRIMARY_UOM_FLAG, PRICE_LIST_UOM, ' ||
1320 ' SYSDATE,'|| FND_GLOBAL.USER_ID || ', ' ||
1321 ' SYSDATE,'|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||' '||
1322 'FROM ' || p_source_table || ' ' ||
1323 'WHERE PRICE_LIST_NAME like NVL(:p_price_list, PRICE_LIST_NAME) ';
1324
1325 ELSIF p_dest_table = MSD_COMMON_UTILITIES.PRICING_FACT_TABLE THEN
1326 IF (p_price_list is not NULL) THEN
1327 UPDATE msd_price_list
1328 SET Action_code = 'D', last_refresh_num = p_new_refresh_num
1329 WHERE Action_code = 'I' and instance = p_instance_id and
1330 price_list_name like p_price_list;
1331 ELSE
1332 UPDATE msd_price_list
1333 SET Action_code = 'D', last_refresh_num = p_new_refresh_num
1334 WHERE Action_code = 'I' and instance = p_instance_id;
1335
1336 END IF;
1337
1338 v_sql_stmt :=
1339 'INSERT INTO ' || p_dest_table ||
1340 ' ( INSTANCE, ' ||
1341 ' ORGANIZATION_LVL_ID, SR_ORGANIZATION_LVL_PK, ' ||
1342 ' PRODUCT_LVL_ID, SR_PRODUCT_LVL_PK, ' ||
1343 ' SALESCHANNEL_LVL_ID, SR_SALESCHANNEL_LVL_PK, ' ||
1344 ' SALES_REP_LVL_ID, SR_SALES_REP_LVL_PK, ' ||
1345 ' GEOGRAPHY_LVL_ID, SR_GEOGRAPHY_LVL_PK, ' ||
1346 ' USER_DEFINED1_LVL_ID,SR_USER_DEFINED1_LVL_PK,' ||
1347 ' USER_DEFINED2_LVL_ID,SR_USER_DEFINED2_LVL_PK,' ||
1348 ' DEMAND_CLASS_LVL_ID, SR_DEMAND_CLASS_LVL_PK,' ||
1349 ' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
1350 ' PRICE, PRIORITY, ' ||
1351 ' last_refresh_num, created_by_refresh_num, action_code, '||
1352 ' CREATION_DATE, CREATED_BY, ' ||
1353 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) ' ||
1354 'SELECT ''' || p_instance_id ||
1355 ''', nvl(ORGANIZATION_LVL_ID, 29) , nvl(SR_ORGANIZATION_LVL_PK,msd_sr_util.get_all_org_pk), ' ||
1356 ' nvl(PRODUCT_LVL_ID, 28) , nvl(SR_PRODUCT_LVL_PK,msd_sr_util.get_all_prd_pk), ' ||
1357 ' nvl(SALESCHANNEL_LVL_ID, 33) , nvl(SR_SALESCHANNEL_LVL_PK,msd_sr_util.get_all_scs_pk), ' ||
1358 ' nvl(SALES_REP_LVL_ID, 32) , nvl(SR_SALES_REP_LVL_PK,msd_sr_util.get_all_rep_pk), ' ||
1359 ' nvl(GEOGRAPHY_LVL_ID, 30) , nvl(SR_GEOGRAPHY_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
1360 ' USER_DEFINED1_LVL_ID,nvl(SR_USER_DEFINED1_LVL_PK,msd_sr_util.get_null_pk),' ||
1361 ' USER_DEFINED2_LVL_ID,nvl(SR_USER_DEFINED2_LVL_PK,msd_sr_util.get_null_pk),' ||
1362 ' nvl(DEMAND_CLASS_LVL_ID, 40) , nvl(SR_DEMAND_CLASS_LVL_PK,msd_sr_util.get_all_geo_pk), ' ||
1363 ' PRICE_LIST_NAME, START_DATE, END_DATE, ' ||
1364 ' PRICE, PRIORITY, ' ||
1365 p_new_refresh_num || ', ' || p_new_refresh_num || ', '|| '''I''' || ', ' ||
1366 ' SYSDATE,'|| FND_GLOBAL.USER_ID || ', ' ||
1367 ' SYSDATE,'|| FND_GLOBAL.USER_ID || ', '|| FND_GLOBAL.USER_ID ||' '||
1368 'FROM ' || p_source_table || ' ' ||
1369 'WHERE PRICE_LIST_NAME like NVL(:p_price_list, PRICE_LIST_NAME) ';
1370 /* DWK. if the source is from staging table, then we shouldn't insert instance = '0'
1371 rows into fact table*/
1372 IF p_source_table = MSD_COMMON_UTILITIES.PRICING_STAGING_TABLE then
1373 v_sql_stmt := v_sql_stmt || ' and nvl(instance,''888'') <> '||'''0''';
1374 END IF;
1375 END IF;
1376
1377 EXECUTE IMMEDIATE v_sql_stmt USING p_price_list;
1378
1379 retcode := 0;
1380
1381 exception
1382
1383 when others then
1384
1385 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1386 errbuf := substr(SQLERRM,1,150);
1387 retcode := -1 ;
1388 raise;
1389
1390 End translate_pricing_data ;
1391
1392
1393
1394 /******************* PROCEDURE *************************************************/
1395 /* The mfg_post_process first populate calendar dates into msd_st_time table with
1396 given instance. This populate_calendar_date procedure only populates working
1397 dates according to the calendar. We split raw entity by inserting all the sub
1398 entities into msd_mfg_forecast talbe and delete the original raw entity from
1399 that table. When we insert sub entities into msd_mfg_forecast table, we join
1400 this table with msd_st_time table and only select days which is in between
1401 forecast_date and rate_end_date of individual raw entity. This INSERT
1402 statement contains 3 SELECT statement according to raw entities bucket_type.
1403 We connect the results with UNION. Only one SELECT statement will be
1404 executed due to different bucket_type condition.
1405 Once we insert all sub entity, we delte all raw entities, and delete
1406 all calendar dates we populated at the beginning.
1407
1408 1) mfg_post_process will UPDATE the following rows
1409 -----------------------------------------------------------
1410 BUCKET_TYPE ACTION
1411 -----------------------------------------------------------
1412 DAY NONE
1413 WEEK YES only if rate_end_date is NULL
1414 MONTH YES only if rate_end_date is NULL
1415
1416 2) mfg_post_process will EXPLODE the following rows
1417 -----------------------------------------------------------
1418 BUCKET_TYPE ACTION
1419 -----------------------------------------------------------
1420 DAY YES only if rate_end_date IS NOT NULL and
1421 rate_end_date <> forecast_date.
1422 WEEK YES only if rate_end_date IS NOT NULL.
1423 MONTH YES only if rate_end_date IS NOT NULL.
1424
1425 3) mfg_post_process will DELETE the following rows
1426 -----------------------------------------------------------
1427 BUCKET_TYPE ACTION
1428 -----------------------------------------------------------
1429 DAY YES rate_end_date IS NOT NULL and
1430 rate_end_date <> forecast_date
1431 WEEK YES rate_end_date IS NOT NULL
1432 MONTH YES rate_end_date IS NOT NULL
1433 */
1434 PROCEDURE mfg_post_process( errbuf OUT NOCOPY VARCHAR2,
1435 retcode OUT NOCOPY VARCHAR2,
1436 p_instance IN VARCHAR2,
1437 p_designator IN VARCHAR2,
1438 p_new_refresh_num IN NUMBER) IS
1439 p_min_date DATE;
1440 p_max_date DATE;
1441 p_org_id Varchar2(40);
1442 p_dblink Varchar2(20);
1443 p_cal_code Varchar2(30);
1444 p_str Varchar2(5000);
1445
1446 l_count NUMBER := 0;
1447
1448 l_day_a NUMBER;
1449 l_day_b NUMBER;
1450 l_week_a NUMBER;
1451 l_week_b NUMBER;
1452 l_month_a NUMBER;
1453 l_month_b NUMBER;
1454
1455
1456 /* DWK Test */
1457 a_FORECAST_DESIGNATOR a_forecast_designator_type;
1458 a_ITEM a_item_type;
1459 a_INV_ORG a_inv_org_type;
1460 a_CUSTOMER a_customer_type;
1461 a_SALES_CHANNEL a_sales_channel_type;
1462 a_SHIP_TO_LOC a_ship_to_loc_type;
1463 a_USER_DEFINED1 a_user_defined1_type;
1464 a_USER_DEFINED2 a_user_defined2_type;
1465 a_BUCKET_TYPE a_bucket_type_type;
1466 a_FORECAST_DATE a_forecast_date_type;
1467 a_RATE_END_DATE a_rate_end_date_type;
1468 a_ORIGINAL_QUANTITY a_original_quantity_type;
1469 a_CURRENT_QUANTITY a_current_quantity_type;
1470 a_sr_inv_org_pk a_sr_inv_org_pk_type;
1471 a_sr_item_pk a_sr_item_pk_type;
1472 a_sr_customer_pk a_sr_customer_pk_type;
1473 a_sr_sales_channel_pk a_sr_sales_channel_pk_type;
1474 a_sr_ship_to_loc_pk a_sr_ship_to_loc_pk_type;
1475 a_sr_user_defined1_pk a_sr_user_defined1_pk_type;
1476 a_sr_user_defined2_pk a_sr_user_defined2_pk_type;
1477 a_sr_demand_class_pk a_sr_user_defined2_pk_type;
1478 a_prd_level_id a_prd_level_id_type;
1479
1480
1481
1482 /* DWK For the performance tunning */
1483 p_temp_start_date DATE;
1484 p_temp_end_date DATE;
1485
1486 CURSOR c_temp_end_date IS
1487 select month_end_date from msd_st_time
1488 where instance = '-999' and day <= p_temp_end_date
1489 order by day desc;
1490
1491
1492 BEGIN
1493 /* Select min and max date for the forecast_date
1494 and rate_end_date. This will reduce the number of
1495 dates populate_calendar_dates will populate */
1496
1497 SELECT min(forecast_date), max( nvl(rate_end_date, forecast_date)) INTO p_min_date, p_max_date
1498 FROM msd_mfg_forecast
1499 WHERE instance = p_instance AND
1500 created_by_refresh_num = p_new_refresh_num AND
1501 forecast_designator = nvl(p_designator,forecast_designator);
1502
1503 /************************** Update forecast_date *********************************/
1504
1505 /* First, Update forecast_date to bucket_end_date only if, it has NULL
1506 for rate_end_date. Since we are not exploding any rows with NULL
1507 value in rate_end_date, we have to update forecast_date separately */
1508
1509 /* First check if there is any row to be updated or not */
1510 select count(*) into l_count
1511 from msd_mfg_forecast
1512 where rate_end_date is null and
1513 bucket_type = '1' and
1514 instance = p_instance and
1515 forecast_designator = nvl(p_designator, forecast_designator) and
1516 created_by_refresh_num = p_new_refresh_num and
1517 rownum < 2;
1518
1519
1520 IF (l_count > 0) THEN
1521 p_str := 'UPDATE msd_mfg_forecast f ' ||
1522 ' SET forecast_date = ' ||
1523 ' nvl((SELECT t.week_end_date FROM msd_st_time t ' ||
1524 ' WHERE f.forecast_date = t.day and t.instance = '||
1525 ''''|| -999 ||'''' ||'), f.forecast_date)
1526 WHERE f.rate_end_date is NULL and
1527 f.bucket_type = ' || '''' || 1 ||''''|| ' and
1528 f.created_by_refresh_num = ' || p_new_refresh_num || ' and
1529 f.instance = ' || ''''||p_instance ||'''' ||
1530 ' and f.forecast_designator = nvl(:p_designator, f.forecast_designator) ';
1531 EXECUTE IMMEDIATE p_str USING p_designator ;
1532
1533 END IF;
1534
1535 /* First check if there is any row to be updated or not */
1536 select count(*) into l_count
1537 from msd_mfg_forecast
1538 where rate_end_date is null and
1539 bucket_type = '2' and
1540 instance = p_instance and
1541 forecast_designator = nvl(p_designator, forecast_designator) and
1542 rownum < 2;
1543
1544 IF (l_count > 0) THEN
1545 /* Monthly Bucket */
1546 p_str := ' UPDATE msd_mfg_forecast f ' ||
1547 ' SET forecast_date = ' ||
1548 ' nvl((SELECT t.month_end_date FROM msd_st_time t
1549 WHERE f.forecast_date = t.day and t.instance = '||
1550 '''' || -999 ||'''' ||'), f.forecast_date)
1551 WHERE f.rate_end_date is NULL and
1552 f.bucket_type = ' || '''' || 2 ||''''|| ' and
1553 f.created_by_refresh_num = ' || p_new_refresh_num || ' and
1554 f.instance = ' || ''''||p_instance ||'''' ||
1555 ' and f.forecast_designator = nvl(:p_designator,f.forecast_designator) ';
1556
1557 EXECUTE IMMEDIATE p_str USING p_designator;
1558 END IF;
1559
1560 /******************************* Insert ****************************************/
1561
1562 /* Find If there is any row needs to be exploded with Daily bucket.
1563 If so, delete those rows from fact table and cache them into arrary */
1564
1565 DELETE FROM msd_mfg_forecast
1566 WHERE instance = p_instance AND
1567 forecast_designator = nvl(p_designator,forecast_designator) AND
1568 bucket_type = C_DAILY_BUCKET AND
1569 created_by_refresh_num = p_new_refresh_num AND
1570 forecast_date <> nvl(rate_end_date, forecast_date)
1571 RETURNING
1572 FORECAST_DESIGNATOR, PRD_LEVEL_ID, ITEM, INV_ORG, CUSTOMER,
1573 SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
1574 BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
1575 ORIGINAL_QUANTITY, CURRENT_QUANTITY,
1576 sr_inv_org_pk, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
1577 sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
1578 sr_demand_class_pk
1579 BULK COLLECT INTO
1580 a_FORECAST_DESIGNATOR, a_prd_level_id, a_ITEM, a_INV_ORG, a_CUSTOMER,
1581 a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
1582 a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
1583 a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
1584 a_sr_inv_org_pk, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
1585 a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
1586 a_sr_demand_class_pk;
1587
1588 /* Bulk INSERT cached rows with explosion, For Daily Bucket */
1589 IF (a_sr_item_pk.exists(1)) THEN
1590 FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1591 INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1592 forecast_date, rate_end_date, original_quantity,
1593 current_quantity, creation_date, created_by,
1594 last_update_date, last_updated_by, last_update_login,
1595 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1596 sr_sales_channel_pk, sr_ship_to_loc_pk,
1597 sr_user_defined1_pk, sr_user_defined2_pk,
1598 sr_demand_class_pk,
1599 created_by_refresh_num, last_refresh_num, action_code)
1600 SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
1601 t.day, NULL, a_original_quantity(i),
1602 a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
1603 SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
1604 a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
1605 a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
1606 a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
1607 a_sr_demand_class_pk(i),
1608 p_new_refresh_num, p_new_refresh_num, 'I'
1609 FROM
1610 msd_st_time t
1611 WHERE
1612 t.day between a_forecast_date(i) and a_rate_end_date(i) and
1613 t.instance = '-999';
1614
1615 END IF;
1616
1617
1618
1619 /* Weekly Bucket */
1620 /* Find If there is any row needs to be exploded with Weekly bucket.
1621 If so, delete those rows from fact table and cache them into arrary */
1622 /* Also, rate_end_date can equal to forecast_date
1623 since we already update it */
1624 DELETE FROM msd_mfg_forecast
1625 WHERE instance = p_instance AND
1626 forecast_designator = nvl(p_designator,forecast_designator) AND
1627 bucket_type = C_WEEKLY_BUCKET AND
1628 created_by_refresh_num = p_new_refresh_num AND
1629 rate_end_date IS NOT NULL
1630 RETURNING
1631 FORECAST_DESIGNATOR, ITEM, INV_ORG, CUSTOMER,
1632 SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
1633 BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
1634 ORIGINAL_QUANTITY, CURRENT_QUANTITY,
1635 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
1636 sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
1637 sr_demand_class_pk
1638 BULK COLLECT INTO
1639 a_FORECAST_DESIGNATOR, a_ITEM, a_INV_ORG, a_CUSTOMER,
1640 a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
1641 a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
1642 a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
1643 a_sr_inv_org_pk, a_prd_level_id, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
1644 a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
1645 a_sr_demand_class_pk;
1646
1647 /* Bulk INSERT cached rows with explosion, For Weekly Bucket */
1648 IF (a_sr_item_pk.exists(1)) THEN
1649 FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1650 INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1651 forecast_date, rate_end_date, original_quantity,
1652 current_quantity, creation_date, created_by,
1653 last_update_date, last_updated_by, last_update_login,
1654 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1655 sr_sales_channel_pk, sr_ship_to_loc_pk,
1656 sr_user_defined1_pk, sr_user_defined2_pk,
1657 sr_demand_class_pk,
1658 created_by_refresh_num, last_refresh_num, action_code)
1659 SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
1660 t.week_end_date, NULL, a_original_quantity(i),
1661 a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
1662 SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
1663 a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
1664 a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
1665 a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
1666 a_sr_demand_class_pk(i),
1667 p_new_refresh_num, p_new_refresh_num, 'I'
1668 FROM
1669 (select distinct week_start_date, week_end_date
1670 from msd_st_time
1671 where instance = '-999') t
1672 WHERE
1673 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);
1674 END IF;
1675
1676
1677
1678 /* Monthly Bucket */
1679 /* Find If there is any row needs to be exploded with Monthly bucket.
1680 If so, delete those rows from fact table and cache them into arrary */
1681 /* Also, rate_end_date can equal to forecast_date
1682 since we already update it */
1683 DELETE FROM msd_mfg_forecast
1684 WHERE instance = p_instance AND
1685 forecast_designator = nvl(p_designator,forecast_designator) AND
1686 bucket_type = C_MONTHLY_BUCKET AND
1687 created_by_refresh_num = p_new_refresh_num AND
1688 rate_end_date IS NOT NULL
1689 RETURNING
1690 FORECAST_DESIGNATOR, ITEM, INV_ORG, CUSTOMER,
1691 SALES_CHANNEL, SHIP_TO_LOC, USER_DEFINED1, USER_DEFINED2,
1692 BUCKET_TYPE, FORECAST_DATE, RATE_END_DATE,
1693 ORIGINAL_QUANTITY, CURRENT_QUANTITY,
1694 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk, sr_sales_channel_pk,
1695 sr_ship_to_loc_pk, sr_user_defined1_pk, sr_user_defined2_pk,
1696 sr_demand_class_pk
1697 BULK COLLECT INTO
1698 a_FORECAST_DESIGNATOR, a_ITEM, a_INV_ORG, a_CUSTOMER,
1699 a_SALES_CHANNEL, a_SHIP_TO_LOC, a_USER_DEFINED1, a_USER_DEFINED2,
1700 a_BUCKET_TYPE, a_FORECAST_DATE, a_RATE_END_DATE,
1701 a_ORIGINAL_QUANTITY, a_CURRENT_QUANTITY,
1702 a_sr_inv_org_pk, a_prd_level_id, a_sr_item_pk, a_sr_customer_pk, a_sr_sales_channel_pk,
1703 a_sr_ship_to_loc_pk, a_sr_user_defined1_pk, a_sr_user_defined2_pk,
1704 a_sr_demand_class_pk;
1705
1706 /* Bulk INSERT cached rows with explosion, For Monthly Bucket */
1707 IF (a_sr_item_pk.exists(1)) THEN
1708 FORALL i IN a_sr_item_pk.FIRST..a_sr_item_pk.LAST
1709 INSERT INTO msd_mfg_forecast(instance, forecast_designator, bucket_type,
1710 forecast_date, rate_end_date, original_quantity,
1711 current_quantity, creation_date, created_by,
1712 last_update_date, last_updated_by, last_update_login,
1713 sr_inv_org_pk, prd_level_id, sr_item_pk, sr_customer_pk,
1714 sr_sales_channel_pk, sr_ship_to_loc_pk,
1715 sr_user_defined1_pk, sr_user_defined2_pk,
1716 sr_demand_class_pk,
1717 created_by_refresh_num, last_refresh_num, action_code)
1718 SELECT p_instance, a_forecast_designator(i), a_bucket_type(i),
1719 t.month_end_date, NULL, a_original_quantity(i),
1720 a_current_quantity(i), SYSDATE, FND_GLOBAL.USER_ID,
1721 SYSDATE, FND_GLOBAL.USER_ID, fnd_global.login_id,
1722 a_sr_inv_org_pk(i), a_prd_level_id(i), a_sr_item_pk(i), a_sr_customer_pk(i),
1723 a_sr_sales_channel_pk(i), a_sr_ship_to_loc_pk(i),
1724 a_sr_user_defined1_pk(i), a_sr_user_defined2_pk(i),
1725 a_sr_demand_class_pk(i),
1726 p_new_refresh_num, p_new_refresh_num, 'I'
1727 FROM
1728 (select distinct month_start_date, month_end_date
1729 from msd_st_time
1730 where instance = '-999') t
1731 WHERE
1732 t.month_start_date between a_forecast_date(i) and a_rate_end_date(i);
1733 END IF;
1734
1735 /* Bug 4729883 - Bring Weekly Manufacturing Forecast as Daily.
1736 For All rows with Bucket Type = 1 (weekly), set Bucket Type to 9 (daily) */
1737 update msd_mfg_forecast
1738 set bucket_type = C_DAILY_BUCKET
1739 WHERE instance = p_instance AND
1740 forecast_designator = nvl(p_designator,forecast_designator) AND
1741 bucket_type = C_WEEKLY_BUCKET AND
1742 created_by_refresh_num = p_new_refresh_num;
1743
1744 exception
1745
1746 WHEN others THEN
1747 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1748 errbuf := substr(SQLERRM,1,150);
1749 retcode := -1 ;
1750 raise;
1751
1752 END MFG_POST_PROCESS;
1753
1754
1755 /************************************ PROCEDURE ****************************************/
1756 /* This procedure will populate all the working dates into msd_st_time table.
1757 From msd_sr_mfg_time_v,
1758 In WHERE clause, the condition, bcd.seq_num IS NOT NULL, guaranteed the we
1759 only insert working date into the table, since holiday and weekends will have
1760 NULL for seq_num.
1761 */
1762 PROCEDURE populate_calendar_dates(p_cal_code VARCHAR2,
1763 p_min_date DATE,
1764 p_max_date DATE,
1765 p_dblink VARCHAR2 ) IS
1766
1767 p_cal_type NUMBER;
1768 p_str VARCHAR2(4000);
1769
1770 BEGIN
1771 p_cal_type := 10;
1772
1773 p_str := 'INSERT INTO msd_st_time(instance, calendar_code,
1774 calendar_type,seq_num, month_start_date,
1775 month_end_date, week_start_date,
1776 week_end_date, day,last_update_date,
1777 last_updated_by, creation_date, created_by,
1778 last_update_login)
1779 SELECT ' || '''-999''' || ', calendar_code, 1,
1780 seq_num, month_start_date,month_end_date, week_start_date, week_end_date, day,
1781 SYSDATE, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID
1782 FROM msd_sr_mfg_time_v' || p_dblink ||
1783 ' WHERE seq_num <> -1 and calendar_code = :p_cal_code '||
1784 ' AND day BETWEEN :p_min_date AND :p_max_date ';
1785
1786 EXECUTE IMMEDIATE p_str USING p_cal_code, p_min_date, p_max_date;
1787
1788 exception
1789 WHEN others THEN
1790 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1791 raise;
1792
1793 END populate_calendar_dates;
1794
1795
1796
1797 /************************************ Procedure ****************************************/
1798 PROCEDURE populate_calendar( errbuf OUT NOCOPY VARCHAR2,
1799 retcode OUT NOCOPY VARCHAR2,
1800 p_instance IN VARCHAR2,
1801 p_new_refresh_num IN NUMBER,
1802 p_table_name IN VARCHAR2) IS
1803
1804 /* DWK To Populate calendar */
1805 l_min_date DATE;
1806 l_max_date DATE;
1807 l_org_id Varchar2(40);
1808 l_dblink Varchar2(20);
1809 l_cal_code Varchar2(30);
1810 l_str Varchar2(5000);
1811
1812 l_where NUMBER;
1813
1814
1815 BEGIN
1816
1817 /* Popoulate Calendar per INSTANCE. This will
1818 reduce the number of populating calendar for the same
1819 instance, but different forecast_designator */
1820
1821 /* Select min and max date for the forecast_date and rate_end_date. */
1822 l_str := 'SELECT min(forecast_date), max( nvl(rate_end_date, forecast_date))'||
1823 ' FROM '||p_table_name|| ' WHERE instance = '||''''||p_instance||'''';
1824
1825 IF ( p_table_name = MSD_COMMON_UTILITIES.MFG_FCST_FACT_TABLE) THEN
1826 l_str := l_str || ' and created_by_refresh_num = ' || p_new_refresh_num;
1827 END IF;
1828 EXECUTE IMMEDIATE l_str INTO l_min_date, l_max_date;
1829
1830 --fnd_file.put_line(fnd_file.log, l_min_date || ' ' || l_max_date );
1831
1832 -- Get Database Link
1833 msd_common_utilities.get_db_link(p_instance, l_dblink, retcode);
1834
1835 -- Get Master OrganizationID
1836 l_str := 'SELECT parameter_value FROM msd_setup_parameters'||
1837 l_dblink || ' WHERE parameter_name = '|| '''MSD_MASTER_ORG''';
1838 l_where := 1;
1839 EXECUTE IMMEDIATE l_str INTO l_org_id;
1840
1841 -- Get Calendar Code
1842 l_str := 'SELECT calendar_code FROM mtl_parameters' ||
1843 l_dblink || ' WHERE organization_id = :l_org_id ';
1844 l_where := 2;
1845 EXECUTE IMMEDIATE l_str INTO l_cal_code USING l_org_id;
1846
1847 /* DWK Delete existing calendar before populating the current one */
1848 DELETE msd_st_time WHERE instance = '-999';
1849
1850 /* Populate calendar dates. We will delete all these data
1851 after we finish exploiting entities */
1852 populate_calendar_dates(l_cal_code, l_min_date, l_max_date, l_dblink);
1853
1854 EXCEPTION
1855 WHEN no_data_found THEN
1856 IF (l_where = 1 ) THEN
1857 fnd_file.put_line(fnd_file.log, 'Master ORG has not been defined for instance_id : '
1858 || p_instance );
1859 errbuf := 'Master ORG has not been defined for instance_id : '|| p_instance;
1860 ELSIF (l_where = 2) THEN
1861 fnd_file.put_line(fnd_file.log, 'A calendar has not been defined ' ||
1862 'for master org ' || l_org_id ||' for instance_id : ' || p_instance);
1863 errbuf := 'A calendar has not been defined for master org';
1864 END IF;
1865 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1866 errbuf := substr(SQLERRM,1,150);
1867 retcode := -1;
1868 raise;
1869 WHEN OTHERS THEN
1870 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1871 errbuf := substr(SQLERRM,1,150);
1872 retcode := -1;
1873 raise;
1874
1875 END populate_calendar;
1876
1877 /************************************ FUNCTION ****************************************/
1878 FUNCTION Is_Post_Process_Required( errbuf OUT NOCOPY VARCHAR2,
1879 retcode OUT NOCOPY VARCHAR2,
1880 p_instance IN VARCHAR2,
1881 p_designator IN VARCHAR2 ) return BOOLEAN Is
1882
1883
1884 CURSOR c_instance_info IS
1885 SELECT instance_type FROM msc_apps_instances
1886 WHERE to_char(instance_id) = p_instance;
1887
1888 l_instance_type NUMBER(2);
1889 l_is_required BOOLEAN := TRUE;
1890 l_count NUMBER := 0;
1891
1892 BEGIN
1893
1894 OPEN c_instance_info;
1895 FETCH c_instance_info INTO l_instance_type;
1896 /* IF no instance info is found */
1897 IF ( (c_instance_info%NOTFOUND) OR (l_instance_type = NULL) ) THEN
1898 l_is_required := FALSE;
1899 /* Instance Info is found, but type is OTHERS */
1900 ELSIF ( l_instance_type = 3 ) THEN
1901 l_is_required := FALSE;
1902 END IF;
1903 CLOSE c_instance_info;
1904
1905 /* Check whether there is anything to explode or not */
1906 SELECT count(*) INTO l_count FROM msd_mfg_forecast
1907 WHERE instance = p_instance AND
1908 forecast_designator = nvl(p_designator,forecast_designator) AND
1909 (bucket_type <> C_DAILY_BUCKET OR
1910 forecast_date <> nvl(rate_end_date, forecast_date)) and
1911 -- VM created_by_refresh .....
1912 rownum < 2;
1913
1914 IF (l_count = 0) THEN
1915 l_is_required := FALSE;
1916 END IF;
1917
1918 return l_is_required;
1919
1920 EXCEPTION
1921 when others then
1922 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1923 errbuf := substr(SQLERRM,1,150);
1924 retcode := -1;
1925 return NULL;
1926 END;
1927
1928
1929 PROCEDURE CLEAN_FACT_DATA( errbuf OUT NOCOPY VARCHAR2,
1930 retcode OUT NOCOPY VARCHAR2,
1931 p_table_name IN VARCHAR2,
1932 p_cs_definition_id IN NUMBER DEFAULT NULL) IS
1933
1934 CURSOR msd_cs_data_drec IS
1935 SELECT count(*) cnt,
1936 created_by_refresh_num cn,
1937 last_refresh_num LN
1938 FROM msd_cs_data
1939 WHERE cs_definition_id = p_cs_definition_id
1940 AND action_code = 'D'
1941 GROUP BY created_by_refresh_num,
1942 last_refresh_num;
1943
1944 CURSOR scn_build_ref_num IS
1945 SELECT dp.scn_build_refresh_num bldn
1946 FROM msd_dp_parameters dp,
1947 msd_cs_definitions mcd
1948 WHERE mcd.cs_definition_id = p_cs_definition_id
1949 AND mcd.name = dp.parameter_type
1950 AND dp.scn_build_refresh_num IS NOT NULL;
1951
1952 l_flag NUMBER;
1953 l_least_refresh_num NUMBER := 0;
1954 l_sql_stmt varchar2(4000);
1955
1956 BEGIN
1957
1958 IF (p_table_name = 'MSD_CS_DATA') THEN
1959
1960 FOR drec IN msd_cs_data_drec
1961 LOOP
1962 l_flag := 0;
1963 FOR ref_num IN scn_build_ref_num
1964 LOOP
1965 IF(drec.cn <= ref_num.bldn
1966 AND drec.LN > ref_num.bldn) THEN
1967 l_flag := 1;
1968 END IF;
1969 END LOOP;
1970 IF(l_flag = 0) THEN
1971 l_sql_stmt := ' DELETE FROM ' || p_table_name ||' WHERE ACTION_CODE = ' || '''D''' ||' and LAST_REFRESH_NUM = ' || drec.LN ||
1972 ' and created_by_refresh_num = ' || drec.cn || ' and cs_definition_id = ' || p_cs_definition_id;
1973 execute immediate l_sql_stmt;
1974 END IF;
1975 END LOOP;
1976
1977 ELSE
1978 SELECT nvl(min(scn_build_refresh_num), 0) INTO l_least_refresh_num
1979 FROM msd_dp_parameters;
1980 l_sql_stmt := ' DELETE FROM ' || p_table_name ||
1981 ' WHERE ACTION_CODE = ' || '''D''' ||
1982 ' and LAST_REFRESH_NUM <= ' || l_least_refresh_num;
1983 EXECUTE IMMEDIATE l_sql_stmt;
1984 END IF;
1985
1986
1987 EXCEPTION
1988 when others then
1989 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1990 errbuf := substr(SQLERRM,1,150);
1991 retcode := -1;
1992 raise;
1993
1994 END CLEAN_FACT_DATA;
1995
1996 PROCEDURE populate_cs_data_header(p_instance in number,
1997 p_name IN VARCHAR2,
1998 p_ref_num in number) is
1999
2000 cursor get_cs_id is
2001 select cs_definition_id
2002 from msd_cs_definitions
2003 where name = p_name;
2004
2005 x_cs_id number;
2006
2007 begin
2008
2009 open get_cs_id;
2010 fetch get_cs_id into x_cs_id;
2011 close get_cs_id;
2012
2013 if (x_cs_id is not null) then
2014
2015 insert into msd_cs_data_headers
2016 ( cs_data_header_id,
2017 instance,
2018 cs_definition_id,
2019 cs_name,
2020 last_update_date,
2021 last_updated_by,
2022 creation_date,
2023 created_by,
2024 last_update_login,
2025 last_refresh_num
2026 )
2027 select msd_cs_data_headers_s.nextval,
2028 p_instance,
2029 x_cs_id,
2030 'SINGLE_STREAM',
2031 sysdate,
2032 fnd_global.user_id,
2033 sysdate,
2034 fnd_global.user_id,
2035 fnd_global.login_id,
2036 p_ref_num from dual;
2037
2038 end if;
2039
2040 end populate_cs_data_header;
2041
2042
2043
2044
2045
2046
2047
2048
2049 END MSD_TRANSLATE_FACT_DATA;