1 PACKAGE BODY PSP_VALID_NON_ORCL_PKG AS
2 /* $Header: PSPNONB.pls 120.1.12000000.4 2007/02/22 14:30:38 spchakra noship $ */
3 /**********************************************************************************
4 -- Made modifications on May 18th, 1998
5 RETURN CODES FROM EACH OF THE INDIVIDUAL VALIDATIONS MEAN THE FOLLOWING
6 0 => Individual Validation was performed successfully (NO ERROR. Hence, No Error Code when this value is returned)
7 1 => Invalid Payroll ID
8 2 => Invalid Payroll Period ID
9 3 => Invalid Assignment ID
10 4 => Invalid Person ID
11 5 => Invalid Effective Date (Effective Date does not occur within Time Period's Start and End Dates)
12 6 => Invalid Sub Line Start Date (Sub Line Start Date does not occur within Time Period's Start and End Dates)
13 7 => Invalid Sub Line End Date (Sub Line End Date does not occur within Time Period's Start and End Dates)
14 8 => Sub Line Start Date is later than the Sub Line End Date
15 9 => Invalid Payroll Source Code (Payroll Source Code does not occur in the PSP_LOOKUPS table)
16 10 => Invalid Daily Rate (Daily Rate is greater than the pay amount)
17 11 => Invalid Element Type ID
18 PRE_GEN 12 => Invalid Distribution Date
19 PRE_GEN 13 => Invalid GL_Code_Combination_ID
20 PRE_GEN 14 => Invalid Project ID
21 PRE_GEN 15 => Invalid Expenditure Organization ID
22 PRE_GEN 16 => Invalid Expenditure Type
23 PRE_GEN 17 => Invalid Task ID
24 PRE_GEN 18 => Invalid Award ID
25 PRE_GEN 19 => GL_Code_Combination_ID and Project ID cannot both exist for the same distribution line
26 PRE_GEN 20 => GL_Code_Combination_ID and Project ID fields are both empty for the same distribution line
27 NON_ORCL 21 => Invalid GL_Code_Combination ID value obtained for selected Payroll
28 PRE_GEN 22 => Invalid Costed GL_CCID (Costed GL_CCID is NULL)
29 NON_ORCL 23 => Set_Of_Books_ID from Profile does not match Set of Books ID in Payrolls table
30 24 => No Business Days in selected date range (Summarize and Transfer will fail)
31 25 => Invalid Debit/Credit Flag
32 26 => Sub-line start and end dates co-incide with the dates in another sub-line in batch in Payroll Interface
33 27 => Sub-line start and end dates co-incide with dates in another sub-line in batch in Payroll Sub-lines table
34 NON_ORCL 28 => Pay Amount Not equal to Daily Rate * Business Days -- Added by Al on 02/16/99 for bug 707404
35 29 => Invalid Sub Line Start Date (Sub Line Start Date does not occur within Assignment Start Date
36 and End date)
37 30 => Invalid Sub Line End Date (Sub Line End Date does not occur within Assignment Start and End Dates)
38
39 PSP_HR_SHARED profile is deleted since we will not be able to support in multi-org
40 Multi-org changes with addition of business group id and set of books id conditions -- vcirigir
41
42 **********************************************************************************/
43 Function Validate_Payroll_ID(v_Payroll_ID IN Number, v_Assignment_ID IN Number, v_Effective_Date IN Date,
44 v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER) Return Number;
45 Function Validate_Payroll_Period_ID(v_Payroll_ID IN Number, v_Payroll_Period_ID IN Number, v_Effective_Date IN Date,
46 v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER) Return Number;
47 Function Validate_Assignment_ID(v_Person_ID IN Number, v_Assignment_ID IN Number, v_Effective_Date IN Date,
48 v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER) Return Number;
49 Function Validate_Person_ID(v_Person_ID IN Number, v_Effective_Date IN Date,v_business_group_id IN NUMBER,
50 v_set_of_books_id IN NUMBER) Return Number;
51 -- No need to validate Effective Dates anymore
52 -- Function Validate_Effective_Date(v_Effective_Date IN Date, v_Payroll_ID IN number, v_Payroll_Period_ID IN number) return Number;
53 Function Validate_Sub_Line_End_Date(v_Sub_Line_End_Date IN Date,v_Sub_Line_Start_Date IN Date,
54 v_Payroll_ID IN Number,v_Payroll_Period_ID IN Number,
55 v_Effective_Date IN Date, v_Assignment_id IN Number) Return Number;
56 Function Validate_Sub_Line_Start_Date(v_Sub_Line_Start_Date IN Date, v_Sub_Line_End_Date IN Date,
57 v_Payroll_ID IN number, v_Payroll_Period_ID IN number, v_Effective_Date IN Date,
58 v_Assignment_id IN Number) Return Number;
59 Function Validate_Payroll_Source_Code(v_Payroll_Source_Code IN varchar2) Return Number;
60 Function Validate_Daily_Rate(v_Pay_Amount IN Number, v_Daily_Rate IN Number, v_currency_code IN VARCHAR2) Return Number;
61 Function Validate_Element_Type_ID(v_Element_Type_ID IN Number, v_Payroll_Period_ID IN Number,
62 v_business_group_id IN NUMBER, -- Introduced for bug 3098050
63 v_set_of_books_id IN NUMBER, -- Introduced for bug 3098050
64 v_currency_code IN VARCHAR2 ) Return Number;
65
66 -- introduced parameter v_currency_code for bug 2916848 for Ilo Enhancement in validate_element_type_id
67
68 Time_Period_Start_Date Date;
69 Time_Period_End_Date Date;
70 Assignment_start_date DATE;
71 Assignment_end_date DATE;
72 g_hire_zero_work_days CHAR(1) := 'N';--Added for zero work days build.Bug 1994421.
73
74 Procedure Obtain_Start_End_Dates_From_HR(v_Payroll_ID IN number, v_Payroll_Period_ID IN number,
75 v_Effective_Date IN Date,v_Assignment_id IN NUMBER);
76 -- Function Validate_GL_CCID(v_Payroll_ID IN Number, v_Effective_Date IN DATE) return NUMBER;
77 Function Find_DB_Error_Code(num_Err_Code Number) return varchar2;
78 Function Validate_Sub_Line_Dates(v_Assignment_ID IN Number, v_Payroll_Period_ID IN Number,
79 v_Element_Type_ID IN Number, v_Sub_Line_Start_Date Date, v_Sub_Line_End_Date Date,
80 v_Batch_Name IN varchar2) return NUMBER;
81 Function Validate_Pay_Amount(v_Pay_Amount IN Number, v_Daily_Rate IN Number, v_Sub_Line_Start_Date Date,
82 v_Sub_Line_End_Date Date,v_Assignment_ID Number, v_Effective_Date Date,
83 v_precision IN NUMBER,v_ext_precision IN NUMBER) return Number;
84
85 -- introduced v_precision parameter in validate_pay_amount for Bug 2916848
86
87 -- introduced v_precision,v_ext_precision,v_currency_code parameters for bug 2916848
88 Procedure ALL_RECORDS(v_Batch_Name IN VARCHAR2,
89 v_business_group_id IN NUMBER,
90 v_set_of_books_id IN NUMBER,
91 v_precision IN NUMBER,
92 v_ext_precision IN NUMBER,
93 v_currency_code IN VARCHAR2) IS
94
95 cursor IMPORT_CURSOR is
96 Select *
97 from PSP_PAYROLL_INTERFACE
98 where BATCH_NAME = v_Batch_Name
99 and STATUS_CODE <> 'T'
100 FOR UPDATE OF STATUS_CODE, ERROR_CODE;
101
102 retVal Number;
103 v_DB_Err_Code varchar2(10);
104 b_Records_Exist_In_Cursor BOOLEAN := FALSE;
105
106 Begin
107
108 Begin
109 FND_STATS.Gather_Table_Stats(ownname => 'PSP',
110 tabname => 'PSP_PAYROLL_INTERFACE');
111
112 --- percent => 10,
113 -- tmode => 'NORMAL');
114 -- Above two parameters commented out for bug fix 2463762
115
116 Exception
117 When others then
118 null;
119 End;
120
121 retVal := 0;
122 FOR IMPORT_CURSOR_Agg IN IMPORT_CURSOR LOOP
123
124 b_Records_Exist_In_Cursor := TRUE;
125
126 If retVal = 0 Then
127 Begin
128 retVal := Validate_Person_ID(Import_Cursor_Agg.Person_ID, Import_cursor_Agg.EFFECTIVE_DATE,
129 v_business_group_id,v_set_of_books_id);
130
131 If retVal = 0 Then
132 Null;
133 Else
134 v_DB_Err_Code := Find_DB_Error_Code(retVal);
135 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
136 End If;
137 End;
138 End If;
139
140 If retVal =0 Then
141 Begin
142 retVal := Validate_Assignment_ID(Import_Cursor_Agg.Person_ID, Import_Cursor_Agg.Assignment_ID,
143 Import_Cursor_Agg.Effective_Date,v_business_group_id,
144 v_set_of_books_id);
145
146 If retVal = 0 Then
147 Null;
148 Else
149 v_DB_Err_Code := Find_DB_Error_Code(retVal);
150 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
151 End If;
152 End;
153 End If;
154
155 If retVal =0 Then
156 Begin
157 retVal := Validate_Payroll_ID(Import_Cursor_Agg.Payroll_ID, Import_Cursor_Agg.Assignment_ID,
158 Import_Cursor_Agg.Effective_Date,v_business_group_id,v_set_of_books_id);
159 If retVal <> 0 Then
160 v_DB_Err_Code := Find_DB_Error_Code(retVal);
161 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
162 End If;
163 End;
164 End If;
165
166 If retVal = 0 Then
167 Begin
168 retVal := Validate_Payroll_Period_ID(Import_Cursor_Agg.Payroll_ID, Import_Cursor_Agg.Payroll_Period_ID,
169 Import_Cursor_Agg.Effective_Date,v_business_group_id,
170 v_set_of_books_id);
171
172 If retVal = 0 Then
173 Null;
174 Else
175 v_DB_Err_Code := Find_DB_Error_Code(retVal);
176 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
177 End If;
178 End;
179 End If;
180
181 If retVal = 0 Then
182 Begin
183 retVal := Validate_Sub_Line_End_Date(Import_Cursor_Agg.Sub_Line_End_Date,
184 Import_Cursor_Agg.Sub_Line_Start_Date, Import_Cursor_Agg.Payroll_ID,
185 Import_Cursor_Agg.Payroll_Period_ID, Import_Cursor_Agg.Effective_Date,
186 Import_Cursor_Agg.Assignment_id);
187 If retVal = 0 Then
188 Null;
189 Else
190 v_DB_Err_Code := Find_DB_Error_Code(retVal);
191 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
192 End If;
193 End;
194 End If;
195
196 If retVal = 0 Then
197 Begin
198 retVal := Validate_Sub_Line_Start_Date(Import_Cursor_Agg.Sub_Line_Start_Date,
199 Import_Cursor_Agg.Sub_Line_End_Date, Import_Cursor_Agg.Payroll_ID,
200 Import_Cursor_Agg.Payroll_Period_ID, Import_Cursor_Agg.Effective_Date,
201 Import_Cursor_Agg.Assignment_id);
202
203 If retVal = 0 Then
204 Null;
205 Else
206 v_DB_Err_Code := Find_DB_Error_Code(retVal);
207 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
208 End If;
209 End;
210 End If;
211
212 If retVal = 0 Then
213 Begin
214 retVal := Validate_Payroll_Source_Code(Import_Cursor_Agg.Payroll_Source_Code);
215 If retVal = 0 Then
216 Null;
217 Else
218 v_DB_Err_Code := Find_DB_Error_Code(retVal);
219 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
220 End If;
221 End;
222 End If;
223
224 If retVal = 0 Then
225 Begin
226 retVal := Validate_Daily_Rate(Import_Cursor_Agg.Pay_Amount, Import_Cursor_Agg.Daily_Rate, Import_Cursor_Agg.currency_code);
227 If retVal = 0 Then
228 Null;
229 Else
230 v_DB_Err_Code := Find_DB_Error_Code(retVal);
231 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
232 End If;
233 End;
234 End If;
235
236 If retVal = 0 Then
237 -- Need to validate Pay Amount
238 -- Check if Pay Amount = Daily Rate * Num. of Business Days
239 -- fnd_message.debug('Validating Pay Amount');
240 Begin
241 retVal := Validate_Pay_Amount(Import_Cursor_Agg.Pay_Amount, Import_Cursor_Agg.Daily_Rate,
242 Import_Cursor_Agg.Sub_Line_Start_Date, Import_Cursor_Agg.Sub_Line_End_Date,
243 Import_Cursor_Agg.Assignment_id,Import_Cursor_Agg.Effective_Date,
244 v_precision,v_ext_precision);
245 If retVal = 0 Then
246 Null;
247 Else
248 v_DB_Err_Code := Find_DB_Error_Code(retVal);
249 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
250 End If;
251 End;
252 End If;
253
254 If retVal = 0 Then
255 Begin
256 retVal := Validate_Element_Type_ID(Import_Cursor_Agg.Element_Type_ID, Import_Cursor_Agg.Payroll_Period_ID,
257 v_business_group_id, v_set_of_books_id,
258 v_currency_code);
259 If retVal = 0 Then
260 Null;
261 Else
262 v_DB_Err_Code := Find_DB_Error_Code(retVal);
263 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
264 End If;
265 End;
266 End If;
267
268 /********* Commented since GL_CCID is not being called**************
269 If retVal = 0 Then
270 -- Need to check if a valid GL_Code_Combination_ID is to be obtained for selected payroll
271 Begin
272 retVal := Validate_GL_CCID(Import_Cursor_Agg.Payroll_ID, Import_Cursor_Agg.Effective_Date);
273 If retVal = 0 Then
274 Null;
275 Else
276 v_DB_Err_Code := Find_DB_Error_Code(retVal);
277 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
278 End If;
279 End;
280 End If;
281 ************End Of Commenting*********************/
282
283 If retVal = 0 Then
284 -- Need to check if there is atleast one business day in selected sub_line date range
285 Begin
286 If PSP_General.Business_Days(Import_Cursor_Agg.Sub_Line_Start_Date, Import_Cursor_Agg.Sub_Line_End_Date) = 0
287 AND g_hire_zero_work_days='N' Then --Modified for zero work days build.Bug 1994421.
288 retVal := 24;
289 End If;
290
291 If retVal = 0 Then
292 Null;
293 Else
294 v_DB_Err_Code := Find_DB_Error_Code(retVal);
295 UPDATE PSP_PAYROLL_INTERFACE set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
296 End If;
297 End;
298 End If;
299
300
301 If retVal = 0 Then
302 Begin
303 retVal := Validate_Sub_Line_Dates(Import_Cursor_Agg.Assignment_ID, Import_Cursor_Agg.Payroll_Period_ID,
304 Import_Cursor_Agg.Element_Type_ID, Import_Cursor_Agg.Sub_Line_Start_Date,
305 Import_Cursor_Agg.Sub_Line_End_Date, v_Batch_Name);
306
307 If retVal = 0 Then
308 Null;
309 Else
310 v_DB_Err_Code := Find_DB_Error_Code(retVal);
311 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
312 End If;
313 End;
314 End If;
315 If retVal <> 0 Then
316 retVal := 0;
317 Else
318 UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'V', ERROR_CODE = '' where CURRENT OF Import_Cursor;
319 End If;
320
321 END LOOP;
322 If b_Records_Exist_In_Cursor Then
323 COMMIT;
324 End If;
325
326 Exception
327 when OTHERS Then
328 null;
329 End All_Records;
330
331 Function Validate_Sub_Line_Dates(v_Assignment_ID IN Number, v_Payroll_Period_ID IN Number, v_Element_Type_ID IN Number,
332 v_Sub_Line_Start_Date Date, v_Sub_Line_End_Date Date,
333 v_Batch_Name IN varchar2) return NUMBER is
334
335 v_Record_Count number:= 0;
336 Begin
337
338 -- This cursor checks if the current line's start and end date overlaps over any other line's dates for same
339 -- time period, assignment, and element type in current batch in Interface table
340 Select count(*)
341 into v_Record_Count
342 from PSP_PAYROLL_INTERFACE
343 where Payroll_Period_ID = v_Payroll_Period_ID
344 and Assignment_ID = v_Assignment_ID
345 and Element_Type_ID = v_Element_Type_ID
346 and Batch_Name = v_Batch_Name
347 and ((Sub_Line_Start_Date Between v_Sub_Line_Start_Date and v_Sub_Line_End_Date)
348 or (Sub_Line_End_Date Between v_Sub_Line_Start_Date and v_Sub_Line_End_Date)
349 or (Sub_Line_Start_Date < v_Sub_Line_Start_Date and Sub_Line_End_Date > v_Sub_Line_End_Date));
350
351 If v_Record_Count > 1 Then
352 return 26; -- Error Message indicating that multiple records with overlapping dates exist in Interface table.
353 End If;
354
355 v_Record_Count := 0; -- Reset counter
356 /***********************************************************
357 -- This cursor checks if the current line's start and end date overlaps over any other line's dates for same
358 -- time period, assignment, and element type in PSP_Payroll_Sub_Lines table.
359 Select count(*)
360 into v_Record_Count
361 from PSP_PAYROLL_LINES a,
362 PSP_PAYROLL_SUB_LINES b,
363 PSP_PAYROLL_CONTROLS c
364 where a.PAYROLL_CONTROL_ID = c.PAYROLL_CONTROL_ID
365 and a.PAYROLL_LINE_ID = b.PAYROLL_LINE_ID
366 and c.TIME_PERIOD_ID = v_Payroll_Period_ID
367 and a.ASSIGNMENT_ID = v_Assignment_ID
368 and a.ELEMENT_TYPE_ID = v_Element_Type_ID
369 and ((b.Sub_Line_Start_Date Between v_Sub_Line_Start_Date and v_Sub_Line_End_Date)
370 or (b.Sub_Line_End_Date Between v_Sub_Line_Start_Date and v_Sub_Line_End_Date)
371 or (b.Sub_Line_Start_Date < v_Sub_Line_Start_Date and b.Sub_Line_End_Date > v_Sub_Line_End_Date));
372
373 If v_Record_Count > 1 Then
374 return 27; -- Error Message indicating that multiple records with overlapping dates exist in Payroll Sub-lines.
375 End If;
376 *****************************************************************/
377
378 return 0;
379 End Validate_Sub_Line_Dates;
380
381 Function Validate_GL_CCID(v_Payroll_ID IN Number, v_Effective_Date IN DATE) return NUMBER is
382 n_Set_Of_Books_ID Number;
383 v_Set_Of_Books_ID varchar2(30);
384 n_Cost_Allocation_KeyFlex_ID Number;
385 n_GL_Code_Combination_ID Number;
386 Begin
387 /*************************************************************************
388 --- We are no longer validating the GL CCID in the Validate Procedure. Instead, we will be checking for
389 -- the GL CCID in the Import Sub-lines Process. Moreover the GLCCID will be obtained from
390 -- PSP_Clearing_Account table. (Decided by Venkat in 07/1998)
391 select a.GL_SET_OF_BOOKS_ID, Cost_Allocation_KeyFlex_ID
392 into n_Set_Of_Books_ID, n_Cost_Allocation_KeyFlex_ID
393 from PAY_PAYROLLS_F a
394 where a.PAYROLL_ID = v_Payroll_ID
395 and v_Effective_Date BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE;
396
397 v_Set_Of_Books_ID := fnd_Profile.Value('PSP_SET_OF_BOOKS');
398
399 If (v_Set_Of_Books_ID IS NULL) or (to_number(v_Set_Of_Books_ID) <> n_Set_Of_Books_ID) Then
400 -- fnd_message.debug('Profile value for Set of Books ID :' || v_Set_Of_Books_ID || ' does not match
401 -- value from PAY_PAYROLLS_F. Cannot proceed');
402 return 23;
403 End If;
404
405 -- Obtain Cost Allocation Key Flex ID, GL_Code_Combination_ID, and Balance_Amount using
406 -- Venkat's procedure
407
408 PSP_General.get_GL_CCID(P_Payroll_ID => v_Payroll_ID, P_Set_Of_Books_ID => n_Set_Of_Books_ID,
409 P_Cost_KeyFlex_ID => n_Cost_Allocation_KeyFlex_ID, x_GL_CCID => n_GL_Code_Combination_ID);
410 If n_GL_Code_Combination_ID IS NULL or n_GL_Code_Combination_ID = 0 Then
411 -- fnd_message.debug('GL Code Combination ID is invalid. Cannot proceed');
412 return 21;
413 End If;
414
415 ***************************************************************************/
416 return 0;
417 Exception
418 when OTHERS Then
419 return 21;
420 End Validate_GL_CCID;
421
422 Procedure Obtain_Start_End_Dates_From_HR(v_Payroll_ID IN number, v_Payroll_Period_ID IN number, v_Effective_Date IN Date,v_Assignment_id IN Number) is
423
424 -- This cursor is added to check for subline start date to be greater than or equal to the hiredate of the person
425 /* The following cursor is added to replace the select statement to get assignment dates.
426 Bug 1994421 "Zero Work Days build */
427 CURSOR assignment_date_cur IS
428 SELECT min(effective_start_date), max(effective_end_date)
429 FROM PER_ALL_ASSIGNMENTS_F
430 WHERE assignment_id = v_assignment_id
431 AND assignment_type ='E'; --Added for bug 2624259
432
433
434 Begin
435 select a.Start_Date, a.End_Date
436 into Time_Period_Start_Date, Time_Period_End_Date
437 from PER_TIME_PERIODS a
438 where a.Time_Period_ID = v_Payroll_Period_ID
439 and a.PAYROLL_ID = v_Payroll_ID
440 and (v_Effective_Date BETWEEN a.Start_Date AND a.End_Date);
441
442 -- This query is added to check for subline start date to be greater than or equal to the hiredate of the person
443
444 /* The following code is commented for bug 1994421,"Zero Work days build " */
445 /* select min(a.effective_start_date),max(a.effective_end_date)
446 into assignment_start_date,assignment_end_date
447 from PER_ALL_ASSIGNMENTS_F a
448 where a.assignment_id = v_Assignment_id
449 and assignment_status_type_id IN (select distinct assignment_status_type_id
450 from per_assignment_status_types
451 where per_system_status = 'ACTIVE_ASSIGN') */
452
453 /* The following code is added to get assignment dates. Bug 1994421 "Zero Work Days build */
454 OPEN assignment_date_cur;
455 FETCH assignment_date_cur into assignment_start_date,assignment_end_date;
456 CLOSE assignment_date_cur;
457
458 End Obtain_Start_End_Dates_From_HR;
459
460 Function Validate_Payroll_ID(v_Payroll_ID IN Number, v_Assignment_ID IN Number,
461 v_Effective_Date IN Date, v_business_group_id IN NUMBER,
462 v_set_of_books_id IN NUMBER) Return Number IS
463 v_local_number Number;
464 Begin
465 If v_Payroll_ID is NULL Then
466 return 1;
467 ELSE
468 /***** Modifed the following SELECT for 11510_CU2 consolidated performance fixes.
469 Select DISTINCT a.payroll_id
470 into v_local_number
471 from pay_payrolls_f a, per_assignments_f b
472 where a.payroll_id = b.payroll_id
473 and a.PAYROLL_ID = v_Payroll_ID
474 and b.assignment_id = v_assignment_id
475 and (v_effective_date between a.effective_start_date and a.effective_end_date)
476 and a.business_group_id = v_business_group_id
477 and a.gl_set_of_books_id = v_set_of_books_id;
478 End of comment for 11510_CU2 consolidated performance fixes. *****/
479
480 -- Introduced the following for 11510_CU2 conslodated fixes.
481 SELECT a.payroll_id
482 INTO v_local_number
483 FROM pay_payrolls_f a
484 WHERE a.payroll_id = v_payroll_id
485 AND (v_effective_date between a.effective_start_date and a.effective_end_date)
486 AND a.business_group_id = v_business_group_id
487 AND a.gl_set_of_books_id = v_set_of_books_id
488 AND EXISTS (SELECT 1
489 FROM per_assignments_f b
490 WHERE b.payroll_id = a.payroll_id
491 AND b.assignment_id = v_assignment_id
492 AND v_effective_date BETWEEN b.effective_start_date AND b.effective_end_date);
493 -- End of changes for 11510_CU2 conslodated fixes.
494
495 End If;
496
497 Return 0;
498 Exception
499 WHEN NO_DATA_FOUND THEN
500 Return 1;
501 WHEN TOO_MANY_ROWS THEN
502 Return 0;
503 WHEN OTHERS THEN
504 Return 1;
505 End Validate_Payroll_ID;
506
507 Function Validate_Payroll_Period_ID(v_Payroll_ID IN number, v_Payroll_Period_ID IN number, v_Effective_Date IN Date,
508 v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER)
509 return NUMBER IS
510 v_local_number Number;
511 v_Cost_Allocation_KeyFlex_ID Number;
512 v_GL_CCID Number;
513 Begin
514
515 If v_Payroll_Period_ID IS NULL Then
516 return 2;
517 End If;
518
519 Select Time_Period_id
520 into v_local_number
521 From Per_Time_Periods
522 where Payroll_id = v_Payroll_ID
523 and Time_Period_ID = v_Payroll_Period_ID
524 and (v_Effective_Date between start_date and end_date);
525
526
527 Select Cost_Allocation_KeyFlex_ID
528 into v_Cost_Allocation_Keyflex_ID
529 from PAY_PAYROLLS_F
530 where Payroll_ID = v_Payroll_Id
531 and v_Effective_Date between Effective_Start_Date and Effective_End_Date
532 and business_group_id = v_business_group_id
533 and gl_set_of_books_id = v_set_of_books_id;
534
535 PSP_General.get_GL_CCID(P_Payroll_ID => v_Payroll_ID, P_Set_Of_Books_ID => v_Set_Of_Books_ID,
536 P_Cost_KeyFlex_ID => v_Cost_Allocation_KeyFlex_ID, x_GL_CCID => v_GL_CCID);
537
538 If v_GL_CCID = 0 Then
539 return 21;
540 End If;
541
542 Return 0;
543 Exception
544 when no_data_found then
545 Return 2;
546 when TOO_MANY_ROWS Then
547 Return 0;
548 when OTHERS then
549 Return 2;
550 End Validate_Payroll_Period_ID;
551
552 Function Validate_Assignment_ID(v_Person_ID IN Number, v_Assignment_ID IN Number, v_Effective_Date IN Date,
553 v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER) Return Number IS
554 v_local_number Number;
555 Begin
556 If v_Assignment_ID IS Null Then
557 Return 3;
558 End If;
559
560 Select a.assignment_id
561 into v_local_number
562 from per_assignments_f a ,
563 pay_payrolls_f b
564 where a.person_id = v_person_id
565 AND a.assignment_type ='E' --Added for bug 2624259.
566 and a.assignment_id = v_Assignment_ID
567 and (v_effective_date between a.effective_start_date and a.effective_end_date)
568 and a.business_group_id = v_business_group_id
569 and b.gl_set_of_books_id = v_set_of_books_id
570 and a.payroll_id = b.payroll_id;
571
572 return 0;
573 Exception
574 when no_data_found then
575 Return 3;
576 when TOO_MANY_ROWS Then
577 Return 0;
578 when OTHERS then
579 Return 3;
580 End Validate_Assignment_ID;
581
582 Function Validate_Person_ID(v_Person_ID IN Number, v_Effective_Date IN Date,
583 v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER)
584 Return Number IS
585 v_local_number Number;
586 /* Following cursor is added to replace the select statement to get person_id.
587 Bug 1994421 "Zero Work Days Build" */
588 /***** Modified the following cursor for R12 performance fixes (bug 4507892)
589 CURSOR Valid_person_cur IS
590 SELECT a.person_id
591 FROM Per_People_F a
592 WHERE a.Person_ID = v_person_id
593 -- AND a.current_employee_flag ='Y' --Added for bug 2624259. Commented for Bug 3424494
594 AND (v_effective_date BETWEEN a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE)
595 AND v_effective_date <= (SELECT max(b.effective_end_date)
596 FROM per_assignments_f b,pay_payrolls_f f
597 WHERE a.person_id = b.person_id
598 AND b.assignment_type ='E' --Added for bug 2624259.
599 AND b.business_group_id = v_business_group_id
600 AND f.gl_set_of_books_id = v_set_of_books_id
601 AND f.payroll_id = b.payroll_id) ;
602 End of comment for bug fix 4507892 *****/
603
604 -- New cursor definition for R12 performance fix (4507892)
605 CURSOR valid_person_cur IS
606 SELECT ppf.person_id
607 FROM per_people_f ppf
608 WHERE ppf.person_id = v_person_id
609 AND (v_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
610 AND v_effective_date <= (SELECT MAX(paf.effective_end_date)
611 FROM per_assignments_f paf,
612 pay_payrolls_f ppf2
613 WHERE paf.person_id = v_person_id
614 AND paf.business_group_id = v_business_group_id
615 AND ppf2.payroll_id = paf.payroll_id
616 AND paf.assignment_type ='E'
617 AND ppf2.gl_set_of_books_id = v_set_of_books_id);
618 Begin
619 If v_Person_ID IS Null Then
620 Return 4;
621 End If;
622
623 /* Commented for bug no 1994421,"Zero Work Days Build"*/
624 /* Select a.person_id
625 into v_local_number
626 from Per_People_F a
627 where a.Person_ID = v_Person_ID
628 and (v_effective_date BETWEEN a.EFFECTIVE_START_DATE and A.EFFECTIVE_END_DATE)
629 and a.business_group_id = v_business_group_id
630 and a.person_id in ( select distinct person_id
631 from per_assignments_f f,pay_payrolls_f b
632 where (v_effective_date between f.effective_start_date and f.effective_end_date)
633 and f.business_group_id = v_business_group_id
634 and b.gl_set_of_books_id = v_set_of_books_id
635 and f.payroll_id = b.payroll_id )
636 and a.current_employee_flag = 'Y'; */
637 --Added following code to get person_id into v_local_number.Bug 1994421,"Zero Work Days Build".
638 OPEN Valid_person_cur;
639 FETCH Valid_Person_cur into v_local_number;
640 CLOSE Valid_Person_cur;
641
642 /* Following added for bug 2624259 */
643 IF v_local_number IS NULL THEN
644 Return 4;
645 END IF ;
646
647 /*****************************************************************************
648 -- Commented out the foll. lines of code bcos of performance issues. (AL comm.
649 -- the lines on 12/09/98)
650 and a.Person_ID in
651 (select distinct Person_ID
652 from per_assignments_f
653 where (v_Effective_Date between per_assignments_f.effective_start_date
654 and per_assignments_f.effective_end_date));
655 ****************************************************************************/
656 return 0;
657 Exception
658 when no_data_found then
659 Return 4;
660 when too_many_rows then
661 Return 0;
662 when OTHERS then
663 Return 4;
664 End Validate_Person_ID;
665
666 Function Validate_Sub_Line_Start_Date(v_Sub_Line_Start_Date IN Date, v_Sub_Line_End_Date IN Date, v_Payroll_ID IN number, v_Payroll_Period_ID IN number, v_Effective_Date IN Date,v_Assignment_id IN Number) Return Number IS
667 v_Local_Date Date;
668 Begin
669
670 If v_Sub_Line_Start_Date IS NULL Then
671 return 6;
672 End If;
673
674 If v_Sub_Line_End_Date IS NOT NULL Then
675 If v_Sub_Line_Start_Date > v_Sub_Line_End_Date Then
676 return 8;
677 End If;
678 End If;
679
680 Obtain_Start_End_Dates_From_HR(v_Payroll_ID, v_Payroll_Period_ID, v_Effective_Date,v_Assignment_id);
681 If ((v_Sub_Line_Start_Date >= Time_Period_Start_Date) AND (v_Sub_Line_Start_Date <= Time_Period_End_Date)) AND (v_sub_line_start_date >= Assignment_start_date) Then
682 return 0;
683 Else
684 if (v_sub_line_start_date < Assignment_start_date) then
685 return 29;
686 elsif (v_sub_line_start_date < Time_Period_Start_Date) then --Added for bug 1994421."Zero work days"
687 return 31; --Added for bug 1994421."Zero work days"
688 else
689 return 6;
690 end if;
691 End If;
692
693 End Validate_Sub_Line_Start_Date;
694
695 Function Validate_Sub_Line_End_Date(v_Sub_Line_End_Date IN Date, v_Sub_Line_Start_Date IN Date, v_Payroll_ID IN Number, v_Payroll_Period_ID IN Number, v_Effective_Date IN Date,v_Assignment_id IN Number) Return Number IS
696 v_Local_Date Date;
697 Begin
698
699 If v_Sub_Line_End_Date IS NULL Then
700 return 7;
701 End If;
702
703 If v_Sub_Line_Start_date IS NOT NULL Then
704 If v_Sub_Line_End_Date < v_Sub_Line_Start_Date Then
705 return 8;
706 End If;
707 End If;
708
709 Obtain_Start_End_Dates_From_HR(v_Payroll_ID, v_Payroll_Period_ID, v_Effective_Date,v_Assignment_id);
710 If ((v_Sub_Line_End_Date >= Time_Period_Start_Date) AND (v_Sub_Line_End_Date <= Time_Period_End_Date))
711 AND (v_sub_line_end_date <= assignment_end_date) Then
712 return 0;
713 Else
714 if (v_sub_line_end_date > Assignment_end_date) then
715 return 30;
716 elsif (v_sub_line_end_date > Time_Period_End_Date) then -- Added for bug 1994421."Zero work days build"
717 return 32; -- Added for bug 1994421."Zero work days build"
718 else
719 return 7;
720 end if;
721 end if;
722
723 End Validate_Sub_Line_End_Date;
724
725 Function Validate_Payroll_Source_Code(v_Payroll_Source_Code IN varchar2) Return Number IS
726 v_local_char varchar2(30);
727 Begin
728 If v_Payroll_Source_Code IS Null Then
729 Return 9;
730 End If;
731
732 select a.source_code
733 into v_local_char
734 from psp_payroll_sources a
735 where a.source_type = 'N'
736 and a.source_code = v_Payroll_Source_Code;
737
738 /****************************
739 Select DISTINCT LOOKUP_CODE into v_local_char from PSP_LOOKUPS
740 where LOOKUP_CODE = v_Payroll_Source_Code
741 and LOOKUP_TYPE = 'PAYROLL_SOURCES'
742 and LOOKUP_CODE NOT IN ('PSP','LDM');
743 ***************************/
744 return 0;
745
746 Exception
747 when no_data_found then
748 Return 9;
749 when too_many_rows then
750 Return 0;
751 when OTHERS then
752 Return 9;
753 End Validate_Payroll_Source_Code;
754
755 Function Validate_Daily_Rate(v_Pay_Amount IN Number, v_Daily_Rate IN Number, v_currency_code IN Varchar2)
756 Return Number IS
757 Begin
758 If (abs(v_Pay_Amount) < abs(v_Daily_Rate)) or (v_Pay_Amount IS Null) or (v_Daily_Rate IS Null) Then
759 Return 10;
760 End If;
761
762 IF (v_currency_code = 'STAT') AND ((v_daily_rate < -24) OR (v_daily_rate > 24)) THEN
763 Return 10;
764 END IF;
765
766 Return 0;
767 End Validate_Daily_Rate;
768
769 -- Introduced v_currency_code variable for Bug 2916848 Ilo Enhancement
770
771 Function Validate_Element_Type_ID(v_Element_Type_ID IN Number, v_Payroll_Period_ID IN Number,
772 v_business_group_id IN NUMBER,
773 v_set_of_books_id IN NUMBER,
774 v_currency_code IN Varchar2) Return Number IS
775 v_local_number NUMBER;
776 Begin
777
778 /* commented the following code to agument Element check based on currency
779 for Bug 2916848 Ilo Enhancement
780
781 Select a.element_type_id
782 into v_local_number
783 from psp_element_types a, pay_element_types_f b, per_time_periods c
784 where a.element_type_id = b.element_type_id
785 and a.element_type_id = v_Element_Type_ID
786 and c.time_period_id = v_payroll_period_id
787 and ((c.start_date between a.start_date_active and a.end_date_active)
788 or (c.end_date between a.start_date_active and a.end_date_active)
789 or ((a.start_date_active < c.start_date) and (a.end_date_active > c.end_date)))
790 and b.output_currency_code = v_currency_code;
791 */ -- End of Commenting
792
793 /* Introduced the following check for element_type_id for Bug 2916848 Ilo Enhancement */
794
795 SELECT a.element_type_id
796 INTO v_local_number
797 FROM PSP_ELEMENT_TYPES a,
798 PER_TIME_PERIODS b
799 WHERE a.element_type_id = v_element_type_id
800 AND b.time_period_id = v_payroll_period_id
801 -- Introduced BG/SOB check on psp_element_types for bug fix 3098050
802 AND a.business_group_id = v_business_group_id
803 AND a.set_of_books_id = v_set_of_books_id
804 AND b.start_date <= a.end_date_active
805 AND b.end_date >= a.start_date_active
806 AND exists
807 (SELECT 1
808 FROM PAY_ELEMENT_TYPES_F pef
809 WHERE pef.element_type_id = a.element_type_id
810 AND ( pef.output_currency_code = v_currency_code
811 OR v_currency_code = 'STAT')
812 AND pef.effective_end_date >= a.start_date_active
813 AND pef.effective_start_date <= a.end_date_active
814 );
815
816 return 0;
817
818 Exception
819 when no_data_found then
820 Return 11;
821 when too_many_rows then
822 Return 0;
823 when OTHERS then
824 Return 11;
825
826 End Validate_Element_Type_ID;
827
828 /* Added new parameters v_assignment_id,v_effective_date for bug 1004421 */
829 -- Adding new parameter v_precision,v_ext_precision for ILO enhancement Bug 2916848
830
831 Function Validate_Pay_Amount(v_Pay_Amount IN Number, v_Daily_Rate IN Number, v_Sub_Line_Start_Date Date,
832 v_Sub_Line_End_Date Date, v_assignment_id Number,v_effective_date date,
833 v_precision IN NUMBER,v_ext_precision IN NUMBER) return Number is
834
835 n_Business_Days Number;
836 Begin
837 /* The following code is added for bug 1994421 ."Zero work days build"*/
838 n_Business_Days := PSP_General.Business_Days(v_Sub_Line_Start_Date, v_Sub_Line_End_Date, v_assignment_id);
839
840 IF n_Business_Days=0 and v_sub_line_start_date=v_effective_date THEN
841 g_hire_zero_work_days:='Y';
842 IF v_sub_line_start_date<>v_sub_line_end_date THEN
843 return 34;
844 END IF;
845
846 IF v_Pay_Amount <> v_Daily_Rate THEN
847 return 33;
848 END IF; -- End of Modification for bug 1994421."Zero work days build".
849
850 ELSE
851 IF ROUND(v_Pay_Amount,v_precision) <> ROUND((ROUND(v_Daily_Rate,v_ext_precision)* n_Business_Days),
852 v_precision)
853 Then
854
855 -- Changed the precision from 2 to the v_precision for Bug 2916848
856 -- fnd_message.debug('Please ensure that Pay Amount equals Daily Rate times num. of business days.');
857 return 28;
858 End If;
859 END IF ;
860 return 0;
861
862 Exception
863 when OTHERS then
864 return 28;
865
866 End Validate_Pay_Amount;
867
868 Function Find_DB_Error_Code(num_Err_Code Number) return varchar2 is
869 Begin
870 If num_Err_Code is NULL or num_Err_Code = 0 Then
871 return NULL;
872 End If;
873
874 If num_Err_Code = 1 Then
875 return 'INV_PID';
876 elsif num_Err_Code = 2 Then
877 return 'INV_TPI';
878 elsif num_Err_Code = 3 Then
879 return 'INV_ASG';
880 elsif num_Err_Code = 4 Then
881 return 'INV_PER';
882 elsif num_Err_Code = 5 Then
883 return 'INV_EFF';
884 elsif num_Err_Code = 6 Then
885 return 'INV_STD';
886 elsif num_Err_Code = 7 Then
887 return 'INV_END';
888 elsif num_Err_Code = 8 Then
889 return 'ST_END';
890 elsif num_Err_Code = 9 Then
891 return 'INV_SRC';
892 elsif num_Err_Code = 10 Then
893 return 'INV_DLY';
894 elsif num_Err_Code = 11 Then
895 return 'INV_ELE';
896 elsif num_Err_Code = 12 Then
897 return 'INV_DIS';
898 elsif num_Err_Code = 13 Then
899 return 'INV_GLC';
900 elsif num_Err_Code = 14 Then
901 return 'INV_PRI';
902 elsif num_Err_Code = 15 Then
903 return 'INV_EOI';
904 elsif num_Err_Code = 16 Then
905 return 'INV_ET';
906 elsif num_Err_Code = 17 Then
907 return 'INV_TI';
908 elsif num_Err_Code = 18 Then
909 return 'INV_AI';
910 elsif num_Err_Code = 19 Then
911 return 'NOT_GLP';
912 elsif num_Err_Code = 20 Then
913 return 'NUL_GLP';
914 elsif num_Err_Code = 21 Then
915 return 'INV_GL2';
916 elsif num_Err_Code = 22 Then
917 return 'INV_COS';
918 elsif num_Err_Code = 23 Then
919 return 'SOB_PRO';
920 elsif num_Err_Code = 24 Then
921 return 'NUL_BUS';
922 elsif num_Err_Code = 25 Then
923 return 'INV_D_C';
924 elsif num_Err_Code = 26 Then
925 return 'OVLP_DT1';
926 /*********************************
927 elsif num_Err_Code = 27 Then
928 return 'OVLP_DT2';
929 **********************************/
930 elsif num_Err_Code = 28 Then
931 return 'INV_PAY';
932 elsif num_Err_Code = 29 Then
933 return 'INV_ASDT';
934 elsif num_Err_Code = 30 Then
935 return 'INV_AEDT';
936 elsif num_Err_Code = 31 Then --Added for bug 1994421."Zero work days build".
937 return 'INV_PSDT';
938 elsif num_Err_Code = 32 Then --Added for bug 1994421."Zero work days build".
939 return 'INV_PEDT';
940 elsif num_Err_Code = 33 Then --Added for bug 1994421."Zero work days build".
941 return 'INV_PAY1';
942 elsif num_Err_Code = 34 Then --Added for bug 1994421."Zero work days build".
943 return 'INV_SUDT';
944
945 else
946 return NULL;
947 End If;
948 End Find_DB_Error_Code;
949
950 END;
[Home] [Help]