1 PACKAGE BODY gme_revert_step_pvt AS
2 /* $Header: GMEVRWSB.pls 120.4 2005/10/26 15:55:51 pxkumar noship $ */
3
4 G_DEBUG VARCHAR2(5) := FND_PROFILE.VALUE('AFLOG_LEVEL');
5 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_revert_step_pvt';
6
7 /*===========================================================================================
8 Procedure
9 revert_step
10 Description
11 This particular procedure call Uncertifies the batch steps.
12 Parameters
13 p_api_version For version specific processing - Default 1
14 p_validation_level Errors to skip before returning - Default 100
15 p_init_msg_list Signals wether the message stack should be initialised
16 p_commit Indicator to commit the changes made
17 p_batch_step The batch step row to identify the step.
18 x_message_count The number of messages in the message stack
19 x_message_list message stack where the api writes its messages
20 x_return_status outcome of the API call
21 S - Success
22 E - Error
23 U - Unexpected error
24 N - Unallocated Items Found
25 =============================================================================================*/
26
27 PROCEDURE revert_step
28 (p_batch_step_rec IN GME_BATCH_STEPS%ROWTYPE
29 ,p_batch_header_rec IN GME_BATCH_HEADER%ROWTYPE
30 ,x_batch_step_rec OUT NOCOPY GME_BATCH_STEPS%ROWTYPE
31 ,x_return_status OUT NOCOPY VARCHAR2) IS
32
33 /* Buffers for database reads/writes */
34 l_batch_header_rec gme_batch_header%ROWTYPE;
35 l_in_batch_header_rec gme_batch_header%ROWTYPE;
36 x_in_batch_step_rec gme_batch_steps%ROWTYPE;
37 l_batch_step_rec gme_batch_steps%ROWTYPE;
38 l_material_details_tab gme_common_pvt.material_details_tab ;
39 /* Bug 2685645 added batch_id as parama and used in where clause */
40 CURSOR cur_dep_steps (v_batchstep_id NUMBER,
41 v_batch_id NUMBER) IS
42 SELECT batchstep_no, s.step_status,d.dep_type,d.standard_delay, s.steprelease_type
46 s.batch_id = v_batch_id AND
43 FROM gme_batch_step_dependencies d, gme_batch_steps s
44 WHERE d.batchstep_id = s.batchstep_id AND
45 d.dep_step_id = v_batchstep_id AND
47 d.batch_id = s.batch_id;
48
49 CURSOR Cur_lock_step_materials ( v_batchstep_id NUMBER,
50 v_batch_id NUMBER) IS
51 SELECT D.*
52 FROM gme_batch_step_items i, gme_material_details d
53 WHERE d.batch_id = v_batch_id AND
54 i.material_detail_id = d.material_detail_id AND
55 i.batchstep_id = v_batchstep_id AND
56 d.release_type = 3
57 FOR UPDATE OF actual_qty NOWAIT;
58
59 -- ((d.line_type = -1 AND d.phantom_id IS NOT NULL)
60 -- OR (d.line_type <> -1 AND phantom_line_id IS NULL));
61 /* Exception definitions */
62
63
64 invalid_dep_step_status EXCEPTION;
65 batch_revert_error EXCEPTION;
66 invalid_batch_status EXCEPTION;
67 BATCH_LINES_LOCKED EXCEPTION;
68 REVERT_LINE_FAIL EXCEPTION;
69 BATCH_STEP_UPD_ERR EXCEPTION;
70 /* Local variables */
71 l_return_status VARCHAR2 (1);
72 l_inv_trans_count NUMBER;
73 l_rsrc_trans_count NUMBER;
74 l_api_name CONSTANT VARCHAR2 (30) := 'revert_step';
75 l_dep_step_rec Cur_dep_steps%ROWTYPE;
76 BEGIN
77 /* Set the return status to success initially */
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79 IF g_debug <= gme_debug.g_log_procedure THEN
80 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
81 || 'Entering');
82 END IF;
83
84 l_batch_step_rec := p_batch_step_rec ;
85 l_batch_header_rec := p_batch_header_rec;
86 IF g_debug <= gme_debug.g_log_procedure THEN
87 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
88 || 'batchstep_id'||l_batch_step_rec.batchstep_id);
89 END IF;
90 /* The Batch must be in WIP to revert a Batch Step. */
91 IF (l_batch_header_rec.batch_status <> 2) THEN
92 IF ( g_debug <= gme_debug.g_log_procedure) THEN
93 gme_debug.put_line ('batch status'|| l_batch_header_rec.batch_status);
94 END IF;
95 RAISE invalid_batch_status;
96 END IF;
97
98 IF l_batch_header_rec.automatic_step_calculation = 1 OR
99 l_batch_step_rec.steprelease_type = 2 /*automatic*/ OR
100 l_batch_header_rec.enforce_step_dependency = 1 THEN
101
102 FOR l_dep_step_rec IN cur_dep_steps (l_batch_step_rec.batchstep_id, l_batch_step_rec.batch_id)
103 LOOP
104 IF (l_dep_step_rec.dep_type = 0) THEN
105 IF l_dep_step_rec.step_status > 1 THEN
106 RAISE invalid_dep_step_status;
107 END IF ;/*step_status > 1*/
108 END IF;
109 END LOOP; /* FOR dep_step_rec IN Cur_dep_steps */
110 END IF; /* IF ASQC is on OR release_type is automatic */
111
112 /* If the profile value for step control is 'Y' then if the batch status */
113 /* is completed then we have to call the revert batch API and then go */
114 /* ahead in revert the step. If the step control profile is set to */
115 /* 'N' then the batch should be in WIP status to revert the step */
116
117 IF GME_common_pvt.g_step_controls_batch_sts_ind = 1 THEN
118
119 /* If the batch is certified and the batch is not a phantom */
120 IF (l_batch_header_rec.batch_status = 3) AND
121 (l_batch_header_rec.parentline_id IS NULL) THEN
122 /* Call the revert batch API */
123 IF ( g_debug <= gme_debug.g_log_procedure ) THEN
124 gme_debug.put_line ('revert batch'||GME_common_pvt.g_step_controls_batch_sts_ind);
125 END IF;
126 l_in_batch_header_rec := l_batch_header_rec;
127 gme_revert_batch_pvt.revert_batch
128 (p_batch_header_rec => l_in_batch_header_rec
129 ,x_batch_header_rec => l_batch_header_rec
130 ,x_return_status => l_return_status);
131
132 IF l_return_status <> x_return_status THEN
133
134 RAISE batch_revert_error;
135 END IF;
136 END IF; /* IF (l_batch_header_rec.batch_status = 3) */
137 END IF; /* IFGME_common_pvt.STEP_CONTROL = 'Y' */
138
139
140 -- remove actual date from step record
141 l_batch_step_rec.actual_cmplt_date := NULL;
142 -- Update step status to WIP
143 l_batch_step_rec.step_status := 2;
144 gme_common_pvt.g_batch_status_check := fnd_api.g_false;
145 -- Update the batch step to the database
146 IF NOT (gme_batch_steps_dbl.update_row (l_batch_step_rec)) THEN
147 RAISE batch_step_upd_err;
148 END IF;
149 IF ( g_debug <= gme_debug.g_log_procedure ) THEN
150 gme_debug.put_line ('batchstep status'||l_batch_step_rec.step_status);
151 gme_debug.put_line ('batch_id'||l_batch_step_rec.batch_id);
152 gme_debug.put_line ('batchstep_id'||l_batch_step_rec.batchstep_id);
153
154 END IF;
155 OPEN Cur_lock_step_materials(l_batch_step_rec.batchstep_id,l_batch_step_rec.batch_id);
156 FETCH Cur_lock_step_materials BULK COLLECT INTO l_material_details_tab;
157 IF sqlcode = -54 THEN
158 CLOSE Cur_lock_step_materials;
159 RAISE BATCH_LINES_LOCKED;
160 END IF;
161 CLOSE Cur_lock_step_materials;
162 IF ( g_debug <= gme_debug.g_log_procedure) THEN
163 gme_debug.put_line ('l_material_details_tab count'||l_material_details_tab.count);
164 END IF;
165 FOR i IN 1..l_material_details_tab.COUNT LOOP
166 IF ((l_material_details_tab(i).line_type = -1 AND l_material_details_tab(i).phantom_id IS NOT NULL) OR
167 (l_material_details_tab(i).line_type <> -1 AND l_material_details_tab(i).phantom_line_id IS NULL)) THEN
168 IF ( g_debug <= gme_debug.g_log_procedure ) THEN
169 gme_debug.put_line ('calling revert for material line '
170 ||l_material_details_tab(i).material_detail_id);
171 gme_debug.put_line ('batch update inventory '
172 ||l_batch_header_rec.update_inventory_ind);
173 END IF;
174
175 gme_revert_batch_pvt.revert_line
176 (p_batch_header_rec => l_batch_header_rec
177 ,p_material_details_rec => l_material_details_tab(i)
178 ,p_batch_step_rec => l_batch_step_rec
179 ,x_return_status => l_return_status) ;
180 IF l_return_status <> x_return_status THEN
181 RAISE REVERT_LINE_FAIL;
182 END IF;
183 END IF;
184 END LOOP;
185
186
187 UPDATE gme_batch_step_activities
188 SET actual_cmplt_date = NULL
189 ,last_updated_by = gme_common_pvt.g_user_ident
190 ,last_update_date = gme_common_pvt.g_timestamp
191 ,last_update_login = gme_common_pvt.g_login_id
192 WHERE batchstep_id = l_batch_step_rec.batchstep_id
193 AND batch_id = p_batch_header_rec.batch_id;
194
195
196 UPDATE gme_batch_step_resources
197 SET actual_cmplt_date = NULL
198 ,last_updated_by = gme_common_pvt.g_user_ident
199 ,last_update_date = gme_common_pvt.g_timestamp
200 ,last_update_login = gme_common_pvt.g_login_id
201 WHERE batchstep_id = l_batch_step_rec.batchstep_id;
202
203
204 EXCEPTION
205
206 WHEN invalid_dep_step_status THEN
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 GME_common_pvt.log_message ('GME_INV_DEP_STEP_STATUS');
209
210 WHEN REVERT_LINE_FAIL THEN
211 x_return_status := l_return_status;
212 WHEN batch_revert_error THEN
213 x_return_status := l_return_status;
214 WHEN invalid_batch_status THEN
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 GME_common_pvt.log_message ('GME_API_INV_BATCH_UNCERT_STEP');
217
218 WHEN BATCH_LINES_LOCKED THEN
219 x_return_status := FND_API.G_RET_STS_ERROR;
220 gme_common_pvt.log_message('GME_API_BATCH_LINES_LOCKED');
221 WHEN OTHERS THEN
222 x_return_status := fnd_api.g_ret_sts_unexp_error;
223
224 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
225 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'WHEN OTHERS:' || SQLERRM);
226 END IF;
227 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
228 END revert_step;
229
230 END gme_revert_step_pvt;
[Home] [Help]