1 PACKAGE body PAY_GB_ENROLL_PENSION
2 /* $Header: pygbpaep.pkb 120.91.12020000.9 2013/03/28 10:25:20 rsadhana noship $ */
3 AS
4
5 FUNCTION GET_PROFILE_OPTION(
6 p_profile_option_name IN VARCHAR)
7 RETURN VARCHAR
8 IS
9 l_profile_value VARCHAR2(50);
10 BEGIN
11 fnd_profile.get(p_profile_option_name,l_profile_value);
12 RETURN l_profile_value;
13 END GET_PROFILE_OPTION;
14 ----------------------------------------------------------------------------
15 FUNCTION FETCH_START_DATE(
16 P_DATE_OF_BIRTH DATE)
17 RETURN DATE
18 IS
19 L_DATE DATE;
20 BEGIN
21 -- RETURNS DATE OF BIRTH +22 YEARS
22 SELECT ADD_MONTHS(P_DATE_OF_BIRTH,(22*12))
23 INTO L_DATE
24 FROM DUAL;
25 RETURN L_DATE;
26 END FETCH_START_DATE;
27 ----------------------------------------------------------------------------
28 FUNCTION GET_AGGREGATED_EARNINGS(
29 P_ASSIGNMENT_ID IN NUMBER,
30 P_REGULAR_PAYMENT_DATE IN DATE,
31 P_PQE_ASG_RUN IN NUMBER)
32 RETURN NUMBER
33 IS
34 ----Local variables
35 L_AGGREGATION VARCHAR2(1) :='N';
36 L_MAIN_ENTRY VARCHAR2(1) :='N';
37 L_PRP_START_DATE DATE;
38 L_PRP_END_DATE DATE;
39 L_PAYROLL_ID NUMBER;
40 L_BALANCE_VALUE NUMBER :=0;
41 L_DEFINED_BALANCE_ID NUMBER;
42 L_TOTAL_QUAL_EARNINGS NUMBER;
43 L_PERIOD_TYPE PAY_PAYROLLS_F.PERIOD_TYPE%TYPE;
44 L_PERSON_ID NUMBER;
45 L_AGE NUMBER;
46 L_SEX PER_ALL_PEOPLE_F.SEX%TYPE;
47 L_DATE_OF_BIRTH DATE;
48 L_BUSINESS_GROUP_ID NUMBER;
49 L_EMPLOYEE_STATUS NUMBER;
50 L_AUTO_ENROL_DATE VARCHAR2(30);
51 L_ELIGIBLE_JOB_HOLDER_DATE VARCHAR2(30);
52 L_QUALIFYING_SCHEME_EXISTS VARCHAR2(1);
53 L_POSTPONEMENT_DATE VARCHAR2(30);
54 L_OPTOUT_DATE VARCHAR2(30);
55 L_NUM_DATE_BIRTH VARCHAR2(10);
56 N_STATE_PENSION_AGE NUMBER := 0;
57 RETIRE_DATE DATE;
58 L_AUTOTHRESHOLD NUMBER :=0;
59 L_LOWER_THRESHOLD NUMBER :=0;
60 L_ERROR NUMBER;
61 ------Cursor to get Globals
62
63 L_SERVICE_START date;
64 L_DOB_TT date;
65 L_EFFECTIVE_DATE date;
66 l_asg_start_date DATE;
67 --To Fetch the Assignment Start Date
68 Cursor c_asg_start_date is
69 select EFFECTIVE_START_DATE from per_all_assignments_f
70 where assignment_id = p_assignment_id
71 and l_prp_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
72 ---To Fetch Service Start Date of the employee
73 CURSOR C_SERVICE_START (P_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE)
74 IS
75 SELECT DATE_START
76 FROM PER_PERIODS_OF_SERVICE
77 WHERE PERSON_ID = P_PERSON_ID
78 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
79
80 CURSOR C_GET_GLOBAL_VALUE(P_EFFECTIVE_DATE DATE)
81 IS
82 SELECT MAX(DECODE(GLOBAL_NAME,'QE_WEEKLY_LT', GLOBAL_VALUE ,NULL)) G_QE_WEEKLY_LT ,
83 MAX(DECODE(GLOBAL_NAME,'QE_MONTHLY_LT', GLOBAL_VALUE ,NULL)) G_QE_MONTHLY_LT ,
84 MAX(DECODE(GLOBAL_NAME,'QE_ANNUAL_LT', GLOBAL_VALUE ,NULL)) G_QE_ANNUAL_LT ,
85 MAX(DECODE(GLOBAL_NAME,'QE_WEEKLY_UT', GLOBAL_VALUE ,NULL)) G_QE_WEEKLY_UT ,
86 MAX(DECODE(GLOBAL_NAME,'QE_MONTHLY_UT', GLOBAL_VALUE ,NULL)) G_QE_MONTHLY_UT ,
87 MAX(DECODE(GLOBAL_NAME,'QE_ANNUAL_UT', GLOBAL_VALUE ,NULL)) G_QE_ANNUAL_UT ,
88 MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_WEEKLY', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_WEEKLY ,
89 MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_MONTHLY', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_MONTHLY ,
90 MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_ANNUAL', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_ANNUAL,
91 MAX(DECODE(GLOBAL_NAME,'QE_BIWEEK_LT', GLOBAL_VALUE ,NULL)) G_QE_BIWEEK_LT,
92 MAX(DECODE(GLOBAL_NAME,'QE_LUNAR_LT', GLOBAL_VALUE ,NULL)) G_QE_LUNAR_LT,
93 MAX(DECODE(GLOBAL_NAME,'QE_QUARTERLY_LT', GLOBAL_VALUE ,NULL)) G_QE_QUARTERLY_LT,
94 MAX(DECODE(GLOBAL_NAME,'QE_SEMIYEAR_LT', GLOBAL_VALUE ,NULL)) G_QE_SEMIYEAR_LT,
95 MAX(DECODE(GLOBAL_NAME,'QE_BIWEEK_UT', GLOBAL_VALUE ,NULL)) G_QE_BIWEEK_UT,
96 MAX(DECODE(GLOBAL_NAME,'QE_LUNAR_UT', GLOBAL_VALUE ,NULL)) G_QE_LUNAR_UT,
97 MAX(DECODE(GLOBAL_NAME,'QE_QUARTERLY_UT', GLOBAL_VALUE ,NULL)) G_QE_QUARTERLY_UT,
98 MAX(DECODE(GLOBAL_NAME,'QE_SEMIYEAR_UT', GLOBAL_VALUE ,NULL)) G_QE_SEMIYEAR_UT,
99 MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_BIWEEK', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_BIWEEK,
100 MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_LUNAR', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_LUNAR,
101 MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_QUARTERLY', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_QUARTERLY,
102 MAX(DECODE(GLOBAL_NAME,'AUTO_ENROL_TRIG_SEMIYEAR', GLOBAL_VALUE ,NULL)) G_AUTO_ENROL_TRIG_SEMIYEAR
103 FROM FF_GLOBALS_F FGF
104 WHERE GLOBAL_NAME IN ('QE_ANNUAL_LT', 'QE_WEEKLY_LT', 'QE_MONTHLY_LT', 'QE_ANNUAL_UT', 'QE_WEEKLY_UT', 'QE_MONTHLY_UT', 'AUTO_ENROL_TRIG_ANNUAL', 'AUTO_ENROL_TRIG_WEEKLY', 'AUTO_ENROL_TRIG_MONTHLY',
105 'QE_BIWEEK_LT','QE_LUNAR_LT','QE_QUARTERLY_LT','QE_SEMIYEAR_LT', 'QE_BIWEEK_UT','QE_LUNAR_UT','QE_QUARTERLY_UT','QE_SEMIYEAR_UT', 'AUTO_ENROL_TRIG_BIWEEK','AUTO_ENROL_TRIG_LUNAR','AUTO_ENROL_TRIG_QUARTERLY',
106 'AUTO_ENROL_TRIG_SEMIYEAR')
107 AND P_EFFECTIVE_DATE BETWEEN FGF.EFFECTIVE_START_DATE AND FGF.EFFECTIVE_END_DATE;
108 G_GLOBAL_VALUE C_GET_GLOBAL_VALUE%ROWTYPE;
109 ---Cursot to get age an Gender as on Payment Date for the payroll run
110 CURSOR C_AGE_SEX(V_PERSON_ID NUMBER)
111 IS
112 SELECT MONTHS_BETWEEN(P_REGULAR_PAYMENT_DATE,DATE_OF_BIRTH)/12,
113 SEX,
114 DATE_OF_BIRTH
115 FROM PER_ALL_PEOPLE_F
116 WHERE PERSON_ID = V_PERSON_ID
117 AND P_REGULAR_PAYMENT_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
118 --Cursor for aggregated case
119 CURSOR C_ASSIGNMENTS_AGG(V_PERSON_ID NUMBER)
120 IS
121 SELECT PAAF.ASSIGNMENT_ID,
122 PAAF.PAYROLL_ID
123 FROM PER_ALL_ASSIGNMENTS_F PAAF,
124 PER_ASSIGNMENT_STATUS_TYPES PAST
125 WHERE PAAF.PERSON_ID = V_PERSON_ID
126 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
127 -- AND PAST.PAY_SYSTEM_STATUS='P'
128 AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
129 AND L_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE;
130 CURSOR C_PERSON_ID
131 IS
132 SELECT PERSON_ID
133 FROM PER_ALL_ASSIGNMENTS_F
134 WHERE ASSIGNMENT_ID =P_ASSIGNMENT_ID
135 AND L_PRP_END_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
136 --Remove as it is already defined in the Header
137 CURSOR C_DEF_BAL
138 IS
139 SELECT PDB.DEFINED_BALANCE_ID
140 FROM PAY_BALANCE_TYPES PBT,
141 PAY_DEFINED_BALANCES PDB,
142 PAY_BALANCE_DIMENSIONS PBD
143 WHERE PDB.BALANCE_TYPE_ID=PBT.BALANCE_TYPE_ID
144 AND PBT.BALANCE_NAME LIKE 'Pension Qualifying Earnings'
145 AND PBD.DATABASE_ITEM_SUFFIX='_ASG_RUN'
146 AND PBD.BALANCE_DIMENSION_ID=PDB.BALANCE_DIMENSION_ID;
147 --Remove as it is already defined in the Header
148 --To Get the Payroll Id of the Assignment
149 CURSOR C_PAYROLL_ID
150 IS
151 SELECT PAYROLL_ID,
152 BUSINESS_GROUP_ID
153 FROM PER_ALL_ASSIGNMENTS_F
154 WHERE ASSIGNMENT_ID=P_ASSIGNMENT_ID
155 AND P_REGULAR_PAYMENT_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
156 -- To get PRP Dates Corresponding to the Payroll's Date Paid
157 CURSOR C_PRP_DETAILS
158 IS
159 SELECT START_DATE,
160 END_DATE
161 FROM PER_TIME_PERIODS PTP
162 WHERE P_REGULAR_PAYMENT_DATE BETWEEN START_DATE AND END_DATE
163 AND PAYROLL_ID=L_PAYROLL_ID;
164 --To get the Period_type
165 CURSOR C_PERIOD_TYPE(V_PAYROLL_ID NUMBER)
166 IS
167 SELECT period_type
168 FROM pay_payrolls_f
169 WHERE payroll_id = V_PAYROLL_ID
170 AND L_PRP_END_DATE BETWEEN effective_start_date AND effective_end_date ;
171 --Changed for Pensions 2 - to fetch all the assignment actions for the assignment within the PRP
172 CURSOR C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID NUMBER)
173 IS
174 SELECT ASSIGNMENT_ACTION_ID,
175 PPA.EFFECTIVE_DATE DATE_PAID,
176 ptp.end_date DATE_EARNED,
177 ptp.TIME_PERIOD_ID,
178 ptp.PAYROLL_ID
179 FROM PAY_PAYROLL_ACTIONS PPA,
180 PAY_ASSIGNMENT_ACTIONS PAA,
181 PER_TIME_PERIODS PTP
182 WHERE PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
183 AND REGULAR_PAYMENT_DATE = PPA.EFFECTIVE_DATE
184 AND ptp.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
185 -- AND PPA.TIME_PERIOD_ID = NVL(P_TIME_PERIOD,PPA.TIME_PERIOD_ID)
186 AND ASSIGNMENT_ID =P_ASSIGNMENT_ID
187 AND PPA.ACTION_TYPE IN ('Q','R')
188 AND PPA.EFFECTIVE_DATE BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE
189 -- AND BUSINESS_GROUP_ID =G_BUSINESS_GROUP_ID
190 AND PAA.ACTION_STATUS ='C'
191 AND SOURCE_ACTION_ID IS NOT NULL
192 ORDER BY TIME_PERIOD_ID,
193 ASSIGNMENT_ACTION_ID DESC;
194 --Cursor to check for Aggregation as on PRP End date
195 CURSOR C_AGGREGATION_CHECK(V_ASSIGNMENT_ID NUMBER)
196 IS
197 SELECT MAX(DECODE(pivf.name,'Aggregate Earnings',peevf.screen_entry_value)) Agg_earnings,
198 MAX(DECODE(pivf.name,'Main Entry for Aggregation',peevf.screen_entry_value)) Main_entry
199 FROM per_all_assignments_f paaf,
200 pay_element_types_f petf,
201 pay_input_values_f pivf,
202 pay_element_entries_f peef,
203 pay_element_entry_values_f peevf
204 WHERE paaf.assignment_id = V_ASSIGNMENT_ID
205 AND petf.element_name ='Pensions Information'
206 AND petf.legislation_code = 'GB'
207 AND pivf.name IN ( 'Aggregate Earnings','Main Entry for Aggregation')
208 AND pivf.legislation_code = 'GB'
209 AND pivf.element_type_id = petf.element_type_id
210 AND peef.element_type_id = petf.element_type_id
211 AND peef.assignment_id = paaf.assignment_id
212 AND peef.element_entry_id = peevf.element_entry_id
213 AND pivf.input_value_id = peevf.input_value_id
214 AND L_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
215 AND L_PRP_END_DATE BETWEEN petf.effective_start_date AND petf.effective_end_date
216 AND L_PRP_END_DATE BETWEEN pivf.effective_start_date AND pivf.effective_end_date
217 AND L_PRP_END_DATE BETWEEN peef.effective_start_date AND peef.effective_end_date
218 AND L_PRP_END_DATE BETWEEN peevf.effective_start_date AND peevf.effective_end_date;
219 --Cursor to check if AutoEnrollment has been done already or not
220 CURSOR C_AED_CHECK(V_ASSIGNMENT_ID NUMBER)
221 IS
222 SELECT MAX(DECODE(pivf.name,'Auto Enrollment Date',peevf.screen_entry_value)) AED,
223 MAX(DECODE(pivf.name,'Qualifying scheme exists',peevf.screen_entry_value)) QSE,
224 MAX(DECODE(pivf.name,'Opt Out Date',peevf.screen_entry_value)) OPT_OUT,
225 MAX(DECODE(pivf.name,'Postponement End Date',peevf.screen_entry_value)) POSTPONEMENT,
226 MAX(DECODE(pivf.name,'Eligible Job Holder Date',peevf.screen_entry_value)) EJH
227 FROM per_all_assignments_f paaf,
228 pay_element_types_f petf,
229 pay_input_values_f pivf,
230 pay_element_entries_f peef,
231 pay_element_entry_values_f peevf
232 WHERE paaf.assignment_id = V_ASSIGNMENT_ID
233 AND petf.element_name ='Pensions Information'
234 AND petf.legislation_code = 'GB'
235 AND pivf.name IN ( 'Auto Enrollment Date', 'Opt Out Date','Postponement End Date','Qualifying scheme exists','Eligible Job Holder Date')
236 AND pivf.legislation_code = 'GB'
237 AND pivf.element_type_id = petf.element_type_id
238 AND peef.element_type_id = petf.element_type_id
239 AND peef.assignment_id = paaf.assignment_id
240 AND peef.element_entry_id = peevf.element_entry_id
241 AND pivf.input_value_id = peevf.input_value_id
242 AND L_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
243 AND L_PRP_END_DATE BETWEEN petf.effective_start_date AND petf.effective_end_date
244 AND L_PRP_END_DATE BETWEEN pivf.effective_start_date AND pivf.effective_end_date
245 AND L_PRP_END_DATE BETWEEN peef.effective_start_date AND peef.effective_end_date
246 AND L_PRP_END_DATE BETWEEN peevf.effective_start_date AND peevf.effective_end_date;
247 BEGIN
248 -- Defined Balance Id
249 OPEN C_DEF_BAL;
250 FETCH C_DEF_BAL INTO L_DEFINED_BALANCE_ID;
251 CLOSE C_DEF_BAL;
252 hr_utility.trace('Balance defined id is:'||L_DEFINED_BALANCE_ID);
253 -- hr_utility.trace('Main Entry is:'||P_MAIN_ENTRY);
254 --Payroll Id
255 OPEN C_PAYROLL_ID;
256 FETCH C_PAYROLL_ID INTO L_PAYROLL_ID,L_BUSINESS_GROUP_ID;
257 CLOSE C_PAYROLL_ID;
258 --PRP Dates
259 OPEN C_PRP_DETAILS;
260 FETCH C_PRP_DETAILS INTO L_PRP_START_DATE,L_PRP_END_DATE;
261 CLOSE C_PRP_DETAILS;
262 --Person Id
263 OPEN C_PERSON_ID;
264 FETCH C_PERSON_ID INTO L_PERSON_ID;
265 CLOSE C_PERSON_ID;
266 --Global Details
267 /*
268 OPEN C_GET_GLOBAL_VALUE;
269 FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
270 CLOSE C_GET_GLOBAL_VALUE;
271 */
272 OPEN C_AGE_SEX(L_PERSON_ID);
273 FETCH C_AGE_SEX INTO L_AGE,L_SEX,L_DATE_OF_BIRTH;
274 CLOSE C_AGE_SEX;
275 /* 16409174 - PAE THRESHOLD CHANGES start*/
276 OPEN c_asg_start_date;
277 FETCH c_asg_start_date INTO l_asg_start_date ;
278 CLOSE c_asg_start_date;
279
280 OPEN C_SERVICE_START (L_PERSON_ID) ;
281 FETCH C_SERVICE_START INTO L_SERVICE_START;
282 CLOSE C_SERVICE_START;
283 -- 22nd Birthday
284 L_DOB_TT := FETCH_START_DATE(L_DATE_OF_BIRTH);
285
286 IF ((L_DOB_TT BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE) OR (L_SERVICE_START BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE)) THEN
287 L_EFFECTIVE_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,l_asg_start_date);
288 G_GLOBAL_VALUE := NULL;
289 OPEN C_GET_GLOBAL_VALUE(L_EFFECTIVE_DATE);
290 FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
291 CLOSE C_GET_GLOBAL_VALUE;
292 hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
293 end if;
294 /* 16409174 - PAE THRESHOLD CHANGES end*/
295
296
297 ---Aggregation
298 OPEN C_AGGREGATION_CHECK(P_ASSIGNMENT_ID);
299 FETCH C_AGGREGATION_CHECK INTO L_AGGREGATION,L_MAIN_ENTRY;
300 CLOSE C_AGGREGATION_CHECK;
301 hr_utility.trace('Period Type: '||L_PERIOD_TYPE);
302 IF (NVL(L_AGGREGATION,'N') ='N') THEN
303 OPEN C_PERIOD_TYPE(L_PAYROLL_ID);
304 FETCH C_PERIOD_TYPE INTO L_PERIOD_TYPE;
305 CLOSE C_PERIOD_TYPE;
306 ELSIF NVL(L_MAIN_ENTRY,'N') = 'Y' AND NVL(L_AGGREGATION,'N') ='Y' THEN
307 OPEN C_PERIOD_TYPE(L_PAYROLL_ID);
308 FETCH C_PERIOD_TYPE INTO L_PERIOD_TYPE;
309 CLOSE C_PERIOD_TYPE;
310 END IF;
311 hr_utility.trace('L_PRP_END_DATE: '||L_PRP_END_DATE);
312 hr_utility.trace('L_PAYROLL_ID: '||L_PAYROLL_ID);
313 hr_utility.trace('Period Type: '||L_PERIOD_TYPE);
314 IF NVL(L_AGGREGATION,'N') ='N' THEN
315 FOR L_ASSIGNMENT_ACTIONS IN C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID)
316 LOOP
317 L_BALANCE_VALUE := L_BALANCE_VALUE+PAY_BALANCE_PKG.GET_VALUE ( P_DEFINED_BALANCE_ID =>L_DEFINED_BALANCE_ID ,P_ASSIGNMENT_ACTION_ID=>L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID ,P_GET_RR_ROUTE => FALSE ,
318 P_GET_RB_ROUTE => FALSE) ;
319 END LOOP;
320 END IF;
321 IF NVL(L_AGGREGATION,'N') ='Y' THEN
322 FOR ALL_ASSGN IN C_ASSIGNMENTS_AGG(L_PERSON_ID)
323 LOOP
324 FOR V_ASSIGNMENT_ACTIONS IN C_ASSIGNMENT_ACTION(ALL_ASSGN.ASSIGNMENT_ID)
325 LOOP
326 L_BALANCE_VALUE := L_BALANCE_VALUE+PAY_BALANCE_PKG.GET_VALUE ( P_DEFINED_BALANCE_ID =>L_DEFINED_BALANCE_ID ,P_ASSIGNMENT_ACTION_ID=>V_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID ,P_GET_RR_ROUTE => FALSE ,
327 P_GET_RB_ROUTE => FALSE) ;
328 IF L_PERIOD_TYPE IS NULL THEN
329 OPEN C_AGGREGATION_CHECK(ALL_ASSGN.ASSIGNMENT_ID);
330 FETCH C_AGGREGATION_CHECK INTO L_AGGREGATION,L_MAIN_ENTRY;
331 CLOSE C_AGGREGATION_CHECK;
332 IF NVL(L_MAIN_ENTRY,'N') = 'Y' THEN
333 OPEN C_PERIOD_TYPE(ALL_ASSGN.PAYROLL_ID);
334 FETCH C_PERIOD_TYPE INTO L_PERIOD_TYPE;
335 CLOSE C_PERIOD_TYPE;
336 END IF;
337 END IF;
338 END LOOP;
339 END LOOP;
340 END IF;
341 IF L_PERIOD_TYPE IS NULL THEN
342 hr_utility.trace('No Main Entry Found for Aggregation as on '||L_PRP_END_DATE||'. Please Check');
343 END IF;
344 L_TOTAL_QUAL_EARNINGS := L_BALANCE_VALUE +NVL(P_PQE_ASG_RUN,0);
345 ------------------Earnings Calculated ----------------------------
346 IF L_AGE >= 59 THEN
347 --Pension age calculation needs to be done only if age >= 59
348 IF L_SEX = 'M' THEN
349 N_STATE_PENSION_AGE := 65;
350 ELSIF L_SEX = 'F' THEN
351 IF L_DATE_OF_BIRTH >= TO_DATE('1955/04/06','YYYY/MM/DD') THEN
352 /* The retirement age for Employees born after 6th April 1955 is 65 */
353 N_STATE_PENSION_AGE := 65;
354 ELSIF L_DATE_OF_BIRTH >= TO_DATE('1950/04/06','YYYY/MM/DD') THEN
355 /* For employee born between 6 April 1950 and 6th April 1955, the retirement date will be derived from the user table */
356 /* Convert the Birth date of the employee to YYYYMMDD format. */
357 L_NUM_DATE_BIRTH := TO_CHAR(L_DATE_OF_BIRTH,'YYYYMMDD');
358 /* Pass the birth date and get the retire date corresponding to birth date. */
359 RETIRE_DATE := TO_DATE(HRUSERDT.GET_TABLE_VALUE(L_BUSINESS_GROUP_ID,'STATE_RETIREMENT_AGE_FOR_WOMEN','STATE_PENSION_AGE',L_NUM_DATE_BIRTH,L_PRP_END_DATE),'YYYY/MM/DD');
360 N_STATE_PENSION_AGE := TRUNC((RETIRE_DATE - L_DATE_OF_BIRTH)/365);
361 END IF; --l_date_of_birth
362 END IF; -- l_sex
363 ELSE
364 N_STATE_PENSION_AGE := 65;
365 END IF; --Age >= 59
366 hr_utility.trace('N_STATE_PENSION_AGE: '||N_STATE_PENSION_AGE);
367 ---------------------------State Pension Age calculated--------------------------
368 IF L_PERIOD_TYPE = 'Week' THEN
369 L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_WEEKLY;
370 L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_WEEKLY_LT;
371 ELSIF L_PERIOD_TYPE = 'Calendar Month' THEN
372 L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY ;
373 L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT ;
374 ELSIF L_PERIOD_TYPE = 'Year' THEN
375 L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_ANNUAL;
376 L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_ANNUAL_LT;
377 ELSIF L_PERIOD_TYPE = 'Quarter' THEN
378 L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_QUARTERLY ;
379 L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_QUARTERLY_LT;
380 ELSIF L_PERIOD_TYPE = 'Lunar Month' THEN
381 L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_LUNAR ;
382 L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_LUNAR_LT;
383 ELSIF L_PERIOD_TYPE = 'Bi-Week' THEN
384 L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_BIWEEK;
385 L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_BIWEEK_LT;
386 ELSIF L_PERIOD_TYPE = 'Semi-Year' THEN
387 L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_SEMIYEAR;
388 L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_SEMIYEAR_LT ;
389 ELSIF L_PERIOD_TYPE = 'Bi-Month' THEN -- Monthly's Threshold multiplied by 2
390 L_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY * 2;
391 L_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
392 END IF;
393 ---------------------------Threshold values -------------------------
394 IF ((L_AGE BETWEEN 22 AND N_STATE_PENSION_AGE) AND (L_TOTAL_QUAL_EARNINGS >= L_AUTOTHRESHOLD)) THEN
395 L_EMPLOYEE_STATUS := 1; --Eligible Job holder
396 ELSIF ((L_AGE BETWEEN 16 AND 75) AND (L_TOTAL_QUAL_EARNINGS >= L_LOWER_THRESHOLD AND L_TOTAL_QUAL_EARNINGS < L_AUTOTHRESHOLD)) THEN
397 L_EMPLOYEE_STATUS := 2;--Non Eligible Job holder
398 ELSIF ((L_AGE BETWEEN 16 AND 22 OR L_AGE BETWEEN N_STATE_PENSION_AGE AND 75 ) AND (L_TOTAL_QUAL_EARNINGS >= L_AUTOTHRESHOLD)) THEN
399 L_EMPLOYEE_STATUS := 2;--Non Eligible Job holder
400 ELSE
401 L_EMPLOYEE_STATUS := 3;--Worker
402 END IF;
403 ---------------------------Employee Status Calculated------------------
404 OPEN C_AED_CHECK(P_ASSIGNMENT_ID);
405 FETCH C_AED_CHECK
406 INTO L_AUTO_ENROL_DATE,
407 L_QUALIFYING_SCHEME_EXISTS,
408 L_OPTOUT_DATE,
409 L_POSTPONEMENT_DATE ,
410 L_ELIGIBLE_JOB_HOLDER_DATE ;
411 CLOSE C_AED_CHECK;
412 --IF L_AUTO_ENROL_DATE IS NOT NULL THEN
413 --L_AUTO_ENROL_DATE := L_AUTO_ENROL_DATE);
414 --END IF;
415 hr_utility.trace('L_EMPLOYEE_STATUS: '||L_EMPLOYEE_STATUS);
416 hr_utility.trace('L_AUTO_ENROL_DATE: '||L_AUTO_ENROL_DATE);
417 L_QUALIFYING_SCHEME_EXISTS := NVL(L_QUALIFYING_SCHEME_EXISTS,'N');
418 hr_utility.trace('L_QUALIFYING_SCHEME_EXISTS: '||L_QUALIFYING_SCHEME_EXISTS);
419 --Bug 15872415
420 IF (L_ELIGIBLE_JOB_HOLDER_DATE IS NULL) then
421 return 0; -- Do Nothing
422 END IF;
423 --Bug 14793919
424 -- IF (L_QUALIFYING_SCHEME_EXISTS ='Y') THEN
425 IF (L_QUALIFYING_SCHEME_EXISTS ='Y') AND L_AUTO_ENROL_DATE IS NULL THEN -- Qualifying scheme already exists.
426 RETURN 0; -- Do Nothing if Qualifying Scheme Exists Already
427 END IF;
428 --Bug 14793919
429 IF (NVL(fnd_date.canonical_to_date(L_OPTOUT_DATE),G_END_OF_TIME) > L_PRP_END_DATE) THEN
430 IF (NVL(fnd_date.canonical_to_date(L_POSTPONEMENT_DATE),L_PRP_START_DATE) < L_PRP_END_DATE) THEN
431 IF (L_EMPLOYEE_STATUS =1) THEN
432 IF L_AUTO_ENROL_DATE IS NULL THEN
433 L_ERROR :=1; -- Qualified Yet Not Automatically Enrolled - Could be any reason like Postponement or Qualifying scheme already exists.
434 ELSIF L_AUTO_ENROL_DATE IS NOT NULL AND fnd_date.canonical_to_date(L_AUTO_ENROL_DATE) BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE THEN
435 L_ERROR :=3; --Qualified and Automatically Enrolled
436 END IF;
437 END IF;
438 END IF;--Postponement
439 END IF; --Opt Out
440 IF (L_EMPLOYEE_STATUS <>1) THEN
441 IF L_AUTO_ENROL_DATE IS NOT NULL AND fnd_date.canonical_to_date(L_AUTO_ENROL_DATE) BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE THEN
442 L_ERROR :=2; -- Not qualified, but has been automatically enrolled.
443 END IF;
444 END IF;
445 RETURN NVL(L_ERROR,0);
446 -- RETURN L_TOTAL_QUAL_EARNINGS;
447 EXCEPTION
448 WHEN OTHERS THEN
449 hr_utility.trace('Error:'||sqlerrm);
450 -- RETURN NVL(L_ERROR,0);
451 RAISE;
452 END GET_AGGREGATED_EARNINGS;
453 ----------------------------------------------------------------------------
454 --To Get assignment Number
455 FUNCTION get_assg_number(
456 p_assignment_id NUMBER)
457 RETURN VARCHAR2
458 IS
459 l_assignment_number VARCHAR2(240);
460 BEGIN
461 OPEN get_assignment_number (p_assignment_id);
462 FETCH get_assignment_number INTO l_assignment_number;
463 CLOSE get_assignment_number;
464 RETURN l_assignment_number;
465 END;
466 ----------------------------------------------------------------------------
467
468 ---To Fetch MAX Deferment Period Start Date of the employee
469 -- 3 months
470 /*FUNCTION C_PAYROLL_MAX_DATE(
471 P_DATE DATE)
472 RETURN DATE
473 IS
474 L_DATE DATE;
475 CURSOR C_MAX_DATE
476 IS
477 SELECT (PTP.START_DATE-1)
478 FROM PER_TIME_PERIODS PTP
479 WHERE ADD_MONTHS(P_DATE,3) BETWEEN START_DATE AND END_DATE
480 AND PAYROLL_ID=G_PAYROLL_ID;
481 BEGIN
482 OPEN C_MAX_DATE;
483 FETCH C_MAX_DATE INTO L_DATE;
484 CLOSE C_MAX_DATE;
485 RETURN L_DATE;
486 END C_PAYROLL_MAX_DATE;
487 */
488
489 FUNCTION C_PAYROLL_MAX_DATE(
490 P_DATE DATE,PERIOD VARCHAR2 default null)
491 RETURN DATE
492 IS
493 L_DATE DATE;
494 L_DAY NUMBER;
495
496 CURSOR C_MAX_DATE
497 IS
498 SELECT (PTP.START_DATE-1)
499 FROM PER_TIME_PERIODS PTP
500 WHERE ADD_MONTHS(P_DATE,3) BETWEEN START_DATE AND END_DATE
501 AND PAYROLL_ID=G_PAYROLL_ID;
502
503 BEGIN
504 hr_utility.trace('P_DATE: '||P_DATE);
505 hr_utility.trace('PERIOD: '||PERIOD);
506
507 L_DATE := P_DATE;
508 if PERIOD is not null then
509 if PERIOD = 'MAX_DEFER_PERIOD_START_DATE' then
510 OPEN C_MAX_DATE;
511 FETCH C_MAX_DATE INTO L_DATE;
512 CLOSE C_MAX_DATE;
513 elsif PERIOD = 'NEXT_PAYROLL_PERIOD_START_DATE' then
514 L_DATE := G_PRP_END_DATE;
515 elsif PERIOD = 'ONE_MONTH' then
516 L_DATE := ADD_MONTHS(L_DATE,1)-1;
517 elsif PERIOD = 'TWO_MONTH' then
518 L_DATE := ADD_MONTHS(L_DATE,2)-1;
519 else
520 L_DATE := L_DATE + TO_NUMBER(PERIOD) -1;
521 end if;
522 end if;
523 hr_utility.trace('L_DATE: '||L_DATE);
524 RETURN L_DATE;
525 END C_PAYROLL_MAX_DATE;
526
527 PROCEDURE update_ni_category_pension(
528 p_assignment_id IN NUMBER,
529 p_auto_enrol_date IN DATE,
530 p_mode in VARCHAR2 --GB_VALIDATE_COMMIT
531 )
532 IS
533 /*** Local variables ***/
534 l_ni_new_category varchar2(1) := ' ';
535 l_ni_new_pension varchar2(30) := ' ';
536 l_ni_old_category varchar2(1) := ' ';
537 l_ni_old_pension varchar2(30) := ' ';
538 l_input_value_id_category number := -1;
539 l_input_value_id_pension number := -1;
540 l_element_entry_id number := -1;
541 l_input_value_name varchar2(50) := ' ';
542 l_update_flag varchar2(1) := 'N';
543
544 TYPE pen_basis_type IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(1);
545 pen_basis pen_basis_type;
546 -- Cursor retrieves all the element entries that are to be updated.
547
548 cursor csr_element_entries_for_update
549 is
550 SELECT peevf.*
551 FROM pay_element_types_f petf
552 ,pay_input_values_f pivf
553 ,pay_element_entries_f peef
554 ,pay_element_entry_values_f peevf
555 WHERE
556 petf.element_name = 'NI'
557 AND petf.legislation_code = 'GB'
558 AND petf.element_type_id = pivf.element_type_id
559 AND peef.assignment_id = p_assignment_id
560 AND peef.element_type_id = petf.element_type_id
561 AND peef.element_entry_id = peevf.element_entry_id
562 AND peevf.input_value_id = pivf.input_value_id
563 AND pivf.name in ('Category','Pension')
564 AND p_auto_enrol_date BETWEEN peef.effective_start_date
565 AND peef.effective_end_date
566 AND p_auto_enrol_date BETWEEN peevf.effective_start_date
567 AND peevf.effective_end_date
568 AND p_auto_enrol_date BETWEEN petf.effective_start_date AND petf.effective_end_date
569 AND p_auto_enrol_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
570
571 cursor c_get_input_value_name(l_input_value_id NUMBER)
572 is
573 select name
574 from pay_input_values_f
575 where input_value_id = l_input_value_id
576 AND LEGISLATION_CODE = 'GB';
577
578 l_rec csr_element_entries_for_update%rowtype;
579
580 BEGIN
581 hr_utility.trace('Entering ' || '.update_ni_category_pension');
582 hr_utility.trace('Auto Enrollment Date = ' || p_auto_enrol_date);
583 hr_utility.trace('Validate_mode' ||p_mode);
584
585 -- Initialization
586 pen_basis('A') := 'APP not contracted out';
587 pen_basis('C') := 'Contracted out';
588 pen_basis('M') := 'Contracted Out Money Purchase';
589 pen_basis('N') := 'Not contracted out';
590 pen_basis('X') := 'N/A for Category X';
591
592 open csr_element_entries_for_update;
593 loop
594 fetch csr_element_entries_for_update into l_rec;
595 exit when csr_element_entries_for_update%notfound;
596
597 -- get the input_value_name for element entry values.
598 open c_get_input_value_name(l_rec.input_value_id);
599 fetch c_get_input_value_name into l_input_value_name;
600 close c_get_input_value_name;
601
602 l_element_entry_id := l_rec.element_entry_id;
603 if l_input_value_name = 'Category' then
604 l_ni_old_category := l_rec.screen_entry_value ;
605 l_input_value_id_category := l_rec.input_value_id;
606 elsif l_input_value_name = 'Pension' then
607 l_ni_old_pension := l_rec.screen_entry_value ;
608 l_input_value_id_pension := l_rec.input_value_id;
609 end if;
610
611 end loop;
612 close csr_element_entries_for_update;
613
614 --Determine the new pension basis for each category.
615 /* For reference below are the lookups and their meanings.
616 Lookup Code Meaning
617 ----------- -------
618 A APP not contracted out
619 C Contracted out
620 M Contracted Out Money Purchase
621 N Not contracted out
622 X N/A for Category X
623
624 */
625
626 -- determine the new category and new pension basis for this element entry.
627 if l_ni_old_category = 'A' THEN
628 l_ni_new_category := 'D';
629 l_ni_new_pension := 'C';
630 l_update_flag := 'Y';
631 elsif l_ni_old_category = 'B' THEN
632 l_ni_new_category := 'E';
633 l_ni_new_pension := 'C';
634 l_update_flag := 'Y';
635 elsif l_ni_old_category = 'J' THEN
636 l_ni_new_category := 'L';
637 l_ni_new_pension := 'C';
638 l_update_flag := 'Y';
639 end if;
640
641
642 SAVEPOINT update_NI_element;
643 if p_mode = 'GB_VALIDATE_COMMIT' and l_update_flag = 'Y' THEN
644 -- Call the update api in UPDATE_OVERRIDE mode for updating the element entries for future entries as well.
645 hr_entry_api.update_element_entry(p_dt_update_mode => 'UPDATE_OVERRIDE',
646 p_session_date => p_auto_enrol_date,
647 p_element_entry_id => l_element_entry_id,
648 p_input_value_id1 => l_input_value_id_category,
649 p_input_value_id2 => l_input_value_id_pension,
650 p_entry_value1 => l_ni_new_category,
651 p_entry_value2 => pen_basis(l_ni_new_pension)
652 );
653
654
655
656 hr_utility.trace('Assignment_id Element_entry_id Old NI Category New NI Category Old Pension Basis New Pension Basis Eff Date');
657 hr_utility.trace('------------- ---------------- -------------- --------------- ----------------- ----------------- --------');
658
659 hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id||' ' ||l_ni_old_category||' ' ||l_ni_new_category
660 ||' ' ||l_ni_old_pension||' ' ||l_ni_new_pension);
661
662
663 hr_utility.trace(p_assignment_id ||' ' || l_element_entry_id|| ' Updated Successfully');
664
665 hr_utility.trace('Leaving ' || '.update_ni_catergory_and_pension');
666 end if;
667 if not (p_mode = 'GB_VALIDATE_COMMIT') THEN
668 ROLLBACK to update_NI_element;
669 end if;
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 hr_utility.trace('Error While Updating NI category');
674 hr_utility.trace('SQLERRM'||sqlerrm);
675 --RAISE_APPLICATION_ERROR(-20001, SQLCODE||' - '||SQLERRM);
676 RAISE;
677 END update_ni_category_pension;
678
679 PROCEDURE FETCH_DATA
680 IS
681 --
682 P_PAYROLL_ID NUMBER;
683 P_TIME_PERIOD_ID NUMBER;
684 -- Concurrent Program Parameters
685 CURSOR CSR_CP_INFO
686 IS
687 SELECT TO_NUMBER(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS, 'PAYROLL_ID')) PAYROLL_ID,
688 TO_NUMBER(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS, 'PERIOD_ID')) TIME_PERIOD_ID,
689 PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS,'MODE') L_MODE,
690 BUSINESS_GROUP_ID
691 FROM PAY_PAYROLL_ACTIONS
692 WHERE PAYROLL_ACTION_ID = G_PAYROLL_ACTION_ID ;
693 --Tax details Reference for staging date and postponement rule.
694 CURSOR C_TAX_REF_PENSION_DETAILS
695 IS
696 SELECT UPPER(SUBSTR(TRIM(HOI.ORG_INFORMATION2),1,35)) PENSION_STAGING_DATE,
697 NVL(UPPER(SUBSTR(LTRIM(HOI.ORG_INFORMATION3),1,35)),'NONE') WORKER_POSTPONEMENT_RULE,
698 NVL(UPPER(SUBSTR(LTRIM(HOI.ORG_INFORMATION4),1,35)),'NONE') ELIGIBLE_POSPONEMENT_RULE,
699 PPF.PERIOD_TYPE
700 FROM PAY_PAYROLLS_F PPF,
701 HR_SOFT_CODING_KEYFLEX HSCF,
702 HR_ORGANIZATION_INFORMATION HOI
703 WHERE PPF.PAYROLL_ID = P_PAYROLL_ID
704 AND PPF.BUSINESS_GROUP_ID = HOI.ORGANIZATION_ID
705 AND HOI.ORG_INFORMATION_CONTEXT = 'UK Pensions'
706 AND NVL(HOI.ORG_INFORMATION10,'UK') = 'UK'
707 AND PPF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
708 AND HSCF.SEGMENT1 = HOI.ORG_INFORMATION1
709 AND G_PRP_END_DATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE;
710 --To fetch Payroll Period Dates
711 CURSOR C_PAYROLL_DETAILS
712 IS
713 SELECT PAYROLL_ID,
714 START_DATE,
715 END_DATE,
716 REGULAR_PAYMENT_DATE ,
717 PERIOD_NAME
718 FROM PER_TIME_PERIODS PTP
719 WHERE PTP.TIME_PERIOD_ID = P_TIME_PERIOD_ID;
720 --To fetch PRP Period Dates
721 CURSOR C_PRP_DETAILS
722 IS
723 SELECT START_DATE,
724 END_DATE,
725 REGULAR_PAYMENT_DATE
726 FROM PER_TIME_PERIODS PTP
727 WHERE G_PAYROLL_DATE_PAID BETWEEN START_DATE AND END_DATE
728 AND PAYROLL_ID=G_PAYROLL_ID;
729 --To Fetch Payroll Name
730 CURSOR C_PAYROLL_NAME
731 IS
732 SELECT PAYROLL_NAME
733 FROM PAY_PAYROLLS_F
734 WHERE PAYROLL_ID = G_PAYROLL_ID
735 AND G_PRP_END_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
736 --This cursor will check whether the default Pension element entered is a QUALIFYING pension scheme or not.
737 CURSOR C_VALIDATE_PENSION_INFO(P_SCHEME_ELEMENT_NAME VARCHAR2 )
738 IS
739 SELECT 1
740 FROM DUAL
741 WHERE EXISTS
742 (SELECT PCV_INFORMATION1
743 FROM PQP_CONFIGURATION_VALUES
744 WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
745 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
746 AND PCV_INFORMATION2 = P_SCHEME_ELEMENT_NAME
747 );
748 L_TEMP NUMBER;
749 BEGIN
750 ------*********Assign all global values to null before assigning values
751 hr_utility.trace('Inside fetch data');
752 G_MODE := NULL;
753 G_PAYROLL_ID := NULL;
754 G_TIME_PERIOD_ID := NULL;
755 G_PAYROLL_START_DATE := NULL;
756 G_PAYROLL_END_DATE := NULL;
757 G_ELEMENT_DETAILS := NULL;
758 G_PENSION_STAGING_DATE := NULL;
759 G_WORKER_POSTPONEMENT_RULE := NULL;
760 G_ELIGIBLE_POSTPONEMENT_RULE:= NULL;
761 G_PAYROLL_PERIOD_TYPE := NULL;
762 G_GLOBAL_VALUE := NULL;
763 G_BUSINESS_GROUP_ID := NULL;
764 hr_utility.trace('Before Concurrent Program Info');
765 OPEN CSR_CP_INFO;
766 FETCH CSR_CP_INFO
767 INTO G_PAYROLL_ID,
768 G_TIME_PERIOD_ID,
769 G_MODE,
770 G_BUSINESS_GROUP_ID;
771 CLOSE CSR_CP_INFO;
772 --Remove Below Every Where use Global ##Just added for compiling purpose
773 P_PAYROLL_ID := G_PAYROLL_ID;
774 P_TIME_PERIOD_ID :=G_TIME_PERIOD_ID;
775 --Remove
776 hr_utility.trace('Before Defined Balance');
777 -- Defined Balance Id
778 OPEN C_DEF_BAL;
779 FETCH C_DEF_BAL INTO G_DEFINED_BALANCE_ID;
780 CLOSE C_DEF_BAL;
781 hr_utility.trace('Before payroll details');
782 -- Payroll Details
783 OPEN C_PAYROLL_DETAILS;
784 FETCH C_PAYROLL_DETAILS
785 INTO G_PAYROLL_ID,
786 G_PAYROLL_START_DATE,
787 G_PAYROLL_END_DATE,
788 G_PAYROLL_DATE_PAID ,
789 G_PERIOD_NAME ;
790 CLOSE C_PAYROLL_DETAILS;
791 ---PRP Details
792 OPEN C_PRP_DETAILS;
793 FETCH C_PRP_DETAILS INTO G_PRP_START_DATE, G_PRP_END_DATE, G_PRP_DATE_PAID ;
794 CLOSE C_PRP_DETAILS;
795 --PAYROLL NAME
796 OPEN C_PAYROLL_NAME;
797 FETCH C_PAYROLL_NAME INTO G_PAYROLL_NAME;
798 CLOSE C_PAYROLL_NAME;
799 -- EARNING PERIOD Details
800 OPEN C_EARN_PERIOD_DETAILS(G_PAYROLL_ID);
801 FETCH C_EARN_PERIOD_DETAILS
802 INTO G_TIME_PERIOD_ID,
803 G_EARN_START_DATE ,
804 G_EARN_END_DATE ,
805 G_EARN_DATE_PAID ;
806 CLOSE C_EARN_PERIOD_DETAILS;
807 hr_utility.trace('Values fetched from payroll :'||G_PAYROLL_ID||'START'||G_PAYROLL_START_DATE||'END'||G_PAYROLL_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
808 hr_utility.trace('Values fetched for PRP :'||G_PAYROLL_ID||'START'||G_PRP_START_DATE||'END'||G_PRP_END_DATE||' G_DEFINED_BALANCE_ID'||G_DEFINED_BALANCE_ID);
809 hr_utility.trace('Values fetched for Earn :'||G_PAYROLL_ID||'START'||G_EARN_START_DATE||'END'||G_EARN_END_DATE||'PAYMENT DATE'||G_EARN_DATE_PAID);
810 hr_utility.trace('Values fetched from payroll:'||' G_PAYROLL_NAME :'||G_PAYROLL_NAME);
811 hr_utility.trace('Before C_PENSION_ELEMENT_INF details');
812 -- Element Details
813 OPEN C_PENSION_ELEMENT_INF;
814 FETCH C_PENSION_ELEMENT_INF INTO G_ELEMENT_DETAILS;
815 CLOSE C_PENSION_ELEMENT_INF;
816 hr_utility.trace('G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID:'||G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID);
817 hr_utility.trace('Before C_TAX_REF_PENSION_DETAILS details');
818 --TAX Ref Details
819 OPEN C_TAX_REF_PENSION_DETAILS;
820 FETCH C_TAX_REF_PENSION_DETAILS
821 INTO G_PENSION_STAGING_DATE,
822 G_WORKER_POSTPONEMENT_RULE,
823 G_ELIGIBLE_POSTPONEMENT_RULE,
824 G_PAYROLL_PERIOD_TYPE;
825 CLOSE C_TAX_REF_PENSION_DETAILS;
826 hr_utility.trace('Tax details fetched are:'||G_PENSION_STAGING_DATE||'-'||G_PAYROLL_PERIOD_TYPE||'-'||G_WORKER_POSTPONEMENT_RULE||'-'||G_ELIGIBLE_POSTPONEMENT_RULE);
827 hr_utility.trace('Before C_GET_GLOBAL_VALUE details');
828 --Global Details
829 OPEN C_GET_GLOBAL_VALUE(G_PRP_START_DATE);
830 FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
831 CLOSE C_GET_GLOBAL_VALUE;
832 hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
833 hr_utility.trace('Before C_GET_QUALIFYING_PENSION_INFO details');
834 EXCEPTION
835 WHEN OTHERS THEN
836 hr_utility.trace('Fetch data failed:'||SQLERRM);
837 -- END;
838 -- hr_utility.trace('End of fetch data');
839 END FETCH_DATA;
840 ------------------------------------------------------------------------------------------------
841 FUNCTION EXEC_FORMULA(
842 P_ASSIGNMENT_ID NUMBER,
843 P_ELEM_ENTRY_ID NUMBER,
844 P_STAT_PROC_RULE_ID NUMBER,
845 P_ASSACTID NUMBER,
846 P_FORMULA_ID NUMBER,
847 P_RESULT_RULE_NAME VARCHAR2 DEFAULT NULL)
848 RETURN NUMBER
849 IS
850 -- Fetch Original Entry Id for the Element Entry -- Bug 14080693
851 CURSOR C_ORIGINAL_ENTRY_ID
852 IS
853 SELECT ORIGINAL_ENTRY_ID
854 FROM pay_element_entries_f peef
855 WHERE peef.element_entry_id = P_ELEM_ENTRY_ID
856 AND g_payroll_end_date BETWEEN peef.effective_start_date AND peef.effective_end_date;
857 -- Fetch Element Type Id for the Element Entry Id -- Bug 14064422
858 CURSOR C_ELEMENT_TYPE_ID
859 IS
860 SELECT pelf.element_type_id
861 FROM pay_element_entries_f peef ,
862 pay_element_links_f pelf
863 WHERE pelf.element_link_id = peef.element_link_id
864 AND peef.element_entry_id = P_ELEM_ENTRY_ID
865 AND g_payroll_end_date BETWEEN peef.effective_start_date AND peef.effective_end_date
866 AND g_payroll_end_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date;
867 -- Fetch The Latest Action Ids for the Assignment
868 CURSOR C_ACTION_IDS(P_ASSIGNMENT_ID NUMBER )
869 IS
870 SELECT PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID ,
871 PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
872 FROM PAY_PAYROLL_ACTIONS PPA,
873 PAY_ASSIGNMENT_ACTIONS PAA
874 WHERE PPA.ACTION_TYPE IN ('Q','R')
875 AND PAA.ACTION_STATUS = 'C'
876 AND PPA.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
877 AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
878 AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
879 AND EFFECTIVE_DATE = G_PAYROLL_DATE_PAID--Check the date
880 ORDER BY ASSIGNMENT_ACTION_ID DESC;
881 CURSOR ELE_SCREEN_VALUE (P_ELEM_ENTRY_ID NUMBER)
882 IS
883 SELECT PEEF.SCREEN_ENTRY_VALUE,
884 PEEF.INPUT_VALUE_ID,
885 UPPER(REPLACE(PIVF.NAME,' ','_')) NAME
886 FROM PAY_ELEMENT_ENTRY_VALUES_F PEEF,
887 PAY_INPUT_VALUES_F PIVF
888 WHERE PEEF.ELEMENT_ENTRY_ID = P_ELEM_ENTRY_ID
889 AND PEEF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID
890 AND g_payroll_end_date BETWEEN peef.EFFECTIVE_START_DATE AND peef.EFFECTIVE_END_DATE
891 AND g_payroll_end_date BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE ;
892 CURSOR GET_PAY_VAL_RSLT_NAME(P_STAT_PROC_RULE_ID NUMBER, P_PAY_VALUE_ID NUMBER)
893 IS
894 SELECT RESULT_NAME
895 FROM PAY_FORMULA_RESULT_RULES_F FRR
896 WHERE STATUS_PROCESSING_RULE_ID = P_STAT_PROC_RULE_ID
897 AND RESULT_RULE_TYPE ='D'
898 AND INPUT_VALUE_ID = P_PAY_VALUE_ID
899 AND g_payroll_end_date BETWEEN FRR.EFFECTIVE_START_DATE AND FRR.EFFECTIVE_END_DATE ;
900 C_ACTION_IDS_ROW C_ACTION_IDS%ROWTYPE;
901 L_INPUTS FF_EXEC.INPUTS_T;
902 P_INPUTS FF_EXEC.INPUTS_T;
903 L_OUTPUTS FF_EXEC.OUTPUTS_T;
904 l_result_rule_name PAY_FORMULA_RESULT_RULES_F.RESULT_NAME%TYPE;
905 Iteration NUMBER;
906 l_pay_value_ivid NUMBER;
907 L_EARNINGS_VALUE NUMBER := 0;
908 l_element_type_id NUMBER ; -- Bug 14064422
909 l_original_entry_id NUMBER ; -- Bug 14080693
910 BEGIN
911 -- Bug 14064422
912 OPEN C_ELEMENT_TYPE_ID;
913 FETCH C_ELEMENT_TYPE_ID INTO l_element_type_id;
914 HR_UTILITY.trace('In Exec Formula - Element Type Id :'||l_element_type_id);
915 CLOSE C_ELEMENT_TYPE_ID;
916 -- Bug 14064422
917 HR_UTILITY.trace('In Exec Formula -Element Entry Id :'||P_ELEM_ENTRY_ID);
918 -- Bug 14080693
919 OPEN C_ORIGINAL_ENTRY_ID;
920 FETCH C_ORIGINAL_ENTRY_ID INTO l_original_entry_id;
921 HR_UTILITY.trace('In Exec Formula -Original Entry Id :'||l_original_entry_id);
922 CLOSE C_ORIGINAL_ENTRY_ID;
923 -- Bug 14080693
924 l_result_rule_name:=P_RESULT_RULE_NAME;
925 HR_UTILITY.trace(' Inside run Element FORMULA '||P_FORMULA_ID);
926 L_INPUTS.delete;
927 P_INPUTS.delete;
928 L_OUTPUTS.delete;
929 -- setting the contexts
930 P_INPUTS(1).name := 'ASSIGNMENT_ID';
931 P_INPUTS(1).value := P_ASSIGNMENT_ID;
932 P_INPUTS(2).name := 'DATE_EARNED';
933 P_INPUTS(2).value := FND_DATE.DATE_TO_CANONICAL(G_PAYROLL_END_DATE);
934 P_INPUTS(3).name := 'BUSINESS_GROUP_ID';
935 P_INPUTS(3).value := G_BUSINESS_GROUP_ID;
936 P_INPUTS(4).name := 'PAYROLL_ID';
937 P_INPUTS(4).value := G_PAYROLL_ID;
938 -- Bug 14064422
939 P_INPUTS(5).name := 'ELEMENT_TYPE_ID';
940 P_INPUTS(5).value := l_element_type_id;
941 -- Bug 14080693
942 P_INPUTS(6).name := 'ELEMENT_ENTRY_ID';
943 P_INPUTS(6).value := P_ELEM_ENTRY_ID;
944 P_INPUTS(7).name := 'ORIGINAL_ENTRY_ID';
945 P_INPUTS(7).value := NVL(l_original_entry_id, P_ELEM_ENTRY_ID);
946 -- Bug 14080693
947 --Bug 14615369
948 P_INPUTS(8).name := 'BALANCE_DATE';
949 P_INPUTS(8).value := FND_DATE.DATE_TO_CANONICAL(G_PAYROLL_END_DATE); -- Date Earned
950
951 --Bug 14615369
952 OPEN C_ACTION_IDS(P_ASSIGNMENT_ID);
953 FETCH C_ACTION_IDS INTO C_ACTION_IDS_ROW;
954 IF C_ACTION_IDS_ROW.ASSIGNMENT_ACTION_ID IS NOT NULL THEN
955 P_INPUTS(9).name := 'PAYROLL_ACTION_ID';
956 P_INPUTS(9).value := C_ACTION_IDS_ROW.PAYROLL_ACTION_ID;
957 P_INPUTS(10).name := 'ASSIGNMENT_ACTION_ID';
958 P_INPUTS(10).value := C_ACTION_IDS_ROW.ASSIGNMENT_ACTION_ID;
959 END IF;
960 IF C_ACTION_IDS%NOTFOUND THEN
961 HR_UTILITY.trace('No Action Ids Found for the Assignment');
962 P_INPUTS(9).name := 'PAYROLL_ACTION_ID';
963 P_INPUTS(9).value := G_PAYROLL_ACTION_ID;
964 P_INPUTS(10).name := 'ASSIGNMENT_ACTION_ID';
965 P_INPUTS(10).value := P_ASSACTID;
966 END IF;
967 CLOSE C_ACTION_IDS;
968 Iteration := 11;--Bug 14615369
969 -- Bug 14064422
970 /*
971 OPEN C_ACTION_IDS(P_ASSIGNMENT_ID);
972 FETCH C_ACTION_IDS INTO C_ACTION_IDS_ROW;
973 IF C_ACTION_IDS_ROW.ASSIGNMENT_ACTION_ID IS NOT NULL THEN
974 P_INPUTS(5).name := 'PAYROLL_ACTION_ID';
975 P_INPUTS(5).value := C_ACTION_IDS_ROW.PAYROLL_ACTION_ID;
976 P_INPUTS(6).name := 'ASSIGNMENT_ACTION_ID';
977 P_INPUTS(6).value := C_ACTION_IDS_ROW.ASSIGNMENT_ACTION_ID;
978 END IF;
979 IF C_ACTION_IDS%NOTFOUND THEN
980 HR_UTILITY.trace('No Action Ids Found for the Assignment');
981 P_INPUTS(5).name := 'PAYROLL_ACTION_ID';
982 P_INPUTS(5).value := G_PAYROLL_ACTION_ID;
983 P_INPUTS(6).name := 'ASSIGNMENT_ACTION_ID';
984 P_INPUTS(6).value := P_ASSACTID;
985 END IF;
986 CLOSE C_ACTION_IDS;
987 Iteration := 7;
988 */
989 FOR iter IN ELE_SCREEN_VALUE (P_ELEM_ENTRY_ID)
990 LOOP
991 HR_UTILITY.trace('Iteration value'||Iteration);
992 P_INPUTS(Iteration).name := iter.name;
993 P_INPUTS(Iteration).value := iter.screen_entry_value;
994 IF (iter.name = 'PAY_VALUE') THEN
995 l_pay_value_ivid := iter.input_value_id;
996 END IF;
997 Iteration := Iteration+1;
998 END LOOP;
999 IF l_result_rule_name IS NULL THEN
1000 OPEN get_pay_val_rslt_name(P_STAT_PROC_RULE_ID, l_pay_value_ivid);
1001 FETCH get_pay_val_rslt_name INTO l_result_rule_name;
1002 IF get_pay_val_rslt_name%notfound THEN
1003 HR_UTILITY.trace('Result Rule Name not Found');
1004 END IF;
1005 CLOSE get_pay_val_rslt_name;
1006 END IF;
1007 HR_UTILITY.trace(' PAYROLL_ACTION_ID '|| P_INPUTS(5).value);
1008 HR_UTILITY.trace(' ASSIGNMENT_ACTION_ID '||P_INPUTS(6).value );
1009 HR_UTILITY.trace(' p_assignment_id '||P_ASSIGNMENT_ID);
1010 HR_UTILITY.trace(' Date earned '|| FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE));
1011 HR_UTILITY.trace(' p_payroll_id '||G_PAYROLL_ID);
1012 FF_EXEC.INIT_FORMULA(P_FORMULA_ID, G_PRP_END_DATE , L_INPUTS, L_OUTPUTS);
1013 HR_UTILITY.trace('L_INPUTS.COUNT Count:'||L_INPUTS.COUNT);
1014 HR_UTILITY.trace('P_INPUTS.COUNT Count:'||P_INPUTS.COUNT);
1015 IF L_INPUTS.COUNT > 0 AND P_INPUTS.COUNT > 0 THEN
1016 FOR I IN L_INPUTS.first..L_INPUTS.last
1017 LOOP
1018 FOR J IN P_INPUTS.first..P_INPUTS.last
1019 LOOP
1020 IF L_INPUTS(I).name = P_INPUTS(J).name THEN
1021 HR_UTILITY.trace('Inputs Name:'||L_INPUTS(I).name);
1022 HR_UTILITY.trace('Inputs value:'||L_INPUTS(I).value);
1023 HR_UTILITY.trace('---------------------------');
1024 L_INPUTS(I).value := P_INPUTS(J).value;
1025 EXIT;
1026 END IF;
1027 END LOOP;
1028 END LOOP;
1029 END IF;
1030 HR_UTILITY.trace('Inputs Count:'||L_INPUTS.count);
1031 /*
1032 FOR I IN L_INPUTS.first..L_INPUTS.last
1033 LOOP
1034 HR_UTILITY.trace(' i= '||I||' name '||L_INPUTS(I).name ||' value '||L_INPUTS(I).value);
1035 END LOOP;
1036 */
1037 --executing the formula
1038 HR_UTILITY.trace('Before Executing Formula');
1039 FF_EXEC.RUN_FORMULA(L_INPUTS,L_OUTPUTS);
1040 HR_UTILITY.trace('After Executing Formula');
1041 --UTILITY.trace(' calculated value from Element formula '||L_OUTPUTS(1).value);
1042 -- L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_OUTPUTS(1).value,0);
1043 FOR iter IN L_OUTPUTS.first..L_OUTPUTS.last
1044 LOOP
1045 IF (L_OUTPUTS(iter).name = l_result_rule_name) THEN
1046 L_EARNINGS_VALUE := NVL(L_OUTPUTS(iter).value,0);
1047 EXIT;
1048 END IF;
1049 END LOOP;
1050 RETURN L_EARNINGS_VALUE;
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 HR_UTILITY.trace('EXEC_FORMULA Exception:'||sqlerrm);
1054 L_EARNINGS_VALUE := 0;
1055 RAISE;
1056 END EXEC_FORMULA;
1057 --------------------------------------------------------------------------------------------------
1058 FUNCTION CALCULATE_EARNINGS_INFO(
1059 P_ASSIGNMENT_ID NUMBER,
1060 P_EFFECTIVE_DATE DATE,
1061 P_assactid NUMBER,
1062 p_element_type_id_miss NUMBER )
1063 RETURN NUMBER
1064 IS
1065 ---Information element's Calculation
1066 CURSOR C_ELEMENT_DETAILS_INFO(P_EFFECTIVE_DATE DATE , P_ASSIGNMENT_ID NUMBER)
1067 IS
1068 SELECT PEEF.ELEMENT_ENTRY_ID,
1069 PEEF.ELEMENT_TYPE_ID
1070 FROM PAY_ELEMENT_ENTRIES_F PEEF
1071 WHERE PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
1072 AND PEEF.ELEMENT_TYPE_ID IN
1073 ( SELECT DISTINCT ELEMENT_TYPE_ID
1074 FROM pay_element_types_f petf ,
1075 PAY_ELEMENT_CLASSIFICATIONS PEC
1076 WHERE Petf.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
1077 AND PEC.CLASSIFICATION_NAME = 'Information'
1078 AND PETF.ELEMENT_NAME NOT IN ('NI','PAYE')
1079 AND P_EFFECTIVE_DATE BETWEEN petf.EFFECTIVE_START_DATE AND petf.EFFECTIVE_END_DATE
1080 )
1081 AND NVL(p_element_type_id_miss,PEEF.ELEMENT_TYPE_ID) =PEEF.ELEMENT_TYPE_ID
1082 AND P_EFFECTIVE_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE;
1083 --Fetch Result Name from the Formula Results
1084 CURSOR C_RESULT_RULE_NAME (P_STATUS_PROCESSING_RULE_ID NUMBER,P_EFFECTIVE_DATE DATE)
1085 IS
1086 SELECT result_name
1087 FROM pay_formula_result_rules_f frr,
1088 pay_input_values_f pivf
1089 WHERE RESULT_RULE_TYPE = 'I'
1090 AND STATUS_PROCESSING_RULE_ID= P_STATUS_PROCESSING_RULE_ID
1091 AND P_EFFECTIVE_DATE BETWEEN frr.EFFECTIVE_START_DATE AND frr.EFFECTIVE_END_DATE
1092 AND P_EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
1093 AND frr.ELEMENT_TYPE_ID IN
1094 ( SELECT DISTINCT ELEMENT_TYPE_ID
1095 FROM pay_sub_classification_rules_f PSCR ,
1096 PAY_ELEMENT_CLASSIFICATIONS PEC
1097 WHERE PSCR.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
1098 AND PEC.CLASSIFICATION_NAME = 'Pension Qualifying Earnings'
1099 AND P_EFFECTIVE_DATE BETWEEN PSCR.EFFECTIVE_START_DATE AND PSCR.EFFECTIVE_END_DATE
1100 )
1101 AND frr.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
1102 AND pivf.name ='Pay Value';
1103 L_FORMULA_ID NUMBER;
1104 L_STATUS_PROCESSING_RULE_ID NUMBER;
1105 L_RESULT_NAME pay_formula_result_rules_f.result_name%type :=NULL;
1106 L_EARNINGS NUMBER :=0;
1107 BEGIN
1108 HR_UTILITY.trace(' Information CALCULATE_EARNINGS_INFO');
1109 HR_UTILITY.trace(' P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1110 FOR I IN C_ELEMENT_DETAILS_INFO(P_EFFECTIVE_DATE,P_ASSIGNMENT_ID)
1111 LOOP
1112 OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,P_EFFECTIVE_DATE);
1113 FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCESSING_RULE_ID;
1114 HR_UTILITY.trace('I.ELEMENT_TYPE_ID:: '||I.ELEMENT_TYPE_ID);
1115 HR_UTILITY.trace('L_STATUS_PROCESSING_RULE_ID:: '||L_STATUS_PROCESSING_RULE_ID);
1116 CLOSE C_PENSIONS_FORMULA_ID;
1117 IF L_STATUS_PROCESSING_RULE_ID IS NOT NULL THEN
1118 HR_UTILITY.trace('Before Indirect result rules');
1119 FOR rec_result IN C_RESULT_RULE_NAME (L_STATUS_PROCESSING_RULE_ID,P_EFFECTIVE_DATE)
1120 LOOP
1121 HR_UTILITY.trace('Result Rule :'||rec_result.result_name);
1122 L_RESULT_NAME := rec_result.result_name;
1123 IF L_RESULT_NAME IS NOT NULL THEN
1124 HR_UTILITY.trace('L_EARNINGS:'||L_EARNINGS);
1125 L_EARNINGS:=L_EARNINGS+EXEC_FORMULA(P_ASSIGNMENT_ID,I.ELEMENT_ENTRY_ID,L_STATUS_PROCESSING_RULE_ID,P_ASSACTID,L_FORMULA_ID,L_RESULT_NAME);
1126 END IF;
1127 L_RESULT_NAME := NULL; -- Bug 14255540
1128 END LOOP; -- Indirect results loop
1129 END IF;
1130 --Bug 14662582
1131 L_STATUS_PROCESSING_RULE_ID := NULL;
1132 L_FORMULA_ID :=NULL;
1133 END LOOP; -- Information elements loop
1134 RETURN L_EARNINGS;
1135 END CALCULATE_EARNINGS_INFO;
1136 --------------------------------------------------------------------------------------------------
1137 --------------------------------------------------------------------------------------------------
1138 FUNCTION CALCULATE_ASG_ACTIONS(
1139 P_ASSIGNMENT_ID NUMBER,
1140 P_ASSACTID NUMBER,
1141 L_BALANCE_FLAG IN OUT NOCOPY VARCHAR2,
1142 P_TIME_PERIOD NUMBER)
1143 RETURN NUMBER
1144 IS
1145 --Changed for Pensions 2 - to fetch all the assignment actions for the assignment within the PRP
1146 CURSOR C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID NUMBER)
1147 IS
1148 SELECT ASSIGNMENT_ACTION_ID,
1149 PPA.EFFECTIVE_DATE DATE_PAID,
1150 ptp.end_date DATE_EARNED,
1151 ptp.TIME_PERIOD_ID,
1152 ptp.PAYROLL_ID,
1153 PRT.RUN_TYPE_NAME
1154 FROM PAY_PAYROLL_ACTIONS PPA,
1155 PAY_ASSIGNMENT_ACTIONS PAA,
1156 PAY_RUN_TYPES_F PRT,
1157 PER_TIME_PERIODS PTP
1158 WHERE PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
1159 AND REGULAR_PAYMENT_DATE = PPA.EFFECTIVE_DATE
1160 AND ptp.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
1161 AND PPA.TIME_PERIOD_ID = NVL(P_TIME_PERIOD,PPA.TIME_PERIOD_ID)
1162 AND ASSIGNMENT_ID =P_ASSIGNMENT_ID
1163 AND PPA.ACTION_TYPE IN ('Q','R')
1164 AND PPA.EFFECTIVE_DATE BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE
1165 AND PPA.BUSINESS_GROUP_ID =G_BUSINESS_GROUP_ID
1166 AND PAA.ACTION_STATUS ='C'
1167 AND SOURCE_ACTION_ID IS NOT NULL
1168 AND PRT.LEGISLATION_CODE ='GB'
1169 AND PRT.RUN_TYPE_ID =PAA.RUN_TYPE_ID
1170 ORDER BY TIME_PERIOD_ID,
1171 ASSIGNMENT_ACTION_ID DESC;
1172 --Created for Pensions 2 - to fetch elements which have been missed out in the payroll run for that assignment_action_id
1173 CURSOR C_MISSED_ELEMENTS(P_TIME_PERIOD_ID NUMBER,P_DATE_EARNED DATE,P_ASSIGNMENT_ID NUMBER)
1174 IS
1175 SELECT DISTINCT petf.element_type_id
1176 FROM pay_element_types_f petf ,
1177 pay_element_entries_f peef ,
1178 ff_formulas_f fff,
1179 ff_formula_types fft
1180 WHERE petf.element_type_id = peef.element_type_id
1181 AND peef.element_type_id NOT IN
1182 (SELECT DISTINCT prr.ELEMENT_TYPE_ID
1183 FROM pay_run_results prr,
1184 pay_assignment_actions paa,
1185 pay_payroll_actions ppa
1186 WHERE paa.assignment_action_id = prr.assignment_action_id
1187 AND paa.payroll_action_id = ppa.payroll_action_id
1188 AND ppa.time_period_id = P_TIME_PERIOD_ID
1189 AND SOURCE_ACTION_ID IS NOT NULL
1190 AND paa.ASSIGNMENT_ID =P_ASSIGNMENT_ID
1191 AND PPA.BUSINESS_GROUP_ID =G_BUSINESS_GROUP_ID
1192 AND PPA.ACTION_TYPE IN ('Q','R')
1193 AND PAA.ACTION_STATUS ='C'
1194 AND SOURCE_TYPE = 'E'
1195 )
1196 AND NVL(petf.FORMULA_ID,fff.formula_id) = fff.formula_id
1197 AND fff.formula_type_id = fft.formula_type_id
1198 AND FORMULA_TYPE_NAME <>'Element Skip'
1199 AND P_DATE_EARNED BETWEEN fff.effective_start_date AND fff.effective_end_date
1200 AND peef.assignment_id = P_ASSIGNMENT_ID
1201 AND P_DATE_EARNED BETWEEN petf.effective_start_date AND petf.effective_end_date
1202 AND P_DATE_EARNED BETWEEN peef.effective_start_date AND peef.effective_end_date;
1203 L_SCREEN_ENTRY_VALUE PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%type;
1204 L_FORMULA_ID FF_FORMULAS_F.FORMULA_ID%type;
1205 L_STATUS_PROCES_ID NUMBER;
1206 L_TOTAL_EARNINGS_VALUE NUMBER :=0;
1207 L_INPUTS FF_EXEC.INPUTS_T;
1208 P_INPUTS FF_EXEC.INPUTS_T;
1209 L_OUTPUTS FF_EXEC.OUTPUTS_T;
1210 L_ASSIGNMENT_ACTION_ID NUMBER :=NULL;
1211 P_PAY_VALUE_IVID NUMBER;
1212 L_RESULT_RULE_NAME PAY_FORMULA_RESULT_RULES_F.RESULT_NAME%TYPE;
1213 Iteration NUMBER;
1214 L_TIME_PERIOD_ID NUMBER :=0;
1215 BEGIN
1216 L_BALANCE_FLAG :='N';
1217 HR_UTILITY.trace('Inside CALCULATE_ASG_ACTIONS');
1218 FOR L_ASSIGNMENT_ACTIONS IN C_ASSIGNMENT_ACTION(P_ASSIGNMENT_ID)
1219 LOOP
1220 --Fetch Value from Balance
1221 HR_UTILITY.trace('Assignment Action : '||L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID);
1222 L_BALANCE_FLAG :='Y';
1223 L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE + PAY_BALANCE_PKG.GET_VALUE ( P_DEFINED_BALANCE_ID =>G_DEFINED_BALANCE_ID ,P_ASSIGNMENT_ACTION_ID=>L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID ,P_GET_RR_ROUTE => FALSE ,
1224 P_GET_RB_ROUTE => FALSE) ;
1225 HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE Balance Value : '||L_TOTAL_EARNINGS_VALUE);
1226 IF (L_TIME_PERIOD_ID = L_ASSIGNMENT_ACTIONS.TIME_PERIOD_ID) THEN
1227 -- For multiple payroll runs in the same period this will avoid multiple calculations of missed elements
1228 hr_utility.trace('- Second time occuring of the L_TIME_PERIOD_ID - Older assignment action :'||L_TIME_PERIOD_ID);
1229 ELSE --else time period
1230 --Check for element entries that have been missed out in the latest run
1231 FOR missed_ele IN C_MISSED_ELEMENTS(L_ASSIGNMENT_ACTIONS.TIME_PERIOD_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED,P_ASSIGNMENT_ID)
1232 LOOP
1233 -- Call Calculation logic for those missed elements alone
1234 HR_UTILITY.trace('Missed Ele Type Id:'||missed_ele.element_type_id);
1235 FOR I IN C_ELEMENT_DETAILS(L_ASSIGNMENT_ACTIONS.DATE_EARNED,P_ASSIGNMENT_ID,missed_ele.element_type_id)
1236 LOOP
1237 L_SCREEN_ENTRY_VALUE := NULL;
1238 L_FORMULA_ID := NULL;
1239 p_pay_value_ivid := NULL;
1240 L_STATUS_PROCES_ID := NULL;
1241 l_result_rule_name := NULL;
1242 OPEN C_SCREEN_ENTRY_VALUE(I.ELEMENT_ENTRY_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED);
1243 FETCH C_SCREEN_ENTRY_VALUE INTO L_SCREEN_ENTRY_VALUE;
1244 CLOSE C_SCREEN_ENTRY_VALUE;
1245 IF L_SCREEN_ENTRY_VALUE IS NULL THEN
1246 OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED);
1247 FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
1248 CLOSE C_PENSIONS_FORMULA_ID;
1249 IF L_FORMULA_ID IS NOT NULL THEN
1250 HR_UTILITY.trace('Formula Id Not Null');
1251 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1252 HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1253 END IF;
1254 ELSE --Screen Entry Value not Null
1255 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1256 HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1257 END IF;
1258 END LOOP;--calculation logic for missed ele
1259 L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_ASSIGNMENT_ACTIONS.DATE_EARNED,L_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,missed_ele.element_type_id);
1260 HR_UTILITY.trace('Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1261 END LOOP;--missed ele
1262 END IF; --else time period
1263 L_TIME_PERIOD_ID := L_ASSIGNMENT_ACTIONS.TIME_PERIOD_ID;
1264 END LOOP;
1265 RETURN L_TOTAL_EARNINGS_VALUE;
1266 END CALCULATE_ASG_ACTIONS;
1267 --------------------------------------------------------------------------------------------------
1268 FUNCTION CALCULATE_EARNINGS(
1269 P_ASSIGNMENT_ID NUMBER,
1270 P_AGGR_FLAG VARCHAR2 DEFAULT 'N',
1271 P_PERSON_ID NUMBER,
1272 P_EFFECTIVE_DATE DATE,
1273 P_ASSACTID NUMBER)
1274 --P_ELEMENT_ENTRY_ID NUMBER)
1275 RETURN NUMBER
1276 IS
1277 --To fetch the dates paid which do not have a payroll/assignment action for the assignment id
1278 CURSOR C_DATES_PAID_MISSED
1279 IS
1280 SELECT TIME_PERIOD_ID,
1281 START_DATE,
1282 END_DATE,
1283 PAYROLL_ID,
1284 REGULAR_PAYMENT_DATE DATE_PAID
1285 FROM per_time_periods
1286 WHERE payroll_id IN
1287 (SELECT payroll_id
1288 FROM per_all_assignments_f paaf
1289 WHERE assignment_id = p_assignment_id
1290 AND G_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1291 )
1292 AND regular_payment_date BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE
1293 AND regular_payment_date NOT IN
1294 (SELECT effective_date
1295 FROM pay_payroll_actions ppa ,
1296 pay_assignment_actions paa ,
1297 per_time_periods ptp
1298 WHERE ppa.action_type IN ('Q', 'R')
1299 AND ppa.payroll_action_id = paa.payroll_action_id
1300 AND paa.assignment_id = p_assignment_id
1301 AND ppa.time_period_id = ptp.time_period_id
1302 AND ppa.time_period_id IN
1303 (SELECT time_period_id
1304 FROM per_time_periods
1305 WHERE payroll_id IN
1306 (SELECT payroll_id
1307 FROM per_all_assignments_f paaf
1308 WHERE assignment_id = p_assignment_id
1309 AND G_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1310 )
1311 AND regular_payment_date BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE
1312 )
1313 );
1314 ------------------AGGREGATED
1315 CURSOR C_ASSIGNMENTS_AGG
1316 IS
1317 SELECT PAAF.PAYROLL_ID,
1318 PAAF.ASSIGNMENT_ID,
1319 PEEF.ELEMENT_ENTRY_ID
1320 FROM PER_ALL_PEOPLE_F PAPF,
1321 PER_ALL_ASSIGNMENTS_F PAAF,
1322 PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
1323 PAY_ELEMENT_ENTRIES_F PEEF,
1324 PER_ASSIGNMENT_STATUS_TYPES PAST
1325 WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
1326 AND PAPF.PERSON_ID =P_PERSON_ID
1327 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
1328 -- AND PAST.PAY_SYSTEM_STATUS='P'
1329 AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1330 AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
1331 AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
1332 AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
1333 AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID
1334 AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
1335 AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
1336 AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
1337 AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
1338 AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
1339 -----------------------------
1340 L_SCREEN_ENTRY_VALUE PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%type;
1341 L_FORMULA_ID FF_FORMULAS_F.FORMULA_ID%type;
1342 L_STATUS_PROCES_ID NUMBER;
1343 L_TOTAL_EARNINGS_VALUE NUMBER :=0;
1344 L_INPUTS FF_EXEC.INPUTS_T;
1345 P_INPUTS FF_EXEC.INPUTS_T;
1346 L_OUTPUTS FF_EXEC.OUTPUTS_T;
1347 L_ASSIGNMENT_ACTION_ID NUMBER :=NULL;
1348 L_BALANCE_FLAG VARCHAR(2) := 'N';
1349 P_PAY_VALUE_IVID NUMBER;
1350 L_RESULT_RULE_NAME PAY_FORMULA_RESULT_RULES_F.RESULT_NAME%TYPE;
1351 Iteration NUMBER;
1352 L_TIME_PERIOD_ID NUMBER :=0;
1353 BEGIN
1354 hr_utility.trace('Inside CALCULATE_EARNINGS');
1355 hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
1356 hr_utility.trace('Person_id '||P_PERSON_ID);
1357 hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
1358 hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
1359 ---#######################################################################################################
1360 IF (P_AGGR_FLAG='N') THEN
1361 --Fetch Value from Balance
1362 L_TOTAL_EARNINGS_VALUE :=CALCULATE_ASG_ACTIONS(P_ASSIGNMENT_ID,P_ASSACTID,L_BALANCE_FLAG,NULL);
1363 HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
1364 hr_utility.trace('L_BALANCE_FLAG: '||L_BALANCE_FLAG);
1365 IF (L_BALANCE_FLAG ='Y') THEN -- Balance(s) Found --
1366 /*To handle Payroll Change and if the new payroll
1367 has no payroll-assg actions and if the previous payroll has payroll/assg
1368 actions within the PRP*/
1369 hr_utility.trace('To Fetch datepaids which donot have an assignment action');
1370 FOR L_DATE_PAID_MISSED IN C_DATES_PAID_MISSED
1371 LOOP
1372 hr_utility.trace('The Missed Date Paid :'||L_DATE_PAID_MISSED.DATE_PAID);
1373 hr_utility.trace('The Corresponding Date Earned :'||L_DATE_PAID_MISSED.END_DATE);
1374 FOR I IN C_ELEMENT_DETAILS(L_DATE_PAID_MISSED.END_DATE,P_ASSIGNMENT_ID,NULL)
1375 LOOP
1376 L_SCREEN_ENTRY_VALUE := NULL;
1377 L_FORMULA_ID := NULL;
1378 p_pay_value_ivid := NULL;
1379 L_STATUS_PROCES_ID := NULL;
1380 l_result_rule_name := NULL;
1381 OPEN C_SCREEN_ENTRY_VALUE(I.ELEMENT_ENTRY_ID,L_DATE_PAID_MISSED.END_DATE);
1382 FETCH C_SCREEN_ENTRY_VALUE INTO L_SCREEN_ENTRY_VALUE;
1383 CLOSE C_SCREEN_ENTRY_VALUE;
1384 IF L_SCREEN_ENTRY_VALUE IS NULL THEN
1385 OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,L_DATE_PAID_MISSED.END_DATE);
1386 FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
1387 CLOSE C_PENSIONS_FORMULA_ID;
1388 IF L_FORMULA_ID IS NOT NULL THEN
1389 HR_UTILITY.trace('Formula Id Not Null');
1390 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1391 HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1392 END IF;
1393 ELSE --Screen Entry Value not Null
1394 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1395 HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1396 END IF;
1397 END LOOP;
1398 L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_DATE_PAID_MISSED.END_DATE,P_assactid,NULL);
1399 HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1400 END LOOP;
1401 END IF;--Balance Found
1402 --No Balance Found
1403 IF (L_BALANCE_FLAG ='N') THEN
1404 FOR I IN C_ELEMENT_DETAILS(P_EFFECTIVE_DATE,P_ASSIGNMENT_ID,NULL)
1405 LOOP
1406 L_SCREEN_ENTRY_VALUE := NULL;
1407 L_FORMULA_ID := NULL;
1408 p_pay_value_ivid := NULL;
1409 L_STATUS_PROCES_ID := NULL;
1410 l_result_rule_name := NULL;
1411 OPEN C_SCREEN_ENTRY_VALUE(I.ELEMENT_ENTRY_ID,P_EFFECTIVE_DATE);
1412 FETCH C_SCREEN_ENTRY_VALUE INTO L_SCREEN_ENTRY_VALUE;
1413 CLOSE C_SCREEN_ENTRY_VALUE;
1414 IF L_SCREEN_ENTRY_VALUE IS NULL THEN
1415 OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,P_EFFECTIVE_DATE);
1416 FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
1417 CLOSE C_PENSIONS_FORMULA_ID;
1418 IF L_FORMULA_ID IS NOT NULL THEN
1419 HR_UTILITY.trace('Formula Id Not Null');
1420 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1421 HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1422 END IF;
1423 ELSE --Screen Entry Value not Null
1424 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1425 HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1426 END IF;
1427 END LOOP;
1428 L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,P_EFFECTIVE_DATE,P_assactid,NULL);
1429 HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
1430 END IF;--Balance Flag
1431 END IF;
1432 ---#######################################################################################################
1433 --AGGREGATION
1434 ---#######################################################################################################
1435 IF (P_AGGR_FLAG='Y') THEN
1436 HR_UTILITY.trace('Aggregation Case - Calculate_earnings');
1437 FOR I IN C_ASSIGNMENTS_AGG
1438 LOOP
1439 IF (I.PAYROLL_ID =G_PAYROLL_ID) THEN
1440 L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE+CALCULATE_EARNINGS(I.ASSIGNMENT_ID,'N',P_PERSON_ID,G_EARN_END_DATE,p_assactid);
1441 HR_UTILITY.trace('Same Payroll ID - L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
1442 ELSE --Main Entry and this asg are of different payrolls
1443 FOR J IN C_EARN_PERIOD_DETAILS(I.PAYROLL_ID)
1444 LOOP
1445 L_BALANCE_FLAG :='N'; --Reset Flag
1446 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE +CALCULATE_ASG_ACTIONS (P_ASSIGNMENT_ID ,P_ASSACTID ,L_BALANCE_FLAG ,J.TIME_PERIOD_ID );
1447 hr_utility.trace('BALANCE Flag:'||L_BALANCE_FLAG);
1448 IF (L_BALANCE_FLAG ='N') THEN-- If Balance not found
1449 FOR X IN C_ELEMENT_DETAILS(J.END_DATE,I.ASSIGNMENT_ID,NULL)
1450 LOOP
1451 hr_utility.trace('Inside Different Payroll Aggregated Case--and balance not Found');
1452 L_SCREEN_ENTRY_VALUE := NULL;
1453 L_FORMULA_ID :=NULL;
1454 p_pay_value_ivid :=NULL;
1455 L_STATUS_PROCES_ID :=NULL;
1456 l_result_rule_name :=NULL;
1457 OPEN C_SCREEN_ENTRY_VALUE(X.ELEMENT_ENTRY_ID,J.END_DATE);
1458 FETCH C_SCREEN_ENTRY_VALUE INTO L_SCREEN_ENTRY_VALUE;
1459 CLOSE C_SCREEN_ENTRY_VALUE;
1460 IF L_SCREEN_ENTRY_VALUE IS NULL THEN
1461 OPEN C_PENSIONS_FORMULA_ID(X.ELEMENT_TYPE_ID,J.END_DATE);
1462 FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
1463 CLOSE C_PENSIONS_FORMULA_ID;
1464 END IF;
1465 IF L_FORMULA_ID IS NOT NULL THEN
1466 HR_UTILITY.trace(' Inside run Element FORMULA '||L_FORMULA_ID);
1467 -- setting the contexts
1468 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(I.ASSIGNMENT_ID, X.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
1469 HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1470 ELSE
1471 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
1472 hr_utility.trace('Else part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
1473 HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
1474 END IF;
1475 END LOOP;
1476 L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(I.ASSIGNMENT_ID,J.END_DATE,P_assactid,NULL);
1477 hr_utility.trace('Information part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
1478 END IF;-- If Balance not found
1479 END LOOP;-- For every Date Paid in that Payroll
1480 END IF; -- Same Payroll Id as Main Entry
1481 END LOOP;
1482 END IF;
1483 HR_UTILITY.trace(' Total value from all Pension Element values = '||L_TOTAL_EARNINGS_VALUE);
1484 RETURN L_TOTAL_EARNINGS_VALUE;
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 HR_UTILITY.trace(SQLERRM);
1488 RAISE;
1489 END CALCULATE_EARNINGS;
1490 --------------------------------------------------------------------------------------------------
1491 FUNCTION CHECK_AGE_EARNINGS(
1492 P_PENSIONABLE_EARNINGS NUMBER ,
1493 P_ASSIGNMENT_ID NUMBER,
1494 P_PERSON_ID NUMBER,
1495 P_AGE OUT NOCOPY NUMBER,
1496 P_REASON OUT NOCOPY VARCHAR2)
1497 RETURN NUMBER
1498 IS
1499 L_SEX PER_ALL_PEOPLE_F.SEX%type;
1500 L_AGE NUMBER;
1501 L_EMPLOYEE_STATUS_FOR_PENSION NUMBER;
1502 L_DATE_OF_BIRTH PER_ALL_PEOPLE_F.DATE_OF_BIRTH%type;
1503 L_NUM_DATE_BIRTH VARCHAR2(10);
1504 CURSOR C_AGE_SEX
1505 IS
1506 SELECT months_between (least (effective_end_date, g_prp_end_date), date_of_birth) / 12
1507 , date_of_birth
1508 , sex
1509 FROM per_all_people_f papf
1510 WHERE person_id = p_person_id
1511 AND effective_start_date =
1512 (
1513 SELECT max (effective_start_date)
1514 FROM per_all_people_f
1515 WHERE person_id = papf.person_id
1516 AND effective_start_date <= g_prp_end_date
1517 AND current_employee_flag = 'Y'
1518 )
1519 AND g_prp_end_date >= effective_start_date;
1520 /*
1521 SELECT MONTHS_BETWEEN(G_PRP_END_DATE,DATE_OF_BIRTH)/12,
1522 DATE_OF_BIRTH,
1523 SEX
1524 FROM PER_ALL_PEOPLE_F
1525 WHERE PERSON_ID = P_PERSON_ID
1526 AND G_PRP_END_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
1527 */
1528
1529 cursor c_period_number is
1530 select NUMBER_PER_FISCAL_YEAR
1531 from per_time_period_types
1532 where PERIOD_TYPE = G_PAYROLL_PERIOD_TYPE;
1533
1534 ---To Fetch Service Start Date of the employee
1535 CURSOR C_SERVICE_START (P_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE)
1536 IS
1537 SELECT DATE_START
1538 FROM PER_PERIODS_OF_SERVICE
1539 WHERE PERSON_ID = P_PERSON_ID
1540 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
1541
1542 N_STATE_PENSION_AGE NUMBER := 0;
1543 RETIRE_DATE DATE;
1544 G_AUTOTHRESHOLD NUMBER :=0;
1545 G_LOWER_THRESHOLD NUMBER :=0;
1546 l_period_number number :=0;
1547 L_SERVICE_START date;
1548 L_DOB_TT date;
1549 L_EFFECTIVE_DATE date;
1550 l_asg_start_date DATE;
1551 --To Fetch the Assignment Start Date
1552 Cursor c_asg_start_date is
1553 select EFFECTIVE_START_DATE from per_all_assignments_f
1554 where assignment_id = p_assignment_id
1555 and g_prp_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
1556 BEGIN
1557 P_REASON := ' ';
1558 OPEN C_AGE_SEX;
1559 FETCH C_AGE_SEX INTO L_AGE, L_DATE_OF_BIRTH, L_SEX ;
1560 CLOSE C_AGE_SEX;
1561 IF L_AGE >= 59 THEN
1562 --Pension age calculation needs to be done only if age >= 59
1563 IF L_SEX = 'M' THEN
1564 N_STATE_PENSION_AGE := 65;
1565 ELSIF L_SEX = 'F' THEN
1566 IF L_DATE_OF_BIRTH >= TO_DATE('1955/04/06','YYYY/MM/DD') THEN
1567 /* The retirement age for Employees born after 6th April 1955 is 65 */
1568 N_STATE_PENSION_AGE := 65;
1569 ELSIF L_DATE_OF_BIRTH >= TO_DATE('1950/04/06','YYYY/MM/DD') THEN
1570 /* For employee born between 6 April 1950 and 6th April 1955, the retirement date will be derived from the user table */
1571 /* Convert the Birth date of the employee to YYYYMMDD format. */
1572 L_NUM_DATE_BIRTH := TO_CHAR(L_DATE_OF_BIRTH,'YYYYMMDD');
1573 /* Pass the birth date and get the retire date corresponding to birth date. */
1574 -- Bug 14137856
1575 --RETIRE_DATE := HRUSERDT.GET_TABLE_VALUE(G_BUSINESS_GROUP_ID,'STATE_RETIREMENT_AGE_FOR_WOMEN','STATE_PENSION_AGE',L_NUM_DATE_BIRTH,G_PRP_END_DATE);
1576 RETIRE_DATE := TO_DATE(HRUSERDT.GET_TABLE_VALUE(G_BUSINESS_GROUP_ID,'STATE_RETIREMENT_AGE_FOR_WOMEN','STATE_PENSION_AGE',L_NUM_DATE_BIRTH,G_PRP_END_DATE),'YYYY/MM/DD');
1577 N_STATE_PENSION_AGE := ((RETIRE_DATE - L_DATE_OF_BIRTH)/365);
1578 END IF; --l_date_of_birth
1579 END IF; -- l_sex
1580 ELSE
1581 N_STATE_PENSION_AGE := 65;
1582 END IF; --Age >= 59
1583 /*
1584 Quarter
1585 Lunar Month
1586 Bi-Week
1587 Semi-Year
1588 */
1589 open c_period_number;
1590 fetch c_period_number into l_period_number;
1591 close c_period_number;
1592
1593 hr_utility.trace('l_period_number:'||l_period_number);
1594
1595 hr_utility.trace('G_PAYROLL_PERIOD_TYPE before:'||G_PAYROLL_PERIOD_TYPE);
1596
1597 IF NVL(l_period_number,0) <> 0 THEN
1598 IF l_period_number in (52,53,54,55,56) THEN
1599 G_PAYROLL_PERIOD_TYPE := 'Week';
1600 ELSIF l_period_number = 12 THEN
1601 G_PAYROLL_PERIOD_TYPE := 'Calendar Month';
1602 ELSIF l_period_number = 1 THEN
1603 G_PAYROLL_PERIOD_TYPE := 'Year' ;
1604 ELSIF l_period_number = 4 THEN
1605 G_PAYROLL_PERIOD_TYPE := 'Quarter';
1606 ELSIF l_period_number in (13,14) THEN
1607 G_PAYROLL_PERIOD_TYPE := 'Lunar Month';
1608 ELSIF l_period_number in (26,27) THEN
1609 G_PAYROLL_PERIOD_TYPE := 'Bi-Week';
1610 ELSIF l_period_number = 2 THEN
1611 G_PAYROLL_PERIOD_TYPE := 'Semi-Year' ;
1612 ELSIF l_period_number = 6 THEN
1613 G_PAYROLL_PERIOD_TYPE := 'Bi-Month' ;
1614 END IF;
1615 END IF;
1616
1617
1618 /* 16409174 - PAE THRESHOLD CHANGES start*/
1619 OPEN c_asg_start_date;
1620 FETCH c_asg_start_date INTO l_asg_start_date ;
1621 CLOSE c_asg_start_date;
1622
1623 OPEN C_SERVICE_START (P_PERSON_ID) ;
1624 FETCH C_SERVICE_START INTO L_SERVICE_START;
1625 CLOSE C_SERVICE_START;
1626 -- 22nd Birthday
1627 L_DOB_TT := FETCH_START_DATE(L_DATE_OF_BIRTH);
1628
1629 IF ((L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) OR (L_SERVICE_START BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE)) THEN
1630 L_EFFECTIVE_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,l_asg_start_date);
1631 G_GLOBAL_VALUE := NULL;
1632 OPEN C_GET_GLOBAL_VALUE(L_EFFECTIVE_DATE);
1633 FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
1634 CLOSE C_GET_GLOBAL_VALUE;
1635 hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
1636 end if;
1637 /* 16409174 - PAE THRESHOLD CHANGES end*/
1638
1639
1640 hr_utility.trace('G_PAYROLL_PERIOD_TYPE after:'||G_PAYROLL_PERIOD_TYPE);
1641 IF G_PAYROLL_PERIOD_TYPE = 'Week' THEN
1642 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_WEEKLY;
1643 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_WEEKLY_LT;
1644 ELSIF G_PAYROLL_PERIOD_TYPE = 'Calendar Month' THEN
1645 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY ;
1646 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT ;
1647 ELSIF G_PAYROLL_PERIOD_TYPE = 'Year' THEN
1648 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_ANNUAL;
1649 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_ANNUAL_LT;
1650 ELSIF G_PAYROLL_PERIOD_TYPE = 'Quarter' THEN
1651 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_QUARTERLY ;
1652 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_QUARTERLY_LT;
1653 ELSIF G_PAYROLL_PERIOD_TYPE = 'Lunar Month' THEN
1654 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_LUNAR ;
1655 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_LUNAR_LT;
1656 ELSIF G_PAYROLL_PERIOD_TYPE = 'Bi-Week' THEN
1657 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_BIWEEK;
1658 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_BIWEEK_LT;
1659 ELSIF G_PAYROLL_PERIOD_TYPE = 'Semi-Year' THEN
1660 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_SEMIYEAR;
1661 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_SEMIYEAR_LT ;
1662 ELSIF G_PAYROLL_PERIOD_TYPE = 'Bi-Month' THEN -- Monthly's Threshold multiplied by 2
1663 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY * 2;
1664 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
1665 END IF;
1666 hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
1667 hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
1668 hr_utility.trace('L_AGE:'||L_AGE);
1669 hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
1670 /*Bug 14645724: Age check criteria modified*/
1671 IF L_AGE >= 16 AND L_AGE < 75 THEN
1672 IF ((L_AGE BETWEEN 22 AND N_STATE_PENSION_AGE) AND (P_PENSIONABLE_EARNINGS >= G_AUTOTHRESHOLD)) THEN
1673 L_EMPLOYEE_STATUS_FOR_PENSION := 1;
1674 ELSIF ((L_AGE BETWEEN 16 AND 75) AND (P_PENSIONABLE_EARNINGS >= G_LOWER_THRESHOLD AND P_PENSIONABLE_EARNINGS < G_AUTOTHRESHOLD)) THEN
1675 L_EMPLOYEE_STATUS_FOR_PENSION := 2;
1676 P_REASON := P_REASON || ' This assignment is Non Eligible Job holder';
1677 ELSIF ((L_AGE BETWEEN 16 AND 22 OR L_AGE BETWEEN N_STATE_PENSION_AGE AND 75 ) AND (P_PENSIONABLE_EARNINGS >= G_AUTOTHRESHOLD)) THEN
1678 L_EMPLOYEE_STATUS_FOR_PENSION := 2;
1679 P_REASON := P_REASON || ' This assignment is Non Eligible Job holder';
1680 ELSE
1681 L_EMPLOYEE_STATUS_FOR_PENSION := 3;
1682 P_REASON := P_REASON || ' This assignment is worker';
1683 END IF;
1684 ELSE
1685 /*This criteria means employee should not be picked for assesment*/
1686 L_EMPLOYEE_STATUS_FOR_PENSION := 4;
1687 END IF;
1688 P_AGE := L_AGE;
1689 RETURN L_EMPLOYEE_STATUS_FOR_PENSION;
1690 END CHECK_AGE_EARNINGS;
1691 --------------------------------------------------------------------------------------------------
1692 FUNCTION ASSESS_EMPLOYEE_PROCESS(
1693 P_PERSON_ID IN NUMBER ,
1694 P_ASSIGNMENT_ID IN NUMBER ,
1695 P_AGGR_FLAG IN VARCHAR2 DEFAULT 'N',
1696 P_EARNINGS OUT NOCOPY NUMBER,
1697 P_AGE OUT NOCOPY NUMBER,
1698 p_assactid IN NUMBER)
1699 RETURN NUMBER
1700 IS
1701
1702 CURSOR c_payroll_run
1703 (p_assignment_id IN number) IS
1704 SELECT ppa.payroll_action_id
1705 FROM pay_payroll_actions ppa
1706 , pay_assignment_actions paa
1707 WHERE ppa.time_period_id = nvl (g_time_period_id, ppa.time_period_id)
1708 AND paa.assignment_id = p_assignment_id
1709 AND ppa.effective_date
1710 BETWEEN g_prp_start_date
1711 AND g_prp_end_date
1712 AND ppa.action_type IN ('Q', 'R')
1713 AND ppa.business_group_id = g_business_group_id
1714 AND ppa.payroll_action_id = paa.payroll_action_id
1715 ORDER BY time_period_id
1716 , assignment_action_id DESC;
1717
1718 cursor c_get_pay_status(p_person_id number,p_assignment_id number) is
1719 SELECT past.pay_system_status
1720 FROM per_assignment_status_types past
1721 , per_all_assignments_f paaf
1722 WHERE past.assignment_status_type_id = paaf.assignment_status_type_id
1723 AND paaf.assignment_id = p_assignment_id
1724 AND paaf.person_id = p_person_id
1725 AND g_prp_end_date >= paaf.effective_start_date
1726 AND g_prp_start_date <= paaf.effective_end_date;
1727
1728 L_PENSIONABLE_EARNINGS NUMBER := 0;
1729 L_EMPLOYEE_STATUS_FOR_PENSION NUMBER;
1730 L_AGE NUMBER;
1731 L_REASON VARCHAR2(200);
1732 payroll_run c_payroll_run%ROWTYPE; -- bug 16169601
1733 L_PAY_STATUS per_assignment_status_types.pay_system_status%TYPE;-- bug 16169601
1734
1735 BEGIN
1736 HR_UTILITY.trace(' Entered ASSESS_EMPLOYEE_PROCESS');
1737 --L_PENSIONABLE_EARNINGS := CALCULATE_EARNINGS(P_ASSIGNMENT_ID,P_AGGR_FLAG,P_PERSON_ID,G_EARN_END_DATE,p_assactid); -- Bug 14015189
1738 L_PENSIONABLE_EARNINGS := TRUNC (CALCULATE_EARNINGS(P_ASSIGNMENT_ID,P_AGGR_FLAG,P_PERSON_ID,G_EARN_END_DATE,p_assactid)+ 0.004 , 2);
1739 --Check Age and earnings with threshold
1740 hr_utility.trace('After calculate earnings');
1741 L_EMPLOYEE_STATUS_FOR_PENSION := CHECK_AGE_EARNINGS(L_PENSIONABLE_EARNINGS,P_ASSIGNMENT_ID, P_PERSON_ID,L_AGE,L_REASON);
1742 hr_utility.trace('After check earnings');
1743 hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION'||L_EMPLOYEE_STATUS_FOR_PENSION);
1744 P_EARNINGS := L_PENSIONABLE_EARNINGS;
1745 P_AGE := L_AGE;
1746
1747 -- get pay_status of assignment -- to check if its P or D or null
1748 open c_get_pay_status(P_PERSON_ID,P_ASSIGNMENT_ID);
1749 fetch c_get_pay_status into L_PAY_STATUS;
1750 hr_utility.trace('P_PERSON_ID: '||P_PERSON_ID);
1751 hr_utility.trace('P_ASSIGNMENT_ID: '||P_ASSIGNMENT_ID);
1752 hr_utility.trace('g_prp_end_date: '||g_prp_end_date);
1753 hr_utility.trace('g_prp_start_date: '||g_prp_start_date);--G_TIME_PERIOD_ID
1754 hr_utility.trace('G_TIME_PERIOD_ID: '||G_TIME_PERIOD_ID);
1755 close c_get_pay_status;
1756 hr_utility.trace('L_PAY_STATUS: '||L_PAY_STATUS);
1757 if L_PAY_STATUS <> 'P' then
1758 -- if its not P then check if payroll run exists for that period for given assignment
1759 open c_payroll_run(P_ASSIGNMENT_ID);
1760 fetch c_payroll_run into payroll_run;
1761 if c_payroll_run%NOTFOUND THEN
1762 hr_utility.trace('No Payroll run found');
1763 L_EMPLOYEE_STATUS_FOR_PENSION := 3;
1764 P_EARNINGS := 0;
1765 END IF;
1766 close c_payroll_run;
1767 end if;
1768
1769 hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION is:'||L_EMPLOYEE_STATUS_FOR_PENSION);
1770 hr_utility.trace('P_EARNINGS is:'||P_EARNINGS);
1771
1772 RETURN L_EMPLOYEE_STATUS_FOR_PENSION;
1773 END ASSESS_EMPLOYEE_PROCESS;
1774 --------------------------------------------------------------------------------------------------
1775 FUNCTION check_all_null(
1776 p_rules_tab eligiblity_table )
1777 RETURN NUMBER
1778 IS
1779 BEGIN
1780 IF ( p_rules_tab('Emp Category') IS NULL AND p_rules_tab('Grade') IS NULL AND p_rules_tab('Group') IS NULL AND p_rules_tab('Job')IS NULL AND p_rules_tab('Location')IS NULL AND p_rules_tab('Organization')IS NULL
1781 AND p_rules_tab('Payroll') IS NULL AND p_rules_tab('Position') IS NULL AND p_rules_tab('Salary Basis') IS NULL) THEN
1782 RETURN 1;
1783 ELSE
1784 RETURN 0;
1785 END IF;
1786 END check_all_null;
1787 --------------------------------------------------------------------------------------------------
1788 FUNCTION others_check(
1789 p_config_values eligiblity_table,
1790 p_assignment_id NUMBER)
1791 RETURN NUMBER
1792 IS
1793 l_flag NUMBER:=0;
1794 l_test NUMBER;
1795 l_value VARCHAR2(1000);
1796 temp_num NUMBER:=0;
1797 l_position NUMBER:=0;
1798 l_seg_collect seg_collection_table;
1799 CURSOR c_location(p_location VARCHAR2)
1800 IS
1801 SELECT 1
1802 FROM per_all_assignments_f a,
1803 hr_locations_all l
1804 WHERE assignment_id=p_assignment_id
1805 AND a.LOCATION_ID = l.LOCATION_ID
1806 AND l.location_code=p_location
1807 AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
1808 CURSOR c_organization(p_org_name VARCHAR2)
1809 IS
1810 SELECT 1
1811 FROM per_all_assignments_f a,
1812 HR_ALL_ORGANIZATION_UNITS u
1813 WHERE assignment_id =p_assignment_id
1814 AND u.ORGANIZATION_ID = a.ORGANIZATION_ID
1815 AND u.business_group_id=g_business_group_id
1816 AND u.name =p_org_name
1817 AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
1818 CURSOR c_payroll(p_payroll_name VARCHAR2)
1819 IS
1820 SELECT 1
1821 FROM per_all_assignments_f a,
1822 pay_payrolls_f b
1823 WHERE assignment_id =p_assignment_id
1824 AND b.business_group_id = G_BUSINESS_GROUP_ID
1825 AND b.PAYROLL_ID = a.PAYROLL_ID
1826 AND b.payroll_name =p_payroll_name
1827 AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date
1828 AND G_PRP_END_DATE BETWEEN b.effective_start_date AND b.effective_end_date;
1829 CURSOR c_grade(p_grade VARCHAR2)
1830 IS
1831 SELECT 1
1832 FROM per_all_assignments_f a,
1833 per_grades g
1834 WHERE assignment_id =p_assignment_id
1835 AND g.business_group_id = G_BUSINESS_GROUP_ID
1836 AND g.GRADE_ID = a.GRADE_ID
1837 AND g.name =p_grade
1838 AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
1839 CURSOR c_job(p_job VARCHAR2)
1840 IS
1841 SELECT 1
1842 FROM per_all_assignments_f a,
1843 per_jobs j
1844 WHERE assignment_id =p_assignment_id
1845 AND j.business_group_id = G_BUSINESS_GROUP_ID
1846 AND j.JOB_ID = a.JOB_ID
1847 AND j.name =p_job
1848 AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
1849 CURSOR c_pay_basis(p_pay_basis VARCHAR2)
1850 IS
1851 SELECT 1
1852 FROM per_all_assignments_f a,
1853 per_pay_bases p
1854 WHERE assignment_id =p_assignment_id
1855 AND p.business_group_id = G_BUSINESS_GROUP_ID
1856 AND p.PAY_BASIS_ID = a.PAY_BASIS_ID
1857 AND p.name =p_pay_basis
1858 AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
1859 CURSOR c_position(p_position VARCHAR2)
1860 IS
1861 SELECT 1
1862 FROM per_all_assignments_f a,
1863 per_all_positions p
1864 WHERE assignment_id =p_assignment_id
1865 AND p.business_group_id = G_BUSINESS_GROUP_ID
1866 AND p.POSITION_ID = a.POSITION_ID
1867 AND p.name =p_position
1868 AND G_PRP_END_DATE BETWEEN a.effective_start_date AND a.effective_end_date;
1869 CURSOR c_employment_category(p_emp_category VARCHAR2)
1870 IS
1871 SELECT 1
1872 FROM hr_lookups
1873 WHERE lookup_type = 'EMP_CAT'
1874 AND meaning = p_emp_category
1875 AND lookup_code IN
1876 (SELECT EMPLOYMENT_CATEGORY
1877 FROM per_all_assignments_f
1878 WHERE assignment_id=p_assignment_id
1879 AND G_PRP_END_DATE BETWEEN effective_start_date AND effective_end_date
1880 );
1881 BEGIN
1882 l_flag:=1;
1883 ---------------------------------------Location-------------------------------------------
1884 SELECT p_config_values('Location')
1885 INTO l_value
1886 FROM dual ;
1887 hr_utility.trace('Location:: l_value:'||l_value);
1888 IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
1889 l_flag :=0;
1890 l_position :=0;
1891 temp_num :=0;
1892 l_seg_collect.delete;
1893 LOOP -- '~'
1894 temp_num:=temp_num+1;
1895 SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
1896 SELECT instr(l_value,'~') INTO l_position FROM dual;
1897 IF(l_position=0) THEN
1898 SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
1899 END IF;
1900 EXIT
1901 WHEN l_position=0;
1902 SELECT trim(SUBSTR(l_value,1,(l_position-1)))
1903 INTO l_seg_collect(temp_num)
1904 FROM dual;
1905 END LOOP; -- '~'
1906 FOR i IN 1..l_seg_collect.count
1907 LOOP
1908 hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
1909 OPEN c_location(l_seg_collect(i));
1910 FETCH c_location INTO l_test;
1911 IF c_location%found THEN
1912 l_flag :=1;
1913 END IF;
1914 CLOSE c_location;
1915 IF l_flag = 1 THEN
1916 EXIT;
1917 END IF;
1918 END LOOP;
1919 hr_utility.trace('L_FLAG:Location:'||l_flag);
1920 IF l_flag =0 THEN
1921 RETURN 0;-- Stop here as the location criteria has failed
1922 END IF;
1923 END IF;--
1924 ---------------------------------------Location-------------------------------------------
1925 IF l_flag =1 OR l_value IS NULL THEN
1926 SELECT p_config_values('Grade') INTO l_value FROM dual ;
1927 hr_utility.trace('Grade :: l_value:'||l_value);
1928 IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
1929 l_flag :=0;
1930 l_position :=0;
1931 temp_num :=0;
1932 l_seg_collect.delete;
1933 LOOP -- '~'
1934 temp_num:=temp_num+1;
1935 SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
1936 SELECT instr(l_value,'~') INTO l_position FROM dual;
1937 IF(l_position=0) THEN
1938 SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
1939 END IF;
1940 EXIT
1941 WHEN l_position=0;
1942 SELECT trim(SUBSTR(l_value,1,(l_position-1)))
1943 INTO l_seg_collect(temp_num)
1944 FROM dual;
1945 END LOOP; -- '~'
1946 FOR i IN 1..l_seg_collect.count
1947 LOOP
1948 hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
1949 OPEN c_grade(l_seg_collect(i));
1950 FETCH c_grade INTO l_test;
1951 IF c_grade%found THEN
1952 l_flag :=1;
1953 END IF;
1954 CLOSE c_grade;
1955 IF l_flag = 1 THEN
1956 EXIT;
1957 END IF;
1958 END LOOP;
1959 hr_utility.trace('L_FLAG:Grade:'||l_flag);
1960 IF l_flag =0 THEN
1961 RETURN 0;-- Stop here as the location criteria has failed
1962 END IF;
1963 END IF;
1964 END IF;--
1965 ---------------------------------------Grade-------------------------------------------
1966 IF l_flag =1 OR l_value IS NULL THEN
1967 SELECT p_config_values('Job') INTO l_value FROM dual ;
1968 hr_utility.trace('Job :: l_value:'||l_value);
1969 IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
1970 l_flag :=0;
1971 l_position :=0;
1972 temp_num :=0;
1973 l_seg_collect.delete;
1974 LOOP -- '~'
1975 temp_num:=temp_num+1;
1976 SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
1977 SELECT instr(l_value,'~') INTO l_position FROM dual;
1978 IF(l_position=0) THEN
1979 SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
1980 END IF;
1981 EXIT
1982 WHEN l_position=0;
1983 SELECT trim(SUBSTR(l_value,1,(l_position-1)))
1984 INTO l_seg_collect(temp_num)
1985 FROM dual;
1986 END LOOP; -- '~'
1987 FOR i IN 1..l_seg_collect.count
1988 LOOP
1989 hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
1990 OPEN c_job(l_seg_collect(i));
1991 FETCH c_job INTO l_test;
1992 IF c_job%found THEN
1993 l_flag :=1;
1994 END IF;
1995 CLOSE c_job;
1996 IF l_flag = 1 THEN
1997 EXIT;
1998 END IF;
1999 END LOOP;
2000 hr_utility.trace('L_FLAG:Job:'||l_flag);
2001 IF l_flag =0 THEN
2002 RETURN 0;-- Stop here as the location criteria has failed
2003 END IF;
2004 END IF;
2005 END IF;--
2006 ---------------------------------------Job-------------------------------------------
2007 IF l_flag =1 OR l_value IS NULL THEN
2008 SELECT p_config_values('Organization') INTO l_value FROM dual ;
2009 hr_utility.trace('Organization :: l_value:'||l_value);
2010 IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2011 l_flag :=0;
2012 l_position :=0;
2013 temp_num :=0;
2014 l_seg_collect.delete;
2015 LOOP -- '~'
2016 temp_num:=temp_num+1;
2017 SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
2018 SELECT instr(l_value,'~') INTO l_position FROM dual;
2019 IF(l_position=0) THEN
2020 SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
2021 END IF;
2022 EXIT
2023 WHEN l_position=0;
2024 SELECT trim(SUBSTR(l_value,1,(l_position-1)))
2025 INTO l_seg_collect(temp_num)
2026 FROM dual;
2027 END LOOP; -- '~'
2028 FOR i IN 1..l_seg_collect.count
2029 LOOP
2030 hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2031 OPEN c_organization(l_seg_collect(i));
2032 FETCH c_organization INTO l_test;
2033 IF c_organization%found THEN
2034 l_flag :=1;
2035 END IF;
2036 CLOSE c_organization;
2037 IF l_flag = 1 THEN
2038 EXIT;
2039 END IF;
2040 END LOOP;
2041 hr_utility.trace('L_FLAG:Organization:'||l_flag);
2042 IF l_flag =0 THEN
2043 RETURN 0;-- Stop here as the location criteria has failed
2044 END IF;
2045 END IF;
2046 END IF;--
2047 ---------------------------------------Organization-------------------------------------------
2048 IF l_flag =1 OR l_value IS NULL THEN
2049 SELECT p_config_values('Payroll') INTO l_value FROM dual ;
2050 hr_utility.trace('Payroll :: l_value:'||l_value);
2051 IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2052 l_flag :=0;
2053 l_position :=0;
2054 temp_num :=0;
2055 l_seg_collect.delete;
2056 LOOP -- '~'
2057 temp_num:=temp_num+1;
2058 SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
2059 SELECT instr(l_value,'~') INTO l_position FROM dual;
2060 IF(l_position=0) THEN
2061 SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
2062 END IF;
2063 EXIT
2064 WHEN l_position=0;
2065 SELECT trim(SUBSTR(l_value,1,(l_position-1)))
2066 INTO l_seg_collect(temp_num)
2067 FROM dual;
2068 END LOOP; -- '~'
2069 FOR i IN 1..l_seg_collect.count
2070 LOOP
2071 hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2072 OPEN c_payroll(l_seg_collect(i));
2073 FETCH c_payroll INTO l_test;
2074 IF c_payroll%found THEN
2075 l_flag :=1;
2076 END IF;
2077 CLOSE c_payroll;
2078 IF l_flag = 1 THEN
2079 EXIT;
2080 END IF;
2081 END LOOP;
2082 hr_utility.trace('L_FLAG:Payroll:'||l_flag);
2083 IF l_flag =0 THEN
2084 RETURN 0;-- Stop here as the location criteria has failed
2085 END IF;
2086 END IF;
2087 END IF;--
2088 ---------------------------------------Payroll-------------------------------------------
2089 IF l_flag =1 OR l_value IS NULL THEN
2090 SELECT p_config_values('Position') INTO l_value FROM dual ;
2091 hr_utility.trace('Position :: l_value:'||l_value);
2092 IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2093 l_flag :=0;
2094 l_position :=0;
2095 temp_num :=0;
2096 l_seg_collect.delete;
2097 LOOP -- '~'
2098 temp_num:=temp_num+1;
2099 SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
2100 SELECT instr(l_value,'~') INTO l_position FROM dual;
2101 IF(l_position=0) THEN
2102 SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
2103 END IF;
2104 EXIT
2105 WHEN l_position=0;
2106 SELECT trim(SUBSTR(l_value,1,(l_position-1)))
2107 INTO l_seg_collect(temp_num)
2108 FROM dual;
2109 END LOOP; -- '~'
2110 FOR i IN 1..l_seg_collect.count
2111 LOOP
2112 hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2113 OPEN c_position(l_seg_collect(i));
2114 FETCH c_position INTO l_test;
2115 IF c_position%found THEN
2116 l_flag :=1;
2117 END IF;
2118 CLOSE c_position;
2119 IF l_flag = 1 THEN
2120 EXIT;
2121 END IF;
2122 END LOOP;
2123 hr_utility.trace('L_FLAG:Position:'||l_flag);
2124 IF l_flag =0 THEN
2125 RETURN 0;-- Stop here as the location criteria has failed
2126 END IF;
2127 END IF;
2128 END IF;--
2129 ---------------------------------------Position-------------------------------------------
2130 IF l_flag =1 OR l_value IS NULL THEN
2131 SELECT p_config_values('Salary Basis') INTO l_value FROM dual ;
2132 hr_utility.trace('Salary Basis :: l_value:'||l_value);
2133 IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2134 l_flag :=0;
2135 l_position :=0;
2136 temp_num :=0;
2137 l_seg_collect.delete;
2138 LOOP -- '~'
2139 temp_num:=temp_num+1;
2140 SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
2141 SELECT instr(l_value,'~') INTO l_position FROM dual;
2142 IF(l_position=0) THEN
2143 SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
2144 END IF;
2145 EXIT
2146 WHEN l_position=0;
2147 SELECT trim(SUBSTR(l_value,1,(l_position-1)))
2148 INTO l_seg_collect(temp_num)
2149 FROM dual;
2150 END LOOP; -- '~'
2151 FOR i IN 1..l_seg_collect.count
2152 LOOP
2153 hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2154 OPEN c_pay_basis(l_seg_collect(i));
2155 FETCH c_pay_basis INTO l_test;
2156 IF c_pay_basis%found THEN
2157 l_flag :=1;
2158 END IF;
2159 CLOSE c_pay_basis;
2160 IF l_flag = 1 THEN
2161 EXIT;
2162 END IF;
2163 END LOOP;
2164 hr_utility.trace('L_FLAG:Salary Basis:'||l_flag);
2165 IF l_flag =0 THEN
2166 RETURN 0;-- Stop here as the location criteria has failed
2167 END IF;
2168 END IF;
2169 END IF;--
2170 ---------------------------------------Salary Basis-------------------------------------------
2171 IF l_flag =1 OR l_value IS NULL THEN
2172 SELECT p_config_values('Emp Category') INTO l_value FROM dual ;
2173 hr_utility.trace('Emp Category :: l_value:'||l_value);
2174 IF l_value IS NOT NULL THEN --If Any eligiblity criteria is set
2175 l_flag :=0;
2176 l_position :=0;
2177 temp_num :=0;
2178 LOOP -- '~'
2179 temp_num:=temp_num+1;
2180 SELECT trim(SUBSTR(l_value,(l_position+1))) INTO l_value FROM dual;
2181 SELECT instr(l_value,'~') INTO l_position FROM dual;
2182 IF(l_position=0) THEN
2183 SELECT trim(SUBSTR(l_value,1)) INTO l_seg_collect(temp_num) FROM dual;
2184 END IF;
2185 EXIT
2186 WHEN l_position=0;
2187 SELECT trim(SUBSTR(l_value,1,(l_position-1)))
2188 INTO l_seg_collect(temp_num)
2189 FROM dual;
2190 END LOOP; -- '~'
2191 FOR i IN 1..l_seg_collect.count
2192 LOOP
2193 hr_utility.trace('Segment Collection:: '||i||l_seg_collect(i));
2194 OPEN c_employment_category(l_seg_collect(i));
2195 FETCH c_employment_category INTO l_test;
2196 IF c_employment_category%found THEN
2197 l_flag :=1;
2198 END IF;
2199 CLOSE c_employment_category;
2200 IF l_flag = 1 THEN
2201 EXIT;
2202 END IF;
2203 END LOOP;
2204 hr_utility.trace('L_FLAG:Emp Category:'||l_flag);
2205 IF l_flag =0 THEN
2206 RETURN 0;-- Stop here as the location criteria has failed
2207 END IF;
2208 END IF;
2209 END IF;--
2210 ---------------------------------------Emp Category-------------------------------------------
2211 IF l_flag =1 THEN
2212 RETURN 1;--Succesful Could be The criteria has been met or every eligibility rule is null except People Group
2213 END IF;
2214 IF l_flag >1 THEN
2215 RETURN 2;--Not Mutually Exclusive
2216 END IF;
2217 RETURN 0;--Failed to meet the criteria
2218 END others_check;
2219 --------------------------------------------------------------------------------------------------
2220 PROCEDURE conv_configval_to_table
2221 IS
2222 temp NUMBER;
2223 L_rulestab eligiblity_table;
2224 CURSOR X
2225 IS
2226 SELECT trim(PCV_INFORMATION1) PCV_INFORMATION1,
2227 trim(PCV_INFORMATION2) PCV_INFORMATION2,
2228 trim(PCV_INFORMATION3) PCV_INFORMATION3,
2229 trim(PCV_INFORMATION4) PCV_INFORMATION4,
2230 trim(PCV_INFORMATION5) PCV_INFORMATION5,
2231 trim(PCV_INFORMATION6) PCV_INFORMATION6,
2232 trim(PCV_INFORMATION7) PCV_INFORMATION7,
2233 trim(PCV_INFORMATION8) PCV_INFORMATION8,
2234 trim(PCV_INFORMATION9) PCV_INFORMATION9,
2235 trim(PCV_INFORMATION10) PCV_INFORMATION10,
2236 trim(PCV_INFORMATION11) PCV_INFORMATION11,
2237 trim(PCV_INFORMATION12) PCV_INFORMATION12,
2238 trim(PCV_INFORMATION13) PCV_INFORMATION13
2239 FROM PQP_CONFIGURATION_VALUES
2240 WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
2241 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
2242 function get_data_file(p_file_name varchar2)
2243 return varchar2
2244 is
2245 l_data varchar2(20000) := null;
2246 l_file_name varchar2(100);
2247 l_file_handle utl_file.file_type;
2248 l_location varchar2(150);
2249 e_fatal_error exception;
2250 begin
2251 l_file_name := trim(substr(p_file_name,instr(p_file_name,':') + 1));
2252 -- Get I/O Directory
2253 --
2254 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
2255
2256 fnd_file.put_line(fnd_file.LOG,'directory: <'|| l_location || '>');
2257 if l_location is null then
2258 -- error : I/O directory not defined
2259 fnd_file.put_line(fnd_file.LOG,'Input directory not defined. Set PER_DATA_EXCHANGE_DIR profile (HR: Data Exchange directory).');
2260 raise e_fatal_error;
2261 end if;
2262 -- Open file
2263 l_file_handle := utl_file.fopen(l_location,l_file_name,'r');
2264 utl_file.get_line(l_file_handle,l_data);
2265 fnd_file.put_line(fnd_file.LOG,l_data);
2266 utl_file.fclose(l_file_handle);
2267 return l_data;
2268 exception
2269 when e_fatal_error then
2270 UTL_FILE.FCLOSE(l_file_handle);
2271 fnd_file.put_line(fnd_file.LOG,'Fatal Error');
2272 return null;
2273 when UTL_FILE.INVALID_OPERATION then
2274 UTL_FILE.FCLOSE(l_file_handle);
2275 fnd_file.put_line(fnd_file.LOG,'Reading NINO Reply File - Invalid Operation (file not found).');
2276 return null;
2277 when UTL_FILE.INTERNAL_ERROR then
2278 UTL_FILE.FCLOSE(l_file_handle);
2279 fnd_file.put_line(fnd_file.LOG,'Reading NINO Reply File - Internal Error.');
2280 return null;
2281 when UTL_FILE.INVALID_MODE then
2282 UTL_FILE.FCLOSE(l_file_handle);
2283 fnd_file.put_line(fnd_file.LOG,'Reading NINO Reply File - Invalid Mode.');
2284 return null;
2285 when UTL_FILE.INVALID_PATH then
2286 UTL_FILE.FCLOSE(l_file_handle);
2287 fnd_file.put_line(fnd_file.LOG,'Reading NINO Reply File - Invalid Path.');
2288 return null;
2289 when UTL_FILE.INVALID_FILEHANDLE then
2290 UTL_FILE.FCLOSE(l_file_handle);
2291 fnd_file.put_line(fnd_file.LOG,'Reading NINO Reply File - Invalid File Handle.');
2292 return null;
2293 when UTL_FILE.READ_ERROR then
2294 UTL_FILE.FCLOSE(l_file_handle);
2295 fnd_file.put_line(fnd_file.LOG,'Reading NINO Reply File - Read Error.');
2296 return null;
2297 when others then
2298 fnd_file.put_line(fnd_file.LOG,'Exception : ' || SQLERRM || ' ' || sqlcode);
2299 fnd_file.put_line(fnd_file.LOG,DBMS_UTILITY.format_error_backtrace);
2300 utl_file.fclose(l_file_handle);
2301 return null;
2302 end get_data_file;
2303
2304 PROCEDURE get_values_into_table(
2305 a VARCHAR2,
2306 c VARCHAR2)
2307 IS
2308 b varchar2(2000);
2309 BEGIN
2310
2311 /** Here if b contains a file name which is identified by 'file' string
2312 then read the data from the file and
2313 replace be with the contents of the file in b.
2314 **/
2315 if instr(lower(c),'file:') <> 0 then
2316 b :=get_data_file(trim(c));
2317 else
2318 b := c;
2319 end if;
2320 CASE a
2321 WHEN 'Emp Category' THEN
2322 L_rulestab('Emp Category'):= b ;
2323 WHEN 'Grade' THEN
2324 L_rulestab('Grade') := b ;
2325 WHEN 'Group' THEN
2326 L_rulestab('Group') := b ;
2327 WHEN 'Job' THEN
2328 L_rulestab('Job') := b ;
2329 WHEN 'Location' THEN
2330 L_rulestab('Location') := b ;
2331 WHEN 'Organization' THEN
2332 L_rulestab('Organization') := b ;
2333 WHEN 'Payroll' THEN
2334 L_rulestab('Payroll') := b ;
2335 WHEN 'Position' THEN
2336 L_rulestab('Position'):= b ;
2337 WHEN 'Salary Basis' THEN
2338 L_rulestab('Salary Basis') := b ;
2339 END CASE;
2340 END get_values_into_table;
2341 BEGIN
2342 temp :=0;
2343 FOR I IN X
2344 LOOP
2345 --Initialization
2346 L_rulestab('Emp Category') := NULL ;
2347 L_rulestab('Grade') := NULL ;
2348 L_rulestab('Group') := NULL ;
2349 L_rulestab('Job') := NULL ;
2350 L_rulestab('Location') := NULL ;
2351 L_rulestab('Organization') := NULL ;
2352 L_rulestab('Payroll') := NULL ;
2353 L_rulestab('Position') := NULL ;
2354 L_rulestab('Salary Basis') := NULL ;
2355 --Initialization
2356 temp:=temp+1;
2357 hr_utility.trace('----------------------------------------------------');
2358 hr_utility.trace('Scheme Name:: '||I.PCV_INFORMATION1 );
2359 IF I.PCV_INFORMATION4 IS NOT NULL THEN
2360 hr_utility.trace(I.PCV_INFORMATION5||'::'||I.PCV_INFORMATION4);
2361 get_values_into_table(I.PCV_INFORMATION4,I.PCV_INFORMATION5);
2362 END IF;
2363 IF I.PCV_INFORMATION6 IS NOT NULL THEN
2364 hr_utility.trace(I.PCV_INFORMATION7||'::'||I.PCV_INFORMATION6);
2365 get_values_into_table(I.PCV_INFORMATION6,I.PCV_INFORMATION7);
2366 END IF;
2367 IF I.PCV_INFORMATION8 IS NOT NULL THEN
2368 hr_utility.trace(I.PCV_INFORMATION9||'::'||I.PCV_INFORMATION8);
2369 get_values_into_table(I.PCV_INFORMATION8,I.PCV_INFORMATION9);
2370 END IF;
2371 IF I.PCV_INFORMATION10 IS NOT NULL THEN
2372 hr_utility.trace(I.PCV_INFORMATION11||'::'||I.PCV_INFORMATION10);
2373 get_values_into_table(I.PCV_INFORMATION10,I.PCV_INFORMATION11);
2374 END IF;
2375 IF I.PCV_INFORMATION12 IS NOT NULL THEN
2376 hr_utility.trace(I.PCV_INFORMATION13||'::'||I.PCV_INFORMATION12);
2377 get_values_into_table(I.PCV_INFORMATION12,I.PCV_INFORMATION13);
2378 END IF;
2379 L_rulestab('Scheme Name') :=I.PCV_INFORMATION1;
2380 L_rulestab('Employee Element Name'):=I.PCV_INFORMATION2;
2381 L_rulestab('Employer Element Name'):=I.PCV_INFORMATION3;
2382 g_collecttab(temp) :=L_rulestab;
2383 END LOOP;
2384 hr_utility.trace('value of Collective Count::'||g_collecttab.count);
2385 ---Printing Tables
2386 FOR i IN 1..g_collecttab.count
2387 LOOP
2388 hr_utility.trace('%%%%%%%%%%%%%%%%%%%%%%%%%%%%');
2389 hr_utility.trace(g_collecttab(i)('Scheme Name'));
2390 hr_utility.trace(g_collecttab(i)('Employee Element Name'));
2391 hr_utility.trace(g_collecttab(i)('Employer Element Name'));
2392 hr_utility.trace(g_collecttab(i)('Emp Category'));
2393 hr_utility.trace(g_collecttab(i)('Grade'));
2394 hr_utility.trace(g_collecttab(i)('Group'));
2395 hr_utility.trace(g_collecttab(i)('Job'));
2396 hr_utility.trace(g_collecttab(i)('Location'));
2397 hr_utility.trace(g_collecttab(i)('Organization'));
2398 hr_utility.trace(g_collecttab(i)('Payroll'));
2399 hr_utility.trace(g_collecttab(i)('Position'));
2400 hr_utility.trace(g_collecttab(i)('Salary Basis'));
2401 END LOOP;
2402 --Printing Tables
2403 END conv_configval_to_table;
2404 --------------------------------------------------------------------------------------------------
2405 FUNCTION group_check(
2406 P_ASSIGNMENT_ID NUMBER,
2407 l_er_scheme_name IN OUT NOCOPY VARCHAR2)
2408 RETURN NUMBER
2409 IS
2410 l_receive_ppl_group people_group_id_tab;
2411 stmt VARCHAR2(10000);
2412 l_test NUMBER;
2413 l_flag NUMBER;
2414 l_final_flag NUMBER;
2415 CURSOR C_ASSIGNMENT_CHECK(p_people_group_id NUMBER)
2416 IS
2417 SELECT 1
2418 FROM PER_ALL_ASSIGNMENTS_F PAAF
2419 WHERE PAAF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
2420 AND p_people_group_id = people_group_id
2421 AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE;
2422 CURSOR c_group_name_check (p_group_name VARCHAR2)
2423 IS
2424 SELECT 1
2425 FROM fnd_id_flex_structures b ,
2426 fnd_id_flex_structures_tl t
2427 WHERE b.id_flex_num = t.id_flex_num
2428 AND b.id_flex_code = 'GRP'
2429 AND t.language = 'US'
2430 AND t.id_flex_structure_name = p_group_name;
2431 CURSOR c_col_name (p_group_name VARCHAR2,p_seg_name VARCHAR2)
2432 IS
2433 SELECT application_column_name,
2434 b.id_flex_num
2435 FROM fnd_id_flex_structures b ,
2436 fnd_id_flex_structures_tl t ,
2437 fnd_id_flex_segments c
2438 WHERE b.id_flex_code = 'GRP'
2439 AND t.language = 'US'
2440 AND t.id_flex_num = b.id_flex_num
2441 AND c.id_flex_num = b.id_flex_num
2442 AND t.id_flex_structure_name = p_group_name
2443 AND c.segment_name = p_seg_name;
2444 l_col_name VARCHAR2(150);
2445 l_flex_num NUMBER;
2446 PROCEDURE get_group_details(
2447 p_config_values eligiblity_table,
2448 l_people_group_id_tab IN OUT nocopy people_group_id_tab)
2449 IS
2450 type name_value
2451 IS
2452 record
2453 (
2454 name VARCHAR2(150), -- Segment Name
2455 value VARCHAR2(150)); --Segment value
2456 TYPE seg_name_value_tab
2457 IS
2458 TABLE OF name_value INDEX BY BINARY_INTEGER;
2459 l_seg_collection seg_collection_table;
2460 l_seg_name_value_tab seg_name_value_tab;
2461 l_group_name VARCHAR2(150);
2462 L_group_value VARCHAR2(1000);
2463 l_position NUMBER; -- '|'
2464 l_position_sub NUMBER; -- '~'
2465 temp_num NUMBER;
2466 temp_num_sub NUMBER;
2467 l_people_group_id NUMBER;
2468 l_temp_char VARCHAR2(150);
2469 tc sys_refcursor;
2470 BEGIN
2471 SELECT p_config_values('Group') INTO L_group_value FROM dual ;
2472 IF L_group_value IS NOT NULL THEN --If Any eligiblity criteria is set for 'GROUP'
2473 SELECT instr(L_group_value,'|') INTO l_position FROM dual;
2474 SELECT trim(SUBSTR(L_group_value,1,(l_position-1)))
2475 INTO l_group_name
2476 FROM dual;
2477 hr_utility.trace('l_group_name:'||l_group_name);
2478 OPEN c_group_name_check(l_group_name);
2479 FETCH c_group_name_check INTO l_test;
2480 IF c_group_name_check%notfound THEN
2481 hr_utility.trace('People Group Name is not valid::'||l_group_name);
2482 RAISE_APPLICATION_ERROR(-20001,'The People Group Name '||l_group_name||' entered for Eligibility Value is not valid.');
2483 END IF;
2484 CLOSE c_group_name_check;
2485 IF l_group_name IS NOT NULL THEN -- If No GROUP NAME is entered
2486 l_seg_name_value_tab(1).name :='$NAME$'; -- $ to avoid incase the same clashes with any People group Field Name
2487 l_seg_name_value_tab(1).value:=l_group_name;
2488 temp_num :=0;
2489 LOOP -- '~'
2490 temp_num:=temp_num +1;
2491 SELECT trim(SUBSTR(L_group_value,(l_position+1)))
2492 INTO L_group_value
2493 FROM dual;
2494 SELECT instr(L_group_value,'~') INTO l_position FROM dual;
2495 IF(l_position=0) THEN
2496 SELECT trim(SUBSTR(L_group_value,1))
2497 INTO l_seg_collection(temp_num)
2498 FROM dual;
2499 END IF;
2500 EXIT
2501 WHEN l_position=0;
2502 SELECT trim(SUBSTR(L_group_value,1,(l_position-1)))
2503 INTO l_seg_collection(temp_num)
2504 FROM dual;
2505 END LOOP; -- '~'
2506 FOR i IN 1..l_seg_collection.count
2507 LOOP
2508 hr_utility.trace('Seg Collection:: '||i||l_seg_collection(i));
2509 END LOOP;
2510 stmt :=NULL;
2511 FOR ii IN 1..l_seg_collection.count
2512 LOOP -- Segments Collection
2513 l_seg_name_value_tab.delete;
2514 l_seg_name_value_tab(1).name :='$NAME$';-- $ to avoid incase the same clashes with any People group Field Name
2515 l_seg_name_value_tab(1).value:=l_group_name;
2516 temp_num_sub :=0;
2517 l_position :=0;
2518 LOOP -- '|'
2519 temp_num_sub:=temp_num_sub +1;
2520 SELECT trim(SUBSTR(l_seg_collection(ii),(l_position+1)))
2521 INTO l_seg_collection(ii)
2522 FROM dual;
2523 SELECT instr(l_seg_collection(ii),'|') INTO l_position FROM dual;
2524 IF(l_position=0) THEN
2525 SELECT trim(SUBSTR(l_seg_collection(ii),1)) INTO l_temp_char FROM dual;
2526 SELECT trim(SUBSTR(l_temp_char,1,(instr(l_temp_char,'.')-1))),
2527 trim(SUBSTR(l_temp_char,(instr(l_temp_char,'.') +1)))
2528 INTO l_seg_name_value_tab(temp_num_sub).name,
2529 l_seg_name_value_tab(temp_num_sub).value
2530 FROM dual;
2531 END IF;
2532 EXIT
2533 WHEN l_position=0;
2534 SELECT trim(SUBSTR(l_seg_collection(ii),1,(l_position-1)))
2535 INTO l_temp_char
2536 FROM dual;
2537 SELECT trim(SUBSTR(l_temp_char,1,(instr(l_temp_char,'.')-1))),
2538 trim(SUBSTR(l_temp_char,(instr(l_temp_char,'.') +1)))
2539 INTO l_seg_name_value_tab(temp_num_sub).name,
2540 l_seg_name_value_tab(temp_num_sub).value
2541 FROM dual;
2542 END LOOP;-- '|'
2543 FOR i IN 1..l_seg_name_value_tab.count
2544 LOOP
2545 hr_utility.trace('For ii='||ii||'Seg val tab::'||l_seg_name_value_tab(i).name||'##'||l_seg_name_value_tab(i).value);
2546 END LOOP;
2547 IF ii =1 THEN -- Segment Collection First
2548 FOR i IN 1..l_seg_name_value_tab.count
2549 LOOP
2550 hr_utility.trace(l_seg_name_value_tab(i).name||'---'||l_seg_name_value_tab(i).value);
2551 OPEN c_col_name(l_group_name,l_seg_name_value_tab(i).name) ;
2552 FETCH c_col_name INTO l_col_name,l_flex_num;
2553 CLOSE c_col_name;
2554 hr_utility.trace('Column Name::'||l_col_name);
2555 hr_utility.trace('l_flex_num::'||l_flex_num);
2556 -- if i=2 then -- i=1 will be '$NAME$'
2557 IF l_flex_num IS NOT NULL AND l_col_name IS NOT NULL THEN
2558 IF i =1 THEN
2559 stmt := 'select PEOPLE_GROUP_ID from pay_people_groups where (ID_FLEX_NUM ='||l_flex_num||' and '|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2560 ELSE
2561 stmt := stmt ||' and '|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2562 END IF;
2563 ELSE
2564 hr_utility.trace('No Column found for the flexfield segment:: '||l_seg_name_value_tab(i).name);
2565 RAISE_APPLICATION_ERROR(-20001,'Segment Name: '||l_seg_name_value_tab(i).name||' entered for the People Group: '||l_group_name||' is not valid.');
2566 END IF;
2567 END LOOP;
2568 stmt := stmt||')';
2569 ELSE
2570 FOR i IN 1..l_seg_name_value_tab.count
2571 LOOP
2572 hr_utility.trace(l_seg_name_value_tab(i).name||'---'||l_seg_name_value_tab(i).value);
2573 OPEN c_col_name(l_group_name,l_seg_name_value_tab(i).name) ;
2574 FETCH c_col_name INTO l_col_name,l_flex_num;
2575 CLOSE c_col_name;
2576 hr_utility.trace('Column Name::'||l_col_name);
2577 hr_utility.trace('l_flex_num::'||l_flex_num);
2578 IF l_col_name IS NOT NULL THEN
2579 IF i =1 THEN
2580 stmt := stmt ||' OR ('|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2581 ELSE
2582 stmt := stmt ||' and '|| l_col_name ||' = '||''''||l_seg_name_value_tab(i).value||'''';
2583 END IF;
2584 ELSE
2585 hr_utility.trace('No Column found for the flexfield segment:: '||l_seg_name_value_tab(i).name);
2586 RAISE_APPLICATION_ERROR(-20001,'Segment Name: '||l_seg_name_value_tab(i).name||' entered for the People Group: '||l_group_name||' is not valid.');
2587 END IF;
2588 END LOOP;
2589 stmt := stmt||')';
2590 END IF; -- Segment Collection First
2591 END LOOP; -- Segments Collection
2592 hr_utility.trace('Final Statement : '||stmt);
2593 OPEN tc FOR stmt;
2594 LOOP
2595 FETCH tc INTO l_people_group_id;
2596 EXIT
2597 WHEN tc%notfound;
2598 hr_utility.trace('People_group_id::'||l_people_group_id);
2599 l_people_group_id_tab(l_people_group_id_tab.count+1) :=l_people_group_id;
2600 hr_utility.trace('PPPL GROUP ID into Table::'||l_people_group_id_tab(l_people_group_id_tab.count));
2601 END LOOP;
2602 CLOSE tc;
2603 END IF; -- If No GROUP NAME is entered
2604 END IF; --If Any eligiblity criteria is set for 'GROUP'
2605 END get_group_details;
2606 BEGIN
2607 --conv_configval_to_table(); -- Moved to ARCH_INIT
2608 l_flag :=0;
2609 l_final_flag :=0;
2610 FOR i IN 1..g_collecttab.count --Every Config Value
2611 LOOP
2612 l_receive_ppl_group.delete;
2613 l_flag:=0;
2614 hr_utility.trace('--------------------------------------------------------------------');
2615 hr_utility.trace('Scheme Number::: '||i);
2616 hr_utility.trace('--------------------------------------------------------------------');
2617 IF check_all_null(g_collecttab(i))=0 THEN --If Not, All values are null
2618 IF g_collecttab(i)('Group') IS NOT NULL THEN --Check for People Group and Others also If this is true
2619 get_group_details(g_collecttab(i),l_receive_ppl_group);
2620 hr_utility.trace('Count::'||l_receive_ppl_group.count);
2621 FOR j IN 1..l_receive_ppl_group.count--PPL Group ID Check
2622 LOOP
2623 hr_utility.trace(l_receive_ppl_group(j));
2624 ---Loop where all PPL Group ID are getting fetched one by one
2625 OPEN C_ASSIGNMENT_CHECK(l_receive_ppl_group(j));
2626 FETCH C_ASSIGNMENT_CHECK INTO l_test;
2627 IF C_ASSIGNMENT_CHECK%found THEN
2628 l_flag :=1;
2629 END IF;
2630 CLOSE C_ASSIGNMENT_CHECK;
2631 IF l_flag = 1 THEN
2632 EXIT;-- People group id loop
2633 END IF;
2634 END LOOP; --PPL Group ID Check
2635 END IF; --group not null
2636 IF l_flag =1 OR g_collecttab(i)('Group') IS NULL THEN --Check Only for Others
2637 l_flag := others_check(g_collecttab(i),P_ASSIGNMENT_ID);
2638 END IF;
2639 IF l_flag =1 THEN --Set Final Flag
2640 l_er_scheme_name :=g_collecttab(i)('Scheme Name');
2641 l_final_flag :=l_final_flag+1;
2642 IF (l_final_flag >1) THEN
2643 hr_utility.trace('Schemes Not Mutually Exclusive. Hence exiting group_check');
2644 RETURN 2;
2645 END IF;
2646 END IF;--Set Final Flag
2647 END IF; --If Not, All values are null
2648 END LOOP; --Every Config Value
2649 hr_utility.trace('Final criteria Flag::'||l_final_flag);
2650 IF l_final_flag =1 THEN
2651 hr_utility.trace('Criteria Met for the Scheme-'||l_er_scheme_name);
2652 RETURN 1;
2653 END IF;
2654 IF l_final_flag>1 THEN
2655 hr_utility.trace('Not Mutually Exclusive');
2656 RETURN 2;
2657 END IF;
2658 IF l_final_flag =0 THEN
2659 hr_utility.trace('No Scheme met the Criteria for the assignment');--Should throw an error
2660 RETURN 0; --No Scheme Found eligible for this assignment
2661 END IF;
2662 /*
2663 0 -- No Match
2664 1 -- Exaclty One Match
2665 2 -- More than one Match - Not Mutually Exclusive
2666 */
2667 END group_check;
2668 ---------------------------------------------------------------------------------------------------
2669 ---------------------------------------------------------------------------------------------------
2670 PROCEDURE UPDATE_INFO_ELEMENT(
2671 P_ASSIGNMENT_ID NUMBER ,
2672 P_INPUT_VALUE_ID1 NUMBER ,
2673 P_ENTRY_VALUE1 VARCHAR2 ,
2674 P_INPUT_VALUE_ID2 NUMBER ,
2675 P_ENTRY_VALUE2 VARCHAR2 ,
2676 P_INPUT_VALUE_ID3 NUMBER ,
2677 P_ENTRY_VALUE3 VARCHAR2 ,
2678 P_INPUT_VALUE_ID4 NUMBER ,
2679 P_ENTRY_VALUE4 VARCHAR2 ,
2680 P_INPUT_VALUE_ID5 NUMBER ,
2681 P_ENTRY_VALUE5 VARCHAR2 ,
2682 P_INPUT_VALUE_ID6 NUMBER ,
2683 P_ENTRY_VALUE6 VARCHAR2 ,
2684 P_INPUT_VALUE_ID7 NUMBER ,
2685 P_ENTRY_VALUE7 VARCHAR2 ,
2686 P_INPUT_VALUE_ID8 NUMBER ,
2687 P_ENTRY_VALUE8 VARCHAR2 ,
2688 P_INPUT_VALUE_ID9 NUMBER ,
2689 P_ENTRY_VALUE9 VARCHAR2 ,
2690 P_INPUT_VALUE_ID10 NUMBER ,
2691 P_ENTRY_VALUE10 VARCHAR2 ,
2692 P_INPUT_VALUE_ID11 NUMBER ,
2693 P_ENTRY_VALUE11 VARCHAR2 ,
2694 P_INPUT_VALUE_ID12 NUMBER ,
2695 P_ENTRY_VALUE12 VARCHAR2 ,
2696 P_INPUT_VALUE_ID13 NUMBER ,
2697 P_ENTRY_VALUE13 VARCHAR2 )
2698 IS
2699 -- PRAGMA AUTONOMOUS_TRANSACTION;
2700 L_ENTRY_VALUE2 VARCHAR(5):='No';
2701 L_ENTRY_VALUE4 VARCHAR(5):='No';
2702 L_ENTRY_VALUE10 VARCHAR(5):='No';
2703 CURSOR C_ELEMENT_ENTRIES
2704 IS
2705 SELECT ELEMENT_ENTRY_ID,
2706 GREATEST(EFFECTIVE_START_DATE,G_PRP_START_DATE) START_DATE
2707 FROM PAY_ELEMENT_ENTRIES_F
2708 WHERE ASSIGNMENT_ID =P_ASSIGNMENT_ID
2709 AND ELEMENT_TYPE_ID = G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
2710 AND (G_PRP_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
2711 OR EFFECTIVE_START_DATE > G_PRP_START_DATE);
2712 BEGIN
2713 hr_utility.trace('Inside Update Element Procedure with the mode :'|| G_MODE);
2714 hr_utility.trace('INput Value ID');
2715 hr_utility.trace(P_INPUT_VALUE_ID1);
2716 hr_utility.trace(P_INPUT_VALUE_ID2);
2717 hr_utility.trace(P_INPUT_VALUE_ID3);
2718 hr_utility.trace(P_INPUT_VALUE_ID4);
2719 hr_utility.trace(P_INPUT_VALUE_ID5);
2720 hr_utility.trace(P_INPUT_VALUE_ID6);
2721 hr_utility.trace(P_INPUT_VALUE_ID7);
2722 hr_utility.trace(P_INPUT_VALUE_ID8);
2723 hr_utility.trace(P_INPUT_VALUE_ID9);
2724 hr_utility.trace(P_INPUT_VALUE_ID10);
2725 hr_utility.trace(P_INPUT_VALUE_ID11);
2726 hr_utility.trace(P_INPUT_VALUE_ID12);
2727 hr_utility.trace(P_INPUT_VALUE_ID13);
2728 hr_utility.trace('Entry Values');
2729 hr_utility.trace(P_ENTRY_VALUE1);
2730 hr_utility.trace(P_ENTRY_VALUE2);
2731 hr_utility.trace(P_ENTRY_VALUE3);
2732 hr_utility.trace(P_ENTRY_VALUE4);
2733 hr_utility.trace(P_ENTRY_VALUE5);
2734 hr_utility.trace(P_ENTRY_VALUE6);
2735 hr_utility.trace(P_ENTRY_VALUE7);
2736 hr_utility.trace(P_ENTRY_VALUE8);
2737 hr_utility.trace(P_ENTRY_VALUE9);
2738 hr_utility.trace(P_ENTRY_VALUE10);
2739 hr_utility.trace(P_ENTRY_VALUE11);
2740 hr_utility.trace(P_ENTRY_VALUE12);
2741 hr_utility.trace(P_ENTRY_VALUE13);
2742 IF (P_ENTRY_VALUE2='Y') THEN
2743 L_ENTRY_VALUE2 :='Yes';
2744 ELSE
2745 L_ENTRY_VALUE2:='No';
2746 END IF;
2747 IF (P_ENTRY_VALUE4='Y') THEN
2748 L_ENTRY_VALUE4 :='Yes';
2749 ELSE
2750 L_ENTRY_VALUE4:='No';
2751 END IF;
2752 IF (P_ENTRY_VALUE10='Y') THEN
2753 L_ENTRY_VALUE10 :='Yes';
2754 ELSE
2755 L_ENTRY_VALUE10:='No';
2756 END IF;
2757 --Mode if -GB_VALIDATE Then Donot Update the Element
2758 FOR entries IN C_ELEMENT_ENTRIES
2759 LOOP
2760 SAVEPOINT update_element_transaction;
2761 HR_ENTRY_API.UPDATE_ELEMENT_ENTRY (P_DT_UPDATE_MODE =>'UPDATE', P_SESSION_DATE => entries.START_DATE, P_CHECK_FOR_UPDATE =>'N' , P_CREATOR_TYPE => 'F', P_ELEMENT_ENTRY_ID => entries.ELEMENT_ENTRY_ID ,
2762 P_INPUT_VALUE_ID1 => P_INPUT_VALUE_ID1, P_ENTRY_VALUE1 => P_ENTRY_VALUE1 , P_INPUT_VALUE_ID2 => P_INPUT_VALUE_ID2, P_ENTRY_VALUE2 => L_ENTRY_VALUE2 ,P_INPUT_VALUE_ID3 => P_INPUT_VALUE_ID3,
2763 P_ENTRY_VALUE3 => P_ENTRY_VALUE3 , P_INPUT_VALUE_ID4 => P_INPUT_VALUE_ID4, P_ENTRY_VALUE4 => L_ENTRY_VALUE4, P_INPUT_VALUE_ID5 => P_INPUT_VALUE_ID5, P_ENTRY_VALUE5 => P_ENTRY_VALUE5,
2764 P_INPUT_VALUE_ID6 => P_INPUT_VALUE_ID6, P_ENTRY_VALUE6 => P_ENTRY_VALUE6, P_INPUT_VALUE_ID7 => P_INPUT_VALUE_ID7, P_ENTRY_VALUE7 => P_ENTRY_VALUE7, P_INPUT_VALUE_ID8 => P_INPUT_VALUE_ID8,
2765 P_ENTRY_VALUE8 => P_ENTRY_VALUE8, P_INPUT_VALUE_ID9 => P_INPUT_VALUE_ID9, P_ENTRY_VALUE9 => P_ENTRY_VALUE9, P_INPUT_VALUE_ID10 => P_INPUT_VALUE_ID10, P_ENTRY_VALUE10 => L_ENTRY_VALUE10,
2766 P_INPUT_VALUE_ID11 => P_INPUT_VALUE_ID11, P_ENTRY_VALUE11 => P_ENTRY_VALUE11, P_INPUT_VALUE_ID12 => P_INPUT_VALUE_ID12, P_ENTRY_VALUE12 => P_ENTRY_VALUE12, P_INPUT_VALUE_ID13 => P_INPUT_VALUE_ID13,
2767 P_ENTRY_VALUE13 => P_ENTRY_VALUE13 );
2768 IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
2769 ROLLBACK TO update_element_transaction;
2770 END IF;
2771 END LOOP;
2772 --Rollback to the savepoint 'update_element_transaction' if not in Commit Mode.
2773 EXCEPTION
2774 WHEN OTHERS THEN
2775 hr_utility.trace('ERROR'||SQLERRM);
2776 hr_utility.trace('Error While Updating Pensions Information Element');
2777 --ROLLBACK;
2778 RAISE;
2779 END UPDATE_INFO_ELEMENT;
2780 --------------------------------------------------------------------------------------------------
2781 --
2782 PROCEDURE populate_run_msg(
2783 p_assignment_action_id IN NUMBER ,
2784 p_message_text IN VARCHAR2 )
2785 IS
2786 PRAGMA AUTONOMOUS_TRANSACTION;
2787 BEGIN
2788 hr_utility.set_location(' Entering: populate_run_msg',111);
2789 INSERT
2790 INTO pay_message_lines
2791 (
2792 line_sequence,
2793 payroll_id,
2794 message_level,
2795 source_id,
2796 source_type,
2797 line_text
2798 )
2799 VALUES
2800 (
2801 pay_message_lines_s.nextval ,
2802 123 ,
2803 'F' ,
2804 p_assignment_action_id ,
2805 'A' ,
2806 SUBSTR(p_message_text,1,2000)
2807 );
2808 hr_utility.set_location(' Leaving: populate_run_msg',999);
2809 COMMIT;
2810 EXCEPTION
2811 WHEN OTHERS THEN
2812 hr_utility.trace('Error occured in populate_run_msg');
2813 RAISE;
2814 END populate_run_msg;
2815 -----------------------------------------------------------------------------------------------------
2816 PROCEDURE populate_warn_msg
2817 (
2818 p_assignment_action_id IN NUMBER ,
2819 p_message_text IN VARCHAR2
2820 )
2821 IS
2822 PRAGMA AUTONOMOUS_TRANSACTION;
2823 BEGIN
2824 hr_utility.set_location(' Entering: populate_warn_msg',111);
2825 INSERT
2826 INTO pay_message_lines
2827 (
2828 line_sequence,
2829 payroll_id,
2830 message_level,
2831 source_id,
2832 source_type,
2833 line_text
2834 )
2835 VALUES
2836 (
2837 pay_message_lines_s.nextval ,
2838 111 ,
2839 'W' ,
2840 p_assignment_action_id ,
2841 'A' ,
2842 SUBSTR(p_message_text,1,2000)
2843 );
2844 hr_utility.set_location(' Leaving: populate_warn_msg',999);
2845 COMMIT;
2846 EXCEPTION
2847 WHEN OTHERS THEN
2848 hr_utility.trace('Error occured in populate_run_msg');
2849 RAISE;
2850 END populate_warn_msg;
2851 PROCEDURE INSERT_PENSION_ELEMENT
2852 (
2853 V_EFF_START_DATE IN OUT NOCOPY DATE ,-- Auto Enrolment Date is passed here -- Bug 14343133
2854 V_EFF_END_DATE IN OUT NOCOPY DATE ,
2855 V_ELEMENT_ENTRY_ID IN OUT NOCOPY NUMBER ,
2856 P_ASSIGNMENT_ID NUMBER ,
2857 P_ELEMENT_TYPE_NAME VARCHAR,
2858 P_AGGREGATION_FLAG VARCHAR,
2859 P_PERSON_ID NUMBER,
2860 P_ASSIGNMENT_ACTION_ID NUMBER
2861 )
2862 IS
2863 -- PRAGMA AUTONOMOUS_TRANSACTION;
2864 L_SERVICE_START DATE;
2865 l_asg_start_date DATE;
2866 --To Fetch the Assignment Start Date
2867 Cursor c_asg_start_date is
2868 select EFFECTIVE_START_DATE from per_all_assignments_f
2869 where assignment_id = p_assignment_id
2870 and g_prp_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
2871 ---To Fetch Service Start Date of the employee
2872 CURSOR C_SERVICE_START (P_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE)
2873 IS
2874 SELECT DATE_START
2875 FROM PER_PERIODS_OF_SERVICE
2876 WHERE PERSON_ID = P_PERSON_ID
2877 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
2878 -- To get the Period start date of the period - date paid immeditely after Auto-Enrolment Date
2879 CURSOR C_PENSION_ENTRY_START(P_PAYROLL_ID NUMBER,P_SERVICE_START DATE,P_ASG_START_DATE DATE)
2880 IS
2881 SELECT GREATEST(START_DATE,P_SERVICE_START,P_ASG_START_DATE)
2882 FROM PER_TIME_PERIODS
2883 WHERE REGULAR_PAYMENT_DATE >= V_EFF_START_DATE
2884 AND PAYROLL_ID = P_PAYROLL_ID
2885 ORDER BY REGULAR_PAYMENT_DATE;
2886 CURSOR C_ELEMENT_INFO
2887 IS
2888 SELECT ELEMENT_TYPE_ID
2889 FROM PAY_ELEMENT_TYPES_F
2890 WHERE ELEMENT_NAME =P_ELEMENT_TYPE_NAME
2891 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
2892 AND V_EFF_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
2893 -- Cursor to fetch all Active Aggregated assignments across payrolls
2894 CURSOR C_ALL_ASSIGNMENTS_AGG (L_PERSON_ID NUMBER)
2895 IS
2896 SELECT PAAF.PAYROLL_ID,PAAF.EFFECTIVE_START_DATE,
2897 PAAF.ASSIGNMENT_ID,
2898 PEEF.ELEMENT_ENTRY_ID,
2899 PAAF.ASSIGNMENT_NUMBER
2900 FROM PER_ALL_PEOPLE_F PAPF,
2901 PER_ALL_ASSIGNMENTS_F PAAF,
2902 PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
2903 PAY_ELEMENT_ENTRIES_F PEEF,
2904 PER_ASSIGNMENT_STATUS_TYPES PAST
2905 WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
2906 AND PAPF.PERSON_ID =L_PERSON_ID
2907 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
2908 -- AND PAST.PAY_SYSTEM_STATUS='P'
2909 AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
2910 AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
2911 AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
2912 AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
2913 AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID
2914 AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
2915 AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
2916 AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
2917 AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
2918 AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
2919 L_PENSION_ENTRY_START DATE :=NULL;
2920 P_ELEMENT_LINK_ID NUMBER;
2921 P_ELEMENT_TYPE_ID NUMBER;
2922 BEGIN
2923 hr_utility.trace('Insert Pension Element');
2924 hr_utility.trace('G_BUSINESS_GROUP_ID :'||G_BUSINESS_GROUP_ID);
2925 hr_utility.trace('P_ELEMENT_TYPE_NAME:'||P_ELEMENT_TYPE_NAME);
2926 OPEN C_SERVICE_START(P_PERSON_ID);
2927 FETCH C_SERVICE_START INTO L_SERVICE_START;
2928 CLOSE C_SERVICE_START;
2929 hr_utility.trace('L_SERVICE_START : '||L_SERVICE_START);
2930
2931 OPEN c_asg_start_date;
2932 FETCH c_asg_start_date INTO l_asg_start_date;
2933 CLOSE c_asg_start_date;
2934 hr_utility.trace('l_asg_start_date : '||l_asg_start_date);
2935 OPEN C_ELEMENT_INFO ;
2936 FETCH C_ELEMENT_INFO INTO P_ELEMENT_TYPE_ID;
2937 IF C_ELEMENT_INFO%NOTFOUND THEN
2938 hr_utility.trace('No Details Found for the Pension Deduction Element: '||P_ELEMENT_TYPE_NAME);
2939 -- RAISE_APPLICATION_ERROR(-20001,'No Details Found for the Pension Deduction Element: '||P_ELEMENT_TYPE_NAME);
2940 END IF;
2941 CLOSE C_ELEMENT_INFO;
2942 hr_utility.trace('P_AGGREGATION_FLAG::'||P_AGGREGATION_FLAG);
2943 --Non Aggregated
2944 IF NVL(P_AGGREGATION_FLAG,'N')='N' THEN
2945 --To get the date where the pension element is to be attached
2946 OPEN C_PENSION_ENTRY_START(G_PAYROLL_ID,L_SERVICE_START,l_asg_start_date);
2947 FETCH C_PENSION_ENTRY_START INTO L_PENSION_ENTRY_START;
2948 CLOSE C_PENSION_ENTRY_START;
2949 hr_utility.trace('L_PENSION_ENTRY_START:'||L_PENSION_ENTRY_START);
2950 --Get Link Id
2951 P_ELEMENT_LINK_ID:=hr_entry_api.get_link( P_assignment_id => P_ASSIGNMENT_ID, P_element_type_id => P_ELEMENT_TYPE_ID, P_session_date => L_PENSION_ENTRY_START);
2952 hr_utility.trace('P_ELEMENT_LINK_ID:'||P_ELEMENT_LINK_ID);
2953 IF P_ELEMENT_LINK_ID IS NULL THEN
2954 hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||P_ASSIGNMENT_ID ||' as on '||L_PENSION_ENTRY_START);
2955 --raise_application_error(-20001,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||P_ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2956 populate_run_msg(P_ASSIGNMENT_ACTION_ID,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment Number ' ||NVL(get_assg_number(P_ASSIGNMENT_ID),'N/A') ||' as on '||
2957 L_PENSION_ENTRY_START||'.');
2958 RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
2959 ELSE
2960 --Insert Pension Element
2961 --Mode if -GB_VALIDATE Then Donot Insert the Element
2962 SAVEPOINT insert_element_transaction;
2963 hr_utility.trace('VAlues Before .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
2964 HR_ENTRY_API.INSERT_ELEMENT_ENTRY(P_EFFECTIVE_START_DATE => L_PENSION_ENTRY_START, P_EFFECTIVE_END_DATE => V_EFF_END_DATE, P_ELEMENT_ENTRY_ID => V_ELEMENT_ENTRY_ID, P_ASSIGNMENT_ID => P_ASSIGNMENT_ID,
2965 P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID, P_CREATOR_TYPE => 'F', P_ENTRY_TYPE => 'E' );
2966 hr_utility.trace('VAlues After .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
2967 IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
2968 ROLLBACK TO insert_element_transaction;
2969 END IF;--Rollback to the savepoint 'insert_element_transaction' if not in Commit Mode.
2970 IF (V_EFF_END_DATE <G_END_OF_TIME) THEN
2971 hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' is attached to the Assignment ID '||P_ASSIGNMENT_ID||' effective from '||L_PENSION_ENTRY_START||' to ' ||V_EFF_END_DATE||'.');
2972 populate_warn_msg(p_assignment_action_id,'The Pension Element '||P_ELEMENT_TYPE_NAME||' has been attached to the Assignment Number '||NVL(get_assg_number(P_ASSIGNMENT_ID),'N/A')||' effective from '||
2973 L_PENSION_ENTRY_START||' to ' ||V_EFF_END_DATE||'. Please review the reason for end date not being extended beyond '||V_EFF_END_DATE ||'.');
2974 END IF;
2975 END IF;
2976 END IF;--Non Aggregated
2977 --######################################################
2978 --Aggregated
2979 IF NVL(P_AGGREGATION_FLAG,'N')='Y' THEN
2980 FOR I IN C_ALL_ASSIGNMENTS_AGG(P_PERSON_ID)
2981 LOOP
2982 --To get the date where the pension element is to be attached
2983 OPEN C_PENSION_ENTRY_START(I.PAYROLL_ID,L_SERVICE_START,I.EFFECTIVE_START_DATE);
2984 FETCH C_PENSION_ENTRY_START INTO L_PENSION_ENTRY_START;
2985 CLOSE C_PENSION_ENTRY_START;
2986 --Get Link Id
2987 hr_utility.trace('Inside Aggregation Insert Pension Element');
2988 P_ELEMENT_LINK_ID:=hr_entry_api.get_link( P_assignment_id => I.ASSIGNMENT_ID, P_element_type_id => P_ELEMENT_TYPE_ID, P_session_date => L_PENSION_ENTRY_START);
2989 hr_utility.trace('P_ELEMENT_LINK_ID:'||P_ELEMENT_LINK_ID);
2990 IF P_ELEMENT_LINK_ID IS NULL THEN
2991 hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||I.ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2992 -- raise_application_error(-20001,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment ID ' ||I.ASSIGNMENT_ID||' as on '||L_PENSION_ENTRY_START);
2993 populate_run_msg(p_assignment_action_id,'The Pension Element '||P_ELEMENT_TYPE_NAME||' does not have an eligible link to the Assignment Number ' ||NVL(get_assg_number(I.ASSIGNMENT_ID),'N/A')||' as on '
2994 ||L_PENSION_ENTRY_START||'.');
2995 RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
2996 ELSE
2997 --Insert Pension Element
2998 --Mode if -GB_VALIDATE Then Donot Insert the Element
2999 SAVEPOINT insert_element_transaction_agg;
3000 hr_utility.trace('VAlues Before .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
3001 HR_ENTRY_API.INSERT_ELEMENT_ENTRY(P_EFFECTIVE_START_DATE => L_PENSION_ENTRY_START, P_EFFECTIVE_END_DATE => V_EFF_END_DATE, P_ELEMENT_ENTRY_ID => V_ELEMENT_ENTRY_ID, P_ASSIGNMENT_ID => I.ASSIGNMENT_ID,
3002 P_ELEMENT_LINK_ID => P_ELEMENT_LINK_ID, P_CREATOR_TYPE => 'F', P_ENTRY_TYPE => 'E' );
3003 hr_utility.trace('VAlues After .1. Start'||L_PENSION_ENTRY_START||'.2. End '||V_EFF_END_DATE||'.3. Entry Id'||V_ELEMENT_ENTRY_ID);
3004 IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
3005 ROLLBACK TO insert_element_transaction_agg;
3006 END IF;--Rollback to the savepoint 'insert_element_transaction_agg' if not in Commit Mode.
3007 IF (V_EFF_END_DATE <G_END_OF_TIME) THEN
3008 hr_utility.trace('The Pension Element '||P_ELEMENT_TYPE_NAME||' is attached to the Assignment ID '||I.ASSIGNMENT_ID||' effective from '||L_PENSION_ENTRY_START||' to ' ||V_EFF_END_DATE||'.');
3009 populate_warn_msg(p_assignment_action_id,'The Pension Element '||P_ELEMENT_TYPE_NAME||' has been attached to the Assignment Number '||NVL(get_assg_number(I.ASSIGNMENT_ID),'N/A')||' effective from '||
3010 L_PENSION_ENTRY_START||' to ' ||V_EFF_END_DATE||'. Please review the reason for end date not being extended beyond '||V_EFF_END_DATE ||'.');
3011 END IF;
3012 END IF;
3013 END LOOP;
3014 END IF;--Aggregated
3015 --######################################################
3016 EXCEPTION
3017 WHEN OTHERS THEN
3018 hr_utility.trace('Error While Inserting Pension Element');
3019 hr_utility.trace('SQLERRM'||sqlerrm);
3020 RAISE;
3021 END INSERT_PENSION_ELEMENT;
3022 --------------------------------------------------------------------------------------------------
3023 /*
3024 (Aggregated) Active assignments under that employee will be processed in this procedure except the Main Entry.
3025 Main Entry Will be processed in the Enroll Pensions even before this Procedure is called.
3026 */
3027 PROCEDURE AGGREGATED(
3028 L_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE,
3029 L_QUALIFYING_SCHEME_NAME PQP_GB_PENSION_SCHEMES_V.ELEMENT_NAME%TYPE ,
3030 L_AGGREGATION_FLAG VARCHAR2,
3031 L_AUTO_ENROLLMENT_DATE DATE,
3032 L_QUALIFYING_SCHEME_EXISTS VARCHAR2 ,
3033 L_POSTPONEMENT_TYPE VARCHAR2,
3034 L_POSTPONEMENT_DATE DATE ,
3035 L_EMPLOYEE_CLASS_FOR_PENSION VARCHAR2,
3036 L_OPTIN_DATE DATE ,
3037 L_OPTOUT_DATE DATE ,
3038 L_MAIN_ENTRY VARCHAR2,
3039 L_EARNINGS NUMBER,
3040 L_OPTOUT_END_DATE DATE ,
3041 L_ELIGIBLE_JH_DATE DATE )
3042 IS
3043 -- Cursor to fetch all Active Aggregated assignments across payrolls
3044 CURSOR C_ALL_ASSIGNMENTS_AGG (L_PERSON_ID NUMBER)
3045 IS
3046 SELECT PAAF.PAYROLL_ID,
3047 PAAF.ASSIGNMENT_ID,
3048 PEEF.ELEMENT_ENTRY_ID,
3049 PAAF.ASSIGNMENT_NUMBER
3050 FROM PER_ALL_PEOPLE_F PAPF,
3051 PER_ALL_ASSIGNMENTS_F PAAF,
3052 PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
3053 PAY_ELEMENT_ENTRIES_F PEEF,
3054 PER_ASSIGNMENT_STATUS_TYPES PAST
3055 WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
3056 AND PAPF.PERSON_ID =L_PERSON_ID
3057 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
3058 -- AND PAST.PAY_SYSTEM_STATUS='P'
3059 AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
3060 AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
3061 AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
3062 AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
3063 AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID
3064 AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
3065 AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
3066 AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
3067 AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
3068 AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
3069 BEGIN
3070 hr_utility.trace('Inside the Procedure AGGREGATED');
3071 FOR I IN C_ALL_ASSIGNMENTS_AGG(L_PERSON_ID)
3072 LOOP
3073 P_PENSION_INPUT_VALUES_DUMMY := NULL ;
3074 hr_utility.trace('---------------------------------------------------------------------------------------------');
3075 OPEN C_PENSION_INPUT_VALUES(I.ELEMENT_ENTRY_ID);
3076 FETCH C_PENSION_INPUT_VALUES INTO P_PENSION_INPUT_VALUES_DUMMY;
3077 CLOSE C_PENSION_INPUT_VALUES;
3078 UPDATE_INFO_ELEMENT( I.ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
3079 G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID, L_AUTO_ENROLLMENT_DATE , G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID, L_QUALIFYING_SCHEME_EXISTS , G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,
3080 L_POSTPONEMENT_TYPE , G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID, L_POSTPONEMENT_DATE , G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID, L_EMPLOYEE_CLASS_FOR_PENSION , G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,
3081 L_OPTIN_DATE , G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID, L_OPTOUT_DATE , G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID, P_PENSION_INPUT_VALUES_DUMMY.G_MAIN_ENTRY -- Main Entry has to be retained as before
3082 , G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID, L_EARNINGS , G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID, L_OPTOUT_END_DATE , G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID ,
3083 /*Bug 14622818*/
3084 to_char (l_eligible_jh_date ,fnd_date.name_in_mask) );
3085 hr_utility.trace('---------------------------------------------------------------------------------------------');
3086 END LOOP;
3087 END;
3088 PROCEDURE WRITE_OUTPUT
3089 IS
3090 -- Cursor to fetch Completed records
3091 CURSOR get_asg_action_id
3092 IS
3093 SELECT DISTINCT paa.assignment_action_id asg_action_id,
3094 assignment_id
3095 FROM pay_payroll_actions ppa,
3096 pay_assignment_actions paa
3097 WHERE ppa.payroll_action_id = G_PAYROLL_ACTION_ID -- pact_id
3098 AND paa.payroll_action_id = ppa.payroll_action_id
3099 AND paa.action_status = 'C'
3100 --Bug 14492289 - Reporting EJH in separate Sections
3101 AND NOT EXISTS
3102 (SELECT 1
3103 FROM PAY_MESSAGE_LINES
3104 WHERE PAYROLL_ID = 123
3105 AND MESSAGE_LEVEL ='F'
3106 AND SOURCE_ID = PAA.ASSIGNMENT_ACTION_ID
3107 AND SOURCE_TYPE = 'A'
3108 )
3109 --Bug 14492289 - Reporting EJH in separate Sections
3110 ORDER BY assignment_id;
3111 -- Cursor to fetch Errored Records
3112 CURSOR get_asg_action_id_error
3113 IS
3114 SELECT DISTINCT paa.assignment_action_id asg_action_id,
3115 assignment_id
3116 FROM pay_payroll_actions ppa,
3117 pay_assignment_actions paa
3118 WHERE ppa.payroll_action_id = G_PAYROLL_ACTION_ID -- pact_id
3119 AND paa.payroll_action_id = ppa.payroll_action_id
3120 AND
3121 --Bug 14492289 - Reporting EJH in separate Sections
3122 ( paa.action_status = 'E'
3123 OR (paa.action_status = 'C'
3124 AND EXISTS
3125 (SELECT 1
3126 FROM PAY_MESSAGE_LINES
3127 WHERE PAYROLL_ID = 123
3128 AND MESSAGE_LEVEL ='F'
3129 AND SOURCE_ID = PAA.ASSIGNMENT_ACTION_ID
3130 AND SOURCE_TYPE = 'A'
3131 ) ) )
3132 --Bug 14492289 - Reporting EJH in separate Sections
3133 ORDER BY assignment_id;
3134 -- To Get Details for the assignment
3135 CURSOR get_assignment_info(p_assignment_id NUMBER,p_asg_actid NUMBER)
3136 IS
3137 SELECT PAI.ACTION_INFORMATION15 assignment_number,
3138 PAI.ACTION_INFORMATION14 full_name,
3139 NVL(PAI.ACTION_INFORMATION7,'NA') classification,
3140 NVL(TO_CHAR(fnd_date.canonical_to_date(PAI.ACTION_INFORMATION9)),'NA') auto_en_date
3141 FROM PAY_ACTION_INFORMATION PAI
3142 WHERE PAI.assignment_id =p_assignment_id
3143 AND PAI.ACTION_CONTEXT_ID =p_asg_actid
3144 AND PAI.ACTION_CONTEXT_TYPE='AAP';
3145 ---To Fetch Messages
3146 CURSOR get_messages(p_assignment_action_id NUMBER)
3147 IS
3148 SELECT DISTINCT line_text
3149 FROM pay_message_lines
3150 WHERE source_id = p_assignment_action_id
3151 AND payroll_id = 123;
3152 --To Fetch Warning Messages
3153 CURSOR get_warn_messages(p_assignment_action_id NUMBER)
3154 IS
3155 SELECT DISTINCT line_text
3156 FROM pay_message_lines
3157 WHERE source_id = p_assignment_action_id
3158 AND payroll_id = 111;
3159 l_assgn_number per_all_assignments_f.assignment_number%type;
3160 BEGIN
3161 fnd_file.put_line(fnd_file.OUTPUT,'ASSIGNMENTS PROCESSED SUCCESSFULLY');
3162 fnd_file.put_line(fnd_file.OUTPUT,'----------------------------------');
3163 --fnd_file.put_line(fnd_file.OUTPUT,'-----------------------------------');
3164 fnd_file.put_line(fnd_file.OUTPUT,rpad('ASSIGNMENT NUMBER',20)|| rpad('EMPLOYEE NAME',50)|| rpad('EMPLOYEE CLASSIFICATION',30)|| rpad('AUTO-ENROLMENT DATE',25));
3165 fnd_file.put_line(fnd_file.OUTPUT,'-----------------------------------------------------------------------------------------------------------------------------');
3166 FOR I IN get_asg_action_id
3167 LOOP
3168 FOR J IN get_assignment_info(I.assignment_id,I.asg_action_id)
3169 LOOP
3170 fnd_file.put_line(fnd_file.OUTPUT,rpad(J.assignment_number,20)|| rpad(J.full_name,50)|| rpad(J.classification,30)|| rpad(J.auto_en_date,25));
3171 /*FOR J IN get_warn_messages(I.asg_action_id)
3172 LOOP
3173 fnd_file.put_line(fnd_file.OUTPUT,lpad('WARNING: ',10)||J.line_text);
3174 --fnd_file.put_line(fnd_file.OUTPUT,lpad(J.line_text,125));
3175 END LOOP;
3176 */
3177 END LOOP;
3178 END LOOP;
3179 fnd_file.put_line(fnd_file.OUTPUT,' ');
3180 fnd_file.put_line(fnd_file.OUTPUT,' ');
3181 fnd_file.put_line(fnd_file.OUTPUT,'ASSIGNMENTS COMPLETED WITH WARNINGS');
3182 fnd_file.put_line(fnd_file.OUTPUT,'-----------------------------------');
3183 fnd_file.put_line(fnd_file.OUTPUT,'ASSIGNMENT NUMBER - MESSAGE');
3184 fnd_file.put_line(fnd_file.OUTPUT,'-------------------------------------');
3185 FOR I IN get_asg_action_id
3186 LOOP
3187 FOR J IN get_warn_messages(I.asg_action_id)
3188 LOOP
3189 OPEN get_assignment_number(I.assignment_id);
3190 FETCH get_assignment_number INTO l_assgn_number;
3191 CLOSE get_assignment_number;
3192 fnd_file.put_line(fnd_file.OUTPUT,rpad(NVL(l_assgn_number,'NA'),20)||J.line_text);
3193 END LOOP;
3194 END LOOP;
3195 fnd_file.put_line(fnd_file.OUTPUT,' ');
3196 fnd_file.put_line(fnd_file.OUTPUT,' ');
3197 fnd_file.put_line(fnd_file.OUTPUT,'ERRORED ASSIGNMENTS');
3198 fnd_file.put_line(fnd_file.OUTPUT,'-------------------');
3199 fnd_file.put_line(fnd_file.OUTPUT,'ASSIGNMENT NUMBER - MESSAGE');
3200 fnd_file.put_line(fnd_file.OUTPUT,'-------------------------------------');
3201 FOR I IN get_asg_action_id_error
3202 LOOP
3203 FOR J IN get_messages(I.asg_action_id)
3204 LOOP
3205 OPEN get_assignment_number(I.assignment_id);
3206 FETCH get_assignment_number INTO l_assgn_number;
3207 CLOSE get_assignment_number;
3208 fnd_file.put_line(fnd_file.OUTPUT,rpad(NVL(l_assgn_number,'NA'),20)||J.line_text);
3209 END LOOP;
3210 END LOOP;
3211 END;
3212 -----
3213 PROCEDURE archinit(
3214 p_payroll_action_id IN NUMBER)
3215 IS
3216 l_proc CONSTANT VARCHAR2(50) := ' archinit';
3217 l_payroll_id NUMBER;
3218 l_time_period_id NUMBER;
3219 ------------------------------
3220 -- Cursor to get Pension Scheme Name at Payroll Level if any
3221 CURSOR C_GET_PAYROLL_PENSION_INFO
3222 IS
3223 SELECT scl.SEGMENT19
3224 FROM pay_all_payrolls_f ppf,
3225 hr_soft_coding_keyflex scl
3226 WHERE ppf.payroll_id =G_PAYROLL_ID
3227 AND scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
3228 AND G_PRP_END_DATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
3229 ------------------------------------------------------------------------------------------
3230 --Bug 14370667
3231 -- Concurrent Program Parameters
3232 CURSOR CSR_ACT_CP_INFO
3233 IS
3234 SELECT TO_NUMBER(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS, 'PAYROLL_ID')) PAYROLL_ID,
3235 TO_NUMBER(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(LEGISLATIVE_PARAMETERS, 'PERIOD_ID')) TIME_PERIOD_ID,
3236 BUSINESS_GROUP_ID
3237 FROM PAY_PAYROLL_ACTIONS
3238 WHERE PAYROLL_ACTION_ID = p_payroll_action_id ;
3239 l_act_payroll_id NUMBER;
3240 l_act_time_period_id NUMBER;
3241 l_act_business_id NUMBER;
3242 --Bug 14370667
3243 BEGIN
3244 hr_utility.set_location('Entering '|| l_proc, 10);
3245 G_PAYROLL_ACTION_ID := p_payroll_action_id;
3246 FETCH_DATA();
3247 --Bug 14370667
3248 hr_utility.trace ('Debugging: Before 14370667 Updating');
3249 OPEN CSR_ACT_CP_INFO;
3250 FETCH CSR_ACT_CP_INFO
3251 INTO l_act_payroll_id,
3252 l_act_time_period_id,
3253 l_act_business_id;
3254 CLOSE CSR_ACT_CP_INFO;
3255 hr_utility.trace('Debugging - l_act_payroll_id: '||l_act_payroll_id);
3256 hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3257 hr_utility.trace('Debugging - l_act_business_id: '||l_act_business_id);
3258 UPDATE pay_payroll_actions
3259 SET time_period_id = l_act_time_period_id ,
3260 payroll_id = l_act_payroll_id ,
3261 date_earned = G_PAYROLL_END_DATE ,
3262 effective_date = G_PAYROLL_DATE_PAID
3263 WHERE payroll_action_id = p_payroll_action_id
3264 AND BUSINESS_GROUP_ID =l_act_business_id;
3265 hr_utility.trace ('Debugging: After 14370667 Updating');
3266 --Bug 14370667
3267 -- Included Payroll Level
3268 -- Get Default Pension Scheme --Payroll Level
3269 OPEN C_GET_PAYROLL_PENSION_INFO;
3270 FETCH C_GET_PAYROLL_PENSION_INFO INTO G_DEFAULT_SCHEME_NAME;
3271 CLOSE C_GET_PAYROLL_PENSION_INFO;
3272 hr_utility.trace('PAYROLL Level Scheme Name::'||G_DEFAULT_SCHEME_NAME );
3273 --Fetch Data() would have assigned the default details to the globals
3274 --If there is any at Payroll Level it will be over-ridden here
3275 IF (G_DEFAULT_SCHEME_NAME IS NOT NULL) THEN
3276 OPEN C_GET_PENSION_ELEMENTS(G_DEFAULT_SCHEME_NAME);
3277 FETCH C_GET_PENSION_ELEMENTS
3278 INTO G_DEFAULT_SCH_ELEMENT_NAME,
3279 G_EMPLOYER_COMPONENT;
3280 CLOSE C_GET_PENSION_ELEMENTS;
3281 hr_utility.trace('PAYROLL Level Scheme Name Details ::'||G_DEFAULT_SCH_ELEMENT_NAME||' Employer::'||G_EMPLOYER_COMPONENT);
3282 END IF;
3283 -- Included Payroll Level
3284 --If Only at Default
3285 IF (G_DEFAULT_SCHEME_NAME IS NULL) THEN
3286 hr_utility.trace('No Default Scheme Found for the Payroll.');
3287 --Get the Default Pension Scheme
3288 OPEN C_GET_DEFAULT_PENSION_INFO ;
3289 FETCH C_GET_DEFAULT_PENSION_INFO
3290 INTO G_DEFAULT_SCHEME_NAME,
3291 G_DEFAULT_SCH_ELEMENT_NAME,
3292 G_EMPLOYER_COMPONENT;
3293 IF C_GET_DEFAULT_PENSION_INFO%NOTFOUND THEN
3294 G_DEF_FLAG :='Y';
3295 END IF;
3296 CLOSE C_GET_DEFAULT_PENSION_INFO;
3297 IF (G_DEF_FLAG='Y') THEN
3298 conv_configval_to_table(); -- All Configuration Values under Qualifying Schemes Will be saved into g_collecttab
3299 END IF;
3300 END IF;
3301 hr_utility.set_location('Leaving '|| l_proc, 10);
3302 EXCEPTION
3303 WHEN OTHERS THEN
3304 hr_utility.trace('Error occured in archinit');
3305 RAISE;
3306 END archinit;
3307 --
3308 PROCEDURE range_cursor(
3309 pactid IN NUMBER,
3310 sqlstr OUT NOCOPY VARCHAR2)
3311 IS
3312 l_proc CONSTANT VARCHAR2(35):= 'range_cursor';
3313 BEGIN
3314 hr_utility.set_location('Entering: '||l_proc,1);
3315 G_PAYROLL_ACTION_ID := pactid;
3316 FETCH_DATA();
3317 /* IF G_PENSION_STAGING_DATE IS NULL THEN
3318 fnd_file.put_line(fnd_file.OUTPUT,'Staging Date has not been Set.');
3319 RAISE_APPLICATION_ERROR (-20001,'Please enter a Staging Date.') ;
3320 END IF;
3321 IF (G_PRP_END_DATE < FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE)) THEN-- Staging Date
3322 fnd_file.put_line(fnd_file.OUTPUT,'Staging Date Not Yet Reached in this Pay Reference Period.');
3323 RAISE_APPLICATION_ERROR (-20001,'Please check the Staging Date / Default Pension Scheme if they are properly set up.') ;
3324 END IF;-- Staging Date
3325 bug 16178067 */
3326 /* IF G_BUSINESS_GROUP_ID IS NOT NULL AND G_PAYROLL_ID IS NOT NULL AND G_PRP_END_DATE IS NOT NULL THEN
3327 sqlstr := 'select distinct ppf.person_id ' || 'from per_people_f ppf, ' || 'pay_payroll_actions ppa, ' || 'per_all_assignments_f paaf, ' || 'per_assignment_status_types past ' ||
3328 'where ppa.payroll_action_id = :PAYROLL_ACTION_ID ' || ' and ppa.business_group_id = ' ||G_BUSINESS_GROUP_ID || ' and paaf.payroll_id = ' ||G_PAYROLL_ID || ' and paaf.person_id = ppf.person_id ' ||
3329 ' and paaf.assignment_status_type_id = past.assignment_status_type_id ' || 'and past.PER_SYSTEM_status=''ACTIVE_ASSIGN'' ' || 'and ' ||''''||
3330 G_PRP_END_DATE ||''''||' between paaf.effective_start_date and paaf.effective_end_date ' || 'order by ppf.person_id' ;
3331 ELSIF G_PAYROLL_ID IS NOT NULL THEN
3332 */ -- bug 14813352
3333 IF G_PAYROLL_ID IS NOT NULL THEN
3334 -- Payroll ID used in param, restrict by this.
3335 hr_utility.set_location(l_proc,20);
3336 sqlstr := 'select distinct paaf.person_id '|| 'from pay_payroll_actions ppa, '|| 'per_all_assignments_f paaf '|| 'where ppa.payroll_action_id = :payroll_action_id '||
3337 'and paaf.business_group_id + 0 = ppa.business_group_id '|| 'and paaf.payroll_id = '||G_PAYROLL_ID|| ' order by paaf.person_id';
3338 ELSE
3339 sqlstr := 'select distinct person_id '|| 'from per_people_f ppf, '|| 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '|| 'and ppa.business_group_id = ppf.business_group_id '||
3340 'order by ppf.person_id';
3341 END IF;
3342 hr_utility.trace(' Payroll Action Id : '||pactid);
3343 hr_utility.trace(' Range Cursor Statement : '||sqlstr);
3344 hr_utility.set_location(' Leaving: '||l_proc,100);
3345 /*
3346 OPEN C_GET_DEFAULT_PENSION_INFO ;
3347 FETCH C_GET_DEFAULT_PENSION_INFO
3348 INTO
3349 G_DEFAULT_SCHEME_NAME,
3350 G_DEFAULT_SCH_ELEMENT_NAME,
3351 G_EMPLOYER_COMPONENT;
3352 IF C_GET_DEFAULT_PENSION_INFO%NOTFOUND THEN
3353 --ERROR
3354 hr_utility.trace('No Default Pension Scheme has been set up.');
3355 fnd_file.put_line(fnd_file.OUTPUT,'No Default Pension Scheme has been set up.');
3356 RAISE_APPLICATION_ERROR(-20001,'No Default Pension Scheme has been set up.');
3357 END IF;
3358 CLOSE C_GET_DEFAULT_PENSION_INFO;
3359 */
3360 --Commented for the ER considering Eligiblity Rules
3361 EXCEPTION
3362 WHEN OTHERS THEN
3363 G_ERROR:='Y';
3364 RAISE;
3365 END range_cursor;
3366 --
3367 PROCEDURE action_creation(
3368 pactid IN NUMBER,
3369 stperson IN NUMBER,
3370 endperson IN NUMBER,
3371 chunk IN NUMBER)
3372 IS
3373 l_proc CONSTANT VARCHAR2(90):= 'action_creation';
3374 l_ass_act_id NUMBER;
3375 -- Cursor to fetch all Active assignments for the Payroll
3376 CURSOR C_ALL_ASSIGNMENTS_PAYROLL
3377 IS
3378 SELECT DISTINCT PAAF.ASSIGNMENT_ID
3379 FROM PER_ALL_ASSIGNMENTS_F PAAF ,
3380 PER_ASSIGNMENT_STATUS_TYPES PAST
3381 WHERE PAAF.PAYROLL_ID = G_PAYROLL_ID
3382 AND PAAF.BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID
3383 AND PAAF.PERSON_ID BETWEEN stperson AND endperson
3384 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
3385 -- AND PAST.PAY_SYSTEM_STATUS='P'
3386 AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
3387 AND PAAF.ASSIGNMENT_TYPE = 'E'
3388 -- AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
3389 AND G_PRP_END_DATE >= PAAF.EFFECTIVE_START_DATE
3390 AND G_PRP_START_DATE <= PAAF.EFFECTIVE_END_DATE
3391 ORDER BY ASSIGNMENT_ID ;
3392 BEGIN
3393 hr_utility.set_location('Entering: '||l_proc,1);
3394 G_PAYROLL_ACTION_ID := pactid;
3395 FETCH_DATA();
3396 hr_utility.trace('stperson '||stperson ||'endperson '||endperson);
3397 /*
3398 OPEN C_GET_DEFAULT_PENSION_INFO ;
3399 FETCH C_GET_DEFAULT_PENSION_INFO
3400 INTO
3401 L_DEFAULT_SCHEME_NAME,
3402 L_DEFAULT_SCH_ELEMENT_NAME,
3403 L_EMPLOYER_COMPONENT;
3404 IF C_GET_DEFAULT_PENSION_INFO%NOTFOUND THEN
3405 hr_utility.trace('No Default Scheme has been Setup.Please Set it up And then run the process again.');
3406 RAISE L_EXCEPTION;
3407 END IF;
3408 CLOSE C_GET_DEFAULT_PENSION_INFO;
3409 */
3410 /*--- Moved to Arch Init
3411 -- Included Payroll Level
3412 -- Get Default Pension Scheme --Payroll Level
3413 OPEN C_GET_PAYROLL_PENSION_INFO;
3414 FETCH C_GET_PAYROLL_PENSION_INFO INTO L_DEFAULT_SCHEME_NAME;
3415 CLOSE C_GET_PAYROLL_PENSION_INFO;
3416 hr_utility.trace('PAYROLL Level Scheme Name::'||L_DEFAULT_SCHEME_NAME );
3417 IF (L_DEFAULT_SCHEME_NAME IS NOT NULL) THEN
3418 OPEN C_GET_PENSION_ELEMENTS(L_DEFAULT_SCHEME_NAME);
3419 FETCH C_GET_PENSION_ELEMENTS
3420 INTO
3421 L_DEFAULT_SCH_ELEMENT_NAME,
3422 L_EMPLOYER_COMPONENT;
3423 CLOSE C_GET_PENSION_ELEMENTS;
3424 hr_utility.trace('PAYROLL Level Scheme Name Details ::'||L_DEFAULT_SCH_ELEMENT_NAME||' Employer::'||L_EMPLOYER_COMPONENT);
3425 END IF;
3426 ------------------------------------------
3427 IF (L_DEFAULT_SCHEME_NAME IS NULL) THEN
3428 hr_utility.trace('No Default Scheme Found for the Payroll.');
3429 --Get the Default Pension Scheme
3430 OPEN C_GET_DEFAULT_PENSION_INFO ;
3431 FETCH C_GET_DEFAULT_PENSION_INFO
3432 INTO
3433 L_DEFAULT_SCHEME_NAME,
3434 L_DEFAULT_SCH_ELEMENT_NAME,
3435 L_EMPLOYER_COMPONENT;
3436 CLOSE C_GET_DEFAULT_PENSION_INFO;
3437 END IF;
3438 -- Included Payroll Level
3439 G_DEFAULT_SCHEME_NAME := L_DEFAULT_SCHEME_NAME;
3440 G_DEFAULT_SCH_ELEMENT_NAME := L_DEFAULT_SCH_ELEMENT_NAME;
3441 G_EMPLOYER_COMPONENT := L_EMPLOYER_COMPONENT;
3442 --- Moved to Arch Init*/
3443 FOR ASG_REC IN C_ALL_ASSIGNMENTS_PAYROLL
3444 LOOP
3445 hr_utility.set_location('Creating assignment action for ' || asg_rec.assignment_id,30);
3446 SELECT pay_assignment_actions_s.nextval INTO l_ass_act_id FROM dual;
3447 --
3448 -- insert into pay_assignment_actions.
3449 hr_nonrun_asact.insact(l_ass_act_id, asg_rec.assignment_id, pactid, chunk, NULL);
3450 END LOOP;
3451 EXCEPTION
3452 /*
3453 WHEN L_EXCEPTION THEN
3454 hr_utility.trace('Please Check the Staging Date / Default Pension Scheme if they are properly Set up.');
3455 fnd_file.put_line(fnd_file.OUTPUT,' ');
3456 RAISE_APPLICATION_ERROR (-20001,'Please Check the Staging Date / Default Pension Scheme if they are properly Set up.') ;
3457 */
3458 WHEN OTHERS THEN
3459 RAISE_APPLICATION_ERROR (-20001,'Error Occured in Action Creation');
3460 END action_creation;
3461 --
3462 PROCEDURE deinitialization_code(
3463 pactid IN NUMBER)
3464 IS
3465 l_proc CONSTANT VARCHAR2(50) := 'deinitialization_code';
3466 xml_layout BOOLEAN;
3467 l_request_id NUMBER;
3468 l_mode VARCHAR2(30);
3469 BEGIN
3470 IF G_ERROR <>'Y' THEN
3471 hr_utility.set_location('Entering: '||l_proc,1);
3472 --this is a eText report, Spawn the BI Publisher process
3473 hr_utility.trace('Spawn the BI Publisher process');
3474 WRITE_OUTPUT();
3475 /* Commented for bug 14273214 as this values are needed to be viewed in the Payroll view Form
3476 UPDATE pay_payroll_actions
3477 SET time_period_id = null
3478 , payroll_id = null
3479 , date_earned = null
3480 WHERE payroll_action_id = pactid
3481 and BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID;
3482 hr_utility.trace ('Debugging: Before 14370667 Rolling Back');
3483 */
3484 hr_utility.trace('Debugging - G_PRP_START_DATE: '||G_PRP_START_DATE);
3485 hr_utility.trace('Debugging - G_PRP_END_DATE: '||G_PRP_END_DATE);
3486 hr_utility.trace('Debugging - G_PAYROLL_START_DATE: '||G_PAYROLL_START_DATE);
3487 hr_utility.trace('Debugging - G_PAYROLL_END_DATE: '||G_PAYROLL_END_DATE);
3488 hr_utility.trace('Debugging - G_EARN_START_DATE: '||G_EARN_START_DATE);
3489 hr_utility.trace('Debugging - G_EARN_END_DATE: '||G_EARN_END_DATE);
3490 ----------------------------SPAWNING--------------------------------------------
3491 --AUTO ENROLLED REPORT
3492 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBPAER','en','US','PDF');
3493 IF xml_layout = true THEN
3494 ---'GB_VALIDATE' Then Donot Update the Element
3495 --G_MODE='GB_VALIDATE_COMMIT')
3496 SELECT DECODE(G_MODE,'GB_VALIDATE','Validate Only','GB_VALIDATE_COMMIT','Validate and Commit','NA')
3497 INTO l_mode
3498 FROM DUAL;
3499 l_request_id := fnd_request.submit_request (application => 'PAY' ,program => 'PYGBPAER' ,argument1 => pactid ,argument2 =>fnd_date.date_to_canonical( G_PAYROLL_START_DATE) ,
3500 argument3 => fnd_date.date_to_canonical(G_PAYROLL_END_DATE) ,argument4 =>fnd_date.date_to_canonical( G_PRP_START_DATE) ,argument5 => fnd_date.date_to_canonical(G_PRP_END_DATE) ,
3501 argument6 => L_MODE ,argument7 => G_PAYROLL_NAME ,argument8 => G_PERIOD_NAME );
3502 -- Commit;
3503 IF l_request_id = 0 THEN
3504 hr_utility.set_location('Error spawning new process',1);
3505 END IF;
3506 END IF;
3507 --NOT AUTO ENROLLED REPORT
3508 xml_layout :=NULL;
3509 l_request_id :=NULL;
3510 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBPAENR','en','US','PDF');
3511 IF xml_layout = true THEN
3512 ---'GB_VALIDATE' Then Donot Update the Element
3513 --G_MODE='GB_VALIDATE_COMMIT')
3514 SELECT DECODE(G_MODE,'GB_VALIDATE','Validate Only','GB_VALIDATE_COMMIT','Validate and Commit','NA')
3515 INTO l_mode
3516 FROM DUAL;
3517 l_request_id := fnd_request.submit_request (application => 'PAY' ,program => 'PYGBPAENR' ,argument1 => pactid ,argument2 =>fnd_date.date_to_canonical( G_PAYROLL_START_DATE) ,
3518 argument3 => fnd_date.date_to_canonical(G_PAYROLL_END_DATE) ,argument4 =>fnd_date.date_to_canonical( G_PRP_START_DATE) ,argument5 => fnd_date.date_to_canonical(G_PRP_END_DATE) ,
3519 argument6 => L_MODE ,argument7 => G_PAYROLL_NAME ,argument8 => G_PERIOD_NAME );
3520 -- Commit;
3521 IF l_request_id = 0 THEN
3522 hr_utility.set_location('Error spawning new process',1);
3523 END IF;
3524 END IF;
3525 --Already Enrolled Employees (Not By Auto Enrollment)
3526 --NOT AUTO ENROLLED REPORT
3527 xml_layout :=NULL;
3528 l_request_id :=NULL;
3529 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBPAEQ','en','US','PDF');
3530 IF xml_layout = true THEN
3531 ---'GB_VALIDATE' Then Donot Update the Element
3532 --G_MODE='GB_VALIDATE_COMMIT')
3533 SELECT DECODE(G_MODE,'GB_VALIDATE','Validate Only','GB_VALIDATE_COMMIT','Validate and Commit','NA')
3534 INTO l_mode
3535 FROM DUAL;
3536 l_request_id := fnd_request.submit_request (application => 'PAY' ,program => 'PYGBPAEQ' ,argument1 => pactid ,argument2 =>fnd_date.date_to_canonical( G_PAYROLL_START_DATE) ,
3537 argument3 => fnd_date.date_to_canonical(G_PAYROLL_END_DATE) ,argument4 =>fnd_date.date_to_canonical( G_PRP_START_DATE) ,argument5 => fnd_date.date_to_canonical(G_PRP_END_DATE) ,
3538 argument6 => L_MODE ,argument7 => G_PAYROLL_NAME ,argument8 => G_PERIOD_NAME );
3539 -- Commit;
3540 IF l_request_id = 0 THEN
3541 hr_utility.set_location('Error spawning new process',1);
3542 END IF;
3543 END IF;
3544 ----------------------------SPAWNING--------------------------------------------
3545 hr_utility.set_location('Leaving: '||l_proc,999);
3546 END IF;
3547 END deinitialization_code;
3548
3549 FUNCTION CAL_STAGING_DATE_EARNINGS(
3550 P_ASSIGNMENT_ID NUMBER,
3551 P_AGGR_FLAG VARCHAR2 DEFAULT 'N',
3552 P_PERSON_ID NUMBER,
3553 P_ASSACTID NUMBER)
3554 --P_ELEMENT_ENTRY_ID NUMBER)
3555 RETURN NUMBER
3556 IS
3557 --To fetch the dates paid which do not have a payroll/assignment action for the assignment id as on period in which staging date falls
3558 -- to get prp_start_date , prp_end_date and regular_payment_date of period in which staging date falls
3559 CURSOR C_STAGING_PRP_PERIOD
3560 is
3561 select start_date , end_date , REGULAR_PAYMENT_DATE from per_time_periods where payroll_id = G_PAYROLL_ID and FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) between START_DATE and END_DATE;
3562
3563 CURSOR C_DATES_PAID_MISSED(L_PRP_END_DATE date, L_PRP_START_DATE date)
3564 IS
3565 SELECT TIME_PERIOD_ID,
3566 START_DATE,
3567 END_DATE,
3568 PAYROLL_ID,
3569 REGULAR_PAYMENT_DATE DATE_PAID
3570 FROM per_time_periods
3571 WHERE payroll_id IN
3572 (SELECT payroll_id
3573 FROM per_all_assignments_f paaf
3574 WHERE assignment_id = p_assignment_id
3575 AND L_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
3576 )
3577 AND regular_payment_date BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE
3578 AND regular_payment_date NOT IN
3579 (SELECT effective_date
3580 FROM pay_payroll_actions ppa ,
3581 pay_assignment_actions paa ,
3582 per_time_periods ptp
3583 WHERE ppa.action_type IN ('Q', 'R')
3584 AND ppa.payroll_action_id = paa.payroll_action_id
3585 AND paa.assignment_id = p_assignment_id
3586 AND ppa.time_period_id = ptp.time_period_id
3587 AND ppa.time_period_id IN
3588 (SELECT time_period_id
3589 FROM per_time_periods
3590 WHERE payroll_id IN
3591 (SELECT payroll_id
3592 FROM per_all_assignments_f paaf
3593 WHERE assignment_id = p_assignment_id
3594 AND L_PRP_END_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
3595 )
3596 AND regular_payment_date BETWEEN L_PRP_START_DATE AND L_PRP_END_DATE
3597 )
3598 );
3599 ------------------AGGREGATED
3600 CURSOR C_ASSIGNMENTS_AGG(L_PRP_END_DATE date)
3601 IS
3602 SELECT PAAF.PAYROLL_ID,
3603 PAAF.ASSIGNMENT_ID,
3604 PEEF.ELEMENT_ENTRY_ID
3605 FROM PER_ALL_PEOPLE_F PAPF,
3606 PER_ALL_ASSIGNMENTS_F PAAF,
3607 PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
3608 PAY_ELEMENT_ENTRIES_F PEEF,
3609 PER_ASSIGNMENT_STATUS_TYPES PAST
3610 WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
3611 AND PAPF.PERSON_ID =P_PERSON_ID
3612 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
3613 -- AND PAST.PAY_SYSTEM_STATUS='P'
3614 AND PAST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
3615 AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
3616 AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
3617 AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
3618 AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID
3619 AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
3620 AND L_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
3621 AND L_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
3622 AND L_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
3623 AND L_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
3624 -----------------------------
3625 L_SCREEN_ENTRY_VALUE PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%type;
3626 L_FORMULA_ID FF_FORMULAS_F.FORMULA_ID%type;
3627 L_STATUS_PROCES_ID NUMBER;
3628 L_TOTAL_EARNINGS_VALUE NUMBER :=0;
3629 L_INPUTS FF_EXEC.INPUTS_T;
3630 P_INPUTS FF_EXEC.INPUTS_T;
3631 L_OUTPUTS FF_EXEC.OUTPUTS_T;
3632 L_ASSIGNMENT_ACTION_ID NUMBER :=NULL;
3633 L_BALANCE_FLAG VARCHAR(2) := 'N';
3634 P_PAY_VALUE_IVID NUMBER;
3635 L_RESULT_RULE_NAME PAY_FORMULA_RESULT_RULES_F.RESULT_NAME%TYPE;
3636 Iteration NUMBER;
3637 L_TIME_PERIOD_ID NUMBER :=0;
3638 L_PRP_START_DATE date;
3639 L_PRP_END_DATE date;
3640 L_EFFECTIVE_DATE date;
3641 BEGIN
3642 hr_utility.trace('Inside CALCULATE_EARNINGS');
3643 hr_utility.trace('AGGREGATED?? '||P_AGGR_FLAG);
3644 hr_utility.trace('Person_id '||P_PERSON_ID);
3645 hr_utility.trace('P_AGGR_FLAG:'||P_AGGR_FLAG);
3646 hr_utility.trace('P_ASSIGNMENT_ID:'||P_ASSIGNMENT_ID);
3647 ---#######################################################################################################
3648 open C_STAGING_PRP_PERIOD;
3649 fetch C_STAGING_PRP_PERIOD into L_PRP_START_DATE, L_PRP_END_DATE, L_EFFECTIVE_DATE;
3650 close C_STAGING_PRP_PERIOD;
3651 IF (P_AGGR_FLAG='N') THEN
3652 --Fetch Value from Balance
3653 L_TOTAL_EARNINGS_VALUE :=CALCULATE_ASG_ACTIONS(P_ASSIGNMENT_ID,P_ASSACTID,L_BALANCE_FLAG,NULL);
3654 HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
3655 hr_utility.trace('L_BALANCE_FLAG: '||L_BALANCE_FLAG);
3656 IF (L_BALANCE_FLAG ='Y') THEN -- Balance(s) Found --
3657 /*To handle Payroll Change and if the new payroll
3658 has no payroll-assg actions and if the previous payroll has payroll/assg
3659 actions within the PRP*/
3660 hr_utility.trace('To Fetch datepaids which donot have an assignment action');
3661 FOR L_DATE_PAID_MISSED IN C_DATES_PAID_MISSED(L_PRP_END_DATE,L_PRP_START_DATE)
3662 LOOP
3663 hr_utility.trace('The Missed Date Paid :'||L_DATE_PAID_MISSED.DATE_PAID);
3664 hr_utility.trace('The Corresponding Date Earned :'||L_DATE_PAID_MISSED.END_DATE);
3665 FOR I IN C_ELEMENT_DETAILS(L_DATE_PAID_MISSED.END_DATE,P_ASSIGNMENT_ID,NULL)
3666 LOOP
3667 L_SCREEN_ENTRY_VALUE := NULL;
3668 L_FORMULA_ID := NULL;
3669 p_pay_value_ivid := NULL;
3670 L_STATUS_PROCES_ID := NULL;
3671 l_result_rule_name := NULL;
3672 OPEN C_SCREEN_ENTRY_VALUE(I.ELEMENT_ENTRY_ID,L_DATE_PAID_MISSED.END_DATE);
3673 FETCH C_SCREEN_ENTRY_VALUE INTO L_SCREEN_ENTRY_VALUE;
3674 CLOSE C_SCREEN_ENTRY_VALUE;
3675 IF L_SCREEN_ENTRY_VALUE IS NULL THEN
3676 OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,L_DATE_PAID_MISSED.END_DATE);
3677 FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
3678 CLOSE C_PENSIONS_FORMULA_ID;
3679 IF L_FORMULA_ID IS NOT NULL THEN
3680 HR_UTILITY.trace('Formula Id Not Null');
3681 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3682 HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3683 END IF;
3684 ELSE --Screen Entry Value not Null
3685 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3686 HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3687 END IF;
3688 END LOOP;
3689 L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_DATE_PAID_MISSED.END_DATE,P_assactid,NULL);
3690 HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3691 END LOOP;
3692 END IF;--Balance Found
3693 --No Balance Found
3694 IF (L_BALANCE_FLAG ='N') THEN
3695 FOR I IN C_ELEMENT_DETAILS(L_EFFECTIVE_DATE,P_ASSIGNMENT_ID,NULL)
3696 LOOP
3697 L_SCREEN_ENTRY_VALUE := NULL;
3698 L_FORMULA_ID := NULL;
3699 p_pay_value_ivid := NULL;
3700 L_STATUS_PROCES_ID := NULL;
3701 l_result_rule_name := NULL;
3702 OPEN C_SCREEN_ENTRY_VALUE(I.ELEMENT_ENTRY_ID,L_EFFECTIVE_DATE);
3703 FETCH C_SCREEN_ENTRY_VALUE INTO L_SCREEN_ENTRY_VALUE;
3704 CLOSE C_SCREEN_ENTRY_VALUE;
3705 IF L_SCREEN_ENTRY_VALUE IS NULL THEN
3706 OPEN C_PENSIONS_FORMULA_ID(I.ELEMENT_TYPE_ID,L_EFFECTIVE_DATE);
3707 FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
3708 CLOSE C_PENSIONS_FORMULA_ID;
3709 IF L_FORMULA_ID IS NOT NULL THEN
3710 HR_UTILITY.trace('Formula Id Not Null');
3711 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(P_ASSIGNMENT_ID, I.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3712 HR_UTILITY.trace('L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3713 END IF;
3714 ELSE --Screen Entry Value not Null
3715 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3716 HR_UTILITY.trace(' Screen value Not Null :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3717 END IF;
3718 END LOOP;
3719 L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(P_ASSIGNMENT_ID,L_EFFECTIVE_DATE,P_assactid,NULL);
3720 HR_UTILITY.trace('Add Information Element :: L_TOTAL_EARNINGS_VALUE :'||L_TOTAL_EARNINGS_VALUE);
3721 END IF;--Balance Flag
3722 END IF;
3723 ---#######################################################################################################
3724 --AGGREGATION
3725 ---#######################################################################################################
3726 IF (P_AGGR_FLAG='Y') THEN
3727 HR_UTILITY.trace('Aggregation Case - Calculate_earnings');
3728 FOR I IN C_ASSIGNMENTS_AGG(L_PRP_END_DATE)
3729 LOOP
3730 IF (I.PAYROLL_ID =G_PAYROLL_ID) THEN
3731 L_TOTAL_EARNINGS_VALUE:=L_TOTAL_EARNINGS_VALUE+CALCULATE_EARNINGS(I.ASSIGNMENT_ID,'N',P_PERSON_ID,G_EARN_END_DATE,p_assactid);
3732 HR_UTILITY.trace('Same Payroll ID - L_TOTAL_EARNINGS_VALUE : '||L_TOTAL_EARNINGS_VALUE);
3733 ELSE --Main Entry and this asg are of different payrolls
3734 FOR J IN C_EARN_PERIOD_DETAILS(I.PAYROLL_ID)
3735 LOOP
3736 L_BALANCE_FLAG :='N'; --Reset Flag
3737 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE +CALCULATE_ASG_ACTIONS (P_ASSIGNMENT_ID ,P_ASSACTID ,L_BALANCE_FLAG ,J.TIME_PERIOD_ID );
3738 hr_utility.trace('BALANCE Flag:'||L_BALANCE_FLAG);
3739 IF (L_BALANCE_FLAG ='N') THEN-- If Balance not found
3740 FOR X IN C_ELEMENT_DETAILS(J.END_DATE,I.ASSIGNMENT_ID,NULL)
3741 LOOP
3742 hr_utility.trace('Inside Different Payroll Aggregated Case--and balance not Found');
3743 L_SCREEN_ENTRY_VALUE := NULL;
3744 L_FORMULA_ID :=NULL;
3745 p_pay_value_ivid :=NULL;
3746 L_STATUS_PROCES_ID :=NULL;
3747 l_result_rule_name :=NULL;
3748 OPEN C_SCREEN_ENTRY_VALUE(X.ELEMENT_ENTRY_ID,J.END_DATE);
3749 FETCH C_SCREEN_ENTRY_VALUE INTO L_SCREEN_ENTRY_VALUE;
3750 CLOSE C_SCREEN_ENTRY_VALUE;
3751 IF L_SCREEN_ENTRY_VALUE IS NULL THEN
3752 OPEN C_PENSIONS_FORMULA_ID(X.ELEMENT_TYPE_ID,J.END_DATE);
3753 FETCH C_PENSIONS_FORMULA_ID INTO L_FORMULA_ID,L_STATUS_PROCES_ID;
3754 CLOSE C_PENSIONS_FORMULA_ID;
3755 END IF;
3756 IF L_FORMULA_ID IS NOT NULL THEN
3757 HR_UTILITY.trace(' Inside run Element FORMULA '||L_FORMULA_ID);
3758 -- setting the contexts
3759 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + EXEC_FORMULA(I.ASSIGNMENT_ID, X.ELEMENT_ENTRY_ID, L_STATUS_PROCES_ID, P_assactid, L_FORMULA_ID);
3760 HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3761 ELSE
3762 L_TOTAL_EARNINGS_VALUE := L_TOTAL_EARNINGS_VALUE + NVL(L_SCREEN_ENTRY_VALUE,0);
3763 hr_utility.trace('Else part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
3764 HR_UTILITY.trace(' calculated value'||L_TOTAL_EARNINGS_VALUE);
3765 END IF;
3766 END LOOP;
3767 L_TOTAL_EARNINGS_VALUE :=L_TOTAL_EARNINGS_VALUE+ calculate_earnings_info(I.ASSIGNMENT_ID,J.END_DATE,P_assactid,NULL);
3768 hr_utility.trace('Information part L_TOTAL_EARNINGS_VALUE:'||L_TOTAL_EARNINGS_VALUE);
3769 END IF;-- If Balance not found
3770 END LOOP;-- For every Date Paid in that Payroll
3771 END IF; -- Same Payroll Id as Main Entry
3772 END LOOP;
3773 END IF;
3774 HR_UTILITY.trace(' Total value from all Pension Element values = '||L_TOTAL_EARNINGS_VALUE);
3775 RETURN L_TOTAL_EARNINGS_VALUE;
3776 EXCEPTION
3777 WHEN OTHERS THEN
3778 HR_UTILITY.trace(SQLERRM);
3779 RAISE;
3780 END CAL_STAGING_DATE_EARNINGS;
3781
3782
3783 FUNCTION CHK_STAGING_DATE_EARNINGS(
3784 P_PENSIONABLE_EARNINGS NUMBER ,
3785 P_ASSIGNMENT_ID NUMBER,
3786 P_PERSON_ID NUMBER,
3787 P_AGE OUT NOCOPY NUMBER,
3788 P_REASON OUT NOCOPY VARCHAR2)
3789 RETURN NUMBER
3790 IS
3791 L_SEX PER_ALL_PEOPLE_F.SEX%type;
3792 L_AGE NUMBER;
3793 L_EMPLOYEE_STATUS_FOR_PENSION NUMBER;
3794 L_DATE_OF_BIRTH PER_ALL_PEOPLE_F.DATE_OF_BIRTH%type;
3795 L_NUM_DATE_BIRTH VARCHAR2(10);
3796 CURSOR C_AGE_SEX
3797 IS
3798 SELECT MONTHS_BETWEEN(FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),DATE_OF_BIRTH)/12,
3799 DATE_OF_BIRTH,
3800 SEX
3801 FROM PER_ALL_PEOPLE_F
3802 WHERE PERSON_ID = P_PERSON_ID;
3803
3804 ---To Fetch Service Start Date of the employee
3805 CURSOR C_SERVICE_START (P_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE)
3806 IS
3807 SELECT DATE_START
3808 FROM PER_PERIODS_OF_SERVICE
3809 WHERE PERSON_ID = P_PERSON_ID
3810 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
3811
3812
3813 N_STATE_PENSION_AGE NUMBER := 0;
3814 RETIRE_DATE DATE;
3815 G_AUTOTHRESHOLD NUMBER :=0;
3816 G_LOWER_THRESHOLD NUMBER :=0;
3817 L_SERVICE_START date;
3818 L_DOB_TT date;
3819 L_EFFECTIVE_DATE date;
3820 l_asg_start_date DATE;
3821 --To Fetch the Assignment Start Date
3822 Cursor c_asg_start_date is
3823 select EFFECTIVE_START_DATE from per_all_assignments_f
3824 where assignment_id = p_assignment_id
3825 and g_prp_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
3826 BEGIN
3827 P_REASON := ' ';
3828 OPEN C_AGE_SEX;
3829 FETCH C_AGE_SEX INTO L_AGE, L_DATE_OF_BIRTH, L_SEX ;
3830 CLOSE C_AGE_SEX;
3831 IF L_AGE >= 59 THEN
3832 --Pension age calculation needs to be done only if age >= 59
3833 IF L_SEX = 'M' THEN
3834 N_STATE_PENSION_AGE := 65;
3835 ELSIF L_SEX = 'F' THEN
3836 IF L_DATE_OF_BIRTH >= TO_DATE('1955/04/06','YYYY/MM/DD') THEN
3837 /* The retirement age for Employees born after 6th April 1955 is 65 */
3838 N_STATE_PENSION_AGE := 65;
3839 ELSIF L_DATE_OF_BIRTH >= TO_DATE('1950/04/06','YYYY/MM/DD') THEN
3840 /* For employee born between 6 April 1950 and 6th April 1955, the retirement date will be derived from the user table */
3841 /* Convert the Birth date of the employee to YYYYMMDD format. */
3842 L_NUM_DATE_BIRTH := TO_CHAR(L_DATE_OF_BIRTH,'YYYYMMDD');
3843 /* Pass the birth date and get the retire date corresponding to birth date. */
3844 -- Bug 14137856
3845 --RETIRE_DATE := HRUSERDT.GET_TABLE_VALUE(G_BUSINESS_GROUP_ID,'STATE_RETIREMENT_AGE_FOR_WOMEN','STATE_PENSION_AGE',L_NUM_DATE_BIRTH,G_PRP_END_DATE);
3846 RETIRE_DATE := TO_DATE(HRUSERDT.GET_TABLE_VALUE(G_BUSINESS_GROUP_ID,'STATE_RETIREMENT_AGE_FOR_WOMEN','STATE_PENSION_AGE',L_NUM_DATE_BIRTH,G_PRP_END_DATE),'YYYY/MM/DD');
3847 N_STATE_PENSION_AGE := ((RETIRE_DATE - L_DATE_OF_BIRTH)/365);
3848 END IF; --l_date_of_birth
3849 END IF; -- l_sex
3850 ELSE
3851 N_STATE_PENSION_AGE := 65;
3852 END IF; --Age >= 59
3853 /*
3854 Quarter
3855 Lunar Month
3856 Bi-Week
3857 Semi-Year
3858 */
3859 IF G_PAYROLL_PERIOD_TYPE = 'Week' THEN
3860 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_WEEKLY;
3861 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_WEEKLY_LT;
3862 ELSIF G_PAYROLL_PERIOD_TYPE = 'Calendar Month' THEN
3863 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY ;
3864 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT ;
3865 ELSIF G_PAYROLL_PERIOD_TYPE = 'Year' THEN
3866 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_ANNUAL;
3867 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_ANNUAL_LT;
3868 ELSIF G_PAYROLL_PERIOD_TYPE = 'Quarter' THEN
3869 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_QUARTERLY ;
3870 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_QUARTERLY_LT;
3871 ELSIF G_PAYROLL_PERIOD_TYPE = 'Lunar Month' THEN
3872 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_LUNAR ;
3873 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_LUNAR_LT;
3874 ELSIF G_PAYROLL_PERIOD_TYPE = 'Bi-Week' THEN
3875 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_BIWEEK;
3876 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_BIWEEK_LT;
3877 ELSIF G_PAYROLL_PERIOD_TYPE = 'Semi-Year' THEN
3878 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_SEMIYEAR;
3879 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_SEMIYEAR_LT ;
3880 ELSIF G_PAYROLL_PERIOD_TYPE = 'Bi-Month' THEN -- Monthly's Threshold multiplied by 2
3881 G_AUTOTHRESHOLD := G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY * 2;
3882 G_LOWER_THRESHOLD := G_GLOBAL_VALUE.G_QE_MONTHLY_LT * 2;
3883 END IF;
3884 hr_utility.trace('P_PENSIONABLE_EARNINGS:'||P_PENSIONABLE_EARNINGS);
3885 hr_utility.trace('G_AUTOTHRESHOLD:'||G_AUTOTHRESHOLD);
3886 hr_utility.trace('L_AGE:'||L_AGE);
3887 hr_utility.trace('N_STATE_PENSION_AGE:'||N_STATE_PENSION_AGE);
3888
3889
3890 /* 16409174 - PAE THRESHOLD CHANGES start*/
3891
3892 OPEN c_asg_start_date;
3893 FETCH c_asg_start_date INTO l_asg_start_date ;
3894 CLOSE c_asg_start_date;
3895 OPEN C_SERVICE_START (P_PERSON_ID) ;
3896 FETCH C_SERVICE_START INTO L_SERVICE_START;
3897 CLOSE C_SERVICE_START;
3898 -- 22nd Birthday
3899 L_DOB_TT := FETCH_START_DATE(L_DATE_OF_BIRTH);
3900
3901 IF ((L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) OR (L_SERVICE_START BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE)) THEN
3902 L_EFFECTIVE_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,l_asg_start_date);
3903 G_GLOBAL_VALUE := NULL;
3904 OPEN C_GET_GLOBAL_VALUE(L_EFFECTIVE_DATE);
3905 FETCH C_GET_GLOBAL_VALUE INTO G_GLOBAL_VALUE;
3906 CLOSE C_GET_GLOBAL_VALUE;
3907 hr_utility.trace('C_GET_GLOBAL_VALUE fetched is:'||G_GLOBAL_VALUE.G_QE_MONTHLY_LT||'-'||G_GLOBAL_VALUE.G_AUTO_ENROL_TRIG_MONTHLY);
3908 end if;
3909 /* 16409174 - PAE THRESHOLD CHANGES end*/
3910
3911 /*Bug 14645724: Age check criteria modified*/
3912 IF L_AGE >= 16 AND L_AGE < 75 THEN
3913 IF ((L_AGE BETWEEN 22 AND N_STATE_PENSION_AGE) AND (P_PENSIONABLE_EARNINGS >= G_AUTOTHRESHOLD)) THEN
3914 L_EMPLOYEE_STATUS_FOR_PENSION := 1;
3915 ELSIF ((L_AGE BETWEEN 16 AND 75) AND (P_PENSIONABLE_EARNINGS >= G_LOWER_THRESHOLD AND P_PENSIONABLE_EARNINGS < G_AUTOTHRESHOLD)) THEN
3916 L_EMPLOYEE_STATUS_FOR_PENSION := 2;
3917 P_REASON := P_REASON || ' This assignment is Non Eligible Job holder';
3918 ELSIF ((L_AGE BETWEEN 16 AND 22 OR L_AGE BETWEEN N_STATE_PENSION_AGE AND 75 ) AND (P_PENSIONABLE_EARNINGS >= G_AUTOTHRESHOLD)) THEN
3919 L_EMPLOYEE_STATUS_FOR_PENSION := 2;
3920 P_REASON := P_REASON || ' This assignment is Non Eligible Job holder';
3921 ELSE
3922 L_EMPLOYEE_STATUS_FOR_PENSION := 3;
3923 P_REASON := P_REASON || ' This assignment is worker';
3924 END IF;
3925 ELSE
3926 /*This criteria means employee should not be picked for assesment*/
3927 L_EMPLOYEE_STATUS_FOR_PENSION := 4;
3928 END IF;
3929 P_AGE := L_AGE;
3930 RETURN L_EMPLOYEE_STATUS_FOR_PENSION;
3931 END CHK_STAGING_DATE_EARNINGS;
3932
3933 --
3934 PROCEDURE archive_code(
3935 p_assactid IN NUMBER,
3936 p_effective_date IN DATE)
3937 IS
3938 l_proc CONSTANT VARCHAR2(35):= 'archive_code';
3939 ------------------------------------------------------------------------------------------
3940 ------------------------------------------------------------------------------------------
3941 ---To Fetch Service Start Date of the employee
3942 CURSOR C_SERVICE_START (P_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE)
3943 IS
3944 SELECT DATE_START
3945 FROM PER_PERIODS_OF_SERVICE
3946 WHERE PERSON_ID = P_PERSON_ID
3947 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
3948 ------------------------------------------------------------------------------------------
3949 ---To find If the Person reaches 22 Yrs. within the Payroll Period
3950 CURSOR C_AGE_BETWEEN_PAYROLL (P_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE)
3951 IS
3952 SELECT DATE_OF_BIRTH
3953 FROM PER_ALL_PEOPLE_F
3954 WHERE PERSON_ID = P_PERSON_ID
3955 AND G_PRP_END_DATE >= EFFECTIVE_START_DATE
3956 AND G_PRP_START_DATE <= EFFECTIVE_END_DATE;
3957 -- AND G_PRP_END_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3958 ------------------------------------------------------------------------------------------
3959 -- Cursor to Find if Any Qualifying Scheme Exists for the Employee
3960 /* -- Bug 14210213
3961 CURSOR C_QUALIFYING_SCHEME_EXISTS (P_ASSIGNMENT_ID PAY_ELEMENT_ENTRIES_F.ASSIGNMENT_ID%TYPE)
3962 IS
3963 SELECT PPSV.PENSION_SCHEME_NAME
3964 FROM PQP_GB_PENSION_SCHEMES_V PPSV ,
3965 PQP_CONFIGURATION_VALUES PCV ,
3966 PAY_ELEMENT_TYPES_F PETF ,
3967 PAY_ELEMENT_ENTRIES_F PEEF
3968 WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
3969 AND PCV.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
3970 AND PCV.PCV_INFORMATION2 = PPSV.ELEMENT_NAME
3971 AND PPSV.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
3972 AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
3973 -- AND PPSV.EMPLOYER_COMPONENT='Y'--Commented for checking
3974 AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
3975 AND G_PRP_END_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
3976 AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
3977 ORDER BY PETF.EFFECTIVE_START_DATE DESC;
3978 */
3979 CURSOR C_QUALIFYING_SCHEME_EXISTS (P_ASSIGNMENT_ID PAY_ELEMENT_ENTRIES_F.ASSIGNMENT_ID%TYPE)
3980 IS
3981 SELECT PCV.PCV_INFORMATION1
3982 FROM PQP_CONFIGURATION_VALUES PCV ,
3983 PAY_ELEMENT_TYPES_F PETF ,
3984 PAY_ELEMENT_ENTRIES_F PEEF
3985 WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
3986 AND PCV.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
3987 AND PCV.PCV_INFORMATION2 = PETF.ELEMENT_NAME
3988 AND PETF.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
3989 AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
3990 AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
3991 -- AND G_PRP_END_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
3992 -- AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
3993 AND G_PRP_END_DATE >= ALL(PETF.EFFECTIVE_START_DATE,PEEF.EFFECTIVE_START_DATE )
3994 AND G_PRP_START_DATE <= ALL(PETF.EFFECTIVE_END_DATE,PEEF.EFFECTIVE_END_DATE )
3995 ORDER BY PETF.EFFECTIVE_START_DATE DESC;
3996 ------------------------------------------------------------------------------------------
3997 -- Cursor to fetch the Assignment's Details for the Payroll
3998 CURSOR C_ASSIGNMENT_DETAILS
3999 IS
4000 SELECT PAAF.PERSON_ID ,PAAF.EFFECTIVE_START_DATE,
4001 PAAF.ASSIGNMENT_ID ,
4002 PEEF.ELEMENT_ENTRY_ID ,
4003 PAAF.ASSIGNMENT_NUMBER
4004 FROM PER_ALL_ASSIGNMENTS_F PAAF ,
4005 PAY_ELEMENT_ENTRIES_F PEEF,
4006 PAY_ASSIGNMENT_ACTIONS PAA
4007 WHERE PAAF.PAYROLL_ID = G_PAYROLL_ID
4008 AND PAAF.ASSIGNMENT_ID = PEEF.ASSIGNMENT_ID
4009 AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
4010 AND PEEF.ELEMENT_TYPE_ID = G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
4011 AND PAA.ASSIGNMENT_ACTION_ID = p_assactid
4012 AND G_PRP_END_DATE >= ALL(PAAF.EFFECTIVE_START_DATE,PEEF.EFFECTIVE_START_DATE )
4013 AND G_PRP_START_DATE <= ALL(PAAF.EFFECTIVE_END_DATE,PEEF.EFFECTIVE_END_DATE );
4014 -- AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
4015 -- AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE;
4016 ------------------------------------------------------------------------------------------
4017 -- Cursor to Fetch Person's Name
4018 CURSOR PERSON_NAME(P_PERSON_ID NUMBER)
4019 IS
4020 SELECT FULL_NAME
4021 FROM PER_ALL_PEOPLE_F
4022 WHERE PERSON_ID =P_PERSON_ID
4023 AND BUSINESS_GROUP_ID=G_BUSINESS_GROUP_ID
4024 AND G_PRP_END_DATE >= EFFECTIVE_START_DATE
4025 AND G_PRP_START_DATE <= EFFECTIVE_END_DATE;
4026 -- AND G_PRP_END_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
4027 --------------------------------------------------------------------------------------------
4028 /* Bug 14778657
4029 --Cursor to Check if the EJH Postponement has been applied for the assignment already
4030 CURSOR CHECK_POSTP_EJH(P_ELEMENT_ENTRY_ID NUMBER)
4031 IS
4032 SELECT 1
4033 FROM PAY_ELEMENT_ENTRY_VALUES_F
4034 WHERE ELEMENT_ENTRY_ID = P_ELEMENT_ENTRY_ID
4035 AND INPUT_VALUE_ID = G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID
4036 AND EFFECTIVE_START_DATE <= G_PRP_END_DATE
4037 AND SCREEN_ENTRY_VALUE = 'Eligible Job Holder Postponement';
4038 ------------------------------------------------------------------------------------------
4039
4040 --To get Previous Period Start Date
4041 CURSOR PREV_PER_DATE
4042 IS
4043 SELECT PTP.START_DATE
4044 FROM PER_TIME_PERIODS PTP
4045 WHERE (PTP.END_DATE, PTP.PAYROLL_ID) =
4046 (SELECT START_DATE - 1 ,
4047 PAYROLL_ID
4048 FROM PER_TIME_PERIODS PTP1
4049 WHERE PTP1.START_DATE = G_PRP_START_DATE
4050 AND PTP1.END_DATE = G_PRP_END_DATE
4051 AND PTP1.PAYROLL_ID = G_PAYROLL_ID
4052 );
4053 Bug 14778657 */
4054 ------------------------------------------------------------------------------------------
4055 CURSOR C_MAIN_ENTRY_CHECK(P_PERSON_ID NUMBER,P_ASSIGNMENT_ID NUMBER)
4056 IS
4057 SELECT PAAF.ASSIGNMENT_NUMBER
4058 FROM PER_ALL_PEOPLE_F PAPF,
4059 PER_ALL_ASSIGNMENTS_F PAAF,
4060 PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
4061 PAY_ELEMENT_ENTRIES_F PEEF,
4062 PER_ASSIGNMENT_STATUS_TYPES PAST
4063 WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
4064 AND PAPF.PERSON_ID =P_PERSON_ID
4065 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
4066 -- AND PAST.PAY_SYSTEM_STATUS='P'
4067 AND PAST.PER_SYSTEM_STATUS <>'TERM_ASSIGN'
4068 AND PEEF.ASSIGNMENT_ID =PAAF.ASSIGNMENT_ID
4069 AND PEEF.ELEMENT_TYPE_ID =G_ELEMENT_DETAILS.G_ELEMENT_TYPE_ID
4070 AND PEEVF.ELEMENT_ENTRY_ID =PEEF.ELEMENT_ENTRY_ID
4071 AND PEEVF.INPUT_VALUE_ID =G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID
4072 AND PEEVF.SCREEN_ENTRY_VALUE ='Y'
4073 AND PAAF.ASSIGNMENT_ID <> P_ASSIGNMENT_ID
4074 AND G_PRP_END_DATE >= all(PAPF.EFFECTIVE_START_DATE,PAAF.EFFECTIVE_START_DATE,PEEVF.EFFECTIVE_START_DATE,PEEF.EFFECTIVE_START_DATE )
4075 AND G_PRP_START_DATE <= all(PAPF.EFFECTIVE_END_DATE,PAAF.EFFECTIVE_END_DATE,PEEVF.EFFECTIVE_END_DATE,PEEF.EFFECTIVE_END_DATE);
4076 /* AND G_PRP_END_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
4077 AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
4078 AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
4079 AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
4080 */
4081 ------------------------------------------------------------------------------------------
4082 CURSOR C_QUALIFYING_SCHEME_VALID (P_QUAL_SCHEME_NAME PQP_GB_PENSION_SCHEMES_V.PENSION_SCHEME_NAME%TYPE)
4083 IS
4084 SELECT 1
4085 FROM PQP_CONFIGURATION_VALUES PCV
4086 WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
4087 AND PCV.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
4088 AND PCV.PCV_INFORMATION1 = P_QUAL_SCHEME_NAME;
4089 ------------------------------------------------------------------------------------------
4090 /*
4091 retunrs 0 if employee has never been in Qualifying Pensions Scheme as an EJH
4092 else returns no of times he has been in PQS as EJH
4093 */
4094 cursor c_ejh_history(P_ASSIGNMENT_ID number)
4095 is
4096 SELECT count (*)
4097 FROM pay_element_entry_values_f p1
4098 , pay_element_entry_values_f p2
4099 WHERE p1.element_entry_id = p2.element_entry_id
4100 AND p1.element_entry_id IN
4101 (
4102 SELECT element_entry_id
4103 FROM pay_element_entries_f
4104 WHERE element_type_id =
4105 (
4106 SELECT element_type_id
4107 FROM pay_element_types_f
4108 WHERE element_name LIKE 'Pensions Information'
4109 )
4110 AND assignment_id = P_ASSIGNMENT_ID
4111 )
4112 AND p1.input_value_id =
4113 (
4114 SELECT input_value_id
4115 FROM pay_input_values_f
4116 WHERE element_type_id =
4117 (
4118 SELECT element_type_id
4119 FROM pay_element_types_f
4120 WHERE element_name LIKE 'Pensions Information'
4121 )
4122 AND name = 'Pension Classification'
4123 )
4124 AND p2.input_value_id =
4125 (
4126 SELECT input_value_id
4127 FROM pay_input_values_f
4128 WHERE element_type_id =
4129 (
4130 SELECT element_type_id
4131 FROM pay_element_types_f
4132 WHERE element_name LIKE 'Pensions Information'
4133 )
4134 AND name = 'Qualifying scheme exists'
4135 )
4136 AND p1.effective_start_date = p2.effective_start_date
4137 AND p1.effective_end_date = p2.effective_end_date
4138 AND p1.screen_entry_value = 'ELIGIBLE JOB HOLDER'
4139 AND p2.screen_entry_value = 'Y';
4140
4141 /*
4142 Check if Qualifying Pensions Scheme exists for an employee on Staging Date
4143 If exists then returns Scheme name else returns null
4144 */
4145 CURSOR C_QPS_EXISTS_ON_STAGING_DATE (P_ASSIGNMENT_ID PAY_ELEMENT_ENTRIES_F.ASSIGNMENT_ID%TYPE)
4146 IS
4147 SELECT PCV.PCV_INFORMATION1
4148 FROM PQP_CONFIGURATION_VALUES PCV ,
4149 PAY_ELEMENT_TYPES_F PETF ,
4150 PAY_ELEMENT_ENTRIES_F PEEF
4151 WHERE PCV_INFORMATION_CATEGORY = 'PAY_GB_QUALIFIED_PENSIONS_INFO'
4152 AND PCV.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
4153 AND PCV.PCV_INFORMATION2 = PETF.ELEMENT_NAME
4154 AND PETF.BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID
4155 AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
4156 AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
4157 AND FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
4158 AND FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
4159 ORDER BY PETF.EFFECTIVE_START_DATE DESC;
4160
4161 /*
4162 CURSOR C_ERROR_MSG IS
4163 SELECT SUBSTR(line_text,1,240) FROM PAY_MESSAGE_LINES
4164 WHERE PAYROLL_ID = 123 AND MESSAGE_LEVEL ='F' AND SOURCE_ID = P_ASSACTID AND SOURCE_TYPE = 'A';
4165 */
4166 ---------------------------------------------------------------------------------------------
4167 -- to get pensions staging date at Payroll level
4168 CURSOR C_PAYROLL_PENSION_STAGING_DATE
4169 IS
4170 SELECT scl.SEGMENT20
4171 FROM pay_all_payrolls_f ppf,
4172 hr_soft_coding_keyflex scl
4173 WHERE ppf.payroll_id =G_PAYROLL_ID
4174 AND scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
4175 AND G_PRP_END_DATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
4176
4177 -- to get pensions staging date at Employee/Person level
4178 CURSOR C_EMP_PENSION_STAGING_DATE(P_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE)
4179 IS
4180 SELECT pei_information1
4181 FROM per_people_extra_info
4182 WHERE pei_information_category LIKE 'GB_PENSIONS_STAGING_DATE'
4183 AND person_id = P_PERSON_ID;
4184
4185 -- to get pensions staging date at Payee/Employer level
4186 CURSOR C_PAYEE_PENSION_STAGING_DATE
4187 IS
4188 SELECT UPPER(SUBSTR(TRIM(HOI.ORG_INFORMATION2),1,35)) PENSION_STAGING_DATE
4189 FROM PAY_PAYROLLS_F PPF,
4190 HR_SOFT_CODING_KEYFLEX HSCF,
4191 HR_ORGANIZATION_INFORMATION HOI
4192 WHERE PPF.PAYROLL_ID = G_PAYROLL_ID
4193 AND PPF.BUSINESS_GROUP_ID = HOI.ORGANIZATION_ID
4194 AND HOI.ORG_INFORMATION_CONTEXT = 'UK Pensions'
4195 AND NVL(HOI.ORG_INFORMATION10,'UK') = 'UK'
4196 AND PPF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
4197 AND HSCF.SEGMENT1 = HOI.ORG_INFORMATION1;
4198
4199 -- to get custom function name for given pensions scheme
4200 CURSOR C_VALIDATE_CONFIG_VALUE(P_CONFIGURATION_TYPE VARCHAR2, P_SCHEME_NAME PQP_CONFIGURATION_VALUES.PCV_INFORMATION1%TYPE )
4201 IS
4202 SELECT PCV_INFORMATION2
4203 FROM PQP_CONFIGURATION_VALUES
4204 WHERE PCV_INFORMATION_CATEGORY = P_CONFIGURATION_TYPE -- 'PAY_GB_ENROLMENT_CUSTOM_PROC'
4205 AND PCV_INFORMATION1 = P_SCHEME_NAME
4206 AND BUSINESS_GROUP_ID = G_BUSINESS_GROUP_ID;
4207
4208 ------------------------------------------------------------------------------------------
4209 --13 Input values of Pension Info Element
4210 L_QUALIFYING_SCHEME_NAME PQP_GB_PENSION_SCHEMES_V.ELEMENT_NAME%TYPE :=NULL;
4211 L_AGGREGATION_FLAG VARCHAR2(5) :=NULL;
4212 L_AUTO_ENROLLMENT_DATE DATE :=NULL;
4213 L_QUALIFYING_SCHEME_EXISTS VARCHAR2(5) :=NULL;
4214 L_POSTPONEMENT_TYPE VARCHAR2(100) :=NULL;--New
4215 L_POSTPONEMENT_DATE DATE :=NULL;
4216 L_EMPLOYEE_CLASS_FOR_PENSION VARCHAR2(100) :=NULL;--New
4217 L_OPTIN_DATE DATE :=NULL;--New
4218 L_OPTOUT_DATE DATE :=NULL;--New
4219 L_MAIN_ENTRY VARCHAR2(5) :=NULL;--New
4220 L_EARNINGS NUMBER :=0;
4221 L_OPTOUT_END_DATE DATE :=NULL;
4222 L_ELIGIBLE_JH_DATE DATE :=NULL;
4223 -- Other Local Variables
4224 L_MAX_POSTPO_DATE DATE;
4225 L_ASSIGN_NUM_CHECK PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE :=NULL;
4226 L_EMPLOYER_COMPONENT PQP_GB_PENSION_SCHEMES_V.ELEMENT_NAME%TYPE :=NULL;
4227 L_DEFAULT_SCHEME_NAME PQP_GB_PENSION_SCHEMES_V.ELEMENT_NAME%TYPE :=NULL;
4228 L_DEFAULT_SCH_ELEMENT_NAME PQP_GB_PENSION_SCHEMES_V.PENSION_SCHEME_NAME%TYPE :=NULL;
4229 L_AGE_START NUMBER :=0;
4230 L_AGE_END NUMBER :=0;
4231 L_DATE_OF_BIRTH DATE :=NULL;
4232 L_EMPLOYEE_STATUS_FOR_PENSION NUMBER;
4233 L_AGE NUMBER;
4234 L_REASON VARCHAR2(200);
4235 L_WORKER_POSTP_FLAG VARCHAR2(3) DEFAULT 'N';
4236 L_EJH_POSTP_FLAG VARCHAR2(3) DEFAULT 'N';
4237 L_SERVICE_START DATE :=NULL;
4238 L_TEMP VARCHAR2(30);
4239 L_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE :=NULL;
4240 L_ASSIGNMENT_ID PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE :=NULL;
4241 L_ELEMENT_ENTRY_ID PAY_ELEMENT_ENTRIES_F.ELEMENT_ENTRY_ID%TYPE :=NULL;
4242 -- L_PENSION_ENTRY_START DATE; -- Bug 14343133
4243 L_PENSION_ENTRY_END DATE;
4244 L_PENSION_ENTRY_ID NUMBER;
4245 L_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
4246 L_ASSIGNMENT_NUMBER VARCHAR2(30);
4247 L_NOMINAL_AUTO_ENROLL_DATE DATE;
4248 L_NOMINAL_FLAG VARCHAR(3) :='N';
4249 -- L_PREV_PER_START_DATE DATE;
4250 L_ACTION_ID NUMBER;
4251 L_OVN NUMBER;
4252 L_DOB_TT DATE;
4253 L_ER_SCHEME_NAME VARCHAR2(150);
4254 --L_ERR_MSG VARCHAR2(240);
4255 --Bug 14649941
4256 L_EARNINGS_TEMP NUMBER :=0;
4257 L_AGE_TEMP NUMBER :=0;
4258 L_FLAG_TEMP BOOLEAN := FALSE;
4259 l_history number;
4260 L_STAGING_DATE_EARNINGS number;
4261 L_PENSION_STATUS_SD number;
4262 l_qps_on_staging_date PQP_GB_PENSION_SCHEMES_V.ELEMENT_NAME%TYPE :=NULL;
4263 --Bug 14649941
4264 L_EARNINGS_OLD NUMBER :=0; -- bug 16102066
4265 L_EMPLOYEE_PENSION_CLASS_OLD VARCHAR2(100) :=NULL;--bug 16102066
4266 L_PENSION_STAGING_DATE_TEMP HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2%TYPE; -- bug 16178067
4267 -- bug 14380856
4268 --Bug 16394694
4269 l_asg_start_date Date;
4270 --Bug 16394694
4271 L_CUSTOM_PROCEDURE PQP_CONFIGURATION_VALUES.PCV_INFORMATION2%type := null; -- ER 14380856
4272 L_STMT varchar2(200) := null; -- ER 14380856
4273 l_status number :=2; -- ER 14380856
4274 l_custom_eff_date date;-- ER 14380856
4275 L_EXCEPTION_NO_ERROR EXCEPTION;-- Nothing to be Done. Just Skip the Assignment
4276 L_EXCEPTION EXCEPTION;-- Unsuccessful.
4277 L_EXCEPTION_UPDATE EXCEPTION;--Update the Pension Info Element and also archive the assignment details.
4278 L_EXCEPTION_UPDATE_INFO EXCEPTION;--Updates ONLY the Pension Info Element - Does not archive the data.
4279 BEGIN
4280 hr_utility.trace('ARCHIVE CODE');
4281 BEGIN -- Inner Section
4282 --- Get The Assignment's Details
4283 OPEN C_ASSIGNMENT_DETAILS;
4284 FETCH C_ASSIGNMENT_DETAILS
4285 INTO L_PERSON_ID,l_asg_start_date,
4286 L_ASSIGNMENT_ID,
4287 L_ELEMENT_ENTRY_ID,
4288 L_ASSIGNMENT_NUMBER;
4289 IF C_ASSIGNMENT_DETAILS%NOTFOUND THEN
4290 hr_utility.trace('No Data(Element Entry) Found for Pension Information for the Assignment Action ID :'||p_assactid);
4291 populate_run_msg(p_assactid,'No Data Found for Pensions Information for the Assignment.');
4292 L_FLAG_TEMP := TRUE;
4293 END IF;
4294 CLOSE C_ASSIGNMENT_DETAILS;
4295 if L_FLAG_TEMP then
4296 RAISE L_EXCEPTION;
4297 end if;
4298
4299 -- Find Pensions Staging Date in order of -- 1.Employee 2.Payroll 3.Employer --bug 16178067
4300 open C_EMP_PENSION_STAGING_DATE(L_PERSON_ID);
4301 fetch C_EMP_PENSION_STAGING_DATE into L_PENSION_STAGING_DATE_TEMP;
4302 if C_EMP_PENSION_STAGING_DATE%FOUND and L_PENSION_STAGING_DATE_TEMP is not null then
4303 -- employee level pensions staging date
4304 G_PENSION_STAGING_DATE := L_PENSION_STAGING_DATE_TEMP;
4305 hr_utility.trace('Employee level G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4306 else
4307 L_PENSION_STAGING_DATE_TEMP := null;
4308 open C_PAYROLL_PENSION_STAGING_DATE;
4309 fetch C_PAYROLL_PENSION_STAGING_DATE into L_PENSION_STAGING_DATE_TEMP;
4310 if C_PAYROLL_PENSION_STAGING_DATE%FOUND and L_PENSION_STAGING_DATE_TEMP is not null then
4311 -- payroll_level pensions staging date
4312 G_PENSION_STAGING_DATE := L_PENSION_STAGING_DATE_TEMP;
4313 hr_utility.trace('Payroll level G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4314 else
4315 open C_PAYEE_PENSION_STAGING_DATE;
4316 fetch C_PAYEE_PENSION_STAGING_DATE into L_PENSION_STAGING_DATE_TEMP;
4317 if C_PAYEE_PENSION_STAGING_DATE%FOUND and L_PENSION_STAGING_DATE_TEMP is not null then
4318 -- employer level pensions staging date
4319 G_PENSION_STAGING_DATE := L_PENSION_STAGING_DATE_TEMP;
4320 hr_utility.trace('Payee level G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4321 else
4322 -- no pensions staging date found
4323 G_PENSION_STAGING_DATE := null;
4324 end if;
4325 close C_PAYEE_PENSION_STAGING_DATE;
4326 end if;
4327 close C_PAYROLL_PENSION_STAGING_DATE;
4328 end if;
4329 close C_EMP_PENSION_STAGING_DATE;
4330
4331 hr_utility.trace('Final G_PENSION_STAGING_DATE : '||G_PENSION_STAGING_DATE);
4332
4333 IF G_PENSION_STAGING_DATE IS NULL THEN
4334 populate_run_msg(p_assactid,'Staging Date has not been set for this employee at any level, hence this assignment is being skipped.');
4335 hr_utility.trace('Staging Date has not been Set.');
4336 RAISE L_EXCEPTION_NO_ERROR;
4337 END IF;
4338 IF (G_PRP_END_DATE < FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE)) THEN-- Staging Date
4339 populate_run_msg(p_assactid,'Staging Date not yet reached for this employee, hence this assignment is being skipped.');
4340 hr_utility.trace('Staging Date Not Yet Reached for this employee.');
4341 RAISE L_EXCEPTION_NO_ERROR;
4342 END IF;
4343
4344 -- Get the Full Name of the Employee
4345 OPEN PERSON_NAME(L_PERSON_ID);
4346 FETCH PERSON_NAME INTO L_FULL_NAME;
4347 CLOSE PERSON_NAME;
4348 --Get Entry Values for the Assignment's Pension Element
4349 P_PENSION_INPUT_VALUES := NULL ;
4350 hr_utility.trace('Outside C_PENSION_INPUT_VALUES --L_ELEMENT_ENTRY_ID Value: '||L_ELEMENT_ENTRY_ID);
4351 OPEN C_PENSION_INPUT_VALUES(L_ELEMENT_ENTRY_ID);
4352 hr_utility.trace('Inside C_PENSION_INPUT_VALUES --L_ELEMENT_ENTRY_ID Value: '||L_ELEMENT_ENTRY_ID);
4353 FETCH C_PENSION_INPUT_VALUES INTO P_PENSION_INPUT_VALUES;
4354 IF C_PENSION_INPUT_VALUES%NOTFOUND THEN
4355 hr_utility.trace('No Data( Entry Values) Found for Pension Information for the Assignment Action ID :'||p_assactid);
4356 populate_run_msg(p_assactid,'No Data Found for Pensions Information for the Assignment.');
4357 L_FLAG_TEMP := TRUE;
4358 END IF;
4359 CLOSE C_PENSION_INPUT_VALUES;
4360 if L_FLAG_TEMP then
4361 RAISE L_EXCEPTION;
4362 end if;
4363 ---CHECK
4364 hr_utility.trace('For the Assignment Number :'||L_ASSIGNMENT_NUMBER);
4365 hr_utility.trace('ENTRY VALUES CHECK:::::::');
4366 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME);
4367 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS);
4368 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4369 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS);
4370 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE);
4371 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4372 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS);
4373 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4374 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4375 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_MAIN_ENTRY);
4376 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP);
4377 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_OPT_OUT_END_DATE);
4378 hr_utility.trace('VAlUE:'||P_PENSION_INPUT_VALUES.G_ELIGIBLE_JH_DATE);
4379 ---CHECK
4380 --Assignment to Local Variables
4381 L_QUALIFYING_SCHEME_NAME :=P_PENSION_INPUT_VALUES.G_QUALIFYING_SCHEME_NAME;
4382 L_AGGREGATION_FLAG :=P_PENSION_INPUT_VALUES.G_AGG_EARNINGS_PENSIONS;
4383 L_AUTO_ENROLLMENT_DATE :=fnd_date.canonical_to_date(P_PENSION_INPUT_VALUES.G_AUTO_ENROLLMENT_DATE);
4384 L_QUALIFYING_SCHEME_EXISTS :=P_PENSION_INPUT_VALUES.G_QUALIFYING_PS_EXISTS;
4385 L_POSTPONEMENT_TYPE :=P_PENSION_INPUT_VALUES.G_POSTPONEMENT_TYPE;
4386 L_POSTPONEMENT_DATE :=fnd_date.canonical_to_date(P_PENSION_INPUT_VALUES.G_POSTPONEMENT_END_DATE);
4387 L_EMPLOYEE_CLASS_FOR_PENSION :=P_PENSION_INPUT_VALUES.G_EMPLOYEE_CLASS;
4388 L_OPTIN_DATE :=fnd_date.canonical_to_date(P_PENSION_INPUT_VALUES.G_OPT_IN_DATE);
4389 L_OPTOUT_DATE :=fnd_date.canonical_to_date(P_PENSION_INPUT_VALUES.G_OPT_OUT_DATE);
4390 L_MAIN_ENTRY :=P_PENSION_INPUT_VALUES.G_MAIN_ENTRY;
4391 L_EARNINGS :=P_PENSION_INPUT_VALUES.G_TOTAL_EARNING_PRP;
4392 L_OPTOUT_END_DATE :=fnd_date.canonical_to_date(P_PENSION_INPUT_VALUES.G_OPT_OUT_END_DATE);
4393 L_ELIGIBLE_JH_DATE :=fnd_date.canonical_to_date(P_PENSION_INPUT_VALUES.G_ELIGIBLE_JH_DATE);
4394 --New Worker or Staging Date
4395 OPEN C_SERVICE_START (L_PERSON_ID) ;
4396 FETCH C_SERVICE_START INTO L_SERVICE_START;
4397 CLOSE C_SERVICE_START;
4398 -- 22nd Birthday
4399 OPEN C_AGE_BETWEEN_PAYROLL(L_PERSON_ID) ;
4400 FETCH C_AGE_BETWEEN_PAYROLL INTO L_DATE_OF_BIRTH;
4401 CLOSE C_AGE_BETWEEN_PAYROLL;
4402 L_DOB_TT := FETCH_START_DATE(L_DATE_OF_BIRTH);
4403 hr_utility.trace('Twenty Second birthday::'||L_DOB_TT );
4404 hr_utility.trace('L_ELIGIBLE_JH_DATE Value::'||L_ELIGIBLE_JH_DATE);
4405 hr_utility.trace('L_SERVICE_START Value::'||L_SERVICE_START);
4406 hr_utility.trace('L_DOB_TT Value::'||L_DOB_TT);
4407 /* Bug fix 14581138
4408 IF G_DEF_FLAG <>'Y' THEN*/
4409 hr_utility.trace('G_DEF_FLAG Value::'||NVL(G_DEF_FLAG,'Null'));
4410 IF (NVL(G_DEF_FLAG,'N') <>'Y') THEN
4411 L_DEFAULT_SCHEME_NAME := G_DEFAULT_SCHEME_NAME;
4412 L_DEFAULT_SCH_ELEMENT_NAME := G_DEFAULT_SCH_ELEMENT_NAME;
4413 L_EMPLOYER_COMPONENT := G_EMPLOYER_COMPONENT;
4414 END IF;
4415 -- VALIDATIONS
4416 -- Check if Qualifying scheme is a Valid Qualifying scheme
4417 IF L_QUALIFYING_SCHEME_NAME IS NOT NULL THEN
4418 OPEN C_QUALIFYING_SCHEME_VALID(L_QUALIFYING_SCHEME_NAME);
4419 FETCH C_QUALIFYING_SCHEME_VALID INTO L_TEMP;
4420 IF C_QUALIFYING_SCHEME_VALID%NOTFOUND THEN
4421 hr_utility.trace('Qualifying Scheme Name entered for the assignment is not valid.');
4422 populate_run_msg(p_assactid,'Qualifying Scheme Name entered for the assignment is not valid.');
4423 RAISE L_EXCEPTION;
4424 END IF;
4425 CLOSE C_QUALIFYING_SCHEME_VALID;
4426 IF (NVL(L_QUALIFYING_SCHEME_EXISTS,'N') ='N') THEN
4427 hr_utility.trace('Qualifying Scheme Name is entered for the assignment, but Qualifying Scheme Exists is not set to Yes.');
4428 populate_run_msg(p_assactid,'Qualifying Scheme Name is entered for the assignment, but Qualifying Scheme Exists is not set to Yes.');
4429 RAISE L_EXCEPTION;
4430 END IF;
4431 END IF;
4432 hr_utility.trace('L_POSTPONEMENT_TYPE: '||L_POSTPONEMENT_TYPE);
4433 --Check if PP End Date is present without the PP Type
4434 IF L_POSTPONEMENT_DATE IS NOT NULL AND L_POSTPONEMENT_TYPE IS NULL THEN
4435 hr_utility.trace('Postponement End Date is entered for the assignment, but Postponement Type is not entered.');
4436 populate_run_msg(p_assactid,'Postponement End Date is entered for the assignment, but Postponement Type is not entered.');
4437 RAISE L_EXCEPTION;
4438 END IF;
4439 -- Check if Postponement End Date is more than three months - if not DB Scheme Postponement
4440 IF L_POSTPONEMENT_TYPE <> 'DB Scheme Postponement' THEN -- if not DB Scheme Postponement
4441 /*Bug 14622818 Modified the condition*/
4442 IF (L_ELIGIBLE_JH_DATE IS NOT NULL and l_eligible_jh_date <> hr_api.g_eot) THEN
4443 L_MAX_POSTPO_DATE := L_ELIGIBLE_JH_DATE;
4444 ELSE
4445 L_MAX_POSTPO_DATE := GREATEST(L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4446 END IF;
4447 hr_utility.trace('L_MAX_POSTPO_DATE Value::'||L_MAX_POSTPO_DATE);
4448 IF (ADD_MONTHS(L_MAX_POSTPO_DATE,3) - 1 ) < L_POSTPONEMENT_DATE THEN
4449 hr_utility.trace('More than three months postponement - error');
4450 hr_utility.trace('Postponement Period is longer than 3 months. Please Check the Postponement End Date of the assignment.');
4451 populate_run_msg(p_assactid,'Postponement Period is longer than 3 months.');
4452 RAISE L_EXCEPTION;
4453 ELSE
4454 hr_utility.trace('less than three months');
4455 END IF;
4456 END IF;-- if not DB Scheme Postponement
4457 --
4458 -- Check if Main Entry is set to Yes for more than one assignments for the same person
4459 IF (NVL(L_MAIN_ENTRY,'N') = 'Y') THEN
4460 OPEN C_MAIN_ENTRY_CHECK(L_PERSON_ID,L_ASSIGNMENT_ID);
4461 FETCH C_MAIN_ENTRY_CHECK INTO L_ASSIGN_NUM_CHECK;
4462 IF C_MAIN_ENTRY_CHECK%FOUND THEN
4463 hr_utility.trace('Main Entry for Aggregation is set to YES for more than one assignment for the same person.');
4464 populate_run_msg(p_assactid,'Main Entry for Aggregation is set to YES for more than one assignment for the same person.');
4465 hr_utility.trace('Assignment Number - '||L_ASSIGN_NUM_CHECK);
4466 RAISE L_EXCEPTION;
4467 END IF;
4468 CLOSE C_MAIN_ENTRY_CHECK;
4469 IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
4470 hr_utility.trace('Main Entry for Aggregation is set to YES, but Aggregate Earnings is not set to YES.');
4471 populate_run_msg(p_assactid,'Main Entry for Aggregation is set to YES, but Aggregate Earnings is not set to YES.');
4472 RAISE L_EXCEPTION;
4473 END IF;
4474 END IF;
4475
4476
4477 --DB Scheme Postponement validation - Only for EJH -- Bug 14649941
4478
4479 IF (L_POSTPONEMENT_DATE IS NULL) AND (L_POSTPONEMENT_TYPE IS NOT NULL) THEN
4480 hr_utility.trace('Postponement Type is entered, but Postponement End Date is not entered.');
4481 populate_run_msg(p_assactid,'Postponement Type is entered, but Postponement End Date is not entered.');
4482 RAISE L_EXCEPTION;
4483 END IF;
4484
4485 IF (L_POSTPONEMENT_DATE IS NOT NULL) AND (L_POSTPONEMENT_TYPE IS NULL) THEN
4486 hr_utility.trace('Postponement End Date is entered, but Postponement Type is not entered.');
4487 populate_run_msg(p_assactid,'Postponement End Date is entered, but Postponement Type is not entered.');
4488 RAISE L_EXCEPTION;
4489 END IF;
4490
4491 IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME)= G_END_OF_TIME and L_EMPLOYEE_CLASS_FOR_PENSION = 'Eligible Job Holder' THEN
4492 hr_utility.trace('Pension Classification is set to Eligible Job Holder, but Eligible Job Holder Date is not set correctly.');
4493 populate_run_msg(p_assactid,'Pension Classification is set to Eligible Job Holder, but Eligible Job Holder Date is not set correctly.');
4494 RAISE L_EXCEPTION;
4495 END IF;
4496
4497 --DB Scheme Postponement validation - Only for EJH and only if the postponement is still prevailing in the PRP
4498 -- IF the postponement is over then the validation is not required
4499 IF (L_POSTPONEMENT_TYPE = 'DB Scheme Postponement') THEN --DB Postponement
4500 IF L_ELIGIBLE_JH_DATE IS NULL AND L_POSTPONEMENT_DATE >=G_PRP_END_DATE THEN -- Never assessed before--First time run
4501
4502 IF (ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS_TEMP,L_AGE_TEMP,p_assactid))=1 THEN
4503 hr_utility.trace('DB Scheme Postponement is valid. Never assessed but Now assessed');
4504 -- update pensions classsification
4505 L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4506 --L_ELIGIBLE_JH_DATE := HR_API.G_EOT;
4507 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4508 L_ELIGIBLE_JH_DATE := L_DOB_TT;
4509 ELSE
4510 L_ELIGIBLE_JH_DATE := G_PRP_START_DATE;
4511 END IF;
4512 RAISE L_EXCEPTION_UPDATE_INFO;
4513 ELSE
4514 populate_run_msg(p_assactid,'DB Scheme Postponement is set, but it is invalid as the assignment is not an Eligible Job Holder.');
4515 RAISE L_EXCEPTION;
4516 END IF;
4517 END IF;-- Never assessed before--First time run
4518
4519 IF L_ELIGIBLE_JH_DATE IS NOT NULL AND L_POSTPONEMENT_DATE >=G_PRP_END_DATE THEN -- Already assessed atleast once
4520 IF L_EMPLOYEE_CLASS_FOR_PENSION = 'Eligible Job Holder' THEN
4521 hr_utility.trace('DB Scheme Postponement is valid. - Already Assessed');
4522 --L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4523 RAISE L_EXCEPTION;
4524 ELSE
4525 hr_utility.trace('DB Scheme Postponement is set, but it is invalid as the assignment is not an Eligible Job Holder.');
4526 populate_run_msg(p_assactid,'DB Scheme Postponement is set, but it is invalid as the assignment is not an Eligible Job Holder.');
4527 END IF;
4528 END IF;-- Already assessed atleast once
4529 END IF;--DB Postponement
4530 --DB Scheme Postponement validation - Only for EJH -- Bug 14649941
4531 ----------- Logic STARTS
4532 hr_utility.trace('L_AGGREGATION_FLAG Value::'||L_AGGREGATION_FLAG);
4533 --A.E.D. Null
4534 IF (L_AUTO_ENROLLMENT_DATE) IS NULL THEN
4535 --OPT Out date Crossed
4536 -- IF (NVL(L_OPTOUT_DATE,G_END_OF_TIME) > G_PRP_END_DATE) THEN
4537 --Postponement Check
4538 hr_utility.trace('L_POSTPONEMENT_DATE Value::'||L_POSTPONEMENT_DATE);
4539 IF (NVL(L_POSTPONEMENT_DATE,G_PRP_START_DATE) <G_PRP_END_DATE) THEN
4540 --Main Entry
4541 hr_utility.trace('Qualifying Scheme Attached???'||L_QUALIFYING_SCHEME_EXISTS);
4542 --Check if It is a Main Entry , if not throw an warning that it has been skipped.
4543 IF (NVL(L_AGGREGATION_FLAG,'N')='Y' AND NVL(L_MAIN_ENTRY,'N') = 'N') THEN
4544 populate_warn_msg(p_assactid,'The assignment is under Aggregation. The Main Entry for Aggregation is not set to Yes, hence this assignment is being skipped.');
4545 END IF;
4546 --Check if It is a Main Entry , if not throw an warning that it has been skipped.
4547 IF (NVL(L_AGGREGATION_FLAG,'N') = 'N') OR (NVL(L_AGGREGATION_FLAG,'N')='Y' AND NVL(L_MAIN_ENTRY,'N') = 'Y') THEN
4548 --------------------
4549 --Scheme Already Exists
4550 IF (NVL(L_QUALIFYING_SCHEME_EXISTS,'N') ='N') THEN
4551 -- Find the Quailifying Scheme for the Assignment from element entries
4552 OPEN C_QUALIFYING_SCHEME_EXISTS (L_ASSIGNMENT_ID) ;
4553 FETCH C_QUALIFYING_SCHEME_EXISTS INTO L_QUALIFYING_SCHEME_NAME;
4554 IF C_QUALIFYING_SCHEME_EXISTS%FOUND THEN
4555 L_QUALIFYING_SCHEME_EXISTS := 'Y';
4556 L_NOMINAL_FLAG :='Y';
4557 -- RAISE L_EXCEPTION_UPDATE; -- Remove Later as Nominal Date of Enrollment has to be reported
4558 hr_utility.trace('The Assignment already has a Qualifying Scheme Attached');
4559 --Removed the fix for 16303795
4560 ELSE
4561 L_QUALIFYING_SCHEME_EXISTS := 'N';
4562 hr_utility.trace('The Assignment already doesnot have a Qualifying Scheme Attached');
4563 l_qps_on_staging_date := null;
4564 --chk if he was part of a QPS on staging date
4565 open C_QPS_EXISTS_ON_STAGING_DATE(L_ASSIGNMENT_ID);
4566 fetch C_QPS_EXISTS_ON_STAGING_DATE into l_qps_on_staging_date;
4567 close C_QPS_EXISTS_ON_STAGING_DATE;
4568 hr_utility.trace('chking if he was part of a QPS on staging date');
4569
4570 --if l_staging_date_pi > 0 then
4571 if (l_qps_on_staging_date is not null) then
4572 hr_utility.trace('Qualifying Pensions Scheme exists on Staging Date');
4573 L_STAGING_DATE_EARNINGS := TRUNC (CAL_STAGING_DATE_EARNINGS(L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_PERSON_ID,p_assactid)+ 0.004 , 2);
4574 hr_utility.trace('L_STAGING_DATE_EARNINGS : '||L_STAGING_DATE_EARNINGS);
4575 L_PENSION_STATUS_SD := CHK_STAGING_DATE_EARNINGS(L_STAGING_DATE_EARNINGS,L_ASSIGNMENT_ID, L_PERSON_ID,L_AGE,L_REASON);
4576 hr_utility.trace('L_PENSION_STATUS_SD : '||L_PENSION_STATUS_SD);
4577 if (L_PENSION_STATUS_SD = 1) then
4578 hr_utility.trace('Employee was in QPS as an EJH on staging date hence not processing employee');
4579 raise l_exception;
4580 end if;
4581 end if;
4582
4583 END IF;
4584 CLOSE C_QUALIFYING_SCHEME_EXISTS;
4585 ------------------------------------------------------------------------------------------------------
4586 --New Worker or Staging Date
4587 IF (L_NOMINAL_FLAG ='N') THEN --Nominal Check
4588 --TAX REF Level
4589 /*Bug 14622818 Modified the condition*/
4590 IF l_eligible_jh_date IS NULL then
4591 hr_utility.trace ('Eligible Job Holder Date is not already set, means employee assessed for the first time');
4592 l_eligible_jh_date := hr_api.g_eot;
4593 /*IF ((FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) OR ( L_SERVICE_START BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE))THEN*/
4594 IF(NVL(G_WORKER_POSTPONEMENT_RULE,'NONE')='NONE') THEN
4595 L_WORKER_POSTP_FLAG := 'N';
4596 ELSE
4597 l_postponement_date := C_PAYROLL_MAX_DATE(GREATEST(L_SERVICE_START,l_asg_start_date,FND_DATE.canonical_to_date( G_PENSION_STAGING_DATE) ),G_WORKER_POSTPONEMENT_RULE);
4598
4599 l_worker_postp_flag := 'Y';
4600
4601 IF l_postponement_date < g_prp_end_date THEN
4602 l_worker_postp_flag := 'N';
4603 L_POSTPONEMENT_TYPE :='Worker Postponement';
4604 --l_postponement_date := NULL;
4605
4606 END IF;
4607 End if;
4608 /* ELSIF (G_WORKER_POSTPONEMENT_RULE ='MAX_DEFER_PERIOD_START_DATE') THEN
4609 L_POSTPONEMENT_DATE := ADD_MONTHS(G_PRP_START_DATE,3)-1;
4610 IF ( FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE) BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4611 L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4612 ELSIF (L_SERVICE_START BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4613 L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (L_SERVICE_START);
4614 ELSE
4615 L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (G_PRP_START_DATE);
4616 END IF;
4617 l_postponement_date := c_payroll_max_date (greatest (l_service_start
4618 ,fnd_date.canonical_to_date (g_pension_staging_date)));
4619 L_WORKER_POSTP_FLAG := 'Y';
4620 IF l_postponement_date < g_prp_end_date THEN --Bug 14622818
4621 l_worker_postp_flag := 'N';
4622 l_postponement_date := NULL;
4623 END IF;
4624
4625 ELSIF (G_WORKER_POSTPONEMENT_RULE ='NEXT_PAYROLL_PERIOD_START_DATE') THEN
4626 l_postponement_date := ADD_MONTHS(GREATEST(L_SERVICE_START,FND_DATE.canonical_to_date( G_PENSION_STAGING_DATE) ),1)-1;
4627 l_worker_postp_flag := 'Y';
4628
4629 IF l_postponement_date < g_prp_end_date THEN--Bug 14622818
4630 l_worker_postp_flag := 'N';
4631 l_postponement_date := NULL;
4632 END IF;
4633
4634 END IF;
4635 */
4636 hr_utility.trace('POSTPONEMENT END_DATE NEW WORKER starting '||L_POSTPONEMENT_DATE );
4637 END IF;--TAX REF Level
4638
4639
4640 IF (L_WORKER_POSTP_FLAG = 'Y') THEN
4641 L_POSTPONEMENT_TYPE :='Worker Postponement';
4642
4643 --------------------------- bug 15913417 : to classify employee even if worker postponment is applied--------------------------------------------------------
4644
4645 L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4646 hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION Value :'||L_EMPLOYEE_STATUS_FOR_PENSION);
4647 hr_utility.trace('EARNINGS :'||L_EARNINGS);
4648 hr_utility.trace('AGE :'||L_AGE);
4649 --Employee Classififcation
4650 /*Bug 14645724:This check is for employee who are >=75 years of age or less than 16 years of age*/
4651 IF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='4') THEN
4652 RETURN;
4653 ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='3') THEN
4654 hr_utility.trace('Assignment is a Worker :');
4655 L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4656 -- RAISE L_EXCEPTION_UPDATE;
4657 ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4658 L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4659 hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4660 -- RAISE L_EXCEPTION_UPDATE;
4661 ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4662 L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4663 hr_utility.trace('Person Id'|| L_PERSON_ID);
4664 IF (L_ELIGIBLE_JH_DATE IS NULL ) THEN -- Never Found eligible
4665 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4666 L_ELIGIBLE_JH_DATE := L_DOB_TT;
4667 ELSE
4668 L_ELIGIBLE_JH_DATE := G_PRP_START_DATE;
4669 END IF;
4670 END IF;
4671 IF (L_ELIGIBLE_JH_DATE IS NOT NULL ) THEN -- Bug 13945549
4672 IF (G_PRP_START_DATE-1)=L_POSTPONEMENT_DATE THEN -- To check if Last Period was the period when the assignment was first assessed as EJH
4673 hr_utility.trace('One Day after the postponement end date , this PRP starts.');
4674 ELSE
4675 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4676 L_ELIGIBLE_JH_DATE := L_DOB_TT;
4677 ELSE
4678 L_ELIGIBLE_JH_DATE := G_PRP_START_DATE;
4679 END IF;
4680 END IF;
4681 END IF;
4682 END IF;
4683 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4684 RAISE L_EXCEPTION_UPDATE;
4685 END IF;
4686 END IF;--Nominal Check
4687 ---Assesment
4688 IF (L_WORKER_POSTP_FLAG ='N') THEN
4689 L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID, L_ASSIGNMENT_ID,NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4690 hr_utility.trace('L_EMPLOYEE_STATUS_FOR_PENSION Value :'||L_EMPLOYEE_STATUS_FOR_PENSION);
4691 hr_utility.trace('EARNINGS :'||L_EARNINGS);
4692 hr_utility.trace('AGE :'||L_AGE);
4693 open c_ejh_history(L_ASSIGNMENT_ID);
4694 fetch c_ejh_history into l_history;
4695 close c_ejh_history;
4696 if (l_history > 0) then
4697 hr_utility.trace('Ignoring employee as he has already been in QPS as an EJH');
4698 raise l_exception;
4699 else
4700 hr_utility.trace('Processing employee as he has never been in QPS as an EJH');
4701 --Employee Classififcation
4702 /*Bug 14645724:This check is for employee who are >=75 years of age or less than 16 years of age*/
4703 IF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='4') THEN
4704 RETURN;
4705 ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='3') THEN
4706 hr_utility.trace('Assignment is a Worker :');
4707 L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4708 RAISE L_EXCEPTION_UPDATE;
4709 ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4710 L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4711 hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4712 RAISE L_EXCEPTION_UPDATE;
4713 ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4714 L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4715 hr_utility.trace('Person Id'|| L_PERSON_ID);
4716 IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) = G_END_OF_TIME THEN -- Never Found eligible
4717 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4718 L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
4719 --L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4720 ELSE
4721 L_ELIGIBLE_JH_DATE := GREATEST(G_PRP_START_DATE,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
4722 --L_ELIGIBLE_JH_DATE := GREATEST(G_PRP_START_DATE,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4723 END IF;
4724 END IF;
4725 -- Found Eligible previously
4726 -- IF (L_ELIGIBLE_JH_DATE IS NOT NULL ) THEN -- Bug 13945549
4727 IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) <> G_END_OF_TIME THEN
4728 -- IF (G_PRP_START_DATE-1)=L_POSTPONEMENT_DATE THEN -- To check if Last Period was the period when the assignment was first assessed as EJH
4729 IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND L_POSTPONEMENT_TYPE = 'Eligible Job Holder Postponement' AND (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,
4730 L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE))-1)THEN
4731 hr_utility.trace('One Day after the postponement end date , this PRP starts.');
4732 -- ELSE
4733 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4734 L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
4735 --L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4736 ELSE
4737 L_ELIGIBLE_JH_DATE := GREATEST(G_PRP_START_DATE,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
4738 --L_ELIGIBLE_JH_DATE := GREATEST(G_PRP_START_DATE,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4739 END IF;
4740 END IF;
4741 END IF;
4742 IF (L_NOMINAL_FLAG ='N') THEN --Nominal Check
4743 ---------************************************************************************************
4744 --EJH POSTPONEMENT RULE
4745 --One Period Gap -- Atleast One Day Gap
4746 /*
4747 OPEN PREV_PER_DATE;
4748 FETCH PREV_PER_DATE INTO L_PREV_PER_START_DATE ;
4749 CLOSE PREV_PER_DATE;
4750 hr_utility.trace('CAREFUL!! Previous'||L_PREV_PER_START_DATE);
4751 hr_utility.trace('CAREFUL!! Postpo'||L_POSTPONEMENT_DATE);
4752 */
4753 -- IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< L_PREV_PER_START_DATE) THEN
4754 --Greatest() -- Needed in cases where EJH is retained from the previous run itself.
4755 hr_utility.trace('G_PRP_START_DATE:: '||G_PRP_START_DATE);
4756 hr_utility.trace('L_ELIGIBLE_JH_DATE:: '||L_ELIGIBLE_JH_DATE);
4757 hr_utility.trace('L_POSTPONEMENT_DATE:: '||L_POSTPONEMENT_DATE);
4758 IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),L_ELIGIBLE_JH_DATE)-1) THEN
4759 L_EJH_POSTP_FLAG := 'Y';
4760 hr_utility.trace('CAREFUL!! Worker Postp applied before One Day ');
4761 ELSIF L_POSTPONEMENT_DATE IS NULL THEN
4762 L_EJH_POSTP_FLAG := 'Y';
4763 hr_utility.trace('No Postponement present');
4764 END IF;
4765 --One Period gap -- Atleast One Day Gap
4766 IF (L_EJH_POSTP_FLAG = 'Y') THEN--One Month Check
4767 L_EJH_POSTP_FLAG := 'N';
4768 /*
4769 OPEN CHECK_POSTP_EJH (L_ELEMENT_ENTRY_ID);
4770 FETCH CHECK_POSTP_EJH INTO L_TEMP;
4771 IF CHECK_POSTP_EJH%NOTFOUND THEN-- Postponement Not Already Applied */
4772 IF(NVL(G_ELIGIBLE_POSTPONEMENT_RULE,'NONE')='NONE') THEN
4773 L_EJH_POSTP_FLAG := 'N';
4774 ELSIF (G_ELIGIBLE_POSTPONEMENT_RULE ='MAX_DEFER_PERIOD_START_DATE') AND (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4775 L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (GREATEST(L_DOB_TT,L_ELIGIBLE_JH_DATE,l_asg_start_date),G_ELIGIBLE_POSTPONEMENT_RULE);
4776 hr_utility.trace('Twenty Second birthday Inside'||L_DOB_TT );
4777 IF l_postponement_date >= g_prp_end_date THEN
4778 L_POSTPONEMENT_TYPE := 'Eligible Job Holder Postponement';
4779 L_EJH_POSTP_FLAG := 'Y';
4780 END IF;
4781 IF l_postponement_date < g_prp_end_date THEN
4782 L_POSTPONEMENT_TYPE := NULL;
4783 L_EJH_POSTP_FLAG := 'N';
4784 END IF;
4785 --L_POSTPONEMENT_TYPE := 'Eligible Job Holder Postponement';
4786 --L_EJH_POSTP_FLAG := 'Y';
4787 ELSE
4788 L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (GREATEST(G_PRP_START_DATE,L_ELIGIBLE_JH_DATE,l_asg_start_date),G_ELIGIBLE_POSTPONEMENT_RULE);
4789 IF l_postponement_date >= g_prp_end_date THEN
4790 L_POSTPONEMENT_TYPE := 'Eligible Job Holder Postponement';
4791 L_EJH_POSTP_FLAG := 'Y';
4792 --L_POSTPONEMENT_TYPE := 'Eligible Job Holder Postponement';
4793 --L_EJH_POSTP_FLAG := 'Y';
4794 END IF;
4795 IF l_postponement_date < g_prp_end_date THEN
4796 L_POSTPONEMENT_TYPE := NULL;
4797 L_EJH_POSTP_FLAG := 'N';
4798 END IF;
4799 END IF;/*
4800 ELSIF (G_ELIGIBLE_POSTPONEMENT_RULE ='MAX_DEFER_PERIOD_START_DATE') THEN
4801 -- L_POSTPONEMENT_DATE := ADD_MONTHS(G_PRP_START_DATE,3)-1;
4802 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4803 L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (L_DOB_TT);
4804 hr_utility.trace('Twenty Second birthday Inside'||L_DOB_TT );
4805 ELSE
4806 L_POSTPONEMENT_DATE := C_PAYROLL_MAX_DATE (GREATEST(G_PRP_START_DATE,L_ELIGIBLE_JH_DATE));
4807 hr_utility.trace('Twenty Second birthday Outside'||L_DOB_TT );
4808 END IF;
4809 L_EJH_POSTP_FLAG := 'Y';
4810 L_POSTPONEMENT_TYPE :='Eligible Job Holder Postponement' ;
4811 ELSIF (G_ELIGIBLE_POSTPONEMENT_RULE ='NEXT_PAYROLL_PERIOD_START_DATE') THEN
4812 L_POSTPONEMENT_TYPE := 'Eligible Job Holder Postponement';
4813 L_POSTPONEMENT_DATE := G_PRP_END_DATE;
4814 L_EJH_POSTP_FLAG := 'Y';
4815 END IF;
4816 */
4817 hr_utility.trace('ELIGIBLE POSTPONEMENT END DATE '||L_POSTPONEMENT_DATE );
4818 /* END IF;-- Postponement Not Already Applied
4819 CLOSE CHECK_POSTP_EJH ; */
4820 END IF;--One Month Check
4821 --Check if it has been more than a period since Worker Postponement Ended
4822 --EJH POSTPONEMENT RULE
4823 ---------************************************************************************************
4824 END IF; --Nominal Check
4825 hr_utility.trace('NOMINAL FLAG::'||L_NOMINAL_FLAG);
4826 IF (L_NOMINAL_FLAG ='Y') THEN
4827 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4828 L_NOMINAL_AUTO_ENROLL_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4829 ELSE
4830 L_NOMINAL_AUTO_ENROLL_DATE := GREATEST(G_PRP_START_DATE,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4831 END IF;
4832 --Bug 14387977
4833 IF L_POSTPONEMENT_DATE IS NOT NULL THEN
4834 L_NOMINAL_AUTO_ENROLL_DATE := GREATEST(L_NOMINAL_AUTO_ENROLL_DATE,L_POSTPONEMENT_DATE+1);
4835 END IF;
4836 --Bug 14387977
4837 RAISE L_EXCEPTION_UPDATE;
4838 END IF;
4839 -- Check for Eligible Job Holder Postponement to be ADDED Here
4840 IF(L_EJH_POSTP_FLAG = 'N') AND (L_NOMINAL_FLAG ='N') THEN --EJH Postponement is not there
4841 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4842 L_AUTO_ENROLLMENT_DATE := GREATEST(L_DOB_TT,l_asg_start_date,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4843 ELSE
4844 L_AUTO_ENROLLMENT_DATE :=GREATEST(G_PRP_START_DATE,l_asg_start_date,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4845 END IF;
4846 -- Bug 14387977
4847 IF L_POSTPONEMENT_DATE IS NOT NULL THEN
4848 L_AUTO_ENROLLMENT_DATE := GREATEST(L_AUTO_ENROLLMENT_DATE,L_POSTPONEMENT_DATE+1);
4849 END IF;
4850 -- Bug 14387977
4851 L_OPTOUT_END_DATE :=ADD_MONTHS(L_AUTO_ENROLLMENT_DATE,1)-1;
4852 L_QUALIFYING_SCHEME_EXISTS := 'Y';
4853 L_QUALIFYING_SCHEME_NAME :=L_DEFAULT_SCHEME_NAME;
4854 -- L_PENSION_ENTRY_START :=G_PAYROLL_START_DATE; -- Bug 14343133
4855 L_PENSION_ENTRY_END :=G_END_OF_TIME;
4856 hr_utility.trace('PENSION ELEMENT INSERTION LOGIC');
4857 hr_utility.trace('Assignment is an ELIGIBLE JOB HOLDER :');
4858 --ER
4859 IF G_DEF_FLAG='Y' THEN
4860 -- Only People Group Check
4861 hr_utility.trace('As No defautl Scheme has been Set, Considering the eligibility criteria');
4862 IF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 2 THEN
4863 hr_utility.trace('ER Criteria Not mutually exclusive for People Group');
4864 --RAISE_APPLICATION_ERROR(-20001,'This assignment is eligible for more than one Qualifying Scheme.');
4865 populate_run_msg(p_assactid,'This assignment is eligible for more than one Qualifying Scheme.');
4866 RAISE G_EXCEPTION_NO_UPDATE; -- Just Archive for Error Cases.
4867 ELSIF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 0 THEN --No Matching criteria
4868 hr_utility.trace('This Assignment is an Eligible Job Holder but not eligible for any of the Qualifying Schemes.');
4869 --RAISE_APPLICATION_ERROR(-20001,'This assignment is an eligible jobholder but not eligible for any of the Qualifying Schemes.');
4870 populate_run_msg(p_assactid,'This assignment is an eligible jobholder but not eligible for any of the Qualifying Schemes.');
4871 RAISE G_EXCEPTION_NO_UPDATE;-- Just Archive for Error Cases.
4872 ELSIF group_check(L_ASSIGNMENT_ID,L_ER_SCHEME_NAME) = 1 THEN
4873 hr_utility.trace('Group Criteria Met');
4874 hr_utility.trace('ER Scheme Name:: '||L_ER_SCHEME_NAME);
4875 L_QUALIFYING_SCHEME_NAME:=L_ER_SCHEME_NAME;
4876 IF (L_ER_SCHEME_NAME IS NOT NULL) THEN
4877 OPEN C_GET_PENSION_ELEMENTS(L_ER_SCHEME_NAME);
4878 FETCH C_GET_PENSION_ELEMENTS
4879 INTO L_DEFAULT_SCH_ELEMENT_NAME,
4880 L_EMPLOYER_COMPONENT;
4881 CLOSE C_GET_PENSION_ELEMENTS;
4882 END IF;
4883 -- Only People Group Check
4884 END IF;
4885 END IF;
4886 --ER
4887 hr_utility.trace('L_DEFAULT_SCHEME_NAME Value::'||L_DEFAULT_SCHEME_NAME);
4888 hr_utility.trace('L_DEFAULT_SCH_ELEMENT_NAME Value::'||L_DEFAULT_SCH_ELEMENT_NAME);
4889 hr_utility.trace('L_EMPLOYER_COMPONENT Value::'||L_EMPLOYER_COMPONENT);
4890
4891 ------------------------------------- bug 14380856 -------------------------------------------------
4892
4893 hr_utility.trace('L_QUALIFYING_SCHEME_NAME :'||L_QUALIFYING_SCHEME_NAME);
4894
4895 if l_postponement_date < g_prp_end_date and g_prp_start_date < l_postponement_date then
4896 l_custom_eff_date := GREATEST(l_postponement_date+1,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4897 else
4898 l_custom_eff_date := GREATEST(L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE));
4899 end if;
4900
4901 open C_VALIDATE_CONFIG_VALUE('PAY_GB_ENROLMENT_CUSTOM_PROC',L_QUALIFYING_SCHEME_NAME);
4902 fetch C_VALIDATE_CONFIG_VALUE into l_custom_procedure;
4903 hr_utility.trace('l_custom_procedure :'||l_custom_procedure);
4904 if C_VALIDATE_CONFIG_VALUE%found and l_custom_procedure is not null then
4905 hr_utility.trace('Running Custom Procedure');
4906 hr_utility.trace('l_custom_eff_date : '||l_custom_eff_date);
4907 hr_utility.trace('l_assignment_id :'||l_assignment_id);
4908 begin
4909 SAVEPOINT custom_proc;
4910 l_stmt := 'BEGIN :status := '||l_custom_procedure||'( :pens_qual_scheme_name, :effective_start_date, :auto_enrollment_date, :assignment_id ); exception when others then :status := 0 ; END;' ;
4911 execute immediate l_stmt using in out l_status, l_qualifying_scheme_name, l_custom_eff_date, l_auto_enrollment_date, l_assignment_id;
4912 IF NOT (G_MODE='GB_VALIDATE_COMMIT') THEN
4913 ROLLBACK TO custom_proc;
4914 END IF;
4915 exception when others then
4916 hr_utility.trace('Error occured in execute immediate');
4917 l_status := 0;
4918 ROLLBACK TO custom_proc;
4919 end;
4920 end if;
4921 hr_utility.trace('l_status :'||l_status);
4922 if l_status = 0 then
4923 --error;
4924 hr_utility.trace('Error in custom procedure, hence skipping assignment');
4925 populate_run_msg(p_assactid,'Error in custom procedure, hence this assignment is being skipped.');
4926 elsif l_status = 2 then
4927
4928 IF(L_DEFAULT_SCH_ELEMENT_NAME IS NOT NULL) THEN
4929 --EMployee Element
4930 hr_utility.trace('EMPLOYEE ELEMENT INSERTION LOGIC');
4931 INSERT_PENSION_ELEMENT( V_EFF_START_DATE => L_AUTO_ENROLLMENT_DATE, V_EFF_END_DATE => L_PENSION_ENTRY_END, V_ELEMENT_ENTRY_ID => L_PENSION_ENTRY_ID , P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
4932 P_ELEMENT_TYPE_NAME=>L_DEFAULT_SCH_ELEMENT_NAME, P_AGGREGATION_FLAG=>L_AGGREGATION_FLAG,P_PERSON_ID=>L_PERSON_ID,P_ASSIGNMENT_ACTION_ID=>p_assactid );
4933 END IF;
4934 --Employer Element
4935 IF(L_EMPLOYER_COMPONENT IS NOT NULL) THEN
4936 hr_utility.trace('EMPLOYER ELEMENT INSERTION LOGIC');
4937 INSERT_PENSION_ELEMENT( V_EFF_START_DATE => L_AUTO_ENROLLMENT_DATE, V_EFF_END_DATE => L_PENSION_ENTRY_END, V_ELEMENT_ENTRY_ID => L_PENSION_ENTRY_ID , P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
4938 P_ELEMENT_TYPE_NAME=>L_EMPLOYER_COMPONENT, P_AGGREGATION_FLAG=>L_AGGREGATION_FLAG,P_PERSON_ID=>L_PERSON_ID,P_ASSIGNMENT_ACTION_ID=>p_assactid );
4939 ELSE
4940 hr_utility.trace('EMPLOYER ELEMENT INSERTION LOGIC - Employer Element is NULL');
4941 END IF;
4942
4943 end if;
4944
4945 update_ni_category_pension(L_ASSIGNMENT_ID,L_AUTO_ENROLLMENT_DATE,G_MODE);
4946 ----------------------------------------------------------------------------------------------
4947
4948 --Mid Period Enrolment
4949 IF (L_AUTO_ENROLLMENT_DATE > G_PRP_START_DATE AND L_AUTO_ENROLLMENT_DATE <=G_PRP_END_DATE) THEN
4950 populate_warn_msg(p_assactid,'The assignment has the Automatic Enrolment Date after the start date of the Pay Reference Period.');
4951 END IF;
4952 --Mid Period Enrolment
4953 END IF;--EJH Postponement is not there
4954 RAISE L_EXCEPTION_UPDATE;
4955 END IF;--Employee Classififcation
4956 end if;
4957 END IF;
4958 ---Assesment
4959 ------------------------------------------------------------------------------------------------------
4960 ELSIF (NVL(L_QUALIFYING_SCHEME_EXISTS,'N') ='Y') THEN --Scheme Already Exists
4961 IF (L_QUALIFYING_SCHEME_NAME IS NULL) THEN
4962 hr_utility.trace('Qualifying Scheme Exists is set to Yes, but Qualifying Scheme Name does not contain any value.');
4963 populate_run_msg(p_assactid,'Qualifying Scheme Exists is set to Yes, but Qualifying Scheme Name does not contain any value.');
4964 RAISE L_EXCEPTION;
4965 END IF;
4966 OPEN C_QUALIFYING_SCHEME_EXISTS (L_ASSIGNMENT_ID) ;
4967 FETCH C_QUALIFYING_SCHEME_EXISTS INTO L_QUALIFYING_SCHEME_NAME;
4968 IF C_QUALIFYING_SCHEME_EXISTS%NOTFOUND THEN
4969 hr_utility.trace('Assignment doesnot have a Qualifying Scheme attached, yet Qualifying Scheme Exists Flag is Set to Yes.');
4970 populate_run_msg(p_assactid,'Assignment does not have a Qualifying Scheme attached, yet Qualifying Scheme Exists flag is set to Yes.');
4971 RAISE L_EXCEPTION;
4972 END IF;
4973 CLOSE C_QUALIFYING_SCHEME_EXISTS;
4974 -- RAISE L_EXCEPTION_UPDATE; -- Remove Later as Nominal Date of Enrollment has to be reported
4975
4976
4977 hr_utility.trace('Assignment older classification to variable ');
4978 L_EMPLOYEE_PENSION_CLASS_OLD := L_EMPLOYEE_CLASS_FOR_PENSION;
4979 L_EARNINGS_OLD := L_EARNINGS ;
4980 hr_utility.trace('assessing employee again for change in status');
4981 L_EMPLOYEE_STATUS_FOR_PENSION := ASSESS_EMPLOYEE_PROCESS(L_PERSON_ID,L_ASSIGNMENT_ID,
4982 NVL(L_AGGREGATION_FLAG,'N'),L_EARNINGS,L_AGE,p_assactid);
4983 if (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='3') THEN
4984 hr_utility.trace('Assignment is a Worker :');
4985 L_EMPLOYEE_CLASS_FOR_PENSION:='Worker';
4986 elsif (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='2') THEN
4987 L_EMPLOYEE_CLASS_FOR_PENSION :='Non Eligible Job Holder';
4988 hr_utility.trace('Assignment is a NON ELIGIBLE JOB HOLDER :');
4989 ELSIF (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4990 L_EMPLOYEE_CLASS_FOR_PENSION :='Eligible Job Holder';
4991 end if;
4992 hr_utility.trace('Done assessing');
4993 if(L_EMPLOYEE_PENSION_CLASS_OLD <> L_EMPLOYEE_CLASS_FOR_PENSION) or (L_EARNINGS_OLD <> L_EARNINGS) then
4994 hr_utility.trace('Employee assessed again as there is a change in pensions classification/Earnings');
4995 if (NVL(L_EMPLOYEE_STATUS_FOR_PENSION,'3')='1') THEN
4996 IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) = G_END_OF_TIME THEN -- Never Found eligible
4997 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
4998 L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),
4999 nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
5000 ELSE
5001 L_ELIGIBLE_JH_DATE := GREATEST(G_PRP_START_DATE,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),
5002 nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
5003 END IF;
5004 END IF;
5005 -- Found Eligible previously
5006 IF NVL(L_ELIGIBLE_JH_DATE,G_END_OF_TIME) <> G_END_OF_TIME THEN
5007 IF (L_POSTPONEMENT_DATE IS NOT NULL ) AND L_POSTPONEMENT_TYPE = 'Eligible Job Holder Postponement' AND
5008 (L_POSTPONEMENT_DATE< GREATEST(G_PRP_START_DATE,L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE))-1)THEN
5009 hr_utility.trace('One Day after the postponement end date , this PRP starts.');
5010 IF (L_DOB_TT BETWEEN G_PRP_START_DATE AND G_PRP_END_DATE) THEN
5011 L_ELIGIBLE_JH_DATE := GREATEST(L_DOB_TT,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),
5012 nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
5013 ELSE
5014 L_ELIGIBLE_JH_DATE := GREATEST(G_PRP_START_DATE,L_SERVICE_START,FND_DATE.canonical_to_date(G_PENSION_STAGING_DATE),
5015 nvl(FND_DATE.canonical_to_date(L_POSTPONEMENT_DATE)+1,to_date('0001-01-01','YYYY-MM-DD')));
5016 END IF;
5017 END IF;
5018 end if;
5019 end if;
5020 RAISE L_EXCEPTION_UPDATE;
5021 else
5022 hr_utility.trace('Employee not assessed again as there is no change in pensions classification');
5023 RAISE L_EXCEPTION_NO_ERROR; -- Bug 14550021 -- Just Skip if the Q.S.Exists Flag is set to Yes.
5024 end if;
5025 END IF;--Scheme Already Exists
5026 -----------------------------------
5027 ELSE--Main Entry
5028 hr_utility.trace('Not a Main Entry');
5029 RAISE L_EXCEPTION_NO_ERROR;
5030 --Any Other Checks??
5031 END IF; --Main Entry
5032 ELSE --Postponement Check
5033 hr_utility.trace('Postponement End Date Check');
5034 RAISE L_EXCEPTION_NO_ERROR;
5035 --Any Other Checks??
5036 END IF;--Postponement Check
5037 -- ELSE --OPT Out date Crossed
5038 -- hr_utility.trace('OPT Out date Crossed');
5039 -- RAISE L_EXCEPTION_NO_ERROR;
5040 --Any Other Checks??
5041 -- NULL;
5042 -- END IF;--OPT Out date Crossed
5043 ELSE --A.E.D. Not Null
5044 hr_utility.trace('A.E.D. Not Null. Already Enrolled');
5045 RAISE L_EXCEPTION_NO_ERROR;
5046 --Any Other Checks??
5047 END IF; --A.E.D. Null
5048 EXCEPTION
5049 WHEN L_EXCEPTION_NO_ERROR THEN
5050 hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_NO_ERROR');
5051 WHEN L_EXCEPTION_UPDATE_INFO THEN
5052 hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_UPDATE_INFO');
5053 --Update Info Element
5054 IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
5055 hr_utility.trace('Exception Update-- NON-AGGREGATED Case');
5056 --UPDATE_INFO_ELEMENT( L_ELEMENT_ENTRY_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME
5057 UPDATE_INFO_ELEMENT( L_ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
5058 G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID, L_AUTO_ENROLLMENT_DATE , G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID, L_QUALIFYING_SCHEME_EXISTS , G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,
5059 L_POSTPONEMENT_TYPE , G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID, L_POSTPONEMENT_DATE , G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID, L_EMPLOYEE_CLASS_FOR_PENSION , G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,
5060 L_OPTIN_DATE , G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID, L_OPTOUT_DATE , G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID, L_MAIN_ENTRY , G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID, L_EARNINGS ,
5061 G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID, L_OPTOUT_END_DATE , G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID , to_char (l_eligible_jh_date
5062 /*Bug 14622818*/
5063 ,fnd_date.name_in_mask) );
5064 END IF;
5065 --*****************************************************************************************
5066 --AGGREGATION
5067 IF (NVL(L_AGGREGATION_FLAG,'N')='Y') THEN
5068 hr_utility.trace('---------------------------------------------------------------------------------------------');
5069 hr_utility.trace('Exception Update-- AGGREGATED Case');
5070 -- CAll the Procedrure for Aggreagated Case
5071 AGGREGATED (L_PERSON_ID, L_QUALIFYING_SCHEME_NAME, L_AGGREGATION_FLAG, L_AUTO_ENROLLMENT_DATE, L_QUALIFYING_SCHEME_EXISTS, L_POSTPONEMENT_TYPE, L_POSTPONEMENT_DATE, L_EMPLOYEE_CLASS_FOR_PENSION, L_OPTIN_DATE,
5072 L_OPTOUT_DATE, L_MAIN_ENTRY, L_EARNINGS, L_OPTOUT_END_DATE, L_ELIGIBLE_JH_DATE );
5073 --L_AUTO_ENROLLMENT_DATE DATE,L_DEFAULT_SCHEME_NAME VARCHAR2);
5074 hr_utility.trace('---------------------------------------------------------------------------------------------');
5075 END IF;
5076 --AGGREGATION
5077 --*****************************************************************************************
5078 WHEN L_EXCEPTION_UPDATE THEN
5079 hr_utility.trace('INSIDE EXCEPTION: L_EXCEPTION_UPDATE');
5080 --*****************************************************-
5081 pay_action_information_api.create_action_information( p_action_information_id => l_action_id, p_object_version_number => l_ovn, p_action_information_category => 'GB_ENROLL_PENSIONS', p_action_context_id => p_assactid,
5082 p_action_context_type => 'AAP', p_assignment_id => L_ASSIGNMENT_ID, p_effective_date =>G_PRP_END_DATE, p_action_information1 =>L_QUALIFYING_SCHEME_NAME, p_action_information2 =>L_AGGREGATION_FLAG,
5083 p_action_information3 =>L_MAIN_ENTRY, p_action_information4 =>L_QUALIFYING_SCHEME_EXISTS, p_action_information5 =>L_POSTPONEMENT_TYPE, p_action_information6 =>FND_DATE.DATE_TO_CANONICAL(L_POSTPONEMENT_DATE),
5084 p_action_information7 =>L_EMPLOYEE_CLASS_FOR_PENSION, p_action_information8 =>FND_DATE.DATE_TO_CANONICAL(L_ELIGIBLE_JH_DATE), p_action_information9 =>FND_DATE.DATE_TO_CANONICAL(L_AUTO_ENROLLMENT_DATE),
5085 p_action_information10 =>FND_DATE.DATE_TO_CANONICAL(L_OPTOUT_END_DATE), p_action_information11 =>FND_DATE.DATE_TO_CANONICAL(L_OPTIN_DATE), p_action_information12 =>
5086 FND_DATE.DATE_TO_CANONICAL(L_OPTOUT_DATE), p_action_information13 =>L_EARNINGS, p_action_information14 =>L_FULL_NAME, p_action_information15 =>L_ASSIGNMENT_NUMBER,
5087 p_action_information16 =>FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE), p_action_information17 =>FND_DATE.DATE_TO_CANONICAL(L_NOMINAL_AUTO_ENROLL_DATE), p_action_information18 =>floor(L_AGE) );
5088 --**************************************
5089 --Update Info Element
5090 IF (NVL(L_AGGREGATION_FLAG,'N')='N') THEN
5091 hr_utility.trace('Exception Update-- NON-AGGREGATED Case');
5092 --UPDATE_INFO_ELEMENT( L_ELEMENT_ENTRY_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME
5093 UPDATE_INFO_ELEMENT( L_ASSIGNMENT_ID , G_ELEMENT_DETAILS.G_QUALIFYING_SCHEME_NAME_ID, L_QUALIFYING_SCHEME_NAME , G_ELEMENT_DETAILS.G_AGG_EARNINGS_PENSIONS_ID, L_AGGREGATION_FLAG ,
5094 G_ELEMENT_DETAILS.G_AUTO_ENROLLMENT_DATE_ID, L_AUTO_ENROLLMENT_DATE , G_ELEMENT_DETAILS.G_QUALIFYING_PS_EXISTS_ID, L_QUALIFYING_SCHEME_EXISTS , G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID,
5095 L_POSTPONEMENT_TYPE , G_ELEMENT_DETAILS.G_POSTPONEMENT_END_DATE_ID, L_POSTPONEMENT_DATE , G_ELEMENT_DETAILS.G_EMPLOYEE_CLASS_ID, L_EMPLOYEE_CLASS_FOR_PENSION , G_ELEMENT_DETAILS.G_OPT_IN_DATE_ID,
5096 L_OPTIN_DATE , G_ELEMENT_DETAILS.G_OPT_OUT_DATE_ID, L_OPTOUT_DATE , G_ELEMENT_DETAILS.G_MAIN_ENTRY_ID, L_MAIN_ENTRY , G_ELEMENT_DETAILS.G_TOTAL_EARNING_PRP_ID, L_EARNINGS ,
5097 G_ELEMENT_DETAILS.G_OPT_OUT_END_DATE_ID, L_OPTOUT_END_DATE , G_ELEMENT_DETAILS.G_ELIGIBLE_JH_DATE_ID , to_char (l_eligible_jh_date
5098 /*Bug 14622818*/
5099 ,fnd_date.name_in_mask) );
5100 END IF;
5101 --*****************************************************************************************
5102 --AGGREGATION
5103 IF (NVL(L_AGGREGATION_FLAG,'N')='Y') THEN
5104 hr_utility.trace('---------------------------------------------------------------------------------------------');
5105 hr_utility.trace('Exception Update-- AGGREGATED Case');
5106 -- CAll the Procedrure for Aggreagated Case
5107 AGGREGATED (L_PERSON_ID, L_QUALIFYING_SCHEME_NAME, L_AGGREGATION_FLAG, L_AUTO_ENROLLMENT_DATE, L_QUALIFYING_SCHEME_EXISTS, L_POSTPONEMENT_TYPE, L_POSTPONEMENT_DATE, L_EMPLOYEE_CLASS_FOR_PENSION, L_OPTIN_DATE,
5108 L_OPTOUT_DATE, L_MAIN_ENTRY, L_EARNINGS, L_OPTOUT_END_DATE, L_ELIGIBLE_JH_DATE );
5109 --L_AUTO_ENROLLMENT_DATE DATE,L_DEFAULT_SCHEME_NAME VARCHAR2);
5110 hr_utility.trace('---------------------------------------------------------------------------------------------');
5111 END IF;
5112 --AGGREGATION
5113 --*****************************************************************************************
5114 END ; -- Inner Section
5115 EXCEPTION -- Outer Section
5116 WHEN L_EXCEPTION THEN
5117 --RAISE_APPLICATION_ERROR(-20001,'Archive Failed for the assignment');
5118 hr_utility.trace('Archive Failed for the assignment');
5119 WHEN G_EXCEPTION_NO_UPDATE THEN
5120 hr_utility.trace('INSIDE EXCEPTION: G_EXCEPTION_NO_UPDATE');
5121 --*****************************************************-
5122 pay_action_information_api.create_action_information( p_action_information_id => l_action_id, p_object_version_number => l_ovn, p_action_information_category => 'GB_ENROLL_PENSIONS', p_action_context_id => p_assactid,
5123 p_action_context_type => 'AAP', p_assignment_id => L_ASSIGNMENT_ID, p_effective_date =>G_PRP_END_DATE, p_action_information1 =>L_QUALIFYING_SCHEME_NAME, p_action_information2 =>L_AGGREGATION_FLAG,
5124 p_action_information3 =>L_MAIN_ENTRY, p_action_information4 =>L_QUALIFYING_SCHEME_EXISTS, p_action_information5 =>L_POSTPONEMENT_TYPE, p_action_information6 =>FND_DATE.DATE_TO_CANONICAL(L_POSTPONEMENT_DATE),
5125 p_action_information7 =>L_EMPLOYEE_CLASS_FOR_PENSION, p_action_information8 =>FND_DATE.DATE_TO_CANONICAL(L_ELIGIBLE_JH_DATE), p_action_information9 =>FND_DATE.DATE_TO_CANONICAL(L_AUTO_ENROLLMENT_DATE),
5126 p_action_information10 =>FND_DATE.DATE_TO_CANONICAL(L_OPTOUT_END_DATE), p_action_information11 =>FND_DATE.DATE_TO_CANONICAL(L_OPTIN_DATE), p_action_information12 =>FND_DATE.DATE_TO_CANONICAL(L_OPTOUT_DATE),
5127 p_action_information13 =>L_EARNINGS, p_action_information14 =>L_FULL_NAME, p_action_information15 =>L_ASSIGNMENT_NUMBER, p_action_information16 =>FND_DATE.DATE_TO_CANONICAL(G_PRP_END_DATE),
5128 p_action_information17 =>FND_DATE.DATE_TO_CANONICAL(L_NOMINAL_AUTO_ENROLL_DATE), p_action_information18 =>floor (l_age) );
5129 --**************************************
5130 /*
5131 OPEN C_ERROR_MSG;
5132 FETCH C_ERROR_MSG INTO L_ERR_MSG;
5133 CLOSE C_ERROR_MSG;
5134 RAISE_APPLICATION_ERROR(-20001,L_ERR_MSG);
5135 */
5136 WHEN OTHERS THEN
5137 populate_run_msg(p_assactid,sqlerrm);
5138 raise;
5139 END archive_code;
5140 ---ONE TIME EXECUTION
5141 END PAY_GB_ENROLL_PENSION;
[Home] [Help]