[Home] [Help]
146: ,l_horizon_end,
147: l_new_refresh_Num
148: );
149:
150: insert into msd_cs_data (
151: cs_data_id,
152: cs_definition_id,
153: cs_name,
154: attribute_1,
181: last_refresh_num,
182: created_by_refresh_num
183: )
184: ( SELECT
185: msd_cs_data_s.nextval,
186: p_cs_definition_id,
187: p_designator,
188: t1.sr_instance_id, -- Bug 5729146
189: 1,
339:
340:
341: BEGIN
342: /*
343: delete from msd_cs_data
344: where cs_name = p_designator
345: and cs_definition_id = p_cs_definition_id
346: and attribute_11 = nvl(to_char(p_org_id), attribute_11)
347: and attribute_1 = nvl(to_char(p_sr_instance_id), attribute_1)
367: p_sr_ship_to_loc_pk :=to_number(NULL);
368: end if;
369:
370:
371: update msd_cs_data
372: set action_code = 'D'
373: where cs_name = p_designator
374: and cs_definition_id = p_cs_definition_id
375: and attribute_11 = nvl(to_char(p_org_id), attribute_11)
381:
382: /* Delete rows that are not used by any demand plans */
383: MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
384: retcode,
385: 'MSD_CS_DATA');
386:
387:
388: /* Bug 2488293. Delets data from headers table. */
389: delete_from_headers(p_cs_definition_id,p_designator);
455:
456: End get_sr_tp_site_id;
457:
458:
459: /* This procedure will delete the data from the msd_cs_data_headers
460: * table.
461: *
462: * Bug 2488293.
463: */
467: p_designator in varchar2) IS
468:
469: BEGIN
470:
471: DELETE from msd_cs_data_headers mcdh
472: where
473: cs_definition_id = p_cs_definition_id
474: and cs_name = p_designator
475: and not exists
473: cs_definition_id = p_cs_definition_id
474: and cs_name = p_designator
475: and not exists
476: (select 1
477: from msd_cs_data mcd
478: where mcd.cs_definition_id = mcdh.cs_definition_id
479: and mcd.cs_name = mcdh.cs_name
480: and mcd.attribute_1 = mcdh.instance
481: and mcd.action_code = 'I'
482: and rownum = 1);
483:
484: Exception
485: When others then
486: fnd_file.put_line(fnd_file.log, 'Error in deleting from MSD_CS_DATA_HEADERS');
487: fnd_file.put_line(fnd_file.log, sqlerrm);
488: raise;
489:
490: End delete_from_headers;
490: End delete_from_headers;
491:
492:
493: /* This procedure will insert cs_definition_id, cs_name, and instance into
494: * msd_cs_data_headers table.
495: * Bug 2488293.
496: */
497: Procedure Insert_Data_Into_Headers
498: (p_cs_definition_id in number,
500: p_refresh_num in number) IS
501:
502: BEGIN
503:
504: insert into msd_cs_data_headers
505: (
506: cs_data_header_id,
507: instance,
508: cs_definition_id,
513: created_by,
514: last_update_login,
515: last_refresh_num
516: )
517: select msd_cs_data_headers_s.nextval,
518: mcd.instance,
519: mcd.cs_definition_id,
520: mcd.cs_name,
521: sysdate,
526: p_refresh_num
527: from
528: (
529: select distinct attribute_1 instance, cs_definition_id, cs_name
530: from msd_cs_data
531: where cs_definition_id = p_cs_definition_id
532: and cs_name = p_designator
533: minus
534: select instance, cs_definition_id, cs_name
531: where cs_definition_id = p_cs_definition_id
532: and cs_name = p_designator
533: minus
534: select instance, cs_definition_id, cs_name
535: from msd_cs_data_headers
536: ) mcd;
537:
538: if (sql%rowcount = 0) then
539:
536: ) mcd;
537:
538: if (sql%rowcount = 0) then
539:
540: update msd_cs_data_headers
541: set last_refresh_num = p_refresh_num,
542: last_update_date = sysdate,
543: last_updated_by = fnd_global.user_id,
544: last_update_login = fnd_global.login_id
548: end if;
549:
550: Exception
551: When others then
552: fnd_file.put_line(fnd_file.log, 'Error in inserting into MSD_CS_DATA_HEADERS');
553: fnd_file.put_line(fnd_file.log, sqlerrm);
554: raise;
555:
556: END Insert_Data_Into_Headers;