[Home] [Help]
PACKAGE BODY: APPS.ASG_PUB_SEQUENCE_PKG
Source
1 PACKAGE BODY ASG_PUB_SEQUENCE_PKG as
2 /* $Header: asgpseqb.pls 120.3 2011/03/25 06:28:02 saradhak ship $ */
3
4
5 --
6 -- NAME
7 -- ASG_PUB_SEQUENCE
8 --
9 -- PURPOSE
10 --
11 -- HISTORY
12 -- SEP 08, 2008 trajasek changes done for SERVICEL to SERVICEP migration
13 -- SEP 21, 2004 yazhang using orace_id to get oracle_username for shcema name
14 -- MAR 31, 2003 ytian not to update the creation_date for update_row.
15 -- JUL 09, 2002 ytian Modified get_next_client_number.
16 -- JUN 26, 2002 ytian modified not to update STATUS.
17 -- JUn 25 2002 yazhang hardcoded asg_max_client_number with 1,000,000.
18 -- JUN 03, 2002 ytian changed _id pk type to varchar2.
19 -- MAR 22, 2002 ytian modified insert_row to insert Last_release_version
20 -- as 0, so that it gets created/upgraded surely.
21 -- Mar 21, 2002 ytian Updated update_row not to update
22 -- last_release_version
23 -- Mar 20, 2002 yazhang return value in getNextClientNumber
24 -- MAR 11, 2002 ytian added insert_row, update_row, upload_row
25 -- Mar 08, 2002 yazhang add get_next_client_number
26 -- Mar 07, 2002 ytian created
27
28 /* get the clientnumber */
29 Function getCLIENT_NUMBER(X_Clientid IN Varchar2) RETURN number IS
30
31 CURSOR C_CLIENT_NUMBER(v_clientid varchar2)
32 IS
33 Select client_number
34 from ASG_USER
35 where upper(user_name) = upper(v_clientid);
36
37 v_client_number number;
38 BEGIN
39
40 OPEN C_CLIENT_NUMBER(x_clientid);
41 FETCH C_CLIENT_NUMBER into v_client_number;
42 IF C_CLIENT_NUMBER%NOTFOUND then
43 CLOSE C_CLIENT_NUMBER; return null;
44 END IF;
45 CLOSE C_CLIENT_NUMBER;
46 return v_client_number;
47
48 END getCLIENT_NUMBER;
49
50
51 Function getNEXT_VALUE RETURN number
52 IS
53
54 V_VAL_Z varchar2(100);
55 V_DEFINED_Z boolean;
56 BEGIN
57 /*
58 FND_PROFILE.GET_SPECIFIC('ASG_MAX_CLIENT_NUM',null, null, 279,
59 V_VAL_Z, V_DEFINED_Z);
60
61 if v_val_z is null then
62 v_val_z := 1000000;
63 END if;
64 */
65 v_val_z := 1000000;
66
67 return v_val_z;
68
69 END getNEXT_VALUE;
70
71 Function getSTART_VALUE(X_CLIENT_NUMBER number, X_TABLE_NAME varchar2,
72 X_PRIMARY_KEY varchar2, X_START_MOBILE varchar2 ) RETURN number
73 IS
74
75 v_servername varchar2(240);
76 v_id number;
77 v_start number;
78 dummy_num number;
79 v_statement varchar2(1000);
80 v_minvalue number;
81 v_start_mobile number;
82 v_max_users number;
83 v_seq_statement VARCHAR2(1000);
84 l_csl_sequence_name VARCHAR2(30):=NULL;
85 TYPE cur_typ IS REF CURSOR;
86 c_csl_seq cur_typ;
87 l_csl_sequence_no NUMBER := NULL;
88 BEGIN
89
90 v_max_users := getNEXT_VALUE;
91 v_start_mobile := to_number(x_start_mobile);
92
93 --Get the Sequence Name for ServiceL
94 v_seq_statement := 'SELECT SEQUENCE_NAME FROM ASG_PUB_SEQUENCE ' ||
95 'WHERE PUBLICATION_ID IN (''SERVICEL'',''JTM'' ) ' ||
96 'AND B_TABLE = :1 ' ||
97 'AND B_COLUMN = :2 ' ;
98
99 OPEN c_csl_seq FOR v_seq_statement USING X_TABLE_NAME, X_PRIMARY_KEY;
100 FETCH c_csl_seq INTO l_csl_sequence_name ;
101 CLOSE c_csl_seq;
102
103 --Get the Latest Sequence from ServiceL
104 IF l_csl_sequence_name IS NOT NULL THEN
105
106 v_seq_statement := 'SELECT CURR_VAL FROM ASG_SEQUENCE_PARTITIONS ' ||
107 'WHERE NAME = :1 ' ||
108 'AND MOD(CURR_VAL,1000000) = :2 ' ;
109
110 OPEN c_csl_seq FOR v_seq_statement USING l_csl_sequence_name, X_CLIENT_NUMBER;
111 FETCH c_csl_seq INTO l_csl_sequence_no ;
112 CLOSE c_csl_seq;
113
114 END IF;
115
116 IF l_csl_sequence_no IS NOT NULL THEN
117 v_start := l_csl_sequence_no;
118 ELSE --if csl sequence not found then check from base tables
119
120 v_statement := 'select nvl(max(' || x_primary_key ||
121 '), '|| x_client_number ||
122 ') from '|| x_table_name ||
123 ' where MOD(' || x_primary_key || ',' || v_max_users ||
124 ') = '|| x_client_number;
125
126
127 v_id := DBMS_SQL.OPEN_CURSOR;
128 DBMS_SQL.PARSE(v_id, v_statement, DBMS_SQL.native);
129 DBMS_SQL.DEFINE_COLUMN(v_id, 1, v_start);
130 dummy_num := DBMS_SQL.EXECUTE(v_id);
131 if DBMS_SQL.FETCH_ROWS(v_id) = 0 then
132 return -1;
133 END if;
134 DBMS_SQL.COLUMN_VALUE(v_id, 1, v_start);
135 DBMS_SQL.CLOSE_CURSOR(v_id);
136 END IF;
137
138 if(v_start > v_start_mobile) then
139 v_minValue := v_start;
140 else
141 v_minValue := v_start_mobile + x_client_number;
142 END if;
143
144 return v_minValue;
145
146 END getSTART_VALUE;
147
148
149 Function Next_Client_Number (MaxClientNum INTEGER, StartNum INTEGER ) RETURN INTEGER IS
150
151 Max_Num INTEGER;
152 Temp_Num INTEGER;
153 Curr_Num INTEGER;
154 Prev_Num INTEGER;
155 Ret_Flag BOOLEAN := FALSE;
156 CURSOR client_num_cur(StartNum INTEGER) IS
157 SELECT client_number
158 FROM ASG_USER
159 WHERE client_number > StartNum
160 ORDER BY client_number;
161 CURSOR start_num_cur(StartNum INTEGER) IS
162 SELECT client_number
163 FROM ASG_USER
164 WHERE client_number = StartNum;
165 CURSOR max_client_num_cur IS
166 SELECT MAX(Client_Number)
167 FROM ASG_USER;
168 BEGIN
169
170 if (StartNum >= MaxClientNum) then
171 return(0);
172 end if;
173 open start_num_cur(StartNum+1);
174 fetch start_num_cur into Temp_Num;
175 if (start_num_cur%NOTFOUND AND ((StartNum+1) <= MaxClientNum) ) then
176 Ret_Flag := TRUE;
177 end if;
178 close start_num_cur;
179
180 if (Ret_Flag = TRUE) then
181 return (StartNum+1);
182 end if;
183
184 open max_client_num_cur;
185 fetch max_client_num_cur into Max_Num;
186 close max_client_num_cur;
187 IF((Max_Num IS NOT NULL) AND (MaxClientNum >= (Max_Num+1))) THEN
188 RETURN (Max_Num+1);
189 END IF;
190
191
192 OPEN client_num_cur(StartNum);
193 Prev_Num := StartNum;
194 Curr_Num := StartNum;
195
196 LOOP
197 -- Else loop thru all the rows in the table
198 -- To identify an unused number
199 FETCH client_num_cur INTO Temp_Num;
200 IF (Temp_Num IS NOT NULL) THEN
201 Prev_Num := Curr_Num;
202 Curr_Num := Temp_Num;
203 END IF;
204 IF (client_num_cur%NOTFOUND) THEN
205 -- Have looked at all the rows
206 -- Close the cursor
207 CLOSE client_num_cur;
208 IF ((Prev_Num+1) < Curr_Num ) THEN
209 -- We have a number to use
210 return (Prev_Num+1);
211 ELSE
212 if (Curr_Num < MaxClientNum) then
213 return (Curr_Num+1);
214 else
215 return (0);
216 end if;
217 END IF;
218 END IF;
219
220 -- Else check if there is a hole in the sequence
221 IF ((Prev_Num+1) < Curr_Num) THEN
222 CLOSE client_num_cur;
223 return (Prev_Num+1);
224 END IF;
225 END LOOP;
226 END Next_Client_Number;
227
228
229 Function Get_Next_Client_Number return INTEGER IS
230 l_max_client_num_char VARCHAR2(40);
231 l_max_client_num INTEGER;
232 client_number INTEGER;
233 BEGIN
234 /* FND_PROFILE.get('ASG_MAX_CLIENT_NUMBER', l_max_client_num_char);*/
235
236 l_max_client_num := null;
237 if l_max_client_num is null
238 then
239 l_max_client_num := 1999999;
240 end if;
241 client_number := Next_Client_Number(l_max_client_num,0);
242 return client_number;
243 END Get_Next_Client_Number;
244
245
246
247 procedure insert_row (
248 x_SEQUENCE_ID in VARCHAR2,
249 x_SEQUENCE_NAME in VARCHAR2,
250 x_PUBLICATION_ID in VARCHAR2,
251 x_B_SCHEMA in VARCHAR2,
252 x_B_TABLE in VARCHAR2,
253 x_B_COLUMN in VARCHAR2,
254 x_MOBILE_VALUE in VARCHAR2,
255 x_ENABLED in VARCHAR2,
256 x_STATUS in VARCHAR2,
257 x_CURRENT_RELEASE_VERSION in NUMBER,
258 x_LAST_RELEASE_VERSION in NUMBER,
259 x_CREATION_DATE in DATE,
260 x_CREATED_BY in NUMBER,
261 x_LAST_UPDATE_DATE in DATE,
262 x_LAST_UPDATED_BY in NUMBER )
263 is
264
265 l_b_schema varchar2(30);
266
267 BEGIN
268 begin
269 select oracle_username into l_b_schema
270 from fnd_oracle_userid
271 where oracle_id = X_B_SCHEMA;
272 exception
273 when others then
274 l_b_schema := x_B_SCHEMA;
275 end;
276 insert into ASG_PUB_SEQUENCE (
277 SEQUENCE_ID,
278 SEQUENCE_NAME,
279 PUBLICATION_ID,
280 B_SCHEMA,
281 B_TABLE,
282 B_COLUMN,
283 MOBILE_VALUE,
284 ENABLED,
285 STATUS,
286 CURRENT_RELEASE_VERSION,
287 LAST_RELEASE_VERSION,
288 CREATION_DATE,
289 CREATED_BY,
290 LAST_UPDATE_DATE,
291 LAST_UPDATED_BY
292 ) values (
293 decode(x_SEQUENCE_ID,FND_API.G_MISS_CHAR, NULL, x_SEQUENCE_ID),
294 decode(x_SEQUENCE_NAME,FND_API.G_MISS_CHAR, NULL, x_SEQUENCE_NAME),
295 decode(x_PUBLICATION_ID,FND_API.G_MISS_CHAR,NULL, x_PUBLICATION_ID),
296 decode(l_B_SCHEMA,FND_API.G_MISS_CHAR, NULL, l_B_SCHEMA),
297 decode(x_B_TABLE,FND_API.G_MISS_CHAR, NULL, x_B_TABLE),
298 decode(x_B_COLUMN,FND_API.G_MISS_CHAR, NULL,x_B_COLUMN),
299 decode(x_MOBILE_VALUE,FND_API.G_MISS_CHAR, NULL, x_MOBILE_VALUE),
300 decode(x_ENABLED,FND_API.G_MISS_CHAR, NULL, x_ENABLED),
301 'N',
302 decode(x_CURRENT_RELEASE_VERSION,FND_API.G_MISS_NUM, NULL,
303 x_CURRENT_RELEASE_VERSION),
304 0,
305 decode(x_CREATION_DATE,FND_API.G_MISS_DATE, NULL, x_CREATION_DATE),
306 decode(x_CREATED_BY,FND_API.G_MISS_NUM, NULL, x_CREATED_BY),
307 decode(x_LAST_UPDATE_DATE,FND_API.G_MISS_DATE, NULL,
308 x_LAST_UPDATE_DATE),
309 decode(x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATED_BY)
310 );
311
312 END INSERT_ROW;
313
314 procedure update_row (
315 x_SEQUENCE_ID in VARCHAR2,
316 x_SEQUENCE_NAME in VARCHAR2,
317 x_PUBLICATION_ID in VARCHAR2,
318 x_B_SCHEMA in VARCHAR2,
319 x_B_TABLE in VARCHAR2,
320 x_B_COLUMN in VARCHAR2,
321 x_MOBILE_VALUE in VARCHAR2,
322 x_ENABLED in VARCHAR2,
323 x_STATUS in VARCHAR2,
324 x_CURRENT_RELEASE_VERSION in NUMBER,
325 x_LAST_RELEASE_VERSION in NUMBER,
326 x_CREATION_DATE in DATE,
327 x_CREATED_BY in NUMBER,
328 x_LAST_UPDATE_DATE in DATE,
329 x_LAST_UPDATED_BY in NUMBER )
330 is
331
332 l_b_schema varchar2(30);
333
334 BEGIN
335
336 begin
337 select oracle_username into l_b_schema
338 from fnd_oracle_userid
339 where oracle_id = X_B_SCHEMA;
340 exception
341 when others then
342 l_b_schema := x_B_SCHEMA;
343 end;
344
345 update asg_pub_sequence set
346 sequence_id = x_sequence_id,
347 sequence_name = x_sequence_name,
348 publication_id = x_publication_id,
349 b_schema = l_b_schema,
350 b_table = x_b_table,
351 b_column = x_b_column,
352 mobile_value = x_mobile_value,
353 enabled = x_enabled,
354 -- status = x_status,
355 current_release_version = x_current_release_version,
356 -- last_release_version = x_last_release_version,
357 -- CREATION_DATE = x_CREATION_DATE,
358 -- created_by = x_created_by,
359 last_update_date = x_last_update_date,
360 last_updated_by = x_last_updated_by
361 where sequence_id = X_SEQUENCE_ID;
362
363 if (sql%notfound ) then
364 raise no_data_found;
365 end if;
366 END UPDATE_ROW;
367
368 procedure load_row (
369 x_SEQUENCE_ID in VARCHAR2,
370 x_SEQUENCE_NAME in VARCHAR2,
371 x_PUBLICATION_ID in VARCHAR2,
372 x_B_SCHEMA in VARCHAR2,
373 x_B_TABLE in VARCHAR2,
374 x_B_COLUMN in VARCHAR2,
375 x_MOBILE_VALUE in VARCHAR2,
376 x_ENABLED in VARCHAR2,
377 x_STATUS in VARCHAR2,
378 x_CURRENT_RELEASE_VERSION in NUMBER,
379 x_LAST_RELEASE_VERSION in NUMBER,
380 x_CREATION_DATE in DATE,
381 x_CREATED_BY in NUMBER,
382 x_LAST_UPDATE_DATE in DATE,
383 x_LAST_UPDATED_BY in NUMBER,
384 p_owner in VARCHAR2) IS
385
386 l_user_id number := 0;
387
388 BEGIN
389
390 if (p_owner = 'SEED' ) then
391 l_user_id := 1;
392 end if;
393
394 asg_pub_sequence_pkg.UPDATE_ROW (
395 x_SEQUENCE_ID => x_SEQUENCE_ID,
396 x_SEQUENCE_NAME => x_SEQUENCE_NAME,
397 x_PUBLICATION_ID => x_PUBLICATION_ID,
398 x_B_SCHEMA => x_B_SCHEMA,
399 x_B_TABLE => x_B_TABLE,
400 x_B_COLUMN => x_B_COLUMN,
401 x_MOBILE_VALUE => x_MOBILE_VALUE,
402 x_ENABLED => x_ENABLED,
403 x_STATUS => x_STATUS,
404 x_CURRENT_RELEASE_VERSION => x_CURRENT_RELEASE_VERSION,
405 x_LAST_RELEASE_VERSIOn => x_LAST_RELEASE_VERSION,
406 x_CREATION_DATE => x_CREATION_DATE,
407 x_CREATED_BY => x_CREATED_BY,
408 x_LAST_UPDATE_DATE => sysdate,
409 x_LAST_UPDATED_BY => l_USER_ID);
410
411 EXCEPTION
412 when no_DATA_FOUND THEN
413 asg_pub_sequence_pkg.insert_row (
414 x_SEQUENCE_ID => x_SEQUENCE_ID,
415 x_SEQUENCE_NAME => x_SEQUENCE_NAME,
416 x_PUBLICATION_ID => x_PUBLICATION_ID,
417 x_B_SCHEMA => x_B_SCHEMA,
418 x_B_TABLE => x_B_TABLE,
419 x_B_COLUMN => x_B_COLUMN,
420 x_MOBILE_VALUE => x_MOBILE_VALUE,
421 x_ENABLED => x_ENABLED,
422 x_STATUS => x_STATUS,
423 x_CURRENT_RELEASE_VERSION => x_CURRENT_RELEASE_VERSION,
424 x_LAST_RELEASE_VERSIOn => x_LAST_RELEASE_VERSION,
425 x_CREATION_DATE => sysdate,
426 x_CREATED_BY => l_user_id,
427 x_LAST_UPDATE_DATE => sysdate,
428 x_LAST_UPDATED_BY => l_USER_ID);
429
430 END LOAD_ROW;
431
432 END ASG_PUB_SEQUENCE_PKG;
433