1 PACKAGE BODY PSP_PI_IMPORT_DATA AS
2 /* $Header: PSPPII2B.pls 120.3 2006/10/19 05:45:11 dpaudel noship $ */
3 /*********************************************************************************************
4 This package has been created for concurrent processing. This contains parameters
5 for errbuf and retCode. This has been modified on 05/08/98 by Al Arunachalam
6 **********************************************************************************************/
7 retVal Number;
8 g_bg_currency_code psp_payroll_interface.currency_code%type;
9 g_sob_currency_code gl_sets_of_books.currency_code%type; -- Introduced for bug fix 3107800
10
11
12 -- Introduced v_precision,v_ext_precision,v_currency_code in perform_validation for Bug 2916848
13 Function Perform_Validations(v_Batch_Name IN varchar2,v_business_group_id IN NUMBER,
14 v_set_of_books_id IN NUMBER,v_precision IN NUMBER,
15 v_ext_precision IN NUMBER,v_currency_code IN VARCHAR2) return Number;
16
17 -- Introduced v_precision,v_ext_precision,v_currency_code IN perform_import function for Bug 2916848
18 Function Perform_Import(v_Batch_Name IN varchar2,v_business_group_id IN NUMBER,
19 v_set_of_books_id IN NUMBER,v_precision IN NUMBER,
20 v_ext_precision IN NUMBER,v_currency_code IN VARCHAR2) return Number;
21
22 -- Introduced v_precision,v_ext_precision IN import_payroll_lines function for Bug 2916848
23 Function Import_Payroll_Lines(V_Batch_Name IN varchar2,v_business_group_id IN NUMBER,
24 v_set_of_books_id IN NUMBER,v_precision IN NUMBER,
25 v_ext_precision IN NUMBER) return Number;
26 Function Import_Payroll_Sub_Lines(v_RowID IN OUT NOCOPY varchar2, n_Payroll_Sub_Lines_ID number,
27 n_Payroll_Lines_ID Number, d_Sub_Line_Start_Date DATE,
28 d_Sub_Line_End_Date Date, v_Reason_Code varchar2, n_Pay_Amount Number, n_Daily_Rate
29 Number, n_Salary_Used Number, n_Current_Salary Number,
30 n_FTE Number, n_Organization_ID Number, n_Job_ID Number, n_Position_ID Number,
31 d_Employment_Begin_Date Date, d_Employment_End_Date Date, d_Status_Inactive_Date Date,
32 d_Status_Active_Date Date, d_Assignment_Begin_Date Date, d_Assignment_End_Date Date,
33 p_attribute_category IN VARCHAR2, p_attribute1 IN VARCHAR2, -- Introduced DFF columns for bug fix 2908859
34 p_attribute2 IN VARCHAR2, p_attribute3 IN VARCHAR2,
35 p_attribute4 IN VARCHAR2, p_attribute5 IN VARCHAR2,
36 p_attribute6 IN VARCHAR2, p_attribute7 IN VARCHAR2,
37 p_attribute8 IN VARCHAR2, p_attribute9 IN VARCHAR2,
38 p_attribute10 IN VARCHAR2)
39 return Number;
40
41 -- Introduced v_precision,v_ext_precision in process_payroll_sub_lines function for Bug 2916848
42 Function Process_Payroll_Sub_Lines(v_Batch_Name varchar2, n_Payroll_Period_ID number,
43 n_Assignment_ID Number, n_Element_Type_Id Number, n_Payroll_Lines_ID Number,
44 d_Sub_Line_Start_Date DATE, d_Sub_Line_End_Date DATE,v_precision number,
45 v_ext_precision number, v_business_group_id IN NUMBER) return Number; -- Introduced BG for bug 2908859
46
47 Function Change_To_Transfer(v_Batch_Name IN varchar2) return Number;
48 Function Check_For_Valid_Batches(v_Batch_Name IN varchar2,v_business_group_id IN NUMBER,
49 v_set_of_books_id IN NUMBER) return Number;
50
51 -- Enc Fix 2916848
52 -- Introduces check for valid currency for Bug2916848 to check whether a batch has more than
53 -- one currency.
54
55 Function Check_For_Valid_Currency(v_batch_name IN VARCHAR2,v_business_group_id IN NUMBER,
56 v_set_of_books_id IN NUMBER) return NUMBER;
57
58 -- Introduced get currency for batch for Bug 2916848
59
60 Function Get_Currency_For_Batch(v_batch_name IN VARCHAR2,v_business_group_id IN NUMBER,
61 v_set_of_books_id IN NUMBER) return VARCHAR2;
62
63 -- End of Enc fix 2916848
64
65 /*************************************IMPORT_RECORDS**************************************
66 OBJ: This is a public procedure (called externally, from the concurrent manager). This
67 serves as a wrapper for calling the Validations procedure, for performing the import,
68 and for Changing the status of records imported to 'TRANSFER'.
69 CREATED BY: AL ARUNACHALAM
70 DATE: 03/27/98
71 *****************************************************************************************/
72 Procedure Imp_Rec(errBuf OUT NOCOPY varchar2, retCode OUT NOCOPY varchar2,
73 v_Batch_Name IN varchar2, v_business_group_id IN NUMBER,
74 v_set_of_books_id IN NUMBER) IS
75
76 -- Enh. fix 2094036
77 CURSOR payroll_interface_check_cur IS
78 SELECT payroll_interface_id
79 FROM psp_payroll_interface
80 WHERE batch_name = v_batch_name
81 AND business_group_id = v_business_group_id
82 AND set_of_books_id = v_set_of_books_id
83 FOR UPDATE OF payroll_interface_id NOWAIT;
84
85 l_payroll_interface_id NUMBER;
86
87 RECORD_ALREADY_LOCKED EXCEPTION;
88
89 -- Enc. Fix 2916848
90
91 -- Introduced the following for bug fix 3107800
92 CURSOR sob_currency_code_cur IS
93 SELECT currency_code
94 FROM gl_sets_of_books gsob
95 WHERE set_of_books_id = v_set_of_books_id;
96 -- End of fix 3107800
97
98 l_currency_code psp_payroll_interface.currency_code%type;
99
100 l_precision NUMBER;
101 l_ext_precision NUMBER;
102
103
104 -- End of Fix 2916848 by tbalacha
105
106 PRAGMA EXCEPTION_INIT (RECORD_ALREADY_LOCKED, -54);
107 -- End of Enh. fix 2094036
108
109 l_error_api_name VARCHAR2(2000);
110
111 l_msg_count NUMBER;
112 l_msg_data VARCHAR2(2000);
113 l_dist_message VARCHAR2(2000);
114 Begin
115
116 -- Initialize the FND_MSG_PUB package
117 fnd_msg_pub.Initialize;
118
119 -- Enh. fix 2094036
120 OPEN payroll_interface_check_cur;
121 FETCH payroll_interface_check_cur INTO l_payroll_interface_id;
122 IF (payroll_interface_check_cur%NOTFOUND) THEN
123 CLOSE payroll_interface_check_cur;
124 RAISE RECORD_ALREADY_LOCKED;
125 END IF;
126
127 CLOSE payroll_interface_check_cur;
128 -- End of Enh. fix 2094036
129
130 -- First, Validate all records in this batch
131
132
133 If Check_For_Valid_Batches(v_Batch_Name,v_business_group_id,v_set_of_books_id) <> 0 Then
134 l_error_api_name := 'INVALID BATCH';
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 End If;
137
138
139 -- ENH. fix 2916848
140
141 g_bg_currency_code := psp_general.get_currency_code(v_business_group_id);
142
143 -- Introduced for bug fix 3107800
144 OPEN sob_currency_code_cur;
145 FETCH sob_currency_code_cur INTO g_sob_currency_code;
146 CLOSE sob_currency_code_cur;
147 -- End of bug fix 3107800
148
149 IF Check_for_valid_currency(v_batch_name,v_business_group_id,v_set_of_books_id) <> 0 Then
150 l_error_api_name := 'INVALID_CURRENCY';
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152 END IF;
153
154
155 l_currency_code := Get_Currency_For_Batch(v_batch_name,v_business_group_id,v_set_of_books_id);
156
157 psp_general.get_currency_precision(l_currency_code,l_precision,l_ext_precision);
158
159 -- calling procedure get_currency_precision to calculate precision based on currency_code
160 -- End Enh 2916848
161
162 -- Intorduced l_precisin,l_ext_precision,l_currency_code parameters in perform_validation for bug 2916848
163
164 If Perform_Validations(v_Batch_Name,v_business_group_id,v_set_of_books_id,l_precision,
165 l_ext_precision,l_currency_code) <> 0 Then
166 -- dbms_output.put_line('Errors occured during validation');
167 l_error_api_name := 'PERFORM VALIDATIONS';
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 End If;
170 -- Now, perform the IMPORT
171 -- Introduced l_precision,l_ext_precision l_currency_code variables for Bug 2916848
172
173 If Perform_Import(v_Batch_Name,v_business_group_id,v_set_of_books_id,
174 l_precision,l_ext_precision,l_currency_code) <> 0 Then
175 -- dbms_output.put_line('Errors occured during Import process');
176 l_error_api_name := 'IMPORT PROCESS';
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 End If;
179 -- COMMIT;
180
181 /* commit commented out to allow proper rollback in the event that problem occurs during change to_transfer
182 procedure */
183
184 -- Finally, change the statuses of all records in this batch to TRANSFER
185 If Change_To_Transfer(v_Batch_Name) <> 0 Then
186 -- dbms_output.put_line('Unable to change the statuses of all records to TRANSFER');
187 l_error_api_name := 'CHANGE TO TRANSFER STATUS';
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 End If;
190
191 COMMIT;
192 retCode := 0;
193 Exception
194 -- Enh. fix 2094036
195 WHEN RECORD_ALREADY_LOCKED THEN
196 fnd_message.set_name('PSP', 'PSP_PI_BATCH_IN_PROGRESS');
197 fnd_message.set_token('BATCH_NAME', v_batch_name);
198 l_dist_message := fnd_message.get;
199 errbuf := l_error_api_name || fnd_global.local_chr(10) || l_dist_message;
200 retcode:= 2;
201 -- End of Enh. fix 2094036
202 when FND_API.G_EXC_UNEXPECTED_ERROR Then
203 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
204 p_encoded => FND_API.G_FALSE,
205 p_data => l_msg_data,
206 p_msg_index_out => l_msg_count);
207 fnd_message.set_name('PSP','PSP_PI_IMPORT_GENERAL');
208 fnd_message.set_token('PROCEDURE_NAME',l_error_api_name);
209 l_dist_message := fnd_message.get;
210 errbuf := substr(l_error_api_name || fnd_global.local_chr(10) || l_msg_data || fnd_global.local_chr(10) || l_dist_message, 1, 232);
211 retCode := 2;
212 rollback;
213
214
215 when others then
216 -- dbms_output.put_line('Unknown Error ' || sqlerrm);
217 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA',l_error_api_name);
218 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
219 p_encoded => FND_API.G_FALSE,
220 p_data => l_msg_data,
221 p_msg_index_out => l_msg_count);
222 fnd_message.set_name('PSP','PSP_PI_IMPORT_GENERAL');
223 fnd_message.set_token('PROCEDURE_NAME',l_error_api_name);
224 l_dist_message := fnd_message.get;
225
226 errbuf := substr(l_error_api_name || fnd_global.local_chr(10) || l_msg_data || fnd_global.local_chr(10) || l_dist_message, 1, 232);
227 retCode := 2;
228 rollback;
229 End Imp_Rec;
230 /***********************************Change_To_Transfer************************************
231 OBJ: This is a private procedure (called internally, from Import_Records). This
232 is where the status of records imported are changed to 'TRANSFER'.
233 CREATED BY: AL ARUNACHALAM
234 DATE: 03/27/98
235 *****************************************************************************************/
236 Function Change_To_Transfer(v_Batch_Name IN varchar2) return Number IS
237 Cursor Change_Statuses Is
238 select STATUS_CODE
239 from PSP_PAYROLL_INTERFACE
240 where Batch_Name = v_Batch_Name
241 FOR UPDATE OF STATUS_CODE;
242 Change_Statuses_Agg Change_Statuses%RowType;
243 Begin
244 FOR Change_Statuses_Agg IN Change_Statuses LOOP
245 Update PSP_PAYROLL_INTERFACE set STATUS_CODE = 'T' where CURRENT OF Change_Statuses;
246 End Loop;
247 return 0;
248 Exception
249 when others then
250
251 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA', 'Change_To_Transfer');
252 return 2;
253 End Change_To_Transfer;
254 /*******************************Perform_Validations***************************************
255 OBJ: This is a private procedure (called internally, from Import_Records). This
256 is where the server side package PSP_VALID_NON_ORCL_PKG is called to perform
257 validations.
258 CREATED BY: AL ARUNACHALAM
259 DATE: 03/27/98
260 *****************************************************************************************/
261
262 Function Perform_Validations(v_Batch_Name IN VARCHAR2,
263 v_business_group_id IN NUMBER,
264 v_set_of_books_id IN NUMBER,
265 v_precision IN NUMBER,
266 v_ext_precision IN NUMBER,
267 v_currency_code IN VARCHAR2) return NUMBER IS
268 n_Valid_Records Number;
269 Begin
270 PSP_VALID_NON_ORCL_PKG.All_Records(v_Batch_Name,v_business_group_id,v_set_of_books_id,
271 v_precision,v_ext_precision,v_currency_code);
272 -- Next, check if any records have statuses other than Valid
273 select count(*)
274 into n_Valid_Records
275 from PSP_PAYROLL_INTERFACE
276 where Batch_Name = v_Batch_Name
277 and STATUS_CODE <> 'V';
278 -- If records do not have Valid status, then inform the user
279 -- and exit. Else, continue
280 If n_Valid_Records > 0 Then
281
282 -- dbms_output.put_line('Records are not all valid in this batch. Please validate the records
283 -- Non_Orcl Maintenance screen');
284 FND_MESSAGE.SET_NAME('PSP', 'PSP_PI_INVALID_RECORDS');
285 FND_MESSAGE.SET_TOKEN('BATCH_NAME', v_Batch_Name);
286 return (1);
287 Else
288 -- dbms_output.put_line('Server side validation done successfully. Now, on to import
289 -- process.');
290 return (0);
291 End If;
292 End Perform_Validations;
293 /*******************************Perform_Import*******************************************
294 OBJ: This is a private procedure (called internally, from Import_Records). This
295 is where data from PSP_PAYROLL_INTERFACE is imported to PSP_PAYROLL_CONTROLS. After
296 this has been done successfully, the Import_Payroll_Lines procedure is called.
297 CREATED BY: AL ARUNACHALAM
298 DATE: 03/27/98
299
300 -- Intoduced v_precision,v_ext_precision,v_currency_code for bug 2916848
301 *****************************************************************************************/
302 Function Perform_Import(v_Batch_Name IN varchar2,v_business_group_id IN NUMBER,
303 v_set_of_books_id IN NUMBER,v_precision IN NUMBER,
304 v_ext_precision IN NUMBER,v_currency_code IN VARCHAR2) return Number IS
305 cursor Control_Record IS
306 Select DISTINCT Payroll_Period_ID, Payroll_Source_Code,
307 GL_POSTING_OVERRIDE_DATE,GMS_POSTING_OVERRIDE_DATE
308 From PSP_PAYROLL_INTERFACE
309 Where Batch_Name = v_Batch_Name
310 And STATUS_CODE <> 'T';
311 n_Payroll_ID Number;
312
313 n_Number_Of_Credits Number;
314 n_Number_Of_Debits Number;
315 n_Credit_Amount Number;
316 n_Debit_Amount Number;
317 n_Payroll_Control_ID Number;
318 v_ROWID Varchar2(30);
319 n_Payroll_Action_ID Number := 1;
320 v_Rollback_Flag varchar2(30);
321 v_Rollback_Date DATE;
322 v_Sublines_CR_Amount Number;
323 v_Sublines_DR_Amount Number;
324
325 -- intorduced the following for Bug 2916848
326
327 CURSOR Time_period_end_date_cur(n_time_period_id IN NUMBER) IS
328 SELECT end_date
329 FROM PER_TIME_PERIODS
330 WHERE time_period_id = n_time_period_id;
331
332 l_exchange_rate_type psp_payroll_controls.exchange_rate_type%type;
333 l_end_date DATE;
334 l_currency_chk BOOLEAN := TRUE;
335
336 -- End of Bug 2916848
337
338
339 Begin
340
341 -- Performing the following currency_code check for population of Exchange_rate_type
342 -- for Bug 2916848
343
344 IF ((g_bg_currency_code = v_currency_code) AND (g_bg_currency_code = g_sob_currency_code)) THEN
345 l_currency_chk := FALSE;
346 END IF;
347
348 -- End of code for Bug 2916848
349
350 -- First, create Control Records in PSP_Payroll_Controls
351 -- Create a record for every unique Payroll_Period_ID, Payroll_Source_Code
352 For Control_Record_Agg IN Control_Record LOOP
353 -- Obtain the Payroll ID for every Payroll Period being added to the table
354 Begin
355 Select DISTINCT Payroll_ID
356 Into n_Payroll_ID
357 From PSP_PAYROLL_INTERFACE
358 where PAYROLL_PERIOD_ID = Control_Record_Agg.Payroll_Period_ID
359 and PAYROLL_SOURCE_CODE = Control_Record_Agg.Payroll_Source_Code
360 and BATCH_NAME = v_Batch_Name;
361 Exception
362 when too_many_rows then
363 -- dbms_output.put_line('Too many Payroll IDs returned for the Batch ' ||
364 -- v_batch_name || ' while creating control lines');
365
366 fnd_message.set_name('PSP','PSP_PI_MULTPL_PAYROLLS');
367 fnd_message.set_token('BATCH_NAME',v_batch_name);
368 fnd_msg_pub.add;
369 return 1;
370 when no_data_found then
371 fnd_message.set_name('PSP','PSP_PI_NO_PAYROLLS');
372 fnd_message.set_token('BATCH_NAME',v_batch_name);
373 fnd_msg_pub.add;
374 return 1;
375 End;
376
377 -- Introduced the following code to fetch exchange_rate_type for Bug 2916848
378 l_exchange_rate_type := NULL;
379
380 IF (l_currency_chk) THEN
381 open Time_period_end_date_cur(Control_Record_Agg.payroll_period_id);
382 fetch Time_period_end_date_cur into l_end_date;
383 close Time_period_end_date_cur;
384 l_exchange_rate_type := hruserdt.get_table_value(v_business_group_id,'EXCHANGE_RATE_TYPES',
385 'Conversion Rate Type','PAY' ,l_end_date);
386
387 END IF;
388
389
390 -- End of code for Bug 2916848
391
392
393
394 -- Next, obtain the Number of Credits, Debits, Credit Amount, and Debit amount
395 -- for every record being added
396 select Count(DR_CR_FLAG), SUM(ROUND(PAY_AMOUNT,v_precision))
397 into n_Number_Of_Credits, n_Credit_Amount
398 from PSP_PAYROLL_INTERFACE
399 where Payroll_Period_ID = Control_Record_Agg.Payroll_Period_ID
400 and Payroll_Source_Code = Control_Record_Agg.Payroll_Source_Code
401 and Batch_Name = v_Batch_Name
402 and UPPER(DR_CR_FLAG) = 'C';
403
404 select Count(DR_CR_FLAG), SUM(ROUND(PAY_AMOUNT,v_precision))
405 into n_Number_Of_Debits, n_Debit_Amount
406 from PSP_PAYROLL_INTERFACE
407 where Payroll_Period_ID = Control_Record_Agg.Payroll_Period_ID
408 and Payroll_Source_Code = Control_Record_Agg.Payroll_Source_Code
409 and Batch_Name = v_Batch_Name
410 and UPPER(DR_CR_FLAG) = 'D';
411
412 -- Also, obtain the Total Sublines CR Amount and Total Sublines DR Amount for Batch
413 Select SUM(ROUND(PAY_AMOUNT,v_precision))
414 into v_Sublines_CR_Amount
415 from PSP_PAYROLL_INTERFACE
416 where Batch_Name = v_Batch_Name
417 -- Introduced Time Period and Source Code check for bug fix 3116383
418 AND payroll_period_id = control_record_agg.payroll_period_id
419 AND payroll_source_code = control_record_agg.payroll_source_code
420 and UPPER(DR_CR_FLAG) = 'C';
421
422 Select SUM(ROUND(PAY_AMOUNT,v_precision))
423 into v_Sublines_DR_Amount
424 from PSP_PAYROLL_INTERFACE
425 where Batch_Name = v_Batch_Name
426 -- Introduced Time Period and Source Code check for bug fix 3116383
427 AND payroll_period_id = control_record_agg.payroll_period_id
428 AND payroll_source_code = control_record_agg.payroll_source_code
429 and UPPER(DR_CR_FLAG) = 'D';
430
431
432 -- Insert Payroll Control Records into PSP_Payroll_Controls table
433 Select PSP_PAYROLL_CONTROLS_S.NextVal
434 into n_Payroll_Control_ID
435 from DUAL;
436
437 PSP_PAYROLL_CONTROLS_PKG.INSERT_ROW (
438 X_ROWID => v_ROWID,
439 X_PAYROLL_CONTROL_ID => n_Payroll_Control_ID,
440 X_PAYROLL_ACTION_ID => n_Payroll_Action_ID,
441 X_PAYROLL_SOURCE_CODE => Control_Record_Agg.Payroll_Source_Code,
442 X_SOURCE_TYPE => 'N',
443 X_PAYROLL_ID => n_Payroll_ID,
444 X_TIME_PERIOD_ID => Control_Record_Agg.Payroll_Period_ID,
445 X_NUMBER_OF_CR => n_Number_Of_Credits,
446 X_NUMBER_OF_DR => n_Number_Of_Debits,
447 X_TOTAL_DR_AMOUNT => n_Debit_Amount,
448 X_TOTAL_CR_AMOUNT => n_Credit_Amount,
449 -- X_ROLLBACK_FLAG => v_Rollback_Flag,
450 -- X_ROLLBACK_DATE => v_Rollback_Date,
451 X_BATCH_NAME => v_Batch_Name,
452 X_SUBLINES_DR_AMOUNT => v_Sublines_DR_Amount,
453 X_SUBLINES_CR_AMOUNT => v_Sublines_CR_Amount,
454 -- X_DISTRIBUTION_AMOUNT => NULL,
455 X_DIST_DR_AMOUNT => NULL,
456 X_DIST_CR_AMOUNT => NULL,
457 X_OGM_DR_AMOUNT => NULL,
458 X_OGM_CR_AMOUNT => NULL,
459 X_GL_DR_AMOUNT => NULL,
460 X_GL_CR_AMOUNT => NULL,
461 X_STATUS_CODE => 'N',
462 X_MODE => 'R' ,
463 X_GL_POSTING_OVERRIDE_DATE => Control_Record_Agg.GL_POSTING_OVERRIDE_DATE,
464 X_GMS_POSTING_OVERRIDE_DATE => Control_Record_Agg.GMS_POSTING_OVERRIDE_DATE,
465 X_set_of_books_id => v_set_of_books_id,
466 X_business_group_id => v_business_group_id,
467 X_GL_phase => NULL,
468 X_GMS_PHASE => NULL,
469 X_ADJ_SUM_BATCH_NAME => NULL,
470 X_CURRENCY_CODE => v_currency_code,
471 X_EXCHANGE_RATE_TYPE => l_exchange_rate_type);
472
473 -- dbms_output.put_line('Insert to PAYROLL_CONTROLS table done successfully');
474 END LOOP;
475 -- Call the Import_Payroll_Lines function to import data to Payroll_Lines and
476 -- to Payroll_Sub_Lines
477 retVal := Import_Payroll_Lines(v_Batch_Name,v_business_group_id,v_set_of_books_id,
478 v_precision,v_ext_precision);
479 -- Introduced v_precision,v_ext_precision for import_payroll_lines call for bug 2916848
480
481 If retVal <> 0 Then
482 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
483 Else
484 -- dbms_output.put_line('Import to Payroll Controls table done completely');
485 return 0;
486 End If;
487 EXCEPTION
488 when FND_API.G_EXC_UNEXPECTED_ERROR Then
489 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA','IMPORT_PERFORM_IMPORT');
490 return 3;
491 when OTHERS Then
492 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA','IMPORT_PERFORM_IMPORT');
493 return 2;
494 End Perform_Import;
495 /*****************************Import_Payroll_Lines***************************************
496 OBJ: This is a private procedure (called internally, from Perform_Import). This procedure
497 inserts records to the PSP_PAYROLL_LINES table and creates corresponding sub_lines
498
499 by calling the Process_Payroll_Sub_Lines procedure.
500 ASSUMPTIONS: The foll. fields that are to be entered to the Payroll Lines tables have been left
501 empty (ref: Subbarao, date: 03/27/98)
502 COST_ID, COST_ALLOCATION_KEYFLEX_ID, GL_CODE_COMBINATION_ID, BALANCE_AMOUNT
503 CREATED BY: AL ARUNACHALAM
504 DATE: 03/27/98
505
506 -- Intoduced v_precision parameter for Bug 2916848
507 *****************************************************************************************/
508 Function Import_Payroll_Lines(v_Batch_Name IN varchar2,v_business_group_id IN NUMBER,
509 v_set_of_books_id IN NUMBER,
510 v_precision IN NUMBER,v_ext_precision IN NUMBER) return Number IS
511 cursor Lines_Record IS
512 Select DISTINCT Payroll_Period_ID, Assignment_ID, Element_Type_ID, Payroll_Source_Code,
513 Sub_Line_Start_Date, Sub_Line_End_Date
514 From PSP_PAYROLL_INTERFACE
515 Where Batch_Name = v_Batch_Name
516 And STATUS_CODE <> 'T';
517 Lines_Record_Agg Lines_Record%ROWTYPE;
518 v_ROWID varchar2(30);
519 n_Payroll_ID Number;
520 n_Payroll_Control_ID Number;
521 n_Payroll_Lines_ID Number;
522 n_Set_Of_Books_ID Number;
523 d_Effective_Date DATE;
524 n_Person_ID Number;
525 n_Cost_ID Number;
526 n_Pay_Amount Number;
527
528 d_Check_Date Date;
529 d_Earned_Date Date;
530 v_DR_CR_Flag varchar2(1);
531 n_Cost_Allocation_KeyFlex_ID Number;
532 n_GL_Code_Combination_ID Number;
533 n_Balance_Amount Number;
534
535 -- v_Set_Of_Books_ID varchar2(30);
536 Begin
537 -- dbms_output.put_line('About to insert Payroll Lines');
538 -- Next, create Payroll Line records in PSP_Payroll_Lines table
539 -- Create a record for every unique Payroll_Period_ID, Assignment_ID, Element_Type_ID
540 FOR Lines_Record_Agg IN Lines_Record LOOP
541 -- Obtain foreign key reference from PSP_Payroll_Controls table
542 BEGIN
543 select DISTINCT PAYROLL_CONTROL_ID
544 into n_Payroll_Control_ID
545 from PSP_PAYROLL_CONTROLS
546 where TIME_PERIOD_ID = Lines_Record_Agg.Payroll_Period_ID
547 and PAYROLL_SOURCE_CODE = Lines_Record_Agg.Payroll_Source_Code
548 and BATCH_NAME = v_Batch_Name
549 and business_group_id = v_business_group_id
550 and set_of_books_id = v_set_of_books_id;
551
552 EXCEPTION
553 when too_many_rows then
554 fnd_message.set_name('PSP','PSP_PI_MULTPL_PAYROLL_CNTRL_ID');
555 fnd_message.set_token('PAYROLL_PERIOD_ID', to_char(Lines_Record_Agg.Payroll_Period_ID));
556 fnd_message.set_token( 'PAYROLL_SOURCE_CODE', Lines_Record_Agg.Payroll_Source_Code);
557 fnd_msg_pub.add;
558 return 1;
559 END;
560 /* Bug 4155144 - commented this block since the same check has already been done when inserting the control record
561 -- Obtain Payroll ID for a given Payroll Period and the non-oracle source code
562 BEGIN
563 Select DISTINCT Payroll_ID
564 Into n_Payroll_ID
565 From PSP_PAYROLL_INTERFACE
566 where PAYROLL_PERIOD_ID = Lines_Record_Agg.Payroll_Period_ID
567 and PAYROLL_SOURCE_CODE = Lines_Record_Agg.Payroll_Source_Code
568 and BATCH_NAME = v_Batch_Name;
569 -- dbms_output.put_line('Payroll ID ' || to_Char(n_Payroll_ID) || ' obtained');
570 EXCEPTION
571 when OTHERS then
572 fnd_message.set_name('PSP', 'PSP_PI_INV_PAYROLL_FOR_PERIOD');
573 fnd_message.set_token('PAYROLL_PERIOD', to_char(Lines_Record_Agg.Payroll_Period_ID));
574 fnd_message.set_token('PAYROLL_SOURCE', Lines_Record_Agg.Payroll_Source_Code);
575 fnd_msg_pub.add;
576 return 1;
577 END;
578
579 */
580 -- Obtain information from PSP_PAYROLL_INTERFACE table that is to be inserted into the
581 -- PSP_PAYROLL_LINES table
582 -- n_payroll_id Added for bug fix 4179476
583 BEGIN
584 Select DISTINCT Effective_Date, Person_ID, round(Pay_Amount,v_precision),
585 Check_Date, Earned_Date, UPPER(DR_CR_Flag), payroll_id
586 Into d_Effective_Date, n_Person_ID, n_Pay_Amount, d_Check_Date, d_Earned_Date,
587 v_DR_CR_Flag, n_payroll_id
588 From PSP_PAYROLL_INTERFACE
589 where PAYROLL_PERIOD_ID = Lines_Record_Agg.Payroll_Period_ID
590 and PAYROLL_SOURCE_CODE = Lines_Record_Agg.Payroll_Source_Code
591 --Condition Droped for bug fix 4179476
592 -- and PAYROLL_ID = n_Payroll_ID
593 and ASSIGNMENT_ID = Lines_Record_Agg.Assignment_ID
594 and ELEMENT_TYPE_ID = Lines_Record_Agg.Element_Type_ID
595 and SUB_LINE_START_DATE = Lines_Record_Agg.Sub_Line_Start_Date
596 and SUB_LINE_END_DATE = Lines_Record_Agg.Sub_Line_End_Date
597 and BATCH_NAME = v_Batch_Name;
598 EXCEPTION
599 when OTHERS then
600 fnd_message.set_name('PSP', 'PSP_PI_MUL_REC_FOR_PER_ASS_EL');
601 fnd_message.set_token('PAYROLL_PERIOD', to_char(Lines_Record_Agg.Payroll_Period_ID));
602 fnd_message.set_token('PAYROLL_SOURCE', Lines_Record_Agg.Payroll_Source_Code);
603 fnd_message.set_token('ASSIGNMENT', to_char(Lines_Record_Agg.Assignment_ID));
604 fnd_message.set_token('ELEMENT_TYPE', to_char(Lines_Record_Agg.Element_Type_ID));
605 fnd_msg_pub.add;
606 return 1;
607 END;
608
609 select Cost_Allocation_KeyFlex_ID
610 into n_Cost_Allocation_KeyFlex_ID
611 from PAY_PAYROLLS_F a
612 where a.PAYROLL_ID = n_Payroll_ID
613 and d_Effective_Date BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
614 and a.business_group_id = v_business_group_id;
615
616 -- obtain the primary key for the PSP_PAYROLL_LINES table from the DUAL table
617 select PSP_PAYROLL_LINES_S.NextVal
618 into n_Payroll_Lines_ID
619 from DUAL;
620
621 /*********************************************************************************************
622 -- Commented out the following code bcos we no longer want to obtain GL_CCID from the complex
623 -- procedure below. Instead, we want to obtain GL_CCID from PSP_Clearing_Account (Venkat 06/24)
624 -- Next, obtain the Set_Of_Books_ID from Pay_Payrolls_F
625 -- Obtain Cost Allocation Key Flex ID, GL_Code_Combination_ID, and
626 -- Balance_Amount using Venkat's procedure
627 -- dbms_output.put_line('Running Venkat''s procedure for GL CCID');
628 v_Set_Of_Books_ID := FND_Profile.Value('PSP_SET_OF_BOOKS');
629
630 If (v_Set_Of_Books_ID IS NULL) or (to_number(v_Set_Of_Books_ID) <> n_Set_Of_Books_ID) Then
631 -- dbms_output.put_line('Profile value for Set of Books ID :' ||
632 -- v_Set_Of_Books_ID || ' does not match value from PAY_PAYROLLS_F. Cannot
633 -- proceed');
634 fnd_message.set_name('PSP', 'PSP_PI_INVALID_SET_OF_BOOKS');
635 fnd_msg_pub.add;
636 return 2;
637 End If;
638
639 PSP_General.get_GL_CCID(P_Payroll_ID => n_Payroll_ID, P_Set_Of_Books_ID =>
640 n_Set_Of_Books_ID, P_Cost_KeyFlex_ID => n_Cost_Allocation_KeyFlex_ID,
641 x_GL_CCID => n_GL_Code_Combination_ID);
642
643 If n_GL_Code_Combination_ID IS NULL or n_GL_Code_Combination_ID = 0 Then
644 -- dbms_output.put_line('GL Code Combination ID is invalid. Cannot proceed');
645 fnd_message.set_name('PSP', 'PSP_INVALID_GL_CCID');
646 fnd_msg_pub.add;
647 return 2;
648 End If;
649 ******************************************************************************************/
650 Begin
651 Select reversing_gl_ccid
652 into n_GL_Code_Combination_ID
653 from PSP_CLEARING_ACCOUNT a
654 where a.business_group_id = v_business_group_id
655 and a.set_of_books_id = v_set_of_books_id
656 and a.payroll_id = n_payroll_id; -- Added for bug 5592964
657
658 If n_GL_Code_Combination_ID IS NULL or n_GL_Code_Combination_ID = 0 Then
659 -- dbms_output.put_line('GL Code Combination ID is invalid. Cannot proceed');
660 fnd_message.set_name('PSP', 'PSP_NO_CLEARING_ACCOUNT');
661 fnd_msg_pub.add;
662 return 2;
663 End If;
664
665 Exception
666 when OTHERS Then
667 fnd_message.set_name('PSP', 'PSP_NO_CLEARING_ACCOUNT');
668 fnd_msg_pub.add;
669 return 2;
670 End;
671
672 -- v_Set_Of_Books_ID := FND_Profile.Value('PSP_SET_OF_BOOKS');
673
674 If (v_Set_Of_Books_ID IS NULL) Then
675 -- dbms_output.put_line('Profile value for Set of Books ID :' ||
676 -- v_Set_Of_Books_ID || ' does not match value from PAY_PAYROLLS_F. Cannot
677 -- proceed');
678 fnd_message.set_name('PSP', 'PSP_PI_INVALID_SET_OF_BOOKS');
679 fnd_msg_pub.add;
680 return 2;
681 End If;
682 -- n_Set_Of_Books_ID := to_number(v_set_of_books_id);
683
684 -- dbms_output.put_line('Obtained GL CCID. Now, inserting to Payroll Lines');
685 -- Now, insert rows to the PSP_PAYROLL_LINES table
686 PSP_PAYROLL_LINES_PKG.INSERT_ROW (
687 X_ROWID => v_ROWID,
688 X_PAYROLL_LINE_ID => n_Payroll_Lines_ID,
689 X_PAYROLL_CONTROL_ID => n_Payroll_Control_ID,
690 X_SET_OF_BOOKS_ID => v_Set_Of_Books_ID,
691 X_ASSIGNMENT_ID => Lines_Record_Agg.Assignment_ID,
692 X_PERSON_ID => n_Person_ID,
693 X_COST_ID => n_Cost_ID,
694 X_ELEMENT_TYPE_ID => Lines_Record_Agg.Element_Type_ID,
695 X_PAY_AMOUNT => n_Pay_Amount,
696 X_STATUS_CODE => 'N',
697 X_EFFECTIVE_DATE => d_Effective_Date,
698 X_CHECK_DATE => d_Check_Date,
699 X_EARNED_DATE => d_Earned_Date,
700 X_COST_ALLOCATION_KEYFLEX_ID => n_Cost_Allocation_KeyFlex_ID,
701 X_GL_CODE_COMBINATION_ID => n_GL_Code_Combination_ID,
702 X_BALANCE_AMOUNT => n_Balance_Amount,
703 X_DR_CR_FLAG => v_DR_CR_Flag,
704 X_MODE => 'R'
705 );
706 -- Finally, create Payroll Sub line records in PSP_Payroll_Sub_Lines table
707 -- Create a record in PSP_payroll_sub_lines table for every record in
708 -- PSP_Payroll_Interface table
709 -- dbms_output.put_line('Inserted into Payroll Lines. Now, inserting to Payroll Sub
710 -- lines');
711 retVal := Process_Payroll_Sub_Lines(v_batch_name, Lines_Record_Agg.Payroll_Period_ID,
712 Lines_Record_Agg.Assignment_ID, Lines_Record_Agg.Element_Type_ID,
713 n_Payroll_Lines_ID, Lines_Record_Agg.Sub_Line_Start_Date,
714 Lines_Record_Agg.Sub_Line_End_Date,v_precision,v_ext_precision,
715 v_business_group_id); -- Introduced BG for bug 2908859
716 If retVal <> 0 Then
717 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
718 End If;
719 END LOOP;
720 return 0;
721 EXCEPTION
722 when FND_API.G_EXC_UNEXPECTED_ERROR Then
723 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA', 'IMPORT_PAYROLL_LINES');
724 return 3;
725 when others then
726 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA', 'IMPORT_PAYROLL_LINES');
727 return 2;
728 End Import_Payroll_Lines;
729 /*****************************Process_Payroll_Sub_Lines************************************
730 OBJ: This is a private procedure (called internally, from Import_Payroll_Lines). This procedure
731 inserts records to the PSP_PAYROLL_SUB_LINES table and creates corresponding sub_lines
732 by calling the Import_Payroll_Sub_Lines procedure.
733 ASSUMPTIONS: The foll. fields that are to be entered to the Payroll Lines tables have been left
734 empty (ref: Subbarao, date: 03/27/98)
735 ORGANIZATION_ID, JOB_ID, POSITION_ID, EMP_BEGIN_DATE, EMP_END_DATE, EMP_STATUS_INACTIVE_DATE,EMP_STATUS_ACTIVE_DATE, ASSIGNMENT_BEGIN_DATE, ASSIGNMENT_END_DATE
736 CREATED BY: AL ARUNACHALAM
737 DATE: 03/27/98
738 *****************************************************************************************/
739 Function Process_Payroll_Sub_Lines(v_Batch_Name varchar2, n_Payroll_Period_ID number,
740 n_Assignment_ID Number, n_Element_Type_Id Number, n_Payroll_Lines_ID Number,
741 d_Sub_Line_Start_Date DATE, d_Sub_Line_End_Date DATE,
742 v_precision IN NUMBER,v_ext_precision IN NUMBER,
743 v_business_group_id IN NUMBER) -- Introduced BG for bug 2908859
744 return Number IS
745 cursor Sub_Lines_Record IS
746 Select *
747 From PSP_PAYROLL_INTERFACE
748 where Batch_Name = v_batch_name
749 and PAYROLL_PERIOD_ID = n_Payroll_Period_ID
750 and ASSIGNMENT_ID = n_Assignment_ID
751 and ELEMENT_TYPE_ID = n_Element_Type_ID
752 and SUB_LINE_START_DATE = d_Sub_Line_Start_Date
753 and SUB_LINE_END_DATE = d_Sub_Line_End_Date
754 and STATUS_CODE <> 'T';
755 Sub_Lines_Record_Agg Sub_Lines_Record%ROWTYPE;
756 v_RowID varchar2(30);
757 n_Payroll_Sub_Lines_ID Number;
758 n_Current_Salary Number;
759 n_Organization_ID Number;
760 n_Job_ID Number;
761 n_Position_ID Number;
762 d_Employment_Begin_Date Date;
763 d_Employment_End_Date Date;
764 d_Status_Inactive_Date Date;
765 d_Status_Active_Date Date;
766 d_Assignment_Begin_Date Date;
767 d_Assignment_End_Date Date;
768 -- commented following line for 4992668
769 ---l_dff_grouping_option VARCHAR2(1) DEFAULT psp_general.get_act_dff_grouping_option(v_business_group_id); -- Introduced for bug 2908859
770 l_attribute_category VARCHAR2(30);
771 l_attribute1 VARCHAR2(150);
772 l_attribute2 VARCHAR2(150);
773 l_attribute3 VARCHAR2(150);
774 l_attribute4 VARCHAR2(150);
775 l_attribute5 VARCHAR2(150);
776 l_attribute6 VARCHAR2(150);
777 l_attribute7 VARCHAR2(150);
778 l_attribute8 VARCHAR2(150);
779 l_attribute9 VARCHAR2(150);
780 l_attribute10 VARCHAR2(150);
781 BEGIN
782 FOR Sub_Lines_Record_Agg IN Sub_Lines_Record LOOP
783 -- The Payroll Lines ID key is the current value of n_Payroll_Lines_ID
784 -- Obtain the primary key for the PSP_PAYROLL_SUB_LINES table from DUAL
785 select PSP_PAYROLL_SUB_LINES_S.nextval
786 into n_Payroll_Sub_Lines_ID
787 from DUAL;
788 -- Insert records to the PSP_PAYROLL_SUB_LINES table
789 -- Leave Organization_ID, Job_ID, Position_ID, Employment_Begin_Date,
790 -- dbms_output.put_line('Inserting into payroll sub lines table');
791 -- Employment_End_Date, Assignment Date, etc empty (Venkat 03/19/98)
792 -- Introduced extended precision for daily_rate for Bug2916948
793 -- Introduced currency_precision for pay_amount,salary_used for Bug2916848
794
795 -- Introduced the folowing for bug fix 2908859
796 ----IF (l_dff_grouping_option = 'Y') THEN --- commented for 4992668
797 l_attribute_category := sub_lines_record_agg.attribute_category;
798 l_attribute1 := sub_lines_record_agg.attribute1;
799 l_attribute2 := sub_lines_record_agg.attribute2;
800 l_attribute3 := sub_lines_record_agg.attribute3;
801 l_attribute4 := sub_lines_record_agg.attribute4;
802 l_attribute5 := sub_lines_record_agg.attribute5;
803 l_attribute6 := sub_lines_record_agg.attribute6;
804 l_attribute7 := sub_lines_record_agg.attribute7;
805 l_attribute8 := sub_lines_record_agg.attribute8;
806 l_attribute9 := sub_lines_record_agg.attribute9;
807 l_attribute10 := sub_lines_record_agg.attribute10;
808 ---END IF;
809 -- End of changes for bug fix 2908859
810
811 retVal := Import_Payroll_Sub_Lines(v_RowID, n_Payroll_Sub_Lines_ID, n_Payroll_Lines_ID,
812 Sub_Lines_Record_Agg.Sub_Line_Start_Date,
813 Sub_Lines_Record_Agg.Sub_Line_End_Date,
814 Sub_Lines_Record_Agg.Reason_Code,
815 ROUND(Sub_Lines_Record_Agg.Pay_Amount,v_precision),
816 ROUND(Sub_Lines_Record_Agg.Daily_Rate,v_ext_precision),
817 ROUND(Sub_Lines_Record_Agg.Salary_Used,v_precision),
818 n_Current_Salary, Sub_Lines_Record_Agg.FTE, n_Organization_ID, n_Job_ID,
819 n_Position_ID, d_Employment_Begin_Date, d_Employment_End_Date,
820 d_Status_Inactive_Date, d_Status_Active_Date, d_Assignment_Begin_Date,
821 d_Assignment_End_Date,
822 l_attribute_category, -- Introduced DFF columns for bug 2908859
823 l_attribute1, l_attribute2, l_attribute3, l_attribute4, l_attribute5,
824 l_attribute6, l_attribute7, l_attribute8, l_attribute9, l_attribute10);
825 if retVal <> 0 Then
826 -- dbms_output.put_line('Error occured while inserting sub-line');
827 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
828 else
829 -- dbms_output.put_line('Successfully imported Payroll Sub Line');
830 null;
831 end if;
832 END LOOP;
833 return 0;
834 EXCEPTION
835 when FND_API.G_EXC_UNEXPECTED_ERROR Then
836 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA', 'PROCESS_PAYROLL_SUB_LINES');
837 return 3;
838 when others then
839 -- dbms_output.put_line('Error occured while processing sub-lines. Error Message' ||
840 -- sqlerrm);
841 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA', 'PROCESS_PAYROLL_SUB_LINES');
842 return 2;
843 END Process_Payroll_Sub_Lines;
844
845 Function Import_Payroll_Sub_Lines(v_RowID IN OUT NOCOPY varchar2, n_Payroll_Sub_Lines_ID number,
846 n_Payroll_Lines_ID Number, d_Sub_Line_Start_Date DATE,
847 d_Sub_Line_End_Date Date, v_Reason_Code varchar2, n_Pay_Amount
848 Number, n_Daily_Rate Number, n_Salary_Used Number,
849 n_Current_Salary Number, n_FTE Number, n_Organization_ID Number,
850 n_Job_ID Number, n_Position_ID Number,
851 d_Employment_Begin_Date Date, d_Employment_End_Date Date,
852 d_Status_Inactive_Date Date, d_Status_Active_Date Date,
853 d_Assignment_Begin_Date Date, d_Assignment_End_Date Date,
854 -- Introduced DFF column parameters for bug 2908859
855 p_attribute_category IN VARCHAR2, p_attribute1 IN VARCHAR2,
856 p_attribute2 IN VARCHAR2, p_attribute3 IN VARCHAR2,
857 p_attribute4 IN VARCHAR2, p_attribute5 IN VARCHAR2,
858 p_attribute6 IN VARCHAR2, p_attribute7 IN VARCHAR2,
859 p_attribute8 IN VARCHAR2, p_attribute9 IN VARCHAR2,
860 p_attribute10 IN VARCHAR2)
861 Return Number IS
862 Begin
863 PSP_PAYROLL_SUB_LINES_PKG.INSERT_ROW (
864 X_ROWID => v_RowID,
865 X_PAYROLL_SUB_LINE_ID => n_Payroll_Sub_Lines_ID,
866 X_PAYROLL_LINE_ID => n_Payroll_Lines_ID,
867 X_SUB_LINE_START_DATE => d_Sub_Line_Start_Date,
868 X_SUB_LINE_END_DATE => d_Sub_Line_End_Date,
869 X_REASON_CODE => v_Reason_Code,
870 X_PAY_AMOUNT => n_Pay_Amount,
871 X_DAILY_RATE => n_Daily_Rate,
872 X_SALARY_USED => n_Salary_Used,
873 X_CURRENT_SALARY => n_Current_Salary,
874 X_FTE => n_FTE,
875 X_ORGANIZATION_ID => n_Organization_ID,
876 X_JOB_ID => n_Job_ID,
877 X_POSITION_ID => n_Position_ID,
878 X_GRADE_ID => NULL, --- Bug Fix 2023955
879 X_PEOPLE_GRP_ID => NULL,
880 X_EMPLOYMENT_BEGIN_DATE => d_Employment_Begin_Date,
881 X_EMPLOYMENT_END_DATE => d_Employment_End_Date,
882 X_EMPLOYEE_STATUS_INACTIVE_DAT => d_Status_Inactive_Date,
883 X_EMPLOYEE_STATUS_ACTIVE_DATE => d_Status_Active_Date,
884 X_ASSIGNMENT_BEGIN_DATE => d_Assignment_Begin_Date,
885 X_ASSIGNMENT_END_DATE => d_Assignment_End_Date,
886 X_ATTRIBUTE_CATEGORY => p_attribute_category, -- Introduced DFF column parameters for bug 2908859
887 X_ATTRIBUTE1 => p_attribute1,
888 X_ATTRIBUTE2 => p_attribute2,
889 X_ATTRIBUTE3 => p_attribute3,
890 X_ATTRIBUTE4 => p_attribute4,
891 X_ATTRIBUTE5 => p_attribute5,
892 X_ATTRIBUTE6 => p_attribute6,
893 X_ATTRIBUTE7 => p_attribute7,
894 X_ATTRIBUTE8 => p_attribute8,
895 X_ATTRIBUTE9 => p_attribute9,
896 X_ATTRIBUTE10 => p_attribute10,
897 X_MODE => 'R'
898 );
899 -- dbms_output.put_line('Insert of row to PSP_Payroll_Sub_Lines table done successfully');
900 return 0;
901 Exception
902 when others then
903 -- dbms_output.put_line('Error occured while inserting sub-lines. Error Message' ||
904 -- sqlerrm);
905 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA', 'IMPORT_PAYROLL_SUB_LINES');
906 return 2;
907 End Import_Payroll_Sub_Lines;
908
909
910 FUNCTION Check_For_Valid_Batches(v_Batch_Name IN VARCHAR2,
911 v_business_group_id IN NUMBER,
912 v_set_of_books_id IN NUMBER ) return NUMBER IS
913 -- Check if any invalid batch names exist in PSP_PAYROLL_INTERFACE
914 -- i.e. check if any non-transferred batch exists in PSP_PAYROLL_INTERFACE
915 -- that already exists in PSP_PAYROLL_CONTROLS.
916 -- If it does, then inform the user about the error and exit.
917 cursor C1 is
918 /***** Modified the following cursor defn for R12 performance fixes (bug 4507892)
919 Select DISTINCT a.batch_name batch_name
920 from PSP_PAYROLL_INTERFACE a,
921 PSP_PAYROLL_CONTROLS b
922 where a.batch_name = b.batch_name
923 and a.status_code <> 'T'
924 and b.SOURCE_TYPE = 'N'
925 and a.BATCH_NAME = v_Batch_Name
926 and b.business_group_id = v_business_group_id
927 and b.set_of_books_id = v_set_of_books_id;
928 End of comment for bug fix 4507892 *****/
929 -- New cursor defn for bug fix 4507892
930 SELECT ppi.batch_name batch_name
931 FROM psp_payroll_interface ppi
932 WHERE ppi.batch_name = v_batch_name
933 AND ppi.business_group_id = v_business_group_id
934 AND ppi.set_of_books_id = v_set_of_books_id
935 AND ppi.status_code <> 'T'
936 AND EXISTS (SELECT 1
937 FROM psp_payroll_controls ppc
938 WHERE ppc.batch_name = v_batch_name
939 AND ppc.SOURCE_TYPE = 'N'
940 AND ppc.business_group_id = v_business_group_id
941 AND ppc.set_of_books_id = v_set_of_books_id);
942
943 C1_Batch_Name PSP_PAYROLL_INTERFACE.batch_name%TYPE;
944 BEGIN
945 Open C1;
946 LOOP
947 Fetch C1 INTO C1_Batch_Name;
948 Exit when c1%NOTFOUND;
949 Exit;
950 END LOOP;
951 Close C1;
952
953 If C1_Batch_Name IS NOT NULL Then
954 fnd_message.set_name('PSP', 'PSP_PI_INVALID_BATCH_NAME');
955 fnd_message.set_token('PSP_BATCH_NAME', C1_Batch_Name);
956 fnd_msg_pub.add;
957 return 2;
958 End If;
959
960 return 0;
961 Exception
962 when OTHERS then
963 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA', 'PSP_PI_INVALID_BATCH_NAME');
964 return 2;
965 End Check_For_Valid_Batches;
966
967 -- Introduced the function check_for_valid_currency to check whether a batch has got more than one currency
968 -- for Bug 2916848
969
970 FUNCTION Check_For_Valid_Currency(v_batch_name in VARCHAR2,v_business_group_id IN NUMBER,
971 v_set_of_books_id IN NUMBER) return NUMBER IS
972
973
974 CURSOR Count_currency_code_cur IS
975 SELECT COUNT(DISTINCT(NVL(currency_code,'*')))
976 FROM PSP_PAYROLL_INTERFACE
977 WHERE batch_name = v_batch_name
978 AND business_group_id = v_business_group_id
979 AND set_of_books_id = v_set_of_books_id;
980
981 l_count_currency NUMBER;
982
983 BEGIN
984
985 OPEN Count_currency_code_cur;
986 FETCH Count_currency_code_cur into l_count_currency;
987 CLOSE Count_currency_code_cur;
988
989 IF (l_count_currency >1 ) then
990 fnd_message.set_name ('PSP','PSP_PI_INVALID_CURRENCY');
991 fnd_message.set_token('BATCH_NAME',v_batch_name);
992 fnd_msg_pub.add;
993 return 2;
994 END IF;
995
996 return 0;
997
998 EXCEPTION
999
1000 when others then
1001 fnd_msg_pub.add_exc_msg('PSP_PI_IMPORT_DATA','PSP_PI_INVALID_CURRENCY');
1002 return 2;
1003
1004 END Check_For_Valid_Currency;
1005 /**** end of check for valid currency *******/
1006
1007 -- Introduced function get currency for batch for Bug 2916848
1008
1009 FUNCTION Get_Currency_For_Batch(v_batch_name IN VARCHAR2,v_business_group_id IN NUMBER,
1010 v_set_of_books_id IN NUMBER) return VARCHAR2 IS
1011
1012
1013 CURSOR get_currency_code_cur IS
1014 SELECT DISTINCT(NVL(currency_code,g_bg_currency_code))
1015 FROM PSP_PAYROLL_INTERFACE
1016 WHERE batch_name = v_batch_name
1017 AND business_group_id = v_business_group_id
1018 AND set_of_books_id = v_set_of_books_id
1019 AND rownum = 1;
1020
1021 l_currency_code psp_payroll_interface.currency_code%type;
1022
1023
1024 BEGIN
1025
1026 OPEN get_currency_code_cur;
1027 FETCH get_currency_code_cur INTO l_currency_code;
1028 CLOSE get_currency_code_cur;
1029
1030 return (l_currency_code);
1031
1032 END Get_Currency_For_Batch;
1033
1034
1035
1036
1037 END;
[Home] [Help]