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