DBA Data[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;