1 PACKAGE BODY gme_phantom_pvt AS
2 /* $Header: GMEVPHBB.pls 120.7.12020000.2 2012/10/15 13:13:16 gmurator ship $ */
3 /*
4 REM **********************************************************************
5 REM * *
6 REM * FILE: GMEVPHBB.pls *
7 REM * PURPOSE: Package Body for the GME PHANTOM API routines *
8 REM * AUTHOR: Thomas Daniel, OPM Development *
9 REM * DATE: July 10th 2001 *
10 REM * HISTORY: *
11 REM * ========
12 04-JUN-2009 G. Muratore Bug 8490219
13 No need to timestamp batch header record when updating the
14 parentline_id as it was just created.
15 PROCEDURE: create_phantom
16
17 07-OCT-2012 G. Muratore Bug 14681911
18 Refresh the variable data before the next update
19 so that the timestamp is accurate.
20 PROCEDURE: create_phantom
21 REM **********************************************************************
22 */
23
24 /*************************************************************************
25 * This file contains procedures for the Phantom Batch APIs for GME in *
26 * Oracle Process Manufacturing (OPM). Each procedure has a common set of *
27 * parameters to which API-specific parameters are appended. *
28 *************************************************************************/
29 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_phantom_pvt';
30 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
31
32 /*===========================================================================================
33 Procedure
34 create_phantom
35 Description
36 This particular procedure creates the phantom batch for the material detail line.
37 Parameters
38 p_material_detail The material detail row to identify the detail line.
39 x_material_detail The material detail out row.
40 p_validity_rule_id Validity rule id for creating the phantom batch.
41 p_ignore_shortages Ignore shortages in the batch auto allocation.
42 x_return_status outcome of the API call
43 S - Success
44 E - Error
45 W - Warning
46 U - Unexpected error
47 History
48 Jun 19 2002 Chandrashekar Tiruvidula Bug# 2378540
49 Moved code from within
50 Jul 24 2002 Shrikant Nene Bug# 2386578 Added parameter
51 p_batch_no
52 07-Sep-2006 Namit S. Bug#5436643 Pass Revision Number during create batch
53 11-JAN-2007 Swapna K Bug#6738476 Added parameter,p_batch_header_rec
54
55 04-JUN-2009 G. Muratore Bug 8490219
56 No need to timestamp batch header record when updating the parentline_id as it was just created.
57 =============================================================================================*/
58 PROCEDURE create_phantom (
59 p_material_detail_rec IN gme_material_details%ROWTYPE
60 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
61 ,p_batch_no IN VARCHAR2 DEFAULT NULL
62 ,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE
63 ,p_validity_rule_id IN NUMBER
64 ,p_use_workday_cal IN VARCHAR2
65 ,p_contiguity_override IN VARCHAR2
66 ,p_use_least_cost_validity_rule IN VARCHAR2 := fnd_api.g_false
67 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
68 ,x_return_status OUT NOCOPY VARCHAR2)
69 IS
70 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_PHANTOM';
71 /* Exception definitions */
72 material_detail_fetch_error EXCEPTION;
73 batch_header_fetch_error EXCEPTION;
74 batch_upd_error EXCEPTION;
75 not_a_phantom EXCEPTION;
76 phantom_exists EXCEPTION;
77 validity_fetch_error EXCEPTION;
78 no_validity_found EXCEPTION;
79 batch_creation_failed EXCEPTION;
80 phantom_item_not_found EXCEPTION;
81 /* Local variables */
82 l_object_type VARCHAR2 (1);
83 l_return_code NUMBER (5);
84 l_validity_rule_id NUMBER;
85 l_msg_count NUMBER;
86 l_msg_list VARCHAR2 (2000);
87 l_plan_qty NUMBER;
88 l_return_status VARCHAR2 (1);
89 l_prod_mtl_dtl_id NUMBER;
90 l_batch_header gme_batch_header%ROWTYPE;
91 l_phant_batch gme_batch_header%ROWTYPE;
92 l_in_phant_batch gme_batch_header%ROWTYPE;
93 l_material_detail gme_material_details%ROWTYPE;
94 l_in_material_detail gme_material_details%ROWTYPE;
95 l_validity_tbl gmd_validity_rules.recipe_validity_tbl;
96
97 CURSOR cur_step_matl_detail (v_material_detail_id NUMBER)
98 IS
99 SELECT d.plan_start_date, d.plan_cmplt_date
100 FROM gme_batch_step_items i, gme_batch_steps d
101 WHERE d.batchstep_id = i.batchstep_id
102 AND i.material_detail_id = v_material_detail_id;
103
104 l_step_rec cur_step_matl_detail%ROWTYPE;
105
106 CURSOR cur_get_phantom_prod (
107 v_batch_id NUMBER
108 ,v_inventory_item_id NUMBER)
109 IS
110 SELECT material_detail_id
111 FROM gme_material_details
112 WHERE batch_id = v_batch_id
113 AND inventory_item_id = v_inventory_item_id
114 AND line_type = gme_common_pvt.g_line_type_prod
115 ORDER BY line_no;
116 BEGIN
117 IF g_debug <= gme_debug.g_log_procedure THEN
118 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
119 || l_api_name);
120 END IF;
121
122 /* Set the return status to success initially */
123 x_return_status := fnd_api.g_ret_sts_success;
124
125 IF NOT (gme_material_details_dbl.fetch_row (p_material_detail_rec
126 ,x_material_detail_rec) ) THEN
127 RAISE material_detail_fetch_error;
128 END IF;
129
130 l_batch_header.batch_id := x_material_detail_rec.batch_id;
131
132 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header, l_batch_header) ) THEN
133 RAISE batch_header_fetch_error;
134 END IF;
135
136 --Bug#6738476 This is just to assign the attirbute information of the flexfields
137 l_phant_batch := p_batch_header_rec;
138
139 /* Check for phantom specific validations */
140 IF x_material_detail_rec.phantom_type = 0 THEN
141 gme_common_pvt.log_message ('PM_NOTAPHANTOM');
142 RAISE not_a_phantom;
143 ELSIF NVL (x_material_detail_rec.phantom_id, 0) > 0 THEN
144 gme_common_pvt.log_message ('PM_PHANTOM_EXISTS');
145 RAISE phantom_exists;
146 END IF;
147
148 /* No need for scrap factor since plan_qty now includes scrap factor */
149 l_plan_qty := x_material_detail_rec.plan_qty;
150
151 /* Check for the validity rule passed in */
152 IF p_validity_rule_id IS NULL THEN
153 IF l_batch_header.laboratory_ind = 1 THEN
154 l_object_type := 'L';
155 ELSIF l_batch_header.batch_type = gme_common_pvt.g_doc_type_fpo THEN
156 l_object_type := 'F';
157 ELSE
158 l_object_type := 'P';
159 END IF;
160
161 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
162 gme_debug.put_line ( g_pkg_name
163 || '.'
164 || l_api_name
165 || ':'
166 || 'Get VR for phantom ingredient '||x_material_detail_rec.inventory_item_id||' revision '||x_material_detail_rec.revision);
167 END IF;
168
169 gmd_val_data_pub.get_val_data
170 (p_api_version => 1.0
171 ,p_object_type => l_object_type
172 ,p_item_id => x_material_detail_rec.inventory_item_id
173 ,p_product_qty => l_plan_qty
174 ,p_uom => x_material_detail_rec.dtl_um
175 ,p_revision => x_material_detail_rec.revision --nsinghi bug#5436643 Pass Revision Number
176 ,p_start_date => NULL
177 ,p_end_date => l_batch_header.plan_start_date
178 ,p_organization_id => l_batch_header.organization_id
179 ,p_least_cost_validity => p_use_least_cost_validity_rule
180 ,x_return_status => x_return_status
181 ,x_msg_count => l_msg_count
182 ,x_msg_data => l_msg_list
183 ,x_return_code => l_return_code
184 ,x_recipe_validity_out => l_validity_tbl);
185
186 IF x_return_status <> fnd_api.g_ret_sts_success THEN
187 RAISE validity_fetch_error;
188 ELSIF l_validity_tbl.COUNT = 0 THEN
189 RAISE no_validity_found;
190 END IF;
191
192 l_validity_rule_id := l_validity_tbl (1).recipe_validity_rule_id;
193 ELSE
194 l_validity_rule_id := p_validity_rule_id;
195 END IF; /* IF p_validity_rule_id IS NULL */
196 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
197 gme_debug.put_line ( g_pkg_name
198 || '.'
199 || l_api_name
200 || ':'
201 || 'VR for phantom ingredient '||l_validity_rule_id);
202 END IF;
203
204 l_phant_batch.organization_id := l_batch_header.organization_id;
205 l_phant_batch.plan_cmplt_date := l_batch_header.plan_start_date;
206 l_phant_batch.batch_type := l_batch_header.batch_type;
207 l_phant_batch.update_inventory_ind :=
208 l_batch_header.update_inventory_ind;
209 l_phant_batch.laboratory_ind := l_batch_header.laboratory_ind;
210 l_phant_batch.batch_no := p_batch_no;
211 l_phant_batch.recipe_validity_rule_id := l_validity_rule_id;
212
213 IF x_material_detail_rec.release_type IN
214 (gme_common_pvt.g_mtl_autobystep_release
215 ,gme_common_pvt.g_mtl_incremental_release
216 ,gme_common_pvt.g_mtl_manual_release) THEN
217 OPEN cur_step_matl_detail (x_material_detail_rec.material_detail_id);
218
219 FETCH cur_step_matl_detail
220 INTO l_step_rec;
221
222 IF cur_step_matl_detail%FOUND THEN
223 l_phant_batch.plan_cmplt_date := l_step_rec.plan_start_date;
224 END IF;
225
226 CLOSE cur_step_matl_detail;
227 END IF;
228
229 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
230 gme_debug.put_line ( g_pkg_name
231 || '.'
232 || l_api_name
233 || ':'
234 || 'Calling Create_batch');
235 END IF;
236
237 l_in_phant_batch := l_phant_batch;
238 SAVEPOINT create_auto_phantom;
239 gme_create_batch_pvt.create_batch
240 (x_return_status => x_return_status
241 ,p_batch_header_rec => l_in_phant_batch
242 ,x_batch_header_rec => l_phant_batch
243 ,p_batch_size => l_plan_qty
244 ,p_batch_size_uom => x_material_detail_rec.dtl_um
245 ,p_creation_mode => 'PRODUCT'
246 ,p_use_workday_cal => p_use_workday_cal
247 ,p_contiguity_override => p_contiguity_override
248 ,x_exception_material_tbl => x_exception_material_tbl
249 ,p_is_phantom => 'Y');
250
251 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
252 gme_debug.put_line ('create_batch-return_status ' || x_return_status);
253 gme_debug.put_line ( g_pkg_name
254 || '.'
255 || l_api_name
256 || ':'
257 || ' Create_batch returned '
258 || x_return_status);
259 END IF;
260
261 IF x_return_status <> fnd_api.g_ret_sts_success
262 AND x_return_status <> gme_common_pvt.g_inv_short_err THEN
263 ROLLBACK TO SAVEPOINT create_auto_phantom;
264 RAISE batch_creation_failed;
265 END IF;
266
267 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
268 gme_debug.put_line ('Update batch_id '|| l_phant_batch.batch_id||' with parentline_id '||p_material_detail_rec.material_detail_id);
269 END IF;
270
271 -- Bug 14681911 - Refresh the variable data before the next update.
272 IF NOT (gme_batch_header_dbl.fetch_row (l_phant_batch, l_phant_batch)) THEN
273 RAISE batch_header_fetch_error;
274 END IF;
275
276 l_phant_batch.parentline_id := p_material_detail_rec.material_detail_id;
277
278 -- Bug 8490219 - No need to timestamp this record as it was just created.
279 -- This leads to problems if a customized call is made in the interim and timestamp is
280 -- changed. Also, the update_row function stamps it properly so this is redundant.
281 -- l_phant_batch.last_update_date := gme_common_pvt.g_timestamp;
282
283 IF NOT gme_batch_header_dbl.update_row (p_batch_header => l_phant_batch) THEN
284 RAISE batch_upd_error;
285 END IF;
286
287 OPEN cur_get_phantom_prod (l_phant_batch.batch_id
288 ,x_material_detail_rec.inventory_item_id);
289
290 FETCH cur_get_phantom_prod
291 INTO l_prod_mtl_dtl_id;
292
293 CLOSE cur_get_phantom_prod;
294
295 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
296 gme_debug.put_line ('Lets Update line ids now');
297 gme_debug.put_line ('Update phantom prod material_detail_id '|| l_prod_mtl_dtl_id||' with phantom_line_id '||p_material_detail_rec.material_detail_id);
298 END IF;
299
300 UPDATE gme_material_details
301 SET phantom_line_id = p_material_detail_rec.material_detail_id
302 ,release_type = x_material_detail_rec.release_type
303 ,subinventory = x_material_detail_rec.subinventory
304 ,locator_id = x_material_detail_rec.locator_id
305 ,last_update_date = gme_common_pvt.g_timestamp
306 ,last_updated_by = gme_common_pvt.g_user_ident
307 ,last_update_login = gme_common_pvt.g_login_id
308 WHERE material_detail_id = l_prod_mtl_dtl_id;
309
310 x_material_detail_rec.phantom_id := l_phant_batch.batch_id;
311 x_material_detail_rec.phantom_line_id := l_prod_mtl_dtl_id;
312
313 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
314 gme_debug.put_line (' ');
315 gme_debug.put_line ('Update material_detail_id '|| p_material_detail_rec.material_detail_id||' with phantom_line_id '||l_prod_mtl_dtl_id);
316 END IF;
317
318 UPDATE gme_material_details
319 SET phantom_line_id = l_prod_mtl_dtl_id
320 ,phantom_id = l_phant_batch.batch_id
321 ,last_update_date = gme_common_pvt.g_timestamp
322 ,last_updated_by = gme_common_pvt.g_user_ident
323 ,last_update_login = gme_common_pvt.g_login_id
324 WHERE material_detail_id = p_material_detail_rec.material_detail_id;
325
326 x_return_status := fnd_api.g_ret_sts_success;
327
328 IF g_debug <= gme_debug.g_log_procedure THEN
329 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
330 END IF;
331 EXCEPTION
332 WHEN batch_creation_failed OR material_detail_fetch_error OR not_a_phantom OR phantom_exists OR batch_header_fetch_error OR validity_fetch_error OR batch_creation_failed OR batch_upd_error THEN
333 x_return_status := fnd_api.g_ret_sts_error;
334 WHEN no_validity_found THEN
335 x_return_status := 'W';
336 WHEN OTHERS THEN
337 IF g_debug <= gme_debug.g_log_unexpected THEN
338 gme_debug.put_line ( 'When others exception in '
339 || g_pkg_name
340 || '.'
341 || l_api_name
342 || ' Error is '
343 || SQLERRM);
344 END IF;
345
346 x_return_status := fnd_api.g_ret_sts_unexp_error;
347 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
348 END create_phantom;
349
350 /*===========================================================================================
351 Function
352 is_phantom
353 Description
354 This particular function checks wether the batch passed in is a phantom batch.
355 Parameters
356 p_batch_header The batch header row to identify the batch.
357 x_return_status outcome of the API call
358 S - Success
359 E - Error
360 U - Unexpected error
361 =============================================================================================*/
362 FUNCTION is_phantom (
363 p_batch_header IN gme_batch_header%ROWTYPE
364 ,x_return_status OUT NOCOPY VARCHAR2)
365 RETURN BOOLEAN
366 IS
367 l_batch_header gme_batch_header%ROWTYPE;
368 error_fetch_header EXCEPTION;
369 l_api_name CONSTANT VARCHAR2 (30) := 'IS_PHANTOM';
370 BEGIN
371 IF g_debug <= gme_debug.g_log_procedure THEN
372 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
373 || l_api_name);
374 END IF;
375
376 x_return_status := fnd_api.g_ret_sts_success;
377
378 IF NOT gme_batch_header_dbl.fetch_row (p_batch_header => p_batch_header
379 ,x_batch_header => l_batch_header) THEN
380 RAISE error_fetch_header;
381 END IF;
382
383 IF NVL (l_batch_header.parentline_id, 0) > 0 THEN
384 RETURN TRUE;
385 ELSE
386 RETURN FALSE;
387 END IF;
388 EXCEPTION
389 WHEN error_fetch_header THEN
390 x_return_status := fnd_api.g_ret_sts_error;
391 RETURN FALSE;
392 WHEN OTHERS THEN
393 IF g_debug <= gme_debug.g_log_unexpected THEN
394 gme_debug.put_line ( 'When others exception in '
395 || g_pkg_name
396 || '.'
397 || l_api_name
398 || ' Error is '
399 || SQLERRM);
400 END IF;
401
402 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
403 RETURN FALSE;
404 END is_phantom;
405
406 /*===========================================================================================
407 Procedure
408 fetch_step_phantoms
409 Description
410 This particular procedure returns all the phantom IDs of the materials
411 associated with the batch step.
412 Parameters
413 p_batch_id The batch id of the step.
414 p_batchstep_id Batch step ID of the step.
415 x_phantom_ids The out row with all the phantom IDs.
416 x_return_status outcome of the API call
417 S - Success
418 E - Error
419 U - Unexpected error
420
421 History
422 Bug 3045672 - added p_all_release_type_assoc parameter and into cursor
423 =============================================================================================*/
424 PROCEDURE fetch_step_phantoms (
425 p_batch_id IN NUMBER
426 ,p_batchstep_id IN NUMBER
427 ,p_all_release_type_assoc IN NUMBER DEFAULT 0
428 ,x_phantom_ids OUT NOCOPY gme_common_pvt.number_tab
429 ,x_return_status OUT NOCOPY VARCHAR2)
430 IS
431 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_STEP_PHANTOMS';
432
433 CURSOR cur_step_matl_detail_ids (
434 v_batch_id NUMBER
435 ,v_batchstep_id NUMBER)
436 IS
437 SELECT d.phantom_id
438 FROM gme_batch_step_items i, gme_material_details d
439 WHERE d.batch_id = v_batch_id
440 AND i.material_detail_id = d.material_detail_id
441 AND batchstep_id = v_batchstep_id
442 AND ( d.release_type = gme_common_pvt.g_mtl_autobystep_release
443 OR ( d.release_type IN
444 (gme_common_pvt.g_mtl_manual_release
445 ,gme_common_pvt.g_mtl_incremental_release)
446 AND p_all_release_type_assoc = 1) )
447 AND NVL (d.phantom_id, 0) > 0;
448
449 no_keys EXCEPTION;
450 BEGIN
451 IF g_debug <= gme_debug.g_log_procedure THEN
452 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
453 || l_api_name);
454 END IF;
455
456 x_return_status := fnd_api.g_ret_sts_success;
457
458 IF (p_batch_id IS NULL) OR (p_batchstep_id IS NULL) THEN
459 RAISE no_keys;
460 END IF;
461
462 OPEN cur_step_matl_detail_ids (p_batch_id, p_batchstep_id);
463
464 FETCH cur_step_matl_detail_ids
465 BULK COLLECT INTO x_phantom_ids;
466
467 CLOSE cur_step_matl_detail_ids;
468
469 IF g_debug <= gme_debug.g_log_procedure THEN
470 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
471 END IF;
472 EXCEPTION
473 WHEN no_keys THEN
474 x_return_status := fnd_api.g_ret_sts_error;
475 gme_common_pvt.log_message ('GME_NO_KEYS'
476 ,'TABLE_NAME'
477 ,'FETCH_STEP_PHANTOMS');
478 WHEN OTHERS THEN
479 IF g_debug <= gme_debug.g_log_unexpected THEN
480 gme_debug.put_line ( 'When others exception in '
481 || g_pkg_name
482 || '.'
483 || l_api_name
484 || ' Error is '
485 || SQLERRM);
486 END IF;
487
488 x_return_status := fnd_api.g_ret_sts_unexp_error;
489 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
490 END fetch_step_phantoms;
491
492 /*===========================================================================================
493 Procedure
494 fetch_line_phantoms
495 Description
496 This particular procedure returns all the phantom IDs of the materials
497 associated which are non step release type.
498 Parameters
499 p_batch_id The batch id of the step.
500 p_include_step Wether to include phantoms of step release line.
501 x_phantom_ids The out row with all the phantom IDs.
502 x_return_status outcome of the API call
503 S - Success
504 E - Error
505 U - Unexpected error
506 =============================================================================================*/
507 PROCEDURE fetch_line_phantoms (
508 p_batch_id IN NUMBER
509 ,p_include_step IN BOOLEAN DEFAULT TRUE
510 ,x_phantom_ids OUT NOCOPY gme_common_pvt.number_tab
511 ,x_return_status OUT NOCOPY VARCHAR2)
512 IS
513 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_LINE_PHANTOMS';
514
515 CURSOR cur_matl_phant_ids (v_batch_id NUMBER, v_release_type NUMBER)
516 IS
517 SELECT d.phantom_id
518 FROM gme_material_details d
519 WHERE d.batch_id = v_batch_id
520 AND d.release_type <= v_release_type
521 AND NVL (d.phantom_id, 0) > 0;
522
523 no_keys EXCEPTION;
524 BEGIN
525 IF g_debug <= gme_debug.g_log_procedure THEN
526 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
527 || l_api_name);
528 END IF;
529
530 x_return_status := fnd_api.g_ret_sts_success;
531
532 IF (p_batch_id IS NULL) THEN
533 RAISE no_keys;
534 END IF;
535
536 IF p_include_step THEN
537 OPEN cur_matl_phant_ids (p_batch_id
538 ,gme_common_pvt.g_mtl_autobystep_release);
539 ELSE
540 OPEN cur_matl_phant_ids (p_batch_id
541 ,gme_common_pvt.g_mtl_incremental_release);
542 END IF;
543
544 FETCH cur_matl_phant_ids
545 BULK COLLECT INTO x_phantom_ids;
546
547 CLOSE cur_matl_phant_ids;
548
549 IF g_debug <= gme_debug.g_log_procedure THEN
550 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
551 END IF;
552 EXCEPTION
553 WHEN no_keys THEN
554 x_return_status := fnd_api.g_ret_sts_error;
555 gme_common_pvt.log_message ('GME_NO_KEYS'
556 ,'TABLE_NAME'
557 ,'FETCH_LINE_PHANTOMS');
558 WHEN OTHERS THEN
559 IF g_debug <= gme_debug.g_log_unexpected THEN
560 gme_debug.put_line ( 'When others exception in '
561 || g_pkg_name
562 || '.'
563 || l_api_name
564 || ' Error is '
565 || SQLERRM);
566 END IF;
567
568 x_return_status := fnd_api.g_ret_sts_unexp_error;
569 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
570 END fetch_line_phantoms;
571 END gme_phantom_pvt;
[Home] [Help]