[Home] [Help]
PACKAGE BODY: APPS.GME_API_GRP
Source
1 PACKAGE BODY gme_api_grp AS
2 /* $Header: GMEGAPIB.pls 120.41 2010/12/01 07:00:38 apmishra ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_API_GRP';
5
6 PROCEDURE gme_pre_process_txns (
7 p_header_id IN NUMBER
8 ,x_return_status OUT NOCOPY VARCHAR2)
9 IS
10 l_api_name CONSTANT VARCHAR2 (30) := 'GME_PRE_PROCESS_TXNS';
11 l_return_status VARCHAR2 (1);
12 BEGIN
13 -- Initially let us assign the return status to success
14 x_return_status := fnd_api.g_ret_sts_success;
15
16 IF (g_debug <= gme_debug.g_log_statement) THEN
17 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
18 || 'Entering');
19 gme_debug.put_line ( g_pkg_name
20 || '.'
21 || l_api_name
22 || ':'
23 || 'header_id is :'
24 || p_header_id);
25 END IF;
26
27 IF p_header_id IS NOT NULL THEN
28 gme_transactions_pvt.gme_pre_process
29 (p_transaction_hdr_id => p_header_id
30 ,x_return_status => l_return_status);
31
32 IF l_return_status <> fnd_api.g_ret_sts_success THEN
33 RAISE fnd_api.g_exc_error;
34 END IF;
35 END IF;
36 x_return_status := l_return_status;
37 IF (g_debug <= gme_debug.g_log_statement) THEN
38 gme_debug.put_line ( g_pkg_name
39 || '.'
40 || l_api_name
41 || ':'
42 || 'Exiting with '
43 || l_return_status);
44 END IF;
45
46 EXCEPTION
47 WHEN fnd_api.g_exc_error THEN
48 x_return_status := l_return_status;
49 WHEN fnd_api.g_exc_unexpected_error THEN
50 x_return_status := fnd_api.g_ret_sts_unexp_error;
51 WHEN OTHERS THEN
52 x_return_status := fnd_api.g_ret_sts_unexp_error;
53
54 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
55 gme_debug.put_line ( g_pkg_name
56 || '.'
57 || l_api_name
58 || ':'
59 || 'WHEN OTHERS:'
60 || SQLERRM);
61 END IF;
62
63 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
64 END;
65
66 PROCEDURE gme_post_process_txns (
67 p_transaction_id IN NUMBER
68 ,x_return_status OUT NOCOPY VARCHAR2
69 ,x_message_data OUT NOCOPY VARCHAR2)
70 IS
71 l_api_name CONSTANT VARCHAR2 (30) := 'gme_post_process_txns';
72 l_return_status VARCHAR2 (1);
73
74 CURSOR get_txns (v_header_id IN NUMBER)
75 IS
76 SELECT *
77 FROM mtl_material_transactions
78 WHERE transaction_set_id = v_header_id;
79
80 l_txn_rec get_txns%ROWTYPE;
81 BEGIN
82 x_return_status := fnd_api.g_ret_sts_success;
83
84 IF (g_debug <= gme_debug.g_log_statement) THEN
85 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
86 || 'Entering');
87 gme_debug.put_line ( g_pkg_name
88 || '.'
89 || l_api_name
90 || ':'
91 || 'transaction_id'
92 || p_transaction_id);
93 END IF;
94
95 -- Bug 8841650 - Initialize user and timestamp just in case this is
96 -- called from an outside process like transact move order.
97 IF gme_common_pvt.g_user_ident IS NULL THEN
98 gme_common_pvt.set_who;
99 END IF;
100
101 gme_transactions_pvt.gme_post_process
102 (p_transaction_id => p_transaction_id
103 ,x_return_status => l_return_status);
104
105 IF l_return_status <> fnd_api.g_ret_sts_success THEN
106 RAISE fnd_api.g_exc_error;
107 END IF;
108
109 x_return_status := l_return_status;
110 IF (g_debug <= gme_debug.g_log_statement) THEN
111 gme_debug.put_line ( g_pkg_name
112 || '.'
113 || l_api_name
114 || ':'
115 || 'Exiting with '
116 || l_return_status);
117 END IF;
118 EXCEPTION
119 WHEN fnd_api.g_exc_error THEN
120 x_return_status := l_return_status;
121 WHEN fnd_api.g_exc_unexpected_error THEN
122 x_return_status := fnd_api.g_ret_sts_unexp_error;
123 WHEN OTHERS THEN
124 x_return_status := fnd_api.g_ret_sts_unexp_error;
125
126 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
127 gme_debug.put_line ( g_pkg_name
128 || '.'
129 || l_api_name
130 || ':'
131 || 'WHEN OTHERS:'
132 || SQLERRM);
133 END IF;
134
135 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
136 END gme_post_process_txns;
137
138 PROCEDURE update_material_date (
139 p_material_detail_id IN NUMBER
140 ,p_material_date IN DATE
141 ,x_return_status OUT NOCOPY VARCHAR2)
142 IS
143 BEGIN
144
145 gme_common_pvt.set_who ;
146 gme_common_pvt.material_date_change
147 (p_material_detail_id => p_material_detail_id
148 ,p_material_date => p_material_date
149 ,x_return_status => x_return_status);
150 /* FPBug#4585491
151 this procedure may return R, B or M depends on whether reservations deleted
152 or MO allocations deleted or both
153 */
154 IF x_return_status in ('R','B','M') THEN
155 x_return_status := fnd_api.g_ret_sts_success;
156 END IF;
157 END update_material_date;
158 /*Bug#6778968 Added the new parameter, p_called_by */
159 PROCEDURE validate_supply_demand
160 ( x_return_status OUT NOCOPY VARCHAR2
161 , x_msg_count OUT NOCOPY NUMBER
162 , x_msg_data OUT NOCOPY VARCHAR2
163 , x_valid_status OUT NOCOPY VARCHAR2
164 , p_organization_id IN NUMBER
165 , p_item_id IN NUMBER
166 , p_supply_demand_code IN NUMBER
167 , p_supply_demand_type_id IN NUMBER
168 , p_supply_demand_header_id IN NUMBER
169 , p_supply_demand_line_id IN NUMBER
170 , p_supply_demand_line_detail IN NUMBER DEFAULT FND_API.G_MISS_NUM
171 , p_demand_ship_date IN DATE
172 , p_expected_receipt_date IN DATE
173 , p_called_by IN VARCHAR2 DEFAULT 'VAL'
174 , p_api_version_number IN NUMBER DEFAULT 1.0
175 , p_init_msg_lst IN VARCHAR2 DEFAULT FND_API.G_FALSE
176 ) IS
177 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_SUPPLY_DEMAND';
178
179 l_material_details_rec GME_MATERIAL_DETAILS%ROWTYPE;
180 l_batch_header_rec GME_BATCH_HEADER%ROWTYPE;
181 l_step_status NUMBER;
182 l_mat_status NUMBER;
183 l_step_id NUMBER;
184
185 invalid_version EXCEPTION;
186 input_param_missing EXCEPTION;
187 validation_error EXCEPTION;
188 fetch_failure EXCEPTION;
189 supply_demand_error EXCEPTION;
190
191 BEGIN
192
193 IF g_debug <= gme_debug.g_log_procedure THEN
194 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
195 END IF;
196
197 /* Set the return status to success initially */
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199
200
201 /* Set the valid status to YES initially */
202 x_valid_status := 'Y';
203
204 IF p_init_msg_lst = FND_API.G_TRUE THEN
205 fnd_msg_pub.initialize;
206 END IF;
207
208 IF NOT FND_API.compatible_api_call(1.0, p_api_version_number, 'validate_supply_demand', g_pkg_name ) THEN
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 RAISE invalid_version;
211 END IF;
212
213 /* Ensure mandatory inputs supplied */
214 IF g_debug <= gme_debug.g_log_statement THEN
215 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' validate for mandatory input parameters ');
216 END IF;
217 IF p_organization_id IS NULL THEN
218 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'ORGANIZATION_ID');
219 RAISE input_param_missing;
220 ELSIF p_item_id IS NULL THEN
221 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'ITEM_ID');
222 RAISE input_param_missing;
223 ELSIF p_supply_demand_code IS NULL THEN
224 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'SUPPLY_DEMAND_CODE');
225 RAISE input_param_missing;
226 ELSIF p_supply_demand_type_id IS NULL THEN
227 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'SUPPLY_DEMAND_TYPE_ID');
228 RAISE input_param_missing;
229 ELSIF p_supply_demand_header_id IS NULL THEN
230 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'SUPPLY_DEMAND_HEADER_ID');
231 RAISE input_param_missing;
232 ELSIF p_supply_demand_line_id IS NULL THEN
233 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'SUPPLY_DEMAND_LINE_ID');
234 RAISE input_param_missing;
235 END IF;
236
237 /* Retrieve batch header row */
238 l_batch_header_rec.batch_id := p_supply_demand_header_id;
239 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec, l_batch_header_rec)) THEN
240 IF g_debug <= gme_debug.g_log_statement THEN
241 gme_debug.put_line(g_pkg_name||'.'||l_api_name||
242 'Fetch failure against gme_batch_header using id of '||p_supply_demand_header_id);
243 END IF;
244 RAISE fetch_failure;
245 END IF;
246 IF g_debug <= gme_debug.g_log_statement THEN
247 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch Status '||l_batch_header_rec.batch_status);
248 END IF;
249
250 /* Verify that update_inventory is allowed for the Batch */
251 IF l_batch_header_rec.update_inventory_ind <> 'Y' THEN
252 gme_common_pvt.log_message('GME_INVENTORY_UPDATE_BLOCKED');
253 RAISE validation_error;
254 END IF;
255
256 /* Verify that Batch is in either Pending or WIP status */
257 IF l_batch_header_rec.batch_status NOT IN (gme_common_pvt.g_batch_pending,
258 gme_common_pvt.g_batch_wip) THEN
259 gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS', 'PROCESS', 'RESERVATION');
260 RAISE validation_error;
261 END IF;
262
263 /* Verify that Batch is WIP status for conversion api calls. */
264 IF (l_batch_header_rec.batch_status <> gme_common_pvt.g_batch_wip AND
265 p_called_by = 'CVT') THEN
266 gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS', 'CONVERT', 'RESERVATION');
267 RAISE validation_error;
268 END IF;
269
270 /* Reservation OR conversion not permitted for FPOs */
271 IF l_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
272 gme_common_pvt.log_message('GME_FPO_RESERVATION_ERROR');
273 RAISE validation_error;
274 END IF;
275
276 /* Retrieve material details record */
277 l_material_details_rec.material_detail_id := p_supply_demand_line_id;
278 IF (NOT(gme_material_details_dbl.fetch_row(l_material_details_rec, l_material_details_rec))) THEN
279 IF g_debug <= gme_debug.g_log_statement THEN
280 gme_debug.put_line(g_pkg_name||'.'||l_api_name||
281 'Fetch failure against gme_material_details using id of '||p_supply_demand_line_id);
282 END IF;
283 RAISE fetch_failure;
284 END IF;
285
286 /* Verify that the supplied organization_id and item are consistent with the material details row */
287 IF p_organization_id <> l_material_details_rec.organization_id THEN
288 --Bug#5439736 replaced the following message
289 gme_common_pvt.log_message('GME_INCONSISTENT_FIELD','FIELD_NAME','ORGANIZATION_ID');
290 RAISE validation_error;
291 ELSIF p_item_id <> l_material_details_rec.inventory_item_id THEN
292 --Bug#5439736
293 gme_common_pvt.log_message('GME_INCONSISTENT_FIELD','FIELD_NAME','INVENTORY_ITEM_ID');
294 RAISE validation_error;
295 END IF;
296
297 /* Verify that Reservation is not for phantom ingredient */
298 IF l_material_details_rec.phantom_type IN (1,2) THEN
299 gme_common_pvt.log_message('GME_INVALID_RSV_FOR_PHANTOM');
300 RAISE validation_error;
301 END IF;
302
303 /* Verify that Reservation is not for sample by-product */
304 IF l_material_details_rec.line_type = gme_common_pvt.g_line_type_byprod AND
305 l_material_details_rec.by_product_type = 'Y' THEN
306 gme_common_pvt.log_message('GME_INVALID_RSV_FOR_BYPROD');
307 RAISE validation_error;
308 END IF;
309
310 IF g_debug <= gme_debug.g_log_statement THEN
311 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Material Release Type '||l_material_details_rec.release_type);
312 END IF;
313 /* Verify p_supply_demand_code and line_type. */
314 IF p_supply_demand_code = 2 /* Demand */ AND
315 l_material_details_rec.line_type <> gme_common_pvt.g_line_type_ing THEN
316 gme_common_pvt.log_message('GME_INVALID_DEMAND_LINE');
317 RAISE supply_demand_error;
318 ELSIF p_supply_demand_code = 1 /* Supply */ AND
319 l_material_details_rec.line_type <> gme_common_pvt.g_line_type_prod THEN
320 gme_common_pvt.log_message('GME_INVALID_SUPPLY_LINE');
321 RAISE supply_demand_error;
322 ELSIF NVL(p_supply_demand_code,0) NOT IN (1,2) THEN
323 gme_common_pvt.log_message('GME_INVALID_SUPPLY_DEMAND');
324 RAISE supply_demand_error;
325 ELSIF p_supply_demand_code = 2 /* Demand */ AND
326 l_material_details_rec.line_type = gme_common_pvt.g_line_type_ing THEN
327 IF l_material_details_rec.release_type NOT IN ( gme_common_pvt.g_mtl_manual_release,gme_common_pvt.g_mtl_incremental_release) THEN
328 l_mat_status := gme_common_pvt.is_material_auto_release(l_material_details_rec.material_detail_id);
329 ELSE
330 l_mat_status := l_material_details_rec.release_type;
331 END IF;
332 --Bug#4604943 following code is commented out
333 /* BUG 4604943 BEGIN - check for auto AND autobystep here
334 IF l_mat_status in ( gme_common_pvt.g_mtl_auto_release ,gme_common_pvt.g_mtl_autobystep_release) AND
335 l_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip THEN
336 IF g_debug <= gme_debug.g_log_statement THEN
337 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Automatic Release Material in WIP batch not a valid demand');
338 END IF;
339 gme_common_pvt.log_message('GME_INVALID_DEMAND_LINE');
340 RAISE supply_demand_error;
341 BUG 4604943 END
342 ELS */
343 IF l_mat_status = gme_common_pvt.g_mtl_auto_release AND
344 l_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip THEN
345 IF p_called_by <> 'CVT' THEN
346 IF g_debug <= gme_debug.g_log_statement THEN
347 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Automatic Release Material in WIP batch not a valid demand');
348 END IF;
349 gme_common_pvt.log_message('GME_INVALID_DEMAND_LINE');
350 RAISE supply_demand_error;
351 END IF;
352 ELSIF l_mat_status = gme_common_pvt.g_mtl_autobystep_release THEN
353 IF NOT gme_common_pvt.get_assoc_step(l_material_details_rec.material_detail_id,l_step_id,l_step_status) THEN
354 IF g_debug <= gme_debug.g_log_statement THEN
355 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Error in get_assoc_step');
356 END IF;
357 gme_common_pvt.log_message('GME_INVALID_DEMAND_LINE');
358 RAISE supply_demand_error;
359 ELSIF l_step_id IS NOT NULL THEN
360 /* for conversions of reservations, the step status should not be pending and
361 for creating the reservations, the step status should be greater than pending */
362 IF (p_called_by = 'CVT' and NVL(l_step_status,-1) <> gme_common_pvt.g_step_wip)
363 OR (p_called_by <> 'CVT' and NVL(l_step_status,-1) >= gme_common_pvt.g_step_wip) THEN
364 IF g_debug <= gme_debug.g_log_statement THEN
365 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Automatic By Step Material in step: '||l_step_id||
366 ' with status of '||l_step_status||' not a valid demand');
367 END IF;
368 gme_common_pvt.log_message('GME_INVALID_DEMAND_LINE');
369 RAISE supply_demand_error;
370 END IF;
371 END IF; -- IF l_step_id IS NOT NULL AND NVL(l_step_status,-1) = gme_common_pvt.g_step_wip THEN
372 END IF; -- IF l_mat_status = gme_common_pvt.g_mtl_auto_release AND
373 END IF;
374
375 IF g_debug <= gme_debug.g_log_procedure THEN
376 gme_debug.put_line ('Completed '|| l_api_name|| ' at '|| TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
377 END IF;
378 EXCEPTION
379 /* Exception handling */
380 WHEN invalid_version OR input_param_missing OR validation_error
381 OR fetch_failure OR supply_demand_error THEN
382 x_return_status := FND_API.g_ret_sts_error;
383 x_valid_status := 'N';
384 gme_common_pvt.count_and_get(x_count => x_msg_count,
385 p_encoded => FND_API.g_false,
386 x_data => x_msg_data);
387 WHEN OTHERS THEN
388 IF g_debug <= gme_debug.g_log_unexpected THEN
389 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'When others exception:'|| SQLERRM);
390 END IF;
391 x_return_status := FND_API.g_ret_sts_unexp_error;
392 x_valid_status := 'N';
393 gme_common_pvt.count_and_get(x_count => x_msg_count,
394 p_encoded => FND_API.g_false,
395 x_data => x_msg_data);
396 END validate_supply_demand;
397
398 PROCEDURE get_available_supply_demand
399 ( x_return_status OUT NOCOPY VARCHAR2
400 , x_msg_count OUT NOCOPY NUMBER
401 , x_msg_data OUT NOCOPY VARCHAR2
402 , x_available_quantity OUT NOCOPY NUMBER
403 , x_source_uom_code OUT NOCOPY VARCHAR2
404 , x_source_primary_uom_code OUT NOCOPY VARCHAR2
405 , p_organization_id IN NUMBER DEFAULT NULL
406 , p_item_id IN NUMBER DEFAULT NULL
407 , p_revision IN VARCHAR2 DEFAULT NULL
408 , p_lot_number IN VARCHAR2 DEFAULT NULL
409 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
410 , p_locator_id IN NUMBER DEFAULT NULL
411 , p_supply_demand_code IN NUMBER
412 , p_supply_demand_type_id IN NUMBER
413 , p_supply_demand_header_id IN NUMBER
414 , p_supply_demand_line_id IN NUMBER
415 , p_supply_demand_line_detail IN NUMBER DEFAULT FND_API.G_MISS_NUM
416 , p_lpn_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
417 , p_project_id IN NUMBER DEFAULT NULL
418 , p_task_id IN NUMBER DEFAULT NULL
419 , p_api_version_number IN NUMBER DEFAULT 1.0
420 , p_init_msg_lst IN VARCHAR2 DEFAULT FND_API.G_FALSE
421 ) IS
422 l_api_name CONSTANT VARCHAR2 (30) := 'GET_AVAILABLE_SUPPLY_DEMAND';
423
424 l_material_details_rec GME_MATERIAL_DETAILS%ROWTYPE;
425 l_batch_header_rec GME_BATCH_HEADER%ROWTYPE;
426 l_step_status NUMBER(5);
427 l_primary_uom_code VARCHAR2(3);
428 l_available_quantity NUMBER;
429
430 CURSOR cur_get_step_status (v_material_detail_id NUMBER) IS
431 SELECT step_status
432 FROM gme_batch_steps s,
433 gme_batch_step_items i
434 WHERE s.batchstep_id = i.batchstep_id
435 AND i.material_detail_id = v_material_detail_id;
436
437 CURSOR cur_item(v_org_id NUMBER, v_inventory_item_id NUMBER) IS
438 SELECT primary_uom_code
439 FROM mtl_system_items_b
440 WHERE organization_id = V_org_id
441 AND inventory_item_id = V_inventory_item_id;
442
443
444 invalid_version EXCEPTION;
445 input_param_missing EXCEPTION;
446 validation_error EXCEPTION;
447 fetch_failure EXCEPTION;
448
449 BEGIN
450
451 IF g_debug <= gme_debug.g_log_procedure THEN
452 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
453 END IF;
454
455 /* Set the return status to success initially */
456 x_return_status := FND_API.G_RET_STS_SUCCESS;
457
458 IF p_init_msg_lst = FND_API.G_TRUE THEN
459 fnd_msg_pub.initialize;
460 END IF;
461
462 IF NOT FND_API.compatible_api_call(1.0, p_api_version_number, 'validate_supply_demand', g_pkg_name ) THEN
463 x_return_status := FND_API.G_RET_STS_ERROR;
464 RAISE invalid_version;
465 END IF;
466
467 /* Ensure mandatory inputs supplied */
468 IF g_debug <= gme_debug.g_log_statement THEN
469 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' validate for mandatory input parameters ');
470 END IF;
471 IF p_supply_demand_code IS NULL THEN
472 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'SUPPLY_DEMAND_CODE');
473 RAISE input_param_missing;
474 ELSIF p_supply_demand_type_id IS NULL THEN
475 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'SUPPLY_DEMAND_TYPE_ID');
476 RAISE input_param_missing;
477 ELSIF p_supply_demand_header_id IS NULL THEN
478 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'SUPPLY_DEMAND_HEADER_ID');
479 RAISE input_param_missing;
480 ELSIF p_supply_demand_line_id IS NULL THEN
481 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'SUPPLY_DEMAND_LINE_ID');
482 RAISE input_param_missing;
483 END IF;
484
485 /* Retrieve batch header */
486 l_batch_header_rec.batch_id := p_supply_demand_header_id;
487 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec, l_batch_header_rec)) THEN
488 IF g_debug <= gme_debug.g_log_statement THEN
489 gme_debug.put_line(g_pkg_name||'.'||l_api_name||
490 'Fetch failure against gme_batch_header using id of '||p_supply_demand_header_id);
491 END IF;
492 RAISE fetch_failure;
493 END IF;
494
495 /* For Batch in Completed status , return 0*/
496 IF l_batch_header_rec.batch_status = gme_common_pvt.g_batch_completed THEN
497 x_available_quantity := 0;
498 IF g_debug <= gme_debug.g_log_statement THEN
499 gme_debug.put_line(g_pkg_name||'.'||l_api_name||
500 'Batch status is completed so return available of 0 ');
501 END IF;
502 RETURN;
503 END IF;
504
505 /* Retrieve material details record */
506 l_material_details_rec.material_detail_id := p_supply_demand_line_id;
507 IF (NOT(gme_material_details_dbl.fetch_row(l_material_details_rec, l_material_details_rec))) THEN
508 IF g_debug <= gme_debug.g_log_statement THEN
509 gme_debug.put_line(g_pkg_name||'.'||l_api_name||
510 'Fetch failure against gme_material_details using id of '||p_supply_demand_line_id);
511 END IF;
512 RAISE fetch_failure;
513 END IF;
514
515 /* IF line is associated to step and step is completed then return Zero */
516 OPEN cur_get_step_status (l_material_details_rec.material_detail_id);
517 FETCH cur_get_step_status INTO l_step_status;
518 CLOSE cur_get_step_status;
519
520 -- Bug 10182779 -- Allow access for materials associated to steps for completed
521 -- steps if they are not autobystep. Also added check for closed steps.
522 IF (NVL(l_step_status, 0) = gme_common_pvt.g_step_completed AND l_material_details_rec.release_type = 3) OR
523 NVL(l_step_status, 0) = gme_common_pvt.g_step_closed THEN
524 x_available_quantity := 0;
525 RETURN;
526 END IF;
527
528 /* If supplied,verify that the supplied organization_id and item are consistent with the material details row */
529 IF p_organization_id is NOT NULL THEN
530 IF p_organization_id <> l_material_details_rec.organization_id THEN
531 gme_common_pvt.log_message('GME_INCONSISTENT_FIELD','FIELD_NAME','ORGANIZATION_ID');
532 RAISE validation_error;
533 END IF;
534 END IF;
535
536 IF p_item_id is NOT NULL THEN
537 IF p_item_id <> l_material_details_rec.inventory_item_id THEN
538 gme_common_pvt.log_message('GME_INCONSISTENT_FIELD','FIELD_NAME','INVENTORY_ITEM_ID');
539 RAISE validation_error;
540 END IF;
541 END IF;
542
543 /* Now retrieve the primary UOM code for the item */
544 OPEN cur_item (l_material_details_rec.organization_id,l_material_details_rec.inventory_item_id);
545 FETCH cur_item INTO l_primary_uom_code;
546 CLOSE cur_item;
547 IF g_debug <= gme_debug.g_log_statement THEN
548 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' primary uom code '||l_primary_uom_code);
549 END IF;
550
551 /* Return the uom code and primary uom code */
552 x_source_uom_code := l_material_details_rec.dtl_um;
553 x_source_primary_uom_code := l_primary_uom_code;
554
555 /* Return the available supply demand quantity.*/
556 /* The value will be same for p_supply_demand_code as Demand or Supply */
557 IF (g_debug <= gme_debug.g_log_unexpected) THEN
558 gme_debug.put_line ( g_pkg_name
559 || '.'
560 || l_api_name
561 || ':'
562 || 'Compute available qty from these figues:'
563 || ' wip_plan_qty => '
564 || l_material_details_rec.wip_plan_qty
565 || ' plan_qty => '
566 || l_material_details_rec.plan_qty
567 || ' actual_qty => '
568 || l_material_details_rec.actual_qty);
569 END IF;
570 IF p_supply_demand_code = 2 THEN
571 x_available_quantity := 1000000000000;
572 ELSE
573 l_available_quantity := NVL(l_material_details_rec.wip_plan_qty, l_material_details_rec.plan_qty) - l_material_details_rec.actual_qty;
574 -- To conform to INV standards, round to 5 decimal places
575 x_available_quantity := ROUND(l_available_quantity,5);
576 END IF;
577 IF g_debug <= gme_debug.g_log_procedure THEN
578 gme_debug.put_line ('Available quantity computes as '|| x_available_quantity);
579 gme_debug.put_line ('Completed '|| l_api_name|| ' at '|| TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
580 END IF;
581 RETURN;
582 EXCEPTION
583 /* Exception handling */
584 WHEN invalid_version OR input_param_missing OR validation_error OR fetch_failure THEN
585 x_return_status := FND_API.g_ret_sts_error;
586 gme_common_pvt.count_and_get(x_count => x_msg_count,
587 p_encoded => FND_API.g_false,
588 x_data => x_msg_data);
589 WHEN OTHERS THEN
590 IF g_debug <= gme_debug.g_log_unexpected THEN
591 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'When others exception:'|| SQLERRM);
592 END IF;
593 x_return_status := FND_API.g_ret_sts_unexp_error;
594 gme_common_pvt.count_and_get(x_count => x_msg_count,
595 p_encoded => FND_API.g_false,
596 x_data => x_msg_data);
597 END get_available_supply_demand;
598
599 PROCEDURE update_step_quality_status (
600 p_batchstep_id IN NUMBER
601 ,p_org_id IN NUMBER
602 ,p_quality_status IN NUMBER
603 ,x_return_status OUT NOCOPY VARCHAR2)
604 IS
605 l_batch_step gme_batch_steps%ROWTYPE;
606 expected_err EXCEPTION;
607 BEGIN
608 IF NOT gme_common_pvt.g_setup_done THEN
609 gme_common_pvt.g_setup_done := gme_common_pvt.setup (p_org_id);
610
611 IF NOT gme_common_pvt.g_setup_done THEN
612 x_return_status := fnd_api.g_ret_sts_error;
613 RAISE expected_err;
614 END IF;
615 END IF;
616
617 gme_common_pvt.set_timestamp;
618 l_batch_step.batchstep_id := p_batchstep_id;
619
620 IF (NOT (gme_batch_steps_dbl.fetch_row (l_batch_step, l_batch_step) ) ) THEN
621 RAISE expected_err;
622 END IF;
623
624 IF ( p_quality_status IS NULL
625 OR p_quality_status < 1
626 OR p_quality_status > 6) THEN
627 gme_common_pvt.log_message ('GME_INV_STEP_QUALITY_STATUS');
628 RAISE expected_err;
629 END IF;
630
631 IF (l_batch_step.step_status > 2) THEN
632 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
633 RAISE expected_err;
634 END IF;
635
636 IF (l_batch_step.step_status = 1 AND p_quality_status > 2)
637 OR (l_batch_step.step_status = 2 AND p_quality_status <= 2) THEN
638 gme_common_pvt.log_message ('GME_INV_STEP_STATUS_QUALITY');
639 RAISE expected_err;
640 END IF;
641
642 l_batch_step.quality_status := p_quality_status;
643
644 IF (NOT (gme_batch_steps_dbl.update_row (l_batch_step) ) ) THEN
645 RAISE expected_err;
646 END IF;
647 EXCEPTION
648 WHEN expected_err THEN
649 x_return_status := fnd_api.g_ret_sts_error;
650 WHEN OTHERS THEN
651 fnd_msg_pub.add_exc_msg ('GME_API_GRP'
652 ,'UPDATE_STEP_QUALITY_STATUS');
653 x_return_status := fnd_api.g_ret_sts_unexp_error;
654 END update_step_quality_status;
655
656
657 PROCEDURE get_batch_shortages (
658 p_api_version_number IN NUMBER DEFAULT 1.0
659 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
660 ,x_msg_count OUT NOCOPY NUMBER
661 ,x_msg_data OUT NOCOPY VARCHAR2
662 ,p_organization_id IN NUMBER
663 ,p_batch_id IN NUMBER
664 ,p_invoke_mode IN VARCHAR2
665 ,p_tree_mode IN NUMBER
666 ,x_return_status OUT NOCOPY VARCHAR2
667 ,x_exception_tbl OUT NOCOPY gme_common_pvt.exceptions_tab)
668 IS
669 l_api_name CONSTANT VARCHAR2 (30) := 'get_batch_shortages';
670 BEGIN
671 -- Initially let us assign the return status to success
672 x_return_status := fnd_api.g_ret_sts_success;
673
674 IF (g_debug <= gme_debug.g_log_statement) THEN
675 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
676 || 'Entering');
677 gme_debug.put_line ( g_pkg_name
678 || '.'
679 || l_api_name
680 || ':'
681 || 'batch_id is :'
682 || p_batch_id);
683 END IF;
684
685 IF p_batch_id IS NOT NULL THEN
686 gme_common_pvt.get_batch_shortages (
687 p_organization_id => p_organization_id
688 ,p_batch_id => p_batch_id
689 ,p_invoke_mode => p_invoke_mode
690 ,p_tree_mode => p_tree_mode
691 ,x_return_status => x_return_status
692 ,x_exception_tbl => x_exception_tbl);
693
694 IF x_return_status <> fnd_api.g_ret_sts_success THEN
695 RAISE fnd_api.g_exc_error;
696 END IF;
697 END IF;
698
699 IF (g_debug <= gme_debug.g_log_statement) THEN
700 gme_debug.put_line ( g_pkg_name
701 || '.'
702 || l_api_name
703 || ':'
704 || 'Exiting with '
705 || x_return_status);
706 END IF;
707 EXCEPTION
708 WHEN fnd_api.g_exc_error THEN
709 gme_common_pvt.count_and_get (x_count => x_msg_count
710 ,p_encoded => fnd_api.g_false
711 ,x_data => x_msg_data);
712
713 WHEN OTHERS THEN
714 x_return_status := fnd_api.g_ret_sts_unexp_error;
715
716 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
717 gme_debug.put_line ( g_pkg_name
718 || '.'
719 || l_api_name
720 || ':'
721 || 'WHEN OTHERS:'
722 || SQLERRM);
723 END IF;
724 gme_common_pvt.count_and_get (x_count => x_msg_count
725 ,p_encoded => fnd_api.g_false
726 ,x_data => x_msg_data);
727 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
728 END;
729 PROCEDURE get_material_reservations (
730 p_api_version_number IN NUMBER DEFAULT 1.0
731 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
732 ,x_msg_count OUT NOCOPY NUMBER
733 ,x_msg_data OUT NOCOPY VARCHAR2
734 ,p_organization_id IN NUMBER
735 ,p_batch_id IN NUMBER
736 ,p_material_detail_id IN NUMBER
737 ,x_return_status OUT NOCOPY VARCHAR2
738 ,x_reservations_tbl OUT NOCOPY gme_common_pvt.reservations_tab)
739
740 IS
741 l_api_name CONSTANT VARCHAR2 (30) := 'get_material_reservations';
742 BEGIN
743 -- Initially let us assign the return status to success
744 x_return_status := fnd_api.g_ret_sts_success;
745
746 IF (g_debug <= gme_debug.g_log_statement) THEN
747 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
748 || 'Entering');
749 gme_debug.put_line ( g_pkg_name
750 || '.'
751 || l_api_name
752 || ':'
753 || 'batch_id is :'
754 || p_batch_id);
755 END IF;
756
757 IF p_batch_id IS NOT NULL THEN
758 gme_reservations_pvt.get_material_reservations (
759 p_organization_id => p_organization_id
760 ,p_batch_id => p_batch_id
761 ,p_material_detail_id => p_material_detail_id
762 ,x_return_status => x_return_status
763 ,x_reservations_tbl => x_reservations_tbl);
764
765 IF x_return_status <> fnd_api.g_ret_sts_success THEN
766 RAISE fnd_api.g_exc_error;
767 END IF;
768 END IF;
769
770 IF (g_debug <= gme_debug.g_log_statement) THEN
771 gme_debug.put_line ( g_pkg_name
772 || '.'
773 || l_api_name
774 || ':'
775 || 'Exiting with '
776 || x_return_status);
777 END IF;
778 EXCEPTION
779 WHEN fnd_api.g_exc_error THEN
780 gme_common_pvt.count_and_get (x_count => x_msg_count
781 ,p_encoded => fnd_api.g_false
782 ,x_data => x_msg_data);
783
784 WHEN OTHERS THEN
785 x_return_status := fnd_api.g_ret_sts_unexp_error;
786
787 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
788 gme_debug.put_line ( g_pkg_name
789 || '.'
790 || l_api_name
791 || ':'
792 || 'WHEN OTHERS:'
793 || SQLERRM);
794 END IF;
795 gme_common_pvt.count_and_get (x_count => x_msg_count
796 ,p_encoded => fnd_api.g_false
797 ,x_data => x_msg_data);
798 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
799 END get_material_reservations;
800
801 PROCEDURE create_lcf_batch (
802 p_api_version IN NUMBER DEFAULT 1.0
803 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
804 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
805 ,x_message_count OUT NOCOPY NUMBER
806 ,x_message_list OUT NOCOPY VARCHAR2
807 ,x_return_status OUT NOCOPY VARCHAR2
808 ,p_batch_header_rec IN gme_batch_header%rowtype
809 ,p_formula_dtl_tbl IN gmdfmval_pub.formula_detail_tbl
810 ,p_recipe_rout_tbl IN gmd_recipe_fetch_pub.recipe_rout_tbl
811 ,p_recipe_step_tbl IN gmd_recipe_fetch_pub.recipe_step_tbl
812 ,p_routing_depd_tbl IN gmd_recipe_fetch_pub.routing_depd_tbl
813 ,p_oprn_act_tbl IN gmd_recipe_fetch_pub.oprn_act_tbl
814 ,p_oprn_resc_tbl IN gmd_recipe_fetch_pub.oprn_resc_tbl
815 ,p_proc_param_tbl IN gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
816 ,p_use_workday_cal IN VARCHAR2 DEFAULT FND_API.G_TRUE
817 ,p_contiguity_override IN VARCHAR2 DEFAULT FND_API.G_TRUE
818 ,x_batch_header_rec OUT NOCOPY gme_batch_header%rowtype
819 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
820 ) IS
821
822 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_LCF_BATCH';
823 BEGIN
824 IF g_debug <= gme_debug.g_log_procedure THEN
825 gme_debug.log_initialize('CreateLCFBatch');
826 END IF;
827
828 IF g_debug <= gme_debug.g_log_procedure THEN
829 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
830 || l_api_name);
831 END IF;
832 IF p_init_msg_list = fnd_api.g_true THEN
833 fnd_msg_pub.initialize;
834 gme_common_pvt.g_error_count := 0;
835 END IF;
836 /* Make sure we are call compatible */
837 IF NOT fnd_api.compatible_api_call (1
838 ,p_api_version
839 ,'create_lcf_batch'
840 ,g_pkg_name) THEN
841 x_return_status := fnd_api.g_ret_sts_error;
842 gme_common_pvt.log_message ('GME_INVALID_API_VERSION');
843 RAISE fnd_api.g_exc_error;
844 END IF;
845 /* Setup the common constants used accross the apis */
846 IF g_debug <= gme_debug.g_log_procedure THEN
847 gme_debug.put_line ('Calling gme_common_pvt.setup.');
848 END IF;
849
850 gme_common_pvt.g_setup_done :=
851 gme_common_pvt.setup (p_org_id => p_batch_header_rec.organization_id
852 ,p_org_code => NULL);
853
854 IF g_debug <= gme_debug.g_log_procedure THEN
855 gme_debug.put_line ('After calling gme_common_pvt.setup.');
856 END IF;
857
858 IF NOT gme_common_pvt.g_setup_done THEN
859 x_return_status := fnd_api.g_ret_sts_error;
860 RAISE fnd_api.g_exc_error;
861 END IF;
862 gme_common_pvt.set_timestamp;
863 gme_common_pvt.materials := p_formula_dtl_tbl;
864 gme_common_pvt.routings := p_recipe_rout_tbl;
865 gme_common_pvt.steps := p_recipe_step_tbl;
866 gme_common_pvt.step_dependencies := p_routing_depd_tbl;
867 gme_common_pvt.activities := p_oprn_act_tbl;
868 gme_common_pvt.resources := p_oprn_resc_tbl;
869 gme_common_pvt.process_parameters := p_proc_param_tbl;
870 gme_create_batch_pvt.create_batch(
871 p_validation_level => 100
872 ,x_return_status => x_return_status
873 ,p_batch_header_rec => p_batch_header_rec
874 ,x_batch_header_rec => x_batch_header_rec
875 ,p_batch_size => p_formula_dtl_tbl(1).qty
876 ,p_batch_size_uom => p_formula_dtl_tbl(1).detail_uom
877 ,p_creation_mode => 'LCF'
878 ,p_ignore_qty_below_cap => FND_API.G_TRUE
879 ,p_use_workday_cal => p_use_workday_cal
880 ,p_contiguity_override => p_contiguity_override
881 ,p_is_phantom => 'N'
882 ,x_exception_material_tbl => x_exception_material_tbl
883 );
884 IF x_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, gme_common_pvt.g_inv_short_err,'C') THEN
885 IF g_debug <= gme_debug.g_log_procedure THEN
886 gme_debug.put_line ('Error in Create Batch: return status'||x_return_status);
887 END IF;
888 RAISE fnd_api.g_exc_error;
889 END IF;
890 IF g_debug <= gme_debug.g_log_procedure THEN
891 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
892 END IF;
893
894
895 EXCEPTION
896 WHEN fnd_api.g_exc_error THEN
897 x_return_status := fnd_api.g_ret_sts_error;
898 x_batch_header_rec := null;
899 gme_common_pvt.count_and_get (x_count => x_message_count
900 ,p_encoded => fnd_api.g_false
901 ,x_data => x_message_list);
902 WHEN fnd_api.g_exc_unexpected_error THEN
903 x_return_status := fnd_api.g_ret_sts_unexp_error;
904 x_batch_header_rec := null;
905 gme_common_pvt.count_and_get (x_count => x_message_count
906 ,p_encoded => fnd_api.g_false
907 ,x_data => x_message_list);
908 WHEN OTHERS THEN
909 x_return_status := fnd_api.g_ret_sts_unexp_error;
910 x_batch_header_rec := null;
911 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
912 gme_debug.put_line ( g_pkg_name
913 || '.'
914 || l_api_name
915 || ':'
916 || 'WHEN OTHERS:'
917 || SQLERRM);
918 END IF;
919
920 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
921
922 END create_lcf_batch;
923
924 FUNCTION get_planning_open_qty (
925 p_organization_id IN NUMBER
926 ,p_batch_id IN NUMBER
927 ,p_material_detail_id IN NUMBER
928 ,p_prim_plan_qty IN NUMBER
929 ,p_prim_wip_plan_qty IN NUMBER
930 ,p_prim_actual_qty IN NUMBER
931 ,p_prim_uom IN VARCHAR2)
932 RETURN NUMBER
933 IS
934 l_api_name CONSTANT VARCHAR2 (30) := 'get_planning_open_qty';
935 l_open_qty NUMBER := 0;
936 l_return_status VARCHAR2 (1);
937 l_mtl_dtl_rec gme_material_details%ROWTYPE;
938 BEGIN
939 IF g_debug <= gme_debug.g_log_procedure THEN
940 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
941 || l_api_name);
942 END IF;
943
944 l_mtl_dtl_rec.organization_id := p_organization_id;
945 l_mtl_dtl_rec.batch_id := p_batch_id;
946 l_mtl_dtl_rec.material_detail_id := p_material_detail_id;
947 l_mtl_dtl_rec.plan_qty := p_prim_plan_qty;
948 l_mtl_dtl_rec.wip_plan_qty := p_prim_wip_plan_qty;
949 l_mtl_dtl_rec.actual_qty := p_prim_actual_qty;
950 l_mtl_dtl_rec.dtl_um := p_prim_uom;
951 gme_common_pvt.get_open_qty (p_mtl_dtl_rec => l_mtl_dtl_rec
952 ,p_called_by => 'S'
953 ,x_open_qty => l_open_qty
954 ,x_return_status => l_return_status);
955
956 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
957 RETURN 0;
958 ELSE
959 RETURN l_open_qty;
960 END IF;
961 EXCEPTION
962 WHEN OTHERS THEN
963 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
964
965 IF g_debug <= gme_debug.g_log_unexpected THEN
966 gme_debug.put_line ( 'When others exception in '
967 || g_pkg_name
968 || '.'
969 || l_api_name
970 || ' Error is '
971 || SQLERRM);
972 END IF;
973
974 RETURN 0;
975 END get_planning_open_qty;
976
977 FUNCTION IS_RESERVATION_FULLY_SPECIFIED(p_reservation_id IN NUMBER )
978 RETURN NUMBER
979 IS
980 l_api_name CONSTANT VARCHAR2 (30) := 'Is_reservation_fully_specified';
981 l_reservation_type NUMBER := 0;
982 l_item_rec mtl_system_items_b%ROWTYPE;
983 l_rsv_rec mtl_reservations%ROWTYPE;
984 l_rsv_type NUMBER;
985 l_msg_count NUMBER;
986 l_msg_list VARCHAR2(32767);
987 l_return_status VARCHAR2 (10);
988 fetch_error EXCEPTION;
989 error_unexpected EXCEPTION;
990
991 CURSOR cur_rsv_rec (v_reservation_id NUMBER)
992 IS
993 SELECT *
994 FROM mtl_reservations
995 WHERE reservation_id = v_reservation_id;
996
997 CURSOR cur_fetch_item (v_org_id NUMBER, v_inventory_item_id NUMBER)
998 IS
999 SELECT *
1000 FROM mtl_system_items_b
1001 WHERE organization_id = v_org_id
1002 AND inventory_item_id = v_inventory_item_id;
1003 BEGIN
1004 IF g_debug <= gme_debug.g_log_procedure THEN
1005 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1006 || l_api_name);
1007 END IF;
1008 IF p_reservation_id IS NOT NULL THEN
1009 OPEN cur_rsv_rec (p_reservation_id);
1010 FETCH cur_rsv_rec
1011 INTO l_rsv_rec;
1012
1013 IF cur_rsv_rec%NOTFOUND THEN
1014 CLOSE cur_rsv_rec;
1015
1016 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1017 ,'TABLE_NAME'
1018 ,'MTL_RESERVATIONS');
1019
1020 IF g_debug <= gme_debug.g_log_statement THEN
1021 gme_debug.put_line
1022 ( g_pkg_name
1023 || '.'
1024 || l_api_name
1025 || ' Retrieval failure against mtl_reservations using id of '
1026 || p_reservation_id);
1027 END IF;
1028
1029 RAISE fetch_error;
1030 END IF;
1031 CLOSE cur_rsv_rec;
1032 END IF ;
1033
1034 OPEN cur_fetch_item (l_rsv_rec.organization_id
1035 ,l_rsv_rec.inventory_item_id);
1036
1037 FETCH cur_fetch_item
1038 INTO l_item_rec;
1039
1040 IF cur_fetch_item%NOTFOUND THEN
1041 CLOSE cur_fetch_item;
1042
1043 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1044 ,'TABLE_NAME'
1045 ,'MTL_SYSTEM_ITEMS');
1046
1047 IF g_debug <= gme_debug.g_log_statement THEN
1048 gme_debug.put_line
1049 ( g_pkg_name
1050 || '.'
1051 || l_api_name
1052 || ' Retrieval failure against mtl_system_items using id of '
1053 || l_rsv_rec.inventory_item_id);
1054 END IF;
1055
1056 RAISE fetch_error;
1057 END IF;
1058
1059 CLOSE cur_fetch_item;
1060 /* Bug 5441643 Added NVL condition for location control code*/
1061 l_rsv_type :=
1062 gme_reservations_pvt.reservation_fully_specified
1063 (p_reservation_rec => l_rsv_rec
1064 ,p_item_location_control => NVL(l_item_rec.location_control_code,1)
1065 ,p_item_restrict_locators => l_item_rec.restrict_locators_code);
1066 IF g_debug <= gme_debug.g_log_statement THEN
1067 gme_debug.put_line
1068 ( g_pkg_name
1069 || '.'
1070 || l_api_name
1071 || ' Return rsv_type from gme_reservations_pvt.reservation_fully_specified is '
1072 || TO_CHAR (l_rsv_type) );
1073 END IF;
1074
1075 IF l_rsv_type = -1 THEN
1076 gme_common_pvt.log_message ('GME_RSV_DETAIL_REQUIRED');
1077 RAISE error_unexpected;
1078 END IF;
1079
1080 IF l_rsv_type IN (0, 2) THEN
1081 RETURN 0;
1082 ELSE
1083 RETURN 1;
1084 END IF;
1085 EXCEPTION
1086 WHEN error_unexpected OR fetch_error THEN
1087 RETURN 0;
1088 gme_common_pvt.count_and_get (x_count => l_msg_count
1089 ,p_encoded => fnd_api.g_false
1090 ,x_data => l_msg_list);
1091
1092 WHEN OTHERS THEN
1093 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1094
1095 IF g_debug <= gme_debug.g_log_unexpected THEN
1096 gme_debug.put_line ( 'When others exception in '
1097 || g_pkg_name
1098 || '.'
1099 || l_api_name
1100 || ' Error is '
1101 || SQLERRM);
1102 END IF;
1103
1104 RETURN 0;
1105 END IS_RESERVATION_FULLY_SPECIFIED;
1106
1107 /*======================================================================
1108 -- PROCEDURE:
1109 -- substitute_ingredients
1110 --
1111 -- DESCRIPTION:
1112 -- Procedure to substitute ingredients for the passed item_no,
1113 -- org_id, from and to batch_no, start and end dates.
1114 --
1115 -- HISTORY:
1116 -- Sivakumar.G FPBug#4351032 08-DEC-2005
1117 -- gme_api_grp.log_message calls replaced by gme_common_pvt.log_message
1118 -- Namit Singhi FPBug#5674398 01-JAN-2007
1119 -- Modified for ingred sub enhancement FP
1120 -- G. Muratore 05-SEP-2008 Bug 7352169
1121 -- Do not call auto detail line during item substitution.
1122
1123 -- A. Mishra 09-Nov-2009 Bug 8820175
1124 Commenting the duplicate code to stop the "line" from appearing twice
1125 ======================================================================*/
1126 PROCEDURE substitute_ingredients (
1127 errbuf OUT NOCOPY VARCHAR2,
1128 retcode OUT NOCOPY VARCHAR2,
1129 p_org_id IN NUMBER,
1130 p_from_batch_no IN VARCHAR2,
1131 p_to_batch_no IN VARCHAR2,
1132 p_item_id IN NUMBER,
1133 p_start_date IN VARCHAR2,
1134 p_end_date IN VARCHAR2
1135 ) IS
1136 /* Bug 5212569 Removed * and selecting only required columns */
1137 CURSOR item_master_cursor (v_item_id NUMBER
1138 ,v_org_id NUMBER) IS
1139 SELECT concatenated_segments, mtl_transactions_enabled_flag, process_execution_enabled_flag,
1140 eng_item_flag, primary_uom_code, reservable_type
1141 FROM mtl_system_items_kfv
1142 WHERE inventory_item_id = v_item_id
1143 AND organization_id = v_org_id;
1144
1145 CURSOR get_ingredients (
1146 v_org_id IN NUMBER,
1147 v_item_id IN NUMBER,
1148 v_start_date IN DATE,
1149 v_end_date IN DATE,
1150 v_from_batch_no IN VARCHAR2,
1151 v_to_batch_no IN VARCHAR2
1152 ) IS
1153 SELECT m.material_detail_id, m.batch_id, h.batch_no, m.material_requirement_date, m.line_no,
1154 m.formulaline_id, m.plan_qty, m.dtl_um, m.scale_multiple, h.formula_id,
1155 m.inventory_item_id, m.move_order_line_id, i.concatenated_segments item_no, h.organization_id, h.batch_type
1156 FROM gme_material_details m, gme_batch_header h, mtl_system_items_kfv i
1157 WHERE m.batch_id = h.batch_id
1158 AND h.organization_id = v_org_id
1159 AND h.batch_status = 1
1160 AND m.material_requirement_date >= v_start_date
1161 AND m.material_requirement_date <= NVL (v_end_date, m.material_requirement_date)
1162 AND m.line_type = -1
1163 AND m.phantom_type = 0 -- Non phantom ingrdients
1164 AND m.inventory_item_id = i.inventory_item_id
1165 AND m.organization_id = i.organization_id
1166 AND (m.inventory_item_id = v_item_id OR v_item_id IS NULL)
1167 AND (h.batch_no >= v_from_batch_no OR v_from_batch_no is null)
1168 AND (h.batch_no <= v_to_batch_no OR v_to_batch_no is null)
1169 AND m.formulaline_id IS NOT NULL --only for the ingredients in the formula substitution happens
1170 ORDER BY m.material_requirement_date, m.batch_id, m.material_detail_id;
1171
1172 CURSOR c_batchsteps (p_material_detail_id IN NUMBER) IS
1173 SELECT b.*
1174 FROM gme_batch_step_items a, gme_batch_steps b
1175 WHERE a.batchstep_id = b.batchstep_id
1176 AND a.material_detail_id = p_material_detail_id;
1177
1178 CURSOR get_msg (v_msg_name IN VARCHAR2) IS
1179 SELECT substrb(message_text,1,50)
1180 FROM fnd_new_messages
1181 WHERE application_id = 553
1182 AND message_name = v_msg_name;
1183
1184 l_formula_tbl gmdfmval_pub.formula_detail_tbl;
1185 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
1186 l_material_details_tbl gme_common_pvt.material_details_tab;
1187 l_material_details_tbl_out gme_common_pvt.material_details_tab;
1188 l_material_detail_rec gme_material_details%ROWTYPE;
1189 l_return BOOLEAN;
1190 l_return_status VARCHAR2 (1);
1191 x_return_status VARCHAR2 (1);
1192 l_batch_header_rec gme_batch_header%ROWTYPE;
1193 l_old_item_rec item_master_cursor%ROWTYPE;
1194 l_new_item_rec item_master_cursor%ROWTYPE;
1195 l_batchstep_rec gme_batch_steps%ROWTYPE;
1196 x_batchstep_rec gme_batch_steps%ROWTYPE;
1197 l_rsc_trans_count NUMBER;
1198 l_temp_qty NUMBER;
1199 l_trans_loaded BOOLEAN DEFAULT FALSE;
1200 l_oneitem_success BOOLEAN DEFAULT FALSE;
1201 l_oneitem_error BOOLEAN DEFAULT FALSE;
1202 l_api_name VARCHAR2 (50) := 'substitute_ingredients';
1203 l_message_count NUMBER;
1204 l_message_list VARCHAR2 (2048);
1205 l_start_date DATE;
1206 l_end_date DATE;
1207 l_doc_str VARCHAR2(80);
1208 l_ingred_sub_date DATE; -- nsinghi bug#5674398
1209 setup_failure EXCEPTION;
1210 BEGIN
1211
1212 gme_common_pvt.g_setup_done :=
1213 gme_common_pvt.setup (p_org_id => p_org_id);
1214
1215 IF NOT gme_common_pvt.g_setup_done THEN
1216 x_return_status := fnd_api.g_ret_sts_error;
1217 RAISE setup_failure;
1218 END IF;
1219 --set the timestamp
1220 gme_common_pvt.set_timestamp;
1221
1222 IF (g_debug IS NOT NULL) THEN
1223 gme_debug.log_initialize ('IngredientSubstitution');
1224 END IF;
1225
1226 IF p_item_id IS NOT NULL THEN
1227 OPEN item_master_cursor (p_item_id, p_org_id);
1228 FETCH item_master_cursor INTO l_old_item_rec;
1229 CLOSE item_master_cursor;
1230 END IF;
1231
1232 --FPBug#4991508 hard codes strings are seeded for NLS complaint
1233 fnd_message.set_name('GME','GME_INPUT_PARAM');
1234 fnd_file.put (fnd_file.output,fnd_message.get );
1235 fnd_file.new_line (fnd_file.output, 1);
1236 fnd_message.set_name('GME','GME_ORG_ID');
1237 fnd_file.put (fnd_file.output, fnd_message.get || p_org_id);
1238 fnd_file.new_line (fnd_file.output, 1);
1239 fnd_message.set_name('GME','GME_OLD_ITEM');
1240 fnd_file.put (fnd_file.output, fnd_message.get|| l_old_item_rec.concatenated_segments);
1241 fnd_file.new_line (fnd_file.output, 1);
1242 l_start_date := TO_DATE (p_start_date, 'YYYY/MM/DD HH24:MI:SS');
1243 l_end_date := TO_DATE (p_end_date, 'YYYY/MM/DD HH24:MI:SS');
1244 fnd_message.set_name('GME','GME_DATE_RANGE');
1245 fnd_file.put (fnd_file.output,
1246 fnd_message.get
1247 || TO_CHAR (l_start_date, 'DD-MON-YYYY HH24:MI:SS')
1248 || ' <-> '
1249 || TO_CHAR (l_end_date, 'DD-MON-YYYY HH24:MI:SS')
1250 );
1251 --Bug#4533850
1252 fnd_file.new_line (fnd_file.output, 1);
1253 fnd_message.set_name('GME','GME_FROM_BATCH');
1254 fnd_file.put (fnd_file.output, fnd_message.get|| p_from_batch_no);
1255 fnd_file.new_line (fnd_file.output, 1);
1256 fnd_message.set_name('GME','GME_TO_BATCH');
1257 fnd_file.put (fnd_file.output, fnd_message.get|| p_to_batch_no);
1258 fnd_file.new_line (fnd_file.output, 2);
1259
1260 FOR rec IN get_ingredients (p_org_id, p_item_id, l_start_date, l_end_date,p_from_batch_no,p_to_batch_no) LOOP
1261 IF rec.batch_type = 0 THEN
1262 OPEN get_msg('GME_BATCH');
1263 FETCH get_msg INTO l_doc_str;
1264 CLOSE get_msg;
1265 ELSE
1266 OPEN get_msg('GME_FIRM_PLAN_ORDER');
1267 FETCH get_msg INTO l_doc_str;
1268 CLOSE get_msg;
1269 END IF;
1270
1271 -- nsinghi bug#5674398. Pass the ingredient substitution date rather than the material requirement date
1272 l_ingred_sub_date := gme_api_grp.get_ingr_sub_date(rec.batch_id,rec.material_detail_id);
1273
1274 /* Though this call returns a table, we will be looking at the 1st record of the table */
1275 gmdfmval_pub.get_substitute_line_item (pformulaline_id => rec.formulaline_id,
1276 pitem_id => rec.inventory_item_id,
1277 pqty => rec.plan_qty,
1278 puom => rec.dtl_um,
1279 pscale_multiple => rec.scale_multiple,
1280 pdate => l_ingred_sub_date,
1281 xformuladetail_tbl => l_formula_tbl
1282 );
1283
1284 IF l_formula_tbl.COUNT = 0 THEN
1285 GOTO NEXT_RECORD; --GO to next record
1286 END IF;
1287
1288 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1289 gme_debug.put_line (' I/P formulaline_id line id is: ' || rec.formulaline_id);
1290 gme_debug.put_line (' I/P item id is is: ' || rec.inventory_item_id);
1291 gme_debug.put_line (' I/P plan_qty Qty is: ' || rec.plan_qty);
1292 gme_debug.put_line (' I/P UOM is: ' || rec.dtl_um);
1293 gme_debug.put_line (' O/P item id is is: ' || l_formula_tbl (1).inventory_item_id);
1294 gme_debug.put_line (' O/P plan_qty Qty is: ' || l_formula_tbl (1).qty);
1295 gme_debug.put_line ('O/P UOM is: ' || l_formula_tbl (1).detail_uom);
1296 END IF;
1297
1298 IF l_formula_tbl (1).inventory_item_id = rec.inventory_item_id AND
1299 l_formula_tbl (1).qty = rec.plan_qty AND
1300 l_formula_tbl (1).detail_uom = rec.dtl_um THEN
1301 GOTO NEXT_RECORD; --GO to next record
1302 END IF;
1303
1304 --fetch batch header record
1305 l_batch_header_rec.batch_id := rec.batch_id;
1306
1307 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec, l_batch_header_rec)) THEN
1308 l_oneitem_error := TRUE;
1309 gme_common_pvt.count_and_get (x_count => l_message_count,
1310 p_encoded => fnd_api.g_false,
1311 x_data => l_message_list
1312 );
1313 GOTO NEXT_RECORD; --GO to next record
1314 END IF;
1315
1316 -- Fetch material record for the rec.material_detail_id;
1317 l_material_detail_rec.material_detail_id := rec.material_detail_id;
1318
1319 IF NOT gme_material_details_dbl.fetch_row (p_material_detail => l_material_detail_rec,
1320 x_material_detail => l_material_detail_rec
1321 ) THEN
1322 l_oneitem_error := TRUE;
1323 gme_common_pvt.count_and_get (x_count => l_message_count,
1324 p_encoded => fnd_api.g_false,
1325 x_data => l_message_list
1326 );
1327 --FPBug#4991508 replaced hard coded output messages
1328 /*
1329 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1330 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1331 fnd_message.set_token('LINE',rec.line_no);
1332 fnd_message.set_token('ITEM',rec.item_no);
1333 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1334 fnd_message.set_token('MSG',l_message_list);
1335 fnd_file.put (fnd_file.output,fnd_message.get);
1336
1337 fnd_file.new_line (fnd_file.output, 1);
1338 */
1339
1340
1341 --FPBug#4351032 used gme_common_pvt.log_message
1342 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1343 ,p_token1_name => 'DOC'
1344 ,P_token1_value => l_doc_str||' '||rec.batch_no
1345 ,p_token2_name => 'LINE'
1346 ,P_token2_value => rec.line_no
1347 ,p_token3_name => 'ITEM'
1348 ,P_token3_value => rec.item_no
1349 ,p_token4_name => 'PLANQTY'
1350 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1351 ,p_token5_name => 'MSG'
1352 ,P_token5_value => l_message_list
1353 );
1354 gme_common_pvt.count_and_get (x_count => l_message_count,
1355 p_encoded => fnd_api.g_false,
1356 x_data => l_message_list
1357 );
1358 fnd_file.put(fnd_file.output,l_message_list);
1359 fnd_file.new_line (fnd_file.output, 1);
1360 GOTO NEXT_RECORD; --GO to next record
1361 END IF;
1362
1363 -- nsinghi bug#5674398. Moved the code to here.
1364 IF p_item_id IS NULL THEN
1365 OPEN item_master_cursor (l_material_detail_rec.inventory_item_id,l_material_detail_rec.organization_id);
1366 FETCH item_master_cursor INTO l_old_item_rec;
1367 CLOSE item_master_cursor;
1368 END IF;
1369
1370 OPEN item_master_cursor (l_formula_tbl (1).inventory_item_id,l_material_detail_rec.organization_id);
1371 FETCH item_master_cursor INTO l_new_item_rec;
1372 CLOSE item_master_cursor;
1373
1374 IF l_new_item_rec.process_execution_enabled_flag <> 'Y' THEN
1375 l_oneitem_error := TRUE;
1376
1377 /*
1378 --FPBug#4991508 replaced hard coded output messages
1379 fnd_message.set_name('GME','GME_NO_SUBSTITUTION_EXEC');
1380 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1381 fnd_message.set_token('LINE',rec.line_no);
1382 fnd_message.set_token('ITEM',rec.item_no);
1383 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1384 fnd_message.set_token('NEWITEM',l_new_item_rec.concatenated_segments);
1385 fnd_file.put (fnd_file.output,fnd_message.get);
1386
1387 fnd_file.put(fnd_file.output,l_message_list);
1388 fnd_file.new_line (fnd_file.output, 1);
1389 */
1390
1391 --FPBug#4351032 used gme_common_pvt.log_message
1392 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION_EXEC'
1393 ,p_token1_name => 'DOC'
1394 ,P_token1_value => l_doc_str||' '||rec.batch_no
1395 ,p_token2_name => 'LINE'
1396 ,P_token2_value => rec.line_no
1397 ,p_token3_name => 'ITEM'
1398 ,P_token3_value => rec.item_no
1399 ,p_token4_name => 'PLANQTY'
1400 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1401 ,p_token5_name => 'NEWITEM'
1402 ,P_token5_value => l_new_item_rec.concatenated_segments
1403 );
1404 gme_common_pvt.count_and_get (x_count => l_message_count,
1405 p_encoded => fnd_api.g_false,
1406 x_data => l_message_list
1407 );
1408 fnd_file.put(fnd_file.output,l_message_list);
1409 fnd_file.new_line (fnd_file.output, 1);
1410 GOTO NEXT_RECORD; --GO to next record
1411 END IF;
1412
1413 IF l_new_item_rec.eng_item_flag = 'Y' AND
1414 NVL(l_batch_header_rec.laboratory_ind,0) <> 1 THEN
1415 l_oneitem_error := TRUE;
1416 /*
1417 --FPBug#4991508 replaced hard coded output messages
1418 fnd_message.set_name('GME','GME_NO_SUBSTITUTION_ENG');
1419 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1420 fnd_message.set_token('LINE',rec.line_no);
1421 fnd_message.set_token('ITEM',rec.item_no);
1422 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1423 fnd_message.set_token('NEWITEM',l_new_item_rec.concatenated_segments);
1424 fnd_file.put (fnd_file.output,fnd_message.get);
1425
1426 fnd_file.put(fnd_file.output,l_message_list);
1427 fnd_file.new_line (fnd_file.output, 1);
1428 */
1429
1430 --FPBug#4351032 used gme_common_pvt.log_message
1431 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION_ENG'
1432 ,p_token1_name => 'DOC'
1433 ,P_token1_value => l_doc_str||' '||rec.batch_no
1434 ,p_token2_name => 'LINE'
1435 ,P_token2_value => rec.line_no
1436 ,p_token3_name => 'ITEM'
1437 ,P_token3_value => rec.item_no
1438 ,p_token4_name => 'PLANQTY'
1439 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1440 ,p_token5_name => 'NEWITEM'
1441 ,P_token5_value => l_new_item_rec.concatenated_segments
1442 );
1443 gme_common_pvt.count_and_get (x_count => l_message_count,
1444 p_encoded => fnd_api.g_false,
1445 x_data => l_message_list
1446 );
1447 fnd_file.put(fnd_file.output,l_message_list);
1448 fnd_file.new_line (fnd_file.output, 1);
1449 GOTO NEXT_RECORD; --GO to next record
1450 END IF;
1451
1452 SAVEPOINT create_trans;
1453
1454 IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
1455 -- Delete all reservations for this material line
1456 gme_reservations_pvt.delete_material_reservations (
1457 p_organization_id => rec.organization_id
1458 ,p_batch_id => rec.batch_id
1459 ,p_material_detail_id => rec.material_detail_id
1460 ,x_return_status => l_return_status);
1461
1462 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1463 l_oneitem_error := TRUE;
1464 gme_common_pvt.count_and_get (x_count => l_message_count,
1465 p_encoded => fnd_api.g_false,
1466 x_data => l_message_list
1467 );
1468 --FPBug#4991508 replaced hard coded output messages
1469 /*
1470 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1471 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1472 fnd_message.set_token('LINE',rec.line_no);
1473 fnd_message.set_token('ITEM',rec.item_no);
1474 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1475 fnd_message.set_token('MSG',l_message_list);
1476 fnd_file.put (fnd_file.output,fnd_message.get);
1477
1478 fnd_file.put(fnd_file.output,l_message_list);
1479 fnd_file.new_line (fnd_file.output, 1);
1480 */
1481
1482 --FPBug#4351032 used gme_common_pvt.log_message
1483 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1484 ,p_token1_name => 'DOC'
1485 ,P_token1_value => l_doc_str||' '||rec.batch_no
1486 ,p_token2_name => 'LINE'
1487 ,P_token2_value => rec.line_no
1488 ,p_token3_name => 'ITEM'
1489 ,P_token3_value => rec.item_no
1490 ,p_token4_name => 'PLANQTY'
1491 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1492 ,p_token5_name => 'MSG'
1493 ,P_token5_value => l_message_list
1494 );
1495 gme_common_pvt.count_and_get (x_count => l_message_count,
1496 p_encoded => fnd_api.g_false,
1497 x_data => l_message_list
1498 );
1499 fnd_file.put(fnd_file.output,l_message_list);
1500 fnd_file.new_line (fnd_file.output, 1);
1501 GOTO NEXT_RECORD; --GO to next record
1502 END IF;
1503
1504 l_return_status := NULL;
1505 IF l_old_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
1506 gme_move_orders_pvt.delete_move_order_lines
1507 (p_organization_id => rec.organization_id
1508 ,p_batch_id => rec.batch_id
1509 ,p_material_detail_id => rec.material_detail_id
1510 ,p_invis_move_line_id => rec.move_order_line_id
1511 ,x_return_status => l_return_status);
1512
1513 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1514 l_oneitem_error := TRUE;
1515 gme_common_pvt.count_and_get (x_count => l_message_count,
1516 p_encoded => fnd_api.g_false,
1517 x_data => l_message_list
1518 );
1519 --FPBug#4991508 replaced hard coded output messages
1520 /*
1521 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1522 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1523 fnd_message.set_token('LINE',rec.line_no);
1524 fnd_message.set_token('ITEM',rec.item_no);
1525 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1526 fnd_message.set_token('MSG',l_message_list);
1527 fnd_file.put (fnd_file.output,fnd_message.get);
1528
1529 fnd_file.put(fnd_file.output,l_message_list);
1530 fnd_file.new_line (fnd_file.output, 1);
1531 */
1532
1533 --FPBug#4351032 used gme_common_pvt.log_message
1534 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1535 ,p_token1_name => 'DOC'
1536 ,P_token1_value => l_doc_str||' '||rec.batch_no
1537 ,p_token2_name => 'LINE'
1538 ,P_token2_value => rec.line_no
1539 ,p_token3_name => 'ITEM'
1540 ,P_token3_value => rec.item_no
1541 ,p_token4_name => 'PLANQTY'
1542 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1543 ,p_token5_name => 'MSG'
1544 ,P_token5_value => l_message_list
1545 );
1546 gme_common_pvt.count_and_get (x_count => l_message_count,
1547 p_encoded => fnd_api.g_false,
1548 x_data => l_message_list
1549 );
1550 fnd_file.put(fnd_file.output,l_message_list);
1551 fnd_file.new_line (fnd_file.output, 1);
1552 GOTO NEXT_RECORD; --GO to next record
1553 END IF; --IF l_return_status <> fnd_api.g_ret_sts_success THEN
1554 END IF; --IF l_new_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
1555 END IF; -- l_batch_header_rec.update_inventory_ind = 'Y'
1556
1557 l_return_status := NULL;
1558 l_material_detail_rec.inventory_item_id := l_formula_tbl (1).inventory_item_id;
1559 l_material_detail_rec.plan_qty := l_formula_tbl (1).qty;
1560 l_material_detail_rec.dtl_um := l_formula_tbl (1).detail_uom;
1561 l_material_detail_rec.scale_multiple := l_formula_tbl (1).scale_multiple;
1562 l_material_detail_rec.original_qty := 0;
1563
1564 IF l_formula_tbl (1).detail_uom <> l_new_item_rec.primary_uom_code THEN
1565 l_temp_qty := inv_convert.inv_um_convert
1566 (item_id => l_material_detail_rec.inventory_item_id
1567 ,precision => 5
1568 ,from_quantity => l_material_detail_rec.plan_qty
1569 ,from_unit => l_material_detail_rec.dtl_um
1570 ,to_unit => l_new_item_rec.primary_uom_code
1571 ,from_name => NULL
1572 ,to_name => NULL);
1573 ELSE
1574 l_temp_qty := l_material_detail_rec.plan_qty;
1575 END IF;
1576
1577 l_material_detail_rec.original_primary_qty := l_temp_qty;
1578
1579 IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
1580 IF l_new_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
1581 /* Insert Invisible Move Order Line */
1582 l_material_details_tbl(1) := l_material_detail_rec;
1583
1584 gme_move_orders_pvt.create_move_order_lines (
1585 p_move_order_header_id => l_batch_header_rec.move_order_header_id
1586 ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
1587 ,p_material_details_tbl => l_material_details_tbl
1588 ,x_material_details_tbl => l_material_details_tbl_out
1589 ,x_trolin_tbl => l_trolin_tbl
1590 ,x_return_status => l_return_status);
1591
1592 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1593 l_oneitem_error := TRUE;
1594 gme_common_pvt.count_and_get (x_count => l_message_count,
1595 p_encoded => fnd_api.g_false,
1596 x_data => l_message_list
1597 );
1598
1599 --FPBug#4991508 replaced hard coded output messages
1600 /*
1601 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1602 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1603 fnd_message.set_token('LINE',rec.line_no);
1604 fnd_message.set_token('ITEM',rec.item_no);
1605 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1606 fnd_message.set_token('MSG',l_message_list);
1607 fnd_file.put (fnd_file.output,fnd_message.get);
1608
1609 fnd_file.put(fnd_file.output,l_message_list);
1610 fnd_file.new_line (fnd_file.output, 1);
1611 */
1612
1613 --FPBug#4351032 used gme_common_pvt.log_message
1614 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1615 ,p_token1_name => 'DOC'
1616 ,P_token1_value => l_doc_str||' '||rec.batch_no
1617 ,p_token2_name => 'LINE'
1618 ,P_token2_value => rec.line_no
1619 ,p_token3_name => 'ITEM'
1620 ,P_token3_value => rec.item_no
1621 ,p_token4_name => 'PLANQTY'
1622 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1623 ,p_token5_name => 'MSG'
1624 ,P_token5_value => l_message_list
1625 );
1626 gme_common_pvt.count_and_get (x_count => l_message_count,
1627 p_encoded => fnd_api.g_false,
1628 x_data => l_message_list
1629 );
1630 fnd_file.put(fnd_file.output,l_message_list);
1631 fnd_file.new_line (fnd_file.output, 1);
1632 ROLLBACK TO create_trans;
1633 GOTO NEXT_RECORD; --GO to next record
1634 END IF;
1635 l_material_detail_rec.move_order_line_id := l_material_details_tbl_out(1).move_order_line_id;
1636 ELSE --l_new_item_rec.mtl_transactions_enabled_flag <> 'Y'
1637 l_material_detail_rec.move_order_line_id := NULL;
1638 END IF; --IF l_new_item_rec.mtl_transactions_enabled_flag = 'Y'
1639 END IF; -- IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
1640
1641 --Call material_line_dbl.update_rec
1642 l_return := gme_material_details_dbl.update_row (l_material_detail_rec);
1643
1644 IF (l_return = FALSE) THEN
1645 gme_common_pvt.count_and_get (x_count => l_message_count,
1646 p_encoded => fnd_api.g_false,
1647 x_data => l_message_list
1648 );
1649 l_oneitem_error := TRUE;
1650 --FPBug#4991508 replaced hard coded output messages
1651 /*
1652 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1653 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1654 fnd_message.set_token('LINE',rec.line_no);
1655 fnd_message.set_token('ITEM',rec.item_no);
1656 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1657 fnd_message.set_token('MSG',l_message_list);
1658 fnd_file.put (fnd_file.output,fnd_message.get);
1659
1660 fnd_file.put(fnd_file.output,l_message_list);
1661 fnd_file.new_line (fnd_file.output, 1);
1662 */
1663
1664 --FPBug#4351032 used gme_common_pvt.log_message
1665 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1666 ,p_token1_name => 'DOC'
1667 ,P_token1_value => l_doc_str||' '||rec.batch_no
1668 ,p_token2_name => 'LINE'
1669 ,P_token2_value => rec.line_no
1670 ,p_token3_name => 'ITEM'
1671 ,P_token3_value => rec.item_no
1672 ,p_token4_name => 'PLANQTY'
1673 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1674 ,p_token5_name => 'MSG'
1675 ,P_token5_value => l_message_list
1676 );
1677 gme_common_pvt.count_and_get (x_count => l_message_count,
1678 p_encoded => fnd_api.g_false,
1679 x_data => l_message_list
1680 );
1681 fnd_file.put(fnd_file.output,l_message_list);
1682 fnd_file.new_line (fnd_file.output, 1);
1683 ROLLBACK TO create_trans;
1684 GOTO NEXT_RECORD; --GO to next record
1685 END IF; -- IF (l_return = FALSE) THEN
1686
1687 OPEN c_batchsteps (l_material_detail_rec.material_detail_id);
1688 FETCH c_batchsteps INTO l_batchstep_rec;
1689 CLOSE c_batchsteps;
1690
1691 /* FPBug#4351032 update original primary qty field as this field dont get updated
1692 using gme_material_details_dbl.update_row procedure */
1693 UPDATE gme_material_details
1694 SET original_primary_qty = l_material_detail_rec.original_primary_qty
1695 WHERE material_detail_id = l_material_detail_rec.material_detail_id;
1696
1697 l_material_detail_rec.last_update_date := gme_common_pvt.get_timestamp;
1698
1699 /* Bug 7352169 - do not call auto detail line.
1700 -- Swapna K Bug#4354690 12-MAY-2005
1701 IF l_batch_header_rec.update_inventory_ind = 'Y' AND
1702 l_new_item_rec.mtl_transactions_enabled_flag = 'Y' AND
1703 l_new_item_rec.reservable_type = 1
1704 THEN
1705 l_return_status := NULL;
1706 gme_reservations_pvt.auto_detail_line (
1707 p_material_details_rec => l_material_detail_rec
1708 ,x_return_status => l_return_status);
1709
1710 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1711 gme_debug.put_line ('return status from auto detail line is: ' || l_return_status);
1712 END IF;
1713
1714 -- Validate Return Status
1715 IF l_return_status IN (fnd_api.g_ret_sts_unexp_error, fnd_api.g_ret_sts_error) THEN
1716 gme_common_pvt.count_and_get (x_count => l_message_count,
1717 p_encoded => fnd_api.g_false,
1718 x_data => l_message_list
1719 );
1720 l_oneitem_error := TRUE;
1721
1722 --FPBug#4991508 replaced hard coded output messages
1723 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1724 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1725 fnd_message.set_token('LINE',rec.line_no);
1726 fnd_message.set_token('ITEM',rec.item_no);
1727 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1728 fnd_message.set_token('MSG',l_message_list);
1729 fnd_file.put (fnd_file.output,fnd_message.get);
1730
1731 fnd_file.put(fnd_file.output,l_message_list);
1732 fnd_file.new_line (fnd_file.output, 1);
1733 --FPBug#4351032 used gme_common_pvt.log_message
1734 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1735 ,p_token1_name => 'DOC'
1736 ,P_token1_value => l_doc_str||' '||rec.batch_no
1737 ,p_token2_name => 'LINE'
1738 ,P_token2_value => rec.line_no
1739 ,p_token3_name => 'ITEM'
1740 ,P_token3_value => rec.item_no
1741 ,p_token4_name => 'PLANQTY'
1742 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1743 ,p_token5_name => 'MSG'
1744 ,P_token5_value => l_message_list
1745 );
1746 gme_common_pvt.count_and_get (x_count => l_message_count,
1747 p_encoded => fnd_api.g_false,
1748 x_data => l_message_list
1749 );
1750 fnd_file.put(fnd_file.output,l_message_list);
1751 fnd_file.new_line (fnd_file.output, 1);
1752 ROLLBACK TO create_trans;
1753 GOTO NEXT_RECORD; --GO to next record
1754 END IF; -- IF l_return_status IN (fnd_api.g_ret_sts_unexp_error, fnd_api.g_ret_sts_error) THEN
1755
1756 END IF; -- update inventory ind
1757
1758 End Bug 7352169 - do not call auto detail line. */
1759
1760 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1761 gme_debug.put_line ('ASQC is: ' || l_batch_header_rec.automatic_step_calculation);
1762 END IF;
1763
1764 IF l_batch_header_rec.automatic_step_calculation = 1 THEN
1765 IF l_batch_header_rec.update_inventory_ind = 'Y' AND NOT l_trans_loaded THEN
1766 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1767 gme_debug.put_line ('Entered into loading txns');
1768 END IF;
1769
1770 -- Swapna K Bug#4354690 12-MAY-2005
1771 l_return_status := NULL;
1772
1773 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_header_rec,
1774 x_rsc_row_count => l_rsc_trans_count,
1775 x_return_status => l_return_status
1776 );
1777
1778 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1779 gme_common_pvt.count_and_get (x_count => l_message_count,
1780 p_encoded => fnd_api.g_false,
1781 x_data => l_message_list
1782 );
1783 l_oneitem_error := TRUE;
1784 --FPBug#4991508 replaced hard coded output messages
1785 /*
1786 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1787 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1788 fnd_message.set_token('LINE',rec.line_no);
1789 fnd_message.set_token('ITEM',rec.item_no);
1790 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1791 fnd_message.set_token('MSG',l_message_list);
1792 fnd_file.put (fnd_file.output,fnd_message.get);
1793
1794 fnd_file.put(fnd_file.output,l_message_list);
1795 fnd_file.new_line (fnd_file.output, 1);
1796 */
1797
1798 --FPBug#4351032 used gme_common_pvt.log_message
1799 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1800 ,p_token1_name => 'DOC'
1801 ,P_token1_value => l_doc_str||' '||rec.batch_no
1802 ,p_token2_name => 'LINE'
1803 ,P_token2_value => rec.line_no
1804 ,p_token3_name => 'ITEM'
1805 ,P_token3_value => rec.item_no
1806 ,p_token4_name => 'PLANQTY'
1807 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1808 ,p_token5_name => 'MSG'
1809 ,P_token5_value => l_message_list
1810 );
1811 gme_common_pvt.count_and_get (x_count => l_message_count,
1812 p_encoded => fnd_api.g_false,
1813 x_data => l_message_list
1814 );
1815 fnd_file.put(fnd_file.output,l_message_list);
1816 fnd_file.new_line (fnd_file.output, 1);
1817 ROLLBACK TO create_trans;
1818 GOTO NEXT_RECORD; --GO to next record
1819 END IF;
1820
1821 l_trans_loaded := TRUE;
1822 END IF; -- update inventory ind
1823
1824 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1825 gme_debug.put_line ('Before updating step qty');
1826 END IF;
1827
1828 gme_update_step_qty_pvt.update_step_qty (p_batch_step_rec => l_batchstep_rec,
1829 x_message_count => l_message_count,
1830 x_message_list => l_message_list,
1831 x_return_status => x_return_status,
1832 x_batch_step_rec => x_batchstep_rec
1833 );
1834
1835 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1836 gme_debug.put_line ('After update step qty, return status is: ' || x_return_status);
1837 END IF;
1838 -- Swapna K Bug#4354690 12-MAY-2005
1839 /* l_return_status is replaced with x_return_status in the below if condition */
1840 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1841 gme_common_pvt.count_and_get (x_count => l_message_count,
1842 p_encoded => fnd_api.g_false,
1843 x_data => l_message_list
1844 );
1845 l_oneitem_error := TRUE;
1846
1847 --FPBug#4991508 replaced hard coded output messages
1848 /*
1849 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1850 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1851 fnd_message.set_token('LINE',rec.line_no);
1852 fnd_message.set_token('ITEM',rec.item_no);
1853 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1854 fnd_message.set_token('MSG',l_message_list);
1855 fnd_file.put (fnd_file.output,fnd_message.get);
1856
1857 fnd_file.put(fnd_file.output,l_message_list);
1858 fnd_file.new_line (fnd_file.output, 1);
1859 */
1860
1861 --FPBug#4351032 used gme_common_pvt.log_message
1862 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1863 ,p_token1_name => 'DOC'
1864 ,P_token1_value => l_doc_str||' '||rec.batch_no
1865 ,p_token2_name => 'LINE'
1866 ,P_token2_value => rec.line_no
1867 ,p_token3_name => 'ITEM'
1868 ,P_token3_value => rec.item_no
1869 ,p_token4_name => 'PLANQTY'
1870 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1871 ,p_token5_name => 'MSG'
1872 ,P_token5_value => l_message_list
1873 );
1874 gme_common_pvt.count_and_get (x_count => l_message_count,
1875 p_encoded => fnd_api.g_false,
1876 x_data => l_message_list
1877 );
1878 fnd_file.put(fnd_file.output,l_message_list);
1879 fnd_file.new_line (fnd_file.output, 1);
1880 ROLLBACK TO create_trans;
1881 GOTO NEXT_RECORD; --GO to next record
1882 END IF;
1883 END IF; -- ASQC
1884
1885 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1886 gme_debug.put_line ('Before consolidate transacitons');
1887 END IF;
1888
1889 IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
1890
1891 IF l_batch_header_rec.automatic_step_calculation = 1 AND l_trans_loaded THEN
1892 gme_resource_engine_pvt.consolidate_batch_resources (l_batch_header_rec.batch_id,
1893 x_return_status
1894 );
1895
1896 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1897 gme_debug.put_line ('After consolidate resource transactions' || x_return_status);
1898 END IF;
1899
1900 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1901 gme_common_pvt.count_and_get (x_count => l_message_count,
1902 p_encoded => fnd_api.g_false,
1903 x_data => l_message_list
1904 );
1905 l_oneitem_error := TRUE;
1906
1907 --FPBug#4991508 replaced hard coded output messages
1908 /*
1909 fnd_message.set_name('GME','GME_NO_SUBSTITUTION');
1910 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1911 fnd_message.set_token('LINE',rec.line_no);
1912 fnd_message.set_token('ITEM',rec.item_no);
1913 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1914 fnd_message.set_token('MSG',l_message_list);
1915 fnd_file.put (fnd_file.output,fnd_message.get);
1916
1917 fnd_file.put(fnd_file.output,l_message_list);
1918 fnd_file.new_line (fnd_file.output, 1);
1919 */
1920
1921 --FPBug#4351032 used gme_common_pvt.log_message
1922 gme_common_pvt.log_message(p_message_code => 'GME_NO_SUBSTITUTION'
1923 ,p_token1_name => 'DOC'
1924 ,P_token1_value => l_doc_str||' '||rec.batch_no
1925 ,p_token2_name => 'LINE'
1926 ,P_token2_value => rec.line_no
1927 ,p_token3_name => 'ITEM'
1928 ,P_token3_value => rec.item_no
1929 ,p_token4_name => 'PLANQTY'
1930 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1931 ,p_token5_name => 'MSG'
1932 ,P_token5_value => l_message_list
1933 );
1934 gme_common_pvt.count_and_get (x_count => l_message_count,
1935 p_encoded => fnd_api.g_false,
1936 x_data => l_message_list
1937 );
1938 fnd_file.put(fnd_file.output,l_message_list);
1939 fnd_file.new_line (fnd_file.output, 1);
1940 ROLLBACK TO create_trans;
1941 GOTO NEXT_RECORD; --GO to next record
1942 END IF;
1943 END IF; /* l_batch_header_rec.automatic_step_calculation = 1 */
1944 END IF; /* l_batch_header_rec.update_inventory_ind = 'Y' */
1945 COMMIT;
1946 l_oneitem_success := TRUE;
1947
1948 --FPBug#4991508 replaced hard coded output messages
1949 /*
1950 fnd_message.set_name('GME','GME_SUBSTITUTION_SUCCESS');
1951 fnd_message.set_token('DOC',l_doc_str||' '||rec.batch_no);
1952 fnd_message.set_token('LINE',rec.line_no);
1953 fnd_message.set_token('ITEM',rec.item_no);
1954 fnd_message.set_token('PLANQTY',rec.plan_qty||' '||rec.dtl_um);
1955 fnd_message.set_token('NEWITEM',l_new_item_rec.concatenated_segments);
1956 fnd_message.set_token('NEWQTY',l_formula_tbl (1).qty||' '||l_formula_tbl (1).detail_uom);
1957 fnd_file.put (fnd_file.output,fnd_message.get);
1958
1959 fnd_file.put(fnd_file.output,l_message_list);
1960 fnd_file.new_line (fnd_file.output, 1);
1961 */
1962
1963 --FPBug#4351032 used gme_common_pvt.log_message
1964 gme_common_pvt.log_message(p_message_code => 'GME_SUBSTITUTION_SUCCESS'
1965 ,p_token1_name => 'DOC'
1966 ,P_token1_value => l_doc_str||' '||rec.batch_no
1967 ,p_token2_name => 'LINE'
1968 ,P_token2_value => rec.line_no
1969 ,p_token3_name => 'ITEM'
1970 ,P_token3_value => rec.item_no
1971 ,p_token4_name => 'PLANQTY'
1972 ,P_token4_value => rec.plan_qty||' '||rec.dtl_um
1973 ,p_token5_name => 'NEWITEM'
1974 ,P_token5_value => l_new_item_rec.concatenated_segments
1975 ,p_token6_name => 'NEWQTY'
1976 ,P_token6_value => l_formula_tbl (1).qty||' '||l_formula_tbl (1).detail_uom
1977 );
1978 gme_common_pvt.count_and_get (x_count => l_message_count,
1979 p_encoded => fnd_api.g_false,
1980 x_data => l_message_list
1981 );
1982 fnd_file.put(fnd_file.output,l_message_list);
1983 fnd_file.new_line (fnd_file.output, 1);
1984
1985 <<NEXT_RECORD>>
1986 NULL;
1987 END LOOP;
1988
1989 fnd_file.new_line (fnd_file.output, 1);
1990
1991 IF l_oneitem_success = TRUE AND l_oneitem_error = TRUE THEN
1992 gme_common_pvt.log_message('GME_ATLEAST_ONE_NOT_SUBSTITUTE');
1993 gme_common_pvt.count_and_get (x_count => l_message_count,
1994 p_encoded => fnd_api.g_false,
1995 x_data => l_message_list
1996 );
1997 fnd_file.put(fnd_file.output,l_message_list);
1998
1999 --FPBug#4991508 replaced hard coded output messages
2000 /*
2001 fnd_message.set_name('GME','GME_ATLEAST_ONE_NOT_SUBSTITUTE');
2002 fnd_file.put_line (fnd_file.output, fnd_message.get);
2003 */
2004
2005 errbuf := l_message_list;
2006 retcode := 1; --warning
2007 ELSIF l_oneitem_success = FALSE AND l_oneitem_error = TRUE THEN
2008 --FPBug#4991508 replaced hard coded output messages
2009 /*
2010 fnd_message.set_name('GME','GME_NONE_SUBSTITUTED');
2011 fnd_file.put_line (fnd_file.output, fnd_message.get);
2012 */
2013
2014 gme_common_pvt.log_message('GME_NONE_SUBSTITUTED');
2015 gme_common_pvt.count_and_get (x_count => l_message_count,
2016 p_encoded => fnd_api.g_false,
2017 x_data => l_message_list
2018 );
2019 fnd_file.put(fnd_file.output,l_message_list);
2020 errbuf := l_message_list;
2021 retcode := 2; --error
2022 ELSE
2023 --FPBug#4991508 replaced hard coded output messages
2024 /*
2025 fnd_message.set_name('GME','GME_SUBSTITUTION_SUCCESSFUL');
2026 fnd_file.put_line (fnd_file.output, fnd_message.get);
2027 */
2028
2029 errbuf := 'Substitutions are successful';
2030 gme_common_pvt.log_message('GME_SUBSTITUTION_SUCCESSFUL');
2031 gme_common_pvt.count_and_get (x_count => l_message_count,
2032 p_encoded => fnd_api.g_false,
2033 x_data => l_message_list
2034 );
2035 fnd_file.put(fnd_file.output,l_message_list);
2036 errbuf := l_message_list;
2037 retcode := 0; --success
2038 END IF;
2039 EXCEPTION
2040 WHEN SETUP_FAILURE THEN
2041 fnd_file.put (fnd_file.LOG, 'Setup Failed for organization ID '||p_org_id);
2042 fnd_file.new_line (fnd_file.LOG, 1);
2043 errbuf := 'Setup Failed for organization ID '||p_org_id;
2044 WHEN OTHERS THEN
2045 fnd_file.put (fnd_file.LOG, SQLERRM);
2046 fnd_file.new_line (fnd_file.LOG, 1);
2047 errbuf := SQLERRM;
2048 END substitute_ingredients;
2049
2050
2051 /*======================================================================
2052 -- PROCEDURE:
2053 -- get_total_quantity
2054 --
2055 -- DESCRIPTION:
2056 -- Procedure to sum up all product quantities.
2057 --
2058 -- HISTORY:
2059 -- siva FPBug# 4684029
2060 -- siva FPBug#4684029 rework
2061 -- In exception block 'E', 'S' are replaced by FND_API variables.
2062 -- SivakumarG Bug#5111078 Added x_total_wip_plan_qty parameter
2063 ======================================================================*/
2064 PROCEDURE get_total_qty(
2065 p_batch_id IN NUMBER,
2066 p_line_type IN NUMBER,
2067 p_uom IN VARCHAR2,
2068 x_total_plan_qty OUT NOCOPY NUMBER,
2069 x_total_wip_plan_qty OUT NOCOPY NUMBER,
2070 x_total_actual_qty OUT NOCOPY NUMBER,
2071 x_uom OUT NOCOPY VARCHAR2,
2072 x_return_status OUT NOCOPY VARCHAR2)
2073 IS
2074 CURSOR get_primary_product_uom ( v_batch_id IN NUMBER )IS
2075 SELECT gm.dtl_um
2076 FROM gmd_recipe_validity_rules vr, gme_material_details gm, gme_batch_header bh
2077 WHERE bh.recipe_validity_rule_id = vr.recipe_validity_rule_id
2078 AND bh.batch_id = gm.batch_id
2079 AND vr.inventory_item_id = gm.inventory_item_id
2080 AND gm.line_type = 1 /*FPBug# 4684029 rework */
2081 AND bh.batch_id = v_batch_id
2082 AND rownum = 1 ;
2083
2084 CURSOR get_quantities ( v_batch_id IN NUMBER, v_line_type IN NUMBER )IS
2085 SELECT inventory_item_id, plan_qty, wip_plan_qty, actual_qty, dtl_um
2086 FROM gme_material_details
2087 WHERE batch_id = v_batch_id
2088 AND line_type = v_line_type;
2089
2090 l_api_name VARCHAR2 (30):= 'get_total_quantity';
2091 prod_uom VARCHAR2(3);
2092 l_item_id NUMBER;
2093 l_actual_qty NUMBER := 0;
2094 l_plan_qty NUMBER := 0;
2095 l_wip_plan_qty NUMBER := 0;
2096 l_item_um VARCHAR2(3);
2097 l_total_actual_qty NUMBER := 0;
2098 l_total_plan_qty NUMBER := 0;
2099 l_total_wip_plan_qty NUMBER := 0;
2100
2101 uom_conversion_failure EXCEPTION ;
2102 invalid_batch_id EXCEPTION ;
2103 BEGIN
2104 IF (NVL (g_debug, -1) = gme_debug.g_log_procedure) THEN
2105 gme_debug.put_line('Entering gme_api_grp.get_total_quantity with batch id '||p_batch_id);
2106 END IF;
2107
2108 IF p_batch_id IS NULL THEN
2109 gme_common_pvt.log_message ('GME_INVALID_BATCH','ID','BATCH_ID');
2110 RAISE invalid_batch_id;
2111 END IF;
2112
2113
2114 IF p_uom IS NULL THEN
2115 OPEN get_primary_product_uom (p_batch_id) ;
2116 FETCH get_primary_product_uom INTO prod_uom ;
2117 CLOSE get_primary_product_uom ;
2118 ELSE
2119 prod_uom := p_uom;
2120 END IF;
2121
2122 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2123 gme_debug.put_line('UOM being used for possible conversion is '||prod_uom);
2124 END IF;
2125
2126 OPEN get_quantities(p_batch_id, p_line_type);
2127 LOOP
2128 FETCH get_quantities INTO l_item_id, l_plan_qty, l_wip_plan_qty, l_actual_qty, l_item_um;
2129 EXIT when get_quantities%NOTFOUND ;
2130
2131 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2132 gme_debug.put_line('Fetched values for item id '||l_item_id||' are:');
2133 gme_debug.put_line('Plan qty '||l_plan_qty);
2134 gme_debug.put_line('WIP Plan qty '||l_wip_plan_qty);
2135 gme_debug.put_line('Actual qty '||l_actual_qty);
2136 gme_debug.put_line('UOM '||l_item_um);
2137 END IF;
2138
2139 IF l_item_um <> prod_uom THEN
2140 l_plan_qty := inv_convert.inv_um_convert(l_item_id,
2141 5,
2142 l_plan_qty,
2143 l_item_um,
2144 prod_uom,
2145 NULL,
2146 NULL);
2147 IF l_plan_qty < 0 THEN
2148 CLOSE get_quantities;
2149 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2150 gme_debug.put_line('Conversion failed for item id is '||l_item_id);
2151 gme_debug.put_line('Plan qty '||l_plan_qty);
2152 gme_debug.put_line('From UOM '||l_item_um);
2153 gme_debug.put_line('To UOM '||prod_uom);
2154 END IF;
2155
2156 -- Bug 9975725
2157 gme_common_pvt.log_message
2158 (p_message_code => 'INV_UOM_CONVERSION_ERROR'
2159 ,p_token1_name => 'uom1'
2160 ,p_token1_value => l_item_um
2161 ,p_token2_name => 'uom2'
2162 ,p_token2_value => prod_uom
2163 ,p_token3_name => 'module'
2164 ,p_token3_value => 'GME_API_GRP.get_total_qty'
2165 ,p_product_code => 'INV');
2166
2167 RAISE uom_conversion_failure;
2168 END IF;
2169
2170 l_actual_qty := inv_convert.inv_um_convert(l_item_id,
2171 5,
2172 l_actual_qty,
2173 l_item_um,
2174 prod_uom,
2175 NULL,
2176 NULL);
2177 IF l_actual_qty < 0 THEN
2178 CLOSE get_quantities;
2179 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2180 gme_debug.put_line('Converted values for item id '||l_item_id||' are:');
2181 gme_debug.put_line('Plan qty '||l_plan_qty);
2182 gme_debug.put_line('Actual qty '||l_actual_qty);
2183 END IF;
2184 RAISE uom_conversion_failure;
2185 END IF;
2186
2187 --Bug#5111078 Begin
2188 l_wip_plan_qty := inv_convert.inv_um_convert(l_item_id,
2189 5,
2190 l_wip_plan_qty,
2191 l_item_um,
2192 prod_uom,
2193 NULL,
2194 NULL);
2195 IF l_wip_plan_qty < 0 THEN
2196 CLOSE get_quantities;
2197 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2198 gme_debug.put_line('Converted values for item id '||l_item_id||' are:');
2199 gme_debug.put_line('Plan qty '||l_plan_qty);
2200 gme_debug.put_line('WIP Plan qty '||l_wip_plan_qty);
2201 gme_debug.put_line('Actual qty '||l_actual_qty);
2202 END IF;
2203 RAISE uom_conversion_failure;
2204 END IF;
2205 --Bug#5111078 End
2206 END IF ;
2207
2208 l_total_plan_qty := l_total_plan_qty + l_plan_qty ;
2209 --Bug#5111078
2210 l_total_wip_plan_qty := l_total_wip_plan_qty + l_wip_plan_qty;
2211 l_total_actual_qty := l_total_actual_qty + l_actual_qty ;
2212 END LOOP ;
2213 CLOSE get_quantities;
2214
2215 x_total_plan_qty := l_total_plan_qty;
2216 --Bug#5111078
2217 x_total_wip_plan_qty:=l_total_wip_plan_qty;
2218 x_total_actual_qty := l_total_actual_qty;
2219 -- This line will return uom used for conversion.
2220 x_uom := prod_uom;
2221
2222 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2223 gme_debug.put_line('Qtys being returned are:');
2224 gme_debug.put_line('Total plan qty '||l_total_plan_qty);
2225 gme_debug.put_line('Total actual qty '||l_total_actual_qty);
2226 END IF;
2227 x_return_status := fnd_api.g_ret_sts_success;
2228 EXCEPTION
2229 WHEN uom_conversion_failure THEN
2230 x_return_status := fnd_api.g_ret_sts_error;
2231 WHEN invalid_batch_id THEN
2232 x_return_status := fnd_api.g_ret_sts_error;
2233 WHEN OTHERS THEN
2234 x_return_status := fnd_api.g_ret_sts_unexp_error;
2235 fnd_file.put (fnd_file.LOG, SQLERRM);
2236 fnd_file.new_line (fnd_file.LOG, 1);
2237 END get_total_qty;
2238
2239 --siva FPBug# 4684029 End
2240
2241 /*======================================================================
2242 -- PROCEDURE:
2243 -- check_inv_negative
2244 --
2245 -- DESCRIPTION:
2246 -- Procedure to check whether inventory will be driven negative.
2247 -- RETURNS TRUE WHEN
2248 -- Org does not allow negative and transaction will drive qty -ve
2249 -- OR
2250 -- Org allows negative but reservations exist and transaction
2251 -- will drive qty -ve
2252 --
2253 -- HISTORY:
2254 -- Jalaj Srivastava Created for Bug 5021522
2255 ======================================================================*/
2256 PROCEDURE check_inv_negative
2257 ( p_transaction_id IN NUMBER
2258 ,p_item_no IN VARCHAR2
2259 ,x_msg_count OUT NOCOPY NUMBER
2260 ,x_msg_data OUT NOCOPY VARCHAR2
2261 ,x_return_status OUT NOCOPY VARCHAR2
2262 ) IS
2263 l_mmt_rec mtl_material_transactions%ROWTYPE;
2264 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
2265 l_ret boolean;
2266 l_api_name VARCHAR2 (50) := 'check_inv_negative';
2267 BEGIN
2268 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
2269 gme_debug.g_log_procedure THEN
2270 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2271 END IF;
2272 -- Initially let us assign the return status to success
2273 x_return_status := fnd_api.g_ret_sts_success;
2274
2275 /* Get transaction line and lots */
2276 gme_transactions_pvt.get_transactions
2277 ( p_transaction_id => p_transaction_id
2278 ,x_mmt_rec => l_mmt_rec
2279 ,x_mmln_tbl => l_mmln_tbl
2280 ,x_return_status => x_return_status
2281 );
2282 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2283 RAISE fnd_api.g_exc_error;
2284 END IF;
2285
2286 l_ret := gme_unrelease_batch_pvt.check_inv_negative
2287 ( p_mmt_rec => l_mmt_rec
2288 ,p_mmln_tbl => l_mmln_tbl
2289 ,p_org_neg_control => gme_common_pvt.g_allow_neg_inv
2290 ,p_item_no => p_item_no
2291 );
2292 IF l_ret THEN
2293 RAISE fnd_api.g_exc_error;
2294 END IF;
2295
2296 gme_common_pvt.count_and_get
2297 ( x_count => x_msg_count
2298 ,p_encoded => fnd_api.g_false
2299 ,x_data => x_msg_data);
2300
2301 IF (g_debug <= gme_debug.g_log_statement) THEN
2302 gme_debug.put_line ( g_pkg_name
2303 || '.'
2304 || l_api_name
2305 || ':'
2306 || 'Exiting with '
2307 || x_return_status);
2308 END IF;
2309
2310 EXCEPTION
2311
2312 WHEN fnd_api.g_exc_error THEN
2313 x_return_status := fnd_api.g_ret_sts_error;
2314 gme_common_pvt.count_and_get
2315 ( x_count => x_msg_count
2316 ,p_encoded => fnd_api.g_false
2317 ,x_data => x_msg_data);
2318 WHEN fnd_api.g_exc_unexpected_error THEN
2319 x_return_status := fnd_api.g_ret_sts_unexp_error;
2320 gme_common_pvt.count_and_get
2321 ( x_count => x_msg_count
2322 ,p_encoded => fnd_api.g_false
2323 ,x_data => x_msg_data);
2324 WHEN OTHERS THEN
2325 x_return_status := fnd_api.g_ret_sts_unexp_error;
2326 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2327 gme_common_pvt.count_and_get
2328 ( x_count => x_msg_count
2329 ,p_encoded => fnd_api.g_false
2330 ,x_data => x_msg_data);
2331 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2332 gme_debug.put_line ( g_pkg_name
2333 || '.'
2334 || l_api_name
2335 || ':'
2336 || 'WHEN OTHERS:'
2337 || SQLERRM);
2338 END IF;
2339 END check_inv_negative;
2340
2341 --nsinghi bug#5674398 Added following API
2342 /*======================================================================
2343 -- FUNCTION:
2344 -- get_ingr_sub_date
2345 --
2346 -- DESCRIPTION:
2347 -- Function to return the substitution effective date.
2348 -- HISTORY:
2349 -- Namit S. 27-NOV-2006 bug#5674398
2350 ======================================================================*/
2351
2352 FUNCTION get_ingr_sub_date (p_batch_id IN gme_batch_header.batch_id%TYPE,
2353 p_material_detail_id IN gme_material_details.material_detail_id%TYPE) RETURN DATE
2354 IS
2355 CURSOR cur_get_start_end_Date(
2356 p_batch_id IN gme_batch_header.batch_id%TYPE
2357 ) IS
2358 SELECT plan_start_date,plan_cmplt_date
2359 FROM gme_batch_header
2360 WHERE batch_id = p_batch_id;
2361
2362 CURSOR cur_get_matl_requirement_dt(
2363 p_material_detail_id gme_material_details.material_detail_id%TYPE
2364 ) IS
2365 SELECT material_requirement_date
2366 FROM gme_material_details
2367 WHERE material_detail_id = p_material_detail_id;
2368
2369 l_plan_start_date DATE;
2370 l_plan_cmplt_date DATE;
2371 l_ingred_sub_date NUMBER;
2372 l_matl_requirement_dt DATE;
2373 l_api_name VARCHAR2 (50) := 'get_ingr_sub_date';
2374
2375 BEGIN
2376 --Take the value of the profile,GME: Ingredient Substitution Date
2377 l_ingred_sub_date := gme_common_pvt.g_ingr_sub_date;
2378 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2379 gme_debug.put_line ( g_pkg_name
2380 || '.'
2381 || l_api_name
2382 || ':'
2383 || 'l_ingred_sub_date : '
2384 || l_ingred_sub_date);
2385 END IF;
2386
2387 IF l_ingred_sub_date = 2 THEN -- Ingredient Requirement Date
2388 OPEN cur_get_matl_requirement_dt (p_material_detail_id);
2389 FETCH cur_get_matl_requirement_dt INTO l_matl_requirement_dt;
2390 CLOSE cur_get_matl_requirement_dt;
2391 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2392 gme_debug.put_line ( g_pkg_name
2393 || '.'
2394 || l_api_name
2395 || ':'
2396 || 'l_matl_requirement_dt : '
2397 || TO_CHAR(l_matl_requirement_dt, 'MON-DD-YYYY HH24:MI:SS'));
2398 END IF;
2399 RETURN l_matl_requirement_dt;
2400 ELSE
2401 --Fetch batch start and end dates
2402 OPEN cur_get_start_end_Date (p_batch_id);
2403 FETCH cur_get_start_end_Date INTO l_plan_start_date,l_plan_cmplt_date;
2404 CLOSE cur_get_start_end_Date;
2405 IF l_ingred_sub_date = 1 THEN -- Batch Start Date
2406 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2407 gme_debug.put_line ( g_pkg_name
2408 || '.'
2409 || l_api_name
2410 || ':'
2411 || 'l_ingr_sub_dt = l_plan_start_date : '
2412 || TO_CHAR(l_plan_start_date, 'MON-DD-YYYY HH24:MI:SS'));
2413 END IF;
2414 RETURN l_plan_start_date;
2415 ELSIF l_ingred_sub_date = 3 THEN -- Batch Completion Date
2416 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2417 gme_debug.put_line ( g_pkg_name
2418 || '.'
2419 || l_api_name
2420 || ':'
2421 || 'l_ingr_sub_dt = l_plan_cmplt_date : '
2422 || TO_CHAR(l_plan_cmplt_date, 'MON-DD-YYYY HH24:MI:SS'));
2423 END IF;
2424 RETURN l_plan_cmplt_date;
2425 END IF;
2426 END IF;
2427
2428 END get_ingr_sub_date;
2429
2430 /* Bug 5597385 Added below procedures */
2431 PROCEDURE get_mat_resvns(p_organization_id IN NUMBER,
2432 p_mat_det_id IN NUMBER,
2433 p_batch_id IN NUMBER,
2434 x_resvns_cur OUT NOCOPY g_gmo_resvns,
2435 x_return_status OUT NOCOPY VARCHAR2) IS
2436 l_api_name CONSTANT VARCHAR2 (30) := 'get_mat_resvns';
2437 BEGIN
2438 IF g_debug <= gme_debug.g_log_procedure THEN
2439 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2440 END IF;
2441 x_return_status := FND_API.G_RET_STS_SUCCESS;
2442 OPEN x_resvns_cur FOR
2443 SELECT mr.*
2444 FROM mtl_reservations mr
2445 WHERE mr.organization_id = p_organization_id
2446 AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
2447 AND mr.demand_source_header_id = p_batch_id
2448 AND mr.demand_source_line_id = p_mat_det_id
2449 AND NOT EXISTS (SELECT 1
2450 FROM mtl_material_transactions_temp
2451 WHERE reservation_id = mr.reservation_id
2452 AND organization_id = p_organization_id)
2453 ORDER BY mr.requirement_date, mr.reservation_id;
2454 EXCEPTION
2455 WHEN OTHERS THEN
2456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2457 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2458 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2459 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2460 END IF;
2461 END get_mat_resvns;
2462
2463 PROCEDURE get_mat_pplots(p_mat_det_id IN NUMBER,
2464 x_pplot_cur OUT NOCOPY g_gmo_pplots,
2465 x_return_status OUT NOCOPY VARCHAR2) IS
2466 l_api_name CONSTANT VARCHAR2 (30) := 'get_mat_pplots';
2467 BEGIN
2468 IF g_debug <= gme_debug.g_log_procedure THEN
2469 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2470 END IF;
2471 x_return_status := FND_API.G_RET_STS_SUCCESS;
2472 OPEN x_pplot_cur FOR
2473 SELECT *
2474 FROM gme_pending_product_lots
2475 WHERE material_detail_id = p_mat_det_id
2476 ORDER BY sequence asc, lot_number asc;
2477 EXCEPTION
2478 WHEN OTHERS THEN
2479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2480 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2481 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2482 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2483 END IF;
2484 END get_mat_pplots;
2485
2486 PROCEDURE get_mat_trans(p_organization_id IN NUMBER,
2487 p_mat_det_id IN NUMBER,
2488 p_batch_id IN NUMBER,
2489 x_txns_cur OUT NOCOPY g_gmo_txns,
2490 x_return_status OUT NOCOPY VARCHAR2) IS
2491 l_api_name CONSTANT VARCHAR2 (30) := 'get_mat_trans';
2492 BEGIN
2493 IF g_debug <= gme_debug.g_log_procedure THEN
2494 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2495 END IF;
2496 x_return_status := FND_API.G_RET_STS_SUCCESS;
2497 OPEN x_txns_cur FOR
2498 SELECT *
2499 FROM mtl_material_transactions mmt
2500 WHERE trx_source_line_id = p_mat_det_id
2501 AND transaction_source_id = p_batch_id
2502 AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
2503 AND NOT EXISTS (SELECT /*+ no_unnest */
2504 transaction_id1
2505 FROM gme_transaction_pairs
2506 WHERE transaction_id1 = mmt.transaction_id
2507 AND pair_type = gme_common_pvt.g_pairs_reversal_type)
2508 ORDER BY mmt.transaction_id;
2509 EXCEPTION
2510 WHEN OTHERS THEN
2511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2512 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2513 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2514 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2515 END IF;
2516 END get_mat_trans;
2517
2518 PROCEDURE get_lot_trans(p_transaction_id IN NUMBER,
2519 x_lot_txns_cur OUT NOCOPY g_gmo_lot_txns,
2520 x_return_status OUT NOCOPY VARCHAR2) IS
2521 l_api_name CONSTANT VARCHAR2 (30) := 'get_lot_trans';
2522 BEGIN
2523 IF g_debug <= gme_debug.g_log_procedure THEN
2524 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2525 END IF;
2526 x_return_status := FND_API.G_RET_STS_SUCCESS;
2527 OPEN x_lot_txns_cur FOR
2528 SELECT *
2529 FROM mtl_transaction_lot_numbers
2530 WHERE transaction_id = p_transaction_id;
2531 EXCEPTION
2532 WHEN OTHERS THEN
2533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2534 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2535 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2536 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2537 END IF;
2538 END get_lot_trans;
2539
2540 PROCEDURE create_material_txn(p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
2541 p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl,
2542 x_return_status OUT NOCOPY VARCHAR2) IS
2543 l_api_name CONSTANT VARCHAR2 (30) := 'create_material_txn';
2544 l_return_status VARCHAR2(1);
2545 setup_failed EXCEPTION;
2546 create_txn_fail EXCEPTION;
2547 validate_txn_fail EXCEPTION;
2548 BEGIN
2549 IF g_debug <= gme_debug.g_log_procedure THEN
2550 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2551 END IF;
2552 x_return_status := FND_API.G_RET_STS_SUCCESS;
2553 IF NOT(gme_common_pvt.g_setup_done) THEN
2554 gme_common_pvt.g_setup_done := gme_common_pvt.setup(p_org_id => p_mmti_rec.organization_id);
2555 IF NOT(gme_common_pvt.g_setup_done) THEN
2556 RAISE setup_failed;
2557 END IF;
2558 END IF;
2559 IF (gme_common_pvt.g_timestamp IS NULL) THEN
2560 gme_common_pvt.set_timestamp;
2561 END IF;
2562 gme_transactions_pvt.gmo_pre_process_val(p_mmti_rec => p_mmti_rec,
2563 p_mmli_tbl => p_mmli_tbl,
2564 p_mode => 'I',
2565 x_return_status => l_return_status);
2566 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2567 RAISE validate_txn_fail;
2568 END IF;
2569 gme_transactions_pvt.create_material_txn(p_mmti_rec => p_mmti_rec,
2570 p_mmli_tbl => p_mmli_tbl,
2571 x_return_status => l_return_status);
2572 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2573 RAISE create_txn_fail;
2574 END IF;
2575 IF g_debug <= gme_debug.g_log_procedure THEN
2576 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2577 END IF;
2578 EXCEPTION
2579 WHEN setup_failed THEN
2580 x_return_status := FND_API.G_RET_STS_ERROR;
2581 WHEN create_txn_fail OR validate_txn_fail THEN
2582 x_return_status := l_return_status;
2583 WHEN OTHERS THEN
2584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2585 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2586 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2587 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2588 END IF;
2589 END create_material_txn;
2590
2591 PROCEDURE update_material_txn(p_transaction_id IN NUMBER,
2592 p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
2593 p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl,
2594 x_return_status OUT NOCOPY VARCHAR2) IS
2595 l_api_name CONSTANT VARCHAR2 (30) := 'update_material_txn';
2596 l_return_status VARCHAR2(1);
2597 l_org_id NUMBER;
2598 l_batch_id NUMBER;
2599 l_material_detail_id NUMBER;
2600 l_txn_type_id NUMBER;
2601 CURSOR Cur_get_trans(v_transaction_id IN NUMBER) IS
2602 SELECT organization_id, transaction_source_id, trx_source_line_id, transaction_type_id
2603 FROM mtl_material_transactions
2604 WHERE transaction_id = v_transaction_id;
2605 setup_failed EXCEPTION;
2606 update_txn_fail EXCEPTION;
2607 validate_txn_fail EXCEPTION;
2608 update_txn_mismatch EXCEPTION;
2609 BEGIN
2610 IF g_debug <= gme_debug.g_log_procedure THEN
2611 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2612 END IF;
2613 x_return_status := FND_API.G_RET_STS_SUCCESS;
2614 IF NOT(gme_common_pvt.g_setup_done) THEN
2615 gme_common_pvt.g_setup_done := gme_common_pvt.setup(p_org_id => p_mmti_rec.organization_id);
2616 IF NOT(gme_common_pvt.g_setup_done) THEN
2617 RAISE setup_failed;
2618 END IF;
2619 END IF;
2620 IF (gme_common_pvt.g_timestamp IS NULL) THEN
2621 gme_common_pvt.set_timestamp;
2622 END IF;
2623 OPEN Cur_get_trans(p_transaction_id);
2624 FETCH Cur_get_trans INTO l_org_id, l_batch_id, l_material_detail_id, l_txn_type_id;
2625 CLOSE Cur_get_trans;
2626 IF (p_mmti_rec.organization_id <> l_org_id
2627 OR p_mmti_rec.transaction_source_id <> l_batch_id
2628 OR p_mmti_rec.trx_source_line_id <> l_material_detail_id
2629 OR p_mmti_rec.transaction_type_id <> l_txn_type_id) THEN
2630 RAISE update_txn_mismatch;
2631 END IF;
2632 gme_transactions_pvt.gmo_pre_process_val(p_mmti_rec => p_mmti_rec,
2633 p_mmli_tbl => p_mmli_tbl,
2634 p_mode => 'U',
2635 x_return_status => l_return_status);
2636 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2637 RAISE validate_txn_fail;
2638 END IF;
2639 gme_transactions_pvt.update_material_txn(p_transaction_id => p_transaction_id,
2640 p_mmti_rec => p_mmti_rec,
2641 p_mmli_tbl => p_mmli_tbl,
2642 x_return_status => l_return_status);
2643 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2644 RAISE update_txn_fail;
2645 END IF;
2646 IF g_debug <= gme_debug.g_log_procedure THEN
2647 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2648 END IF;
2649 EXCEPTION
2650 WHEN setup_failed THEN
2651 x_return_status := FND_API.G_RET_STS_ERROR;
2652 WHEN update_txn_mismatch THEN
2653 gme_common_pvt.log_message('GME_TXN_UPDATE_MISMATCH');
2654 x_return_status := FND_API.G_RET_STS_ERROR;
2655 WHEN update_txn_fail OR validate_txn_fail THEN
2656 x_return_status := l_return_status;
2657 WHEN OTHERS THEN
2658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2659 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2660 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2661 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2662 END IF;
2663 END update_material_txn;
2664
2665 PROCEDURE delete_material_txn(p_organization_id IN NUMBER,
2666 p_transaction_id IN NUMBER,
2667 x_return_status OUT NOCOPY VARCHAR2) IS
2668 l_api_name CONSTANT VARCHAR2 (30) := 'delete_material_txn';
2669 l_return_status VARCHAR2(1);
2670 l_mmt_rec mtl_material_transactions%ROWTYPE;
2671 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
2672 l_mmti_rec mtl_transactions_interface%ROWTYPE;
2673 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
2674 setup_failed EXCEPTION;
2675 delete_txn_fail EXCEPTION;
2676 get_txn_fail EXCEPTION;
2677 const_txn_fail EXCEPTION;
2678 validate_txn_fail EXCEPTION;
2679 BEGIN
2680 IF g_debug <= gme_debug.g_log_procedure THEN
2681 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2682 END IF;
2683 x_return_status := FND_API.G_RET_STS_SUCCESS;
2684 IF NOT(gme_common_pvt.g_setup_done) THEN
2685 gme_common_pvt.g_setup_done := gme_common_pvt.setup(p_org_id => p_organization_id);
2686 IF NOT(gme_common_pvt.g_setup_done) THEN
2687 RAISE setup_failed;
2688 END IF;
2689 END IF;
2690 IF (gme_common_pvt.g_timestamp IS NULL) THEN
2691 gme_common_pvt.set_timestamp;
2692 END IF;
2693 gme_transactions_pvt.get_mmt_transactions(p_transaction_id => p_transaction_id,
2694 x_mmt_rec => l_mmt_rec,
2695 x_mmln_tbl => l_mmln_tbl,
2696 x_return_status => l_return_status);
2697 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2698 RAISE get_txn_fail;
2699 END IF;
2700 gme_transactions_pvt.construct_mmti(p_mmt_rec => l_mmt_rec,
2701 p_mmln_tbl => l_mmln_tbl,
2702 x_mmti_rec => l_mmti_rec,
2703 x_mmli_tbl => l_mmli_tbl,
2704 x_return_status => l_return_status);
2705 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2706 RAISE const_txn_fail;
2707 END IF;
2708 gme_transactions_pvt.gmo_pre_process_val(p_mmti_rec => l_mmti_rec,
2709 p_mmli_tbl => l_mmli_tbl,
2710 p_mode => 'D',
2711 x_return_status => l_return_status);
2712 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2713 RAISE validate_txn_fail;
2714 END IF;
2715 gme_transactions_pvt.delete_material_txn(p_transaction_id => p_transaction_id,
2716 x_return_status => l_return_status);
2717 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2718 RAISE delete_txn_fail;
2719 END IF;
2720 IF g_debug <= gme_debug.g_log_procedure THEN
2721 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2722 END IF;
2723 EXCEPTION
2724 WHEN setup_failed THEN
2725 x_return_status := FND_API.G_RET_STS_ERROR;
2726 WHEN delete_txn_fail OR get_txn_fail OR const_txn_fail OR validate_txn_fail THEN
2727 x_return_status := l_return_status;
2728 WHEN OTHERS THEN
2729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2730 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2731 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2732 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2733 END IF;
2734 END delete_material_txn;
2735
2736 PROCEDURE create_resource_txn(p_rsrc_txn_gtmp_rec IN gme_resource_txns_gtmp%ROWTYPE,
2737 x_rsrc_txn_gtmp_rec OUT NOCOPY gme_resource_txns_gtmp%ROWTYPE,
2738 x_return_status OUT NOCOPY VARCHAR2) IS
2739 l_api_name CONSTANT VARCHAR2 (30) := 'create_resource_txn';
2740 l_return_status VARCHAR2(1);
2741 l_hour_um VARCHAR2(3);
2742 l_line_id NUMBER;
2743 l_instance_id NUMBER;
2744 l_reason_id NUMBER;
2745 l_step_status NUMBER;
2746 l_usage_time NUMBER;
2747 l_txn_usage NUMBER;
2748 l_rsrc_trans_count NUMBER;
2749 l_trans_date DATE;
2750 l_batch_header_rec gme_batch_header%ROWTYPE;
2751 l_rsrc_txn_gtmp_rec gme_resource_txns_gtmp%ROWTYPE;
2752 l_step_resources gme_batch_step_resources%ROWTYPE;
2753 setup_failed EXCEPTION;
2754 create_txn_fail EXCEPTION;
2755 validate_txn_fail EXCEPTION;
2756 uom_conversion_err EXCEPTION;
2757 missing_profile_option EXCEPTION;
2758 rsrc_fetch_err EXCEPTION;
2759 rsrc_update_err EXCEPTION;
2760 reduce_pend_usage_err EXCEPTION;
2761 error_load_trans EXCEPTION;
2762 BEGIN
2763 IF g_debug <= gme_debug.g_log_procedure THEN
2764 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2765 END IF;
2766 x_return_status := FND_API.G_RET_STS_SUCCESS;
2767 IF NOT(gme_common_pvt.g_setup_done) THEN
2768 gme_common_pvt.g_setup_done := gme_common_pvt.setup(p_org_id => p_rsrc_txn_gtmp_rec.organization_id);
2769 IF NOT(gme_common_pvt.g_setup_done) THEN
2770 RAISE setup_failed;
2771 END IF;
2772 END IF;
2773 gme_common_pvt.set_timestamp;
2774 gme_resource_engine_pvt.validate_rsrc_txn_param(p_called_from => 3
2775 ,p_batchstep_rsrc_id => p_rsrc_txn_gtmp_rec.line_id
2776 ,p_org_code => gme_common_pvt.g_organization_code
2777 ,p_trans_date => p_rsrc_txn_gtmp_rec.trans_date
2778 ,p_start_date => p_rsrc_txn_gtmp_rec.start_date
2779 ,p_end_date => p_rsrc_txn_gtmp_rec.end_date
2780 ,p_usage => p_rsrc_txn_gtmp_rec.resource_usage
2781 ,p_reason_name => NULL
2782 ,p_reason_id => p_rsrc_txn_gtmp_rec.reason_id
2783 ,p_instance_no => NULL
2784 ,p_instance_id => p_rsrc_txn_gtmp_rec.instance_id
2785 ,x_line_id => l_line_id
2786 ,x_step_status => l_step_status
2787 ,x_batch_header_rec => l_batch_header_rec
2788 ,x_instance_id => l_instance_id
2789 ,x_reason_id => l_reason_id
2790 ,x_return_status => l_return_status
2791 ,x_trans_date => l_trans_date);
2792 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2793 RAISE validate_txn_fail;
2794 END IF;
2795 l_usage_time := (p_rsrc_txn_gtmp_rec.end_date - p_rsrc_txn_gtmp_rec.start_date) * 24;
2796 l_hour_um := fnd_profile.value_specific(name => 'BOM:HOUR_UOM_CODE'
2797 ,user_id => gme_common_pvt.g_user_ident);
2798 IF (l_hour_um IS NULL) THEN
2799 gme_common_pvt.log_message('GME_API_UNABLE_TO_GET_CONSTANT','CONSTANT_NAME','BOM:HOUR_UOM_CODE');
2800 RAISE missing_profile_option;
2801 END IF;
2802 IF l_hour_um <> p_rsrc_txn_gtmp_rec.trans_um THEN
2803 l_txn_usage := inv_convert.inv_um_convert (item_id => 0
2804 ,PRECISION => 5
2805 ,from_quantity => l_usage_time
2806 ,from_unit => l_hour_um
2807 ,to_unit => p_rsrc_txn_gtmp_rec.trans_um
2808 ,from_name => NULL
2809 ,to_name => NULL);
2810 IF (l_txn_usage = -99999) THEN
2811 gme_common_pvt.log_message ('GME_RSRC_USG_NT_CNV_SYUOM', 'SY_UOM', l_hour_um, 'RSRC_USG_UOM', p_rsrc_txn_gtmp_rec.trans_um);
2812 RAISE uom_conversion_err;
2813 END IF;
2814 ELSE
2815 l_txn_usage := l_usage_time;
2816 END IF;
2817 l_rsrc_txn_gtmp_rec := p_rsrc_txn_gtmp_rec;
2818 l_rsrc_txn_gtmp_rec.resource_usage := l_txn_usage;
2819 gme_resource_engine_pvt.create_resource_trans(p_tran_rec => l_rsrc_txn_gtmp_rec
2820 ,x_tran_rec => x_rsrc_txn_gtmp_rec
2821 ,x_return_status => l_return_status);
2822 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2823 RAISE create_txn_fail;
2824 END IF;
2825 l_step_resources.batchstep_resource_id := l_rsrc_txn_gtmp_rec.line_id;
2826 IF NOT Gme_Batch_Step_Resources_Dbl.fetch_row(p_batch_step_resources => l_step_resources
2827 ,x_batch_step_resources => l_step_resources) THEN
2828 RAISE rsrc_fetch_err;
2829 END IF;
2830 l_step_resources.actual_rsrc_usage := NVL (l_step_resources.actual_rsrc_usage, 0) + l_txn_usage;
2831 IF l_step_status = 2 THEN
2832 Gme_Trans_Engine_Util.load_rsrc_trans(p_batch_row => l_batch_header_rec
2833 ,x_rsc_row_count => l_rsrc_trans_count
2834 ,x_return_status => l_return_status);
2835 IF l_return_status <> x_return_status THEN
2836 RAISE error_load_trans;
2837 END IF;
2838 Gme_Update_Step_Qty_Pvt.reduce_pending_usage(p_batch_step_resources_rec => l_step_resources
2839 ,x_return_status => l_return_status);
2840 IF l_return_status <> 'S' THEN
2841 RAISE reduce_pend_usage_err;
2842 END IF;
2843 END IF;
2844 IF NOT Gme_Batch_Step_Resources_Dbl.update_row(p_batch_step_resources => l_step_resources) THEN
2845 RAISE rsrc_update_err;
2846 END IF;
2847 IF g_debug <= gme_debug.g_log_procedure THEN
2848 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2849 END IF;
2850 EXCEPTION
2851 WHEN setup_failed OR uom_conversion_err OR missing_profile_option OR rsrc_fetch_err OR rsrc_update_err THEN
2852 x_return_status := FND_API.G_RET_STS_ERROR;
2853 WHEN create_txn_fail OR validate_txn_fail OR error_load_trans OR reduce_pend_usage_err THEN
2854 x_return_status := l_return_status;
2855 WHEN OTHERS THEN
2856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2857 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2858 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2859 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2860 END IF;
2861 END create_resource_txn;
2862
2863 PROCEDURE update_resource_txn(p_rsrc_txn_gtmp_rec IN gme_resource_txns_gtmp%ROWTYPE,
2864 x_return_status OUT NOCOPY VARCHAR2) IS
2865 l_api_name CONSTANT VARCHAR2 (30) := 'update_resource_txn';
2866 l_return_status VARCHAR2(1);
2867 l_line_id NUMBER;
2868 l_instance_id NUMBER;
2869 l_reason_id NUMBER;
2870 l_step_status NUMBER;
2871 l_rsrc_trans_count NUMBER;
2872 l_trans_date DATE;
2873 l_batch_header_rec gme_batch_header%ROWTYPE;
2874 l_step_resources gme_batch_step_resources%ROWTYPE;
2875 l_new_step_resources gme_batch_step_resources%ROWTYPE;
2876 error_load_trans EXCEPTION;
2877 setup_failed EXCEPTION;
2878 update_txn_fail EXCEPTION;
2879 validate_txn_fail EXCEPTION;
2880 rsrc_fetch_err EXCEPTION;
2881 upd_rsrc_err EXCEPTION;
2882 get_usage_fail EXCEPTION;
2883 reduce_pend_usage_err EXCEPTION;
2884 BEGIN
2885 IF g_debug <= gme_debug.g_log_procedure THEN
2886 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2887 END IF;
2888 x_return_status := FND_API.G_RET_STS_SUCCESS;
2889 IF NOT(gme_common_pvt.g_setup_done) THEN
2890 gme_common_pvt.g_setup_done := gme_common_pvt.setup(p_org_id => p_rsrc_txn_gtmp_rec.organization_id);
2891 IF NOT(gme_common_pvt.g_setup_done) THEN
2892 RAISE setup_failed;
2893 END IF;
2894 END IF;
2895 gme_common_pvt.set_timestamp;
2896 gme_resource_engine_pvt.validate_rsrc_txn_param(p_called_from => 1
2897 ,p_batchstep_rsrc_id => p_rsrc_txn_gtmp_rec.line_id
2898 ,p_org_code => gme_common_pvt.g_organization_code
2899 ,p_trans_date => p_rsrc_txn_gtmp_rec.trans_date
2900 ,p_start_date => p_rsrc_txn_gtmp_rec.start_date
2901 ,p_end_date => p_rsrc_txn_gtmp_rec.end_date
2902 ,p_usage => p_rsrc_txn_gtmp_rec.resource_usage
2903 ,p_reason_name => NULL
2904 ,p_reason_id => p_rsrc_txn_gtmp_rec.reason_id
2905 ,p_instance_no => NULL
2906 ,p_instance_id => p_rsrc_txn_gtmp_rec.instance_id
2907 ,x_line_id => l_line_id
2908 ,x_step_status => l_step_status
2909 ,x_batch_header_rec => l_batch_header_rec
2910 ,x_instance_id => l_instance_id
2911 ,x_reason_id => l_reason_id
2912 ,x_return_status => l_return_status
2913 ,x_trans_date => l_trans_date);
2914 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2915 RAISE validate_txn_fail;
2916 END IF;
2917 gme_resource_engine_pvt.update_resource_trans(p_tran_rec => p_rsrc_txn_gtmp_rec
2918 ,x_return_status => l_return_status);
2919 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2920 RAISE update_txn_fail;
2921 END IF;
2922 l_step_resources.batchstep_resource_id := p_rsrc_txn_gtmp_rec.line_id;
2923 IF NOT Gme_Batch_Step_Resources_Dbl.fetch_row(p_batch_step_resources => l_step_resources
2924 ,x_batch_step_resources => l_step_resources) THEN
2925 RAISE rsrc_fetch_err;
2926 END IF;
2927 gme_resource_engine_pvt.get_resource_usage(p_step_resources_rec => l_step_resources
2928 ,x_step_resources_rec => l_new_step_resources
2929 ,x_return_status => l_return_status);
2930 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2931 RAISE get_usage_fail;
2932 END IF;
2933 IF (NVL(l_step_resources.actual_rsrc_usage, -1) <> NVL(l_new_step_resources.actual_rsrc_usage, -1)) THEN
2934 l_step_resources.actual_rsrc_usage := l_new_step_resources.actual_rsrc_usage;
2935 IF l_step_status = 2 THEN
2936 gme_update_step_qty_pvt.reduce_pending_usage(p_batch_step_resources_rec => l_step_resources,
2937 x_return_status => l_return_status);
2938 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2939 RAISE reduce_pend_usage_err;
2940 END IF;
2941 END IF;
2942 IF NOT gme_batch_step_resources_dbl.update_row(p_batch_step_resources => l_step_resources) THEN
2943 RAISE upd_rsrc_err;
2944 END IF;
2945 END IF;
2946 IF g_debug <= gme_debug.g_log_procedure THEN
2947 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2948 END IF;
2949 EXCEPTION
2950 WHEN setup_failed OR error_load_trans OR rsrc_fetch_err OR get_usage_fail OR upd_rsrc_err THEN
2951 x_return_status := FND_API.G_RET_STS_ERROR;
2952 WHEN update_txn_fail OR validate_txn_fail OR reduce_pend_usage_err THEN
2953 x_return_status := l_return_status;
2954 WHEN OTHERS THEN
2955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2956 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2957 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2958 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
2959 END IF;
2960 END update_resource_txn;
2961
2962 PROCEDURE delete_resource_txn(p_rsrc_txn_gtmp_rec IN gme_resource_txns_gtmp%ROWTYPE,
2963 x_return_status OUT NOCOPY VARCHAR2) IS
2964 l_api_name CONSTANT VARCHAR2 (30) := 'delete_resource_txn';
2965 l_asqc NUMBER;
2966 l_return_status VARCHAR2(1);
2967 l_step_status VARCHAR2(1);
2968 l_step_resources gme_batch_step_resources%ROWTYPE;
2969 l_new_step_resources gme_batch_step_resources%ROWTYPE;
2970 l_rsrc_txn_gtmp_rec gme_resource_txns_gtmp%ROWTYPE;
2971 CURSOR Cur_get_step(v_line_id NUMBER) IS
2972 SELECT s.step_status, h.automatic_step_calculation
2973 FROM gme_batch_steps s, gme_batch_step_activities a, gme_batch_step_resources r, gme_batch_header h
2974 WHERE r.batchstep_resource_id = v_line_id
2975 AND a.batchstep_activity_id = r.batchstep_activity_id
2976 AND s.batchstep_id = a.batchstep_id
2977 AND h.batch_id = s.batch_id;
2978 setup_failed EXCEPTION;
2979 delete_txn_fail EXCEPTION;
2980 fetch_txn_failed EXCEPTION;
2981 validation_fail EXCEPTION;
2982 rsrc_fetch_err EXCEPTION;
2983 upd_rsrc_err EXCEPTION;
2984 get_usage_fail EXCEPTION;
2985 reduce_pend_usage_err EXCEPTION;
2986 BEGIN
2987 IF g_debug <= gme_debug.g_log_procedure THEN
2988 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2989 END IF;
2990 x_return_status := FND_API.G_RET_STS_SUCCESS;
2991 IF NOT(gme_common_pvt.g_setup_done) THEN
2992 gme_common_pvt.g_setup_done := gme_common_pvt.setup(p_org_id => p_rsrc_txn_gtmp_rec.organization_id);
2993 IF NOT(gme_common_pvt.g_setup_done) THEN
2994 RAISE setup_failed;
2995 END IF;
2996 END IF;
2997 gme_common_pvt.set_timestamp;
2998 IF NOT gme_resource_txns_gtmp_dbl.fetch_row(p_rsrc_txn_gtmp_rec, l_rsrc_txn_gtmp_rec) THEN
2999 RAISE fetch_txn_failed;
3000 END IF;
3001 OPEN Cur_get_step(l_rsrc_txn_gtmp_rec.line_id);
3002 FETCH Cur_get_step INTO l_step_status, l_asqc;
3003 CLOSE Cur_get_step;
3004 IF l_step_status NOT IN (2, 3) THEN
3005 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
3006 RAISE validation_fail;
3007 END IF;
3008 IF (l_asqc = 1 AND l_step_status = 2) THEN
3009 gme_common_pvt.log_message ('GME_INV_STEP_STATUS_ASQC');
3010 RAISE validation_fail;
3011 END IF;
3012 gme_resource_engine_pvt.delete_resource_trans(p_tran_rec => l_rsrc_txn_gtmp_rec
3013 ,x_return_status => l_return_status);
3014 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3015 RAISE delete_txn_fail;
3016 END IF;
3017 l_step_resources.batchstep_resource_id := p_rsrc_txn_gtmp_rec.line_id;
3018 IF NOT Gme_Batch_Step_Resources_Dbl.fetch_row(p_batch_step_resources => l_step_resources
3019 ,x_batch_step_resources => l_step_resources) THEN
3020 RAISE rsrc_fetch_err;
3021 END IF;
3022 gme_resource_engine_pvt.get_resource_usage(p_step_resources_rec => l_step_resources
3023 ,x_step_resources_rec => l_new_step_resources
3024 ,x_return_status => l_return_status);
3025 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3026 RAISE get_usage_fail;
3027 END IF;
3028 IF (NVL(l_step_resources.actual_rsrc_usage, -1) <> NVL(l_new_step_resources.actual_rsrc_usage, -1)) THEN
3029 l_step_resources.actual_rsrc_usage := l_new_step_resources.actual_rsrc_usage;
3030 IF l_step_status = 2 THEN
3031 gme_update_step_qty_pvt.reduce_pending_usage(p_batch_step_resources_rec => l_step_resources,
3032 x_return_status => l_return_status);
3033 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3034 RAISE reduce_pend_usage_err;
3035 END IF;
3036 END IF;
3037 IF NOT gme_batch_step_resources_dbl.update_row(p_batch_step_resources => l_step_resources) THEN
3038 RAISE upd_rsrc_err;
3039 END IF;
3040 END IF;
3041 IF g_debug <= gme_debug.g_log_procedure THEN
3042 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3043 END IF;
3044 EXCEPTION
3045 WHEN setup_failed OR fetch_txn_failed OR validation_fail OR rsrc_fetch_err OR upd_rsrc_err THEN
3046 x_return_status := FND_API.G_RET_STS_ERROR;
3047 WHEN delete_txn_fail OR get_usage_fail OR reduce_pend_usage_err THEN
3048 x_return_status := l_return_status;
3049 WHEN OTHERS THEN
3050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3051 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3052 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3053 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
3054 END IF;
3055 END delete_resource_txn;
3056 END gme_api_grp;