[Home] [Help]
PACKAGE BODY: APPS.PAYVWELE
Source
1 PACKAGE BODY payvwele AS
2 /* $Header: payvwele.pkb 115.4 2003/05/09 12:34:49 rsirigir ship $ */
3 --
4 --
5 PROCEDURE forms_startup ( p_assignment_id NUMBER,
6 p_assignment_action_id NUMBER,
7 p_session_date DATE,
8 p_action_date IN OUT NOCOPY DATE,
9 p_per_month IN OUT NOCOPY NUMBER,
10 p_per_qtd IN OUT NOCOPY NUMBER,
11 p_per_ytd IN OUT NOCOPY NUMBER,
12 p_asg_lr IN OUT NOCOPY NUMBER,
13 p_asg_ptd IN OUT NOCOPY NUMBER,
14 p_asg_month IN OUT NOCOPY NUMBER,
15 p_asg_qtd IN OUT NOCOPY NUMBER,
16 p_asg_ytd IN OUT NOCOPY NUMBER,
17 p_asg_itd IN OUT NOCOPY NUMBER,
18 p_asg_gre_itd IN OUT NOCOPY NUMBER,
19 p_tax_unit_id IN OUT NOCOPY NUMBER,
20 p_level IN VARCHAR2,
21 p_legislation_code IN VARCHAR2) IS
22 --
23 -- declare local variables
24 --
25 l_session_date DATE;
26
27 BEGIN
28 --
29 hr_utility.set_location('payvwele.forms_startup', 0);
30 --
31 IF p_assignment_action_id = -1 THEN
32 l_session_date := get_fpd_or_atd(p_assignment_id, p_session_date);
33 IF l_session_date IS NULL THEN --current employee
34 l_session_date := p_session_date;
35 ELSIF l_session_date >= p_session_date THEN
36 --current employee at p_session_date time
37 l_session_date := p_session_date;
38 END IF;
39 ELSE
40 l_session_date := p_session_date;
41 END IF;
42 --
43
44 P_PER_MONTH := GET_DIM_ID('_PER_'||p_level||'_'||'MONTH',p_legislation_code);
45 P_PER_QTD := GET_DIM_ID('_PER_'||p_level||'_'||'QTD',p_legislation_code);
46 P_PER_YTD := GET_DIM_ID('_PER_'||p_level||'_'||'YTD',p_legislation_code);
47 P_ASG_LR := GET_DIM_ID('_ASG_'||p_level||'_'||'RUN',p_legislation_code);
48 P_ASG_PTD := GET_DIM_ID('_ASG_'||p_level||'_'||'PTD',p_legislation_code);
49 P_ASG_MONTH := GET_DIM_ID('_ASG_'||p_level||'_'||'MONTH',p_legislation_code);
50 P_ASG_QTD := GET_DIM_ID('_ASG_'||p_level||'_'||'QTD',p_legislation_code);
51 P_ASG_YTD := GET_DIM_ID('_ASG_'||p_level||'_'||'YTD',p_legislation_code);
52 P_ASG_ITD := GET_DIM_ID('_ASG_'||p_level||'_'||'ITD',p_legislation_code);
53 P_ASG_GRE_ITD := GET_DIM_ID('_ASG_GRE_ITD',p_legislation_code);
54 --
55 hr_utility.set_location('payvwele.forms_startup', 1);
56 --
57 -- Commented out as the user can now select the GRE in the form,
58 -- so don't want to be restricted to the GRE of the session date.
59 --
60 --p_tax_unit_id := get_tax_unit_id ( p_assignment_id, l_session_date);
61 --
62 hr_utility.set_location('payvwele.forms_startup', 2);
63 --
64 p_action_date := get_action_date (p_assignment_action_id);
65 --
66 hr_utility.set_location('payvwele.forms_startup', 3);
67 --
68 END forms_startup;
69 --
70 FUNCTION get_tax_unit_id ( p_assignment_id NUMBER,
71 p_session_date DATE ) RETURN NUMBER IS
72 --
73 -- declare local variables
74 --
75 l_tax_unit_id NUMBER(9);
76 --
77 BEGIN
78 --
79 hr_utility.set_location('payvwele.get_tax_unit_id', 0);
80 --
81 SELECT scl.segment1
82 INTO l_tax_unit_id
83 FROM hr_soft_coding_keyflex scl,
84 per_assignments_f paf
85 where scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
86 and paf.assignment_id = p_assignment_id
87 and p_session_date between paf.effective_start_date AND paf.effective_end_date;
88 --
89 --
90 hr_utility.set_location('payvwele.get_tax_unit_id', 1);
91 --
92 RETURN l_tax_unit_id;
93 --
94 exception
95 when NO_DATA_FOUND
96 then
97 hr_utility.set_message('801', 'PAY_7785_VWELE_NO_GRE');
98 hr_utility.raise_error;
99 END get_tax_unit_id;
100 --
101 --
102 --
103 FUNCTION get_dim_id (p_dim_suffix IN VARCHAR2, p_legislation_code IN VARCHAR2) RETURN NUMBER is
104 --
105 v_dim_id number(9);
106 --
107 begin
108 --
109 hr_utility.set_location('payvwele.get_dim_id', 0);
110 --
111 select balance_dimension_id
112 into v_dim_id
113 from pay_balance_dimensions
114 where database_item_suffix = p_dim_suffix
115 and legislation_code = p_legislation_code; /* bug372487 */
116
117 --change for CA use decode
118 --
119 hr_utility.set_location('payvwele.get_dim_id', 1);
120 --
121 return v_dim_id;
122 --
123 exception when no_data_found
124 then
125 hr_utility.set_message(801, 'PAY_7784_VWELE_NO_BAL_DIM_ID');
126 hr_utility.set_message_token('BAL_DIM_ID', p_dim_suffix);
127 hr_utility.raise_error;
128 --
129 end get_dim_id;
130 --
131 --
132 --
133 FUNCTION get_action_date (p_assignment_action_id NUMBER) RETURN DATE IS
134
135 v_action_date DATE;
136
137 BEGIN
138 --
139 --
140 hr_utility.set_location('payvwele.get_action_date', 0);
141
142 IF (p_assignment_action_id = -1) THEN
143
144 hr_utility.set_location('payvwele.get_action_date', 11);
145 v_action_date := '';
146
147 ELSE
148
149 hr_utility.set_location('payvwele.get_action_date', 12);
150 SELECT ppa.effective_date
151 INTO v_action_date
152 FROM pay_payroll_actions ppa,
153 pay_assignment_actions paa
154 WHERE paa.assignment_action_id = p_assignment_action_id
155 AND ppa.payroll_action_id = paa.payroll_action_id;
156
157 END IF;
158
159 hr_utility.set_location('payvwele.get_action_date', 2);
160 return v_action_date;
161 --
162 END get_action_date;
163 --
164 --
165 FUNCTION get_fpd_or_atd(p_assignment_id IN NUMBER,
166 p_session_date IN DATE) RETURN DATE IS
167
168 CURSOR get_fpd_or_atd IS
169 SELECT pps.final_process_date
170 FROM per_periods_of_service pps
171 WHERE date_start <= p_session_date
172 AND pps.period_of_service_id = (
173 SELECT DISTINCT(period_of_service_id)
174 FROM per_all_assignments_f
175 WHERE assignment_id = p_assignment_id
176 AND assignment_type = 'E');
177
178 --
179 -- declare local variables
180 --
181 l_session_date DATE;
182
183
184 BEGIN
185 --get the final processing date or the actual processing date
186 OPEN get_fpd_or_atd;
187 FETCH get_fpd_or_atd INTO l_session_date;
188 CLOSE get_fpd_or_atd;
189
190
191 RETURN l_session_date;
192 END get_fpd_or_atd;
193 --
194
195 END payvwele;