1 PACKAGE BODY gme_revert_batch_pvt AS
2 /* $Header: GMEVRWBB.pls 120.10.12020000.2 2012/07/26 15:47:23 gmurator ship $ */
3
4 G_DEBUG VARCHAR2(5) := FND_PROFILE.VALUE('AFLOG_LEVEL');
5
6 /* Global Variables */
7 G_table_name VARCHAR2(80) DEFAULT 'GME_MATERIAL_DETAILS';
8 g_pkg_name CONSTANT VARCHAR2(30) := 'GME_REVERT_BATCH';
9
10 /*===========================================================================================
11 Procedure
12 revert_batch
13 Description
14 This particular procedure handles revert batch to WIP.
15 Parameters
16 p_batch_header The batch header row to identify the batch
17 x_return_status outcome of the API call
18 S - Success
19 E - Error
20 U - Unexpected error
21 =============================================================================================*/
22
23 PROCEDURE revert_batch
24 ( p_batch_header_rec IN GME_BATCH_HEADER%ROWTYPE
25 , x_batch_header_rec OUT NOCOPY GME_BATCH_HEADER%ROWTYPE
26 , x_return_status OUT NOCOPY VARCHAR2) IS
27
28 CURSOR Cur_lock_batch_materials (v_batch_id NUMBER) IS
29 SELECT *
30 FROM gme_material_details
31 WHERE batch_id = v_batch_id
32 FOR UPDATE OF actual_qty NOWAIT;
33 CURSOR Cur_revert_materials (v_batch_id NUMBER) IS
34 SELECT *
35 FROM gme_material_details
36 WHERE batch_id = v_batch_id
37 AND ((line_type = -1 AND phantom_id IS NOT NULL) OR
38 (line_type <> -1 AND phantom_line_id IS NULL ))
39 ORDER BY line_no ;
40
41 /* Buffers for database reads/writes */
42
43 l_batch_header_rec gme_batch_header%ROWTYPE;
44 l_material_details_tab gme_common_pvt.material_details_tab ;
45
46 /* Exception definitions */
47 BATCH_LINES_LOCKED EXCEPTION;
48 MATERIAL_DETAIL_FETCH_ERROR EXCEPTION;
49
50 BATCH_HIST_INSERT_ERR EXCEPTION;
51 BATCH_HDR_UPDATE_ERROR EXCEPTION;
52 REVERT_LINE_FAIL EXCEPTION;
53
54 /* Local variables */
55 l_return_status VARCHAR2(1);
56 l_count NUMBER(5) DEFAULT 0;
57 l_inv_trans_count NUMBER;
58 l_rsrc_trans_count NUMBER;
59 l_message_count NUMBER;
60 l_message_list VARCHAR2(2000);
61
62 l_api_name CONSTANT VARCHAR2 (30) := 'revert_batch';
63 BEGIN
64 /* Set the return status to success initially */
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 IF (g_debug <= gme_debug.g_log_statement) THEN
68 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
69 || 'Entering');
70 END IF;
71
72 l_batch_header_rec := p_batch_header_rec;
73
74 /* Lock all the material lines associated with the batch */
75 OPEN Cur_lock_batch_materials(l_batch_header_rec.batch_id);
76 FETCH Cur_lock_batch_materials BULK COLLECT INTO l_material_details_tab;
77 IF sqlcode = -54 THEN
78 CLOSE Cur_lock_batch_materials;
79 RAISE BATCH_LINES_LOCKED;
80 END IF;
81 CLOSE Cur_lock_batch_materials;
82
83 /* Mark batch as 'WIP' */
84 l_batch_header_rec.batch_status := 2;
85 gme_common_pvt.g_batch_status_check := fnd_api.g_false;
86 /* Fetch all the material lines associated with the batch */
87 FOR i IN 1..l_material_details_tab.COUNT LOOP
88 IF (g_debug <= gme_debug.g_log_statement) THEN
89 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
90 || 'material line count'||l_material_details_tab.COUNT );
91 END IF;
92 /* We should not revert the phantom product, so added the check below to see */
93 /* if the current line being process is a phantom product, if it is not a phantom */
94 /* product then we add it to the queue of lines for revert */
95 IF
96 ((l_material_details_tab(i).line_type = -1
97 AND l_material_details_tab(i).phantom_id IS NOT NULL)
98 OR
99 (l_material_details_tab(i).line_type <> -1
100 AND l_material_details_tab(i).phantom_line_id IS NULL
101 AND (gme_common_pvt.is_material_auto_release
102 (l_material_details_tab(i).material_detail_id) = 0 )
103 )) THEN
104 IF (g_debug <= gme_debug.g_log_statement) THEN
105 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
106 || ' calling revert line for:'||l_material_details_tab(i).material_detail_id );
107 END IF;
108 revert_line
109 (p_batch_header_rec => l_batch_header_rec
110 ,p_material_details_rec => l_material_details_tab(i)
111 ,p_batch_step_rec => NULL
112 ,x_return_status => l_return_status) ;
113 IF (g_debug <= gme_debug.g_log_statement) THEN
114 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
115 || 'return status from revert line:'||l_return_status);
116 END IF;
117 IF l_return_status <> x_return_status THEN
118 RAISE REVERT_LINE_FAIL;
119 END IF;
120
121 END IF;
122
123 END LOOP;
124
125 IF l_batch_header_rec.update_inventory_ind = 'Y'
126 THEN
127 -- Bug 11846735 - Do not pass in a value for event_id.
128 IF NOT gme_common_pvt.create_history (l_batch_header_rec, p_batch_header_rec.batch_status)
129 -- , gme_common_pvt.g_transaction_header_id)
130 THEN
131 RAISE batch_hist_insert_err;
132 END IF;
133 END IF;
134
135 /* Remove the actual completion date of the batch */
136 l_batch_header_rec.actual_cmplt_date := NULL;
137
138 /* Update the batch header row to the database */
139 IF NOT GME_BATCH_HEADER_DBL.update_row(l_batch_header_rec) THEN
140 RAISE BATCH_HDR_UPDATE_ERROR;
141 END IF;
142 x_batch_header_rec := l_batch_header_rec ;
143 /* Update the row who columns */
144 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
145 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
146 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
147
148 EXCEPTION
149
150
151 WHEN BATCH_LINES_LOCKED THEN
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 gme_common_pvt.log_message('GME_API_BATCH_LINES_LOCKED');
154
155 WHEN REVERT_LINE_FAIL THEN
156 x_return_status := l_return_status;
157
158 WHEN BATCH_HIST_INSERT_ERR THEN
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 WHEN BATCH_HDR_UPDATE_ERROR THEN
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 WHEN app_exception.record_lock_exception THEN
163 x_return_status := FND_API.G_RET_STS_ERROR;
164 gme_common_pvt.log_message('GME_RECORD_LOCKED','TABLE_NAME', g_table_name, 'KEY',to_char(p_batch_header_rec.batch_id));
165 WHEN OTHERS THEN
166 x_return_status := fnd_api.g_ret_sts_unexp_error;
167
168 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
169 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'WHEN OTHERS:' || SQLERRM);
170 END IF;
171
172 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
173 END revert_batch;
174
175
176 /*===========================================================================================
177 Procedure
178 revert_line
179 Description
180 This procedure traverses the list of Transactions for the Material
181 Detail line and reverses the completed transactions and posts a pending transaction
182
183 Parameters
184 p_batch_header Batch header row
185 p_material_detail Material line detail record
186 x_return_status outcome of the API call
187 S - Success
188 E - Error
189 U - Unexpected error
190 =============================================================================================*/
191
192 PROCEDURE revert_line
193 (p_batch_header_rec IN GME_BATCH_HEADER%ROWTYPE
194 ,p_material_details_rec IN gme_material_details%ROWTYPE
195 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
196 ,x_return_status OUT NOCOPY VARCHAR2)IS
197
198 /* Local variable definitions */
199 l_return_status VARCHAR2(1);
200 l_step_id NUMBER;
201 l_step_date DATE;
202 l_api_name CONSTANT VARCHAR2 (30) := 'revert_line';
203 l_rel_type NUMBER;
204 l_batch_header_rec gme_batch_header%ROWTYPE;
205 l_material_details_rec gme_material_details%ROWTYPE;
206 l_ph_batch_header_rec gme_batch_header%ROWTYPE;
207 l_batch_step_rec gme_batch_steps%ROWTYPE;
208 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
209 l_rsrc_trans_count NUMBER;
210 l_message_count NUMBER;
211 l_message_list VARCHAR2(2000);
212 l_exception_material_tbl gme_common_pvt.exceptions_tab;
213 l_actual_qty NUMBER;
214 CURSOR Cur_get_step (V_material_detail_id NUMBER) IS
215 SELECT batchstep_id
216 FROM gme_batch_step_items
217 WHERE material_detail_id = V_material_detail_id;
218 /* Exception definition */
219 UPDATE_STEP_QTY_ERROR EXCEPTION;
220 ERROR_UPD_MATERIAL_DETAIL EXCEPTION;
221 ERROR_LOAD_TRANS EXCEPTION;
222 BATCH_HEADER_FETCH_ERROR EXCEPTION;
223 REVERT_PHANT_FAIL EXCEPTION;
224 REVERT_MATL_ERROR EXCEPTION;
225 /* Bug 5001915 Get item control for transactions */
226 CURSOR Cur_get_item(V_org_id NUMBER,
227 V_inventory_item_id NUMBER) IS
228 SELECT mtl_transactions_enabled_flag
229 FROM mtl_system_items_b
230 WHERE organization_id = V_org_id
231 AND inventory_item_id = V_inventory_item_id;
232 l_txn_enabled_flag VARCHAR2(1);
233 BEGIN
234
235 /* Inititialize the return status to success */
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237 IF (g_debug <= gme_debug.g_log_statement) THEN
238 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
239 || 'Entering');
240 END IF;
241
242 IF p_material_details_rec.line_type = -1 THEN
243 l_ph_batch_header_rec.batch_id := p_material_details_rec.phantom_id ;
244 /* Initialize output batch header */
245 IF NOT (GME_BATCH_HEADER_DBL.fetch_row(l_ph_batch_header_rec, l_ph_batch_header_rec)) THEN
246 RAISE BATCH_HEADER_FETCH_ERROR;
247 END IF;
248 -- revert_batch for phantom batch
249 IF (g_debug <= gme_debug.g_log_statement) THEN
250 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
251 || 'calling for phantom:'||l_ph_batch_header_rec.batch_id );
252 END IF;
253 revert_batch
254 ( p_batch_header_rec => l_ph_batch_header_rec
255 , x_batch_header_rec => l_batch_header_rec
256 , x_return_status => x_return_status);
257 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
258 RAISE REVERT_PHANT_FAIL;
259 END IF;
260 ELSE
261 l_batch_header_rec := p_batch_header_rec ;
262 l_material_details_rec := p_material_details_rec ;
263 IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
264 IF (g_debug <= gme_debug.g_log_statement) THEN
265 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
266 || 'calling revert mat:'||p_material_details_rec.material_detail_id);
267 END IF;
268 /* Bug 5001915 get values for item control and added if condition before calling revert mtl */
269 OPEN Cur_get_item(p_material_details_rec.organization_id, p_material_details_rec.inventory_item_id);
270 FETCH Cur_get_item INTO l_txn_enabled_flag;
271 CLOSE Cur_get_item;
272 IF l_txn_enabled_flag = 'Y' THEN
273 gme_unrelease_batch_pvt.revert_material_full
274 (p_material_detail_rec => p_material_details_rec
275 ,p_create_resv_pend_lots => 1
276 ,x_actual_qty => l_actual_qty
277 ,x_exception_material_tbl => l_exception_material_tbl
278 ,x_return_status => x_return_status );
279 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
280 RAISE REVERT_MATL_ERROR;
281 END IF;
282 END IF;
283 /* Default the actual qty to zero */
284 l_material_details_rec.actual_qty := 0;
285
286 /* Update the material detail row to the database */
287 IF NOT GME_MATERIAL_DETAILS_DBL.update_row (l_material_details_rec) THEN
288 RAISE ERROR_UPD_MATERIAL_DETAIL;
289 END IF;
290 IF l_batch_header_rec.POC_IND= 'Y' THEN
291 IF l_batch_header_rec.automatic_step_calculation = 1 THEN
292 OPEN Cur_get_step (l_material_details_rec.material_detail_id);
293 FETCH Cur_get_step INTO l_batch_step_rec.batchstep_id;
294 CLOSE Cur_get_step;
295 IF l_batch_step_rec.batchstep_id IS NOT NULL THEN
296 /* Invoke the update step qty API to update the step quantities and the */
297 /* quantities of the succeeding steps */
298
299
300 GME_TRANS_ENGINE_UTIL.load_rsrc_trans (l_batch_header_rec
301 ,l_rsrc_trans_count
302 ,l_return_status);
303 IF l_return_status <> x_return_status THEN
304 RAISE ERROR_LOAD_TRANS;
305 END IF;
306 /* Bug 5021522 Added l_in_batch_step_rec new rec type because of NOCOPY */
307 l_in_batch_step_rec := l_batch_step_rec;
308 GME_UPDATE_STEP_QTY_pvt.update_step_qty (P_batch_step_rec => l_in_batch_step_rec
309 ,x_message_count => l_message_count
310 ,x_message_list => l_message_list
311 ,x_return_status => l_return_status
312 ,x_batch_step_rec => l_batch_step_rec);
313 IF l_return_status <> x_return_status THEN
314 RAISE UPDATE_STEP_QTY_ERROR;
315 END IF;
316 END IF; /*l_batch_step_rec.batchstep_id*/
317 END IF; /*IF x_batch_header.automatic_step_calculation = 1 */
318 END IF; /* POC_IND*/
319 END IF; /* hupdate_inventory_ind*/
320 END IF;/* line_type */
321
322 EXCEPTION
323
324 WHEN REVERT_MATL_ERROR THEN
325 NULL;
326 WHEN BATCH_HEADER_FETCH_ERROR THEN
327 x_return_status := FND_API.G_RET_STS_ERROR;
328 GME_common_pvt.log_message('GME_API_BATCH_FETCH_ERROR');
329 WHEN REVERT_PHANT_FAIL THEN
330 NULL;
331 WHEN ERROR_LOAD_TRANS OR UPDATE_STEP_QTY_ERROR THEN
332 x_return_status := l_return_status;
333 WHEN OTHERS THEN
334 x_return_status := fnd_api.g_ret_sts_unexp_error;
335
336 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
337 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'WHEN OTHERS:' || SQLERRM);
338 END IF;
339
340 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
341 END revert_line;
342
343
344
345 END gme_revert_batch_pvt;
[Home] [Help]