[Home] [Help]
PACKAGE BODY: APPS.GMD_ROUTING_STEPS_PVT
Source
1 PACKAGE BODY GMD_ROUTING_STEPS_PVT AS
2 /* $Header: GMDVRTSB.pls 120.1 2006/06/12 06:38:45 rkrishan noship $ */
3
4
5 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
6 --Forward declaration.
7 FUNCTION set_debug_flag RETURN VARCHAR2;
8 l_debug VARCHAR2(1) := set_debug_flag;
9
10 FUNCTION set_debug_flag RETURN VARCHAR2 IS
11 l_debug VARCHAR2(1):= 'N';
12 BEGIN
13 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
14 l_debug := 'Y';
15 END IF;
16 RETURN l_debug;
17 END set_debug_flag;
18 --Bug 3222090, NSRIVAST 20-FEB-2004, END
19
20 /* =============================================================== */
21 /* Procedure: */
22 /* insert_routing_steps */
23 /* */
24 /* DESCRIPTION: */
25 /* */
26 /* API returns (x_return_code) = 'S' if the insert into routing */
27 /* details (fm_rout_dtl) table is successfully. */
28 /* */
29 /* History : */
30 /* Shyam 07/29/2002 Initial implementation */
31 /* Raju 31-OCT-02 Changed the code to add default values. */
32 /* Raju 18-NOV-02 Tested in opm115qa db and fixed the issues */
33 /* =============================================================== */
34 PROCEDURE insert_routing_steps
35 ( p_routing_id IN gmd_routings.routing_id%TYPE
36 , p_routing_step_rec IN fm_rout_dtl%ROWTYPE
37 , x_return_status OUT NOCOPY VARCHAR2
38 ) IS
39
40 /* Local variable section */
41 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROUTING_STEPS';
42 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 l_routingStep_id fm_rout_dtl.routingStep_id%TYPE;
44 l_steprelease_type fm_rout_dtl.steprelease_type%TYPE;
45
46 /*define cursor */
47 /* get routing step id sequence */
48 CURSOR Get_routingstep_id_seq IS
49 SELECT gem5_routingstep_id_s.NEXTVAL
50 FROM sys.dual;
51
52 /* Exception section */
53 routing_step_creation_failure EXCEPTION;
54 invalid_version EXCEPTION;
55 setup_failure EXCEPTION;
56
57 l_dummy number;
58
59 BEGIN
60
61 /* Intialize the setup fields */
62 IF NOT gmd_api_grp.setup_done THEN
63 gmd_api_grp.setup_done := gmd_api_grp.setup;
64 END IF;
65 IF NOT gmd_api_grp.setup_done THEN
66 RAISE setup_failure;
67 END IF;
68 /* Set the return status to success initially */
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 /* Get the routingStep_id from sequence generator */
72 IF (l_debug = 'Y') THEN
73 gmd_debug.put_line('Get the routingstep id value : ');
74 END IF;
75
76 IF p_routing_step_rec.routingstep_id IS NULL THEN
77 OPEN Get_routingstep_id_seq;
78 FETCH Get_routingstep_id_seq INTO l_routingStep_id;
79 IF Get_routingStep_id_seq%NOTFOUND then
80 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ROUT_SEQ');
81 FND_MSG_PUB.ADD;
82 CLOSE Get_routingstep_id_seq;
83 RAISE routing_step_creation_failure;
84 END IF;
85 CLOSE Get_routingstep_id_seq;
86 END IF;
87
88 /* Step 1 : Create Routing steps */
89 IF (l_debug = 'Y') THEN
90 gmd_debug.put_line('Insert the routing steps for routing with routingstep id = '||l_routingstep_id);
91 END IF;
92
93 INSERT INTO fm_rout_dtl
94 ( routing_id, routingstep_no, routingstep_id , oprn_id ,step_qty, steprelease_type, text_code
95 ,last_updated_by, created_by, last_update_date, creation_date, last_update_login
96 ,attribute1, attribute2, attribute3, attribute4, attribute5 , attribute6 , attribute7
97 ,attribute8 , attribute9 , attribute10 , attribute11 , attribute12, attribute13
98 ,attribute14 , attribute15, attribute16, attribute17, attribute18, attribute19
99 ,attribute20 , attribute21, attribute22, attribute23 , attribute24,attribute25
100 ,attribute26, attribute27 , attribute28, attribute29 , attribute30, attribute_category
101 ,x_coordinate, y_coordinate,minimum_transfer_qty)
102 VALUES
103 ( p_routing_id , p_routing_step_rec.routingstep_no, NVL(p_routing_step_rec.routingstep_id,l_routingStep_id)
104 ,p_routing_step_rec.oprn_id, p_routing_step_rec.step_qty, p_routing_step_rec.steprelease_type
105 ,p_routing_step_rec.text_code, gmd_api_grp.user_id
106 ,gmd_api_grp.user_id, NVL(p_routing_step_rec.last_update_date,SYSDATE)
107 ,NVL(p_routing_step_rec.creation_date,SYSDATE), p_routing_step_rec.last_update_login
108 ,p_routing_step_rec.attribute1, p_routing_step_rec.attribute2
109 ,p_routing_step_rec.attribute3, p_routing_step_rec.attribute4
110 ,p_routing_step_rec.attribute5, p_routing_step_rec.attribute6
111 ,p_routing_step_rec.attribute7, p_routing_step_rec.attribute8
112 ,p_routing_step_rec.attribute9, p_routing_step_rec.attribute10
113 ,p_routing_step_rec.attribute11, p_routing_step_rec.attribute12
114 ,p_routing_step_rec.attribute13, p_routing_step_rec.attribute14
115 ,p_routing_step_rec.attribute15, p_routing_step_rec.attribute16
116 ,p_routing_step_rec.attribute17, p_routing_step_rec.attribute18
117 ,p_routing_step_rec.attribute19, p_routing_step_rec.attribute20
118 ,p_routing_step_rec.attribute21, p_routing_step_rec.attribute22
119 ,p_routing_step_rec.attribute23, p_routing_step_rec.attribute24
120 ,p_routing_step_rec.attribute25, p_routing_step_rec.attribute26
121 ,p_routing_step_rec.attribute27, p_routing_step_rec.attribute28
122 ,p_routing_step_rec.attribute29, p_routing_step_rec.attribute30
123 ,p_routing_step_rec.attribute_category, p_routing_step_rec.x_coordinate
124 ,p_routing_step_rec.y_coordinate,p_routing_step_rec.minimum_transfer_qty);
125
126 IF (l_debug = 'Y') THEN
127 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
128 END IF;
129 EXCEPTION
130 WHEN routing_step_creation_failure OR invalid_version THEN
131 IF (l_debug = 'Y') THEN
132 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete '||SQLERRM);
133 END IF;
134 x_return_status := FND_API.G_RET_STS_ERROR;
135 WHEN setup_failure THEN
136 x_return_status := FND_API.G_RET_STS_ERROR;
137 WHEN OTHERS THEN
138 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
139 IF (l_debug = 'Y') THEN
140 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
141 END IF;
142 x_return_status := FND_API.g_ret_sts_unexp_error;
143 END insert_routing_steps;
144
145 /* =============================================================== */
146 /* Procedure: */
147 /* insert_step_dependencies */
148 /* */
149 /* DESCRIPTION: */
150 /* */
151 /* API returns (x_return_code) = 'S' if the insert into step */
152 /* dependency table is successfully. */
153 /* */
154 /* History : */
155 /* Shyam 07/29/2002 Initial implemenation */
156 /* S.Dulyk 8/24/03 remove NVL and ,0 from max_delay line in */
157 /* insert to fm_rout_Dep */
158 /* =============================================================== */
159 PROCEDURE insert_step_dependencies
160 ( p_routing_id IN gmd_routings.routing_id%TYPE
161 , p_routingstep_no IN fm_rout_dtl.routingstep_no%TYPE
162 , p_routings_step_dep_tbl IN GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab
163 , x_return_status OUT NOCOPY VARCHAR2
164 ) IS
165
166 /* Cursor section */
167 CURSOR get_step_qty (vRouting_id NUMBER,vroutingstep_no NUMBER) IS
168 Select step_qty
169 From fm_rout_dtl
170 Where routing_id = vRouting_id
171 AND routingstep_no = vroutingstep_no;
172
173 /* Local variable section */
174 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_STEP_DEPENDENCIES';
175 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
176 l_step_qty NUMBER;
177 l_transfer_qty NUMBER;
178
179 /* Exception section */
180 routing_step_dep_failure EXCEPTION;
181 routing_cir_ref_failure EXCEPTION;
182 invalid_version EXCEPTION;
183 setup_failure EXCEPTION;
184
185
186 BEGIN
187 IF (l_debug = 'Y') THEN
188 gmd_debug.log_initialize('Crsdpvt');
189 END IF;
190
191 /* Intialize the setup fields */
192 IF NOT gmd_api_grp.setup_done THEN
193 gmd_api_grp.setup_done := gmd_api_grp.setup;
194 END IF;
195 IF NOT gmd_api_grp.setup_done THEN
196 RAISE setup_failure;
197 END IF;
198
199 /* Set the return status to success initially */
200 x_return_status := FND_API.G_RET_STS_SUCCESS;
201
202
203 /* Insert made into the step dependency table */
204 FOR i IN 1 .. p_routings_step_dep_tbl.count LOOP
205 /* get the step qty for tranfer qty calculation */
206 OPEN get_step_qty(p_routing_id,p_routingstep_no);
207 FETCH get_step_qty INTO l_step_qty;
208 CLOSE get_step_qty;
209 l_transfer_qty := l_step_qty * p_routings_step_dep_tbl(i).transfer_pct * .01;
210
211
212 /* S.Dulyk - 12/27/02 Bug 2669986 Added validation for max_delay */
213 IF (p_routings_step_dep_tbl(i).max_delay < p_routings_step_dep_tbl(i).standard_delay AND
214 p_routings_step_dep_tbl(i).max_delay IS NOT NULL) THEN
215 FND_MESSAGE.SET_NAME('GMD','GMD_MAX_DELAY_VALIDATION');
216 FND_MSG_PUB.ADD;
217 x_return_status := FND_API.g_ret_sts_error;
218 END IF;
219
220
221 INSERT INTO fm_rout_dep
222 (routingstep_no ,dep_routingstep_no ,routing_id ,dep_type ,rework_code
223 ,standard_delay ,minimum_delay ,max_delay ,transfer_qty ,routingstep_no_uom
224 ,text_code ,last_updated_by ,created_by ,last_update_date ,creation_date
225 ,last_update_login ,transfer_pct ) VALUES
226 (p_routingstep_no
227 ,p_routings_step_dep_tbl(i).dep_routingstep_no
228 ,p_routing_id
229 ,NVL(p_routings_step_dep_tbl(i).dep_type,0)
230 ,p_routings_step_dep_tbl(i).rework_code
231 ,NVL(p_routings_step_dep_tbl(i).standard_delay,0)
232 ,NVL(p_routings_step_dep_tbl(i).minimum_delay,0)
233 ,p_routings_step_dep_tbl(i).max_delay
234 ,NVL(l_transfer_qty,0)
235 ,p_routings_step_dep_tbl(i).routingstep_no_uom
236 ,p_routings_step_dep_tbl(i).text_code
237 ,gmd_api_grp.user_id
238 ,gmd_api_grp.user_id
239 ,NVL(p_routings_step_dep_tbl(i).last_update_date,SYSDATE)
240 ,NVL(p_routings_step_dep_tbl(i).creation_date,SYSDATE)
241 ,p_routings_step_dep_tbl(i).last_update_login
242 ,NVL(p_routings_step_dep_tbl(i).transfer_pct,100)
243 );
244
245 -- Check if routing step dependencies were created
246 IF (l_debug = 'Y') THEN
247 gmd_debug.put_line('After inserting routing step dependencies');
248 END IF;
249
250 IF SQL%ROWCOUNT = 0 THEN
251 RAISE routing_step_dep_failure;
252 END IF;
253 END LOOP; /* End loop for p_routings_step_dep_tbl.count */
254 /* Validation after step dependenices creation */
255 /* Validation : Check for circular step dependencies */
256 IF GMDRTVAL_PUB.circular_dependencies_exist (p_routing_id) THEN
257 RAISE routing_cir_ref_failure;
258 END IF;
259
260 /* Check if work was done */
261 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
262 RAISE routing_step_dep_failure;
263 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
264 IF (l_debug = 'Y') THEN
265 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
266 END IF;
267
268 EXCEPTION
269 WHEN routing_step_dep_failure OR invalid_version THEN
270 IF (l_debug = 'Y') THEN
271 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
272 END IF;
273 x_return_status := FND_API.G_RET_STS_ERROR;
274
275 WHEN routing_cir_ref_failure THEN
276 IF (l_debug = 'Y') THEN
277 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete due circular reference');
278 END IF;
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 WHEN setup_failure THEN
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 WHEN OTHERS THEN
283 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
284 IF (l_debug = 'Y') THEN
285 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
286 END IF;
287 x_return_status := FND_API.g_ret_sts_unexp_error;
288 END insert_step_dependencies;
289
290 /* =============================================================== */
291 /* Procedure: */
292 /* update_routing_steps */
293 /* */
294 /* DESCRIPTION: */
295 /* */
296 /* API returns (x_return_code) = 'S' if the update into routing */
297 /* details (fm_rout_dtl table) is success. */
298 /* */
299 /* History : */
300 /* Shyam 07/29/2002 Initial implementation */
301 /* =============================================================== */
302 PROCEDURE update_routing_steps
303 ( p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE
304 , p_update_table IN GMD_ROUTINGS_PUB.update_tbl_type
305 , x_return_status OUT NOCOPY VARCHAR2
306 ) IS
307
308 /* Local variable section */
309 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROUTING_STEPS';
310 l_routingstep_id fm_rout_dtl.routingStep_id%TYPE;
311 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
312 l_db_date DATE;
313
314 /* Define record type that hold the routing data */
315 l_old_routingStep_rec fm_rout_dtl%ROWTYPE;
316
317 /* Define Exceptions */
318 last_update_date_failure EXCEPTION;
319 routing_update_step_failure EXCEPTION;
320 invalid_version EXCEPTION;
321 setup_failure EXCEPTION;
322
323 CURSOR get_old_routingStep_rec(vRoutingStep_id fm_rout_dtl.routingStep_id%TYPE) IS
324 Select *
325 From fm_rout_dtl
326 Where RoutingStep_id = vRoutingStep_id;
327
328 BEGIN
329 /* Set the return status to success initially */
330 x_return_status := FND_API.G_RET_STS_SUCCESS;
331
332 /* Get the old routing rec value */
333 IF (l_debug = 'Y') THEN
334 gmd_debug.put_line('Fetch : Populate the old routing step record ');
335 END IF;
336
337 /* Intialize the setup fields */
338 IF NOT gmd_api_grp.setup_done THEN
339 gmd_api_grp.setup_done := gmd_api_grp.setup;
340 END IF;
341 IF NOT gmd_api_grp.setup_done THEN
342 RAISE setup_failure;
343 END IF;
344
345 OPEN get_old_routingStep_rec(p_routingStep_id);
346 FETCH get_old_routingStep_rec INTO l_old_routingStep_rec;
347 IF get_old_routingStep_rec%NOTFOUND THEN
348 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
349 FND_MSG_PUB.ADD;
350 CLOSE get_old_routingStep_rec;
351 RAISE routing_update_step_failure;
352 END IF;
353 CLOSE get_old_routingStep_rec;
354
355 /* Get the last update date from database */
356 l_db_date := l_old_routingStep_rec.LAST_UPDATE_DATE;
357
358 /* Actual update in fm_rout_dtl table */
359 /* Loop thro' every column in p_update_table table and for each column name
360 assign or replace the old value with the table value */
361 IF (l_debug = 'Y') THEN
362 gmd_debug.put_line('Seting the update column value ');
363 END IF;
364 FOR i IN 1 .. p_update_table.count LOOP
365 IF (UPPER(p_update_table(i).p_col_to_update) = 'STEP_QTY') THEN
366 l_old_routingStep_rec.STEP_QTY := p_update_table(i).p_value;
367 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'STEPRELEASE_TYPE') THEN
368 l_old_routingStep_rec.STEPRELEASE_TYPE := TO_NUMBER(p_update_table(i).p_value);
369 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'TEXT_CODE') THEN
370 l_old_routingStep_rec.TEXT_CODE := p_update_table(i).p_value;
371 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATED_BY') THEN
372 l_old_routingStep_rec.LAST_UPDATED_BY := gmd_api_grp.user_id;
373 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_DATE') THEN
374 l_old_routingstep_rec.LAST_UPDATE_DATE :=
375 FND_DATE.CANONICAL_TO_DATE(p_update_table(i).p_value);
376 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_LOGIN') THEN
377 l_old_routingStep_rec.LAST_UPDATE_LOGIN := gmd_api_grp.user_id;
378 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE1') THEN
379 l_old_routingStep_rec.ATTRIBUTE1 := p_update_table(i).p_value;
380 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE2') THEN
381 l_old_routingStep_rec.ATTRIBUTE2 := p_update_table(i).p_value;
382 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE3') THEN
383 l_old_routingStep_rec.ATTRIBUTE3 := p_update_table(i).p_value;
384 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE4') THEN
385 l_old_routingStep_rec.ATTRIBUTE4 := p_update_table(i).p_value;
386 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE5') THEN
387 l_old_routingStep_rec.ATTRIBUTE5 := p_update_table(i).p_value;
388 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE6') THEN
389 l_old_routingStep_rec.ATTRIBUTE6 := p_update_table(i).p_value;
390 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE7') THEN
391 l_old_routingStep_rec.ATTRIBUTE7 := p_update_table(i).p_value;
392 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE8') THEN
393 l_old_routingStep_rec.ATTRIBUTE8 := p_update_table(i).p_value;
394 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE9') THEN
395 l_old_routingStep_rec.ATTRIBUTE9 := p_update_table(i).p_value;
396 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE10') THEN
397 l_old_routingStep_rec.ATTRIBUTE10 := p_update_table(i).p_value;
398 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE11') THEN
399 l_old_routingStep_rec.ATTRIBUTE11 := p_update_table(i).p_value;
400 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE12') THEN
401 l_old_routingStep_rec.ATTRIBUTE12 := p_update_table(i).p_value;
402 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE13') THEN
403 l_old_routingStep_rec.ATTRIBUTE13 := p_update_table(i).p_value;
404 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE14') THEN
405 l_old_routingStep_rec.ATTRIBUTE14 := p_update_table(i).p_value;
406 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE15') THEN
407 l_old_routingStep_rec.ATTRIBUTE15 := p_update_table(i).p_value;
408 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE16') THEN
409 l_old_routingStep_rec.ATTRIBUTE16 := p_update_table(i).p_value;
410 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE17') THEN
411 l_old_routingStep_rec.ATTRIBUTE17 := p_update_table(i).p_value;
412 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE18') THEN
413 l_old_routingStep_rec.ATTRIBUTE18 := p_update_table(i).p_value;
414 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE19') THEN
415 l_old_routingStep_rec.ATTRIBUTE19 := p_update_table(i).p_value;
416 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE20') THEN
417 l_old_routingStep_rec.ATTRIBUTE20 := p_update_table(i).p_value;
418 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE21') THEN
419 l_old_routingStep_rec.ATTRIBUTE21 := p_update_table(i).p_value;
420 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE22') THEN
421 l_old_routingStep_rec.ATTRIBUTE22 := p_update_table(i).p_value;
422 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE23') THEN
423 l_old_routingStep_rec.ATTRIBUTE23 := p_update_table(i).p_value;
424 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE24') THEN
425 l_old_routingStep_rec.ATTRIBUTE24 := p_update_table(i).p_value;
426 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE25') THEN
427 l_old_routingStep_rec.ATTRIBUTE25 := p_update_table(i).p_value;
428 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE26') THEN
429 l_old_routingStep_rec.ATTRIBUTE26 := p_update_table(i).p_value;
430 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE27') THEN
431 l_old_routingStep_rec.ATTRIBUTE27 := p_update_table(i).p_value;
432 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE28') THEN
433 l_old_routingStep_rec.ATTRIBUTE28 := p_update_table(i).p_value;
434 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE29') THEN
435 l_old_routingStep_rec.ATTRIBUTE29 := p_update_table(i).p_value;
436 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE30') THEN
437 l_old_routingStep_rec.ATTRIBUTE30 := p_update_table(i).p_value;
438 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE_CATEGORY') THEN
439 l_old_routingStep_rec.ATTRIBUTE_CATEGORY := p_update_table(i).p_value;
440 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'X_COORDINATE') THEN
441 l_old_routingStep_rec.X_COORDINATE := p_update_table(i).p_value;
442 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'Y_COORDINATE') THEN
443 l_old_routingStep_rec.Y_COORDINATE := p_update_table(i).p_value;
444 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'MINIMUM_TRANSFER_QTY') THEN
445 l_old_routingStep_rec.MINIMUM_TRANSFER_QTY := p_update_table(i).p_value;
446 -- Added for MSNR replace
447 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'OPRN_ID') THEN
448 l_old_routingStep_rec.OPRN_ID := p_update_table(i).p_value;
449 END IF;
450
451 /* Compare Dates - if the last update date passed in via the API is less than
452 the last update in the db - it indicates someelse has updated this row after this
453 row was selected */
454 IF l_old_routingStep_rec.last_update_date < l_db_date THEN
455 RAISE last_update_date_failure;
456 END IF;
457
458 IF (l_debug = 'Y') THEN
459 gmd_debug.put_line('Before routing step table update ');
460 END IF;
461 UPDATE fm_rout_dtl
462 SET oprn_id = l_old_routingStep_rec.oprn_id
463 ,step_qty = l_old_routingStep_rec.step_qty
464 ,steprelease_type = l_old_routingStep_rec.steprelease_type
465 ,text_code = l_old_routingStep_rec.text_code
466 ,last_updated_by = l_old_routingStep_rec.last_updated_by
467 ,last_update_date = NVL(l_old_routingStep_rec.last_update_date,SYSDATE)
468 ,last_update_login = l_old_routingStep_rec.last_update_login
469 ,attribute1 = l_old_routingStep_rec.attribute1
470 ,attribute2 = l_old_routingStep_rec.attribute2
471 ,attribute3 = l_old_routingStep_rec.attribute3
472 ,attribute4 = l_old_routingStep_rec.attribute4
473 ,attribute5 = l_old_routingStep_rec.attribute5
474 ,attribute6 = l_old_routingStep_rec.attribute6
475 ,attribute7 = l_old_routingStep_rec.attribute7
476 ,attribute8 = l_old_routingStep_rec.attribute8
477 ,attribute9 = l_old_routingStep_rec.attribute9
478 ,attribute10 = l_old_routingStep_rec.attribute10
479 ,attribute11 = l_old_routingStep_rec.attribute11
480 ,attribute12 = l_old_routingStep_rec.attribute12
481 ,attribute13 = l_old_routingStep_rec.attribute13
482 ,attribute14 = l_old_routingStep_rec.attribute14
483 ,attribute15 = l_old_routingStep_rec.attribute15
484 ,attribute16 = l_old_routingStep_rec.attribute16
485 ,attribute17 = l_old_routingStep_rec.attribute17
486 ,attribute18 = l_old_routingStep_rec.attribute18
487 ,attribute19 = l_old_routingStep_rec.attribute19
488 ,attribute20 = l_old_routingStep_rec.attribute20
489 ,attribute21 = l_old_routingStep_rec.attribute21
490 ,attribute22 = l_old_routingStep_rec.attribute22
491 ,attribute23 = l_old_routingStep_rec.attribute23
492 ,attribute24 = l_old_routingStep_rec.attribute24
493 ,attribute25 = l_old_routingStep_rec.attribute25
494 ,attribute26 = l_old_routingStep_rec.attribute26
495 ,attribute27 = l_old_routingStep_rec.attribute27
496 ,attribute28 = l_old_routingStep_rec.attribute28
497 ,attribute29 = l_old_routingStep_rec.attribute29
498 ,attribute30 = l_old_routingStep_rec.attribute30
499 ,attribute_category = l_old_routingStep_rec.attribute_category
500 ,minimum_transfer_qty = l_old_routingStep_rec.minimum_transfer_qty
501 ,x_coordinate = l_old_routingStep_rec.x_coordinate
502 ,y_coordinate = l_old_routingStep_rec.y_coordinate
503 WHERE routingStep_id = p_routingstep_id;
504
505 IF (l_debug = 'Y') THEN
506 gmd_debug.put_line('After routing step table update ');
507 END IF;
508 IF SQL%ROWCOUNT = 0 THEN
509 RAISE routing_update_step_failure;
510 END IF;
511 END LOOP;
512
513 /* Check if work was done */
514 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
515 RAISE routing_update_step_failure;
516 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
517 IF (l_debug = 'Y') THEN
518 gmd_debug.put_line('Completed '||m_pkg_name||'.'||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
519 END IF;
520
521 EXCEPTION
522 WHEN routing_update_step_failure OR invalid_version THEN
523 IF (l_debug = 'Y') THEN
524 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
525 END IF;
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 WHEN last_update_date_failure THEN
528 x_return_status := FND_API.G_RET_STS_ERROR;
529 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
530 FND_MSG_PUB.ADD;
531 WHEN setup_failure THEN
532 x_return_status := FND_API.G_RET_STS_ERROR;
533 WHEN OTHERS THEN
534 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
535 IF (l_debug = 'Y') THEN
536 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
537 END IF;
538 x_return_status := FND_API.g_ret_sts_unexp_error;
539 END update_routing_steps;
540
541 /* =============================================================== */
542 /* Procedure: */
543 /* update_step_dependencies */
544 /* */
545 /* DESCRIPTION: */
546 /* */
547 /* API returns (x_return_code) = 'S' if the update into routing */
548 /* step dependency (fm_rout_dep table) is success. */
549 /* */
550 /* History : */
551 /* Shyam 07/29/2002 Initial implementation */
552 /* =============================================================== */
553 PROCEDURE update_step_dependencies
554 ( p_routingstep_no IN fm_rout_dep.routingstep_no%TYPE
555 , p_dep_routingstep_no IN fm_rout_dep.routingstep_no%TYPE
556 , p_routing_id IN fm_rout_dep.routing_id%TYPE
557 , p_update_table IN GMD_ROUTINGS_PUB.update_tbl_type
558 , x_return_status OUT NOCOPY VARCHAR2
559 ) IS
560
561 /* Local variable section */
562 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_STEP_DEPENDENCIES';
563 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
564 l_db_date DATE;
565
566 /* Define record type that hold the routing data */
567 l_old_stepDep_rec fm_rout_dep%ROWTYPE;
568
569 /* Define Exceptions */
570 last_update_date_failure EXCEPTION;
571 routing_update_dep_failure EXCEPTION;
572 invalid_version EXCEPTION;
573 setup_failure EXCEPTION;
574
575 CURSOR get_old_stepDep_rec( vRoutingStep_no fm_rout_dep.routingStep_no%TYPE
576 ,vdep_RoutingStep_no fm_rout_dep.dep_routingStep_no%TYPE
577 ,vRouting_id fm_rout_dep.routing_id%TYPE) IS
578 Select *
579 From fm_rout_dep
580 Where RoutingStep_no = vRoutingStep_no
581 And dep_RoutingStep_no = vdep_RoutingStep_no
582 And Routing_id = vRouting_id;
583
584 BEGIN
585 IF (l_debug = 'Y') THEN
586 gmd_debug.log_initialize('Updsdpvt');
587 END IF;
588
589 /* Intialize the setup fields */
590 IF NOT gmd_api_grp.setup_done THEN
591 gmd_api_grp.setup_done := gmd_api_grp.setup;
592 END IF;
593 IF NOT gmd_api_grp.setup_done THEN
594 RAISE setup_failure;
595 END IF;
596
597 /* Set the return status to success initially */
598 x_return_status := FND_API.G_RET_STS_SUCCESS;
599
600 /* The old routing step dependency record */
601 OPEN get_old_stepDep_rec(p_routingstep_no,p_dep_routingstep_no,p_routing_id);
602 FETCH get_old_stepDep_rec INTO l_old_stepDep_rec;
603 IF get_old_stepDep_rec%NOTFOUND THEN
604 FND_MESSAGE.SET_NAME('GMD', 'GMD_STEPDEP_INVALID');
605 FND_MSG_PUB.ADD;
606 CLOSE get_old_stepDep_rec;
607 RAISE routing_update_dep_failure;
608 END IF;
609 CLOSE get_old_stepDep_rec;
610
611 /* Get the last update date in database */
612 l_db_date := l_old_stepDep_rec.LAST_UPDATE_DATE;
613
614 /* Actual update in fm_rout_dep table */
615 /* Loop thro' every column in p_update_table table and for each column name
616 assign or replace the old value with the table value */
617 FOR i IN 1 .. p_update_table.count LOOP
618 IF (UPPER(p_update_table(i).p_col_to_update) = 'DEP_TYPE') THEN
619 l_old_stepDep_rec.DEP_TYPE := p_update_table(i).p_value;
620 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'REWORK_CODE') THEN
621 l_old_stepDep_rec.REWORK_CODE := p_update_table(i).p_value;
622 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'STANDARD_DELAY') THEN
623 l_old_stepDep_rec.STANDARD_DELAY := p_update_table(i).p_value;
624 /* S.Dulyk - 12/27/02 Bug 2669986 Added max_delay */
625 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'MAX_DELAY') THEN
626 l_old_stepDep_rec.MAX_DELAY := p_update_table(i).p_value;
627 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'TRANSFER_PCT') THEN
628 l_old_stepDep_rec.TRANSFER_PCT := p_update_table(i).p_value;
629 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'TEXT_CODE') THEN
630 l_old_stepDep_rec.TEXT_CODE := p_update_table(i).p_value;
631 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATED_BY') THEN
632 l_old_stepDep_rec.LAST_UPDATED_BY := gmd_api_grp.user_id;
633 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_DATE') THEN
634 l_old_stepdep_rec.LAST_UPDATE_DATE := FND_DATE.CANONICAL_TO_DATE(p_update_table(i).p_value);
635 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_LOGIN') THEN
636 l_old_stepDep_rec.LAST_UPDATE_LOGIN := gmd_api_grp.user_id;
637 END IF;
638
639 /* Compare Dates - if the last update date passed in via the API is less than
640 the last update in the db - it indicates someelse has updated this row after this
641 row was selected */
642 IF l_old_stepDep_rec.last_update_date < l_db_date THEN
643 RAISE last_update_date_failure;
644 END IF;
645
646 /* S.Dulyk - 12/27/02 Bug 2669986 Added validation for max_delay */
647 IF (l_old_stepDep_rec.max_delay < l_old_stepDep_rec.standard_delay AND
648 l_old_stepDep_rec.max_delay IS NOT NULL) THEN
649 FND_MESSAGE.SET_NAME('GMD','GMD_MAX_DELAY_VALIDATION');
650 FND_MSG_PUB.ADD;
651 x_return_status := FND_API.g_ret_sts_error;
652 END IF;
653
654 /* S.Dulyk - 12/27/02 Bug 2669986 Added max_delay */
655 UPDATE fm_rout_dep
656 SET dep_type = l_old_stepDep_rec.dep_type
657 ,rework_code = l_old_stepDep_rec.rework_code
658 ,standard_delay = l_old_stepDep_rec.standard_delay
659 ,max_delay = l_old_stepDep_rec.max_delay
660 ,text_code = l_old_stepDep_rec.text_code
661 ,last_updated_by = l_old_stepDep_rec.last_updated_by
662 ,last_update_date = NVL(l_old_stepDep_rec.last_update_date,SYSDATE)
663 ,last_update_login = l_old_stepDep_rec.last_update_login
664 ,transfer_pct = l_old_stepDep_rec.transfer_pct
665 WHERE routingstep_no = p_routingstep_no
666 AND dep_routingstep_no = p_dep_routingstep_no
667 AND routing_id = p_routing_id;
668
669 IF SQL%ROWCOUNT = 0 THEN
670 RAISE routing_update_dep_failure;
671 END IF;
672 END LOOP;
673
674 /* Check if work was done */
675 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
676 RAISE routing_update_dep_failure;
677 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
678 IF (l_debug = 'Y') THEN
679 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
680 END IF;
681
682 EXCEPTION
683 WHEN routing_update_dep_failure OR invalid_version THEN
684 IF (l_debug = 'Y') THEN
685 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
686 END IF;
687 x_return_status := FND_API.G_RET_STS_ERROR;
688 WHEN last_update_date_failure THEN
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
691 FND_MSG_PUB.ADD;
692 WHEN setup_failure THEN
693 x_return_status := FND_API.G_RET_STS_ERROR;
694 WHEN OTHERS THEN
695 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
696 IF (l_debug = 'Y') THEN
697 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
698 END IF;
699 x_return_status := FND_API.g_ret_sts_unexp_error;
700 END update_step_dependencies;
701
702 /* =============================================================== */
703 /* Procedure: */
704 /* Delete_Routing_step */
705 /* */
706 /* DESCRIPTION: */
707 /* */
708 /* API returns (x_return_code) = 'S' if the delete into routing */
709 /* step dependency (fm_rout_dep table) is success. */
710 /* */
711 /* History : */
712 /* Shyam 07/29/2002 Initial implementation */
713 /* =============================================================== */
714 PROCEDURE delete_routing_step
715 ( p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE
716 , p_routing_id IN gmd_routings.routing_id%TYPE := NULL
717 , x_return_status OUT NOCOPY VARCHAR2
718 ) IS
719
720 /* Local variable section */
721 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROUTING_STEP';
722 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
723 l_return_from_routing_step_dep VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
724 l_routingStep_id fm_rout_dtl.routingStep_id%TYPE;
725 l_routingstep_no fm_rout_dep.routingStep_no%TYPE;
726 l_stepdep_count NUMBER := 0;
727 l_exists PLS_INTEGER;
728
729 /* Define Cursors */
730 /* Cursor that check if there any row in the step dependency table that
731 needs to be deleted */
732 Cursor Check_Step_dep_rec(vRoutingstep_no fm_rout_dep.routingStep_no%TYPE
733 ,vRouting_id gmd_routings.Routing_id%TYPE) IS
734 Select count(*)
735 From fm_rout_dep
736 Where (routingStep_no = vRoutingStep_no OR dep_routingStep_no = vRoutingStep_no)
737 And routing_id = vrouting_id;
738
739
740 CURSOR Cur_check_step IS
741 SELECT 1
742 FROM SYS.DUAL
743 WHERE EXISTS (SELECT 1
744 FROM gmd_recipe_routing_steps
745 WHERE routingstep_id = p_routingstep_id);
746
747 CURSOR Cur_check_step2 IS
748 SELECT 1
749 FROM sys.dual
750 WHERE EXISTS (SELECT 1
751 FROM gmd_recipe_step_materials
752 WHERE routingstep_id = p_routingstep_id);
753
754 CURSOR Cur_check_orgn_act IS
755 SELECT 1
756 FROM sys.dual
757 WHERE EXISTS (SELECT 1
758 FROM gmd_recipe_orgn_activities
759 WHERE routingstep_id = p_routingstep_id);
760
761 CURSOR Cur_check_orgn_res IS
762 SELECT 1
763 FROM sys.dual
764 WHERE EXISTS (SELECT 1
765 FROM gmd_recipe_orgn_resources
766 WHERE routingstep_id = p_routingstep_id);
767
768
769 /* Define Exceptions */
770 routing_delete_step_failure EXCEPTION;
771 routing_delete_stepdep_failure EXCEPTION;
772 step_used_in_recipe EXCEPTION;
773 invalid_version EXCEPTION;
774 setup_failure EXCEPTION;
775
776 BEGIN
777 IF (l_debug = 'Y') THEN
778 gmd_debug.log_initialize('Derspvt');
779 END IF;
780
781 /* Intialize the setup fields */
782 IF NOT gmd_api_grp.setup_done THEN
783 gmd_api_grp.setup_done := gmd_api_grp.setup;
784 END IF;
785 IF NOT gmd_api_grp.setup_done THEN
786 RAISE setup_failure;
787 END IF;
788
789 /* Set the return status to success initially */
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791
792 /* Get the routingstep_no (routingstep_no is used for the routing step dep delete ) */
793 IF (l_debug = 'Y') THEN
794 gmd_debug.put_line(' get the RoutingStep_id - if it is not passed as a parameter ');
795 END IF;
796 IF p_routingStep_id IS NOT NULL THEN
797 l_routingstep_id := p_routingstep_id;
798 GMDRTVAL_PUB.get_routingstep_info(pxRoutingStep_no => l_routingstep_no
799 ,pxRoutingStep_id => l_routingstep_id
800 ,x_return_status => l_return_status );
801 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
802 /* it indicates that this routing does'ntexists */
803 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
804 FND_MSG_PUB.ADD;
805 RAISE routing_delete_step_failure;
806 END IF;
807 END IF;
808
809 IF (l_debug = 'Y') THEN
810 gmd_debug.put_line('RoutingStep_no = '||l_routingStep_no );
811 END IF;
812
813 /* Check if work was done */
814 IF SQL%ROWCOUNT = 0 THEN
815 RAISE routing_delete_step_failure;
816 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
817
818 /* Bug#5211932 - Check if the routing step is used in the recipe */
819
820 /* Check for any overrides at recipe routing steps */
821 OPEN Cur_check_step;
822 FETCH Cur_check_step INTO l_exists;
823 IF (Cur_check_step%FOUND) THEN
824 CLOSE Cur_check_step;
825 RAISE step_used_in_recipe;
826 END IF;
827 CLOSE Cur_check_step;
828
829 /* Check for any overrides at recipe step material */
830 OPEN Cur_check_step2;
831 FETCH Cur_check_step2 INTO l_exists;
832 IF (Cur_check_step2%FOUND) THEN
833 CLOSE Cur_check_step2;
834 RAISE step_used_in_recipe;
835 END IF;
836 CLOSE Cur_check_step2;
837
838 /* Check for any overrides at recipe organization activity level */
839 OPEN Cur_check_orgn_act;
840 FETCH Cur_check_orgn_act INTO l_exists;
841 IF (Cur_check_orgn_act%FOUND) THEN
842 CLOSE Cur_check_orgn_act;
843 RAISE step_used_in_recipe;
844 END IF;
845 CLOSE Cur_check_orgn_act;
846
847 /* Check for any overrides at recipe organization resource level */
848 OPEN Cur_check_orgn_res;
849 FETCH Cur_check_orgn_res INTO l_exists;
850 IF (Cur_check_orgn_res%FOUND) THEN
851 CLOSE Cur_check_orgn_res;
852 RAISE step_used_in_recipe;
853 END IF;
854 CLOSE Cur_check_orgn_res;
855
856 /* Check if any rows from fm_rout_dep needs to be deleted */
857 IF p_routing_id IS NOT NULL THEN
858 OPEN Check_Step_dep_rec(vRoutingstep_no => l_routingstep_no
859 ,vRouting_id => p_routing_id ) ;
860 FETCH Check_Step_dep_rec INTO l_stepdep_count;
861 CLOSE Check_Step_dep_rec;
862 END IF;
863
864 IF l_stepdep_count > 0 THEN
865 /* Delete rows in the step dependency table specific to this
866 routing_id and routingstep_no */
867 GMD_ROUTING_STEPS_PVT.delete_step_dependencies
868 (p_routingstep_no => l_routingstep_no
869 , p_routing_id => p_routing_id
870 , x_return_status => l_return_from_routing_step_dep
871 );
872
873 /* Check if insert of step dependency was done */
874 IF l_return_from_routing_step_dep <> FND_API.G_RET_STS_SUCCESS THEN
875 RAISE routing_delete_stepdep_failure;
876 END IF; /* IF l_return_from_routing_step_dep <> FND_API.G_RET_STS_SUCCESS */
877 END IF; /* l_stepdep_count > 0 */
878
879 /* Actual delete is performed */
880 DELETE FROM fm_rout_dtl
881 WHERE routingStep_id = p_routingStep_id;
882
883 IF (l_debug = 'Y') THEN
884 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
885 END IF;
886
887 EXCEPTION
888 WHEN routing_delete_step_failure OR invalid_version THEN
889 IF (l_debug = 'Y') THEN
890 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
891 END IF;
892 x_return_status := FND_API.G_RET_STS_ERROR;
893 WHEN step_used_in_recipe THEN
894 IF (l_debug = 'Y') THEN
895 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'routing step '||l_routingstep_no||' has override data ');
896 END IF;
897 FND_MESSAGE.SET_NAME('GMD', 'GMD_STEP_USED_IN_RECIPE');
898 FND_MSG_PUB.ADD;
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 WHEN routing_delete_stepdep_failure THEN
901 IF (l_debug = 'Y') THEN
902 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'delete step dep API not complete');
903 END IF;
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 WHEN setup_failure THEN
906 x_return_status := FND_API.G_RET_STS_ERROR;
907 WHEN OTHERS THEN
908 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
909 IF (l_debug = 'Y') THEN
910 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
911 END IF;
912 x_return_status := FND_API.g_ret_sts_unexp_error;
913 END delete_routing_step;
914
915
916 /* =============================================================== */
917 /* Procedure: */
918 /* delete_step_dependencies */
919 /* */
920 /* DESCRIPTION: */
921 /* */
922 /* API returns (x_return_code) = 'S' if the delete in routing */
923 /* step dependency (fm_rout_dep table) is success. */
924 /* */
925 /* History : */
926 /* Shyam 07/29/2002 Initial implementation */
927 /* =============================================================== */
928 PROCEDURE delete_step_dependencies
929 ( p_routingstep_no IN fm_rout_dep.routingstep_no%TYPE
930 , p_dep_routingstep_no IN fm_rout_dep.routingstep_no%TYPE := NULL
931 , p_routing_id IN fm_rout_dep.routing_id%TYPE
932 , x_return_status OUT NOCOPY VARCHAR2
933 ) IS
934
935 /* Local variable section */
936 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STEP_DEPENDENCIES';
937 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
938
939 /* Define Exceptions */
940 routing_delete_dep_failure EXCEPTION;
941 invalid_version EXCEPTION;
942 setup_failure EXCEPTION;
943
944 BEGIN
945 IF (l_debug = 'Y') THEN
946 gmd_debug.log_initialize('Desdpvt');
947 END IF;
948
949 /* Set the return status to success initially */
950 x_return_status := FND_API.G_RET_STS_SUCCESS;
951
952 /* Actual delete in fm_rout_dep table */
953 /* This delete can be specific to a dep_routingstep_no or a
954 Routingstep_no */
955 IF (l_debug = 'Y') THEN
956 gmd_Debug.put_line('About to delete from step dep table - the routingstep no = '||p_routingstep_no ||' and routing id = '||p_routing_id);
957 END IF;
958
959 /* Intialize the setup fields */
960 IF NOT gmd_api_grp.setup_done THEN
961 gmd_api_grp.setup_done := gmd_api_grp.setup;
962 END IF;
963 IF NOT gmd_api_grp.setup_done THEN
964 RAISE setup_failure;
965 END IF;
966
967 IF p_dep_routingstep_no IS NOT NULL THEN
968 DELETE FROM fm_rout_dep
969 WHERE routingstep_no = p_routingstep_no
970 AND dep_routingstep_no = p_dep_routingstep_no
971 AND routing_id = p_routing_id;
972 ELSE /* this would all dep steps for this step */
973 DELETE FROM fm_rout_dep
974 WHERE routingstep_no = p_routingstep_no
975 AND routing_id = p_routing_id;
976 DELETE FROM fm_rout_dep
977 WHERE dep_routingstep_no = p_routingstep_no
978 AND routing_id = p_routing_id;
979 END IF;
980
981 /* Check if work was done */
982 IF (l_debug = 'Y') THEN
983 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
984 END IF;
985
986 EXCEPTION
987 WHEN routing_delete_dep_failure OR invalid_version THEN
988 IF (l_debug = 'Y') THEN
989 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
990 END IF;
991 x_return_status := FND_API.G_RET_STS_ERROR;
992 WHEN setup_failure THEN
993 x_return_status := FND_API.G_RET_STS_ERROR;
994 WHEN OTHERS THEN
995 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
996 IF (l_debug = 'Y') THEN
997 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
998 END IF;
999 x_return_status := FND_API.g_ret_sts_unexp_error;
1000
1001 END delete_step_dependencies;
1002
1003 END GMD_ROUTING_STEPS_PVT;