1 PACKAGE BODY PSP_GENERAL AS
2 /* $Header: PSPGENEB.pls 120.13.12010000.4 2008/12/04 08:56:28 amakrish ship $ */
3 g_ws_option VARCHAR2(1);
4
5 FUNCTION good_time_format ( p_time IN VARCHAR2 ) RETURN BOOLEAN IS
6 --
7 BEGIN
8 --
9 IF p_time IS NOT NULL THEN
10 --
11 IF NOT (SUBSTR(p_time,1,2) BETWEEN '00' AND '23' AND
12 SUBSTR(p_time,4,2) BETWEEN '00' AND '59' AND
13 SUBSTR(p_time,3,1) = ':' AND
14 LENGTH(p_time) = 5) THEN
15 RETURN FALSE;
16 ELSE
17 RETURN TRUE;
18 END IF;
19 --
20 ELSE
21 RETURN FALSE;
22 END IF;
23 --
24 EXCEPTION
25 --
26 WHEN OTHERS THEN
27 RETURN FALSE;
28 --
29 END good_time_format;
30
31 PROCEDURE calc_sch_based_dur ( p_days_or_hours IN VARCHAR2,
32 p_date_start IN DATE,
33 p_date_end IN DATE,
34 p_time_start IN VARCHAR2,
35 p_time_end IN VARCHAR2,
36 p_assignment_id IN NUMBER,
37 p_duration IN OUT NOCOPY NUMBER
38 ) IS
39 --
40 p_start_duration NUMBER;
41 p_end_duration NUMBER;
42 l_idx NUMBER;
43 l_ref_date DATE;
44 l_first_band BOOLEAN;
45 l_day_start_time VARCHAR2(5);
46 l_day_end_time VARCHAR2(5);
47 l_start_time VARCHAR2(5);
48 l_end_time VARCHAR2(5);
49 --
50 l_start_date DATE;
51 l_end_date DATE;
52 l_schedule cac_avlblty_time_varray;
53 l_schedule_source VARCHAR2(10);
54 l_return_status VARCHAR2(1);
55 l_return_message VARCHAR2(2000);
56 --
57 l_time_start VARCHAR2(5);
58 l_time_end VARCHAR2(5);
59 --
60 e_bad_time_format EXCEPTION;
61 --
62 BEGIN
63 hr_utility.set_location('Entering psp_general.calc_sch_based_dur',10);
64 p_duration := 0;
65 l_time_start := p_time_start;
66 l_time_end := p_time_end;
67 --
68 IF l_time_start IS NULL THEN
69 l_time_start := '00:00';
70 ELSE
71 IF NOT good_time_format(l_time_start) THEN
72 RAISE e_bad_time_format;
73 END IF;
74 END IF;
75 IF l_time_end IS NULL THEN
76 l_time_end := '00:00';
77 ELSE
78 IF NOT good_time_format(l_time_end) THEN
79 RAISE e_bad_time_format;
80 END IF;
81 END IF;
82 IF p_days_or_hours = 'D' THEN
83 l_time_end := '23:59';
84 END IF;
85 l_start_date := TO_DATE(TO_CHAR(p_date_start,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
86 l_end_date := TO_DATE(TO_CHAR(p_date_end,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
87
88 hr_utility.trace('p_assignment_id ' ||p_assignment_id);
89 hr_utility.trace('l_start_date ' ||l_start_date);
90 hr_utility.trace('l_end_date ' ||l_end_date);
91 hr_utility.trace('p_time_start ' ||p_time_start);
92 hr_utility.trace('p_time_end ' ||p_time_end);
93 hr_utility.trace('p_days_or_hours ' ||p_days_or_hours);
94
95 --
96 -- Fetch the work schedule
97 --
98 hr_wrk_sch_pkg.get_per_asg_schedule
99 ( p_person_assignment_id => p_assignment_id
100 , p_period_start_date => l_start_date
101 , p_period_end_date => l_end_date
102 , p_schedule_category => NULL
103 , p_include_exceptions => 'N'-- for bug 5102813 'Y'
104 , p_busy_tentative_as => 'FREE'
105 , x_schedule_source => l_schedule_source
106 , x_schedule => l_schedule
107 , x_return_status => l_return_status
108 , x_return_message => l_return_message
109 );
110 --
111
112 hr_utility.trace('l_return_status ' ||l_return_status);
113 IF l_return_status = '0' THEN
114 --
115 -- Calculate duration
116 --
117 l_idx := l_schedule.first;
118 hr_utility.trace('l_idx ' || l_idx);
119 hr_utility.trace('Schedule Counts ' ||l_schedule.count);
120 --
121 IF p_days_or_hours = 'D' THEN
122 --
123 l_first_band := TRUE;
124 l_ref_date := NULL;
125 WHILE l_idx IS NOT NULL
126 LOOP
127 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
128 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
129 IF l_first_band THEN
130 l_first_band := FALSE;
131 l_ref_date := TRUNC(l_schedule(l_idx).START_DATE_TIME);
132 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
133 ELSE -- not first time
134 IF TRUNC(l_schedule(l_idx).START_DATE_TIME) = l_ref_date THEN
135 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
136 ELSE
137 l_ref_date := TRUNC(l_schedule(l_idx).END_DATE_TIME);
138 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
139 END IF;
140 END IF;
141 END IF;
142 END IF;
143 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
144 END LOOP;
145 --
146 ELSE -- p_days_or_hours is 'H'
147 --
148 l_day_start_time := '00:00';
149 l_day_end_time := '23:59';
150 WHILE l_idx IS NOT NULL
151 LOOP
152 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE ' || l_schedule(l_idx).FREE_BUSY_TYPE );
153 hr_utility.trace('l_schedule(l_idx).END_DATE_TIME ' || l_schedule(l_idx).END_DATE_TIME );
154 hr_utility.trace('l_schedule(l_idx).START_DATE_TIME ' || l_schedule(l_idx).START_DATE_TIME );
155
156 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
157 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE is not null ' || l_schedule(l_idx).FREE_BUSY_TYPE );
158 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
159 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE is FREE ' || l_schedule(l_idx).FREE_BUSY_TYPE );
160 hr_utility.trace('l_schedule(l_idx).END_DATE_TIME ' || l_schedule(l_idx).END_DATE_TIME );
161 hr_utility.trace('l_schedule(l_idx).START_DATE_TIME ' || l_schedule(l_idx).START_DATE_TIME );
162 IF l_schedule(l_idx).END_DATE_TIME < l_schedule(l_idx).START_DATE_TIME THEN
163 -- Skip this invalid slot which ends before it starts
164 NULL;
165 ELSE
166 IF TRUNC(l_schedule(l_idx).END_DATE_TIME) > TRUNC(l_schedule(l_idx).START_DATE_TIME) THEN
167 -- Start and End on different days
168 --
169 -- Get first day hours
170 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
171 hr_utility.trace('l_start_time ' || l_start_time);
172
173 SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
174 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
175 INTO p_duration
176 FROM DUAL;
177 -- hr_utility.trace('p_start_duration ' || p_start_duration);
178 hr_utility.trace('Start p_duration ' || p_duration);
179
180 --
181 -- Get last day hours
182 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
183 hr_utility.trace('l_end_time ' || l_end_time);
184 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
185 (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
186 INTO p_duration
187 FROM DUAL;
188 --hr_utility.trace('p_end_duration ' || p_end_duration);
189 hr_utility.trace('End p_duration ' || p_duration);
190 --
191 -- Get between full day hours
192 SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
193 INTO p_duration
194 FROM DUAL;
195 ELSE
196 -- Start and End on same day
197 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
198 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
199
200 hr_utility.trace('l_start_time ' || l_start_time);
201 hr_utility.trace('l_end_time ' || l_end_time);
202
203 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
204 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
205 INTO p_duration
206 FROM DUAL;
207 hr_utility.trace('duration l_idx '||l_idx||' ' ||p_duration);
208
209 END IF;
210 END IF;
211 END IF;
212 END IF;
213 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
214 END LOOP;
215 hr_utility.trace('duration ' ||p_duration);
216
217 p_duration := ROUND(p_duration,2);
218 --
219 END IF;
220 END IF;
221 --
222 hr_utility.set_location('Leaving psp_general.calc_sch_based_dur',20);
223 EXCEPTION
224 --
225 WHEN e_bad_time_format THEN
226 hr_utility.set_location('Leaving psp_general.calc_sch_based_dur',30);
227 hr_utility.set_location(SQLERRM,35);
228 RAISE;
229 WHEN OTHERS THEN
230 hr_utility.set_location('Leaving psp_general.calc_sch_based_dur',40);
231 hr_utility.set_location(SQLERRM,45);
232 RAISE;
233 END calc_sch_based_dur;
234
235 FUNCTION p_org_exists(organization_id1 IN NUMBER)
236 RETURN NUMBER IS
237 l_dummy CHAR(1);
238 BEGIN
239 BEGIN
240 SELECT 'x'
241 INTO l_dummy
242 FROM HR_ORGANIZATION_UNITS
243 WHERE organization_id = organization_id1 AND
244 ROWNUM = 1;
245 RETURN(0);
246 EXCEPTION
247 WHEN OTHERS THEN
248 RETURN(-1);
249 END;
250 END;
251 ---
252
253 PROCEDURE get_annual_salary (p_assignment_id in number,
254 p_session_date in date,
255 p_annual_salary out NOCOPY number) is
256 /***** Modified teh following cursor for R12 peformance fixes (bug 4507892)
257 CURSOR get_salary is
258 select ppp.proposed_salary,
259 ppb.pay_basis
260 from per_pay_proposals ppp,
261 per_assignments_f paf,
262 per_pay_bases ppb
263 where paf.assignment_id = p_assignment_id and
264 ppp.assignment_id = paf.assignment_id and
265 paf.pay_basis_id = ppb.pay_basis_id and
266 ppb.pay_basis in ('ANNUAL', 'MONTHLY') and
267 ppp.change_date = (select max(change_date) from per_pay_proposals ppp1
268 where ppp1.assignment_id = paf.assignment_id and
269 ppp1.approved = 'Y' and ppp1.change_date <= p_session_date);
270 End of comment for R12 performance fixes (bug 4507892) *****/
271
272 -- New cursor definition for bug fix 4507892
273 CURSOR get_salary IS
274 SELECT ppp.proposed_salary,
275 ppb.pay_basis
276 FROM per_pay_proposals ppp,
277 per_assignments_f paf,
278 per_pay_bases ppb
279 WHERE paf.assignment_id = p_assignment_id
280 AND ppp.assignment_id = paf.assignment_id
281 AND paf.pay_basis_id = ppb.pay_basis_id
282 AND ppb.pay_basis IN ('ANNUAL', 'MONTHLY')
283 AND ppp.change_date = (SELECT MAX(change_date)
284 FROM per_pay_proposals ppp1
285 WHERE ppp1.assignment_id = p_assignment_id
286 AND ppp1.approved = 'Y'
287 AND ppp1.change_date <= p_session_date);
288
289
290 l_annual_salary number(22) := 0;
291 l_pay_basis varchar2(30) := NULL;
292 BEGIN
293 OPEN get_salary;
294 fetch get_salary into l_annual_salary, l_pay_basis;
295
296 if get_salary%NOTFOUND then
297 p_annual_salary := 0;
298 else
299 begin
300 if l_pay_basis = 'ANNUAL' then
301 p_annual_salary := l_annual_salary;
302 elsif
303 l_pay_basis = 'MONTHLY' then
304 l_annual_salary := l_annual_salary * 12;
305 p_annual_salary := l_annual_salary;
306 else
307 p_annual_salary := 0;
308 end if;
309 end;
310 end if;
311
312 EXCEPTION
313 WHEN NO_DATA_FOUND then
314 p_annual_salary := 0;
315 WHEN TOO_MANY_ROWS then
316 p_annual_salary := 0;
317 WHEN OTHERS then
318 p_annual_salary := 0;
319 END;
320 PROCEDURE get_gl_ccid (p_payroll_id in number,
321 p_set_of_books_id in number,
322 p_cost_keyflex_id in number,
323 x_gl_ccid out NOCOPY number) is
324 CURSOR get_segment_maps_csr is
325 SELECT gl_account_segment,payroll_cost_segment
326 FROM PAY_PAYROLL_GL_FLEX_MAPS
327 WHERE payroll_id = p_payroll_id and
328 gl_set_of_books_id = p_set_of_books_id;
329
330 CURSOR get_chart_of_accounts_csr IS
331 SELECT chart_of_accounts_id
332 FROM GL_SETS_OF_BOOKS
333 WHERE set_of_books_id = p_set_of_books_id;
334
335 l_chart_of_accounts_id number := 0;
336 l_gl_segment varchar2(30) := NULL;
337 l_cost_segment varchar2(30) := NULL;
338 l_sql_string varchar2(2000) := NULL;
339 l_cursor INTEGER;
340 l_cost_value varchar2(22);
341 l_gl_ccid number(15);
342 l_rows INTEGER;
343
344 BEGIN
345 open get_segment_maps_csr;
346 LOOP
347 fetch get_segment_maps_csr into l_gl_segment, l_cost_segment;
348 EXIT WHEN get_segment_maps_csr%NOTFOUND;
349 --DBMS_OUTPUT.PUT_LINE(' DEBUG -- l_cost_segment ' || l_cost_segment);
350 --DBMS_OUTPUT.PUT_LINE(' DEBUG -- l_gl_segment ' || l_gl_segment);
351 l_rows := 0;
352 l_cost_value := 0;
353 --DBMS_OUTPUT.PUT_LINE(' DEBUG -- p_cost_keyflex_id ' || to_char(p_cost_keyflex_id));
354 l_cursor := dbms_sql.open_cursor;
355 dbms_sql.parse(l_cursor,'select ' || l_cost_segment || ' from pay_cost_allocation_keyflex where cost_allocation_keyflex_id = :p_cost_keyflex_id',dbms_sql.V7);
356 dbms_sql.bind_variable(l_cursor,'p_cost_keyflex_id',p_cost_keyflex_id);
357 dbms_sql.define_column(l_cursor,1,l_cost_value,22);
358 l_rows := dbms_sql.execute_and_fetch(l_cursor);
359 dbms_sql.column_value(l_cursor,1,l_cost_value);
360 --DBMS_OUTPUT.PUT_LINE(' DEBUG -- l_cost_value ' || l_cost_value);
361 dbms_sql.close_cursor(l_cursor);
362
363 l_sql_string := l_sql_string || ' and ' || l_gl_segment || ' = ''' || l_cost_value || '''';
364
365 --DBMS_OUTPUT.PUT_LINE(' DEBUG -- l_sql_string ' || l_sql_string);
366 END LOOP;
367 --dbms_output.put_line('DEBUG.....Crossed First Loop');
368 OPEN get_chart_of_accounts_csr;
369 fetch get_chart_of_accounts_csr into l_chart_of_accounts_id;
370 --dbms_output.put_line('DEBUG.....Crossed Fetch');
371 if get_chart_of_accounts_csr%NOTFOUND then
372 --dbms_output.put_line('DEBUG.....NotFound');
373 l_chart_of_accounts_id := 0;
374 end if;
375 --dbms_output.put_line('DEBUG.....NotFound');
376 --dbms_output.put_line('DEBUG.....Chart of accounts ' || to_char(l_chart_of_accounts_id));
377 --dbms_output.put_line('DEBUG.....Set of Books ' || to_char(p_set_of_books_id));
378 if l_chart_of_accounts_id <> 0 then
379 l_cursor :=dbms_sql.open_cursor;
380 dbms_sql.parse(l_cursor,'select code_combination_id from gl_code_combinations where chart_of_accounts_id = :p_chart_of_accounts_id ' || l_sql_string,dbms_sql.V7);
381 dbms_sql.bind_variable(l_cursor,'p_chart_of_accounts_id',l_chart_of_accounts_id);
382 dbms_sql.define_column(l_cursor,1,l_gl_ccid);
383 l_rows := dbms_sql.execute_and_fetch(l_cursor);
384 dbms_sql.column_value(l_cursor,1,l_gl_ccid);
385 if l_gl_ccid > 0 then
386 x_gl_ccid := l_gl_ccid;
387 else
388 x_gl_ccid := 0;
389 end if;
390
391 --DBMS_OUTPUT.PUT_LINE(' DEBUG -- l_gl_ccid ' || to_char(l_gl_ccid));
392 dbms_sql.close_cursor(l_cursor);
393 end if;
394
395 END;
396
397 FUNCTION business_days (low_date date,
398 high_date date,
399 p_assignment_id NUMBER DEFAULT NULL) return number is
400 l_no_of_days integer := 0;
401 curr_date date := trunc(low_date);
402 -- Introduced the following cursor for bug fix 5077073
403 /*Bug 5557724: to_char(some_date,'D') returns a number indicating the weekday. However, for a given date, this number
404 returned varies with NLS_TERRITORY. So replaced it with to_char(some_date,'DY') that gives the abbreviated day. */
405 CURSOR business_days_cur (p_low_date IN DATE,
406 p_high_date IN DATE) IS
407 SELECT SUM(DECODE(TO_CHAR(p_low_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), 'SUN', 0, 'SAT', 0, 1))
408 FROM DUAL
409 CONNECT BY 1=1
410 AND ROWNUM <= (p_high_date + 1) - p_low_date;
411
412 -- Added the following cursor to find whether a particular day is a working day or not when
413 -- working schedules is not enabled, Bug 6779790
414
415 CURSOR business_days2_cur (p_date IN DATE) IS
416 SELECT DECODE(TO_CHAR(p_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), 'SUN', 0, 'SAT', 0, 1)
417 FROM DUAL;
418
419 -- Introduced the following cursors for work schedules enh.
420 l_schedule_source VARCHAR2(100);
421 l_schedule VARCHAR2(100);
422 l_business_day NUMBER;
423 l_return_status NUMBER;
424 l_return_message VARCHAR2(100);
425 l_wrk_sch_exists NUMBER;
426 l_business_group_id NUMBER(15);
427 l_legislation_code VARCHAR2(10);
428 l_ws_table_name VARCHAR2(100);
429 l_work_schedule VARCHAR2(100);
430 l_effective_start_date DATE;
431 l_effective_end_date DATE;
432 l_no_of_days_in_chunk NUMBER;
433
434 CURSOR business_days_ws_cur (p_low_date IN DATE,
435 p_high_date IN DATE) IS
436 SELECT SUM(DECODE(hruserdt.get_table_value(l_business_group_id, l_ws_table_name, l_work_schedule,
437 TO_CHAR(p_low_date+ (ROWNUM-1), 'DY', 'nls_date_language=english'), p_low_date+ (ROWNUM-1)), 0, 0, 1))
438 FROM DUAL
439 CONNECT BY 1=1
440 AND ROWNUM <= (p_high_date + 1) - p_low_date;
441
442 CURSOR workschedule_config_cur IS
443 SELECT pcv_information1 work_schedules
444 FROM pqp_configuration_values
445 WHERE pcv_information_category = 'PSP_ENABLE_WORK_SCHEDULES'
446 AND legislation_code IS NULL
447 AND NVL(business_group_id, l_business_group_id) = l_business_group_id;
448
449 CURSOR business_group_id_cur IS
450 SELECT business_group_id
451 FROM per_assignments_f
452 WHERE assignment_id = p_assignment_id
453 AND effective_start_date <= high_date
454 AND effective_end_date >= low_date;
455
456 CURSOR legislation_code_cur IS
457 SELECT legislation_code
458 FROM per_business_groups_perf
459 WHERE business_group_id = l_business_group_id;
460
461 CURSOR wrk_sch_exists_cur IS
462 SELECT put.user_table_name,
463 puc.user_column_name,
464 GREATEST(assign.effective_start_date, low_date),
465 LEAST(assign.effective_end_date, high_date)
466 FROM pay_user_tables PUT,
467 pay_user_columns PUC,
468 hr_soft_coding_keyflex target,
469 per_all_assignments_f ASSIGN
470 WHERE PUC.USER_COLUMN_ID (+) = target.SEGMENT4
471 AND high_date >= ASSIGN.effective_start_date
472 AND low_date <= ASSIGN.effective_end_date
473 AND ASSIGN.assignment_id = p_assignment_id
474 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
475 AND target.enabled_flag = 'Y'
476 AND target.id_flex_num = (SELECT rule_mode
477 FROM pay_legislation_rules
478 WHERE legislation_code = l_legislation_code
479 AND rule_type = 'S')
480 AND NVL(PUC.business_group_id, l_business_group_id) = l_business_group_id
481 AND NVL(PUC.legislation_code, l_legislation_code) = l_legislation_code
482 AND PUC.user_table_id = PUT.user_table_id (+)
483 AND ( PUT.user_table_id IS NULL
484 OR PUT.user_table_name = (SELECT put.user_table_name
485 FROM hr_organization_information hoi,
486 pay_user_tables put
487 WHERE hoi.organization_id = l_business_group_id
488 AND hoi.org_information_context ='Work Schedule'
489 AND hoi.org_information1 = put.user_table_id));
490 -- end of changes for work schedules enh.
491
492 begin
493 /***** Commented for bug fix 5077073
494 while curr_date <= trunc(high_date) loop
495 if to_char(curr_date, 'D') NOT IN (1, 7) then
496 l_no_of_days := l_no_of_days + 1;
497 end if;
498 curr_date := curr_date + 1;
499 end loop;
500 End of comment for bug fix 5077073 *****/
501
502
503 IF (high_date < low_date) THEN
504 RETURN 0;
505 END IF;
506
507 OPEN business_group_id_cur;
508 FETCH business_group_id_cur INTO l_business_group_id;
509
510 OPEN legislation_code_cur;
511 FETCH legislation_code_cur INTO l_legislation_code;
512 CLOSE legislation_code_cur;
513
514 IF (p_assignment_id IS NULL) THEN
515 g_ws_option := NULL;
516 END IF;
517
518 IF (g_ws_option IS NULL) THEN
519 OPEN workschedule_config_cur;
520 FETCH workschedule_config_cur INTO g_ws_option;
521 CLOSE workschedule_config_cur;
522 END IF;
523
524 IF (g_ws_option = 'Y') THEN
525 calc_sch_based_dur('D', low_date, high_date, NULL, NULL, p_assignment_id, l_no_of_days);
526 /*curr_date := low_date;
527 l_no_of_days := 0;
528 LOOP
529 EXIT WHEN curr_date > high_date;
530 l_business_day := pay_core_ff_udfs.calculate_actual_hours_worked
531 (NULL,
532 p_assignment_id,
533 l_business_group_id,
534 NULL,
535 curr_date,
536 curr_date,
537 curr_date,
538 NULL,
539 NULL,
540 NULL,
541 NULL,
542 l_schedule_source,
543 l_schedule,
544 l_return_status,
545 l_return_message,
546 'D');
547
548 IF (l_business_day > 0) THEN
549 l_no_of_days := l_no_of_days + 1;
550 END IF;
551 curr_date := curr_date + 1;
552 END LOOP;*/
553
554 IF (l_no_of_days = 0) THEN
555 OPEN wrk_sch_exists_cur;
556 LOOP
557 FETCH wrk_sch_exists_cur INTO l_ws_table_name, l_work_schedule, l_effective_start_date, l_effective_end_date;
558 EXIT WHEN wrk_sch_exists_cur%NOTFOUND;
559
560 IF (l_work_schedule IS NULL) THEN
561 OPEN business_days_cur(l_effective_start_date, l_effective_end_date);
562 FETCH business_days_cur INTO l_no_of_days_in_chunk;
563 CLOSE business_days_cur;
564 ELSE
565 OPEN business_days_ws_cur(l_effective_start_date, l_effective_end_date);
566 FETCH business_days_ws_cur INTO l_no_of_days_in_chunk;
567 CLOSE business_days_ws_cur;
568 END IF;
569 l_no_of_days := l_no_of_days + l_no_of_days_in_chunk;
570 END LOOP;
571 CLOSE wrk_sch_exists_cur;
572 END IF;
573 ELSE
574 IF (low_date = high_date) THEN
575 OPEN business_days2_cur(low_date);
576 FETCH business_days2_cur INTO l_no_of_days;
577 CLOSE business_days2_cur;
578 ELSE
579 OPEN business_days_cur(low_date, high_date);
580 FETCH business_days_cur INTO l_no_of_days;
581 CLOSE business_days_cur;
582 END IF;
583 END IF;
584 CLOSE business_group_id_cur;
585
586 return l_no_of_days;
587 END business_days;
588
589 FUNCTION last_working_date (last_date date )
590 return date is
591 curr_date date := last_date;
592 begin
593 loop
594 /*Bug 5557724: to_char(some_date,'D') returns a number indicating the weekday. However, for a given date, this number
595 returned varies with NLS_TERRITORY. So replaced it with to_char(some_date,'DY') that gives the abbreviated day. */
596 if to_char(curr_date, 'DY', 'nls_date_language=english') in ('SUN','SAT')
597 then
598 curr_date := curr_date - 1;
599 else
600 exit;
601 end if;
602 end loop;
603 return curr_date;
604 end last_working_date;
605
606 FUNCTION get_gl_description(p_set_of_books_id IN NUMBER,
607 a_code_combination_id IN NUMBER) RETURN VARCHAR2 IS
608 ---------------------------------------------------History-----------------------------------------------------------
609 --DATE MODIFIED BY DESCRIPTION
610 -------- ----------- -----------
611 --02/10/99 Shu Lei Fix Bug #819605: undefined segments.
612 ---------------------------------------------------------------------------------------------------------------------
613 t_nc NUMBER(3);
614 t_desc VARCHAR2(1000);
615 nc NUMBER;
616 x_chart_of_accts VARCHAR2(20);
617
618 FUNCTION t_glccid_exists(code_combination_id1 IN NUMBER,chart_of_accounts_id1 IN NUMBER)
619 RETURN NUMBER IS
620 t_dummy CHAR(1);
621 BEGIN
622 IF code_combination_id1 IS NULL THEN
623 RETURN(-1);
624 END IF;
625 ---
626 SELECT 'x'
627 INTO t_dummy
628 FROM gl_code_combinations
629 WHERE code_combination_id = code_combination_id1 AND
630 chart_of_accounts_id = chart_of_accounts_id1 AND
631 ROWNUM = 1;
632 RETURN(0);
633 EXCEPTION
634 WHEN OTHERS THEN
635 RETURN(-2);
636 END;
637
638 BEGIN
639 nc := find_chart_of_accts(p_set_of_books_id,x_chart_of_accts);
640 IF nc = -1 THEN
641 RETURN('** Chart Of Accts Failed **');
642 END IF;
643 ---
644 t_nc := t_glccid_exists(a_code_combination_id,to_number(x_chart_of_accts));
645 IF t_nc = -1 THEN
646 RETURN(NULL);
647 ELSIF t_nc = -2 THEN
648 RETURN('** Invalid GL CCID '||to_char(a_code_combination_id)||' :: No Such Code Exists **');
649 END IF;
650 ---
651
652 /*-------Fix bug #819605: use AOL package to obtain segment descriptions-------*/
653 /*-------Note: if the output concatenated segment descriptions are truncated, modify the values in column concatenation_description_len of -----------*/
654 /*-------table fnd_id_flex_segments. The column determines how long the segment descriptions FND_FLEX_KEYVAL.concated_description will take.---------*/
655 IF FND_FLEX_KEYVAL.validate_ccid('SQLGL', 'GL#', x_chart_of_accts, a_code_combination_id ) THEN
656 t_desc := substr(FND_FLEX_KEYVAL.concatenated_descriptions, 1, 1000);
657 return(t_desc);
658 END IF;
659 END get_gl_description;
660
661 FUNCTION find_global_suspense(p_start_date_active IN DATE, -- DEFAULT NULL,Commented for bug fix 2635110
662 p_business_group_id IN NUMBER,
663 p_set_of_books_id IN NUMBER,
664 p_organization_account_id OUT NOCOPY NUMBER)
665 --- Valid return codes are
666 --- PROFILE_VAL_DATE_MATCHES Profile and Value and Date matching 'G'
667 --- NO_PROFILE_EXISTS No Profile
668 --- NO_VAL_DATE_MATCHES Profile and Either Value/date do not match with 'G'
669 --- NO_GLOBAL_ACCT_EXISTS No 'G' exists
670 RETURN VARCHAR2 IS
671 --Modified the cursor for bug 2056877,Removed nvl from end_date check.
672 CURSOR global_susp_exists IS
673 SELECT organization_id, organization_account_id,rownum
674 FROM psp_organization_accounts
675 WHERE account_type_code = 'G' AND
676 business_group_id = p_business_group_id AND
677 set_of_books_id = p_set_of_books_id AND
678 (p_start_date_active IS NULL OR
679 p_start_date_active BETWEEN start_date_active AND end_date_active);
680
681
682 l_organization_id NUMBER(15);
683 l_count NUMBER(2) := 0;
684 l_global_susp_acct VARCHAR2(100);
685 l_organization_account_id NUMBER(9);
686 BEGIN
687 p_organization_account_id := -1;
688 l_global_susp_acct := psp_general.get_specific_profile('PSP_GLOBAL_SUSP_ACC_ORG');
689 IF l_global_susp_acct IS NULL THEN
690 -- --dbms_output.put_line('NO_PROFILE_EXISTS');
691 RETURN('NO_PROFILE_EXISTS');
692 END IF;
693 -- --dbms_output.put_line(l_global_susp_acct);
694 OPEN global_susp_exists;
695 LOOP
696 FETCH global_susp_exists INTO
697 l_organization_id,l_organization_account_id,l_count;
698 EXIT WHEN global_susp_exists%NOTFOUND;
699 ---
700 IF p_org_exists(l_organization_id) = 0 THEN
701 /* Followin code is added for bug 2056877,Validating the global suspense account to be
702 same as profile value,If it is not then returning 'NO_VAL_DATE_MATCHES' */
703 IF to_number(l_global_susp_acct) = l_organization_id THEN
704 --dbms_output.put_line('PROFILE_VAL_DATE_MATCHES');
705 p_organization_account_id := l_organization_account_id;
706 RETURN('PROFILE_VAL_DATE_MATCHES');
707 ELSE
708 RETURN('NO_VAL_DATE_MATCHES');
709
710 END IF; --Bug 2056877.
711 END IF;
712 END LOOP;
713 CLOSE global_susp_exists;
714 ---
715 IF l_count = 0 THEN
716 --dbms_output.put_line(l_count||'NO_GLOBAL_ACCT_EXISTS');
717 RETURN('NO_GLOBAL_ACCT_EXISTS');
718 END IF;
719 ---
720 --dbms_output.put_line('NO_VAL_DATE_MATCHES');
721 RETURN('NO_VAL_DATE_MATCHES');
722 END;
723 ----
724 FUNCTION find_chart_of_accts(p_set_of_books_id IN NUMBER,
725 p_chart_of_accts OUT NOCOPY VARCHAR2)
726 RETURN NUMBER
727 IS
728 t_set_of_books_id NUMBER(15) := p_set_of_books_id;
729 t_chart_of_accounts_id NUMBER(15) := NULL;
730 CURSOR C1 (c_set_of_books_id NUMBER) IS
731 SELECT chart_of_accounts_id
732 FROM gl_sets_of_books
733 WHERE set_of_books_id = c_set_of_books_id;
734 BEGIN
735 ---
736 OPEN C1(t_set_of_books_id);
737 FETCH C1 INTO t_chart_of_accounts_id;
738 CLOSE C1;
739 ---
740 IF t_chart_of_accounts_id IS NULL THEN
741 p_chart_of_accts := NULL;
742 RETURN(-1);
743 END IF;
744 ---
745 p_chart_of_accts := to_char(t_chart_of_accounts_id);
746 RETURN(0);
747 END;
748 ----
749 PROCEDURE TRANSACTION_CHANGE_PURGEBLE IS
750 v_flag pa_transaction_sources.purgeable_flag%TYPE;
751
752 BEGIN
753 -- 2431917: changed OLD to GOLD, introduced GOLDE
754 BEGIN
755 SELECT purgeable_flag INTO v_flag
756 FROM pa_transaction_sources
757 WHERE transaction_source = 'GOLDE'
758 FOR UPDATE OF purgeable_flag ;
759
760 IF v_flag='Y' THEN
761 --v_flag :='N';
762 UPDATE pa_transaction_sources
763 SET purgeable_flag = 'N' --- v_flag
764 WHERE transaction_source = 'GOLDE';
765 COMMIT;
766 END IF;
767 EXCEPTION
768 WHEN NO_DATA_FOUND THEN
769 NULL;
770 END;
771
772 SELECT purgeable_flag INTO v_flag
773 FROM pa_transaction_sources
774 WHERE transaction_source = 'GOLD'
775 FOR UPDATE OF purgeable_flag ;
776
777 IF v_flag='Y' THEN
778 ---v_flag :='N';
779 UPDATE pa_transaction_sources
780 SET purgeable_flag = 'N' ---v_flag
781 WHERE transaction_source = 'GOLD';
782 COMMIT;
783 END IF;
784 EXCEPTION
785 WHEN NO_DATA_FOUND THEN
786 NULL;
787
788 END TRANSACTION_CHANGE_PURGEBLE;
789
790 --------------- P O E T A E F F E C T I V E D A T E ---------------------------------
791 -- When GMS is installed
792 PROCEDURE poeta_effective_date(p_payroll_end_date IN DATE,
793 p_project_id IN NUMBER,
794 p_award_id IN NUMBER,
795 p_task_id IN NUMBER,
796 p_effective_date OUT NOCOPY DATE,
797 p_return_status OUT NOCOPY VARCHAR2) IS
798 l_project_end_date DATE;
799 l_award_end_date DATE;
800 l_completion_date DATE;
801 l_msg_id number(9);
802 l_poeta_effective_date DATE;
803 BEGIN
804 ----dbms_output.put_line('starting poeta_effective_date');
805 --insert_into_psp_stout( 'starting poeta_effective_date');
806
807 SELECT nvl(completion_date,p_payroll_end_date)
808 INTO l_project_end_date
809 FROM pa_projects_all
810 WHERE project_id = p_project_id;
811
812 SELECT nvl(end_date_active,p_payroll_end_date)
813 INTO l_award_end_date
814 FROM gms_awards_all -- Bug 6908158
815 WHERE award_id = p_award_id;
816
817 SELECT nvl(completion_date,p_payroll_end_date)
818 INTO l_completion_date
819 FROM pa_tasks
820 WHERE task_id = p_task_id;
821
822 SELECT least(p_payroll_end_date,l_project_end_date,l_award_end_date,l_completion_date)
823 INTO l_poeta_effective_date
824 FROM dual;
825
826 IF (l_poeta_effective_date < p_payroll_end_date ) THEN
827 p_effective_date := p_payroll_end_date ;
828 ELSE
829 p_effective_date := l_poeta_effective_date ;
830 END IF;
831 p_return_status := fnd_api.g_ret_sts_success;
832 EXCEPTION
833 WHEN OTHERS THEN
834 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','POETA_EFFECTIVE_DATE');
835 p_return_status := fnd_api.g_ret_sts_unexp_error;
836 END poeta_effective_date;
837
838 --------------- P O E T A E F F E C T I V E D A T E ---------------------------------
839 -- When GMS is not installed
840 PROCEDURE poeta_effective_date(p_payroll_end_date IN DATE,
841 p_project_id IN NUMBER,
842 p_task_id IN NUMBER,
843 p_effective_date OUT NOCOPY DATE,
844 p_return_status OUT NOCOPY VARCHAR2) IS
845 l_project_end_date DATE;
846 l_completion_date DATE;
847 l_msg_id number(9);
848 l_poeta_effective_date DATE;
849 BEGIN
850 ----dbms_output.put_line('starting poeta_effective_date');
851 --insert_into_psp_stout( 'starting poeta_effective_date');
852
853 SELECT nvl(completion_date,p_payroll_end_date)
854 INTO l_project_end_date
855 FROM pa_projects_all
856 WHERE project_id = p_project_id;
857
858
859 SELECT nvl(completion_date,p_payroll_end_date)
860 INTO l_completion_date
861 FROM pa_tasks
862 WHERE task_id = p_task_id;
863
864 SELECT least(p_payroll_end_date,l_project_end_date,l_completion_date)
865 INTO l_poeta_effective_date
866 FROM dual;
867
868 IF (l_poeta_effective_date < p_payroll_end_date ) THEN
869 p_effective_date := p_payroll_end_date ;
870 ELSE
871 p_effective_date := l_poeta_effective_date ;
872 END IF;
873 p_return_status := fnd_api.g_ret_sts_success;
874 EXCEPTION
875 WHEN OTHERS THEN
876 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','POETA_EFFECTIVE_DATE');
877 p_return_status := fnd_api.g_ret_sts_unexp_error;
878 END poeta_effective_date;
879
880
881
882 --------------- G E T G M S E F F E C T I V E D A T E ---------------------------------
883 /**********************************************************
884 Created By:skotwal
885
886 Date Created By:14-SEP-2001
887
888 Purpose:
889 This procedure "get_gms_effective_date" returns the effective end date of the last
890 active primary assignment for the period_id passed with respect to effective date
891 passed to it. For bug 1994421
892
893 Know limitations, enhancements or remarks
894
895 Change History
896
897 Who When What
898 skotwal 14-SEP-2001 Created
899 lveerubh 18-OCT-2001 Bug 2039161 : Introduced the period_of_service_id check
900 to ignore the new record created each time a employee is terminated
901 venkat 25-jun-2002 Bug 2426343: Return NULL if primary assignment was never active wrt eff date.
902 ***************************************************************/
903
904 PROCEDURE get_gms_effective_date(p_person_id in number, p_effective_date in out NOCOPY date)
905 IS
906 l_effective_date DATE;
907 l_count number:= 0;
908
909 CURSOR active_assign_cur
910 IS
911 SELECT count(*)
912 FROM per_all_assignments_f ainner,
913 per_assignment_status_types binner
914 WHERE ainner.person_id = p_person_id
915 AND ainner.primary_flag = 'Y'
916 AND ainner.assignment_status_type_id= binner.assignment_status_type_id
917 AND binner.per_system_status = 'ACTIVE_ASSIGN'
918 AND p_effective_date between ainner.effective_start_date and ainner.effective_end_date
919 AND ainner.period_of_service_id IS NOT NULL;
920
921 CURSOR effective_date_cur
922 IS
923 SELECT max(a.effective_end_date)
924 FROM per_all_assignments_f a,
925 per_assignment_status_types b
926 WHERE a.person_id = p_person_id
927 AND a.primary_flag = 'Y'
928 AND a.assignment_status_type_id = b.assignment_status_type_id
929 AND b.per_system_status = 'ACTIVE_ASSIGN'
930 AND a.period_of_service_id IS NOT NULL -- Included for the Bug fix 2039161
931 AND (trunc(a.effective_end_date) <= trunc(p_effective_date));
932
933 BEGIN
934 OPEN active_assign_cur;
935 FETCH active_assign_cur INTO l_count;
936 CLOSE active_assign_cur;
937
938 IF (l_count = 0) THEN
939 -- If Assignment is not active for p_effective date then return max(effective_date) before p_effective_date
940 OPEN effective_date_cur;
941 FETCH effective_date_cur INTO l_effective_date;
942 if effective_date_cur%NOTFOUND then -- introduced for bug 2426343.
943 p_Effective_date := NULL;
944 else
945 p_effective_date:=l_effective_date;
946 end if;
947 CLOSE effective_date_cur;
948 END IF;
949 END get_gms_effective_date ;
950
951
952 PROCEDURE MULTIORG_CLIENT_INFO(
953 p_gl_set_of_bks_id OUT NOCOPY NUMBER,
954 p_business_group_id OUT NOCOPY NUMBER,
955 p_operating_unit OUT NOCOPY NUMBER,
956 p_pa_gms_install_options OUT NOCOPY VARCHAR2) IS
957
958 l_pa_install NUMBER := 0;
959 l_gms_install NUMBER := 0;
960
961 begin
962 fnd_profile.get('GL_SET_OF_BKS_ID', p_gl_set_of_bks_id);
963
964 fnd_profile.get('PER_BUSINESS_GROUP_ID', p_business_group_id);
965
966 fnd_profile.get('ORG_ID',p_operating_unit);
967
968 -- --dbms_output.put_line('bg is ' || to_char(p_business_group_id));
969 -- --dbms_output.put_line('sob is ' || to_char(p_gl_set_of_bks_id));
970 -- --dbms_output.put_line('mo is ' || to_char(p_operating_unit));
971
972 -- Check whether Multi-Org is implemented for the BG, SOB and ORG_ID combination.
973
974 -- initialize multiorg setup
975 init_moac;
976
977 select count(*)
978 into l_pa_install
979 from pa_implementations_all p
980 where business_group_id = p_business_group_id
981 and set_of_books_id = p_gl_set_of_bks_id
982 -- Commented for Bug 5498280: MOAC changes
983 -- and nvl(org_id,-999) = nvl(p_operating_unit,-999);
984 -- Added nvl by Neerav for Bug 1538262
985 and ((mo_global.get_current_org_id is NULL and mo_global.check_access(p.org_id) = 'Y')
986 or ( mo_global.get_current_org_id is NOT NULL and p.org_id = mo_global.get_current_org_id ));
987 -- --dbms_output.put_line('l_pa_install is ' || to_char(l_pa_install));
988
989 if (l_pa_install > 0) then
990 -- if (gms_install.site_enabled) then
991
992 -- Commented for Bug 5498280: MOAC changes
993 /*
994 -- Check if Multi-Org is implemented for the ORG_ID..
995 -- If p_operating_unit added by Neerav for Bug 1538262
996 if p_operating_unit is NOT NULL then
997
998 if (gms_install.enabled(p_operating_unit)) then
999 l_gms_install := 1;
1000 else
1001 l_gms_install := 0;
1002 end if;
1003
1004 else
1005 */
1006 if (gms_install.enabled) then
1007 l_gms_install := 1;
1008 else
1009 l_gms_install := 0;
1010 end if;
1011 -- end if;
1012
1013
1014 end if;
1015
1016 -- --dbms_output.put_line('l_gms_install is ' || to_char(l_gms_install));
1017
1018 if (l_pa_install > 0) and (l_gms_install > 0) then
1019 p_pa_gms_install_options := 'PA_GMS';
1020 elsif (l_pa_install > 0) and (l_gms_install = 0) then
1021 p_pa_gms_install_options := 'PA_ONLY';
1022 else
1023 p_pa_gms_install_options := 'NO_PA_GMS';
1024 end if;
1025
1026 exception
1027 WHEN OTHERS THEN
1028 fnd_msg_pub.add_exc_msg('PSP_GENERAL',SQLERRM);
1029 raise_application_error(SQLCODE, SQLERRM);
1030 end;
1031
1032 FUNCTION get_specific_profile(
1033 p_profile_name IN VARCHAR2)
1034 return VARCHAR2 IS
1035
1036 l_profile_value VARCHAR2(80);
1037 BEGIN
1038 fnd_profile.get(p_profile_name, l_profile_value);
1039
1040 if l_profile_value is not null
1041 then
1042 return l_profile_value;
1043 else
1044 return NULL;
1045 end if;
1046
1047 END;
1048
1049 -- Wrapper function for checking whether LD is implemented..used by PSB
1050
1051 FUNCTION IS_LD_ENABLED (P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1052
1053 -- The following function is used to check presence of a clearing account
1054
1055 FUNCTION CHECK_CLEARING_ACCT (P_BUSINESS_GROUP_ID IN NUMBER) RETURN BOOLEAN IS
1056
1057 l_clearing_account NUMBER;
1058 BEGIN
1059
1060 SELECT count(*)
1061 INTO l_clearing_account
1062 FROM psp_clearing_account
1063 WHERE business_group_id = p_business_group_id;
1064
1065 IF l_clearing_account > 0 THEN
1066 RETURN TRUE;
1067 ELSE
1068 RETURN FALSE;
1069 END IF;
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 RETURN FALSE;
1074 END;
1075
1076 -- The following function is used to check presence of a generic suspense account
1077
1078 FUNCTION CHECK_GENERIC_SUSP_ACCT (P_BUSINESS_GROUP_ID IN NUMBER) RETURN BOOLEAN IS
1079 l_gen_susp_acct NUMBER;
1080 BEGIN
1081 SELECT count(*)
1082 INTO l_gen_susp_acct
1083 FROM psp_organization_accounts
1084 WHERE business_group_id = p_business_group_id;
1085
1086 IF l_gen_susp_acct > 0 then
1087 return TRUE;
1088 ELSE
1089 return FALSE;
1090 END IF;
1091
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 RETURN FALSE;
1095 END;
1096
1097 BEGIN
1098
1099 IF (CHECK_CLEARING_ACCT(P_BUSINESS_GROUP_ID) AND CHECK_GENERIC_SUSP_ACCT(P_BUSINESS_GROUP_ID))
1100 THEN
1101 RETURN FND_API.G_TRUE ;
1102 ELSE
1103 RETURN FND_API.G_FALSE;
1104 END IF;
1105
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 RETURN FND_API.G_FALSE;
1109 END;
1110
1111 -- The following function is used to perform an Award Date Validation
1112 FUNCTION AWARD_DATE_VALIDATION(P_AWARD_ID IN NUMBER,
1113 P_START_DATE IN DATE,
1114 P_END_DATE IN DATE) RETURN BOOLEAN IS
1115 x_award_start_date DATE;
1116 x_award_end_date DATE;
1117 BEGIN
1118 select nvl(preaward_date,start_date_active), end_date_active
1119 into
1120 x_award_start_date, x_award_end_date
1121 from gms_awards_all --Bug 6908158
1122 where award_id = p_award_id;
1123
1124 IF (p_start_date NOT BETWEEN x_award_start_date AND
1125 NVL(x_award_end_date, fnd_date.canonical_to_date('4712/12/31'))) OR
1126 (NVL(p_end_date, fnd_date.canonical_to_date('4712/12/31')) NOT BETWEEN x_award_start_date AND
1127 NVL(x_award_end_date, fnd_date.canonical_to_date('4712/12/31'))) THEN
1128 return(FALSE);
1129 ELSE
1130 return(TRUE);
1131 END IF;
1132 EXCEPTION
1133 WHEN OTHERS
1134 THEN
1135 return (FALSE);
1136
1137 END AWARD_DATE_VALIDATION;
1138
1139 /**********************************************************
1140 Created By : lveerubh
1141
1142 Date Created By : 04-OCT-2001
1143
1144 Purpose : This function returns concatenated gl segment values . This is introduced to be used in reports
1145 to display the segment values rather than gl description.
1146
1147 Know limitations, enhancements or remarks
1148
1149 Change History
1150
1151 Who When What
1152 Lveerubh 04-OCT-2001 Created the function
1153
1154
1155 **********************************************/
1156
1157 FUNCTION get_gl_values(p_set_of_books_id IN NUMBER,
1158 a_code_combination_id IN NUMBER) RETURN VARCHAR2 IS
1159 ---------------------------------------------------History-----------------------------------------------------------
1160 --DATE MODIFIED BY DESCRIPTION
1161 -------- ----------- -----------
1162 --02/10/99 Shu Lei Fix Bug #819605: undefined segments.
1163 ---------------------------------------------------------------------------------------------------------------------
1164 t_nc NUMBER(3);
1165 t_values VARCHAR2(1000);
1166 nc NUMBER;
1167 x_chart_of_accts VARCHAR2(20);
1168
1169 FUNCTION t_glccid_exists(code_combination_id1 IN NUMBER,chart_of_accounts_id1 IN NUMBER)
1170 RETURN NUMBER IS
1171 t_dummy CHAR(1);
1172 BEGIN
1173 IF code_combination_id1 IS NULL THEN
1174 RETURN(-1);
1175 END IF;
1176 ---
1177 SELECT 'x'
1178 INTO t_dummy
1179 FROM gl_code_combinations
1180 WHERE code_combination_id = code_combination_id1 AND
1181 chart_of_accounts_id = chart_of_accounts_id1 AND
1182 ROWNUM = 1;
1183 RETURN(0);
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 RETURN(-2);
1187 END;
1188 BEGIN
1189 nc := find_chart_of_accts(p_set_of_books_id,x_chart_of_accts);
1190 IF nc = -1 THEN
1191 RETURN('** Chart Of Accts Failed **');
1192 END IF;
1193 ---
1194 t_nc := t_glccid_exists(a_code_combination_id,to_number(x_chart_of_accts));
1195 IF t_nc = -1 THEN
1196 RETURN(NULL);
1197 ELSIF t_nc = -2 THEN
1198 RETURN('** Invalid GL CCID '||to_char(a_code_combination_id)||' :: No Such Code Exists **');
1199 END IF;
1200 ---
1201 IF FND_FLEX_KEYVAL.validate_ccid('SQLGL', 'GL#', x_chart_of_accts, a_code_combination_id ) THEN
1202 t_values := FND_FLEX_KEYVAL.concatenated_values;
1203 return(t_values);
1204 END IF;
1205 END get_gl_values;
1206
1207 -- Following functions added by Ritesh on 14-NOV-2001 for Bug:2103460
1208 --*************************************
1209 ---- FUNCTION get_person_name
1210 --*************************************
1211 -- This function returns the full_name of the person. If there are multiple
1212 -- records it will get the name which is valid on the effective/distribution date. If person_id
1213 -- or effective/distribution date is invalid, it will return an error message.
1214 -- This function is called from psp_payroll_interface_v and psp_distribution_interface_v
1215
1216 FUNCTION get_person_name
1217 (p_person_id IN NUMBER,
1218 p_effective_date IN DATE)
1219 RETURN VARCHAR2 IS
1220
1221 CURSOR c1 IS
1222 SELECT full_name
1223 FROM per_all_people_f ppf
1224 WHERE ppf.person_id = p_person_id
1225 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1226
1227 v_name VARCHAR2(240);
1228 BEGIN
1229 OPEN c1;
1230 FETCH c1 INTO v_name;
1231
1232 IF c1%NOTFOUND
1233 THEN
1234 CLOSE c1;
1235 -- RETURN ('Person Name Not Found on Effective Date');
1236 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_NON_PERSON_NOT_FOUND'));
1237 END IF;
1238
1239 CLOSE c1;
1240
1241 RETURN(v_name);
1242
1243 END get_person_name;
1244
1245 --*************************************
1246 ---- FUNCTION get_assignment_num
1247 --*************************************
1248 -- This function returns assignment number for the given assignment_id and effective_date/distribution_date.
1249 -- If no row exists for the given parameter, it will return an error message.
1250 -- This function is called from psp_payroll_interface_v and psp_distribution_interface_v
1251
1252 FUNCTION get_assignment_num
1253 (p_assignment_id IN NUMBER,
1254 p_effective_date IN DATE)
1255 RETURN VARCHAR2 IS
1256
1257 CURSOR c1 IS
1258 SELECT assignment_number
1259 FROM per_all_assignments_f paf
1260 WHERE paf.assignment_id = p_assignment_id
1261 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
1262 AND period_of_service_id IS NOT NULL;
1263
1264 v_name VARCHAR2(30);
1265
1266 BEGIN
1267 OPEN c1;
1268 FETCH c1 INTO v_name;
1269
1270 IF c1%NOTFOUND
1271 THEN
1272 CLOSE c1;
1273 -- RETURN ('Assg Num Not Found on Eff Date');
1274 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_NON_ASSIGNMENT_NOT_FOUND'));
1275 END IF;
1276
1277 CLOSE c1;
1278
1279 RETURN(v_name);
1280
1281 END get_assignment_num;
1282
1283 --*************************************
1284 ---- FUNCTION get_payroll_name
1285 --*************************************
1286 -- This function returns the payroll name for the given payroll_id and effective_date/distribution_date.
1287 -- If no row exists for the given parameters, an error message is returned.
1288 -- This function is called from psp_payroll_interface_v and psp_distribution_interface_v
1289
1290
1291 FUNCTION get_payroll_name
1292 (p_payroll_id IN NUMBER,
1293 p_effective_date IN DATE)
1294 RETURN VARCHAR2 IS
1295
1296 CURSOR c1 IS
1297 SELECT payroll_name
1298 FROM pay_all_payrolls_f pap
1299 WHERE pap.payroll_id = p_payroll_id
1300 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1301
1302 v_name VARCHAR2(80);
1303
1304 BEGIN
1305 OPEN c1;
1306 FETCH c1 INTO v_name;
1307
1308 IF c1%NOTFOUND
1309 THEN
1310 CLOSE c1;
1311 -- RETURN ('Payroll Name Not Found on Effective Date');
1312 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_NON_PAYROLL_NOT_FOUND'));
1313 END IF;
1314
1315 CLOSE c1;
1316
1317 RETURN(v_name);
1318
1319 END get_payroll_name;
1320
1321 -- End additions for Bug:2103460
1322 --------------------------------------------------------------
1323
1324 /* Commented the following procedure for bug fix 2397883
1325 -- Introduced the following procedure for bug 2209483 for IGW LD Integration
1326 PROCEDURE igw_percent_effort (p_person_id IN NUMBER,
1327 p_award_id IN NUMBER,
1328 p_effective_date IN DATE,
1329 p_percent_effort OUT NOCOPY NUMBER,
1330 p_msg_data OUT NOCOPY VARCHAR2,
1331 p_return_status OUT NOCOPY VARCHAR2)
1332 IS
1333 CURSOR percent_effort_cur IS
1334 SELECT NVL(SUM(schedule_percent), 0)
1335 FROM per_assignments_f paf,
1336 psp_schedule_hierarchy psh,
1337 psp_schedule_lines psl
1338 WHERE paf.person_id = p_person_id
1339 AND TRUNC(p_effective_date) BETWEEN paf.effective_start_date AND paf.effective_end_date
1340 AND psh.assignment_id = paf.assignment_id
1341 AND psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
1342 AND psl.award_id = p_award_id
1343 AND TRUNC(p_effective_date) BETWEEN psl.schedule_begin_date AND psl.schedule_end_date;
1344
1345 l_msg_count NUMBER;
1346
1347 BEGIN
1348 OPEN percent_effort_cur;
1349 FETCH percent_effort_cur INTO p_percent_effort;
1350 CLOSE percent_effort_cur;
1351 p_return_status := fnd_api.g_ret_sts_success;
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 p_return_status := fnd_api.g_ret_sts_unexp_error;
1355 fnd_msg_pub.add_exc_msg
1356 (p_pkg_name => 'PSP_GENERAL',
1357 p_procedure_name => 'IGW_PERCENT_EFFORT');
1358 fnd_msg_pub.count_and_get
1359 (p_count => l_msg_count,
1360 p_data => p_msg_data);
1361 END igw_percent_effort;
1362
1363 End of bug fix 2397883 */
1364
1365 -- Introduced the following for bug fix 2635110
1366 FUNCTION get_project_number (p_project_id IN NUMBER) RETURN VARCHAR2 IS
1367 CURSOR project_number_cur IS
1368 SELECT segment1
1369 FROM pa_projects_all
1370 WHERE project_id = p_project_id;
1371
1372 l_project_number VARCHAR2(200);
1373
1374 BEGIN
1375 OPEN project_number_cur;
1376 FETCH project_number_cur INTO l_project_number;
1377 IF (project_number_cur%NOTFOUND) THEN
1378 fnd_message.set_name('PSP', 'PSP_PROJECT_NOT_FOUND');
1379 l_project_number := fnd_message.get;
1380 END IF;
1381 CLOSE project_number_cur;
1382 RETURN l_project_number;
1383 END get_project_number;
1384
1385 FUNCTION get_task_number (p_task_id IN NUMBER) RETURN VARCHAR2 IS
1386 CURSOR task_number_cur IS
1387 SELECT task_number
1388 FROM pa_tasks
1389 WHERE task_id = p_task_id;
1390
1391 l_task_number VARCHAR2(200);
1392
1393 BEGIN
1394 OPEN task_number_cur;
1395 FETCH task_number_cur INTO l_task_number;
1396 IF (task_number_cur%NOTFOUND) THEN
1397 fnd_message.set_name('PSP', 'PSP_TASK_NOT_FOUND');
1398 l_task_number := fnd_message.get;
1399 END IF;
1400 CLOSE task_number_cur;
1401 RETURN l_task_number;
1402 END get_task_number;
1403
1404 FUNCTION get_award_number (p_award_id IN NUMBER) RETURN VARCHAR2 IS
1405 CURSOR award_number_cur IS
1406 SELECT award_number
1407 FROM gms_awards_all
1408 WHERE award_id = p_award_id;
1409
1410 l_award_number VARCHAR2(200);
1411
1412 cursor default_award_cur is ---- for pregen form for bug 5643110/5742525
1413 select default_dist_award_number
1414 from gms_implementations
1415 where award_distribution_option = 'Y'
1416 and default_dist_award_id = p_award_id;
1417 BEGIN
1418 OPEN award_number_cur;
1419 FETCH award_number_cur INTO l_award_number;
1420 IF (award_number_cur%NOTFOUND) THEN
1421 open default_Award_cur;
1422 fetch default_Award_cur into l_Award_number;
1423 if default_Award_cur%notfound then
1424 fnd_message.set_name('PSP', 'PSP_AWARD_NOT_FOUND');
1425 l_award_number := fnd_message.get;
1426 end if;
1427 close default_award_cur;
1428 END IF;
1429 CLOSE award_number_cur;
1430 RETURN l_award_number;
1431 END get_award_number;
1432
1433
1434 FUNCTION get_org_name (p_org_id IN NUMBER) RETURN VARCHAR2 IS
1435 CURSOR org_name_cur IS
1436 SELECT name
1437 FROM hr_all_organization_units
1438 WHERE organization_id = p_org_id;
1439
1440 l_org_name hr_all_organization_units.name%TYPE;
1441
1442 BEGIN
1443 OPEN org_name_cur;
1444 FETCH org_name_cur INTO l_org_name;
1445 IF (org_name_cur%NOTFOUND) THEN
1446 fnd_message.set_name('PSP', 'PSP_ORG_NOT_FOUND');
1447 l_org_name := fnd_message.get;
1448 END IF;
1449 CLOSE org_name_cur;
1450 RETURN l_org_name;
1451 END get_org_name;
1452
1453 FUNCTION get_period_name (p_period_id IN NUMBER) RETURN VARCHAR2 IS
1454 CURSOR period_name_cur IS
1455 SELECT period_name
1456 FROM per_time_periods
1457 WHERE time_period_id = p_period_id;
1458
1459 l_period_name per_time_periods.period_name%TYPE;
1460
1461 BEGIN
1462 OPEN period_name_cur;
1463 FETCH period_name_cur INTO l_period_name;
1464 IF (period_name_cur%NOTFOUND) THEN
1465 fnd_message.set_name('PSP', 'PSP_PERIOD_NOT_FOUND');
1466 l_period_name := fnd_message.get;
1467 END IF;
1468 CLOSE period_name_cur;
1469 RETURN l_period_name;
1470 END get_period_name;
1471
1472 FUNCTION get_element_name (p_element_type_id IN NUMBER,
1473 p_effective_date IN DATE) RETURN VARCHAR2 IS
1474 CURSOR element_name_cur IS
1475 SELECT element_name
1476 FROM pay_element_types_f
1477 WHERE element_type_id = p_element_type_id
1478 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1479
1480 l_element_name pay_element_types_f.element_name%TYPE;
1481
1482 BEGIN
1483 OPEN element_name_cur;
1484 FETCH element_name_cur INTO l_element_name;
1485 IF (element_name_cur%NOTFOUND) THEN
1486 fnd_message.set_name('PSP', 'PSP_ELEMENT_NOT_FOUND');
1487 l_element_name := fnd_message.get;
1488 END IF;
1489 CLOSE element_name_cur;
1490 RETURN l_element_name;
1491 END get_element_name;
1492
1493 -- Introduced the following for bug fix 4189270
1494 FUNCTION get_element_name (p_element_type_id IN NUMBER) RETURN VARCHAR2 IS
1495 CURSOR element_name_cur IS
1496 SELECT element_name
1497 FROM pay_element_types_f
1498 WHERE element_type_id = p_element_type_id
1499 AND ( (TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date)
1500 OR (effective_start_date = (SELECT MIN(effective_start_date)
1501 FROM pay_element_types_f petf2
1502 WHERE petf2.element_type_id = p_element_type_id)));
1503
1504 l_element_name pay_element_types_f.element_name%TYPE;
1505
1506 BEGIN
1507 OPEN element_name_cur;
1508 FETCH element_name_cur INTO l_element_name;
1509 CLOSE element_name_cur;
1510
1511 RETURN l_element_name;
1512 END get_element_name;
1513 -- End of bug fix 4189270
1514
1515 FUNCTION get_source_type (p_source_type IN VARCHAR2,
1516 p_source_code IN VARCHAR2) RETURN VARCHAR2 IS
1517 CURSOR source_type_cur IS
1518 SELECT description
1519 FROM psp_payroll_sources
1520 WHERE source_type = p_source_type
1521 AND source_code = p_source_code;
1522
1523 l_source_type psp_payroll_sources.description%TYPE;
1524
1525 BEGIN
1526 OPEN source_type_cur;
1527 FETCH source_type_cur INTO l_source_type;
1528 IF (source_type_cur%NOTFOUND) THEN
1529 fnd_message.set_name('PSP', 'PSP_SOURCE_TYPE_NOT_FOUND');
1530 l_source_type := fnd_message.get;
1531 END IF;
1532 CLOSE source_type_cur;
1533 RETURN l_source_type;
1534 END get_source_type;
1535
1536 FUNCTION get_status_description (p_status_code IN VARCHAR2) RETURN VARCHAR2 IS
1537 /***** Modified the following cursor for R12 performance fixes (bug 4507892)
1538 CURSOR status_description_cur IS
1539 SELECT MEANING
1540 FROM PSP_LOOKUPS
1541 WHERE lookup_code = p_status_code
1542 AND lookup_type = 'PSP_STATUS';
1543 End of comment for R12 performance fixes (bug 4507892) *****/
1544 -- New cursor defn. for bug fix 4507892
1545 CURSOR status_description_cur IS
1546 SELECT meaning
1547 FROM FND_LOOKUP_VALUES FLV
1548 WHERE lookup_type = 'PSP_STATUS'
1549 AND lookup_code = p_status_code
1550 AND language = USERENV('LANG');
1551
1552 l_status_description psp_lookups.meaning%TYPE;
1553
1554 BEGIN
1555 OPEN status_description_cur;
1556 FETCH status_description_cur INTO l_status_description;
1557 IF (status_description_cur%NOTFOUND) THEN
1558 fnd_message.set_name('PSP', 'PSP_STATUS_DESCR_NOT_FOUND');
1559 l_status_description := fnd_message.get;
1560 END IF;
1561 CLOSE status_description_cur;
1562 RETURN l_status_description;
1563 END get_status_description;
1564
1565 FUNCTION get_error_description (p_error_code IN VARCHAR2) RETURN VARCHAR2 IS
1566 /***** Modified the following cursor for R12 performance fixes (bug 4507892)
1567 CURSOR error_description_cur IS
1568 SELECT MEANING
1569 FROM PSP_LOOKUPS
1570 WHERE lookup_code = p_error_code
1571 AND lookup_type = 'PSP_ERROR_CODE';
1572 End of comment for R12 performace fixes (bug 4507892) *****/
1573
1574 -- New cursor defn for bug fix 4507892
1575 CURSOR error_description_cur IS
1576 SELECT meaning
1577 FROM FND_LOOKUP_VALUES FLV
1578 WHERE lookup_type = 'PSP_ERROR_CODE'
1579 AND lookup_code = p_error_code
1580 AND language = USERENV('LANG');
1581
1582 l_error_description psp_lookups.meaning%TYPE;
1583
1584 BEGIN
1585 OPEN error_description_cur;
1586 FETCH error_description_cur INTO l_error_description;
1587 IF (error_description_cur%NOTFOUND) THEN
1588 l_error_description := p_error_code;
1589 END IF;
1590 CLOSE error_description_cur;
1591 RETURN l_error_description;
1592 END get_error_description;
1593 -- End of bug fix 2635110
1594
1595
1596 -- For Qubec fixes by tbalacha---
1597 --Bug no 2478000 ---
1598 /***************************************************************************************
1599 Funtion added for Qubec
1600 Description : This code returns the Currency code associated with a business_group_id
1601 Purpose : To remove Hardcoded USD from Labor Distribution Forms and reports
1602 Creation date:25-APR-2003
1603 *****************************************************************************************/
1604 FUNCTION get_currency_code(p_business_group_id IN NUMBER ) RETURN VARCHAR2 IS
1605 l_curr_code VARCHAR2(15);
1606
1607 CURSOR get_curr_for_bg is SELECT currency_code from per_business_groups where
1608 business_group_id=p_business_group_id;
1609
1610
1611 BEGIN
1612 OPEN get_curr_for_bg;
1613 FETCH get_curr_for_bg into l_curr_code;
1614
1615 IF get_curr_for_bg%NOTFOUND THEN
1616
1617 FND_MESSAGE.SET_NAME('PSP' , 'PSP_HR_CUR_NOT_SET_UP');
1618 CLOSE get_curr_for_bg;
1619 RAISE fnd_api.g_exc_unexpected_error;
1620
1621 ELSE
1622
1623 CLOSE get_curr_for_bg;
1624 RETURN (l_curr_code);
1625
1626 END IF;
1627 END get_currency_code;
1628
1629 -- End of code for Bug no 2478000
1630
1631 -- Introduced the following for bug 2916848
1632 PROCEDURE get_currency_precision
1633 (p_currency_code IN VARCHAR2,
1634 p_precision OUT NOCOPY NUMBER,
1635 p_ext_precision OUT NOCOPY NUMBER) IS
1636 l_min_acct_unit NUMBER;
1637 BEGIN
1638 fnd_currency.get_info (currency_code => p_currency_code,
1639 precision => p_precision,
1640 ext_precision => p_ext_precision,
1641 min_acct_unit => l_min_acct_unit);
1642 p_ext_precision := NVL(p_ext_precision, 6);
1643 END get_currency_precision;
1644
1645 /***** Commented the following function for bug fix 3146167
1646 FUNCTION get_payroll_currency (p_payroll_control_id IN NUMBER) RETURN VARCHAR2 IS
1647 CURSOR currency_code_cur IS
1648 SELECT currency_code
1649 FROM psp_payroll_controls ppc
1650 WHERE ppc.payroll_control_id = p_payroll_control_id;
1651
1652 l_currency_code psp_payroll_controls.currency_code%TYPE;
1653 BEGIN
1654 OPEN currency_code_cur;
1655 FETCH currency_code_cur INTO l_currency_code;
1656 CLOSE currency_code_cur;
1657
1658 RETURN l_currency_code;
1659 END get_payroll_currency;
1660 End of comment for bug fix 3146167 *****/
1661 -- End of bug fix 2916848
1662
1663 /*************************************************************************************************
1664 Description: This function call would replace call to profile option
1665 PSP: Enable Update Encumbrance, as the profile,
1666 PSP: Enable Update Encumbrance will be obsoleted by end dating it to '01-jan-2003'.
1667 The call to the profile PSP: Enable Update Encumbrance , in all the files except
1668 GMS.pll will be removed and this new function START_CAPTURING_UPDATES will
1669 instead called in its place
1670 Date of Creation: 23-Jul-2003
1671 Bug :3075435 Dynamic trigger IMplementation.
1672 **********************************************************************************************/
1673 FUNCTION START_CAPTURING_UPDATES(p_business_group_id IN NUMBER) RETURN VARCHAR2 IS
1674
1675 CURSOR update_enc_cur IS
1676 SELECT 'Y'
1677 FROM psp_enc_end_dates
1678 WHERE default_org_flag = 'Y'
1679 AND business_group_id = p_business_group_id
1680 AND prev_enc_end_date IS NOT NULL;
1681
1682
1683 l_start_capturing_updates VARCHAR2(2);
1684
1685 BEGIN
1686 OPEN update_enc_cur;
1687 FETCH update_enc_cur INTO l_start_capturing_updates;
1688 IF (update_enc_cur%NOTFOUND) THEN
1689 l_start_capturing_updates := 'N';
1690 END IF;
1691 CLOSE update_enc_cur;
1692
1693 RETURN l_start_capturing_updates;
1694
1695 End START_CAPTURING_UPDATES;
1696
1697
1698 /***********************************************************************************
1699 Decription : This procedure was created for the Ads bug 2935850
1700 Purpose : PA transaction import when kicks off ,it reports
1701 pa_too_many_employees error
1702 Creation Date : 23-Aug-2003
1703 ***********************************************************************************/
1704 FUNCTION PERSON_BUSINESS_GROUP_ID_EXIST RETURN BOOLEAN IS
1705
1706 CURSOR chk_insert(p_table_owner varchar2) IS
1707 SELECT 1
1708 FROM all_tab_columns
1709 WHERE table_name = 'PA_TRANSACTION_INTERFACE_ALL'
1710 AND column_name = 'PERSON_BUSINESS_GROUP_ID'
1711 AND owner = p_table_owner; -- bug 3871687
1712
1713 l_pa_bg_id Number;
1714 l_return_status Boolean;
1715 p_status Varchar2(100);
1716 p_industry Varchar2(100);
1717 p_table_owner Varchar2(100);
1718
1719
1720 BEGIN
1721
1722 l_return_status := FND_INSTALLATION.GET_APP_INFO(application_short_name => 'PA',
1723 status => p_status, industry => p_industry, oracle_schema => p_table_owner);
1724
1725 OPEN chk_insert(p_table_owner);
1726 FETCH chk_insert into l_pa_bg_id;
1727 IF chk_insert%NOTFOUND THEN
1728 CLOSE chk_insert;
1729 RETURN FALSE;
1730 END IF;
1731 CLOSE chk_insert;
1732 RETURN TRUE;
1733
1734
1735 END PERSON_BUSINESS_GROUP_ID_EXIST;
1736
1737
1738
1739 /*****************************************************************************
1740 Function name : VALIDATE_PROC_FOR_HR_UPG
1741 Creation date : 21-Apr-2004
1742 Purpose : This procedure returns true when Labor Distribtion Product
1743 is Installed.
1744 *****************************************************************************/
1745 PROCEDURE VALIDATE_PROC_FOR_HR_UPG(do_upg OUT NOCOPY VARCHAR2)
1746 is
1747
1748 PSP_APPLICATION_ID constant number:=8403;
1749 PSP_STATUS_INSTALLED constant varchar2(2):='I';
1750
1751 l_installed fnd_product_installations.status%type;
1752
1753 cursor csr_psp_installed is
1754 select status
1755 from fnd_product_installations
1756 where application_id = PSP_APPLICATION_ID;
1757
1758 l_do_submit varchar2(10) := 'FALSE';
1759
1760 begin
1761
1762 open csr_psp_installed;
1763 fetch csr_psp_installed into l_installed;
1764 if ( l_installed =PSP_STATUS_INSTALLED ) then
1765 l_do_submit := 'TRUE';
1766 end if;
1767 close csr_psp_installed;
1768
1769 do_upg := l_do_submit;
1770
1771 END validate_proc_for_hr_upg;
1772
1773 -- Introduced the following for bug fix 2908859/2907203
1774 FUNCTION get_act_dff_grouping_option (p_business_group_id IN NUMBER)
1775 RETURN VARCHAR2 IS
1776 CURSOR grouping_option_cur IS
1777 SELECT 1 hierarchy, PCV_INFORMATION1
1778 FROM pqp_configuration_values pcv
1779 WHERE pcv.business_group_id = p_business_group_id
1780 AND pcv_information_category = 'PSP_ACT_DFF_GROUPING'
1781 UNION ALL
1782 SELECT 2 hierarchy, PCV_INFORMATION1
1783 FROM pqp_configuration_values pcv
1784 WHERE pcv.business_group_id IS NULL
1785 AND pcv_information_category = 'PSP_ACT_DFF_GROUPING'
1786 ORDER BY 1;
1787
1788 l_grouping_option CHAR(1);
1789 l_hierarchy NUMBER;
1790 BEGIN
1791 OPEN grouping_option_cur;
1792 FETCH grouping_option_cur INTO l_hierarchy, l_grouping_option;
1793 CLOSE grouping_option_cur;
1794 l_grouping_option := NVL(l_grouping_option, 'N');
1795
1796 RETURN l_grouping_option;
1797 END get_act_dff_grouping_option;
1798
1799 FUNCTION get_enc_dff_grouping_option (p_business_group_id IN NUMBER)
1800 RETURN VARCHAR2 IS
1801 CURSOR grouping_option_cur IS
1802 SELECT 1 hierarchy, PCV_INFORMATION1
1803 FROM pqp_configuration_values pcv
1804 WHERE pcv.business_group_id = p_business_group_id
1805 AND pcv_information_category = 'PSP_ENC_DFF_GROUPING'
1806 UNION ALL
1807 SELECT 2 hierarchy, PCV_INFORMATION1
1808 FROM pqp_configuration_values pcv
1809 WHERE pcv.business_group_id IS NULL
1810 AND pcv_information_category = 'PSP_ENC_DFF_GROUPING'
1811 ORDER BY 1;
1812
1813 l_grouping_option CHAR(1);
1814 l_hierarchy NUMBER;
1815 BEGIN
1816 OPEN grouping_option_cur;
1817 FETCH grouping_option_cur INTO l_hierarchy, l_grouping_option;
1818 CLOSE grouping_option_cur;
1819 l_grouping_option := NVL(l_grouping_option, 'N');
1820
1821 RETURN l_grouping_option;
1822 END get_enc_dff_grouping_option;
1823
1824 FUNCTION get_sponsored_flag (p_project_id IN NUMBER) RETURN VARCHAR2 IS
1825 CURSOR sponsored_flag_cur IS
1826 SELECT sponsored_flag
1827 FROM pa_projects_all ppa,
1828 gms_project_types gpt -- Changed from gms_project_types_all for P1 bug 4078481
1829 WHERE gpt.project_type = ppa.project_type
1830 AND ppa.project_type <> 'AWARD_PROJECT'
1831 AND ppa.project_id = p_project_id;
1832
1833 l_sponsored_flag CHAR(1);
1834 BEGIN
1835 OPEN sponsored_flag_cur;
1836 FETCH sponsored_flag_cur INTO l_sponsored_flag;
1837 CLOSE sponsored_flag_cur;
1838
1839 RETURN NVL(l_sponsored_flag, 'N');
1840 END get_sponsored_flag;
1841 -- End of changes for bug fix 2908859/2907203
1842
1843 -- Changes for Effort Reporting Self service Page
1844
1845 FUNCTION get_person_name_er(p_person_id IN VARCHAR2, p_effective_date IN DATE) RETURN VARCHAR2 IS
1846 cursor eff_dates is
1847 select max(ppf.effective_end_date),min(ppf.effective_start_date)
1848 from per_people_f ppf
1849 where ppf.current_employee_flag = 'Y'
1850 and ppf.person_id = p_person_id
1851 group by ppf.person_id ;
1852
1853 cursor c1(p_calculated_date date)
1854 is
1855 select full_name from per_people_f
1856 where person_id = p_person_id
1857 and p_calculated_date between effective_start_date and effective_end_date;
1858
1859 l_person_name varchar2(240);
1860 max_eff_end_date date;
1861 min_eff_start_date date;
1862
1863 begin
1864
1865
1866 l_person_name := psp_general.get_person_name(p_person_id,p_effective_date);
1867 If (l_person_name = FND_MESSAGE.GET_STRING('PSP','PSP_NON_PERSON_NOT_FOUND') ) Then
1868 open eff_dates;
1869 fetch eff_dates into max_eff_end_date,min_eff_start_date;
1870 close eff_dates;
1871
1872 If ( p_effective_date < min_eff_start_date ) Then
1873
1874 open c1(min_eff_start_date);
1875 fetch c1 into l_person_name;
1876 IF c1%NOTFOUND THEN
1877 CLOSE c1;
1878 -- RETURN ('Person Name Not Found on Effective Date');
1879 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_NON_PERSON_NOT_FOUND'));
1880 END IF;
1881
1882
1883 Else
1884
1885 open c1(max_eff_end_date);
1886 fetch c1 into l_person_name;
1887 IF c1%NOTFOUND THEN
1888 CLOSE c1;
1889 -- RETURN ('Person Name Not Found on Effective Date');
1890 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_NON_PERSON_NOT_FOUND'));
1891 END IF;
1892
1893 End if ;
1894
1895 If c1%ISOPEN then
1896 Close c1;
1897 End if;
1898 End If;
1899
1900 return l_person_name ;
1901
1902 END get_person_name_er;
1903
1904 FUNCTION chk_person_validity(p_person_id IN VARCHAR2,p_effective_date IN DATE) RETURN VARCHAR2
1905 IS
1906
1907 cursor eff_dates is
1908 select max(ppf.effective_end_date),min(ppf.effective_start_date)
1909 from per_people_f ppf
1910 where ppf.current_employee_flag = 'Y'
1911 and ppf.person_id = p_person_id
1912 group by ppf.person_id ;
1913
1914 max_eff_end_date date;
1915 min_eff_start_date date;
1916
1917 BEGIN
1918
1919 open eff_dates;
1920 fetch eff_dates into max_eff_end_date,min_eff_start_date;
1921 close eff_dates;
1922
1923 If ((min_eff_start_date <= p_effective_date) and ( p_effective_date <= max_eff_end_date)) then
1924
1925 return 'Y';
1926
1927 Else
1928
1929 return 'N';
1930
1931 End if;
1932
1933
1934 END chk_person_validity;
1935
1936 FUNCTION chk_payroll_validity(p_payroll_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2
1937 IS
1938
1939 cursor eff_dates is
1940 select max(papf.effective_end_date),min(papf.effective_start_date)
1941 from pay_all_payrolls_f papf
1942 WHERE papf.payroll_id = p_payroll_id;
1943
1944 max_eff_end_date date;
1945 min_eff_start_date date;
1946
1947 BEGIN
1948
1949 open eff_dates;
1950 fetch eff_dates into max_eff_end_date,min_eff_start_date;
1951 close eff_dates;
1952
1953 If ((min_eff_start_date <= p_effective_date) and ( p_effective_date <= max_eff_end_date)) then
1954
1955 return 'Y';
1956
1957 Else
1958
1959 return 'N';
1960
1961 End if;
1962
1963
1964 END chk_payroll_validity;
1965
1966 FUNCTION get_payroll_name_er(p_payroll_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2 IS
1967 cursor eff_dates is
1968 select max(papf.effective_end_date),min(papf.effective_start_date)
1969 from pay_all_payrolls_f papf
1970 WHERE papf.payroll_id = p_payroll_id;
1971
1972 cursor c1(p_calculated_date date)
1973 is
1974 select payroll_name
1975 from pay_all_payrolls_f papf
1976 WHERE papf.payroll_id = p_payroll_id
1977 and p_calculated_date between papf.effective_start_date and papf.effective_end_date;
1978
1979 l_payroll_name varchar2(240);
1980 max_eff_end_date date;
1981 min_eff_start_date date;
1982
1983 begin
1984
1985
1986 l_payroll_name := psp_general.get_payroll_name(p_payroll_id,p_effective_date);
1987 If (l_payroll_name = FND_MESSAGE.GET_STRING('PSP','PSP_NON_PAYROLL_NOT_FOUND') ) Then
1988 open eff_dates;
1989 fetch eff_dates into max_eff_end_date,min_eff_start_date;
1990 close eff_dates;
1991
1992 If ( p_effective_date < min_eff_start_date ) Then
1993
1994 open c1(min_eff_start_date);
1995 fetch c1 into l_payroll_name;
1996 IF c1%NOTFOUND THEN
1997 CLOSE c1;
1998 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_NON_PAYROLL_NOT_FOUND'));
1999 END IF;
2000
2001
2002 Else
2003
2004 open c1(max_eff_end_date);
2005 fetch c1 into l_payroll_name;
2006 IF c1%NOTFOUND THEN
2007 CLOSE c1;
2008 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_NON_PAYROLL_NOT_FOUND'));
2009 END IF;
2010
2011 End if ;
2012
2013 If c1%ISOPEN then
2014 Close c1;
2015 End if;
2016
2017 End If;
2018
2019 return l_payroll_name ;
2020
2021 END get_payroll_name_er;
2022
2023 FUNCTION chk_position_validity(p_position_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2
2024 IS
2025
2026 cursor eff_dates is
2027 select max(hpf.effective_end_date),min(hpf.effective_start_date)
2028 from hr_positions_f hpf
2029 WHERE hpf.position_id = p_position_id;
2030
2031 max_eff_end_date date;
2032 min_eff_start_date date;
2033
2034 BEGIN
2035
2036 open eff_dates;
2037 fetch eff_dates into max_eff_end_date,min_eff_start_date;
2038 close eff_dates;
2039
2040 If ((min_eff_start_date <= p_effective_date) and ( p_effective_date <= max_eff_end_date)) then
2041
2042 return 'Y';
2043
2044 Else
2045
2046 return 'N';
2047
2048 End if;
2049
2050
2051 END chk_position_validity;
2052
2053
2054
2055
2056 FUNCTION get_position_name_er(p_position_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2 IS
2057
2058 cursor eff_dates is
2059 select max(hpf.effective_end_date),min(hpf.effective_start_date)
2060 from hr_positions_f hpf
2061 WHERE hpf.position_id = p_position_id ;
2062
2063 cursor c1(p_calculated_date date)
2064 is
2065 select name
2066 from hr_positions_f hpf
2067 WHERE hpf.position_id = p_position_id
2068 and p_calculated_date between hpf.effective_start_date and hpf.effective_end_date;
2069
2070 l_position_name varchar2(240);
2071 max_eff_end_date date;
2072 min_eff_start_date date;
2073
2074 begin
2075
2076
2077
2078 open eff_dates;
2079 fetch eff_dates into max_eff_end_date,min_eff_start_date;
2080 close eff_dates;
2081
2082 If ( p_effective_date < min_eff_start_date ) Then
2083
2084 open c1(min_eff_start_date);
2085 fetch c1 into l_position_name;
2086 IF c1%NOTFOUND THEN
2087 CLOSE c1;
2088 END IF;
2089
2090
2091 Else
2092
2093 open c1(max_eff_end_date);
2094 fetch c1 into l_position_name;
2095 IF c1%NOTFOUND THEN
2096 CLOSE c1;
2097 END IF;
2098
2099 End if ;
2100
2101 If c1%ISOPEN then
2102 Close c1;
2103 End if;
2104
2105 return l_position_name ;
2106
2107 END get_position_name_er;
2108
2109 FUNCTION chk_fastformula_validity(p_formula_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2
2110 IS
2111
2112 cursor eff_dates is
2113 select max(ff.effective_end_date),min(ff.effective_start_date)
2114 from ff_formulas_f ff
2115 WHERE ff.formula_id = p_formula_id;
2116
2117 max_eff_end_date date;
2118 min_eff_start_date date;
2119
2120 BEGIN
2121
2122 open eff_dates;
2123 fetch eff_dates into max_eff_end_date,min_eff_start_date;
2124 close eff_dates;
2125
2126 If ((min_eff_start_date <= p_effective_date) and ( p_effective_date <= max_eff_end_date)) then
2127
2128 return 'Y';
2129
2130 Else
2131
2132 return 'N';
2133
2134 End if;
2135
2136
2137 END chk_fastformula_validity;
2138
2139
2140
2141
2142 FUNCTION get_fastformula_name_er(p_formula_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2 IS
2143
2144 cursor eff_dates is
2145 select max(ff.effective_end_date),min(ff.effective_start_date)
2146 from ff_formulas_f ff
2147 WHERE ff.formula_id = p_formula_id;
2148
2149 cursor c1(p_calculated_date date)
2150 is
2151 select formula_name
2152 from ff_formulas_f ff
2153 WHERE ff.formula_id = p_formula_id
2154 and p_calculated_date between ff.effective_start_date and ff.effective_end_date;
2155
2156 l_formula_name varchar2(240);
2157 max_eff_end_date date;
2158 min_eff_start_date date;
2159
2160 begin
2161
2162
2163
2164 open eff_dates;
2165 fetch eff_dates into max_eff_end_date,min_eff_start_date;
2166 close eff_dates;
2167
2168 If ( p_effective_date < min_eff_start_date ) Then
2169
2170 open c1(min_eff_start_date);
2171 fetch c1 into l_formula_name;
2172 IF c1%NOTFOUND THEN
2173 CLOSE c1;
2174 END IF;
2175
2176
2177 Else
2178
2179 open c1(max_eff_end_date);
2180 fetch c1 into l_formula_name;
2181 IF c1%NOTFOUND THEN
2182 CLOSE c1;
2183 END IF;
2184
2185 End if ;
2186
2187
2188 If c1%ISOPEN then
2189 Close c1;
2190 End if;
2191
2192
2193 return l_formula_name ;
2194
2195 END get_fastformula_name_er;
2196
2197 FUNCTION get_fastformula_desc_er(p_formula_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2 IS
2198 cursor eff_date is
2199 select max(ff.effective_end_date),min(ff.effective_start_date)
2200 from ff_formulas_f ff
2201 WHERE ff.formula_id = p_formula_id;
2202
2203 cursor c1(p_calculated_date date)
2204 is
2205 select description
2206 from ff_formulas_f ff
2207 WHERE ff.formula_id = p_formula_id
2208 and p_calculated_date between ff.effective_start_date and ff.effective_end_date;
2209
2210 l_formula_desc ff_formulas_f.description%type;
2211 max_eff_end_date date;
2212 min_eff_start_date date;
2213
2214 begin
2215
2216
2217
2218 open eff_date;
2219 fetch eff_date into max_eff_end_date,min_eff_start_date;
2220 close eff_date;
2221
2222 If ( p_effective_date < min_eff_start_date ) Then
2223
2224 open c1(min_eff_start_date);
2225 fetch c1 into l_formula_desc;
2226 IF c1%NOTFOUND THEN
2227 CLOSE c1;
2228 END IF;
2229
2230
2231 Else
2232
2233 open c1(max_eff_end_date);
2234 fetch c1 into l_formula_desc;
2235 IF c1%NOTFOUND THEN
2236 CLOSE c1;
2237 END IF;
2238
2239 End if ;
2240
2241
2242 If c1%ISOPEN then
2243 Close c1;
2244 End if;
2245
2246
2247 return l_formula_desc ;
2248
2249 End get_fastformula_desc_er;
2250
2251 FUNCTION chk_job_validity(p_job_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2 IS
2252
2253 cursor eff_dates is
2254 select max(nvl(pjv.date_to,to_date('31/12/4712','DD/MM/RRRR'))),min(pjv.date_from)
2255 from per_jobs_v pjv
2256 WHERE pjv.job_id = p_job_id;
2257
2258 max_eff_end_date date;
2259 min_eff_start_date date;
2260
2261 BEGIN
2262
2263 open eff_dates;
2264 fetch eff_dates into max_eff_end_date,min_eff_start_date;
2265 close eff_dates;
2266
2267 If ((min_eff_start_date <= p_effective_date) and ( p_effective_date <= max_eff_end_date)) then
2268
2269 return 'Y';
2270
2271 Else
2272
2273 return 'N';
2274
2275 End if;
2276
2277
2278 END chk_job_validity;
2279
2280
2281
2282
2283 FUNCTION get_job_name_er(p_job_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2 IS
2284
2285 cursor eff_dates is
2286 select max(nvl(pjv.date_to,to_date('31/12/4712','DD/MM/RRRR'))),min(pjv.date_from)
2287 from per_jobs_v pjv
2288 WHERE pjv.job_id = p_job_id;
2289
2290 cursor c1(p_calculated_date date)
2291 is
2292 select name
2293 from per_jobs_v pjv
2294 WHERE pjv.job_id = p_job_id
2295 and p_calculated_date between pjv.date_from and trunc(nvl(pjv.date_to,to_date('31/12/4712','DD/MM/RRRR')));
2296
2297 l_job_name varchar2(240);
2298 max_eff_end_date date;
2299 min_eff_start_date date;
2300
2301 begin
2302
2303
2304
2305 open eff_dates;
2306 fetch eff_dates into max_eff_end_date,min_eff_start_date;
2307 close eff_dates;
2308
2309 If ( p_effective_date < min_eff_start_date ) Then
2310
2311 open c1(min_eff_start_date);
2312 fetch c1 into l_job_name;
2313 IF c1%NOTFOUND THEN
2314 CLOSE c1;
2315 END IF;
2316
2317
2318 Else
2319
2320 open c1(max_eff_end_date);
2321 fetch c1 into l_job_name;
2322 IF c1%NOTFOUND THEN
2323 CLOSE c1;
2324 END IF;
2325
2326 End if ;
2327
2328
2329 if c1%ISOPEN then
2330 Close c1;
2331 End if;
2332
2333
2334 return l_job_name ;
2335
2336 END get_job_name_er;
2337
2338 FUNCTION chk_org_validity(p_org_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2 IS
2339
2340 cursor eff_dates is
2341 select max(nvl(hou.date_to,to_date('31/12/4712','DD/MM/RRRR'))),min(hou.date_from)
2342 from hr_organization_units hou
2343 WHERE hou.organization_id = p_org_id;
2344
2345 max_eff_end_date date;
2346 min_eff_start_date date;
2347
2348 BEGIN
2349
2350 open eff_dates;
2351 fetch eff_dates into max_eff_end_date,min_eff_start_date;
2352 close eff_dates;
2353
2354 If ((min_eff_start_date <= p_effective_date) and ( p_effective_date <= max_eff_end_date)) then
2355
2356 return 'Y';
2357
2358 Else
2359
2360 return 'N';
2361
2362 End if;
2363
2364 END chk_org_validity;
2365
2366
2367 FUNCTION get_org_name_er(p_org_id IN NUMBER, p_effective_date IN DATE) RETURN VARCHAR2 IS
2368
2369 cursor eff_dates is
2370 select max(nvl(hou.date_to,to_date('31/12/4712','DD/MM/RRRR'))),min(hou.date_from)
2371 from hr_organization_units hou
2372 WHERE hou.organization_id = p_org_id;
2373
2374 cursor c1(p_calculated_date date)
2375 is
2376 select name
2377 from hr_organization_units hou
2378 WHERE hou.organization_id = p_org_id
2379 and p_calculated_date between hou.date_from and trunc(nvl(hou.date_to,to_date('31/12/4712','DD/MM/RRRR')));
2380
2381 l_organization_name varchar2(240);
2382 max_eff_end_date date;
2383 min_eff_start_date date;
2384
2385 begin
2386
2387
2388 open eff_dates;
2389 fetch eff_dates into max_eff_end_date,min_eff_start_date;
2390 close eff_dates;
2391
2392 If ( p_effective_date < min_eff_start_date ) Then
2393
2394 open c1(min_eff_start_date);
2395 fetch c1 into l_organization_name;
2396 IF c1%NOTFOUND THEN
2397 CLOSE c1;
2398 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_ORG_NOT_FOUND'));
2399 END IF;
2400
2401
2402 Else
2403
2404 open c1(max_eff_end_date);
2405 fetch c1 into l_organization_name;
2406 IF c1%NOTFOUND THEN
2407 CLOSE c1;
2408 RETURN(FND_MESSAGE.GET_STRING('PSP','PSP_ORG_NOT_FOUND'));
2409 END IF;
2410
2411 End if ;
2412
2413
2414 if c1%ISOPEN then
2415 Close c1;
2416 End if;
2417
2418 return l_organization_name ;
2419
2420 END get_org_name_er;
2421
2422
2423
2424 -- End of changes for Self service Page
2425 /*
2426 * --- Following section has Functions that are called from AME
2427 *
2428 * Format of the AME Transaction_Id:
2429 * ================================
2430 *
2431 * For Seeded Approvals:
2432 * 1-20 Approval Type(incomming types will be tagged, eg. SEED-EMP)
2433 * 21-35 Person_id / Project_id / Task_id / Award_id
2434 * 36-50 Effort_report_id/Effort_report_Detail_id
2435 *
2436 * For Custom Approval:
2437 * 1-30 Approval Type
2438 * 36-50 Effort_report_id/Effort_report_Detail_id
2439 *
2440 */
2441
2442 function get_approval_type(txn_id varchar2) return varchar2 is
2443 begin
2444 if txn_id like 'SEED%' then
2445 return trim(substr(txn_id,6,15));
2446 else
2447 return trim(substr(txn_id,1,30));
2448 end if;
2449 end;
2450
2451 function get_person_id(txn_id varchar2) return number is
2452 cursor get_person_id is
2453 select person_id
2454 from psp_eff_reports
2455 where effort_report_id in
2456 (select effort_report_id
2457 from psp_eff_report_details
2458 where effort_report_detail_id = substr(txn_id, 36,15));
2459 l_person_id number;
2460 begin
2461 if txn_id like 'SEED-EMP%' or
2462 txn_id like 'SEED-ESU%' or
2463 txn_id like 'SEED-SUP%' then
2464 l_person_id := to_number(substr(txn_id,21,15));
2465 elsif txn_id not like 'SEED%' then
2466 open get_person_id;
2467 fetch get_person_id into l_person_id;
2468 close get_person_id;
2469 else
2470 l_person_id := null;
2471 end if;
2472 return l_person_id;
2473 exception
2474 when others then
2475 return null;
2476 end;
2477
2478 function get_eff_Report_detail_id(txn_id varchar2) return number is
2479 begin
2480 return to_number(substr(txn_id,36,15));
2481 end;
2482
2483 function get_task_id(txn_id varchar2) return number is
2484 begin
2485 if txn_id like 'SEED-TMG%' then
2486 return to_number(substr(txn_id,21,15));
2487 else
2488 return -999;
2489 end if;
2490 end;
2491
2492 function get_project_id(txn_id varchar2) return number is
2493 begin
2494 if txn_id like 'SEED-PMG%' then
2495 return to_number(substr(txn_id,21,15));
2496 else
2497 return -999;
2498 end if;
2499 end;
2500
2501 function get_emp_term_flag(txn_id varchar2) return varchar2 is
2502 term_flag varchar2(1);
2503 cursor term_cur is
2504 select nvl(current_employee_flag,'N')
2505 from per_all_people_f
2506 where person_id = get_person_id(txn_id)
2507 and sysdate between effective_start_date and effective_end_date;
2508 begin
2509 open term_cur;
2510 fetch term_cur into term_flag;
2511 if term_cur%notfound then
2512 term_flag := 'N';
2513 end if;
2514 close term_cur;
2515 return term_flag;
2516 end;
2517
2518 /*Added for Bug 6786413*/
2519 function get_user_id_flag(txn_id varchar2) return varchar2 is
2520 userid_flag varchar2(1);
2521 userid_count number;
2522 begin
2523 select count(*) into userid_count
2524 from fnd_user
2525 where employee_id = get_person_id(txn_id)
2526 and trunc(sysdate) between start_date and nvl(end_date,sysdate);
2527
2528 if (userid_count > 0) then
2529 userid_flag := 'Y';
2530 else
2531 userid_flag := 'N';
2532 end if;
2533 return userid_flag;
2534 end;
2535
2536 ----- ===== End of AME functions
2537
2538 -- Introduced the following for bug fix 3867234
2539 PROCEDURE add_report_error(p_request_id IN NUMBER,
2540 p_message_level IN VARCHAR2,
2541 p_source_id IN NUMBER,
2542 p_error_message IN VARCHAR2,
2543 p_payroll_action_id IN NUMBER,
2544 p_return_status OUT NOCOPY VARCHAR2,
2545 p_source_name IN VARCHAR2 DEFAULT NULL,
2546 p_parent_source_id IN NUMBER DEFAULT NULL,
2547 p_parent_source_name IN VARCHAR2 DEFAULT NULL,
2548 p_value1 IN NUMBER DEFAULT NULL,
2549 p_value2 IN NUMBER DEFAULT NULL,
2550 p_value3 IN NUMBER DEFAULT NULL,
2551 p_value4 IN NUMBER DEFAULT NULL,
2552 p_value5 IN NUMBER DEFAULT NULL,
2553 p_value6 IN NUMBER DEFAULT NULL,
2554 p_value7 IN NUMBER DEFAULT NULL,
2555 p_value8 IN NUMBER DEFAULT NULL,
2556 p_value9 IN NUMBER DEFAULT NULL,
2557 p_value10 IN NUMBER DEFAULT NULL,
2558 p_information1 IN VARCHAR2 DEFAULT NULL,
2559 p_information2 IN VARCHAR2 DEFAULT NULL,
2560 p_information3 IN VARCHAR2 DEFAULT NULL,
2561 p_information4 IN VARCHAR2 DEFAULT NULL,
2562 p_information5 IN VARCHAR2 DEFAULT NULL,
2563 p_information6 IN VARCHAR2 DEFAULT NULL,
2564 p_information7 IN VARCHAR2 DEFAULT NULL,
2565 p_information8 IN VARCHAR2 DEFAULT NULL,
2566 p_information9 IN VARCHAR2 DEFAULT NULL,
2567 p_information10 IN VARCHAR2 DEFAULT NULL) IS
2568 PRAGMA AUTONOMOUS_TRANSACTION;
2569 BEGIN
2570 INSERT INTO psp_report_errors
2571 (error_sequence_id, request_id, message_level,
2572 source_id, error_message, payroll_action_id,
2573 source_name, parent_source_id, parent_source_name,
2574 value1, value2, value3, value4, value5,
2575 value6, value7, value8, value9, value10,
2576 information1, information2, information3, information4, information5,
2577 information6, information7, information8, information9, information10)
2578 VALUES
2579 (psp_report_errors_s.NEXTVAL, p_request_id, p_message_level,
2580 p_source_id, p_error_message, p_payroll_action_id,
2581 p_source_name, p_parent_source_id, p_parent_source_name,
2582 p_value1, p_value2, p_value3, p_value4, p_value5,
2583 p_value6, p_value7, p_value8, p_value9, p_value10,
2584 p_information1, p_information2, p_information3, p_information4, p_information5,
2585 p_information6, p_information7, p_information8, p_information9, p_information10);
2586
2587 COMMIT;
2588
2589 p_return_status := 'S';
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 p_return_status := 'E';
2593 END;
2594
2595 PROCEDURE add_report_error(p_request_id IN NUMBER,
2596 p_message_level IN VARCHAR2,
2597 p_source_id IN NUMBER,
2598 p_retry_request_id IN NUMBER,
2599 p_pdf_request_id IN NUMBER,
2600 p_error_message IN VARCHAR2,
2601 p_return_status OUT NOCOPY VARCHAR2) IS
2602 PRAGMA AUTONOMOUS_TRANSACTION;
2603 BEGIN
2604 INSERT INTO psp_report_errors
2605 (error_sequence_id, request_id, message_level, source_id,
2606 retry_request_id, pdf_request_id, error_message)
2607 VALUES
2608 (psp_report_errors_s.NEXTVAL, p_request_id, p_message_level, p_source_id,
2609 p_retry_request_id, p_pdf_request_id, p_error_message);
2610
2611 COMMIT;
2612
2613 p_return_status := 'S';
2614 EXCEPTION
2615 WHEN OTHERS THEN
2616 p_return_status := 'E';
2617 END;
2618 -- End of changes for bug fix 3867234
2619
2620 -- Introduced the following for bug fix 4022334
2621 FUNCTION IS_EFFORT_REPORT_MIGRATED
2622 RETURN BOOLEAN
2623 IS
2624 l_curr_er_phase NUMBER;
2625 er_flag BOOLEAN;
2626 BEGIN
2627 SELECT NVL(MAX(PHASE),0) INTO l_curr_er_phase FROM PSP_UPGRADE_115 WHERE STATUS ='R' ;
2628 IF l_curr_er_phase = 10000 THEN
2629 er_flag := TRUE;
2630 ELSE
2631 er_flag := FALSE;
2632 END IF;
2633 RETURN er_flag;
2634 END IS_EFFORT_REPORT_MIGRATED;
2635 -- End of changes for bug fix 4022334
2636
2637 -- Start of Bug 7137755
2638 FUNCTION GET_PRE_APP_EMP_LIST(P_REQUEST_ID IN NUMBER)
2639 RETURN VARCHAR2
2640 IS
2641 l_emp_list VARCHAR2(4000) ;
2642 l_full_name VARCHAR2(50);
2643 i NUMBER := 0;
2644
2645 CURSOR get_emp_cur IS
2646 SELECT distinct full_name FROM psp_eff_reports
2647 WHERE request_id = p_request_id;
2648
2649 BEGIN
2650 l_emp_list := NULL;
2651 open get_emp_cur;
2652 LOOP
2653 fetch get_emp_cur into l_full_name;
2654 EXIT WHEN get_emp_cur%NOTFOUND;
2655
2656 i := i + 1;
2657 l_emp_list := l_emp_list || ' (' || i || ') ' || l_full_name ;
2658
2659 END LOOP;
2660 close get_emp_cur;
2661 return l_emp_list;
2662 END;
2663
2664
2665 FUNCTION GET_APP_REJ_EMP_LIST(P_WF_ITEM_KEY IN VARCHAR2)
2666 RETURN VARCHAR2
2667 IS
2668 l_emp_list VARCHAR2(4000) ;
2669 l_full_name VARCHAR2(50);
2670 i NUMBER := 0;
2671
2672 -- Modified cursor for Bug 7524262
2673 CURSOR get_emp_cur IS
2674 SELECT DISTINCT per.full_name
2675 FROM psp_eff_reports per,
2676 psp_eff_report_details perd,
2677 psp_eff_report_approvals pera
2678 WHERE pera.wf_item_key = p_wf_item_key
2679 AND pera.effort_report_detail_id = perd.effort_report_detail_id
2680 AND perd.effort_report_id = per.effort_report_id;
2681
2682 BEGIN
2683 l_emp_list := NULL;
2684 open get_emp_cur;
2685 LOOP
2686 fetch get_emp_cur into l_full_name;
2687 EXIT WHEN get_emp_cur%NOTFOUND;
2688
2689 i := i + 1;
2690 l_emp_list := l_emp_list || ' (' || i || ') ' || l_full_name ;
2691
2692 END LOOP;
2693 close get_emp_cur;
2694 return l_emp_list;
2695 END;
2696
2697 -- End of Bug 7137755
2698
2699 -- Start BUG 4244924YALE ENHANCEMENTS. Added additional paramater for Salary cap
2700 function GET_CONFIGURATION_OPTION_VALUE(p_business_group_id IN NUMBER,
2701 p_pcv_information_category in varchar2,
2702 p_pcv_information1 in varchar2 default null) return varchar2 IS
2703 Cursor get_CONFIGURATION_OPTION_CSR IS
2704 select 1 hierarchy, PCV_INFORMATION1 , pcv_information2
2705 from pqp_configuration_values
2706 where PCV_INFORMATION_CATEGORY = p_PCV_INFORMATION_CATEGORY
2707 and BUSINESS_GROUP_ID = p_business_group_id
2708 and (p_pcv_information1 is null
2709 or p_pcv_information1 = pcv_information1)
2710 UNION ALL
2711 select 2 hierarchy, PCV_INFORMATION1 , pcv_information2
2712 from pqp_configuration_values
2713 where PCV_INFORMATION_CATEGORY = p_PCV_INFORMATION_CATEGORY
2714 and BUSINESS_GROUP_ID IS NULL
2715 and (p_pcv_information1 is null
2716 or p_pcv_information1 = pcv_information1)
2717 ORDER BY 1;
2718
2719 l_PCV_INFORMATION1 varchar2(30);
2720 l_PCV_INFORMATION2 varchar2(30);
2721 l_hierarchy Number;
2722
2723 BEGIN
2724 Open get_CONFIGURATION_OPTION_CSR;
2725 fetch get_CONFIGURATION_OPTION_CSR into l_hierarchy,l_PCV_INFORMATION1 ,
2726 l_pcv_information2;
2727 close get_CONFIGURATION_OPTION_CSR ;
2728 if p_pcv_information_category not in ( 'PSP_CAP_ELEMENT_SET_ID', 'PSP_GENERIC_EXCESS_ACCT_ORG') then
2729 l_PCV_INFORMATION1 := NVL(l_PCV_INFORMATION1,'N');
2730 end if;
2731 if p_pcv_information1 is null then
2732 return l_PCV_INFORMATION1;
2733 else
2734 return l_PCV_INFORMATION2;
2735 end if;
2736 END;
2737
2738 Procedure GET_GL_PTAOE_MAPPING(p_business_group_id IN NUMBER,
2739 p_proj_segment OUT NOCOPY varchar2, p_tsk_segment OUT NOCOPY varchar2,
2740 p_awd_sgement OUT NOCOPY varchar2, p_exp_org_segment OUT NOCOPY varchar2,
2741 p_exp_type_segment OUT NOCOPY varchar2) is
2742
2743 Cursor get_GL_APTOE_MAPPING_CSR(p_business_group_id IN NUMBER) IS
2744 select 1 hierarchy, PCV_INFORMATION1,PCV_INFORMATION2,PCV_INFORMATION3,PCV_INFORMATION4,PCV_INFORMATION5
2745 from pqp_configuration_values
2746 where PCV_INFORMATION_CATEGORY = 'PSP_GL_PTAOE_MAPPING'
2747 and BUSINESS_GROUP_ID = p_business_group_id
2748 UNION ALL
2749 select 2 hierarchy, PCV_INFORMATION1,PCV_INFORMATION2,PCV_INFORMATION3,PCV_INFORMATION4,PCV_INFORMATION5
2750 from pqp_configuration_values
2751 where PCV_INFORMATION_CATEGORY = 'PSP_GL_PTAOE_MAPPING'
2752 and BUSINESS_GROUP_ID is null
2753 ORDER BY 1;
2754
2755 l_hierarchy Number;
2756 Begin
2757 open get_GL_APTOE_MAPPING_CSR(p_business_group_id);
2758 fetch get_GL_APTOE_MAPPING_CSR into l_hierarchy, p_proj_segment, p_tsk_segment, p_awd_sgement, p_exp_org_segment, p_exp_type_segment;
2759 Close get_GL_APTOE_MAPPING_CSR;
2760 -- if p_proj_segment is null or p_tsk_segment is null or p_awd_sgement is null
2761 -- or p_exp_org_segment is null or p_exp_type_segment is null then
2762 -- Raise;
2763 -- end if;
2764 EXCEPTION
2765 WHEN OTHERS THEN
2766 raise;
2767 END GET_GL_PTAOE_MAPPING;
2768
2769 -- END BUG 4244924 YALE ENHANCEMENTS
2770
2771 --Bug 4334816:Function added for Effort Report Status Monitor
2772 FUNCTION Is_eff_Report_status_changed (p_status_code IN Varchar2, p_wf_itrm_key IN Number)
2773 return varchar2 IS
2774 Cursor Is_eff_Report_superseded_csr(p_status_code IN Varchar2, p_wf_itrm_key IN Number) IS
2775 select 'Y'
2776 from psp_eff_reports per,
2777 psp_eff_report_details perd,
2778 psp_eff_report_approvals pera
2779 where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
2780 and perd.EFFORT_REPORT_DETAIL_ID = pera.EFFORT_REPORT_DETAIL_ID
2781 AND per.STATUS_CODE = p_status_code
2782 and WF_ITEM_KEY = p_wf_itrm_key;
2783
2784 Cursor Is_new_eff_Report_created_csr(p_status_code IN Varchar2, p_wf_itrm_key IN Number) IS
2785 select 'Y'
2786 from psp_eff_reports per,
2787 (select person_id ,STATUS_CODE, per.Start_date,per.end_date,per.EFFORT_REPORT_ID
2788 from psp_eff_reports per,
2789 psp_eff_report_details perd,
2790 psp_eff_report_approvals pera
2791 where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
2792 and perd.EFFORT_REPORT_DETAIL_ID = pera.EFFORT_REPORT_DETAIL_ID
2793 AND per.STATUS_CODE = p_status_code
2794 and pera.WF_ITEM_KEY = p_wf_itrm_key) temp
2795 where per.person_id = temp.person_id
2796 and per.start_date = temp.start_date
2797 and per.end_date = temp.end_date
2798 AND per.EFFORT_REPORT_ID > temp.EFFORT_REPORT_ID;
2799
2800 l_data_Exist_flag varchar2(1) ;
2801 BEGIN
2802 IF p_status_code = 'S' THEN
2803 open Is_eff_Report_superseded_csr(p_status_code,p_wf_itrm_key);
2804 fetch Is_eff_Report_superseded_csr into l_data_Exist_flag;
2805 close Is_eff_Report_superseded_csr;
2806 l_data_Exist_flag := NVL(l_data_Exist_flag,'N');
2807 ELSE
2808 open Is_new_eff_Report_created_csr(p_status_code,p_wf_itrm_key);
2809 fetch Is_new_eff_Report_created_csr into l_data_Exist_flag;
2810 close Is_new_eff_Report_created_csr;
2811 l_data_Exist_flag := NVL(l_data_Exist_flag,'N');
2812 END IF;
2813 return l_data_Exist_flag;
2814 END;
2815
2816 -- New function to display assignment_status in labor schedules
2817 -- bug 3887531 ,2889182
2818 FUNCTION get_assignment_status( P_ASSIGNMENT_ID IN NUMBER ,
2819 P_EFFECTIVE_DATE IN DATE )
2820 RETURN VARCHAR2 IS
2821
2822
2823 cursor eff_dates_csr is
2824 select max(paf.effective_end_date)
2825 from per_assignments_f paf
2826 where paf.assignment_id = p_assignment_id;
2827
2828
2829 l_date date ;
2830 l_effective_date date;
2831
2832 cursor fetch_asg_status_csr(P_ASSIGNMENT_ID IN NUMBER,P_EFFECTIVE_DATE IN DATE )is
2833 select past.USER_STATUS
2834 from per_assignment_status_types past ,
2835 per_assignments_f paf
2836 where paf.assignment_id = p_assignment_id
2837 and past.ASSIGNMENT_STATUS_TYPE_ID = paf.ASSIGNMENT_STATUS_TYPE_ID
2838 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
2839
2840 L_USER_STATUS per_assignment_status_types. USER_STATUS%TYPE;
2841
2842 begin
2843
2844 open eff_dates_csr;
2845 fetch eff_dates_csr into l_date ;
2846 close eff_dates_csr ;
2847
2848 if (l_date < P_EFFECTIVE_DATE )
2849 then
2850 L_USER_STATUS := 'End' ;
2851 else
2852 l_EFFECTIVE_DATE := p_effective_date ;
2853 OPEN fetch_asg_status_csr(P_ASSIGNMENT_ID,l_EFFECTIVE_DATE) ;
2854 FETCH fetch_asg_status_csr INTO L_USER_STATUS;
2855 CLOSE fetch_asg_status_csr;
2856 end if ;
2857
2858
2859
2860 RETURN L_USER_STATUS ;
2861
2862 END ;
2863
2864 --R12 MOAC Uptake
2865
2866 PROCEDURE INIT_MOAC IS
2867 BEGIN
2868 mo_global.init ('PSP');
2869 mo_global.set_policy_context('M', null);
2870 END INIT_MOAC;
2871
2872
2873 FUNCTION Get_transaction_org_id (p_project_id Number,p_expenditure_organization_id Number)
2874 RETURN NUMBER IS
2875
2876 l_org_id Number(15);
2877 BEGIN
2878 IF (p_project_id = G_PREV_PROJ_ID) THEN
2879 l_org_id := G_PREV_ORG_ID;
2880 ELSE
2881 /*
2882 If PA_UTILS.IsCrossChargeable(p_project_id) Then
2883 -- get the org_id for the Expenditure Org..
2884 BEGIN
2885 SELECT org_id
2886 INTO l_org_id
2887 FROM PA_ALL_ORGANIZATIONS
2888 WHERE PA_ORG_USE_TYPE = 'EXPENDITURES'
2889 AND organization_id = p_expenditure_organization_id
2890 AND rownum=1;
2891 EXCEPTION
2892 WHEN NO_DATA_FOUND THEN
2893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2894 END;
2895 Else
2896 */
2897 -- get the org_id for the project..
2898 BEGIN
2899 SELECT org_id
2900 INTO l_org_id
2901 FROM pa_projects_all
2902 WHERE project_id = p_project_Id;
2903 EXCEPTION
2904 WHEN NO_DATA_FOUND THEN
2905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2906 END;
2907 -- END IF;
2908 G_PREV_PROJ_ID := p_project_id;
2909 G_PREV_ORG_ID := l_org_id;
2910 END IF;
2911
2912 Return l_org_id;
2913 END Get_transaction_org_id;
2914
2915
2916 END psp_general;
[Home] [Help]