[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jre.resource_id
FROM jtf_rs_resource_extns jre
WHERE jre.resource_id = p_resource_id;
PROCEDURE Update_User_Action(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_approval_rec IN approval_rec_type
)
IS
l_api_name CONSTANT varchar2(80) := 'Update_User_Action';
select user_id
from jtf_rs_resource_extns
where source_id = p_source_id
and sysdate >= start_date_active
and sysdate <= nvl(end_date_active, sysdate)
and rownum < 2;
SELECT approval_access_id, approver_id, approver_type,action_code
FROM ozf_approval_access
WHERE approval_access_flag = 'Y'
AND object_type = p_object_type
AND object_id = p_object_id;
SELECT count(1)
FROM ozf_approval_access
WHERE approval_access_flag = 'Y'
AND object_type = p_object_type
AND object_id = p_object_id;
SELECT nvl(min(approval_level),0)
FROM ozf_approval_access
WHERE object_type = p_object_type
AND object_id = p_object_id;
SELECT nvl(max(approval_level),0)
FROM ozf_approval_access
WHERE object_type = p_object_type
AND object_id = p_object_id;
select count(1)
from jtf_auth_principal_maps jtfpm,
jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm, jtf_rs_resource_extns pj,
fnd_user usr
where PJ.user_name = jtfp1.principal_name
and pj.category = 'EMPLOYEE'
and usr.user_id = pj.user_id
and (usr.end_date > sysdate OR usr.end_date IS NULL)
and jtfp1.is_user_flag=1
and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
and jtfp2.is_user_flag=0
and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
and jtfrp.positive_flag = 1
and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
and jtfperm.permission_name = pc_permission
and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
and jtfd.domain_name='CRM_DOMAIN'
and usr.user_id = pc_userid;
SAVEPOINT Update_User_Action_PVT;
UPDATE ozf_approval_access
SET action_code = p_approval_rec.action_code
, action_date = SYSDATE
, action_performed_by = p_approval_rec.action_performed_by
WHERE approval_access_id = l_approval_access_id;
UPDATE ozf_approval_access
SET action_code = p_approval_rec.action_code
, action_date = SYSDATE
, action_performed_by = p_approval_rec.action_performed_by
WHERE object_type = p_approval_rec.object_type
AND object_id = p_approval_rec.object_id
AND approver_id = p_approval_rec.action_performed_by
AND approval_level = l_approver_level;
03/20/04 by feliu: to check l_action_code. when budget line get rejected, we can not go to ame to update status.
*/
if l_min_reassign_level <> 0 AND l_action_code is NULL then
-- Update AME with approvers action
/*
type approverRecord is record(
user_id fnd_user.user_id%type,
person_id per_all_people_f.person_id%type,
first_name per_all_people_f.first_name%type,
last_name per_all_people_f.last_name%type,
api_insertion varchar2(1),
authority varchar2(1),
approval_status varchar2(50),
approval_type_id integer,
group_or_chain_id integer,
occurrence integer,
source varchar2(500));
l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
l_ame_forward_rec.api_insertion := ame_util.apiAuthorityInsertion;
l_ame_forward_rec.api_insertion := ame_util.apiAuthorityInsertion;
l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
l_ame_approver_rec.api_insertion := ame_util.oamGenerated;
l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
l_ame_approver_rec.api_insertion := ame_util.oamGenerated;
l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
l_ame_approver_rec.api_insertion := ame_util.oamGenerated;
-- Update AME of Approval Status
AME_API.updateApprovalStatus(applicationIdIn => l_application_id
,transactionIdIn => p_approval_rec.object_id
,approverIn => l_ame_approver_rec
,transactionTypeIn => p_approval_rec.object_type
,forwardeeIn => l_ame_forward_rec
);
ROLLBACK TO Update_User_Action_PVT;
ROLLBACK TO Update_User_Action_PVT;
ROLLBACK TO Update_User_Action_PVT;
END Update_User_Action;
SELECT nvl(max(approval_level),0)
FROM ozf_approval_access
WHERE object_type = p_object_type
AND object_id = p_object_id;
--Insert Default Approver into Group
l_ame_approver_rec.user_id := x_approvers(1).approver_id;
l_ame_approver_rec.api_insertion := ame_util.apiAuthorityInsertion;
AME_API.updateApprovalStatus(applicationIdIn => l_application_id
,transactionIdIn => p_approval_rec.object_id
,approverIn => l_ame_approver_rec
,transactionTypeIn => p_approval_rec.object_type
,forwardeeIn => l_ame_forward_rec
);
SELECT ozf_approval_access_s.NEXTVAL
FROM dual;
ozf_utility_pvt.debug_message( 'Inserting data into OZF_APPROVAL_ACCESS table');
INSERT INTO OZF_APPROVAL_ACCESS(
approval_access_id
,object_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_type
,object_id
,approval_level
,approver_type
,approver_id
,approval_access_flag
,workflow_itemkey
) VALUES (
l_approval_access_id
,1
,SYSDATE
,G_USER_ID
,SYSDATE
,G_USER_ID
,G_LOGIN_ID
,p_approval_rec.object_type
,p_approval_rec.object_id
,p_approvers(i).approver_level
,p_approvers(i).approver_type
,p_approvers(i).approver_id
,'Y'
,l_workflow_itemkey
);
ozf_utility_pvt.error_message('OZF_APPROVAL_ACCESS_INSERT_ERR');
SELECT approval_access_id
FROM ozf_approval_access
WHERE approval_access_flag = 'Y'
AND object_type = p_object_type
AND object_id = p_object_id;
UPDATE ozf_approval_access
SET approval_access_flag = 'N'
WHERE approval_access_id = l_approval_access_id;
SELECT name
FROM wf_events
WHERE name = p_event_name;
SELECT user_role
, wf_message_type
, wf_message_name
FROM pv_notification_setups
WHERE benefit_id = p_object_id
AND entity_status = p_status
AND user_role like p_user_role;
SELECT count(1)
FROM ozf_approval_access
WHERE object_type = p_object_type
AND object_id = p_object_id
AND approval_access_flag = 'Y';
SELECT fnd_user.user_name
FROM pv_partner_accesses acc
, jtf_rs_resource_extns res
, fnd_user
WHERE acc.partner_id = p_partner_id
AND acc.resource_id = res.resource_id
AND res.user_id = fnd_user.user_id;
SELECT fu.user_name
FROM ozf_approval_access oaa
, fnd_user fu
--, jtf_rs_resource_extns jre
WHERE oaa.object_type = p_object_type
AND oaa.object_id = p_object_id
AND oaa.approver_type = 'USER'
--AND fu.user_id = jre.user_id
--AND oaa.approver_id = jre.resource_id
AND oaa.approver_id = fu.user_id
AND oaa.approval_access_flag = 'Y'
UNION
SELECT jre.user_name
FROM ozf_approval_access oaa
, jtf_rs_resource_extns jre
WHERE oaa.object_type = p_object_type
AND oaa.object_id = p_object_id
AND oaa.approver_type = 'PERSON'
AND oaa.approver_id = jre.source_id
AND oaa.approval_access_flag = 'Y'
group by jre.user_name;
EXECUTE IMMEDIATE 'SELECT ' || l_user_callback_api ||
'(:itemtype, :entity_id, :usertype, :status) FROM dual'
INTO l_role_list
USING l_object_type, l_object_id, l_user_type, l_status ;
update ozf_approval_access
set workflow_itemkey = substr(l_item_key,1,239)
where object_type = l_object_type
and object_id = l_object_id
and approval_level = ( select max (approval_level)
from ozf_approval_access
where object_type = l_object_type
and object_id = l_object_id);
select nvl(approval_level,1) + 1
from ozf_approval_access
where object_id = p_request_id
and object_type = p_object_type
and approval_access_flag = 'Y'
and rownum < 2;
select benefit_id
, partner_id
from ozf_request_headers_all_b
where request_header_id = p_request_id;
ozf_utility_pvt.debug_message( 'Update User Action ' || p_approval_rec.action_code );
Update_User_Action(
p_api_version => p_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_approval_rec => p_approval_rec);
l_insert_mode VARCHAR2(1); --:= 'Y';
SELECT user_id, resource_id
FROM jtf_rs_resource_extns
WHERE category = 'EMPLOYEE'
AND source_id = p_person_id
AND user_id IS NOT NULL
AND sysdate >= start_date_active
AND sysdate <= nvl(end_date_active, sysdate)
AND rownum < 2;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE category = 'EMPLOYEE'
AND user_id = p_user_id
AND sysdate >= start_date_active
AND sysdate <= nvl(end_date_active, sysdate)
AND rownum < 2;
SELECT count(*)
FROM OZF_SD_REQUEST_ACCESS
WHERE request_header_id = p_object_id
AND approver_flag = 'Y'
AND enabled_flag = 'Y';
l_insert_mode := 'Y';
l_insert_mode := 'N';
l_insert_mode := 'Y';
l_insert_mode := 'N';
IF (l_insert_mode = 'Y') THEN
IF G_DEBUG THEN
ozf_utility_pvt.debug_message( 'Invoke Add_SD_Access for AME approver: ' || l_orig_system_id);
l_insert_mode := 'N';
l_insert_mode := 'Y';
l_insert_mode := 'N';
IF (l_insert_mode = 'Y') THEN
IF G_DEBUG THEN
ozf_utility_pvt.debug_message( 'Invoke Add_SD_Access for default approver: ' || l_user_id);
select user_id
from jtf_rs_resource_extns
where resource_id = p_resource_id
and sysdate >= start_date_active
and sysdate <= nvl(end_date_active, sysdate)
and rownum < 2;
select resource_id
from jtf_rs_resource_extns
where user_id = p_user_id
and sysdate >= start_date_active
and sysdate <= nvl(end_date_active, sysdate)
and rownum < 2;
l_insert_mode VARCHAR2(1) := 'N';
SELECT OZF_SD_REQUEST_ACCESS_S.NEXTVAL
FROM dual;
SELECT resource_id, user_id, approver_flag, object_version_number
FROM OZF_SD_REQUEST_ACCESS
WHERE request_header_id = p_object_id
AND enabled_flag = 'Y'
AND owner_flag = 'Y';
SELECT resource_id, owner_flag, approver_flag, object_version_number
FROM OZF_SD_REQUEST_ACCESS
WHERE request_header_id = p_object_id
AND enabled_flag = 'Y'
AND resource_id = p_resource_id;
UPDATE OZF_SD_REQUEST_ACCESS
SET approver_flag = 'Y',
object_version_number = l_exist_version_number + 1
WHERE request_header_id = p_access_rec.REQUEST_HEADER_ID
AND resource_id = p_access_rec.RESOURCE_ID
AND enabled_flag = 'Y';
l_insert_mode :='Y';
UPDATE OZF_SD_REQUEST_ACCESS
SET enabled_flag = NULL,
object_version_number = l_exist_version_number + 1
WHERE request_header_id = p_access_rec.REQUEST_HEADER_ID
AND resource_id = l_exist_resource_id
AND enabled_flag = 'Y';
UPDATE OZF_SD_REQUEST_ACCESS
SET owner_flag = NULL,
object_version_number = l_exist_version_number + 1
WHERE request_header_id = p_access_rec.REQUEST_HEADER_ID
AND resource_id = l_exist_resource_id
AND enabled_flag = 'Y';
UPDATE OZF_SD_REQUEST_ACCESS
SET owner_flag = 'Y',
object_version_number = l_exist_version_number + 1
WHERE request_header_id = p_access_rec.REQUEST_HEADER_ID
AND resource_id = p_access_rec.RESOURCE_ID
AND enabled_flag = 'Y';
l_insert_mode :='Y';
l_insert_mode :='Y';
IF (l_insert_mode = 'Y') THEN
BEGIN
IF G_DEBUG THEN
ozf_utility_pvt.debug_message( 'Inserting data into OZF_SD_REQUEST_ACCESS table');
INSERT INTO OZF_SD_REQUEST_ACCESS(
request_access_id
,request_header_id
,user_id
,resource_id
,person_id
,owner_flag
,approver_flag
,enabled_flag
,object_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
VALUES(
l_approval_access_id
,p_access_rec.REQUEST_HEADER_ID
,p_access_rec.USER_ID
,p_access_rec.RESOURCE_ID
,p_access_rec.PERSON_ID
,p_access_rec.OWNER_FLAG
,p_access_rec.APPROVER_FLAG
,p_access_rec.ENABLED_FLAG
,1
,SYSDATE
,G_USER_ID
,SYSDATE
,G_USER_ID
,G_LOGIN_ID
);
ozf_utility_pvt.error_message('OZF_SD_REQ_ACCESS_INSERT_ERR');
SELECT fu.user_name
FROM ozf_sd_request_access ora,
fnd_user fu
WHERE ora.request_header_id = p_object_id
AND ora.person_id IS NULL
AND ora.user_id = fu.user_id
AND ora.approver_flag = 'Y'
AND ora.enabled_flag = 'Y'
UNION
SELECT jre.user_name
FROM ozf_sd_request_access ora,
jtf_rs_resource_extns jre
WHERE ora.request_header_id = p_object_id
AND ora.person_id IS NOT NULL
AND ora.person_id = jre.source_id
AND ora.approver_flag = 'Y'
AND ora.enabled_flag = 'Y'
GROUP BY jre.user_name;
SELECT fu.user_name
FROM ozf_sd_request_access ora,
fnd_user fu
WHERE ora.request_header_id = p_object_id
AND ora.person_id IS NULL
AND ora.user_id = fu.user_id
AND ora.enabled_flag = 'Y'
UNION
SELECT jre.user_name
FROM ozf_sd_request_access ora,
jtf_rs_resource_extns jre
WHERE ora.request_header_id = p_object_id
AND ora.person_id IS NOT NULL
AND ora.person_id = jre.source_id
AND ora.enabled_flag = 'Y'
GROUP BY jre.user_name;
SELECT function_id FROM fnd_form_functions
WHERE function_name = p_func_name ;
SELECT orh.request_number,
requester.source_name,
aps.vendor_name,
hou.name,
orh.creation_date,
orh.request_start_date,
orh.request_end_date,
orh.supplier_response_date,
orh.supplier_response_by_date,
orh.authorization_number
FROM ozf_sd_request_headers_all_b orh
, ap_suppliers aps
, jtf_rs_resource_extns requester
, hr_all_organization_units hou
WHERE
orh.request_header_id = p_object_id
AND orh.requestor_id = requester.resource_id (+)
AND orh.SUPPLIER_ID = aps. vendor_id (+)
AND orh.org_id = hou.organization_id (+);
SELECT assignee.source_name,
orh.asignee_response_date,
orh.asignee_response_by_date
FROM ozf_sd_request_headers_all_b orh
, jtf_rs_resource_extns assignee
WHERE
orh.request_header_id = p_object_id
AND orh.asignee_resource_id = assignee.resource_id;
SELECT ACSV.setup_name
FROM ams_custom_setups_vl ACSV,
OZF_SD_REQUEST_HEADERS_ALL_B OSRH
WHERE OSRH.request_header_id = p_object_id
AND ACSV.custom_setup_id= OSRH.request_type_setup_id;
SELECT function_id
FROM fnd_form_functions
WHERE function_name = pc_func_name;