[Home] [Help]
91: FUNCTION isPartnerType(p_partner_id IN NUMBER,p_partner_type IN VARCHAR2)
92: RETURN VARCHAR2 IS
93: CURSOR partnerType_cur(p_part_id number,p_ptr_type varchar2) IS
94: SELECT 'Y'
95: FROM DUAL
96: WHERE EXISTS (SELECT 1
97: FROM pv_enty_attr_values
98: WHERE entity = 'PARTNER'
99: AND attribute_id = 3
231: AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
232: UNION
233: SELECT dependent_id
234: FROM
235: DUAL
236: );
237:
238: CURSOR enrtype_renew_csr(g_ptr_id NUMBER,dependent_id NUMBER) IS
239: SELECT min(original_end_date)
254: AND nvl(EFFECTIVE_TO_DATE,sysdate+1)>=sysdate
255: UNION
256: SELECT dependent_id
257: FROM
258: DUAL
259: );
260:
261: CURSOR get_global_csr( sub_ptr_id NUMBER) IS
262: SELECT glob.partner_id
435: l_temp varchar2(1);
436: isrecord boolean:=false ;
437: CURSOR rec_cur(p_prgm_id number) IS
438: SELECT 'X'
439: FROM dual
440: WHERE EXISTS
441: ( SELECT 'X'
442: FROM pv_program_contracts
443: WHERE program_id = p_prgm_id
527: FUNCTION check_pending_default(p_enrollment_req_id in number) return boolean is
528: l_temp varchar2(1);
529: isrecord boolean:=false ;
530: cursor rec_cur(p_enrl_req_id number) is
531: select 'X' from dual where exists (
532: select entity_approver_id from pv_ge_temp_approvers
533: where appr_for_entity_id=p_enrl_req_id
534: and approval_status_code='PENDING_DEFAULT');
535:
555: FUNCTION check_record_exists(p_enrollment_req_id in number) return boolean is
556: l_temp varchar2(1);
557: isrecord boolean:=false ;
558: cursor rec_cur(p_enrl_req_id number) is
559: select 'X' from dual where exists
560: ( select entity_approver_id from pv_ge_temp_approvers where appr_for_entity_id=p_enrl_req_id);
561:
562: BEGIN
563: OPEN rec_cur(p_enrollment_req_id);
631: FUNCTION check_pending_approval (p_enrollment_req_id in number) return boolean is
632: l_temp varchar2(1);
633: isPendingApproval boolean:=false;
634: cursor rec_cur(p_enrl_req_id number) is
635: select 'X' from dual where exists (
636: select entity_approver_id from pv_ge_temp_approvers
637: where appr_for_entity_id=p_enrl_req_id
638: and approval_status_code in ('PENDING_APPROVAL'));
639: BEGIN
690: WHERE enrq.program_id=prgm.program_id
691: AND enrl_request_id=p_enrl_req_id;
692:
693: CURSOR checklistresponse_cur(p_enrl_req_id number) IS
694: SELECT 'X' from dual
695: where EXISTS
696: ( SELECT checklist_item_id
697: FROM pv_ge_chklst_responses
698: WHERE response_for_entity_id = p_enrl_req_id
730: FUNCTION isApproverExists (p_program_type_id in number) return boolean is
731: l_temp varchar2(1);
732: isavailable boolean:=false ;
733: cursor app_cur(p_prgm_type_id varchar) is
734: select 'X' from dual where exists
735: (select approver_id from ams_approvers appr,ams_approval_details apdt
736: where nvl(appr.start_date_active,sysdate)<=sysdate
737: and nvl(appr.end_date_active,sysdate)>=sysdate
738: and appr.ams_approval_detail_id =apdt.approval_detail_id
2563: WHERE enrl_request_id=p_enrl_req_id;
2564:
2565: /**
2566: CURSOR check_attr_exist(p_program_id NUMBER) IS
2567: SELECT 'X' from dual where exists( SELECT distinct(entity_attr_id)
2568: FROM pv_ge_qsnr_elements_b
2569: WHERE arc_used_by_entity_code='PRGM'
2570: AND used_by_entity_id in
2571: ( SELECT program_id
2576: );
2577: */
2578:
2579: CURSOR check_attr_exist(p_program_id NUMBER) IS
2580: SELECT 'X' from dual where exists( SELECT distinct(attr.attribute_id)
2581: FROM pv_ge_qsnr_elements_b qsnr, pv_entity_attrs attr
2582: WHERE arc_used_by_entity_code='PRGM'
2583: AND attr.ENTITY_ATTR_ID=qsnr.entity_attr_id
2584: AND used_by_entity_id in
4995: l_program_name VARCHAR2(60);
4996:
4997: CURSOR isPrimaryExist ( ptr_id IN NUMBER ) IS
4998: SELECT 'Y'
4999: FROM dual
5000: WHERE EXISTS
5001: (
5002: SELECT user_id
5003: FROM pv_partner_primary_users_v