[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT primary_item_id
FROM wip_flow_schedules
WHERE request_id = USERENV('SESSIONID')
AND organization_id = p_org_id
AND wip_entity_id >= G_WIP_ENTITY_ID;
SELECT DISTINCT schedule_group_id schedule_group
FROM wip_flow_schedules
WHERE request_id = USERENV('SESSIONID')
AND organization_id = p_org_id
AND wip_entity_id >= G_WIP_ENTITY_ID;
and routing_revision_date to be selected by the sursor. */
CURSOR fs_list (p_line_id NUMBER, p_org_id NUMBER) IS
SELECT wip_entity_id, schedule_group_id, scheduled_completion_date,
primary_item_id, bom_revision_date, routing_revision_date
FROM wip_flow_schedules
WHERE request_id = USERENV('SESSIONID')
AND line_id = p_line_id
AND organization_id = p_org_id
AND scheduled_flag = C_YES
AND wip_entity_id >= G_WIP_ENTITY_ID
ORDER BY scheduled_completion_date;
SELECT number1, number2,number3
FROM mrp_form_query
WHERE date1 = to_date(p_date,'J')
AND query_id = p_query_id
ORDER BY number3 DESC;
SELECT wip_entity_id,planned_quantity,quantity_completed,
schedule_group_id,build_sequence,primary_item_id
FROM wip_flow_schedules
WHERE to_number(to_char(scheduled_completion_date,'J'))
= p_date
AND line_id = p_line_id
AND organization_id = p_org_id
AND scheduled_flag = C_YES
ORDER BY schedule_group_id,build_sequence;
v_select VARCHAR2(5000);
fs_select_rec fs_select_type;
v_select := 'SELECT '||
' fs.wip_entity_id wip_entity, '||
' sol.creation_date creation_date, '||
' NVL(sol.schedule_ship_date,fs.scheduled_completion_date) schedule_date,'||
' sol.promise_date promise_date, '||
' sol.request_date request_date, '||
' sol.planning_priority planning_priority, '||
' fs.primary_item_id primary_item_id, '||
' fs.planned_quantity planned_quantity, '||
' fs.schedule_group_id schedule_group_id '||
' FROM oe_order_lines_all sol,wip_flow_schedules fs '||
' WHERE fs.request_id = :v_session_id '||
' AND fs.organization_id = :p_org_id '||
' AND fs.line_id = :p_line_id '||
' AND sol.line_id(+) = fs.demand_source_line '||
' AND fs.scheduled_flag = 3 '||
' AND :p_item_id > 0 ';
v_select := v_select || ' AND fs.primary_item_id = :p_item_id ';
v_select := v_select || ' ORDER BY '|| v_seq_criteria;
dbms_sql.parse(cursor_name, v_select, dbms_sql.v7);
dbms_sql.define_column(cursor_name,1, fs_select_rec.wip_entity);
dbms_sql.define_column(cursor_name,2, fs_select_rec.creation_date);
dbms_sql.define_column(cursor_name,3, fs_select_rec.schedule_date);
dbms_sql.define_column(cursor_name,4, fs_select_rec.promise_date);
dbms_sql.define_column(cursor_name,5, fs_select_rec.request_date);
dbms_sql.define_column(cursor_name,6, fs_select_rec.planning_priority);
dbms_sql.define_column(cursor_name,7, fs_select_rec.primary_item_id);
dbms_sql.define_column(cursor_name,8, fs_select_rec.planned_quantity);
dbms_sql.define_column(cursor_name,9, fs_select_rec.schedule_group_id);
select nvl(max(FENCE_DAYS),-1)
into v_fence_days
from bom_resource_flex_fences
where fence_days <= v_days_out
and department_id = p_line_id;
select max(tolerance_percentage)
into v_tol_percent
from bom_resource_flex_fences
where fence_days = v_fence_days
and department_id = p_line_id;
SELECT calendar_code,calendar_exception_set_id
INTO v_cal_code,v_excep_code
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT maximum_rate, start_time, stop_time
INTO v_hr_rate, v_line_start_time, v_line_stop_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
select sum(planned_quantity)
into v_qty_to_sched
from wip_flow_schedules
where request_id = USERENV('SESSIONID')
and line_id = p_line_id
and organization_id = p_org_id
and scheduled_flag = C_NO
and wip_entity_id >= G_WIP_ENTITY_ID;
SELECT NVL(SUM(NVL(start_quantity,0)
- NVL(quantity_completed,0)
- NVL(quantity_scrapped,0)),0)
INTO v_sum_dj
FROM wip_discrete_jobs
WHERE line_id = p_line_id
AND organization_id = p_org_id
--fix bug#3783650
--AND to_number(to_char(scheduled_completion_date,'J'))
-- = v_current_date;
SELECT NVL(SUM(NVL(planned_quantity,0)
-NVL(quantity_completed,0)),0)
INTO v_sum_fs
FROM wip_flow_schedules
WHERE line_id = p_line_id
AND organization_id = p_org_id
--fix bug#3783650
--AND to_number(to_char(scheduled_completion_date,'J'))
-- = v_current_date
AND scheduled_completion_date BETWEEN v_current_date_in_client00
AND v_current_date_in_client00+1-(1/86400)
AND scheduled_flag = C_YES;
SELECT NVL(SUM(NVL(MRP_HORIZONTAL_PLAN_SC.compute_daily_rate_t(
v_cal_code,
v_excep_code,
wip_repetitive_schedules.daily_production_rate,
wip_repetitive_schedules.quantity_completed,
wip_repetitive_schedules.first_unit_completion_date,
to_date(v_current_date,'J')),0)),0)
INTO v_sum_rs
FROM wip_repetitive_schedules
WHERE line_id = p_line_id
AND organization_id = p_org_id
--fix bug#3783650
--AND v_current_date BETWEEN
-- to_number(to_char(first_unit_completion_date,'J'))
--AND to_number(to_char(last_unit_completion_date,'J'));
CURSOR criteria_select IS
SELECT usage_code
FROM mrp_scheduling_rules
WHERE rule_id = p_rule_id
AND NVL(user_defined,C_USER_DEFINE_NO) = C_USER_DEFINE_NO
ORDER BY sequence_number;
SELECT demand_source_type
INTO v_source_type
FROM wip_flow_schedules
WHERE request_id = USERENV('SESSIONID')
AND scheduled_flag = C_NO
AND rownum = 1
AND wip_entity_id >= G_WIP_ENTITY_ID;
FOR criteria_select_rec IN criteria_select LOOP
IF i > 1 THEN
v_ordered_criteria := v_ordered_criteria ||',';
IF criteria_select_rec.usage_code = 1 THEN
v_ordered_criteria := v_ordered_criteria || 'sol.request_date';
ELSIF criteria_select_rec.usage_code = 2 THEN
v_ordered_criteria := v_ordered_criteria || 'sol.schedule_ship_date';
ELSIF criteria_select_rec.usage_code = 3 THEN
v_ordered_criteria := v_ordered_criteria || 'sol.promise_date';
ELSIF criteria_select_rec.usage_code = 4 THEN
v_ordered_criteria := v_ordered_criteria || 'sol.creation_date';
ELSIF criteria_select_rec.usage_code = 5 THEN
v_ordered_criteria := v_ordered_criteria || 'sol.planning_priority';
ELSIF criteria_select_rec.usage_code = 7 THEN
v_ordered_criteria := v_ordered_criteria || 'sol.cust_production_seq_num';
ELSIF criteria_select_rec.usage_code = 8 THEN
v_ordered_criteria := v_ordered_criteria || 'sol.cust_production_seq_num desc';
SELECT NVL(fixed_order_quantity,0), NVL(fixed_lot_multiplier,0),
NVL(minimum_order_quantity,0), NVL(maximum_order_quantity,0)
INTO v_fixed_qty, v_lot_multiple, v_min_qty, v_max_qty
FROM mtl_system_items
WHERE inventory_item_id = item_list_rec.primary_item_id
AND organization_id = p_org_id;
SELECT NVL(MAX(build_sequence),0)
INTO p_build_seq_tab(schedule_group_rec.schedule_group).buildseq
FROM wip_flow_schedules fs
WHERE fs.schedule_group_id = schedule_group_rec.schedule_group
AND fs.line_id = p_line_id
AND fs.organization_id = p_org_id
AND scheduled_flag = C_YES;
SELECT NVL(MAX(build_sequence),0)
INTO v_null_build_seq
FROM wip_flow_schedules fs
WHERE fs.schedule_group_id IS NULL
AND fs.line_id = p_line_id
AND fs.organization_id = p_org_id
AND scheduled_flag = C_YES;
SELECT to_number(to_char(bom_cal.calendar_date,'J')) workday
FROM bom_calendar_dates bom_cal
WHERE bom_cal.calendar_code = l_cal_code
AND bom_cal.exception_set_id = l_excep_code
AND bom_cal.calendar_date between to_date(l_schedule_start_date,'j') and
to_date(l_schedule_end_date,'j')
-- AND to_number(to_char(bom_cal.calendar_date,'J'))
-- between l_schedule_start_date and l_schedule_end_date
AND bom_cal.seq_num is NOT NULL
ORDER BY bom_cal.calendar_date;
SELECT maximum_rate, start_time, stop_time
INTO v_hr_line_rate, v_start_time, v_end_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
SELECT calendar_code,calendar_exception_set_id
INTO l_cal_code,l_excep_code
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT max(scheduled_completion_date)
INTO l_last_comp_date
FROM wip_flow_schedules
WHERE scheduled_completion_date >= to_date(v_date,'J')+v_start_time/86400
AND scheduled_completion_date <= to_date(v_date,'J')+v_end_time/86400
AND line_id = p_line_id
AND organization_id = p_org_id
AND scheduled_flag = C_YES;
SELECT NVL(rounding_control_type,2)
INTO p_demand_tab(item_list_rec.primary_item_id).roundType
FROM mtl_system_items
WHERE inventory_item_id = item_list_rec.primary_item_id
AND organization_id = p_org_id;
SELECT NVL(SUM(NVL(planned_quantity,0)
-NVL(quantity_completed,0)),0)
INTO p_demand_tab(item_list_rec.primary_item_id).totalDemand
FROM wip_flow_schedules fs
WHERE fs.request_id = USERENV('SESSIONID')
AND fs.primary_item_id = item_list_rec.primary_item_id
AND fs.line_id = p_line_id
AND fs.organization_id = p_org_id
AND fs.scheduled_flag = C_NO
AND wip_entity_id >= G_WIP_ENTITY_ID;
SELECT primary_item_id, SUM(TRUNC(NVL(planned_quantity,0)
-NVL(quantity_completed,0)-0.00000001)+1) quantity
FROM wip_flow_schedules fs
WHERE fs.request_id = USERENV('SESSIONID')
AND fs.line_id = p_line_id
AND fs.organization_id = p_org_id
AND fs.scheduled_flag = C_NO
AND wip_entity_id >= G_WIP_ENTITY_ID
GROUP BY primary_item_id
ORDER BY quantity;
SELECT NVL(fixed_lead_time, 0), NVL(variable_lead_time, 0)
INTO v_fixed_lead_time, v_var_lead_time
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = v_item_id
AND organization_id = p_org_id;
fs_select_rec fs_select_type;
dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
dbms_sql.column_value(cursor_name,9, fs_select_rec.schedule_group_id);
IF v_last_wip = fs_select_rec.wip_entity THEN
EXIT;
v_last_wip := fs_select_rec.wip_entity;
IF v_itemQty_tab.EXISTS(fs_select_rec.primary_item_id) = FALSE THEN
v_itemQty_tab(fs_select_rec.primary_item_id).remainQty := 0;
IF v_itemQty_tab(fs_select_rec.primary_item_id).remainQty >=
fs_select_rec.planned_quantity THEN
v_itemQty_tab(fs_select_rec.primary_item_id).remainQty :=
v_itemQty_tab(fs_select_rec.primary_item_id).remainQty - fs_select_rec.planned_quantity;
ELSIF v_itemQty_tab(fs_select_rec.primary_item_id).remainQty <
fs_select_rec.planned_quantity THEN
-- dbms_output.put_line('NOT enough quantity remaining for allocation.');
IF p_orderMod_tab(fs_select_rec.primary_item_id).minVal = 0 AND
p_orderMod_tab(fs_select_rec.primary_item_id).maxVal = 0 THEN
-- dbms_output.put_line('no meaningful values for min and max values!!');
v_order_quantity := fs_select_rec.planned_quantity;
ELSIF p_orderMod_tab(fs_select_rec.primary_item_id).minVal > 0 THEN
-- dbms_output.put_line('minimum value exists and = order quantity!!');
v_order_quantity := p_orderMod_tab(fs_select_rec.primary_item_id).minVal;
SELECT TRUNC((fs_select_rec.planned_quantity -
v_itemQty_tab(fs_select_rec.primary_item_id).remainQty)/
v_order_quantity-0.000000001)+1
INTO v_num_flow
FROM DUAL;
UPDATE wip_flow_schedules
SET planned_quantity =
v_itemQty_tab(fs_select_rec.primary_item_id).remainQty
WHERE wip_entity_id = fs_select_rec.wip_entity
AND organization_id = p_org_id;
to_schedule_qty := fs_select_rec.planned_quantity -
v_itemQty_tab(fs_select_rec.primary_item_id).remainQty;
SELECT wip_entities_s.nextval
INTO v_temp
FROM dual;
INSERT INTO wip_flow_schedules(
scheduled_flag,
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
class_code,
line_id,
primary_item_id,
scheduled_start_date,
planned_quantity,
quantity_completed,
quantity_scrapped,
scheduled_completion_date,
schedule_group_id,
status,
schedule_number,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
demand_source_type,
project_id,
task_id,
end_item_unit_number,
request_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
std_cost_adjustment_account,
overhead_account,
overhead_variance_account,
bom_revision, /* Added for bug 2185087 */
routing_revision,
bom_revision_date,
routing_revision_date,
alternate_bom_designator,
alternate_routing_designator,
completion_subinventory,
completion_locator_id,
demand_class,
attribute_category,
kanban_card_id)
SELECT C_NO,
v_temp,
p_org_id,
SYSDATE,
fs.last_updated_by,
SYSDATE,
fs.created_by,
fs.class_code,
fs.line_id,
fs.primary_item_id,
fs.scheduled_completion_date,
v_planned_quantity,
0,
0,
fs.scheduled_completion_date,
fs.schedule_group_id,
fs.status,
v_schedule_number,
fs.demand_source_header_id,
fs.demand_source_line,
fs.demand_source_delivery,
fs.demand_source_type,
fs.project_id,
fs.task_id,
fs.end_item_unit_number,
USERENV('SESSIONID'),
fs.attribute1,
fs.attribute2,
fs.attribute3,
fs.attribute4,
fs.attribute5,
fs.attribute6,
fs.attribute7,
fs.attribute8,
fs.attribute9,
fs.attribute10,
fs.attribute11,
fs.attribute12,
fs.attribute13,
fs.attribute14,
fs.attribute15,
fs.material_account,
fs.material_overhead_account,
fs.resource_account,
fs.outside_processing_account,
fs.material_variance_account,
fs.resource_variance_account,
fs.outside_proc_variance_account,
fs.std_cost_adjustment_account,
fs.overhead_account,
fs.overhead_variance_account,
fs.bom_revision, /* Added for bug 2185087 */
fs.routing_revision,
fs.bom_revision_date,
fs.routing_revision_date,
fs.alternate_bom_designator,
fs.alternate_routing_designator,
fs.completion_subinventory,
fs.completion_locator_id,
fs.demand_class,
fs.attribute_category,
fs.kanban_card_id
FROM wip_flow_schedules fs
WHERE fs.wip_entity_id = fs_select_rec.wip_entity
AND line_id = p_line_id
AND organization_id = p_org_id;
v_itemQty_tab(fs_select_rec.primary_item_id).remainQty :=
v_order_quantity - v_planned_quantity;
v_itemQty_tab(fs_select_rec.primary_item_id).wip_id := fs_select_rec.wip_entity;
SELECT wip_entities_s.nextval
INTO v_temp
FROM dual;
INSERT INTO wip_flow_schedules(
scheduled_flag,
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
class_code,
line_id,
primary_item_id,
scheduled_start_date,
planned_quantity,
quantity_completed,
quantity_scrapped,
scheduled_completion_date,
schedule_group_id,
status,
schedule_number,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
demand_source_type,
project_id,
task_id,
end_item_unit_number,
request_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
std_cost_adjustment_account,
overhead_account,
overhead_variance_account,
bom_revision, /* Added for bug 2185087 */
routing_revision,
bom_revision_date,
routing_revision_date,
alternate_bom_designator,
alternate_routing_designator,
completion_subinventory,
completion_locator_id,
demand_class,
attribute_category,
kanban_card_id)
SELECT C_NO,
v_temp,
p_org_id,
SYSDATE,
fs.last_updated_by,
SYSDATE,
fs.created_by,
fs.class_code,
fs.line_id,
fs.primary_item_id,
fs.scheduled_completion_date,
v_itemQty_tab(item_list_rec.primary_item_id).remainQty,
0,
0,
fs.scheduled_completion_date,
fs.schedule_group_id,
fs.status,
v_schedule_number,
fs.demand_source_header_id,
fs.demand_source_line,
fs.demand_source_delivery,
fs.demand_source_type,
fs.project_id,
fs.task_id,
fs.end_item_unit_number,
USERENV('SESSIONID'),
fs.attribute1,
fs.attribute2,
fs.attribute3,
fs.attribute4,
fs.attribute5,
fs.attribute6,
fs.attribute7,
fs.attribute8,
fs.attribute9,
fs.attribute10,
fs.attribute11,
fs.attribute12,
fs.attribute13,
fs.attribute14,
fs.attribute15,
fs.material_account,
fs.material_overhead_account,
fs.resource_account,
fs.outside_processing_account,
fs.material_variance_account,
fs.resource_variance_account,
fs.outside_proc_variance_account,
fs.std_cost_adjustment_account,
fs.overhead_account,
fs.overhead_variance_account,
fs.bom_revision, /* Added for bug 2185087 */
fs.routing_revision,
fs.bom_revision_date,
fs.routing_revision_date,
fs.alternate_bom_designator,
fs.alternate_routing_designator,
fs.completion_subinventory,
fs.completion_locator_id,
fs.demand_class,
fs.attribute_category,
fs.kanban_card_id
FROM wip_flow_schedules fs
WHERE fs.wip_entity_id = v_itemQty_tab(item_list_rec.primary_item_id).wip_id
AND line_id = p_line_id
AND organization_id = p_org_id;
DELETE
FROM wip_flow_schedules
WHERE planned_quantity = 0
AND line_id = p_line_id
AND organization_id = p_org_id;
fs_select_rec fs_select_type;
dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
IF v_last_wip = fs_select_rec.wip_entity THEN
EXIT;
v_round_qty := TRUNC(fs_select_rec.planned_quantity-0.0000000001) + 1;
IF v_round_qty > fs_select_rec.planned_quantity THEN
v_round_total := v_round_total +
(v_round_qty - fs_select_rec.planned_quantity);
UPDATE wip_flow_schedules fs
SET planned_quantity = v_round_qty
WHERE fs.wip_entity_id = fs_select_rec.wip_entity
AND organization_id = p_org_id;
v_last_wip := fs_select_rec.wip_entity;
dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
IF v_last_wip = fs_select_rec.wip_entity THEN
EXIT;
IF fs_select_rec.planned_quantity > v_round_total THEN
V_ERROR_LINE := 7;
UPDATE wip_flow_schedules fs
SET planned_quantity = fs_select_rec.planned_quantity -
v_round_total
WHERE fs.wip_entity_id = fs_select_rec.wip_entity
AND organization_id = p_org_id;
v_round_total := v_round_total - fs_select_rec.planned_quantity;
DELETE
FROM wip_flow_schedules
WHERE wip_entity_id = fs_select_rec.wip_entity
AND organization_id = p_org_id;
v_last_wip := fs_select_rec.wip_entity;
fs_select_rec fs_select_type;
SELECT NVL(MAX(build_sequence),0)
INTO v_null_build_seq
FROM wip_flow_schedules fs
WHERE fs.schedule_group_id IS NULL
AND fs.line_id = p_line_id
AND fs.organization_id = p_org_id
AND scheduled_flag = C_YES;
SELECT maximum_rate, start_time, stop_time
INTO v_hr_line_rate, v_start_time, v_end_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
dbms_sql.column_value(cursor_name,2, fs_select_rec.creation_date);
dbms_sql.column_value(cursor_name,3, fs_select_rec.schedule_date);
dbms_sql.column_value(cursor_name,4, fs_select_rec.promise_date);
dbms_sql.column_value(cursor_name,5, fs_select_rec.request_date);
dbms_sql.column_value(cursor_name,6, fs_select_rec.planning_priority);
dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
dbms_sql.column_value(cursor_name,9, fs_select_rec.schedule_group_id);
SELECT NVL(fixed_lead_time, 0), NVL(variable_lead_time, 0)
INTO l_fixed_lead_time, l_variable_lead_time
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = fs_select_rec.primary_item_id
AND organization_id = p_org_id;
IF v_last_wip = fs_select_rec.wip_entity THEN
RETURN;
v_last_wip := fs_select_rec.wip_entity;
v_remain_qty := fs_select_rec.planned_quantity;
v_current_wip := fs_select_rec.wip_entity;
select scheduled_completion_date, demand_source_line
into date_temp, trans_temp
from wip_flow_schedules
where wip_entity_id = v_current_wip
and organization_id = p_org_id;
IF fs_select_rec.schedule_group_id IS NOT NULL THEN
v_build_seq :=
v_build_seq_tab(fs_select_rec.schedule_group_id).buildseq + 1;
v_build_seq_tab(fs_select_rec.schedule_group_id).buildseq := v_build_seq;
SELECT start_time
INTO v_final_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
UPDATE wip_flow_schedules
SET scheduled_start_date = v_begin_time,
scheduled_completion_date = v_completion_time,
scheduled_flag = C_YES,
build_sequence = v_build_seq
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id;
select planned_quantity
into qty_temp
from wip_flow_schedules
where wip_entity_id = v_current_wip
and organization_id = p_org_id;
SELECT primary_item_id
INTO v_current_item
FROM wip_flow_schedules
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id;
SELECT start_time
INTO v_final_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
UPDATE wip_flow_schedules
SET scheduled_start_date = v_begin_time,
scheduled_completion_date = v_completion_time,
planned_quantity = v_current_cap,
scheduled_flag = C_YES,
build_sequence = v_build_seq
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id;
select planned_quantity
into qty_temp
from wip_flow_schedules
where wip_entity_id = v_current_wip
and organization_id = p_org_id;
SELECT wip_entities_s.nextval
INTO v_temp
FROM dual;
INSERT INTO wip_flow_schedules(
scheduled_flag,
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
class_code,
line_id,
primary_item_id,
scheduled_start_date,
planned_quantity,
quantity_completed,
quantity_scrapped,
scheduled_completion_date,
schedule_group_id,
status,
schedule_number,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
demand_source_type,
project_id,
task_id,
end_item_unit_number,
request_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
std_cost_adjustment_account,
overhead_account,
overhead_variance_account,
bom_revision, /* Added for bug 2185087 */
routing_revision,
bom_revision_date,
routing_revision_date,
alternate_bom_designator,
alternate_routing_designator,
completion_subinventory,
completion_locator_id,
demand_class,
attribute_category,
kanban_card_id)
SELECT C_NO,
v_temp,
p_org_id,
SYSDATE,
fs.last_updated_by,
SYSDATE,
fs.created_by,
fs.class_code,
fs.line_id,
fs.primary_item_id,
to_date(v_current_date,'J'),
v_remain_qty - v_current_cap,
0,
0,
to_date(v_current_date,'J'),
fs.schedule_group_id,
fs.status,
v_schedule_number,
fs.demand_source_header_id,
fs.demand_source_line,
fs.demand_source_delivery,
fs.demand_source_type,
fs.project_id,
fs.task_id,
fs.end_item_unit_number,
USERENV('SESSIONID'),
fs.attribute1,
fs.attribute2,
fs.attribute3,
fs.attribute4,
fs.attribute5,
fs.attribute6,
fs.attribute7,
fs.attribute8,
fs.attribute9,
fs.attribute10,
fs.attribute11,
fs.attribute12,
fs.attribute13,
fs.attribute14,
fs.attribute15,
fs.material_account,
fs.material_overhead_account,
fs.resource_account,
fs.outside_processing_account,
fs.material_variance_account,
fs.resource_variance_account,
fs.outside_proc_variance_account,
fs.std_cost_adjustment_account,
fs.overhead_account,
fs.overhead_variance_account,
fs.bom_revision, /* Added for bug 2185087 */
fs.routing_revision,
fs.bom_revision_date,
fs.routing_revision_date,
fs.alternate_bom_designator,
fs.alternate_routing_designator,
fs.completion_subinventory,
fs.completion_locator_id,
fs.demand_class,
fs.attribute_category,
fs.kanban_card_id
FROM wip_flow_schedules fs
WHERE fs.wip_entity_id = fs_select_rec.wip_entity
AND organization_id = p_org_id;
SELECT number1,number2
FROM mrp_form_query mfq, mtl_system_items mtl
WHERE mfq.date1 = to_date(v_current_date,'J')
AND mfq.query_id = v_query_id
AND mfq.number1 = mtl.inventory_item_id
AND NVL(mtl.rounding_control_type,2) <> C_ROUND_TYPE
AND mtl.organization_id = p_org_id
ORDER BY number2;
SELECT mrp_form_query_s.nextval
INTO v_query_id
FROM DUAL;
INSERT INTO MRP_FORM_QUERY (
QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DATE1,
NUMBER1,
NUMBER2,
NUMBER3)
VALUES (
v_query_id,
SYSDATE,
1,
SYSDATE,
1,
to_date(v_current_date,'J'),
item_list_rec.primary_item_id,
TRUNC(v_production),
v_production - TRUNC(v_production));
UPDATE MRP_FORM_QUERY
SET number2 = v_production,
number3 = 0
WHERE number1 = item_list_rec.primary_item_id
AND query_id = v_query_id
AND date1 = to_date(v_current_date,'J');
SELECT SUM(number2)
INTO v_trunc_sum
FROM mrp_form_query
WHERE query_id = v_query_id
AND date1 = to_date(v_current_date,'J');
UPDATE mrp_form_query
SET number2 = v_item_round_qty + v_cap_diff
WHERE number1 = v_item_round
AND date1 = to_date(v_current_date,'J')
AND query_id = v_query_id;
UPDATE mrp_form_query
SET number2 =(SELECT number2 +1
FROM mrp_form_query
WHERE date1 = to_date(v_current_date,'J')
AND query_id = v_query_id
AND number1 = production_round_rec.number1)
WHERE date1 = to_date(v_current_date,'J')
AND query_id = v_query_id
AND number1 = production_round_rec.number1;
SELECT number2
INTO v_decrement_qty
FROM mrp_form_query
WHERE query_id = v_query_id
AND number1 = item_list_rec.primary_item_id
AND date1 = to_date(v_current_date,'J');
INSERT INTO MRP_FORM_QUERY (
QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DATE1,
NUMBER1,
NUMBER2,
NUMBER3)
VALUES (
v_query_id,
SYSDATE,
1,
SYSDATE,
1,
to_date(v_current_date,'J'),
item_list_rec.primary_item_id,
0,
0);
fs_select_rec fs_select_type;
SELECT maximum_rate, start_time, stop_time
INTO v_hr_line_rate, v_start_time, v_end_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
UPDATE mrp_form_query
SET number4 = C_DATE_ON
WHERE query_id = v_query_id
AND date1 = to_date(v_current_date,'J');
dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
dbms_sql.column_value(cursor_name,2, fs_select_rec.creation_date);
dbms_sql.column_value(cursor_name,3, fs_select_rec.schedule_date);
dbms_sql.column_value(cursor_name,4, fs_select_rec.promise_date);
dbms_sql.column_value(cursor_name,5, fs_select_rec.request_date);
dbms_sql.column_value(cursor_name,6, fs_select_rec.planning_priority);
dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
dbms_sql.column_value(cursor_name,9, fs_select_rec.schedule_group_id);
SELECT NVL(fixed_lead_time, 0), NVL(variable_lead_time, 0)
INTO l_fixed_lead_time, l_variable_lead_time
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = fs_select_rec.primary_item_id
AND organization_id = p_org_id;
IF v_last_wip = fs_select_rec.wip_entity THEN
EXIT;
v_last_wip := fs_select_rec.wip_entity;
SELECT number2, to_number(to_char(date1,'J'))
INTO v_alloc_qty, v_current_date
FROM mrp_form_query
WHERE query_id = v_query_id
AND number1 = fs_select_rec.primary_item_id
AND number4 = C_DATE_ON;
v_remain_qty := fs_select_rec.planned_quantity;
v_current_wip := fs_select_rec.wip_entity;
SELECT number5
INTO v_finish_flag
FROM mrp_form_query
WHERE number1 = fs_select_rec.primary_item_id
AND number4 = C_DATE_ON
AND query_id = v_query_id;
SELECT number5
INTO v_finish_flag
FROM mrp_form_query
WHERE number1 = fs_select_rec.primary_item_id
AND number4 = C_DATE_ON
AND query_id = v_query_id;
UPDATE mrp_form_query
SET number5 = C_COMPLETE
WHERE number4 = C_DATE_ON
AND query_id = v_query_id
AND number1 = fs_select_rec.primary_item_id;
UPDATE mrp_form_query
SET number4 = NULL
WHERE date1 = to_date(v_current_date,'J')
AND query_id = v_query_id
AND number1 = fs_select_rec.primary_item_id;
UPDATE mrp_form_query
SET number4 = C_DATE_ON
WHERE date1 = to_date(v_current_date,'J')
AND query_id = v_query_id
AND number1 = fs_select_rec.primary_item_id;
SELECT number2
INTO v_alloc_qty
FROM mrp_form_query
WHERE query_id = v_query_id
AND number1 = fs_select_rec.primary_item_id
AND number4 = C_DATE_ON;
SELECT number5
INTO v_finish_flag
FROM mrp_form_query
WHERE number1 = fs_select_rec.primary_item_id
AND number4 = C_DATE_ON
AND query_id = v_query_id;
SELECT start_time
INTO v_final_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
UPDATE wip_flow_schedules
SET scheduled_start_date = v_begin_time,
scheduled_completion_date = v_completion_time,
scheduled_flag = C_YES
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id;
UPDATE mrp_form_query
SET number2 = (SELECT number2 - v_remain_qty
FROM mrp_form_query
WHERE date1 = to_date(v_current_date,'J')
AND query_id = v_query_id
AND number1 = fs_select_rec.primary_item_id)
WHERE date1 = to_date(v_current_date,'J')
AND query_id = v_query_id
AND number1 = fs_select_rec.primary_item_id;
UPDATE wip_flow_schedules
SET scheduled_start_date = v_begin_time,
scheduled_completion_date = v_completion_time,
planned_quantity = v_alloc_qty,
scheduled_flag = C_YES
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id;
SELECT wip_entities_s.nextval
INTO v_temp
FROM dual;
INSERT INTO wip_flow_schedules(
scheduled_flag,
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
class_code,
line_id,
primary_item_id,
scheduled_start_date,
planned_quantity,
quantity_completed,
quantity_scrapped,
scheduled_completion_date,
schedule_group_id,
status,
schedule_number,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
demand_source_type,
project_id,
task_id,
end_item_unit_number,
request_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
std_cost_adjustment_account,
overhead_account,
overhead_variance_account,
bom_revision, /* Added for bug 2185087 */
routing_revision,
bom_revision_date,
routing_revision_date,
alternate_bom_designator,
alternate_routing_designator,
completion_subinventory,
completion_locator_id,
demand_class,
attribute_category,
kanban_card_id)
SELECT C_NO,
v_temp,
p_org_id,
SYSDATE,
fs.last_updated_by,
SYSDATE,
fs.created_by,
fs.class_code,
fs.line_id,
fs.primary_item_id,
to_date(v_current_date,'J'),
v_remain_qty - v_alloc_qty,
0,
0,
to_date(v_current_date,'J'),
fs.schedule_group_id,
fs.status,
v_schedule_number,
fs.demand_source_header_id,
fs.demand_source_line,
fs.demand_source_delivery,
fs.demand_source_type,
fs.project_id,
fs.task_id,
fs.end_item_unit_number,
USERENV('SESSIONID'),
fs.attribute1,
fs.attribute2,
fs.attribute3,
fs.attribute4,
fs.attribute5,
fs.attribute6,
fs.attribute7,
fs.attribute8,
fs.attribute9,
fs.attribute10,
fs.attribute11,
fs.attribute12,
fs.attribute13,
fs.attribute14,
fs.attribute15,
fs.material_account,
fs.material_overhead_account,
fs.resource_account,
fs.outside_processing_account,
fs.material_variance_account,
fs.resource_variance_account,
fs.outside_proc_variance_account,
fs.std_cost_adjustment_account,
fs.overhead_account,
fs.overhead_variance_account,
fs.bom_revision, /* added for bug 2185087 */
fs.routing_revision,
fs.bom_revision_date,
fs.routing_revision_date,
fs.alternate_bom_designator,
fs.alternate_routing_designator,
fs.completion_subinventory,
fs.completion_locator_id,
fs.demand_class,
fs.attribute_category,
fs.kanban_card_id
FROM wip_flow_schedules fs
WHERE fs.wip_entity_id = fs_select_rec.wip_entity
AND organization_id = p_org_id;
PROCEDURE update_buildseq(
p_line_id IN NUMBER,
p_org_id IN NUMBER)
IS
v_build_seq NUMBER;
V_PROCEDURE_NAME := 'Update_Buildseq';
SELECT NVL(MAX(build_sequence),0)
INTO v_null_build_seq
FROM wip_flow_schedules fs
WHERE fs.schedule_group_id IS NULL
AND fs.line_id = p_line_id
AND fs.organization_id = p_org_id
AND scheduled_flag = C_YES;
UPDATE wip_flow_schedules
SET build_sequence = v_build_seq
WHERE wip_entity_id = fs_list_rec.wip_entity_id
AND organization_id = p_org_id;
END update_buildseq;
fs_select_rec fs_select_type;
v_update_date DATE;
v_updated_by NUMBER;
SELECT maximum_rate, start_time, stop_time
INTO v_hr_line_rate, v_start_time, v_end_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
dbms_sql.column_value(cursor_name,1, fs_select_rec.wip_entity);
dbms_sql.column_value(cursor_name,6, fs_select_rec.planning_priority);
dbms_sql.column_value(cursor_name,7, fs_select_rec.primary_item_id);
dbms_sql.column_value(cursor_name,8, fs_select_rec.planned_quantity);
dbms_sql.column_value(cursor_name,9, fs_select_rec.schedule_group_id);
IF v_last_wip = fs_select_rec.wip_entity THEN
EXIT;
v_last_wip := fs_select_rec.wip_entity;
p_demand_tab(fs_select_rec.primary_item_id).sequence + 1;
p_demand_tab(fs_select_rec.primary_item_id).sequence := v_sequence;
UPDATE wip_flow_schedules
SET build_sequence = v_sequence
WHERE wip_entity_id = fs_select_rec.wip_entity
AND organization_id = p_org_id;
SELECT NVL(MAX(build_sequence),0)
INTO v_null_build_seq
FROM wip_flow_schedules fs
WHERE fs.schedule_group_id IS NULL
AND fs.line_id = p_line_id
AND fs.organization_id = p_org_id
AND scheduled_flag = C_YES;
SELECT wip_entity_id,planned_quantity,schedule_group_id
INTO v_current_wip,v_planned_quantity,v_schedule_group
FROM wip_flow_schedules
WHERE primary_item_id = p_item_demand_tab(v_current_num).item
AND organization_id = p_org_id
AND line_id = p_line_id
AND scheduled_flag = C_NO
AND build_sequence = v_sequence
AND request_id = USERENV('SESSIONID')
AND rownum = 1
AND wip_entity_id >= G_WIP_ENTITY_ID;
SELECT start_time
INTO v_final_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
SELECT last_update_date, last_updated_by, creation_date, created_by,
class_code, status, schedule_number, demand_source_header_id,
demand_source_line, demand_source_delivery, demand_source_type,
project_id, task_id, end_item_unit_number, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
material_account, material_overhead_account, resource_account,
outside_processing_account, material_variance_account, resource_variance_account,
outside_proc_variance_account, std_cost_adjustment_account, overhead_account,
overhead_variance_account,bom_revision,routing_revision,/*Added for bug2185087*/
bom_revision_date, routing_revision_date,
alternate_bom_designator, alternate_routing_designator,
completion_subinventory,completion_locator_id,
demand_class, attribute_category, kanban_card_id
INTO v_update_date, v_updated_by, v_creation_date, v_created_by,
v_class_code, v_status, v_schedule, v_header_id, v_source_line,
v_source_delivery, v_source_type, v_project_id, v_task_id, v_end_item_unit_number, v_attribute1,
v_attribute2, v_attribute3, v_attribute4,
v_attribute5, v_attribute6, v_attribute7, v_attribute8, v_attribute9, v_attribute10,
v_attribute11, v_attribute12, v_attribute13, v_attribute14, v_attribute15,
v_material_account, v_material_overhead_account, v_resource_account,
v_outside_processing_account, v_material_variance_account, v_resource_variance_account,
v_outside_proc_var_account, v_std_cost_adjustment_account, v_overhead_account,
v_overhead_variance_account,v_bom_revision,v_routing_revision, /* 2185087*/
v_bom_revision_date, v_routing_revision_date,
v_alternate_bom_designator,v_alternate_routing_designator,
v_completion_subinventory, v_completion_locator_id,
v_demand_class, v_attribute_category,v_kanban_card_id
FROM wip_flow_schedules
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id
AND request_id = userenv('sessionid')
AND wip_entity_id >= G_WIP_ENTITY_ID;
v_flow_schedule_tab(v_current_wip).last_update_date := v_update_date;
v_flow_schedule_tab(v_current_wip).last_updated_by := v_updated_by;
DELETE
FROM wip_flow_schedules
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id;
SELECT start_time
INTO v_final_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_org_id;
SELECT wip_entities_s.nextval
INTO v_temp
FROM dual;
SELECT last_update_date, last_updated_by, creation_date, created_by,
class_code, status, schedule_number, demand_source_header_id,
demand_source_line, demand_source_delivery, demand_source_type,
project_id, task_id, end_item_unit_number, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
material_account, material_overhead_account, resource_account,
outside_processing_account, material_variance_account, resource_variance_account,
outside_proc_variance_account, std_cost_adjustment_account, overhead_account,
overhead_variance_account,bom_revision,routing_revision , /*2185087 */
bom_revision_date, routing_revision_date,
alternate_bom_designator, alternate_routing_designator,
completion_subinventory, completion_locator_id,
demand_class,attribute_category, kanban_card_id
INTO v_update_date, v_updated_by, v_creation_date, v_created_by,
v_class_code, v_status, v_schedule, v_header_id, v_source_line,
v_source_delivery, v_source_type, v_project_id, v_task_id, v_end_item_unit_number,
v_attribute1, v_attribute2, v_attribute3, v_attribute4,
v_attribute5, v_attribute6, v_attribute7, v_attribute8, v_attribute9, v_attribute10,
v_attribute11, v_attribute12, v_attribute13, v_attribute14, v_attribute15,
v_material_account, v_material_overhead_account, v_resource_account,
v_outside_processing_account, v_material_variance_account, v_resource_variance_account,
v_outside_proc_var_account, v_std_cost_adjustment_account, v_overhead_account,
v_overhead_variance_account,v_bom_revision,v_routing_revision, /*2185087*/
v_bom_revision_date, v_routing_revision_date,
v_alternate_bom_designator,v_alternate_routing_designator,
v_completion_subinventory, v_completion_locator_id,
v_demand_class, v_attribute_category, v_kanban_card_id
FROM wip_flow_schedules
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id;
v_flow_schedule_tab(v_temp).last_update_date := v_update_date;
v_flow_schedule_tab(v_temp).last_updated_by := v_updated_by;
UPDATE wip_flow_schedules
SET planned_quantity = planned_quantity - 1
WHERE wip_entity_id = v_current_wip
AND organization_id = p_org_id;
INSERT INTO wip_flow_schedules(
scheduled_flag,
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
class_code,
line_id,
primary_item_id,
scheduled_start_date,
planned_quantity,
quantity_completed,
quantity_scrapped,
scheduled_completion_date,
schedule_group_id,
build_sequence,
status,
schedule_number,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
demand_source_type,
project_id,
task_id,
end_item_unit_number,
request_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
std_cost_adjustment_account,
overhead_account,
overhead_variance_account,
bom_revision, /* 2185087 */
routing_revision,
bom_revision_date,
routing_revision_date,
alternate_bom_designator,
alternate_routing_designator,
completion_subinventory,
completion_locator_id,
demand_class,
attribute_category,
kanban_card_id)
VALUES (
v_flow_schedule_tab(v_current_schedule).scheduled_flag,
v_current_schedule,
v_flow_schedule_tab(v_current_schedule).organization_id,
v_flow_schedule_tab(v_current_schedule).last_update_date,
v_flow_schedule_tab(v_current_schedule).last_updated_by,
v_flow_schedule_tab(v_current_schedule).creation_date,
v_flow_schedule_tab(v_current_schedule).created_by,
v_flow_schedule_tab(v_current_schedule).class_code,
v_flow_schedule_tab(v_current_schedule).line_id,
v_flow_schedule_tab(v_current_schedule).primary_item_id,
v_flow_schedule_tab(v_current_schedule).scheduled_start_date,
v_flow_schedule_tab(v_current_schedule).planned_quantity,
v_flow_schedule_tab(v_current_schedule).quantity_completed,
0,
v_flow_schedule_tab(v_current_schedule).scheduled_completion_date,
v_flow_schedule_tab(v_current_schedule).schedule_group_id,
v_flow_schedule_tab(v_current_schedule).build_sequence,
v_flow_schedule_tab(v_current_schedule).status,
v_flow_schedule_tab(v_current_schedule).schedule_number,
v_flow_schedule_tab(v_current_schedule).demand_source_header_id,
v_flow_schedule_tab(v_current_schedule).demand_source_line,
v_flow_schedule_tab(v_current_schedule).demand_source_delivery,
v_flow_schedule_tab(v_current_schedule).demand_source_type,
v_flow_schedule_tab(v_current_schedule).project_id, v_flow_schedule_tab(v_current_schedule).task_id,
v_flow_schedule_tab(v_current_schedule).end_item_unit_number,
userenv('sessionid'),
v_flow_schedule_tab(v_current_schedule).attribute1,
v_flow_schedule_tab(v_current_schedule).attribute2,
v_flow_schedule_tab(v_current_schedule).attribute3,
v_flow_schedule_tab(v_current_schedule).attribute4,
v_flow_schedule_tab(v_current_schedule).attribute5,
v_flow_schedule_tab(v_current_schedule).attribute6,
v_flow_schedule_tab(v_current_schedule).attribute7,
v_flow_schedule_tab(v_current_schedule).attribute8,
v_flow_schedule_tab(v_current_schedule).attribute9,
v_flow_schedule_tab(v_current_schedule).attribute10,
v_flow_schedule_tab(v_current_schedule).attribute11,
v_flow_schedule_tab(v_current_schedule).attribute12,
v_flow_schedule_tab(v_current_schedule).attribute13,
v_flow_schedule_tab(v_current_schedule).attribute14,
v_flow_schedule_tab(v_current_schedule).attribute15,
v_flow_schedule_tab(v_current_schedule).material_account,
v_flow_schedule_tab(v_current_schedule).material_overhead_account,
v_flow_schedule_tab(v_current_schedule).resource_account,
v_flow_schedule_tab(v_current_schedule).outside_processing_account,
v_flow_schedule_tab(v_current_schedule).material_variance_account,
v_flow_schedule_tab(v_current_schedule).resource_variance_account,
v_flow_schedule_tab(v_current_schedule).outside_proc_var_account,
v_flow_schedule_tab(v_current_schedule).std_cost_adjustment_account,
v_flow_schedule_tab(v_current_schedule).overhead_account,
v_flow_schedule_tab(v_current_schedule).overhead_variance_account,
v_flow_schedule_tab(v_current_schedule).bom_revision, /*2185087*/
v_flow_schedule_tab(v_current_schedule).routing_revision,
v_flow_schedule_tab(v_current_schedule).bom_revision_date,
v_flow_schedule_tab(v_current_schedule).routing_revision_date,
v_flow_schedule_tab(v_current_schedule).alternate_bom_designator,
v_flow_schedule_tab(v_current_schedule).alternate_routing_designator,
v_flow_schedule_tab(v_current_schedule).completion_subinventory,
v_flow_schedule_tab(v_current_schedule).completion_locator_id,
v_flow_schedule_tab(v_current_schedule).demand_class,
v_flow_schedule_tab(v_current_schedule).attribute_category,
v_flow_schedule_tab(v_current_schedule).kanban_card_id
);
SELECT to_number(to_char(p_scheduling_start_date,'J')),
to_number(to_char(p_scheduling_end_date,'J')),
to_char(p_scheduling_start_date,'SSSSS'),
to_char(p_scheduling_end_date,'SSSSS')
INTO v_start_date, v_end_date, v_start_time, v_end_time
FROM dual;
SELECT MIN(wip_entity_id)
INTO G_WIP_ENTITY_ID
FROM wip_flow_schedules
WHERE line_id = p_line_id
and organization_id = p_org_id
and request_id = USERENV('SESSIONID');
SELECT DISTINCT heuristic_code
INTO v_algorithm
FROM mrp_scheduling_rules
WHERE rule_id = p_rule_id
AND NVL(user_defined,C_USER_DEFINE_NO) = C_USER_DEFINE_NO ;
SELECT demand_source_type
INTO v_source_type
FROM wip_flow_schedules
WHERE request_id = USERENV('SESSIONID')
AND organization_id = p_org_id
AND scheduled_flag = C_NO
AND rownum = 1
AND wip_entity_id >= G_WIP_ENTITY_ID;
update_buildseq(p_line_id, p_org_id);
update_buildseq(p_line_id, p_org_id);
DELETE
FROM wip_flow_schedules
WHERE scheduled_flag = C_NO
AND request_id = USERENV('SESSIONID')
AND organization_id = p_org_id
AND wip_entity_id >= G_WIP_ENTITY_ID;
UPDATE wip_flow_schedules
SET bom_revision = v_bom_rev,
bom_revision_date = v_bom_rev_date,
routing_revision = v_rout_rev,
routing_revision_date = v_rout_rev_date
WHERE wip_entity_id = fs_list_rec.wip_entity_id
AND organization_id = p_org_id;
select start_time, stop_time, 3600/maximum_rate
into l_line_start_time, l_line_end_time, l_sec_rate
from wip_lines
where organization_id = p_org_id
and line_id = p_line_id;
select NVL(fixed_lead_time,0)*l_working_time,NVL(variable_lead_time,0)*l_working_time
into l_fixed_time,l_variable_time
from mtl_system_items
where organization_id = p_org_id
and inventory_item_id = p_item_id;