[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_ARCHIVE_DBI_PKG
Source
1 PACKAGE BODY pay_mx_archive_dbi_pkg AS
2 /* $Header: pymxarchdbipkg.pkb 120.0 2005/09/29 13:59:54 vmehta noship $ */
3
4 -------------------------------------------------------------------------
5 -- This procedure creates the archive routes needed by the
6 -- create_archive_dbi procedure
7 -------------------------------------------------------------------------
8 PROCEDURE create_archive_routes IS
9
10 l_text LONG;
11 l_gre_context_id NUMBER;
12 l_assignment_action_context_id NUMBER;
13 l_exists VARCHAR2(1);
14
15 BEGIN
16
17 -- Find the Context ID's
18 SELECT context_id
19 INTO l_assignment_action_context_id
20 FROM ff_contexts
21 WHERE context_name = 'ASSIGNMENT_ACTION_ID';
22
23 SELECT context_id
24 INTO l_gre_context_id
25 FROM ff_contexts
26 WHERE context_name = 'TAX_UNIT_ID';
27
28 -------------------------------------------------------------------------
29 -- Define the Balances archive route
30 -------------------------------------------------------------------------
31 BEGIN
32
33 l_text :=
34 ' ff_archive_items target,
35 ff_archive_item_contexts fac
36 where target.user_entity_id = &U1
37 and target.context1 = &B1 /* context assignment action id */
38 and fac.archive_item_id = target.archive_item_id
39 and fac.context = &B2 /* 2nd context of tax_unit_id */';
40
41 SELECT 'Y'
42 INTO l_exists
43 FROM ff_routes
44 WHERE route_name = 'MX_BALANCES_ARCHIVE_ROUTE';
45
46 UPDATE ff_routes
47 SET text = l_text
48 WHERE route_name = 'MX_BALANCES_ARCHIVE_ROUTE';
49
50 EXCEPTION WHEN NO_DATA_FOUND THEN
51
52 INSERT INTO ff_routes
53 (
54 route_id,
55 route_name,
56 user_defined_flag,
57 description,
58 text,
59 last_update_date,
60 last_updated_by,
61 last_update_login,
62 created_by,
63 creation_date
64 )
65 VALUES
66 (
67 ff_routes_s.NEXTVAL,
68 'MX_BALANCES_ARCHIVE_ROUTE',
69 'N',
70 'MX Year End Balances Archive Route',
71 l_text,
72 SYSDATE,
73 0,
74 0,
75 0,
76 SYSDATE
77 );
78
79 -- Define the route parameter
80 INSERT INTO ff_route_parameters
81 (
82 route_parameter_id,
83 route_id,
84 data_type,
85 parameter_name,
86 sequence_no
87 )
88 SELECT ff_route_parameters_s.NEXTVAL,
89 ff_routes_s.CURRVAL,
90 'N',
91 'User Entity ID',
92 1
93 FROM dual;
94
95 -- Define the route context usage
96 INSERT INTO ff_route_context_usages
97 (
98 route_id,
99 context_id,
100 sequence_no
101 )
102 SELECT ff_routes_s.CURRVAL,
103 l_assignment_action_context_id,
104 1
105 FROM dual;
106
107 INSERT INTO ff_route_context_usages
108 (
109 route_id,
110 context_id,
111 sequence_no
112 )
113 SELECT ff_routes_s.CURRVAL,
114 l_gre_context_id,
115 2
116 from dual;
117
118 END;
119
120
121 END create_archive_routes;
122
123 -------------------------------------------------------------------------
124 -- This procedure creates an archive database item, for the live database
125 -- item that is passed as a parameter
126 -- Note: p_item_name should be A_ and then the name of the live database
127 -- item
128 -------------------------------------------------------------------------
129 PROCEDURE create_archive_dbi(p_item_name VARCHAR2) is
130
131 -- Find the attributes from the live database item and create an
132 -- arcive version of it
133
134 l_dbi_null_allowed_flag VARCHAR2(1);
135 l_dbi_description VARCHAR2(240);
136 l_dbi_data_type VARCHAR2(1);
137 l_dbi_user_name VARCHAR2(240);
138 l_ue_notfound_allowed_flag VARCHAR2(1);
139 l_ue_creator_type VARCHAR2(30);
140 l_ue_entity_description VARCHAR2(240);
141 l_user_entity_seq NUMBER;
142 l_user_entity_id NUMBER;
143 l_route_parameter_id NUMBER;
144 l_dummy_id NUMBER;
145 l_route_id NUMBER;
146 l_live_route_id NUMBER;
147 l_balances_route NUMBER;
148 l_definition_text VARCHAR2(240);
149
150 BEGIN
151
152 BEGIN
153
154 -- Check whether the MX database item exists
155 SELECT ue.notfound_allowed_flag,
156 ue.creator_type,
157 ue.entity_description,
158 ue.route_id,
159 dbi.null_allowed_flag,
160 dbi.description ,
161 dbi.data_type,
162 dbi.user_name
163 INTO l_ue_notfound_allowed_flag,
164 l_ue_creator_type,
165 l_ue_entity_description,
166 l_live_route_id,
167 l_dbi_null_allowed_flag,
168 l_dbi_description,
169 l_dbi_data_type,
170 l_dbi_user_name
171 FROM ff_database_items dbi,
172 ff_user_entities ue
173 WHERE dbi.user_name = SUBSTR(p_item_name, 3, LENGTH(p_item_name) - 2)
174 AND dbi.user_entity_id = ue.user_entity_id
175 AND ue.legislation_code = 'MX'
176 AND ue.business_group_id IS NULL;
177
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 -- Check whether the core database item exists
181 SELECT ue.notfound_allowed_flag,
182 ue.creator_type,
183 ue.entity_description,
184 ue.route_id,
185 dbi.null_allowed_flag,
186 dbi.description,
187 dbi.data_type,
188 dbi.user_name
189 INTO l_ue_notfound_allowed_flag,
190 l_ue_creator_type,
191 l_ue_entity_description,
192 l_live_route_id,
193 l_dbi_null_allowed_flag,
194 l_dbi_description,
195 l_dbi_data_type,
196 l_dbi_user_name
197 FROM ff_database_items dbi,
198 ff_user_entities ue
199 WHERE dbi.user_name = SUBSTR(p_item_name, 3, LENGTH(p_item_name) - 2)
200 AND dbi.user_entity_id = ue.user_entity_id
201 AND ue.legislation_code IS NULL
202 AND ue.business_group_id IS NULL;
203
204 END;
205
206 /* SELECT route_id
207 INTO l_number_archive_route_id
208 FROM ff_routes
209 WHERE route_name = 'MX_NUMBER_ARCHIVE_ROUTE';
210
211 SELECT route_id
212 INTO l_date_archive_route_id
213 FROM ff_routes
214 WHERE route_name = 'MX_DATE_ARCHIVE_ROUTE';
215
216 SELECT route_id
217 INTO l_character_archive_route_id
218 FROM ff_routes
219 WHERE route_name = 'MX_CHARACTER_ARCHIVE_ROUTE';
220 */
221 SELECT route_id
222 INTO l_balances_route
223 FROM ff_routes
224 WHERE route_name = 'MX_BALANCES_ARCHIVE_ROUTE';
225
226 -- Choose the archive route, based on the live db item's data type
227 IF l_dbi_data_type = 'N' THEN
228
229 l_definition_text := 'to_number(target.value)';
230 l_route_id := l_balances_route;
231
232 END IF;
233
234 -- Find the User Entity Route parameter that goes with the archive route
235 SELECT route_parameter_id
236 INTO l_route_parameter_id
237 FROM ff_route_parameters
238 WHERE parameter_name = 'User Entity ID'
239 AND route_id = l_route_id;
240
241 BEGIN
242
243 -- Check to see if the archive database item already exist
244 SELECT user_entity_id
245 INTO l_user_entity_seq
246 FROM ff_user_entities
247 WHERE user_entity_name = p_item_name
248 AND legislation_code = 'MX'
249 AND business_group_id IS NULL;
250
251 -- Commented because FF_USER_ENTITIES_BRU doesn't allow update
252 --
253 -- UPDATE ff_user_entities
254 -- SET route_id = l_route_id,
255 -- notfound_allowed_flag = 'Y', -- l_ue_notfound_allowed_flag,
256 -- entity_description = SUBSTR('Archive of ' ||
257 -- l_ue_entity_description, 1, 240)
258 -- WHERE user_entity_name = p_item_name
259 -- AND legislation_code = 'MX'
260 -- AND business_group_id IS NULL;
261
262 BEGIN
263
264 SELECT route_parameter_id
265 INTO l_dummy_id
266 FROM ff_route_parameter_values
267 WHERE route_parameter_id = l_route_parameter_id
268 AND user_entity_id = l_user_entity_seq;
269
270 UPDATE ff_route_parameter_values
271 SET value = l_user_entity_seq
272 WHERE route_parameter_id = l_route_parameter_id
273 AND user_entity_id = l_user_entity_seq;
274
275 EXCEPTION WHEN NO_DATA_FOUND THEN
276
277 INSERT INTO ff_route_parameter_values
278 (
279 route_parameter_id,
280 user_entity_id,
281 value,
282 last_update_date,
283 last_updated_by,
284 last_update_login,
285 created_by,
286 creation_date
287 )
288 VALUES
289 (
290 l_route_parameter_id,
291 l_user_entity_seq,
292 l_user_entity_seq,
293 SYSDATE,
294 0,
295 0,
296 0,
297 SYSDATE
298 );
299
300 END;
301
302 UPDATE ff_database_items
303 SET user_entity_id = l_user_entity_seq,
304 data_type = l_dbi_data_type,
305 definition_text = l_definition_text,
306 null_allowed_flag = 'Y', -- l_dbi_null_allowed_flag,
307 description = SUBSTR('Archive of item ' ||
308 l_dbi_description, 1, 240)
309 WHERE user_name = p_item_name;
310
311 EXCEPTION WHEN NO_DATA_FOUND THEN
312
313 -- Create the archive database item
314 SELECT ff_user_entities_s.NEXTVAL
315 INTO l_user_entity_seq
316 FROM dual;
317
318 INSERT INTO ff_user_entities
319 (
320 user_entity_id,
321 business_group_id,
322 legislation_code,
323 route_id,
324 notfound_allowed_flag,
325 user_entity_name,
326 creator_id,
327 creator_type,
328 entity_description,
329 last_update_date,
330 last_updated_by,
331 last_update_login,
332 created_by,
333 creation_date
334 )
335 VALUES
336 (
337 l_user_entity_seq, -- user_entity_id
338 NULL, -- business_group_id
339 'MX', -- legislation_code
340 l_route_id, -- route_id
341 'Y', -- l_ue_notfound_allowed_flag,
342 p_item_name, -- user_entity_name
343 0, -- creator_id
344 'X', -- archive extract creator_type
345 SUBSTR('Archive of ' ||
346 l_ue_entity_description, 1, 240),-- entity_description
347 SYSDATE, -- last_update_date
348 0, -- last_updated_by
349 0, -- last_update_login
350 0, -- created_by
351 SYSDATE -- creation_date
352 );
353
354 INSERT INTO ff_route_parameter_values
355 (
356 route_parameter_id,
357 user_entity_id,
358 value,
359 last_update_date,
360 last_updated_by,
361 last_update_login,
362 created_by,
363 creation_date
364 )
365 VALUES
366 (
367 l_route_parameter_id,
368 l_user_entity_seq,
369 l_user_entity_seq,
370 SYSDATE,
371 0,
372 0,
373 0,
374 SYSDATE
375 );
376
377 INSERT INTO ff_database_items
378 (
379 user_name,
380 user_entity_id,
381 data_type,
382 definition_text,
383 null_allowed_flag,
384 description,
385 last_update_date,
386 last_updated_by,
387 last_update_login,
388 created_by,
389 creation_date
390 )
391 VALUES
392 (
393 p_item_name,
394 l_user_entity_seq,
395 l_dbi_data_type,
396 l_definition_text,
397 'Y', -- l_dbi_null_allowed_flag,
398 SUBSTR('Archive of item ' || l_dbi_description, 1, 240),
399 SYSDATE,
400 0,
401 0,
402 0,
403 SYSDATE
404 );
405
406 END;
407
408 END create_archive_dbi;
409
410 END pay_mx_archive_dbi_pkg;