[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT item1.concatenated_segments Assembly_Item_Name,
p_target_orgcode Organization_Code, --myerrams, Select the Target Org code into the cursor
bor.alternate_routing_designator Alternate_Routing_Code,
DECODE(bor.routing_type, 1, 2, 2, 1, NULL) Eng_Routing_Flag,
item2.concatenated_segments Common_Assembly_Item_Name,
bor.routing_comment Routing_Comment,
bor.completion_subinventory Completion_Subinventory,
locators.concatenated_segments Completion_Location_Name,
wl.line_code Line_Code,
bor.cfm_Routing_Flag CFM_Routing_Flag,
bor.mixed_model_map_flag Mixed_Model_Map_Flag,
bor.priority Priority,
bor.total_product_cycle_time Total_Cycle_Time,
bor.ctp_flag CTP_Flag,
bor.attribute_category Attribute_category,
bor.attribute1 Attribute1,
bor.attribute2 Attribute2,
bor.attribute3 Attribute3,
bor.attribute4 Attribute4,
bor.attribute5 Attribute5,
bor.attribute6 Attribute6,
bor.attribute7 Attribute7,
bor.attribute8 Attribute8,
bor.attribute9 Attribute9,
bor.attribute10 Attribute10,
bor.attribute11 Attribute11,
bor.attribute12 Attribute12,
bor.attribute13 Attribute13,
bor.attribute14 Attribute14,
bor.attribute15 Attribute15,
bor.original_system_reference Original_System_Reference,
'CREATE' Transaction_Type,
NULL Return_Status,
NULL Delete_Group_Name,
NULL DG_Description,
NULL ser_start_op_seq,
NULL row_identifier
FROM bom_operational_routings bor,
wip_lines wl,
mtl_parameters org,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2,
mtl_item_locations_kfv locators
WHERE wl.line_id(+) = bor.line_id
AND org.organization_id = bor.organization_id
AND item1.organization_id = bor.organization_id
AND item1.inventory_item_id = bor.assembly_item_id
AND item2.inventory_item_id(+) = bor.common_assembly_item_id
AND item2.organization_id(+) = bor.organization_id
AND locators.inventory_location_id(+) = bor.completion_locator_id
AND locators.organization_id (+) = bor.organization_id
AND bor.organization_id = p_model_org_id --myerrams, Filter Records based on Model Org Id
Order by bor.alternate_routing_designator desc, bor.assembly_item_id; --myerrams, To copy Alternate Routings at the end.
SELECT item.concatenated_segments Assembly_Item_Name,
p_target_orgcode Organization_Code,
NULL Alternate_Routing_Code,
rev.Process_Revision Revision,
rev.Effectivity_Date Start_Effective_Date,
rev.Attribute_category Attribute_category,
rev.Attribute1 Attribute1,
rev.Attribute2 Attribute2,
rev.Attribute3 Attribute3,
rev.Attribute4 Attribute4,
rev.Attribute5 Attribute5,
rev.Attribute6 Attribute6,
rev.Attribute7 Attribute7,
rev.Attribute8 Attribute8,
rev.Attribute9 Attribute9,
rev.Attribute10 Attribute10,
rev.Attribute11 Attribute11,
rev.Attribute12 Attribute12,
rev.Attribute13 Attribute13,
rev.Attribute14 Attribute14,
rev.Attribute15 Attribute15,
NULL Original_System_Reference,
'CREATE' Transaction_Type,
NULL Return_Status,
NULL Row_Identifier
FROM mtl_rtg_item_revisions rev,
mtl_parameters org,
mtl_system_items_kfv item
WHERE org.organization_id = rev.organization_id
AND item.organization_id = rev.organization_id
AND item.inventory_item_id = rev.inventory_item_id
AND item.concatenated_segments = P_assembly_item_name
AND org.organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
SELECT item.concatenated_segments Assembly_Item_Name,
p_target_orgcode Organization_Code,
rtg.alternate_routing_designator Alternate_Routing_Code,
op_seq.Operation_Seq_Num Operation_Sequence_Number,
op_seq.Operation_Type Operation_Type,
op_seq.Effectivity_Date Start_Effective_Date,
NULL New_Operation_Sequence_Number,
NULL New_Start_Effective_Date,
op_seq.Standard_Operation_Code Standard_Operation_Code,
op_seq.Department_Code Department_Code,
op_seq.Operation_Lead_Time_Percent Op_Lead_Time_Percent,
op_seq.Minimum_Transfer_Quantity Minimum_Transfer_Quantity,
op_seq.Count_Point_Type Count_Point_Type,
op_seq.Operation_Description Operation_Description,
op_seq.Disable_Date Disable_Date,
op_seq.Backflush_Flag Backflush_Flag,
op_seq.Option_Dependent_Flag Option_Dependent_Flag,
op_seq.Reference_Flag Reference_Flag,
op_seq.Process_Seq_Num Process_Seq_Number,
op_seq.Process_Code Process_Code,
op_seq.Line_Op_Seq_Num Line_Op_Seq_Number,
op_seq.Line_Op_Code Line_Op_Code,
op_seq.Yield Yield,
op_seq.Cumulative_Yield Cumulative_Yield,
op_seq.Reverse_Cumulative_Yield Reverse_CUM_Yield,
op_seq.Labor_Time_User User_Labor_Time,
op_seq.Machine_Time_User User_Machine_Time,
op_seq.Net_Planning_Percent Net_Planning_Percent,
op_seq.Include_In_Rollup Include_In_Rollup,
op_seq.Operation_Yield_Enabled Op_Yield_Enabled_Flag,
op_seq.SHUTDOWN_TYPE Shutdown_Type,
op_seq.Attribute_category Attribute_category,
op_seq.Attribute1 Attribute1,
op_seq.Attribute2 Attribute2,
op_seq.Attribute3 Attribute3,
op_seq.Attribute4 Attribute4,
op_seq.Attribute5 Attribute5,
op_seq.Attribute6 Attribute6,
op_seq.Attribute7 Attribute7,
op_seq.Attribute8 Attribute8,
op_seq.Attribute9 Attribute9,
op_seq.Attribute10 Attribute10,
op_seq.Attribute11 Attribute11,
op_seq.Attribute12 Attribute12,
op_seq.Attribute13 Attribute13,
op_seq.Attribute14 Attribute14,
op_seq.Attribute15 Attribute15,
op_seq.Original_System_Reference Original_System_Reference,
'CREATE' Transaction_Type,
NULL Return_Status,
NULL Delete_Group_Name,
NULL DG_Description,
NULL Long_Description,
NULL Row_Identifier
FROM bom_operation_sequences_v op_seq,
bom_operational_routings rtg,
mtl_parameters org,
mtl_system_items_kfv item
WHERE rtg.routing_sequence_id = op_seq.routing_sequence_id
AND org.organization_id = rtg.organization_id
AND item.organization_id = rtg.organization_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.concatenated_segments = P_assembly_item_name
AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
SELECT item.concatenated_segments Assembly_Item_Name,
p_target_orgcode Organization_Code,
rtg.alternate_routing_designator Alternate_Routing_Code,
op_seq.operation_seq_num Operation_Sequence_Number,
op_seq.operation_type Operation_Type,
op_seq.effectivity_date Op_Start_Effective_Date,
bor.Resource_Seq_Num Resource_Sequence_Number,
br.Resource_Code Resource_Code,
ca.Activity Activity,
bor.Standard_Rate_Flag Standard_Rate_Flag,
bor.Assigned_Units Assigned_Units,
bor.Usage_Rate_Or_amount Usage_Rate_Or_Amount,
bor.Usage_Rate_Or_Amount_Inverse Usage_Rate_Or_Amount_Inverse,
bor.Basis_Type Basis_Type,
bor.Schedule_Flag Schedule_Flag,
bor.Resource_Offset_Percent Resource_Offset_Percent,
bor.Autocharge_Type Autocharge_Type,
bor.Substitute_Group_Num Substitute_Group_Number,
bor.Schedule_Seq_Num Schedule_Sequence_Number,
bor.Principle_Flag Principle_Flag,
bor.Attribute_category Attribute_category,
bor.Attribute1 Attribute1,
bor.Attribute2 Attribute2,
bor.Attribute3 Attribute3,
bor.Attribute4 Attribute4,
bor.Attribute5 Attribute5,
bor.Attribute6 Attribute6,
bor.Attribute7 Attribute7,
bor.Attribute8 Attribute8,
bor.Attribute9 Attribute9,
bor.Attribute10 Attribute10,
bor.Attribute11 Attribute11,
bor.Attribute12 Attribute12,
bor.Attribute13 Attribute13,
bor.Attribute14 Attribute14,
bor.Attribute15 Attribute15,
NULL Original_System_Reference,
'CREATE' Transaction_Type,
NULL Return_Status,
bst.Setup_Code Setup_Type,
NULL Row_Identifier
FROM bom_operation_resources bor,
bom_resources br,
cst_activities ca,
bom_setup_types bst,
bom_operation_sequences op_seq,
bom_operational_routings rtg,
mtl_system_items_kfv item,
mtl_parameters org
WHERE op_seq.operation_sequence_id = bor.operation_sequence_id
AND rtg.routing_sequence_id = op_seq.routing_sequence_id
AND org.organization_id = rtg.organization_id
AND item.organization_id = rtg.organization_id
AND item.inventory_item_id = rtg.assembly_item_id
AND br.resource_id = bor.resource_id
AND ca.activity_id(+) = bor.activity_id
AND bst.setup_id(+) = bor.setup_id
AND item.concatenated_segments = P_assembly_item_name
AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
SELECT item.concatenated_segments Assembly_Item_Name,
p_target_orgcode Organization_Code,
rtg.alternate_routing_designator Alternate_Routing_Code,
op_seq.operation_seq_num Operation_Sequence_Number,
op_seq.operation_type Operation_Type,
op_seq.effectivity_date Op_Start_Effective_Date,
br.Resource_Code Sub_Resource_Code,
null New_Sub_Resource_Code,
bsor.substitute_group_num Substitute_Group_Number,
bsor.schedule_seq_num Schedule_Sequence_Number,
bsor.replacement_group_num Replacement_Group_Number,
TO_NUMBER(NULL) New_Replacement_Group_Number,
ca.Activity Activity,
bsor.Standard_Rate_Flag Standard_Rate_Flag,
bsor.Assigned_Units Assigned_Units,
bsor.Usage_Rate_Or_Amount Usage_Rate_Or_Amount,
bsor.Usage_Rate_Or_Amount_Inverse Usage_Rate_Or_Amount_Inverse,
bsor.Basis_Type Basis_Type,
TO_NUMBER(NULL) New_Basis_Type,
bsor.Schedule_Flag Schedule_Flag,
bsor.Resource_Offset_Percent Resource_Offset_Percent,
bsor.Autocharge_Type Autocharge_Type,
bsor.Principle_Flag Principle_Flag,
bsor.Attribute_category Attribute_category,
bsor.Attribute1 Attribute1,
bsor.Attribute2 Attribute2,
bsor.Attribute3 Attribute3,
bsor.Attribute4 Attribute4,
bsor.Attribute5 Attribute5,
bsor.Attribute6 Attribute6,
bsor.Attribute7 Attribute7,
bsor.Attribute8 Attribute8,
bsor.Attribute9 Attribute9,
bsor.Attribute10 Attribute10,
bsor.Attribute11 Attribute11,
bsor.Attribute12 Attribute12,
bsor.Attribute13 Attribute13,
bsor.Attribute14 Attribute14,
bsor.Attribute15 Attribute15,
bsor.original_system_reference Original_System_Reference,
'CREATE' Transaction_Type,
NULL Return_Status,
bst.setup_Code Setup_Type,
NULL Row_Identifier
FROM bom_sub_operation_resources bsor,
mtl_system_items_kfv item,
mtl_parameters org,
bom_operational_routings rtg,
bom_operation_sequences op_seq,
bom_resources br,
cst_activities ca,
bom_setup_types bst
WHERE op_seq.operation_sequence_id = bsor.operation_sequence_id
AND rtg.routing_sequence_id = op_seq.routing_sequence_id
AND org.organization_id = rtg.organization_id
AND item.organization_id = rtg.organization_id
AND item.inventory_item_id = rtg.assembly_item_id
AND br.resource_id = bsor.resource_id
AND ca.activity_id(+) = bsor.activity_id
AND bst.setup_id(+) = bsor.setup_id
AND item.concatenated_segments = P_assembly_item_name
AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
SELECT item.concatenated_segments Assembly_Item_Name,
p_target_orgcode Organization_Code,
rtg.alternate_routing_designator 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,
null New_From_Op_Seq_Number,
null New_From_Start_Effective_Date,
null New_To_Op_Seq_Number,
null 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,
NULL Row_Identifier
FROM bom_operation_networks_v bonv,
bom_operation_sequences bos1,
bom_operation_sequences bos2,
bom_operational_routings rtg,
mtl_system_items_kfv item,
mtl_parameters org
WHERE rtg.routing_sequence_id = bonv.routing_sequence_id
AND bos1.Operation_Sequence_Id = bonv.From_Op_Seq_Id
AND bos2.Operation_Sequence_Id = bonv.To_Op_Seq_Id
AND org.organization_id = rtg.organization_id
AND item.organization_id = rtg.organization_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.concatenated_segments = P_assembly_item_name
AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
SELECT COUNT(*)
INTO l_hdr_cnt
FROM bom_operational_routings bor,
wip_lines wl,
mtl_parameters org,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2,
mtl_item_locations_kfv locators
WHERE wl.line_id(+) = bor.line_id
AND org.organization_id = bor.organization_id
AND item1.organization_id = bor.organization_id
AND item1.inventory_item_id = bor.assembly_item_id
AND item2.inventory_item_id(+) = bor.common_assembly_item_id
AND item2.organization_id(+) = bor.organization_id
AND locators.inventory_location_id(+) = bor.completion_locator_id
AND locators.organization_id (+) = bor.organization_id
AND bor.organization_id = p_model_org_id;
l_rtg_header_tbl.DELETE;
l_rtg_revision_tbl.DELETE;
l_operation_tbl.DELETE;
l_op_resource_tbl.DELETE;
l_sub_resource_tbl.DELETE;
l_op_network_tbl.DELETE;
X_rtg_revision_tbl.DELETE;
X_operation_tbl.DELETE;
X_op_resource_tbl.DELETE;
X_sub_resource_tbl.DELETE;
X_op_network_tbl.DELETE;