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