[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT labor_management_enabled_flag
INTO l_labor_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT labor_enabled
INTO l_labor_enabled_flag
FROM wms_zones_b
WHERE organization_id = p_org_id
AND zone_id = p_zone_id;
SELECT 1
INTO l_user_id
FROM WMS_ELS_NON_TRACKED_USERS
WHERE USER_ID = P_USER_ID
AND ORGANIZATION_ID= P_ORG_ID;
CURSOR els_history(l_org_id NUMBER,l_purge_date VARCHAR2) IS SELECT els_data_id
FROM wms_els_individual_tasks_b
WHERE history_flag = 1
AND organization_id = l_org_id
AND archive_date <= NVL (TO_DATE(l_purge_date,'YYYY/MM/DD HH24:MI:SS') ,SYSDATE);
l_records_deleted NUMBER;
l_records_deleted :=0;
DELETE wms_els_trx_src
WHERE els_data_id = l_els_id_tab(i);
l_records_deleted :=SQL%ROWCOUNT;
debug('Number of records deleted from wms_els_trx_src table '|| l_records_deleted,
'PURGE_LMS_SETUP_HISTORY'
);
l_records_deleted:=0;
DELETE wms_els_individual_tasks_b
WHERE els_data_id = l_els_id_tab(j);
l_records_deleted :=SQL%ROWCOUNT;
debug('Number of records deleted from wms_els_individual_tasks_b table '|| l_records_deleted,
'PURGE_LMS_SETUP_HISTORY'
);
l_records_deleted:=0;
DELETE wms_els_individual_tasks_tl
WHERE els_data_id = l_els_id_tab(k);
l_records_deleted :=SQL%ROWCOUNT;
debug('Number of records deleted from wms_els_individual_tasks_tl table '|| l_records_deleted,
'PURGE_LMS_SETUP_HISTORY'
);
l_records_inserted NUMBER;
l_records_inserted :=0;
INSERT INTO WMS_ELS_TRX_SRC
(els_trx_src_id,
els_data_id,
organization_id,
travel_time,
transaction_time,
idle_time,
last_update_date,
last_updated_by,
created_by,
creation_date
)
SELECT WMS_ELS_TRX_SRC_S.NEXTVAL,
els_data_id,
organization_id,
travel_time,
transaction_time,
idle_time,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
SYSDATE
FROM
(SELECT ELS_DATA_ID els_data_id,
organization_id,
AVG(TRAVEL_TIME) travel_time,
AVG(TRANSACTION_TIME) transaction_time,
AVG(IDLE_TIME) Idle_time
FROM WMS_ELS_TRX_SRC
WHERE transaction_date < p_purge_date
AND organization_id = p_org_id
AND els_data_id IS NOT NULL
GROUP BY ELS_DATA_ID,organization_id
);
l_records_inserted := SQL%ROWCOUNT;
debug( 'The number of bucketed records inserted are '|| l_records_inserted
,'BUCKET_ACTUAL_TIMINGS');
l_records_deleted NUMBER;
l_records_deleted := 0;
DELETE wms_els_trx_src
WHERE transaction_date <= NVL(TO_DATE(p_purge_date,'YYYY/MM/DD HH24:MI:SS'),SYSDATE)
AND organization_id = p_org_id;
l_records_deleted :=SQL%ROWCOUNT;
debug( 'Number of rows deleted'|| l_records_deleted
,'PURGE_LMS_TRANSACTIONS'
);
l_records_updated NUMBER;
SELECT ELS_DATA_ID
, ORGANIZATION_ID
, SEQUENCE_NUMBER
, ANALYSIS_ID
, ACTIVITY_ID
, ACTIVITY_DETAIL_ID
, OPERATION_ID
, EQUIPMENT_ID
, SOURCE_ZONE_ID
, SOURCE_SUBINVENTORY
, DESTINATION_ZONE_ID
, DESTINATION_SUBINVENTORY
, LABOR_TXN_SOURCE_ID
, TRANSACTION_UOM
, FROM_QUANTITY
, TO_QUANTITY
, ITEM_CATEGORY_ID
, OPERATION_PLAN_ID
, GROUP_ID
, TASK_TYPE_ID
, TASK_METHOD_ID
, EXPECTED_TRAVEL_TIME
, EXPECTED_TXN_TIME
, EXPECTED_IDLE_TIME
, ACTUAL_TRAVEL_TIME
, ACTUAL_TXN_TIME
, ACTUAL_IDLE_TIME
, TRAVEL_TIME_THRESHOLD
, ARCHIVE_DATE
, NUM_TRX_MATCHED
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
FROM wms_els_individual_tasks_b
WHERE organization_id= l_org_id
AND nvl(HISTORY_FLAG, -999) <> 1;
SELECT description
, language
, source_lang
FROM wms_els_individual_tasks_tl
WHERE els_data_id = l_els_data_id;
l_records_updated:=0;
SELECT WMS_ELS_INDIVIDUAL_TASKS_S.NEXTVAL
INTO l_new_els_data_id
FROM dual;
INSERT INTO wms_els_individual_tasks_b (
ELS_DATA_ID
, ORGANIZATION_ID
, SEQUENCE_NUMBER
, ANALYSIS_ID
, ACTIVITY_ID
, ACTIVITY_DETAIL_ID
, OPERATION_ID
, EQUIPMENT_ID
, SOURCE_ZONE_ID
, SOURCE_SUBINVENTORY
, DESTINATION_ZONE_ID
, DESTINATION_SUBINVENTORY
, LABOR_TXN_SOURCE_ID
, TRANSACTION_UOM
, FROM_QUANTITY
, TO_QUANTITY
, ITEM_CATEGORY_ID
, OPERATION_PLAN_ID
, GROUP_ID
, TASK_TYPE_ID
, TASK_METHOD_ID
, EXPECTED_TRAVEL_TIME
, EXPECTED_TXN_TIME
, EXPECTED_IDLE_TIME
, ACTUAL_TRAVEL_TIME
, ACTUAL_TXN_TIME
, ACTUAL_IDLE_TIME
, TRAVEL_TIME_THRESHOLD
, HISTORY_FLAG
, ARCHIVE_DATE
, NUM_TRX_MATCHED
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, LAST_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15)
VALUES ( l_new_els_data_id
, els_individual_tasks_rec.ORGANIZATION_ID
, els_individual_tasks_rec.SEQUENCE_NUMBER
, els_individual_tasks_rec.ANALYSIS_ID
, els_individual_tasks_rec.ACTIVITY_ID
, els_individual_tasks_rec.ACTIVITY_DETAIL_ID
, els_individual_tasks_rec.OPERATION_ID
, els_individual_tasks_rec.EQUIPMENT_ID
, els_individual_tasks_rec.SOURCE_ZONE_ID
, els_individual_tasks_rec.SOURCE_SUBINVENTORY
, els_individual_tasks_rec.DESTINATION_ZONE_ID
, els_individual_tasks_rec.DESTINATION_SUBINVENTORY
, els_individual_tasks_rec.LABOR_TXN_SOURCE_ID
, els_individual_tasks_rec.TRANSACTION_UOM
, els_individual_tasks_rec.FROM_QUANTITY
, els_individual_tasks_rec.TO_QUANTITY
, els_individual_tasks_rec.ITEM_CATEGORY_ID
, els_individual_tasks_rec.OPERATION_PLAN_ID
, els_individual_tasks_rec.GROUP_ID
, els_individual_tasks_rec.TASK_TYPE_ID
, els_individual_tasks_rec.TASK_METHOD_ID
, els_individual_tasks_rec.EXPECTED_TRAVEL_TIME
, els_individual_tasks_rec.EXPECTED_TXN_TIME
, els_individual_tasks_rec.EXPECTED_IDLE_TIME
, els_individual_tasks_rec.ACTUAL_TRAVEL_TIME
, els_individual_tasks_rec.ACTUAL_TXN_TIME
, els_individual_tasks_rec.ACTUAL_IDLE_TIME
, els_individual_tasks_rec.TRAVEL_TIME_THRESHOLD
, 1 -- The new record created will be a history record. Hence, it takes the values as 1.
, SYSDATE -- ARCHIVE_DATE
, els_individual_tasks_rec.NUM_TRX_MATCHED
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, FND_GLOBAL.USER_ID
, SYSDATE
, SYSDATE
, els_individual_tasks_rec.ATTRIBUTE_CATEGORY
, els_individual_tasks_rec.ATTRIBUTE1
, els_individual_tasks_rec.ATTRIBUTE2
, els_individual_tasks_rec.ATTRIBUTE3
, els_individual_tasks_rec.ATTRIBUTE4
, els_individual_tasks_rec.ATTRIBUTE5
, els_individual_tasks_rec.ATTRIBUTE6
, els_individual_tasks_rec.ATTRIBUTE7
, els_individual_tasks_rec.ATTRIBUTE8
, els_individual_tasks_rec.ATTRIBUTE9
, els_individual_tasks_rec.ATTRIBUTE10
, els_individual_tasks_rec.ATTRIBUTE12
, els_individual_tasks_rec.ATTRIBUTE13
, els_individual_tasks_rec.ATTRIBUTE14
, els_individual_tasks_rec.ATTRIBUTE15);
/* Insert the history record data in TL tables*/
FOR els_individual_tasks_tl_rec IN els_individual_tasks_tl_cur(els_individual_tasks_rec.els_data_id) LOOP
INSERT INTO wms_els_individual_tasks_tl(
ELS_DATA_ID
, LANGUAGE
, SOURCE_LANG
, DESCRIPTION
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, LAST_UPDATE_DATE)
VALUES( l_new_els_data_id
, els_individual_tasks_tl_rec.language
, els_individual_tasks_tl_rec.source_lang
, els_individual_tasks_tl_rec.description
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, FND_GLOBAL.USER_ID
, SYSDATE
, SYSDATE);
UPDATE wms_els_individual_tasks_b
SET
Expected_Travel_Time = NVL(Actual_Travel_time,Expected_Travel_Time),
Expected_Txn_Time = NVL(Actual_Txn_Time,Expected_Txn_Time),
Expected_Idle_Time = NVL(Actual_Idle_Time,Expected_Idle_Time),
NUM_TRX_MATCHED = NULL, -- Added for bug # 5520074
LAST_UPDATED_BY = FND_GLOBAL.USER_ID, -- Added for bug # 5520074
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, -- Added for bug # 5520074
LAST_UPDATE_DATE = SYSDATE -- Added for bug # 5520074
WHERE
organization_id= p_org_id
AND history_flag IS NULL;
l_records_updated := SQL%ROWCOUNT;
debug( 'The number of setup records where expectyed timing has been copied are '|| l_records_updated
,'COPY_ACTUAL_TIMINGS');
l_records_updated := 0;
UPDATE wms_els_grouped_tasks_b
SET
Expected_Travel_Time = NVL(Actual_Travel_time,Expected_Travel_Time)
WHERE
organization_id= p_org_id;
l_records_updated := SQL%ROWCOUNT;
debug( 'The number of setup records where expected timing has been copied are '|| l_records_updated
,'COPY_ACTUAL_TIMINGS');
SELECT els_data_id FROM WMS_ELS_INDIVIDUAL_TASKS_B WHERE
organization_id=l_organization_id
and history_flag IS NULL;
l_records_updated NUMBER;
SELECT time_frame_average_id INTO l_time_frame_average_id
FROM WMS_ELS_PARAMETERS WHERE organization_id = p_org_id;
l_records_updated := 0;
UPDATE wms_els_individual_tasks_b SET
actual_travel_time=(
SELECT AVG(travel_time) FROM WMS_ELS_TRX_SRC
WHERE els_data_id=l_els_data_id_tab(i)
AND NVL(Transaction_Date,SYSDATE) >= NVL(l_date,SYSDATE)
AND (
(l_time_frame_average_id =3)
OR ((l_time_frame_average_id <> 3 ) AND (TRANSACTION_DATE IS NOT NULL))
)
),
actual_txn_time =(
SELECT AVG(Transaction_time) FROM WMS_ELS_TRX_SRC
WHERE els_data_id=l_els_data_id_tab(i)
AND NVL(Transaction_Date,SYSDATE) >= NVL(l_date,SYSDATE)
AND (
(l_time_frame_average_id =3)
OR ((l_time_frame_average_id <> 3 ) AND (TRANSACTION_DATE IS NOT NULL))
)
),
actual_idle_time=(
SELECT AVG(idle_time) FROM WMS_ELS_TRX_SRC
WHERE els_data_id=l_els_data_id_tab(i)
AND NVL(Transaction_Date,SYSDATE) >= NVL(l_date,SYSDATE)
AND (
(l_time_frame_average_id =3)
OR ((l_time_frame_average_id <> 3 ) AND (TRANSACTION_DATE IS NOT NULL))
)
)
WHERE els_data_id = l_els_data_id_tab(i);
l_records_updated := SQL%ROWCOUNT;
debug('Number of records updated'||l_records_updated ,'CALCULATE_ACTUAL_TIMINGS');
select 1 into is_unprocessed from dual where exists (select 1 from wms_els_exp_resource
where els_data_id IS NULL and organization_id = p_org_id
);
select 1 into is_unprocessed from dual where exists (select 1 from wms_els_trx_src
where els_data_id IS NULL
and organization_id = p_org_id
and els_trx_src_id <= p_max_id
);
select ARGUMENT2,ARGUMENT3,
ARGUMENT4,ARGUMENT5
into l_argument2,l_argument3,
l_argument4,l_argument5
from fnd_concurrent_requests where argument1 = to_char(p_org_id)
and request_id = (select Max(Request_ID)
From Fnd_Concurrent_Requests
Where Concurrent_Program_ID = p_concurrent_program_id
AND PHASE_CODE = 'C' and argument1 = to_char(p_org_id)); -- added this condition for bug 5478746
select meaning into l_meaning from mfg_lookups where lookup_code = l_argument2
and lookup_type = 'WMS_ELS_FUTURE_PERIOD';
select min(requested_start_date) into l_next_scheduled_date
from fnd_concurrent_requests
where concurrent_program_id = p_concurrent_program_id
and program_application_id = p_application_id
and phase_code = 'P'
and STATUS_CODE IN ( 'I','Q')
and argument1 = to_char(p_org_id);
select actual_completion_date
into l_actual_completion_date
from fnd_concurrent_requests where argument1 = to_char(p_org_id)
and request_id = (select Max(Request_ID)
From Fnd_Concurrent_Requests
Where Concurrent_Program_ID = p_concurrent_program_id
AND PHASE_CODE = 'C'
and argument1 = to_char(p_org_id)); -- added this condition for bug 5478746
select actual_completion_date
into l_actual_completion_date
from fnd_concurrent_requests where argument1 = to_char(p_org_id)
and request_id = (select Max(Request_ID)
From Fnd_Concurrent_Requests
Where Concurrent_Program_ID = p_concurrent_program_id
AND PHASE_CODE = 'C'
AND STATUS_CODE = 'C'
and argument1 = to_char(p_org_id)); -- added this condition for bug 5478746
select fl.meaning
into l_last_run_status
from fnd_concurrent_requests fcr, fnd_lookups fl
where argument1 = to_char(p_org_id)
and fl.lookup_code = fcr.status_code
and fl.Lookup_type = 'CP_STATUS_CODE'
and request_id = (select Max(Request_ID)
from Fnd_Concurrent_Requests
where Concurrent_Program_ID = p_concurrent_program_id
AND PHASE_CODE = 'C'
and argument1 = to_char(p_org_id)); -- added this condition for bug 5478746
SELECT COUNT(*) || ' ' || NVL(DOCUMENT_TYPE, ML1.MEANING) AS WORK
FROM WMS_ELS_EXP_RESOURCE, MFG_LOOKUPS ML1
WHERE ORGANIZATION_ID = p_OrgId
AND ACTIVITY_ID = p_ActivityId
AND ACTIVITY_DETAIL_ID = p_ActivityDetailId
AND ML1.LOOKUP_TYPE = 'WMS_ELS_TASKS_LOOKUP'
GROUP BY DOCUMENT_TYPE, ML1.MEANING;
SELECT COUNT(*) AS WORK
FROM WMS_ELS_EXP_RESOURCE, MFG_LOOKUPS ML1
WHERE ORGANIZATION_ID = p_OrgId
AND ACTIVITY_ID = p_ActivityId
AND ACTIVITY_DETAIL_ID = p_ActivityDetailId
AND ML1.LOOKUP_TYPE = 'WMS_ELS_TASKS_LOOKUP';
SELECT meaning
INTO rating
FROM mfg_lookups
WHERE lookup_type = 'WMS_LABOR_RATINGS' AND lookup_code = l_points;
select round(MAX(sequence_number),-1) into x_seq_num_ind_and_sys_directed
from WMS_ELS_INDIVIDUAL_TASKS_B
where group_id= 3
AND history_flag IS null
AND organization_id = p_org_id;-- individual group
select round(MAX(sequence_number),-1) into x_seq_num_man_and_sys_directed
from WMS_ELS_INDIVIDUAL_TASKS_B
where group_id= 2
AND history_flag IS null
AND organization_id = p_org_id; -- manual group
select round(MAX(sequence_number),-1) into x_seq_num_man_and_usr_directed
from WMS_ELS_INDIVIDUAL_TASKS_B
where group_id= 1
AND history_flag IS null
AND organization_id = p_org_id; -- manual group
select round(MAX(sequence_number),-1) into x_seq_num_grouped
from WMS_ELS_GROUPED_TASKS_B
where organization_id = p_org_id; -- grouped tasks
X_NUM_LINES_INSERTED_TASKS OUT NOCOPY NUMBER
, X_NUM_LINES_INSERTED_GROUP OUT NOCOPY NUMBER
, X_RETURN_STATUS OUT NOCOPY VARCHAR2
, X_MSG_NAME OUT NOCOPY VARCHAR2
, P_COPY_ID IN VARCHAR2
, P_COPY_ANALYSIS IN VARCHAR2
, P_ORG_ID IN NUMBER
) IS
l_seq_num_ind_and_sys_directed NUMBER;
l_ind_or_man_tasks_inserted NUMBER;
X_MSG_NAME := 'WMS_NO_LINES_SELECTED';
X_MSG_NAME := 'WMS_NO_LINES_SELECTED';
debug('Before inserting into individual tasks table','STANDARDIZE_LINES');
l_ind_or_man_tasks_inserted := 0;
INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B
(
els_data_id
, organization_id
, sequence_number
, analysis_id
, activity_id
, activity_detail_id
, operation_id
, equipment_id
, source_zone_id
, source_subinventory
, destination_zone_id
, destination_subinventory
, labor_txn_source_id
, transaction_uom
, from_quantity
, to_quantity
, item_category_id
, operation_plan_id
, group_id
, task_type_id
, task_method_id
, expected_travel_time
, expected_txn_time
, expected_idle_time
, actual_travel_time
, actual_txn_time
, actual_idle_time
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
)
SELECT
wms_els_individual_tasks_s.nextval
, organization_id
, decode(group_id,1,nvl(l_seq_num_man_and_usr_directed,0)+10,
2,nvl(l_seq_num_man_and_sys_directed,0) + 10,
3,nvl(l_seq_num_ind_and_sys_directed,0) + 10
)
, l_analysis_id
, activity_id
, activity_detail_id
, operation_id
, equipment_id
, source_zone_id
, source_subinventory
, destination_zone_id
, destination_subinventory
, labor_txn_source_id
, transaction_uom
, quantity
, quantity
, item_category_id
, operation_plan_id
, group_id
, task_type_id
, task_method_id
, travel_and_idle_time
, transaction_time
, NULL
, travel_and_idle_time
, transaction_time
, idle_time
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.LOGIN_ID
, FND_GLOBAL.USER_ID
, sysdate
FROM WMS_ELS_TRX_SRC
WHERE ELS_DATA_ID IS NULL
AND ELS_TRX_SRC_ID = l_els_trx_src_id
AND UNATTRIBUTED_FLAG = 1 ;
/* Added the following select statement for bug 5194353 */
SELECT nvl(group_id,0) into l_grp_id
FROM WMS_ELS_TRX_SRC
WHERE ELS_TRX_SRC_ID = l_els_trx_src_id;
l_ind_or_man_tasks_inserted := l_ind_or_man_tasks_inserted + 1;
-- X_NUM_LINES_INSERTED_TASKS := X_NUM_LINES_INSERTED_TASKS + SQL%ROWCOUNT;
X_NUM_LINES_INSERTED_TASKS := l_ind_or_man_tasks_inserted;
l_ind_or_man_tasks_inserted := l_ind_or_man_tasks_inserted;
X_MSG_NAME := 'WMS_LMS_LINES_UPDATE_ERROR';
X_NUM_LINES_INSERTED_TASKS := 0;
X_NUM_LINES_INSERTED_GROUP := 0;
debug('Error Occured in Individual Task Insertion ' || SQLERRM ,'STANDARDIZE_LINES');
INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_TL
(
els_data_id,
language,
source_lang,
description,
last_updated_by,
last_update_login,
created_by,
creation_date,
last_update_date
)
SELECT b.els_data_id
, l.language_code
, userenv('lang') --Have to change this line. Need to give a Source Language.
, null
, b.last_updated_by
, b.last_update_login
, b.created_by
, b.creation_date
, b.last_update_date
FROM WMS_ELS_INDIVIDUAL_TASKS_B b,
FND_LANGUAGES L
WHERE els_data_id NOT IN (
SELECT DISTINCT els_data_id
FROM wms_els_individual_tasks_tl
)
AND L.INSTALLED_FLAG in ('I', 'B');
debug('Num lines inserted into WMS_ELS_INDIVIDUAL_TASKS_B '||X_NUM_LINES_INSERTED_TASKS ,'STANDARDIZE_LINES');
debug('Before inserting into grouped tasks table','STANDARDIZE_LINES');
'INSERT INTO WMS_ELS_GROUPED_TASKS_B' ||
'(' ||
' Els_Group_Id' ||
' , Organization_id' ||
' , Sequence_Number' ||
' , activity_id' ||
' , activity_detail_id' ||
' , operation_id' ||
' , labor_txn_source_id' ||
' , source_zone_id' ||
' , source_subinventory' ||
' , destination_zone_id' ||
' , destination_subinventory' ||
' , task_method_id' ||
' , task_range_from' ||
' , task_range_to' ||
' , expected_travel_time' ||
' , actual_travel_time' ||
' , last_updated_by' ||
' , last_update_date' ||
' , last_update_login' ||
' , created_by' ||
' , creation_date' ||
')' ||
'SELECT wms_els_grouped_tasks_s.NEXTVAL' ||
' , organization_id' ||
' , (nvl('||l_seq_num_grouped||',0) + (10*ROWNUM)) sequence_number' ||
' , activity_id' ||
' , activity_detail_id' ||
' , operation_id' ||
' , labor_txn_source_id ' ||
' , source_zone_id' ||
' , source_subinventory' ||
' , destination_zone_id' ||
' , destination_subinventory' ||
' , task_method_id' ||
' , task_range_from' ||
' , task_range_to' ||
' , exp_travel_time' ||
' , act_travel_Time' ||
' , FND_GLOBAL.USER_ID last_updated_by ' ||
' , sysdate last_update_date' ||
' , FND_GLOBAL.LOGIN_ID last_update_login' ||
' , FND_GLOBAL.USER_ID created_by' ||
' , sysdate creation_date' ||
' FROM ' ||
'( select organization_id' ||
' , activity_id' ||
' , activity_detail_id' ||
' , operation_id' ||
' , source_zone_id' ||
' , source_subinventory' ||
' , destination_zone_id' ||
' , destination_subinventory' ||
' , labor_txn_source_id' ||
' , task_method_id' ||
' , count(*) task_range_from' ||
' , count(*) task_range_to' ||
' , sum(travel_and_idle_time) exp_travel_time' ||
' , sum(travel_and_idle_time) act_travel_Time' ||
' FROM wms_els_trx_src' ||
' where els_data_id is null' ||
' and organization_id = '|| p_org_id ||
' and Els_Trx_Src_Id IN (' || P_COPY_ID || ') '||
' and unattributed_flag = 1 '||
' and grouped_Task_identifier IS NOT NULL'||
' group by grouped_Task_identifier' ||
' , organization_id' ||
' , activity_id' ||
' , activity_detail_id' ||
' , operation_id' ||
' , source_zone_id' ||
' , source_subinventory' ||
' , destination_zone_id' ||
' , destination_subinventory' ||
' , labor_txn_source_id' ||
' , task_method_id' ||
' )';
X_NUM_LINES_INSERTED_GROUP := SQL%ROWCOUNT;
debug('Num lines inserted in groups table'||X_NUM_LINES_INSERTED_GROUP ,'STANDARDIZE_LINES');
INSERT INTO WMS_ELS_GROUPED_TASKS_TL
(
els_group_id,
language,
source_lang,
description,
last_updated_by,
last_update_login,
created_by,
creation_date,
last_update_date
)
SELECT b.els_group_id,
l.language_code,
userenv('lang'), --Have to change this line. Need to give a Source Language.
null,
b.last_updated_by,
b.last_update_login,
b.created_by,
b.creation_date,
b.last_update_date
FROM WMS_ELS_GROUPED_TASKS_B b,
FND_LANGUAGES L
WHERE els_group_id NOT IN (
SELECT DISTINCT els_group_id
FROM wms_els_grouped_tasks_tl
)
AND L.INSTALLED_FLAG in ('I', 'B');
'UPDATE wms_els_trx_src ' ||
'SET unattributed_flag = null ' ||
'WHERE Els_Trx_Src_Id IN (' || P_COPY_ID || ')';
debug('In Exception No lines inserted'||SQLERRM ,'STANDARDIZE_LINES');
X_MSG_NAME := 'WMS_LMS_LINES_UPDATE_ERROR';
X_NUM_LINES_INSERTED_TASKS := 0;
X_NUM_LINES_INSERTED_GROUP := 0;
l_num_lines_inserted NUMBER;
l_num_lines_inserted :=0;
' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
|| ' ( '
|| ' els_data_id '
|| ' , organization_id '
|| ' , sequence_number '
|| ' , analysis_id '
|| ' , activity_id '
|| ' , activity_detail_id '
|| ' , operation_id '
|| ' , equipment_id '
|| ' , source_zone_id '
|| ' , source_subinventory '
|| ' , destination_zone_id '
|| ' , destination_subinventory '
|| ' , labor_txn_source_id '
|| ' , operation_plan_id '
|| ' , group_id '
|| ' , task_type_id '
|| ' , task_method_id '
|| ' , expected_travel_time '
|| ' , expected_txn_time '
|| ' , expected_idle_time '
|| ' , actual_travel_time '
|| ' , actual_txn_time '
|| ' , actual_idle_time '
|| ' , last_updated_by '
|| ' , last_update_date '
|| ' , last_update_login '
|| ' , created_by '
|| ' , creation_date '
|| ' ) '
|| ' SELECT '
|| ' wms_els_individual_tasks_s.NEXTVAL ' -- els_data_id
|| ' , organization_id ' --organization_id
|| ' , nvl ( ' || l_seq_num_man_and_usr_directed || ',0)+10*ROWNUM ' --sequence_number
|| ' , ' || p_analysis_type --analysis_id
|| ' , activity_id ' --activity_id
|| ' , activity_detail_id ' --activity_detail_id
|| ' , operation_id ' --operation_id
|| ' , equipment_id ' --equipment_id
|| ' , source_zone_id ' --source_zone_id
|| ' , source_subinventory '--source_subinventory
|| ' , destination_zone_id ' --destination_zone_id
|| ' , destination_subinventory ' --destination_subinventory
|| ' , labor_txn_source_id ' --labor_txn_source_id
|| ' , operation_plan_id ' --operation_plan_id
|| ' , group_id ' --group_id
|| ' , task_type_id ' --task_type_id
|| ' , task_method_id ' --task_method_id
|| ' , travel_and_idle_time ' --expected_travel_time
|| ' , transaction_time ' --expected_txn_time
|| ' , NULL' --expected_idle_time
|| ' , travel_and_idle_time ' --actual_travel_time
|| ' , transaction_time ' --actual_txn_time
|| ' , idle_time ' --actual_idle_time
|| ' , FND_GLOBAL.USER_ID '
|| ' , sysdate '
|| ' , FND_GLOBAL.LOGIN_ID '
|| ' , FND_GLOBAL.USER_ID '
|| ' , sysdate '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' organization_id ' --organization_id
|| ' , activity_id ' --activity_id
|| ' , activity_detail_id ' --activity_detail_id
|| ' , operation_id ' --operation_id
|| ' , equipment_id ' --equipment_id
|| ' , source_zone_id ' --source_zone_id
|| ' , source_subinventory '--source_subinventory
|| ' , destination_zone_id ' --destination_zone_id
|| ' , destination_subinventory ' --destination_subinventory
|| ' , labor_txn_source_id ' --labor_txn_source_id
|| ' , operation_plan_id ' --operation_plan_id
|| ' , group_id ' --group_id
|| ' , task_type_id ' --task_type_id
|| ' , task_method_id ' --task_method_id
|| ' , ROUND(AVG(travel_and_idle_time),3) travel_and_idle_time ' --expected_travel_time
|| ' , ROUND(AVG(transaction_time),3) transaction_time' --expected_txn_time
|| ' , ROUND(AVG(idle_time),3) idle_time '
|| ' FROM WMS_ELS_TRX_SRC '
|| ' WHERE UNATTRIBUTED_FLAG = 1 '
|| ' AND organization_id = ' || p_org_id
|| ' AND group_id = 1 '
|| l_where_clause
|| ' GROUP BY '
|| ' organization_id,activity_id,activity_detail_id,operation_id '
|| ' ,equipment_id,source_zone_id,source_subinventory,destination_zone_id '
|| ' ,destination_subinventory,labor_txn_source_id,operation_plan_id '
|| ' ,group_id,task_type_id,task_method_id '
|| ' )';
l_num_lines_inserted := SQL%ROWCOUNT;
debug('Number of lines inserted for manual and user directed tasks'|| l_num_lines_inserted,'STANDARDIZE_LINES_CP');
l_num_lines_inserted :=0;--reinitialize
' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
|| ' ( '
|| ' els_data_id '
|| ' , organization_id '
|| ' , sequence_number '
|| ' , analysis_id '
|| ' , activity_id '
|| ' , activity_detail_id '
|| ' , operation_id '
|| ' , equipment_id '
|| ' , source_zone_id '
|| ' , source_subinventory '
|| ' , destination_zone_id '
|| ' , destination_subinventory '
|| ' , labor_txn_source_id '
|| ' , operation_plan_id '
|| ' , group_id '
|| ' , task_type_id '
|| ' , task_method_id '
|| ' , expected_travel_time '
|| ' , expected_txn_time '
|| ' , expected_idle_time '
|| ' , actual_travel_time '
|| ' , actual_txn_time '
|| ' , actual_idle_time '
|| ' , last_updated_by '
|| ' , last_update_date '
|| ' , last_update_login '
|| ' , created_by '
|| ' , creation_date '
|| ' ) '
|| ' SELECT '
|| ' wms_els_individual_tasks_s.NEXTVAL ' -- els_data_id
|| ' , organization_id ' --organization_id
|| ' , nvl ( ' || l_seq_num_man_and_sys_directed || ',0)+10*ROWNUM ' --sequence_number
|| ' , ' || p_analysis_type --analysis_id
|| ' , activity_id ' --activity_id
|| ' , activity_detail_id ' --activity_detail_id
|| ' , operation_id ' --operation_id
|| ' , equipment_id ' --equipment_id
|| ' , source_zone_id ' --source_zone_id
|| ' , source_subinventory '--source_subinventory
|| ' , destination_zone_id ' --destination_zone_id
|| ' , destination_subinventory ' --destination_subinventory
|| ' , labor_txn_source_id ' --labor_txn_source_id
|| ' , operation_plan_id ' --operation_plan_id
|| ' , group_id ' --group_id
|| ' , task_type_id ' --task_type_id
|| ' , task_method_id ' --task_method_id
|| ' , travel_and_idle_time ' --expected_travel_time
|| ' , transaction_time ' --expected_txn_time
|| ' , NULL' --expected_idle_time
|| ' , travel_and_idle_time ' --actual_travel_time
|| ' , transaction_time ' --actual_txn_time
|| ' , idle_time ' --actual_idle_time
|| ' , FND_GLOBAL.USER_ID '
|| ' , sysdate '
|| ' , FND_GLOBAL.LOGIN_ID '
|| ' , FND_GLOBAL.USER_ID '
|| ' , sysdate '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' organization_id ' --organization_id
|| ' , activity_id ' --activity_id
|| ' , activity_detail_id ' --activity_detail_id
|| ' , operation_id ' --operation_id
|| ' , equipment_id ' --equipment_id
|| ' , source_zone_id ' --source_zone_id
|| ' , source_subinventory '--source_subinventory
|| ' , destination_zone_id ' --destination_zone_id
|| ' , destination_subinventory ' --destination_subinventory
|| ' , labor_txn_source_id ' --labor_txn_source_id
|| ' , operation_plan_id ' --operation_plan_id
|| ' , group_id ' --group_id
|| ' , task_type_id ' --task_type_id
|| ' , task_method_id ' --task_method_id
|| ' , ROUND(AVG(travel_and_idle_time),3) travel_and_idle_time ' --expected_travel_time
|| ' , ROUND(AVG(transaction_time),3) transaction_time' --expected_txn_time
|| ' , ROUND(AVG(idle_time),3) idle_time '
|| ' FROM WMS_ELS_TRX_SRC '
|| ' WHERE UNATTRIBUTED_FLAG = 1 '
|| ' AND organization_id = ' || p_org_id
|| ' AND group_id = 2 '
|| l_where_clause
|| ' GROUP BY '
|| ' organization_id,activity_id,activity_detail_id,operation_id '
|| ' ,equipment_id,source_zone_id,source_subinventory,destination_zone_id '
|| ' ,destination_subinventory,labor_txn_source_id,operation_plan_id '
|| ' ,group_id,task_type_id,task_method_id '
|| ' )';
l_num_lines_inserted := SQL%ROWCOUNT;
debug('Number of lines inserted for manual and system directed tasks'|| l_num_lines_inserted,'STANDARDIZE_LINES_CP');
l_num_lines_inserted :=0;--reinitialize
' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
|| ' ( '
|| ' els_data_id '
|| ' , organization_id '
|| ' , sequence_number '
|| ' , analysis_id '
|| ' , activity_id '
|| ' , activity_detail_id '
|| ' , operation_id '
|| ' , equipment_id '
|| ' , source_zone_id '
|| ' , source_subinventory '
|| ' , destination_zone_id '
|| ' , destination_subinventory '
|| ' , labor_txn_source_id '
|| ' , operation_plan_id '
|| ' , group_id '
|| ' , task_type_id '
|| ' , task_method_id '
|| ' , expected_travel_time '
|| ' , expected_txn_time '
|| ' , expected_idle_time '
|| ' , actual_travel_time '
|| ' , actual_txn_time '
|| ' , actual_idle_time '
|| ' , last_updated_by '
|| ' , last_update_date '
|| ' , last_update_login '
|| ' , created_by '
|| ' , creation_date '
|| ' ) '
|| ' SELECT '
|| ' wms_els_individual_tasks_s.NEXTVAL ' -- els_data_id
|| ' , organization_id ' --organization_id
|| ' , nvl ( ' || l_seq_num_ind_and_sys_directed || ',0)+10*ROWNUM ' --sequence_number
|| ' , ' || p_analysis_type --analysis_id
|| ' , activity_id ' --activity_id
|| ' , activity_detail_id ' --activity_detail_id
|| ' , operation_id ' --operation_id
|| ' , equipment_id ' --equipment_id
|| ' , source_zone_id ' --source_zone_id
|| ' , source_subinventory '--source_subinventory
|| ' , destination_zone_id ' --destination_zone_id
|| ' , destination_subinventory ' --destination_subinventory
|| ' , labor_txn_source_id ' --labor_txn_source_id
|| ' , operation_plan_id ' --operation_plan_id
|| ' , group_id ' --group_id
|| ' , task_type_id ' --task_type_id
|| ' , task_method_id ' --task_method_id
|| ' , travel_and_idle_time ' --expected_travel_time
|| ' , transaction_time ' --expected_txn_time
|| ' , NULL' --expected_idle_time
|| ' , travel_and_idle_time ' --actual_travel_time
|| ' , transaction_time ' --actual_txn_time
|| ' , idle_time ' --actual_idle_time
|| ' , FND_GLOBAL.USER_ID '
|| ' , sysdate '
|| ' , FND_GLOBAL.LOGIN_ID '
|| ' , FND_GLOBAL.USER_ID '
|| ' , sysdate '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' organization_id ' --organization_id
|| ' , activity_id ' --activity_id
|| ' , activity_detail_id ' --activity_detail_id
|| ' , operation_id ' --operation_id
|| ' , equipment_id ' --equipment_id
|| ' , source_zone_id ' --source_zone_id
|| ' , source_subinventory '--source_subinventory
|| ' , destination_zone_id ' --destination_zone_id
|| ' , destination_subinventory ' --destination_subinventory
|| ' , labor_txn_source_id ' --labor_txn_source_id
|| ' , operation_plan_id ' --operation_plan_id
|| ' , group_id ' --group_id
|| ' , task_type_id ' --task_type_id
|| ' , task_method_id ' --task_method_id
|| ' , ROUND(AVG(travel_and_idle_time),3) travel_and_idle_time ' --expected_travel_time
|| ' , ROUND(AVG(transaction_time),3) transaction_time' --expected_txn_time
|| ' , ROUND(AVG(idle_time),3) idle_time '
|| ' FROM WMS_ELS_TRX_SRC '
|| ' WHERE UNATTRIBUTED_FLAG = 1 '
|| ' AND organization_id = ' || p_org_id
|| ' AND group_id = 3 '
|| l_where_clause
|| ' GROUP BY '
|| ' organization_id,activity_id,activity_detail_id,operation_id '
|| ' ,equipment_id,source_zone_id,source_subinventory,destination_zone_id '
|| ' ,destination_subinventory,labor_txn_source_id,operation_plan_id '
|| ' ,group_id,task_type_id,task_method_id '
|| ' )';
l_num_lines_inserted := SQL%ROWCOUNT;
debug('Number of lines inserted for individual and system directed tasks'|| l_num_lines_inserted,'STANDARDIZE_LINES_CP');
INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_TL
(
els_data_id,
language,
source_lang,
description,
last_updated_by,
last_update_login,
created_by,
creation_date,
last_update_date
)
SELECT b.els_data_id
, l.language_code
, userenv('lang')
, null
, b.last_updated_by
, b.last_update_login
, b.created_by
, b.creation_date
, b.last_update_date
FROM WMS_ELS_INDIVIDUAL_TASKS_B b,
FND_LANGUAGES L
WHERE els_data_id NOT IN (
SELECT DISTINCT els_data_id
FROM wms_els_individual_tasks_tl
)
AND L.INSTALLED_FLAG in ('I', 'B');
debug('Insertion into TL table failed so rolling back everything'||SQLERRM,'STANDARDIZE_LINES_CP');
l_num_lines_inserted :=0;--reinitialize
SELECT substr(l_which_group_failed,0,length(l_which_group_failed)-1)
INTO l_which_group_failed
FROM dual;
'INSERT INTO WMS_ELS_GROUPED_TASKS_B' ||
'(' ||
' Els_Group_Id' ||
' , Organization_id' ||
' , Sequence_Number' ||
' , activity_id' ||
' , activity_detail_id' ||
' , operation_id' ||
' , labor_txn_source_id' ||
' , source_zone_id' ||
' , source_subinventory' ||
' , destination_zone_id' ||
' , destination_subinventory' ||
' , task_method_id' ||
' , task_range_from' ||
' , task_range_to' ||
' , expected_travel_time' ||
' , actual_travel_time' ||
' , last_updated_by' ||
' , last_update_date' ||
' , last_update_login' ||
' , created_by' ||
' , creation_date' ||
')' ||
'SELECT wms_els_grouped_tasks_s.NEXTVAL' ||
' , organization_id' ||
' , (nvl('||l_seq_num_grouped||',0) + (10*ROWNUM)) sequence_number' ||
' , activity_id' ||
' , activity_detail_id' ||
' , operation_id' ||
' , labor_txn_source_id ' ||
' , source_zone_id' ||
' , source_subinventory' ||
' , destination_zone_id' ||
' , destination_subinventory' ||
' , task_method_id' ||
' , task_range_from' ||
' , task_range_to' ||
' , exp_travel_time' ||
' , act_travel_Time' ||
' , FND_GLOBAL.USER_ID last_updated_by ' ||
' , sysdate last_update_date' ||
' , FND_GLOBAL.LOGIN_ID last_update_login' ||
' , FND_GLOBAL.USER_ID created_by' ||
' , sysdate creation_date' ||
' FROM ' ||
'( select organization_id' ||
' , activity_id' ||
' , activity_detail_id' ||
' , operation_id' ||
' , source_zone_id' ||
' , source_subinventory' ||
' , destination_zone_id' ||
' , destination_subinventory' ||
' , labor_txn_source_id' ||
' , task_method_id' ||
' , count(*) task_range_from' ||
' , count(*) task_range_to' ||
' , sum(travel_and_idle_time) exp_travel_time' ||
' , sum(travel_and_idle_time) act_travel_Time' ||
' FROM wms_els_trx_src' ||
' where els_data_id is null' ||
' and organization_id = '|| p_org_id ||
' and unattributed_flag = 1 '||
' and grouped_Task_identifier IS NOT NULL ' ||
l_where_clause || l_not_in_clause ||
' group by grouped_Task_identifier' ||
' , organization_id' ||
' , activity_id' ||
' , activity_detail_id' ||
' , operation_id' ||
' , source_zone_id' ||
' , source_subinventory' ||
' , destination_zone_id' ||
' , destination_subinventory' ||
' , labor_txn_source_id' ||
' , task_method_id' ||
' )';
l_num_lines_inserted := SQL%ROWCOUNT;
debug('Number of lines inserted for grouped and system directed tasks'|| l_num_lines_inserted,'STANDARDIZE_LINES_CP');
INSERT INTO WMS_ELS_GROUPED_TASKS_TL
(
els_group_id,
language,
source_lang,
description,
last_updated_by,
last_update_login,
created_by,
creation_date,
last_update_date
)
SELECT b.els_group_id,
l.language_code,
userenv('lang'), --Have to change this line. Need to give a Source Language.
null,
b.last_updated_by,
b.last_update_login,
b.created_by,
b.creation_date,
b.last_update_date
FROM WMS_ELS_GROUPED_TASKS_B b,
FND_LANGUAGES L
WHERE els_group_id NOT IN (
SELECT DISTINCT els_group_id
FROM wms_els_grouped_tasks_tl
)
AND L.INSTALLED_FLAG in ('I', 'B');
debug('Insertion to TL table failed with '||SQLERRM,'STANDARDIZE_LINES_CP');
'UPDATE wms_els_trx_src ' ||
'SET unattributed_flag = null ' ||
'WHERE organization_id = ' || p_org_id ||l_where_clause || l_not_in_clause ;
PROCEDURE INSERT_ELS_TRX
(
P_ACTIVITY_ID NUMBER,
P_ACTIVITY_DETAIL_ID NUMBER,
P_OPERATION_ID NUMBER,
P_ORGANIZATION_ID NUMBER,
P_USER_ID NUMBER,
P_EQUIPMENT_ID NUMBER,
P_SOURCE_SUBINVENTORY VARCHAR2,
P_DESTINATION_SUBINVENTORY VARCHAR2,
P_FROM_LOCATOR_ID NUMBER,
P_TO_LOCATOR_ID NUMBER,
P_LABOR_TXN_SOURCE_ID NUMBER,
P_TRANSACTION_UOM VARCHAR2,
P_QUANTITY NUMBER,
P_INVENTORY_ITEM_ID NUMBER,
P_GROUP_ID NUMBER,
P_TASK_METHOD_ID NUMBER,
P_TASK_TYPE_ID NUMBER,
P_GROUPED_TASK_IDENTIFIER NUMBER,
P_GROUP_SIZE NUMBER,
P_TRANSACTION_TIME NUMBER,
P_TRAVEL_AND_IDLE_TIME NUMBER,
P_CREATED_BY NUMBER,
P_OPERATION_PLAN_ID NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2
) AS
PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO WMS_ELS_TRX_SRC
(
ELS_TRX_SRC_ID,
TRANSACTION_DATE,
ACTIVITY_ID,
ACTIVITY_DETAIL_ID,
OPERATION_ID,
ORGANIZATION_ID,
USER_ID,
EQUIPMENT_ID,
SOURCE_SUBINVENTORY,
DESTINATION_SUBINVENTORY,
FROM_LOCATOR_ID,
TO_LOCATOR_ID,
LABOR_TXN_SOURCE_ID,
TRANSACTION_UOM,
QUANTITY,
INVENTORY_ITEM_ID,
GROUP_ID,
TASK_METHOD_ID,
TASK_TYPE_ID,
GROUPED_TASK_IDENTIFIER,
GROUP_SIZE,
TRANSACTION_TIME,
TRAVEL_AND_IDLE_TIME,
CREATION_DATE,
CREATED_BY,
OPERATION_PLAN_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
values
(
WMS_ELS_TRX_SRC_S.NEXTVAL,
SYSDATE,
P_ACTIVITY_ID,
P_ACTIVITY_DETAIL_ID,
P_OPERATION_ID,
P_ORGANIZATION_ID,
P_USER_ID,
P_EQUIPMENT_ID,
P_SOURCE_SUBINVENTORY,
P_DESTINATION_SUBINVENTORY,
P_FROM_LOCATOR_ID,
P_TO_LOCATOR_ID,
P_LABOR_TXN_SOURCE_ID,
P_TRANSACTION_UOM,
P_QUANTITY,
P_INVENTORY_ITEM_ID,
P_GROUP_ID,
P_TASK_METHOD_ID,
P_TASK_TYPE_ID,
P_GROUPED_TASK_IDENTIFIER,
P_GROUP_SIZE,
P_TRANSACTION_TIME,
P_TRAVEL_AND_IDLE_TIME,
SYSDATE,
P_CREATED_BY,
P_OPERATION_PLAN_ID,
SYSDATE,
P_CREATED_BY
);
debug('Successfully inserted record in WMS_ELS_TRX_SRC table');
debug('Insertion in WMS_ELS_TRX_SRC failed'||SQLERRM);
END INSERT_ELS_TRX;