[Home] [Help]
PACKAGE BODY: APPS.CN_OBJECTS_PKG
Source
1 PACKAGE BODY cn_objects_pkg AS
2 -- $Header: cnreobjb.pls 120.6 2005/09/26 01:51:32 hakhter noship $
3 --+
4 -- Public Functions
5 --+
6
7 PROCEDURE insert_row (
8 X_rowid IN OUT NOCOPY ROWID,
9 X_object_id cn_objects.object_id%TYPE,
10 X_dependency_map_complete cn_objects.dependency_map_complete%TYPE,
11 X_name cn_objects.name%TYPE,
12 X_description cn_objects.description%TYPE,
13 X_object_type cn_objects.object_type%TYPE,
14 X_repository_id cn_objects.repository_id%TYPE,
15 X_next_synchronization_date cn_objects.next_synchronization_date%TYPE,
16 X_synchronization_frequency cn_objects.synchronization_frequency%TYPE,
17 X_object_status cn_objects.object_status%TYPE,
18 X_object_value cn_objects.object_value%TYPE,
19 X_org_id cn_objects.org_id%TYPE) IS
20
21 BEGIN
22 INSERT INTO cn_objects (
23 object_id,
24 dependency_map_complete,
25 name,
26 description,
27 object_type,
28 repository_id,
29 next_synchronization_date,
30 synchronization_frequency,
31 object_status,
32 object_value,
33 org_id,
34 object_version_number)
35 VALUES (
36 X_object_id,
37 X_dependency_map_complete,
38 X_name,
39 X_description,
40 X_object_type,
41 X_repository_id,
42 X_next_synchronization_date,
43 X_synchronization_frequency,
44 X_object_status,
45 X_object_value,
46 X_org_id,
47 1);
48
49 SELECT ROWID
50 INTO X_rowid
51 FROM cn_objects
52 WHERE object_id = X_object_id
53 AND org_id = X_org_id;
54
55 IF (SQL%NOTFOUND) THEN
56 RAISE NO_DATA_FOUND;
57 END IF;
58
59 END insert_row;
60
61
62 PROCEDURE select_row (
63 recinfo IN OUT NOCOPY cn_objects%ROWTYPE) IS
64
65 BEGIN
66
67 -- select row based on object_id (primary key)
68 IF (recinfo.object_id IS NOT NULL) THEN
69
70 SELECT * INTO recinfo
71 FROM cn_objects co
72 WHERE co.object_id = recinfo.object_id;
73
74 END IF;
75
76 END select_row;
77
78 PROCEDURE LOAD_SEED_ROW (
79 x_UPLOAD_MODE in varchar2,
80 x_OBJECT_ID in varchar2,
81 x_DEPENDENCY_MAP_COMPLETE in varchar2,
82 x_NAME in varchar2,
83 x_OBJECT_TYPE in varchar2,
84 x_REPOSITORY_ID in varchar2,
85 x_OBJECT_STATUS in varchar2,
86 x_LAST_UPDATE_DATE in varchar2,
87 x_LAST_UPDATED_BY in varchar2,
88 x_CREATION_DATE in varchar2,
89 x_CREATED_BY in varchar2,
90 x_LAST_UPDATE_LOGIN in varchar2,
91 x_DESCRIPTION in varchar2,
92 x_NEXT_SYNCHRONIZATION_DATE in varchar2,
93 x_SYNCHRONIZATION_FREQUENCY in varchar2,
94 x_DATA_LENGTH in varchar2,
95 x_DATA_TYPE in varchar2,
96 x_NULLABLE in varchar2,
97 x_PRIMARY_KEY in varchar2,
98 x_POSITION in varchar2,
99 x_DIMENSION_ID in varchar2,
100 x_DATA_SCALE in varchar2,
101 x_COLUMN_TYPE in varchar2,
102 x_TABLE_ID in varchar2,
103 x_UNIQUE_FLAG in varchar2,
104 x_PACKAGE_TYPE in varchar2,
105 x_PACKAGE_SPECIFICATION_ID in varchar2,
106 x_PARAMETER_LIST in varchar2,
107 x_RETURN_TYPE in varchar2,
108 x_PROCEDURE_TYPE in varchar2,
109 x_PACKAGE_ID in varchar2,
110 x_START_VALUE in varchar2,
111 x_INCREMENT_VALUE in varchar2,
112 x_STATEMENT_TEXT in varchar2,
113 x_ALIAS in varchar2,
114 x_TABLE_LEVEL in varchar2,
115 x_TABLE_TYPE in varchar2,
116 x_WHEN_CLAUSE in varchar2,
117 x_TRIGGERING_EVENT in varchar2,
118 x_EVENT_ID in varchar2,
119 x_PUBLIC_FLAG in varchar2,
120 x_CHILD_FLAG in varchar2,
121 x_FOR_EACH_ROW in varchar2,
122 x_TRIGGER_TYPE in varchar2,
123 x_USER_COLUMN_NAME in varchar2,
124 x_SEED_OBJECT_ID in varchar2,
125 x_PRIMARY_KEY_COLUMN_ID in varchar2,
126 x_USER_NAME_COLUMN_ID in varchar2,
127 x_CONNECT_TO_USERNAME in varchar2,
128 x_CONNECT_TO_PASSWORD in varchar2,
129 x_CONNECT_TO_HOST in varchar2,
130 x_USER_NAME in varchar2,
131 x_SCHEMA in varchar2,
132 x_FOREIGN_KEY in varchar2,
133 x_CLASSIFICATION_COLUMN in varchar2,
134 x_ORG_ID in varchar2,
135 x_CALC_FORMULA_FLAG in varchar2,
136 x_CALC_ELIGIBLE_FLAG in varchar2,
137 x_COLUMN_DATATYPE in varchar2,
138 x_VALUE_SET_ID in varchar2,
139 x_OBJECT_VALUE in varchar2,
140 x_CUSTOM_CALL in varchar2,
141 x_SECURITY_GROUP_ID in varchar2,
142 x_APPLICATION_SHORT_NAME in varchar2,
143 x_OWNER in varchar2)
144 IS
145 BEGIN
146 if (x_upload_mode = 'NLS') then
147 --CN_OBJECTS_PKG.TRANSLATE_ROW(x_owner);
148 -- As this ldt is not required to loaded translated data, you could leave it blank.
149 null;
150 else
151 CN_OBJECTS_PKG.LOAD_ROW(
152 x_OBJECT_ID,
153 x_DEPENDENCY_MAP_COMPLETE,
154 x_NAME,
155 x_OBJECT_TYPE,
156 x_REPOSITORY_ID,
157 x_OBJECT_STATUS,
158 x_LAST_UPDATE_DATE,
159 x_LAST_UPDATED_BY,
160 x_CREATION_DATE,
161 x_CREATED_BY,
162 x_LAST_UPDATE_LOGIN,
163 x_DESCRIPTION,
164 x_NEXT_SYNCHRONIZATION_DATE,
165 x_SYNCHRONIZATION_FREQUENCY,
166 x_DATA_LENGTH,
167 x_DATA_TYPE,
168 x_NULLABLE,
169 x_PRIMARY_KEY,
170 x_POSITION,
171 x_DIMENSION_ID,
172 x_DATA_SCALE,
173 x_COLUMN_TYPE,
174 x_TABLE_ID,
175 x_UNIQUE_FLAG,
176 x_PACKAGE_TYPE,
177 x_PACKAGE_SPECIFICATION_ID,
178 x_PARAMETER_LIST,
179 x_RETURN_TYPE,
180 x_PROCEDURE_TYPE,
181 x_PACKAGE_ID,
182 x_START_VALUE,
183 x_INCREMENT_VALUE,
184 x_STATEMENT_TEXT,
185 x_ALIAS,
186 x_TABLE_LEVEL,
187 x_TABLE_TYPE,
188 x_WHEN_CLAUSE,
189 x_TRIGGERING_EVENT,
190 x_EVENT_ID,
191 x_PUBLIC_FLAG,
192 x_CHILD_FLAG,
193 x_FOR_EACH_ROW,
194 x_TRIGGER_TYPE,
195 x_USER_COLUMN_NAME,
196 x_SEED_OBJECT_ID,
197 x_PRIMARY_KEY_COLUMN_ID,
198 x_USER_NAME_COLUMN_ID,
199 x_CONNECT_TO_USERNAME,
200 x_CONNECT_TO_PASSWORD,
201 x_CONNECT_TO_HOST,
202 x_USER_NAME,
203 x_SCHEMA,
204 x_FOREIGN_KEY,
205 x_CLASSIFICATION_COLUMN,
206 x_ORG_ID,
207 x_CALC_FORMULA_FLAG,
208 x_CALC_ELIGIBLE_FLAG,
209 x_COLUMN_DATATYPE,
210 x_VALUE_SET_ID,
211 x_OBJECT_VALUE,
212 x_CUSTOM_CALL,
213 x_SECURITY_GROUP_ID,
214 x_APPLICATION_SHORT_NAME,
215 x_OWNER
216
217 );
218 null;
219 end if;
220 END LOAD_SEED_ROW;
221
222
223 PROCEDURE LOAD_ROW
224 ( x_OBJECT_ID in varchar2,
225 x_DEPENDENCY_MAP_COMPLETE in varchar2,
226 x_NAME in varchar2,
227 x_OBJECT_TYPE in varchar2,
228 x_REPOSITORY_ID in varchar2,
229 x_OBJECT_STATUS in varchar2,
230 x_LAST_UPDATE_DATE in varchar2,
231 x_LAST_UPDATED_BY in varchar2,
232 x_CREATION_DATE in varchar2,
233 x_CREATED_BY in varchar2,
234 x_LAST_UPDATE_LOGIN in varchar2,
235 x_DESCRIPTION in varchar2,
236 x_NEXT_SYNCHRONIZATION_DATE in varchar2,
237 x_SYNCHRONIZATION_FREQUENCY in varchar2,
238 x_DATA_LENGTH in varchar2,
239 x_DATA_TYPE in varchar2,
240 x_NULLABLE in varchar2,
241 x_PRIMARY_KEY in varchar2,
242 x_POSITION in varchar2,
243 x_DIMENSION_ID in varchar2,
244 x_DATA_SCALE in varchar2,
245 x_COLUMN_TYPE in varchar2,
246 x_TABLE_ID in varchar2,
247 x_UNIQUE_FLAG in varchar2,
248 x_PACKAGE_TYPE in varchar2,
249 x_PACKAGE_SPECIFICATION_ID in varchar2,
250 x_PARAMETER_LIST in varchar2,
251 x_RETURN_TYPE in varchar2,
252 x_PROCEDURE_TYPE in varchar2,
253 x_PACKAGE_ID in varchar2,
254 x_START_VALUE in varchar2,
255 x_INCREMENT_VALUE in varchar2,
256 x_STATEMENT_TEXT in varchar2,
257 x_ALIAS in varchar2,
258 x_TABLE_LEVEL in varchar2,
259 x_TABLE_TYPE in varchar2,
260 x_WHEN_CLAUSE in varchar2,
261 x_TRIGGERING_EVENT in varchar2,
262 x_EVENT_ID in varchar2,
263 x_PUBLIC_FLAG in varchar2,
264 x_CHILD_FLAG in varchar2,
265 x_FOR_EACH_ROW in varchar2,
266 x_TRIGGER_TYPE in varchar2,
267 x_USER_COLUMN_NAME in varchar2,
268 x_SEED_OBJECT_ID in varchar2,
269 x_PRIMARY_KEY_COLUMN_ID in varchar2,
270 x_USER_NAME_COLUMN_ID in varchar2,
271 x_CONNECT_TO_USERNAME in varchar2,
272 x_CONNECT_TO_PASSWORD in varchar2,
273 x_CONNECT_TO_HOST in varchar2,
274 x_USER_NAME in varchar2,
275 x_SCHEMA in varchar2,
276 x_FOREIGN_KEY in varchar2,
277 x_CLASSIFICATION_COLUMN in varchar2,
278 x_ORG_ID in varchar2,
279 x_CALC_FORMULA_FLAG in varchar2,
280 x_CALC_ELIGIBLE_FLAG in varchar2,
281 x_COLUMN_DATATYPE in varchar2,
282 x_VALUE_SET_ID in varchar2,
283 x_OBJECT_VALUE in varchar2,
284 x_CUSTOM_CALL in varchar2,
285 x_SECURITY_GROUP_ID in varchar2,
286 x_APPLICATION_SHORT_NAME in varchar2,
287 x_OWNER in varchar2)
288 IS
289 USER_ID NUMBER;
290 BEGIN
291 NULL;
292
293 -- Proceed only when the object_id is not null
294 if (x_OBJECT_ID is NOT NULL) then
295
296 -- Check whether SEED Data or Custom Data you are uploading
297 IF (X_OWNER IS NOT NULL) AND (X_OWNER = 'SEED') THEN
298 USER_ID := 1;
299 ELSE
300 USER_ID := 0;
301 END IF;
302
303 update cn_objects_all set
304 DEPENDENCY_MAP_COMPLETE = x_DEPENDENCY_MAP_COMPLETE,
305 NAME = x_NAME,
306 OBJECT_TYPE = x_OBJECT_TYPE,
307 REPOSITORY_ID = to_number(x_REPOSITORY_ID),
308 OBJECT_STATUS = x_OBJECT_STATUS,
309 LAST_UPDATE_DATE = SYSDATE,
310 LAST_UPDATED_BY = USER_ID,
311 CREATION_DATE = to_date(x_CREATION_DATE,'DD-MM-YYYY'),
312 CREATED_BY = to_number(x_CREATED_BY),
313 LAST_UPDATE_LOGIN = 0,
314 DESCRIPTION = x_DESCRIPTION,
315 NEXT_SYNCHRONIZATION_DATE = to_date(x_NEXT_SYNCHRONIZATION_DATE,'DD-MM-YYYY'),
316 SYNCHRONIZATION_FREQUENCY = x_SYNCHRONIZATION_FREQUENCY,
317 DATA_LENGTH = to_number(x_DATA_LENGTH),
318 DATA_TYPE = x_DATA_TYPE,
319 NULLABLE = x_NULLABLE,
320 PRIMARY_KEY = x_PRIMARY_KEY,
321 POSITION = to_number(x_POSITION),
322 DIMENSION_ID = to_number(x_DIMENSION_ID),
323 DATA_SCALE = to_number(x_DATA_SCALE),
324 COLUMN_TYPE = x_COLUMN_TYPE,
325 TABLE_ID = to_number(x_TABLE_ID),
329 PARAMETER_LIST = x_PARAMETER_LIST,
326 UNIQUE_FLAG = x_UNIQUE_FLAG,
327 PACKAGE_TYPE = x_PACKAGE_TYPE,
328 PACKAGE_SPECIFICATION_ID = x_PACKAGE_SPECIFICATION_ID,
330 RETURN_TYPE = x_RETURN_TYPE,
331 PROCEDURE_TYPE = x_PROCEDURE_TYPE,
332 PACKAGE_ID = to_number(x_PACKAGE_ID),
333 START_VALUE = to_number(x_START_VALUE),
334 INCREMENT_VALUE = to_number(x_INCREMENT_VALUE),
335 STATEMENT_TEXT = x_STATEMENT_TEXT,
336 ALIAS = x_ALIAS,
337 TABLE_LEVEL = x_TABLE_LEVEL,
338 TABLE_TYPE = x_TABLE_TYPE,
339 WHEN_CLAUSE = x_WHEN_CLAUSE,
340 TRIGGERING_EVENT = x_TRIGGERING_EVENT,
341 EVENT_ID = to_number(x_EVENT_ID),
342 PUBLIC_FLAG = x_PUBLIC_FLAG,
343 CHILD_FLAG = x_CHILD_FLAG,
344 FOR_EACH_ROW = x_FOR_EACH_ROW,
345 TRIGGER_TYPE = x_TRIGGER_TYPE,
346 USER_COLUMN_NAME = x_USER_COLUMN_NAME,
347 SEED_OBJECT_ID = to_number(x_SEED_OBJECT_ID),
348 PRIMARY_KEY_COLUMN_ID = to_number(x_PRIMARY_KEY_COLUMN_ID),
349 USER_NAME_COLUMN_ID = to_number(x_USER_NAME_COLUMN_ID),
350 CONNECT_TO_USERNAME = x_CONNECT_TO_USERNAME,
351 CONNECT_TO_PASSWORD = x_CONNECT_TO_PASSWORD,
352 CONNECT_TO_HOST = x_CONNECT_TO_HOST,
353 USER_NAME = x_USER_NAME,
354 SCHEMA = x_SCHEMA,
355 FOREIGN_KEY = x_FOREIGN_KEY,
356 CLASSIFICATION_COLUMN = x_CLASSIFICATION_COLUMN,
357 ORG_ID = to_number(x_ORG_ID),
358 CALC_FORMULA_FLAG = x_CALC_FORMULA_FLAG,
359 CALC_ELIGIBLE_FLAG = x_CALC_ELIGIBLE_FLAG,
360 COLUMN_DATATYPE = x_COLUMN_DATATYPE,
361 VALUE_SET_ID = to_number(x_VALUE_SET_ID),
362 OBJECT_VALUE = x_OBJECT_VALUE,
363 CUSTOM_CALL = x_CUSTOM_CALL,
364 SECURITY_GROUP_ID = to_number(x_SECURITY_GROUP_ID)
365 where
366 OBJECT_ID = X_OBJECT_ID
367 and ORG_ID = X_ORG_ID;
368
369 IF (SQL%NOTFOUND) THEN
370 -- Insert new record to CN_OBJECTS_TABLE table
371 insert into CN_OBJECTS_ALL
372 (
373 OBJECT_ID,
374 DEPENDENCY_MAP_COMPLETE,
375 NAME,
376 OBJECT_TYPE,
377 REPOSITORY_ID,
378 OBJECT_STATUS,
379 LAST_UPDATE_DATE,
380 LAST_UPDATED_BY,
381 CREATION_DATE,
382 CREATED_BY,
383 LAST_UPDATE_LOGIN,
384 DESCRIPTION,
385 NEXT_SYNCHRONIZATION_DATE,
386 SYNCHRONIZATION_FREQUENCY,
387 DATA_LENGTH,
388 DATA_TYPE,
392 DIMENSION_ID,
389 NULLABLE,
390 PRIMARY_KEY,
391 POSITION,
393 DATA_SCALE,
394 COLUMN_TYPE,
395 TABLE_ID,
396 UNIQUE_FLAG,
397 PACKAGE_TYPE,
398 PACKAGE_SPECIFICATION_ID,
399 PARAMETER_LIST,
400 RETURN_TYPE,
401 PROCEDURE_TYPE,
402 PACKAGE_ID,
403 START_VALUE,
404 INCREMENT_VALUE,
405 STATEMENT_TEXT,
406 ALIAS,
407 TABLE_LEVEL,
408 TABLE_TYPE,
409 WHEN_CLAUSE,
410 TRIGGERING_EVENT,
411 EVENT_ID,
412 PUBLIC_FLAG,
413 CHILD_FLAG,
414 FOR_EACH_ROW,
415 TRIGGER_TYPE,
416 USER_COLUMN_NAME,
417 SEED_OBJECT_ID,
418 PRIMARY_KEY_COLUMN_ID,
419 USER_NAME_COLUMN_ID,
420 CONNECT_TO_USERNAME,
421 CONNECT_TO_PASSWORD,
422 CONNECT_TO_HOST,
423 USER_NAME,
424 SCHEMA,
425 FOREIGN_KEY,
426 CLASSIFICATION_COLUMN,
427 ORG_ID,
428 CALC_FORMULA_FLAG,
429 CALC_ELIGIBLE_FLAG,
430 COLUMN_DATATYPE,
431 VALUE_SET_ID,
432 OBJECT_VALUE,
433 CUSTOM_CALL,
434 SECURITY_GROUP_ID,
435 OBJECT_VERSION_NUMBER)
436
437 values
438 (
439 to_number(x_OBJECT_ID),
440 x_DEPENDENCY_MAP_COMPLETE,
441 x_NAME,
442 x_OBJECT_TYPE,
443 to_number(x_REPOSITORY_ID),
444 x_OBJECT_STATUS,
445 SYSDATE,
446 USER_ID,
447 SYSDATE,
448 USER_ID,
449 0,
450 x_DESCRIPTION,
451 to_date(x_NEXT_SYNCHRONIZATION_DATE,'DD-MM-YYYY'),
452 x_SYNCHRONIZATION_FREQUENCY,
453 to_number(x_DATA_LENGTH),
454 x_DATA_TYPE,
455 x_NULLABLE,
456 x_PRIMARY_KEY,
457 to_number(x_POSITION),
458 to_number(x_DIMENSION_ID),
459 to_number(x_DATA_SCALE),
460 x_COLUMN_TYPE,
461 to_number(x_TABLE_ID),
462 x_UNIQUE_FLAG,
463 x_PACKAGE_TYPE,
464 to_number(x_PACKAGE_SPECIFICATION_ID),
465 x_PARAMETER_LIST,
466 x_RETURN_TYPE,
467 x_PROCEDURE_TYPE,
468 to_number(x_PACKAGE_ID),
469 to_number(x_START_VALUE),
470 to_number(x_INCREMENT_VALUE),
471 x_STATEMENT_TEXT,
472 x_ALIAS,
473 x_TABLE_LEVEL,
474 x_TABLE_TYPE,
475 x_WHEN_CLAUSE,
476 x_TRIGGERING_EVENT,
477 to_number(x_EVENT_ID),
478 x_PUBLIC_FLAG,
479 x_CHILD_FLAG,
480 x_FOR_EACH_ROW,
481 x_TRIGGER_TYPE,
482 x_USER_COLUMN_NAME,
483 to_number(x_SEED_OBJECT_ID),
484 to_number(x_PRIMARY_KEY_COLUMN_ID),
485 to_number(x_USER_NAME_COLUMN_ID),
486 x_CONNECT_TO_USERNAME,
487 x_CONNECT_TO_PASSWORD,
488 x_CONNECT_TO_HOST,
489 x_USER_NAME,
490 x_SCHEMA,
491 x_FOREIGN_KEY,
492 x_CLASSIFICATION_COLUMN,
493 to_number(x_ORG_ID),
494 x_CALC_FORMULA_FLAG,
495 x_CALC_ELIGIBLE_FLAG,
496 x_COLUMN_DATATYPE,
497 to_number(x_VALUE_SET_ID),
498 x_OBJECT_VALUE,
499 x_CUSTOM_CALL,
500 to_number(x_SECURITY_GROUP_ID),
501 1
502 );
503
504 END IF;
505 end if;
506
507 END LOAD_ROW;
508
509
510
511 END cn_objects_pkg;