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