[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATED_SCHEDULE CONSTANT INTEGER := 2;
select cal.prior_seq_num
into var_fucd_seq
FROM bom_calendar_dates cal
WHERE cal.exception_set_id = var_exception_set_id
AND cal.calendar_code = var_calendar_code
AND cal.calendar_date = TRUNC(fucd) ;
select cal.prior_seq_num
into var_lucd_seq
FROM bom_calendar_dates cal
WHERE cal.exception_set_id = var_exception_set_id
AND cal.calendar_code = var_calendar_code
AND cal.calendar_date = TRUNC(var_date) ;
SELECT DECODE(arg_plan_id, -1, sysdate, trunc(curr_start_date)),
DECODE(arg_plan_id, -1, sysdate+365, trunc(curr_cutoff_date))
FROM msc_plans
WHERE plan_id = arg_plan_id;
SELECT cal.calendar_date
FROM msc_calendar_dates cal,
msc_trading_partners tp
WHERE tp.sr_tp_id = arg_plan_organization_id
AND tp.sr_instance_id = arg_plan_instance_id
AND tp.calendar_exception_set_id = cal.exception_set_id
AND tp.partner_type = 3
AND tp.calendar_code = cal.calendar_code
AND tp.sr_instance_id = cal.sr_instance_id
AND cal.calendar_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
ORDER BY cal.calendar_date;
select period_start_date
from msc_analysis_aggregate
where plan_id = arg_plan_id
and record_type = 3
and period_type = 1
and period_start_date <= p_st_date
order by period_start_date desc;
SELECT /*+ INDEX(rec, MSC_SUPPLIES_N1) */
list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
DECODE(rec.order_type,
PURCHASE_ORDER, PO,
PURCH_REQ, DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT,REQ),
WORK_ORDER, WIP,
FLOW_SCHED, WIP,
REPETITIVE_SCHEDULE,PLANNED,
--PLANNED_ORDER, DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT,PLANNED),
PLANNED_ORDER, DECODE(nvl(rec.item_type_value,1),2,PLANNED_DEF_TO,PLANNED),/*Modified for USAF Suppl */
NONSTD_JOB, WIP,
RECEIPT_PURCH_ORDER,RECEIVING,
--SHIPMENT, TRANSIT,
SHIPMENT ,DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT,TRANSIT),
RECEIPT_SHIPMENT, RECEIVING,
PAYBACK_SUPPLY, PB_SUPPLY,
--ON_HAND_QTY, ON_HAND,
ON_HAND_QTY,DECODE(nvl(rec.item_type_value,1),2,DEF_OH,ON_HAND),
AGG_REP_SCHEDULE, CURRENT_S,
-- RETURNS, RETURN_SUP,
PLANNED__REPAIR_WORK_ORDER, PLANNED_REPAIR_WO,
REPAIR_WORK_ORDER, decode(rec.produces_to_stock,1,SUPPLY,REPAIR_WO), /*Added for bug 12731259*/
REPAIR_WO_AGGR,decode(rec.produces_to_stock,1,SUPPLY,REPAIR_WO),
RETURNS_FORECAST , RET_FOR,
EXTERNAL_REPAIR_WO,EXTERNAL_RO,
PLANNED) row_type,
DECODE(rec.order_type,
PURCHASE_ORDER, PO_OFF,
PURCH_REQ, DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT_OFF,REQ_OFF),
WORK_ORDER, WIP_OFF,
FLOW_SCHED, WIP_OFF,
REPETITIVE_SCHEDULE,PLANNED_OFF,
PLANNED_ORDER, DECODE(nvl(rec.item_type_value,1),2,PLANNED_DEF_TO_OFF,PLANNED_OFF),
NONSTD_JOB, WIP_OFF,
RECEIPT_PURCH_ORDER,RECEIVING_OFF,
--SHIPMENT, TRANSIT_OFF,
SHIPMENT,DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT_OFF,TRANSIT_OFF),
RECEIPT_SHIPMENT, RECEIVING_OFF,
DIS_JOB_BY, WIP_OFF,
NON_ST_JOB_BY, WIP_OFF,
REP_SCHED_BY, PLANNED_OFF,
PLANNED_BY, PLANNED_OFF,
FLOW_SCHED_BY, WIP_OFF,
PAYBACK_SUPPLY, PB_SUPPLY_OFF,
--ON_HAND_QTY, ON_HAND_OFF,
ON_HAND_QTY ,DECODE(nvl(rec.item_type_value,1),2,DEF_OH_OFF,ON_HAND_OFF),
AGG_REP_SCHEDULE, CURRENT_S_OFF,
-- RETURNS, RETURNS_OFF,
PLANNED__REPAIR_WORK_ORDER,PLANNED_REPAIR_WO_OFF,
REPAIR_WORK_ORDER,decode(rec.produces_to_stock,1,SUPPLY_OFF,REPAIR_WO_OFF), /*Added for bug 12731259*/
REPAIR_WO_AGGR,decode(rec.produces_to_stock,1,SUPPLY_OFF,REPAIR_WO_OFF),
RETURNS_FORECAST, RET_FOR_OFF,
EXTERNAL_REPAIR_WO,EXTERNAL_RO_OFF,
PLANNED_OFF) offset,
dates.calendar_date new_date,
decode(rec.order_type, PAYBACK_SUPPLY,
dates.calendar_date, rec.old_schedule_date) old_date,
SUM(DECODE(rec.disposition_status_type,
2, 0, DECODE(rec.last_unit_completion_date,
NULL, nvl(rec.firm_quantity,rec.new_order_quantity),
rec.daily_rate))) new_quantity,
SUM(NVL(rec.old_order_quantity,0)) old_quantity,
sum(0) dos,
0 cost
FROM msc_form_query list,
msc_trading_partners param,
msc_system_items msi,
msc_supplies rec,
msc_calendar_dates dates
WHERE (arg_res_level = 1
OR (arg_res_level = 2
AND rec.project_id is NULL)
OR (DECODE(arg_res_level,
3,nvl(rec.planning_group,'-23453'),
4,nvl(to_char(rec.project_id), '-23453'))
= nvl(arg_resval1,'-23453'))
OR (arg_res_level = 5
AND nvl(to_char(rec.project_id), '-23453')
= nvl(arg_resval1,'-23453')
AND nvl(rec.task_id, -23453) = nvl(arg_resval2, -23453)))
AND dates.sr_instance_id = rec.sr_instance_id
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.calendar_code = param.calendar_code
AND dates.calendar_date BETWEEN
trunc(nvl(rec.firm_date,rec.new_schedule_date))
AND NVL(rec.last_unit_completion_date,
trunc(nvl(rec.firm_date,rec.new_schedule_date)))
AND (trunc(nvl(rec.firm_date,rec.new_schedule_date)) <= last_date OR
trunc(rec.old_schedule_date) <= last_date)
AND rec.plan_id = msi.plan_id
AND rec.inventory_item_id = msi.inventory_item_id
AND rec.organization_id = msi.organization_id
AND rec.sr_instance_id = msi.sr_instance_id
AND msi.plan_id = list.number4
AND msi.inventory_item_id = list.number1
AND msi.organization_id = list.number2
AND msi.sr_instance_id = list.number3
AND param.sr_tp_id = rec.organization_id
AND param.sr_instance_id = rec.sr_instance_id
AND param.partner_type = 3
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
GROUP BY
list.number5,
list.number6,
list.number3,
DECODE(rec.order_type,
PURCHASE_ORDER, PO,
PURCH_REQ, DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT,REQ),
WORK_ORDER, WIP,
FLOW_SCHED, WIP,
REPETITIVE_SCHEDULE,PLANNED,
--PLANNED_ORDER, DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT,PLANNED),
PLANNED_ORDER, DECODE(nvl(rec.item_type_value,1),2,PLANNED_DEF_TO,PLANNED), /* Modified for USAF Suppl*/
NONSTD_JOB, WIP,
RECEIPT_PURCH_ORDER,RECEIVING,
-- SHIPMENT, TRANSIT,
SHIPMENT ,DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT,TRANSIT),
RECEIPT_SHIPMENT, RECEIVING,
PAYBACK_SUPPLY, PB_SUPPLY,
--ON_HAND_QTY, ON_HAND,
ON_HAND_QTY,DECODE(nvl(rec.item_type_value,1),2,DEF_OH,ON_HAND),
AGG_REP_SCHEDULE, CURRENT_S,
-- RETURNS, RETURN_SUP,
PLANNED__REPAIR_WORK_ORDER, PLANNED_REPAIR_WO,
REPAIR_WORK_ORDER, decode(rec.produces_to_stock,1,SUPPLY,REPAIR_WO), /*Added for bug 12731259*/
REPAIR_WO_AGGR,decode(rec.produces_to_stock,1,SUPPLY,REPAIR_WO),
RETURNS_FORECAST , RET_FOR,
EXTERNAL_REPAIR_WO,EXTERNAL_RO,
PLANNED),
DECODE(rec.order_type,
PURCHASE_ORDER, PO_OFF,
PURCH_REQ, DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT_OFF,REQ_OFF),
WORK_ORDER, WIP_OFF,
FLOW_SCHED, WIP_OFF,
REPETITIVE_SCHEDULE,PLANNED_OFF,
PLANNED_ORDER, DECODE(nvl(rec.item_type_value,1),2,PLANNED_DEF_TO_OFF,PLANNED_OFF),
NONSTD_JOB, WIP_OFF,
RECEIPT_PURCH_ORDER,RECEIVING_OFF,
--SHIPMENT, TRANSIT_OFF,
SHIPMENT,DECODE(nvl(rec.item_type_value,1),2,DEF_IN_TRANSIT_OFF,TRANSIT_OFF),
RECEIPT_SHIPMENT, RECEIVING_OFF,
DIS_JOB_BY, WIP_OFF,
NON_ST_JOB_BY, WIP_OFF,
REP_SCHED_BY, PLANNED_OFF,
PLANNED_BY, PLANNED_OFF,
FLOW_SCHED_BY, WIP_OFF,
PAYBACK_SUPPLY, PB_SUPPLY_OFF,
--ON_HAND_QTY, ON_HAND_OFF,
ON_HAND_QTY ,DECODE(nvl(rec.item_type_value,1),2,DEF_OH_OFF,ON_HAND_OFF),
AGG_REP_SCHEDULE, CURRENT_S_OFF,
-- RETURNS, RETURNS_OFF,
PLANNED__REPAIR_WORK_ORDER,PLANNED_REPAIR_WO_OFF,
REPAIR_WORK_ORDER,decode(rec.produces_to_stock,1,SUPPLY_OFF,REPAIR_WO_OFF), /*Added for bug 12731259*/
REPAIR_WO_AGGR,decode(rec.produces_to_stock,1,SUPPLY_OFF,REPAIR_WO_OFF),
RETURNS_FORECAST, RET_FOR_OFF,
EXTERNAL_REPAIR_WO,EXTERNAL_RO_OFF, /* Modified for USAF Suppl */
PLANNED_OFF),
dates.calendar_date,
decode(rec.order_type, PAYBACK_SUPPLY, dates.calendar_date,
rec.old_schedule_date)
UNION ALL
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
DECODE(mgr.origination_type,
--1, DECODE(mgr.item_type_value,2,DEFECTIVE_PD,DEPENDENT),
1,DECODE(nvl(mgr.item_type_value,1),2,PLANNED_DEF_PD,DEPENDENT),
2, DEPENDENT,
3, DEPENDENT,
4, DEPENDENT,
5, EXP_LOT,
6, SALES,
7, FORECAST,
8, OTHER,
9, OTHER,
10, OTHER,
11, OTHER,
12, OTHER,
15, OTHER,
16, SCRAP,
17, SCRAP,
18, SCRAP,
19, SCRAP,
20, SCRAP,
21, SCRAP,
22, PROD_FORECAST,
23, SCRAP,
24, DEPENDENT,
25, DEPENDENT,
26, SCRAP,
29, FORECAST, -- for SRO
30, DECODE(nvl(mgr.item_type_value,1),2,DEFECTIVE_PD,SALES),
DEMAND_PAYBACK, PB_DEMAND,
DEFECTIVE_PART_DEMAND , DEFECTIVE_PD ,
PLANNED_PART_DEMAND,DEFECTIVE_PD, /*Added for bug 12665917 */
MAINTENANCE_WO_DEMAND,REPAIR_WO_DEMAND, /*Added for bug#12651354 */
REPAIR_WO_AGGR,REPAIR_WO_DEMAND,/*Added for USAF Suppl*/
OTHER) row_type,
DECODE(mgr.origination_type,
1, DECODE(nvl(mgr.item_type_value,1),2,PLANNED_DEF_PD_OFF,DEPENDENT_OFF),
2, DEPENDENT_OFF,
3, DEPENDENT_OFF,
4, DEPENDENT_OFF,
5, EXP_LOT_OFF,
6, SALES_OFF,
7, FORECAST_OFF,
8, OTHER_OFF,
9, OTHER_OFF,
10, OTHER_OFF,
11, OTHER_OFF,
12, OTHER_OFF,
15, OTHER_OFF,
16, SCRAP_OFF,
17, SCRAP_OFF,
18, SCRAP_OFF,
19, SCRAP_OFF,
20, SCRAP_OFF,
21, SCRAP_OFF,
22, PROD_FORECAST_OFF,
23, SCRAP_OFF,
24, DEPENDENT_OFF,
25, DEPENDENT_OFF,
26, SCRAP_OFF,
29, FORECAST_OFF,
30, DECODE(nvl(mgr.item_type_value,1),2,DEFECTIVE_PD_OFF,SALES_OFF),
DEMAND_PAYBACK, PB_DEMAND_OFF,
DEFECTIVE_PART_DEMAND,DEFECTIVE_PD_OFF,
PLANNED_PART_DEMAND,DEFECTIVE_PD_OFF, /*Added for bug 12665917 */
MAINTENANCE_WO_DEMAND,REPAIR_WO_DEMAND_OFF, /*Added for bug#12651354 */
REPAIR_WO_AGGR,REPAIR_WO_DEMAND_OFF,/*Added for USAF Suppl*/
OTHER_OFF) offset,
dates.calendar_date new_date,
dates.calendar_date old_date,
SUM(DECODE(mgr.assembly_demand_comp_date,
NULL, DECODE(mgr.origination_type,
29,(nvl(mgr.probability,1)*
nvl(mgr.firm_quantity,using_requirement_quantity)),
31, 0,
nvl(mgr.firm_quantity,using_requirement_quantity)),
DECODE(mgr.origination_type,
29,(nvl(mgr.probability,1)*daily_demand_rate),
31, 0,
daily_demand_rate)))/
DECODE(nvl(LEAST(SUM(DECODE(mgr.origination_type,
29,nvl(mgr.probability,0),
null)) ,1) ,1),
0,1,
nvl(LEAST(SUM(DECODE(mgr.origination_type,
29,nvl(mgr.probability,0),
null)) ,1) ,1)) new_quantity,
0 old_quantity,
0 dos,
0 cost
FROM msc_form_query list,
msc_trading_partners param,
msc_demands mgr,
msc_calendar_dates dates
WHERE (arg_res_level = 1
OR (arg_res_level = 2
AND mgr.project_id is NULL)
OR (DECODE(arg_res_level,
3,nvl(mgr.planning_group,'-23453'),
4,nvl(to_char(mgr.project_id), '-23453'))
= nvl(arg_resval1,'-23453'))
OR (arg_res_level = 5
AND nvl(to_char(mgr.project_id), '-23453')
= nvl(arg_resval1,'-23453')
AND nvl(mgr.task_id, -23453) = nvl(arg_resval2, -23453)))
AND dates.sr_instance_id = mgr.sr_instance_id
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.calendar_code = param.calendar_code
AND dates.calendar_date BETWEEN trunc(
nvl(mgr.firm_date,mgr.using_assembly_demand_date))
AND NVL(trunc(mgr.assembly_demand_comp_date),
trunc(nvl(mgr.firm_date,mgr.using_assembly_demand_date)))
AND trunc(nvl(mgr.firm_date,mgr.using_assembly_demand_date))
<= trunc(last_date)
AND mgr.plan_id = list.number4
AND mgr.inventory_item_id = list.number1
AND mgr.organization_id = list.number2
AND mgr.sr_instance_id = list.number3
AND mgr.origination_type > 0 -- bug5653263
AND param.sr_tp_id = mgr.organization_id
AND param.sr_instance_id = mgr.sr_instance_id
AND param.partner_type = 3
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
AND (l_plan_type <> 4 or
l_plan_type = 4 and -- 5086979: IO plan don't show past due demand
trunc(mgr.using_assembly_demand_date) >= trunc(l_plan_start_date))
AND not exists (
select 'cancelled IR'
from msc_supplies mr
where mgr.origination_type in (30,6)
and mgr.disposition_id = mr.transaction_id
and mgr.plan_id = mr.plan_id
and mgr.sr_instance_id = mr.sr_instance_id
and mr.disposition_status_type = 2)
GROUP BY
list.number5,
list.number6,
list.number3,
DECODE(mgr.origination_type,
--1, DECODE(mgr.item_type_value,2,DEFECTIVE_PD,DEPENDENT),
1,DECODE(nvl(mgr.item_type_value,1),2,PLANNED_DEF_PD,DEPENDENT),
2, DEPENDENT,
3, DEPENDENT,
4, DEPENDENT,
5, EXP_LOT,
6, SALES,
7, FORECAST,
8, OTHER,
9, OTHER,
10, OTHER,
11, OTHER,
12, OTHER,
15, OTHER,
16, SCRAP,
17, SCRAP,
18, SCRAP,
19, SCRAP,
20, SCRAP,
21, SCRAP,
22, PROD_FORECAST,
23, SCRAP,
24, DEPENDENT,
25, DEPENDENT,
26, SCRAP,
29, FORECAST,
30, DECODE(nvl(mgr.item_type_value,1),2,DEFECTIVE_PD,SALES),
DEMAND_PAYBACK, PB_DEMAND,
DEFECTIVE_PART_DEMAND , DEFECTIVE_PD,
PLANNED_PART_DEMAND,DEFECTIVE_PD, /*Added for bug 12665917 */
MAINTENANCE_WO_DEMAND,REPAIR_WO_DEMAND, /*Added for bug#12651354 */
REPAIR_WO_AGGR,REPAIR_WO_DEMAND,/*Added for USAF Suppl*/
OTHER),
DECODE(mgr.origination_type,
1, DECODE(nvl(mgr.item_type_value,1),2,PLANNED_DEF_PD_OFF,DEPENDENT_OFF),
2, DEPENDENT_OFF,
3, DEPENDENT_OFF,
4, DEPENDENT_OFF,
5, EXP_LOT_OFF,
6, SALES_OFF,
7, FORECAST_OFF,
8, OTHER_OFF,
9, OTHER_OFF,
10, OTHER_OFF,
11, OTHER_OFF,
12, OTHER_OFF,
15, OTHER_OFF,
16, SCRAP_OFF,
17, SCRAP_OFF,
18, SCRAP_OFF,
19, SCRAP_OFF,
20, SCRAP_OFF,
21, SCRAP_OFF,
22, PROD_FORECAST_OFF,
23, SCRAP_OFF,
24, DEPENDENT_OFF,
25, DEPENDENT_OFF,
26, SCRAP_OFF,
29, FORECAST_OFF,
30, DECODE(nvl(mgr.item_type_value,1),2,DEFECTIVE_PD_OFF,SALES_OFF),
DEMAND_PAYBACK, PB_DEMAND_OFF,
DEFECTIVE_PART_DEMAND,DEFECTIVE_PD_OFF,
PLANNED_PART_DEMAND,DEFECTIVE_PD_OFF, /*Added for bug 12665917 */
MAINTENANCE_WO_DEMAND,REPAIR_WO_DEMAND_OFF, /*Added for bug#12651354 */
REPAIR_WO_AGGR,REPAIR_WO_DEMAND_OFF,/*Added for USAF Suppl*/
OTHER_OFF),
dates.calendar_date,
dates.calendar_date,
0
UNION ALL
--- ------------------------------------
--- FOR MAD / MAPE
--- ------------------------------------
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
MAD1 row_type,
MAD_OFF offset,
dates.calendar_date new_date,
dates.calendar_date old_date,
SQRT(SUM(DECODE(mgr.error_type, 1, mgr.forecast_MAD * mgr.forecast_MAD, 0))) new_quantity,
SQRT(SUM(DECODE(mgr.error_type, 2, ((mgr.forecast_MAD * mgr.using_requirement_quantity) * (mgr.forecast_MAD * mgr.using_requirement_quantity)), 0))) /
DECODE(SUM (NVL(mgr.using_requirement_quantity, 1)) ,0 ,1 ,
SUM (NVL(mgr.using_requirement_quantity, 1))) old_quantity,
0 dos,
0 cost
FROM msc_form_query list,
msc_trading_partners param,
msc_demands mgr,
msc_calendar_dates dates
WHERE (arg_res_level = 1
OR (arg_res_level = 2
AND mgr.project_id is NULL)
OR (DECODE(arg_res_level,
3,nvl(mgr.planning_group,'-23453'),
4,nvl(to_char(mgr.project_id), '-23453'))
= nvl(arg_resval1,'-23453'))
OR (arg_res_level = 5
AND nvl(to_char(mgr.project_id), '-23453')
= nvl(arg_resval1,'-23453')
AND nvl(mgr.task_id, -23453) = nvl(arg_resval2, -23453)))
AND dates.sr_instance_id = mgr.sr_instance_id
AND dates.exception_set_id = param.calendar_exception_set_id
AND dates.calendar_code = param.calendar_code
AND dates.calendar_date BETWEEN trunc(mgr.using_assembly_demand_date)
AND NVL(trunc(mgr.assembly_demand_comp_date),
trunc(mgr.using_assembly_demand_date))
AND trunc(mgr.using_assembly_demand_date) <= trunc(last_date)
AND mgr.plan_id = list.number4
AND mgr.inventory_item_id = list.number1
AND mgr.organization_id = list.number2
AND mgr.sr_instance_id = list.number3
AND param.sr_tp_id = mgr.organization_id
AND param.sr_instance_id = mgr.sr_instance_id
AND param.partner_type = 3
AND mgr.origination_type in (7, 29)
AND list.query_id = item_list_id
AND l_plan_type = 4 -- only show MAD for IO plan
AND list.number7 <> NODE_GL_FORECAST_ITEM
GROUP BY
list.number5,
list.number6,
list.number3,
MAD1, MAD_OFF,
dates.calendar_date,
dates.calendar_date,
0
UNION ALL
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
ATP row_type,
ATP_OFF offset,
avail.schedule_date new_date,
avail.schedule_date old_date,
avail.quantity_available new_quantity,
0 old_quantity,
0 dos,
0 cost
FROM msc_form_query list,
msc_available_to_promise avail
WHERE avail.schedule_date < last_date
AND avail.organization_id = list.number2
AND avail.plan_id = list.number4
AND avail.inventory_item_id = list.number1
AND avail.sr_instance_id = list.number3
AND list.query_id = item_list_id
UNION ALL
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
SS row_type,
SS_OFF offset,
safety.period_start_date new_date,
safety.period_start_date old_date,
sum(safety.safety_stock_quantity) new_quantity,
safety.organization_id old_quantity,
sum(safety.achieved_days_of_supply) dos,
sum(safety.safety_stock_quantity * item.standard_cost) cost
FROM msc_safety_stocks safety,
msc_form_query list ,
msc_system_items item
WHERE safety.period_start_date <= last_date
AND safety.organization_id = list.number2
AND safety.sr_instance_id = list.number3
AND safety.plan_id = list.number4
AND safety.inventory_item_id = list.number1
AND nvl(safety.project_id,1) =
decode(arg_res_level,4,nvl(arg_resval1,nvl(safety.project_id,1)),5,nvl(arg_resval1,nvl(safety.project_id,1)),nvl(safety.project_id,1))
AND nvl(safety.task_id,1) =
decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
and safety.safety_stock_quantity is not null
AND safety.organization_id = item.organization_id
AND safety.sr_instance_id = item.sr_instance_id
AND safety.plan_id = item.plan_id
AND safety.inventory_item_id = item.inventory_item_id
GROUP BY list.number5,
list.number6,
list.number3,
SS, SS_OFF, safety.period_start_date, safety.organization_id
UNION ALL
--------------------------------------------------------------------
-- This will select unconstrained safety stock for sro plans
---------------------------------------------------------------------
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
SS_UNC row_type,
SSUNC_OFF offset,
safety.period_start_date new_date,
safety.period_start_date old_date,
sum(safety.TARGET_SAFETY_STOCK) new_quantity,
sum(safety.TOTAL_UNPOOLED_SAFETY_STOCK) old_quantity,
sum(safety.target_days_of_supply) dos,
sum(safety.TARGET_SAFETY_STOCK * item.standard_cost) cost
FROM msc_safety_stocks safety,
msc_form_query list ,
msc_system_items item
WHERE safety.period_start_date <= last_date
AND safety.organization_id = list.number2
AND safety.sr_instance_id = list.number3
AND safety.plan_id = list.number4
AND safety.inventory_item_id = list.number1
AND nvl(safety.project_id,1) =
decode(arg_res_level,4,nvl(arg_resval1,nvl(safety.project_id,1)),5,nvl(arg_resval1,nvl(safety.project_id,1)),nvl(safety.project_id,1))
AND nvl(safety.task_id,1) =
decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
-- and safety.target_safety_stock is not null
AND safety.organization_id = item.organization_id
AND safety.sr_instance_id = item.sr_instance_id
AND safety.plan_id = item.plan_id
AND safety.inventory_item_id = item.inventory_item_id
GROUP BY list.number5,list.number6,list.number3,
SS_UNC, SSUNC_OFF,
safety.period_start_date
UNION ALL
--------------------------------------------------------------------
-- This will select user specified safety stocks
---------------------------------------------------------------------
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
USS row_type,
USS_OFF offset,
safety.period_start_date new_date,
safety.period_start_date old_date,
sum(safety.USER_DEFINED_SAFETY_STOCKS) new_quantity,
sum(0) old_quantity,
sum(safety.user_defined_dos) dos,
sum(safety.USER_DEFINED_SAFETY_STOCKS * item.standard_cost) cost
FROM msc_safety_stocks safety,
msc_form_query list,
msc_system_items item
WHERE safety.period_start_date <= last_date
AND safety.organization_id = list.number2
AND safety.sr_instance_id = list.number3
AND safety.plan_id = list.number4
AND safety.inventory_item_id = list.number1
AND nvl(safety.project_id,1) =
decode(arg_res_level,4,nvl(arg_resval1,nvl(safety.project_id,1)),5,nvl(arg_resval1,nvl(safety.project_id,1)),nvl(safety.project_id,1))
AND nvl(safety.task_id,1) =
decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
and nvl(safety.user_defined_safety_stocks,safety.user_defined_dos) is not null
AND safety.organization_id = item.organization_id
AND safety.sr_instance_id = item.sr_instance_id
AND safety.plan_id = item.plan_id
AND safety.inventory_item_id = item.inventory_item_id
GROUP BY list.number5,list.number6,list.number3,
USS, USS_OFF,
safety.period_start_date, 0
UNION ALL
--------------------------------------------------------------------
-- This will select Lead Time Variability Percentages
---------------------------------------------------------------------
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
MANU_VARI row_type,
MANF_VARI_OFF offset,
safety.period_start_date new_date,
safety.period_start_date old_date,
sum(safety.MFG_LTVAR_SS_PERCENT) new_quantity,
sum(safety.SUP_LTVAR_SS_PERCENT) old_quantity,
sum(safety.TRANSIT_LTVAR_SS_PERCENT) dos,
sum(safety.DEMAND_VAR_SS_PERCENT) cost
FROM msc_safety_stocks safety,
msc_form_query list,
msc_system_items item
WHERE safety.period_start_date <= last_date
AND safety.organization_id = list.number2
AND safety.sr_instance_id = list.number3
AND safety.plan_id = list.number4
AND safety.inventory_item_id = list.number1
AND nvl(safety.project_id,1) =
decode(arg_res_level,4,nvl(arg_resval1,nvl(safety.project_id,1)),5,nvl(arg_resval1,nvl(safety.project_id,1)),nvl(safety.project_id,1))
AND nvl(safety.task_id,1) =
decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
AND safety.organization_id = item.organization_id
AND safety.sr_instance_id = item.sr_instance_id
AND safety.plan_id = item.plan_id
AND safety.inventory_item_id = item.inventory_item_id
GROUP BY list.number5,list.number6,list.number3,
MANU_VARI, MANF_VARI_OFF,
safety.period_start_date
UNION ALL
--------------------------------------------------------------------
-- This will select minimum inventory levels
---------------------------------------------------------------------
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
min_inv_lvl row_type,
min_inv_lvl_off offset,
lvl.inventory_date new_date,
lvl.inventory_date old_date,
min(lvl.Min_quantity) new_quantity,
min(0) old_quantity,
min(lvl.min_quantity_dos) dos,
0
FROM msc_inventory_levels lvl,
msc_form_query list
WHERE lvl.inventory_date <= last_date
AND lvl.organization_id = list.number2
AND lvl.sr_instance_id = list.number3
AND lvl.plan_id = list.number4
AND lvl.inventory_item_id = list.number1
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
AND nvl(lvl.min_quantity,lvl.min_quantity_dos) is not null
GROUP BY list.number5,list.number6,list.number3,
min_inv_lvl, min_inv_lvl_off,
lvl.inventory_date
UNION ALL
--------------------------------------------------------------------
-- This will select maximum inventory levels
---------------------------------------------------------------------
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
max_inv_lvl row_type,
max_inv_lvl_off offset,
lvl.inventory_date new_date,
lvl.inventory_date old_date,
max(lvl.Max_quantity) new_quantity,
max(0) old_quantity,
max(lvl.max_quantity_dos) dos,
0
FROM msc_inventory_levels lvl,
msc_form_query list
WHERE lvl.inventory_date<= last_date
AND lvl.organization_id = list.number2
AND lvl.sr_instance_id = list.number3
AND lvl.plan_id = list.number4
AND lvl.inventory_item_id = list.number1
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
AND nvl(lvl.max_quantity,lvl.max_quantity_dos) is not null
GROUP BY list.number5,list.number6,list.number3,
max_inv_lvl, max_inv_lvl_off,
lvl.inventory_date
union all
--------------------------------------------------------------------
-- This will select Target Inventory Levels
---------------------------------------------------------------------
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
TARGET_SER_LVL row_type,
TARGET_SER_OFF offset,
nvl(lvl.week_start_date, lvl.period_start_date) new_date,
nvl(lvl.week_start_date, lvl.period_start_date) old_date,
avg(lvl.TARGET_SERVICE_LEVEL) new_quantity,
0 old_quantity,
0 dos,
0
FROM msc_analysis_aggregate lvl,
msc_form_query list,
msc_plan_buckets mpb
WHERE lvl.record_type = 3
AND lvl.period_type = 1
AND lvl.plan_id = list.number4
AND lvl.inventory_item_id = list.number1
AND lvl.organization_id = list.number2
AND lvl.sr_instance_id = list.number3
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
AND mpb.plan_id = lvl.plan_id
AND ( (mpb.bucket_type = 2 and lvl.week_start_date = mpb.BKT_START_DATE) or
(mpb.bucket_type = 3 and lvl.period_start_date = mpb.BKT_START_DATE) )
GROUP BY list.number5,list.number6,list.number3,
TARGET_SER_LVL, TARGET_SER_OFF,
nvl(lvl.week_start_date, lvl.period_start_date) ,
nvl(lvl.week_start_date, lvl.period_start_date)
union all
--------------------------------------------------------------------
-- This will select ACHIEVED Inventory Levels
---------------------------------------------------------------------
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
ACHIEVED_SER_LVL row_type,
ACHIEVED_SER_OFF offset,
nvl(lvl.week_start_date, lvl.period_start_date) new_date,
nvl(lvl.week_start_date, lvl.period_start_date) old_date,
sum(lvl.ACHIEVED_SERVICE_LEVEL_QTY1)/sum(decode(lvl.ACHIEVED_SERVICE_LEVEL_QTY2, 0, 1, lvl.ACHIEVED_SERVICE_LEVEL_QTY2)) new_quantity,
0 old_quantity,
0 dos,
0
FROM msc_analysis_aggregate lvl,
msc_form_query list,
msc_plan_buckets mpb
WHERE lvl.record_type = 3
AND lvl.period_type = 1
AND lvl.plan_id = list.number4
AND lvl.organization_id = list.number2
AND lvl.sr_instance_id = list.number3
AND lvl.inventory_item_id = list.number1
AND list.query_id = item_list_id
AND list.number7 <> NODE_GL_FORECAST_ITEM
AND mpb.plan_id = lvl.plan_id
AND ( (mpb.bucket_type = 2 and lvl.week_start_date = mpb.BKT_START_DATE) or
(mpb.bucket_type = 3 and lvl.period_start_date = mpb.BKT_START_DATE) )
GROUP BY list.number5,list.number6,list.number3,
ACHIEVED_SER_LVL, ACHIEVED_SER_OFF,
nvl(lvl.week_start_date, lvl.period_start_date) ,
nvl(lvl.week_start_date, lvl.period_start_date)
union all
--------------------------------------------------------------------
-- This select will ensure that all selected items get into cursor
-- even though they do not have any activity
---------------------------------------------------------------------
SELECT list.number5,
list.number6,
list.number3,
ON_HAND,
ON_HAND_OFF,
to_date(1, 'J'),
to_date(1, 'J'),
0,
0,
0,
0
FROM msc_form_query list
WHERE list.query_id = item_list_id
ORDER BY
1, 2, 6, 4;
select nvl(standard_cost,0)
from msc_system_items
where inventory_item_id=p_inventory_item_id
and organization_id =p_organization_id
and sr_instance_id =p_sr_instance_id
and plan_id =p_plan_id;
prev_ss_org.delete;
prev_ss_qty.delete;
prev_ss_dos_arr.delete;
prev_ss_cost_arr.delete;
SELECT msi.calculate_atp
FROM msc_system_items msi,
msc_form_query mfq
WHERE msi.inventory_item_id = mfq.number1
AND msi.organization_id = mfq.number2
AND msi.plan_id = arg_plan_id
AND msi.sr_instance_id = mfq.number3
AND mfq.query_id = arg_query_id
and mfq.number5 = p_item_id
and mfq.number6 = p_org_id
and mfq.number3 = p_inst_id;
INSERT INTO msc_material_plans(
query_id,
organization_id,
sr_instance_id,
plan_id,
plan_organization_id,
plan_instance_id,
inventory_item_id,
horizontal_plan_type,
horizontal_plan_type_text,
bucket_type,
bucket_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity1, -- SALES_OFF
quantity2, -- FORECAST_OFF
quantity3, -- PROD_FORECAST
quantity4, -- DEPENDENT_OFF
quantity5, -- SCRAP_OFF
quantity6, -- PB_DEMAND_OFF CONSTANT INTEGER := 5
quantity7, -- OTHER_OFF CONSTANT INTEGER := 6
quantity8, -- GROSS_OFF CONSTANT INTEGER := 7
quantity9, -- WIP_OFF CONSTANT INTEGER := 8
quantity10, -- PO_OFF CONSTANT INTEGER := 9
quantity11, -- REQ_OFF CONSTANT INTEGER := 10
quantity12, -- TRANSIT_OFF CONSTANT INTEGER := 11
quantity13, -- RECEIVING_OFF_OFF CONSTANT INTEGER := 12
quantity14, -- PLANEED_OFF_OFF CONSTANT INTEGER := 13
quantity15, -- PB_SUPPLY_OFF CONSTANT INTEGER := 14
quantity16, -- SUPPLY_OFF CONSTANT INTEGER := 15
quantity17, -- ON_HAND_OFF CONSTANT INTEGER := 16
quantity18, -- PAB_OFF CONSTANT INTEGER := 17
quantity19, -- SS_OFF CONSTANT INTEGER := 18
quantity20, -- ATP_OFF CONSTANT INTEGER := 19
quantity21, -- CURRENT_S_OFF CONSTANT INTEGER := 20
quantity22, -- POH_OFF CONSTANT INTEGER := 21
quantity23, -- EXP_LOT_OFF CONSTANT INTEGER := 22
quantity24, -- SSUNC_OFF CONSTANT INTEGER := 24
quantity25, -- min_inv_lvl_off CONSTANT INTEGER := 25
quantity26, -- max_inv_lvl_off CONSTANT INTEGER := 26
quantity27, -- SS_DOS_OFF CONSTANT INTEGER := 27
quantity28, -- SS_VAL_OFF CONSTANT INTEGER := 28
quantity29, -- SSUNC_DOS_OFF CONSTANT INTEGER := 29
quantity30, -- SSUNC_VAL_OFF CONSTANT INTEGER := 30
quantity31, -- USS_OFF CONSTANT INTEGER := 31
quantity32, -- USS_DOS_OFF CONSTANT INTEGER := 32
quantity33, -- USS_VAL_OFF CONSTANT INTEGER := 33
quantity34, -- TAGET_OFF
quantity35,
quantity36, -- Non Pool
quantity37, -- Manf Vari
quantity38,
quantity39,
quantity40,
quantity41,
quantity42,
quantity46,
quantity47,
quantity48,
quantity49,
quantity50,
quantity51,
quantity52 ,
quantity53,
quantity54,
quantity55,
quantity56 )
VALUES (
arg_query_id,
p_org_id,
p_inst_id,
arg_plan_id,
arg_plan_organization_id,
arg_plan_instance_id,
p_item_id,
1,
'HORIZONTAL PLAN',
arg_bucket_type,
var_dates(a),
SYSDATE,
-1,
SYSDATE,
-1,
bkt_data.qty1(a),
bkt_data.qty2(a),
bkt_data.qty3(a),
bkt_data.qty4(a),
bkt_data.qty5(a),
bkt_data.qty6(a),
bkt_data.qty7(a),
bkt_data.qty8(a),
bkt_data.qty9(a),
bkt_data.qty10(a),
bkt_data.qty11(a),
bkt_data.qty12(a),
bkt_data.qty13(a),
bkt_data.qty14(a),
bkt_data.qty15(a),
bkt_data.qty16(a),
bkt_data.qty17(a),
bkt_data.qty18(a),
bkt_data.qty19(a),
bkt_data.qty20(a),
bkt_data.qty21(a),
bkt_data.qty22(a),
bkt_data.qty23(a),
bkt_data.qty24(a),
bkt_data.qty25(a),
bkt_data.qty26(a),
bkt_data.qty27(a),
bkt_data.qty28(a),
bkt_data.qty29(a),
bkt_data.qty30(a),
bkt_data.qty31(a),
bkt_data.qty32(a),
bkt_data.qty33(a),
bkt_data.qty34(a),
bkt_data.qty35(a),
bkt_data.qty36(a),
bkt_data.qty37(a),
bkt_data.qty38(a),
bkt_data.qty39(a),
bkt_data.qty40(a),
bkt_data.qty41(a),
bkt_data.qty42(a),
bkt_data.qty46(a),
bkt_data.qty47(a),
bkt_data.qty48(a),
bkt_data.qty49(a),
bkt_data.qty50(a),
bkt_data.qty51(a),
bkt_data.qty52(a),
bkt_data.qty53(a),
bkt_data.qty54(a),
bkt_data.qty55(a),
bkt_data.qty56(a));
INSERT INTO msc_material_plans(
query_id,
organization_id,
sr_instance_id,
plan_id,
plan_organization_id,
plan_instance_id,
inventory_item_id,
horizontal_plan_type,
horizontal_plan_type_text,
bucket_type,
bucket_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity1, quantity2, quantity3, quantity4,
quantity5, quantity6, quantity7, quantity8,
quantity9, quantity10, quantity11, quantity12,
quantity13, quantity14, quantity15, quantity16,
quantity17, quantity18, quantity19, quantity20,
quantity21, quantity22, quantity23, quantity24, quantity25,
quantity26, quantity27, quantity28, quantity29,
quantity30, quantity31, quantity32, quantity33, quantity34,
quantity42,quantity43,quantity44,quantity45,quantity46,quantity47,
quantity48,quantity49,quantity50,quantity51,quantity52)
VALUES (
arg_query_id,
p_org_id,
p_inst_id,
arg_plan_id,
arg_plan_organization_id,
arg_plan_instance_id,
p_item_id,
10,
'ENTERPRIZE_VIEW',
arg_bucket_type,
sysdate,
SYSDATE,
-1,
SYSDATE,
-1,
ep_bucket_cells_tab(0),
ep_bucket_cells_tab(1),
ep_bucket_cells_tab(2),
ep_bucket_cells_tab(3),
ep_bucket_cells_tab(4),
ep_bucket_cells_tab(5),
ep_bucket_cells_tab(6),
ep_bucket_cells_tab(7),
ep_bucket_cells_tab(8),
ep_bucket_cells_tab(9),
ep_bucket_cells_tab(10),
ep_bucket_cells_tab(11),
ep_bucket_cells_tab(12),
ep_bucket_cells_tab(13),
ep_bucket_cells_tab(14),
ep_bucket_cells_tab(15),
ep_bucket_cells_tab(16),
ep_bucket_cells_tab(17),
ep_bucket_cells_tab(18),
ep_bucket_cells_tab(19),
ep_bucket_cells_tab(20),
ep_bucket_cells_tab(21),
ep_bucket_cells_tab(22),
ep_bucket_cells_tab(23),
ep_bucket_cells_tab(24),
ep_bucket_cells_tab(25),
ep_bucket_cells_tab(26),
ep_bucket_cells_tab(27),
ep_bucket_cells_tab(28),
ep_bucket_cells_tab(29),
ep_bucket_cells_tab(30),
ep_bucket_cells_tab(31),
ep_bucket_cells_tab(32),
ep_bucket_cells_tab(33),
ep_bucket_cells_tab(41),
ep_bucket_cells_tab(42),
ep_bucket_cells_tab(43),
ep_bucket_cells_tab(44),
ep_bucket_cells_tab(45),
ep_bucket_cells_tab(46),
ep_bucket_cells_tab(47),
ep_bucket_cells_tab(48),
ep_bucket_cells_tab(49),
ep_bucket_cells_tab(50),
ep_bucket_cells_tab(51)
);
SELECT plan_type into l_plan_type
FROM msc_plans
WHERE plan_id = arg_plan_id;
select 1
from msc_system_items
where plan_id = p_plan_id
and organization_id = v_org_id
and sr_instance_id = v_inst_id
and inventory_item_id = v_item_id
and bom_item_type in (2,5);
select 1
from msc_system_items
where plan_id = p_plan_id
and inventory_item_id = v_item_id
and bom_item_type in (2,5);
select mpt.object_type, mpt.source_type, mpt.sequence_id
from msc_pq_types mpt,
msc_system_items msi
where mpt.query_id = p_pf
and mpt.object_type = msi.organization_id
and mpt.source_type = msi.sr_instance_id
and msi.plan_id = p_plan_id
and msi.inventory_item_id = v_item_id;
select source_organization_id, sr_instance_id2
from msc_item_sourcing
where plan_id = p_plan_id
and organization_id = v_org_id
and inventory_item_id = v_item_id
and sr_instance_id = v_inst_id
and (source_organization_id <> organization_id or
sr_instance_id2 <> sr_instance_id)
and source_organization_id <> -1
union select organization_id, sr_instance_id
from msc_item_sourcing
where plan_id = p_plan_id
and source_organization_id = v_org_id
and inventory_item_id = v_item_id
and sr_instance_id2 = v_inst_id
and (source_organization_id <> organization_id or
sr_instance_id2 <> sr_instance_id)
and organization_id <> -1;
sql_stmt1 := 'INSERT INTO msc_form_query ( '||
'query_id, '||
'last_update_date, '||
'last_updated_by, '||
'creation_date, '||
'created_by, '||
'last_update_login, '||
'number1, '|| -- item_id
'number2, '|| -- org_id
'number3, '|| -- inst_id
'number4, '|| -- plan_id
'number5, '|| -- displayed item_id
'number6, '|| -- displayed org_id
'number7, '|| -- node type
'number8, '|| -- org sequence
'char1, '||
'char2) '||
' SELECT DISTINCT :p_query_id, '||
'sysdate, '||
'1, '||
'sysdate, '||
'1, '||
'1, '||
'msi.inventory_item_id, '||
'msi.organization_id, '||
'msi.sr_instance_id, '||
'msi.plan_id, ';
dbms_output.put_line('insert for org='||p_query_id||','||p_node_type||','||
v_inst_id||','|| v_org_id||','||
v_item_id||','||p_plan_id);
SELECT number1
FROM msc_form_query
WHERE query_id = l_query_id;
SELECT curr_start_date,
curr_plan_type,
decode(daily_material_constraints,1, 1, 0) +
decode(daily_resource_constraints,1, 1, 0) +
decode(weekly_material_constraints,1, 1, 0) +
decode(weekly_resource_constraints,1, 1, 0) +
decode(period_material_constraints,1, 1, 0) +
decode(period_resource_constraints,1, 1, 0),
decode(enforce_sup_cap_constraints,1,1,0),
decode(daily_material_constraints,1, 1, 0) --ascp_supplier_constraints
FROM msc_plans
WHERE plan_id = p_plan_id;
select distinct number2,number1
from msc_form_query
where query_id = p_item_query_id
and number5 = p_item_id
and number6 = p_org_id
and number3 = p_inst_id ;
select distinct number1,number8
from msc_form_query
where query_id = p_item_query_id;
SELECT msc_form_query_s.nextval
INTO l_query_id
FROM dual;
sql_stmt := 'INSERT INTO msc_form_query ( '||
'query_id, '||
'last_update_date, '||
'last_updated_by, '||
'creation_date, '||
'created_by, '||
'last_update_login, '||
'number1) ' ||
'SELECT distinct :l_query_id,' ||
' sysdate, '||
' 1, '||
' sysdate, '||
' 1, '||
' 1, ';
FUNCTION update_ss
(p_plan_id number,
p_sr_instance_id number,
p_organization_id number,
p_item_id number,
p_from_date date,
p_to_date date ,
p_new_qty number ) return number is
PRAGMA AUTONOMOUS_TRANSACTION;
update msc_safety_stocks
set SAFETY_STOCK_QUANTITY = p_new_qty
where
plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_item_id
and period_start_date between p_from_date and p_to_date;
END update_ss ;