[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 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
-- Bug 10316535.
-- ,1 Reference_Flag -- copied always referenced
,bosv.reference_flag
,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
,bosv.OPERATION_SEQUENCE_ID OPERATION_SEQUENCE_ID
,bosv.STANDARD_OPERATION_ID STANDARD_OPERATION_ID
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
, null Operation_Sequence_Number
, null Operation_Type
, null Op_Start_Effective_Date
, borv.resource_seq_num Resource_Sequence_Number
, borv.resource_code Resource_Code
, borv.activity Activity
, borv.standard_rate_flag Standard_Rate_Flag
, borv.assigned_units Assigned_Units
, borv.usage_rate_or_amount Usage_Rate_Or_Amount
, borv.usage_rate_or_amount_inverse Usage_Rate_Or_Amount_Inverse
, borv.basis_type Basis_Type
, borv.schedule_flag Schedule_Flag
, borv.resource_offset_percent Resource_offset_percent
, borv.autocharge_type Autocharge_type
, borv.substitute_group_num Substitute_group_number
, borv.schedule_seq_num Schedule_sequence_number
, borv.principle_flag Principle_flag
, borv.attribute_category Attribute_category
, borv.attribute1 Attribute1
, borv.attribute2 Attribute2
, borv.attribute3 Attribute3
, borv.attribute4 Attribute4
, borv.attribute5 Attribute5
, borv.attribute6 Attribute6
, borv.attribute7 Attribute7
, borv.attribute8 Attribute8
, borv.attribute9 Attribute9
, borv.attribute10 Attribute10
, borv.attribute11 Attribute11
, borv.attribute12 Attribute12
, borv.attribute13 Attribute13
, borv.attribute14 Attribute14
, borv.attribute15 Attribute15
, borv.original_system_reference Original_system_reference
,'UPDATE' Transaction_type
, null Return_status
, borv.setup_code Setup_type
, null Row_identifier
From bom_operation_resources_v borv
Where borv.operation_sequence_id = p_op_sequence_id;
select
l_assembly_item_name Assembly_Item_Name
, l_org_code Organization_Code
, p_alternate_code_to Alternate_Routing_Code
, null Operation_Sequence_Number
, null Operation_Type
, null Op_Start_Effective_Date
, bsorv.Resource_Code Sub_Resource_Code
, bsorv.Resource_Code New_Sub_Resource_Code
, bsorv.Substitute_Group_Num Substitute_Group_Number
, bsorv.Schedule_Seq_Num Schedule_Sequence_Number
, bsorv.Replacement_Group_Num Replacement_Group_Number
, bsorv.Replacement_Group_Num New_Replacement_Group_Number
, bsorv.Activity Activity
, bsorv.Standard_Rate_Flag Standard_Rate_Flag
, bsorv.Assigned_Units Assigned_Units
, bsorv.Usage_Rate_Or_Amount Usage_Rate_Or_Amount
, bsorv.Usage_Rate_Or_Amount_Inverse Usage_Rate_Or_Amount_Inverse
, bsorv.Basis_Type Basis_Type
, bsorv.Basis_Type New_Basis_Type
, bsorv.Schedule_Flag Schedule_Flag
, bsorv.Resource_Offset_Percent Resource_Offset_Percent
, bsorv.Autocharge_Type Autocharge_Type
, bsorv.principle_flag Principle_Flag
, bsorv.Attribute_category Attribute_category
, bsorv.Attribute1 Attribute1
, bsorv.Attribute2 Attribute2
, bsorv.Attribute3 Attribute3
, bsorv.Attribute4 Attribute4
, bsorv.Attribute5 Attribute5
, bsorv.Attribute6 Attribute6
, bsorv.Attribute7 Attribute7
, bsorv.Attribute8 Attribute8
, bsorv.Attribute9 Attribute9
, bsorv.Attribute10 Attribute10
, bsorv.Attribute11 Attribute11
, bsorv.Attribute12 Attribute12
, bsorv.Attribute13 Attribute13
, bsorv.Attribute14 Attribute14
, bsorv.Attribute15 Attribute15
, bsorv.Original_System_Reference Original_System_Reference
, 'UPDATE' Transaction_Type
, null Return_Status
, bsorv.Setup_code Setup_Type
, bsorv.resource_id Resource_id
from BOM_SUB_OPERATION_RESOURCES_V BSORV
Where bsorv.operation_sequence_id = p_op_sequence_id;
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);
select count(*) into l_diff_count
from (select department_id from bom_standard_operations where standard_operation_id = stdop.standard_operation_id) a,
(select department_id from bom_standard_operations where organization_id = p_organization_id and line_id = p_line_id_to
and operation_code = stdop.standard_operation_code and operation_type = stdop.operation_type) b
where a.department_id <> b.department_id;
l_operation_tbl(j).Delete_Group_Name := null;
l_operation_tbl(j).Delete_Group_Name := l_operations_rec.Delete_Group_Name;
select count(1) into l_op_res_cnt
from bom_std_op_resources bsor
where bsor.Standard_Operation_id = l_operations_rec.Standard_Operation_id
and bsor.resource_seq_num = l_op_res_rec.Resource_Sequence_Number;
l_op_resource_tbl(k).Transaction_type := 'UPDATE';
select count(1) into l_op_res_cnt
from BOM_STD_SUB_OP_RESOURCES bsor
where bsor.Standard_Operation_id = l_operations_rec.Standard_Operation_id
and bsor.resource_id = l_op_sub_res_rec.resource_id
and bsor.substitute_group_num = l_op_sub_res_rec.Substitute_Group_Number
and bsor.replacement_group_num = l_op_sub_res_rec.Replacement_Group_Number
and bsor.basis_type = l_op_sub_res_rec.Basis_Type;
l_sub_resource_tbl(l).Transaction_type := 'UPDATE';
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);