[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
into l_alloc_percent
from msc_demands d
--5027568
where decode(d.origination_type, -100, 30, d.origination_type) in (6, 30)--DEMAND_SALES_ORDER
and d.plan_id = p_plan_id
and d.demand_id = p_demand_id
and NVL(d.demand_class,'-1') = p_demand_class;
select 1
into l_alloc_percent
from msc_sales_orders so,
msc_demands d
where d.origination_type in (6, 30) --DEMAND_SALES_ORDER
and d.plan_id = p_plan_id
and d.demand_id = p_demand_id
and d.reservation_id = so.demand_id
and so.sr_instance_id= d.sr_instance_id
and so.organization_id= d.organization_id
and so.inventory_item_id= d.inventory_item_id
--bug 2424357: treat others as -1
--and NVL(so.demand_class, NVL(l_default_demand_class,'@@@')) = p_demand_class
and NVL(so.demand_class, NVL(l_default_demand_class,'-1')) = p_demand_class
and so.parent_demand_id is null;*/
select 1
into l_alloc_percent
from msc_demands d
where d.plan_id = p_plan_id
and d.demand_id = p_demand_id
and MSC_AATP_FUNC.get_hierarchy_demand_class(d.customer_id, d.ship_to_site_id,d.inventory_item_id, d.organization_id, d.sr_instance_id, p_demand_date, p_level_id, null) = p_demand_class;
SELECT 1
INTO l_alloc_percent
FROM msc_sales_orders so,
msc_demands d
WHERE d.demand_id = p_demand_id
AND d.plan_id = p_plan_id
AND so.demand_id = d.reservation_id
AND so.sr_instance_id = d.sr_instance_id
AND so.inventory_item_id = d.inventory_item_id
AND so.organization_id = d.organization_id
AND so.parent_demand_id is null
AND NVL(so.demand_class, NVL(l_default_demand_class,'@@@')) = p_demand_class;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE demand_class = p_demand_class
AND inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND demand_class = p_demand_class
AND level_id = -1
AND p_request_date between effective_date and disable_date;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND demand_class = p_demand_class
AND level_id = -1
AND trunc(sysdate) between effective_date and disable_date;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND (p_request_date between effective_date and disable_date
OR trunc(sysdate) between effective_date and disable_date)
AND level_id = -1;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id = -1;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND demand_class = p_demand_class
AND level_id <> -1
AND p_request_date between effective_date and disable_date;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND demand_class = p_demand_class
AND level_id <> -1
AND trunc(sysdate) between effective_date and disable_date;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND (p_request_date between effective_date and disable_date
OR trunc(sysdate) between effective_date and disable_date)
AND level_id <> -1;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id <> -1;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE demand_class = p_demand_class
AND department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND level_id = -1
AND p_request_date between effective_date and disable_date;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE demand_class = p_demand_class
AND department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND level_id = -1
AND trunc(sysdate) between effective_date and disable_date;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND (p_request_date between effective_date and disable_date
OR trunc(sysdate) between effective_date and disable_date)
AND level_id = -1;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id = -1;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE demand_class = p_demand_class
AND department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND level_id <> -1
AND p_request_date between effective_date and disable_date;
SELECT allocation_percent/100
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE demand_class = p_demand_class
AND department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND level_id <> -1
AND trunc(sysdate) between effective_date and disable_date;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND (p_request_date between effective_date and disable_date
OR trunc(sysdate) between effective_date and disable_date)
AND level_id <> -1;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id <> -1;
SELECT distinct time_phase_id, allocation_rule_name
INTO l_time_phase_id, MSC_ATP_PVT.G_ALLOCATION_RULE_NAME
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND sr_instance_id = p_instance_id
AND greatest(p_request_date,trunc(sysdate)) between effective_date and disable_date; --bug3099066
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE demand_class = p_demand_class
AND time_phase_id = l_time_phase_id
--AND level_id = 1 -- demand_class hence no level_id
--AND class = '-1' -- demand_class hence no customer_class
;
SELECT CUSTOMER_CLASS_CODE
INTO l_class_code
FROM msc_trading_partners
WHERE PARTNER_ID = p_partner_id;
SELECT distinct time_phase_id, allocation_rule_name
INTO l_time_phase_id, MSC_ATP_PVT.G_ALLOCATION_RULE_NAME
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND sr_instance_id = p_instance_id
AND greatest(p_request_date,trunc(sysdate)) between effective_date and disable_date; --bug3099066
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 3
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = p_partner_id
AND partner_site_id = p_partner_site_id;
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, after the first select');
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, first select fails');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 3
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = p_partner_id
AND partner_site_id = -1;
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, after the second select');
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, second select fails');
/*SELECT count(*)
INTO l_temp
FROM msc_allocations
WHERE level_id =3
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = p_partner_id;
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 3
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = -1
AND partner_site_id = -1;
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, after the third select');
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, third select fails');
/*SELECT count(*)
INTO l_temp
FROM msc_allocations
WHERE level_id =3
AND time_phase_id = l_time_phase_id
AND class = l_class_code;
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 3
AND time_phase_id = l_time_phase_id
AND class = '-1'
AND partner_id = -1
AND partner_site_id = -1;
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, after the 4th select');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 2
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = p_partner_id;
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, after the first select');
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, first select fails');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 2
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = -1;
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, after the second select');
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, second select fails');
SELECT count(*)
INTO l_temp
FROM msc_allocations
WHERE level_id =2
AND time_phase_id = l_time_phase_id
AND class = l_class_code;
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id =2
AND time_phase_id = l_time_phase_id
AND class = '-1'
AND partner_id = -1;
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, after the third select');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 1
AND time_phase_id = l_time_phase_id
AND class = l_class_code;
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 1, after the first select');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 1
AND time_phase_id = l_time_phase_id
AND class = '-1';
msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 1, after the second select');
SELECT (allocation_percent- NVL(min_allocation_percent,0))/100
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE demand_class = p_demand_class
AND inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id = -1;
SELECT (allocation_percent- NVL(min_allocation_percent,0))/100
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE demand_class = p_demand_class
AND inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id <> -1;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id = -1;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_item_hierarchy_mv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id <> -1;
SELECT (allocation_percent- NVL(min_allocation_percent, 0))/100
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE demand_class = p_demand_class
AND department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id = -1;
SELECT (allocation_percent- NVL(min_allocation_percent, 0))/100
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE demand_class = p_demand_class
AND department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id <> -1;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id = -1;
SELECT DECODE(count(allocation_percent), 0, NULL, 0)
INTO l_alloc_percent
FROM msc_resource_hierarchy_mv
WHERE department_id = p_dept_id
AND resource_id = p_res_id
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id
AND p_request_date between effective_date and disable_date
AND level_id <> -1;
SELECT distinct time_phase_id, allocation_rule_name
INTO l_time_phase_id, MSC_ATP_PVT.G_ALLOCATION_RULE_NAME
FROM msc_resource_hierarchy_mv
WHERE resource_id = p_resource_id
AND department_id = p_department_id
AND organization_id = p_organization_id
AND sr_instance_id = p_instance_id
AND GREATEST(p_request_date,trunc(sysdate)) between effective_date and disable_date; --bug3333114
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE demand_class = p_demand_class
AND time_phase_id = l_time_phase_id
--AND level_id = 1 -- demand_class hence no level_id
--AND class = '-1' -- demand_class hence no customer_class
;
SELECT CUSTOMER_CLASS_CODE
INTO l_class_code
FROM msc_trading_partners
WHERE PARTNER_ID = p_partner_id;
SELECT distinct time_phase_id, allocation_rule_name
INTO l_time_phase_id, MSC_ATP_PVT.G_ALLOCATION_RULE_NAME
FROM msc_resource_hierarchy_mv
WHERE resource_id = p_resource_id
AND department_id = p_department_id
AND organization_id = p_organization_id
AND sr_instance_id = p_instance_id
AND greatest(p_request_date,trunc(sysdate)) between effective_date and disable_date; --bug3333114
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 3
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = p_partner_id
AND partner_site_id = p_partner_site_id;
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, after the first select');
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, first select fails');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 3
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = p_partner_id
AND partner_site_id = -1;
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, after the second select');
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, second select fails');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 3
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = -1
AND partner_site_id = -1;
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, after the third select');
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, third select fails');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 3
AND time_phase_id = l_time_phase_id
AND class = '-1'
AND partner_id = -1
AND partner_site_id = -1;
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, after the 4th select');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 2
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = p_partner_id;
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, after the first select');
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, first select fails');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 2
AND time_phase_id = l_time_phase_id
AND class = l_class_code
AND partner_id = -1;
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, after the second select');
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, second select fails');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id =2
AND time_phase_id = l_time_phase_id
AND class = '-1'
AND partner_id = -1;
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, after the third select');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 1
AND time_phase_id = l_time_phase_id
AND class = l_class_code;
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 1, after the first select');
SELECT distinct demand_class
INTO l_hierarchy_demand_class
FROM msc_allocations
WHERE level_id = 1
AND time_phase_id = l_time_phase_id
AND class = '-1';
msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 1, after the second select');