eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_GB_MULTI_PAYROLL_RUN

Source


1 PACKAGE BODY PAY_GB_MULTI_PAYROLL_RUN
2 /* $Header: pygbmps.pkb 120.0.12020000.2 2013/03/09 07:57:58 ssanjays noship $ */
3 AS
4 
5 
6 
7 
8 
9 /*
10 Procedure to update master and child record in series, ijn response to submit a process
11 like Pre-payments, BACS, Payslip generation, Retro
12 
13 */
14 PROCEDURE UPDATE_MASTER_CHILD(
15   P_CONCURRENT_REQUEST_ID IN NUMBER
16  ,P_PAYROLL_GROUP_ID IN NUMBER
17  ,P_PROCESS_NAME IN VARCHAR2
18  ,P_REQUEST_PARAMETERS IN VARCHAR2
19  ,P_REQUEST_SUBMISSION_DATE IN DATE
20  ,P_CHILD_REQ PAY_GB_MULTI_PAYROLL_RUN.CHILD_REQ)
21 IS
22 
23 BEGIN
24 
25 
26  PAY_GB_MULTI_DML_AND_VAL.ADD_MASTER_CON_DETAILS(
27 	  P_CONCURRENT_REQUEST_ID=>P_CONCURRENT_REQUEST_ID
28 	 ,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
29 	 ,P_PROCESS_NAME=>P_PROCESS_NAME
30 	 ,P_REQUEST_PARAMETERS=>P_REQUEST_PARAMETERS
31 	 ,P_REQUEST_SUBMISSION_DATE=>SYSDATE
32 	);
33 
34 
35 FOR P IN P_CHILD_REQ.FIRST..P_CHILD_REQ.LAST
36 LOOP
37  hr_utility.trace('Child request to be stored:'||P_CHILD_REQ(P));
38 		 PAY_GB_MULTI_DML_AND_VAL.ADD_CHILD_CON_DETAILS(
39 					 P_CHILD_CON_ID=>P_CHILD_REQ(P),P_PARENT_CON_ID =>P_CONCURRENT_REQUEST_ID);
40 END LOOP;
41 
42 END UPDATE_MASTER_CHILD;
43 
44 
45 FUNCTION GET_PERIOD_NAME(P_TIME_PERIOD_NAME IN VARCHAR, P_PAYROLL_ID IN NUMBER)
46 RETURN NUMBER
47 IS
48 
49 V_TIME_PERIOD_ID NUMBER;
50 BEGIN
51 
52 	SELECT PTP.TIME_PERIOD_ID INTO
53 			V_TIME_PERIOD_ID
54 			FROM PER_TIME_PERIODS PTP
55 			WHERE PTP.PERIOD_NAME=P_TIME_PERIOD_NAME
56 			AND   PTP.PAYROLL_ID=P_PAYROLL_ID;
57 
58 RETURN V_TIME_PERIOD_ID;
59 
60 END GET_PERIOD_NAME;
61 
62 /*
63 Procedure to Bulk Run for Payroll Run UK
64 */
65 
66 PROCEDURE RUN_PAYROLL_UK(
67   errbuf  OUT NOCOPY VARCHAR2
68  ,retcode  OUT NOCOPY NUMBER
69 ,P_PAYROLL_GROUP_ID IN NUMBER
70 ,P_TIME_PERIOD_NAME IN VARCHAR
71 ,P_ELEMENT_SET_NAME IN NUMBER
72 ,P_RUN_TYPE IN NUMBER
73 ,P_ACTION_PARAMETER_GROUP IN VARCHAR2
74 )
75 
76  IS
77 
78 V_CONDOLIDATION_SET_ID NUMBER;
79 V_REGULAR_PAY_DATE DATE;
80 V_END_DATE DATE;
81 V_RUN_TYPE_ID NUMBER;
82 L_REQ_ID NUMBER;
83 V_PERIOD_ID NUMBER;
84 PAR_REQ_ID NUMBER;
85 PAR_REQ_PARAMS VARCHAR2(240);
86 l_ARGUMENT7    VARCHAR2(240);
87 l_ARGUMENT9    VARCHAR2(240):=P_ACTION_PARAMETER_GROUP;
88 l_ARGUMENT10   VARCHAR2(240);
89 l_profile_value VARCHAR2(240);
90 
91 
92 T_CHILD_REQ CHILD_REQ;
93 REQ_COUNT NUMBER;
94 
95  BEGIN
96 
97      hr_utility.trace('Inside '||PAY_GB_MULTI_PAYROLL_RUN.g_package);
98           hr_utility.trace('P_RUN_TYPE:'||P_RUN_TYPE);
99 hr_utility.trace('P_TIME_PERIOD_NAME'||P_TIME_PERIOD_NAME);
100 
101 
102 REQ_COUNT:=0;
103  FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
104 
105  LOOP
106 
107      hr_utility.trace('Payroll_Id:'||PAY_RUN.PAYROLL_ID);
108      OPEN CSR_PAYROLL_PARAMETERS(PAY_RUN.PAYROLL_ID,P_TIME_PERIOD_NAME);
109           FETCH CSR_PAYROLL_PARAMETERS INTO V_CONDOLIDATION_SET_ID, V_REGULAR_PAY_DATE,V_END_DATE;
110           hr_utility.trace('V_CONDOLIDATION_SET--_V_REGULAR_PAY_DATE --V_END_DATE are:'||V_CONDOLIDATION_SET_ID||'-'||V_REGULAR_PAY_DATE||'-'||V_END_DATE);
111           hr_utility.trace('Payroll_id'||PAY_RUN.payroll_id);
112           hr_utility.trace('EARNED DATE:'||V_END_DATE);
113           hr_utility.trace('V_REGULAR_PAY_DATE:'||V_REGULAR_PAY_DATE);
114           hr_utility.trace('P_ELEMENT_SET_NAME:'||P_ELEMENT_SET_NAME);
115 
116 
117   					L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
118             			APPLICATION       =>       'PAY'
119 								, PROGRAM           =>       'PAYROLL_RUN_UK'
120 								, DESCRIPTION       =>       NULL
121 								, START_TIME        =>       SYSDATE
122 								, SUB_REQUEST       =>      FALSE
123 								, ARGUMENT1         =>      'RUN'
124 								, ARGUMENT2        =>        PAY_RUN.payroll_id
125 								, ARGUMENT3        =>        V_CONDOLIDATION_SET_ID
126 								, ARGUMENT4        =>        fnd_date.date_to_canonical(V_END_DATE)
127 								, ARGUMENT5        =>        fnd_date.date_to_canonical(V_REGULAR_PAY_DATE)
128 								, ARGUMENT6        =>        P_ELEMENT_SET_NAME
129 								, ARGUMENT7        =>        l_ARGUMENT7
130 								, ARGUMENT8        =>        TO_NUMBER(P_RUN_TYPE)
131 								, ARGUMENT9        =>        l_ARGUMENT9
132 								, ARGUMENT10       =>        l_ARGUMENT10
133 						);
134 
135           hr_utility.trace('Concurrent Request Submitted for Payroll:'||PAY_RUN.payroll_id||'-'||L_REQ_ID);
136 			CLOSE CSR_PAYROLL_PARAMETERS;
137 T_CHILD_REQ(REQ_COUNT):=L_REQ_ID;
138 hr_utility.trace('Child request copied is:'||T_CHILD_REQ(REQ_COUNT));
139 REQ_COUNT:=REQ_COUNT+1;
140  END LOOP;
141 
142 OPEN CSR_PARENT_REQ_ID(L_REQ_ID);
143   FETCH CSR_PARENT_REQ_ID INTO PAR_REQ_ID, PAR_REQ_PARAMS;
144 CLOSE CSR_PARENT_REQ_ID;
145 
146 PAR_REQ_PARAMS:=PAR_REQ_PARAMS ||',ROLLBACK_PARAM=' ||P_TIME_PERIOD_NAME;
147 hr_utility.trace('Master request details stored are:'||PAR_REQ_ID||'-'||PAR_REQ_PARAMS);
148 
149 /*
150 Storing into the master and child tables
151 */
152 
153 --ADDING MASTER DETAILS
154 /*
155 Call to update the master and child record
156 */
157 
158   PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
159  		 P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
160 	  ,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
161 	  ,P_PROCESS_NAME =>'Payroll Run UK Multiple'
162 	  ,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
163 	  ,P_REQUEST_SUBMISSION_DATE =>SYSDATE
164     ,P_CHILD_REQ =>T_CHILD_REQ);
165 
166 hr_utility.trace('after inserting into master and child records:');
167 
168  END RUN_PAYROLL_UK;
169 
170 
171 /*
172 Procedure to Run Bulk Prepayments
173 
174 */
175 PROCEDURE RUN_PREPAYMENTS_UK(
176   ERRBUF  OUT NOCOPY VARCHAR2
177  ,RETCODE  OUT NOCOPY NUMBER
178 ,P_PAYROLL_GROUP_ID IN NUMBER
179 ,P_START_DATE IN VARCHAR2
180 ,P_END_DATE IN VARCHAR2
181 ,P_PAYMENT_OVERRIDE_Y_N IN VARCHAR
182 ,P_PAYMENT_OVERRIDE_NAME IN VARCHAR
183 ,P_ACTION_PARAMETER_GROUP IN VARCHAR2
184 )
185 
186 IS
187 
188 L_REQ_ID NUMBER;
189 V_CONDOLIDATION_SET_ID NUMBER;
190 V_REGULAR_PAY_DATE DATE;
191 V_END_DATE DATE;
192 V_RUN_TYPE_ID      NUMBER;
193 l_ARGUMENT7        VARCHAR2(240);
194 l_ARGUMENT9        VARCHAR2(240);
195 l_ARGUMENT8        VARCHAR2(240);
196 PAR_REQ_ID NUMBER;
197 PAR_REQ_PARAMS VARCHAR2(240);
198 
199 
200 CURSOR CSR_DEF_PAY_METHOD (P_PAYROLL_ID IN NUMBER) IS
201 SELECT DISTINCT POPMF.ORG_PAYMENT_METHOD_NAME
202 FROM
203 PAY_ALL_PAYROLLS_F PAPF,
204 PAY_ORG_PAYMENT_METHODS_F POPMF
205 WHERE PAPF.DEFAULT_PAYMENT_METHOD_ID=POPMF.ORG_PAYMENT_METHOD_ID
206 AND   PAPF.BUSINESS_GROUP_ID=POPMF.BUSINESS_GROUP_ID
207 AND   PAPF.PAYROLL_ID=P_PAYROLL_ID;
208 
209 
210 
211 CURSOR CSR_GET_CONS_ID(P_PAYROLL_ID IN NUMBER)
212 IS
213 SELECT CONSOLIDATION_SET_ID
214 FROM PAY_ALL_PAYROLLS_F
215 WHERE PAYROLL_ID=P_PAYROLL_ID;
216 
217 T_CHILD_REQ CHILD_REQ;
218 REQ_COUNT NUMBER;
219 L_END_DATE DATE;
220 V_PAYMENT_OVERRIDE_NAME VARCHAR2(100);
221 
222 BEGIN
223 
224 
225 
226 		      hr_utility.trace('Inside:'||PAY_GB_MULTI_PAYROLL_RUN.g_package);
227 				  hr_utility.trace('P_START_DATE'||P_START_DATE);
228 					hr_utility.trace('P_END_DATE'||P_END_DATE);
229 					hr_utility.trace('P_PAYMENT_METHOD_OVERRIDE:'||P_PAYMENT_OVERRIDE_Y_N);
230 
231 
232 REQ_COUNT:=0;
233 
234 
235 IF P_PAYMENT_OVERRIDE_Y_N='Yes' THEN
236    V_PAYMENT_OVERRIDE_NAME:=P_PAYMENT_OVERRIDE_NAME;
237 END IF;
238 
239 
240  FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
241  LOOP
242 
243    hr_utility.trace('Payroll_Id:'||PAY_RUN.PAYROLL_ID);
244    OPEN CSR_GET_CONS_ID(PAY_RUN.PAYROLL_ID);
245                  FETCH CSR_GET_CONS_ID INTO V_CONDOLIDATION_SET_ID;
246 
247 					L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
248  	           			APPLICATION       =>       'PAY'
249 								, PROGRAM           =>       'PREPAY'
250 								, DESCRIPTION       =>       NULL
251 								, START_TIME        =>       SYSDATE
252 								, SUB_REQUEST        =>      FALSE
253 								, ARGUMENT1          =>      'PREPAY'
254 								, ARGUMENT2        =>        PAY_RUN.payroll_id
255 								, ARGUMENT3        =>        V_CONDOLIDATION_SET_ID
256 								, ARGUMENT4        =>        P_START_DATE
257 								, ARGUMENT5        =>        P_END_DATE
258 								, ARGUMENT6        =>        V_PAYMENT_OVERRIDE_NAME
259                 , ARGUMENT7        =>        P_ACTION_PARAMETER_GROUP
260 						);
261 
262       hr_utility.trace('Leaving one process: request id: is'||L_REQ_ID);
263      CLOSE CSR_GET_CONS_ID;
264 T_CHILD_REQ(REQ_COUNT):=L_REQ_ID;
265 hr_utility.trace('Child request copied is:'||T_CHILD_REQ(REQ_COUNT));
266 REQ_COUNT:=REQ_COUNT+1;
267   END LOOP;
268 
269 OPEN CSR_PARENT_REQ_ID(L_REQ_ID);
270   FETCH CSR_PARENT_REQ_ID INTO PAR_REQ_ID, PAR_REQ_PARAMS;
271 CLOSE CSR_PARENT_REQ_ID;
272 
273 hr_utility.trace('Master request details stored are:'||PAR_REQ_ID||'-'||PAR_REQ_PARAMS);
274 
275 /*
276 Storing into the master and child tables
277 */
278 /*
279 procedure call
280 */
281 
282 PAR_REQ_PARAMS:=PAR_REQ_PARAMS || 'ROLLBACK_PARAM=' ||P_END_DATE;
283 
284   PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
285  		 P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
286 	  ,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
287 	  ,P_PROCESS_NAME =>'Pre-payments'
288 	  ,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
289 	  ,P_REQUEST_SUBMISSION_DATE =>SYSDATE
290     ,P_CHILD_REQ =>T_CHILD_REQ);
291 --ADDING MASTER DETAILS
292 
293 hr_utility.trace('Child records also updated successfully');
294 
295 END RUN_PREPAYMENTS_UK;
296 
297 
298 
299 /*
300 Bulk BACS submission
301 */
302 PROCEDURE RUN_BACS_UK(
303   ERRBUF  OUT NOCOPY VARCHAR2
304  ,RETCODE  OUT NOCOPY NUMBER
305  ,P_PAYROLL_GROUP_ID IN NUMBER
306  ,P_START_DATE IN VARCHAR
307  ,P_END_DATE IN VARCHAR
308  ,P_PAYMENT_TYPE IN VARCHAR2
309  ,P_PAYMENT_METHOD IN VARCHAR2
310  ,P_PROCESS_DATE IN VARCHAR
311  ,P_ACTION_PARAMETER_GROUP IN VARCHAR2
312  ,P_SORT_SEQUENCE IN VARCHAR2
313  ,P_SUBMISSION_NUMBER IN NUMBER
314  ,P_EXPIRATION_DATE IN VARCHAR
315  ,P_AUDIT_SAM_FREQ IN NUMBER
316  ,P_B_REPORT_TYPE IN VARCHAR2
317  ,P_B_FORMAT_TYPE IN VARCHAR2
318 )
319 IS
320 
321 L_REQ_ID NUMBER;
322 V_SUBMISSION_NUMBER_HID VARCHAR2(200);
323 V_EXPIR_DATE_HID VARCHAR2(300);
324 V_AUDIT_SAMPLE_HID VARCHAR2(50);
325 V_CONDOLIDATION_SET_ID NUMBER;
326 PAR_REQ_ID NUMBER;
327 PAR_REQ_PARAMS VARCHAR2(240);
328 T_CHILD_REQ CHILD_REQ;
329 REQ_COUNT NUMBER;
330 
331 CURSOR CSR_GET_CONS_ID(P_PAYROLL_ID IN NUMBER)
332 IS
333 SELECT CONSOLIDATION_SET_ID
334 FROM PAY_ALL_PAYROLLS_F
335 WHERE PAYROLL_ID=P_PAYROLL_ID;
336 
337 BEGIN
338 
339 		      hr_utility.trace('Inside:RUN_BACS_UK');
340 					hr_utility.trace('P_PAYROLL_GROUP_NAME:'||P_PAYROLL_GROUP_ID);
341 				  hr_utility.trace('P_START_DATE'||P_START_DATE);
342 					hr_utility.trace('P_END_DATE'||P_END_DATE);
343 					hr_utility.trace('P_PAYMENT_TYPE:'||'BACS Tape');
344 					hr_utility.trace('P_PAYMENT_METHOD:'||P_PAYMENT_METHOD);
345 					hr_utility.trace('P_PROCESS_DATE:'||P_PROCESS_DATE);
346 					hr_utility.trace('P_ACTION_PARAMETER_GROUP:'||P_ACTION_PARAMETER_GROUP);
347 					hr_utility.trace('P_SORT_SEQUENCE:'||P_SORT_SEQUENCE);
348 					hr_utility.trace('P_SUBMISSION_NUMBER:'||P_SUBMISSION_NUMBER);
349 					hr_utility.trace('P_EXPIRATION_DATE:'||P_EXPIRATION_DATE);
350 					hr_utility.trace('P_AUDIT_SAM_FREQ:'||P_AUDIT_SAM_FREQ);
351 					hr_utility.trace('P_B_REPORT_TYPE:'||P_B_REPORT_TYPE);
352 					hr_utility.trace('P_B_FORMAT_TYPE:'||P_B_FORMAT_TYPE);
353 
354 
355 REQ_COUNT:=0;
356  FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
357  LOOP
358 
359   V_AUDIT_SAMPLE_HID:='TRANSFER_SELECT_COUNT='||lpad(nvl(P_AUDIT_SAM_FREQ,'0'),4,'0');
360 	V_EXPIR_DATE_HID:='TRANSFER_EXPIRY_DATE='||to_char(to_date('2012/12/12', 'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS');
361   V_SUBMISSION_NUMBER_HID:='SUBMISSION_NUMBER='||P_SUBMISSION_NUMBER;
362 
363 					hr_utility.trace('V_AUDIT_SAMPLE_HID:'||V_AUDIT_SAMPLE_HID);
364 					hr_utility.trace('V_EXPIR_DATE_HID:'||V_EXPIR_DATE_HID);
365 					hr_utility.trace('V_SUBMISSION_NUMBER_HID:'||V_SUBMISSION_NUMBER_HID);
366 
367  OPEN CSR_GET_CONS_ID(PAY_RUN.payroll_id);
368    FETCH CSR_GET_CONS_ID INTO V_CONDOLIDATION_SET_ID;
369 
370 					hr_utility.trace('V_CONDOLIDATION_SET_ID:'||V_CONDOLIDATION_SET_ID);
371 
372 					L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
373  	           			APPLICATION       =>       'PAY'
374 								, PROGRAM           =>       'BACS'
375 								, DESCRIPTION       =>       NULL
376 								, START_TIME        =>       SYSDATE
380 								, ARGUMENT3        =>        V_CONDOLIDATION_SET_ID
377 								, SUB_REQUEST        =>      FALSE
378 								, ARGUMENT1          =>      'MAGTAPE'
379 								, ARGUMENT2        =>        PAY_RUN.payroll_id
381 								, ARGUMENT4        =>        P_START_DATE
382 								, ARGUMENT5        =>        P_END_DATE
383 								, ARGUMENT6        =>        P_PAYMENT_TYPE
384                 , ARGUMENT7        =>        P_PAYMENT_METHOD
385 								, ARGUMENT8        =>        P_PROCESS_DATE
386 								, ARGUMENT9        =>        P_ACTION_PARAMETER_GROUP
387 								, ARGUMENT10        =>       P_SORT_SEQUENCE
388 								, ARGUMENT11        =>       P_SUBMISSION_NUMBER
389                 , ARGUMENT12        =>       V_SUBMISSION_NUMBER_HID
390 								, ARGUMENT13          =>     P_EXPIRATION_DATE
391 								, ARGUMENT14        =>       V_EXPIR_DATE_HID
392 								, ARGUMENT15       =>        P_AUDIT_SAM_FREQ
393 								, ARGUMENT16        =>       V_AUDIT_SAMPLE_HID
394 								, ARGUMENT17        =>       P_B_REPORT_TYPE
395 								, ARGUMENT18       =>        P_B_FORMAT_TYPE
396 						);
397  CLOSE CSR_GET_CONS_ID;
398 T_CHILD_REQ(REQ_COUNT):=L_REQ_ID;
399 hr_utility.trace('Child request copied is:'||T_CHILD_REQ(REQ_COUNT));
400 REQ_COUNT:=REQ_COUNT+1;
401  END LOOP;
402 
403 
404 OPEN CSR_PARENT_REQ_ID(L_REQ_ID);
405   FETCH CSR_PARENT_REQ_ID INTO PAR_REQ_ID, PAR_REQ_PARAMS;
406 CLOSE CSR_PARENT_REQ_ID;
407 
408 PAR_REQ_PARAMS:=PAR_REQ_PARAMS ||'ROLLBACK_PARAM=' ||P_END_DATE;
409 
410   PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
411  		 P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
412 	  ,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
413 	  ,P_PROCESS_NAME =>'Magnetic transfer'
414 	  ,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
415 	  ,P_REQUEST_SUBMISSION_DATE =>SYSDATE
416     ,P_CHILD_REQ =>T_CHILD_REQ);
417 
418 END RUN_BACS_UK;
419 
420 
421 
422 /*
423 Run Bulk Cheque Writer Process
424 */
425 PROCEDURE RUN_CHEQUE_WRITER_UK(
426   ERRBUF  OUT NOCOPY VARCHAR2
427  ,RETCODE  OUT NOCOPY NUMBER
428 ,P_PAYROLL_GROUP_ID IN NUMBER
429  ,P_START_DATE IN VARCHAR2
430  ,P_END_DATE IN VARCHAR2
431  ,P_PAYMENT_TYPE IN VARCHAR2
432  ,P_PAYMENT_METHOD IN VARCHAR2
433  ,P_SORT_SEQUENCE IN VARCHAR2
434  ,P_CHEQUE_STYLE IN VARCHAR2
435  ,P_CHEQUE_START_NUMBER IN NUMBER
436  ,P_CHEQUE_END_NUMBER IN NUMBER
437  ,P_ACTION_PARAMETER_GROUP IN VARCHAR2
438 )
439 
440 IS
441 
442 L_REQ_ID NUMBER;
443 V_CONDOLIDATION_SET_ID NUMBER;
444 V_START_CHEQUE_NUMBER NUMBER;
445 T_START_CHEQUE_NUMBER NUMBER;
446 PAR_REQ_ID NUMBER;
447 PAR_REQ_PARAMS VARCHAR2(240);
448 T_CHILD_REQ CHILD_REQ;
449 REQ_COUNT NUMBER;
450 /*
451 CURSOR
452 */
453 CURSOR CSR_NEXT_CHEQUE_SEQUENCE (P_PAYROLL_ID IN NUMBER)
454 IS
455 SELECT COUNT(*)
456 FROM
457 PAY_PRE_PAYMENTS PPP
458 ,PAY_ASSIGNMENT_ACTIONS PAA
459 ,PER_ALL_ASSIGNMENTS_F PAAF
460 ,PAY_ORG_PAYMENT_METHODS_F POPMF
461 ,PER_TIME_PERIODS PTP
462 WHERE PPP.ASSIGNMENT_ACTION_ID=PAA.ASSIGNMENT_ACTION_ID
463 AND   PAA.ASSIGNMENT_ID=PAAF.ASSIGNMENT_ID
464 AND   PAAF.PAYROLL_ID=PTP.PAYROLL_ID
465 AND   PAAF.PAYROLL_ID=P_PAYROLL_ID
466 AND   PTP.START_DATE>=FND_DATE.CANONICAL_TO_DATE(P_START_DATE)
467 AND   PTP.END_DATE<=FND_DATE.CANONICAL_TO_DATE(P_END_DATE)
468 AND   POPMF.ORG_PAYMENT_METHOD_NAME='Cheque'
469 AND   PPP.ORG_PAYMENT_METHOD_ID=POPMF.ORG_PAYMENT_METHOD_ID;
470 
471 
472 CURSOR CSR_GET_CONS_ID(P_PAYROLL_ID IN NUMBER)
473 IS
474 SELECT CONSOLIDATION_SET_ID
475 FROM PAY_ALL_PAYROLLS_F
476 WHERE PAYROLL_ID=P_PAYROLL_ID;
477 
478 BEGIN
479 REQ_COUNT:=0;
480 
481 V_START_CHEQUE_NUMBER:=P_CHEQUE_START_NUMBER;
482 
483 
484 hr_utility.trace('P_START_DATE:'||P_START_DATE);
485  FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
486  LOOP
487   OPEN CSR_GET_CONS_ID(PAY_RUN.payroll_id);
488     FETCH CSR_GET_CONS_ID INTO V_CONDOLIDATION_SET_ID;
489 
490 
491   hr_utility.trace('Start cheque number for payrollL'||PAY_RUN.payroll_id||V_START_CHEQUE_NUMBER);
492 
493 
494 
495 
496               L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
497  	           			APPLICATION       =>       'PAY'
498 								, PROGRAM           =>       'CHEQUE_WRITER'
499 								, DESCRIPTION       =>       NULL
500 								, START_TIME        =>       SYSDATE
501 								, SUB_REQUEST        =>      FALSE
502 								, ARGUMENT1          =>      'CHEQUE'
503 								, ARGUMENT2        =>        PAY_RUN.payroll_id
504 								, ARGUMENT3        =>        V_CONDOLIDATION_SET_ID
505 								, ARGUMENT4        =>        P_START_DATE
506 								, ARGUMENT5        =>        P_END_DATE
507 								, ARGUMENT6        =>        P_PAYMENT_TYPE
508                 , ARGUMENT7        =>        P_PAYMENT_METHOD
509 								, ARGUMENT8        =>        P_SORT_SEQUENCE
510 								, ARGUMENT9        =>        P_CHEQUE_STYLE
511 								, ARGUMENT10        =>       V_START_CHEQUE_NUMBER
512 								, ARGUMENT11        =>       P_CHEQUE_END_NUMBER
513                 , ARGUMENT12        =>       P_ACTION_PARAMETER_GROUP
514 						);
515    CLOSE CSR_GET_CONS_ID;
516     hr_utility.trace('V_CONDOLIDATION_SET_ID:'||V_CONDOLIDATION_SET_ID);
517     hr_utility.trace('request_id:'||L_REQ_ID);
518    T_CHILD_REQ(REQ_COUNT):=L_REQ_ID;
519     hr_utility.trace('REQ_COUNT:'||REQ_COUNT);
520     REQ_COUNT:=REQ_COUNT+1;
521     hr_utility.trace('REQ_COUNT:'||REQ_COUNT);
522    --hr_utility.trace('Child request copied is:'||T_CHILD_REQ(REQ_COUNT));
523     OPEN  CSR_NEXT_CHEQUE_SEQUENCE(PAY_RUN.payroll_id);
524       FETCH CSR_NEXT_CHEQUE_SEQUENCE INTO T_START_CHEQUE_NUMBER;
525     CLOSE CSR_NEXT_CHEQUE_SEQUENCE;
526 
530  END LOOP;
527 V_START_CHEQUE_NUMBER:=V_START_CHEQUE_NUMBER+T_START_CHEQUE_NUMBER;
528 hr_utility.trace('V_START_CHEQUE_NUMBER:'||V_START_CHEQUE_NUMBER);
529 
531 hr_utility.trace('Loop over');
532 OPEN CSR_PARENT_REQ_ID(L_REQ_ID);
533   FETCH CSR_PARENT_REQ_ID INTO PAR_REQ_ID, PAR_REQ_PARAMS;
534 CLOSE CSR_PARENT_REQ_ID;
535 
536 
537 PAR_REQ_PARAMS:=PAR_REQ_PARAMS ||'ROLLBACK_PARAM=' ||P_END_DATE;
538 
539   PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
540  		 P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
541 	  ,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
542 	  ,P_PROCESS_NAME =>'Cheque writer'
543 	  ,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
544 	  ,P_REQUEST_SUBMISSION_DATE =>SYSDATE
545     ,P_CHILD_REQ =>T_CHILD_REQ);
546 
547 
548 
549 END RUN_CHEQUE_WRITER_UK;
550 
551 
552 
553 /*
554 Run Bulk Self Service PaySlip Generation
555 */
556 PROCEDURE RUN_SS_PAY_SLIP_UK(
557 
558  ERRBUF  OUT NOCOPY VARCHAR2
559 ,RETCODE  OUT NOCOPY NUMBER
560 ,P_START_DATE IN VARCHAR
561 ,P_EFFECTIVE_DATE IN VARCHAR
562 ,P_BUSINESS_GROUP_ID IN NUMBER
563 ,P_ACTION_PARAMETER_GROUP IN VARCHAR2
564 ,P_END_DATE IN VARCHAR2
565 ,P_PAYROLL_GROUP_ID IN NUMBER
566 ,P_ARCHIVE_START_DATE IN VARCHAR2
567 ,P_ARCHIVE_END_DATE IN VARCHAR
568 ,P_ARCHIVE_START_DATE_HIDDEN IN VARCHAR2
569 ,P_ARCHIVE_END_DATE_HIDDEN IN VARCHAR2
570 )
571 
572 
573 IS
574 
575 V_CONDOLIDATION_SET_ID NUMBER;
576 L_REQ_ID NUMBER;
577 V_CONSOLIDATION_SET_NAME VARCHAR2(300);
578 V_CONSOLIDATION_SET_NAME_HID VARCHAR2(300);
579 V_CONSOLIDATION_SET_ID NUMBER;
580 V_MAG_FILE_NAME VARCHAR2(30);
581 V_REP_FILE_NAME VARCHAR2(30);
582 V_PAYROLL_ID_HID varchar2(200);
583 PAR_REQ_ID NUMBER;
584 PAR_REQ_PARAMS VARCHAR2(240);
585 T_CHILD_REQ CHILD_REQ;
586 REQ_COUNT NUMBER;
587 
588 CURSOR CSR_CONS_DET (P_PAYROLL_ID IN NUMBER)
589 IS
590 
591 SELECT PCS.CONSOLIDATION_SET_ID FROM
592 PAY_ALL_PAYROLLS_F PAPF,
593 PAY_CONSOLIDATION_SETS PCS
594 WHERE PAPF.CONSOLIDATION_SET_ID=PCS.CONSOLIDATION_SET_ID
595 AND   PAPF.PAYROLL_ID=P_PAYROLL_ID;
596 
597 BEGIN
598 
599 
600 
601     hr_utility.trace('P_PAYROLL_GROUP_NAME:'||P_PAYROLL_GROUP_ID);
602     hr_utility.trace('P_START_DATE:'||P_START_DATE);
603     hr_utility.trace('P_EFFECTIVE_DATE:'||P_EFFECTIVE_DATE);
604     hr_utility.trace('P_BUSINESS_GROUP_ID:'||P_BUSINESS_GROUP_ID);
605     hr_utility.trace('P_MAG_FILE_NAME:'||V_MAG_FILE_NAME);
606     hr_utility.trace('P_REP_FILE_NAME:'||V_REP_FILE_NAME);
607     hr_utility.trace('P_ACTION_PARAMETER_GROUP:'||P_ACTION_PARAMETER_GROUP);
608     hr_utility.trace('P_END_DATE:'||P_END_DATE);
609     hr_utility.trace('P_ARCHIVE_START_DATE:'||P_ARCHIVE_START_DATE);
610     hr_utility.trace('P_ARCHIVE_END_DATE:'||P_ARCHIVE_END_DATE);
611     hr_utility.trace('P_ARCHIVE_START_DATE_HIDDEN:'||P_ARCHIVE_START_DATE_HIDDEN);
612     hr_utility.trace('P_ARCHIVE_END_DATE_HIDDEN:'||P_ARCHIVE_END_DATE_HIDDEN);
613     hr_utility.trace('P_PAYROLL_ID_HID:'|| V_PAYROLL_ID_HID);
614     hr_utility.trace('V_CONSOLIDATION_SET_NAME:'||V_CONSOLIDATION_SET_NAME);
615 
616 REQ_COUNT:=0;
617  FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
618  LOOP
619   OPEN CSR_CONS_DET(PAY_RUN.PAYROLL_ID);
620        FETCH CSR_CONS_DET INTO V_CONSOLIDATION_SET_ID;
621              V_PAYROLL_ID_HID:='PAYROLL='||PAY_RUN.PAYROLL_ID;
622   					 V_CONSOLIDATION_SET_NAME_HID:='CONSOLIDATION='||V_CONSOLIDATION_SET_ID;
623         hr_utility.trace('Payroll Id is:'||PAY_RUN.PAYROLL_ID);
624         hr_utility.trace('Payroll Id is:'||V_PAYROLL_ID_HID);
625         hr_utility.trace('V_CONSOLIDATION_SET_NAME_HID:'||V_CONSOLIDATION_SET_NAME_HID);
626 
627        L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
628  	           			APPLICATION       =>       'PAY'
629 								, PROGRAM           =>       'UKPSGEN'
630 								, DESCRIPTION       =>       NULL
631 								, START_TIME        =>       SYSDATE
632 								, SUB_REQUEST        =>      FALSE
633 								, ARGUMENT1          =>      'ARCHIVE'
634 								, ARGUMENT2        =>        'UKPS'
635 								, ARGUMENT3        =>        'GB'
636 								, ARGUMENT4        =>         P_START_DATE
637 								, ARGUMENT5        =>         P_EFFECTIVE_DATE
638 								, ARGUMENT6        =>        'ARCHIVE'
639                 , ARGUMENT7        =>         P_BUSINESS_GROUP_ID
640                 , ARGUMENT8        =>         V_MAG_FILE_NAME
641                 , ARGUMENT9        =>         V_REP_FILE_NAME
642                 , ARGUMENT10        =>        P_ACTION_PARAMETER_GROUP
643                 , ARGUMENT11        =>        P_END_DATE
644                 , ARGUMENT12        =>        PAY_RUN.PAYROLL_ID
645                 , ARGUMENT13        =>        V_CONSOLIDATION_SET_ID
646                 , ARGUMENT14        =>        P_ARCHIVE_START_DATE
647                 , ARGUMENT15        =>        P_ARCHIVE_END_DATE
648                 , ARGUMENT16        =>        PAY_RUN.PAYROLL_ID
649                 , ARGUMENT17        =>        V_CONSOLIDATION_SET_NAME_HID
650                 , ARGUMENT18        =>        P_ARCHIVE_START_DATE_HIDDEN
651                 , ARGUMENT19        =>        P_ARCHIVE_END_DATE_HIDDEN
652 						);
653 
654 
655   CLOSE CSR_CONS_DET;
656   hr_utility.trace('Request Id: for payroll: '|| PAY_RUN.payroll_id ||' is:' ||L_REQ_ID);
657    T_CHILD_REQ(REQ_COUNT):=L_REQ_ID;
658    REQ_COUNT:=REQ_COUNT+1;
659   hr_utility.trace('REQ_COUNT:'||REQ_COUNT);
660  END LOOP;
661 OPEN CSR_PARENT_REQ_ID(L_REQ_ID);
662   FETCH CSR_PARENT_REQ_ID INTO PAR_REQ_ID, PAR_REQ_PARAMS;
663 CLOSE CSR_PARENT_REQ_ID;
664 
665 PAR_REQ_PARAMS:=PAR_REQ_PARAMS || 'ROLLBACK_PARAM=' ||P_END_DATE;
666 
667   PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
668  		 P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
672 	  ,P_REQUEST_SUBMISSION_DATE =>SYSDATE
669 	  ,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
670 	  ,P_PROCESS_NAME =>'Magnetic report'
671 	  ,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
673     ,P_CHILD_REQ =>T_CHILD_REQ);
674 
675 
676 END RUN_SS_PAY_SLIP_UK;
677 
678 
679 
680 
681 
682 PROCEDURE RUN_RETRO_ENHANCED(
683   ERRBUF  OUT NOCOPY VARCHAR2
684  ,RETCODE  OUT NOCOPY NUMBER
685  ,P_ELEMENT_SET IN VARCHAR2
686  ,P_START_DATE IN VARCHAR2
687  ,P_EFFECTIVE_DATE IN VARCHAR2
688  ,P_ACTION_PARAMETER_GROUP IN VARCHAR2
689  ,P_RETRO_PAY_DEF IN VARCHAR2
690  ,P_PAYROLL_GROUP_ID IN NUMBER
691  ,P_ENTRY_CREATION_DATE IN VARCHAR2
692 )
693 IS
694 
695 L_REQ_ID NUMBER;
696 V_ASSIGNMENT_SET NUMBER;
697 PAR_REQ_ID NUMBER;
698 PAR_REQ_PARAMS VARCHAR2(240);
699 T_CHILD_REQ CHILD_REQ;
700 REQ_COUNT NUMBER;
701 BEGIN
702 REQ_COUNT:=0;
703 
704 		hr_utility.trace('P_PAYROLL_GROUP_NAME'||P_PAYROLL_GROUP_ID);
705 		hr_utility.trace('P_ELEMENT_SET'||P_ELEMENT_SET);
706 		hr_utility.trace('P_START_DATE'||P_START_DATE);
707 		hr_utility.trace('P_EFFECTIVE_DATE'||P_EFFECTIVE_DATE);
708 		hr_utility.trace('P_ACTION_PARAMETER_GROUPL_GROUP_NAME'||P_ACTION_PARAMETER_GROUP);
709 		hr_utility.trace('P_RETRO_PAY_DEF'||P_RETRO_PAY_DEF);
710 		hr_utility.trace('P_ENTRY_CREATION_DATE'||P_ENTRY_CREATION_DATE);
711 
712 FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
713 LOOP
714 
715 				L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
716  	           			APPLICATION      =>       'PAY'
717 								, PROGRAM          =>       'RETROENH'
718 								, DESCRIPTION      =>       NULL
719 								, START_TIME       =>       SYSDATE
720 								, SUB_REQUEST      =>       FALSE
721 								, ARGUMENT1        =>       'RETROELEMENT'
722 								, ARGUMENT2        =>       V_ASSIGNMENT_SET
723 								, ARGUMENT3        =>       P_ELEMENT_SET
724 								, ARGUMENT4        =>       P_START_DATE
725 								, ARGUMENT5        =>       P_EFFECTIVE_DATE
726 								, ARGUMENT6        =>       P_ACTION_PARAMETER_GROUP
727 								, ARGUMENT7        =>       P_RETRO_PAY_DEF
728 								, ARGUMENT8        =>       PAY_RUN.payroll_id
729 								, ARGUMENT9        =>       P_ENTRY_CREATION_DATE
730 								);
731 
732    T_CHILD_REQ(REQ_COUNT):=L_REQ_ID;
733 
734    REQ_COUNT:=REQ_COUNT+1;
735 END LOOP;
736 
737 OPEN CSR_PARENT_REQ_ID(L_REQ_ID);
738   FETCH CSR_PARENT_REQ_ID INTO PAR_REQ_ID, PAR_REQ_PARAMS;
739 CLOSE CSR_PARENT_REQ_ID;
740 
741 
742 PAR_REQ_PARAMS:=PAR_REQ_PARAMS ||'ROLLBACK_PARAM=' ||P_START_DATE;
743   PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
744  		 P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
745 	  ,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
746 	  ,P_PROCESS_NAME =>'Retropay by Element'
747 	  ,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
748 	  ,P_REQUEST_SUBMISSION_DATE =>SYSDATE
749     ,P_CHILD_REQ =>T_CHILD_REQ);
750 
751 END RUN_RETRO_ENHANCED;
752 
753 /*
754 Tax Payments Listing Report
755 */
756 
757 PROCEDURE RUN_TPL_MULTI(
758   ERRBUF  OUT NOCOPY VARCHAR2
759  ,RETCODE  OUT NOCOPY NUMBER
760  ,P_BUSINESS_GROUP_ID IN NUMBER
761  ,P_PAYROLL_GROUP_ID IN NUMBER
762  ,P_TIME_PERIOD_NAME IN VARCHAR2
763  ,P_SORT_ORDER IN VARCHAR2
764  ,P_SUMMARY_ONLY IN VARCHAR2
765 
766 )
767 IS
768 
769 L_REQ_ID NUMBER;
770 V_CONDOLIDATION_SET_ID NUMBER;
771 V_TIME_PERIOD_ID NUMBER;
772 
773 CURSOR CSR_GET_CONS_ID(P_PAYROLL_ID IN NUMBER)
774 IS
775 SELECT CONSOLIDATION_SET_ID
776 FROM PAY_ALL_PAYROLLS_F
777 WHERE PAYROLL_ID=P_PAYROLL_ID;
778 
779 
780 
781 BEGIN
782 
783 		hr_utility.trace('Start of TPL Multiple Report');
784 		hr_utility.trace('P_PAYROLL_GROUP_NAME'||P_PAYROLL_GROUP_ID);
785 		hr_utility.trace('P_TIME_PERIOD_NAME'||P_TIME_PERIOD_NAME);
786 		hr_utility.trace('P_SORT_ORDER'||P_SORT_ORDER);
787 		hr_utility.trace('P_SUMMARY_ONLY'||P_SUMMARY_ONLY);
788 
789 FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
790 LOOP
791 
792    OPEN CSR_GET_CONS_ID(PAY_RUN.PAYROLL_ID);
793                  FETCH CSR_GET_CONS_ID INTO V_CONDOLIDATION_SET_ID;
794            hr_utility.trace('payroll_id:'||PAY_RUN.PAYROLL_ID);
795            hr_utility.trace('P_TIME_PERIOD_NAME:'||P_TIME_PERIOD_NAME);
796         V_TIME_PERIOD_ID:=PAY_GB_MULTI_PAYROLL_RUN.GET_PERIOD_NAME(P_TIME_PERIOD_NAME,PAY_RUN.PAYROLL_ID);
797 	L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
798  	           			APPLICATION       =>       'PAY'
799 								, PROGRAM           =>       'PAYRPTPL'
800 								, DESCRIPTION       =>       NULL
801 								, START_TIME        =>       SYSDATE
802 								, SUB_REQUEST        =>      FALSE
803 								, ARGUMENT1          =>      P_BUSINESS_GROUP_ID
804 								, ARGUMENT2        =>        PAY_RUN.payroll_id
805 								, ARGUMENT3        =>        V_TIME_PERIOD_ID
806 								, ARGUMENT4        =>        V_CONDOLIDATION_SET_ID
807 								, ARGUMENT5        =>        P_SORT_ORDER
808 								, ARGUMENT6        =>        P_SUMMARY_ONLY
809 
810 						);
811   hr_utility.trace('Request Id: for payroll: '|| PAY_RUN.payroll_id ||' is:' ||L_REQ_ID);
812    CLOSE CSR_GET_CONS_ID;
813 END LOOP;
814 
815 
816 END RUN_TPL_MULTI;
817 
818 
819 /*
820 Payment Summary Report
821 */
822 
823 PROCEDURE RUN_PAY_SUM_REP(
824   ERRBUF  OUT NOCOPY VARCHAR2
825  ,RETCODE  OUT NOCOPY NUMBER
826  ,P_BUSINESS_GROUP_ID IN NUMBER
827  ,P_PAYROLL_GROUP_ID IN NUMBER
828  ,P_EARLY_START_DATE IN VARCHAR2
829  ,P_EARLY_END_DATE IN VARCHAR2
830 )
831 
832 IS
833 L_REQ_ID NUMBER;
834 V_CONDOLIDATION_SET_NAME VARCHAR2(20);
838 		hr_utility.trace('P_PAYROLL_GROUP_NAME'||P_PAYROLL_GROUP_ID);
835 BEGIN
836 
837 		hr_utility.trace('P_BUSINESS_GROUP_ID'||P_BUSINESS_GROUP_ID);
839 		hr_utility.trace('P_EARLY_START_DATE'||P_EARLY_START_DATE);
840 		hr_utility.trace('P_EARLY_END_DATE'||P_EARLY_END_DATE);
841 
842 FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
843 LOOP
844 
845 	L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
846  	           			APPLICATION       =>       'PAY'
847 								, PROGRAM           =>       'PAYGBPAY'
848 								, DESCRIPTION       =>       NULL
849 								, START_TIME        =>       SYSDATE
850 								, SUB_REQUEST        =>      FALSE
851 								, ARGUMENT1          =>      P_BUSINESS_GROUP_ID
852 								, ARGUMENT2        =>        PAY_RUN.payroll_id
853 								, ARGUMENT3        =>        P_EARLY_START_DATE
854 								, ARGUMENT4        =>        P_EARLY_END_DATE
855 								, ARGUMENT5        =>        V_CONDOLIDATION_SET_NAME
856 								);
857   hr_utility.trace('Request Id: for payroll: '|| PAY_RUN.payroll_id ||' is:' ||L_REQ_ID);
858 
859 END LOOP;
860 
861 END RUN_PAY_SUM_REP;
862 
863 
864 /*
865 Gross to Net Report
866 */
867 PROCEDURE RUN_GROSS_TO_NET_MULTI(
868   ERRBUF  OUT NOCOPY VARCHAR2
869  ,RETCODE  OUT NOCOPY NUMBER
870  ,P_BUSINESS_GROUP_ID IN NUMBER
871  ,P_PAYROLL_GROUP_ID IN NUMBER
872  ,P_TIME_PERIOD_NAME IN VARCHAR2
873  ,P_GROSS_PAY_ID IN NUMBER
874  ,P_TOT_DED_ID IN NUMBER
875  ,P_DIR_PAY_ID IN NUMBER
876  ,P_EMP_CHAR_ID IN NUMBER
877 )
878 IS
879 L_REQ_ID NUMBER;
880 
881 V_CONDOLIDATION_SET_NAME VARCHAR2(20);
882 V_TIME_PERIOD_ID NUMBER;
883 BEGIN
884 
885 		hr_utility.trace('P_BUSINESS_GROUP_ID'||P_BUSINESS_GROUP_ID);
886 		hr_utility.trace('P_PAYROLL_GROUP_NAME'||P_PAYROLL_GROUP_ID);
887 		hr_utility.trace('P_TIME_PERIOD_NAME'||P_TIME_PERIOD_NAME);
888 
889 
890 
891 
892 
893 FOR PAY_RUN IN CSR_ALL_PAYROLLS(P_PAYROLL_GROUP_ID)
894 LOOP
895 
896 
897         V_TIME_PERIOD_ID:=PAY_GB_MULTI_PAYROLL_RUN.GET_PERIOD_NAME(P_TIME_PERIOD_NAME,PAY_RUN.PAYROLL_ID);
898 	L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
899  	           			APPLICATION      =>       'PAY'
900 								, PROGRAM          =>       'PAYGBGTN'
901 								, DESCRIPTION      =>       NULL
902 								, START_TIME       =>       SYSDATE
903 								, SUB_REQUEST      =>       FALSE
904 								, ARGUMENT1        =>       P_BUSINESS_GROUP_ID
905 								, ARGUMENT2        =>       PAY_RUN.payroll_id
906 								, ARGUMENT3        =>       V_TIME_PERIOD_ID
907 								, ARGUMENT4        =>       V_CONDOLIDATION_SET_NAME
908 								, ARGUMENT5        =>       P_GROSS_PAY_ID
909 								, ARGUMENT6        =>       P_TOT_DED_ID
910 								, ARGUMENT7        =>       P_DIR_PAY_ID
911 								, ARGUMENT8        =>       P_EMP_CHAR_ID
912 								);
913   hr_utility.trace('Request Id: for payroll: '|| PAY_RUN.payroll_id ||' is:' ||L_REQ_ID);
914 END LOOP;
915 
916 END RUN_GROSS_TO_NET_MULTI;
917 
918 
919 
920 PROCEDURE RUN_ROLLBACK_RUN_UK(
921 
922   ERRBUF  OUT NOCOPY VARCHAR2
923  ,RETCODE  OUT NOCOPY NUMBER
924  ,P_PAYROLL_GROUP_ID IN NUMBER
925  ,P_PARENT_REQ_ID IN NUMBER
926 )
927 
928 IS
929 L_REQ_ID NUMBER;
930 V_PROCESS_NAME VARCHAR2(30);
931 V_PAYROLL_RUN number;
932 V_ASSIGNMENT_SET NUMBER;
933 T_CHILD_REQ_AL CHILD_REQ;
934 V_AL_COUNT NUMBER;
935 T_CHILD_REQ_NO_AL CHILD_REQ;
936 V_N_COUNT NUMBER;
937 PAR_REQ_ID NUMBER;
938 PAR_REQ_PARAMS VARCHAR2(100);
939 T_CHILD_REQ CHILD_REQ;
940 V_COUNT NUMBER;
941 P_PAY_ACTION_ID NUMBER;
942 
943 
944 
945 --CURSOR TO FIND THE CHIDL REQUEST OF THIS TYPE
946 
947 CURSOR CSR_CHILD_REQUEST(P_PARENT_REQ_ID IN NUMBER)
948 IS
949 SELECT PCMCRD.CHILD_CONCURRENT_REQUEST_ID CON_ID
950 FROM
951 PAY_GB_MP_CHILD_REQUEST_DET PCMCRD
952 WHERE PCMCRD.PARENT_CONCURRENT_REQUEST_ID=P_PARENT_REQ_ID;
953 
954 CURSOR CSR_PAYACT_ID(PROCESS_NAME IN NUMBER)
955 IS
956    SELECT PPA.PAYROLL_ACTION_ID
957     FROM PAY_PAYROLL_ACTIONS PPA WHERE PPA.REQUEST_ID=PROCESS_NAME;
958 
959 
960 T_ROLLBACK_REQ CHILD_REQ;
961 REQ_COUNT NUMBER;
962 
963 --type child_request_id is table of outputs_r index by binary_integer;
964 BEGIN
965 V_AL_COUNT:=0;
966 V_N_COUNT:=0;
967 V_COUNT:=0;
968 
969 REQ_COUNT:=1;
970 
971 hr_utility.trace('Parent Request Id:'||P_PARENT_REQ_ID);
972 
973 FOR PAY_RUN IN CSR_CHILD_REQUEST(P_PARENT_REQ_ID)
974 LOOP
975    hr_utility.trace('aCTUAL REQUEST iD:'||PAY_RUN.CON_ID);
976  IF PAY_GB_MULTI_DML_AND_VAL.CHK_ALREADY_ROLLED_BACK(PAY_RUN.CON_ID)=1 THEN
977         hr_utility.trace('child requestId:'||PAY_RUN.CON_ID);
978 
979        OPEN CSR_PAYACT_ID(PAY_RUN.CON_ID);
980             FETCH CSR_PAYACT_ID INTO P_PAY_ACTION_ID;
981        CLOSE CSR_PAYACT_ID;
982 
983   hr_utility.trace('payroll action id is:'||P_PAY_ACTION_ID);
984 
985 				L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
986  	           			APPLICATION      =>       'PAY'
987 								, PROGRAM          =>       'ROLLBACK_RUN'
988 								, DESCRIPTION      =>       NULL
989 								, START_TIME       =>       SYSDATE
990 								, SUB_REQUEST      =>       FALSE
991 								, ARGUMENT1        =>       'ROLLBACK'
992 								, ARGUMENT2        =>       P_PAY_ACTION_ID
993 								, ARGUMENT3        =>       V_ASSIGNMENT_SET
994 								);
995 
996   ELSE
997            T_CHILD_REQ_AL(V_AL_COUNT):=PAY_RUN.CON_ID;
998       V_AL_COUNT:=V_AL_COUNT+1;
999   END IF;
1000 T_CHILD_REQ(REQ_COUNT):=PAY_RUN.CON_ID;
1001 T_ROLLBACK_REQ(REQ_COUNT):=L_REQ_ID;
1005 
1002 hr_utility.trace('Child ROLLBACK request copied is:'||T_CHILD_REQ(REQ_COUNT));
1003 REQ_COUNT:=REQ_COUNT+1;
1004 END LOOP;
1006 OPEN CSR_PARENT_REQ_ID(L_REQ_ID);
1007   FETCH CSR_PARENT_REQ_ID INTO PAR_REQ_ID, PAR_REQ_PARAMS;
1008 CLOSE CSR_PARENT_REQ_ID;
1009 -- update the child table and parent table with rollback details
1010 
1011 PAY_GB_MULTI_DML_AND_VAL.UPDATE_MASTER_ROLLBACK_DETAILS
1012 (
1013  P_MASTER_REQUEST_ID=>P_PARENT_REQ_ID
1014 ,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
1015 ,P_ROLLBACK_REQUEST_ID=>PAR_REQ_ID
1016 );
1017 
1018 
1019 FOR P IN 1..REQ_COUNT
1020 LOOP
1021 
1022  PAY_GB_MULTI_DML_AND_VAL.UPDATE_CHILD_ROLLBACK_DETAILS
1023 (
1024  P_MASTER_REQUEST_ID=>P_PARENT_REQ_ID
1025 ,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
1026 ,P_ROLLBACK_REQUEST_ID=>T_ROLLBACK_REQ(P)
1027 ,P_CHILD_REQUEST_ID=>T_CHILD_REQ(P)
1028 );
1029 END LOOP;
1030 
1031 FOR P IN 0..V_AL_COUNT
1032 LOOP
1033        PAY_GB_MULTI_DML_AND_VAL.UPDATE_ALREADY_ROLLEDBACK(P_REQUEST_ID=>T_CHILD_REQ_AL(P));
1034 
1035 END LOOP;
1036 
1037 
1038 END RUN_ROLLBACK_RUN_UK;
1039 
1040 
1041 
1042 PROCEDURE RUN_ROLLBACK_ALL_UK(
1043 
1044   ERRBUF  OUT NOCOPY VARCHAR2
1045  ,RETCODE  OUT NOCOPY NUMBER
1046  ,P_YEAR IN NUMBER
1047  ,P_PAYROLL_GROUP_ID IN NUMBER
1048  ,P_PROCESS_TYPE IN VARCHAR2
1049  ,P_PROCESS_NAME IN NUMBER
1050 )
1051 IS
1052 
1053 L_REQ_ID NUMBER;
1054 V_DUMMY_ONE VARCHAR2(10);
1055 V_DUMMY_TWO VARCHAR2(10);
1056 V_ASSIGNMENT_SET NUMBER;
1057 V_ASSIGN_SET_HID VARCHAR2(100);
1058 V_PAYROLL_RUN_HID VARCHAR2(100);
1059 T_CHILD_REQ_AL CHILD_REQ;
1060 V_AL_COUNT NUMBER;
1061 T_CHILD_REQ_NO_AL CHILD_REQ;
1062 V_N_COUNT NUMBER;
1063 PAR_REQ_ID NUMBER;
1064 PAR_REQ_PARAMS VARCHAR2(100);
1065 T_CHILD_REQ CHILD_REQ;
1066 V_COUNT NUMBER;
1067 P_PAY_ACTION_ID NUMBER;
1068 
1069 CURSOR CSR_CHILD_REQUEST(P_PARENT_REQ_ID IN NUMBER)
1070 IS
1071 SELECT PCMCRD.CHILD_CONCURRENT_REQUEST_ID CON_ID
1072 FROM
1073 PAY_GB_MP_CHILD_REQUEST_DET PCMCRD
1074 WHERE PCMCRD.PARENT_CONCURRENT_REQUEST_ID=P_PARENT_REQ_ID;
1075 
1076 
1077 CURSOR CSR_PAYACT_ID(PROCESS_NAME IN NUMBER)
1078 IS
1079    SELECT PPA.PAYROLL_ACTION_ID
1080     FROM PAY_PAYROLL_ACTIONS PPA WHERE PPA.REQUEST_ID=PROCESS_NAME;
1081 
1082 
1083 BEGIN
1084 V_AL_COUNT:=0;
1085 V_N_COUNT:=0;
1086 V_COUNT:=0;
1087 
1088 
1089 NULL;
1090 
1091 hr_utility.trace('P_PAYROLL_GROUP_ID:'||P_PAYROLL_GROUP_ID);
1092 hr_utility.trace('P_YEAR:'||P_YEAR);
1093 hr_utility.trace('P_PROCESS_TYPE:'||P_PROCESS_TYPE);
1094 hr_utility.trace('P_PROCESS_NAME:'||P_PROCESS_NAME);
1095 
1096 
1097 FOR PAY_RUN IN CSR_CHILD_REQUEST(P_PROCESS_NAME)
1098 LOOP
1099 
1100            hr_utility.trace('PAY_RUN.CON_ID'||PAY_RUN.CON_ID);
1101 
1102      IF PAY_GB_MULTI_DML_AND_VAL.CHK_ALREADY_ROLLED_BACK(PAY_RUN.CON_ID)=1 THEN
1103         hr_utility.trace('child requestId:'||PAY_RUN.CON_ID);
1104 
1105        OPEN CSR_PAYACT_ID(PAY_RUN.CON_ID);
1106             FETCH CSR_PAYACT_ID INTO P_PAY_ACTION_ID;
1107        CLOSE CSR_PAYACT_ID;
1108 
1109         hr_utility.trace('PAY_GB_MULTI_DML_AND_VAL.CHK_ALREADY_ROLLED_BACK(PAY_RUN.CON_ID)=1');
1110         hr_utility.trace('P_PAY_ACTION_ID passed is:'||P_PAY_ACTION_ID);
1111         V_PAYROLL_RUN_HID:='PAYROLL_ACTION_ID='||P_PAY_ACTION_ID;
1112      	L_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(
1113  	           			APPLICATION      =>       'PAY'
1114 								, PROGRAM          =>       'ROLLBACK'
1115 								, DESCRIPTION      =>       NULL
1116 								, START_TIME       =>       SYSDATE
1117 								, SUB_REQUEST      =>       FALSE
1118 								, ARGUMENT1        =>       'ROLLBACK'
1119 								, ARGUMENT2        =>       V_DUMMY_ONE
1120 								, ARGUMENT3        =>       V_DUMMY_TWO
1121 								, ARGUMENT4        =>       P_YEAR
1122 								, ARGUMENT5        =>       P_PROCESS_TYPE
1123 								, ARGUMENT6        =>       P_PAY_ACTION_ID
1124 								, ARGUMENT7        =>       V_ASSIGNMENT_SET
1125 								, ARGUMENT8        =>       V_PAYROLL_RUN_HID
1126 								, ARGUMENT9        =>       V_ASSIGN_SET_HID
1127 								);
1128          T_CHILD_REQ(V_COUNT):= PAY_RUN.CON_ID;
1129          V_COUNT:=V_COUNT+1;
1130          T_CHILD_REQ_NO_AL(V_N_COUNT):=L_REQ_ID;
1131          V_N_COUNT:=V_N_COUNT+1;
1132 
1133       ELSE
1134 		T_CHILD_REQ_AL(V_AL_COUNT):=PAY_RUN.CON_ID;
1135 		V_AL_COUNT:=V_AL_COUNT+1;
1136       END IF;
1137 
1138 END LOOP;
1139 
1140 /*
1141 Find the request id of parent request submitted
1142 */
1143 
1144 OPEN CSR_PARENT_REQ_ID(L_REQ_ID);
1145   FETCH CSR_PARENT_REQ_ID INTO PAR_REQ_ID, PAR_REQ_PARAMS;
1146 CLOSE CSR_PARENT_REQ_ID;
1147 
1148 hr_utility.trace('bEFORE MASTER UPDATE');
1149 
1150 PAY_GB_MULTI_DML_AND_VAL.UPDATE_MASTER_ROLLBACK_DETAILS
1151 (
1152  P_MASTER_REQUEST_ID=>P_PROCESS_NAME
1153 ,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
1154 ,P_ROLLBACK_REQUEST_ID=>PAR_REQ_ID
1155 );
1156 
1157 /*
1158 UPDATING THE ROLLBACK
1159 */
1160 
1161 hr_utility.trace('BEFORE CHILD UPDATE');
1162 FOR P IN 0..V_N_COUNT
1163 LOOP
1164 
1165  PAY_GB_MULTI_DML_AND_VAL.UPDATE_CHILD_ROLLBACK_DETAILS
1166 (
1167  P_MASTER_REQUEST_ID=>P_PROCESS_NAME
1168 ,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
1169 ,P_ROLLBACK_REQUEST_ID=>T_CHILD_REQ_NO_AL(P)
1170 ,P_CHILD_REQUEST_ID=>T_CHILD_REQ(P)
1171 );
1172 END LOOP;
1173 
1174 
1175 /*
1176 UPDATE FOR ALREADY ROLLEDBACK REQUESTS
1177 */
1178 FOR P IN 0..V_AL_COUNT
1179 LOOP
1180        PAY_GB_MULTI_DML_AND_VAL.UPDATE_ALREADY_ROLLEDBACK(P_REQUEST_ID=>T_CHILD_REQ_AL(P));
1181 
1182 END LOOP;
1183 
1184 END RUN_ROLLBACK_ALL_UK;
1185 
1186 END PAY_GB_MULTI_PAYROLL_RUN;