[Home] [Help]
110: ,l_horizon_end,
111: l_new_refresh_Num
112: );
113:
114: insert into msd_cs_data (
115: cs_data_id,
116: cs_definition_id,
117: cs_name,
118: attribute_1,
145: last_refresh_num,
146: created_by_refresh_num
147: )
148: ( SELECT
149: msd_cs_data_s.nextval,
150: p_cs_definition_id,
151: p_designator,
152: t1.sr_instance_id, -- Bug 5729146
153: 1,
302:
303:
304: BEGIN
305: /*
306: delete from msd_cs_data
307: where cs_name = p_designator
308: and cs_definition_id = p_cs_definition_id
309: and attribute_11 = nvl(to_char(p_org_id), attribute_11)
310: and attribute_1 = nvl(to_char(p_sr_instance_id), attribute_1)
330: p_sr_ship_to_loc_pk :=to_number(NULL);
331: end if;
332:
333:
334: update msd_cs_data
335: set action_code = 'D'
336: where cs_name = p_designator
337: and cs_definition_id = p_cs_definition_id
338: and attribute_11 = nvl(to_char(p_org_id), attribute_11)
344:
345: /* Delete rows that are not used by any demand plans */
346: MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
347: retcode,
348: 'MSD_CS_DATA');
349:
350:
351: /* Bug 2488293. Delets data from headers table. */
352: delete_from_headers(p_cs_definition_id,p_designator);
418:
419: End get_sr_tp_site_id;
420:
421:
422: /* This procedure will delete the data from the msd_cs_data_headers
423: * table.
424: *
425: * Bug 2488293.
426: */
430: p_designator in varchar2) IS
431:
432: BEGIN
433:
434: DELETE from msd_cs_data_headers mcdh
435: where
436: cs_definition_id = p_cs_definition_id
437: and cs_name = p_designator
438: and not exists
436: cs_definition_id = p_cs_definition_id
437: and cs_name = p_designator
438: and not exists
439: (select 1
440: from msd_cs_data mcd
441: where mcd.cs_definition_id = mcdh.cs_definition_id
442: and mcd.cs_name = mcdh.cs_name
443: and mcd.attribute_1 = mcdh.instance
444: and mcd.action_code = 'I'
445: and rownum = 1);
446:
447: Exception
448: When others then
449: fnd_file.put_line(fnd_file.log, 'Error in deleting from MSD_CS_DATA_HEADERS');
450: fnd_file.put_line(fnd_file.log, sqlerrm);
451: raise;
452:
453: End delete_from_headers;
453: End delete_from_headers;
454:
455:
456: /* This procedure will insert cs_definition_id, cs_name, and instance into
457: * msd_cs_data_headers table.
458: * Bug 2488293.
459: */
460: Procedure Insert_Data_Into_Headers
461: (p_cs_definition_id in number,
463: p_refresh_num in number) IS
464:
465: BEGIN
466:
467: insert into msd_cs_data_headers
468: (
469: cs_data_header_id,
470: instance,
471: cs_definition_id,
476: created_by,
477: last_update_login,
478: last_refresh_num
479: )
480: select msd_cs_data_headers_s.nextval,
481: mcd.instance,
482: mcd.cs_definition_id,
483: mcd.cs_name,
484: sysdate,
489: p_refresh_num
490: from
491: (
492: select distinct attribute_1 instance, cs_definition_id, cs_name
493: from msd_cs_data
494: where cs_definition_id = p_cs_definition_id
495: and cs_name = p_designator
496: minus
497: select instance, cs_definition_id, cs_name
494: where cs_definition_id = p_cs_definition_id
495: and cs_name = p_designator
496: minus
497: select instance, cs_definition_id, cs_name
498: from msd_cs_data_headers
499: ) mcd;
500:
501: if (sql%rowcount = 0) then
502:
499: ) mcd;
500:
501: if (sql%rowcount = 0) then
502:
503: update msd_cs_data_headers
504: set last_refresh_num = p_refresh_num,
505: last_update_date = sysdate,
506: last_updated_by = fnd_global.user_id,
507: last_update_login = fnd_global.login_id
511: end if;
512:
513: Exception
514: When others then
515: fnd_file.put_line(fnd_file.log, 'Error in inserting into MSD_CS_DATA_HEADERS');
516: fnd_file.put_line(fnd_file.log, sqlerrm);
517: raise;
518:
519: END Insert_Data_Into_Headers;