[Home] [Help]
128: where batch_id = p_batch_id;
129:
130: BEGIN
131: x_return_status := FND_API.G_RET_STS_SUCCESS;
132: IF g_debug <= gme_debug.g_log_procedure THEN
133: gme_debug.put_line ('Entering api '
134: || g_pkg_name || '.'
135: || l_api_name);
136: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' FPO batch_id '||p_fpo_batch_id);
129:
130: BEGIN
131: x_return_status := FND_API.G_RET_STS_SUCCESS;
132: IF g_debug <= gme_debug.g_log_procedure THEN
133: gme_debug.put_line ('Entering api '
134: || g_pkg_name || '.'
135: || l_api_name);
136: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' FPO batch_id '||p_fpo_batch_id);
137: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' BATCH batch_id '||p_new_batch_id);
132: IF g_debug <= gme_debug.g_log_procedure THEN
133: gme_debug.put_line ('Entering api '
134: || g_pkg_name || '.'
135: || l_api_name);
136: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' FPO batch_id '||p_fpo_batch_id);
137: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' BATCH batch_id '||p_new_batch_id);
138: END IF;
139:
140: /* loop through all the product lines in the batch */
133: gme_debug.put_line ('Entering api '
134: || g_pkg_name || '.'
135: || l_api_name);
136: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' FPO batch_id '||p_fpo_batch_id);
137: gme_debug.put_line(g_pkg_name||'.'||l_api_name||' BATCH batch_id '||p_new_batch_id);
138: END IF;
139:
140: /* loop through all the product lines in the batch */
141: For batch_line in get_batch_line(p_FPO_batch_id) Loop
141: For batch_line in get_batch_line(p_FPO_batch_id) Loop
142: l_fpo_batch_line_id := batch_line.material_detail_id ;
143: l_inventory_item_id := batch_line.inventory_item_id ;
144: l_fpo_qty := batch_line.plan_qty;
145: IF g_debug <= gme_debug.g_log_procedure THEN
146: gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ' FPO batch_line_id '||l_fpo_batch_line_id);
147: END IF;
148: /* check reservation exist or not */
149: Open get_res_count(l_FPO_batch_line_id);
142: l_fpo_batch_line_id := batch_line.material_detail_id ;
143: l_inventory_item_id := batch_line.inventory_item_id ;
144: l_fpo_qty := batch_line.plan_qty;
145: IF g_debug <= gme_debug.g_log_procedure THEN
146: gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ' FPO batch_line_id '||l_fpo_batch_line_id);
147: END IF;
148: /* check reservation exist or not */
149: Open get_res_count(l_FPO_batch_line_id);
150: Fetch get_res_count into l_res_count;
149: Open get_res_count(l_FPO_batch_line_id);
150: Fetch get_res_count into l_res_count;
151: Close get_res_count;
152:
153: IF g_debug <= gme_debug.g_log_procedure THEN
154: gme_debug.put_line(g_pkg_name||'.'||l_api_name ||' FPO reservation cound '||l_res_count);
155: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' NEW batch_line_id '||l_batch_line_id);
156: END IF;
157:
150: Fetch get_res_count into l_res_count;
151: Close get_res_count;
152:
153: IF g_debug <= gme_debug.g_log_procedure THEN
154: gme_debug.put_line(g_pkg_name||'.'||l_api_name ||' FPO reservation cound '||l_res_count);
155: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' NEW batch_line_id '||l_batch_line_id);
156: END IF;
157:
158: IF l_res_count = 0 THEN
151: Close get_res_count;
152:
153: IF g_debug <= gme_debug.g_log_procedure THEN
154: gme_debug.put_line(g_pkg_name||'.'||l_api_name ||' FPO reservation cound '||l_res_count);
155: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' NEW batch_line_id '||l_batch_line_id);
156: END IF;
157:
158: IF l_res_count = 0 THEN
159: goto next_batch_line;
167: /* loop to see if different whse may have exist -- only one org in R12*/
168: For each_whse in check_whse(l_fpo_batch_line_id) Loop
169: l_organization_id := each_whse.organization_id;
170:
171: IF g_debug <= gme_debug.g_log_procedure THEN
172: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' reservation org id '||l_organization_id);
173: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' batch inventory_item_id '||l_inventory_item_id);
174: END IF;
175:
168: For each_whse in check_whse(l_fpo_batch_line_id) Loop
169: l_organization_id := each_whse.organization_id;
170:
171: IF g_debug <= gme_debug.g_log_procedure THEN
172: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' reservation org id '||l_organization_id);
173: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' batch inventory_item_id '||l_inventory_item_id);
174: END IF;
175:
176: Open get_new_batch_cmpt_date (p_new_batch_id) ;
169: l_organization_id := each_whse.organization_id;
170:
171: IF g_debug <= gme_debug.g_log_procedure THEN
172: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' reservation org id '||l_organization_id);
173: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' batch inventory_item_id '||l_inventory_item_id);
174: END IF;
175:
176: Open get_new_batch_cmpt_date (p_new_batch_id) ;
177: Fetch get_new_batch_cmpt_date Into l_plan_cmplt_date;
201: ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
202: RAISE fnd_api.g_exc_unexpected_error;
203: END IF;
204:
205: IF g_debug <= gme_debug.g_log_procedure THEN
206: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' fpo reservation count '||l_fpo_rsv_count);
207: END IF;
208:
209: l_remaining_qty := l_planned_qty;
202: RAISE fnd_api.g_exc_unexpected_error;
203: END IF;
204:
205: IF g_debug <= gme_debug.g_log_procedure THEN
206: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' fpo reservation count '||l_fpo_rsv_count);
207: END IF;
208:
209: l_remaining_qty := l_planned_qty;
210: for i in 1..l_fpo_rsv_count Loop
211: l_fpo_rsv_rec := l_fpo_rsv_tbl(i);
212: /* check the batch planned cplt date with the scheduled_ship_date
213: * if the date is out, skip this record
214: */
215: IF g_debug <= gme_debug.g_log_procedure THEN
216: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' build res record for the new batch');
217: END IF;
218: /*IF l_fpo_rsv_rec.requirement_date> l_plan_cmplt_date THEN
219: Goto next_res_line;
212: /* check the batch planned cplt date with the scheduled_ship_date
213: * if the date is out, skip this record
214: */
215: IF g_debug <= gme_debug.g_log_procedure THEN
216: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' build res record for the new batch');
217: END IF;
218: /*IF l_fpo_rsv_rec.requirement_date> l_plan_cmplt_date THEN
219: Goto next_res_line;
220: END IF;
265: , from_name => NULL
266: , to_name => NULL);
267: end if;
268:
269: IF g_debug <= gme_debug.g_log_procedure THEN
270: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' new reserved qty '||l_reserved_qty);
271: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' new reserved qty2 '||l_reserved_qty2);
272: END IF;
273:
266: , to_name => NULL);
267: end if;
268:
269: IF g_debug <= gme_debug.g_log_procedure THEN
270: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' new reserved qty '||l_reserved_qty);
271: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' new reserved qty2 '||l_reserved_qty2);
272: END IF;
273:
274: inv_reservation_pub.transfer_reservation
267: end if;
268:
269: IF g_debug <= gme_debug.g_log_procedure THEN
270: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' new reserved qty '||l_reserved_qty);
271: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' new reserved qty2 '||l_reserved_qty2);
272: END IF;
273:
274: inv_reservation_pub.transfer_reservation
275: (p_api_version_number => 1.0,
314: FND_MSG_PUB.count_and_get
315: ( p_count => x_msg_count
316: , p_data => x_msg_data
317: );
318: IF g_debug <= gme_debug.g_log_procedure THEN
319: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'u EXCEPTION: Expected');
320: END IF;
321: WHEN OTHERS THEN
322: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315: ( p_count => x_msg_count
316: , p_data => x_msg_data
317: );
318: IF g_debug <= gme_debug.g_log_procedure THEN
319: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'u EXCEPTION: Expected');
320: END IF;
321: WHEN OTHERS THEN
322: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
323:
328: FND_MSG_PUB.count_and_get
329: ( p_count => x_msg_count
330: , p_data => x_msg_data
331: );
332: IF g_debug <= gme_debug.g_log_procedure THEN
333: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'u EXCEPTION: Others');
334: END IF;
335:
336: END create_reservation_from_FPO;
329: ( p_count => x_msg_count
330: , p_data => x_msg_data
331: );
332: IF g_debug <= gme_debug.g_log_procedure THEN
333: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'u EXCEPTION: Others');
334: END IF;
335:
336: END create_reservation_from_FPO;
337:
463: BEGIN
464:
465: x_return_status := FND_API.G_RET_STS_SUCCESS;
466:
467: IF g_debug <= gme_debug.g_log_procedure THEN
468: gme_debug.put_line ('Entering api '
469: || g_pkg_name || '.'
470: || l_api_name);
471: END IF;
464:
465: x_return_status := FND_API.G_RET_STS_SUCCESS;
466:
467: IF g_debug <= gme_debug.g_log_procedure THEN
468: gme_debug.put_line ('Entering api '
469: || g_pkg_name || '.'
470: || l_api_name);
471: END IF;
472: l_session_id := USERENV('sessionid');
475: l_organization_id := p_organization_id;
476:
477: /* will send the work flow */
478: IF p_batch_id is not null THEN
479: IF g_debug <= gme_debug.g_log_procedure THEN
480: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Batch_id is'|| p_batch_id);
481: END IF;
482: OPEN so_line_id_for_batch(p_batch_id);
483: FETCH so_line_id_for_batch INTO l_so_line_id;
476:
477: /* will send the work flow */
478: IF p_batch_id is not null THEN
479: IF g_debug <= gme_debug.g_log_procedure THEN
480: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Batch_id is'|| p_batch_id);
481: END IF;
482: OPEN so_line_id_for_batch(p_batch_id);
483: FETCH so_line_id_for_batch INTO l_so_line_id;
484: IF(so_line_id_for_batch%NOTFOUND) THEN
482: OPEN so_line_id_for_batch(p_batch_id);
483: FETCH so_line_id_for_batch INTO l_so_line_id;
484: IF(so_line_id_for_batch%NOTFOUND) THEN
485: CLOSE so_line_id_for_batch;
486: IF g_debug <= gme_debug.g_log_procedure THEN
487: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' so_line_id_for_batch%NOTFOUND, returning from Notify CSR');
488: END IF;
489: RETURN;
490: END IF;
483: FETCH so_line_id_for_batch INTO l_so_line_id;
484: IF(so_line_id_for_batch%NOTFOUND) THEN
485: CLOSE so_line_id_for_batch;
486: IF g_debug <= gme_debug.g_log_procedure THEN
487: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' so_line_id_for_batch%NOTFOUND, returning from Notify CSR');
488: END IF;
489: RETURN;
490: END IF;
491:
494: OPEN get_batch_type(p_batch_id);
495: FETCH get_batch_type INTO l_batch_type;
496: IF(get_batch_type%NOTFOUND) THEN
497: CLOSE get_batch_type;
498: IF g_debug <= gme_debug.g_log_procedure THEN
499: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' get_batch_type%NOTFOUND, returning from Notify CSR');
500: END IF;
501: RETURN;
502: END IF;
495: FETCH get_batch_type INTO l_batch_type;
496: IF(get_batch_type%NOTFOUND) THEN
497: CLOSE get_batch_type;
498: IF g_debug <= gme_debug.g_log_procedure THEN
499: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' get_batch_type%NOTFOUND, returning from Notify CSR');
500: END IF;
501: RETURN;
502: END IF;
503:
505:
506: END IF;
507:
508: IF p_batch_line_id is not null THEN
509: IF g_debug <= gme_debug.g_log_procedure THEN
510: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Batch_line_id is '|| p_batch_line_id);
511: END IF;
512: OPEN so_line_id_for_batch_line(p_batch_line_id);
513: FETCH so_line_id_for_batch_line INTO l_so_line_id;
506: END IF;
507:
508: IF p_batch_line_id is not null THEN
509: IF g_debug <= gme_debug.g_log_procedure THEN
510: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Batch_line_id is '|| p_batch_line_id);
511: END IF;
512: OPEN so_line_id_for_batch_line(p_batch_line_id);
513: FETCH so_line_id_for_batch_line INTO l_so_line_id;
514: IF(so_line_id_for_batch_line%NOTFOUND) THEN
512: OPEN so_line_id_for_batch_line(p_batch_line_id);
513: FETCH so_line_id_for_batch_line INTO l_so_line_id;
514: IF(so_line_id_for_batch_line%NOTFOUND) THEN
515: CLOSE so_line_id_for_batch_line;
516: IF g_debug <= gme_debug.g_log_procedure THEN
517: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' so_line_id_for_batch_line%NOTFOUND, returning from Notify CSR');
518: END IF;
519: RETURN;
520: END IF;
513: FETCH so_line_id_for_batch_line INTO l_so_line_id;
514: IF(so_line_id_for_batch_line%NOTFOUND) THEN
515: CLOSE so_line_id_for_batch_line;
516: IF g_debug <= gme_debug.g_log_procedure THEN
517: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' so_line_id_for_batch_line%NOTFOUND, returning from Notify CSR');
518: END IF;
519: RETURN;
520: END IF;
521: CLOSE so_line_id_for_batch_line;
523: OPEN get_batch_id_for_line(p_batch_line_id);
524: FETCH get_batch_id_for_line INTO l_batch_id,l_batch_type;
525: IF(get_batch_id_for_line%NOTFOUND) THEN
526: CLOSE get_batch_id_for_line;
527: IF g_debug <= gme_debug.g_log_procedure THEN
528: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' get_batch_id_for_line%NOTFOUND, returning from Notify CSR');
529: END IF;
530: RETURN;
531: END IF;
524: FETCH get_batch_id_for_line INTO l_batch_id,l_batch_type;
525: IF(get_batch_id_for_line%NOTFOUND) THEN
526: CLOSE get_batch_id_for_line;
527: IF g_debug <= gme_debug.g_log_procedure THEN
528: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' get_batch_id_for_line%NOTFOUND, returning from Notify CSR');
529: END IF;
530: RETURN;
531: END IF;
532: CLOSE get_batch_id_for_line;
532: CLOSE get_batch_id_for_line;
533:
534: END IF;
535:
536: IF g_debug <= gme_debug.g_log_procedure THEN
537: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : so_line_id is'|| l_so_line_id);
538: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_organization_id is'|| l_organization_id);
539: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_action_code is'|| p_action_code);
540: END IF;
533:
534: END IF;
535:
536: IF g_debug <= gme_debug.g_log_procedure THEN
537: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : so_line_id is'|| l_so_line_id);
538: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_organization_id is'|| l_organization_id);
539: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_action_code is'|| p_action_code);
540: END IF;
541:
534: END IF;
535:
536: IF g_debug <= gme_debug.g_log_procedure THEN
537: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : so_line_id is'|| l_so_line_id);
538: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_organization_id is'|| l_organization_id);
539: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_action_code is'|| p_action_code);
540: END IF;
541:
542: l_action_code := p_action_code;
535:
536: IF g_debug <= gme_debug.g_log_procedure THEN
537: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : so_line_id is'|| l_so_line_id);
538: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_organization_id is'|| l_organization_id);
539: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_action_code is'|| p_action_code);
540: END IF;
541:
542: l_action_code := p_action_code;
543:
553: IF(p_action_code = 'CMPLT_DATE_CHANGE') THEN
554: l_action_code := 'PLAN_COMPL_DATE_CHANGED';
555: END IF;
556:
557: IF g_debug <= gme_debug.g_log_procedure THEN
558: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_action_code is'|| l_action_code);
559: END IF;
560:
561: OPEN CSR_for_so_line(l_so_line_id);
554: l_action_code := 'PLAN_COMPL_DATE_CHANGED';
555: END IF;
556:
557: IF g_debug <= gme_debug.g_log_procedure THEN
558: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_action_code is'|| l_action_code);
559: END IF;
560:
561: OPEN CSR_for_so_line(l_so_line_id);
562: FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_so_header_id;
561: OPEN CSR_for_so_line(l_so_line_id);
562: FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_so_header_id;
563: IF(CSR_for_so_line%NOTFOUND) THEN
564: CLOSE CSR_for_so_line;
565: IF g_debug <= gme_debug.g_log_procedure THEN
566: gme_debug.put_line(g_pkg_name||'.'||l_api_name ||' CSR_for_so_line%NOTFOUND, returning from Notify CSR');
567: END IF;
568: RETURN;
569: END IF;
562: FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_so_header_id;
563: IF(CSR_for_so_line%NOTFOUND) THEN
564: CLOSE CSR_for_so_line;
565: IF g_debug <= gme_debug.g_log_procedure THEN
566: gme_debug.put_line(g_pkg_name||'.'||l_api_name ||' CSR_for_so_line%NOTFOUND, returning from Notify CSR');
567: END IF;
568: RETURN;
569: END IF;
570: CLOSE CSR_for_so_line;
567: END IF;
568: RETURN;
569: END IF;
570: CLOSE CSR_for_so_line;
571: IF g_debug <= gme_debug.g_log_procedure THEN
572: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_last_updated_by is '||l_last_updated_by);
573: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Business Event......');
574: END IF;
575:
568: RETURN;
569: END IF;
570: CLOSE CSR_for_so_line;
571: IF g_debug <= gme_debug.g_log_procedure THEN
572: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_last_updated_by is '||l_last_updated_by);
573: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Business Event......');
574: END IF;
575:
576: -- MakeToOrder BEGIN
569: END IF;
570: CLOSE CSR_for_so_line;
571: IF g_debug <= gme_debug.g_log_procedure THEN
572: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_last_updated_by is '||l_last_updated_by);
573: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Business Event......');
574: END IF;
575:
576: -- MakeToOrder BEGIN
577: IF p_action_code = 'NEW_BATCH_CREATED' THEN
574: END IF;
575:
576: -- MakeToOrder BEGIN
577: IF p_action_code = 'NEW_BATCH_CREATED' THEN
578: IF g_debug <= gme_debug.g_log_procedure THEN
579: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Retrieve any pending lot for line '||p_batch_line_id);
580: END IF;
581: OPEN Get_pending_lot(p_batch_id ,p_batch_line_id );
582: FETCH Get_pending_lot INTO l_lot_number;
575:
576: -- MakeToOrder BEGIN
577: IF p_action_code = 'NEW_BATCH_CREATED' THEN
578: IF g_debug <= gme_debug.g_log_procedure THEN
579: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Retrieve any pending lot for line '||p_batch_line_id);
580: END IF;
581: OPEN Get_pending_lot(p_batch_id ,p_batch_line_id );
582: FETCH Get_pending_lot INTO l_lot_number;
583: IF(Get_pending_lot%NOTFOUND) THEN
580: END IF;
581: OPEN Get_pending_lot(p_batch_id ,p_batch_line_id );
582: FETCH Get_pending_lot INTO l_lot_number;
583: IF(Get_pending_lot%NOTFOUND) THEN
584: IF g_debug <= gme_debug.g_log_procedure THEN
585: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' no pending lot exists for line '|| p_batch_line_id);
586: END IF;
587: END IF;
588:
581: OPEN Get_pending_lot(p_batch_id ,p_batch_line_id );
582: FETCH Get_pending_lot INTO l_lot_number;
583: IF(Get_pending_lot%NOTFOUND) THEN
584: IF g_debug <= gme_debug.g_log_procedure THEN
585: gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' no pending lot exists for line '|| p_batch_line_id);
586: END IF;
587: END IF;
588:
589: CLOSE Get_pending_lot;
610: wf_event.AddParameterToList('FPO_ID',p_fpo_id ,l_parameter_list);
611: wf_event.AddParameterToList('ORGANIZATION_ID',l_organization_id ,l_parameter_list);
612: wf_event.AddParameterToList('ACTION_CODE',l_action_code ,l_parameter_list);
613: wf_event.AddParameterToList('LOT_NO',l_lot_number,l_parameter_list);
614: IF g_debug <= gme_debug.g_log_procedure THEN
615: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : raising business event ');
616: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : session_id '|| l_session_id );
617: End if;
618: wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
611: wf_event.AddParameterToList('ORGANIZATION_ID',l_organization_id ,l_parameter_list);
612: wf_event.AddParameterToList('ACTION_CODE',l_action_code ,l_parameter_list);
613: wf_event.AddParameterToList('LOT_NO',l_lot_number,l_parameter_list);
614: IF g_debug <= gme_debug.g_log_procedure THEN
615: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : raising business event ');
616: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : session_id '|| l_session_id );
617: End if;
618: wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
619: p_event_key => l_session_id,
612: wf_event.AddParameterToList('ACTION_CODE',l_action_code ,l_parameter_list);
613: wf_event.AddParameterToList('LOT_NO',l_lot_number,l_parameter_list);
614: IF g_debug <= gme_debug.g_log_procedure THEN
615: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : raising business event ');
616: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : session_id '|| l_session_id );
617: End if;
618: wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
619: p_event_key => l_session_id,
620: p_parameters => l_parameter_list);
620: p_parameters => l_parameter_list);
621:
622: IF(l_last_updated_by <> l_created_by) THEN
623:
624: IF g_debug <= gme_debug.g_log_procedure THEN
625: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
626: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
627: END IF;
628:
621:
622: IF(l_last_updated_by <> l_created_by) THEN
623:
624: IF g_debug <= gme_debug.g_log_procedure THEN
625: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
626: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
627: END IF;
628:
629: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
622: IF(l_last_updated_by <> l_created_by) THEN
623:
624: IF g_debug <= gme_debug.g_log_procedure THEN
625: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
626: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
627: END IF;
628:
629: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
630: , p_approver => l_created_by
655: IF l_new_header_id <> l_old_header_id THEN
656: l_so_header_id := l_new_header_id;
657: l_old_header_id := l_new_header_id;
658:
659: IF g_debug <= gme_debug.g_log_procedure THEN
660: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
661: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
662: END IF;
663: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
656: l_so_header_id := l_new_header_id;
657: l_old_header_id := l_new_header_id;
658:
659: IF g_debug <= gme_debug.g_log_procedure THEN
660: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
661: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
662: END IF;
663: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
664: , p_approver => l_last_updated_by
657: l_old_header_id := l_new_header_id;
658:
659: IF g_debug <= gme_debug.g_log_procedure THEN
660: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
661: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
662: END IF;
663: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
664: , p_approver => l_last_updated_by
665: , p_so_header_id=> l_so_header_id
678: p_parameters => l_parameter_list);
679:
680: IF(l_last_updated_by <> l_created_by) THEN
681:
682: IF g_debug <= gme_debug.g_log_procedure THEN
683: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
684: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
685: END IF;
686:
679:
680: IF(l_last_updated_by <> l_created_by) THEN
681:
682: IF g_debug <= gme_debug.g_log_procedure THEN
683: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
684: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
685: END IF;
686:
687: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
680: IF(l_last_updated_by <> l_created_by) THEN
681:
682: IF g_debug <= gme_debug.g_log_procedure THEN
683: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
684: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
685: END IF;
686:
687: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
688: , p_approver => l_created_by
711: IF l_new_header_id <> l_old_header_id THEN
712: l_so_header_id := l_new_header_id;
713: l_old_header_id := l_new_header_id;
714:
715: IF g_debug <= gme_debug.g_log_procedure THEN
716: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
717: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
718: END IF;
719: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
712: l_so_header_id := l_new_header_id;
713: l_old_header_id := l_new_header_id;
714:
715: IF g_debug <= gme_debug.g_log_procedure THEN
716: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
717: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
718: END IF;
719: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
720: , p_approver => l_last_updated_by
713: l_old_header_id := l_new_header_id;
714:
715: IF g_debug <= gme_debug.g_log_procedure THEN
716: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
717: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
718: END IF;
719: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
720: , p_approver => l_last_updated_by
721: , p_so_header_id=> l_so_header_id
734: p_parameters => l_parameter_list);
735:
736: IF(l_last_updated_by <> l_created_by) THEN
737:
738: IF g_debug <= gme_debug.g_log_procedure THEN
739: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
740: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
741: END IF;
742:
735:
736: IF(l_last_updated_by <> l_created_by) THEN
737:
738: IF g_debug <= gme_debug.g_log_procedure THEN
739: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
740: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
741: END IF;
742:
743: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
736: IF(l_last_updated_by <> l_created_by) THEN
737:
738: IF g_debug <= gme_debug.g_log_procedure THEN
739: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
740: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
741: END IF;
742:
743: /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
744: , p_approver => l_created_by
760: END IF;
761: l_parameter_list.DELETE;
762:
763:
764: IF g_debug <= gme_debug.g_log_procedure THEN
765: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Exiting Notify_CSR .............');
766: END IF;
767:
768: EXCEPTION
761: l_parameter_list.DELETE;
762:
763:
764: IF g_debug <= gme_debug.g_log_procedure THEN
765: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Exiting Notify_CSR .............');
766: END IF;
767:
768: EXCEPTION
769:
767:
768: EXCEPTION
769:
770: WHEN OTHERS THEN
771: IF g_debug <= gme_debug.g_log_procedure THEN
772: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'WARNING.... In Others Exception in Notify CSR');
773: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
774: END IF;
775:
768: EXCEPTION
769:
770: WHEN OTHERS THEN
771: IF g_debug <= gme_debug.g_log_procedure THEN
772: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'WARNING.... In Others Exception in Notify CSR');
773: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
774: END IF;
775:
776: END notify_CSR;
769:
770: WHEN OTHERS THEN
771: IF g_debug <= gme_debug.g_log_procedure THEN
772: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'WARNING.... In Others Exception in Notify CSR');
773: gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
774: END IF;
775:
776: END notify_CSR;
777:
820: uom_conversion_error EXCEPTION;
821: get_trans_err EXCEPTION;
822: notify_CSR_err EXCEPTION;
823: BEGIN
824: IF g_debug <= gme_debug.g_log_procedure THEN
825: gme_debug.put_line ('Entering api '
826: || g_pkg_name || '.'
827: || l_api_name);
828: END IF;
821: get_trans_err EXCEPTION;
822: notify_CSR_err EXCEPTION;
823: BEGIN
824: IF g_debug <= gme_debug.g_log_procedure THEN
825: gme_debug.put_line ('Entering api '
826: || g_pkg_name || '.'
827: || l_api_name);
828: END IF;
829:
828: END IF;
829:
830: x_return_status := fnd_api.g_ret_sts_success;
831:
832: IF g_debug <= gme_debug.g_log_statement THEN
833: gme_debug.put_line
834: ( g_pkg_name
835: || '.'
836: || l_api_name
829:
830: x_return_status := fnd_api.g_ret_sts_success;
831:
832: IF g_debug <= gme_debug.g_log_statement THEN
833: gme_debug.put_line
834: ( g_pkg_name
835: || '.'
836: || l_api_name
837: || ' Retrieve data for transaction id => '
843: ,x_mmln_tbl => l_mmln_tbl
844: ,x_return_status => x_return_status);
845:
846: IF x_return_status <> fnd_api.g_ret_sts_success THEN
847: IF (g_debug <= gme_debug.g_log_statement) THEN
848: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get transactions');
849: END IF;
850: RAISE get_trans_err;
851: END IF;
844: ,x_return_status => x_return_status);
845:
846: IF x_return_status <> fnd_api.g_ret_sts_success THEN
847: IF (g_debug <= gme_debug.g_log_statement) THEN
848: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get transactions');
849: END IF;
850: RAISE get_trans_err;
851: END IF;
852:
856: l_yield_txn_counter := l_mmln_tbl.count;
857: ELSE
858: l_yield_txn_counter := 1;
859: END IF;
860: IF g_debug <= gme_debug.g_log_statement THEN
861: gme_debug.put_line
862: ( g_pkg_name
863: || '.'
864: || l_api_name
857: ELSE
858: l_yield_txn_counter := 1;
859: END IF;
860: IF g_debug <= gme_debug.g_log_statement THEN
861: gme_debug.put_line
862: ( g_pkg_name
863: || '.'
864: || l_api_name
865: || ' Looping through transaction yield for a count of => '
867: END IF;
868:
869: FOR x in 1..l_yield_txn_counter LOOP
870: IF l_SO_RSV_exist = 'N' THEN
871: IF g_debug <= gme_debug.g_log_statement THEN
872: gme_debug.put_line
873: ( g_pkg_name
874: || '.'
875: || l_api_name
868:
869: FOR x in 1..l_yield_txn_counter LOOP
870: IF l_SO_RSV_exist = 'N' THEN
871: IF g_debug <= gme_debug.g_log_statement THEN
872: gme_debug.put_line
873: ( g_pkg_name
874: || '.'
875: || l_api_name
876: || 'No reservations outstanding for this supply line so exit now');
878: EXIT;
879: END IF;
880: IF NVL(l_mmln_tbl.count,0) > 0 THEN
881: -- If mmli is populated, use the lot level data
882: IF g_debug <= gme_debug.g_log_statement THEN
883: gme_debug.put_line
884: ( g_pkg_name
885: || '.'
886: || l_api_name
879: END IF;
880: IF NVL(l_mmln_tbl.count,0) > 0 THEN
881: -- If mmli is populated, use the lot level data
882: IF g_debug <= gme_debug.g_log_statement THEN
883: gme_debug.put_line
884: ( g_pkg_name
885: || '.'
886: || l_api_name
887: || ' Using lot level transaction data, lot is => '
892: l_txn_secondary:= l_mmln_tbl(x).secondary_transaction_quantity;
893: l_txn_lot := l_mmln_tbl(x).lot_number;
894: ELSE
895: -- Use the transaction level data
896: IF g_debug <= gme_debug.g_log_statement THEN
897: gme_debug.put_line
898: ( g_pkg_name
899: || '.'
900: || l_api_name
893: l_txn_lot := l_mmln_tbl(x).lot_number;
894: ELSE
895: -- Use the transaction level data
896: IF g_debug <= gme_debug.g_log_statement THEN
897: gme_debug.put_line
898: ( g_pkg_name
899: || '.'
900: || l_api_name
901: || ' Item is NOT lot controlled ');
905: l_txn_secondary:= l_mmt_rec.secondary_transaction_quantity;
906: l_txn_lot := NULL;
907: END IF;
908:
909: IF g_debug <= gme_debug.g_log_statement THEN
910: gme_debug.put_line
911: ( g_pkg_name
912: || '.'
913: || l_api_name
906: l_txn_lot := NULL;
907: END IF;
908:
909: IF g_debug <= gme_debug.g_log_statement THEN
910: gme_debug.put_line
911: ( g_pkg_name
912: || '.'
913: || l_api_name
914: || ' primary transaction quantity to be processed is '
933: ,x_msg_count => x_message_count
934: ,x_msg_data => x_message_list
935: ,x_return_status => x_return_status);
936:
937: IF g_debug <= gme_debug.g_log_statement THEN
938: gme_debug.put_line
939: ( g_pkg_name
940: || '.'
941: || l_api_name
934: ,x_msg_data => x_message_list
935: ,x_return_status => x_return_status);
936:
937: IF g_debug <= gme_debug.g_log_statement THEN
938: gme_debug.put_line
939: ( g_pkg_name
940: || '.'
941: || l_api_name
942: || 'Return status from query_prod_supply_reservations is '
940: || '.'
941: || l_api_name
942: || 'Return status from query_prod_supply_reservations is '
943: || x_return_status);
944: gme_debug.put_line
945: ( g_pkg_name
946: || '.'
947: || l_api_name
948: || ' number of reservations is '
949: || l_rsv_count);
950: END IF;
951:
952: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
953: IF g_debug <= gme_debug.g_log_statement THEN
954: gme_debug.put_line
955: ( g_pkg_name
956: || '.'
957: || l_api_name
950: END IF;
951:
952: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
953: IF g_debug <= gme_debug.g_log_statement THEN
954: gme_debug.put_line
955: ( g_pkg_name
956: || '.'
957: || l_api_name
958: || 'Error is :'
963:
964: -- if there are no reservations then there is no work to do so exit now
965: -- ====================================================================
966: IF NVL(l_rsv_count,0) = 0 THEN
967: IF g_debug <= gme_debug.g_log_statement THEN
968: gme_debug.put_line
969: ( g_pkg_name
970: || '.'
971: || l_api_name
964: -- if there are no reservations then there is no work to do so exit now
965: -- ====================================================================
966: IF NVL(l_rsv_count,0) = 0 THEN
967: IF g_debug <= gme_debug.g_log_statement THEN
968: gme_debug.put_line
969: ( g_pkg_name
970: || '.'
971: || l_api_name
972: || 'No SO reservation demand so EXIT now ');
978: l_rsv_rec := l_rsv_tbl (1);
979:
980: -- We will transfer the reserved quantity OR the transaction quantity; whichever is the smaller
981: -- ================================================================================================
982: IF g_debug <= gme_debug.g_log_statement THEN
983: gme_debug.put_line
984: ( g_pkg_name
985: || '.'
986: || l_api_name
979:
980: -- We will transfer the reserved quantity OR the transaction quantity; whichever is the smaller
981: -- ================================================================================================
982: IF g_debug <= gme_debug.g_log_statement THEN
983: gme_debug.put_line
984: ( g_pkg_name
985: || '.'
986: || l_api_name
987: || ' Transaction primary quantity is '
1009: , to_unit => l_rsv_rec.secondary_uom_code
1010: , from_name => NULL -- from uom name
1011: , to_name => NULL -- to uom name
1012: );
1013: IF g_debug <= gme_debug.g_log_statement THEN
1014: gme_debug.put_line
1015: ( g_pkg_name
1016: || '.'
1017: || l_api_name
1010: , from_name => NULL -- from uom name
1011: , to_name => NULL -- to uom name
1012: );
1013: IF g_debug <= gme_debug.g_log_statement THEN
1014: gme_debug.put_line
1015: ( g_pkg_name
1016: || '.'
1017: || l_api_name
1018: || ' After UOM conversion the secondary qty is '
1024: RAISE uom_conversion_error;
1025: END IF;
1026: END IF;
1027: END IF;
1028: IF g_debug <= gme_debug.g_log_statement THEN
1029: gme_debug.put_line
1030: ( g_pkg_name
1031: || '.'
1032: || l_api_name
1025: END IF;
1026: END IF;
1027: END IF;
1028: IF g_debug <= gme_debug.g_log_statement THEN
1029: gme_debug.put_line
1030: ( g_pkg_name
1031: || '.'
1032: || l_api_name
1033: || ' primary quantity to be reserved against INV is'
1042: l_transfer_quantity := l_transfer_secondary;
1043: ELSE
1044: l_transfer_quantity := NULL;
1045: END IF;
1046: IF g_debug <= gme_debug.g_log_statement THEN
1047: gme_debug.put_line
1048: ( g_pkg_name
1049: || '.'
1050: || l_api_name
1043: ELSE
1044: l_transfer_quantity := NULL;
1045: END IF;
1046: IF g_debug <= gme_debug.g_log_statement THEN
1047: gme_debug.put_line
1048: ( g_pkg_name
1049: || '.'
1050: || l_api_name
1051: || ' This is the primary quantity we are about to transfer => '
1066: -- Target Inventory IS RESERVABLE
1067: -- Transfer the reservation supply source from JOB to INVENTORY
1068: -- Use the detailing outlined on the COMPLETION transaction
1069: -- ============================================================
1070: IF g_debug <= gme_debug.g_log_statement THEN
1071: gme_debug.put_line
1072: ( g_pkg_name
1073: || '.'
1074: || l_api_name
1067: -- Transfer the reservation supply source from JOB to INVENTORY
1068: -- Use the detailing outlined on the COMPLETION transaction
1069: -- ============================================================
1070: IF g_debug <= gme_debug.g_log_statement THEN
1071: gme_debug.put_line
1072: ( g_pkg_name
1073: || '.'
1074: || l_api_name
1075: || ' This inventory yield IS reservable so proceed with transfer ');
1091: l_to_rsv_rec.reservation_quantity := l_transfer_quantity;
1092: l_to_rsv_rec.primary_reservation_quantity := l_transfer_primary;
1093: l_to_rsv_rec.secondary_reservation_quantity := l_transfer_secondary;
1094:
1095: IF (g_debug <= gme_debug.g_log_statement) THEN
1096: gme_debug.put_line ('Calling inv_reservation_pub.transfer_reservation');
1097: END IF;
1098:
1099: inv_reservation_pub.Transfer_Reservation
1092: l_to_rsv_rec.primary_reservation_quantity := l_transfer_primary;
1093: l_to_rsv_rec.secondary_reservation_quantity := l_transfer_secondary;
1094:
1095: IF (g_debug <= gme_debug.g_log_statement) THEN
1096: gme_debug.put_line ('Calling inv_reservation_pub.transfer_reservation');
1097: END IF;
1098:
1099: inv_reservation_pub.Transfer_Reservation
1100: ( p_api_version_number => 1.0
1108: , p_to_serial_number => l_dummy_sn
1109: , p_validation_flag => FND_API.G_FALSE
1110: , x_to_reservation_id => l_rsv_id);
1111:
1112: IF (g_debug <= gme_debug.g_log_statement) THEN
1113: gme_debug.put_line ('After Calling inv_reservation_pub.transfer_reservation status is '||x_return_status);
1114: END IF;
1115:
1116: IF (l_return_status IN
1109: , p_validation_flag => FND_API.G_FALSE
1110: , x_to_reservation_id => l_rsv_id);
1111:
1112: IF (g_debug <= gme_debug.g_log_statement) THEN
1113: gme_debug.put_line ('After Calling inv_reservation_pub.transfer_reservation status is '||x_return_status);
1114: END IF;
1115:
1116: IF (l_return_status IN
1117: (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1120: ELSE
1121: -- Target Inventory IS NOT RESERVABLE so we cannot transfer the reservation
1122: -- Need to debit the original SUPPLY reservation and notify sales staff is there is sales impact
1123: -- =============================================================================================
1124: IF g_debug <= gme_debug.g_log_statement THEN
1125: gme_debug.put_line
1126: ( g_pkg_name
1127: || '.'
1128: || l_api_name
1121: -- Target Inventory IS NOT RESERVABLE so we cannot transfer the reservation
1122: -- Need to debit the original SUPPLY reservation and notify sales staff is there is sales impact
1123: -- =============================================================================================
1124: IF g_debug <= gme_debug.g_log_statement THEN
1125: gme_debug.put_line
1126: ( g_pkg_name
1127: || '.'
1128: || l_api_name
1129: || ' This inventory yield IS NOT reservable so CANNOT transfer resv supply for line_id '
1142: , X_return_status => x_return_status
1143: , X_msg_cont => x_message_count
1144: , X_msg_data => x_message_list );
1145:
1146: IF g_debug <= gme_debug.g_log_procedure THEN
1147: gme_debug.put_line ( g_pkg_name || '.'
1148: || l_api_name
1149: || ' after calling notify_CSR status is '
1150: || x_return_status );
1143: , X_msg_cont => x_message_count
1144: , X_msg_data => x_message_list );
1145:
1146: IF g_debug <= gme_debug.g_log_procedure THEN
1147: gme_debug.put_line ( g_pkg_name || '.'
1148: || l_api_name
1149: || ' after calling notify_CSR status is '
1150: || x_return_status );
1151: END IF;
1155: END IF;
1156:
1157: END IF;
1158:
1159: IF g_debug <= gme_debug.g_log_statement THEN
1160: gme_debug.put_line
1161: ( g_pkg_name
1162: || '.'
1163: || l_api_name
1156:
1157: END IF;
1158:
1159: IF g_debug <= gme_debug.g_log_statement THEN
1160: gme_debug.put_line
1161: ( g_pkg_name
1162: || '.'
1163: || l_api_name
1164: || ' Invoke relieve reservation for quantity of '
1181: , x_primary_remain_quantity => l_primary_remain
1182: , x_secondary_remain_quantity => l_secondary_remain
1183: );
1184:
1185: IF g_debug <= gme_debug.g_log_procedure THEN
1186: gme_debug.put_line ( g_pkg_name || '.'
1187: || l_api_name
1188: || ' after calling relieve_reservation status is '
1189: || x_return_status );
1182: , x_secondary_remain_quantity => l_secondary_remain
1183: );
1184:
1185: IF g_debug <= gme_debug.g_log_procedure THEN
1186: gme_debug.put_line ( g_pkg_name || '.'
1187: || l_api_name
1188: || ' after calling relieve_reservation status is '
1189: || x_return_status );
1190: END IF;
1206: END IF;
1207: END LOOP; -- WHILE l_l_transfer_complete = 'N'
1208: END LOOP; -- Transaction Quantity Processing
1209:
1210: IF g_debug <= gme_debug.g_log_procedure THEN
1211: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1212: END IF;
1213:
1214: EXCEPTION
1207: END LOOP; -- WHILE l_l_transfer_complete = 'N'
1208: END LOOP; -- Transaction Quantity Processing
1209:
1210: IF g_debug <= gme_debug.g_log_procedure THEN
1211: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1212: END IF;
1213:
1214: EXCEPTION
1215: WHEN uom_conversion_error THEN
1217: fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1218: fnd_message.set_token ('ITEM_NO', l_rsv_rec.inventory_item_id);
1219: fnd_message.set_token ('FROM_UOM', l_rsv_rec.primary_uom_code);
1220: fnd_message.set_token ('TO_UOM', l_rsv_rec.secondary_uom_code);
1221: IF (g_debug <= gme_debug.g_log_error) THEN
1222: gme_debug.put_line
1223: ( 'unit of measure conversion error for item '
1224: || l_rsv_rec.inventory_item_id
1225: || 'from unit '
1218: fnd_message.set_token ('ITEM_NO', l_rsv_rec.inventory_item_id);
1219: fnd_message.set_token ('FROM_UOM', l_rsv_rec.primary_uom_code);
1220: fnd_message.set_token ('TO_UOM', l_rsv_rec.secondary_uom_code);
1221: IF (g_debug <= gme_debug.g_log_error) THEN
1222: gme_debug.put_line
1223: ( 'unit of measure conversion error for item '
1224: || l_rsv_rec.inventory_item_id
1225: || 'from unit '
1226: || l_rsv_rec.primary_uom_code
1227: || 'to unit '
1228: || l_rsv_rec.secondary_uom_code);
1229: END IF;
1230: WHEN transfer_reservation_err THEN
1231: IF (g_debug <= gme_debug.g_log_error) THEN
1232: gme_debug.put_line
1233: ( 'terminating due to error in inv_reservation_pub.transfer_reservation ');
1234: gme_debug.put_line ('error message is ' || x_message_list);
1235: END IF;
1228: || l_rsv_rec.secondary_uom_code);
1229: END IF;
1230: WHEN transfer_reservation_err THEN
1231: IF (g_debug <= gme_debug.g_log_error) THEN
1232: gme_debug.put_line
1233: ( 'terminating due to error in inv_reservation_pub.transfer_reservation ');
1234: gme_debug.put_line ('error message is ' || x_message_list);
1235: END IF;
1236: WHEN update_reservation_err THEN
1230: WHEN transfer_reservation_err THEN
1231: IF (g_debug <= gme_debug.g_log_error) THEN
1232: gme_debug.put_line
1233: ( 'terminating due to error in inv_reservation_pub.transfer_reservation ');
1234: gme_debug.put_line ('error message is ' || x_message_list);
1235: END IF;
1236: WHEN update_reservation_err THEN
1237: IF (g_debug <= gme_debug.g_log_error) THEN
1238: gme_debug.put_line
1233: ( 'terminating due to error in inv_reservation_pub.transfer_reservation ');
1234: gme_debug.put_line ('error message is ' || x_message_list);
1235: END IF;
1236: WHEN update_reservation_err THEN
1237: IF (g_debug <= gme_debug.g_log_error) THEN
1238: gme_debug.put_line
1239: ( 'terminating due to error in inv_reservation_pub.update_reservation ');
1240: gme_debug.put_line ('error message is ' || x_message_list);
1241: END IF;
1234: gme_debug.put_line ('error message is ' || x_message_list);
1235: END IF;
1236: WHEN update_reservation_err THEN
1237: IF (g_debug <= gme_debug.g_log_error) THEN
1238: gme_debug.put_line
1239: ( 'terminating due to error in inv_reservation_pub.update_reservation ');
1240: gme_debug.put_line ('error message is ' || x_message_list);
1241: END IF;
1242: WHEN query_reservation_error THEN
1236: WHEN update_reservation_err THEN
1237: IF (g_debug <= gme_debug.g_log_error) THEN
1238: gme_debug.put_line
1239: ( 'terminating due to error in inv_reservation_pub.update_reservation ');
1240: gme_debug.put_line ('error message is ' || x_message_list);
1241: END IF;
1242: WHEN query_reservation_error THEN
1243: IF (g_debug <= gme_debug.g_log_error) THEN
1244: gme_debug.put_line
1239: ( 'terminating due to error in inv_reservation_pub.update_reservation ');
1240: gme_debug.put_line ('error message is ' || x_message_list);
1241: END IF;
1242: WHEN query_reservation_error THEN
1243: IF (g_debug <= gme_debug.g_log_error) THEN
1244: gme_debug.put_line
1245: ( 'terminating due to error querying reservations ');
1246: gme_debug.put_line ('error message is ' || x_message_list);
1247: END IF;
1240: gme_debug.put_line ('error message is ' || x_message_list);
1241: END IF;
1242: WHEN query_reservation_error THEN
1243: IF (g_debug <= gme_debug.g_log_error) THEN
1244: gme_debug.put_line
1245: ( 'terminating due to error querying reservations ');
1246: gme_debug.put_line ('error message is ' || x_message_list);
1247: END IF;
1248: WHEN notify_CSR_err THEN
1242: WHEN query_reservation_error THEN
1243: IF (g_debug <= gme_debug.g_log_error) THEN
1244: gme_debug.put_line
1245: ( 'terminating due to error querying reservations ');
1246: gme_debug.put_line ('error message is ' || x_message_list);
1247: END IF;
1248: WHEN notify_CSR_err THEN
1249: IF (g_debug <= gme_debug.g_log_error) THEN
1250: gme_debug.put_line
1245: ( 'terminating due to error querying reservations ');
1246: gme_debug.put_line ('error message is ' || x_message_list);
1247: END IF;
1248: WHEN notify_CSR_err THEN
1249: IF (g_debug <= gme_debug.g_log_error) THEN
1250: gme_debug.put_line
1251: ( 'terminating due to error invoking notify_CSR ');
1252: END IF;
1253: WHEN get_trans_err THEN
1246: gme_debug.put_line ('error message is ' || x_message_list);
1247: END IF;
1248: WHEN notify_CSR_err THEN
1249: IF (g_debug <= gme_debug.g_log_error) THEN
1250: gme_debug.put_line
1251: ( 'terminating due to error invoking notify_CSR ');
1252: END IF;
1253: WHEN get_trans_err THEN
1254: IF (g_debug <= gme_debug.g_log_error) THEN
1250: gme_debug.put_line
1251: ( 'terminating due to error invoking notify_CSR ');
1252: END IF;
1253: WHEN get_trans_err THEN
1254: IF (g_debug <= gme_debug.g_log_error) THEN
1255: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get_transactions');
1256: END IF;
1257:
1258: WHEN OTHERS THEN
1251: ( 'terminating due to error invoking notify_CSR ');
1252: END IF;
1253: WHEN get_trans_err THEN
1254: IF (g_debug <= gme_debug.g_log_error) THEN
1255: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get_transactions');
1256: END IF;
1257:
1258: WHEN OTHERS THEN
1259: IF g_debug <= gme_debug.g_log_unexpected THEN
1255: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get_transactions');
1256: END IF;
1257:
1258: WHEN OTHERS THEN
1259: IF g_debug <= gme_debug.g_log_unexpected THEN
1260: gme_debug.put_line ( 'When others exception in '
1261: || g_pkg_name
1262: || '.'
1263: || l_api_name
1256: END IF;
1257:
1258: WHEN OTHERS THEN
1259: IF g_debug <= gme_debug.g_log_unexpected THEN
1260: gme_debug.put_line ( 'When others exception in '
1261: || g_pkg_name
1262: || '.'
1263: || l_api_name
1264: || ' Error is '
1289: l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1290: -- ======================================================================
1291: query_reservation_error EXCEPTION;
1292: BEGIN
1293: IF g_debug <= gme_debug.g_log_procedure THEN
1294: gme_debug.put_line ('Entering api '
1295: || g_pkg_name || '.'
1296: || l_api_name);
1297: END IF;
1290: -- ======================================================================
1291: query_reservation_error EXCEPTION;
1292: BEGIN
1293: IF g_debug <= gme_debug.g_log_procedure THEN
1294: gme_debug.put_line ('Entering api '
1295: || g_pkg_name || '.'
1296: || l_api_name);
1297: END IF;
1298:
1307: l_rsv_rec_inq.supply_source_header_id := p_matl_dtl_rec.batch_id;
1308: l_rsv_rec_inq.supply_source_line_id := p_matl_dtl_rec.material_detail_id;
1309: -- l_rsv_rec_inq.demand_source_type_id := 2;
1310:
1311: IF g_debug <= gme_debug.g_log_statement THEN
1312: gme_debug.put_line
1313: ( g_pkg_name
1314: || '.'
1315: || l_api_name
1308: l_rsv_rec_inq.supply_source_line_id := p_matl_dtl_rec.material_detail_id;
1309: -- l_rsv_rec_inq.demand_source_type_id := 2;
1310:
1311: IF g_debug <= gme_debug.g_log_statement THEN
1312: gme_debug.put_line
1313: ( g_pkg_name
1314: || '.'
1315: || l_api_name
1316: || ' Calling inv_reservation_pub.query_reservation for reservations against PROD supply for line'
1322: ELSE
1323: l_rsv_rec_inq.supply_source_type_id := 5;
1324: l_rsv_rec_inq.supply_source_header_id := p_matl_dtl_rec.batch_id;
1325:
1326: IF g_debug <= gme_debug.g_log_statement THEN
1327: gme_debug.put_line
1328: ( g_pkg_name
1329: || '.'
1330: || l_api_name
1323: l_rsv_rec_inq.supply_source_type_id := 5;
1324: l_rsv_rec_inq.supply_source_header_id := p_matl_dtl_rec.batch_id;
1325:
1326: IF g_debug <= gme_debug.g_log_statement THEN
1327: gme_debug.put_line
1328: ( g_pkg_name
1329: || '.'
1330: || l_api_name
1331: || ' Calling inv_reservation_pub.query_reservation for reservations against PROD supply for batce'
1345: ,x_mtl_reservation_tbl => x_mtl_reservation_tbl
1346: ,x_mtl_reservation_tbl_count => x_mtl_reservation_tbl_count
1347: ,x_error_code => l_error_code);
1348:
1349: IF g_debug <= gme_debug.g_log_statement THEN
1350: gme_debug.put_line
1351: ( g_pkg_name
1352: || '.'
1353: || l_api_name
1346: ,x_mtl_reservation_tbl_count => x_mtl_reservation_tbl_count
1347: ,x_error_code => l_error_code);
1348:
1349: IF g_debug <= gme_debug.g_log_statement THEN
1350: gme_debug.put_line
1351: ( g_pkg_name
1352: || '.'
1353: || l_api_name
1354: || 'Return status from inv_reservation_pub.query_reservation is '
1360: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1361: RAISE query_reservation_error;
1362: END IF;
1363:
1364: IF g_debug <= gme_debug.g_log_statement THEN
1365: gme_debug.put_line
1366: ( g_pkg_name
1367: || '.'
1368: || l_api_name
1361: RAISE query_reservation_error;
1362: END IF;
1363:
1364: IF g_debug <= gme_debug.g_log_statement THEN
1365: gme_debug.put_line
1366: ( g_pkg_name
1367: || '.'
1368: || l_api_name
1369: || ' Number of reservations against this production supply line is '
1369: || ' Number of reservations against this production supply line is '
1370: || x_mtl_reservation_tbl_count);
1371: END IF;
1372:
1373: IF g_debug <= gme_debug.g_log_procedure THEN
1374: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1375: END IF;
1376:
1377: EXCEPTION
1370: || x_mtl_reservation_tbl_count);
1371: END IF;
1372:
1373: IF g_debug <= gme_debug.g_log_procedure THEN
1374: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1375: END IF;
1376:
1377: EXCEPTION
1378: WHEN query_reservation_error THEN
1375: END IF;
1376:
1377: EXCEPTION
1378: WHEN query_reservation_error THEN
1379: IF (g_debug <= gme_debug.g_log_error) THEN
1380: gme_debug.put_line
1381: ( 'inv_reservation_pub.query_reservation returns '
1382: || x_return_status);
1383: gme_debug.put_line ('error message is ' || x_msg_data);
1376:
1377: EXCEPTION
1378: WHEN query_reservation_error THEN
1379: IF (g_debug <= gme_debug.g_log_error) THEN
1380: gme_debug.put_line
1381: ( 'inv_reservation_pub.query_reservation returns '
1382: || x_return_status);
1383: gme_debug.put_line ('error message is ' || x_msg_data);
1384: END IF;
1379: IF (g_debug <= gme_debug.g_log_error) THEN
1380: gme_debug.put_line
1381: ( 'inv_reservation_pub.query_reservation returns '
1382: || x_return_status);
1383: gme_debug.put_line ('error message is ' || x_msg_data);
1384: END IF;
1385:
1386: WHEN OTHERS THEN
1387: IF g_debug <= gme_debug.g_log_unexpected THEN
1383: gme_debug.put_line ('error message is ' || x_msg_data);
1384: END IF;
1385:
1386: WHEN OTHERS THEN
1387: IF g_debug <= gme_debug.g_log_unexpected THEN
1388: gme_debug.put_line ( 'When others exception in '
1389: || g_pkg_name
1390: || '.'
1391: || l_api_name
1384: END IF;
1385:
1386: WHEN OTHERS THEN
1387: IF g_debug <= gme_debug.g_log_unexpected THEN
1388: gme_debug.put_line ( 'When others exception in '
1389: || g_pkg_name
1390: || '.'
1391: || l_api_name
1392: || ' Error is '
1423: update_reservation_err EXCEPTION;
1424: delete_reservation_err EXCEPTION;
1425: notify_CSR_err EXCEPTION;
1426: BEGIN
1427: IF g_debug <= gme_debug.g_log_procedure THEN
1428: gme_debug.put_line ('Entering api '
1429: || g_pkg_name || '.'
1430: || l_api_name
1431: || ' material_detail_id '
1424: delete_reservation_err EXCEPTION;
1425: notify_CSR_err EXCEPTION;
1426: BEGIN
1427: IF g_debug <= gme_debug.g_log_procedure THEN
1428: gme_debug.put_line ('Entering api '
1429: || g_pkg_name || '.'
1430: || l_api_name
1431: || ' material_detail_id '
1432: || p_matl_dtl_rec.material_detail_id);
1445: ,x_msg_count => x_msg_count
1446: ,x_msg_data => x_msg_data
1447: ,x_return_status => x_return_status);
1448:
1449: IF g_debug <= gme_debug.g_log_statement THEN
1450: gme_debug.put_line
1451: ( g_pkg_name
1452: || '.'
1453: || l_api_name
1446: ,x_msg_data => x_msg_data
1447: ,x_return_status => x_return_status);
1448:
1449: IF g_debug <= gme_debug.g_log_statement THEN
1450: gme_debug.put_line
1451: ( g_pkg_name
1452: || '.'
1453: || l_api_name
1454: || 'Return status from query_prod_supply_reservations is '
1452: || '.'
1453: || l_api_name
1454: || 'Return status from query_prod_supply_reservations is '
1455: || x_return_status);
1456: gme_debug.put_line
1457: ( g_pkg_name
1458: || '.'
1459: || l_api_name
1460: || ' number of reservations is '
1461: || l_rsv_count);
1462: END IF;
1463:
1464: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1465: IF g_debug <= gme_debug.g_log_statement THEN
1466: gme_debug.put_line
1467: ( g_pkg_name
1468: || '.'
1469: || l_api_name
1462: END IF;
1463:
1464: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1465: IF g_debug <= gme_debug.g_log_statement THEN
1466: gme_debug.put_line
1467: ( g_pkg_name
1468: || '.'
1469: || l_api_name
1470: || 'Error is :'
1473: RAISE query_reservation_error;
1474: END IF;
1475:
1476: IF NVL(l_rsv_count,0) <= 0 THEN
1477: IF g_debug <= gme_debug.g_log_statement THEN
1478: gme_debug.put_line
1479: ( g_pkg_name
1480: || '.'
1481: || l_api_name
1474: END IF;
1475:
1476: IF NVL(l_rsv_count,0) <= 0 THEN
1477: IF g_debug <= gme_debug.g_log_statement THEN
1478: gme_debug.put_line
1479: ( g_pkg_name
1480: || '.'
1481: || l_api_name
1482: || ' No reservations against material detail line as a source of supply so RETURN now ');
1504: ,to_name => NULL);
1505: END IF;
1506: -- Compare supply and demand. If supply meets/exceeds demand, there is nothing to do
1507: -- =================================================================================
1508: IF g_debug <= gme_debug.g_log_statement THEN
1509: gme_debug.put_line
1510: ( g_pkg_name
1511: || '.'
1512: || l_api_name
1505: END IF;
1506: -- Compare supply and demand. If supply meets/exceeds demand, there is nothing to do
1507: -- =================================================================================
1508: IF g_debug <= gme_debug.g_log_statement THEN
1509: gme_debug.put_line
1510: ( g_pkg_name
1511: || '.'
1512: || l_api_name
1513: || ' Current demand figure => '
1515: || ' Current supply figure => '
1516: || l_available_quantity);
1517: END IF;
1518: IF l_available_quantity >= l_total_primary_demand THEN
1519: IF g_debug <= gme_debug.g_log_statement THEN
1520: gme_debug.put_line
1521: ( g_pkg_name
1522: || '.'
1523: || l_api_name
1516: || l_available_quantity);
1517: END IF;
1518: IF l_available_quantity >= l_total_primary_demand THEN
1519: IF g_debug <= gme_debug.g_log_statement THEN
1520: gme_debug.put_line
1521: ( g_pkg_name
1522: || '.'
1523: || l_api_name
1524: || ' Supply exceeds Demand so no adjustments required; return now ');
1530:
1531: FOR I IN 1..l_rsv_tbl.COUNT LOOP
1532:
1533: l_rsv_rec := l_rsv_tbl(I);
1534: IF g_debug <= gme_debug.g_log_procedure THEN
1535: gme_debug.put_line ( g_pkg_name || '.'
1536: || l_api_name
1537: || ' primary reserved qty '
1538: || l_rsv_rec.primary_reservation_quantity);
1531: FOR I IN 1..l_rsv_tbl.COUNT LOOP
1532:
1533: l_rsv_rec := l_rsv_tbl(I);
1534: IF g_debug <= gme_debug.g_log_procedure THEN
1535: gme_debug.put_line ( g_pkg_name || '.'
1536: || l_api_name
1537: || ' primary reserved qty '
1538: || l_rsv_rec.primary_reservation_quantity);
1539: END IF;
1550: , X_return_status => x_return_status
1551: , X_msg_cont => x_msg_count
1552: , X_msg_data => x_msg_data );
1553:
1554: IF g_debug <= gme_debug.g_log_procedure THEN
1555: gme_debug.put_line ( g_pkg_name || '.'
1556: || l_api_name
1557: || ' after calling notify_CSR status is '
1558: || x_return_status );
1551: , X_msg_cont => x_msg_count
1552: , X_msg_data => x_msg_data );
1553:
1554: IF g_debug <= gme_debug.g_log_procedure THEN
1555: gme_debug.put_line ( g_pkg_name || '.'
1556: || l_api_name
1557: || ' after calling notify_CSR status is '
1558: || x_return_status );
1559: END IF;
1563: RAISE notify_CSR_err;
1564: END IF;
1565:
1566: IF (l_rsv_rec.primary_reservation_quantity <= l_primary_to_unreserve) THEN
1567: IF g_debug <= gme_debug.g_log_procedure THEN
1568: gme_debug.put_line ( g_pkg_name || '.'
1569: || l_api_name
1570: || ' about to invoke delete_reservation ');
1571: END IF;
1564: END IF;
1565:
1566: IF (l_rsv_rec.primary_reservation_quantity <= l_primary_to_unreserve) THEN
1567: IF g_debug <= gme_debug.g_log_procedure THEN
1568: gme_debug.put_line ( g_pkg_name || '.'
1569: || l_api_name
1570: || ' about to invoke delete_reservation ');
1571: END IF;
1572:
1579: , p_rsv_rec => l_rsv_rec
1580: , p_serial_number => l_dummy_sn
1581: );
1582:
1583: IF g_debug <= gme_debug.g_log_procedure THEN
1584: gme_debug.put_line ( g_pkg_name || '.'
1585: || l_api_name
1586: || ' after calling delete_reservation status is '
1587: || x_return_status );
1580: , p_serial_number => l_dummy_sn
1581: );
1582:
1583: IF g_debug <= gme_debug.g_log_procedure THEN
1584: gme_debug.put_line ( g_pkg_name || '.'
1585: || l_api_name
1586: || ' after calling delete_reservation status is '
1587: || x_return_status );
1588: END IF;
1611: , from_name => NULL
1612: , to_name => NULL);
1613: END IF;
1614:
1615: IF g_debug <= gme_debug.g_log_procedure THEN
1616: gme_debug.put_line ( g_pkg_name || '.'
1617: || l_api_name
1618: || 'qty to relieve => '
1619: || l_primary_to_unreserve
1612: , to_name => NULL);
1613: END IF;
1614:
1615: IF g_debug <= gme_debug.g_log_procedure THEN
1616: gme_debug.put_line ( g_pkg_name || '.'
1617: || l_api_name
1618: || 'qty to relieve => '
1619: || l_primary_to_unreserve
1620: || ' secondary qty to relieve => '
1639: , x_primary_remain_quantity => l_primary_remain
1640: , x_secondary_remain_quantity => l_secondary_remain
1641: );
1642:
1643: IF g_debug <= gme_debug.g_log_procedure THEN
1644: gme_debug.put_line ( g_pkg_name || '.'
1645: || l_api_name
1646: || ' after calling relieve_reservation status is '
1647: || x_return_status );
1640: , x_secondary_remain_quantity => l_secondary_remain
1641: );
1642:
1643: IF g_debug <= gme_debug.g_log_procedure THEN
1644: gme_debug.put_line ( g_pkg_name || '.'
1645: || l_api_name
1646: || ' after calling relieve_reservation status is '
1647: || x_return_status );
1648: END IF;
1662: END IF;
1663:
1664: END LOOP;
1665:
1666: IF g_debug <= gme_debug.g_log_procedure THEN
1667: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1668: END IF;
1669: EXCEPTION
1670: WHEN delete_reservation_err THEN
1663:
1664: END LOOP;
1665:
1666: IF g_debug <= gme_debug.g_log_procedure THEN
1667: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1668: END IF;
1669: EXCEPTION
1670: WHEN delete_reservation_err THEN
1671: IF (g_debug <= gme_debug.g_log_error) THEN
1667: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1668: END IF;
1669: EXCEPTION
1670: WHEN delete_reservation_err THEN
1671: IF (g_debug <= gme_debug.g_log_error) THEN
1672: gme_debug.put_line
1673: ( 'inv_reservation_pub.delete_reservation failure ');
1674: gme_debug.put_line ('error message is ' || x_msg_data);
1675: END IF;
1668: END IF;
1669: EXCEPTION
1670: WHEN delete_reservation_err THEN
1671: IF (g_debug <= gme_debug.g_log_error) THEN
1672: gme_debug.put_line
1673: ( 'inv_reservation_pub.delete_reservation failure ');
1674: gme_debug.put_line ('error message is ' || x_msg_data);
1675: END IF;
1676: WHEN update_reservation_err THEN
1670: WHEN delete_reservation_err THEN
1671: IF (g_debug <= gme_debug.g_log_error) THEN
1672: gme_debug.put_line
1673: ( 'inv_reservation_pub.delete_reservation failure ');
1674: gme_debug.put_line ('error message is ' || x_msg_data);
1675: END IF;
1676: WHEN update_reservation_err THEN
1677: IF (g_debug <= gme_debug.g_log_error) THEN
1678: gme_debug.put_line
1673: ( 'inv_reservation_pub.delete_reservation failure ');
1674: gme_debug.put_line ('error message is ' || x_msg_data);
1675: END IF;
1676: WHEN update_reservation_err THEN
1677: IF (g_debug <= gme_debug.g_log_error) THEN
1678: gme_debug.put_line
1679: ( 'inv_reservation_pub.relieve_reservation failure ');
1680: gme_debug.put_line ('error message is ' || x_msg_data);
1681: END IF;
1674: gme_debug.put_line ('error message is ' || x_msg_data);
1675: END IF;
1676: WHEN update_reservation_err THEN
1677: IF (g_debug <= gme_debug.g_log_error) THEN
1678: gme_debug.put_line
1679: ( 'inv_reservation_pub.relieve_reservation failure ');
1680: gme_debug.put_line ('error message is ' || x_msg_data);
1681: END IF;
1682: WHEN notify_CSR_err THEN
1676: WHEN update_reservation_err THEN
1677: IF (g_debug <= gme_debug.g_log_error) THEN
1678: gme_debug.put_line
1679: ( 'inv_reservation_pub.relieve_reservation failure ');
1680: gme_debug.put_line ('error message is ' || x_msg_data);
1681: END IF;
1682: WHEN notify_CSR_err THEN
1683: IF (g_debug <= gme_debug.g_log_error) THEN
1684: gme_debug.put_line
1679: ( 'inv_reservation_pub.relieve_reservation failure ');
1680: gme_debug.put_line ('error message is ' || x_msg_data);
1681: END IF;
1682: WHEN notify_CSR_err THEN
1683: IF (g_debug <= gme_debug.g_log_error) THEN
1684: gme_debug.put_line
1685: ( 'inv_reservation_pub.update_reservation failure ');
1686: gme_debug.put_line ('error message is ' || x_msg_data);
1687: END IF;
1680: gme_debug.put_line ('error message is ' || x_msg_data);
1681: END IF;
1682: WHEN notify_CSR_err THEN
1683: IF (g_debug <= gme_debug.g_log_error) THEN
1684: gme_debug.put_line
1685: ( 'inv_reservation_pub.update_reservation failure ');
1686: gme_debug.put_line ('error message is ' || x_msg_data);
1687: END IF;
1688:
1682: WHEN notify_CSR_err THEN
1683: IF (g_debug <= gme_debug.g_log_error) THEN
1684: gme_debug.put_line
1685: ( 'inv_reservation_pub.update_reservation failure ');
1686: gme_debug.put_line ('error message is ' || x_msg_data);
1687: END IF;
1688:
1689:
1690: WHEN OTHERS THEN
1687: END IF;
1688:
1689:
1690: WHEN OTHERS THEN
1691: IF g_debug <= gme_debug.g_log_unexpected THEN
1692: gme_debug.put_line ( 'When others exception in '
1693: || g_pkg_name
1694: || '.'
1695: || l_api_name
1688:
1689:
1690: WHEN OTHERS THEN
1691: IF g_debug <= gme_debug.g_log_unexpected THEN
1692: gme_debug.put_line ( 'When others exception in '
1693: || g_pkg_name
1694: || '.'
1695: || l_api_name
1696: || ' Error is '
1721: delete_reservation_err EXCEPTION;
1722: matl_fetch_error EXCEPTION;
1723: notify_CSR_err EXCEPTION;
1724: BEGIN
1725: IF g_debug <= gme_debug.g_log_procedure THEN
1726: gme_debug.put_line ('Entering api '
1727: || g_pkg_name || '.'
1728: || l_api_name
1729: || ' material_detail_id '
1722: matl_fetch_error EXCEPTION;
1723: notify_CSR_err EXCEPTION;
1724: BEGIN
1725: IF g_debug <= gme_debug.g_log_procedure THEN
1726: gme_debug.put_line ('Entering api '
1727: || g_pkg_name || '.'
1728: || l_api_name
1729: || ' material_detail_id '
1730: || p_matl_dtl_rec.material_detail_id);
1754: ,x_msg_count => x_msg_count
1755: ,x_msg_data => x_msg_data
1756: ,x_return_status => x_return_status);
1757:
1758: IF g_debug <= gme_debug.g_log_statement THEN
1759: gme_debug.put_line
1760: ( g_pkg_name
1761: || '.'
1762: || l_api_name
1755: ,x_msg_data => x_msg_data
1756: ,x_return_status => x_return_status);
1757:
1758: IF g_debug <= gme_debug.g_log_statement THEN
1759: gme_debug.put_line
1760: ( g_pkg_name
1761: || '.'
1762: || l_api_name
1763: || 'Return status from query_prod_supply_reservations is '
1761: || '.'
1762: || l_api_name
1763: || 'Return status from query_prod_supply_reservations is '
1764: || x_return_status);
1765: gme_debug.put_line
1766: ( g_pkg_name
1767: || '.'
1768: || l_api_name
1769: || ' number of reservations is '
1770: || l_rsv_count);
1771: END IF;
1772:
1773: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1774: IF g_debug <= gme_debug.g_log_statement THEN
1775: gme_debug.put_line
1776: ( g_pkg_name
1777: || '.'
1778: || l_api_name
1771: END IF;
1772:
1773: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1774: IF g_debug <= gme_debug.g_log_statement THEN
1775: gme_debug.put_line
1776: ( g_pkg_name
1777: || '.'
1778: || l_api_name
1779: || 'Error is :'
1782: RAISE query_reservation_error;
1783: END IF;
1784:
1785: IF NVL(l_rsv_count,0) <= 0 THEN
1786: IF g_debug <= gme_debug.g_log_statement THEN
1787: gme_debug.put_line
1788: ( g_pkg_name
1789: || '.'
1790: || l_api_name
1783: END IF;
1784:
1785: IF NVL(l_rsv_count,0) <= 0 THEN
1786: IF g_debug <= gme_debug.g_log_statement THEN
1787: gme_debug.put_line
1788: ( g_pkg_name
1789: || '.'
1790: || l_api_name
1791: || ' No reservations against material detail line as a source of supply so RETURN now ');
1812: , X_return_status => x_return_status
1813: , X_msg_cont => x_msg_count
1814: , X_msg_data => x_msg_data );
1815:
1816: IF g_debug <= gme_debug.g_log_procedure THEN
1817: gme_debug.put_line ( g_pkg_name || '.'
1818: || l_api_name
1819: || ' after calling notify_CSR status is '
1820: || x_return_status );
1813: , X_msg_cont => x_msg_count
1814: , X_msg_data => x_msg_data );
1815:
1816: IF g_debug <= gme_debug.g_log_procedure THEN
1817: gme_debug.put_line ( g_pkg_name || '.'
1818: || l_api_name
1819: || ' after calling notify_CSR status is '
1820: || x_return_status );
1821: END IF;
1824: (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1825: RAISE notify_CSR_err;
1826: END IF;
1827:
1828: IF g_debug <= gme_debug.g_log_procedure THEN
1829: gme_debug.put_line ( g_pkg_name || '.'
1830: || l_api_name
1831: || ' about to invoke delete_reservation for reservation id '
1832: || l_rsv_rec.reservation_id);
1825: RAISE notify_CSR_err;
1826: END IF;
1827:
1828: IF g_debug <= gme_debug.g_log_procedure THEN
1829: gme_debug.put_line ( g_pkg_name || '.'
1830: || l_api_name
1831: || ' about to invoke delete_reservation for reservation id '
1832: || l_rsv_rec.reservation_id);
1833: END IF;
1841: , p_rsv_rec => l_rsv_rec
1842: , p_serial_number => l_dummy_sn
1843: );
1844:
1845: IF g_debug <= gme_debug.g_log_procedure THEN
1846: gme_debug.put_line ( g_pkg_name || '.'
1847: || l_api_name
1848: || ' after calling delete_reservation status is '
1849: || x_return_status );
1842: , p_serial_number => l_dummy_sn
1843: );
1844:
1845: IF g_debug <= gme_debug.g_log_procedure THEN
1846: gme_debug.put_line ( g_pkg_name || '.'
1847: || l_api_name
1848: || ' after calling delete_reservation status is '
1849: || x_return_status );
1850: END IF;
1855: RAISE delete_reservation_err;
1856: END IF;
1857:
1858: END LOOP;
1859: IF g_debug <= gme_debug.g_log_procedure THEN
1860: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1861: END IF;
1862:
1863: EXCEPTION
1856: END IF;
1857:
1858: END LOOP;
1859: IF g_debug <= gme_debug.g_log_procedure THEN
1860: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1861: END IF;
1862:
1863: EXCEPTION
1864: WHEN delete_reservation_err THEN
1861: END IF;
1862:
1863: EXCEPTION
1864: WHEN delete_reservation_err THEN
1865: IF (g_debug <= gme_debug.g_log_error) THEN
1866: gme_debug.put_line
1867: ( 'inv_reservation_pub.delete_reservation failure ');
1868: gme_debug.put_line ('error message is ' || x_msg_data);
1869: END IF;
1862:
1863: EXCEPTION
1864: WHEN delete_reservation_err THEN
1865: IF (g_debug <= gme_debug.g_log_error) THEN
1866: gme_debug.put_line
1867: ( 'inv_reservation_pub.delete_reservation failure ');
1868: gme_debug.put_line ('error message is ' || x_msg_data);
1869: END IF;
1870: WHEN notify_CSR_err THEN
1864: WHEN delete_reservation_err THEN
1865: IF (g_debug <= gme_debug.g_log_error) THEN
1866: gme_debug.put_line
1867: ( 'inv_reservation_pub.delete_reservation failure ');
1868: gme_debug.put_line ('error message is ' || x_msg_data);
1869: END IF;
1870: WHEN notify_CSR_err THEN
1871: IF (g_debug <= gme_debug.g_log_error) THEN
1872: gme_debug.put_line
1867: ( 'inv_reservation_pub.delete_reservation failure ');
1868: gme_debug.put_line ('error message is ' || x_msg_data);
1869: END IF;
1870: WHEN notify_CSR_err THEN
1871: IF (g_debug <= gme_debug.g_log_error) THEN
1872: gme_debug.put_line
1873: ( 'gme_supply_res_pvt.notify_CSR failure ');
1874: gme_debug.put_line ('error message is ' || x_msg_data);
1875: END IF;
1868: gme_debug.put_line ('error message is ' || x_msg_data);
1869: END IF;
1870: WHEN notify_CSR_err THEN
1871: IF (g_debug <= gme_debug.g_log_error) THEN
1872: gme_debug.put_line
1873: ( 'gme_supply_res_pvt.notify_CSR failure ');
1874: gme_debug.put_line ('error message is ' || x_msg_data);
1875: END IF;
1876: WHEN matl_fetch_error THEN
1870: WHEN notify_CSR_err THEN
1871: IF (g_debug <= gme_debug.g_log_error) THEN
1872: gme_debug.put_line
1873: ( 'gme_supply_res_pvt.notify_CSR failure ');
1874: gme_debug.put_line ('error message is ' || x_msg_data);
1875: END IF;
1876: WHEN matl_fetch_error THEN
1877: x_return_status := fnd_api.g_ret_sts_error;
1878: WHEN OTHERS THEN
1875: END IF;
1876: WHEN matl_fetch_error THEN
1877: x_return_status := fnd_api.g_ret_sts_error;
1878: WHEN OTHERS THEN
1879: IF g_debug <= gme_debug.g_log_unexpected THEN
1880: gme_debug.put_line ( 'When others exception in '
1881: || g_pkg_name
1882: || '.'
1883: || l_api_name
1876: WHEN matl_fetch_error THEN
1877: x_return_status := fnd_api.g_ret_sts_error;
1878: WHEN OTHERS THEN
1879: IF g_debug <= gme_debug.g_log_unexpected THEN
1880: gme_debug.put_line ( 'When others exception in '
1881: || g_pkg_name
1882: || '.'
1883: || l_api_name
1884: || ' Error is '
1909: delete_reservation_err EXCEPTION;
1910: matl_fetch_error EXCEPTION;
1911: notify_CSR_err EXCEPTION;
1912: BEGIN
1913: IF g_debug <= gme_debug.g_log_procedure THEN
1914: gme_debug.put_line ('Entering api '
1915: || g_pkg_name || '.'
1916: || l_api_name
1917: || ' batch_id => '
1910: matl_fetch_error EXCEPTION;
1911: notify_CSR_err EXCEPTION;
1912: BEGIN
1913: IF g_debug <= gme_debug.g_log_procedure THEN
1914: gme_debug.put_line ('Entering api '
1915: || g_pkg_name || '.'
1916: || l_api_name
1917: || ' batch_id => '
1918: || p_batch_header_rec.batch_id);
1929: ,x_msg_count => x_msg_count
1930: ,x_msg_data => x_msg_data
1931: ,x_return_status => x_return_status);
1932:
1933: IF g_debug <= gme_debug.g_log_statement THEN
1934: gme_debug.put_line
1935: ( g_pkg_name
1936: || '.'
1937: || l_api_name
1930: ,x_msg_data => x_msg_data
1931: ,x_return_status => x_return_status);
1932:
1933: IF g_debug <= gme_debug.g_log_statement THEN
1934: gme_debug.put_line
1935: ( g_pkg_name
1936: || '.'
1937: || l_api_name
1938: || 'Return status from query_prod_supply_reservations is '
1936: || '.'
1937: || l_api_name
1938: || 'Return status from query_prod_supply_reservations is '
1939: || x_return_status);
1940: gme_debug.put_line
1941: ( g_pkg_name
1942: || '.'
1943: || l_api_name
1944: || ' number of reservations is '
1945: || l_rsv_count);
1946: END IF;
1947:
1948: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1949: IF g_debug <= gme_debug.g_log_statement THEN
1950: gme_debug.put_line
1951: ( g_pkg_name
1952: || '.'
1953: || l_api_name
1946: END IF;
1947:
1948: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1949: IF g_debug <= gme_debug.g_log_statement THEN
1950: gme_debug.put_line
1951: ( g_pkg_name
1952: || '.'
1953: || l_api_name
1954: || 'Error is :'
1957: RAISE query_reservation_error;
1958: END IF;
1959:
1960: IF NVL(l_rsv_count,0) <= 0 THEN
1961: IF g_debug <= gme_debug.g_log_statement THEN
1962: gme_debug.put_line
1963: ( g_pkg_name
1964: || '.'
1965: || l_api_name
1958: END IF;
1959:
1960: IF NVL(l_rsv_count,0) <= 0 THEN
1961: IF g_debug <= gme_debug.g_log_statement THEN
1962: gme_debug.put_line
1963: ( g_pkg_name
1964: || '.'
1965: || l_api_name
1966: || ' No reservations against batch as a source of supply so RETURN now ');
1982: , X_return_status => x_return_status
1983: , X_msg_cont => x_msg_count
1984: , X_msg_data => x_msg_data );
1985:
1986: IF g_debug <= gme_debug.g_log_procedure THEN
1987: gme_debug.put_line ( g_pkg_name || '.'
1988: || l_api_name
1989: || ' after calling notify_CSR status is '
1990: || x_return_status );
1983: , X_msg_cont => x_msg_count
1984: , X_msg_data => x_msg_data );
1985:
1986: IF g_debug <= gme_debug.g_log_procedure THEN
1987: gme_debug.put_line ( g_pkg_name || '.'
1988: || l_api_name
1989: || ' after calling notify_CSR status is '
1990: || x_return_status );
1991: END IF;
2003: (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
2004: RAISE notify_CSR_err;
2005: END IF;
2006:
2007: IF g_debug <= gme_debug.g_log_procedure THEN
2008: gme_debug.put_line ( g_pkg_name || '.'
2009: || l_api_name
2010: || ' about to invoke delete_reservation for reservation id '
2011: || l_rsv_rec.reservation_id);
2004: RAISE notify_CSR_err;
2005: END IF;
2006:
2007: IF g_debug <= gme_debug.g_log_procedure THEN
2008: gme_debug.put_line ( g_pkg_name || '.'
2009: || l_api_name
2010: || ' about to invoke delete_reservation for reservation id '
2011: || l_rsv_rec.reservation_id);
2012: END IF;
2020: , p_rsv_rec => l_rsv_rec
2021: , p_serial_number => l_dummy_sn
2022: );
2023:
2024: IF g_debug <= gme_debug.g_log_procedure THEN
2025: gme_debug.put_line ( g_pkg_name || '.'
2026: || l_api_name
2027: || ' after calling delete_reservation status is '
2028: || x_return_status );
2021: , p_serial_number => l_dummy_sn
2022: );
2023:
2024: IF g_debug <= gme_debug.g_log_procedure THEN
2025: gme_debug.put_line ( g_pkg_name || '.'
2026: || l_api_name
2027: || ' after calling delete_reservation status is '
2028: || x_return_status );
2029: END IF;
2034: RAISE delete_reservation_err;
2035: END IF;
2036:
2037: END LOOP;
2038: IF g_debug <= gme_debug.g_log_procedure THEN
2039: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2040: END IF;
2041:
2042: EXCEPTION
2035: END IF;
2036:
2037: END LOOP;
2038: IF g_debug <= gme_debug.g_log_procedure THEN
2039: gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2040: END IF;
2041:
2042: EXCEPTION
2043: WHEN delete_reservation_err THEN
2040: END IF;
2041:
2042: EXCEPTION
2043: WHEN delete_reservation_err THEN
2044: IF (g_debug <= gme_debug.g_log_error) THEN
2045: gme_debug.put_line
2046: ( 'inv_reservation_pub.delete_reservation failure ');
2047: gme_debug.put_line ('error message is ' || x_msg_data);
2048: END IF;
2041:
2042: EXCEPTION
2043: WHEN delete_reservation_err THEN
2044: IF (g_debug <= gme_debug.g_log_error) THEN
2045: gme_debug.put_line
2046: ( 'inv_reservation_pub.delete_reservation failure ');
2047: gme_debug.put_line ('error message is ' || x_msg_data);
2048: END IF;
2049: WHEN notify_CSR_err THEN
2043: WHEN delete_reservation_err THEN
2044: IF (g_debug <= gme_debug.g_log_error) THEN
2045: gme_debug.put_line
2046: ( 'inv_reservation_pub.delete_reservation failure ');
2047: gme_debug.put_line ('error message is ' || x_msg_data);
2048: END IF;
2049: WHEN notify_CSR_err THEN
2050: IF (g_debug <= gme_debug.g_log_error) THEN
2051: gme_debug.put_line
2046: ( 'inv_reservation_pub.delete_reservation failure ');
2047: gme_debug.put_line ('error message is ' || x_msg_data);
2048: END IF;
2049: WHEN notify_CSR_err THEN
2050: IF (g_debug <= gme_debug.g_log_error) THEN
2051: gme_debug.put_line
2052: ( 'gme_supply_res_pvt.notify_CSR failure ');
2053: gme_debug.put_line ('error message is ' || x_msg_data);
2054: END IF;
2047: gme_debug.put_line ('error message is ' || x_msg_data);
2048: END IF;
2049: WHEN notify_CSR_err THEN
2050: IF (g_debug <= gme_debug.g_log_error) THEN
2051: gme_debug.put_line
2052: ( 'gme_supply_res_pvt.notify_CSR failure ');
2053: gme_debug.put_line ('error message is ' || x_msg_data);
2054: END IF;
2055: WHEN matl_fetch_error THEN
2049: WHEN notify_CSR_err THEN
2050: IF (g_debug <= gme_debug.g_log_error) THEN
2051: gme_debug.put_line
2052: ( 'gme_supply_res_pvt.notify_CSR failure ');
2053: gme_debug.put_line ('error message is ' || x_msg_data);
2054: END IF;
2055: WHEN matl_fetch_error THEN
2056: x_return_status := fnd_api.g_ret_sts_error;
2057: WHEN OTHERS THEN
2054: END IF;
2055: WHEN matl_fetch_error THEN
2056: x_return_status := fnd_api.g_ret_sts_error;
2057: WHEN OTHERS THEN
2058: IF g_debug <= gme_debug.g_log_unexpected THEN
2059: gme_debug.put_line ( 'When others exception in '
2060: || g_pkg_name
2061: || '.'
2062: || l_api_name
2055: WHEN matl_fetch_error THEN
2056: x_return_status := fnd_api.g_ret_sts_error;
2057: WHEN OTHERS THEN
2058: IF g_debug <= gme_debug.g_log_unexpected THEN
2059: gme_debug.put_line ( 'When others exception in '
2060: || g_pkg_name
2061: || '.'
2062: || l_api_name
2063: || ' Error is '