[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select organization_code into l_org_code
from mtl_parameters
where organization_id = p_org_id;
select line_code into l_line_code
from wip_lines
where organization_id = p_org_id
and line_id = p_line_id;
select count(*) into l_total
from bom_standard_operations
where organization_id = p_org_id
and line_id = p_line_id
and operation_code = p_std_op_code
and operation_type = p_op_type;
select count(*) into l_total
from bom_bill_of_materials
where assembly_item_id = p_item_id
and organization_id = p_org_id
and nvl(alternate_bom_designator,'NONE') = nvl(p_alternate,'NONE');
X_last_update_login => '',
X_program_application_id=> '',
X_program_id => '',
X_request_id => ''
);
Select
bor.routing_sequence_id,
bor.common_routing_sequence_id,
bor.assembly_item_id,
bor.common_assembly_item_id,
bor.alternate_routing_designator
From
bom_operational_routings bor, mtl_system_items_kfv msi_kfv
Where
bor.organization_id = p_organization_id
and bor.organization_id = msi_kfv.organization_id
and bor.assembly_item_id = msi_kfv.inventory_item_id
and bor.line_id = p_line_id_from
and bor.cfm_routing_flag = 1
and bor.routing_type = 1
and ((p_mode = 1 and msi_kfv.bom_item_type <> 5) or (p_mode = 2 and msi_kfv.bom_item_type = 5))
and ((p_alternate_code_from is NULL and bor.alternate_routing_designator is NULL)
or p_alternate_code_from = bor.alternate_routing_designator)
and (p_product_family_id is NULL or p_product_family_id = msi_kfv.product_family_item_id)
and (p_assembly_name_from is NULL or p_assembly_name_from <= msi_kfv.concatenated_segments)
and (p_assembly_name_to is NULL or p_assembly_name_to >= msi_kfv.concatenated_segments)
and (p_tpct_from is NULL or bor.total_product_cycle_time >= p_tpct_from)
and (p_tpct_to is NULL or bor.total_product_cycle_time <= p_tpct_to)
and (p_lineop_code is NULL or exists (
select 1 from bom_operation_sequences_v bosv
where bosv.routing_sequence_id = bor.common_routing_sequence_id
and p_lineop_code = bosv.standard_operation_code
and bosv.operation_type = 3)
)
and (p_process_code is NULL or exists (
select 1 from bom_operation_sequences_v bosv
where bosv.routing_sequence_id = bor.common_routing_sequence_id
and p_process_code = bosv.standard_operation_code
and bosv.operation_type = 2)
)
Order by
bor.routing_sequence_id
;
Select
bor.routing_sequence_id,
bor.common_routing_sequence_id,
bor.assembly_item_id,
bor.common_assembly_item_id,
bor.alternate_routing_designator
From
bom_operational_routings bor, mtl_system_items_kfv msi_kfv
Where
bor.organization_id = p_organization_id
and bor.organization_id = msi_kfv.organization_id
and bor.assembly_item_id = msi_kfv.inventory_item_id
and bor.line_id = p_line_id_from
and bor.cfm_routing_flag = 1
and bor.routing_type = 1
and ((p_mode = 1 and msi_kfv.bom_item_type <> 5) or (p_mode = 2 and msi_kfv.bom_item_type = 5))
and ((p_alternate_code_from is NULL and bor.alternate_routing_designator is NULL)
or p_alternate_code_from = bor.alternate_routing_designator)
and (p_product_family_id is NULL or p_product_family_id = msi_kfv.product_family_item_id)
and (p_assembly_name_from is NULL or p_assembly_name_from <= msi_kfv.concatenated_segments)
and (p_assembly_name_to is NULL or p_assembly_name_to >= msi_kfv.concatenated_segments)
and (p_tpct_from is NULL or bor.total_product_cycle_time >= p_tpct_from)
and (p_tpct_to is NULL or bor.total_product_cycle_time <= p_tpct_to)
and (p_lineop_code is NULL or exists (
select 1
from bom_operation_sequences bos1, bom_standard_operations bso1
where bos1.routing_sequence_id = bor.common_routing_sequence_id
and p_lineop_code = bso1.operation_code
and bos1.standard_operation_id = bso1.standard_operation_id
and bso1.organization_id = p_organization_id
and bso1.line_id = p_line_id_from
and bos1.operation_type = 3)
)
and (p_process_code is NULL or exists (
select 1
from bom_operation_sequences bos1, bom_standard_operations bso1
where bos1.routing_sequence_id = bor.common_routing_sequence_id
and p_process_code = bso1.operation_code
and bos1.standard_operation_id = bso1.standard_operation_id
and bso1.organization_id = p_organization_id
and bso1.line_id = p_line_id_from
and bos1.operation_type = 2)
)
Order by
bor.routing_sequence_id
;
Select
*
From
bom_bill_of_materials
Where
organization_id = p_org_id
and assembly_item_id = p_item_id
and (nvl(alternate_bom_designator,'NONE') = nvl(p_alternate,'NONE'))
;
Select
flm_util.get_key_flex_item(assembly_item_id,p_organization_id) Assembly_Item_Name
,l_org_code Organization_Code
,p_alternate_code_to Alternate_Routing_Code
,2 Eng_Routing_Flag --eng_routing_flag is oAlternate_Routing_Codepposite of routing_type
,flm_util.get_key_flex_item(common_assembly_item_id,p_organization_id) Common_Assembly_Item_Name
,routing_comment Routing_Comment
,completion_subinventory Completion_Subinventory
,flm_util.get_key_flex_location(completion_locator_id,p_organization_id) Completion_Location_Name
,l_line_code Line_Code
,cfm_routing_flag CFM_Routing_Flag
,2 Mixed_Model_Map_Flag -- mixed_model_map_flag has only one 'Y'=1 for an item
,priority Priority
,total_product_cycle_time Total_Cycle_Time
,2 CTP_Flag --ctp_flag: only one YES=1 for an item
,attribute_category Attribute_category
,attribute1 Attribute1
,attribute2 Attribute2
,attribute3 Attribute3
,attribute4 Attribute4
,attribute5 Attribute5
,attribute6 Attribute6
,attribute7 Attribute7
,attribute8 Attribute8
,attribute9 Attribute9
,attribute10 Attribute10
,attribute11 Attribute11
,attribute12 Attribute12
,attribute13 Attribute13
,attribute14 Attribute14
,attribute15 Attribute15
,original_system_reference Original_System_Reference
,'CREATE' Transaction_Type
,NULL Return_Status
,NULL Delete_Group_Name
,NULL DG_Description
From
bom_operational_routings_v
Where
routing_sequence_id = p_routing_sequence_id
;
Select
l_assembly_item_name
,l_org_code -- organization_code
,p_alternate_code_to
,process_revision revision
,effectivity_date start_effective_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,NULL
,'CREATE' -- transaction_type
,NULL -- return status
From
mtl_rtg_item_revisions mrir
Where
inventory_item_id = p_assembly_item_id
and organization_id = p_organization_id
;
Select
l_assembly_item_name Assembly_Item_Name
,l_org_code Organization_Code
,p_alternate_code_to Alternate_Routing_Code
,bosv.operation_seq_num Operation_Sequence_Number
,bosv.operation_type Operation_Type
,bosv.effectivity_date Start_Effective_Date
,bosv.operation_seq_num New_Operation_Sequence_Number
,bosv.effectivity_date New_Start_Effective_Date
,bosv.standard_operation_code Standard_Operation_Code
,bosv.department_code Department_Code
,bosv.operation_lead_time_percent Op_Lead_Time_Percent
,bosv.minimum_transfer_quantity Minimum_Transfer_Quantity
,bosv.count_point_type Count_Point_Type
,bosv.operation_description Operation_Description
,bosv.disable_date Disable_Date
,bosv.backflush_flag Backflush_Flag
,NULL Option_Dependent_Flag
,1 Reference_Flag -- copied always referenced
,bosv.process_seq_num Process_Seq_Number
,bosv.process_code Process_Code
,bosv.line_op_seq_num Line_Op_Seq_Number
,bosv.line_op_code Line_Op_Code
,bosv.yield Yield
,bosv.cumulative_yield Cumulative_Yield
,bosv.reverse_cumulative_yield Reverse_CUM_Yield
,bosv.labor_time_user User_Labor_Time
,bosv.machine_time_user User_Machine_Time
,100 Net_Planning_Percent --??????
,bosv.include_in_rollup Include_In_Rollup
,bosv.operation_yield_enabled Op_Yield_Enabled_Flag
,bosv.shutdown_type Shutdown_Type
,bosv.attribute_category Attribute_category
,bosv.attribute1 Attribute1
,bosv.attribute2 Attribute2
,bosv.attribute3 Attribute3
,bosv.attribute4 Attribute4
,bosv.attribute5 Attribute5
,bosv.attribute6 Attribute6
,bosv.attribute7 Attribute7
,bosv.attribute8 Attribute8
,bosv.attribute9 Attribute9
,bosv.attribute10 Attribute10
,bosv.attribute11 Attribute11
,bosv.attribute12 Attribute12
,bosv.attribute13 Attribute13
,bosv.attribute14 Attribute14
,bosv.attribute15 Attribute15
,bosv.original_system_reference Original_System_Reference
,'CREATE' Transaction_Type
,NULL Return_Status
,NULL Delete_Group_Name
,NULL DG_Description
From
bom_operation_sequences_v bosv
Where
bosv.routing_sequence_id = p_routing_sequence_id
and ((bosv.effectivity_date <= l_today and nvl(bosv.disable_date,l_today+1) > l_today)
or (bosv.effectivity_date > l_today and nvl(bosv.disable_date, bosv.effectivity_date+1) > bosv.effectivity_date))
Order by
bosv.operation_type desc
;
Select
l_assembly_item_name Assembly_Item_Name
,l_org_code Organization_Code
,p_alternate_code_to Alternate_Routing_Code
,bonv.operation_type Operation_Type
,bonv.from_seq_num From_Op_Seq_Number
,bos1.x_coordinate From_X_Coordinate
,bos1.y_coordinate From_Y_Coordinate
,bonv.from_effectivity_date From_Start_Effective_Date
,bonv.to_seq_num To_Op_Seq_Number
,bos2.x_coordinate To_X_Coordinate
,bos2.y_coordinate To_Y_Coordinate
,bonv.to_effectivity_date To_Start_Effective_Date
,bonv.from_seq_num New_From_Op_Seq_Number
,bonv.from_effectivity_date New_From_Start_Effective_Date
,bonv.to_seq_num New_To_Op_Seq_Number
,bonv.to_effectivity_date New_To_Start_Effective_Date
,bonv.transition_type Connection_Type
,bonv.planning_pct Planning_Percent
,bonv.attribute_category Attribute_category
,bonv.attribute1 Attribute1
,bonv.attribute2 Attribute2
,bonv.attribute3 Attribute3
,bonv.attribute4 Attribute4
,bonv.attribute5 Attribute5
,bonv.attribute6 Attribute6
,bonv.attribute7 Attribute7
,bonv.attribute8 Attribute8
,bonv.attribute9 Attribute9
,bonv.attribute10 Attribute10
,bonv.attribute11 Attribute11
,bonv.attribute12 Attribute12
,bonv.attribute13 Attribute13
,bonv.attribute14 Attribute14
,bonv.attribute15 Attribute15
,bonv.original_system_reference Original_System_Reference
,'CREATE' Transaction_Type
,NULL Return_Status
From
bom_operation_networks_v bonv,
bom_operation_sequences bos1,
bom_operation_sequences bos2
Where
bonv.routing_sequence_id = p_routing_sequence_id
and bonv.from_op_seq_id = bos1.operation_sequence_id
and ((bos1.effectivity_date <= l_today and nvl(bos1.disable_date,l_today+1) > l_today)
or (bos1.effectivity_date > l_today and nvl(bos1.disable_date, bos1.effectivity_date+1) > bos1.effectivity_date))
and bonv.to_op_seq_id = bos2.operation_sequence_id
and ((bos2.effectivity_date <= l_today and nvl(bos2.disable_date,l_today+1) > l_today)
or (bos2.effectivity_date > l_today and nvl(bos2.disable_date, bos2.effectivity_date+1) > bos2.effectivity_date))
;
Select distinct
standard_operation_id
,standard_operation_code
,operation_type
From
bom_operation_sequences_v bosv
Where
routing_sequence_id = p_routing_sequence_id
and ((effectivity_date <= l_today and nvl(disable_date,l_today+1) > l_today)
or (effectivity_date > l_today and nvl(disable_date, effectivity_date+1) > effectivity_date))
Order by
standard_operation_code
;
Select
*
From
bom_standard_operations
Where
standard_operation_id = p_standard_operation_id
;
Select
*
From
bom_std_op_resources
Where
standard_operation_id = p_standard_operation_id
;
Select
routing_sequence_id
From
bom_operational_routings
Where
assembly_item_id = p_item_id
and organization_id = p_org_id
and nvl(alternate_routing_designator,'NONE') = nvl(p_alternate,'NONE')
;
Select
operation_sequence_id
From
bom_operation_sequences
Where
routing_sequence_id = p_rtg_seq_id
and nvl(operation_type,0) = nvl(p_op_type,0)
and operation_seq_num = p_op_seq_num
;
select count(*) into dup
from bom_operational_routings bor
where bor.organization_id = p_organization_id
-- and bor.line_id = p_line_id_to
and bor.assembly_item_id = l_rtg_tbl(i).assembly_item_id
and bor.alternate_routing_designator = p_alternate_code_to;
l_rtg_tbl.DELETE(i);
select count(*) into dup
from bom_operational_routings bor
where bor.organization_id = p_organization_id
-- and bor.line_id = p_line_id_to
and bor.assembly_item_id = l_rtg_tbl(i).common_assembly_item_id
and bor.alternate_routing_designator = p_alternate_code_to;
select routing_sequence_id,common_routing_sequence_id,assembly_item_id,
common_assembly_item_id,alternate_routing_designator
into l_rtg_tbl(j)
from bom_operational_routings
where routing_sequence_id = l_rtg_tbl(i).common_routing_sequence_id;
l_rtg_header_rec.Delete_Group_Name := l_routing_header_rec.Delete_Group_Name;
b_std_op_pkg.Insert_Row( x_rowid =>new_row_id
,x_standard_operation_id =>new_std_op_id
,x_operation_code =>std_op_rec.operation_code
,x_operation_type =>std_op_rec.operation_type
,x_line_id =>p_line_id_to
,x_sequence_num =>std_op_rec.sequence_num
,x_organization_id =>std_op_rec.organization_id
,x_department_id =>std_op_rec.department_id
,x_last_update_date =>sysdate
,x_last_updated_by =>fnd_global.user_id
,x_creation_date =>sysdate
,x_created_by =>fnd_global.user_id
,x_last_update_login =>fnd_global.login_id
,x_minimum_transfer_quantity =>std_op_rec.minimum_transfer_quantity
,x_count_point_type =>std_op_rec.count_point_type
,x_operation_description =>std_op_rec.operation_description
,x_option_dependent_flag =>std_op_rec.option_dependent_flag
,x_attribute_category =>std_op_rec.attribute_category
,x_attribute1 =>std_op_rec.attribute1
,x_attribute2 =>std_op_rec.attribute2
,x_attribute3 =>std_op_rec.attribute3
,x_attribute4 =>std_op_rec.attribute4
,x_attribute5 =>std_op_rec.attribute5
,x_attribute6 =>std_op_rec.attribute6
,x_attribute7 =>std_op_rec.attribute7
,x_attribute8 =>std_op_rec.attribute8
,x_attribute9 =>std_op_rec.attribute9
,x_attribute10 =>std_op_rec.attribute10
,x_attribute11 =>std_op_rec.attribute11
,x_attribute12 =>std_op_rec.attribute12
,x_attribute13 =>std_op_rec.attribute13
,x_attribute14 =>std_op_rec.attribute14
,x_attribute15 =>std_op_rec.attribute15
,x_backflush_flag =>std_op_rec.backflush_flag
,x_wms_task_type =>std_op_rec.wms_task_type
,x_yield =>std_op_rec.yield
,x_operation_yield_enabled =>std_op_rec.operation_yield_enabled);
b_std_op_res_pkg.Insert_Row( x_rowid =>new_row_id
,x_standard_operation_id =>new_std_op_id
,x_resource_id =>std_op_res_rec.resource_id
,x_activity_id =>std_op_res_rec.activity_id
,x_last_update_date =>sysdate
,x_last_updated_by =>fnd_global.user_id
,x_creation_date =>sysdate
,x_created_by =>fnd_global.user_id
,x_last_update_login =>fnd_global.login_id
,x_resource_seq_num =>std_op_res_rec.resource_seq_num
,x_usage_rate_or_amount =>std_op_res_rec.usage_rate_or_amount
,x_usage_rate_or_amount_inverse =>std_op_res_rec.usage_rate_or_amount_inverse
,x_basis_type =>std_op_res_rec.basis_type
,x_autocharge_type =>std_op_res_rec.autocharge_type
,x_standard_rate_flag =>std_op_res_rec.standard_rate_flag
,x_assigned_units =>std_op_res_rec.assigned_units
,x_schedule_flag =>std_op_res_rec.schedule_flag
,x_attribute_category =>std_op_res_rec.attribute_category
,x_attribute1 =>std_op_res_rec.attribute1
,x_attribute2 =>std_op_res_rec.attribute2
,x_attribute3 =>std_op_res_rec.attribute3
,x_attribute4 =>std_op_res_rec.attribute4
,x_attribute5 =>std_op_res_rec.attribute5
,x_attribute6 =>std_op_res_rec.attribute6
,x_attribute7 =>std_op_res_rec.attribute7
,x_attribute8 =>std_op_res_rec.attribute8
,x_attribute9 =>std_op_res_rec.attribute9
,x_attribute10 =>std_op_res_rec.attribute10
,x_attribute11 =>std_op_res_rec.attribute11
,x_attribute12 =>std_op_res_rec.attribute12
,x_attribute13 =>std_op_res_rec.attribute13
,x_attribute14 =>std_op_res_rec.attribute14
,x_attribute15 =>std_op_res_rec.attribute15);
l_operation_tbl(j).Delete_Group_Name := null;
l_operation_tbl(j).Delete_Group_Name := l_operations_rec.Delete_Group_Name;
bom_bill_of_matls_pkg.Insert_Row(
X_Rowid =>new_row_id
,X_Assembly_Item_Id =>l_bom_rec.assembly_item_id
,X_Organization_Id =>l_bom_rec.organization_id
,X_Alternate_Bom_Designator =>p_alternate_code_to
,X_Last_Update_Date =>sysdate
,X_Last_Updated_By =>fnd_global.user_id
,X_Creation_Date =>sysdate
,X_Created_By =>fnd_global.user_id
,X_Last_Update_Login =>fnd_global.login_id
,X_Common_Assembly_Item_Id =>l_bom_rec.common_assembly_item_id
,X_Specific_Assembly_Comment =>l_bom_rec.Specific_Assembly_Comment
,X_Pending_From_Ecn =>l_bom_rec.Pending_From_Ecn
,X_Attribute_Category =>l_bom_rec.attribute_category
,X_Attribute1 =>l_bom_rec.attribute1
,X_Attribute2 =>l_bom_rec.attribute2
,X_Attribute3 =>l_bom_rec.attribute3
,X_Attribute4 =>l_bom_rec.attribute4
,X_Attribute5 =>l_bom_rec.attribute5
,X_Attribute6 =>l_bom_rec.attribute6
,X_Attribute7 =>l_bom_rec.attribute7
,X_Attribute8 =>l_bom_rec.attribute8
,X_Attribute9 =>l_bom_rec.attribute9
,X_Attribute10 =>l_bom_rec.attribute10
,X_Attribute11 =>l_bom_rec.attribute11
,X_Attribute12 =>l_bom_rec.attribute12
,X_Attribute13 =>l_bom_rec.attribute13
,X_Attribute14 =>l_bom_rec.attribute14
,X_Attribute15 =>l_bom_rec.attribute15
,X_Assembly_Type =>l_bom_rec.assembly_type
,X_Common_Bill_Sequence_Id =>l_to_common_seq_id
,X_Bill_Sequence_Id =>l_to_sequence_id
,X_Common_Organization_Id =>l_bom_rec.Common_Organization_Id
,X_Next_Explode_Date =>l_bom_rec.Next_Explode_Date
);