[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT enrl_req.creation_date,
prog.program_id,
prog.program_name,
prog.membership_valid_period ||' ' || lookup1.meaning,
lookup2.meaning,
enrl_req.Requestor_resource_id,
enrl_req.partner_id
FROM pv_pg_enrl_requests enrl_req,
pv_partner_program_vl prog,
fnd_lookups lookup1,
fnd_lookups lookup2
WHERE enrl_req.enrl_request_id = cv_enrl_request_id
AND enrl_req.program_id = prog.program_id
AND lookup1.lookup_type='PV_PROGRAM_PMNT_UNIT'
AND lookup1.lookup_code = prog.membership_period_unit
AND lookup2.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
AND lookup2.lookup_code = enrl_req.enrollment_type_code;
SELECT user_id, source_name, user_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = cv_req_resource_id;
SELECT vendor.party_id VENDOR_ID,
vendor.party_name VENDOR_NAME,
partner.party_id PARTNER_ID,
partner.party_name PARTNER_NAME
FROM pv_partner_profiles prtnr_profile,
hz_relationships rel_ship,
hz_parties partner,
hz_parties vendor
WHERE prtnr_profile.partner_id =cv_partner_id
AND prtnr_profile.partner_id = rel_ship.party_id
AND prtnr_profile.partner_party_id = rel_ship.object_id
AND rel_ship.party_id = cv_partner_id
AND rel_ship.subject_id = vendor.party_id
AND rel_ship.object_id = partner.PARTY_ID;
SELECT vendor.party_id VENDOR_ID,
vendor.party_name VENDOR_NAME,
partner.party_id PARTNER_ID,
partner.party_name PARTNER_NAME
FROM pv_pg_enrl_requests enrl_req,
pv_partner_profiles prtnr_profile,
hz_relationships rel_ship,
hz_parties partner,
hz_parties vendor
WHERE enrl_req.enrl_request_id = cv_enrl_request_id
AND enrl_req.partner_id= prtnr_profile.partner_id
AND prtnr_profile.partner_id = rel_ship.party_id
AND prtnr_profile.partner_party_id = rel_ship.object_id
AND enrl_req.partner_id = rel_ship.party_id
AND rel_ship.subject_id = vendor.party_id
AND rel_ship.object_id = partner.PARTY_ID;
SELECT enrl_req.creation_date ,
program.program_id ,
program.program_name ,
membership.enrl_request_id ,
membership.start_date ,
membership.original_end_date ,
enrl_req.requestor_resource_id,
enrl_req.partner_id,
lookup.meaning
FROM pv_pg_memberships membership,
pv_pg_enrl_requests enrl_req,
pv_partner_program_vl program,
fnd_lookups lookup
WHERE membership.membership_id = cv_membership_id
AND enrl_req.enrl_request_id = membership.enrl_request_id
AND membership.program_id = program.program_id
AND lookup.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
AND lookup.lookup_code = enrl_req.enrollment_type_code;
SELECT user_id,
user_name,
resource_id
FROM pv_partner_primary_users_v
WHERE partner_id = cv_partner_id ;
SELECT source_id, user_id, category
FROM JTF_RS_RESOURCE_EXTNS_VL
WHERE resource_id > 0
AND ( category = 'EMPLOYEE'
OR category = 'PARTNER'
OR category = 'PARTY')
AND resource_id = p_resource_id ;
SELECT meaning
FROM FND_LOOKUPS
WHERE lookup_type= 'PV_NOTIFICATION_TYPE'
AND lookup_code= p_notif_code;
SELECT fnd_web_config.jsp_agent FROM dual;
SELECT web_html_call
FROM fnd_form_functions
WHERE function_name = 'PV_ENRL_NOW';
SELECT nvl(citem_version_id ,0)
FROM pv_partner_program_b
WHERE program_id = cv_program_id;
SELECT active_flag
FROM pv_ge_notif_rules_vl
WHERE notif_type_code = p_notif_type
AND arc_notif_for_entity_code = 'PRGM'
AND notif_for_entity_id = p_program_id ;
SELECT enrl_req.creation_date
, prog.program_id
, prog.program_name
, prog.membership_valid_period ||' ' || lookup1.meaning
, lookup2.meaning
, enrl_req.Requestor_resource_id
, enrl_req.partner_id
, memb.start_date
, memb.ORIGINAL_END_DATE
, memb.membership_id
FROM pv_pg_enrl_requests enrl_req
, pv_partner_program_vl prog
, fnd_lookups lookup1
, fnd_lookups lookup2
, pv_pg_memberships memb
WHERE enrl_req.enrl_request_id = cv_enrl_request_id
AND enrl_req.program_id = prog.program_id
AND lookup1.lookup_type='PV_PROGRAM_PMNT_UNIT'
AND lookup1.lookup_code = prog.membership_period_unit
AND lookup2.lookup_type='PV_ENROLLMENT_REQUEST_TYPE'
AND lookup2.lookup_code = enrl_req.enrollment_type_code
AND enrl_req.enrl_request_id=memb.enrl_request_id(+);
/* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
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_pgp_notif_rec => l_pgp_notif_rec,
x_party_notification_id => x_party_notification_id );
/* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
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_pgp_notif_rec => l_pgp_notif_rec,
x_party_notification_id => x_party_notification_id );
/* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
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_pgp_notif_rec => l_pgp_notif_rec,
x_party_notification_id => x_party_notification_id );
/* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
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_pgp_notif_rec => l_pgp_notif_rec,
x_party_notification_id => x_party_notification_id );
/* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
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_pgp_notif_rec => l_pgp_notif_rec,
x_party_notification_id => x_party_notification_id );
/* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATION */
PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
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_pgp_notif_rec => l_pgp_notif_rec,
x_party_notification_id => x_party_notification_id );
/* Select the proper hitory category code based on the Notification Type.*/
IF (p_mbrship_chng_rec.NOTIF_TYPE = 'PG_INVITE') THEN
l_history_category_code := 'INVITE' ;
SELECT 'Y'
FROM pv_pg_memberships mmbr
WHERE mmbr.membership_id = cv_membership_id
AND NOT EXISTS
( SELECT 1
FROM pv_pg_mmbr_transitions trans,
pv_pg_memberships mmbr_future
WHERE trans.from_membership_id = mmbr.membership_id
AND trans.to_membership_id = mmbr_future.membership_id
AND mmbr_future.membership_status_code = 'FUTURE' );
SELECT 'Y'
FROM pv_pg_enrl_requests enrq
WHERE enrq.enrl_request_id = cv_enrl_request_id
AND enrq.contract_status_code = 'AWAITING_FAX_OR_MAIL';
SELECT mmbr.membership_id,
mmbr.enrl_request_id,
mmbr.partner_id,
(notif_rule.repeat_freq_value * DECODE(notif_rule.repeat_freq_unit, 'PV_DAYS',1,'PV_WEEK', 7,'PV_MONTH', 30)) wait_time_in_days
FROM pv_pg_memberships mmbr,
pv_ge_notif_rules_b notif_rule
WHERE mmbr.membership_status_code = 'ACTIVE'
AND trunc(mmbr.original_end_date - sysdate) < notif_rule.send_notif_before_value *
DECODE(notif_rule.send_notif_before_unit, 'PV_DAYS',1,'PV_WEEK', 7,
'PV_MONTH', 30)
AND mmbr.program_id = notif_rule.notif_for_entity_id
AND notif_rule.arc_notif_for_entity_code = 'PRGM'
AND notif_rule.active_flag = 'Y'
AND notif_rule.notif_type_code = 'PG_MEM_EXP'
AND NOT EXISTS
( SELECT 1
FROM pv_pg_mmbr_transitions trans,
pv_pg_memberships mmbr_future
WHERE trans.from_membership_id = mmbr.membership_id
AND trans.to_membership_id = mmbr_future.membership_id
AND mmbr_future.membership_status_code = 'FUTURE'
)
AND NOT EXISTS
( SELECT 1
FROM pv_ge_party_notifications sent_notif
WHERE sent_notif.ARC_NOTIF_FOR_ENTITY_CODE = 'ENRQ'
AND sent_notif.NOTIF_FOR_ENTITY_ID = mmbr.enrl_request_id
AND sent_notif.notif_type_code = notif_rule.notif_type_code
AND sent_notif.partner_id = mmbr.partner_id
);
SELECT enrq.enrl_request_id,
enrq.partner_id,
notif_rule.repeat_freq_value*
DECODE(notif_rule.repeat_freq_unit, 'PV_DAYS',1,
'PV_WEEK', 7,
'PV_MONTH', 30) "wait_time_in_days"
FROM pv_pg_enrl_requests enrq,
pv_ge_notif_rules_b notif_rule
WHERE enrq.contract_status_code = 'AWAITING_FAX_OR_MAIL'
AND enrq.request_status_code in ('AWAITING_APPROVAL', 'APPROVED')
AND (enrq.request_submission_date -sysdate) < notif_rule.send_notif_after_value *
DECODE(notif_rule.send_notif_after_unit, 'PV_DAYS',1,
'PV_WEEK', 7,
'PV_MONTH', 30)
AND enrq.program_id = notif_rule.notif_for_entity_id
AND notif_rule.arc_notif_for_entity_code = 'PRGM'
AND notif_rule.active_flag = 'Y'
AND notif_rule.notif_type_code = 'PG_CONTRCT_NRCVD'
AND NOT EXISTS
( SELECT 1
FROM pv_ge_party_notifications sent_notif
WHERE sent_notif.ARC_NOTIF_FOR_ENTITY_CODE = 'ENRQ'
AND sent_notif. NOTIF_FOR_ENTITY_ID = enrq.enrl_request_id
AND sent_notif.notif_type_code = notif_rule.notif_type_code
AND sent_notif.partner_id = enrq.partner_id);
/* Process all the Membership expiry records selected in c_get_memberships Cursor */
OPEN c_get_memberships;
SELECT mmbr.membership_id membership_id
, mmbr.partner_id
, mmbr.object_version_number
FROM pv_pg_memberships mmbr
WHERE mmbr.membership_status_code = 'ACTIVE'
AND trunc(SYSDATE - mmbr.original_end_date) >= 1
AND NOT EXISTS
( SELECT 1
FROM pv_pg_mmbr_transitions trans,
pv_pg_memberships mmbr_future
WHERE trans.from_membership_id = mmbr.membership_id
AND trans.to_membership_id = mmbr_future.membership_id
AND mmbr_future.membership_status_code = 'FUTURE' ) ;
SELECT membership_status_code
FROM pv_pg_memberships
WHERE membership_id=mmbr_id;
SELECT mmbr.membership_id current_membership_id,
mmbr_future.membership_id future_membership_id,
mmbr.partner_id,
mmbr.object_version_number,
mmbr_future.object_version_number future_memb_obj_ver_no
FROM pv_pg_memberships mmbr,
pv_pg_memberships mmbr_future,
pv_pg_mmbr_transitions trans
WHERE mmbr.membership_status_code = 'ACTIVE'
--AND trunc(SYSDATE - mmbr.original_end_date) >= 1
AND mmbr.original_end_date <= trunc(SYSDATE -1 )
AND trans.from_membership_id = mmbr.membership_id
AND trans.to_membership_id = mmbr_future.membership_id
AND mmbr_future.membership_status_code = 'FUTURE';
SELECT distinct party.party_name, prgm.program_name
FROM hz_parties party
, pv_partner_profiles prof
, pv_pg_memberships memb
, pv_partner_program_vl prgm
WHERE prof.status = 'A'
AND prof.partner_party_id = party.party_id
AND memb.partner_id = prof.partner_id
AND memb.membership_id = memb_id
AND memb.program_id=prgm.program_id;
/* Logic to update the membership status to EXPIRE for all the EXPIRED members */
Write_log (1, 'Updating the Membership Status to EXPIRED -');
/* call update table handler for pv_pg_memberships by passing
membership_status_code = 'EXPIRED', actual_end_date as sysdate */
/*l_memb_rec.membership_id := l_get_expire_memberships_rec.membership_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,
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 => l_memb_rec );
PV_Pg_Memberships_PVT.Update_Pg_Memberships(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
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 => l_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 => l_memb_rec );
/* Call the Create_Ge_Party_Notif to insert a record in PV_GE_PARTY_NOTIFICATIONS */
PV_GE_PARTY_NOTIF_PVT.Create_Ge_Party_Notif (
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_pgp_notif_rec => l_pgp_notif_rec,
x_party_notification_id => x_party_notifid
);
SELECT program_name
FROM pv_partner_program_vl
WHERE program_id=prgm_id;
SELECT prgm.program_name
FROM PV_PG_INVITE_HEADERS_b inv
, pv_partner_program_vl prgm
WHERE inv.invite_header_id=inv_hdr_id
AND inv.invite_for_program_id =prgm.program_id;
SELECT PV_LEAD_WORKFLOWS_S.nextval
INTO l_val
FROM dual;
SELECT program_name
FROM pv_partner_program_vl
WHERE program_id=prgm_id;
SELECT prgm.program_name
FROM PV_PG_INVITE_HEADERS_b inv
, pv_partner_program_vl prgm
WHERE inv.invite_header_id=inv_hdr_id
AND inv.invite_for_program_id =prgm.program_id;
SELECT name
FROM fnd_currencies_vl
WHERE currency_code = currencyCode;
SELECT PV_LEAD_WORKFLOWS_S.nextval
INTO l_val
FROM dual;