[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select value
INTO l_utl_file_dir
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
FROM FND_LOOKUPS AssigneeTypeLookup
WHERE AssigneeTypeLookup.LOOKUP_CODE = p_assignee_type
AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES' ;
SELECT MenuTL.USER_MENU_NAME ASSIGNEE_NAME
FROM FND_MENUS_TL MenuTL
WHERE MenuTL.LANGUAGE = USERENV('LANG')
AND MenuTL.MENU_ID = p_role_id ;
SELECT AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
, '' ASSIGNEE_COMPANY
FROM FND_LOOKUPS AssigneeTypeLookup
WHERE AssigneeTypeLookup.LOOKUP_CODE = 'ROLE'
AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES' ;
SELECT Obj.DISPLAY_NAME OBJECT_NAME
FROM ( select distinct f.object_id, e.menu_id
from fnd_form_functions f, fnd_menu_entries e
where e.function_id = f.function_id) EgoRoles,
FND_OBJECTS_VL Obj
WHERE Obj.OBJECT_ID = EgoRoles.OBJECT_ID
-- AND Obj.OBJ_NAME <> 'ENG_CHANGE'
AND EgoRoles.MENU_ID = p_role_id ;
SELECT Parties.PARTY_NAME ASSIGNEE_NAME
, AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
, Company.PARTY_NAME ASSIGNEE_COMPANY
FROM FND_LOOKUPS AssigneeTypeLookup
, HZ_RELATIONSHIPS Emp_Cmpy
, HZ_PARTIES Company
, HZ_PARTIES Parties
WHERE AssigneeTypeLookup.LOOKUP_CODE = 'PERSON'
AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
AND 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 (+)= Parties.PARTY_ID
AND Parties.PARTY_TYPE = 'PERSON'
AND Parties.PARTY_ID = p_person_id ;
SELECT Grp.PARTY_NAME ASSIGNEE_NAME
, AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
, '' ASSIGNEE_COMPANY
FROM FND_LOOKUPS AssigneeTypeLookup
, HZ_PARTIES Grp
WHERE AssigneeTypeLookup.LOOKUP_CODE = 'GROUP'
AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
AND Grp.PARTY_TYPE = 'GROUP'
AND Grp.PARTY_ID = p_group_id ;
SELECT ChgPolicyAssigneeLookup.MEANING ASSIGNEE_NAME
, AssigneeTypeLookup.MEANING ASSIGNEE_TYPE
, '' ASSIGNEE_COMPANY
FROM FND_LOOKUPS ChgPolicyAssigneeLookup
, FND_LOOKUPS AssigneeTypeLookup
WHERE AssigneeTypeLookup.LOOKUP_CODE = 'CHANGE_POLICY'
AND AssigneeTypeLookup.LOOKUP_TYPE = 'ENG_ROUTE_ASSIGNEE_TYPES'
AND ChgPolicyAssigneeLookup.LOOKUP_TYPE = 'ENG_ROUTE_CHG_POLICY_ASSIGNEES'
AND ChgPolicyAssigneeLookup.LOOKUP_CODE = p_chg_policy_assignee_id ;
cursor c is select
ROUTE_PEOPLE_ID,
STEP_ID,
ASSIGNEE_ID,
ASSIGNEE_TYPE_CODE,
ADHOC_PEOPLE_FLAG,
WF_NOTIFICATION_ID,
RESPONSE_CODE,
RESPONSE_DATE,
REQUEST_ID,
ORIGINAL_SYSTEM_REFERENCE,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
ORIGINAL_ASSIGNEE_ID,
ORIGINAL_ASSIGNEE_TYPE_CODE,
RESPONSE_CONDITION_CODE,
RESPONSE_DESCRIPTION
from ENG_CHANGE_ROUTE_PEOPLE_VL
where STEP_ID = P_FROM_STEP_ID
and PARENT_ROUTE_PEOPLE_ID IS NULL
;
SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL into l_people_id
FROM DUAL;
INSERT_ROW (
X_ROWID => l_rowid,
X_ROUTE_PEOPLE_ID => l_people_id ,
X_STEP_ID => P_TO_STEP_ID ,
X_ASSIGNEE_ID => recinfo.ASSIGNEE_ID,
X_ASSIGNEE_TYPE_CODE => recinfo.ASSIGNEE_TYPE_CODE,
X_ADHOC_PEOPLE_FLAG => recinfo.ADHOC_PEOPLE_FLAG,
X_WF_NOTIFICATION_ID => NULL ,
X_RESPONSE_CODE => NULL ,
X_RESPONSE_DATE => NULL ,
X_REQUEST_ID => recinfo.REQUEST_ID,
X_ORIGINAL_SYSTEM_REFERENCE => recinfo.ORIGINAL_SYSTEM_REFERENCE,
X_RESPONSE_DESCRIPTION => NULL,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => l_fnd_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => l_fnd_user_id,
X_LAST_UPDATE_LOGIN => l_fnd_login_id,
X_PROGRAM_ID => recinfo.PROGRAM_ID,
X_PROGRAM_APPLICATION_ID => recinfo.PROGRAM_APPLICATION_ID,
X_PROGRAM_UPDATE_DATE => recinfo.PROGRAM_UPDATE_DATE,
X_ORIGINAL_ASSIGNEE_ID => recinfo.ORIGINAL_ASSIGNEE_ID,
X_ORIGINAL_ASSIGNEE_TYPE_CODE => recinfo.ORIGINAL_ASSIGNEE_TYPE_CODE,
X_RESPONSE_CONDITION_CODE => recinfo.RESPONSE_CONDITION_CODE,
X_PARENT_ROUTE_PEOPLE_ID => NULL
) ;
* PROCEDURE INSERT_ROW;
* PROCEDURE UPDATE_ROW;
* PROCEDURE DELETE_ROW;
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_ROUTE_PEOPLE_ID IN NUMBER,
X_STEP_ID IN NUMBER,
X_ASSIGNEE_ID IN NUMBER,
X_ASSIGNEE_TYPE_CODE IN VARCHAR2,
X_ADHOC_PEOPLE_FLAG IN VARCHAR2,
X_WF_NOTIFICATION_ID IN NUMBER,
X_RESPONSE_CODE IN VARCHAR2,
X_RESPONSE_DATE IN DATE,
X_REQUEST_ID IN NUMBER,
X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
X_RESPONSE_DESCRIPTION IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_PROGRAM_ID IN NUMBER,
X_PROGRAM_APPLICATION_ID IN NUMBER,
X_PROGRAM_UPDATE_DATE IN DATE,
X_ORIGINAL_ASSIGNEE_ID IN NUMBER,
X_ORIGINAL_ASSIGNEE_TYPE_CODE IN VARCHAR2,
X_RESPONSE_CONDITION_CODE IN VARCHAR2,
X_PARENT_ROUTE_PEOPLE_ID IN NUMBER
)
IS
cursor C is select ROWID from ENG_CHANGE_ROUTE_PEOPLE
where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
;
insert into ENG_CHANGE_ROUTE_PEOPLE (
ROUTE_PEOPLE_ID,
STEP_ID,
ASSIGNEE_ID,
ASSIGNEE_TYPE_CODE,
ADHOC_PEOPLE_FLAG,
WF_NOTIFICATION_ID,
RESPONSE_CODE,
RESPONSE_DATE,
REQUEST_ID,
ORIGINAL_SYSTEM_REFERENCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
ORIGINAL_ASSIGNEE_ID,
ORIGINAL_ASSIGNEE_TYPE_CODE,
RESPONSE_CONDITION_CODE,
PARENT_ROUTE_PEOPLE_ID
) values (
X_ROUTE_PEOPLE_ID,
X_STEP_ID,
X_ASSIGNEE_ID,
X_ASSIGNEE_TYPE_CODE,
X_ADHOC_PEOPLE_FLAG,
X_WF_NOTIFICATION_ID,
X_RESPONSE_CODE,
X_RESPONSE_DATE,
X_REQUEST_ID,
X_ORIGINAL_SYSTEM_REFERENCE,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_PROGRAM_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_UPDATE_DATE,
X_ORIGINAL_ASSIGNEE_ID,
X_ORIGINAL_ASSIGNEE_TYPE_CODE,
X_RESPONSE_CONDITION_CODE,
X_PARENT_ROUTE_PEOPLE_ID
);
insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
X_ROUTE_PEOPLE_ID,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_RESPONSE_DESCRIPTION,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
END INSERT_ROW;
X_PROGRAM_UPDATE_DATE IN DATE,
X_ORIGINAL_ASSIGNEE_ID IN NUMBER,
X_ORIGINAL_ASSIGNEE_TYPE_CODE IN VARCHAR2,
X_RESPONSE_CONDITION_CODE IN VARCHAR2,
X_PARENT_ROUTE_PEOPLE_ID IN NUMBER
)
IS
cursor c is select
STEP_ID,
ASSIGNEE_ID,
ASSIGNEE_TYPE_CODE,
ADHOC_PEOPLE_FLAG,
WF_NOTIFICATION_ID,
RESPONSE_CODE,
RESPONSE_DATE,
REQUEST_ID,
ORIGINAL_SYSTEM_REFERENCE,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
ORIGINAL_ASSIGNEE_ID,
ORIGINAL_ASSIGNEE_TYPE_CODE,
RESPONSE_CONDITION_CODE,
PARENT_ROUTE_PEOPLE_ID
from ENG_CHANGE_ROUTE_PEOPLE
where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
for update of ROUTE_PEOPLE_ID nowait;
cursor c1 is select
RESPONSE_DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from ENG_CHANGE_ROUTE_PEOPLE_TL
where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of ROUTE_PEOPLE_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE UPDATE_ROW (
X_ROUTE_PEOPLE_ID IN NUMBER,
X_STEP_ID IN NUMBER,
X_ASSIGNEE_ID IN NUMBER,
X_ASSIGNEE_TYPE_CODE IN VARCHAR2,
X_ADHOC_PEOPLE_FLAG IN VARCHAR2,
X_WF_NOTIFICATION_ID IN NUMBER,
X_RESPONSE_CODE IN VARCHAR2,
X_RESPONSE_DATE IN DATE,
X_REQUEST_ID IN NUMBER,
X_ORIGINAL_SYSTEM_REFERENCE IN VARCHAR2,
X_RESPONSE_DESCRIPTION IN VARCHAR2,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_PROGRAM_ID IN NUMBER,
X_PROGRAM_APPLICATION_ID IN NUMBER,
X_PROGRAM_UPDATE_DATE IN DATE,
X_ORIGINAL_ASSIGNEE_ID IN NUMBER,
X_ORIGINAL_ASSIGNEE_TYPE_CODE IN VARCHAR2,
X_RESPONSE_CONDITION_CODE IN VARCHAR2,
X_PARENT_ROUTE_PEOPLE_ID IN NUMBER
)
IS
BEGIN
update ENG_CHANGE_ROUTE_PEOPLE set
STEP_ID = X_STEP_ID,
ASSIGNEE_ID = X_ASSIGNEE_ID,
ASSIGNEE_TYPE_CODE = X_ASSIGNEE_TYPE_CODE,
ADHOC_PEOPLE_FLAG = X_ADHOC_PEOPLE_FLAG,
WF_NOTIFICATION_ID = X_WF_NOTIFICATION_ID,
RESPONSE_CODE = X_RESPONSE_CODE,
RESPONSE_DATE = X_RESPONSE_DATE,
REQUEST_ID = X_REQUEST_ID,
ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
ORIGINAL_ASSIGNEE_ID = X_ORIGINAL_ASSIGNEE_ID,
ORIGINAL_ASSIGNEE_TYPE_CODE = X_ORIGINAL_ASSIGNEE_TYPE_CODE,
RESPONSE_CONDITION_CODE = X_RESPONSE_CONDITION_CODE,
PARENT_ROUTE_PEOPLE_ID = PARENT_ROUTE_PEOPLE_ID
where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID;
update ENG_CHANGE_ROUTE_PEOPLE_TL set
RESPONSE_DESCRIPTION = X_RESPONSE_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
END UPDATE_ROW;
PROCEDURE DELETE_ROW (
X_ROUTE_PEOPLE_ID IN NUMBER
)
IS
BEGIN
delete from ENG_CHANGE_ROUTE_PEOPLE_TL
where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID;
delete from ENG_CHANGE_ROUTE_PEOPLE
where ROUTE_PEOPLE_ID = X_ROUTE_PEOPLE_ID;
END DELETE_ROW ;
delete from ENG_CHANGE_ROUTE_PEOPLE_TL T
where not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE B
where B.ROUTE_PEOPLE_ID = T.ROUTE_PEOPLE_ID
);
update ENG_CHANGE_ROUTE_PEOPLE_TL T set (
RESPONSE_DESCRIPTION
) = (select
B.RESPONSE_DESCRIPTION
from ENG_CHANGE_ROUTE_PEOPLE_TL B
where B.ROUTE_PEOPLE_ID = T.ROUTE_PEOPLE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ROUTE_PEOPLE_ID,
T.LANGUAGE
) in (select
SUBT.ROUTE_PEOPLE_ID,
SUBT.LANGUAGE
from ENG_CHANGE_ROUTE_PEOPLE_TL SUBB, ENG_CHANGE_ROUTE_PEOPLE_TL SUBT
where SUBB.ROUTE_PEOPLE_ID = SUBT.ROUTE_PEOPLE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.RESPONSE_DESCRIPTION <> SUBT.RESPONSE_DESCRIPTION
or (SUBB.RESPONSE_DESCRIPTION is null and SUBT.RESPONSE_DESCRIPTION is not null)
or (SUBB.RESPONSE_DESCRIPTION is not null and SUBT.RESPONSE_DESCRIPTION is null)
));
insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
ROUTE_PEOPLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RESPONSE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
B.ROUTE_PEOPLE_ID,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.RESPONSE_DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from ENG_CHANGE_ROUTE_PEOPLE_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from ENG_CHANGE_ROUTE_PEOPLE_TL T
where T.ROUTE_PEOPLE_ID = B.ROUTE_PEOPLE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);