[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
NOT IN ('CREATE', 'UPDATE', 'DELETE')))
OR
((p_entity IN ('Rev_Comps','Op_Seq') AND -- L1
NVL(p_transaction_type, FND_API.G_MISS_CHAR)
NOT IN ('CREATE', 'UPDATE', 'DELETE', 'CANCEL')))
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
IF p_entity = 'ECO_Header'
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ECO_TRANS_TYPE_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
ELSIF p_operation = G_OPR_UPDATE THEN
l_control_rec.default_attributes:= FALSE;
ELSIF p_operation = G_OPR_DELETE THEN
l_control_rec.default_attributes:= FALSE;
SELECT process_name
INTO l_process_name
FROM eng_change_type_processes
WHERE change_order_type_id = p_change_order_type_id
AND organization_id = p_organization_id -- 2230130
AND (( p_priority_code is NOT NULL
AND eng_change_priority_code = p_priority_code)
OR
(p_priority_code is NULL
AND eng_change_priority_code is NULL));
G_REQUEST_TBL.DELETE;
SELECT approval_status_type
INTO l_approval_status_type
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id;
UPDATE eng_engineering_changes
SET approval_status_type = 1,
approval_request_date = null,
approval_date = null,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE organization_id = p_organization_id
AND change_notice = p_change_notice;
UPDATE eng_revised_items
SET status_type = 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE organization_id = p_organization_id
AND change_notice = p_change_notice
AND status_type = 4;
select assembly_type
into l_assembly_type
from eng_change_order_types
where change_order_type_id =
(select change_order_type_id
from eng_engineering_changes
where change_notice = p_change_notice
and organization_id = p_organization_id);
FUNCTION ECO_Cannot_Update
( p_change_notice IN VARCHAR2
, p_organization_id IN NUMBER
)RETURN BOOLEAN
IS
l_ret BOOLEAN := FALSE;
cursor get_pri_chgtype_stat is select priority_code, change_order_type_id, status_type
from ENG_ENGINEERING_CHANGES
where change_notice = p_change_notice
and organization_id = p_organization_id;
cursor check_ECO is select 1
from ENG_ENGINEERING_CHANGES
where change_notice = p_change_notice
and organization_id = p_organization_id
and approval_status_type = 3;
END ECO_Cannot_Update;
SELECT nvl(plm_or_erp_change, 'PLM') plm_or_erp
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id;
select assembly_type
into l_assembly_type
from eng_change_order_types
where change_order_type_id =
p_change_order_type_id;
, p_msg_name => 'ENG_CHGORD_TYPE_DELETED'
, x_err_text => x_err_text );
* 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 := NULL
, 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 OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status 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
-- Bug 2232521
-- Some time NULL value was defaulted to the cfm_routing_flag.
-- So, the form ENGFDECN.fmb is not displaying the Operations.
-- , cfm_routing_flag
, NVL(cfm_routing_flag,2)
, 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 ;
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 ;
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 ;
, 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_rev_op_unexp_rec.revised_item_sequence_id
, p_original_system_reference => l_rev_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_rev_op_unexp_rec.revised_item_id
, l_rev_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_rev_operation_rec.eco_name
, SYSDATE
, SYSDATE
, l_rev_op_unexp_rec.revised_item_sequence_id
FROM MTL_PARAMETERS mp
WHERE mp.organization_id = l_rev_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_rev_op_unexp_rec.organization_id
AND inventory_item_id = l_rev_op_unexp_rec.revised_item_id
) ;