[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_MULTI_DML_AND_VAL
Source
1 PACKAGE BODY PAY_GB_MULTI_DML_AND_VAL
2 /* $Header: pygbmpdml.pkb 120.1.12020000.2 2013/03/09 06:21:52 ssanjays noship $ */
3 AS
4
5
6 FUNCTION GET_GROUP_MAX_ID
7 RETURN NUMBER
8 IS
9
10 V_COUNT NUMBER;
11 BEGIN
12
13 V_COUNT:=0;
14 hr_utility.trace('V_COUNT:'||V_COUNT);
15
16
17 SELECT NVL(MAX(payroll_group_id),0)+1
18 INTO V_COUNT FROM PAY_GB_PAYROLL_GROUPS;
19
20
21 RETURN V_COUNT;
22 END GET_GROUP_MAX_ID;
23
24
25 FUNCTION VALIDATE_FREQUENCY(
26 P_PAYROLL_ID IN NUMBER
27 ,P_PAYROLL_GROUP_ID IN NUMBER
28 )
29 RETURN NUMBER
30
31 IS
32 V_STATUS NUMBER;
33 G_FREQUENCY VARCHAR2(30);
34 P_FREQUENCY VARCHAR2(30);
35 BEGIN
36
37 SELECT PAY_FREQUENCY INTO G_FREQUENCY
38 FROM PAY_GB_PAYROLL_GROUPS WHERE
39 PAYROLL_GROUP_ID =P_PAYROLL_GROUP_ID;
40
41 SELECT distinct PERIOD_TYPE INTO P_FREQUENCY
42 FROM PAY_ALL_PAYROLLS_F WHERE
43 PAYROLL_ID=P_PAYROLL_ID;
44
45 IF P_FREQUENCY = G_FREQUENCY THEN
46 V_STATUS:=1;
47 ELSE
48 V_STATUS:=0;
49 END IF;
50 RETURN V_STATUS;
51 END VALIDATE_FREQUENCY;
52
53
54 FUNCTION CHK_ALREADY_ROLLED_BACK(P_REQUEST_ID NUMBER)
55 RETURN NUMBER
56 IS
57
58 V_PAY_ACTION_ID NUMBER;
59 V_STATUS NUMBER;
60 BEGIN
61
62 V_STATUS:=1;
63
64 BEGIN
65 SELECT PPA.PAYROLL_ACTION_ID INTO
66 V_PAY_ACTION_ID FROM PAY_PAYROLL_ACTIONS PPA
67 WHERE PPA.REQUEST_ID=P_REQUEST_ID;
68
69 hr_utility.trace('V_STATUS:'||V_STATUS);
70 EXCEPTION WHEN NO_DATA_FOUND THEN
71 V_STATUS:=0;
72 END;
73 hr_utility.trace('V_STATUS after excpetion'||V_STATUS);
74 RETURN V_STATUS;
75 END CHK_ALREADY_ROLLED_BACK;
76
77
78 FUNCTION VAL_PAYROLL_GROUPS
79 (
80 P_PAYROLL_GROUP_NAME IN VARCHAR2
81 ,P_BUSINESS_GROUP_ID IN NUMBER
82 )
83 RETURN NUMBER
84 IS
85
86 V_PAYROLL_GROUP_NAME VARCHAR2(100);
87 V_STATUS NUMBER :=0;
88 BEGIN
89
90 SELECT PGPG.PAYROLL_GROUP_NAME INTO V_PAYROLL_GROUP_NAME
91 FROM PAY_GB_PAYROLL_GROUPS PGPG
92 WHERE PGPG.PAYROLL_GROUP_NAME=P_PAYROLL_GROUP_NAME
93 AND PGPG.BUSINESS_GROUP_ID=P_BUSINESS_GROUP_ID;
94
95 V_STATUS:=0;
96 RETURN V_STATUS;
97
98 EXCEPTION WHEN NO_DATA_FOUND THEN
99 V_STATUS:=1;
100
101 RETURN V_STATUS;
102 END VAL_PAYROLL_GROUPS;
103
104 FUNCTION VAL_PAYROLLS_IN_GROUP
105 (
106 P_PAYROLL_GROUP_ID NUMBER
107 ,P_PAYROLL_ID NUMBER
108 ,P_PAYROLL_GROUP_NAME VARCHAR2
109 )
110 RETURN NUMBER
111 IS
112 V_STATUS NUMBER;
113 V_PAYROLL_ID NUMBER;
114 BEGIN
115
116 V_STATUS:=0;
117 hr_utility.trace('V_STATUS:'||V_STATUS);
118 SELECT PGPGM.PAYROLL_ID INTO
119 V_PAYROLL_ID
120 FROM PAY_GB_PAYROLL_GROUP_MAPPINGS PGPGM
121 WHERE PGPGM.PAYROLL_ID=P_PAYROLL_ID
122 AND PGPGM.PAYROLL_GROUP_ID=P_PAYROLL_GROUP_ID;
123
124 V_STATUS:=1;
125 hr_utility.trace('V_STATUS:'||V_STATUS);
126 EXCEPTION WHEN NO_DATA_FOUND THEN
127 V_STATUS:=2;
128 RETURN V_STATUS;
129 END VAL_PAYROLLS_IN_GROUP;
130
131
132
133
134 FUNCTION VAL_MASTER_CONCURRENT_REQUEST
135 (
136 P_CONCURRENT_REQUEST_ID NUMBER
137 ,P_PAYROLL_GROUP_ID NUMBER
138 )
139 RETURN NUMBER
140 IS
141 V_STATUS NUMBER;
142 V_CONC_ID NUMBER;
143 BEGIN
144
145 V_STATUS:=1;
146 hr_utility.trace('V_STATUS:'||V_STATUS);
147 SELECT CONCURRENT_REQUEST_ID
148 INTO V_CONC_ID
149 FROM PAY_GB_MP_MASTER_REQUEST_DET PGPMMRD
150 WHERE PGPMMRD.CONCURRENT_REQUEST_ID=P_CONCURRENT_REQUEST_ID
151 AND PGPMMRD.PAYROLL_GROUP_ID=P_PAYROLL_GROUP_ID;
152
153 EXCEPTION WHEN NO_DATA_FOUND THEN
154 V_STATUS:=1;
155
156 RETURN V_STATUS;
157 END VAL_MASTER_CONCURRENT_REQUEST;
158
159 FUNCTION VAL_CHILD_CONCURRENT_REQUEST
160 (
161 P_CHILD_CONCURRENT_REQUEST_ID NUMBER
162 ,P_PARENT_CONC_REQUEST_ID NUMBER
163 )
164 RETURN NUMBER
165 IS
166 V_STATUS NUMBER;
167 V_CONC_ID NUMBER;
168
169 BEGIN
170
171
172 V_STATUS:=0;
173
174 SELECT PGMPCRD.CHILD_CONCURRENT_REQUEST_ID
175 INTO V_CONC_ID
176 FROM PAY_GB_MP_CHILD_REQUEST_DET PGMPCRD,
177 PAY_GB_MP_MASTER_REQUEST_DET PGMMRD
178 WHERE PGMPCRD.CHILD_CONCURRENT_REQUEST_ID=P_CHILD_CONCURRENT_REQUEST_ID
179 AND PGMPCRD.PARENT_CONCURRENT_REQUEST_ID=PGMMRD.CONCURRENT_REQUEST_ID
180 AND PGMMRD.CONCURRENT_REQUEST_ID=P_PARENT_CONC_REQUEST_ID;
181
182 EXCEPTION WHEN NO_DATA_FOUND THEN
183 V_STATUS:=1;
184 RETURN V_STATUS;
185 END VAL_CHILD_CONCURRENT_REQUEST;
186
187 /*
188 Procedure to create a payroll group
189 */
190 PROCEDURE CREATE_PAYROLL_GROUP
191 (
192 P_PAYROLL_GROUP_NAME IN VARCHAR2
193 ,P_FREQUENCY IN VARCHAR2
194 ,P_BUSINESS_GROUP_ID IN NUMBER
195 ,P_DESCRIPTION IN VARCHAR2
196 ,P_PAYROLL_GROUP_ID OUT NOCOPY NUMBER
197 ,P_RETURN_STATUS OUT NOCOPY VARCHAR2
198 )
199
200 IS
201
202 M_PAYROLL_GROUP_ID NUMBER;
203
204 BEGIN
205
206
207 M_PAYROLL_GROUP_ID:=PAY_GB_MULTI_DML_AND_VAL.GET_GROUP_MAX_ID;
208
209 hr_utility.trace('Payroll Group Id:'||M_PAYROLL_GROUP_ID);
210
211 IF VAL_PAYROLL_GROUPS(P_PAYROLL_GROUP_NAME,P_BUSINESS_GROUP_ID) =1 THEN
212
213 hr_utility.trace(' VAL_PAYROLL_GROUPS returned 1');
214 INSERT INTO PAY_GB_PAYROLL_GROUPS
215 (
216 PAYROLL_GROUP_ID
217 ,PAYROLL_GROUP_NAME
218 ,PAY_FREQUENCY
219 ,DESCRIPTION
220 ,BUSINESS_GROUP_ID
221 ,CREATION_DATE
222 ,OBJECT_VERSION_NUMBER
223 )
224 VALUES
225 (
226 M_PAYROLL_GROUP_ID
227 ,P_PAYROLL_GROUP_NAME
228 ,P_FREQUENCY
229 ,P_DESCRIPTION
230 ,P_BUSINESS_GROUP_ID
231 ,SYSDATE
232 ,1
233 );
234
235 commit;
236 P_PAYROLL_GROUP_ID := M_PAYROLL_GROUP_ID;
237 P_RETURN_STATUS := '1';
238
239 ELSE
240 P_PAYROLL_GROUP_ID := 0;
241 P_RETURN_STATUS := '0'; -- Means Name already exist
242 hr_utility.trace('Group Already Exists');
243 END IF;
244
245
246
247 hr_utility.trace('Insert Over');
248
249
250 END CREATE_PAYROLL_GROUP;
251
252
253
254 PROCEDURE ADD_PAYROLLS
255 (
256 P_PAYROLL_ID IN NUMBER
257 ,P_PAYROLL_GROUP_ID IN NUMBER
258 ,P_BUSINESS_GROUP_ID IN NUMBER
259 )
260 IS
261
262 V_PAYROLL_NAME VARCHAR2(100);
263 V_PAYROLL_GROUP_NAME VARCHAR2(100);
264 BEGIN
265
266
267 hr_utility.trace(P_PAYROLL_ID);
268 hr_utility.trace(P_PAYROLL_GROUP_ID);
269 hr_utility.trace(P_BUSINESS_GROUP_ID);
270
271 SELECT PAPF.PAYROLL_NAME INTO V_PAYROLL_NAME
272 FROM PAY_ALL_PAYROLLS_F PAPF
273 WHERE PAPF.PAYROLL_ID=P_PAYROLL_ID
274 AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
275 AND PAPF.BUSINESS_GROUP_ID=P_BUSINESS_GROUP_ID;
276
277 SELECT PGPG.PAYROLL_GROUP_NAME INTO V_PAYROLL_GROUP_NAME
278 FROM PAY_GB_PAYROLL_GROUPS PGPG
279 WHERE PGPG.PAYROLL_GROUP_ID=P_PAYROLL_GROUP_ID
280 AND PGPG.BUSINESS_GROUP_ID=P_BUSINESS_GROUP_ID;
281
282 hr_utility.trace('Payroll name is:'||V_PAYROLL_NAME);
283 IF VAL_PAYROLLS_IN_GROUP(P_PAYROLL_GROUP_ID,
284 P_PAYROLL_ID, V_PAYROLL_GROUP_NAME)=2 THEN
285 hr_utility.trace('VAL_PAYROLLS_IN_GROUP returns 1');
286 hr_utility.trace('P_BUSINESS_GROUP_ID:'||P_BUSINESS_GROUP_ID);
287 IF VALIDATE_FREQUENCY(P_PAYROLL_ID,P_PAYROLL_GROUP_ID)=1 THEN
288 INSERT INTO PAY_GB_PAYROLL_GROUP_MAPPINGS
289 (
290 PAYROLL_ID
291 ,PAYROLL_NAME
292 ,PAYROLL_GROUP_ID
293 ,BUSINESS_GROUP_ID
294 ,CREATION_DATE
295 ,OBJECT_VERSION_NUMBER
296 )
297 VALUES
298 (
299 P_PAYROLL_ID
300 ,V_PAYROLL_NAME
301 ,P_PAYROLL_GROUP_ID
302 ,P_BUSINESS_GROUP_ID
303 ,SYSDATE
304 ,1
305 );
306 END IF;
307 COMMIT;
308
309 hr_utility.trace('Insert over');
310 ELSE
311 hr_utility.set_message(801,'The payrol trying to add is not of same frequency off this group');
312 hr_utility.raise_error;
313 END IF;
314
315 END ADD_PAYROLLS;
316
317 PROCEDURE ADD_MASTER_CON_DETAILS
318 (
319 P_CONCURRENT_REQUEST_ID IN NUMBER
320 ,P_PAYROLL_GROUP_ID IN NUMBER
321 ,P_PROCESS_NAME IN VARCHAR2
322 ,P_REQUEST_PARAMETERS IN VARCHAR2
323 ,P_REQUEST_SUBMISSION_DATE IN DATE
324 )
325
326 IS
327
328 BEGIN
329
330 IF VAL_MASTER_CONCURRENT_REQUEST(P_CONCURRENT_REQUEST_ID,P_PAYROLL_GROUP_ID)=1 THEN
331
332 INSERT INTO PAY_GB_MP_MASTER_REQUEST_DET
333 (
334 CONCURRENT_REQUEST_ID
335 ,PAYROLL_GROUP_ID
336 ,PROCESS_NAME
337 ,REQUEST_PARAMETERS
338 ,REQUEST_SUBMISSION_DATE
339 ,OBJECT_VERSION_NUMBER
340 ,CREATION_DATE
341 )
342 VALUES
343 (
344 P_CONCURRENT_REQUEST_ID
345 ,P_PAYROLL_GROUP_ID
346 ,P_PROCESS_NAME
347 ,P_REQUEST_PARAMETERS
348 ,P_REQUEST_SUBMISSION_DATE
349 ,1
350 ,SYSDATE
351 );
352
353 COMMIT;
354 END IF;
355
356 END ADD_MASTER_CON_DETAILS;
357
358
359 PROCEDURE ADD_CHILD_CON_DETAILS
360 (
361 P_CHILD_CON_ID IN NUMBER
362 ,P_PARENT_CON_ID IN NUMBER
363 )
364
365 IS
366 BEGIN
367
368 IF VAL_CHILD_CONCURRENT_REQUEST(P_CHILD_CON_ID,P_PARENT_CON_ID)=1 THEN
369
370 INSERT INTO PAY_GB_MP_CHILD_REQUEST_DET
371 (
372 CHILD_CONCURRENT_REQUEST_ID
373 ,PARENT_CONCURRENT_REQUEST_ID
374 ,OBJECT_VERSION_NUMBER
375 ,CREATION_DATE
376 )
377 VALUES
378 (
379 P_CHILD_CON_ID
380 ,P_PARENT_CON_ID
381 ,1
382 ,SYSDATE
383
384 );
385
386 COMMIT;
387 END IF;
388 END ADD_CHILD_CON_DETAILS;
389
390
391
392
393 /*
394 PAY_GB_MP_MASTER_REQUEST_DET table updated
395 */
396 PROCEDURE UPDATE_MASTER_ROLLBACK_DETAILS
397 (
398 P_MASTER_REQUEST_ID IN NUMBER
399 ,P_PAYROLL_GROUP_ID IN NUMBER
400 ,P_ROLLBACK_REQUEST_ID IN NUMBER
401 )
402
403 IS
404 CURR_OBJ_VERS NUMBER;
405 BEGIN
406
407 SELECT OBJECT_VERSION_NUMBER INTO CURR_OBJ_VERS
408 FROM PAY_GB_MP_MASTER_REQUEST_DET
409 WHERE CONCURRENT_REQUEST_ID=P_MASTER_REQUEST_ID;
410 hr_utility.trace('object version is for master is:'||CURR_OBJ_VERS);
411
412 UPDATE PAY_GB_MP_MASTER_REQUEST_DET
413 SET
414 ROLLBACK_REQUEST_ID=P_ROLLBACK_REQUEST_ID
415 ,LAST_UPDATE_DATE=SYSDATE
416 ,OBJECT_VERSION_NUMBER=(CURR_OBJ_VERS+1)
417 WHERE CONCURRENT_REQUEST_ID=P_MASTER_REQUEST_ID;
418
419 COMMIT;
420 END UPDATE_MASTER_ROLLBACK_DETAILS;
421
422 PROCEDURE UPDATE_CHILD_ROLLBACK_DETAILS
423 (
424 P_MASTER_REQUEST_ID IN NUMBER
425 ,P_PAYROLL_GROUP_ID IN NUMBER
426 ,P_ROLLBACK_REQUEST_ID IN NUMBER
427 ,P_CHILD_REQUEST_ID IN NUMBER
428 )
429
430 IS
431
432 CURR_OBJ_VERS NUMBER;
433
434 BEGIN
435
436 SELECT OBJECT_VERSION_NUMBER INTO CURR_OBJ_VERS
437 FROM PAY_GB_MP_CHILD_REQUEST_DET
438 WHERE CHILD_CONCURRENT_REQUEST_ID=P_CHILD_REQUEST_ID
439 AND PARENT_CONCURRENT_REQUEST_ID=P_MASTER_REQUEST_ID;
440 hr_utility.trace('object version is for CHILD is:'||CURR_OBJ_VERS);
441
442 UPDATE PAY_GB_MP_CHILD_REQUEST_DET
443 SET
444 ROLLBACK_REQUEST_ID=P_ROLLBACK_REQUEST_ID
445 ,LAST_UPDATE_DATE=SYSDATE
446 ,OBJECT_VERSION_NUMBER=(CURR_OBJ_VERS+1)
447 WHERE PARENT_CONCURRENT_REQUEST_ID=P_MASTER_REQUEST_ID
448 AND CHILD_CONCURRENT_REQUEST_ID=P_CHILD_REQUEST_ID;
449
450 COMMIT;
451 END UPDATE_CHILD_ROLLBACK_DETAILS;
452
453
454
455 PROCEDURE DEL_PAYROLL_GROUP
456 (
457 P_PAYROLL_GROUP_ID IN NUMBER
458 ,P_BUSINESS_GROUP_ID IN NUMBER
459 )
460
461
462 IS
463 BEGIN
464
465
466
467 DELETE PAY_GB_PAYROLL_GROUP_MAPPINGS PGPGM
468 WHERE PGPGM.PAYROLL_GROUP_ID=P_PAYROLL_GROUP_ID
469 AND PGPGM.BUSINESS_GROUP_ID=P_BUSINESS_GROUP_ID;
470
471 DELETE PAY_GB_PAYROLL_GROUPS PGPG
472 WHERE PGPG.PAYROLL_GROUP_ID=P_PAYROLL_GROUP_ID
473 AND PGPG.BUSINESS_GROUP_ID=P_BUSINESS_GROUP_ID;
474 COMMIT;
475 END DEL_PAYROLL_GROUP;
476
477 PROCEDURE DELETE_PAYROLL_FROM_GRP
478 (
479 P_PAYROLL_ID IN NUMBER
480 ,P_PAYROLL_GROUP_ID IN NUMBER)
481
482 IS
483 BEGIN
484
485 DELETE PAY_GB_PAYROLL_GROUP_MAPPINGS
486 WHERE PAYROLL_ID=P_PAYROLL_ID
487 AND PAYROLL_GROUP_ID=P_PAYROLL_GROUP_ID;
488
489 COMMIT;
490
491 END DELETE_PAYROLL_FROM_GRP;
492
493 PROCEDURE UPDATE_ALREADY_ROLLEDBACK
494 (P_REQUEST_ID IN NUMBER)
495 IS
496
497 BEGIN
498
499
500 UPDATE PAY_GB_MP_CHILD_REQUEST_DET
501 SET ROLLBACK_REQUEST_ID=-1
502 WHERE CHILD_CONCURRENT_REQUEST_ID=P_REQUEST_ID;
503
504 COMMIT;
505
506 END UPDATE_ALREADY_ROLLEDBACK;
507
508
509 END PAY_GB_MULTI_DML_AND_VAL;