[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*), min(last_update_date), max(last_update_date),
(max(last_update_date) - min(last_update_date))
from wms_dispatched_tasks_history
where last_update_date < (sysdate - l_purge_days)
and organization_id = nvl(p_org_id, organization_id);
select (l_from_date + l_each_worker_chunk) into l_to_date from dual;
trace(l_proc || ' Start of insert into wms_dispatched_tasks_arch ...');
insert into wms_dispatched_tasks_arch(
TASK_ID
,TRANSACTION_ID
,ORGANIZATION_ID
,USER_TASK_TYPE
,PERSON_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,EQUIPMENT_ID
,EQUIPMENT_INSTANCE
,PERSON_RESOURCE_ID
,MACHINE_RESOURCE_ID
,STATUS
,DISPATCHED_TIME
,LOADED_TIME
,DROP_OFF_TIME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,TASK_TYPE
,PRIORITY
,TASK_GROUP_ID
,SUGGESTED_DEST_SUBINVENTORY
,SUGGESTED_DEST_LOCATOR_ID
,OPERATION_PLAN_ID
,MOVE_ORDER_LINE_ID
,TRANSFER_LPN_ID
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,INVENTORY_ITEM_ID
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM_CODE
,SOURCE_SUBINVENTORY_CODE
,SOURCE_LOCATOR_ID
,DEST_SUBINVENTORY_CODE
,DEST_LOCATOR_ID
,LPN_ID
,CONTENT_LPN_ID
,IS_PARENT
,PARENT_TRANSACTION_ID
,TRANSFER_ORGANIZATION_ID
,SOURCE_DOCUMENT_ID
,OP_PLAN_INSTANCE_ID
,TASK_METHOD
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_ACTION_ID)
select
wdth.TASK_ID
,wdth.TRANSACTION_ID
,wdth.ORGANIZATION_ID
,wdth.USER_TASK_TYPE
,wdth.PERSON_ID
,wdth.EFFECTIVE_START_DATE
,wdth.EFFECTIVE_END_DATE
,wdth.EQUIPMENT_ID
,wdth.EQUIPMENT_INSTANCE
,wdth.PERSON_RESOURCE_ID
,wdth.MACHINE_RESOURCE_ID
,wdth.STATUS
,wdth.DISPATCHED_TIME
,wdth.LOADED_TIME
,wdth.DROP_OFF_TIME
,wdth.LAST_UPDATE_DATE
,wdth.LAST_UPDATED_BY
,wdth.CREATION_DATE
,wdth.CREATED_BY
,wdth.LAST_UPDATE_LOGIN
,wdth.ATTRIBUTE_CATEGORY
,wdth.ATTRIBUTE1
,wdth.ATTRIBUTE2
,wdth.ATTRIBUTE3
,wdth.ATTRIBUTE4
,wdth.ATTRIBUTE5
,wdth.ATTRIBUTE6
,wdth.ATTRIBUTE7
,wdth.ATTRIBUTE8
,wdth.ATTRIBUTE9
,wdth.ATTRIBUTE10
,wdth.ATTRIBUTE11
,wdth.ATTRIBUTE12
,wdth.ATTRIBUTE13
,wdth.ATTRIBUTE14
,wdth.ATTRIBUTE15
,wdth.TASK_TYPE
,wdth.PRIORITY
,wdth.TASK_GROUP_ID
,wdth.SUGGESTED_DEST_SUBINVENTORY
,wdth.SUGGESTED_DEST_LOCATOR_ID
,wdth.OPERATION_PLAN_ID
,wdth.MOVE_ORDER_LINE_ID
,wdth.TRANSFER_LPN_ID
,wdth.TRANSACTION_BATCH_ID
,wdth.TRANSACTION_BATCH_SEQ
,wdth.INVENTORY_ITEM_ID
,wdth.REVISION
,wdth.TRANSACTION_QUANTITY
,wdth.TRANSACTION_UOM_CODE
,wdth.SOURCE_SUBINVENTORY_CODE
,wdth.SOURCE_LOCATOR_ID
,wdth.DEST_SUBINVENTORY_CODE
,wdth.DEST_LOCATOR_ID
,wdth.LPN_ID
,wdth.CONTENT_LPN_ID
,wdth.IS_PARENT
,wdth.PARENT_TRANSACTION_ID
,wdth.TRANSFER_ORGANIZATION_ID
,wdth.SOURCE_DOCUMENT_ID
,wdth.OP_PLAN_INSTANCE_ID
,wdth.TASK_METHOD
,wdth.TRANSACTION_TYPE_ID
,wdth.TRANSACTION_SOURCE_TYPE_ID
,wdth.TRANSACTION_ACTION_ID
from wms_dispatched_tasks_history wdth, wms_op_plan_instances_hist wopih
where wdth.last_update_date > l_min_date
and wdth.last_update_date <= l_max_date
and wdth.parent_transaction_id is not null
and wdth.task_type in (2,8)
and wdth.op_plan_instance_id = wopih.op_plan_instance_id
and wdth.organization_id = wopih.organization_id
and wdth.organization_id = nvl(p_org_id, wdth.organization_id);
trace(l_proc || ' End of insert into wms_dispatched_tasks_arch ...');
trace(l_proc || ' Start of insert into wms_dispatched_tasks_arch ...');
insert into wms_dispatched_tasks_arch(
TASK_ID
,TRANSACTION_ID
,ORGANIZATION_ID
,USER_TASK_TYPE
,PERSON_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,EQUIPMENT_ID
,EQUIPMENT_INSTANCE
,PERSON_RESOURCE_ID
,MACHINE_RESOURCE_ID
,STATUS
,DISPATCHED_TIME
,LOADED_TIME
,DROP_OFF_TIME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,TASK_TYPE
,PRIORITY
,TASK_GROUP_ID
,SUGGESTED_DEST_SUBINVENTORY
,SUGGESTED_DEST_LOCATOR_ID
,OPERATION_PLAN_ID
,MOVE_ORDER_LINE_ID
,TRANSFER_LPN_ID
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,INVENTORY_ITEM_ID
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM_CODE
,SOURCE_SUBINVENTORY_CODE
,SOURCE_LOCATOR_ID
,DEST_SUBINVENTORY_CODE
,DEST_LOCATOR_ID
,LPN_ID
,CONTENT_LPN_ID
,IS_PARENT
,PARENT_TRANSACTION_ID
,TRANSFER_ORGANIZATION_ID
,SOURCE_DOCUMENT_ID
,OP_PLAN_INSTANCE_ID
,TASK_METHOD
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_ACTION_ID)
select
TASK_ID
,TRANSACTION_ID
,ORGANIZATION_ID
,USER_TASK_TYPE
,PERSON_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,EQUIPMENT_ID
,EQUIPMENT_INSTANCE
,PERSON_RESOURCE_ID
,MACHINE_RESOURCE_ID
,STATUS
,DISPATCHED_TIME
,LOADED_TIME
,DROP_OFF_TIME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,TASK_TYPE
,PRIORITY
,TASK_GROUP_ID
,SUGGESTED_DEST_SUBINVENTORY
,SUGGESTED_DEST_LOCATOR_ID
,OPERATION_PLAN_ID
,MOVE_ORDER_LINE_ID
,TRANSFER_LPN_ID
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,INVENTORY_ITEM_ID
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM_CODE
,SOURCE_SUBINVENTORY_CODE
,SOURCE_LOCATOR_ID
,DEST_SUBINVENTORY_CODE
,DEST_LOCATOR_ID
,LPN_ID
,CONTENT_LPN_ID
,IS_PARENT
,PARENT_TRANSACTION_ID
,TRANSFER_ORGANIZATION_ID
,SOURCE_DOCUMENT_ID
,OP_PLAN_INSTANCE_ID
,TASK_METHOD
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_ACTION_ID
from wms_dispatched_tasks_history wdth
where wdth.last_update_date > l_min_date
and wdth.last_update_date <= l_max_date
and (wdth.parent_transaction_id is null or wdth.task_type not in (2,8))
and wdth.organization_id = nvl(p_org_id, wdth.organization_id);
trace(l_proc || ' End of insert into wms_dispatched_tasks_arch ...');
trace(l_proc || ' Start of insert into wms_op_plan_instances_arch ...');
insert into wms_op_plan_instances_arch(
OP_PLAN_INSTANCE_ID
,OPERATION_PLAN_ID
,STATUS
,ORGANIZATION_ID
,PLAN_EXECUTION_START_DATE
,PLAN_EXECUTION_END_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ACTIVITY_TYPE_ID
,PLAN_TYPE_ID
,ORIG_SOURCE_SUB_CODE
,ORIG_SOURCE_LOC_ID
,ORIG_DEST_SUB_CODE
,ORIG_DEST_LOC_ID)
select
OP_PLAN_INSTANCE_ID
,OPERATION_PLAN_ID
,STATUS
,ORGANIZATION_ID
,PLAN_EXECUTION_START_DATE
,PLAN_EXECUTION_END_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ACTIVITY_TYPE_ID
,PLAN_TYPE_ID
,ORIG_SOURCE_SUB_CODE
,ORIG_SOURCE_LOC_ID
,ORIG_DEST_SUB_CODE
,ORIG_DEST_LOC_ID
from wms_op_plan_instances_hist wopih
where wopih.last_update_date > l_min_date
and wopih.last_update_date <= l_max_date
and wopih.organization_id = nvl(p_org_id, wopih.organization_id);
trace(l_proc || ' End of insert into wms_op_plan_instances_arch ...');
trace(l_proc || ' Start of insert into wms_op_opertn_instances_arch ...');
insert into wms_op_opertn_instances_arch(
OPERATION_INSTANCE_ID
,OP_PLAN_INSTANCE_ID
,ORGANIZATION_ID
,OPERATION_STATUS
,OPERATION_PLAN_DETAIL_ID
,OPERATION_SEQUENCE
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,OPERATION_TYPE_ID
,ACTIVITY_TYPE_ID
,SUG_TO_SUB_CODE
,SUG_TO_LOCATOR_ID
,SOURCE_TASK_ID
,EMPLOYEE_ID
,EQUIPMENT_ID
,ACTIVATE_TIME
,COMPLETE_TIME
,IS_IN_INVENTORY)
select
OPERATION_INSTANCE_ID
,OP_PLAN_INSTANCE_ID
,ORGANIZATION_ID
,OPERATION_STATUS
,OPERATION_PLAN_DETAIL_ID
,OPERATION_SEQUENCE
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,OPERATION_TYPE_ID
,ACTIVITY_TYPE_ID
,SUG_TO_SUB_CODE
,SUG_TO_LOCATOR_ID
,SOURCE_TASK_ID
,EMPLOYEE_ID
,EQUIPMENT_ID
,ACTIVATE_TIME
,COMPLETE_TIME
,IS_IN_INVENTORY
from wms_op_opertn_instances_hist wooih
where wooih.last_update_date > l_min_date
and wooih.last_update_date <= l_max_date
and wooih.organization_id = nvl(p_org_id, wooih.organization_id);
trace(l_proc || ' End of insert into wms_op_opertn_instances_arch ...');
trace(l_proc || ' Start of delete from wms_dispatched_tasks_history ...');
delete from wms_dispatched_tasks_history
where task_id in (
select wdth.task_id
from wms_dispatched_tasks_history wdth, wms_op_plan_instances_hist wopih
where wdth.last_update_date > l_min_date
and wdth.last_update_date <= l_max_date
and wdth.parent_transaction_id is not null
and wdth.task_type in (2,8)
and wdth.op_plan_instance_id = wopih.op_plan_instance_id
and wdth.organization_id = wopih.organization_id
and wdth.organization_id = nvl(p_org_id, wdth.organization_id));
trace(l_proc || ' End of delete from wms_dispatched_tasks_history ...');
trace(l_proc || ' Start of delete from wms_dispatched_tasks_history ...');
delete from wms_dispatched_tasks_history wdth
where wdth.last_update_date > l_min_date
and wdth.last_update_date <= l_max_date
and (wdth.parent_transaction_id is null or wdth.task_type not in (2,8))
and wdth.organization_id = nvl(p_org_id, wdth.organization_id);
trace(l_proc || ' End of delete from wms_dispatched_tasks_history ...');
trace(l_proc || ' Start of delete from wms_op_plan_instances_hist ...');
delete from wms_op_plan_instances_hist wopih
where wopih.last_update_date > l_min_date
and wopih.last_update_date <= l_max_date
and wopih.organization_id = nvl(p_org_id, wopih.organization_id);
trace(l_proc || ' End of delete from wms_op_plan_instances_hist ...');
trace(l_proc || ' Start of delete from wms_op_opertn_instances_hist ...');
delete from wms_op_opertn_instances_hist wooih
where wooih.last_update_date > l_min_date
and wooih.last_update_date <= l_max_date
and wooih.organization_id = nvl(p_org_id, wooih.organization_id);
trace(l_proc || ' End of delete from wms_op_opertn_instances_hist ...');
trace(l_proc || ' Start of delete from wms_exceptions ...');
delete from wms_exceptions wex
where wex.creation_date > l_min_date
and wex.creation_date <= l_max_date
and wex.organization_id = nvl(p_org_id, wex.organization_id);
trace(l_proc || ' End of delete from wms_exceptions ...');
select count(*), (l_max_date - l_min_date)
from wms_dispatched_tasks_arch
where last_update_date > l_min_date
and last_update_date <= l_max_date
and organization_id = nvl(p_org_id, organization_id);
trace(l_proc || ' Start of Insert based on the exception condition...');
insert into wms_dispatched_tasks_history(
TASK_ID
,TRANSACTION_ID
,ORGANIZATION_ID
,USER_TASK_TYPE
,PERSON_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,EQUIPMENT_ID
,EQUIPMENT_INSTANCE
,PERSON_RESOURCE_ID
,MACHINE_RESOURCE_ID
,STATUS
,DISPATCHED_TIME
,LOADED_TIME
,DROP_OFF_TIME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,TASK_TYPE
,PRIORITY
,TASK_GROUP_ID
,SUGGESTED_DEST_SUBINVENTORY
,SUGGESTED_DEST_LOCATOR_ID
,OPERATION_PLAN_ID
,MOVE_ORDER_LINE_ID
,TRANSFER_LPN_ID
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,INVENTORY_ITEM_ID
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM_CODE
,SOURCE_SUBINVENTORY_CODE
,SOURCE_LOCATOR_ID
,DEST_SUBINVENTORY_CODE
,DEST_LOCATOR_ID
,LPN_ID
,CONTENT_LPN_ID
,IS_PARENT
,PARENT_TRANSACTION_ID
,TRANSFER_ORGANIZATION_ID
,SOURCE_DOCUMENT_ID
,OP_PLAN_INSTANCE_ID
,TASK_METHOD
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_ACTION_ID)
select
wdta.TASK_ID
,wdta.TRANSACTION_ID
,wdta.ORGANIZATION_ID
,wdta.USER_TASK_TYPE
,wdta.PERSON_ID
,wdta.EFFECTIVE_START_DATE
,wdta.EFFECTIVE_END_DATE
,wdta.EQUIPMENT_ID
,wdta.EQUIPMENT_INSTANCE
,wdta.PERSON_RESOURCE_ID
,wdta.MACHINE_RESOURCE_ID
,wdta.STATUS
,wdta.DISPATCHED_TIME
,wdta.LOADED_TIME
,wdta.DROP_OFF_TIME
,wdta.LAST_UPDATE_DATE
,wdta.LAST_UPDATED_BY
,wdta.CREATION_DATE
,wdta.CREATED_BY
,wdta.LAST_UPDATE_LOGIN
,wdta.ATTRIBUTE_CATEGORY
,wdta.ATTRIBUTE1
,wdta.ATTRIBUTE2
,wdta.ATTRIBUTE3
,wdta.ATTRIBUTE4
,wdta.ATTRIBUTE5
,wdta.ATTRIBUTE6
,wdta.ATTRIBUTE7
,wdta.ATTRIBUTE8
,wdta.ATTRIBUTE9
,wdta.ATTRIBUTE10
,wdta.ATTRIBUTE11
,wdta.ATTRIBUTE12
,wdta.ATTRIBUTE13
,wdta.ATTRIBUTE14
,wdta.ATTRIBUTE15
,wdta.TASK_TYPE
,wdta.PRIORITY
,wdta.TASK_GROUP_ID
,wdta.SUGGESTED_DEST_SUBINVENTORY
,wdta.SUGGESTED_DEST_LOCATOR_ID
,wdta.OPERATION_PLAN_ID
,wdta.MOVE_ORDER_LINE_ID
,wdta.TRANSFER_LPN_ID
,wdta.TRANSACTION_BATCH_ID
,wdta.TRANSACTION_BATCH_SEQ
,wdta.INVENTORY_ITEM_ID
,wdta.REVISION
,wdta.TRANSACTION_QUANTITY
,wdta.TRANSACTION_UOM_CODE
,wdta.SOURCE_SUBINVENTORY_CODE
,wdta.SOURCE_LOCATOR_ID
,wdta.DEST_SUBINVENTORY_CODE
,wdta.DEST_LOCATOR_ID
,wdta.LPN_ID
,wdta.CONTENT_LPN_ID
,wdta.IS_PARENT
,wdta.PARENT_TRANSACTION_ID
,wdta.TRANSFER_ORGANIZATION_ID
,wdta.SOURCE_DOCUMENT_ID
,wdta.OP_PLAN_INSTANCE_ID
,wdta.TASK_METHOD
,wdta.TRANSACTION_TYPE_ID
,wdta.TRANSACTION_SOURCE_TYPE_ID
,wdta.TRANSACTION_ACTION_ID
from wms_dispatched_tasks_arch wdta, wms_op_plan_instances_arch wopia
where wdta.last_update_date < l_min_date
and wopia.op_plan_instance_id = wdta.op_plan_instance_id
and wdta.organization_id = wopia.organization_id
and wdta.organization_id = nvl(p_org_id, wdta.organization_id)
and wopia.last_update_date > l_min_date
and wopia.last_update_date <= l_max_date;
trace(l_proc || ' End of Insert based on the exception condition...');
trace(l_proc || ' Start of Delete based on the exception condition...');
delete from wms_dispatched_tasks_arch
where task_id in (
select wdta.task_id
from wms_dispatched_tasks_arch wdta, wms_op_plan_instances_arch wopia
where wdta.last_update_date <= l_min_date
and wopia.op_plan_instance_id = wdta.op_plan_instance_id
and wdta.organization_id = wopia.organization_id
and wdta.organization_id = nvl(p_org_id, wdta.organization_id)
and wopia.last_update_date > l_min_date
and wopia.last_update_date <= l_max_date);
trace(l_proc || ' End of Delete based on the exception condition...');
select (l_from_date + l_each_worker_chunk) into l_to_date from dual;
trace(l_proc || ' Start of insert into wms_dispatched_tasks_history ...');
insert into wms_dispatched_tasks_history(
TASK_ID
,TRANSACTION_ID
,ORGANIZATION_ID
,USER_TASK_TYPE
,PERSON_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,EQUIPMENT_ID
,EQUIPMENT_INSTANCE
,PERSON_RESOURCE_ID
,MACHINE_RESOURCE_ID
,STATUS
,DISPATCHED_TIME
,LOADED_TIME
,DROP_OFF_TIME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,TASK_TYPE
,PRIORITY
,TASK_GROUP_ID
,SUGGESTED_DEST_SUBINVENTORY
,SUGGESTED_DEST_LOCATOR_ID
,OPERATION_PLAN_ID
,MOVE_ORDER_LINE_ID
,TRANSFER_LPN_ID
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,INVENTORY_ITEM_ID
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM_CODE
,SOURCE_SUBINVENTORY_CODE
,SOURCE_LOCATOR_ID
,DEST_SUBINVENTORY_CODE
,DEST_LOCATOR_ID
,LPN_ID
,CONTENT_LPN_ID
,IS_PARENT
,PARENT_TRANSACTION_ID
,TRANSFER_ORGANIZATION_ID
,SOURCE_DOCUMENT_ID
,OP_PLAN_INSTANCE_ID
,TASK_METHOD
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_ACTION_ID)
select
wdta.TASK_ID
,wdta.TRANSACTION_ID
,wdta.ORGANIZATION_ID
,wdta.USER_TASK_TYPE
,wdta.PERSON_ID
,wdta.EFFECTIVE_START_DATE
,wdta.EFFECTIVE_END_DATE
,wdta.EQUIPMENT_ID
,wdta.EQUIPMENT_INSTANCE
,wdta.PERSON_RESOURCE_ID
,wdta.MACHINE_RESOURCE_ID
,wdta.STATUS
,wdta.DISPATCHED_TIME
,wdta.LOADED_TIME
,wdta.DROP_OFF_TIME
,wdta.LAST_UPDATE_DATE
,wdta.LAST_UPDATED_BY
,wdta.CREATION_DATE
,wdta.CREATED_BY
,wdta.LAST_UPDATE_LOGIN
,wdta.ATTRIBUTE_CATEGORY
,wdta.ATTRIBUTE1
,wdta.ATTRIBUTE2
,wdta.ATTRIBUTE3
,wdta.ATTRIBUTE4
,wdta.ATTRIBUTE5
,wdta.ATTRIBUTE6
,wdta.ATTRIBUTE7
,wdta.ATTRIBUTE8
,wdta.ATTRIBUTE9
,wdta.ATTRIBUTE10
,wdta.ATTRIBUTE11
,wdta.ATTRIBUTE12
,wdta.ATTRIBUTE13
,wdta.ATTRIBUTE14
,wdta.ATTRIBUTE15
,wdta.TASK_TYPE
,wdta.PRIORITY
,wdta.TASK_GROUP_ID
,wdta.SUGGESTED_DEST_SUBINVENTORY
,wdta.SUGGESTED_DEST_LOCATOR_ID
,wdta.OPERATION_PLAN_ID
,wdta.MOVE_ORDER_LINE_ID
,wdta.TRANSFER_LPN_ID
,wdta.TRANSACTION_BATCH_ID
,wdta.TRANSACTION_BATCH_SEQ
,wdta.INVENTORY_ITEM_ID
,wdta.REVISION
,wdta.TRANSACTION_QUANTITY
,wdta.TRANSACTION_UOM_CODE
,wdta.SOURCE_SUBINVENTORY_CODE
,wdta.SOURCE_LOCATOR_ID
,wdta.DEST_SUBINVENTORY_CODE
,wdta.DEST_LOCATOR_ID
,wdta.LPN_ID
,wdta.CONTENT_LPN_ID
,wdta.IS_PARENT
,wdta.PARENT_TRANSACTION_ID
,wdta.TRANSFER_ORGANIZATION_ID
,wdta.SOURCE_DOCUMENT_ID
,wdta.OP_PLAN_INSTANCE_ID
,wdta.TASK_METHOD
,wdta.TRANSACTION_TYPE_ID
,wdta.TRANSACTION_SOURCE_TYPE_ID
,wdta.TRANSACTION_ACTION_ID
from wms_dispatched_tasks_arch wdta
where wdta.last_update_date > l_min_date
and wdta.last_update_date <= l_max_date
and wdta.organization_id = nvl(p_org_id, wdta.organization_id);
trace(l_proc || ' End of insert into wms_dispatched_tasks_history ...');
trace(l_proc || ' Start of insert into wms_op_plan_instance_hist ...');
insert into wms_op_plan_instances_hist(
OP_PLAN_INSTANCE_ID
,OPERATION_PLAN_ID
,STATUS
,ORGANIZATION_ID
,PLAN_EXECUTION_START_DATE
,PLAN_EXECUTION_END_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ACTIVITY_TYPE_ID
,PLAN_TYPE_ID
,ORIG_SOURCE_SUB_CODE
,ORIG_SOURCE_LOC_ID
,ORIG_DEST_SUB_CODE
,ORIG_DEST_LOC_ID)
select
OP_PLAN_INSTANCE_ID
,OPERATION_PLAN_ID
,STATUS
,ORGANIZATION_ID
,PLAN_EXECUTION_START_DATE
,PLAN_EXECUTION_END_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ACTIVITY_TYPE_ID
,PLAN_TYPE_ID
,ORIG_SOURCE_SUB_CODE
,ORIG_SOURCE_LOC_ID
,ORIG_DEST_SUB_CODE
,ORIG_DEST_LOC_ID
from wms_op_plan_instances_arch wopia
where wopia.last_update_date > l_min_date
and wopia.last_update_date <= l_max_date
and wopia.organization_id = nvl(p_org_id, wopia.organization_id);
trace(l_proc || ' End of insert into wms_op_plan_instance_hist ...');
trace(l_proc || ' Start of insert into wms_op_opertn_instances_hist ...');
insert into wms_op_opertn_instances_hist(
OPERATION_INSTANCE_ID
,OP_PLAN_INSTANCE_ID
,ORGANIZATION_ID
,OPERATION_STATUS
,OPERATION_PLAN_DETAIL_ID
,OPERATION_SEQUENCE
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,OPERATION_TYPE_ID
,ACTIVITY_TYPE_ID
,SUG_TO_SUB_CODE
,SUG_TO_LOCATOR_ID
,SOURCE_TASK_ID
,EMPLOYEE_ID
,EQUIPMENT_ID
,ACTIVATE_TIME
,COMPLETE_TIME
,IS_IN_INVENTORY)
select
OPERATION_INSTANCE_ID
--,OPERATION_TYPE
,OP_PLAN_INSTANCE_ID
,ORGANIZATION_ID
,OPERATION_STATUS
,OPERATION_PLAN_DETAIL_ID
,OPERATION_SEQUENCE
--,LPN_ID
--,FROM_ZONE_ID
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
--,TO_ZONE_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,OPERATION_TYPE_ID
,ACTIVITY_TYPE_ID
,SUG_TO_SUB_CODE
,SUG_TO_LOCATOR_ID
,SOURCE_TASK_ID
,EMPLOYEE_ID
,EQUIPMENT_ID
,ACTIVATE_TIME
,COMPLETE_TIME
,IS_IN_INVENTORY
from wms_op_opertn_instances_arch wooia
where wooia.last_update_date > l_min_date
and wooia.last_update_date <= l_max_date
and wooia.organization_id = nvl(p_org_id, wooia.organization_id);
trace(l_proc || ' End of insert into wms_op_opertn_instances_hist ...');
trace(l_proc || ' Start of delete from wms_dispatched_tasks_arch ...');
delete from wms_dispatched_tasks_arch wdta
where wdta.last_update_date > l_min_date
and wdta.last_update_date <= l_max_date
and wdta.organization_id = nvl(p_org_id, wdta.organization_id);
trace(l_proc || ' End of delete from wms_dispatched_tasks_arch ...');
trace(l_proc || ' Start of delete from wms_op_plan_instances_arch ...');
delete from wms_op_plan_instances_arch wopia
where wopia.last_update_date > l_min_date
and wopia.last_update_date <= l_max_date
and wopia.organization_id = nvl(p_org_id, wopia.organization_id);
trace(l_proc || ' End of delete from wms_op_plan_instances_arch ...');
trace(l_proc || ' Start of delete from wms_op_opertn_instances_arch ...');
delete from wms_op_opertn_instances_arch wooia
where wooia.last_update_date > l_min_date
and wooia.last_update_date <= l_max_date
and wooia.organization_id = nvl(p_org_id, wooia.organization_id);
trace(l_proc || ' End of delete from wms_op_opertn_instances_arch ...');