1 PACKAGE BODY jtf_activity_sqlapi AS
2 /* $Header: jtfactsb.pls 120.2 2005/10/25 05:12:40 psanyal ship $ */
3
4
5 FUNCTION getActivityNameID(
6 ActivityName IN JTF_ACT_TYPES_TL.activity_name%TYPE)
7 RETURN NUMBER IS
8
9 v_Result NUMBER;
10
11 BEGIN
12
13 SELECT activity_name_id
14 INTO v_Result
15 FROM JTF_ACT_TYPES_TL
16 WHERE activity_name = ActivityName AND
17 language = userenv('LANG');
18 RETURN v_Result;
19
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN
22 RETURN 0;
23 WHEN OTHERS THEN
24 RETURN 0;
25
26 END getActivityNameID;
27
28 FUNCTION getActivityAttributeNameID(
29 ActivityName IN JTF_ACT_TYPES_TL.activity_name%TYPE,
30 AttributeName IN JTF_ACT_TYPES_ATTRS_TL.attribute_name%TYPE)
31 RETURN NUMBER IS
32
33 v_Result NUMBER;
34 v_activity_name_id NUMBER;
35 BEGIN
36
37 v_activity_name_id := getActivityNameID(ActivityName);
38
39 SELECT b.attribute_name_id INTO v_Result
40 FROM JTF_ACT_TYPES_ATTRS_B b, JTF_ACT_TYPES_ATTRS_TL t
41 WHERE b.activity_name_id = v_activity_name_id
42 AND b.attribute_name_id = t.attribute_name_id
43 AND t.attribute_name = AttributeName
44 AND t.language = userenv('LANG');
45
46 RETURN v_Result;
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 RETURN -1;
50 WHEN OTHERS THEN
51 RETURN -1;
52
53 END getActivityAttributeNameID;
54
55 FUNCTION isActivityDefined(
56 ActivityName IN jtf_act_types_tl.activity_name%TYPE)
57 RETURN NUMBER IS
58
59 v_row NUMBER;
60 BEGIN
61
62 v_row := getActivityNameID(ActivityName);
63 if (v_row <0) then
64 return 0;
65 else
66 return v_row;
67 end if;
68 END isActivityDefined;
69
70
71 -- delete a row in jtf_activity_type_b table;
72
73 procedure deleteActivityType(activityname IN varchar2) AS
74 name_id NUMBER;
75 i binary_integer;
76
77 Cursor C is
78 select t.activity_name_id from jtf_act_types_tl t where t.language=userenv('LANG') and t.activity_name =activityname;
79 begin
80
81 open C;
82
83 loop
84 fetch C into name_id;
85 Exit when C%NOTFOUND;
86 delete from jtf_act_types_tl where activity_name_id = name_id;
87 delete from jtf_act_types_b where activity_name_id = name_id;
88 end loop;
89
90 close c;
91 end deleteActivityType;
92
93 procedure subscribeActivityType(AppID IN number, CategoryName IN varchar2, ActivityName IN varchar2)
94 AS
95 v_activityname_id NUMBER;
96 v_act_app_cats_id NUMBER;
97 v_act_types_b_id NUMBER;
98 begin
99 -- get the jtf_act_app_cats_id from jtf_act_app_cats_id table
100 select jtf_act_app_cats_id into v_act_app_cats_id from jtf_act_app_cats
101 where application_id = AppID and category_name_code=CategoryName;
102
103 -- get the activity name id
104 v_activityname_id := getActivityNameID(ActivityName);
105
106 select jtf_act_types_b_id into v_act_types_b_id from jtf_act_types_b
107 where activity_name_id = v_activityname_id;
108
109 insert into jtf_act_activity_cats
110 (jtf_act_types_b_id, jtf_act_app_cats_id, priority, status_code,
111 created_by, creation_date, last_updated_by, last_update_date)
112 values (v_act_types_b_id, v_act_app_cats_id, 1, 'ON',
113 AppID, sysdate, AppID, sysdate);
114
115 end subscribeActivityType;
116
117 procedure unsubscribeActivityType(AppID IN NUMBER, CategoryName IN varchar2, ActivityName IN varchar2)
118 AS
119 v_activityname_id NUMBER;
120 v_act_app_cats_id NUMBER;
121 v_act_types_b_id NUMBER;
122 begin
123
124 -- get the jtf_act_app_cats_id from jtf_act_app_cats_id table
125 select jtf_act_app_cats_id into v_act_app_cats_id from jtf_act_app_cats
126 where application_id = AppID and category_name_code=CategoryName;
127
128 -- get the activity name id
129 v_activityname_id := getActivityNameID(ActivityName);
130
131 select jtf_act_types_b_id into v_act_types_b_id from jtf_act_types_b
132 where activity_name_id = v_activityname_id;
133
134 delete from jtf_act_activity_cats
135 where jtf_act_app_cats_id = v_act_app_cats_id and
136 jtf_act_types_b_id = v_act_types_b_id;
137 end unsubscribeActivityType;
138
139 procedure setActivityPriority(appID IN NUMBER, categoryname IN varchar2, activityname IN varchar2, Priority IN NUMBER)
140 AS
141
142 v_activityname_id NUMBER;
143 v_act_app_cats_id NUMBER;
144 v_act_types_b_id NUMBER;
145 begin
146 -- get the jtf_act_app_cats_id from jtf_act_app_cats_id table
147 select jtf_act_app_cats_id into v_act_app_cats_id from jtf_act_app_cats
148 where application_id = AppID and category_name_code=CategoryName;
149 -- get the activity name id
150 v_activityname_id := getActivityNameID(ActivityName);
151 select jtf_act_types_b_id into v_act_types_b_id from jtf_act_types_b
152 where activity_name_id = v_activityname_id;
153
154 update jtf_act_activity_cats set priority = Priority
155 where jtf_act_app_cats_id = v_act_app_cats_id and
156 jtf_act_types_b_id = v_act_types_b_id;
157 end setActivityPriority;
158
159 procedure setActivityStatus(appID IN NUMBER, categoryname IN varchar2, activityname IN varchar2, Status IN varchar2)
160 AS
161
162 v_activityname_id NUMBER;
163 v_act_app_cats_id NUMBER;
164 v_act_types_b_id NUMBER;
165 begin
166 -- get the jtf_act_app_cats_id from jtf_act_app_cats_id table
167 select jtf_act_app_cats_id into v_act_app_cats_id from jtf_act_app_cats
168 where application_id = AppID and category_name_code=CategoryName;
169
170 -- get the activity name id
171 v_activityname_id := getActivityNameID(ActivityName);
172 select jtf_act_types_b_id into v_act_types_b_id from jtf_act_types_b
173 where activity_name_id = v_activityname_id;
174
175 update jtf_act_activity_cats set status_code = Status
176 where jtf_act_app_cats_id = v_act_app_cats_id and
177 jtf_act_types_b_id = v_act_types_b_id;
178
179 end setActivityStatus;
180
181
182 -- These APIs help insert rows in _B and _T tables
183
184 PROCEDURE addActivityType (
185 p_ActivityName IN JTF_ACT_TYPES_TL.activity_name%TYPE,
186 p_appID IN JTF_ACT_TYPES_TL.last_updated_by%TYPE,
187 p_tableName IN JTF_ACT_TYPES_B.table_name%TYPE,
188 p_Desc IN JTF_ACT_TYPES_TL.description%TYPE,
189 p_PK OUT NOCOPY /* file.sql.39 change */ NUMBER,
190 p_NameID OUT NOCOPY /* file.sql.39 change */ JTF_ACT_TYPES_B.activity_name_id%TYPE)
191 AS
192 v_FKey NUMBER;
193 v_row NUMBER;
194 begin
195 -- first check whether the activity name is already defined
196 IF (isActivityDefined(p_ActivityName) = 0 ) THEN
197
198 -- First do an insert into JTF_ACT_TYPES_B table
199 -- activity_name_id is the same as primary key
200 INSERT INTO JTF_ACT_TYPES_B
201 ( table_name, created_by, creation_date, last_updated_by, last_update_date)
202 VALUES (p_tableName, p_appID, SYSDATE, p_appID, SYSDATE);
203
204 select jtf_act_types_b_s.currval into p_PK from dual;
205
206 -- Finally insert the translatable columns into JTF_ProfileProperties_TL
207 INSERT INTO JTF_ACT_TYPES_TL
208 ( activity_name_id, activity_name, act_description, language,
209 created_by, creation_date, last_updated_by, last_update_date)
210 VALUES (p_PK, p_ActivityName, p_Desc, userenv('LANG'),
211 p_appID, SYSDATE, p_appID, SYSDATE);
212 p_NameID := p_PK;
213 END IF;
214
215 END addActivityType;
216
217 PROCEDURE addActivityAttribute (
218 p_ActivityName IN JTF_ACT_TYPES_TL.activity_name%TYPE,
219 p_appID IN JTF_ACT_TYPES_TL.last_updated_by%TYPE,
220 p_AttributeName IN JTF_ACT_TYPES_ATTRS_TL.attribute_name%TYPE,
221 p_ColumnName IN JTF_ACT_TYPES_ATTRS_B.column_name%TYPE)
222 AS
223 v_activity_name_id NUMBER;
224 v_attribute_name_id NUMBER;
225 begin
226
227 -- first check whether the activity name is already defined
228 IF (isActivityDefined(p_ActivityName) > 0 ) THEN
229
230 v_activity_name_id := getActivityNameID(p_ActivityName);
231 -- First do an insert into JTF_ACT_TYPES_ATTRS_B table
232 -- activity_name_id is the same as primary key
233 INSERT INTO JTF_ACT_TYPES_ATTRS_B
234 ( activity_name_id, column_name, created_by, creation_date, last_updated_by, last_update_date)
235 VALUES (v_activity_name_id, p_ColumnName, p_appID, SYSDATE, p_appID, SYSDATE);
236
237 SELECT attribute_name_id INTO v_attribute_name_id
238 FROM jtf_act_types_attrs_b
239 WHERE activity_name_id = v_activity_name_id
240 and column_name = p_ColumnName;
241
242 -- Finally insert the translatable columns into JTF_ProfileProperties_TL
243 INSERT INTO JTF_ACT_TYPES_ATTRS_TL
244 ( attribute_name_id, attribute_name, language,
245 created_by, creation_date, last_updated_by, last_update_date)
246 VALUES (v_attribute_name_id, p_AttributeName, userenv('LANG'),
247 p_appID, SYSDATE, p_appID, SYSDATE);
248 END IF;
249
250 END addActivityAttribute;
251
252
253
254 procedure run is
255 v_PK NUMBER;
256 v_NameID NUMBER;
257 v_AttrID NUMBER;
258 v_ATTR VARCHAR2(50);
259 begin
260 -- Simply call addAttribute with dummy input
261 --v_PK := isActivityDefined('foo');
262 v_PK := getActivityNameID('Customer_DisputeBill2');
263 --addActivityType('foo', 10, 'foo','this is a sample activity type', v_PK, v_NameID);
264 --addActivityAttribute('Customer_DisputeBill', 10, 'billid', 'column1');
265 --deleteActivityType('foo3' );
266 unsubscribeActivityType(690, 'Bill Management', 'Customer_DisputeBill2');
267 --DBMS_OUTPUT.ENABLE(1000000);
268 --DBMS_OUTPUT.PUT_LINE('This is the output' );
269 --DBMS_OUTPUT.PUT_LINE('Value of primary key is: ' || v_PK);
270 --DBMS_OUTPUT.PUT_LINE('Value of name id is: ' || v_NameID);
271 end run;
272
273 end jtf_activity_sqlapi;
[Home] [Help]