The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_DeleteRows boolean;
Select rowid,
routing_sequence_id,
assembly_item_id,
organization_id,
alternate_routing_designator,
routing_type,
common_assembly_item_id,
common_routing_sequence_id,
routing_comment,
completion_subinventory,
completion_locator_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
process_revision,
organization_code,
assembly_item_number,
common_item_number,
location_name,
transaction_type,
line_id,
line_code,
mixed_model_map_flag,
priority,
cfm_routing_flag,
total_product_cycle_time,
ctp_flag
From bom_op_routings_interface
Where process_flag = 1
And (g_AllOrgs = g_yes
or organization_id = g_OrgId
or organization_code = g_OrgCode
);
cursor l_RtgDeleteGroup_csr is
Select delete_group_name,
description
From bom_interface_delete_groups
Where upper(entity_name) = G_RtgDelEntity
And rownum = 1;
Select mtl_system_items_interface_s.nextval transaction_id
From dual;
l_interface_rec.transaction_type := G_Insert ;
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.routing_type :=
nvl(l_interface_rec.routing_type, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.common_assembly_item_id :=
nvl(l_interface_rec.common_assembly_item_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.common_routing_sequence_id :=
nvl(l_interface_rec.common_routing_sequence_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.routing_comment :=
nvl(l_interface_rec.routing_comment, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.completion_subinventory :=
nvl(l_interface_rec.completion_subinventory, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.completion_locator_id :=
nvl(l_interface_rec.completion_locator_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute_category :=
nvl(l_interface_rec.attribute_category, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute1 :=
nvl(l_interface_rec.attribute1, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute2 :=
nvl(l_interface_rec.attribute2, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute3 :=
nvl(l_interface_rec.attribute3, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute4 :=
nvl(l_interface_rec.attribute4, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute5 :=
nvl(l_interface_rec.attribute5, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute6 :=
nvl(l_interface_rec.attribute6, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute7 :=
nvl(l_interface_rec.attribute7, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute8 :=
nvl(l_interface_rec.attribute8, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute9 :=
nvl(l_interface_rec.attribute9, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute10 :=
nvl(l_interface_rec.attribute10, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute11 :=
nvl(l_interface_rec.attribute11, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute12 :=
nvl(l_interface_rec.attribute12, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute13 :=
nvl(l_interface_rec.attribute13, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute14 :=
nvl(l_interface_rec.attribute14, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.attribute15 :=
nvl(l_interface_rec.attribute15, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.organization_code :=
nvl(l_interface_rec.organization_code, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.assembly_item_number :=
nvl(l_interface_rec.assembly_item_number, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.common_item_number :=
nvl(l_interface_rec.common_item_number, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.location_name :=
nvl(l_interface_rec.location_name, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.line_id :=
nvl(l_interface_rec.line_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.line_code :=
nvl(l_interface_rec.line_code, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.mixed_model_map_flag :=
nvl(l_interface_rec.mixed_model_map_flag, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.priority :=
nvl(l_interface_rec.priority, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.cfm_routing_flag :=
nvl(l_interface_rec.cfm_routing_flag, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.total_product_cycle_time :=
nvl(l_interface_rec.total_product_cycle_time, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_routing_rec.ctp_flag :=
nvl(l_interface_rec.ctp_flag, Fnd_Api.G_Miss_Num);
If l_interface_rec.transaction_type = G_Insert then
BOM_RoutingHeader_PVT.CreateRouting (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_routing_rec => l_routing_rec,
x_routing_rec => l_routing_rec
);
Insert into mtl_rtg_item_revs_interface
(inventory_item_id,
inventory_item_number,
organization_id,
organization_code,
process_revision,
process_flag,
effectivity_date,
implementation_date,
transaction_type)
values (l_interface_rec.assembly_item_id,
l_interface_rec.assembly_item_number,
l_interface_rec.organization_id,
l_interface_rec.organization_code,
upper(l_interface_rec.process_revision),
1,
sysdate,
sysdate,
G_Insert);
Elsif l_interface_rec.transaction_type = G_Update then
BOM_RoutingHeader_PVT.UpdateRouting(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_routing_rec => l_routing_rec,
x_routing_rec => l_routing_rec);
Elsif l_interface_rec.transaction_type = G_Delete then
l_DelGrpFound := false;
For l_DelGrp_rec in l_RtgDeleteGroup_csr loop
l_DelGrpFound := true;
BOM_RoutingHeader_PVT.DeleteRouting(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_delete_group => l_DelGrp_rec.delete_group_name,
p_description => l_DelGrp_rec.description,
p_routing_rec => l_routing_rec,
x_routing_rec => l_routing_rec
);
End loop; -- delete routing
Fnd_Message.Set_Name('BOM', 'BOM_DELETE_GROUP_NULL');
End if; -- Delete group unspecified
End if; -- insert, update and delete
If g_DeleteRows then
Delete from bom_op_routings_interface
Where rowid = l_interface_rec.rowid;
Update bom_op_routings_interface
Set process_flag = 7,
transaction_id = mtl_system_items_interface_s.nextval,
request_id = nvl(request_id,g_RequestId),
program_id = nvl(program_id,g_ProgramId),
program_application_id = nvl(program_application_id,g_ApplicationId),
program_update_date = nvl(program_update_date,sysdate),
created_by = nvl(created_by,g_UserId),
last_updated_by = nvl(last_updated_by,g_UserId),
creation_date = nvl(creation_date,sysdate),
last_update_date = nvl(last_update_date,sysdate),
last_update_login = nvl(last_update_login,g_LoginId)
Where rowid = l_interface_rec.rowid;
Update bom_op_routings_interface
Set process_flag = 3,
transaction_id = l_transaction_rec.transaction_id
Where rowid = l_interface_rec.rowid;
Select rowid,
operation_sequence_id,
routing_sequence_id,
operation_seq_num,
standard_operation_id,
department_id,
operation_lead_time_percent,
minimum_transfer_quantity,
count_point_type,
operation_description,
effectivity_date,
disable_date,
backflush_flag,
option_dependent_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
assembly_item_id,
organization_id,
alternate_routing_designator,
organization_code,
assembly_item_number,
department_code,
operation_code,
resource_id1,
resource_id2,
resource_id3,
resource_code1,
resource_code2,
resource_code3,
transaction_type,
new_operation_seq_num,
new_effectivity_date,
operation_type,
reference_flag,
process_op_seq_id,
line_op_seq_id,
yield,
cumulative_yield,
reverse_cumulative_yield,
labor_time_calc,
machine_time_calc,
total_time_calc,
labor_time_user,
machine_time_user,
total_time_user,
net_planning_percent,
include_in_rollup,
operation_yield_enabled
From bom_op_sequences_interface
Where process_flag = 1
And (g_AllOrgs = g_yes
or organization_id = g_OrgId
or organization_code = g_OrgCode
);
cursor l_OprDeleteGroup_csr is
Select delete_group_name,
description
From bom_interface_delete_groups
Where upper(entity_name) = G_OprDelEntity
And rownum = 1;
Select mtl_system_items_interface_s.nextval transaction_id
From dual;
l_interface_rec.transaction_type := G_Insert ;
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.operation_sequence_id :=
nvl(l_interface_rec.operation_sequence_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.routing_sequence_id :=
nvl(l_interface_rec.routing_sequence_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.assembly_item_id :=
nvl(l_interface_rec.assembly_item_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.assembly_item_number :=
nvl(l_interface_rec.assembly_item_number, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.organization_id :=
nvl(l_interface_rec.organization_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.organization_code :=
nvl(l_interface_rec.organization_code, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.alternate_routing_designator :=
nvl(l_interface_rec.alternate_routing_designator,
Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.operation_seq_num :=
nvl(l_interface_rec.operation_seq_num, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.new_operation_seq_num :=
nvl(l_interface_rec.new_operation_seq_num, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.standard_operation_id :=
nvl(l_interface_rec.standard_operation_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.operation_code :=
nvl(l_interface_rec.operation_code, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.department_id :=
nvl(l_interface_rec.department_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.department_code :=
nvl(l_interface_rec.department_code, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.operation_lead_time_percent :=
nvl(l_interface_rec.operation_lead_time_percent,
Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.minimum_transfer_quantity :=
nvl(l_interface_rec.minimum_transfer_quantity, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.count_point_type :=
nvl(l_interface_rec.count_point_type, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.operation_description :=
nvl(l_interface_rec.operation_description, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.effectivity_date :=
nvl(l_interface_rec.effectivity_date, Fnd_Api.G_Miss_Date);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.new_effectivity_date :=
nvl(l_interface_rec.new_effectivity_date, Fnd_Api.G_Miss_Date);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.disable_date :=
nvl(l_interface_rec.disable_date, Fnd_Api.G_Miss_Date);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.backflush_flag :=
nvl(l_interface_rec.backflush_flag, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.option_dependent_flag :=
nvl(l_interface_rec.option_dependent_flag, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute_category :=
nvl(l_interface_rec.attribute_category, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute1 :=
nvl(l_interface_rec.attribute1, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute2 :=
nvl(l_interface_rec.attribute2, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute3 :=
nvl(l_interface_rec.attribute3, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute4 :=
nvl(l_interface_rec.attribute4, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute5 :=
nvl(l_interface_rec.attribute5, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute6 :=
nvl(l_interface_rec.attribute6, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute7 :=
nvl(l_interface_rec.attribute7, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute8 :=
nvl(l_interface_rec.attribute8, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute9 :=
nvl(l_interface_rec.attribute9, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute10 :=
nvl(l_interface_rec.attribute10, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute11 :=
nvl(l_interface_rec.attribute11, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute12 :=
nvl(l_interface_rec.attribute12, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute13 :=
nvl(l_interface_rec.attribute13, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute14 :=
nvl(l_interface_rec.attribute14, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.attribute15 :=
nvl(l_interface_rec.attribute15, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.operation_type :=
nvl(l_interface_rec.operation_type, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.reference_flag :=
nvl(l_interface_rec.reference_flag, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.process_op_seq_id :=
nvl(l_interface_rec.process_op_seq_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.line_op_seq_id :=
nvl(l_interface_rec.line_op_seq_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.yield :=
nvl(l_interface_rec.yield, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.cumulative_yield :=
nvl(l_interface_rec.cumulative_yield, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.reverse_cumulative_yield :=
nvl(l_interface_rec.reverse_cumulative_yield, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.labor_time_calc :=
nvl(l_interface_rec.labor_time_calc, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.machine_time_calc :=
nvl(l_interface_rec.machine_time_calc, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.total_time_calc :=
nvl(l_interface_rec.total_time_calc, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.labor_time_user :=
nvl(l_interface_rec.labor_time_user, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.machine_time_user :=
nvl(l_interface_rec.machine_time_user, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.total_time_user :=
nvl(l_interface_rec.total_time_user, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.net_planning_percent :=
nvl(l_interface_rec.net_planning_percent, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.include_in_rollup :=
nvl(l_interface_rec.include_in_rollup, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_operation_rec.operation_yield_enabled :=
nvl(l_interface_rec.operation_yield_enabled, Fnd_Api.G_Miss_Num);
If l_interface_rec.transaction_type = G_Insert then
BOM_Operation_PVT.CreateOperation(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_operation_rec => l_operation_rec,
x_operation_rec => l_operation_rec
);
insert into bom_op_resources_interface(
OPERATION_SEQUENCE_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ALTERNATE_ROUTING_DESIGNATOR,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_CODE,
PROCESS_FLAG,
TRANSACTION_TYPE)
values (
l_operation_rec.operation_sequence_id,
l_operation_rec.assembly_item_id,
l_operation_rec.assembly_item_number,
l_operation_rec.organization_id,
l_operation_rec.organization_code,
l_operation_rec.alternate_routing_designator,
l_operation_rec.operation_seq_num,
l_operation_rec.effectivity_date,
10,
l_interface_rec.resource_id1,
l_interface_rec.resource_code1,
1,
G_Insert);
insert into bom_op_resources_interface (
OPERATION_SEQUENCE_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ALTERNATE_ROUTING_DESIGNATOR,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_CODE,
PROCESS_FLAG,
TRANSACTION_TYPE)
values (
l_operation_rec.operation_sequence_id,
l_operation_rec.assembly_item_id,
l_operation_rec.assembly_item_number,
l_operation_rec.organization_id,
l_operation_rec.organization_code,
l_operation_rec.alternate_routing_designator,
l_operation_rec.operation_seq_num,
l_operation_rec.effectivity_date,
20,
l_interface_rec.resource_id2,
l_interface_rec.resource_code2,
1,
G_Insert);
insert into bom_op_resources_interface (
OPERATION_SEQUENCE_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ALTERNATE_ROUTING_DESIGNATOR,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_CODE,
PROCESS_FLAG,
TRANSACTION_TYPE)
values(
l_operation_rec.operation_sequence_id,
l_operation_rec.assembly_item_id,
l_operation_rec.assembly_item_number,
l_operation_rec.organization_id,
l_operation_rec.organization_code,
l_operation_rec.alternate_routing_designator,
l_operation_rec.operation_seq_num,
l_operation_rec.effectivity_date,
30,
l_interface_rec.resource_id3,
l_interface_rec.resource_code3,
1,
G_Insert);
Elsif l_interface_rec.transaction_type = G_Update then
BOM_Operation_PVT.UpdateOperation(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_operation_rec => l_operation_rec,
x_operation_rec => l_operation_rec);
Elsif l_interface_rec.transaction_type = G_Delete then
l_DelGrpFound := false;
For l_DelGrp_rec in l_OprDeleteGroup_csr loop
l_DelGrpFound := true;
BOM_Operation_Pvt.DeleteOperation(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_delete_group => l_DelGrp_rec.delete_group_name,
p_description => l_DelGrp_rec.description,
p_operation_rec => l_operation_rec,
x_operation_rec => l_operation_rec
);
End loop; -- delete operation
Fnd_Message.Set_Name('BOM', 'BOM_DELETE_GROUP_NULL');
End if; -- Delete group unspecified
End if; -- insert, update and delete
If g_DeleteRows then
Delete from bom_op_sequences_interface
Where rowid = l_interface_rec.rowid;
Update bom_op_sequences_interface
Set process_flag = 7,
transaction_id = mtl_system_items_interface_s.nextval,
request_id = nvl(request_id,g_RequestId),
program_id = nvl(program_id,g_ProgramId),
program_application_id = nvl(program_application_id,g_ApplicationId),
program_update_date = nvl(program_update_date,sysdate),
created_by = nvl(created_by,g_UserId),
last_updated_by = nvl(last_updated_by,g_UserId),
creation_date = nvl(creation_date,sysdate),
last_update_date = nvl(last_update_date,sysdate),
last_update_login = nvl(last_update_login,g_LoginId)
Where rowid = l_interface_rec.rowid;
Update bom_op_sequences_interface
Set process_flag = 3,
transaction_id = l_transaction_rec.transaction_id
Where rowid = l_interface_rec.rowid;
Select rowid,
operation_sequence_id,
routing_sequence_id,
assembly_item_id,
assembly_item_number,
organization_id,
organization_code,
alternate_routing_designator,
operation_seq_num,
effectivity_date,
resource_seq_num,
new_resource_seq_num,
resource_id,
resource_code,
activity_id,
activity,
standard_rate_flag,
assigned_units,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
basis_type,
schedule_flag,
resource_offset_percent,
autocharge_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
transaction_type,
principle_flag, -- 2514018
schedule_seq_num -- 2514018
From bom_op_resources_interface
Where process_flag = 1
And (g_AllOrgs = g_yes
or organization_id = g_OrgId
or organization_code = g_OrgCode
);
l_phase NUMBER := 1; -- scan table twice in case inserting and updating same
Select mtl_system_items_interface_s.nextval transaction_id
From dual;
l_interface_rec.transaction_type := G_Insert ;
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.operation_sequence_id :=
nvl(l_interface_rec.operation_sequence_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.routing_sequence_id :=
nvl(l_interface_rec.routing_sequence_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.assembly_item_id :=
nvl(l_interface_rec.assembly_item_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.assembly_item_number :=
nvl(l_interface_rec.assembly_item_number, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.organization_id :=
nvl(l_interface_rec.organization_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.organization_code :=
nvl(l_interface_rec.organization_code, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.alternate_routing_designator :=
nvl(l_interface_rec.alternate_routing_designator,
Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.operation_seq_num :=
nvl(l_interface_rec.operation_seq_num, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.effectivity_date :=
nvl(l_interface_rec.effectivity_date, Fnd_Api.G_Miss_Date);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.resource_seq_num :=
nvl(l_interface_rec.resource_seq_num, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.new_resource_seq_num :=
nvl(l_interface_rec.new_resource_seq_num, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.resource_id :=
nvl(l_interface_rec.resource_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.resource_code :=
nvl(l_interface_rec.resource_code, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.activity_id :=
nvl(l_interface_rec.activity_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.activity :=
nvl(l_interface_rec.activity, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.standard_rate_flag :=
nvl(l_interface_rec.standard_rate_flag, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.assigned_units :=
nvl(l_interface_rec.assigned_units, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.usage_rate_or_amount :=
nvl(l_interface_rec.usage_rate_or_amount, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.usage_rate_or_amount_inverse :=
nvl(l_interface_rec.usage_rate_or_amount_inverse,
Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.basis_type :=
nvl(l_interface_rec.basis_type, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.schedule_flag :=
nvl(l_interface_rec.schedule_flag, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.resource_offset_percent :=
nvl(l_interface_rec.resource_offset_percent, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.autocharge_type :=
nvl(l_interface_rec.autocharge_type, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute_category :=
nvl(l_interface_rec.attribute_category, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute1 :=
nvl(l_interface_rec.attribute1, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute2 :=
nvl(l_interface_rec.attribute2, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute3 :=
nvl(l_interface_rec.attribute3, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute4 :=
nvl(l_interface_rec.attribute4, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute5 :=
nvl(l_interface_rec.attribute5, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute6 :=
nvl(l_interface_rec.attribute6, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute7 :=
nvl(l_interface_rec.attribute7, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute8 :=
nvl(l_interface_rec.attribute8, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute9 :=
nvl(l_interface_rec.attribute9, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute10 :=
nvl(l_interface_rec.attribute10, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute11 :=
nvl(l_interface_rec.attribute11, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute12 :=
nvl(l_interface_rec.attribute12, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute13 :=
nvl(l_interface_rec.attribute13, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute14 :=
nvl(l_interface_rec.attribute14, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.attribute15 :=
nvl(l_interface_rec.attribute15, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.principle_flag :=
nvl(l_interface_rec.principle_flag,Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_resource_rec.schedule_seq_num :=
nvl(l_interface_rec.schedule_seq_num,Fnd_Api.G_Miss_Num);
If l_interface_rec.transaction_type = G_Insert then
Bom_OpResource_Pvt.CreateResource(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_resource_rec => l_resource_rec,
x_resource_rec => l_resource_rec
);
Elsif l_interface_rec.transaction_type = G_Update then
Bom_OpResource_Pvt.UpdateResource(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_resource_rec => l_resource_rec,
x_resource_rec => l_resource_rec
);
Elsif l_interface_rec.transaction_type = G_Delete then
Bom_OpResource_Pvt.DeleteResource(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_resource_rec => l_resource_rec,
x_resource_rec => l_resource_rec
);
End if; -- insert, update and delete
If g_DeleteRows then
Delete from bom_op_resources_interface
Where rowid = l_interface_rec.rowid;
Update bom_op_resources_interface
Set process_flag = 7,
transaction_id = mtl_system_items_interface_s.nextval,
request_id = nvl(request_id,g_RequestId),
program_id = nvl(program_id,g_ProgramId),
program_application_id = nvl(program_application_id,g_ApplicationId),
program_update_date = nvl(program_update_date,sysdate),
created_by = nvl(created_by,g_UserId),
last_updated_by = nvl(last_updated_by,g_UserId),
creation_date = nvl(creation_date,sysdate),
last_update_date = nvl(last_update_date,sysdate),
last_update_login = nvl(last_update_login,g_LoginId)
Where rowid = l_interface_rec.rowid;
Update bom_op_resources_interface
Set process_flag = 3,
transaction_id = l_transaction_rec.transaction_id
Where rowid = l_interface_rec.rowid;
Select rowid,
inventory_item_id,
inventory_item_number,
organization_id,
organization_code,
process_revision,
change_notice,
ecn_initiation_date,
implementation_date,
implemented_serial_number,
effectivity_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
transaction_type
From mtl_rtg_item_revs_interface
Where process_flag = 1
And (g_AllOrgs = g_yes
or organization_id = g_OrgId
or organization_code = g_OrgCode
);
l_phase NUMBER := 1; -- scan table twice in case inserting and updating same
Select mtl_system_items_interface_s.nextval transaction_id
From dual;
l_interface_rec.transaction_type := G_Insert ;
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.inventory_item_id :=
nvl(l_interface_rec.inventory_item_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.inventory_item_number :=
nvl(l_interface_rec.inventory_item_number, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.organization_id :=
nvl(l_interface_rec.organization_id, Fnd_Api.G_Miss_Num);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.organization_code :=
nvl(l_interface_rec.organization_code, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.process_revision :=
nvl(l_interface_rec.process_revision, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.change_notice :=
nvl(l_interface_rec.change_notice, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.ecn_initiation_date :=
nvl(l_interface_rec.ecn_initiation_date, Fnd_Api.G_Miss_Date);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.implementation_date :=
nvl(l_interface_rec.implementation_date, Fnd_Api.G_Miss_Date);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.implemented_serial_number :=
nvl(l_interface_rec.implemented_serial_number, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.effectivity_date :=
nvl(l_interface_rec.effectivity_date, Fnd_Api.G_Miss_Date);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute_category :=
nvl(l_interface_rec.attribute_category, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute1 :=
nvl(l_interface_rec.attribute1, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute2 :=
nvl(l_interface_rec.attribute2, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute3 :=
nvl(l_interface_rec.attribute3, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute4 :=
nvl(l_interface_rec.attribute4, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute5 :=
nvl(l_interface_rec.attribute5, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute6 :=
nvl(l_interface_rec.attribute6, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute7 :=
nvl(l_interface_rec.attribute7, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute8 :=
nvl(l_interface_rec.attribute8, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute9 :=
nvl(l_interface_rec.attribute9, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute10 :=
nvl(l_interface_rec.attribute10, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute11 :=
nvl(l_interface_rec.attribute11, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute12 :=
nvl(l_interface_rec.attribute12, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute13 :=
nvl(l_interface_rec.attribute13, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute14 :=
nvl(l_interface_rec.attribute14, Fnd_Api.G_Miss_Char);
Elsif l_interface_rec.transaction_type = G_Update then
l_revision_rec.attribute15 :=
nvl(l_interface_rec.attribute15, Fnd_Api.G_Miss_Char);
If l_interface_rec.transaction_type = G_Insert then
Bom_RoutingRevision_Pvt.CreateRtgRevision(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_RtgRevision_rec => l_revision_rec,
x_RtgRevision_rec => l_revision_rec
);
Elsif l_interface_rec.transaction_type = G_Update then
Bom_RoutingRevision_Pvt.UpdateRtgRevision(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_RtgRevision_rec => l_revision_rec,
x_RtgRevision_rec => l_revision_rec
);
Elsif l_interface_rec.transaction_type = G_Delete then
Bom_RoutingRevision_Pvt.DeleteRtgRevision(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_RtgRevision_rec => l_revision_rec,
x_RtgRevision_rec => l_revision_rec
);
End if; -- insert, update and delete
If g_DeleteRows then
Delete from mtl_rtg_item_revs_interface
Where rowid = l_interface_rec.rowid;
Update mtl_rtg_item_revs_interface
Set process_flag = 7,
transaction_id = mtl_system_items_interface_s.nextval,
request_id = nvl(request_id,g_RequestId),
program_id = nvl(program_id,g_ProgramId),
program_application_id = nvl(program_application_id,g_ApplicationId),
program_update_date = nvl(program_update_date,sysdate),
created_by = nvl(created_by,g_UserId),
last_updated_by = nvl(last_updated_by,g_UserId),
creation_date = nvl(creation_date,sysdate),
last_update_date = nvl(last_update_date,sysdate),
last_update_login = nvl(last_update_login,g_LoginId)
Where rowid = l_interface_rec.rowid;
Update mtl_rtg_item_revs_interface
Set process_flag = 3,
transaction_id = l_transaction_rec.transaction_id
Where rowid = l_interface_rec.rowid;
p_delete_rows IN VARCHAR2 := FND_API.G_FALSE
)IS
l_api_name CONSTANT VARCHAR2(30) := 'ImportRouting';
Select organization_code
From mtl_parameters
Where organization_id = p_organization_id;
g_DeleteRows := Fnd_Api.to_boolean(p_delete_rows);