1 package body pay_fr_update_pcs_code as
2 /* $Header: pyfrupcs.pkb 115.3 2004/01/02 00:08 ayegappa noship $ */
3
4 procedure update_old_pcs_codes (errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_business_group_id in number)
7 is
8
9 cursor csr_get_job (c_business_group_id number) is
10 select pj.job_id job_id
11 ,pj.name job_name
12 ,pj.job_information1 pcs_code
13 from per_jobs pj
14 where business_group_id = c_business_group_id
15 and JOB_INFORMATION_CATEGORY = 'FR'
16 and not exists (select 1
17 from fnd_common_lookups fcl
18 where fcl.lookup_type = 'FR_NEW_PCS_CODE'
19 and fcl.lookup_code = pj.job_information1);
20
21 cursor csr_get_new_code (c_pcs_code in varchar2) is
22 select description new_code
23 from fnd_common_lookups
24 where lookup_code = c_pcs_code
25 and lookup_type = 'FR_PCS_CODE';
26
27 l_new_code fnd_common_lookups.description%type;
28
29 --Getting the message
30 l_value varchar2(240);
31
32 begin
33 FOR get_pcs_code IN csr_get_job (p_business_group_id)
34 LOOP
35 l_new_code := null;
36 l_value := null;
37 OPEN csr_get_new_code (get_pcs_code.pcs_code);
38 FETCH csr_get_new_code INTO l_new_code;
39 CLOSE csr_get_new_code;
40 IF l_new_code IS NULL THEN
41 l_value := pay_fr_general.get_payroll_message('PAY_75193_OLD_CODE', null, null, null);
42 fnd_file.put_line(fnd_file.log, get_pcs_code.job_name||', old pcs code '|| get_pcs_code.pcs_code||l_value||' '||get_pcs_code.job_id);
43 /* Making the old pcs code to be null. The user will take the jobs from the log message and change the
44 old pcs codes to new pcs codes manually */
45 update per_jobs
46 set job_information1 = NULL
47 where business_group_id = p_business_group_id
48 and JOB_INFORMATION_CATEGORY = 'FR'
49 and job_id = get_pcs_code.job_id;
50 ELSE
51 update per_jobs
52 set job_information1 = l_new_code
53 where business_group_id = p_business_group_id
54 and JOB_INFORMATION_CATEGORY = 'FR'
55 and job_id = get_pcs_code.job_id;
56 l_value := pay_fr_general.get_payroll_message('PAY_75194_UPDATED', null, null, null);
57 fnd_file.put_line(fnd_file.log, get_pcs_code.job_name||', old pcs code '|| get_pcs_code.pcs_code||' converted to ' ||l_new_code||'. '||l_value||' '||get_pcs_code.job_id);
58 END IF;
59 END LOOP;
60 end update_old_pcs_codes;
61
62 End pay_fr_update_pcs_code;
[Home] [Help]