[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(invite_header_id)
FROM PV_PG_INVITE_HEADERS_b
WHERE partner_id=p_ptr_id
AND nvl(invite_end_date,sysdate+1)>sysdate
AND invite_for_program_id =p_prgm_id
AND invite_type_code =p_inv_type_code;
Select 'X' from dual
WHERE EXISTS
(
select membership_id from pv_pg_memberships
where membership_status_code in ('ACTIVE','FUTURE')
AND partner_id=p_global_ptr_id
AND program_id IN
(
select CHANGE_to_program_id from pv_pg_enrl_change_rules
where change_direction_code='UPGRADE'
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 CHANGE_TO_PROGRAM_ID<>PRIOR CHANGE_FROM_PROGRAM_Id
union select p_prgm_id FROM DUAL
)
);
SELECT glob_prof.partner_id
FROM pv_partner_profiles subs_prof
,pv_partner_profiles glob_prof
,hz_relationships rel
WHERE rel.subject_id=subs_prof.partner_party_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_sub_ptr_id
AND REL.OBJECT_ID=glob_prof.partner_party_id;
SELECT pvppb.program_id program_id
,pvppb.program_name program_name
,pvppb.citem_version_id citem_version_id
,pvppb.global_mmbr_reqd_flag global_mmbr_reqd_flag
,pvppb.prereq_process_rule_id prereq_process_rule_id
,pvppb.no_fee_flag no_fee_flag
,pvppb.vad_invite_allow_flag vad_invite_allow_flag
FROM pv_partner_program_type_b pvpptb
,pv_partner_program_vl pvppb
WHERE pvppb.program_status_code = 'ACTIVE'
AND pvppb.program_level_code ='MEMBERSHIP'
AND pvppb.enabled_flag = 'Y'
AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
AND pvppb.program_type_id = pvpptb.program_type_ID
AND pvpptb.ACTIVE_FLAG = 'Y'
AND pvpptb.enabled_flag = 'Y'
-- AND inv.invite_type_code(+)='INVITE'
AND EXISTS
(
SELECT 'X' FROM pv_program_partner_types pvppt
WHERE pvppt.partner_type IN
(
SELECT attr_value
FROM pv_enty_attr_values pveav
WHERE pveav.enabled_flag = 'Y'
AND pveav.latest_flag = 'Y'
AND pveav.entity = 'PARTNER'
AND pveav.entity_id = ptnr_id
AND pveav.attribute_id = 3
)
AND pvpptb.program_type_id = pvppt.program_type_id
)
AND pvppb.program_id NOT IN
(
SELECT rules.change_to_program_id
FROM pv_pg_enrl_change_rules rules
WHERE change_direction_code = 'UPGRADE'
AND effective_from_date <= sysdate
AND nvl(effective_to_date, sysdate) >= sysdate
AND active_flag = 'Y'
)
/* AND EXISTS -- check for pre-populated cache for pre-req evaluation
( SELECT 1
FROM pv_pg_elig_programs elig
WHERE elig.program_id = pvppb.program_id
AND elig.partner_id = ptnr_id
) */
;
SELECT enrl_request_id,request_status_code,enrollment_type_code
FROM pv_pg_enrl_requests
WHERE enrl_request_id=
(
SELECT max(enrl_request_id)
FROM pv_pg_enrl_requests
WHERE partner_id=ptr_id
AND program_id=prgm_id
);
SELECT membership_id,membership_status_code
FROM pv_pg_memberships
WHERE enrl_request_id=enrl_id;
SELECT pvppb.program_id program_id
,pvppb.global_mmbr_reqd_flag global_mmbr_reqd_flag
,pvppb.prereq_process_rule_id prereq_process_rule_id
,pvppb.no_fee_flag no_fee_flag
,pvppb.vad_invite_allow_flag vad_invite_allow_flag
FROM pv_partner_program_type_b pvpptb ,pv_partner_program_vl pvppb
WHERE pvppb.program_status_code = 'ACTIVE'
AND pvppb.program_level_code ='MEMBERSHIP'
AND pvppb.enabled_flag = 'Y'
AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
AND pvppb.program_type_id = pvpptb.program_type_ID
AND pvpptb.ACTIVE_FLAG = 'Y'
AND pvpptb.enabled_flag = 'Y'
AND EXISTS
(
SELECT 'X' FROM pv_program_partner_types pvppt
WHERE pvppt.partner_type IN
(
SELECT attr_value
FROM pv_enty_attr_values pveav
WHERE pveav.enabled_flag = 'Y'
AND pveav.latest_flag = 'Y'
AND pveav.entity = 'PARTNER'
AND pveav.entity_id = ptnr_id
AND pveav.attribute_id = 3
)
AND pvpptb.program_type_id = pvppt.program_type_id
)
AND pvppb.program_id NOT IN
(
SELECT rules.change_to_program_id
FROM pv_pg_enrl_change_rules rules
WHERE change_direction_code = 'UPGRADE'
AND effective_from_date <= sysdate
AND nvl(effective_to_date, sysdate) >= sysdate
AND active_flag = 'Y'
);
l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
OPEN x_enrq_param_cur FOR SELECT * FROM TABLE( CAST (l_enrq_param_tbl AS PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
SELECT pvppb.program_id program_id
,pvppb.program_name program_name
,pvppb.citem_version_id citem_version_id
,pvppb.global_mmbr_reqd_flag global_mmbr_reqd_flag
,pvppb.prereq_process_rule_id prereq_process_rule_id
,pvppb.no_fee_flag no_fee_flag
,pvppb.vad_invite_allow_flag vad_invite_allow_flag
,memb.membership_id membership_id
,memb.membership_status_code membership_status_code
,memb.original_end_date original_end_date
-- ,inv.invite_header_id invite_header_id
FROM pv_partner_program_type_b pvpptb
,pv_partner_program_vl pvppb
,pv_pg_memberships memb
-- ,pv_pg_invite_headers_b inv
WHERE pvppb.program_status_code = 'ACTIVE'
AND pvppb.program_level_code ='MEMBERSHIP'
AND pvppb.enabled_flag = 'Y'
AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
AND pvppb.program_type_id = pvpptb.program_type_ID
AND pvpptb.ACTIVE_FLAG = 'Y'
AND pvpptb.enabled_flag = 'Y'
--AND inv.partner_id (+) = ptr_id
--AND inv.invite_for_program_id (+) = pvppb.program_id
-- AND NVL(inv.invite_end_date, sysdate+1) > sysdate
--AND inv.invite_type_code(+)='INVITE'
AND EXISTS
( SELECT 'X' FROM pv_program_partner_types pvppt
WHERE pvppt.partner_type IN
(
SELECT attr_value
FROM pv_enty_attr_values pveav
WHERE pveav.enabled_flag = 'Y'
AND pveav.latest_flag = 'Y'
AND pveav.entity = 'PARTNER'
AND pveav.entity_id = ptr_id
AND pveav.attribute_id = 3
)
AND pvpptb.program_type_id = pvppt.program_type_id
)
AND pvppb.program_id =memb.program_id
AND memb.membership_id =
( SELECT max(membership_id)
FROM PV_PG_MEMBERSHIPS
WHERE program_id=memb.program_id
AND partner_id=ptr_id
AND MEMBERSHIP_STATUS_CODE IN ('ACTIVE','EXPIRED')
)
/*AND EXISTS -- check for pre-populated cache for pre-req evaluation
( SELECT 1
FROM pv_pg_elig_programs elig
WHERE elig.program_id = pvppb.program_id
AND elig.partner_id = ptr_id
)*/
;
SELECT decode(send_notif_before_unit, 'PV_MONTHS',add_months(sysdate,send_notif_before_value)
,'PV_WEEKS', sysdate+ send_notif_before_value*7
,'PV_DAYS', sysdate+send_notif_before_value,null) cdate
FROM pv_ge_notif_rules_b
WHERE arc_notif_for_entity_code = 'PRGM'
AND notif_for_entity_id = program_id
AND notif_type_code = 'PG_MEM_EXP'
AND active_flag = 'Y';
SELECT enrl_request_id,request_status_code ,enrollment_type_code
FROM pv_pg_enrl_requests
WHERE enrl_request_id=
(
SELECT max(enrl_request_id)
FROM pv_pg_enrl_requests
WHERE partner_id=ptr_id
AND program_id=prgm_id
AND enrollment_type_code='RENEW'
);
SELECT 'X'
FROM pv_pg_enrl_change_rules rules
WHERE change_direction_code = 'UPGRADE'
AND effective_from_date <= sysdate
AND nvl(effective_to_date, sysdate) >= sysdate
AND active_flag = 'Y'
AND change_from_program_id=prgm_id
AND
EXISTS
( SELECT enrl_request_id
FROM pv_pg_enrl_requests
WHERE partner_id=ptr_id
AND request_status_code IN ('AWAITING_APPROVAL','APPROVED','INCOMPLETE')
AND program_id=rules.change_to_program_id
);
SELECT membership_status_code
FROM pv_pg_memberships
WHERE enrl_request_id=enrl_id;
l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
Open x_enrq_param_cur for select * from TABLE(cast (l_enrq_param_tbl as PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
SELECT rules.change_to_program_id program_id
,pvppb.program_name program_name
,pvppb.citem_version_id citem_version_id
,memb.membership_id membership_id
,rules.change_from_program_id change_from_program_id
,rules.enrl_change_rule_id enrl_change_rule_id
,pvppb.global_mmbr_reqd_flag global_mmbr_reqd_flag
,pvppb.prereq_process_rule_id prereq_process_rule_id
,pvppb.no_fee_flag no_fee_flag
,pvppb.vad_invite_allow_flag vad_invite_allow_flag
FROM pv_pg_enrl_change_rules rules
,pv_pg_memberships memb
,pv_partner_program_vl pvppb
,pv_partner_program_type_b pvpptb
WHERE pvppb.program_status_code = 'ACTIVE'
AND pvppb.program_level_code ='MEMBERSHIP'
AND pvppb.enabled_flag = 'Y'
AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
AND pvppb.program_type_id = pvpptb.program_type_ID
AND pvpptb.ACTIVE_FLAG = 'Y'
AND pvpptb.enabled_flag = 'Y'
AND rules.change_from_program_id=memb.program_id
AND memb.partner_id=p_ptr_id
--AND memb.membership_status_code in ('ACTIVE' , 'UPGRADED')
AND memb.program_id= (
SELECT memb10.program_id
FROM pv_pg_memberships memb10
WHERE memb10.membership_id =
( SELECT max(membership_id)
FROM pv_pg_memberships memb9
WHERE memb9.program_id=memb.program_id
AND memb9.partner_id=memb.partner_id
)
AND memb10.membership_status_code in ('ACTIVE' , 'UPGRADED')
)
AND rules.change_direction_code = 'UPGRADE'
AND rules.effective_from_date <= sysdate
AND nvl(rules.effective_to_date, sysdate) >= sysdate
AND rules.active_flag = 'Y'
AND rules.change_to_program_id=pvppb.program_id
AND rules.change_to_program_id not in (
/*SELECT memb2.program_id
FROM pv_pg_memberships memb2
WHERE memb2.program_id=rules.change_to_program_id
AND memb2.partner_id=memb.partner_id
AND memb2.membership_status_code in ('ACTIVE','UPGRADED','EXPIRED')
*/
SELECT memb2.program_id
FROM pv_pg_memberships memb2
WHERE memb2.membership_id =
(
SELECT max(membership_id)
FROM pv_pg_memberships memb3
WHERE memb3.program_id=rules.change_to_program_id
AND memb3.partner_id=memb.partner_id
)
AND memb2.membership_status_code in ('ACTIVE','UPGRADED','EXPIRED','FUTURE')
)
AND EXISTS
( SELECT 'X' FROM pv_program_partner_types pvppt
WHERE pvppt.partner_type IN
(
SELECT attr_value
FROM pv_enty_attr_values pveav
WHERE pveav.enabled_flag = 'Y'
AND pveav.latest_flag = 'Y'
AND pveav.entity = 'PARTNER'
AND pveav.entity_id =p_ptr_id
AND pveav.attribute_id = 3
)
AND pvpptb.program_type_id = pvppt.program_type_id
)
/* AND EXISTS -- check for pre-populated cache for pre-req evaluation
( SELECT 1
FROM pv_pg_elig_programs elig
WHERE elig.program_id = pvppb.program_id
AND elig.partner_id = p_ptr_id
)
*/
order by rules.change_to_program_id desc;
SELECT enrq.enrl_request_id,enrq.request_status_code,enrq.enrollment_type_code, memb.membership_status_code
FROM pv_pg_enrl_requests enrq
, pv_pg_memberships memb
WHERE enrq.enrl_request_id=
(
SELECT max(enrl_request_id)
FROM pv_pg_enrl_requests
WHERE partner_id=ptr_id
AND program_id=prgm_id
)
and enrq.enrl_request_id=memb.enrl_request_id(+);
l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
Open x_enrq_param_cur for select * from TABLE(cast (l_enrq_param_tbl as PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
SELECT enrq.enrl_request_id
,enrq.enrollment_type_code
,enrq.program_id
,enrq.custom_setup_id
,pvppb.program_name
,pvppb.citem_version_id
,pvppb.global_mmbr_reqd_flag
,pvppb.prereq_process_rule_id prereq_process_rule_id
,pvppb.no_fee_flag no_fee_flag
,pvppb.vad_invite_allow_flag vad_invite_allow_flag
FROM pv_partner_program_type_b pvpptb
,pv_partner_program_vl pvppb
,pv_pg_enrl_requests enrq
WHERE pvppb.program_status_code = 'ACTIVE'
AND pvppb.program_level_code ='MEMBERSHIP'
AND pvppb.enabled_flag = 'Y'
AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
AND pvppb.program_type_id = pvpptb.program_type_ID AND pvpptb.ACTIVE_FLAG = 'Y'
AND pvpptb.enabled_flag = 'Y'
AND EXISTS
( SELECT 'X'
FROM pv_program_partner_types pvppt
WHERE pvppt.partner_type
IN ( SELECT attr_value
FROM pv_enty_attr_values pveav
WHERE pveav.enabled_flag = 'Y'
AND pveav.latest_flag = 'Y'
AND pveav.entity = 'PARTNER'
AND pveav.entity_id =p_ptr_id
AND pveav.attribute_id = 3
)
AND pvpptb.program_type_id = pvppt.program_type_id
)
AND enrq.enrl_request_id =
( SELECT max(enrl_request_id)
FROM pv_pg_enrl_requests
WHERE partner_id= p_ptr_id
AND program_id=pvppb.PROGRAM_ID
AND request_status_code='INCOMPLETE'
)
/*AND EXISTS -- check for pre-populated cache for pre-req evaluation
( SELECT 1
FROM pv_pg_elig_programs elig
WHERE elig.program_id = pvppb.program_id
AND elig.partner_id = p_ptr_id
)
*/
;
l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';
Open x_enrq_param_cur for select * from TABLE(cast (l_enrq_param_tbl as PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
OPEN x_enrq_param_cur FOR SELECT * FROM TABLE( CAST (l_nienrq_param_tbl AS PV_ENRL_REQ_PARAM_TBL)) order by upper(programname) asc;
SELECT attr_value
FROM pv_enty_attr_values
WHERE entity='PARTNER'
AND entity_id=p_ptr_id
AND attribute_id=6
AND latest_flag='Y';
SELECT 'Y' , global_mmbr_reqd_flag
FROM pv_partner_program_type_b pvpptb
, pv_partner_program_b pvppb
WHERE program_id=p_prgm_id
AND nvl(allow_enrl_until_date, sysdate) > sysdate-1
AND pvppb.program_type_id = pvpptb.program_type_ID
AND pvpptb.ACTIVE_FLAG = 'Y'
AND pvpptb.enabled_flag = 'Y'
AND program_id NOT IN
(
SELECT rules.change_to_program_id
FROM pv_pg_enrl_change_rules rules
WHERE change_direction_code = 'UPGRADE'
AND effective_from_date <= sysdate
AND nvl(effective_to_date, sysdate) >= sysdate
AND active_flag = 'Y'
)
AND EXISTS
(
SELECT 'X'
FROM pv_program_partner_types pvppt
WHERE pvppt.partner_type IN
(
SELECT attr_value
FROM pv_enty_attr_values pveav
WHERE pveav.enabled_flag = 'Y'
AND pveav.latest_flag = 'Y'
AND pveav.entity = 'PARTNER'
AND pveav.entity_id = ptnr_id
AND pveav.attribute_id = 3
)
AND pvpptb.program_type_id = pvppt.program_type_id
)
/*AND EXISTS -- check for pre-populated cache for pre-req evaluation
(
SELECT 1
FROM pv_pg_elig_programs elig
WHERE elig.program_id = pvppb.program_id
AND elig.partner_id = ptnr_id
)*/
;
SELECT 'Y'
, global_mmbr_reqd_flag
FROM pv_pg_enrl_change_rules rules
, pv_pg_memberships memb
, pv_partner_program_vl pvppb
, pv_partner_program_type_b pvpptb
WHERE pvppb.program_status_code = 'ACTIVE'
AND pvppb.program_level_code ='MEMBERSHIP'
AND pvppb.enabled_flag = 'Y'
AND pvppb.program_id=p_to_id
AND nvl(pvppb.allow_enrl_until_date, sysdate) > sysdate-1
AND pvppb.program_type_id = pvpptb.program_type_ID
AND pvpptb.ACTIVE_FLAG = 'Y'
AND pvpptb.enabled_flag = 'Y'
AND memb.program_id=p_from_id
AND memb.partner_id=p_ptr_id
--AND memb.membership_status_code in ('ACTIVE' , 'UPGRADED')
AND memb.program_id= (
SELECT memb10.program_id
FROM pv_pg_memberships memb10
WHERE memb10.membership_id =
( SELECT max(membership_id)
FROM pv_pg_memberships memb9
WHERE memb9.program_id=memb.program_id
AND memb9.partner_id=memb.partner_id
)
AND memb10.membership_status_code in ('ACTIVE' , 'UPGRADED')
)
AND rules.change_from_program_id =p_from_id
AND rules.change_direction_code = 'UPGRADE'
AND rules.effective_from_date <= sysdate
AND nvl(rules.effective_to_date, sysdate) >= sysdate
AND rules.active_flag = 'Y'
AND rules.change_to_program_id=p_to_id
AND rules.change_to_program_id not in
(
/* SELECT memb2.program_id
FROM pv_pg_memberships memb2
WHERE memb2.program_id=p_to_id
AND memb2.partner_id=p_ptr_id
AND memb2.membership_status_code in ('ACTIVE', 'UPGRADED')
*/
SELECT memb2.program_id
FROM pv_pg_memberships memb2
WHERE memb2.membership_id =
(
SELECT max(membership_id)
FROM pv_pg_memberships memb3
WHERE memb3.program_id=rules.change_to_program_id
AND memb3.partner_id=memb.partner_id
)
AND memb2.membership_status_code in ('ACTIVE','UPGRADED','EXPIRED')
)
AND EXISTS
(
SELECT 'X'
FROM pv_program_partner_types pvppt
WHERE pvppt.partner_type IN
(
SELECT attr_value
FROM pv_enty_attr_values pveav
WHERE pveav.enabled_flag = 'Y'
AND pveav.latest_flag = 'Y'
AND pveav.entity = 'PARTNER'
AND pveav.entity_id = p_ptr_id
AND pveav.attribute_id = 3
)
AND pvpptb.program_type_id = pvppt.program_type_id
)
/* AND EXISTS -- check for pre-populated cache for pre-req evaluation
(
SELECT 1
FROM pv_pg_elig_programs elig
WHERE elig.program_id = pvppb.program_id
AND elig.partner_id = p_ptr_id
)
*/
;
SELECT request_status_code,enrollment_type_code
FROM pv_pg_enrl_requests
WHERE enrl_request_id=
(
SELECT max(enrl_request_id)
FROM pv_pg_enrl_requests
WHERE partner_id=ptr_id
AND program_id=prgm_id
);
l_query_str1:= 'SELECT ''X'' FROM pv_pg_elig_programs elig WHERE elig.partner_id=:1' || 'AND elig.program_id =:2';