The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
max(BUILD_SEQUENCE)
FROM
WIP_FLOW_SCHEDULES
WHERE
ORGANIZATION_ID = p_organization_id AND
LINE_ID = p_line_id AND
SCHEDULED_COMPLETION_DATE <= l_date1;
SELECT
min(BUILD_SEQUENCE)
FROM
WIP_FLOW_SCHEDULES
WHERE
ORGANIZATION_ID = p_organization_id AND
LINE_ID = p_line_id AND
SCHEDULED_COMPLETION_DATE > l_date2;
sch_rec_tbl.DELETE;
g_cto_line_tbl.DELETE;
* To default the schedule columns and inserting the schedules *
******************************************************************/
PROCEDURE create_schedules (o_return_code OUT NOCOPY NUMBER) IS
l_return_code NUMBER;
l_sch_tbl_to_insert wip_flow_schedule_tbl;
default_attributes(sch_rec_tbl, l_sch_tbl_to_insert, l_return_code);
insert_schedules(l_sch_tbl_to_insert, l_return_code);
explode_all_items(l_sch_tbl_to_insert,l_return_code);
update_mrp_recommendations(l_sch_tbl_to_insert,l_return_code);
l_sch_tbl_to_insert IN OUT NOCOPY wip_flow_schedule_tbl,
o_return_code OUT NOCOPY NUMBER) IS
l_index NUMBER;
l_sch_tbl_to_insert(l_index).primary_item_id :=
sch_rec_tbl(l_index).primary_item_id;
l_sch_tbl_to_insert(l_index).organization_id :=
sch_rec_tbl(l_index).org_id;
l_sch_tbl_to_insert(l_index).planned_quantity :=
sch_rec_tbl(l_index).planned_quantity;
l_sch_tbl_to_insert(l_index).alternate_routing_designator :=
sch_rec_tbl(l_index).alt_rtg_designator;
l_sch_tbl_to_insert(l_index).scheduled_start_date :=
sch_rec_tbl(l_index).sch_start_date;
l_sch_tbl_to_insert(l_index).scheduled_completion_date :=
sch_rec_tbl(l_index).sch_completion_date;
l_sch_tbl_to_insert(l_index).schedule_group_id :=
sch_rec_tbl(l_index).sch_group_id;
l_sch_tbl_to_insert(l_index).build_sequence := null;
l_sch_tbl_to_insert(l_index).build_sequence :=
sch_rec_tbl(l_index).build_sequence;
l_sch_tbl_to_insert(l_index).line_id :=
sch_rec_tbl(l_index).line_id;
l_sch_tbl_to_insert(l_index).demand_source_type :=
sch_rec_tbl(l_index).demand_type;
l_sch_tbl_to_insert(l_index).demand_source_line :=
to_char(sch_rec_tbl(l_index).demand_id);
l_sch_tbl_to_insert(l_index).wip_entity_id :=
sch_rec_tbl(l_index).wip_entity_id;
l_sch_tbl_to_insert(l_index).schedule_number :=
sch_rec_tbl(l_index).schedule_number;
l_sch_tbl_to_insert(l_index).last_update_date := sysdate;
l_sch_tbl_to_insert(l_index).last_updated_by := g_user_id;
l_sch_tbl_to_insert(l_index).creation_date := sysdate;
l_sch_tbl_to_insert(l_index).created_by := g_user_id;
l_sch_tbl_to_insert(l_index).last_update_login := g_login_id;
l_sch_tbl_to_insert(l_index).scheduled_flag := 1;
l_sch_tbl_to_insert(l_index).status := 1;
l_sch_tbl_to_insert(l_index).quantity_completed := 0;
l_sch_tbl_to_insert(l_index).quantity_scrapped := 0;
l_sch_tbl_to_insert(l_index).date_closed := null;
l_sch_tbl_to_insert(l_index).project_id := null;
l_sch_tbl_to_insert(l_index).task_id := null;*/
l_sch_tbl_to_insert(l_index).kanban_card_id := null;
l_sch_tbl_to_insert(l_index).attribute1 := g_attribute1;
l_sch_tbl_to_insert(l_index).attribute2 := g_attribute2;
l_sch_tbl_to_insert(l_index).attribute3 := g_attribute3;
l_sch_tbl_to_insert(l_index).attribute4 := g_attribute4;
l_sch_tbl_to_insert(l_index).attribute5 := g_attribute5;
l_sch_tbl_to_insert(l_index).attribute6 := g_attribute6;
l_sch_tbl_to_insert(l_index).attribute7 := g_attribute7;
l_sch_tbl_to_insert(l_index).attribute8 := g_attribute8;
l_sch_tbl_to_insert(l_index).attribute9 := g_attribute9;
l_sch_tbl_to_insert(l_index).attribute10 := g_attribute10;
l_sch_tbl_to_insert(l_index).attribute11 := g_attribute11;
l_sch_tbl_to_insert(l_index).attribute12 := g_attribute12;
l_sch_tbl_to_insert(l_index).attribute13 := g_attribute13;
l_sch_tbl_to_insert(l_index).attribute14 := g_attribute14;
l_sch_tbl_to_insert(l_index).attribute15 := g_attribute15;
l_class_code := get_class_code(l_sch_tbl_to_insert(l_index).organization_id,
l_sch_tbl_to_insert(l_index).primary_item_id);
l_sch_tbl_to_insert(l_index).class_code := l_class_code;
get_account_ids (l_sch_tbl_to_insert(l_index).organization_id,
l_sch_tbl_to_insert(l_index).class_code,
l_sch_tbl_to_insert(l_index).material_account,
l_sch_tbl_to_insert(l_index).material_overhead_account,
l_sch_tbl_to_insert(l_index).resource_account,
l_sch_tbl_to_insert(l_index).outside_processing_account,
l_sch_tbl_to_insert(l_index).material_variance_account,
l_sch_tbl_to_insert(l_index).resource_variance_account,
l_sch_tbl_to_insert(l_index).outside_proc_variance_account,
l_sch_tbl_to_insert(l_index).std_cost_adjustment_account,
l_sch_tbl_to_insert(l_index).overhead_account,
l_sch_tbl_to_insert(l_index).overhead_variance_account);
get_bom_rev_and_date(l_sch_tbl_to_insert(l_index).organization_id,
l_sch_tbl_to_insert(l_index).primary_item_id,
l_sch_tbl_to_insert(l_index).scheduled_completion_date,
l_sch_tbl_to_insert(l_index).bom_revision,
l_sch_tbl_to_insert(l_index).bom_revision_date);
get_rtg_rev_and_date(l_sch_tbl_to_insert(l_index).organization_id,
l_sch_tbl_to_insert(l_index).primary_item_id,
l_sch_tbl_to_insert(l_index).scheduled_completion_date,
l_sch_tbl_to_insert(l_index).routing_revision,
l_sch_tbl_to_insert(l_index).routing_revision_date);
get_alt_bom_designator(l_sch_tbl_to_insert(l_index).organization_id,
l_sch_tbl_to_insert(l_index).primary_item_id,
l_sch_tbl_to_insert(l_index).alternate_routing_designator,
l_sch_tbl_to_insert(l_index).alternate_bom_designator);
get_completion_subinv_and_loc(l_sch_tbl_to_insert(l_index).organization_id,
l_sch_tbl_to_insert(l_index).primary_item_id,
l_sch_tbl_to_insert(l_index).alternate_routing_designator,
l_sch_tbl_to_insert(l_index).completion_subinventory,
l_sch_tbl_to_insert(l_index).completion_locator_id);
l_sch_tbl_to_insert(l_index).demand_class,
l_sch_tbl_to_insert(l_index).demand_source_header_id);
l_sch_tbl_to_insert(l_index).project_id,
l_sch_tbl_to_insert(l_index).task_id);
l_sch_tbl_to_insert(l_index).request_id := USERENV('SESSIONID');
SELECT wip_entities_s.nextval
INTO l_wip_entity_id
FROM dual;
SELECT demand_class_code,header_id
INTO l_demand_class, l_demand_header
FROM OE_ORDER_LINES_ALL
WHERE line_id = i_demand_id;
SELECT project_id,task_id
INTO l_project_id, l_task_id
FROM OE_ORDER_LINES_ALL
WHERE line_id = i_demand_id;
SELECT count(bill_sequence_id)
INTO l_bill_count
FROM BOM_BILL_OF_MATERIALS
WHERE organization_id = i_org_id AND
assembly_item_id = i_primary_item_id AND
alternate_bom_designator = i_alt_rtg_designator;
SELECT 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
INTO
l_material_act, l_material_overhead_act,
l_resource_act, l_outside_processing_act,
l_material_variance_act, l_resource_variance_act,
l_outside_proc_variance_act, l_std_cost_adjustment_act,
l_overhead_act, l_overhead_variance_act
FROM WIP_ACCOUNTING_CLASSES
WHERE ORGANIZATION_ID = i_org_id AND
CLASS_CODE = i_class_code;
SELECT default_discrete_class
INTO l_class_code
FROM wip_parameters
WHERE organization_id = i_org_id;
select msi.build_in_wip_flag, msi.replenish_to_order_flag
into l_build_in_wip_flag, l_replenish_to_order_flag
from mtl_system_items msi
where msi.inventory_item_id = l_primary_item_id
and msi.organization_id = l_org_id;
* To update the mrp_recommendations based on schedules inserted *
******************************************************************/
PROCEDURE update_mrp_recommendations(i_schedules_tbl IN wip_flow_schedule_tbl,
o_return_code IN OUT NOCOPY NUMBER) IS
l_index NUMBER;
l_item_tbl_to_update_rec wip_flow_schedule_tbl;
if(l_item_tbl_to_update_rec.COUNT > 1) then
for j in l_item_tbl_to_update_rec.FIRST .. l_item_tbl_to_update_rec.LAST
LOOP
if(
(l_item_tbl_to_update_rec(j).organization_id =
i_schedules_tbl(i).organization_id) AND
(l_item_tbl_to_update_rec(j).demand_source_line =
i_schedules_tbl(i).demand_source_line) AND
(i_schedules_tbl(i).demand_source_header_id IS NULL )
) then
l_item_tbl_to_update_rec(j).planned_quantity :=
l_item_tbl_to_update_rec(j).planned_quantity +
i_schedules_tbl(i).planned_quantity;
l_index := l_item_tbl_to_update_rec.COUNT;
l_item_tbl_to_update_rec(l_index).organization_id :=
i_schedules_tbl(i).organization_id;
l_item_tbl_to_update_rec(l_index).demand_source_line :=
i_schedules_tbl(i).demand_source_line;
l_item_tbl_to_update_rec(l_index).planned_quantity :=
i_schedules_tbl(i).planned_quantity;
if(l_item_tbl_to_update_rec.COUNT > 0) then
for k in l_item_tbl_to_update_rec.FIRST .. l_item_tbl_to_update_rec.LAST
LOOP
UPDATE mrp_recommendations
SET quantity_in_process =
nvl(quantity_in_process,0) + l_item_tbl_to_update_rec(k).planned_quantity
WHERE transaction_id = l_item_tbl_to_update_rec(k).demand_source_line;
END update_mrp_recommendations;
* Used to insert all the schedule in the table *
******************************************************************/
PROCEDURE insert_schedules (
i_schedules_tbl IN wip_flow_schedule_tbl,
o_return_code OUT NOCOPY NUMBER) IS
l_index NUMBER;
insert into wip_flow_schedules
(
scheduled_flag,
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
primary_item_id,
class_code,
scheduled_start_date,
date_closed,
planned_quantity,
quantity_completed,
mps_scheduled_completion_date,
mps_net_quantity,
bom_revision,
routing_revision,
bom_revision_date,
routing_revision_date,
alternate_bom_designator,
alternate_routing_designator,
completion_subinventory,
completion_locator_id,
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,
demand_class,
scheduled_completion_date,
schedule_group_id,
build_sequence,
line_id,
project_id,
task_id,
status,
schedule_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
demand_source_header_id,
demand_source_line,
demand_source_delivery,
demand_source_type,
kanban_card_id,
quantity_scrapped
)
values
(
i_schedules_tbl(l_index).scheduled_flag,
i_schedules_tbl(l_index).wip_entity_id,
i_schedules_tbl(l_index).organization_id,
i_schedules_tbl(l_index).last_update_date,
i_schedules_tbl(l_index).last_updated_by,
i_schedules_tbl(l_index).creation_date,
i_schedules_tbl(l_index).created_by,
i_schedules_tbl(l_index).last_update_login,
i_schedules_tbl(l_index).request_id,
i_schedules_tbl(l_index).program_application_id,
i_schedules_tbl(l_index).program_id,
i_schedules_tbl(l_index).program_update_date,
i_schedules_tbl(l_index).primary_item_id,
i_schedules_tbl(l_index).class_code,
i_schedules_tbl(l_index).scheduled_start_date,
i_schedules_tbl(l_index).date_closed,
i_schedules_tbl(l_index).planned_quantity,
i_schedules_tbl(l_index).quantity_completed,
i_schedules_tbl(l_index).mps_scheduled_completion_date,
i_schedules_tbl(l_index).mps_net_quantity,
i_schedules_tbl(l_index).bom_revision,
i_schedules_tbl(l_index).routing_revision,
i_schedules_tbl(l_index).bom_revision_date,
i_schedules_tbl(l_index).routing_revision_date,
i_schedules_tbl(l_index).alternate_bom_designator,
i_schedules_tbl(l_index).alternate_routing_designator,
i_schedules_tbl(l_index).completion_subinventory,
i_schedules_tbl(l_index).completion_locator_id,
i_schedules_tbl(l_index).material_account,
i_schedules_tbl(l_index).material_overhead_account,
i_schedules_tbl(l_index).resource_account,
i_schedules_tbl(l_index).outside_processing_account,
i_schedules_tbl(l_index).material_variance_account,
i_schedules_tbl(l_index).resource_variance_account,
i_schedules_tbl(l_index).outside_proc_variance_account,
i_schedules_tbl(l_index).std_cost_adjustment_account,
i_schedules_tbl(l_index).overhead_account,
i_schedules_tbl(l_index).overhead_variance_account,
i_schedules_tbl(l_index).demand_class,
i_schedules_tbl(l_index).scheduled_completion_date,
i_schedules_tbl(l_index).schedule_group_id,
i_schedules_tbl(l_index).build_sequence,
i_schedules_tbl(l_index).line_id,
i_schedules_tbl(l_index).project_id,
i_schedules_tbl(l_index).task_id,
i_schedules_tbl(l_index).status,
i_schedules_tbl(l_index).schedule_number,
i_schedules_tbl(l_index).attribute_category,
i_schedules_tbl(l_index).attribute1,
i_schedules_tbl(l_index).attribute2,
i_schedules_tbl(l_index).attribute3,
i_schedules_tbl(l_index).attribute4,
i_schedules_tbl(l_index).attribute5,
i_schedules_tbl(l_index).attribute6,
i_schedules_tbl(l_index).attribute7,
i_schedules_tbl(l_index).attribute8,
i_schedules_tbl(l_index).attribute9,
i_schedules_tbl(l_index).attribute10,
i_schedules_tbl(l_index).attribute11,
i_schedules_tbl(l_index).attribute12,
i_schedules_tbl(l_index).attribute13,
i_schedules_tbl(l_index).attribute14,
i_schedules_tbl(l_index).attribute15,
i_schedules_tbl(l_index).demand_source_header_id,
i_schedules_tbl(l_index).demand_source_line,
i_schedules_tbl(l_index).demand_source_delivery,
i_schedules_tbl(l_index).demand_source_type,
i_schedules_tbl(l_index).kanban_card_id,
i_schedules_tbl(l_index).quantity_scrapped
);
END insert_schedules;
SELECT distinct(ATTRIBUTE_VALUE1_NUM) inventory_item_id
FROM FLM_SEQ_TASK_CONSTRAINTS
WHERE SEQ_TASK_ID = p_seq_task_id
AND ORGANIZATION_ID = p_organization_id
AND CONSTRAINT_TYPE = 7;
SELECT line_id
FROM FLM_SEQ_TASK_LINES
WHERE SEQ_TASK_ID = p_seq_task_id;