[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_wdd VARCHAR2(1) := 'N';
SELECT SUM(NVL(L.ORDERED_QUANTITY, 0) * NVL(L.UNIT_SELLING_PRICE, 0))
INTO l_order_value
FROM OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES_ALL L
WHERE H.HEADER_ID = p_header_id
and l.line_id = p_line_id
AND L.HEADER_ID = H.HEADER_ID;
procedure delete_wave_header(p_wave_header_id in number)
is
begin
delete from wms_wp_wave_headers_vl
where wave_header_id = p_wave_header_id;
end delete_wave_header;
wms_wave_planning_pvt.insert_wave_record(v_WAVE_HEADER_ID);
select order_type_id,
from_order_header_id,
backorders_flag,
include_planned_lines,
customer_id,
inventory_item_id,
ship_priority,
ship_method_code,
SHIP_TO_LOCATION_ID,
project_id,
task_id,
delivery_id,
trip_id,
trip_stop_id,
pick_seq_rule_id,
pick_grouping_rule_id,
scheduled_days,
scheduled_hrs,
dock_appointment_days,
dock_appointment_hours,
customer_class_id,
planning_criteria_id,
initiate_wave_planning,
RELEASE_IMMEDIATELY,
carrier_id,
category_id
into p_order_type_id,
p_order_header_id,
p_backorders_flag,
p_include_planned_lines,
p_customer_id,
p_inventory_item_id,
p_shipment_priority_code,
p_ship_method_code,
p_ship_to_loc_id,
p_project_id,
p_task_id,
p_delivery_id,
p_trip_id,
p_trip_stop_id,
p_pick_seq_rule_id,
p_pick_grouping_rule_id,
p_scheduled_days,
p_scheduled_hrs,
p_dock_days,
p_dock_hours,
p_customer_class_id,
p_planning_criteria_id,
p_plan_wave,
p_release_wave,
p_carrier_id,
p_category_id
from wms_wp_wave_headers_vl
where wave_header_id = v_wave_header_id;
update wms_wp_wave_headers_vl
set request_id = fnd_global.conc_request_id
where wave_header_id = v_wave_header_id;
delete_wave_header(v_wave_header_id);
procedure insert_wave_record(p_wave_header_id in OUT NOCOPY number) is
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
select WMS_WP_WAVE_HEADERS_S.NEXTVAL into l_wave_header_id from dual;
INSERT INTO wms_wp_wave_headers_vl
(wave_header_id,
WAVE_NAME,
WAVE_DESCRIPTION,
start_time, -- start time changes
WAVE_SOURCE,
WAVE_STATUS,
TYPE_ID,
BATCH_ID,
SHIP_TO_LOCATION_ID,
CUSTOMER_CLASS_ID,
pull_replenishment_flag,
INITIATE_WAVE_PLANNING,
RELEASE_IMMEDIATELY,
TABLE_NAME,
ADVANCED_CRITERIA,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ORGANIZATION_ID,
PICK_SEQ_RULE_ID,
PICK_GROUPING_RULE_ID,
TRIP_ID,
TRIP_STOP_ID,
SHIP_METHOD_CODE,
SHIPMENT_PRIORITY_CODE,
CARRIER_ID,
DELIVERY_ID,
FROM_ORDER_HEADER_ID,
ORDER_TYPE_ID,
CUSTOMER_ID,
TASK_ID,
PROJECT_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
INVENTORY_ITEM_ID,
BACKORDERS_FLAG,
INCLUDE_PLANNED_LINES,
TASK_PLANNING_FLAG,
APPEND_DELIVERIES,
AUTO_CREATE_DELIVERY,
AUTO_CREATE_DELIVERY_CRITERIA,
TASK_PRIORITY,
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
DEFAULT_ALLOCATION_METHOD,
WAVE_FIRMED_FLAG,
WAVE_COMPLETION_TIME,
ORDER_NAME,
CUSTOMER,
ORDER_TYPE,
CUSTOMER_CLASS,
SHIP_METHOD,
CARRIER,
SHIP_PRIORITY,
DELIVERY,
TRIP,
TRIP_STOP,
ITEM,
ITEM_CATEGORY,
PROJECT_NAME,
TASK_NAME,
SCHEDULED_DAYS,
SCHEDULED_HRS,
DOCK_APPOINTMENT_DAYS,
DOCK_APPOINTMENT_HOURS,
PICK_SLIP_GROUP,
RELEASE_SEQ_RULE,
STAGING_SUBINVENTORY,
STAGING_LOCATOR,
CROSS_DOCK_CRITERIA,
PLANNING_CRITERIA,
PLANNING_CRITERIA_ID,
pick_subinventory)
SELECT l_wave_header_id,
WAVE_NAME || '-' || l_wave_header_id,
WAVE_DESCRIPTION,
start_time, -- start time changes
WAVE_SOURCE,
WAVE_STATUS, --Need to Confirm??????
'W',
BATCH_ID,
SHIP_TO_LOCATION_ID,
CUSTOMER_CLASS_ID,
pull_replenishment_flag,
INITIATE_WAVE_PLANNING,
RELEASE_IMMEDIATELY,
TABLE_NAME,
ADVANCED_CRITERIA,
CREATED_BY,
CREATION_DATE,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
ORGANIZATION_ID,
PICK_SEQ_RULE_ID,
PICK_GROUPING_RULE_ID,
TRIP_ID,
TRIP_STOP_ID,
SHIP_METHOD_CODE,
SHIPMENT_PRIORITY_CODE,
CARRIER_ID,
DELIVERY_ID,
FROM_ORDER_HEADER_ID,
ORDER_TYPE_ID,
CUSTOMER_ID,
TASK_ID,
PROJECT_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
INVENTORY_ITEM_ID,
BACKORDERS_FLAG,
INCLUDE_PLANNED_LINES,
TASK_PLANNING_FLAG,
APPEND_DELIVERIES,
AUTO_CREATE_DELIVERY,
AUTO_CREATE_DELIVERY_CRITERIA,
TASK_PRIORITY,
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
DEFAULT_ALLOCATION_METHOD,
WAVE_FIRMED_FLAG,
WAVE_COMPLETION_TIME,
ORDER_NAME,
CUSTOMER,
ORDER_TYPE,
CUSTOMER_CLASS,
SHIP_METHOD,
CARRIER,
SHIP_PRIORITY,
DELIVERY,
TRIP,
TRIP_STOP,
ITEM,
ITEM_CATEGORY,
PROJECT_NAME,
TASK_NAME,
SCHEDULED_DAYS,
SCHEDULED_HRS,
DOCK_APPOINTMENT_DAYS,
DOCK_APPOINTMENT_HOURS,
PICK_SLIP_GROUP,
RELEASE_SEQ_RULE,
STAGING_SUBINVENTORY,
STAGING_LOCATOR,
CROSS_DOCK_CRITERIA,
PLANNING_CRITERIA,
PLANNING_CRITERIA_ID,
pick_subinventory
FROM wms_wp_wave_headers_vl
WHERE wave_header_id = p_wave_header_id;
INSERT INTO wms_wp_Advanced_Criteria
(RULE_ID,
RULE_WAVE_HEADER_ID,
SEQUENCE_NUMBER,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LOGICAL_OPERATOR_CODE,
LOGICAL_OPERATOR_MEANING,
BRACKET_OPEN,
OBJECT_ID,
OBJECT_NAME,
OBJECT_DESCRIPTION,
PARAMETER_ID,
PARAMETER_NAME,
PARAMETER_DESCRIPTION,
OPERATOR_CODE,
OPERATOR_MEANING,
OPERATOR_DESCRIPTION,
OPERAND_VALUE,
OPERAND_CHAR,
BRACKET_CLOSE)
SELECT wms_wp_advanced_criteria_s.NEXTVAL,
l_wave_header_id,
SEQUENCE_NUMBER,
fnd_global.user_id,
sysdate,
CREATED_BY,
CREATION_DATE,
fnd_global.login_id,
LOGICAL_OPERATOR_CODE,
LOGICAL_OPERATOR_MEANING,
BRACKET_OPEN,
OBJECT_ID,
OBJECT_NAME,
OBJECT_DESCRIPTION,
PARAMETER_ID,
PARAMETER_NAME,
PARAMETER_DESCRIPTION,
OPERATOR_CODE,
OPERATOR_MEANING,
OPERATOR_DESCRIPTION,
OPERAND_VALUE,
OPERAND_CHAR,
BRACKET_CLOSE
FROM wms_wp_Advanced_Criteria
WHERE rule_wave_header_id = p_wave_header_id;
select WMS_WP_WAVE_HEADERS_S.CURRVAL into p_wave_header_id from dual;
select WMS_WP_WAVE_HEADERS_S.CURRVAL into p_wave_header_id from dual;
print_debug('Error in Insert Wave Record: ' || SQLCODE || ' : ' ||
SQLERRM,
l_debug);
end insert_wave_record;
SELECT wlp.resource_type resource_name,
wlp.source_subinventory,
wlp.destination_subinventory,
wlp.pick_uom pick_uom,
wlp.transaction_time,
wlp.travel_time,
wlp.processing_overhead_duration,
wlp.category_id,
Decode(plan_type, 'A', -1, 'R', wlp.operation_plan_id) operation_plan_id,
br.resource_type resource_type
FROM wms_wp_labor_planning wlp,
bom_department_resources bdr,
bom_Resources br,
wms_wp_planning_Criteria_vl wpl
WHERE wlp.planning_criteria_id = p_plan_id
AND wpl.planning_criteria_id = p_plan_id
AND br.resource_code = wlp.resource_type
AND br.resource_id = bdr.resource_id
AND bdr.department_id = wpl.department_id
ORDER BY br.resource_type DESC;
SELECT DISTINCT resource_type
FROM wms_wp_labor_planning
WHERE planning_criteria_id = p_plan_id;
select department_id
into l_dept_id
from wms_wp_planning_criteria_vl
where planning_criteria_id = l_planning_criteria_id;
when it calls for inventory movements only we need to update resource capacity table.
After doing labor resource planning for inventory movements, labor planning api will call this again for xdock movements;
SELECT 'Y'
INTO l_std_oprn_id
FROM dual
WHERE x_labor_time_tbl(task)
.standard_operation_id IN
(SELECT standard_operation_id
FROM bom_std_op_resources_v
WHERE resource_code = x_labor_setup_tbl(setup)
.resource_name);
SELECT 'Y'
INTO l_item_cat_id
FROM dual
WHERE x_labor_setup_tbl(setup)
.item_category_id is null
or (x_labor_setup_tbl(setup)
.item_category_id IN
(SELECT category_id
FROM mtl_category_set_valid_cats_v a
WHERE a.category_id IN
(SELECT DISTINCT b.category_id
FROM mtl_item_categories b
WHERE a.category_id = b.category_id
and b.inventory_item_id =
x_labor_time_tbl(TASK)
.inventory_item_id
AND b.organization_id = l_organization_id)));
select nvl(utilization, 100) / 100,
nvl(efficiency, 100) / 100
into l_utilization, l_efficiency
from bom_department_resources_v bdrv
where department_id = l_dept_id
and resource_code = x_resource_capacity_tbl(cap_det)
.resource_name;
SELECT DISTINCT mmtt1.transaction_temp_id,
mmtt1.standard_operation_id,
mmtt1.parent_line_id,
mmtt1.transaction_quantity,
wz1.zone_name from_zone,
Decode(mmtt1.transfer_to_location,
NULL,
NULL,
wz2.zone_name) to_zone,
mmtt1.transaction_uom,
mmtt1.operation_plan_id,
mmtt1.inventory_item_id
FROM mtl_material_transactions_temp mmtt1,
mtl_material_transactions_temp mmtt2,
wms_zone_locators wzl1,
wms_zone_locators wzl2,
wms_zones_vl wz1,
wms_zones_vl wz2
WHERE mmtt2.move_order_header_id = p_move_order_hdr_id
and mmtt1.locator_id = wzl1.inventory_location_id
and (mmtt1.transfer_to_location = wzl2.inventory_location_id OR
mmtt1.transfer_to_location IS NULL)
and mmtt1.organization_id = wzl1.organization_id
and mmtt1.organization_id = wzl2.organization_id
and wz1.zone_id = wzl1.zone_id
and wz2.zone_id = wzl2.zone_id
and wz1.zone_type = 'L'
and wz2.zone_type = 'L'
AND (mmtt1.transaction_temp_id = mmtt2.transaction_temp_id OR
mmtt1.transaction_temp_id = mmtt2.parent_line_id)
AND (mmtt2.parent_line_id IS NULL OR
mmtt1.transaction_temp_id = mmtt1.parent_line_id);
select distinct transaction_temp_id,
wz1.zone_name from_zone,
wz2.zone_name to_zone,
transaction_uom,
operation_plan_id,
inventory_item_id,
dropping_order,
transaction_quantity
from mtl_material_transactions_temp mmtt,
MTL_SECONDARY_INVENTORIES MSI,
wms_zone_locators wzl1,
wms_zone_locators wzl2,
wms_zones_vl wz1,
wms_zones_vl wz2
where move_order_header_id = p_move_order_hdr_id
--AND mmtt.transfer_subinventory =msi.SECONDARY_INVENTORY_NAME
AND mmtt.organization_id = msi.organization_id
and mmtt.locator_id = wzl1.inventory_location_id
and mmtt.transfer_to_location = wzl2.inventory_location_id
and mmtt.organization_id = wzl1.organization_id
and mmtt.organization_id = wzl2.organization_id
and wz1.zone_id = wzl1.zone_id
and wz2.zone_id = wzl2.zone_id
and wz1.zone_type = 'L'
and wz2.zone_type = 'L'
and wzl2.subinventory_code = msi.SECONDARY_INVENTORY_NAME
and parent_line_id = p_parent_line_id
order by dropping_order;
SELECT DISTINCT mmtt1.transaction_temp_id,
mmtt1.standard_operation_id,
mmtt1.parent_line_id,
mmtt1.transaction_quantity,
mmtt1.subinventory_code,
mmtt1.transfer_subinventory,
mmtt1.transaction_uom,
mmtt1.operation_plan_id,
mmtt1.inventory_item_id
FROM mtl_material_transactions_temp mmtt1,
mtl_material_transactions_temp mmtt2
WHERE mmtt2.move_order_header_id = p_move_order_hdr_id
AND (mmtt1.transaction_temp_id = mmtt2.transaction_temp_id OR
mmtt1.transaction_temp_id = mmtt2.parent_line_id)
AND (mmtt2.parent_line_id IS NULL OR
mmtt1.transaction_temp_id = mmtt1.parent_line_id);
select distinct transaction_temp_id,
subinventory_code,
transfer_subinventory,
transaction_uom,
operation_plan_id,
inventory_item_id,
dropping_order,
transaction_quantity
from mtl_material_transactions_temp mmtt,
MTL_SECONDARY_INVENTORIES MSI
where move_order_header_id = p_move_order_hdr_id
AND mmtt.transfer_subinventory = msi.SECONDARY_INVENTORY_NAME
AND mmtt.organization_id = msi.organization_id
and parent_line_id = p_parent_line_id
order by dropping_order;
select labor_setup_mode, allocation_method, destination_subinventory
into l_labor_setup_mode,
l_allocation_method,
v_destination_subinventory
from wms_wp_planning_criteria_vl
where PLANNING_CRITERIA_ID = p_planning_criteria_id;
SELECT staging_subinventory
INTO v_destination_subinventory
FROM wms_wp_wave_headers_vl
WHERE wave_header_id = p_wave_header_id;
SELECT 'Not Applicable',v_destination_subinventory,
Min(wwl.crossdock_uom),
Sum(wwl.crossdock_quantity),
-1, wdd.inventory_item_id,
-1 bulk collect into
c_labor_time_tbl
FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd...FIXED
WHERE wwl.wave_header_id = p_wave_header_id
and wwl.organization_id = wdd.organization_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
GROUP BY wdd.inventory_item_id
ORDER BY wdd.inventory_item_id;
SELECT distinct zone_name into v_zone_name from wms_zones_vl wz, wms_zone_locators wzl WHERE
wz.zone_id=wzl.zone_id
and wz.zone_type ='L'
and wzl.subinventory_code=v_destination_subinventory;
SELECT 'Not Applicable', v_zone_name,
Min(wwl.crossdock_uom),
Sum(wwl.crossdock_quantity),
-1, wdd.inventory_item_id,
-1 bulk collect into
c_labor_time_tbl
FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd...FIXED
WHERE wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
GROUP BY wdd.inventory_item_id
ORDER BY wdd.inventory_item_id;
DELETE FROM wms_wp_labor_Statistics
WHERE wave_header_id = p_wave_header_id;
insert into wms_wp_labor_statistics
(wave_header_id,
resource_name,
planned_wave_load,
total_capacity,
actual_workload,
available_capacity,
NUMBER_OF_ACTUAL_TASKS,
NUMBER_OF_PLANNED_TASKS)
values
(p_wave_header_id,
x_resource_capacity_tbl(i).resource_name,
x_resource_capacity_tbl(i).planned_load,
x_resource_capacity_tbl(i).total_Capacity,
x_resource_capacity_tbl(i).current_load,
x_resource_capacity_tbl(i).available_capacity,
x_resource_capacity_tbl(i).actual_tasks,
x_resource_capacity_tbl(i).planned_tasks);
select wdd.source_header_number,
wdd.source_line_id,
wdd.customer_id,
wdd.source_header_id,
wwl.delivery_detail_id,
wdd.organization_id,
wdd.inventory_item_id,
(wdd.requested_quantity - nvl(wwr.allocated_quantity, 0)) requested_quantity,
wdd.requested_quantity_uom,
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) demand_source_header_id
from wms_wp_wave_headers_vl wwh,
wms_wp_wave_lines wwl, --11775489 get from wdd... FIXED
wms_wp_planning_criteria_vl wwp,
wsh_delivery_details wdd,
wms_wp_rules_simulation wwr
where wwh.wave_header_id = p_wave_header_id
and wwp.planning_criteria_id = p_planning_criteria_id
and wwh.planning_criteria_id = wwp.planning_criteria_id
and wwh.wave_header_id = wwl.wave_header_id
and wwr.delivery_detail_id = wwl.delivery_detail_id
and wwr.wave_header_id=wwl.wave_header_id
and wwl.delivery_detail_id = wdd.delivery_Detail_id
and wdd.organization_id = wwl.organization_id
and wdd.released_status in ('R','B'); --- For Hot Order Changes
select wdd.delivery_detail_id,
wdd.inventory_item_id,
subinventory,
locator_id,
lot_number,
revision,
wdd.source_header_id,
wwr.requested_quantity - Nvl(wwr.crossdocked_quantity, 0),
wdd.source_line_id,
wdd.REQUESTED_QUANTITY_UOM,
SRC_REQUESTED_QUANTITY,
SRC_REQUESTED_QUANTITY_UOM,
wwr.requested_quantity2 - Nvl(wwr.crossdocked_quantity2, 0),
wdd.requested_quantity_uom2,
ship_set_id,
SHIP_MODEL_COMPLETE_FLAG,
top_model_line_id,
date_scheduled,
project_id,
task_id,
unit_NUMBER,
preferred_grade,
NULL,
NULL
FROM wsh_delivery_details wdd,
wms_wp_wave_lines wwl, --11775489 added org_id join...FIXED
wms_wp_rules_simulation wwr
WHERE wdd.released_status in ('R', 'B')
AND wwr.delivery_detail_id = wdd.delivery_detail_id
and wwr.wave_header_id=wwl.wave_header_id
and wdd.delivery_detail_id = wwl.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wwl.wave_header_id = p_wave_id
and wwr.wave_header_id=p_wave_id
ORDER BY wave_line_id;
SELECT enforce_ship_set_and_smc,
default_stage_subinventory,
default_stage_locator_id
INTO g_enforce_ship_set_and_smc,
g_default_stage_subinventory,
g_default_stage_locator_id
FROM WSH_SHIPPING_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT MTL_TXN_REQUEST_HEADERS_S.NEXTVAL
into l_request_number
FROM SYS.DUAL;
INSERT INTO mtl_txn_request_headers
(HEADER_ID,
REQUEST_NUMBER,
TRANSACTION_TYPE_ID,
MOVE_ORDER_TYPE,
ORGANIZATION_ID,
DESCRIPTION,
DATE_REQUIRED,
FROM_SUBINVENTORY_CODE,
TO_SUBINVENTORY_CODE,
TO_ACCOUNT_ID,
HEADER_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,
GROUPING_RULE_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
SHIP_TO_LOCATION_ID,
FREIGHT_CODE,
SHIPMENT_METHOD,
AUTO_RECEIPT_FLAG,
REFERENCE_ID,
REFERENCE_DETAIL_ID,
ASSIGNMENT_ID)
VALUES
(l_mo_header_id,
l_request_number,
NULL,
INV_GLOBALS.G_MOVE_ORDER_PICK_WAVE,
p_organization_id,
NULL,
sysdate,
NULL,
NULL,
NULL,
INV_Globals.G_TO_STATUS_PREAPPROVED,
NULL,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
NULL,
NULL,
NULL,
NULL,
g_PICK_GROUPING_RULE_ID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL);
select WSH_DELIVERY_GROUP_S.nextval
into l_group_match_seq_tbl(i).delivery_group_id
from dual;
SELECT MTL_TXN_REQUEST_LINES_S.NEXTVAL INTO l_seq_val FROM dual; -- Changed
g_mtrl_tbl(i).LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
g_mtrl_tbl(i).LAST_UPDATE_LOGIN := FND_GLOBAL.CONC_LOGIN_ID;
g_mtrl_tbl(i).LAST_UPDATE_DATE := SYSDATE;
g_mtrl_tbl(i).PROGRAM_UPDATE_DATE := null;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items
WHERE organization_id = g_mtrl_tbl(i)
.organization_id
AND inventory_item_id = g_mtrl_tbl(i).inventory_item_id;
SELECT ORDERED_QUANTITY, ORDER_QUANTITY_UOM
into l_ordered_quantity, l_order_quantity_uom
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = l_top_model_line_id;
insert into mtl_txn_request_lines values g_mtrl_tbl (i);
print_debug( l_mtrl_count||' move order lines inserted INTO MTRL', l_debug);
UPDATE wsh_delivery_details
SET move_order_line_id = g_mtrl_tbl(i).line_id
WHERE delivery_detail_id = g_mtrl_tbl(i).TXN_SOURCE_LINE_DETAIL_ID
AND move_order_line_id IS NULL;
print_debug( l_wdd_count||' WDDs updated with move_order_line_Id', l_debug);
print_debug('Could not update all WDD lines', l_debug);
g_update_wdd := 'Y';
lt_last_update_date date_tab;
lt_last_updated_by num_tab;
lt_last_update_login num_tab;
lt_program_update_date date_tab;
SELECT reservation_id ,
requirement_date,
organization_id,
inventory_item_id,
demand_source_type_id,
demand_source_name,
demand_source_header_id,
demand_source_line_id,
demand_source_delivery,
primary_uom_code,
primary_uom_id,
reservation_uom_code ,
reservation_uom_id,
reservation_quantity,
primary_reservation_quantity,
autodetail_group_id,
external_source_code,
external_source_line_id,
supply_source_type_id,
supply_source_header_id,
supply_source_line_id,
supply_source_line_detail,
supply_source_name ,
revision,
subinventory_code,
subinventory_id,
locator_id,
lot_number ,
lot_number_id ,
serial_number,
serial_number_id ,
partial_quantities_allowed ,
auto_detailed ,
pick_slip_number ,
lpn_id ,
last_update_date,
last_updated_by,
creation_date ,
created_by,
last_update_login ,
request_id ,
program_application_id,
program_id ,
program_update_date,
attribute_category,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
ship_ready_flag,
n_column1,
detailed_quantity,
cost_group_id ,
container_lpn_id ,
staged_flag ,
secondary_detailed_quantity ,
secondary_reservation_quantity,
secondary_uom_code,
secondary_uom_id,
crossdock_flag,
crossdock_criteria_id,
demand_source_line_detail,
serial_reservation_quantity,
supply_receipt_date,
demand_ship_date ,
exception_code,
orig_supply_source_type_id,
orig_supply_source_header_id,
orig_supply_source_line_id,
orig_supply_source_line_detail ,
orig_demand_source_type_id,
orig_demand_source_header_id,
orig_demand_source_line_id,
orig_demand_source_line_detail,
project_id,
task_id
FROM mtl_reservations
WHERE demand_source_line_id IN
(SELECT DISTINCT wdd.source_line_id
FROM WMS_WP_WAVE_LINES wwl,
wsh_delivery_details wdd--11775489 get from wdd... FIXED
WHERE wwl.wave_header_id = p_wave_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = wwl.organization_id
);
lt_last_update_date ,
lt_last_updated_by ,
lt_creation_date ,
lt_created_by ,
lt_last_update_login ,
lt_request_id ,
lt_program_application_id ,
lt_program_id ,
lt_program_update_date ,
lt_attribute_category ,
lt_attribute1 ,
lt_attribute2 ,
lt_attribute3 ,
lt_attribute4 ,
lt_attribute5 ,
lt_attribute6 ,
lt_attribute7 ,
lt_attribute8 ,
lt_attribute9 ,
lt_attribute10 ,
lt_attribute11 ,
lt_attribute12 ,
lt_attribute13 ,
lt_attribute14 ,
lt_attribute15 ,
lt_ship_ready_flag ,
lt_n_column1 ,
lt_detailed_quantity ,
lt_cost_group_id ,
lt_container_lpn_id ,
lt_staged_flag ,
lt_secondary_detailed_quantity ,
lt_sec_res_qty ,
lt_secondary_uom_code ,
lt_secondary_uom_id ,
lt_crossdock_flag ,
lt_crossdock_criteria_id ,
lt_demand_source_line_detail,
lt_serial_reservation_quantity ,
lt_supply_receipt_date ,
lt_demand_ship_date ,
lt_exception_code ,
lt_orig_supply_source_type_id ,
lt_orig_sup_src_hdr_id ,
lt_orig_supply_source_line_id ,
lt_orig_sup_src_line_det ,
lt_orig_demand_source_type_id ,
lt_orig_dd_src_hdr_id ,
lt_orig_demand_source_line_id ,
lt_orig_dd_src_line_det ,
lt_project_id ,
lt_task_id;
INSERT
INTO wms_wp_reservations_gtmp
(
reservation_id ,
requirement_date,
organization_id,
inventory_item_id,
demand_source_type_id,
demand_source_name,
demand_source_header_id,
demand_source_line_id,
demand_source_delivery,
primary_uom_code,
primary_uom_id,
reservation_uom_code ,
reservation_uom_id,
reservation_quantity,
primary_reservation_quantity,
autodetail_group_id,
external_source_code,
external_source_line_id,
supply_source_type_id,
supply_source_header_id,
supply_source_line_id,
supply_source_line_detail,
supply_source_name ,
revision,
subinventory_code,
subinventory_id,
locator_id,
lot_number ,
lot_number_id ,
serial_number,
serial_number_id ,
partial_quantities_allowed ,
auto_detailed ,
pick_slip_number ,
lpn_id ,
last_update_date,
last_updated_by,
creation_date ,
created_by,
last_update_login ,
request_id ,
program_application_id,
program_id ,
program_update_date,
attribute_category,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
ship_ready_flag,
n_column1,
detailed_quantity,
cost_group_id ,
container_lpn_id ,
staged_flag ,
secondary_detailed_quantity ,
secondary_reservation_quantity,
secondary_uom_code,
secondary_uom_id,
crossdock_flag,
crossdock_criteria_id,
demand_source_line_detail,
serial_reservation_quantity,
supply_receipt_date,
demand_ship_date ,
exception_code,
orig_supply_source_type_id,
orig_supply_source_header_id,
orig_supply_source_line_id,
orig_supply_source_line_detail ,
orig_demand_source_type_id,
orig_demand_source_header_id,
orig_demand_source_line_id,
orig_demand_source_line_detail,
project_id,
task_id
)
VALUES
(
lt_reservation_id(i) ,
lt_requirement_date(i) ,
lt_organization_id(i) ,
lt_inventory_item_id(i) ,
lt_demand_source_type_id(i) ,
lt_demand_source_name(i) ,
lt_demand_source_header_id(i) ,
lt_demand_source_line_id(i) ,
lt_demand_source_delivery(i) ,
lt_primary_uom_code(i) ,
lt_primary_uom_id(i) ,
lt_reservation_uom_code(i) ,
lt_reservation_uom_id(i) ,
lt_reservation_quantity(i) ,
lt_prim_res_qty(i) ,
lt_autodetail_group_id(i) ,
lt_external_source_code(i) ,
lt_external_source_line_id(i) ,
lt_supply_source_type_id(i) ,
lt_supply_source_header_id(i) ,
lt_supply_source_line_id(i) ,
lt_supply_source_line_detail(i) ,
lt_supply_source_name(i) ,
lt_revision(i) ,
lt_subinventory_code(i) ,
lt_subinventory_id(i) ,
lt_locator_id(i) ,
lt_lot_number(i) ,
lt_lot_number_id(i) ,
lt_serial_number(i) ,
lt_serial_number_id(i) ,
lt_partial_quantities_allowed(i) ,
lt_auto_detailed(i) ,
lt_pick_slip_number(i) ,
lt_lpn_id(i) ,
lt_last_update_date(i) ,
lt_last_updated_by(i) ,
lt_creation_date(i) ,
lt_created_by(i) ,
lt_last_update_login(i) ,
lt_request_id(i) ,
lt_program_application_id(i) ,
lt_program_id(i) ,
lt_program_update_date(i) ,
lt_attribute_category(i) ,
lt_attribute1(i) ,
lt_attribute2(i) ,
lt_attribute3(i) ,
lt_attribute4(i) ,
lt_attribute5(i) ,
lt_attribute6(i) ,
lt_attribute7(i) ,
lt_attribute8(i) ,
lt_attribute9(i) ,
lt_attribute10(i) ,
lt_attribute11(i) ,
lt_attribute12(i) ,
lt_attribute13(i) ,
lt_attribute14(i) ,
lt_attribute15(i) ,
lt_ship_ready_flag(i) ,
lt_n_column1(i) ,
lt_detailed_quantity(i) ,
lt_cost_group_id(i) ,
lt_container_lpn_id(i) ,
lt_staged_flag(i) ,
lt_secondary_detailed_quantity(i) ,
lt_sec_res_qty(i) ,
lt_secondary_uom_code(i) ,
lt_secondary_uom_id(i) ,
lt_crossdock_flag(i) ,
lt_crossdock_criteria_id(i) ,
lt_demand_source_line_detail(i),
lt_serial_reservation_quantity(i) ,
lt_supply_receipt_date(i) ,
lt_demand_ship_date(i) ,
lt_exception_code(i) ,
lt_orig_supply_source_type_id(i) ,
lt_orig_sup_src_hdr_id(i) ,
lt_orig_supply_source_line_id(i) ,
lt_orig_sup_src_line_det(i) ,
lt_orig_demand_source_type_id(i) ,
lt_orig_dd_src_hdr_id(i) ,
lt_orig_demand_source_line_id(i) ,
lt_orig_dd_src_line_det(i) ,
lt_project_id(i) ,
lt_task_id(i)
);
DELETE
FROM mtl_reservations
WHERE demand_source_line_id IN
(SELECT DISTINCT wdd.source_line_id
FROM WMS_WP_WAVE_LINES wwl,
wsh_delivery_details wdd--11775489 get from wdd... FIXED
WHERE wave_header_id = p_wave_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = wwl.organization_id
);
INSERT
INTO mtl_reservations
(
reservation_id ,
requirement_date,
organization_id,
inventory_item_id,
demand_source_type_id,
demand_source_name,
demand_source_header_id,
demand_source_line_id,
demand_source_delivery,
primary_uom_code,
primary_uom_id,
reservation_uom_code ,
reservation_uom_id,
reservation_quantity,
primary_reservation_quantity,
autodetail_group_id,
external_source_code,
external_source_line_id,
supply_source_type_id,
supply_source_header_id,
supply_source_line_id,
supply_source_line_detail,
supply_source_name ,
revision,
subinventory_code,
subinventory_id,
locator_id,
lot_number ,
lot_number_id ,
serial_number,
serial_number_id ,
partial_quantities_allowed ,
auto_detailed ,
pick_slip_number ,
lpn_id ,
last_update_date,
last_updated_by,
creation_date ,
created_by,
last_update_login ,
request_id ,
program_application_id,
program_id ,
program_update_date,
attribute_category,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
ship_ready_flag,
n_column1,
detailed_quantity,
cost_group_id ,
container_lpn_id ,
staged_flag ,
secondary_detailed_quantity ,
secondary_reservation_quantity,
secondary_uom_code,
secondary_uom_id,
crossdock_flag,
crossdock_criteria_id,
demand_source_line_detail,
serial_reservation_quantity,
supply_receipt_date,
demand_ship_date ,
exception_code,
orig_supply_source_type_id,
orig_supply_source_header_id,
orig_supply_source_line_id,
orig_supply_source_line_detail ,
orig_demand_source_type_id,
orig_demand_source_header_id,
orig_demand_source_line_id,
orig_demand_source_line_detail,
project_id,
task_id
)
SELECT reservation_id ,
requirement_date,
organization_id,
inventory_item_id,
demand_source_type_id,
demand_source_name,
demand_source_header_id,
demand_source_line_id,
demand_source_delivery,
primary_uom_code,
primary_uom_id,
reservation_uom_code ,
reservation_uom_id,
reservation_quantity,
primary_reservation_quantity,
autodetail_group_id,
external_source_code,
external_source_line_id,
supply_source_type_id,
supply_source_header_id,
supply_source_line_id,
supply_source_line_detail,
supply_source_name ,
revision,
subinventory_code,
subinventory_id,
locator_id,
lot_number ,
lot_number_id ,
serial_number,
serial_number_id ,
partial_quantities_allowed ,
auto_detailed ,
pick_slip_number ,
lpn_id ,
last_update_date,
last_updated_by,
creation_date ,
created_by,
last_update_login ,
request_id ,
program_application_id,
program_id ,
program_update_date,
attribute_category,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
ship_ready_flag,
n_column1,
detailed_quantity,
cost_group_id ,
container_lpn_id ,
staged_flag ,
secondary_detailed_quantity ,
secondary_reservation_quantity,
secondary_uom_code,
secondary_uom_id,
crossdock_flag,
crossdock_criteria_id,
demand_source_line_detail,
serial_reservation_quantity,
supply_receipt_date,
demand_ship_date ,
exception_code,
orig_supply_source_type_id,
orig_supply_source_header_id,
orig_supply_source_line_id,
orig_supply_source_line_detail ,
orig_demand_source_type_id,
orig_demand_source_header_id,
orig_demand_source_line_id,
orig_demand_source_line_detail,
project_id,
task_id
FROM wms_wp_reservations_gtmp;
UPDATE wms_pr_workers
SET processed_flag = 'Y'
WHERE organization_id = p_organization_id
AND batch_id = p_wave_id
AND mo_header_id = p_mo_header_id
AND processed_flag = 'N'
AND worker_mode = 'RBP'
AND transaction_batch_id IS NULL;
SELECT rowid INTO l_row_id
FROM wms_pr_workers
WHERE organization_id = p_organization_id
AND batch_id = p_wave_id
AND mo_header_id = p_mo_header_id
AND processed_flag = 'N'
AND worker_mode = 'RBP'
AND transaction_batch_id IS NOT NULL
AND rownum < 2
FOR UPDATE NOWAIT;
UPDATE wms_pr_workers
SET processed_flag = 'Y'
WHERE rowid = l_row_id
RETURNING transaction_batch_id -- inventory_item_id
INTO l_inventory_item_id;
SELECT
attribute1, attribute10, attribute11, attribute12, attribute13,
attribute14, attribute15, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9,
attribute_category, created_by, creation_date, date_required, from_locator_id,
from_subinventory_code, from_subinventory_id, header_id, inventory_item_id, last_updated_by,
last_update_date, last_update_login, line_id, line_number, line_status,
lot_number, organization_id, program_application_id, program_id, program_update_date,
project_id, quantity, quantity_delivered, quantity_detailed, reason_id,
reference, reference_id, reference_type_code, request_id, revision,
serial_number_end, serial_number_start, status_date, task_id, to_account_id,
to_locator_id, to_subinventory_code, to_subinventory_id, transaction_header_id, transaction_type_id,
txn_source_id, txn_source_line_id, txn_source_line_detail_id, transaction_source_type_id, primary_quantity,
to_organization_id, pick_strategy_id, put_away_strategy_id, uom_code, unit_number,
ship_to_location_id, from_cost_group_id, to_cost_group_id, lpn_id, to_lpn_id,
pick_methodology_id, container_item_id, carton_grouping_id, FND_API.G_MISS_CHAR, FND_API.G_MISS_CHAR,
FND_API.G_MISS_CHAR, inspection_status, wms_process_flag, pick_slip_number, pick_slip_date,
ship_set_id, ship_model_id, model_quantity, required_quantity, secondary_quantity,
secondary_uom_code, secondary_quantity_detailed, secondary_quantity_delivered, grade_code, secondary_required_quantity
FROM mtl_txn_request_lines mtrl, wms_wp_rules_simulation wwr
WHERE mtrl.TXN_SOURCE_LINE_DETAIL_ID = wwr.delivery_detail_id
AND p_mode = 'RBP'
and wwr.wave_header_id=p_wave_id
AND mtrl.inventory_item_id = v_inventory_item_id
UNION
SELECT
mtrl.attribute1, mtrl.attribute10, mtrl.attribute11, mtrl.attribute12, mtrl.attribute13,
mtrl.attribute14, mtrl.attribute15, mtrl.attribute2, mtrl.attribute3, mtrl.attribute4,
mtrl.attribute5, mtrl.attribute6, mtrl.attribute7, mtrl.attribute8, mtrl.attribute9,
mtrl.attribute_category, mtrl.created_by, mtrl.creation_date, mtrl.date_required, mtrl.from_locator_id,
mtrl.from_subinventory_code, mtrl.from_subinventory_id, mtrl.header_id, mtrl.inventory_item_id, mtrl.last_updated_by,
mtrl.last_update_date, mtrl.last_update_login, mtrl.line_id, mtrl.line_number, mtrl.line_status,
mtrl.lot_number, mtrl.organization_id, mtrl.program_application_id, mtrl.program_id, mtrl.program_update_date,
mtrl.project_id, mtrl.quantity, mtrl.quantity_delivered, mtrl.quantity_detailed, mtrl.reason_id,
mtrl.reference, mtrl.reference_id, mtrl.reference_type_code, mtrl.request_id, mtrl.revision,
mtrl.serial_number_end, mtrl.serial_number_start, mtrl.status_date, mtrl.task_id, mtrl.to_account_id,
mtrl.to_locator_id, mtrl.to_subinventory_code, mtrl.to_subinventory_id, mtrl.transaction_header_id, mtrl.transaction_type_id,
mtrl.txn_source_id, mtrl.txn_source_line_id, mtrl.txn_source_line_detail_id, mtrl.transaction_source_type_id, mtrl.primary_quantity,
mtrl.to_organization_id, mtrl.pick_strategy_id, mtrl.put_away_strategy_id, mtrl.uom_code, mtrl.unit_number,
mtrl.ship_to_location_id, mtrl.from_cost_group_id, mtrl.to_cost_group_id, mtrl.lpn_id, mtrl.to_lpn_id,
mtrl.pick_methodology_id, mtrl.container_item_id, mtrl.carton_grouping_id, FND_API.G_MISS_CHAR, FND_API.G_MISS_CHAR,
FND_API.G_MISS_CHAR, mtrl.inspection_status, mtrl.wms_process_flag, mtrl.pick_slip_number, mtrl.pick_slip_date,
mtrl.ship_set_id, mtrl.ship_model_id, mtrl.model_quantity, mtrl.required_quantity, mtrl.secondary_quantity,
mtrl.secondary_uom_code, mtrl.secondary_quantity_detailed, mtrl.secondary_quantity_delivered, mtrl.grade_code, mtrl.secondary_required_quantity
FROM mtl_txn_request_lines mtrl, wms_wp_rules_simulation wwr, wsh_delivery_details wdd
WHERE mtrl.TXN_SOURCE_LINE_DETAIL_ID = wwr.delivery_detail_id
AND wwr.delivery_detail_id = wdd.delivery_detail_id
AND p_mode = 'RBP-SS'
and wwr.wave_header_id=p_wave_id
AND (wdd.SHIP_SET_ID IS NOT NULL OR Nvl(wdd.SHIP_MODEL_COMPLETE_FLAG, 'N') = 'Y')
ORDER BY line_id
;
print_debug('In wave simulation mode. Update the allocation table with complete quantity fulfilled', l_debug);
UPDATE wms_wp_rules_simulation
SET ALLOCATED_QUANTITY = l_mo_line.quantity,
ALLOCATED_QUANTITY2 = l_mo_line.secondary_quantity
WHERE DELIVERY_DETAIL_ID = l_mo_line.txn_source_line_detail_id
and wave_header_id=p_wave_id;
update mtl_txn_request_lines
set quantity = 0,
quantity_detailed = 0,
secondary_quantity = decode(secondary_quantity, fnd_api.g_miss_num, NULL, 0),
secondary_quantity_detailed = decode(secondary_quantity_detailed, fnd_api.g_miss_num, NULL, 0),
line_status = 5,
status_date = sysdate
where line_id = l_mo_line.line_id;
SELECT ordered_quantity, order_quantity_uom
INTO l_cur_txn_source_req_qty, l_txn_source_line_uom
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_cur_txn_source_line_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = l_mo_line.organization_id
AND inventory_item_id = l_mo_line.inventory_item_id;
SELECT ordered_quantity, order_quantity_uom
INTO l_set_txn_source_req_qty, l_txn_source_line_uom
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_set_txn_source_line_id;
update mtl_txn_request_lines
set quantity = 0,
quantity_detailed = 0,
line_status = 5,
status_date = sysdate,
model_quantity = l_new_model_quantity
where line_id = l_mo_line.line_id;
update mtl_txn_request_lines
set quantity = l_new_line_quantity,
quantity_detailed = NULL,
model_quantity = l_new_model_quantity
where line_id = l_mo_line.line_id;
SELECT WDD.ORGANIZATION_ID, WDD.INVENTORY_ITEM_ID, COUNT(wwl.wave_line_id)
FROM wms_wp_wave_lines wwl,
wsh_delivery_details wdd --11775489 get from wdd... FIXED
WHERE wwl.organization_id = p_organization_id
and wwl.wave_header_id = p_wave_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
GROUP BY WDD.ORGANIZATION_ID, WDD.INVENTORY_ITEM_ID;
SELECT Wdd.ORGANIZATION_ID, DECODE(Wdd.SHIP_SET_ID,NULL,DECODE(Wdd.SHIP_MODEL_COMPLETE_FLAG,'Y',NULL,Wdd.INVENTORY_ITEM_ID),NULL), COUNT(*)
FROM wms_wp_rules_simulation wwl, wsh_delivery_details wdd
WHERE wdd.organization_id = p_organization_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.wave_header_id=p_wave_id
GROUP BY Wdd.ORGANIZATION_ID, DECODE(Wdd.SHIP_SET_ID,NULL,DECODE(Wdd.SHIP_MODEL_COMPLETE_FLAG,'Y',NULL,Wdd.INVENTORY_ITEM_ID),NULL);
INSERT INTO wms_pr_workers
(batch_id,
worker_mode,
processed_flag,
organization_id,
mo_header_id,
transaction_batch_id,
detailed_count
)
VALUES (p_wave_id,
'RBP',
'N',
l_cTblorg(i),
p_mo_header_id,
l_cTblitem(i),
l_cTblcount(i)
);
print_debug(l_tot_worker_records ||' no of rows inserted into wms_pr_workers', l_debug);
SELECT Count(*) INTO l_tot_smc_records
FROM wms_pr_workers
WHERE organization_id=p_organization_id
AND batch_id=p_wave_id
and mo_header_id=p_mo_header_id
AND worker_mode='RBP'
AND processed_flag='N'
AND transaction_batch_id IS NULL;
select mtrl.TXN_SOURCE_LINE_DETAIL_ID delivery_detail_id,
sum(transaction_quantity) l_quantity,
sum(secondary_transaction_quantity) l_quantity2
from wms_wp_rules_simulation wwr,
mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl
where wwr.delivery_detail_id = mtrl.TXN_SOURCE_LINE_DETAIL_ID
and wwr.wave_header_id=p_wave_header_id
AND mtrl.line_Id = mmtt.move_order_line_id
and (mmtt.parent_line_id IS NULL OR
mmtt.transaction_temp_id <> mmtt.parent_line_id)
group by mtrl.TXN_SOURCE_LINE_DETAIL_ID;
update wms_wp_rules_simulation
set allocated_quantity = x_wp_rules_tbl(i).allocated_qty,
allocated_quantity2 = x_wp_rules_tbl(i).allocated_qty2
where delivery_detail_id = x_wp_rules_tbl(i).delivery_detail_id
and wave_header_id=p_wave_header_id;
select enable_labor_planning
into l_enable_labor_planning
from wms_wp_planning_Criteria_vl
where planning_criteria_id = p_planning_criteria_id;
select count(1)
into v_labor_count
from wms_wp_labor_planning
where planning_Criteria_id = p_planning_Criteria_id;
delete from wms_wp_labor_statistics
where wave_header_id = p_wave_header_id;
SELECT DISTINCT mmtt1.transaction_temp_id, mmtt1.transaction_header_id, mmtt1.ITEM_LOT_CONTROL_CODE, mmtt1.ITEM_SERIAL_CONTROL_CODE
FROM mtl_material_transactions_temp mmtt1, mtl_material_transactions_temp mmtt2
WHERE mmtt2.move_order_header_id=p_mo_header_id
AND (mmtt1.transaction_temp_id=mmtt2.transaction_temp_id OR mmtt1.transaction_temp_id=mmtt2.parent_line_id);
IF g_update_wdd = 'Y' THEN
UPDATE wsh_delivery_details
SET move_order_line_id=NULL
WHERE delivery_detail_id IN (SELECT delivery_Detail_id
FROM wms_wp_rules_simulation where wave_header_id=p_wave_id);
print_debug(SQL%ROWCOUNT || ' Rows updated from WDD ',l_debug);
g_update_wdd := 'N';
print_debug('Could not update WDD', l_debug);
DELETE FROM wms_pr_workers
WHERE batch_id = p_wave_id
AND worker_mode = 'RBP'
AND organization_id = p_organization_id
AND mo_header_id = p_mo_header_id;
print_debug(SQL%ROWCOUNT || ' Rows deleted from wms_pr_workers ',l_debug);
DELETE FROM wms_wp_reservations_gtmp;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL, line_mark_id = NULL, lot_line_mark_id = NULL
WHERE l_delMMTTserialctrl(i) IN (2, 5)
AND ((l_delMMTTlotctrl(i) <> 2 AND (group_mark_id = l_delMMTTtempid(i) OR group_mark_id = l_delMMTThdrid(i)))
OR (l_delMMTTlotctrl(i) = 2
AND (group_mark_id IN (SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_delMMTTtempid(i))
OR group_mark_id = l_delMMTThdrid(i))));
print_debug(SQL%ROWCOUNT || ' Rows updated from MSN ',l_debug);
DELETE FROM mtl_serial_numbers_temp
WHERE l_delMMTTserialctrl(i) IN (2, 5)
AND ((l_delMMTTlotctrl(i) <> 2
AND transaction_temp_id = l_delMMTTtempid(i))
OR (l_delMMTTlotctrl(i) = 2
AND transaction_temp_id IN (SELECT SERIAL_TRANSACTION_TEMP_ID
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_delMMTTtempid(i))));
print_debug(SQL%ROWCOUNT|| ' Rows deleted from MSNT ',l_debug);
DELETE FROM mtl_transaction_lots_temp
WHERE l_delMMTTlotctrl(i) = 2
AND transaction_temp_id = l_delMMTTtempid(i);
print_debug(SQL%ROWCOUNT || ' Rows deleted from MTLT ',l_debug);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_delMMTTtempid(i);
print_debug(SQL%ROWCOUNT || ' Rows deleted from MMTT ',l_debug);
DELETE FROM mtl_txn_request_Lines WHERE header_id=p_mo_header_id;
print_debug(SQL%ROWCOUNT || ' Rows deleted from MTRL ', l_debug);
DELETE FROM mtl_txn_request_headers WHERE header_id=p_mo_header_id;
print_debug(SQL%ROWCOUNT || ' Rows deleted from MTRH ', l_debug);
print_debug('Update WDD', l_debug);
UPDATE wsh_delivery_details
SET move_order_line_id=NULL
WHERE delivery_detail_id IN (SELECT delivery_Detail_id
FROM wms_wp_rules_simulation where wave_header_id=p_wave_id);
g_update_wdd := 'N';
print_debug('Could not update WDD. need to update it again during clean_up', l_debug);
SELECT DISTINCT wdd.inventory_item_id, wwl.organization_id
FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd--11775489 get from wdd...FIXED
WHERE wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and nvl(remove_from_wave_flag, 'N') <> 'Y';
select wdd.source_header_number,
wwl.delivery_detail_id,
wdd.organization_id,
wdd.inventory_item_id,
wdd.requested_quantity,
wdd.requested_quantity_uom,
wwh.planning_criteria_id,
wdd.ship_set_id,
wdd.source_line_id,
wdd.top_model_line_id,
-- wdd.source_header_id,
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id) demand_source_header_id,
-- wdd.source_header_type_id,
decode(wdd.source_document_type_id, 10, 8, 2) demand_source_type_id,
wwl.wave_line_id
from wms_wp_wave_headers_vl wwh,
wms_wp_wave_lines wwl, --11775489 get from wdd... FIXED...may have performance impact...
wms_wp_planning_criteria_vl wwp,
wsh_delivery_details wdd
where wwh.wave_header_id = p_wave_header_id
and wwl.organization_id = l_org_id
and wdd.inventory_item_id = l_item_id
and wwp.planning_criteria_id = p_planning_criteria_id
and wwh.planning_criteria_id = wwp.planning_criteria_id
and wwh.wave_header_id = wwl.wave_header_id
and wwl.delivery_detail_id = wdd.delivery_Detail_id
and wdd.organization_id = wwl.organization_id
and wdd.released_status in ('R','B') --- For Hot Order Changes
and nvl(remove_from_wave_flag, 'N') <> 'Y';
SELECT WDD.DELIVERY_DETAIL_ID
FROM WMS_WP_WAVE_LINES WWL, WSH_DELIVERY_DETAILS WDD --11775489 added org_id join... FIXED
WHERE WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and WWL.organization_id = wdd.organization_id
AND WWL.WAVE_HEADER_ID = p_wave_header_id
AND WDD.SHIP_SET_ID = v_shipset_no
and wdd.released_status in ('R','B') --- For Hot Order Changes
and v_shipset_no is not null;
SELECT WDD.DELIVERY_DETAIL_ID
FROM WMS_WP_WAVE_LINES WWL, WSH_DELIVERY_DETAILS WDD --11775489 added org_id join... FIXED
WHERE WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and WWL.organization_id = wdd.organization_id
AND WWL.WAVE_HEADER_ID = p_wave_header_id
AND WDD.top_model_line_id = v_model_no
and wdd.released_status in ('R','B') --- For Hot Order Changes
and v_model_no is not null;
SELECT wwl.wave_line_id, wdd.source_line_id, wdd.source_header_id
FROM WMS_WP_WAVE_LINES WWL, --11775489 get from wdd... FIXED... check below comment
wsh_delivery_details wdd
WHERE WWL.WAVE_HEADER_ID = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
-- and wdd.released_status in ('R','B') --- For Hot Order Changes
and nvl(wwl.remove_from_wave_flag, 'N') <> 'Y';
select wwl.delivery_Detail_id
from wms_wp_wave_lines wwl, wsh_delivery_details wdd
where wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id --11775489 get from wdd... FIXED
and wwl.organization_id = wdd.organization_id;
SELECT start_time, organization_id,pick_seq_rule_id,
pick_grouping_rule_id,pick_subinventory,staging_subinventory,
DEFAULT_STAGE_LOCATOR_ID,wave_status,pull_replenishment_flag
INTO l_start_time, l_organization_id,
g_pick_seq_rule_id,
g_pick_grouping_rule_id,
g_pick_subinventory,
g_staging_subinventory,
g_to_locator,
l_wave_status,
l_pull_replenishment
FROM wms_wp_wave_headers_vl
WHERE wave_header_id = p_wave_header_id;
select enable_labor_planning
into l_enable_labor_planning
from wms_wp_planning_Criteria_vl
where planning_criteria_id = p_planning_criteria_id;
SELECT MAX(nvl(wdab.end_time,
(nvl(wts.planned_departure_date,
nvl(wnd.latest_pickup_date, wdd.date_scheduled)))))
into l_target_date
FROM wms_wp_wave_lines wwl, --11775489 get from wdd... FIXED...check performance impact
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wms_dock_appointments_b wdab
WHERE wwl.organization_id = l_organization_id
AND wwl.wave_header_id = p_wave_header_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.organization_id = wwl.organization_id
--and wdd.released_status in ('R','B') --- For Hot Order Changes
AND wda.delivery_id = wnd.delivery_id(+)
AND wnd.delivery_id = wdl.delivery_id(+)
AND wdl.pick_up_stop_id = wts.stop_id(+)
AND wts.stop_id = wdab.trip_stop(+);
insert into wms_wp_rules_simulation
(wave_header_id,delivery_Detail_id, requested_quantity, requested_quantity2)
select wwl.wave_header_id,wwl.delivery_Detail_id, wdd.requested_quantity, wdd.requested_quantity2
from wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd... FIXED
where wave_header_id = p_wave_header_id
and wdd.released_status in ('R','B') --- For Hot Order Changes;
update wms_wp_wave_lines
set crossdock_quantity = 0
where wave_header_id = p_wave_header_id;
select Backorder_flag,
Reject_order_line_flag,
Reject_all_lines_shipset_flag,
Reject_all_lines_model_flag,
Reject_Order_flag,
Reserve_stock_flag,
auto_create_deliveries_flag,
credit_check_hold_flag,
picking_subinventory,
enable_labor_planning,
planning_method,
crossdock_criteria,
crossdock_criteria_id,
allocation_method
into v_backorder_flag,
v_reject_line_flag,
v_reject_shipset_flag,
v_reject_model_flag,
v_reject_order_flag,
v_reserve_flag,
v_create_delivery_flag,
v_credit_check_hold_flag,
l_pick_subinventory,
l_enable_labor_planning,
l_planning_method,
l_crossdock_criteria,
l_crossdock_criteria_id,
l_allocation_method
from wms_wp_planning_criteria_vl
where planning_criteria_id = p_planning_criteria_id;
SELECT nvl(sum(reservation_quantity), 0)
INTO l_reserved_qty
FROM mtl_Reservations
WHERE demand_source_line_id = c_rec.SOURCE_LINE_ID
and organization_id = L_ORG_ID
and inventory_item_id = L_ITEM_ID
and (subinventory_code = l_pick_subinventory or
l_pick_subinventory is null);
SELECT Nvl(SUM(wdd.requested_quantity), 0)
INTO l_other_wdd_qty
FROM wsh_delivery_details wdd
WHERE wdd.organization_id = l_org_id
AND wdd.inventory_item_id = l_item_id
and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
and wdd.delivery_Detail_id not in
(select wwl.delivery_detail_id
from wms_wp_wave_lines wwl, wsh_delivery_details wdd2 --11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd2.delivery_Detail_id
and wwl.organization_id = wdd2.organization_id
-- and nvl(remove_from_wave_flag, 'N') <> 'Y' --- Removed by Ajith in Phase III as planning it second time is giving an issue
and wdd2.source_line_id = wdd.source_line_id);
update wms_wp_rules_simulation
set allocated_quantity = c_rec.requested_quantity
where delivery_detail_id = c_rec.delivery_Detail_id
and wave_header_id=p_wave_header_id;
update wms_wp_rules_simulation
set allocated_quantity =
(l_atr + l_reserved_qty)
where delivery_detail_id = c_rec.delivery_Detail_id
and wave_header_id=p_wave_header_id;
select sum(wdd.requested_quantity) into l_total_qty
from wms_wp_wave_lines wwl, wsh_delivery_details wdd
where wave_header_id = p_wave_header_id --11775489 get from wdd... FIXED
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wdd.inventory_item_id = l_item_id and wdd.organization_id=l_org_id;
SELECT nvl(sum(reservation_quantity), 0)
INTO l_reserved_qty
FROM mtl_Reservations
WHERE demand_source_line_id = c_rec.SOURCE_LINE_ID
and organization_id = L_ORG_ID
and inventory_item_id = L_ITEM_ID
and (subinventory_code = l_pick_subinventory or
l_pick_subinventory is null);
SELECT Nvl(SUM(wdd.requested_quantity), 0)
INTO l_other_wdd_qty
FROM wsh_delivery_details wdd
WHERE wdd.organization_id = l_org_id
AND wdd.inventory_item_id = l_item_id
and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
and wdd.delivery_Detail_id not in
(select wwl.delivery_detail_id
from wms_wp_wave_lines wwl, wsh_delivery_details wdd2 --11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd2.delivery_Detail_id
and wwl.organization_id = wdd2.organization_id
-- and nvl(remove_from_wave_flag, 'N') <> 'Y' --- Removed by Ajith in Phase III as planning it second time is giving an issue
and wdd2.source_line_id = wdd.source_line_id);
select crossdocked_quantity
into l_crossdock_qty
from wms_wp_rules_simulation
where delivery_Detail_id = c_rec.delivery_Detail_id
and wave_header_id=p_wave_header_id;
update wms_wp_rules_simulation
set crossdocked_quantity = 0
where delivery_detail_id = c_rec.delivery_Detail_id
and wave_header_id=p_wave_header_id;
update wms_wp_rules_simulation
set crossdocked_quantity = c_rec.requested_quantity -
(l_atr + l_reserved_qty)
where delivery_detail_id = c_rec.delivery_Detail_id
and wave_header_id=p_wave_header_id;
select crossdocked_quantity
into l_crossdock_qty
from wms_wp_rules_simulation
where delivery_Detail_id = c_rec.delivery_Detail_id
and wave_header_id=p_wave_header_id;
select crossdocked_quantity, allocated_quantity
into l_crossdock_qty, l_allocated_qty
from wms_wp_rules_simulation
where delivery_Detail_id = c_rec.delivery_Detail_id
and wave_header_id=p_wave_header_id;
update wms_wp_wave_lines
set remove_from_wave_flag = 'Y',
message = 'Line removed from wave due to credit check hold'
where wave_line_id = l_credit_check_tbl(c1)
and wave_header_id = p_wave_header_id
and nvl(remove_from_wave_flag, 'N') <> 'Y';
l_credit_check_tbl.delete;
l_credit_check_tbl.delete;
G_backorder_deliv_tab.DELETE;
G_backorder_qty_tab.DELETE;
G_dummy_table.DELETE;
G_backorder_deliv_tab.DELETE;
G_backorder_qty_tab.DELETE;
G_dummy_table.DELETE;
update wms_wp_wave_lines
set remove_from_wave_flag = 'Y',
message = 'Line removed from wave due to insufficient quantity'
where delivery_Detail_id = X_DELIVERY_DETAIL_TBL(l)
and wave_header_id = p_wave_header_id;
X_DELIVERY_DETAIL_TBL.delete;
update wms_wp_wave_lines
set remove_from_wave_flag = 'Y',
message = 'Line removed from wave due to insufficient quantity'
where delivery_Detail_id = X_DELIVERY_DETAIL_TBL(l)
and wave_header_id = p_wave_header_id;
X_DELIVERY_DETAIL_TBL.delete;
update wms_wp_wave_lines --11775489 get from wdd... FIXED
set remove_from_wave_flag = 'Y',
message = 'Line removed from wave due to insufficient quantity'
where wave_line_id in (select wwl.wave_line_id
from wms_wp_wave_lines wwl, wsh_delivery_details wdd
where wwl.wave_header_id = p_wave_header_id
and nvl(wwl.remove_from_wave_flag, 'N') <> 'Y'
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and wdd.source_header_number = l_insufficient_order_number(i3));
l_insufficient_order_number.delete;
update wms_wp_wave_lines
set remove_from_wave_flag = 'Y',
message = 'Line removed from wave due to insufficient quantity'
where delivery_Detail_id = l_insufficient_DD_TBL(i2)
and wave_header_id = p_wave_header_id
and nvl(remove_from_wave_flag, 'N') <> 'Y';
l_insufficient_DD_TBL.delete;
select delivery_detail_id, crossdocked_quantity bulk collect
into x_wp_crossdock_tbl
from wms_wp_rules_simulation
where wave_header_id=p_wave_header_id;
update wms_wp_wave_lines
set crossdock_quantity = x_wp_crossdock_tbl(i).crossdock_qty
where delivery_detail_id = x_wp_crossdock_tbl(i).delivery_detail_id
and wave_header_id = p_wave_header_id;
update wms_wp_wave_lines
set planned_fill_rate = X_CONSOL_LINES_fill_rate(m2)
where wave_line_id = X_CONSOL_LINES_wave_line_id(m2);
print_debug('Before Calling API to Update Wave Status: ', l_debug);
update_wave_header_status(x_return_status, p_wave_header_id, 'Planned');
select count(1)
into v_labor_count
from wms_wp_labor_planning
where planning_Criteria_id = p_planning_Criteria_id;
delete from wms_wp_labor_statistics
where wave_header_id = p_wave_header_id;
delete from wms_wp_rules_simulation where wave_header_id=p_wave_header_id;
delete from wms_wp_rules_simulation where wave_header_id=p_wave_header_id;
SELECT distinct round(bst.to_time / 60) end_time,
round(bst.from_time / 60) start_time,
nvl(brs.CAPACITY_UNITS, 0) cap_units,
brs.shift_num shift_num
from bom_resource_shifts brs,
mtl_parameters mp,
bom_shift_dates bsd,
bom_shift_times bst
where brs.department_id = p_dept_id
and brs.resource_id = p_resource_id
and mp.organization_id = p_org_id
and mp.calendar_code = bsd.calendar_code
and mp.calendar_exception_set_id = bsd.exception_set_id
and brs.shift_num = bsd.shift_num
--and bsd.shift_date BETWEEN Trunc(SYSDATE) AND Trunc(SYSDATE + 1)
and bsd.seq_num is not null
and bst.shift_num = bsd.shift_num
and bst.calendar_code = bsd.calendar_code
order BY brs.shift_num;
select count(*)
into l_instance_count
from BOM_DEPT_RES_INSTANCES
WHERE resource_id = p_resource_id
and department_id = p_dept_id;
select source_subinventory,
destination_subinventory,
pick_uom,
transaction_time,
travel_time,
processing_overhead_duration
from wms_wp_labor_planning
where planning_criteria_id = p_planning_criteria_id
and resource_type = p_resource;
select LABOR_SETUP_MODE into l_LABOR_SETUP_MODE from wms_wp_planning_criteria_vl
where PLANNING_CRITERIA_ID = p_planning_criteria_id;
select department_id
into l_dept_id
from wms_wp_planning_criteria_vl
where planning_criteria_id = p_planning_criteria_id;
select (nvl(utilization, 100) / 100),
(nvl(efficiency, 100) / 100),
capacity_units,
unit_of_measure,
resource_id,
ORGANIZATION_ID,
AVAILABLE_24_HOURS_FLAG
into l_utilization,
l_efficiency,
l_capacity_units,
l_unit_of_measure,
l_resource_id,
l_org_id,
l_24hrs
from bom_department_resources_v
where department_id = l_dept_id
and resource_code = p_resource;
select sum(mmtt.transaction_quantity), Count(*), br.resource_type
into l_transaction_quantity, l_number_of_tasks, l_resource_type
from bom_resources br,
mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt
where mmtt.subinventory_code =
l_resource_details.source_subinventory
and mmtt.transfer_subinventory =
l_resource_details.destination_subinventory
and mmtt.transaction_uom = l_resource_details.pick_uom
and mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
and wdt.person_resource_id = br.resource_id(+)
and br.resource_code = p_resource
group by br.resource_type;
SELECT resource_type
INTO l_resource_type
FROM bom_resources
WHERE resource_code = p_resource
AND resource_id IN
(SELECT DISTINCT resource_id
FROM BOM_DEPARTMENT_RESOURCES
WHERE department_id IN
(SELECT DISTINCT department_id
FROM wms_wp_planning_criteria_vl
WHERE planning_criteria_id = p_planning_criteria_id));
select sum(mmtt.transaction_quantity), Count(*), br.resource_type
into l_transaction_quantity, l_number_of_tasks, l_resource_type
from bom_resources br,
mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt,
wms_zone_locators wzl1,
wms_zone_locators wzl2,
wms_zones_vl wz1,
wms_zones_vl wz2
where wz1.zone_name =
l_resource_details.source_subinventory
and wz2.zone_name =
l_resource_details.destination_subinventory
and mmtt.locator_id = wzl1.inventory_location_id
and mmtt.transfer_to_location = wzl2.inventory_location_id
and mmtt.organization_id = wzl1.organization_id
and mmtt.organization_id = wzl2.organization_id
and wz1.zone_id = wzl1.zone_id
and wz2.zone_id = wzl2.zone_id
and wz1.zone_type = 'L'
and wz2.zone_type = 'L'
and mmtt.transaction_uom = l_resource_details.pick_uom
and mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
and wdt.person_resource_id = br.resource_id(+)
and br.resource_code = p_resource
group by br.resource_type;
SELECT resource_type
INTO l_resource_type
FROM bom_resources
WHERE resource_code = p_resource
AND resource_id IN
(SELECT DISTINCT resource_id
FROM BOM_DEPARTMENT_RESOURCES
WHERE department_id IN
(SELECT DISTINCT department_id
FROM wms_wp_planning_criteria_vl
WHERE planning_criteria_id = p_planning_criteria_id));
select time_uom
into l_uom
from wms_wp_planning_criteria_vl
where planning_Criteria_id = p_planning_criteria_id;
SELECT MAX(nvl(wdab.end_time,
(nvl(wts.planned_departure_date,
nvl(wnd.latest_pickup_date, wdd.date_scheduled)))))
into l_target_date
FROM wms_wp_wave_lines wwl, --11775489 get from wdd... FIXED check performance impact
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wms_dock_appointments_b wdab
WHERE wwl.organization_id = l_org_id
AND wwl.wave_header_id = p_wave_header_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_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.pick_up_stop_id = wts.stop_id(+)
AND wts.stop_id = wdab.trip_stop(+);
SELECT start_time
INTO l_start_date
FROM wms_wp_wave_headers_vl
WHERE wave_header_id = p_wave_header_id;
select DISTINCT wwl.organization_id,
wdd.inventory_item_id,
wwh.pull_replenishment_flag pull_replenishment_flag,
wwl.wave_line_id,
(wdd.requested_quantity - wwl.crossdock_quantity) requested_quantity
from WMS_WP_WAVE_HEADERS_B wwh, wms_wp_wave_lines wwl,wsh_delivery_details wdd --11775489 get from wdd... FIXED check performance impact
where wwh.wave_header_id = p_wave_header_id
and wwh.wave_header_id = wwl.wave_header_id
AND Nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y'
AND wdd.inventory_item_id = l_item_id
and wwl.organization_id = l_org_id
and wwl.delivery_detail_id=wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wdd.released_status in ('R','B') --- For Hot Order Changes
ORDER BY inventory_item_id, wave_line_id;
SELECT DISTINCT wdd.inventory_item_id, wwl.organization_id
FROM wms_wp_wave_lines wwl,wsh_delivery_Details wdd --11775489 get from wdd... FIXED
WHERE wave_header_id = p_wave_header_id
and wwl.delivery_detail_id=wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wdd.released_status in ('R','B') --- For Hot Order Changes
AND Nvl(remove_from_Wave_flag, 'N') <> 'Y';
select DISTINCT MSI.SECONDARY_INVENTORY_NAME,
moqd.SUBINVENTORY_CODE,
wms_wave_planning_pvt.get_source_subinventory(MSIB.INVENTORY_ITEM_id,
MSI.SECONDARY_INVENTORY_NAME) source_subinventory,
MSI.PICK_UOM_CODE,
wms_wave_planning_pvt.get_conversion_rate(MSIB.INVENTORY_ITEM_id,
MSI.PICK_UOM_CODE,
MSIB.PRIMARY_UOM_CODE) AS CONVERSION_RATE,
MSI.PICKING_ORDER
from MTL_SECONDARY_INVENTORIES MSI,
MTL_SYSTEM_ITEMS_B MSIB,
mtl_onhand_quantities_Detail moqd
WHERE MSIB.organization_id = l_org_id
and MSIB.INVENTORY_ITEM_ID = l_item_id
AND MSI.PICK_UOM_CODE IS NOT NULL
AND (MSI.SECONDARY_INVENTORY_NAME = v_picking_subinventory or
v_picking_subinventory is null)
AND wms_wave_planning_pvt.get_conversion_rate(MSIB.INVENTORY_ITEM_id,
MSI.PICK_UOM_CODE,
MSIB.PRIMARY_UOM_CODE) > 0
AND moqd.INVENTORY_ITEM_id = MSIB.INVENTORY_ITEM_id
AND moqd.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND moqd.SUBINVENTORY_CODE = msi.SECONDARY_INVENTORY_NAME
AND moqd.ORGANIZATION_ID = msi.ORGANIZATION_ID
ORDER BY CONVERSION_RATE DESC, MSI.PICKING_ORDER;
SELECT source_subinventory,
destination_subinventory,
pick_uom,
transaction_time,
travel_time,
resource_type
FROM wms_wp_labor_planning
WHERE planning_criteria_id = p_planning_criteria_id
group by source_subinventory,
destination_subinventory,
pick_uom,
transaction_time,
travel_time,
resource_type; -- group by added by
SELECT distinct resource_type
FROM wms_wp_labor_planning
WHERE planning_criteria_id = p_planning_criteria_id;
SELECT distinct wwlp.resource_type
FROM wms_wp_labor_planning wwlp,BOM_DEPARTMENT_RESOURCES_V bdr,wms_wp_planning_criteria_vl wwp
WHERE wwlp.planning_criteria_id = p_planning_criteria_id
AND wwp.planning_criteria_id=wwlp.planning_criteria_id
and wwp.department_id=v_department_id
and wwp.department_id=bdr.department_id
and wwlp.resource_type=bdr.resource_code
AND (bdr.AVAILABLE_24_HOURS_FLAG=2
OR (SELECT Count(1) FROM BOM_DEPT_RES_INSTANCES WHERE resource_id= bdr.resource_id AND department_id=bdr.department_id) > 0);*/
SELECT RESOURCE_code,
(nvl(utilization, 100) / 100) utilization,
(nvl(efficiency, 100) / 100) efficiency,
unit_of_measure,
capacity_units
FROM BOM_DEPARTMENT_RESOURCES_V
WHERE department_id = v_department_id; */
select wwp.picking_subinventory,
wwp.destination_subinventory,
bulk_labor_planning_flag,
department_id,
time_uom,
allocation_method
into v_picking_subinventory,
v_destination_subinventory,
l_bulk_planning,
v_department_id,
v_time_uom,
l_allocation_method
from wms_wp_planning_criteria_vl wwp
where planning_Criteria_id = p_planning_criteria_id;
SELECT nvl(sum(reservation_quantity), 0)
INTO l_reserved_qty
FROM mtl_Reservations
WHERE demand_source_line_id in
(select wdd.source_line_id
from wms_wp_wave_lines wwl, wsh_delivery_details wdd--11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and nvl(wwl.remove_from_wave_flag, 'N') <> 'Y')
and organization_id = L_ORG_ID
and inventory_item_id = L_ITEM_ID
and (subinventory_code = v_picking_subinventory or
v_picking_subinventory is null);
SELECT Nvl(SUM(wdd.requested_quantity), 0)
INTO l_other_wdd_qty
FROM wsh_delivery_details wdd
WHERE wdd.organization_id = l_org_id
AND wdd.inventory_item_id = l_item_id
and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
and wdd.delivery_Detail_id not in
(select delivery_detail_id
from wms_wp_wave_lines
where wave_header_id = p_wave_header_id
and nvl(remove_from_wave_flag, 'N') <> 'Y');
select sum(wdd.requested_quantity), sum(wwl.crossdock_quantity)
into l_demand_qty, l_crossdock_qty
from wms_wp_wave_lines wwl,wsh_delivery_details wdd --11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and nvl(remove_from_wave_flag, 'N') <> 'Y'
and wwl.delivery_detail_id=wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wdd.released_status in ('R','B') --- For Hot Order Changes
and wdd.inventory_item_id = l_item_id;
select pull_replenishment_flag
into v_replenishment_required
from wms_wp_wave_headers_vl
where wave_header_id = p_wave_header_id;
update_bulk_labor_record(x_labor_plan_tbl => x_labor_plan_tbl,
v_replenishment_required => v_replenishment_required,
v_destination_subinventory => v_destination_subinventory,
p_demand_qty => l_demand_qty,
x_return_status => l_return_status);
x_labor_plan_tbl.delete;
ideal_labor_plan_tbl.delete;
x_labor_plan_tbl.delete;
SELECT 'Not Applicable',
v_destination_subinventory,
Min(wdd.requested_quantity_uom), --11775489 select from wdd
Sum(crossdock_quantity),
-1,
wdd.inventory_item_id,
-1 bulk collect
into c_labor_time_tbl
FROM wms_wp_wave_lines wwl,wsh_delivery_details wdd --11775489 get from wdd...
WHERE wave_header_id = p_wave_header_id
and wwl.delivery_detail_id=wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wdd.released_status in ('R','B') --- For Hot Order Changes
GROUP BY wdd.inventory_item_id
ORDER BY wdd.inventory_item_id;
SELECT 'Not Applicable',
v_destination_subinventory,
wwl.requested_quantity_uom, --11775489 select from wdd
crossdock_quantity,
-1,
wdd.inventory_item_id,
-1 bulk collect
into c_labor_time_tbl
FROM wms_wp_wave_lines wwl,wsh_delivery_details wdd --11775489 get from wdd...
WHERE wave_header_id = p_wave_header_id
and wwl.delivery_detail_id=wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wdd.released_status in ('R','B') --- For Hot Order Changes
ORDER BY inventory_item_id;
SELECT organization_id INTO l_org_id FROM wms_Wp_Wave_headers_vl WHERE wave_header_id=p_wave_header_id;
DELETE FROM wms_wp_labor_Statistics
WHERE wave_header_id = p_wave_header_id;
insert into wms_wp_labor_statistics
(wave_header_id,
resource_name,
planned_wave_load,
total_capacity,
actual_workload,
available_capacity,
NUMBER_OF_ACTUAL_TASKS,
NUMBER_OF_PLANNED_TASKS)
values
(p_wave_header_id,
x_resource_capacity_tbl(i).resource_name,
x_resource_capacity_tbl(i).planned_load,
x_resource_capacity_tbl(i).total_Capacity,
x_resource_capacity_tbl(i).current_load,
x_resource_capacity_tbl(i).available_capacity,
x_resource_capacity_tbl(i).actual_tasks,
x_resource_capacity_tbl(i).planned_tasks);
x_labor_time_tbl.delete;
c_labor_time_tbl.delete;
x_resource_capacity_tbl.delete;
procedure update_bulk_labor_record(x_labor_plan_tbl in OUT NOCOPY labor_plan_tbl,
v_replenishment_required in varchar2,
v_destination_subinventory in varchar2,
p_demand_qty in OUT NOCOPY number,
x_return_status OUT NOCOPY varchar2)
is
i NUMBER := 0;
print_debug('Error in Update Labor record: ' || SQLCODE || ' : ' ||
SQLERRM,
l_debug);
end update_bulk_labor_record;
select source_subinventory
into v_source_subinventory
from MTL_ITEM_SUB_INVENTORIES
where inventory_item_id = p_item_id
and SECONDARY_INVENTORY = p_subinventory;
UPDATE wms_wp_wave_lines
SET message = 'This line has been Firmed in Wave ' ||
p_wave_header_id,
remove_from_Wave_flag = 'Y'
WHERE delivery_detail_id IN
(SELECT wwl.delivery_detail_id
FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
WHERE wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id --11775489 get from wdd... FIXED
and wwl.organization_id = wdd.organization_id
and nvl(remove_from_wave_flag, 'N') <> 'Y')
and wave_header_id <> p_wave_header_id;
update wms_wp_wave_headers_vl
set wave_firmed_flag = 'Y'
where wave_header_id = p_wave_header_id;
/* update wms_wp_wave_headers_vl
set request_id = p_request_id
where wave_header_id = p_wave_header_id;*/
update_wave_header_status(x_return_status,
p_wave_header_id,
'Released',
p_batch_id);
update_wave_header_status(x_return_status,
p_wave_header_id,
'Released(Warning)',
p_batch_id);
SELECT distinct res_equip.inventory_item_id
FROM mtl_material_transactions_temp mmtt,
bom_resource_equipments res_equip,
bom_resources res,
bom_std_op_resources tt_x_res
WHERE mmtt.transaction_temp_id = p_task_id
AND mmtt.standard_operation_id = tt_x_res.standard_operation_id
AND tt_x_res.resource_id = res.resource_id
AND res.resource_type = 1
AND res_equip.resource_id = tt_x_res.resource_id;
SELECT mmtt.transaction_uom,
mmtt.inventory_item_id,
mmtt.transaction_quantity,
item.primary_uom_code,
item.organization_id
INTO l_txn_uom_code,
l_item_id,
l_init_qty,
l_item_prim_uom_code,
l_organization_id
FROM mtl_material_transactions_temp mmtt,
mtl_item_locations mil,
mtl_secondary_inventories msi,
mtl_system_items item
WHERE mmtt.transaction_temp_id = l_mmtt_tbl(i)
AND mmtt.locator_id = mil.inventory_location_id(+)
AND mmtt.organization_id = mil.organization_id(+)
AND mmtt.subinventory_code = msi.secondary_inventory_name
AND mmtt.organization_id = msi.organization_id
AND mmtt.inventory_item_id = item.inventory_item_id
AND mmtt.organization_id = item.organization_id;
SELECT count(distinct mmtt.transaction_temp_id)
into l_mmtt_count
FROM mtl_material_transactions_temp mmtt,
wsh_Delivery_details wdd,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE wdd.source_line_id = mmtt.trx_source_line_id
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and mmtt.transfer_to_location = p_consol_locator_id
and mmtt.wms_task_status <> 8
and wnd.delivery_id <> p_delivery_id
and wnd.delivery_id is not null;
SELECT count(distinct mmtt.transaction_temp_id)
into l_mmtt_count
FROM mtl_material_transactions_temp mmtt,
wsh_Delivery_details wdd,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE wdd.source_line_id = mmtt.trx_source_line_id
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and mmtt.wms_task_status <> 8
and mmtt.transfer_to_location = p_consol_locator_id
and wnd.delivery_id is not null;
select field_name,
--ltrim(rtrim(field_value)) field_value,
field_value,
organization_id,
query_type
from wms_saved_queries
where query_name = p_query_name
and (query_type = 'TASK_PLANNING' or
query_type = 'TEMP_TASK_PLANNING')
FOR UPDATE NOWAIT;
select transaction_temp_id,
status,
status_id,
task_type,
task_type_id,
source_header
from wms_waveplan_tasks_temp;
select distinct transaction_temp_id from wms_waveplan_tasks_temp;
SELECT DISTINCT carton_grouping_id
FROM wms_waveplan_tasks_temp wwtt,
mtl_txn_request_lines mtrl,
wsh_Delivery_details wdd
WHERE wwtt.move_order_line_id = mtrl.line_id
and wdd.source_line_id = wwtt.transaction_source_line_id
and wwtt.transaction_temp_id in
(select transaction_temp_id
from wms_wp_tp_mmtt
where indicator_flag = 'C')
and wwtt.status_id = 8; --- Ajith????????????
SELECT distinct wdd.delivery_detail_id
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl,
wsh_Delivery_details wdd
WHERE mmtt.move_order_line_id = mtrl.line_id
AND wdd.source_line_id = mmtt.trx_source_line_id
AND Nvl(wdd.REPLENISHMENT_STATUS, 'C') = 'C'
AND mtrl.carton_grouping_id = p_carton_grouping_id;
select DISTINCT (DELIVERY_DETAIL_ID) from
(
SELECT WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.REPLENISHMENT_STATUS = 'R'
AND (WDD.BATCH_ID IN
( SELECT WDD.BATCH_ID --bug14534180
FROM WSH_DELIVERY_DETAILS WDD,
MTL_TXN_REQUEST_LINES MTRL,
MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND WDD.SOURCE_LINE_ID = MMTT.TRX_SOURCE_LINE_ID
AND MTRL.CARTON_GROUPING_ID = p_carton_grouping_id))
UNION ALL
SELECT WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.REPLENISHMENT_STATUS = 'R'
AND (WDD.BATCH_ID IN
( SELECT SELECTED_BATCH_ID
FROM WSH_PICKING_BATCHES WPB,
MTL_TXN_REQUEST_LINES MTRL,
WSH_DELIVERY_DETAILS WDD2,
MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND WDD2.SOURCE_LINE_ID = MMTT.TRX_SOURCE_LINE_ID
AND WPB.BATCH_ID = WDD2.BATCH_ID
AND MTRL.CARTON_GROUPING_ID = p_carton_grouping_id))
);
SELECT wwtt.transaction_temp_id
FROM wms_waveplan_tasks_temp wwtt, mtl_txn_request_lines mtrl
WHERE wwtt.move_order_line_id = mtrl.line_id
and wwtt.transaction_temp_id in
(select transaction_temp_id
from wms_wp_tp_mmtt
where indicator_flag = 'C')
AND mtrl.carton_grouping_id = p_carton_grouping_id;
SELECT DISTINCT wwtt.user_task_type
FROM wms_waveplan_tasks_temp wwtt;
SELECT wwtt.transaction_temp_id transaction_temp_id
FROM wms_waveplan_tasks_temp wwtt
WHERE user_task_type=p_user_task_type;
SELECT distinct wts.trip_id trip_id
FROM wms_waveplan_tasks_temp wwtt,
wsh_Delivery_details wdd,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_LEGS WLG
WHERE WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND wdd.source_line_id = wwtt.transaction_source_line_id
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WTS.STOP_ID(+) = WLG.DROP_OFF_STOP_ID
AND WLG.DELIVERY_ID(+) = WND.DELIVERY_ID
and wwtt.transaction_temp_id in
(select transaction_temp_id
from wms_wp_tp_mmtt
where indicator_flag = 'F') --- Ajith????????????
and wwtt.status_id = 8
and wts.trip_id is not null; -- Get All Trips for tasks in UnReleased Status
SELECT distinct wts.stop_id stop_id, wts.STOP_SEQUENCE_NUMBER
FROM mtl_material_transactions_temp mmtt,
wsh_Delivery_details wdd,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_LEGS WLG
WHERE WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
and wts.trip_id = l_trip_id
AND wdd.source_line_id = mmtt.trx_source_line_id
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WTS.STOP_ID(+) = WLG.DROP_OFF_STOP_ID
AND WLG.DELIVERY_ID(+) = WND.DELIVERY_ID
ORDER BY wts.STOP_SEQUENCE_NUMBER desc;
SELECT distinct mmtt.transaction_temp_id transaction_temp_id
FROM mtl_material_transactions_temp mmtt,
wsh_Delivery_details wdd,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_LEGS WLG
WHERE wts.stop_id = l_stop_id
AND wdd.source_line_id = mmtt.trx_source_line_id
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WTS.STOP_ID(+) = WLG.DROP_OFF_STOP_ID
AND WLG.DELIVERY_ID(+) = WND.DELIVERY_ID
/* and wwtt.transaction_temp_id in
(select transaction_temp_id
from wms_wp_tp_mmtt
where indicator_flag = 'F') --- Ajith???????????? */
-- AND wwtt.status_id <> 6
-- ORDER BY wts.STOP_SEQUENCE_NUMBER desc
union
SELECT distinct mmt.transaction_id transaction_temp_id
FROM mtl_material_transactions mmt,
wsh_Delivery_details wdd,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_LEGS WLG
WHERE wts.stop_id = l_stop_id
AND wdd.source_line_id = mmt.trx_source_line_id
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WTS.STOP_ID(+) = WLG.DROP_OFF_STOP_ID
AND WLG.DELIVERY_ID(+) = WND.DELIVERY_ID
/* and wwtt.transaction_temp_id in
(select transaction_temp_id
from wms_wp_tp_mmtt
where indicator_flag = 'F') --- Ajith???????????? */
-- AND wwtt.status_id <> 6
-- ORDER BY wts.STOP_SEQUENCE_NUMBER desc
;
select distinct wwtt.to_locator_id, wwtt.to_locator
from wms_waveplan_tasks_temp wwtt, mtl_item_locations_kfv mil
where wwtt.to_locator_id = mil.inventory_location_id
and wwtt.to_organization_id = mil.organization_id
and mil.inventory_location_type in (4, 5)
and wwtt.transaction_temp_id in
(select transaction_temp_id
from wms_wp_tp_mmtt
where indicator_flag = 'T') --- Ajith????????????
and wwtt.status_id = 8; -- We get only the Un Released Lines Delivery.
select distinct wnd.delivery_id
from wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_details wdd2,
wsh_delivery_assignments wda
where wdd2.
lpn_id in (select distinct lpn_id
from mtl_onhand_quantities_detail moqd
where locator_id = p_consol_locator_id)
and WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id(+);
select wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where wnd.delivery_id = p_delivery_id
and WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and wdd.released_status in ('Y', 'S');
select wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where wnd.delivery_id = p_delivery_id
and WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and wdd.released_status in ('Y');
SELECT DISTINCT WND.DELIVERY_ID delivery_id
FROM wms_waveplan_tasks_temp wwtt,
wsh_Delivery_details wdd,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE wdd.source_line_id = wwtt.transaction_source_line_id
and wwtt.transaction_temp_id in
(select transaction_temp_id
from wms_wp_tp_mmtt
where indicator_flag = 'T') --- Ajith????????????
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and wwtt.status_id = 8 -- We get only the Un Released Lines Delivery.
and wwtt.to_locator_id = p_consol_locator_id
order by WND.DELIVERY_ID;
SELECT distinct wwtt.transaction_temp_id transaction_temp_id
FROM wms_waveplan_tasks_temp wwtt,
wsh_Delivery_details wdd,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE WND.DELIVERY_ID = p_delivery_mmtt_id
and wdd.source_line_id = wwtt.transaction_source_line_id
and wwtt.transaction_temp_id in
(select transaction_temp_id
from wms_wp_tp_mmtt
where indicator_flag = 'T')
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID;
select wdd.delivery_Detail_id, mmtt.move_order_line_id
-- into p_line_rows(l1), p_move_order_line_tbl(l1)
from wsh_delivery_details wdd, mtl_material_transactions_temp mmtt
where wdd.Source_line_id = mmtt.trx_source_line_id
AND Nvl(wdd.REPLENISHMENT_STATUS, 'C') = 'C'
and mmtt.transaction_temp_id in
(select * from table(wms_wave_planning_pvt.list_mmtt_release)); --11870443
select distinct HEADER_ID
from mtl_txn_request_lines
where to_char(line_id) in
(SELECT TRIM(SUBSTR(txt,
INSTR(txt, ',', 1, level) + 1,
INSTR(txt, ',', 1, level + 1) -
INSTR(txt, ',', 1, level) - 1)) AS token
FROM (SELECT ',' || l_move_order_line_id || ',' AS txt
FROM dual)
CONNECT BY level <=
LENGTH(txt) - LENGTH(REPLACE(txt, ',', '')) - 1);*/
select replen_completed_flag,
nvl(replen_tolerance, 0),
min_equip_capacity_flag,
rev_trip_stop_flag,
nvl(trip_stop_tolerance, 0),
cons_locator_flag,
nvl(cons_locator_tolerance, 0),
custom_task_plan_flag,
nvl(custom_plan_tolerance, 0)
into l_replenishment_complete,
l_replen_tolerance,
l_fill_capacity,
l_reverse_trip_Stop,
l_trip_tolerance,
l_consol_locator,
l_cons_locator_tolerance,
l_custom_flag,
l_custom_plan_tolerance
from wms_task_Release_vl
where criteria_id = p_task_release_id;
SELECT NVL(cartonization_flag, -1), NVL(cartonize_sales_orders, 'Y')
INTO v_cart_value, l_cartonize_sales_orders
FROM mtl_parameters
WHERE organization_id = p_organization_id;
delete from wms_waveplan_tasks_temp;
insert into wms_wp_tp_mmtt values (l_final_mmtt_table(m), 'N');
g_mmtt_table.delete; --11870443 change to global g_mmtt_table 11870443
select transaction_temp_id bulk collect
into g_mmtt_table --11870443 change to global g_mmtt_table 11870443
from wms_waveplan_tasks_temp
where user_Task_type = l_user_task_type.user_task_type;
select sum(transaction_quantity)
into l_sum_qty
from wms_waveplan_tasks_temp
where user_Task_type = l_user_task_type.user_task_type;
update wms_wp_tp_mmtt
set indicator_flag = 'F'
where transaction_temp_id = g_mmtt_table(i); --11870443 change to global g_mmtt_table 11870443
g_mmtt_table.DELETE; --11870443 change to global g_mmtt_table 11870443
update wms_wp_tp_mmtt
set indicator_flag = 'Y'
where transaction_temp_id = l_mmtt_table(i); */
g_mmtt_table.DELETE; --11870443 change to global g_mmtt_table 11870443
update wms_wp_tp_mmtt
set indicator_flag = 'F'
where transaction_temp_id = l_final_mmtt_table(i);
select count(1)
into l_completed_task_count
from mtl_material_transactions
where transaction_id = l_task.transaction_temp_id;
select 1
into l_unreleased_count
from wms_wp_tp_mmtt
where indicator_flag = 'F'
and transaction_temp_id = l_task.transaction_temp_id;
update wms_wp_tp_mmtt
set indicator_flag = 'T'
where transaction_temp_id = g_mmtt_table(i); --11870443 change to global g_mmtt_table 11870443
g_mmtt_table.delete; --11870443 change to global g_mmtt_table 11870443
update wms_wp_tp_mmtt
set indicator_flag = 'T'
where transaction_temp_id = g_mmtt_table(i); --11870443 change to global g_mmtt_table 11870443
update wms_wp_tp_mmtt
set indicator_flag = 'T'
where transaction_temp_id = l_final_mmtt_table(i);
update wms_wp_tp_mmtt
set indicator_flag = 'C'
where transaction_temp_id = l_mmtt_table2(i);
l_mmtt_table2.delete;
update wms_wp_tp_mmtt
set indicator_flag = 'C'
where transaction_temp_id = g_mmtt_table(i); --11870443 change to global g_mmtt_table 11870443
update wms_wp_tp_mmtt
set indicator_flag = 'C'
where transaction_temp_id = l_final_mmtt_table(i);
select transaction_temp_id bulk collect
into g_mmtt_table --11870443 change to global g_mmtt_table 11870443
from wms_wp_tp_mmtt
where indicator_flag = 'C';
print_DEBUG('Before Update mtrl carton grouping id to null .',
l_debug);
update mtl_txn_request_lines
set carton_grouping_id = null
where line_id = p_move_order_line_tbl(l2);
print_DEBUG('After Update mtrl .', l_debug);
/* l_group_match_seq_tbl.delete;
select WSH_DELIVERY_GROUP_S.nextval
into l_group_match_seq_tbl(i) .delivery_group_id
from dual;
select WSH_DELIVERY_GROUP_S.nextval
into l_group_match_seq_tbl(i) .delivery_group_id
from dual;
g_mmtt_table.delete; --11870443 change to global g_mmtt_table 11870443
update wms_wp_tp_mmtt
set indicator_flag = 'L'
where transaction_temp_id = g_mmtt_table(i); --11870443 change to global g_mmtt_table 11870443
g_mmtt_table.delete; --11870443 change to global g_mmtt_table 11870443
select transaction_temp_id bulk collect
into g_mmtt_table --11870443 change to global g_mmtt_table 11870443
from wms_wp_tp_mmtt
where indicator_flag = 'L';
l_trohdr_rec.last_updated_by := fnd_global.user_id;
l_trohdr_rec.last_update_date := sysdate;
l_trohdr_rec.last_update_login := fnd_global.user_id;
update mtl_txn_request_lines
set header_id = l_x_trohdr_rec.header_id,
line_number = line_count
where line_id = p_move_order_line_tbl(i2);
update mtl_material_transactions_temp
set move_order_header_id = l_x_trohdr_rec.header_id
where move_order_line_id = p_move_order_line_tbl(i3);
select HEADER_ID
into p_move_order_hdr_tbl(i2)
from mtl_txn_request_lines
where line_id = p_move_order_line_tbl(i2); */
print_DEBUG(' Transaction temp ids selected for Releasing .' ||
g_mmtt_table(l1),
l_debug);
UPDATE mtl_material_transactions_temp
SET wms_task_status = 1
WHERE transaction_temp_id = g_mmtt_table(i) --11870443 change to global g_mmtt_table 11870443
and wms_task_status = 8;
l_field_name_table.delete;
l_field_value_table.delete;
l_query_type_table.delete;
g_mmtt_table.delete;
SELECT Sum(outstanding_order_value(wdd.source_header_id,wdd.source_line_id)) net_value,
wdd.source_header_number,
max(ooh.TRANSACTIONAL_CURR_CODE) TRANSACTIONAL_CURR_CODE,
max(ooh.conversion_rate) conversion_rate,
max(ooh.conversion_type_code) conversion_type_code,
max(ooh.conversion_rate_Date) conversion_rate_Date
--max(org_id) org_id 12371804 remove this
FROM wms_wp_Wave_lines wwl, oe_order_headers_all ooh, wsh_delivery_details wdd --11775489 get from wdd... FIXED...check performance problem
WHERE wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
AND wdd.source_header_number = ooh.order_number
AND Nvl(wwl.remove_From_Wave_flag, 'N') <> 'Y'
GROUP BY wdd.source_header_number;
select currency_code
into G_SOB_CURRENCY
from oe_gl_sets_of_books_v ogsob, org_organization_definitions ood, wms_wp_wave_headers_b wwh
where wwh.wave_header_id = p_wave_header_id
and wwh.organization_id = ood.organization_id
and ood.set_of_books_id = ogsob.set_of_books_id;
PROCEDURE insert_wave_header(x_return_status OUT nocopy VARCHAR2,
p_wave_header_rec in wms_wp_wave_headers_vl%ROWTYPE) is
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
insert into wms_wp_wave_headers_vl
(WAVE_HEADER_ID,
WAVE_NAME,
WAVE_DESCRIPTION,
start_time, -- start time changes
WAVE_SOURCE,
WAVE_STATUS,
TYPE_ID,
BATCH_ID,
SHIP_TO_LOCATION_ID,
CUSTOMER_CLASS_ID,
pull_replenishment_flag,
INITIATE_WAVE_PLANNING,
RELEASE_IMMEDIATELY,
TABLE_NAME,
ADVANCED_CRITERIA,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ORGANIZATION_ID,
PICK_SEQ_RULE_ID,
PICK_GROUPING_RULE_ID,
TRIP_ID,
TRIP_STOP_ID,
SHIP_METHOD_CODE,
SHIPMENT_PRIORITY_CODE,
CARRIER_ID,
DELIVERY_ID,
FROM_ORDER_HEADER_ID,
ORDER_TYPE_ID,
CUSTOMER_ID,
TASK_ID,
PROJECT_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
INVENTORY_ITEM_ID,
BACKORDERS_FLAG,
INCLUDE_PLANNED_LINES,
TASK_PLANNING_FLAG,
APPEND_DELIVERIES,
AUTO_CREATE_DELIVERY,
AUTO_CREATE_DELIVERY_CRITERIA,
TASK_PRIORITY,
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
DEFAULT_ALLOCATION_METHOD,
ORDER_NAME,
CUSTOMER,
ORDER_TYPE,
CUSTOMER_CLASS,
SHIP_METHOD,
CARRIER,
SHIP_PRIORITY,
DELIVERY,
TRIP,
TRIP_STOP,
ITEM,
ITEM_CATEGORY,
PROJECT_NAME,
TASK_NAME,
SCHEDULED_DAYS,
SCHEDULED_HRS,
DOCK_APPOINTMENT_DAYS,
DOCK_APPOINTMENT_HOURS,
PICK_SLIP_GROUP,
RELEASE_SEQ_RULE,
STAGING_SUBINVENTORY,
STAGING_LOCATOR,
CROSS_DOCK_CRITERIA,
PICK_SUBINVENTORY,
PLANNING_CRITERIA,
PLANNING_CRITERIA_ID,
REQUEST_ID,
WAVE_FIRMED_FLAG)
values
(p_wave_header_rec.WAVE_HEADER_ID,
p_WAVE_NAME,
p_wave_header_rec.WAVE_DESCRIPTION,
p_wave_header_rec.start_time, -- start time changes
p_wave_header_rec.WAVE_SOURCE,
p_wave_header_rec.WAVE_STATUS,
p_wave_header_rec.TYPE_ID,
p_wave_header_rec.BATCH_ID,
p_wave_header_rec.SHIP_TO_LOCATION_ID,
p_wave_header_rec.CUSTOMER_CLASS_ID,
p_wave_header_rec.pull_replenishment_flag,
p_wave_header_rec.INITIATE_WAVE_PLANNING,
p_wave_header_rec.RELEASE_IMMEDIATELY,
p_wave_header_rec.TABLE_NAME,
p_wave_header_rec.ADVANCED_CRITERIA,
p_wave_header_rec.CREATED_BY,
p_wave_header_rec.CREATION_DATE,
p_wave_header_rec.LAST_UPDATED_BY,
p_wave_header_rec.LAST_UPDATE_DATE,
p_wave_header_rec.LAST_UPDATE_LOGIN,
p_wave_header_rec.ORGANIZATION_ID,
p_wave_header_rec.PICK_SEQ_RULE_ID,
p_wave_header_rec.PICK_GROUPING_RULE_ID,
p_wave_header_rec.TRIP_ID,
p_wave_header_rec.TRIP_STOP_ID,
p_wave_header_rec.SHIP_METHOD_CODE,
p_wave_header_rec.SHIPMENT_PRIORITY_CODE,
p_wave_header_rec.CARRIER_ID,
p_wave_header_rec.DELIVERY_ID,
p_wave_header_rec.FROM_ORDER_HEADER_ID,
p_wave_header_rec.ORDER_TYPE_ID,
p_wave_header_rec.CUSTOMER_ID,
p_wave_header_rec.TASK_ID,
p_wave_header_rec.PROJECT_ID,
p_wave_header_rec.CATEGORY_SET_ID,
p_wave_header_rec.CATEGORY_ID,
p_wave_header_rec.INVENTORY_ITEM_ID,
p_wave_header_rec.BACKORDERS_FLAG,
p_wave_header_rec.INCLUDE_PLANNED_LINES,
p_wave_header_rec.TASK_PLANNING_FLAG,
p_wave_header_rec.APPEND_DELIVERIES,
p_wave_header_rec.AUTO_CREATE_DELIVERY,
p_wave_header_rec.AUTO_CREATE_DELIVERY_CRITERIA,
p_wave_header_rec.TASK_PRIORITY,
p_wave_header_rec.DEFAULT_STAGE_SUBINVENTORY,
p_wave_header_rec.DEFAULT_STAGE_LOCATOR_ID,
p_wave_header_rec.DEFAULT_ALLOCATION_METHOD,
p_wave_header_rec.ORDER_NAME,
p_wave_header_rec.CUSTOMER,
p_wave_header_rec.ORDER_TYPE,
p_wave_header_rec.CUSTOMER_CLASS,
p_wave_header_rec.SHIP_METHOD,
p_wave_header_rec.CARRIER,
p_wave_header_rec.SHIP_PRIORITY,
p_wave_header_rec.DELIVERY,
p_wave_header_rec.TRIP,
p_wave_header_rec.TRIP_STOP,
p_wave_header_rec.ITEM,
p_wave_header_rec.ITEM_CATEGORY,
p_wave_header_rec.PROJECT_NAME,
p_wave_header_rec.TASK_NAME,
p_wave_header_rec.SCHEDULED_DAYS,
p_wave_header_rec.SCHEDULED_HRS,
p_wave_header_rec.DOCK_APPOINTMENT_DAYS,
p_wave_header_rec.DOCK_APPOINTMENT_HOURS,
p_wave_header_rec.PICK_SLIP_GROUP,
p_wave_header_rec.RELEASE_SEQ_RULE,
p_wave_header_rec.STAGING_SUBINVENTORY,
p_wave_header_rec.STAGING_LOCATOR,
p_wave_header_rec.CROSS_DOCK_CRITERIA,
p_wave_header_rec.PICK_SUBINVENTORY,
p_wave_header_rec.PLANNING_CRITERIA,
p_wave_header_rec.PLANNING_CRITERIA_ID,
fnd_global.conc_request_id,
p_wave_header_rec.WAVE_FIRMED_FLAG);
print_debug('Error in insert wave header API : ' || SQLCODE || ' : ' ||
SQLERRM,
l_debug);
END insert_wave_header;
update_wave_header_status(x_return_status,
p_wave_header_id,
'Released',
p_batch_id);
update_wave_header_status(x_return_status,
p_wave_header_id,
'Released(Warning)',
p_batch_id);
select FROM_ORDER_HEADER_ID,
BACKORDERS_FLAG,
SHIP_PRIORITY,
SHIP_METHOD_CODE,
CUSTOMER_ID,
INVENTORY_ITEM_ID,
ORDER_TYPE_ID,
SHIP_TO_LOCATION_ID,
TRIP_ID,
DELIVERY_ID,
INCLUDE_PLANNED_LINES,
PICK_GROUPING_RULE_ID,
PICK_SEQ_RULE_ID,
AUTO_CREATE_DELIVERY,
TRIP_STOP_ID,
staging_subinventory,
DEFAULT_STAGE_LOCATOR_ID,
PROJECT_ID,
TASK_ID,
ORGANIZATION_ID,
TASK_PLANNING_FLAG,
CATEGORY_SET_ID,
CATEGORY_ID,
AUTO_CREATE_DELIVERY_CRITERIA,
TASK_PRIORITY,
DEFAULT_ALLOCATION_METHOD,
pull_replenishment_flag,
INITIATE_WAVE_PLANNING,
RELEASE_IMMEDIATELY,
pick_subinventory,
cross_dock_criteria,
append_deliveries -- ssk
into p_FROM_ORDER_HEADER_ID,
p_BACKORDERS_FLAG,
p_SHIPMENT_PRIORITY_CODE,
p_SHIP_METHOD_CODE,
p_CUSTOMER_ID,
p_INVENTORY_ITEM_ID,
p_ORDER_TYPE_ID,
p_SHIP_TO_LOCATION_ID,
p_TRIP_ID,
p_DELIVERY_ID,
p_INCLUDE_PLANNED_LINES,
p_PICK_GROUPING_RULE_ID,
p_PICK_SEQ_RULE_ID,
p_AUTO_CREATE_DELIVERY,
p_TRIP_STOP_ID,
p_DEFAULT_STAGE_SUBINVENTORY,
p_DEFAULT_STAGE_LOCATOR_ID,
p_PROJECT_ID,
p_TASK_ID,
p_ORGANIZATION_ID,
p_TASK_PLANNING_FLAG,
p_CATEGORY_SET_ID,
p_CATEGORY_ID,
p_ac_DELIVERY_CRITERIA,
p_TASK_PRIORITY,
p_DEFAULT_ALLOCATION_METHOD,
p_REPLENISHMENT_ONLY,
p_INITIATE_WAVE_PLANNING,
p_RELEASE_IMMEDIATELY,
p_pick_subinventory,
p_crossdock_criteria_name,
p_append_flag -- ssk
from wms_wp_wave_headers_vl
where wave_header_id = p_wave_header_id;
select decode(MO_PICK_CONFIRM_REQUIRED,1,'N',2,'Y')
into new_wave_type.auto_pick_confirm_flag
from mtl_parameters
where organization_id = p_ORGANIZATION_ID;
SELECT NAME,
NVL(ORDER_ID_PRIORITY, -1),
DECODE(ORDER_ID_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(INVOICE_VALUE_PRIORITY, -1),
DECODE(INVOICE_VALUE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(SCHEDULE_DATE_PRIORITY, -1),
DECODE(SCHEDULE_DATE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(SHIPMENT_PRI_PRIORITY, -1),
DECODE(SHIPMENT_PRI_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(TRIP_STOP_DATE_PRIORITY, -1),
DECODE(TRIP_STOP_DATE_SORT, 'A', 'ASC', 'D', 'DESC', '')
FROM WSH_PICK_SEQUENCE_RULES
WHERE PICK_SEQUENCE_RULE_ID = v_psr_id
AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
NVL(END_DATE_ACTIVE, TRUNC(SYSDATE) + 1);
SELECT NVL(ORDER_NUMBER_FLAG, 'N')
FROM WSH_PICK_GROUPING_RULES
WHERE PICK_GROUPING_RULE_ID = v_pgr_id
AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
NVL(END_DATE_ACTIVE, TRUNC(SYSDATE) + 1);
select planning_criteria_id
into p_planning_criteria_id
from wms_wp_wave_headers_vl
where wave_header_id = p_wave_header_id;
UPDATE wms_wp_wave_lines
SET message = 'This line has been Firmed in Wave ' ||
p_wave_header_id,
remove_from_Wave_flag = 'Y'
WHERE delivery_detail_id IN
(SELECT wwl.delivery_detail_id
FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd... FIXED
WHERE wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and nvl(remove_from_wave_flag, 'N') <> 'Y')
and wave_header_id <> p_wave_header_id;
update wms_wp_wave_headers_vl
set wave_firmed_flag = 'Y'
where wave_header_id = p_wave_header_id;
update_wave_header_status(x_return_status,
p_wave_header_id,
'Released',
p_batch_id);
update_wave_header_status(x_return_status,
p_wave_header_id,
'Released(Warning)',
p_batch_id);
delete_wave_header(p_wave_header_id);
SELECT NVL(PRINT_PICK_SLIP_MODE, 'E'),
NVL(AUTOCREATE_DEL_ORDERS_FLAG, 'Y'),
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
PICK_SEQUENCE_RULE_ID,
PICK_GROUPING_RULE_ID
FROM WSH_SHIPPING_PARAMETERS
WHERE ORGANIZATION_ID = org_id;
SELECT restrictions.PARAMETER_ID,
restrictions.OPERATOR_MEANING,
restrictions.OPERAND_VALUE
FROM WMS_WP_ADVANCED_CRITERIA restrictions,
wms_wp_wave_headers_vl headers
WHERE restrictions.RULE_WAVE_HEADER_ID = headers.wave_header_id
and restrictions.RULE_WAVE_HEADER_ID = P_WAVE_HEADER_ID
AND PARAMETER_ID IN (6002, 6003, 6004, 6005, 6006);
SELECT restrictions.PARAMETER_ID,
restrictions.OPERATOR_MEANING,
restrictions.OPERAND_VALUE
FROM WMS_WP_ADVANCED_CRITERIA restrictions,
wms_wp_wave_headers_vl headers
WHERE restrictions.RULE_WAVE_HEADER_ID = headers.wave_header_id
and restrictions.RULE_WAVE_HEADER_ID = P_WAVE_HEADER_ID
AND PARAMETER_ID = 6001;
select advanced_criteria, nvl(wave_firmed_flag, 'N')
into v_advanced_sql, l_firmed_flag
from wms_wp_wave_headers_vl
where wave_header_id = p_wave_header_id;
--12371394 put the get_lines call and the insert to wwl inside a loop
l_done_flag := FND_API.G_FALSE;
insert into wms_wp_wave_lines
(WAVE_HEADER_ID,
WAVE_LINE_ID,
WAVE_LINE_SOURCE,
WAVE_LINE_STATUS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DELIVERY_DETAIL_ID,
ORGANIZATION_ID,
DEMAND_SOURCE_HEADER_ID,
crossdock_uom)--11775489 this column added
values
(p_wave_header_id,
WMS_WP_WAVE_LINES_S.NEXTVAL,
'OE',
'Created',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
l_delivery_Detail_id_tb(i),
l_organization_id_tb(i),
inv_salesorder.get_salesorder_for_oeheader(l_source_header_id_tb(i)), --???????? Demand Source Header Id
l_requested_quantity_uom_tb(i));--11775489 this column added
select sum(wdd.net_weight)
into l_count
from wms_wp_wave_lines wwl, wsh_delivery_Details wdd --11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
select sum(wdd.volume)
into l_count
from wms_wp_wave_lines wwl, wsh_delivery_details wdd--11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
select sum(outstanding_order_value(wdd.source_header_id, wdd.source_line_id))
into l_count
from wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd... FIXED
where wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
select count(distinct wda.delivery_id)
into l_count
from wms_wp_wave_lines wwl, wsh_delivery_assignments wda, wsh_delivery_details wdd --11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and wda.delivery_detail_id = wdd.delivery_detail_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
select count(distinct wdd.inventory_item_id)
into l_count
from wms_wp_wave_lines wwl, wsh_delivery_Details wdd --11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id in
(select wave_line_id from
(select x.wave_line_id, x.Cum_total_weight
from
(
select wl.wave_line_id , SUM(wdd.net_weight) OVER(ORDER BY wave_line_id) Cum_total_weight from wms_wp_wave_lines wl, wsh_delivery_details wdd
where wl.wave_header_id = :wave_header_id and wdd.delivery_detail_id = wwl.delivery_detail_id and wdd.organization_id = wwl.organization_id
) x
where Cum_total_weight ' ||
C_REC.OPERATOR_MEANING ||
C_REC.OPERAND_VALUE || '))'
using p_wave_header_id;
print_debug('No of Lines deleted based on condition Total Weight is ' ||
SQL%ROWCOUNT,
l_debug);
EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id in
(select wave_line_id from
(select x.wave_line_id, x.Cum_total_volume
from
(
select wl.wave_line_id , SUM(wdd.volume) OVER(ORDER BY wave_line_id) Cum_total_volume from wms_wp_wave_lines wl, wsh_delivery_details wdd
where wl.wave_header_id = :wave_header_id and wwl.delivery_detail_id = wdd.delivery_detail_id and wdd.organization_id = wwl.organization_id
) x
where Cum_total_volume ' ||
C_REC.OPERATOR_MEANING ||
C_REC.OPERAND_VALUE || '))'
using p_wave_header_id;
print_debug('No of Lines deleted based on condition Total Volume is ' ||
SQL%ROWCOUNT,
l_debug);
EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id in
(select wave_line_id from
(select x.wave_line_id, x.Cum_value
from
(
select wl.wave_line_id , OUTSTANDING_ORDER_VALUE(wdd.source_header_id, wdd.source_line_id) OVER(ORDER BY wave_line_id) Cum_value from wms_wp_wave_lines wl, wsh_delivery_details
where wave_header_id = :wave_header_id and wwl.delivery_detail_id = wdd.delivery_detail_id and wdd.organization_id = wwl.organization_id
) x
where Cum_value' ||
C_REC.OPERATOR_MEANING ||
C_REC.OPERAND_VALUE || '))'
using p_wave_header_id;
print_debug('No of Lines deleted based on condition Total Value is ' ||
SQL%ROWCOUNT,
l_debug);
EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id in
(select wave_line_id from wms_wp_wave_lines wwl, wsh_delivery_assignments wda, wsh_delivery_details wdd
where wwl.delivery_detail_id = wdd.delivery_detail_id and wwl.organization_id = wdd.organization_id and wdd.delivery_detail_id = wda.delivery_detail_id and wda.delivery_id not in
(
select delivery_id from
(
select min(wwwl.WAVE_LINE_ID) min_line_id, wnd.delivery_id
from WMS_WP_WAVE_LINES wwwl, wsh_delivery_details wdd,
WSH_NEW_DELIVERIES wnd, WSH_DELIVERY_ASSIGNMENTS WDA
where wave_header_id = :p_wave_header_id
and wnd.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND wwwl.delivery_Detail_id = WDD.DELIVERY_DETAIL_ID
and wdd.organization_id = wwwl.organization_id
AND wnd.delivery_id IS NOT null
group by wnd.delivery_id
order by min_line_id)' ||
' where rownum ' || C_REC.OPERATOR_MEANING ||
C_REC.OPERAND_VALUE || '))' ||
' and wave_header_id = :wave_header_id and delivery_id is not null '
using p_wave_header_id, p_wave_header_id;
print_debug('No of Lines deleted based on condition Delivery Count is ' ||
SQL%ROWCOUNT,
l_debug);
EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id IN
(SELECT wwl.wave_line_id FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
WHERE wwl.organization_id = wdd.organization_id AND wwl.delivery_detail_id = wdd.delivery_detail_id AND wdd.inventory_item_id not in
(
select inventory_item_id from
(
select min(wwwl.WAVE_LINE_ID) min_line_id, wdd.inventory_item_id
from WMS_WP_WAVE_LINES wwwl, wsh_delivery_details wdd
where wwwl.wave_header_id = :wave_header_id and wwwl.delivery_detail_id = wdd.delivery_detail_id and wdd.organization_id = wwwl.organization_id
group by wdd.inventory_item_id
order by min_line_id) ' ||
' where rownum ' || C_REC.OPERATOR_MEANING ||
C_REC.OPERAND_VALUE || '))' ||
' and wave_header_id = :wave_header_id'
using p_wave_header_id, p_wave_header_id;
print_debug('No of Lines deleted based on condition Item Count is ' ||
SQL%ROWCOUNT,
l_debug);
select count(1)
into l_count
from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_header_id = p_wave_header_id;
v_wave_sql := 'delete from wms_wp_wave_lines where rownum ' ||
C_REC1.OPERATOR_MEANING || C_REC1.OPERAND_VALUE ||
' and wave_header_id = ' || p_wave_header_id;
EXECUTE IMMEDIATE 'delete from wms_wp_wave_lines where wave_line_id not in
(
select wave_line_id from wms_wp_wave_lines where wave_header_id = :wave_header_id and rownum' ||
C_REC1.OPERATOR_MEANING ||
C_REC1.OPERAND_VALUE || ' )' ||
' and wave_header_id = :wave_header_id'
using p_wave_header_id, p_wave_header_id;
print_debug('No of Lines deleted based on condition Line Count is ' ||
SQL%ROWCOUNT,
l_debug);
UPDATE wms_wp_wave_lines
SET message = 'This line has been Firmed in Wave ' ||
p_wave_header_id,
remove_from_Wave_flag = 'Y'
WHERE delivery_detail_id IN
(SELECT wwl.delivery_detail_id --11775489 get from wdd... FIXED
FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
WHERE wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and nvl(remove_from_wave_flag, 'N') <> 'Y')
and wave_header_id <> p_wave_header_id;
DELETE FROM wms_wp_wave_lines
WHERE DELIVERY_DETAIL_ID = l_custom_line_tbl(i).DELIVERY_DETAIL_ID
AND l_custom_line_action_tbl(i) = 'REMOVE'
AND wave_header_id = p_wave_header_id;
print_debug('Deleted ' || SQL%ROWCOUNT ||
' lines from wms_wp_wave_lines using custom API returned lines',
l_debug);
SELECT released_status, Nvl(replenishment_status, 'C')
INTO l_released_status, l_replenishment_status
FROM wsh_delivery_details
WHERE delivery_detail_id = l_custom_line_tbl(i)
.delivery_detail_id;
SELECT (1)
INTO a
FROM dual
WHERE l_custom_line_tbl(i)
.delivery_detail_id IN
(SELECT distinct wwl.delivery_detail_id
FROM WMS_WP_WAVE_LINES wwl,
wms_wp_wave_headers_vl wwh
WHERE wwl.wave_header_id = p_wave_header_id -- Bug 13448469 (Replaced 367 with p_wave_header_id)
OR (wwl.wave_header_id = wwh.wave_header_id and
nvl(wwh.wave_firmed_flag, 'N') = 'Y' and
wwh.wave_status <> 'Cancelled' and
nvl(wwl.remove_from_wave_flag, 'N') <> 'Y'));
print_debug('Not inserting detail line ' ||
l_custom_line_tbl(i)
.delivery_detail_id ||
' returned by custom API as line already added to the current wave or it is firmed in other wave ',
l_debug);
l_custom_line_tbl.delete(i);
l_custom_line_action_tbl.delete(i);
' from custom API will be inserted into lines table',
l_debug);
print_debug('Not inserting detail line ' ||
l_custom_line_tbl(i)
.delivery_detail_id ||
' returned by custom API as replenishment is not completed yet',
l_debug);
l_custom_line_tbl.delete(i);
l_custom_line_action_tbl.delete(i);
l_custom_line_tbl.delete(i);
l_custom_line_action_tbl.delete(i);
INSERT INTO wms_wp_wave_lines VALUES l_custom_line_tbl (i);
print_debug('Inserted ' || SQL%ROWCOUNT ||
' detail lines returned by custom API into wms_wp_wave_lines ',
l_debug);
SELECT PARAMETER_ID
FROM WMS_WP_ADVANCED_CRITERIA
WHERE RULE_WAVE_HEADER_ID = V_WAVE_HEADER_ID
AND PARAMETER_ID IN
(1006, 2001, 2002, 3002, 5001, 5002, 5003, 5004, 5005, 5006, 3001, 1011, 1004, 2006, 2004, 2005, 4002);
SELECT enforce_ship_set_and_smc
INTO g_enforce_ship_set_and_smc
FROM WSH_SHIPPING_PARAMETERS WSP
WHERE WSP.ORGANIZATION_ID = p_organization_id;
g_Unreleased_SQL := 'SELECT distinct WDD.SOURCE_CODE,' ||
' WDD.SOURCE_HEADER_ID,' ||
' WDD.SOURCE_LINE_ID,' ||
' WDD.SOURCE_HEADER_NUMBER,' ||
' WDD.SOURCE_LINE_NUMBER,' ||
' WDD.SOURCE_HEADER_TYPE_NAME,' ||
' WDD.SOURCE_HEADER_TYPE_ID,' ||
' WDD.SOURCE_DOCUMENT_TYPE_ID,' ||
' WDD.DELIVERY_DETAIL_ID,' ||
' WDD.RELEASED_STATUS,' ||
' WDD.ORGANIZATION_ID,' ||
' WDD.INVENTORY_ITEM_ID,' ||
' WDD.REQUESTED_QUANTITY,' ||
' WDD.REQUESTED_QUANTITY_UOM,' ||
' WDD.MOVE_ORDER_LINE_ID,' ||
' WDD.SHIP_MODEL_COMPLETE_FLAG,' ||
' WDD.TOP_MODEL_LINE_ID,' ||
' WDD.SHIP_FROM_LOCATION_ID,' ||
' WDD.SHIP_TO_LOCATION_ID,' ||
' WDD.SHIP_METHOD_CODE,' ||
' WDD.SHIPMENT_PRIORITY_CODE,' ||
' WDD.SHIP_SET_ID,' ||
' WDD.DATE_SCHEDULED,' ||
' WTS.PLANNED_DEPARTURE_DATE,' ||
' WDA.DELIVERY_ID,' ||
' WDD.CUSTOMER_ID,' ||
' WDD.CARRIER_ID,' ||
' WDD.PREFERRED_GRADE,' ||
' WDD.SRC_REQUESTED_QUANTITY2,' ||
' WDD.SRC_REQUESTED_QUANTITY_UOM2,' ||
' WDD.PROJECT_ID,' || ' WDD.TASK_ID,' ||
' WDD.SUBINVENTORY,' ||
' WDD.WEIGHT_UOM_CODE,' ||
' WDD.NET_WEIGHT,' || --????? Is it Net Weight or Gross Weight
' WDD.VOLUME_UOM_CODE,' ||
' WDD.VOLUME '; -- Ajith Changed Need to Check
',(SELECT SUM(WDD1.SRC_REQUESTED_QUANTITY) QUANTITY, WDD1.SOURCE_LINE_ID FROM WSH_DELIVERY_DETAILS WDD1
GROUP BY WDD1.SOURCE_LINE_ID) SALES_ORDER_LINE ';
',(SELECT COUNT(distinct SOURCE_HEADER_NUMBER) ORDER_COUNT , WDE.DELIVERY_ID DELIVERY_ID FROM WSH_DELIVERY_DETAILS WDD ,WSH_NEW_DELIVERIES WDE, WSH_DELIVERY_ASSIGNMENTS WDA
WHERE WDE.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
GROUP BY WDE.DELIVERY_ID) DELIVERY ';
' select 1 FROM wsh_delivery_details wdd1 where wdd1.source_header_number = wdd.source_header_number GROUP BY SOURCE_HEADER_NUMBER HAVING Count(SOURCE_HEADER_NUMBER)=1)';
',(select cust_Acct.customer_class_code customer_class_code,
cust_acct.cust_account_id cust_account_id from
hz_parties PARTY
, hz_cust_accounts CUST_ACCT
where party.party_id = cust_acct.party_id
and cust_acct.status = ''A'') CUSTOMER_CLASS ';
'(SELECT distinct wwl.delivery_detail_id FROM WMS_WP_WAVE_LINES wwl ,wms_wp_wave_headers_vl wwh, wsh_delivery_details wdd
where wwl.wave_header_id=wwh.wave_header_id and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id and (nvl(wwh.wave_firmed_flag,''N'') = ''Y'' and wwh.wave_status <> ''Cancelled'' and nvl(wwl.remove_from_wave_flag,''N'') <>''Y''))';
'(select distinct delivery_detail_id from wms_wp_Wave_lines where wave_header_id=:X_wave_header_id)';
g_cond_SQL := g_Cond_SQL || ' AND WDD.CUSTOMER_ID IN (select cust_acct.cust_account_id
from hz_parties PARTY
, hz_cust_accounts CUST_ACCT
where party.party_id = cust_acct.party_id
and cust_acct.status = ''A''' ||
' AND cust_Acct.customer_class_code = :X_CUSTOMER_CLASS_ID) ' || '';
g_cond_SQL := g_Cond_SQL || ' and wdd.inventory_item_id in (select distinct inventory_item_id
from mtl_item_categories mic
where mic.category_id =:X_category_id) ' || '';
release_table.delete;
l_source_code_tb.delete;
l_source_header_id_tb.delete;
l_source_line_id_tb.delete;
l_source_header_number_tb.delete;
l_source_line_number_tb.delete;
l_source_header_type_id_tb.delete;
l_source_document_type_id_tb.delete;
l_delivery_Detail_id_tb.delete;
l_organization_id_tb.delete;
l_item_id_tb.delete;
l_requested_quantity_tb.delete;
l_requested_quantity_uom_tb.delete;
l_requested_quantity2_tb.delete;
l_requested_quantity_uom2_tb.delete;
l_demand_header_id_tb.delete;
l_net_weight_tb.delete;
l_volume_tb.delete;
l_net_value.delete;
Insert_RL_Row(g_return_status);
print_debug('Error occurred in Insert_RL_Row', l_debug);
PROCEDURE Insert_RL_Row(x_api_status OUT NOCOPY VARCHAR2) IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
print_debug('Insert into table', l_debug);
print_debug('Unexpected error in Insert_RL_Row', l_debug);
END Insert_RL_Row;
select wwl.wave_header_id,
wwl.wave_line_id,
wdd.source_header_number,
wdd.source_line_id,
wwl.delivery_detail_id,
wwl.organization_id,
wdd.inventory_item_id,
wdd.requested_quantity
from wms_wp_wave_lines wwl, wsh_delivery_details wdd --11775489 get from wdd... FIXED...check performance
where wwl.wave_header_id = p_wave_header_id
and wdd.inventory_item_id = p_item_id
and wwl.organization_id = p_org_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and nvl(wwl.remove_from_wave_flag, 'N') = 'N'
ORDER BY wave_header_id, inventory_item_id, wave_line_id;
SELECT DISTINCT wdd.inventory_item_id, wwl.organization_id
FROM wms_wp_wave_lines wwl, wsh_delivery_Details wdd --11775489 get from wdd... FIXED
WHERE wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wdd.organization_id = wwl.organization_id;
select picking_subinventory
into l_pick_subinventory
from wms_wp_planning_criteria_vl wwp, wms_wp_wave_headers_vl wwh
where wave_header_id = p_wave_header_id
and wwh.planning_Criteria_id = wwp.planning_Criteria_id;
SELECT nvl(sum(reservation_quantity), 0)
INTO l_reserved_qty
FROM mtl_Reservations
WHERE demand_source_line_id = c_rec.SOURCE_LINE_ID
and organization_id = L_ORG_ID
and inventory_item_id = L_ITEM_ID
and (subinventory_code = l_pick_subinventory or
l_pick_subinventory is null);
SELECT Nvl(SUM(wdd.requested_quantity), 0)
INTO l_other_wdd_qty
FROM wsh_delivery_details wdd
WHERE wdd.organization_id = l_org_id
AND wdd.inventory_item_id = l_item_id
and wdd.released_status in ('B', 'E', 'F', 'K', 'R','S') --11822962 only consider wdds where requested material is against a possible reservation
and wdd.delivery_Detail_id not in
(select wwl.delivery_detail_id
from wms_wp_wave_lines wwl, wsh_delivery_Details wdd2 --11775489 get from wdd... FIXED
where wwl.wave_header_id = p_wave_header_id
and nvl(wwl.remove_from_wave_flag, 'N') <> 'Y'
and wdd2.source_line_id = wdd.source_line_id
and wwl.delivery_detail_id = wdd2.delivery_detail_id
and wdd2.organization_id = wwl.organization_id);
update wms_wp_wave_lines
set planned_fill_rate = X_CONSOL_LINES_TBL(m).planned_fill_rate
where wave_line_id = X_CONSOL_LINES_TBL(m).wave_line_id;
select wwl.wave_line_id
from wms_wp_wave_lines wwl
where wave_header_id = p_wave_header_id
and nvl(remove_from_wave_flag, 'N') <> 'Y'; --11775489 added filter
update_line_actual_fill_rate(l_line.wave_line_id);
select nvl(net_weight, 0) net_weight, weight_uom_code
from wsh_delivery_details
where source_header_id = p_source_header_id;
SELECT uom_code
into v_weight_uom
FROM mtl_units_of_measure_vl
WHERE uom_class = (SELECT weight_uom_class
FROM wsh_shipping_parameters
WHERE organization_id = p_orgid)
AND base_uom_flag = 'Y';
select nvl(volume, 0) volume, volume_uom_code
from wsh_delivery_details
where source_header_id = p_source_header_id;
SELECT uom_code
into v_volume_uom
FROM mtl_units_of_measure_vl
WHERE uom_class = (SELECT volume_uom_class
FROM wsh_shipping_parameters
WHERE organization_id = p_orgid)
AND base_uom_flag = 'Y';
select nvl(net_weight, 0) net_weight,
weight_uom_code,
nvl(volume, 0) volume,
volume_uom_code
from wsh_delivery_details
where delivery_detail_id in
(SELECT wwl.delivery_detail_id --11775489 get from wdd... FIXED
FROM wms_wp_wave_lines wwl, wsh_delivery_details wdd
WHERE wwl.wave_header_id = p_wave_header_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and nvl(remove_from_wave_flag, 'N') <> 'Y');
SELECT uom_code
into v_weight_uom
FROM mtl_units_of_measure_vl
WHERE uom_class = (SELECT weight_uom_class
FROM wsh_shipping_parameters
WHERE organization_id = p_orgid)
AND base_uom_flag = 'Y';
SELECT uom_code
into v_volume_uom
FROM mtl_units_of_measure_vl
WHERE uom_class = (SELECT volume_uom_class
FROM wsh_shipping_parameters
WHERE organization_id = p_orgid)
AND base_uom_flag = 'Y';
procedure update_wave_header_status(x_return_status OUT NOCOPY varchar2,
p_wave_header_id in number,
Status in varchar2,
p_batch_id in number default null) IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
print_debug('Update Wave Header Status 2: ' || Status ||'the val of batch'||p_batch_id, l_debug);
update wms_wp_wave_headers_vl
set wave_status = Status,
batch_id = p_batch_id
where wave_header_id = p_wave_header_id;
print_debug('Update Wave Header Status 3: ' || Status ||'the val of batch'||p_batch_id, l_debug);
update wms_wp_wave_headers_vl
set wave_status = Status
where wave_header_id = p_wave_header_id;
print_debug('Error in Update Wave Header Status: ' || SQLCODE ||
' : ' || SQLERRM,
l_debug);
end update_wave_header_status;
PROCEDURE update_task(p_transaction_temp_id IN WMS_WAVE_PLANNING_PVT.transaction_temp_table_type,
p_task_type_id IN WMS_WAVE_PLANNING_PVT.task_type_id_table_type,
p_employee IN VARCHAR2,
p_employee_id IN NUMBER,
p_user_task_type IN VARCHAR2,
p_user_task_type_id IN NUMBER,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_person_resource_id IN NUMBER,
p_person_resource_code IN VARCHAR2,
p_force_employee_change IN BOOLEAN,
p_to_status IN VARCHAR2,
p_to_status_id IN NUMBER,
p_priority_type IN varchar2 DEFAULT 'S', --mitgupta
p_priority IN NUMBER,
p_clear_priority IN VARCHAR2,
x_result OUT NOCOPY WMS_WAVE_PLANNING_PVT.result_table_type,
x_message OUT NOCOPY WMS_WAVE_PLANNING_PVT.message_table_type,
x_task_id OUT NOCOPY WMS_WAVE_PLANNING_PVT.task_id_table_type,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER) IS
l_task_id NUMBER;
g_cannot_update_putaway WMS_WP_TASKS_GTMP.error%TYPE;
g_task_updated WMS_WP_TASKS_GTMP.error%TYPE;
print_debug('XXXX Update Wave Header Status: ', l_debug);
IF g_cannot_update_putaway IS NULL
THEN
fnd_message.set_name ('WMS', 'WMS_CANNOT_UPDATE_PUTAWAY_TASK');
g_cannot_update_putaway := fnd_message.get;
fnd_message.set_name ('WMS', 'WMS_TASK_UPDATED');
g_task_updated := fnd_message.get;
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'X'
WHERE transaction_temp_id = p_transaction_temp_id (i)
AND task_type_id = p_task_type_id (i);
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'E',
error =
DECODE (task_type_id,
2, g_cannot_update_putaway
)
WHERE RESULT = 'X' AND task_type_id IN (2);
g_task_updated := fnd_message.get;
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'X'
WHERE transaction_temp_id = p_transaction_temp_id(i)
AND task_type_id = p_task_type_id(i);*/
SELECT transaction_temp_id, task_type_id, status BULK COLLECT
INTO l_transaction_temp_ids, l_task_type_ids, l_statuses
FROM wms_wp_tasks_gtmp
WHERE RESULT = 'X'
AND NOT ((status_id = 8 AND p_to_status_id IN (1, 2)) -- Unreleased to pending or queued
OR (status_id = 1 AND p_to_status_id IN (2, 8)) -- Pending to queued or unreleased
OR (status_id = 2 AND p_to_status_id IN (1, 8)) -- Queued to pending or unreleased
OR (status_id = 9 AND p_to_status_id IN (1, 8)) -- R12:Active to Pending or unreleased
OR (status_id = 3 AND p_to_status_id IN (1, 8)) -- R12:Dispatched to Pending or unreleased
OR (status_id = p_to_status_id)); -- No Status Change
fnd_message.set_name('WMS', 'WMS_CANNOT_UPDATE_STATUS');
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'E', error = l_messages(i)
WHERE transaction_temp_id = l_transaction_temp_ids(i)
AND task_type_id = l_task_type_ids(i);
SELECT wwtt.transaction_temp_id, wwtt.task_type_id, wwtt.status BULK COLLECT
INTO l_transaction_temp_ids, l_task_type_ids, l_statuses
FROM wms_wp_tasks_gtmp wwtt,
mtl_material_transactions_temp mmtt,
WMS_DISPATCHED_TASKS wdt
WHERE wwtt.transaction_temp_id = wdt.transaction_temp_id
AND wwtt.transaction_temp_id = mmtt.transaction_temp_id
AND wwtt.RESULT = 'X'
AND wwtt.status_id = 3
AND EXISTS
(SELECT 1
FROM WMS_DISPATCHED_TASKS wdt2
WHERE wdt2.person_id = wwtt.person_id
AND wdt2.status = 9
AND wdt2.task_method IS NOT NULL
AND wdt2.transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp mmtt1
WHERE DECODE(wdt.TASK_METHOD,
'CARTON',
mmtt1.cartonization_id,
'PICK_SLIP',
mmtt1.pick_slip_number,
'DISCRETE',
mmtt1.pick_slip_number,
mmtt1.transaction_source_id) =
DECODE(wdt.TASK_METHOD,
'CARTON',
mmtt.cartonization_id,
'PICK_SLIP',
mmtt.pick_slip_number,
'DISCRETE',
mmtt.pick_slip_number,
mmtt.transaction_source_id)));
fnd_message.set_name('WMS', 'WMS_GROUP_TASKS_CANNOT_UPDATE');
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'E',
error = 'This group of tasks is currently being worked, cannot change status'
WHERE transaction_temp_id = l_transaction_temp_ids(i)
AND task_type_id = l_task_type_ids(i);
SELECT transaction_temp_id, task_type_id, status BULK COLLECT
INTO l_transaction_temp_ids, l_task_type_ids, l_statuses
FROM wms_wp_tasks_gtmp wwtt
WHERE RESULT = 'X'
AND status_id = 9
AND EXISTS
(SELECT 1
FROM MTL_MOBILE_LOGIN_HIST MMLH, WMS_DISPATCHED_TASKS WDT
WHERE WDT.TRANSACTION_TEMP_ID = WWTT.TRANSACTION_TEMP_ID
AND MMLH.USER_ID = WDT.LAST_UPDATED_BY
AND MMLH.LOGOFF_DATE IS NULL
AND MMLH.EVENT_MESSAGE IS NULL);
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'E',
error = 'This task is currently being worked, cannot change status'
WHERE transaction_temp_id = l_transaction_temp_ids(i)
AND task_type_id = l_task_type_ids(i);
fnd_message.set_name('WMS', 'WMS_CANNOT_UPDATE_EMPLOYEE');
UPDATE wms_wp_tasks_gtmp wwtt
SET RESULT = 'E', error = l_message
WHERE RESULT = 'X'
AND NOT EXISTS
(SELECT 1
FROM bom_std_op_resources bsor, bom_resource_employees bre
WHERE wwtt.user_task_type_id = bsor.standard_operation_id
AND bsor.resource_id = bre.resource_id
AND bre.person_id = p_employee_id);
SELECT transaction_temp_id BULK COLLECT
INTO l_transaction_temp_ids_temp
FROM wms_wp_tasks_gtmp
WHERE RESULT = 'X';
fnd_message.set_name('WMS', 'WMS_CANNOT_UPDATE_EMPLOYEE');
UPDATE wms_wp_tasks_gtmp wwtt
SET RESULT = 'E', error = l_message
WHERE transaction_temp_id = l_transaction_temp_ids_temp(i)
AND NOT EXISTS
(SELECT 1
FROM wms_device_assignment_temp
WHERE device_id = l_device_id
AND employee_id = p_employee_id);
UPDATE wms_wp_tasks_gtmp wwtt
SET person_resource_id = (SELECT bre.resource_id
FROM bom_std_op_resources bsor,
bom_resource_employees bre
WHERE wwtt.user_task_type_id =
bsor.standard_operation_id
AND bsor.resource_id = bre.resource_id
AND bre.person_id = wwtt.person_id
AND ROWNUM < 2)
WHERE RESULT = 'X';
UPDATE wms_wp_tasks_gtmp wwtt
SET RESULT = 'E', error = l_message
WHERE RESULT = 'X'
AND (status_id NOT IN (1, 2, 3, 8, 9) AND
p_to_status_id IN (1, 8))
RETURNING transaction_temp_id, task_type_id, status BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses;
UPDATE wms_wp_tasks_gtmp wwtt
SET RESULT = 'E', error = l_message
WHERE RESULT = 'X'
AND status_id NOT IN (1, 8)
RETURNING transaction_temp_id, task_type_id, status BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses;
fnd_message.set_name('WMS', 'WMS_CANNOT_UPDATE_USER_TASK_TYPE');
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'E', error = l_messages(i)
WHERE transaction_temp_id = l_transaction_temp_ids(i)
AND task_type_id = l_task_type_ids(i);
UPDATE wms_wp_tasks_gtmp wwtt
SET RESULT = 'E', error = l_message
WHERE RESULT = 'X'
AND (status_id NOT IN (1, 2, 3, 8, 9) AND
p_to_status_id IN (1, 8))
RETURNING transaction_temp_id, task_type_id, status BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses;
UPDATE wms_wp_tasks_gtmp wwtt
SET RESULT = 'E', error = l_message
WHERE RESULT = 'X'
AND status_id NOT IN (1, 2, 8)
RETURNING transaction_temp_id, task_type_id, status BULK COLLECT INTO l_transaction_temp_ids, l_task_type_ids, l_statuses;
fnd_message.set_name('WMS', 'WMS_CANNOT_UPDATE_PRIORITY');
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'E', error = l_messages(i)
WHERE transaction_temp_id = l_transaction_temp_ids(i)
AND task_type_id = l_task_type_ids(i);
UPDATE wms_wp_tasks_gtmp
SET task_id = NVL(task_id, wms_dispatched_tasks_s.NEXTVAL),
status = p_to_status,
status_id = p_to_status_id,
priority = DECODE(p_clear_priority,
'Y',
NULL,
DECODE(p_priority_type,
'I',
NVL(priority, 0) + p_priority, -- R12: Increment priority
'D',
DECODE(SIGN(NVL(priority, 0) -
p_priority), -- R12: Decrement priority
-1,
0,
+1,
NVL(priority, 0) - p_priority,
0,
0),
'S',
NVL(p_priority, priority), -- R12: Set priority
priority)),
person = p_employee,
person_id = p_employee_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
person_resource_code = p_person_resource_code,
person_resource_id = p_person_resource_id,
RESULT = 'S',
error = g_task_updated,
is_modified = 'Y'
WHERE RESULT = 'X';
UPDATE wms_wp_tasks_gtmp
SET task_id = DECODE(p_to_status_id,
1,
NULL,
8,
NULL,
task_id),
status = NVL(p_to_status, status),
status_id = NVL(p_to_status_id, status_id),
user_task_type = NVL(p_user_task_type, user_task_type),
user_task_type_id = NVL(p_user_task_type_id, user_task_type_id),
priority = DECODE(p_clear_priority,
'Y',
NULL,
DECODE(p_priority_type,
'I',
NVL(priority, 0) + p_priority, -- R12: Increment priority
'D',
DECODE(SIGN(NVL(priority, 0) -
p_priority), -- R12: Decrement priority
-1,
0,
+1,
NVL(priority, 0) - p_priority,
0,
0),
'S',
NVL(p_priority, priority), -- R12: Set Constant priority value
priority)),
person = DECODE(p_to_status_id,
1,
NULL,
8,
NULL,
person),
person_id = DECODE(p_to_status_id,
1,
NULL,
8,
NULL,
person_id),
effective_start_date = DECODE(p_to_status_id,
1,
NULL,
8,
NULL,
effective_start_date),
effective_end_date = DECODE(p_to_status_id,
1,
NULL,
8,
NULL,
effective_end_date),
person_resource_code = DECODE(p_to_status_id,
1,
NULL,
8,
NULL,
person_resource_code),
person_resource_id = DECODE(p_person_resource_id,
1,
NULL,
8,
NULL,
person_resource_id),
RESULT = 'S',
error = g_task_updated,
is_modified = 'Y'
WHERE RESULT = 'X';
UPDATE wms_wp_tasks_gtmp
SET RESULT = RESULT
WHERE transaction_temp_id = p_transaction_temp_id(i)
AND task_type_id = p_task_type_id(i)
RETURNING task_id, RESULT, error BULK COLLECT INTO x_task_id, x_result, x_message;
END update_task;
TYPE user_task_type_id IS TABLE OF wms_waveplan_tasks_temp.user_task_type_id%TYPE -- R12: Update User Task Type Id
INDEX BY BINARY_INTEGER;
l_update_date DATE;
SELECT transaction_temp_id,
task_type_id,
mmtt_last_updated_by,
mmtt_last_update_date,
wdt_last_updated_by,
wdt_last_update_date,
person_id,
person_id_original,
person_resource_id,
effective_start_date,
effective_end_date,
status_id,
status_id_original,
priority,
priority_original,
user_task_type_id,
num_of_child_tasks
FROM wms_wp_tasks_gtmp wwtt
WHERE wwtt.is_modified = 'Y';
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE parent_line_id = trx_temp_id
AND transaction_temp_id <> trx_temp_id
FOR UPDATE nowait;
UPDATE wms_wp_tasks_gtmp
SET is_modified = 'N', RESULT = 'S', error = g_plan_cancelled
WHERE transaction_temp_id = rec_wwtt.transaction_temp_id;
SELECT mmtt.transaction_temp_id
INTO l_transaction_temp_id_table(i)
FROM mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = rec_wwtt.transaction_temp_id
AND mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
AND mmtt.wms_task_type = wdt.task_type(+)
AND DECODE(wdt.status,
NULL,
NVL(mmtt.wms_task_status, 1),
wdt.status) = NVL(rec_wwtt.status_id_original, -1)
AND NVL(mmtt.task_priority, -1) =
NVL(rec_wwtt.priority_original, -1)
AND NVL(wdt.person_id, -1) =
NVL(rec_wwtt.person_id_original, -1)
AND mmtt.last_updated_by = rec_wwtt.mmtt_last_updated_by
AND mmtt.last_update_date = rec_wwtt.mmtt_last_update_date
AND NVL(wdt.last_updated_by, -1) =
NVL(rec_wwtt.wdt_last_updated_by, -1)
AND (wdt.last_update_date = rec_wwtt.wdt_last_update_date OR
(wdt.last_update_date IS NULL AND
rec_wwtt.wdt_last_update_date IS NULL))
FOR UPDATE NOWAIT;
l_user_task_type_id(i) := rec_wwtt.user_task_type_id; -- R12: Update User Task Type Id
UPDATE mtl_material_transactions_temp
SET wms_task_status = DECODE(l_wms_task_status_table(i),
8,
8,
1,
1,
NULL),
task_priority = l_task_priority_table(i),
last_update_date = SYSDATE,
last_updated_by = p_user_id,
last_update_login = p_login_id,
standard_operation_id = l_user_task_type_id(i)
WHERE transaction_temp_id = l_transaction_temp_id_table(i) -- R12: Update User Task Type Id
AND l_task_type_id(i) <> 3;
print_DEBUG('No of records updated are-777 ' || SQL%ROWCOUNT,
l_debug);
DELETE wms_dispatched_tasks wdt
WHERE wdt.status IN (2, 3, 9) -- R12: Delete the Active or Dispatched tasks which were updated to pending/Unreleased
AND wdt.transaction_temp_id IN
(SELECT transaction_temp_id
FROM wms_wp_tasks_gtmp wwtt
WHERE wwtt.status_id IN (1, 8)
AND wwtt.is_modified = 'Y');
print_DEBUG('No of records deleted are-555 ' || SQL%ROWCOUNT,
l_debug);
l_update_date := SYSDATE;
print_DEBUG('inserting into WDT ' || x_save_count, l_debug);
INSERT INTO wms_dispatched_tasks
(task_id,
transaction_temp_id,
organization_id,
user_task_type,
person_id,
effective_start_date,
effective_end_date,
person_resource_id,
machine_resource_id,
status,
dispatched_time,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
task_type,
priority,
move_order_line_id,
operation_plan_id,
transfer_lpn_id)
(SELECT wwtt.task_id,
wwtt.transaction_temp_id,
wwtt.organization_id,
NVL(wwtt.user_task_type_id, 0),
wwtt.person_id,
sysdate,
sysdate,
wwtt.person_resource_id,
NULL,
2, -- Queued
NULL,
l_update_date,
p_user_id,
l_update_date,
p_user_id,
p_login_id,
wwtt.task_type_id,
wwtt.priority,
wwtt.move_order_line_id,
wwtt.operation_plan_id,
wwtt.to_lpn_id
FROM wms_wp_tasks_gtmp wwtt
WHERE wwtt.status_id = 2
AND wwtt.status_id_original IN (1, 8)
AND wwtt.is_modified = 'Y'
AND NOT EXISTS
(SELECT 1
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = wwtt.transaction_temp_id));
print_DEBUG('No of records inserted are-444 ' || SQL%ROWCOUNT,
l_debug);
UPDATE wms_dispatched_tasks
SET person_id = l_person_id_table(i),
person_resource_id = l_person_resource_id_table(i),
effective_start_date = l_effective_start_date_table(i),
effective_end_date = l_effective_end_date_table(i),
priority = l_task_priority_table(i),
last_update_date = l_update_date,
last_updated_by = p_user_id,
last_update_login = p_login_id
WHERE transaction_temp_id = l_transaction_temp_id_table(i);
print_DEBUG('No of records updated are-333 ' || SQL%ROWCOUNT,
l_debug);
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'S',
error = g_task_saved,
is_modified = 'N',
person_id_original = l_person_id_table(i),
status_id_original = l_wms_task_status_table(i),
priority_original = l_task_priority_table(i),
mmtt_last_updated_by = p_user_id,
mmtt_last_update_date = l_update_date,
wdt_last_updated_by = DECODE(l_wms_task_status_table(i),
1,
NULL,
8,
NULL,
p_user_id),
wdt_last_update_date = TO_DATE(DECODE(l_wms_task_status_table(i),
1,
NULL,
8,
NULL,
TO_CHAR(l_update_date,
'DD-MON-YY HH24:MI:SS')),
'DD-MON-YY HH24:MI:SS')
WHERE transaction_temp_id = l_transaction_temp_id_table(i);
print_DEBUG('No of records updated are-222 ' || SQL%ROWCOUNT,
l_debug);
UPDATE wms_wp_tasks_gtmp
SET RESULT = 'E', error = l_error_message
WHERE is_modified = 'Y';
print_DEBUG('No of records updated are-111 ' || SQL%ROWCOUNT,
l_debug);
SELECT wms_task_dispatch_device.get_eligible_device(mmtt.organization_id,
mmtt.subinventory_code,
mmtt.locator_id)
INTO p_device_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_temp_id;
SELECT force_sign_on_flag
INTO l_force_sign_on
FROM wms_devices_b
WHERE device_id = p_device_id;
SELECT COUNT(1), wwtt.transaction_temp_id BULK COLLECT
INTO l_num_of_child_tasks_tbl, l_parent_temp_ids_tbl
FROM wms_wp_tasks_gtmp wwtt, mtl_material_transactions_temp mmtt
WHERE wwtt.transaction_temp_id = mmtt.parent_line_id
AND wwtt.transaction_temp_id <> mmtt.transaction_temp_id
GROUP BY wwtt.transaction_temp_id;
UPDATE wms_wp_tasks_gtmp wwtt
SET wwtt.num_of_child_tasks = l_num_of_child_tasks_tbl(i)
WHERE wwtt.transaction_temp_id = l_parent_temp_ids_tbl(i);
PROCEDURE Update_Completion_Status_CP(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER)
is
--Cursor to find whether the wave lines in Released Status are staged.
cursor c_waves is
select wwh.wave_header_id
from wms_wp_wave_headers_vl wwh,
(select wave_header_id, count(1) count_lines
from wms_wp_wwb_lines_v
where nvl(remove_from_wave_flag, 'N') = 'N'
group by wave_header_id) wwlv,
(select count(1) count_completed_lines, wave_header_id
from wms_wp_wwb_lines_v
where line_progress_id >= 8
and nvl(remove_from_wave_flag, 'N') = 'N'
group by wave_header_id) wwlv1
where wwh.wave_header_id = wwlv.wave_header_id
and wwh.wave_status = 'Released'
and wwlv.count_lines = wwlv1.count_completed_lines
and wwlv.wave_header_id = wwlv1.wave_header_id;
update wms_wp_wave_headers_vl
set wave_status = 'Completed', wave_Completion_time = sysdate -- should we change the column name to Wave Completion Date
where wave_header_id = l_waves.wave_header_id;
update wms_wp_wave_lines
set pick_fill_rate = 100
where wave_header_id = l_waves.wave_header_id;
print_debug('Error in update completion status : ' || SQLCODE ||
' : ' || SQLERRM,
l_debug);
end Update_Completion_Status_CP;
select wwh.wave_header_id
from wms_wp_wave_headers_vl wwh,
(select wave_header_id, count(1) count_lines
from wms_wp_wwb_lines_v
where nvl(remove_from_wave_flag, 'N') = 'N'
group by wave_header_id) wwlv,
(select count(1) count_completed_lines, wave_header_id
from wms_wp_wwb_lines_v
where line_progress_id = 11
and nvl(remove_from_wave_flag, 'N') = 'N'
group by wave_header_id) wwlv1
where wwh.wave_header_id = wwlv.wave_header_id
and wwh.wave_status in ('Completed', 'Released') -- Adding released status if direct ship is done lines will be in shipped directly
and wwlv.count_lines = wwlv1.count_completed_lines
and wwlv.wave_header_id = wwlv1.wave_header_id;
update wms_wp_wave_headers_vl
set wave_status = 'Closed' -- should we change the column name to Wave Completion Date
where wave_header_id = l_wave_header_tbl(k);
select wwl.wave_line_id
from wms_wp_wave_lines wwl, --11775489 added org_id join...
wms_wp_wave_headers_vl wwh,
wsh_delivery_details wdd
where wwh.wave_header_id = wwl.wave_header_id
and (wwh.wave_status = 'Released')
and nvl(wwl.remove_from_wave_flag, 'N') = 'N'
and wdd.delivery_detail_id = wwl.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wdd.released_status in ('B', 'E', 'Y');
update_line_pick_fill_rate(l_line.wave_line_id);
SELECT Sum(Decode(Nvl(wdt.status,0), 4, 1, 0)) loaded,
Count(1) total_tasked
into v_loaded, v_total
FROM wsh_delivery_details wdd, mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
WHERE wdd.delivery_detail_id = p_Delivery_Detail_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdt.transaction_temp_id (+) = mmtt.TRANSACTION_temp_id;
SELECT DISTINCT wwl.wave_header_id,
MIN(nvl(wdab.end_time,
(nvl(wts.planned_arrival_date,
nvl(wnd.earliest_pickup_date,
wdd.date_scheduled))))) date_scheduled
FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
wsh_delivery_details wdd,
(SELECT wave_header_id wave_id
FROM wms_wp_wave_headers_vl
WHERE ((wave_header_id = p_wave) AND
LOWER(wave_status) in ('released', 'completed'))
OR (p_wave IS NULL AND
LOWER(wave_status) in ('released', 'completed'))) wwh,
wsh_delivery_assignments wda,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts,
wms_dock_appointments_b wdab
WHERE wwl.wave_header_id = wwh.wave_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_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.pick_up_stop_id = wts.stop_id(+)
AND wts.stop_id = wdab.trip_stop(+)
AND wdd.organization_id = p_organization_id
and nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y' --11775489 filter out lines no longer in wave
GROUP BY wwl.wave_header_id;
SELECT DISTINCT wts.trip_id,
nvl(wdab.end_time, wts.planned_arrival_date) date_scheduled
FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts,
wms_dock_appointments_b wdab
WHERE wwl.wave_header_id = p_wave_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_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.pick_up_stop_id = wts.stop_id(+)
AND wts.trip_id IS NOT NULL
AND wts.stop_id = wdab.trip_stop(+)
and nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y' --11775489 filter out lines no longer in wave
AND wdd.organization_id = p_organization_id;
SELECT DISTINCT wts.trip_id,
wda.delivery_id,
nvl(wdab.end_time,
(nvl(wts.planned_arrival_date,
wnd.earliest_pickup_date))) date_scheduled
FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts,
wms_dock_appointments_b wdab
WHERE wwl.wave_header_id = p_wave_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND wda.delivery_id = wnd.delivery_id(+)
AND wnd.delivery_id = wdl.delivery_id(+)
AND wdl.pick_up_stop_id = wts.stop_id(+)
AND wts.stop_id = wdab.trip_stop(+)
and nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y' --11775489 filter out lines no longer in wave
AND wdd.organization_id = p_organization_id;
SELECT DISTINCT wts.trip_id,
wda.delivery_id,
shi.source_header_id,
shi.date_scheduled,
wdd.source_header_number
FROM (SELECT wdd.source_header_id,
MIN(nvl(wdab.end_time,
(nvl(wts.planned_arrival_date,
nvl(wnd.earliest_pickup_date,
wdd.date_scheduled))))) date_scheduled
FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts,
wms_dock_appointments_b wdab
WHERE wwl.wave_header_id = p_wave_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_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.pick_up_stop_id = wts.stop_id(+)
AND wts.stop_id = wdab.trip_stop(+)
AND wdd.organization_id = p_organization_id
and nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y' --11775489 filter out lines no longer in wave
GROUP BY wdd.source_header_id) shi,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts
WHERE wdd.source_header_id = shi.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.pick_up_stop_id = wts.stop_id(+)
ORDER BY shi.source_header_id;
SELECT DISTINCT wts.trip_id,
wda.delivery_id,
shi.source_line_id,
shi.date_scheduled,
wdd.source_header_number
FROM (SELECT wdd.source_line_id,
MIN(nvl(wdab.end_time,
(nvl(wts.planned_arrival_date,
nvl(wnd.earliest_pickup_date,
wdd.date_scheduled))))) date_scheduled
FROM wms_wp_wave_lines wwl, --11775489 added org_id join...
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts,
wms_dock_appointments_b wdab
WHERE wwl.wave_header_id = p_wave_id
AND wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_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.pick_up_stop_id = wts.stop_id(+)
AND wts.stop_id = wdab.trip_stop(+)
AND wdd.organization_id = p_organization_id
and nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y' --11775489 filter out lines no longer in wave
GROUP BY wdd.source_line_id) shi,
wms_wp_wave_lines wwl,--11775489 added org_id join...
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts
WHERE wdd.source_line_id = shi.source_line_id
AND wdd.delivery_detail_id = wwl.delivery_detail_id
and wwl.organization_id = wdd.organization_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.pick_up_stop_id = wts.stop_id(+)
and nvl(wwl.remove_from_Wave_flag, 'N') <> 'Y' --11775489 filter out lines no longer in wave
order by shi.source_line_id;
SELECT exception_id
INTO l_exception_id
FROM wms_wp_wave_exceptions_vl
WHERE wave_header_id = l_get_waves.wave_header_id
AND exception_entity = p_exception_entity
AND exception_stage = p_progress_stage
AND LOWER(status) <> 'closed';
SELECT Count(*),
Sum(CASE
WHEN line_progress_id >= l_progress_stage THEN
1
ELSE
0
END)
INTO l_total_lines, l_perfect_lines
FROM wms_wp_wwb_lines_v
WHERE wave_header_id = l_get_waves.wave_header_id
AND organization_id = p_organization_id
and nvl(remove_from_Wave_flag, 'N') <> 'Y';
SELECT exception_id
INTO l_exception_id
FROM wms_wp_wave_exceptions_vl
WHERE wave_header_id = l_get_waves.wave_header_id
AND exception_entity = p_exception_entity
AND exception_stage = p_progress_stage
AND trip_id = l_get_trips.trip_id
AND LOWER(status) <> 'closed';
SELECT Count(*),
Sum(CASE
WHEN line_progress_id >= l_progress_stage THEN
1
ELSE
0
END)
INTO l_total_lines, l_perfect_lines
FROM wms_wp_wwb_lines_v
WHERE wave_header_id = l_get_waves.wave_header_id
AND TRIP = l_get_trips.trip_id
AND organization_id = p_organization_id
and nvl(remove_from_Wave_flag, 'N') <> 'Y';
SELECT exception_id
INTO l_exception_id
FROM wms_wp_wave_exceptions_vl
WHERE wave_header_id = l_get_waves.wave_header_id
AND exception_entity = p_exception_entity
AND exception_stage = p_progress_stage
AND delivery_id = l_get_deliveries.delivery_id
AND LOWER(status) <> 'closed';
SELECT Count(*),
Sum(CASE
WHEN line_progress_id >= l_progress_stage THEN
1
ELSE
0
END)
INTO l_total_lines, l_perfect_lines
FROM wms_wp_wwb_lines_v
WHERE wave_header_id = l_get_waves.wave_header_id
AND delivery_id = l_get_deliveries.delivery_id
AND organization_id = p_organization_id
and nvl(remove_from_Wave_flag, 'N') <> 'Y';
SELECT exception_id
INTO l_exception_id
FROM wms_wp_wave_exceptions_vl
WHERE wave_header_id = l_get_waves.wave_header_id
AND exception_entity = p_exception_entity
AND exception_stage = p_progress_stage
AND order_number = l_get_so.source_header_number
AND LOWER(status) <> 'closed';
SELECT Count(*),
Sum(CASE
WHEN line_progress_id >= l_progress_stage THEN
1
ELSE
0
END)
INTO l_total_lines, l_perfect_lines
FROM wms_wp_wwb_lines_v
WHERE wave_header_id = l_get_waves.wave_header_id
AND source_header_id = l_get_so.source_header_id
AND organization_id = p_organization_id
and nvl(remove_from_Wave_flag, 'N') <> 'Y';
SELECT exception_id
INTO l_exception_id
FROM wms_wp_wave_exceptions_vl
WHERE wave_header_id = l_get_waves.wave_header_id
AND exception_entity = p_exception_entity
AND exception_stage = p_progress_stage
AND ORDER_LINE_ID = l_get_lines.source_line_id
AND LOWER(status) <> 'closed';
SELECT Count(*),
Sum(CASE
WHEN line_progress_id >= l_progress_stage THEN
1
ELSE
0
END)
INTO l_total_lines, l_perfect_lines
FROM wms_wp_wwb_lines_v
WHERE wave_header_id = l_get_waves.wave_header_id
AND source_line_id = l_get_lines.source_line_id
AND organization_id = p_organization_id
and nvl(remove_from_Wave_flag, 'N') <> 'Y';
PROCEDURE insert_purge_exceptions(p_exception_name VARCHAR2,
p_exception_entity VARCHAR2,
p_exception_level VARCHAR2,
p_completion_threshold NUMBER,
p_progress_stage VARCHAR2,
p_exception_threshold NUMBER,
p_wave_id NUMBER,
p_trip_id NUMBER,
p_delivery_id NUMBER,
p_order_number NUMBER,
p_order_line_id NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_currval NUMBER;
print_debug('In insert_purge_exceptions procedure', l_debug);
SELECT wms_WP_WAVE_exceptions_s.NEXTVAL INTO l_seq_val FROM dual; -- Changed
WMS_WP_WAVE_EXCEPTIONS_PKG.INSERT_ROW(
-- X_ROWID => l_null, --
X_EXCEPTION_ID => l_seq_val,
X_EXCEPTION_ENTITY => p_exception_entity,
X_EXCEPTION_STAGE => p_progress_stage,
X_EXCEPTION_LEVEL => p_exception_level,
X_EXCEPTION_MSG => l_msg,
X_WAVE_HEADER_ID => p_wave_id,
X_TRIP_ID => p_trip_id,
X_DELIVERY_ID => p_delivery_id,
X_ORDER_NUMBER => p_order_number,
X_ORDER_LINE_ID => p_order_line_id,
X_STATUS => 'Active',
X_READY_TO_RELEASE => null,
X_BACKORDERED => null,
X_CROSSDOCK_PLANNED => null,
X_REPLENISHMENT_PLANNED => null,
X_TASKED => null,
X_PICKED => null,
X_PACKED => null,
X_STAGED => null,
X_LOADED_TO_DOCK => null,
X_SHIPPED => null,
X_CONCURRENT_REQUEST_ID => fnd_global.conc_request_id,
X_PROGRAM_ID => fnd_global.conc_program_id, --
X_EXCEPTION_NAME => p_exception_name,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.conc_login_id);
/* INSERT INTO wms_wp_wave_exceptions_vl
(exception_id,
exception_name,
exception_entity,
exception_stage,
exception_level,
exception_msg,
wave_header_id,
trip_id,
delivery_id,
order_number,
order_line_id,
status,
concurrent_request_id,
program_id,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(wms_WP_WAVE_exceptions_s.nextval,
p_exception_name,
p_exception_entity,
p_progress_stage,
p_exception_level,
l_msg,
p_wave_id,
p_trip_id,
p_delivery_id,
p_order_number,
p_order_line_id,
'Active',
fnd_global.conc_request_id,
fnd_global.conc_program_id,
fnd_global.user_id,
sysdate,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id);*/
DELETE FROM wms_wave_exceptions
WHERE wave_header_id = p_wave_id
AND ((p_exception_entity = 'Wave' AND
(wave_header_id = p_wave_id AND trip_id IS NULL AND
delivery_id IS NULL AND order_number IS NULL AND
order_line_id IS NULL)) OR
(p_exception_entity = 'Trip' AND trip_id = p_trip_id AND
delivery_id IS NULL AND order_number IS NULL AND
order_line_id IS NULL) OR
(p_exception_entity = 'Delivery' AND
delivery_id = p_delivery_id AND order_number IS NULL AND
order_line_id IS NULL) OR
(p_exception_entity = 'Order' AND order_number = p_order_number AND
order_line_id IS NULL) OR (p_exception_entity = 'Order Line' AND
order_line_id = p_order_line_id))
AND concurrent_request_id != fnd_global.conc_request_id
AND exception_id != (l_currval);
print_debug('Exception in insert_purge_exceptions' || SQLCODE || ':' ||
sqlerrm,
l_debug);
END insert_purge_exceptions;
SELECT exception_id, delivery_id, trip_id
INTO l_exception_id, l_previous_delivery, l_previous_trip
FROM wms_wp_wave_exceptions_vl
WHERE concurrent_request_id = fnd_global.conc_request_id
AND order_number = p_current_order;
print_debug('Nothing to update or insert : Entity = Order', l_debug);
update wms_wp_wave_exceptions_b
set delivery_id = null,
last_update_login = fnd_global.conc_login_id,
last_updated_by = fnd_global.user_id,
last_update_date = l_date
where exception_id = l_exception_id;
update WMS_WP_WAVE_EXCEPTIONS_TL
set LAST_UPDATE_DATE = l_date,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_ID = l_exception_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
/* UPDATE wms_wp_wave_exceptions
SET delivery_id = NULL
WHERE exception_id = l_exception_id;*/
update wms_wp_wave_exceptions_b
set trip_id = null,
delivery_id = null,
last_update_login = fnd_global.conc_login_id,
last_updated_by = fnd_global.user_id,
last_update_date = l_date
where exception_id = l_exception_id;
update WMS_WP_WAVE_EXCEPTIONS_TL
set LAST_UPDATE_DATE = l_date,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_ID = l_exception_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
UPDATE wms_wp_wave_exceptions
SET trip_id = NULL, delivery_id = NULL
WHERE exception_id = l_exception_id;*/
no_data_found: Atleast exception_id should be fetched in select query
too_many_rows: For that particular concurrent request,if the order number
comes for the second time we are calling this procedure and this will retain only
one record for that.*/
WHEN no_data_found THEN
print_debug('No data was fetched from check_so procedure query',
l_debug);
SELECT exception_id, delivery_id, trip_id
INTO l_exception_id, l_previous_delivery, l_previous_trip
FROM wms_wp_wave_exceptions_vl
WHERE concurrent_request_id = fnd_global.conc_request_id
AND ORDER_LINE_ID = p_current_line;
print_debug('Nothing to update or insert : Entity = Line', l_debug);
update wms_wp_wave_exceptions_b
set delivery_id = null,
last_update_login = fnd_global.conc_login_id,
last_updated_by = fnd_global.user_id,
last_update_date = l_date
where exception_id = l_exception_id;
update WMS_WP_WAVE_EXCEPTIONS_TL
set LAST_UPDATE_DATE = l_date,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_ID = l_exception_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update wms_wp_wave_exceptions_b
set trip_id = null,
delivery_id = null,
last_update_login = fnd_global.conc_login_id,
last_updated_by = fnd_global.user_id,
last_update_date = l_date
where exception_id = l_exception_id;
update WMS_WP_WAVE_EXCEPTIONS_TL
set LAST_UPDATE_DATE = l_date,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_ID = l_exception_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
l_update_exception_id NUMBER;
SELECT to_char(ROUND((nvl(SUM(decode(line_progress_id, 1, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 2, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 3, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 4, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 5, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 7, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 8, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 9, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 10, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 11, 1, 0)),
0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%'
INTO l_readytorelease,
l_backordered,
l_crossdocked,
l_replenishment,
l_tasked,
l_picked,
l_staged,
l_packed,
l_loadedtodock,
l_shipped
FROM wms_wp_wwb_lines_v
WHERE wave_header_id = p_wave_id
AND ((p_entity = 'Wave' AND wave_header_id = p_wave_id) OR
(p_entity = 'Trip' AND trip = p_entity_value) OR
(p_entity = 'Delivery' AND delivery_id = p_entity_value) OR
(p_entity = 'Order' AND order_number = p_entity_value) OR
(p_entity = 'Order Line' AND
source_line_id = p_entity_value));
UPDATE wms_wp_wave_exceptions_b
SET exception_level = l_level,
exception_msg = l_msg,
ready_to_release = l_readytorelease,
backordered = l_backordered,
crossdock_planned = l_crossdocked,
replenishment_planned = l_replenishment,
tasked = l_tasked,
picked = l_picked,
packed = l_packed,
staged = l_staged,
loaded_to_dock = l_loadedtodock,
shipped = l_shipped,
concurrent_request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = SYSDATE
WHERE exception_id = p_exception_id;
update WMS_WP_WAVE_EXCEPTIONS_TL
set LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_ID = p_exception_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
UPDATE wms_wp_wave_exceptions_b
SET status = 'Closed',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE exception_id = p_exception_id;
update WMS_WP_WAVE_EXCEPTIONS_TL
set LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_ID = p_exception_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
insert_purge_exceptions(p_exception_name,
p_entity,
l_level,
p_completion_threshold,
p_progress_stage,
l_completion_threshold,
p_wave_id,
p_trip_id,
p_delivery_id,
p_order_number,
p_order_line_id,
x_return_status);
print_debug('Error returned from insert_purge_exceptions ',
l_debug);
SELECT to_char(ROUND((nvl(SUM(decode(line_progress_id, 1, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 2, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 3, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 4, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 5, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 7, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 8, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 9, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 10, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%',
to_char(ROUND((nvl(SUM(decode(line_progress_id, 11, 1, 0)), 0) /
decode(COUNT(1), 0, -1, COUNT(-1))) * 100,
2)) || '%'
INTO l_readytorelease,
l_backordered,
l_crossdocked,
l_replenishment,
l_tasked,
l_picked,
l_staged,
l_packed,
l_loadedtodock,
l_shipped
FROM wms_wp_wwb_lines_v
WHERE wave_header_id = p_wave_id
AND ((p_entity = 'Wave' AND wave_header_id = p_wave_id) OR
(p_entity = 'Trip' AND trip = p_entity_value) OR
(p_entity = 'Delivery' AND delivery_id = p_entity_value) OR
(p_entity = 'Order' AND order_number = p_entity_value) OR
(p_entity = 'Order Line' AND source_line_id = p_entity_value));
SELECT wms_WP_WAVE_exceptions_s.currval INTO l_seq_val FROM dual; -- Changed
l_update_exception_id := l_seq_val; -- Changed
UPDATE wms_wp_wave_exceptions_b
SET ready_to_release = l_readytorelease,
backordered = l_backordered,
crossdock_planned = l_crossdocked,
replenishment_planned = l_replenishment,
tasked = l_tasked,
picked = l_picked,
packed = l_packed,
staged = l_staged,
loaded_to_dock = l_loadedtodock,
shipped = l_shipped,
concurrent_request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id
WHERE exception_id = l_update_exception_id;
update WMS_WP_WAVE_EXCEPTIONS_TL
set LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_ID = l_update_exception_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
SELECT wms_WP_WAVE_exceptions_s.CURRVAL INTO l_seq_val FROM dual; -- Changed
l_select_query VARCHAR2(4000);
l_updated_record NUMBER := 0;
select field_name, field_value, query_type
from wms_saved_queries
where query_name = p_action_name
and (query_type = 'TASK_ACTION' or query_type = 'TEMP_TASK_ACTION')
FOR UPDATE NOWAIT;
select distinct query_type
from wms_saved_queries
where query_name = p_action_name;
l_field_name_table.delete;
l_field_value_table.delete;
l_query_type_table.delete;
l_select_query := 'select mmtt.TRANSACTION_TEMP_ID, mmtt.wms_task_type';
l_final_query := l_select_query || l_from_query || l_where_query;
l_transaction_temp_id_table.DELETE;
l_task_type_id_table.DELETE;
insert into wms_wp_tasks_gtmp
select task_id,
to_lpn,
to_lpn_id,
transaction_temp_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_source_id,
transaction_source_line_id,
organization_id,
organization_code,
to_organization_id,
to_organization_code,
to_subinventory,
to_locator_id,
to_locator,
user_task_type_id,
user_task_type,
person_id,
person_id_original,
person,
effective_start_date,
effective_end_date,
person_resource_id,
person_resource_code,
status_id,
status_id_original,
status,
mmtt_last_update_date,
mmtt_last_updated_by,
wdt_last_update_date,
wdt_last_updated_by,
priority,
priority_original,
task_type_id,
task_type,
is_modified,
plan_task,
result,
error,
source_header,
line_number,
item,
item_description,
revision,
subinventory,
locator,
secondary_quantity,
secondary_uom,
operation_plan_id,
operation_plan,
operation_sequence,
operation_plan_instance,
ship_method,
shipment_date,
shipment_priority,
department,
child_tasks,
num_of_child_tasks,
allocated_lpn,
cartonized_lpn,
container_item,
content_lpn,
dispatched_time,
equipment,
from_lpn,
parent_line_id,
move_order_line_id,
pick_slip_number,
picked_lpn,
transaction_uom,
transaction_quantity
from wms_wp_wwb_tasks_v
where wave_header_id = p_wave_id
AND transaction_temp_id = l_transaction_temp_id_table(ttemp_id);
/* Bug 5485730 - The employee details should be null if the status is being updated
to Pending or Unreleased */
IF l_action_type = 'U' then
IF l_status_code IN (1, 8) THEN
l_employee := NULL;
print_DEBUG('Calling wms_wave_planning_pvt.UPDATE_TASK', l_debug);
print_DEBUG('p_update_priority_type => ' || l_priority_type,
l_debug);
print_DEBUG('p_update_priority => ' || l_priority, l_debug);
update_task(p_transaction_temp_id => l_transaction_temp_id_table,
p_task_type_id => l_task_type_id_table,
p_employee => l_employee,
p_employee_id => l_employee_id,
p_user_task_type => l_user_task_type,
p_user_task_type_id => l_user_task_type_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_person_resource_id => l_person_resource_id,
p_person_resource_code => l_person_resource_code,
p_force_employee_change => l_override_emp_check,
p_to_status => l_status,
p_to_status_id => l_status_code,
p_priority_type => l_priority_type,
p_priority => l_priority,
p_clear_priority => l_clear_priority,
x_result => l_result_table,
x_message => l_message_table,
x_task_id => l_task_id_table,
x_return_status => l_return_status,
x_return_msg => l_msg_data,
x_msg_count => l_msg_count);
print_DEBUG('update_task l_return_status ' || l_return_status,
l_debug);
print_DEBUG('Error in update_task ', l_debug);
print_DEBUG('Records Updated by update_task ' ||
l_result_table.count,
l_debug);
l_updated_record := l_result_table.Count;
l_updated_record := 0;
l_updated_record := 0;
IF l_updated_record > 0 then
print_DEBUG('Calling wms_waveplan_tasks_pvt.save_tasks ', l_debug);
l_transaction_temp_id_table.DELETE;
l_task_type_id_table.DELETE;
l_select_query VARCHAR2(4000);
select distinct (source_header_id)
into l_entity_value
from wsh_delivery_details
where source_header_number = p_entity_value
and organization_id = p_organization_id;
l_select_query := 'select wwl.delivery_detail_id';
l_final_query := l_select_query || l_from_query || l_where_query;
UPDATE wms_wp_wave_lines
SET re_release_flag = 'Y'
WHERE delivery_detail_id = l_wdd_table(i);
l_wdd_table.DELETE;
UPDATE wms_wp_wave_exceptions_b
SET status = 'Action Taken'
WHERE exception_id = p_exception_id;
update WMS_WP_WAVE_EXCEPTIONS_TL
set LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
SOURCE_LANG = userenv('LANG')
where EXCEPTION_ID = p_exception_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
print_debug('after update of exceptions table', l_debug);
SELECT mtrl.move_order_line_id,
mtrl.move_order_header_id,
mtrl.reservation_id,
mtrl.transaction_quantity,
mtrl.primary_quantity,
mtrl.secondary_transaction_quantity,
mtrl.organization_id,
mtrh.move_order_type
INTO l_mol_id,
l_moh_id,
l_res_id,
l_txn_qty,
l_prim_qty,
l_sec_qty,
l_org_id,
l_mo_type
FROM mtl_material_transactions_temp mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.transaction_temp_id = l_txn_temp_id
AND mtrh.header_id = mtrl.move_order_header_id;
SELECT COUNT(*)
INTO l_no_mmtt
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mol_id;
SELECT delivery_detail_id,
oe_header_id,
oe_line_id,
released_status
INTO l_shipping_attr(1).delivery_detail_id,
l_shipping_attr(1).source_header_id,
l_shipping_attr(1).source_line_id,
l_shipping_attr(1).released_status
FROM wsh_inv_delivery_details_v
WHERE move_order_line_id = l_mol_id
AND move_order_line_id IS NOT NULL
AND released_status = 'S';
SELECT from_subinventory_code,
from_locator_id,
secondary_uom_code,
quantity,
quantity_delivered,
quantity_detailed,
secondary_quantity,
secondary_quantity_delivered,
secondary_quantity_detailed
INTO l_from_sub,
l_from_loc,
l_sec_uom,
l_qty,
l_qty_del,
l_qty_det,
l_sec_mol_qty,
l_sec_qty_del,
l_sec_qty_det
FROM mtl_txn_request_lines
WHERE line_id = l_mol_id;
/* The below call deletes mtlt, msnt and mmtt. Takes care of everything*/
inv_mo_backorder_pvt.delete_details(x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_transaction_temp_id => l_txn_temp_id,
p_move_order_line_id => l_mol_id,
p_reservation_id => l_res_id,
p_transaction_quantity => l_txn_qty,
p_primary_trx_qty => l_prim_qty,
p_secondary_trx_qty => l_sec_qty);
UPDATE mtl_txn_request_lines
SET line_status = 5,
quantity_detailed = (quantity_detailed - l_txn_qty),
quantity = (quantity - l_txn_qty)
WHERE line_id = l_mol_id;
UPDATE mtl_txn_request_lines
SET line_status = 5,
quantity_detailed = (quantity_detailed -
l_txn_qty),
secondary_quantity_detailed = (secondary_quantity_detailed -
l_sec_qty),
quantity = (quantity - l_txn_qty),
secondary_quantity = (secondary_quantity -
l_sec_qty),
status_date = sysdate
WHERE line_id = l_mol_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = (quantity_detailed - l_txn_qty),
quantity = (quantity - l_txn_qty)
WHERE line_id = l_mol_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = (quantity_detailed -
l_txn_qty),
secondary_quantity_detailed = (secondary_quantity_detailed -
l_sec_qty),
quantity = (quantity - l_txn_qty),
secondary_quantity = (secondary_quantity -
l_sec_qty),
status_date = sysdate
WHERE line_id = l_mol_id;
wsh_interface.update_shipping_attributes(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => x_return_status);
PROCEDURE update_wave_lines_tbl (p_mo_line_table IN INV_Move_Order_PUB.TROLIN_TBL_TYPE
,x_return_status OUT NOCOPY VARCHAR2) IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
print_debug('Inside the call to update_wave_lines_tbl at place 1',l_debug);
--bug 10256506 copy p_mo_line_table(i).txn_source_line_detail_id to local l_mo_line_table, then do bulk delete using l_mo_line_table
if p_mo_line_table.count > 0 then -- 14781148
for i in p_mo_line_table.first .. p_mo_line_table.last loop
if p_mo_line_table.exists(i) then -- 14781148
l_mo_line_table(j) := p_mo_line_table(i).txn_source_line_detail_id;
DELETE FROM wms_wp_wave_lines wwwl
WHERE wwwl.delivery_detail_id = l_mo_line_table(i)
AND exists (select 1 from wms_wp_wave_headers_b wwwh where wwwh.wave_firmed_flag = 'N' and wwwl.wave_header_id = wwwh.wave_header_id)
AND nvl(wwwl.remove_from_wave_flag , 'N')='N';
print_debug('Inside the call to update_wave_lines_tbl at place 2',l_debug);
print_debug('Before returning from update_wave_lines_tbl at place 3',l_debug);
print_debug('Error in update_wave_lines_tbl',l_debug);
END update_wave_lines_tbl;
PROCEDURE insert_global_temp(p_wave_header_id IN number)
IS
TYPE lookup_meaning_table IS TABLE OF mfg_lookups.meaning%TYPE INDEX BY BINARY_INTEGER;
select lookup_code, REPLACE (meaning, '''', '''''') bulk collect into l_plan_task_type_codes, l_plan_task_types_temp
from mfg_lookups
where lookup_type = 'WMS_PLAN_TASK_TYPES'
order by lookup_code;
delete wms_wp_orders_gtmp;
delete wms_wp_lines_gtmp;
delete from wms_wp_exceptions_gtmp;
delete from wms_wp_tasks_gtmp;
delete from wms_wp_labor_gtmp;
--inner most select finds all lines associated with wave; next outer select groups by order number; outermost select adds in order-level information
insert into wms_wp_orders_gtmp (order_number,
customer,
planned_fill_rate,
release_fill_rate,
pick_fill_rate,
order_type,
customer_id,
cust_po_number,
ordered_date,
order_request_date,
order_progress)
select /*+ use_nl(CUST_ACCT) index(CUST_ACCT HZ_CUST_ACCOUNTS_U1) */ ooh.order_number,
HP.PARTY_NAME CUSTOMER,
wl.planned_fill_rate,
wl.release_fill_rate,
wl.pick_fill_rate,
ott.name,
ooh.sold_to_org_id,
ooh.CUST_PO_NUMBER,
ooh.ordered_date,
ooh.request_date order_request_date,
wl.order_progress
from oe_order_headers_all ooh,
HZ_PARTIES HP,
hz_cust_accounts CUST_ACCT,
oe_transaction_types_tl ott,
(select wwlv.source_header_id,
sum(wwlv.requested_quantity*wwlv.planned_fill_rate)/decode(sum(wwlv.requested_quantity),0,1,sum(wwlv.requested_quantity)) planned_fill_rate,
sum(wwlv.requested_quantity*wwlv.release_fill_rate)/decode(sum(wwlv.requested_quantity),0,1,sum(wwlv.requested_quantity)) release_fill_rate,
sum(wwlv.requested_quantity*wwlv.pick_fill_rate)/decode(sum(wwlv.pick_fill_rate),0,1,sum(wwlv.pick_fill_rate)) pick_fill_rate,
decode(count(DISTINCT wwlv.line_progress_id),1,'','Partially ')||Decode(max(wwlv.line_progress_id),1,'Ready to Release',2,'Backordered',3,'Crossdock Planned',4,'Replenishment Created',5,'Tasked',6,'Picked',
7,'Picked',9,'In Packing',8,'Staged',10,'Loaded to Dock',11,'Shipped') ORDER_PROGRESS
from (SELECT wwl.wave_line_id,
wdd.requested_quantity, --11775489 select from wdd instead...
wdd.source_header_id source_header_id,
Nvl(WWl.planned_FILL_RATE, 0) planned_fill_rate,
Nvl(wwl.release_fill_rate,0) release_fill_rate,
Nvl(wwl.pick_fill_rate,0) pick_fill_rate,
Decode(wdd.released_status,
'S',Decode(wms_Wave_planning_pvt.get_loaded_status(wdd.delivery_Detail_id),1,6,2,7,Decode(Nvl(wdd.move_order_line_id,-99),-99,3,5)),
'C',11,
'B',Decode(wdd.replenishment_status,'R',4,2),
'R',1,
'K',3,
'Y',Decode(wlpn.lpn_context,11,Decode(mil.inventory_location_type,2,8,4,9,5,9),9,10)) LINE_PROGRESS_ID,
wwl.wave_header_id
FROM
WSH_DELIVERY_DETAILS WDD,
WMS_WP_WAVE_LINES WWL,
WMS_LICENSE_PLATE_NUMBERS WLPN,
WSH_DELIVERY_ASSIGNMENTS WDA,
mtl_item_locations_kfv mil,
wsh_Delivery_details wdd2
WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id(+)
AND wdd.locator_id = mil.inventory_location_id(+)
and WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and wwl.organization_id = wdd.organization_id --11775489 get from wdd... FIXED
AND wlpn.lpn_id(+) = wdd2.lpn_id
AND wwl.wave_header_id = p_wave_header_id) wwlv
group by wwlv.source_header_id) wl
where wl.source_header_id = ooh.header_id
and HP.party_id = cust_acct.party_id
and cust_acct.status = 'A'
and cust_acct.cust_account_id= ooh.SOLD_TO_ORG_ID
and ott.transaction_type_id = ooh.order_type_id
and ott.LANGUAGE = userenv('LANG');
insert into wms_wp_lines_gtmp
(DELIVER_TO,
DELIVERY,
EXCEPTION_ID,
ITEM,
LINE_STATUS,
LINE_PROGRESS,
ORDER_NUMBER,
SOURCE_LINE_NUMBER,
REQUESTED_QTY,
PLANNED_FILL_RATE,
RELEASE_FILL_RATE,
PICK_FILL_RATE,
SHIP_TO,
SHIPPED_QTY,
DATE_SCHEDULED,
BATCH,
CARRIER,
CONTACT,
CUSTOMER_MODEL_SERIAL_NUMBER,
DATE_REQUESTED,
DELIVER_TO_CONTACT,
DELIVER_TO_CUSTOMER,
DELIVERED_QTY,
EARLIEST_DELIVERY_DATE,
EARLIEST_SHIP_DATE,
FOB,
FREIGHT_TERMS,
GRADE,
GROSS_WEIGHT,
HAZARD_CLASS,
IGNORE_FOR_PLANNING,
INSPECTION_STATUS,
INTERMEDIATE_SHIP_TO,
INTERMEDIATE_SHIP_TO_CONTACT,
ITEM_DESCRIPTION,
LATEST_DELIVERY_DATE,
LOADING_SEQUENCE,
LOCATOR,
LOT,
MAX_LOAD_WEIGHT,
MAX_VOLUME,
MIN_FILL_PERCENT,
MODE_OF_TRANSPORT,
NET_WEIGHT,
ORDER_TYPE,
ORG_NAME,
PACKING_INSTRUCTIONS,
REQUESTED_QTY_UOM,
REVISION,
SEAL_CODE,
SECONDARY_DELIVERED_QTY,
SECONDARY_REQUESTED_QTY,
SECONDARY_REQUESTED_QTY_UOM,
SECONDARY_SHIPPED_QTY,
SECONDARY_SRC_REQ_QTY,
SECONDARY_SRC_REQ_QTY_UOM,
SERVICE_LEVEL,
SHIP_METHOD,
SHIP_MODEL_COMPLETE,
SHIP_SET,
SHIP_TO_CONTACT,
SHIP_TO_CUSTOMER,
SHIPMENT_PRIORITY,
SHIPPING_INSTRUCTIONS,
SOURCE_REQUESTED_QTY,
SOURCE_REQUESTED_QTY_UOM,
TARE_WEIGHT,
TASK_NUMBER,
TRACKING_NUMBER,
UNIT_NUMBER,
VOLUME,
VOLUME_UOM,
WEIGHT_UOM,
CUSTOMER,
PROJECT,
CUSTOMER_CLASS,
TRIP,
REMOVE_FROM_WAVE_FLAG,
WAVE_FIRMED_FLAG,
MESSAGE)
select /*+ use_nl(MSIV) */
(select ui_location_code from wsh_locations where wsh_location_id=OOL.DELIVER_TO_ORG_ID) DELIVER_TO,
WND.NAME DELIVERY,
null,
MSIV.concatenated_segments ITEM, --12642410
(select meaning from FND_LOOKUP_VALUES WHERE lookup_type = 'PICK_STATUS' AND LANGUAGE = userenv('LANG') and lookup_code = wdd.released_status) line_status, --12693583
Decode(wdd.released_status,
'S',Decode(wms_Wave_planning_pvt.get_loaded_status(wdd.delivery_Detail_id),1,'Partially Picked',2,'Picked',Decode(Nvl(wdd.move_order_line_id,-99),-99,'Crossdock Planned','Tasked')),
'C','Shipped',
'B',Decode(wdd.replenishment_status,'R','Replenishment Created','Backordered'),
'R','Ready to Release',
'Y',Decode(wlpn.lpn_context,11,Decode(mil.inventory_location_type,2,'Staged',4,'In Packing',5,'In Packing'),9,'Loaded to Dock')) LINE_PROGRESS,
WDD.SOURCE_HEADER_NUMBER ORDER_NUMBER,
WDD.SOURCE_LINE_NUMBER,
WDD.REQUESTED_QUANTITY REQUESTED_QTY,
WWl.planned_FILL_RATE,
wwl.release_fill_rate,
wwl.pick_fill_rate,
(select ui_location_code from wsh_locations where wsh_location_id=WDD.SHIP_TO_LOCATION_ID) SHIP_TO,
WDD.SHIPPED_QUANTITY SHIPPED_QTY,
WDD.DATE_SCHEDULED,
WDD.BATCH_ID BATCH,
WC.FREIGHT_CODE CARRIER,
(select ui_location_code from wsh_locations where wsh_location_id=OOL.SHIP_TO_CONTACT_ID) Line_Ship_To_Contact,
OOL.CUST_MODEL_SERIAL_NUMBER,
OOL.REQUEST_DATE Line_request_date,
(select ui_location_code from wsh_locations where wsh_location_id=OOL.DELIVER_TO_CONTACT_ID) DELIVER_TO_CONTACT,
(select ui_location_code from wsh_locations where
wsh_location_id=WDD.DELIVER_TO_LOCATION_ID) DELIVER_TO_LOCATION,
WDD.DELIVERED_QUANTITY,
WDD.EARLIEST_DROPOFF_DATE,
to_date(OOL.EARLIEST_SHIP_DATE),
WDD.FOB_CODE,
WDD.FREIGHT_TERMS_CODE,
WDD.PREFERRED_GRADE,
WDD.GROSS_WEIGHT,
WDD.HAZARD_CLASS_ID,
WDD.IGNORE_FOR_PLANNING,
WDD.INSPECTION_FLAG,
(select ui_location_code from wsh_locations where wsh_location_id=WDD.INTMED_SHIP_TO_LOCATION_ID),
(select ui_location_code from wsh_locations where wsh_location_id=WDD.INTMED_SHIP_TO_CONTACT_ID),
WDD.ITEM_DESCRIPTION,
WDD.LATEST_DROPOFF_DATE,
WDD.LOAD_SEQ_NUMBER,
mil.concatenated_segments,
WDD.LOT_NUMBER,
WDD.MAXIMUM_LOAD_WEIGHT,
WDD.MAXIMUM_VOLUME,
WDD.MINIMUM_FILL_PERCENT,
WDD.MODE_OF_TRANSPORT,
WDD.NET_WEIGHT,
WDD.SOURCE_HEADER_TYPE_NAME "ORDER TYPE",
MP.ORGANIZATION_CODE,
WDD.PACKING_INSTRUCTIONS,
WDD.REQUESTED_QUANTITY_UOM,
WDD.REVISION,
WDD.SEAL_CODE,
WDD.DELIVERED_QUANTITY2,
WDD.REQUESTED_QUANTITY2,
WDD.REQUESTED_QUANTITY_UOM2,
WDD.SHIPPED_QUANTITY2,
WDD.SRC_REQUESTED_QUANTITY2,
WDD.SRC_REQUESTED_QUANTITY_UOM2,
WDD.SERVICE_LEVEL,
WDD.SHIP_METHOD_CODE SHIP_METHOD,
WDD.SHIP_MODEL_COMPLETE_FLAG,
WDD.SHIP_SET_ID,
(select ui_location_code from wsh_locations where wsh_location_id=WDD.SHIP_TO_CONTACT_ID),
(SELECT p.party_name FROM hz_party_sites ps, hz_parties p WHERE p.party_id = ps.party_id AND ps.location_id = wdd.ship_to_location_id AND party_type = 'ORGANIZATION' and rownum=1) SHIP_TO_CUSTOMER,
WDD.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY,
WDD.SHIPPING_INSTRUCTIONS,
WDD.SRC_REQUESTED_QUANTITY,
WDD.SRC_REQUESTED_QUANTITY_UOM,
WLPN.TARE_WEIGHT,
WDD.TASK_ID TASK_NUMBER,
WDD.TRACKING_NUMBER,
WDD.UNIT_NUMBER,
WDD.VOLUME,
WDD.VOLUME_UOM_CODE,
WDD.WEIGHT_UOM_CODE,
HP.PARTY_NAME CUSTOMER,
WDD.PROJECT_ID PROJECT,
cust_Acct.customer_class_code CUSTOMER_CLASS,
WTS.TRIP_ID TRIP,
wwl.remove_from_wave_flag,
wwh.wave_firmed_flag,
wwl.message
from
WSH_DELIVERY_DETAILS WDD,
HZ_PARTIES HP,
hz_cust_accounts CUST_ACCT,
OE_ORDER_LINES_ALL OOL,
wms_wp_wave_headers_b WWH,
WMS_WP_WAVE_LINES WWL,
WSH_NEW_DELIVERIES WND,
MTL_SYSTEM_ITEMS_KFV MSIV,
WSH_CARRIERS WC,
MTL_PARAMETERS MP,
WMS_LICENSE_PLATE_NUMBERS WLPN,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_LEGS WLG,
mtl_item_locations_kfv mil,
wsh_Delivery_details wdd2
WHERE
HP.party_id = cust_acct.party_id
and cust_acct.status = 'A'
and cust_acct.cust_account_id= wdd.customer_id
AND WDD.SOURCE_LINE_ID = OOL.LINE_ID
AND WDD.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND WDD.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID
AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.CARRIER_ID = WC.CARRIER_ID(+)
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id(+)
AND wdd.locator_id = mil.inventory_location_id(+)
AND WWH.WAVE_HEADER_ID = WWL.WAVE_HEADER_ID
and WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and wwl.organization_id = wdd.organization_id --11775489 get from wdd... FIXED
AND WTS.STOP_ID(+) = WLG.PICK_UP_STOP_ID
AND WLG.DELIVERY_ID(+) = WND.DELIVERY_ID
AND wlpn.lpn_id(+) = wdd2.lpn_id
AND WDD.ORGANIZATION_ID = MSIV.ORGANIZATION_ID --12371804
AND wwl.wave_header_id = p_wave_header_id;
insert into wms_wp_tasks_gtmp(
TASK_ID,
TO_LPN,
TO_LPN_ID,
TRANSACTION_TEMP_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_LINE_ID,
ORGANIZATION_ID,
ORGANIZATION_CODE,
TO_ORGANIZATION_ID,
TO_ORGANIZATION_CODE,
TO_SUBINVENTORY,
TO_LOCATOR_ID,
TO_LOCATOR,
USER_TASK_TYPE_ID,
USER_TASK_TYPE,
PERSON_ID,
PERSON_ID_ORIGINAL,
PERSON,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PERSON_RESOURCE_ID,
PERSON_RESOURCE_CODE,
STATUS_ID,
STATUS_ID_ORIGINAL,
STATUS,
MMTT_LAST_UPDATE_DATE,
MMTT_LAST_UPDATED_BY,
WDT_LAST_UPDATE_DATE,
WDT_LAST_UPDATED_BY,
PRIORITY,
PRIORITY_ORIGINAL,
TASK_TYPE_ID,
TASK_TYPE,
IS_MODIFIED,
PLAN_TASK,
RESULT,
ERROR,
SOURCE_HEADER,
LINE_NUMBER,
ITEM,
ITEM_DESCRIPTION,
REVISION,
SUBINVENTORY,
LOCATOR,
SECONDARY_QUANTITY,
SECONDARY_UOM,
OPERATION_PLAN_ID,
OPERATION_PLAN,
OPERATION_SEQUENCE,
OPERATION_PLAN_INSTANCE,
SHIP_METHOD,
SHIPMENT_DATE,
SHIPMENT_PRIORITY,
DEPARTMENT,
CHILD_TASKS,
NUM_OF_CHILD_TASKS,
ALLOCATED_LPN,
CARTONIZED_LPN,
CONTAINER_ITEM,
CONTENT_LPN,
DISPATCHED_TIME,
EQUIPMENT,
FROM_LPN,
PARENT_LINE_ID,
MOVE_ORDER_LINE_ID,
PICK_SLIP_NUMBER,
PICKED_LPN,
TRANSACTION_UOM,
TRANSACTION_QUANTITY)
SELECT /*+ use_nl(WWL,WDD,mmtt) index(mmtt) */ DISTINCT WDT.TASK_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.TRANSFER_LPN_ID) TO_LPN,
MMTT.TRANSFER_LPN_ID TO_LPN_ID,
mmtt.transaction_temp_id,
mmtt.transaction_type_id,
mmtt.transaction_action_id,
mmtt.transaction_source_type_id,
mmtt.transaction_source_id,
mmtt.trx_source_line_id,
mmtt.organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmtt.organization_id) organization_code,
mmtt.transfer_organization to_organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmtt.transfer_organization) to_organization_code,
mmtt.transfer_subinventory to_subinventory,
mmtt.transfer_to_location to_locator_id,
(SELECT mil.concatenated_segments --bug12642410
FROM MTL_ITEM_LOCATIONS_kfv mil
WHERE mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.organization_id = mil.organization_id) to_locator,
mmtt.standard_operation_id user_task_type_id,
bso.operation_code,
wdt.person_id person_id,
wdt.person_id person_id_original,
pap.full_name person,
wdt.effective_start_date,
wdt.effective_end_date,
wdt.person_resource_id,
br1.resource_code,
decode(wdt.status,
null,
nvl(mmtt.wms_task_status, 1),
wdt.status) status_id,
decode(wdt.status,
null,
nvl(mmtt.wms_task_status, 1),
wdt.status) status_id_original,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_STATUS' and lookup_code = decode(wdt.status,null, nvl(mmtt.wms_task_status,1), wdt.status)) STATUS, --12693583
mmtt.last_update_date mmtt_last_update_date,
mmtt.last_updated_by mmtt_last_update_by,
wdt.last_update_date wdt_last_update_date,
wdt.last_updated_by wdt_last_update_by,
mmtt.task_priority,
mmtt.task_priority priority_original,
mmtt.wms_task_type task_type_id,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_TYPES' and lookup_code =mmtt.wms_task_type) task_type, --12693583
'N' is_modified,
decode(mmtt.parent_line_id,
null,
decode(mmtt.operation_plan_id,
null,
l_plan_task_types(1),
decode(mmtt.transaction_action_id,
28,
l_plan_task_types(1),
decode(mmtt.transaction_source_type_id,
5,
l_plan_task_types(1),
13,
decode(mmtt.transaction_type_id,
51,
l_plan_task_types(1),
l_plan_task_types(3))))),
mmtt.transaction_temp_id,
l_plan_task_types(4),
l_plan_task_types(2)) plan_task, --12693583
' ' result,
' ' error,
WDD.SOURCE_HEADER_NUMBER source_header,
WDD.SOURCE_LINE_NUMBER line_number,
MSIV.concatenated_segments item, --bug12642410
MSIV.DESCRIPTION item_description,
MMTT.REVISION,
MMTT.SUBINVENTORY_CODE subinventory,
(mil.concatenated_segments) locator,
MMTT.SECONDARY_TRANSACTION_QUANTITY,
MMTT.SECONDARY_UOM_CODE,
MMTT.OPERATION_PLAN_ID,
WOPV.OPERATION_PLAN_NAME,
WOOI.OPERATION_SEQUENCE,
WOOI.OP_PLAN_INSTANCE_ID,
WDD.SHIP_METHOD_CODE SHIP_METHOD,
WDD.DATE_SCHEDULED shipment_date,
WDD.SHIPMENT_PRIORITY_CODE shipment_priority,
MMTT.DEPARTMENT_CODE,
'N' CHILD_TASKS,
null num_of_child_tasks,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.ALLOCATED_LPN_ID) ALLOCATED_LPN,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CARTONIZATION_ID) CARTONIZED_LPN,
(SELECT concatenated_segments FROM mtl_system_items_kfv WHERE inventory_item_id= MMTT.CONTAINER_ITEM_ID AND organization_id=mmtt.organization_id),
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CONTENT_LPN_ID) CONTENT_LPN,
WDT.DISPATCHED_TIME,
WDT.EQUIPMENT_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.LPN_ID) FROM_LPN,
MMTT.PARENT_LINE_ID,
mmtt.move_order_line_id,
MMTT.PICK_SLIP_NUMBER,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CONTENT_LPN_ID) PICKED_LPN,
mmtt.transaction_uom,
mmtt.transaction_quantity
FROM mtl_material_transactions_temp mmtt,
WSH_DELIVERY_DETAILS WDD,
MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
mtl_system_items_kfv msiv,
wms_op_plans_vl wopv,
per_all_people_f pap,
wms_op_operation_instances wooi,
WMS_WP_WAVE_LINES WWL,
wms_dispatched_tasks wdt,
bom_standard_operations bso,
bom_resources br1
where mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
AND wdt.person_id = pap.person_id(+)
AND wdt.effective_start_date >= pap.effective_start_date(+)
AND wdt.effective_end_date <= pap.effective_end_date(+)
AND mmtt.organization_id = msiv.organization_id
AND mmtt.inventory_item_id = msiv.inventory_item_id
and mmtt.transaction_action_id = 28
AND mmtt.organization_id = mil.organization_id(+)
AND mmtt.locator_id = mil.inventory_location_id(+)
AND WDD.source_line_id = MMTT.trx_source_line_id
AND wdd.move_order_line_id=mmtt.move_order_line_id
AND mmtt.transaction_temp_id = wooi.source_task_id(+)
AND mmtt.operation_plan_id = wopv.operation_plan_id(+)
AND WWL.delivery_detail_id = WDD.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and wwl.wave_header_id = p_wave_header_id
and nvl(wwl.remove_from_wave_flag,'N') <> 'Y'
AND mmtt.standard_operation_id = bso.standard_operation_id(+)
AND mmtt.organization_id = bso.organization_id(+)
AND wdt.person_resource_id = br1.resource_id(+)
AND mmtt.parent_line_id is null --12736677 no longer display bulk tasks (parent or child)
union
--12736677 now include separate select for the bulk tasks
SELECT /*+ use_nl(WWL,WDD,mmtt) index(mmtt) */ DISTINCT WDT.TASK_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.TRANSFER_LPN_ID) TO_LPN,
MMTT.TRANSFER_LPN_ID TO_LPN_ID,
mmtt.transaction_temp_id,
mmtt.transaction_type_id,
mmtt.transaction_action_id,
mmtt.transaction_source_type_id,
mmtt.transaction_source_id,
mmtt.trx_source_line_id,
mmtt.organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmtt.organization_id) organization_code,
mmtt.transfer_organization to_organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmtt.transfer_organization) to_organization_code,
mmtt.transfer_subinventory to_subinventory,
mmtt.transfer_to_location to_locator_id,
(SELECT mil.concatenated_segments --bug12642410
FROM MTL_ITEM_LOCATIONS_kfv mil
WHERE mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.organization_id = mil.organization_id) to_locator,
mmtt.standard_operation_id user_task_type_id,
bso.operation_code,
wdt.person_id person_id,
wdt.person_id person_id_original,
pap.full_name person,
wdt.effective_start_date,
wdt.effective_end_date,
wdt.person_resource_id,
br1.resource_code,
decode(wdt.status,
null,
nvl(mmtt.wms_task_status, 1),
wdt.status) status_id,
decode(wdt.status,
null,
nvl(mmtt.wms_task_status, 1),
wdt.status) status_id_original,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_STATUS' and lookup_code = decode(wdt.status,null, nvl(mmtt.wms_task_status,1), wdt.status)) STATUS, --12693583
mmtt.last_update_date mmtt_last_update_date,
mmtt.last_updated_by mmtt_last_update_by,
wdt.last_update_date wdt_last_update_date,
wdt.last_updated_by wdt_last_update_by,
mmtt.task_priority,
mmtt.task_priority priority_original,
mmtt.wms_task_type task_type_id,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_TYPES' and lookup_code =mmtt.wms_task_type) task_type, --12693583
'N' is_modified,
l_plan_task_types(4) plan_task, --12693583, 12736677
' ' result,
' ' error,
null source_header,
null line_number,
MSIV.concatenated_segments item, --bug12642410
MSIV.DESCRIPTION item_description,
MMTT.REVISION,
MMTT.SUBINVENTORY_CODE subinventory,
(mil.concatenated_segments) locator,
MMTT.SECONDARY_TRANSACTION_QUANTITY,
MMTT.SECONDARY_UOM_CODE,
MMTT.OPERATION_PLAN_ID,
WOPV.OPERATION_PLAN_NAME,
WOOI.OPERATION_SEQUENCE,
WOOI.OP_PLAN_INSTANCE_ID,
null SHIP_METHOD,
null shipment_date,
null shipment_priority,
MMTT.DEPARTMENT_CODE,
'N' CHILD_TASKS,
(select count(1) from mtl_material_transactions_temp where parent_line_id = mmtt.transaction_temp_id and transaction_temp_id <> mmtt.transaction_temp_id) num_of_child_tasks,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.ALLOCATED_LPN_ID) ALLOCATED_LPN,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CARTONIZATION_ID) CARTONIZED_LPN,
(SELECT concatenated_segments FROM mtl_system_items_kfv WHERE inventory_item_id= MMTT.CONTAINER_ITEM_ID AND organization_id=mmtt.organization_id),
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CONTENT_LPN_ID) CONTENT_LPN,
WDT.DISPATCHED_TIME,
WDT.EQUIPMENT_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.LPN_ID) FROM_LPN,
MMTT.PARENT_LINE_ID,
mmtt.move_order_line_id,
MMTT.PICK_SLIP_NUMBER,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CONTENT_LPN_ID) PICKED_LPN,
mmtt.transaction_uom,
mmtt.transaction_quantity
FROM mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp mmtt2, --12736677
WSH_DELIVERY_DETAILS WDD,
MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
mtl_system_items_kfv msiv,
wms_op_plans_vl wopv,
per_all_people_f pap,
wms_op_operation_instances wooi,
WMS_WP_WAVE_LINES WWL,
wms_dispatched_tasks wdt,
bom_standard_operations bso,
bom_resources br1
where mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
AND wdt.person_id = pap.person_id(+)
AND wdt.effective_start_date >= pap.effective_start_date(+)
AND wdt.effective_end_date <= pap.effective_end_date(+)
AND mmtt.organization_id = msiv.organization_id
AND mmtt.inventory_item_id = msiv.inventory_item_id
and mmtt.transaction_action_id = 28
AND mmtt.organization_id = mil.organization_id(+)
AND mmtt.locator_id = mil.inventory_location_id(+)
AND WDD.source_line_id = mmtt2.trx_source_line_id --12736677
AND wdd.move_order_line_id=mmtt2.move_order_line_id --12736677
and mmtt.transaction_temp_id = mmtt2.parent_line_id --12736677
and mmtt.transaction_temp_id = mmtt.parent_line_id --12736677
AND mmtt.transaction_temp_id = wooi.source_task_id(+)
AND mmtt.operation_plan_id = wopv.operation_plan_id(+)
AND WWL.delivery_detail_id = WDD.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and wwl.wave_header_id = p_wave_header_id
and nvl(wwl.remove_from_wave_flag,'N') <> 'Y'
AND mmtt.standard_operation_id = bso.standard_operation_id(+)
AND mmtt.organization_id = bso.organization_id(+)
AND wdt.person_resource_id = br1.resource_id(+)
union
SELECT DISTINCT WDT.TASK_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.TRANSFER_LPN_ID) TO_LPN,
MMTT.TRANSFER_LPN_ID TO_LPN_ID,
mmtt.transaction_temp_id,
mmtt.transaction_type_id,
mmtt.transaction_action_id,
mmtt.transaction_source_type_id,
mmtt.transaction_source_id,
mmtt.trx_source_line_id,
mmtt.organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmtt.organization_id) organization_code,
mmtt.transfer_organization to_organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmtt.transfer_organization) to_organization_code,
mmtt.transfer_subinventory to_subinventory,
mmtt.transfer_to_location to_locator_id,
(select mil.concatenated_segments --bug12642410
FROM MTL_ITEM_LOCATIONS_kfv mil
WHERE mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.organization_id = mil.organization_id) to_locator,
mmtt.standard_operation_id user_task_type_id,
bso.operation_code,
wdt.person_id person_id,
wdt.person_id person_id_original,
pap.full_name person,
wdt.effective_start_date,
wdt.effective_end_date,
wdt.person_resource_id,
br1.resource_code,
decode(wdt.status,
null,
nvl(mmtt.wms_task_status, 1),
wdt.status) status_id,
decode(wdt.status,
null,
nvl(mmtt.wms_task_status, 1),
wdt.status) status_id_original,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_STATUS' and lookup_code = decode(wdt.status,null, nvl(mmtt.wms_task_status,1), wdt.status)) STATUS, --12693583
mmtt.last_update_date mmtt_last_update_date,
mmtt.last_updated_by mmtt_last_update_by,
wdt.last_update_date wdt_last_update_date,
wdt.last_updated_by wdt_last_update_by,
mmtt.task_priority,
mmtt.task_priority priority_original,
mmtt.wms_task_type task_type_id,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_TYPES' and lookup_code =mmtt.wms_task_type) task_type, --12693583
'N' is_modified,
decode(mmtt.parent_line_id,
null,
decode(mmtt.operation_plan_id,
null,
l_plan_task_types(1),
decode(mmtt.transaction_action_id,
28,
l_plan_task_types(1),
decode(mmtt.transaction_source_type_id,
5,
l_plan_task_types(1),
13,
decode(mmtt.transaction_type_id,
51,
l_plan_task_types(1),
l_plan_task_types(3))))),
mmtt.transaction_temp_id,
l_plan_task_types(4),
l_plan_task_types(2)) plan_task, --12693583
' ' result,
' ' error,
to_char(mtrh.request_number) source_header,
to_char(mtrl.line_number) line_number,
MSIV.concatenated_segments, --bug12642410
MSIV.DESCRIPTION,
MMTT.REVISION,
MMTT.SUBINVENTORY_CODE,
(mil.concatenated_segments) locator, --bug12642410
MMTT.SECONDARY_TRANSACTION_QUANTITY,
MMTT.SECONDARY_UOM_CODE,
MMTT.OPERATION_PLAN_ID,
WOPV.OPERATION_PLAN_NAME,
WOOI.OPERATION_SEQUENCE,
WOOI.OP_PLAN_INSTANCE_ID,
null SHIP_METHOD,
null shipment_date,
null shipment_priority,
MMTT.DEPARTMENT_CODE,
DECODE(PARENT_TRANSACTION_TEMP_ID, NULL, 'N', 'Y') CHILD_TASKS,
null num_of_child_tasks,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.ALLOCATED_LPN_ID) ALLOCATED_LPN,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CARTONIZATION_ID) CARTONIZED_LPN,
(SELECT concatenated_segments FROM mtl_system_items_kfv WHERE inventory_item_id= MMTT.CONTAINER_ITEM_ID AND organization_id=mmtt.organization_id), --bug12642410
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CONTENT_LPN_ID) CONTENT_LPN,
WDT.DISPATCHED_TIME,
WDT.EQUIPMENT_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.LPN_ID) FROM_LPN,
MMTT.PARENT_LINE_ID,
mmtt.move_order_line_id,
MMTT.PICK_SLIP_NUMBER,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CONTENT_LPN_ID) PICKED_LPN,
mmtt.transaction_uom,
mmtt.transaction_quantity
FROM mtl_material_transactions_temp mmtt,
MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
mtl_system_items_kfv msiv,
wms_op_plans_vl wopv,
per_all_people_f pap,
wms_op_operation_instances wooi,
WMS_WP_WAVE_LINES WWL,
wms_dispatched_tasks wdt,
bom_standard_operations bso,
bom_resources br1,
mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
wms_replenishment_Details wrd
where mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
and mtrl.header_id = mtrh.header_id
--and mtrl.line_id = mmtt.trx_source_line_id changed to move_order_line_id below
and mtrl.line_id = mmtt.move_order_line_id
and mmtt.transaction_source_type_id = 4
and (mtrh.move_order_type = 2)
and (mmtt.transaction_action_id =2)
AND wdt.person_id = pap.person_id(+)
AND wdt.effective_start_date >= pap.effective_start_date(+)
AND wdt.effective_end_date <= pap.effective_end_date(+)
AND mmtt.organization_id = msiv.organization_id
AND mmtt.inventory_item_id = msiv.inventory_item_id
AND mmtt.organization_id = mil.organization_id
AND mmtt.locator_id = mil.inventory_location_id
AND mmtt.transaction_temp_id = wooi.source_task_id(+)
AND mmtt.operation_plan_id = wopv.operation_plan_id(+)
and wrd.organization_id = wrd.organization_id
and wwl.wave_header_id = p_wave_header_id
AND wwl.demand_source_header_id=wrd.demand_header_id
and nvl(wwl.remove_from_wave_flag,'N') <> 'Y'
AND wrd.source_header_id=mtrh.header_id
AND mmtt.standard_operation_id = bso.standard_operation_id(+)
AND mmtt.organization_id = bso.organization_id(+)
AND wdt.person_resource_id = br1.resource_id(+)
union
SELECT DISTINCT wdth.TASK_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = mmt.TRANSFER_LPN_ID) TO_LPN,
mmt.TRANSFER_LPN_ID TO_LPN_ID,
mmt.transaction_id,
mmt.transaction_type_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.transaction_source_id,
mmt.trx_source_line_id,
mmt.organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmt.organization_id) organization_code,
mmt.transfer_organization_id to_organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmt.transfer_organization_id) to_organization_code,
mmt.transfer_subinventory to_subinventory,
mmt.transfer_locator_id to_locator_id,
(SELECT mil.concatenated_segments --bug12642410
FROM MTL_ITEM_LOCATIONS_kfv mil
WHERE mmt.transfer_locator_id = mil.inventory_location_id
AND mmt.organization_id = mil.organization_id) to_locator,
wdth.user_task_type user_task_type_id,
bso.operation_code,
wdth.person_id person_id,
wdth.person_id person_id_original,
pap.full_name person,
wdth.effective_start_date,
wdth.effective_end_date,
wdth.person_resource_id,
br1.resource_code,
6 status_id,
6 status_id_original,
(SELECT meaning
FROM mfg_lookups
WHERE lookup_type = 'WMS_TASK_STATUS'
AND lookup_code = 6) STATUS,
to_date(null)mmtt_last_update_date,
NULL mmtt_last_updated_by,
to_date(null) wdt_last_update_date,
NULL wdt_last_updated_by,
wdth.priority,
wdth.priority priority_original,
wdth.task_type task_type_id,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_TYPES' and lookup_code =wdth.task_type) task_type, --12693583
'N' is_modified,
decode(wdth.is_parent,
'Y',
decode(wdth.transaction_action_id,
28,
l_plan_task_types(4),
decode(wdth.transaction_source_type_id,
5,
decode(wdth.transaction_type_id,
35,
l_plan_task_types(4),
l_plan_task_types(3)),
13,
decode(wdth.transaction_type_id,
51,
l_plan_task_types(4),
l_plan_task_types(3)),
l_plan_task_types(3))),
decode(wdth.parent_transaction_id,
null,
l_plan_task_types(1),
decode(wdth.transaction_action_id,
28,
l_plan_task_types(5),
decode(wdth.transaction_source_type_id,
5,
decode(wdth.transaction_type_id,
35,
l_plan_task_types(5),
l_plan_task_types(2)),
13,
decode(wdth.transaction_type_id,
51,
l_plan_task_types(5),
l_plan_task_types(2)),
l_plan_task_types(2))))) plan_task, --12693583
' ' result,
' ' error,
WDD.SOURCE_HEADER_NUMBER source_header,
WDD.SOURCE_LINE_NUMBER line_number,
MSIV.concatenated_segments, --bug12642410
MSIV.DESCRIPTION,
mmt.REVISION,
mmt.subinventory_code,
(mil.concatenated_segments) locator, --bug12642410
abs(mmt.SECONDARY_TRANSACTION_QUANTITY), -- 13360870
mmt.SECONDARY_UOM_CODE,
WDTH.OPERATION_PLAN_ID,
WOPV.OPERATION_PLAN_NAME,
NULL operation_sequence,
WDTH.OP_PLAN_INSTANCE_ID,
WDD.SHIP_METHOD_CODE SHIP_METHOD,
WDD.DATE_SCHEDULED shipment_date,
WDD.SHIPMENT_PRIORITY_CODE shipment_priority,
To_Char(mmt.DEPARTMENT_id),
DECODE(mmt.PARENT_TRANSACTION_ID, NULL, 'N', 'Y') CHILD_TASKS,
0 num_of_child_tasks,
' ' ALLOCATED_LPN,
' ' CARTONIZED_LPN,
' ' CONTAINER_ITEM_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = mmt.CONTENT_LPN_ID) CONTENT_LPN,
wdth.DISPATCHED_TIME,
wdth.EQUIPMENT_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = mmt.LPN_ID) FROM_LPN,
wdth.parent_transaction_id,
mmt.move_order_line_id,
mmt.PICK_SLIP_NUMBER,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = mmt.CONTENT_LPN_ID) PICKED_LPN,
mmt.transaction_uom,
abs(mmt.transaction_quantity)
FROM mtl_material_transactions mmt,
WSH_DELIVERY_DETAILS WDD,
MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
mtl_system_items_kfv msiv,
wms_op_plans_vl wopv,
per_all_people_f pap,
wms_op_operation_instances wooi,
WMS_WP_WAVE_LINES WWL,
wms_dispatched_tasks_history wdth,
bom_standard_operations bso,
bom_resources br1
where mmt.transaction_set_id = wdth.transaction_id
AND decode(mmt.transfer_transaction_id, null, 0, mmt.transaction_quantity) <= 0
AND nvl(mmt.transaction_batch_id, -1) =
decode(wdth.task_type,
2,
nvl(mmt.transaction_batch_id, -1),
3,
nvl(mmt.transaction_batch_id, -1),
decode(wdth.transaction_batch_id,
-999,
nvl(mmt.transaction_batch_id, -1),
nvl(wdth.transaction_batch_id,
nvl(mmt.transaction_batch_id, -1))))
AND nvl(mmt.transaction_batch_seq, -1) =
decode(wdth.task_type,
2,
nvl(mmt.transaction_batch_seq, -1),
3,
nvl(mmt.transaction_batch_seq, -1),
decode(wdth.transaction_batch_seq,
-999,
nvl(mmt.transaction_batch_seq, -1),
nvl(wdth.transaction_batch_seq,
nvl(mmt.transaction_batch_seq, -1))))
AND wdth.person_id = pap.person_id(+)
AND wdth.effective_start_date >= pap.effective_start_date(+)
AND wdth.effective_end_date <= pap.effective_end_date(+)
AND mmt.organization_id = msiv.organization_id
AND mmt.inventory_item_id = msiv.inventory_item_id
AND mmt.organization_id = mil.organization_id(+)
AND mmt.locator_id = mil.inventory_location_id(+)
-- commented for bug 14293167
/* after performing the shipping transaction in wave workbench, the wdd.source_line_id was being updated
for the split line and following condition was not getting satisfied*/
-- AND WDD.source_line_id = mmt.trx_source_line_id
and WDD.move_order_line_id = mmt.move_order_line_id
AND wdth.transaction_temp_id = wooi.source_task_id(+)
AND wdth.operation_plan_id = wopv.operation_plan_id(+)
AND WWL.delivery_detail_id = WDD.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and wwl.wave_header_id = p_wave_header_id
and nvl(wwl.remove_from_wave_flag,'N') <> 'Y'
AND wdth.user_task_type = bso.standard_operation_id(+)
AND wdth.organization_id = bso.organization_id(+)
and mmt.transaction_action_id = 28
AND wdth.person_resource_id = br1.resource_id(+)
union -- For Crossdocked Tasks
SELECT DISTINCT WDT.TASK_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.TRANSFER_LPN_ID) TO_LPN,
MMTT.TRANSFER_LPN_ID TO_LPN_ID,
mmtt.transaction_temp_id,
mmtt.transaction_type_id,
mmtt.transaction_action_id,
mmtt.transaction_source_type_id,
mmtt.transaction_source_id,
mmtt.trx_source_line_id,
mmtt.organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmtt.organization_id) organization_code,
mmtt.transfer_organization to_organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = mmtt.transfer_organization) to_organization_code,
mmtt.transfer_subinventory to_subinventory,
mmtt.transfer_to_location to_locator_id,
(SELECT mil.concatenated_segments --bug12642410
FROM MTL_ITEM_LOCATIONS_kfv mil
WHERE mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.organization_id = mil.organization_id) to_locator,
mmtt.standard_operation_id user_task_type_id,
bso.operation_code,
wdt.person_id person_id,
wdt.person_id person_id_original,
pap.full_name person,
wdt.effective_start_date,
wdt.effective_end_date,
wdt.person_resource_id,
br1.resource_code,
decode(wdt.status,
null,
nvl(mmtt.wms_task_status, 1),
wdt.status) status_id,
decode(wdt.status,
null,
nvl(mmtt.wms_task_status, 1),
wdt.status) status_id_original,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_STATUS' and lookup_code = decode(wdt.status,null, nvl(mmtt.wms_task_status,1), wdt.status)) STATUS, --12693583
mmtt.last_update_date mmtt_last_update_date,
mmtt.last_updated_by mmtt_last_update_by,
wdt.last_update_date wdt_last_update_date,
wdt.last_updated_by wdt_last_update_by,
mmtt.task_priority,
mmtt.task_priority priority_original,
mmtt.wms_task_type task_type_id,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_TYPES' and lookup_code =mmtt.wms_task_type) task_type, --12693583
'N' is_modified,
decode(mmtt.parent_line_id,
null,
decode(mmtt.operation_plan_id,
null,
l_plan_task_types(1),
decode(mmtt.transaction_action_id,
28,
l_plan_task_types(1),
decode(mmtt.transaction_source_type_id,
5,
l_plan_task_types(1),
13,
decode(mmtt.transaction_type_id,
51,
l_plan_task_types(1),
l_plan_task_types(3))))),
mmtt.transaction_temp_id,
l_plan_task_types(4),
l_plan_task_types(2)) plan_task,
' ' result,
' ' error,
WDD.SOURCE_HEADER_NUMBER source_header,
WDD.SOURCE_LINE_NUMBER line_number,
MSIV.concatenated_segments item, --bug12642410
MSIV.DESCRIPTION item_description,
MMTT.REVISION,
MMTT.SUBINVENTORY_CODE subinventory,
(mil.concatenated_segments) locator,
MMTT.SECONDARY_TRANSACTION_QUANTITY,
MMTT.SECONDARY_UOM_CODE,
MMTT.OPERATION_PLAN_ID,
WOPV.OPERATION_PLAN_NAME,
WOOI.OPERATION_SEQUENCE,
WOOI.OP_PLAN_INSTANCE_ID,
WDD.SHIP_METHOD_CODE SHIP_METHOD,
WDD.DATE_SCHEDULED shipment_date,
WDD.SHIPMENT_PRIORITY_CODE shipment_priority,
MMTT.DEPARTMENT_CODE,
DECODE(PARENT_TRANSACTION_TEMP_ID, NULL, 'N', 'Y') CHILD_TASKS,
null num_of_child_tasks,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.ALLOCATED_LPN_ID) ALLOCATED_LPN,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CARTONIZATION_ID) CARTONIZED_LPN,
(SELECT concatenated_segments FROM mtl_system_items_kfv WHERE inventory_item_id= MMTT.CONTAINER_ITEM_ID AND organization_id=mmtt.organization_id), --bug12642410
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CONTENT_LPN_ID) CONTENT_LPN,
WDT.DISPATCHED_TIME,
WDT.EQUIPMENT_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.LPN_ID) FROM_LPN,
MMTT.PARENT_LINE_ID,
mmtt.move_order_line_id,
MMTT.PICK_SLIP_NUMBER,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = MMTT.CONTENT_LPN_ID) PICKED_LPN,
mmtt.transaction_uom,
mmtt.transaction_quantity
FROM mtl_material_transactions_temp mmtt,
WSH_DELIVERY_DETAILS WDD,
MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
mtl_system_items_kfv msiv,
wms_op_plans_vl wopv,
per_all_people_f pap,
wms_op_operation_instances wooi,
WMS_WP_WAVE_LINES WWL,
wms_dispatched_tasks wdt,
bom_standard_operations bso,
bom_resources br1,
mtl_txn_request_lines mtrl
where mmtt.transaction_temp_id = wdt.transaction_temp_id(+)
AND wdt.person_id = pap.person_id(+)
AND wdt.effective_start_date >= pap.effective_start_date(+)
AND wdt.effective_end_date <= pap.effective_end_date(+)
AND mmtt.organization_id = msiv.organization_id
AND mmtt.inventory_item_id = msiv.inventory_item_id
and mmtt.transaction_action_id = 27
AND mmtt.organization_id = mil.organization_id(+)
AND mmtt.locator_id = mil.inventory_location_id(+)
-- AND WDD.source_line_id = MMTT.trx_source_line_id
and mmtt.move_order_line_id=mtrl.line_id
AND mmtt.transaction_temp_id = wooi.source_task_id(+)
AND mmtt.operation_plan_id = wopv.operation_plan_id(+)
AND WWL.delivery_detail_id = WDD.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and wwl.wave_header_id = p_wave_header_id
and nvl(wwl.remove_from_wave_flag,'N') <> 'Y'
AND mtrl.BACKORDER_DELIVERY_DETAIL_ID = wdd.delivery_detail_id
AND mmtt.standard_operation_id = bso.standard_operation_id(+)
AND mmtt.organization_id = bso.organization_id(+)
AND wdt.person_resource_id = br1.resource_id(+)
union -- Crossdocked Tasks completed
SELECT DISTINCT wdth.TASK_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = wdth.TRANSFER_LPN_ID) TO_LPN,
wdth.TRANSFER_LPN_ID TO_LPN_ID,
wdth.transaction_id,
wdth.transaction_type_id,
wdth.transaction_action_id,
wdth.transaction_source_type_id,
to_number(null) transaction_source_id,
to_number(null) trx_source_line_id,
wdth.organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = wdth.organization_id) organization_code,
wdth.transfer_organization_id to_organization_id,
(SELECT organization_code
FROM mtl_parameters
WHERE organization_id = wdth.transfer_organization_id) to_organization_code,
wdth.dest_subinventory_code to_subinventory,
wdth.dest_locator_id to_locator_id,
(SELECT mil.concatenated_segments --bug12642410
FROM MTL_ITEM_LOCATIONS_kfv mil
WHERE wdth.dest_locator_id = mil.inventory_location_id
AND wdth.organization_id = mil.organization_id) to_locator,
wdth.user_task_type user_task_type_id,
bso.operation_code,
wdth.person_id person_id,
wdth.person_id person_id_original,
pap.full_name person,
wdth.effective_start_date,
wdth.effective_end_date,
wdth.person_resource_id,
br1.resource_code,
6 status_id,
6 status_id_original,
(SELECT meaning
FROM mfg_lookups
WHERE lookup_type = 'WMS_TASK_STATUS'
AND lookup_code = 6) STATUS,
to_date(null)mmtt_last_update_date,
NULL mmtt_last_updated_by,
to_date(null) wdt_last_update_date,
NULL wdt_last_updated_by,
wdth.priority,
wdth.priority priority_original,
wdth.task_type task_type_id,
(select meaning from mfg_lookups where lookup_type = 'WMS_TASK_TYPES' and lookup_code =wdth.task_type) task_type, --12693583
'N' is_modified,
decode(wdth.is_parent,
'Y',
decode(wdth.transaction_action_id,
28,
l_plan_task_types(4),
decode(wdth.transaction_source_type_id,
5,
decode(wdth.transaction_type_id,
35,
l_plan_task_types(4),
l_plan_task_types(3)),
13,
decode(wdth.transaction_type_id,
51,
l_plan_task_types(4),
l_plan_task_types(3)),
l_plan_task_types(3))),
decode(wdth.parent_transaction_id,
null,
l_plan_task_types(1),
decode(wdth.transaction_action_id,
28,
l_plan_task_types(5),
decode(wdth.transaction_source_type_id,
5,
decode(wdth.transaction_type_id,
35,
l_plan_task_types(5),
l_plan_task_types(2)),
13,
decode(wdth.transaction_type_id,
51,
l_plan_task_types(5),
l_plan_task_types(2)),
l_plan_task_types(2))))) plan_task, --12693583
' ' result,
' ' error,
WDD.SOURCE_HEADER_NUMBER source_header,
WDD.SOURCE_LINE_NUMBER line_number,
MSIV.concatenated_segments, --bug12642410
MSIV.DESCRIPTION,
wdth.REVISION,
wdth.source_subinventory_code,
(mil.concatenated_segments) locator, --bug12642410
wdth.SECONDARY_TRANSACTION_QUANTITY,
wdth.SECONDARY_TRANSACTION_UOM_CODE,
WDTH.OPERATION_PLAN_ID,
WOPV.OPERATION_PLAN_NAME,
NULL operation_sequence,
WDTH.OP_PLAN_INSTANCE_ID,
WDD.SHIP_METHOD_CODE SHIP_METHOD,
WDD.DATE_SCHEDULED shipment_date,
WDD.SHIPMENT_PRIORITY_CODE shipment_priority,
to_char(null),
DECODE(wdth.PARENT_TRANSACTION_ID, NULL, 'N', 'Y') CHILD_TASKS,
0 num_of_child_tasks,
' ' ALLOCATED_LPN,
' ' CARTONIZED_LPN,
' ' CONTAINER_ITEM_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = wdth.CONTENT_LPN_ID) CONTENT_LPN,
wdth.DISPATCHED_TIME,
wdth.EQUIPMENT_ID,
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = wdth.LPN_ID) FROM_LPN,
wdth.parent_transaction_id,
wdth.move_order_line_id,
to_number(null),
(SELECT LICENSE_PLATE_NUMBER
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = wdth.CONTENT_LPN_ID) PICKED_LPN,
wdth.transaction_uom_code,
abs(wdth.transaction_quantity)
FROM
WSH_DELIVERY_DETAILS WDD,
MTL_ITEM_LOCATIONS_kfv mil, --bug12642410
mtl_system_items_kfv msiv,
wms_op_plans_vl wopv,
per_all_people_f pap,
wms_op_operation_instances wooi,
WMS_WP_WAVE_LINES WWL,
wms_dispatched_tasks_history wdth,
bom_standard_operations bso,
bom_resources br1,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
where 1=1
AND wdth.person_id = pap.person_id(+)
AND wdth.effective_start_date >= pap.effective_start_date(+)
AND wdth.effective_end_date <= pap.effective_end_date(+)
AND wdth.organization_id = msiv.organization_id
AND wdth.inventory_item_id = msiv.inventory_item_id
AND wdth.organization_id = mil.organization_id(+)
AND wdth.source_locator_id = mil.inventory_location_id(+)
AND wdth.organization_id = mtrl.organization_id
AND wdth.inventory_item_id = mtrl.inventory_item_id
AND wdth.move_order_line_id = mtrl.line_id
and wdth.is_parent = 'N'
AND mtrl.BACKORDER_DELIVERY_DETAIL_ID = wdd.delivery_detail_id
and mtrh.header_id=mtrl.header_id
AND wdth.transaction_temp_id = wooi.source_task_id(+)
AND wdth.operation_plan_id = wopv.operation_plan_id(+)
AND WWL.delivery_detail_id = WDD.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and wwl.wave_header_id = p_wave_header_id
and nvl(wwl.remove_from_wave_flag,'N') <> 'Y'
AND wdth.user_task_type = bso.standard_operation_id(+)
AND wdth.organization_id = bso.organization_id(+)
AND wdth.person_resource_id = br1.resource_id(+)
and wdth.task_type IN (2)
and (
(wdth.transaction_source_type_id = 1
and wdth.transaction_action_id = 27) OR
(wdth.transaction_source_type_id = 12
and wdth.transaction_action_id = 27) OR
(wdth.transaction_source_type_id = 7
and wdth.transaction_action_id = 12) OR
(wdth.transaction_source_type_id = 13
and wdth.transaction_action_id = 12) OR
(wdth.transaction_source_type_id = 4
and wdth.transaction_action_id = 2) OR
(wdth.transaction_source_type_id = 4
and wdth.transaction_action_id = 27)
);
insert into wms_wp_exceptions_gtmp_v(
EXCEPTION_NAME,
EXCEPTION_ID,
LOGGING_ENTITY,
ENTITY_VALUE,
SEVERITY_LEVEL,
EXCEPTION_MESSAGE,
CREATION_DATE,
STATUS,
READY_TO_RELEASE,
BACKORDERED,
CROSSDOCK_PLANNED,
REPLENISHMENT_PLANNED,
TASKED,
PICKED,
PACKED,
STAGED,
LOADED_TO_DOCK,
SHIPPED)
select exception_name,exception_id,exception_entity,
Decode(exception_entity,'Order Line',order_line_id,'Wave',wave_header_id,'Trip',trip_id,'Delivery',delivery_id,'Order', order_number) entity_value, -- to show entity
exception_level,
exception_msg,creation_date,
status,
ready_to_release,
backordered,
crossdock_planned,
replenishment_planned,
tasked,
picked,
packed,
staged,
loaded_to_dock,
shipped
from wms_wp_wave_exceptions_vl where wave_header_id = p_wave_header_id
and lower(status) <> 'closed';
insert into wms_wp_labor_gtmp_v(
RESOURCE_NAME,
NUMBER_OF_ACTUAL_TASKS,
NUMBER_OF_PLANNED_TASKS,
ACTUAL_WORKLOAD,
PLANNED_WAVE_LOAD,
AVAILABLE_CAPACITY,
TOTAL_CAPACITY,
TIME_UOM)
select wls.RESOURCE_NAME,
wls.number_of_actual_tasks,
wls.number_of_planned_tasks,
wls.actual_workload,
wls.planned_wave_load,
wls.available_capacity,
wls.total_capacity,
wpl.time_uom
from wms_wp_labor_statistics wls,
wms_wp_planning_criteria_vl wpl,
wms_wp_wave_headers_vl wwl
WHERE wwl.wave_header_id = p_wave_header_id
and wls.wave_header_id = wwl.wave_header_id
AND wpl.planning_criteria_id = wwl.planning_criteria_id;
END insert_global_temp;
procedure update_line_pick_fill_rate(p_wave_line_id in number)
is
l_pick_fill_rate number;
select
Decode(wdd.released_status,
'Y',
(((get_conversion_rate(wdd.inventory_item_id,
wwtv.transaction_uom,
wdd.requested_quantity_uom) *
wwtv.transaction_quantity) / wdd.requested_quantity) * 100),
'B',
0,
'E',
0)
into l_pick_fill_rate
from wms_wp_wave_lines wwl,
wsh_delivery_details wdd,
wms_wp_wwb_tasks_v wwtv,
wms_wp_wave_headers_vl wwh
where wwl.wave_line_id = p_wave_line_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and wwtv.transaction_source_line_id(+) = wdd.source_line_id
and wwh.wave_header_id = wwl.wave_header_id
and (wwh.wave_status = 'Released')
and nvl(wwl.remove_from_wave_flag, 'N') = 'N'
and wdd.released_status in ('B', 'E', 'Y');
update wms_wp_wave_lines
set pick_fill_rate = l_pick_fill_rate
where wave_line_id = p_wave_line_id;
print_debug('Error in update_line_pick_fill_rate for line id '||p_wave_line_id||' : ' || SQLCODE || ' : ' || SQLERRM, l_debug);
end update_line_pick_fill_rate;
procedure update_line_actual_fill_rate(p_wave_line_id in number)
is
l_crossdocked varchar2(3):='N';
select 'Y' into l_crossdocked from dual where exists
(select 1 from wsh_delivery_details wdd, wms_wp_wave_lines wwl
where wwl.wave_line_id = p_wave_line_id
and wwl.delivery_detail_id = wdd.delivery_detail_id
and wwl.organization_id = wdd.organization_id
and (wdd.released_status = 'K' or (wdd.move_order_line_id is null and wdd.released_status = 'S')));
select sum((get_conversion_rate(wdd.inventory_item_id,
mmtt.transaction_uom,
wdd.requested_quantity_uom) *
Nvl(MMTT.TRANSACTION_QUANTITY, 0)))
into l_allocated_quantity
from wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt,
wms_wp_wave_lines wwl
where wdd.move_order_line_id = mmtt.move_order_line_id
and wdd.delivery_detail_id = wwl.delivery_detail_id
and wdd.organization_id = wwl.organization_id
and wwl.wave_line_id = p_wave_line_id;
select round(decode(wdd.released_status, 'B', 0, l_allocated_quantity)*100/wdd.requested_quantity)
into l_fill_rate
from wsh_delivery_details wdd,
wms_wp_wave_lines wwl
where wwl.wave_line_id = p_wave_line_id
and wwl.organization_id = wdd.organization_id
and wwl.delivery_detail_id = wdd.delivery_detail_id;
update wms_wp_wave_lines
set release_fill_rate = l_fill_rate
where wave_line_id = p_wave_line_id;
print_debug('Error in update_line_actual_fill_rate for line_id '||p_wave_line_id||' : ' || SQLCODE || ' : ' || SQLERRM, l_debug);
end update_line_actual_fill_rate;
select WMS_WP_WAVE_HEADERS_S.NEXTVAL into l_wave_header_id from dual;
INSERT INTO wms_wp_wave_headers_vl
(wave_header_id,
WAVE_NAME,
WAVE_DESCRIPTION,
start_time, -- start time changes
WAVE_SOURCE,
WAVE_STATUS,
TYPE_ID,
BATCH_ID,
SHIP_TO_LOCATION_ID,
CUSTOMER_CLASS_ID,
pull_replenishment_flag,
INITIATE_WAVE_PLANNING,
RELEASE_IMMEDIATELY,
TABLE_NAME,
ADVANCED_CRITERIA,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ORGANIZATION_ID,
PICK_SEQ_RULE_ID,
PICK_GROUPING_RULE_ID,
TRIP_ID,
TRIP_STOP_ID,
SHIP_METHOD_CODE,
SHIPMENT_PRIORITY_CODE,
CARRIER_ID,
DELIVERY_ID,
FROM_ORDER_HEADER_ID,
ORDER_TYPE_ID,
CUSTOMER_ID,
TASK_ID,
PROJECT_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
INVENTORY_ITEM_ID,
BACKORDERS_FLAG,
INCLUDE_PLANNED_LINES,
TASK_PLANNING_FLAG,
APPEND_DELIVERIES,
AUTO_CREATE_DELIVERY,
AUTO_CREATE_DELIVERY_CRITERIA,
TASK_PRIORITY,
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
DEFAULT_ALLOCATION_METHOD,
WAVE_FIRMED_FLAG,
WAVE_COMPLETION_TIME,
ORDER_NAME,
CUSTOMER,
ORDER_TYPE,
CUSTOMER_CLASS,
SHIP_METHOD,
CARRIER,
SHIP_PRIORITY,
DELIVERY,
TRIP,
TRIP_STOP,
ITEM,
ITEM_CATEGORY,
PROJECT_NAME,
TASK_NAME,
SCHEDULED_DAYS,
SCHEDULED_HRS,
DOCK_APPOINTMENT_DAYS,
DOCK_APPOINTMENT_HOURS,
PICK_SLIP_GROUP,
RELEASE_SEQ_RULE,
STAGING_SUBINVENTORY,
STAGING_LOCATOR,
CROSS_DOCK_CRITERIA,
PLANNING_CRITERIA,
PLANNING_CRITERIA_ID,
pick_subinventory)
VALUES
(l_wave_header_id,
P_WAVE_NAME || '-' || l_wave_header_id,
P_WAVE_DESCRIPTION,
P_start_time, -- start time changes
'OE',
'Created', --Need to Confirm??????
'W',
null,
P_SHIP_TO_LOCATION_ID,
P_CUSTOMER_CLASS_ID,
P_pull_replenishment_flag,
'N',
P_RELEASE_IMMEDIATELY,
null,
null,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
P_ORGANIZATION_ID,
P_PICK_SEQ_RULE_ID,
P_PICK_GROUPING_RULE_ID,
P_TRIP_ID,
P_TRIP_STOP_ID,
P_SHIP_METHOD_CODE,
P_SHIPMENT_PRIORITY_CODE,
P_CARRIER_ID,
P_DELIVERY_ID,
P_FROM_ORDER_HEADER_ID,
P_ORDER_TYPE_ID,
P_CUSTOMER_ID,
null,
null,
P_CATEGORY_SET_ID,
P_CATEGORY_ID,
P_INVENTORY_ITEM_ID,
null,
P_INCLUDE_PLANNED_LINES,
P_TASK_PLANNING_FLAG,
P_APPEND_DELIVERIES,
P_AUTO_CREATE_DELIVERY,
P_AUTODELIVERY_CRITERIA,
P_TASK_PRIORITY,
P_DEFAULT_STAGE_SUBINVENTORY,
P_DEFAULT_STAGE_LOCATOR_ID,
P_DEFAULT_ALLOCATION_METHOD,
P_WAVE_FIRMED_FLAG,
null,
P_ORDER_NAME,
P_CUSTOMER,
P_ORDER_TYPE,
P_CUSTOMER_CLASS,
P_SHIP_METHOD,
P_CARRIER,
P_SHIP_PRIORITY,
P_DELIVERY,
P_TRIP,
P_TRIP_STOP,
P_ITEM,
P_ITEM_CATEGORY,
null,
null,
P_SCHEDULED_DAYS,
P_SCHEDULED_HRS,
P_DOCK_APPOINTMENT_DAYS,
P_DOCK_APPOINTMENT_HOURS,
P_PICK_SLIP_GROUP,
P_RELEASE_SEQ_RULE,
P_STAGING_SUBINVENTORY,
P_STAGING_LOCATOR,
P_CROSS_DOCK_CRITERIA,
null,
null,
P_pick_subinventory);
print_debug('Inserted Wave Record LAUNCH_WAVE_ENDECA ' || ' : ' ||
SQL%ROWCOUNT,
l_debug);
update wms_wp_wave_headers_vl
set release_immediately = 'Y'
WHERE WAVE_HEADER_ID = P_WAVE_HEADER_ID;
/*UPDATE wms_wp_wave_lines
SET message = 'This line has been Firmed in Wave '||p_wave_header_id,
remove_from_Wave_flag = 'Y'
WHERE delivery_detail_id IN
(SELECT delivery_detail_id FROM wms_wp_wave_lines WHERE wave_header_id = p_wave_header_id
and nvl(remove_from_wave_flag,'N')<>'Y')
and wave_header_id != p_wave_header_id;
update wms_wp_wave_headers_vl
set wave_firmed_flag = 'Y'
where wave_header_id = p_wave_header_id;
delete from wms_wp_wave_lines
where wave_line_id in
(select wave_line_id
from wms_wp_wave_lines wwl, wsh_delivery_details wdd
where wwl.wave_header_id = p_wave_header_id
and wdd.delivery_detail_id = wwl.delivery_Detail_id
and wdd.organization_id = wwl.organization_id
and wdd.source_line_number = p_line_number
and wdd.source_header_number = p_order_number);