[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT entity_approver_id, object_version_number,approval_status_code
FROM pv_ge_temp_approvers
WHERE APPR_FOR_ENTITY_ID =enrl_id
AND ARC_APPR_FOR_ENTITY_CODE='ENRQ'
AND APPROVER_ID=DECODE( APPROVER_TYPE_CODE,'PERSON',apprid,'USER',FND_GLOBAL.USER_ID,null );
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pv_enty_attr_values
WHERE entity = 'PARTNER'
AND attribute_id = 3
AND latest_flag = 'Y'
AND entity_id = p_part_id
AND attr_value= p_ptr_type
AND attr_value_extn = 'Y'
);
SELECT user_id
FROM fnd_user
WHERE user_name=uname;
select program_end_date,
decode( membership_period_unit
,'DAY',start_date+membership_valid_period
,'MONTH',add_months(start_date,membership_valid_period)
,'YEAR',add_months(start_date,12*membership_valid_period)
,null
) membership_end_date
from pv_partner_program_b
where program_id=p_prgm_id;
SELECT min(original_end_date)
FROM pv_pg_memberships
WHERE partner_id=g_ptr_id
AND membership_status_code='ACTIVE'
AND program_id IN
(
SELECT distinct(change_to_program_id)
FROM pv_pg_enrl_change_rules
START WITH change_from_program_id=dependent_id
AND change_direction_code='UPGRADE'
AND ACTIVE_FLAG='Y'
AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
CONNECT BY change_from_program_id=PRIOR change_to_program_id
AND change_direction_code='UPGRADE'
AND ACTIVE_FLAG='Y'
AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
UNION
SELECT dependent_id
FROM
DUAL
);
SELECT min(original_end_date)
FROM pv_pg_memberships
WHERE partner_id=g_ptr_id
AND membership_status_code='FUTURE'
AND program_id IN
(
SELECT distinct(change_to_program_id)
FROM pv_pg_enrl_change_rules
START WITH change_from_program_id=dependent_id
AND change_direction_code='UPGRADE'
AND ACTIVE_FLAG='Y'
AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
CONNECT BY change_from_program_id=PRIOR change_to_program_id
AND change_direction_code='UPGRADE'
AND ACTIVE_FLAG='Y'
AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
UNION
SELECT dependent_id
FROM
DUAL
);
SELECT glob.partner_id
FROM pv_partner_profiles glob
, hz_relationships rel
, pv_partner_profiles sub
WHERE glob.partner_party_id= rel.object_id
AND rel.subject_id=sub.partner_party_id
AND sub.partner_id=sub_ptr_id
AND relationship_type='PARTNER_HIERARCHY'
AND rel.status='A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE ;
SELECT nvl(actual_end_date,original_end_date) prev_end_date
FROM pv_pg_memberships memb,pv_pg_enrq_init_sources eni
WHERE eni.prev_membership_id=memb.membership_id
AND eni.enrl_request_id=p_enrl_req_id;
SELECT enrollment_type_code,program_id,tentative_start_date,tentative_end_date,request_status_code,partner_id,dependent_program_id
FROM pv_pg_enrl_requests
WHERE enrl_request_id=p_enrl_req_id;
SELECT attr_value
FROM pv_enty_attr_values
WHERE entity_id=p_ptnr_id
AND attribute_id=6
AND latest_flag='Y'
AND entity='PARTNER';
SELECT start_date start_date
, nvl(actual_end_date,original_end_date) end_date
FROM pv_pg_memberships memb
WHERE enrl_request_id=p_enrl_req_id;
SELECT 'X'
FROM dual
WHERE EXISTS
( SELECT 'X'
FROM pv_program_contracts
WHERE program_id = p_prgm_id
);
SELECT no_fee_flag
FROM pv_partner_program_b
WHERE program_id = p_prgm_id;
select 'X' from dual where exists (
select entity_approver_id from pv_ge_temp_approvers
where appr_for_entity_id=p_enrl_req_id
and approval_status_code='PENDING_DEFAULT');
select 'X' from dual where exists
( select entity_approver_id from pv_ge_temp_approvers where appr_for_entity_id=p_enrl_req_id);
select 'X' from dual where exists (
select entity_approver_id from pv_ge_temp_approvers
where appr_for_entity_id=p_enrl_req_id
and approval_status_code in ('PENDING_APPROVAL'));
SELECT contract_status_code
FROM pv_pg_enrl_requests
WHERE enrl_request_id=p_enrl_req_id;
SELECT prgm.allow_enrl_wout_chklst_flag
FROM pv_pg_enrl_requests enrq, pv_partner_program_b prgm
WHERE enrq.program_id=prgm.program_id
AND enrl_request_id=p_enrl_req_id;
SELECT 'X' from dual
where EXISTS
( SELECT checklist_item_id
FROM pv_ge_chklst_responses
WHERE response_for_entity_id = p_enrl_req_id
AND RESPONSE_FLAG='N'
);
select 'X' from dual where exists
(select approver_id from ams_approvers appr,ams_approval_details apdt
where nvl(appr.start_date_active,sysdate)<=sysdate
and nvl(appr.end_date_active,sysdate)>=sysdate
and appr.ams_approval_detail_id =apdt.approval_detail_id
and apdt.approval_object_type=p_prgm_type_id
and apdt.approval_object='PRGT'
and apdt.approval_type='CONCEPT'
and nvl(apdt.active_flag,'Y') = 'Y'
and nvl(appr.active_flag,'Y')='Y'
);
select PROGRAM_STATUS_CODE from pv_partner_program_b where program_id=p_parent_prgm_id and ENABLED_FLAG='Y';
SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
WHERE PROGRAM_ID =p_program_id
AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
WHERE PROGRAM_ID =p_program_id
AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
WHERE PROGRAM_ID =p_program_id
AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
WHERE PROGRAM_ID =p_program_id
AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
SELECT PROGRAM_NAME,MEANING,PROGRAM_START_DATE,PROGRAM_END_DATE,SOURCE_NAME,PROGRAM_DESCRIPTION,SOURCE_BUSINESS_GRP_NAME
FROM PV_PARTNER_PROGRAM_VL ,JTF_RS_RESOURCE_EXTNS,FND_LOOKUP_VALUES_VL
WHERE PROGRAM_ID =p_program_id
AND PROGRAM_OWNER_RESOURCE_ID =RESOURCE_ID
AND LOOKUP_CODE=PROGRAM_LEVEL_CODE
AND LOOKUP_TYPE='PV_PROGRAM_LEVEL';
SELECT ppv.program_name,to_char(ppv.program_type_id) from pv_partner_program_vl ppv, pv_partner_program_type_vl ppt
WHERE ppv.program_id=p_program_id
AND ppv.program_type_id=ppt.program_type_id;
PROCEDURE update_parprogram_status (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2
) IS
l_status_code VARCHAR2 (15);
l_api_name CONSTANT VARCHAR2 (30) := 'Update_ParProgram_Status';
l_update_status VARCHAR2 (12);
SELECT cont.object_version_number object_version_number , prog.citem_version_id citem_version_id
FROM ibc_citem_versions_b cont_ver, ibc_content_items cont,pv_partner_program_b prog
WHERE prog.program_id = prgm_id
AND prog.citem_version_id = cont_ver.citem_version_id
AND cont_ver.content_item_id = cont.content_item_id;
SELECT *
FROM PV_PARTNER_PROGRAM_B
WHERE PROGRAM_PARENT_ID = cv_program_id
AND ENABLED_FLAG='Y'
AND SUBMIT_CHILD_NODES='Y'
AND PROGRAM_STATUS_CODE='NEW';
SELECT user_status_id
FROM AMS_USER_STATUSES_B
where SYSTEM_STATUS_TYPE='PV_PROGRAM_STATUS'
and SYSTEM_STATUS_CODE=cv_status_code;
SELECT object_version_number
FROM pv_partner_program_b
WHERE program_id=cv_program_id;
SELECT program_status_code,object_version_number
FROM pv_partner_program_b
WHERE program_id=cv_program_id;
l_update_status :=
wf_engine.getitemattrtext (
itemtype=> itemtype,
itemkey=> itemkey,
aname => 'UPDATE_GEN_STATUS'
);
IF l_update_status = 'APPROVED' THEN
l_next_status_id :=
wf_engine.getitemattrnumber (
itemtype=> itemtype,
itemkey=> itemkey,
aname => 'AMS_NEW_STAT_ID'
);
|| l_update_status);
PV_PARTNER_PROGRAM_PVT.Update_Partner_Program (
p_api_version_number=> l_api_version,
p_init_msg_list=> fnd_api.g_false,
--p_commit => FND_API.G_FALSE,
--p_validation_level => FND_API.g_valid_level_full,
x_return_status=> l_return_status,
x_msg_count=> l_msg_count,
x_msg_data=> l_msg_data,
p_ptr_prgm_rec=> l_program_rec
);
update pv_partner_program_b set PROGRAM_STATUS_CODE='PENDING_APPROVAL',
USER_STATUS_ID=l_user_status_for_pa,
object_version_number=l_object_version_number+1
where
program_id=child_cur.program_id;
END update_parprogram_status;
SELECT 'X'
FROM pv_ge_temp_Approvers
WHERE approval_status_code IN ( 'APPROVED','REJECTED', 'PEER_RESPONDED','APPROVER_CHANGED')
AND entity_approver_id=p_obj_id
AND approver_id=p_appr_id
AND arc_appr_for_entity_code='ENRQ';
/**SELECT partner.party_name
,ppvl.program_name
,fl.description enrollment_type
FROM pv_partner_program_vl ppvl
,fnd_lookups fl
,pv_pg_enrl_requests pper
,pv_partner_profiles ppp
,hz_parties PARTNER
WHERE pper.partner_id = ppp.partner_id
AND ppp.partner_id=PARTNER.party_id
AND fl.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
AND fl.lookup_code = pper.enrollment_type_code
AND pper.program_id = ppvl.program_id
AND pper.enrl_request_id =cv_enrl_request_id;
SELECT partner.party_name
,ppvl.program_name
,fl.description enrollment_type
FROM pv_partner_program_vl ppvl
,fnd_lookups fl
,pv_pg_enrl_requests pper
,pv_partner_profiles ppp
,hz_parties PARTNER
WHERE pper.partner_id = ppp.partner_id
AND ppp.partner_party_id=PARTNER.party_id
AND fl.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
AND fl.lookup_code = pper.enrollment_type_code
AND pper.program_id = ppvl.program_id
AND pper.enrl_request_id =cv_enrl_id;
SELECT object_version_number,approver_id
FROM pv_ge_temp_approvers
WHERE entity_approver_id=p_entity_appr_id;
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_approver_rec => l_approver_rec
);
SELECT partner_id,program_id,enrollment_type_code,tentative_start_date,tentative_end_date
FROM pv_pg_enrl_requests
WHERE enrl_request_id=p_enrl_req_id;
SELECT object_version_number,entity_approver_id,approver_id,approver_type_code
FROM pv_ge_temp_approvers
WHERE appr_for_entity_id=p_enrl_req_id;
SELECT enrq.object_version_number,enrollment_type_code,order_header_id,partner_id,program_name, enrq.program_id
FROM pv_pg_enrl_requests enrq
, pv_partner_program_vl prgm
WHERE enrq.enrl_request_id=p_enrl_req_id
AND enrq.program_id=prgm.program_id;
SELECT object_version_number
FROM pv_pg_memberships
WHERE membership_id=p_membership_id;
SELECT prev_membership_id
FROM pv_pg_enrq_init_sources
WHERE enrl_request_id=p_enrl_req_id;
SELECT 'X' from dual where exists( SELECT distinct(entity_attr_id)
FROM pv_ge_qsnr_elements_b
WHERE arc_used_by_entity_code='PRGM'
AND used_by_entity_id in
( SELECT program_id
FROM pv_partner_program_b
START WITH program_id =p_program_id
CONNECT BY program_id = prior program_parent_id
)
);
SELECT 'X' from dual where exists( SELECT distinct(attr.attribute_id)
FROM pv_ge_qsnr_elements_b qsnr, pv_entity_attrs attr
WHERE arc_used_by_entity_code='PRGM'
AND attr.ENTITY_ATTR_ID=qsnr.entity_attr_id
AND used_by_entity_id in
( SELECT program_id
FROM pv_partner_program_b
START WITH program_id =p_program_id
CONNECT BY program_id = prior program_parent_id
)
);
SELECT distinct(attr.attribute_id)
FROM pv_ge_qsnr_elements_b qsnr, pv_entity_attrs attr
WHERE arc_used_by_entity_code='PRGM'
AND attr.ENTITY_ATTR_ID=qsnr.entity_attr_id
AND used_by_entity_id in
( SELECT program_id
FROM pv_partner_program_b
START WITH program_id =p_program_id
CONNECT BY program_id = prior program_parent_id
);
SELECT distinct(entity_attr_id)
FROM pv_ge_qsnr_elements_b
WHERE arc_used_by_entity_code='PRGM'
AND used_by_entity_id in
( SELECT program_id
FROM pv_partner_program_b
START WITH program_id =p_program_id
CONNECT BY program_id = prior program_parent_id
);
SELECT entity_approver_id,object_version_number
FROM pv_ge_temp_approvers
WHERE APPR_FOR_ENTITY_ID =enrl_id
AND APPROVAL_STATUS_CODE IN ('PENDING_APPROVAL','PENDING_DEFAULT');
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr(
p_api_version_number => 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
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_approver_rec =>l_approver_rec
);
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests(
p_api_version_number => 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
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_enrl_request_rec => l_enrq_rec );
PV_Pg_Memberships_PVT.Update_pg_memberships(
p_api_version_number => 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
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_memb_rec => pv_pg_prev_memb_rec
);
PV_Pg_Memberships_PVT.Update_Pg_memberships(
p_api_version_number => 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
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_memb_rec => pv_pg_prev_memb_rec
);
PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests(
p_api_version_number => 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
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_enrl_request_rec => l_enrq_rec
);
SELECT object_version_number,entity_approver_id,approver_id,approver_type_code
FROM pv_ge_temp_approvers
WHERE appr_for_entity_id=p_enrl_req_id
AND approver_id = DECODE( APPROVER_TYPE_CODE,'PERSON',p_person_id,'USER',p_user_id,null )
AND arc_appr_for_entity_code='ENRQ';
SELECT res.resource_id,res.resource_name
FROM jtf_rs_resource_extns_vl res,fnd_user fnd
WHERE fnd.employee_id = p_appr_id
AND res.user_id=fnd.user_id;
SELECT res.resource_id,res.resource_name
FROM jtf_rs_resource_extns_vl res,fnd_user fnd
WHERE fnd.user_id = p_appr_id
AND res.user_id=fnd.user_id;
SELECT partner_id
FROM pv_pg_enrl_requests
WHERE enrl_request_id=enrl_id;
SELECT entity_approver_id,object_version_number
FROM pv_ge_temp_approvers
WHERE APPR_FOR_ENTITY_ID =enrl_id
AND APPROVAL_STATUS_CODE IN ('PENDING_APPROVAL','PENDING_DEFAULT');
SELECT person_id ,full_name
FROM per_all_people_f per
, FND_USER usr
WHERE user_id=p_user_id
AND usr.person_party_id=per.party_id;
SELECT 'Y'
FROM fnd_user usr
, jtf_rs_resource_extns_vl res
WHERE usr.user_id=p_usr_id
AND usr.user_id=res.user_id
AND res.category='PARTY';
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_rec_appr
);
hence their approval status should be updated
*/
FOR x in pending_appovers_csr(enrl_request_id) LOOP
l_rec_appr.entity_approver_id :=x.entity_approver_id;
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_rec_appr
);
was sent to default approver, OAM does not record that and we cannot update OAM in that case.
We store the information in Pv_Ge_Temp_Approvers table that the request is awating approval
from the default approver and so the approval status code is PENDING_DEFAULT
*/
l_default_approver_id:= isnumber( FND_PROFILE.VALUE('PV_ENRQ_DEFAULT_APPR') );
hence their approval status should be updated
*/
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
hence their approval status should be updated
*/
FOR x in pending_appovers_csr(enrl_request_id) LOOP
l_rec_appr.entity_approver_id :=x.entity_approver_id;
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_rec_appr
);
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_rec_appr
);
hence their approval status should be updated
*/
FOR x in pending_appovers_csr(enrl_request_id) LOOP
l_rec_appr.entity_approver_id :=x.entity_approver_id;
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_rec_appr
);
/* We need to update temp approvers table record with approved status
for the logged in user for this enrollment request. The approval status
for the logged in user can be only approved in this scenario.
*/
l_approverPersonId:= FND_GLOBAL.EMPLOYEE_ID;
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_rec_appr
);
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
approvers has any record with 'PENDING_APPROVAL' status, then it needs to be updated
with status 'PEER_RESPONDED'
*/
IF x_nextApproversOut.COUNT>0 and x_approvalProcessCompleteYNOut=ame_util2.notCompleted THEN
l_approver_rec:=NULL;
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
PROCEDURE update_enrl_req_status
(
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
, enrl_request_id IN NUMBER
, entity_code IN VARCHAR2
, approvalStatus IN VARCHAR2
, start_date IN DATE
, end_date IN DATE
, 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_enrl_req_status';
SELECT object_version_number,entity_approver_id,approver_id,approver_type_code
FROM pv_ge_temp_approvers
WHERE appr_for_entity_id=p_enrl_req_id
AND APPROVER_ID = DECODE( APPROVER_TYPE_CODE,'PERSON',p_person_id,'USER',p_user_id,null )
AND ARC_APPR_FOR_ENTITY_CODE='ENRQ';
SELECT object_version_number, partner_id
FROM pv_pg_enrl_requests
WHERE enrl_request_id=p_enrl_req_id;
SELECT res.resource_id,res.resource_name
FROM jtf_rs_resource_extns_vl res,fnd_user fnd
WHERE fnd.employee_id = p_appr_id
AND res.user_id=fnd.user_id;
SELECT res.resource_id,res.resource_name
FROM jtf_rs_resource_extns_vl res,fnd_user fnd
WHERE fnd.user_id = p_appr_id
AND res.user_id=fnd.user_id;
SELECT person_id ,full_name
FROM per_all_people_f per
, FND_USER usr
WHERE user_id=p_user_id
AND usr.person_party_id=per.party_id;
SAVEPOINT update_enrl_req_status;
PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_enrl_request_rec => l_enrq_rec
);
ame_api2.updateApprovalStatus2
( applicationIdIn => 691
, transactionTypeIn => 'ENRQ'
, transactionIdIn => enrl_request_id
, approvalStatusIn => l_approvalStatus
, approverNameIn => x_role_name
, itemClassIn => null
, itemIdIn => null
, actionTypeIdIn => null
, groupOrChainIdIn => null
, occurrenceIn => null
, forwardeeIn => null
, updateItemIn => null
);
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
Pv_Ge_Temp_Approvers_PVT.Update_Ptr_Enr_Temp_Appr
( p_api_version_number => 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
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_approver_rec =>l_approver_rec
);
ROLLBACK TO update_enrl_req_status;
ROLLBACK TO update_enrl_req_status;
ROLLBACK TO update_enrl_req_status;
END update_enrl_req_status;
SELECT appr.appr_for_entity_id, appr.arc_appr_for_entity_code
FROM pv_ge_temp_approvers appr
WHERE appr.approval_status_code = 'ERROR'
AND appr.arc_appr_for_entity_code = 'ENRQ';
/* Logic to update the membership status to EXPIRE for all the EXPIRED members */
FOR l_get_errored_requests_rec IN c_get_errored_requests LOOP
l_enrl_request_id := l_get_errored_requests_rec.appr_for_entity_id;
SELECT memb.object_version_number,memb.partner_id,memb.original_end_date,enrq.custom_setup_id
FROM pv_pg_memberships memb,pv_pg_enrl_requests enrq
WHERE memb.membership_id=p_memb_id
AND memb.enrl_request_id=enrq.enrl_request_id;
SELECT program_name
FROM pv_partner_program_tl
WHERE program_id=p_prgm_id;
PV_Pg_Memberships_PVT.Update_Pg_Memberships
( p_api_version_number => 1.0
,p_init_msg_list => Fnd_Api.g_false
,p_commit => Fnd_Api.g_false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_memb_rec => l_pv_pg_memb_rec
);
SELECT 'Y'
FROM dual
WHERE EXISTS
(
SELECT user_id
FROM pv_partner_primary_users_v
WHERE partner_id = ptr_id
);
SELECT program_name
FROM pv_partner_program_vl
WHERE program_id=prgm_id;
SELECT invite.invited_by_partner_id
FROM pv_pg_invite_headers_b invite
, pv_pg_enrl_requests enrq
WHERE enrq.enrl_request_id=enrl_id
AND enrq.invite_header_id=invite.invite_header_id;
SELECT enty.attr_value
FROM pv_enty_attr_values enty
WHERE enty.entity = 'PARTNER'
AND enty.entity_id = ptr_id
AND enty.attribute_id = 6
AND enty.latest_flag = 'Y';
SELECT global_prof.partner_id
FROM pv_partner_profiles global_prof
, pv_partner_profiles subs_prof
, hz_relationships rel
WHERE subs_prof.partner_party_id=rel.subject_id
AND rel.relationship_code = 'SUBSIDIARY_OF'
AND rel.relationship_type = 'PARTNER_HIERARCHY'
AND rel.status = 'A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE
AND subs_prof.partner_id=p_subs_partner_id
AND rel.object_id=global_prof.partner_party_id;