[Home] [Help]
PACKAGE BODY: APPS.PQP_ERT_SHD
Source
1 Package Body pqp_ert_shd as
2 /* $Header: pqertrhi.pkb 120.7 2006/09/15 00:09:58 sshetty noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_ert_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 Begin
16 --
17 Return (nvl(g_api_dml, false));
18 --
19 End return_api_dml_status;
20 --
21 -- ----------------------------------------------------------------------------
22 -- |---------------------------< constraint_error >---------------------------|
23 -- ----------------------------------------------------------------------------
24 Procedure constraint_error
25 (p_constraint_name in all_constraints.constraint_name%TYPE
26 ) Is
27 --
28 l_proc varchar2(72) := g_package||'constraint_error';
29 --
30 Begin
31 --
32 If (p_constraint_name = 'PEL_PK') Then
33 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('STEP','5');
36 fnd_message.raise_error;
37 Else
38 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
41 fnd_message.raise_error;
42 End If;
43 --
44 End constraint_error;
45 --
46 -- ----------------------------------------------------------------------------
47 -- |-----------------------------< api_updating >-----------------------------|
48 -- ----------------------------------------------------------------------------
49 Function api_updating
50 (p_exception_report_id in number
51 ,p_language in varchar2
52 ) Return Boolean Is
53 --
54 --
55 -- Cursor selects the 'current' row from the HR Schema
56 --
57 Cursor C_Sel1 is
58 select
59 exception_report_id
60 ,exception_report_name
61 ,language
62 ,source_lang
63 from pqp_exception_reports_tl
64 where exception_report_id = p_exception_report_id
65 and language = p_language;
66 --
67 l_fct_ret boolean;
68 --
69 Begin
70 --
71 If (p_exception_report_id is null or
72 p_language is null
73 ) Then
74 --
75 -- One of the primary key arguments is null therefore we must
76 -- set the returning function value to false
77 --
78 l_fct_ret := false;
79 Else
80 If (p_exception_report_id
81 = pqp_ert_shd.g_old_rec.exception_report_id and
82 p_language
83 = pqp_ert_shd.g_old_rec.language
84 ) Then
85 --
86 -- The g_old_rec is current therefore we must
87 -- set the returning function to true
88 --
89 l_fct_ret := true;
90 Else
91 --
92 -- Select the current row into g_old_rec
93 --
94 Open C_Sel1;
95 Fetch C_Sel1 Into pqp_ert_shd.g_old_rec;
96 If C_Sel1%notfound Then
97 Close C_Sel1;
98 --
99 -- The primary key is invalid therefore we must error
100 --
101 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
102 fnd_message.raise_error;
103 End If;
104 Close C_Sel1;
105 l_fct_ret := true;
106 End If;
107 End If;
108 Return (l_fct_ret);
109 --
110 End api_updating;
111 --
112 -- ----------------------------------------------------------------------------
113 -- |---------------------------------< lck >----------------------------------|
114 -- ----------------------------------------------------------------------------
115 Procedure lck
116 (p_exception_report_id in number
117 ,p_language in varchar2
118 ) is
119 --
120 -- Cursor selects the 'current' row from the HR Schema
121 --
122 Cursor C_Sel1 is
123 select
124 exception_report_id
125 ,exception_report_name
126 ,language
127 ,source_lang
128 from pqp_exception_reports_tl
129 where exception_report_id = p_exception_report_id
130 and language = p_language
131 for update nowait;
132 --
133 l_proc varchar2(72) := g_package||'lck';
134 --
135 Begin
136 hr_utility.set_location('Entering:'||l_proc, 5);
137 --
138 hr_api.mandatory_arg_error
139 (p_api_name => l_proc
140 ,p_argument => 'EXCEPTION_REPORT_ID'
141 ,p_argument_value => p_exception_report_id
142 );
143 hr_utility.set_location(l_proc,6);
144 hr_api.mandatory_arg_error
145 (p_api_name => l_proc
146 ,p_argument => 'LANGUAGE'
147 ,p_argument_value => p_language
148 );
149 hr_utility.set_location(l_proc,7);
150 --
151 Open C_Sel1;
152 Fetch C_Sel1 Into pqp_ert_shd.g_old_rec;
153 If C_Sel1%notfound then
154 Close C_Sel1;
155 --
156 -- The primary key is invalid therefore we must error
157 --
158 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
159 fnd_message.raise_error;
160 End If;
161 Close C_Sel1;
162 --
163 hr_utility.set_location(' Leaving:'||l_proc, 10);
164 --
165 -- We need to trap the ORA LOCK exception
166 --
167 Exception
168 When HR_Api.Object_Locked then
169 --
170 -- The object is locked therefore we need to supply a meaningful
171 -- error message.
172 --
173 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
174 fnd_message.set_token('TABLE_NAME', 'pqp_exception_reports_tl');
175 fnd_message.raise_error;
176 End lck;
177 --
178 -- ----------------------------------------------------------------------------
179 -- |----------------------------< add_language >------------------------------|
180 -- ----------------------------------------------------------------------------
181 procedure ADD_LANGUAGE
182 is
183 begin
184 delete from PQP_EXCEPTION_REPORTS_TL T
185 where not exists
186 (select NULL
187 from PQP_EXCEPTION_REPORTS B
188 where B.EXCEPTION_REPORT_ID = T.EXCEPTION_REPORT_ID
189 );
190
191 update PQP_EXCEPTION_REPORTS_TL T set (
192 EXCEPTION_REPORT_NAME
193 ) = (select
194 B.EXCEPTION_REPORT_NAME
195 from PQP_EXCEPTION_REPORTS_TL B
196 where B.EXCEPTION_REPORT_ID = T.EXCEPTION_REPORT_ID
197 and B.LANGUAGE = T.SOURCE_LANG)
198 where (
199 T.EXCEPTION_REPORT_ID,
200 T.LANGUAGE
201 ) in (select
202 SUBT.EXCEPTION_REPORT_ID,
203 SUBT.LANGUAGE
204 from PQP_EXCEPTION_REPORTS_TL SUBB, PQP_EXCEPTION_REPORTS_TL SUBT
205 where SUBB.EXCEPTION_REPORT_ID = SUBT.EXCEPTION_REPORT_ID
206 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
207 and (SUBB.EXCEPTION_REPORT_NAME <> SUBT.EXCEPTION_REPORT_NAME
208 ));
209
210
211 insert into PQP_EXCEPTION_REPORTS_TL (
212 EXCEPTION_REPORT_ID,
213 EXCEPTION_REPORT_NAME,
214 LAST_UPDATED_BY,
215 LAST_UPDATE_DATE,
216 LAST_UPDATE_LOGIN,
217 CREATED_BY,
218 CREATION_DATE,
219 -- OBJECT_VERSION_NUMBER,
220 LANGUAGE,
221 SOURCE_LANG
222 ) select
223 B.EXCEPTION_REPORT_ID,
224 B.EXCEPTION_REPORT_NAME,
225 B.LAST_UPDATED_BY,
226 B.LAST_UPDATE_DATE,
227 B.LAST_UPDATE_LOGIN,
228 B.CREATED_BY,
229 B.CREATION_DATE,
230 -- B.OBJECT_VERSION_NUMBER,
231 L.LANGUAGE_CODE,
232 B.SOURCE_LANG
233 from PQP_EXCEPTION_REPORTS_TL B, FND_LANGUAGES L
234 where L.INSTALLED_FLAG in ('I', 'B')
235 and B.LANGUAGE = userenv('LANG')
236 and not exists
237 (select NULL
238 from PQP_EXCEPTION_REPORTS_TL T
239 where T.EXCEPTION_REPORT_ID = B.EXCEPTION_REPORT_ID
240 and T.LANGUAGE = L.LANGUAGE_CODE);
241 end ADD_LANGUAGE;
242 --
243 --
244 Procedure load_row (
245 p_exception_report_name IN VARCHAR2
246 ,p_legislation_code IN VARCHAR2
247 ,p_business_group_id IN NUMBER
248 ,p_currency_code IN VARCHAR2
249 ,p_balance_type_id IN NUMBER
250 ,p_balance_dimension_id IN NUMBER
251 ,p_variance_type IN VARCHAR2
252 ,p_variance_value IN NUMBER
253 ,p_comparison_type IN VARCHAR2
254 ,p_comparison_value IN NUMBER
255 ,p_exception_report_id IN OUT NOCOPY NUMBER
256 ,p_object_version_number IN OUT NOCOPY NUMBER
257 ,p_output_format_type IN VARCHAr2
258 ,p_variance_operator IN VARCHAr2
259 ,p_type IN VARCHAR2)
260 IS
261
262 begin
263
264 IF p_type='I' THEN
265
266 pqp_exr_api.create_exception_report (
267 p_exception_report_name => p_exception_report_name
268 ,p_legislation_code => p_legislation_code
269 ,p_business_group_id => p_business_group_id
270 ,p_currency_code => p_currency_code
271 ,p_balance_type_id => p_balance_type_id
272 ,p_balance_dimension_id => p_balance_dimension_id
273 ,p_variance_type => p_variance_type
274 ,p_variance_value => p_variance_value
275 ,p_comparison_type => p_comparison_type
276 ,p_comparison_value => p_comparison_value
277 ,p_exception_report_id => p_exception_report_id
278 ,p_object_version_number => p_object_version_number
279 ,p_output_format_type => p_output_format_type
280 ,p_variance_operator => p_variance_operator );
281 ELSE
282 pqp_exr_api.update_exception_report (
283 p_exception_report_name => p_exception_report_name
284 ,p_legislation_code => p_legislation_code
285 ,p_business_group_id => p_business_group_id
286 ,p_currency_code => p_currency_code
287 ,p_balance_type_id => p_balance_type_id
288 ,p_balance_dimension_id => p_balance_dimension_id
289 ,p_variance_type => p_variance_type
290 ,p_variance_value => p_variance_value
291 ,p_comparison_type => p_comparison_type
292 ,p_comparison_value => p_comparison_value
293 ,p_exception_report_id => p_exception_report_id
294 ,p_object_version_number => p_object_version_number
295 ,p_output_format_type => p_output_format_type
296 ,p_variance_operator => p_variance_operator );
297 END IF;
298 end;
299 -- ----------------------------------------------------------------------------
300 -- |-----------------------------< convert_args >-----------------------------|
301 -- ----------------------------------------------------------------------------
302 Function convert_args
303 (p_exception_report_id in number
304 ,p_exception_report_name in varchar2
305 ,p_language in varchar2
306 ,p_source_lang in varchar2
307 )
308 Return g_rec_type is
309 --
310 l_rec g_rec_type;
311 --
312 Begin
313 --
314 -- Convert arguments into local l_rec structure.
315 --
316 l_rec.exception_report_id := p_exception_report_id;
317 l_rec.exception_report_name := p_exception_report_name;
318 l_rec.language := p_language;
319 l_rec.source_lang := p_source_lang;
320 --
321 -- Return the plsql record structure.
322 --
323 Return(l_rec);
324 --
325 End convert_args;
326 --
327 end pqp_ert_shd;