[Home] [Help]
PACKAGE BODY: APPS.PSP_PREGEN
Source
1 PACKAGE BODY PSP_PREGEN AS
2 /* $Header: PSPLDPGB.pls 120.8 2007/01/26 16:38:34 spchakra noship $ */
3 --
4
5 -- Introduced the following for bug fix 2916848
6 g_bg_currency_code psp_payroll_controls.currency_code%TYPE; -- Business Group Currency
7 g_sob_currency_code gl_sets_of_books.currency_code%TYPE; -- SOB Currency (Introduced for bug 3107800)
8 g_currency_code psp_payroll_controls.currency_code%TYPE; -- Batch Currency
9 g_precision NUMBER;
10 g_ext_precision NUMBER;
11 g_pop_exchange_rate_type BOOLEAN DEFAULT TRUE; -- Identifies if Exchange Rate Type has to be populated
12 -- End of bug fix 2916848
13
14 g_pregen_autopop varchar2(1); --- 2 vars for 5080403
15 g_suspense_autopop varchar2(1);
16
17 /* deleted code for get_clearing_ccid, bug 2007521 */
18 Procedure get_suspense_account(p_organization_id in number,
19 p_organization_name in varchar2,
20 p_effective_date in date,
21 p_gms_pa_install in varchar2,
22 p_person_id in number,
23 p_business_group_id in number,
24 p_set_of_books_id in number,
25 p_distribution_interface_id in number,
26 x_suspense_account out NOCOPY number,
27 x_return_status out NOCOPY varchar2,
28 x_suspense_auto_glccid out nocopy number,
29 x_suspense_auto_exp_type out nocopy varchar2);
30 Procedure stick_suspense_account( p_assignment_id in number,
31 p_effective_date in date,
32 p_gms_pa_install in varchar2,
33 p_person_id in number,
34 p_distribution_interface_id in number,
35 p_suspense_reason_code in varchar2,
36 p_business_group_id in number,
37 p_set_of_books_id in number,
38 p_return_status out NOCOPY varchar2);
39 Procedure Validate_Person_ID(X_Person_ID IN Number,
40 X_Effective_Date IN Date,
41 X_Business_group_id IN NUMBER,
42 X_Payroll_ID IN Number,
43 X_set_of_books_id IN Number,
44 X_return_status OUT NOCOPY varchar2,
45 X_return_code OUT NOCOPY varchar2);
46 --
47 Procedure Validate_Assignment_ID(X_Assignment_ID IN Number,
48 X_Effective_Date IN Date,
49 X_return_status OUT NOCOPY varchar2,
50 X_return_code OUT NOCOPY varchar2,
51 X_business_group_id IN Number,
52 X_set_of_books_id IN Number);
53
54 --
55 Procedure Validate_Payroll_ID(X_Payroll_ID IN Number,
56 X_Assignment_ID IN Number,
57 X_Effective_Date IN Date,
58 X_return_status OUT NOCOPY varchar2,
59 X_return_code OUT NOCOPY varchar2,
60 X_business_group_id IN Number,
61 X_set_of_books_id IN Number);
62
63 --
64 Procedure Validate_Payroll_Period_ID(X_Payroll_ID IN Number,
65 X_Payroll_Period_ID IN Number,
66 X_Effective_Date IN Date,
67 X_return_status OUT NOCOPY varchar2,
68 X_return_code OUT NOCOPY varchar2);
69 --
70 Procedure Validate_Payroll_Source_Code(X_Payroll_Source_Code IN varchar2,
71 X_return_status OUT NOCOPY varchar2,
72 X_return_code OUT NOCOPY varchar2);
73 --
74 Procedure Validate_Element_Type_ID(X_Element_Type_ID IN Number,
75 X_Payroll_Period_ID IN Number,
76 -- Introduced BG/SOB parameters for bug fix 3098050
77 X_business_group_id IN Number,
78 X_set_of_books_id IN Number,
79 X_return_status OUT NOCOPY varchar2,
80 X_return_code OUT NOCOPY varchar2);
81 --
82 Procedure Validate_Project_details(X_Project_ID IN Number,
83 X_task_id IN Number,
84 X_award_id IN Number,
85 X_expenditure_type IN Varchar2,
86 X_exp_org_id IN Number,
87 X_gms_pa_install IN VARCHAR2,
88 X_Person_ID IN VARCHAR2,
89 X_Effective_date IN DATE,
90 X_return_status OUT NOCOPY Varchar2,
91 X_return_code OUT NOCOPY Varchar2);
92 --
93 Procedure update_record_with_error(X_distribution_interface_id IN Number,
94 X_error_code IN Varchar2,
95 X_return_status OUT NOCOPY Varchar2);
96 --
97 Procedure update_record_with_valid(X_distribution_interface_id IN Number,
98 X_return_status OUT NOCOPY varchar2);
99
100 Procedure update_record_with_exp(X_distribution_interface_id IN Number,
101 X_expenditure_type IN Varchar2,
102 X_return_status OUT NOCOPY Varchar2);
103
104 --
105 Procedure update_record_with_na(X_distribution_interface_id IN Number,
106 X_gl_code_combination_id IN Number,
107 X_return_status OUT NOCOPY Varchar2);
108
109 /* Bug fix 2985061: Created this procedure.*/
110 PROCEDURE VALIDATE_DR_CR_FLAG ( X_DR_CR_FLAG IN VARCHAR2,
111 X_return_status OUT NOCOPY varchar2,
112 X_return_code OUT NOCOPY varchar2);
113
114 /* Bug fix 2985061: Created this procedure.*/
115 PROCEDURE VALIDATE_GL_CC_ID( X_CODE_COMBINATION_ID IN NUMBER,
116 X_return_status OUT NOCOPY varchar2,
117 X_return_code OUT NOCOPY varchar2);
118 g_use_pre_gen_suspense varchar(1); /* Bug 2007521: Profile use suspense A/C */
119 --
120 --
121 -- This is the Main procedure which is being called by Concurrent process
122 -- Input parameter is Batch Name
123 -- fetches all records from psp_distribution_interface table belongs to given batch name and
124 -- validates each record whether it is valid or not.
125 -- If all records are valid then imports into psp_pre_gen_dist_lines table and marks as Transfered in
126 -- psp_distribution_interface table
127 --
128 PROCEDURE IMPORT_PREGEN_LINES (ERRBUF OUT NOCOPY Varchar2,
129 RETCODE OUT NOCOPY Number,
130 p_batch_name IN VARCHAR2,
131 p_business_group_id IN NUMBER,
132 p_set_of_books_id IN NUMBER,
133 p_operating_unit IN NUMBER,
134 p_gms_pa_install IN VARCHAR2) IS
135 -- p_gms_pa_install IN VARCHAR2 default NULL) IS Commented as part of bug fix 2447912
136
137 CURSOR get_all_from_interface_csr is
138 SELECT *
139 FROM psp_distribution_interface
140 WHERE batch_name = p_batch_name and
141 status_code <> 'V' FOR UPDATE;
142
143 -- Declared the following cursor and exception for bug fix 2094036
144 CURSOR pregen_distribution_check_cur IS
145 SELECT distribution_interface_id
146 FROM psp_distribution_interface
147 WHERE batch_name = p_batch_name
148 AND business_group_id = p_business_group_id
149 AND set_of_books_id = p_set_of_books_id
150 FOR UPDATE OF distribution_interface_id NOWAIT;
151 l_distribution_interface_id NUMBER;
152
153 RECORD_ALREADY_LOCKED EXCEPTION;
154 PRAGMA EXCEPTION_INIT (RECORD_ALREADY_LOCKED, -54);
155
156 g_pregen_rec get_all_from_interface_csr%ROWTYPE;
157
158 CURSOR get_for_total_csr is
159 SELECT *
160 FROM psp_distribution_interface
161 WHERE batch_name = p_batch_name
162 ORDER BY source_code,time_period_id;
163
164 g_for_total_rec get_for_total_csr%ROWTYPE;
165
166 /* deleted cursors get_count_for_gl_csr, get_count_for_project_csr Bug 2007521 */
167
168 CURSOR get_batch_name_csr is
169 SELECT count(*)
170 FROM psp_payroll_controls
171 WHERE source_type = 'P' and
172 batch_name = p_batch_name and
173 business_group_id = p_business_group_id and
174 set_of_books_id = p_set_of_books_id;
175
176 -- Introduced the following for bug fix 2916848
177 CURSOR currency_count_cur IS
178 SELECT COUNT(DISTINCT NVL(currency_code, 'bg_currency'))
179 FROM psp_distribution_interface
180 WHERE batch_name = p_batch_name
181 AND business_group_id = p_business_group_id
182 AND set_of_books_id = p_set_of_books_id;
183
184 CURSOR currency_code_cur IS
185 SELECT currency_code
186 FROM psp_distribution_interface
187 WHERE batch_name = p_batch_name
188 AND business_group_id = p_business_group_id
189 AND set_of_books_id = p_set_of_books_id
190 AND currency_code IS NOT NULL
191 AND ROWNUM = 1;
192
193 CURSOR period_end_date_cur (p_time_period_id NUMBER) IS
194 SELECT end_date
195 FROM per_time_periods ptp
196 WHERE ptp.time_period_id = p_time_period_id;
197
198 l_currency_count NUMBER;
199 l_period_end_date DATE;
200 l_exchange_rate_type psp_payroll_controls.exchange_rate_type%TYPE;
201 -- End of bug fix 2916848
202
203 -- Introdced the following for bug fix 3107800
204 CURSOR sob_currency_cur IS
205 SELECT currency_code
206 FROM gl_sets_of_books gsob
207 WHERE set_of_books_id = p_set_of_books_id;
208 -- End of bug fix 3107800
209
210 l_batch_name_count number;
211 ---g_auto_population VARCHAR2(1); 5080403
212
213 -- Error Handling variables
214
215 l_error_api_name varchar2(2000);
216 l_return_status varchar2(1);
217 l_return_code varchar2(30);
218 l_batch_status number(1):= 0;
219 l_msg_count number;
220 l_msg_data varchar2(2000);
221 l_msg_index_out number;
222 --
223 l_api_name varchar2(30) := 'PSP_PREGEN';
224 l_subline_message varchar2(200);
225 --
226 l_ft_source_code varchar2(30);
227 l_ft_time_period_id number(15);
228 l_ft_payroll_id number(9);
229 l_ft_number_of_cr number(9);
230 l_ft_number_of_dr number(9);
231 l_ft_dr_amount number(22,2);
232 l_ft_cr_amount number(22,2);
233 l_ft_counter number(9);
234 l_control_id number(9);
235 --Gl_posting_date included to incorporate the date changes by vijay cirigiri
236 l_gl_posting_date date:=NULL;
237 l_gms_posting_date date:=NULL;
238 l_rowid varchar2(20);
239 l_set_of_books_id number(15):= 0;
240 l_count_status_v NUMBER(9);
241 l_gms_pa_install Varchar2(10);
242 d_set_of_books_id NUMBER;
243 d_business_group_id NUMBER;
244 d_operating_unit NUMBER;
245 ---l_dff_grouping_option VARCHAR2(1) DEFAULT psp_general.get_act_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
246 -- commented above line for 4992668
247
248 -- following cursor for 5080403
249 cursor autopop_config_cur is
250 select pcv_information7 suspense,
251 pcv_information9 pregen
252 from pqp_configuration_values
253 where pcv_information_category = 'PSP_ENABLE_AUTOPOPULATION'
254 and legislation_code is null
255 and nvl(business_group_id, p_business_group_id) = p_business_group_id;
256
257
258 BEGIN
259 ---hr_utility.trace_on('Y', 'PREGEN');
260 g_use_pre_gen_suspense := FND_PROFILE.VALUE('PSP_USE_PREGEN_SUSPENSE'); -- Bug 2007521
261 FND_MSG_PUB.Initialize;
262 --dbms_output.PUT_LINE('................0');
263
264 -- l_set_of_books_id := FND_PROFILE.VALUE('PSP_SET_OF_BOOKS');
265 l_set_of_books_id := p_set_of_books_id;
266 if NVL(l_set_of_books_id,0) = 0 then
267 l_error_api_name := 'IMPORT_PREGEN_LINES';
268 fnd_message.set_name('PSP','PSP_PI_NO_PROFILE_FOR_SOB');
269 fnd_msg_pub.add;
270 raise FND_API.G_EXC_UNEXPECTED_ERROR;
271 end if;
272
273 Begin
274 FND_STATS.Gather_Table_Stats(ownname => 'PSP',
275 tabname => 'PSP_DISTRIBUTION_INTERFACE');
276
277 -- percent => 10,
278 -- tmode => 'NORMAL');
279 -- Removed percent and tmode parameters for bug fix 2463762
280
281 Exception
282 When others then
283 null;
284 End;
285
286 if p_gms_pa_install IS NULL then
287 PSP_GENERAL.MULTIORG_CLIENT_INFO(d_set_of_books_id,d_business_group_id,d_operating_unit,l_gms_pa_install);
288 else
289 l_gms_pa_install := p_gms_pa_install;
290 end if;
291
292 -- Included the following for bug fix 2094036
293 OPEN pregen_distribution_check_cur;
294 FETCH pregen_distribution_check_cur INTO l_distribution_interface_id;
295 IF (pregen_distribution_check_cur%NOTFOUND) THEN
296 CLOSE pregen_distribution_check_cur;
297 RAISE RECORD_ALREADY_LOCKED;
298 END IF;
299
300 CLOSE pregen_distribution_check_cur;
301 -- End of bug fix 2094036
302
303 -- Introduced the following for bug fix 2916848
304 g_bg_currency_code := psp_general.get_currency_code(p_business_group_id);
305
306 -- Introduced the following for bug fix 3107800
307 OPEN sob_currency_cur;
308 FETCH sob_currency_cur INTO g_sob_currency_code;
309 CLOSE sob_currency_cur;
310 -- End of bug fix 3107800
311
312 OPEN currency_count_cur;
313 FETCH currency_count_cur INTO l_currency_count;
314 CLOSE currency_count_cur;
315
316 IF (l_currency_count > 1) THEN
317 fnd_message.set_name('PSP', 'PSP_PI_INVALID_CURRENCY');
318 fnd_message.set_token('BATCH_NAME', p_batch_name);
319 fnd_msg_pub.add;
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END IF;
322
323 OPEN currency_code_cur;
324 FETCH currency_code_cur INTO g_currency_code;
325 CLOSE currency_code_cur;
326
327 g_currency_code := NVL(g_currency_code, g_bg_currency_code);
328
329 IF ((g_bg_currency_code = g_currency_code) AND (g_sob_currency_code = g_bg_currency_code)) THEN
330 g_pop_exchange_rate_type := FALSE;
331 END IF;
332
333 psp_general.get_currency_precision(g_currency_code, g_precision, g_ext_precision);
334 -- End of bug fix 2916848
335
336 /*************************************************************************************************
337 if g_auto_population ='Y' then
338 Autopop(errbuf, retcode, p_batch_name);
339 IF retcode != FND_API.G_RET_STS_SUCCESS THEN
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END IF;
342 end if;
343
344 Modified Procedure Call
345 **************************************************************************************************/
346 --- cursor for 5080403
347 open autopop_config_cur;
348 fetch autopop_config_cur into g_suspense_autopop, g_pregen_autopop;
349 close autopop_config_cur;
350
351 if g_pregen_autopop ='Y' then --- changed to new option for 5080403
352 hr_utility.trace('Calling Autopop');
353 Autopop(
354 X_Batch_name => p_batch_name,
355 X_Set_of_Books_Id =>p_set_of_books_id,
356 X_Business_Group_ID =>p_business_group_id,
357 X_Operating_Unit =>p_operating_unit,
358 X_Gms_Pa_Install => l_gms_pa_install,
359 X_Return_Status =>l_return_status);
360
361 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
362 -- Included changes for bug fix 2094036, to commit autopop errors as part of inerface errors
363 l_batch_status := 1;
364 IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END IF;
367 END IF;
368 end if;
369
370 open get_all_from_interface_csr;
371 fetch get_all_from_interface_csr into g_pregen_rec;
372
373 if get_all_from_interface_csr%NOTFOUND then
374 --RAISE NO_DATA_FOUND;
375 /* Code added by Subha on 7/23/1999 to allow processing to continue in the vent that all
376 records in interface table have a status of 'VALID' */
377
378 select count(*) into l_count_status_v from psp_distribution_interface
379 where batch_name=p_batch_name and status_code='V';
380 if l_count_status_v = 0 then
381 raise NO_DATA_FOUND;
382 end if;
383 end if;
384 close get_all_from_interface_csr;
385 --
386 --dbms_output.PUT_LINE('................1');
387 open get_batch_name_csr;
388 fetch get_batch_name_csr into l_batch_name_count;
389 close get_batch_name_csr;
390
391 if NVL(l_batch_name_count,0) > 0 then
392 fnd_message.set_name('PSP','PSP_PI_INVALID_BATCH_NAME');
393 fnd_message.set_token('PSP_BATCH_NAME',p_batch_name);
394 fnd_msg_pub.add;
395 raise FND_API.G_EXC_UNEXPECTED_ERROR;
396 end if;
397
398 /* Bug 2007521: Optimization, Run this part only if autopop is OFF */
399 if nvl(g_pregen_autopop,'N') <> 'Y' then
400 open get_all_from_interface_csr;
401 LOOP
402 --dbms_output.PUT_LINE('................2');
403 fetch get_all_from_interface_csr into g_pregen_rec;
404 EXIT WHEN get_all_from_interface_csr%NOTFOUND ; -- Exit when last record is reached
405
406 --dbms_output.PUT_LINE('................3');
407 --dbms_output.PUT_LINE('Interface ID....' || to_char(g_pregen_rec.distribution_interface_id));
408
409 Validate_Person_ID(X_Person_ID => g_pregen_rec.person_id,
410 X_Effective_date => g_pregen_rec.distribution_date,
411 X_Business_group_id => p_business_group_id,
412 X_Payroll_ID => g_pregen_rec.payroll_id,
413 X_set_of_books_id => p_set_of_books_id,
414 X_return_status => l_return_status,
415 X_return_code => l_return_code);
416 --dbms_output.PUT_LINE('................4');
417 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
418 l_batch_status := 1;
419 if l_return_code = 'OTHER' then
420 l_error_api_name := 'VALIDATE_PERSON_ID';
421 raise FND_API.G_EXC_UNEXPECTED_ERROR;
422 else
423 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
424 X_error_code => l_return_code,
425 X_return_status => l_return_status);
426 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
427 raise FND_API.G_EXC_UNEXPECTED_ERROR;
428 end if;
429 end if;
430 else
431 Validate_Assignment_ID(X_Assignment_ID => g_pregen_rec.assignment_id,
432 X_Effective_Date => g_pregen_rec.distribution_date,
433 X_return_status => l_return_status,
434 X_return_code => l_return_code,
435 X_business_group_id => p_business_group_id,
436 X_set_of_books_id => p_set_of_books_id);
437
438 --dbms_output.PUT_LINE('................5');
439 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
440 l_batch_status := 1;
441 if l_return_code = 'OTHER' then
442 l_error_api_name := 'VALIDATE_ASSIGNMENT_ID';
443 raise FND_API.G_EXC_UNEXPECTED_ERROR;
444 else
445 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
446 X_error_code => l_return_code,
447 X_return_status => l_return_status);
448 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
449 raise FND_API.G_EXC_UNEXPECTED_ERROR;
450 end if;
451 end if;
452 else
453 Validate_Payroll_ID(X_Payroll_ID => g_pregen_rec.payroll_id,
454 X_Assignment_ID => g_pregen_rec.assignment_id,
455 X_Effective_Date => g_pregen_rec.distribution_date,
456 X_return_status => l_return_status,
457 X_return_code => l_return_code,
458 X_business_group_id => p_business_group_id,
459 X_set_of_books_id => p_set_of_books_id);
460
461 --dbms_output.PUT_LINE('................6');
462 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
463 l_batch_status := 1;
464 if l_return_code = 'OTHER' then
465 l_error_api_name := 'VALIDATE_PAYROLL_ID';
466 raise FND_API.G_EXC_UNEXPECTED_ERROR;
467 else
468 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
469 X_error_code => l_return_code,
470 X_return_status => l_return_status);
471 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
472 raise FND_API.G_EXC_UNEXPECTED_ERROR;
473 end if;
474 end if;
475 else
476 Validate_Payroll_Period_ID(X_Payroll_ID => g_pregen_rec.payroll_id,
477 X_Payroll_Period_ID => g_pregen_rec.time_period_id,
478 X_Effective_Date => g_pregen_rec.distribution_date,
479 X_return_status => l_return_status,
480 X_return_code => l_return_code);
481 --dbms_output.PUT_LINE('................7');
482 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
483 l_batch_status := 1;
484 if l_return_code = 'OTHER' then
485 l_error_api_name := 'VALIDATE_PAYROLL_PERIOD_ID';
486 raise FND_API.G_EXC_UNEXPECTED_ERROR;
487 else
488 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
489 X_error_code => l_return_code,
490 X_return_status => l_return_status);
491 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
492 raise FND_API.G_EXC_UNEXPECTED_ERROR;
493 end if;
494 end if;
495 else
496 --For Bug fix 2985061 : adding the validation of DR_CR Flag
497 VALIDATE_DR_CR_FLAG ( X_DR_CR_FLAG => g_pregen_rec.dr_cr_flag,
498 X_return_status => l_return_status,
499 X_return_code => l_return_code);
500
501 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
502 l_batch_status := 1;
503 IF l_return_code = 'OTHER' THEN
504 l_error_api_name := 'VALIDATE_DR_CR_FLAG';
505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506 ELSE
507 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
508 X_error_code => l_return_code,
509 X_return_status => l_return_status);
510 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
512 END IF;
513 END IF;
514 ELSE
515 --End of Changes for Bug 2985061
516 Validate_Payroll_Source_Code(X_Payroll_Source_Code => g_pregen_rec.source_code,
517 X_return_status => l_return_status,
518 X_return_code => l_return_code);
519 --dbms_output.PUT_LINE('................8');
520 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
521 --dbms_output.PUT_LINE('Entered Failure................8');
522 l_batch_status := 1;
523 if l_return_code = 'OTHER' then
524 l_error_api_name := 'VALIDATE_PAYROLL_SOURCE_CODE';
525 raise FND_API.G_EXC_UNEXPECTED_ERROR;
526 else
527 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
528 X_error_code => l_return_code,
529 X_return_status => l_return_status);
530 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
531 raise FND_API.G_EXC_UNEXPECTED_ERROR;
532 end if;
533 end if;
534 else
535 Validate_Element_Type_ID(X_Element_Type_ID => g_pregen_rec.element_type_id,
536 X_Payroll_Period_ID => g_pregen_rec.time_period_id,
537 -- Introduced BG/SOB parameters for bug fix 3098050
538 x_business_group_id => p_business_group_id,
539 x_set_of_books_id => p_set_of_books_id,
540 X_return_status => l_return_status,
541 X_return_code => l_return_code);
542 --dbms_output.PUT_LINE('................9');
543 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
544 l_batch_status := 1;
545 if l_return_code = 'OTHER' then
546 l_error_api_name := 'VALIDATE_ELEMENT_TYPE_ID';
547 raise FND_API.G_EXC_UNEXPECTED_ERROR;
548 else
549 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
550 X_error_code => l_return_code,
551 X_return_status => l_return_status);
552 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
553 raise FND_API.G_EXC_UNEXPECTED_ERROR;
554 end if;
555 end if;
556 elsif g_pregen_rec.gl_code_combination_id IS NULL and
557 g_pregen_rec.project_id IS NULL then
558 l_batch_status := 1;
559 l_return_code := 'NUL_GLP';
560 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
561 X_error_code => l_return_code,
562 X_return_status => l_return_status);
563 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
564 raise FND_API.G_EXC_UNEXPECTED_ERROR;
565 end if;
566 elsif g_pregen_rec.gl_code_combination_id IS NOT NULL and
567 g_pregen_rec.project_id IS NOT NULL then
568 l_batch_status := 1;
569 l_return_code := 'NOT_GLP';
570 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
571 X_error_code => l_return_code,
572 X_return_status => l_return_status);
573 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
574 raise FND_API.G_EXC_UNEXPECTED_ERROR;
575 end if;
576 /* Bug fix 2985061 */
577 elsif g_pregen_rec.gl_code_combination_id IS NOT NULL then
578 validate_gl_cc_id( x_code_combination_id => g_pregen_rec.gl_code_combination_id,
579 x_return_status => l_return_status,
580 x_return_code => l_return_code);
581
582 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
583 l_batch_status := 1;
584 IF l_return_code = 'OTHER' THEN
585 l_error_api_name := 'VALIDATE_GL_CC_ID';
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587 ELSE
588 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
589 X_error_code => l_return_code,
590 X_return_status => l_return_status);
591 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
592 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
593 END IF;
594 END IF;
595 END IF;
596
597 elsif g_pregen_rec.project_id is not null then /* Bug fix 2985061 */
598 -- elsif NVL(g_pregen_rec.project_id,0) <> 0 then
599 /****** Check for projects is installed or not ***************/
600 if l_gms_pa_install ='NO_PA_GMS' then
601 l_batch_status:=1;
602 l_return_status:= FND_API.G_RET_STS_ERROR;
603 l_return_code:='NO_PA';
604 update_record_with_error(
605 X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
606 X_error_code => l_return_code,
607 X_return_status => l_return_status);
608 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
609 raise FND_API.G_EXC_UNEXPECTED_ERROR;
610 end if;
611 else
612 Validate_Project_details(X_Project_ID => g_pregen_rec.project_id,
613 X_task_id => g_pregen_rec.task_id,
614 X_award_id => g_pregen_rec.award_id,
615 X_expenditure_type => g_pregen_rec.expenditure_type,
616 X_exp_org_id => g_pregen_rec.expenditure_organization_id,
617 X_gms_pa_install => l_gms_pa_install,
618 X_Person_ID => g_pregen_rec.person_id,
619 X_Effective_date => g_pregen_rec.distribution_date,
620 X_return_status => l_return_status,
621 X_return_code => l_return_code);
622 --dbms_output.PUT_LINE('................10');
623 --dbms_output.PUT_LINE('return status.....' || l_return_status);
624 --dbms_output.PUT_LINE('return code.....' || l_return_code);
625 end if;
626 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
627 -- l_batch_status := 1;
628 if l_return_code = 'OTHER' then
629 l_batch_status := 1; /* 2007521 */
630 l_error_api_name := 'VALIDATE_PROJECTS_DETAILS';
631 raise FND_API.G_EXC_UNEXPECTED_ERROR;
632 else
633 if g_use_pre_gen_suspense = 'Y' then
634 stick_suspense_account( g_pregen_rec.assignment_id,
635 g_pregen_rec.distribution_date,
636 l_gms_pa_install,
637 g_pregen_rec.person_id,
638 g_pregen_rec.distribution_interface_id,
639 l_return_code,
640 p_business_group_id,
641 p_set_of_books_id,
642 l_return_status);
643 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
644 raise FND_API.G_EXC_UNEXPECTED_ERROR;
645 end if;
646 /* Bug 2007521: moved code into stick suspense a/c
647 update_record_with_valid(X_distribution_interface_id=>
648 g_pregen_rec.distribution_interface_id,
649 X_return_status => l_return_status);
650 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
651 raise FND_API.G_EXC_UNEXPECTED_ERROR;
652 end if; */
653 else
654 l_batch_status := 1;
655 update_record_with_error(X_distribution_interface_id=>
656 g_pregen_rec.distribution_interface_id,
657 X_error_code => l_return_code,
658 X_return_status => l_return_status);
659 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
660 raise FND_API.G_EXC_UNEXPECTED_ERROR;
661 end if;
662 end if;
663 end if;
664 else
665 ----dbms_output.put_line ('Update record with valid');
666 l_return_code := 0;
667 update_record_with_valid(X_distribution_interface_id=> g_pregen_rec.distribution_interface_id,
668 X_return_status => l_return_status);
669 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
670 raise FND_API.G_EXC_UNEXPECTED_ERROR;
671 end if;
672 end if;
673 else
674 ----dbms_output.put_line ('Update record with valid');
675 l_return_code := 0;
676 update_record_with_valid(X_distribution_interface_id=> g_pregen_rec.distribution_interface_id,
677 X_return_status => l_return_status);
678 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
679 raise FND_API.G_EXC_UNEXPECTED_ERROR;
680 end if;
681 end if;
682 end if;
683 end if;
684 end if;
685 end if;
686 end if;
687 end if; -- for bug fix 2985061
688 END LOOP;
689 close get_all_from_interface_csr;
690 --dbms_output.PUT_LINE('...End Loop .....l_batch_status ' || to_char(l_batch_status));
691 end if; /* Bug 2007521: Optimization */--Moved the End if for Bug 2096440
692
693 if l_batch_status = 1 then
694 /* 2007521: Introduced update statement, to revert sticking suspense a/c if
695 there are some other errors. Give chance to user to correct all errors */
696 if nvl(g_use_pre_gen_suspense,'N') = 'Y' then
697 update psp_distribution_interface
698 set suspense_org_account_id = null,
699 status_code = 'E'
700 where batch_name = p_batch_name and
701 suspense_org_account_id is not null;
702 end if;
703 fnd_message.set_name('PSP','PSP_BATCH_HAS_ERRORS');
704 fnd_msg_pub.add;
705 -- This comment was added by Chandra to commit records
706 -- in the PSP_DISTRIBUTION_INTERFACE table with the status
707 commit; -- Added by Chandra
708 raise FND_API.G_EXC_UNEXPECTED_ERROR;
709 end if;
710 --For Bug 2096440 : Moved the END IF above , the check for l_batch_status=1 has to be applicable for autopop 'Y' as well as 'N'
711 -- end if; /* Bug 2007521: Optimization */
712 --
713 if l_batch_status = 0 then
714 INSERT INTO PSP_PRE_GEN_DIST_LINES ( pre_gen_dist_line_id,
715 distribution_interface_id,
716 person_id,
717 assignment_id,
718 element_type_id,
719 distribution_date,
720 effective_date,
721 distribution_amount,
722 dr_cr_flag,
723 payroll_control_id,
724 source_type,
725 source_code,
726 time_period_id,
727 batch_name,
728 status_code,
729 set_of_books_id,
730 gl_code_combination_id,
731 project_id,
732 expenditure_organization_id,
733 expenditure_type,
734 task_id,
735 award_id,
736 suspense_org_account_id,
737 suspense_reason_code,
738 effort_report_id,
739 version_num,
740 summary_line_id,
741 reversal_entry_flag,
742 user_defined_field,
743 business_group_id,
744 attribute_category, -- Introduced DFF columns for bug fix 2908859
745 attribute1,
746 attribute2,
747 attribute3,
748 attribute4,
749 attribute5,
750 attribute6,
751 attribute7,
752 attribute8,
753 attribute9,
754 attribute10,
755 suspense_auto_glccid,
756 suspense_auto_exp_type)
757 SELECT psp_distribution_lines_s.nextval,
758 a.distribution_interface_id,
759 a.person_id,
760 a.assignment_id,
761 a.element_type_id,
762 a.distribution_date,
763 --- Replaced dist date with period end date for GL -2876055
764 decode(nvl(a.gl_code_combination_id, susp.gl_code_combination_id)
765 , null, a.distribution_date, t.end_date), -- added nvl for 5164744
766 ---a.distribution_date,
767 ROUND(a.distribution_amount, g_precision), -- Introduced ROUND for bug fix 2651379; Corrected precision for bug fix 2916848
768 UPPER(a.dr_cr_flag),
769 0,
770 'P',
771 a.source_code,
772 a.time_period_id,
773 p_batch_name,
774 'N',
775 l_set_of_books_id,
776 a.gl_code_combination_id,
777 a.project_id,
778 a.expenditure_organization_id,
779 a.expenditure_type,
780 a.task_id,
781 a.award_id,
782 a.suspense_org_account_id, /* 2007521 */
783 a.error_code, --NULL, Bug 2007521: Susp changed to error code
784 NULL, -- effort report id
785 NULL, -- version num
786 NULL, -- summary line id
787 NULL, -- reversal_entry_flag
788 NULL, -- user_defined_field
789 p_business_group_id,
790 a.attribute_category, -- Introduced DFF columns for bug fix 2908859,
791 --- another fix for 4992668 DFF column always to gointo lines
792 a.attribute1,
793 a.attribute2,
794 a.attribute3,
795 a.attribute4,
796 a.attribute5,
797 a.attribute6,
798 a.attribute7,
799 a.attribute8,
800 a.attribute9,
801 a.attribute10,
802 a.suspense_auto_glccid,
803 a.suspense_auto_exp_type
804 FROM psp_distribution_interface a,
805 per_time_periods T,
806 psp_organization_accounts susp --- introduced for 5164744
807 WHERE batch_name = p_batch_name and
808 T.time_period_id = a.time_period_id and
809 susp.organization_account_id(+) = a.suspense_org_account_id;
810 if sql%NOTFOUND then
811 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while inserting data');
812 raise FND_API.G_EXC_UNEXPECTED_ERROR;
813 end if;
814 --dbms_output.PUT_LINE('...Crossed First Insert ' );
815 --dbms_output.PUT_LINE('...L_gl_count ' || to_char(l_gl_count) );
816 --dbms_output.PUT_LINE('...L_project_count ' || to_char(l_project_count) );
817
818 /* 2007521: Deleted creation of balancing lines for Projects/GL, S and T does this */
819
820 UPDATE psp_distribution_interface
821 SET status_code = 'T'
822 WHERE batch_name = p_batch_name;
823 --dbms_output.PUT_LINE('...Crossed Update ' );
824 if sql%NOTFOUND then
825 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while Updating Transfer status ');
826 raise FND_API.G_EXC_UNEXPECTED_ERROR;
827 end if;
828
829 -- Total up all records by source_code, time_period_id to write into payroll_control table
830
831 open get_for_total_csr;
832 l_ft_payroll_id := 0;
833 l_ft_number_of_cr := 0;
834 l_ft_number_of_dr := 0;
835 l_ft_dr_amount := 0;
836 l_ft_cr_amount := 0;
837 l_ft_counter := 0;
838 LOOP
839 fetch get_for_total_csr into g_for_total_rec;
840 EXIT WHEN get_for_total_csr%NOTFOUND;
841 --dbms_output.PUT_LINE('...Entered Payroll Controls LOOP.... ' );
842 l_ft_counter := l_ft_counter + 1;
843 if l_ft_counter = 1 then
844 l_ft_source_code := g_for_total_rec.source_code;
845 l_ft_time_period_id := g_for_total_rec.time_period_id;
846 l_ft_payroll_id := g_for_total_rec.payroll_id;
847 end if;
848 if ( nvl(l_ft_source_code,' ') <> nvl(g_for_total_rec.source_code,' ') or
849 nvl(l_ft_time_period_id, 0) <> nvl(g_for_total_rec.time_period_id,0)) then
850 -- Insert a record in psp_payroll_controls
851 --dbms_output.PUT_LINE('...Before Insert into payroll controls ' );
852 select psp_payroll_controls_s.nextval into l_control_id from dual;
853
854 -- Introduced for bug fix 2916848
855 OPEN period_end_date_cur(l_ft_time_period_id);
856 FETCH period_end_date_cur INTO l_period_end_date;
857 CLOSE period_end_date_cur;
858
859 IF (g_pop_exchange_rate_type) THEN
860 l_exchange_rate_type := hruserdt.get_table_value
861 (p_bus_group_id => p_business_group_id,
862 p_table_name => 'EXCHANGE_RATE_TYPES',
863 p_col_name => 'Conversion Rate Type',
864 p_row_value => 'PAY',
865 p_effective_date => l_period_end_date);
866 END IF;
867 -- End of bug fix 2916848
868
869 PSP_PAYROLL_CONTROLS_PKG.INSERT_ROW (
870 X_ROWID => l_rowid,
871 X_PAYROLL_CONTROL_ID => l_control_id,
872 X_PAYROLL_ACTION_ID => 0,
873 X_PAYROLL_SOURCE_CODE => l_ft_Source_Code,
874 X_SOURCE_TYPE => 'P',
875 X_PAYROLL_ID => l_ft_payroll_id,
876 X_TIME_PERIOD_ID => l_ft_time_period_id,
877 X_NUMBER_OF_CR => l_ft_number_of_cr,
878 X_NUMBER_OF_DR => l_ft_number_of_dr,
879 X_TOTAL_DR_AMOUNT => NULL,
880 X_TOTAL_CR_AMOUNT => NULL,
881 X_BATCH_NAME => p_batch_name,
882 X_SUBLINES_DR_AMOUNT => NULL,
883 X_SUBLINES_CR_AMOUNT => NULL,
884 X_DIST_CR_AMOUNT => l_ft_cr_amount,
885 X_DIST_DR_AMOUNT => l_ft_dr_amount,
886 X_OGM_DR_AMOUNT => NULL,
887 X_OGM_CR_AMOUNT => NULL,
888 X_GL_DR_AMOUNT => NULL,
889 X_GL_CR_AMOUNT => NULL,
890 X_STATUS_CODE => 'N',
891 X_MODE => 'R',
892 X_GL_POSTING_OVERRIDE_DATE =>g_for_total_rec.gl_posting_override_date ,
893 X_GMS_POSTING_OVERRIDE_DATE =>g_for_total_rec.gms_posting_override_date,
894 X_business_group_id => p_business_group_id,
895 X_set_of_books_id => l_set_of_books_id ,
896 X_GL_PHASE => NULL,
897 X_GMS_PHASE => NULL,
898 X_ADJ_SUM_BATCH_NAME => NULL,
899 -- Introduced the following for bug fix 2916848
900 x_currency_code => g_currency_code,
901 x_exchange_rate_type => l_exchange_rate_type);
902 if sql%NOTFOUND then
903 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while inserting data in Payroll Controls');
904 raise FND_API.G_EXC_UNEXPECTED_ERROR;
905 end if;
906 --dbms_output.PUT_LINE('...Before Updating control_id in pre gen ' );
907 UPDATE psp_pre_gen_dist_lines
908 SET payroll_control_id = l_control_id
909 WHERE time_period_id = l_ft_time_period_id and
910 batch_name = p_batch_name and
911 source_type = 'P' and
912 source_code = l_ft_source_code and
913 set_of_books_id= l_set_of_books_id and
914 business_group_id = p_business_group_id;
915 if sql%NOTFOUND then
916 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while updating control_id in pre-gen-dist-lines ');
917 raise FND_API.G_EXC_UNEXPECTED_ERROR;
918 end if;
919
920 l_ft_source_code := g_for_total_rec.source_code;
921 l_ft_time_period_id := g_for_total_rec.time_period_id;
922 l_ft_payroll_id := g_for_total_rec.payroll_id;
923 l_ft_number_of_cr := 0;
924 l_ft_number_of_dr := 0;
925 l_ft_dr_amount := 0;
926 l_ft_cr_amount := 0;
927 end if;
928
929 if NVL(g_for_total_rec.dr_cr_flag,' ') = 'D' then
930 l_ft_number_of_dr := l_ft_number_of_dr + 1;
931 l_ft_dr_amount := l_ft_dr_amount + NVL(g_for_total_rec.distribution_amount,0);
932 else
933 l_ft_number_of_cr := l_ft_number_of_cr + 1;
934 l_ft_cr_amount := l_ft_cr_amount + NVL(g_for_total_rec.distribution_amount,0);
935 end if;
936 END LOOP;
937 -- Insert the last record into payroll_controls
938 --dbms_output.PUT_LINE('...Before Insert into payroll controls ' );
939 select psp_payroll_controls_s.nextval into l_control_id from dual;
940
941 -- Introduced for bug fix 2916848
942 OPEN period_end_date_cur(l_ft_time_period_id);
943 FETCH period_end_date_cur INTO l_period_end_date;
944 CLOSE period_end_date_cur;
945
946 IF (g_pop_exchange_rate_type) THEN
947 l_exchange_rate_type := hruserdt.get_table_value
948 (p_bus_group_id => p_business_group_id,
949 p_table_name => 'EXCHANGE_RATE_TYPES',
950 p_col_name => 'Conversion Rate Type',
951 p_row_value => 'PAY',
952 p_effective_date => l_period_end_date);
953 END IF;
954 -- End of bug fix 2916848
955
956 PSP_PAYROLL_CONTROLS_PKG.INSERT_ROW (
957 X_ROWID => l_rowid,
958 X_PAYROLL_CONTROL_ID => l_control_id,
959 X_PAYROLL_ACTION_ID => 0,
960 X_PAYROLL_SOURCE_CODE => l_ft_Source_Code,
961 X_SOURCE_TYPE => 'P',
962 X_PAYROLL_ID => l_ft_payroll_id,
963 X_TIME_PERIOD_ID => l_ft_time_period_id,
964 X_NUMBER_OF_CR => l_ft_number_of_cr,
965 X_NUMBER_OF_DR => l_ft_number_of_dr,
966 X_TOTAL_DR_AMOUNT => NULL,
967 X_TOTAL_CR_AMOUNT => NULL,
968 X_BATCH_NAME => p_batch_name,
969 X_SUBLINES_DR_AMOUNT => NULL,
970 X_SUBLINES_CR_AMOUNT => NULL,
971 X_DIST_CR_AMOUNT => l_ft_cr_amount,
972 X_DIST_DR_AMOUNT => l_ft_dr_amount,
973 X_OGM_DR_AMOUNT => NULL,
974 X_OGM_CR_AMOUNT => NULL,
975 X_GL_DR_AMOUNT => NULL,
976 X_GL_CR_AMOUNT => NULL,
977 X_STATUS_CODE => 'N',
978 X_MODE => 'R',
979 X_GL_POSTING_OVERRIDE_DATE => g_for_total_rec.gl_posting_override_date,
980 X_GMS_POSTING_OVERRIDE_DATE =>g_for_total_rec.gms_posting_override_date ,
981 X_business_group_id => p_business_group_id,
982 X_set_of_books_id => l_set_of_books_id,
983 X_GL_PHASE => NULL,
984 X_GMS_PHASE => NULL,
985 X_ADJ_SUM_BATCH_NAME=> NULL,
986 -- Introduced the following for bug fix 2916848
987 x_currency_code => g_currency_code,
988 x_exchange_rate_type => l_exchange_rate_type);
989
990 if sql%NOTFOUND then
991 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while inserting data in Payroll Controls');
992 raise FND_API.G_EXC_UNEXPECTED_ERROR;
993 end if;
994 --dbms_output.PUT_LINE('...Before Updating control_id in pre gen ' );
995 UPDATE psp_pre_gen_dist_lines
996 SET payroll_control_id = l_control_id
997 WHERE time_period_id = l_ft_time_period_id and
998 batch_name = p_batch_name and
999 source_type = 'P' and
1000 source_code = l_ft_source_code and
1001 set_of_books_id= l_set_of_books_id and
1002 business_group_id = p_business_group_id;
1003 if sql%NOTFOUND then
1004 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Error while updating control_id in pre-gen-dist-lines ');
1005 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1006 end if;
1007 close get_for_total_csr;
1008 end if;
1009 errbuf := l_subline_message;
1010 retcode := 0;
1011 commit;
1012 PSP_MESSAGE_S.Print_Success;
1013
1014
1015
1016 EXCEPTION
1017 -- Included the following exception handling as part of bug fix 2094036
1018 WHEN RECORD_ALREADY_LOCKED THEN
1019 fnd_message.set_name('PSP', 'PSP_PI_BATCH_IN_PROGRESS');
1020 fnd_message.set_token('BATCH_NAME', p_batch_name);
1021 l_subline_message := fnd_message.get;
1022 errbuf := l_error_api_name ||fnd_global.local_chr(10)||l_subline_message;
1023 retcode:= 2;
1024 WHEN NO_DATA_FOUND then
1025 close get_all_from_interface_csr;
1026 FND_MESSAGE.SET_NAME('PSP','PSP_LD_NO_TRANS');
1027 l_subline_message := fnd_message.get;
1028 errbuf := l_error_api_name || fnd_global.local_chr(10) || l_subline_message;
1029 retcode := 0;
1030 fnd_message.set_name('PSP','PSP_PROGRAM_SUCCESS') ;
1031 fnd_msg_pub.add;
1032
1033 psp_message_s.print_error(p_mode=>FND_FILE.log,
1034 p_print_header=>FND_API.G_FALSE);
1035 PSP_MESSAGE_S.Print_Success;
1036 return;
1037
1038 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
1039 ----dbms_output.put_line('Unexpected Error...........');
1040 /*
1041 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
1042 p_encoded => FND_API.G_FALSE,
1043 p_data => l_msg_data,
1044 p_msg_index_out => l_msg_count);
1045
1046 */
1047 errbuf := l_error_api_name || fnd_global.local_chr(10) || l_msg_data || fnd_global.local_chr(10);
1048 retcode := 2;
1049 rollback;
1050 psp_message_s.print_error(p_mode => FND_FILE.LOG,
1051 p_print_header => FND_API.G_TRUE);
1052 return;
1053 WHEN OTHERS then
1054 ----dbms_output.put_line('When others Error...........');
1055 /*
1056 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
1057 p_encoded => FND_API.G_FALSE,
1058 p_data => l_msg_data,
1059 p_msg_index_out => l_msg_count);
1060
1061 */
1062 errbuf := l_error_api_name || fnd_global.local_chr(10) || l_msg_data || fnd_global.local_chr(10);
1063 rollback;
1064 retcode := 2;
1065 psp_message_s.print_error(p_mode => FND_FILE.LOG,
1066 p_print_header => FND_API.G_TRUE);
1067 return;
1068 END;
1069 --
1070
1071
1072
1073
1074 -------------------------------------VALIDATE_PERSON_ID------------------------------------------
1075 -- This procedure is to validate the person id with Oracle HR
1076 --
1077 Procedure Validate_Person_ID(X_Person_ID IN Number,
1078 X_Effective_Date IN Date,
1079 X_Business_group_id IN NUMBER,
1080 X_Payroll_ID IN Number,
1081 X_set_of_books_id IN Number,
1082 X_return_status OUT NOCOPY Varchar2,
1083 X_return_code OUT NOCOPY Varchar2) IS
1084
1085 /* Modified the cursor below for "Processing of employee assignments with zero work days"
1086 enhancement : Bug 1994421 */
1087
1088 /***** Modified the following cursor for R12 performance fixes (bug 4507892)
1089 CURSOR check_person_csr IS
1090 SELECT a.person_id
1091 FROM Per_People_F a
1092 WHERE a.Person_ID = x_person_id
1093 -- AND a.current_employee_flag ='Y' --Added for bug 2624259. Commented for Bug 3424494
1094 AND (x_effective_date BETWEEN a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE)
1095 AND x_effective_date <= ( SELECT max(b.effective_end_date)
1096 FROM per_assignments_f b,
1097 pay_payrolls_f c
1098 WHERE a.person_id = b.person_id
1099 AND b.business_group_id = x_business_group_id
1100 AND c.payroll_id = b.payroll_id
1101 AND b.assignment_type ='E' --Added for bug 2624259.
1102 AND c.gl_set_of_books_id = X_set_of_books_id);
1103 End of comment for R12 performance fixes (bug 4507892) *****/
1104 -- New cursor defn. for R12 performance fixes (bug 4507892)
1105 CURSOR check_person_csr IS
1106 SELECT ppf.person_id
1107 FROM per_people_f ppf
1108 WHERE ppf.person_id = x_person_id
1109 AND (x_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
1110 AND x_effective_date <= (SELECT MAX(paf.effective_end_date)
1111 FROM per_assignments_f paf,
1112 pay_payrolls_f ppf2
1113 WHERE paf.person_id = x_person_id
1114 AND paf.business_group_id = x_business_group_id
1115 AND ppf2.payroll_id = paf.payroll_id
1116 AND paf.assignment_type ='E'
1117 AND ppf2.gl_set_of_books_id = x_set_of_books_id);
1118
1119 l_person_id number(9);
1120
1121 Begin
1122 open check_person_csr;
1123 fetch check_person_csr into l_person_id;
1124 if check_person_csr%NOTFOUND then
1125 x_return_status := FND_API.G_RET_STS_ERROR;
1126 x_return_code := 'INV_PER';
1127 close check_person_csr;
1128 return;
1129 else
1130 x_return_status := FND_API.G_RET_STS_SUCCESS;
1131 x_return_code := ' ';
1132 end If;
1133 close check_person_csr;
1134 Exception
1135 when no_data_found or too_many_rows then
1136 x_return_status := FND_API.G_RET_STS_ERROR;
1137 x_return_code := 'INV_PER';
1138 close check_person_csr;
1139 return;
1140 when OTHERS then
1141 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Validate_Person_id : Unexpected Error');
1142 x_return_status := FND_API.G_RET_STS_ERROR;
1143 x_return_code := 'OTHER';
1144 close check_person_csr;
1145 End Validate_Person_ID;
1146
1147 -----------------------------VALIDATE_ASSIGNMENT_ID-------------------------------------------
1148 --This procedure is to validate Assignment ID with Oracle HR
1149 --
1150 Procedure Validate_Assignment_ID(X_Assignment_ID IN Number,
1151 X_Effective_Date IN Date,
1152 X_return_status OUT NOCOPY varchar2,
1153 X_return_code OUT NOCOPY varchar2,
1154 X_business_group_id IN NUMBER,
1155 X_set_of_books_id IN NUMBER) IS
1156 CURSOR check_assg_csr IS
1157 SELECT assignment_id
1158 FROM per_assignments_f a, pay_payrolls_f b
1159 WHERE assignment_id = x_assignment_id
1160 AND a.assignment_type ='E' --Added for bug 2624259.
1161 and a.payroll_id=b.payroll_id
1162 AND x_effective_date between a.effective_start_date and a.effective_end_date
1163 AND x_effective_date between b.effective_start_date and b.effective_end_date
1164 AND a.business_group_id = X_business_group_id
1165 AND b.gl_set_of_books_id = X_set_of_books_id;
1166
1167 l_assignment_id number(9);
1168
1169 Begin
1170 open check_assg_csr;
1171 fetch check_assg_csr into l_assignment_id;
1172 if check_assg_csr%NOTFOUND then
1173 x_return_status := FND_API.G_RET_STS_ERROR;
1174 x_return_code := 'INV_ASG';
1175 close check_assg_csr;
1176 return;
1177 else
1178 x_return_status := FND_API.G_RET_STS_SUCCESS;
1179 x_return_code := ' ';
1180 end If;
1181 close check_assg_csr;
1182
1183 Exception
1184 when no_data_found or too_many_rows then
1185 x_return_status := FND_API.G_RET_STS_ERROR;
1186 x_return_code := 'INV_ASG';
1187 close check_assg_csr;
1188 return;
1189 when OTHERS then
1190 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Validate_Assignment_id : Unexpected Error');
1191 x_return_status := FND_API.G_RET_STS_ERROR;
1192 x_return_code := 'OTHER';
1193 close check_assg_csr;
1194 End Validate_Assignment_ID;
1195
1196 ------------------------VALIDATE_PAYROLL_ID---------------------------------------------------
1197 -- This procedure is to validate payroll ID with Oracle Payroll
1198 --
1199 Procedure Validate_Payroll_ID(X_Payroll_ID IN Number,
1200 X_Assignment_ID IN Number,
1201 X_Effective_Date IN Date,
1202 X_return_status OUT NOCOPY varchar2,
1203 X_return_code OUT NOCOPY varchar2,
1204 X_business_group_id IN Number,
1205 X_set_of_books_id IN Number) IS
1206 CURSOR check_payroll_csr IS
1207 SELECT a.payroll_id
1208 FROM pay_payrolls_f a, per_assignments_f b
1209 WHERE a.payroll_id = x_payroll_id
1210 AND x_effective_date between a.effective_start_date and a.effective_end_date
1211 AND a.payroll_id = b.payroll_id
1212 AND b.assignment_id = X_assignment_id
1213 AND (X_effective_date between b.effective_start_date and b.effective_end_date)
1214 AND a.business_group_id = X_business_group_id
1215 AND a.gl_set_of_books_id = X_set_of_books_id;
1216
1217 l_payroll_id number(9);
1218
1219 Begin
1220 ----dbms_output.put_line('payroll id ' || to_char(x_payroll_id));
1221 ----dbms_output.put_line('Assignment ID ' || to_char(x_assignment_id));
1222 ----dbms_output.put_line('Effective Date ' || to_char(x_effective_date));
1223
1224 begin
1225 open check_payroll_csr;
1226 fetch check_payroll_csr into l_payroll_id;
1227 if check_payroll_csr%NOTFOUND then
1228 ----dbms_output.put_line('%NOTFOUND');
1229 x_return_status := FND_API.G_RET_STS_ERROR;
1230 x_return_code := 'INV_PID';
1231 close check_payroll_csr;
1232 return;
1233 else
1234 ----dbms_output.put_line('%SUCCESS');
1235 x_return_status := FND_API.G_RET_STS_SUCCESS;
1236 x_return_code := ' ';
1237 end If;
1238 close check_payroll_csr;
1239 Exception
1240 when no_data_found or too_many_rows then
1241 ----dbms_output.put_line('NO DATA FOUND');
1242 x_return_status := FND_API.G_RET_STS_ERROR;
1243 x_return_code := 'INV_PID';
1244 close check_payroll_csr;
1245 return;
1246 when OTHERS then
1247 ----dbms_output.put_line('OTHERS ');
1248 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Validate_Payroll_id : Unexpected Error');
1249 x_return_status := FND_API.G_RET_STS_ERROR;
1250 x_return_code := 'OTHER';
1251 close check_payroll_csr;
1252 return;
1253 end;
1254 End Validate_Payroll_ID;
1255
1256
1257 ----------------------------------VALIDATE_PAYROLL_PERIOD_ID-----------------------------------
1258 -- This procedure is to validate Time Period id with Oracle HR
1259 --
1260 Procedure Validate_Payroll_Period_ID(X_Payroll_ID IN number,
1261 X_Payroll_Period_ID IN number,
1262 X_Effective_Date IN Date,
1263 X_return_status OUT NOCOPY varchar2,
1264 X_return_code OUT NOCOPY varchar2) IS
1265 CURSOR check_period_csr IS
1266 SELECT Time_Period_id
1267 FROM Per_Time_Periods
1268 WHERE Payroll_id = x_Payroll_ID
1269 and Time_Period_ID = x_Payroll_Period_ID
1270 and (x_Effective_Date between start_date and end_date);
1271
1272 l_period_id number(9);
1273
1274 Begin
1275 open check_period_csr;
1276 fetch check_period_csr into l_period_id;
1277 if check_period_csr%NOTFOUND then
1278 x_return_status := FND_API.G_RET_STS_ERROR;
1279 x_return_code := 'INV_TPI';
1280 close check_period_csr;
1281 return;
1282 else
1283 x_return_status := FND_API.G_RET_STS_SUCCESS;
1284 x_return_code := ' ';
1285 end If;
1286 close check_period_csr;
1287 Exception
1288 when no_data_found or too_many_rows then
1289 x_return_status := FND_API.G_RET_STS_ERROR;
1290 x_return_code := 'INV_TPI';
1291 close check_period_csr;
1292 return;
1293 when OTHERS then
1294 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Validate_Period_id : Unexpected Error');
1295 x_return_status := FND_API.G_RET_STS_ERROR;
1296 x_return_code := 'OTHER';
1297 close check_period_csr;
1298 return;
1299 End Validate_Payroll_Period_ID;
1300 ----------------------------------VALIDATE_PAYROLL_SOURCE_ID-----------------------------------
1301 -- This procedure is to validate Source code with Psp_payroll_sources
1302 --
1303 Procedure Validate_Payroll_Source_Code(x_Payroll_Source_Code IN varchar2,
1304 X_return_status OUT NOCOPY varchar2,
1305 X_return_code OUT NOCOPY varchar2) IS
1306 CURSOR check_source_csr IS
1307 SELECT source_code
1308 FROM PSP_PAYROLL_SOURCES
1309 WHERE source_code = x_Payroll_Source_Code and
1310 source_type = 'P';
1311 l_lookup_code varchar2(30);
1312 Begin
1313 open check_source_csr;
1314 fetch check_source_csr into l_lookup_code;
1315 if check_source_csr%NOTFOUND then
1316 --dbms_output.PUT_LINE('Enter NOTFOUND......8');
1317 x_return_status := FND_API.G_RET_STS_ERROR;
1318 x_return_code := 'INV_SRC';
1319 close check_source_csr;
1320 return;
1321 else
1322 --dbms_output.PUT_LINE('Enter Success.....8');
1323 x_return_status := FND_API.G_RET_STS_SUCCESS;
1324 x_return_code := ' ';
1325 end If;
1326 close check_source_csr;
1327 Exception
1328 when no_data_found or too_many_rows then
1329 --dbms_output.PUT_LINE('Enter Too_many_rows....8');
1330 x_return_status := FND_API.G_RET_STS_ERROR;
1331 x_return_code := 'INV_SRC';
1332 close check_source_csr;
1333 return;
1334 when OTHERS then
1335 --dbms_output.PUT_LINE('Enter Too_many_rows....8');
1336 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Validate_Payroll_Source_Code : Unexpected Error');
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 x_return_code := 'OTHER';
1339 close check_source_csr;
1340 return;
1341 End Validate_Payroll_Source_Code;
1342 ----------------------------------VALIDATE_ELEMENT_TYPE_ID-----------------------------------
1343 --This procedure is to validate Element types with psp element types
1344 --
1345 Procedure Validate_Element_Type_ID(X_Element_Type_ID IN Number,
1346 X_Payroll_Period_ID IN Number,
1347 -- Introduced BG/SOB parameters for bug fix 3098050
1348 x_business_group_id IN NUMBER,
1349 x_set_of_books_id IN NUMBER,
1350 X_return_status OUT NOCOPY varchar2,
1351 X_return_code OUT NOCOPY varchar2) IS
1352 CURSOR check_element_csr IS
1353 SELECT a.element_type_id
1354 FROM psp_element_types a,
1355 per_time_periods c
1356 WHERE a.element_type_id = x_Element_Type_ID and
1357 c.time_period_id = x_payroll_period_id
1358 -- and ((c.start_date between a.start_date_active and a.end_date_active)
1359 -- or (c.end_date between a.start_date_active and a.end_date_active)
1360 -- or ((a.start_date_active < c.start_date) and (a.end_date_active > c.end_date)))
1361 -- Introduced this for bug fix 2916848
1362 AND c.start_date <= a.end_date_active
1363 AND c.end_date >= a.start_date_active
1364 AND EXISTS (SELECT 1
1365 FROM pay_element_types_f pef
1366 WHERE pef.element_type_id = a.element_type_id
1367 AND ( pef.output_currency_code = g_currency_code
1368 OR g_currency_code = 'STAT')
1369 AND pef.effective_end_date >= a.start_date_active
1370 AND pef.effective_start_date <= a.end_date_active)
1371 -- Introduced for bug fix 3098050
1372 AND a.business_group_id = x_business_group_id
1373 AND a.set_of_books_id = x_set_of_books_id;
1374
1375 l_element_id number(9);
1376
1377 Begin
1378 open check_element_csr;
1379 fetch check_element_csr into l_element_id;
1380 if check_element_csr%NOTFOUND then
1381 x_return_status := FND_API.G_RET_STS_ERROR;
1382 x_return_code := 'INV_ELE';
1383 close check_element_csr;
1384 return;
1385 else
1386 x_return_status := FND_API.G_RET_STS_SUCCESS;
1387 x_return_code := ' ';
1388 end If;
1389 close check_element_csr;
1390 Exception
1391 when no_data_found or too_many_rows then
1392 x_return_status := FND_API.G_RET_STS_ERROR;
1393 x_return_code := 'INV_ELE';
1394 close check_element_csr;
1395 return;
1396 when OTHERS then
1397 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Validate_Element_Type_Id : Unexpected Error');
1398 x_return_status := FND_API.G_RET_STS_ERROR;
1399 x_return_code := 'OTHER';
1400 close check_element_csr;
1401 return;
1402 End Validate_Element_Type_ID;
1403 --
1404 ----------------------------------VALIDATE_PROJECT_DETAILS-----------------------------------
1405 -- This procedure is to validate POETA
1406 Procedure Validate_Project_details(X_Project_ID IN NUMBER,
1407 X_task_id IN NUMBER,
1408 X_award_id IN NUMBER,
1409 X_expenditure_type IN VARCHAR2,
1410 X_exp_org_id IN NUMBER,
1411 X_gms_pa_install IN VARCHAR2,
1412 X_Person_ID IN VARCHAR2,
1413 X_Effective_date IN DATE,
1414 X_return_status OUT NOCOPY VARCHAR2,
1415 X_return_code OUT NOCOPY VARCHAR2) IS
1416 /*************************************************************************************
1417 Commented for Bug 2096440 - Suspense Reason Code to display the error code returned by PA instead of using custom
1418 code. -lveerubh
1419
1420 CURSOR check_project_csr IS
1421 SELECT project_id
1422 FROM gms_projects_expend_v
1423 where project_id = x_project_id;
1424
1425 CURSOR check_exp_org_csr IS
1426 SELECT organization_id
1427 FROM pa_organizations_expend_v
1428 WHERE organization_id = x_exp_org_id
1429 AND active_flag = 'Y';
1430
1431 CURSOR check_task_csr IS
1432 SELECT task_id
1433 FROM pa_tasks_expend_v
1434 WHERE project_id = x_project_id and
1435 task_id = x_task_id;
1436 --End of Commenting for Bug 2096440
1437 ****************************************************************************************/
1438
1439 /* Commented for bug 2054610
1440 CURSOR check_award_csr IS
1441 SELECT award_id
1442 FROM gms_awards_basic_v
1443 WHERE award_id = x_award_id
1444 and project_id = x_project_id
1445 and ROWNUM = 1; */
1446 /************************************************************************************
1447 Commented for Bug 2096440 - Suspense Reason Code to display the error code returned by PA instead of using custom
1448 code. -lveerubh
1449 l_project_id number(15);
1450
1451 CURSOR check_exp_type_csr IS
1452 SELECT et.expenditure_type
1453 FROM pa_expenditure_types_expend_v et
1454 WHERE et.system_linkage_function IN ('STRAIGHT_TIME', 'ST') and
1455 exists(select a.expenditure_type
1456 from gms_allowable_expenditures a
1457 where a.expenditure_type = et.expenditure_type
1458 and a.allowability_schedule_id = (select allowable_schedule_id
1459 from gms_awards
1460 where award_id = x_award_id))
1461 and et.expenditure_type = x_Expenditure_Type;
1462
1463
1464 CURSOR check_exp_type_csr1 IS
1465 SELECT et.expenditure_type
1466 FROM pa_expenditure_types_expend_v et
1467 WHERE et.system_linkage_function IN ('STRAIGHT_TIME', 'ST')
1468 and et.expenditure_type = x_Expenditure_Type;
1469
1470
1471 l_exp_type varchar2(80);
1472 l_control_num number(1) := 0;
1473 --End of Commenting for Bug 2096440
1474 *****************************************************************************/
1475 l_msg_app VARCHAR2(80);
1476 l_msg_type VARCHAR2(80);
1477 l_msg_token1 VARCHAR2(80);
1478 l_msg_token2 VARCHAR2(80);
1479 l_msg_token3 VARCHAR2(80);
1480 l_msg_count NUMBER;
1481 l_patc_status VARCHAR2(2000); -- Increased the width from 80 to 2000 for bug fix 2636830
1482 l_award_status VARCHAR2(2000); -- Increased the width from 80 to 2000 for bug fix 2636830
1483 l_billable_flag VARCHAR2(80);
1484
1485 begin
1486
1487 IF X_GMS_PA_INSTALL IN ('PA_ONLY','PA_GMS') THEN
1488 --- added if condition for 2985061
1489 if x_exp_org_id is null then
1490 x_return_status := FND_API.G_RET_STS_ERROR;
1491 x_return_code := 'NULL_EXP_ORG';
1492 return;
1493 else
1494 pa_transactions_pub.validate_transaction(
1495 x_project_id => x_project_id,
1496 x_task_id => x_task_id,
1497 x_ei_date => x_effective_date,
1498 x_expenditure_type => x_expenditure_type,
1499 x_non_labor_resource => null,
1500 x_person_id => x_person_id,
1501 x_incurred_by_org_id => x_exp_org_id,
1502 x_calling_module => 'PSPLDPGB',
1503 x_msg_application => l_msg_app,
1504 x_msg_type => l_msg_type,
1505 x_msg_token1 => l_msg_token1,
1506 x_msg_token2 => l_msg_token2,
1507 x_msg_token3 => l_msg_token3,
1508 x_msg_count => l_msg_count,
1509 x_msg_data => l_patc_status,
1510 x_billable_flag => l_billable_flag,
1511 p_sys_link_function => 'ST'); --Bug 5639589: Added parameter
1512
1513 ----dbms_output.put_line('patc stat 1 '|| l_patc_status);
1514 ----dbms_output.put_line('x_project_id'|| x_project_id);
1515
1516
1517 if l_patc_status is not null then
1518 x_return_status := FND_API.G_RET_STS_ERROR;
1519 ----Commented for Bug 2096440 : Added the following line-
1520 ----Passing l_patc_status as the return code ,which will be passed to stick_suspense_account procedure
1521 ----as suspense_reason_code
1522 -- x_return_code := 'INV_PATC';
1523 x_return_code := substr(l_patc_status,1,50); --Added the line
1524 return;
1525 end if;
1526 -----------
1527 --IF X_GMS_PA_INSTALL = 'PA_GMS' THEN Commented for bug fix 2908859
1528 IF (psp_general.get_sponsored_flag(x_project_id) = 'Y') THEN -- Introduced for bug fix 2908859
1529 --l_control_num := 4; Bug 2096440
1530 /* Commented for bug 2054610
1531 open check_award_csr;
1532 fetch check_award_csr into l_project_id;
1533 if check_award_csr%NOTFOUND then
1534 x_return_status := FND_API.G_RET_STS_ERROR;
1535 x_return_code := 'INV_AI';
1536 close check_award_csr;
1537 return;
1538 else
1539 x_return_status := FND_API.G_RET_STS_SUCCESS;
1540 x_return_code := ' ';
1541 end If;
1542 close check_award_csr; */
1543
1544 if l_patc_status is null then
1545 gms_transactions_pub.validate_transaction
1546 (x_project_id,
1547 x_task_id,
1548 x_award_id,
1549 x_expenditure_type,
1550 x_effective_date,
1551 'PSPLDPGB',
1552 l_award_status);
1553
1554 IF l_award_status IS NOT NULL THEN
1555 x_return_status := FND_API.G_RET_STS_ERROR;
1556 ----Commented for Bug 2096440 : Added the following line-
1557 ----Passing l_award_status as the return code ,which will be passed to stick_suspense_account procedure
1558 ----as suspense_reason_code
1559 --x_return_code := 'INV_PATCAW';
1560 x_return_code := substr(l_award_status,1,50);
1561 return;
1562 END IF;
1563 END IF;
1564 END IF; --End if of PA_GMS , for Bug 2096440
1565 end if; ---2985061
1566 END IF; --For Bug 2096440
1567
1568 x_return_status := FND_API.G_RET_STS_SUCCESS;
1569 x_return_code := ' ';
1570 /*******************************************************************************
1571 Commented For Bug 2096440
1572
1573 l_control_num := 5;
1574 open check_exp_type_csr;
1575 fetch check_exp_type_csr into l_exp_type;
1576 if check_exp_type_csr%NOTFOUND then
1577 x_return_status := FND_API.G_RET_STS_ERROR;
1578 x_return_code := 'INV_ET';
1579 close check_exp_type_csr;
1580 return;
1581 else
1582 x_return_status := FND_API.G_RET_STS_SUCCESS;
1583 x_return_code := ' ';
1584 end If;
1585 END IF;
1586 IF X_GMS_PA_INSTALL = 'PA_ONLY' THEN
1587 l_control_num := 5;
1588 open check_exp_type_csr1;
1589 fetch check_exp_type_csr1 into l_exp_type;
1590 if check_exp_type_csr1%NOTFOUND then
1591 x_return_status := FND_API.G_RET_STS_ERROR;
1592 x_return_code := 'INV_ET';
1593 close check_exp_type_csr1;
1594 return;
1595 else
1596 x_return_status := FND_API.G_RET_STS_SUCCESS;
1597 x_return_code := ' ';
1598 end If;
1599 EN IF;
1600 END IF;
1601 --End of Commenting for Byug 2096440
1602 ************************************************************************************/
1603
1604 EXCEPTION
1605 when OTHERS then
1606 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Validate_Project_Details : Unexpected Error');
1607 x_return_status := FND_API.G_RET_STS_ERROR;
1608 x_return_code := 'OTHER';
1609 /*************************************************************************************
1610 Commenting for bug 2096440
1611 if l_control_num = 1 then
1612 close check_project_csr;
1613 elsif l_control_num = 2 then
1614 close check_exp_org_csr;
1615 elsif l_control_num = 3 then
1616 close check_task_csr;
1617 elsif l_control_num = 4 then
1618 -- close check_award_csr; --Commented for bug 2054610;
1619 null; --Added for bug 2054610.
1620 elsif l_control_num = 5 then
1621 close check_exp_type_csr;
1622 end if;
1623 ************************************************************************************/
1624 return;
1625 END;
1626 --
1627 ----------------------------------UPDATE_RECORD_WITH_ERROR-----------------------------------
1628 -- This procedure is to update the psp_distribution_interface table
1629 -- with given error code
1630 --
1631 Procedure update_record_with_error(X_distribution_interface_id IN Number,
1632 X_error_code IN Varchar2,
1633 X_return_status OUT NOCOPY varchar2) IS
1634 begin
1635 UPDATE psp_distribution_interface
1636 SET status_code = 'E',
1637 error_code = x_error_code
1638 WHERE distribution_interface_id = x_distribution_interface_id;
1639
1640 if SQL%NOTFOUND then
1641 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1642 end if;
1643 --dbms_output.PUT_LINE('.....Update Record With Error ');
1644 x_return_status := FND_API.G_RET_STS_SUCCESS;
1645 EXCEPTION
1646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
1647 fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_error : Error while updating');
1648 x_return_status := FND_API.G_RET_STS_ERROR;
1649 WHEN OTHERS then
1650 fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_error : Unexpected error');
1651 x_return_status := FND_API.G_RET_STS_ERROR;
1652
1653 end;
1654 --
1655 ----------------------------------UPDATE_RECORD_WITH_VALID-----------------------------------
1656 -- This procedure is to update psp_distribution_interface table with Valid status
1657 --
1658 Procedure update_record_with_valid(X_distribution_interface_id IN Number,
1659 X_return_status OUT NOCOPY varchar2) IS
1660 begin
1661 UPDATE psp_distribution_interface
1662 SET status_code = 'V',
1663 error_code = NULL
1664 WHERE distribution_interface_id = x_distribution_interface_id;
1665
1666 if SQL%NOTFOUND then
1667 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1668 end if;
1669
1670 x_return_status := FND_API.G_RET_STS_SUCCESS;
1671 EXCEPTION
1672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
1673 fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_valid : Error while updating');
1674 x_return_status := FND_API.G_RET_STS_ERROR;
1675 WHEN OTHERS then
1676 fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_valid : Unexpected error');
1677 x_return_status := FND_API.G_RET_STS_ERROR;
1678
1679 end;
1680 --
1681 Function get_least_date(x_time_period_id IN Number,x_person_id IN Number, x_gl_ccid IN Number,
1682 x_project_id IN Number, x_award_id IN Number,
1683 x_task_id IN Number,x_distribution_date IN Date) Return Date IS
1684 l_project_end_date DATE;
1685 l_award_end_date DATE;
1686 l_payroll_end_date DATE;
1687 l_task_end_date DATE;
1688 l_effective_date DATE;
1689 l_payroll_begin_date DATE; -- Added by Pvelamur as a fix for 900768
1690 l_termination_date DATE;
1691
1692 begin
1693 SELECT start_date,end_date
1694 into l_payroll_begin_date,l_payroll_end_date
1695 FROM per_time_periods
1696 WHERE time_period_id = x_time_period_id;
1697
1698 if NVL(x_gl_ccid,0) = 0 and NVL(x_project_id,0) = 0 then
1699 return x_distribution_date;
1700 end if;
1701 If NVL(x_gl_ccid,0) = 0 then
1702 SELECT nvl(completion_date,l_payroll_end_date)
1703 INTO l_project_end_date
1704 FROM pa_projects_all
1705 WHERE project_id = x_project_id;
1706 --
1707 SELECT nvl(end_date_active,l_payroll_end_date)
1708 INTO l_award_end_date
1709 FROM gms_awards
1710 WHERE award_id = x_award_id;
1711 --
1712 -- The following code added by PVELAMUR to fix bug 888089
1713 SELECT nvl(completion_date,l_payroll_end_date)
1714 INTO l_task_end_date
1715 FROM pa_tasks
1716 WHERE task_id = x_task_id;
1717 -- The above code added by PVELAMUR tp fix the bug 888089
1718 -- The following code added by PVELAMUR to fix bug 900768
1719 SELECT nvl(actual_termination_date,l_payroll_end_date)
1720 into l_termination_date
1721 FROM per_periods_of_service
1722 WHERE person_id = x_person_id and
1723 (date_start between l_payroll_begin_date and l_payroll_end_date) ;
1724 -- The above code added by PVELAMUR tp fix the bug 900768
1725
1726 SELECT least(l_payroll_end_date,l_project_end_date,l_award_end_date,l_task_end_date,l_termination_date)
1727 INTO l_effective_date
1728 FROM dual;
1729 return l_effective_date;
1730 else
1731 return l_payroll_end_date;
1732 end if;
1733 --
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 -- fnd_msg_pub.add_exc_msg('PSP_PREGEN','GET_LEAST_DATE');
1737 return x_distribution_date;
1738 END get_least_date;
1739 --
1740
1741 /* autopop stuff */
1742
1743 -- Called from Import_Pregen when autopop profile option is set to 'Y'
1744 -- fetches all records from psp_distribution_interface table belongs to given batch name and
1745 -- validates each record whether it is valid or not for purposes of running auto-population.
1746 -- If a record is valid then auto-population will replace the expenditure type for
1747 -- project lines or the GL code combination ID for Gl lines. The regular import Pregen process is
1748 -- called separately and will re-do valoidations to insure the new expenditure type and other info
1749 -- is valid before it imports the lines into LDM.
1750 -- Subha 10/mar/2000 Multi-org and validation changes
1751
1752 Procedure Autopop( X_Batch_name IN VARCHAR2,
1753 X_Set_of_Books_Id IN NUMBER,
1754 X_Business_Group_Id IN NUMBER,
1755 X_Operating_Unit IN NUMBER,
1756 X_Gms_Pa_Install IN VARCHAR2,
1757 X_Return_Status OUT NOCOPY VARCHAR2
1758 )IS
1759
1760 --For Bug 2651339 : Introduced the status code check
1761 --to avoid revalidation of valid records
1762 CURSOR get_all_from_interface_csr is
1763 SELECT *
1764 FROM psp_distribution_interface
1765 WHERE batch_name = x_batch_name
1766 AND status_code <> 'V'; --Introduced for bug 2651339
1767 -- FOR UPDATE; Commented FOR UPDATE for bug fix 2094036
1768
1769 g_pregen_rec get_all_from_interface_csr%ROWTYPE;
1770
1771 --For Bug 2616807 : Modifying the Select to check for correct source type 'P' instead of 'N'
1772 CURSOR get_batch_name_csr is
1773 SELECT count(*)
1774 FROM psp_payroll_controls
1775 WHERE source_type = 'P' and
1776 batch_name = x_batch_name;
1777
1778 l_batch_name_count number;
1779
1780 -- Error Handling variables
1781 l_error_api_name varchar2(2000);
1782 l_return_status varchar2(1);
1783 l_return_code varchar2(30);
1784 l_batch_status number(1) := 0;
1785 l_msg_count number;
1786 l_msg_data varchar2(2000);
1787 l_msg_index_out number;
1788 --
1789 l_api_name varchar2(30) := 'PSP_PREGEN';
1790 l_subline_message varchar2(200);
1791 --
1792
1793 -- Auto-Population Variables
1794 l_new_expenditure_type varchar2(30);
1795 l_new_gl_code_combination_id number(15);
1796 l_autopop_status varchar2(1);
1797
1798 BEGIN
1799 --dbms_output.PUT_LINE('................0');
1800 /* Commented the following as the locking is taken care in the main procedure
1801 open get_all_from_interface_csr;
1802 fetch get_all_from_interface_csr into g_pregen_rec;
1803
1804 if get_all_from_interface_csr%NOTFOUND then
1805 RAISE NO_DATA_FOUND;
1806 end if;
1807 close get_all_from_interface_csr;
1808 End of bug fix 2094036 */
1809
1810 --dbms_output.PUT_LINE('................1');
1811 open get_batch_name_csr;
1812 fetch get_batch_name_csr into l_batch_name_count;
1813 close get_batch_name_csr;
1814
1815 if NVL(l_batch_name_count,0) > 0 then
1816 fnd_message.set_name('PSP','PSP_PI_INVALID_BATCH_NAME');
1817 fnd_message.set_token('PSP_BATCH_NAME',x_batch_name);
1818 fnd_msg_pub.add;
1819 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1820 end if;
1821
1822 open get_all_from_interface_csr;
1823 LOOP
1824 --dbms_output.PUT_LINE('................2');
1825 fetch get_all_from_interface_csr into g_pregen_rec;
1826 EXIT WHEN get_all_from_interface_csr%NOTFOUND ; -- Exit when last record is reached
1827
1828 --dbms_output.PUT_LINE('................3');
1829 --dbms_output.PUT_LINE('Interface ID....' || to_char(g_pregen_rec.distribution_interface_id));
1830
1831 Validate_Person_ID( X_Person_ID => g_pregen_rec.person_id,
1832 X_Effective_date => g_pregen_rec.distribution_date,
1833 X_Payroll_ID => g_pregen_rec.payroll_id,
1834 X_set_of_books_id => x_set_of_books_id,
1835 X_return_status => x_return_status,
1836 X_return_code => l_return_code,
1837 X_Business_group_Id => x_business_group_id
1838 );
1839 --dbms_output.PUT_LINE('................4');
1840 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1841 l_batch_status := 1;
1842 if l_return_code = 'OTHER' then
1843 l_error_api_name := 'VALIDATE_PERSON_ID';
1844 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1845 else
1846 update_record_with_error(
1847 X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
1848 X_error_code => l_return_code,
1849 X_return_status => x_return_status);
1850 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1851 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1852 end if;
1853 end if;
1854 else
1855
1856 Validate_Assignment_ID(X_Assignment_ID => g_pregen_rec.assignment_id,
1857 X_Effective_Date => g_pregen_rec.distribution_date,
1858 X_Return_Status => x_return_status,
1859 X_Return_Code => l_return_code,
1860 X_Business_Group_Id =>x_business_group_id,
1861 X_Set_of_Books_Id => x_set_of_books_id);
1862 --dbms_output.PUT_LINE('................5');
1863 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1864 l_batch_status := 1;
1865 if l_return_code = 'OTHER' then
1866 l_error_api_name := 'VALIDATE_ASSIGNMENT_ID';
1867 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1868 else
1869 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
1870 X_error_code => l_return_code,
1871 X_return_status => x_return_status);
1872 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1873 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1874 end if;
1875 end if;
1876
1877 else
1878 ---For Bug 2616807 : Validate Payroll Id and Validate Payroll source Code missing from AUTOPOP
1879 --Adding the following code
1880 Validate_Payroll_ID(X_Payroll_ID => g_pregen_rec.payroll_id,
1881 X_Assignment_ID => g_pregen_rec.assignment_id,
1882 X_Effective_Date => g_pregen_rec.distribution_date,
1883 X_return_status => x_return_status,
1884 X_return_code => l_return_code,
1885 X_business_group_id => x_business_group_id,
1886 X_set_of_books_id => x_set_of_books_id);
1887
1888 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1889 l_batch_status := 1;
1890 IF l_return_code = 'OTHER' THEN
1891 l_error_api_name := 'VALIDATE_PAYROLL_ID';
1892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1893 ELSE
1894 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
1895 X_error_code => l_return_code,
1896 X_return_status => x_return_status);
1897 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1898 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1899 END IF;
1900 END IF;
1901 ELSE
1902 --End of Changes for Bug 2616807
1903 Validate_Payroll_Period_ID(X_Payroll_ID => g_pregen_rec.payroll_id,
1904 X_Payroll_Period_ID => g_pregen_rec.time_period_id,
1905 X_Effective_Date => g_pregen_rec.distribution_date,
1906 X_return_status => x_return_status,
1907 X_return_code => l_return_code);
1908 --dbms_output.PUT_LINE('................7');
1909 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1910 l_batch_status := 1;
1911 if l_return_code = 'OTHER' then
1912 l_error_api_name := 'VALIDATE_PAYROLL_PERIOD_ID';
1913 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1914 else
1915 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
1916 X_error_code => l_return_code,
1917 X_return_status => x_return_status);
1918 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1919 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1920 end if;
1921 end if;
1922 else
1923 --For Bug fix 2985061 : adding the validation of DR_CR Flag
1924
1925 VALIDATE_DR_CR_FLAG ( X_DR_CR_FLAG => g_pregen_rec.dr_cr_flag,
1926 X_return_status => x_return_status,
1927 X_return_code => l_return_code);
1928
1929 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1930 l_batch_status := 1;
1931 IF l_return_code = 'OTHER' THEN
1932 l_error_api_name := 'VALIDATE_DR_CR_FLAG';
1933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1934 ELSE
1935 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
1936 X_error_code => l_return_code,
1937 X_return_status => x_return_status);
1938 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1939 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1940 END IF;
1941 END IF;
1942 ELSE
1943 --End of Changes for Bug 2985061
1944
1945 --For Bug fix 2616807 : adding the validation of Source code
1946 Validate_Payroll_Source_Code(X_Payroll_Source_Code => g_pregen_rec.source_code,
1947 X_return_status => x_return_status,
1948 X_return_code => l_return_code);
1949
1950 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1951 l_batch_status := 1;
1952 IF l_return_code = 'OTHER' THEN
1953 l_error_api_name := 'VALIDATE_PAYROLL_SOURCE_CODE';
1954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1955 ELSE
1956 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
1957 X_error_code => l_return_code,
1958 X_return_status => x_return_status);
1959 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1961 END IF;
1962 END IF;
1963 ELSE
1964 --End of Changes for Bug 2616807
1965 Validate_Element_Type_ID(X_Element_Type_ID => g_pregen_rec.element_type_id,
1966 X_Payroll_Period_ID => g_pregen_rec.time_period_id,
1967 -- Introduced BG/SOB parameters for bug fix 3098050
1968 x_business_group_id => x_business_group_id,
1969 x_set_of_books_id => x_set_of_books_id,
1970 X_return_status => x_return_status,
1971 X_return_code => l_return_code);
1972 --dbms_output.PUT_LINE('................9');
1973 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1974 l_batch_status := 1;
1975 if l_return_code = 'OTHER' then
1976 l_error_api_name := 'VALIDATE_ELEMENT_TYPE_ID';
1977 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1978 else
1979 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
1980 X_error_code => l_return_code,
1981 X_return_status => x_return_status);
1982 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1983 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1984 end if;
1985 end if;
1986
1987 elsif g_pregen_rec.gl_code_combination_id IS NULL and
1988 g_pregen_rec.project_id IS NULL then
1989 l_batch_status := 1;
1990 l_return_code := 'NUL_GLP';
1991 update_record_with_error(
1992 X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
1993 X_error_code => l_return_code,
1994 X_return_status => x_return_status);
1995 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1996 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1997 end if;
1998 elsif g_pregen_rec.gl_code_combination_id IS NOT NULL and
1999 g_pregen_rec.project_id IS NOT NULL then
2000 l_batch_status := 1;
2001 l_return_code := 'NOT_GLP';
2002 update_record_with_error(
2003 X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
2004 X_error_code => l_return_code,
2005 X_return_status => x_return_status);
2006
2007 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2008 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2009 end if;
2010
2011 elsif g_pregen_rec.project_id is not null then /* Bug fix 2985061 */
2012 hr_utility.trace('project_id is not null');
2013 -- elsif (NVL(g_pregen_rec.project_id,0) <> 0) then
2014
2015 /********************************************************************************************************
2016
2017 Batch contains a project record , yet projects is not installed. cannot proceed
2018
2019 *********************************************************************************************************/
2020
2021
2022 if x_gms_pa_install ='NO_PA_GMS' then
2023 l_batch_status:=1;
2024 x_return_status:= FND_API.G_RET_STS_ERROR;
2025 l_return_code:='NO_PA';
2026 update_record_with_error(
2027 X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
2028 X_error_code => l_return_code,
2029 X_return_status => x_return_status);
2030 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2031 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2032 end if;
2033
2034
2035
2036 else /* projects is installed . Can proceed with the validations */
2037
2038 Validate_Project_details(X_Project_ID => g_pregen_rec.project_id,
2039 X_task_id => g_pregen_rec.task_id,
2040 X_award_id => g_pregen_rec.award_id,
2041 X_expenditure_type => g_pregen_rec.expenditure_type,
2042 X_exp_org_id => g_pregen_rec.expenditure_organization_id,
2043 X_gms_pa_install => x_gms_pa_install,
2044 X_Person_ID => g_pregen_rec.person_id,
2045 X_Effective_date => g_pregen_rec.distribution_date,
2046 X_return_status => x_return_status,
2047 X_return_code => l_return_code);
2048 --dbms_output.PUT_LINE('................10');
2049 --dbms_output.PUT_LINE('return status.....' || l_return_status);
2050 --dbms_output.PUT_LINE('return code.....' || l_return_code);
2051
2052 end if;
2053 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2054 --- l_batch_status := 1; /* Bug 2007521 */
2055 if l_return_code = 'OTHER' then
2056 l_error_api_name := 'VALIDATE_PROJECTS_DETAILS';
2057 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2058 else
2059 if g_use_pre_gen_suspense = 'Y' then
2060 stick_suspense_account( g_pregen_rec.assignment_id,
2061 g_pregen_rec.distribution_date,
2062 x_gms_pa_install,
2063 g_pregen_rec.person_id,
2064 g_pregen_rec.distribution_interface_id,
2065 l_return_code,
2066 x_business_group_id,
2067 x_set_of_books_id,
2068 x_return_status);
2069 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2070 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2071 end if;
2072 /* Bug 2007521: moved code into stick suspense a/c
2073 update_record_with_valid(X_distribution_interface_id=>
2074 g_pregen_rec.distribution_interface_id,
2075 X_return_status => l_return_status);
2076 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2077 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2078 end if; */
2079 else
2080 l_batch_status := 1;
2081 update_record_with_error(
2082 X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
2083 X_error_code => l_return_code,
2084 X_return_status => x_return_status);
2085
2086 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2087 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2088 end if;
2089 end if;
2090 end if;
2091 else
2092 -- Call Auto-Population for a new expenditure type.
2093 hr_utility.trace('Calling Autopop for Exp type');
2094 psp_autopop.main(p_acct_type => 'E',
2095 p_person_id => g_pregen_rec.person_id,
2096 p_assignment_id => g_pregen_rec.assignment_id,
2097 p_element_type_id => g_pregen_rec.element_type_id,
2098 p_project_id => g_pregen_rec.project_id,
2099 p_expenditure_organization_id => g_pregen_rec.expenditure_organization_id,
2100 p_task_id => g_pregen_rec.task_id,
2101 p_award_id => g_pregen_rec.award_id,
2102 p_expenditure_type => g_pregen_rec.expenditure_type,
2103 p_gl_code_combination_id => null,
2104 p_payroll_date => g_pregen_rec.distribution_date,
2105 p_set_of_books_id => x_set_of_books_id,
2106 p_business_group_id => x_business_group_id,
2107 ret_expenditure_type => l_new_expenditure_type,
2108 ret_gl_code_combination_id => l_new_gl_code_combination_id,
2109 retcode => l_autopop_status);
2110
2111 IF l_autopop_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2112 l_batch_status := 1;
2113 l_return_code := 'AUTOPOP_EXP_ERR';
2114 update_record_with_error(X_distribution_interface_id =>
2115 g_pregen_rec.distribution_interface_id,
2116 X_error_code => l_return_code,
2117 X_return_status => x_return_status);
2118 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2119 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2120 end if;
2121 ELSIF l_autopop_status = FND_API.G_RET_STS_ERROR THEN
2122 /********Will not populate distribution interface table if autopop returns no value
2123 ******** as it is not considered an error condition.
2124 l_return_code := 'AUTOPOP_NO_VAL';
2125 update_record_with_error(X_distribution_interface_id =>
2126 g_pregen_rec.distribution_interface_id,
2127 X_error_code => l_return_code,
2128 X_return_status => x_return_status);
2129 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2130 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2131 end if;
2132 *********************************************************/
2133
2134 l_return_code := 0;
2135 update_record_with_valid(X_distribution_interface_id =>
2136 g_pregen_rec.distribution_interface_id,
2137 X_return_status => x_return_status);
2138 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2139 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2140 end if;
2141 ELSIF l_autopop_status = FND_API.G_RET_STS_SUCCESS THEN
2142 update_record_with_exp(X_distribution_interface_id =>
2143 g_pregen_rec.distribution_interface_id,
2144 X_expenditure_type => l_new_expenditure_type,
2145 X_return_status => x_return_status);
2146 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2147 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2148 end if;
2149 /* Introduced validation for Bug 2007521 */
2150 Validate_Project_details(X_Project_ID => g_pregen_rec.project_id,
2151 X_task_id => g_pregen_rec.task_id,
2152 X_award_id => g_pregen_rec.award_id,
2153 X_expenditure_type => l_new_expenditure_type,
2154 X_exp_org_id => g_pregen_rec.expenditure_organization_id,
2155 X_gms_pa_install => x_gms_pa_install,
2156 X_Person_ID => g_pregen_rec.person_id,
2157 X_Effective_date => g_pregen_rec.distribution_date,
2158 X_return_status => x_return_status,
2159 X_return_code => l_return_code);
2160
2161 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2162 if l_return_code = 'OTHER' then
2163 l_error_api_name := 'VALIDATE_PROJECTS_DETAILS';
2164 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2165 else
2166 if g_use_pre_gen_suspense = 'Y' then
2167 /* stick suspense, also makes Valid and puts in error code Bug 2007521 */
2168 stick_suspense_account( g_pregen_rec.assignment_id,
2169 g_pregen_rec.distribution_date,
2170 x_gms_pa_install,
2171 g_pregen_rec.person_id,
2172 g_pregen_rec.distribution_interface_id,
2173 l_return_code,
2174 x_business_group_id,
2175 x_set_of_books_id,
2176 x_return_status);
2177 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2178 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2179 end if;
2180 else
2181 l_batch_status := 1;
2182 update_record_with_error(
2183 X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
2184 X_error_code => l_return_code,
2185 X_return_status => x_return_status);
2186 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2187 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2188 end if;
2189 end if;
2190 end if;
2191 else
2192 ----dbms_output.put_line ('Update record with valid');
2193 l_return_code := 0;
2194 update_record_with_valid(X_distribution_interface_id =>
2195 g_pregen_rec.distribution_interface_id,
2196 X_return_status => x_return_status);
2197 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2198 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2199 end if;
2200 end if;
2201 END IF;
2202 END IF;
2203 else
2204 /* Bug fix 2985061, moved this code here from separate elseif for 4717564 */
2205 validate_gl_cc_id( x_code_combination_id => g_pregen_rec.gl_code_combination_id,
2206 x_return_status => x_return_status,
2207 x_return_code => l_return_code);
2208
2209 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2210 l_batch_status := 1;
2211 IF l_return_code = 'OTHER' THEN
2212 l_error_api_name := 'VALIDATE_GL_CC_ID';
2213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2214 ELSE
2215 update_record_with_error(X_distribution_interface_id => g_pregen_rec.distribution_interface_id,
2216 X_error_code => l_return_code,
2217 X_return_status => x_return_status);
2218 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2220 END IF;
2221 END IF;
2222 END IF;
2223 -- Call Auto-Population for a new GL Code Combination Id that has a new Natural Account.
2224 psp_autopop.main(p_acct_type => 'N',
2225 p_person_id => g_pregen_rec.person_id,
2226 p_assignment_id => g_pregen_rec.assignment_id,
2227 p_element_type_id => g_pregen_rec.element_type_id,
2228 p_project_id => null,
2229 p_expenditure_organization_id => null,
2230 p_task_id => null,
2231 p_award_id => null,
2232 p_expenditure_type => null,
2233 p_gl_code_combination_id => g_pregen_rec.gl_code_combination_id,
2234 p_payroll_date => g_pregen_rec.distribution_date,
2235 p_set_of_books_id => x_set_of_books_id,
2236 p_business_group_id => x_business_group_id,
2237 ret_expenditure_type => l_new_expenditure_type,
2238 ret_gl_code_combination_id => l_new_gl_code_combination_id,
2239 retcode => l_autopop_status);
2240
2241 IF l_autopop_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2242 l_batch_status := 1;
2243 l_return_code := 'AUTOPOP_NA_ERR';
2244 update_record_with_error(X_distribution_interface_id =>
2245 g_pregen_rec.distribution_interface_id,
2246 X_error_code => l_return_code,
2247 X_return_status => x_return_status);
2248 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2249 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2250 end if;
2251 ELSIF l_autopop_status = FND_API.G_RET_STS_ERROR THEN
2252
2253 /********Will not populate distribution interface table if autopop returns no value
2254 ******** as it is not considered an error condition.
2255 l_return_code := 'AUTOPOP_NO_VAL';
2256 update_record_with_error(X_distribution_interface_id =>
2257 g_pregen_rec.distribution_interface_id,
2258 X_error_code => l_return_code,
2259 X_return_status => x_return_status);
2260 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2261 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2262 end if;
2263 ******************************************************/
2264 l_return_code := 0;
2265 update_record_with_valid(X_distribution_interface_id =>
2266 g_pregen_rec.distribution_interface_id,
2267 X_return_status => x_return_status);
2268 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2269 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2270 end if;
2271 ELSIF l_autopop_status = FND_API.G_RET_STS_SUCCESS THEN
2272 ----dbms_output.put_line ('Update record with valid');
2273 l_return_code := 0;
2274 update_record_with_valid(X_distribution_interface_id =>
2275 g_pregen_rec.distribution_interface_id,
2276 X_return_status => x_return_status);
2277 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2278 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2279 end if;
2280
2281 update_record_with_na(X_distribution_interface_id =>
2282 g_pregen_rec.distribution_interface_id,
2283 X_gl_code_combination_id => l_new_gl_code_combination_id,
2284 X_return_status => x_return_status);
2285 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2286 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2287 end if;
2288 END IF;
2289 END IF;
2290 end if;
2291 end if;
2292 END IF;
2293 end if; -- Added for bug 2985061
2294 END IF; --Added for Bug2616807
2295 END IF; --Added for Bug 2616807
2296
2297 END LOOP;
2298 close get_all_from_interface_csr;
2299 --dbms_output.PUT_LINE('...End Loop .....l_batch_status ' || to_char(l_batch_status));
2300
2301
2302 -- errbuf := l_subline_message;
2303
2304 /* Changed the return code for errors , so that regular pre-gen does not repeat the validations
2305 again in case of errors either during the first phase of validations or
2306 'AUTOPOP_NA_ERR, or AUTOPOP_EXP_ERR
2307 :- Subha, Jly 17, 2000
2308 */
2309
2310
2311 if l_batch_status =1 then
2312 /* 2007521: Introduced update statement, to revert sticking suspense a/c if
2313 there are some other errors. Give chance to user to correct all errors */
2314 if g_use_pre_gen_suspense = 'Y' then
2315 update psp_distribution_interface
2316 set suspense_org_account_id = null,
2317 status_code = 'E'
2318 where batch_name = x_batch_name and
2319 suspense_org_account_id is not null;
2320 end if;
2321
2322 fnd_message.set_name('PSP','PSP_BATCH_HAS_ERRORS');
2323 fnd_msg_pub.add;
2324 x_return_status:=FND_API.G_RET_STS_ERROR;
2325 else
2326 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2327 end if;
2328 -- commit; Commented COMMIT for bug fix 2094036, error reason codes get commited in main procedure.
2329 return;
2330 EXCEPTION
2331 WHEN NO_DATA_FOUND then
2332 close get_all_from_interface_csr;
2333 FND_MESSAGE.SET_NAME('PSP','PSP_LD_NO_TRANS');
2334 l_subline_message := fnd_message.get;
2335 -- errbuf := SUBSTR(l_error_api_name ||fnd_global.local_chr(10) || l_subline_message,1,230);
2336 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2337 return;
2338
2339 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
2340 ----dbms_output.put_line('Unexpected Error...........');
2341 /*******************************************************************************************************
2342 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
2343 p_encoded => FND_API.G_FALSE,
2344 p_data => l_msg_data,
2345 p_msg_index_out => l_msg_count);
2346
2347 Printed from Message Stack
2348 **********************************************************************************************************/
2349 -- errbuf := SUBSTR(l_error_api_name || fnd_global.local_chr(10) || l_msg_data || fnd_global.local_chr(10),1,230);
2350 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Autopop-Unexpected Error');
2351 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2352
2353 rollback;
2354 return;
2355 WHEN OTHERS then
2356 ----dbms_output.put_line('When others Error...........');
2357
2358 /*********************************************************************************************************
2359 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
2360 p_encoded => FND_API.G_FALSE,
2361 p_data => l_msg_data,
2362 p_msg_index_out => l_msg_count);
2363 -- errbuf := SUBSTR(l_error_api_name || fnd_global.local_chr(10) || l_msg_data || fnd_global.local_chr(10),1,230);
2364
2365 printed from mesasge stack
2366 **********************************************************************************************************/
2367 rollback;
2368 fnd_msg_pub.add_exc_msg('PSP_PREGEN','Autopop-Error');
2369 x_return_status := FND_API.G_RET_STS_ERROR;
2370 --- x_retcode := 2;
2371 return;
2372 END Autopop;
2373
2374 --
2375
2376 ----------------------------------UPDATE_RECORD_WITH_EXP-----------------------------------
2377 -- This procedure is to update psp_distribution_interface table with Auto-Populated expenditure type
2378 --
2379
2380 Procedure update_record_with_exp(X_distribution_interface_id IN Number,
2381 X_expenditure_type IN Varchar2,
2382 X_return_status OUT NOCOPY Varchar2) IS
2383 begin
2384 UPDATE psp_distribution_interface
2385 SET expenditure_type = X_expenditure_type
2386 WHERE distribution_interface_id = X_distribution_interface_id;
2387
2388 if SQL%NOTFOUND then
2389 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2390 end if;
2391
2392 x_return_status := FND_API.G_RET_STS_SUCCESS;
2393 EXCEPTION
2394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
2395 fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_exp : Error while updating');
2396 x_return_status := FND_API.G_RET_STS_ERROR;
2397 WHEN OTHERS then
2398 fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_exp : Unexpected error');
2399 x_return_status := FND_API.G_RET_STS_ERROR;
2400
2401 end;
2402
2403 --
2404 ----------------------------------UPDATE_RECORD_WITH_NA-----------------------------------
2405 -- This procedure is to update psp_distribution_interface table with Auto-Populated expenditure type
2406 --
2407
2408 Procedure update_record_with_na(X_distribution_interface_id IN Number,
2409 X_gl_code_combination_id IN Number,
2410 X_return_status OUT NOCOPY Varchar2) IS
2411 begin
2412 UPDATE psp_distribution_interface
2413 SET gl_code_combination_id = X_gl_code_combination_id
2414 WHERE distribution_interface_id = X_distribution_interface_id;
2415
2416 if SQL%NOTFOUND then
2417 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2418 end if;
2419
2420 x_return_status := FND_API.G_RET_STS_SUCCESS;
2421 EXCEPTION
2422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
2423 fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_na : Error while updating');
2424 x_return_status := FND_API.G_RET_STS_ERROR;
2425 WHEN OTHERS then
2426 fnd_msg_pub.add_exc_msg('PSP_PREGEN','update_record_with_na : Unexpected error');
2427 x_return_status := FND_API.G_RET_STS_ERROR;
2428
2429 end;
2430
2431 ---================================= GET_SUSPENSE_ACCOUNT ===================
2432 /* Introduced this function for bug 2007521.
2433 Gets suspense account for person/assignment org. */
2434 Procedure get_suspense_account(p_organization_id in number,
2435 p_organization_name varchar2,
2436 p_effective_date in date,
2437 p_gms_pa_install in varchar2,
2438 p_person_id in number,
2439 p_business_group_id in number,
2440 p_set_of_books_id in number,
2441 p_distribution_interface_id in number,
2442 x_suspense_account out NOCOPY number,
2443 x_return_status out NOCOPY varchar2,
2444 x_suspense_auto_glccid out NOCOPY number,
2445 x_suspense_auto_exp_type out NOCOPY varchar2) Is
2446 cursor org_suspense_cur(P_effective_date in date,
2447 p_account_type_code in varchar2) is
2448 SELECT organization_account_id,
2449 gl_code_combination_id,
2450 project_id,
2451 task_id,
2452 award_id,
2453 expenditure_type,
2454 expenditure_organization_id
2455 FROM psp_organization_accounts
2456 WHERE business_group_id = p_business_group_id
2457 AND set_of_books_id = p_set_of_books_id
2458 AND organization_id = p_organization_id
2459 AND account_type_code = p_account_type_code
2460 AND p_effective_date BETWEEN start_date_active AND
2461 nvl(end_date_active, p_effective_date);
2462 cursor org_suspense_cur2(p_suspense_account_id in number) is
2463 SELECT organization_account_id,
2464 gl_code_combination_id,
2465 project_id,
2466 task_id,
2467 award_id,
2468 expenditure_type,
2469 expenditure_organization_id
2470 FROM psp_organization_accounts
2471 WHERE organization_account_id = p_suspense_account_id;
2472
2473 /* Following cursor is added for bug 2514611 */
2474 CURSOR employee_name_cur IS
2475 SELECT full_name
2476 FROM per_people_f
2477 WHERE person_id =p_person_id;
2478
2479 v_return_value varchar2(30);
2480 v_return_Status varchar2(1);
2481 v_return_code varchar2(100);
2482 v_suspense_account_id number := NULL;
2483 l_employee_name VARCHAR2(240); --Added for bug 2514611
2484 suspense_rec org_suspense_cur%ROWTYPE;
2485
2486 profile_val_date_matches EXCEPTION;
2487 no_profile_exists EXCEPTION;
2488 no_val_date_matches EXCEPTION;
2489 no_global_acct_exists EXCEPTION;
2490 suspense_ac_invalid EXCEPTION;
2491 l_auto_status varchar2(100);
2492 l_auto_pop_status varchar2(100);
2493 l_acct_type varchar2(1);
2494 l_element_type_id number;
2495 l_assignment_id number;
2496 l_assignment_number varchar2(100);
2497 l_element_type varchar2(200);
2498 l_account varchar2(1000);
2499 l_auto_org_name hr_all_organization_units_tl.name%TYPE;
2500 l_new_exp_type varchar2(30);
2501 l_new_glccid number;
2502
2503 cursor get_element_type is
2504 select element_type_id,
2505 assignment_id
2506 from psp_distribution_interface
2507 where distribution_interface_id = p_distribution_interface_id;
2508
2509 cursor get_asg_details is
2510 select ppf.full_name,
2511 paf.assignment_number,
2512 pet.element_name,
2513 hou.name
2514 from per_all_people_f ppf,
2515 per_all_assignments_f paf,
2516 pay_element_types_f pet,
2517 hr_all_organization_units hou
2518 where ppf.person_id = p_person_id
2519 and p_effective_date between paf.effective_start_date and paf.effective_end_date
2520 and paf.assignment_id = l_assignment_id
2521 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
2522 and pet.element_type_id = l_element_type_id
2523 and p_effective_date between pet.effective_start_date and pet.effective_end_date
2524 and hou.organization_id = paf.organization_id;
2525
2526 Begin
2527 open org_suspense_cur(p_effective_date,'S');
2528 fetch org_suspense_cur into suspense_rec;
2529 if org_suspense_cur%NOTFOUND then
2530 close org_suspense_cur;
2531 v_return_value:= psp_general.find_global_suspense(p_effective_date,
2532 p_business_group_id,
2533 p_set_of_books_id,
2534 v_suspense_account_id );
2535
2536 IF v_return_value = 'PROFILE_VAL_DATE_MATCHES' THEN
2537 open org_suspense_cur2(v_suspense_account_id);
2538 fetch org_suspense_cur2 into suspense_rec;
2539 close org_suspense_cur2;
2540 ELSIF v_return_value = 'NO_GLOBAL_ACCT_EXISTS' THEN
2541 RAISE no_global_acct_exists;
2542 ELSIF v_return_value = 'NO_VAL_DATE_MATCHES' THEN
2543 RAISE no_val_date_matches;
2544 ELSIF v_return_value = 'NO_PROFILE_EXISTS' THEN
2545 RAISE no_profile_exists;
2546 END IF;
2547 else
2548 close org_suspense_cur;
2549 end if;
2550 --- autopop for suspense account 5080403
2551 if g_suspense_autopop = 'Y' then
2552 if suspense_rec.gl_code_combination_id is null then
2553 l_acct_type:='E';
2554 else
2555 l_acct_type:='N';
2556 end if;
2557 open get_element_type;
2558 fetch get_element_type into l_element_type_id, l_assignment_id;
2559 close get_element_type;
2560 psp_autopop.main(
2561 p_acct_type => l_acct_type,
2562 p_person_id => p_person_id,
2563 p_assignment_id => l_assignment_id,
2564 p_element_type_id => l_element_type_id,
2565 p_project_id => suspense_rec.project_id,
2566 p_expenditure_organization_id => suspense_rec.expenditure_organization_id,
2567 p_task_id => suspense_rec.task_id,
2568 p_award_id => suspense_rec.award_id,
2569 p_expenditure_type => suspense_rec.expenditure_type,
2570 p_gl_code_combination_id => suspense_rec.gl_code_combination_id,
2571 p_payroll_date => p_effective_date,
2572 p_set_of_books_id => p_set_of_books_id,
2573 p_business_group_id => p_business_group_id,
2574 ret_expenditure_type => l_new_exp_type,
2575 ret_gl_code_combination_id => l_new_glccid,
2576 retcode => l_auto_pop_status);
2577 /* fnd_file.put_line(fnd_file.log, 'Suspense.. After autopop call'|| 'p_acct_type =>'|| l_acct_type
2578 || 'p_person_id => '||p_person_id
2579 || ' p_assignment_id => '||l_assignment_id
2580 ||' p_element_type_id => '||l_element_type_id
2581 ||' p_project_id =>'|| suspense_rec.project_id
2582 ||' p_expenditure_organization_id =>'|| suspense_rec.expenditure_organization_id
2583 ||' p_task_id =>'|| suspense_rec.task_id
2584 ||' p_award_id =>'|| suspense_rec.award_id
2585 ||' p_expenditure_type =>'|| suspense_rec.expenditure_type
2586 ||' p_gl_code_combination_id =>'|| suspense_rec.gl_code_combination_id
2587 ||' p_payroll_date => '||p_effective_date
2588 ||' p_set_of_books_id => '||p_set_of_books_id
2589 ||' p_business_group_id => '||p_business_group_id);*/
2590
2591 if (l_auto_pop_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2592 (l_auto_pop_status = FND_API.G_RET_STS_ERROR) then
2593 if l_auto_pop_status = FND_API.G_RET_STS_UNEXP_ERROR then
2594 if l_acct_type ='N' then
2595 l_auto_status := 'AUTO_POP_NA_ERROR';
2596 else
2597 l_auto_status :='AUTO_POP_EXP_ERROR';
2598 end if;
2599 elsif l_auto_pop_status = FND_API.G_RET_STS_ERROR then
2600 l_auto_status := 'AUTO_POP_NO_VALUE';
2601 end if;
2602 open get_asg_details;
2603 fetch get_asg_details into l_employee_name, l_assignment_number, l_element_type, l_auto_org_name;
2604 close get_asg_details;
2605 psp_enc_crt_xml.p_set_of_books_id := p_set_of_books_id;
2606 psp_enc_crt_xml.p_business_group_id := p_business_group_id;
2607 if l_acct_type = 'N' then
2608 l_account :=
2609 psp_enc_crt_xml.cf_charging_instformula(suspense_rec.gl_code_combination_id,
2610 null,
2611 null,
2612 null,
2613 null,
2614 null);
2615 else
2616 l_account :=
2617 psp_enc_crt_xml.cf_charging_instformula(null,
2618 suspense_rec.project_id,
2619 suspense_rec.task_id,
2620 suspense_rec.award_id,
2621 l_new_exp_type,
2622 suspense_rec.expenditure_organization_id);
2623 end if;
2624 fnd_message.set_name('PSP','PSP_SUSPENSE_AUTOPOP_FAIL');
2625 fnd_message.set_token('ORG_NAME',l_auto_org_name);
2626 fnd_message.set_token('EMPLOYEE_NAME',l_employee_name);
2627 fnd_message.set_token('ASG_NUM',l_assignment_number);
2628 fnd_message.set_token('CHARGING_ACCOUNT',l_account);
2629 fnd_message.set_token('AUTOPOP_ERROR',l_auto_status);
2630 fnd_message.set_token('EFF_DATE',p_effective_date);
2631 fnd_msg_pub.add;
2632 x_return_status := fnd_api.g_ret_sts_unexp_error;
2633 else
2634 x_suspense_auto_glccid := l_new_glccid;
2635 x_suspense_auto_exp_type := l_new_exp_type;
2636 suspense_rec.gl_code_combination_id := x_suspense_auto_glccid;
2637 suspense_rec.expenditure_type := x_suspense_auto_exp_type;
2638 end if;
2639 end if;
2640 if x_return_status is null then
2641 if suspense_rec.project_id is not null then
2642 Validate_Project_details(X_Project_ID => suspense_rec.project_id,
2643 X_task_id => suspense_rec.task_id,
2644 X_award_id => suspense_rec.award_id,
2645 X_expenditure_type => suspense_rec.expenditure_type,
2646 X_exp_org_id => suspense_rec.expenditure_organization_id,
2647 X_gms_pa_install => p_gms_pa_install,
2648 X_Person_ID => p_person_id,
2649 X_Effective_date => p_effective_date,
2650 X_return_status => v_return_status,
2651 X_return_code => v_return_code);
2652
2653 if v_return_status <> FND_API.G_RET_STS_SUCCESS then
2654 if v_return_code = 'OTHER' then
2655 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2656 else
2657 raise SUSPENSE_AC_INVALID; /* should raise functional fatal error 2007521 */
2658 end if;
2659 end if;
2660 end if;
2661
2662 x_return_status := FND_API.G_RET_STS_SUCCESS;
2663 end if;
2664 x_suspense_account := suspense_rec.organization_account_id;
2665 EXCEPTION
2666 WHEN NO_PROFILE_EXISTS THEN
2667 fnd_message.set_name('PSP','PSP_NO_PROFILE_EXISTS');
2668 fnd_msg_pub.add;
2669 x_return_status := fnd_api.g_ret_sts_unexp_error;
2670
2671 WHEN NO_VAL_DATE_MATCHES THEN
2672 fnd_message.set_name('PSP','PSP_NO_VAL_DATE_MATCHES');
2673 fnd_message.set_token('ORG_NAME',p_organization_name);
2674 fnd_message.set_token('PAYROLL_DATE',p_effective_date);
2675 fnd_msg_pub.add;
2676 x_return_status := fnd_api.g_ret_sts_unexp_error;
2677
2678 WHEN NO_GLOBAL_ACCT_EXISTS THEN
2679 fnd_message.set_name('PSP','PSP_NO_GLOBAL_ACCT_EXISTS');
2680 fnd_message.set_token('ORG_NAME',p_organization_name);
2681 fnd_message.set_token('PAYROLL_DATE',p_effective_date);
2682 fnd_msg_pub.add;
2683 x_return_status := fnd_api.g_ret_sts_unexp_error;
2684
2685 WHEN SUSPENSE_AC_INVALID THEN
2686 /* Following added for bug 2514611 */
2687 OPEN employee_name_cur;
2688 FETCH employee_name_cur INTO l_employee_name;
2689 CLOSE employee_name_cur;
2690 fnd_message.set_name('PSP','PSP_LD_SUSPENSE_AC_INVALID');
2691 fnd_message.set_token('ORG_NAME',p_organization_name);
2692 fnd_message.set_token('PATC_STATUS',v_return_code);
2693 fnd_message.set_token('EMPLOYEE_NAME',l_employee_name); --Bug 2514611
2694 fnd_msg_pub.add;
2695 x_return_status := fnd_api.g_ret_sts_unexp_error;
2696
2697 WHEN OTHERS THEN
2698 if org_suspense_cur%isopen then
2699 close org_suspense_cur;
2700 end if;
2701 if org_suspense_cur2%isopen then
2702 close org_suspense_cur2;
2703 end if;
2704 fnd_msg_pub.add_exc_msg('PSP_PREGEN','GET_SUSPENSE_ACCOUNT');
2705 x_return_status := fnd_api.g_ret_sts_unexp_error;
2706 END;
2707 ---================================= STICK_SUSPENSE_ACCOUNT ===================
2708 /* Bug fix 2007521: Created this procedure.
2709 Sticks suspense account for Pre-Gen line with invalid POETA account. */
2710 Procedure stick_suspense_account( p_assignment_id in number,
2711 p_effective_date in date,
2712 p_gms_pa_install in varchar2,
2713 p_person_id in number,
2714 p_distribution_interface_id in number,
2715 p_suspense_reason_code in varchar2,
2716 p_business_group_id in number,
2717 p_set_of_books_id in number,
2718 p_return_status out NOCOPY varchar2) Is
2719 CURSOR org_name_cur IS
2720 SELECT hou.organization_id,
2721 hou.name
2722 FROM per_assignments_f paf,
2723 hr_organization_units hou
2724 WHERE paf.business_group_id = p_business_group_id
2725 AND paf.assignment_id = p_assignment_id
2726 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
2727 AND p_effective_date between hou.date_from and nvl(hou.date_to,p_effective_date)
2728 AND p_business_group_id = hou.business_group_id
2729 AND paf.organization_id = hou.organization_id;
2730
2731 v_organization_id number;
2732 v_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
2733 v_suspense_account number;
2734 v_return_status varchar2(1);
2735 assign_org_not_found exception;
2736 l_suspense_auto_glccid number;
2737 l_suspense_auto_exp_type varchar2(30);
2738 l_pre_gen_line_id number;
2739
2740 BEGIN
2741 open org_name_cur;
2742 fetch org_name_cur into v_organization_id, v_org_name;
2743 if org_name_cur%NOTFOUND then
2744 close org_name_cur;
2745 raise ASSIGN_ORG_NOT_FOUND;
2746 else
2747 l_pre_gen_line_id := p_distribution_interface_id;
2748 get_suspense_account(v_organization_id,
2749 v_org_name,
2750 p_effective_date,
2751 p_gms_pa_install,
2752 p_person_id,
2753 p_business_group_id,
2754 p_set_of_books_id,
2755 l_pre_gen_line_id,
2756 v_suspense_account,
2757 v_return_status,
2758 l_suspense_auto_glccid,
2759 l_suspense_auto_exp_type);
2760 if v_return_status <> FND_API.G_RET_STS_SUCCESS then
2761 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2762 end if;
2763
2764 update psp_distribution_interface
2765 set suspense_org_account_id = v_suspense_account,
2766 error_code = p_suspense_reason_code,
2767 status_code = 'V',
2768 suspense_auto_glccid = l_suspense_auto_glccid, --- added for 5080403
2769 suspense_auto_exp_type = l_suspense_auto_exp_type
2770 where distribution_interface_id = p_distribution_interface_id;
2771 close org_name_cur;
2772 end if;
2773 p_return_status := FND_API.G_RET_STS_SUCCESS;
2774 EXCEPTION
2775 When ASSIGN_ORG_NOT_FOUND then
2776 fnd_msg_pub.add_exc_msg('PSP_PREGEN','STICK_SUSPENSE_ACCOUNT-(Assign ORG)');
2777 p_return_status := fnd_api.g_ret_sts_unexp_error;
2778 When others then
2779 if org_name_cur%isopen then
2780 close org_name_cur;
2781 end if;
2782 fnd_msg_pub.add_exc_msg('PSP_PREGEN','STICK_SUSPENSE_ACCOUNT');
2783 p_return_status := fnd_api.g_ret_sts_unexp_error;
2784 END;
2785
2786 /* Bug fix 2985061: Created this procedure.
2787 If Cr_Dr_Flag in the psp_distribution_interface table is not in ('C','D') then throw Exception */
2788
2789 PROCEDURE VALIDATE_DR_CR_FLAG ( X_DR_CR_FLAG IN VARCHAR2,
2790 X_return_status OUT NOCOPY varchar2,
2791 X_return_code OUT NOCOPY varchar2) IS
2792 BEGIN
2793 if (X_DR_CR_FLAG = 'D') or (X_DR_CR_FLAG = 'C') then
2794 x_return_status := FND_API.G_RET_STS_SUCCESS;
2795 x_return_code := ' ';
2796 else
2797 x_return_status := FND_API.G_RET_STS_ERROR;
2798 x_return_code := 'INV_D_C';
2799 end if;
2800 EXCEPTION
2801 when OTHERS then
2802 fnd_msg_pub.add_exc_msg('PSP_PREGEN','VALIDATE_DR_CR_FLAG : Unexpected Error');
2803 x_return_status := FND_API.G_RET_STS_ERROR;
2804 x_return_code := 'OTHER';
2805 return;
2806 END VALIDATE_DR_CR_FLAG;
2807
2808 /* Bug fix 2985061: Created this procedure.
2809 If CODE_COMBINATION_ID is not in table gl_code_combinations then throw Exception */
2810 PROCEDURE VALIDATE_GL_CC_ID( X_CODE_COMBINATION_ID IN NUMBER,
2811 X_return_status OUT NOCOPY varchar2,
2812 X_return_code OUT NOCOPY varchar2) IS
2813 CURSOR check_code_combination_csr is
2814 select 1
2815 from gl_code_combinations
2816 where CODE_COMBINATION_ID = X_CODE_COMBINATION_ID;
2817
2818 l_code_combination_id number(15);
2819 BEGIN
2820 open check_code_combination_csr;
2821 fetch check_code_combination_csr into l_code_combination_id;
2822 if check_code_combination_csr%NOTFOUND then
2823 x_return_status := FND_API.G_RET_STS_ERROR;
2824 x_return_code := 'INV_GLC';
2825 close check_code_combination_csr;
2826 return;
2827 else
2828 x_return_status := FND_API.G_RET_STS_SUCCESS;
2829 x_return_code := ' ';
2830 end If;
2831 close check_code_combination_csr;
2832 Exception
2833 when no_data_found or too_many_rows then
2834 x_return_status := FND_API.G_RET_STS_ERROR;
2835 x_return_code := 'INV_GLC';
2836 close check_code_combination_csr;
2837 return;
2838 when OTHERS then
2839 fnd_msg_pub.add_exc_msg('PSP_PREGEN','VALIDATE_GL_CODE_COMBINATION_ID : Unexpected Error');
2840 x_return_status := FND_API.G_RET_STS_ERROR;
2841 x_return_code := 'OTHER';
2842 close check_code_combination_csr;
2843 return;
2844 End VALIDATE_GL_CC_ID;
2845
2846 END PSP_PREGEN; -- End of Package Body