[Home] [Help]
PACKAGE BODY: APPS.PAY_ES_RUN_TYPES
Source
1 PACKAGE BODY pay_es_run_types AS
2 /* $Header: pyesrunt.pkb 120.0 2005/05/29 04:38:50 appldev noship $ */
3
4 l_index NUMBER := 0;
5 l_prod_flag BOOLEAN := True;
6 l_user_flag BOOLEAN := True;
7 --------------------------------------------------------------------------------
8 -- rebuild_run_types
9 --------------------------------------------------------------------------------
10 PROCEDURE rebuild_run_types(errbuf out nocopy VARCHAR2
11 ,retcode out nocopy VARCHAR2
12 ,p_business_group_id VARCHAR2)
13 IS
14
15 --
16 Cursor csr_element_all(c_business_group_id number) IS
17 SELECT pet.element_type_id
18 ,pet.element_name
19 ,pet.business_group_id
20 ,pet.legislation_code
21 ,pet.indirect_only_flag
22 ,MIN(pet.effective_start_date) effective_date
23 FROM pay_element_types_f pet
24 ,per_business_groups pbg
25 WHERE Nvl(pet.indirect_only_flag, 'N') = 'N'
26 AND pet.business_group_id = c_business_group_id
27 AND pbg.business_group_id = pet.business_group_id
28 AND pbg.legislation_code = 'ES'
29 AND pet.legislation_code IS NULL
30 GROUP BY pet.element_type_id
31 ,pet.element_name
32 ,pet.business_group_id
33 ,pet.legislation_code
34 ,pet.indirect_only_flag;
35 --
36 l_element_rec csr_element_all%ROWTYPE;
37 l_effective_start_date DATE;
38 l_effective_end_date DATE;
39 l_header VARCHAR2(500);
40 l_underline VARCHAR2(500);
41 l_business_group_id NUMBER;
42 --
43 Begin
44 hr_utility.trace('Entering pay_es_run_types.rebuild_run_types ');
45 --
46 l_business_group_id := fnd_number.canonical_to_number(p_business_group_id);
47 --
48 l_header := rpad(hr_general.decode_lookup('ES_FORM_LABELS','ELE_NAME'),60)||' '||
49 rpad(hr_general.decode_lookup('ES_FORM_LABELS','ELE_CLASS'),60)||' '||
50 rpad(hr_general.decode_lookup('ES_FORM_LABELS','RUN_TYPE'),30)||' '||
51 rpad(hr_general.decode_lookup('ES_FORM_LABELS','STATUS'),20);
52 --
53 l_underline :=rpad('-',60,'-')||' '||
54 rpad('-',60,'-')||' '||
55 rpad('-',30,'-')||' '||
56 rpad('-',20,'-');
57 Fnd_File.New_Line(FND_FILE.LOG,1);
58 Fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('ES_FORM_LABELS','RRT'));
59 Fnd_File.New_Line(FND_FILE.LOG,1);
60 Fnd_file.put_line(FND_FILE.LOG,l_underline);
61 Fnd_file.put_line(FND_FILE.LOG,l_header);
62 Fnd_file.put_line(FND_FILE.LOG,l_underline);
63
64 FOR l_element_rec IN csr_element_all(l_business_group_id) LOOP
65 hr_utility.set_location('Creating run type usage For Element '||l_element_rec.element_name,20);
66 IF l_element_rec.indirect_only_flag = 'N' THEN
67 --
68 create_element_run_type_usages(p_effective_date => l_element_rec.effective_date
69 ,p_element_name => l_element_rec.element_name
70 ,p_element_type_id => l_element_rec.element_type_id
71 ,p_legislation_code=> l_element_rec.legislation_code
72 ,p_business_gr_id => l_element_rec.business_group_id);
73 END IF;
74 END LOOP;
75 hr_utility.trace('Leaving pay_es_run_types.rebuild_run_types ');
76 --
77 END rebuild_run_types;
78 --------------------------------------------------------------------------------
79 -- create_element_run_type_usages
80 --------------------------------------------------------------------------------
81 PROCEDURE create_element_run_type_usages(p_effective_date IN DATE
82 ,p_element_name IN VARCHAR2
83 ,p_element_type_id IN NUMBER
84 ,p_legislation_code IN VARCHAR2
85 ,p_business_gr_id IN NUMBER)
86 IS
87 l_element_type_usage_id NUMBER;
88 l_object_version_number NUMBER;
89 l_effective_start_date DATE;
90 l_effective_end_date DATE;
91 l_flag VARCHAR2(1);
92 l_print_element_info varchar2(2000);
93 --
94 CURSOR get_run_types(c_element_type_id NUMBER) IS
95 SELECT prt.run_type_id
96 ,prt.shortname
97 FROM pay_run_types_f prt
98 WHERE legislation_code = 'ES'
99 AND prt.shortname = 'TAX_WITHHOLDING_RATE'
100 AND p_effective_date between effective_start_date and effective_end_date
101 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
102 FROM pay_element_type_usages_f petu
103 WHERE petu.RUN_TYPE_ID = prt.RUN_TYPE_ID
104 AND petu.ELEMENT_TYPE_ID = p_ELEMENT_TYPE_ID
105 AND p_effective_date between petu.effective_start_date and petu.effective_end_date);
106
107 CURSOR csr_get_element_print_info(c_element_type_id NUMBER
108 ,c_effective_date DATE ) IS
109 SELECT petl.element_name element_name
110 ,pecl.classification_name classification_name
111 FROM pay_element_types_f pet
112 ,pay_element_types_f_tl petl
113 ,pay_element_classifications_tl pecl
114 WHERE pet.element_type_id = c_element_type_id
115 AND pet.element_type_id = petl.element_type_id
116 AND petl.language = USERENV('LANG')
117 AND pet.classification_id = pecl.classification_id
118 AND c_effective_date BETWEEN pet.effective_start_date and pet.effective_end_date;
119
120 CURSOR csr_get_run_type_print_info(c_run_type_id NUMBER) IS
121 SELECT prt.run_type_name run_type_name
122 FROM pay_run_types_f_tl prt
123 WHERE prt.run_type_id = c_run_type_id
124 AND prt.language = USERENV('LANG');
125
126 l_usage_exist varchar2(1);
127 l_element_info csr_get_element_print_info%ROWTYPE;
128 l_runtype_info csr_get_run_type_print_info%ROWTYPE;
129 BEGIN
130 --
131 IF p_legislation_code IS NULL THEN
132 hr_startup_data_api_support.enable_startup_mode('USER');
133 END IF;
134 --
135 FOR i IN get_run_types(p_element_type_id) LOOP
136 --
137 pay_element_type_usage_api.create_element_type_usage(
138 p_validate => FALSE
139 ,p_effective_date => p_effective_date
140 ,p_run_type_id => i.run_type_id
141 ,p_element_type_id => p_element_type_id
142 ,p_legislation_code => p_legislation_code
143 ,p_business_group_id => p_business_gr_id
147 ,p_effective_end_date => l_effective_end_date);
144 ,p_element_type_usage_id => l_element_type_usage_id
145 ,p_object_version_number => l_object_version_number
146 ,p_effective_start_date => l_effective_start_date
148 l_index := l_index + 1;
149
150 OPEN csr_get_element_print_info(p_element_type_id, p_effective_date);
151 FETCH csr_get_element_print_info into l_element_info;
152 CLOSE csr_get_element_print_info;
153
154 OPEN csr_get_run_type_print_info(i.run_type_id);
155 FETCH csr_get_run_type_print_info into l_runtype_info;
156 CLOSE csr_get_run_type_print_info;
157
158 l_print_element_info:= rpad(nvl(l_element_info.element_name,' '),60)||' '||
159 rpad(nvl(l_element_info.classification_name,' '),60)||' '||
160 rpad(nvl(l_runtype_info.run_type_name,' '),30)||' '||
161 rpad(hr_general.decode_lookup('ES_FORM_LABELS','EXCLUDE'),20);
162
163 Fnd_file.put_line(FND_FILE.LOG,l_print_element_info);
164 END LOOP;
165 END create_element_run_type_usages;
166 --
167 End pay_es_run_types;