[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_concurrent_update EXCEPTION;
PRAGMA EXCEPTION_INIT(g_concurrent_update, -00054);
SELECT partner_id ,benefit_id,benefit_type_code
FROM pv_referrals_b
WHERE referral_id = pc_referral_id;
SELECT source_name FROM jtf_rs_resource_extns WHERE user_id = pc_user_id;
SELECT APPROVER_ID
FROM PV_GE_TEMP_APPROVERS
WHERE ARC_APPR_FOR_ENTITY_CODE = p_approval_entity
AND APPR_FOR_ENTITY_ID = p_referral_id;
SELECT entity_approver_id
FROM pv_ge_temp_approvers
WHERE arc_appr_for_entity_code = p_approval_entity
AND appr_for_entity_id = p_referral_id
FOR UPDATE NOWAIT;
update pv_referrals_b set customer_country = p_country_code where referral_id = p_referral_id;
UPDATE pv_ge_temp_approvers
SET approval_status_code = 'PRIOR_APPROVER'
WHERE arc_appr_for_entity_code = p_approval_entity
AND appr_for_entity_id = p_referral_id;
DELETE FROM pv_ge_temp_approvers
WHERE arc_appr_for_entity_code = p_approval_entity
AND appr_for_entity_id = p_referral_id
AND approver_id = approverUserIds(x);
INSERT INTO pv_ge_temp_approvers
(
ENTITY_APPROVER_ID
,OBJECT_VERSION_NUMBER
,ARC_APPR_FOR_ENTITY_CODE
,APPR_FOR_ENTITY_ID
,APPROVER_ID
,APPROVER_TYPE_CODE
,APPROVAL_STATUS_CODE
,WORKFLOW_ITEM_KEY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
VALUES
(
pv_ge_temp_approvers_s.NEXTVAL
,1
,p_approval_entity
,p_referral_id
,approverUserIds(i)
,'USER'
,l_appr_status
,null
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.LOGIN_ID
);
,'Sucessfully inserted users into pv_ge_temp_approvers...'
);
l_log_params_tbl.DELETE;
FOR x in (SELECT source_name FROM jtf_rs_resource_extns B WHERE user_id = approverUserIds(apprCnt) )
LOOP
l_log_params_tbl(1).param_value := x.source_name;
WHEN g_concurrent_update THEN
fnd_message.Set_Name('PV', 'PV_REQUERY_THE_RECORD');
ROLLBACK TO UPDATE_APPROVER_RESPONSE;
This procedure starts the updates Responses given by approvers to the AME system.
*********************************************************************************/
PROCEDURE UPDATE_APPROVER_RESPONSE( p_api_version_number 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_referral_id IN NUMBER
, p_approval_entity IN VARCHAR2 -- PVREFFRL/PVDEALRN/PVDQMAPR
, p_response IN VARCHAR2 -- refer to AME_UTIL.approverIn
, p_approver_user_id IN NUMBER -- userID of the person sending approver resp
, p_forwardee_user_id IN NUMBER -- if forwarding then userID of the forwardee
, p_note_added_flag IN VARCHAR2 DEFAULT 'N' -- If note was added as part of this response.
, x_approval_done OUT NOCOPY VARCHAR2 -- True if approval process is finished False if not.
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_APPROVER_RESPONSE';
select approval_status_code from pv_ge_temp_approvers
where arc_appr_for_entity_code = pc_benefit_type
and appr_for_entity_id = pc_entity_id
and approver_type_code = 'USER'
and approver_id = pc_user_id
and approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT');
select decode(category, 'EMPLOYEE', source_id, null), category, source_name, user_name from jtf_rs_resource_extns where user_id = pc_user_id;
select return_reason_code from pv_referrals_b
where referral_id = p_referral_id;
select jtf_note_id
from (select jtf_note_id
from jtf_notes_vl
where source_object_id = p_referral_id
and source_object_code = p_approval_entity
order by entered_date desc)
where rownum = 1;
select count(entity_approver_id)
from pv_ge_temp_approvers
where arc_appr_for_entity_code = p_approval_entity
and appr_for_entity_id = p_referral_id
and approval_status_code in ('APPROVED','REJECTED');
SELECT entity_approver_id
FROM pv_ge_temp_approvers
WHERE arc_appr_for_entity_code = p_approval_entity
AND appr_for_entity_id = p_referral_id
FOR UPDATE NOWAIT;
SELECT APPROVER_ID
FROM PV_GE_TEMP_APPROVERS
WHERE ARC_APPR_FOR_ENTITY_CODE = p_approval_entity
AND APPR_FOR_ENTITY_ID = p_referral_id;
SAVEPOINT UPDATE_APPROVER_RESPONSE;
FOR x IN (SELECT employee_id,user_name FROM fnd_user WHERE user_id = p_forwardee_user_id
AND (end_date IS NULL OR end_date > sysdate-1))
LOOP
l_forwardee.orig_system_id := x.employee_id;
,'Forward case :b4 updateStatus call Forwardee ID :' || l_forwardee.orig_system_id||
' l_forwardee.name '||l_forwardee.name,FALSE);
FOR x IN (SELECT partner_id FROM pv_referrals_b WHERE referral_id = p_referral_id) LOOP
l_partner_id := x.partner_id;
,'Current User is in AME system. Sending update reponse to AME...');
(l_approver.api_insertion = ame_util.apiAuthorityInsertion
OR l_approver.api_insertion = ame_util.oamGenerated) ) THEN
l_forwardee.api_insertion := ame_util.apiAuthorityInsertion;
l_forwardee.api_insertion := ame_util.apiInsertion;
ame_api2.updateApprovalStatus2
(applicationIdIn => 691
, transactionTypeIn => p_approval_entity
, transactionIdIn => p_referral_id
, approvalStatusIn => l_response_to_ame
, approverNameIn => l_user_name
, itemClassIn => null
, itemIdIn => null
, actionTypeIdIn => null
, groupOrChainIdIn => null
, occurrenceIn => null
, forwardeeIn => l_forwardee
, updateItemIn => null
);
,'After AME_API.updateApprovalStatus2 call...'
);
,'Current User is no more in the AME System as an approver. AME was not updated...'
);
UPDATE pv_ge_temp_approvers
SET approval_status_code = l_approval_status
WHERE arc_appr_for_entity_code = p_approval_entity
AND appr_for_entity_id = p_referral_id
AND approver_id = p_approver_user_id
AND approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT')
AND rownum = 1;
UPDATE pv_ge_temp_approvers
SET approval_status_code = 'PEER_RESPONDED'
WHERE arc_appr_for_entity_code = p_approval_entity
AND appr_for_entity_id = p_referral_id
AND approval_status_code IN ('PENDING_APPROVAL');
,p_mode => 'UPDATE'
,x_approval_list => l_approval_list
,x_approval_completed => x_approval_done
,x_default_approver => l_default_approver
,x_user_id_exists => l_valid_users_flag);
* only then update current approvers to PEER_APPROVED
*
* Bug fix 5256368: Remove approver from FRW case. If the current approver
* is no longer in the list then updates to other approvers rows is not to
* be allowed.
*/
IF (NOT VALIDATE_APPROVAL(p_transaction_id => p_referral_id
, p_transaction_type => p_approval_entity
, p_user_id => p_approver_user_id
, p_person_id => l_approver_source_id
, p_mode => 'CHECK_PENDING_APPROVERS'
, p_approval_level => l_curr_appr_level
, x_approver => l_approver) AND l_approverInList)
THEN
UPDATE pv_ge_temp_approvers
SET approval_status_code = 'PEER_RESPONDED'
WHERE arc_appr_for_entity_code = p_approval_entity
AND appr_for_entity_id = p_referral_id
AND approval_status_code = 'PENDING_APPROVAL';
,'Inserting users into pv_ge_temp_approvers...'
);
INSERT INTO pv_ge_temp_approvers(
ENTITY_APPROVER_ID
,OBJECT_VERSION_NUMBER
,ARC_APPR_FOR_ENTITY_CODE
,APPR_FOR_ENTITY_ID
,APPROVER_ID
,APPROVER_TYPE_CODE
,APPROVAL_STATUS_CODE
,WORKFLOW_ITEM_KEY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)VALUES(
pv_ge_temp_approvers_s.NEXTVAL
,1
,p_approval_entity
,p_referral_id
,l_approval_list(i)
,'USER'
,l_pending_status
,null
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.LOGIN_ID
);
FOR x in (SELECT source_name FROM jtf_rs_resource_extns B WHERE user_id = l_approval_list(apprCnt) )
LOOP
l_log_params_tbl(1).param_value := x.source_name;
FOR x IN (SELECT partner_id ,benefit_id FROM pv_referrals_b WHERE referral_id = p_referral_id) LOOP
l_benefit_id := x.benefit_id;
ROLLBACK TO UPDATE_APPROVER_RESPONSE;
ROLLBACK TO UPDATE_APPROVER_RESPONSE;
WHEN g_concurrent_update THEN
fnd_message.Set_Name('PV', 'PV_REQUERY_THE_RECORD');
ROLLBACK TO UPDATE_APPROVER_RESPONSE;
ROLLBACK TO UPDATE_APPROVER_RESPONSE;
END UPDATE_APPROVER_RESPONSE;
SELECT 'Y'
FROM fnd_user A , pv_ge_temp_approvers appr
WHERE A.employee_id = pc_person_id
AND ( A.end_date IS NULL OR A.end_date > sysdate-1)
AND A.user_id = appr.approver_id
AND appr.approval_status_code = 'PENDING_APPROVAL'
AND appr.APPR_FOR_ENTITY_ID = p_transaction_id
AND appr.ARC_APPR_FOR_ENTITY_CODE = p_transaction_type;
FOR x IN (SELECT A.user_id FROM fnd_user A, jtf_rs_resource_extns B
WHERE employee_id = currApprRec.orig_system_id
AND A.user_id = B.user_id
AND ( A.end_date IS NULL OR A.end_date > sysdate-1) )
LOOP
x_approval_list.EXTEND();
IF p_mode = 'UPDATE' THEN
RAISE;
SELECT APPROVER_ID
FROM PV_GE_TEMP_APPROVERS
WHERE ARC_APPR_FOR_ENTITY_CODE = p_approval_entity
AND APPR_FOR_ENTITY_ID = p_referral_id;
DELETE FROM pv_ge_temp_approvers
WHERE arc_appr_for_entity_code = p_approval_entity
AND appr_for_entity_id = p_referral_id
AND approver_id = p_approval_list(x)
AND approval_status_code IN ('PRIOR_APPROVER');