[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status_type
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id
AND nvl(plm_or_erp_change, 'PLM') = 'PLM'; -- Added for bug 3692807
(Bom_Rtg_Globals.G_OPR_CREATE, Bom_Rtg_Globals.G_OPR_UPDATE)
THEN
Bom_Validate_Sub_Op_Res.Check_Attributes
( p_rev_sub_resource_rec => l_rev_sub_resource_rec
, p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
(Bom_Rtg_Globals.G_OPR_UPDATE, Bom_Rtg_Globals.G_OPR_DELETE)
THEN
--
-- Process flow step 16: Populate NULL columns for Update and Delete
-- Call Bom_Default_Op_Res.Populate_Null_Columns
--
IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populate NULL columns') ;
, Bom_Rtg_Globals.G_OPR_UPDATE )
THEN
Bom_Default_Sub_OP_Res.Entity_Defaulting
( p_rev_sub_resource_rec => l_rev_sub_resource_rec
, p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
, p_control_rec => Bom_Rtg_Pub.G_Default_Control_Rec
, x_rev_sub_resource_rec => l_rev_sub_resource_rec
, x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
, x_mesg_token_tbl => l_mesg_token_tbl
, x_return_status => l_return_status
) ;
(Bom_Rtg_Globals.G_OPR_CREATE, Bom_Rtg_Globals.G_OPR_UPDATE)
THEN
Bom_Validate_Op_Res.Check_Attributes
( p_rev_op_resource_rec => l_rev_op_resource_rec
, p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
('Query the original op res for rev op res with acd type : change or delete completed with return_status: ' || l_return_status) ;
l_rev_op_resource_rec.transaction_type IN (ENG_GLOBALS.G_OPR_UPDATE ,
ENG_GLOBALS.G_OPR_DELETE)
THEN
--
-- Process flow step 12: Populate NULL columns for Update and Delete
-- Call Bom_Default_Op_Res.Populate_Null_Columns
--
IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populate NULL columns') ;
, Bom_Rtg_Globals.G_OPR_UPDATE )
THEN
Bom_Default_Op_res.Entity_Defaulting
( p_rev_op_resource_rec => l_rev_op_resource_rec
, p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
, p_control_rec => Bom_Rtg_Pub.G_Default_Control_Rec
, x_rev_op_resource_rec => l_rev_op_resource_rec
, x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
, x_mesg_token_tbl => l_mesg_token_tbl
, x_return_status => l_return_status
) ;
(Bom_Rtg_Globals.G_OPR_UPDATE, Bom_Rtg_Globals.G_OPR_DELETE,
Bom_Rtg_Globals.G_OPR_CANCEL)
THEN
BOM_Validate_Op_Seq.Check_Lineage
( p_routing_sequence_id =>
l_rev_op_unexp_rec.routing_sequence_id
, p_operation_sequence_number =>
l_rev_operation_rec.operation_sequence_number
, p_effectivity_date =>
l_rev_operation_rec.start_effective_date
, p_operation_type =>
l_rev_operation_rec.operation_type
, p_revised_item_sequence_id =>
l_rev_op_unexp_rec.revised_item_sequence_id
, x_mesg_token_tbl => l_mesg_token_tbl
, x_return_status => l_return_status
) ;
(Bom_Rtg_Globals.G_OPR_CREATE, Bom_Rtg_Globals.G_OPR_UPDATE)
THEN
Bom_Validate_Op_Seq.Check_Attributes
( p_rev_operation_rec => l_rev_operation_rec
, p_rev_op_unexp_rec => l_rev_op_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
l_rev_operation_rec.transaction_type IN (ENG_GLOBALS.G_OPR_UPDATE ,
ENG_GLOBALS.G_OPR_DELETE ,
ENG_GLOBALS.G_OPR_CANCEL)
THEN
--
-- Process flow step 15: Populate NULL columns for Update and Delete
-- and Creates with ACD_Type 'Add'.
-- Call Bom_Default_Op_Seq.Populate_Null_Columns
--
IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populate NULL columns') ;
, Bom_Rtg_Globals.G_OPR_UPDATE )
THEN
Bom_Validate_Op_Seq.Check_Conditionally_Required
( p_rev_operation_rec => l_rev_operation_rec
, p_rev_op_unexp_rec => l_rev_op_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
, Bom_Rtg_Globals.G_OPR_UPDATE )
THEN
Bom_Default_Op_Seq.Entity_Defaulting
( p_rev_operation_rec => l_rev_operation_rec
, p_rev_op_unexp_rec => l_rev_op_unexp_rec
, p_control_rec => Bom_Rtg_Pub.G_Default_Control_Rec
, x_rev_operation_rec => l_rev_operation_rec
, x_rev_op_unexp_rec => l_rev_op_unexp_rec
, x_mesg_token_tbl => l_mesg_token_tbl
, x_return_status => l_return_status
) ;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
Bom_Validate_Sub_Component.Check_Attributes
( x_return_status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_sub_component_rec => l_sub_component_rec
, p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Process flow step 13 - Populate NULL columns for Update and
-- Delete.
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populating NULL Columns'); END IF;
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Process flow step 11 - Populate NULL columns for Update and
-- Delete.
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populating NULL Columns'); END IF;
IF l_ref_designator_rec.transaction_type = 'DELETE'
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Calling Entity Delete validation'); END IF;
Bom_Validate_Ref_Designator.Check_Entity_Delete
( p_ref_designator_rec => l_ref_designator_rec
, p_ref_desg_unexp_rec => l_ref_desg_unexp_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
l_Token_Tbl.DELETE;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
Bom_Validate_Bom_Component.Check_Attributes
( x_return_status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_rev_component_rec => l_rev_component_rec
, p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
);
l_rev_component_rec.transaction_type IN (ENG_GLOBALS.G_OPR_UPDATE,
ENG_GLOBALS.G_OPR_DELETE)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populate NULL columns'); END IF;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
Bom_Default_Bom_Component.Entity_Defaulting
( p_rev_component_rec => l_rev_component_rec
, p_old_rev_component_rec => l_old_rev_component_rec
, x_rev_component_rec => l_rev_component_rec
);
l_Token_Tbl.DELETE;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
Bom_Validate_Bom_Component.Check_Attributes
( x_return_status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_rev_component_rec => l_rev_component_rec
, p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
);
l_rev_component_rec.transaction_type IN (ENG_GLOBALS.G_OPR_UPDATE,
ENG_GLOBALS.G_OPR_DELETE)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populate NULL columns'); END IF;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
Bom_Default_Bom_Component.Entity_Defaulting
( p_rev_component_rec => l_rev_component_rec
, p_old_rev_component_rec => l_old_rev_component_rec
, x_rev_component_rec => l_rev_component_rec
);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Bug 2918350
-- Start Changes
IF p_change_notice IS NOT NULL AND p_organization_id IS NOT NULL THEN
l_chk_co_sch := ret_co_status ( p_change_notice, p_organization_id);
IF l_revised_item_rec.Updated_Revised_Item_Revision IS NOT null
THEN
l_revised_item_rec.Updated_Revised_Item_Revision := UPPER(l_revised_item_rec.Updated_Revised_Item_Revision);
-- Process Flow step 10: Attribute Validation for CREATE and UPDATE
--
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Attribute Validation'); END IF;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
ENG_Validate_Revised_Item.Check_Attributes
( x_return_status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_revised_item_rec => l_revised_item_rec
, p_rev_item_unexp_rec => l_rev_item_unexp_rec
, p_old_revised_item_rec => l_old_revised_item_rec
, p_old_rev_item_unexp_rec => l_old_rev_item_unexp_rec
);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Process flow step 11 - Populate NULL columns for Update and
-- Delete.
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populating NULL Columns'); END IF;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
ENG_Default_Revised_Item.Entity_Defaulting
( p_revised_item_rec => l_revised_item_rec
, p_rev_item_unexp_rec => l_rev_item_unexp_rec
, p_old_revised_item_rec => l_old_revised_item_rec
, p_old_rev_item_unexp_rec => l_old_rev_item_unexp_rec
, x_revised_item_rec => l_revised_item_rec
, x_rev_item_unexp_rec => l_rev_item_unexp_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_return_status
);
IF l_revised_item_rec.transaction_type = 'DELETE'
THEN
Eng_Validate_Revised_Item.Check_Entity_Delete
( p_revised_item_rec => l_revised_item_rec
, p_rev_item_unexp_rec => l_rev_item_unexp_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Bug 2918350
-- Start Changes
IF p_change_notice IS NOT NULL AND p_organization_id IS NOT NULL THEN
l_chk_co_sch := ret_co_status ( p_change_notice, p_organization_id);
-- The update case when the CO is in scheduled status is handled saperately
IF (l_revised_item_rec.Transaction_Type <> ENG_GLOBALS.G_OPR_UPDATE )
AND (l_chk_co_sch = 4) THEN
l_return_status := error_handler.g_status_error;
IF l_revised_item_rec.Updated_Revised_Item_Revision IS NOT null
THEN
l_revised_item_rec.Updated_Revised_Item_Revision := UPPER(l_revised_item_rec.Updated_Revised_Item_Revision);
-- In case if the transaciton is update, pass parameter to avoid scheduled date validations
IF (l_revised_item_rec.Transaction_Type = ENG_GLOBALS.G_OPR_UPDATE ) THEN
ENG_Validate_ECO.Check_Access
( p_change_notice => l_revised_item_rec.ECO_Name
, p_organization_id => l_rev_item_unexp_rec.organization_id
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_return_status
, p_check_scheduled_status => FALSE -- bug 5756870 , don't check for scheduled date validation..
);
-- If the transaction is not update, fire the default validations...
ENG_Validate_ECO.Check_Access
( p_change_notice => l_revised_item_rec.ECO_Name
, p_organization_id => l_rev_item_unexp_rec.organization_id
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_return_status
, p_check_scheduled_status => TRUE -- bug 5756870
);
IF (l_revised_item_rec.Transaction_Type = ENG_GLOBALS.G_OPR_UPDATE ) THEN
ENG_Validate_Revised_Item.Check_Access
( p_change_notice => l_revised_item_rec.ECO_Name
, p_organization_id => l_rev_item_unexp_rec.organization_id
, p_revised_item_id => l_rev_item_unexp_rec.revised_item_id
, p_new_item_revision => l_revised_item_rec.new_revised_item_revision
, p_effectivity_date => l_revised_item_rec.start_effective_date
, p_new_routing_revsion => l_revised_item_rec.new_routing_revision -- Added by MK on 11/02/00
, p_from_end_item_number => l_revised_item_rec.from_end_item_unit_number -- Added by MK on 11/02/00
, p_revised_item_name => l_revised_item_rec.revised_item_name
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
, p_check_scheduled_status => FALSE -- bug 5756870 , don't check for scheduled date validation..
);
--Note: we need not check if the transaction type is anything other than update
-- because it has been check above, and execution will not make it to this line in such cases
IF(l_chk_co_sch = 4 OR l_old_revised_item_rec.status_type = 4) THEN
ENG_Validate_Revised_Item.Check_Access_Scheduled(
x_Return_Status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_rev_item_unexp_Rec => l_rev_item_unexp_rec
, p_revised_item_Rec => l_revised_item_rec
);
-- Process Flow step 10: Attribute Validation for CREATE and UPDATE
--
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Attribute Validation'); END IF;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
ENG_Validate_Revised_Item.Check_Attributes
( x_return_status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_revised_item_rec => l_revised_item_rec
, p_rev_item_unexp_rec => l_rev_item_unexp_rec
, p_old_revised_item_rec => l_old_revised_item_rec
, p_old_rev_item_unexp_rec => l_old_rev_item_unexp_rec
);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Process flow step 11 - Populate NULL columns for Update and
-- Delete.
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populating NULL Columns'); END IF;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
ENG_Default_Revised_Item.Entity_Defaulting
( p_revised_item_rec => l_revised_item_rec
, p_rev_item_unexp_rec => l_rev_item_unexp_rec
, p_old_revised_item_rec => l_old_revised_item_rec
, p_old_rev_item_unexp_rec => l_old_rev_item_unexp_rec
, x_revised_item_rec => l_revised_item_rec
, x_rev_item_unexp_rec => l_rev_item_unexp_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_return_status
);
IF l_revised_item_rec.transaction_type = 'DELETE'
THEN
Eng_Validate_Revised_Item.Check_Entity_Delete
( p_revised_item_rec => l_revised_item_rec
, p_rev_item_unexp_rec => l_rev_item_unexp_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Bug 2918350
-- Start Changes
IF p_change_notice IS NOT NULL AND p_organization_id IS NOT NULL THEN
l_chk_co_sch := ret_co_status ( p_change_notice, p_organization_id);
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
ENG_Validate_Change_Line.Check_Attributes
( x_return_status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_change_line_rec => l_change_line_rec
, p_change_line_unexp_rec => l_change_line_unexp_rec
, p_old_change_line_rec => l_old_change_line_rec
, p_old_change_line_unexp_rec => l_old_change_line_unexp_rec
);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Process flow step 11 - Populate NULL columns for Update and
-- Delete.
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populating NULL Columns'); END IF;
(ENG_GLOBALS.G_OPR_CREATE, ENG_GLOBALS.G_OPR_UPDATE)
THEN
ENG_Default_Change_Line.Entity_Defaulting
( p_change_line_rec => l_change_line_rec
, p_change_line_unexp_rec => l_change_line_unexp_rec
, p_old_change_line_rec => l_old_change_line_rec
, p_old_change_line_unexp_rec => l_old_change_line_unexp_rec
, x_change_line_rec => l_change_line_rec
, x_change_line_unexp_rec => l_change_line_unexp_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_return_status
);
IF l_change_line_rec.transaction_type = ENG_GLOBALS.G_OPR_DELETE
THEN
ENG_Validate_Change_Line.Check_Entity_Delete
( p_change_line_rec => l_change_line_rec
, p_change_line_unexp_rec => l_change_line_unexp_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_Return_Status
);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
IF p_change_notice IS NOT NULL AND p_organization_id IS NOT NULL THEN
l_chk_co_sch := ret_co_status ( p_change_notice, p_organization_id);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Process flow step 7 - Populate NULL columns for Update and
-- Delete.
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populating NULL Columns'); END IF;
SELECT *
FROM eng_lifecycle_statuses
WHERE entity_name = 'ENG_CHANGE_TYPE'
AND entity_id1 = p_change_type_id;
SELECT ENG_LIFECYCLE_STATUSES_S.NEXTVAL
INTO l_lifecycle_status_id
FROM dual;
-- Insert the Statuses data
INSERT INTO ENG_LIFECYCLE_STATUSES
( CHANGE_LIFECYCLE_STATUS_ID
, ENTITY_NAME
, ENTITY_ID1
, ENTITY_ID2
, ENTITY_ID3
, ENTITY_ID4
, ENTITY_ID5
, SEQUENCE_NUMBER
, STATUS_CODE
, START_DATE
, COMPLETION_DATE
, CHANGE_WF_ROUTE_ID
, AUTO_PROMOTE_STATUS
, AUTO_DEMOTE_STATUS
, WORKFLOW_STATUS
, CHANGE_EDITABLE_FLAG
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, ITERATION_NUMBER
, ACTIVE_FLAG
, CHANGE_WF_ROUTE_TEMPLATE_ID
)
VALUES
( l_lifecycle_status_id
, 'ENG_CHANGE'
, p_change_id
, NULL -- cls.ENTITY_ID2
, NULL -- cls.ENTITY_ID3
, NULL -- cls.ENTITY_ID4
, NULL -- cls.ENTITY_ID5
, cls.SEQUENCE_NUMBER
, cls.STATUS_CODE
, NULL -- cls.START_DATE
, NULL -- cls.COMPLETION_DATE
, NULL -- cls.CHANGE_WF_ROUTE_ID
, cls.AUTO_PROMOTE_STATUS
, cls.AUTO_DEMOTE_STATUS
, NULL -- cls.WORKFLOW_STATUS
, cls.CHANGE_EDITABLE_FLAG
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, p_login_id
, 0 -- cls.ITERATION_NUMBER
, 'Y' -- cls.ACTIVE_FLAG
, cls.CHANGE_WF_ROUTE_ID -- cls.CHANGE_WF_ROUTE_TEMPLATE_ID
);
-- Inserting the status properties
INSERT INTO eng_status_properties(
CHANGE_LIFECYCLE_STATUS_ID
, STATUS_CODE
, PROMOTION_STATUS_FLAG
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
) SELECT l_lifecycle_status_id, status_code, PROMOTION_STATUS_FLAG,
sysdate, p_user_id, sysdate, p_user_id, p_login_id
FROM eng_status_properties
WHERE CHANGE_LIFECYCLE_STATUS_ID = cls.CHANGE_LIFECYCLE_STATUS_ID;
Error_Handler.Write_Debug('Unexpected Error occured in Insert in Create_Change_Lifecycle . . .' || SQLERRM);
l_err_text := G_PKG_NAME||' : Utility (Create_Change_Lifecycle Lifecycle Insert) '||SUBSTR(SQLERRM, 1, 200);
SELECT tsk.sequence_number,
tsk.required_flag,
tsk.default_assignee_id,
tsk.default_assignee_type,
tsk.task_name,
tsk.description,
typtsk.complete_before_status_code,
typtsk.start_after_status_code,
typtsk.change_type_id
FROM eng_change_tasks_vl tsk,
eng_change_type_org_tasks typtsk
WHERE tsk.organization_id = typtsk.organization_id
AND typtsk.organization_id = v_organization_id
AND tsk.change_template_id = typtsk.change_template_or_task_id
AND typtsk.template_or_task_flag ='E'
AND typtsk.change_type_id = v_change_type_id;
SELECT member_person_id
FROM ego_group_members_v
WHERE group_id = v_default_assignee_id;
SELECT fg.grantee_orig_system_id
FROM fnd_grants fg,
fnd_menus_tl tl,
fnd_menus m,
(SELECT distinct f.object_id,
e.menu_id
FROM fnd_form_functions f,
fnd_menu_entries e
WHERE e.function_id = f.function_id) r,
fnd_objects o
WHERE fg.grantee_orig_system='HZ_PARTY'
AND fg.grantee_type = 'USER'
AND fg.menu_id = tl.menu_id
AND fg.object_id = o.object_id
AND tl.menu_id = r.menu_id
AND m.menu_id = tl.menu_id
AND tl.menu_id = v_assignee_id
AND tl.LANGUAGE= USERENV('LANG')
AND r.object_id = o.object_id
AND o.obj_name = v_assignee_type;
SELECT eng_change_lines_s.nextval INTO l_change_line_unexp_rec.change_line_id FROM SYS.DUAL;
l_sql_stmt := ' SELECT member_person_id '
|| ' FROM ego_group_members_v '
|| ' WHERE group_id = :1 ';
Error_Handler.Write_Debug('Unexpected Error occured in Insert . . .' || SQLERRM);
Eng_Change_Line_Util.Insert_Row
( p_change_line_rec => l_change_line_rec
, p_change_line_unexp_rec => l_change_line_unexp_rec
, x_Mesg_Token_Tbl => l_msg_token_tbl
, x_return_status => l_return_status
);
select ENG_CHANGE_OBJ_RELATIONSHIPS_S.nextval
into l_new_prop_relation
from dual;
insert into eng_change_obj_relationships (
CHANGE_RELATIONSHIP_ID,
CHANGE_ID,
RELATIONSHIP_CODE,
OBJECT_TO_NAME,
OBJECT_TO_ID1,
OBJECT_TO_ID2,
OBJECT_TO_ID3,
OBJECT_TO_ID4,
OBJECT_TO_ID5,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN )
values(
l_new_prop_relation,
ENGECOBO.GLOBAL_CHANGE_ID,
'PROPAGATED_TO',
'ENG_CHANGE',
p_change_id,
ENGECOBO.GLOBAL_ORG_ID,
p_organization_id,
null,
null,
sysdate,
Eng_Globals.Get_User_Id,
sysdate,
Eng_Globals.Get_User_Id,
Eng_Globals.Get_Login_id
);
('Unexpected Error occured in Insert . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Relationship Insert) ' ||
SUBSTR(SQLERRM, 1, 200);
SELECT ecot.type_name CHANGE_ORDER_TYPE, eec.assignee_id
FROM eng_engineering_changes eec, eng_change_order_types_vl ecot
WHERE eec.change_notice =p_change_notice
AND eec.organization_id = p_organization_id
AND eec.change_order_type_id = ecot.change_order_type_id;
-- Check if the CO record is updated
IF l_eco_rec.transaction_type = eng_launch_eco_oi_pk.g_update THEN
-- Find the Organization ID corresponding to the Organization Code
l_organization_id := eng_val_to_id.organization
( l_eco_rec.organization_code, l_err_text);
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_CREATE)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Attribute validation'); END IF;
(ENG_GLOBALS.G_OPR_UPDATE, ENG_GLOBALS.G_OPR_DELETE)
THEN
-- Process flow step 8 - Populate NULL columns for Update and
-- Delete.
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Populating NULL Columns'); END IF;
IF l_eco_rec.transaction_type = 'DELETE'
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Calling Check_Delete...'); END IF;
ENG_Validate_ECO.Check_Delete
( p_eco_rec => l_eco_rec
, p_Unexp_ECO_rec => l_ECO_Unexp_Rec
, x_return_status => l_return_status
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
/* The procedure first explodes and inserts the Statuses for
the given Type, Routes for each Status, Steps for each Route,
People for each Step, and Persons for each Group and Role. */
l_user_id := Eng_Globals.Get_User_Id;
l_row_inserted_flag number := 0; -- bug 13860012
SELECT 'A' grantee_type,
'A1' name_link,
grants.grant_guid grant_guid,
grants.start_date start_date,
grants.end_date end_date,
grants.instance_type object_key_type,
grants.instance_pk1_value object_key,
ltrim(grantee_global.party_name, '* ') party_name,
NULL company_name,
-1 company_id,
grantee_global.party_id party_id,
granted_menu.menu_name role_name,
granted_menu.menu_name role_description,
obj.obj_name object_name,
granted_menu.menu_id menu_id,
'egorolegranttableviewrolename' switcherCol,
menutl.user_menu_name roleNameLink,
grants.instance_pk1_value pk1_value,
grants.instance_pk2_value pk2_value,
grants.instance_pk3_value pk3_value,
grants.instance_pk4_value pk4_value,
grants.instance_pk5_value pk5_value,
grants.instance_set_id instance_set_id,
grants.ROWID as row_id,
LTRIM(grantee_global.party_name, '* ') as decoded_party_name
FROM fnd_grants grants,
hz_parties grantee_global,
fnd_menus granted_menu,
fnd_objects obj,
fnd_menus_tl menutl
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'GLOBAL'
AND NVL(grants.end_date, SYSDATE + 1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
AND grants.menu_id = menutl.menu_id
AND menutl.language = USERENV('LANG')
AND grantee_global.party_id = -1000
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = '*NULL*'
AND grants.instance_pk2_value = '*NULL*'
AND grants.instance_pk3_value = '*NULL*'
AND grants.instance_pk4_value = '*NULL*'
AND grants.instance_pk5_value = '*NULL*'
union all
SELECT 'A' grantee_type,
'A1' name_link,
grants.grant_guid grant_guid,
grants.start_date start_date,
grants.end_date end_date,
grants.instance_type object_key_type,
grants.instance_pk1_value object_key,
ltrim(grantee_global.party_name, '* ') party_name,
NULL company_name,
-1 company_id,
grantee_global.party_id party_id,
granted_menu.menu_name role_name,
granted_menu.menu_name role_description,
obj.obj_name object_name,
granted_menu.menu_id menu_id,
'egorolegranttableviewrolename' switcherCol,
menutl.user_menu_name roleNameLink,
grants.instance_pk1_value pk1_value,
grants.instance_pk2_value pk2_value,
grants.instance_pk3_value pk3_value,
grants.instance_pk4_value pk4_value,
grants.instance_pk5_value pk5_value,
grants.instance_set_id instance_set_id,
grants.ROWID as row_id,
LTRIM(grantee_global.party_name, '* ') as decoded_party_name
FROM fnd_grants grants,
hz_parties grantee_global,
fnd_menus granted_menu,
fnd_objects obj,
fnd_menus_tl menutl
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'GLOBAL'
AND NVL(grants.end_date, SYSDATE + 1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
AND grants.menu_id = menutl.menu_id
AND menutl.language = USERENV('LANG')
AND grantee_global.party_id = -1000
AND grants.instance_type = 'SET'
AND grants.instance_set_id = cp_instance_set_id
union all
SELECT 'G' grantee_type,
'G1' name_link,
grants.grant_guid grant_guid,
grants.start_date start_date,
grants.end_date end_date,
grants.instance_type object_key_type,
grants.instance_pk1_value object_key,
grantee_group.group_name party_name,
NULL company_name,
-1 company_id,
grantee_group.group_id party_id,
granted_menu.menu_name role_name,
granted_menu.menu_name role_description,
obj.obj_name object_name,
granted_menu.menu_id menu_id,
'egorolegranttableviewrolename' switcherCol,
menutl.user_menu_name roleNameLink,
grants.instance_pk1_value pk1_value,
grants.instance_pk2_value pk2_value,
grants.instance_pk3_value pk3_value,
grants.instance_pk4_value pk4_value,
grants.instance_pk5_value pk5_value,
grants.instance_set_id instance_set_id,
grants.ROWID as row_id,
grantee_group.group_name as decoded_party_name
FROM fnd_grants grants,
ego_groups_v grantee_group,
fnd_menus granted_menu,
fnd_objects obj,
fnd_menus_tl menutl
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'GROUP'
AND TO_NUMBER(REPLACE(grants.grantee_key, 'HZ_GROUP:', '')) =
grantee_group.group_id
AND grantee_key like 'HZ_GROUP%'
AND NVL(grants.end_date, SYSDATE + 1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
AND grants.menu_id = menutl.menu_id
AND menutl.language = USERENV('LANG')
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = '*NULL*'
AND grants.instance_pk2_value = '*NULL*'
AND grants.instance_pk3_value = '*NULL*'
AND grants.instance_pk4_value = '*NULL*'
AND grants.instance_pk5_value = '*NULL*'
union all
SELECT 'G' grantee_type,
'G1' name_link,
grants.grant_guid grant_guid,
grants.start_date start_date,
grants.end_date end_date,
grants.instance_type object_key_type,
grants.instance_pk1_value object_key,
grantee_group.group_name party_name,
NULL company_name,
-1 company_id,
grantee_group.group_id party_id,
granted_menu.menu_name role_name,
granted_menu.menu_name role_description,
obj.obj_name object_name,
granted_menu.menu_id menu_id,
'egorolegranttableviewrolename' switcherCol,
menutl.user_menu_name roleNameLink,
grants.instance_pk1_value pk1_value,
grants.instance_pk2_value pk2_value,
grants.instance_pk3_value pk3_value,
grants.instance_pk4_value pk4_value,
grants.instance_pk5_value pk5_value,
grants.instance_set_id instance_set_id,
grants.ROWID as row_id,
grantee_group.group_name decoded_party_name
FROM fnd_grants grants,
ego_groups_v grantee_group,
fnd_menus granted_menu,
fnd_objects obj,
fnd_menus_tl menutl
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'GROUP'
AND TO_NUMBER(REPLACE(grants.grantee_key, 'HZ_GROUP:', '')) =
grantee_group.group_id
AND grantee_key like 'HZ_GROUP%'
AND NVL(grants.end_date, SYSDATE + 1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
AND grants.menu_id = menutl.menu_id
AND menutl.language = USERENV('LANG')
AND grants.instance_type = 'SET'
AND grants.instance_set_id = cp_instance_set_id
union all
SELECT 'P' grantee_type,
'P1' name_link,
grants.grant_guid grant_guid,
grants.start_date start_date,
grants.end_date end_date,
grants.instance_type object_key_type,
grants.instance_pk1_value object_key,
ltrim(grantee_person.person_name, '* ') party_name,
grantee_person.company_name company_name,
grantee_person.company_id company_id,
grantee_person.person_id party_id,
granted_menu.menu_name role_name,
granted_menu.menu_name role_description,
obj.obj_name object_name,
granted_menu.menu_id menu_id,
'egorolegranttableviewrolename' switcherCol,
menutl.user_menu_name roleNameLink,
grants.instance_pk1_value pk1_value,
grants.instance_pk2_value pk2_value,
grants.instance_pk3_value pk3_value,
grants.instance_pk4_value pk4_value,
grants.instance_pk5_value pk5_value,
grants.instance_set_id instance_set_id,
grants.ROWID as row_id,
LTRIM(grantee_person.person_name, '* ') as decoded_party_name
FROM fnd_grants grants,
ego_person_company_v grantee_person,
fnd_menus granted_menu,
fnd_objects obj,
fnd_menus_tl menutl
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'USER'
AND TO_NUMBER(REPLACE(grants.grantee_key, 'HZ_PARTY:', '')) =
grantee_person.person_id
AND grantee_key like 'HZ_PARTY%'
AND NVL(grants.end_date, SYSDATE + 1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
AND grants.menu_id = menutl.menu_id
AND menutl.language = USERENV('LANG')
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = '*NULL*'
AND grants.instance_pk2_value = '*NULL*'
AND grants.instance_pk3_value = '*NULL*'
AND grants.instance_pk4_value = '*NULL*'
AND grants.instance_pk5_value = '*NULL*'
union all
SELECT 'P' grantee_type,
'P1' name_link,
grants.grant_guid grant_guid,
grants.start_date start_date,
grants.end_date end_date,
grants.instance_type object_key_type,
grants.instance_pk1_value object_key,
ltrim(grantee_person.person_name, '* ') party_name,
grantee_person.company_name company_name,
grantee_person.company_id company_id,
grantee_person.person_id party_id,
granted_menu.menu_name role_name,
granted_menu.menu_name role_description,
obj.obj_name object_name,
granted_menu.menu_id menu_id,
'egorolegranttableviewrolename' switcherCol,
menutl.user_menu_name roleNameLink,
grants.instance_pk1_value pk1_value,
grants.instance_pk2_value pk2_value,
grants.instance_pk3_value pk3_value,
grants.instance_pk4_value pk4_value,
grants.instance_pk5_value pk5_value,
grants.instance_set_id instance_set_id,
grants.ROWID as row_id,
LTRIM(grantee_person.person_name, '* ') as decoded_party_name
FROM fnd_grants grants,
ego_person_company_v grantee_person,
fnd_menus granted_menu,
fnd_objects obj,
fnd_menus_tl menutl
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'USER'
AND TO_NUMBER(REPLACE(grants.grantee_key, 'HZ_PARTY:', '')) =
grantee_person.person_id
AND grantee_key like 'HZ_PARTY%'
AND NVL(grants.end_date, SYSDATE + 1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
AND grants.menu_id = menutl.menu_id
AND menutl.language = USERENV('LANG')
AND grants.instance_type = 'SET'
AND grants.instance_set_id = cp_instance_set_id
union all
SELECT 'C' grantee_type,
'C1' name_link,
grants.grant_guid grant_id,
grants.start_date start_date,
grants.end_date end_date,
grants.instance_type object_key_type,
grants.instance_pk1_value object_key,
grantee_company.company_name party_name,
grantee_company.company_name company_name,
grantee_company.company_id company_id,
grantee_company.company_id party_id,
granted_menu.menu_name role_name,
granted_menu.menu_name role_description,
obj.obj_name object_name,
granted_menu.menu_id menu_id,
'egorolegranttableviewrolename' switcherCol,
menutl.user_menu_name roleNameLink,
grants.instance_pk1_value pk1_value,
grants.instance_pk2_value pk2_value,
grants.instance_pk3_value pk3_value,
grants.instance_pk4_value pk4_value,
grants.instance_pk5_value pk5_value,
grants.instance_set_id instance_set_id,
grants.ROWID as row_id,
grantee_company.company_name decoded_party_name
FROM fnd_grants grants,
ego_companies_v grantee_company,
fnd_menus granted_menu,
fnd_objects obj,
fnd_menus_tl menutl
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'COMPANY'
AND NVL(grants.end_date, SYSDATE + 1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
AND grants.menu_id = menutl.menu_id
AND TO_NUMBER(REPLACE(grants.grantee_key, 'HZ_COMPANY:', '')) =
grantee_company.company_id
AND grantee_key like 'HZ_COMPANY%'
AND menutl.language = USERENV('LANG')
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = '*NULL*'
AND grants.instance_pk2_value = '*NULL*'
AND grants.instance_pk3_value = '*NULL*'
AND grants.instance_pk4_value = '*NULL*'
AND grants.instance_pk5_value = '*NULL*'
union all
SELECT 'C' grantee_type,
'C1' name_link,
grants.grant_guid grant_id,
grants.start_date start_date,
grants.end_date end_date,
grants.instance_type object_key_type,
grants.instance_pk1_value object_key,
grantee_company.company_name party_name,
grantee_company.company_name company_name,
grantee_company.company_id company_id,
grantee_company.company_id party_id,
granted_menu.menu_name role_name,
granted_menu.menu_name role_description,
obj.obj_name object_name,
granted_menu.menu_id menu_id,
'egorolegranttableviewrolename' switcherCol,
menutl.user_menu_name roleNameLink,
grants.instance_pk1_value pk1_value,
grants.instance_pk2_value pk2_value,
grants.instance_pk3_value pk3_value,
grants.instance_pk4_value pk4_value,
grants.instance_pk5_value pk5_value,
grants.instance_set_id instance_set_id,
grants.ROWID as row_id,
grantee_company.company_name decoded_party_name
FROM fnd_grants grants,
ego_companies_v grantee_company,
fnd_menus granted_menu,
fnd_objects obj,
fnd_menus_tl menutl
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'COMPANY'
AND NVL(grants.end_date, SYSDATE + 1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
AND grants.menu_id = menutl.menu_id
AND TO_NUMBER(REPLACE(grants.grantee_key, 'HZ_COMPANY:', '')) =
grantee_company.company_id
AND grantee_key like 'HZ_COMPANY%'
AND menutl.language = USERENV('LANG')
AND grants.instance_type = 'SET'
AND grants.instance_set_id = cp_instance_set_id;
SELECT DISTINCT map.PARENT_OBJECT_ID,
map.PARENT_ROLE_ID,
map.CHILD_OBJECT_ID,
map.CHILD_OBJECT_TYPE,
map.CHILD_ROLE_ID,
menus.MENU_NAME CHILD_ROLE,
decode(e.created_by,
1,
menus.description,
menus.user_menu_name) CHILD_ROLE_NAME,
lookup.change_mgmt_type_code CHANGE_MGMT_TYPE,
lookup.name CHANGE_MGMT_TYPE_NAME,
'ENG_CHANGE'
FROM EGO_OBJ_ROLE_MAPPINGS map,
fnd_menus_vl menus,
eng_change_mgmt_types_vl lookup,
fnd_menu_entries e,
fnd_objects fo
WHERE menus.menu_id(+) = map.child_role_id
AND e.menu_id(+) = menus.menu_id
AND map.CHILD_OBJECT_TYPE(+) = lookup.change_mgmt_type_code
AND lookup.disable_flag = 'N'
AND lookup.base_change_mgmt_type_code <> 'DOM_DOCUMENT_LIFECYCLE'
AND lookup.change_mgmt_type_code = cp_change_mgmt_type_code
AND fo.obj_name = 'EGO_ITEM'
AND map.PARENT_OBJECT_ID = fo.object_id
AND map.CHILD_ROLE_ID(+) = cp_role_id;
SELECT CHANGE_LIFECYCLE_STATUS_ID,
ENTITY_NAME,
ENTITY_ID1,
SEQUENCE_NUMBER,
STATUS_CODE,
START_DATE,
COMPLETION_DATE,
CHANGE_WF_ROUTE_ID,
CHANGE_WF_ROUTE_TEMPLATE_ID,
AUTO_PROMOTE_STATUS,
WORKFLOW_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
FROM ENG_LIFECYCLE_STATUSES
WHERE ENTITY_ID1 IN
(SELECT CHANGE_ID
FROM ENG_ENGINEERING_CHANGES
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_org_id)
and change_wf_route_id is null
and change_wf_route_template_id is not null --needed for part with workflow
ORDER BY ENTITY_ID1, SEQUENCE_NUMBER;
SELECT *
FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE STEP_ID IN (SELECT STEP_ID
FROM ENG_CHANGE_ROUTE_STEPS
WHERE ROUTE_ID IN (cp_route_id))
AND ORIGINAL_ASSIGNEE_TYPE_CODE IS NULL
AND ORIGINAL_ASSIGNEE_ID IS NULL
AND ASSIGNEE_TYPE_CODE <> 'PERSON'
ORDER BY STEP_ID;
SELECT MEMBER_PERSON_ID
FROM ENG_SECURITY_GROUP_MEMBERS_V
WHERE GROUP_ID = cp_group_id
ORDER BY MEMBER_PERSON_ID;
SELECT eec.change_id
FROM eng_engineering_changes eec ,
eng_change_obj_relationships ecor
WHERE eec.change_id = ecor.object_to_id1
AND ecor.relationship_code IN ( 'PROPAGATED_TO', 'TRANSFERRED_TO' )
AND ecor.object_to_name ='ENG_CHANGE'
AND ecor.object_to_id3 = cp_local_organization_id
AND eec.change_notice = cp_change_notice;
SELECT CHANGE_MGMT_TYPE_CODE
INTO l_change_mgmt_type_code
FROM ENG_ENGINEERING_CHANGES
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_org_id;
SELECT ENG_CHANGE_ROUTES_S.NEXTVAL into l_to_route_id FROM DUAL;
UPDATE ENG_LIFECYCLE_STATUSES
SET CHANGE_WF_ROUTE_ID = l_to_route_id,
WORKFLOW_STATUS = 'NOT_STARTED'
WHERE ENTITY_ID1 = C_CHANGES_REC.ENTITY_ID1
AND CHANGE_LIFECYCLE_STATUS_ID =
C_CHANGES_REC.CHANGE_LIFECYCLE_STATUS_ID;
SELECT to_char(status_code)
INTO l_classification_code
FROM eng_lifecycle_statuses
WHERE change_wf_route_id = l_to_route_id;
UPDATE ENG_CHANGE_ROUTES
SET OBJECT_ID1 = C_CHANGES_REC.ENTITY_ID1,
CLASSIFICATION_CODE = l_classification_code,
OWNER_ID = FND_GLOBAL.USER_ID,
APPLIED_TEMPLATE_ID = C_CHANGES_REC.CHANGE_WF_ROUTE_TEMPLATE_ID
WHERE ROUTE_ID = l_to_route_id;
UPDATE ENG_CHANGE_ROUTE_PEOPLE -- bug 13860012
SET ORIGINAL_ASSIGNEE_TYPE_CODE = 'PERSON'
WHERE ASSIGNEE_TYPE_CODE = 'PERSON'
AND STEP_ID IN ( SELECT STEP_ID FROM ENG_CHANGE_ROUTE_STEPS_VL WHERE ROUTE_ID = l_to_route_id );
select count(1)
into l_row_count
from fnd_menus
where menu_id = l_menu_id;
select menu_name
into l_menu_name
from fnd_menus
where menu_id = l_menu_id;
SELECT ASSIGNEE_ID
into l_assignee_id
FROM ENG_ENGINEERING_CHANGES
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_org_id;
SELECT COUNT(1)
INTO l_people_existed_flag
FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE step_id = cr.step_id
AND assignee_id = l_assignee_id;
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
l_assignee_id,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL
(ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG)
select l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
-1,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL
(ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG)
select l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT REQUESTOR_ID
into l_requestor_id
FROM ENG_ENGINEERING_CHANGES
WHERE CHANGE_NOTICE = p_change_notice
AND ORGANIZATION_ID = p_org_id;
SELECT COUNT(1)
INTO l_people_existed_flag
FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE step_id = cr.step_id
AND assignee_id = l_requestor_id;
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
l_requestor_id,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL
(ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG)
select l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
-1,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL
(ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG)
select l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT COUNT(1)
INTO l_people_existed_flag
FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE step_id = cr.step_id
AND assignee_id = cr2.member_person_id;
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
cr2.member_person_id,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
--cr.route_people_id
null);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
l_row_inserted_flag := 0; -- bug 13860012
SELECT COUNT(1)
INTO l_people_existed_flag
FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE step_id = cr.step_id
AND assignee_id = l_items_org_role_table(t_index).party_id;
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
l_items_org_role_table(t_index).party_id,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
l_row_inserted_flag := 1; -- bug 13860012
SELECT COUNT(1)
INTO l_people_existed_flag
FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE step_id = cr.step_id
AND assignee_id = cr2.member_person_id;
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
cr2.member_person_id,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
l_row_inserted_flag := 1; -- bug 13860012
SELECT COUNT(1)
INTO l_people_existed_flag
FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE step_id = cr.step_id
AND assignee_id = l_items_org_role_table(t_index).party_id;
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
l_items_org_role_table(t_index).party_id,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
l_row_inserted_flag := 1; -- bug 13860012
SELECT COUNT(1)
INTO l_people_existed_flag
FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE step_id = cr.step_id
AND assignee_id = cr2.member_person_id;
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
cr2.member_person_id,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
l_row_inserted_flag := 1; -- bug 13860012
IF(l_row_inserted_flag = 0) THEN -- bug 13860012
-- generate new people id
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
into l_people_id
FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
(route_people_id,
step_id,
assignee_id,
assignee_type_code,
adhoc_people_flag,
wf_notification_id,
response_code,
response_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
original_system_reference,
original_assignee_id,
original_assignee_type_code,
response_condition_code,
parent_route_people_id)
VALUES
(l_people_id,
cr.step_id,
-1,
'PERSON',
cr.adhoc_people_flag,
cr.wf_notification_id,
cr.response_code,
cr.response_date,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
cr.request_id,
cr.program_id,
cr.program_application_id,
cr.program_update_date,
cr.original_system_reference,
cr.assignee_id,
cr.assignee_type_code,
cr.response_condition_code,
cr.route_people_id);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
l_people_id,
cr.creation_date,
cr.created_by,
cr.last_update_date,
cr.last_updated_by,
cr.last_update_login,
NULL,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = l_people_id
and T.LANGUAGE = L.LANGUAGE_CODE);
DELETE FROM ENG_CHANGE_ROUTE_PEOPLE
WHERE route_people_id = cr.route_people_id;
DELETE FROM ENG_CHANGE_ROUTE_PEOPLE_TL
WHERE route_people_id = cr.route_people_id;
SELECT change_id
INTO l_change_id
FROM eng_engineering_changes WHERE change_notice = p_change_notice
AND organization_id = p_org_id;