[Home] [Help]
PACKAGE BODY: APPS.FEM_SIGNAGE_PKG
Source
1 PACKAGE BODY FEM_Signage_Pkg AS
2 -- $Header: fem_signage_utl.plb 120.0 2005/06/15 18:21:01 appldev noship $
3
4 /***************************************************************************
5
6 Package Variables
7
8 **************************************************************************/
9
10 c_user_id CONSTANT NUMBER := FND_GLOBAL.User_ID;
11 c_login_id CONSTANT NUMBER := FND_GLOBAL.Login_ID;
12 c_conc_prg_id CONSTANT NUMBER := FND_GLOBAL.Conc_Program_ID;
13 c_prg_app_id CONSTANT NUMBER := FND_GLOBAL.Prog_Appl_ID;
14
15 c_false CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
16 c_true CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
17
18 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
19 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
20 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
21 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
22 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
23 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
24
25 c_signage_method CONSTANT VARCHAR2(80) := FND_PROFILE.VALUE_SPECIFIC (
26 name => 'FEM_SIGNAGE_METHOD',
27 user_id => c_user_id);
28
29 f_set_status BOOLEAN;
30
31 /***************************************************************************
32
33 PROCEDURE: Sign_Ext_Acct_Types
34
35 **************************************************************************/
36
37 PROCEDURE Sign_Ext_Acct_Types (
38 errbuf OUT NOCOPY VARCHAR2,
39 retcode OUT NOCOPY VARCHAR2
40 )
41 IS
42
43 ------------------
44 -- Declarations --
45 ------------------
46 v_equity_sign NUMBER;
47 v_asset_sign NUMBER;
48 v_liab_sign NUMBER;
49 v_expense_sign NUMBER;
50 v_revenue_sign NUMBER;
51 v_na_sign NUMBER;
52 v_sign_val NUMBER;
53
54 v_mem_code VARCHAR2(30);
55 v_mem_attr VARCHAR2(30);
56
57 v_count NUMBER;
58 v_sign_attr NUMBER;
59 v_sign_vers NUMBER;
60 v_type_attr NUMBER;
61 v_type_vers NUMBER;
62 v_aw_flag VARCHAR2(1);
63 v_mem_b_tab VARCHAR2(30);
64
65 v_message VARCHAR2(4000);
66
67 v_block CONSTANT VARCHAR2(80) :=
68 'fem.plsql.fem_signage_pkg.ext_acct_types';
69
70 v_sql_cmd VARCHAR(32767);
71
72 CURSOR c_member IS
73 SELECT distinct ext_account_type_code,dim_attribute_varchar_member
74 FROM fem_ext_acct_types_attr
75 WHERE attribute_id = v_type_attr
76 ORDER BY ext_account_type_code;
77
78 e_no_sign_val EXCEPTION;
79 e_bad_sign_val EXCEPTION;
80 e_sign_vers EXCEPTION;
81 e_type_vers EXCEPTION;
82
83 BEGIN
84
85 FEM_ENGINES_PKG.TECH_MESSAGE
86 (p_severity => c_log_level_3,
87 p_module => v_block,
88 p_msg_text => 'Begin');
89
90 ---------------------------------------------
91 -- Set Signage Values based on Signage Method
92 ---------------------------------------------
93 FEM_ENGINES_PKG.TECH_MESSAGE
94 (p_severity => c_log_level_2,
95 p_module => v_block||'.signage_method',
96 p_msg_text => c_signage_method);
97
98 IF (c_signage_method IS NULL)
99 THEN
100 RAISE e_no_sign_val;
101
102 ELSIF (c_signage_method = 'ABSOLUTE_VALUE')
103 THEN
104 v_equity_sign := -1;
105 v_asset_sign := 1;
106 v_liab_sign := -1;
107 v_expense_sign := 1;
108 v_revenue_sign := -1;
109 v_na_sign := 1;
110
111 ELSIF (c_signage_method = 'GAAP_STANDARD')
112 THEN
113 v_equity_sign := 1;
114 v_asset_sign := 1;
115 v_liab_sign := 1;
116 v_expense_sign := 1;
117 v_revenue_sign := 1;
118 v_na_sign := 1;
119
120 ELSIF (c_signage_method = 'GAAP_REVERSE')
121 THEN
122 v_equity_sign := -1;
123 v_asset_sign := -1;
124 v_liab_sign := -1;
125 v_expense_sign := -1;
126 v_revenue_sign := -1;
127 v_na_sign := -1;
128
129 ELSE
130 RAISE e_bad_sign_val;
131 END IF;
132
133 FEM_ENGINES_PKG.TECH_MESSAGE
134 (p_severity => c_log_level_3,
135 p_module => v_block||'.equity_sign',
136 p_msg_text => v_equity_sign);
137 FEM_ENGINES_PKG.TECH_MESSAGE
138 (p_severity => c_log_level_3,
139 p_module => v_block||'.asset_sign',
140 p_msg_text => v_asset_sign);
141 FEM_ENGINES_PKG.TECH_MESSAGE
142 (p_severity => c_log_level_3,
143 p_module => v_block||'.liab_sign',
144 p_msg_text => v_liab_sign);
145 FEM_ENGINES_PKG.TECH_MESSAGE
146 (p_severity => c_log_level_3,
147 p_module => v_block||'.expense_sign',
148 p_msg_text => v_expense_sign);
149 FEM_ENGINES_PKG.TECH_MESSAGE
150 (p_severity => c_log_level_3,
151 p_module => v_block||'.revenue_sign',
152 p_msg_text => v_revenue_sign);
153 FEM_ENGINES_PKG.TECH_MESSAGE
154 (p_severity => c_log_level_3,
155 p_module => v_block||'.na_sign',
156 p_msg_text => v_na_sign);
157
158 -------------------------------
159 -- Verify SIGN Attribute Values
160 -------------------------------
161 SELECT min(aw_snapshot_flag),
162 min(attribute_id),
163 min(version_id),
164 count(*)
165 INTO v_aw_flag,
166 v_sign_attr,
167 v_sign_vers,
168 v_count
169 FROM fem_dim_attr_versions_b
170 WHERE attribute_id =
171 (SELECT attribute_id
172 FROM fem_dim_attributes_b
173 WHERE attribute_varchar_label = 'SIGN'
174 AND dimension_id =
175 (SELECT A.dimension_id
176 FROM fem_dimensions_b A
177 WHERE A.dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE'))
178 AND default_version_flag='Y';
179
180 FEM_ENGINES_PKG.TECH_MESSAGE
181 (p_severity => c_log_level_2,
182 p_module => v_block||'.sign_aw_snapshot_flag',
183 p_msg_text => v_aw_flag);
184 FEM_ENGINES_PKG.TECH_MESSAGE
185 (p_severity => c_log_level_2,
186 p_module => v_block||'.sign_attribute_id',
187 p_msg_text => v_sign_attr);
188 FEM_ENGINES_PKG.TECH_MESSAGE
189 (p_severity => c_log_level_2,
190 p_module => v_block||'.sign_version_id',
191 p_msg_text => v_sign_vers);
192 FEM_ENGINES_PKG.TECH_MESSAGE
193 (p_severity => c_log_level_2,
194 p_module => v_block||'.sign_version_count',
195 p_msg_text => v_count);
196
197 IF (v_count <> 1)
198 THEN
199 ------------------------------------------------------
200 -- SIGN Attribute does NOT have single default version
201 ------------------------------------------------------
202 RAISE e_sign_vers;
203 END IF;
204
205 ------------------------------------
206 -- Verify TYPE_CODE Attribute Values
207 ------------------------------------
208 SELECT min(attribute_id),
209 min(version_id),
210 count(*)
211 INTO v_type_attr,
212 v_type_vers,
213 v_count
214 FROM fem_dim_attr_versions_b
215 WHERE attribute_id =
216 (SELECT attribute_id
217 FROM fem_dim_attributes_b
218 WHERE attribute_varchar_label = 'BASIC_ACCOUNT_TYPE_CODE'
219 AND dimension_id =
220 (SELECT A.dimension_id
221 FROM fem_dimensions_b A
222 WHERE A.dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE'))
223 AND default_version_flag='Y';
224
225 FEM_ENGINES_PKG.TECH_MESSAGE
226 (p_severity => c_log_level_2,
227 p_module => v_block||'.type_attribute_id',
228 p_msg_text => v_type_attr);
229 FEM_ENGINES_PKG.TECH_MESSAGE
230 (p_severity => c_log_level_2,
231 p_module => v_block||'.type_version_id',
232 p_msg_text => v_type_vers);
233 FEM_ENGINES_PKG.TECH_MESSAGE
234 (p_severity => c_log_level_2,
235 p_module => v_block||'.type_version_count',
236 p_msg_text => v_count);
237
238 IF (v_count <> 1)
239 THEN
240 -----------------------------------------------------------
241 -- TYPE_CODE Attribute does NOT have single default version
242 -----------------------------------------------------------
243 RAISE e_type_vers;
244 END IF;
245
246 ----------------------------
247 -- Delete old signage values
248 ----------------------------
249 v_sql_cmd :=
250 'DELETE FROM fem_ext_acct_types_attr'||
251 ' WHERE attribute_id = :b_sign_attr'||
252 ' AND version_id = :b_sign_vers';
253
254 FEM_ENGINES_PKG.TECH_MESSAGE
255 (p_severity => c_log_level_1,
256 p_module => v_block||'.delete_sql_stmt',
257 p_msg_text => v_sql_cmd);
258
259 EXECUTE IMMEDIATE
260 v_sql_cmd
261 USING v_sign_attr,v_sign_vers;
262
263 ----------------------------
264 -- Insert new signage values
265 ----------------------------
266 FOR r_member IN c_member
267 LOOP
268 v_mem_code := r_member.ext_account_type_code;
269 v_mem_attr := r_member.dim_attribute_varchar_member;
270
271 FEM_ENGINES_PKG.TECH_MESSAGE
272 (p_severity => c_log_level_1,
273 p_module => v_block||'.acct_type_code',
274 p_msg_text => v_mem_code);
275 FEM_ENGINES_PKG.TECH_MESSAGE
276 (p_severity => c_log_level_1,
277 p_module => v_block||'.varchar_member',
278 p_msg_text => v_mem_attr);
279
280 CASE v_mem_attr
281 WHEN 'EQUITY' THEN v_sign_val := v_equity_sign;
282 WHEN 'ASSET' THEN v_sign_val := v_asset_sign;
283 WHEN 'LIABILITY' THEN v_sign_val := v_liab_sign;
284 WHEN 'EXPENSE' THEN v_sign_val := v_expense_sign;
285 WHEN 'REVENUE' THEN v_sign_val := v_revenue_sign;
286 WHEN 'NOT_APPLICABLE' THEN v_sign_val := v_na_sign;
287 WHEN 'STATISTICAL' THEN v_sign_val := 1;
288 ELSE null;
289 END CASE;
290
291 FEM_ENGINES_PKG.TECH_MESSAGE
292 (p_severity => c_log_level_1,
293 p_module => v_block||'.sign_value',
294 p_msg_text => v_sign_val);
295
296 v_sql_cmd := 'INSERT INTO fem_ext_acct_types_attr'||
297 '(attribute_id,version_id,ext_account_type_code'||
298 ',number_assign_value,creation_date,created_by'||
299 ',last_updated_by,last_update_date'||
300 ',object_version_number,aw_snapshot_flag)'||
301 ' VALUES '||
302 '('||v_sign_attr||','||v_sign_vers||','''||v_mem_code||''''||
303 ','||v_sign_val||','''||sysdate||''','||c_user_id||
304 ','||c_user_id||','''||sysdate||''''||
305 ',1,'''||v_aw_flag||''')';
306
307 FEM_ENGINES_PKG.TECH_MESSAGE
308 (p_severity => c_log_level_1,
309 p_module => v_block||'.insert_sql_stmt',
310 p_msg_text => v_sql_cmd);
311
312 EXECUTE IMMEDIATE
313 v_sql_cmd;
314
315 END LOOP;
316 COMMIT;
317
318 --------------------------
319 -- Post Completion Message
320 --------------------------
321 FEM_ENGINES_PKG.PUT_MESSAGE
322 (p_app_name => 'FEM',
323 p_msg_name => 'FEM_SIGN_COMPLETION_TXT');
324 v_message :=FND_MSG_PUB.GET(p_encoded => c_false);
325
326 FEM_ENGINES_PKG.TECH_MESSAGE
327 (p_severity => c_log_level_3,
328 p_module => v_block||'.Complete',
329 p_msg_text => v_message);
330
331 FEM_ENGINES_PKG.USER_MESSAGE
332 (p_msg_text => v_message);
333
334 ----------------
335 -- Exceptions --
336 ----------------
337 EXCEPTION
338
339 WHEN e_no_sign_val THEN
340
341 FEM_ENGINES_PKG.PUT_MESSAGE
342 (p_app_name => 'FEM',
343 p_msg_name => 'FEM_SIGN_NO_SIGN_VAL_ERR');
344
345 v_message := FND_MSG_PUB.GET(p_encoded => c_false);
346
347 FEM_ENGINES_PKG.TECH_MESSAGE
348 (p_severity => c_log_level_2,
349 p_module => v_block||'.no_sign_val',
350 p_msg_text => v_message);
351
352 FEM_ENGINES_PKG.USER_MESSAGE
353 (p_msg_text => v_message);
354
355 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
356
357 WHEN e_bad_sign_val THEN
358
359 FEM_ENGINES_PKG.PUT_MESSAGE
360 (p_app_name => 'FEM',
361 p_msg_name => 'FEM_SIGN_BAD_SIGN_VAL_ERR',
362 p_token1 => 'SIGN',
363 p_value1 => c_signage_method);
364
365 v_message := FND_MSG_PUB.GET(p_encoded => c_false);
366
367 FEM_ENGINES_PKG.TECH_MESSAGE
368 (p_severity => c_log_level_2,
369 p_module => v_block||'.bad_sign_val',
370 p_msg_text => v_message);
371
372 FEM_ENGINES_PKG.USER_MESSAGE
373 (p_msg_text => v_message);
374
375 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
376
377 WHEN e_sign_vers THEN
378
379 FEM_ENGINES_PKG.PUT_MESSAGE
380 (p_app_name => 'FEM',
381 p_msg_name => 'FEM_SIGN_BAD_SIGN_VER_ERR');
382
383 v_message := FND_MSG_PUB.GET(p_encoded => c_false);
384
385 FEM_ENGINES_PKG.TECH_MESSAGE
386 (p_severity => c_log_level_2,
387 p_module => v_block||'.bad_sign_ver',
388 p_msg_text => v_message);
389
390 FEM_ENGINES_PKG.USER_MESSAGE
391 (p_msg_text => v_message);
392
393 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
394
395 WHEN e_type_vers THEN
396
397 FEM_ENGINES_PKG.PUT_MESSAGE
398 (p_app_name => 'FEM',
399 p_msg_name => 'FEM_SIGN_BAD_TYPE_VER_ERR');
400
401 v_message := FND_MSG_PUB.GET(p_encoded => c_false);
402
403 FEM_ENGINES_PKG.TECH_MESSAGE
404 (p_severity => c_log_level_2,
405 p_module => v_block||'.bad_type_ver',
406 p_msg_text => v_message);
407
408 FEM_ENGINES_PKG.USER_MESSAGE
409 (p_msg_text => v_message);
410
411 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
412
413 WHEN others THEN
414
415 v_message := sqlerrm;
416
417 FEM_ENGINES_PKG.TECH_MESSAGE
418 (p_severity => c_log_level_6,
419 p_module => v_block||'.Exception',
420 p_msg_text => sqlerrm);
421
422 FEM_ENGINES_PKG.USER_MESSAGE
423 (p_msg_text => v_message);
424
425 END Sign_Ext_Acct_Types;
426
427 END FEM_Signage_Pkg;