[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
select value
INTO l_utl_file_dir
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT user_name
INTO l_user_name
FROM FND_USER
WHERE user_id = p_user_id ;
SELECT ENG_WORKFLOW_REVISION_S.NEXTVAL
INTO l_rev_seq
FROM DUAL;
SELECT change_id
FROM ENG_CHANGE_LINES
WHERE change_line_id = p_change_line_id ;
SELECT change_notice
FROM ENG_ENGINEERING_CHANGES
WHERE change_id = p_change_id;
SELECT ecl.sequence_number
FROM ENG_CHANGE_LINES ecl
WHERE ecl.change_line_id = p_change_line_id ;
SELECT
MP.organization_code organization_code,
HAOTL.name organization_name
INTO x_organization_code
, x_organization_name
FROM
HR_ALL_ORGANIZATION_UNITS_TL HAOTL,
MTL_PARAMETERS MP
WHERE
HAOTL.organization_id = p_organization_id
AND HAOTL.organization_id = MP.ORGANIZATION_ID
AND HAOTL.LANGUAGE = USERENV('LANG');
SELECT party_type
INTO l_party_type
FROM HZ_PARTIES
WHERE party_id = p_party_id ;
SELECT 'Role Exists'
FROM DUAL
WHERE EXISTS ( SELECT null
from WF_LOCAL_ROLES
WHERE NAME = p_role_name
AND ORIG_SYSTEM = 'WF_LOCAL_ROLES'
AND ORIG_SYSTEM_ID = 0
) ;
PROCEDURE DeleteRoleAndUsers
( p_role_name IN VARCHAR2)
IS
BEGIN
/* This might NOT be following standard
-- Deleting these adhoc role and user roles
-- should be done by WF Purge Program
-- Instead of this, Set Adhoc Role Expiration
-- using WF API. Then once user run WF Purge progam
-- deleting these role and user roles is taken care of.
-- DELETE FROM wf_local_roles
-- WHERE name = p_role_name ;
END DeleteRoleAndUsers ;
SELECT EngSecPeople.user_name user_role
FROM ENG_SECURITY_PEOPLE_V EngSecPeople
WHERE EngSecPeople.person_id = p_party_id ;
SELECT member.member_user_name user_role
FROM ENG_SECURITY_GROUP_MEMBERS_V member
WHERE member.group_id = p_group_id ;
SELECT EngSecPeople.user_name user_role
FROM ENG_SECURITY_PEOPLE_V EngSecPeople
, ENG_CHANGE_ROUTE_PEOPLE step_people
, ENG_CHANGE_ROUTE_STEPS step
WHERE EngSecPeople.person_id = step_people.assignee_id
AND step_people.assignee_type_code = Eng_Workflow_Util.G_PERSON
AND step_people.assignee_id <> -1
AND step_people.step_id = step.step_id
AND step.step_status_code <> Eng_Workflow_Util.G_RT_NOT_STARTED
AND step.step_start_date IS NOT NULL
AND step.route_id = p_route_id ;
SELECT EngSecPeople.user_name user_role
FROM ENG_SECURITY_PEOPLE_V EngSecPeople
, ENG_CHANGE_ROUTE_PEOPLE step_people
WHERE EngSecPeople.person_id = step_people.assignee_id
AND step_people.assignee_id <> -1
AND step_people.assignee_type_code = Eng_Workflow_Util.G_PERSON
AND step_people.step_id = p_route_step_id
AND ( step_people.response_code IS NULL
OR step_people.response_code = Eng_Workflow_Util.G_RT_SUBMITTED
OR step_people.response_code = Eng_Workflow_Util.G_RT_NOT_RECEIVED
) ;
SELECT EngSecPeople.user_name user_role
FROM ENG_SECURITY_PEOPLE_V EngSecPeople
WHERE EngSecPeople.person_id = p_party_id ;
SELECT member.member_user_name user_role
FROM ENG_SECURITY_GROUP_MEMBERS_V member
WHERE member.group_id = p_group_id ;
SELECT EngSecPeople.user_name user_role
FROM ENG_SECURITY_PEOPLE_V EngSecPeople
, ENG_CHANGE_ROUTE_PEOPLE step_people
, ENG_CHANGE_ROUTE_STEPS step
WHERE EngSecPeople.person_id = step_people.assignee_id
AND step_people.assignee_type_code = Eng_Workflow_Util.G_PERSON
AND step_people.assignee_id <> -1
AND step_people.step_id = step.step_id
AND step.step_status_code <> Eng_Workflow_Util.G_RT_NOT_STARTED
AND step.step_start_date IS NOT NULL
AND step.route_id = p_route_id ;
SELECT EngSecPeople.user_name user_role
FROM ENG_SECURITY_PEOPLE_V EngSecPeople
, ENG_CHANGE_ROUTE_PEOPLE step_people
WHERE EngSecPeople.person_id = step_people.assignee_id
AND step_people.assignee_id <> -1
AND step_people.assignee_type_code = Eng_Workflow_Util.G_PERSON
AND step_people.step_id = p_route_step_id
AND ( step_people.response_code IS NULL
OR step_people.response_code = Eng_Workflow_Util.G_RT_SUBMITTED
OR step_people.response_code = Eng_Workflow_Util.G_RT_NOT_RECEIVED
) ;
SELECT eec.assignee_id
, eec.requestor_id
, eec.created_by
INTO x_assignee_party_id
, x_requestor_party_id
, x_creator_user_id
FROM ENG_ENGINEERING_CHANGES eec
WHERE eec.change_id = p_change_id ;
SELECT ecl.assignee_id
, ecl.created_by
INTO x_assignee_party_id
, x_creator_user_id
FROM ENG_CHANGE_LINES ecl
WHERE ecl.change_line_id = p_change_line_id ;
SELECT revision_id
, revision
FROM ENG_CHANGE_ORDER_REVISIONS
WHERE start_date <= SYSDATE
AND (end_date >= TRUNC(SYSDATE) OR end_date IS NULL)
AND change_id = p_change_id
ORDER BY revision ;
SELECT ATTACHMENT_ID
, SOURCE_MEDIA_ID
, REPOSITORY_ID
, CREATED_BY
FROM ENG_ATTACHMENT_CHANGES eec
WHERE eec.change_id = p_change_id ;
SELECT 'Y' AS enable_rev_items_flag -- ecmt.enable_rev_items_flag
, 'Y' AS enable_tasks_flag -- ecmt.enable_tasks_flag
FROM ENG_CHANGE_MGMT_TYPES ecmt,
ENG_ENGINEERING_CHANGES eec
WHERE ecmt.change_mgmt_type_code = eec.change_mgmt_type_code
AND eec.change_id = p_change_id ;
SELECT document_entity_id
, pk1_column
, pk2_column
, pk3_column
, pk4_column
, pk5_column
FROM fnd_document_entities
WHERE data_object_code = p_data_object_code ;
INSERT INTO ENG_ECO_SUBMIT_REVISIONS
( change_notice
, organization_id
, process_name
, revision_id
, request_id
, submit_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
)
VALUES
( p_change_notice
, p_organization_id
, p_Process_Name
, p_item_key
, ''
, SYSDATE
, SYSDATE
, p_wf_user_id
, SYSDATE
, p_wf_user_id
, p_wf_user_id
);
select WI.BEGIN_DATE
, WI.ROOT_ACTIVITY
into l_begin_date
, l_process_name
from WF_ITEMS WI
where WI.ITEM_TYPE = p_item_type
and WI.ITEM_KEY = p_item_key ;
select WIAS.ACTIVITY_STATUS
-- , PA.ACTIVITY_NAME
-- , WIAS.ACTIVITY_RESULT_CODE,
-- , WIAS.ASSIGNED_USER,
-- , WIAS.NOTIFICATION_ID,
-- , WIAS.BEGIN_DATE, WIAS.END_DATE,
-- , WIAS.DUE_DATE,
-- , WIAS.ERROR_NAME, WIAS.ERROR_MESSAGE,
-- , WIAS.ERROR_STACK
into x_activity_status
from WF_ITEM_ACTIVITY_STATUSES WIAS
, WF_PROCESS_ACTIVITIES PA
, WF_ACTIVITIES A
where WIAS.ITEM_TYPE = p_item_type
and WIAS.ITEM_KEY = p_item_key
and WIAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and PA.PROCESS_ITEM_TYPE = p_item_type
and PA.PROCESS_NAME = l_process_name
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
and A.ITEM_TYPE = p_activity_item_type
and A.NAME = p_activity_name
and l_begin_date >= A.BEGIN_DATE
and l_begin_date < NVL(A.END_DATE, l_begin_date + 1) ;
SELECT grp.party_name party_name
, company.party_name comp_name
INTO x_party_name
, x_party_company
FROM HZ_RELATIONSHIPS emp_cmpy,
HZ_PARTIES company,
HZ_RELATIONSHIPS owner_group_rel,
HZ_PARTIES owner ,
HZ_PARTIES grp
WHERE emp_cmpy.subject_type (+)= 'PERSON'
AND emp_cmpy.subject_table_name (+)= 'HZ_PARTIES'
AND emp_cmpy.object_type (+)= 'ORGANIZATION'
AND emp_cmpy.relationship_code (+)= 'EMPLOYEE_OF'
AND emp_cmpy.object_table_name(+)= 'HZ_PARTIES'
AND emp_cmpy.status (+)= 'A'
AND emp_cmpy.start_date (+)<= SYSDATE
AND ( emp_cmpy.end_date IS NULL OR emp_cmpy.end_date >= SYSDATE)
AND company.party_id (+)= emp_cmpy.object_id
AND company.status (+)= 'A'
AND emp_cmpy.subject_id (+)= owner.party_id
-- AND owner.status='A'
AND owner.party_id = owner_group_rel.subject_id
AND owner_group_rel.subject_type = 'PERSON'
AND owner_group_rel.subject_table_name = 'HZ_PARTIES'
AND owner_group_rel.object_type = 'GROUP'
AND owner_group_rel.object_table_name = 'HZ_PARTIES'
AND owner_group_rel.relationship_code = 'OWNER_OF'
AND owner_group_rel.status = 'A'
AND owner_group_rel.start_date <= SYSDATE
AND ( owner_group_rel.end_date IS NULL OR owner_group_rel.end_date >= SYSDATE)
AND owner_group_rel.object_id = grp.party_id
-- AND grp.status = 'A'
AND grp.party_id = p_party_id ;
SELECT grp.group_name party_name
, '' comp_name
INTO x_party_name
, x_party_company
FROM EGO_GROUPS_V grp
WHERE grp.group_id = p_party_id ;
SELECT employee.party_name party_name
, company.party_name comp_name
INTO x_party_name
, x_party_company
FROM hz_parties employee
, hz_relationships emp_cmpy
, hz_parties company
WHERE emp_cmpy.subject_type (+)= 'PERSON'
AND emp_cmpy.subject_table_name (+)= 'HZ_PARTIES'
AND emp_cmpy.object_type (+)= 'ORGANIZATION'
AND emp_cmpy.relationship_code (+)= 'EMPLOYEE_OF'
AND emp_cmpy.object_table_name (+)= 'HZ_PARTIES'
AND emp_cmpy.status (+)= 'A'
AND emp_cmpy.start_date (+) <= SYSDATE
AND (emp_cmpy.end_date IS NULL OR emp_cmpy.end_date >= SYSDATE)
-- AND employee.status = 'A'
AND company.party_id (+) = emp_cmpy.object_id
AND company.status (+)= 'A'
AND emp_cmpy.subject_id (+)= employee.party_id
AND employee.party_id = p_party_id ;
SELECT person_name party_name
, company_name comp_name
INTO x_party_name
, x_party_company
FROM EGO_PEOPLE_V
WHERE person_id = p_party_id
AND ROWNUM = 1 ;
SELECT WfActAttr.TEXT_DEFAULT DEFAULT_ROLE_NAME,
WfActAttr2.TEXT_DEFAULT ACTIVITY_CONDITION_CODE
FROM WF_ACTIVITIES WfAct,
WF_ACTIVITY_ATTRIBUTES WfActAttr,
WF_ACTIVITY_ATTRIBUTES WfActAttr2,
ENG_CHANGE_ROUTE_STEPS RouteStep
WHERE WfActAttr.NAME (+)= 'DEFAULT_CHANGE_ROLE'
AND WfActAttr.ACTIVITY_VERSION (+)= WfAct.VERSION
AND WfActAttr.ACTIVITY_ITEM_TYPE (+)= WfAct.ITEM_TYPE
AND WfActAttr.ACTIVITY_NAME (+)= WfAct.NAME
AND WfActAttr2.NAME (+)= 'ACTIVITY_CONDITION_CODE'
AND WfActAttr2.ACTIVITY_VERSION (+)= WfAct.VERSION
AND WfActAttr2.ACTIVITY_ITEM_TYPE (+)= WfAct.ITEM_TYPE
AND WfActAttr2.ACTIVITY_NAME (+)= WfAct.NAME
AND WfAct.TYPE = 'PROCESS'
AND WfAct.BEGIN_DATE <= SYSDATE
AND (WfAct.END_DATE >= SYSDATE OR WfAct.END_DATE IS NULL)
AND WfAct.ITEM_TYPE = RouteStep.wf_item_type
AND WfAct.NAME = RouteStep.wf_process_name
AND RouteStep.step_id = p_step_id ;
SELECT document_role_attr.TEXT_DEFAULT DOCUMENT_ROLE
, doc_role.MENU_ID DOCUMENT_ROLE_ID
, cs_role_attr.TEXT_DEFAULT OCS_ROLE
FROM FND_MENUS doc_role
, WF_ACTIVITIES WfAct
, WF_ACTIVITY_ATTRIBUTES document_role_attr
, WF_ACTIVITY_ATTRIBUTES cs_role_attr
, ENG_CHANGE_ROUTE_STEPS RouteStep
WHERE doc_role.MENU_NAME (+)= document_role_attr.TEXT_DEFAULT
AND document_role_attr.NAME (+)= 'AUTO_GRANT_DOCUMENT_ROLE'
AND document_role_attr.ACTIVITY_VERSION (+)= WfAct.VERSION
AND document_role_attr.ACTIVITY_ITEM_TYPE (+)= WfAct.ITEM_TYPE
AND document_role_attr.ACTIVITY_NAME (+)= WfAct.NAME
AND cs_role_attr.NAME (+)= 'AUTO_GRANT_OCS_ROLE'
AND cs_role_attr.ACTIVITY_VERSION (+)= WfAct.VERSION
AND cs_role_attr.ACTIVITY_ITEM_TYPE (+)= WfAct.ITEM_TYPE
AND cs_role_attr.ACTIVITY_NAME (+)= WfAct.NAME
AND WfAct.TYPE = 'PROCESS'
AND WfAct.BEGIN_DATE <= SYSDATE
AND (WfAct.END_DATE >= SYSDATE OR WfAct.END_DATE IS NULL)
AND WfAct.ITEM_TYPE = RouteStep.wf_item_type
AND WfAct.NAME = RouteStep.wf_process_name
AND RouteStep.step_id = c_step_id ;
SELECT vote_option_attr.TEXT_DEFAULT VOTE_OPTION
FROM WF_ACTIVITIES WfAct
, WF_ACTIVITY_ATTRIBUTES vote_option_attr
, ENG_CHANGE_ROUTE_STEPS RouteStep
WHERE vote_option_attr.NAME (+)= 'VOTE_OPTION_FOR_STEP_ALL'
AND vote_option_attr.ACTIVITY_VERSION (+)= WfAct.VERSION
AND vote_option_attr.ACTIVITY_ITEM_TYPE (+)= WfAct.ITEM_TYPE
AND vote_option_attr.ACTIVITY_NAME (+)= WfAct.NAME
AND WfAct.TYPE = 'PROCESS'
AND WfAct.BEGIN_DATE <= SYSDATE
AND (WfAct.END_DATE >= SYSDATE OR WfAct.END_DATE IS NULL)
AND WfAct.ITEM_TYPE = RouteStep.wf_item_type
AND WfAct.NAME = RouteStep.wf_process_name
AND RouteStep.step_id = c_step_id ;
SELECT vote_option_attr.TEXT_DEFAULT VOTE_OPTION
FROM WF_ACTIVITIES WfAct
, WF_ACTIVITY_ATTRIBUTES vote_option_attr
, ENG_CHANGE_ROUTE_STEPS RouteStep
WHERE vote_option_attr.NAME (+)= 'DEFAULT_ASSIGNEE_RESP_CODE'
AND vote_option_attr.ACTIVITY_VERSION (+)= WfAct.VERSION
AND vote_option_attr.ACTIVITY_ITEM_TYPE (+)= WfAct.ITEM_TYPE
AND vote_option_attr.ACTIVITY_NAME (+)= WfAct.NAME
AND WfAct.TYPE = 'PROCESS'
AND WfAct.BEGIN_DATE <= SYSDATE
AND (WfAct.END_DATE >= SYSDATE OR WfAct.END_DATE IS NULL)
AND WfAct.ITEM_TYPE = RouteStep.wf_item_type
AND WfAct.NAME = RouteStep.wf_process_name
AND RouteStep.step_id = c_step_id ;
SELECT required_relative_days
FROM ENG_CHANGE_ROUTE_STEPS RouteStep
WHERE RouteStep.step_id = p_step_id ;
SELECT TempLCStatus.WF_SIG_POLICY
FROM ENG_LIFECYCLE_STATUSES TempLCStatus,
ENG_CHANGE_ROUTES Route,
ENG_ENGINEERING_CHANGES EngChange
WHERE TempLCStatus.ENTITY_NAME = 'ENG_CHANGE_TYPE'
AND TempLCStatus.ENTITY_ID1 = EngChange.CHANGE_ORDER_TYPE_ID
AND TO_CHAR(TempLCStatus.STATUS_CODE) = Route.CLASSIFICATION_CODE
AND EngChange.CHANGE_ID = Route.OBJECT_ID1
AND Route.OBJECT_NAME = 'ENG_CHANGE'
AND Route.ROUTE_ID = p_route_id ;
SELECT na.NAME
, na.TEXT_VALUE
, na.NUMBER_VALUE
, na.DATE_VALUE
FROM WF_NOTIFICATION_ATTRIBUTES na,
WF_MESSAGE_ATTRIBUTES ma,
WF_NOTIFICATIONS ntf
WHERE ntf.NOTIFICATION_ID = p_orig_ntf_id
AND na.NOTIFICATION_ID = ntf.NOTIFICATION_ID
AND ma.MESSAGE_NAME = ntf.MESSAGE_NAME
AND ma.MESSAGE_TYPE = ntf.MESSAGE_TYPE
AND ma.NAME = na.NAME
AND ma.SUBTYPE = 'RESPOND' ;
SELECT ChangeCategory.BASE_CHANGE_MGMT_TYPE_CODE
FROM ENG_ENGINEERING_CHANGES EngineeringChangeEO,
ENG_CHANGE_ORDER_TYPES ChangeCategory
WHERE ChangeCategory.type_classification = 'CATEGORY'
AND ChangeCategory.change_mgmt_type_code = EngineeringChangeEO.change_mgmt_type_code
AND EngineeringChangeEO.change_id = p_change_id ;
SELECT owner_role
INTO x_item_owner_role
FROM WF_ITEMS
WHERE item_type = p_item_type
AND item_key = p_item_key ;
SELECT ROUTE_TYPE_CODE
FROM ENG_CHANGE_ROUTES
WHERE ROUTE_ID = p_route_id ;
SELECT item.concatenated_segments item_name
FROM MTL_SYSTEM_ITEMS_KFV item
WHERE item.organization_id = p_organization_id
AND item.inventory_item_id = p_item_id ;
SELECT rev.revision item_revision
, rev.revision_label item_revision_label
FROM MTL_ITEM_REVISIONS rev
WHERE rev.organization_id = p_organization_id
AND rev.inventory_item_id = p_item_id
AND rev.revision_id = p_item_revision_id ;
SELECT TO_NUMBER(subject.pk2_value) organization_id
, TO_NUMBER(subject.pk1_value) item_id
, TO_NUMBER(subject.pk3_value) item_revision_id
FROM ENG_CHANGE_SUBJECTS subject
WHERE subject.pk1_value IS NOT NULL
AND subject.pk2_value IS NOT NULL
AND subject.entity_name = p_entity_name
AND subject.change_line_id IS NULL
AND subject.change_id = p_change_id ;
SELECT TO_NUMBER(subject.pk2_value) organization_id
, TO_NUMBER(subject.pk1_value) item_id
, TO_NUMBER(subject.pk3_value) item_revision_id
FROM ENG_CHANGE_SUBJECTS subject
WHERE subject.pk1_value IS NOT NULL
AND subject.pk2_value IS NOT NULL
AND subject.entity_name = p_entity_name
AND subject.change_line_id = p_change_line_id
AND subject.change_id = p_change_id ;
SELECT eec.change_notice ,
eec.organization_id ,
eec.change_name ,
eec.description ,
ecs.meaning change_status ,
eclf.status_name change_lc_phase,
-- eec.status_type ,
mlu.meaning approval_status ,
-- eec.approval_status_type ,
-- eec.approval_date ,
-- eec.approval_list_id ,
eec.priority_code priority ,
-- priority.description priority,
eec.reason_code reason,
-- reason.description reason ,
ecmt_tl.type_name change_managemtent_type ,
-- eec.change_mgmt_type_code ,
ecot.type_name change_order_type ,
-- eec.change_order_type_id ,
-- ecot.description change_order_type_description,
hou.name eco_department ,
-- eec.responsible_organization_id
eec.assignee_id ,
eec.status_type ,
eec.status_code ,
eclf.status_type phase_status_type
INTO x_change_notice
, x_organization_id
, x_change_name
, x_description
, x_change_status
, x_change_lc_phase
, x_approval_status
, x_priority
, x_reason
, x_change_managemtent_type
, x_change_order_type
, x_eco_department
, l_assignee_id
, l_change_status_type
, l_change_status_code
, l_phase_status_type
FROM ENG_CHANGE_ORDER_TYPES_TL ecot,
-- ENG_CHANGE_PRIORITIES priority,
-- ENG_CHANGE_REASONS reason,
MFG_LOOKUPS ecs,
ENG_CHANGE_STATUSES_VL eclf,
MFG_LOOKUPS mlu,
HR_ORGANIZATION_UNITS hou,
ENG_CHANGE_ORDER_TYPES ecmt,
ENG_CHANGE_ORDER_TYPES_TL ecmt_tl,
-- ENG_CHANGE_ORDER_TYPES_VL ecmt,
ENG_ENGINEERING_CHANGES eec
WHERE eec.change_order_type_id = ecot.change_order_type_id
AND ecot.language = userenv('LANG')
-- AND eec.priority_code = priority.eng_change_priority_code(+)
-- AND priority.organization_id(+)= -1
-- AND eec.reason_code = reason.eng_change_reason_code(+)
-- AND reason.organization_id(+) = -1
AND eec.responsible_organization_id = hou.organization_id(+)
AND ecs.lookup_code (+)= eec.status_type
AND ecs.lookup_type (+)= 'ECG_ECN_STATUS'
AND eclf.status_code = eec.status_code
AND mlu.lookup_code (+)= eec.approval_status_type
AND mlu.lookup_type (+)= 'ENG_ECN_APPROVAL_STATUS'
AND ecmt_tl.language = userenv('LANG')
AND ecmt_tl.change_order_type_id = ecmt.change_order_type_id
AND ecmt.type_classification = 'CATEGORY'
AND ecmt.change_mgmt_type_code = eec.change_mgmt_type_code
AND eec.change_id = p_change_id ;
SELECT eec.change_notice ,
eec.organization_id ,
eec.change_name ,
eec.description ,
ecs.status_name change_status ,
-- eec.status_type ,
mlu.meaning approval_status ,
-- eec.approval_status_type ,
-- eec.approval_date ,
-- eec.approval_list_id ,
eec.priority_code priority ,
-- priority.description priority,
eec.reason_code reason,
-- reason.description reason ,
ecmt.name change_managemtent_type ,
-- eec.change_mgmt_type_code ,
ecot.change_order_type change_order_type ,
-- eec.change_order_type_id ,
-- ecot.description change_order_type_description,
hou.name eco_department ,
-- eec.responsible_organization_id
eec.assignee_id
INTO x_change_notice
, x_organization_id
, x_change_name
, x_description
, x_change_status
, x_approval_status
, x_priority
, x_reason
, x_change_managemtent_type
, x_change_order_type
, x_eco_department
, l_assignee_id
FROM ENG_CHANGE_ORDER_TYPES ecot,
-- ENG_CHANGE_PRIORITIES priority,
-- ENG_CHANGE_REASONS reason,
ENG_CHANGE_STATUSES_TL ecs,
MFG_LOOKUPS mlu,
HR_ORGANIZATION_UNITS hou,
ENG_CHANGE_MGMT_TYPES_TL ecmt,
ENG_ENGINEERING_CHANGES eec
WHERE eec.change_order_type_id = ecot.change_order_type_id
-- AND eec.priority_code = priority.eng_change_priority_code(+)
-- AND priority.organization_id(+)= -1
-- AND eec.reason_code = reason.eng_change_reason_code(+)
-- AND reason.organization_id(+) = -1
AND eec.responsible_organization_id = hou.organization_id(+)
AND ecs.status_code = eec.status_type
AND ecs.language = userenv('LANG')
AND mlu.lookup_code (+)= eec.approval_status_type
AND mlu.lookup_type (+)= 'ENG_ECN_APPROVAL_STATUS'
AND ecmt.language = userenv('LANG')
AND ecmt.change_mgmt_type_code = eec.change_mgmt_type_code
AND eec.change_id = p_change_id ;
SELECT ecl.change_id ,
ecl.sequence_number ,
ecl.name ,
ecl.description ,
flu.meaning line_status ,
mlu.meaning line_approval_status ,
ecl.assignee_id,
dist_stat_flu.meaning dist_line_status ,
line_wf.route_type_code
INTO x_change_id
, x_line_sequence_number
, x_line_name
, x_line_description
, x_line_status
, x_line_approval_status
, l_assignee_id
, l_list_dist_status
, l_route_type_code
FROM FND_LOOKUPS dist_stat_flu,
FND_LOOKUPS flu,
MFG_LOOKUPS mlu,
ENG_CHANGE_ROUTES line_wf,
ENG_CHANGE_LINES_VL ecl
WHERE line_wf.route_id (+)= ecl.route_id
AND dist_stat_flu.lookup_code (+)= ecl.status_code
AND dist_stat_flu.lookup_type (+)= 'ENG_DIST_LINE_STATUSES'
AND flu.lookup_code (+)= ecl.status_code
AND flu.lookup_type (+)= 'ENG_CHANGE_LINE_STATUSES'
AND mlu.lookup_code (+)= ecl.approval_status_type
AND mlu.lookup_type (+)= 'ENG_ECN_APPROVAL_STATUS'
AND ecl.change_line_id = p_change_line_id ;
SELECT ecav.ACTION_TYPE
FROM ENG_CHANGE_ACTIONS ecav
WHERE ecav.action_id = p_action_id ;
SELECT ecav.action_id
-- ,ecav.ACTION_TYPE
, ecav.created_by created_by
, ecav.creation_date creation_date
, ecav.description description
, EgoPeople.person_name person_name
, EgoPeople.company_name company_name
FROM ENG_CHANGE_ACTIONS_VL ecav
, EGO_PEOPLE_V EgoPeople
WHERE EgoPeople.user_id = ecav.created_by
AND ecav.action_id = p_action_id ;
SELECT Step.step_seq_num
, TRUNC(Step.required_date) required_date
, ConditionTypeLookup.meaning condition_type
, Step.instruction
FROM FND_LOOKUPS ConditionTypeLookup ,
ENG_CHANGE_ROUTE_STEPS_VL Step
WHERE ConditionTypeLookup.lookup_code = Step.condition_type_code
AND ConditionTypeLookup.lookup_type = 'ENG_CHANGE_ROUTE_CONDITIONS'
AND Step.step_id = p_route_step_id ;
SELECT meaning
INTO l_approval_status
FROM MFG_LOOKUPS
WHERE lookup_code = p_new_appr_status_type
AND lookup_type = 'ENG_ECN_APPROVAL_STATUS' ;
UPDATE eng_engineering_changes
SET approval_status_type = p_new_appr_status_type ,
approval_date = sysdate ,
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id ;
UPDATE eng_revised_items
SET status_type = 4 , -- Set Rev Item Status: Scheduled
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id
AND status_type = 1; -- Rev Item Status: Open
UPDATE eng_engineering_changes
SET status_type = 4 , -- Scheduled
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id
AND status_type = 1; -- Open
UPDATE eng_engineering_changes
SET approval_status_type = p_new_appr_status_type,
approval_date = NULL ,
approval_request_date = DECODE(p_new_appr_status_type
, Eng_Workflow_Util.G_REQUESTED
, sysdate
, approval_request_date) ,
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_id = p_change_id ;
UPDATE eng_change_lines
SET approval_status_type = p_new_appr_status_type ,
approval_date = sysdate ,
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_line_id = p_change_line_id ;
UPDATE eng_change_lines
SET approval_status_type = p_new_appr_status_type,
approval_date = NULL ,
approval_request_date = DECODE(p_new_appr_status_type
, Eng_Workflow_Util.G_REQUESTED
, sysdate
, approval_request_date) ,
request_id = l_request_id ,
last_update_date = SYSDATE ,
last_updated_by = l_user_id ,
last_update_login = l_login_id
WHERE change_line_id = p_change_line_id ;
SELECT ecl.change_line_id
FROM ENG_CHANGE_LINES ecl
-- , ENG_CHANGE_ROUTES ecr
WHERE ( ecl.approval_status_type <> Eng_Workflow_Util.G_APPROVED
AND ecl.approval_status_type <> Eng_Workflow_Util.G_REQUESTED )
-- AND ( ecl.status_code <> Eng_Workflow_Util.G_CL_COMPLETED
-- AND ecl.status_code <> Eng_Workflow_Util.G_CL_CANCELLED )
AND ecl.sequence_number <> -1
AND ecl.change_type_id <> -1
-- AND ecl.parent_line_id IS NULL
-- AND ecr.status_code = Eng_Workflow_Util.G_RT_NOT_STARTED
-- AND ecl.route_id = ecr.route_id
AND ecl.change_id = p_change_id ;
update ENG_CHANGE_ROUTES set
OWNER_ID = DECODE(l_set_start_date_flag, 1, p_wf_user_id, OWNER_ID) ,
WF_ITEM_TYPE = p_item_type ,
WF_ITEM_KEY = p_item_key,
STATUS_CODE = p_new_status_code ,
ROUTE_START_DATE = DECODE(l_set_start_date_flag, 1, SYSDATE, ROUTE_START_DATE),
ROUTE_END_DATE = DECODE(l_set_end_date_flag, 1, SYSDATE, ROUTE_END_DATE),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_wf_user_id,
LAST_UPDATE_LOGIN = '',
CHANGE_REVISION = DECODE(l_set_rev, 1, l_change_revision, CHANGE_REVISION)
where ROUTE_ID = p_route_id ;
update ENG_CHANGE_LINES set
STATUS_CODE = l_dist_line_status_code ,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_wf_user_id,
LAST_UPDATE_LOGIN = ''
where ROUTE_ID = p_route_id
and CHANGE_LINE_ID = p_change_line_id;
update ENG_CHANGE_ROUTE_STEPS set
WF_ITEM_TYPE = p_item_type ,
WF_ITEM_KEY = p_item_key,
STEP_STATUS_CODE = p_new_status_code,
STEP_START_DATE = DECODE(l_set_start_date_flag, 1, SYSDATE,STEP_START_DATE),
STEP_END_DATE = DECODE(l_set_end_date_flag, 1, SYSDATE,STEP_END_DATE),
REQUIRED_DATE = DECODE(l_set_start_date_flag, 1, l_required_date, REQUIRED_DATE) ,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_wf_user_id,
LAST_UPDATE_LOGIN = ''
where STEP_ID = p_route_step_id ;
update ENG_CHANGE_ROUTE_PEOPLE set
response_code = l_default_assignee_resp,
last_update_date = SYSDATE ,
last_updated_by = p_wf_user_id,
last_update_login = ''
where step_id = p_route_step_id
and assignee_id <> -1
and response_code IS NULL ;
update ENG_CHANGE_ROUTE_PEOPLE set
response_code = '',
last_update_date = SYSDATE ,
last_updated_by = p_wf_user_id,
last_update_login = ''
where step_id = p_route_step_id
and assignee_id <> -1
and ( response_code = Eng_Workflow_Util.G_RT_SUBMITTED
OR response_code = Eng_Workflow_Util.G_RT_NOT_RECEIVED ) ;
SELECT step_status_code
FROM ENG_CHANGE_ROUTE_STEPS
WHERE step_id = p_route_step_id ;
SELECT step_seq_num
, required_date
FROM ENG_CHANGE_ROUTE_STEPS
WHERE route_id = p_route_id
AND required_date IS NOT NULL
ORDER BY step_seq_num ;
SELECT 'Can not be started'
FROM ENG_CHANGE_ROUTES
WHERE route_id = p_route_id
AND status_code <> Eng_Workflow_Util.G_RT_NOT_STARTED ;
SELECT 'Can not be started'
FROM ENG_CHANGE_ROUTES
WHERE route_id = p_route_id
AND template_flag <> Eng_Workflow_Util.G_RT_INSTANCE ;
SELECT step.step_seq_num
FROM ENG_CHANGE_ROUTE_STEPS step
WHERE EXISTS (SELECT 'Invalid Assignee Exists'
FROM ENG_CHANGE_ROUTE_PEOPLE people
WHERE people.assignee_id = -1
AND people.assignee_type_code = Eng_Workflow_Util.G_PERSON
AND people.step_id = step.step_id )
AND step.route_id = p_route_id ;
SELECT steps.STEP_SEQ_NUM ,steps.step_id
FROM ENG_CHANGE_ROUTE_STEPS steps ,
ENG_CHANGE_ROUTE_PEOPLE PEOPLE
WHERE CONDITION_TYPE_CODE = 'PEOPLE'
AND STEPS.STEP_ID = PEOPLE.STEP_ID
AND PEOPLE.RESPONSE_CONDITION_CODE = 'MANDATORY'
AND PEOPLE.ASSIGNEE_ID=-1
AND STEPS.route_id=p_route_id;
SELECT change_line_id
FROM ENG_CHANGE_LINES
WHERE change_id = p_change_id
AND change_type_id <> -1 -- excluding change task
AND sequence_number <> -1 ;
PROCEDURE DeleteAdhocRolesAndUsers
( x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_item_type IN VARCHAR2
, p_item_key IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DeleteAdhocRolesAndUsers';
DeleteRoleAndUsers(p_role_name => l_role_names(i) ) ;
END DeleteAdhocRolesAndUsers ;
SELECT fu.user_id
FROM FND_USER fu
, WF_NOTIFICATIONS wn
WHERE fu.user_name = wn.recipient_role
AND wn.notification_id = p_notification_id ;
SELECT STATUS_CODE
, CLASSIFICATION_CODE
, OBJECT_NAME
, OBJECT_ID1
FROM ENG_CHANGE_ROUTES
WHERE ROUTE_ID = p_route_id
AND TEMPLATE_FLAG = Eng_Workflow_Util.G_RT_INSTANCE ;
SELECT STATUS_CODE
, CLASSIFICATION_CODE
, OBJECT_NAME
, OBJECT_ID1
FROM ENG_CHANGE_ROUTES
WHERE ROUTE_ID = p_route_id
AND TEMPLATE_FLAG = Eng_Workflow_Util.G_RT_INSTANCE ;
ENG_CHANGE_LIFECYCLE_UTIL.Update_Lifecycle_States
( p_api_version => 1.0
, p_commit => FND_API.g_FALSE
, p_init_msg_list => FND_API.g_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_debug => FND_API.G_FALSE
, p_output_dir => NULL -- '/appslog/bis_top/utl/plm115dv/log'
, p_debug_filename => NULL -- 'UpdateLCStatesFromWF.log' || to_char(p_route_id)
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_change_id => l_change_id
, p_api_caller => p_api_caller
, p_wf_route_id => p_route_id
, p_status_code => l_status_code
, p_route_status => l_route_status
);
SELECT wi.item_type
, wi.item_key
FROM ENG_ENGINEERING_CHANGES eec
, ENG_CHANGE_ROUTES ecr
, WF_ITEMS wi
WHERE wi.end_date IS NULL
AND wi.item_key = ecr.wf_item_key
AND wi.item_type = ecr.wf_item_type
AND ecr.status_code = Eng_Workflow_Util.G_RT_IN_PROGRESS
AND ecr.route_id = eec.route_id
AND eec.approval_status_type = Eng_Workflow_Util.G_REQUESTED
AND eec.change_id = p_change_id ;
SELECT wi.item_type
, wi.item_key
FROM ENG_CHANGE_LINES ecl
, ENG_CHANGE_ROUTES ecr
, WF_ITEMS wi
WHERE wi.end_date IS NULL
AND wi.item_key = ecr.wf_item_key
AND wi.item_type = ecr.wf_item_type
AND ecr.status_code = Eng_Workflow_Util.G_RT_IN_PROGRESS
AND ecr.route_id = ecl.route_id
-- AND ecl.parent_line_id IS NULL
AND ecl.approval_status_type = Eng_Workflow_Util.G_REQUESTED
AND ecl.sequence_number <> -1
AND ecl.change_type_id <> -1
AND ecl.change_id = p_change_id ;
SELECT EngSecPeople.user_id
, EngSecPeople.person_id
, RoutePeople.route_people_id
, RoutePeople.adhoc_people_flag
, RoutePeople.assignee_type_code
, RoutePeople.original_assignee_id
, RoutePeople.original_assignee_type_code
, RoutePeople.response_condition_code
FROM ENG_CHANGE_ROUTE_PEOPLE RoutePeople
, ENG_SECURITY_PEOPLE_V EngSecPeople
, WF_NOTIFICATIONS wn
WHERE RoutePeople.assignee_id = EngSecPeople.person_id
-- AND RoutePeople.assignee_type_code = p_assignee_type_code
AND RoutePeople.step_id = p_step_id
AND EngSecPeople.user_name = wn.recipient_role
AND wn.notification_id = p_notification_id ;
SELECT ntf.NOTIFICATION_ID,
ntf.RECIPIENT_ROLE,
ntf.MESSAGE_NAME,
ntf.message_type
FROM WF_ITEM_ACTIVITY_STATUSES wias,
WF_NOTIFICATIONS ntf ,
WF_NOTIFICATIONS orig_ntf
WHERE ntf.STATUS = 'OPEN'
AND wias.NOTIFICATION_ID = ntf.group_id
AND wias.NOTIFICATION_ID IS NOT NULL
AND (wias.ACTIVITY_STATUS = 'NOTIFIED' OR wias.ACTIVITY_STATUS = 'ERROR')
AND wias.ITEM_TYPE = p_item_type
AND wias.ITEM_KEY = p_item_key
AND ntf.RECIPIENT_ROLE = orig_ntf.RECIPIENT_ROLE
AND ntf.GROUP_ID = orig_ntf.GROUP_ID
AND ntf.NOTIFICATION_ID <> orig_ntf.NOTIFICATION_ID
AND orig_ntf.NOTIFICATION_ID = p_orig_ntf_id
AND EXISTS (SELECT 1
FROM WF_NOTIFICATION_ATTRIBUTES na,
WF_MESSAGE_ATTRIBUTES ma
WHERE na.NOTIFICATION_ID = ntf.NOTIFICATION_ID
AND ma.MESSAGE_NAME = ntf.MESSAGE_NAME
AND ma.MESSAGE_TYPE = ntf.MESSAGE_TYPE
AND ma.NAME = na.NAME
AND ma.SUBTYPE = 'RESPOND') ;
* if we use UPDATE_ROW api, the response is only updated with
* the lang record used while responding to ntf
IF g_debug_flag THEN
Write_Debug('Calling Eng_Change_Route_People_Util.UPDATE_ROW - Route People Id: ' || l_route_people_id ) ;
Eng_Change_Route_People_Util.UPDATE_ROW
( X_ROUTE_PEOPLE_ID => l_route_people_id ,
X_REQUEST_ID => null ,
X_ORIGINAL_SYSTEM_REFERENCE => null ,
X_ASSIGNEE_ID => l_performer_party_id ,
X_RESPONSE_DATE => SYSDATE ,
X_STEP_ID => l_step_id,
X_ASSIGNEE_TYPE_CODE => l_assignee_type_code,
X_ADHOC_PEOPLE_FLAG => l_adhoc_people_flag,
X_WF_NOTIFICATION_ID => p_notification_id,
X_RESPONSE_CODE => p_response_code,
X_RESPONSE_DESCRIPTION => l_comment ,
X_LAST_UPDATE_DATE => SYSDATE ,
X_LAST_UPDATED_BY => l_performer_user_id ,
X_LAST_UPDATE_LOGIN => null,
X_PROGRAM_ID => null,
X_PROGRAM_APPLICATION_ID => null ,
X_PROGRAM_UPDATE_DATE => null ,
X_ORIGINAL_ASSIGNEE_ID => l_original_assignee_id ,
X_ORIGINAL_ASSIGNEE_TYPE_CODE => l_original_assignee_type_code ,
X_RESPONSE_CONDITION_CODE => l_response_condition_code
) ;
update ENG_CHANGE_ROUTE_PEOPLE set
WF_NOTIFICATION_ID = p_notification_id,
RESPONSE_CODE = p_response_code ,
RESPONSE_DATE = SYSDATE ,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = l_performer_user_id ,
LAST_UPDATE_LOGIN = null
where ROUTE_PEOPLE_ID = l_route_people_id ;
update ENG_CHANGE_ROUTE_PEOPLE_TL set
RESPONSE_DESCRIPTION = l_comment,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = l_performer_user_id ,
LAST_UPDATE_LOGIN = null ,
SOURCE_LANG = userenv('LANG')
where ROUTE_PEOPLE_ID = l_route_people_id ;
SELECT step_id,
step_seq_num,
wf_item_type,
wf_process_name
FROM ENG_CHANGE_ROUTE_STEPS
WHERE route_id = p_route_id
AND step_status_code = Eng_Workflow_Util.G_RT_NOT_STARTED
AND step_start_date IS NULL
AND step_end_date IS NULL
ORDER BY 2 ASC ;
SELECT wi.item_type wf_item_type
, wi.item_key wf_item_key
, wi.root_activity wf_process_name
FROM WF_ITEMS wi
WHERE wi.parent_item_type = p_route_item_type
AND wi.parent_item_key = p_route_item_key
AND wi.item_type IN ( Eng_Workflow_Util.G_CHANGE_ROUTE_STEP_ITEM_TYPE
, Eng_Workflow_Util.G_CHANGE_ROUTE_DOC_STEP_TYPE
, Eng_Workflow_Util.G_CHANGE_ROUTE_LINE_STEP_TYPE )
AND wi.end_date IS NULL ;
SELECT ecl.change_line_id
, ecl.route_id
, ecr.wf_item_type
, ecr.wf_process_name
FROM ENG_CHANGE_LINES ecl
, ENG_CHANGE_ROUTES ecr
WHERE ( ecl.status_code <> Eng_Workflow_Util.G_CL_COMPLETED
AND ecl.status_code <> Eng_Workflow_Util.G_CL_CANCELLED )
AND ( ecl.approval_status_type <> Eng_Workflow_Util.G_APPROVED
AND ecl.approval_status_type <> Eng_Workflow_Util.G_REQUESTED )
AND ecl.sequence_number <> -1
-- AND ecl.parent_line_id IS NULL
-- AND ecr.status_code = Eng_Workflow_Util.G_RT_NOT_STARTED
AND ecl.change_type_id <> -1
AND ecl.route_id = ecr.route_id
AND ecl.change_id = p_change_id ;
SELECT 'Non Approved Line Exists'
FROM DUAL
WHERE EXISTS ( SELECT null
FROM ENG_CHANGE_LINES ecl
WHERE ( ecl.status_code <> Eng_Workflow_Util.G_CL_COMPLETED
AND ecl.status_code <> Eng_Workflow_Util.G_CL_CANCELLED )
AND ecl.approval_status_type <> Eng_Workflow_Util.G_APPROVED
AND ecl.change_type_id <> -1
AND ecl.sequence_number <> -1
-- AND ecl.parent_line_id IS NULL
AND ecl.route_id IS NOT NULL
AND ecl.change_id = p_change_id
) ;
SELECT web_html_call
FROM FND_FORM_FUNCTIONS
WHERE function_name = p_function_name ;
SELECT RoutePeople.assignee_id
, RoutePeople.adhoc_people_flag
FROM ENG_CHANGE_ROUTE_PEOPLE RoutePeople
WHERE RoutePeople.assignee_type_code = p_assignee_type_code
AND RoutePeople.assignee_id <> -1
AND RoutePeople.step_id = p_step_id ;
select wfl.lookup_code result_code
from wf_lookups wfl,
wf_activities wfa,
wf_process_activities wfpa,
wf_items wfi
where wfl.lookup_type = wfa.result_type
and wfa.name = wfpa.activity_name
and wfi.begin_date >= wfa.begin_date
and wfi.begin_date < nvl(wfa.end_date,wfi.begin_date+1)
and wfpa.activity_item_type = wfa.item_type
and wfpa.instance_id = actid
and wfi.item_key = itemkey
and wfi.item_type = itemtype;
SELECT COUNT(*)
INTO l_total_pop
FROM ENG_CHANGE_ROUTE_PEOPLE RoutePeople
, EGO_USER_V UserV
, WF_NOTIFICATIONS wn
WHERE RoutePeople.assignee_id = UserV.party_id
AND RoutePeople.response_condition_code = 'MANDATORY'
AND RoutePeople.step_id = StepId
AND UserV.user_name = wn.recipient_role
AND wn.group_id = Gid;
SELECT COUNT(*)
INTO l_total_voted
FROM ENG_CHANGE_ROUTE_PEOPLE RoutePeople
, EGO_USER_V UserV
, WF_NOTIFICATIONS wn
WHERE RoutePeople.assignee_id = UserV.party_id
AND RoutePeople.response_condition_code = 'MANDATORY'
AND RoutePeople.step_id = StepId
AND UserV.user_name = wn.recipient_role
AND wn.status = 'CLOSED'
AND wn.group_id = Gid;
SELECT COUNT(*)
INTO l_code_count
FROM ENG_CHANGE_ROUTE_PEOPLE RoutePeople
, EGO_USER_V UserV
, WF_NOTIFICATION_ATTRIBUTES wfna
, WF_NOTIFICATIONS wn
WHERE RoutePeople.assignee_id = UserV.party_id
AND RoutePeople.response_condition_code = 'MANDATORY'
AND RoutePeople.step_id = StepId
AND UserV.user_name = wn.recipient_role
AND wfna.name = 'RESULT'
AND wfna.text_value = ResultCode
AND wn.notification_id = wfna.notification_id
AND wn.status = 'CLOSED'
AND wn.group_id = Gid;
select wfl.lookup_code result_code
from wf_lookups wfl,
wf_activities wfa,
wf_process_activities wfpa,
wf_items wfi
where wfl.lookup_type = wfa.result_type
and wfa.name = wfpa.activity_name
and wfi.begin_date >= wfa.begin_date
and wfi.begin_date < nvl(wfa.end_date,wfi.begin_date+1)
and wfpa.activity_item_type = wfa.item_type
and wfpa.instance_id = actid
and wfi.item_key = itemkey
and wfi.item_type = itemtype;
SELECT ecr.wf_item_type parent_item_type
, ecr.wf_item_key parent_item_key
FROM ENG_CHANGE_ROUTES ecr
, ENG_ENGINEERING_CHANGES eec
, WF_ITEMS wi
WHERE wi.item_type = ecr.wf_item_type
AND wi.item_key = ecr.wf_item_key
AND wi.end_date IS NULL
AND ecr.route_id = eec.route_id
AND eec.change_id = p_change_id ;
SELECT item_key
INTO dummy
FROM wf_items
WHERE item_type = l_parent_itemtype
AND item_key = l_parent_itemkey
FOR UPDATE ;
SELECT UserV.user_id user_id
FROM EGO_USER_V UserV
, WF_NOTIFICATIONS wf
WHERE UserV.user_name = wf.recipient_role
AND wf.notification_id = p_ntf_id ;
SELECT UserV.party_id person_id
FROM EGO_USER_V UserV
WHERE UserV.user_id = p_user_id ;
SELECT UserV.user_id user_id
FROM EGO_USER_V UserV
, WF_NOTIFICATIONS wf
WHERE UserV.user_name = wf.recipient_role
AND wf.notification_id = p_ntf_id ;
SELECT UserV.party_id person_id
FROM EGO_USER_V UserV
WHERE UserV.user_id = p_user_id ;
SELECT ntf.NOTIFICATION_ID,
ntf.RECIPIENT_ROLE,
ntf.MESSAGE_NAME,
ntf.message_type
FROM WF_ITEM_ACTIVITY_STATUSES wias,
WF_NOTIFICATIONS ntf
WHERE ntf.STATUS = 'OPEN'
AND wias.NOTIFICATION_ID = ntf.group_id
AND wias.NOTIFICATION_ID IS NOT NULL
AND (wias.ACTIVITY_STATUS = 'NOTIFIED' OR wias.ACTIVITY_STATUS = 'ERROR')
AND wias.ITEM_TYPE = p_item_type
AND wias.ITEM_KEY = p_item_key
AND ntf.RECIPIENT_ROLE = p_responder
AND EXISTS (SELECT 1
FROM WF_NOTIFICATION_ATTRIBUTES na,
WF_MESSAGE_ATTRIBUTES ma
WHERE na.NOTIFICATION_ID = ntf.NOTIFICATION_ID
AND ma.MESSAGE_NAME = ntf.MESSAGE_NAME
AND ma.MESSAGE_TYPE = ntf.MESSAGE_TYPE
AND ma.NAME = na.NAME
AND ma.SUBTYPE = 'RESPOND') ;
UPDATE eng_revised_items
SET mrp_active = p_mrp_flag
, last_update_date = SYSDATE
, last_updated_by = l_fnd_user_id
, last_update_login = l_fnd_login_id
WHERE change_id = p_change_id
AND status_type in (0, 1, 4, 7, 8) ;
SELECT EngSecPeople.user_id
, EngSecPeople.person_id
, RoutePeople.route_people_id
, RoutePeople.adhoc_people_flag
, RoutePeople.assignee_type_code
, RoutePeople.original_assignee_id
, RoutePeople.original_assignee_type_code
, RoutePeople.response_condition_code
FROM ENG_CHANGE_ROUTE_PEOPLE RoutePeople
, ENG_SECURITY_PEOPLE_V EngSecPeople
, WF_NOTIFICATIONS wn
WHERE RoutePeople.assignee_id = EngSecPeople.person_id
-- AND RoutePeople.assignee_type_code = p_assignee_type_code
AND RoutePeople.step_id = p_step_id
AND ( RoutePeople.response_code IS NULL
OR RoutePeople.response_code = Eng_Workflow_Util.G_RT_SUBMITTED
OR RoutePeople.response_code = Eng_Workflow_Util.G_RT_NOT_RECEIVED )
AND EngSecPeople.user_name = wn.recipient_role
AND wn.notification_id = p_notification_id ;
SELECT ASSOC_OBJECT_NAME,ASSOC_OBJ_PK1_VALUE, ADHOC_ASSOC_FLAG,
OBJECT_NAME, OBJECT_ID1 from ENG_CHANGE_ROUTE_ASSOCS
where ROUTE_PEOPLE_ID=p_route_people_id;
SELECT person_id into l_new_party_id
FROM ENG_SECURITY_PEOPLE_V
WHERE user_name = WF_ENGINE.context_new_role;
update ENG_CHANGE_ROUTE_PEOPLE set
WF_NOTIFICATION_ID = p_notification_id,
RESPONSE_CODE = l_response_code ,
RESPONSE_DATE = SYSDATE ,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = l_performer_user_id ,
LAST_UPDATE_LOGIN = null
where ROUTE_PEOPLE_ID = l_route_people_id ;
update ENG_CHANGE_ROUTE_PEOPLE_TL set
RESPONSE_DESCRIPTION = l_comment,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = l_performer_user_id ,
LAST_UPDATE_LOGIN = null ,
SOURCE_LANG = userenv('LANG')
where ROUTE_PEOPLE_ID = l_route_people_id ;
select ENG_CHANGE_ROUTE_PEOPLE_S.nextval into l_new_route_people_id from dual;
Eng_Change_Route_People_Util.INSERT_ROW
(
X_ROWID => l_rowid,
X_ROUTE_PEOPLE_ID => l_new_route_people_id ,
X_STEP_ID => l_step_id ,
X_ASSIGNEE_ID => l_new_party_id,
X_ASSIGNEE_TYPE_CODE => G_PERSON,
X_ADHOC_PEOPLE_FLAG => 'Y',
X_WF_NOTIFICATION_ID => l_notification_id ,
X_RESPONSE_CODE => l_default_assignee_resp ,
X_RESPONSE_DATE => TO_DATE(NULL),
X_REQUEST_ID => NULL,
X_ORIGINAL_SYSTEM_REFERENCE => NULL,
X_RESPONSE_DESCRIPTION => NULL,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => l_performer_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => l_performer_user_id,
X_LAST_UPDATE_LOGIN => null,
X_PROGRAM_ID => null,
X_PROGRAM_APPLICATION_ID => null,
X_PROGRAM_UPDATE_DATE => null,
X_ORIGINAL_ASSIGNEE_ID => l_original_assignee_id,
X_ORIGINAL_ASSIGNEE_TYPE_CODE => l_original_assignee_type_code,
X_RESPONSE_CONDITION_CODE => l_response_condition_code,
X_PARENT_ROUTE_PEOPLE_ID => l_route_people_id
) ;
SELECT ENG_CHANGE_ROUTE_ASSOCS_S.nextval into l_new_route_assoc_id from DUAL;
insert into ENG_CHANGE_ROUTE_ASSOCS
(
ROUTE_ASSOCIATION_ID ,
ROUTE_PEOPLE_ID,
ASSOC_OBJECT_NAME,
ASSOC_OBJ_PK1_VALUE,
ADHOC_ASSOC_FLAG,
OBJECT_NAME,
OBJECT_ID1,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
(
l_new_route_assoc_id,
l_new_route_people_id,
assoc_rec.ASSOC_OBJECT_NAME,
assoc_rec.ASSOC_OBJ_PK1_VALUE,
'Y',
assoc_rec.OBJECT_NAME,
assoc_rec.OBJECT_ID1,
SYSDATE,
l_performer_user_id,
SYSDATE,
l_performer_user_id,
l_performer_user_id
);
SELECT RoutePeople.STEP_ID
, RoutePeople.ASSIGNEE_ID
, RoutePeople.ASSIGNEE_TYPE_CODE
, RoutePeople.ADHOC_PEOPLE_FLAG
, RoutePeople.ORIGINAL_ASSIGNEE_ID
, RoutePeople.ORIGINAL_ASSIGNEE_TYPE_CODE
, RoutePeople.RESPONSE_CONDITION_CODE
, TO_CHAR(NULL) ORIG_ROLE_OBJECT_NAME
, TO_NUMBER(NULL) ORIG_ROLE_OBJECT_ID
FROM ENG_CHANGE_ROUTE_PEOPLE RoutePeople
WHERE RoutePeople.ASSIGNEE_TYPE_CODE = 'PERSON'
AND RoutePeople.ASSIGNEE_ID <> -1
AND RoutePeople.ORIGINAL_ASSIGNEE_TYPE_CODE <> 'ROLE'
AND ( RoutePeople.RESPONSE_CODE IS NULL
OR RoutePeople.RESPONSE_CODE = G_RT_NOT_RECEIVED
OR RoutePeople.RESPONSE_CODE = G_RT_SUBMITTED
)
AND RoutePeople.STEP_ID = c_step_id
UNION ALL
SELECT RoutePeople.STEP_ID
, RoutePeople.ASSIGNEE_ID
, RoutePeople.ASSIGNEE_TYPE_CODE
, RoutePeople.ADHOC_PEOPLE_FLAG
, RoutePeople.ORIGINAL_ASSIGNEE_ID
, RoutePeople.ORIGINAL_ASSIGNEE_TYPE_CODE
, RoutePeople.RESPONSE_CONDITION_CODE
, fnd_obj.OBJ_NAME ORIG_ROLE_OBJECT_NAME
, fnd_obj.OBJECT_ID ORIG_ROLE_OBJECT_ID
FROM FND_FORM_FUNCTIONS fnd_func
, FND_MENU_ENTRIES fnd_menu
, FND_OBJECTS fnd_obj
, ENG_CHANGE_ROUTE_PEOPLE RoutePeople
WHERE fnd_obj.OBJECT_ID = fnd_func.OBJECT_ID
AND fnd_func.FUNCTION_ID = fnd_menu.FUNCTION_ID
AND fnd_menu.MENU_ID = ORIGINAL_ASSIGNEE_ID
AND ( RoutePeople.RESPONSE_CODE IS NULL
OR RoutePeople.RESPONSE_CODE = G_RT_NOT_RECEIVED
OR RoutePeople.RESPONSE_CODE = G_RT_SUBMITTED
)
AND RoutePeople.ASSIGNEE_TYPE_CODE = 'PERSON'
AND RoutePeople.ASSIGNEE_ID <> -1
AND RoutePeople.ORIGINAL_ASSIGNEE_TYPE_CODE = 'ROLE'
AND RoutePeople.STEP_ID = c_step_id ;
SELECT ChangeSubj.ENTITY_NAME CHANGE_OBJECT_NAME
, ChangeSubj.PK1_VALUE
, ChangeSubj.PK2_VALUE
, ChangeSubj.PK3_VALUE
, ChangeSubj.PK4_VALUE
, ChangeSubj.PK5_VALUE
, ChangeSubj.CHANGE_ID
, ChangeSubj.CHANGE_LINE_ID
FROM ENG_CHANGE_SUBJECTS ChangeSubj
, FND_OBJECTS FndObj
WHERE ChangeSubj.ENTITY_NAME = FndObj.OBJ_NAME
AND ChangeSubj.CHANGE_ID = c_change_id
AND ( ChangeSubj.CHANGE_LINE_ID = c_change_line_id
OR (ChangeSubj.CHANGE_LINE_ID IS NULL
AND c_change_line_id = -1)
)
AND FndObj.OBJ_NAME = G_DOM_DOCUMENT_REVISION ; -- R12B We only support G_DOM_DOCUMENT_REVISION
SELECT routes.OBJECT_ID1 CHANGE_ID
, TO_NUMBER(NULL) CHANGE_LINE_ID
, routes.ROUTE_ID
FROM ENG_CHANGE_ROUTES routes
WHERE routes.STATUS_CODE IN ( Eng_Workflow_Util.G_RT_REJECTED
, Eng_Workflow_Util.G_RT_APPROVED
, Eng_Workflow_Util.G_RT_COMPLETED
, Eng_Workflow_Util.G_RT_TIME_OUT) -- G_RT_ABORTED was taken care by ABORT WF
AND ( routes.TEMPLATE_FLAG = G_RT_INSTANCE
OR routes.TEMPLATE_FLAG = G_RT_HISTORY)
AND routes.OBJECT_NAME = G_ENG_CHANGE
AND routes.OBJECT_ID1 = c_change_id ;
SELECT chg_line.CHANGE_ID
, chg_line.CHANGE_LINE_ID
, line_routes.ROUTE_ID
FROM ENG_CHANGE_ROUTES line_routes
, ENG_CHANGE_LINES chg_line
WHERE line_routes.STATUS_CODE IN ( Eng_Workflow_Util.G_RT_REJECTED
, Eng_Workflow_Util.G_RT_APPROVED
, Eng_Workflow_Util.G_RT_COMPLETED
, Eng_Workflow_Util.G_RT_TIME_OUT ) -- G_RT_ABORTED was taken care by ABORT WF
AND ( line_routes.TEMPLATE_FLAG = G_RT_INSTANCE
OR line_routes.TEMPLATE_FLAG = G_RT_HISTORY)
AND line_routes.object_id1 = chg_line.CHANGE_LINE_ID
AND line_routes.OBJECT_NAME = G_ENG_CHANGE_LINE
AND ( chg_line.CHANGE_LINE_ID = c_change_line_id OR c_change_line_id = -1)
AND chg_line.CHANGE_ID = c_change_id ;
SELECT RouteStep.STEP_ID
FROM ENG_CHANGE_ROUTE_STEPS RouteStep
WHERE ( RouteStep.STEP_STATUS_CODE <> Eng_Workflow_Util.G_RT_NOT_STARTED
AND RouteStep.STEP_STATUS_CODE <> Eng_Workflow_Util.G_RT_IN_PROGRESS)
AND RouteStep.ROUTE_ID = c_route_id ;
SELECT RouteStep.ROUTE_ID
, RoutePeople.STEP_ID
, RoutePeople.ASSIGNEE_ID
, RoutePeople.ASSIGNEE_TYPE_CODE
, RoutePeople.ADHOC_PEOPLE_FLAG
, RoutePeople.ORIGINAL_ASSIGNEE_ID
, RoutePeople.ORIGINAL_ASSIGNEE_TYPE_CODE
, RoutePeople.RESPONSE_CONDITION_CODE
, TO_CHAR(NULL) ORIG_ROLE_OBJECT_NAME
, TO_NUMBER(NULL) ORIG_ROLE_OBJECT_ID
FROM ENG_CHANGE_ROUTE_PEOPLE RoutePeople
, ENG_CHANGE_ROUTE_STEPS RouteStep
WHERE RoutePeople.ASSIGNEE_TYPE_CODE = 'PERSON'
AND RoutePeople.ASSIGNEE_ID <> -1
AND RoutePeople.ORIGINAL_ASSIGNEE_TYPE_CODE <> 'ROLE'
AND RoutePeople.STEP_ID = RouteStep.STEP_ID
AND ( RouteStep.STEP_STATUS_CODE <> Eng_Workflow_Util.G_RT_NOT_STARTED
AND RouteStep.STEP_STATUS_CODE <> Eng_Workflow_Util.G_RT_IN_PROGRESS)
AND RouteStep.STEP_ID = c_step_id
UNION
SELECT RouteStep.ROUTE_ID
, RoutePeople.STEP_ID
, RoutePeople.ASSIGNEE_ID
, RoutePeople.ASSIGNEE_TYPE_CODE
, RoutePeople.ADHOC_PEOPLE_FLAG
, RoutePeople.ORIGINAL_ASSIGNEE_ID
, RoutePeople.ORIGINAL_ASSIGNEE_TYPE_CODE
, RoutePeople.RESPONSE_CONDITION_CODE
, fnd_obj.OBJ_NAME ORIG_ROLE_OBJECT_NAME
, fnd_obj.OBJECT_ID ORIG_ROLE_OBJECT_ID
FROM FND_FORM_FUNCTIONS fnd_func
, FND_MENU_ENTRIES fnd_menu
, FND_OBJECTS fnd_obj
, ENG_CHANGE_ROUTE_PEOPLE RoutePeople
, ENG_CHANGE_ROUTE_STEPS RouteStep
WHERE fnd_obj.OBJECT_ID = fnd_func.OBJECT_ID
AND fnd_func.FUNCTION_ID = fnd_menu.FUNCTION_ID
AND fnd_menu.MENU_ID = ORIGINAL_ASSIGNEE_ID
AND RoutePeople.ASSIGNEE_TYPE_CODE = 'PERSON'
AND RoutePeople.ASSIGNEE_ID <> -1
AND RoutePeople.ORIGINAL_ASSIGNEE_TYPE_CODE = 'ROLE'
AND RoutePeople.STEP_ID = RouteStep.STEP_ID
AND ( RouteStep.STEP_STATUS_CODE <> Eng_Workflow_Util.G_RT_NOT_STARTED
AND RouteStep.STEP_STATUS_CODE <> Eng_Workflow_Util.G_RT_IN_PROGRESS)
AND RouteStep.STEP_ID = c_step_id ;
SELECT ChangeSubj.ENTITY_NAME CHANGE_OBJECT_NAME
, ChangeSubj.PK1_VALUE
, ChangeSubj.PK2_VALUE
, ChangeSubj.PK3_VALUE
, ChangeSubj.PK4_VALUE
, ChangeSubj.PK5_VALUE
, ChangeSubj.CHANGE_ID
, ChangeSubj.CHANGE_LINE_ID
FROM ENG_CHANGE_SUBJECTS ChangeSubj
, FND_OBJECTS FndObj
WHERE ChangeSubj.ENTITY_NAME = FndObj.OBJ_NAME
AND ChangeSubj.CHANGE_ID = c_change_id
AND ( ChangeSubj.CHANGE_LINE_ID = c_change_line_id
OR (ChangeSubj.CHANGE_LINE_ID IS NULL
AND c_change_line_id = -1)
)
AND FndObj.OBJ_NAME = G_DOM_DOCUMENT_REVISION ; -- R12B We only support G_DOM_DOCUMENT_REVISION