1 PACKAGE BODY pa_hr_bill_rates AS
2 /* $Header: PAHRBLRB.pls 115.0 99/07/16 15:04:54 porting shi $ */
3 --
4 --
5 PROCEDURE check_person_reference (p_person_id IN number,
6 Error_Message OUT varchar2,
7 Reference_Exist OUT varchar2)
8 IS
9 reference_exists exception;
10 dummy1 varchar2(1);
11
12 cursor bill_rate_over( p_person_id number ) is
13 select null
14 from pa_emp_bill_rate_overrides pa
15 where pa.project_id > -1
16 and pa.task_id > -1
17 and pa.person_id = P_PERSON_ID;
18
19 cursor assign_over( p_person_id number ) is
20 select null
21 from pa_job_assignment_overrides pa
22 where ((pa.project_id > -1 AND
23 pa.person_id = P_PERSON_ID)
24 OR
25 (pa.task_id > -1 AND
26 pa.person_id = P_PERSON_ID));
27
28 cursor bill_rate( p_person_id number ) is
29 select
30 null
31 from pa_bill_rates_all pa
32 where pa.bill_rate_organization_id > -1
33 and pa.std_bill_rate_schedule <> 'DUMMY'
34 and pa.person_id = P_PERSON_ID;
35
36 BEGIN
37
38 Error_Message := 'PA_HR_PER_BILL_RATE_OVERD';
39 OPEN bill_rate_over(p_person_id);
40 FETCH bill_rate_over INTO dummy1;
41 IF bill_rate_over%found THEN
42 CLOSE bill_rate_over;
43 raise reference_exists;
44 END IF;
45 CLOSE bill_rate_over;
46
47 Error_Message := 'PA_HR_PER_ASSIGN_OVERD';
48 OPEN assign_over(p_person_id);
49 FETCH assign_over INTO dummy1;
50 IF assign_over%found THEN
51 CLOSE assign_over;
52 raise reference_exists;
53 END IF;
54 CLOSE assign_over;
55
56 Error_Message := 'PA_HR_PER_BILL_RATE';
57 OPEN bill_rate(p_person_id);
58 FETCH bill_rate INTO dummy1;
59 IF bill_rate%found THEN
60 CLOSE bill_rate;
61 raise reference_exists;
62 END IF;
63 CLOSE bill_rate;
64
65 Reference_Exist := 'N';
66 Error_Message := NULL;
67 EXCEPTION
68 WHEN reference_exists THEN
69 Reference_Exist := 'Y';
70 WHEN others THEN
71 raise;
72 END check_person_reference;
73
74 PROCEDURE check_job_reference (p_job_id IN number,
75 Error_Message OUT varchar2,
76 Reference_Exist OUT varchar2)
77 IS
78 reference_exists exception;
79 dummy1 varchar2(1);
80
81 cursor bill_rate( p_job_id number ) is
82 select null
83 from pa_bill_rates_all pa
84 where pa.job_id = P_JOB_ID;
85
86 cursor assign_over( p_job_id number ) is
87 select null
88 from pa_job_assignment_overrides pa
89 where pa.job_id = P_JOB_ID;
90
91 cursor bill_rate_over( p_job_id number ) is
92 select null
93 from PA_JOB_BILL_RATE_OVERRIDES pa
94 where pa.job_id = P_JOB_ID;
95
96 cursor bill_rate_title( p_job_id number ) is
97 select null
98 from PA_JOB_BILL_TITLE_OVERRIDES pa
99 where pa.job_id = P_JOB_ID;
100
101 BEGIN
102
103 Error_Message := 'PA_HR_JOB_BILL_RATE';
104 OPEN bill_rate(p_job_id);
105 FETCH bill_rate INTO dummy1;
106 IF bill_rate%found THEN
107 CLOSE bill_rate;
108 raise reference_exists;
109 END IF;
110 CLOSE bill_rate;
111
112 Error_Message := 'PA_HR_JOB_ASSIGN_OVERD';
113 OPEN assign_over(p_job_id);
114 FETCH assign_over INTO dummy1;
115 IF assign_over%found THEN
116 CLOSE assign_over;
117 raise reference_exists;
118 END IF;
119 CLOSE assign_over;
120
121 Error_Message := 'PA_HR_JOB_BILL_RATE_OVERD';
122 OPEN bill_rate_over(p_job_id);
123 FETCH bill_rate_over INTO dummy1;
124 IF bill_rate_over%found THEN
125 CLOSE bill_rate_over;
126 raise reference_exists;
127 END IF;
128 CLOSE bill_rate_over;
129
130 Error_Message := 'PA_HR_JOB_BILL_TITLE_OVERD';
131 OPEN bill_rate_title(p_job_id);
132 FETCH bill_rate_title INTO dummy1;
133 IF bill_rate_title%found THEN
134 CLOSE bill_rate_title;
135 raise reference_exists;
136 END IF;
137 CLOSE bill_rate_title;
138
139 Reference_Exist := 'N';
140 Error_Message := NULL;
141 EXCEPTION
142 WHEN reference_exists THEN
143 Reference_Exist := 'Y';
144 WHEN others THEN
145 raise;
146 END check_job_reference;
147
148 --
149 END pa_hr_bill_rates;
[Home] [Help]