1 PACKAGE BODY PA_SYSTEM_NUMBERS_PKG AS
2 --$Header: PASNUMTB.pls 120.0 2005/05/30 07:05:56 appldev noship $
3
4 procedure GET_NEXT_NUMBER (
5 p_system_number_id IN NUMBER := NULL
6 ,p_object1_pk1_value IN NUMBER := NULL
7 ,p_object1_type IN VARCHAR2 := NULL
8 ,p_object2_pk1_value IN NUMBER := NULL
9 ,p_object2_type IN VARCHAR2 := NULL
10
11 ,x_system_number_id OUT NOCOPY NUMBER
12 ,x_next_number OUT NOCOPY NUMBER
13 ,x_return_status OUT NOCOPY VARCHAR2
14 ,x_msg_count OUT NOCOPY NUMBER
15 ,x_msg_data OUT NOCOPY VARCHAR2
16 ) is
17
18 l_system_id NUMBER;
19 l_next_number NUMBER;
20
21 /*CURSOR lock_number_record
22 IS
23 SELECT system_number_id
24 FROM PA_SYSTEM_NUMBERS
25 WHERE p_object1_pk1_value = object1_pk1_value
26 FOR UPDATE of system_number_id NOWAIT; */
27
28 CURSOR c_next_num IS
29 SELECT system_number_id, next_number
30 from PA_SYSTEM_NUMBERS
31 where system_number_id = p_system_number_id;
32
33 cp_next_num c_next_num%ROWTYPE;
34
35 CURSOR c_next_obj_num IS
36 SELECT system_number_id, next_number
37 from PA_SYSTEM_NUMBERS
38 where p_object1_pk1_value = object1_pk1_value
39 and p_object1_type = object1_type
40 and nvl(p_object2_pk1_value,0) = nvl(object2_pk1_value,0)
41 and nvl(p_object2_type,' ') = nvl(object2_type,' ')
42 FOR UPDATE of next_number NOWAIT;
43
44 cp_next_obj_num c_next_obj_num%ROWTYPE;
45
46 BEGIN
47
48 pa_debug.init_err_stack('PA_SYSTEM_NUMBERS_PKG:GET_NEXT_NUMBER');
49
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51
52 if p_system_number_id is null then
53 if p_object1_pk1_value is null OR p_object1_type is null then
54 PA_UTILS.Add_Message( p_app_short_name => 'PA'
55 ,p_msg_name => 'PA_CI_SYS_NUM_NULL_VALUES');
56 x_return_status := FND_API.G_RET_STS_ERROR;
57 PA_DEBUG.RESET_ERR_STACK;
58 return;
59 end if;
60 end if;
61
62
63 --if record already exists, retrieve the next available number
64 if p_system_number_id is not null then
65 open c_next_num;
66 fetch c_next_num into cp_next_num;
67 if (c_next_num%notfound) then
68 close c_next_num;
69 raise no_data_found;
70 end if;
71 l_system_id := cp_next_num.system_number_id;
72 l_next_number := cp_next_num.next_number;
73 close c_next_num;
74 else
75 open c_next_obj_num;
76 fetch c_next_obj_num into cp_next_obj_num;
77 -- here we need to add a new record for these 2 objects
78 if (c_next_obj_num%notfound) then
79 close c_next_obj_num;
80 INSERT_ROW (
81 p_object1_pk1_value
82 ,p_object1_type
83 ,p_object2_pk1_value
84 ,p_object2_type
85 ,NULL
86 ,x_next_number
87 ,x_system_number_id
88 ,x_return_status
89 ,x_msg_count
90 ,x_msg_data);
91 --l_system_id := x_system_number_id;
92 --l_next_number := x_next_number;
93 return;
94
95 else
96 l_system_id := cp_next_obj_num.system_number_id;
97 l_next_number := cp_next_obj_num.next_number;
98 close c_next_obj_num;
99 end if;
100 end if;
101
102 x_system_number_id := l_system_id;
103 x_next_number := l_next_number;
104
105 --now bump the existing number
106 UPDATE_ROW (
107 l_system_id,null,null,null,null,l_next_number+1,x_return_status,x_msg_count,x_msg_data);
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
112 RAISE;
113
114 end GET_NEXT_NUMBER;
115
116 procedure INSERT_ROW (
117 p_object1_pk1_value IN NUMBER
118 ,p_object1_type IN VARCHAR2
119
120 ,p_object2_pk1_value IN NUMBER := NULL
121 ,p_object2_type IN VARCHAR2 := NULL
122 ,p_next_number IN NUMBER := NULL
123
124 ,x_next_number OUT NOCOPY NUMBER
125 ,x_system_number_id OUT NOCOPY NUMBER
126 ,x_return_status OUT NOCOPY VARCHAR2
127 ,x_msg_count OUT NOCOPY NUMBER
128 ,x_msg_data OUT NOCOPY VARCHAR2
129 ) is
130
131 l_system_id NUMBER;
132 l_next_number NUMBER;
133 l_rowid ROWID;
134
135
136 cursor C is select ROWID from PA_SYSTEM_NUMBERS
137 where system_number_id = l_system_id;
138
139
140 BEGIN
141
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143
144 --get the unique system number id from the Oracle Sequence
145 /* Commented for bug 3866224 to remove hard coded schema reference and modified as below
146 SELECT pa.pa_system_numbers_s.nextval */
147 SELECT pa_system_numbers_s.nextval
148 INTO l_system_id
149 FROM DUAL;
150
151 if p_next_number is NULL then
152 l_next_number := 1;
153 else
154 l_next_number := p_next_number;
155 end if;
156
157 insert into PA_SYSTEM_NUMBERS (
158 system_number_id
159 ,object1_pk1_value
160 ,object1_type
161 ,object2_pk1_value
162 ,object2_type
163 ,next_number
164 ,LAST_UPDATED_BY
165 ,CREATED_BY
166 ,CREATION_DATE
167 ,LAST_UPDATE_DATE
168 ,LAST_UPDATE_LOGIN
169
170 ) VALUES (
171 l_system_id
172 ,p_object1_pk1_value
173 ,p_object1_type
174 ,p_object2_pk1_value
175 ,p_object2_type
176 ,l_next_number+1
177 ,fnd_global.user_id
178 ,fnd_global.user_id
179 ,sysdate
180 ,sysdate
181 ,fnd_global.user_id
182 );
183
184
185 open c;
186 fetch c into l_ROWID;
187 if (c%notfound) then
188 close c;
189 raise no_data_found;
190 end if;
191 close c;
192 x_system_number_id := l_system_id;
193 x_next_number := l_next_number;
194
195 EXCEPTION
196 -- set error message: in case more than user CREATED a new row
197 WHEN DUP_VAL_ON_INDEX then
198 PA_UTILS.Add_Message( p_app_short_name => 'PA'
199 ,p_msg_name => 'PA_UPDATE_FAILED');
200 x_return_status := FND_API.G_RET_STS_ERROR;
201
202
203 WHEN OTHERS THEN
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
205 RAISE;
206
207 end INSERT_ROW;
208
209 procedure UPDATE_ROW (
210 p_system_number_id IN NUMBER := NULL
211 ,p_object1_pk1_value IN NUMBER := NULL
212 ,p_object1_type IN VARCHAR2 := NULL
213
214 ,p_object2_pk1_value IN NUMBER := NULL
215 ,p_object2_type IN VARCHAR2 := NULL
216 ,p_next_number IN NUMBER := NULL
217
218 ,x_return_status OUT NOCOPY VARCHAR2
219 ,x_msg_count OUT NOCOPY NUMBER
220 ,x_msg_data OUT NOCOPY VARCHAR2
221 ) is
222 begin
223 x_return_status := FND_API.G_RET_STS_SUCCESS;
224
225 update PA_SYSTEM_NUMBERS set
226 object1_pk1_value = Nvl(p_object1_pk1_value,object1_pk1_value)
227 ,object1_type = Nvl(p_object1_type,object1_type)
228 ,object2_pk1_value = Nvl(p_object2_pk1_value,object2_pk1_value)
229 ,object2_type = Nvl(p_object2_type,object2_type)
230 ,next_number = Nvl(p_next_number,next_number)
231 ,LAST_UPDATED_BY = fnd_global.user_id
232 ,LAST_UPDATE_DATE = sysdate
233 ,LAST_UPDATE_LOGIN = fnd_global.login_id
234 where system_number_id = nvl(p_system_number_id,0)
235 OR (object1_pk1_value = p_object1_pk1_value AND
236 p_object1_type = p_object1_type AND
237 nvl(object2_pk1_value, 0) = nvl(p_object2_pk1_value,0) AND
238 nvl(p_object2_type,' ') = nvl(p_object2_type,' ') );
239
240 if (sql%notfound) then
241 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_CI_SYS_NUM_NOT_FOUND');
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 end if;
244
245 EXCEPTION
246 WHEN OTHERS THEN
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
248 RAISE;
249 end UPDATE_ROW;
250
251
252
253 procedure DELETE_ROW (
254 p_system_number_id IN NUMBER := NULL
255 ,p_object1_pk1_value IN NUMBER := NULL
256 ,p_object1_type IN VARCHAR2 := NULL
257
258 ,p_object2_pk1_value IN NUMBER := NULL
259 ,p_object2_type IN VARCHAR2 := NULL
260
261 ,x_return_status OUT NOCOPY VARCHAR2
262 ,x_msg_count OUT NOCOPY NUMBER
263 ,x_msg_data OUT NOCOPY VARCHAR2
264
265 ) is
266 begin
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268
269 DELETE FROM PA_SYSTEM_NUMBERS
270 where system_number_id = nvl(p_system_number_id,0)
271 OR (object1_pk1_value = p_object1_pk1_value AND
272 p_object1_type = p_object1_type AND
273 nvl(object2_pk1_value, 0) = nvl(p_object2_pk1_value,0) AND
274 nvl(p_object2_type,' ') = nvl(p_object2_type,' ') );
275
276
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281 RAISE;
282 end DELETE_ROW;
283
284 END PA_SYSTEM_NUMBERS_PKG;
[Home] [Help]