1 PACKAGE BODY pay_balance_utilities AS
2 /* $Header: pyblutil.pkb 120.1 2005/10/05 03:16:56 schauhan noship $ */
3
4 /* Name : get_work_codes
5 Purpose : To get the work state code, work state name, work city code,
6 work city name, work county code and work county name.
7
8 This procedure is a copy of the procedure in PAY_US_EMP_DT_TAX_VAL
9 which is a Rel 11 package for Date-tracked W4. It is duplicated here
10 so that no dependancy on the patch for W4 is needed. In the Rel 11
11 version of this form the above package is used.
12 */
13
14 procedure get_work_codes (p_assignment_id in number,
15 p_session_date in date,
16 p_work_state_code out nocopy varchar2,
17 p_work_county_code out nocopy varchar2,
18 p_work_city_code out nocopy varchar2,
19 p_work_state_name out nocopy varchar2,
20 p_work_county_name out nocopy varchar2,
21 p_work_city_name out nocopy varchar2) is
22
23 /* Cursor to get the work state, county and city */
24 cursor csr_get_work is
25 select pus.state_code,
26 puc.county_code,
27 puci.city_code,
28 pus.state_name,
29 puc.county_name,
30 puci.city_name
31 from PER_ASSIGNMENTS_F paf,
32 HR_LOCATIONS hrl,
33 PAY_US_STATES pus,
34 PAY_US_COUNTIES puc,
35 PAY_US_CITY_NAMES puci
36 where paf.assignment_id = p_assignment_id
37 and p_session_date between paf.effective_start_date and
38 paf.effective_end_date
39 and paf.location_id = hrl.location_id
40 and pus.state_abbrev = hrl.region_2
41 and puc.state_code = pus.state_code
42 and puc.county_name = hrl.region_1
43 and puci.state_code = pus.state_code
44 and puci.county_code = puc.county_code
45 and puci.city_name = hrl.town_or_city;
46
47 begin
48 hr_utility.set_location('hr_tools.get_work_codes',1);
49 /* Get the work location details */
50 open csr_get_work;
51 fetch csr_get_work into p_work_state_code,
52 p_work_county_code,
53 p_work_city_code,
54 p_work_state_name,
55 p_work_county_name,
56 p_work_city_name;
57 if csr_get_work%NOTFOUND then
58 p_work_state_code := null;
59 p_work_county_code := null;
60 p_work_city_code := null;
61 p_work_state_name := null;
62 p_work_county_name := null;
63 p_work_city_name := null;
64 end if;
65 hr_utility.set_location('hr_tools.get_work_codes',3);
66 close csr_get_work;
67 end get_work_codes;
68
69
70 FUNCTION get_current_asact_id (p_date IN DATE,
71 p_assignment_id IN NUMBER,
72 p_tax_unit_id IN NUMBER,
73 p_action_type IN OUT NOCOPY VARCHAR2,
74 p_eff_date IN OUT NOCOPY DATE)RETURN NUMBER IS
75
76 /* Name : get_current_asact_id
77 Purpose : To get the assignment_action_id given a date and assignment id. The
78 function finds the greatest assignment action id with an effective
79 date on or before the date parameter. If there are no actions in the
80 same year, the function returns a 0 for the assignemnt_action_id.
81 */
82 CURSOR c_asact IS
83 SELECT max(paa.assignment_action_id), ppa.action_type, ppa.effective_date
84 FROM pay_payroll_actions ppa,
85 pay_assignment_actions paa
86 WHERE paa.assignment_id = p_assignment_id
87 AND ppa.effective_date <= p_date
88 AND to_char(ppa.effective_date,'YYYY') = to_char(p_date,'YYYY')
89 and ppa.payroll_action_id = paa.payroll_action_id
90 and ppa.action_type in ('Q','R','V','I','B')
91 and paa.action_status = 'C'
92 and paa.tax_unit_id = p_tax_unit_id
93 and paa.action_sequence =
94 ( SELECT max(paa2.action_sequence)
95 FROM pay_assignment_actions paa2,
96 pay_payroll_actions ppa2
97 WHERE paa2.assignment_id = paa.assignment_id
98 AND ppa2.effective_date <= p_date
99 AND to_char(ppa2.effective_date,'YYYY') = to_char(p_date,'YYYY')
100 and ppa2.payroll_action_id = paa2.payroll_action_id
101 and ppa2.action_type in ('Q','R','V','I','B')
102 and paa2.action_status = 'C'
103 and paa2.tax_unit_id = paa.tax_unit_id
104 )
105 group by action_type, effective_date;
106
107 l_asact NUMBER;
108
109
110 BEGIN
111 OPEN c_asact;
112 FETCH c_asact INTO l_asact, p_action_type, p_eff_date;
113 IF c_asact%NOTFOUND
114 THEN
115 l_asact:= 0;
116 END IF;
117 CLOSE c_asact;
118 RETURN l_asact;
119
120 END;
121
122
123 end pay_balance_utilities;
[Home] [Help]