APPS.MSD_SPF_COLLECT_HISTORY_DATA dependencies on MSC_ITEM_CATEGORIES

Line 2946: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org ||

2942: x_sql := 'select mi.item_name ' ||
2943: ' from msc_item_substitutes mis, msc_items mi ' ||
2944: ' where mis.plan_id = -1 and mis.sr_instance_id = ' || p_sr_instance_id ||
2945: ' and mis.relationship_type = 8 ' ||
2946: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org ||
2947: ' and mic.sr_instance_id = ' || p_sr_instance_id || ' and mic.inventory_item_id = mis.higher_item_id and mic.category_set_id = ' || x_category_set_id || ' ) ' ||
2948: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org ||
2949: ' and mic.sr_instance_id = ' || p_sr_instance_id || ' and mic.inventory_item_id = mis.lower_item_id and mic.category_set_id = ' || x_category_set_id || ' ) ' ||
2950: ' and mi.inventory_item_id = mis.lower_item_id group by mi.item_name having count(*) > 1';

Line 2948: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org ||

2944: ' where mis.plan_id = -1 and mis.sr_instance_id = ' || p_sr_instance_id ||
2945: ' and mis.relationship_type = 8 ' ||
2946: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org ||
2947: ' and mic.sr_instance_id = ' || p_sr_instance_id || ' and mic.inventory_item_id = mis.higher_item_id and mic.category_set_id = ' || x_category_set_id || ' ) ' ||
2948: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org ||
2949: ' and mic.sr_instance_id = ' || p_sr_instance_id || ' and mic.inventory_item_id = mis.lower_item_id and mic.category_set_id = ' || x_category_set_id || ' ) ' ||
2950: ' and mi.inventory_item_id = mis.lower_item_id group by mi.item_name having count(*) > 1';
2951:
2952: open rctvar for x_sql;

Line 2977: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064

2973: ' from msc_item_substitutes mis '||
2974: ' where sr_instance_id = ' || p_sr_instance_id ||
2975: ' and plan_id = -1 and relationship_type = 8 ' ||
2976: ' and not exists (select 1 from msc_item_substitutes misx where misx.sr_instance_id = mis.sr_instance_id and misx.lower_item_id = mis.higher_item_id and misx.plan_id = -1 and misx.relationship_type = 8)' ||
2977: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064
2978: ' and mic.inventory_item_id = mis.higher_item_id and mic.category_set_id = ' || x_category_set_id || ' ))' ; -- Bug#12675064
2979: execute immediate x_sql;
2980:
2981: msd_dem_common_utilities.log_debug(x_sql);

Line 2995: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064

2991: ' where sc.sequence_id = '|| (x_run_seq - 1) ||
2992: ' and mis.sr_instance_id = ' || p_sr_instance_id ||
2993: ' and mis.plan_id = -1 and mis.relationship_type = 8 ' ||
2994: ' and sc.superseeded_item_id = mis.higher_item_id ' ||
2995: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064
2996: ' and mic.inventory_item_id = mis.higher_item_id and mic.category_set_id = ' || x_category_set_id || ' ))'; -- Bug#12675064
2997: execute immediate x_sql;
2998:
2999: msd_dem_common_utilities.log_debug(x_sql);

Line 3011: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064

3007: ' from msd_spf_item_supersession sc, '||
3008: ' (select chain_id, max(sequence_id) max_seq from MSD_SPF_ITEM_SUPERSESSION group by chain_id) scg' ||
3009: ' where sc.chain_id = scg.chain_id' ||
3010: ' and sc.sequence_id = scg.max_seq' ||
3011: ' and exists (select 1 from msc_item_categories mic where mic.organization_id = ' || x_spf_master_org || ' and mic.sr_instance_id = ' || p_sr_instance_id || -- Bug#12675064
3012: ' and mic.inventory_item_id = sc.superseeded_item_id and mic.category_set_id = ' || x_category_set_id || ' ))'; -- Bug#12675064
3013: execute immediate x_sql;
3014: msd_dem_common_utilities.log_debug(x_sql);
3015:

Line 3609: ' and not exists (select 1 from msc_system_items msi, msc_item_categories mic ' ||

3605: end loop;
3606:
3607: -- remove records for non serviceable spares in msd_spf_mfg_bom_temp -- Bug#12675064
3608: x_sql := 'DELETE from MSD_SPF_MFG_BOM_TEMP tbom where retain_record <> 1 ' ||
3609: ' and not exists (select 1 from msc_system_items msi, msc_item_categories mic ' ||
3610: ' where msi.plan_id = -1 and ' || ' msi.sr_instance_id = '|| p_sr_instance_id ||
3611: ' and msi.organization_id = ' || x_spf_master_org || ' and msi.sr_inventory_item_id = dm_item_sr_pk ' ||
3612: ' and mic.organization_id = msi.organization_id and mic.sr_instance_id = msi.sr_instance_id and msi.inventory_item_id = mic.inventory_item_id ' ||
3613: ' and mic.category_set_id = ' || x_category_set_id || ')';

Line 4243: and not exists (select 1 from msc_system_items msi, msc_item_categories mic -- Bug#12675064

4239: SET process_flag = -1,
4240: error_text = 'Spare not assigned to Spare Cat Set ' || x_category_set_id || ', for - '
4241: || ' DM_ITEM_CODE = ' || dm_item_code || ''
4242: WHERE (process_flag is null or process_flag <> -1)
4243: and not exists (select 1 from msc_system_items msi, msc_item_categories mic -- Bug#12675064
4244: where msi.sr_inventory_item_id = su.dm_item_sr_pk and msi.plan_id = -1 and
4245: msi.sr_instance_id = p_sr_instance_id and msi.organization_id = su.dm_org_sr_pk
4246: and mic.organization_id = var_spf_master_org -- Bug#12675064
4247: and mic.sr_instance_id = msi.sr_instance_id