[Home] [Help]
10: g_organization_id NUMBER;
11: g_print_mode VARCHAR2(1);
12:
13: -- Bug# 4258360: Added these global constants which refer to the allocation method
14: -- stored for the current pick release batch (refers to INV_CACHE.wpb_rec.allocation_method)
15: g_inventory_only CONSTANT VARCHAR2(1) := 'I';
16: g_crossdock_only CONSTANT VARCHAR2(1) := 'C';
17: g_prioritize_inventory CONSTANT VARCHAR2(1) := 'N';
18: g_prioritize_crossdock CONSTANT VARCHAR2(1) := 'X';
1018: print_debug('Inside Pick_Release', 'INV_Pick_Release_Pub.Pick_Release');
1019: print_debug('p_dynamic_replenishment :'||p_dynamic_replenishment, 'INV_Pick_Release_Pub.Pick_Release');
1020: End If;
1021:
1022: l_return_value := inv_cache.set_pick_release(TRUE); --Added for bug3237702
1023: inv_log_util.g_maintain_log_profile := TRUE; -- Bug 5558315 - duplication so no dependency btw inv_cache and inv_log
1024:
1025: SAVEPOINT Pick_Release_PUB;
1026:
1019: print_debug('p_dynamic_replenishment :'||p_dynamic_replenishment, 'INV_Pick_Release_Pub.Pick_Release');
1020: End If;
1021:
1022: l_return_value := inv_cache.set_pick_release(TRUE); --Added for bug3237702
1023: inv_log_util.g_maintain_log_profile := TRUE; -- Bug 5558315 - duplication so no dependency btw inv_cache and inv_log
1024:
1025: SAVEPOINT Pick_Release_PUB;
1026:
1027: -- Standard Call to check for call compatibility
1058: END IF;
1059:
1060: -- Set move order transaction date if passed in as not NULL
1061: IF p_mo_transact_date <> fnd_api.g_miss_date THEN
1062: inv_cache.mo_transaction_date := p_mo_transact_date;
1063: END IF;
1064:
1065: -- Validate that all move order lines are from the same org, that all lines
1066: -- have a status of pre-approved(7) or approved(3),and that all of the move
1111: END IF;
1112:
1113: IF l_mo_line.header_id <> l_current_header_id OR
1114: l_current_header_id IS NULL THEN
1115: l_return_value := INV_CACHE.set_mtrh_rec(l_mo_line.header_Id);
1116: If NOT l_return_value Then
1117: If is_debug then
1118: print_debug('Error setting cache for move order header ','INV_Pick_Release_Pub.Pick_Release');
1119: End If;
1118: print_debug('Error setting cache for move order header ','INV_Pick_Release_Pub.Pick_Release');
1119: End If;
1120: RAISE fnd_api.g_exc_unexpected_error;
1121: End If;
1122: l_mo_type := INV_CACHE.mtrh_rec.move_order_type;
1123: l_mo_number := INV_CACHE.mtrh_rec.request_number;
1124: l_current_header_id := l_mo_line.header_id;
1125: END IF;
1126:
1119: End If;
1120: RAISE fnd_api.g_exc_unexpected_error;
1121: End If;
1122: l_mo_type := INV_CACHE.mtrh_rec.move_order_type;
1123: l_mo_number := INV_CACHE.mtrh_rec.request_number;
1124: l_current_header_id := l_mo_line.header_id;
1125: END IF;
1126:
1127: IF l_mo_type <> 3 THEN
1327: -- FND_MSG_PUB.Add;
1328: -- RAISE fnd_api.g_exc_unexpected_error;
1329: END;
1330:
1331: l_return_value := INV_CACHE.set_mso_rec(l_oe_header_id);
1332: IF NOT l_return_value THEN
1333: l_mso_header_id :=-9999;
1334: If is_debug then
1335: print_debug('No Mtl_Sales_Order ID found for oe header','Inv_Pick_Release_PUB.Process_Line');
1334: If is_debug then
1335: print_debug('No Mtl_Sales_Order ID found for oe header','Inv_Pick_Release_PUB.Process_Line');
1336: End If;
1337: ELSE
1338: l_mso_header_id := INV_CACHE.mso_rec.sales_order_id;
1339: END IF;
1340:
1341: BEGIN
1342: select t.transaction_source_type_id
1409: -- now sort the move order line by the header_id
1410: --BENCHMARK - check cache
1411: --l_wms_installed := inv_install.adv_inv_installed(
1412: -- l_mo_line_tbl(l_line_index).organization_id);
1413: l_return_value := INV_CACHE.set_wms_installed(l_organization_id);
1414: If NOT l_return_value Then
1415: If is_debug then
1416: print_debug('Error setting cache for wms installed','INV_Pick_Release_Pub.Pick_Release');
1417: End If;
1416: print_debug('Error setting cache for wms installed','INV_Pick_Release_Pub.Pick_Release');
1417: End If;
1418: RAISE fnd_api.g_exc_unexpected_error;
1419: End If;
1420: l_wms_installed := INV_CACHE.wms_installed;
1421: --sort only necessary if wms installed and multiple headers in the
1422: -- line_tbl
1423: IF l_wms_installed and l_multiple_headers THEN
1424: sort(l_mo_line_tbl, l_mo_line_tbl.FIRST, l_mo_line_tbl.LAST);
1438: l_auto_pick_confirm := p_auto_pick_confirm;
1439: END IF;
1440: ELSE
1441: -- Retrieve the org-level parameter for auto-pick confirm
1442: l_return_value := INV_CACHE.set_org_rec(l_organization_id);
1443: If NOT l_return_value Then
1444: If is_debug then
1445: print_debug('Error setting cache for organization',
1446: 'INV_Pick_Release_Pub.Pick_Release');
1446: 'INV_Pick_Release_Pub.Pick_Release');
1447: End If;
1448: RAISE fnd_api.g_exc_unexpected_error;
1449: End If;
1450: l_auto_pick_confirm:= INV_CACHE.org_rec.mo_pick_confirm_required;
1451: END IF;
1452:
1453: -- Determine what printing mode to use when pick releasing lines.
1454: IF g_organization_id IS NOT NULL AND
1507:
1508: -- Bug# 4258360: Query for and cache the picking batch record.
1509: -- Even if multiple move order headers are created for this batch (not likely),
1510: -- they should all refer to the same picking batch record. A value for the move
1511: -- order header record should be cached by now in INV_CACHE. 'Batch' refers to
1512: -- the set of MOL's passed into the pick release API and not the entire set of pick
1513: -- release lines.
1514: -- {{
1515: -- Pick Release a batch such that you have orders from multiple
1515: -- Pick Release a batch such that you have orders from multiple
1516: -- organizations. This will cause multiple MOH to be created.
1517: -- If successfully pick released, then no bug.
1518: -- }}
1519: IF (NOT INV_CACHE.set_wpb_rec
1520: (p_batch_id => NULL,
1521: p_request_number => INV_CACHE.mtrh_rec.request_number)) THEN
1522: IF (is_debug) THEN
1523: print_debug('Error setting cache for WSH picking batch record','INV_Pick_Release_Pub.Pick_Release');
1517: -- If successfully pick released, then no bug.
1518: -- }}
1519: IF (NOT INV_CACHE.set_wpb_rec
1520: (p_batch_id => NULL,
1521: p_request_number => INV_CACHE.mtrh_rec.request_number)) THEN
1522: IF (is_debug) THEN
1523: print_debug('Error setting cache for WSH picking batch record','INV_Pick_Release_Pub.Pick_Release');
1524: END IF;
1525: RAISE fnd_api.g_exc_unexpected_error;
1524: END IF;
1525: RAISE fnd_api.g_exc_unexpected_error;
1526: END IF;
1527: -- Set the allocation method variable
1528: l_allocation_method := NVL(INV_CACHE.wpb_rec.allocation_method, g_inventory_only);
1529:
1530: -- Bug# 4258360: Validate that p_trolin_delivery_ids and p_del_detail_id tables have the
1531: -- same number of entries. There should be a one to one relationship between the tables
1532: -- and the indices used. If NULL tables are passed, the counts should still match (0 = 0).
1604: IF (l_mo_line.return_status <> FND_API.G_RET_STS_UNEXP_ERROR and
1605: l_mo_line.return_status <> FND_API.G_RET_STS_ERROR) THEN
1606: -- First retrieve the new Grouping Rule ID if necessary.
1607: IF l_get_header_rule = 1 THEN
1608: l_return_value := INV_CACHE.set_mtrh_rec(l_mo_line.header_id);
1609: If NOT l_return_value Then
1610: If is_debug then
1611: print_debug('Error setting cache for move order header ',
1612: 'INV_Pick_Release_Pub.Pick_Release');
1612: 'INV_Pick_Release_Pub.Pick_Release');
1613: End If;
1614: RAISE fnd_api.g_exc_unexpected_error;
1615: End If;
1616: l_grouping_rule_id := INV_CACHE.mtrh_rec.grouping_rule_id;
1617:
1618: -- If the header did not have a grouping rule ID, retrieve it from
1619: -- the organization-level default.
1620: IF l_grouping_rule_id IS NULL THEN
2018: print_debug('Backorder mo line:' || l_mo_line.line_id,
2019: 'Inv_Pick_Release_Pub.Pick_Release');
2020: End If;
2021:
2022: l_return_value := INV_CACHE.set_wdd_rec(l_mo_line.line_id);
2023: If NOT l_return_value Then
2024: If is_debug then
2025: print_debug('Error setting cache for delivery line',
2026: 'INV_Pick_Release_Pub.Pick_Release');
2026: 'INV_Pick_Release_Pub.Pick_Release');
2027: End If;
2028: RAISE fnd_api.g_exc_unexpected_error;
2029: End If;
2030: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2031: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2032: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2033: l_released_status := INV_CACHE.wdd_rec.released_status;
2034:
2027: End If;
2028: RAISE fnd_api.g_exc_unexpected_error;
2029: End If;
2030: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2031: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2032: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2033: l_released_status := INV_CACHE.wdd_rec.released_status;
2034:
2035:
2028: RAISE fnd_api.g_exc_unexpected_error;
2029: End If;
2030: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2031: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2032: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2033: l_released_status := INV_CACHE.wdd_rec.released_status;
2034:
2035:
2036: --Call Update_Shipping_Attributes to backorder detail line
2029: End If;
2030: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2031: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2032: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2033: l_released_status := INV_CACHE.wdd_rec.released_status;
2034:
2035:
2036: --Call Update_Shipping_Attributes to backorder detail line
2037: l_shipping_attr(1).source_header_id := l_source_header_id;
2374: print_debug('New line qty: ' || l_new_line_quantity,
2375: 'Inv_Pick_Release_Pub.Pick_Release');
2376: End If;
2377:
2378: l_return_value := INV_CACHE.set_wdd_rec(l_mo_line.line_id);
2379: If NOT l_return_value Then
2380: If is_debug then
2381: print_debug('Error setting cache for delivery line',
2382: 'INV_Pick_Release_Pub.Pick_Release');
2382: 'INV_Pick_Release_Pub.Pick_Release');
2383: End If;
2384: RAISE fnd_api.g_exc_unexpected_error;
2385: End If;
2386: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2387: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2388: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2389: l_released_status := INV_CACHE.wdd_rec.released_status;
2390:
2383: End If;
2384: RAISE fnd_api.g_exc_unexpected_error;
2385: End If;
2386: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2387: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2388: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2389: l_released_status := INV_CACHE.wdd_rec.released_status;
2390:
2391: --Call Update_Shipping_Attributes to backorder detail line
2384: RAISE fnd_api.g_exc_unexpected_error;
2385: End If;
2386: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2387: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2388: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2389: l_released_status := INV_CACHE.wdd_rec.released_status;
2390:
2391: --Call Update_Shipping_Attributes to backorder detail line
2392: l_shipping_attr(1).source_header_id := l_source_header_id;
2385: End If;
2386: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2387: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2388: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2389: l_released_status := INV_CACHE.wdd_rec.released_status;
2390:
2391: --Call Update_Shipping_Attributes to backorder detail line
2392: l_shipping_attr(1).source_header_id := l_source_header_id;
2393: l_shipping_attr(1).source_line_id := l_source_line_id;
2568: -- shipset or ship model
2569:
2570:
2571: -- 8519286
2572: /* l_return_value := INV_CACHE.set_wdd_rec(l_mo_line.line_id); */
2573: l_return_value := FALSE;
2574:
2575: BEGIN
2576: SELECT *
2573: l_return_value := FALSE;
2574:
2575: BEGIN
2576: SELECT *
2577: INTO INV_CACHE.wdd_rec
2578: FROM WSH_DELIVERY_DETAILS
2579: WHERE move_order_line_id = l_mo_line.line_id
2580: AND NVL(released_status, 'Z') NOT IN ('Y','C');
2581: l_return_value := TRUE;
2588: -- End of 8519286
2589:
2590:
2591: -- Retrieve the WDD record corresponding to the current MOL
2592: l_return_value := INV_CACHE.set_wdd_rec(l_mo_line.line_id);
2593: IF (NOT l_return_value) THEN
2594: IF (is_debug) THEN
2595: print_debug('Error setting cache for delivery line',
2596: 'INV_Pick_Release_Pub.Pick_Release');
2596: 'INV_Pick_Release_Pub.Pick_Release');
2597: END IF;
2598: RAISE fnd_api.g_exc_unexpected_error;
2599: END IF;
2600: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2601: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2602: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2603: l_released_status := INV_CACHE.wdd_rec.released_status;
2604:
2597: END IF;
2598: RAISE fnd_api.g_exc_unexpected_error;
2599: END IF;
2600: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2601: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2602: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2603: l_released_status := INV_CACHE.wdd_rec.released_status;
2604:
2605: -- Bug# 4258360: If allocation mode = N (Prioritize Inventory), instead of simply
2598: RAISE fnd_api.g_exc_unexpected_error;
2599: END IF;
2600: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2601: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2602: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2603: l_released_status := INV_CACHE.wdd_rec.released_status;
2604:
2605: -- Bug# 4258360: If allocation mode = N (Prioritize Inventory), instead of simply
2606: -- backordering the unallocated quantity on the WDD record, we need to perform
2599: END IF;
2600: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2601: l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2602: l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2603: l_released_status := INV_CACHE.wdd_rec.released_status;
2604:
2605: -- Bug# 4258360: If allocation mode = N (Prioritize Inventory), instead of simply
2606: -- backordering the unallocated quantity on the WDD record, we need to perform
2607: -- new logic to support crossdocking. The WDD record needs to be split or updated
2842: from mtl_reservations mr
2843: WHERE MR.DEMAND_SOURCE_LINE_ID = l_source_line_id
2844: and MR.DEMAND_SOURCE_HEADER_ID =
2845: inv_salesorder.get_salesorder_for_oeheader(l_source_header_id)
2846: and MR.demand_source_type_id = decode(INV_CACHE.wdd_rec.source_document_type_id, 10, 8, 2)
2847: and MR.SUBINVENTORY_CODE IS NOT NULL --locator is not needed
2848: and (nvl(mr.staged_flag,'N') = 'N' and nvl(mr.detailed_quantity,0) = 0);
2849:
2850: IF is_debug THEN
2967: -- x-docking.
2968: -- }}
2969: IF (l_allocation_method = g_prioritize_inventory) THEN
2970: -- Retrieve the WDD record associated with the current MOL
2971: IF (NOT INV_CACHE.set_wdd_rec(l_mo_line.line_id)) THEN
2972: IF (is_debug) THEN
2973: print_debug('Error setting cache for WDD delivery line',
2974: 'INV_Pick_Release_Pub.Pick_Release');
2975: END IF;
2974: 'INV_Pick_Release_Pub.Pick_Release');
2975: END IF;
2976: RAISE fnd_api.g_exc_unexpected_error;
2977: END IF;
2978: l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2979:
2980: -- Update WDD record in release table with a released status of 'S'
2981: IF (p_wsh_release_table.COUNT > 0) THEN
2982: p_wsh_release_table(l_wdd_index_tbl(l_delivery_detail_id)).released_status := 'S';
3443: p_commit => fnd_api.g_false,
3444: x_return_status => l_api_return_status,
3445: x_msg_count => x_msg_count,
3446: x_msg_data => x_msg_data,
3447: p_batch_id => INV_CACHE.wpb_rec.batch_id,
3448: p_wsh_release_table => p_wsh_release_table,
3449: p_trolin_delivery_ids => p_trolin_delivery_ids,
3450: p_del_detail_id => p_del_detail_id);
3451:
3479: print_Debug('x_return_status is ' || x_return_status,
3480: 'Inv_Pick_Release_Pub.Pick_Release');
3481: END IF;
3482:
3483: inv_cache.mo_transaction_date := NULL;
3484: l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3485: inv_log_util.g_maintain_log_profile := FALSE;
3486:
3487: EXCEPTION
3480: 'Inv_Pick_Release_Pub.Pick_Release');
3481: END IF;
3482:
3483: inv_cache.mo_transaction_date := NULL;
3484: l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3485: inv_log_util.g_maintain_log_profile := FALSE;
3486:
3487: EXCEPTION
3488: WHEN FND_API.G_EXC_ERROR THEN
3487: EXCEPTION
3488: WHEN FND_API.G_EXC_ERROR THEN
3489: --
3490: x_return_status := FND_API.G_RET_STS_ERROR;
3491: l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3492: inv_log_util.g_maintain_log_profile := TRUE;
3493: --
3494: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3495: , p_data => x_msg_data);
3496: --
3497: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3498: --
3499: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3500: l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3501: inv_log_util.g_maintain_log_profile := TRUE;
3502: --
3503: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3504: , p_data => x_msg_data);
3510: END IF;
3511:
3512: ROLLBACK TO Pick_Release_PUB;
3513: --
3514: l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3515: inv_log_util.g_maintain_log_profile := TRUE;
3516: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3517: --
3518: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN