[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
INTO g_crossdock_criteria_tb(p_criterion_id)
FROM wms_crossdock_criteria
WHERE criterion_id = p_criterion_id;
SELECT *
INTO g_crossdock_criteria_tb(p_criterion_id)
FROM wms_crossdock_criteria
WHERE criterion_id = p_criterion_id;
FUNCTION delete_crossdock_criteria
(p_criterion_id IN NUMBER) RETURN BOOLEAN
IS
BEGIN
IF (p_criterion_id IS NULL) THEN
RETURN FALSE;
g_crossdock_criteria_tb.DELETE(p_criterion_id);
END delete_crossdock_criteria;
g_crossdock_criteria_tb.DELETE;
SELECT receiving_routing_id
INTO g_item_routing_id_tb(p_item_id)
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT receiving_routing_id
INTO g_vendor_routing_id_tb(p_vendor_id)
FROM po_vendors
WHERE vendor_id = p_vendor_id;
SELECT NVL(receiving_routing_id, 1)
INTO g_org_routing_id_tb(p_organization_id)
FROM rcv_parameters
WHERE organization_id = p_organization_id;
SELECT 1
INTO l_wip_exists
FROM dual
WHERE EXISTS (SELECT reservation_id
FROM mtl_reservations
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND demand_source_type_id = p_demand_type_id
AND demand_source_header_id = p_demand_header_id
AND demand_source_line_id = p_demand_line_id
AND supply_source_type_id = inv_reservation_global.g_source_type_wip);
SELECT 1
INTO l_non_wip_exists
FROM dual
WHERE EXISTS (SELECT reservation_id
FROM mtl_reservations
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND demand_source_type_id = p_demand_type_id
AND demand_source_header_id = p_demand_header_id
AND demand_source_line_id = p_demand_line_id
AND supply_source_type_id <> inv_reservation_global.g_source_type_wip
AND supply_source_type_id <> inv_reservation_global.g_source_type_inv);
-- Insert the split WDD line into p_wsh_release_table.
-- The split WDD release record should be the same as the original one with
-- only the following fields modified: delivery_detail_id, released_status,
-- move_order_line_id (if supply used is receiving) and requested_quantity fields
l_split_wdd_rel_rec := p_wsh_release_table(l_wdd_index);
-- Store this newly inserted split WDD index value. In case of rollback,
-- we need to remove this record from p_wsh_release_table.
l_split_wdd_index := l_index;
-- Update the original WDD line in p_wsh_release_table with the current
-- unallocated quantity while retaining the original released_status
-- (should be 'R' or 'B'). Do this in the UOM of the WDD line and also update
-- the secondary requested quantity field.
p_wsh_release_table(l_wdd_index).requested_quantity := l_demand_qty - l_atd_wdd_qty;
print_debug(p_log_prefix || 'Updated the WDD records in p_wsh_release_table');
-- update this variable since it isn't used. Doing it here for completeness.
l_demand_atr_qty := l_demand_qty;
-- Crossdock/Update the corresponding record in p_wsh_release_table
p_wsh_release_table(l_wdd_index).released_status := 'S';
print_debug(p_log_prefix || 'Update the crossdocked WDD record: ' || l_split_wdd_id);
-- Store this newly inserted delivery related index value. In case of rollback,
-- we need to remove these records from p_del_detail_id and p_trolin_delivery_ids.
l_split_delivery_index := l_index;
print_debug(p_log_prefix || 'Inserted record into delivery tables for crossdocked WDD');
print_debug(p_log_prefix || 'Call the update_reservation API to crossdock the RSV record');
INV_RESERVATION_PVT.update_reservation
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_original_rsv_rec => l_original_rsv_rec,
p_to_rsv_rec => l_to_rsv_rec,
p_original_serial_number => l_original_serial_number,
p_to_serial_number => l_to_serial_number,
p_validation_flag => fnd_api.g_true,
p_check_availability => fnd_api.g_false);
print_debug(p_log_prefix || 'Error returned from update_reservation API: '
|| x_return_status);
print_debug(p_log_prefix || 'Successfully updated and crossdocked the RSV record');
INSERT INTO mtl_txn_request_lines
(LINE_ID
,HEADER_ID
,LINE_NUMBER
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,REVISION
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,TO_ACCOUNT_ID
,LOT_NUMBER
,SERIAL_NUMBER_START
,SERIAL_NUMBER_END
,UOM_CODE
,QUANTITY
,QUANTITY_DELIVERED
,QUANTITY_DETAILED
,DATE_REQUIRED
,REASON_ID
,REFERENCE
,REFERENCE_TYPE_CODE
,REFERENCE_ID
,PROJECT_ID
,TASK_ID
,TRANSACTION_HEADER_ID
,LINE_STATUS
,STATUS_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,CREATED_BY
,CREATION_DATE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE_CATEGORY
,TXN_SOURCE_ID
,TXN_SOURCE_LINE_ID
,TXN_SOURCE_LINE_DETAIL_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,PRIMARY_QUANTITY
,TO_ORGANIZATION_ID
,PUT_AWAY_STRATEGY_ID
,PICK_STRATEGY_ID
,SHIP_TO_LOCATION_ID
,UNIT_NUMBER
,REFERENCE_DETAIL_ID
,ASSIGNMENT_ID
,FROM_COST_GROUP_ID
,TO_COST_GROUP_ID
,LPN_ID
,TO_LPN_ID
,PICK_SLIP_NUMBER
,PICK_SLIP_DATE
,INSPECTION_STATUS
,PICK_METHODOLOGY_ID
,CONTAINER_ITEM_ID
,CARTON_GROUPING_ID
,BACKORDER_DELIVERY_DETAIL_ID
,WMS_PROCESS_FLAG
,SHIP_SET_ID
,SHIP_MODEL_ID
,MODEL_QUANTITY
,FROM_SUBINVENTORY_ID
,TO_SUBINVENTORY_ID
,CROSSDOCK_TYPE
,REQUIRED_QUANTITY
,GRADE_CODE
,SECONDARY_QUANTITY
,SECONDARY_QUANTITY_DELIVERED
,SECONDARY_QUANTITY_DETAILED
,SECONDARY_REQUIRED_QUANTITY
,SECONDARY_UOM_CODE
,WIP_ENTITY_ID
,REPETITIVE_SCHEDULE_ID
,OPERATION_SEQ_NUM
,WIP_SUPPLY_TYPE
)
(SELECT
mtl_txn_request_lines_s.NEXTVAL -- LINE_ID
,HEADER_ID
,mtrl_max.line_num --LINE_NUMBER
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,REVISION
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,TO_ACCOUNT_ID
,LOT_NUMBER
,SERIAL_NUMBER_START
,SERIAL_NUMBER_END
,UOM_CODE
,l_atd_qty --QUANTITY
,QUANTITY_DELIVERED
,QUANTITY_DETAILED
,DATE_REQUIRED
,REASON_ID
,REFERENCE
,REFERENCE_TYPE_CODE
,REFERENCE_ID
,PROJECT_ID
,TASK_ID
,TRANSACTION_HEADER_ID
,LINE_STATUS
,STATUS_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,SYSDATE --LAST_UPDATE_DATE
,CREATED_BY
,SYSDATE --CREATION_DATE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE_CATEGORY
,TXN_SOURCE_ID
,TXN_SOURCE_LINE_ID
,TXN_SOURCE_LINE_DETAIL_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,l_atd_prim_qty --PRIMARY_QUANTITY
,TO_ORGANIZATION_ID
,PUT_AWAY_STRATEGY_ID
,PICK_STRATEGY_ID
,SHIP_TO_LOCATION_ID
,UNIT_NUMBER
-- Change made for Inbound. For Opportunistic cases inbound
-- can call crossdock API for a particular MOL. Then they need
-- to know the MOLs that have been split and created for this
-- line so that they can requery them somehow for creating suggestions.
,Decode(l_criterion_type,g_crt_type_opp,l_mol_line_id,reference_detail_id)
,ASSIGNMENT_ID
,FROM_COST_GROUP_ID
,TO_COST_GROUP_ID
,LPN_ID
,TO_LPN_ID
,PICK_SLIP_NUMBER
,PICK_SLIP_DATE
,INSPECTION_STATUS
,PICK_METHODOLOGY_ID
,CONTAINER_ITEM_ID
,CARTON_GROUPING_ID
,l_backorder_detail_id --BACKORDER_DELIVERY_DETAIL_ID
,WMS_PROCESS_FLAG
,SHIP_SET_ID
,SHIP_MODEL_ID
,MODEL_QUANTITY
,FROM_SUBINVENTORY_ID
,TO_SUBINVENTORY_ID
,l_crossdock_type --CROSSDOCK_TYPE
,REQUIRED_QUANTITY
,GRADE_CODE
,l_atd_mol_qty2 --SECONDARY_QUANTITY
,SECONDARY_QUANTITY_DELIVERED
,SECONDARY_QUANTITY_DETAILED
,SECONDARY_REQUIRED_QUANTITY
,SECONDARY_UOM_CODE
,l_wip_entity_id --WIP_ENTITY_ID
,l_repetitive_schedule_id --REPETITIVE_SCHEDULE_ID
,l_operation_seq_num --OPERATION_SEQ_NUM
,l_wip_supply_type --WIP_SUPPLY_TYPE
FROM mtl_txn_request_lines mtrl, (SELECT MAX(line_number) + 1 AS line_num
FROM mtl_txn_request_lines
WHERE header_id = l_mol_header_id) mtrl_max
WHERE mtrl.line_id = l_mol_line_id);
print_debug(p_log_prefix || 'Error inserting split MOL record');
END; -- End inserting split MOL record into MTL_TXN_REQUEST_LINES
SELECT line_id
INTO l_split_mol_line_id
FROM mtl_txn_request_lines
WHERE header_id = l_mol_header_id
AND ROWNUM = 1
ORDER BY line_number DESC;
print_debug(p_log_prefix || 'Successfully inserted/split the MOL record: ' ||
l_split_mol_line_id);
UPDATE mtl_txn_request_lines SET
quantity = l_mol_qty - l_atd_qty,
primary_quantity = l_mol_prim_qty - l_atd_prim_qty,
secondary_quantity = l_mol_qty2 - l_atd_mol_qty2
WHERE line_id = l_mol_line_id;
print_debug(p_log_prefix || 'Successfully updated and crossdocked the MOL record');
UPDATE mtl_txn_request_lines SET
backorder_delivery_detail_id = l_backorder_detail_id,
crossdock_type = l_crossdock_type,
wip_entity_id = l_wip_entity_id,
repetitive_schedule_id = l_repetitive_schedule_id,
operation_seq_num = l_operation_seq_num,
wip_supply_type = l_wip_supply_type
WHERE line_id = l_mol_line_id;
print_debug(p_log_prefix || 'Successfully updated and crossdocked the MOL record');
-- WDD record was also split so update the appropriate crossdocked WDD
p_wsh_release_table(l_split_wdd_index).move_order_line_id := l_split_mol_line_id;
-- WDD record was not split so update the current WDD
p_wsh_release_table(l_wdd_index).move_order_line_id := l_split_mol_line_id;
print_debug(p_log_prefix || 'Successfully updated the WDD records with the split MOL line');
END IF; -- End of logic to update WDD records with split MOL line
SELECT
poll.po_header_id AS header_id,
poll.line_location_id AS line_id,
NULL AS line_detail_id,
NULL AS quantity,
muom.uom_code AS uom_code,
NULL AS primary_quantity,
NULL AS secondary_quantity,
NULL AS secondary_uom_code,
MIN(pod.project_id) AS project_id,
MIN(pod.task_id) AS task_id,
NULL AS lpn_id
FROM po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
po_distributions_all pod, po_line_types plt, mtl_units_of_measure muom
WHERE poh.type_lookup_code IN ('STANDARD','PLANNED','BLANKET','CONTRACT')
AND NVL(poh.cancel_flag, 'N') IN ('N', 'I')
AND NVL(poh.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pol.po_header_id = poh.po_header_id
AND poh.po_header_id = poll.po_header_id
AND pol.po_line_id = poll.po_line_id
AND pod.po_header_id = poh.po_header_id
AND pod.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND pol.item_id = l_inventory_item_id
AND pol.line_type_id = plt.line_type_id
AND NVL(plt.outside_operation_flag, 'N') = 'N'
AND poll.unit_meas_lookup_code = muom.unit_of_measure
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.ship_to_organization_id = l_organization_id
AND poll.quantity > NVL(poll.quantity_received, 0)
AND NVL(poll.receiving_routing_id,
WMS_Xdock_Pegging_Pub.get_default_routing_id(l_organization_id,
l_inventory_item_id,
poh.vendor_id)) <> 3
AND NOT EXISTS (SELECT 'Invalid Destination'
FROM po_distributions_all pod2
WHERE pod2.po_header_id = poll.po_header_id
AND pod2.po_line_id = poll.po_line_id
AND pod2.line_location_id = poll.line_location_id
AND NVL(pod2.destination_type_code, pod2.destination_context) IN
('EXPENSE','SHOP FLOOR'))
AND NOT EXISTS (SELECT 'Drop Ship'
FROM oe_drop_ship_sources odss
WHERE odss.po_header_id = poll.po_header_id
AND odss.po_line_id = poll.po_line_id
AND odss.line_location_id = poll.line_location_id)
GROUP BY poll.po_header_id, poll.po_line_id, poll.line_location_id, muom.uom_code
HAVING COUNT(DISTINCT NVL(pod.project_id, -999)) = 1
AND COUNT(DISTINCT NVL(pod.task_id, -999)) = 1;
SELECT
rsl.po_header_id AS header_id,
rsl.po_line_location_id AS line_id,
rsl.shipment_line_id AS line_detail_id,
NULL AS quantity,
muom.uom_code AS uom_code,
NULL AS primary_quantity,
NULL AS secondary_quantity,
NULL AS secondary_uom_code,
MIN(pod.project_id) AS project_id,
MIN(pod.task_id) AS task_id,
NULL AS lpn_id
FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_lines_all pol, po_line_types plt,
po_line_locations_all poll, po_distributions_all pod, mtl_units_of_measure muom
WHERE rsh.shipment_num IS NOT NULL
AND rsh.receipt_source_code = 'VENDOR'
AND rsh.asn_type in ('ASN','ASBN')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.to_organization_id = l_organization_id
AND rsl.shipment_line_status_code in ('EXPECTED','PARTIALLY RECEIVED')
AND rsl.item_id = l_inventory_item_id
AND rsl.quantity_shipped > NVL(rsl.quantity_received, 0)
AND rsl.po_line_id = pol.po_line_id
AND pol.line_type_id = plt.line_type_id
AND NVL(plt.outside_operation_flag, 'N') = 'N'
AND pol.po_line_id = poll.po_line_id
AND rsl.po_line_location_id = poll.line_location_id
AND pod.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND rsl.unit_of_measure = muom.unit_of_measure
AND NVL(poll.receiving_routing_id,
WMS_Xdock_Pegging_Pub.get_default_routing_id(l_organization_id,
l_inventory_item_id,
rsh.vendor_id)) <> 3
AND NOT EXISTS (SELECT 'Invalid Destination'
FROM po_distributions_all pod2
WHERE pod2.po_header_id = poll.po_header_id
AND pod2.po_line_id = poll.po_line_id
AND pod2.line_location_id = poll.line_location_id
AND NVL(pod2.destination_type_code, pod2.destination_context) IN
('EXPENSE','SHOP FLOOR'))
AND NOT EXISTS (SELECT 'Drop Ship'
FROM oe_drop_ship_sources odss
WHERE odss.po_header_id = poll.po_header_id
AND odss.po_line_id = poll.po_line_id
AND odss.line_location_id = poll.line_location_id)
GROUP BY rsl.po_header_id, rsl.po_line_location_id, rsl.shipment_line_id,
muom.uom_code
HAVING COUNT(DISTINCT NVL(pod.project_id, -999)) = 1
AND COUNT(DISTINCT NVL(pod.task_id, -999)) = 1;
SELECT
prl.requisition_header_id AS header_id,
prl.requisition_line_id AS line_id,
rsl.shipment_line_id AS line_detail_id,
NULL AS quantity,
muom.uom_code AS uom_code,
NULL AS primary_quantity,
NULL AS secondary_quantity,
NULL AS secondary_uom_code,
MIN(prd.project_id) AS project_id,
MIN(prd.task_id) AS task_id,
NULL AS lpn_id
FROM po_requisition_headers_all prh, po_requisition_lines_all prl,
rcv_shipment_lines rsl, rcv_shipment_headers rsh, po_req_distributions_all prd,
mtl_units_of_measure muom
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prd.requisition_line_id = prl.requisition_line_id
AND prh.authorization_status = 'APPROVED'
AND NVL(prl.cancel_flag,'N') = 'N'
AND prl.source_type_code = 'INVENTORY'
AND prl.destination_organization_id = l_organization_id
AND prl.item_id = l_inventory_item_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND rsl.routing_header_id > 0
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED')
AND rsl.to_organization_id = l_organization_id
AND rsl.item_id = l_inventory_item_id
AND rsl.quantity_shipped > NVL(rsl.quantity_received, 0)
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.unit_of_measure = muom.unit_of_measure
AND NVL(rsl.routing_header_id,
WMS_Xdock_Pegging_Pub.get_default_routing_id(l_organization_id,
l_inventory_item_id,
rsh.vendor_id)) <> 3
AND NVL(NVL(prl.destination_type_code, prl.destination_context), 'INVENTORY') NOT IN
('EXPENSE', 'SHOP FLOOR')
GROUP BY prl.requisition_header_id, prl.requisition_line_id, rsl.shipment_line_id,
muom.uom_code
HAVING COUNT(DISTINCT NVL(prd.project_id, -999)) = 1
AND COUNT(DISTINCT NVL(prd.task_id, -999)) = 1
UNION
-- Approved but not shipped Internal Reqs
SELECT
prl.requisition_header_id AS header_id,
prl.requisition_line_id AS line_id,
NULL AS line_detail_id,
NULL AS quantity,
muom.uom_code AS uom_code,
NULL AS primary_quantity,
NULL AS secondary_quantity,
NULL AS secondary_uom_code,
MIN(prd.project_id) AS project_id,
MIN(prd.task_id) AS task_id,
NULL AS lpn_id
FROM po_requisition_headers_all prh, po_requisition_lines_all prl,
po_req_distributions_all prd, mtl_interorg_parameters mip, mtl_units_of_measure muom
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prd.requisition_line_id = prl.requisition_line_id
AND prh.authorization_status = 'APPROVED'
AND NVL(prl.cancel_flag,'N') = 'N'
AND prl.source_type_code = 'INVENTORY'
AND prl.destination_organization_id = l_organization_id
AND prl.item_id = l_inventory_item_id
AND NOT EXISTS (SELECT 'Ship Confirmed'
FROM rcv_shipment_lines rsl
WHERE rsl.requisition_line_id = prl.requisition_line_id
AND rsl.routing_header_id > 0
AND rsl.shipment_line_status_code <> 'CANCELLED'
AND rsl.to_organization_id = l_organization_id
AND rsl.item_id = l_inventory_item_id)
AND mip.from_organization_id = prl.source_organization_id
AND mip.to_organization_id = prl.destination_organization_id
AND prl.unit_meas_lookup_code = muom.unit_of_measure
AND NVL(mip.routing_header_id,
WMS_Xdock_Pegging_Pub.get_default_routing_id(l_organization_id,
l_inventory_item_id,
prl.vendor_id)) <> 3
AND NVL(NVL(prl.destination_type_code, prl.destination_context), 'INVENTORY') NOT IN
('EXPENSE', 'SHOP FLOOR')
GROUP BY prl.requisition_header_id, prl.requisition_line_id, muom.uom_code
HAVING COUNT(DISTINCT NVL(prd.project_id, -999)) = 1
AND COUNT(DISTINCT NVL(prd.task_id, -999)) = 1;
SELECT
rsl.shipment_header_id AS header_id,
rsl.shipment_line_id AS line_id,
NULL AS line_detail_id,
NULL AS quantity,
muom.uom_code AS uom_code,
NULL AS primary_quantity,
NULL AS secondary_quantity,
NULL AS secondary_uom_code,
NULL AS project_id,
NULL AS task_id,
NULL AS lpn_id
FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, mtl_units_of_measure muom
WHERE rsh.shipment_num IS NOT NULL
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsh.receipt_source_code = 'INVENTORY'
AND EXISTS (SELECT 'Available Supply'
FROM mtl_supply ms
WHERE ms.to_organization_id = l_organization_id
AND ms.shipment_header_id = rsh.shipment_header_id)
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED')
AND rsl.to_organization_id = l_organization_id
AND rsl.item_id = l_inventory_item_id
AND rsl.quantity_shipped > NVL(rsl.quantity_received, 0)
AND rsl.unit_of_measure = muom.unit_of_measure
AND NVL(rsl.routing_header_id,
WMS_Xdock_Pegging_Pub.get_default_routing_id(l_organization_id,
l_inventory_item_id,
rsh.vendor_id)) <> 3
AND NVL(NVL(rsl.destination_type_code, rsl.destination_context), 'INVENTORY') NOT IN
('EXPENSE','SHOP FLOOR');
SELECT
mtrl.header_id AS header_id,
mtrl.line_id AS line_id,
NULL AS line_detail_id,
mtrl.quantity AS quantity,
mtrl.uom_code AS uom_code,
mtrl.primary_quantity AS primary_quantity,
mtrl.secondary_quantity AS secondary_quantity,
mtrl.secondary_uom_code AS secondary_uom_code,
mtrl.project_id AS project_id,
mtrl.task_id AS task_id,
mtrl.lpn_id AS lpn_id
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
wms_license_plate_numbers wlpn
WHERE mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrl.organization_id = l_organization_id
AND mtrl.inventory_item_id = l_inventory_item_id
-- Modified the line below to use an IN instead of <> so the
-- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
-- AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
inv_globals.g_to_status_approved)
AND mtrl.backorder_delivery_detail_id IS NULL
AND mtrl.lpn_id IS NOT NULL
AND mtrl.quantity > 0
AND NVL(mtrl.quantity_delivered, 0) = 0
AND NVL(mtrl.quantity_detailed, 0) = 0
AND NVL(mtrl.inspection_status, 2) = 2
AND NVL(mtrl.wms_process_flag, 1) = 1
AND NVL(mtrl.reference, 'non-RMA') <> 'ORDER_LINE_ID'
AND mtrl.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context = 3
-- Added the following line so the index: WMS_LICENSE_PLATE_NUMBERS_N6
-- can be used in case the SQL optimizer uses WLPN as the driving table.
AND wlpn.organization_id = l_organization_id;
SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id
FOR UPDATE NOWAIT;
SELECT reservation_id, supply_source_type_id, supply_source_header_id,
supply_source_line_id, supply_source_line_detail,
reservation_quantity, reservation_uom_code,
secondary_reservation_quantity, secondary_uom_code,
primary_reservation_quantity, primary_uom_code
FROM mtl_reservations
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_source_type_id = l_demand_type_id
AND demand_source_line_id = l_demand_line_id
AND supply_source_type_id <> inv_reservation_global.g_source_type_inv
AND NVL(crossdock_flag, 'N') = 'N'
AND primary_reservation_quantity - NVL(detailed_quantity, 0) > 0
FOR UPDATE SKIP LOCKED; --Bug 6813492
SELECT muom.uom_code, pod.project_id, pod.task_id
FROM po_line_locations_all poll, mtl_units_of_measure muom,
(SELECT po_header_id, po_line_id, line_location_id,
MIN(project_id) AS project_id, MIN(task_id) AS task_id
FROM po_distributions_all
WHERE po_header_id = l_supply_header_id
AND line_location_id = l_supply_line_id
GROUP BY po_header_id, po_line_id, line_location_id
HAVING COUNT(DISTINCT NVL(project_id, -999)) = 1
AND COUNT(DISTINCT NVL(task_id, -999)) = 1
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(MIN(project_id), -999) = NVL(p_project_id, -999) AND
NVL(MIN(task_id), -999) = NVL(p_task_id, -999)))) pod
WHERE poll.po_header_id = l_supply_header_id
AND poll.line_location_id = l_supply_line_id
AND poll.unit_meas_lookup_code = muom.unit_of_measure
AND (p_uom_code IS NULL OR muom.uom_code = p_uom_code)
AND pod.po_header_id = poll.po_header_id
AND pod.po_line_id = poll.po_line_id
AND pod.line_location_id = poll.line_location_id
FOR UPDATE OF poll.line_location_id NOWAIT;
SELECT muom.uom_code, pod.project_id, pod.task_id
FROM rcv_shipment_lines rsl, po_line_locations_all poll, mtl_units_of_measure muom,
(SELECT po_header_id, po_line_id, line_location_id,
MIN(project_id) AS project_id, MIN(task_id) AS task_id
FROM po_distributions_all
WHERE po_header_id = l_supply_header_id
AND line_location_id = l_supply_line_id
GROUP BY po_header_id, po_line_id, line_location_id
HAVING COUNT(DISTINCT NVL(project_id, -999)) = 1
AND COUNT(DISTINCT NVL(task_id, -999)) = 1
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(MIN(project_id), -999) = NVL(p_project_id, -999) AND
NVL(MIN(task_id), -999) = NVL(p_task_id, -999)))) pod
WHERE rsl.po_header_id = l_supply_header_id
AND rsl.po_line_location_id = l_supply_line_id
AND rsl.shipment_line_id = l_supply_line_detail_id
AND rsl.po_line_location_id = poll.line_location_id
AND rsl.unit_of_measure = muom.unit_of_measure
AND (p_uom_code IS NULL OR muom.uom_code = p_uom_code)
AND pod.po_header_id = poll.po_header_id
AND pod.po_line_id = poll.po_line_id
AND pod.line_location_id = poll.line_location_id
FOR UPDATE OF rsl.shipment_line_id, poll.line_location_id NOWAIT;
SELECT muom_prl.uom_code, prd.project_id, prd.task_id
FROM po_requisition_lines_all prl, rcv_shipment_lines rsl, mtl_units_of_measure muom_prl,
(SELECT requisition_line_id, MIN(project_id) AS project_id, MIN(task_id) AS task_id
FROM po_req_distributions_all
WHERE requisition_line_id = l_supply_line_id
GROUP BY requisition_line_id
HAVING COUNT(DISTINCT NVL(project_id, -999)) = 1
AND COUNT(DISTINCT NVL(task_id, -999)) = 1
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(MIN(project_id), -999) = NVL(p_project_id, -999) AND
NVL(MIN(task_id), -999) = NVL(p_task_id, -999)))) prd
WHERE prl.requisition_header_id = l_supply_header_id
AND prl.requisition_line_id = l_supply_line_id
AND prl.unit_meas_lookup_code = muom_prl.unit_of_measure
AND prl.requisition_line_id = rsl.requisition_line_id (+)
AND (p_uom_code IS NULL OR muom_prl.uom_code = p_uom_code)
AND prd.requisition_line_id = prl.requisition_line_id
FOR UPDATE OF prl.requisition_line_id, rsl.shipment_line_id NOWAIT;
SELECT muom.uom_code, NULL AS project_id, NULL AS task_id
FROM rcv_shipment_lines rsl, mtl_units_of_measure muom
WHERE rsl.shipment_header_id = l_supply_header_id
AND rsl.shipment_line_id = l_supply_line_id
AND rsl.unit_of_measure = muom.unit_of_measure
AND (p_uom_code IS NULL OR muom.uom_code = p_uom_code)
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(p_project_id IS NULL AND p_task_id IS NULL))
FOR UPDATE OF rsl.shipment_line_id NOWAIT;
SELECT
mtrl.header_id AS header_id,
mtrl.line_id AS line_id,
NULL AS line_detail_id,
mtrl.quantity AS quantity,
mtrl.uom_code AS uom_code,
mtrl.primary_quantity AS primary_quantity,
mtrl.secondary_quantity AS secondary_quantity,
mtrl.secondary_uom_code AS secondary_uom_code,
mtrl.project_id AS project_id,
mtrl.task_id AS task_id,
mtrl.lpn_id AS lpn_id
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
wms_license_plate_numbers wlpn
WHERE mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrl.organization_id = l_organization_id
AND mtrl.inventory_item_id = l_inventory_item_id
-- Modified the line below to use an IN instead of <> so the
-- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
-- AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
inv_globals.g_to_status_approved)
AND mtrl.backorder_delivery_detail_id IS NULL
AND mtrl.lpn_id IS NOT NULL
AND mtrl.quantity > 0
AND NVL(mtrl.quantity_delivered, 0) = 0
AND NVL(mtrl.quantity_detailed, 0) = 0
AND NVL(mtrl.inspection_status, 2) = 2
AND NVL(mtrl.wms_process_flag, 1) = 1
AND NVL(mtrl.reference, 'non-RMA') <> 'ORDER_LINE_ID'
AND (p_uom_code IS NULL OR mtrl.uom_code = p_uom_code)
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(mtrl.project_id, -999) = NVL(p_project_id, -999) AND
NVL(mtrl.task_id, -999) = NVL(p_task_id, -999)))
AND mtrl.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context = 3
-- Added the following line so the index: WMS_LICENSE_PLATE_NUMBERS_N6
-- can be used in case the SQL optimizer uses WLPN as the driving table.
AND wlpn.organization_id = l_organization_id
ORDER BY ABS(mtrl.primary_quantity - p_rsv_prim_qty) ASC
FOR UPDATE OF mtrl.line_id NOWAIT;
SELECT source_code
FROM wms_xdock_source_assignments
WHERE criterion_id = l_crossdock_criteria_id
AND source_type = G_SRC_TYPE_SUP
ORDER BY priority;
SELECT ROWID,
inventory_item_id,
xdock_source_code,
source_type_id,
source_header_id,
source_line_id,
source_line_detail_id,
dock_start_time,
dock_mean_time,
dock_end_time,
expected_time,
quantity,
reservable_quantity,
uom_code,
primary_quantity,
secondary_quantity,
secondary_uom_code,
project_id,
task_id,
lpn_id,
wip_supply_type
FROM wms_xdock_pegging_gtmp
WHERE inventory_item_id = l_inventory_item_id
AND xdock_source_code IN (p_po_sup, p_asn_sup, p_intreq_sup, p_intship_sup, p_rcv_sup)
AND ((expected_time IS NOT NULL AND (p_past_due_time IS NULL OR
expected_time > SYSDATE - p_past_due_interval)) OR
(dock_start_time IS NOT NULL AND (p_past_due_time IS NULL OR
dock_start_time > SYSDATE - p_past_due_interval)))
-- Only pick up supply lines that match the project/task if necessary
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(project_id, -999) = NVL(p_project_id, -999) AND
NVL(task_id, -999) = NVL(p_task_id, -999)))
AND (-- Dock Appointment Exists
(dock_start_time IS NOT NULL AND
((p_sup_sched_method = G_APPT_START_TIME AND
dock_start_time BETWEEN p_xdock_start_time AND p_xdock_end_time) OR
(p_sup_sched_method = G_APPT_MEAN_TIME AND
dock_mean_time BETWEEN p_xdock_start_time AND p_xdock_end_time) OR
(p_sup_sched_method = G_APPT_END_TIME AND
dock_end_time BETWEEN p_xdock_start_time AND p_xdock_end_time))
)
-- No Dock Appointment but supply can be rescheduled
OR (dock_start_time IS NULL AND p_sup_resched_flag = 1 AND
expected_time BETWEEN TRUNC(p_xdock_start_time) AND
TO_DATE(TO_CHAR(TRUNC(p_xdock_end_time), 'DD-MON-YYYY') ||
' 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
)
-- No Dock Appointment and supply cannot be rescheduled
OR (dock_start_time IS NULL AND p_sup_resched_flag = 2 AND
expected_time BETWEEN p_xdock_start_time AND p_xdock_end_time
)
)
ORDER BY DECODE (xdock_source_code,
G_PLAN_SUP_PO_APPR, p_po_priority,
G_PLAN_SUP_ASN, p_asn_priority,
G_PLAN_SUP_REQ, p_intreq_priority,
G_PLAN_SUP_INTR, p_intship_priority,
G_PLAN_SUP_RCV, p_rcv_priority,
99),
-- For In Receiving supply lines, order by the quantity closest to the
-- ATR demand qty we are crossdocking for. The expected times should all
-- be the same since the material has already been received.
-- Putting this order by first before the crossdocking goal since only
-- In Receiving supply lines will have a non-null value for primary_quantity.
-- For other supply types, this order by will do nothing. Doing this since the
-- expected time for In Receiving lines just use SYSDATE. We do not want the
-- order the MOL's are encountered and inserted into wms_xdock_pegging_gtmp to
-- affect the order we consume them for crossdocking.
ABS(NVL(primary_quantity, 0) - p_demand_prim_qty) ASC,
DECODE (p_crossdock_goal,
G_MINIMIZE_WAIT, SYSDATE - NVL(expected_time, dock_start_time),
G_MAXIMIZE_XDOCK, NVL(expected_time, dock_start_time) - SYSDATE,
G_CUSTOM_GOAL, NULL,
NULL);
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_line_detail_id_tb.DELETE;
l_dock_start_time_tb.DELETE;
l_dock_mean_time_tb.DELETE;
l_dock_end_time_tb.DELETE;
l_expected_time_tb.DELETE;
l_quantity_tb.DELETE;
l_uom_code_tb.DELETE;
l_primary_quantity_tb.DELETE;
l_secondary_quantity_tb.DELETE;
l_secondary_uom_code_tb.DELETE;
l_project_id_tb.DELETE;
l_task_id_tb.DELETE;
l_lpn_id_tb.DELETE;
l_src_types_retrieved_tb.DELETE;
l_item_params_tb.DELETE;
l_uom_class_tb.DELETE;
g_item_uom_conversion_tb.DELETE;
l_supply_src_types_tb.DELETE;
l_locked_mols_tb.DELETE;
l_crossdocked_lpns_tb.DELETE;
l_detail_info_tab.DELETE;
g_crossdock_criteria_tb.DELETE;
SELECT NVL(allow_cross_proj_issues, 'N')
INTO l_allow_cross_proj_issues
FROM pjm_org_parameters
WHERE organization_id = l_organization_id;
SELECT primary_uom_code, NVL(reservable_type, 1),
NVL(lot_control_code, 1), NVL(lot_divisible_flag, 'Y'), item_type
INTO l_item_params_tb(l_inventory_item_id)
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT uom_class
INTO l_uom_class_tb(l_demand_uom_code)
FROM mtl_units_of_measure
WHERE uom_code = l_demand_uom_code;
l_existing_rsvs_tb.DELETE;
l_rcv_lines_tb.DELETE;
-- 2.4 - Crossdock detail the reservation and update the demand and supply line records.
IF (l_debug = 1) THEN
print_debug('2.4 - Crossdock detail the relevant records: RSV, WDD, supply');
p_wsh_release_table.DELETE(l_split_wdd_index);
p_del_detail_id.DELETE(l_split_delivery_index);
p_trolin_delivery_ids.DELETE(l_split_delivery_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
p_wsh_release_table.DELETE(l_split_wdd_index);
p_del_detail_id.DELETE(l_split_delivery_index);
p_trolin_delivery_ids.DELETE(l_split_delivery_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
p_wsh_release_table.DELETE(l_split_wdd_index);
p_del_detail_id.DELETE(l_split_delivery_index);
p_trolin_delivery_ids.DELETE(l_split_delivery_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
l_rcv_lines_tb.DELETE;
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_line_detail_id_tb.DELETE;
l_dock_start_time_tb.DELETE;
l_dock_mean_time_tb.DELETE;
l_dock_end_time_tb.DELETE;
l_expected_time_tb.DELETE;
l_quantity_tb.DELETE;
l_uom_code_tb.DELETE;
l_primary_quantity_tb.DELETE;
l_secondary_quantity_tb.DELETE;
l_secondary_uom_code_tb.DELETE;
l_project_id_tb.DELETE;
l_task_id_tb.DELETE;
l_lpn_id_tb.DELETE;
-- 3.4 - Insert the available supply lines into the global temp table.
-- {{
-- Make sure the valid supply lines are properly inserted into the
-- global temp table. }}
IF (l_debug = 1) THEN
print_debug('3.4 - Insert the available supply lines into the global temp table');
INSERT INTO wms_xdock_pegging_gtmp
(inventory_item_id,
xdock_source_code,
source_type_id,
source_header_id,
source_line_id,
source_line_detail_id,
dock_start_time,
dock_mean_time,
dock_end_time,
expected_time,
quantity,
uom_code,
primary_quantity,
secondary_quantity,
secondary_uom_code,
project_id,
task_id,
lpn_id
)
VALUES
(l_inventory_item_id,
l_supply_src_code,
l_supply_type_id,
l_header_id_tb(k),
l_line_id_tb(k),
l_line_detail_id_tb(k),
l_dock_start_time_tb(k),
l_dock_mean_time_tb(k),
l_dock_end_time_tb(k),
l_expected_time_tb(k),
l_quantity_tb(k),
l_uom_code_tb(k),
l_primary_quantity_tb(k),
l_secondary_quantity_tb(k),
l_secondary_uom_code_tb(k),
l_project_id_tb(k),
l_task_id_tb(k),
l_lpn_id_tb(k)
);
print_debug('3.4 - Error inserting available supply lines into temp table');
-- If an exception occurs while inserting supply line records, just
-- rollback the changes and go to the next WDD record to crossdock.
ROLLBACK TO Supply_Lines_sp;
print_debug('3.4 - Successfully inserted ' || l_header_id_tb.COUNT ||
' available supply lines into temp table');
-- Clear the PLSQL tables used once the data is inserted into the global temp table
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_line_detail_id_tb.DELETE;
l_dock_start_time_tb.DELETE;
l_dock_mean_time_tb.DELETE;
l_dock_end_time_tb.DELETE;
l_expected_time_tb.DELETE;
l_quantity_tb.DELETE;
l_uom_code_tb.DELETE;
l_primary_quantity_tb.DELETE;
l_secondary_quantity_tb.DELETE;
l_secondary_uom_code_tb.DELETE;
l_project_id_tb.DELETE;
l_task_id_tb.DELETE;
l_lpn_id_tb.DELETE;
l_shopping_basket_tb.DELETE;
-- just delete the supply line from the shopping basket table. Shopping basket
-- table can therefore be a sparsely populated table after running custom logic.
l_shopping_basket_tb.DELETE(i);
-- just delete the supply line from the shopping basket table. Shopping basket
-- table can therefore be a sparsely populated table after running custom logic.
l_shopping_basket_tb.DELETE(i);
l_sorted_order_tb.DELETE;
print_debug('3.6 - Custom API is NOT implemented even though Custom Goal is selected!');
l_indices_used_tb.DELETE;
l_shopping_basket_temp_tb.DELETE;
l_shopping_basket_temp_tb.DELETE;
l_shopping_basket_temp_tb.DELETE;
l_supply_rowid_tb.DELETE;
l_supply_atr_qty_tb.DELETE;
-- Initialize the supply ATR index variable which is used to update
-- the ATR qty for the supply lines used in this loop when crossdocked.
l_supply_atr_index := NULL;
-- They will be used to update/split the MOL supply line used for crossdocking.
IF (l_supply_type_id = 27) THEN
l_mol_header_id := l_supply_header_id;
l_rcv_lines_tb.DELETE;
l_rcv_lines_tb.DELETE;
-- Update the supply line record in the global temp table with the reservable_quantity
-- available after using up ATD qty from it for crossdocking.
-- Do not do this if the supply is of type In Receiving or Internal Requisition.
-- The reason is there can be multiple supply lines of these types in the shopping
-- basket table. However, when we create reservations, they will not be at that
-- line level detail. e.g. Receiving supply reservations will only be at the org/item
-- level while Internal Reqs will be at the Req header/line level (and not the shipment
-- line level). Availability to reserve needs to take the whole set of lines into account
-- so it should be recalculated each time.
IF (l_supply_type_id NOT IN (7, 27)) THEN
IF (l_debug = 1) THEN
print_debug('4.3 - Update the ATR qty for the crossdocked supply line');
-- just store the values for the record that needs to be updated. We will do a
-- bulk update outside the loop. In case of exception for the current supply line
-- used for crossdocking, we should remove that record from these tables so the
-- global temp table won't be updated incorrectly. This needs to be only done
-- when we are still staying within the supply lines loop. For exceptions where
-- we go to the next_record, we do not need to remove the current supply line record.
-- The reason is because the bulk update logic after the supply lines loop is skipped.
l_supply_atr_index := l_supply_rowid_tb.COUNT + 1;
-- Commented the below out since we will do a bulk update instead outside
-- the supply lines loop
/*BEGIN
UPDATE wms_xdock_pegging_gtmp
SET reservable_quantity = l_supply_atr_qty - l_atd_qty
WHERE ROWID = l_shopping_basket_tb(l_supply_index).ROWID;
print_debug('4.3 - Could not update the ATR quantity for the supply line');
-- Remove the supply line from these tables so the ATR qty is not updated
IF (l_supply_atr_index IS NOT NULL) THEN
l_supply_rowid_tb.DELETE(l_supply_atr_index);
l_supply_atr_qty_tb.DELETE(l_supply_atr_index);
p_wsh_release_table.DELETE(l_split_wdd_index);
p_del_detail_id.DELETE(l_split_delivery_index);
p_trolin_delivery_ids.DELETE(l_split_delivery_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
p_wsh_release_table.DELETE(l_split_wdd_index);
p_del_detail_id.DELETE(l_split_delivery_index);
p_trolin_delivery_ids.DELETE(l_split_delivery_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
p_wsh_release_table.DELETE(l_split_wdd_index);
p_del_detail_id.DELETE(l_split_delivery_index);
p_trolin_delivery_ids.DELETE(l_split_delivery_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
print_debug('4.5 - For all crossdocked supply lines, update the ATR qty in the temp table');
UPDATE wms_xdock_pegging_gtmp
SET reservable_quantity = l_supply_atr_qty_tb(i)
WHERE ROWID = l_supply_rowid_tb(i);
print_debug('4.5 - Could not update the ATR qty for the crossdocked supply lines!');
-- If an exception occurs while performing this bulk update, raise an exception.
-- This error should not occur but if it does, we should stop the crossdock pegging
-- process.
RAISE fnd_api.g_exc_error;
print_debug('4.5 - Successfully updated the ATR qty for ' || l_supply_rowid_tb.COUNT
|| ' (non RCV or Int Req) supply lines ');
print_debug('5.1 - Call the Create_Update_Delivery_Detail API for ' ||
l_detail_info_tab.COUNT || ' crossdocked WDD records');
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec
);
print_debug('5.1 - Error returned from Create_Update_Delivery_Detail API: '
|| x_return_status);
print_debug('5.1 - Successfully updated the crossdocked WDD records');
-- Insert the crossdocked WDD lines into the delivery tables
IF (p_wsh_release_table(l_wdd_index).released_status = 'S') THEN
l_index := NVL(p_del_detail_id.LAST, 0) + 1;
print_debug('5.3 - Successfully inserted ' || p_del_detail_id.COUNT ||
' crossdocked WDD lines into delivery tables');
l_shipping_attr.DELETE;
print_debug('5.4 - Call the Update_Shipping_Attributes API to backorder ' ||
l_shipping_attr.COUNT || ' WDD records');
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => x_return_status
);
print_debug('5.4 - Error returned from Update_Shipping_Attributes API: '
|| x_return_status);
p_del_detail_id.DELETE(l_index);
p_trolin_delivery_ids.DELETE(l_index);
DELETE wms_xdock_pegging_gtmp;
SELECT
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) AS header_id,
wdd.source_line_id AS line_id,
wdd.delivery_detail_id AS line_detail_id,
wdd.requested_quantity AS quantity,
wdd.requested_quantity_uom AS uom_code,
wdd.requested_quantity2 AS secondary_quantity,
wdd.requested_quantity_uom2 AS secondary_uom_code,
wdd.project_id AS project_id,
wdd.task_id AS task_id,
NULL AS wip_supply_type
FROM wsh_delivery_details wdd, oe_order_lines_all ool
WHERE wdd.organization_id = l_organization_id
AND wdd.inventory_item_id = l_inventory_item_id
AND wdd.released_status = 'R'
AND wdd.source_code = 'OE'
AND wdd.requested_quantity > 0
AND wdd.source_line_id = ool.line_id
AND NVL(ool.source_document_type_id, -999) <> 10
AND ool.booked_flag = 'Y'
AND ool.open_flag = 'Y'
AND NOT EXISTS (SELECT 'Drop Ship'
FROM oe_drop_ship_sources odss
WHERE odss.header_id = ool.header_id
AND odss.line_id = ool.line_id)
-- Only pick up WDD lines that match the project/task of the MOL if necessary
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(wdd.project_id, -999) = NVL(p_project_id, -999) AND
NVL(wdd.task_id, -999) = NVL(p_task_id, -999)));
SELECT
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) AS header_id,
wdd.source_line_id AS line_id,
wdd.delivery_detail_id AS line_detail_id,
wdd.requested_quantity AS quantity,
wdd.requested_quantity_uom AS uom_code,
wdd.requested_quantity2 AS secondary_quantity,
wdd.requested_quantity_uom2 AS secondary_uom_code,
wdd.project_id AS project_id,
wdd.task_id AS task_id,
NULL AS wip_supply_type
FROM wsh_delivery_details wdd, oe_order_lines_all ool
WHERE wdd.organization_id = l_organization_id
AND wdd.inventory_item_id = l_inventory_item_id
AND wdd.released_status = 'B'
AND wdd.source_code = 'OE'
AND wdd.requested_quantity > 0
AND wdd.source_line_id = ool.line_id
AND NVL(ool.source_document_type_id, -999) <> 10
AND ool.booked_flag = 'Y'
AND ool.open_flag = 'Y'
AND NOT EXISTS (SELECT 'Drop Ship'
FROM oe_drop_ship_sources odss
WHERE odss.header_id = ool.header_id
AND odss.line_id = ool.line_id)
-- Only pick up WDD lines that match the project/task of the MOL if necessary
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(wdd.project_id, -999) = NVL(p_project_id, -999) AND
NVL(wdd.task_id, -999) = NVL(p_task_id, -999)));
SELECT
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) AS header_id,
wdd.source_line_id AS line_id,
wdd.delivery_detail_id AS line_detail_id,
wdd.requested_quantity AS quantity,
wdd.requested_quantity_uom AS uom_code,
wdd.requested_quantity2 AS secondary_quantity,
wdd.requested_quantity_uom2 AS secondary_uom_code,
wdd.project_id AS project_id,
wdd.task_id AS task_id,
NULL AS wip_supply_type
FROM wsh_delivery_details wdd, oe_order_lines_all ool
WHERE wdd.organization_id = l_organization_id
AND wdd.inventory_item_id = l_inventory_item_id
AND wdd.released_status = 'R'
AND wdd.source_code = 'OE'
AND wdd.requested_quantity > 0
AND wdd.source_line_id = ool.line_id
AND NVL(ool.source_document_type_id, -999) = 10
AND ool.booked_flag = 'Y'
AND ool.open_flag = 'Y'
AND NOT EXISTS (SELECT 'Drop Ship'
FROM oe_drop_ship_sources odss
WHERE odss.header_id = ool.header_id
AND odss.line_id = ool.line_id)
-- Only pick up WDD lines that match the project/task of the MOL if necessary
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(wdd.project_id, -999) = NVL(p_project_id, -999) AND
NVL(wdd.task_id, -999) = NVL(p_task_id, -999)));
SELECT
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) AS header_id,
wdd.source_line_id AS line_id,
wdd.delivery_detail_id AS line_detail_id,
wdd.requested_quantity AS quantity,
wdd.requested_quantity_uom AS uom_code,
wdd.requested_quantity2 AS secondary_quantity,
wdd.requested_quantity_uom2 AS secondary_uom_code,
wdd.project_id AS project_id,
wdd.task_id AS task_id,
NULL AS wip_supply_type
FROM wsh_delivery_details wdd, oe_order_lines_all ool
WHERE wdd.organization_id = l_organization_id
AND wdd.inventory_item_id = l_inventory_item_id
AND wdd.released_status = 'B'
AND wdd.source_code = 'OE'
AND wdd.requested_quantity > 0
AND wdd.source_line_id = ool.line_id
AND NVL(ool.source_document_type_id, -999) = 10
AND ool.booked_flag = 'Y'
AND ool.open_flag = 'Y'
AND NOT EXISTS (SELECT 'Drop Ship'
FROM oe_drop_ship_sources odss
WHERE odss.header_id = ool.header_id
AND odss.line_id = ool.line_id)
-- Only pick up WDD lines that match the project/task of the MOL if necessary
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(wdd.project_id, -999) = NVL(p_project_id, -999) AND
NVL(wdd.task_id, -999) = NVL(p_task_id, -999)));
SELECT
wmsv.wip_entity_id AS header_id,
wmsv.operation_seq_num AS line_id,
wmsv.repetitive_schedule_id AS line_detail_id,
wmsv.date_required AS expected_time,
wmsv.quantity_backordered AS quantity,
wmsv.primary_uom_code AS uom_code,
NULL AS secondary_quantity,
NULL AS secondary_uom_code,
wmsv.project_id AS project_id,
wmsv.task_id AS task_id,
wmsv.wip_supply_type AS wip_supply_type
FROM wip_material_shortages_v wmsv
WHERE wmsv.organization_id = l_organization_id
AND wmsv.inventory_item_id = l_inventory_item_id
AND wmsv.wip_entity_type in (1, 2, 5)
AND wmsv.date_required IS NOT NULL
-- Only pick up WIP demand lines that lie within the crossdock window
-- OR have a date_required value in the past.
AND (wmsv.date_required BETWEEN p_xdock_start_time AND p_xdock_end_time OR
wmsv.date_required < SYSDATE)
-- Only pick up WIP lines that match the project/task of the MOL if necessary
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(wmsv.project_id, -999) = NVL(p_project_id, -999) AND
NVL(wmsv.task_id, -999) = NVL(p_task_id, -999)));
SELECT mtrl.*,
msi.primary_uom_code AS primary_uom_code,
NVL(msi.reservable_type, 1) AS reservable_type,
NVL(msi.lot_control_code, 1) AS lot_control_code,
NVL(msi.lot_divisible_flag, 'Y') AS lot_divisible_flag,
wlpn.lpn_context AS lpn_context
FROM mtl_txn_request_lines mtrl, mtl_system_items msi, wms_license_plate_numbers wlpn
WHERE mtrl.line_id = p_move_order_line_id
AND mtrl.organization_id = l_organization_id
-- Modified the line below to use an IN instead of <>
-- AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
inv_globals.g_to_status_approved)
AND mtrl.backorder_delivery_detail_id IS NULL
AND mtrl.lpn_id IS NOT NULL
AND mtrl.quantity > 0
AND NVL(mtrl.quantity_delivered, 0) = 0
AND NVL(mtrl.quantity_detailed, 0) = 0
AND NVL(mtrl.inspection_status, 2) = 2
AND NVL(mtrl.wms_process_flag, 1) = 1
AND NVL(mtrl.reference, 'non-RMA') <> 'ORDER_LINE_ID'
AND mtrl.inventory_item_id = msi.inventory_item_id
AND mtrl.organization_id = msi.organization_id
AND mtrl.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context IN (2, 3) -- WIP or RCV
-- Added the following line so the index: WMS_LICENSE_PLATE_NUMBERS_N6
-- can be used in case the SQL optimizer uses WLPN as the driving table.
AND wlpn.organization_id = l_organization_id
FOR UPDATE OF mtrl.line_id NOWAIT;
SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
wms_license_plate_numbers wlpn
WHERE mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrl.organization_id = l_organization_id
AND mtrl.inventory_item_id = l_inventory_item_id
-- Modified the line below to use an IN instead of <> so the
-- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
-- AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
inv_globals.g_to_status_approved)
AND mtrl.backorder_delivery_detail_id IS NULL
AND mtrl.lpn_id IS NOT NULL
AND mtrl.quantity > 0
AND NVL(mtrl.quantity_delivered, 0) = 0
AND NVL(mtrl.quantity_detailed, 0) = 0
AND NVL(mtrl.inspection_status, 2) = 2
AND NVL(mtrl.wms_process_flag, 1) = 1
AND NVL(mtrl.reference, 'non-RMA') <> 'ORDER_LINE_ID'
AND mtrl.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context = p_lpn_context
-- Added the following line so the index: WMS_LICENSE_PLATE_NUMBERS_N6
-- can be used in case the SQL optimizer uses WLPN as the driving table.
AND wlpn.organization_id = l_organization_id
AND (p_lpn_context = 3 OR -- RCV
(p_lpn_context = 2 AND -- WIP
mtrl.txn_source_id = p_wip_entity_id)
-- The two lines below are not required since WIP putaway MOLs do not store
-- the operation sequence num or the repetitive schedule ID.
-- NVL(mtrl.txn_source_line_id, -999) = NVL(p_operation_seq_num, -999) AND
-- NVL(mtrl.reference_id, -999) = NVL(p_repetitive_schedule_id, -999))
)
FOR UPDATE OF mtrl.line_id NOWAIT;
SELECT reservation_id, demand_source_type_id, demand_source_header_id,
demand_source_line_id, reservation_quantity, reservation_uom_code,
secondary_reservation_quantity, secondary_uom_code,
primary_reservation_quantity, primary_uom_code
FROM mtl_reservations
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND ((p_lpn_context = 3 AND -- RCV supply
supply_source_type_id = inv_reservation_global.g_source_type_rcv)
OR
(p_lpn_context = 2 AND -- WIP supply
supply_source_type_id = inv_reservation_global.g_source_type_wip AND
supply_source_header_id = p_wip_entity_id))
AND demand_source_type_id <> inv_reservation_global.g_source_type_wip
AND demand_source_line_detail IS NULL
AND NVL(crossdock_flag, 'N') = 'N'
AND primary_reservation_quantity - NVL(detailed_quantity, 0) > 0
FOR UPDATE SKIP LOCKED;
SELECT wdd.delivery_detail_id, wdd.requested_quantity, wdd.requested_quantity_uom,
wdd.requested_quantity2, wdd.requested_quantity_uom2,
wdd.released_status, wdd.project_id, wdd.task_id
FROM wsh_delivery_details wdd, oe_order_lines_all ool
WHERE wdd.organization_id = l_organization_id
AND wdd.inventory_item_id = l_inventory_item_id
AND wdd.released_status IN ('R', 'B')
AND wdd.source_code = 'OE'
AND wdd.requested_quantity > 0
AND wdd.source_header_id = l_demand_header_id
AND wdd.source_line_id = l_demand_line_id
AND wdd.source_line_id = ool.line_id
AND ool.booked_flag = 'Y'
AND ool.open_flag = 'Y'
AND NOT EXISTS (SELECT 'Drop Ship'
FROM oe_drop_ship_sources odss
WHERE odss.header_id = ool.header_id
AND odss.line_id = ool.line_id)
-- Only pick up WDD lines that match the project/task of the MOL if necessary
AND (l_project_ref_enabled = 2 OR
l_allow_cross_proj_issues = 'Y' OR
(NVL(wdd.project_id, -999) = NVL(p_project_id, -999) AND
NVL(wdd.task_id, -999) = NVL(p_task_id, -999)))
FOR UPDATE OF wdd.delivery_detail_id SKIP LOCKED;
SELECT source_code
FROM wms_xdock_source_assignments
WHERE criterion_id = l_crossdock_criteria_id
AND source_type = G_SRC_TYPE_DEM
ORDER BY priority;
SELECT ROWID,
inventory_item_id,
xdock_source_code,
source_type_id,
source_header_id,
source_line_id,
source_line_detail_id,
dock_start_time,
dock_mean_time,
dock_end_time,
expected_time,
quantity,
reservable_quantity,
uom_code,
primary_quantity,
secondary_quantity,
secondary_uom_code,
project_id,
task_id,
lpn_id,
wip_supply_type
FROM wms_xdock_pegging_gtmp
WHERE inventory_item_id = l_inventory_item_id
ORDER BY DECODE (xdock_source_code,
G_OPP_DEM_SO_SCHED, p_so_sched_priority,
G_OPP_DEM_SO_BKORD, p_so_back_priority,
G_OPP_DEM_IO_SCHED, p_io_sched_priority,
G_OPP_DEM_IO_BKORD, p_io_back_priority,
G_OPP_DEM_WIP_BKORD, p_wip_priority,
99),
DECODE (p_crossdock_goal,
G_MINIMIZE_WAIT, SYSDATE - expected_time,
G_MAXIMIZE_XDOCK, expected_time - SYSDATE,
G_CUSTOM_GOAL, NULL,
NULL);
SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id
FOR UPDATE NOWAIT;
SELECT wip_entity_id
FROM wip_requirement_operations
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_operation_seq_num
AND NVL(repetitive_schedule_id, -999) = NVL(p_repetitive_schedule_id, -999)
FOR UPDATE NOWAIT;
SELECT NVL(SUM(mtrl.primary_quantity), 0)
FROM mtl_txn_request_lines mtrl, wsh_delivery_details wdd,
wms_license_plate_numbers wlpn
WHERE mtrl.organization_id = l_organization_id
AND mtrl.inventory_item_id = l_inventory_item_id
-- Modified the line below to use an IN instead of <> so the
-- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
-- AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
inv_globals.g_to_status_approved)
AND NVL(mtrl.quantity_delivered, 0) = 0
AND mtrl.txn_source_id = p_wip_entity_id
-- The two lines below are not required since WIP putaway MOLs do not store
-- the operation sequence num or the repetitive schedule ID.
-- AND NVL(mtrl.txn_source_line_id, -999) = NVL(p_operation_seq_num, -999)
-- AND NVL(mtrl.reference_id, -999) = NVL(p_repetitive_schedule_id, -999)
AND mtrl.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context = 2 -- WIP
-- Added the following line so the index: WMS_LICENSE_PLATE_NUMBERS_N6
-- can be used in case the SQL optimizer uses WLPN as the driving table.
AND wlpn.organization_id = l_organization_id
AND mtrl.crossdock_type = 1 -- Crossdocked to OE demand
AND mtrl.backorder_delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_header_id = p_demand_source_header_id
AND wdd.source_line_id = p_demand_source_line_id;
SELECT DISTINCT demand_source_type_id, demand_source_header_id, demand_source_line_id
FROM mtl_reservations
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND supply_source_type_id = inv_reservation_global.g_source_type_wip
AND supply_source_header_id = l_supply_header_id;
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_line_detail_id_tb.DELETE;
l_quantity_tb.DELETE;
l_uom_code_tb.DELETE;
l_secondary_quantity_tb.DELETE;
l_secondary_uom_code_tb.DELETE;
l_project_id_tb.DELETE;
l_task_id_tb.DELETE;
l_dock_start_time_tb.DELETE;
l_dock_mean_time_tb.DELETE;
l_dock_end_time_tb.DELETE;
l_expected_time_tb.DELETE;
l_wip_supply_type_tb.DELETE;
l_detail_info_tab.DELETE;
g_item_uom_conversion_tb.DELETE;
SELECT NVL(allow_cross_proj_issues, 'N')
INTO l_allow_cross_proj_issues
FROM pjm_org_parameters
WHERE organization_id = l_organization_id;
SELECT entity_type
INTO l_wip_entity_type
FROM wip_entities
WHERE wip_entity_id = l_supply_header_id
AND organization_id = l_organization_id;
SELECT DECODE(ool.source_document_type_id, 10, 8, 2),
wlc.demand_source_header_id, wlc.demand_source_line
INTO l_wip_demand_type_id, l_wip_demand_header_id, l_wip_demand_line_id
FROM wip_lpn_completions wlc, oe_order_lines_all ool
WHERE wlc.header_id = l_mol_rec.reference_id
-- MOL reference_id is link to header_id in WIP LPN Flow Completions table
AND wlc.wip_entity_id = l_supply_header_id
AND wlc.lpn_id = l_mol_lpn_id
AND wlc.inventory_item_id = l_inventory_item_id
AND wlc.organization_id = l_organization_id
AND wlc.demand_source_line = ool.line_id (+);
l_existing_rsvs_tb.DELETE;
l_reserved_wdd_lines_tb.DELETE;
-- 2.4 - Crossdock detail the reservation and update the demand and supply line records.
IF (l_debug = 1) THEN
print_debug('2.4 - Crossdock detail the relevant records: RSV, WDD, MOL');
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_line_detail_id_tb.DELETE;
l_dock_start_time_tb.DELETE;
l_dock_mean_time_tb.DELETE;
l_dock_end_time_tb.DELETE;
l_expected_time_tb.DELETE;
l_quantity_tb.DELETE;
l_uom_code_tb.DELETE;
l_secondary_quantity_tb.DELETE;
l_secondary_uom_code_tb.DELETE;
l_project_id_tb.DELETE;
l_task_id_tb.DELETE;
l_wip_supply_type_tb.DELETE;
-- so remove this line from the local tables prior to insertion.
IF (l_debug = 1) THEN
print_debug('3.2 - Current demand line is invalid for crossdocking so remove it');
l_header_id_tb.DELETE(j);
l_line_id_tb.DELETE(j);
l_line_detail_id_tb.DELETE(j);
l_quantity_tb.DELETE(j);
l_uom_code_tb.DELETE(j);
l_secondary_quantity_tb.DELETE(j);
l_secondary_uom_code_tb.DELETE(j);
l_project_id_tb.DELETE(j);
l_task_id_tb.DELETE(j);
l_dock_start_time_tb.DELETE(j);
l_dock_mean_time_tb.DELETE(j);
l_dock_end_time_tb.DELETE(j);
l_expected_time_tb.DELETE(j);
l_wip_supply_type_tb.DELETE(j);
l_header_id_tb.DELETE(j);
l_line_id_tb.DELETE(j);
l_line_detail_id_tb.DELETE(j);
l_quantity_tb.DELETE(j);
l_uom_code_tb.DELETE(j);
l_secondary_quantity_tb.DELETE(j);
l_secondary_uom_code_tb.DELETE(j);
l_project_id_tb.DELETE(j);
l_task_id_tb.DELETE(j);
l_dock_start_time_tb.DELETE(j);
l_dock_mean_time_tb.DELETE(j);
l_dock_end_time_tb.DELETE(j);
l_expected_time_tb.DELETE(j);
l_wip_supply_type_tb.DELETE(j);
-- can ONLY crossdock to. If that is the case, do not insert any other demand lines
-- into the xdock pegging temp table. Only do this check if the demand line has not
-- already been removed above for not lying within the crossdock window or being part
-- of a partial WIP xdock.
IF (l_header_id_tb.EXISTS(j) AND
l_wip_entity_type IS NOT NULL AND l_wip_demand_header_id IS NOT NULL) THEN
IF (l_demand_type_id <> l_wip_demand_type_id OR
l_header_id_tb(j) <> l_wip_demand_header_id OR
l_line_id_tb(j) <> l_wip_demand_line_id) THEN
-- Demand is not valid for crossdocking due to WIP specified OE demand restriction.
-- So remove this line from the local tables prior to insertion.
IF (l_debug = 1) THEN
print_debug('3.2 - Current demand line does not match WIP OE demand so remove it');
l_header_id_tb.DELETE(j);
l_line_id_tb.DELETE(j);
l_line_detail_id_tb.DELETE(j);
l_quantity_tb.DELETE(j);
l_uom_code_tb.DELETE(j);
l_secondary_quantity_tb.DELETE(j);
l_secondary_uom_code_tb.DELETE(j);
l_project_id_tb.DELETE(j);
l_task_id_tb.DELETE(j);
l_dock_start_time_tb.DELETE(j);
l_dock_mean_time_tb.DELETE(j);
l_dock_end_time_tb.DELETE(j);
l_expected_time_tb.DELETE(j);
l_wip_supply_type_tb.DELETE(j);
print_debug('3.3 - Insert ' || l_header_id_tb.COUNT ||
' crossdockable demand lines into the global temp table');
INSERT INTO wms_xdock_pegging_gtmp
(inventory_item_id,
xdock_source_code,
source_type_id,
source_header_id,
source_line_id,
source_line_detail_id,
dock_start_time,
dock_mean_time,
dock_end_time,
expected_time,
quantity,
uom_code,
secondary_quantity,
secondary_uom_code,
project_id,
task_id,
wip_supply_type
)
VALUES
(l_inventory_item_id,
l_demand_src_code,
l_demand_type_id,
l_header_id_tb(k),
l_line_id_tb(k),
l_line_detail_id_tb(k),
l_dock_start_time_tb(k),
l_dock_mean_time_tb(k),
l_dock_end_time_tb(k),
l_expected_time_tb(k),
l_quantity_tb(k),
l_uom_code_tb(k),
l_secondary_quantity_tb(k),
l_secondary_uom_code_tb(k),
l_project_id_tb(k),
l_task_id_tb(k),
l_wip_supply_type_tb(k)
);
print_debug('3.3 - Error inserting available demand lines into temp table');
-- If an exception occurs while inserting demand line records,
-- rollback the changes and stop crossdock processing.
ROLLBACK TO Demand_Lines_sp;
print_debug('3.3 - Successfully inserted ' || l_header_id_tb.COUNT ||
' crossdockable demand lines into temp table');
l_header_id_tb.DELETE;
l_line_id_tb.DELETE;
l_line_detail_id_tb.DELETE;
l_dock_start_time_tb.DELETE;
l_dock_mean_time_tb.DELETE;
l_dock_end_time_tb.DELETE;
l_expected_time_tb.DELETE;
l_quantity_tb.DELETE;
l_uom_code_tb.DELETE;
l_secondary_quantity_tb.DELETE;
l_secondary_uom_code_tb.DELETE;
l_project_id_tb.DELETE;
l_task_id_tb.DELETE;
l_wip_supply_type_tb.DELETE;
l_shopping_basket_tb.DELETE;
-- just delete the demand line from the shopping basket table. Shopping basket
-- table can therefore be a sparsely populated table after running custom logic.
l_shopping_basket_tb.DELETE(i);
-- just delete the demand line from the shopping basket table. Shopping basket
-- table can therefore be a sparsely populated table after running custom logic.
l_shopping_basket_tb.DELETE(i);
l_sorted_order_tb.DELETE;
print_debug('3.5 - Custom API is NOT implemented even though Custom Goal is selected!');
l_indices_used_tb.DELETE;
l_shopping_basket_temp_tb.DELETE;
l_shopping_basket_temp_tb.DELETE;
l_shopping_basket_temp_tb.DELETE;
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
l_detail_info_tab.DELETE(l_xdocked_wdd_index);
print_debug('5.1 - Call the Create_Update_Delivery_Detail API for ' ||
l_detail_info_tab.COUNT || ' crossdocked WDD records');
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec
);
print_debug('5.1 - Error returned from Create_Update_Delivery_Detail API: '
|| x_return_status);
print_debug('5.1 - Successfully updated the crossdocked WDD records');
DELETE wms_xdock_pegging_gtmp;
SELECT wdd.organization_id AS organization_id,
wts.stop_id AS trip_stop_id,
NVL(wt.carrier_id,
NVL(NVL(wnd.carrier_id, wcs_wnd.carrier_id),
NVL(NVL(wdd.carrier_id, wcs_wdd.carrier_id),
NVL(wc_ool.carrier_id, wcs_ool.carrier_id)))) AS carrier_id,
NVL(wts.planned_departure_date,
NVL(wdd.date_scheduled,
NVL(ool.schedule_ship_date, ool.promise_date))) AS expected_ship_date
FROM wsh_delivery_details wdd, oe_order_lines_all ool,
wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
wsh_carrier_services wcs_wdd, wsh_carrier_services wcs_ool, wsh_carriers wc_ool
WHERE wdd.delivery_detail_id = p_source_line_detail_id
AND ool.line_id = p_source_line_id
AND wdd.source_line_id = ool.line_id
AND inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) = p_source_header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND wda.delivery_id = wnd.delivery_id (+)
AND wnd.delivery_id = wdl.delivery_id (+)
AND (wdl.sequence_number IS NULL OR
wdl.sequence_number = (SELECT MIN(sequence_number)
FROM wsh_delivery_legs wdl_first_leg
WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
AND wdl.pick_up_stop_id = wts.stop_id (+)
AND wts.trip_id = wt.trip_id (+)
AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
AND wdd.ship_method_code = wcs_wdd.ship_method_code (+)
AND ool.shipping_method_code = wcs_ool.ship_method_code (+)
AND ool.freight_carrier_code = wc_ool.freight_code (+);
SELECT poll.ship_to_organization_id AS organization_id,
wts.stop_id AS trip_stop_id,
NVL(wt.carrier_id,
NVL(NVL(wnd.carrier_id, wcs_wnd.carrier_id),
NVL(NVL(wdd.carrier_id, wcs_wdd.carrier_id),
wc_poll.carrier_id))) AS carrier_id,
NVL(wts.planned_arrival_date,
NVL(poll.promised_date, poll.need_by_date)) AS expected_receipt_date
FROM po_line_locations_all poll, wsh_delivery_details wdd,
wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
wsh_carrier_services wcs_wdd, wsh_carriers wc_poll
WHERE poll.po_header_id = p_source_header_id
AND poll.line_location_id = p_source_line_id
AND poll.po_header_id = wdd.source_header_id (+)
AND poll.po_line_id = wdd.source_line_id (+)
AND poll.line_location_id = wdd.po_shipment_line_id (+)
AND 'PO' = wdd.source_code (+)
AND 'I' = wdd.line_direction (+)
AND 'L' <> wdd.released_status (+)
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND wda.delivery_id = wnd.delivery_id (+)
AND wnd.delivery_id = wdl.delivery_id (+)
AND (wdl.sequence_number IS NULL OR
wdl.sequence_number = (SELECT MIN(sequence_number)
FROM wsh_delivery_legs wdl_first_leg
WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
AND wdl.drop_off_stop_id = wts.stop_id (+)
AND wts.trip_id = wt.trip_id (+)
AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
AND wdd.ship_method_code = wcs_wdd.ship_method_code (+)
AND poll.ship_via_lookup_code = wc_poll.freight_code (+)
ORDER BY expected_receipt_date ASC;
SELECT rsl.to_organization_id AS organization_id,
wts.stop_id AS trip_stop_id,
NVL(wt.carrier_id,
NVL(NVL(wnd.carrier_id, wcs_wnd.carrier_id),
NVL(NVL(wdd.carrier_id, wcs_wdd.carrier_id),
NVL(wc_rsh.carrier_id,
wc_poll.carrier_id)))) AS carrier_id,
NVL(wts.planned_arrival_date,
NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
NVL(poll.promised_date, poll.need_by_date))) AS expected_receipt_date
FROM po_line_locations_all poll, wsh_delivery_details wdd,
rcv_shipment_headers rsh, rcv_shipment_lines rsl,
wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
wsh_carrier_services wcs_wdd, wsh_carriers wc_poll, wsh_carriers wc_rsh
WHERE rsl.po_header_id = p_source_header_id
AND rsl.po_line_location_id = p_source_line_id
AND rsl.shipment_line_id = p_source_line_detail_id
AND poll.po_header_id = rsl.po_header_id
AND poll.po_line_id = rsl.po_line_id
AND poll.line_location_id = rsl.po_line_location_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_num IS NOT NULL
AND rsh.receipt_source_code = 'VENDOR'
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.po_header_id = wdd.source_header_id (+)
AND rsl.po_line_id = wdd.source_line_id (+)
AND rsl.po_line_location_id = wdd.po_shipment_line_id (+)
AND rsl.shipment_line_id = wdd.rcv_shipment_line_id (+)
AND 'PO' = wdd.source_code (+)
AND 'I' = wdd.line_direction (+)
AND 'L' <> wdd.released_status (+)
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND wda.delivery_id = wnd.delivery_id (+)
AND wnd.delivery_id = wdl.delivery_id (+)
AND (wdl.sequence_number IS NULL OR
wdl.sequence_number = (SELECT MIN(sequence_number)
FROM wsh_delivery_legs wdl_first_leg
WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
AND wdl.drop_off_stop_id = wts.stop_id (+)
AND wts.trip_id = wt.trip_id (+)
AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
AND wdd.ship_method_code = wcs_wdd.ship_method_code (+)
AND poll.ship_via_lookup_code = wc_poll.freight_code (+)
AND rsh.freight_carrier_code = wc_rsh.freight_code (+);
SELECT prl.destination_organization_id AS organization_id,
wts.stop_id AS trip_stop_id,
NVL(wt.carrier_id,
NVL(NVL(wnd.carrier_id, wcs_wnd.carrier_id),
NVL(NVL(wdd.carrier_id, wcs_wdd.carrier_id),
NVL(NVL(wc_ool.carrier_id, wcs_ool.carrier_id),
NVL(wc_rsh.carrier_id,
wcs_prl.carrier_id))))) AS carrier_id,
NVL(wts.planned_arrival_date,
NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
prl.need_by_date)) AS expected_receipt_date
FROM po_requisition_lines_all prl, rcv_shipment_lines rsl, rcv_shipment_headers rsh,
oe_order_lines_all ool, wsh_delivery_details wdd,
wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
wsh_carrier_services wcs_wdd, wsh_carrier_services wcs_ool,
wsh_carriers wc_ool, wsh_carriers wc_rsh, wsh_carrier_services wcs_prl
WHERE prl.requisition_header_id = p_source_header_id
AND prl.requisition_line_id = p_source_line_id
AND prl.source_type_code = 'INVENTORY'
AND NVL(prl.cancel_flag, 'N') = 'N'
AND prl.requisition_line_id = rsl.requisition_line_id (+)
AND rsl.shipment_header_id = rsh.shipment_header_id (+)
AND 10 = ool.order_source_id (+) -- Internal Order source type
AND prl.requisition_header_id = ool.source_document_id (+)
AND prl.requisition_line_id = ool.source_document_line_id (+)
AND prl.item_id = ool.inventory_item_id (+)
AND ool.header_id = wdd.source_header_id (+)
AND ool.line_id = wdd.source_line_id (+)
AND 'OE' = wdd.source_code (+)
AND 'IO' = wdd.line_direction (+)
AND 'L' <> wdd.released_status (+)
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND wda.delivery_id = wnd.delivery_id (+)
AND wnd.delivery_id = wdl.delivery_id (+)
AND (wdl.sequence_number IS NULL OR
wdl.sequence_number = (SELECT MIN(sequence_number)
FROM wsh_delivery_legs wdl_first_leg
WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
AND wdl.drop_off_stop_id = wts.stop_id (+)
AND wts.trip_id = wt.trip_id (+)
AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
AND wdd.ship_method_code = wcs_wdd.ship_method_code (+)
AND ool.shipping_method_code = wcs_ool.ship_method_code (+)
AND ool.freight_carrier_code = wc_ool.freight_code (+)
AND rsh.freight_carrier_code = wc_rsh.freight_code (+)
AND prl.ship_method = wcs_prl.ship_method_code (+)
ORDER BY expected_receipt_date ASC;
SELECT rsl.to_organization_id AS organization_id,
NULL AS trip_stop_id,
wc_rsh.carrier_id as carrier_id,
NVL(rsh.expected_receipt_date,
NVL(rsh.shipped_date + NVL(mism.intransit_time, 0),
rsh.shipped_date)) AS expected_receipt_date
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh,
wsh_carriers wc_rsh, mtl_interorg_ship_methods mism
WHERE rsl.shipment_header_id = p_source_header_id
AND rsl.shipment_line_id = p_source_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_num IS NOT NULL
AND rsh.receipt_source_code = 'INVENTORY'
AND rsh.freight_carrier_code = wc_rsh.freight_code (+)
AND rsl.from_organization_id = mism.from_organization_id (+)
AND rsl.to_organization_id = mism.to_organization_id (+)
AND 1 = mism.default_flag (+);
-- to the current best time, update the best dock appointment variables to
-- point to the current dock appointment.
IF (abs(l_current_dock_appt_time - l_expected_date) <
abs(l_dock_appt_time - l_expected_date)) THEN
l_dock_start_time := l_dock_appt_list(i).start_time;
SELECT wnd.organization_id AS organization_id,
wts.stop_id AS trip_stop_id,
MIN(NVL(wt.carrier_id,
NVL(NVL(wnd.carrier_id, wcs_wnd.carrier_id),
NVL(NVL(wdd.carrier_id, wcs_wdd.carrier_id),
NVL(wc_ool.carrier_id, wcs_ool.carrier_id))))) AS carrier_id,
MIN(NVL(wts.planned_departure_date,
NVL(wdd.date_scheduled,
NVL(ool.schedule_ship_date, ool.promise_date)))) AS min_expected_ship_date,
MAX(NVL(wts.planned_departure_date,
NVL(wdd.date_scheduled,
NVL(ool.schedule_ship_date, ool.promise_date)))) AS max_expected_ship_date
FROM wsh_new_deliveries wnd, wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt, oe_order_lines_all ool,
wsh_carrier_services wcs_wnd, wsh_carrier_services wcs_wdd,
wsh_carrier_services wcs_ool, wsh_carriers wc_ool
WHERE wnd.delivery_id = p_delivery_id
AND wnd.shipment_direction = 'O'
AND wnd.delivery_id = wda.delivery_id (+)
AND wda.delivery_detail_id = wdd.delivery_detail_id (+)
AND wdd.source_line_id = ool.line_id (+)
AND wnd.delivery_id = wdl.delivery_id (+)
AND (wdl.sequence_number IS NULL OR
wdl.sequence_number = (SELECT MIN(sequence_number)
FROM wsh_delivery_legs wdl_first_leg
WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
AND wdl.pick_up_stop_id = wts.stop_id (+)
AND wts.trip_id = wt.trip_id (+)
AND wnd.ship_method_code = wcs_wnd.ship_method_code (+)
AND wdd.ship_method_code = wcs_wdd.ship_method_code (+)
AND ool.shipping_method_code = wcs_ool.ship_method_code (+)
AND ool.freight_carrier_code = wc_ool.freight_code (+)
GROUP BY wnd.organization_id, wnd.delivery_id, wts.stop_id;
-- to the current best time, update the best dock appointment variables to
-- point to the current dock appointment.
IF (abs(l_current_dock_appt_time - l_max_expected_date) <
abs(l_dock_appt_time - l_max_expected_date)) THEN
l_dock_appointment_id := l_dock_appt_list(i).dock_appointment_id;