[Home] [Help]
PACKAGE BODY: APPS.GME_REVERT_BATCH_PVT
Source
1 PACKAGE BODY gme_revert_batch_pvt AS
2 /* $Header: GMEVRWBB.pls 120.8 2006/07/13 17:37:24 creddy noship $ */
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 IF NOT gme_common_pvt.create_history (l_batch_header_rec, p_batch_header_rec.batch_status,
128 gme_common_pvt.g_transaction_header_id)
129 THEN
130 RAISE batch_hist_insert_err;
131 END IF;
132 END IF;
133
134 /* Remove the actual completion date of the batch */
135 l_batch_header_rec.actual_cmplt_date := NULL;
136
137 /* Update the batch header row to the database */
138 IF NOT GME_BATCH_HEADER_DBL.update_row(l_batch_header_rec) THEN
139 RAISE BATCH_HDR_UPDATE_ERROR;
140 END IF;
141 x_batch_header_rec := l_batch_header_rec ;
142 /* Update the row who columns */
143 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
144 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
145 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
146
147 EXCEPTION
148
149
150 WHEN BATCH_LINES_LOCKED THEN
151 x_return_status := FND_API.G_RET_STS_ERROR;
152 gme_common_pvt.log_message('GME_API_BATCH_LINES_LOCKED');
153
154 WHEN REVERT_LINE_FAIL THEN
155 x_return_status := l_return_status;
156
157 WHEN BATCH_HIST_INSERT_ERR THEN
158 x_return_status := FND_API.G_RET_STS_ERROR;
159 WHEN BATCH_HDR_UPDATE_ERROR THEN
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 WHEN app_exception.record_lock_exception THEN
162 x_return_status := FND_API.G_RET_STS_ERROR;
163 gme_common_pvt.log_message('GME_RECORD_LOCKED','TABLE_NAME', g_table_name, 'KEY',to_char(p_batch_header_rec.batch_id));
164 WHEN OTHERS THEN
165 x_return_status := fnd_api.g_ret_sts_unexp_error;
166
167 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
168 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'WHEN OTHERS:' || SQLERRM);
169 END IF;
170
171 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
172 END revert_batch;
173
174
175 /*===========================================================================================
176 Procedure
177 revert_line
178 Description
179 This procedure traverses the list of Transactions for the Material
180 Detail line and reverses the completed transactions and posts a pending transaction
181
182 Parameters
183 p_batch_header Batch header row
184 p_material_detail Material line detail record
185 x_return_status outcome of the API call
186 S - Success
187 E - Error
188 U - Unexpected error
189 =============================================================================================*/
190
191 PROCEDURE revert_line
192 (p_batch_header_rec IN GME_BATCH_HEADER%ROWTYPE
193 ,p_material_details_rec IN gme_material_details%ROWTYPE
194 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
195 ,x_return_status OUT NOCOPY VARCHAR2)IS
196
197 /* Local variable definitions */
198 l_return_status VARCHAR2(1);
199 l_step_id NUMBER;
200 l_step_date DATE;
201 l_api_name CONSTANT VARCHAR2 (30) := 'revert_line';
202 l_rel_type NUMBER;
203 l_batch_header_rec gme_batch_header%ROWTYPE;
204 l_material_details_rec gme_material_details%ROWTYPE;
205 l_ph_batch_header_rec gme_batch_header%ROWTYPE;
206 l_batch_step_rec gme_batch_steps%ROWTYPE;
207 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
208 l_rsrc_trans_count NUMBER;
209 l_message_count NUMBER;
210 l_message_list VARCHAR2(2000);
211 l_exception_material_tbl gme_common_pvt.exceptions_tab;
212 l_actual_qty NUMBER;
213 CURSOR Cur_get_step (V_material_detail_id NUMBER) IS
214 SELECT batchstep_id
215 FROM gme_batch_step_items
216 WHERE material_detail_id = V_material_detail_id;
217 /* Exception definition */
218 UPDATE_STEP_QTY_ERROR EXCEPTION;
219 ERROR_UPD_MATERIAL_DETAIL EXCEPTION;
220 ERROR_LOAD_TRANS EXCEPTION;
221 BATCH_HEADER_FETCH_ERROR EXCEPTION;
222 REVERT_PHANT_FAIL EXCEPTION;
223 REVERT_MATL_ERROR EXCEPTION;
224 /* Bug 5001915 Get item control for transactions */
225 CURSOR Cur_get_item(V_org_id NUMBER,
226 V_inventory_item_id NUMBER) IS
227 SELECT mtl_transactions_enabled_flag
228 FROM mtl_system_items_b
229 WHERE organization_id = V_org_id
230 AND inventory_item_id = V_inventory_item_id;
231 l_txn_enabled_flag VARCHAR2(1);
232 BEGIN
233
234 /* Inititialize the return status to success */
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236 IF (g_debug <= gme_debug.g_log_statement) THEN
237 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
238 || 'Entering');
239 END IF;
240
241 IF p_material_details_rec.line_type = -1 THEN
242 l_ph_batch_header_rec.batch_id := p_material_details_rec.phantom_id ;
243 /* Initialize output batch header */
244 IF NOT (GME_BATCH_HEADER_DBL.fetch_row(l_ph_batch_header_rec, l_ph_batch_header_rec)) THEN
245 RAISE BATCH_HEADER_FETCH_ERROR;
246 END IF;
247 -- revert_batch for phantom batch
248 IF (g_debug <= gme_debug.g_log_statement) THEN
249 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
250 || 'calling for phantom:'||l_ph_batch_header_rec.batch_id );
251 END IF;
252 revert_batch
253 ( p_batch_header_rec => l_ph_batch_header_rec
254 , x_batch_header_rec => l_batch_header_rec
255 , x_return_status => x_return_status);
256 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
257 RAISE REVERT_PHANT_FAIL;
258 END IF;
259 ELSE
260 l_batch_header_rec := p_batch_header_rec ;
261 l_material_details_rec := p_material_details_rec ;
262 IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
263 IF (g_debug <= gme_debug.g_log_statement) THEN
264 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
265 || 'calling revert mat:'||p_material_details_rec.material_detail_id);
266 END IF;
267 /* Bug 5001915 get values for item control and added if condition before calling revert mtl */
268 OPEN Cur_get_item(p_material_details_rec.organization_id, p_material_details_rec.inventory_item_id);
269 FETCH Cur_get_item INTO l_txn_enabled_flag;
270 CLOSE Cur_get_item;
271 IF l_txn_enabled_flag = 'Y' THEN
272 gme_unrelease_batch_pvt.revert_material_full
273 (p_material_detail_rec => p_material_details_rec
274 ,p_create_resv_pend_lots => 1
275 ,x_actual_qty => l_actual_qty
276 ,x_exception_material_tbl => l_exception_material_tbl
277 ,x_return_status => x_return_status );
278 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
279 RAISE REVERT_MATL_ERROR;
280 END IF;
281 END IF;
282 /* Default the actual qty to zero */
283 l_material_details_rec.actual_qty := 0;
284
285 /* Update the material detail row to the database */
286 IF NOT GME_MATERIAL_DETAILS_DBL.update_row (l_material_details_rec) THEN
287 RAISE ERROR_UPD_MATERIAL_DETAIL;
288 END IF;
289 IF l_batch_header_rec.POC_IND= 'Y' THEN
290 IF l_batch_header_rec.automatic_step_calculation = 1 THEN
291 OPEN Cur_get_step (l_material_details_rec.material_detail_id);
292 FETCH Cur_get_step INTO l_batch_step_rec.batchstep_id;
293 CLOSE Cur_get_step;
294 IF l_batch_step_rec.batchstep_id IS NOT NULL THEN
295 /* Invoke the update step qty API to update the step quantities and the */
296 /* quantities of the succeeding steps */
297
298
299 GME_TRANS_ENGINE_UTIL.load_rsrc_trans (l_batch_header_rec
300 ,l_rsrc_trans_count
301 ,l_return_status);
302 IF l_return_status <> x_return_status THEN
303 RAISE ERROR_LOAD_TRANS;
304 END IF;
305 /* Bug 5021522 Added l_in_batch_step_rec new rec type because of NOCOPY */
306 l_in_batch_step_rec := l_batch_step_rec;
307 GME_UPDATE_STEP_QTY_pvt.update_step_qty (P_batch_step_rec => l_in_batch_step_rec
308 ,x_message_count => l_message_count
309 ,x_message_list => l_message_list
310 ,x_return_status => l_return_status
311 ,x_batch_step_rec => l_batch_step_rec);
312 IF l_return_status <> x_return_status THEN
313 RAISE UPDATE_STEP_QTY_ERROR;
314 END IF;
315 END IF; /*l_batch_step_rec.batchstep_id*/
316 END IF; /*IF x_batch_header.automatic_step_calculation = 1 */
317 END IF; /* POC_IND*/
318 END IF; /* hupdate_inventory_ind*/
319 END IF;/* line_type */
320
321 EXCEPTION
322
323 WHEN REVERT_MATL_ERROR THEN
324 NULL;
325 WHEN BATCH_HEADER_FETCH_ERROR THEN
326 x_return_status := FND_API.G_RET_STS_ERROR;
327 GME_common_pvt.log_message('GME_API_BATCH_FETCH_ERROR');
328 WHEN REVERT_PHANT_FAIL THEN
329 NULL;
330 WHEN ERROR_LOAD_TRANS OR UPDATE_STEP_QTY_ERROR THEN
331 x_return_status := l_return_status;
332 WHEN OTHERS THEN
333 x_return_status := fnd_api.g_ret_sts_unexp_error;
334
335 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
336 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'WHEN OTHERS:' || SQLERRM);
337 END IF;
338
339 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
340 END revert_line;
341
342
343
344 END gme_revert_batch_pvt;