1 PACKAGE BODY pa_hr_transactions AS
2 /* $Header: PAHRTRXB.pls 120.1 2005/08/17 12:56:48 ramurthy noship $ */
3 --
4 --
5 PROCEDURE check_person_reference (p_person_id IN number,
6 Error_Message OUT NOCOPY varchar2,
7 Reference_Exist OUT NOCOPY varchar2)
8 IS
9 reference_exists exception;
10 dummy1 varchar2(1);
11
12 cursor trans_interface( p_person_id number ) is
13 select null
14 from PA_TRANSACTION_INTERFACE_ALL pa,
15 --FP M CWK changes
16 --PER_PEOPLE_F ppf
17 PER_ALL_PEOPLE_F ppf
18 --where pa.employee_number = ppf.employee_number
19 where pa.employee_number in (ppf.employee_number, ppf.npw_number)
20 and ppf.person_id = P_PERSON_ID
21 AND pa.transaction_status_code <> 'A';
22
23 cursor routing( p_person_id number ) is
24 select null
25 from PA_ROUTINGS pa
26 where pa.routed_from_person_id = P_PERSON_ID
27 OR pa.routed_to_person_id = P_PERSON_ID;
28
29 /* *********
30 cursor pte_multi_org( p_person_id number ) is
31 select null
32 from PA_PTE_MULTI_ORG_EMP_MAP pa
33 where pa.person_id = P_PERSON_ID;
34 **** */
35
36 cursor online_exp( p_person_id number ) is
37 select null
38 from PA_ONLINE_EXP_SETTINGS pa
39 where pa.person_id = P_PERSON_ID;
40
41 cursor exp_comment( p_person_id number ) is
42 select null
43 from PA_EXPEND_COMMENT_ALIASES pa
44 where pa.person_id = P_PERSON_ID;
45
46 /* Commented for Bug#3211124
47 cursor ei_denorm( p_person_id number ) is
48 select null
49 from PA_EI_DENORM pa
50 where pa.person_id = P_PERSON_ID;
51 * Commented code for Bug#3211124 ends here */
52
53 cursor expenditure( p_person_id number ) is
54 select null
55 from pa_expenditures_all pa
56 where pa.incurred_by_person_id = P_PERSON_ID;
57
58 cursor trans_control( p_person_id number ) is
59 select null
60 from pa_transaction_controls pa
61 where pa.project_id > -1
62 and pa.person_id = P_PERSON_ID;
63 BEGIN
64 Error_Message := 'PA_HR_PER_TRANS_INTERFACE';
65 OPEN trans_interface(p_person_id);
66 FETCH trans_interface INTO dummy1;
67 IF trans_interface%found THEN
68 CLOSE trans_interface;
69 raise reference_exists;
70 END IF;
71 CLOSE trans_interface;
72
73 /* Bug#3211124 - Take care of the case - id support introduced in FP K */
74 BEGIN
75 SELECT null
76 INTO dummy1
77 FROM pa_transaction_interface_all
78 WHERE person_id = p_person_id
79 AND transaction_status_code <> 'A'
80 AND rownum = 1;
81
82 RAISE reference_exists;
83
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 NULL;
87 END;
88
89
90 Error_Message := 'PA_HR_PER_ROUTING_FROM';
91 OPEN routing(p_person_id);
92 FETCH routing INTO dummy1;
93 IF routing%found THEN
94 CLOSE routing;
95 raise reference_exists;
96 END IF;
97 CLOSE routing;
98
99 /* ****
100 Error_Message := 'PA_HR_PER_PTE_MULTI_ORG';
101 OPEN pte_multi_org(p_person_id);
102 FETCH pte_multi_org INTO dummy1;
103 IF pte_multi_org%found THEN
104 CLOSE pte_multi_org;
105 raise reference_exists;
106 END IF;
107 CLOSE pte_multi_org;
108 **** */
109
110 Error_Message := 'PA_HR_PER_ONLINE_SETTING';
111 OPEN online_exp(p_person_id);
112 FETCH online_exp INTO dummy1;
113 IF online_exp%found THEN
114 CLOSE online_exp;
115 raise reference_exists;
116 END IF;
117 CLOSE online_exp;
118
119 Error_Message := 'PA_HR_PER_EXPEND_COMMENT';
120 OPEN exp_comment(p_person_id);
121 FETCH exp_comment INTO dummy1;
122 IF exp_comment%found THEN
123 CLOSE exp_comment;
124 raise reference_exists;
125 END IF;
126 CLOSE exp_comment;
127
128 /* Commented for Bug#3211124
129 Error_Message := 'PA_HR_PER_EI_DENORM';
130 OPEN ei_denorm(p_person_id);
131 FETCH ei_denorm INTO dummy1;
132 IF ei_denorm%found THEN
133 CLOSE ei_denorm;
134 raise reference_exists;
135 END IF;
136 CLOSE ei_denorm;
137 * Commented code for Bug#3211124 ends here */
138
139 Error_Message := 'PA_HR_PER_EXPENDITURE';
140 OPEN expenditure(p_person_id);
141 FETCH expenditure INTO dummy1;
142 IF expenditure%found THEN
143 CLOSE expenditure;
144 raise reference_exists;
145 END IF;
146 CLOSE expenditure;
147
148 Error_Message := 'PA_HR_PER_TRANS_CONTROL';
149 OPEN trans_control(p_person_id);
150 FETCH trans_control INTO dummy1;
151 IF trans_control%found THEN
152 CLOSE trans_control;
153 raise reference_exists;
154 END IF;
155 CLOSE trans_control;
156
157 Reference_Exist := 'N';
158 Error_Message := NULL;
159 EXCEPTION
160 WHEN reference_exists THEN
161 Reference_Exist := 'Y';
162 WHEN others THEN
163 raise;
164 END check_person_reference;
165
166 PROCEDURE check_job_reference (p_job_id IN number,
167 Error_Message OUT NOCOPY varchar2,
168 Reference_Exist OUT NOCOPY varchar2)
169 IS
170 reference_exists exception;
171 dummy1 varchar2(1);
172
173 cursor expenditure( p_job_id varchar ) is
174 SELECT 'Y'
175 FROM DUAL
176 WHERE EXISTS (
177 select null
178 from pa_expenditure_items_all pa
179 where (pa.job_id is not null
180 AND pa.job_id = P_JOB_ID)
181 OR
182 (pa.bill_job_id is not null
183 AND pa.bill_job_id = P_JOB_ID));
184
185 /* Commented for Bug#3211124
186 cursor ei_denorm( p_job_id varchar ) is
187 select null
188 from pa_ei_denorm pa
189 where (pa.job_id_1 is not null
190 AND pa.job_id_1 = P_JOB_ID )
191 OR (pa.job_id_2 is not null
192 AND pa.job_id_2 = P_JOB_ID )
193 OR (pa.job_id_3 is not null
194 AND pa.job_id_3 = P_JOB_ID )
195 OR (pa.job_id_4 is not null
196 AND pa.job_id_4 = P_JOB_ID )
197 OR (pa.job_id_5 is not null
198 AND pa.job_id_5 = P_JOB_ID )
199 OR (pa.job_id_6 is not null
200 AND pa.job_id_6 = P_JOB_ID )
201 OR (pa.job_id_7 is not null
202 AND pa.job_id_7 = P_JOB_ID );
203 * Commented code for Bug#3211124 ends here */
204
205 /* Added for Bug#3211124 */
206 cursor oit_expenditures is
207 select * from pa_expenditures_all
208 where expenditure_class_code = 'PT'
209 and expenditure_status_code = 'APPROVED'
210 and transfer_status_code = 'P';
211
212 cursor ei_denorm(p_exp_id number) is
213 select expenditure_item_date_1, quantity_1,
214 expenditure_item_date_2, quantity_2,
215 expenditure_item_date_3, quantity_3,
216 expenditure_item_date_4, quantity_4,
217 expenditure_item_date_5, quantity_5,
218 expenditure_item_date_6, quantity_6,
219 expenditure_item_date_7, quantity_7,
220 person_id
221 from pa_ei_denorm
222 where expenditure_id = p_exp_id;
223
224 BEGIN
225 Error_Message := 'PA_HR_JOB_ONLINE_EXPEND';
226 /* Commented for Bug#3211124
227 OPEN ei_denorm(p_job_id);
228 FETCH ei_denorm INTO dummy1;
229 IF ei_denorm%found THEN
230 raise reference_exists;
231 END IF;
232 * Commented code for Bug#3211124 ends here */
233
234 for oit_exp_rec in oit_expenditures loop
235 for ei_denorm_rec in ei_denorm(oit_exp_rec.expenditure_id) loop
236 if (ei_denorm_rec.quantity_1 is not null) then
237 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_1) = P_JOB_ID then
238 raise reference_exists;
239 end if;
240 end if;
241 if (ei_denorm_rec.quantity_2 is not null) then
242 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_2) = P_JOB_ID then
243 raise reference_exists;
244 end if;
245 end if;
246 if (ei_denorm_rec.quantity_3 is not null) then
247 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_3) = P_JOB_ID then
248 raise reference_exists;
249 end if;
250 end if;
251 if (ei_denorm_rec.quantity_4 is not null) then
252 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_4) = P_JOB_ID then
253 raise reference_exists;
254 end if;
255 end if;
256 if (ei_denorm_rec.quantity_5 is not null) then
257 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_5) = P_JOB_ID then
258 raise reference_exists;
259 end if;
260 end if;
261 if (ei_denorm_rec.quantity_6 is not null) then
262 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_6) = P_JOB_ID then
263 raise reference_exists;
264 end if;
265 end if;
266 if (ei_denorm_rec.quantity_7 is not null) then
267 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_7) = P_JOB_ID then
268 raise reference_exists;
269 end if;
270 end if;
271 end loop;
272 end loop;
273
274 Error_Message := 'PA_HR_JOB_EXPEND_ITEM';
275 OPEN expenditure(p_job_id);
276 FETCH expenditure INTO dummy1;
277 IF expenditure%found THEN
278 raise reference_exists;
279 END IF;
280
281 Reference_Exist := 'N';
282 Error_Message := NULL;
283 EXCEPTION
284 WHEN reference_exists THEN
285 Reference_Exist := 'Y';
286 WHEN others THEN
287 raise;
288 END check_job_reference;
289 --
290 END pa_hr_transactions ;
[Home] [Help]