APPS.MSD_ITEM_RELATIONSHIPS_PKG dependencies on MSD_ITEM_RELATIONSHIPS

Line 1: PACKAGE BODY MSD_ITEM_RELATIONSHIPS_PKG AS

1: PACKAGE BODY MSD_ITEM_RELATIONSHIPS_PKG AS
2: /* $Header: msdsupsb.pls 120.2.12010000.1 2008/05/15 08:50:04 vrepaka ship $ */
3:
4: /* This procedure to collect Supersession data from source instance to DP staging table. */
5:

Line 111: delete from msd_item_relationships

107: EXIT WHEN item_instance_c2%NOTFOUND;
108:
109: /* Delete records in fact table by instance before pull supersession data from staging table */
110:
111: delete from msd_item_relationships
112: where instance_id = l_instance_id;
113:
114: /* Insert Supersession data into DP fact table */
115:

Line 116: insert into msd_item_relationships (

112: where instance_id = l_instance_id;
113:
114: /* Insert Supersession data into DP fact table */
115:
116: insert into msd_item_relationships (
117: instance_id,
118: inventory_item_id,
119: inventory_item,
120: related_item_id,

Line 159: msd_item_relationships_pkg.create_supersession_events (

155: EXIT WHEN auto_update_events_c1%NOTFOUND;
156:
157: fnd_file.put_line(fnd_file.log, 'Auto Refreshing Event: '|| l_event_name || ' Instance Id: '|| l_instance_id);
158:
159: msd_item_relationships_pkg.create_supersession_events (
160: errbuf => errbuf,
161: retcode => retcode,
162: p_instance_id => l_instance_id,
163: p_event_name => l_event_name );

Line 425: from msd_item_relationships;

421: l_instance_id number;
422:
423: CURSOR item_instance_c1 IS
424: select distinct instance_id
425: from msd_item_relationships;
426:
427: BEGIN
428:
429: OPEN item_instance_c1;

Line 436: msd_item_relationships_pkg.create_supersession_events (

432:
433: FETCH item_instance_c1 INTO l_instance_id;
434: EXIT WHEN item_instance_c1%NOTFOUND;
435:
436: msd_item_relationships_pkg.create_supersession_events (
437: errbuf => errbuf,
438: retcode => retcode,
439: p_instance_id => l_instance_id,
440: p_event_name => p_event_name );

Line 484: FROM msd_item_relationships rel,

480: /*
481: CURSOR new_item_c1 IS
482: SELECT lvl.sr_level_pk,
483: lvl.level_value
484: FROM msd_item_relationships rel,
485: msd_level_values lvl
486: WHERE lvl.instance = rel.instance_id
487: AND lvl.sr_level_pk = rel.inventory_item_id
488: AND lvl.level_id = 1

Line 490: FROM msd_item_relationships re2

486: WHERE lvl.instance = rel.instance_id
487: AND lvl.sr_level_pk = rel.inventory_item_id
488: AND lvl.level_id = 1
489: AND rel.inventory_item_id not in (SELECT re2.related_item_id
490: FROM msd_item_relationships re2
491: WHERE re2.instance_id = to_char(p_instance_id))
492: AND rel.instance_id = to_char(p_instance_id);
493: */
494:

Line 498: FROM msd_item_relationships rel,

494:
495: CURSOR new_item_c1 IS
496: SELECT lvl.sr_level_pk,
497: lvl.level_value, rel.start_date, rel.end_date /*--Bug#4707819--*/
498: FROM msd_item_relationships rel,
499: msd_level_values lvl
500: WHERE lvl.instance = rel.instance_id
501: AND lvl.sr_level_pk = rel.inventory_item_id
502: AND lvl.level_id = 1

Line 507: FROM msd_item_relationships re2

503: AND rel.instance_id = to_char(p_instance_id)
504: MINUS
505: SELECT re2.related_item_id,
506: re2.related_item, re2.start_date, re2.end_date /*--Bug#4707819--*/
507: FROM msd_item_relationships re2
508: WHERE re2.instance_id = to_char(p_instance_id);
509:
510:
511: CURSOR superseded_item_c1(l_inventory_item_id in number) IS

Line 514: FROM msd_item_relationships

510:
511: CURSOR superseded_item_c1(l_inventory_item_id in number) IS
512: SELECT related_item_id,
513: related_item
514: FROM msd_item_relationships
515: START WITH inventory_item_id = l_inventory_item_id
516: AND instance_id = p_instance_id
517: CONNECT BY PRIOR related_item_id = inventory_item_id
518: AND instance_id = p_instance_id;

Line 536: msd_item_relationships_pkg.delete_events_data (

532: where event_name = p_event_name;
533:
534: /* Delete refresh events before updating with supersession item relationships */
535:
536: msd_item_relationships_pkg.delete_events_data (
537: errbuf => errbuf,
538: retcode => retcode,
539: p_instance_id => p_instance_id,
540: p_event_id => l_event_id );

Line 561: msd_item_relationships_pkg.insert_event_products(

557: FROM dual;
558:
559: /* Insert new items into MSD_EVENT_PRODUCTS table */
560:
561: msd_item_relationships_pkg.insert_event_products(
562: errbuf => errbuf,
563: retcode => retcode,
564: p_instance_id => p_instance_id,
565: l_event_id => l_event_id,

Line 612: msd_item_relationships_pkg.insert_evt_prod_relationships(

608: l_qty_mod_factor := 0;
609: l_npi_prd_relshp := 1;
610:
611: /* Insert base items into MSD_EVT_PROD_RELATIONSHIPS table */
612: msd_item_relationships_pkg.insert_evt_prod_relationships(
613: errbuf => errbuf,
614: retcode => retcode,
615: p_instance_id => p_instance_id,
616: l_event_id => l_event_id,

Line 643: msd_item_relationships_pkg.insert_evt_prod_relationships(

639: l_qty_mod_factor := 100;
640: l_npi_prd_relshp := 2;
641:
642: /* Insert cannabilized items into MSD_EVT_PROD_RELATIONSHIPS table */
643: msd_item_relationships_pkg.insert_evt_prod_relationships(
644: errbuf => errbuf,
645: retcode => retcode,
646: p_instance_id => p_instance_id,
647: l_event_id => l_event_id,

Line 660: msd_item_relationships_pkg.insert_evt_product_details(

656: l_start_time => l_start_time, /*--Bug#4707819--*/
657: l_end_time =>l_end_time ); /*--Bug#4707819--*/
658:
659: /* Insert cannabilized items details into MSD_EVT_PRODUCT_DETAILS table */
660: msd_item_relationships_pkg.insert_evt_product_details(
661: errbuf => errbuf,
662: retcode => retcode,
663: p_instance_id => p_instance_id,
664: l_event_id => l_event_id,

Line 709: END MSD_ITEM_RELATIONSHIPS_PKG;

705: rollback;
706:
707: END create_supersession_events;
708:
709: END MSD_ITEM_RELATIONSHIPS_PKG;