[Home] [Help]
PACKAGE BODY: APPS.GME_MATERIAL_DETAIL_PVT
Source
1 PACKAGE BODY gme_material_detail_pvt AS
2 /* $Header: GMEVMTLB.pls 120.36.12020000.3 2012/09/19 17:19:04 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name VARCHAR2 (30) := 'gme_material_detail_pvt';
5
6 PROCEDURE insert_material_line
7 (p_batch_header_rec IN gme_batch_header%ROWTYPE
8 ,p_material_detail_rec IN gme_material_details%ROWTYPE
9 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
10 ,p_trans_id IN NUMBER
11 ,x_transacted OUT NOCOPY VARCHAR2
12 ,x_return_status OUT NOCOPY VARCHAR2
13 ,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE)
14 IS
15 l_api_name CONSTANT VARCHAR2 (30) := 'insert_material_line';
16 l_material_detail_rec gme_material_details%ROWTYPE;
17 l_batchstep_items gme_batch_step_items%ROWTYPE;
18 l_batch_step_rec gme_batch_steps%ROWTYPE;
19 l_out_material_detail_tbl gme_common_pvt.material_details_tab;
20 l_material_detail_tbl gme_common_pvt.material_details_tab;
21 l_recipe_id NUMBER;
22 l_message_count NUMBER;
23 l_message_list VARCHAR2 (2000);
24 l_proc VARCHAR2 (100);
25 l_rsc_count NUMBER;
26 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
27 l_return_status varchar2(1);
28 l_config_id NUMBER;
29
30 -- Bug 12674978
31 l_subinventory VARCHAR2 (10);
32 l_locator_id NUMBER;
33
34 CURSOR recipe_validity_rule_cursor (v_recipe_validity_rule_id NUMBER)
35 IS
36 SELECT recipe_id
37 FROM gmd_recipe_validity_rules
38 WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
39
40 error_dbl EXCEPTION;
41 error_processing EXCEPTION;
42 error_dispensing EXCEPTION;
43
44 BEGIN
45 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
46 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
47 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_header_rec.batch_id);
48 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batchstep_id='||p_batch_step_rec.batchstep_id);
49 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_no='||p_material_detail_rec.line_no);
50 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type='||p_material_detail_rec.line_type);
51 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' inventory_item_id='||p_material_detail_rec.inventory_item_id);
52 END IF;
53
54 /* Set the return status to success initially */
55 x_return_status := fnd_api.g_ret_sts_success;
56
57 -- renumber subsequent lines
58 UPDATE gme_material_details
59 SET line_no = line_no + 1
60 ,last_updated_by = gme_common_pvt.g_user_ident
61 ,last_update_date = gme_common_pvt.g_timestamp
62 ,last_update_login = gme_common_pvt.g_login_id
63 WHERE batch_id = p_material_detail_rec.batch_id
64 AND line_type = p_material_detail_rec.line_type
65 AND line_no >= p_material_detail_rec.line_no;
66
67 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
68 gme_debug.put_line (g_pkg_name||'.'|| l_api_name||': '||SQL%ROWCOUNT||' records renumbered');
69 END IF;
70
71 -- insert new material line
72 IF NOT gme_material_details_dbl.insert_row (p_material_detail_rec
73 ,x_material_detail_rec) THEN
74 l_proc := 'gme_material_details_dbl.insert_row';
75 RAISE error_dbl;
76 END IF;
77
78 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
79 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' inserted material; material_detail_id= '
80 || x_material_detail_rec.material_detail_id);
81 END IF;
82
83 -- Bug 10046018 - set the defaults properly.
84 IF (x_material_detail_rec.subinventory IS NULL OR x_material_detail_rec.locator_id IS NULL) THEN
85 IF (x_material_detail_rec.line_type = -1) THEN
86 gme_common_pvt.get_supply_defaults
87 (p_organization_id => x_material_detail_rec.organization_id
88 ,p_inventory_item_id => x_material_detail_rec.inventory_item_id
89 ,x_subinventory => l_subinventory
90 ,x_locator_id => l_locator_id
91 ,x_return_status => l_return_status);
92 ELSE
93 gme_common_pvt.get_yield_defaults
94 (p_organization_id => x_material_detail_rec.organization_id
95 ,p_inventory_item_id => x_material_detail_rec.inventory_item_id
96 ,p_line_type => x_material_detail_rec.line_type
97 ,x_subinventory => l_subinventory
98 ,x_locator_id => l_locator_id
99 ,x_return_status => l_return_status);
100 END IF;
101
102 -- Bug 12674978 - Only set the values if they are null.
103 IF x_material_detail_rec.subinventory IS NULL THEN
104 x_material_detail_rec.subinventory := l_subinventory;
105 END IF;
106
107 IF x_material_detail_rec.locator_id IS NULL THEN
108 x_material_detail_rec.locator_id := l_locator_id;
109 END IF;
110 END IF;
111
112 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
113 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' default subinventory is '|| x_material_detail_rec.subinventory);
114 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' default locator_id is '|| x_material_detail_rec.locator_id);
115 END IF;
116 -- End Bug 10046018
117
118 --FPbug#4543872 moved this code over here
119 -- can call this regardless of batch/step status... will also handle if batch is pending and qty is 0 (will do nothing)
120 open_and_process_actual_qty
121 (p_batch_header_rec => p_batch_header_rec
122 ,p_material_detail_rec => x_material_detail_rec
123 ,p_batch_step_rec => p_batch_step_rec
124 ,p_trans_id => p_trans_id
125 ,p_insert => FND_API.g_true
126 ,x_transacted => x_transacted
127 ,x_return_status => x_return_status);
128
129 IF x_return_status <> fnd_api.g_ret_sts_success THEN
130 l_proc := 'open_and_process_actual_qty';
131 RAISE error_processing;
132 END IF;
133
134 -- item / step association
135 IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
136 l_batchstep_items.material_detail_id := x_material_detail_rec.material_detail_id;
137 l_batchstep_items.batch_id := x_material_detail_rec.batch_id;
138 l_batchstep_items.batchstep_id := p_batch_step_rec.batchstep_id;
139
140 IF NOT gme_batch_step_items_dbl.insert_row
141 (p_batch_step_items => l_batchstep_items
142 ,x_batch_step_items => l_batchstep_items) THEN
143 l_proc := 'gme_batch_step_items_dbl.insert_row';
144 RAISE error_dbl;
145 END IF;
146 END IF;
147
148 l_material_detail_rec := x_material_detail_rec;
149
150 gme_common_pvt.calc_mtl_req_date
151 (p_batch_header_rec => p_batch_header_rec
152 ,p_batchstep_rec => p_batch_step_rec
153 ,p_mtl_dtl_rec => l_material_detail_rec
154 ,x_mtl_req_date => x_material_detail_rec.material_requirement_date
155 ,x_return_status => x_return_status);
156
157 IF x_return_status <> fnd_api.g_ret_sts_success THEN
158 l_proc := 'gme_common_pvt.calc_mtl_req_date';
159 RAISE error_processing;
160 END IF;
161
162 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
163 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' material_reqirement_date= '
164 || TO_CHAR(x_material_detail_rec.material_requirement_date,
165 gme_material_detail_pvt.g_date_fmt));
166 END IF;
167 /* Bug 4866700 added update inventory check */
168 IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing AND
169 x_material_detail_rec.phantom_type = 0 AND
170 p_batch_header_rec.update_inventory_ind = 'Y' THEN
171 l_material_detail_tbl (1) := x_material_detail_rec;
172
173 -- add material line into invisible move order
174 gme_move_orders_pvt.create_move_order_lines
175 (p_move_order_header_id => p_batch_header_rec.move_order_header_id
176 ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
177 ,p_material_details_tbl => l_material_detail_tbl
178 ,x_material_details_tbl => l_out_material_detail_tbl
179 ,x_trolin_tbl => l_trolin_tbl
180 ,x_return_status => x_return_status);
181
182 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
183 l_proc := 'gme_move_orders_pvt.create_move_order_lines';
184 RAISE error_processing;
185 END IF;
186
187 x_material_detail_rec := l_out_material_detail_tbl(1);
188
189 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
190 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' move_order_line_id= '
191 ||x_material_detail_rec.move_order_line_id);
192 END IF;
193
194 IF p_batch_header_rec.recipe_validity_rule_id IS NULL THEN -- LCF batch
195 l_recipe_id := NULL;
196 ELSE
197 OPEN recipe_validity_rule_cursor(p_batch_header_rec.recipe_validity_rule_id);
198 FETCH recipe_validity_rule_cursor INTO l_recipe_id;
199 CLOSE recipe_validity_rule_cursor;
200 --Pawan Kumar add for bug 5365883
201 -- moved the END IF after dispense item processing
202 --END IF;
203
204 gmo_dispense_grp.is_dispense_item
205 (p_api_version => 1.0
206 ,p_init_msg_list => fnd_api.g_false
207 ,x_return_status => l_return_status
208 ,x_msg_count => l_message_count
209 ,x_msg_data => l_message_list
210 ,p_inventory_item_id => x_material_detail_rec.inventory_item_id
211 ,p_organization_id => x_material_detail_rec.organization_id
212 ,p_recipe_id => l_recipe_id
213 ,x_dispense_required => x_material_detail_rec.dispense_ind
214 ,x_dispense_config_id => l_config_id);
215
216 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
217 l_proc := 'gmo_dispense_grp.is_dispense_item';
218 -- Pawan Kumar 01-10-2006 bug 4742244 *
219 -- Raising exception after is_dispense_item procedure returns error *
220 RAISE error_dispensing;
221 END IF;
222
223 -- Pawan Kumar bug 4947535 new code added for GMO changes
224 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND x_material_detail_rec.dispense_ind = 'Y') then
225 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
226 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'Instantiating material line id'
227 || x_material_detail_rec.material_detail_id);
228 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' config id ' || l_config_id);
229 END IF;
230 GMO_DISPENSE_GRP.INSTANTIATE_DISPENSE_SETUP
231 (p_api_version => 1.0
232 ,p_dispense_config_id => l_config_id
233 ,p_entity_name => GMO_DISPENSE_GRP.G_MATERIAL_LINE_ENTITY
234 ,p_entity_key => x_material_detail_rec.material_detail_id
235 ,p_init_msg_list => FND_API.G_FALSE
236 ,x_return_status => l_return_status
237 ,x_msg_count => l_message_count
238 ,x_msg_data => l_message_list);
239 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
240 l_proc := 'gmo_dispense_grp.instantiate_dispense_setup';
241 RAISE error_processing;
242 END IF;
243 END IF;
244 END IF; -- IF recipe_id null
245 END IF;
246
247 IF NOT gme_material_details_dbl.update_row (x_material_detail_rec) THEN
248 l_proc := 'gme_material_details_dbl.update_row';
249 RAISE error_dbl;
250 END IF;
251
252 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
253 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' updated material; material_detail_id= '
254 || x_material_detail_rec.material_detail_id);
255 END IF;
256
257 -- call gme_trans_engine_util.load_rsrc_trans in preparation for update step qty
258 IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
259 gme_trans_engine_util.load_rsrc_trans
260 (p_batch_row => p_batch_header_rec
261 ,x_rsc_row_count => l_rsc_count
262 ,x_return_status => x_return_status);
263
264 IF x_return_status <> fnd_api.g_ret_sts_success THEN
265 l_proc := 'gme_trans_engine_util.load_rsrc_trans';
266 RAISE error_processing;
267 END IF;
268
269 gme_update_step_qty_pvt.update_step_qty
270 (p_batch_step_rec => p_batch_step_rec
271 ,x_message_count => l_message_count
272 ,x_message_list => l_message_list
273 ,x_return_status => x_return_status
274 ,x_batch_step_rec => l_batch_step_rec);
275
276 IF x_return_status <> fnd_api.g_ret_sts_success THEN
277 l_proc := 'gme_update_step_qty_pvt.update_step_qty';
278 RAISE error_processing;
279 END IF;
280 END IF;
281
282 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
283 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
284 END IF;
285
286 EXCEPTION
287 WHEN error_processing THEN
288 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
289 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': ' || l_proc|| ' error returned');
290 END IF;
291 WHEN error_dbl THEN
292 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
293 x_return_status := FND_API.g_ret_sts_unexp_error;
294
295 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
296 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '
297 || l_proc|| ' unexpected error: '|| SQLERRM);
298 END IF;
299
300 WHEN error_dispensing THEN
301 gme_common_pvt.log_message ('GME_DISPENSE_NON_RESERVE');
302 x_return_status := l_return_status;
303 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
304 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '
305 || l_proc|| 'Dispensing error returned ');
306 END IF;
307 WHEN OTHERS THEN
308 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
309 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
310 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
311 END IF;
312 x_return_status := FND_API.g_ret_sts_unexp_error;
313 END insert_material_line;
314
315 PROCEDURE validate_batch_for_matl_ins
316 (p_batch_header_rec IN gme_batch_header%ROWTYPE
317 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
318 ,x_return_status OUT NOCOPY VARCHAR2) IS
319
320 l_api_name CONSTANT VARCHAR2 (30) := 'validate_batch_for_matl_ins';
321
322
323 validation_error EXCEPTION;
324
325 BEGIN
326 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
327 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
328 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_header_rec.batch_id);
329 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batchstep_id='||p_batch_step_rec.batchstep_id);
330 END IF;
331
332 /* Set the return status to success initially */
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334
335 --Bug#5078853 added check for invalid batch type
336 IF p_batch_header_rec.batch_type <> 0 THEN
337 gme_common_pvt.log_message ('GME_INV_BATCH_TYPE_OPER');
338 RAISE validation_error;
339 END IF;
340
341 IF (p_batch_header_rec.batch_status NOT IN
342 (gme_common_pvt.g_batch_pending
343 ,gme_common_pvt.g_batch_wip
344 ,gme_common_pvt.g_batch_completed )) THEN
345 gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
346 RAISE validation_error;
347 END IF;
348
349 IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
350 IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
351 ,gme_common_pvt.g_step_wip
352 ,gme_common_pvt.g_step_completed)) THEN
353 gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
354 RAISE validation_error;
355 END IF;
356 END IF;
357
358 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
359 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
360 END IF;
361
362 EXCEPTION
363 WHEN validation_error THEN
364 x_return_status := fnd_api.g_ret_sts_error;
365 WHEN OTHERS THEN
366 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
367 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
368 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
369 END IF;
370 x_return_status := FND_API.g_ret_sts_unexp_error;
371 END validate_batch_for_matl_ins;
372
373 /***********************************************************************
374 *
375 * Following fields are not used and will be ignored/not populated:
376 * 1. cost,
377 * 2. item_um,
378 * 3. item_um2
379 * 4. alloc_ind
380 *
381
382 *
383 * Following fields are not supported to be populated by API
384 * 1. text_code
385 *
386 *
387 ***********************************************************************/
388 PROCEDURE validate_material_for_ins (
389 p_batch_header_rec IN gme_batch_header%ROWTYPE
390 ,p_material_detail_rec IN gme_material_details%ROWTYPE
391 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
392 ,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE
393 ,x_return_status OUT NOCOPY VARCHAR2)
394 IS
395 l_item_rec mtl_system_items_b%ROWTYPE;
396 l_status NUMBER;
397 l_subinventory VARCHAR2(30);
398 l_api_name CONSTANT VARCHAR2 (30) := 'validate_material_for_ins';
399 l_val_proc VARCHAR2 (100);
400 val_error EXCEPTION;
401 BEGIN
402 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
403 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
404 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_header_rec.batch_id);
405 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batchstep_id='||p_batch_step_rec.batchstep_id);
406 END IF;
407
408 /* Set the return status to success initially */
409 x_return_status := FND_API.G_RET_STS_SUCCESS;
410
411 -- set output structure
412 x_material_detail_rec := p_material_detail_rec;
413
414 x_material_detail_rec.batch_id := p_batch_header_rec.batch_id;
415 x_material_detail_rec.organization_id := p_batch_header_rec.organization_id;
416 x_material_detail_rec.formulaline_id := NULL;
417
418 -- Item_ID
419 validate_item_id (p_org_id => p_batch_header_rec.organization_id
420 ,p_item_id => p_material_detail_rec.inventory_item_id
421 ,x_item_rec => l_item_rec
422 ,x_return_status => x_return_status);
423
424 IF x_return_status <> fnd_api.g_ret_sts_success THEN
425 l_val_proc := 'validate_item_id';
426 RAISE val_error;
427 END IF;
428
429 -- Revision
430 validate_revision (p_item_rec => l_item_rec
431 ,p_revision => p_material_detail_rec.revision
432 ,x_return_status => x_return_status);
433
434 IF x_return_status <> fnd_api.g_ret_sts_success THEN
435 l_val_proc := 'validate_revision';
436 RAISE val_error;
437 END IF;
438
439 -- Line_Type
440 validate_line_type (p_line_type => p_material_detail_rec.line_type
441 ,x_return_status => x_return_status);
442
443 IF x_return_status <> fnd_api.g_ret_sts_success THEN
444 l_val_proc := 'validate_line_type';
445 RAISE val_error;
446 END IF;
447
448 -- ByProduct_Type
449 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
450 validate_byproduct_type
451 (p_byproduct_type => p_material_detail_rec.by_product_type
452 ,x_return_status => x_return_status);
453
454 IF x_return_status <> fnd_api.g_ret_sts_success THEN
455 l_val_proc := 'validate_byproduct_type';
456 RAISE val_error;
457 END IF;
458 ELSE
459 x_material_detail_rec.by_product_type := NULL;
460 END IF;
461
462 -- Line_no
463 validate_line_no (
464 p_line_no => p_material_detail_rec.line_no
465 ,p_line_type => p_material_detail_rec.line_type
466 ,p_batch_id => p_batch_header_rec.batch_id
467 ,x_line_no => x_material_detail_rec.line_no
468 ,x_return_status => x_return_status);
469
470 IF x_return_status <> fnd_api.g_ret_sts_success THEN
471 l_val_proc := 'validate_line_no';
472 RAISE val_error;
473 END IF;
474
475 -- Dtl_UM
476 validate_dtl_um (p_dtl_um => p_material_detail_rec.dtl_um
477 ,p_primary_uom => l_item_rec.primary_uom_code
478 ,p_item_id => p_material_detail_rec.inventory_item_id
479 ,p_org_id => x_material_detail_rec.organization_id
480 ,x_return_status => x_return_status);
481
482 IF x_return_status <> fnd_api.g_ret_sts_success THEN
483 l_val_proc := 'validate_dtl_um';
484 RAISE val_error;
485 END IF;
486
487 /* Bug#5078853 moved this code over here */
488 -- Release_Type
489 -- following should be validated in form
490 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod AND
491 p_material_detail_rec.by_product_type = 'S' THEN
492 x_material_detail_rec.release_type := 1;
493 ELSIF p_material_detail_rec.release_type IS NULL THEN
494 x_material_detail_rec.release_type := gme_common_pvt.g_release_type;
495 ELSE
496 validate_release_type
497 (p_material_detail_rec => p_material_detail_rec
498 ,p_release_type => p_material_detail_rec.release_type
499 ,x_return_status => x_return_status);
500
501 IF x_return_status <> fnd_api.g_ret_sts_success THEN
502 l_val_proc := 'validate_release_type';
503 RAISE val_error;
504 END IF;
505 END IF;
506
507 -- Calculate status of material based on release type, association and batch/step status
508 l_status := p_batch_header_rec.batch_status;
509 IF p_material_detail_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
510 p_batch_step_rec.batchstep_id IS NOT NULL THEN
511 l_status := p_batch_step_rec.step_status;
512 END IF;
513
514 -- Plan_Qty
515 IF l_status = gme_common_pvt.g_batch_pending THEN
516 validate_plan_qty (p_plan_qty => p_material_detail_rec.plan_qty
517 ,x_return_status => x_return_status);
518
519 IF x_return_status <> fnd_api.g_ret_sts_success THEN
520 l_val_proc := 'validate_plan_qty';
521 RAISE val_error;
522 END IF;
523 ELSE
524 x_material_detail_rec.plan_qty := 0;
525 END IF;
526
527 -- WIP_Plan_Qty
528 IF l_status <> gme_common_pvt.g_batch_wip THEN
529 x_material_detail_rec.wip_plan_qty := NULL;
530 ELSE
531 validate_wip_plan_qty
532 (p_wip_plan_qty => p_material_detail_rec.wip_plan_qty
533 ,x_return_status => x_return_status);
534 --sunitha ch. bug rework#5333521 assign 0 to wip_plan_qty if it is NULL
535 IF p_material_detail_rec.wip_plan_qty IS NULL THEN
536 x_material_detail_rec.wip_plan_qty:=0;
537 END IF;
538 IF x_return_status <> fnd_api.g_ret_sts_success THEN
539 l_val_proc := 'validate_wip_plan_qty';
540 RAISE val_error;
541 END IF;
542 END IF;
543
544 -- Actual_Qty
545 IF p_material_detail_rec.actual_qty IS NULL OR
546 p_material_detail_rec.actual_qty = 0 THEN
547
548 x_material_detail_rec.actual_qty := 0;
549 ELSE
550 IF l_status NOT IN ( gme_common_pvt.g_batch_wip,
551 gme_common_pvt.g_batch_completed ) THEN
552 gme_common_pvt.log_message ('GME_INV_STAT_UPD_ACT');
553 RAISE val_error;
554 END IF;
555 validate_actual_qty (p_actual_qty => x_material_detail_rec.actual_qty
556 ,x_return_status => x_return_status);
557
558 IF x_return_status <> fnd_api.g_ret_sts_success THEN
559 l_val_proc := 'validate_actual_qty';
560 RAISE val_error;
561 END IF;
562 END IF;
563
564
565 /* Bug#5078853 modified validation for scrap factor */
566 -- Scrap_Factor
567 IF p_batch_header_rec.batch_status <> gme_common_pvt.g_batch_pending OR
568 p_material_detail_rec.scrap_factor IS NULL OR
569 p_material_detail_rec.scrap_factor = 0 OR
570 p_material_detail_rec.line_type <> gme_common_pvt.g_line_type_ing THEN
571 x_material_detail_rec.scrap_factor := 0;
572 ELSE
573 validate_scrap_factor(p_scrap => p_material_detail_rec.scrap_factor
574 ,x_return_status => x_return_status);
575
576 IF x_return_status <> fnd_api.g_ret_sts_success THEN
577 l_val_proc := 'validate_scrap_factor';
578 RAISE val_error;
579 END IF;
580
581 x_material_detail_rec.scrap_factor := p_material_detail_rec.scrap_factor / 100;
582 /* nsinghi Bug4911461 Re-work. Modify plan qty to include scrap. */
583 x_material_detail_rec.plan_qty := x_material_detail_rec.plan_qty +
584 (x_material_detail_rec.scrap_factor * x_material_detail_rec.plan_qty);
585
586 END IF;
587
588
589 -- Scale_Type
590 validate_scale_type (p_scale_type => p_material_detail_rec.scale_type
591 ,x_return_status => x_return_status);
592
593 IF x_return_status <> fnd_api.g_ret_sts_success THEN
594 l_val_proc := 'validate_scale_type';
595 RAISE val_error;
596 END IF;
597
598 IF p_material_detail_rec.scale_type = 2 THEN -- integer scaling
599 -- Scale_Multiple
600 validate_scale_multiple
601 (p_scale_mult => p_material_detail_rec.scale_multiple
602 ,x_return_status => x_return_status);
603
604 IF x_return_status <> fnd_api.g_ret_sts_success THEN
605 l_val_proc := 'validate_scale_multiple';
606 RAISE val_error;
607 END IF;
608
609 -- Scale_Rounding_Variance
610 validate_scale_round_var
611 (p_scale_var => p_material_detail_rec.scale_rounding_variance
612 ,x_return_status => x_return_status);
613
614 IF x_return_status <> fnd_api.g_ret_sts_success THEN
615 l_val_proc := 'validate_scale_round_var';
616 RAISE val_error;
617 END IF;
618
619 x_material_detail_rec.scale_rounding_variance :=
620 p_material_detail_rec.scale_rounding_variance / 100;
621
622 -- Rounding_Direction
623 validate_rounding_direction
624 (p_round_dir => p_material_detail_rec.rounding_direction
625 ,x_return_status => x_return_status);
626
627 IF x_return_status <> fnd_api.g_ret_sts_success THEN
628 l_val_proc := 'validate_rounding_direction';
629 RAISE val_error;
630 END IF;
631 ELSE
632 x_material_detail_rec.scale_multiple := NULL;
633 x_material_detail_rec.scale_rounding_variance := NULL;
634 x_material_detail_rec.rounding_direction := NULL;
635 END IF;
636
637 -- Cost_Alloc
638 IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod THEN
639 -- validate 0 <= cost_alloc <= 1
640 -- at save_batch, check that sum(cost_alloc for all products) <= 1
641 validate_cost_alloc
642 (p_material_detail_rec => p_material_detail_rec
643 ,x_return_status => x_return_status);
644
645 IF x_return_status <> fnd_api.g_ret_sts_success THEN
646 l_val_proc := 'validate_cost_alloc';
647 RAISE val_error;
648 END IF;
649 ELSE
650 x_material_detail_rec.cost_alloc := NULL;
651 END IF;
652
653 -- Phantom_Type
654 IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending AND
655 x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
656 validate_phantom_type
657 (p_phantom_type => p_material_detail_rec.phantom_type
658 ,x_return_status => x_return_status);
659
660 IF x_return_status <> fnd_api.g_ret_sts_success THEN
661 l_val_proc := 'validate_phantom_type';
662 RAISE val_error;
663 END IF;
664 ELSE
665 x_material_detail_rec.phantom_type := 0;
666 END IF;
667
668 -- Contribute_Yield_Ind
669 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
670 validate_contr_yield_ind
671 (p_contr_yield_ind => p_material_detail_rec.contribute_yield_ind
672 ,x_return_status => x_return_status);
673
674 IF x_return_status <> fnd_api.g_ret_sts_success THEN
675 l_val_proc := 'validate_contr_yield_ind';
676 RAISE val_error;
677 END IF;
678 ELSE
679 x_material_detail_rec.contribute_yield_ind := 'Y';
680 END IF;
681
682 -- Contribute_Step_Qty_Ind
683 IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
684 validate_contr_step_qty_ind
685 (p_contr_step_qty_ind => p_material_detail_rec.contribute_step_qty_ind
686 ,x_return_status => x_return_status);
687
688 IF x_return_status <> fnd_api.g_ret_sts_success THEN
689 l_val_proc := 'validate_contr_step_qty_ind';
690 RAISE val_error;
691 END IF;
692 ELSE
693 x_material_detail_rec.contribute_step_qty_ind := NULL;
694 END IF;
695
696 -- Subinventory and Locator
697 IF p_material_detail_rec.subinventory = fnd_api.g_miss_char THEN
698 x_material_detail_rec.subinventory := NULL;
699 x_material_detail_rec.locator_id := NULL;
700 ELSIF p_material_detail_rec.subinventory IS NULL THEN
701 --Bug#5078853 Begin get the default sub inv and locator either from gme_parameters or from Item Master
702 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
703 gme_common_pvt.get_supply_defaults ( p_organization_id => x_material_detail_rec.organization_id
704 ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
705 ,x_subinventory => x_material_detail_rec.subinventory
706 ,x_locator_id => x_material_detail_rec.locator_id
707 ,x_return_status => x_return_status );
708 IF x_return_status <> fnd_api.g_ret_sts_success THEN
709 l_val_proc := 'get_supply_defaults';
710 RAISE val_error;
711 END IF;
712 ELSE
713 gme_common_pvt.get_yield_defaults ( p_organization_id => x_material_detail_rec.organization_id
714 ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
715 ,p_line_type => p_material_detail_rec.line_type
716 ,x_subinventory => x_material_detail_rec.subinventory
717 ,x_locator_id => x_material_detail_rec.locator_id
718 ,x_return_status => x_return_status );
719 IF x_return_status <> fnd_api.g_ret_sts_success THEN
720 l_val_proc := 'get_yield_defaults';
721 RAISE val_error;
722 END IF;
723 END IF;
724 --Bug#5078853 End
725 ELSE -- subinventory is not NULL
726 validate_subinventory
727 (p_item_rec => l_item_rec
728 ,p_subinv => x_material_detail_rec.subinventory
729 ,x_return_status => x_return_status);
730
731 IF x_return_status <> fnd_api.g_ret_sts_success THEN
732 l_val_proc := 'validate_subinventory';
733 RAISE val_error;
734 END IF;
735
736 IF p_material_detail_rec.locator_id = fnd_api.g_miss_num THEN
737 x_material_detail_rec.locator_id := NULL;
738 ELSIF p_material_detail_rec.locator_id IS NULL THEN
739 /* Bug#5078853 Begin if we come here then locator id is not being passed from public api
740 so get the default locator id to validate with passed sub inv */
741 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
742 gme_common_pvt.get_supply_defaults ( p_organization_id => x_material_detail_rec.organization_id
743 ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
744 ,x_subinventory => l_subinventory
745 ,x_locator_id => x_material_detail_rec.locator_id
746 ,x_return_status => x_return_status );
747 IF x_return_status <> fnd_api.g_ret_sts_success THEN
748 l_val_proc := 'get_supply_defaults';
749 RAISE val_error;
750 END IF;
751 ELSE
752 gme_common_pvt.get_yield_defaults ( p_organization_id => x_material_detail_rec.organization_id
753 ,p_inventory_item_id => p_material_detail_rec.inventory_item_id
754 ,p_line_type => p_material_detail_rec.line_type
755 ,x_subinventory => l_subinventory
756 ,x_locator_id => x_material_detail_rec.locator_id
757 ,x_return_status => x_return_status );
758 IF x_return_status <> fnd_api.g_ret_sts_success THEN
759 l_val_proc := 'get_yield_defaults';
760 RAISE val_error;
761 END IF;
762 END IF;
763 --Bug#5078853 End
764 END IF;
765
766 IF x_material_detail_rec.locator_id IS NOT NULL THEN
767 -- subinventory is not NULL, locator is not null
768 validate_locator
769 (p_subinv => x_material_detail_rec.subinventory
770 ,p_locator_id => x_material_detail_rec.locator_id
771 ,p_item_rec => l_item_rec
772 ,p_line_type => x_material_detail_rec.line_type
773 ,x_return_status => x_return_status);
774
775 IF x_return_status <> fnd_api.g_ret_sts_success THEN
776 l_val_proc := 'validate_locator';
777 RAISE val_error;
778 END IF;
779 END IF; -- IF x_material_detail_rec.locator_id IS NOT NULL ...
780 END IF; -- IF x_material_detail_rec.subinventory = ...
781
782 /* Bug#5078853 added the following call for flex field validation
783 gme_common_pvt.g_flex_validate_prof has to be set in public API to enforce flex field validation */
784 gme_validate_flex_fld_pvt.validate_flex_material_details
785 ( p_material_detail_rec => p_material_detail_rec
786 ,x_material_detail_rec => x_material_detail_rec
787 ,x_return_status => x_return_status);
788 IF x_return_status <> fnd_api.g_ret_sts_success THEN
789 l_val_proc := 'validate_flex_material_details';
790 RAISE val_error;
791 END IF;
792
793 x_material_detail_rec.move_order_line_id := NULL;
794 x_material_detail_rec.phantom_id := NULL;
795 x_material_detail_rec.phantom_line_id := NULL;
796 x_material_detail_rec.backordered_qty := 0;
797 x_material_detail_rec.original_qty := x_material_detail_rec.plan_qty;
798
799 get_converted_qty
800 (p_org_id => x_material_detail_rec.organization_id
801 ,p_item_id => x_material_detail_rec.inventory_item_id
802 ,p_lot_number => NULL
803 ,p_qty => x_material_detail_rec.original_qty
804 ,p_from_um => x_material_detail_rec.dtl_um
805 ,p_to_um => l_item_rec.primary_uom_code
806 ,x_conv_qty => x_material_detail_rec.original_primary_qty
807 ,x_return_status => x_return_status);
808
809 IF x_return_status <> fnd_api.g_ret_sts_success THEN
810 l_val_proc := 'get_converted_qty';
811 RAISE val_error;
812 END IF;
813
814 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
815 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' original_primary_qty= '
816 || x_material_detail_rec.original_primary_qty);
817 END IF;
818
819 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
820 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
821 END IF;
822 EXCEPTION
823 WHEN val_error THEN
824 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
825 gme_debug.put_line (g_pkg_name||'.'||l_api_name||': validation error from proc: '|| l_val_proc);
826 END IF;
827 x_return_status := FND_API.g_ret_sts_error;
828 WHEN OTHERS THEN
829 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
830 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
831 gme_debug.put_line ('Unexpected error: '||g_pkg_name||'.'||l_api_name||': '||SQLERRM);
832 END IF;
833 x_return_status := FND_API.g_ret_sts_unexp_error;
834 END validate_material_for_ins;
835
836 PROCEDURE open_and_process_actual_qty (
837 p_batch_header_rec IN gme_batch_header%ROWTYPE
838 ,p_material_detail_rec IN gme_material_details%ROWTYPE
839 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE DEFAULT NULL
840 ,p_trans_id IN NUMBER
841 ,p_insert IN VARCHAR2
842 ,x_transacted OUT NOCOPY VARCHAR2
843 ,x_return_status OUT NOCOPY VARCHAR2)
844 IS
845 CURSOR item_no_cursor (v_org_id NUMBER, v_inventory_item_id NUMBER)
846 IS
847 SELECT concatenated_segments
848 FROM mtl_system_items_kfv
849 WHERE inventory_item_id = v_inventory_item_id
850 AND organization_id = v_org_id;
851
852 l_item_no mtl_system_items_kfv.concatenated_segments%TYPE;
853 l_trans_id NUMBER;
854 l_item_rec mtl_system_items_b%ROWTYPE;
855
856 open_actual_qty_error EXCEPTION;
857 open_actual_qty_unexp_error EXCEPTION;
858 process_actual_qty_error EXCEPTION;
859 error_get_rec EXCEPTION;
860
861 l_api_name CONSTANT VARCHAR2 (30) := 'open_and_process_actual_qty';
862 l_field_name CONSTANT VARCHAR2 (20) := 'actual_qty';
863
864
865 BEGIN
866 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
867 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
868 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_header_rec.batch_id);
869 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_detail_rec.material_detail_id);
870 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batchstep_id='||p_batch_step_rec.batchstep_id);
871 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' actual_qty='||p_material_detail_rec.actual_qty);
872 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_trans_id='||p_trans_id);
873 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_insert='||p_insert);
874 END IF;
875
876 /* Set the return status to success initially */
877 x_return_status := FND_API.G_RET_STS_SUCCESS;
878 x_transacted := FND_API.g_false;
879
880 get_item_rec (p_org_id => p_batch_header_rec.organization_id
881 ,p_item_id => p_material_detail_rec.inventory_item_id
882 ,x_item_rec => l_item_rec
883 ,x_return_status => x_return_status);
884
885 IF x_return_status <> fnd_api.g_ret_sts_success THEN
886 RAISE error_get_rec;
887 END IF;
888
889 IF p_trans_id IS NULL THEN
890 /* Bug 5441643 Added NVL condition for location control code*/
891 l_trans_id :=
892 open_actual_qty (p_material_detail_rec => p_material_detail_rec
893 ,p_batch_status => p_batch_header_rec.batch_status
894 ,p_update_inventory_ind => p_batch_header_rec.update_inventory_ind
895 ,p_batchstep_id => p_batch_step_rec.batchstep_id
896 ,p_step_status => p_batch_step_rec.step_status
897 ,p_lot_control_code => l_item_rec.lot_control_code
898 ,p_location_control_code => NVL(l_item_rec.location_control_code,1)
899 ,p_restrict_locators_code => l_item_rec.restrict_locators_code
900 ,p_insert => p_insert);
901 ELSE
902 l_trans_id := p_trans_id;
903 END IF;
904
905 IF l_trans_id = -1 THEN
906 IF p_material_detail_rec.actual_qty = 0 THEN
907 -- not an error because actual quantity is 0 when the field is closed
908 NULL;
909 ELSE
910 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
911 gme_debug.put_line(g_pkg_name||'.'||l_api_name||': open_actual_qty returned -1; can not update actual qty');
912 END IF;
913 RAISE open_actual_qty_error;
914 END IF;
915 ELSIF l_trans_id = -2 THEN
916 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
917 gme_debug.put_line(g_pkg_name||'.'||l_api_name||': open_actual_qty returned -2 raised unexpected error');
918 END IF;
919 RAISE open_actual_qty_unexp_error;
920 END IF;
921
922 IF p_batch_header_rec.update_inventory_ind = 'Y' AND
923 (l_trans_id > 0 OR
924 (l_trans_id = 0 AND p_material_detail_rec.actual_qty > 0)) THEN
925 process_actual_qty (p_batch_header_rec => p_batch_header_rec
926 ,p_material_detail_rec => p_material_detail_rec
927 ,p_batch_step_rec => p_batch_step_rec
928 ,p_trans_id => l_trans_id
929 ,p_item_rec => l_item_rec
930 ,x_return_status => x_return_status);
931
932 IF x_return_status <> fnd_api.g_ret_sts_success THEN
933 RAISE process_actual_qty_error;
934 END IF;
935
936 x_transacted := FND_API.g_true;
937 END IF;
938
939 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
940 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
941 END IF;
942
943 EXCEPTION
944 WHEN error_get_rec THEN
945 NULL;
946 WHEN open_actual_qty_error THEN
947 OPEN item_no_cursor (p_batch_header_rec.organization_id, l_item_rec.inventory_item_id);
948 FETCH item_no_cursor INTO l_item_no;
949 CLOSE item_no_cursor;
950
951 gme_common_pvt.log_message ('GME_UPD_ACTUAL_QTY_ERR'
952 ,'ITEM_NO'
953 ,l_item_no);
954 x_return_status := fnd_api.g_ret_sts_error;
955 WHEN open_actual_qty_unexp_error THEN
956 x_return_status := fnd_api.g_ret_sts_unexp_error;
957 WHEN process_actual_qty_error THEN
958 NULL;
959 WHEN OTHERS THEN
960 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
961
962 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
963 gme_debug.put_line ( 'Unexpected error: '
964 || g_pkg_name
965 || '.'
966 || l_api_name
967 || ': '
968 || SQLERRM);
969 END IF;
970
971 x_return_status := fnd_api.g_ret_sts_unexp_error;
972 END open_and_process_actual_qty;
973
974 /* **********************
975 * open_actual_qty returns following:
976 * -2 => unexpected error: caller should raise unexpected error
977 * -1 => open actual qty: No
978 * 0 => open actual qty: Yes with no transactions; must create a transaction
979 * >0 => open actual qty: Yes with transaction id returned; must update existing transaction
980 * **********************
981 */
982 FUNCTION open_actual_qty (
983 p_material_detail_rec IN gme_material_details%ROWTYPE
984 ,p_batch_status IN NUMBER
985 ,p_update_inventory_ind IN VARCHAR2
986 ,p_batchstep_id IN NUMBER DEFAULT NULL
987 ,p_step_status IN NUMBER DEFAULT NULL
988 ,p_lot_control_code IN NUMBER DEFAULT NULL
989 ,p_location_control_code IN NUMBER DEFAULT NULL
990 ,p_restrict_locators_code IN NUMBER DEFAULT NULL
991 ,p_insert IN VARCHAR2)
992 RETURN NUMBER
993 IS
994 CURSOR cur_get_trans (v_material_detail_id NUMBER, v_batch_id NUMBER)
995 IS
996 SELECT transaction_id
997 FROM mtl_material_transactions
998 WHERE transaction_source_id = v_batch_id
999 AND trx_source_line_id = v_material_detail_id
1000 AND transaction_id NOT IN (
1001 SELECT transaction_id1
1002 FROM gme_transaction_pairs
1003 WHERE batch_id = v_batch_id
1004 AND material_detail_id = v_material_detail_id
1005 AND pair_type = gme_common_pvt.g_pairs_reversal_type);
1006
1007 CURSOR cur_get_mtl_trxn_lot (v_trans_id NUMBER)
1008 IS
1009 SELECT COUNT (1)
1010 FROM mtl_transaction_lot_numbers
1011 WHERE transaction_id = v_trans_id;
1012
1013 CURSOR cur_sub_control (v_org_id NUMBER, v_subinventory VARCHAR2)
1014 IS
1015 SELECT locator_type
1016 FROM mtl_secondary_inventories
1017 WHERE organization_id = v_org_id
1018 AND secondary_inventory_name = v_subinventory;
1019 /* Bug 5441643 Added NVL condition for location control code*/
1020 /* Bug 5681997 Added mtl_transactions_enabled_flag from item master */
1021 CURSOR cur_get_item_attrib (v_org_id NUMBER, v_item_id NUMBER) IS
1022 SELECT lot_control_code, NVL(location_control_code, 1) location_control_code, restrict_locators_code,
1023 mtl_transactions_enabled_flag
1024 FROM mtl_system_items_b
1025 WHERE organization_id = v_org_id
1026 AND inventory_item_id = v_item_id;
1027
1028 CURSOR cur_get_step_status (v_step_id NUMBER)
1029 IS
1030 SELECT step_status
1031 FROM gme_batch_steps
1032 WHERE batchstep_id = v_step_id;
1033 --Bug#5129153 To find out if the item is revision controlled or not Start.
1034 CURSOR cur_get_rev_code (v_org_id NUMBER,v_item_id NUMBER)
1035 IS
1036 SELECT revision_qty_control_code
1037 FROM mtl_system_items_b
1038 WHERE organization_id = v_org_id
1039 AND inventory_item_id = v_item_id;
1040 --Bug#5129153 End.
1041 l_count_trans NUMBER;
1042 l_step_status NUMBER;
1043 l_trans_id NUMBER;
1044 l_mtl_dtl_rec gme_material_details%ROWTYPE;
1045 l_count_lot_trans NUMBER;
1046 l_release_type NUMBER;
1047 l_sub_locator_type NUMBER;
1048 l_txn_action NUMBER;
1049 l_eff_locator_control NUMBER;
1050 l_lot_control_code NUMBER;
1051 l_location_control_code NUMBER;
1052 l_restrict_locators_code NUMBER;
1053 l_mtl_txn_enabled_flag VARCHAR2(1);
1054 --Bug#5129153
1055 l_rev_code NUMBER;
1056 l_api_name CONSTANT VARCHAR2 (30) := 'open_actual_qty';
1057
1058 BEGIN
1059
1060 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1061 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1062 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_material_detail_rec.batch_id);
1063 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_detail_rec.material_detail_id);
1064 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_status='||p_batch_status);
1065 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_update_inventory_ind='||p_update_inventory_ind);
1066 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batchstep_id='||p_batchstep_id);
1067 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_step_status='||p_step_status);
1068 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_lot_control_code='||p_lot_control_code);
1069 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_location_control_code='||p_location_control_code);
1070 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_restrict_locators_code='||p_restrict_locators_code);
1071 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_insert='||p_insert);
1072 END IF;
1073
1074 l_mtl_dtl_rec := p_material_detail_rec;
1075
1076 /* Auto, manual, incremental or auto by step with following number of transactions...
1077 * subinventory and locator can be specified on the material detail line
1078
1079 * plain and subinv specified - 0 or 1 transaction
1080 * locator and locator specifed - 0 or 1 transaction
1081
1082 * plain and subinv not specified - 1 transaction
1083 * locator and locator not specifed - 1 transaction
1084
1085 * lot - 1 transaction
1086 */
1087
1088 l_release_type := l_mtl_dtl_rec.release_type;
1089 IF l_release_type = gme_common_pvt.g_mtl_autobystep_release AND
1090 p_batchstep_id IS NULL THEN
1091 l_release_type := gme_common_pvt.g_mtl_auto_release;
1092 END IF;
1093
1094 -- if it's auto by step, ensure we have the step status...
1095 IF l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN
1096 IF p_step_status IS NULL THEN
1097 OPEN cur_get_step_status(p_batchstep_id);
1098 FETCH cur_get_step_status INTO l_step_status;
1099 CLOSE cur_get_step_status;
1100 ELSE
1101 l_step_status := p_step_status;
1102 END IF;
1103 END IF;
1104
1105 -- Not open for products/byproducts that are not in completed state (auto) or
1106 -- wip/completed (manual/incremental)
1107
1108 IF l_mtl_dtl_rec.line_type IN
1109 (gme_common_pvt.g_line_type_prod
1110 ,gme_common_pvt.g_line_type_byprod)
1111 AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1112 AND l_step_status <> gme_common_pvt.g_step_completed)
1113 OR (l_release_type = gme_common_pvt.g_mtl_auto_release
1114 AND p_batch_status <> gme_common_pvt.g_batch_completed)
1115 OR (l_release_type IN (gme_common_pvt.g_mtl_manual_release,
1116 gme_common_pvt.g_mtl_incremental_release)
1117 AND p_batch_status NOT IN (gme_common_pvt.g_batch_wip
1118 ,gme_common_pvt.g_batch_completed))
1119 ) THEN
1120 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1121 gme_debug.put_line
1122 ( g_pkg_name
1123 || '.'
1124 || l_api_name
1125 || 'matl_dtl_id= '
1126 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1127 || ': return -1: product/byproduct not in correct state');
1128 END IF;
1129
1130 RETURN -1;
1131 END IF;
1132
1133 -- Not open for ingredients that are not in WIP or completed state
1134 IF l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing
1135 AND ( (l_release_type = gme_common_pvt.g_mtl_autobystep_release
1136 AND l_step_status NOT IN
1137 (gme_common_pvt.g_step_wip
1138 ,gme_common_pvt.g_step_completed) )
1139 -- following for manual, incremental and auto
1140 OR (p_batch_status NOT IN
1141 (gme_common_pvt.g_batch_wip
1142 ,gme_common_pvt.g_batch_completed) ) ) THEN
1143 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1144 gme_debug.put_line
1145 ( g_pkg_name
1146 || '.'
1147 || l_api_name
1148 || 'matl_dtl_id= '
1149 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1150 || ': return -1: ingredient not in wip or completed state');
1151 END IF;
1152
1153 RETURN -1;
1154 END IF;
1155
1156 -- if this is an insert, no need to go frther because there are no transactions...
1157 -- return 0 to indicate no transactions and that open is allowed
1158
1159 /* FPbug#4543872 removed 'p_insert = fnd_api.G_TRUE OR' from IF condition
1160 because even if we are inserting new material line we need to check for other
1161 conditions like lot control, locator control, subinventory availaility etc. anyway
1162 no.of transactions will be zero for new material line that is being inserted
1163 */
1164 IF ( p_update_inventory_ind = 'N') THEN
1165 RETURN 0;
1166 END IF;
1167
1168 OPEN cur_get_trans (l_mtl_dtl_rec.material_detail_id
1169 ,l_mtl_dtl_rec.batch_id);
1170
1171 FETCH cur_get_trans INTO l_trans_id;
1172
1173 IF cur_get_trans%FOUND THEN
1174 l_count_trans := 1;
1175
1176 FETCH cur_get_trans
1177 INTO l_trans_id;
1178
1179 IF cur_get_trans%FOUND THEN
1180 l_count_trans := 2;
1181 -- no need to continue to count how many; too many already
1182 END IF;
1183 ELSE
1184 -- if not found, set trans_id to 0, indicating that there are no transactions
1185 l_count_trans := 0;
1186 l_trans_id := 0;
1187 END IF;
1188
1189 CLOSE cur_get_trans;
1190
1191 IF l_count_trans = 2 THEN
1192 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1193 gme_debug.put_line
1194 ( g_pkg_name
1195 || '.'
1196 || l_api_name
1197 || 'matl_dtl_id= '
1198 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1199 || ': return -1: more than 1 trxn in mtl_material_transactions');
1200 END IF;
1201
1202 RETURN -1;
1203 END IF;
1204
1205 /* Bug 5681997 get value from cursor to variable */
1206 -- retrieve the item attributes
1207
1208 OPEN cur_get_item_attrib(l_mtl_dtl_rec.organization_id, l_mtl_dtl_rec.inventory_item_id);
1209 FETCH cur_get_item_attrib INTO l_lot_control_code, l_location_control_code, l_restrict_locators_code, l_mtl_txn_enabled_flag;
1210 CLOSE cur_get_item_attrib;
1211 /* Bug 5681997 if item is not txn enabled then open actual qty */
1212 IF (l_mtl_txn_enabled_flag = 'N') THEN
1213 RETURN 0;
1214 END IF;
1215 -- If it's lot control, ensure that there's only 1 transaction and there is only 1 entry
1216 -- in mtl_transaction_lots
1217 IF (l_lot_control_code = 2) THEN
1218 IF l_count_trans = 0 THEN
1219 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1220 gme_debug.put_line
1221 ( g_pkg_name
1222 || '.'
1223 || l_api_name
1224 || 'matl_dtl_id= '
1225 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1226 || ': return -1: lot ctrl with 0 trxn in mtl_material_transactions');
1227 END IF;
1228
1229 RETURN -1;
1230 ELSE
1231 OPEN cur_get_mtl_trxn_lot (l_trans_id);
1232
1233 FETCH cur_get_mtl_trxn_lot
1234 INTO l_count_lot_trans;
1235
1236 CLOSE cur_get_mtl_trxn_lot;
1237
1238 -- check MTL_TRANSACTION_LOT_NUMBERS table; join on transaction_id = l_trans_id
1239 -- if there is more than 1 record there, then can't update these transactions
1240 -- in essence, this is more than 1 transaction.
1241 IF l_count_lot_trans > 1 THEN
1242 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1243 gme_debug.put_line
1244 ( g_pkg_name
1245 || '.'
1246 || l_api_name
1247 || 'matl_dtl_id= '
1248 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1249 || ': return -1: 1 trxn in mtl_material_transactions with more than 1 lot entry in mtl_transaction_lot_numbers'
1250 || ' trans_id= '
1251 || TO_CHAR (l_trans_id) );
1252 END IF;
1253
1254 RETURN -1;
1255 ELSE
1256 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1257 gme_debug.put_line
1258 ( g_pkg_name
1259 || '.'
1260 || l_api_name
1261 || 'matl_dtl_id= '
1262 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1263 || ': return trans_id: success lot ctrl with 1 trxn: trans_id= '
1264 || TO_CHAR (l_trans_id) );
1265 END IF;
1266
1267 RETURN l_trans_id;
1268 END IF;
1269 END IF;
1270 END IF;
1271
1272 -- At this point, the item is either plain or locator control... if there is 1 transaction,
1273 -- then actual qty can be updated, if there are none, then we have to check if matl has
1274 -- subinventory/locator specified on it in order to create the transaction
1275 IF l_count_trans = 1 THEN
1276 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1277 gme_debug.put_line
1278 ( g_pkg_name
1279 || '.'
1280 || l_api_name
1281 || 'matl_dtl_id= '
1282 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1283 || ': return trans_id: success plain or locator with 1 trxn: trans_id= '
1284 || TO_CHAR (l_trans_id) );
1285 END IF;
1286
1287 RETURN l_trans_id;
1288 END IF;
1289
1290 -- There are no transactions, so a transaction must be created
1291 --Bug#5129153 Check if the item is revision controlled. Start.
1292 OPEN cur_get_rev_code(l_mtl_dtl_rec.organization_id,l_mtl_dtl_rec.inventory_item_id);
1293 FETCH cur_get_rev_code INTO l_rev_code;
1294 CLOSE cur_get_rev_code;
1295 --Bug#5129153 End.
1296 IF l_mtl_dtl_rec.subinventory IS NULL THEN
1297 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1298 gme_debug.put_line
1299 ( g_pkg_name
1300 || '.'
1301 || l_api_name
1302 || 'matl_dtl_id= '
1303 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1304 || ': return -1: plain or locator; 0 transactions with subinventory on material NULL');
1305 END IF;
1306
1307 RETURN -1;
1308 ELSE
1309
1310 OPEN cur_sub_control (l_mtl_dtl_rec.organization_id, l_mtl_dtl_rec.subinventory);
1311 FETCH cur_sub_control INTO l_sub_locator_type;
1312 CLOSE cur_sub_control;
1313
1314 IF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing) THEN
1315 l_txn_action := gme_common_pvt.g_ing_issue_txn_action;
1316 ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_prod) THEN
1317 l_txn_action := gme_common_pvt.g_prod_comp_txn_action;
1318 ELSIF (l_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_byprod) THEN
1319 l_txn_action := gme_common_pvt.g_byprod_comp_txn_action;
1320 END IF;
1321 --Bug#5129153 If item is revision controlled and revision field is NULL then return -1. Start.
1322 IF l_rev_code = 2 AND l_mtl_dtl_rec.revision IS NULL THEN
1323 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1324 gme_debug.put_line
1325 ( g_pkg_name
1326 || '.'
1327 || l_api_name
1328 || 'matl_dtl_id= '
1329 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1330 || ': return -1: Plain item which is revision controlled but has revision field as NULL ');
1331 END IF;
1332 RETURN -1;
1333 END IF;
1334 --Bug#5129153 End.
1335 /* Bug 5441643 Added NVL condition for location control code*/
1336 l_eff_locator_control :=
1337 gme_common_pvt.eff_locator_control
1338 (p_organization_id => l_mtl_dtl_rec.organization_id
1339 ,p_org_control => gme_common_pvt.g_org_locator_control
1340 ,p_subinventory => l_mtl_dtl_rec.subinventory
1341 ,p_sub_control => l_sub_locator_type
1342 ,p_item_control => NVL(l_location_control_code,1)
1343 ,p_item_loc_restrict => l_restrict_locators_code
1344 ,p_action => l_txn_action);
1345
1346 IF l_eff_locator_control = 1 THEN -- No locator control
1347 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1348 gme_debug.put_line
1349 ( g_pkg_name
1350 || '.'
1351 || l_api_name
1352 || 'matl_dtl_id= '
1353 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1354 || ': return trans_id: success plain with 0 trxn and with subinv on matl specified: trans_id= '
1355 || TO_CHAR (l_trans_id) );
1356 END IF;
1357
1358 RETURN l_trans_id;
1359 ELSE -- locator control
1360 -- ensure there is a locator_id on the material
1361 IF l_mtl_dtl_rec.locator_id IS NULL THEN
1362 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1363 gme_debug.put_line
1364 ( g_pkg_name
1365 || '.'
1366 || l_api_name
1367 || 'matl_dtl_id= '
1368 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1369 || ': return -1: locator ctrl with 0 transactions and locator_id on material NULL');
1370 END IF;
1371
1372 RETURN -1;
1373 ELSE
1374 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1375 gme_debug.put_line
1376 ( g_pkg_name
1377 || '.'
1378 || l_api_name
1379 || 'matl_dtl_id= '
1380 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1381 || ': return trans_id: success locator with 0 trxn and with subinv/locator on matl specified: trans_id= '
1382 || TO_CHAR (l_trans_id) );
1383 END IF;
1384
1385 RETURN l_trans_id;
1386 END IF;
1387 END IF;
1388 END IF;
1389
1390 -- shouldn't get to this point...
1391 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1392 gme_debug.put_line
1393 ( g_pkg_name
1394 || '.'
1395 || l_api_name
1396 || 'matl_dtl_id= '
1397 || TO_CHAR (l_mtl_dtl_rec.material_detail_id)
1398 || ': return -1: fall through all conditions; programming error; figure out why code got here');
1399 END IF;
1400
1401 RETURN -1;
1402 -- -1 means you can't create a transaction; 0 means there are no transactions
1403 -- but you can create transactions; other is trans_id => 1 and only 1 trans exists
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1407
1408 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1409 gme_debug.put_line ( 'Unexpected error: '
1410 || g_pkg_name
1411 || '.'
1412 || l_api_name
1413 || ': '
1414 || SQLERRM);
1415 END IF;
1416
1417 RETURN -2;
1418 END open_actual_qty;
1419
1420 PROCEDURE process_actual_qty (
1421 p_batch_header_rec IN gme_batch_header%ROWTYPE
1422 ,p_material_detail_rec IN gme_material_details%ROWTYPE
1423 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
1424 DEFAULT NULL
1425 ,p_trans_id IN NUMBER
1426 ,p_item_rec IN mtl_system_items_b%ROWTYPE
1427 ,x_return_status OUT NOCOPY VARCHAR2)
1428 IS
1429
1430 l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_ACTUAL_QTY';
1431
1432 l_mmt_rec mtl_material_transactions%ROWTYPE;
1433 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1434
1435 l_mmti_rec mtl_transactions_interface%ROWTYPE;
1436 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1437
1438 l_cnt NUMBER DEFAULT 0;
1439 l_secondary_qty NUMBER;
1440 l_primary_qty NUMBER;
1441 l_multiplier NUMBER;
1442
1443 error_trans EXCEPTION;
1444
1445 BEGIN
1446
1447 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1448 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1449 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_header_rec.batch_id);
1450 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_material_detail_rec.material_detail_id);
1451 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batchstep_id='||p_batch_step_rec.batchstep_id);
1452 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' trans_id='||p_trans_id);
1453 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' actual_qty='||p_material_detail_rec.actual_qty);
1454 END IF;
1455
1456 x_return_status := fnd_api.g_ret_sts_success;
1457
1458 -- Begin Bug 13743650 Moved this piece of code up
1459 IF p_item_rec.secondary_uom_code IS NULL THEN
1460 l_secondary_qty := NULL;
1461 ELSE
1462 l_secondary_qty := p_material_detail_rec.actual_qty;
1463 IF p_material_detail_rec.dtl_um <> p_item_rec.secondary_uom_code THEN
1464 l_secondary_qty := 0;
1465 get_converted_qty (
1466 p_org_id => p_material_detail_rec.organization_id
1467 ,p_item_id => p_material_detail_rec.inventory_item_id
1468 ,p_lot_number => NULL
1469 ,p_qty => p_material_detail_rec.actual_qty
1470 ,p_from_um => p_material_detail_rec.dtl_um
1471 ,p_to_um => p_item_rec.secondary_uom_code
1472 ,x_conv_qty => l_secondary_qty
1473 ,x_return_status => x_return_status);
1474 END IF;
1475 END IF;
1476
1477 -- If the new actual qty is zero we need to delete the transaction
1478 IF (p_material_detail_rec.actual_qty = 0 AND p_trans_id > 0) THEN
1479
1480 gme_transactions_pvt.delete_material_txn
1481 (p_transaction_id => p_trans_id
1482 ,p_txns_pair => NULL
1483 ,x_return_status => x_return_status);
1484
1485 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1486 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1487 gme_debug.put_line
1488 ( g_pkg_name
1489 || '.'
1490 || l_api_name
1491 || ' return '
1492 || x_return_status
1493 || ' from gme_transactions_pvt.delete_material_txn');
1494 END IF;
1495
1496 RAISE error_trans;
1497 END IF;
1498
1499 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1500 gme_debug.put_line ( g_pkg_name
1501 || '.'
1502 || l_api_name
1503 || 'deleted transaction for trans_id= '
1504 || TO_CHAR (p_trans_id) );
1505 END IF;
1506 ELSE
1507 /* Bug 5681997 added condition to touch txns only if enabled */
1508 IF p_trans_id = 0 AND p_item_rec.mtl_transactions_enabled_flag = 'Y' THEN -- insert new txn
1509 -- Bug 12563379
1510 l_multiplier := 1;
1511 IF p_material_detail_rec.line_type = -1 THEN
1512 l_multiplier := -1;
1513 END IF;
1514
1515 -- Bug 12563379 - Let's see if a transaction already exists before inserting a new one.
1516 -- Use the qty check to exclude returns based on multiple overtypes.
1517 IF (l_multiplier < 0) THEN
1518 SELECT count(*)
1519 INTO l_cnt
1520 FROM mtl_material_transactions_temp
1521 WHERE transaction_source_id = p_batch_header_rec.batch_id
1522 AND trx_source_line_id = p_material_detail_rec.material_detail_id
1523 AND transaction_quantity < 0;
1524 ELSE
1525 SELECT count(*)
1526 INTO l_cnt
1527 FROM mtl_material_transactions_temp
1528 WHERE transaction_source_id = p_batch_header_rec.batch_id
1529 AND trx_source_line_id = p_material_detail_rec.material_detail_id
1530 AND transaction_quantity > 0;
1531 END IF;
1532
1533 IF l_cnt = 1 THEN
1534 -- Let's update the existing transaction in the temp table.
1535
1536 -- 13976194 handle primary qty also.
1537 l_primary_qty := p_material_detail_rec.actual_qty;
1538 IF p_material_detail_rec.dtl_um <> p_item_rec.primary_uom_code THEN
1539 l_primary_qty := 0;
1540 get_converted_qty (
1541 p_org_id => p_material_detail_rec.organization_id
1542 ,p_item_id => p_material_detail_rec.inventory_item_id
1543 ,p_lot_number => NULL
1544 ,p_qty => p_material_detail_rec.actual_qty
1545 ,p_from_um => p_material_detail_rec.dtl_um
1546 ,p_to_um => p_item_rec.primary_uom_code
1547 ,x_conv_qty => l_primary_qty
1548 ,x_return_status => x_return_status);
1549 END IF;
1550
1551 IF l_secondary_qty IS NOT NULL THEN
1552 l_secondary_qty := l_multiplier * l_secondary_qty;
1553 END IF;
1554
1555 -- 13976194 handle primary qty also.
1556 IF (l_multiplier < 0) THEN
1557 UPDATE mtl_material_transactions_temp
1558 SET transaction_quantity = (l_multiplier * p_material_detail_rec.actual_qty),
1559 primary_quantity = (l_multiplier * l_primary_qty),
1560 secondary_transaction_quantity = l_secondary_qty
1561 WHERE transaction_source_type_id = 5
1562 AND transaction_source_id = p_batch_header_rec.batch_id
1563 AND trx_source_line_id = p_material_detail_rec.material_detail_id
1564 AND transaction_quantity < 0;
1565 ELSE
1566 UPDATE mtl_material_transactions_temp
1567 SET transaction_quantity = (l_multiplier * p_material_detail_rec.actual_qty),
1568 primary_quantity = (l_multiplier * l_primary_qty),
1569 secondary_transaction_quantity = l_secondary_qty
1570 WHERE transaction_source_type_id = 5
1571 AND transaction_source_id = p_batch_header_rec.batch_id
1572 AND trx_source_line_id = p_material_detail_rec.material_detail_id
1573 AND transaction_quantity > 0;
1574 END IF;
1575
1576 -- END Bug 12563379
1577 ELSE
1578 -- construct new transaction; will be plain or locator
1579 construct_trans_row
1580 (p_matl_dtl_rec => p_material_detail_rec
1581 ,p_item_rec => p_item_rec
1582 ,p_batch_hdr_rec => p_batch_header_rec
1583 ,p_batch_step_rec => p_batch_step_rec
1584 ,x_mmti_rec => l_mmti_rec
1585 ,x_return_status => x_return_status);
1586
1587 gme_transactions_pvt.create_material_txn
1588 (p_mmti_rec => l_mmti_rec
1589 ,p_mmli_tbl => l_mmli_tbl
1590 ,p_phantom_trans => 0
1591 ,x_return_status => x_return_status);
1592
1593 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1594 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1595 gme_debug.put_line
1596 ( g_pkg_name
1597 || '.'
1598 || l_api_name
1599 || ' return '
1600 || x_return_status
1601 || ' from gme_transactions_pvt.create_material_txn');
1602 END IF;
1603
1604 RAISE error_trans;
1605 END IF;
1606
1607 --FPbug#4543872
1608 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1609 gme_debug.put_line
1610 ( g_pkg_name
1611 || '.'
1612 || l_api_name
1613 ||' transaction header id after create mtl txn'
1614 || gme_common_pvt.g_transaction_header_id);
1615 END IF;
1616 END IF;
1617 ELSE
1618 /* Bug 5681997 added condition to touch txns only if enabled */
1619 IF p_item_rec.mtl_transactions_enabled_flag = 'Y' THEN
1620 -- Actual qty is non zero. Need to update the transaction.
1621 gme_transactions_pvt.get_transactions
1622 (p_transaction_id => p_trans_id
1623 ,x_mmt_rec => l_mmt_rec
1624 ,x_mmln_tbl => l_mmln_tbl
1625 ,x_return_status => x_return_status);
1626 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1627 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1628 gme_debug.put_line( g_pkg_name|| '.'|| l_api_name|| ' return '|| x_return_status|| ' from gme_transactions_pvt.get_transactions');
1629 END IF;
1630 RAISE error_trans;
1631 END IF;
1632 l_mmt_rec.transaction_quantity := p_material_detail_rec.actual_qty;
1633 -- Bug 13743650 - Assign secondary qty with a value instead of NULL.
1634 l_mmt_rec.secondary_transaction_quantity := l_secondary_qty;
1635
1636 --FPbug#4543872 Added IF condition to check the count
1637 IF l_mmln_tbl.COUNT > 0 THEN
1638 IF l_mmln_tbl(1).lot_number IS NOT NULL THEN
1639
1640 -- Bug 11939155 - Initialize primary to null
1641 l_mmt_rec.primary_quantity := NULL;
1642 l_mmln_tbl(1).primary_quantity := NULL;
1643
1644 l_mmln_tbl(1).transaction_quantity := p_material_detail_rec.actual_qty;
1645
1646 -- Bug 13743650 - Assign secondary qty with a value instead of NULL.
1647 l_mmln_tbl(1).secondary_transaction_quantity := l_secondary_qty;
1648 END IF;
1649 END IF;
1650 gme_transactions_pvt.update_material_txn
1651 (p_mmt_rec => l_mmt_rec
1652 ,p_mmln_tbl => l_mmln_tbl
1653 ,x_return_status => x_return_status);
1654
1655 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1656 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1657 gme_debug.put_line( g_pkg_name|| '.'|| l_api_name|| ' return '|| x_return_status|| ' from gme_transactions_pvt.create_material_txn');
1658 END IF;
1659 RAISE error_trans;
1660 END IF;
1661 END IF;
1662 END IF;
1663 END IF;
1664
1665 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1666 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1667 END IF;
1668
1669 EXCEPTION
1670 WHEN error_trans THEN
1671 NULL;
1672 WHEN OTHERS THEN
1673 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1674
1675 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1676 gme_debug.put_line ( 'Unexpected error: '
1677 || g_pkg_name
1678 || '.'
1679 || l_api_name
1680 || ': '
1681 || SQLERRM);
1682 END IF;
1683
1684 x_return_status := fnd_api.g_ret_sts_unexp_error;
1685 END process_actual_qty;
1686
1687 PROCEDURE construct_trans_row (
1688 p_matl_dtl_rec IN gme_material_details%ROWTYPE
1689 ,p_item_rec IN mtl_system_items_b%ROWTYPE
1690 ,p_batch_hdr_rec IN gme_batch_header%ROWTYPE
1691 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
1692 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1693 ,x_return_status OUT NOCOPY VARCHAR2)
1694 IS
1695
1696 l_api_name CONSTANT VARCHAR2 (30) := 'construct_trans_row';
1697
1698 l_val_proc VARCHAR2 (30);
1699 l_release_type NUMBER;
1700 l_trans_date DATE;
1701 --FPbug#4543872
1702 l_line_type NUMBER;
1703
1704 error_construct EXCEPTION;
1705 error_fetch_trans_date EXCEPTION;
1706
1707 BEGIN
1708 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1709 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1710 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batch_id='||p_batch_hdr_rec.batch_id);
1711 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' material_detail_id='||p_matl_dtl_rec.material_detail_id);
1712 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' batchstep_id='||p_batch_step_rec.batchstep_id);
1713 --FPbug#4543872
1714 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type='||p_matl_dtl_rec.line_type);
1715 END IF;
1716
1717 l_line_type := p_matl_dtl_rec.line_type;
1718 x_return_status := fnd_api.g_ret_sts_success;
1719
1720 x_mmti_rec.source_header_id := p_matl_dtl_rec.batch_id;
1721
1722 /* FPbug#4543872 Begin
1723 Initialized the following in mmti record
1724 */
1725 x_mmti_rec.transaction_source_id := p_matl_dtl_rec.batch_id;
1726 x_mmti_rec.organization_id := p_matl_dtl_rec.organization_id;
1727
1728 --getting transaction_type_id depends on the line type
1729 IF l_line_type = gme_common_pvt.g_line_type_ing THEN
1730 x_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1731 ELSIF l_line_type = gme_common_pvt.g_line_type_prod THEN
1732 x_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1733 ELSIF l_line_type = gme_common_pvt.g_line_type_byprod THEN
1734 x_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_completion;
1735 END IF;
1736 --x_mmti_rec.TRANSACTION_TYPE_ID := 44;
1737 x_mmti_rec.subinventory_code := p_matl_dtl_rec.subinventory;
1738 x_mmti_rec.locator_id := p_matl_dtl_rec.locator_id;
1739 --Bug#5129153 Populate the value of revision field in the mmti record.
1740 x_mmti_rec.revision := p_matl_dtl_rec.revision;
1741 /* FPbug#4543872 End */
1742
1743 /*Bug#5394232 Begin
1744 if we don't pass any date to this procedure then we have to default the trans date*/
1745 IF x_mmti_rec.transaction_date IS NULL THEN
1746 gme_common_pvt.fetch_trans_date(
1747 p_material_detail_id => p_matl_dtl_rec.material_detail_id
1748 ,p_invoke_mode => 'T'
1749 ,x_trans_date => l_trans_date
1750 ,x_return_status => x_return_status );
1751
1752 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1753 RAISE error_fetch_trans_date;
1754 END IF;
1755 --initializing the transaction date according to default rules
1756 x_mmti_rec.transaction_date := l_trans_date;
1757 END IF;
1758 --FPbug#4543872 rework
1759 --x_mmti_rec.transaction_date := sysdate;
1760 --Bug#5394232 End
1761
1762 x_mmti_rec.trx_source_line_id := p_matl_dtl_rec.material_detail_id;
1763 x_mmti_rec.transaction_quantity := p_matl_dtl_rec.actual_qty;
1764 x_mmti_rec.transaction_uom := p_matl_dtl_rec.dtl_um;
1765 x_mmti_rec.inventory_item_id := p_matl_dtl_rec.inventory_item_id;
1766 x_mmti_rec.secondary_uom_code := p_item_rec.secondary_uom_code;
1767
1768 -- If item is dual, get 2ary qty
1769 /* FPbug#4543872 commented out the following IF condition
1770 and added modified one */
1771 --IF p_item_rec.dual_uom_control <> 0 THEN
1772 IF p_matl_dtl_rec.dtl_um <> p_item_rec.secondary_uom_code THEN
1773 get_converted_qty (
1774 p_org_id => p_matl_dtl_rec.organization_id
1775 ,p_item_id => p_matl_dtl_rec.inventory_item_id
1776 ,p_lot_number => NULL
1777 ,p_qty => p_matl_dtl_rec.actual_qty
1778 ,p_from_um => p_matl_dtl_rec.dtl_um
1779 ,p_to_um => p_item_rec.secondary_uom_code
1780 ,x_conv_qty => x_mmti_rec.secondary_transaction_quantity
1781 ,x_return_status => x_return_status);
1782
1783 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1784 l_val_proc := 'get_converted_qty';
1785 RAISE error_construct;
1786 END IF;
1787 END IF;
1788
1789 -- Calculate Transaction Date
1790 l_release_type := p_matl_dtl_rec.release_type;
1791 IF l_release_type = gme_common_pvt.g_mtl_autobystep_release AND
1792 p_batch_step_rec.batchstep_id IS NULL THEN
1793 l_release_type := gme_common_pvt.g_mtl_auto_release;
1794 END IF;
1795
1796 IF l_release_type = gme_common_pvt.g_mtl_autobystep_release THEN -- abs... dates come from step
1797 IF p_batch_step_rec.step_status = gme_common_pvt.g_step_completed THEN
1798 x_mmti_rec.transaction_date := p_batch_step_rec.actual_cmplt_date;
1799 ELSE -- must be WIP
1800 x_mmti_rec.transaction_date := p_batch_step_rec.actual_start_date;
1801 END IF;
1802 ELSE -- auto release... dates come from batch
1803 IF p_batch_hdr_rec.batch_status =
1804 gme_common_pvt.g_batch_completed THEN
1805 x_mmti_rec.transaction_date :=
1806 p_batch_hdr_rec.actual_cmplt_date;
1807 ELSE -- must be WIP
1808 x_mmti_rec.transaction_date :=
1809 p_batch_hdr_rec.actual_start_date;
1810 END IF;
1811 END IF;
1812
1813 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1814 gme_debug.put_line ( g_pkg_name
1815 || '.'
1816 || l_api_name
1817 || ' construct trans with following:');
1818 gme_debug.put_line ( g_pkg_name
1819 || '.'
1820 || l_api_name
1821 || ' batch_id:'
1822 || TO_CHAR (x_mmti_rec.source_header_id) );
1823 gme_debug.put_line ( g_pkg_name
1824 || '.'
1825 || l_api_name
1826 || ' mtl_dtl_id:'
1827 || TO_CHAR (x_mmti_rec.trx_source_line_id) );
1828 gme_debug.put_line ( g_pkg_name
1829 || '.'
1830 || l_api_name
1831 || ' inventory_item_id:'
1832 || TO_CHAR (x_mmti_rec.inventory_item_id) );
1833 gme_debug.put_line ( g_pkg_name
1834 || '.'
1835 || l_api_name
1836 || ' transaction_quantity:'
1837 || TO_CHAR (x_mmti_rec.transaction_quantity) );
1838 gme_debug.put_line ( g_pkg_name
1839 || '.'
1840 || l_api_name
1841 || ' transaction_uom:'
1842 || x_mmti_rec.transaction_uom);
1843 gme_debug.put_line
1844 ( g_pkg_name
1845 || '.'
1846 || l_api_name
1847 || ' secondary_transaction_quantity:'
1848 || TO_CHAR
1849 (x_mmti_rec.secondary_transaction_quantity) );
1850 gme_debug.put_line ( g_pkg_name
1851 || '.'
1852 || l_api_name
1853 || ' secondary_uom_code:'
1854 || x_mmti_rec.secondary_uom_code);
1855 gme_debug.put_line ( g_pkg_name
1856 || '.'
1857 || l_api_name
1858 || ' transaction_date:'
1859 || TO_CHAR (x_mmti_rec.transaction_date, g_date_fmt) );
1860 END IF;
1861
1862 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1863 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1864 END IF;
1865
1866 EXCEPTION
1867 WHEN error_construct OR error_fetch_trans_date THEN
1868 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1869 gme_debug.put_line (g_pkg_name||'.'||l_api_name
1870 ||': error from proc: '|| l_val_proc);
1871 END IF;
1872 WHEN OTHERS THEN
1873 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1874
1875 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1876 gme_debug.put_line ( 'Unexpected error: '
1877 || g_pkg_name
1878 || '.'
1879 || l_api_name
1880 || ': '
1881 || SQLERRM);
1882 END IF;
1883
1884 x_return_status := fnd_api.g_ret_sts_unexp_error;
1885 END construct_trans_row;
1886
1887 PROCEDURE get_converted_qty (
1888 p_org_id IN NUMBER
1889 ,p_item_id IN NUMBER
1890 ,p_lot_number IN VARCHAR2 DEFAULT NULL
1891 ,p_qty IN NUMBER
1892 ,p_from_um IN VARCHAR2
1893 ,p_to_um IN VARCHAR2
1894 ,x_conv_qty OUT NOCOPY NUMBER
1895 ,x_return_status OUT NOCOPY VARCHAR2) IS
1896
1897 l_api_name CONSTANT VARCHAR2 (30) := 'get_converted_qty';
1898
1899 um_convert_error EXCEPTION;
1900
1901 BEGIN
1902 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1903 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1904 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_item_id='||p_item_id);
1905 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_lot_number='||p_lot_number);
1906 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_qty='||p_qty);
1907 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_from_um='||p_from_um);
1908 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_to_um='||p_to_um);
1909 END IF;
1910
1911 /* Set the return status to success initially */
1912 x_return_status := FND_API.G_RET_STS_SUCCESS;
1913
1914 IF p_to_um = p_from_um THEN
1915 x_conv_qty := p_qty;
1916 ELSE
1917 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1918 gme_debug.put_line
1919 ( g_pkg_name
1920 || '.'
1921 || l_api_name
1922 || ' before call to inv_convert.inv_um_convert');
1923 END IF;
1924
1925 x_conv_qty := inv_convert.inv_um_convert
1926 (item_id => p_item_id
1927 ,lot_number => p_lot_number
1928 ,organization_id => p_org_id
1929 ,precision => gme_common_pvt.g_precision
1930 ,from_quantity => p_qty
1931 ,from_unit => p_from_um
1932 ,to_unit => p_to_um
1933 ,from_name => NULL
1934 ,to_name => NULL);
1935
1936 -- Note: -99999 should be in gme_common_pvt
1937 IF x_conv_qty = -99999 THEN
1938 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1939 gme_debug.put_line
1940 ( g_pkg_name
1941 || '.'
1942 || l_api_name
1943 || ' inv_convert.inv_um_convert returned error');
1944 END IF;
1945
1946 RAISE um_convert_error;
1947 END IF;
1948 END IF; -- IF p_to_um = p_from_um THEN
1949
1950 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1951 gme_debug.put_line
1952 ( g_pkg_name
1953 || '.'
1954 || l_api_name
1955 || ' converted qty = '||x_conv_qty);
1956 END IF;
1957
1958 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1959 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
1960 END IF;
1961 EXCEPTION
1962 WHEN um_convert_error THEN
1963 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
1964 FND_MESSAGE.SET_TOKEN('PGM_NAME',g_pkg_name||'.'||l_api_name);
1965 fnd_msg_pub.ADD;
1966 x_return_status := fnd_api.g_ret_sts_error;
1967 WHEN OTHERS THEN
1968 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1969
1970 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1971 gme_debug.put_line ( 'Unexpected error: '
1972 || g_pkg_name
1973 || '.'
1974 || l_api_name
1975 || ': '
1976 || SQLERRM);
1977 END IF;
1978 x_return_status := fnd_api.g_ret_sts_unexp_error;
1979 END get_converted_qty;
1980
1981 PROCEDURE get_item_rec (
1982 p_org_id IN NUMBER
1983 ,p_item_id IN NUMBER
1984 ,x_item_rec OUT NOCOPY mtl_system_items_b%ROWTYPE
1985 ,x_return_status OUT NOCOPY VARCHAR2)
1986 IS
1987 CURSOR cur_get_item_rec (v_org_id NUMBER, v_item_id NUMBER)
1988 IS
1989 SELECT *
1990 FROM mtl_system_items_b
1991 WHERE inventory_item_id = v_item_id
1992 AND organization_id = v_org_id;
1993
1994 error_get_item EXCEPTION;
1995 l_api_name CONSTANT VARCHAR2 (30) := 'get_item_rec';
1996 BEGIN
1997 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
1998 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
1999 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_org_id='||p_org_id);
2000 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_item_id='||p_item_id);
2001 END IF;
2002
2003 x_return_status := fnd_api.g_ret_sts_success;
2004
2005 IF p_item_id IS NULL THEN
2006 gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', 'mtl_system_items_b');
2007 RAISE error_get_item;
2008 END IF;
2009
2010 OPEN cur_get_item_rec (p_org_id, p_item_id);
2011 FETCH cur_get_item_rec INTO x_item_rec;
2012 CLOSE cur_get_item_rec;
2013
2014 IF x_item_rec.inventory_item_id IS NULL THEN -- not found
2015 gme_common_pvt.log_message ('PM_INVALID_ITEM');
2016
2017 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2018 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' no record in mtl_system_items_b: ');
2019 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'inventory_item_id = ' ||p_item_id );
2020 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'organization_id = ' ||p_org_id);
2021 END IF;
2022 RAISE error_get_item;
2023 END IF;
2024
2025 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2026 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2027 END IF;
2028
2029 EXCEPTION
2030 WHEN error_get_item THEN
2031 x_return_status := fnd_api.g_ret_sts_error;
2032 WHEN OTHERS THEN
2033 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2034
2035 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2036 gme_debug.put_line ( 'Unexpected error: '
2037 || g_pkg_name
2038 || '.'
2039 || l_api_name
2040 || ': '
2041 || SQLERRM);
2042 END IF;
2043
2044 x_return_status := fnd_api.g_ret_sts_unexp_error;
2045 END get_item_rec;
2046
2047 PROCEDURE validate_item_id (
2048 p_org_id IN NUMBER
2049 ,p_item_id IN NUMBER
2050 ,x_item_rec OUT NOCOPY mtl_system_items_b%ROWTYPE
2051 ,x_return_status OUT NOCOPY VARCHAR2)
2052 IS
2053 l_api_name CONSTANT VARCHAR2 (30) := 'validate_item_id';
2054 error_validate EXCEPTION;
2055 error_get_rec EXCEPTION;
2056
2057 l_segm mtl_system_items_kfv.concatenated_segments%TYPE;
2058 l_field VARCHAR2(100);
2059
2060 BEGIN
2061 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2062 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2063 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' item_id='||p_item_id);
2064 END IF;
2065
2066 /* Set return status to success initially */
2067 x_return_status := fnd_api.g_ret_sts_success;
2068
2069 get_item_rec (p_org_id => p_org_id
2070 ,p_item_id => p_item_id
2071 ,x_item_rec => x_item_rec
2072 ,x_return_status => x_return_status);
2073
2074 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2075 RAISE error_get_rec;
2076 END IF;
2077
2078 -- process_execution_enabled_flag Y/N
2079 IF x_item_rec.process_execution_enabled_flag = 'N' THEN
2080 l_field := 'process_execution_enabled_flag';
2081 RAISE error_validate;
2082 END IF;
2083
2084 -- eng_item_flag Y/N
2085 -- lab_batch 1 eng item Y OK
2086 -- lab_batch 0 eng item Y N
2087 -- lab_batch 1 eng item N OK
2088 -- lab_batch 0 eng item N OK
2089 IF gme_common_pvt.g_lab_ind = 0 AND x_item_rec.eng_item_flag = 'Y' THEN
2090 l_field := 'eng_item_flag';
2091 RAISE error_validate;
2092 END IF;
2093
2094 -- inventory_item_flag
2095 IF x_item_rec.inventory_item_flag = 'N' THEN
2096 l_field := 'inventory_item_flag';
2097 RAISE error_validate;
2098 END IF;
2099
2100 -- Bug 8693767 - Add 6 as a valid option to serial type list.
2101 -- serial_number_control_code 1 = no serial number control
2102 IF x_item_rec.serial_number_control_code NOT IN (1, 6) THEN
2103 l_field := 'serial_number_control_code';
2104 RAISE error_validate;
2105 END IF;
2106
2107 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2108 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2109 END IF;
2110
2111 EXCEPTION
2112 WHEN error_get_rec THEN
2113 NULL;
2114 WHEN error_validate THEN
2115 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2116 gme_debug.put_line
2117 ( g_pkg_name
2118 || '.'
2119 || l_api_name
2120 || ' validation failed for item field: '||l_field);
2121 END IF;
2122
2123 SELECT concatenated_segments
2124 INTO l_segm
2125 FROM mtl_system_items_kfv
2126 WHERE inventory_item_id = p_item_id
2127 AND organization_id = p_org_id;
2128 --Bug#5078853
2129 gme_common_pvt.log_message ('GME_INV_ITEM_INSERT', 'ITEM_NO', l_field);
2130 x_return_status := fnd_api.g_ret_sts_error;
2131
2132 WHEN OTHERS THEN
2133 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2134
2135 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2136 gme_debug.put_line ( 'Unexpected error: '
2137 || g_pkg_name
2138 || '.'
2139 || l_api_name
2140 || ': '
2141 || SQLERRM);
2142 END IF;
2143
2144 x_return_status := fnd_api.g_ret_sts_unexp_error;
2145 END validate_item_id;
2146
2147 PROCEDURE validate_revision (
2148 p_revision IN VARCHAR2
2149 ,p_item_rec IN mtl_system_items_b%ROWTYPE
2150 ,x_return_status OUT NOCOPY VARCHAR2)
2151 IS
2152 CURSOR cur_get_revision (
2153 v_org_id NUMBER
2154 ,v_item_id NUMBER
2155 ,v_revision VARCHAR2)
2156 IS
2157 SELECT 1
2158 FROM mtl_item_revisions_b
2159 WHERE inventory_item_id = v_item_id
2160 AND organization_id = v_org_id
2161 AND revision = v_revision;
2162
2163 l_api_name CONSTANT VARCHAR2 (30) := 'validate_revision';
2164 l_is_revision_found NUMBER;
2165 error_not_revision_control EXCEPTION;
2166 error_revision_not_found EXCEPTION;
2167
2168 BEGIN
2169
2170 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2171 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2172 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' revision='||p_revision);
2173 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' item_id='||p_item_rec.inventory_item_id);
2174 END IF;
2175
2176 x_return_status := fnd_api.g_ret_sts_success;
2177
2178 IF p_revision IS NULL THEN -- not required even if revision controlled
2179 RETURN;
2180 END IF;
2181
2182 -- revision_qty_control_code
2183 -- 1=No revision qty control; 2=Under revision qty control
2184 IF p_item_rec.revision_qty_control_code = 1 THEN
2185 gme_common_pvt.log_message ('GME_NOT_REV_CTRL');
2186 RAISE error_not_revision_control;
2187 END IF;
2188
2189 OPEN cur_get_revision (p_item_rec.organization_id
2190 ,p_item_rec.inventory_item_id
2191 ,p_revision);
2192 FETCH cur_get_revision INTO l_is_revision_found;
2193 CLOSE cur_get_revision;
2194
2195 IF l_is_revision_found IS NULL OR l_is_revision_found <> 1 THEN
2196 gme_common_pvt.log_message ('GME_REV_NOT_DEFD');
2197 RAISE error_revision_not_found;
2198 END IF;
2199
2200 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2201 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2202 END IF;
2203
2204 EXCEPTION
2205 WHEN error_not_revision_control OR error_revision_not_found THEN
2206 x_return_status := fnd_api.g_ret_sts_error;
2207 WHEN OTHERS THEN
2208 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2209
2210 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2211 gme_debug.put_line ( 'Unexpected error: '
2212 || g_pkg_name
2213 || '.'
2214 || l_api_name
2215 || ': '
2216 || SQLERRM);
2217 END IF;
2218
2219 x_return_status := fnd_api.g_ret_sts_unexp_error;
2220 END validate_revision;
2221
2222 PROCEDURE validate_line_type (
2223 p_line_type IN NUMBER
2224 ,x_return_status OUT NOCOPY VARCHAR2)
2225 IS
2226 l_api_name CONSTANT VARCHAR2 (30) := 'validate_line_type';
2227 validation_error EXCEPTION;
2228 BEGIN
2229 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2230 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2231 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_line_type='||p_line_type);
2232 END IF;
2233
2234 /* Set return status to success initially */
2235 x_return_status := fnd_api.g_ret_sts_success;
2236
2237 -- check GMD parameter FM$BYPROD_ACTIVE
2238 -- value of 1 means Yes; byproducts are available for insert, update and delete
2239 -- value of 2 means No; byproducts are not available
2240 -- if this parameter is set to 2 and the material being inserted is a byproduct, then
2241 -- raise an error
2242 IF gme_common_pvt.g_byprod_active = 2 AND
2243 p_line_type = gme_common_pvt.g_line_type_byprod THEN
2244 fnd_message.set_name ('GMD', 'FM_BYPROD_INACTIVE');
2245 fnd_msg_pub.ADD;
2246 RAISE validation_error;
2247 END IF;
2248
2249 IF p_line_type NOT IN
2250 (gme_common_pvt.g_line_type_ing
2251 ,gme_common_pvt.g_line_type_prod
2252 ,gme_common_pvt.g_line_type_byprod) THEN
2253 gme_common_pvt.log_message ('GME_INVALID_LINE_TYPE');
2254 RAISE validation_error;
2255 END IF;
2256
2257 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2258 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2259 END IF;
2260
2261 EXCEPTION
2262 WHEN validation_error THEN
2263 x_return_status := fnd_api.g_ret_sts_error;
2264 WHEN OTHERS THEN
2265 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2266
2267 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2268 gme_debug.put_line ( 'Unexpected error: '
2269 || g_pkg_name
2270 || '.'
2271 || l_api_name
2272 || ': '
2273 || SQLERRM);
2274 END IF;
2275
2276 x_return_status := fnd_api.g_ret_sts_unexp_error;
2277 END validate_line_type;
2278 --Bug#5129153 Changed the data type of 'p_byproduct_type' to VARCHAR2.
2279 PROCEDURE validate_byproduct_type (
2280 p_byproduct_type IN VARCHAR2
2281 ,x_return_status OUT NOCOPY VARCHAR2)
2282 IS
2283 CURSOR cur_byprod_type (v_byprod_type VARCHAR2)
2284 IS
2285 SELECT 1
2286 FROM gem_lookup_values
2287 WHERE lookup_type = 'GMD_BY_PRODUCT_TYPE'
2288 AND lookup_code = v_byprod_type;
2289
2290 l_api_name CONSTANT VARCHAR2 (30) := 'validate_byproduct_type';
2291 l_exists NUMBER;
2292 invalid_byproduct_type EXCEPTION;
2293 BEGIN
2294 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2295 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2296 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_byproduct_type='||p_byproduct_type);
2297 END IF;
2298
2299 /* Set return status to success initially */
2300 x_return_status := fnd_api.g_ret_sts_success;
2301
2302 -- By_product_type of NULL is OK
2303 IF p_byproduct_type IS NOT NULL THEN
2304 OPEN cur_byprod_type (p_byproduct_type);
2305 FETCH cur_byprod_type INTO l_exists;
2306 CLOSE cur_byprod_type;
2307
2308 IF l_exists IS NULL OR l_exists <> 1 THEN
2309 gme_common_pvt.log_message ('GME_INVALID_BYPROD_TYPE');
2310 RAISE invalid_byproduct_type;
2311 END IF;
2312 END IF;
2313
2314 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2315 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2316 END IF;
2317
2318 EXCEPTION
2319 WHEN invalid_byproduct_type THEN
2320 x_return_status := fnd_api.g_ret_sts_error;
2321 WHEN OTHERS THEN
2322 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2323
2324 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2325 gme_debug.put_line ( 'Unexpected error: '
2326 || g_pkg_name
2327 || '.'
2328 || l_api_name
2329 || ': '
2330 || SQLERRM);
2331 END IF;
2332
2333 x_return_status := fnd_api.g_ret_sts_unexp_error;
2334 END validate_byproduct_type;
2335
2336 PROCEDURE validate_line_no (
2337 p_line_no IN NUMBER
2338 ,p_line_type IN NUMBER
2339 ,p_batch_id IN NUMBER
2340 ,x_line_no OUT NOCOPY NUMBER
2341 ,x_return_status OUT NOCOPY VARCHAR2)
2342 IS
2343
2344 CURSOR cur_last_line_no (v_batch_id NUMBER, v_line_type NUMBER)
2345 IS
2346 SELECT max(line_no)
2347 FROM gme_material_details
2348 WHERE batch_id = v_batch_id
2349 AND line_type = v_line_type;
2350
2351 l_api_name CONSTANT VARCHAR2 (30) := 'validate_line_no';
2352 invalid_line_no EXCEPTION;
2353 BEGIN
2354 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2355 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2356 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_line_no='||p_line_no);
2357 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_line_type='||p_line_type);
2358 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_id='||p_batch_id);
2359 END IF;
2360
2361 x_return_status := fnd_api.g_ret_sts_success;
2362
2363 IF p_line_no <= 0 THEN
2364 gme_common_pvt.log_message ('GME_INVALID_LINE_NUMBER');
2365 RAISE invalid_line_no;
2366 END IF;
2367
2368 OPEN cur_last_line_no(p_batch_id, p_line_type);
2369 FETCH cur_last_line_no INTO x_line_no;
2370 CLOSE cur_last_line_no;
2371
2372 IF p_line_no IS NULL OR p_line_no > x_line_no THEN
2373 x_line_no := NVL(x_line_no, 0) + 1;
2374 ELSE
2375 x_line_no := p_line_no;
2376 END IF;
2377
2378 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2379 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2380 END IF;
2381
2382 EXCEPTION
2383 WHEN invalid_line_no THEN
2384 x_return_status := fnd_api.g_ret_sts_error;
2385 WHEN OTHERS THEN
2386 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2387
2388 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2389 gme_debug.put_line ( 'Unexpected error: '
2390 || g_pkg_name
2391 || '.'
2392 || l_api_name
2393 || ': '
2394 || SQLERRM);
2395 END IF;
2396
2397 x_return_status := fnd_api.g_ret_sts_unexp_error;
2398 END validate_line_no;
2399
2400 PROCEDURE validate_dtl_um (
2401 p_dtl_um IN VARCHAR2
2402 ,p_primary_uom IN VARCHAR2
2403 ,p_item_id IN NUMBER
2404 ,p_org_id IN NUMBER
2405 ,x_return_status OUT NOCOPY VARCHAR2)
2406 IS
2407 l_api_name CONSTANT VARCHAR2 (30) := 'validate_dtl_um';
2408 l_disable_date DATE;
2409 l_qty NUMBER;
2410 -- invalid_dtl_um EXCEPTION;
2411 disabled_dtl_um EXCEPTION;
2412 um_convert_error EXCEPTION;
2413
2414 CURSOR cur_get_uom_code_date (v_uom_code VARCHAR2)
2415 IS
2416 SELECT disable_date
2417 FROM mtl_units_of_measure
2418 WHERE uom_code = v_uom_code;
2419
2420 BEGIN
2421 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2422 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2423 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_dtl_um='||p_dtl_um);
2424 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_primary_uom='||p_primary_uom);
2425 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_item_id='||p_item_id);
2426 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_org_id='||p_org_id);
2427 END IF;
2428
2429 x_return_status := fnd_api.g_ret_sts_success;
2430
2431 OPEN cur_get_uom_code_date (p_dtl_um);
2432 FETCH cur_get_uom_code_date INTO l_disable_date;
2433 CLOSE cur_get_uom_code_date;
2434
2435 -- Namit bug#4515560. Disable date can be null for UOM and do not raise exception for it.
2436 /*
2437 IF l_disable_date IS NULL THEN
2438 fnd_message.set_name ('GMI', 'IC_UMCODE');
2439 fnd_msg_pub.ADD;
2440 RAISE invalid_dtl_um;
2441 END IF;
2442 */
2443
2444 IF l_disable_date <= gme_common_pvt.g_timestamp THEN
2445 gme_common_pvt.log_message ('GME_UM_DISABLED');
2446 RAISE disabled_dtl_um;
2447 END IF;
2448
2449 get_converted_qty (
2450 p_org_id => p_org_id
2451 ,p_item_id => p_item_id
2452 ,p_lot_number => NULL
2453 ,p_qty => 1
2454 ,p_from_um => p_dtl_um
2455 ,p_to_um => p_primary_uom
2456 ,x_conv_qty => l_qty
2457 ,x_return_status => x_return_status);
2458
2459 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2460 RAISE um_convert_error;
2461 END IF;
2462
2463 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2464 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2465 END IF;
2466
2467 EXCEPTION
2468 -- WHEN invalid_dtl_um OR disabled_dtl_um THEN
2469 WHEN disabled_dtl_um THEN
2470 x_return_status := fnd_api.g_ret_sts_error;
2471 WHEN um_convert_error THEN
2472 NULL;
2473 WHEN OTHERS THEN
2474 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2475
2476 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2477 gme_debug.put_line ( 'Unexpected error: '
2478 || g_pkg_name
2479 || '.'
2480 || l_api_name
2481 || ': '
2482 || SQLERRM);
2483 END IF;
2484
2485 x_return_status := fnd_api.g_ret_sts_unexp_error;
2486 END validate_dtl_um;
2487
2488 PROCEDURE validate_plan_qty (
2489 p_plan_qty IN NUMBER
2490 ,x_return_status OUT NOCOPY VARCHAR2)
2491 IS
2492 val_error EXCEPTION;
2493 l_api_name CONSTANT VARCHAR2 (30) := 'validate_plan_qty';
2494 BEGIN
2495 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2496 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2497 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_plan_qty='||p_plan_qty);
2498 END IF;
2499
2500 x_return_status := fnd_api.g_ret_sts_success;
2501
2502 IF p_plan_qty < 0 THEN
2503 gme_common_pvt.log_message ('GME_INVALID_PLAN_QTY');
2504 RAISE val_error;
2505 END IF;
2506
2507 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2508 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2509 END IF;
2510
2511 EXCEPTION
2512 WHEN val_error THEN
2513 x_return_status := fnd_api.g_ret_sts_error;
2514 WHEN OTHERS THEN
2515 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2516
2517 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2518 gme_debug.put_line ( 'Unexpected error: '
2519 || g_pkg_name
2520 || '.'
2521 || l_api_name
2522 || ': '
2523 || SQLERRM);
2524 END IF;
2525
2526 x_return_status := fnd_api.g_ret_sts_unexp_error;
2527 END validate_plan_qty;
2528
2529 PROCEDURE validate_wip_plan_qty (
2530 p_wip_plan_qty IN NUMBER
2531 ,x_return_status OUT NOCOPY VARCHAR2)
2532 IS
2533 val_error EXCEPTION;
2534 l_api_name CONSTANT VARCHAR2 (30) := 'validate_wip_plan_qty';
2535 BEGIN
2536 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2537 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2538 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_wip_plan_qty='||p_wip_plan_qty);
2539 END IF;
2540
2541 x_return_status := fnd_api.g_ret_sts_success;
2542
2543 IF p_wip_plan_qty < 0 THEN
2544 gme_common_pvt.log_message ('GME_INVALID_WIP_PLAN_QTY');
2545 RAISE val_error;
2546 END IF;
2547
2548 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2549 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2550 END IF;
2551
2552 EXCEPTION
2553 WHEN val_error THEN
2554 x_return_status := fnd_api.g_ret_sts_error;
2555 WHEN OTHERS THEN
2556 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2557
2558 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2559 gme_debug.put_line ( 'Unexpected error: '
2560 || g_pkg_name
2561 || '.'
2562 || l_api_name
2563 || ': '
2564 || SQLERRM);
2565 END IF;
2566
2567 x_return_status := fnd_api.g_ret_sts_unexp_error;
2568 END validate_wip_plan_qty;
2569
2570 PROCEDURE validate_actual_qty (
2571 p_actual_qty IN NUMBER
2572 ,x_return_status OUT NOCOPY VARCHAR2)
2573 IS
2574 val_error EXCEPTION;
2575 l_api_name CONSTANT VARCHAR2 (30) := 'validate_actual_qty';
2576 BEGIN
2577 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2578 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2579 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_actual_qty='||p_actual_qty);
2580 END IF;
2581
2582 x_return_status := fnd_api.g_ret_sts_success;
2583
2584 IF p_actual_qty < 0 THEN
2585 gme_common_pvt.log_message ('GME_INVALID_ACTUAL_QTY');
2586 RAISE val_error;
2587 END IF;
2588
2589 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2590 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2591 END IF;
2592
2593 EXCEPTION
2594 WHEN val_error THEN
2595 x_return_status := fnd_api.g_ret_sts_error;
2596 WHEN OTHERS THEN
2597 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2598
2599 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2600 gme_debug.put_line ( 'Unexpected error: '
2601 || g_pkg_name
2602 || '.'
2603 || l_api_name
2604 || ': '
2605 || SQLERRM);
2606 END IF;
2607
2608 x_return_status := fnd_api.g_ret_sts_unexp_error;
2609 END validate_actual_qty;
2610
2611 PROCEDURE validate_release_type (
2612 p_material_detail_rec IN gme_material_details%ROWTYPE
2613 ,p_release_type IN NUMBER
2614 ,x_return_status OUT NOCOPY VARCHAR2)
2615 IS
2616 CURSOR cur_rel_type (v_rel_type VARCHAR2) IS
2617 SELECT 1
2618 FROM gem_lookup_values
2619 WHERE lookup_type = 'GMD_MATERIAL_RELEASE_TYPE'
2620 AND lookup_code = v_rel_type;
2621
2622 l_exists NUMBER;
2623 val_error EXCEPTION;
2624
2625 l_api_name CONSTANT VARCHAR2 (30) := 'validate_release_type';
2626 BEGIN
2627 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2628 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2629 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_release_type='||p_release_type);
2630 END IF;
2631
2632 x_return_status := fnd_api.g_ret_sts_success;
2633
2634 OPEN cur_rel_type (p_release_type);
2635 FETCH cur_rel_type INTO l_exists;
2636 CLOSE cur_rel_type;
2637
2638 IF l_exists IS NULL OR l_exists <> 1 THEN
2639 gme_common_pvt.log_message ('GME_INVALID_RELEASE_TYPE');
2640 RAISE val_error;
2641 END IF;
2642
2643 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2644 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2645 END IF;
2646
2647 EXCEPTION
2648 WHEN val_error THEN
2649 x_return_status := fnd_api.g_ret_sts_error;
2650 WHEN OTHERS THEN
2651 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2652
2653 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2654 gme_debug.put_line ( 'Unexpected error: '
2655 || g_pkg_name
2656 || '.'
2657 || l_api_name
2658 || ': '
2659 || SQLERRM);
2660 END IF;
2661
2662 x_return_status := fnd_api.g_ret_sts_unexp_error;
2663 END validate_release_type;
2664
2665 PROCEDURE validate_scrap_factor (
2666 p_scrap IN NUMBER
2667 ,x_return_status OUT NOCOPY VARCHAR2)
2668 IS
2669
2670 val_error EXCEPTION;
2671 l_api_name CONSTANT VARCHAR2 (30) := 'validate_scrap_factor';
2672 BEGIN
2673 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2674 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2675 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_scrap='||p_scrap);
2676 END IF;
2677
2678 x_return_status := fnd_api.g_ret_sts_success;
2679
2680 IF (p_scrap < 0 OR p_scrap > 1000000) THEN
2681 gme_common_pvt.log_message ('GME_INVALID_SCRAP_FACTOR');
2682 RAISE val_error;
2683 END IF;
2684
2685 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2686 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2687 END IF;
2688
2689 EXCEPTION
2690 WHEN val_error THEN
2691 x_return_status := fnd_api.g_ret_sts_error;
2692 WHEN OTHERS THEN
2693 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2694
2695 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2696 gme_debug.put_line ( 'Unexpected error: '
2697 || g_pkg_name
2698 || '.'
2699 || l_api_name
2700 || ': '
2701 || SQLERRM);
2702 END IF;
2703
2704 x_return_status := fnd_api.g_ret_sts_unexp_error;
2705 END validate_scrap_factor;
2706
2707 PROCEDURE validate_scale_multiple (
2708 p_scale_mult IN NUMBER
2709 ,x_return_status OUT NOCOPY VARCHAR2)
2710 IS
2711 val_error EXCEPTION;
2712 l_api_name CONSTANT VARCHAR2 (30) := 'validate_scale_multiple';
2713 BEGIN
2714 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2715 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2716 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_scale_mult='||p_scale_mult);
2717 END IF;
2718
2719 x_return_status := fnd_api.g_ret_sts_success;
2720
2721 IF p_scale_mult <= 0 THEN
2722 gme_common_pvt.log_message ('GME_INVALID_SCALE_MULT');
2723 RAISE val_error;
2724 END IF;
2725
2726 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2727 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2728 END IF;
2729
2730 EXCEPTION
2731 WHEN val_error THEN
2732 x_return_status := fnd_api.g_ret_sts_error;
2733 WHEN OTHERS THEN
2734 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2735
2736 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2737 gme_debug.put_line ( 'Unexpected error: '
2738 || g_pkg_name
2739 || '.'
2740 || l_api_name
2741 || ': '
2742 || SQLERRM);
2743 END IF;
2744
2745 x_return_status := fnd_api.g_ret_sts_unexp_error;
2746 END validate_scale_multiple;
2747
2748 PROCEDURE validate_scale_round_var (
2749 p_scale_var IN NUMBER
2750 ,x_return_status OUT NOCOPY VARCHAR2)
2751 IS
2752 val_error EXCEPTION;
2753 l_api_name CONSTANT VARCHAR2 (30) := 'validate_scale_round_var';
2754 BEGIN
2755 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2756 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2757 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_scale_var='||p_scale_var);
2758 END IF;
2759
2760 x_return_status := fnd_api.g_ret_sts_success;
2761
2762 IF (p_scale_var < 0 OR p_scale_var > 100) THEN
2763 gme_common_pvt.log_message ('GME_INVALID_SCALE_ROUND_VAR');
2764 RAISE val_error;
2765 END IF;
2766
2767 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2768 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2769 END IF;
2770
2771 EXCEPTION
2772 WHEN val_error THEN
2773 x_return_status := fnd_api.g_ret_sts_error;
2774 WHEN OTHERS THEN
2775 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2776
2777 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2778 gme_debug.put_line ( 'Unexpected error: '
2779 || g_pkg_name
2780 || '.'
2781 || l_api_name
2782 || ': '
2783 || SQLERRM);
2784 END IF;
2785
2786 x_return_status := fnd_api.g_ret_sts_unexp_error;
2787 END validate_scale_round_var;
2788
2789 PROCEDURE validate_rounding_direction (
2790 p_round_dir IN NUMBER
2791 ,x_return_status OUT NOCOPY VARCHAR2)
2792 IS
2793 CURSOR cur_round_dir (v_round_dir VARCHAR2)
2794 IS
2795 SELECT 1
2796 FROM gem_lookup_values
2797 WHERE lookup_type = 'GMD_ROUNDING_DIRECTION'
2798 AND lookup_code = v_round_dir;
2799
2800 l_exists NUMBER;
2801 val_error EXCEPTION;
2802
2803 l_api_name CONSTANT VARCHAR2 (30) := 'validate_rounding_direction';
2804 BEGIN
2805 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2806 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2807 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_round_dir='||p_round_dir);
2808 END IF;
2809
2810 x_return_status := fnd_api.g_ret_sts_success;
2811
2812 OPEN cur_round_dir (p_round_dir);
2813 FETCH cur_round_dir INTO l_exists;
2814 CLOSE cur_round_dir;
2815
2816 IF l_exists IS NULL OR l_exists <> 1 THEN
2817 gme_common_pvt.log_message ('GME_INVALID_ROUND_DIR');
2818 RAISE val_error;
2819 END IF;
2820
2821 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2822 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2823 END IF;
2824
2825 EXCEPTION
2826 WHEN val_error THEN
2827 x_return_status := fnd_api.g_ret_sts_error;
2828 WHEN OTHERS THEN
2829 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2830
2831 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2832 gme_debug.put_line ( 'Unexpected error: '
2833 || g_pkg_name
2834 || '.'
2835 || l_api_name
2836 || ': '
2837 || SQLERRM);
2838 END IF;
2839
2840 x_return_status := fnd_api.g_ret_sts_unexp_error;
2841 END validate_rounding_direction;
2842
2843 PROCEDURE validate_scale_type (
2844 p_scale_type IN NUMBER
2845 ,x_return_status OUT NOCOPY VARCHAR2)
2846 IS
2847
2848 CURSOR cur_scale_type (v_scale_type VARCHAR2)
2849 IS
2850 SELECT 1
2851 FROM gem_lookup_values
2852 WHERE lookup_type = 'SCALE_TYPE'
2853 AND lookup_code = v_scale_type;
2854
2855 l_exists NUMBER;
2856 l_api_name CONSTANT VARCHAR2 (30) := 'validate_scale_type';
2857 val_error EXCEPTION;
2858 BEGIN
2859 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2860 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2861 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_scale_type='||p_scale_type);
2862 END IF;
2863
2864 x_return_status := fnd_api.g_ret_sts_success;
2865
2866 OPEN cur_scale_type (p_scale_type);
2867 FETCH cur_scale_type INTO l_exists;
2868 CLOSE cur_scale_type;
2869
2870 IF l_exists IS NULL OR l_exists <> 1 THEN
2871 gme_common_pvt.log_message ('GME_INVALID_SCALE_TYPE');
2872 RAISE val_error;
2873 END IF;
2874
2875 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2876 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2877 END IF;
2878
2879 EXCEPTION
2880 WHEN val_error THEN
2881 x_return_status := fnd_api.g_ret_sts_error;
2882 WHEN OTHERS THEN
2883 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2884
2885 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2886 gme_debug.put_line ( 'Unexpected error: '
2887 || g_pkg_name
2888 || '.'
2889 || l_api_name
2890 || ': '
2891 || SQLERRM);
2892 END IF;
2893
2894 x_return_status := fnd_api.g_ret_sts_unexp_error;
2895 END validate_scale_type;
2896
2897 --FPBug#4524232 changed parameter to p_material_detail_rec from p_cost_alloc
2898 PROCEDURE validate_cost_alloc(
2899 p_material_detail_rec IN gme_material_details%ROWTYPE
2900 ,x_return_status OUT NOCOPY VARCHAR2)
2901 IS
2902 --FPBug#4524232 Begin
2903 CURSOR Cur_get_step_status(v_material_detail_id NUMBER) IS
2904 SELECT steprelease_type,step_status
2905 FROM gme_batch_step_items si, gme_batch_steps s
2906 WHERE si.batchstep_id = s.batchstep_id
2907 AND si.material_detail_id = v_material_detail_id;
2908 CURSOR Cur_get_batch_status(v_batch_id NUMBER) IS
2909 SELECT batch_status
2910 FROM gme_batch_header
2911 WHERE batch_id = v_batch_id;
2912 CURSOR Cur_get_cost_alloc(v_material_detail_id NUMBER) IS
2913 SELECT cost_alloc
2914 FROM gme_material_details
2915 WHERE material_detail_id = v_material_detail_id;
2916
2917 l_batch_id NUMBER;
2918 l_material_detail_id NUMBER;
2919 l_status NUMBER;
2920 l_step_status NUMBER;
2921 l_rel_type NUMBER;
2922 l_cost_alloc NUMBER;
2923 --FPBug#4524232 End
2924 val_error EXCEPTION;
2925 l_api_name CONSTANT VARCHAR2 (30) := 'validate_cost_alloc';
2926 BEGIN
2927 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2928 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2929 END IF;
2930 x_return_status := fnd_api.g_ret_sts_success;
2931
2932 --FPBug#4524232 Begin
2933 l_batch_id := p_material_detail_rec.batch_id;
2934 l_material_detail_id := p_material_detail_rec.material_detail_id;
2935
2936 OPEN Cur_get_batch_status(l_batch_id);
2937 FETCH Cur_get_batch_status INTO l_status;
2938 CLOSE Cur_get_batch_status;
2939
2940 IF l_status = gme_common_pvt.g_batch_wip THEN
2941 OPEN Cur_get_step_status(l_material_detail_id);
2942 FETCH Cur_get_step_status INTO l_rel_type,l_step_status;
2943 CLOSE Cur_get_step_status;
2944 END IF;
2945
2946 /* For the completed batches and the wip batches where the associated step is
2947 completed, the cost allocation is not updatable */
2948 IF l_status = gme_common_pvt.g_batch_completed OR
2949 ( l_status = gme_common_pvt.g_batch_wip AND l_rel_type=gme_common_pvt.g_mtl_autobystep_release AND
2950 l_step_status = gme_common_pvt.g_step_completed) THEN
2951 IF l_material_detail_id is NULL THEN
2952 /* in insert */
2953 IF p_material_detail_rec.cost_alloc <> 0 THEN
2954 gme_common_pvt.log_message ('GME_INVALID_COST_ALLOC');
2955 RAISE val_error;
2956 END IF;
2957 ELSE
2958 /* in update */
2959 OPEN Cur_get_cost_alloc(l_material_detail_id);
2960 FETCH Cur_get_cost_alloc INTO l_cost_alloc;
2961 CLOSE Cur_get_cost_alloc;
2962 /*if the passed cost allocation is diff from stored cost alloc raise error */
2963 IF l_cost_alloc <> p_material_detail_rec.cost_alloc THEN
2964 gme_common_pvt.log_message ('GME_COST_ALLOC_CANNOT_UPD');
2965 RAISE val_error;
2966 END IF;
2967 END IF;
2968 END IF;
2969 --commented out the following lines
2970 /*IF p_cost_alloc < 0 OR p_cost_alloc > 1 THEN
2971 gme_common_pvt.log_message ('GME_INVALID_COST_ALLOC');
2972 RAISE val_error;
2973 END IF; */
2974 --FPBug#4524232 End
2975 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
2976 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2977 END IF;
2978 EXCEPTION
2979 WHEN val_error THEN
2980 x_return_status := fnd_api.g_ret_sts_error;
2981 WHEN OTHERS THEN
2982 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2983 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2984 gme_debug.put_line ( 'Unexpected error: '
2985 || g_pkg_name
2986 || '.'
2987 || l_api_name
2988 || ': '
2989 || SQLERRM);
2990 END IF;
2991 x_return_status := fnd_api.g_ret_sts_unexp_error;
2992 END validate_cost_alloc;
2993
2994 PROCEDURE validate_phantom_type (
2995 p_phantom_type IN NUMBER
2996 ,x_return_status OUT NOCOPY VARCHAR2)
2997 IS
2998
2999 CURSOR cur_phantom_type (v_phantom_type VARCHAR2)
3000 IS
3001 SELECT 1
3002 FROM gem_lookup_values
3003 WHERE lookup_type = 'PHANTOM_TYPE'
3004 AND lookup_code = v_phantom_type;
3005
3006 l_exists NUMBER;
3007 val_error EXCEPTION;
3008 l_api_name CONSTANT VARCHAR2 (30) := 'validate_phantom_type';
3009 BEGIN
3010 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3011 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
3012 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_phantom_type='||p_phantom_type);
3013 END IF;
3014
3015 x_return_status := fnd_api.g_ret_sts_success;
3016
3017 OPEN cur_phantom_type (p_phantom_type);
3018 FETCH cur_phantom_type INTO l_exists;
3019 CLOSE cur_phantom_type;
3020
3021 IF l_exists IS NULL OR l_exists <> 1 THEN
3022 gme_common_pvt.log_message ('GME_INV_PHANTOM_TYPE');
3023 RAISE val_error;
3024 END IF;
3025
3026 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3027 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
3028 END IF;
3029
3030 EXCEPTION
3031 WHEN val_error THEN
3032 x_return_status := fnd_api.g_ret_sts_error;
3033 WHEN OTHERS THEN
3034 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3035
3036 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3037 gme_debug.put_line ( 'Unexpected error: '
3038 || g_pkg_name
3039 || '.'
3040 || l_api_name
3041 || ': '
3042 || SQLERRM);
3043 END IF;
3044
3045 x_return_status := fnd_api.g_ret_sts_unexp_error;
3046 END validate_phantom_type;
3047
3048 PROCEDURE validate_contr_yield_ind (
3049 p_contr_yield_ind IN VARCHAR2 --FPBug#5040865
3050 ,x_return_status OUT NOCOPY VARCHAR2)
3051 IS
3052 val_error EXCEPTION;
3053 l_api_name CONSTANT VARCHAR2 (30) := 'validate_contr_yield_ind';
3054 BEGIN
3055 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3056 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
3057 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_contr_yield_ind='||p_contr_yield_ind);
3058 END IF;
3059
3060 x_return_status := fnd_api.g_ret_sts_success;
3061
3062 IF p_contr_yield_ind NOT IN ('Y', 'N') THEN
3063 gme_common_pvt.log_message ('GME_INVALID_CONTR_YIELD');
3064 RAISE val_error;
3065 END IF;
3066
3067 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3068 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
3069 END IF;
3070
3071 EXCEPTION
3072 WHEN val_error THEN
3073 x_return_status := fnd_api.g_ret_sts_error;
3074 WHEN OTHERS THEN
3075 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3076
3077 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3078 gme_debug.put_line ( 'Unexpected error: '
3079 || g_pkg_name
3080 || '.'
3081 || l_api_name
3082 || ': '
3083 || SQLERRM);
3084 END IF;
3085
3086 x_return_status := fnd_api.g_ret_sts_unexp_error;
3087 END validate_contr_yield_ind;
3088
3089 PROCEDURE validate_contr_step_qty_ind (
3090 p_contr_step_qty_ind IN VARCHAR2 --FPBug#5040865
3091 ,x_return_status OUT NOCOPY VARCHAR2)
3092 IS
3093 val_error EXCEPTION;
3094
3095 l_api_name CONSTANT VARCHAR2 (30) := 'validate_contr_step_qty_ind';
3096 BEGIN
3097 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3098 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
3099 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_contr_step_qty_ind='||p_contr_step_qty_ind);
3100 END IF;
3101
3102 x_return_status := fnd_api.g_ret_sts_success;
3103
3104 IF p_contr_step_qty_ind NOT IN ('Y', 'N') THEN
3105 gme_common_pvt.log_message ('GME_INVALID_CONTR_STEP');
3106 RAISE val_error;
3107 END IF;
3108
3109 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3110 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
3111 END IF;
3112
3113 EXCEPTION
3114 WHEN val_error THEN
3115 x_return_status := fnd_api.g_ret_sts_error;
3116 WHEN OTHERS THEN
3117 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3118
3119 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3120 gme_debug.put_line ( 'Unexpected error: '
3121 || g_pkg_name
3122 || '.'
3123 || l_api_name
3124 || ': '
3125 || SQLERRM);
3126 END IF;
3127
3128 x_return_status := fnd_api.g_ret_sts_unexp_error;
3129 END validate_contr_step_qty_ind;
3130
3131 PROCEDURE validate_subinventory (
3132 p_subinv IN VARCHAR2
3133 ,p_item_rec IN mtl_system_items_b%ROWTYPE
3134 ,x_return_status OUT NOCOPY VARCHAR2)
3135 IS
3136 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_SUBINVENTORY';
3137 sub_not_valid EXCEPTION;
3138 BEGIN
3139 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3140 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
3141 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_subinv='||p_subinv);
3142 END IF;
3143
3144 x_return_status := fnd_api.g_ret_sts_success;
3145
3146 IF p_subinv IS NULL THEN
3147 NULL; -- not required
3148 ELSIF gme_common_pvt.check_subinventory
3149 (p_organization_id => p_item_rec.organization_id
3150 ,p_subinventory => p_subinv
3151 ,p_inventory_item_id => p_item_rec.inventory_item_id
3152 ,p_restrict_subinv => p_item_rec.restrict_subinventories_code) THEN
3153 NULL;
3154 ELSE
3155 RAISE sub_not_valid;
3156 END IF;
3157
3158 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3159 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
3160 END IF;
3161
3162 EXCEPTION
3163 WHEN sub_not_valid THEN
3164 x_return_status := fnd_api.g_ret_sts_error;
3165 WHEN OTHERS THEN
3166 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3167
3168 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3169 gme_debug.put_line ( 'Unexpected error: '
3170 || g_pkg_name
3171 || '.'
3172 || l_api_name
3173 || ': '
3174 || SQLERRM);
3175 END IF;
3176
3177 x_return_status := fnd_api.g_ret_sts_unexp_error;
3178 END validate_subinventory;
3179
3180 PROCEDURE validate_locator (
3181 p_subinv IN VARCHAR2
3182 ,p_locator_id IN NUMBER
3183 ,p_item_rec IN mtl_system_items_b%ROWTYPE
3184 ,p_line_type IN NUMBER
3185 ,x_return_status OUT NOCOPY VARCHAR2)
3186 IS
3187 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_LOCATOR';
3188 l_txn_action_id NUMBER;
3189 l_sub_locator_type NUMBER;
3190 loc_not_valid EXCEPTION;
3191
3192 CURSOR cur_sub_control (v_org_id NUMBER, v_subinventory VARCHAR2)
3193 IS
3194 SELECT locator_type
3195 FROM mtl_secondary_inventories
3196 WHERE organization_id = v_org_id
3197 AND secondary_inventory_name = v_subinventory;
3198 BEGIN
3199 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3200 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
3201 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_subinv='||p_subinv);
3202 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_locator_id='||p_locator_id);
3203 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_line_type='||p_line_type);
3204 END IF;
3205
3206 x_return_status := fnd_api.g_ret_sts_success;
3207
3208 IF p_line_type = gme_common_pvt.g_line_type_ing THEN
3209 l_txn_action_id := gme_common_pvt.g_ing_issue_txn_action;
3210 ELSIF p_line_type = gme_common_pvt.g_line_type_prod THEN
3211 l_txn_action_id := gme_common_pvt.g_prod_comp_txn_action;
3212 ELSIF p_line_type = gme_common_pvt.g_line_type_byprod THEN
3213 l_txn_action_id := gme_common_pvt.g_byprod_comp_txn_action;
3214 END IF;
3215
3216 IF p_locator_id IS NULL THEN
3217 NULL; -- not required
3218 ELSE
3219 OPEN cur_sub_control (p_item_rec.organization_id, p_subinv);
3220 FETCH cur_sub_control INTO l_sub_locator_type;
3221 CLOSE cur_sub_control;
3222 /* Bug 5441643 Added NVL condition for location control code*/
3223 IF gme_common_pvt.check_locator
3224 (p_organization_id => p_item_rec.organization_id
3225 ,p_locator_id => p_locator_id
3226 ,p_subinventory => p_subinv
3227 ,p_inventory_item_id => p_item_rec.inventory_item_id
3228 ,p_org_control => gme_common_pvt.g_org_locator_control
3229 ,p_sub_control => l_sub_locator_type
3230 ,p_item_control => NVL(p_item_rec.location_control_code,1)
3231 ,p_item_loc_restrict => p_item_rec.restrict_locators_code
3232 ,p_org_neg_allowed => gme_common_pvt.g_allow_neg_inv
3233 ,p_txn_action_id => l_txn_action_id) THEN
3234 NULL;
3235 ELSE
3236 RAISE loc_not_valid;
3237 END IF;
3238 END IF;
3239
3240 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3241 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
3242 END IF;
3243
3244 EXCEPTION
3245 WHEN loc_not_valid THEN
3246 x_return_status := fnd_api.g_ret_sts_error;
3247 WHEN OTHERS THEN
3248 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3249
3250 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3251 gme_debug.put_line ( 'Unexpected error: '
3252 || g_pkg_name
3253 || '.'
3254 || l_api_name
3255 || ': '
3256 || SQLERRM);
3257 END IF;
3258
3259 x_return_status := fnd_api.g_ret_sts_unexp_error;
3260 END validate_locator;
3261
3262 PROCEDURE update_material_line (
3263 p_batch_header_rec IN gme_batch_header%ROWTYPE
3264 ,p_material_detail_rec IN gme_material_details%ROWTYPE
3265 ,p_stored_material_detail_rec IN gme_material_details%ROWTYPE
3266 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
3267 ,p_scale_phantom IN VARCHAR2 := fnd_api.g_false
3268 ,p_trans_id IN NUMBER
3269 ,x_transacted OUT NOCOPY VARCHAR2
3270 ,x_return_status OUT NOCOPY VARCHAR2
3271 ,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE)
3272 IS
3273 l_api_name CONSTANT VARCHAR2 (30) := 'update_material_line';
3274 l_factor NUMBER;
3275 l_old_scrap NUMBER;
3276 l_new_scrap NUMBER;
3277 l_batch_status NUMBER;
3278 l_status NUMBER;
3279 l_qty NUMBER;
3280 l_eff_qty NUMBER;
3281 l_old_plan_qty NUMBER;
3282 l_new_plan_qty NUMBER;
3283 l_old_wip_plan NUMBER;
3284 l_new_wip_plan NUMBER;
3285 l_proc VARCHAR2(100);
3286 l_rsc_count NUMBER;
3287 l_message_count NUMBER;
3288 l_message_list VARCHAR2 (2000);
3289
3290 l_compare_qty NUMBER; -- 13076579
3291
3292 l_ph_batch_header_rec gme_batch_header%ROWTYPE;
3293 l_batch_header_rec gme_batch_header%ROWTYPE;
3294 l_out_material_detail_tbl gme_common_pvt.material_details_tab;
3295 l_material_detail_tbl gme_common_pvt.material_details_tab;
3296 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
3297
3298 l_mtl_dtl_rec gme_material_details%ROWTYPE;
3299 l_ph_mtl_dtl_rec gme_material_details%ROWTYPE;
3300 l_db_mtl_dtl_rec gme_material_details%ROWTYPE;
3301 l_batch_step_rec gme_batch_steps%ROWTYPE;
3302 l_exception_material_tbl gme_common_pvt.exceptions_tab;
3303 l_ph_batch_step_rec gme_batch_steps%ROWTYPE;
3304 l_phantom_batch_header_rec_out gme_batch_header%ROWTYPE;
3305 l_step_tbl gme_reschedule_step_pvt.step_tab;
3306 x_batch_step_rec gme_batch_steps%ROWTYPE;
3307 error_dbl EXCEPTION;
3308 error_processing EXCEPTION;
3309 l_plan_cmplt_date gme_batch_header.plan_cmplt_date%TYPE;
3310
3311 BEGIN
3312 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3313 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
3314 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_header_rec.batch_id='||p_batch_header_rec.batch_id);
3315 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_material_detail_rec.material_detail_id='||p_material_detail_rec.material_detail_id);
3316 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_step_rec.batchstep_id='||p_batch_step_rec.batchstep_id);
3317 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_scale_phantom='||p_scale_phantom);
3318 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_trans_id='||p_trans_id);
3319 END IF;
3320
3321 /* Set return status to success initially */
3322 x_return_status := fnd_api.g_ret_sts_success;
3323
3324 l_mtl_dtl_rec := p_material_detail_rec;
3325 l_db_mtl_dtl_rec := p_stored_material_detail_rec;
3326
3327 IF (l_mtl_dtl_rec.actual_qty <> l_db_mtl_dtl_rec.actual_qty) THEN
3328 -- can call this regardless of batch/step status... will also handle if batch is pending and qty is 0 (will do nothing)
3329 open_and_process_actual_qty
3330 (p_batch_header_rec => p_batch_header_rec
3331 ,p_material_detail_rec => l_mtl_dtl_rec
3332 ,p_batch_step_rec => p_batch_step_rec
3333 ,p_trans_id => NULL
3334 ,p_insert => FND_API.g_false
3335 ,x_transacted => x_transacted
3336 ,x_return_status => x_return_status);
3337
3338 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3339 l_proc := 'open_and_process_actual_qty';
3340 RAISE error_processing;
3341 END IF;
3342 END IF;
3343 /*Sunitha Ch. Bug# 5391396 restructured the code and also added the code that will
3344 handle the rescheduling batch/step when update yield Type of the Child batch is done.*/
3345
3346 -- need to compare new and old of plan qty / wip plan qty for
3347 -- 1. calculating factor to scale phantom batch if p_scale_phantom
3348 -- is true
3349 -- 2. if they are different, need to update any move order lines
3350 -- with new qty
3351 -- batch_status is used to decide whether to use plan or wip_plan
3352 l_factor := 1;
3353 l_batch_status := p_batch_header_rec.batch_status;
3354
3355 IF l_batch_status = gme_common_pvt.g_batch_pending THEN
3356 l_qty := l_mtl_dtl_rec.plan_qty;
3357 --Bug#4965141 check for zero plan qty
3358 IF l_db_mtl_dtl_rec.plan_qty = 0 THEN
3359 l_eff_qty := 1;
3360 ELSE
3361 l_eff_qty := l_db_mtl_dtl_rec.plan_qty;
3362 END IF;
3363 l_factor := l_mtl_dtl_rec.plan_qty / l_eff_qty;
3364 ELSIF l_batch_status = gme_common_pvt.g_batch_wip THEN
3365 l_qty := l_mtl_dtl_rec.wip_plan_qty;
3366 --Bug#4965141 check for zero wip plan qty
3367 IF NVL(l_db_mtl_dtl_rec.wip_plan_qty,0) = 0 THEN
3368 l_eff_qty := 1;
3369 ELSE
3370 l_eff_qty := l_db_mtl_dtl_rec.wip_plan_qty;
3371 END IF;
3372 l_factor := l_mtl_dtl_rec.wip_plan_qty / l_eff_qty;
3373 END IF;
3374
3375 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3376 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_qty='||l_qty);
3377 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' l_factor='||l_factor);
3378 END IF;
3379
3380 l_status := p_batch_header_rec.batch_status;
3381 IF l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3382 p_batch_step_rec.batchstep_id IS NOT NULL THEN
3383 l_status := p_batch_step_rec.step_status;
3384 END IF;
3385
3386 -- if scrap was changed and plan/wip plan (based on batch status)
3387 -- was not changed, then recalculate plan/wip plan
3388 l_old_scrap := l_db_mtl_dtl_rec.scrap_factor;
3389 l_new_scrap := l_mtl_dtl_rec.scrap_factor;
3390
3391 l_old_plan_qty := l_db_mtl_dtl_rec.plan_qty;
3392 l_new_plan_qty := l_mtl_dtl_rec.plan_qty;
3393
3394 l_old_wip_plan := l_db_mtl_dtl_rec.wip_plan_qty;
3395 l_new_wip_plan := l_mtl_dtl_rec.wip_plan_qty;
3396
3397 IF l_old_scrap <> l_new_scrap
3398 AND l_status = gme_common_pvt.g_batch_pending
3399 AND l_old_plan_qty = l_new_plan_qty THEN
3400 l_old_plan_qty := x_material_detail_rec.plan_qty
3401 / (1 + l_old_scrap);
3402 x_material_detail_rec.plan_qty :=
3403 l_old_plan_qty
3404 * (1 + x_material_detail_rec.scrap_factor);
3405 ELSIF l_old_scrap <> l_new_scrap
3406 AND l_status = gme_common_pvt.g_batch_wip
3407 AND l_old_wip_plan = l_new_wip_plan THEN
3408 l_old_plan_qty :=
3409 x_material_detail_rec.wip_plan_qty
3410 / (1 + l_old_scrap);
3411 x_material_detail_rec.wip_plan_qty :=
3412 l_old_plan_qty
3413 * (1 + x_material_detail_rec.scrap_factor);
3414 END IF;
3415 /* 5391396 moved the code up*/
3416 IF l_mtl_dtl_rec.release_type <> l_db_mtl_dtl_rec.release_type THEN
3417
3418 gme_common_pvt.calc_mtl_req_date
3419 (p_batch_header_rec => p_batch_header_rec
3420 ,p_batchstep_rec => p_batch_step_rec
3421 ,p_mtl_dtl_rec => l_mtl_dtl_rec
3422 ,x_mtl_req_date => l_mtl_dtl_rec.material_requirement_date
3423 ,x_return_status => x_return_status);
3424
3425 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3426 l_proc := 'gme_common_pvt.calc_mtl_req_date';
3427 RAISE error_processing;
3428 END IF;
3429
3430 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3431 gme_debug.put_line ( g_pkg_name
3432 || '.'
3433 || l_api_name
3434 || ' after gme_common_pvt.calc_mtl_req_date');
3435 gme_debug.put_line
3436 ( g_pkg_name
3437 || '.'
3438 || l_api_name
3439 || ' material_requirement_date= '
3440 || TO_CHAR
3441 (l_mtl_dtl_rec.material_requirement_date
3442 ,'YYYY-MON-DD HH24:MI:SS') );
3443 END IF;
3444 /* Pawan Kumar bug 5127489 Changed so as to change the move order
3445 and reservation dates */
3446 gme_common_pvt.material_date_change (
3447 p_material_detail_id => l_mtl_dtl_rec.material_detail_id
3448 ,p_material_date => l_mtl_dtl_rec.material_requirement_date
3449 ,x_return_status => x_return_status );
3450
3451 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3452 l_proc := 'gme_common_pvt.material_date_change';
3453 RAISE error_processing;
3454 END IF;
3455 --Bug#5159393 Susruth D.
3456 l_mtl_dtl_rec.last_update_date := gme_common_pvt.g_timestamp;
3457 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3458 gme_debug.put_line ( g_pkg_name
3459 || '.'
3460 || l_api_name
3461 || ' after gme_common_pvt.material_date_change');
3462 gme_debug.put_line
3463 ( g_pkg_name
3464 || '.'
3465 || l_api_name
3466 || ' material_requirement_date= '
3467 || TO_CHAR
3468 (l_mtl_dtl_rec.material_requirement_date
3469 ,'YYYY-MON-DD HH24:MI:SS') );
3470 END IF;
3471 END IF;--IF l_mtl_dtl_rec.release_type <> l_db_mtl_dtl_rec.release_type
3472 /* 5391396 seperate the condition */
3473
3474 -- check if this is a phantom ... if so, update the partner phantom
3475 /* Bug 4867497 added subinventory locator and qty logic and moved this out of the IF l_mtl_dtl_rec.release_type <> l_db_mtl_dtl_rec.release_type THEN condn*/
3476
3477 -- ToDo Put an IF condition to check if material_requirement_date has changed or release_type has changed. Only if either has changed, then we need to do the re-schedule stuff.
3478
3479 IF l_mtl_dtl_rec.phantom_line_id IS NOT NULL THEN
3480 l_ph_mtl_dtl_rec.material_detail_id := l_mtl_dtl_rec.phantom_line_id;
3481 IF NOT gme_material_details_dbl.fetch_row(l_ph_mtl_dtl_rec, l_ph_mtl_dtl_rec) THEN
3482 l_proc := 'gme_material_details_dbl.fetch_row';
3483 RAISE error_dbl;
3484 END IF;
3485 l_ph_mtl_dtl_rec.material_requirement_date := l_mtl_dtl_rec.material_requirement_date;
3486 l_ph_mtl_dtl_rec.release_type := l_mtl_dtl_rec.release_type;
3487 l_ph_mtl_dtl_rec.subinventory := l_mtl_dtl_rec.subinventory;
3488 l_ph_mtl_dtl_rec.locator_id := l_mtl_dtl_rec.locator_id;
3489 --sunitha ch. bug 5566769 update the revision field of the phantom batch
3490 l_ph_mtl_dtl_rec.revision := l_mtl_dtl_rec.revision;
3491 IF l_status = gme_common_pvt.g_batch_pending AND (l_mtl_dtl_rec.plan_qty <> l_db_mtl_dtl_rec.plan_qty) THEN
3492 IF (l_mtl_dtl_rec.dtl_um = l_ph_mtl_dtl_rec.dtl_um) THEN
3493 l_ph_mtl_dtl_rec.plan_qty := l_mtl_dtl_rec.plan_qty;
3494 ELSE
3495 l_ph_mtl_dtl_rec.plan_qty := inv_convert.inv_um_convert(item_id => l_mtl_dtl_rec.inventory_item_id
3496 ,organization_id => l_mtl_dtl_rec.organization_id
3497 ,precision => gme_common_pvt.g_precision
3498 ,from_quantity => l_mtl_dtl_rec.plan_qty
3499 ,from_unit => l_mtl_dtl_rec.dtl_um
3500 ,to_unit => l_ph_mtl_dtl_rec.dtl_um
3501 ,from_name => NULL
3502 ,to_name => NULL);
3503 END IF;
3504 ELSIF (NVL(l_mtl_dtl_rec.wip_plan_qty,0) <> NVL(l_db_mtl_dtl_rec.wip_plan_qty,0)) THEN
3505 IF (l_mtl_dtl_rec.dtl_um = l_ph_mtl_dtl_rec.dtl_um) THEN
3506 l_ph_mtl_dtl_rec.wip_plan_qty := l_mtl_dtl_rec.wip_plan_qty;
3507 ELSE
3508 l_ph_mtl_dtl_rec.wip_plan_qty := inv_convert.inv_um_convert(item_id => l_mtl_dtl_rec.inventory_item_id
3509 ,organization_id => l_mtl_dtl_rec.organization_id
3510 ,precision => gme_common_pvt.g_precision
3511 ,from_quantity => l_mtl_dtl_rec.wip_plan_qty
3512 ,from_unit => l_mtl_dtl_rec.dtl_um
3513 ,to_unit => l_ph_mtl_dtl_rec.dtl_um
3514 ,from_name => NULL
3515 ,to_name => NULL);
3516 END IF;
3517 END IF;
3518 /* End Bug 4867497 */
3519
3520 IF l_db_mtl_dtl_rec.phantom_id IS NOT NULL THEN
3521 IF (l_db_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing ) THEN
3522 /*REWORK Sunitha ch. Bug 5353941 Check whether phantom material is associated to
3523 step and call reschedule batch if it is not associated to step or call
3524 rescedule step for that batch if it is associated to step*/
3525 l_ph_batch_header_rec.batch_id := l_db_mtl_dtl_rec.phantom_id;
3526 IF NOT gme_batch_header_dbl.fetch_row(l_ph_batch_header_rec, l_ph_batch_header_rec) THEN
3527 l_proc := 'gme_batch_header_dbl.fetch_row';
3528 RAISE error_dbl;
3529 END IF;
3530
3531 IF p_scale_phantom = FND_API.G_TRUE AND l_factor <> 1 THEN
3532
3533 gme_scale_batch_pvt.scale_batch
3534 (p_batch_header_rec => l_ph_batch_header_rec
3535 ,p_scale_factor => l_factor
3536 ,p_primaries => 'OUTPUTS'
3537 ,p_qty_type => 1
3538 ,p_validity_rule_id => l_ph_batch_header_rec.recipe_validity_rule_id
3539 ,p_enforce_vldt_check => fnd_api.g_true
3540 ,p_recalc_dates => fnd_api.g_false
3541 ,p_use_workday_cal => fnd_api.g_false
3542 ,p_contiguity_override => fnd_api.g_true
3543 ,x_exception_material_tbl => l_exception_material_tbl
3544 ,x_batch_header_rec => l_batch_header_rec
3545 ,x_return_status => x_return_status);
3546
3547 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3548 l_proc := 'gme_scale_batch_pvt.scale_batch';
3549 RAISE error_processing;
3550 END IF;
3551 END IF; --IF l_factor <> 1
3552
3553 IF(l_mtl_dtl_rec.material_requirement_date <> l_db_mtl_dtl_rec.material_requirement_date ) THEN
3554 IF(l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3555 gme_common_pvt.is_material_assoc_to_step
3556 (l_ph_mtl_dtl_rec.material_detail_id )) THEN
3557
3558 /* REWORK Sunitha Bug 5353941. Following select statement will only execute for ingredient of parent batch and never for product of phantom batch as we have the check of line type to be ingredient. */
3559
3560 l_ph_batch_step_rec.batch_id:=l_db_mtl_dtl_rec.phantom_id;
3561
3562 SELECT batchstep_id INTO l_ph_batch_step_rec.batchstep_id
3563 FROM gme_batch_step_items
3564 WHERE batch_id = l_mtl_dtl_rec.phantom_id
3565 AND material_detail_id = l_ph_mtl_dtl_rec.material_detail_id;
3566 IF NOT gme_batch_steps_dbl.fetch_row(l_ph_batch_step_rec, l_ph_batch_step_rec) THEN
3567 l_proc := 'gme_batch_steps_dbl.fetch_row';
3568 RAISE error_dbl;
3569 END IF;
3570 l_ph_batch_step_rec.plan_cmplt_date:=l_mtl_dtl_rec.material_requirement_date;
3571 l_ph_batch_step_rec.plan_start_date:=NULL;
3572 gme_reschedule_step_pvt.reschedule_step
3573 (p_batch_step_rec => l_ph_batch_step_rec
3574 ,p_source_step_id_tbl => l_step_tbl
3575 ,p_contiguity_override => fnd_api.g_true
3576 ,p_reschedule_preceding => fnd_api.g_true
3577 ,p_reschedule_succeeding => fnd_api.g_true
3578 ,p_use_workday_cal => fnd_api.g_false
3579 ,x_batch_step_rec => x_batch_step_rec
3580 ,x_return_status => x_return_status);
3581 ELSE
3582 l_ph_batch_header_rec.plan_cmplt_date:=l_mtl_dtl_rec.material_requirement_date;
3583 l_ph_batch_header_rec.plan_start_date:=NULL;
3584 gme_reschedule_batch_pvt.reschedule_batch
3585 (p_batch_header_rec => l_ph_batch_header_rec
3586 ,p_use_workday_cal => fnd_api.g_false
3587 ,p_contiguity_override => fnd_api.g_true
3588 ,x_batch_header_rec => l_phantom_batch_header_rec_out
3589 ,x_return_status => x_return_status);
3590 END IF;
3591 END IF; --l_mtl_dtl_rec.material_requirement_date <> l_db_mtl_dtl_rec.material_requirement_date
3592 /* End Bug 5353941 REWORK*/
3593 END IF;--IF l_db_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing
3594 ELSE -- if it is phantom Product
3595 /*Sunitha Ch. Bug#5391396 rescheduling batch/step when update yield Type
3596 of the Child batch is done */
3597 IF ( l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3598 gme_common_pvt.is_material_assoc_to_step
3599 (l_ph_mtl_dtl_rec.material_detail_id )) THEN
3600 SELECT plan_start_date
3601 INTO l_plan_cmplt_date
3602 FROM gme_batch_steps
3603 WHERE batch_id = l_ph_mtl_dtl_rec.batch_id
3604 AND batchstep_id =
3605 (SELECT batchstep_id
3606 FROM gme_batch_step_items
3607 WHERE batch_id = l_ph_mtl_dtl_rec.batch_id
3608 AND material_detail_id =
3609 l_ph_mtl_dtl_rec.material_detail_id );
3610 ELSE
3611 SELECT plan_start_date
3612 INTO l_plan_cmplt_date
3613 FROM gme_batch_header
3614 WHERE batch_id = l_ph_mtl_dtl_rec.batch_id;
3615 END IF;
3616 l_mtl_dtl_rec.material_requirement_date:=l_plan_cmplt_date;
3617 l_ph_mtl_dtl_rec.material_requirement_date := l_mtl_dtl_rec.material_requirement_date;
3618 IF(l_mtl_dtl_rec.release_type = gme_common_pvt.g_mtl_autobystep_release AND
3619 gme_common_pvt.is_material_assoc_to_step
3620 (l_mtl_dtl_rec.material_detail_id )) THEN
3621
3622 /* Sunitha Bug 5391396 . Following select statement will only execute for ingredient of parent batch and never for product of phantom batch as we have the check of line type to be ingredient. */
3623 l_batch_step_rec:=p_batch_step_rec;
3624 l_batch_step_rec.plan_start_date:=NULL;
3625 l_batch_step_rec.plan_cmplt_date:=l_plan_cmplt_date;
3626 gme_reschedule_step_pvt.reschedule_step
3627 (p_batch_step_rec => l_batch_step_rec
3628 ,p_source_step_id_tbl => l_step_tbl
3629 ,p_contiguity_override => fnd_api.g_true
3630 ,p_reschedule_preceding => fnd_api.g_true
3631 ,p_reschedule_succeeding => fnd_api.g_true
3632 ,p_use_workday_cal => fnd_api.g_false
3633 ,x_batch_step_rec => x_batch_step_rec
3634 ,x_return_status => x_return_status);
3635 ELSE
3636 l_batch_header_rec:=p_batch_header_rec;
3637 l_batch_header_rec.plan_start_date:=NULL;
3638 l_batch_header_rec.plan_cmplt_date:=l_plan_cmplt_date;
3639 gme_reschedule_batch_pvt.reschedule_batch
3640 (p_batch_header_rec => l_batch_header_rec
3641 ,p_use_workday_cal => fnd_api.g_false
3642 ,p_contiguity_override => fnd_api.g_true
3643 ,x_batch_header_rec => l_phantom_batch_header_rec_out
3644 ,x_return_status => x_return_status);
3645 END IF;
3646 END IF;--IF l_db_mtl_dtl_rec.phantom_id IS NOT NULL
3647
3648 /* Sunitha REWORK Bug 5353941. We do not need to call fetch_row, as this will override the updated values of material_requirement_date, release_type, subinventory and locator that have been set above. */
3649 SELECT last_update_date INTO l_ph_mtl_dtl_rec.last_update_date
3650 FROM gme_material_details
3651 WHERE batch_id = l_ph_mtl_dtl_rec.batch_id
3652 AND material_detail_id = l_ph_mtl_dtl_rec.material_detail_id;
3653
3654
3655 IF NOT gme_material_details_dbl.update_row (l_ph_mtl_dtl_rec) THEN
3656 l_proc := 'gme_material_details_dbl.update_row';
3657 RAISE error_dbl;
3658 END IF;
3659
3660 END IF; -- IF l_mtl_dtl_rec.phantom_line_id IS NOT NULL THEN
3661
3662 -- Bug 13076579 - Restructured following condition.
3663 /*
3664 IF l_factor <> 1 THEN -- will only be for pending and WIP
3665 gme_move_orders_pvt.update_move_order_lines
3666 (p_batch_id => l_mtl_dtl_rec.batch_id
3667 ,p_material_detail_id => l_mtl_dtl_rec.material_detail_id
3668 ,p_new_qty => l_qty
3669 ,p_new_date => l_mtl_dtl_rec.material_requirement_date
3670 ,p_invis_move_line_id => NULL
3671 ,x_return_status => x_return_status);
3672
3673 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3674 l_proc := 'gme_move_orders_pvt.update_move_order_lines';
3675 RAISE error_processing;
3676 END IF;
3677 END IF;
3678 */
3679 IF l_factor <> 1 THEN -- will only be for pending and WIP
3680 -- Bug 13076579 - Update move orders only when user is lowering qty from original.
3681 -- l_status is the batch_status and l_qty is the new qty.
3682 l_compare_qty := NVL(l_db_mtl_dtl_rec.wip_plan_qty, 0);
3683 IF l_status = 1 THEN
3684 l_compare_qty := l_db_mtl_dtl_rec.plan_qty;
3685 END IF;
3686
3687 -- Bug 13076579 - Update move orders only when decreasing.
3688 -- Increase differences will be accounted for when user does picking.
3689 IF (l_qty < l_compare_qty) THEN
3690 -- Bug 14612460 - if the new qty is zero then we just need to delete the move orders.
3691 IF (l_qty > 0) THEN
3692 gme_move_orders_pvt.update_move_order_lines
3693 (p_batch_id => l_mtl_dtl_rec.batch_id
3694 ,p_material_detail_id => l_mtl_dtl_rec.material_detail_id
3695 ,p_new_qty => l_qty
3696 ,p_new_date => l_mtl_dtl_rec.material_requirement_date
3697 ,p_invis_move_line_id => NULL
3698 ,x_return_status => x_return_status);
3699
3700 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3701 l_proc := 'gme_move_orders_pvt.update_move_order_lines';
3702 RAISE error_processing;
3703 END IF;
3704 ELSE
3705 gme_move_orders_pvt.delete_move_order_lines
3706 (p_organization_id => l_mtl_dtl_rec.organization_id
3707 ,p_batch_id => l_mtl_dtl_rec.batch_id
3708 ,p_material_detail_id => l_mtl_dtl_rec.material_detail_id
3709 ,p_invis_move_line_id => NULL
3710 ,x_return_status => x_return_status);
3711
3712 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3713 l_proc := 'gme_move_orders_pvt.delete_move_order_lines';
3714 RAISE error_processing;
3715 END IF;
3716 END IF;
3717 END IF;
3718 END IF;
3719
3720
3721
3722 IF l_mtl_dtl_rec.phantom_type <> l_db_mtl_dtl_rec.phantom_type THEN
3723 IF l_mtl_dtl_rec.phantom_type IN (1,2) THEN -- phantom ing should not have invisible mo line
3724 gme_move_orders_pvt.delete_move_order_lines
3725 (p_organization_id => p_batch_header_rec.organization_id
3726 ,p_batch_id => p_batch_header_rec.batch_id
3727 ,p_material_detail_id => l_mtl_dtl_rec.material_detail_id
3728 ,p_invis_move_line_id => l_mtl_dtl_rec.move_order_line_id
3729 ,x_return_status => x_return_status);
3730
3731 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3732 l_proc := 'gme_move_orders_pvt.delete_move_order_lines';
3733 RAISE error_processing;
3734 END IF;
3735
3736 l_mtl_dtl_rec.move_order_line_id := NULL;
3737
3738 ELSE -- phantom type is changed to 0 -> not a phantom, so the invisible mo line must be created
3739 l_material_detail_tbl (1) := l_mtl_dtl_rec;
3740
3741 -- add material line into invisible move order
3742 gme_move_orders_pvt.create_move_order_lines
3743 (p_move_order_header_id => p_batch_header_rec.move_order_header_id
3744 ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
3745 ,p_material_details_tbl => l_material_detail_tbl
3746 ,x_material_details_tbl => l_out_material_detail_tbl
3747 ,x_trolin_tbl => l_trolin_tbl
3748 ,x_return_status => x_return_status);
3749
3750 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3751 l_proc := 'gme_move_orders_pvt.create_move_order_lines';
3752 RAISE error_processing;
3753 END IF;
3754
3755 l_mtl_dtl_rec := l_out_material_detail_tbl(1);
3756 END IF;
3757 END IF;
3758
3759 -- 4944024 BEGIN
3760 -- If there is a decrease in anticipated yield, then reservations associated to this supply
3761 -- need to be decreased
3762 -- ========================================================================================
3763 IF l_mtl_dtl_rec.line_type <> -1 AND
3764 (l_mtl_dtl_rec.plan_qty < l_db_mtl_dtl_rec.plan_qty OR
3765 l_mtl_dtl_rec.wip_plan_qty < l_db_mtl_dtl_rec.wip_plan_qty OR
3766 l_mtl_dtl_rec.dtl_um <> l_db_mtl_dtl_rec.dtl_um) THEN
3767 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3768 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoking relieve_prod_supply_resv' );
3769 END IF;
3770 gme_supply_res_pvt.relieve_prod_supply_resv (
3771 p_matl_dtl_rec => l_mtl_dtl_rec
3772 ,x_msg_count => l_message_count
3773 ,x_msg_data => l_message_list
3774 ,x_return_status => x_return_status);
3775
3776 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3777 l_proc := 'gme_reservations_pvt.relieve_prod_supply_resv ';
3778 RAISE error_processing;
3779 END IF;
3780 END IF;
3781 -- 4944024 KYH END
3782 SELECT last_update_date INTO l_mtl_dtl_rec.last_update_date
3783 FROM gme_material_details
3784 WHERE batch_id = l_mtl_dtl_rec.batch_id
3785 AND material_detail_id = l_mtl_dtl_rec.material_detail_id;
3786 IF NOT gme_material_details_dbl.update_row (l_mtl_dtl_rec) THEN
3787 l_proc := 'gme_material_details_dbl.update_row';
3788 RAISE error_dbl;
3789 -- nsinghi bug#5208923. added the else part.
3790 ELSE
3791 gme_common_pvt.get_who(x_user_ident => x_material_detail_rec.last_updated_by,
3792 x_login_id => x_material_detail_rec.last_update_login,
3793 x_timestamp => x_material_detail_rec.last_update_date,
3794 x_return_status => x_return_status);
3795 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3796 l_proc := 'gme_common_pvt.get_who ';
3797 RAISE error_processing;
3798 END IF;
3799
3800 END IF;
3801
3802 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3803 gme_debug.put_line ( g_pkg_name
3804 || '.'
3805 || l_api_name
3806 || ' after gme_material_details_dbl.update_row');
3807 gme_debug.put_line ( g_pkg_name
3808 || '.'
3809 || l_api_name
3810 || ' successfully updated material_detail_id= '
3811 || TO_CHAR (l_mtl_dtl_rec.material_detail_id) );
3812 END IF;
3813
3814 IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
3815 gme_trans_engine_util.load_rsrc_trans
3816 (p_batch_row => p_batch_header_rec
3817 ,x_rsc_row_count => l_rsc_count
3818 ,x_return_status => x_return_status);
3819
3820 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3821 l_proc := 'gme_trans_engine_util.load_rsrc_trans';
3822 RAISE error_processing;
3823 END IF;
3824 gme_update_step_qty_pvt.update_step_qty
3825 (p_batch_step_rec => p_batch_step_rec
3826 ,x_message_count => l_message_count
3827 ,x_message_list => l_message_list
3828 ,x_return_status => x_return_status
3829 ,x_batch_step_rec => l_batch_step_rec);
3830
3831 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3832 l_proc := 'gme_update_step_qty_pvt.update_step_qty';
3833 RAISE error_processing;
3834 END IF;
3835
3836 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3837 gme_debug.put_line
3838 ( g_pkg_name
3839 || '.'
3840 || l_api_name
3841 || ': '
3842 || ' after gme_update_step_qty_pvt.update_step_qty: successful');
3843 END IF;
3844 END IF;
3845
3846 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3847 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
3848 END IF;
3849
3850 EXCEPTION
3851 WHEN error_processing THEN
3852 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3853 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': ' || l_proc|| ' error returned');
3854 END IF;
3855 WHEN error_dbl THEN
3856 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
3857 x_return_status := FND_API.g_ret_sts_unexp_error;
3858
3859 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3860 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '
3861 || l_proc|| ' unexpected error: '|| SQLERRM);
3862 END IF;
3863 WHEN OTHERS THEN
3864 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3865
3866 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3867 gme_debug.put_line ( 'Unexpected error: '
3868 || g_pkg_name
3869 || '.'
3870 || l_api_name
3871 || ': '
3872 || SQLERRM);
3873 END IF;
3874
3875 x_return_status := fnd_api.g_ret_sts_unexp_error;
3876 END update_material_line;
3877
3878 --Bug#5078853 removed p_validate_flexfields parameter
3879 PROCEDURE val_and_pop_material_for_upd (
3880 p_batch_header_rec IN gme_batch_header%ROWTYPE
3881 ,p_material_detail_rec IN gme_material_details%ROWTYPE
3882 ,p_stored_material_detail_rec IN gme_material_details%ROWTYPE
3883 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
3884 ,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE
3885 ,x_return_status OUT NOCOPY VARCHAR2)
3886 IS
3887 l_item_rec mtl_system_items_b%ROWTYPE;
3888 l_batch_status NUMBER;
3889 l_status NUMBER;
3890 l_step_status NUMBER;
3891 l_material_detail_rec gme_material_details%ROWTYPE;
3892
3893 l_scale_type NUMBER;
3894 l_scale_round_var NUMBER;
3895 l_val_proc VARCHAR2 (100);
3896 l_api_name CONSTANT VARCHAR2 (30) := 'val_and_pop_material_for_upd';
3897
3898 l_field VARCHAR2(100);
3899
3900 error_no_upd EXCEPTION;
3901 val_error EXCEPTION;
3902 expected_error EXCEPTION;
3903 error_no_null EXCEPTION;
3904 BEGIN
3905 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
3906 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
3907 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_header_rec.batch_id='||p_batch_header_rec.batch_id);
3908 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_material_detail_rec.material_detail_id='||p_material_detail_rec.material_detail_id);
3909 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_step_rec.batchstep_id='||p_batch_step_rec.batchstep_id);
3910 END IF;
3911
3912 /* Set return status to success initially */
3913 x_return_status := fnd_api.g_ret_sts_success;
3914
3915 -- Following are not supported for update in API and will be ignored...
3916 -- cost
3917 -- alloc_ind
3918 -- text_code
3919 x_material_detail_rec := p_stored_material_detail_rec;
3920 l_material_detail_rec := p_material_detail_rec;
3921
3922 l_batch_status := p_batch_header_rec.batch_status;
3923 l_step_status := p_batch_step_rec.step_status;
3924
3925 IF p_material_detail_rec.formulaline_id IS NOT NULL THEN
3926 gme_common_pvt.log_message ('GME_FORMID_CHG_NOT_ALLOWED');
3927 RAISE error_no_upd;
3928 END IF;
3929
3930 IF p_material_detail_rec.inventory_item_id IS NOT NULL THEN
3931 gme_common_pvt.log_message ('GME_ITEMID_CHG_NOT_ALLOWED');
3932 RAISE error_no_upd;
3933 END IF;
3934
3935 --Bug#5078853
3936 IF p_material_detail_rec.phantom_line_id IS NOT NULL OR
3937 p_material_detail_rec.backordered_qty IS NOT NULL OR
3938 p_material_detail_rec.original_primary_qty IS NOT NULL OR
3939 p_material_detail_rec.move_order_line_id IS NOT NULL THEN
3940 gme_common_pvt.log_message ('GME_FIELD_CHG_NOT_ALLOWED');
3941 RAISE error_no_upd;
3942 END IF;
3943
3944 get_item_rec
3945 (p_org_id => p_stored_material_detail_rec.organization_id
3946 ,p_item_id => p_stored_material_detail_rec.inventory_item_id
3947 ,x_item_rec => l_item_rec
3948 ,x_return_status => x_return_status);
3949
3950 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3951 RAISE expected_error;
3952 END IF;
3953
3954 IF p_material_detail_rec.revision IS NOT NULL THEN
3955 IF p_material_detail_rec.revision = fnd_api.g_miss_char THEN
3956 x_material_detail_rec.revision := NULL;
3957 ELSE
3958 x_material_detail_rec.revision := p_material_detail_rec.revision;
3959 validate_revision
3960 (p_revision => x_material_detail_rec.revision
3961 ,p_item_rec => l_item_rec
3962 ,x_return_status => x_return_status);
3963
3964 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3965 l_val_proc := 'validate_revisioin';
3966 RAISE val_error;
3967 END IF;
3968 END IF;
3969 END IF;
3970
3971 IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_byprod THEN
3972 IF p_material_detail_rec.by_product_type IS NOT NULL THEN
3973 IF p_material_detail_rec.by_product_type = fnd_api.g_miss_char THEN
3974 x_material_detail_rec.by_product_type := NULL;
3975 ELSE
3976 x_material_detail_rec.by_product_type :=
3977 p_material_detail_rec.by_product_type;
3978 END IF;
3979
3980 validate_byproduct_type
3981 (p_byproduct_type => x_material_detail_rec.by_product_type
3982 ,x_return_status => x_return_status);
3983
3984 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3985 l_val_proc := 'validate_byproduct_type';
3986 RAISE val_error;
3987 END IF;
3988
3989 END IF;
3990 ELSE
3991 x_material_detail_rec.by_product_type := NULL;
3992 END IF;
3993
3994 IF p_material_detail_rec.release_type IS NOT NULL THEN
3995 IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending THEN
3996 IF p_material_detail_rec.release_type = fnd_api.g_miss_num THEN
3997 l_field := 'release_type';
3998 RAISE error_no_null;
3999 ELSE
4000 x_material_detail_rec.release_type :=
4001 p_material_detail_rec.release_type;
4002 validate_release_type
4003 (p_material_detail_rec => x_material_detail_rec
4004 ,p_release_type => x_material_detail_rec.release_type
4005 ,x_return_status => x_return_status);
4006
4007 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4008 l_val_proc := 'validate_release_type';
4009 RAISE val_error;
4010 END IF;
4011 END IF;
4012 ELSE
4013 gme_common_pvt.log_message ('GME_INV_STAT_UPD_REL');
4014 RAISE expected_error;
4015 END IF;
4016 END IF;
4017
4018 -- Set status of material based on release type, assocation and step status
4019 IF x_material_detail_rec.release_type =
4020 gme_common_pvt.g_mtl_autobystep_release
4021 AND p_batch_step_rec.batchstep_id IS NOT NULL THEN
4022 l_status := p_batch_step_rec.step_status;
4023 ELSE
4024 l_status := p_batch_header_rec.batch_status;
4025 END IF;
4026
4027 IF p_material_detail_rec.plan_qty IS NOT NULL THEN
4028 IF l_batch_status = gme_common_pvt.g_batch_pending THEN
4029 IF p_material_detail_rec.plan_qty = fnd_api.g_miss_num THEN
4030 l_field := 'plan_qty';
4031 RAISE error_no_null;
4032 ELSE
4033 x_material_detail_rec.plan_qty :=
4034 p_material_detail_rec.plan_qty;
4035 validate_plan_qty
4036 (p_plan_qty => x_material_detail_rec.plan_qty
4037 ,x_return_status => x_return_status);
4038
4039 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4040 l_val_proc := 'validate_plan_qty';
4041 RAISE val_error;
4042 END IF;
4043 END IF;
4044 ELSE
4045 gme_common_pvt.log_message ('GME_INV_STAT_UPD_PLAN_QTY');
4046 RAISE expected_error;
4047 END IF;
4048 END IF;
4049
4050 IF p_material_detail_rec.dtl_um IS NOT NULL THEN
4051 IF l_batch_status = gme_common_pvt.g_batch_pending THEN
4052 IF p_material_detail_rec.dtl_um = fnd_api.g_miss_char THEN
4053 l_field := 'dtl_um';
4054 RAISE error_no_null;
4055 ELSE
4056 x_material_detail_rec.dtl_um :=
4057 p_material_detail_rec.dtl_um;
4058
4059 validate_dtl_um
4060 (p_dtl_um => x_material_detail_rec.dtl_um
4061 ,p_primary_uom => l_item_rec.primary_uom_code
4062 ,p_item_id => p_material_detail_rec.inventory_item_id
4063 ,p_org_id => p_material_detail_rec.organization_id
4064 ,x_return_status => x_return_status);
4065
4066 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4067 l_val_proc := 'validate_dtl_um';
4068 RAISE val_error;
4069 END IF;
4070 END IF;
4071 ELSE
4072 gme_common_pvt.log_message ('GME_INV_STAT_UPD_DTL_UM');
4073 RAISE expected_error;
4074 END IF;
4075 END IF;
4076
4077 IF p_material_detail_rec.wip_plan_qty IS NOT NULL THEN
4078 IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip THEN
4079 IF p_material_detail_rec.wip_plan_qty = fnd_api.g_miss_num THEN
4080 l_field := 'wip_plan_qty';
4081 RAISE error_no_null;
4082 ELSE
4083 x_material_detail_rec.wip_plan_qty :=
4084 p_material_detail_rec.wip_plan_qty;
4085 validate_wip_plan_qty
4086 (p_wip_plan_qty => x_material_detail_rec.wip_plan_qty
4087 ,x_return_status => x_return_status);
4088
4089 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4090 l_val_proc := 'validate_wip_plan_qty';
4091 RAISE val_error;
4092 END IF;
4093 END IF;
4094 ELSE
4095 gme_common_pvt.log_message ('GME_INV_STAT_UPD_WIP_PLAN');
4096 RAISE expected_error;
4097 END IF;
4098 END IF;
4099
4100 --Bug#5078853 modified validation for actual qty
4101 IF p_material_detail_rec.actual_qty IS NOT NULL THEN
4102 IF p_batch_header_rec.batch_status IN (gme_common_pvt.g_batch_wip,
4103 gme_common_pvt.g_batch_completed )THEN
4104 IF p_material_detail_rec.actual_qty = fnd_api.g_miss_num THEN
4105 l_field := 'actual_qty';
4106 RAISE error_no_null;
4107 ELSE
4108 x_material_detail_rec.actual_qty := p_material_detail_rec.actual_qty;
4109 validate_actual_qty
4110 (p_actual_qty => x_material_detail_rec.actual_qty
4111 ,x_return_status => x_return_status);
4112
4113 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4114 l_val_proc := 'validate_actual_qty';
4115 RAISE val_error;
4116 END IF;
4117 END IF;
4118 ELSE
4119 gme_common_pvt.log_message ('GME_INV_STAT_UPD_ACT');
4120 RAISE expected_error;
4121 END IF; /*status check */
4122 END IF;
4123
4124 --Bug#5078853 allow scrap factor to be changed in pending
4125 -- don't allow NULL
4126 IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending AND
4127 p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
4128
4129 IF p_material_detail_rec.scrap_factor IS NOT NULL THEN
4130 IF p_material_detail_rec.scrap_factor = fnd_api.g_miss_num THEN
4131 l_field := 'scrap_factor';
4132 RAISE error_no_null;
4133 ELSE
4134 validate_scrap_factor
4135 (p_scrap => p_material_detail_rec.scrap_factor
4136 ,x_return_status => x_return_status);
4137
4138 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4139 l_val_proc := 'validate_scrap_factor';
4140 RAISE val_error;
4141 END IF;
4142 -- scrap is a percent
4143 x_material_detail_rec.scrap_factor := p_material_detail_rec.scrap_factor / 100;
4144 /* nsinghi Bug4911461 Re-work. Modify Plan qty to include scrap factor. */
4145 IF p_material_detail_rec.plan_qty IS NOT NULL THEN
4146 x_material_detail_rec.plan_qty := p_material_detail_rec.plan_qty +
4147 (x_material_detail_rec.scrap_factor * p_material_detail_rec.plan_qty);
4148 ELSE
4149 x_material_detail_rec.plan_qty := p_stored_material_detail_rec.plan_qty +
4150 (x_material_detail_rec.scrap_factor * p_stored_material_detail_rec.plan_qty);
4151 END IF;
4152
4153 END IF; /* miss_num */
4154 END IF; /*p_material_detail_rec.scrap_factor IS NOT NULL*/
4155 END IF;
4156
4157 --Bug#5078853 scale type can be changed in both pending and WIP
4158 IF p_material_detail_rec.scale_type IS NOT NULL THEN
4159 IF p_batch_header_rec.batch_status IN ( gme_common_pvt.g_batch_pending,
4160 gme_common_pvt.g_batch_wip ) THEN
4161 -- scale_type can be changed, but not to NULL
4162 IF p_material_detail_rec.scale_type = fnd_api.g_miss_num THEN
4163 l_field := 'scale_type';
4164 RAISE error_no_null;
4165 ELSE
4166 validate_scale_type
4167 (p_scale_type => p_material_detail_rec.scale_type
4168 ,x_return_status => x_return_status);
4169
4170 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4171 l_val_proc := 'validate_scale_type';
4172 RAISE val_error;
4173 END IF;
4174 x_material_detail_rec.scale_type := p_material_detail_rec.scale_type;
4175 END IF;
4176
4177 -- Following validation belongs in form also
4178 IF x_material_detail_rec.scale_type = 2 THEN -- integer scaling
4179 -- Scale_Multiple
4180 validate_scale_multiple
4181 (p_scale_mult => p_material_detail_rec.scale_multiple
4182 ,x_return_status => x_return_status);
4183
4184 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4185 l_val_proc := 'validate_scale_multiple';
4186 RAISE val_error;
4187 END IF;
4188 x_material_detail_rec.scale_multiple := p_material_detail_rec.scale_multiple;
4189
4190 -- Scale_Rounding_Variance
4191 validate_scale_round_var
4192 (p_scale_var => p_material_detail_rec.scale_rounding_variance
4193 ,x_return_status => x_return_status);
4194
4195 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4196 l_val_proc := 'validate_scale_round_var';
4197 RAISE val_error;
4198 END IF;
4199
4200 x_material_detail_rec.scale_rounding_variance :=
4201 p_material_detail_rec.scale_rounding_variance / 100;
4202
4203 -- Rounding_Direction
4204 validate_rounding_direction
4205 (p_round_dir => p_material_detail_rec.rounding_direction
4206 ,x_return_status => x_return_status);
4207
4208 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4209 l_val_proc := 'validate_rounding_direction';
4210 RAISE val_error;
4211 END IF;
4212 x_material_detail_rec.rounding_direction := p_material_detail_rec.rounding_direction;
4213 ELSE
4214 x_material_detail_rec.scale_multiple := NULL;
4215 x_material_detail_rec.scale_rounding_variance := NULL;
4216 x_material_detail_rec.rounding_direction := NULL;
4217 END IF; /* x_material_detail_rec.scale_type = 2 */
4218 ELSE
4219 gme_common_pvt.log_message ('GME_INV_STAT_UPD_SCALE_TYPE');
4220 RAISE expected_error;
4221 END IF; /* status check */
4222 END IF; /* p_material_detail_rec.scale_type IS NOT NULL */
4223
4224 -- can change cost alloc for product; ignore other line types
4225 IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod THEN
4226 -- validate 0 <= cost_alloc <= 1
4227 -- at save_batch, check that sum(cost_alloc for all products) <= 1
4228 validate_cost_alloc
4229 (p_material_detail_rec => p_material_detail_rec
4230 ,x_return_status => x_return_status);
4231
4232 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4233 l_val_proc := 'validate_cost_alloc';
4234 RAISE val_error;
4235 END IF;
4236 x_material_detail_rec.cost_alloc := p_material_detail_rec.cost_alloc;
4237 END IF;
4238
4239 --Bug#5078853 modified
4240 -- can change phantom type for ingredient only if not exploded;
4241 -- error otherwise
4242 IF p_material_detail_rec.phantom_type IS NOT NULL
4243 AND x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing
4244 AND x_material_detail_rec.phantom_id IS NULL
4245 AND p_material_detail_rec.phantom_type <> x_material_detail_rec.phantom_type THEN
4246
4247 IF p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending THEN
4248 x_material_detail_rec.phantom_type := p_material_detail_rec.phantom_type;
4249
4250 validate_phantom_type
4251 (p_phantom_type => x_material_detail_rec.phantom_type
4252 ,x_return_status => x_return_status);
4253
4254 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4255 l_val_proc := 'validate_phantom_type';
4256 RAISE val_error;
4257 END IF;
4258 --Bug#5078853 changed to x_material_detail_rec
4259 validate_phantom_type_change
4260 (p_material_detail_rec => x_material_detail_rec
4261 ,x_return_status => x_return_status);
4262 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4263 l_val_proc := 'validate_phantom_type_change';
4264 RAISE val_error;
4265 END IF;
4266 ELSE
4267 gme_common_pvt.log_message ('GME_INV_STAT_UPD_PHAN_TYPE');
4268 RAISE expected_error;
4269 END IF; /* batch status check */
4270 END IF;
4271
4272 -- can't update to NULL; only update for ingred, ignore other line type
4273 IF x_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing THEN
4274 validate_contr_yield_ind
4275 (p_contr_yield_ind => x_material_detail_rec.contribute_yield_ind
4276 ,x_return_status => x_return_status);
4277
4278 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4279 l_val_proc := 'validate_contr_yield_ind';
4280 RAISE val_error;
4281 END IF;
4282 END IF;
4283
4284 -- can't update to NULL
4285 validate_contr_step_qty_ind
4286 (p_contr_step_qty_ind => x_material_detail_rec.contribute_step_qty_ind
4287 ,x_return_status => x_return_status);
4288
4289 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4290 l_val_proc := 'validate_contr_step_qty_ind';
4291 RAISE val_error;
4292 END IF;
4293
4294 IF p_material_detail_rec.subinventory = fnd_api.g_miss_char THEN
4295 x_material_detail_rec.subinventory := NULL;
4296 x_material_detail_rec.locator_id := NULL;
4297 ELSIF p_material_detail_rec.subinventory IS NOT NULL THEN
4298 x_material_detail_rec.subinventory :=
4299 p_material_detail_rec.subinventory;
4300 validate_subinventory
4301 (p_subinv => x_material_detail_rec.subinventory
4302 ,p_item_rec => l_item_rec
4303 ,x_return_status => x_return_status);
4304
4305 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4306 l_val_proc := 'validate_subinventory';
4307 RAISE val_error;
4308 END IF;
4309
4310 IF p_material_detail_rec.locator_id = fnd_api.g_miss_num THEN
4311 x_material_detail_rec.locator_id := NULL;
4312 ELSE
4313 x_material_detail_rec.locator_id :=
4314 p_material_detail_rec.locator_id;
4315 validate_locator
4316 (p_subinv => x_material_detail_rec.subinventory
4317 ,p_locator_id => x_material_detail_rec.locator_id
4318 ,p_item_rec => l_item_rec
4319 ,p_line_type => x_material_detail_rec.line_type
4320 ,x_return_status => x_return_status);
4321
4322 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4323 l_val_proc := 'validate_locator';
4324 RAISE val_error;
4325 END IF;
4326 END IF;
4327 ELSE -- subinv is NULL not changing it, but maybe changing locator
4328 IF p_material_detail_rec.locator_id = fnd_api.g_miss_num THEN
4329 x_material_detail_rec.locator_id := NULL;
4330 ELSE
4331 IF p_material_detail_rec.locator_id IS NOT NULL THEN
4332 x_material_detail_rec.locator_id :=
4333 p_material_detail_rec.locator_id;
4334 END IF;
4335
4336 -- if locator is null, then x_material_detail_rec.locator
4337 -- is the value stored in the db, validate that with subinv
4338 -- as long as they have values
4339 IF x_material_detail_rec.subinventory IS NOT NULL
4340 AND x_material_detail_rec.locator_id IS NOT NULL THEN
4341 validate_locator
4342 (p_subinv => x_material_detail_rec.subinventory
4343 ,p_locator_id => x_material_detail_rec.locator_id
4344 ,p_item_rec => l_item_rec
4345 ,p_line_type => x_material_detail_rec.line_type
4346 ,x_return_status => x_return_status);
4347
4348 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4349 l_val_proc := 'validate_locator';
4350 RAISE val_error;
4351 END IF;
4352 END IF;
4353 END IF;
4354 END IF;
4355
4356 /* Bug#5078853 added the following call for flex field validation
4357 gme_common_pvt.g_flex_validate_prof has to be set in public API to enforce flex field validation
4358 l_material_detail_rec will have flex field values passed from public API , if any*/
4359
4360 l_material_detail_rec.material_detail_id := x_material_detail_rec.material_detail_id;
4361 gme_validate_flex_fld_pvt.validate_flex_material_details
4362 ( p_material_detail_rec => l_material_detail_rec
4363 ,x_material_detail_rec => x_material_detail_rec
4364 ,x_return_status => x_return_status);
4365
4366 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4367 l_val_proc := 'validate_flex_material_detail';
4368 RAISE val_error;
4369 END IF;
4370
4371 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4372 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
4373 END IF;
4374
4375 EXCEPTION
4376 WHEN error_no_null THEN
4377 gme_common_pvt.log_message ('GME_INVALID_VALUE_SPECIFIED'
4378 ,'FIELD_NAME'
4379 ,l_field);
4380 x_return_status := fnd_api.g_ret_sts_error;
4381 WHEN error_no_upd OR expected_error THEN
4382 x_return_status := fnd_api.g_ret_sts_error;
4383 WHEN val_error THEN
4384 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4385 gme_debug.put_line (g_pkg_name||'.'||l_api_name||': validation error from proc: '|| l_val_proc);
4386 END IF;
4387 WHEN OTHERS THEN
4388 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4389
4390 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4391 gme_debug.put_line ( 'Unexpected error: '
4392 || g_pkg_name
4393 || '.'
4394 || l_api_name
4395 || ': '
4396 || SQLERRM);
4397 END IF;
4398
4399 x_return_status := fnd_api.g_ret_sts_unexp_error;
4400 END val_and_pop_material_for_upd;
4401
4402 --Bug#5078853 Procedure Created
4403 PROCEDURE validate_material_for_del (
4404 p_batch_header_rec IN gme_batch_header%ROWTYPE
4405 ,p_material_detail_rec IN gme_material_details%ROWTYPE
4406 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
4407 ,x_return_status OUT NOCOPY VARCHAR2) IS
4408
4409 l_api_name CONSTANT VARCHAR2 (30) := 'validate_material_for_del';
4410
4411 CURSOR c_get_delete(v_org_id NUMBER) IS
4412 SELECT delete_material_ind
4413 FROM gme_parameters
4414 WHERE organization_id = v_org_id;
4415
4416 CURSOR num_detail_lines (v_batch_id NUMBER, v_line_type NUMBER) IS
4417 SELECT COUNT (*)
4418 FROM gme_material_details
4419 WHERE batch_id = v_batch_id AND
4420 line_type = v_line_type;
4421
4422 CURSOR c_prim_prod(v_rule_id NUMBER, v_det_id NUMBER) IS
4423 SELECT 1
4424 FROM gmd_recipe_validity_rules
4425 WHERE recipe_validity_rule_id = v_rule_id
4426 AND inventory_item_id = (SELECT inventory_item_id
4427 FROM gme_material_details
4428 WHERE material_detail_id = v_det_id);
4429
4430 /*CURSOR cur_parent_phant (v_batch_id NUMBER, v_item_id NUMBER) IS
4431 SELECT 1
4432 FROM sys.DUAL
4433 WHERE EXISTS ( SELECT 1
4434 FROM gme_batch_header h, gmd_recipe_validity_rules r
4435 WHERE h.batch_id = v_batch_id
4436 AND h.recipe_validity_rule_id = r.recipe_validity_rule_id
4437 AND r.item_id = v_item_id
4438 AND h.parentline_id > 0); */
4439
4440 l_delete_ind NUMBER;
4441 l_dummy NUMBER;
4442 l_material_count NUMBER := 0;
4443
4444 val_error EXCEPTION;
4445 BEGIN
4446 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4447 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
4448 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_material_detail_rec.batch_id='||p_material_detail_rec.batch_id);
4449 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_material_detail_rec.material_detail_id='||p_material_detail_rec.material_detail_id);
4450 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_step_rec.batch_step_id='||p_batch_step_rec.batchstep_id);
4451 END IF;
4452
4453 /* Set return status to success initially */
4454 x_return_status := fnd_api.g_ret_sts_success;
4455
4456 /* validate batch for material deletion */
4457 IF p_batch_header_rec.batch_type <> 0 THEN
4458 gme_common_pvt.log_message('GME_INV_BATCH_TYPE_OPER');
4459 RAISE val_error;
4460 END IF;
4461
4462 --Fetch allow material deletion profile
4463 OPEN c_get_delete(p_batch_header_rec.organization_id);
4464 FETCH c_get_delete INTO l_delete_ind;
4465 CLOSE c_get_delete;
4466
4467 l_delete_ind := NVL(l_delete_ind, 1);
4468
4469 IF NOT( p_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending OR
4470 (l_delete_ind = 2 AND
4471 p_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip AND
4472 p_batch_header_rec. automatic_step_calculation = 0)
4473 ) THEN
4474 gme_common_pvt.log_message ('GME_INV_BATCH_STATUS_OPER');
4475 RAISE val_error;
4476 END IF;
4477
4478 /* check step status if material is assocaited to step */
4479 IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
4480 IF (p_batch_step_rec.step_status NOT IN (gme_common_pvt.g_step_pending
4481 ,gme_common_pvt.g_step_wip)) THEN
4482 gme_common_pvt.log_message('PC_STEP_STATUS_ERR');
4483 RAISE val_error;
4484 END IF;
4485 END IF; /* p_batch_step_rec.batchstep_id IS NOT NULL */
4486
4487 --line can't be deleted if line is ing and has an exploded phantom
4488 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_ing AND
4489 p_material_detail_rec.phantom_id IS NOT NULL THEN
4490 gme_common_pvt.log_message('GME_NO_DEL_PHANT_ING');
4491 RAISE val_error;
4492 END IF; /* exploded phantom check */
4493
4494 /* if there is only one ingredient or product we should not the delete */
4495 IF p_material_detail_rec.line_type IN (gme_common_pvt.g_line_type_ing,
4496 gme_common_pvt.g_line_type_prod) THEN
4497 OPEN num_detail_lines(p_batch_header_rec.batch_id,p_material_detail_rec.line_type);
4498 FETCH num_detail_lines INTO l_material_count;
4499 CLOSE num_detail_lines;
4500 IF l_material_count = 1 THEN
4501 gme_common_pvt.log_message('GME_ONE_ING_PROD_REQD');
4502 RAISE val_error;
4503 END IF;
4504 END IF; /* number of lines check */
4505
4506 -- Bug 10062802 - Add formulaline_id condition to validation.
4507 IF p_material_detail_rec.line_type = gme_common_pvt.g_line_type_prod AND
4508 p_material_detail_rec.formulaline_id IS NOT NULL THEN
4509 --if product is primary product then do not allow deletion
4510 OPEN c_prim_prod(p_batch_header_rec.recipe_validity_rule_id, p_material_detail_rec.material_detail_id);
4511 FETCH c_prim_prod INTO l_dummy;
4512 IF c_prim_prod%FOUND THEN
4513 CLOSE c_prim_prod;
4514 gme_common_pvt.log_message('GME_PRIM_PROD_NO_DEL');
4515 RAISE val_error;
4516 END IF;
4517 CLOSE c_prim_prod;
4518
4519 /*OPEN cur_parent_phant(p_batch_header_rec.batch_id, p_material_detail_rec.inventory_item_id);
4520 FETCH cur_parent_phant INTO l_dummy;
4521 IF cur_parent_phant%FOUND THEN
4522 CLOSE cur_parent_phant;
4523 gme_common_pvt.log_message('GME_NO_DEL_PHANT_PROD');
4524 RAISE val_error;
4525 END IF;
4526 CLOSE cur_parent_phant; */
4527 END IF; /* line type check */
4528
4529 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4530 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
4531 END IF;
4532 EXCEPTION
4533 WHEN val_error THEN
4534 x_return_status := fnd_api.g_ret_sts_error;
4535 WHEN OTHERS THEN
4536 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4537 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4538 gme_debug.put_line ('Unexpected error: '
4539 || g_pkg_name
4540 || '.'
4541 || l_api_name
4542 || ': '
4543 || SQLERRM);
4544 END IF;
4545 x_return_status := fnd_api.g_ret_sts_unexp_error;
4546 END validate_material_for_del;
4547
4548 PROCEDURE validate_phantom_type_change (
4549 p_material_detail_rec IN gme_material_details%ROWTYPE
4550 ,x_return_status OUT NOCOPY VARCHAR2) IS
4551
4552 val_error EXCEPTION;
4553
4554 l_api_name CONSTANT VARCHAR2 (30) := 'validate_phantom_type_change';
4555 BEGIN
4556 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4557 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
4558 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_material_detail_rec.batch_id='||p_material_detail_rec.batch_id);
4559 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_material_detail_rec.material_detail_id='||p_material_detail_rec.material_detail_id);
4560 END IF;
4561
4562 /* Set return status to success initially */
4563 x_return_status := fnd_api.g_ret_sts_success;
4564
4565 IF gme_reservations_pvt.pending_reservations_exist
4566 (p_organization_id => p_material_detail_rec.organization_id
4567 ,p_batch_id => p_material_detail_rec.batch_id
4568 ,p_material_detail_id => p_material_detail_rec.material_detail_id) THEN
4569 gme_common_pvt.log_message ('GME_PENDING_RSRV_EXIST');
4570 RAISE val_error;
4571 END IF;
4572
4573 IF gme_move_orders_pvt.pending_move_orders_exist
4574 (p_organization_id => p_material_detail_rec.organization_id
4575 ,p_batch_id => p_material_detail_rec.batch_id
4576 ,p_material_detail_id => p_material_detail_rec.material_detail_id) THEN
4577 gme_common_pvt.log_message ('GME_PENDING_MO_EXIST');
4578 RAISE val_error;
4579 END IF;
4580
4581 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4582 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
4583 END IF;
4584
4585 EXCEPTION
4586 WHEN val_error THEN
4587 x_return_status := fnd_api.g_ret_sts_error;
4588 WHEN OTHERS THEN
4589 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4590
4591 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4592 gme_debug.put_line ( 'Unexpected error: '
4593 || g_pkg_name
4594 || '.'
4595 || l_api_name
4596 || ': '
4597 || SQLERRM);
4598 END IF;
4599 x_return_status := fnd_api.g_ret_sts_unexp_error;
4600 END validate_phantom_type_change;
4601
4602 PROCEDURE delete_material_line (
4603 p_batch_header_rec IN gme_batch_header%ROWTYPE
4604 ,p_material_detail_rec IN gme_material_details%ROWTYPE
4605 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
4606 ,x_transacted OUT NOCOPY VARCHAR2
4607 ,x_return_status OUT NOCOPY VARCHAR2)
4608 IS
4609
4610 l_message_count NUMBER;
4611 l_message_list VARCHAR2 (2000);
4612 l_batch_step_rec gme_batch_steps%ROWTYPE;
4613 l_rsc_count NUMBER;
4614 l_proc VARCHAR2(100);
4615
4616 l_mmt_tbl gme_common_pvt.mtl_mat_tran_tbl;
4617
4618 error_processing EXCEPTION;
4619 error_dbl EXCEPTION;
4620
4621 l_api_name CONSTANT VARCHAR2 (30) := 'delete_material_line';
4622 BEGIN
4623 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4624 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
4625 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_header_rec.batch_id='||p_batch_header_rec.batch_id);
4626 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_material_detail_rec.material_detail_id='||p_material_detail_rec.material_detail_id);
4627 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' p_batch_step_rec.batchstep_id='||p_batch_step_rec.batchstep_id);
4628 END IF;
4629
4630 /* Set return status to success initially */
4631 x_return_status := fnd_api.g_ret_sts_success;
4632
4633 --Bug#5078853 deleting pending lots if any
4634 gme_pending_product_lots_pvt.delete_pending_product_lot
4635 (p_material_detail_id => p_material_detail_rec.material_detail_id
4636 ,x_return_status => x_return_status);
4637
4638 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4639 l_proc := 'gme_pending_product_lots.delete_pending_product_lot';
4640 RAISE error_processing;
4641 END IF;
4642
4643 gme_move_orders_pvt.delete_move_order_lines
4644 (p_organization_id => p_batch_header_rec.organization_id
4645 ,p_batch_id => p_batch_header_rec.batch_id
4646 ,p_material_detail_id => p_material_detail_rec.material_detail_id
4647 ,p_invis_move_line_id => p_material_detail_rec.move_order_line_id
4648 ,x_return_status => x_return_status);
4649
4650 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4651 l_proc := 'gme_move_orders_pvt.delete_move_order_lines';
4652 RAISE error_processing;
4653 END IF;
4654
4655 gme_reservations_pvt.delete_material_reservations (
4656 p_organization_id => p_batch_header_rec.organization_id
4657 ,p_batch_id => p_batch_header_rec.batch_id
4658 ,p_material_detail_id => p_material_detail_rec.material_detail_id
4659 ,x_return_status => x_return_status);
4660
4661 -- delete all transactions for this line
4662 gme_transactions_pvt.get_mat_trans (
4663 p_mat_det_id => p_material_detail_rec.material_detail_id
4664 ,p_batch_id => p_batch_header_rec.batch_id
4665 ,x_mmt_tbl => l_mmt_tbl
4666 ,x_return_status => x_return_status);
4667
4668 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4669 l_proc := 'gme_transactions_pvt.get_mat_trans';
4670 RAISE error_processing;
4671 END IF;
4672
4673 FOR i in 1..l_mmt_tbl.COUNT LOOP
4674 gme_transactions_pvt.delete_material_txn (
4675 p_transaction_id => l_mmt_tbl(i).transaction_id
4676 ,p_txns_pair => NULL
4677 ,x_return_status => x_return_status);
4678 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4679 l_proc := 'gme_transactions_pvt.delete_material_txn';
4680 RAISE error_processing;
4681 END IF;
4682 END LOOP;
4683
4684 IF l_mmt_tbl.COUNT > 0 THEN
4685 x_transacted := fnd_api.G_TRUE;
4686 ELSE
4687 x_transacted := fnd_api.G_FALSE;
4688 END IF;
4689
4690 -- 4944024 BEGIN
4691 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4692 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' line_type is ' ||p_material_detail_rec.line_type );
4693 END IF;
4694 -- Delete any reservations against this supply source
4695 -- ==================================================
4696 IF NVL(p_material_detail_rec.line_type,0) <> -1 THEN
4697 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4698 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoking delete_prod_supply_resv' );
4699 END IF;
4700 gme_supply_res_pvt.delete_prod_supply_resv (
4701 p_matl_dtl_rec => p_material_detail_rec
4702 ,x_msg_count => l_message_count
4703 ,x_msg_data => l_message_list
4704 ,x_return_status => x_return_status);
4705
4706 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4707 l_proc := 'gme_reservations_pvt.delete_prod_prod_supply_resv ';
4708 RAISE error_processing;
4709 END IF;
4710 END IF;
4711 -- 4944024 END
4712
4713 IF NOT gme_material_details_dbl.delete_row (p_material_detail_rec) THEN
4714 l_proc := 'gme_material_details_dbl.delete_row';
4715 RAISE error_dbl;
4716 END IF;
4717
4718 -- renumber subsequent lines
4719 UPDATE gme_material_details
4720 SET line_no = line_no - 1
4721 ,last_updated_by = gme_common_pvt.g_user_ident
4722 ,last_update_date = gme_common_pvt.g_timestamp
4723 ,last_update_login = gme_common_pvt.g_login_id
4724 WHERE batch_id = p_material_detail_rec.batch_id
4725 AND line_type = p_material_detail_rec.line_type
4726 AND line_no >= p_material_detail_rec.line_no;
4727
4728 IF p_batch_step_rec.batchstep_id IS NOT NULL THEN
4729 DELETE FROM gme_batch_step_items
4730 WHERE material_detail_id =
4731 p_material_detail_rec.material_detail_id
4732 AND batchstep_id = p_batch_step_rec.batchstep_id;
4733
4734 gme_trans_engine_util.load_rsrc_trans
4735 (p_batch_row => p_batch_header_rec
4736 ,x_rsc_row_count => l_rsc_count
4737 ,x_return_status => x_return_status);
4738
4739 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4740 l_proc := 'gme_trans_engine_util.load_rsrc_trans';
4741 RAISE error_processing;
4742 END IF;
4743
4744 gme_update_step_qty_pvt.update_step_qty
4745 (p_batch_step_rec => p_batch_step_rec
4746 ,x_message_count => l_message_count
4747 ,x_message_list => l_message_list
4748 ,x_return_status => x_return_status
4749 ,x_batch_step_rec => l_batch_step_rec);
4750
4751 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4752 l_proc := 'gme_update_step_qty_pvt.update_step_qty';
4753 RAISE error_processing;
4754 END IF;
4755
4756 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4757 gme_debug.put_line
4758 ( g_pkg_name
4759 || '.'
4760 || l_api_name
4761 || ': '
4762 || ' after gme_update_step_qty_pvt.update_step_qty: successful');
4763 END IF;
4764 END IF;
4765
4766 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
4767 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
4768 END IF;
4769
4770 EXCEPTION
4771 WHEN error_processing THEN
4772 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4773 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': ' || l_proc|| ' error returned');
4774 END IF;
4775 WHEN error_dbl THEN
4776 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
4777 x_return_status := FND_API.g_ret_sts_unexp_error;
4778
4779 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4780 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '
4781 || l_proc|| ' unexpected error: '|| SQLERRM);
4782 END IF;
4783 WHEN OTHERS THEN
4784 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4785
4786 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4787 gme_debug.put_line ( 'Unexpected error: '
4788 || g_pkg_name
4789 || '.'
4790 || l_api_name
4791 || ': '
4792 || SQLERRM);
4793 END IF;
4794
4795 x_return_status := fnd_api.g_ret_sts_unexp_error;
4796 END delete_material_line;
4797 END gme_material_detail_pvt;