1 PACKAGE BODY PA_BILLING_SCHEDULE_UTILS AS
2 /* $Header: PABLINUB.pls 120.3 2005/08/19 16:16:26 mwasowic noship $ */
3
4 -- API name : Emp_bill_rate_sch_name_To_Id
5 -- Type : Public procedure
6 -- Pre-reqs : None
7 -- Return Value : N/A
8 -- Prameters
9 -- p_emp_bill_rate_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
10 -- p_emp_bill_rate_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR
11 -- p_check_id_flag IN VARCHAR2 REQUIRED DEFAULT 'A'
12 -- x_ emp_bill_rate_id OUT NUMBER REQUIRED
13 -- x_return_status OUT VARCHAR2 REQUIRED
14 -- x_error_msg_code OUT VARCHAR2 REQUIRED
15 --
16 -- History
17 --
18 -- 21-MAY-01 Majid Ansari -Created
19 --
20 --
21
22 PROCEDURE Emp_bill_rate_sch_name_To_Id(
23 p_emp_bill_rate_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
24 p_emp_bill_rate_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
25 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
26 x_emp_bill_rate_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
27 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
28 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
29 ) IS
30
31 l_current_id NUMBER := NULL;
32 l_num_ids NUMBER := 0;
33 l_id_found_flag VARCHAR(1) := 'N';
34
35 CURSOR c_ids
36 IS
37 SELECT brs.bill_rate_sch_id
38 FROM pa_std_bill_rate_schedules brs, pa_lookups l
39 WHERE brs.schedule_type = 'EMPLOYEE'
40 AND brs.std_bill_rate_schedule = p_emp_bill_rate_name
41 AND l.lookup_type = 'SCHEDULE TYPE'
42 AND l.lookup_code (+) = brs.schedule_type;
43
44 BEGIN
45
46 IF p_emp_bill_rate_id IS NOT NULL
47 THEN
48 IF p_check_id_flag = 'Y'
49 THEN
50 SELECT brs.bill_rate_sch_id
51 INTO x_emp_bill_rate_id
52 FROM pa_std_bill_rate_schedules brs, pa_lookups l
53 WHERE brs.schedule_type = 'EMPLOYEE'
54 AND brs.bill_rate_sch_id = p_emp_bill_rate_id
55 AND l.lookup_type = 'SCHEDULE TYPE'
56 AND l.lookup_code (+) = brs.schedule_type;
57 ELSIF p_check_id_flag = 'N'
58 THEN
59 x_emp_bill_rate_id := p_emp_bill_rate_id;
60 ELSIF p_check_id_flag = 'A'
61 THEN
62 IF (p_emp_bill_rate_name IS NULL) THEN
63 -- Return a null ID since the name is null.
64 x_emp_bill_rate_id := NULL;
65 ELSE
66 -- Find the ID which matches the Name passed
67 OPEN c_ids;
68 LOOP
69 FETCH c_ids INTO l_current_id;
70 EXIT WHEN c_ids%NOTFOUND;
71 IF (l_current_id = p_emp_bill_rate_id) THEN
72 l_id_found_flag := 'Y';
73 x_emp_bill_rate_id := p_emp_bill_rate_id;
74 END IF;
75 END LOOP;
76 l_num_ids := c_ids%ROWCOUNT;
77 CLOSE c_ids;
78 IF (l_num_ids = 0) THEN
79 -- No IDs for name
80 RAISE NO_DATA_FOUND;
81 ELSIF (l_num_ids = 1) THEN
82 -- Since there is only one ID for the name use it.
83 x_emp_bill_rate_id := l_current_id;
84 ELSIF (l_id_found_flag = 'N') THEN
85 -- More than one ID for the name and none of the IDs matched
86 -- the ID passed in.
87 RAISE TOO_MANY_ROWS;
88 END IF;
89 END IF;
90 END IF;
91 ELSE
92 IF p_emp_bill_rate_name IS NOT NULL
93 THEN
94 SELECT brs.bill_rate_sch_id
95 INTO x_emp_bill_rate_id
96 FROM pa_std_bill_rate_schedules brs, pa_lookups l
97 WHERE brs.schedule_type = 'EMPLOYEE'
98 AND brs.std_bill_rate_schedule = p_emp_bill_rate_name
99 AND l.lookup_type = 'SCHEDULE TYPE'
100 AND l.lookup_code (+) = brs.schedule_type;
101 ELSE
102 x_emp_bill_rate_id := null;
103 END IF;
104 END IF;
105 x_return_status:= FND_API.G_RET_STS_SUCCESS;
106 EXCEPTION
107 WHEN no_data_found THEN
108 x_emp_bill_rate_id := null;
109 x_return_status:= FND_API.G_RET_STS_ERROR;
110 x_error_msg_code:= 'PA_PRJ_INV_EMP_BR_SCH_ID';
111 WHEN too_many_rows THEN
112 x_emp_bill_rate_id := null;
113 x_return_status:= FND_API.G_RET_STS_ERROR;
114 x_error_msg_code:= 'PA_PRJ_TOO_MANY_EMP_BR';
115 WHEN OTHERS THEN
116 x_emp_bill_rate_id := null;
117 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
118 RAISE;
119 END Emp_bill_rate_sch_name_To_Id;
120
121 -- API name : Job_bill_rate_sch_name_To_Id
122 -- Type : Public procedure
123 -- Pre-reqs : None
124 -- Return Value : N/A
125 -- Prameters
126 -- p_job_bill_rate_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
127 -- p_job_bill_rate_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR
128 -- p_check_id_flag IN VARCHAR2 REQUIRED DEFAULT 'A'
129 -- p_job_group_id IN NUMBER REQUIRED
130 -- x_job_bill_rate_id OUT NUMBER REQUIRED
131 -- x_return_status OUT VARCHAR2 REQUIRED
132 -- x_error_msg_code OUT VARCHAR2 REQUIRED
133 --
134 -- History
135 --
136 -- 21-MAY-01 Majid Ansari -Created
137 --
138 --
139
140 PROCEDURE job_bill_rate_sch_name_To_Id(
141 p_job_bill_rate_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
142 p_job_bill_rate_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
143 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
144 p_job_group_id IN NUMBER ,
145 x_job_bill_rate_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
146 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
147 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
148 ) IS
149
150 l_current_id NUMBER := NULL;
151 l_num_ids NUMBER := 0;
152 l_id_found_flag VARCHAR(1) := 'N';
153
154 CURSOR c_ids
155 IS
156 SELECT brs.bill_rate_sch_id
157 FROM pa_std_bill_rate_schedules brs, pa_lookups l
158 WHERE brs.schedule_type = 'JOB'
159 AND brs.std_bill_rate_schedule = p_job_bill_rate_name
160 AND brs.job_group_id = p_job_group_id
161 AND l.lookup_type = 'SCHEDULE TYPE'
162 AND l.lookup_code (+) = brs.schedule_type;
163
164 BEGIN
165
166 IF p_job_bill_rate_id IS NOT NULL
167 THEN
168 IF p_check_id_flag = 'Y'
169 THEN
170 SELECT brs.bill_rate_sch_id
171 INTO x_job_bill_rate_id
172 FROM pa_std_bill_rate_schedules brs, pa_lookups l
173 WHERE brs.schedule_type = 'JOB'
174 AND brs.bill_rate_sch_id = p_job_bill_rate_id
175 AND brs.job_group_id = p_job_group_id
176 AND l.lookup_type = 'SCHEDULE TYPE'
177 AND l.lookup_code (+) = brs.schedule_type;
178 ELSIF p_check_id_flag = 'N'
179 THEN
180 x_job_bill_rate_id := p_job_bill_rate_id;
181 ELSIF p_check_id_flag = 'A'
182 THEN
183 IF (p_job_bill_rate_name IS NULL) THEN
184 -- Return a null ID since the name is null.
185 x_job_bill_rate_id := NULL;
186 ELSE
187 -- Find the ID which matches the Name passed
188 OPEN c_ids;
189 LOOP
190 FETCH c_ids INTO l_current_id;
191 EXIT WHEN c_ids%NOTFOUND;
192 IF (l_current_id = p_job_bill_rate_id) THEN
193 l_id_found_flag := 'Y';
194 x_job_bill_rate_id := p_job_bill_rate_id;
195 END IF;
196 END LOOP;
197 l_num_ids := c_ids%ROWCOUNT;
198 CLOSE c_ids;
199 IF (l_num_ids = 0) THEN
200 -- No IDs for name
201 RAISE NO_DATA_FOUND;
202 ELSIF (l_num_ids = 1) THEN
206 -- More than one ID for the name and none of the IDs matched
203 -- Since there is only one ID for the name use it.
204 x_job_bill_rate_id := l_current_id;
205 ELSIF (l_id_found_flag = 'N') THEN
207 -- the ID passed in.
208 RAISE TOO_MANY_ROWS;
209 END IF;
210 END IF;
211 END IF;
212 ELSE
213 IF p_job_bill_rate_name IS NOT NULL
214 THEN
215 SELECT brs.bill_rate_sch_id
216 INTO x_job_bill_rate_id
217 FROM pa_std_bill_rate_schedules brs, pa_lookups l
218 WHERE brs.schedule_type = 'JOB'
219 AND brs.std_bill_rate_schedule = p_job_bill_rate_name
220 AND brs.job_group_id = p_job_group_id
221 AND l.lookup_type = 'SCHEDULE TYPE'
222 AND l.lookup_code (+) = brs.schedule_type;
223 ELSE
224 x_job_bill_rate_id := null;
225 END IF;
226 END IF;
227 x_return_status:= FND_API.G_RET_STS_SUCCESS;
228 EXCEPTION
229 WHEN no_data_found THEN
230 x_job_bill_rate_id := null;
231 x_return_status:= FND_API.G_RET_STS_ERROR;
232 x_error_msg_code:= 'PA_PRJ_INV_JOB_BR_SCH_ID';
233 WHEN too_many_rows THEN
234 x_job_bill_rate_id := null;
235 x_return_status:= FND_API.G_RET_STS_ERROR;
236 x_error_msg_code:= 'PA_PRJ_TOO_MANY_JOB_BR';
237 WHEN OTHERS THEN
238 x_job_bill_rate_id := null;
239 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
240 RAISE;
241 END Job_bill_rate_sch_name_To_Id;
242
243
244 -- API name : Rev_Sch_Name_To_Id
245 -- Type : Public procedure
246 -- Pre-reqs : None
247 -- Return Value : N/A
248 -- Prameters
249 -- p_rev_sch_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
250 -- p_rev_sch_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR
251 -- p_check_id_flag IN VARCHAR2 REQUIRED DEFAULT 'A'
252 -- x_rev_sch_id OUT NUMBER REQUIRED
253 -- x_return_status OUT VARCHAR2 REQUIRED
254 -- x_error_msg_code OUT VARCHAR2 REQUIRED
255 --
256 -- History
257 --
258 -- 21-MAY-01 Majid Ansari -Created
259 --
260 --
261
262 PROCEDURE Rev_Sch_Name_To_Id(
263 p_rev_sch_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
264 p_rev_sch_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
265 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
266 x_rev_sch_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
267 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
268 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
269 ) IS
270
271 l_current_id NUMBER := NULL;
272 l_num_ids NUMBER := 0;
273 l_id_found_flag VARCHAR(1) := 'N';
274
275 CURSOR c_ids
276 IS
277 SELECT irs.ind_rate_sch_id
278 FROM pa_lookups l, pa_ind_rate_schedules irs
279 WHERE l.lookup_type = 'IND RATE SCHEDULE TYPE'
280 AND l.lookup_code = irs.ind_rate_schedule_type
281 AND irs.project_id IS NULL
282 AND trunc(sysdate) BETWEEN irs.start_date_active
283 AND nvl(irs.end_date_active,trunc(sysdate+1))
284 AND irs.ind_rate_sch_name = p_rev_sch_name ;
285
286 BEGIN
287 IF p_rev_sch_id IS NOT NULL
288 THEN
289 IF p_check_id_flag = 'Y'
290 THEN
291 SELECT irs.ind_rate_sch_id
292 INTO x_rev_sch_id
293 FROM pa_lookups l, pa_ind_rate_schedules irs
294 WHERE l.lookup_type = 'IND RATE SCHEDULE TYPE'
295 AND l.lookup_code = irs.ind_rate_schedule_type
296 AND irs.project_id IS NULL
297 AND trunc(sysdate) BETWEEN irs.start_date_active
298 AND nvl(irs.end_date_active,trunc(sysdate+1))
299 AND irs.ind_rate_sch_id = p_rev_sch_id;
300
301 ELSIF p_check_id_flag = 'N'
302 THEN
303 x_rev_sch_id := p_rev_sch_id;
304 ELSIF p_check_id_flag = 'A'
305 THEN
306 IF (p_rev_sch_name IS NULL) THEN
307 -- Return a null ID since the name is null.
308 x_rev_sch_id := NULL;
309 ELSE
310 -- Find the ID which matches the Name passed
311 OPEN c_ids;
312 LOOP
313 FETCH c_ids INTO l_current_id;
314 EXIT WHEN c_ids%NOTFOUND;
315 IF (l_current_id = p_rev_sch_id) THEN
316 l_id_found_flag := 'Y';
317 x_rev_sch_id := p_rev_sch_id;
318 END IF;
319 END LOOP;
320 l_num_ids := c_ids%ROWCOUNT;
321 CLOSE c_ids;
322 IF (l_num_ids = 0) THEN
323 -- No IDs for name
324 RAISE NO_DATA_FOUND;
328 ELSIF (l_id_found_flag = 'N') THEN
325 ELSIF (l_num_ids = 1) THEN
326 -- Since there is only one ID for the name use it.
327 x_rev_sch_id := l_current_id;
329 -- More than one ID for the name and none of the IDs matched
330 -- the ID passed in.
331 RAISE TOO_MANY_ROWS;
332 END IF;
333 END IF;
334 END IF;
335 ELSE
336 IF p_rev_sch_name IS NOT NULL
337 THEN
338 SELECT irs.ind_rate_sch_id
339 INTO x_rev_sch_id
340 FROM pa_lookups l, pa_ind_rate_schedules irs
341 WHERE l.lookup_type = 'IND RATE SCHEDULE TYPE'
342 AND l.lookup_code = irs.ind_rate_schedule_type
343 AND irs.project_id IS NULL
344 AND trunc(sysdate) BETWEEN irs.start_date_active
345 AND nvl(irs.end_date_active,trunc(sysdate+1))
346 AND irs.ind_rate_sch_name = p_rev_sch_name ;
347 ELSE
348 x_rev_sch_id := null;
349 END IF;
350 END IF;
351 x_return_status:= FND_API.G_RET_STS_SUCCESS;
352 EXCEPTION
353 WHEN no_data_found THEN
354 x_rev_sch_id := null;
355 x_return_status:= FND_API.G_RET_STS_ERROR;
356 x_error_msg_code:= 'PA_PRJ_REV_SCHEDULE_ID';
357 WHEN too_many_rows THEN
358 x_rev_sch_id := null;
359 x_return_status:= FND_API.G_RET_STS_ERROR;
360 x_error_msg_code:= 'PA_PRJ_TOO_MANY_REV_SCH';
361 WHEN OTHERS THEN
362 x_rev_sch_id := null;
363 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
364 RAISE;
365 END Rev_Sch_Name_To_Id;
366
367
368 -- API name : Inv_Sch_Name_To_Id
369 -- Type : Public procedure
370 -- Pre-reqs : None
371 -- Return Value : N/A
372 -- Prameters
373 -- p_inv_sch_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
374 -- p_inv_sch_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR
375 -- p_check_id_flag IN VARCHAR2 REQUIRED DEFAULT 'A'
376 -- x_inv_sch_id OUT NUMBER REQUIRED
377 -- x_return_status OUT VARCHAR2 REQUIRED
378 -- x_error_msg_code OUT VARCHAR2 REQUIRED
379 --
380 -- History
381 --
382 -- 21-MAY-01 Majid Ansari -Created
383 --
384 --
385
386 PROCEDURE Inv_Sch_Name_To_Id(
387 p_Inv_sch_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
388 p_Inv_sch_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
389 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
390 x_Inv_sch_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
391 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
392 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
393 ) IS
394
395 l_current_id NUMBER := NULL;
396 l_num_ids NUMBER := 0;
397 l_id_found_flag VARCHAR(1) := 'N';
398
399 CURSOR c_ids
400 IS
401 SELECT irs.ind_rate_sch_id
402 FROM pa_lookups l, pa_ind_rate_schedules irs
403 WHERE l.lookup_type = 'IND RATE SCHEDULE TYPE'
404 AND l.lookup_code = irs.ind_rate_schedule_type
405 AND irs.project_id IS NULL
406 AND trunc(sysdate) BETWEEN irs.start_date_active
407 AND nvl(irs.end_date_active,trunc(sysdate+1))
408 AND irs.ind_rate_sch_name = p_inv_sch_name ;
409
410 BEGIN
411 IF p_inv_sch_id IS NOT NULL
412 THEN
413 IF p_check_id_flag = 'Y'
414 THEN
415 SELECT irs.ind_rate_sch_id
416 INTO x_inv_sch_id
417 FROM pa_lookups l, pa_ind_rate_schedules irs
418 WHERE l.lookup_type = 'IND RATE SCHEDULE TYPE'
419 AND l.lookup_code = irs.ind_rate_schedule_type
420 AND irs.project_id IS NULL
421 AND trunc(sysdate) BETWEEN irs.start_date_active
422 AND nvl(irs.end_date_active,trunc(sysdate+1))
423 AND irs.ind_rate_sch_id = p_inv_sch_id;
424
425 ELSIF p_check_id_flag = 'N'
426 THEN
427 x_inv_sch_id := p_inv_sch_id;
428 ELSIF p_check_id_flag = 'A'
429 THEN
430 IF (p_inv_sch_name IS NULL) THEN
431 -- Return a null ID since the name is null.
432 x_inv_sch_id := NULL;
433 ELSE
437 FETCH c_ids INTO l_current_id;
434 -- Find the ID which matches the Name passed
435 OPEN c_ids;
436 LOOP
438 EXIT WHEN c_ids%NOTFOUND;
439 IF (l_current_id = p_inv_sch_id) THEN
440 l_id_found_flag := 'Y';
441 x_inv_sch_id := p_inv_sch_id;
442 END IF;
443 END LOOP;
444 l_num_ids := c_ids%ROWCOUNT;
445 CLOSE c_ids;
446 IF (l_num_ids = 0) THEN
447 -- No IDs for name
448 RAISE NO_DATA_FOUND;
449 ELSIF (l_num_ids = 1) THEN
450 -- Since there is only one ID for the name use it.
451 x_inv_sch_id := l_current_id;
452 ELSIF (l_id_found_flag = 'N') THEN
453 -- More than one ID for the name and none of the IDs matched
454 -- the ID passed in.
455 RAISE TOO_MANY_ROWS;
456 END IF;
457 END IF;
458 END IF;
459 ELSE
460 IF p_inv_sch_name IS NOT NULL
461 THEN
462 SELECT irs.ind_rate_sch_id
463 INTO x_inv_sch_id
464 FROM pa_lookups l, pa_ind_rate_schedules irs
465 WHERE l.lookup_type = 'IND RATE SCHEDULE TYPE'
466 AND l.lookup_code = irs.ind_rate_schedule_type
467 AND irs.project_id IS NULL
468 AND trunc(sysdate) BETWEEN irs.start_date_active
469 AND nvl(irs.end_date_active,trunc(sysdate+1))
470 AND irs.ind_rate_sch_name = p_inv_sch_name ;
471 ELSE
472 x_inv_sch_id := null;
473 END IF;
474 END IF;
475 x_return_status:= FND_API.G_RET_STS_SUCCESS;
476 EXCEPTION
477 WHEN no_data_found THEN
478 x_inv_sch_id := null;
479 x_return_status:= FND_API.G_RET_STS_ERROR;
480 x_error_msg_code:= 'PA_PRJ_INV_SCHEDULE_ID';
481 WHEN too_many_rows THEN
482 x_inv_sch_id := null;
486 x_inv_sch_id := null;
483 x_return_status:= FND_API.G_RET_STS_ERROR;
484 x_error_msg_code:= 'PA_PRJ_TOO_MANY_INV_SCH';
485 WHEN OTHERS THEN
487 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
488 RAISE;
489 END Inv_Sch_Name_To_Id;
490
491
492 -- API name : Nlbr_schedule_name_To_Id
493 -- Type : Public procedure
494 -- Pre-reqs : None
495 -- Return Value : N/A
496 -- Prameters
497 -- p_sch_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
498 -- p_sch_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR
499 -- p_nlbr_org_id IN NUMBER REQUIRED
500 -- p_check_id_flag IN VARCHAR2 REQUIRED DEFAULT 'A'
501 -- x_sch_id OUT NUMBER REQUIRED
502 -- x_return_status OUT VARCHAR2 REQUIRED
503 -- x_error_msg_code OUT VARCHAR2 REQUIRED
504 --
505 -- History
506 --
507 -- 21-MAY-01 Majid Ansari -Created
508 --
509 --
510
511 PROCEDURE Nlbr_schedule_name_To_Id(
512 p_sch_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
513 p_nlbr_org_id IN NUMBER,
514 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
515 x_sch_name OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
516 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
517 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
518 ) IS
519 BEGIN
520 IF p_sch_name IS NOT NULL
521 THEN
522 IF p_check_id_flag = 'Y'
523 THEN
524 SELECT brs.std_bill_rate_schedule
525 INTO x_sch_name
526 FROM pa_std_bill_rate_schedules brs, pa_lookups l
527 WHERE organization_id = p_nlbr_org_id
528 AND brs.schedule_type = 'NON-LABOR'
529 AND l.lookup_type = 'SCHEDULE TYPE'
530 AND l.lookup_code (+) = brs.schedule_type
531 AND brs.std_bill_rate_schedule = p_sch_name;
532 ELSE
533 x_sch_name := p_sch_name;
534 END IF;
535 END IF;
536 x_return_status:= FND_API.G_RET_STS_SUCCESS;
537 EXCEPTION
538 WHEN no_data_found THEN
539 x_sch_name := NULL;
540 x_return_status:= FND_API.G_RET_STS_ERROR;
541 x_error_msg_code:= 'PA_PRJ_NL_SCHEDULE_ID';
542 WHEN too_many_rows THEN
543 x_sch_name := NULL;
544 x_return_status:= FND_API.G_RET_STS_ERROR;
545 x_error_msg_code:= 'PA_PRJ_TOO_MANY_NL_SCH';
546 WHEN OTHERS THEN
547 x_sch_name := NULL;
548 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
549 RAISE;
550 END Nlbr_schedule_name_To_Id;
551
552
556 -- Return Value : N/A
553 -- API name : NL_org_sch_Name_To_Id
554 -- Type : Public procedure
555 -- Pre-reqs : None
557 -- Prameters
558 -- p_org_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
559 -- p_org_name IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_CHAR
560 -- p_nlbr_org_id IN NUMBER REQUIRED
561 -- p_check_id_flag IN VARCHAR2 REQUIRED DEFAULT 'A'
562 -- x_org_id OUT NUMBER REQUIRED
563 -- x_return_status OUT VARCHAR2 REQUIRED
564 -- x_error_msg_code OUT VARCHAR2 REQUIRED
565 --
566 -- History
567 --
568 -- 21-MAY-01 Majid Ansari -Created
569 --
570 --
571
572 PROCEDURE NL_org_sch_Name_To_Id(
573 p_org_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
574 p_org_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
575 p_check_id_flag IN VARCHAR2 DEFAULT 'A',
576 x_org_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
577 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
578 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
579 ) IS
580
581 l_current_id NUMBER := NULL;
582 l_num_ids NUMBER := 0;
583 l_id_found_flag VARCHAR(1) := 'N';
584
585 CURSOR c_ids
586 IS
587 SELECT o.organization_id
588 FROM pa_organizations_sbrs_v o
589 WHERE o.schedule_type = 'NON-LABOR'
590 AND o.name = p_org_name;
591
592 BEGIN
593 IF p_org_id IS NOT NULL
594 THEN
595 IF p_check_id_flag = 'Y'
596 THEN
597 SELECT o.organization_id
598 INTO x_org_id
599 FROM pa_organizations_sbrs_v o
600 WHERE o.schedule_type = 'NON-LABOR'
601 AND o.organization_id = p_org_id;
602 ELSIF p_check_id_flag = 'N'
603 THEN
604 x_org_id := p_org_id;
605 ELSIF p_check_id_flag = 'A'
606 THEN
607 IF (p_org_name IS NULL) THEN
608 -- Return a null ID since the name is null.
609 x_org_id := NULL;
610 ELSE
611 -- Find the ID which matches the Name passed
612 OPEN c_ids;
613 LOOP
614 FETCH c_ids INTO l_current_id;
615 EXIT WHEN c_ids%NOTFOUND;
616 IF (l_current_id = p_org_id) THEN
617 l_id_found_flag := 'Y';
618 x_org_id := p_org_id;
619 END IF;
620 END LOOP;
621 l_num_ids := c_ids%ROWCOUNT;
622 CLOSE c_ids;
623 IF (l_num_ids = 0) THEN
624 -- No IDs for name
625 RAISE NO_DATA_FOUND;
626 ELSIF (l_num_ids = 1) THEN
627 -- Since there is only one ID for the name use it.
628 x_org_id := l_current_id;
629 ELSIF (l_id_found_flag = 'N') THEN
630 -- More than one ID for the name and none of the IDs matched
631 -- the ID passed in.
632 RAISE TOO_MANY_ROWS;
633 END IF;
634 END IF;
635 END IF;
636 ELSE
637 IF p_org_name IS NOT NULL
638 THEN
639 SELECT o.organization_id
640 INTO x_org_id
641 FROM pa_organizations_sbrs_v o
642 WHERE o.schedule_type = 'NON-LABOR'
643 AND o.name = p_org_name;
644 ELSE
645 x_org_id := null;
646 END IF;
647 END IF;
648 x_return_status:= FND_API.G_RET_STS_SUCCESS;
649 EXCEPTION
650 WHEN no_data_found THEN
651 x_org_id := null;
652 x_return_status:= FND_API.G_RET_STS_ERROR;
653 x_error_msg_code:= 'PA_PRJ_INVALID_ORG';
654 WHEN too_many_rows THEN
655 x_org_id := null;
661 RAISE;
656 x_return_status:= FND_API.G_RET_STS_ERROR;
657 x_error_msg_code:= 'PA_PRJ_TOO_MANY_ORG';
658 WHEN OTHERS THEN
659 x_org_id := null;
660 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
662 END NL_org_sch_Name_To_Id;
663
664
665 -- API name : Duplicate_labor_Multiplier
666 -- Type : Public Function
667 -- Pre-reqs : None
668 -- Return Value : BOLLEAN
669 -- Prameters
670 -- p_project_id IN NUMBER REQUIRED
671 -- p_task_id IN NUMBER OPTIONAL DEFAULT FND_API.MISS_NUM
672 -- p_effective_from_date IN DATE REQUIRED
673 -- p_effective_to_date IN DATE
674 -- p_labor_multiplier_id IN NUMBER
675 -- x_return_status OUT VARCHAR2 REQUIRED
676 --
677 -- History
678 --
679 -- 21-MAY-01 Majid Ansari -Created
680 --
681 --
682
683 FUNCTION Duplicate_labor_Multiplier(
684 p_project_id IN NUMBER ,
685 p_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
686 p_effective_from_date IN DATE ,
687 p_effective_to_date IN DATE ,
688 p_labor_multiplier_id IN NUMBER ,
689 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
690 ) RETURN BOOLEAN IS
691
692 CURSOR cur_lbr_mult
693 IS
694 SELECT 'x'
695 FROM pa_labor_multipliers
696 WHERE project_id = p_project_id
697 AND NVL( TASK_ID, -1 ) = DECODE( p_task_id, NULL, -1, p_task_id )
698 AND (p_effective_from_date BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, p_effective_from_date + 1)
699 OR p_effective_to_date BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, p_effective_to_date + 1)
700 OR START_DATE_ACTIVE BETWEEN p_effective_from_date AND NVL(p_effective_to_date, START_DATE_ACTIVE + 1))
701 AND ( p_labor_multiplier_id IS NULL OR labor_multiplier_id <> p_labor_multiplier_id );
702 l_dummy_char VARCHAR2(1);
703 BEGIN
704 OPEN cur_lbr_mult;
705 FETCH cur_lbr_mult INTO l_dummy_char;
706 IF cur_lbr_mult%FOUND
707 THEN
708 CLOSE cur_lbr_mult;
709 RETURN TRUE;
710 ELSE
711 CLOSE cur_lbr_mult;
712 RETURN FALSE;
713 END IF;
714 x_return_status:= FND_API.G_RET_STS_SUCCESS;
715 EXCEPTION
716 WHEN OTHERS THEN
717 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
718 RAISE;
719 END Duplicate_labor_Multiplier;
720
721 -- API name : Emp_job_mandatory_validation
722 -- Type : Public procedure
723 -- Pre-reqs : None
724 -- Return Value : N/A
725 -- Prameters
726 -- p_emp_bill_rate_sch_id IN NUMBER OPTIONAL DEFAULT FND_API.G_MISS_NUM
727 -- p_job_bill_rate_sch_id IN VARCHAR2 OPTIONAL DEFAULT FND_API.G_MISS_NUM
728 -- x_return_status OUT VARCHAR2 REQUIRED
729 -- x_error_msg_code OUT VARCHAR2 REQUIRED
730 --
731 -- History
732 --
733 -- 21-MAY-01 Majid Ansari -Created
734 --
735 --
736
737 PROCEDURE Emp_job_mandatory_validation(
738 p_emp_bill_rate_sch_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
739 p_job_bill_rate_sch_id IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM,
740 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
741 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
742 ) IS
743 BEGIN
744 IF pa_install.is_prm_licensed = 'Y' THEN
745 IF p_job_bill_rate_sch_id IS NULL OR p_job_bill_rate_sch_id = FND_API.G_MISS_NUM
746 THEN
747 x_return_status:= FND_API.G_RET_STS_ERROR;
748 x_error_msg_code:= 'PA_JOB_BILL_RT_SCH_NOT_NULL';
749 END IF;
750 ELSE
751 IF ( p_emp_bill_rate_sch_id IS NULL OR p_emp_bill_rate_sch_id = FND_API.G_MISS_NUM ) AND
752 ( p_job_bill_rate_sch_id IS NULL OR p_job_bill_rate_sch_id = FND_API.G_MISS_NUM )
753 THEN
754 x_return_status:= FND_API.G_RET_STS_ERROR;
755 x_error_msg_code:= 'PA_EJ_BILL_RT_SCH_NOT_NULL';
756 END IF;
757 END IF;
758 EXCEPTION
759 WHEN OTHERS THEN
760 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
761 RAISE;
762 END Emp_job_mandatory_validation;
763
764 -- API name : Get_Job_Group_Id
765 -- Type : Public procedure
766 -- Pre-reqs : None
767 -- Return Value : N/A
768 -- Prameters
769 -- p_project_id IN NUMBER REQUIRED
770 -- x_return_status OUT VARCHAR2 REQUIRED
771 --
772 -- History
773 --
774 -- 21-MAY-01 Majid Ansari -Created
775 --
776 --
777
778 FUNCTION Get_Job_Group_Id(
779 p_project_id NUMBER ,
780 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
781 ) RETURN NUMBER AS
782
783 --Derive bill_job_group_id from pa_projects_prm_v using project-task id.
784 CURSOR cur_job_group IS
785 SELECT bill_job_group_id
786 FROM pa_projects_prm_v
787 WHERE project_id = p_project_id;
788 l_job_group_id NUMBER;
789
790 BEGIN
791 OPEN cur_job_group;
792 FETCH cur_job_group INTO l_job_group_id;
793 CLOSE cur_job_group;
794 x_return_status:= FND_API.G_RET_STS_SUCCESS;
795 RETURN l_job_group_id;
796 EXCEPTION
800 END Get_Job_Group_Id;
797 WHEN OTHERS THEN
798 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
799 RAISE;
801
802
803 -- API name : Get_Project_Type_Class
804 -- Type : Public procedure
805 -- Pre-reqs : None
806 -- Return Value : VARCHAR2
807 -- Prameters
808 -- p_project_id IN NUMBER REQUIRED
809 -- x_return_status OUT VARCHAR2 REQUIRED
810 --
811 -- History
812 --
813 -- 21-MAY-01 Majid Ansari -Created
814 --
815 --
816
817 FUNCTION Get_Project_Type_Class(
818 p_project_id NUMBER ,
819 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
820 ) RETURN VARCHAR2 AS
821
822 CURSOR cur_projects_all
823 IS
824 SELECT ppt.project_type_class_code
825 FROM pa_projects_all ppa, pa_project_types ppt
826 WHERE ppa.project_id = p_project_id
827 AND ppa.project_type = ppt.project_type;
828
829 l_project_type_class_code VARCHAR2(30);
830 BEGIN
831 OPEN cur_projects_all;
832 FETCH cur_projects_all INTO l_project_type_class_code;
833 CLOSE cur_projects_all;
834 RETURN l_project_type_class_code;
835 x_return_status:= FND_API.G_RET_STS_SUCCESS;
836 EXCEPTION
837 WHEN OTHERS THEN
838 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
839 END Get_Project_Type_Class;
840
841 -- API name : CHECK_BILL_INFO_REQ
842 -- Type : Public procedure
843 -- Pre-reqs : None
844 -- Return Value : N/A
845 -- Prameters
846 -- p_project_type_class_code IN VARCHAR2 REQUIRED
847 -- p_lbr_schedule_type IN VARCHAR2 REQUIRED
848 -- p_non_lbr_schedule_type IN VARCHAR2 REQUIRED
849 -- p_emp_bill_rate_sch_id IN NUMBER REQUIRED
850 -- p_job_bill_rate_sch_id IN NUMBER REQUIRED
851 -- p_rev_schedule_id IN NUMBER REQUIRED
852 -- p_inv_schedule_id IN NUMBER REQUIRED
853 -- p_nlbr_bill_rate_org_id IN NUMBER REQUIRED
854 -- p_nlbr_std_bill_rate_sch IN VARCHAR2 REQUIRED
855 -- x_error_msg_code OUT VARCHAR2 REQUIRED
856 -- x_return_status OUT VARCHAR2 REQUIRED
857 --
858 -- History
859 --
860 -- 06-JUN-01 Majid Ansari -Created
861 --
862 --
863
864 PROCEDURE CHECK_BILL_INFO_REQ(
865 p_project_type_class_code IN VARCHAR2,
866 p_lbr_schedule_type IN VARCHAR2,
867 p_non_lbr_schedule_type IN VARCHAR2,
868 p_emp_bill_rate_sch_id IN NUMBER,
869 p_job_bill_rate_sch_id IN NUMBER,
870 p_rev_schedule_id IN NUMBER,
871 p_inv_schedule_id IN NUMBER,
872 p_nlbr_bill_rate_org_id IN NUMBER,
873 p_nlbr_std_bill_rate_sch IN VARCHAR2,
874 x_error_msg_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
875 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
876 ) AS
877
878 l_error_msg_code VARCHAR2(250);
879 l_return_status VARCHAR2(1);
880 BEGIN
881 x_return_status := 'S';
882 IF p_project_type_class_code = 'CONTRACT'
883 THEN
884 IF p_lbr_schedule_type = 'B'
885 THEN
886 PA_BILLING_SCHEDULE_UTILS.Emp_job_mandatory_validation(
887 p_emp_bill_rate_sch_id,
888 p_job_bill_rate_sch_id,
889 l_return_status,
890 l_error_msg_code );
891 IF l_return_status = FND_API.G_RET_STS_ERROR
892 THEN
893 x_error_msg_code := l_error_msg_code;
894 x_return_status := 'E';
895 RAISE FND_API.G_EXC_ERROR;
896 END IF;
897 ELSIF p_lbr_schedule_type = 'I'
898 THEN
899 --If any of the revenue or inv schedule is null then raise a message.
900 IF p_rev_schedule_id IS NULL OR p_rev_schedule_id = FND_API.G_MISS_NUM
901 THEN
902 x_error_msg_code := 'PA_PRJ_REV_SCH_REQ';
903 x_return_status:= FND_API.G_RET_STS_ERROR;
904 RAISE FND_API.G_EXC_ERROR;
905 END IF;
906
907 IF p_inv_schedule_id IS NULL OR p_inv_schedule_id = FND_API.G_MISS_NUM
908 THEN
909 x_error_msg_code := 'PA_PRJ_INV_SCH_REQ';
910 x_return_status:= FND_API.G_RET_STS_ERROR;
911 RAISE FND_API.G_EXC_ERROR;
912 END IF;
913 END IF;
914
915 IF p_non_lbr_schedule_type = 'B'
916 THEN
917 IF p_nlbr_bill_rate_org_id IS NULL OR p_nlbr_bill_rate_org_id = FND_API.G_MISS_NUM
918 THEN
919 x_error_msg_code := 'PA_PRJ_ORG_ID_REQ';
920 x_return_status:= FND_API.G_RET_STS_ERROR;
921 RAISE FND_API.G_EXC_ERROR;
922 END IF;
923
924 IF p_nlbr_std_bill_rate_sch IS NULL OR p_nlbr_std_bill_rate_sch = FND_API.G_MISS_CHAR
925 THEN
926 x_error_msg_code := 'PA_PRJ_NL_SCH_REQ';
927 x_return_status:= FND_API.G_RET_STS_ERROR;
928 RAISE FND_API.G_EXC_ERROR;
929 END IF;
930
931 ELSIF p_non_lbr_schedule_type = 'I'
932 THEN
933
934 IF p_rev_schedule_id IS NULL OR p_rev_schedule_id = FND_API.G_MISS_NUM
935 THEN
936 x_error_msg_code := 'PA_PRJ_REV_SCH_REQ';
937 x_return_status:= FND_API.G_RET_STS_ERROR;
938 RAISE FND_API.G_EXC_ERROR;
939 END IF;
940
941 IF p_inv_schedule_id IS NULL OR p_inv_schedule_id = FND_API.G_MISS_NUM
942 THEN
943 x_error_msg_code := 'PA_PRJ_INV_SCH_REQ';
944 x_return_status:= FND_API.G_RET_STS_ERROR;
945 RAISE FND_API.G_EXC_ERROR;
946 END IF;
947 END IF;
948
949 END IF;
950 EXCEPTION
951 WHEN FND_API.G_EXC_ERROR THEN
952 x_return_status:= FND_API.G_RET_STS_ERROR;
953 END CHECK_BILL_INFO_REQ;
954
955 -- API name : CHECK_LABOR_MULTIPLIER_REQ
956 -- Type : Public procedure
957 -- Pre-reqs : None
958 -- Return Value : N/A
959 -- Prameters
960 -- p_labor_multiplier IN NUMBER,
961 -- p_effective_from_date IN VARCHAR2,
962 -- x_error_msg_code OUT VARCHAR2 REQUIRED
963 -- x_return_status OUT VARCHAR2 REQUIRED
964 --
965 -- History
966 --
967 -- 06-JUN-01 Majid Ansari -Created
968 --
969 --
970
971 PROCEDURE CHECK_LABOR_MULTIPLIER_REQ(
972 p_labor_multiplier IN NUMBER,
973 p_effective_from_date IN DATE,
974 x_error_msg_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
975 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
976 ) AS
977 BEGIN
978 x_return_status:= FND_API.G_RET_STS_SUCCESS;
979 IF p_labor_multiplier IS NULL OR p_labor_multiplier = FND_API.G_MISS_NUM
980 THEN
981 x_error_msg_code := 'PA_PRJ_LB_MULT_REQ';
982 x_return_status:= FND_API.G_RET_STS_ERROR;
983 RAISE FND_API.G_EXC_ERROR;
984 END IF;
985
986 IF p_effective_from_date IS NULL OR p_effective_from_date = FND_API.G_MISS_DATE
987 THEN
988 x_error_msg_code := 'PA_PRJ_ST_DT_REQ';
989 x_return_status:= FND_API.G_RET_STS_ERROR;
990 RAISE FND_API.G_EXC_ERROR;
991 END IF;
992
993 EXCEPTION
994 WHEN FND_API.G_EXC_ERROR THEN
995 x_return_status:= FND_API.G_RET_STS_ERROR;
996 END CHECK_LABOR_MULTIPLIER_REQ;
997
998 -- API name : CHECK_START_END_DATE
999 -- Type : Public procedure
1000 -- Pre-reqs : None
1001 -- Return Value : N/A
1002 -- Prameters
1003 -- p_effective_from_date IN DATE REQUIRED
1004 -- p_effective_to_date IN DATE REQUIRED
1005 -- x_msg_count OUT NUMBER REQUIRED
1006 -- x_msg_data OUT VARCHAR2 REQUIRED
1007 -- x_return_status OUT VARCHAR2 REQUIRED
1008 --
1009 -- History
1010 --
1011 -- 06-JUN-01 Majid Ansari -Created
1012 --
1013 --
1014
1015 PROCEDURE CHECK_START_END_DATE(
1016 p_effective_from_date IN DATE,
1017 p_effective_to_date IN DATE,
1018 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1019 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1020 ) AS
1021 BEGIN
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023 IF p_effective_from_date IS NOT NULL AND p_effective_to_date IS NOT NULL
1024 THEN
1025 IF p_effective_from_date > p_effective_to_date
1026 THEN
1027 x_error_msg_code := 'PA_PRJ_ST_DT_LESS_ED_DT';
1028 x_return_status:= FND_API.G_RET_STS_ERROR;
1029 RAISE FND_API.G_EXC_ERROR;
1030 END IF;
1031 END IF;
1032 EXCEPTION
1033 WHEN FND_API.G_EXC_ERROR THEN
1034 x_return_status:= FND_API.G_RET_STS_ERROR;
1035 END CHECK_START_END_DATE;
1036
1037 END PA_BILLING_SCHEDULE_UTILS;
[Home] [Help]