[Home] [Help]
PACKAGE BODY: APPS.GME_COMMON_PVT
Source
1 PACKAGE BODY gme_common_pvt AS
2 /* $Header: GMEVCMNB.pls 120.61.12020000.4 2012/11/28 15:35:54 adeshmuk ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_COMMON_PVT';
5
6 /************************************************************************************************
7 Oracle Process Manufacturing Process Execution APIs
8
9 File Name: GMEVCMNB.pls
10 Contents: GME common procedures.
11 HISTORY
12
13 SivakumarG Bug#4585491 16-FEB-06
14 Used return status B, M and R instead of W3,W2 and W1 to represent Both Reservations and
15 MO allocations are deleted,Only Reservations deleted,Only MO allocations deleted respectively
16 Pawan Kumar -- bug 5138929 2nd may 2006
17 Added procedure check_close_period
18 Susruth D. Bug#5311713 Rounded the wip_plan_qty and plan_qty to 5 decimal places.
19 Namit S -- bug 5176319 20 Jun 2006
20 Added procedure reset_txn_hdr_tbl
21 Sunitha ch. Bug#5336007 checked the parameter p_validate_plan_dates_ind.If it is 1 then
22 validate planned start date only.If it is 0 then validate both
23 SivakumarG Bug#Bug#5394232 26-Jun-2006
24 Added new procedure fetch_trans_date that can give us the default transaction date by
25 considering release types batch actions and all
26 Sunitha ch. Bug 5404329 Changed the condition fron AND to OR
27 Sunitha ch. bug 5581523 removed the default value of the l_qty variable
28 SivakumarG Bug#5618961 07-Oct-2006
29 Modified the get_process_loss procedure to consider the qty correctly for getting correct
30 theoretical process loss from routing class table
31 Swapna K Bug#7157383 16-JUL-08
32 Modified validate_material_detail procedure to comment the validation for
33 the phantom batch
34 Swapna K Bug#7346653 16-JUL-08
35 Commented the procedure reservation_fully_specified in the get_open_qty
36 procedure.
37 Swapna K Bug#7385805 13-OCT-2008
38 Commented the code to divide the leadtime by 24 as the leadtime from Item
39 master is already defined in days.
40
41 G. Muratore 01-DEC-2008 Bug 7562848
42 Added parameter p_check_phantom to allow calling routine to bypass phantom check.
43 Many api's call validate_batch, but not all the api's are invalid for phantom batches.
44 This can be used in the future by other api's when it is determined that they are valid
45 for phantoms. Procedure: Validate_batch
46
47 Aditi Sharma 21-May-2009 Bug 7496141
48 Added two condition in the query(i.e TRANSACTION_SOURCE_ID NOT IN (l_mtl_dtl_rec.batch_id) AND
49 TRX_SOURCE_LINE_ID NOT IN (l_mtl_dtl_rec.material_detail_id)) which fetches the total qty
50 allocated to find out org level shortages.
51
52 A.Mishra 01-JUL-2009 Bug -- Bug 8582412
53 Moved cursor outside of if - we need unusable for both calculations of ATR and ATT.
54 Basically we are now disregarding qty's for lots that have a status which is not useable for production.
55 Prior to fix it was doing it only for ATT but now it is doing for ATR also.
56
57 G. Muratore 11-MAR-2010 Bug 9442109
58 Pass in material_requirement_date as p_lot_expiration_date parameter when making the call to query
59 quantities. The INV function will use this value to exclude lots which aill be expired by this date.
60 Procedure: get_batch_shortages
61
62 G. Muratore 13-MAY-2010 Bug 9666304
63 Insert att variable value into exception table using NVL.
64 Procedure: insert_exceptions
65
66 Kishore 22-Jul-2010 Bug No.9924437
67 Added code in get_open_qty when p_called_by is 'Z'. As WMS is not considering allocations
68 while doing reservations, added code to exclude allocations from planned qty before sending to WMS.
69
70 G. Muratore 03-AUG-2010 Bug 9938452
71 Renamed procedure set_move_to_temp_off to set_move_to_temp_toggle.
72 Also, added parameter to allow toggling of the global variable g_move_to_temp.
73 PROCEDURE: set_move_to_temp_toggle
74
75 G. Muratore 22-Jun-2011 Bug 12613813
76 Pass in true for new parameter when calling get_reserved_qty for picking only.
77 This will direct get_open_qty to consider locator as well when summing the reservations.
78 PROCEDURE: get_open_qty
79
80 G. Muratore 14-NOV-2011 Bug 13356510
81 Commented out validation code that was incorrectly checking for phantom batch.
82 PROCEDURE: Validate_batch_step
83
84 G. Muratore 20-MAR-2012 Bug 13811289
85 Make sure validity rule is valid for the current organization.
86 PROCEDURE: Validate_validity_rule
87
88 G. Muratore 28-MAR-2012 Bug 13611486
89 Backout calculation introduced in 8582412. There is missing functionality when calling
90 INV to get att/atr. It is not considering the transaction type in tandem with material status.
91 This fix will be included in patch 13795581 and bug 13611486 will be transferred to INV to
92 see if they can correct the functionality.
93 PROCEDURE: get_batch_shortages
94
95 G. Muratore 03-APR-2012 Bug 13881792
96 During IB, return the user entered transaction date when exceptions appear.
97 Procedure: fetch_trans_date and reset_txn_header_id.
98
99 G. Muratore 16-APR-2012 Bug 13946067
100 Introduce new table and joins to make use of existing index.
101 Procedure: resource_qty_below_capacity.
102
103 G. Muratore 19-APR-2012 Bug 13721430
104 Calculate leadtime properly by considering lot size.
105 Procedure: calc_date_from_prod_rule.
106
107 G. Muratore 01-OCT-2012 Bug 14488987
108 Backout calculation introduced in 7462542 for release 12.1 and above.
109 INV code already considers Move orders in the newer versions of the code.
110 PROCEDURE: get_batch_shortages
111
112 A. Mundhe 15-NOV-2012 Bug 15847423
113 Modified function setup to return true only if the org_id passed is same as the g_organization_id.
114 ***********************************************************************************************/
115
116 FUNCTION get_txn_header_id
117 RETURN NUMBER
118 IS
119 BEGIN
120 RETURN gme_common_pvt.g_transaction_header_id;
121 END get_txn_header_id;
122
123 PROCEDURE set_txn_header_id(p_txn_header_id NUMBER) IS
124 BEGIN
125 gme_common_pvt.g_transaction_header_id := p_txn_header_id;
126 END set_txn_header_id;
127
128 PROCEDURE reset_txn_header_id IS
129 BEGIN
130 gme_common_pvt.g_transaction_header_id := NULL;
131
132 -- Bug 13881792 - Reset IB timestamp set global.
133 gme_common_pvt.g_ib_timestamp_set := 0;
134
135 END reset_txn_header_id;
136
137 FUNCTION get_timestamp
138 RETURN DATE
139 IS
140 BEGIN
141 RETURN gme_common_pvt.g_timestamp;
142 END get_timestamp;
143
144 -- Bug 9938452 - renamed procedure set_move_to_temp_off to set_move_to_temp_toggle.
145 -- Also, added parameter to allow toggling of the global variable.
146 PROCEDURE set_move_to_temp_toggle(p_true IN NUMBER DEFAULT 0)
147 IS
148 BEGIN
149 IF p_true = 1 THEN
150 gme_common_pvt.g_move_to_temp := fnd_api.g_true;
151 ELSE
152 gme_common_pvt.g_move_to_temp := fnd_api.g_false;
153 END IF;
154 END set_move_to_temp_toggle;
155
156 -- nsinghi bug#5176319. Added this proc.
157 /* This procedure will take care of deleting the table g_mat_txn_hdr_tbl. This table should be
158 truncated before and after unrelease_step and unrelease_batch. The after part is taken care of
159 gme_transactions_pvt.
160 */
161 PROCEDURE reset_txn_hdr_tbl IS
162 BEGIN
163 IF gme_common_pvt.g_mat_txn_hdr_tbl.COUNT > 0 THEN
164 gme_common_pvt.g_mat_txn_hdr_tbl.DELETE;
165 END IF;
166 END reset_txn_hdr_tbl;
167
168 PROCEDURE log_message (
169 p_message_code IN VARCHAR2
170 ,p_token1_name IN VARCHAR2 := NULL
171 ,p_token1_value IN VARCHAR2 := NULL
172 ,p_token2_name IN VARCHAR2 := NULL
173 ,p_token2_value IN VARCHAR2 := NULL
174 ,p_token3_name IN VARCHAR2 := NULL
175 ,p_token3_value IN VARCHAR2 := NULL
176 ,p_token4_name IN VARCHAR2 := NULL
177 ,p_token4_value IN VARCHAR2 := NULL
178 ,p_token5_name IN VARCHAR2 := NULL
179 ,p_token5_value IN VARCHAR2 := NULL
180 --FPBug#4351032 Added new token and value
181 ,p_token6_name IN VARCHAR2 := NULL
182 ,p_token6_value IN VARCHAR2 := NULL
183 ,p_product_code IN VARCHAR2 := 'GME')
184 IS
185 l_api_name CONSTANT VARCHAR2 (30) := 'log_message';
186 BEGIN
187 IF g_debug <= gme_debug.g_log_procedure THEN
188 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
189 || l_api_name);
190 END IF;
191
192 fnd_message.set_name (p_product_code, p_message_code);
193
194 IF p_token1_name IS NOT NULL THEN
195 fnd_message.set_token (p_token1_name, p_token1_value);
196 END IF;
197
198 IF p_token2_name IS NOT NULL THEN
199 fnd_message.set_token (p_token2_name, p_token2_value);
200 END IF;
201
202 IF p_token3_name IS NOT NULL THEN
203 fnd_message.set_token (p_token3_name, p_token3_value);
204 END IF;
205
206 IF p_token4_name IS NOT NULL THEN
207 fnd_message.set_token (p_token4_name, p_token4_value);
208 END IF;
209
210 IF p_token5_name IS NOT NULL THEN
211 fnd_message.set_token (p_token5_name, p_token5_value);
212 END IF;
213 --FPBug#4351032
214 IF p_token6_name IS NOT NULL THEN
215 fnd_message.set_token (p_token6_name, p_token6_value);
216 END IF;
217
218 fnd_msg_pub.ADD;
219
220 IF g_debug <= gme_debug.g_log_procedure THEN
221 --FPBug#4351032 modified to include new token and value
222 gme_debug.put_line ( 'FROM LOG_MESSAGE '
223 || p_message_code
224 || '/'
225 || p_token1_name
226 || '/'
227 || p_token1_value
228 || '/'
229 || p_token2_name
230 || '/'
231 || p_token2_value
232 || '/'
233 || p_token3_name
234 || '/'
235 || p_token3_value
236 || '/'
237 || p_token4_name
238 || '/'
239 || p_token4_value
240 || '/'
241 || p_token5_name
242 || '/'
243 || p_token5_value
244 || '/'
245 || p_token6_name
246 || '/'
247 || p_token6_value);
248 END IF;
249
250 gme_common_pvt.g_error_count := gme_common_pvt.g_error_count + 1;
251
252 IF g_debug <= gme_debug.g_log_procedure THEN
253 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
254 END IF;
255 EXCEPTION
256 WHEN OTHERS THEN
257 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
258
259 IF g_debug <= gme_debug.g_log_unexpected THEN
260 gme_debug.put_line ( 'When others exception in '
261 || g_pkg_name
262 || '.'
263 || l_api_name
264 || ' Error is '
265 || SQLERRM);
266 END IF;
267 END log_message;
268
269 FUNCTION setup (p_org_id IN NUMBER := NULL, p_org_code IN VARCHAR2 := NULL)
270 RETURN BOOLEAN
271 IS
272 CURSOR cur_get_gme_params (v_org_id NUMBER)
273 IS
274 SELECT *
275 FROM gme_parameters
276 WHERE organization_id = v_org_id;
277
278 CURSOR cur_get_inv_params (v_org_id NUMBER, v_org_code VARCHAR2)
279 IS
280 SELECT organization_code, calendar_code, negative_inv_receipt_code
281 ,stock_locator_control_code, organization_id
282 FROM mtl_parameters
283 WHERE (organization_id = v_org_id)
284 OR (v_org_id IS NULL AND organization_code = v_org_code);
285
286 CURSOR cur_get_user (v_user_id NUMBER)
287 IS
288 SELECT 1
289 FROM DUAL
290 WHERE EXISTS (SELECT 1
291 FROM fnd_user
292 WHERE user_id = v_user_id);
293
294 l_gmd_params_rec gmd_parameters_dtl_pkg.parameter_rec_type;
295 l_cur_get_org_params cur_get_gme_params%ROWTYPE;
296 l_dummy NUMBER;
297 l_return_status VARCHAR2 (1);
298 l_api_name CONSTANT VARCHAR2 (30) := 'setup';
299 invalid_user EXCEPTION;
300 gme_params_not_defined EXCEPTION;
301 gmd_params_not_defined EXCEPTION;
302 inv_params_not_found EXCEPTION;
303 missing_profile_option EXCEPTION;
304 BEGIN
305 IF g_debug <= gme_debug.g_log_procedure THEN
306 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
307 || l_api_name);
308 END IF;
309
310 -- Bug 15847423
311 -- Return true only if the org_id passed is same as the g_organization_id.
312 IF (gme_common_pvt.g_setup_done) THEN
313 IF NVL(p_org_id,-999) = gme_common_pvt.g_organization_id THEN
314 RETURN TRUE;
315 END IF;
316 END IF;
317
318 gme_common_pvt.g_login_id := TO_NUMBER (fnd_profile.VALUE ('LOGIN_ID') );
319 gme_common_pvt.g_user_ident :=
320 TO_NUMBER (fnd_profile.VALUE ('USER_ID') );
321
322 OPEN cur_get_user (gme_common_pvt.g_user_ident);
323
324 FETCH cur_get_user
325 INTO l_dummy;
326
327 IF (cur_get_user%NOTFOUND) THEN
328 CLOSE cur_get_user;
329 RAISE invalid_user;
330 END IF;
331
332 CLOSE cur_get_user;
333
334 IF NVL (gme_common_pvt.g_user_ident, -1) = -1 THEN
335 log_message ('GME_API_INVALID_USER_NAME'
336 ,'USER_NAME'
337 ,gme_common_pvt.g_user_name);
338 RAISE missing_profile_option;
339 END IF;
340
341 OPEN cur_get_inv_params (p_org_id, p_org_code);
342
343 FETCH cur_get_inv_params
344 INTO gme_common_pvt.g_organization_code
345 ,gme_common_pvt.g_calendar_code, gme_common_pvt.g_allow_neg_inv
346 ,gme_common_pvt.g_org_locator_control
347 ,gme_common_pvt.g_organization_id;
348
349 IF (cur_get_inv_params%NOTFOUND) THEN
350 CLOSE cur_get_inv_params;
351
352 RAISE inv_params_not_found;
353 END IF;
354
355 CLOSE cur_get_inv_params;
356
357 gmd_api_grp.fetch_parm_values
358 (p_orgn_id => gme_common_pvt.g_organization_id
359 ,x_out_rec => l_gmd_params_rec
360 ,x_return_status => l_return_status);
361
362 /*Assign the gmd params values to package variables */
363 IF l_return_status = fnd_api.g_ret_sts_success THEN
364 gme_common_pvt.g_plant_ind := l_gmd_params_rec.plant_ind;
365 gme_common_pvt.g_lab_ind := l_gmd_params_rec.lab_ind;
366 gme_common_pvt.g_yield_type := l_gmd_params_rec.fm_yield_type;
367 gme_common_pvt.g_release_type := l_gmd_params_rec.steprelease_type;
368 gme_common_pvt.g_byprod_active :=
369 l_gmd_params_rec.gmd_byproduct_active;
370 gme_common_pvt.g_mass_um_type := l_gmd_params_rec.gmd_mass_um_type;
371 gme_common_pvt.g_volume_um_type := l_gmd_params_rec.gmd_volume_um_type;
372 ELSE
373 RAISE gmd_params_not_defined;
374 END IF;
375
376 OPEN cur_get_gme_params (gme_common_pvt.g_organization_id);
377
378 FETCH cur_get_gme_params
379 INTO l_cur_get_org_params;
380
381 IF cur_get_gme_params%NOTFOUND THEN
382 CLOSE cur_get_gme_params;
383
384 RAISE gme_params_not_defined;
385 END IF;
386
387 CLOSE cur_get_gme_params;
388
389 gme_common_pvt.g_auto_consume_supply_sub_only :=
390 l_cur_get_org_params.auto_consume_supply_sub_only;
391 gme_common_pvt.g_subinv_loc_ind :=
392 l_cur_get_org_params.subinv_loc_ind; --Bug#5663458
393 gme_common_pvt.g_supply_subinventory :=
394 l_cur_get_org_params.supply_subinventory;
395 gme_common_pvt.g_supply_locator_id :=
396 l_cur_get_org_params.supply_locator_id;
397 gme_common_pvt.g_yield_subinventory :=
398 l_cur_get_org_params.yield_subinventory;
399 gme_common_pvt.g_yield_locator_id :=
400 l_cur_get_org_params.yield_locator_id;
401 gme_common_pvt.g_delete_material_ind :=
402 l_cur_get_org_params.delete_material_ind;
403 gme_common_pvt.g_validate_plan_dates_ind :=
404 l_cur_get_org_params.validate_plan_dates_ind;
405 --Bug#5111078
406 gme_common_pvt.g_ib_factor_ind :=
407 l_cur_get_org_params.ib_factor_ind;
408 --nsinghi bug#5674398 Added the global variable for ingr subs date type
409 -- Default it to Material Requirement Date as in R12, default ingr sub happens using mat req date.
410 gme_common_pvt.g_ingr_sub_date :=
411 NVL(l_cur_get_org_params.ingr_sub_date, 2);
412
413 gme_common_pvt.g_display_unconsumed_material :=
414 l_cur_get_org_params.display_unconsumed_material;
415 gme_common_pvt.g_step_controls_batch_sts_ind :=
416 l_cur_get_org_params.step_controls_batch_sts_ind;
417 gme_common_pvt.g_backflush_rsrc_usg_ind :=
418 l_cur_get_org_params.backflush_rsrc_usg_ind;
419 gme_common_pvt.g_def_actual_rsrc_usg_ind :=
420 l_cur_get_org_params.def_actual_rsrc_usg_ind;
421 gme_common_pvt.g_calc_interim_rsrc_usg_ind :=
422 l_cur_get_org_params.calc_interim_rsrc_usg_ind;
423 gme_common_pvt.g_allow_qty_below_min_ind :=
424 l_cur_get_org_params.allow_qty_below_min_ind;
425 gme_common_pvt.g_display_non_work_days_ind :=
426 l_cur_get_org_params.display_non_work_days_ind;
427 gme_common_pvt.g_check_shortages_ind :=
428 l_cur_get_org_params.check_shortages_ind;
429 gme_common_pvt.g_copy_formula_text_ind :=
430 l_cur_get_org_params.copy_formula_text_ind;
431 gme_common_pvt.g_copy_routing_text_ind :=
432 l_cur_get_org_params.copy_routing_text_ind;
433 gme_common_pvt.g_create_high_level_resv_ind :=
434 l_cur_get_org_params.create_high_level_resv_ind;
435 gme_common_pvt.g_create_move_orders_ind :=
436 l_cur_get_org_params.create_move_orders_ind;
437 gme_common_pvt.g_reservation_timefence :=
438 l_cur_get_org_params.reservation_timefence;
439 gme_common_pvt.g_move_order_timefence :=
440 l_cur_get_org_params.move_order_timefence;
441 gme_common_pvt.g_batch_doc_numbering :=
442 l_cur_get_org_params.batch_doc_numbering;
443 gme_common_pvt.g_batch_no_last_assigned :=
444 l_cur_get_org_params.batch_no_last_assigned;
445 gme_common_pvt.g_fpo_doc_numbering :=
446 l_cur_get_org_params.fpo_doc_numbering;
447 gme_common_pvt.g_fpo_no_last_assigned :=
448 l_cur_get_org_params.fpo_no_last_assigned;
449 gme_common_pvt.g_rule_based_resv_horizon :=
450 l_cur_get_org_params.rule_based_resv_horizon;
451 gme_common_pvt.g_hour_uom_code :=
452 fnd_profile.VALUE ('BOM:HOUR_UOM_CODE');
453
454 IF g_debug <= gme_debug.g_log_procedure THEN
455 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
456 END IF;
457
458 RETURN TRUE;
459 EXCEPTION
460 WHEN invalid_user THEN
461 fnd_message.set_name ('INV', 'INV_INT_USRCODE');
462 fnd_msg_pub.ADD;
463 RETURN FALSE;
464 WHEN missing_profile_option THEN
465 RETURN FALSE;
466 WHEN inv_params_not_found THEN
467 fnd_message.set_name ('INV', 'INV-NO ORG INFORMATION');
468 fnd_msg_pub.ADD;
469 RETURN FALSE;
470 WHEN gmd_params_not_defined THEN
471 RETURN FALSE;
472 WHEN gme_params_not_defined THEN
473 gme_common_pvt.log_message ('GME_DEFINE_GME_PARAMETERS');
474 RETURN FALSE;
475 WHEN OTHERS THEN
476 IF g_debug <= gme_debug.g_log_unexpected THEN
477 gme_debug.put_line ( 'When others exception in '
478 || g_pkg_name
479 || '.'
480 || l_api_name
481 || ' Error is '
482 || SQLERRM);
483 END IF;
484
485 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
486 RETURN FALSE;
487 END setup;
488
489 PROCEDURE set_timestamp
490 IS
491 l_api_name CONSTANT VARCHAR2 (30) := 'set_timestamp';
492 BEGIN
493 gme_common_pvt.g_timestamp := SYSDATE;
494 EXCEPTION
495 WHEN OTHERS THEN
496 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
497
498 IF g_debug <= gme_debug.g_log_unexpected THEN
499 gme_debug.put_line ( 'When others exception in '
500 || g_pkg_name
501 || '.'
502 || l_api_name
503 || ' Error is '
504 || SQLERRM);
505 END IF;
506 END set_timestamp;
507
508 PROCEDURE set_who
509 IS
510 l_api_name CONSTANT VARCHAR2 (30) := 'set_who';
511 BEGIN
512 gme_common_pvt.g_user_ident := fnd_profile.VALUE ('USER_ID');
513 gme_common_pvt.g_login_id := fnd_profile.VALUE ('LOGIN_ID');
514 gme_common_pvt.set_timestamp;
515 EXCEPTION
516 WHEN OTHERS THEN
517 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
518
519 IF g_debug <= gme_debug.g_log_unexpected THEN
520 gme_debug.put_line ( 'When others exception in '
521 || g_pkg_name
522 || '.'
523 || l_api_name
524 || ' Error is '
525 || SQLERRM);
526 END IF;
527 END set_who;
528
529 PROCEDURE count_and_get (
530 p_encoded IN VARCHAR2 := 'T'
531 ,x_count OUT NOCOPY NUMBER
532 ,x_data OUT NOCOPY VARCHAR2)
533 IS
534 l_msg_count NUMBER;
535 l_api_name CONSTANT VARCHAR2 (30) := 'count_and_get';
536 BEGIN
537 l_msg_count := fnd_msg_pub.count_msg;
538 x_data :=
539 fnd_msg_pub.get (p_msg_index => fnd_msg_pub.g_last
540 ,p_encoded => p_encoded);
541 x_count := l_msg_count;
542 EXCEPTION
543 WHEN OTHERS THEN
544 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
545
546 IF g_debug <= gme_debug.g_log_unexpected THEN
547 gme_debug.put_line ( 'When others exception in '
548 || g_pkg_name
549 || '.'
550 || l_api_name
551 || ' Error is '
552 || SQLERRM);
553 END IF;
554 END count_and_get;
555
556 PROCEDURE get_who (
557 x_user_ident OUT NOCOPY NUMBER
558 ,x_login_id OUT NOCOPY NUMBER
559 ,x_timestamp OUT NOCOPY DATE
560 ,x_return_status OUT NOCOPY VARCHAR2)
561 IS
562 l_message_count NUMBER;
563 l_message_list VARCHAR2 (2000);
564 l_api_name CONSTANT VARCHAR2 (30) := 'GET_WHO';
565 BEGIN
566 /* Set the return status to success initially */
567 x_return_status := fnd_api.g_ret_sts_success;
568
569 IF gme_common_pvt.g_user_ident IS NULL THEN
570 set_who;
571 END IF;
572
573 x_user_ident := gme_common_pvt.g_user_ident;
574 x_login_id := gme_common_pvt.g_login_id;
575 x_timestamp := gme_common_pvt.g_timestamp;
576 EXCEPTION
577 WHEN OTHERS THEN
578 x_return_status := fnd_api.g_ret_sts_unexp_error;
579 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
580 fnd_msg_pub.count_and_get (p_count => l_message_count
581 ,p_data => l_message_list);
582
583 IF g_debug <= gme_debug.g_log_unexpected THEN
584 gme_debug.put_line ( 'When others exception in '
585 || g_pkg_name
586 || '.'
587 || l_api_name
588 || ' Error is '
589 || SQLERRM);
590 END IF;
591 END get_who;
592
593 PROCEDURE get_supply_defaults (
594 p_organization_id IN NUMBER
595 ,p_inventory_item_id IN NUMBER
596 ,x_subinventory OUT NOCOPY VARCHAR2
597 ,x_locator_id OUT NOCOPY NUMBER
598 ,x_return_status OUT NOCOPY VARCHAR2)
599 IS
600 l_api_name CONSTANT VARCHAR2 (30) := 'get_supply_defaults';
601 l_eff_locator_control NUMBER;
602 l_restrict_subinv NUMBER;
603 l_item_loc_control NUMBER;
604 l_restrict_locators NUMBER;
605 l_sub_locator_type NUMBER;
606 /* Bug 5441643 Added NVL condition for location control code*/
607 CURSOR cur_supply_defaults (v_org_id NUMBER, v_inventory_item_id NUMBER)
608 IS
609 SELECT NVL (i.process_supply_subinventory
610 ,g.supply_subinventory)
611 ,NVL (i.process_supply_locator_id
612 ,g.supply_locator_id)
613 ,i.restrict_subinventories_code, NVL(i.location_control_code,1)
614 ,i.restrict_locators_code
615 FROM mtl_system_items_b i, gme_parameters g
616 WHERE i.organization_id = v_org_id
617 AND i.inventory_item_id = v_inventory_item_id
618 AND g.organization_id = v_org_id;
619
620 CURSOR cur_sub_control (v_org_id NUMBER, v_subinventory VARCHAR2)
621 IS
622 SELECT locator_type
623 FROM mtl_secondary_inventories
624 WHERE organization_id = v_org_id
625 AND secondary_inventory_name = v_subinventory;
626 BEGIN
627 IF g_debug <= gme_debug.g_log_procedure THEN
628 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
629 || l_api_name);
630 END IF;
631
632 x_return_status := fnd_api.g_ret_sts_success;
633
634 OPEN cur_supply_defaults (p_organization_id, p_inventory_item_id);
635
636 FETCH cur_supply_defaults
637 INTO x_subinventory, x_locator_id, l_restrict_subinv
638 ,l_item_loc_control, l_restrict_locators;
639
640 CLOSE cur_supply_defaults;
641
642 IF (x_subinventory IS NOT NULL) THEN
643 IF NOT (gme_common_pvt.check_subinventory
644 (p_organization_id => p_organization_id
645 ,p_subinventory => x_subinventory
646 ,p_inventory_item_id => p_inventory_item_id
647 ,p_restrict_subinv => l_restrict_subinv) ) THEN
648 x_subinventory := NULL;
649 x_locator_id := NULL;
650 RETURN;
651 END IF;
652 END IF;
653
654 OPEN cur_sub_control (p_organization_id, x_subinventory);
655
656 FETCH cur_sub_control
657 INTO l_sub_locator_type;
658
659 CLOSE cur_sub_control;
660
661 l_eff_locator_control :=
662 gme_common_pvt.eff_locator_control
663 (p_organization_id => p_organization_id
664 ,p_org_control => gme_common_pvt.g_org_locator_control
665 ,p_subinventory => x_subinventory
666 ,p_sub_control => l_sub_locator_type
667 ,p_item_control => l_item_loc_control
668 ,p_item_loc_restrict => l_restrict_locators
669 ,p_action => gme_common_pvt.g_ing_issue_txn_action);
670
671 IF (l_eff_locator_control = 1) THEN
672 x_locator_id := NULL;
673 RETURN;
674 END IF;
675
676 IF (x_locator_id IS NOT NULL) THEN
677 OPEN cur_sub_control (p_organization_id, x_subinventory);
678
679 FETCH cur_sub_control
680 INTO l_sub_locator_type;
681
682 CLOSE cur_sub_control;
683
684 IF NOT (gme_common_pvt.check_locator
685 (p_organization_id => p_organization_id
686 ,p_locator_id => x_locator_id
687 ,p_subinventory => x_subinventory
688 ,p_inventory_item_id => p_inventory_item_id
689 ,p_org_control => gme_common_pvt.g_org_locator_control
690 ,p_sub_control => l_sub_locator_type
691 ,p_item_control => l_item_loc_control
692 ,p_item_loc_restrict => l_restrict_locators
693 ,p_org_neg_allowed => gme_common_pvt.g_allow_neg_inv
694 ,p_txn_action_id => gme_common_pvt.g_ing_issue_txn_action) ) THEN
695 x_locator_id := NULL;
696 END IF;
697 END IF;
698
699 IF g_debug <= gme_debug.g_log_procedure THEN
700 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
701 END IF;
702 EXCEPTION
703 WHEN OTHERS THEN
704 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
705
706 IF g_debug <= gme_debug.g_log_unexpected THEN
707 gme_debug.put_line ( 'When others exception in '
708 || g_pkg_name
709 || '.'
710 || l_api_name
711 || ' Error is '
712 || SQLERRM);
713 END IF;
714
715 x_return_status := fnd_api.g_ret_sts_unexp_error;
716 END get_supply_defaults;
717
718 PROCEDURE get_yield_defaults (
719 p_organization_id IN NUMBER
720 ,p_inventory_item_id IN NUMBER
721 ,p_line_type IN NUMBER
722 ,x_subinventory OUT NOCOPY VARCHAR2
723 ,x_locator_id OUT NOCOPY NUMBER
724 ,x_return_status OUT NOCOPY VARCHAR2)
725 IS
726 l_api_name CONSTANT VARCHAR2 (30) := 'get_yield_defaults';
727 l_txn_action NUMBER;
728 l_eff_locator_control NUMBER;
729 l_restrict_subinv NUMBER;
730 l_item_loc_control NUMBER;
731 l_restrict_locators NUMBER;
732 l_sub_locator_type NUMBER;
733 /* Bug 5441643 Added NVL condition for location control code*/
734 CURSOR cur_yield_defaults (v_org_id NUMBER, v_inventory_item_id NUMBER)
735 IS
736 SELECT NVL (i.process_yield_subinventory
737 ,g.yield_subinventory)
738 ,NVL (i.process_yield_locator_id
739 ,g.yield_locator_id)
740 ,i.restrict_subinventories_code, NVL(i.location_control_code,1)
741 ,i.restrict_locators_code
742 FROM mtl_system_items_b i, gme_parameters g
743 WHERE i.organization_id = v_org_id
744 AND i.inventory_item_id = v_inventory_item_id
745 AND g.organization_id = v_org_id;
746
747 CURSOR cur_sub_control (v_org_id NUMBER, v_subinventory VARCHAR2)
748 IS
749 SELECT locator_type
750 FROM mtl_secondary_inventories
751 WHERE organization_id = v_org_id
752 AND secondary_inventory_name = v_subinventory;
753 BEGIN
754 IF g_debug <= gme_debug.g_log_procedure THEN
755 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
756 || l_api_name);
757 END IF;
758
759 x_return_status := fnd_api.g_ret_sts_success;
760
761 OPEN cur_yield_defaults (p_organization_id, p_inventory_item_id);
762
763 FETCH cur_yield_defaults
764 INTO x_subinventory, x_locator_id, l_restrict_subinv
765 ,l_item_loc_control, l_restrict_locators;
766
767 CLOSE cur_yield_defaults;
768
769 IF (x_subinventory IS NOT NULL) THEN
770 IF NOT (gme_common_pvt.check_subinventory
771 (p_organization_id => p_organization_id
772 ,p_subinventory => x_subinventory
773 ,p_inventory_item_id => p_inventory_item_id
774 ,p_restrict_subinv => l_restrict_subinv) ) THEN
775 x_subinventory := NULL;
776 x_locator_id := NULL;
777 RETURN;
778 END IF;
779 END IF;
780
781 IF (p_line_type = g_line_type_prod) THEN
782 l_txn_action := gme_common_pvt.g_prod_comp_txn_action;
783 ELSIF (p_line_type = g_line_type_prod) THEN
784 l_txn_action := gme_common_pvt.g_byprod_comp_txn_action;
785 END IF;
786
787 OPEN cur_sub_control (p_organization_id, x_subinventory);
788
789 FETCH cur_sub_control
790 INTO l_sub_locator_type;
791
792 CLOSE cur_sub_control;
793
794 l_eff_locator_control :=
795 gme_common_pvt.eff_locator_control
796 (p_organization_id => p_organization_id
797 ,p_org_control => gme_common_pvt.g_org_locator_control
798 ,p_subinventory => x_subinventory
799 ,p_sub_control => l_sub_locator_type
800 ,p_item_control => l_item_loc_control
801 ,p_item_loc_restrict => l_restrict_locators
802 ,p_action => l_txn_action);
803
804 IF (l_eff_locator_control = 1) THEN
805 x_locator_id := NULL;
806 RETURN;
807 END IF;
808
809 IF (x_locator_id IS NOT NULL) THEN
810 IF NOT (gme_common_pvt.check_locator
811 (p_organization_id => p_organization_id
812 ,p_locator_id => x_locator_id
813 ,p_subinventory => x_subinventory
814 ,p_inventory_item_id => p_inventory_item_id
815 ,p_org_control => gme_common_pvt.g_org_locator_control
816 ,p_sub_control => l_sub_locator_type
817 ,p_item_control => l_item_loc_control
818 ,p_item_loc_restrict => l_restrict_locators
819 ,p_org_neg_allowed => gme_common_pvt.g_allow_neg_inv
820 ,p_txn_action_id => gme_common_pvt.g_ing_issue_txn_action) ) THEN
821 x_locator_id := NULL;
822 END IF;
823 END IF;
824
825 IF g_debug <= gme_debug.g_log_procedure THEN
826 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
827 END IF;
828 EXCEPTION
829 WHEN OTHERS THEN
830 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
831
832 IF g_debug <= gme_debug.g_log_unexpected THEN
833 gme_debug.put_line ( 'When others exception in '
834 || g_pkg_name
835 || '.'
836 || l_api_name
837 || ' Error is '
838 || SQLERRM);
839 END IF;
840
841 x_return_status := fnd_api.g_ret_sts_unexp_error;
842 END get_yield_defaults;
843
844 PROCEDURE construct_material_detail (
845 p_formula_detail_rec IN fm_matl_dtl%ROWTYPE
846 ,p_item_master_rec IN mtl_system_items_kfv%ROWTYPE
847 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
848 ,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE
849 ,x_return_status OUT NOCOPY VARCHAR2)
850 IS
851 l_api_name CONSTANT VARCHAR2 (30) := 'construct_material_detail';
852 l_return_status VARCHAR2 (1);
853 get_defaults_err EXCEPTION;
854 BEGIN
855 IF g_debug <= gme_debug.g_log_procedure THEN
856 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
857 || l_api_name);
858 END IF;
859
860 x_material_detail_rec.formulaline_id :=
861 p_formula_detail_rec.formulaline_id;
862 x_material_detail_rec.line_no := p_formula_detail_rec.line_no;
863 x_material_detail_rec.inventory_item_id :=
864 p_formula_detail_rec.inventory_item_id;
865 x_material_detail_rec.line_type := p_formula_detail_rec.line_type;
866 x_material_detail_rec.plan_qty :=
867 p_formula_detail_rec.qty
868 * (1 + p_formula_detail_rec.scrap_factor);
869 x_material_detail_rec.dtl_um := p_formula_detail_rec.detail_uom;
870 x_material_detail_rec.actual_qty := 0;
871 x_material_detail_rec.release_type := p_formula_detail_rec.release_type;
872 x_material_detail_rec.scrap_factor := p_formula_detail_rec.scrap_factor;
873 x_material_detail_rec.scale_type := p_formula_detail_rec.scale_type;
874 x_material_detail_rec.phantom_type := p_formula_detail_rec.phantom_type;
875 x_material_detail_rec.cost_alloc := p_formula_detail_rec.cost_alloc;
876 x_material_detail_rec.text_code := p_formula_detail_rec.text_code;
877 x_material_detail_rec.contribute_yield_ind :=
878 NVL (p_formula_detail_rec.contribute_yield_ind, 'Y');
879 x_material_detail_rec.contribute_step_qty_ind :=
880 p_formula_detail_rec.contribute_step_qty_ind;
881 x_material_detail_rec.scale_multiple :=
882 p_formula_detail_rec.scale_multiple;
883 x_material_detail_rec.scale_rounding_variance :=
884 p_formula_detail_rec.scale_rounding_variance;
885 x_material_detail_rec.rounding_direction :=
886 p_formula_detail_rec.rounding_direction;
887 x_material_detail_rec.by_product_type :=
888 p_formula_detail_rec.by_product_type;
889 x_material_detail_rec.organization_id :=
890 p_batch_header_rec.organization_id;
891 x_material_detail_rec.revision := p_formula_detail_rec.revision;
892
893 IF (x_material_detail_rec.line_type = -1) THEN
894 get_supply_defaults
895 (p_organization_id => x_material_detail_rec.organization_id
896 ,p_inventory_item_id => x_material_detail_rec.inventory_item_id
897 ,x_subinventory => x_material_detail_rec.subinventory
898 ,x_locator_id => x_material_detail_rec.locator_id
899 ,x_return_status => l_return_status);
900 ELSE
901 get_yield_defaults
902 (p_organization_id => x_material_detail_rec.organization_id
903 ,p_inventory_item_id => x_material_detail_rec.inventory_item_id
904 ,p_line_type => x_material_detail_rec.line_type
905 ,x_subinventory => x_material_detail_rec.subinventory
906 ,x_locator_id => x_material_detail_rec.locator_id
907 ,x_return_status => l_return_status);
908 END IF;
909
910 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
911 RAISE get_defaults_err;
912 END IF;
913
914 x_return_status := fnd_api.g_ret_sts_success;
915
916 IF g_debug <= gme_debug.g_log_procedure THEN
917 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
918 END IF;
919 EXCEPTION
920 WHEN get_defaults_err THEN
921 x_return_status := l_return_status;
922 WHEN OTHERS THEN
923 gme_common_pvt.log_message ('GME_API_MATL_DTL_SETUP_FAILURE');
924
925 IF g_debug <= gme_debug.g_log_unexpected THEN
926 gme_debug.put_line ( 'When others exception in '
927 || g_pkg_name
928 || '.'
929 || l_api_name
930 || ' Error is '
931 || SQLERRM);
932 END IF;
933
934 x_return_status := fnd_api.g_ret_sts_unexp_error;
935 END construct_material_detail;
936
937 FUNCTION get_process_loss (
938 p_batch_id IN NUMBER DEFAULT NULL
939 ,p_validity_rule_id IN NUMBER DEFAULT NULL
940 ,p_organization_id IN NUMBER DEFAULT NULL
941 ,p_total_output_qty_scaled IN NUMBER
942 ,p_total_output_qty_pre_scale IN NUMBER)
943 RETURN NUMBER
944 IS
945 l_api_name VARCHAR2 (30) := 'GET_PROCESS_LOSS';
946 l_process_loss_a NUMBER;
947 l_process_loss_b NUMBER;
948 l_process_loss_c NUMBER;
949 l_process_loss NUMBER;
950 l_recipe_id NUMBER;
951 l_formula_id NUMBER;
952 l_routing_id NUMBER;
953 l_total_output_qty_pre_scale NUMBER := 0;
954 l_total_output_qty_scaled NUMBER := 0;
955 l_validity_rule_id NUMBER;
956 l_plant_type NUMBER;
957 l_plant_code VARCHAR2 (4);
958 l_routing_class gmd_routings_b.routing_class%TYPE;
959 /*Bug#5618961 Begin changed to gmd_routings_b.routing_uom from gmd_routings_b.item_um
960 and declared few variables */
961 l_uom gmd_routings_b.routing_uom%TYPE;
962 l_routing_class_uom gmd_routings_b.routing_uom%TYPE;
963 l_routing_uom gmd_routings_b.routing_uom%TYPE;
964 l_qty NUMBER;
965 l_routing_process_loss NUMBER;
966 l_routing_qty NUMBER;
967 l_item_id NUMBER;
968 --Bug#5618961 End
969 expected_error EXCEPTION;
970
971
972 CURSOR cur_get_info (v_batch_id NUMBER)
973 IS
974 SELECT b.recipe_validity_rule_id, a.recipe_id, b.organization_id
975 ,b.routing_id, b.formula_id
976 FROM gme_batch_header b, gmd_recipe_validity_rules a
977 WHERE b.batch_id = v_batch_id
978 AND b.recipe_validity_rule_id = a.recipe_validity_rule_id;
979
980 CURSOR cur_get_info_from_validity (v_validity_rule_id NUMBER)
981 IS
982 SELECT r.recipe_id, r.routing_id, r.formula_id
983 FROM gmd_recipes_b r, gmd_recipe_validity_rules v
984 WHERE v.recipe_validity_rule_id = v_validity_rule_id
985 AND v.recipe_id = r.recipe_id;
986
987 /*Bug#5618961 cursor modified to select routing class uom instead of
988 selecting routing uom*/
989 CURSOR cur_get_rtclass (v_routing_id NUMBER)
990 IS
991 SELECT a.routing_class,routing_uom, b.routing_class_uom, --item_um
992 process_loss, routing_qty
993 FROM fm_rout_hdr a, gmd_routing_class_b b
994 WHERE a.routing_id = v_routing_id
995 AND a.routing_class = b.routing_class (+);
996
997 CURSOR orgn_process_loss_cursor (v_recipe_id NUMBER, v_org_id NUMBER)
998 IS
999 SELECT process_loss
1000 FROM gmd_recipe_process_loss
1001 WHERE recipe_id = v_recipe_id AND organization_id = v_org_id;
1002
1003 CURSOR recipe_process_loss_cursor (v_recipe_id NUMBER)
1004 IS
1005 SELECT planned_process_loss
1006 FROM gmd_recipes
1007 WHERE recipe_id = v_recipe_id;
1008
1009 /*Bug#5618961 cursor modified to select routing qty and uom */
1010 CURSOR routing_process_loss_cursor (v_routing_id NUMBER)
1011 IS
1012 SELECT process_loss, routing_qty, routing_uom
1013 FROM fm_rout_hdr
1014 WHERE routing_id = v_routing_id;
1015
1016 CURSOR class_process_loss_cursor (v_routing_class VARCHAR2, v_qty NUMBER)
1017 IS
1018 SELECT process_loss
1019 FROM gmd_process_loss
1020 WHERE v_qty <= NVL (max_quantity, v_qty)
1021 AND routing_class = v_routing_class
1022 ORDER BY max_quantity;
1023
1024 --Bug#5618961 cursor modified to select std qty, uom and inventory item id
1025 CURSOR validity_process_loss_cursor (v_recipe_validity_rule_id NUMBER)
1026 IS
1027 SELECT planned_process_loss, std_qty, detail_uom, inventory_item_id
1028 FROM gmd_recipe_validity_rules
1029 WHERE recipe_validity_rule_id = NVL (v_recipe_validity_rule_id, -1);
1030 BEGIN
1031 IF g_debug <= gme_debug.g_log_procedure THEN
1032 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1033 || l_api_name);
1034 END IF;
1035
1036 l_total_output_qty_pre_scale := p_total_output_qty_pre_scale;
1037 l_total_output_qty_scaled := p_total_output_qty_scaled;
1038
1039 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1040 gme_debug.put_line (l_api_name || ': Parameters');
1041 gme_debug.put_line ('p_batch_id:' || p_batch_id);
1042 gme_debug.put_line ('p_validity_rule_id:' || p_validity_rule_id);
1043 gme_debug.put_line ('p_organization_id:' || p_organization_id);
1044 gme_debug.put_line ( 'p__total_output_qty_scaled:'
1045 || p_total_output_qty_scaled);
1046 gme_debug.put_line ( 'p__total_output_qty_pre_scale:'
1047 || p_total_output_qty_pre_scale);
1048 END IF;
1049
1050 /* Use and validate the batch id if it is passed in. */
1051 IF (p_batch_id IS NOT NULL) THEN
1052 OPEN cur_get_info (p_batch_id);
1053
1054 FETCH cur_get_info
1055 INTO l_validity_rule_id, l_recipe_id, l_plant_code, l_routing_id
1056 ,l_formula_id;
1057
1058 CLOSE cur_get_info;
1059
1060 IF l_recipe_id IS NULL THEN
1061 gme_common_pvt.log_message ('GME_INVALID_BATCH');
1062 RAISE expected_error;
1063 END IF;
1064 ELSIF p_validity_rule_id IS NOT NULL THEN
1065 -- Validate recipe validity rule
1066 l_validity_rule_id := p_validity_rule_id;
1067
1068 OPEN cur_get_info_from_validity (p_validity_rule_id);
1069
1070 FETCH cur_get_info_from_validity
1071 INTO l_recipe_id, l_routing_id, l_formula_id;
1072
1073 CLOSE cur_get_info_from_validity;
1074
1075 IF l_recipe_id IS NULL THEN
1076 gme_common_pvt.log_message ('GME_API_INVALID_RULE');
1077 RAISE expected_error;
1078 END IF;
1079 END IF;
1080
1081 /* Fetch the routing class for the given routing id. */
1082 IF (l_routing_id IS NOT NULL) THEN
1083 OPEN cur_get_rtclass (l_routing_id);
1084 --Bug#5618961 fetching l_routing_class_uom too
1085 FETCH cur_get_rtclass
1086 INTO l_routing_class, l_routing_uom, l_routing_class_uom, l_routing_process_loss, l_routing_qty;
1087
1088 CLOSE cur_get_rtclass;
1089 ELSE
1090 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1091 gme_debug.put_line (l_api_name || ': No routing defined');
1092 END IF;
1093 END IF;
1094
1095 -- Determine the process loss.
1096 l_process_loss_a := NULL;
1097
1098 --Pawan kumar added this code for bug 2473858 for validity rule project
1099 OPEN validity_process_loss_cursor (l_validity_rule_id);
1100
1101 FETCH validity_process_loss_cursor
1102 INTO l_process_loss_a, l_qty, l_uom, l_item_id;
1103
1104 CLOSE validity_process_loss_cursor;
1105
1106 IF l_process_loss_a IS NULL THEN
1107 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1108 gme_debug.put_line ( l_api_name
1109 || ': No process_loss at validity level');
1110 END IF;
1111
1112 --Bug#5618961 initializing the variables if we did not find process loss VR level
1113 l_uom := l_routing_uom;
1114 l_qty := NULL;
1115 l_item_id := 0;
1116
1117 OPEN orgn_process_loss_cursor (l_recipe_id, l_plant_code);
1118
1119 FETCH orgn_process_loss_cursor
1120 INTO l_process_loss_a;
1121
1122 CLOSE orgn_process_loss_cursor;
1123
1124 IF l_process_loss_a IS NULL THEN
1125 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1126 gme_debug.put_line ( l_api_name
1127 || ': No process_loss at orgn level');
1128 END IF;
1129
1130 OPEN recipe_process_loss_cursor (l_recipe_id);
1131
1132 FETCH recipe_process_loss_cursor
1133 INTO l_process_loss_a;
1134
1135 CLOSE recipe_process_loss_cursor;
1136
1137 IF l_process_loss_a IS NULL THEN
1138 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1139 gme_debug.put_line ( l_api_name
1140 || 'No process_loss at recipe level');
1141 END IF;
1142
1143 --Bug#5618961 Begin commented the following cursor code as we already fetch the values
1144 /*OPEN routing_process_loss_cursor (l_routing_id);
1145
1146 FETCH routing_process_loss_cursor
1147 INTO l_process_loss_a;
1148
1149 CLOSE routing_process_loss_cursor; */
1150
1151 l_process_loss_a := NVL (l_routing_process_loss, 0);
1152 l_qty := l_routing_qty;
1153 l_uom := l_routing_uom;
1154 --Bug#5618961 End
1155 END IF; /* l_process_loss_a IS NULL @recipe level*/
1156 END IF; /* l_process_loss_a IS NULL @orgn level*/
1157 END IF; /* l_process_loss_a IS NULL @validity rule level*/
1158
1159 -- Initialize l_process_loss here one time. It will have a value even if it is zero.
1160 l_process_loss := l_process_loss_a;
1161
1162 --Bug#5618961 Begin assigning the actual qty tht has to be considered
1163 IF l_qty IS NOT NULL THEN
1164 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1165 gme_debug.put_line(l_api_name|| 'Qty to be considered for theoretical process loss'|| l_qty);
1166 END IF;
1167 l_total_output_qty_pre_scale := l_qty;
1168 END IF;
1169
1170 IF (l_routing_id IS NOT NULL AND l_process_loss > 0 AND
1171 l_routing_class IS NOT NULL) THEN
1172
1173 IF l_uom <> l_routing_class_uom THEN
1174 l_qty := inv_convert.inv_um_convert
1175 (item_id => l_item_id
1176 ,PRECISION => gme_common_pvt.g_precision
1177 ,from_quantity => l_total_output_qty_pre_scale
1178 ,from_unit => l_uom
1179 ,to_unit => l_routing_class_uom
1180 ,from_name => NULL
1181 ,to_name => NULL);
1182 IF l_qty < 0 THEN
1183 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1184 gme_debug.put_line( l_api_name
1185 || 'No process_loss at routing class level for output B'
1186 || l_total_output_qty_pre_scale);
1187 END IF;
1188 ELSE
1189 --assign the qty to pre scale i.e in routing class uom
1190 l_total_output_qty_pre_scale := l_qty;
1191 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1192 gme_debug.put_line(l_api_name|| 'Before scaled qty after converting to routing calss uom'|| l_qty);
1193 END IF;
1194 END IF; /*l_qty < 0 */
1195 END IF; /*l_uom <> l_routing_class_uom*/
1196
1197 IF l_routing_uom <> l_routing_class_uom THEN
1198 l_qty := inv_convert.inv_um_convert
1199 (item_id => 0
1200 ,PRECISION => gme_common_pvt.g_precision
1201 ,from_quantity => l_total_output_qty_scaled
1202 ,from_unit => l_routing_uom
1203 ,to_unit => l_routing_class_uom
1204 ,from_name => NULL
1205 ,to_name => NULL);
1206 IF l_qty < 0 THEN
1207 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1208 gme_debug.put_line( l_api_name
1209 || 'No process_loss at routing class level for output B'
1210 || p_total_output_qty_scaled);
1211 END IF;
1212 ELSE
1213 --assign the qty to pre scale i.e in routing class uom
1214 l_total_output_qty_scaled := l_qty;
1215 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1216 gme_debug.put_line(l_api_name|| 'after scaled Qty after converting to routing calss uom'|| l_qty);
1217 END IF;
1218 END IF; /*l_qty < 0 */
1219 END IF; /*l_uom <> l_routing_class_uom*/
1220
1221 END IF; /*l_routing_id IS NOT NUL */
1222
1223 --Bug#5618961 End
1224
1225 -- The following IF condition will override l_process_loss only when necessary.
1226 IF (l_routing_id IS NOT NULL) AND l_process_loss > 0 THEN
1227 IF l_routing_class IS NOT NULL THEN
1228 l_process_loss_b := NULL;
1229
1230 OPEN class_process_loss_cursor
1231 (l_routing_class
1232 ,NVL (l_total_output_qty_scaled
1233 ,l_total_output_qty_pre_scale) );
1234
1235 FETCH class_process_loss_cursor
1236 INTO l_process_loss_b;
1237
1238 CLOSE class_process_loss_cursor;
1239
1240 IF NVL (l_process_loss_b, 0) = 0 THEN
1241 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1242 gme_debug.put_line
1243 ( l_api_name
1244 || 'No process_loss at routing class level for output B'
1245 || p_total_output_qty_scaled);
1246 END IF;
1247 ELSE
1248 l_process_loss_c := NULL;
1249
1250 OPEN class_process_loss_cursor (l_routing_class
1251 ,l_total_output_qty_pre_scale);
1252
1253 FETCH class_process_loss_cursor
1254 INTO l_process_loss_c;
1255
1256 CLOSE class_process_loss_cursor;
1257
1258 IF NVL (l_process_loss_c, 0) = 0 THEN
1259 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1260 gme_debug.put_line
1261 ( l_api_name
1262 || 'No process_loss at routing class level for output C'
1263 || p_total_output_qty_pre_scale);
1264 END IF;
1265 ELSE
1266 /* If it makes it here then it found a loss for both the pre and post scale quantities. */
1267 l_process_loss :=
1268 l_process_loss * l_process_loss_b / l_process_loss_c;
1269
1270 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1271 gme_debug.put_line ( l_api_name
1272 || 'Process_loss a'
1273 || l_process_loss_a
1274 || 'Process_loss b'
1275 || l_process_loss_b
1276 || 'Process_loss c'
1277 || l_process_loss_c);
1278 END IF;
1279 END IF; /* NVL (l_process_loss_c, 0) = 0 */
1280 END IF; /* NVL (l_process_loss_b, 0) = 0 */
1281 END IF; /* l_routing_class IS NOT NULL */
1282 END IF; /* (l_routing_id IS NOT NULL AND l_process_loss > 0) */
1283
1284 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
1285 gme_debug.put_line ('Process Loss is ' || l_process_loss);
1286 END IF;
1287
1288 IF g_debug <= gme_debug.g_log_procedure THEN
1289 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1290 END IF;
1291
1292 RETURN (l_process_loss);
1293 EXCEPTION
1294 WHEN expected_error THEN
1295 RETURN (NULL);
1296 WHEN OTHERS THEN
1297 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1298
1299 IF g_debug <= gme_debug.g_log_unexpected THEN
1300 gme_debug.put_line ( 'When others exception in '
1301 || g_pkg_name
1302 || '.'
1303 || l_api_name
1304 || ' Error is '
1305 || SQLERRM);
1306 END IF;
1307
1308 RETURN (NULL);
1309 END get_process_loss;
1310
1311 PROCEDURE create_document_no (
1312 p_batch_header_rec IN gme_batch_header%ROWTYPE
1313 ,x_batch_header_rec IN OUT NOCOPY gme_batch_header%ROWTYPE)
1314 IS
1315 l_assignment_type NUMBER;
1316 l_document_no gme_batch_header.batch_no%TYPE;
1317 invalid_doc_no EXCEPTION;
1318 l_api_name CONSTANT VARCHAR2 (30) := 'create_document_no';
1319 PRAGMA AUTONOMOUS_TRANSACTION;
1320 BEGIN
1321 IF g_debug <= gme_debug.g_log_procedure THEN
1322 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1323 || l_api_name);
1324 END IF;
1325
1326 x_batch_header_rec := p_batch_header_rec;
1327
1328 IF (x_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_batch) THEN
1329 SELECT batch_doc_numbering, batch_no_last_assigned + 1
1330 INTO l_assignment_type, l_document_no
1331 FROM gme_parameters
1332 WHERE organization_id = x_batch_header_rec.organization_id
1333 FOR UPDATE OF batch_no_last_assigned NOWAIT;
1334 ELSE
1335 SELECT fpo_doc_numbering, fpo_no_last_assigned + 1
1336 INTO l_assignment_type, l_document_no
1337 FROM gme_parameters
1338 WHERE organization_id = x_batch_header_rec.organization_id
1339 FOR UPDATE OF batch_no_last_assigned NOWAIT;
1340 END IF;
1341
1342 IF l_assignment_type = gme_common_pvt.g_auto_doc_numbering THEN
1343 IF l_document_no IS NULL THEN
1344 RAISE invalid_doc_no;
1345 END IF;
1346
1347 IF (x_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_batch) THEN
1348 UPDATE gme_parameters
1349 SET batch_no_last_assigned = batch_no_last_assigned + 1
1350 WHERE organization_id = p_batch_header_rec.organization_id;
1351 ELSE
1352 UPDATE gme_parameters
1353 SET fpo_no_last_assigned = fpo_no_last_assigned + 1
1354 WHERE organization_id = p_batch_header_rec.organization_id;
1355 END IF;
1356
1357 x_batch_header_rec.batch_no := l_document_no;
1358 COMMIT;
1359 ELSE
1360 ROLLBACK;
1361 END IF;
1362
1363 IF g_debug <= gme_debug.g_log_procedure THEN
1364 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1365 END IF;
1366 EXCEPTION
1367 WHEN invalid_doc_no THEN
1368 x_batch_header_rec.batch_no := NULL;
1369 fnd_message.set_name ('GME', 'GME_INVALID_AUTO_DOC');
1370 fnd_msg_pub.ADD;
1371 ROLLBACK;
1372 WHEN app_exception.record_lock_exception THEN
1373 gme_common_pvt.log_message ('GME_DOC_SEQ_LOCK'
1374 ,'S1'
1375 ,p_batch_header_rec.organization_id);
1376 ROLLBACK;
1377 WHEN NO_DATA_FOUND THEN
1378 x_batch_header_rec.batch_no := NULL;
1379 fnd_message.set_name ('GMA', 'SY_NODOCSEQREC');
1380 fnd_msg_pub.ADD;
1381 ROLLBACK;
1382 WHEN OTHERS THEN
1383 IF g_debug <= gme_debug.g_log_unexpected THEN
1384 gme_debug.put_line ( 'When others exception in '
1385 || g_pkg_name
1386 || '.'
1387 || l_api_name
1388 || ' Error is '
1389 || SQLERRM);
1390 END IF;
1391
1392 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1393 x_batch_header_rec.batch_no := NULL;
1394 ROLLBACK;
1395 END create_document_no;
1396
1397 FUNCTION calc_date_from_prod_rule (
1398 p_organization_id IN NUMBER
1399 ,p_inventory_item_id IN NUMBER
1400 ,p_item_qty IN NUMBER
1401 ,p_start_date IN DATE
1402 ,p_cmplt_date IN DATE
1403 ,x_start_date IN OUT NOCOPY DATE
1404 ,x_cmplt_date IN OUT NOCOPY DATE)
1405 RETURN BOOLEAN
1406 IS
1407 -- Bug 13721430 - Add std lot size.
1408 CURSOR cur_common_rules (v_org_id NUMBER, v_inventory_item_id NUMBER)
1409 IS
1410 SELECT std_lot_size, fixed_lead_time, variable_lead_time
1411 FROM mtl_system_items_b
1412 WHERE organization_id = v_org_id
1413 AND inventory_item_id = v_inventory_item_id;
1414
1415 l_rule_rec cur_common_rules%ROWTYPE;
1416 l_total_leadtime NUMBER;
1417 l_api_name CONSTANT VARCHAR2 (30) := 'calc_date_from_prod_rule';
1418 BEGIN
1419 IF g_debug <= gme_debug.g_log_procedure THEN
1420 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1421 || l_api_name);
1422 END IF;
1423
1424 OPEN cur_common_rules (p_organization_id, p_inventory_item_id);
1425
1426 FETCH cur_common_rules
1427 INTO l_rule_rec;
1428
1429 CLOSE cur_common_rules;
1430
1431 IF ( NVL (l_rule_rec.fixed_lead_time, 0) = 0
1432 AND NVL (l_rule_rec.variable_lead_time, 0) = 0) THEN
1433 RETURN FALSE;
1434 END IF;
1435
1436 -- Bug 13721430 - Add std lot size to calculation. p_item_qty is already passed
1437 -- in using primary uom and std lot size is considered to be in primary uom.
1438 l_total_leadtime := ROUND (NVL(l_rule_rec.fixed_lead_time, 0)
1439 + (NVL(l_rule_rec.variable_lead_time, 0) * p_item_qty / NVL(l_rule_rec.std_lot_size, 1)) ,5);
1440
1441 -- l_total_leadtime :=
1442 -- ROUND ( NVL (l_rule_rec.fixed_lead_time, 0)
1443 -- + NVL (l_rule_rec.variable_lead_time, 0) * p_item_qty
1444 -- ,5);
1445
1446 -- l_total_leadtime := ROUND (l_total_leadtime / 24, 5); /*Bug#7385805 */
1447
1448 IF p_start_date IS NOT NULL THEN
1449 x_cmplt_date := p_start_date + l_total_leadtime;
1450 x_start_date := p_start_date;
1451 ELSIF p_cmplt_date IS NOT NULL THEN
1452 x_start_date := p_cmplt_date - l_total_leadtime;
1453 x_cmplt_date := p_cmplt_date;
1454 ELSE
1455 -- if you get here, that means that a rule was found, but no dates were passed in...
1456 -- let's default start date to sysdate and work in the prod rule...
1457 x_start_date := gme_common_pvt.g_timestamp;
1458 x_cmplt_date := x_start_date + l_total_leadtime;
1459 END IF;
1460
1461 IF g_debug <= gme_debug.g_log_procedure THEN
1462 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1463 END IF;
1464
1465 RETURN TRUE;
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468 IF g_debug <= gme_debug.g_log_unexpected THEN
1469 gme_debug.put_line ( 'When others exception in '
1470 || g_pkg_name
1471 || '.'
1472 || l_api_name
1473 || ' Error is '
1474 || SQLERRM);
1475 END IF;
1476
1477 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1478 RETURN FALSE;
1479 END calc_date_from_prod_rule;
1480
1481 PROCEDURE calc_mtl_req_date (
1482 p_batch_header_rec IN gme_batch_header%ROWTYPE
1483 DEFAULT NULL
1484 ,p_batchstep_rec IN gme_batch_steps%ROWTYPE
1485 DEFAULT NULL
1486 ,p_mtl_dtl_rec IN gme_material_details%ROWTYPE
1487 ,x_mtl_req_date OUT NOCOPY DATE
1488 ,x_return_status OUT NOCOPY VARCHAR2)
1489 IS
1490 l_batchstep_id NUMBER;
1491 l_step_start_date DATE;
1492 l_step_cmplt_date DATE;
1493 l_batch_start_date DATE;
1494 l_batch_cmplt_date DATE;
1495 l_api_name CONSTANT VARCHAR2 (30) := 'calc_mtl_req_date';
1496
1497 CURSOR cur_get_associated_step (v_material_detail_id NUMBER)
1498 IS
1499 SELECT batchstep_id
1500 FROM gme_batch_step_items
1501 WHERE material_detail_id = v_material_detail_id;
1502
1503 CURSOR cur_fetch_step_dates (v_batchstep_id NUMBER)
1504 IS
1505 SELECT plan_start_date, plan_cmplt_date
1506 FROM gme_batch_steps
1507 WHERE batchstep_id = v_batchstep_id;
1508
1509 CURSOR cur_fetch_batch_dates (v_batch_id NUMBER)
1510 IS
1511 SELECT plan_start_date, plan_cmplt_date
1512 FROM gme_batch_header
1513 WHERE batch_id = v_batch_id;
1514 BEGIN
1515 IF g_debug <= gme_debug.g_log_procedure THEN
1516 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1517 || l_api_name);
1518 END IF;
1519
1520 x_return_status := fnd_api.g_ret_sts_success;
1521
1522 IF p_mtl_dtl_rec.release_type IN
1523 (gme_common_pvt.g_mtl_manual_release
1524 ,gme_common_pvt.g_mtl_incremental_release
1525 ,gme_common_pvt.g_mtl_autobystep_release) THEN
1526 IF (p_batchstep_rec.batchstep_id IS NULL) THEN
1527 OPEN cur_get_associated_step (p_mtl_dtl_rec.material_detail_id);
1528
1529 FETCH cur_get_associated_step
1530 INTO l_batchstep_id;
1531
1532 CLOSE cur_get_associated_step;
1533 ELSE
1534 l_batchstep_id := p_batchstep_rec.batchstep_id;
1535 END IF;
1536 END IF;
1537
1538 IF l_batchstep_id IS NOT NULL THEN
1539 IF ( p_batchstep_rec.plan_start_date IS NULL
1540 OR p_batchstep_rec.plan_cmplt_date IS NULL) THEN
1541 OPEN cur_fetch_step_dates (l_batchstep_id);
1542
1543 FETCH cur_fetch_step_dates
1544 INTO l_step_start_date, l_step_cmplt_date;
1545
1546 CLOSE cur_fetch_step_dates;
1547 ELSE
1548 l_step_start_date := p_batchstep_rec.plan_start_date;
1549 l_step_cmplt_date := p_batchstep_rec.plan_cmplt_date;
1550 END IF;
1551
1552 IF p_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
1553 x_mtl_req_date := l_step_start_date;
1554 ELSE
1555 x_mtl_req_date := l_step_cmplt_date;
1556 END IF;
1557 ELSE
1558 IF ( p_batch_header_rec.plan_start_date IS NULL
1559 OR p_batch_header_rec.plan_cmplt_date IS NULL) THEN
1560 OPEN cur_fetch_batch_dates (p_mtl_dtl_rec.batch_id);
1561
1562 FETCH cur_fetch_batch_dates
1563 INTO l_batch_start_date, l_batch_cmplt_date;
1564
1565 CLOSE cur_fetch_batch_dates;
1566 ELSE
1567 l_batch_start_date := p_batch_header_rec.plan_start_date;
1568 l_batch_cmplt_date := p_batch_header_rec.plan_cmplt_date;
1569 END IF;
1570
1571 IF p_mtl_dtl_rec.line_type = gme_common_pvt.g_line_type_ing THEN
1572 x_mtl_req_date := l_batch_start_date;
1573 ELSE
1574 x_mtl_req_date := l_batch_cmplt_date;
1575 END IF;
1576 END IF;
1577
1578 IF g_debug <= gme_debug.g_log_procedure THEN
1579 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1580 END IF;
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 IF g_debug <= gme_debug.g_log_unexpected THEN
1584 gme_debug.put_line ( 'When others exception in '
1585 || g_pkg_name
1586 || '.'
1587 || l_api_name
1588 || ' Error is '
1589 || SQLERRM);
1590 END IF;
1591
1592 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1593 x_return_status := fnd_api.g_ret_sts_unexp_error;
1594 END calc_mtl_req_date;
1595
1596 FUNCTION is_qty_below_capacity (
1597 p_batch_step_resources_rec IN gme_batch_step_resources%ROWTYPE)
1598 RETURN BOOLEAN
1599 IS
1600 CURSOR cur_get_um_type (v_uom_code VARCHAR2)
1601 IS
1602 SELECT b.uom_class, a.conversion_rate
1603 FROM mtl_uom_conversions a, mtl_units_of_measure b
1604 WHERE a.uom_code = b.uom_code
1605 AND a.inventory_item_id = 0
1606 AND b.uom_code = v_uom_code;
1607
1608 CURSOR cur_get_activity_factor (v_batchstep_activity_id NUMBER)
1609 IS
1610 SELECT plan_activity_factor, actual_activity_factor
1611 FROM gme_batch_step_activities
1612 WHERE batchstep_activity_id = v_batchstep_activity_id;
1613
1614 l_resource_qty NUMBER;
1615 l_min_capacity NUMBER;
1616 l_cap_um_type sy_uoms_typ.um_type%TYPE;
1617 l_qty_um_type sy_uoms_typ.um_type%TYPE;
1618 l_cap_std_factor NUMBER;
1619 l_qty_std_factor NUMBER;
1620 l_plan_activity_factor NUMBER;
1621 l_actual_activity_factor NUMBER;
1622 l_api_name CONSTANT VARCHAR2 (30) := 'is_qty_below_capacity';
1623 BEGIN
1624 IF g_debug <= gme_debug.g_log_procedure THEN
1625 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1626 || l_api_name);
1627 END IF;
1628
1629 /* If capacities are not defined for the resource then we have nothing to check against so return true */
1630 IF (p_batch_step_resources_rec.min_capacity IS NULL)
1631 OR (p_batch_step_resources_rec.capacity_um IS NULL) THEN
1632 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1633 gme_debug.put_line
1634 (' Resource min capacity or capacity um is null');
1635 RETURN FALSE;
1636 END IF;
1637 END IF;
1638
1639 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1640 gme_debug.put_line ( ' Resource min capacity:'
1641 || p_batch_step_resources_rec.min_capacity
1642 || ' Capacity UM:'
1643 || p_batch_step_resources_rec.capacity_um
1644 || ' Resource UOM:'
1645 || p_batch_step_resources_rec.resource_qty_um);
1646 END IF;
1647
1648 /* Let us also get the activity factors as the resource */
1649 /* quantities include the activity factor in them */
1650 OPEN cur_get_activity_factor
1651 (p_batch_step_resources_rec.batchstep_activity_id);
1652
1653 FETCH cur_get_activity_factor
1654 INTO l_plan_activity_factor, l_actual_activity_factor;
1655
1656 CLOSE cur_get_activity_factor;
1657
1658 IF l_actual_activity_factor IS NULL THEN
1659 l_actual_activity_factor := l_plan_activity_factor;
1660 END IF;
1661
1662 /* Let us determine the quantity to check against the min capacity */
1663 IF NVL (p_batch_step_resources_rec.actual_rsrc_qty, -1) <> -1 THEN
1664 IF l_actual_activity_factor > 0 THEN
1665 l_resource_qty :=
1666 p_batch_step_resources_rec.actual_rsrc_qty
1667 / l_actual_activity_factor;
1668
1669 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1670 gme_debug.put_line ( ' Resource Qty by Actual:'
1671 || l_resource_qty
1672 || ' Activity Factor:'
1673 || l_actual_activity_factor);
1674 END IF;
1675 ELSE
1676 /* If the activitiy is not being used then we need not check the resource */
1677 RETURN FALSE;
1678 END IF;
1679 ELSE
1680 IF l_plan_activity_factor > 0 THEN
1681 l_resource_qty :=
1682 p_batch_step_resources_rec.plan_rsrc_qty
1683 / l_plan_activity_factor;
1684
1685 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1686 gme_debug.put_line ( ' Resource Qty by Plan:'
1687 || l_resource_qty
1688 || ' Activity Factor:'
1689 || l_plan_activity_factor);
1690 END IF;
1691 ELSE
1692 /* If the activitiy is not being used then we need not check the resource */
1693 RETURN FALSE;
1694 END IF;
1695 END IF;
1696 /* IF NVL(p_batch_step_resources_rec.actual_rsrc_qty, -1) <> -1 */
1697 /* If the uoms are the same then we can do a direct comparison */
1698
1699 IF p_batch_step_resources_rec.capacity_um =
1700 p_batch_step_resources_rec.resource_qty_um THEN
1701 IF l_resource_qty < p_batch_step_resources_rec.min_capacity THEN
1702 RETURN TRUE;
1703 ELSE
1704 RETURN FALSE;
1705 END IF;
1706 /* IF l_resource_qty < p_batch_step_resources_rec.min_capacity */
1707 ELSE
1708 /* Lets convert the capacity to the resource qty uom */
1709 /* Since their is no item associated we cannot do a conversion between */
1710 /* inter um types. let us first fetch the um types to determine that */
1711 OPEN cur_get_um_type (p_batch_step_resources_rec.capacity_um);
1712
1713 FETCH cur_get_um_type
1714 INTO l_cap_um_type, l_cap_std_factor;
1715
1716 CLOSE cur_get_um_type;
1717
1718 OPEN cur_get_um_type (p_batch_step_resources_rec.resource_qty_um);
1719
1720 FETCH cur_get_um_type
1721 INTO l_qty_um_type, l_qty_std_factor;
1722
1723 CLOSE cur_get_um_type;
1724
1725 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1726 gme_debug.put_line ( ' Capacity uom type:'
1727 || l_cap_um_type
1728 || ' Std Factor:'
1729 || l_cap_std_factor
1730 || ' Qty uom type:'
1731 || l_qty_um_type
1732 || ' Qty Std Factor:'
1733 || l_qty_std_factor);
1734 END IF;
1735
1736 /* We have everything - let us check now */
1737 IF l_cap_um_type <> l_qty_um_type THEN
1738 RETURN FALSE;
1739 ELSE
1740 l_min_capacity :=
1741 p_batch_step_resources_rec.min_capacity
1742 * (l_cap_std_factor / l_qty_std_factor);
1743
1744 IF l_resource_qty < l_min_capacity THEN
1745 RETURN TRUE;
1746 ELSE
1747 RETURN FALSE;
1748 END IF; /* IF l_resource_qty < l_min_capacity */
1749 END IF; /* IF l_cap_um_type <> l_qty_um_type */
1750 END IF;
1751 /* IF p_batch_step_resources_rec.capacity_uom = p_batch_step_resources_rec.process_qty_um */
1752
1753 IF g_debug <= gme_debug.g_log_procedure THEN
1754 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1755 END IF;
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 IF g_debug <= gme_debug.g_log_unexpected THEN
1759 gme_debug.put_line ( 'When others exception in '
1760 || g_pkg_name
1761 || '.'
1762 || l_api_name
1763 || ' Error is '
1764 || SQLERRM);
1765 END IF;
1766
1767 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1768 RETURN FALSE;
1769 END is_qty_below_capacity;
1770
1771 FUNCTION resource_qty_below_capacity (p_batch_id IN NUMBER)
1772 RETURN BOOLEAN
1773 IS
1774 CURSOR cur_get_resources_old
1775 IS
1776 SELECT batchstep_resource_id
1777 FROM gme_batch_step_resources
1778 WHERE batch_id = p_batch_id;
1779
1780 -- Bug 13946067 - Introduce new table and joins to make use of existing index.
1781 CURSOR cur_get_resources
1782 IS
1783 SELECT batchstep_resource_id
1784 FROM gme_batch_step_resources r, gme_batch_steps s
1785 WHERE r.batch_id = p_batch_id
1786 AND s.batch_id = p_batch_id
1787 AND s.batchstep_id = r.batchstep_id;
1788
1789 l_resource_ids gme_common_pvt.number_tab;
1790 l_batch_step_resources gme_batch_step_resources%ROWTYPE;
1791 l_found NUMBER (5) DEFAULT 0;
1792 l_resources VARCHAR2 (2000);
1793 l_api_name CONSTANT VARCHAR2 (30) := 'resource_qty_below_capacity';
1794 BEGIN
1795 IF g_debug <= gme_debug.g_log_procedure THEN
1796 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1797 || l_api_name);
1798 END IF;
1799
1800 OPEN cur_get_resources;
1801
1802 FETCH cur_get_resources
1803 BULK COLLECT INTO l_resource_ids;
1804
1805 CLOSE cur_get_resources;
1806
1807 FOR i IN 1 .. l_resource_ids.COUNT LOOP
1808 l_batch_step_resources.batchstep_resource_id := l_resource_ids (i);
1809
1810 IF gme_batch_step_resources_dbl.fetch_row
1811 (p_batch_step_resources => l_batch_step_resources
1812 ,x_batch_step_resources => l_batch_step_resources) THEN
1813 IF is_qty_below_capacity
1814 (p_batch_step_resources_rec => l_batch_step_resources) THEN
1815 l_found := 1;
1816
1817 IF l_resources IS NULL THEN
1818 l_resources := l_batch_step_resources.resources;
1819 ELSE
1820 l_resources :=
1821 l_resources || ',' || l_batch_step_resources.resources;
1822 END IF; /* IF l_resources IS NULL */
1823 END IF;
1824 /* IF is_qty_below_capacity (p_batch_step_resources => l_batch_step_resources) */
1825 END IF;
1826 /* IF GME_BATCH_STEP_RESOURCES_DBL.fetch_row (p_batch_step_resources => l_batch_step_resources */
1827 END LOOP;
1828 /* FOR i IN 1..l_resource_ids.COUNT */
1829 /* If we have found atleast one resource falling below capacity then return true */
1830
1831 IF g_debug <= gme_debug.g_log_procedure THEN
1832 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1833 END IF;
1834
1835 IF l_found = 1 THEN
1836 gme_common_pvt.log_message ('GME_API_RSRC_QTY_BELOW_CAP'
1837 ,'RESOURCES'
1838 ,l_resources);
1839 RETURN TRUE;
1840 ELSE
1841 RETURN FALSE;
1842 END IF;
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 IF g_debug <= gme_debug.g_log_unexpected THEN
1846 gme_debug.put_line ( 'When others exception in '
1847 || g_pkg_name
1848 || '.'
1849 || l_api_name
1850 || ' Error is '
1851 || SQLERRM);
1852 END IF;
1853
1854 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1855 RETURN FALSE;
1856 END resource_qty_below_capacity;
1857
1858 FUNCTION eff_locator_control (
1859 p_organization_id IN NUMBER
1860 ,p_subinventory IN VARCHAR2 DEFAULT NULL
1861 ,p_inventory_item_id IN NUMBER DEFAULT NULL
1862 ,p_org_control IN NUMBER DEFAULT NULL
1863 ,p_sub_control IN NUMBER DEFAULT NULL
1864 ,p_item_control IN NUMBER DEFAULT NULL
1865 ,p_item_loc_restrict IN NUMBER DEFAULT NULL
1866 ,p_org_neg_allowed IN NUMBER DEFAULT NULL
1867 ,p_action IN NUMBER)
1868 RETURN NUMBER
1869 IS
1870 l_org_control NUMBER;
1871 l_org_neg_allowed NUMBER;
1872 l_sub_control NUMBER;
1873 l_item_control NUMBER;
1874 l_item_loc_restrict NUMBER;
1875 l_eff_control NUMBER;
1876 l_return_status VARCHAR2 (10);
1877 l_msg_count NUMBER;
1878 l_msg_data VARCHAR2 (2000);
1879 l_api_name CONSTANT VARCHAR2 (30) := 'eff_locator_control';
1880
1881 CURSOR cur_org_details (v_org_id NUMBER)
1882 IS
1883 SELECT negative_inv_receipt_code, stock_locator_control_code
1884 FROM mtl_parameters
1885 WHERE organization_id = v_org_id;
1886
1887 CURSOR cur_subinventory_details (
1888 v_org_id NUMBER
1889 ,v_subinventory VARCHAR2)
1890 IS
1891 SELECT NVL (locator_type, 1)
1892 FROM mtl_secondary_inventories
1893 WHERE organization_id = v_org_id
1894 AND secondary_inventory_name = v_subinventory;
1895 /* Bug 5441643 Added NVL condition for location control code*/
1896 CURSOR cur_item_details (v_org_id NUMBER, v_inventory_item_id NUMBER)
1897 IS
1898 SELECT NVL(location_control_code,1), restrict_locators_code
1899 FROM mtl_system_items_b
1900 WHERE organization_id = v_org_id
1901 AND inventory_item_id = v_inventory_item_id;
1902
1903 org_info_not_provided EXCEPTION;
1904 subinventory_info_not_provided EXCEPTION;
1905 item_info_not_provided EXCEPTION;
1906 BEGIN
1907 IF g_debug <= gme_debug.g_log_procedure THEN
1908 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1909 || l_api_name);
1910 END IF;
1911
1912 IF (p_org_control IS NULL OR p_org_neg_allowed IS NULL) THEN
1913 IF (p_organization_id IS NOT NULL) THEN
1914 OPEN cur_org_details (p_organization_id);
1915
1916 FETCH cur_org_details
1917 INTO l_org_neg_allowed, l_org_control;
1918
1919 CLOSE cur_org_details;
1920 ELSE
1921 RAISE org_info_not_provided;
1922 END IF;
1923 ELSE
1924 l_org_neg_allowed := p_org_neg_allowed;
1925 l_org_control := p_org_control;
1926 END IF;
1927
1928 IF (p_sub_control IS NULL) THEN
1929 IF (p_subinventory IS NOT NULL) THEN
1930 OPEN cur_subinventory_details (p_organization_id, p_subinventory);
1931
1932 FETCH cur_subinventory_details
1933 INTO l_sub_control;
1934
1935 CLOSE cur_subinventory_details;
1936 ELSE
1937 RAISE subinventory_info_not_provided;
1938 END IF;
1939 ELSE
1940 l_sub_control := p_sub_control;
1941 END IF;
1942
1943 IF (p_item_control IS NULL OR p_item_loc_restrict IS NULL) THEN
1944 IF (p_inventory_item_id IS NOT NULL) THEN
1945 OPEN cur_item_details (p_organization_id, p_inventory_item_id);
1946
1947 FETCH cur_item_details
1948 INTO l_item_control, l_item_loc_restrict;
1949
1950 CLOSE cur_item_details;
1951 ELSE
1952 RAISE item_info_not_provided;
1953 END IF;
1954 ELSE
1955 l_item_control := p_item_control;
1956 l_item_loc_restrict := p_item_loc_restrict;
1957 END IF;
1958
1959 l_eff_control :=
1960 inv_globals.locator_control
1961 (x_return_status => l_return_status
1962 ,x_msg_count => l_msg_count
1963 ,x_msg_data => l_msg_data
1964 ,p_org_control => l_org_control
1965 ,p_sub_control => l_sub_control
1966 ,p_item_control => l_item_control
1967 ,p_item_loc_restrict => l_item_loc_restrict
1968 ,p_org_neg_allowed => l_org_neg_allowed
1969 ,p_action => p_action);
1970
1971 IF g_debug <= gme_debug.g_log_procedure THEN
1972 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1973 END IF;
1974
1975 RETURN l_eff_control;
1976 EXCEPTION
1977 WHEN org_info_not_provided THEN
1978 RETURN -1;
1979 WHEN subinventory_info_not_provided THEN
1980 RETURN -1;
1981 WHEN item_info_not_provided THEN
1982 RETURN -1;
1983 WHEN OTHERS THEN
1984 IF g_debug <= gme_debug.g_log_unexpected THEN
1985 gme_debug.put_line ( 'When others exception in '
1986 || g_pkg_name
1987 || '.'
1988 || l_api_name
1989 || ' Error is '
1990 || SQLERRM);
1991 END IF;
1992
1993 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1994 RETURN -1;
1995 END eff_locator_control;
1996
1997 FUNCTION check_locator (
1998 p_organization_id IN NUMBER
1999 ,p_locator_id IN NUMBER
2000 ,p_subinventory IN VARCHAR2
2001 ,p_inventory_item_id IN NUMBER
2002 ,p_org_control IN NUMBER
2003 ,p_sub_control IN NUMBER
2004 ,p_item_control IN NUMBER
2005 ,p_item_loc_restrict IN NUMBER
2006 ,p_org_neg_allowed IN NUMBER
2007 ,p_txn_action_id IN NUMBER)
2008 RETURN BOOLEAN
2009 IS
2010 l_api_name CONSTANT VARCHAR2 (30) := 'check_locator';
2011 l_locator inv_validate.LOCATOR;
2012 l_org inv_validate.org;
2013 l_item inv_validate.item;
2014 l_sub inv_validate.sub;
2015 BEGIN
2016 IF g_debug <= gme_debug.g_log_procedure THEN
2017 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2018 || l_api_name);
2019 END IF;
2020
2021 IF g_debug <= gme_debug.g_log_statement THEN
2022 gme_debug.put_line ( g_pkg_name
2023 || '.'
2024 || l_api_name
2025 || 'p_organization_id '
2026 || p_organization_id);
2027 gme_debug.put_line ( g_pkg_name
2028 || '.'
2029 || l_api_name
2030 || 'p_locator_id '
2031 || p_locator_id);
2032 gme_debug.put_line ( g_pkg_name
2033 || '.'
2034 || l_api_name
2035 || 'p_subinventory '
2036 || p_subinventory);
2037 gme_debug.put_line ( g_pkg_name
2038 || '.'
2039 || l_api_name
2040 || 'p_inventory_item_id '
2041 || p_inventory_item_id);
2042 gme_debug.put_line ( g_pkg_name
2043 || '.'
2044 || l_api_name
2045 || 'p_txn_action_id '
2046 || p_txn_action_id);
2047 END IF;
2048
2049 l_locator.inventory_location_id := p_locator_id;
2050 l_org.organization_id := p_organization_id;
2051 l_org.stock_locator_control_code := p_org_control;
2052 l_org.negative_inv_receipt_code := p_org_neg_allowed;
2053 l_item.inventory_item_id := p_inventory_item_id;
2054 /* Bug 5441643 Added NVL condition for location control code*/
2055 l_item.location_control_code := NVL(p_item_control,1);
2056 l_item.restrict_locators_code := p_item_loc_restrict;
2057 l_sub.secondary_inventory_name := p_subinventory;
2058 l_sub.locator_type := p_sub_control;
2059
2060 IF (inv_validate.check_locator (p_locator => l_locator
2061 ,p_org => l_org
2062 ,p_item => l_item
2063 ,p_sub => l_sub
2064 ,p_project_id => NULL
2065 ,p_task_id => NULL
2066 ,p_txn_action_id => p_txn_action_id
2067 ,p_is_from_locator => NULL
2068 ,p_dynamic_ok => TRUE) =
2069 inv_validate.f) THEN
2070 fnd_message.set_name ('INV', 'INV_INVALID_LOCATION');
2071 fnd_msg_pub.ADD;
2072
2073 IF g_debug <= gme_debug.g_log_statement THEN
2074 gme_debug.put_line ( g_pkg_name
2075 || '.'
2076 || l_api_name
2077 || ' Locator IS invalid');
2078 END IF;
2079
2080 RETURN FALSE;
2081 END IF;
2082
2083 IF g_debug <= gme_debug.g_log_procedure THEN
2084 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2085 END IF;
2086
2087 RETURN TRUE;
2088 EXCEPTION
2089 WHEN OTHERS THEN
2090 IF g_debug <= gme_debug.g_log_unexpected THEN
2091 gme_debug.put_line ( 'WHEN OTHERS EXCEPTION IN '
2092 || g_pkg_name
2093 || '.'
2094 || l_api_name
2095 || ' Error IS '
2096 || SQLERRM);
2097 END IF;
2098
2099 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2100 RETURN FALSE;
2101 END check_locator;
2102
2103 FUNCTION check_subinventory (
2104 p_organization_id IN NUMBER
2105 ,p_subinventory IN VARCHAR2
2106 ,p_inventory_item_id IN NUMBER
2107 ,p_restrict_subinv IN NUMBER DEFAULT NULL)
2108 RETURN BOOLEAN
2109 IS
2110 l_api_name CONSTANT VARCHAR2 (30) := 'check_subinventory';
2111 l_org inv_validate.org;
2112 l_item inv_validate.item;
2113 l_sub inv_validate.sub;
2114
2115 CURSOR cur_item_control
2116 IS
2117 SELECT restrict_subinventories_code
2118 FROM mtl_system_items_b
2119 WHERE organization_id = p_organization_id
2120 AND inventory_item_id = p_inventory_item_id;
2121
2122 l_item_restict_subinv NUMBER;
2123 BEGIN
2124 IF g_debug <= gme_debug.g_log_procedure THEN
2125 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2126 || l_api_name);
2127 END IF;
2128
2129 IF g_debug <= gme_debug.g_log_statement THEN
2130 gme_debug.put_line ( g_pkg_name
2131 || '.'
2132 || l_api_name
2133 || 'p_organization_id '
2134 || p_organization_id);
2135 gme_debug.put_line ( g_pkg_name
2136 || '.'
2137 || l_api_name
2138 || 'p_subinventory '
2139 || p_subinventory);
2140 gme_debug.put_line ( g_pkg_name
2141 || '.'
2142 || l_api_name
2143 || 'p_inventory_item_id '
2144 || p_inventory_item_id);
2145 END IF;
2146
2147 l_org.organization_id := p_organization_id;
2148 l_item.inventory_item_id := p_inventory_item_id;
2149 l_sub.secondary_inventory_name := p_subinventory;
2150
2151 IF (p_restrict_subinv IS NULL) THEN
2152 OPEN cur_item_control;
2153
2154 FETCH cur_item_control
2155 INTO l_item_restict_subinv;
2156
2157 CLOSE cur_item_control;
2158 ELSE
2159 l_item_restict_subinv := p_restrict_subinv;
2160 END IF;
2161
2162 IF (l_item_restict_subinv = 1) THEN
2163 IF (inv_validate.subinventory (p_org => l_org
2164 ,p_item => l_item
2165 ,p_sub => l_sub) = inv_validate.f) THEN
2166 IF g_debug <= gme_debug.g_log_statement THEN
2167 gme_debug.put_line ( g_pkg_name
2168 || '.'
2169 || l_api_name
2170 || ' Subinventory is invalid');
2171 END IF;
2172
2173 RETURN FALSE;
2174 END IF;
2175 ELSE
2176 IF (inv_validate.subinventory (p_org => l_org, p_sub => l_sub) =
2177 inv_validate.f) THEN
2178 IF g_debug <= gme_debug.g_log_statement THEN
2179 gme_debug.put_line ( g_pkg_name
2180 || '.'
2181 || l_api_name
2182 || ' Subinventory is invalid');
2183 END IF;
2184
2185 RETURN FALSE;
2186 END IF;
2187 END IF;
2188
2189 IF g_debug <= gme_debug.g_log_procedure THEN
2190 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2191 END IF;
2192
2193 RETURN TRUE;
2194 EXCEPTION
2195 WHEN OTHERS THEN
2196 IF g_debug <= gme_debug.g_log_unexpected THEN
2197 gme_debug.put_line ( 'When others exception in '
2198 || g_pkg_name
2199 || '.'
2200 || l_api_name
2201 || ' Error is '
2202 || SQLERRM);
2203 END IF;
2204
2205 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2206 RETURN FALSE;
2207 END check_subinventory;
2208
2209 FUNCTION validate_validity_rule (
2210 p_validity_rule_id IN NUMBER
2211 ,p_organization_id IN NUMBER
2212 ,p_prim_product_id IN NUMBER
2213 ,p_qty IN NUMBER
2214 ,p_uom IN VARCHAR2
2215 ,p_object_type IN VARCHAR2
2216 ,p_start_date IN DATE
2217 ,p_cmplt_date IN DATE
2218 ,p_creation_mode IN VARCHAR2 DEFAULT NULL)
2219 RETURN BOOLEAN
2220 IS
2221 CURSOR get_validity_rule (v_validity_rule_id NUMBER)
2222 IS
2223 SELECT *
2224 FROM gmd_recipe_validity_rules
2225 WHERE recipe_validity_rule_id = v_validity_rule_id;
2226
2227 CURSOR cur_item_no (v_org_id NUMBER, v_inventory_item_id NUMBER)
2228 IS
2229 SELECT concatenated_segments
2230 FROM mtl_system_items_kfv
2231 WHERE organization_id = v_org_id
2232 AND inventory_item_id = v_inventory_item_id;
2233
2234 l_validity_rec gmd_recipe_validity_rules%ROWTYPE;
2235 --Sunitha ch. bug 5581523 removed the default value of the l_qty variable
2236 --l_qty NUMBER := 0;
2237 l_qty NUMBER;
2238 l_item_id NUMBER := NULL;
2239 l_from_uom VARCHAR2 (4);
2240 l_to_uom VARCHAR2 (4);
2241 l_item_no VARCHAR2 (80);
2242 l_start_date DATE;
2243 l_cmplt_date DATE;
2244 l_recipe_use NUMBER;
2245 l_msg_count NUMBER;
2246 l_msg_data VARCHAR2 (2000);
2247 l_return_code NUMBER;
2248 l_return_status VARCHAR2 (1);
2249 l_total_input NUMBER;
2250 l_total_output NUMBER;
2251 l_recipe_validity_tbl gmd_validity_rules.recipe_validity_tbl;
2252 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_validity_rule';
2253 -- Bug 9914962
2254 l_status_type VARCHAR2 (10);
2255 /* EXCEPTION Definitions */
2256 uom_conversion_failure EXCEPTION;
2257 validation_failure EXCEPTION;
2258 BEGIN
2259 IF g_debug <= gme_debug.g_log_procedure THEN
2260 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2261 || l_api_name);
2262 END IF;
2263
2264 OPEN get_validity_rule (p_validity_rule_id);
2265
2266 FETCH get_validity_rule
2267 INTO l_validity_rec;
2268
2269 IF get_validity_rule%NOTFOUND THEN
2270 CLOSE get_validity_rule;
2271
2272 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
2273 RETURN FALSE;
2274 END IF;
2275
2276 CLOSE get_validity_rule;
2277
2278 -- Bug 13811289 - Make sure this is valid for the specific organization.
2279 IF NVL(l_validity_rec.organization_id, p_organization_id) <> p_organization_id THEN
2280 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
2281 RETURN FALSE;
2282 END IF;
2283
2284 IF l_validity_rec.delete_mark = 1 THEN
2285 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
2286 RETURN FALSE;
2287 ELSIF l_validity_rec.inventory_item_id <> p_prim_product_id THEN
2288 gme_common_pvt.log_message ('GME_INVALID_VALIDITY_RULE_PROD');
2289 RETURN FALSE;
2290 ELSIF p_creation_mode NOT IN ('OUTPUT', 'INPUT') THEN
2291 IF p_uom <> l_validity_rec.detail_uom THEN
2292 l_qty :=
2293 inv_convert.inv_um_convert
2294 (item_id => p_prim_product_id
2295 ,PRECISION => gme_common_pvt.g_precision
2296 ,from_quantity => p_qty
2297 ,from_unit => p_uom
2298 ,to_unit => l_validity_rec.detail_uom
2299 ,from_name => NULL
2300 ,to_name => NULL);
2301
2302 IF (l_qty < 0) THEN
2303 l_item_id := p_prim_product_id;
2304 l_from_uom := p_uom;
2305 l_to_uom := l_validity_rec.detail_uom;
2306 RAISE uom_conversion_failure;
2307 END IF;
2308 ELSE
2309 l_qty := p_qty;
2310 END IF;
2311
2312 IF l_qty < l_validity_rec.min_qty OR l_qty > l_validity_rec.max_qty THEN
2313 gme_common_pvt.log_message ('GME_INVALID_VALIDITY_RULE_QTY');
2314 RETURN FALSE;
2315 END IF;
2316
2317 l_qty := p_qty;
2318 END IF;
2319
2320 IF p_object_type = 'F' /* FPO */ THEN
2321 IF l_validity_rec.recipe_use NOT IN
2322 (0, 1) /* Production , Planning */ THEN
2323 gme_common_pvt.log_message ('GME_INVALID_VALIDITY_RULE_USE');
2324 RETURN FALSE;
2325 END IF;
2326
2327 /* BUG 5021736 added between logic */
2328 IF NOT(l_validity_rec.validity_rule_status between 700 and 799 OR l_validity_rec.validity_rule_status between 900 and 999) THEN
2329 gme_common_pvt.log_message ('GME_INVALID_VALIDITY_RULE_STAT');
2330 RETURN FALSE;
2331 END IF;
2332
2333 l_recipe_use := 0;
2334 ELSIF p_object_type = 'P' /* Production Batch */ THEN
2335 IF l_validity_rec.recipe_use <> 0 /* Production */ THEN
2336 gme_common_pvt.log_message ('GME_INVALID_VALIDITY_RULE_USE');
2337 RETURN FALSE;
2338 END IF;
2339 /* BUG 5021736 added between logic */
2340 IF NOT(l_validity_rec.validity_rule_status between 700 and 799 OR l_validity_rec.validity_rule_status between 900 and 999) THEN
2341 gme_common_pvt.log_message ('GME_INVALID_VALIDITY_RULE_STAT');
2342 RETURN FALSE;
2343 END IF;
2344
2345 l_recipe_use := 1;
2346 ELSIF p_object_type = 'L' /* Lab Batch */ THEN
2347 IF l_validity_rec.recipe_use <> 0 /* Production */ THEN
2348 gme_common_pvt.log_message ('GME_INVALID_VALIDITY_RULE_USE');
2349 RETURN FALSE;
2350 END IF;
2351 /* BUG 5021736 added between logic */
2352 IF NOT(l_validity_rec.validity_rule_status between 400 and 499 OR
2353 l_validity_rec.validity_rule_status between 500 and 599 OR
2354 l_validity_rec.validity_rule_status between 700 and 799 OR
2355 l_validity_rec.validity_rule_status between 900 and 999 OR
2356 l_validity_rec.validity_rule_status between 600 and 699 ) THEN
2357 gme_common_pvt.log_message('GME_INVALID_VALIDITY_RULE_STAT');
2358 RETURN FALSE;
2359 END IF;
2360
2361 l_recipe_use := 1;
2362 END IF;
2363
2364 /* If both dates are passed ten check them against validity rule
2365 If only one date is passed then check that date only
2366 If both dates are not passed then assume both as sysdate and check */
2367 l_start_date := p_start_date;
2368 l_cmplt_date := p_cmplt_date;
2369
2370 IF NOT gme_common_pvt.check_validity_rule_dates
2371 (p_validity_rule_id => p_validity_rule_id
2372 ,p_start_date => l_start_date
2373 ,p_cmplt_date => l_cmplt_date) THEN
2374 RETURN FALSE;
2375 END IF;
2376
2377 IF p_start_date IS NULL AND p_cmplt_date IS NULL THEN
2378 l_start_date := SYSDATE;
2379 l_cmplt_date := SYSDATE;
2380 ELSIF p_start_date IS NULL THEN
2381 l_start_date := l_cmplt_date;
2382 ELSIF p_cmplt_date IS NULL THEN
2383 l_cmplt_date := l_start_date;
2384 END IF;
2385
2386 /* Validating for creating as Recipe validity rule or product and quantity */
2387 IF p_creation_mode NOT IN ('OUTPUT', 'INPUT') THEN
2388 RETURN TRUE;
2389 END IF;
2390
2391 IF p_creation_mode = 'OUTPUT' THEN
2392 l_total_output := p_qty;
2393 ELSE /* p_creation mode = INPUT */
2394 l_total_input := p_qty;
2395 END IF;
2396
2397 gmd_val_data_pub.get_val_data
2398 (p_api_version => 1
2399 ,p_init_msg_list => fnd_api.g_false
2400 ,p_object_type => p_object_type
2401 ,p_recipe_no => NULL
2402 ,p_recipe_version => NULL
2403 ,p_recipe_id => NULL
2404 ,p_total_input => l_total_input
2405 ,p_total_output => l_total_output
2406 ,p_formula_id => NULL
2407 ,p_item_id => p_prim_product_id
2408 ,p_item_no => NULL
2409 ,p_product_qty => l_qty
2410 ,p_uom => p_uom
2411 ,p_recipe_use => l_recipe_use
2412 ,p_organization_id => p_organization_id
2413 ,p_start_date => l_start_date
2414 ,p_end_date => l_cmplt_date
2415 ,p_status_type => l_status_type /* Bug 9914962 */
2416 --,p_status_type => '700'
2417 ,p_validity_rule_id => p_validity_rule_id
2418 ,x_return_status => l_return_status
2419 ,x_msg_count => l_msg_count
2420 ,x_msg_data => l_msg_data
2421 ,x_return_code => l_return_code
2422 ,x_recipe_validity_out => l_recipe_validity_tbl);
2423
2424 IF l_return_status <> fnd_api.g_ret_sts_success
2425 OR l_recipe_validity_tbl.COUNT <= 0 THEN
2426 RAISE validation_failure;
2427 END IF;
2428
2429 IF g_debug <= gme_debug.g_log_procedure THEN
2430 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2431 END IF;
2432
2433 RETURN TRUE;
2434 EXCEPTION
2435 WHEN uom_conversion_failure THEN
2436 IF l_item_no IS NULL THEN
2437 OPEN cur_item_no (p_organization_id, l_item_id);
2438
2439 FETCH cur_item_no
2440 INTO l_item_no;
2441
2442 CLOSE cur_item_no;
2443 END IF;
2444
2445 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2446 gme_debug.put_line
2447 ('UOM conversion failed in validate_validity_rule');
2448 END IF;
2449
2450 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
2451 fnd_message.set_token ('ITEM_NO', l_item_no);
2452 fnd_message.set_token ('FROM_UOM', l_from_uom);
2453 fnd_message.set_token ('TO_UOM', l_to_uom);
2454 fnd_msg_pub.ADD;
2455 RETURN FALSE;
2456 WHEN validation_failure THEN
2457 gme_common_pvt.log_message ('GME_INVALID_VALIDITY_RULE_QTY');
2458 RETURN FALSE;
2459 WHEN OTHERS THEN
2460 IF g_debug <= gme_debug.g_log_unexpected THEN
2461 gme_debug.put_line ( 'When others exception in '
2462 || g_pkg_name
2463 || '.'
2464 || l_api_name
2465 || ' Error is '
2466 || SQLERRM);
2467 END IF;
2468
2469 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2470 RETURN FALSE;
2471 END validate_validity_rule;
2472
2473 PROCEDURE get_batch_shortages (
2474 p_organization_id IN NUMBER
2475 ,p_batch_id IN NUMBER
2476 ,p_invoke_mode IN VARCHAR2
2477 ,p_tree_mode IN NUMBER
2478 ,x_return_status OUT NOCOPY VARCHAR2
2479 ,x_exception_tbl OUT NOCOPY gme_common_pvt.exceptions_tab) IS
2480 /* Bug 5212165 added line_no in the order by */
2481 CURSOR cur_get_materials IS
2482 SELECT d.*
2483 FROM gme_material_details d, mtl_system_items_b i
2484 WHERE d.batch_id IN (SELECT DISTINCT batch_id
2485 FROM gme_material_details
2486 START WITH batch_id = p_batch_id
2487 CONNECT BY batch_id = PRIOR phantom_id)
2488 AND d.line_type = -1
2489 AND d.phantom_type = 0
2490 AND d.actual_qty < NVL (d.wip_plan_qty, d.plan_qty)
2491 AND (p_invoke_mode = 'O' OR (p_invoke_mode = 'S' AND d.subinventory IS NOT NULL))
2492 AND i.organization_id = d.organization_id
2493 AND i.inventory_item_id = d.inventory_item_id
2494 AND i.stock_enabled_flag = 'Y'
2495 AND i.mtl_transactions_enabled_flag = 'Y'
2496 ORDER BY d.inventory_item_id, d.batch_id, d.revision, d.line_no;
2497
2498 --FPbug#4912179 modified query to select required columns only
2499 /* Bug 5441643 Added NVL condition for location control code*/
2500 CURSOR cur_get_item (v_org_id NUMBER, v_inventory_item_id NUMBER)
2501 IS
2502 SELECT concatenated_segments, NVL(location_control_code,1) location_control_code,
2503 restrict_locators_code, primary_uom_code,
2504 reservable_type
2505 FROM mtl_system_items_kfv
2506 WHERE organization_id = v_org_id
2507 AND inventory_item_id = v_inventory_item_id;
2508
2509 CURSOR cur_get_onhand (
2510 v_org_id NUMBER
2511 ,v_inventory_item_id NUMBER
2512 ,v_sub_code VARCHAR2
2513 ,v_revision VARCHAR2)
2514 IS
2515 SELECT NVL (SUM (primary_transaction_quantity), 0) onhand
2516 FROM mtl_onhand_quantities_detail
2517 WHERE organization_id = v_org_id
2518 AND inventory_item_id = v_inventory_item_id
2519 AND (v_revision IS NULL OR revision = v_revision)
2520 AND (p_invoke_mode = 'O' OR (subinventory_code = v_sub_code) )
2521 AND (inv_material_status_grp.is_status_applicable
2522 (NULL
2523 ,NULL
2524 ,gme_common_pvt.g_ing_issue
2525 ,NULL
2526 ,NULL
2527 ,v_org_id
2528 ,inventory_item_id
2529 ,subinventory_code
2530 ,locator_id
2531 ,lot_number
2532 ,NULL
2533 ,'A') = 'N');
2534
2535 -- Bug 14488987 - Get patch level.
2536 CURSOR cur_patch_level IS
2537 SELECT 1
2538 FROM fnd_product_installations
2539 WHERE application_id = 553 -- GME product
2540 AND patch_level like 'R12.GME.A.%'; -- This indicates that installed GME product level is 12.0
2541
2542 l_dummy NUMBER;
2543
2544
2545 l_api_name CONSTANT VARCHAR2 (30) := 'get_batch_shortages';
2546 l_qoh NUMBER;
2547 l_rqoh NUMBER;
2548 l_qr NUMBER;
2549 l_qs NUMBER;
2550 l_att NUMBER;
2551 l_atr NUMBER;
2552 l_sqoh NUMBER;
2553 l_srqoh NUMBER;
2554 l_sqr NUMBER;
2555 l_sqs NUMBER;
2556 l_satt NUMBER;
2557 l_satr NUMBER;
2558 l_tree_mode NUMBER;
2559 l_unusable_qty NUMBER;
2560 l_open_qty NUMBER;
2561 l_open_qty_prim NUMBER;
2562 l_msg_count NUMBER;
2563 l_msg_data VARCHAR2 (2000);
2564 l_subinventory VARCHAR2 (10);
2565 l_return_status VARCHAR2 (1);
2566 l_mtl_dtl_rec gme_material_details%ROWTYPE;
2567 --FPbug#4912179
2568 --l_item_rec mtl_system_items_kfv%ROWTYPE;
2569 l_item_rec cur_get_item%ROWTYPE;
2570 l_exception_rec gme_exceptions_gtmp%ROWTYPE;
2571 setup_failure EXCEPTION;
2572 get_open_qty_err EXCEPTION;
2573 uom_conv_error EXCEPTION;
2574 exception_ins_err EXCEPTION;
2575 unable_to_query_tree EXCEPTION;
2576 TYPE qty_rec IS RECORD (onhand NUMBER, available NUMBER);
2577 TYPE qty_tab IS TABLE OF qty_rec INDEX BY VARCHAR2(25);
2578 l_qty_tbl qty_tab;
2579 TYPE item_rec IS RECORD (concatenated_segments VARCHAR2(2000), location_control_code NUMBER,
2580 restrict_locators_code NUMBER, primary_uom_code VARCHAR2(3), reservable_type NUMBER);
2581 TYPE item_tab IS TABLE OF item_rec INDEX BY BINARY_INTEGER;
2582 l_item_tbl item_tab;
2583 l_item_hash VARCHAR2(25);
2584 l_mtl_hash VARCHAR2(25);
2585 l_item_onhand NUMBER;
2586 l_item_available NUMBER;
2587 l_mtl_onhand NUMBER;
2588 l_mtl_available NUMBER;
2589 l_temp_qty NUMBER;
2590 l_count NUMBER := 0;
2591 l_allocated_qty NUMBER := 0;
2592 BEGIN
2593 IF (g_debug IS NOT NULL) THEN
2594 gme_debug.log_initialize ('BatchShortages');
2595 END IF;
2596 IF g_debug <= gme_debug.g_log_procedure THEN
2597 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
2598 gme_debug.put_line ('p_tree_mode '||p_tree_mode);
2599 END IF;
2600 x_return_status := fnd_api.g_ret_sts_success;
2601 IF NOT (gme_common_pvt.g_setup_done) THEN
2602 IF NOT (gme_common_pvt.setup (p_org_id => p_organization_id) ) THEN
2603 RAISE setup_failure;
2604 END IF;
2605 END IF;
2606
2607 /* Bug 5256543 If get shortages is called multiple times then records have to be cleared */
2608 DELETE FROM gme_exceptions_gtmp;
2609
2610 OPEN cur_get_materials;
2611 LOOP
2612 /* Bug 5512352 l_exception_rec needs to reset for each material */
2613 l_exception_rec := NULL;
2614 FETCH cur_get_materials INTO l_mtl_dtl_rec;
2615 IF (cur_get_materials%NOTFOUND) THEN
2616 EXIT;
2617 END IF;
2618 BEGIN
2619 l_item_rec.concatenated_segments := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).concatenated_segments;
2620 l_item_rec.location_control_code := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).location_control_code;
2621 l_item_rec.restrict_locators_code := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).restrict_locators_code;
2622 l_item_rec.primary_uom_code := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).primary_uom_code;
2623 l_item_rec.reservable_type := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).reservable_type;
2624 EXCEPTION
2625 WHEN NO_DATA_FOUND THEN
2626 OPEN cur_get_item (l_mtl_dtl_rec.organization_id, l_mtl_dtl_rec.inventory_item_id);
2627 FETCH cur_get_item INTO l_item_tbl(l_mtl_dtl_rec.inventory_item_id).concatenated_segments,
2628 l_item_tbl(l_mtl_dtl_rec.inventory_item_id).location_control_code,
2629 l_item_tbl(l_mtl_dtl_rec.inventory_item_id).restrict_locators_code,
2630 l_item_tbl(l_mtl_dtl_rec.inventory_item_id).primary_uom_code,
2631 l_item_tbl(l_mtl_dtl_rec.inventory_item_id).reservable_type;
2632 CLOSE cur_get_item;
2633 l_item_rec.concatenated_segments := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).concatenated_segments;
2634 l_item_rec.location_control_code := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).location_control_code;
2635 l_item_rec.restrict_locators_code := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).restrict_locators_code;
2636 l_item_rec.primary_uom_code := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).primary_uom_code;
2637 l_item_rec.reservable_type := l_item_tbl(l_mtl_dtl_rec.inventory_item_id).reservable_type;
2638 END;
2639
2640 /* Bug 5441643 Added NVL condition for location control code*/
2641 get_open_qty
2642 (p_mtl_dtl_rec => l_mtl_dtl_rec
2643 ,p_called_by => 'S'
2644 ,p_item_location_control => NVL(l_item_rec.location_control_code,1)
2645 ,p_item_restrict_locators => l_item_rec.restrict_locators_code
2646 ,x_open_qty => l_open_qty
2647 ,x_return_status => l_return_status);
2648 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2649 RAISE get_open_qty_err;
2650 ELSE
2651 IF (l_mtl_dtl_rec.dtl_um = l_item_rec.primary_uom_code) THEN
2652 l_open_qty_prim := l_open_qty;
2653 ELSE
2654 l_temp_qty := inv_convert.inv_um_convert
2655 (item_id => l_mtl_dtl_rec.inventory_item_id
2656 ,PRECISION => gme_common_pvt.g_precision
2657 ,from_quantity => l_open_qty
2658 ,from_unit => l_mtl_dtl_rec.dtl_um
2659 ,to_unit => l_item_rec.primary_uom_code
2660 ,from_name => NULL
2661 ,to_name => NULL);
2662 IF (l_temp_qty < 0) THEN
2663 RAISE uom_conv_error;
2664 ELSE
2665 l_open_qty_prim := l_temp_qty;
2666 END IF;
2667 END IF;
2668 END IF;
2669 IF g_debug <= gme_debug.g_log_statement THEN
2670 gme_debug.put_line ('item = ' || l_item_rec.concatenated_segments);
2671 gme_debug.put_line ('l_open_qty_prim = ' || l_open_qty_prim);
2672 END IF;
2673 IF (l_open_qty_prim > 0) THEN
2674 IF (p_invoke_mode = 'S') THEN
2675 l_subinventory := l_mtl_dtl_rec.subinventory;
2676 ELSE
2677 l_subinventory := NULL;
2678 END IF;
2679 l_item_hash := RPAD(l_mtl_dtl_rec.inventory_item_id, 12, 'X')||'***'||NVL(l_subinventory, '##########');
2680 l_mtl_hash := RPAD(l_mtl_dtl_rec.inventory_item_id, 12, 'X')||NVL(RPAD(l_mtl_dtl_rec.revision, 3, '*'), '***')||NVL(RPAD(l_subinventory, 10, '#'), '##########');
2681 IF g_debug <= gme_debug.g_log_statement THEN
2682 gme_debug.put_line ('l_item_hash = ' || l_item_hash);
2683 gme_debug.put_line ('l_mtl_hash = ' || l_mtl_hash);
2684 END IF;
2685 BEGIN
2686 l_item_onhand := l_qty_tbl(l_item_hash).onhand;
2687 l_item_available := l_qty_tbl(l_item_hash).available;
2688 IF g_debug <= gme_debug.g_log_statement THEN
2689 gme_debug.put_line ('found l_item_onhand = ' || l_item_onhand);
2690 gme_debug.put_line ('found l_item_available = ' || l_item_available);
2691 END IF;
2692 EXCEPTION
2693 WHEN NO_DATA_FOUND THEN
2694 IF p_tree_mode = g_tree_transaction_mode OR l_item_rec.reservable_type = 2 THEN
2695 l_tree_mode := g_tree_transaction_mode;
2696 ELSE
2697 l_tree_mode := g_tree_reservation_mode;
2698 END IF;
2699 gme_transactions_pvt.query_quantities
2700 (x_return_status => l_return_status
2701 ,x_msg_count => l_msg_count
2702 ,x_msg_data => l_msg_data
2703 ,p_organization_id => l_mtl_dtl_rec.organization_id
2704 ,p_inventory_item_id => l_mtl_dtl_rec.inventory_item_id
2705 ,p_tree_mode => l_tree_mode
2706 ,p_grade_code => NULL
2707 ,p_demand_source_header_id => l_mtl_dtl_rec.batch_id
2708 ,p_demand_source_line_id => l_mtl_dtl_rec.material_detail_id
2709 ,p_lot_expiration_date => l_mtl_dtl_rec.material_requirement_date -- 9442109
2710 ,p_revision => NULL
2711 ,p_lot_number => NULL
2712 ,p_subinventory_code => l_subinventory
2713 ,p_locator_id => NULL
2714 ,x_qoh => l_qoh
2715 ,x_rqoh => l_rqoh
2716 ,x_qr => l_qr
2717 ,x_qs => l_qs
2718 ,x_att => l_att
2719 ,x_atr => l_atr
2720 ,x_sqoh => l_sqoh
2721 ,x_srqoh => l_srqoh
2722 ,x_sqr => l_sqr
2723 ,x_sqs => l_sqs
2724 ,x_satt => l_satt
2725 ,x_satr => l_satr);
2726 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
2727 IF g_debug <= gme_debug.g_log_statement THEN
2728 gme_debug.put_line('Item level qtys');
2729 gme_debug.put_line('l_qoh = ' || l_qoh);
2730 gme_debug.put_line('l_att = ' || l_att);
2731 gme_debug.put_line('l_atr = ' || l_atr);
2732 END IF;
2733 l_qty_tbl(l_item_hash).onhand := l_qoh;
2734
2735 -- Bug 13611486 - This backs out fix done in 8582412. INV team must provide a way of
2736 -- accurately getting atr/att for a given trans type i.e. wip issue and consider status.
2737
2738 -- Bug 8582412 - Moved cursor outside of if - we need unusable for both calculations.
2739 OPEN cur_get_onhand (l_mtl_dtl_rec.organization_id, l_mtl_dtl_rec.inventory_item_id, null, null);
2740 FETCH cur_get_onhand INTO l_unusable_qty;
2741 CLOSE cur_get_onhand;
2742
2743 l_unusable_qty := 0; -- Bug 13611486 - This effectively disregards previous fetch.
2744
2745 IF (l_tree_mode = g_tree_transaction_mode) THEN
2746 -- OPEN cur_get_onhand (l_mtl_dtl_rec.organization_id, l_mtl_dtl_rec.inventory_item_id, null, null);
2747 -- FETCH cur_get_onhand INTO l_unusable_qty;
2748 -- CLOSE cur_get_onhand;
2749 -- l_qty_tbl(l_item_hash).available := l_att;
2750 -- l_qty_tbl(l_item_hash).available := l_qty_tbl(l_item_hash).available - NVL(l_unusable_qty,0);
2751 l_qty_tbl(l_item_hash).available := l_att - NVL(l_unusable_qty,0);
2752 ELSE
2753 l_qty_tbl(l_item_hash).available := l_atr - NVL(l_unusable_qty,0);
2754 END IF;
2755 ELSE
2756 RAISE unable_to_query_tree;
2757 END IF;
2758 l_item_onhand := l_qty_tbl(l_item_hash).onhand;
2759 l_item_available := l_qty_tbl(l_item_hash).available;
2760 END;
2761 IF (l_item_hash <> l_mtl_hash) THEN
2762 IF g_debug <= gme_debug.g_log_statement THEN
2763 gme_debug.put_line('Item level and mtl level hashes are diff');
2764 END IF;
2765 BEGIN
2766 l_mtl_onhand := l_qty_tbl(l_mtl_hash).onhand;
2767 l_mtl_available := l_qty_tbl(l_mtl_hash).available;
2768 IF g_debug <= gme_debug.g_log_statement THEN
2769 gme_debug.put_line ('found l_mtl_onhand = ' || l_mtl_onhand);
2770 gme_debug.put_line ('found l_mtl_available = ' || l_mtl_available);
2771 END IF;
2772 EXCEPTION
2773 WHEN NO_DATA_FOUND THEN
2774 IF p_tree_mode = g_tree_transaction_mode OR l_item_rec.reservable_type = 2 THEN
2775 l_tree_mode := g_tree_transaction_mode;
2776 ELSE
2777 l_tree_mode := g_tree_reservation_mode;
2778 END IF;
2779 gme_transactions_pvt.query_quantities
2780 (x_return_status => l_return_status
2781 ,x_msg_count => l_msg_count
2782 ,x_msg_data => l_msg_data
2783 ,p_organization_id => l_mtl_dtl_rec.organization_id
2784 ,p_inventory_item_id => l_mtl_dtl_rec.inventory_item_id
2785 ,p_tree_mode => l_tree_mode
2786 ,p_grade_code => NULL
2787 ,p_demand_source_header_id => l_mtl_dtl_rec.batch_id
2788 ,p_demand_source_line_id => l_mtl_dtl_rec.material_detail_id
2789 ,p_lot_expiration_date => l_mtl_dtl_rec.material_requirement_date -- 9442109
2790 ,p_revision => l_mtl_dtl_rec.revision
2791 ,p_lot_number => NULL
2792 ,p_subinventory_code => l_subinventory
2793 ,p_locator_id => NULL
2794 ,x_qoh => l_qoh
2795 ,x_rqoh => l_rqoh
2796 ,x_qr => l_qr
2797 ,x_qs => l_qs
2798 ,x_att => l_att
2799 ,x_atr => l_atr
2800 ,x_sqoh => l_sqoh
2801 ,x_srqoh => l_srqoh
2802 ,x_sqr => l_sqr
2803 ,x_sqs => l_sqs
2804 ,x_satt => l_satt
2805 ,x_satr => l_satr);
2806 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
2807 IF g_debug <= gme_debug.g_log_statement THEN
2808 gme_debug.put_line('Item/Revision/Sub level qtys');
2809 gme_debug.put_line('l_qoh = ' || l_qoh);
2810 gme_debug.put_line('l_att = ' || l_att);
2811 gme_debug.put_line('l_atr = ' || l_atr);
2812 END IF;
2813 l_qty_tbl(l_mtl_hash).onhand := l_qoh;
2814 IF (l_tree_mode = g_tree_transaction_mode) THEN
2815 l_qty_tbl(l_mtl_hash).available := l_att;
2816
2817 -- Bug 13611486 - This backs out fix done in 8582412. INV team must provide a way of
2818 -- accurately getting atr/att for a given trans type i.e. wip issue and consider status.
2819 OPEN cur_get_onhand (l_mtl_dtl_rec.organization_id
2820 ,l_mtl_dtl_rec.inventory_item_id
2821 ,l_mtl_dtl_rec.subinventory
2822 ,l_mtl_dtl_rec.revision);
2823 FETCH cur_get_onhand INTO l_unusable_qty;
2824 CLOSE cur_get_onhand;
2825
2826 l_unusable_qty := 0; -- Bug 13611486 - This effectively disregards previous fetch.
2827
2828 l_qty_tbl(l_mtl_hash).available := l_qty_tbl(l_mtl_hash).available - NVL(l_unusable_qty,0);
2829 ELSE
2830 l_qty_tbl(l_mtl_hash).available := l_atr;
2831 END IF;
2832 ELSE
2833 RAISE unable_to_query_tree;
2834 END IF;
2835 l_mtl_onhand := l_qty_tbl(l_mtl_hash).onhand;
2836 l_mtl_available := l_qty_tbl(l_mtl_hash).available;
2837 END;
2838 ELSE
2839 IF g_debug <= gme_debug.g_log_statement THEN
2840 gme_debug.put_line('Item level and mtl level hashes are same');
2841 gme_debug.put_line('Invoke mode is '||p_invoke_mode);
2842 gme_debug.put_line('onhand is '||l_item_onhand);
2843 gme_debug.put_line('avail is '||l_item_available);
2844 END IF;
2845 l_mtl_onhand := l_item_onhand;
2846 l_mtl_available := l_item_available;
2847 END IF; /* IF (l_item_hash <> l_mtl_hash) THEN */
2848
2849
2850 /*************************************************************************
2851 Customer bug 7462542 Consider the Manufacturing Order allocations as
2852 part of unavailable inventory.
2853 This issue occurs only at org lvel. So we need to get sum of move order
2854 allocation at org level where no reservation exists.
2855 *************************************************************************/
2856
2857 -- Bug 14488987 - Move orders are already accounted for in qty tree in release 12.1 and above.
2858 -- Leave logic of bug 7462542 in place for release 12.0 only.
2859 OPEN cur_patch_level;
2860 FETCH cur_patch_level
2861 INTO l_dummy;
2862
2863 IF (cur_patch_level%NOTFOUND) THEN
2864 l_dummy := 0;
2865 END IF;
2866
2867 CLOSE cur_patch_level;
2868
2869 IF p_invoke_mode = 'O' and l_dummy = 1 then
2870 BEGIN
2871 SELECT SUM(l.primary_quantity) into l_allocated_qty
2872 FROM mtl_material_transactions_temp t,
2873 mtl_txn_request_lines l,
2874 mtl_txn_request_headers h
2875 WHERE t.move_order_line_id = l.line_id
2876 AND t.move_order_header_id = h.header_id
2877 AND t.organization_id = l_mtl_dtl_rec.organization_id
2878 AND t.inventory_item_id = l_mtl_dtl_rec.inventory_item_id
2879 AND t.reservation_id not in (select reservation_id from mtl_reservations
2880 where demand_source_header_id = l_mtl_dtl_rec.batch_id and
2881 demand_source_line_id = l_mtl_dtl_rec.material_detail_id and
2882 demand_Source_type_id = gme_common_pvt.g_txn_source_type)
2883 AND TRANSACTION_SOURCE_ID NOT IN (l_mtl_dtl_rec.batch_id) --bug 7496141
2884 AND TRX_SOURCE_LINE_ID NOT IN (l_mtl_dtl_rec.material_detail_id) --bug 7496141
2885 AND h.move_order_type = gme_common_pvt.g_txn_source_type
2886 AND l.line_status NOT IN (5, 6)
2887 AND h.header_id = l.header_id
2888 AND h.move_order_type NOT IN
2889 (gme_common_pvt.g_invis_move_order_type
2890 ,inv_globals.g_move_order_put_away);
2891
2892 IF g_debug <= gme_debug.g_log_statement THEN
2893 gme_debug.put_line('Additional Material Allocated to Move Order:'||l_allocated_qty);
2894 END IF;
2895
2896 EXCEPTION WHEN NO_DATA_FOUND THEN
2897 l_allocated_qty := 0;
2898 END;
2899 l_mtl_available := l_mtl_available - nvl(l_allocated_qty,0);
2900 IF g_debug <= gme_debug.g_log_statement THEN
2901 gme_debug.put_line('Material Available before deducting additional Move Order Allocation:'||(l_mtl_available + nvl(l_allocated_qty,0)));
2902 gme_debug.put_line('Material Available after deducting additional Move Order Allocation:'||l_mtl_available );
2903 END IF;
2904
2905 END IF;
2906
2907 IF (l_open_qty_prim > l_mtl_available) THEN
2908 l_exception_rec.organization_id := l_mtl_dtl_rec.organization_id;
2909 l_exception_rec.onhand_qty := l_qoh;
2910 l_exception_rec.material_detail_id := l_mtl_dtl_rec.material_detail_id;
2911 IF l_tree_mode = g_tree_transaction_mode THEN
2912 l_exception_rec.att := l_mtl_available;
2913 ELSE
2914 l_exception_rec.atr := l_mtl_available;
2915 END IF;
2916 IF NOT (insert_exceptions (l_exception_rec) ) THEN
2917 RAISE exception_ins_err;
2918 END IF;
2919 l_count := l_count + 1;
2920 x_exception_tbl (l_count) := l_exception_rec;
2921 END IF;
2922
2923 l_qty_tbl(l_mtl_hash).available := l_qty_tbl(l_mtl_hash).available - l_open_qty_prim;
2924 IF (l_item_hash <> l_mtl_hash) THEN
2925 l_qty_tbl(l_item_hash).available := l_qty_tbl(l_item_hash).available - l_open_qty_prim;
2926 END IF;
2927 END IF; /* IF (l_open_qty_prim > 0) THEN */
2928 END LOOP;
2929 CLOSE cur_get_materials;
2930 IF g_debug <= gme_debug.g_log_procedure THEN
2931 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2932 END IF;
2933 EXCEPTION
2934 WHEN setup_failure THEN
2935 x_return_status := fnd_api.g_ret_sts_error;
2936 WHEN get_open_qty_err OR unable_to_query_tree THEN
2937 x_return_status := l_return_status;
2938 IF (cur_get_materials%ISOPEN) THEN
2939 CLOSE cur_get_materials;
2940 END IF;
2941 WHEN uom_conv_error THEN
2942 x_return_status := fnd_api.g_ret_sts_error;
2943 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
2944 fnd_message.set_token ('ITEM_NO', l_item_rec.concatenated_segments);
2945 fnd_message.set_token ('FROM_UOM', l_mtl_dtl_rec.dtl_um);
2946 fnd_message.set_token ('TO_UOM', l_item_rec.primary_uom_code);
2947 IF (cur_get_materials%ISOPEN) THEN
2948 CLOSE cur_get_materials;
2949 END IF;
2950 fnd_msg_pub.add;
2951 WHEN exception_ins_err THEN
2952 x_return_status := fnd_api.g_ret_sts_unexp_error;
2953 IF (cur_get_materials%ISOPEN) THEN
2954 CLOSE cur_get_materials;
2955 END IF;
2956 WHEN OTHERS THEN
2957 IF g_debug <= gme_debug.g_log_unexpected THEN
2958 gme_debug.put_line ( 'When others exception in '
2959 || g_pkg_name
2960 || '.'
2961 || l_api_name
2962 || ' Error is '
2963 || SQLERRM);
2964 END IF;
2965 IF (cur_get_materials%ISOPEN) THEN
2966 CLOSE cur_get_materials;
2967 END IF;
2968 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2969 x_return_status := fnd_api.g_ret_sts_unexp_error;
2970 END get_batch_shortages;
2971
2972 PROCEDURE get_open_qty (
2973 p_mtl_dtl_rec IN gme_material_details%ROWTYPE
2974 ,p_called_by IN VARCHAR2
2975 , /* P- picking, R-reservation, S-shortages, Z-from Auto-Detail line */
2976 p_item_location_control IN NUMBER DEFAULT NULL
2977 ,p_item_restrict_locators IN NUMBER DEFAULT NULL
2978 ,x_open_qty OUT NOCOPY NUMBER
2979 ,x_return_status OUT NOCOPY VARCHAR2)
2980 IS
2981 l_api_name CONSTANT VARCHAR2 (30) := 'get_open_qty';
2982 l_return_status VARCHAR2 (1);
2983 l_reserved_qty NUMBER := 0;
2984 l_mo_line_qty NUMBER := 0;
2985 l_temp_qty NUMBER := 0;
2986 /* Bug 5441643 Added NVL condition for location control code*/
2987 CURSOR cur_item_controls (v_org_id NUMBER, v_inventory_item_id NUMBER)
2988 IS
2989 SELECT NVL(location_control_code,1), restrict_locators_code
2990 FROM mtl_system_items_b
2991 WHERE organization_id = v_org_id
2992 AND inventory_item_id = v_inventory_item_id;
2993
2994 l_item_loc_control NUMBER;
2995 l_item_restrict_locators NUMBER;
2996 l_resv_tbl gme_common_pvt.reservations_tab;
2997 get_reserved_qty_err EXCEPTION;
2998 get_reservations_err EXCEPTION;
2999 get_pending_qty_err EXCEPTION;
3000 BEGIN
3001 IF g_debug <= gme_debug.g_log_procedure THEN
3002 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3003 || l_api_name);
3004 gme_debug.put_line ('Being called by ' || p_called_by);
3005 END IF;
3006
3007 x_return_status := fnd_api.g_ret_sts_success;
3008
3009 -- All qty should be in the detail line uom.
3010 -- all allocations excludes the invisible move orders
3011 IF p_called_by = 'S' THEN
3012 gme_reservations_pvt.get_reserved_qty
3013 (p_mtl_dtl_rec => p_mtl_dtl_rec
3014 ,x_reserved_qty => l_reserved_qty
3015 ,x_return_status => l_return_status);
3016
3017 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3018 RAISE get_reserved_qty_err;
3019 END IF;
3020
3021 x_open_qty :=
3022 NVL (p_mtl_dtl_rec.wip_plan_qty, p_mtl_dtl_rec.plan_qty)
3023 - (p_mtl_dtl_rec.actual_qty + NVL (l_reserved_qty, 0) );
3024
3025 IF g_debug <= gme_debug.g_log_statement THEN
3026 gme_debug.put_line ( g_pkg_name
3027 || '.'
3028 || l_api_name
3029 || ' Calculated open qty for shortages is '
3030 || x_open_qty);
3031 END IF;
3032 ELSIF p_called_by = 'P' THEN
3033 -- Bug 12613813 - pass in true for p_locator_only parameter so picking can consider locator also.
3034 gme_reservations_pvt.get_reserved_qty
3035 (p_mtl_dtl_rec => p_mtl_dtl_rec
3036 ,p_supply_sub_only => fnd_api.g_true
3037 ,p_locator_only => fnd_api.g_true
3038 ,x_reserved_qty => l_reserved_qty
3039 ,x_return_status => l_return_status);
3040
3041 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3042 RAISE get_reserved_qty_err;
3043 END IF;
3044
3045 gme_move_orders_pvt.get_pending_move_order_qty
3046 (p_mtl_dtl_rec => p_mtl_dtl_rec
3047 ,x_pending_qty => l_mo_line_qty
3048 ,x_return_status => l_return_status);
3049
3050 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3051 RAISE get_pending_qty_err;
3052 END IF;
3053
3054 IF g_debug <= gme_debug.g_log_statement THEN
3055 gme_debug.put_line ( g_pkg_name
3056 || '.'
3057 || l_api_name
3058 || ' p_mtl_dtl_rec.plan_qty = '
3059 || p_mtl_dtl_rec.plan_qty);
3060 gme_debug.put_line ( g_pkg_name
3061 || '.'
3062 || l_api_name
3063 || ' p_mtl_dtl_rec.wip_plan_qty = '
3064 || p_mtl_dtl_rec.wip_plan_qty);
3065 gme_debug.put_line ( g_pkg_name
3066 || '.'
3067 || l_api_name
3068 || ' p_mtl_dtl_rec.actual_qty = '
3069 || p_mtl_dtl_rec.actual_qty);
3070 gme_debug.put_line ( g_pkg_name
3071 || '.'
3072 || l_api_name
3073 || ' l_reserved_qty = '
3074 || NVL (l_reserved_qty, 0) );
3075 gme_debug.put_line ( g_pkg_name
3076 || '.'
3077 || l_api_name
3078 || ' p_mtl_dtl_rec.backordered_qty = '
3079 || NVL (p_mtl_dtl_rec.backordered_qty, 0) );
3080 gme_debug.put_line ( g_pkg_name
3081 || '.'
3082 || l_api_name
3083 || ' l_mo_line_qty = '
3084 || NVL (l_mo_line_qty, 0) );
3085 END IF;
3086
3087 -- x_open_qty := NVL(p_mtl_dtl_rec.wip_plan_qty, p_mtl_dtl_rec.plan_qty)
3088 -- - (p_mtl_dtl_rec.actual_qty + NVL(l_reserved_qty,0) - NVL(p_mtl_dtl_rec.backordered_qty,0) + NVL(l_mo_line_qty,0));
3089 --bUG#5311713 Rounded the wip_plan_qty and plan_qty to 5 decimal places.
3090 x_open_qty :=
3091 ROUND(NVL (p_mtl_dtl_rec.wip_plan_qty, p_mtl_dtl_rec.plan_qty),5)
3092 - ( p_mtl_dtl_rec.actual_qty
3093 + NVL (l_reserved_qty, 0)
3094 + NVL (l_mo_line_qty, 0) );
3095
3096 IF g_debug <= gme_debug.g_log_statement THEN
3097 gme_debug.put_line ( g_pkg_name
3098 || '.'
3099 || l_api_name
3100 || ' Calculated open qty for picking is '
3101 || x_open_qty);
3102 END IF;
3103 ELSIF p_called_by = 'R' THEN
3104 gme_reservations_pvt.get_material_reservations
3105 (p_organization_id => p_mtl_dtl_rec.organization_id
3106 ,p_batch_id => p_mtl_dtl_rec.batch_id
3107 ,p_material_detail_id => p_mtl_dtl_rec.material_detail_id
3108 ,x_return_status => l_return_status
3109 ,x_reservations_tbl => l_resv_tbl);
3110
3111 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3112 RAISE get_reservations_err;
3113 END IF;
3114
3115 FOR i IN 1 .. l_resv_tbl.COUNT LOOP
3116 IF ( p_item_location_control IS NULL
3117 OR p_item_restrict_locators IS NULL) THEN
3118 OPEN cur_item_controls (p_mtl_dtl_rec.organization_id
3119 ,p_mtl_dtl_rec.inventory_item_id);
3120
3121 FETCH cur_item_controls
3122 INTO l_item_loc_control, l_item_restrict_locators;
3123
3124 CLOSE cur_item_controls;
3125 ELSE
3126 l_item_loc_control := p_item_location_control;
3127 l_item_restrict_locators := p_item_restrict_locators;
3128 END IF;
3129
3130 -- Assess fully detailed (1) and part detailed (2) reservations
3131 /*Bug#7346653 commenting the below if condition as the open qty
3132 should not include the the already created high level reservation as this is
3133 causing the extra HLR to be created from the gme_reservations_pvt.auto_detail_line */
3134 /* IF (gme_reservations_pvt.reservation_fully_specified
3135 (p_reservation_rec => l_resv_tbl (i)
3136 ,p_item_location_control => l_item_loc_control
3137 ,p_item_restrict_locators => l_item_restrict_locators) in (1,2)) THEN */
3138
3139 gme_reservations_pvt.get_reservation_dtl_qty
3140 (p_reservation_rec => l_resv_tbl
3141 (i)
3142 ,p_uom_code => p_mtl_dtl_rec.dtl_um
3143 ,x_qty => l_temp_qty
3144 ,x_return_status => l_return_status);
3145
3146 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3147 RAISE get_reserved_qty_err;
3148 END IF;
3149 IF g_debug <= gme_debug.g_log_statement THEN
3150 gme_debug.put_line ( g_pkg_name
3151 || '.'
3152 || l_api_name
3153 || ' get_reservation_dtl_qty returns qty of '
3154 || l_temp_qty);
3155 END IF;
3156
3157 l_reserved_qty := NVL (l_reserved_qty, 0) + NVL (l_temp_qty, 0);
3158 IF g_debug <= gme_debug.g_log_statement THEN
3159 gme_debug.put_line ( g_pkg_name
3160 || '.'
3161 || l_api_name
3162 || ' so total reserved qty computes to '
3163 || l_reserved_qty);
3164 END IF;
3165
3166
3167 -- END IF;
3168 END LOOP;
3169 IF g_debug <= gme_debug.g_log_statement THEN
3170 gme_debug.put_line ( g_pkg_name
3171 || '.'
3172 || l_api_name
3173 || ' wip_plan_qty => '
3174 || p_mtl_dtl_rec.wip_plan_qty);
3175 gme_debug.put_line ( g_pkg_name
3176 || '.'
3177 || l_api_name
3178 || ' plan_qty => '
3179 || p_mtl_dtl_rec.plan_qty);
3180 gme_debug.put_line ( g_pkg_name
3181 || '.'
3182 || l_api_name
3183 || ' actual_qty => '
3184 || p_mtl_dtl_rec.actual_qty);
3185 gme_debug.put_line ( g_pkg_name
3186 || '.'
3187 || l_api_name
3188 || ' reserved_qty => '
3189 || l_reserved_qty);
3190 END IF;
3191
3192 x_open_qty :=
3193 NVL (p_mtl_dtl_rec.wip_plan_qty, p_mtl_dtl_rec.plan_qty)
3194 - (p_mtl_dtl_rec.actual_qty + l_reserved_qty);
3195
3196 IF g_debug <= gme_debug.g_log_statement THEN
3197 gme_debug.put_line
3198 ( g_pkg_name
3199 || '.'
3200 || l_api_name
3201 || ' Calculated open qty for reservations is '
3202 || x_open_qty);
3203 END IF;
3204 /* Added below ELSIF in bug 9924437*/
3205 ELSIF p_called_by = 'Z' THEN
3206 gme_reservations_pvt.get_material_res
3207 (p_organization_id => p_mtl_dtl_rec.organization_id
3208 ,p_batch_id => p_mtl_dtl_rec.batch_id
3209 ,p_material_detail_id => p_mtl_dtl_rec.material_detail_id
3210 ,x_return_status => l_return_status
3211 ,x_reservations_tbl => l_resv_tbl);
3212
3213 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3214 RAISE get_reservations_err;
3215 END IF;
3216
3217 FOR i IN 1 .. l_resv_tbl.COUNT LOOP
3218 IF ( p_item_location_control IS NULL
3219 OR p_item_restrict_locators IS NULL) THEN
3220 OPEN cur_item_controls (p_mtl_dtl_rec.organization_id
3221 ,p_mtl_dtl_rec.inventory_item_id);
3222
3223 FETCH cur_item_controls
3224 INTO l_item_loc_control, l_item_restrict_locators;
3225
3226 CLOSE cur_item_controls;
3227 ELSE
3228 l_item_loc_control := p_item_location_control;
3229 l_item_restrict_locators := p_item_restrict_locators;
3230 END IF;
3231
3232 gme_reservations_pvt.get_reservation_dtl_qty
3233 (p_reservation_rec => l_resv_tbl
3234 (i)
3235 ,p_uom_code => p_mtl_dtl_rec.dtl_um
3236 ,x_qty => l_temp_qty
3237 ,x_return_status => l_return_status);
3238
3239 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3240 RAISE get_reserved_qty_err;
3241 END IF;
3242 IF g_debug <= gme_debug.g_log_statement THEN
3243 gme_debug.put_line ( g_pkg_name
3244 || '.'
3245 || l_api_name
3246 || ' get_reservation_dtl_qty returns qty of '
3247 || l_temp_qty);
3248 END IF;
3249
3250 l_reserved_qty := NVL (l_reserved_qty, 0) + NVL (l_temp_qty, 0);
3251 IF g_debug <= gme_debug.g_log_statement THEN
3252 gme_debug.put_line ( g_pkg_name
3253 || '.'
3254 || l_api_name
3255 || ' so total reserved qty computes to '
3256 || l_reserved_qty);
3257 END IF;
3258
3259 END LOOP;
3260 IF g_debug <= gme_debug.g_log_statement THEN
3261 gme_debug.put_line ( g_pkg_name
3262 || '.'
3263 || l_api_name
3264 || ' wip_plan_qty => '
3265 || p_mtl_dtl_rec.wip_plan_qty);
3266 gme_debug.put_line ( g_pkg_name
3267 || '.'
3268 || l_api_name
3269 || ' plan_qty => '
3270 || p_mtl_dtl_rec.plan_qty);
3271 gme_debug.put_line ( g_pkg_name
3272 || '.'
3273 || l_api_name
3274 || ' actual_qty => '
3275 || p_mtl_dtl_rec.actual_qty);
3276 gme_debug.put_line ( g_pkg_name
3277 || '.'
3278 || l_api_name
3279 || ' reserved_qty => '
3280 || l_reserved_qty);
3281 END IF;
3282
3283 x_open_qty :=
3284 NVL (p_mtl_dtl_rec.wip_plan_qty, p_mtl_dtl_rec.plan_qty)
3285 - (p_mtl_dtl_rec.actual_qty + l_reserved_qty);
3286
3287 IF g_debug <= gme_debug.g_log_statement THEN
3288 gme_debug.put_line
3289 ( g_pkg_name
3290 || '.'
3291 || l_api_name
3292 || ' Calculated open qty for reservations is '
3293 || x_open_qty);
3294 END IF;
3295 /*End of Bug No.9924437 */
3296 ELSE
3297 x_open_qty := 0;
3298 END IF;
3299
3300 IF g_debug <= gme_debug.g_log_procedure THEN
3301 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3302 END IF;
3303 EXCEPTION
3304 WHEN get_reserved_qty_err OR get_pending_qty_err THEN
3305 x_return_status := l_return_status;
3306 WHEN OTHERS THEN
3307 IF g_debug <= gme_debug.g_log_unexpected THEN
3308 gme_debug.put_line ( 'When others exception in '
3309 || g_pkg_name
3310 || '.'
3311 || l_api_name
3312 || ' Error is '
3313 || SQLERRM);
3314 END IF;
3315
3316 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3317 x_return_status := fnd_api.g_ret_sts_unexp_error;
3318 END get_open_qty;
3319
3320 FUNCTION insert_exceptions (p_exception_rec IN gme_exceptions_gtmp%ROWTYPE)
3321 RETURN BOOLEAN
3322 IS
3323 l_api_name CONSTANT VARCHAR2 (30) := 'insert_exceptions';
3324 BEGIN
3325 IF g_debug <= gme_debug.g_log_procedure THEN
3326 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3327 || l_api_name);
3328 END IF;
3329
3330 -- Bug 9666304 - NVL on att variable.
3331 INSERT INTO gme_exceptions_gtmp
3332 (organization_id
3333 ,pending_move_order_ind
3334 ,pending_reservations_ind
3335 ,onhand_qty, att
3336 ,atr, material_detail_id
3337 ,transacted_qty
3338 ,exception_qty, batch_id)
3339 VALUES (p_exception_rec.organization_id
3340 ,p_exception_rec.pending_move_order_ind
3341 ,p_exception_rec.pending_reservations_ind
3342 ,p_exception_rec.onhand_qty, NVL(p_exception_rec.att, 0)
3343 ,p_exception_rec.atr, p_exception_rec.material_detail_id
3344 ,p_exception_rec.transacted_qty
3345 ,p_exception_rec.exception_qty, p_exception_rec.batch_id);
3346
3347 IF g_debug <= gme_debug.g_log_procedure THEN
3348 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3349 END IF;
3350
3351 RETURN TRUE;
3352 EXCEPTION
3353 WHEN OTHERS THEN
3354 IF g_debug <= gme_debug.g_log_unexpected THEN
3355 gme_debug.put_line ( 'When others exception in '
3356 || g_pkg_name
3357 || '.'
3358 || l_api_name
3359 || ' Error is '
3360 || SQLERRM);
3361 END IF;
3362
3363 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3364 RETURN FALSE;
3365 END insert_exceptions;
3366
3367 FUNCTION populate_temp_from_struct (
3368 p_exception_tbl IN gme_common_pvt.exceptions_tab)
3369 RETURN BOOLEAN
3370 IS
3371 l_api_name CONSTANT VARCHAR2 (30) := 'populate_temp_from_struct';
3372 BEGIN
3373 IF g_debug <= gme_debug.g_log_procedure THEN
3374 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3375 || l_api_name);
3376 END IF;
3377
3378 FOR i IN 1 .. p_exception_tbl.COUNT LOOP
3379 IF NOT (insert_exceptions (p_exception_tbl (i) ) ) THEN
3380 RETURN FALSE;
3381 END IF;
3382 END LOOP;
3383
3384 IF g_debug <= gme_debug.g_log_procedure THEN
3385 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
3386 END IF;
3387
3388 RETURN TRUE;
3389 EXCEPTION
3390 WHEN OTHERS THEN
3391 IF g_debug <= gme_debug.g_log_unexpected THEN
3392 gme_debug.put_line ( 'When others exception in '
3393 || g_pkg_name
3394 || '.'
3395 || l_api_name
3396 || ' Error is '
3397 || SQLERRM);
3398 END IF;
3399
3400 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3401 RETURN FALSE;
3402 END populate_temp_from_struct;
3403
3404 FUNCTION is_material_assoc_to_step (
3405 p_material_detail_id IN gme_material_details.material_detail_id%TYPE)
3406 RETURN BOOLEAN
3407 IS
3408 l_assoc_count NUMBER;
3409 l_is_assoc BOOLEAN;
3410 l_api_name CONSTANT VARCHAR2 (30) := 'is_material_assoc_to_step';
3411 BEGIN
3412 IF g_debug <= gme_debug.g_log_procedure THEN
3413 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3414 || l_api_name);
3415 END IF;
3416
3417 SELECT COUNT (1)
3418 INTO l_assoc_count
3419 FROM DUAL
3420 WHERE EXISTS (SELECT 1
3421 FROM gme_batch_step_items
3422 WHERE material_detail_id = p_material_detail_id);
3423
3424 IF l_assoc_count = 0 THEN
3425 l_is_assoc := FALSE;
3426 ELSE
3427 l_is_assoc := TRUE;
3428 END IF;
3429
3430 RETURN l_is_assoc;
3431 EXCEPTION
3432 WHEN OTHERS THEN
3433 IF g_debug <= gme_debug.g_log_unexpected THEN
3434 gme_debug.put_line ( 'When others exception in '
3435 || g_pkg_name
3436 || '.'
3437 || l_api_name
3438 || ' Error is '
3439 || SQLERRM);
3440 END IF;
3441
3442 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3443 l_is_assoc := FALSE;
3444 RETURN l_is_assoc;
3445 END is_material_assoc_to_step;
3446
3447 FUNCTION get_assoc_step (
3448 p_material_detail_id IN gme_material_details.material_detail_id%TYPE
3449 ,x_batchstep_id OUT NOCOPY NUMBER
3450 ,x_batchstep_status OUT NOCOPY NUMBER)
3451 RETURN BOOLEAN
3452 IS
3453 l_api_name CONSTANT VARCHAR2 (30) := 'get_assoc_step';
3454 no_assoc_step EXCEPTION;
3455
3456 CURSOR get_assoc_step (v_material_detail_id IN NUMBER)
3457 IS
3458 SELECT a.batchstep_id, s.step_status
3459 FROM gme_batch_step_items a, gme_batch_steps s
3460 WHERE a.material_detail_id = v_material_detail_id
3461 AND a.batchstep_id = s.batchstep_id
3462 AND a.batch_id = s.batch_id;
3463 BEGIN
3464 IF g_debug <= gme_debug.g_log_procedure THEN
3465 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3466 || l_api_name);
3467 END IF;
3468
3469 OPEN get_assoc_step (p_material_detail_id);
3470
3471 FETCH get_assoc_step
3472 INTO x_batchstep_id, x_batchstep_status;
3473
3474 IF get_assoc_step%NOTFOUND THEN
3475 CLOSE get_assoc_step;
3476
3477 RAISE no_assoc_step;
3478 END IF;
3479
3480 CLOSE get_assoc_step;
3481
3482 RETURN TRUE;
3483 EXCEPTION
3484 WHEN no_assoc_step THEN
3485 IF g_debug <= gme_debug.g_log_unexpected THEN
3486 gme_debug.put_line ( 'no associate steps '
3487 || g_pkg_name
3488 || '.'
3489 || l_api_name
3490 || ' Error is '
3491 || SQLERRM);
3492 END IF;
3493 --FPBug#4585491 commented out the following line
3494 --fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3495 RETURN FALSE;
3496 WHEN OTHERS THEN
3497 IF g_debug <= gme_debug.g_log_unexpected THEN
3498 gme_debug.put_line ( 'When others exception in '
3499 || g_pkg_name
3500 || '.'
3501 || l_api_name
3502 || ' Error is '
3503 || SQLERRM);
3504 END IF;
3505
3506 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3507 RETURN FALSE;
3508 END get_assoc_step;
3509
3510 FUNCTION get_batch_header (
3511 p_batch_header_rec IN gme_batch_header%ROWTYPE
3512 ,p_org_code IN VARCHAR2
3513 ,p_batch_type IN NUMBER
3514 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE)
3515 RETURN BOOLEAN
3516 IS
3517 CURSOR get_organization (p_org_code IN VARCHAR2)
3518 IS
3519 SELECT organization_id
3520 FROM mtl_parameters
3521 WHERE organization_code = p_org_code;
3522
3523 l_batch_header_rec gme_batch_header%ROWTYPE;
3524 l_api_name VARCHAR2 (30) := 'GET_BATCH_HEADER';
3525 BEGIN
3526 IF g_debug <= gme_debug.g_log_procedure THEN
3527 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3528 || l_api_name);
3529 END IF;
3530
3531 l_batch_header_rec := p_batch_header_rec;
3532
3533 IF p_batch_header_rec.batch_id IS NULL THEN
3534 IF p_org_code IS NULL AND p_batch_header_rec.organization_id IS NULL THEN
3535 gme_common_pvt.log_message ('GME_NO_KEYS'
3536 ,'TABLE_NAME'
3537 ,'GME_BATCH_HEADER');
3538 RAISE fnd_api.g_exc_error;
3539 ELSIF p_org_code IS NOT NULL THEN
3540 OPEN get_organization (p_org_code);
3541
3542 FETCH get_organization
3543 INTO l_batch_header_rec.organization_id;
3544
3545 IF get_organization%NOTFOUND THEN
3546 CLOSE get_organization;
3547 gme_common_pvt.log_message (p_message_code => 'IC_ORGNCODERR'
3548 ,p_product_code => 'GMI');
3549 RAISE fnd_api.g_exc_error;
3550 END IF;
3551 CLOSE get_organization;
3552 END IF;
3553
3554 IF NVL (p_batch_header_rec.batch_type, p_batch_type) <> p_batch_type THEN
3555 gme_common_pvt.log_message ('GME_INVALID_BATCH_TYPE');
3556 RAISE fnd_api.g_exc_error;
3557 ELSE
3558 l_batch_header_rec.batch_type :=
3559 NVL (p_batch_header_rec.batch_type, p_batch_type);
3560 END IF;
3561 END IF;
3562
3563 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
3564 ,x_batch_header_rec) ) THEN
3565 RAISE fnd_api.g_exc_error;
3566 END IF;
3567
3568 IF x_batch_header_rec.batch_type <> p_batch_type THEN
3569 gme_common_pvt.log_message ('GME_INVALID_BATCH_TYPE');
3570 RAISE fnd_api.g_exc_error;
3571 END IF;
3572
3573 RETURN TRUE;
3574 EXCEPTION
3575 WHEN fnd_api.g_exc_error THEN
3576 IF g_debug <= gme_debug.g_log_error THEN
3577 gme_debug.put_line ( 'Expected error '
3578 || g_pkg_name
3579 || '.'
3580 || l_api_name);
3581 END IF;
3582
3583 RETURN FALSE;
3584 WHEN OTHERS THEN
3585 IF g_debug <= gme_debug.g_log_unexpected THEN
3586 gme_debug.put_line ( 'When others exception in '
3587 || g_pkg_name
3588 || '.'
3589 || l_api_name
3590 || ' Error is '
3591 || SQLERRM);
3592 END IF;
3593
3594 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3595 RETURN FALSE;
3596 END get_batch_header;
3597
3598 FUNCTION get_batch_step (
3599 p_batch_step_rec IN gme_batch_steps%ROWTYPE
3600 ,p_org_code IN VARCHAR2
3601 ,p_batch_no IN VARCHAR2
3602 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
3603 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE)
3604 RETURN BOOLEAN
3605 IS
3606
3607 l_batch_header_rec_in gme_batch_header%ROWTYPE;
3608 l_batch_header_rec gme_batch_header%ROWTYPE;
3609 l_batch_step_rec gme_batch_steps%ROWTYPE;
3610 l_api_name VARCHAR2 (30) := 'GET_BATCH_STEP';
3611
3612 BEGIN
3613 IF g_debug <= gme_debug.g_log_procedure THEN
3614 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3615 || l_api_name);
3616 END IF;
3617 l_batch_step_rec := p_batch_step_rec;
3618 IF p_batch_step_rec.batchstep_id IS NULL THEN
3619 l_batch_header_rec_in.batch_no := p_batch_no;
3620 l_batch_header_rec_in.batch_id := p_batch_step_rec.batch_id;
3621 l_batch_header_rec_in.batch_type := gme_common_pvt.g_doc_type_batch;
3622 IF NOT gme_common_pvt.get_batch_header (
3623 p_batch_header_rec => l_batch_header_rec_in
3624 ,p_org_code => p_org_code
3625 ,p_batch_type => gme_common_pvt.g_doc_type_batch
3626 ,x_batch_header_rec => l_batch_header_rec) THEN
3627 RETURN FALSE;
3628 ELSE
3629 l_batch_step_rec.batch_id := l_batch_header_rec.batch_id;
3630 END IF;
3631 END IF;
3632
3633 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step_rec
3634 ,l_batch_step_rec) ) THEN
3635 RAISE fnd_api.g_exc_error;
3636 END IF;
3637
3638 l_batch_header_rec.batch_id := l_batch_step_rec.batch_id;
3639 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
3640 ,l_batch_header_rec) ) THEN
3641 RAISE fnd_api.g_exc_error;
3642 END IF;
3643
3644 x_batch_header_rec := l_batch_header_rec;
3645 x_batch_step_rec := l_batch_step_rec;
3646 RETURN TRUE;
3647 EXCEPTION
3648 WHEN fnd_api.g_exc_error THEN
3649 IF g_debug <= gme_debug.g_log_error THEN
3650 gme_debug.put_line ( 'Expected error '
3651 || g_pkg_name
3652 || '.'
3653 || l_api_name);
3654 END IF;
3655
3656 RETURN FALSE;
3657 WHEN OTHERS THEN
3658 IF g_debug <= gme_debug.g_log_unexpected THEN
3659 gme_debug.put_line ( 'When others exception in '
3660 || g_pkg_name
3661 || '.'
3662 || l_api_name
3663 || ' Error is '
3664 || SQLERRM);
3665 END IF;
3666
3667 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3668 RETURN FALSE;
3669 END get_batch_step;
3670
3671 FUNCTION create_history (
3672 p_batch_header_rec IN gme_batch_header%ROWTYPE
3673 ,p_original_status IN NUMBER
3674 ,p_event_id IN NUMBER DEFAULT NULL)
3675 RETURN BOOLEAN
3676 IS
3677 l_ins_history gme_batch_history%ROWTYPE;
3678 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_HISTORY';
3679 BEGIN
3680 IF g_debug <= gme_debug.g_log_procedure THEN
3681 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3682 || l_api_name);
3683 END IF;
3684
3685 l_ins_history.batch_id := p_batch_header_rec.batch_id;
3686 l_ins_history.orig_status := p_original_status;
3687 l_ins_history.new_status := p_batch_header_rec.batch_status;
3688 l_ins_history.event_id := p_event_id;
3689 l_ins_history.gl_posted_ind := 0;
3690
3691 IF NOT (gme_batch_history_dbl.insert_row (l_ins_history, l_ins_history) ) THEN
3692 RETURN FALSE;
3693 ELSE
3694 RETURN TRUE;
3695 END IF;
3696 EXCEPTION
3697 WHEN OTHERS THEN
3698 IF g_debug <= gme_debug.g_log_unexpected THEN
3699 gme_debug.put_line ( 'When others exception in '
3700 || g_pkg_name
3701 || '.'
3702 || l_api_name
3703 || ' Error is '
3704 || SQLERRM);
3705 END IF;
3706
3707 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3708 RETURN FALSE;
3709 END create_history;
3710
3711 FUNCTION close_period_check_flexible (
3712 p_org_id IN NUMBER
3713 ,p_trans_date IN DATE
3714 ,x_trans_date OUT NOCOPY DATE
3715 ,x_period_id OUT NOCOPY INTEGER)
3716 RETURN BOOLEAN
3717 IS
3718 l_period_id INTEGER;
3719 l_open_past_period BOOLEAN;
3720 l_api_name VARCHAR2 (100) := 'close_period_check_flexible';
3721 BEGIN
3722 IF g_debug <= gme_debug.g_log_procedure THEN
3723 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
3724 || l_api_name);
3725 END IF;
3726
3727 invttmtx.tdatechk (org_id => p_org_id
3728 ,transaction_date => p_trans_date
3729 ,period_id => l_period_id
3730 ,open_past_period => l_open_past_period);
3731
3732 IF l_open_past_period = FALSE THEN
3733 invttmtx.tdatechk (org_id => p_org_id
3734 ,transaction_date => gme_common_pvt.g_timestamp
3735 ,period_id => l_period_id
3736 ,open_past_period => l_open_past_period);
3737
3738 IF l_open_past_period = FALSE THEN
3739 RETURN FALSE;
3740 ELSE
3741 x_trans_date := gme_common_pvt.g_timestamp;
3742 x_period_id := l_period_id;
3743 END IF;
3744 ELSE
3745 x_trans_date := p_trans_date;
3746 x_period_id := l_period_id;
3747 END IF;
3748
3749 RETURN TRUE;
3750 EXCEPTION
3751 WHEN OTHERS THEN
3752 IF g_debug <= gme_debug.g_log_unexpected THEN
3753 gme_debug.put_line ( 'When others exception in '
3754 || g_pkg_name
3755 || '.'
3756 || l_api_name
3757 || ' Error is '
3758 || SQLERRM);
3759 END IF;
3760
3761 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3762 RETURN FALSE;
3763 END close_period_check_flexible;
3764
3765 FUNCTION get_batchstep_rsrc (
3766 p_batchstep_rsrc_rec IN gme_batch_step_resources%ROWTYPE
3767 ,p_org_code IN VARCHAR2
3768 ,p_batch_no IN VARCHAR2
3769 ,p_batchstep_no IN NUMBER
3770 ,p_activity IN VARCHAR2
3771 ,p_resource IN VARCHAR2
3772 ,x_batchstep_rsrc_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE)
3773 RETURN BOOLEAN
3774 IS
3775 CURSOR get_organization (l_org_code IN VARCHAR2)
3776 IS
3777 SELECT organization_id
3778 FROM mtl_parameters
3779 WHERE organization_code = p_org_code;
3780
3781 CURSOR get_batch_id (l_org_id IN NUMBER, l_batch_no IN VARCHAR2)
3782 IS
3783 SELECT batch_id
3784 FROM gme_batch_header
3785 WHERE organization_id = l_org_id AND batch_no = l_batch_no;
3786
3787 CURSOR get_batchstep_id (l_batch_id IN NUMBER, l_batchstep_no IN NUMBER)
3788 IS
3789 SELECT batchstep_id
3790 FROM gme_batch_steps
3791 WHERE batch_id = l_batch_id AND batchstep_no = l_batchstep_no;
3792
3793 /* Join of gme_batch_step_resources and
3794 gme_batch_step_activities to get unique BATCHSTEP_RESOURCE_ID */
3795 CURSOR gme_batchstep_resource_id (
3796 l_organization_id IN NUMBER
3797 ,l_batch_id IN NUMBER
3798 ,l_batchstep_id IN NUMBER
3799 ,l_activity IN VARCHAR2
3800 ,l_resource IN VARCHAR2)
3801 IS
3802 SELECT gbsr.batchstep_resource_id
3803 FROM gme_batch_step_resources gbsr
3804 ,gme_batch_step_activities gbsa
3805 WHERE gbsr.organization_id = l_organization_id
3806 AND gbsr.batch_id = l_batch_id
3807 AND gbsr.batchstep_id = l_batchstep_id
3808 AND gbsa.batch_id = l_batch_id
3809 AND gbsa.batchstep_id = l_batchstep_id
3810 AND gbsa.activity = l_activity
3811 AND gbsr.batchstep_activity_id = gbsa.batchstep_activity_id
3812 AND gbsr.resources = l_resource;
3813
3814 CURSOR c_gbsr (l_batchstep_resource_id IN NUMBER)
3815 IS
3816 SELECT *
3817 FROM gme_batch_step_resources
3818 WHERE batchstep_resource_id = l_batchstep_resource_id;
3819
3820 l_api_name CONSTANT VARCHAR2 (30) := 'GET_BATCHSTEP_RSRC';
3821 l_organization_id NUMBER;
3822 l_batch_id NUMBER;
3823 l_batchstep_id NUMBER;
3824 l_batchstep_activity_id NUMBER;
3825 l_batchstep_resource_id NUMBER;
3826 btchstep_rsrc_fetch_err EXCEPTION;
3827 invalid_organization EXCEPTION;
3828 invalid_batch EXCEPTION;
3829 invalid_batchstep EXCEPTION;
3830 invalid_batchstep_activity EXCEPTION;
3831 invalid_batchstep_resource EXCEPTION;
3832 invalid_record EXCEPTION;
3833 BEGIN
3834 IF g_debug <= gme_debug.g_log_procedure THEN
3835 gme_debug.put_line ( 'Entering FUNCTION '
3836 || g_pkg_name
3837 || '.'
3838 || l_api_name);
3839 gme_debug.put_line ( g_pkg_name
3840 || '.'
3841 || l_api_name
3842 || ' input organization_id => '
3843 || p_batchstep_rsrc_rec.organization_id);
3844 gme_debug.put_line ( g_pkg_name
3845 || '.'
3846 || l_api_name
3847 || ' input batchstep rsrc id => '
3848 || p_batchstep_rsrc_rec.organization_id);
3849 gme_debug.put_line ( g_pkg_name
3850 || '.'
3851 || l_api_name
3852 || ' input org_code => '
3853 || p_org_code);
3854 gme_debug.put_line ( g_pkg_name
3855 || '.'
3856 || l_api_name
3857 || ' input batch_no => '
3858 || p_batch_no);
3859 gme_debug.put_line ( g_pkg_name
3860 || '.'
3861 || l_api_name
3862 || ' input batchstep_no => '
3863 || p_batchstep_no);
3864 gme_debug.put_line ( g_pkg_name
3865 || '.'
3866 || l_api_name
3867 || ' input activity => '
3868 || p_activity);
3869 gme_debug.put_line ( g_pkg_name
3870 || '.'
3871 || l_api_name
3872 || ' input resource => '
3873 || p_batchstep_rsrc_rec.resources);
3874 END IF;
3875
3876 IF p_batchstep_rsrc_rec.batchstep_resource_id IS NOT NULL THEN
3877 OPEN c_gbsr (p_batchstep_rsrc_rec.batchstep_resource_id);
3878
3879 FETCH c_gbsr
3880 INTO x_batchstep_rsrc_rec;
3881
3882 IF c_gbsr%NOTFOUND THEN
3883 CLOSE c_gbsr;
3884 RAISE invalid_record;
3885 END IF;
3886
3887 CLOSE c_gbsr;
3888
3889 RETURN TRUE;
3890 ELSIF ( p_org_code IS NULL
3891 AND p_batchstep_rsrc_rec.organization_id IS NULL)
3892 OR (p_batch_no IS NULL AND p_batchstep_rsrc_rec.batch_id IS NULL)
3893 OR ( p_batchstep_no IS NULL
3894 AND p_batchstep_rsrc_rec.batchstep_id IS NULL)
3895 OR ( p_activity IS NULL
3896 AND p_batchstep_rsrc_rec.batchstep_activity_id IS NULL)
3897 OR ( p_resource IS NULL
3898 AND p_batchstep_rsrc_rec.batchstep_resource_id IS NULL) THEN
3899 RAISE btchstep_rsrc_fetch_err;
3900 END IF;
3901
3902 IF p_batchstep_rsrc_rec.organization_id IS NULL THEN
3903 IF g_debug <= gme_debug.g_log_statement THEN
3904 gme_debug.put_line ( g_pkg_name
3905 || '.'
3906 || l_api_name
3907 || ' retrieve org_id using '
3908 || p_org_code);
3909 END IF;
3910
3911 OPEN get_organization (p_org_code);
3912
3913 FETCH get_organization
3914 INTO l_organization_id;
3915
3916 IF get_organization%NOTFOUND THEN
3917 CLOSE get_organization;
3918 RAISE invalid_organization;
3919 END IF;
3920
3921 CLOSE get_organization;
3922 ELSE
3923 l_organization_id := p_batchstep_rsrc_rec.organization_id;
3924 END IF;
3925
3926 IF p_batchstep_rsrc_rec.batch_id IS NULL THEN
3927 IF g_debug <= gme_debug.g_log_statement THEN
3928 gme_debug.put_line ( g_pkg_name
3929 || '.'
3930 || l_api_name
3931 || ' retrieve batch_id using '
3932 || p_batch_no);
3933 END IF;
3934
3935 OPEN get_batch_id (l_organization_id, p_batch_no);
3936
3937 FETCH get_batch_id
3938 INTO l_batch_id;
3939
3940 IF get_batch_id%NOTFOUND THEN
3941 CLOSE get_batch_id;
3942 RAISE invalid_batch;
3943 END IF;
3944
3945 CLOSE get_batch_id;
3946 ELSE
3947 l_batch_id := p_batchstep_rsrc_rec.batch_id;
3948 END IF;
3949
3950 IF p_batchstep_rsrc_rec.batchstep_id IS NULL THEN
3951 IF g_debug <= gme_debug.g_log_statement THEN
3952 gme_debug.put_line ( g_pkg_name
3953 || '.'
3954 || l_api_name
3955 || ' retrieve batchstep_id using '
3956 || p_batchstep_no);
3957 END IF;
3958
3959 OPEN get_batchstep_id (l_batch_id, p_batchstep_no);
3960
3961 FETCH get_batchstep_id
3962 INTO l_batchstep_id;
3963
3964 IF get_batchstep_id%NOTFOUND THEN
3965 CLOSE get_batchstep_id;
3966 RAISE invalid_batchstep;
3967 END IF;
3968
3969 CLOSE get_batchstep_id;
3970 ELSE
3971 l_batchstep_id := p_batchstep_rsrc_rec.batchstep_id;
3972 END IF;
3973
3974 IF p_batchstep_rsrc_rec.batchstep_resource_id IS NULL THEN
3975 IF g_debug <= gme_debug.g_log_statement THEN
3976 gme_debug.put_line ( g_pkg_name
3977 || '.'
3978 || l_api_name
3979 || ' retrieve resource_id using org_id '
3980 || l_organization_id
3981 || ' and batch_id '
3982 || l_batch_id
3983 || ' and batchstep_id '
3984 || l_batchstep_id
3985 || ' and activity '
3986 || p_activity
3987 || ' and resource '
3988 || p_resource);
3989 END IF;
3990
3991 OPEN gme_batchstep_resource_id (l_organization_id
3992 ,l_batch_id
3993 ,l_batchstep_id
3994 ,p_activity
3995 ,p_resource);
3996
3997 FETCH gme_batchstep_resource_id
3998 INTO l_batchstep_resource_id;
3999
4000 IF gme_batchstep_resource_id%NOTFOUND THEN
4001 CLOSE gme_batchstep_resource_id;
4002 RAISE invalid_batchstep_resource;
4003 ELSE ---GME_BATCHSTEP_RESOURCE_ID%FOUND
4004 CLOSE gme_batchstep_resource_id;
4005
4006 OPEN c_gbsr (l_batchstep_resource_id);
4007
4008 FETCH c_gbsr
4009 INTO x_batchstep_rsrc_rec;
4010
4011 IF c_gbsr%NOTFOUND THEN
4012 CLOSE c_gbsr;
4013 RAISE invalid_record;
4014 END IF;
4015
4016 CLOSE c_gbsr;
4017
4018 RETURN TRUE;
4019 END IF; ----GME_BATCHSTEP_RESOURCE_ID%NOTFOUND THEN
4020 END IF; -----IF p_batchstep_rsrc_rec.BATCHSTEP_RESOURCE_ID IS NULL THEN
4021
4022 RETURN TRUE;
4023 EXCEPTION
4024 WHEN invalid_record OR btchstep_rsrc_fetch_err OR invalid_organization OR invalid_batch OR invalid_batchstep OR invalid_batchstep_activity OR invalid_batchstep_resource THEN
4025 IF g_debug <= gme_debug.g_log_unexpected THEN
4026 gme_debug.put_line
4027 ( ' user defined exception in gme_common_pvt.get_resource function'
4028 || SQLERRM);
4029 END IF;
4030
4031 RETURN FALSE;
4032 WHEN OTHERS THEN
4033 IF g_debug <= gme_debug.g_log_unexpected THEN
4034 gme_debug.put_line
4035 ( ' when others: exception in gme_common_pvt.get_resource function'
4036 || SQLERRM);
4037 END IF;
4038
4039 RETURN FALSE;
4040 END get_batchstep_rsrc;
4041
4042 /*======================================================================
4043 -- PROCEDURE :
4044 -- Check_validity_rule
4045 --
4046 -- DESCRIPTION:
4047 -- This procedure validates the validity rule dates only
4048 --
4049 -- SYNOPSIS:
4050 -- Pawan Kumar created for bug 3088739 to check validity rule dates */
4051 -- Navin: changed based on GME_Reschedule_Batch_Step_TD
4052 -- SivakumarG Bug#5111748 corrected the parameter value checking before
4053 -- checking validity rule dates
4054 -- Sunitha ch. Bug#5336007 checked the parameter p_validate_plan_dates_ind.If it is 1 then
4055 -- validate planned start date only.If it is 0 then validate both
4056 -- Sunitha ch. Bug 5404329 Changed the condition fron AND to OR
4057 --===================================================================== */
4058 FUNCTION check_validity_rule_dates (
4059 p_validity_rule_id IN NUMBER
4060 ,p_start_date IN DATE
4061 ,p_cmplt_date IN DATE
4062 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
4063 ,p_validate_plan_dates_ind IN NUMBER DEFAULT 0)
4064 RETURN BOOLEAN
4065 IS
4066 /* Cusror definitions */
4067 CURSOR get_validity_rule (v_validity_rule_id NUMBER)
4068 IS
4069 SELECT *
4070 FROM gmd_recipe_validity_rules
4071 WHERE recipe_validity_rule_id = v_validity_rule_id;
4072
4073 -- Check if the validity rule status is either 'OBSOLETE' or 'ON HOLD'.
4074 CURSOR cur_get_status_type (v_validity_rule_id NUMBER)
4075 IS
4076 SELECT status_type
4077 FROM gmd_status gs, gmd_recipe_validity_rules grvr
4078 WHERE grvr.recipe_validity_rule_id = v_validity_rule_id
4079 AND status_code = grvr.validity_rule_status;
4080
4081 /* Local variables */
4082 l_validity_rec gmd_recipe_validity_rules%ROWTYPE;
4083 l_start_date DATE;
4084 l_cmplt_date DATE;
4085 l_status_type gmd_recipe_validity_rules.validity_rule_status%TYPE;
4086 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_VALIDITY_RULE';
4087 /* EXCEPTION Definitions */
4088 invalid_validity_rule EXCEPTION;
4089 BEGIN
4090 IF (NVL (g_debug, 0) IN
4091 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4092 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4093 || 'Entering');
4094 END IF;
4095
4096 OPEN get_validity_rule (p_validity_rule_id);
4097
4098 FETCH get_validity_rule INTO l_validity_rec;
4099
4100 IF get_validity_rule%NOTFOUND THEN
4101 CLOSE get_validity_rule;
4102 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
4103 RETURN FALSE;
4104 END IF;
4105
4106 CLOSE get_validity_rule;
4107
4108 IF l_validity_rec.delete_mark = 1 THEN
4109 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
4110 RETURN FALSE;
4111 END IF;
4112
4113 l_start_date := p_start_date;
4114 l_cmplt_date := p_cmplt_date;
4115
4116 IF p_start_date IS NULL
4117 AND NVL (gme_common_pvt.g_validate_plan_dates_ind, 0) = 1 THEN
4118 /* Validate only start date against validity rules AND start date is not passed */
4119 RETURN TRUE;
4120 END IF;
4121
4122 IF p_start_date IS NULL AND p_cmplt_date IS NULL THEN
4123 l_start_date := SYSDATE;
4124 l_cmplt_date := SYSDATE;
4125 ELSIF p_start_date IS NULL THEN
4126 l_start_date := l_cmplt_date;
4127 ELSIF p_cmplt_date IS NULL THEN
4128 l_cmplt_date := l_start_date;
4129 END IF;
4130
4131 /* Bug#5111748
4132 parameter value 0: validate both planned dates
4133 1: validate planned start date only
4134 */
4135 --Sunitha ch. Bug#5336007 checked the parameter p_validate_plan_dates_ind.If it is 1 then
4136 --validate planned start date only.If it is 0 then validate both
4137 --Sunitha ch. Bug#5404329 Changed the condition from AND to OR and changed the log message to
4138 --GME_INVAL_VAL_RULE_DATES when validating for actual start date
4139 IF NVL (gme_common_pvt.g_validate_plan_dates_ind, 0) = 1 OR NVL (p_validate_plan_dates_ind, 0) = 1 THEN
4140 IF l_start_date < l_validity_rec.start_date
4141 OR l_start_date > NVL (l_validity_rec.end_date, l_start_date) THEN
4142 IF NVL (p_validate_plan_dates_ind, 0) = 1 THEN
4143 gme_common_pvt.log_message ('GME_INVAL_VAL_RULE_DATES');
4144 ELSE
4145 gme_common_pvt.log_message ('GME_DATES_EXCEED_VALDTY_RULE');
4146 END IF;
4147 RETURN FALSE;
4148 END IF;
4149 ELSE
4150 /* Validate both planned dates against validity rules*/
4151 IF l_start_date < l_validity_rec.start_date
4152 OR l_start_date > NVL (l_validity_rec.end_date, l_start_date)
4153 OR l_cmplt_date < l_validity_rec.start_date
4154 OR l_cmplt_date > NVL (l_validity_rec.end_date, l_cmplt_date) THEN
4155 gme_common_pvt.log_message ('GME_DATES_EXCEED_VALDTY_RULE');
4156 RETURN FALSE;
4157 END IF;
4158 END IF;
4159
4160 -- Navin: Check if the validity rule status is either 'OBSOLETE' or 'ON HOLD'.
4161 IF p_batch_header_rec.batch_id IS NOT NULL THEN
4162 OPEN cur_get_status_type (p_validity_rule_id);
4163
4164 FETCH cur_get_status_type
4165 INTO l_status_type;
4166
4167 CLOSE cur_get_status_type;
4168
4169 IF l_status_type IN ('1000', '800') THEN
4170 IF p_batch_header_rec.batch_status =
4171 gme_common_pvt.g_batch_pending
4172 AND p_batch_header_rec.batch_type = 0 THEN
4173 gme_common_pvt.log_message ('GME_VALIDITY_OBSO_OR_ONHOLD');
4174 RAISE invalid_validity_rule;
4175 ELSIF p_batch_header_rec.batch_status =
4176 gme_common_pvt.g_batch_wip
4177 AND p_batch_header_rec.batch_type = 0 THEN
4178 gme_common_pvt.log_message ('GME_VALIDITY_OBSO_OR_ONHOLD1');
4179 END IF;
4180 END IF; /* l_status_type IN ('1000', '800') */
4181 END IF; /* p_batch_header_rec.batch_id IS NOT NULL */
4182
4183 IF (NVL (g_debug, 0) IN
4184 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4185 gme_debug.put_line ( g_pkg_name
4186 || '.'
4187 || l_api_name
4188 || ':'
4189 || 'Exiting with TRUE');
4190 END IF;
4191
4192 RETURN TRUE;
4193 EXCEPTION
4194 WHEN invalid_validity_rule THEN
4195 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4196 RETURN FALSE;
4197 WHEN OTHERS THEN
4198 IF (NVL (g_debug, 0) > 0) THEN
4199 gme_debug.put_line ( g_pkg_name
4200 || '.'
4201 || l_api_name
4202 || ':'
4203 || ' OTHERS:'
4204 || SQLERRM);
4205 END IF;
4206
4207 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4208 RETURN FALSE;
4209 END check_validity_rule_dates;
4210
4211 /*======================================================================
4212 -- PROCEDURE :
4213 -- material_date_change
4214 --
4215 -- DESCRIPTION:
4216 -- This procedure will update the material required date of the material
4217 -- whose material_detail_id is passed based on the consumption/yield type
4218 -- and its step association. It will then update the required dates on
4219 -- any pending reservations, move order allocations as per the
4220 -- new material required date.
4221 --
4222 -- If any of the lots allocated to reservations, move order allocations
4223 -- happen to expire, this procedure will give appropriate error message to the user.
4224 --
4225 -- SYNOPSIS:
4226 -- 02-Mar-2005 Navin Sinha : Created as part of changed based on GME_Reschedule_Batch_Step_TD build.
4227 --
4228 --===================================================================== */
4229
4230 /*Navin: Added new procedure to update the material required date*/
4231 PROCEDURE material_date_change (
4232 p_material_detail_id IN NUMBER
4233 ,p_material_date IN DATE
4234 ,x_return_status OUT NOCOPY VARCHAR2)
4235 IS
4236 l_api_name CONSTANT VARCHAR2 (30) := 'MATERIAL_DATE_CHANGE';
4237 reserv_mo_err EXCEPTION;
4238 reserv_err EXCEPTION;
4239 mo_err EXCEPTION;
4240 reservation_api_failed EXCEPTION;
4241 process_move_order_failed EXCEPTION;
4242 delete_allocations_failed EXCEPTION;
4243 query_reservations_error EXCEPTION; -- 4944024
4244 notify_CSR_err EXCEPTION; -- 4944024
4245
4246 /* Bug 5016399 Added conditions organization_id and material details table for better performance */
4247 /* Bug#5590100 selecting only required columns and added join condition to match batch_id to avoid
4248 FTS and for better performance */
4249 CURSOR cur_get_mo_lines
4250 IS
4251 SELECT mtrl.line_id, mtrl.inventory_item_id
4252 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, gme_material_details d
4253 WHERE d.material_detail_id = p_material_detail_id
4254 AND mtrl.organization_id = d.organization_id
4255 AND mtrh.organization_id = d.organization_id
4256 AND mtrl.inventory_item_id = d.inventory_item_id
4257 AND mtrl.txn_source_id = d.batch_id
4258 AND mtrl.header_id = mtrh.header_id
4259 AND mtrl.txn_source_line_id = p_material_detail_id
4260 AND mtrh.move_order_type = gme_common_pvt.g_move_order_type;
4261
4262 --FPbug#4912179 query modified to improve performance
4263 CURSOR cur_get_mo_allocations (p_line_id NUMBER)
4264 IS
4265 SELECT mtlt.*
4266 FROM mtl_transaction_lots_temp mtlt,
4267 mtl_material_transactions_temp mmtt
4268 WHERE mmtt.move_order_line_id = p_line_id
4269 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id;
4270 /*SELECT mtlt.*
4271 FROM mtl_txn_request_lines mtrl
4272 ,mtl_transaction_lots_temp mtlt
4273 ,mtl_material_transactions_temp mmtt
4274 WHERE mtrl.line_id = p_line_id
4275 AND mtrl.txn_source_line_id = p_material_detail_id
4276 AND mtrl.line_id = mmtt.move_order_line_id
4277 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id; */
4278
4279 CURSOR cur_get_lot_date (p_item_id NUMBER, p_lot_number VARCHAR2)
4280 IS
4281 SELECT lots.expiration_date
4282 FROM mtl_lot_numbers lots
4283 WHERE lots.inventory_item_id = p_item_id
4284 AND lots.lot_number = p_lot_number;
4285
4286 TYPE l_mo_lines_tbl_typ IS TABLE OF mtl_txn_request_lines%ROWTYPE
4287 INDEX BY BINARY_INTEGER;
4288
4289 TYPE l_mo_line_allocations_tbl_typ IS TABLE OF mtl_transaction_lots_temp%ROWTYPE
4290 INDEX BY BINARY_INTEGER;
4291
4292 --Bug#5590100 Begin
4293 --l_mo_lines_tbl l_mo_lines_tbl_typ; commented the existing line
4294 TYPE mo_lines IS RECORD (
4295 line_id NUMBER,
4296 inventory_item_id NUMBER
4297 );
4298 TYPE mo_lines_tab IS TABLE OF mo_lines INDEX BY BINARY_INTEGER;
4299 l_mo_lines_tbl mo_lines_tab;
4300 --Bug#5590100 End
4301 l_mo_line_allocations_tbl l_mo_line_allocations_tbl_typ;
4302 l_reservations_deleted NUMBER := 0;
4303 l_mo_deleted NUMBER := 0;
4304 l_mo_alloc_deleted NUMBER := 0;
4305 l_size NUMBER;
4306 l_loop_count_mo_lines NUMBER;
4307 l_loop_count_mo_alloc NUMBER;
4308 l_msg_count NUMBER;
4309 l_shelf_life_days NUMBER;
4310 l_return_status VARCHAR2 (1);
4311 l_msg_data VARCHAR2 (2000);
4312 l_material_date DATE;
4313 l_lot_expiration_date DATE;
4314 l_material_detail_rec gme_material_details%ROWTYPE;
4315 l_batch_header_rec gme_batch_header%ROWTYPE;
4316 l_rsv inv_reservation_global.mtl_reservation_rec_type;
4317 ---l_rsv_array inv_reservation_global.mtl_reservation_tbl_type; --------- Punit Kumar
4318 l_rsv_array gme_common_pvt.reservations_tab;
4319 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4320 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type; -- 4944024
4321 l_rsv_count NUMBER; -- 4944024
4322 /* Punit Kumar */
4323 l_batchstep_rec gme_batch_steps%ROWTYPE;
4324 BEGIN
4325 IF (NVL (g_debug, 0) IN
4326 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4327 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4328 || 'Entering');
4329 gme_debug.put_line ( 'Value of p_material_detail_id : '
4330 || p_material_detail_id
4331 || ', p_material_date: '
4332 || TO_CHAR (p_material_date
4333 ,'MM/DD/YYYY HH24:MI:SS') );
4334 END IF;
4335
4336 /* Initially set the return status to success */
4337 x_return_status := fnd_api.g_ret_sts_success;
4338 -- Fetch material details.
4339 l_material_detail_rec.material_detail_id := p_material_detail_id;
4340
4341 IF NOT gme_material_details_dbl.fetch_row (l_material_detail_rec
4342 ,l_material_detail_rec) THEN
4343 RAISE fnd_api.g_exc_error;
4344 END IF;
4345
4346 l_batch_header_rec.batch_id := l_material_detail_rec.batch_id;
4347
4348 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
4349 ,l_batch_header_rec) ) THEN
4350 RAISE fnd_api.g_exc_error;
4351 END IF;
4352
4353 IF p_material_date IS NOT NULL THEN
4354 l_material_date := p_material_date;
4355
4356 IF (NVL (g_debug, 0) IN
4357 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4358 gme_debug.put_line
4359 ( 'Update existing Material Required Date : '
4360 || TO_CHAR (l_material_detail_rec.material_requirement_date
4361 ,'MM/DD/YYYY HH24:MI:SS')
4362 || ' in Gme_material_details with new Material Required Date : '
4363 || TO_CHAR (p_material_date, 'MM/DD/YYYY HH24:MI:SS') );
4364 END IF;
4365 ELSE /* p_material_date IS NULL */
4366 IF (NVL (g_debug, 0) IN
4367 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4368 gme_debug.put_line
4369 ('p_material_date is null calling gme_common_pvt.calc_mtl_req_date');
4370 END IF;
4371
4372 IF get_assoc_step (p_material_detail_id
4373 ,l_batchstep_rec.batchstep_id
4374 ,l_batchstep_rec.step_status) THEN
4375 IF NOT gme_batch_steps_dbl.fetch_row (l_batchstep_rec
4376 ,l_batchstep_rec) THEN
4377 RAISE fnd_api.g_exc_error;
4378 END IF;
4379 END IF;
4380
4381 gme_common_pvt.calc_mtl_req_date
4382 (p_batch_header_rec => l_batch_header_rec
4383 ,p_batchstep_rec => l_batchstep_rec
4384 ,p_mtl_dtl_rec => l_material_detail_rec
4385 ,x_mtl_req_date => l_material_date
4386 ,x_return_status => l_return_status);
4387
4388 IF (NVL (g_debug, 0) IN
4389 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4390 gme_debug.put_line
4391 ( 'Came back from gme_common_pvt.calc_mtl_req_date with status '
4392 || l_return_status);
4393 END IF;
4394
4395 -- Error handling for deduce_material_date call.
4396 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4397 RAISE fnd_api.g_exc_error;
4398 END IF;
4399 END IF; /* p_material_date IS NOT NULL */
4400
4401 -- Update new material_required_date in gme_material_details.
4402 l_material_detail_rec.material_requirement_date := l_material_date;
4403
4404 IF NOT gme_material_details_dbl.update_row
4405 (p_material_detail => l_material_detail_rec) THEN
4406 RAISE fnd_api.g_exc_error;
4407 END IF;
4408
4409 IF (NVL (g_debug, 0) IN
4410 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4411 gme_debug.put_line ('Came back from update of gme_material_details');
4412 END IF;
4413
4414 IF (NVL (g_debug, 0) IN
4415 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4416 gme_debug.put_line ( g_pkg_name
4417 || '.'
4418 || l_api_name
4419 || ':'
4420 || 'Exiting with '
4421 || x_return_status);
4422 END IF;
4423
4424 /* BUG 4944024 BEGIN */
4425 /* Change of date a supply line could influence associated sales reservations so */
4426 /* we need to notify the sales representatve accordingly */
4427 /* ==============================================================================*/
4428 IF (NVL (g_debug, 0) IN
4429 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4430 gme_debug.put_line (' Assessing line type which is '||l_material_detail_rec.line_type);
4431 END IF;
4432 IF l_material_detail_rec.line_type <> gme_common_pvt.g_line_type_ing THEN /* -1 */
4433 IF (NVL (g_debug, 0) IN
4434 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4435 gme_debug.put_line (' Line is a source of supply so invoke Query_Prod_Supply_Reservations');
4436 END IF;
4437 GME_SUPPLY_RES_PVT.query_prod_supply_reservations
4438 (p_matl_dtl_rec => l_material_detail_rec
4439 ,x_mtl_reservation_tbl => l_rsv_tbl
4440 ,x_mtl_reservation_tbl_count => l_rsv_count
4441 ,x_msg_count => l_msg_count
4442 ,x_msg_data => l_msg_data
4443 ,x_return_status => l_return_status);
4444
4445 IF g_debug <= gme_debug.g_log_procedure THEN
4446 gme_debug.put_line
4447 ( g_pkg_name
4448 || '.'
4449 || l_api_name
4450 || 'Return status from query_prod_supply_reservations is '
4451 || l_return_status);
4452 gme_debug.put_line
4453 ( g_pkg_name
4454 || '.'
4455 || l_api_name
4456 || ' number of reservations is '
4457 || l_rsv_count);
4458 END IF;
4459
4460 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
4461 RAISE query_reservations_error;
4462 END IF;
4463 FOR k IN 1 .. l_rsv_count LOOP
4464 gme_debug.put_line ('source line id is => '|| l_rsv_tbl(k).demand_source_line_id);
4465 -- Issue notifications for any impacted sales order lines
4466 IF l_rsv_tbl(k).demand_source_type_id = 2 THEN -- sales
4467 IF g_debug <= gme_debug.g_log_procedure THEN
4468 gme_debug.put_line ('Demand is from Sales ');
4469 END IF;
4470 GME_SUPPLY_RES_PVT.notify_CSR
4471 ( P_Batch_id => l_rsv_tbl(k).supply_source_header_id
4472 , P_Batch_line_id => l_rsv_tbl(k).supply_source_line_id
4473 , P_So_line_id => l_rsv_tbl(k).demand_source_line_id
4474 , P_batch_trans_id => NULL
4475 , P_organization_id => l_rsv_tbl(k).organization_id
4476 , P_action_code => 'CHANGE_PLANNED_COMPLETION_DATE'
4477 , X_return_status => x_return_status
4478 , X_msg_cont => l_msg_count
4479 , X_msg_data => l_msg_data );
4480
4481 IF g_debug <= gme_debug.g_log_procedure THEN
4482 gme_debug.put_line ( g_pkg_name || '.'
4483 || l_api_name
4484 || ' after calling notify_CSR for date change status is '
4485 || x_return_status );
4486 END IF;
4487
4488 IF (x_return_status IN
4489 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
4490 RAISE notify_CSR_err;
4491 END IF;
4492 END IF;
4493 END LOOP;
4494 END IF;
4495 /* BUG 4944024 END */
4496 /*
4497 * No further processing of reservations and Move Orders are required in folllowing cases:
4498 * 1. batch_header_rec.update_inventory_ind is not set.
4499 * 2. if line_type is not Ingredient.
4500 */
4501 IF l_batch_header_rec.update_inventory_ind <> 'Y'
4502 OR l_material_detail_rec.line_type <>
4503 gme_common_pvt.g_line_type_ing /* -1 */ THEN
4504 IF (NVL (g_debug, 0) IN
4505 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4506 gme_debug.put_line
4507 ( 'Returning no further processing of reservations and Move Orders are required. '
4508 || 'Because update_inventory_ind : '
4509 || l_batch_header_rec.update_inventory_ind
4510 || ' line_type : '
4511 || l_material_detail_rec.line_type);
4512 END IF;
4513
4514 RETURN;
4515 END IF;
4516
4517 /* Query reservations for the material_detail_id by calling Query_reservation. */
4518 l_rsv.demand_source_type_id := gme_common_pvt.g_txn_source_type;
4519
4520 IF (NVL (g_debug, 0) IN
4521 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4522 gme_debug.put_line ('Calling Query Reservation.');
4523 END IF;
4524
4525 /* start ,Punit Kumar */
4526 gme_reservations_pvt.get_material_reservations
4527 (p_organization_id => l_material_detail_rec.organization_id
4528 ,p_batch_id => l_material_detail_rec.batch_id
4529 ,p_material_detail_id => l_material_detail_rec.material_detail_id
4530 ,x_return_status => l_return_status
4531 ,x_reservations_tbl => l_rsv_array);
4532 l_size := l_rsv_array.COUNT;
4533
4534 /* end */
4535 IF (NVL (g_debug, 0) IN
4536 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4537 gme_debug.put_line
4538 ( 'Came back from Query Reservation with status '
4539 || l_return_status);
4540 END IF;
4541
4542 IF l_size > 0 AND l_return_status = fnd_api.g_ret_sts_success THEN
4543 /* pending reservations found; check for expired lots*/
4544 FOR j IN 1 .. l_size LOOP
4545 -- Check for lot expiration.
4546 OPEN cur_get_lot_date (l_rsv_array (j).inventory_item_id
4547 ,l_rsv_array (j).lot_number);
4548
4549 FETCH cur_get_lot_date
4550 INTO l_lot_expiration_date;
4551
4552 CLOSE cur_get_lot_date;
4553
4554 IF l_lot_expiration_date <= l_material_date THEN
4555 l_reservations_deleted := 1;
4556
4557 /* Delete the reservation by calling
4558 * The parameters that will be assigned to identity the reservations
4559 * to be deleted are the same as used for querying the reservations*/
4560 IF (NVL (g_debug, 0) IN
4561 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4562 gme_debug.put_line ('Calling Delete Reservation.');
4563 END IF;
4564
4565 gme_reservations_pvt.delete_reservation
4566 (p_reservation_id => l_rsv_array (j).reservation_id
4567 ,x_return_status => l_return_status);
4568
4569 IF (NVL (g_debug, 0) IN
4570 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4571 gme_debug.put_line
4572 ( 'Came back from Delete Reservation with status '
4573 || l_return_status);
4574 END IF;
4575
4576 -- Error handling for delete_reservation call.
4577 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4578 RAISE reservation_api_failed;
4579 END IF;
4580 ELSE
4581 /* l_lot_expiration_date <= l_material_date */
4582 IF (NVL (g_debug, 0) IN
4583 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4584 gme_debug.put_line
4585 ('Lot expiration date > material date + l_shelf_life_days');
4586 END IF;
4587
4588 /* For remaining reserved lots, update the material required
4589 date with the l_material_date. Assign the new required date to
4590 the corresponding parameter of p_to_rsv_rec */
4591 IF (NVL (g_debug, 0) IN
4592 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4593 gme_debug.put_line
4594 ( 'Calling Update Reservation for reservation_id: '
4595 || l_rsv_array (j).reservation_id
4596 || ' requirement_date: '
4597 || TO_CHAR (l_material_date, 'MM/DD/YYYY HH24:MI:SS') );
4598 END IF;
4599
4600 gme_reservations_pvt.update_reservation
4601 (p_reservation_id => l_rsv_array (j).reservation_id
4602 ,p_revision => l_rsv_array (j).revision
4603 ,p_subinventory => l_rsv_array (j).subinventory_code
4604 ,p_locator_id => l_rsv_array (j).locator_id
4605 ,p_lot_number => l_rsv_array (j).lot_number
4606 ,p_new_qty => l_rsv_array (j).reservation_quantity
4607 ,p_new_uom => l_rsv_array (j).reservation_uom_code
4608 ,p_new_date => l_material_date
4609 ,x_return_status => l_return_status);
4610
4611 IF (NVL (g_debug, 0) IN
4612 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4613 gme_debug.put_line
4614 ( 'Came back from Update Reservation with status '
4615 || l_return_status);
4616 END IF;
4617
4618 -- Error handling for update reservation call.
4619 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4620 RAISE reservation_api_failed;
4621 END IF;
4622 END IF;
4623 /* l_lot_expiration_date <= l_material_date*/
4624 END LOOP;
4625 --FPBug#4585491 /* j in 1..l_size */
4626 ELSIF l_return_status <> fnd_api.g_ret_sts_success THEN /* If querying of reservations threw an error */
4627 RAISE reservation_api_failed;
4628 END IF; /* l_size > 0 and l_return_status = fnd_api.g_ret_sts_success */
4629
4630 OPEN cur_get_mo_lines;
4631
4632 FETCH cur_get_mo_lines
4633 BULK COLLECT INTO l_mo_lines_tbl;
4634
4635 l_loop_count_mo_lines := cur_get_mo_lines%ROWCOUNT;
4636
4637 CLOSE cur_get_mo_lines;
4638
4639 FOR k IN 1 .. l_loop_count_mo_lines LOOP
4640 -- Update MO lines
4641 IF (NVL (g_debug, 0) IN
4642 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4643 gme_debug.put_line
4644 ( 'Calling Process move order line for txn_source_line_id : '
4645 || l_material_detail_rec.material_detail_id
4646 || ' Date_required : '
4647 || TO_CHAR (l_material_date, 'MM/DD/YYYY HH24:MI:SS') );
4648 END IF;
4649
4650 gme_move_orders_pvt.update_move_order_lines
4651 (p_batch_id => l_material_detail_rec.batch_id
4652 ,p_material_detail_id => l_material_detail_rec.material_detail_id
4653 ,p_new_qty => NULL
4654 ,p_new_date => l_material_date
4655 ,p_invis_move_line_id => NULL
4656 ,x_return_status => l_return_status);
4657
4658 IF (NVL (g_debug, 0) IN
4659 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4660 gme_debug.put_line
4661 ( 'Came back from Process move order line with status '
4662 || l_return_status);
4663 END IF;
4664
4665 -- Error handling for process_move_order_line.
4666 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4667 RAISE process_move_order_failed;
4668 END IF;
4669
4670 /* For each of the mo lines, get the corresponding allocations */
4671 OPEN cur_get_mo_allocations (l_mo_lines_tbl (k).line_id);
4672
4673 FETCH cur_get_mo_allocations
4674 BULK COLLECT INTO l_mo_line_allocations_tbl;
4675
4676 l_loop_count_mo_alloc := cur_get_mo_allocations%ROWCOUNT;
4677
4678 CLOSE cur_get_mo_allocations;
4679
4680 FOR j IN 1 .. l_loop_count_mo_alloc LOOP
4681 -- Check for lot expiration.
4682 OPEN cur_get_lot_date (l_mo_lines_tbl (k).inventory_item_id
4683 ,l_mo_line_allocations_tbl (j).lot_number);
4684
4685 FETCH cur_get_lot_date
4686 INTO l_lot_expiration_date;
4687
4688 CLOSE cur_get_lot_date;
4689
4690 IF l_lot_expiration_date <= l_material_date THEN
4691 l_mo_deleted := 1;
4692
4693 -- Delete mo alloacations
4694 IF (NVL (g_debug, 0) IN
4695 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4696 gme_debug.put_line
4697 ( 'Delete mo alloacations for mo_line_id: '
4698 || l_mo_line_allocations_tbl (j).transaction_temp_id);
4699 END IF;
4700
4701 inv_mo_line_detail_util.delete_allocations
4702 (x_return_status => l_return_status
4703 ,x_msg_data => l_msg_data
4704 ,x_msg_count => l_msg_count
4705 ,p_mo_line_id => l_mo_lines_tbl (k).line_id
4706 ,p_transaction_temp_id => l_mo_line_allocations_tbl (j).transaction_temp_id);
4707
4708 IF (NVL (g_debug, 0) IN
4709 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4710 gme_debug.put_line
4711 ( 'Came back from Delete Allocations with status '
4712 || l_return_status);
4713 END IF;
4714
4715 -- Error handling for process_move_order_line.
4716 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4717 RAISE delete_allocations_failed;
4718 END IF;
4719 END IF;
4720 END LOOP; /* j in 1..l_loop_count_mo_alloc */
4721 END LOOP; /* k IN 1..l_loop_count_mo_lines */
4722
4723 /*Give error message to user based on the values of the 2 flags */
4724 IF l_reservations_deleted = 1 AND l_mo_deleted = 1 THEN
4725 RAISE reserv_mo_err;
4726 ELSIF l_reservations_deleted = 1 AND l_mo_deleted = 0 THEN
4727 RAISE reserv_err;
4728 ELSIF l_reservations_deleted = 0 AND l_mo_deleted = 1 THEN
4729 RAISE mo_err;
4730 END IF;
4731
4732 IF (NVL (g_debug, 0) IN
4733 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4734 gme_debug.put_line ( g_pkg_name
4735 || '.'
4736 || l_api_name
4737 || ':'
4738 || 'Exiting with '
4739 || x_return_status);
4740 END IF;
4741 EXCEPTION
4742 WHEN fnd_api.g_exc_error THEN
4743 x_return_status := fnd_api.g_ret_sts_error;
4744 WHEN reserv_mo_err THEN
4745 /* GME_EXPIRED_RESERV_MO_DELETED: Due to requirement date change,
4746 some reserved lots and move orders allocations expired and are hence deleted.
4747 */
4748 --correct message will be set in the calling proc.
4749 --FPBug#4585491 changed status to B from W3
4750 x_return_status := 'B';
4751 WHEN reserv_err THEN
4752 -- Due to requirement date change, some reserved lots expired and are hence deleted.
4753 --correct message will be set in the calling proc.
4754 --FPBug#4585491 changed status to R from W1
4755 x_return_status := 'R';
4756 WHEN mo_err THEN
4757 -- Due to requirement date change, some move orders allocations expired and are hence deleted.
4758 --correct message will be set in the calling proc.
4759 --FPBug#4585491 changed status to M from W2
4760 x_return_status := 'M';
4761 WHEN reservation_api_failed OR process_move_order_failed OR delete_allocations_failed THEN
4762 x_return_status := l_return_status;
4763 WHEN query_reservations_error OR notify_CSR_err THEN -- 4944024 BEGIN
4764 IF g_debug <= gme_debug.g_log_statement THEN
4765 gme_debug.put_line
4766 ( g_pkg_name
4767 || '.'
4768 || l_api_name
4769 || 'Error is :'
4770 || l_msg_data);
4771 END IF; -- 4944024 END
4772 WHEN OTHERS THEN
4773 x_return_status := fnd_api.g_ret_sts_unexp_error;
4774 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4775
4776 IF (NVL (g_debug, 0) > 0) THEN
4777 gme_debug.put_line ( g_pkg_name
4778 || '.'
4779 || l_api_name
4780 || ':'
4781 || ' OTHERS:'
4782 || SQLERRM);
4783 END IF;
4784 END material_date_change;
4785
4786 /*======================================================================
4787 -- PROCEDURE :
4788 -- Is_material_auto_release
4789 --
4790 -- DESCRIPTION:
4791 -- This procedure determines whether a material is auto release
4792 -- A material can be auto release if the release_type is auto
4793 -- or auto by step with no step dependencies.
4794 -- REQUIREMENTS
4795 -- Pass in the material_detail_id of the material that you are
4796 -- interested in.
4797 --
4798 -- SYNOPSIS:
4799 --
4800 --===================================================================== */
4801 FUNCTION is_material_auto_release (
4802 p_line_id IN gme_material_details.material_detail_id%TYPE)
4803 RETURN NUMBER
4804 IS
4805 l_release_type gme_material_details.release_type%TYPE;
4806 l_dep_count NUMBER;
4807 l_is_auto NUMBER;
4808 l_api_name CONSTANT VARCHAR2 (30) := 'IS_MATERIAL_AUTO_RELEASE';
4809 BEGIN
4810 IF (NVL (g_debug, 0) IN
4811 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4812 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4813 || 'Entering');
4814 END IF;
4815
4816 SELECT release_type
4817 INTO l_release_type
4818 FROM gme_material_details
4819 WHERE material_detail_id = p_line_id;
4820
4821 l_is_auto := l_release_type;
4822
4823 IF l_release_type = 0 THEN
4824 l_is_auto := 0;
4825 ELSIF l_release_type = 3 THEN
4826 SELECT COUNT (1)
4827 INTO l_dep_count
4828 FROM DUAL
4829 WHERE EXISTS (SELECT 1
4830 FROM gme_batch_step_items
4831 WHERE material_detail_id = p_line_id);
4832
4833 IF l_dep_count = 0 THEN
4834 l_is_auto := 0;
4835 ELSE
4836 l_is_auto := 3;
4837 END IF;
4838 END IF; /* l_release_type = 0 */
4839
4840 IF (NVL (g_debug, 0) IN
4841 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4842 gme_debug.put_line ( g_pkg_name
4843 || '.'
4844 || l_api_name
4845 || ':'
4846 || 'Exiting with l_is_auto: '
4847 || l_is_auto);
4848 END IF;
4849
4850 RETURN l_is_auto;
4851 EXCEPTION
4852 WHEN OTHERS THEN
4853 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4854
4855 IF (NVL (g_debug, 0) > 0) THEN
4856 gme_debug.put_line ( g_pkg_name
4857 || '.'
4858 || l_api_name
4859 || ':'
4860 || ' OTHERS:'
4861 || SQLERRM);
4862 END IF;
4863
4864 IF l_is_auto <> 0 OR l_is_auto <> 3 THEN
4865 l_is_auto := 9;
4866 END IF;
4867
4868 RETURN l_is_auto;
4869 END is_material_auto_release;
4870
4871 FUNCTION get_material_detail (
4872 p_material_detail_rec IN gme_material_details%ROWTYPE
4873 ,p_org_code IN VARCHAR2
4874 ,p_batch_no IN VARCHAR2
4875 ,p_batch_type IN NUMBER
4876 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
4877 ,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE)
4878 RETURN BOOLEAN
4879 IS
4880 l_batch_header_rec gme_batch_header%ROWTYPE;
4881 l_material_detail_rec gme_material_details%ROWTYPE;
4882 l_api_name VARCHAR2 (30) := 'GET_MATERIAL_DETAIL';
4883 material_detail_fetch_error EXCEPTION;
4884 batch_header_fetch_error EXCEPTION;
4885 BEGIN
4886 IF g_debug <= gme_debug.g_log_procedure THEN
4887 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
4888 || l_api_name);
4889 END IF;
4890
4891 IF p_org_code IS NOT NULL
4892 AND p_batch_type IS NOT NULL
4893 AND p_batch_no IS NOT NULL THEN
4894 l_batch_header_rec.batch_no := p_batch_no;
4895 l_batch_header_rec.batch_type := p_batch_type;
4896
4897 IF NOT gme_common_pvt.get_batch_header
4898 (p_batch_header_rec => l_batch_header_rec
4899 ,p_org_code => p_org_code
4900 ,p_batch_type => p_batch_type
4901 ,x_batch_header_rec => x_batch_header_rec) THEN
4902 RAISE batch_header_fetch_error;
4903 END IF;
4904 END IF;
4905
4906 l_material_detail_rec := p_material_detail_rec;
4907 l_material_detail_rec.batch_id := x_batch_header_rec.batch_id;
4908
4909 IF NOT (gme_material_details_dbl.fetch_row (l_material_detail_rec
4910 ,x_material_detail_rec) ) THEN
4911 RAISE material_detail_fetch_error;
4912 END IF;
4913
4914 l_batch_header_rec.batch_id := x_material_detail_rec.batch_id;
4915
4916 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
4917 ,x_batch_header_rec) ) THEN
4918 RAISE batch_header_fetch_error;
4919 END IF;
4920
4921 RETURN TRUE;
4922 EXCEPTION
4923 WHEN OTHERS THEN
4924 IF g_debug <= gme_debug.g_log_unexpected THEN
4925 gme_debug.put_line ( 'When others exception in '
4926 || g_pkg_name
4927 || '.'
4928 || l_api_name
4929 || ' Error is '
4930 || SQLERRM);
4931 END IF;
4932
4933 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4934 RETURN FALSE;
4935 END get_material_detail;
4936 -- Pawan Kumar Added for bug 5138929
4937 FUNCTION check_close_period (
4938 p_org_id IN NUMBER
4939 ,p_trans_date IN DATE)
4940 RETURN BOOLEAN
4941 IS
4942 l_period_id INTEGER;
4943 l_open_past_period BOOLEAN;
4944 l_api_name VARCHAR2 (100) := 'check_close_period';
4945 BEGIN
4946 IF g_debug <= gme_debug.g_log_procedure THEN
4947 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
4948 || l_api_name);
4949 END IF;
4950
4951 invttmtx.tdatechk (org_id => p_org_id
4952 ,transaction_date => p_trans_date
4953 ,period_id => l_period_id
4954 ,open_past_period => l_open_past_period);
4955 IF (l_period_id = 0) THEN
4956 FND_MESSAGE.SET_NAME('INV','INV_NO_OPEN_PERIOD');
4957 FND_MSG_PUB.Add;
4958 RETURN FALSE;
4959 ELSIF (l_period_id = -1) THEN
4960 FND_MESSAGE.SET_NAME('INV', 'INV_RETRIEVE_PERIOD');
4961 FND_MSG_PUB.Add;
4962 RETURN FALSE;
4963 ELSIF (l_period_id > 0) THEN
4964 RETURN TRUE;
4965 END IF;
4966 RETURN TRUE;
4967 EXCEPTION
4968 WHEN OTHERS THEN
4969 IF g_debug <= gme_debug.g_log_unexpected THEN
4970 gme_debug.put_line ( 'When others exception in '
4971 || g_pkg_name
4972 || '.'
4973 || l_api_name
4974 || ' Error is '
4975 || SQLERRM);
4976 END IF;
4977
4978 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4979 RETURN FALSE;
4980 END check_close_period;
4981
4982 /*======================================================================
4983 -- PROCEDURE :
4984 -- Validate_batch
4985 --
4986 -- DESCRIPTION:
4987 -- This procedure fetch and validates basic batch attributes
4988 --
4989 -- REQUIREMENTS
4990 -- Pass in the batch_id of the batch to fetched and vaildated.
4991 --
4992 -- SYNOPSIS:
4993 --
4994 -- G. Muratore 01-DEC-2008 Bug 7562848
4995 -- Added parameter to allow calling routine to bypass phantom check.
4996 --===================================================================== */
4997
4998 Procedure Validate_batch (
4999 p_batch_header_rec IN gme_batch_header%ROWTYPE
5000 ,p_org_code IN VARCHAR2
5001 ,p_batch_type IN NUMBER
5002 ,p_check_phantom IN VARCHAR2 DEFAULT 'Y'
5003 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
5004 ,x_message_count OUT NOCOPY NUMBER
5005 ,x_message_list OUT NOCOPY VARCHAR2
5006 ,x_return_status OUT NOCOPY VARCHAR2)
5007
5008 IS
5009 l_api_name VARCHAR2 (100) := 'validate_batch';
5010 l_batch_header_rec gme_batch_header%ROWTYPE;
5011
5012 batch_header_fetch_error EXCEPTION;
5013 BEGIN
5014 IF g_debug <= gme_debug.g_log_procedure THEN
5015 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
5016 || l_api_name);
5017 END IF;
5018 /* Set the return status to success initially */
5019 x_return_status := fnd_api.g_ret_sts_success;
5020 l_batch_header_rec := p_batch_header_rec;
5021
5022 /* IF (p_batch_header_rec.organization_id IS NULL AND p_org_code IS NULL) THEN
5023 gme_common_pvt.log_message(p_product_code => 'INV'
5024 ,p_message_code => 'INV_ORG_REQUIRED');
5025 RAISE fnd_api.g_exc_error;
5026 END IF; */
5027 IF p_batch_header_rec.batch_no IS NULL AND p_batch_header_rec.batch_id IS NULL THEN
5028 gme_common_pvt.log_message ('GME_MISSING_BATCH_IDENTIFIER');
5029 RAISE fnd_api.g_exc_error;
5030 END IF;
5031 IF NOT gme_common_pvt.get_batch_header
5032 (p_batch_header_rec => p_batch_header_rec
5033 ,p_org_code => p_org_code
5034 ,p_batch_type => p_batch_type
5035 ,x_batch_header_rec => l_batch_header_rec) THEN
5036 IF (g_debug = gme_debug.g_log_statement) THEN
5037 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': batch not found ');
5038 END IF;
5039 gme_common_pvt.log_message ('GME_BATCH_NOT_FOUND');
5040 RAISE batch_header_fetch_error;
5041 END IF;
5042
5043 /* Check for phantom batch */
5044 -- Bug 7562848 - Bypass phantom check if calling routine makes that request.
5045 IF NVL (l_batch_header_rec.parentline_id, 0) > 0 AND p_check_phantom = 'Y' THEN
5046 gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
5047 RAISE fnd_api.g_exc_error;
5048 END IF;
5049
5050 /* Check for migrated batch */
5051 IF NVL (l_batch_header_rec.migrated_batch_ind, 'Y') = 'Y' THEN
5052 gme_common_pvt.log_message ('GME_MIGRATED_BATCH');
5053 RAISE fnd_api.g_exc_error;
5054 END IF;
5055 gme_common_pvt.g_error_count := 0;
5056 gme_common_pvt.g_setup_done :=
5057 gme_common_pvt.setup (p_org_id => l_batch_header_rec.organization_id
5058 ,p_org_code => p_org_code);
5059
5060 IF NOT gme_common_pvt.g_setup_done THEN
5061 IF (g_debug = gme_debug.g_log_statement) THEN
5062 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ':set up problem ');
5063 END IF;
5064 RAISE fnd_api.g_exc_error;
5065 ELSE
5066 l_batch_header_rec.organization_id :=
5067 gme_common_pvt.g_organization_id;
5068 END IF;
5069 x_batch_header_rec := l_batch_header_rec ;
5070
5071 IF g_debug <= gme_debug.g_log_statement THEN
5072 gme_debug.put_line ( g_pkg_name || '.' || l_api_name || ':' ||
5073 'exiting batch_header_id = ' || x_batch_header_rec.batch_id);
5074 gme_debug.put_line ( g_pkg_name || '.' || l_api_name || ':' ||
5075 'exiting organization_id = ' || x_batch_header_rec.organization_id);
5076 END IF;
5077 EXCEPTION
5078 WHEN fnd_api.g_exc_error THEN
5079 x_return_status := fnd_api.g_ret_sts_error;
5080 WHEN batch_header_fetch_error THEN
5081 x_return_status := fnd_api.g_ret_sts_error;
5082 WHEN OTHERS THEN
5083 IF g_debug <= gme_debug.g_log_unexpected THEN
5084 gme_debug.put_line ( 'When others exception in '
5085 || g_pkg_name|| '.'||l_api_name||'Error is '
5086 || SQLERRM);
5087 END IF;
5088 x_return_status := fnd_api.g_ret_sts_unexp_error;
5089 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5090
5091 END Validate_batch;
5092 /*======================================================================
5093 -- PROCEDURE :
5094 -- Validate_material_detail
5095 --
5096 -- DESCRIPTION:
5097 -- This procedure fetch and validates basic material attributes
5098 --
5099 -- REQUIREMENTS
5100 -- Pass in the material_id of the batch to fetched and vaildated.
5101 --
5102 -- SYNOPSIS:
5103 --
5104 --===================================================================== */
5105
5106 Procedure Validate_material_detail (
5107 p_material_detail_rec IN gme_material_details%ROWTYPE
5108 ,p_org_code IN VARCHAR2
5109 ,p_batch_no IN VARCHAR2
5110 ,p_batch_type IN NUMBER
5111 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
5112 ,x_material_detail_rec OUT NOCOPY gme_material_details%ROWTYPE
5113 ,x_message_count OUT NOCOPY NUMBER
5114 ,x_message_list OUT NOCOPY VARCHAR2
5115 ,x_return_status OUT NOCOPY VARCHAR2)
5116
5117 IS
5118 l_api_name VARCHAR2 (100) := 'validate_material_detail';
5119 l_batch_header_rec gme_batch_header%ROWTYPE;
5120 l_material_detail_rec gme_material_details%ROWTYPE;
5121 material_fetch_error EXCEPTION;
5122 BEGIN
5123 IF g_debug <= gme_debug.g_log_procedure THEN
5124 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
5125 || l_api_name);
5126 END IF;
5127
5128 /* Set the return status to success initially */
5129 x_return_status := fnd_api.g_ret_sts_success;
5130 l_material_detail_rec := p_material_detail_rec;
5131
5132
5133 /*IF (p_material_detail_rec.organization_id IS NULL AND p_org_code IS NULL) THEN
5134 gme_common_pvt.log_message(p_product_code => 'INV'
5135 ,p_message_code => 'INV_ORG_REQUIRED');
5136 RAISE fnd_api.g_exc_error;
5137 END IF; */
5138 IF NOT gme_common_pvt.get_material_detail
5139 (p_material_detail_rec => p_material_detail_rec
5140 ,p_org_code => p_org_code
5141 ,p_batch_no => p_batch_no
5142 ,p_batch_type => p_batch_type
5143 ,x_batch_header_rec => l_batch_header_rec
5144 ,x_material_detail_rec => l_material_detail_rec) THEN
5145
5146 IF (g_debug = gme_debug.g_log_statement) THEN
5147 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': batch not found ');
5148 END IF;
5149
5150 RAISE material_fetch_error;
5151
5152 END IF;
5153
5154 /* Bug#7157383 Commented the below code as this validation is not correct
5155 * in this place */
5156 /* Check for phantom batch
5157 IF NVL (l_batch_header_rec.parentline_id, 0) > 0 THEN
5158 gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
5159 RAISE fnd_api.g_exc_error;
5160 END IF; */
5161 /* Check for migrated batch */
5162 IF NVL (l_batch_header_rec.migrated_batch_ind, 'Y') = 'Y' THEN
5163 gme_common_pvt.log_message ('GME_MIGRATED_BATCH');
5164 RAISE fnd_api.g_exc_error;
5165 END IF;
5166 gme_common_pvt.g_error_count := 0;
5167 gme_common_pvt.g_setup_done :=
5168 gme_common_pvt.setup (p_org_id => l_batch_header_rec.organization_id
5169 ,p_org_code => p_org_code);
5170
5171 IF NOT gme_common_pvt.g_setup_done THEN
5172 IF (g_debug = gme_debug.g_log_statement) THEN
5173 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ':set up problem ');
5174 END IF;
5175 RAISE fnd_api.g_exc_error;
5176 ELSE
5177 l_batch_header_rec.organization_id :=
5178 gme_common_pvt.g_organization_id;
5179 END IF;
5180 x_batch_header_rec := l_batch_header_rec ;
5181 x_material_detail_rec := l_material_detail_rec ;
5182 IF g_debug <= gme_debug.g_log_statement THEN
5183 gme_debug.put_line ( g_pkg_name || '.' || l_api_name || ':' ||
5184 'exiting batch_header_id = ' || x_batch_header_rec.batch_id);
5185 gme_debug.put_line ( g_pkg_name || '.' || l_api_name || ':' ||
5186 'exiting organization_id = ' || x_batch_header_rec.organization_id);
5187 gme_debug.put_line ( g_pkg_name || '.' || l_api_name || ':' ||
5188 'exiting mat det_id = ' || x_material_detail_rec.material_detail_id);
5189 END IF;
5190
5191 EXCEPTION
5192 WHEN fnd_api.g_exc_error THEN
5193 x_return_status := fnd_api.g_ret_sts_error;
5194 WHEN material_fetch_error THEN
5195 x_return_status := fnd_api.g_ret_sts_error;
5196 WHEN OTHERS THEN
5197 IF g_debug <= gme_debug.g_log_unexpected THEN
5198 gme_debug.put_line ( 'When others exception in '
5199 || g_pkg_name
5200 || '.'
5201 || l_api_name
5202 || ' Error is '
5203 || SQLERRM);
5204 END IF;
5205 x_return_status := fnd_api.g_ret_sts_unexp_error;
5206 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5207 gme_common_pvt.count_and_get (x_count => x_message_count
5208 ,p_encoded => fnd_api.g_false
5209 ,x_data => x_message_list);
5210 END Validate_material_detail;
5211 /*======================================================================
5212 -- PROCEDURE :
5213 -- Validate_batch_step
5214 --
5215 -- DESCRIPTION:
5216 -- This procedure fetch and validates basic batch step attributes
5217 --
5218 -- REQUIREMENTS
5219 -- Pass in the batchstep_id of the batch to fetched and validated.
5220 --
5221 -- SYNOPSIS:
5222 --
5223 -- HISTORY:
5224 -- 14-NOV-2011 G. Muratore Bug 13356510
5225 -- Commented out validation code that was incorrectly checking for phantom batch.
5226 --===================================================================== */
5227 Procedure Validate_batch_step (
5228 p_batch_step_rec IN gme_batch_steps%ROWTYPE
5229 ,p_org_code IN VARCHAR2
5230 ,p_batch_no IN VARCHAR2
5231 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
5232 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
5233 ,x_message_count OUT NOCOPY NUMBER
5234 ,x_message_list OUT NOCOPY VARCHAR2
5235 ,x_return_status OUT NOCOPY VARCHAR2)
5236
5237 IS
5238 l_api_name VARCHAR2 (100) := 'validate_batch_step';
5239 l_batch_header_rec gme_batch_header%ROWTYPE;
5240 l_batch_step_rec gme_batch_steps%ROWTYPE;
5241 step_header_fetch_error EXCEPTION;
5242 BEGIN
5243 IF g_debug <= gme_debug.g_log_procedure THEN
5244 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
5245 || l_api_name);
5246 END IF;
5247 x_return_status := fnd_api.g_ret_sts_success;
5248
5249
5250 IF NOT gme_common_pvt.get_batch_step (
5251 p_batch_step_rec => p_batch_step_rec
5252 ,p_org_code => p_org_code
5253 ,p_batch_no => p_batch_no
5254 ,x_batch_step_rec => l_batch_step_rec
5255 ,x_batch_header_rec => l_batch_header_rec) THEN
5256 RAISE Step_header_fetch_error;
5257 END IF;
5258
5259 /* Bug 13356510 - Commented the below code as this validation is not correct. */
5260 /* Check for phantom batch */
5261 -- IF NVL (l_batch_header_rec.parentline_id, 0) > 0 THEN
5262 -- gme_common_pvt.log_message ('PM_INVALID_PHANTOM_ACTION');
5263 -- RAISE fnd_api.g_exc_error;
5264 -- END IF;
5265
5266 /* Check for migrated batch */
5267 IF NVL (l_batch_header_rec.migrated_batch_ind, 'Y') = 'Y' THEN
5268 gme_common_pvt.log_message ('GME_MIGRATED_BATCH');
5269 RAISE fnd_api.g_exc_error;
5270 END IF;
5271
5272 gme_common_pvt.g_error_count := 0;
5273 gme_common_pvt.g_setup_done :=
5274 gme_common_pvt.setup (p_org_id => l_batch_header_rec.organization_id
5275 ,p_org_code => p_org_code);
5276
5277 IF NOT gme_common_pvt.g_setup_done THEN
5278 IF (g_debug = gme_debug.g_log_statement) THEN
5279 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ':set up problem ');
5280 END IF;
5281 RAISE fnd_api.g_exc_error;
5282 ELSE
5283 l_batch_header_rec.organization_id :=
5284 gme_common_pvt.g_organization_id;
5285 END IF;
5286 x_batch_header_rec := l_batch_header_rec ;
5287 x_batch_step_rec := l_batch_step_rec;
5288 IF g_debug <= gme_debug.g_log_statement THEN
5289 gme_debug.put_line ( g_pkg_name || '.' || l_api_name || ':' ||
5290 'exiting batch_header_id = ' || x_batch_header_rec.batch_id);
5291 gme_debug.put_line ( g_pkg_name || '.' || l_api_name || ':' ||
5292 'exiting organization_id = ' || x_batch_header_rec.organization_id);
5293
5294 END IF;
5295 IF g_debug <= gme_debug.g_log_statement THEN
5296 gme_debug.put_line ( g_pkg_name
5297 || '.'
5298 || l_api_name
5299 || ' BatchStep ID: '
5300 || x_batch_step_rec.batchstep_id);
5301 END IF;
5302 EXCEPTION
5303 WHEN fnd_api.g_exc_error THEN
5304 x_return_status := fnd_api.g_ret_sts_error;
5305 WHEN Step_header_fetch_error THEN
5306 x_return_status := fnd_api.g_ret_sts_error;
5307 WHEN OTHERS THEN
5308 IF g_debug <= gme_debug.g_log_unexpected THEN
5309 gme_debug.put_line ( 'When others exception in '
5310 || g_pkg_name
5311 || '.'
5312 || l_api_name
5313 || ' Error is '
5314 || SQLERRM);
5315 END IF;
5316 x_return_status := fnd_api.g_ret_sts_unexp_error;
5317 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5318 gme_common_pvt.count_and_get (x_count => x_message_count
5319 ,p_encoded => fnd_api.g_false
5320 ,x_data => x_message_list);
5321 END Validate_batch_step;
5322
5323 /* Bug#5394232 Added the following procedure to default transaction date
5324 p_invoke_mode values are 'E' and 'T'
5325 E - To get the default transaction date during any batch/step action and results
5326 in Material Exceptions.
5327 T - To get the default transaction date during manual transactions. This one has to be
5328 be used from wrappers also
5329 */
5330 PROCEDURE fetch_trans_date (
5331 p_material_detail_id IN NUMBER
5332 ,p_invoke_mode IN VARCHAR2 DEFAULT 'T'
5333 ,x_trans_date OUT NOCOPY DATE
5334 ,x_return_status OUT NOCOPY VARCHAR2)
5335 IS
5336 l_api_name CONSTANT VARCHAR2 (30) := 'fetch_trans_date';
5337
5338 l_batchstep_id NUMBER;
5339 l_ing_trans_date DATE;
5340 l_prod_trans_date DATE;
5341 l_trans_date DATE;
5342 l_step_actual_start DATE;
5343 l_step_actual_cmplt DATE;
5344
5345 l_material_detail_rec gme_material_details%ROWTYPE;
5346 l_batch_header_rec gme_batch_header%ROWTYPE;
5347
5348 error_fetch_material EXCEPTION;
5349 error_fetch_batch_details EXCEPTION;
5350 error_close_period EXCEPTION;
5351
5352 CURSOR cur_get_associated_step (v_material_detail_id NUMBER)
5353 IS
5354 SELECT batchstep_id
5355 FROM gme_batch_step_items
5356 WHERE material_detail_id = v_material_detail_id;
5357
5358 CURSOR cur_fetch_step_dates (v_batchstep_id NUMBER)
5359 IS
5360 SELECT actual_start_date, actual_cmplt_date
5361 FROM gme_batch_steps
5362 WHERE batchstep_id = v_batchstep_id;
5363 BEGIN
5364 IF g_debug <= gme_debug.g_log_procedure THEN
5365 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'|| l_api_name);
5366 END IF;
5367
5368 /* initializing the return status*/
5369 x_return_status := fnd_api.g_ret_sts_success;
5370
5371 /* fetch the material detail record */
5372 l_material_detail_rec.material_detail_id := p_material_detail_id;
5373
5374 IF NOT (gme_material_details_dbl.fetch_row(l_material_detail_rec, l_material_detail_rec)) THEN
5375 IF g_debug <= gme_debug.g_log_statement THEN
5376 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'||'Error in Fetching the material detail record');
5377 END IF;
5378 RAISE error_fetch_material;
5379 END IF;
5380
5381 /*fetch the batch details*/
5382 l_batch_header_rec.batch_id := l_material_detail_rec.batch_id;
5383 IF NOT (gme_batch_header_dbl.fetch_row(l_batch_header_rec, l_batch_header_rec)) THEN
5384 IF g_debug <= gme_debug.g_log_statement THEN
5385 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'||'Error in Fetching the Batch Details');
5386 END IF;
5387 RAISE error_fetch_batch_details;
5388 END IF;
5389
5390 IF l_material_detail_rec.release_type IN (gme_common_pvt.g_mtl_autobystep_release,
5391 gme_common_pvt.g_mtl_auto_release ) THEN
5392 /*default ing and prod dates to batch actual dated*/
5393 l_ing_trans_date := l_batch_header_rec.actual_start_date;
5394 l_prod_trans_date := l_batch_header_rec.actual_cmplt_date;
5395
5396 IF l_material_detail_rec.release_type = gme_common_pvt.g_mtl_autobystep_release THEN
5397 /* check whether material line is associated to step */
5398 OPEN cur_get_associated_step (l_material_detail_rec.material_detail_id);
5399 FETCH cur_get_associated_step INTO l_batchstep_id;
5400 CLOSE cur_get_associated_step;
5401
5402 IF l_batchstep_id IS NOT NULL THEN
5403 OPEN cur_fetch_step_dates (l_batchstep_id);
5404 FETCH cur_fetch_step_dates INTO l_step_actual_start, l_step_actual_cmplt;
5405 CLOSE cur_fetch_step_dates;
5406
5407 l_ing_trans_date := l_step_actual_start;
5408 l_prod_trans_date := l_step_actual_cmplt;
5409 END IF;
5410 END IF; /* auto by step check */
5411
5412 IF l_material_detail_rec.line_type = -1 THEN /*ingredient*/
5413 l_trans_date := l_ing_trans_date;
5414 ELSE
5415 l_trans_date := l_prod_trans_date;
5416 END IF;
5417 ELSE
5418 /* Manual and Incremental Release Types.
5419 Transacting manually it has to be defaulted to SYSDATE */
5420 IF p_invoke_mode = 'E' THEN
5421 -- l_trans_date := l_batch_header_rec.actual_cmplt_date;
5422 -- Bug 13881792 - If exception happened during IB then use the date.
5423 IF (gme_common_pvt.g_ib_timestamp_set = -1) THEN
5424 l_trans_date := gme_common_pvt.g_ib_timestamp_date;
5425 ELSE
5426 l_trans_date := l_batch_header_rec.actual_cmplt_date;
5427 END IF;
5428 END IF;
5429 END IF; /*l_material_detail_rec.release_type*/
5430
5431 /* check whether the date is in closed period or not */
5432 IF NOT gme_common_pvt.check_close_period(p_org_id => l_batch_header_rec.organization_id
5433 ,p_trans_date => l_trans_date) THEN
5434 l_trans_date := SYSDATE;
5435 END IF;
5436
5437 x_trans_date := NVL(l_trans_date,SYSDATE);
5438
5439 IF g_debug <= gme_debug.g_log_procedure THEN
5440 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
5441 END IF;
5442 EXCEPTION
5443 WHEN error_fetch_material OR error_fetch_batch_details THEN
5444 x_return_status := fnd_api.g_ret_sts_error;
5445 WHEN OTHERS THEN
5446 IF g_debug <= gme_debug.g_log_unexpected THEN
5447 gme_debug.put_line ( 'When others exception in '
5448 || g_pkg_name
5449 || '.'
5450 || l_api_name
5451 || ' Error is '
5452 || SQLERRM);
5453 END IF;
5454
5455 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5456 x_return_status := fnd_api.g_ret_sts_unexp_error;
5457 END fetch_trans_date;
5458 /*======================================================================
5459 * -- PROCEDURE :
5460 * -- check_oprn_effectivity_dates
5461 * --
5462 * -- DESCRIPTION:
5463 * -- This procedure validates the operation effectivity dates while
5464 * inserting
5465 * -- the step.
5466 * --
5467 * -- SYNOPSIS:
5468 * -- bug 6408612
5469 * -- Swapna K created for bug 6408612 to check operation effectivity dates */
5470 --===================================================================== */
5471 FUNCTION check_oprn_effectivity_dates (
5472 p_oprn_id IN NUMBER
5473 ,p_start_date IN DATE
5474 ,p_cmplt_date IN DATE
5475
5476 )
5477 RETURN BOOLEAN IS
5478
5479 /* Cusror definitions */
5480 Cursor get_oprn_effectivity (v_oprn_id NUMBER) IS
5481 SELECT *
5482 FROM gmd_operations
5483 WHERE oprn_id = v_oprn_id;
5484
5485
5486 /* Local variables */
5487 l_oprn_rec GMD_OPERATIONS%ROWTYPE;
5488 l_start_date DATE;
5489 l_cmplt_date DATE;
5490
5491 l_api_name CONSTANT VARCHAR2 (30) :=
5492 'check_oprn_effectivity_dates';
5493
5494 /* EXCEPTION Definitions */
5495
5496 BEGIN
5497 /* oprn_id would have been already validated by the time this function
5498 * is called so no need of when no-data-found */
5499 OPEN get_oprn_effectivity(p_oprn_id);
5500 FETCH get_oprn_effectivity INTO l_oprn_rec;
5501 CLOSE get_oprn_effectivity;
5502 l_start_date := p_start_date;
5503 l_cmplt_date := p_cmplt_date;
5504
5505 --BAsed on the profile value 'GME:Validate plan dates' check the dates
5506 IF NVL (gme_common_pvt.g_validate_plan_dates_ind, 0) = 0 THEN
5507 /* Validate both planned dates against operation effectivity dates*/
5508 IF l_start_date < l_oprn_rec.EFFECTIVE_START_DATE OR
5509 l_start_date > nvl(l_oprn_rec.EFFECTIVE_END_DATE,l_start_date) OR
5510 l_cmplt_date < l_oprn_rec.EFFECTIVE_START_DATE OR
5511 l_cmplt_date > nvl(l_oprn_rec.EFFECTIVE_END_DATE,l_cmplt_date) THEN
5512 gme_common_pvt.log_message('GME_DATES_EXCEED_OPER_VALIDTY');
5513 RETURN FALSE;
5514 END IF;
5515 ELSE
5516 IF l_start_date < l_oprn_rec.EFFECTIVE_START_DATE OR
5517 l_start_date > nvl(l_oprn_rec.EFFECTIVE_END_DATE,l_start_date) THEN
5518 gme_common_pvt.log_message('GME_DATES_EXCEED_OPER_VALIDTY');
5519 RETURN FALSE;
5520 END IF;
5521 END IF;
5522 RETURN TRUE;
5523 EXCEPTION
5524 WHEN OTHERS THEN
5525 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5526 RETURN FALSE;
5527
5528 END check_oprn_effectivity_dates ;
5529
5530 END gme_common_pvt;