[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT trunc(curr_start_date)
FROM msc_plans
WHERE plan_id = G_plan_id;
SELECT MIN(trunc(mpsd.next_date))
FROM msc_period_start_dates mpsd,
msc_trading_partners mtp
WHERE
mtp.calendar_code = mpsd.calendar_code
AND mtp.calendar_exception_set_id = mpsd.exception_set_id
AND mtp.sr_tp_id = G_org_id
AND mtp.partner_type = 3
AND mtp.sr_instance_id = G_inst_id
AND mpsd.sr_instance_id = G_inst_id --Added instance id as per bug # 12573284 to improve performance
AND mpsd.sr_instance_id = mtp.sr_instance_id
AND mpsd.period_start_date >= trunc(G_week_bckt_cutoff_dt)
-- AND mpsd.period_sequence_num = ((SELECT mpsd2.period_sequence_num /* bug:6784251 Vpedarla */
-- AND mpsd.period_sequence_num = (SELECT mod((mpsd2.period_sequence_num + mtp.sr_instance_id - 1) , 12 ) + 1 /* bug:7257708 Vpedarla */
-- AND mpsd.period_sequence_num = (SELECT mod((mpsd2.period_sequence_num + G_period_bucket - 1) , 12 ) + 1 /* Bug: 8447261 Vpedarla */
-- AND mpsd.period_sequence_num = (SELECT mod((mpsd2.period_sequence_num + G_period_bucket - 1) , 12 ) Bug: 9719725
AND to_char(mpsd.period_start_date,'MON-YYYY') = (select to_CHAR(ADD_MONTHS(G_week_bckt_cutoff_dt,G_period_bucket),'MON-YYYY') from dual);
/* AND mod(mpsd.period_sequence_num,12) = (SELECT mod((mpsd2.period_sequence_num + G_period_bucket - 1) , 12 )
FROM msc_period_start_dates mpsd2
WHERE mpsd2.period_start_date = trunc(G_week_bckt_cutoff_dt)
AND mpsd2.calendar_code = mpsd.calendar_code
AND mpsd2.exception_set_id = mpsd.exception_set_id
-- AND mpsd2.sr_instance_id = mtp.sr_instance_id) + G_period_bucket); -- bug:6784251 Vpedarla
SELECT cal.calendar_date
FROM msc_calendar_dates cal,
msc_trading_partners tp
WHERE tp.sr_tp_id = G_org_id
AND tp.sr_instance_id = G_inst_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.sr_instance_id = G_inst_id --Added instance id as per bug # 12573284 to improve performance
AND cal.calendar_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
ORDER BY cal.calendar_date;
SELECT /*+ INDEX(rec, MSC_SUPPLIES_N1) */
/* list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
*/
gpi.inventory_item_id,
gpi.organization_id,
DECODE(rec.order_type,
PURCHASE_ORDER, PO,
PURCH_REQ, REQ,
WORK_ORDER, WIP,
FLOW_SCHED, WIP,
REPETITIVE_SCHEDULE,PLANNED,
PLANNED_ORDER, PLANNED,
NONSTD_JOB, WIP,
RECEIPT_PURCH_ORDER,RECEIVING,
SHIPMENT, TRANSIT,
RECEIPT_SHIPMENT, RECEIVING,
PAYBACK_SUPPLY, PB_SUPPLY,
ON_HAND_QTY, ON_HAND,
AGG_REP_SCHEDULE, CURRENT_S,
-- RETURNS, RETURN_SUP,
PLANNED) row_type,
DECODE(rec.order_type,
PURCHASE_ORDER, PO_OFF,
PURCH_REQ, REQ_OFF,
WORK_ORDER, WIP_OFF,
FLOW_SCHED, WIP_OFF,
REPETITIVE_SCHEDULE,PLANNED_OFF,
PLANNED_ORDER, PLANNED_OFF,
NONSTD_JOB, WIP_OFF,
RECEIPT_PURCH_ORDER,RECEIVING_OFF,
SHIPMENT, 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,
AGG_REP_SCHEDULE, CURRENT_S_OFF,
-- RETURNS, RETURNS_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(gpi.base_item_id,NULL, DECODE(rec.disposition_status_type, /* nsinghi: need to get replace for base_item_id */
2, 0, DECODE(rec.last_unit_completion_date,
NULL, rec.new_order_quantity, rec.daily_rate) ),
DECODE(rec.last_unit_completion_date,
NULL, 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,
gmp_pdr_items_gtmp gpi,
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(rec.new_schedule_date)
AND NVL(rec.last_unit_completion_date, trunc(rec.new_schedule_date))
AND (trunc(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 gpi.inventory_item_id = rec.inventory_item_id
AND gpi.organization_id = rec.organization_id
AND rec.plan_id = G_plan_id
AND rec.sr_instance_id = G_inst_id
AND param.sr_tp_id = rec.organization_id
AND param.sr_instance_id = G_inst_id --Added instance id as per bug # 12573284 to improve performance
AND param.sr_instance_id = rec.sr_instance_id
AND param.partner_type = 3
--AND list.query_id = item_list_id
GROUP BY
/*
list.number5,
list.number6,
list.number3,
*/
gpi.inventory_item_id,
gpi.organization_id,
-- G_sr_instance_id, /* Will not include sr_instance_id column in gmp_material_plans table. */
DECODE(rec.order_type,
PURCHASE_ORDER, PO,
PURCH_REQ, REQ,
WORK_ORDER, WIP,
FLOW_SCHED, WIP,
REPETITIVE_SCHEDULE,PLANNED,
PLANNED_ORDER, PLANNED,
NONSTD_JOB, WIP,
RECEIPT_PURCH_ORDER,RECEIVING,
SHIPMENT, TRANSIT,
RECEIPT_SHIPMENT, RECEIVING,
PAYBACK_SUPPLY, PB_SUPPLY,
ON_HAND_QTY, ON_HAND,
AGG_REP_SCHEDULE, CURRENT_S,
-- RETURNS, RETURN_SUP,
PLANNED),
DECODE(rec.order_type,
PURCHASE_ORDER, PO_OFF,
PURCH_REQ, REQ_OFF,
WORK_ORDER, WIP_OFF,
FLOW_SCHED, WIP_OFF,
REPETITIVE_SCHEDULE,PLANNED_OFF,
PLANNED_ORDER, PLANNED_OFF,
NONSTD_JOB, WIP_OFF,
RECEIPT_PURCH_ORDER,RECEIVING_OFF,
SHIPMENT, 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,
AGG_REP_SCHEDULE, CURRENT_S_OFF,
-- RETURNS, RETURNS_OFF,
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,
*/
gpi.inventory_item_id,
gpi.organization_id,
DECODE(mgr.origination_type,
1, 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, SALES,
DEMAND_PAYBACK, PB_DEMAND,
OTHER) row_type,
DECODE(mgr.origination_type,
1, 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, SALES_OFF,
DEMAND_PAYBACK, PB_DEMAND_OFF,
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)*using_requirement_quantity),
31, 0,
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,
gmp_pdr_items_gtmp gpi,
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.sr_instance_id = G_inst_id --Added instance id as per bug # 12573284 to improve performance
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 gpi.inventory_item_id = mgr.inventory_item_id
AND gpi.organization_id = mgr.organization_id
AND mgr.sr_instance_id = G_inst_id
AND mgr.plan_id = G_plan_id
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 not exists (
select 'cancelled IR'
from msc_supplies mr
where mgr.origination_type in (30,6)
and mgr.disposition_id = mr.transaction_id
and mr.plan_id = G_plan_id --Added instance id as per bug # 12573284 to improve performance
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,
*/
gpi.inventory_item_id,
gpi.organization_id,
DECODE(mgr.origination_type,
1, 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, SALES,
DEMAND_PAYBACK, PB_DEMAND,
OTHER),
DECODE(mgr.origination_type,
1, 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, SALES_OFF,
DEMAND_PAYBACK, PB_DEMAND_OFF,
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,
*/
gpi.inventory_item_id,
gpi.organization_id,
MAD1 row_type,
MAD_OFF offset,
dates.calendar_date new_date,
dates.calendar_date old_date,
SUM(DECODE(mgr.error_type, 1, mgr.forecast_MAD, 0)) new_quantity,
SUM(DECODE(mgr.error_type, 2, mgr.forecast_MAD, 0)) old_quantity,
0 dos,
0 cost
FROM --msc_form_query list,
gmp_pdr_items_gtmp gpi,
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 gpi.inventory_item_id = mgr.inventory_item_id
AND gpi.organization_id = mgr.organization_id
AND mgr.sr_instance_id = G_inst_id
AND mgr.plan_id = G_plan_id
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
GROUP BY
/*
list.number5,
list.number6,
list.number3,
*/
gpi.inventory_item_id,
gpi.organization_id,
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,
*/
gpi.inventory_item_id,
gpi.organization_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,
gmp_pdr_items_gtmp gpi,
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
*/
AND avail.organization_id = gpi.organization_id
AND avail.inventory_item_id = gpi.inventory_item_id
AND avail.sr_instance_id = G_inst_id
AND avail.plan_id = G_plan_id
UNION ALL
SELECT /*list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
*/
gpi.inventory_item_id,
gpi.organization_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 * gpi.standard_cost) cost
FROM msc_safety_stocks safety,
-- msc_form_query list ,
-- msc_system_items item
gmp_pdr_items_gtmp gpi
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 safety.organization_id = gpi.organization_id
AND safety.inventory_item_id = gpi.inventory_item_id
AND safety.plan_id = G_plan_id
AND safety.sr_instance_id = G_inst_id
/*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 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,
*/
gpi.inventory_item_id,
gpi.organization_id,
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, */
gpi.inventory_item_id,
gpi.organization_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 * gpi.standard_cost) cost
FROM msc_safety_stocks safety,
-- msc_form_query list ,
-- msc_system_items item
gmp_pdr_items_gtmp gpi
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 safety.target_safety_stock is not null
AND safety.organization_id = gpi.organization_id
AND safety.inventory_item_id = gpi.inventory_item_id
AND safety.plan_id = G_plan_id
AND safety.sr_instance_id = G_inst_id
/*
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, */
gpi.inventory_item_id,
gpi.organization_id,
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,
*/
gpi.inventory_item_id,
gpi.organization_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 * gpi.standard_cost) cost
FROM msc_safety_stocks safety,
-- msc_form_query list,
-- msc_system_items item
gmp_pdr_items_gtmp gpi
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 safety.organization_id = gpi.organization_id
AND safety.sr_instance_id = G_inst_id
AND safety.plan_id = G_plan_id
AND safety.inventory_item_id = gpi.inventory_item_id
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, */
gpi.inventory_item_id,
gpi.organization_id,
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,
*/
gpi.inventory_item_id,
gpi.organization_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
gmp_pdr_items_gtmp gpi
WHERE safety.period_start_date <= last_date
AND safety.organization_id = gpi.organization_id
AND safety.sr_instance_id = G_inst_id
AND safety.plan_id = G_plan_id
AND safety.inventory_item_id = gpi.inventory_item_id
/*
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 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, */
gpi.inventory_item_id,
gpi.organization_id,
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,
*/
gpi.inventory_item_id,
gpi.organization_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
gmp_pdr_items_gtmp gpi
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 lvl.organization_id = gpi.organization_id
AND lvl.sr_instance_id = G_inst_id
AND lvl.plan_id = G_plan_id
AND lvl.inventory_item_id = gpi.inventory_item_id
AND nvl(lvl.min_quantity,lvl.min_quantity_dos) IS NOT NULL
GROUP BY /* list.number5,list.number6,list.number3, */
gpi.inventory_item_id,
gpi.organization_id,
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,
*/
gpi.inventory_item_id,
gpi.organization_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
gmp_pdr_items_gtmp gpi
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 lvl.organization_id = gpi.organization_id
AND lvl.sr_instance_id = G_inst_id
AND lvl.plan_id = G_plan_id
AND lvl.inventory_item_id = gpi.inventory_item_id
AND nvl(lvl.max_quantity,lvl.max_quantity_dos) IS NOT NULL
GROUP BY /* list.number5,list.number6,list.number3, */
gpi.inventory_item_id,
gpi.organization_id,
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,
*/
gpi.inventory_item_id,
gpi.organization_id,
TARGET_SER_LVL row_type,
TARGET_SER_OFF offset,
lvl.period_start_date new_date,
lvl.period_start_date old_date,
sum(lvl.TARGET_SERVICE_LEVEL) new_quantity,
0 old_quantity,
0 dos,
0
FROM msc_analysis_aggregate lvl,
-- msc_form_query list
gmp_pdr_items_gtmp gpi
WHERE lvl.period_start_date <= last_date
AND lvl.period_start_date >= l_bckt_start_date -1
AND lvl.record_type = 1
AND lvl.period_type = 0
AND lvl.sr_instance_id IS NULL
AND lvl.organization_id IS NULL
AND lvl.category_name IS NULL
/*
AND lvl.plan_id = list.number4
AND lvl.inventory_item_id = list.number1
AND list.query_id = item_list_id
*/
AND lvl.sr_instance_id = G_inst_id
AND lvl.plan_id = G_plan_id
AND lvl.inventory_item_id = gpi.inventory_item_id
GROUP BY /* list.number5,list.number6,list.number3, */
gpi.inventory_item_id,
gpi.organization_id,
TARGET_SER_LVL, TARGET_SER_OFF,
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,
*/
gpi.inventory_item_id,
gpi.organization_id,
ACHIEVED_SER_LVL row_type,
ACHIEVED_SER_OFF offset,
lvl.period_start_date new_date,
lvl.period_start_date old_date,
sum(lvl.ACHIEVED_SERVICE_LEVEL) new_quantity,
0 old_quantity,
0 dos,
0
FROM msc_analysis_aggregate lvl,
-- msc_form_query list
gmp_pdr_items_gtmp gpi
WHERE lvl.period_start_date <= last_date
AND lvl.period_start_date >= l_bckt_start_date -1
AND lvl.record_type = 1
AND lvl.period_type = 0
AND lvl.sr_instance_id is null
AND lvl.organization_id is null
AND lvl.category_name is null
/*
AND lvl.plan_id = list.number4
AND lvl.inventory_item_id = list.number1
AND list.query_id = item_list_id
*/
AND lvl.sr_instance_id = G_inst_id
AND lvl.plan_id = G_plan_id
AND lvl.inventory_item_id = gpi.inventory_item_id
GROUP BY /* list.number5,list.number6,list.number3, */
gpi.inventory_item_id,
gpi.organization_id,
ACHIEVED_SER_LVL, ACHIEVED_SER_OFF,
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, 5, 3 ;
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 gpi.calculate_atp
FROM gmp_pdr_items_gtmp gpi
WHERE gpi.inventory_item_id = p_item_id
AND gpi.organization_id = p_org_id;
SELECT plan_type INTO l_plan_type
FROM msc_plans
WHERE plan_id = G_plan_id;
/* nsinghi: insert logic -
1) For the num of days buckets, no issue, insert the txns as it is.
2) Do the looping for days buckets till var_dates(loop_counter) < G_day_bckt_cutoff_dt
3) For each week bucket, get the (next week_start_date - 1). Loop the loop_counter for these many days and
add the supply and demand txns. Insert a row for the week_start_date bucket.
4) The loop runs till var_dates(loop_counter) < G_week_bckt_cutoff_dt
5) For each period bucket, get the (next period_start_date - 1). Loop the loop_counter for these many days and
add the supply and demand txns. Insert a row for the period_start_date bucket.
4) The loop runs till var_dates(loop_counter) <= last_date.
*/
sales_sum := 0;
INSERT INTO gmp_horizontal_pdr_gtmp
(
organization_id,
inventory_item_id,
bucket_date,
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
)
VALUES
(
p_org_id,
p_item_id,
var_dates(loop_counter),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SALES_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + FORECAST_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PROD_FORECAST_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DEPENDENT_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SCRAP_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PB_DEMAND_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + OTHER_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + GROSS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + WIP_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PO_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + REQ_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRANSIT_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + RECEIVING_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PLANNED_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PB_SUPPLY_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SUPPLY_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ON_HAND_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PAB_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ATP_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + CURRENT_S_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + POH_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + EXP_LOT_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MIN_INV_LVL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAX_INV_LVL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TARGET_SER_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ACHIEVED_SER_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + NON_POOL_SS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MANF_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PURC_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRAN_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DMND_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAD_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAPE_OFF)
);
/* Vpedarla 8273098 modified the bucket_cells_tab detial selection from
loop_counter - 1 to loop_counter - 2. Since loop_counter - 1 still points to the current week starting day.
But we need previous week ending day. So, chaning it to loop_counter - 2. */
-- Vpedalra Bug: 8363786 Added the IF condiiton
IF G_week_bckt_cutoff_dt IS NOT NULL THEN
IF var_dates(loop_counter) >= G_day_bckt_cutoff_dt AND
var_dates(loop_counter) <= G_week_bckt_cutoff_dt THEN
-- var_dates(loop_counter) < G_week_bckt_cutoff_dt THEN Bug: 8447261 Vpedarla
/* sum the txns. maintain the week start date. */
next_week_start_date := msc_calendar.next_work_day (-1*G_org_id, G_inst_id,
MSC_CALENDAR.TYPE_WEEKLY_BUCKET, var_dates(loop_counter)+1 );
INSERT INTO gmp_horizontal_pdr_gtmp
(
organization_id,
inventory_item_id,
bucket_date,
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
)
VALUES
(
p_org_id,
p_item_id,
var_dates(prev_week_loop_counter),
sales_sum,
forecast_sum,
prod_forecast_sum,
dependent_sum,
scrap_sum,
pb_demand_sum,
other_sum,
gross_sum,
wip_sum,
po_sum,
req_sum,
transit_sum,
receiving_sum,
planned_sum,
pb_supply,
supply_sum,
on_hand_sum,
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PAB_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ATP_OFF),
current_s_sum,
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + POH_OFF),
exp_lot_sum,
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MIN_INV_LVL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAX_INV_LVL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TARGET_SER_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ACHIEVED_SER_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + NON_POOL_SS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MANF_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PURC_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRAN_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DMND_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAD_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAPE_OFF)
);
/* Vpedarla 8273098 modified the bucket_cells_tab detial selection from
loop_counter - 1 to loop_counter - 2. Since loop_counter - 1 still points to the current period starting day.
But we need previous period ending day. So, chaning it to loop_counter - 2. */
-- Vpedalra Bug: 8363786 Added the IF condiiton
IF G_week_bckt_cutoff_dt IS NOT NULL THEN
IF var_dates(loop_counter) >= G_week_bckt_cutoff_dt AND
var_dates(loop_counter) <= last_date THEN
/* sum the txns. maintain the week start date. */
next_period_start_date := msc_calendar.next_work_day (-1*G_org_id, G_inst_id,
MSC_CALENDAR.TYPE_MONTHLY_BUCKET, var_dates(loop_counter)+1 );
INSERT INTO gmp_horizontal_pdr_gtmp
(
organization_id,
inventory_item_id,
bucket_date,
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
)
VALUES
(
p_org_id,
p_item_id,
var_dates(prev_period_loop_counter),
sales_sum,
forecast_sum,
prod_forecast_sum,
dependent_sum,
scrap_sum,
pb_demand_sum,
other_sum,
gross_sum,
wip_sum,
po_sum,
req_sum,
transit_sum,
receiving_sum,
planned_sum,
pb_supply,
supply_sum,
on_hand_sum,
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PAB_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ATP_OFF),
current_s_sum,
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + POH_OFF),
exp_lot_sum,
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MIN_INV_LVL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAX_INV_LVL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_DOS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_VAL_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TARGET_SER_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ACHIEVED_SER_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + NON_POOL_SS_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MANF_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PURC_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRAN_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DMND_VARI_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAD_OFF),
bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAPE_OFF)
);
SELECT plan_type INTO l_plan_type
FROM msc_plans
WHERE plan_id = G_plan_id;
Here the logic from start of this procedure to the point where data is inserted in GMP_Material_Plans is explained.
1) Initially get the number of days that the horiz report needs to consider based
on the days, weeks and periods entered in the conc window.
2) Each row in GMP_Material_Plans will correspond to one day.
3) PL/SQL Table bucket_cells_tab contains (num of report days) * (num of txns type) num of rows. Thus for each transaction on each day has one row in bucket_cells_tab. Each txn is given an offset as defined by
the constants in the procedure. Thus all multiples of the offset will store information of that type of txns.
4) Every time there is a change in the item, the data is inserted in GMP_Material_plans table.
5) For each activity row, get the txn qty. Insert the qty in bucket_cells_tab. The location of the qty in
bucket_cells_tab will depend on the txn type offset and the day. The bucket day is retrieved as follow:
a) Get the dates for all the days of the report and store the dates in PL/SQL date table var_dates
b) Get the row number from var_dates table where the activity row date < date in var_dates
6) For txn of type safety stock, everytime a safety stock activity is retrieved, associate that safety
stock to all the days. This is cause, same safety stock is valid for all the days. If a new safety stock
activity row is later retrieved, replace the new safety stock for all the days after the bucket day
of retrieving the safety stock.
7) Same thing is true for some of the other txns like Manufacturing variation, Demand variation,
Purchase variation. Do not know what all these txns mean.
8) But it is not true for txns like Sales Order, Forecast, Planned Order etc. Obviously, these txns
are only for that specific bucket day and not for all the days after the bucket.
*/
-- bug: 9366921
if l_debug = 'Y' THEN
activity_rec_count := 1 ;
activity_rec_tab.delete ;
altogether. For bucket 10, the safety stock would already have been inserted. Bucket counter will now be
at 10. Now when we get another txn for the item at bucket 14, we start moving bucket counter forward.
But before we move forward, we need to insert safety stock value for buckets 11,12,13 and 14. Inserting
safety stock for bucket 11,12,13 and 14 taken care by code below. Once after txn for item at bucket 14
is inserted and the item changes, the code above will insert the safety stock from bucket 14 to 30.
Whenever safety stock is mentioned, it means any of the txn of safety stock type like SS_OFF, SSunc_OFF,
NON_POOL_SS_OFF etc which are valid for each day.
*/
IF prev_ss_quantity <> -1 THEN
add_to_plan(bucket_counter -1,
SS_OFF,
prev_ss_quantity);