1 PACKAGE BODY FF_LOAD_FTYPES_PKG as
2 /* $Header: ffftypapi.pkb 115.1 2004/06/29 05:01 sspratur noship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := ' ff_load_ftypes_pkg.'; -- Global package name
8 --
9 --
10 -- ----------------------------------------------------------------------------
11 -- | Private Procedures |
12 -- ----------------------------------------------------------------------------
13 -- Called from load row and will insert new rows into ff_formula_types table
14 Procedure insert_formula_types(
15 p_formula_type_name in ff_formula_types.formula_type_name%TYPE
16 ,p_type_description in ff_formula_types.type_description%TYPE
17 ,p_created_by in ff_formula_types.CREATED_BY%TYPE
18 ,p_creation_date in ff_formula_types.CREATION_DATE%TYPE
19 ,p_last_update_date in ff_formula_types.LAST_UPDATE_DATE%TYPE
20 ,p_last_updated_by in ff_formula_types.LAST_UPDATED_BY%TYPE
21 ,p_last_update_login in ff_formula_types.LAST_UPDATE_LOGIN%TYPE);
22
23 -- Called from load row and will update existing rows in ff_formula_types table
24 Procedure update_formula_types(
25 p_formula_type_name in ff_formula_types.formula_type_name%TYPE
26 ,p_type_description in ff_formula_types.type_description%TYPE
27 ,p_last_update_date in ff_formula_types.LAST_UPDATE_DATE%TYPE
28 ,p_last_updated_by in ff_formula_types.LAST_UPDATED_BY%TYPE
29 ,p_last_update_login in ff_formula_types.LAST_UPDATE_LOGIN%TYPE);
30
31 -- Called from load_row_context_usages and will insert new rows
32 -- into FF_FTYPE_CONTEXT_USAGES table
33 Procedure insert_fcontext_usages(
34 p_formula_type_name in FF_FORMULA_TYPES.formula_type_name%TYPE
35 ,p_context_name in FF_CONTEXTS.context_name%TYPE);
36
37 -- Called from insert_fcontext_usages and will
38 -- check if the formula type id is valid one......
39 Function chk_formula_type_name(p_formula_type_name in FF_FORMULA_TYPES.formula_type_name%TYPE) Return Number;
40
41 -- Called from insert_fcontext_usages and will
42 -- check if the context is valid one......
43 Function chk_context_name(p_context_name in FF_CONTEXTS.context_name%TYPE) Return Number;
44 -- ----------------------------------------------------------------------------
45 -- |---------------------------< LOAD_ROW >------------------------------------|
46 -- ----------------------------------------------------------------------------
47 Procedure load_row (
48 p_formula_type_name in ff_formula_types.formula_type_name%TYPE
49 ,p_type_description in ff_formula_types.type_description%TYPE
50 ) is
51 --
52 l_existing_form_id number;
53 --WHO variables
54 l_sysdate date := sysdate;
55 l_created_by ff_formula_types.CREATED_BY%TYPE;
56 l_creation_date ff_formula_types.CREATION_DATE%TYPE;
57 l_last_updated_by ff_formula_types.LAST_UPDATED_BY%TYPE;
58 l_last_update_login ff_formula_types.LAST_UPDATE_LOGIN%TYPE;
59 l_last_update_date ff_formula_types.LAST_UPDATE_DATE%TYPE;
60 --
61 l_proc varchar2(100) := g_package || 'load_row';
62 --
63 --Cursor to see if the existing formula type is updated.....
64 cursor csr_existing is
65 select fft.formula_type_id
66 from ff_formula_types fft
67 where fft.formula_type_name = p_formula_type_name;
68
69 BEGIN
70
71 hr_utility.set_location('Entering:'|| l_proc, 10);
72 -- Set the WHO Columns
73 l_created_by := fnd_global.user_id;
74 l_creation_date := l_sysdate;
75 l_last_update_date := l_sysdate;
76 l_last_updated_by := fnd_global.user_id;
77 l_last_update_login := fnd_global.login_id;
78
79
80 open csr_existing;
81 fetch csr_existing into l_existing_form_id;
82 if csr_existing%FOUND
83 then
84 close csr_existing;
85 update_formula_types(
86 p_formula_type_name => p_formula_type_name
87 ,p_type_description => p_type_description
88 ,p_last_update_date => l_last_update_date
89 ,p_last_updated_by => l_last_updated_by
90 ,p_last_update_login => l_last_update_login);
91 else
92 close csr_existing;
93 -- This is not an update
94 -- call the insert procedure
95 --
96 insert_formula_types(
97 p_formula_type_name => p_formula_type_name
98 ,p_type_description => p_type_description
99 ,p_created_by => l_created_by
100 ,p_creation_date => l_creation_date
101 ,p_last_update_date => l_last_update_date
102 ,p_last_updated_by => l_last_updated_by
103 ,p_last_update_login => l_last_update_login);
104
105 end if;
106 --
107 -- do not pass back any out parameters from the API calls
108 --
109 hr_utility.set_location('Leaving:'|| l_proc, 20);
110 end load_row;
111
112 --
113 -- -------------------------------------------------------------------------------------------
114 -- |---------------------------< load_row_context_usages >------------------------------------|
115 -- -------------------------------------------------------------------------------------------
116 Procedure load_row_context_usages (
117 p_formula_type_name in FF_FORMULA_TYPES.formula_type_name%TYPE
118 ,p_context_name in FF_CONTEXTS.context_name%TYPE)
119 is
120 --
121 l_exist_form_con_id number;
122 l_proc varchar2(100) := g_package || 'load_row_context_usages';
123
124 --Cursor to see if the formula type context usage is existing.....
125 Cursor csr_existing IS
126 SELECT fcu.FORMULA_TYPE_ID
127 FROM FF_FTYPE_CONTEXT_USAGES fcu
128 ,FF_FORMULA_TYPES fft
129 ,FF_CONTEXTS fco
130 WHERE fcu.FORMULA_TYPE_ID = fft.FORMULA_TYPE_ID
131 AND fcu.context_id = fco.context_id
132 AND fft.FORMULA_TYPE_NAME = p_formula_type_name
133 AND fco.context_name = p_context_name;
134
135 BEGIN
136 --
137 hr_utility.set_location('Entering:'|| l_proc, 10);
138
139 open csr_existing;
140 fetch csr_existing into l_exist_form_con_id;
141 if csr_existing%NOTFOUND
142 then
143 close csr_existing;
144 -- call the insert procedure
145 --
146 insert_fcontext_usages(
147 p_formula_type_name => p_formula_type_name
148 ,p_context_name => p_context_name);
149
150 end if;
151 --
152 -- do not pass back any out parameters from the API calls
153 --
154 hr_utility.set_location('Leaving:'|| l_proc, 20);
155
156 end load_row_context_usages;
157
158 --
159 -- ----------------------------------------------------------------------------
160 -- | Private Procedures |
161 -- ----------------------------------------------------------------------------
162 -- Called from load row and will insert new rows into ff_formula_types table
163 Procedure insert_formula_types(
164 p_formula_type_name in ff_formula_types.formula_type_name%TYPE
165 ,p_type_description in ff_formula_types.type_description%TYPE
166 ,p_created_by in ff_formula_types.CREATED_BY%TYPE
167 ,p_creation_date in ff_formula_types.CREATION_DATE%TYPE
168 ,p_last_update_date in ff_formula_types.LAST_UPDATE_DATE%TYPE
169 ,p_last_updated_by in ff_formula_types.LAST_UPDATED_BY%TYPE
170 ,p_last_update_login in ff_formula_types.LAST_UPDATE_LOGIN%TYPE) Is
171 l_proc varchar2(100) := g_package || 'insert_formula_types';
172 Begin
173
174 --
175 hr_utility.set_location('Entering:'|| l_proc, 10);
176 --Insert into ff_formula_types table
177 Insert Into FF_FORMULA_TYPES(
178 FORMULA_TYPE_ID
179 ,FORMULA_TYPE_NAME
180 ,TYPE_DESCRIPTION
181 ,LAST_UPDATE_DATE
182 ,LAST_UPDATED_BY
183 ,LAST_UPDATE_LOGIN
184 ,CREATED_BY
185 ,CREATION_DATE) Values
189 ,p_last_update_date
186 (FF_FORMULA_TYPES_S.NEXTVAL
187 ,p_formula_type_name
188 ,p_type_description
190 ,p_last_updated_by
191 ,p_last_update_login
192 ,p_created_by
193 ,p_creation_date);
194 --
195 hr_utility.set_location('Leaving:'|| l_proc, 20);
196 End insert_formula_types;
197 --
198 -- Called from load row and will update existing rows in ff_formula_types table
199 Procedure update_formula_types(
200 p_formula_type_name in ff_formula_types.formula_type_name%TYPE
201 ,p_type_description in ff_formula_types.type_description%TYPE
202 ,p_last_update_date in ff_formula_types.LAST_UPDATE_DATE%TYPE
203 ,p_last_updated_by in ff_formula_types.LAST_UPDATED_BY%TYPE
204 ,p_last_update_login in ff_formula_types.LAST_UPDATE_LOGIN%TYPE) Is
205 --
206 l_proc varchar2(100) := g_package || 'update_formula_types';
207
208 Begin
209 hr_utility.set_location('Entering:'|| l_proc, 10);
210 --
211 Update ff_formula_types
212 set type_description = p_type_description
213 ,last_update_date = p_last_update_date
214 ,last_updated_by = p_last_updated_by
215 ,last_update_login = p_last_update_login
216 Where formula_type_name = p_formula_type_name;
217 --
218 hr_utility.set_location('Leaving:'|| l_proc, 20);
219 End update_formula_types;
220 --
221
222 --
223 -- Called from load_row_context_usages and will insert new rows into FF_FTYPE_CONTEXT_USAGES table
224 Procedure insert_fcontext_usages(
225 p_formula_type_name in FF_FORMULA_TYPES.formula_type_name%TYPE
226 ,p_context_name in FF_CONTEXTS.context_name%TYPE) IS
227
228 l_proc varchar2(100) := g_package || 'insert_fcontext_usages';
229
230 l_formula_type_id FF_FORMULA_TYPES.formula_type_id%TYPE := 0;
231 l_context_id FF_CONTEXTS.context_name%TYPE := 0;
232 Begin
233 hr_utility.set_location('Entering:'|| l_proc, 10);
234 --Check if the formula type id is valid one......
238 l_context_id := chk_context_name(p_context_name => p_context_name);
235 l_formula_type_id := chk_formula_type_name(p_formula_type_name => p_formula_type_name);
236
237 --Check if the context is valid one......
239
240 If (l_formula_type_id <> 0 and l_context_id <> 0) Then
241 Insert into FF_FTYPE_CONTEXT_USAGES
242 (formula_type_id
243 ,context_id)
244 Values (l_formula_type_id
245 ,l_context_id);
246 End If;
247 --
248 hr_utility.set_location('Leaving:'|| l_proc, 20);
249
250 End insert_fcontext_usages;
251 --
252
253 -- Called from insert_fcontext_usages and will
254 -- check if the formula type id is valid one, also will return the formula_type_id......
255 Function chk_formula_type_name(p_formula_type_name in FF_FORMULA_TYPES.formula_type_name%TYPE)
256 Return Number
257 IS
258 --Cursor to see if the formula_type_id exists
259 Cursor csr_ftype_id Is
260 Select formula_type_id
261 From FF_FORMULA_TYPES
262 Where formula_type_name = p_formula_type_name;
263
264 --local variable
265 l_formula_type_id NUMBER;
266 l_proc varchar2(100) := g_package || 'chk_fomula_type_id';
267 --
268 BEGIN
269 --
270 hr_utility.set_location('Entering:'|| l_proc, 10);
271
272 OPEN csr_ftype_id;
273 FETCH csr_ftype_id into l_formula_type_id;
274 If csr_ftype_id%notfound Then
275 CLOSE csr_ftype_id;
276 fnd_message.set_name('FF', 'FF_34862_INV_FORMULA_TYPE');
277 fnd_message.set_token('FORMULA_TYPE',p_formula_type_name);
278 fnd_message.raise_error;
279 return 0;
280 End if;
281 CLOSE csr_ftype_id;
282
283 hr_utility.set_location(' Leaving:'|| l_proc, 20);
284 return l_formula_type_id;
285 --
286 End chk_formula_type_name;
287
288 -- Called from insert_fcontext_usages and will
289 -- check if the context is valid one, also will return the contextid......
290 Function chk_context_name(p_context_name in FF_CONTEXTS.context_name%TYPE)
291 Return Number
292 IS
293 --Cursor to see if the formula_type_id exists
294 Cursor csr_fcon_id Is
295 Select context_id
296 From FF_CONTEXTS
297 Where context_name = p_context_name;
298
299 --local variable
300 l_context_id NUMBER;
301 l_proc varchar2(100) := g_package || 'chk_context_name';
302 --
303 BEGIN
304 --
305 hr_utility.set_location('Entering:'|| l_proc, 10);
306
307 OPEN csr_fcon_id;
308 FETCH csr_fcon_id into l_context_id;
309 If csr_fcon_id%notfound Then
310 CLOSE csr_fcon_id;
311 fnd_message.set_name('FF', 'FF_34861_INV_CONTEXT_TYPE');
312 fnd_message.set_token('CONTEXT',p_context_name);
313 fnd_message.raise_error;
314 return 0;
315 End if;
316 CLOSE csr_fcon_id;
317
318 hr_utility.set_location(' Leaving:'|| l_proc, 20);
319 return l_context_id;
320 --
321 End chk_context_name;
322
323 ------------------------------------------------------------------------------------------------
324 End ff_load_ftypes_pkg;
[Home] [Help]