[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Procedure:Insert_Plan_instance
* This procedure inserts data into the table WMS_OP_PLAN_INSTANCES.
* @param p_insert_rec - Record Variable of type WMS_OP_PLAN_INSTANCES%rowtype
* @param x_return_status - Return Status
* @param x_msg_count - Returns the Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE insert_plan_instance
(p_insert_rec IN WMS_OP_PLAN_INSTANCES%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE) IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_module_name CONSTANT VARCHAR2(30) := 'Insert_Plan_Instance';
print_debug('Plan Instance Id:'||p_insert_rec.op_plan_instance_id,l_module_name,3);
print_debug('Inserting Records into WMS_OP_PLAN_INSTANCES',l_module_name,9);
INSERT INTO WMS_OP_PLAN_INSTANCES
(OP_PLAN_INSTANCE_ID,
OPERATION_PLAN_ID,
ACTIVITY_TYPE_ID,
PLAN_TYPE_ID,
SOURCE_TASK_ID,
STATUS,
PLAN_EXECUTION_START_DATE,
PLAN_EXECUTION_END_DATE,
ORGANIZATION_ID,
ORIG_SOURCE_SUB_CODE,
ORIG_SOURCE_LOC_ID,
ORIG_DEST_SUB_CODE,
ORIG_DEST_LOC_ID,
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)
VALUES
(decode(p_insert_rec.op_plan_instance_id,NULL,wms_op_instance_s.NEXTVAL,p_insert_rec.op_plan_instance_id),
p_insert_rec.OPERATION_PLAN_ID,
p_insert_rec.ACTIVITY_TYPE_ID,
p_insert_rec.PLAN_TYPE_ID,
p_insert_rec.SOURCE_TASK_ID,
p_insert_rec.STATUS,
p_insert_rec.PLAN_EXECUTION_START_DATE,
p_insert_rec.PLAN_EXECUTION_END_DATE,
p_insert_rec.ORGANIZATION_ID,
p_insert_rec.ORIG_SOURCE_SUB_CODE,
p_insert_rec.ORIG_SOURCE_LOC_ID,
p_insert_rec.ORIG_DEST_SUB_CODE,
p_insert_rec.ORIG_DEST_LOC_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
p_insert_rec.LAST_UPDATE_LOGIN,
p_insert_rec.ATTRIBUTE_CATEGORY,
p_insert_rec.ATTRIBUTE1,
p_insert_rec.ATTRIBUTE2,
p_insert_rec.ATTRIBUTE3,
p_insert_rec.ATTRIBUTE4,
p_insert_rec.ATTRIBUTE5,
p_insert_rec.ATTRIBUTE6,
p_insert_rec.ATTRIBUTE7,
p_insert_rec.ATTRIBUTE8,
p_insert_rec.ATTRIBUTE9,
p_insert_rec.ATTRIBUTE10,
p_insert_rec.ATTRIBUTE11,
p_insert_rec.ATTRIBUTE12,
p_insert_rec.ATTRIBUTE13,
p_insert_rec.ATTRIBUTE14,
p_insert_rec.ATTRIBUTE15);
print_debug('Unexpected Error,Insertion failed'||SQLERRM,l_module_name,3);
* Procedure:Update_Plan_instance
* This procedure updates data into the table WMS_OP_PLAN_INSTANCES.
* @param p_insert_rec - Record Variable of type WMS_OP_PLAN_INSTANCES%rowtype
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE update_plan_instance
(p_update_rec IN WMS_OP_PLAN_INSTANCES%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE) IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_module_name CONSTANT VARCHAR2(30) := 'Update_Plan_Instance';
print_debug('Plan Instance Id:'||p_update_rec.op_plan_instance_id,l_module_name,3);
IF p_update_rec.op_plan_instance_id IS NULL THEN /*Plan instance id is a must for Updation*/
IF (l_debug=1) THEN
print_debug('Plan Instance Id is null',l_module_name,1);
UPDATE WMS_OP_PLAN_INSTANCES
SET
OP_PLAN_INSTANCE_ID = decode(p_update_rec.OP_PLAN_INSTANCE_ID,G_MISS_NUM,NULL,NULL,OP_PLAN_INSTANCE_ID,p_update_rec.OP_PLAN_INSTANCE_ID),
OPERATION_PLAN_ID = decode(p_update_rec.OPERATION_PLAN_ID,G_MISS_NUM,NULL,NULL,OPERATION_PLAN_ID,p_update_rec.OPERATION_PLAN_ID),
ACTIVITY_TYPE_ID = decode(p_update_rec.ACTIVITY_TYPE_ID,G_MISS_NUM,NULL,NULL,ACTIVITY_TYPE_ID,p_update_rec.ACTIVITY_TYPE_ID),
PLAN_TYPE_ID = decode(p_update_rec.PLAN_TYPE_ID,G_MISS_NUM,NULL,NULL,PLAN_TYPE_ID,p_update_rec.PLAN_TYPE_ID),
SOURCE_TASK_ID = decode(p_update_rec.SOURCE_TASK_ID,G_MISS_NUM,NULL,NULL,SOURCE_TASK_ID,p_update_rec.SOURCE_TASK_ID),
STATUS = decode(p_update_rec.STATUS,G_MISS_CHAR,NULL,NULL,STATUS,p_update_rec.STATUS),
PLAN_EXECUTION_START_DATE = decode(p_update_rec.PLAN_EXECUTION_START_DATE,NULL,PLAN_EXECUTION_START_DATE,G_MISS_DATE,NULL,p_update_rec.PLAN_EXECUTION_START_DATE),
PLAN_EXECUTION_END_DATE = decode(p_update_rec.PLAN_EXECUTION_END_DATE,NULL,PLAN_EXECUTION_END_DATE,G_MISS_DATE,NULL,p_update_rec.PLAN_EXECUTION_END_DATE),
ORGANIZATION_ID = decode(p_update_rec.ORGANIZATION_ID,G_MISS_NUM,NULL,NULL,ORGANIZATION_ID,p_update_rec.ORGANIZATION_ID),
ORIG_SOURCE_SUB_CODE = decode(p_update_rec.ORIG_SOURCE_SUB_CODE,G_MISS_CHAR,NULL,NULL,ORIG_SOURCE_SUB_CODE,p_update_rec.ORIG_SOURCE_SUB_CODE),
ORIG_SOURCE_LOC_ID = decode(p_update_rec.ORIG_SOURCE_LOC_ID,G_MISS_NUM,NULL,NULL,ORIG_SOURCE_LOC_ID,p_update_rec.ORIG_SOURCE_LOC_ID),
ORIG_DEST_SUB_CODE = decode(p_update_rec.ORIG_DEST_SUB_CODE,G_MISS_CHAR,NULL,NULL,ORIG_DEST_SUB_CODE,p_update_rec.ORIG_DEST_SUB_CODE),
ORIG_DEST_LOC_ID = decode(p_update_rec.ORIG_DEST_LOC_ID,G_MISS_NUM,NULL,NULL,ORIG_DEST_LOC_ID,p_update_rec.ORIG_DEST_LOC_ID),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = decode(p_update_rec.LAST_UPDATE_LOGIN,G_MISS_NUM,NULL,NULL,LAST_UPDATE_LOGIN,p_update_rec.LAST_UPDATE_LOGIN),
ATTRIBUTE_CATEGORY = decode(p_update_rec.ATTRIBUTE_CATEGORY,G_MISS_CHAR,NULL,NULL,ATTRIBUTE_CATEGORY,p_update_rec.ATTRIBUTE_CATEGORY),
ATTRIBUTE1 = decode(p_update_rec.ATTRIBUTE1,G_MISS_CHAR,NULL,NULL,ATTRIBUTE1,p_update_rec.ATTRIBUTE1),
ATTRIBUTE2 = decode(p_update_rec.ATTRIBUTE2,G_MISS_CHAR,NULL,NULL,ATTRIBUTE2,p_update_rec.ATTRIBUTE2),
ATTRIBUTE3 = decode(p_update_rec.ATTRIBUTE3,G_MISS_CHAR,NULL,NULL,ATTRIBUTE3,p_update_rec.ATTRIBUTE3),
ATTRIBUTE4 = decode(p_update_rec.ATTRIBUTE4,G_MISS_CHAR,NULL,NULL,ATTRIBUTE4,p_update_rec.ATTRIBUTE4),
ATTRIBUTE5 = decode(p_update_rec.ATTRIBUTE5,G_MISS_CHAR,NULL,NULL,ATTRIBUTE5,p_update_rec.ATTRIBUTE5),
ATTRIBUTE6 = decode(p_update_rec.ATTRIBUTE6,G_MISS_CHAR,NULL,NULL,ATTRIBUTE6,p_update_rec.ATTRIBUTE6),
ATTRIBUTE7 = decode(p_update_rec.ATTRIBUTE7,G_MISS_CHAR,NULL,NULL,ATTRIBUTE7,p_update_rec.ATTRIBUTE7),
ATTRIBUTE8 = decode(p_update_rec.ATTRIBUTE8,G_MISS_CHAR,NULL,NULL,ATTRIBUTE8,p_update_rec.ATTRIBUTE8),
ATTRIBUTE9 = decode(p_update_rec.ATTRIBUTE9,G_MISS_CHAR,NULL,NULL,ATTRIBUTE9,p_update_rec.ATTRIBUTE9),
ATTRIBUTE10 = decode(p_update_rec.ATTRIBUTE10,G_MISS_CHAR,NULL,NULL,ATTRIBUTE10,p_update_rec.ATTRIBUTE10),
ATTRIBUTE11 = decode(p_update_rec.ATTRIBUTE11,G_MISS_CHAR,NULL,NULL,ATTRIBUTE11,p_update_rec.ATTRIBUTE11),
ATTRIBUTE12 = decode(p_update_rec.ATTRIBUTE12,G_MISS_CHAR,NULL,NULL,ATTRIBUTE12,p_update_rec.ATTRIBUTE12),
ATTRIBUTE13 = decode(p_update_rec.ATTRIBUTE13,G_MISS_CHAR,NULL,NULL,ATTRIBUTE13,p_update_rec.ATTRIBUTE13),
ATTRIBUTE14 = decode(p_update_rec.ATTRIBUTE14,G_MISS_CHAR,NULL,NULL,ATTRIBUTE14,p_update_rec.ATTRIBUTE14),
ATTRIBUTE15 = decode(p_update_rec.ATTRIBUTE15,G_MISS_CHAR,NULL,NULL,ATTRIBUTE15,p_update_rec.ATTRIBUTE15)
WHERE op_plan_instance_id=p_update_rec.op_plan_instance_id ;
* Procedure:Delete_Plan_instance
* This procedure inserts data into the table WMS_OP_PLAN_INSTANCES.
* @param p_op_plan_instance_id - Operation Plan Instance Id of the Plan that has to be deleted
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE delete_plan_instance
(p_op_plan_instance_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE)IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_module_name CONSTANT VARCHAR2(30):= 'Delete_Plan_Instance';
print_debug('Plan Instance Id is null','Delete_Plan_instance',3);
print_debug('Deleting Plan Instances','Delete_Plan_instance',9);
DELETE FROM WMS_OP_PLAN_INSTANCES
WHERE OP_PLAN_INSTANCE_ID = P_op_plan_instance_id;
print_debug('Record not found while deleting','Delete_Plan_Instance',1);
print_debug('Error while deleting Plan Instance','Delete_Plan_Instance',1);
print_debug('Unexpected Error While Deleting'||SQLERRM,'Delete_Plan_Instance',1);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'DELETE_PLAN_INSTANCE');
* Procedure:Insert_operation_instance
* This procedure inserts data into the table WMS_OP_OPERATION_INSTANCES.
* @param p_insert_rec - Record Variable of type WMS_OP_OPERATION_INSTANCES%rowtype
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE insert_operation_instance
(p_insert_rec IN WMS_OP_OPERATION_INSTANCES%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE) IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_module_name CONSTANT VARCHAR2(30):='Insert_Operation_Instance';
print_debug('Operation Instance Id:'||p_insert_rec.operation_instance_id,l_module_name,3);
print_debug('Inserting records into WMS_OP_OPERATION_INSTANCES',l_module_name,9);
INSERT INTO WMS_OP_OPERATION_INSTANCES (
OPERATION_INSTANCE_ID,
OPERATION_PLAN_DETAIL_ID,
OPERATION_SEQUENCE,
OPERATION_TYPE_ID,
OP_PLAN_INSTANCE_ID,
OPERATION_STATUS,
ACTIVITY_TYPE_ID,
SOURCE_TASK_ID,
ACTIVATE_TIME,
COMPLETE_TIME,
SUG_TO_SUB_CODE,
SUG_TO_LOCATOR_ID,
FROM_SUBINVENTORY_CODE,
FROM_LOCATOR_ID,
TO_SUBINVENTORY_CODE,
TO_LOCATOR_ID,
IS_IN_INVENTORY,
ORGANIZATION_ID,
EMPLOYEE_ID,
EQUIPMENT_ID,
CREATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
VALUES
(decode(p_insert_rec.operation_instance_id,NULL,wms_op_instance_s.NEXTVAL,p_insert_rec.operation_instance_id),
p_insert_rec.OPERATION_PLAN_DETAIL_ID,
p_insert_rec.OPERATION_SEQUENCE,
p_insert_rec.OPERATION_TYPE_ID,
p_insert_rec.OP_PLAN_INSTANCE_ID,
p_insert_rec.OPERATION_STATUS,
p_insert_rec.ACTIVITY_TYPE_ID,
p_insert_rec.SOURCE_TASK_ID,
p_insert_rec.ACTIVATE_TIME,
p_insert_rec.COMPLETE_TIME,
p_insert_rec.SUG_TO_SUB_CODE,
p_insert_rec.SUG_TO_LOCATOR_ID,
p_insert_rec.FROM_SUBINVENTORY_CODE,
p_insert_rec.FROM_LOCATOR_ID,
p_insert_rec.TO_SUBINVENTORY_CODE,
p_insert_rec.TO_LOCATOR_ID,
p_insert_rec.IS_IN_INVENTORY,
p_insert_rec.ORGANIZATION_ID,
p_insert_rec.EMPLOYEE_ID,
p_insert_rec.EQUIPMENT_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
p_insert_rec.LAST_UPDATE_LOGIN,
p_insert_rec.ATTRIBUTE_CATEGORY,
p_insert_rec.ATTRIBUTE1,
p_insert_rec.ATTRIBUTE2,
p_insert_rec.ATTRIBUTE3,
p_insert_rec.ATTRIBUTE4,
p_insert_rec.ATTRIBUTE5,
p_insert_rec.ATTRIBUTE6,
p_insert_rec.ATTRIBUTE7,
p_insert_rec.ATTRIBUTE8,
p_insert_rec.ATTRIBUTE9,
p_insert_rec.ATTRIBUTE10,
p_insert_rec.ATTRIBUTE11,
p_insert_rec.ATTRIBUTE12,
p_insert_rec.ATTRIBUTE13,
p_insert_rec.ATTRIBUTE14,
p_insert_rec.ATTRIBUTE15);
print_debug('Unexptec Error while inserting'||SQLERRM,l_module_name,1);
* Procedure:Delete_Operation_instance
* This procedure deletes the data in the table WMS_OP_PLAN_INSTANCES.
* @param p_operation_instance_id - Plan Instance Id of all the Operations that has to be deleted
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE delete_operation_instance
(p_operation_instance_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE)IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_module_name CONSTANT VARCHAR2(30) :='Delete_operation_instance';
DELETE FROM wms_op_operation_instances
WHERE operation_instance_id = p_operation_instance_id;
* Procedure:Update_Operation_instance
* This procedure updates data into the table WMS_OP_PLAN_INSTANCES.
* @param p_update_rec - Record Variable of type WMS_OP_OPERATION_INSTANCES%rowtype
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE update_operation_instance
(p_update_rec IN WMS_OP_OPERATION_INSTANCES%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE)IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_module_name CONSTANT VARCHAR2(30):='Update_operation_instance';
print_debug('Operation Instance Id'||p_update_rec.operation_instance_id,l_module_name,3);
IF p_update_rec.operation_instance_id IS NULL THEN
IF (l_debug=1) THEN
print_debug('Operation Instance Id is null',l_module_name,1);
UPDATE wms_op_operation_instances
SET
OPERATION_INSTANCE_ID =decode(p_update_rec.OPERATION_INSTANCE_ID,G_MISS_NUM,NULL,NULL,OPERATION_INSTANCE_ID,p_update_rec.OPERATION_INSTANCE_ID),
OPERATION_PLAN_DETAIL_ID=decode(p_update_rec.OPERATION_PLAN_DETAIL_ID,G_MISS_NUM,NULL,NULL,OPERATION_PLAN_DETAIL_ID,p_update_rec.OPERATION_PLAN_DETAIL_ID),
OPERATION_SEQUENCE =decode(p_update_rec.OPERATION_SEQUENCE,G_MISS_NUM,NULL,NULL,OPERATION_SEQUENCE,p_update_rec.OPERATION_SEQUENCE),
OPERATION_TYPE_ID =decode(p_update_rec.OPERATION_TYPE_ID,G_MISS_NUM,NULL,NULL,OPERATION_TYPE_ID,p_update_rec.OPERATION_TYPE_ID),
OP_PLAN_INSTANCE_ID =decode(p_update_rec.OP_PLAN_INSTANCE_ID,G_MISS_NUM,NULL,NULL,OP_PLAN_INSTANCE_ID,p_update_rec.OP_PLAN_INSTANCE_ID),
OPERATION_STATUS =decode(p_update_rec.OPERATION_STATUS,G_MISS_NUM,NULL,NULL,OPERATION_STATUS,p_update_rec.OPERATION_STATUS),
ACTIVITY_TYPE_ID =decode(p_update_rec.ACTIVITY_TYPE_ID,G_MISS_NUM,NULL,NULL,ACTIVITY_TYPE_ID,p_update_rec.ACTIVITY_TYPE_ID),
SOURCE_TASK_ID =decode(p_update_rec.SOURCE_TASK_ID,G_MISS_NUM,NULL,NULL,SOURCE_TASK_ID,p_update_rec.SOURCE_TASK_ID),
ACTIVATE_TIME =decode(p_update_rec.ACTIVATE_TIME,NULL,ACTIVATE_TIME,G_MISS_DATE,NULL,p_update_rec.ACTIVATE_TIME),
COMPLETE_TIME =decode(p_update_rec.COMPLETE_TIME,NULL,COMPLETE_TIME,G_MISS_DATE,NULL,p_update_rec.COMPLETE_TIME),
SUG_TO_SUB_CODE =decode(p_update_rec.SUG_TO_SUB_CODE,G_MISS_CHAR,NULL,NULL,SUG_TO_SUB_CODE,p_update_rec.SUG_TO_SUB_CODE),
SUG_TO_LOCATOR_ID =decode(p_update_rec.SUG_TO_LOCATOR_ID,G_MISS_NUM,NULL,NULL,SUG_TO_LOCATOR_ID,p_update_rec.SUG_TO_LOCATOR_ID),
FROM_SUBINVENTORY_CODE =decode(p_update_rec.FROM_SUBINVENTORY_CODE,G_MISS_CHAR,NULL,NULL,FROM_SUBINVENTORY_CODE,p_update_rec.FROM_SUBINVENTORY_CODE),
FROM_LOCATOR_ID =decode(p_update_rec.FROM_LOCATOR_ID,G_MISS_NUM,NULL,NULL,FROM_LOCATOR_ID,p_update_rec.FROM_LOCATOR_ID),
TO_SUBINVENTORY_CODE =decode(p_update_rec.TO_SUBINVENTORY_CODE,G_MISS_CHAR,NULL,NULL,TO_SUBINVENTORY_CODE,p_update_rec.TO_SUBINVENTORY_CODE),
TO_LOCATOR_ID =decode(p_update_rec.TO_LOCATOR_ID,G_MISS_NUM,NULL,NULL,TO_LOCATOR_ID,p_update_rec.TO_LOCATOR_ID),
IS_IN_INVENTORY =decode(p_update_rec.IS_IN_INVENTORY,G_MISS_CHAR,NULL,NULL,IS_IN_INVENTORY,p_update_rec.IS_IN_INVENTORY),
ORGANIZATION_ID =decode(p_update_rec.ORGANIZATION_ID,G_MISS_NUM,NULL,NULL,ORGANIZATION_ID,p_update_rec.ORGANIZATION_ID),
EMPLOYEE_ID =decode(p_update_rec.EMPLOYEE_ID,G_MISS_NUM,NULL,NULL,EMPLOYEE_ID,p_update_rec.EMPLOYEE_ID),
EQUIPMENT_ID =decode(p_update_rec.EQUIPMENT_ID,G_MISS_NUM,NULL,NULL,EQUIPMENT_ID,p_update_rec.EQUIPMENT_ID),
LAST_UPDATE_DATE =SYSDATE,
LAST_UPDATED_BY =FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN =decode(p_update_rec.LAST_UPDATE_LOGIN,G_MISS_NUM,NULL,NULL,LAST_UPDATE_LOGIN,p_update_rec.LAST_UPDATE_LOGIN),
ATTRIBUTE_CATEGORY =decode(p_update_rec.ATTRIBUTE_CATEGORY,G_MISS_CHAR,NULL,NULL,ATTRIBUTE_CATEGORY,p_update_rec.ATTRIBUTE_CATEGORY),
ATTRIBUTE1 =decode(p_update_rec.ATTRIBUTE1,G_MISS_CHAR,NULL,NULL,ATTRIBUTE1,p_update_rec.ATTRIBUTE1),
ATTRIBUTE2 =decode(p_update_rec.ATTRIBUTE2,G_MISS_CHAR,NULL,NULL,ATTRIBUTE2,p_update_rec.ATTRIBUTE2),
ATTRIBUTE3 =decode(p_update_rec.ATTRIBUTE3,G_MISS_CHAR,NULL,NULL,ATTRIBUTE3,p_update_rec.ATTRIBUTE3),
ATTRIBUTE4 =decode(p_update_rec.ATTRIBUTE4,G_MISS_CHAR,NULL,NULL,ATTRIBUTE4,p_update_rec.ATTRIBUTE4),
ATTRIBUTE5 =decode(p_update_rec.ATTRIBUTE5,G_MISS_CHAR,NULL,NULL,ATTRIBUTE5,p_update_rec.ATTRIBUTE5),
ATTRIBUTE6 =decode(p_update_rec.ATTRIBUTE6,G_MISS_CHAR,NULL,NULL,ATTRIBUTE6,p_update_rec.ATTRIBUTE6),
ATTRIBUTE7 =decode(p_update_rec.ATTRIBUTE7,G_MISS_CHAR,NULL,NULL,ATTRIBUTE7,p_update_rec.ATTRIBUTE7),
ATTRIBUTE8 =decode(p_update_rec.ATTRIBUTE8,G_MISS_CHAR,NULL,NULL,ATTRIBUTE8,p_update_rec.ATTRIBUTE8),
ATTRIBUTE9 =decode(p_update_rec.ATTRIBUTE9,G_MISS_CHAR,NULL,NULL,ATTRIBUTE9,p_update_rec.ATTRIBUTE9),
ATTRIBUTE10 =decode(p_update_rec.ATTRIBUTE10,G_MISS_CHAR,NULL,NULL,ATTRIBUTE10,p_update_rec.ATTRIBUTE10),
ATTRIBUTE11 =decode(p_update_rec.ATTRIBUTE11,G_MISS_CHAR,NULL,NULL,ATTRIBUTE11,p_update_rec.ATTRIBUTE11),
ATTRIBUTE12 =decode(p_update_rec.ATTRIBUTE12,G_MISS_CHAR,NULL,NULL,ATTRIBUTE12,p_update_rec.ATTRIBUTE12),
ATTRIBUTE13 =decode(p_update_rec.ATTRIBUTE13,G_MISS_CHAR,NULL,NULL,ATTRIBUTE13,p_update_rec.ATTRIBUTE13),
ATTRIBUTE14 =decode(p_update_rec.ATTRIBUTE14,G_MISS_CHAR,NULL,NULL,ATTRIBUTE14,p_update_rec.ATTRIBUTE14),
ATTRIBUTE15 =decode(p_update_rec.ATTRIBUTE15,G_MISS_CHAR,NULL,NULL,ATTRIBUTE15,p_update_rec.ATTRIBUTE15)
WHERE operation_instance_id=p_update_rec.operation_instance_id;
* This procedure inserts data into the table WMS_OP_PLAN_INSTANCES.
* @param p_op_plan_instance_id - Operation Plan Instance Id of the Plan that has to be archived.
* @param p_inventory_item_id Inventory Item Id of the Plan
* @param p_transaction_quantity Transaction Quantitity of the Plan
* @param p_transaction_uom Transaction UOM of the Plan
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE archive_plan_instance(
p_op_plan_instance_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
)IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
INSERT INTO WMS_OP_PLAN_INSTANCES_HIST
(OP_PLAN_INSTANCE_ID,
OPERATION_PLAN_ID,
ACTIVITY_TYPE_ID,
PLAN_TYPE_ID,
STATUS,
PLAN_EXECUTION_START_DATE,
PLAN_EXECUTION_END_DATE,
ORGANIZATION_ID,
ORIG_SOURCE_SUB_CODE,
ORIG_SOURCE_LOC_ID,
ORIG_DEST_SUB_CODE,
ORIG_DEST_LOC_ID,
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 )
(SELECT
OP_PLAN_INSTANCE_ID,
OPERATION_PLAN_ID,
ACTIVITY_TYPE_ID,
PLAN_TYPE_ID,
STATUS,
PLAN_EXECUTION_START_DATE,
SYSDATE,
ORGANIZATION_ID,
ORIG_SOURCE_SUB_CODE,
ORIG_SOURCE_LOC_ID,
ORIG_DEST_SUB_CODE,
ORIG_DEST_LOC_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM WMS_OP_PLAN_INSTANCES
WHERE op_plan_instance_id=p_op_plan_instance_id);
print_debug('Records inserted into WMS_OP_PLAN_INSTANCES_HISTORY '||SQL%ROWCOUNT,l_module_name,9);
INSERT INTO WMS_OP_OPERTN_INSTANCES_HIST
( OPERATION_INSTANCE_ID
,OPERATION_TYPE_ID
,OPERATION_PLAN_DETAIL_ID
,OP_PLAN_INSTANCE_ID
,OPERATION_STATUS
,OPERATION_SEQUENCE
,ORGANIZATION_ID
,ACTIVITY_TYPE_ID
,SUG_TO_SUB_CODE
,SUG_TO_LOCATOR_ID
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,SOURCE_TASK_ID
,EMPLOYEE_ID
,EQUIPMENT_ID
,ACTIVATE_TIME
,COMPLETE_TIME
,IS_IN_INVENTORY
,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)
(SELECT OPERATION_INSTANCE_ID
,OPERATION_TYPE_ID
,OPERATION_PLAN_DETAIL_ID
,OP_PLAN_INSTANCE_ID
,OPERATION_STATUS
,OPERATION_SEQUENCE
,ORGANIZATION_ID
,ACTIVITY_TYPE_ID
,SUG_TO_SUB_CODE
,SUG_TO_LOCATOR_ID
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,SOURCE_TASK_ID
,EMPLOYEE_ID
,EQUIPMENT_ID
,ACTIVATE_TIME
,COMPLETE_TIME
,IS_IN_INVENTORY
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
FROM WMS_OP_OPERATION_INSTANCES
WHERE OP_PLAN_INSTANCE_ID=p_op_plan_instance_id);
print_debug('Records inserted into WMS_OP_OPERTN_INSTANCES_HIST '||SQL%ROWCOUNT,l_module_name,9);
DELETE FROM WMS_OP_OPERATION_INSTANCES
WHERE op_plan_instance_id=p_op_plan_instance_id;
print_debug('Deleted records form WMS_OP_OPERATION_INSTANCES'||SQL%ROWCOUNT,l_module_name,9);
print_debug('Calling Delete_plan_instance to delete the Plan instance record',l_module_name,9);
DELETE_PLAN_INSTANCE(p_op_plan_instance_id,x_return_status,x_msg_data,x_msg_count);
print_debug('Return status from Delete_plan_instance'||x_return_status,l_module_name,6);
* This procedure inserts data into the table WMS_OP_PLAN_INSTANCES.
* @param p_op_plan_instance_id - Operation Plan Instance Id of the Plan that has to be completed.
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE complete_plan_instance(
p_op_plan_instance_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
) IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
UPDATE WMS_OP_PLAN_INSTANCES
SET status=WMS_GLOBALS.G_OP_INS_STAT_COMPLETED
WHERE op_plan_instance_id=p_op_plan_instance_id;
* Procedure:Insert_Dispatched_tasks
* This procedure inserts the task records into WMS_DISPATCHED_TASKS
* @param p_wdt_rec - WDT record that has to be inserted.
* @param p_source_task_id - Transaction Temp Id of the WDT record.
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE insert_dispatched_tasks(
p_wdt_rec IN wms_dispatched_tasks%ROWTYPE
, p_source_task_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
)IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_module_name CONSTANT VARCHAR2(30):='Insert_Dispatched_tasks';
INSERT INTO WMS_DISPATCHED_TASKS
( TASK_ID,
TRANSACTION_TEMP_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,
DEVICE_ID,
DEVICE_INVOKED,
DEVICE_REQUEST_ID,
SUGGESTED_DEST_SUBINVENTORY,
SUGGESTED_DEST_LOCATOR_ID,
OPERATION_PLAN_ID,
MOVE_ORDER_LINE_ID,
TRANSFER_LPN_ID,
OP_PLAN_INSTANCE_ID)
VALUES
( decode(p_wdt_rec.TASK_ID,NULL,wms_dispatched_tasks_s.NEXTVAL,p_wdt_rec.TASK_ID),
p_wdt_rec.TRANSACTION_TEMP_ID,
p_wdt_rec.ORGANIZATION_ID,
p_wdt_rec.USER_TASK_TYPE,
p_wdt_rec.PERSON_ID,
--Bug No:6350525
-- p_wdt_rec.EFFECTIVE_START_DATE,
--p_wdt_rec.EFFECTIVE_END_DATE,
SYSDATE,
SYSDATE,
p_wdt_rec.EQUIPMENT_ID,
p_wdt_rec.EQUIPMENT_INSTANCE,
p_wdt_rec.PERSON_RESOURCE_ID,
p_wdt_rec.MACHINE_RESOURCE_ID,
p_wdt_rec.STATUS,
p_wdt_rec.DISPATCHED_TIME,
p_wdt_rec.LOADED_TIME,
p_wdt_rec.DROP_OFF_TIME,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
p_wdt_rec.LAST_UPDATE_LOGIN,
p_wdt_rec.ATTRIBUTE_CATEGORY,
p_wdt_rec.ATTRIBUTE1,
p_wdt_rec.ATTRIBUTE2,
p_wdt_rec.ATTRIBUTE3,
p_wdt_rec.ATTRIBUTE4,
p_wdt_rec.ATTRIBUTE5,
p_wdt_rec.ATTRIBUTE6,
p_wdt_rec.ATTRIBUTE7,
p_wdt_rec.ATTRIBUTE8,
p_wdt_rec.ATTRIBUTE9,
p_wdt_rec.ATTRIBUTE10,
p_wdt_rec.ATTRIBUTE11,
p_wdt_rec.ATTRIBUTE12,
p_wdt_rec.ATTRIBUTE13,
p_wdt_rec.ATTRIBUTE14,
p_wdt_rec.ATTRIBUTE15,
p_wdt_rec.TASK_TYPE,
p_wdt_rec.PRIORITY,
p_wdt_rec.TASK_GROUP_ID,
p_wdt_rec.DEVICE_ID,
p_wdt_rec.DEVICE_INVOKED,
p_wdt_rec.DEVICE_REQUEST_ID,
p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY,
p_wdt_rec.SUGGESTED_DEST_LOCATOR_ID,
p_wdt_rec.OPERATION_PLAN_ID,
p_wdt_rec.MOVE_ORDER_LINE_ID,
p_wdt_rec.TRANSFER_LPN_ID,
p_wdt_rec.OP_PLAN_INSTANCE_ID);
PROCEDURE insert_dispatched_tasks_nauto(
p_wdt_rec IN wms_dispatched_tasks%ROWTYPE
, p_source_task_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
)IS
--PRAGMA AUTONOMOUS_TRANSACTION;
l_module_name CONSTANT VARCHAR2(30):='Insert_Dispatched_tasks';
INSERT INTO WMS_DISPATCHED_TASKS
( TASK_ID,
TRANSACTION_TEMP_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,
DEVICE_ID,
DEVICE_INVOKED,
DEVICE_REQUEST_ID,
SUGGESTED_DEST_SUBINVENTORY,
SUGGESTED_DEST_LOCATOR_ID,
OPERATION_PLAN_ID,
MOVE_ORDER_LINE_ID,
TRANSFER_LPN_ID,
OP_PLAN_INSTANCE_ID)
VALUES
( decode(p_wdt_rec.TASK_ID,NULL,wms_dispatched_tasks_s.NEXTVAL,p_wdt_rec.TASK_ID),
p_wdt_rec.TRANSACTION_TEMP_ID,
p_wdt_rec.ORGANIZATION_ID,
p_wdt_rec.USER_TASK_TYPE,
p_wdt_rec.PERSON_ID,
--Bug No 6162455
-- p_wdt_rec.EFFECTIVE_START_DATE,
--p_wdt_rec.EFFECTIVE_END_DATE,
sysdate,
sysdate,
p_wdt_rec.EQUIPMENT_ID,
p_wdt_rec.EQUIPMENT_INSTANCE,
p_wdt_rec.PERSON_RESOURCE_ID,
p_wdt_rec.MACHINE_RESOURCE_ID,
p_wdt_rec.STATUS,
p_wdt_rec.DISPATCHED_TIME,
p_wdt_rec.LOADED_TIME,
p_wdt_rec.DROP_OFF_TIME,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
p_wdt_rec.LAST_UPDATE_LOGIN,
p_wdt_rec.ATTRIBUTE_CATEGORY,
p_wdt_rec.ATTRIBUTE1,
p_wdt_rec.ATTRIBUTE2,
p_wdt_rec.ATTRIBUTE3,
p_wdt_rec.ATTRIBUTE4,
p_wdt_rec.ATTRIBUTE5,
p_wdt_rec.ATTRIBUTE6,
p_wdt_rec.ATTRIBUTE7,
p_wdt_rec.ATTRIBUTE8,
p_wdt_rec.ATTRIBUTE9,
p_wdt_rec.ATTRIBUTE10,
p_wdt_rec.ATTRIBUTE11,
p_wdt_rec.ATTRIBUTE12,
p_wdt_rec.ATTRIBUTE13,
p_wdt_rec.ATTRIBUTE14,
p_wdt_rec.ATTRIBUTE15,
p_wdt_rec.TASK_TYPE,
p_wdt_rec.PRIORITY,
p_wdt_rec.TASK_GROUP_ID,
p_wdt_rec.DEVICE_ID,
p_wdt_rec.DEVICE_INVOKED,
p_wdt_rec.DEVICE_REQUEST_ID,
p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY,
p_wdt_rec.SUGGESTED_DEST_LOCATOR_ID,
p_wdt_rec.OPERATION_PLAN_ID,
p_wdt_rec.MOVE_ORDER_LINE_ID,
p_wdt_rec.TRANSFER_LPN_ID,
p_wdt_rec.OP_PLAN_INSTANCE_ID);
* Procedure:Delete_Dispatched_task
* This procedure deletes the task records into WMS_DISPATCHED_TASKS
* AND THERE IS AN AUTOMONOUS COMMIT IN THIS DELETE!
* SO IT SHOULD ONLY BE USED TO DELETE WDT CREATED BY insert_dispatched_tasks.
* @param p_source_task_id - Transaction Temp Id of the WDT record.
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*
* This API is necessary for instance when load errors out at online mode.
* The calling procedure will simply rollback, but need to delete WDT
* Autonomously inserted earlier.
*/
PROCEDURE delete_dispatched_task
(
p_source_task_id IN NUMBER
, p_wms_task_type IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
)IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_module_name CONSTANT VARCHAR2(30):='delete_dispatched_task';
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = p_source_task_id
AND task_type= p_wms_task_type;
END delete_dispatched_task;
* Procedure:Update_Dispatched_tasks
* This procedure updates the task records in WMS_DISPATCHED_TASKS
* @param p_wdt_rec - WDT record that has to be updated.
* @param x_return_status - Return Status
* @param x_msg_count - Returns Message Count
* @param x_msg_data - Returns Error Message
*/
PROCEDURE update_dipatched_tasks(
p_wdt_rec IN wms_dispatched_tasks%ROWTYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
)IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_module_name CONSTANT VARCHAR2(30):='Update_Dispatched_Tasks';
UPDATE WMS_DISPATCHED_TASKS
SET
TASK_ID =decode(p_wdt_rec.TASK_ID,G_MISS_NUM,NULL,NULL,TASK_ID,p_wdt_rec.TASK_ID),
TRANSACTION_TEMP_ID =decode(p_wdt_rec.TRANSACTION_TEMP_ID,G_MISS_NUM,NULL,NULL,TRANSACTION_TEMP_ID,p_wdt_rec.TRANSACTION_TEMP_ID),
ORGANIZATION_ID =decode(p_wdt_rec.ORGANIZATION_ID,G_MISS_NUM,NULL,NULL,ORGANIZATION_ID,p_wdt_rec.ORGANIZATION_ID),
USER_TASK_TYPE =decode(p_wdt_rec.USER_TASK_TYPE,G_MISS_NUM,NULL,NULL,USER_TASK_TYPE,p_wdt_rec.USER_TASK_TYPE),
PERSON_ID =decode(p_wdt_rec.PERSON_ID,G_MISS_NUM,NULL,NULL,PERSON_ID,p_wdt_rec.PERSON_ID),
EFFECTIVE_START_DATE =decode(p_wdt_rec.EFFECTIVE_START_DATE,NULL,EFFECTIVE_START_DATE,G_MISS_DATE,NULL,p_wdt_rec.EFFECTIVE_START_DATE),
EFFECTIVE_END_DATE =decode(p_wdt_rec.EFFECTIVE_END_DATE,NULL,EFFECTIVE_END_DATE,G_MISS_DATE,NULL,p_wdt_rec.EFFECTIVE_END_DATE),
EQUIPMENT_ID =decode(p_wdt_rec.EQUIPMENT_ID,G_MISS_NUM,NULL,NULL,EQUIPMENT_ID,p_wdt_rec.EQUIPMENT_ID),
EQUIPMENT_INSTANCE =decode(p_wdt_rec.EQUIPMENT_INSTANCE,G_MISS_CHAR,NULL,NULL,EQUIPMENT_INSTANCE,p_wdt_rec.EQUIPMENT_INSTANCE),
PERSON_RESOURCE_ID =decode(p_wdt_rec.PERSON_RESOURCE_ID,G_MISS_NUM,NULL,NULL,PERSON_RESOURCE_ID,p_wdt_rec.PERSON_RESOURCE_ID),
MACHINE_RESOURCE_ID =decode(p_wdt_rec.MACHINE_RESOURCE_ID,G_MISS_NUM,NULL,NULL,MACHINE_RESOURCE_ID,p_wdt_rec.MACHINE_RESOURCE_ID),
STATUS =decode(p_wdt_rec.STATUS,G_MISS_NUM,NULL,NULL,STATUS,p_wdt_rec.STATUS),
DISPATCHED_TIME =decode(p_wdt_rec.DISPATCHED_TIME,NULL,DISPATCHED_TIME,G_MISS_DATE,NULL,p_wdt_rec.DISPATCHED_TIME),
LOADED_TIME =decode(p_wdt_rec.LOADED_TIME,NULL,LOADED_TIME,G_MISS_DATE,NULL,p_wdt_rec.LOADED_TIME),
DROP_OFF_TIME =decode(p_wdt_rec.DROP_OFF_TIME,NULL,DROP_OFF_TIME,G_MISS_DATE,NULL,p_wdt_rec.DROP_OFF_TIME),
LAST_UPDATE_DATE =SYSDATE,
LAST_UPDATED_BY =FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN =decode(p_wdt_rec.LAST_UPDATE_LOGIN,G_MISS_NUM,NULL,NULL,LAST_UPDATE_LOGIN,p_wdt_rec.LAST_UPDATE_LOGIN),
ATTRIBUTE_CATEGORY =decode(p_wdt_rec.ATTRIBUTE_CATEGORY,G_MISS_CHAR,NULL,NULL,ATTRIBUTE_CATEGORY,p_wdt_rec.ATTRIBUTE_CATEGORY),
ATTRIBUTE1 =decode(p_wdt_rec.ATTRIBUTE1,G_MISS_CHAR,NULL,NULL,ATTRIBUTE1,p_wdt_rec.ATTRIBUTE1),
ATTRIBUTE2 =decode(p_wdt_rec.ATTRIBUTE2,G_MISS_CHAR,NULL,NULL,ATTRIBUTE2,p_wdt_rec.ATTRIBUTE2),
ATTRIBUTE3 =decode(p_wdt_rec.ATTRIBUTE3,G_MISS_CHAR,NULL,NULL,ATTRIBUTE3,p_wdt_rec.ATTRIBUTE3),
ATTRIBUTE4 =decode(p_wdt_rec.ATTRIBUTE4,G_MISS_CHAR,NULL,NULL,ATTRIBUTE4,p_wdt_rec.ATTRIBUTE4),
ATTRIBUTE5 =decode(p_wdt_rec.ATTRIBUTE5,G_MISS_CHAR,NULL,NULL,ATTRIBUTE5,p_wdt_rec.ATTRIBUTE5),
ATTRIBUTE6 =decode(p_wdt_rec.ATTRIBUTE6,G_MISS_CHAR,NULL,NULL,ATTRIBUTE6,p_wdt_rec.ATTRIBUTE6),
ATTRIBUTE7 =decode(p_wdt_rec.ATTRIBUTE7,G_MISS_CHAR,NULL,NULL,ATTRIBUTE7,p_wdt_rec.ATTRIBUTE7),
ATTRIBUTE8 =decode(p_wdt_rec.ATTRIBUTE8,G_MISS_CHAR,NULL,NULL,ATTRIBUTE8,p_wdt_rec.ATTRIBUTE8),
ATTRIBUTE9 =decode(p_wdt_rec.ATTRIBUTE9,G_MISS_CHAR,NULL,NULL,ATTRIBUTE9,p_wdt_rec.ATTRIBUTE9),
ATTRIBUTE10 =decode(p_wdt_rec.ATTRIBUTE10,G_MISS_CHAR,NULL,NULL,ATTRIBUTE10,p_wdt_rec.ATTRIBUTE10),
ATTRIBUTE11 =decode(p_wdt_rec.ATTRIBUTE11,G_MISS_CHAR,NULL,NULL,ATTRIBUTE11,p_wdt_rec.ATTRIBUTE11),
ATTRIBUTE12 =decode(p_wdt_rec.ATTRIBUTE12,G_MISS_CHAR,NULL,NULL,ATTRIBUTE12,p_wdt_rec.ATTRIBUTE12),
ATTRIBUTE13 =decode(p_wdt_rec.ATTRIBUTE13,G_MISS_CHAR,NULL,NULL,ATTRIBUTE13,p_wdt_rec.ATTRIBUTE13),
ATTRIBUTE14 =decode(p_wdt_rec.ATTRIBUTE14,G_MISS_CHAR,NULL,NULL,ATTRIBUTE14,p_wdt_rec.ATTRIBUTE14),
ATTRIBUTE15 =decode(p_wdt_rec.ATTRIBUTE15,G_MISS_CHAR,NULL,NULL,ATTRIBUTE15,p_wdt_rec.ATTRIBUTE15),
TASK_TYPE =decode(p_wdt_rec.TASK_TYPE,G_MISS_NUM,NULL,NULL,TASK_TYPE,p_wdt_rec.TASK_TYPE),
PRIORITY =decode(p_wdt_rec.PRIORITY,G_MISS_NUM,NULL,NULL,PRIORITY,p_wdt_rec.PRIORITY),
TASK_GROUP_ID =decode(p_wdt_rec.TASK_GROUP_ID,G_MISS_NUM,NULL,NULL,TASK_GROUP_ID,p_wdt_rec.TASK_GROUP_ID),
DEVICE_ID =decode(p_wdt_rec.DEVICE_ID,G_MISS_NUM,NULL,NULL,DEVICE_ID,p_wdt_rec.DEVICE_ID),
DEVICE_INVOKED =decode(p_wdt_rec.DEVICE_INVOKED,G_MISS_CHAR,NULL,NULL,DEVICE_INVOKED,p_wdt_rec.DEVICE_INVOKED),
DEVICE_REQUEST_ID =decode(p_wdt_rec.DEVICE_REQUEST_ID,G_MISS_NUM,NULL,NULL,DEVICE_REQUEST_ID,p_wdt_rec.DEVICE_REQUEST_ID),
SUGGESTED_DEST_SUBINVENTORY=decode(p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY,G_MISS_CHAR,NULL,NULL,SUGGESTED_DEST_SUBINVENTORY,p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY),
SUGGESTED_DEST_LOCATOR_ID =decode(p_wdt_rec.SUGGESTED_DEST_LOCATOR_ID,G_MISS_NUM,NULL,NULL,SUGGESTED_DEST_LOCATOR_ID,p_wdt_rec.SUGGESTED_DEST_LOCATOR_ID),
OPERATION_PLAN_ID =decode(p_wdt_rec.OPERATION_PLAN_ID,G_MISS_NUM,NULL,NULL,OPERATION_PLAN_ID,p_wdt_rec.OPERATION_PLAN_ID),
MOVE_ORDER_LINE_ID =decode(p_wdt_rec.MOVE_ORDER_LINE_ID,G_MISS_NUM,NULL,NULL,MOVE_ORDER_LINE_ID,p_wdt_rec.MOVE_ORDER_LINE_ID),
TRANSFER_LPN_ID =decode(p_wdt_rec.TRANSFER_LPN_ID,G_MISS_NUM,NULL,NULL,TRANSFER_LPN_ID,p_wdt_rec.transfer_lpn_id),
OP_PLAN_INSTANCE_ID =decode(p_wdt_rec.OP_PLAN_INSTANCE_ID,G_MISS_NUM,NULL,NULL,OP_PLAN_INSTANCE_ID,p_wdt_rec.OP_PLAN_INSTANCE_ID)
WHERE task_id=p_wdt_rec.task_id;
SELECT dest_subinventory_code, dest_locator_id, drop_off_time, source_document_id
FROM wms_dispatched_tasks_history
WHERE parent_transaction_id = p_source_task_id
ORDER BY task_id DESC;
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
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_TYPE_ID
,transaction_action_id
,transaction_temp_id
,SECONDARY_TRANSACTION_QUANTITY --13431255
,SECONDARY_TRANSACTION_UOM_CODE --13431255
)
(SELECT
WDT.TASK_ID
,WDT.TRANSACTION_TEMP_ID
,WDT.ORGANIZATION_ID
,WDT.USER_TASK_TYPE
,WDT.PERSON_ID
,WDT.EFFECTIVE_START_DATE
,WDT.EFFECTIVE_END_DATE
,WDT.EQUIPMENT_ID
,WDT.EQUIPMENT_INSTANCE
,WDT.PERSON_RESOURCE_ID
,WDT.MACHINE_RESOURCE_ID
,6
,WDT.DISPATCHED_TIME
,WDT.LOADED_TIME
,WDT.DROP_OFF_TIME
,SYSDATE
,FND_GLOBAL.USER_ID
,WDT.CREATION_DATE
,WDT.CREATED_BY
,WDT.LAST_UPDATE_LOGIN
,WDT.ATTRIBUTE_CATEGORY
,WDT.ATTRIBUTE1
,WDT.ATTRIBUTE2
,WDT.ATTRIBUTE3
,WDT.ATTRIBUTE4
,WDT.ATTRIBUTE5
,WDT.ATTRIBUTE6
,WDT.ATTRIBUTE7
,WDT.ATTRIBUTE8
,WDT.ATTRIBUTE9
,WDT.ATTRIBUTE10
,WDT.ATTRIBUTE11
,WDT.ATTRIBUTE12
,WDT.ATTRIBUTE13
,WDT.ATTRIBUTE14
,WDT.ATTRIBUTE15
,WDT.TASK_TYPE
,WDT.PRIORITY
,WDT.TASK_GROUP_ID
,Nvl(WDT.suggested_dest_subinventory,pmmtt.subinventory_code)
,Nvl(WDT.suggested_dest_locator_id,pmmtt.locator_id)
,MMTT.OPERATION_PLAN_ID
,MMTT.MOVE_ORDER_LINE_ID
,MMTT.TRANSFER_LPN_ID
,MMTT.TRANSACTION_BATCH_ID
,MMTT.TRANSACTION_BATCH_SEQ
,MMTT.INVENTORY_ITEM_ID
,MMTT.REVISION
,MMTT.TRANSACTION_QUANTITY
,MMTT.TRANSACTION_UOM
,decode(MMTT.TRANSFER_SUBINVENTORY,NULL,NULL,MMTT.SUBINVENTORY_CODE)
,decode(MMTT.TRANSFER_TO_LOCATION,NULL,NULL,MMTT.LOCATOR_ID)
,nvl(MMTT.TRANSFER_SUBINVENTORY,MMTT.SUBINVENTORY_CODE)
,nvl(MMTT.TRANSFER_TO_LOCATION,MMTT.LOCATOR_ID)
,MMTT.LPN_ID
,MMTT.CONTENT_LPN_ID
,'N'
,MMTT.PARENT_LINE_ID
,MMTT.TRANSFER_ORGANIZATION
,NVL(MMTT.rcv_transaction_id,MMTT.transaction_header_id)
,WDT.OP_PLAN_INSTANCE_ID
,mmtt.transaction_source_type_id
,mmtt.transaction_type_id
,mmtt.transaction_action_id
,mmtt.transaction_temp_id
,mmtt.SECONDARY_TRANSACTION_QUANTITY -- 13431255
,mmtt.SECONDARY_UOM_CODE -- 13431255
FROM WMS_DISPATCHED_TASKS wdt
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, mtl_material_transactions_temp pmmtt
WHERE WDT.TASK_ID=p_task_id
AND WDT.transaction_temp_id=MMTT.transaction_temp_id
AND mmtt.parent_line_id = pmmtt.transaction_temp_id (+)
);
/*Archive Tasks,now delete records from WDT*/
IF (l_debug=1) THEN
print_debug('Archived WDT,now deleting the WDT record',l_module_name,9);
DELETE FROM wms_dispatched_tasks
WHERE task_id = p_task_id;
SELECT parent_transaction_id
INTO l_parent_txn_id
FROM rcv_transactions
WHERE transaction_id = l_current_txn_id;
print_debug('Inserting Records for Parent with source_task_id'||p_source_task_id,l_module_name,9);
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
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_TYPE_ID
,transaction_action_id
,transaction_temp_id
,SECONDARY_TRANSACTION_QUANTITY --13431255
,SECONDARY_TRANSACTION_UOM_CODE --13431255
)
( SELECT
wms_dispatched_tasks_s.NEXTVAL
,MMTT.TRANSACTION_TEMP_ID
,MMTT.ORGANIZATION_ID
,-1
,-1
,SYSDATE
,SYSDATE
,NULL
,NULL
,NULL
,NULL
,decode(p_op_plan_status,3,6,4,12,5,11,6)
,NULL
,NULL
,l_last_drop_off_time
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,MMTT.WMS_TASK_TYPE
,NULL
,NULL
,nvl(MMTT.TRANSFER_SUBINVENTORY,MMTT.SUBINVENTORY_CODE)
,nvl(MMTT.TRANSFER_TO_LOCATION,MMTT.LOCATOR_ID)
,MMTT.OPERATION_PLAN_ID
,MMTT.MOVE_ORDER_LINE_ID
,MMTT.TRANSFER_LPN_ID
,MMTT.TRANSACTION_BATCH_ID
,MMTT.TRANSACTION_BATCH_SEQ
,MMTT.INVENTORY_ITEM_ID
,MMTT.REVISION
,MMTT.TRANSACTION_QUANTITY
,MMTT.TRANSACTION_UOM
,decode(MMTT.TRANSFER_SUBINVENTORY,NULL,NULL,MMTT.SUBINVENTORY_CODE)
,decode(MMTT.TRANSFER_TO_LOCATION,NULL,NULL,MMTT.LOCATOR_ID)
,l_last_operation_dest_sub
,l_last_operation_dest_loc_id
,MMTT.LPN_ID
,MMTT.CONTENT_LPN_ID
,'Y'
,NULL
,MMTT.TRANSFER_ORGANIZATION
,Decode(mmtt.wms_task_type,2,Nvl(mmtt.transaction_source_id,l_parent_txn_id),mmtt.transaction_source_id)
,p_op_plan_instance_id
,mmtt.transaction_source_type_id
,mmtt.transaction_type_id
,mmtt.transaction_action_id
,mmtt.transaction_temp_id
,mmtt.SECONDARY_TRANSACTION_QUANTITY -- 13431255
,mmtt.SECONDARY_UOM_CODE -- 13431255
FROM mtl_material_transactions_temp MMTT
WHERE transaction_temp_id=p_source_task_id);