[Home] [Help]
PACKAGE BODY: APPS.OEONUM
Source
1 PACKAGE BODY OEONUM AS
2 /* $Header: OEXONUMB.pls 115.2 99/07/16 08:13:52 porting shi $ */
3
4 PROCEDURE dummy is
5 begin
6 null;
7 end;
8
9 PROCEDURE CreateSource
10 (
11 sequence_name IN VARCHAR2,
12 cache IN NUMBER,
13 min_value IN NUMBER,
14 start_with IN NUMBER,
15 return_status OUT NUMBER
16 )
17 IS
18
19 sql_buffer varchar2(240);
20 ddl_parameter varchar2(240);
21 rel_name varchar2(240);
22 cid integer;
23 ad_ddl_found boolean := TRUE;
24 oexoenum_found boolean := TRUE;
25 dummy varchar2(1);
26 package_result number;
27 x number;
28 out_status varchar2(240);
29 out_industry varchar2(240);
30 out_oracle_schema varchar2(240);
31
32
33 BEGIN
34
35 BEGIN
36
37 SELECT 'X' into dummy FROM ALL_SEQUENCES
38 WHERE SEQUENCE_NAME = sequence_name and rownum=1;
39
40
41 EXCEPTION
42
43 WHEN NO_DATA_FOUND THEN
44 null;
45
46 WHEN OTHERS THEN
47 return_status := -1;
48 OEONUM.Raise_Exception(Routine=>'OEONUM.CreateSource',
49 Operation=>'Create Source',
50 Message=>'Sql stmt 1');
51 return;
52
53 END;
54
55
56
57 /*
58 ** Check if the AD_DDL PL-SQL package is installed,
59 ** if it is then call it to create the sequence.
60 ** We are doing this for caompatibility issues. If the AD_DDL
61 ** package is not installed then then check to see if the
62 ** OEXOENUM package is installed. If it is
63 ** then let it create the sequence, else create it manually.
64 */
65
66 BEGIN
67
68 SELECT 'X'
69 INTO DUMMY
70 FROM ALL_SOURCE
71 WHERE NAME='AD_DDL'
72 AND ROWNUM = 1 ;
73 EXCEPTION
74
75 WHEN NO_DATA_FOUND THEN
76 ad_ddl_found := FALSE;
77
78 WHEN OTHERS THEN
79 return_status := -1;
80 OEONUM.Raise_Exception(Routine=>'OEONUM.CreateSource',
81 Operation=>'Create Source',
82 Message=>'Sql stmt 2');
83 return;
84
85 END;
86
87 /*
88 ** Check if the CREATE_OE_SEQUENCE PL-SQL package is installed,
89 ** if it is then call it else create the sequence here.
90 ** We are doing this for caompatibility issues, Rel 10.5 is the
91 ** one that has the OEXOENUM package installed in, so if we are
92 ** sending patches to 10.4.% we need to avoid calling this package.
93 */
94
95 if not ad_ddl_found then
96
97 BEGIN
98 SELECT 'X'
99 INTO DUMMY
100 FROM USER_SOURCE
101 WHERE NAME='OEXOENUM'
102 AND ROWNUM = 1 ;
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 oexoenum_found := FALSE;
106 WHEN OTHERS THEN
107 return_status := -1;
108 OEONUM.Raise_Exception(Routine=>'OEONUM.CreateSource',
109 Operation=>'Create Source',
110 Message=>'Sql stmt 3');
111 return;
112 END;
113
114 if not oexoenum_found then
115
116 sql_buffer := 'CREATE SEQUENCE ' || sequence_name || ' ' ||
117 'NOCACHE ' ||
118 'MINVALUE ' || to_char(min_value) ||
119 ' START WITH ' || to_char(start_with) ||
120 ' INCREMENT BY 1 ' ||
121 'NOCYCLE ' ||
122 'ORDER';
123
124 cid := dbms_sql.open_cursor;
125
126 dbms_sql.parse(cid,
127 sql_buffer,
128 dbms_sql.v7);
129
130 dbms_sql.close_cursor(cid);
131
132 return_status := 0;
133 else /* oexoenum found */
134
135 sql_buffer := 'BEGIN ' ||
136 'OEXOENUM.CREATE_OE_SEQUENCE ' ||
137 '( sequence_name => :sequence_name, ' ||
138 ' cache => :cache, ' ||
139 'min_value => :min_value, ' ||
140 'start_with => :start_with );' ||
141 'END;';
142
143 cid := dbms_sql.open_cursor;
144 dbms_sql.parse(cid,
145 sql_buffer,
146 dbms_sql.v7);
147
148 dbms_sql.bind_variable(cid,':sequence_name',sequence_name);
149 dbms_sql.bind_variable(cid,':cache',cache);
150 dbms_sql.bind_variable(cid,':min_value',min_value);
151 dbms_sql.bind_variable(cid,':start_with',start_with);
152
153 package_result := dbms_sql.execute(cid);
154
155 dbms_sql.close_cursor(cid);
156
157 return_status := 0;
158
159 end if; /* not oexoenum_found */
160
161 else /* ad_ddl_found */
162
163
164 /*
165 ** Build PL/SQL block for getting the oracle username
166 */
167
168 IF FND_INSTALLATION.GET_APP_INFO('FND',out_status,out_industry,
169 out_oracle_schema) then
170 x:=1;
171 else
172 x:=0;
173 end if;
174
175 if x = 0 then /* fnd_installation.get_app_info failed */
176 return_status := -1;
177 OEONUM.Raise_Exception(Routine=>'OEONUM.CreateSource',
178 Operation=>'Create Source',
179 Message=>' get_app_info failed 1');
180 return;
181
182 end if;
183
184 ddl_parameter := 'CREATE SEQUENCE ' || sequence_name || ' ' ||
185 ' NOCACHE ' ||
186 ' MINVALUE ' || to_char(min_value) ||
187 ' START WITH ' || to_char(start_with) ||
188 ' INCREMENT BY 1 ' ||
189 ' NOCYCLE ' ||
190 ' ORDER';
191
192
193 SELECT RELEASE_NAME INTO Rel_name FROM FND_PRODUCT_GROUPS
194 WHERE ROWNUM = 1;
195
196
197 if( SubStr(Rel_Name,1,4) = '10.6') then
198 sql_buffer := 'BEGIN ' ||
199 'AD_DDL.DO_DDL(' ||
200 ''''||out_oracle_schema||'''' ||
201 ',''OE'',' ||
202 to_char(AD_DDL.CREATE_SEQUENCE) ||
203 ','||
204 ''''||ddl_parameter||'''' || '); ' ||
205 'END;';
206 else
207 sql_buffer := 'BEGIN ' ||
208 'AD_DDL.DO_DDL(' ||
209 ''''||out_oracle_schema||'''' ||
210 ',''OE'',' ||
211 to_char(AD_DDL.CREATE_SEQUENCE) ||
212 ','||
213 ''''||ddl_parameter||'''' || ',' ||
214 ''''||sequence_name||'''' ||
215 '); ' ||
216 'END;';
217 end if;
218
219 cid := dbms_sql.open_cursor;
220 dbms_sql.parse(cid,
221 sql_buffer,
222 dbms_sql.v7);
223
224 package_result := dbms_sql.execute(cid);
225
226 dbms_sql.close_cursor(cid);
227
228 return_status := 0;
229
230
231 end if; /* not ad_ddl_found */
232
233
234 EXCEPTION
235
236 WHEN OTHERS THEN
237 if dbms_sql.is_open(cid) then
238 dbms_sql.close_cursor(cid);
239 end if;
240 return_status := -1;
241 OEONUM.Raise_Exception(Routine=>'OEONUM.CreateSource',
242 Operation=>'Create Source',
243 Message=>' Sql Stmt 3');
244 return;
245
246 END;
247
248
249 PROCEDURE GetNextNumber
250 (
251 sequence_name IN VARCHAR2,
252 returned_sequence OUT NUMBER,
253 return_status OUT NUMBER
254 )
255 IS
256
257 sql_buffer varchar2(200);
258 cid number;
259 package_result number;
260 sequence_number number;
261
262
263 BEGIN
264
265 sql_buffer := 'SELECT ' || sequence_name ||
266 '.NEXTVAL ' ||
267 ' FROM SYS.DUAL';
268
269 cid := dbms_sql.open_cursor;
270
271
272 dbms_sql.parse(cid,
273 sql_buffer,
274 dbms_sql.v7);
275
276 dbms_sql.define_column(cid,1,sequence_number);
277
278 package_result := dbms_sql.execute(cid);
279
280
281 if dbms_sql.fetch_rows(cid) > 0 then
282 dbms_sql.column_value(cid,1,sequence_number);
283 else
284 dbms_sql.close_cursor(cid);
285 return_status := -1;
286 OEONUM.Raise_Exception(Routine=>'OEONUM.GetNextNumber',
287 Operation=>'Get next number',
288 Message=>' Sql stmt 4');
289 return;
290
291 end if;
292
293
294 dbms_sql.close_cursor(cid);
295
296 returned_sequence := sequence_number;
297
298 return_status := 0;
299
300
301 EXCEPTION
302
303 WHEN OTHERS THEN
304 if dbms_sql.is_open(cid) then
305 dbms_sql.close_cursor(cid);
306 end if;
307 return_status := -1;
308 OEONUM.Raise_Exception(Routine=>'OEONUM.GetNextNumber',
309 Operation=>'Get next number',
310 Message=>' When Others');
311 return;
312
313 END GetNextNumber;
314
315 PROCEDURE GetCurrentNumber
316 (
317 sequence_name IN VARCHAR2,
318 returned_sequence OUT NUMBER,
319 return_status OUT NUMBER
320 )
321 IS
322
323 package_result number;
324 sequence_number number;
325 l_sequence_name VARCHAR2(40);
326
327
328 BEGIN
329 l_sequence_name := sequence_name;
330
331 SELECT LAST_NUMBER into sequence_number FROM ALL_SEQUENCES
332 WHERE SEQUENCE_NAME = l_sequence_name and rownum=1;
333
334 returned_sequence := sequence_number;
335
336 return_status:=0;
337
338
339 EXCEPTION
340
341 WHEN NO_DATA_FOUND THEN
342 return_status := -1;
343 OEONUM.Raise_Exception(Routine=>'OEONUM.GetCurrentNumber',
344 Operation=>'Get current number',
345 Message=>' Sql stmt 5, When no_data_found');
346 return;
347
348 WHEN OTHERS THEN
349 return_status := -1;
350 OEONUM.Raise_Exception(Routine=>'OEONUM.GetCurrentNumber',
351 Operation=>'Get current number',
352 Message=>' Sql stmt 5, when others');
353 return;
354
355
356 END GetCurrentNumber;
357
358 PROCEDURE OrderNumberSequence
359 (
360 source_id IN NUMBER,
361 action IN NUMBER,
362 cache NUMBER DEFAULT 100,
363 min_value NUMBER DEFAULT 1,
364 start_with NUMBER DEFAULT 1,
365 returned_sequence OUT NUMBER,
366 return_status OUT NUMBER
367 )
368 IS
369
370 sql_buffer varchar2(200);
371 cid number;
372 package_result number;
373 sequence_name varchar2(30);
374 sequence_number number(23);
375
376
377 OE_NUM_BASE_SEQ_NAME varchar2(24):='SO_ORDER_NUMBER_SEQUENCE';
378 OE_NUM_ACT_CREATE number:= 1;/* Create order number source */
379 OE_NUM_ACT_CURRENT number:= 2;/* Get current order number */
380 OE_NUM_ACT_NEXT number:= 3;/* Get next order number */
381 OE_NUM_DEFAULT_CACHE number:= 100;
382 OE_NUM_DEFAULT_START number:= 1;
383 DATABASE_OBJECT_LENGTH number:= 30;
384
385
386
387
388 BEGIN
389
390 if source_id is null then
391 return_status := -1;
392 OEONUM.Raise_Exception(Routine=>'OEONUM.OrderNumberSequence',
393 Operation=>'Order Number Sequence',
394 Message=>' Source id is null ');
395 return;
396
397 end if;
398
399
403 return_status := -1;
400 sequence_name := OE_NUM_BASE_SEQ_NAME || ltrim(rtrim(to_char(source_id)));
401
402 if length(sequence_name) > DATABASE_OBJECT_LENGTH then
404 OEONUM.Raise_Exception(Routine=>'OEONUM.OrderNumberSequence',
405 Operation=>'Order Number Sequence',
406 Message=>' Sequence Name Length exceeds ');
407 return;
408
409 end if;
410
411
412 if action = OE_NUM_ACT_CREATE then
413
414 OEONUM.CreateSource(sequence_name => sequence_name,
415 cache => cache,
416 min_value => min_value,
417 start_with => start_with,
418 return_status => return_status);
419
420 elsif action = OE_NUM_ACT_CURRENT then
421
422 OEONUM.GetCurrentNumber(sequence_name => sequence_name,
423 returned_sequence => sequence_number,
424 return_status => return_status);
425
426 returned_sequence:=sequence_number;
427
428 elsif action = OE_NUM_ACT_NEXT then
429
430 OEONUM.GetNextNumber(sequence_name => sequence_name,
431 returned_sequence => sequence_number,
432 return_status => return_status);
433
434 returned_sequence:=sequence_number;
435
436 else
437 return_status := -1;
438 OEONUM.Raise_Exception(Routine=>'OEONUM.OrderNumberSequence',
439 Operation=>'Order Number Sequence',
440 Message=>' Invalid Action ');
441 return;
442 end if;
443
444 EXCEPTION
445
446 WHEN OTHERS THEN
447 return_status := -1;
448 OEONUM.Raise_Exception(Routine=>'OEONUM.OrderNumberSequence',
449 Operation=>'Order Number Sequence',
450 Message=>' When Others');
451 return;
452
453 END OrderNumberSequence;
454
455 PROCEDURE Raise_exception
456 (
457 Routine IN VARCHAR2,
458 Operation IN VARCHAR2,
459 Message IN VARCHAR2
460 )
461 IS
462
463 x BOOLEAN;
464
465 BEGIN
466
467 x :=OE_MSG.Set_Buffer_Message('OE_EXC_INTERNAL_EXCEPTION',
468 'ROUTINE', Routine);
469 x :=OE_MSG.Set_Buffer_Message('OE_EXC_INTERNAL_EXCEPTION',
470 'OPERATION', Operation);
471 x :=OE_MSG.Set_Buffer_Message('OE_EXC_INTERNAL_EXCEPTION',
472 'MESSAGE',Message|| ' sqlcode:'||SQLCODE);
473
474
475
476 END;
477
478
479 END OEONUM;