[Home] [Help]
PACKAGE BODY: APPS.JTF_AE_SQLUTIL_API
Source
1 PACKAGE BODY JTF_AE_SQLUtil_API AS
2 /* $Header: jtfaeapb.pls 120.2 2005/10/25 05:13:26 psanyal ship $ */
3
4 -- Register a new profile for a base table
5 PROCEDURE registerProfile(
6 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE,
7 p_BaseTable IN JTF_PROFILE_MAPPINGS_TL.baseTable%TYPE,
8 p_AppID IN JTF_PROFILE_MAPPINGS_B.ownerID%TYPE,
9 p_userID IN JTF_PROFILE_MAPPINGS_B.created_by%TYPE) IS
10
11 v_pKey NUMBER;
12
13 BEGIN
14
15 /* Step 1: First insert row in JTF_PROFILE_MAPPINGS_B table */
16 select jtf_profile_mappings_b_s1.nextVal
17 into v_pKey
18 from dual;
19
20 JTF_AE_PROFMAPS.INSERT_ROW(
21 X_PROFILE_MAPPINGS_ID => v_pKey,
22 X_SECURITY_GROUP_ID => 0,
23 X_OWNERID => p_AppID,
24 X_OBJECT_VERSION_NUMBER => 0,
25 X_BASETABLE => p_BaseTable,
26 X_PROFILENAME => p_ProfileName,
27 X_CREATION_DATE => sysdate,
28 X_CREATED_BY => p_userID,
29 X_LAST_UPDATE_DATE => sysdate,
30 X_LAST_UPDATED_BY => p_userID,
31 X_LAST_UPDATE_LOGIN => 0);
32
33
34 END registerProfile;
35
36 -- Register primary key
37 PROCEDURE registerPrimaryKey(
38 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE,
39 p_appID IN JTF_ProfileProperties_B.last_updated_by%TYPE,
40 p_propertyName IN JTF_ProfileProperties_TL.propertyName%TYPE,
41 p_attrName IN JTF_ProfileProperties_B.attrName%TYPE,
42 p_propDesc IN JTF_ProfileProperties_TL.prop_description%TYPE,
43 p_userID IN JTF_ProfileProperties_B.created_by%TYPE ) IS
44
45 v_FKey number;
46 v_PKey number;
47
48 Begin
49
50 /* Step 1: Get the profile_mappings_id for p_profileName */
51 v_FKey := getProfileID( p_ProfileName );
52
53 /* Step 2: Get the nextVal for the primary key and object version */
54 select jtf_profileproperties_b_s1.NextVal
55 into v_Pkey
56 from dual;
57
58 /* Insert into JTF_PROFILEPRPOPERTIES tables */
59 JTF_AE_PROFPROPS.INSERT_ROW (
60 X_PROFILEPROPERTIES_ID => v_PKey,
61 X_PROFILE_MAPPINGS_ID => v_FKey,
62 X_ATTRNAME => p_attrName,
63 X_OWNER_APPLICATION_ID => p_appID,
64 X_ISPRIMARYKEY_FLAG_CODE => 'true',
65 X_OBJECT_VERSION_NUMBER => 0,
66 X_SECURITY_GROUP_ID => 0,
67 X_PROPERTYNAME => p_propertyName,
68 X_PROP_DESCRIPTION => p_propDesc,
69 X_CREATION_DATE => sysdate,
70 X_CREATED_BY => p_userID,
71 X_LAST_UPDATE_DATE => sysdate,
72 X_LAST_UPDATED_BY => p_userID,
73 X_LAST_UPDATE_LOGIN => 0);
74
75 End registerPrimaryKey;
76
77 -- Register Object_Version_Number
78
79 PROCEDURE registerObjectVersion(
80 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE,
81 p_appID IN JTF_ProfileProperties_B.last_updated_by%TYPE,
82 p_attrName IN JTF_ProfileProperties_B.attrName%TYPE,
83 p_propDesc IN JTF_ProfileProperties_TL.prop_description%TYPE,
84 p_userID IN JTF_ProfileProperties_B.created_by%TYPE ) IS
85
86 v_FKey number;
87 v_PKey number;
88 v_propertyName JTF_ProfileProperties_TL.propertyName%TYPE;
89
90 Begin
91
92 /* Step 1: Get the profile_mappings_id for p_profileName */
93 v_FKey := getProfileID( p_ProfileName );
94
95 v_propertyName := 'OBJECT_VERSION_NUMBER';
96
97 /* Step 2: Get the nextVal for the primary key and object version */
98 select jtf_profileproperties_b_s1.NextVal
99 into v_Pkey
100 from dual;
101
102 /* Insert into JTF_PROFILEPRPOPERTIES tables */
103 JTF_AE_PROFPROPS.INSERT_ROW (
104 X_PROFILEPROPERTIES_ID => v_PKey,
105 X_PROFILE_MAPPINGS_ID => v_FKey,
106 X_ATTRNAME => p_attrName,
107 X_OWNER_APPLICATION_ID => p_appID,
108 X_ISPRIMARYKEY_FLAG_CODE => 'true',
109 X_OBJECT_VERSION_NUMBER => 0,
110 X_SECURITY_GROUP_ID => 0,
111 X_PROPERTYNAME => v_propertyName,
112 X_PROP_DESCRIPTION => p_propDesc,
113 X_CREATION_DATE => sysdate,
114 X_CREATED_BY => p_userID,
115 X_LAST_UPDATE_DATE => sysdate,
116 X_LAST_UPDATED_BY => p_userID,
117 X_LAST_UPDATE_LOGIN => 0);
118
119 End registerObjectVersion;
120
121
122
123 -- Insert a new Profile Owner object into JTF_ProfileProperties_B/TL
124 -- Return the primary key for the newly inserted object.
125 PROCEDURE addAttribute (
126 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE,
127 p_appID IN JTF_ProfileProperties_B.last_updated_by%TYPE,
128 p_OwnerID IN JTF_ProfileProperties_B.owner_application_id%TYPE,
129 p_propertyName IN JTF_ProfileProperties_TL.propertyName%TYPE,
130 p_propDesc IN JTF_ProfileProperties_TL.prop_description%TYPE,
131 p_isBase IN NUMBER,
132 p_userID IN JTF_ProfileProperties_B.created_by%TYPE,
133 p_PK OUT NOCOPY /* file.sql.39 change */ NUMBER,
134 p_OID OUT NOCOPY /* file.sql.39 change */ NUMBER,
135 p_ATTR OUT NOCOPY /* file.sql.39 change */ JTF_ProfileProperties_B.attrname%TYPE) IS
136
137 v_FKey NUMBER;
138 BEGIN
139
140 /* Step 1: Get the profile_mappings_id for p_profileName */
141 v_FKey := getProfileID( p_ProfileName );
142
143 /* Step 2: Determine the attrName for new property */
144 p_ATTR := getAttrName( v_FKey, p_appID, p_isBase );
145
146 /* Step 3: Get the nextVal for the primary key and object version */
147 select jtf_profileproperties_b_s1.NextVal
148 into p_PK
149 from dual;
150
151 p_OID := 0;
152
153 /* Insert into JTF_PROFILEPRPOPERTIES tables */
154 JTF_AE_PROFPROPS.INSERT_ROW (
155 X_PROFILEPROPERTIES_ID => p_pK,
156 X_PROFILE_MAPPINGS_ID => v_FKey,
157 X_ATTRNAME => p_ATTR,
158 X_OWNER_APPLICATION_ID => p_OwnerID,
159 X_ISPRIMARYKEY_FLAG_CODE => 'false',
160 X_OBJECT_VERSION_NUMBER => p_OID,
161 X_SECURITY_GROUP_ID => 0,
162 X_PROPERTYNAME => p_propertyName,
163 X_PROP_DESCRIPTION => p_propDesc,
164 X_CREATION_DATE => sysdate,
165 X_CREATED_BY => p_userID,
166 X_LAST_UPDATE_DATE => sysdate,
167 X_LAST_UPDATED_BY => p_userID,
168 X_LAST_UPDATE_LOGIN => 0);
169
170 END addAttribute;
171
172 -- Insert a new ProfilePropertyDescriptor into JTF_Profile_Metadata_B/TL
173 -- Return the primary key for the newly inserted object.
174 PROCEDURE addAttribute (
175 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE,
176 p_appID IN JTF_Profile_Metadata_B.last_updated_by%TYPE,
177 p_PropertyName IN JTF_ProfileProperties_TL.propertyName%TYPE,
178 p_pID IN JTF_Profile_Metadata_B.profileproperties_ID%TYPE,
179 p_rID IN JTF_Profile_Metadata_B.profile_rules_ID%TYPE,
180 p_disabledCode IN JTF_Profile_Metadata_B.disabled_flag_code%TYPE,
181 p_mandatoryCode IN JTF_Profile_Metadata_B.mandatory_flag_code%TYPE,
182 p_javaTypeCode IN JTF_Profile_Metadata_B.java_datatype_code%TYPE,
183 p_default IN JTF_Profile_Metadata_TL.default_value%TYPE,
184 p_userID IN JTF_Profile_Metadata_B.created_by%TYPE,
185 p_PK OUT NOCOPY /* file.sql.39 change */ NUMBER,
186 p_OID OUT NOCOPY /* file.sql.39 change */ NUMBER,
187 p_SPID OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
188
189 v_pMapID NUMBER;
190 v_pPID NUMBER;
191
192 BEGIN
193
194 /* Step 1: Get the profile_mappings_id subscribed_profile_id */
195 getAppProfileID( p_ProfileName, p_appID, v_pMapID, p_SPID );
196
197 /* Step 2: If p_pID is null or -1 then determine it */
198 if p_PID = NULL OR p_PID = -1 then
199 select profileproperties_id
200 into v_pPID
201 from jtf_profileproperties_tl t
202 where t.propertyname = p_PropertyName AND t.language = userenv('LANG');
203 else
204 v_pPID := p_PID;
205 end if;
206
207 /* Step 3: Get the nextVal for the primary key */
208 select jtf_profile_metadata_b_s1.NextVal
209 into p_PK
210 from dual;
211
212 p_OID := 0;
213
214 /* Insert into JTF_PROFILE_METADATA tables */
215 JTF_AE_METADATA.INSERT_ROW (
216 X_PROFILE_METADATA_ID => p_PK,
217 X_SECURITY_GROUP_ID => 0,
218 X_SUBSCRIBED_PROFILES_ID => p_SPID,
219 X_PROFILEPROPERTIES_ID => v_pPID,
220 X_PROFILE_RULES_ID => p_rID,
221 X_DISABLED_FLAG_CODE => p_disabledCode,
222 X_MANDATORY_FLAG_CODE => p_mandatoryCode,
223 X_JAVA_DATATYPE_CODE => p_javaTypeCode,
224 X_OBJECT_VERSION_NUMBER => p_OID,
225 X_DEFAULT_VALUE => p_default,
226 X_SQL_VALIDATION => null,
227 X_CREATION_DATE => sysdate,
228 X_CREATED_BY => p_userID,
229 X_LAST_UPDATE_DATE => sysdate,
230 X_LAST_UPDATED_BY => p_userID,
231 X_LAST_UPDATE_LOGIN => 0);
232
233 END addAttribute;
234
235 -- Add an instance specific attribute. If the rule associated
236 -- with the attribute is not registered yet, first register the rule.
237
238 PROCEDURE addAttribute (
239 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE,
240 p_appID IN JTF_Profile_Metadata_B.last_updated_by%TYPE,
241 p_disabledCode IN JTF_Profile_Metadata_B.disabled_flag_code%TYPE,
242 p_mandatoryCode IN JTF_Profile_Metadata_B.mandatory_flag_code%TYPE,
243 p_javaTypeCode IN JTF_Profile_Metadata_B.java_datatype_code%TYPE,
244 p_default IN JTF_Profile_Metadata_TL.default_value%TYPE,
245 p_extPropertyName IN JTF_PROFILEPROPERTIES_TL.propertyName%TYPE,
246 p_basePropertyName IN JTF_PROFILEPROPERTIES_TL.propertyName%TYPE,
247 p_basePropertyVal IN JTF_PROFILE_RULES_TL.base_property_value%TYPE,
248 p_rule IN JTF_Profile_Rules_B.rule%TYPE,
249 p_userID IN JTF_Profile_Rules_B.created_by%TYPE,
250 p_PK OUT NOCOPY /* file.sql.39 change */ NUMBER,
251 p_OID OUT NOCOPY /* file.sql.39 change */ NUMBER,
252 p_SPID OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
253
254 v_pMapID NUMBER;
255 v_ruleID NUMBER;
256 v_PK NUMBER;
257 v_OID NUMBER;
258 v_attr JTF_ProfileProperties_B.attrname%TYPE;
259
260 BEGIN
261
262 /* Check to see if the rule is registered */
263 addRule( p_ProfileName, p_appID, p_basePropertyName,
264 p_basePropertyVal, p_rule, p_userID, v_ruleID);
265
266 /* Add this new instance specific property to profileproperties */
267 addAttribute(p_ProfileName, p_appID, p_appID, p_extPropertyName,
268 'Instance specific property', 1, p_userID, v_PK, v_OID, v_attr);
269
270 /* Add the new attribute's app specific metadata */
271 addAttribute( p_ProfileName, p_appID, p_extPropertyName,
272 v_PK, v_ruleID, p_disabledCode,
273 p_mandatoryCode, p_javaTypeCode, p_default, p_userID,
274 p_PK, p_OID, p_SPID );
275
276
277 END addAttribute;
278
279 -- Modify an existing base or instance specific attribute
280 PROCEDURE modifyAttribute (
281 p_appID IN JTF_Profile_Metadata_B.last_updated_by%TYPE,
282 p_Key IN JTF_Profile_Metadata_B.profile_metadata_id%TYPE,
283 p_disabledCode IN JTF_Profile_Metadata_B.disabled_flag_code%TYPE,
284 p_mandatoryCode IN JTF_Profile_Metadata_B.mandatory_flag_code%TYPE,
285 p_javaTypeCode IN JTF_Profile_Metadata_B.java_datatype_code%TYPE,
286 p_default IN JTF_Profile_Metadata_TL.default_value%TYPE,
287 p_version IN JTF_Profile_Metadata_B.object_version_number%TYPE,
288 p_userID IN JTF_Profile_Metadata_B.created_by%TYPE,
289 p_Count OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
290
291 BEGIN
292
293 /* Modify the _B table first */
294
295 UPDATE JTF_PROFILE_METADATA_B
296 SET disabled_flag_code = p_disabledCode,
297 mandatory_flag_code = p_mandatoryCode,
298 java_datatype_code = p_javaTypeCode,
299 last_update_date = SYSDATE,
300 last_updated_by = p_userID,
301 object_version_number = p_version + 1
302 WHERE profile_metadata_id = p_Key AND object_version_number = p_version;
303
304 /* Check if update successful? */
305 p_Count := SQL%ROWCOUNT;
306
307 if p_Count = 0 then
308 return;
309 end if;
310
311 /* Update successful, so modify _TL table */
312 UPDATE JTF_PROFILE_METADATA_TL
313 SET default_value = p_default,
314 last_update_date = SYSDATE,
315 last_updated_by = p_userID
316 WHERE profile_metadata_id = p_Key;
317
318 END modifyAttribute;
319
320
321 -- If new rule then add rule for instance specific attribute
322 -- Return the PROFILE_RULE_ID of the newly registered rule.
323 PROCEDURE addRule(
324 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE,
325 p_appID IN JTF_PROFILE_RULES_B.last_updated_by%TYPE,
326 p_propertyName IN JTF_ProfileProperties_TL.propertyName%TYPE,
327 p_baseValue IN JTF_PROFILE_RULES_TL.base_property_value%TYPE,
328 p_rule IN JTF_Profile_Rules_B.rule%TYPE,
329 p_userID IN JTF_Profile_Rules_B.created_by%TYPE,
330 p_ruleID OUT NOCOPY /* file.sql.39 change */ JTF_Profile_Rules_B.profile_rules_id%TYPE) IS
331
332 v_Result NUMBER;
333 v_pMapID NUMBER;
334 v_pSubProfID NUMBER;
335 v_pMetaID NUMBER;
336
337 CURSOR c_Rules IS
338 SELECT b.profile_rules_id
339 FROM JTF_Profile_Rules_B b, JTF_Profile_Rules_TL t
340 WHERE b.profile_metadata_id = v_pMetaID AND
341 b.profile_rules_id = t.profile_rules_id and
342 t.base_property_value = p_baseValue and t.language = userenv('LANG');
343
344 BEGIN
345
346 /* Step 1: Get the profile_mappings_id and subscribed_profile_id */
347 getAppProfileID( p_ProfileName, p_appID, v_pMapID, v_pSubProfID );
348
349 /* Step 2: Determine the profile_metadata_id */
350 SELECT a.profile_metadata_id
351 INTO v_pMetaID
352 FROM JTF_Profile_Metadata_B a, JTF_ProfileProperties_B b,
353 JTF_ProfileProperties_TL t
354 WHERE a.subscribed_profiles_id = v_pSubProfID
355 AND a.profileproperties_id = b.profileproperties_id
356 AND b.profile_mappings_id = v_pMapID
357 AND b.profileproperties_id = t.profileproperties_id
358 AND t.propertyName = p_propertyName AND t.language = userenv('LANG');
359
360 /* Step 3: Check to see if the rule is already registered */
361 OPEN c_Rules;
362
363 FETCH c_Rules INTO v_Result;
364
365 if c_Rules%FOUND THEN
366 CLOSE c_Rules;
367 p_ruleID := v_Result;
368
369 RETURN;
370 end if;
371
372 CLOSE c_Rules;
373
374 /* Step 4: Rule NOT Registered .. Add the new rule */
375 select jtf_profile_rules_b_s1.NextVal
376 into v_Result
377 from dual;
378
379 JTF_AE_PROFRULES.INSERT_ROW (
380 X_PROFILE_RULES_ID => v_Result,
381 X_SECURITY_GROUP_ID => 0,
382 X_PROFILE_METADATA_ID => v_pMetaID,
383 X_RULE => p_rule,
384 X_OBJECT_VERSION_NUMBER => 0,
385 X_BASE_PROPERTY_VALUE => p_baseValue,
386 X_CREATION_DATE => sysdate,
387 X_CREATED_BY => p_userID,
388 X_LAST_UPDATE_DATE => sysdate,
389 X_LAST_UPDATED_BY => p_userID,
390 X_LAST_UPDATE_LOGIN => 0
391 );
392
393 /* Return the result */
394 p_ruleID := v_Result;
395
396 END addRule;
397
398
399 -- HELPER FUNCTIONS
400
401 FUNCTION getProfileID(
402 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE)
403 RETURN NUMBER IS
404
405 v_Result NUMBER;
406
407 BEGIN
408
409 SELECT profile_mappings_id
410 INTO v_Result
411 FROM JTF_PROFILE_MAPPINGS_TL t
412 WHERE t.profileName = p_profileName AND
413 t.language = userenv('LANG');
414
415 RETURN v_Result;
416
417 END getProfileID;
418
419 FUNCTION getAttrName (
420 p_profile_mappings_id IN NUMBER,
421 p_appID IN NUMBER,
422 p_isBase in NUMBER )
423 RETURN VARCHAR2 IS
424
425 v_Result JTF_ProfileProperties_B.attrname%TYPE;
426 v_tmp1 JTF_ProfileProperties_B.attrname%TYPE;
427 v_tmp2 JTF_ProfileProperties_B.attrname%TYPE;
428 v_index NUMBER;
429
430 BEGIN
431
432 if p_isBase = 0 THEN
433 v_tmp1 := 'ATTR_';
434 v_tmp2 := 'ATTR_%';
435 else
436 v_tmp1 := 'EXT_';
437 v_tmp2 := 'EXT_%';
438
439 END IF;
440
441 SELECT MAX(TO_NUMBER(LTRIM(ATTRNAME,v_tmp1)))
442 INTO v_index
443 FROM JTF_ProfileProperties_B
444 WHERE profile_mappings_id = p_profile_mappings_id
445 AND attrname LIKE v_tmp2;
446
447 if v_index >= 0 then
448 v_index := v_index + 1;
449 else
450 v_index := 0;
451 END IF;
452
453 v_Result := v_tmp1 || v_index;
454
455 return v_Result;
456
457 END getAttrName;
458
459 PROCEDURE getAppProfileID(
460 p_ProfileName IN JTF_PROFILE_MAPPINGS_TL.profileName%TYPE,
461 p_appID IN JTF_SUBSCRIBED_PROFILES.application_id%TYPE,
462 p_PMAPID OUT NOCOPY /* file.sql.39 change */ NUMBER,
463 p_SUBID OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
464
465 BEGIN
466
467 SELECT s.profile_mappings_id,s.subscribed_profiles_id
468 INTO p_PMAPID, p_SUBID
469 FROM JTF_SUBSCRIBED_PROFILES s, JTF_PROFILE_MAPPINGS_TL t
470 WHERE s.application_id = p_appID
471 AND s.profile_mappings_id = t.profile_mappings_id
472 AND t.profileName = p_ProfileName AND t.language = userenv('LANG');
473
474 END getAppProfileID;
475
476 procedure run is
477 v_PK NUMBER;
478 v_OID NUMBER;
479 v_ATTR VARCHAR2(50);
480
481 begin
482
483 addAttribute('AR_USERPROFILE', 222, 'false', 'false', 'String', null, 'EXT_testAttribute0__0', 'testAttribute0', 'falluda', 'AR_USERPROFILE::222::testAttribute0::falluda', 1,v_PK, v_OID, v_ATTR);
484
485 /* Simply call addAttribute with dummy input
486 addAttribute('foo', 2, 2, 'fooo', 'dummy property', 0, 1,v_PK, v_OID, v_ATTR);
487 */
488
489 /*
490 DBMS_OUTPUT.ENABLE(1000000);
491
492 DBMS_OUTPUT.PUT_LINE('Value of primary key is: ' || v_PK);
493 DBMS_OUTPUT.PUT_LINE('Value of object version number is: ' || v_OID);
494 DBMS_OUTPUT.PUT_LINE('Value of attr name is: ' || v_ATTR);
495 */
496
497 end run;
498
499 END JTF_AE_SQLUtil_API;