1 PACKAGE BODY PSP_VALID_NON_ORCL_PKG AS
2 /* $Header: PSPNONB.pls 120.4.12020000.4 2012/10/10 09:03:48 lkodaman ship $ */
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, Import_Cursor_Agg.Assignment_id) = 0 -- Bug : 14378545 Added assignment id as parameter.
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 /* Commented for Bug : 14605283
527
528 Select Cost_Allocation_KeyFlex_ID
529 into v_Cost_Allocation_Keyflex_ID
530 from PAY_PAYROLLS_F
531 where Payroll_ID = v_Payroll_Id
532 and v_Effective_Date between Effective_Start_Date and Effective_End_Date
533 and business_group_id = v_business_group_id
534 and gl_set_of_books_id = v_set_of_books_id;
535
536 PSP_General.get_GL_CCID(P_Payroll_ID => v_Payroll_ID, P_Set_Of_Books_ID => v_Set_Of_Books_ID,
537 P_Cost_KeyFlex_ID => v_Cost_Allocation_KeyFlex_ID, x_GL_CCID => v_GL_CCID);
538
539 If v_GL_CCID = 0 Then
540 return 21;
541 End If;
542
543 */
544
545 Return 0;
546 Exception
547 when no_data_found then
548 Return 2;
549 when TOO_MANY_ROWS Then
550 Return 0;
551 when OTHERS then
552 Return 2;
553 End Validate_Payroll_Period_ID;
554
555 Function Validate_Assignment_ID(v_Person_ID IN Number, v_Assignment_ID IN Number, v_Effective_Date IN Date,
556 v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER) Return Number IS
557 v_local_number Number;
558 Begin
559 If v_Assignment_ID IS Null Then
560 Return 3;
561 End If;
562
563 Select a.assignment_id
564 into v_local_number
565 from per_assignments_f a ,
566 pay_payrolls_f b
567 where a.person_id = v_person_id
568 AND a.assignment_type ='E' --Added for bug 2624259.
569 and a.assignment_id = v_Assignment_ID
570 and (v_effective_date between a.effective_start_date and a.effective_end_date)
571 and a.business_group_id = v_business_group_id
572 and b.gl_set_of_books_id = v_set_of_books_id
573 and a.payroll_id = b.payroll_id;
574
575 return 0;
576 Exception
577 when no_data_found then
578 Return 3;
579 when TOO_MANY_ROWS Then
580 Return 0;
581 when OTHERS then
582 Return 3;
583 End Validate_Assignment_ID;
584
585 Function Validate_Person_ID(v_Person_ID IN Number, v_Effective_Date IN Date,
586 v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER)
587 Return Number IS
588 v_local_number Number;
589 /* Following cursor is added to replace the select statement to get person_id.
590 Bug 1994421 "Zero Work Days Build" */
591 /***** Modified the following cursor for R12 performance fixes (bug 4507892)
592 CURSOR Valid_person_cur IS
593 SELECT a.person_id
594 FROM Per_People_F a
595 WHERE a.Person_ID = v_person_id
596 -- AND a.current_employee_flag ='Y' --Added for bug 2624259. Commented for Bug 3424494
597 AND (v_effective_date BETWEEN a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE)
598 AND v_effective_date <= (SELECT max(b.effective_end_date)
599 FROM per_assignments_f b,pay_payrolls_f f
600 WHERE a.person_id = b.person_id
601 AND b.assignment_type ='E' --Added for bug 2624259.
602 AND b.business_group_id = v_business_group_id
603 AND f.gl_set_of_books_id = v_set_of_books_id
604 AND f.payroll_id = b.payroll_id) ;
605 End of comment for bug fix 4507892 *****/
606
607 -- New cursor definition for R12 performance fix (4507892)
608 CURSOR valid_person_cur IS
609 SELECT ppf.person_id
610 FROM per_people_f ppf
611 WHERE ppf.person_id = v_person_id
612 AND (v_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
613 AND v_effective_date <= (SELECT MAX(paf.effective_end_date)
614 FROM per_assignments_f paf,
615 pay_payrolls_f ppf2
616 WHERE paf.person_id = v_person_id
617 AND paf.business_group_id = v_business_group_id
618 AND ppf2.payroll_id = paf.payroll_id
619 AND paf.assignment_type ='E'
620 AND ppf2.gl_set_of_books_id = v_set_of_books_id);
621 Begin
622 If v_Person_ID IS Null Then
623 Return 4;
624 End If;
625
626 /* Commented for bug no 1994421,"Zero Work Days Build"*/
627 /* Select a.person_id
628 into v_local_number
629 from Per_People_F a
630 where a.Person_ID = v_Person_ID
631 and (v_effective_date BETWEEN a.EFFECTIVE_START_DATE and A.EFFECTIVE_END_DATE)
632 and a.business_group_id = v_business_group_id
633 and a.person_id in ( select distinct person_id
634 from per_assignments_f f,pay_payrolls_f b
635 where (v_effective_date between f.effective_start_date and f.effective_end_date)
636 and f.business_group_id = v_business_group_id
637 and b.gl_set_of_books_id = v_set_of_books_id
638 and f.payroll_id = b.payroll_id )
639 and a.current_employee_flag = 'Y'; */
640 --Added following code to get person_id into v_local_number.Bug 1994421,"Zero Work Days Build".
641 OPEN Valid_person_cur;
642 FETCH Valid_Person_cur into v_local_number;
643 CLOSE Valid_Person_cur;
644
645 /* Following added for bug 2624259 */
646 IF v_local_number IS NULL THEN
647 Return 4;
648 END IF ;
649
650 /*****************************************************************************
651 -- Commented out the foll. lines of code bcos of performance issues. (AL comm.
652 -- the lines on 12/09/98)
653 and a.Person_ID in
654 (select distinct Person_ID
655 from per_assignments_f
656 where (v_Effective_Date between per_assignments_f.effective_start_date
657 and per_assignments_f.effective_end_date));
658 ****************************************************************************/
659 return 0;
660 Exception
661 when no_data_found then
662 Return 4;
663 when too_many_rows then
664 Return 0;
665 when OTHERS then
666 Return 4;
667 End Validate_Person_ID;
668
669 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
670 v_Local_Date Date;
671 Begin
672
673 If v_Sub_Line_Start_Date IS NULL Then
674 return 6;
675 End If;
676
677 If v_Sub_Line_End_Date IS NOT NULL Then
678 If v_Sub_Line_Start_Date > v_Sub_Line_End_Date Then
679 return 8;
680 End If;
681 End If;
682
683 Obtain_Start_End_Dates_From_HR(v_Payroll_ID, v_Payroll_Period_ID, v_Effective_Date,v_Assignment_id);
684 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
685 return 0;
686 Else
687 if (v_sub_line_start_date < Assignment_start_date) then
688 return 29;
689 elsif (v_sub_line_start_date < Time_Period_Start_Date) then --Added for bug 1994421."Zero work days"
690 return 31; --Added for bug 1994421."Zero work days"
691 else
692 return 6;
693 end if;
694 End If;
695
696 End Validate_Sub_Line_Start_Date;
697
698 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
699 v_Local_Date Date;
700 Begin
701
702 If v_Sub_Line_End_Date IS NULL Then
703 return 7;
704 End If;
705
706 If v_Sub_Line_Start_date IS NOT NULL Then
707 If v_Sub_Line_End_Date < v_Sub_Line_Start_Date Then
708 return 8;
709 End If;
710 End If;
711
712 Obtain_Start_End_Dates_From_HR(v_Payroll_ID, v_Payroll_Period_ID, v_Effective_Date,v_Assignment_id);
713 If ((v_Sub_Line_End_Date >= Time_Period_Start_Date) AND (v_Sub_Line_End_Date <= Time_Period_End_Date))
714 AND (v_sub_line_end_date <= assignment_end_date) Then
715 return 0;
716 Else
717 if (v_sub_line_end_date > Assignment_end_date) then
718 return 30;
719 elsif (v_sub_line_end_date > Time_Period_End_Date) then -- Added for bug 1994421."Zero work days build"
720 return 32; -- Added for bug 1994421."Zero work days build"
721 else
722 return 7;
723 end if;
724 end if;
725
726 End Validate_Sub_Line_End_Date;
727
728 Function Validate_Payroll_Source_Code(v_Payroll_Source_Code IN varchar2) Return Number IS
729 v_local_char varchar2(30);
730 Begin
731 If v_Payroll_Source_Code IS Null Then
732 Return 9;
733 End If;
734
735 select a.source_code
736 into v_local_char
737 from psp_payroll_sources a
738 where a.source_type = 'N'
739 and a.source_code = v_Payroll_Source_Code;
740
741 /****************************
742 Select DISTINCT LOOKUP_CODE into v_local_char from PSP_LOOKUPS
743 where LOOKUP_CODE = v_Payroll_Source_Code
744 and LOOKUP_TYPE = 'PAYROLL_SOURCES'
745 and LOOKUP_CODE NOT IN ('PSP','LDM');
746 ***************************/
747 return 0;
748
749 Exception
750 when no_data_found then
751 Return 9;
752 when too_many_rows then
753 Return 0;
754 when OTHERS then
755 Return 9;
756 End Validate_Payroll_Source_Code;
757
758 Function Validate_Daily_Rate(v_Pay_Amount IN Number, v_Daily_Rate IN Number, v_currency_code IN Varchar2)
759 Return Number IS
760 Begin
761 If (abs(v_Pay_Amount) < abs(v_Daily_Rate)) or (v_Pay_Amount IS Null) or (v_Daily_Rate IS Null) Then
762 Return 10;
763 End If;
764
765 IF (v_currency_code = 'STAT') AND ((v_daily_rate < -24) OR (v_daily_rate > 24)) THEN
766 Return 10;
767 END IF;
768
769 Return 0;
770 End Validate_Daily_Rate;
771
772 -- Introduced v_currency_code variable for Bug 2916848 Ilo Enhancement
773
774 Function Validate_Element_Type_ID(v_Element_Type_ID IN Number, v_Payroll_Period_ID IN Number,
775 v_business_group_id IN NUMBER,
776 v_set_of_books_id IN NUMBER,
777 v_currency_code IN Varchar2) Return Number IS
778 v_local_number NUMBER;
779 Begin
780
781 /* commented the following code to agument Element check based on currency
782 for Bug 2916848 Ilo Enhancement
783
784 Select a.element_type_id
785 into v_local_number
786 from psp_element_types a, pay_element_types_f b, per_time_periods c
787 where a.element_type_id = b.element_type_id
788 and a.element_type_id = v_Element_Type_ID
789 and c.time_period_id = v_payroll_period_id
790 and ((c.start_date between a.start_date_active and a.end_date_active)
791 or (c.end_date between a.start_date_active and a.end_date_active)
792 or ((a.start_date_active < c.start_date) and (a.end_date_active > c.end_date)))
793 and b.output_currency_code = v_currency_code;
794 */ -- End of Commenting
795
796 /* Introduced the following check for element_type_id for Bug 2916848 Ilo Enhancement */
797
798 SELECT a.element_type_id
799 INTO v_local_number
800 FROM PSP_ELEMENT_TYPES a,
801 PER_TIME_PERIODS b
802 WHERE a.element_type_id = v_element_type_id
803 AND b.time_period_id = v_payroll_period_id
804 -- Introduced BG/SOB check on psp_element_types for bug fix 3098050
805 AND a.business_group_id = v_business_group_id
806 AND a.set_of_books_id = v_set_of_books_id
807 AND b.start_date <= a.end_date_active
808 AND b.end_date >= a.start_date_active
809 AND exists
810 (SELECT 1
811 FROM PAY_ELEMENT_TYPES_F pef
812 WHERE pef.element_type_id = a.element_type_id
813 AND ( pef.output_currency_code = v_currency_code
814 OR v_currency_code = 'STAT')
815 AND pef.effective_end_date >= a.start_date_active
816 AND pef.effective_start_date <= a.end_date_active
817 );
818
819 return 0;
820
821 Exception
822 when no_data_found then
823 Return 11;
824 when too_many_rows then
825 Return 0;
826 when OTHERS then
827 Return 11;
828
829 End Validate_Element_Type_ID;
830
831 /* Added new parameters v_assignment_id,v_effective_date for bug 1004421 */
832 -- Adding new parameter v_precision,v_ext_precision for ILO enhancement Bug 2916848
833
834 Function Validate_Pay_Amount(v_Pay_Amount IN Number, v_Daily_Rate IN Number, v_Sub_Line_Start_Date Date,
835 v_Sub_Line_End_Date Date, v_assignment_id Number,v_effective_date date,
836 v_precision IN NUMBER,v_ext_precision IN NUMBER) return Number is
837
838 n_Business_Days Number;
839 Begin
840 /* The following code is added for bug 1994421 ."Zero work days build"*/
841 n_Business_Days := PSP_General.Business_Days(v_Sub_Line_Start_Date, v_Sub_Line_End_Date, v_assignment_id);
842
843 IF n_Business_Days=0 and v_sub_line_start_date=v_effective_date THEN
844 g_hire_zero_work_days:='Y';
845 IF v_sub_line_start_date<>v_sub_line_end_date THEN
846 return 34;
847 END IF;
848
849 IF v_Pay_Amount <> v_Daily_Rate THEN
850 return 33;
851 END IF; -- End of Modification for bug 1994421."Zero work days build".
852
853 ELSE
854 IF ROUND(v_Pay_Amount,v_precision) <> ROUND((ROUND(v_Daily_Rate,v_ext_precision)* n_Business_Days),
855 v_precision)
856 Then
857
858 -- Changed the precision from 2 to the v_precision for Bug 2916848
859 -- fnd_message.debug('Please ensure that Pay Amount equals Daily Rate times num. of business days.');
860 return 28;
861 End If;
862 END IF ;
863 return 0;
864
865 Exception
866 when OTHERS then
867 return 28;
868
869 End Validate_Pay_Amount;
870
871 Function Find_DB_Error_Code(num_Err_Code Number) return varchar2 is
872 Begin
873 If num_Err_Code is NULL or num_Err_Code = 0 Then
874 return NULL;
875 End If;
876
877 If num_Err_Code = 1 Then
878 return 'INV_PID';
879 elsif num_Err_Code = 2 Then
880 return 'INV_TPI';
881 elsif num_Err_Code = 3 Then
882 return 'INV_ASG';
883 elsif num_Err_Code = 4 Then
884 return 'INV_PER';
885 elsif num_Err_Code = 5 Then
886 return 'INV_EFF';
887 elsif num_Err_Code = 6 Then
888 return 'INV_STD';
889 elsif num_Err_Code = 7 Then
890 return 'INV_END';
891 elsif num_Err_Code = 8 Then
892 return 'ST_END';
893 elsif num_Err_Code = 9 Then
894 return 'INV_SRC';
895 elsif num_Err_Code = 10 Then
896 return 'INV_DLY';
897 elsif num_Err_Code = 11 Then
898 return 'INV_ELE';
899 elsif num_Err_Code = 12 Then
900 return 'INV_DIS';
901 elsif num_Err_Code = 13 Then
902 return 'INV_GLC';
903 elsif num_Err_Code = 14 Then
904 return 'INV_PRI';
905 elsif num_Err_Code = 15 Then
906 return 'INV_EOI';
907 elsif num_Err_Code = 16 Then
908 return 'INV_ET';
909 elsif num_Err_Code = 17 Then
910 return 'INV_TI';
911 elsif num_Err_Code = 18 Then
912 return 'INV_AI';
913 elsif num_Err_Code = 19 Then
914 return 'NOT_GLP';
915 elsif num_Err_Code = 20 Then
916 return 'NUL_GLP';
917 elsif num_Err_Code = 21 Then
918 return 'INV_GL2';
919 elsif num_Err_Code = 22 Then
920 return 'INV_COS';
921 elsif num_Err_Code = 23 Then
922 return 'SOB_PRO';
923 elsif num_Err_Code = 24 Then
924 return 'NUL_BUS';
925 elsif num_Err_Code = 25 Then
926 return 'INV_D_C';
927 elsif num_Err_Code = 26 Then
928 return 'OVLP_DT1';
929 /*********************************
930 elsif num_Err_Code = 27 Then
931 return 'OVLP_DT2';
932 **********************************/
933 elsif num_Err_Code = 28 Then
934 return 'INV_PAY';
935 elsif num_Err_Code = 29 Then
936 return 'INV_ASDT';
937 elsif num_Err_Code = 30 Then
938 return 'INV_AEDT';
939 elsif num_Err_Code = 31 Then --Added for bug 1994421."Zero work days build".
940 return 'INV_PSDT';
941 elsif num_Err_Code = 32 Then --Added for bug 1994421."Zero work days build".
942 return 'INV_PEDT';
943 elsif num_Err_Code = 33 Then --Added for bug 1994421."Zero work days build".
944 return 'INV_PAY1';
945 elsif num_Err_Code = 34 Then --Added for bug 1994421."Zero work days build".
946 return 'INV_SUDT';
947
948 else
949 return NULL;
950 End If;
951 End Find_DB_Error_Code;
952
953 END;
[Home] [Help]