[Home] [Help]
PACKAGE BODY: APPS.FM_ROUT_DEP_DBL
Source
1 PACKAGE BODY FM_ROUT_DEP_DBL AS
2 /* $Header: GMDPRDDB.pls 115.2 2002/11/08 23:04:56 txdaniel noship $ */
3 /*============================================================================
4 | Copyright (c) 2001 Oracle Corporation
5 | TVP, Reading
6 | All rights reserved
7 =============================================================================
8 | FILENAME
9 | GMDPRDDB.pls
10 |
11 | DESCRIPTION
12 | Package body for FM_ROUT_DEP table handlers
13 |
14 |
15 |
16 | NOTES
17 |
18 | HISTORY
19 | 20-MAR-01 Thomas Daniel Created
20 |
21 | - create_row
22 | - fetch_row
23 | - update_row
24 | - lock_row
25 |
26 |
27 =============================================================================
28 */
29
30
31 /* Api start of comments
32 +============================================================================
33 | FUNCTION NAME
34 | insert_row
35 |
36 | TYPE
37 | Private
38 | USAGE
39 | Insert_Row will insert a row in fm_rout_dep
40 |
41 |
42 | DESCRIPTION
43 | Insert_Row will insert a row in fm_rout_dep
44 |
45 |
46 |
47 | PARAMETERS
48 | p_out_dep IN fm_rout_dep%ROWTYPE
49 | x_out_dep OUT fm_rout_dep%ROWTYPE
50 |
51 | RETURNS
52 | BOOLEAN
53 | HISTORY
54 | 20-MAR-01 Thomas Daniel Created
55 |
56 |
57 |
58 +=============================================================================
59 Api end of comments
60 */
61
62 FUNCTION insert_row (
63 p_out_dep IN FM_ROUT_DEP%ROWTYPE) RETURN BOOLEAN IS
64 BEGIN
65
66 INSERT INTO FM_ROUT_DEP
67 (
68 ROUTINGSTEP_NO
69 ,DEP_ROUTINGSTEP_NO
70 ,ROUTING_ID
71 ,DEP_TYPE
72 ,REWORK_CODE
73 ,STANDARD_DELAY
74 ,MINIMUM_DELAY
75 ,MAX_DELAY
76 ,TRANSFER_QTY
77 ,ITEM_UM
78 ,TEXT_CODE
79 ,LAST_UPDATED_BY
80 ,CREATED_BY
81 ,LAST_UPDATE_DATE
82 ,CREATION_DATE
83 ,LAST_UPDATE_LOGIN
84 ,TRANSFER_PCT
85 )
86 VALUES
87 (
88 p_out_dep.ROUTINGSTEP_NO
89 ,p_out_dep.DEP_ROUTINGSTEP_NO
90 ,p_out_dep.ROUTING_ID
91 ,p_out_dep.DEP_TYPE
92 ,p_out_dep.REWORK_CODE
93 ,p_out_dep.STANDARD_DELAY
94 ,p_out_dep.MINIMUM_DELAY
95 ,p_out_dep.MAX_DELAY
96 ,p_out_dep.TRANSFER_QTY
97 ,p_out_dep.ITEM_UM
98 ,p_out_dep.TEXT_CODE
99 ,p_out_dep.LAST_UPDATED_BY
100 ,p_out_dep.CREATED_BY
101 ,p_out_dep.LAST_UPDATE_DATE
102 ,p_out_dep.CREATION_DATE
103 ,p_out_dep.LAST_UPDATE_LOGIN
104 ,p_out_dep.TRANSFER_PCT
105 );
106 RETURN TRUE;
107 EXCEPTION
108 WHEN OTHERS THEN
109 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
110 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
111 FND_MSG_PUB.ADD;
112 RETURN FALSE;
113 END insert_row;
114
115
116 /* Api start of comments
117 +============================================================================
118 | FUNCTION NAME
119 | fetch_row
120 |
121 | TYPE
122 | Private
123 | USAGE
124 | Fetch_Row will fetch a row in fm_rout_dep
125 |
126 |
127 | DESCRIPTION
128 | Fetch_Row will fetch a row in fm_rout_dep
129 |
130 |
131 |
132 | PARAMETERS
133 | p_out_dep IN fm_rout_dep%ROWTYPE
134 | x_out_dep OUT fm_rout_dep%ROWTYPE
135 |
136 | RETURNS
137 | BOOLEAN
138 | HISTORY
139 | 20-MAR-01 Thomas Daniel Created
140 |
141 |
142 |
143 +=============================================================================
144 Api end of comments
145 */
146
147 FUNCTION fetch_row (
148 p_out_dep IN FM_ROUT_DEP%ROWTYPE
149 , x_out_dep OUT NOCOPY FM_ROUT_DEP%ROWTYPE) RETURN BOOLEAN IS
150 BEGIN
151 IF p_out_dep.routingstep_no IS NOT NULL
152 AND p_out_dep.dep_routingstep_no IS NOT NULL
153 AND p_out_dep.routing_id IS NOT NULL
154 THEN
155 SELECT
156 ROUTINGSTEP_NO
157 ,DEP_ROUTINGSTEP_NO
158 ,ROUTING_ID
159 ,DEP_TYPE
160 ,REWORK_CODE
161 ,STANDARD_DELAY
162 ,MINIMUM_DELAY
163 ,MAX_DELAY
164 ,TRANSFER_QTY
165 ,ITEM_UM
166 ,TEXT_CODE
167 ,LAST_UPDATED_BY
168 ,CREATED_BY
169 ,LAST_UPDATE_DATE
170 ,CREATION_DATE
171 ,LAST_UPDATE_LOGIN
172 ,TRANSFER_PCT
173 INTO
174 x_out_dep.ROUTINGSTEP_NO
175 ,x_out_dep.DEP_ROUTINGSTEP_NO
176 ,x_out_dep.ROUTING_ID
177 ,x_out_dep.DEP_TYPE
178 ,x_out_dep.REWORK_CODE
179 ,x_out_dep.STANDARD_DELAY
180 ,x_out_dep.MINIMUM_DELAY
181 ,x_out_dep.MAX_DELAY
182 ,x_out_dep.TRANSFER_QTY
183 ,x_out_dep.ITEM_UM
184 ,x_out_dep.TEXT_CODE
185 ,x_out_dep.LAST_UPDATED_BY
186 ,x_out_dep.CREATED_BY
187 ,x_out_dep.LAST_UPDATE_DATE
188 ,x_out_dep.CREATION_DATE
189 ,x_out_dep.LAST_UPDATE_LOGIN
190 ,x_out_dep.TRANSFER_PCT
191 FROM fm_rout_dep
192 WHERE routingstep_no = p_out_dep.routingstep_no
193 AND dep_routingstep_no = p_out_dep.dep_routingstep_no
194 AND routing_id = p_out_dep.routing_id
195 ;
196 ELSE
197 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_KEY_VALUES');
198 FND_MSG_PUB.ADD;
199 RETURN FALSE;
200 END IF;
201 RETURN TRUE;
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 FND_MESSAGE.SET_NAME('GMD', 'GMD_NO_DEPEND_GIVEN_KEYS');
205 FND_MSG_PUB.ADD;
206 RETURN FALSE;
207 WHEN OTHERS THEN
208 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
209 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
210 FND_MSG_PUB.ADD;
211 RETURN FALSE;
212 END fetch_row;
213
214
215 /* Api start of comments
216 +============================================================================
217 | FUNCTION NAME
218 | delete_row
219 |
220 | TYPE
221 | Private
222 | USAGE
223 | Delete_Row will delete a row in fm_rout_dep
224 |
225 |
226 | DESCRIPTION
227 | Delete_Row will delete a row in fm_rout_dep
228 |
229 |
230 |
231 | PARAMETERS
232 | p_out_dep IN fm_rout_dep%ROWTYPE
233 |
234 | RETURNS
235 | BOOLEAN
236 | HISTORY
237 | 20-MAR-01 Thomas Daniel Created
238 |
239 |
240 |
241 +=============================================================================
242 Api end of comments
243 */
244
245 FUNCTION delete_row (
246 p_out_dep IN FM_ROUT_DEP%ROWTYPE) RETURN BOOLEAN IS
247 BEGIN
248 IF p_out_dep.routingstep_no IS NOT NULL
249 AND p_out_dep.dep_routingstep_no IS NOT NULL
250 AND p_out_dep.routing_id IS NOT NULL
251 THEN
252 DELETE FROM fm_rout_dep
253 WHERE routingstep_no = p_out_dep.routingstep_no
254 AND dep_routingstep_no = p_out_dep.dep_routingstep_no
255 AND routing_id = p_out_dep.routing_id
256 ;
257 ELSE
258 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_KEY_VALUES');
259 FND_MSG_PUB.ADD;
260 RETURN FALSE;
261 END IF;
262 IF SQL%FOUND THEN
263 RETURN TRUE;
264 ELSE
265 FND_MESSAGE.SET_NAME('GMD', 'GMD_NO_DEPEND_GIVEN_KEYS');
266 FND_MSG_PUB.ADD;
267 RETURN FALSE;
268 END IF;
269 EXCEPTION
270 WHEN OTHERS THEN
271 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
272 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
273 FND_MSG_PUB.ADD;
274 RETURN FALSE;
275 END delete_row;
276
277
278 /* Api start of comments
279 +============================================================================
280 | FUNCTION NAME
281 | update_row
282 |
283 | TYPE
284 | Private
285 | USAGE
286 | Update_Row will update a row in fm_rout_dep
287 |
288 |
289 | DESCRIPTION
290 | Update_Row will update a row in fm_rout_dep
291 |
292 |
293 |
294 | PARAMETERS
295 | p_out_dep IN fm_rout_dep%ROWTYPE
296 |
297 | RETURNS
298 | BOOLEAN
299 | HISTORY
300 | 20-MAR-01 Thomas Daniel Created
301 |
302 |
303 |
304 +=============================================================================
305 Api end of comments
306 */
307
308 FUNCTION update_row (
309 p_out_dep IN FM_ROUT_DEP%ROWTYPE) RETURN BOOLEAN IS
310 l_dummy NUMBER;
311 locked_by_other_user EXCEPTION;
312 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
313 BEGIN
314 IF p_out_dep.routingstep_no IS NOT NULL
315 AND p_out_dep.dep_routingstep_no IS NOT NULL
316 AND p_out_dep.routing_id IS NOT NULL
317 THEN
318 SELECT 1 INTO l_dummy FROM fm_rout_dep
319 WHERE routingstep_no = p_out_dep.routingstep_no
320 AND dep_routingstep_no = p_out_dep.dep_routingstep_no
321 AND routing_id = p_out_dep.routing_id
322 FOR UPDATE NOWAIT;
323
324 UPDATE fm_rout_dep
325 SET
326 ROUTINGSTEP_NO = p_out_dep.ROUTINGSTEP_NO
327 ,DEP_ROUTINGSTEP_NO = p_out_dep.DEP_ROUTINGSTEP_NO
328 ,ROUTING_ID = p_out_dep.ROUTING_ID
329 ,DEP_TYPE = p_out_dep.DEP_TYPE
330 ,REWORK_CODE = p_out_dep.REWORK_CODE
331 ,STANDARD_DELAY = p_out_dep.STANDARD_DELAY
332 ,MINIMUM_DELAY = p_out_dep.MINIMUM_DELAY
333 ,MAX_DELAY = p_out_dep.MAX_DELAY
334 ,TRANSFER_QTY = p_out_dep.TRANSFER_QTY
335 ,ITEM_UM = p_out_dep.ITEM_UM
336 ,TEXT_CODE = p_out_dep.TEXT_CODE
337 ,LAST_UPDATED_BY = p_out_dep.LAST_UPDATED_BY
338 ,CREATED_BY = p_out_dep.CREATED_BY
339 ,LAST_UPDATE_DATE = p_out_dep.LAST_UPDATE_DATE
340 ,CREATION_DATE = p_out_dep.CREATION_DATE
341 ,LAST_UPDATE_LOGIN = p_out_dep.LAST_UPDATE_LOGIN
342 ,TRANSFER_PCT = p_out_dep.TRANSFER_PCT
343 WHERE routingstep_no = p_out_dep.routingstep_no
344 AND dep_routingstep_no = p_out_dep.dep_routingstep_no
345 AND routing_id = p_out_dep.routing_id
346 AND last_update_date = p_out_dep.last_update_date;
347 ELSE
348 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_KEY_VALUES');
349 FND_MSG_PUB.ADD;
350 RETURN FALSE;
351 END IF;
352 IF SQL%FOUND THEN
353 RETURN TRUE;
354 ELSE
355 FND_MESSAGE.SET_NAME('GMD', 'GMD_NO_DEPEND_GIVEN_KEYS');
356 FND_MSG_PUB.ADD;
357 RETURN FALSE;
358 END IF;
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 FND_MESSAGE.SET_NAME('GMD', 'GMD_DEPEND_LOCK_FAILURE');
362 FND_MSG_PUB.ADD;
363 RETURN FALSE;
364 WHEN OTHERS THEN
365 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
366 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
367 FND_MSG_PUB.ADD;
368 RETURN FALSE;
369 END update_row;
370
371
372 /* Api start of comments
373 +============================================================================
374 | FUNCTION NAME
375 | lock_row
376 |
377 | TYPE
378 | Private
379 | USAGE
380 | Lock_Row will lock a row in fm_rout_dep
381 |
382 |
383 | DESCRIPTION
384 | Lock_Row will lock a row in fm_rout_dep
385 |
386 |
387 |
388 | PARAMETERS
389 | p_out_dep IN fm_rout_dep%ROWTYPE
390 |
391 | RETURNS
392 | BOOLEAN
393 | HISTORY
394 | 20-MAR-01 Thomas Daniel Created
395 |
396 |
397 |
398 +=============================================================================
399 Api end of comments
400 */
401
402 FUNCTION lock_row (
403 p_out_dep IN FM_ROUT_DEP%ROWTYPE) RETURN BOOLEAN IS
404 l_dummy NUMBER;
405 BEGIN
406 IF p_out_dep.routingstep_no IS NOT NULL
407 AND p_out_dep.dep_routingstep_no IS NOT NULL
408 AND p_out_dep.routing_id IS NOT NULL
409 THEN
410 SELECT 1 INTO l_dummy FROM fm_rout_dep
411 WHERE routingstep_no = p_out_dep.routingstep_no
412 AND dep_routingstep_no = p_out_dep.dep_routingstep_no
413 AND routing_id = p_out_dep.routing_id
414 FOR UPDATE NOWAIT;
415 END IF;
416 RETURN TRUE;
417 EXCEPTION
418 WHEN OTHERS THEN
419 RETURN FALSE;
420 END lock_row;
421
422
423 END FM_ROUT_DEP_DBL;