[Home] [Help]
PACKAGE: APPS.OTA_CPE_UTIL
Source
1 PACKAGE OTA_CPE_UTIL as
2 /* $Header: otcpewrs.pkh 120.19.12010000.4 2008/11/07 10:18:49 pekasi ship $ */
3
4 -- ---------------------------------------------------------------------------
5 -- |----------------------< chk_cert_prd_compl >--------------------------|
6 -- ---------------------------------------------------------------------------
7 --
8
9 -- {Start Of Comments}
10 --
11 -- Description:
12 --
13 -- Prerequisites:
14 --
15 --
16 -- In Arguments:
17 --
18 --
19 -- Post Success:
20 --
21 -- Post Failure:
22 --
23 --
24 -- Access Status:
25 -- Internal Development Use Only.
26 --
27 -- {End Of Comments}
28 -- ---------------------------------------------------------------------------
29 Function chk_cert_prd_compl(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type)
30 return varchar2;
31
32 Procedure crt_comp_upd_succ_att(p_event_id in ota_events.event_id%type,
33 p_person_id in number
34 );
35
36 Function is_cert_success_complete(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
37 p_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
38 p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_start_date%type,
39 p_person_id in number)
40 return varchar2;
41
42 Procedure update_cpe_status(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type
43 ,p_certification_status_code OUT NOCOPY VARCHAR2
44 ,p_enroll_from in varchar2 default null
45 ,p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null
46 ,p_child_update_flag in varchar2 default 'Y'
47 ,p_completion_date in date default sysdate);
48
49 -- ---------------------------------------------------------------------------
50 -- |----------------------< is_period_renewable >-------------------------|
51 -- ---------------------------------------------------------------------------
52 -- Returns whether a certification period is renewable or not
53
54 -- {Start Of Comments}
55 --
56 -- Description:
57 --
58 -- Prerequisites:
59 --
60 --
61 -- In Arguments:
62 -- p_cert_enrollment_id
63 --
64 -- Post Success:
65 --
66 -- Post Failure:
67 --
68 --
69 -- Access Status:
70 -- Internal Development Use Only.
71 --
72 -- {End Of Comments}
73 -- ---------------------------------------------------------------------------
74 function is_period_renewable(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type)
75 return varchar2;
76
77 -- ---------------------------------------------------------------------------
78 -- |----------------------< calc_cre_dates >-------------------------|
79 -- ---------------------------------------------------------------------------
80 -- Returns the earliest enroll date and expiration date for the passed
81 --cert_enrollment_id and certification_id
82 -- Based on is_initial_flag would return first or next earliest enroll date
83 --and expiration date's
84
85 -- {Start Of Comments}
86 --
87 -- Description:
88 --
89 -- Prerequisites:
90 --
91 --
92 -- In Arguments:
93 -- p_cert_enrollment_id
94 -- p_certification_id
95 -- p_mode
96 --
97 -- Post Success:
98 -- p_earliest_enroll_date
99 -- p_expiration_date
100 -- Post Failure:
101 --
102 --
103 -- Access Status:
104 -- Internal Development Use Only.
105 --
106 -- {End Of Comments}
107 -- ---------------------------------------------------------------------------
108 PROCEDURE calc_cre_dates(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
109 p_certification_id in ota_cert_enrollments.certification_id%type,
110 p_mode in varchar2,
111 p_earliest_enroll_date OUT nocopy ota_cert_enrollments.earliest_enroll_date%type,
112 p_expiration_date OUT nocopy ota_cert_enrollments.expiration_date%type,
113 p_cert_period_start_date in date default sysdate);
114
115 Function get_next_prd_dur_days(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
116 p_cert_period_start_date in date default sysdate)
117 return varchar2;
118
119 FUNCTION get_cert_mbr_status (p_cert_mbr_id in ota_certification_members.certification_member_id%TYPE,
120 p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
121 p_code in number default 1)
122 RETURN varchar2;
123
124 FUNCTION get_cert_mbr_name (p_cert_mbr_id in ota_certification_members.certification_member_id%TYPE)
125 RETURN varchar2;
126
127 FUNCTION get_cre_status (p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%TYPE,
128 p_mode in varchar2 default 'm')
129 RETURN varchar2;
130
131 FUNCTION get_cpe_edit_enabled(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE)
132 RETURN varchar2;
133
134 FUNCTION chk_prd_end_date(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
135 p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%TYPE,
136 p_mass_update_flag in varchar2 default 'N') return varchar2;
137
138 procedure create_cpe_rec(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
139 p_expiration_date in date,
140 p_cert_period_start_date in date default sysdate,
141 p_cert_prd_enrollment_id OUT NOCOPY ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
142 p_certification_status_code OUT NOCOPY VARCHAR2);
143
144 PROCEDURE delete_prd_cascade(p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
145 p_return_code OUT NOCOPY varchar2);
146
147 procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
148 p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
149 p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
150 p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
151 p_return_status out NOCOPY VARCHAR2,
152 p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate) );
153
154 Procedure update_cert_status_to_expired(
155 ERRBUF OUT NOCOPY VARCHAR2,
156 RETCODE OUT NOCOPY VARCHAR2);
157
158 Procedure sync_cert_status_to_class_enrl(
159 ERRBUF OUT NOCOPY VARCHAR2,
160 RETCODE OUT NOCOPY VARCHAR2);
161
162 FUNCTION get_latest_cpe_col(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%TYPE,
163 p_col_name in varchar2 default 'Period_Status_Meaning') return varchar2;
164
165 function get_elapsed_due_date(p_certification_id in ota_certifications_b.certification_id%type) return date;
166
167 function check_active_periods(p_event_id in ota_events.event_id%type) return varchar2;
168
169 procedure sync_late_subsc_to_class;
170
171 END OTA_CPE_UTIL;
172