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_COURT_ORDER_UPGRADE

Source


1 PACKAGE BODY pay_gb_court_order_upgrade AS
2   /* $Header: pygbupgr.pkb 120.2 2012/01/19 09:26:56 rpahune ship $ */
3   --
4   --
5   -- Global variables.
6   --
7   g_package VARCHAR2(31) := 'pay_gb_court_order_upgrade.';
8   --
9   --
10   -- -------------------------------------------------------------------------------------------
11   -- Return the ID for a given context.
12   -- -------------------------------------------------------------------------------------------
13   --
14   FUNCTION get_context_id(p_context_name VARCHAR2) RETURN NUMBER IS
15 
16     -- Return the ID for a given context.
17 
18     CURSOR csr_context(p_context_name VARCHAR2) IS
19       SELECT context_id
20       FROM   ff_contexts
21       WHERE  context_name = p_context_name;
22 
23     -- Local variables.
24 
25     l_proc       VARCHAR2(61) := g_package || 'get_context_id';
26     l_context_id NUMBER;
27 
28   BEGIN
29 
30     hr_utility.set_location('Entering: ' || l_proc, 10);
31 
32     OPEN csr_context(p_context_name);
33     FETCH csr_context INTO l_context_id;
34     CLOSE csr_context;
35 
36     hr_utility.set_location('Leaving: ' || l_proc, 20);
37 
38     RETURN l_context_id;
39 
40   END get_context_id;
41 
42   -- -------------------------------------------------------------------------------------------
43   -- Upgrade the run results.
44   -- -------------------------------------------------------------------------------------------
45   --
46   PROCEDURE upgrade_action_contexts(p_business_group_id number) IS
47     --
48     --
49       CURSOR csr_results (l_context_id NUMBER) IS
50       SELECT distinct /*+ INDEX(prr PAY_RUN_RESULTS_N50,PAY_RUN_RESULTS_N1) */
51              et.element_type_id
52             ,et.element_name
53             ,iv.input_value_id
54             ,iv.name input_value_name
55             ,rr.run_result_id
56             ,aa.assignment_action_id
57             ,aa.assignment_id
58             ,nvl(prrv.result_value,'Unknown') result_value
59       FROM   pay_element_types_f    et
60             ,pay_input_values_f     iv
61             ,pay_run_results        rr
62             ,per_assignments_f      paf
63             ,pay_assignment_actions aa
64             ,pay_run_result_values  prrv
65       WHERE  paf.business_group_id   = p_business_group_id
66         AND  paf.assignment_id       = aa.assignment_id
67         AND  et.element_name         IN ('Court Order','Court Order NTPP')
68         AND  et.legislation_code     = 'GB'
69         AND  iv.element_type_id      = et.element_type_id
70         AND  iv.name                 = 'Reference'
71         AND  iv.legislation_code     = 'GB'
72         AND  rr.element_type_id      = et.element_type_id
73         AND  aa.assignment_action_id = rr.assignment_action_id
74         AND  prrv.run_result_id      = rr.run_result_id
75         AND  prrv.input_value_id     = iv.input_value_id
76         AND  NOT EXISTS (SELECT NULL
77                          FROM   pay_action_contexts pac
78                          WHERE  pac.assignment_id = aa.assignment_id
79                            AND  pac.assignment_action_id = aa.assignment_action_id
80                            AND  pac.context_id = l_context_id
81                            AND  pac.context_value = nvl(prrv.result_value, 'Unknown'));
82        --ORDER BY aa.assignment_action_id;
83 
84     -- Local variables.
85 
86     l_proc                   VARCHAR2(61) := g_package || 'upgrade_action_contexts';
87     l_result_rec             csr_results%ROWTYPE;
88     l_assact_id              NUMBER := -1;
89     l_assact_count           NUMBER := 0;
90     l_context_id             NUMBER;
91     l_context_value          VARCHAR2(100) := '-1';
92     --
93   BEGIN
94     --
95     hr_utility.set_location('Entering: ' || l_proc, 10);
96 
97     -- Get ID for the context
98 
99        l_context_id      := get_context_id('SOURCE_TEXT');
100 
101     -- Loop through all run results.
102 
103     OPEN csr_results(l_context_id);
104     LOOP
105       --
106       -- Get the next run result.
107       --
108       FETCH csr_results INTO l_result_rec;
109       EXIT WHEN csr_results%NOTFOUND;
110 
111       -- New assignment action being processed.
112       --
113       l_assact_id     := l_result_rec.assignment_action_id;
114       l_context_value := l_result_rec.result_value;
115 
116 
117         --
118         --
119         -- Store the latest assignmment action and keep count of the total number
120         -- of assignment actions that are being processed.
121         --
122 
123 
124            l_assact_count  := l_assact_count + 1;
125         --
126         --
127         -- Commit every 100 records to reduce the transaction size.
128         --
129         IF MOD(l_assact_count, 100) = 0 THEN
130           COMMIT;
131         END IF;
132         --
133         -- Create missing action contexts.
134         --
135         INSERT INTO pay_action_contexts
136         (assignment_action_id
137         ,assignment_id
138         ,context_id
139         ,context_value)
140         (
141 	  select
142 	  l_result_rec.assignment_action_id
143          ,l_result_rec.assignment_id
144           ,l_context_id
145           ,l_result_rec.result_value
146 	   from dual
147 	   where NOT EXISTS (SELECT NULL
148                          FROM   pay_action_contexts pac
149                          WHERE  pac.assignment_id  =  l_result_rec.assignment_id
150                            AND  pac.assignment_action_id = l_result_rec.assignment_action_id
151                            AND  pac.context_id = l_context_id
152                            AND  pac.context_value = nvl(l_result_rec.result_value, 'Unknown')));
153         --
154 
155         -- Update run results with value 'Unknown'.
156 	 UPDATE pay_run_result_values prrv
157 	 SET   prrv.result_value =  'Unknown'
158 	 WHERE prrv.input_value_id = l_result_rec.input_value_id
159 	 AND   prrv.run_result_id  = l_result_rec.run_result_id
160 	 AND   prrv.result_value is null;
161 
162 
163       --
164     END LOOP;
165     --
166     CLOSE csr_results;
167     COMMIT;
168     --
169     hr_utility.set_location('Leaving: ' || l_proc, 20);
170     --
171   END upgrade_action_contexts;
172   --
173   --
174   -- -------------------------------------------------------------------------------------------
175   -- The main upgrade.
176   -- -------------------------------------------------------------------------------------------
177   --
178   PROCEDURE run(errbuf			OUT	NOCOPY VARCHAR2
179 	       ,retcode			OUT	NOCOPY NUMBER
180 	       ,p_bg_id                         IN NUMBER
181 	       ,p_overpaid  IN VARCHAR2
182 	       )  IS
183     --
184     --
185 
186 -- To get business_group name
187   CURSOR csr_business_group
188   is
189   SELECT name
190   FROM   per_business_groups
191   WHERE  business_group_id =p_bg_id;
192 
193 -- To get date
194   CURSOR csr_date
195   is
196   SELECT to_date(SYSDATE,'DD-MM-YYYY')
197   FROM dual;
198 
199 
200 -- To get defined balance id
201      CURSOR csr_defined_balance_id
202       IS
203          SELECT pdb.defined_balance_id
204            FROM pay_balance_dimensions pbd,
205                 pay_balance_types pbt,
206                 pay_defined_balances pdb
207           WHERE pbd.dimension_name = '_PER_CO_TD_REF_ITD'
208             AND pbd.business_group_id IS NULL
209             AND pbd.legislation_code = 'GB'
210             AND pbt.balance_name = 'Court Order'
211             AND pbt.business_group_id IS NULL
212             AND pbt.legislation_code = 'GB'
213             AND pdb.balance_type_id = pbt.balance_type_id
214             AND pdb.balance_dimension_id = pbd.balance_dimension_id
215             AND pdb.business_group_id IS NULL
216             AND pdb.legislation_code = 'GB';
217 
218 
219  -- To get person details
220     CURSOR csr_person_det
221     is
222     SELECT     distinct  ppf.full_name ,
223 	       ppf.person_id,
224 	       ppf.national_identifier
225     FROM       per_people_f           ppf
226               ,pay_payrolls_f         pf
227               ,per_assignments_f      paf
228     WHERE      ppf.business_group_id = p_bg_id
229     AND        pf.payroll_id = paf.payroll_id
230     AND        paf.person_id = ppf.person_id
231     ORDER      by ppf.full_name;
232 
233  -- To get the payroll name
234     CURSOR csr_get_payroll(p_person_id in number)
235     IS
236     SELECT     distinct  pf.payroll_name
237     FROM       per_people_f           ppf
238               ,pay_payrolls_f         pf
239               ,per_assignments_f      paf
240     WHERE      ppf.business_group_id = p_bg_id
241     AND        pf.payroll_id = paf.payroll_id
242     AND        paf.person_id = ppf.person_id
243     AND        ppf.person_id = p_person_id;
244    -- ORDER      by pf.payroll_id;
245 
246 
247  -- To get the paid value
248     CURSOR csr_asg_actions(p_person_id in number
249                           ,p_defined_balance_id in number) is
250     SELECT max(paa.assignment_action_id) assignment_action_id,
251            pac.context_value context_value,
252 	   nvl(pay_balance_pkg.get_value(p_defined_balance_id, max(pac.assignment_action_id), null,null, fc.context_id, pac.context_value,null,null),0) paid_value
253     FROM       per_assignments_f paf,
254 	       pay_assignment_actions paa,
255 	       pay_action_contexts pac,
256 	       ff_contexts fc,
257 	       pay_element_types_f pet,
258 	       pay_input_values_f piv,
259 	       pay_run_results prr,
260 	       pay_run_result_values prrv
261 	      ,pay_input_values_f     piv1
262               ,pay_run_results        prr1
263        	      ,pay_run_result_values  prrv1
264     WHERE   paf.assignment_id     = paa.assignment_id
265         AND paf.person_id           = p_person_id
266         AND fc.context_id           = pac.context_id
267         AND fc.context_name         = 'SOURCE_TEXT'
268         AND paa.assignment_id       =  pac.assignment_id
269         --AND paa.action_status       = 'C'
270 	AND paa.action_status       in ('C','S')    --Modified for the bug 10066755
271         AND pet.element_name        IN ('Court Order','Court Order NTPP')
272         AND pet.legislation_code     = 'GB'
273         AND pet.element_type_id      = piv.element_type_id
274         AND piv.name                 = 'Reference'
275         AND prr.run_result_id        = prrv.run_result_id
276         AND prrv.input_value_id      = piv.input_value_id
277         AND prr.assignment_action_id =  paa.assignment_action_id
278         AND piv.legislation_code     = 'GB'
279         AND paa.assignment_action_id = prr1.assignment_action_id
280         AND prrv1.run_result_id      = prr1.run_result_id
281         AND prr1.element_type_id     = pet.element_type_id
282         AND prr.run_result_id        = prr1.run_result_id
283         AND prrv1.input_value_id     = piv1.input_value_id
284         AND prrv1.result_value       in ('CTO','CCAEO')
285         AND piv1.name                = 'Type'
286         AND piv1.legislation_code    = 'GB'
287 	AND pac.context_value        not in ('Unknown')
288 	group by fc.context_id, pac.context_value;
289 
290 --To get initial debt value
291   CURSOR  csr_intial_debt(p_assignment_action_id number
292                          ,p_paid_value number
293 			 ,p_context_value varchar2) is
294     SELECT nvl(PRRV.result_value ,'0') result_value,
295            nvl((prrv.result_value - p_paid_value),'0') overpaid
296     FROM
297 	pay_element_types_f pet,
298 	pay_input_values_f piv,
299 	pay_run_results prr,
300 	pay_run_result_values prrv,
301 	pay_input_values_f piv1,
302 	pay_run_results prr1,
303 	pay_run_result_values prrv1
304     WHERE      pet.element_name  IN ('Court Order','Court Order NTPP')
305         AND    pet.legislation_code = 'GB'
306         AND    piv.element_type_id  = pet.element_type_id
307         AND    piv.name             = 'Initial Debt'
308         AND    prr.run_result_id    = prrv.run_result_id
309         AND    prrv.input_value_id  = piv.input_value_id
310         AND    prr.assignment_action_id  in (p_assignment_action_id)
311         AND    prrv.result_value is not null
312         AND    prr1.assignment_action_id in (p_assignment_action_id)
313         AND    prrv1.run_result_id      = prr1.run_result_id
314         AND    prr1.element_type_id     = pet.element_type_id
315         AND    prr.run_result_id        = prr1.run_result_id
316         AND    prrv1.input_value_id     = piv1.input_value_id
317         AND    prrv1.result_value       in (p_context_value)
318         AND    piv1.name                = 'Reference'
319         AND    piv1.legislation_code    = 'GB';
320        -- order  by 2 desc;
321 
322        -- Local variables.
323        --
324 	 l_proc VARCHAR2(61) ;
325 	 l_date  date;
326 
327 
328 	v_initial_det         csr_intial_debt%rowtype;
329 	l_defined_balance_id  pay_defined_balances.defined_balance_id%type;
330 	l_business_group_name per_business_groups.name%type;
331 	v_payroll             pay_payrolls_f.payroll_name%type;
332 
333 	l_overpaid_flag varchar2(1);
334 	l_no_data_found varchar2(1);
335 
336   BEGIN
337     --
338     l_proc  := g_package || 'run';
339     retcode := 0;
340     l_overpaid_flag := 'N';
341     l_no_data_found := 'Y';
342 
343     hr_utility.set_location('Entering: ' || l_proc, 10);
344 
345 
346     -- Correct the action contexts.
347        upgrade_action_contexts(p_bg_id);
348     --
349 
350     OPEN  csr_business_group;
351     FETCH csr_business_group into l_business_group_name;
352     CLOSE csr_business_group;
353 
354     OPEN  csr_date;
355     FETCH csr_date into l_date;
356     CLOSE csr_date;
357 
358     hr_utility.set_location('Leaving: ' || l_proc, 20);
359     fnd_file.put_line(FND_FILE.OUTPUT,'------------------------------------------------------------------------------------------------------------------------------------');
360     fnd_file.put_line(FND_FILE.OUTPUT,'                                  Court order details for employees in : '||rpad(l_business_group_name,30));
361     fnd_file.put_line(FND_FILE.OUTPUT,'                                                   report date : '|| l_date);
362     fnd_file.put_line(FND_FILE.OUTPUT,'------------------------------------------------------------------------------------------------------------------------------------');
363     fnd_file.put_line(FND_FILE.OUTPUT,'                                                                                ');
364     fnd_file.put_line(FND_FILE.OUTPUT, rpad('Payroll Name',20)||'  '||rpad('Employee Name',20)||'  '||rpad('National Identifier',20)||'  '||rpad(lpad('Initial Debt',12),12)||'  '||
365     rpad('Reference',20)||'  '||rpad(lpad('Paid to Date',15), 15)||'  '||rpad('Overpaid',8));
366     fnd_file.put_line(FND_FILE.OUTPUT,'------------------------------------------------------------------------------------------------------------------------------------');
367 
368 
369     OPEN  csr_defined_balance_id;
370     FETCH csr_defined_balance_id into l_defined_balance_id;
371     CLOSE csr_defined_balance_id;
372 
373     for v_csr_person in csr_person_det
374     loop
375        OPEN  csr_get_payroll(v_csr_person.person_id);
376        FETCH csr_get_payroll into v_payroll;
377        CLOSE csr_get_payroll;
378 
379         for v_csr_actions in csr_asg_actions(v_csr_person.person_id, l_defined_balance_id)
380         loop
381            OPEN  csr_intial_debt(v_csr_actions.assignment_action_id,v_csr_actions.paid_value,v_csr_actions.context_value);
382 	   FETCH csr_intial_debt into v_initial_det;
383 	   l_no_data_found := 'N';
384 	   CLOSE csr_intial_debt;
385 
386 	   if  nvl(v_csr_actions.paid_value,'0') > nvl(v_initial_det.result_value,'0')  then
387                    l_overpaid_flag := 'Y';
388            else
389 	           l_overpaid_flag := 'N';
390 	   end if;
391 
392 	IF p_overpaid = 'Y' then
393 	/* To print only over paid employees*/
394 	 if  nvl(v_csr_actions.paid_value,'0') > nvl(v_initial_det.result_value,'0')  then
395           if csr_asg_actions%rowcount = 1 then
396 
397             fnd_file.put_line(FND_FILE.OUTPUT,rpad(v_payroll,20)||'  '||rpad(v_csr_person.full_name,20)||'  '||rpad(v_csr_person.national_identifier,20)||'  '||
398             rpad(lpad(to_char(to_number(v_initial_det.result_value),'FM999999990D00'),12),12,' ')||'  '||
399 	    rpad(v_csr_actions.context_value,20)||'  '||rpad(lpad(to_char(to_number(v_csr_actions.paid_value),'FM999999999990D00'),15),15, ' ')||'  '||l_overpaid_flag);
400           else
401 	    fnd_file.put_line(FND_FILE.OUTPUT,rpad(' ',20)||'  '||rpad(' ',20)||'  '||rpad(' ',20)||'  '||
402 	    rpad(lpad(to_char(to_number(v_initial_det.result_value),'FM999999990D00'),12),12,' ')||'  '||
403 	    rpad(v_csr_actions.context_value,20)||'  '||rpad(lpad(to_char(to_number(v_csr_actions.paid_value),'FM999999999990D00'),15),15,' ')||'  '||l_overpaid_flag);
404 	  end if;
405 	  end if;
406          ELSE
407           if csr_asg_actions%rowcount = 1 then
408 
409            fnd_file.put_line(FND_FILE.OUTPUT,rpad(v_payroll,20)||'  '||rpad(v_csr_person.full_name,20)||'  '||rpad(v_csr_person.national_identifier,20)||'  '||
410            rpad(lpad(to_char(to_number(v_initial_det.result_value),'FM999999990D00'),12),12,' ')||'  '||
411 	   rpad(v_csr_actions.context_value,20)||'  '||rpad(lpad(to_char(to_number(v_csr_actions.paid_value),'FM999999999990D00'),15),15,' ')||'  '||l_overpaid_flag);
412           else
413            fnd_file.put_line(FND_FILE.OUTPUT,rpad(' ',20)||'  '||rpad(' ',20)||'  '||rpad(' ',20)||'  '||
414 	   rpad(lpad(to_char(to_number(v_initial_det.result_value),'FM999999990D00'),12),12,' ')||'  '||
415            rpad(v_csr_actions.context_value,20)||'  '||rpad(lpad(to_char(to_number(v_csr_actions.paid_value),'FM999999999990D00'),15),15, ' ')||'  '||l_overpaid_flag);
416           end if;
417 	 END IF;
418        end loop;
419     end loop;
420 
421 if l_no_data_found = 'Y' THEN
422     fnd_file.put_line(FND_FILE.OUTPUT,'--------------------------------------------------------No Data Found --------------------------------------------------------------');
423 end if;
424 
425     --
426   EXCEPTION
427 	WHEN OTHERS THEN
428 		ROLLBACK;
429 		errbuf  := NULL;
430 		retcode := 2;
431 		RAISE_APPLICATION_ERROR(-20001, SQLERRM);
432   END run;
433   --
434 END pay_gb_court_order_upgrade;