[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
SELECT eec.change_id,
eec.change_mgmt_type_code ,
changecategory.base_change_mgmt_type_code,
type_appl.application_id
FROM ENG_ENGINEERING_CHANGES eec,
ENG_CHANGE_ORDER_TYPES ChangeCategory,
ENG_CHANGE_TYPE_APPLICATIONS type_appl
WHERE type_appl.change_type_id = ChangeCategory.change_order_type_id
and type_appl.application_id = p_appl_id
AND ChangeCategory.type_classification = 'CATEGORY'
AND ChangeCategory.change_mgmt_type_code = eec.change_mgmt_type_code
AND eec.change_id = p_change_id ;
SELECT eec.change_mgmt_type_code, eec.promote_status_code, --eec.status_code
ecot.base_change_mgmt_type_code
INTO l_cm_type_code, l_next_status_code, --l_curr_status_code
l_base_cm_type_code
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;
SELECT max(sequence_number)
INTO l_max_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y';
SELECT status_code
INTO l_last_status_code
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND sequence_number = l_max_phase_sn;
SELECT eec.change_mgmt_type_code, ecot.base_change_mgmt_type_code
INTO l_cm_type_code, l_base_cm_type_code
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;
SELECT max(sequence_number)
INTO l_max_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y';
SELECT status_code, start_date
INTO x_last_status_code, l_start_date
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND sequence_number = l_max_phase_sn
AND rownum = 1;
SELECT status_type
INTO x_last_status_type
FROM eng_change_statuses
WHERE status_code = x_last_status_code;
SELECT revised_item_sequence_id
FROM eng_revised_items eri
WHERE eri.change_id = p_change_id
AND eri.status_type NOT IN (G_ENG_CANCELLED, G_ENG_IMPLEMENTED);
PROCEDURE Update_Header_Appr_Status
(
p_api_version IN NUMBER --
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
,p_commit IN VARCHAR2 := FND_API.G_FALSE --
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL
,p_debug_filename IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_change_id IN NUMBER -- header's change_id
,p_status_code IN NUMBER
,p_appr_status IN NUMBER -- header approval status
,p_route_status IN VARCHAR2 -- workflow routing status (for document types)
,p_api_caller IN VARCHAR2 := 'UI' -- must
,p_bypass IN VARCHAR2 := 'N' -- flag to bypass phase type check
,x_sfa_line_items_exists OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Header_Appr_Status';
l_updated BOOLEAN := FALSE ;
Write_Debug('ENG_CHANGE_LIFECYCLE_UTIL.Update_Header_Appr_Status log');
SELECT status_type
INTO l_status_type
FROM eng_change_statuses
WHERE status_code = p_status_code;
SELECT eec.change_mgmt_type_code, ecot.base_change_mgmt_type_code
INTO l_cm_type_code, l_base_cm_type_code
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;
UPDATE eng_engineering_changes
SET approval_status_type = p_appr_status,
approval_request_date = sysdate,
approval_date = null,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
l_updated := TRUE;
SELECT sequence_number
INTO l_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT max(lcs.sequence_number)
INTO l_max_appr_phase_sn
FROM eng_lifecycle_statuses lcs,
eng_change_statuses chs
WHERE lcs.entity_name = G_ENG_CHANGE
AND lcs.entity_id1 = p_change_id
AND lcs.active_flag = 'Y'
AND chs.status_code = lcs.status_code
AND chs.status_type = G_ENG_APPROVED;
UPDATE eng_engineering_changes
SET approval_status_type = p_appr_status,
approval_date = sysdate,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
l_updated := TRUE;
UPDATE eng_engineering_changes
SET approval_status_type = p_appr_status,
approval_date = null,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
l_updated := TRUE;
l_updated := TRUE;
UPDATE eng_engineering_changes
SET approval_status_type = p_appr_status,
approval_request_date = null,
approval_date = null,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
l_updated := TRUE;
IF (l_updated = TRUE)
THEN
-- Select cm type and base type code for upcoming API calls
/*SELECT eec.change_mgmt_type_code, ecot.base_change_mgmt_type_code
INTO l_cm_type_code, l_base_cm_type_code
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;*/
ENG_NIR_UTIL_PKG.Update_Line_Items_App_St(p_change_id, 3, x_sfa_line_items_exists); -- Reset it to SFA
Write_Debug('Before: calling update DM attachment approval/review API');
ENG_ATTACHMENT_IMPLEMENTATION.Update_Attachment_Status
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_debug => p_debug --FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => NULL --p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_workflow_status => p_route_status
,p_approval_status => p_appr_status
,p_api_caller => p_api_caller
);
Write_Debug('After: calling update DM attachment approval/review API: ' || l_return_status) ;
Write_Debug('Before: calling ENG_ICMDB_APIS_UTIL.Update_Approval_Status');
ENG_ICMDB_APIS_UTIL.Update_Approval_Status
( p_change_id => p_change_id
,p_base_change_mgmt_type_code => l_base_cm_type_code
,p_new_approval_status_cde => p_appr_status
,p_workflow_status_code => p_route_status
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
Write_Debug('After: calling ENG_ICMDB_APIS_UTIL.Update_Approval_Status: ' || l_return_status) ;
Write_Debug('Successful: ENG_ICMDB_APIS_UTIL.Update_Approval_Status');
Write_Debug('After: calling update DM attachment approval/review API');
Write_Debug('Before: calling ENG_DOCUMENT_UTIL.Update_Approval_Status');
ENG_DOCUMENT_UTIL.Update_Approval_Status
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status --
,x_msg_count => l_msg_count --
,x_msg_data => l_msg_data --
,p_change_id => p_change_id -- header's change_id
,p_approval_status => p_appr_status -- header approval status
,p_wf_route_status => p_route_status -- workflow routing status (for document types)
,p_api_caller => p_api_caller -- Optionnal for future use
);
Write_Debug('After: calling ENG_DOCUMENT_UTIL.Update_Approval_Status: ' || l_return_status) ;
Write_Debug('Successful: ENG_DOCUMENT_UTIL.Update_Approval_Status');
END IF; -- if (l_updated = true)
Write_Debug('Finish. End Of procedure: Update_Header_Appr_Status') ;
END Update_Header_Appr_Status;
PROCEDURE Update_Header_Appr_Status
(
p_api_version IN NUMBER --
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
,p_commit IN VARCHAR2 := FND_API.G_FALSE --
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL
,p_debug_filename IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_change_id IN NUMBER -- header's change_id
,p_status_code IN NUMBER
,p_appr_status IN NUMBER -- header approval status
,p_route_status IN VARCHAR2 -- workflow routing status (for document types)
,p_api_caller IN VARCHAR2 := 'UI' -- must
,p_bypass IN VARCHAR2 := 'N' -- flag to bypass phase type check
)
IS
l_sfa_line_items_exists VARCHAR2(1);
Update_Header_Appr_Status
(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => p_validation_level
,p_debug => p_debug
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_change_id => p_change_id
,p_status_code => p_status_code
,p_appr_status => p_appr_status
,p_route_status => p_route_status
,p_api_caller => p_api_caller
,p_bypass => p_bypass
,x_sfa_line_items_exists => l_sfa_line_items_exists
);
END Update_Header_Appr_Status;
SELECT DISTINCT route.route_id wf_route_id
, line.change_line_id
FROM eng_change_routes route,
eng_change_statuses s,
eng_change_lines line,
eng_lifecycle_statuses line_start_after,
eng_lifecycle_statuses new_status,
eng_lifecycle_statuses cur_status
WHERE route.status_code = Eng_Workflow_Util.G_RT_NOT_STARTED
AND route.route_id = line.route_id
AND s.status_type <> G_ENG_COMPLETED
AND s.status_type <> G_ENG_IMPLEMENTED
AND s.status_type <> G_ENG_CANCELLED
AND s.status_code = line.status_code
AND line.start_after_status_code = c_new_status_code
AND line.change_id = c_change_id
AND line_start_after.sequence_number <= new_status.sequence_number
AND line_start_after.sequence_number > cur_status.sequence_number
AND line_start_after.entity_name = G_ENG_CHANGE
AND line_start_after.entity_id1 = c_change_id
AND line_start_after.status_code = line.start_after_status_code
AND line_start_after.active_flag = 'Y'
AND new_status.entity_name = G_ENG_CHANGE
AND new_status.entity_id1 = c_change_id
AND new_status.status_code = c_new_status_code
AND new_status.active_flag = 'Y'
AND cur_status.entity_name = G_ENG_CHANGE
AND cur_status.entity_id1 = c_change_id
AND cur_status.status_code = c_cur_status_code
AND cur_status.active_flag = 'Y' ;
SELECT change_wf_route_id, change_wf_route_template_id
INTO l_wf_route_id, l_wf_route_temp_id
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y'
AND rownum = 1;
Write_Debug('Auto-starting workflow is not needed, do post workflow update right away');
Update_Lifecycle_States
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_api_caller => l_api_caller
,p_wf_route_id => NULL
,p_route_status => NULL
,p_comment => p_comment
);
Write_Debug('After: calling Update_Lifecycle_States API: ' || l_return_status) ;
,p_update_ri_flag IN VARCHAR2 := 'Y' -- can also be 'N'
,p_api_caller IN VARCHAR2 := NULL -- can also be 'WF'
,p_comment IN VARCHAR2 := NULL -- only used for co promote-to-implement action
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Promote_Header';
SELECT s.status_type,
l.complete_before_status_code,
l.required_flag,
l.change_type_id ,
route.status_code wf_status_code
FROM eng_change_routes route,
eng_change_statuses s,
eng_change_lines l
WHERE s.status_code = l.status_code
AND route.route_id(+) = l.route_id
AND l.change_id = p_change_id ;
SELECT status_code
FROM eng_revised_items
WHERE change_id = p_change_id
FOR UPDATE;
SELECT op.auto_propagate_flag,
ec.change_notice,
pos.name,
ood.name organization_name
FROM eng_type_org_properties op,
eng_engineering_changes ec,
per_organization_structures pos,
hr_all_organization_units_tl ood
WHERE ec.change_id = p_change_id
--AND ec.PLM_OR_ERP_CHANGE = 'PLM'
AND op.change_type_id = ec.change_order_type_id
AND op.organization_id = ec.organization_id
AND op.propagation_status_code = p_status_code
AND ec.hierarchy_id IS NOT NULL
AND ec.organization_id IS NOT NULL
AND pos.organization_structure_id(+) = ec.hierarchy_id
AND ood.organization_id(+) = ec.organization_id
AND ood.LANGUAGE = USERENV('LANG')
-- R12 UT: Added where clause to not autopropagated if propagation has
-- been initiated to any of the organizations manually or by the
-- TTM process.
AND NOT EXISTS (SELECT 1
FROM eng_change_obj_relationships
WHERE change_id = ec.change_id
AND object_to_name = 'ENG_CHANGE'
AND relationship_code = 'PROPAGATED_TO');
SELECT 'Y'
FROM DUAL
WHERE not exists (SELECT 1
FROM eng_revised_items
WHERE change_id=p_change_id
AND status_code <> 5
);
UPDATE eng_lifecycle_statuses
SET start_date = null,
completion_date = null,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_last_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT sequence_number, status_code
INTO l_curr_phase_sn, l_curr_status_code
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = ( SELECT status_code
FROM eng_engineering_changes
WHERE change_id = p_change_id)
AND active_flag = 'Y'
AND rownum = 1;
SELECT sequence_number
INTO l_new_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT status_type
INTO l_new_status_type
FROM eng_change_statuses
WHERE status_code = p_status_code
AND rownum = 1;
SELECT max(sequence_number)
INTO l_max_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y';
SELECT sequence_number
INTO l_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT eec.change_mgmt_type_code, ecot.base_change_mgmt_type_code, eec.approval_status_type
INTO l_cm_type_code, l_base_cm_type_code, l_eco_approval_status
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;
UPDATE eng_engineering_changes
SET promote_status_code = p_status_code,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
UPDATE eng_lifecycle_statuses
SET start_date = sysdate,
completion_date = null,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y';
SELECT workflow_status, change_wf_route_id
INTO l_wf_status, l_wf_route_id
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y'
AND rownum = 1;
UPDATE eng_lifecycle_statuses
SET change_wf_route_id = l_new_route_id,
workflow_status = Eng_Workflow_Util.G_RT_NOT_STARTED,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y';
UPDATE eng_lifecycle_statuses
SET completion_date = sysdate,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y';
UPDATE eng_engineering_changes
SET status_code = p_status_code,
promote_status_code = null,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
Write_Debug('Before updateing revised items.');
IF ( p_update_ri_flag = 'Y' ) THEN
IF g_debug_flag THEN
Write_Debug('In updateing revised items.');
Write_Debug('In Cursor c_revItems to update revised items.');
SELECT status_type
INTO l_ri_status_type
FROM eng_change_statuses
WHERE status_code = l_ri_status_code
AND rownum = 1;
SELECT sequence_number
INTO l_ri_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_ri_status_code
AND active_flag = 'Y'
AND rownum = 1;
UPDATE eng_revised_items
SET status_code = p_status_code,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE CURRENT OF c_revItems;
END IF; -- p_update_ri_flag = 'Y'
UPDATE eng_lifecycle_statuses
SET start_date = sysdate,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y';
,p_update_ri_flag IN VARCHAR2 := 'Y' -- can also be 'N'
,p_api_caller IN VARCHAR2 := NULL -- can also be 'WF'
,x_sfa_line_items_exists OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Demote_Header';
SELECT *
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND sequence_number >= l_new_phase_sn
AND sequence_number <= l_curr_phase_sn
FOR UPDATE;
SELECT *
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND sequence_number >= l_new_phase_sn
FOR UPDATE;
SELECT *
FROM eng_status_properties
WHERE change_lifecycle_status_id = l_old_row_id
FOR UPDATE;
SELECT status_code
FROM eng_revised_items
WHERE change_id = p_change_id
FOR UPDATE;
select status_type, status_code into l_old_status_code, l_old_status_type from eng_engineering_changes where change_id = p_change_id;
SELECT approval_status_type
INTO l_curr_appr_status
FROM eng_engineering_changes
where change_id = p_change_id;
UPDATE eng_engineering_changes
SET status_type = l_last_status_type,
status_code = l_last_status_code,
promote_status_code = NULL
WHERE change_id = p_change_id;
SELECT sequence_number
INTO l_curr_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND status_code = ( SELECT status_code
FROM eng_engineering_changes
WHERE change_id = p_change_id)
AND rownum = 1;
SELECT sequence_number
INTO l_new_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y'
AND rownum = 1;
UPDATE eng_lifecycle_statuses
SET completion_date = sysdate,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND status_code = ( SELECT status_code
FROM eng_engineering_changes
WHERE change_id = p_change_id)
AND sequence_number = l_curr_phase_sn
AND rownum = 1;
SELECT status_type
INTO l_temp_status_type
FROM eng_change_statuses
WHERE status_code = l_lcStatuses_row.STATUS_CODE;
UPDATE eng_lifecycle_statuses
SET active_flag = 'N',
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE CURRENT OF c_lcStatuses;
SELECT eng_lifecycle_statuses_s.nextval
INTO l_new_row_id
FROM DUAL;
l_lcStatuses_row.last_update_date := sysdate;
l_lcStatuses_row.last_updated_by := l_fnd_user_id;
l_lcStatuses_row.last_update_login := l_fnd_login_id;
UPDATE eng_status_properties
SET change_lifecycle_status_id = l_new_row_id,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE current of c_statusProp;
/* Only Oracle 9.2+ supports this directly row insertion feature
INSERT INTO eng_lifecycle_statuses
VALUES l_lcStatuses_row;
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,
CHANGE_WF_ROUTE_TEMPLATE_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,
WF_SIG_POLICY )
VALUES
( l_lcStatuses_row.CHANGE_LIFECYCLE_STATUS_ID,
l_lcStatuses_row.ENTITY_NAME,
l_lcStatuses_row.ENTITY_ID1,
l_lcStatuses_row.ENTITY_ID2,
l_lcStatuses_row.ENTITY_ID3,
l_lcStatuses_row.ENTITY_ID4,
l_lcStatuses_row.ENTITY_ID5,
l_lcStatuses_row.SEQUENCE_NUMBER,
l_lcStatuses_row.STATUS_CODE,
l_lcStatuses_row.START_DATE,
l_lcStatuses_row.COMPLETION_DATE,
l_lcStatuses_row.CHANGE_WF_ROUTE_ID,
l_lcStatuses_row.CHANGE_WF_ROUTE_TEMPLATE_ID,
l_lcStatuses_row.AUTO_PROMOTE_STATUS,
l_lcStatuses_row.AUTO_DEMOTE_STATUS,
l_lcStatuses_row.WORKFLOW_STATUS,
l_lcStatuses_row.CHANGE_EDITABLE_FLAG,
l_lcStatuses_row.CREATION_DATE,
l_lcStatuses_row.CREATED_BY,
l_lcStatuses_row.LAST_UPDATE_DATE,
l_lcStatuses_row.LAST_UPDATED_BY,
l_lcStatuses_row.LAST_UPDATE_LOGIN,
l_lcStatuses_row.ITERATION_NUMBER,
l_lcStatuses_row.ACTIVE_FLAG,
l_lcStatuses_row.WF_SIG_POLICY );
UPDATE eng_lifecycle_statuses
SET iteration_number = l_old_iter_num + 1,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE CURRENT OF c_lcStatusesToMax;
SELECT status_type
INTO l_new_status_type
FROM eng_change_statuses
WHERE status_code = p_status_code;
UPDATE eng_engineering_changes
SET status_code = p_status_code,
promote_status_code = null,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
Write_Debug('Before: calling Update_Header_Appr_Status');
Update_Header_Appr_Status
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_status_code => p_status_code
,p_appr_status => G_ENG_NOT_SUBMITTED
,p_route_status => NULL
,p_api_caller => p_api_caller
,p_bypass => 'Y'
,x_sfa_line_items_exists => x_sfa_line_items_exists
);
Write_Debug('After: calling Update_Header_Appr_Status: ' || l_return_status) ;
Write_Debug('After: calling Update_Header_Appr_Status');
IF ( p_update_ri_flag = 'Y' ) THEN
-- Demote revised items to the new phase
OPEN c_revItems;
Write_Debug('In Cursor c_revItems to update revised items.');
SELECT status_type
INTO l_ri_status_type
FROM eng_change_statuses
WHERE status_code = l_ri_status_code
AND rownum = 1;
SELECT sequence_number
INTO l_ri_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_ri_status_code
AND active_flag = 'Y'
AND rownum = 1;
UPDATE eng_revised_items
SET status_code = p_status_code,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE CURRENT OF c_revItems;
END IF; -- p_update_ri_flag = 'Y'
,p_update_ri_flag IN VARCHAR2 := 'Y' -- can also be 'N'
,p_api_caller IN VARCHAR2 := NULL -- can also be 'WF'
)
IS
l_sfa_line_items_exists VARCHAR2(1);
,p_update_ri_flag => p_update_ri_flag
,p_api_caller => p_api_caller
,x_sfa_line_items_exists => l_sfa_line_items_exists
);
SELECT sequence_number, status_code
INTO l_ri_phase_sn, l_ri_status_code
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = ( SELECT status_code
FROM eng_revised_items
WHERE revised_item_sequence_id = p_object_id2)
AND active_flag = 'Y'
AND rownum = 1;
SELECT sequence_number
INTO l_new_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT status_type
INTO l_new_status_type
FROM eng_change_statuses
WHERE status_code = p_status_code
AND rownum = 1;
SELECT max(sequence_number)
INTO l_max_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y';
SELECT status_type
INTO l_ri_status_type
FROM eng_change_statuses
WHERE status_code = l_ri_status_code
AND rownum = 1;
SELECT change_notice, organization_id
INTO l_chg_notice, l_org_id
FROM eng_engineering_changes
WHERE change_id = p_change_id;
UPDATE eng_revised_items
SET status_type = G_ENG_IMP_IN_PROGRESS,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id,
Implementation_req_id = l_request_id
WHERE revised_item_sequence_id = p_object_id2;
UPDATE eng_revised_items
SET status_type = G_ENG_IMP_IN_PROGRESS,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id,
Implementation_req_id = l_request_id
WHERE parent_revised_item_seq_id = p_object_id2
AND status_type NOT IN (G_ENG_IMPLEMENTED, G_ENG_CANCELLED);
UPDATE eng_revised_items
SET status_code = p_status_code,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE revised_item_sequence_id = p_object_id2;
/* UPDATE eng_revised_items
SET status_code = p_status_code,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE parent_revised_item_seq_id = p_object_id2
AND status_type NOT IN (G_ENG_IMPLEMENTED, G_ENG_CANCELLED);*/
SELECT sequence_number, status_code
INTO l_ri_phase_sn, l_ri_status_code
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND status_code = ( SELECT status_code
FROM eng_revised_items
WHERE revised_item_sequence_id = p_object_id2)
AND rownum = 1;
SELECT sequence_number
INTO l_new_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT status_type
INTO l_new_status_type
FROM eng_change_statuses
WHERE status_code = p_status_code;
SELECT status_type
INTO l_ri_status_type
FROM eng_change_statuses
WHERE status_code = l_ri_status_code
AND rownum = 1;
UPDATE eng_revised_items
SET status_code = p_status_code,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE revised_item_sequence_id = p_object_id2;
UPDATE eng_revised_items
SET status_code = p_status_code,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE parent_revised_item_seq_id = p_object_id2
AND status_type NOT IN (G_ENG_IMPLEMENTED, G_ENG_CANCELLED);
SELECT sequence_number
INTO l_curr_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND status_code = ( SELECT status_code
FROM eng_engineering_changes
WHERE change_id = p_change_id)
AND rownum = 1;
,p_update_ri_flag => 'N'
,p_api_caller => l_api_caller
);
,p_update_ri_flag IN VARCHAR2 := 'Y' -- can also be 'N'
,p_api_caller IN VARCHAR2 := 'UI'
,p_action_type IN VARCHAR2 := G_ENG_PROMOTE -- promote/demote
,p_comment IN VARCHAR2 := NULL -- only used for co promote-to-implement action
,x_sfa_line_items_exists OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Change_Phase';
SELECT status_code, status_type
INTO l_curr_status_code, l_curr_status_type
FROM eng_engineering_changes
WHERE change_id = p_change_id;
SELECT change_wf_route_id, workflow_status
INTO l_wf_route_id, l_wf_status
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND rownum = 1;
,p_update_ri_flag => p_update_ri_flag
,p_api_caller => p_api_caller
,p_comment => p_comment
);
,p_update_ri_flag IN VARCHAR2 := 'Y' -- can also be 'N'
,p_api_caller IN VARCHAR2 := 'UI'
,p_action_type IN VARCHAR2 := G_ENG_PROMOTE -- promote/demote
,p_comment IN VARCHAR2 := NULL -- only used for co promote-to-implement action
)
IS
l_sfa_line_items_exists VARCHAR2(1);
,p_update_ri_flag => p_update_ri_flag
,p_api_caller => p_api_caller
,p_action_type => p_action_type
,p_comment => p_comment
,x_sfa_line_items_exists => l_sfa_line_items_exists
);
PROCEDURE Update_Lifecycle_States
(
p_api_version IN NUMBER --
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
,p_commit IN VARCHAR2 := FND_API.G_FALSE --
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL
,p_debug_filename IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_change_id IN NUMBER
,p_status_code IN NUMBER := NULL -- passed only by WF call for p_route_status = IN_PROGRESS or CP for imp failure
,p_api_caller IN VARCHAR2 := NULL -- or 'WF'
,p_wf_route_id IN NUMBER
,p_route_status IN VARCHAR2
,p_comment IN VARCHAR2 := NULL -- only used for co promote-to-implement action
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Lifecycle_States';
l_nir_update_flag VARCHAR2(1) := 'F';
SELECT s.status_type
FROM eng_change_lines l,
eng_change_statuses s
WHERE l.change_id = p_change_id
AND s.status_code = l.status_code;
SAVEPOINT Update_Lifecycle_States;
Write_Debug('ENG_CHANGE_LIFECYCLE_UTIL.Update_Lifecycle_States log');
SELECT eec.status_code, eec.promote_status_code, eec.change_mgmt_type_code,
ecot.base_change_mgmt_type_code
INTO l_curr_status_code, l_next_status_code, l_cm_type_code,
l_base_cm_type_code
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;
SELECT status_type
INTO l_curr_status_type
FROM eng_change_statuses
WHERE status_code = l_curr_status_code;
SELECT sequence_number
INTO l_curr_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT max(sequence_number)
INTO l_max_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y';
SELECT status_code, change_wf_route_id
INTO l_last_status_code, l_last_wf_route_id
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND sequence_number = l_max_phase_sn;
UPDATE eng_lifecycle_statuses
SET workflow_status = p_route_status,
completion_date = sysdate, -- newly added for 3479509 fix (launch wf after implementation)
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_next_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT change_notice, organization_id
INTO l_chg_notice, l_org_id
FROM eng_engineering_changes
WHERE change_id = p_change_id;
UPDATE eng_engineering_changes
SET implementation_req_id = l_request_id
WHERE change_id = p_change_id;
UPDATE eng_engineering_changes
SET status_type = G_ENG_IMP_FAILED,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
UPDATE eng_engineering_changes
SET status_type = G_ENG_IMP_IN_PROGRESS,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
UPDATE eng_lifecycle_statuses
SET workflow_status = p_route_status,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND rownum = 1;
UPDATE eng_lifecycle_statuses
SET completion_date = sysdate,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND rownum = 1;
Write_Debug('Current phase row updated');
SELECT approval_status_type
INTO l_curr_appr_status
FROM eng_engineering_changes
where change_id = p_change_id;
l_nir_update_flag := 'Y';
IF ( (l_curr_status_type = G_ENG_APPROVED OR l_nir_update_flag = 'Y')
AND l_chg_appr_status IS NOT NULL
)
THEN
IF g_debug_flag THEN
Write_Debug('Current phase is of type APPROVAL, or last NIR phase needs to force header approval status to APPROVED');
SELECT max(lcs.sequence_number)
INTO l_max_appr_phase_sn
FROM eng_lifecycle_statuses lcs,
eng_change_statuses chs
WHERE lcs.entity_name = G_ENG_CHANGE
AND lcs.entity_id1 = p_change_id
AND lcs.active_flag = 'Y'
AND chs.status_code = lcs.status_code
AND chs.status_type = G_ENG_APPROVED;
OR l_nir_update_flag = 'Y'
)
THEN
IF g_debug_flag THEN
Write_Debug('Current phase is the last of such type');
Write_Debug('Before: calling Update_Header_Appr_Status');
Update_Header_Appr_Status
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_status_code => l_curr_status_code
,p_appr_status => l_chg_appr_status
,p_route_status => p_route_status
,p_api_caller => p_api_caller
,p_bypass => l_nir_update_flag
);
Write_Debug('After: Update_Header_Appr_Status: ' || l_return_status );
Write_Debug('After: calling Update_Header_Appr_Status');
SELECT eec.change_mgmt_type_code, eec.change_notice, eec.organization_id,
ecot.base_change_mgmt_type_code
INTO l_cm_type_code, l_chg_notice, l_org_id,
l_base_cm_type_code
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;
ENG_ATTACHMENT_IMPLEMENTATION.Update_Attachment_Status
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_debug => p_debug --FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => NULL --p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_workflow_status => p_status_code
,p_approval_status => G_ENG_APPR_APPROVED --l_chg_appr_status
,p_api_caller => p_api_caller
);
Write_Debug('After: ENG_ATTACHMENT_IMPLEMENTATION.Update_Attachment_Status: ' || l_return_status );
SELECT promote_status_code
INTO l_next_status_code
FROM eng_engineering_changes
WHERE change_id = p_change_id;
SELECT auto_promote_status
INTO l_next_status_code
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND rownum = 1;
UPDATE eng_lifecycle_statuses
SET workflow_status = p_route_status,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND rownum = 1;
SELECT auto_demote_status
INTO l_next_status_code
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND rownum = 1;
UPDATE eng_engineering_changes
SET promote_status_code = NULL,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
SELECT status_type
INTO l_status_type
FROM eng_change_statuses
WHERE status_code = p_status_code;
Write_Debug('Before: calling Update_Header_Appr_Status');
Update_Header_Appr_Status
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_status_code => p_status_code
,p_appr_status => l_chg_appr_status
,p_route_status => p_route_status
,p_api_caller => p_api_caller
);
Write_Debug('After: calling Update_Header_Appr_Status: ' || l_return_status);
Write_Debug('Successful: calling Update_Header_Appr_Status');
UPDATE eng_lifecycle_statuses
SET workflow_status = p_route_status,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_curr_status_code
AND active_flag = 'Y'
AND rownum = 1;
UPDATE eng_engineering_changes
SET promote_status_code = NULL,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
Update_Header_Appr_Status
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_status_code => p_status_code
,p_appr_status => l_chg_appr_status
,p_route_status => p_route_status
,p_api_caller => p_api_caller
);
Write_Debug('After: calling Update_Header_Appr_Status: ' || l_return_status);
Write_Debug('After: calling Update_Header_Appr_Status');
UPDATE eng_lifecycle_statuses
SET workflow_status = p_route_status,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y';
SELECT status_type
INTO l_status_type
FROM eng_change_statuses
WHERE status_code = p_status_code;
Write_Debug('Before: calling Update_Header_Appr_Status');
Update_Header_Appr_Status
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_status_code => p_status_code
,p_appr_status => G_ENG_APPR_REQUESTED
,p_route_status => p_route_status
,p_api_caller => p_api_caller
);
Write_Debug('After: calling Update_Header_Appr_Status: ' || l_return_status);
Write_Debug('Successful: calling Update_Header_Appr_Status');
UPDATE eng_engineering_changes
SET status_type = G_ENG_IMP_FAILED,
promote_status_code = NULL,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
UPDATE eng_engineering_changes
SET status_type = G_ENG_IMPLEMENTED,
status_code = l_last_status_code,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
UPDATE eng_engineering_changes
SET status_type = p_status_code, --G_ENG_IMP_FAILED
promote_status_code = NULL,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE change_id = p_change_id;
ENG_DOCUMENT_UTIL.Update_Approval_Status
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status --
,x_msg_count => l_msg_count --
,x_msg_data => l_msg_data --
,p_change_id => p_change_id -- header's change_id
,p_approval_status => l_chg_appr_status -- header approval status
,p_wf_route_status => p_route_status -- workflow routing status (for document types)
,p_api_caller => p_api_caller -- Optionnal for future use
);
ENG_DOCUMENT_UTIL.Update_Approval_Status
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status --
,x_msg_count => l_msg_count --
,x_msg_data => l_msg_data --
,p_change_id => p_change_id -- header's change_id
,p_approval_status => l_chg_appr_status -- header approval status
,p_wf_route_status => p_route_status -- workflow routing status (for document types)
,p_api_caller => p_api_caller -- Optionnal for future use
);
Write_Debug('Finish. End Of procedure: Update_Lifecycle_States');
END Update_Lifecycle_States;
SELECT *
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = p_status_code
AND active_flag = 'Y'
FOR UPDATE;
UPDATE eng_lifecycle_statuses
SET change_wf_route_id = l_wf_route_id_new,
workflow_status = ENG_WORKFLOW_UTIL.G_RT_NOT_STARTED,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE CURRENT OF c_currPhase;
Write_Debug('Updated route_id') ;
UPDATE eng_lifecycle_statuses
SET change_wf_route_id = p_wf_route_id,
workflow_status = ENG_WORKFLOW_UTIL.G_RT_NOT_STARTED,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE CURRENT OF c_currPhase;
Write_Debug('Updated route_id') ;
SELECT op.auto_propagate_flag,
ec.change_notice,
pos.name,
ood.name organization_name
FROM eng_type_org_properties op,
eng_engineering_changes ec,
per_organization_structures pos,
hr_all_organization_units_tl ood
WHERE ec.change_id = p_change_id
--AND ec.PLM_OR_ERP_CHANGE = 'PLM'
AND op.change_type_id = ec.change_order_type_id
AND op.organization_id = ec.organization_id
AND op.propagation_status_code = l_status_code
AND ec.hierarchy_id IS NOT NULL
AND ec.organization_id IS NOT NULL
AND pos.organization_structure_id(+) = ec.hierarchy_id
AND ood.organization_id(+) = ec.organization_id
AND ood.LANGUAGE = USERENV('LANG');
SELECT min(sequence_number)
INTO l_initial_phase_sn
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND (status_code = p_init_status_code OR p_init_status_code IS NULL) ;
SELECT lcs.status_code, ecs.status_type
INTO l_status_code, l_status_type
FROM eng_lifecycle_statuses lcs,
eng_change_statuses ecs
WHERE lcs.entity_name = G_ENG_CHANGE
AND lcs.entity_id1 = p_change_id
AND lcs.active_flag = 'Y'
AND lcs.sequence_number = l_initial_phase_sn
AND ecs.status_code = lcs.status_code
AND rownum = 1;
UPDATE eng_engineering_changes
SET status_code = l_status_code,
status_type = l_status_type,
initiation_date = sysdate
WHERE change_id = p_change_id;
SELECT status_code, status_type
INTO l_status_code, l_status_type
FROM eng_engineering_changes
WHERE change_id = p_change_id;
SELECT sequence_number, change_wf_route_id
INTO l_sequence_number, l_wf_route_id
FROM eng_lifecycle_statuses
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND status_code = l_status_code;
UPDATE eng_lifecycle_statuses
SET workflow_status = Eng_Workflow_Util.G_RT_NOT_STARTED,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND active_flag = 'Y'
AND change_wf_route_id IS NOT NULL;
UPDATE eng_lifecycle_statuses
SET start_date = sysdate,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE entity_name = G_ENG_CHANGE
AND entity_id1 = p_change_id
AND status_code = l_status_code
AND active_flag = 'Y';
SELECT eec.change_mgmt_type_code, ecot.base_change_mgmt_type_code
INTO l_cm_type_code, l_base_cm_type_code
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = p_change_id
AND ecot.change_order_type_id = eec.change_order_type_id;
l_param_list.DELETE;
PROCEDURE Update_RevItem_Lifecycle
(
p_api_version IN NUMBER --
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
,p_commit IN VARCHAR2 := FND_API.G_FALSE --
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := NULL
,p_debug_filename IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_rev_item_seq_id IN NUMBER
,p_status_type IN NUMBER -- say 10 for imp_failed
,p_api_caller IN VARCHAR2 := NULL -- 'CP'
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_RevItem_Lifecycle';
Write_Debug('ENG_CHANGE_LIFECYCLE_UTIL.Update_RevItem_Lifecycle log');
UPDATE eng_revised_items
SET status_type = p_status_type,
last_update_date = sysdate,
last_updated_by = l_fnd_user_id,
last_update_login = l_fnd_login_id
WHERE revised_item_sequence_id = p_rev_item_seq_id;
Write_Debug('Finish. End Of procedure: Update_RevItem_Lifecycle') ;
END Update_RevItem_Lifecycle;
SELECT eec.status_code
, eec.promote_status_code
, eec.change_mgmt_type_code
, ecot.base_change_mgmt_type_code
FROM eng_engineering_changes eec,
eng_change_order_types ecot
WHERE eec.change_id = c_change_id
AND ecot.change_order_type_id = eec.change_order_type_id ;
SELECT status_type
INTO l_curr_status_type
FROM eng_change_statuses
WHERE status_code = l_reset_status_code;
Update_Header_Appr_Status
(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_change_id => p_change_id
,p_status_code => l_reset_status_code
,p_appr_status => G_ENG_APPR_REQUESTED
,p_route_status => Eng_Workflow_Util.G_RT_NOT_STARTED
,p_api_caller => p_api_caller
,p_bypass => 'N'
);
ENG_DOCUMENT_UTIL.Update_Approval_Status
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_debug => FND_API.G_FALSE
,p_output_dir => p_output_dir
,p_debug_filename => p_debug_filename
,x_return_status => l_return_status --
,x_msg_count => l_msg_count --
,x_msg_data => l_msg_data --
,p_change_id => p_change_id -- header's change_id
,p_approval_status => G_ENG_APPR_REQUESTED-- header approval status
,p_wf_route_status => Eng_Workflow_Util.G_RT_NOT_STARTED -- workflow routing status (for document types)
,p_api_caller => p_api_caller -- Optionnal for future use
);
Write_Debug('After: Update_Header_Appr_Status: ' || l_return_status );
Write_Debug('After: calling Update_Header_Appr_Status');
SELECT CHANGE_LIFECYCLE_STATUS_ID
, SEQUENCE_NUMBER
, STATUS_CODE
, AUTO_PROMOTE_STATUS
, AUTO_DEMOTE_STATUS
, CHANGE_EDITABLE_FLAG
, CHANGE_WF_ROUTE_ID
, ENTITY_ID1 CHANGE_TYPE_ID
FROM eng_lifecycle_statuses
WHERE entity_name = 'ENG_CHANGE_TYPE'
AND entity_id1 = c_change_type_id
ORDER BY SEQUENCE_NUMBER ASC ;
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
)
SELECT
ENG_LIFECYCLE_STATUSES_S.NEXTVAL
, G_ENG_CHANGE
, ch.CHANGE_ID
, NULL -- ENTITY_ID2
, NULL -- ENTITY_ID3
, NULL -- ENTITY_ID4
, NULL -- ENTITY_ID5
, change_type.SEQUENCE_NUMBER
, change_type.STATUS_CODE
, NULL -- START_DATE
, NULL -- COMPLETION_DATE
, NULL -- CHANGE_WF_ROUTE_ID
, change_type.AUTO_PROMOTE_STATUS
, change_type.AUTO_DEMOTE_STATUS
, NULL -- WORKFLOW_STATUS
, change_type.CHANGE_EDITABLE_FLAG
, SYSDATE
, l_fnd_user_id
, SYSDATE
, l_fnd_user_id
, l_fnd_login_id
, cur_phase.ITERATION_NUMBER
, 'S'
, change_type.CHANGE_WF_ROUTE_ID
FROM ENG_ENGINEERING_CHANGES ch
, ENG_LIFECYCLE_STATUSES cur_phase
, ENG_LIFECYCLE_STATUSES change_type
WHERE change_type.entity_id1 = p_change_type_id
AND change_type.entity_name = 'ENG_CHANGE_TYPE'
AND ch.CHANGE_ORDER_TYPE_ID = change_type.entity_id1
AND ch.STATUS_TYPE NOT IN (0, 5, 6, 11) -- exclude draft, cancel, implemented, completed
AND ch.plm_or_erp_change = 'PLM'
AND cur_phase.entity_name = G_ENG_CHANGE
AND cur_phase.entity_id1 = ch.CHANGE_ID
AND cur_phase.active_flag = 'Y'
AND cur_phase.status_code = ch.STATUS_CODE
AND cur_phase.SEQUENCE_NUMBER < change_type.SEQUENCE_NUMBER
AND NOT EXISTS ( SELECT 'exists'
FROM ENG_LIFECYCLE_STATUSES change_lc_phase
WHERE change_lc_phase.entity_name = G_ENG_CHANGE
AND change_lc_phase.entity_id1 = ch.CHANGE_ID
AND change_lc_phase.active_flag = 'Y'
AND change_lc_phase.status_code = change_type.STATUS_CODE
AND change_lc_phase.SEQUENCE_NUMBER = change_type.SEQUENCE_NUMBER
) ;
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
)
SELECT
ENG_LIFECYCLE_STATUSES_S.NEXTVAL
, G_ENG_CHANGE
, ch.CHANGE_ID
, NULL -- ENTITY_ID2
, NULL -- ENTITY_ID3
, NULL -- ENTITY_ID4
, NULL -- ENTITY_ID5
, change_type.SEQUENCE_NUMBER
, change_type.STATUS_CODE
, NULL -- START_DATE
, NULL -- COMPLETION_DATE
, NULL -- CHANGE_WF_ROUTE_ID
, change_type.AUTO_PROMOTE_STATUS
, change_type.AUTO_DEMOTE_STATUS
, NULL -- WORKFLOW_STATUS
, change_type.CHANGE_EDITABLE_FLAG
, SYSDATE
, l_fnd_user_id
, SYSDATE
, l_fnd_user_id
, l_fnd_login_id
, 0 -- ITERATION_NUMBER
, 'S' -- ACTIVE_FLAG
, change_type.CHANGE_WF_ROUTE_ID
FROM ENG_ENGINEERING_CHANGES ch
, ENG_LIFECYCLE_STATUSES change_type
WHERE change_type.entity_id1 = p_change_type_id
AND change_type.entity_name = 'ENG_CHANGE_TYPE'
AND ch.CHANGE_ORDER_TYPE_ID = change_type.entity_id1
AND ch.STATUS_CODE = 0 -- DRAFT
AND NOT EXISTS ( SELECT 'exists'
FROM ENG_LIFECYCLE_STATUSES change_lc_phase
WHERE change_lc_phase.entity_name = G_ENG_CHANGE
AND change_lc_phase.entity_id1 = ch.CHANGE_ID
AND change_lc_phase.active_flag = 'Y'
AND change_lc_phase.status_code = change_type.STATUS_CODE
AND change_lc_phase.SEQUENCE_NUMBER = change_type.SEQUENCE_NUMBER
) ;
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
lc_phase.CHANGE_LIFECYCLE_STATUS_ID
, phase_prop_setup.STATUS_CODE
, phase_prop_setup.PROMOTION_STATUS_FLAG
, SYSDATE
, l_fnd_user_id
, SYSDATE
, l_fnd_user_id
, l_fnd_login_id
FROM ENG_STATUS_PROPERTIES phase_prop_setup
, ENG_LIFECYCLE_STATUSES lc_phase_setup
, ENG_LIFECYCLE_STATUSES lc_phase
, ENG_ENGINEERING_CHANGES ch
WHERE lc_phase_setup.entity_id1 = p_change_type_id
AND lc_phase_setup.entity_name = 'ENG_CHANGE_TYPE'
AND phase_prop_setup.CHANGE_LIFECYCLE_STATUS_ID = lc_phase_setup.CHANGE_LIFECYCLE_STATUS_ID
AND ch.CHANGE_ORDER_TYPE_ID = lc_phase_setup.ENTITY_ID1
AND ch.STATUS_TYPE NOT IN (5, 6, 11) -- exclude cancel, implemented, completed
AND lc_phase.entity_name = G_ENG_CHANGE
AND lc_phase.entity_id1 = ch.CHANGE_ID
AND lc_phase.SEQUENCE_NUMBER = lc_phase_setup.SEQUENCE_NUMBER
AND lc_phase.STATUS_CODE = lc_phase_setup.STATUS_CODE
AND lc_phase.ACTIVE_FLAG = 'S' ;
UPDATE ENG_LIFECYCLE_STATUSES
SET ACTIVE_FLAG = 'D'
WHERE CHANGE_LIFECYCLE_STATUS_ID IN (
SELECT change_phase.CHANGE_LIFECYCLE_STATUS_ID
FROM ENG_ENGINEERING_CHANGES ch
, ENG_LIFECYCLE_STATUSES cur_phase
, ENG_LIFECYCLE_STATUSES change_phase
WHERE ch.CHANGE_ORDER_TYPE_ID = p_change_type_id
AND ch.STATUS_TYPE NOT IN (5, 6, 11) -- exclude cancel, implemented, completed
AND cur_phase.entity_name = G_ENG_CHANGE
AND cur_phase.entity_id1 = ch.CHANGE_ID
AND cur_phase.active_flag = 'Y'
AND cur_phase.status_code = ch.STATUS_CODE
AND cur_phase.SEQUENCE_NUMBER < change_phase.SEQUENCE_NUMBER
AND change_phase.entity_name = 'ENG_CHANGE'
AND change_phase.entity_id1 = ch.CHANGE_ID
AND change_phase.active_flag = 'Y'
AND NOT EXISTS ( SELECT 'exists'
FROM eng_lifecycle_statuses lc_phase_setup
WHERE lc_phase_setup.entity_name = 'ENG_CHANGE_TYPE'
AND lc_phase_setup.entity_id1 = ch.CHANGE_ORDER_TYPE_ID
AND lc_phase_setup.SEQUENCE_NUMBER = change_phase.SEQUENCE_NUMBER
AND lc_phase_setup.STATUS_CODE = change_phase.STATUS_CODE
)
UNION ALL
SELECT change_phase.CHANGE_LIFECYCLE_STATUS_ID
FROM ENG_ENGINEERING_CHANGES ch
, ENG_LIFECYCLE_STATUSES change_phase
WHERE ch.CHANGE_ORDER_TYPE_ID = p_change_type_id
AND ch.STATUS_CODE = 0 -- DRAFT
AND ch.STATUS_TYPE NOT IN (5, 6, 11) -- exclude cancel, implemented, completed
AND change_phase.entity_name = G_ENG_CHANGE
AND change_phase.entity_id1 = ch.CHANGE_ID
AND change_phase.active_flag = 'Y'
AND NOT EXISTS ( SELECT 'exists'
FROM eng_lifecycle_statuses lc_phase_setup
WHERE lc_phase_setup.entity_name = 'ENG_CHANGE_TYPE'
AND lc_phase_setup.entity_id1 = ch.CHANGE_ORDER_TYPE_ID
AND lc_phase_setup.SEQUENCE_NUMBER = change_phase.SEQUENCE_NUMBER
AND lc_phase_setup.STATUS_CODE = change_phase.STATUS_CODE
)
) ;
Write_Debug(' Mass Update Change LC Phases marked as S to make them Activie. . .') ;
UPDATE ENG_LIFECYCLE_STATUSES lc_phase
SET lc_phase.ACTIVE_FLAG = 'Y'
WHERE lc_phase.CHANGE_LIFECYCLE_STATUS_ID IN (
SELECT added_change_phase.CHANGE_LIFECYCLE_STATUS_ID
FROM ENG_ENGINEERING_CHANGES ch
, ENG_LIFECYCLE_STATUSES added_change_phase
WHERE ch.CHANGE_ORDER_TYPE_ID = p_change_type_id
AND ch.STATUS_TYPE NOT IN (5, 6, 11) -- exclude cancel, implemented, completed
AND added_change_phase.entity_name = G_ENG_CHANGE
AND added_change_phase.entity_id1 = ch.CHANGE_ID
AND added_change_phase.active_flag = 'S'
) ;
Write_Debug(' Mass Update Change to make added phase active: ' || to_char(SQL%ROWCOUNT)) ;