1 package body pay_pyieexc as
2 /* $Header: pyieexc.pkb 115.5 2003/01/28 10:14:34 mmahmad ship $ */
3 /* Copyright (c) Oracle Corporation 1994. All 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 */
39 /*------------------------------ ASG_PTD_EC ----------------------------*/
40 /*
41 NAME
42 ASG_PTD_EC - Assignment Period to Date expiry check.
43 DESCRIPTION
44 Expiry checking code for the following:
45 IE Assignment-level Period To Date Balance Dimension
46 NOTES
47 The associated dimension is expiry checked at assignment action level
48 */
49 procedure ASG_PTD_EC
50 (
51 p_owner_payroll_action_id in number, -- run created balance.
52 p_user_payroll_action_id in number, -- current run.
53 p_owner_assignment_action_id in number, -- assact created balance.
54 p_user_assignment_action_id in number, -- current assact..
55 p_owner_effective_date in date, -- eff date of balance.
56 p_user_effective_date in date, -- eff date of current run.
57 p_dimension_name in varchar2, -- balance dimension name.
58 p_expiry_information out NOCOPY number -- dimension expired flag.
59 ) is
60 l_period_start_date date;
61 begin
62 select ptp.start_date
63 into l_period_start_date
64 from per_time_periods ptp, pay_payroll_actions ppa
65 where ppa.payroll_action_id = p_user_payroll_action_id
66 and ppa.payroll_id = ptp.payroll_id
67 and p_user_effective_date between ptp.start_date and ptp.end_date;
68 -- see if balance was written in this period. If not it is expired
69 IF p_owner_effective_date >= l_period_start_date THEN
70 p_expiry_information := 0;
71 ELSE
72 p_expiry_information := 1;
73 END IF;
74 end ASG_PTD_EC;
75
76 /*------------------------------ ASG_YTD_EC ----------------------------*/
77 /*
78 NAME
79 ASG_YTD_EC - Assignment Tax Year to Date expiry check.
80 DESCRIPTION
81 Expiry checking code for the following:
82 IE Assignment-level Tax Year To Date Balance Dimension
83 NOTES
84 The associated dimension is expiry checked at assignment action level
85 */
86 procedure ASG_YTD_EC
87 (
88 p_owner_payroll_action_id in number, -- run created balance.
89 p_user_payroll_action_id in number, -- current run.
90 p_owner_assignment_action_id in number, -- assact created balance.
91 p_user_assignment_action_id in number, -- current assact..
92 p_owner_effective_date in date, -- eff date of balance.
93 p_user_effective_date in date, -- eff date of current run.
94 p_dimension_name in varchar2, -- balance dimension name.
95 p_expiry_information out NOCOPY number -- dimension expired flag.
96 ) is
97 l_tax_year_start date;
98 l_user_payroll_id number;
99 begin
100 /* select the start of the financial year - if the owning action is
101 * before this or for a different payroll then its expired
102 */
103 Select to_date('01-01-' || to_char( fnd_number.canonical_to_number(
104 to_char( BACT.effective_date,'YYYY'))
105 + decode(sign( BACT.effective_date - to_date('01-01-'
106 || to_char(BACT.effective_date,'YYYY'),'DD-MM-YYYY')),
107 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
108 into l_tax_year_start, l_user_payroll_id
109 from pay_payroll_actions BACT
110 where BACT.payroll_action_id = p_user_payroll_action_id;
111 --
112 --
113 if p_owner_effective_date < l_tax_year_start then
114 p_expiry_information := 1;
115 else
116 p_expiry_information := 0;
117 end if;
118 --
119
120 end ASG_YTD_EC;
121
122 /*------------------------------ ASG_PROC_PTD_EC ----------------------------*/
123 /*
124 NAME
125 ASG_PROC_PTD_EC - Assignment Processing Period to Date expiry check.
126 DESCRIPTION
127 Expiry checking code for the following:
128 IE Assignment-level Process Period To Date Balance Dimension
129 NOTES
130 The associated dimension is expiry checked at payroll action level
131 */
132 procedure ASG_PROC_PTD_EC
133 (
134 p_owner_payroll_action_id in number, -- run created balance.
135 p_user_payroll_action_id in number, -- current run.
136 p_owner_assignment_action_id in number, -- assact created balance.
137 p_user_assignment_action_id in number, -- current assact..
138 p_owner_effective_date in date, -- eff date of balance.
139 p_user_effective_date in date, -- eff date of current run.
140 p_dimension_name in varchar2, -- balance dimension name.
141 p_expiry_information out NOCOPY number -- dimension expired flag.
142 ) is
143 l_user_time_period_id number;
144 l_owner_time_period_id number;
145 begin
146 /*
147 * Select the period of the owning and using action and if they are
148 * the same then the dimension has expired - either a prior period
149 * or a different payroll
150 */
151
152 select time_period_id
153 into l_user_time_period_id
154 from pay_payroll_actions
155 where payroll_action_id = p_user_payroll_action_id;
156
157 select time_period_id
158 into l_owner_time_period_id
159 from pay_payroll_actions
160 where payroll_action_id = p_owner_payroll_action_id;
161
162 if l_user_time_period_id = l_owner_time_period_id then
163 p_expiry_information := 0;
164 else
165 p_expiry_information := 1;
166 end if;
167
168 end ASG_PROC_PTD_EC;
169
170 --------------------------------------------------------------
171
172 end pay_pyieexc;
[Home] [Help]