[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.oracle_username
INTO l_msc_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 724;
select oracle_username
into l_apps_schema
from fnd_oracle_userid
where read_only_flag = 'U';
SELECT table_name, partition_name, partition_name || '_TEMP',
--subpartition_count,
tablespace_name
BULK COLLECT
INTO l_cur_table, l_part_name, l_temp_table,
--l_sub_part,
l_tbspace
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM dba_tab_partitions
FROM all_tab_partitions
WHERE table_owner = l_apps_schema
AND table_name IN ('MSC_ITEM_HIERARCHY_MV', 'MSC_RESOURCE_HIERARCHY_MV')
ORDER BY table_name, partition_name;
SELECT nvl(ITEM_HIER_INIT_EXTENT,40),
nvl(ITEM_HIER_NEXT_EXTENT,5),
nvl(ITEM_HIER_PCT_INCREASE,0),
nvl(ITEM_HIER_INDX_INIT_EXTENT,40),
nvl(ITEM_HIER_INDX_NEXT_EXTENT,2),
nvl(ITEM_HIER_INDX_PCT_INCREASE,0),
nvl(RES_HIER_INIT_EXTENT,40),
nvl(RES_HIER_NEXT_EXTENT,5),
nvl(RES_HIER_PCT_INCREASE,0),
nvl(RES_HIER_INDX_INIT_EXTENT,40),
nvl(RES_HIER_INDX_NEXT_EXTENT,2),
nvl(RES_HIER_INDX_PCT_INCREASE,0)
INTO l_item_hier_init_extent,l_item_hier_next_extent,l_item_hier_pct_inc,
l_item_hier_indx_init_extent,l_item_hier_indx_nxt_extent,l_item_hier_indx_pct_inc,
l_res_hier_init_extent,l_res_hier_next_extent,l_res_hier_pct_inc,l_res_hier_indx_init_extent,
l_res_hier_indx_nxt_extent,l_res_hier_indx_pct_inc
from msc_atp_parameters
WHERE rownum = 1;
SELECT table_name, partition_name, partition_name || '_TEMP',
--subpartition_count,
tablespace_name
BULK COLLECT
INTO l_cur_table, l_part_name, l_temp_table,
--l_sub_part,
l_tbspace
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM dba_tab_partitions
FROM all_tab_partitions
WHERE table_owner = l_apps_schema
AND table_name = 'MSC_ITEM_HIERARCHY_MV'
ORDER BY partition_name;
SELECT NVL(ITEM_HIER_INIT_EXTENT,40),
nvl(ITEM_HIER_NEXT_EXTENT,5),
nvl(ITEM_HIER_PCT_INCREASE,0),
nvl(ITEM_HIER_INDX_INIT_EXTENT,40),
nvl(ITEM_HIER_INDX_NEXT_EXTENT,2),
nvl(ITEM_HIER_INDX_PCT_INCREASE,0)
INTO l_item_hier_init_extent,l_item_hier_next_extent,l_item_hier_pct_inc,
l_item_hier_indx_init_extent,l_item_hier_indx_nxt_extent,l_item_hier_indx_pct_inc
from msc_atp_parameters
WHERE rownum = 1;
'SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_system_items mi,
msc_allocation_assignments maa
WHERE maa.assignment_type = 7
AND maa.inventory_item_id = mi.inventory_item_id
AND maa.organization_id = mi.organization_id
AND maa.sr_instance_id = mi.sr_instance_id
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mi.plan_id = -1
AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
' UNION ALL
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_system_items mi,
msc_allocation_assignments maa
WHERE maa.assignment_type = 3
AND maa.inventory_item_id = mi.inventory_item_id
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mi.plan_id = -1
AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
' AND NOT EXISTS (
SELECT maa7.inventory_item_id
FROM msc_allocation_assignments maa7
WHERE maa7.inventory_item_id = mi.inventory_item_id
AND maa7.organization_id = mi.organization_id
AND maa7.sr_instance_id = mi.sr_instance_id
AND maa7.assignment_type = 7 )
UNION ALL
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa,
msc_system_items mi,
msc_item_categories mic
WHERE maa.assignment_type = 2
AND maa.category_set_id = mic.category_set_id
AND maa.category_name = mic.category_name
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mic.inventory_item_id = mi.inventory_item_id
AND mic.organization_id = mi.organization_id
AND mic.sr_instance_id = mi.sr_instance_id
AND mi.plan_id = -1
AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
' AND NOT EXISTS (
SELECT maa1.category_set_id
FROM msc_allocation_assignments maa1
WHERE maa1.inventory_item_id = mi.inventory_item_id
AND maa1.assignment_type = 3)
AND NOT EXISTS (
SELECT maa7.inventory_item_id
FROM msc_allocation_assignments maa7
WHERE maa7.inventory_item_id = mi.inventory_item_id
AND maa7.organization_id = mi.organization_id
AND maa7.sr_instance_id = mi.sr_instance_id
AND maa7.assignment_type = 7 )
UNION ALL
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa,
msc_system_items mi
WHERE maa.assignment_type = 6
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mi.organization_id = maa.organization_id
AND mi.sr_instance_id = maa.sr_instance_id
AND mi.plan_id = -1
AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
' AND NOT EXISTS (
SELECT /*+ leading(maa1) */ maa1.inventory_item_id
FROM msc_allocation_assignments maa1,
msc_item_categories mic
WHERE maa1.category_set_id = mic.category_set_id
AND maa1.category_name = mic.category_name
AND mi.inventory_item_id = mic.inventory_item_id
AND mi.organization_id = mic.organization_id
AND mi.sr_instance_id = mic.sr_instance_id
AND maa1.assignment_type = 2)
AND NOT EXISTS (
SELECT maa2.inventory_item_id
FROM msc_allocation_assignments maa2
WHERE maa2.inventory_item_id = mi.inventory_item_id
AND maa2.assignment_type = 3)
AND NOT EXISTS (
SELECT maa7.inventory_item_id
FROM msc_allocation_assignments maa7
WHERE maa7.inventory_item_id = mi.inventory_item_id
AND maa7.organization_id = mi.organization_id
AND maa7.sr_instance_id = mi.sr_instance_id
AND maa7.assignment_type = 7 )
UNION ALL
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_system_items mi,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa
WHERE maa.assignment_type = 1
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mi.plan_id = -1
AND mi.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mi.sr_instance_id < ' || to_char(l_max_instance_id) ||
' AND NOT EXISTS (
SELECT /*+ leading(maa1) */ maa1.inventory_item_id
FROM msc_allocation_assignments maa1,
msc_item_categories mic
WHERE maa1.category_set_id = mic.category_set_id
AND maa1.category_name = mic.category_name
AND mi.inventory_item_id = mic.inventory_item_id
AND mi.organization_id = mic.organization_id
AND mi.sr_instance_id = mic.sr_instance_id
AND maa1.assignment_type = 2)
AND NOT EXISTS (
SELECT maa2.inventory_item_id
FROM msc_allocation_assignments maa2
WHERE maa2.inventory_item_id = mi.inventory_item_id
AND maa2.assignment_type = 3)
AND NOT EXISTS (
SELECT maa3.inventory_item_id
FROM msc_allocation_assignments maa3
WHERE maa3.organization_id = mi.organization_id
AND maa3.sr_instance_id = mi.sr_instance_id
AND maa3.assignment_type = 6)
AND NOT EXISTS (
SELECT maa7.inventory_item_id
FROM msc_allocation_assignments maa7
WHERE maa7.inventory_item_id = mi.inventory_item_id
AND maa7.organization_id = mi.organization_id
AND maa7.sr_instance_id = mi.sr_instance_id
AND maa7.assignment_type = 7 )';
'SELECT maa.resource_id RESOURCE_ID,
mdr.department_id DEPARTMENT_ID,
mdr.organization_id ORGANIZATION_ID,
mdr.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa,
msc_department_resources mdr
WHERE maa.assignment_type = 4
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND maa.resource_id = mdr.resource_id
AND maa.department_id = mdr.department_id
AND maa.organization_id = mdr.organization_id
AND maa.sr_instance_id = mdr.sr_instance_id
AND maa.resource_group IS NULL
AND mdr.plan_id = -1
AND mdr.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mdr.sr_instance_id < ' || to_char(l_max_instance_id) ||
' UNION ALL
SELECT mdr.resource_id RESOURCE_ID,
mdr.department_id DEPARTMENT_ID,
mdr.organization_id ORGANIZATION_ID,
mdr.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa,
msc_department_resources mdr
WHERE maa.assignment_type = 5
AND maa.sr_instance_id = mdr.sr_instance_id
AND maa.resource_group = mdr.resource_group_name
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mdr.plan_id = -1
AND maa.resource_group IS NOT NULL
AND mdr.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mdr.sr_instance_id < ' || to_char(l_max_instance_id) ||
' AND NOT EXISTS (
SELECT maa1.resource_id
FROM msc_allocation_assignments maa1
WHERE maa1.resource_id = mdr.resource_id
AND maa1.organization_id = mdr.organization_id
AND maa1.sr_instance_id = mdr.sr_instance_id
AND maa1.department_id = mdr.department_id
AND maa1.assignment_type = 4)
UNION ALL
SELECT mdr.resource_id RESOURCE_ID,
mdr.department_id DEPARTMENT_ID,
mdr.organization_id ORGANIZATION_ID,
mdr.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa,
msc_department_resources mdr
WHERE maa.assignment_type = 6
AND maa.sr_instance_id = mdr.sr_instance_id
AND maa.organization_id = mdr.organization_id
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mdr.plan_id = -1
AND maa.resource_group IS NULL
AND maa.resource_id IS NULL
AND mdr.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mdr.sr_instance_id < ' || to_char(l_max_instance_id) ||
' AND NOT EXISTS (
SELECT maa1.resource_id
FROM msc_allocation_assignments maa1
WHERE maa1.resource_id = mdr.resource_id
AND maa1.organization_id = mdr.organization_id
AND maa1.sr_instance_id = mdr.sr_instance_id
AND maa1.department_id = mdr.department_id
AND maa1.assignment_type = 4)
AND NOT EXISTS (
SELECT maa2.resource_group
FROM msc_allocation_assignments maa2
WHERE maa2.resource_group = mdr.resource_group_name
AND maa2.sr_instance_id = mdr.sr_instance_id
AND maa2.assignment_type = 5)
UNION ALL
SELECT mdr.resource_id RESOURCE_ID,
mdr.department_id DEPARTMENT_ID,
mdr.organization_id ORGANIZATION_ID,
mdr.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_department_resources mdr,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa
WHERE maa.assignment_type = 1
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mdr.plan_id = -1
AND maa.resource_id IS NULL
AND maa.resource_group IS NULL
AND mdr.sr_instance_id >= ' || to_char(l_min_instance_id) ||
' AND mdr.sr_instance_id < ' || to_char(l_max_instance_id) ||
' AND NOT EXISTS (
SELECT maa1.resource_id
FROM msc_allocation_assignments maa1
WHERE maa1.resource_id = mdr.resource_id
AND maa1.department_id = mdr.department_id
AND maa1.organization_id = mdr.organization_id
AND maa1.sr_instance_id = mdr.sr_instance_id
AND maa1.assignment_type = 4)
AND NOT EXISTS (
SELECT maa1.resource_group
FROM msc_allocation_assignments maa1
WHERE maa1.resource_group = mdr.resource_group_name
AND maa1.sr_instance_id = mdr.sr_instance_id
AND maa1.assignment_type = 5)
AND NOT EXISTS (
SELECT maa3.resource_group
FROM msc_allocation_assignments maa3
WHERE maa3.organization_id = mdr.organization_id
AND maa3.sr_instance_id = mdr.sr_instance_id
AND maa3.assignment_type = 6)';