[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * FROM BOM_OPERATION_SEQUENCES
WHERE (( l_bo_id = BOM_Rtg_Globals.G_ECO_BO )
-- AND implementation_date IS NULL )
OR (l_bo_id = BOM_Rtg_Globals.G_RTG_BO
AND implementation_date IS NOT NULL )
)
AND ( ( NVL(OPERATION_TYPE,1) = 1 AND -- Added nvl for bug 2856314
EFFECTIVITY_DATE = p_effectivity_date -- Changed for bug 2647027
-- /** time **/ TRUNC(EFFECTIVITY_DATE) = TRUNC(p_effectivity_date)
)
OR p_operation_type IN (2,3)
)
AND NVL(OPERATION_TYPE, 1) = DECODE(p_operation_type, FND_API.G_MISS_NUM, 1
, NVL(p_operation_type, 1))
AND OPERATION_SEQ_NUM = p_operation_sequence_number
AND routing_sequence_id = p_routing_sequence_id
;
* user has to perform any insert/update/deletes to the
* Operation Sequences table.
*********************************************************************/
PROCEDURE Perform_Writes
( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
, p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
* user has to perform any insert/update/deletes to the
* Operation Sequences table.
*********************************************************************/
PROCEDURE Perform_Writes
( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
, p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
, p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
* Purpose : Perform any insert/update/deletes to the
* Operation Sequences table.
*********************************************************************/
PROCEDURE Perform_Writes
( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
, p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
, p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
SELECT 'Routing Exists'
FROM DUAL
WHERE NOT EXISTS ( SELECT routing_sequence_id
FROM bom_operational_routings
WHERE assembly_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND NVL(alternate_routing_designator, FND_API.G_MISS_CHAR) =
NVL(p_alternate_rtg_code,FND_API.G_MISS_CHAR)
);
SELECT decode(eng_item_flag, 'N', 1, 2) eng_item_flag
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id ;
SELECT bom_operational_routings_s.NEXTVAL routing_sequence_id
FROM DUAL ;
('Operation Sequence: Executing Insert Row. . . ') ;
, p_last_update_date => SYSDATE
, p_last_updated_by => BOM_Rtg_Globals.Get_User_Id
, p_creation_date => SYSDATE
, p_created_by => BOM_Rtg_Globals.Get_User_Id
, p_login_id => BOM_Rtg_Globals.Get_Login_Id
, p_revised_item_sequence_id => l_com_op_unexp_rec.revised_item_sequence_id
, p_original_system_reference => l_com_operation_rec.original_system_reference
, x_Mesg_Token_Tbl => l_temp_mesg_token_Tbl
, x_return_status => l_temp_return_status
) ;
INSERT INTO MTL_RTG_ITEM_REVISIONS
( inventory_item_id
, organization_id
, process_revision
, implementation_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, change_notice
, ecn_initiation_date
, effectivity_date
, revised_item_sequence_id
)
SELECT
l_com_op_unexp_rec.revised_item_id
, l_com_op_unexp_rec.organization_id
, mp.starting_revision
, SYSDATE
, SYSDATE
, BOM_Rtg_Globals.Get_User_Id
, SYSDATE
, BOM_Rtg_Globals.Get_User_Id
, BOM_Rtg_Globals.Get_Login_Id
, l_com_operation_rec.eco_name
, SYSDATE
, SYSDATE
, l_com_op_unexp_rec.revised_item_sequence_id
FROM MTL_PARAMETERS mp
WHERE mp.organization_id = l_com_op_unexp_rec.organization_id
AND NOT EXISTS( SELECT NULL
FROM MTL_RTG_ITEM_REVISIONS
WHERE implementation_date IS NOT NULL
AND organization_id = l_com_op_unexp_rec.organization_id
AND inventory_item_id = l_com_op_unexp_rec.revised_item_id
) ;
Insert_Row
( p_com_operation_rec => l_com_operation_rec
, p_com_op_unexp_rec => l_com_op_unexp_rec
, x_return_status => l_temp_return_status
, x_mesg_token_tbl => l_temp_mesg_token_tbl
) ;
ELSIF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Operatin Sequence: Executing Update Row. . . ') ;
Update_Row
( p_com_operation_rec => l_com_operation_rec
, p_com_op_unexp_rec => l_com_op_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
ELSIF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
THEN
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Operatin Sequence: Executing Delete Row. . . ') ;
Delete_Row
( p_com_operation_rec => l_com_operation_rec
, p_com_op_unexp_rec => l_com_op_unexp_rec
, x_com_operation_rec => l_com_operation_rec
, x_com_op_unexp_rec => l_com_op_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
* procedure will create a Routing and update the revised item
* information indicating that routing for this revised item now
* exists.
******************************************************************************/
PROCEDURE Create_New_Routing
( p_assembly_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_alternate_routing_code IN VARCHAR2
, p_pending_from_ecn IN VARCHAR2
, p_routing_sequence_id IN NUMBER
, p_common_routing_sequence_id IN NUMBER
, p_routing_type IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_login_id IN NUMBER
, p_revised_item_sequence_id IN NUMBER
, p_original_system_reference IN VARCHAR2
, x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
-- Error Handlig Variables
l_return_status VARCHAR2(1);
INSERT INTO bom_operational_routings
( assembly_item_id
, organization_id
, alternate_routing_designator
, pending_from_ecn
, routing_sequence_id
, common_routing_sequence_id
, routing_type
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, original_system_reference
, cfm_routing_flag
, completion_subinventory
, completion_locator_id
, mixed_model_map_flag
, priority
, ctp_flag
, routing_comment
)
SELECT p_assembly_item_id
, p_organization_id
, p_alternate_routing_code
, p_pending_from_ecn
, p_routing_sequence_id
, p_common_routing_sequence_id
, p_routing_type
, p_last_update_date
, p_last_updated_by
, p_creation_date
, p_created_by
, p_login_id
, p_original_system_reference
, cfm_routing_flag
, completion_subinventory
, completion_locator_id
, mixed_model_map_flag
, priority
, ctp_flag
, routing_comment
FROM ENG_REVISED_ITEMS
WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
UPDATE ENG_REVISED_ITEMS
SET routing_sequence_id = p_routing_sequence_id
, last_update_date = p_last_update_date -- Last Update Date
, last_updated_by = p_last_updated_by -- Last Updated By
, last_update_login = p_login_id -- Last Update Login
WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
Desc : For Standered Operations, Eco_Name was not Inserted at the time of insertion of the
operation_resources , so that needed to insert explicitly, because resources query at the form level searches for Eco_Name
and thes Resources could not be Picked at that time.
Procedure Overloaded because tree parameters are passing this time, Two argument Procedure is also taken care after this.
*/
PROCEDURE Copy_Std_Res_and_Docs
( p_operation_sequence_id IN NUMBER
, p_std_operation_id IN NUMBER
, p_change_notice IN VARCHAR2
)
IS
BEGIN
INSERT INTO BOM_OPERATION_RESOURCES
( operation_sequence_id
, resource_seq_num
, resource_id
, acd_type
, 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
, 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
, substitute_group_num
, Schedule_Seq_num -- Bug 7370692
, change_notice)
SELECT p_operation_sequence_id
, resource_seq_num
, resource_id
, DECODE( BOM_Rtg_Globals.Get_Bo_Identifier
, BOM_Rtg_Globals.G_ECO_BO
, 1 -- Acd Type : ADD
, NULL )
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, SYSDATE -- Last Update Date
, BOM_Rtg_Globals.Get_User_Id -- Last Updated By
, SYSDATE -- Creation Date
, BOM_Rtg_Globals.Get_User_Id -- Created By
, BOM_Rtg_Globals.Get_User_Id -- Last Update Login
, autocharge_type
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, NULL -- Request Id
, BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
, BOM_Rtg_Globals.Get_Prog_Id -- Prog id
, SYSDATE -- Program Update Date
, substitute_group_num
, resource_seq_num -- Bug 7370692 (Schedule_Seq_num=resource_seq_num)
, p_change_notice
FROM BOM_STD_OP_RESOURCES
WHERE standard_operation_id = p_std_operation_id ;
INSERT INTO BOM_SUB_OPERATION_RESOURCES
( operation_sequence_id
, substitute_group_num
, resource_id
, schedule_seq_num
, replacement_group_num
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, autocharge_type
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, principle_flag
, setup_id
, change_notice
, acd_type
, original_system_reference)
SELECT p_operation_sequence_id
, substitute_group_num
, resource_id
, schedule_seq_num -- Bug 7370692 0 -- defaulting SSN to zero and the user has to change this value.
, replacement_group_num
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, autocharge_type
, SYSDATE -- Last Update Date
, BOM_Rtg_Globals.Get_User_Id -- Last Updated By
, SYSDATE -- Creation Date
, BOM_Rtg_Globals.Get_User_Id -- Created By
, BOM_Rtg_Globals.Get_User_Id -- Last Update Login
, NULL -- Request Id
, BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
, BOM_Rtg_Globals.Get_Prog_Id -- Prog id
, SYSDATE -- Program Update Date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, NULL -- principle_flag
, NULL -- setup_id
, NULL -- change_notice
, DECODE( BOM_Rtg_Globals.Get_Bo_Identifier
, BOM_Rtg_Globals.G_ECO_BO
, 1 -- Acd Type : ADD
, NULL )
, NULL -- original_system_reference
FROM BOM_STD_SUB_OP_RESOURCES
WHERE standard_operation_id = p_std_operation_id ;
X_last_update_login => BOM_Rtg_Globals.Get_User_Id,
X_program_application_id => BOM_Rtg_Globals.Get_Prog_AppId,
X_program_id => BOM_Rtg_Globals.Get_Prog_Id,
X_request_id => null
) ;
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
, schedule_seq_num
, substitute_group_num
, principle_flag
, change_notice
, acd_type
-- , original_system_reference
-- , setup_id
)
SELECT
p_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 -- Last Update Date
, BOM_Rtg_Globals.Get_User_Id -- Last Updated By
, SYSDATE -- Creation Date
, BOM_Rtg_Globals.Get_User_Id -- Created By
, BOM_Rtg_Globals.Get_User_Id -- 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
, NULL -- Request Id
, BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
, BOM_Rtg_Globals.Get_Prog_Id -- Prog id
, SYSDATE -- Program Update Date
, schedule_seq_num
, substitute_group_num
, principle_flag
, p_eco_name -- change_notice
, 3 -- acd_type : Disable
-- , original_system_reference
-- , setup_id
FROM BOM_OPERATION_RESOURCES
WHERE operation_sequence_id = p_old_operation_sequence_id ;
INSERT INTO BOM_SUB_OPERATION_RESOURCES
( operation_sequence_id
, substitute_group_num
, resource_id
, replacement_group_num
, 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
, principle_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
, schedule_seq_num
, change_notice
, acd_type
-- , original_system_reference
-- , setup_id
)
SELECT
p_operation_sequence_id
, substitute_group_num
, resource_id
, replacement_group_num
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, SYSDATE -- Last Update Date
, BOM_Rtg_Globals.Get_User_Id -- Last Updated By
, SYSDATE -- Creation Date
, BOM_Rtg_Globals.Get_User_Id -- Created By
, BOM_Rtg_Globals.Get_User_Id -- Last Update Login
, resource_offset_percent
, autocharge_type
, principle_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, NULL -- Request Id
, BOM_Rtg_Globals.Get_Prog_AppId -- Program Application Id
, BOM_Rtg_Globals.Get_Prog_Id -- Prog id
, SYSDATE -- Program Update Date
, schedule_seq_num
, p_eco_name -- change_notice
, 3 -- acd_type : Disable
-- , original_system_reference
-- , setup_id
FROM BOM_SUB_OPERATION_RESOURCES
WHERE operation_sequence_id = p_old_operation_sequence_id ;
* Procedure : Insert_Row
* Parameters IN : Common Operation exposed column record
* Common Operation unexposed column record
* Parameters out: Return Status
* Message Token Table
* Purpose : This procedure will insert a record in the Operation Sequence
* table; BOM_OPERATION_SEQUENCES.
PROCEDURE Insert_Row
( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
, p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_Bo_Id VARCHAR2(3) ;
SELECT NVL(eco_for_production, 2)
FROM ENG_REVISED_ITEMS
WHERE revised_item_sequence_id = p_revised_item_sequence_id ;
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 ,
change_notice ,
implementation_date ,
old_operation_sequence_id ,
acd_type ,
revised_item_sequence_id ,
original_system_reference ,
eco_for_production ,
shutdown_type -- Added by MK for eAM changes
, long_description, -- Added for long description project (Bug 2689249)
lowest_acceptable_yield, -- Added for MES Enhancement
use_org_settings,
queue_mandatory_flag,
run_mandatory_flag,
to_move_mandatory_flag,
show_next_op_by_default,
show_scrap_code,
show_lot_attrib,
track_multiple_res_usage_dates -- End of MES Changes
)
VALUES (
p_com_op_unexp_rec.operation_sequence_id
, p_com_op_unexp_rec.routing_sequence_id
, p_com_operation_rec.operation_sequence_number
, SYSDATE -- Last Update Date
, BOM_Rtg_Globals.Get_User_Id -- Last Updated By
, SYSDATE -- Creation Date
, BOM_Rtg_Globals.Get_User_Id -- Created By
, BOM_Rtg_Globals.Get_Login_Id -- Last Update Login
, p_com_op_unexp_rec.standard_operation_id
, p_com_op_unexp_rec.department_id
, p_com_operation_rec.op_lead_time_percent
, p_com_operation_rec.minimum_transfer_quantity
, p_com_operation_rec.count_point_type
, p_com_operation_rec.operation_description
, p_com_operation_rec.start_effective_date
, p_com_operation_rec.disable_date
, p_com_operation_rec.backflush_flag
, p_com_operation_rec.option_dependent_flag
, p_com_operation_rec.attribute_category
, p_com_operation_rec.attribute1
, p_com_operation_rec.attribute2
, p_com_operation_rec.attribute3
, p_com_operation_rec.attribute4
, p_com_operation_rec.attribute5
, p_com_operation_rec.attribute6
, p_com_operation_rec.attribute7
, p_com_operation_rec.attribute8
, p_com_operation_rec.attribute9
, p_com_operation_rec.attribute10
, p_com_operation_rec.attribute11
, p_com_operation_rec.attribute12
, p_com_operation_rec.attribute13
, p_com_operation_rec.attribute14
, p_com_operation_rec.attribute15
, Fnd_Global.Conc_Request_Id -- Request Id
, BOM_Rtg_Globals.Get_Prog_AppId -- Application Id
, BOM_Rtg_Globals.Get_Prog_Id -- Program Id
, SYSDATE -- program_update_date
, p_com_operation_rec.operation_type
, p_com_operation_rec.reference_flag
, p_com_op_unexp_rec.process_op_seq_id
, p_com_op_unexp_rec.line_op_seq_id
, p_com_operation_rec.yield
, p_com_operation_rec.cumulative_yield
, p_com_operation_rec.reverse_CUM_yield
-- , p_com_operation_rec.calculated_labor_time
-- , p_com_operation_rec.calculated_machine_time
-- , p_com_operation_rec.calculated_elapsed_time
, p_com_operation_rec.user_labor_time
, p_com_operation_rec.user_machine_time
, p_com_op_unexp_rec.user_elapsed_time
, p_com_operation_rec.net_planning_percent
-- , p_com_operation_rec.include_in_rollup
-- , p_com_operation_rec.op_yield_enabled_flag
, NVL(p_com_operation_rec.include_in_rollup, 1)
, NVL(p_com_operation_rec.op_yield_enabled_flag,1)
-- For bugfix 1744254, revop does not have these cols
, p_com_operation_rec.eco_name
, DECODE( l_Bo_Id, BOM_Rtg_Globals.G_RTG_BO, SYSDATE, NULL ) -- Implementation Date
, p_com_op_unexp_rec.old_operation_sequence_id
, p_com_operation_rec.acd_type
, p_com_op_unexp_rec.revised_item_sequence_id
, p_com_operation_rec.original_system_reference
, l_Eco_For_Production
-- DECODE( l_Bo_Id, BOM_Rtg_Globals.G_ECO_BO, l_Eco_For_Production, 2) -- Eco for Production flag
, p_com_operation_rec.shutdown_type -- Added by MK for eAM changes
, p_com_operation_rec.long_description -- Added for long description project (Bug 2689249)
, p_com_op_unexp_rec.lowest_acceptable_yield -- Added for MES Enhancement
, p_com_op_unexp_rec.use_org_settings
, p_com_op_unexp_rec.queue_mandatory_flag
, p_com_op_unexp_rec.run_mandatory_flag
, p_com_op_unexp_rec.to_move_mandatory_flag
, p_com_op_unexp_rec.show_next_op_by_default
, p_com_op_unexp_rec.show_scrap_code
, p_com_op_unexp_rec.show_lot_attrib
, p_com_op_unexp_rec.track_multiple_res_usage_dates -- End of MES Changes
);
('Unexpected Error occured in Insert . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Operation Insert) ' ||
SUBSTR(SQLERRM, 1, 200);
END Insert_Row ;
* Procedure : Update_Row
* Parameters IN : Common Operation exposed column record
* Common Operation unexposed column record
* Parameters out: Return Status
* Message Token Table
* Purpose : Update_Row procedure will update the production record with
* the user given values. Any errors will be returned by filling
* the Mesg_Token_Tbl and setting the return_status.
****************************************************************************/
PROCEDURE Update_Row
( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
, p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
-- Error Handlig Variables
l_return_status VARCHAR2(1);
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing update operation . . .') ;
UPDATE BOM_OPERATION_SEQUENCES
SET operation_seq_num = DECODE(p_com_operation_rec.new_operation_sequence_number ,
NULL , p_com_operation_rec.operation_sequence_number ,
p_com_operation_rec.new_operation_sequence_number
)
, last_update_date = SYSDATE /* Last Update Date */
, last_updated_by = BOM_Rtg_Globals.Get_User_Id /* Last Updated By */
, last_update_login = BOM_Rtg_Globals.Get_Login_Id /* Last Update Login */
, standard_operation_id = p_com_op_unexp_rec.standard_operation_id
, department_id = p_com_op_unexp_rec.department_id
, operation_lead_time_percent = p_com_operation_rec.op_lead_time_percent
, minimum_transfer_quantity = p_com_operation_rec.minimum_transfer_quantity
, count_point_type = p_com_operation_rec.count_point_type
, operation_description = p_com_operation_rec.operation_description
, effectivity_date = DECODE(p_com_operation_rec.new_start_effective_date ,
NULL , p_com_operation_rec.start_effective_date ,
p_com_operation_rec.new_start_effective_date
)
, disable_date = p_com_operation_rec.disable_date
, backflush_flag = p_com_operation_rec.backflush_flag
, option_dependent_flag = p_com_operation_rec.option_dependent_flag
, attribute_category = p_com_operation_rec.attribute_category
, attribute1 = p_com_operation_rec.attribute1
, attribute2 = p_com_operation_rec.attribute2
, attribute3 = p_com_operation_rec.attribute3
, attribute4 = p_com_operation_rec.attribute4
, attribute5 = p_com_operation_rec.attribute5
, attribute6 = p_com_operation_rec.attribute6
, attribute7 = p_com_operation_rec.attribute7
, attribute8 = p_com_operation_rec.attribute8
, attribute9 = p_com_operation_rec.attribute9
, attribute10 = p_com_operation_rec.attribute10
, attribute11 = p_com_operation_rec.attribute11
, attribute12 = p_com_operation_rec.attribute12
, attribute13 = p_com_operation_rec.attribute13
, attribute14 = p_com_operation_rec.attribute14
, attribute15 = p_com_operation_rec.attribute15
, program_application_id = BOM_Rtg_Globals.Get_Prog_AppId /* Application Id */
, program_id = BOM_Rtg_Globals.Get_Prog_Id /* Program Id */
, program_update_date = SYSDATE /* program_update_date */
, reference_flag = p_com_operation_rec.reference_flag
, process_op_seq_id = p_com_op_unexp_rec.process_op_seq_id
, line_op_seq_id = p_com_op_unexp_rec.line_op_seq_id
, yield = p_com_operation_rec.yield
, cumulative_yield = p_com_operation_rec.cumulative_yield
, reverse_cumulative_yield = p_com_operation_rec.reverse_CUM_yield
-- , labor_time_calc = p_com_operation_rec.calculated_labor_time
-- , machine_time_calc = p_com_operation_rec.calculated_machine_time
-- , total_time_calc = p_com_operation_rec.calculated_elapsed_time
, labor_time_user = p_com_operation_rec.user_labor_time
, machine_time_user = p_com_operation_rec.user_machine_time
, total_time_user = p_com_op_unexp_rec.user_elapsed_time
, net_planning_percent = p_com_operation_rec.net_planning_percent
-- , include_in_rollup = p_com_operation_rec.include_in_rollup
-- , operation_yield_enabled = p_com_operation_rec.op_yield_enabled_flag
, include_in_rollup = NVL(p_com_operation_rec.include_in_rollup,1)
, operation_yield_enabled = NVL(p_com_operation_rec.op_yield_enabled_flag,1)
-- For bugfix 1744254, RevOp does not have these cols
, acd_type = p_com_operation_rec.acd_type
, original_system_reference = p_com_operation_rec.original_system_reference
, shutdown_type = p_com_operation_rec.shutdown_type -- Added by MK for eAM changes
, long_description = p_com_operation_rec.long_description -- Added for long description (Bug 2689249)
, request_id = Fnd_Global.Conc_Request_Id
WHERE operation_sequence_id = p_com_op_unexp_rec.operation_sequence_id ;
UPDATE BOM_INVENTORY_COMPONENTS bic
SET bic.operation_lead_time_percent = p_com_operation_rec.op_lead_time_percent
, bic.LAST_UPDATE_DATE = SYSDATE
, bic.LAST_UPDATED_BY = BOM_Rtg_Globals.Get_User_Id
, bic.LAST_UPDATE_LOGIN = BOM_Rtg_Globals.Get_Login_Id
, bic.REQUEST_ID = Fnd_Global.Conc_Request_Id
, bic.PROGRAM_ID = Fnd_Global.Conc_Program_Id
, bic.PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
, bic.PROGRAM_UPDATE_DATE = SYSDATE
WHERE bic.operation_seq_num = p_com_operation_rec.operation_sequence_number
AND bic.bill_sequence_id = (SELECT bom.BILL_SEQUENCE_ID
FROM BOM_BILL_OF_MATERIALS bom
, bom_operational_routings bor
WHERE 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
AND bor.routing_sequence_id = p_com_op_unexp_rec.routing_sequence_id) ;
('Operation lead time in bom inventory components refered Operation Seq Num is updated.') ;
( p_message_name => 'BOM_OP_LT_PCT_UPDATED'
, p_mesg_token_tbl => l_mesg_token_Tbl
, x_mesg_token_tbl => l_mesg_token_tbl
, p_message_type => 'W' /* Warning */
, p_Token_Tbl => l_Token_Tbl
) ;
('Unexpected Error occured in Update . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Operation Update) ' ||
SUBSTR(SQLERRM, 1, 200);
END Update_Row ;
* Procedure : Delete_Row
* Parameters IN : Common Operation exposed column record
* Common Operation unexposed column record
* Parameters out: Return Status
* Message Token Table
* Purpose : For ECO BO, procedure will delete a revised operation
* record for a ECO.
* This procedure will not delete a record in production
* which is already implemented.
* For RTG BO, Procedure will perfrom an Delete from the
* BOM_Operation_Sequences by creating a delete Group.
*********************************************************************/
PROCEDURE Delete_Row
( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
, p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
, x_com_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Operation_Rec_Type
, x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
SELECT description
, delete_group_sequence_id
, delete_type
FROM BOM_DELETE_GROUPS
WHERE delete_group_name = p_del_group_name
AND organization_id = p_organization_id ;
DELETE FROM BOM_OPERATION_SEQUENCES
WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
DELETE FROM BOM_OPERATION_RESOURCES
WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
DELETE FROM BOM_SUB_OPERATION_RESOURCES
WHERE OPERATION_SEQUENCE_ID = l_com_op_unexp_rec.operation_sequence_id ;
( p_del_group_name => l_com_operation_rec.delete_group_name
, p_organization_id => l_com_op_unexp_rec.organization_id )
LOOP
IF l_del_grp_rec.delete_type <> 5 /* Operation */
THEN
RAISE DUPLICATE_DEL_GROUP ;
l_com_op_unexp_rec.DG_Sequence_Id := l_del_grp_rec.delete_group_sequence_id ;
( p_message_name => 'NEW_DELETE_GROUP'
, p_mesg_token_tbl => l_mesg_token_Tbl
, x_mesg_token_tbl => l_mesg_token_tbl
, p_message_type => 'W' /* Warning */
) ;
select routing_type into l_rtg_type from bom_operational_routings
where routing_sequence_id = l_com_op_unexp_rec.routing_sequence_id;
MODAL_DELETE.DELETE_MANAGER
( new_group_seq_id => l_dg_sequence_id,
name => l_com_operation_rec.Delete_Group_Name,
group_desc => l_com_operation_rec.dg_description,
org_id => l_com_op_unexp_rec.organization_id,
bom_or_eng => l_rtg_type /* bug 5199643 */,
del_type => 5 /* Operation */,
ent_bill_seq_id => NULL,
ent_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id,
ent_inv_item_id => l_com_op_unexp_rec.revised_item_id,
ent_alt_designator => l_com_operation_rec.alternate_routing_code,
ent_comp_seq_id => NULL,
ent_op_seq_id => l_com_op_unexp_rec.operation_sequence_id ,
user_id => BOM_Rtg_Globals.Get_User_Id
) ;
Error_Handler.Write_Debug('Finished creatin new delete group . . .') ;
('Duplicate Delete Group Error occured in Delete . . .' || SQLERRM);
( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
, p_mesg_token_tbl => l_mesg_token_Tbl
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
('Unexpected Error occured in Delete . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Operation Delete) ' ||
SUBSTR(SQLERRM, 1, 200);
END Delete_Row ;
INSERT INTO ENG_REVISED_OPERATIONS (
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
, x_coordinate
, y_coordinate
, include_in_rollup
, operation_yield_enabled
, change_notice
, implementation_date
, old_operation_sequence_id
, acd_type
, revised_item_sequence_id
, cancellation_date
, cancel_comments
, original_system_reference )
SELECT
OPERATION_SEQUENCE_ID
, routing_sequence_id
, OPERATION_SEQ_NUM
, SYSDATE -- Last Update Date
, p_user_id -- Last Updated By
, SYSDATE -- Creation Date
, p_user_id -- Created By
, p_login_id -- 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
, NULL -- Request Id
, p_prog_appid -- Application Id
, p_prog_id -- Program Id
, SYSDATE -- 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
, X_COORDINATE
, Y_COORDINATE
, INCLUDE_IN_ROLLUP
, OPERATION_YIELD_ENABLED
, CHANGE_NOTICE
, IMPLEMENTATION_DATE
, OLD_OPERATION_SEQUENCE_ID
, ACD_TYPE
, REVISED_ITEM_SEQUENCE_ID
, SYSDATE -- Cancellation Date
, p_cancel_comments -- Cancel Comments
, ORIGINAL_SYSTEM_REFERENCE
FROM BOM_OPERATION_SEQUENCES
WHERE operation_sequence_id = p_operation_sequence_id ;
DELETE FROM BOM_OPERATION_SEQUENCES
WHERE operation_sequence_id = p_operation_sequence_id ;
DELETE FROM BOM_OPERATION_RESOURCES
WHERE operation_sequence_id = p_operation_sequence_id ;
DELETE FROM BOM_SUB_OPERATION_RESOURCES
WHERE operation_sequence_id = p_operation_sequence_id ;