[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM PV_ATTRIBUTE_CODES_VL
WHERE ATTRIBUTE_ID = 6
AND ENABLED_FLAG = 'Y'
AND ATTR_CODE =attr_cd;
SELECT pv_pg_memberships_s.NEXTVAL
FROM dual;
SELECT 1
FROM PV_PG_MEMBERSHIPS
WHERE membership_id = l_id;
Pv_Pg_Memberships_Pkg.Insert_Row(
px_membership_id => l_membership_id,
px_object_version_number => l_object_version_number,
p_partner_id => p_memb_rec.partner_id,
p_program_id => p_memb_rec.program_id,
p_start_date => p_memb_rec.start_date,
p_original_end_date => p_memb_rec.original_end_date,
p_actual_end_date => p_memb_rec.actual_end_date,
p_membership_status_code => p_memb_rec.membership_status_code,
p_status_reason_code => p_memb_rec.status_reason_code,
p_enrl_request_id => p_memb_rec.enrl_request_id,
p_created_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_date => SYSDATE,
p_last_update_login => FND_GLOBAL.conc_login_id,
p_attribute1 => p_memb_rec.attribute1,
p_attribute2 => p_memb_rec.attribute2,
p_attribute3 => p_memb_rec.attribute3,
p_attribute4 => p_memb_rec.attribute4,
p_attribute5 => p_memb_rec.attribute5,
p_attribute6 => p_memb_rec.attribute6,
p_attribute7 => p_memb_rec.attribute7,
p_attribute8 => p_memb_rec.attribute8,
p_attribute9 => p_memb_rec.attribute9,
p_attribute10 => p_memb_rec.attribute10,
p_attribute11 => p_memb_rec.attribute11,
p_attribute12 => p_memb_rec.attribute12,
p_attribute13 => p_memb_rec.attribute13,
p_attribute14 => p_memb_rec.attribute14,
p_attribute15 => p_memb_rec.attribute15
);
PROCEDURE Update_Pg_Memberships(
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_memb_rec IN memb_rec_type
)
IS
CURSOR c_get_pg_memberships(membership_id NUMBER) IS
SELECT *
FROM PV_PG_MEMBERSHIPS
WHERE membership_id = p_memb_rec.membership_id;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Pg_Memberships';
SAVEPOINT update_pg_memberships_pvt;
PVX_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
PVX_UTILITY_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
p_token_name => 'INFO',
p_token_value => 'Pg_Memberships') ;
p_token_value => 'Last_Update_Date') ;
p_validation_mode => JTF_PLSQL_API.g_update,
p_memb_rec => p_memb_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PVX_UTILITY_PVT.debug_message('Private API: Calling update table handler');
Pv_Pg_Memberships_Pkg.Update_Row(
p_membership_id => p_memb_rec.membership_id,
p_object_version_number => p_memb_rec.object_version_number,
p_partner_id => p_memb_rec.partner_id,
p_program_id => p_memb_rec.program_id,
p_start_date => p_memb_rec.start_date,
p_original_end_date => p_memb_rec.original_end_date,
p_actual_end_date => p_memb_rec.actual_end_date,
p_membership_status_code => p_memb_rec.membership_status_code,
p_status_reason_code => p_memb_rec.status_reason_code,
p_enrl_request_id => p_memb_rec.enrl_request_id,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_date => SYSDATE,
p_last_update_login => FND_GLOBAL.conc_login_id,
p_attribute1 => p_memb_rec.attribute1,
p_attribute2 => p_memb_rec.attribute2,
p_attribute3 => p_memb_rec.attribute3,
p_attribute4 => p_memb_rec.attribute4,
p_attribute5 => p_memb_rec.attribute5,
p_attribute6 => p_memb_rec.attribute6,
p_attribute7 => p_memb_rec.attribute7,
p_attribute8 => p_memb_rec.attribute8,
p_attribute9 => p_memb_rec.attribute9,
p_attribute10 => p_memb_rec.attribute10,
p_attribute11 => p_memb_rec.attribute11,
p_attribute12 => p_memb_rec.attribute12,
p_attribute13 => p_memb_rec.attribute13,
p_attribute14 => p_memb_rec.attribute14,
p_attribute15 => p_memb_rec.attribute15
);
ROLLBACK TO UPDATE_Pg_Memberships_PVT;
ROLLBACK TO UPDATE_Pg_Memberships_PVT;
ROLLBACK TO UPDATE_Pg_Memberships_PVT;
End Update_Pg_Memberships;
PROCEDURE Delete_Pg_Memberships(
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_membership_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Pg_Memberships';
SAVEPOINT delete_pg_memberships_pvt;
Pv_Pg_Memberships_Pkg.Delete_Row(
p_membership_id => p_membership_id,
p_object_version_number => p_object_version_number );
ROLLBACK TO DELETE_Pg_Memberships_PVT;
ROLLBACK TO DELETE_Pg_Memberships_PVT;
ROLLBACK TO DELETE_Pg_Memberships_PVT;
End Delete_Pg_Memberships;
SELECT *
FROM pv_pg_memberships
WHERE membership_id = p_memb_rec.membership_id;
IF p_memb_rec.last_updated_by IS NULL THEN
x_complete_rec.last_updated_by := l_memb_rec.last_updated_by;
IF p_memb_rec.last_update_date IS NULL THEN
x_complete_rec.last_update_date := l_memb_rec.last_update_date;
IF p_memb_rec.last_update_login IS NULL THEN
x_complete_rec.last_update_login := l_memb_rec.last_update_login;
SELECT status
FROM pv_partner_profiles
WHERE partner_id=p_ptr_id;
SELECT enrq.enrl_request_id
, enrq.partner_id
, enrq.object_version_number
, prgm.program_name
FROM pv_pg_enrl_requests enrq
, pv_partner_program_vl prgm
WHERE enrq.enrl_request_id=enrl_id
AND enrq.program_id=prgm.program_id;
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
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_enrl_request_rec => l_enrl_request_rec
);
SELECT status
FROM pv_partner_profiles
WHERE partner_id=p_ptr_id;
SELECT contract_id
FROM pv_pg_enrl_requests enrq, pv_pg_memberships memb
WHERE memb.membership_id=mmbr_id
AND memb.enrl_request_id=enrq.enrl_request_id;
SELECT memb.partner_id
, memb.program_id
, memb.object_version_number
, memb.enrl_request_id
, prgm.program_name
FROM pv_pg_memberships memb
, pv_partner_program_vl prgm
WHERE memb.membership_id=mmbr_id
AND memb.program_id=prgm.program_id;
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 membership_id,program_id
FROM pv_pg_memberships
WHERE partner_id=p_ptr_id
AND membership_status_code IN ('ACTIVE','FUTURE');
SELECT enrl_request_id
FROM pv_pg_enrl_requests
WHERE partner_id=p_ptr_id
AND request_status_code in ('INCOMPLETE','AWAITING_APPROVAL');
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 subs_prof.partner_id
FROM pv_partner_profiles subs_prof
, pv_partner_profiles global_prof
, pv_enty_attr_values subs_enty_val
, hz_relationships rel
WHERE global_prof.partner_id = g_ptr_id
AND global_prof.partner_party_id = rel.subject_id
AND rel.relationship_type = 'PARTNER_HIERARCHY'
AND rel.relationship_code = 'PARENT_OF'
AND rel.status = 'A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE
AND rel.object_id = subs_prof.partner_party_id
AND subs_enty_val.entity_id = subs_prof.partner_id
AND subs_enty_val.entity = 'PARTNER'
AND subs_enty_val.attribute_id = 6
AND subs_enty_val.latest_flag = 'Y'
AND subs_enty_val.attr_value = 'SUBSIDIARY';
SELECT /*+ CARDINALITY(sptr 10) */
memb.membership_id membership_id
, memb.program_id program_id
FROM pv_pg_memberships memb
, (SELECT column_value FROM TABLE (CAST(l_sub_str_table AS JTF_NUMBER_TABLE))) sptr
WHERE memb.partner_id=sptr.column_value
AND memb.membership_status_code IN ('ACTIVE','FUTURE');
SELECT /*+ CARDINALITY(sptr 10) */ enrq.enrl_request_id enrl_request_id
FROM pv_pg_enrl_requests enrq
, (SELECT column_value FROM TABLE (CAST(l_sub_str_table AS JTF_NUMBER_TABLE))) sptr
WHERE enrq.partner_id=sptr.column_value
AND request_status_code IN ('INCOMPLETE','AWAITING_APPROVAL');
l_enrl_id_tbl.delete();
l_memb_id_tbl.delete();
SELECT * FROM TABLE (CAST(p_ids AS JTF_NUMBER_TABLE))
GROUP BY column_value )
LOOP
l_unique_id_tbl.extend(1);
SELECT DISTINCT(change_to_program_id)
FROM pv_pg_enrl_change_rules
WHERE change_direction_code='PREREQUISITE'
AND ACTIVE_FLAG='Y'
START WITH change_from_program_id=p_prgm_id
CONNECT BY change_from_program_id=PRIOR change_to_program_id
AND PRIOR CHANGE_TO_PROGRAM_ID<>CHANGE_FROM_PROGRAM_ID;
SELECT memb.program_id program_id,trn.lvl actlevel
FROM pv_pg_memberships memb
,pv_partner_program_b pvpp,
(
SELECT from_membership_id,min(level) lvl
FROM pv_pg_mmbr_transitions
START WITH to_membership_id=to_mmbr_id
CONNECT BY to_membership_id=prior from_membership_id
GROUP BY from_membership_id,level
) trn
WHERE GLOBAL_MMBR_REQD_FLAG = 'Y'
AND pvpp.program_id=memb.program_id
AND memb.membership_id=trn.from_membership_id
ORDER by actlevel;
SELECT memb.program_id
FROM pv_pg_memberships memb
,pv_partner_program_b pvpp
WHERE GLOBAL_MMBR_REQD_FLAG = 'Y'
AND pvpp.program_id=memb.program_id
AND memb.membership_id=to_mmbr_id;
l_dependent_program_id_tbl.delete();
/*we are not inserting data into member transitions table when its a new enrollment request
because there is no from membership.
so to find the dependent program , just query for the GLOBAL_MMBR_REQD_FLAG for the program
of the terminating membership
*/
OPEN prg_csr ( p_membership_id ) ;
SELECT enr.enrl_request_id
, enr.request_status_code
, memb.membership_status_code
, memb.membership_id
FROM pv_pg_enrl_requests enr
, pv_pg_memberships memb
WHERE enr.partner_id = ptr_id
AND enr.program_id
IN ( SELECT * FROM TABLE ( CAST( prgm_id_tbl AS JTF_NUMBER_TABLE ) ) )
AND enr.enrl_request_id = memb.enrl_request_id(+);
SELECT /*+ CARDINALITY(ptr 10) */
enr.enrl_request_id
, enr.request_status_code
, memb.membership_status_code
, memb.membership_id
FROM pv_pg_enrl_requests enr
, pv_pg_memberships memb
, ( SELECT column_value FROM TABLE ( CAST( ptr_id_tbl AS JTF_NUMBER_TABLE ) ) ) ptr
, ( SELECT column_value FROM TABLE ( CAST( prgm_id_tbl AS JTF_NUMBER_TABLE ) ) ) prg
WHERE enr.partner_id =ptr.column_value
AND enr.program_id =prg.column_value
AND enr.enrl_request_id = memb.enrl_request_id(+);
SELECT enr.enrl_request_id
, enr.request_status_code
, memb.membership_status_code
, memb.membership_id
FROM pv_pg_enrl_requests enr
, pv_pg_memberships memb
WHERE enr.partner_id
IN ( SELECT * FROM TABLE ( CAST( ptr_id_tbl AS JTF_NUMBER_TABLE ) ) )
AND enr.program_id
IN ( SELECT * FROM TABLE ( CAST( prgm_id_tbl AS JTF_NUMBER_TABLE ) ) )
AND enr.enrl_request_id = memb.enrl_request_id(+);
SELECT enrq.enrl_request_id
, enrq.request_status_code
, memb.membership_status_code
, memb.membership_id
, memb.partner_id
FROM pv_partner_profiles subs_prof
, pv_partner_profiles global_prof
, pv_enty_attr_values subs_enty_val
, hz_relationships rel
, pv_pg_memberships memb
, pv_pg_enrl_requests enrq
WHERE global_prof.partner_id = global_partner_id
AND global_prof.partner_party_id = rel.subject_id
AND rel.relationship_type = 'PARTNER_HIERARCHY'
AND rel.object_id = subs_prof.partner_party_id
AND rel.relationship_code = 'PARENT_OF'
AND rel.status = 'A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE
AND subs_enty_val.entity = 'PARTNER'
AND subs_enty_val.entity_id = subs_prof.partner_id
AND subs_enty_val.attribute_id = 6
AND subs_enty_val.latest_flag = 'Y'
AND subs_enty_val.attr_value = 'SUBSIDIARY'
AND subs_prof.partner_id = enrq.partner_id
AND enrq.enrl_request_id = memb.enrl_request_id(+)
AND enrq.dependent_program_id
IN ( SELECT * FROM TABLE ( CAST( p_depentent_id_tbl AS JTF_NUMBER_TABLE ) ) );
SELECT /*+ LEADING(t) */ DISTINCT( program_id )
FROM pv_pg_memberships memb
, (SELECT column_value FROM TABLE (CAST(memb_id_tbl AS JTF_NUMBER_TABLE))) t
WHERE t.column_value=memb.membership_id;
SELECT DISTINCT( program_id )
FROM pv_pg_memberships
WHERE membership_id
IN ( SELECT * FROM TABLE ( CAST( memb_id_tbl AS JTF_NUMBER_TABLE ) ) );
SELECT enty.attr_value
, memb.program_id
, memb.partner_id
FROM pv_pg_memberships memb
, pv_enty_attr_values enty
WHERE memb.membership_id = memb_id
AND memb.partner_id = enty.entity_id
AND enty.entity = 'PARTNER'
AND enty.entity_id = memb.partner_id
AND enty.attribute_id = 6
AND enty.latest_flag = 'Y';
SELECT program_id
, partner_id
FROM pv_pg_memberships
WHERE membership_id = memb_id;
l_membid_tbl.delete();
l_enrl_req_tbl.delete();
l_membid_tbl.delete();
l_enrl_req_tbl.delete();
l_membid_tbl.delete();
SELECT partner_id
, original_end_date
, enrl_request_id
, program_name
FROM pv_pg_memberships memb
, pv_partner_program_vl prgm
WHERE membership_id = p_memb_id
AND memb.program_id=prgm.program_id;
SELECT program_name
FROM pv_partner_program_vl
where program_id=p_progm_id ;
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;
PROCEDURE Update_membership_end_date
(
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_membership_id IN NUMBER -- membership_id for which end date needs to be updated
, p_new_date IN DATE
, p_comments IN VARCHAR2 DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
---CURSOR TO get the membertype and partner_id
CURSOR memb_type_csr(memb_id NUMBER) IS
SELECT memb.partner_id
, memb.object_version_number
, enty.attr_value
, memb.original_end_date
FROM pv_pg_memberships memb
, pv_enty_attr_values enty
WHERE memb.membership_id=memb_id
AND memb.partner_id=enty.entity_id
AND enty.entity = 'PARTNER'
AND enty.entity_id = memb.partner_id
AND enty.attribute_id = 6
AND enty.latest_flag = 'Y';
SELECT memb.membership_id
, memb.object_version_number
, memb.original_end_date
, memb.partner_id
, memb.program_id
FROM pv_partner_profiles subs_prof
, pv_partner_profiles global_prof
, pv_enty_attr_values subs_enty_val
, hz_relationships rel
, pv_pg_memberships memb
, pv_pg_enrl_requests enrl
WHERE global_prof.partner_id = global_partner_id
AND global_prof.partner_party_id = rel.subject_id
AND rel.relationship_type = 'PARTNER_HIERARCHY'
AND rel.object_id = subs_prof.partner_party_id
AND rel.relationship_code = 'PARENT_OF'
AND rel.status = 'A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE
AND subs_enty_val.entity = 'PARTNER'
AND subs_enty_val.entity_id = subs_prof.partner_id
AND subs_enty_val.attribute_id = 6
AND subs_enty_val.latest_flag = 'Y'
AND subs_enty_val.attr_value = 'SUBSIDIARY'
AND subs_prof.partner_id=memb.partner_id
AND memb.membership_status_code='ACTIVE'
AND memb.enrl_request_id=enrl.enrl_request_id
AND enrl.dependent_program_id
in ( SELECT * FROM TABLE ( CAST( p_depentent_id_tbl AS JTF_NUMBER_TABLE ) ) );
l_api_name CONSTANT VARCHAR2(30) := 'Update_membership_end_date';
SAVEPOINT Update_membership_end_date ;
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
);
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
);
ROLLBACK TO Update_membership_end_date;
ROLLBACK TO Update_membership_end_date;
ROLLBACK TO Update_membership_end_date;
END Update_membership_end_date;