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