1 package body pyieexc as
2 /* $Header: pyieexc.pkb 120.2 2006/03/05 21:37:29 rbhardwa noship $ */
3 /* Copyright (c) Oracle Corporation 1994. ll rights reserved. */
4 /*
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pyieexc.pkb - PaYroll IE legislation EXpiry Checking code.
9 DESCRIPTION
10 Contains the expiry checking code for the balance dimensions
11 created for IRELAND.
12 PUBLIC FUNCTIONS
13 <none>
14 PRIVATE FUNCTIONS
15 <none>
16 NOTES
17 expiry checking has been made much simpler than the dimensions it
18 supports by the imposition of 2 rules:
19 1) on payroll transfer latest balances are trashed ( so we don't
20 need to check for the date of transfer etc we need only worry
21 about the start date of the current payroll period and payroll
22 year.
23 2) legislative balances rely on the regular payment date of the period
24 to dictate the legislative poeriod they fall into - you can't transfer
25 onto a payroll until the new payrolls period regular payment date is
26 after the regular payment date of the last period processed. This
27 supports the rule that you can't transfer from a longer processing
28 period to a shorter one until the end of the longer period.
29
30 MODIFIED (DD/MM/YY)
31 rmakhija 15/06/01 - first created by editing similar package of GB Localization.
32 abhaduri 16/04/02 - added ASG_PROC_PTD_EC procedure for new dimension
33 _ELEMENT_PTD for Attachment of Earnings Order.
34 smrobins 30/09/02 - changed ASG_YTD_EC procedure, to derive expiry
35 from effective date of action, rather than
36 regular payment date.
37 mmahmad 24/01/03 - Added NOCOPY
38 vmkhande 16/04/03 Added expiry code for ASG_QTD
39 npershad 30/10/03 Added expiry code for ASG_TWO_YTD
40 rtulaban 26/05/04 Added start date code for ASG_TWO_YTD
41 rbhardwa 01/03/06 Made changes to accomodate offset payrolls.
42 Bug 5070091.
43 */
44 /*------------------------------ ASG_PTD_EC ----------------------------*/
45 /*
46 NAME
47 ASG_PTD_EC - Assignment Period to Date expiry check.
48 DESCRIPTION
49 Expiry checking code for the following:
50 IE Assignment-level Period To Date Balance Dimension
51 NOTES
52 The associated dimension is expiry checked at assignment action level
53 */
54 procedure ASG_PTD_EC
55 (
56 p_owner_payroll_action_id in number, -- run created balance.
57 p_user_payroll_action_id in number, -- current run.
58 p_owner_assignment_action_id in number, -- assact created balance.
59 p_user_assignment_action_id in number, -- current assact..
60 p_owner_effective_date in date, -- eff date of balance.
61 p_user_effective_date in date, -- eff date of current run.
62 p_dimension_name in varchar2, -- balance dimension name.
63 p_expiry_information out NOCOPY number -- dimension expired flag.
64 ) is
65 l_period_start_date date;
66 begin
67 select ptp.start_date
68 into l_period_start_date
69 from per_time_periods ptp, pay_payroll_actions ppa
70 where ppa.payroll_action_id = p_user_payroll_action_id
71 and ppa.payroll_id = ptp.payroll_id
72 and p_user_effective_date between ptp.start_date and ptp.end_date;
73 -- see if balance was written in this period. If not it is expired
74 IF p_owner_effective_date >= l_period_start_date THEN
75 p_expiry_information := 0;
76 ELSE
77 p_expiry_information := 1;
78 END IF;
79 end ASG_PTD_EC;
80
81 /*------------------------------ ASG_YTD_EC ----------------------------*/
82 /*
83 NAME
84 ASG_YTD_EC - Assignment Tax Year to Date expiry check.
85 DESCRIPTION
86 Expiry checking code for the following:
87 IE Assignment-level Tax Year To Date Balance Dimension
88 NOTES
89 The associated dimension is expiry checked at assignment action level
90 */
91 procedure ASG_YTD_EC
92 (
93 p_owner_payroll_action_id in number, -- run created balance.
94 p_user_payroll_action_id in number, -- current run.
95 p_owner_assignment_action_id in number, -- assact created balance.
96 p_user_assignment_action_id in number, -- current assact..
97 p_owner_effective_date in date, -- eff date of balance.
98 p_user_effective_date in date, -- eff date of current run.
99 p_dimension_name in varchar2, -- balance dimension name.
100 p_expiry_information out NOCOPY number -- dimension expired flag.
101 ) is
102 l_tax_year_start date;
103 l_user_payroll_id number;
104 begin
105 /* select the start of the financial year - if the owning action is
106 * before this or for a different payroll then its expired
107 */
108 Select to_date('01-01-' || to_char( fnd_number.canonical_to_number(
109 to_char( BACT.effective_date,'YYYY'))
110 + decode(sign( BACT.effective_date - to_date('01-01-'
111 || to_char(BACT.effective_date,'YYYY'),'DD-MM-YYYY')),
112 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
113 into l_tax_year_start, l_user_payroll_id
114 from pay_payroll_actions BACT
115 where BACT.payroll_action_id = p_user_payroll_action_id;
116 --
117 --
118 if p_owner_effective_date < l_tax_year_start then
119 p_expiry_information := 1;
120 else
121 p_expiry_information := 0;
122 end if;
123 --
124
125 end ASG_YTD_EC;
126
127 /*------------------------------ ASG_PROC_PTD_EC ----------------------------*/
128 /*
129 NAME
130 ASG_PROC_PTD_EC - Assignment Processing Period to Date expiry check.
131 DESCRIPTION
132 Expiry checking code for the following:
133 IE Assignment-level Process Period To Date Balance Dimension
134 NOTES
135 The associated dimension is expiry checked at payroll action level
136 */
137 procedure ASG_PROC_PTD_EC
138 (
139 p_owner_payroll_action_id in number, -- run created balance.
140 p_user_payroll_action_id in number, -- current run.
141 p_owner_assignment_action_id in number, -- assact created balance.
142 p_user_assignment_action_id in number, -- current assact..
143 p_owner_effective_date in date, -- eff date of balance.
144 p_user_effective_date in date, -- eff date of current run.
145 p_dimension_name in varchar2, -- balance dimension name.
146 p_expiry_information out NOCOPY number -- dimension expired flag.
147 ) is
148 l_user_time_period_id number;
149 l_owner_time_period_id number;
150 begin
151 /*
152 * Select the period of the owning and using action and if they are
153 * the same then the dimension has expired - either a prior period
154 * or a different payroll
155 */
156
157 select time_period_id
158 into l_user_time_period_id
159 from pay_payroll_actions
160 where payroll_action_id = p_user_payroll_action_id;
161
162 select time_period_id
163 into l_owner_time_period_id
164 from pay_payroll_actions
165 where payroll_action_id = p_owner_payroll_action_id;
166
167 if l_user_time_period_id = l_owner_time_period_id then
168 p_expiry_information := 0;
169 else
170 p_expiry_information := 1;
171 end if;
172
173 end ASG_PROC_PTD_EC;
174 /*------------------------------ ASG_QTD_EC ----------------------------*/
175 /*
176 NAME
177 ASG_QTD_EC - Assignment Quater to Date expiry check.
178 DESCRIPTION
179 Expiry checking code for the following:
180 IE Assignment-level Quater To Date Balance Dimension
181 NOTES
182 The associated dimension is expiry checked at assignment action level
183 */
184 procedure ASG_QTD_EC
185 (
186 p_owner_payroll_action_id in number, -- run created balance.
187 p_user_payroll_action_id in number, -- current run.
188 p_owner_assignment_action_id in number, -- assact created balance.
189 p_user_assignment_action_id in number, -- current assact..
190 p_owner_effective_date in date, -- eff date of balance.
191 p_user_effective_date in date, -- eff date of current run.
192 p_dimension_name in varchar2, -- balance dimension name.
193 p_expiry_information out NOCOPY number -- dimension expired flag.
194 ) is
195 begin
196 hr_utility.trace('p_user_effective_date ' || to_char(p_user_effective_date,'dd-mon-yyyy'));
197 hr_utility.trace('p_owner_effective_date ' || to_char(p_owner_effective_date,'dd-mon-yyyy'));
198 IF p_user_effective_date >= trunc(add_months(p_owner_effective_date,3),'Q')
199 THEN
200 hr_utility.trace(' 1 ');
201 P_expiry_information := 1; -- Expired!
202 ELSE
203 hr_utility.trace(' 0 ');
204 P_expiry_information := 0; -- OK!
205 END IF;
206 end ASG_QTD_EC;
207 /*------------------------------ ASG_TWO_YTD_EC ----------------------------*/
208 /*
209 NAME
210 ASG_TWO_YTD_EC - Assignment Tax Year/Previous Year to Date expiry check.
211 DESCRIPTION
212 Expiry checking code for the following:
213 IE Assignment-level Tax Year/Previous Year To Date Balance Dimension
214 NOTES
215 The associated dimension is expiry checked at assignment action level
216 */
217 procedure ASG_TWO_YTD_EC
218 (
219 p_owner_payroll_action_id in number, -- run created balance.
220 p_user_payroll_action_id in number, -- current run.
221 p_owner_assignment_action_id in number, -- assact created balance.
222 p_user_assignment_action_id in number, -- current assact..
223 p_owner_effective_date in date, -- eff date of balance.
224 p_user_effective_date in date, -- eff date of current run.
225 p_dimension_name in varchar2, -- balance dimension name.
226 p_expiry_information out NOCOPY number -- dimension expired flag.
227 ) is
228 l_tax_year_start date;
229 l_user_payroll_id number;
230 begin
231 /* select the start of the financial year - if the owning action is
232 * before this or for a different payroll then its expired
233 */
234 Select to_date('01-01-' || to_char((fnd_number.canonical_to_number(
235 to_char( BACT.effective_date,'YYYY')) - 1)
236 + decode(sign( BACT.effective_date - to_date('01-01-'
237 || to_char(BACT.effective_date,'YYYY'),'DD-MM-YYYY')),
238 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
239 into l_tax_year_start, l_user_payroll_id
240 from pay_payroll_actions BACT
241 where BACT.payroll_action_id = p_user_payroll_action_id;
242 --
243 --
244 if p_owner_effective_date < l_tax_year_start then
245 p_expiry_information := 1;
246 else
247 p_expiry_information := 0;
248 end if;
249 --
250
251 end ASG_TWO_YTD_EC;
252
253 ----------------------------------------------------------------------------
254 -- Procedure: PROC_TWO_YTD_START
255 -- Description: used by TWO_YTD Dimensions for Run Level Balances only.
256 -- This procedure accepts a date and assignment action and other
257 -- params, and returns the start date of the Previous Tax Year, depending
258 -- on the regular payment date of the payroll action.
259
260 procedure proc_two_ytd_start(p_period_type in varchar2 default null,
261 p_effective_date in date default null,
262 p_start_date out nocopy date,
263 p_start_date_code in varchar2 default null,
264 p_payroll_id in number,
265 p_bus_grp in number default null,
266 p_action_type in varchar2 default null,
267 p_asg_action in number)
268 is
269 l_tax_year_start date;
270 begin
271 select to_date('01-01-' || to_char(( fnd_number.canonical_to_number(
272 to_char( PPA.effective_date,'YYYY'))- 1) -- Bug 5070091 Offset payroll change
273 + decode(sign( PPA.effective_date - to_date('01-01-'
274 || to_char(PPA.effective_date,'YYYY'),'DD-MM-YYYY')),
275 -1,-1,0)),'DD-MM-YYYY') finyear
276 into l_tax_year_start
277 from --per_time_periods PTP,
278 pay_payroll_actions ppa,
279 pay_assignment_actions paa
280 where ppa.payroll_action_id = paa.payroll_action_id
281 and paa.assignment_action_id = p_asg_action
282 and ppa.payroll_id = p_payroll_id;
283 -- and PTP.time_period_id = ppa.time_period_id;
284 --
285 p_start_date := l_tax_year_start;
286 --
287 end proc_two_ytd_start;
288 ----------------------------------------------------------------------------
289
290 end pyieexc;
[Home] [Help]