1 PACKAGE BODY PAY_SE_EXPIRY_SUPPORT AS
2 /*$Header: pyseexsu.pkb 120.1 2011/02/09 09:56:23 knadhan ship $*/
3 -- --------------------------------------------------------------------------
4 -- This is the procedure called by the core logic that manages the expiry of
5 -- latest balances. Its interface is fixed as it is called dynamically.
6 --
7 -- It will return the following output indicating the latest balance expiration
8 -- status ...
9 --
10 -- p_expiry_information = 1 - Expired
11 -- p_expiry_information = 0 - OK
12 -- --------------------------------------------------------------------------
13 --
14 PROCEDURE holiday_pay_ec
15 (p_owner_payroll_action_id NUMBER
16 ,p_user_payroll_action_id NUMBER
17 ,p_owner_assignment_action_id NUMBER
18 ,p_user_assignment_action_id NUMBER
19 ,p_owner_effective_date DATE
20 ,p_user_effective_date DATE
21 ,p_dimension_name VARCHAR2
22 ,p_expiry_information OUT NOCOPY NUMBER) IS
23 --
24 --
25 -- Find the business group of the payroll action and also the period type of the
26 -- balance dimension.
27 --
28 CURSOR csr_info(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
29 SELECT bd.period_type
30 ,pact.business_group_id
31 FROM pay_payroll_actions pact
32 ,hr_organization_information hoi
33 ,pay_balance_dimensions bd
34 WHERE pact.payroll_action_id = p_payroll_action_id
35 AND hoi.organization_id = pact.business_group_id
36 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
37 AND bd.dimension_name = p_dimension_name
38 AND bd.legislation_code = hoi.org_information9;
39 --
40 --
41 -- Local variables.
42 --
43 l_rec csr_info%ROWTYPE;
44 l_expiry_date DATE;
45 BEGIN
46
47 --
48 --
49 -- Find the business group and also the period type of the balance dimension.
50 --
51 OPEN csr_info(p_owner_payroll_action_id, p_dimension_name);
52 FETCH csr_info INTO l_rec;
53 CLOSE csr_info;
54 --
55 --
56 -- Based on the period type of the balance dimension get the expiry date.
57 --
58 IF l_rec.period_type = 'HYEAR' THEN
59
60 l_expiry_date := hyear_ec(p_owner_assignment_action_id,p_owner_effective_date);
61 --
62 END IF;
63 hr_utility.trace(' l_expiry_date => ' || l_expiry_date );
64 --
65 --
66 -- See if the current effective date is within the same span of time as the
67 -- balance's effective date. If yes then it is OK to use cached balance
68 -- otherwise the balance has expired.
69 --
70 IF p_user_effective_date >= l_expiry_date THEN
71 P_expiry_information := 1; -- Expired!
72 ELSE
73 P_expiry_information := 0; -- OK!
74 END IF;
75 /*
76 IF p_dimension_name ='Assignment Previous Holiday Year to Date' AND l_rec.period_type = 'HYEAR' THEN
77 P_expiry_information := 1; -- OK!
78 END IF;
79 */
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 p_expiry_information := NULL;
84 END holiday_pay_ec;
85 --
86 -- ----------------------------------------------------------------------------
87 -- This is the overloaded procedure which returns actual expiry date
88 -- ----------------------------------------------------------------------------
89 --
90 PROCEDURE holiday_pay_ec
91 (p_owner_payroll_action_id NUMBER
92 ,p_user_payroll_action_id NUMBER
93 ,p_owner_assignment_action_id NUMBER
94 ,p_user_assignment_action_id NUMBER
95 ,p_owner_effective_date DATE
96 ,p_user_effective_date DATE
97 ,p_dimension_name VARCHAR2
98 ,p_expiry_information OUT NOCOPY DATE) IS
99 --
100 --
101 -- Find the business group of the payroll action and also the period type of the
102 -- balance dimension.
103 --
104 CURSOR csr_info(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
105 SELECT bd.period_type
106 ,pact.business_group_id
107 FROM pay_payroll_actions pact
108 ,hr_organization_information hoi
109 ,pay_balance_dimensions bd
110 WHERE pact.payroll_action_id = p_payroll_action_id
111 AND hoi.organization_id = pact.business_group_id
112 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
113 AND bd.dimension_name = p_dimension_name
114 AND bd.legislation_code = hoi.org_information9;
115 --
116 --
117 -- Local variables.
118 --
119 l_rec csr_info%ROWTYPE;
120 BEGIN
121
122 --
123 --
124
125 -- Find the business group and also the period type of the balance dimension.
126 --
127 OPEN csr_info(p_owner_payroll_action_id, p_dimension_name);
128 FETCH csr_info INTO l_rec;
129 CLOSE csr_info;
130 --
131 --
132 -- Based on the period type of the balance dimension get the expiry date.
133 --
134 IF l_rec.period_type = 'HYEAR' THEN
135
136 p_expiry_information := hyear_ec(p_owner_assignment_action_id,p_owner_effective_date)-1;
137
138 /*
139 IF p_dimension_name ='Assignment Previous Holiday Year to Date' THEN
140
141 p_expiry_information := p_owner_effective_date - 36500;
142
143 END IF;
144 */
145
146 END IF;
147
148
149 hr_utility.trace(' p_expiry_information => ' || p_expiry_information);
150 --
151 --
152 -- See if the current effective date is within the same span of time as the
153 -- balance's effective date. If yes then it is OK to use cached balance
154 -- otherwise the balance has expired.
155 --
156 EXCEPTION
157 WHEN OTHERS THEN
158 p_expiry_information := NULL;
159 END holiday_pay_ec;
160
161 function hyear_ec
162 (p_assignment_action_id IN number,
163 p_effective_date IN date
164 ) RETURN DATE IS
165
166 l_assignment_id number;
167 l_business_group_id NUMBER;
168 l_start_month number;
169 l_assignment NUMBER;
170
171 /*
172 CURSOR csr_assignment IS
173 SELECT assignment_id
174 FROM pay_assignment_actions
175 WHERE assignment_action_id=p_assignment_action_id;
176 */
177 /* 9761058 start */
178 CURSOR csr_assignment IS
179 SELECT paf.assignment_id, paf.business_group_id
180 FROM pay_assignment_actions paa, per_all_assignments_f paf
181 WHERE paa.assignment_action_id=p_assignment_action_id
182 AND paa.assignment_id=paf.assignment_id
183 AND p_effective_date BETWEEN paf.EFFECTIVE_START_DATE AND paf.EFFECTIVE_END_DATE ;
184
185 /* 9761058 end */
186 --CURSOR csr_holiday_start IS
187
188 CURSOR csr_holiday_start (l_business_group_id number,
189 l_assignment_id number)IS
190 SELECT substr(hoi4.ORG_INFORMATION3,4,2)
191 FROM HR_ORGANIZATION_UNITS o1
192 ,HR_ORGANIZATION_INFORMATION hoi1
193 ,HR_ORGANIZATION_INFORMATION hoi2
194 ,HR_ORGANIZATION_INFORMATION hoi3
195 ,HR_ORGANIZATION_INFORMATION hoi4
196 ,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
197 FROM PER_ALL_ASSIGNMENTS_F ASG
198 ,HR_SOFT_CODING_KEYFLEX SCL
199 WHERE ASG.ASSIGNMENT_ID = l_assignment_id
200 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
201 AND p_effective_date BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
202 WHERE o1.business_group_id = l_business_group_id
203 AND hoi1.organization_id = o1.organization_id
204 AND hoi1.organization_id = X.ORG_ID
205 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
206 AND hoi1.org_information_context = 'CLASS'
207 AND o1.organization_id = hoi2.org_information1
208 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
209 AND hoi2.organization_id = hoi3.organization_id
210 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
211 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
212 AND hoi3.organization_id = hoi4.organization_id
213 AND hoi4.ORG_INFORMATION_CONTEXT='SE_HOLIDAY_YEAR_DEFN'
214 AND hoi4.org_information1 IS NOT NULL;
215 BEGIN
216 OPEN csr_assignment;
217 FETCH csr_assignment INTO l_assignment,l_business_group_id; -- 9761058
218 CLOSE csr_assignment;
219 OPEN csr_holiday_start(l_business_group_id,l_assignment); -- 9761058
220 FETCH csr_holiday_start INTO l_start_month;
221 CLOSE csr_holiday_start;
222 IF to_number(to_char(p_effective_date,'MM'))< l_start_month THEN
223 return (to_date('01/' || l_start_month || '/' || to_char(p_effective_date,'YYYY') , 'DD/MM/YYYY'));
224 ELSE
225 return (to_date('01/' || l_start_month || '/' || to_char(to_number(to_char(p_effective_date,'YYYY'))+1) , 'DD/MM/YYYY')); -- 9761058
226 END IF;
227 END hyear_ec;
228
229 END PAY_SE_EXPIRY_SUPPORT;
230
[Home] [Help]