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