[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select bos.routing_sequence_id,
bos.operation_type,
bos.operation_seq_num
From bom_operation_sequences bos
Where operation_sequence_id = P_OpSeqId;
Select assembly_item_id,
organization_id,
alternate_routing_designator
From bom_operational_routings
Where routing_sequence_id = P_RtgSeqId;
Select organization_id
From mtl_parameters
Where organization_code = P_Code;
select department_id
from bom_departments
where organization_id = P_OrgId
and department_code = P_Code;
select routing_sequence_id
from bom_operational_routings
where organization_id = P_OrgId
and assembly_item_id = P_AssyItemId
and nvl(alternate_routing_designator, 'Primary Alternate') =
nvl(P_Alternate, 'Primary Alternate');
select bso.standard_operation_id
from bom_standard_operations bso,
bom_operational_routings bor
where bso.organization_id = bor.organization_id
and nvl(bso.line_id, -1) = nvl(bor.line_id, -1)
and nvl(bso.operation_type, g_event) =
nvl(P_OpType, g_event)
and bso.operation_code = P_Code
and bor.routing_sequence_id = P_RtgSeqId;
SELECT bom_item_type
INTO g_assy_item_type
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = l_operation_rec.organization_id
AND inventory_item_id = l_operation_rec.Assembly_Item_Id ;
Select 'x' dummy
From dual
Where exists(
Select null
From bom_operation_sequences bos
where bos.routing_sequence_id = P_RtgSeqId
and bos.effectivity_date = P_EffDate
and bos.operation_seq_num = P_OpSeqNum
and nvl(bos.operation_type, g_event) =
nvl(P_OpType, g_event)
and bos.operation_sequence_id <> P_OpSeqId);
Select 'x' dummy
From dual
Where not exists(
select null
from bom_operational_routings bor
where bor.routing_sequence_id = P_RtgSeqId);
Select 'x' dummy
From bom_operational_routings bor
Where bor.routing_sequence_id = P_RtgSeqId
And nvl(bor.cfm_routing_flag, 2) = 2;
Select 'x' dummy
From bom_operational_routings bor
Where bor.routing_sequence_id = P_RtgSeqId
And nvl(bor.cfm_routing_flag,2) = 3;
select 'x' dummy
From dual
Where exists (
select null
from bom_operation_sequences
where operation_sequence_id <> P_OpSeqId
and routing_sequence_id = P_RtgSeqId
and operation_seq_num = P_OpSeqNum
and nvl(operation_type, g_event) =
nvl(P_OpType, g_event)
and (effectivity_date < nvl(P_DisDate,
effectivity_date + 1) and
nvl(disable_date, P_EffDate + 1) >= P_EffDate
)
);
select 'Is pointing to a common' dummy
from bom_operational_routings
where routing_sequence_id = P_RtgSeqId
and common_routing_sequence_id <>
routing_sequence_id;
Select bos.standard_operation_id,
bso.operation_code,
nvl(bos.reference_flag, 2) reference_flag
From bom_operation_sequences bos,
bom_standard_operations bso
Where operation_sequence_id = P_OpSeqId
And bos.standard_operation_id =
bso.standard_operation_id;
Select 'x' dummy
From dual
Where exists(
Select null
From bom_operation_resources
Where operation_sequence_id = P_OpSeqId);
Select 'x' dummy
From dual
Where exists(
Select null
From fnd_attached_documents
Where pk1_value = to_char(P_OpSeqId)
and entity_name = 'BOM_OPERATION_SEQUENCES');
select bso.DEPARTMENT_ID,
bso.MINIMUM_TRANSFER_QUANTITY,
bso.COUNT_POINT_TYPE,
bso.OPERATION_DESCRIPTION,
bso.BACKFLUSH_FLAG,
bso.OPTION_DEPENDENT_FLAG,
bso.ATTRIBUTE_CATEGORY,
bso.ATTRIBUTE1,
bso.ATTRIBUTE2,
bso.ATTRIBUTE3,
bso.ATTRIBUTE4,
bso.ATTRIBUTE5,
bso.ATTRIBUTE6,
bso.ATTRIBUTE7,
bso.ATTRIBUTE8,
bso.ATTRIBUTE9,
bso.ATTRIBUTE10,
bso.ATTRIBUTE11,
bso.ATTRIBUTE12,
bso.ATTRIBUTE13,
bso.ATTRIBUTE14,
bso.ATTRIBUTE15,
bso.OPERATION_YIELD_ENABLED
from bom_standard_operations bso,
bom_operational_routings bor
where bso.standard_operation_id = P_StdOpId
and bor.routing_sequence_id = P_RtgSeqId
and bso.organization_id = bor.organization_id
and nvl(bso.line_id, -1) = nvl(bor.line_id, -1)
and nvl(bso.operation_type, g_event) =
nvl(P_OpType, g_event);
l_ProgramUpdate date;
select 'x' dummy
from dual
where not exists(
Select null
from bom_departments bd,
bom_operational_routings bor
where bd.organization_id = bor.organization_id
and bor.routing_sequence_id = P_RtgSeqId
and bd.department_id = P_DeptId
and nvl(bd.disable_date, P_EffDate+1) > P_EffDate
);
Select 'x' dummy
from bom_operation_resources
where operation_sequence_id = P_ParentSeqId;
l_ProgramUpdate := null;
l_ProgramUpdate := sysdate;
Insert into bom_operation_resources(
operation_sequence_id,
resource_seq_num,
resource_id,
activity_id,
standard_rate_flag,
assigned_units,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
basis_type,
schedule_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
resource_offset_percent,
autocharge_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date)
Select
l_operation_rec.operation_sequence_id,
resource_seq_num,
resource_id,
activity_id,
standard_rate_flag,
assigned_units,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
basis_type,
schedule_flag,
sysdate,
l_UserId,
sysdate,
l_UserId,
l_LoginId,
null,
autocharge_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
l_RequestId,
l_ApplicationId,
l_ProgramId,
l_ProgramUpdate
From bom_std_op_resources
Where standard_operation_id = l_operation_rec.standard_operation_id;
X_last_update_login => l_LoginId,
X_program_application_id => l_ApplicationId,
X_program_id => l_ProgramId,
X_request_id => l_RequestId
);
l_ProgramUpdate date;
Select bom_operation_sequences_s.nextval new_op_seq_id
from dual;
End if; -- validate before inserting
l_ProgramUpdate := null;
l_ProgramUpdate := sysdate;
Insert into bom_operation_sequences(
operation_sequence_id,
routing_sequence_id,
operation_seq_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
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,
request_id,
program_application_id,
program_id,
program_update_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,
implementation_date)
Values(
l_operation_rec.operation_sequence_id,
l_operation_rec.routing_sequence_id,
l_operation_rec.operation_seq_num,
sysdate,
l_UserId,
sysdate,
l_UserId,
l_LoginId,
l_operation_rec.standard_operation_id,
l_operation_rec.department_id,
l_operation_rec.operation_lead_time_percent,
l_operation_rec.minimum_transfer_quantity,
l_operation_rec.count_point_type,
l_operation_rec.operation_description,
l_operation_rec.effectivity_date,
l_operation_rec.disable_date,
l_operation_rec.backflush_flag,
l_operation_rec.option_dependent_flag,
l_operation_rec.attribute_category,
l_operation_rec.attribute1,
l_operation_rec.attribute2,
l_operation_rec.attribute3,
l_operation_rec.attribute4,
l_operation_rec.attribute5,
l_operation_rec.attribute6,
l_operation_rec.attribute7,
l_operation_rec.attribute8,
l_operation_rec.attribute9,
l_operation_rec.attribute10,
l_operation_rec.attribute11,
l_operation_rec.attribute12,
l_operation_rec.attribute13,
l_operation_rec.attribute14,
l_operation_rec.attribute15,
l_RequestId,
l_ApplicationId,
l_ProgramId,
l_ProgramUpdate,
l_operation_rec.operation_type,
l_operation_rec.reference_flag,
l_operation_rec.process_op_seq_id,
l_operation_rec.line_op_seq_id,
l_operation_rec.yield,
l_operation_rec.cumulative_yield,
l_operation_rec.reverse_cumulative_yield,
l_operation_rec.labor_time_calc,
l_operation_rec.machine_time_calc,
l_operation_rec.total_time_calc,
l_operation_rec.labor_time_user,
l_operation_rec.machine_time_user,
l_operation_rec.total_time_user,
l_operation_rec.net_planning_percent,
l_operation_rec.include_in_rollup,
l_operation_rec.operation_yield_enabled,
l_operation_rec.effectivity_date) ;
PROCEDURE UpdateOperation(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2,
p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
) is
l_api_name CONSTANT VARCHAR2(30) := 'UpdateOperation';
l_ProgramUpdate DATE;
Select *
From bom_operation_sequences bos
Where bos.operation_sequence_id = P_OpSeqId
Or (bos.routing_sequence_id = P_RtgSeqId and
nvl(bos.operation_type, g_event) =
nvl(P_OpType, g_event) and
bos.operation_seq_num = P_SeqNum and
bos.effectivity_date = decode(P_OpType,
g_process, bos.effectivity_date,
g_LineOp, bos.effectivity_date,
P_EffDate));
SAVEPOINT UpdateOperation_Pvt;
l_ProgramUpdate := null;
l_ProgramUpdate := sysdate;
update bom_operation_sequences set
operation_seq_num = l_operation_rec.new_operation_seq_num,
last_update_date = sysdate,
last_updated_by = l_UserId,
creation_date = sysdate,
created_by = l_UserId,
last_update_login = l_LoginId,
standard_operation_id = l_operation_rec.standard_operation_id,
department_id = l_operation_rec.department_id,
operation_lead_time_percent =
l_operation_rec.operation_lead_time_percent,
minimum_transfer_quantity = l_operation_rec.minimum_transfer_quantity,
count_point_type = l_operation_rec.count_point_type,
operation_description = l_operation_rec.operation_description,
effectivity_date = l_operation_rec.new_effectivity_date,
disable_date = l_operation_rec.disable_date,
backflush_flag = l_operation_rec.backflush_flag,
option_dependent_flag = l_operation_rec.option_dependent_flag,
attribute_category = l_operation_rec.attribute_category,
attribute1 = l_operation_rec.attribute1,
attribute2 = l_operation_rec.attribute2,
attribute3 = l_operation_rec.attribute3,
attribute4 = l_operation_rec.attribute4,
attribute5 = l_operation_rec.attribute5,
attribute6 = l_operation_rec.attribute6,
attribute7 = l_operation_rec.attribute7,
attribute8 = l_operation_rec.attribute8,
attribute9 = l_operation_rec.attribute9,
attribute10 = l_operation_rec.attribute10,
attribute11 = l_operation_rec.attribute11,
attribute12 = l_operation_rec.attribute12,
attribute13 = l_operation_rec.attribute13,
attribute14 = l_operation_rec.attribute14,
attribute15 = l_operation_rec.attribute15,
request_id = l_RequestId,
program_application_id = l_ApplicationId,
program_id = l_ProgramId,
program_update_date = l_ProgramUpdate,
reference_flag = l_operation_rec.reference_flag,
process_op_seq_id = l_operation_rec.process_op_seq_id,
line_op_seq_id = l_operation_rec.line_op_seq_id,
yield = l_operation_rec.yield,
cumulative_yield = l_operation_rec.cumulative_yield,
reverse_cumulative_yield = l_operation_rec.reverse_cumulative_yield,
labor_time_calc = l_operation_rec.labor_time_calc,
machine_time_calc = l_operation_rec.machine_time_calc,
total_time_calc = l_operation_rec.total_time_calc,
labor_time_user = l_operation_rec.labor_time_user,
machine_time_user = l_operation_rec.machine_time_user,
total_time_user = l_operation_rec.total_time_user,
net_planning_percent = l_operation_rec.net_planning_percent,
include_in_rollup = l_operation_rec.include_in_rollup,
operation_yield_enabled = l_operation_rec.operation_yield_enabled
Where operation_sequence_id = l_operation_rec.operation_sequence_id
Or (routing_sequence_id = l_operation_rec.routing_sequence_id
and nvl(operation_type, g_event) =
nvl(l_operation_rec.operation_type, g_event)
and operation_seq_num = l_operation_rec.operation_seq_num
and effectivity_date = decode(l_operation_rec.operation_type,
g_process, effectivity_date,
g_LineOp, effectivity_date,
l_operation_rec.effectivity_date));
UPDATE BOM_INVENTORY_COMPONENTS bic SET
bic.OPERATION_LEAD_TIME_PERCENT = l_operation_rec.operation_lead_time_percent
WHERE bic.OPERATION_SEQ_NUM = l_operation_rec.new_operation_seq_num
and bic.BILL_SEQUENCE_ID =
(select bom.BILL_SEQUENCE_ID
from BOM_BILL_OF_MATERIALS bom,
BOM_OPERATIONAL_ROUTINGS bor
where bor.routing_sequence_id = l_operation_rec.routing_sequence_id
and nvl(bor.alternate_routing_designator,'NONE') =
nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NONE')
and bom.ASSEMBLY_ITEM_ID = bor.assembly_item_id
and bom.ORGANIZATION_ID = bor.organization_id
);
ROLLBACK TO UpdateOperation_Pvt;
ROLLBACK TO UpdateOperation_Pvt;
ROLLBACK TO UpdateOperation_Pvt;
End UpdateOperation;
PROCEDURE DeleteOperation(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2,
p_delete_group IN VARCHAR2,
p_description IN VARCHAR2 := Null,
p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
) is
l_api_name CONSTANT VARCHAR2(30) := 'DeleteOperation';
l_DeleteGrpSeqId number := null;
Select bos.operation_sequence_id,
bor.routing_sequence_id,
bor.assembly_item_id,
bor.organization_id,
bor.alternate_routing_designator,
bor.routing_type
From bom_operational_routings bor,
bom_operation_sequences bos
Where bor.routing_sequence_id = bos.routing_sequence_id
and (bos.operation_sequence_id = P_OpSeqId
Or
(bos.routing_sequence_id = P_RtgSeqId and
nvl(bos.operation_type, g_event) =
nvl(P_OpType, g_event) and
bos.operation_seq_num = P_SeqNum and
bos.effectivity_date = decode(P_OpType,
g_process, bos.effectivity_date,
g_LineOp, bos.effectivity_date,
P_EffDate))
);
Select delete_group_sequence_id
From bom_delete_groups
Where delete_group_name = p_delete_group
And organization_id = P_OrgId;
l_operation constant number := 5; -- delete type
SAVEPOINT DeleteOperation_Pvt;
l_DeleteGrpSeqId := null;
l_DeleteGrpSeqId := l_DelGrp_rec.delete_group_sequence_id;
End loop; -- get existing delete group
l_ReturnCode := MODAL_DELETE.DELETE_MANAGER_OI(
new_group_seq_id => l_DeleteGrpSeqId,
name => p_delete_group,
group_desc => p_description,
org_id => l_OldOper_rec.organization_id,
bom_or_eng => l_OldOper_rec.routing_type,
del_type => l_operation,
ent_bill_seq_id => null,
ent_rtg_seq_id => l_OldOper_rec.routing_sequence_id,
ent_inv_item_id => l_OldOper_rec.assembly_item_id,
ent_alt_designator => l_OldOper_rec.alternate_routing_designator,
ent_comp_seq_id => null,
ent_op_seq_id => l_OldOper_rec.operation_sequence_id,
user_id => l_UserId,
err_text => l_msg_data
);
p_pkg_name => 'MODAL_DELETE',
p_procedure_name => 'DELETE_MANAGER_OI',
p_error_text => l_msg_data
);
End if; -- SQL error in modal delete
End loop; -- Add to delete group
ROLLBACK TO DeleteOperation_Pvt;
ROLLBACK TO DeleteOperation_Pvt;
ROLLBACK TO DeleteOperation_Pvt;
End DeleteOperation;