[Home] [Help]
PACKAGE BODY: APPS.PAY_CN_REPORT_PKG
Source
1 PACKAGE BODY pay_cn_report_pkg AS
2 /* $Header: pycnrept.pkb 120.4 2006/12/22 07:09:25 rpalli noship $ */
3
4 --------------------------------------------------------------------------
5 -- --
6 -- Name : GET_BALANCE_VALUE --
7 -- Type : FUNCTION --
8 -- Access : Public --
9 -- Description : Function to return the value of a particular --
10 -- balance from PAY_ACTION_INFORMATION --
11 -- --
12 -- Parameters : --
13 -- IN : p_assignment_action_id NUMBER --
14 -- p_balance_name VARCHAR2 --
15 -- p_dimension_name VARCHAR2 --
16 -- OUT : N/A --
17 -- RETURN : NUMBER --
18 -- --
19 -- Change History : --
20 --------------------------------------------------------------------------
21 -- Rev# Date Userid Description --
22 --------------------------------------------------------------------------
23 -- 115.0 13-SEP-2003 statkar Initial Version --
24 -- 115.1 12-May-2006 rpalli Bug#5219815:Modified code to return --
25 -- correct value based on session lang --
26 -- 115.2 19-Dec-2006 rpalli Bug#5724500:Modified code to return --
27 -- balance reporting name --
28 -- 115.3 22-Dec-2006 rpalli Bug#5724500:Removed to_number error --
29 --------------------------------------------------------------------------
30 FUNCTION get_balance_value
31 (p_assignment_action_id IN NUMBER
32 ,p_balance_name IN VARCHAR2
33 ,p_dimension_name IN VARCHAR2 DEFAULT 'PTD'
34 )
35 RETURN NUMBER
36 IS
37
38 CURSOR csr_bal(c_balance_name VARCHAR2) IS
39 SELECT DECODE(p_dimension_name,
40 'PTD', action_information5,
41 'YTD', action_information4) value
42 FROM pay_action_information pai
43 WHERE pai.action_context_id = p_assignment_action_id
44 AND pai.action_context_type = 'AAP'
45 AND pai.action_information_category = 'APAC BALANCES'
46 AND pai.action_information1 = c_balance_name;
47
48 CURSOR csr_bal_name IS
49 SELECT nvl(pbtl.reporting_name,pbtl.balance_name)
50 FROM pay_balance_types pbt,
51 pay_balance_types_tl pbtl
52 WHERE pbt.balance_name = p_balance_name
53 AND pbt.legislation_code = 'CN'
54 AND pbt.balance_type_id = pbtl.balance_type_id
55 AND pbtl.language = userenv('LANG');
56
57 l_value pay_action_information.action_information4%TYPE;
58 l_procedure_name VARCHAR2(50);
59 l_message VARCHAR2(255);
60 l_balance_name VARCHAR2(255);
61 BEGIN
62
63 l_procedure_name := g_package_name||'get_balance_value';
64 hr_utility.set_location( 'Entering:'|| l_procedure_name, 10);
65
66 OPEN csr_bal_name;
67 FETCH csr_bal_name
68 INTO l_balance_name;
69 CLOSE csr_bal_name;
70
71 OPEN csr_bal(l_balance_name);
72 FETCH csr_bal
73 INTO l_value;
74 CLOSE csr_bal;
75
76 hr_utility.trace (' Balance Value : '||l_value);
77 hr_utility.set_location( 'Leaving:'|| l_procedure_name, 20);
78 RETURN fnd_number.canonical_to_number(NVL(l_value,'0'));
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 hr_utility.set_location( 'Leaving:'|| l_procedure_name, 30);
83 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure_name, 'SQLERRMC:'||sqlerrm);
84 hr_utility.trace (l_message);
85 RAISE;
86 END get_balance_value;
87
88 --------------------------------------------------------------------------
89 -- --
90 -- Name : GET_ELEMENT_VALUE --
91 -- Type : FUNCTION --
92 -- Access : Public --
93 -- Description : Function to return the 'Pay Value' of a particular --
94 -- element from PAY_ACTION_INFORMATION --
95 -- --
96 -- Parameters : --
97 -- IN : p_assignment_action_id NUMBER --
98 -- p_element_name VARCHAR2 --
99 -- OUT : N/A --
100 -- RETURN : NUMBER --
101 -- --
102 -- Change History : --
103 --------------------------------------------------------------------------
104 -- Rev# Date Userid Description --
105 --------------------------------------------------------------------------
106 -- 115.0 13-SEP-2003 statkar Initial Version --
107 -- 115.1 12-May-2006 rpalli Bug#5219815:Modified code to return --
108 -- correct value based on session lang --
109 -- 115.2 18-Dec-2006 rpalli Bug#5717755:Modified code to return --
110 -- single value in cursor --
111 -- 115.3 22-Dec-2006 rpalli Bug#5724500:Removed to_number error --
112 --------------------------------------------------------------------------
113 FUNCTION get_element_value
114 (p_assignment_action_id IN NUMBER
115 ,p_element_name IN VARCHAR2
116 )
117 RETURN NUMBER
118 IS
119
120 CURSOR csr_elem(c_element_name VARCHAR2) IS
121 SELECT action_information5 value
122 FROM pay_action_information pai
123 WHERE pai.action_context_id = p_assignment_action_id
124 AND pai.action_context_type = 'AAP'
125 AND pai.action_information_category = 'APAC ELEMENTS'
126 AND pai.action_information1 = c_element_name
127 AND pai.action_information7 IS NULL;
128
129 CURSOR csr_elem_name IS
130 SELECT petl.element_name
131 FROM pay_element_types_f pet,
132 pay_element_types_f_tl petl
133 WHERE pet.element_name = p_element_name
134 AND pet.legislation_code = 'CN'
135 AND pet.element_type_id = petl.element_type_id
136 AND petl.language = userenv('LANG');
137
138 l_value pay_action_information.action_information4%TYPE;
139 l_procedure_name VARCHAR2(50);
140 l_message VARCHAR2(255);
141 l_element_name VARCHAR2(255);
142 BEGIN
143
144 l_procedure_name := g_package_name||'get_element_value_1';
145 hr_utility.set_location( 'Entering:'|| l_procedure_name, 10);
146
147 OPEN csr_elem_name;
148 FETCH csr_elem_name
149 INTO l_element_name;
150 CLOSE csr_elem_name;
151
152 OPEN csr_elem(l_element_name);
153 FETCH csr_elem
154 INTO l_value;
155 CLOSE csr_elem;
156
157 hr_utility.trace (' Element Value : '||l_value);
158 hr_utility.set_location( 'Leaving:'|| l_procedure_name, 20);
159
160 RETURN fnd_number.canonical_to_number(NVL(l_value,'0'));
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 hr_utility.set_location( 'Leaving:'|| l_procedure_name, 30);
165 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure_name, 'SQLERRMC:'||sqlerrm);
166 hr_utility.trace (l_message);
167 RAISE;
168 END get_element_value;
169
170 --------------------------------------------------------------------------
171 -- --
172 -- Name : GET_ELEMENT_VALUE --
173 -- Type : FUNCTION --
174 -- Access : Public --
175 -- Description : Function to return the specified input value of a --
176 -- particular element from PAY_ACTION_INFORMATION --
177 -- --
178 -- Parameters : --
179 -- IN : p_assignment_action_id NUMBER --
180 -- p_element_name VARCHAR2 --
181 -- p_input_value_name VARCHAR2 --
182 -- OUT : N/A --
183 -- RETURN : NUMBER --
184 -- --
185 -- Change History : --
186 --------------------------------------------------------------------------
187 -- Rev# Date Userid Description --
188 --------------------------------------------------------------------------
189 -- 115.0 13-SEP-2003 statkar Initial Version --
190 -- 115.1 12-May-2006 rpalli Bug#5219815:Modified code to return --
191 -- correct value based on session lang --
192 -- 115.2 22-Dec-2006 rpalli Bug#5724500:Removed to_number error --
193 --------------------------------------------------------------------------
194 FUNCTION get_element_value
195 (p_assignment_action_id IN NUMBER
196 ,p_element_name IN VARCHAR2
197 ,p_input_value_name IN VARCHAR2
198 )
199 RETURN NUMBER
200 IS
201
202 CURSOR csr_elem(c_element_name VARCHAR2) IS
203 SELECT action_information5 value
204 FROM pay_action_information pai
205 WHERE pai.action_context_id = p_assignment_action_id
206 AND pai.action_context_type = 'AAP'
207 AND pai.action_information_category = 'APAC ELEMENTS'
208 AND pai.action_information1 = c_element_name
209 AND pai.action_information7 = p_input_value_name;
210
211 CURSOR csr_elem_name IS
212 SELECT petl.element_name
213 FROM pay_element_types_f pet,
214 pay_element_types_f_tl petl
215 WHERE pet.element_name = p_element_name
216 AND pet.legislation_code = 'CN'
217 AND pet.element_type_id = petl.element_type_id
218 AND petl.language = userenv('LANG');
219
220 l_value pay_action_information.action_information4%TYPE;
221 l_procedure_name VARCHAR2(50);
222 l_message VARCHAR2(255);
223 l_element_name VARCHAR2(255);
224 BEGIN
225
226 l_procedure_name := g_package_name||'get_element_value_2';
227 hr_utility.set_location( 'Entering:'|| l_procedure_name, 10);
228
229 OPEN csr_elem_name;
230 FETCH csr_elem_name
231 INTO l_element_name;
232 CLOSE csr_elem_name;
233
234 OPEN csr_elem(l_element_name);
235 FETCH csr_elem
236 INTO l_value;
237 CLOSE csr_elem;
238
239 hr_utility.trace (' Element Value : '||l_value);
240 hr_utility.set_location( 'Leaving:'|| l_procedure_name, 20);
241
242 RETURN fnd_number.canonical_to_number(NVL(l_value,'0'));
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 hr_utility.set_location( 'Leaving:'|| l_procedure_name, 30);
247 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure_name, 'SQLERRMC:'||sqlerrm);
248 hr_utility.trace (l_message);
249 RAISE;
250 END get_element_value;
251
252 END pay_cn_report_pkg;