1 PACKAGE BODY PER_QP_INVOCATIONS as
2 /* $Header: ffqti01t.pkb 115.6 2002/12/23 14:15:02 arashid ship $ */
3 --
4 function get_qp_session_id return NUMBER is
5 --
6 cursor c is
7 select per_quickpaint_invocations_s.nextval
8 from sys.dual;
9 --
10 l_id NUMBER;
11 --
12 begin
13 --
14 open c;
15 fetch c into l_id;
16 close c;
17 return(l_id);
18 --
19 end;
20 --
21 procedure pre_insert_checks(p_qp_session_id NUMBER
22 ,p_invocation_context NUMBER
23 ,p_invocation_type VARCHAR2
24 ,p_qp_report_id NUMBER
25 ,p_qp_invocation_id IN OUT NOCOPY NUMBER) is
26 --
27 cursor c is
28 select 'x'
29 from per_quickpaint_invocations
30 where qp_session_id = p_qp_session_id
31 and invocation_context = p_invocation_context
32 and invocation_type = p_invocation_type
33 and qp_report_id = p_qp_report_id;
34 --
35 cursor c1 is
36 select per_quickpaint_invocations_s.nextval
37 from sys.dual;
38 --
39 l_exists varchar2(1);
40 --
41 begin
42 --
43 hr_utility.set_location('per_qp_invocations.pre_insert_checks',1);
44 --
45 open c;
46 fetch c into l_exists;
47 if c%found then
48 --
49 close c;
50 if p_invocation_type = 'S' then
51 --
52 hr_utility.set_message(801,'HR_6737_QP_NO_SET_TWICE');
53 --
54 else
55 --
56 hr_utility.set_message(801,'HR_6694_QP_NO_RUN_TWICE');
57 --
58 end if;
59 --
60 hr_utility.raise_error;
61 --
62 end if;
63 close c;
64 --
65 open c1;
66 fetch c1 into p_qp_invocation_id;
67 close c1;
68 --
69 end pre_insert_checks;
70 --
71 procedure populate_fields(p_qp_report_id NUMBER
72 ,p_invocation_context NUMBER
73 ,p_invocation_type VARCHAR2
74 ,p_session_date DATE
75 ,p_qp_report_name IN OUT NOCOPY VARCHAR2
76 ,p_assignment_set IN OUT NOCOPY VARCHAR2
77 ,p_full_name IN OUT NOCOPY VARCHAR2
78 ,p_assignment_number IN OUT NOCOPY VARCHAR2
79 ,p_user_person_type IN OUT NOCOPY VARCHAR2) is
80 --
81 cursor c is
82 select assignment_set_name
83 from hr_assignment_sets
84 where assignment_set_id = p_invocation_context;
85 --
86 cursor c1 is
87 select qp_report_name
88 from ff_qp_reports
89 where qp_report_id = p_qp_report_id;
90 --
91 cursor c2 is
92 select p.full_name
93 , a.assignment_number
94 , hr_person_type_usage_info.get_user_person_type(p_session_date,p.person_id)
95 from per_people p
96 , per_assignments_f a
97 where p.person_id = a.person_id
98 and a.assignment_id = p_invocation_context
99 and p_session_date between a.effective_start_date
100 and a.effective_end_date;
101 --
102 begin
103 --
104 hr_utility.set_location('per_qp_invocations.populate_fields',1);
105 --
106 open c1;
107 fetch c1 into p_qp_report_name;
108 close c1;
109 --
110 if p_invocation_type = 'A' then
111 --
112 open c2;
113 fetch c2 into p_full_name, p_assignment_number, p_user_person_type;
114 close c2;
115 --
116 end if;
117 --
118 if p_invocation_type = 'S' then
119 --
120 open c;
121 fetch c into p_assignment_set;
122 close c;
123 --
124 end if;
125 --
126 end populate_fields;
127 --
128 -- Name : format_date_line
129 -- Purpose : To convert canonical dates which appear in quickpaint result text
130 -- into user display dates within the result text.
131 --
132 function format_date_line(p_textline VARCHAR2) return VARCHAR2 is
133 --
134 l_canonical_date varchar2(19);
135 l_display_date varchar2(19);
136 l_start_text varchar2(240);
137 l_end_text varchar2(240);
138 l_new_line varchar2(240);
139 l_start_of_date number;
140 l_end_of_date number;
141 --
142 Begin
143 --
144 --
145 -- Select the location of the start of the first canonical date
146 -- Will be of format 'YYYY/MM/DD 00:00:00'
147 --
148 l_start_of_date := (instr(p_textline,'00:00:00') - 11);
149 --
150 -- If no date is present in the line return line unaltered
151 --
152 if (l_start_of_date < 1) then
153 --
154 return(p_textline);
155 --
156 end if;
157 --
158 -- Select the location of the end of the first canonical date
159 -- and select the chunks of text appearing before and after the date.
160 -- Select the date in canonical format and convert to display format.
161 -- Right pad the display format to prevent formatting errors and
162 -- create the new line inserting the start and end text around the date.
163 --
164 l_end_of_date := (l_start_of_date + 18);
165 l_start_text := substr(p_textline,1,(l_start_of_date - 1));
166 l_end_text := substr(p_textline,(l_end_of_date + 1));
167 l_canonical_date := substr(p_textline,l_start_of_date,19);
168 l_display_date := fnd_date.date_to_chardate(fnd_date.canonical_to_date(l_canonical_date));
169 l_display_date := rpad(l_display_date,19);
170 l_new_line := l_start_text||l_display_date||l_end_text;
171 --
172 -- Recursively call this program again to check if any more canonical dates
173 -- appear in this line of text. Continues until no more dates found and the
174 -- new text line with formatted dates is returned.
175 --
176 l_new_line := format_date_line(l_new_line);
177 --
178 return(l_new_line);
179 --
180 End format_date_line;
181 --
182 -- Name : load_result
183 -- Purpose : To format and return the quickpaint report result text.
184 --
185 function load_result(p_assignment_id NUMBER
186 ,p_qp_invocation_id NUMBER) return VARCHAR2 is
187 --
188 cursor c is
189 select text
190 from per_quickpaint_result_text
191 where assignment_id = p_assignment_id
192 and qp_invocation_id = p_qp_invocation_id
193 order by line_number;
194 --
195 l_result VARCHAR2(16000) := '';
196 l_line VARCHAR2(240);
197 l_formatted_line VARCHAR2(240);
198 l_eol VARCHAR2(1) := '';
199 --
200 Begin
201 --
202 hr_utility.set_location('per_qp_invocations.load_result',1);
203 --
204 open c;
205 loop
206 --
207 fetch c into l_line;
208 exit when c%notfound;
209 l_formatted_line := format_date_line(l_line);
210 l_result := l_result || l_eol || l_formatted_line;
211 --
212 -- Excuse the loss of formatting, but chr(10) has been replaced
213 -- by an acceptable method for getting a newline.
214 --
215 l_eol :=
216 '
217 ';
218 --
219 end loop;
220 close c;
221 --
222 return(l_result);
223 --
224 End load_result;
225 --
226 procedure get_assignment(p_assignment_id NUMBER
227 ,p_qp_invocation_id NUMBER
228 ,p_full_name IN OUT NOCOPY VARCHAR2
229 ,p_user_person_type IN OUT NOCOPY VARCHAR2
230 ,p_assignment_number IN OUT NOCOPY VARCHAR2
231 ,p_result OUT NOCOPY VARCHAR2) is
232 cursor c is
233 select full_name
234 , user_person_type
235 , assignment_number
236 from per_quickpaint_assignments a
237 where assignment_id = p_assignment_id
238 and exists(select null
239 from per_quickpaint_result_text t
240 where t.assignment_id = p_assignment_id
241 and t.qp_invocation_id = p_qp_invocation_id);
242 --
243 begin
244 --
245 hr_utility.set_location('per_qp_invocations.get_assignment',1);
246 --
247 open c;
248 fetch c into p_full_name,p_user_person_type,p_assignment_number;
249 close c;
250 p_result := load_result(p_assignment_id
251 ,p_qp_invocation_id);
252 --
253 end get_assignment;
254 --
255 procedure init_cust(p_customized_restriction_id NUMBER
256 ,p_restrict_empapl IN OUT NOCOPY VARCHAR2
257 ,p_restrict_person_type IN OUT NOCOPY VARCHAR2
258 ,p_restrict_inq IN OUT NOCOPY VARCHAR2
259 ,p_restrict_use IN OUT NOCOPY VARCHAR2) is
260 --
261 cursor restriction(l_customization_type VARCHAR2) is
262 select value
263 from pay_restriction_values
264 where restriction_code = l_customization_type
265 and customized_restriction_id = p_customized_restriction_id;
266 --
267 l_exists varchar2(30);
268 --
269 begin
270 --
271 hr_utility.set_location('per_qp_invocations.init_cust',1);
272 --
273 p_restrict_empapl := 'N';
274 open restriction('EMP_APL');
275 fetch restriction into l_exists;
276 if restriction%found then
277 --
278 p_restrict_empapl := 'Y';
279 --
280 end if;
281 close restriction;
282 --
283 p_restrict_person_type := 'N';
284 open restriction('PERSON_TYPE');
285 fetch restriction into l_exists;
286 if restriction%found then
287 --
288 p_restrict_person_type := 'Y';
289 --
290 end if;
291 close restriction;
292 --
293 p_restrict_inq := 'N';
294 open restriction('QP_INQUIRY');
295 fetch restriction into l_exists;
296 if restriction%found then
297 --
298 p_restrict_inq := 'Y';
299 --
300 end if;
301 close restriction;
302 --
303 open restriction('ASG_SET');
304 fetch restriction into p_restrict_use;
305 close restriction;
306 --
307 end init_cust;
308 --
309 function validate_assignment(p_business_group_id NUMBER
310 ,p_session_date DATE
311 ,p_full_name VARCHAR2
312 ,p_assignment_number VARCHAR2) return NUMBER is
313 --
314 cursor c is
315 select a.assignment_id
316 from per_assignments_f a
317 , per_people p
318 where a.person_id = p.person_id
319 and ((p_assignment_number is null
320 and a.assignment_number is null)
321 or (p_assignment_number is not null
322 and a.assignment_number = p_assignment_number))
323 and p.full_name = p_full_name
324 and a.business_group_id + 0 = p_business_group_id
325 and p_session_date between a.effective_start_date
326 and a.effective_end_date;
327 --
328 l_invocation_context NUMBER;
329 --
330 begin
331 --
332 hr_utility.set_location('per_qp_invocations.validate_assignment',1);
333 --
334 open c;
335 fetch c into l_invocation_context;
336 if c%notfound then
337 --
338 close c;
339 hr_utility.set_message(801,'HR_7097_ASS_SET_INVALID');
340 hr_utility.set_message_token('ASS_SET','assignment');
341 hr_utility.raise_error;
342 --
343 end if;
344 close c;
345 --
346 return(l_invocation_context);
347 --
348 end validate_assignment;
349 --
350 procedure delete_quickpaints(p_qp_session_id NUMBER) is
351 --
352 begin
353 --
354 hr_utility.set_location('per_qp_invocations.delete_quickpaints',1);
355 --
356 delete from per_quickpaint_invocations
357 where qp_session_id = p_qp_session_id;
358 --
359 commit;
360 --
361 end delete_quickpaints;
362 --
363 function print_result(p_business_group_id NUMBER
364 ,p_session_date DATE
365 ,p_qp_invocation_id NUMBER) return BOOLEAN is
366 --
367 l_bg_arg VARCHAR2(80);
368 l_sd_arg VARCHAR2(80);
369 l_qi_arg VARCHAR2(80);
370 l_req_id NUMBER;
371 --
372 begin
373 --
374 l_bg_arg := to_char(p_business_group_id);
375 /* Fix for WWBUG 1756943 */
376 l_sd_arg := fnd_date.date_to_canonical(p_session_date);
377 l_qi_arg := to_char(p_qp_invocation_id);
378 --
379 l_req_id := fnd_request.submit_request
380 ('PER'
381 ,'PERRPRQP'
382 ,NULL
383 ,NULL
384 ,NULL
385 ,l_bg_arg
386 ,l_sd_arg
387 ,l_qi_arg);
388 --
389 if (l_req_id = 0) then
390 --
391 rollback;
392 return(FALSE);
393 --
394 else
395 --
396 commit;
397 return(TRUE);
398 --
399 end if;
400 --
401 end print_result;
402 --
403 END PER_QP_INVOCATIONS;
[Home] [Help]