eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;