[Home] [Help]
PACKAGE BODY: APPS.FV_BE_PKG4
Source
1 package body fv_be_pkg4 as
2 -- $Header: FVBEPG3B.pls 120.7 2005/07/12 22:14:54 snama ship $
3 g_module_name VARCHAR2(100) := 'fv.plsql.FV_BE_PKG4.';
4
5 PROCEDURE re_seq_budget_levels (
6 x_error_code OUT NOCOPY number,
7 x_set_of_books_id IN number)
8 IS
9 l_module_name VARCHAR2(200) := g_module_name || 're_seq_budget_levels';
10 l_errbuf VARCHAR2(1024);
11 v_sequence number := 1;
12
13 CURSOR C IS
14 SELECT BUDGET_LEVEL_NUM, POST_FLAG
15 FROM FV_BUDGET_LEVELS
16 WHERE FV_BUDGET_LEVELS.SET_OF_BOOKS_ID = x_set_of_books_id
17 ORDER BY BUDGET_LEVEL_NUM
18 FOR UPDATE OF BUDGET_LEVEL_ID;
19
20 v_budget_level_num FV_BUDGET_LEVELS.BUDGET_LEVEL_NUM%TYPE;
21 v_post_flag FV_BUDGET_LEVELS.POST_FLAG%TYPE;
22 BEGIN
23
24 OPEN C;
25 LOOP
26 -- FETCH THE NEXT BUGET_LEVEL_NUM
27 FETCH C INTO v_budget_level_num, v_post_flag;
28
29 -- EXIT LOOP WHEN THERE ARE NO MORE ROWS TO FETCH
30 EXIT WHEN C%notfound;
31
32 UPDATE FV_BUDGET_LEVELS
33 SET BUDGET_LEVEL_ID = v_sequence,
34 POST_FLAG = DECODE(v_sequence, 1, 'Y', 2, 'Y', v_post_flag)
35 WHERE CURRENT OF C;
36
37 v_sequence := v_sequence + 1;
38 END LOOP;
39 COMMIT;
40
41 CLOSE C;
42
43 x_error_code := -1;
44 EXCEPTION
45 WHEN OTHERS THEN
46 l_errbuf := SQLERRM;
47 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
48 RAISE;
49 END re_seq_budget_levels;
50 --------------------------------------------------------------------
51
52
53 procedure get_budget_description (x_budget_level_id in number,
54 x_set_of_bks_id in number,
55 x_description OUT NOCOPY varchar2,
56 x_error_code OUT NOCOPY number)
57 is
58 l_module_name VARCHAR2(200) := g_module_name || 'get_budget_description';
59 l_errbuf VARCHAR2(1024);
60
61 begin
62
63 select description
64 into x_description
65 from fv_budget_levels
66 where budget_level_id = x_budget_level_id
67 and set_of_books_id = x_set_of_bks_id ;
68
69 exception
70 when no_data_found then
71 l_errbuf := SQLERRM;
72 x_error_code := -1;
73 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data',l_errbuf);
74
75 WHEN OTHERS THEN
76 l_errbuf := SQLERRM;
77 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
78 RAISE;
79 End get_budget_description;
80 ---------------------------------------------------------------
81
82 procedure get_trans_description(x_transaction_type in varchar2,
83 x_set_of_bks_id in number,
84 x_trans_description OUT NOCOPY varchar2,
85 x_error_code OUT NOCOPY number)
86 is
87 l_module_name VARCHAR2(200) := g_module_name || 'get_trans_description';
88 l_errbuf VARCHAR2(1024);
89 begin
90 select description
91 into x_trans_description
92 from fv_be_transaction_types
93 where apprn_transaction_type = x_transaction_type
94 and set_of_books_id = x_set_of_bks_id;
95
96 exception
97 when no_data_found then
98 x_error_code := -1;
99
100 WHEN OTHERS THEN
101 l_errbuf := SQLERRM;
102 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
103 RAISE;
104 end get_trans_description;
105
106
107 ----------------------------------------------------------------
108 procedure get_user_name (x_error_code OUT NOCOPY number,
109 x_user_name OUT NOCOPY varchar2,
110 x_user_id IN number)
111 is
112 l_module_name VARCHAR2(200) := g_module_name || 'get_user_name';
113 l_errbuf VARCHAR2(1024);
114
115 begin
116 SELECT USER_NAME
117 INTO x_user_name
118 FROM FND_USER
119 WHERE FND_USER.USER_ID = x_user_id;
120
121 exception
122 when no_data_found then
123 x_error_code := -1;
124
125 WHEN OTHERS THEN
126 l_errbuf := SQLERRM;
127 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
128 RAISE;
129 End get_user_name;
130 ----------------------------------------------------------------
131
132 procedure get_resource_type_desc (x_resource_type IN varchar2,
133 x_lookup_type IN varchar2,
134 x_description OUT NOCOPY varchar2,
135 x_error_code OUT NOCOPY number)
136 is
137 l_module_name VARCHAR2(200) := g_module_name || 'get_resource_type_desc';
138 l_errbuf VARCHAR2(1024);
139
140 begin
141 select description
142 into x_description
143 from fv_lookup_codes
144 where lookup_code = x_resource_type and
145 lookup_type = x_lookup_type;
146 exception
147 when no_data_found then
148 x_error_code := -1;
149
150 WHEN OTHERS THEN
151 l_errbuf := SQLERRM;
152 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
153 RAISE;
154 end get_resource_type_desc;
155 ----------------------------------------------------------------
156 procedure create_journal_category (P_SET_OF_BKS_ID IN NUMBER,
157 P_ERR_CODE OUT NOCOPY NUMBER) IS
158 l_module_name VARCHAR2(200) := g_module_name || 'create_journal_category';
159 l_errbuf VARCHAR2(1024);
160
161 CURSOR je_category_cur IS
162 select fvbl.description
163 from fv_budget_levels fvbl
164 where fvbl.set_of_books_id = P_SET_OF_BKS_ID
165 and not exists (
166 select glc.je_category_name
167 from gl_je_categories glc
168 where glc.je_category_name = fvbl.description);
169 begin
170
171 FOR je_category_cur_rec IN je_category_cur LOOP
172
173 gl_je_categories_pkg.load_row(je_category_cur_rec.description,
174 NULL,
175 je_category_cur_rec.description,
176 je_category_cur_rec.description,
177 NULL,
178 NULL,
179 NULL,
180 NULL,
181 NULL,
182 NULL,
183 'SEED',
184 NULL);
185
186 END LOOP;
187 commit;
188
189 EXCEPTION
190 when others then
191 l_errbuf := SQLERRM;
192 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
193 close je_category_cur;
194 p_err_code := -1;
195
196
197 END;
198
199 End fv_be_pkg4 ;