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.ASG_PUB_SEQUENCE_PKG

Source


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