1 PACKAGE BODY PAY_ZA_PAYROLL_ACTION_PKG as
2 /* $Header: pyzapay.pkb 120.2.12010000.1 2008/07/28 00:04:00 appldev ship $ */
3
4 procedure total_payment (p_assignment_action_id in number,
5 p_total_payment out nocopy number) is
6
7 cursor csr_payment is select sum(result_value)
8 from pay_element_types_v1
9 where classification_name in ('Normal Income','Allowances',
10 'Direct Payments','Lump Sum Amounts')
11 and p_assignment_action_id = assignment_action_id;
12 begin
13 open csr_payment;
14 fetch csr_payment into p_total_payment;
15 close csr_payment;
16 --
17 exception
18 when others then
19 p_total_payment := null;
20 --
21 end total_payment;
22
23 ------------------------------------------------------------------------------
24 procedure total_deduct (p_assignment_action_id in number,
25 p_total_deduct out nocopy number) is
26 cursor csr_deduct is select sum(result_value)
27 from pay_element_types_v1
28 where classification_name in ('Statutory Deductions', 'Deductions',
29 'Involuntary Deductions','Voluntary Deductions')
30 and p_assignment_action_id = assignment_action_id;
31 begin
32 open csr_deduct;
33 fetch csr_deduct into p_total_deduct;
34 close csr_deduct;
35 --
36 exception
37 when others then
38 p_total_deduct := null;
39 --
40 end total_deduct;
41 -------------------------------------------------------------------------
42 function defined_balance_id (p_balance_type in varchar2,
43 p_dimension_suffix in varchar2) return number is
44 --
45 l_legislation_code varchar2(30) := 'ZA';
46 --
47 l_found BOOLEAN := FALSE;
48
49 l_balance_name VARCHAR2(80);
50 l_balance_suffix VARCHAR2(30);
51
52 CURSOR c_defined_balance IS
53 SELECT
54 defined_balance_id
55 FROM
56 pay_defined_balances PDB,
57 pay_balance_dimensions PBD,
58 pay_balance_types PBT
59 WHERE PBT.balance_name = p_balance_type
60 AND nvl(PBT.legislation_code, l_legislation_code) = l_legislation_code -- Bug 4377803; added nvl function
61 AND PDB.balance_type_id = PBT.balance_type_id
62 AND PBD.balance_dimension_id = PDB.balance_dimension_id
63 AND nvl(PDB.legislation_code, l_legislation_code) = l_legislation_code -- Bug 4377803; added nvl function
64 AND PBD.database_item_suffix = p_dimension_suffix;
65
66
67
68 --
69 l_result number;
70 --
71 begin
72
73 open c_defined_balance;
74 fetch c_defined_balance into l_result;
75 close c_defined_balance;
76
77 return l_result;
78 end;
79 --
80 -- bug no 4276047
81 FUNCTION get_balance_reporting_name (p_balance_type in varchar2
82 ) return varchar2 is
83 l_legislation_code varchar2(30) := 'ZA';
84 l_reporting_name pay_balance_types_tl.REPORTING_NAME%type;
85 Cursor get_bal_rpt_name is
86 SELECT
87 substr( nvl(pbtl.REPORTING_NAME, nvl(pbt.REPORTING_NAME,pbt.balance_name)),1,50)
88 FROM
89 pay_balance_types PBT,
90 pay_balance_types_tl PBTl
91 WHERE PBT.balance_name = p_balance_type
92 AND nvl(PBT.legislation_code, l_legislation_code) = l_legislation_code -- Bug 4377803; added nvl function
93 and pbt.BALANCE_TYPE_ID = pbtl.BALANCE_TYPE_ID (+)
94 and pbtl.LANGUAGE(+) = userenv('LANG');
95 begin
96 OPEN get_bal_rpt_name;
97 FETCH get_bal_rpt_name INTO l_reporting_name;
98 IF get_bal_rpt_name%ISOPEN then
99 CLOSE get_bal_rpt_name;
100 END if;
101 return l_reporting_name;
102 exception
103 when others then
104 RETURN p_balance_type;
105 END;
106 -- bug no 4276047
107
108 -- bug 4751740: start
109 -- Call to GB Package from PAYZASOE is not a good coding practice
110 -- Thus, duplicate of PAY_GB_PAYROLL_ACTIONS_PKG.FORMULA_INPUTS_WF and
111 -- PAY_GB_PAYROLL_ACTIONS_PKG.FORMULA_INPUTS_HC should be created in this
112 -- ZA package.
113
114 -------------------------------------------------------------------------------
115 -- This procedure is dupllicate of PAY_GB_PAYROLL_ACTIONS_PKG.FORMULA_INPUTS_WF
116 -- It(PAY_GB_PAYROLL_ACTIONS_PKG) was at 37th version when it's copied
117 --
118 procedure formula_inputs_wf (p_session_date in date,
119 p_payroll_exists in out nocopy varchar2,
120 p_assignment_action_id in out nocopy number,
121 p_run_assignment_action_id in out nocopy number,
122 p_assignment_id in number,
123 p_payroll_action_id in out nocopy number,
124 p_date_earned in out nocopy varchar2) is
125 -- select the latest prepayments action for this individual and get the
126 -- details of the last run that that action locked
127 cursor csr_formula is
128 select /*+ ORDERED USE_NL(paa,ppa,rpaa,rppa) */
129 to_char(nvl(rppa.date_earned,rppa.effective_date),'YYYY/MM/DD'),
130 rpaa.payroll_action_id,
131 rpaa.assignment_action_id,
132 paa.assignment_action_id
133 from pay_assignment_actions paa,
134 pay_payroll_actions ppa,
135 pay_assignment_actions rpaa,
136 pay_payroll_actions rppa
137 where paa.payroll_action_id = ppa.payroll_action_id
138 and rppa.payroll_action_id = rpaa.payroll_action_id
139 and paa.assignment_id = rpaa.assignment_id
140 and paa.assignment_action_id =
141 (select
142 to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
143 from pay_payroll_actions pa,
144 pay_assignment_actions aa
145 where pa.action_type in ('U','P')
146 and aa.action_status = 'C'
147 and pa.payroll_action_id = aa.payroll_action_id
148 and aa.assignment_id = p_assignment_id
149 and pa.effective_date <= p_session_date)
150 and ppa.action_type in ('P', 'U')
151 and rpaa.assignment_id = p_assignment_id
152 and rpaa.action_sequence =
153 (select max(aa.action_sequence)
154 from pay_assignment_actions aa,
155 pay_action_interlocks loc
156 where loc.locked_action_id = aa.assignment_action_id
157 and loc.locking_action_id = paa.assignment_action_id);
158
159 -- Copied from HR_GBBAL.get_latest_action_id, include action type P and U
160 cursor csr_formula_2 is
161 SELECT /*+ USE_NL(paa, ppa) */
162 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
163 paa.assignment_action_id),16))
164 FROM pay_assignment_actions paa,
165 pay_payroll_actions ppa
166 WHERE
167 paa.assignment_id = p_assignment_id
168 AND ppa.payroll_action_id = paa.payroll_action_id
169 AND (paa.source_action_id is not null
170 or ppa.action_type in ('I','V','B'))
171 AND ppa.effective_date <= p_session_date
172 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B', 'U', 'P');
173
174 cursor csr_formula_3(p_assig_act_id NUMBER) is
175 select to_char(nvl(ppa.date_earned,ppa.effective_date),'YYYY/MM/DD'),
176 paa.payroll_action_id
177 from pay_payroll_actions ppa,
178 pay_assignment_actions paa
179 where paa.assignment_action_id = p_assig_act_id
180 and ppa.payroll_action_id = paa.payroll_action_id;
181
182 cursor csr_formula_4(p_assig_act_id NUMBER) is
183 select pact.action_type
184 from pay_assignment_actions assact,
185 pay_payroll_actions pact
186 where assact.assignment_action_id = p_assignment_action_id
187 and pact.payroll_action_id = assact.payroll_action_id;
188
189 cursor csr_formula_5(p_assig_act_id NUMBER) is
190 select assact.assignment_action_id
191 from pay_assignment_actions assact,
192 pay_action_interlocks loc
193 where loc.locking_action_id = p_assignment_action_id
194 and assact.assignment_action_id = loc.locked_action_id
195 order by assact.action_sequence desc;
196
197 --
198 l_assignment_action_id NUMBER;
199 l_action_type varchar2(1);
200 --
201 begin
202 --
203 l_assignment_action_id := null;
204 --
205 -- open csr_formula;
206 -- fetch csr_formula into p_date_earned,
207 -- p_payroll_action_id,
208 -- p_run_assignment_action_id,
209 -- p_assignment_action_id;
210 open csr_formula_2;
211 fetch csr_formula_2 into p_assignment_action_id;
212 close csr_formula_2;
213
214 if p_assignment_action_id is NOT NULL then
215 p_payroll_exists := 'TRUE';
216
217 open csr_formula_4(p_assignment_action_id);
218 fetch csr_formula_4 into l_action_type;
219 close csr_formula_4;
220
221 if l_action_type in ('P','U') then
222 open csr_formula_5(p_assignment_action_id);
223 fetch csr_formula_5 into p_run_assignment_action_id;
224 close csr_formula_5;
225 -- Bug 4584572
226 else
227 p_run_assignment_action_id := p_assignment_action_id;
228 end if;
229
230 open csr_formula_3(p_run_assignment_action_id);
231 fetch csr_formula_3 into p_date_earned,
232 p_payroll_action_id;
233 close csr_formula_3;
234
235 end if;
236 -- if csr_formula_2%FOUND then
237 -- p_payroll_exists := 'TRUE';
238 -- end if;
239 -- close csr_formula;
240 --
241 end formula_inputs_wf;
242
243 --
244 -------------------------------------------------------------------------------
245 -- This procedure is dupllicate of PAY_GB_PAYROLL_ACTIONS_PKG.FORMULA_INPUTS_HC
246 -- It(PAY_GB_PAYROLL_ACTIONS_PKG) was at 37th version when it's copied
247 --
248 procedure formula_inputs_hc (p_assignment_action_id in out nocopy number,
249 p_run_assignment_action_id in out nocopy number,
250 p_assignment_id in out nocopy number,
251 p_payroll_action_id in out nocopy number,
252 p_date_earned in out nocopy varchar2) is
253 -- if the action is a run then return the run details
254 -- if the action is a prepayment return the latest run details locked
255 cursor csr_formula is
256 -- find what type of action this is
257 select pact.action_type , assact.assignment_id
258 from pay_assignment_actions assact,
259 pay_payroll_actions pact
260 where assact.assignment_action_id = p_assignment_action_id
261 and pact.payroll_action_id = assact.payroll_action_id
262 ;
263 cursor csr_formula_2 is
264 -- for prepayment action find the latest interlocked run
265 select assact.assignment_action_id
266 from pay_assignment_actions assact,
267 pay_action_interlocks loc
268 where loc.locking_action_id = p_assignment_action_id
269 and assact.assignment_action_id = loc.locked_action_id
270 order by assact.action_sequence desc
271 ;
272 cursor csr_formula_3 is
273 -- for run action check if its been prepaid
274 select assact.assignment_action_id
275 from pay_assignment_actions assact,
276 pay_payroll_actions pact,
277 pay_action_interlocks loc
278 where loc.locked_action_id = p_assignment_action_id
279 and assact.assignment_action_id = loc.locking_action_id
280 and pact.payroll_action_id = assact.payroll_action_id
281 and pact.action_type in ('P','U') /* prepayments only */
282 order by assact.action_sequence desc
283 ;
284 cursor csr_formula_4 is
285 -- now find the date earned and payroll action of the run action
286 select pact.payroll_action_id,
287 to_char(nvl(pact.date_earned,pact.effective_date),'YYYY/MM/DD')
288 from pay_assignment_actions assact,
289 pay_payroll_actions pact
290 where assact.assignment_action_id = p_run_assignment_action_id
291 and pact.payroll_action_id = assact.payroll_action_id
292 ;
293 --
294 l_action_type varchar2(1);
295 --
296 begin
297 --
298 open csr_formula;
299 fetch csr_formula into l_action_type, p_assignment_id;
300 close csr_formula;
301 --
302 if l_action_type in ('P', 'U') then
303 open csr_formula_2;
304 fetch csr_formula_2 into p_run_assignment_action_id;
305 close csr_formula_2;
306 else
307 p_run_assignment_action_id := p_assignment_action_id;
308 end if;
309 -- fetch payroll details
310 open csr_formula_4;
311 fetch csr_formula_4 into p_payroll_action_id,
312 p_date_earned;
313 close csr_formula_4;
314 --
315 end formula_inputs_hc;
316 --
317
318 -- bug 4751740: End
319
320 procedure get_home_add(p_person_id IN NUMBER,
321 p_add1 IN out nocopy VARCHAR2,
322 p_add2 IN out nocopy VARCHAR2,
323 p_add3 IN out nocopy VARCHAR2,
324 p_reg1 IN out nocopy VARCHAR2,
325 p_reg2 IN out nocopy VARCHAR2,
326 p_reg3 IN out nocopy VARCHAR2,
327 p_twnc IN out nocopy VARCHAR2) is
328 --
329 cursor homeadd is
330 select pad.address_line1,
331 pad.address_line2,
332 pad.address_line3,
333 l.meaning,
334 pad.postal_code,
335 pad.region_3,
336 pad.town_or_city
337 from per_addresses pad,
338 hr_lookups l
339 where pad.person_id = p_person_id
340 and pad.primary_flag = 'Y'
341 and l.lookup_type(+) = 'ZA_PROVINCE'
342 and l.lookup_code(+) = pad.region_1
343 and sysdate between nvl(pad.date_from, sysdate)
344 and nvl(pad.date_to, sysdate);
345 --
346 begin
347 --
348 open homeadd;
349 --
350 fetch homeadd into p_add1,
351 p_add2,
352 p_add3,
353 p_reg1,
354 p_reg2,
355 p_reg3,
356 p_twnc;
357 --
358 close homeadd;
359
360 end get_home_add;
361
362
363
364 END PAY_ZA_PAYROLL_ACTION_PKG;
[Home] [Help]