[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select assembly_item_id,
organization_id,
alternate_routing_designator
From bom_operational_routings
Where routing_sequence_id = P_RtgSeqId;
Select bor.routing_sequence_id
From bom_operational_routings bor
Where bor.assembly_item_id = P_AssyId
And bor.organization_id = P_OrgId
And nvl(bor.alternate_routing_designator, 'PRIMARY ALT') =
nvl(P_Alternate , 'PRIMARY ALT');
Select organization_id
From mtl_parameters
Where organization_code = P_Code;
Select line_id
From wip_lines
Where organization_id = P_Organization_Id
And line_code = P_Code;
Select routing_sequence_id,
completion_subinventory,
completion_locator_id
From bom_operational_routings
Where assembly_item_id = P_AssyId
And organization_id = P_OrgId
And nvl(alternate_routing_designator, 'primary alternate') =
nvl(P_Alt, 'primary alternate');
Select assembly_item_id,
completion_subinventory,
completion_locator_id
From bom_operational_routings
Where routing_sequence_id = P_SeqId
And organization_id = P_OrgId
And nvl(alternate_routing_designator, 'Primary Alternate') =
nvl(P_Alternate, 'Primary Alternate');
Select 1 dummy
From dual
Where not exists (
Select null
From mtl_parameters
Where organization_id = P_OrgId);
Select 1 dummy
From dual
Where not exists (
Select null
From bom_alternate_designators
Where organization_id = P_OrgId
And alternate_designator_code = P_Alt);
Select 1 dummy
From dual
Where not exists (
select null
from mtl_system_items
where organization_id = P_Org
and inventory_item_id = P_Item);
Select 1 dummy
From dual
Where exists (
select null
from bom_operational_routings
where routing_sequence_id <> P_RtgSeqId
and assembly_item_id = P_AssyId
and organization_id = P_OrgId
and nvl(alternate_routing_designator, 'Primary Alternate')
= nvl(P_Alternate, 'Primary Alternate'));
Select 1 dummy
From dual
Where not exists (
select null
from bom_operational_routings
where organization_id = P_OrgId
and assembly_item_id = P_AssyId
and alternate_routing_designator is null
and ( routing_type = P_RtgType OR routing_type = 1));
Select 1
From dual
Where not exists (
Select null
From mtl_system_items
Where organization_id = P_OrgId
and inventory_item_id = P_AssyId
and bom_item_type <> 3
and bom_enabled_flag = 'Y'
and pick_components_flag = 'N'
and eng_item_flag =
decode(P_RtgType, 2, eng_item_flag, 'N'));
Select 1 dummy
From dual
Where not exists (
select null
from bom_operational_routings
where routing_sequence_id = P_RtgSeqId);
Select 1 dummy
From dual
Where not exists (
select null
from bom_operational_routings bor
where bor.routing_sequence_id = P_cmn_rtg_id
and nvl(bor.alternate_routing_designator,
'Primary Alternate') = nvl(P_alt_desg, 'Primary Alternate')
and bor.common_routing_sequence_id =
bor.routing_sequence_id
and bor.assembly_item_id <> P_item_id
and bor.organization_id = P_org_id
and bor.routing_type =
decode(P_rtg_type, 1, 1, bor.routing_type));
Select msi.inventory_asset_flag,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
msi.location_control_code,
mp.stock_locator_control_code
from mtl_system_items msi,
mtl_parameters mp
where msi.inventory_item_id = P_ItemId
and msi.organization_id = P_OrgId
and mp.organization_id = msi.organization_id;
Select locator_type
From mtl_secondary_inventories
Where secondary_inventory_name = P_SubInv
And organization_id = P_OrgId
And nvl(disable_date,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
And ((P_Asset = 1 and quantity_tracked = 1) or
(nvl(P_Asset, 0) <> 1 and
((P_Inv_Asst = 'Y' and asset_inventory = 1
and quantity_tracked = 1)
or (P_Inv_Asst = 'N')))
);
Select locator_type
From mtl_secondary_inventories sub,
mtl_item_sub_inventories item
Where item.organization_id = sub.organization_id
And item.secondary_inventory = sub.secondary_inventory_name
And item.inventory_item_id = P_ItemId
And sub.secondary_inventory_name = P_SubInv
And sub.organization_id = P_OrgId
And nvl(sub.disable_date,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
And ((P_Asset = 1 and sub.quantity_tracked = 1) or
(nvl(P_Asset, 0) <> 1 and
((P_Inv_Asst = 'Y' and sub.asset_inventory = 1 and
sub.quantity_tracked = 1) or (P_Inv_Asst = 'N'))
)
);
select 1 dummy
from sys.dual
where not exists(
select null
from mtl_item_locations
where inventory_location_id = P_Location
and organization_id = P_OrgId
and subinventory_code = P_SubInventory
and nvl(disable_date, trunc(SYSDATE)+1) > trunc(SYSDATE));
Select 1 dummy
from dual
where not exists(
select null
from mtl_item_locations loc,
mtl_secondary_locators item
where loc.inventory_location_id = P_Location
and loc.organization_id = P_OrgId
and loc.subinventory_code = P_SubInventory
and nvl(loc.disable_date,trunc(SYSDATE)+1) > trunc(SYSDATE)
and loc.inventory_location_id = item.secondary_locator
and loc.organization_id = item.organization_id
and item.inventory_item_id = P_ItemId);
Select 'x' dummy
From dual
Where not exists (
Select null
From wip_lines
Where line_id = p_line_id);
Select 'x' dummy
From dual
Where exists (
Select null
From bom_operational_routings bor
Where bor.assembly_item_id = p_item_id
And bor.organization_id = p_org_id
And nvl(bor.alternate_routing_designator,
'Primary Alternate') <>
nvl(p_alternate, 'Primary Alternate')
And line_id = p_line_id
And bor.mixed_model_map_flag = g_yes);
Select 'x' dummy
From dual
Where exists (
Select null
From bom_operational_routings bor
Where bor.assembly_item_id = p_item_id
And bor.organization_id = p_org_id
And nvl(bor.alternate_routing_designator,
'Primary Alternate') <>
nvl(p_alternate, 'Primary Alternate')
And bor.priority = p_priority);
Select 'x' dummy
From dual
Where exists (
Select null
From bom_operational_routings bor
Where bor.assembly_item_id = p_item_id
And bor.organization_id = p_org_id
And nvl(bor.alternate_routing_designator,
'Primary Alternate') <>
nvl(p_alternate, 'Primary Alternate')
And bor.ctp_flag = g_yes);
Select bor.line_id
From bom_operational_routings bor
Where bor.routing_sequence_id = P_RtgSeqId
And exists (
Select null
From bom_operation_sequences bos
Where bos.routing_sequence_id = bor.routing_sequence_id
And bos.standard_operation_id is not null
);
Fnd_Message.Set_Name('BOM', 'BOM_CANNOT_UPDATE_OI');
Select
bom_operational_routings_s.nextval routing_sequence_id
From dual;
l_ProgramUpdate date;
End if; -- validate before inserting
l_ProgramUpdate := null;
l_ProgramUpdate := sysdate;
Insert into bom_operational_routings(
routing_sequence_id,
assembly_item_id,
organization_id,
alternate_routing_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
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,
request_id,
program_application_id,
program_id,
program_update_date,
line_id,
cfm_routing_flag,
mixed_model_map_flag,
priority,
ctp_flag,
total_product_cycle_time,
pending_from_ecn)
values(
l_routing_rec.routing_sequence_id,
l_routing_rec.assembly_item_id,
l_routing_rec.organization_id,
l_routing_rec.alternate_routing_designator,
sysdate,
l_UserId,
sysdate,
l_UserId,
l_LoginId,
l_routing_rec.routing_type,
l_routing_rec.common_assembly_item_id,
l_routing_rec.common_routing_sequence_id,
l_routing_rec.routing_comment,
l_routing_rec.completion_subinventory,
l_routing_rec.completion_locator_id,
l_routing_rec.attribute_category,
l_routing_rec.attribute1,
l_routing_rec.attribute2,
l_routing_rec.attribute3,
l_routing_rec.attribute4,
l_routing_rec.attribute5,
l_routing_rec.attribute6,
l_routing_rec.attribute7,
l_routing_rec.attribute8,
l_routing_rec.attribute9,
l_routing_rec.attribute10,
l_routing_rec.attribute11,
l_routing_rec.attribute12,
l_routing_rec.attribute13,
l_routing_rec.attribute14,
l_routing_rec.attribute15,
l_RequestId,
l_ApplicationId,
l_ProgramId,
l_ProgramUpdate,
l_routing_rec.line_id,
l_routing_rec.cfm_routing_flag,
l_routing_rec.mixed_model_map_flag,
l_routing_rec.priority,
l_routing_rec.ctp_flag,
l_routing_rec.total_product_cycle_time,
l_routing_rec.pending_from_ecn);
insert into mtl_rtg_item_revisions(
inventory_item_id,
organization_id,
process_revision,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
implementation_date,
effectivity_date)
select
l_routing_rec.assembly_item_id,
mp.organization_id,
mp.starting_revision,
sysdate,
l_UserId,
sysdate,
l_UserId,
l_LoginId,
sysdate,
sysdate
from mtl_parameters mp
where mp.organization_id = l_routing_rec.organization_id
and not exists (
select null
from mtl_rtg_item_revisions
where organization_id = l_routing_rec.organization_id
and inventory_item_id = l_routing_rec.assembly_item_id);
PROCEDURE UpdateRouting
( 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_routing_rec IN ROUTING_REC_TYPE := G_MISS_ROUTING_REC,
x_routing_rec IN OUT NOCOPY ROUTING_REC_TYPE
) is
l_api_name CONSTANT VARCHAR2(30) := 'UpdateRouting';
l_ProgramUpdate date;
Select *
From bom_operational_routings bor
Where bor.routing_sequence_id = p_routing_seq_id
Or (bor.assembly_item_id = p_assy_item_id and
bor.organization_id = p_org_id and
nvl(bor.alternate_routing_designator,
'primary alternate') =
nvl(p_alternate, 'primary alternate')
);
SAVEPOINT UpdateRouting_Pvt;
l_ProgramUpdate := null;
l_ProgramUpdate := sysdate;
update bom_operational_routings set
last_update_date = sysdate,
last_updated_by = l_UserId,
-- creation_date = nvl(creation_date,sysdate), /* Commented for Bug 3271195 */
-- created_by = l_UserId, /* Commented for Bug 3271195 */
last_update_login = l_LoginId,
common_assembly_item_id = l_routing_rec.common_assembly_item_id,
common_routing_sequence_id = l_routing_rec.common_routing_sequence_id,
routing_comment = l_routing_rec.routing_comment,
completion_subinventory = l_routing_rec.completion_subinventory,
completion_locator_id = l_routing_rec.completion_locator_id,
attribute_category = l_routing_rec.attribute_category,
attribute1 = l_routing_rec.attribute1,
attribute2 = l_routing_rec.attribute2,
attribute3 = l_routing_rec.attribute3,
attribute4 = l_routing_rec.attribute4,
attribute5 = l_routing_rec.attribute5,
attribute6 = l_routing_rec.attribute6,
attribute7 = l_routing_rec.attribute7,
attribute8 = l_routing_rec.attribute8,
attribute9 = l_routing_rec.attribute9,
attribute10 = l_routing_rec.attribute10,
attribute11 = l_routing_rec.attribute11,
attribute12 = l_routing_rec.attribute12,
attribute13 = l_routing_rec.attribute13,
attribute14 = l_routing_rec.attribute14,
attribute15 = l_routing_rec.attribute15,
request_id = l_RequestId,
program_application_id = l_ApplicationId,
program_id = l_ProgramId,
program_update_date = l_ProgramUpdate,
line_id = l_routing_rec.line_id,
cfm_routing_flag = l_routing_rec.cfm_routing_flag,
mixed_model_map_flag = l_routing_rec.mixed_model_map_flag,
priority = l_routing_rec.priority,
ctp_flag = l_routing_rec.ctp_flag,
total_product_cycle_time = l_routing_rec.total_product_cycle_time
Where routing_sequence_id = l_routing_rec.routing_sequence_id
Or (assembly_item_id = l_routing_rec.assembly_item_id and
organization_id = l_routing_rec.organization_id and
nvl(alternate_routing_designator, 'Primary Alternate') =
nvl(l_routing_rec.alternate_routing_designator, 'Primary Alternate')
);
ROLLBACK TO UpdateRouting_Pvt;
ROLLBACK TO UpdateRouting_Pvt;
ROLLBACK TO UpdateRouting_Pvt;
End UpdateRouting;
PROCEDURE DeleteRouting
( 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_routing_rec IN ROUTING_REC_TYPE := G_MISS_ROUTING_REC,
x_routing_rec IN OUT NOCOPY ROUTING_REC_TYPE
) is
l_api_name CONSTANT VARCHAR2(30) := 'DeleteRouting';
l_DeleteGrpSeqId number := null;
Select bor.routing_sequence_id,
bor.assembly_item_id,
bor.organization_id,
bor.alternate_routing_designator,
bor.routing_type
From bom_operational_routings bor
Where bor.routing_sequence_id = p_routing_seq_id
Or (bor.assembly_item_id = p_assy_item_id and
bor.organization_id = p_org_id and
nvl(bor.alternate_routing_designator,
'primary alternate') =
nvl(p_alternate, 'primary alternate')
);
Select delete_group_sequence_id
From bom_delete_groups
Where delete_group_name = p_delete_group
And organization_id = P_OrgId;
l_routing constant number := 3; -- delete type
SAVEPOINT DeleteRouting_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_OldRtg_rec.organization_id,
bom_or_eng => l_OldRtg_rec.routing_type,
del_type => l_routing,
ent_bill_seq_id => null,
ent_rtg_seq_id => l_OldRtg_rec.routing_sequence_id,
ent_inv_item_id => l_OldRtg_rec.assembly_item_id,
ent_alt_designator => l_OldRtg_rec.alternate_routing_designator,
ent_comp_seq_id => null,
ent_op_seq_id => null,
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 DeleteRouting_Pvt;
ROLLBACK TO DeleteRouting_Pvt;
ROLLBACK TO DeleteRouting_Pvt;
END DeleteRouting;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type
)
VALUES (p_assembly_item_id,
p_organization_id,
p_copy_request_id,
NULL,
bom_copy_bill.get_current_item_rev
(p_assembly_item_id,
p_organization_id,
SYSDATE
),
x_msg_data,
'BOM_COPY',
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
'E'
);