[Home] [Help]
PACKAGE BODY: APPS.HR_EMPL_VERF_UTIL
Source
1 PACKAGE BODY HR_EMPL_VERF_UTIL AS
2 /* $Header: hrevutil.pkb 120.6 2006/02/28 05:15:35 srpurani noship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12
13 g_package varchar2(50) := 'hr_empl_verf_util';
14
15 FUNCTION CHECK_TICKET_STRING
16 ( p_ticket IN VARCHAR2,
17 p_operation OUT NOCOPY VARCHAR2,
18 p_argument OUT NOCOPY VARCHAR2)
19 RETURN NUMBER IS
20 isTicketMatched boolean default FALSE;
21 BEGIN
22
23 isTicketMatched := fnd_http_ticket.check_ticket_string(p_ticket => p_ticket,
24 p_operation => p_operation,
25 p_argument => p_argument);
26 IF( isTicketMatched = TRUE) THEN
27 return 1;
28 ELSIF ( isTicketMatched = FALSE ) THEN
29 return 0;
30 ELSIF ( isTicketMatched is null ) THEN
31 return 2;
32 END IF;
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 raise;
37 END CHECK_TICKET_STRING;
38
39
40 FUNCTION CHECK_ONETIME_TICKET_STRING
41 ( p_ticket IN VARCHAR2,
42 p_operation OUT NOCOPY VARCHAR2,
43 p_argument OUT NOCOPY VARCHAR2)
44 RETURN NUMBER IS
45 isTicketMatched boolean default FALSE;
46 BEGIN
47
48 isTicketMatched := fnd_http_ticket.CHECK_ONETIME_TICKET_STRING(p_ticket => p_ticket,
49 p_operation => p_operation,
50 p_argument => p_argument);
51 IF( isTicketMatched ) then
52 return 1;
53 ELSE
54 return 0;
55 END IF;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 raise;
60 END CHECK_ONETIME_TICKET_STRING;
61
62 /*
63
64 PROCEDURE send_mail(to_address IN VARCHAR2, from_address IN VARCHAR2,
65 mail_content VARCHAR2)
66 IS
67 conn utl_smtp.connection;
68 BEGIN
69 conn := utl_smtp.open_connection('rgmamersmtp.oraclecorp.com');
70 utl_smtp.helo(conn, 'oracle.com');
71 utl_smtp.mail(conn, from_address);
72 utl_smtp.rcpt(conn, to_address);
73 utl_smtp.open_data(conn);
74 utl_smtp.write_data(conn, 'From' || ': ' || '"Dev" [email protected]>' || utl_tcp.CRLF);
75 utl_smtp.write_data(conn, 'To' || ': ' || '"Recipient" [email protected]>' || utl_tcp.CRLF);
76 utl_smtp.write_data(conn, 'Subject' || ': ' || 'Employment Verification' || utl_tcp.CRLF);
77 utl_smtp.write_data(conn, utl_tcp.CRLF || mail_content);
78 utl_smtp.close_data(conn);
79 utl_smtp.quit(conn);
80 EXCEPTION
81 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
82 BEGIN
83 utl_smtp.quit(conn);
84 EXCEPTION
85 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
86 NULL; -- When the SMTP server is down or unavailable, we don't have
87 -- a connection to the server. The quit call will raise an
88 -- exception that we can ignore.
89 END;
90 raise_application_error(-20000,
91 'Failed to send mail due to the following error: ' || sqlerrm);
92 END;
93
94 */
95
96 PROCEDURE send_notification
97 (to_address IN VARCHAR2,
98 from_address IN VARCHAR2,
99 reply_to_address in VARCHAR2,
100 access_url VARCHAR2,
101 access_days NUMBER,
102 emp_name VARCHAR2,
103 access_limit NUMBER,
104 personal_key VARCHAR2,
105 comments VARCHAR2)
106 IS
107 lv_role_name wf_roles.name%TYPE;
108 lv_role_display_name wf_roles.display_name%TYPE;
109 ln_notification_id number;
110
111 Role_Info_Tbl wf_directory.wf_local_roles_tbl_type;
112 login_user_info_tbl wf_directory.wf_local_roles_tbl_type;
113 login_user_name fnd_user.user_name%TYPE;
114 lv_subject FND_NEW_MESSAGES.message_text%TYPE;
115 l_proc varchar2(50) := 'send_notification';
116
117
118 BEGIN
119 hr_utility.set_location(' Entering: ' || g_package || '.' || l_proc,5);
120
121 lv_role_name := null;
122 --Fix 4778117
123 begin
124 select to_char(WF_ADHOC_ROLE_S.NEXTVAL)
125 into lv_role_name
126 from SYS.DUAL;
127 exception
128 when others then
129 raise;
130 end;
131
132 lv_role_name := '~WF_ADHOC-' || lv_role_name;
133 lv_role_display_name := to_address;
134 wf_directory.GetRoleInfo2(lv_role_name, Role_Info_Tbl);
135
136 login_user_name := fnd_global.user_name();
137 wf_directory.GetRoleInfo2(login_user_name, login_user_info_tbl);
138
139 begin
140 wf_directory.createadhocrole(role_name => lv_role_name,
141 role_display_name => lv_role_display_name,
142 language => login_user_info_tbl(1).language,
143 territory => login_user_info_tbl(1).territory,
144 role_description => 'Adhoc Role for Employment Verification',
145 notification_preference => 'MAILHTML',
146 role_users => null,
147 email_address => to_address,
148 fax => null,
149 status => 'ACTIVE',
150 expiration_date => trunc(sysdate)+access_days,--4778117
151 parent_orig_system => null,
152 parent_orig_system_id => null,
153 owner_tag => 'PER');
154 exception when others then
155 hr_utility.set_location(' Exception when calling wf_directory.createadhocrole ' || SQLERRM, 55);
156 hr_utility.set_location(' Leaving ' || l_proc, 60);
157 end;
158
159
160
161 ln_notification_id := wf_notification.send(role => lv_role_name,
162 msg_type => 'HRVERF' ,
163 msg_name => 'HR_ACCESS_KEY_MSG',
164 callback => null,
165 context => null,
166 send_comment => null,
167 priority => 50) ;
168
169 fnd_message.set_name('PER','HR_EV_NTF_SUBJECT');
170 fnd_message.set_token('EMPL_NAME',emp_name,false);
171 lv_subject := fnd_message.get;
172
173
174 wf_notification.setattrtext(ln_notification_id,'EXT_URL',access_url);
175 wf_notification.setattrtext(ln_notification_id,'EXP_DAYS',access_days);
176 wf_notification.setattrtext(ln_notification_id,'ACCESS_TIMES',access_limit);
177 wf_notification.setattrtext(ln_notification_id,'#FROM_ROLE',login_user_name);
178 wf_notification.setattrtext(ln_notification_id,'EMP_NAME',lv_subject);
179 wf_notification.setattrtext(ln_notification_id,'REPLY_TO','mailto:' ||reply_to_address);
180 wf_notification.setattrtext(ln_notification_id,'#WFM_FROM', emp_name);
181 wf_notification.setattrtext(ln_notification_id,'#WFM_REPLYTO',reply_to_address);
182 wf_notification.setattrtext(ln_notification_id,'COMMENTS',comments);
183
184
185
186 EXCEPTION WHEN OTHERS THEN
187 rollback;
188 raise;
189 END;
190
191
192 PROCEDURE GET_EMPLOYEE_SALARY
193 (p_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
194 p_Effective_Date In Date,
195 p_salary OUT nocopy number,
196 p_frequency OUT nocopy varchar2,
197 p_annual_salary OUT nocopy number,
198 p_pay_basis OUT nocopy varchar2,
199 p_reason_cd OUT nocopy varchar2,
200 p_currency OUT nocopy varchar2,
201 p_status OUT nocopy number,
202 p_currency_name OUT nocopy varchar2,
203 p_pay_basis_frequency OUT nocopy varchar2
204 ) IS
205 CURSOR get_currency_name(p_code in varchar2) IS
206 select name from fnd_currencies_tl
207 where currency_code = p_code
208 and language = userenv('lang');
209
210 CURSOR get_frequency_name(p_lookup_code in varchar2) IS
211 select meaning from hr_lookups hl
212 where hl.lookup_type = 'PAY_BASIS'
213 and lookup_code = p_lookup_code
214 and hl.enabled_flag = 'Y'
215 and sysdate between
216 nvl(hl.start_date_active, sysdate)
217 and nvl(hl.end_date_active, sysdate);
218
219 BEGIN
220 pqh_employee_salary.get_employee_salary
221 (p_assignment_id => p_assignment_id,
222 p_effective_date => p_effective_date,
223 p_salary => p_salary,
224 p_frequency => p_frequency,
225 p_annual_salary => p_annual_salary,
226 p_pay_basis => p_pay_basis,
227 p_reason_cd => p_reason_cd,
228 p_currency => p_currency,
229 p_status => p_status,
230 p_pay_basis_frequency => p_pay_basis_frequency);
231
232 if( p_currency is not null) then
233 open get_currency_name(p_currency);
234 fetch get_currency_name into p_currency_name;
235 close get_currency_name;
236 end if;
237
238 if(p_pay_basis_frequency is not null) then
239 open get_frequency_name(p_pay_basis_frequency);
240 fetch get_frequency_name into p_pay_basis_frequency;
241 close get_frequency_name;
242 end if;
243
244
245
246 END;
247
248 FUNCTION UPDATE_TICKET_STRING(P_TICKET in varchar2,
249 P_OPERATION in varchar2,
250 P_ARGUMENT in varchar2)
251 RETURN NUMBER IS
252 return_value boolean default false;
253 BEGIN
254 return_value := FND_HTTP_TICKET.UPDATE_TICKET_STRING(P_TICKET, P_OPERATION, P_ARGUMENT);
255
256 if(return_value = TRUE) then
257 return 0;
258 else
259 return 1;
260 end if;
261 EXCEPTION WHEN OTHERS THEN
262 rollback;
263 raise;
264 END;
265
266 -- Enter further code below as specified in the Package spec.
267
268 END HR_EMPL_VERF_UTIL;