1 PACKAGE BODY pa_hr_transactions AS
2 /* $Header: PAHRTRXB.pls 120.1.12010000.2 2009/05/05 09:58:56 jjgeorge ship $ */
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
28
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;
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 /*
174 cursor expenditure( p_job_id varchar ) is
175 SELECT 'Y'
176 FROM DUAL
177 WHERE EXISTS (
178 select null
179 from pa_expenditure_items_all pa
180 where (pa.job_id is not null
181 AND pa.job_id = P_JOB_ID)
182 OR
183 (pa.bill_job_id is not null
184 AND pa.bill_job_id = P_JOB_ID));
185 */
186
187
188 --Bug 8242639 / 8370836
189 cursor expenditure( p_job_id varchar ) is
190 SELECT 'Y'
191 FROM
192 DUAL WHERE EXISTS
193 (( SELECT NULL FROM PA_EXPENDITURE_ITEMS_ALL PA WHERE
194 PA.JOB_ID IS NOT NULL AND PA.JOB_ID = P_JOB_ID )
195 UNION
196 (SELECT NULL FROM PA_EXPENDITURE_ITEMS_ALL PA WHERE
197 PA.BILL_JOB_ID IS NOT NULL AND PA.BILL_JOB_ID = P_JOB_ID )
198 );
199
200 /* Commented for Bug#3211124
201 cursor ei_denorm( p_job_id varchar ) is
202 select null
203 from pa_ei_denorm pa
204 where (pa.job_id_1 is not null
205 AND pa.job_id_1 = P_JOB_ID )
206 OR (pa.job_id_2 is not null
207 AND pa.job_id_2 = P_JOB_ID )
208 OR (pa.job_id_3 is not null
209 AND pa.job_id_3 = P_JOB_ID )
210 OR (pa.job_id_4 is not null
211 AND pa.job_id_4 = P_JOB_ID )
212 OR (pa.job_id_5 is not null
213 AND pa.job_id_5 = P_JOB_ID )
214 OR (pa.job_id_6 is not null
215 AND pa.job_id_6 = P_JOB_ID )
216 OR (pa.job_id_7 is not null
217 AND pa.job_id_7 = P_JOB_ID );
218 * Commented code for Bug#3211124 ends here */
219
220 /* Added for Bug#3211124 */
221 cursor oit_expenditures is
222 select * from pa_expenditures_all
223 where expenditure_class_code = 'PT'
224 and expenditure_status_code = 'APPROVED'
225 and transfer_status_code = 'P';
226
227 cursor ei_denorm(p_exp_id number) is
228 select expenditure_item_date_1, quantity_1,
229 expenditure_item_date_2, quantity_2,
230 expenditure_item_date_3, quantity_3,
231 expenditure_item_date_4, quantity_4,
232 expenditure_item_date_5, quantity_5,
233 expenditure_item_date_6, quantity_6,
234 expenditure_item_date_7, quantity_7,
235 person_id
236 from pa_ei_denorm
237 where expenditure_id = p_exp_id;
238
239 BEGIN
240 Error_Message := 'PA_HR_JOB_ONLINE_EXPEND';
241 /* Commented for Bug#3211124
242 OPEN ei_denorm(p_job_id);
243 FETCH ei_denorm INTO dummy1;
244 IF ei_denorm%found THEN
245 raise reference_exists;
246 END IF;
247 * Commented code for Bug#3211124 ends here */
248
249 for oit_exp_rec in oit_expenditures loop
250 for ei_denorm_rec in ei_denorm(oit_exp_rec.expenditure_id) loop
251 if (ei_denorm_rec.quantity_1 is not null) then
252 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_1) = P_JOB_ID then
253 raise reference_exists;
254 end if;
255 end if;
256 if (ei_denorm_rec.quantity_2 is not null) then
257 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_2) = P_JOB_ID then
258 raise reference_exists;
259 end if;
260 end if;
261 if (ei_denorm_rec.quantity_3 is not null) then
262 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_3) = P_JOB_ID then
263 raise reference_exists;
264 end if;
265 end if;
266 if (ei_denorm_rec.quantity_4 is not null) then
267 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_4) = P_JOB_ID then
268 raise reference_exists;
269 end if;
270 end if;
271 if (ei_denorm_rec.quantity_5 is not null) then
272 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_5) = P_JOB_ID then
273 raise reference_exists;
274 end if;
275 end if;
276 if (ei_denorm_rec.quantity_6 is not null) then
277 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_6) = P_JOB_ID then
278 raise reference_exists;
279 end if;
280 end if;
281 if (ei_denorm_rec.quantity_7 is not null) then
282 if pa_utils.GetEmpJobId(ei_denorm_rec.person_id, ei_denorm_rec.expenditure_item_date_7) = P_JOB_ID then
283 raise reference_exists;
284 end if;
285 end if;
286 end loop;
287 end loop;
288
289 Error_Message := 'PA_HR_JOB_EXPEND_ITEM';
290 OPEN expenditure(p_job_id);
291 FETCH expenditure INTO dummy1;
292 IF expenditure%found THEN
293 raise reference_exists;
294 END IF;
295
296 Reference_Exist := 'N';
297 Error_Message := NULL;
298 EXCEPTION
299 WHEN reference_exists THEN
300 Reference_Exist := 'Y';
301 WHEN others THEN
302 raise;
303 END check_job_reference;
304 --
305 END pa_hr_transactions ;
[Home] [Help]