1 package JTF_UM_SUBSCRIPTIONS_PKG as
2 /* $Header: JTFUMSBS.pls 120.3 2005/11/28 08:52:24 vimohan ship $ */
3 procedure INSERT_ROW (
4 X_SUBSCRIPTION_ID out NOCOPY NUMBER,
5 X_APPLICATION_ID in NUMBER,
6 X_EFFECTIVE_START_DATE in DATE,
7 X_SUBSCRIPTION_KEY in VARCHAR2,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_EFFECTIVE_END_DATE in DATE,
10 X_APPROVAL_ID in NUMBER,
11 X_PARENT_SUBSCRIPTION_ID in NUMBER,
12 X_AVAILABILITY_CODE in VARCHAR2,
13 X_LOGON_DISPLAY_FREQUENCY in NUMBER,
14 X_SUBSCRIPTION_NAME in VARCHAR2,
15 X_DESCRIPTION in VARCHAR2,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER,
21 X_AUTH_DELEGATION_ROLE_ID in NUMBER);
22 procedure LOCK_ROW (
23 X_SUBSCRIPTION_ID in NUMBER,
24 X_APPLICATION_ID in NUMBER,
25 X_EFFECTIVE_START_DATE in DATE,
26 X_SUBSCRIPTION_KEY in VARCHAR2,
27 X_ENABLED_FLAG in VARCHAR2,
28 X_EFFECTIVE_END_DATE in DATE,
29 X_APPROVAL_ID in NUMBER,
30 X_PARENT_SUBSCRIPTION_ID in NUMBER,
31 X_AVAILABILITY_CODE in VARCHAR2,
32 X_LOGON_DISPLAY_FREQUENCY in NUMBER,
33 X_SUBSCRIPTION_NAME in VARCHAR2,
34 X_DESCRIPTION in VARCHAR2,
35 X_AUTH_DELEGATION_ROLE_ID in NUMBER
36 );
37 procedure UPDATE_ROW (
38 X_SUBSCRIPTION_ID in NUMBER,
39 X_APPLICATION_ID in NUMBER,
40 X_SUBSCRIPTION_KEY in VARCHAR2,
41 X_ENABLED_FLAG in VARCHAR2,
42 X_EFFECTIVE_END_DATE in DATE,
43 X_APPROVAL_ID in NUMBER,
44 X_PARENT_SUBSCRIPTION_ID in NUMBER,
45 X_AVAILABILITY_CODE in VARCHAR2,
46 X_LOGON_DISPLAY_FREQUENCY in NUMBER,
47 X_SUBSCRIPTION_NAME in VARCHAR2,
48 X_DESCRIPTION in VARCHAR2,
49 X_LAST_UPDATE_DATE in DATE,
50 X_LAST_UPDATED_BY in NUMBER,
51 X_LAST_UPDATE_LOGIN in NUMBER,
52 X_AUTH_DELEGATION_ROLE_ID in NUMBER
53 );
54 procedure DELETE_ROW (
55 X_SUBSCRIPTION_ID in NUMBER
56 );
57 procedure ADD_LANGUAGE;
58
59 procedure LOAD_ROW (
60 X_SUBSCRIPTION_ID IN NUMBER,
61 X_EFFECTIVE_START_DATE IN DATE,
62 X_EFFECTIVE_END_DATE IN DATE,
63 X_OWNER IN VARCHAR2,
64 X_APPROVAL_ID IN NUMBER,
65 X_APPLICATION_ID IN NUMBER,
66 X_ENABLED_FLAG IN VARCHAR2,
67 X_PARENT_SUBSCRIPTION_ID IN NUMBER,
68 X_AVAILABILITY_CODE IN VARCHAR2,
69 X_LOGON_DISPLAY_FREQUENCY IN NUMBER,
70 X_SUBSCRIPTION_KEY IN VARCHAR2,
71 X_SUBSCRIPTION_NAME IN VARCHAR2,
72 X_DESCRIPTION IN VARCHAR2,
73 X_AUTH_DELEGATION_ROLE_ID IN NUMBER,
74 x_last_update_date in varchar2 default NULL,
75 X_CUSTOM_MODE in varchar2 default NULL
76 );
77
78 procedure TRANSLATE_ROW (
79 X_SUBSCRIPTION_ID in NUMBER, -- key field
80 X_SUBSCRIPTION_NAME in VARCHAR2, -- translated name
81 X_DESCRIPTION in VARCHAR2, -- translated description
82 X_OWNER in VARCHAR2, -- owner field
83 x_last_update_date in varchar2 default NULL,
84 X_CUSTOM_MODE in varchar2 default NULL
85 );
86
87 procedure CREATE_TEMPLATE_ASSIGNMENT(
88 X_SUBSCRIPTION_ID IN NUMBER,
89 X_TEMPLATE_ID IN NUMBER,
90 X_EFFECTIVE_START_DATE IN DATE DEFAULT SYSDATE,
91 X_EFFECTIVE_END_DATE IN DATE DEFAULT NULL,
92 X_CREATED_BY IN NUMBER DEFAULT FND_GLOBAL.USER_ID,
93 X_LAST_UPDATED_BY IN NUMBER DEFAULT FND_GLOBAL.USER_ID
94 );
95
96 procedure UPDATE_TEMPLATE_ASSIGNMENT(
97 X_SUBSCRIPTION_ID IN NUMBER,
98 X_TEMPLATE_ID IN NUMBER,
99 X_EFFECTIVE_START_DATE IN DATE,
100 X_EFFECTIVE_END_DATE IN DATE,
101 X_LAST_UPDATE_DATE IN DATE,
102 X_LAST_UPDATED_BY IN NUMBER,
103 X_LAST_UPDATE_LOGIN IN NUMBER
104 );
105
106 procedure LOAD_SUBSCRIPTION_TMPL_ROW(
107 X_SUBSCRIPTION_ID IN NUMBER,
108 X_TEMPLATE_ID IN NUMBER,
109 X_EFFECTIVE_START_DATE IN DATE,
110 X_EFFECTIVE_END_DATE IN DATE,
111 X_OWNER IN VARCHAR2,
112 x_last_update_date in varchar2 default NULL,
113 X_CUSTOM_MODE in varchar2 default NULL
114 );
115
116 procedure ASSOCIATE_TEMPLATE(
117 X_SUBSCRIPTION_ID NUMBER,
118 X_TEMPLATE_ID NUMBER
119 );
120 procedure REMOVE_TEMPLATE_ASSIGNMENT(
121 X_SUBSCRIPTION_ID IN NUMBER
122 );
123 procedure INSERT_SUBREG_ROW (
124 X_SUBSCRIPTION_ID in NUMBER,
125 X_LAST_APPROVER_COMMENT in VARCHAR2,
126 X_APPROVER_USER_ID in NUMBER,
127 X_EFFECTIVE_END_DATE in DATE,
128 X_WF_ITEM_TYPE in VARCHAR2,
129 X_EFFECTIVE_START_DATE in DATE,
130 X_SUBSCRIPTION_REG_ID out NOCOPY NUMBER,
131 X_USER_ID in NUMBER,
132 X_STATUS_CODE in VARCHAR2,
133 X_CREATION_DATE in DATE,
134 X_CREATED_BY in NUMBER,
135 X_LAST_UPDATE_DATE in DATE,
136 X_LAST_UPDATED_BY in NUMBER,
137 X_LAST_UPDATE_LOGIN in NUMBER,
138 X_GRANT_DELEGATION_FLAG in VARCHAR2 := 'N'
139 );
140
141 /*
142 * Name : update_grant_delegation_flag
143 * Pre_reqs : None
144 * Description : Will update the information of the grant_delegation_flag
145 * Parameters :
146 * input parameters
147 * @param p_subscription_reg_id
148 * description: The subscription_reg_id of an enrollment
149 * required : Y
150 * validation : Must be a valid subscription_id. The procedure will not do
151 * any explicit validation.
152 * p_grant_delegation_flag:
153 * description: The Boolean equivallent int value of the grant_delegation_flag
154 * required : Y
155 * validation : Should be 0 or 1. The procedure will default it to
156 * 0, if null value is passed
157 *
158 * output parameters
159 * None
160 *
161 * Notes:
162 *
163 * This procedure is create as wrapper procedure to pass boolean
164 * values, as JDBC cannot handle boolean !!!!!
165 */
166 procedure update_grant_delegation_flag (
167 p_subscription_reg_id in number,
168 p_grant_delegation_flag in number
169 );
170
171 /*
172 * Name : update_grant_delegation_flag
173 * Pre_reqs : None
174 * Description : Will update the information of the grant_delegation_flag
175 * Parameters :
176 * input parameters
177 * @param p_subscription_reg_id
178 * description: The subscription_reg_id of an enrollment
179 * required : Y
180 * validation : Must be a valid subscription_id. The procedure will not do
181 * any explicit validation.
182 * p_grant_delegation_flag:
183 * description: The Boolean equivallent int value of the grant_delegation_flag
184 * required : Y
185 * validation : Should be 0 or 1. The procedure will default it to
186 * 0, if null value is passed
187 * p_grant_delegation_role:
188 * description: The Boolean equivallent int value of the decision
189 * whether to grant delegation role or not
190 * required : Y
191 * validation : Should be 0 or 1. The procedure will default it to
192 * 0, if null value is passed
193 *
194 * output parameters
195 * None
196 *
197 * Notes:
198 *
199 * This procedure is create as wrapper procedure to pass boolean
200 * values, as JDBC cannot handle boolean !!!!!
201 */
202 procedure update_grant_delegation_flag (
203 p_subscription_reg_id in number,
204 p_grant_delegation_flag in number,
205 p_grant_delegation_role in number
206 );
207
208
209 /*
210 * Name : update_grant_delegation_flag
211 * Pre_reqs : None
212 * Description : Will update the information of the grant_delegation_flag
213 * Parameters :
214 * input parameters
215 * @param p_subscription_reg_id
216 * description: The subscription_reg_id of an enrollment
217 * required : Y
218 * validation : Must be a valid subscription_id. The procedure will not do
219 * any explicit validation.
220 * p_grant_delegation_flag:
221 * description: The Boolean value of the grant_delegation_flag
222 * required : Y
223 * validation : Should be true or false. The procedure will default it to
224 * false, if null value is passed
225 *
226 * output parameters
227 * None
228 *
229 * Notes:
230 *
231 * The procedure will try to update the grant_delegation_flag based on the input values.
232 * If a procedure can not find any matching row, then it will not raise any exception
233 * but will not update any rows. It is caller's responsibility to make sure that
234 * the correct parameters are passed
235 */
236 procedure update_grant_delegation_flag (
237 p_subscription_reg_id in number,
238 p_grant_delegation_flag in boolean
239 );
240
241
242 /*
243 * Name : update_grant_delegation_flag
244 * Pre_reqs : None
245 * Description : Will update the information of the grant_delegation_flag
246 * Parameters :
247 * input parameters
248 * @param p_subscription_id
249 * description: The subscription_id of an enrollment
250 * required : Y
251 * validation : Must be a valid subscription_id. The procedure will not do
252 * any explicit validation.
253 * p_user_name:
254 * description: The user_name of a user
255 * required : Y
256 * validation : Must be a valid user_name.The procedure will not do
257 * any explicit validation.
258 * p_grant_delegation_flag:
259 * description: The Boolean equivallent int value of the grant_delegation_flag
260 * required : Y
261 * validation : Should be 0 or 1. The procedure will default it to
262 * 0, if null value is passed
263 *
264 * output parameters
265 * None
266 *
267 * Notes:
268 *
269 * This procedure is create as wrapper procedure to pass boolean
270 * values, as JDBC cannot handle boolean !!!!!
271 */
272 procedure update_grant_delegation_flag (
273 p_subscription_id in number,
274 p_user_name in varchar2,
275 p_grant_delegation_flag in number
276 );
277
278
279
280
281 /*
282 * Name : update_grant_delegation_flag
283 * Pre_reqs : None
284 * Description : Will update the information of the grant_delegation_flag
285 * Parameters :
286 * input parameters
287 * @param p_subscription_id
288 * description: The subscription_id of an enrollment
289 * required : Y
290 * validation : Must be a valid subscription_id. The procedure will not do
291 * any explicit validation.
292 * p_user_name:
293 * description: The user_name of a user
294 * required : Y
295 * validation : Must be a valid user_name.The procedure will not do
296 * any explicit validation.
297 * p_grant_delegation_flag:
298 * description: The Boolean value of the grant_delegation_flag
299 * required : Y
300 * validation : Should be true or false. The procedure will default it to
301 * false, if null value is passed
302 *
303 * output parameters
304 * None
305 *
306 * Notes:
307 *
308 * The procedure will try to update the grant_delegation_flag based on the input values.
309 * If a procedure can not find any matching row, then it will not raise any exception
310 * but will not update any rows. It is caller's responsibility to make sure that
311 * the correct parameters are passed
312 */
313 procedure update_grant_delegation_flag (
314 p_subscription_id in number,
315 p_user_name in varchar2,
316 p_grant_delegation_flag in boolean
317 );
318
319
320 /*
321 * Name : update_grant_delegation_flag
322 * Pre_reqs : None
323 * Description : Will update the information of the grant_delegation_flag
324 * Parameters :
325 * input parameters
326 * @param p_subscription_id
327 * description: The subscription_id of an enrollment
328 * required : Y
329 * validation : Must be a valid subscription_id. The procedure will not do
330 * any explicit validation.
331 * p_user_id:
332 * description: The user_id of a user
333 * required : Y
334 * validation : Must be a valid user_id.The procedure will not do
335 * any explicit validation.
336 * p_grant_delegation_flag:
337 * description: The Boolean equivallent int value of the grant_delegation_flag
338 * required : Y
339 * validation : Should be 0 or 1. The procedure will default it to
340 * 0, if null value is passed
341 *
342 * output parameters
343 * None
344 *
345 * Notes:
346 *
347 * This procedure is create as wrapper procedure to pass boolean
348 * values, as JDBC cannot handle boolean !!!!!
349 */
350
351 procedure update_grant_delegation_flag (
352 p_subscription_id in number,
353 p_user_id in number,
354 p_grant_delegation_flag in number
355 );
356
357
358 /*
359 * Name : update_grant_delegation_flag
360 * Pre_reqs : None
361 * Description : Will update the information of the grant_delegation_flag
362 * Parameters :
363 * input parameters
364 * @param p_subscription_id
365 * description: The subscription_id of an enrollment
366 * required : Y
367 * validation : Must be a valid subscription_id. The procedure will not do
368 * any explicit validation.
369 * p_user_id:
370 * description: The user_id of a user
371 * required : Y
372 * validation : Must be a valid user_id.The procedure will not do
373 * any explicit validation.
374 * p_grant_delegation_flag:
375 * description: The Boolean value of the grant_delegation_flag
376 * required : Y
377 * validation : Should be true or false. The procedure will default it to
378 * false, if null value is passed
379 *
380 * output parameters
381 * None
382 *
383 * Notes:
384 *
385 * The procedure will try to update the grant_delegation_flag based on the input values.
386 * If a procedure can not find any matching row, then it will not raise any exception
387 * but will not update any rows. It is caller's responsibility to make sure that
388 * the correct parameters are passed
389 */
390 procedure update_grant_delegation_flag (
391 p_subscription_id in number,
392 p_user_id in number,
393 p_grant_delegation_flag in boolean
394 );
395
396 /*
397 * Name : get_delegation_role
398 * Pre_reqs : None
399 * Description : Will determine if an enrollment has a delegation role
400 * Parameters :
401 * input parameters
402 * @param p_subscription_id
403 * description: The subscription_id of an enrollment
404 * required : Y
405 * validation : Must be a valid subscription_id
406 * output parameters
407 * x_delegation_role
408 * description: The value of the column auth_delegation_id of the table
409 * JTF_UM_ENROLLMENTS_B. This value will be 0, if no
410 * no delegation role has been defined for this enrollment
411 *
412 * Note:
413 *
414 * This API will raise an exception if no record is found which matches
415 * to the subscription_id being passed
416 */
417
418 procedure get_delegation_role(
419 p_subscription_id in number,
420 x_delegation_role out NOCOPY number
421 );
422
423 /**
424 * Procedure : get_grant_delegation_flag
425 * Type : Private
426 * Pre_reqs : None
427 * Description : Will return the value of the column grant_delegation_flag
428 * from the table JTF_UM_SUBSCRIPTIONS_REG
429 * Parameters :
430 * input parameters
431 * @param p_subscription_id
432 * description: The subscription_id of an enrollment
433 * required : Y
434 * validation : Must be a valid subscription_id
435 * p_user_id:
436 * description: The user_id of a user
437 * required : Y
438 * validation : Must be a valid user_id
439 * output parameters
440 * x_result: The Boolean value based on the column grant_delegation_flag
441 *
442 * Note:
443 *
444 * This API will raise an exception, if subscription_id or user_id is invalid
445 *
446 */
447
448 procedure get_grant_delegation_flag(
449 p_subscription_id in number,
450 p_user_id in number,
451 x_result out NOCOPY boolean
452 );
453
454 end JTF_UM_SUBSCRIPTIONS_PKG;