1 PACKAGE BODY PA_BILLING_SETUP_UTILS AS
2 /* $Header: PABLSTUB.pls 120.3 2005/08/19 16:16:52 mwasowic noship $ */
3
4
5 -- API name : Validate_Retn_Inv_Format
6 -- Type : Utility procedure
7 -- Pre-reqs : None
8 -- Return Value : N/A
9 -- Prameters
10 -- p_retention_inv_format_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
11 -- p_retention_inv_format_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR
12 -- p_check_id IN VARCHAR2 REQUIRED DEFAULT 'A'
13 -- x_retention_inv_format_id OUT NUMBER REQUIRED
14 -- x_return_status OUT VARCHAR2 REQUIRED
15 -- x_error_msg_code OUT VARCHAR2 REQUIRED
16 --
17 -- History
18 --
19 -- 25-MAY-01 Majid Ansari -Created
20 --
21 --
22
23 PROCEDURE Validate_Retn_Inv_Format(
24 p_retention_inv_format_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
25 p_retention_inv_format_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
26 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
27 x_retention_inv_format_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
28 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
29 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
30 ) AS
31
32 l_current_id NUMBER := NULL;
33 l_num_ids NUMBER := 0;
34 l_id_found_flag VARCHAR(1) := 'N';
35
36 CURSOR c_ids
37 IS
38 SELECT invoice_format_id
39 FROM pa_invoice_formats
40 WHERE name = p_retention_inv_format_name;
41
42 BEGIN
43 IF p_retention_inv_format_id IS NOT NULL
44 THEN
45 IF p_check_id_flag = 'Y'
46 THEN
47 SELECT invoice_format_id
48 INTO x_retention_inv_format_id
49 FROM pa_invoice_formats
50 WHERE invoice_format_id = p_retention_inv_format_id;
51 ELSIF p_check_id_flag = 'N'
52 THEN
53 x_retention_inv_format_id := p_retention_inv_format_id;
54 ELSIF p_check_id_flag = 'A'
55 THEN
56 IF (p_retention_inv_format_name IS NULL) THEN
57 -- Return a null ID since the name is null.
58 x_retention_inv_format_id := NULL;
59 ELSE
60 -- Find the ID which matches the Name passed
61 OPEN c_ids;
62 LOOP
63 FETCH c_ids INTO l_current_id;
64 EXIT WHEN c_ids%NOTFOUND;
65 IF (l_current_id = p_retention_inv_format_id) THEN
66 l_id_found_flag := 'Y';
67 x_retention_inv_format_id := p_retention_inv_format_id;
68 END IF;
69 END LOOP;
70 l_num_ids := c_ids%ROWCOUNT;
71 CLOSE c_ids;
72 IF (l_num_ids = 0) THEN
73 -- No IDs for name
74 RAISE NO_DATA_FOUND;
75 ELSIF (l_num_ids = 1) THEN
76 -- Since there is only one ID for the name use it.
77 x_retention_inv_format_id := l_current_id;
78 ELSIF (l_id_found_flag = 'N') THEN
79 -- More than one ID for the name and none of the IDs matched
80 -- the ID passed in.
81 RAISE TOO_MANY_ROWS;
82 END IF;
83 END IF;
84 END IF;
85 ELSE
86 IF p_retention_inv_format_name IS NOT NULL
87 THEN
88 SELECT invoice_format_id
89 INTO x_retention_inv_format_id
90 FROM pa_invoice_formats
91 WHERE name = p_retention_inv_format_name;
92 ELSE
93 x_retention_inv_format_id := null;
94 END IF;
95 END IF;
96 x_return_status:= FND_API.G_RET_STS_SUCCESS;
97 EXCEPTION
98 WHEN no_data_found THEN
99 x_retention_inv_format_id := null;
100 x_return_status:= FND_API.G_RET_STS_ERROR;
101 x_error_msg_code:= 'PA_PRJ_INV_INVOI_FORMAT_ID';
102
103 /* ATG NOCOPY */
104 x_retention_inv_format_id := null;
105
106 WHEN too_many_rows THEN
107 x_retention_inv_format_id := null;
108 x_return_status:= FND_API.G_RET_STS_ERROR;
109 x_error_msg_code:= 'PA_PRJ_TOO_MANY_INV_FMT_ID';
110 /* ATG NOCOPY */
111 x_retention_inv_format_id := null;
112 WHEN OTHERS THEN
113 x_retention_inv_format_id := null;
114 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
115 /* ATG NOCOPY */
116 x_retention_inv_format_id := null;
117 RAISE;
118 END Validate_Retn_Inv_Format;
119
120 -- API name : Duplicate_credit_receivers
121 -- Type : Utility Function
122 -- Pre-reqs : None
123 -- Return Value : BOOLEAN
124 -- Prameters
125 -- p_project_id IN NUMBER REQUIRED
126 -- p_task_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
127 -- p_credit_type IN VARCHAR2 REQUIRED
128 -- p_person_id IN NUMBER REQUIRED
129 -- p_effective_from_date IN DATE REQUIRED
130 -- p_effective_to_date IN DATE REQUIRED ,
131 -- p_credit_receiver_id IN NUMBER REQUIRED,
132 -- x_return_status OUT VARCHAR2 REQUIRED
133 --
134 -- History
135 --
136 -- 25-MAY-01 Majid Ansari -Created
137 --
138 --
139
140 FUNCTION Duplicate_credit_receivers(
141 p_project_id IN NUMBER ,
142 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
143 p_credit_type IN VARCHAR2 ,
144 p_person_id IN NUMBER ,
145 p_effective_from_date IN DATE ,
146 p_effective_to_date IN DATE ,
147 p_credit_receiver_id IN NUMBER,
148 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
149 ) RETURN BOOLEAN AS
150 CURSOR cur_cr
151 IS
152 SELECT 'X'
153 FROM pa_credit_receivers
154 WHERE project_id = p_project_id
155 AND credit_type_code = p_credit_type
156 AND person_id = p_person_id
157 AND NVL( TASK_ID, -1 ) = DECODE( p_task_id, NULL, -1, p_task_id )
158 AND (p_effective_from_date BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, p_effective_from_date + 1)
159 OR p_effective_to_date BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, p_effective_to_date + 1)
160 OR START_DATE_ACTIVE BETWEEN p_effective_from_date AND NVL(p_effective_to_date, START_DATE_ACTIVE + 1))
161 AND ( p_credit_receiver_id IS NULL OR credit_receiver_id <> p_credit_receiver_id );
162 l_dummy_char VARCHAR2(1);
163 BEGIN
164 OPEN cur_cr;
165 FETCH cur_cr INTO l_dummy_char;
166 IF cur_cr%NOTFOUND
167 THEN
168 CLOSE cur_cr;
169 RETURN FALSE;
170 ELSE
171 CLOSE cur_cr;
172 RETURN TRUE;
173 END IF;
174 x_return_status:= FND_API.G_RET_STS_SUCCESS;
175 EXCEPTION
176 WHEN OTHERS THEN
177 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
178 RAISE;
179 END Duplicate_credit_receivers;
180
181
182 -- API name : Duplicate_billing_assignments
183 -- Type : Utility Function
184 -- Pre-reqs : None
185 -- Return Value : BOOLEAN
186 -- Prameters
187 -- p_project_id IN NUMBER REQUIRED
188 -- p_task_id IN NUMBER OPTIONAL DEFAULT=FND_API.G_MISS_NUM
189 -- p_billing_extension_id IN VARCHAR2 REQUIRED
190 -- p_billing_assignment_id IN NUMBER REQUIRED
191 -- p_active_flag IN VARCHAR2 REQUIRED
192 -- x_return_status OUT VARCHAR2 REQUIRED
193 --
194 -- History
195 --
196 -- 25-MAY-01 Majid Ansari -Created
197 --
198 --
199
200 FUNCTION Duplicate_billing_assignments(
201 p_project_id IN NUMBER ,
202 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
203 p_billing_extension_id IN NUMBER,
204 p_billing_assignment_id IN NUMBER,
205 p_active_flag IN VARCHAR2,
206 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
207 ) RETURN BOOLEAN AS
208 CURSOR cur_ba
209 IS
210 SELECT 'X'
211 FROM pa_billing_assignments_all
212 WHERE project_id = p_project_id
213 AND NVL( active_flag , 'N' ) = 'Y'
214 AND NVL( p_active_flag, 'N' ) = 'Y'
215 AND NVL( top_task_id, -1 ) = DECODE( p_task_id, NULL, -1, p_task_id )
216 AND billing_extension_id = p_billing_extension_id
217 AND ( p_billing_assignment_id IS NULL OR billing_assignment_id <> p_billing_assignment_id );
218
219
220 l_dummy_char VARCHAR2(1);
221 BEGIN
222
223 OPEN cur_ba;
224 FETCH cur_ba INTO l_dummy_char;
225 IF cur_ba%NOTFOUND
226 THEN
227 CLOSE cur_ba;
228 RETURN FALSE;
229 ELSE
230 CLOSE cur_ba;
231 RETURN TRUE;
232 END IF;
233 x_return_status:= FND_API.G_RET_STS_SUCCESS;
234 EXCEPTION
235 WHEN OTHERS THEN
236 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
237 RAISE;
238 END Duplicate_billing_assignments;
239
240
241 -- API name : VALIDATE_PERSON_ID_NAME
242 -- Type : Utility procedure
243 -- Pre-reqs : None
244 -- Return Value : N/A
245 -- Prameters
246 -- p_person_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
247 -- p_person_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR
248 -- p_check_id IN VARCHAR2 REQUIRED DEFAULT 'A'
249 -- x_person_id OUT NUMBER REQUIRED
250 -- x_return_status OUT VARCHAR2 REQUIRED
251 -- x_error_msg_code OUT VARCHAR2 REQUIRED
252 --
253 -- History
254 --
255 -- 25-MAY-01 Majid Ansari -Created
256 --
257 --
258
259 PROCEDURE VALIDATE_PERSON_ID_NAME(
260 p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
261 p_person_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
262 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
263 x_person_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
264 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
265 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
266 ) AS
267
268 l_current_id NUMBER := NULL;
269 l_num_ids NUMBER := 0;
270 l_id_found_flag VARCHAR(1) := 'N';
271
272 CURSOR c_ids
273 IS
274 SELECT person_id
275 FROM pa_employees
276 WHERE full_name = p_person_name;
277
278
279 BEGIN
280 IF p_person_id IS NOT NULL
281 THEN
282 IF p_check_id_flag = 'Y'
283 THEN
284 SELECT person_id
285 INTO x_person_id
286 FROM pa_employees
287 WHERE person_id = p_person_id;
288 ELSIF p_check_id_flag = 'N'
292 THEN
289 THEN
290 x_person_id := p_person_id;
291 ELSIF p_check_id_flag = 'A'
293 IF (p_person_name IS NULL) THEN
294 -- Return a null ID since the name is null.
295 x_person_id := NULL;
296 ELSE
297 -- Find the ID which matches the Name passed
298 OPEN c_ids;
299 LOOP
300 FETCH c_ids INTO l_current_id;
301 EXIT WHEN c_ids%NOTFOUND;
302 IF (l_current_id = p_person_id) THEN
303 l_id_found_flag := 'Y';
304 x_person_id := p_person_id;
305 END IF;
306 END LOOP;
307 l_num_ids := c_ids%ROWCOUNT;
308 CLOSE c_ids;
309 IF (l_num_ids = 0) THEN
310 -- No IDs for name
311 RAISE NO_DATA_FOUND;
312 ELSIF (l_num_ids = 1) THEN
313 -- Since there is only one ID for the name use it.
314 x_person_id := l_current_id;
315 ELSIF (l_id_found_flag = 'N') THEN
316 -- More than one ID for the name and none of the IDs matched
317 -- the ID passed in.
318 RAISE TOO_MANY_ROWS;
319 END IF;
320 END IF;
321 END IF;
322 ELSE
323 IF p_person_name IS NOT NULL
324 THEN
325 SELECT person_id
326 INTO x_person_id
327 FROM pa_employees
328 WHERE full_name = p_person_name;
329 ELSE
330 x_person_id := null;
331 END IF;
332 END IF;
333 x_return_status:= FND_API.G_RET_STS_SUCCESS;
334 EXCEPTION
335 WHEN no_data_found THEN
336 x_person_id := null;
337 x_return_status:= FND_API.G_RET_STS_ERROR;
338 x_error_msg_code:= 'PA_PRJ_INVALID_PERSON_ID';
339 /* ATG NOCOPY */
340 x_person_id := null;
341 WHEN too_many_rows THEN
342 x_person_id := null;
343 x_return_status:= FND_API.G_RET_STS_ERROR;
344 x_error_msg_code:= 'PA_PRJ_TOO_MANY_PERSON_ID';
345 /* ATG NOCOPY */
346 x_person_id := null;
347 WHEN OTHERS THEN
348 x_person_id := null;
349 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
350 /* ATG NOCOPY */
351 x_person_id := null;
352 RAISE;
353 END VALIDATE_PERSON_ID_NAME;
354
355
356 -- API name : Get_Next_Billing_Date
357 -- Type : Utility Function
358 -- Pre-reqs : None
359 -- Return Value : DATE
360 -- Prameters
361 -- p_project_id IN NUMBER
362 -- x_return_status OUT VARCHAR2 REQUIRED
363 --
364 -- History
365 --
366 -- 25-MAY-01 Majid Ansari -Created
367 --
368 --
369
370 FUNCTION Get_Next_Billing_Date(
371 p_project_id IN NUMBER,
372 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
373 ) RETURN DATE AS
374
375 CURSOR cur_projects_all
376 IS
377 SELECT start_date, billing_cycle_id, billing_offset
378 FROM pa_projects_all
379 WHERE project_id = p_project_id;
380
381 l_start_date DATE;
382 l_billing_cycle_id NUMBER;
383 l_billing_offset NUMBER;
384 l_next_billing_date DATE;
385 BEGIN
386
387 OPEN cur_projects_all;
388 FETCH cur_projects_all INTO l_start_date, l_billing_cycle_id, l_billing_offset;
389 CLOSE cur_projects_all;
390 l_next_billing_date := Pa_Billing_Cycles_pkg.Get_Next_billing_Date(
391 p_project_id,
392 l_start_date,
393 l_billing_cycle_id,
394 l_billing_offset,
395 null,
396 null );
397 RETURN l_next_billing_date;
398
399 x_return_status:= FND_API.G_RET_STS_SUCCESS;
400
401 EXCEPTION WHEN OTHERS THEN
402 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
403 RAISE;
404 END Get_Next_Billing_Date;
405
406
407 -- API name : REV_BILL_INF_REQ_CHECK
408 -- Type : Utility procedure
409 -- Pre-reqs : None
410 -- Return Value : N/A
411 -- Prameters
412 -- p_project_type_class_code IN VARCHAR2 REQUIRED
413 -- p_distribution_rule IN VARCHAR2 REQUIRED
414 -- p_billing_cycle_id IN NUMBER REQUIRED
415 -- p_first_bill_offset IN NUMBER REQUIRED
416 -- p_billing_job_group_id IN NUMBER REQUIRED
417 -- p_labor_id IN NUMBER REQUIRED
418 -- p_non_labor_id IN NUMBER REQUIRED
419 -- x_return_status OUT VARCHAR2 REQUIRED
420 -- x_error_msg_code OUT VARCHAR2 REQUIRED
421 --
422 -- History
423 --
424 -- 25-MAY-01 Majid Ansari -Created
425 --
426 --
427
428 PROCEDURE REV_BILL_INF_REQ_CHECK(
429 p_project_type_class_code IN VARCHAR2 ,
430 p_distribution_rule IN VARCHAR2 ,
431 p_billing_cycle_id IN NUMBER ,
432 p_first_bill_offset IN NUMBER ,
433 p_billing_job_group_id IN NUMBER ,
434 p_labor_id IN NUMBER ,
435 p_non_labor_id IN NUMBER ,
436 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
437 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
438 ) AS
439 BEGIN
440 x_return_status:= FND_API.G_RET_STS_SUCCESS;
441 IF p_project_type_class_code = 'CONTRACT'
442 THEN
443 IF p_distribution_rule IS NULL OR p_distribution_rule = FND_API.G_MISS_CHAR
444 THEN
445 x_error_msg_code := 'PA_PRJ_DIST_RULE_REQ';
446 x_return_status:= FND_API.G_RET_STS_ERROR;
447 RAISE FND_API.G_EXC_ERROR;
448 END IF;
449
450 IF p_billing_cycle_id IS NULL OR p_billing_cycle_id = FND_API.G_MISS_NUM
451 THEN
452 x_error_msg_code := 'PA_PRJ_BILLING_CYCLE_REQ';
453 x_return_status:= FND_API.G_RET_STS_ERROR;
454 RAISE FND_API.G_EXC_ERROR;
455 END IF;
456
457 IF p_first_bill_offset IS NULL OR p_first_bill_offset = FND_API.G_MISS_NUM
458 THEN
459 x_error_msg_code := 'PA_PRJ_BILL_OFFSET_REQ';
460 x_return_status:= FND_API.G_RET_STS_ERROR;
461 RAISE FND_API.G_EXC_ERROR;
462 END IF;
463
464 IF p_billing_job_group_id IS NULL OR p_billing_job_group_id = FND_API.G_MISS_NUM
465 THEN
466 x_error_msg_code := 'PA_PRJ_BILL_JOB_GROUP_REQ';
467 x_return_status:= FND_API.G_RET_STS_ERROR;
468 RAISE FND_API.G_EXC_ERROR;
469 END IF;
470
471 IF p_labor_id IS NULL OR p_labor_id = FND_API.G_MISS_NUM
472 THEN
473 x_error_msg_code := 'PA_PRJ_LBR_INV_FORMAT_REQ';
474 x_return_status:= FND_API.G_RET_STS_ERROR;
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477
478 IF p_non_labor_id IS NULL OR p_non_labor_id = FND_API.G_MISS_NUM
479 THEN
480 x_error_msg_code := 'PA_PRJ_NL_INV_FORMAT_REQ';
481 x_return_status:= FND_API.G_RET_STS_ERROR;
482 RAISE FND_API.G_EXC_ERROR;
483 END IF;
484
485 END IF;
486 EXCEPTION
487 WHEN FND_API.G_EXC_ERROR THEN
488 x_return_status:= FND_API.G_RET_STS_ERROR;
489 END REV_BILL_INF_REQ_CHECK;
490
491
492 -- API name : BILL_XTENSION_REQ_CHECK
493 -- Type : Utility procedure
494 -- Pre-reqs : None
495 -- Return Value : N/A
496 -- Prameters
497 -- p_billing_extension_id IN NUMBER REQUIRED
498 -- x_return_status OUT VARCHAR2 REQUIRED
499 -- x_error_msg_code OUT VARCHAR2 REQUIRED
500 --
501 -- History
502 --
503 -- 25-MAY-01 Majid Ansari -Created
504 --
505 --
506
507 PROCEDURE BILL_XTENSION_REQ_CHECK(
508 p_billing_extension_id IN NUMBER ,
509 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
510 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
511 ) IS
512 BEGIN
513 x_return_status:= FND_API.G_RET_STS_SUCCESS;
514 IF p_billing_extension_id IS NULL OR p_billing_extension_id = FND_API.G_MISS_NUM
515 THEN
516 x_error_msg_code := 'PA_PRJ_BILL_XTENSION_REQ';
517 x_return_status:= FND_API.G_RET_STS_ERROR;
518 RAISE FND_API.G_EXC_ERROR;
519 END IF;
520 EXCEPTION
521 WHEN FND_API.G_EXC_ERROR THEN
522 x_return_status:= FND_API.G_RET_STS_ERROR;
523 END BILL_XTENSION_REQ_CHECK;
524
525
526 -- API name : VALIDATE_EMP_NO_TO_ID
527 -- Type : Utility procedure
528 -- Pre-reqs : None
529 -- Return Value : N/A
530 -- Prameters
531 -- p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
532 -- p_emp_number IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
533 -- p_check_id IN VARCHAR2 REQUIRED DEFAULT 'A'
534 -- x_person_id OUT NUMBER ,
535 -- x_return_status OUT VARCHAR2 REQUIRED
536 -- x_error_msg_code OUT VARCHAR2 REQUIRED
537 --
538 -- History
539 --
540 -- 25-MAY-01 Majid Ansari -Created
541 --
542 --
543
544 PROCEDURE VALIDATE_EMP_NO_TO_ID(
545 p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
546 p_emp_number IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
547 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
548 x_person_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
549 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
550 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
551 ) IS
552 l_current_id NUMBER := NULL;
553 l_num_ids NUMBER := 0;
554 l_id_found_flag VARCHAR(1) := 'N';
555
556 CURSOR c_ids
557 IS
558 SELECT person_id
559 FROM pa_employees
560 WHERE employee_number = p_emp_number;
561
562 BEGIN
563 IF p_person_id IS NOT NULL
564 THEN
565 IF p_check_id_flag = 'Y'
566 THEN
567 SELECT person_id
568 INTO x_person_id
569 FROM pa_employees
570 WHERE person_id = p_person_id;
571 ELSIF p_check_id_flag = 'N'
572 THEN
573 x_person_id := p_person_id;
574 ELSIF p_check_id_flag = 'A'
575 THEN
576 IF (p_emp_number IS NULL) THEN
577 -- Return a null ID since the name is null.
578 x_person_id := NULL;
579 ELSE
580 -- Find the ID which matches the Name passed
581 OPEN c_ids;
582 LOOP
583 FETCH c_ids INTO l_current_id;
584 EXIT WHEN c_ids%NOTFOUND;
585 IF (l_current_id = p_person_id) THEN
586 l_id_found_flag := 'Y';
587 x_person_id := p_person_id;
588 END IF;
589 END LOOP;
590 l_num_ids := c_ids%ROWCOUNT;
591 CLOSE c_ids;
592 IF (l_num_ids = 0) THEN
593 -- No IDs for name
594 RAISE NO_DATA_FOUND;
595 ELSIF (l_num_ids = 1) THEN
596 -- Since there is only one ID for the name use it.
597 x_person_id := l_current_id;
598 ELSIF (l_id_found_flag = 'N') THEN
599 -- More than one ID for the name and none of the IDs matched
600 -- the ID passed in.
601 RAISE TOO_MANY_ROWS;
602 END IF;
603 END IF;
604 END IF;
605 ELSE
606 IF p_emp_number IS NOT NULL
607 THEN
608 SELECT person_id
609 INTO x_person_id
610 FROM pa_employees
611 WHERE employee_number = p_emp_number;
612 ELSE
613 x_person_id := null;
614 END IF;
615 END IF;
616 x_return_status:= FND_API.G_RET_STS_SUCCESS;
617 EXCEPTION
618 WHEN no_data_found THEN
619 x_person_id := null;
620 x_return_status:= FND_API.G_RET_STS_ERROR;
621 x_error_msg_code:= 'PA_PRJ_INVALID_PERSON_ID';
622 /* ATG NOCOPY */
623 x_person_id := null;
624 WHEN too_many_rows THEN
625 x_person_id := null;
626 x_return_status:= FND_API.G_RET_STS_ERROR;
627 x_error_msg_code:= 'PA_PRJ_TOO_MANY_PERSON_ID';
628 /* ATG NOCOPY */
629 x_person_id := null;
630 WHEN OTHERS THEN
631 x_person_id := null;
632 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
633 x_error_msg_code := 'Error in VALIDATE_EMP_NO_TO_ID';
634 /* ATG NOCOPY */
635 x_person_id := null;
636 RAISE;
637 END VALIDATE_EMP_NO_TO_ID;
638
639
640 -- API name : VALIDATE_EMP_NO_NAME
641 -- Type : Utility procedure
642 -- Pre-reqs : None
643 -- Return Value : N/A
644 -- Prameters
645 -- p_person_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM,
646 -- p_person_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR,
647 -- p_emp_number IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR,
648 -- p_check_id IN VARCHAR2 REQUIRED DEFAULT 'A'
649 -- x_person_id OUT NUMBER
650 -- x_return_status OUT VARCHAR2 REQUIRED
651 -- x_error_msg_code OUT VARCHAR2 REQUIRED
652 --
653 -- History
654 --
655 -- 25-MAY-01 Majid Ansari -Created
656 --
657 --
658
659 PROCEDURE VALIDATE_EMP_NO_NAME(
660 p_person_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
661 p_person_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
662 p_emp_number IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
663 p_check_id IN VARCHAR2 DEFAULT 'A',
664 x_person_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
665 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
666 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
667 ) IS
668 l_person_id NUMBER;
669 l_error_msg_code VARCHAR2(250);
670 l_return_status VARCHAR2(1);
671 l_person_id2 NUMBER;
672
673
674 BEGIN
675 x_return_status:= FND_API.G_RET_STS_SUCCESS;
676 IF ( p_person_name IS NOT NULL AND p_person_name <> FND_API.G_MISS_CHAR ) AND
677 ( p_emp_number IS NOT NULL AND p_emp_number <> FND_API.G_MISS_CHAR )
678 THEN
679 PA_BILLING_SETUP_UTILS.Validate_person_id_name
680 ( p_person_id => p_person_id
681 ,p_person_name => p_person_name
682 ,p_check_id_flag => 'A'
683 ,x_person_id => l_person_id
684 ,x_return_status => l_return_status
685 ,x_error_msg_code => l_error_msg_code);
686
687
688 l_person_id2 := l_person_id;
689
690 PA_BILLING_SETUP_UTILS.VALIDATE_EMP_NO_TO_ID
691 ( p_person_id => p_person_id
692 ,p_emp_number => p_emp_number
693 ,p_check_id_flag => 'A'
694 ,x_person_id => l_person_id
695 ,x_return_status => l_return_status
696 ,x_error_msg_code => l_error_msg_code);
697
698 IF l_person_id2 <> l_person_id
699 THEN
700 x_error_msg_code := 'PA_PRJ_INV_EMP_NO_NAME';
701 x_return_status:= FND_API.G_RET_STS_ERROR;
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704
705 ELSIF ( p_person_id IS NOT NULL AND p_person_id <> FND_API.G_MISS_NUM ) OR
706 ( p_person_name IS NOT NULL AND p_person_name <> FND_API.G_MISS_CHAR )
707 THEN
708 PA_BILLING_SETUP_UTILS.Validate_person_id_name
709 ( p_person_id => p_person_id
710 ,p_person_name => p_person_name
711 ,p_check_id_flag => 'A'
712 ,x_person_id => l_person_id
713 ,x_return_status => l_return_status
714 ,x_error_msg_code => l_error_msg_code);
715 ELSIF ( p_person_id IS NOT NULL AND p_person_id <> FND_API.G_MISS_NUM ) OR
716 ( p_emp_number IS NOT NULL AND p_emp_number <> FND_API.G_MISS_CHAR )
717 THEN
718 PA_BILLING_SETUP_UTILS.VALIDATE_EMP_NO_TO_ID
719 ( p_person_id => p_person_id
723 ,x_return_status => l_return_status
720 ,p_emp_number => p_emp_number
721 ,p_check_id_flag => 'A'
722 ,x_person_id => l_person_id
724 ,x_error_msg_code => l_error_msg_code);
725
726 END IF;
727 x_person_id := l_person_id;
728 EXCEPTION
729 WHEN FND_API.G_EXC_ERROR THEN
730 x_return_status:= FND_API.G_RET_STS_ERROR;
731 x_error_msg_code := 'Error in VALIDATE_EMP_NO_NAME';
732 /* ATG NOCOPY */
733 x_person_id := null;
734 WHEN OTHERS THEN
735 x_error_msg_code := 'Error in VALIDATE_EMP_NO_NAME';
736 /* ATG NOCOPY */
737 x_person_id := null;
738 END VALIDATE_EMP_NO_NAME;
739
740
741 -- API name : CREDIT_REC_REQ_CHECK
742 -- Type : Utility procedure
743 -- Pre-reqs : None
744 -- Return Value : N/A
745 -- Prameters
746 -- p_credit_type IN VARCHAR2 REQUIRED
747 -- p_person_id IN NUMBER REQUIRED
748 -- p_transfer_to_AR IN VARCHAR2 REQUIRED
749 -- p_effective_from_date IN DATE REQUIRED
750 -- x_return_status OUT VARCHAR2 REQUIRED
751 -- x_error_msg_code OUT VARCHAR2 REQUIRED
752 --
753 -- History
754 --
755 -- 25-MAY-01 Majid Ansari -Created
756 --
757 --
758
759 PROCEDURE CREDIT_REC_REQ_CHECK(
760 p_credit_type IN VARCHAR2 ,
761 p_person_id IN NUMBER,
762 p_transfer_to_AR IN VARCHAR2,
763 p_effective_from_date IN DATE,
764 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
765 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
766 ) AS
767 BEGIN
768 x_return_status:= FND_API.G_RET_STS_SUCCESS;
769 IF p_credit_type IS NULL OR p_credit_type = FND_API.G_MISS_CHAR
770 THEN
771 x_error_msg_code := 'PA_PRJ_CREDIT_TYPE_REQ';
772 x_return_status:= FND_API.G_RET_STS_ERROR;
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775
776 IF p_person_id IS NULL OR p_person_id = FND_API.G_MISS_NUM
777 THEN
778 x_error_msg_code := 'PA_PRJ_EMP_NO_NAME_REQ';
779 x_return_status:= FND_API.G_RET_STS_ERROR;
780 RAISE FND_API.G_EXC_ERROR;
781 END IF;
782
783 IF p_transfer_to_AR IS NULL OR p_transfer_to_AR = FND_API.G_MISS_CHAR
784 THEN
785 x_error_msg_code := 'PA_PRJ_XFER_TO_AR_FLG_REQ';
786 x_return_status:= FND_API.G_RET_STS_ERROR;
787 RAISE FND_API.G_EXC_ERROR;
788 END IF;
789
790 IF p_effective_from_date IS NULL OR p_effective_from_date = FND_API.G_MISS_DATE
791 THEN
792 x_error_msg_code := 'PRJ_PA_ST_DT_REQ';
793 x_return_status:= FND_API.G_RET_STS_ERROR;
794 RAISE FND_API.G_EXC_ERROR;
795 END IF;
796
797 EXCEPTION
798 WHEN FND_API.G_EXC_ERROR THEN
799 x_return_status:= FND_API.G_RET_STS_ERROR;
800 END CREDIT_REC_REQ_CHECK;
801
802 -- API name : GET_SALES_CREDIT_FLAG
803 -- Type : Utility Function
804 -- Pre-reqs : None
805 -- Return Value : VARCHAR2( 'Y', 'N' )
806 -- Prameters
807 -- x_return_status OUT VARCHAR2 REQUIRED
808 --
809 -- History
810 --
811 -- 21-JUN-01 Majid Ansari -Created
812 --
813 --
814
815 FUNCTION GET_SALES_CREDIT_FLAG(
816 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
817 ) RETURN VARCHAR2 AS
818
819 CURSOR sales_crdt_flag
820 IS
821 SELECT NVL(ALLOW_SALES_CREDIT_FLAG, 'N') ALLOW_SALES_CREDIT_FLAG
822 FROM RA_BATCH_SOURCES R,
823 PA_IMPLEMENTATIONS S
824 WHERE R.BATCH_SOURCE_ID = S.INVOICE_BATCH_SOURCE_ID ;
825
826 l_ALLOW_SALES_CREDIT_FLAG VARCHAR2(1);
827 BEGIN
828 x_return_status:= FND_API.G_RET_STS_SUCCESS;
829
830 OPEN sales_crdt_flag;
831 FETCH sales_crdt_flag INTO l_ALLOW_SALES_CREDIT_FLAG;
832 CLOSE sales_crdt_flag;
833
834 RETURN NVL( l_ALLOW_SALES_CREDIT_FLAG, 'N' );
835
836 EXCEPTION
837 WHEN OTHERS THEN
838 x_return_status:= FND_API.G_RET_STS_ERROR;
839 END GET_SALES_CREDIT_FLAG;
840
841
842
843 END PA_BILLING_SETUP_UTILS;
[Home] [Help]