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