eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WMS_LMS_LABOR_PRODUCTIVITY SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 50


  SELECT els_data_id,
          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,
          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,
          travel_time_threshold,
          num_trx_matched
   FROM wms_els_individual_tasks_b
   WHERE organization_id = l_org_id
   AND history_flag IS NULL
   AND analysis_id IN( 2,3)
   ORDER BY sequence_number,group_id;

Line: 82


SELECT els_group_id,
       organization_id,
	   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,
       group_size,
       expected_travel_time
FROM wms_els_grouped_tasks_b
WHERE organization_id = l_org_id
ORDER BY sequence_number;

Line: 102


SELECT els_group_id
FROM wms_els_grouped_tasks_b
WHERE organization_id = l_org_id;

Line: 114


l_update_count NUMBER;

Line: 148


l_update_count               := 0;

Line: 173


select max(els_trx_src_id) into l_max_id from wms_els_trx_src
where organization_id = p_org_id;

Line: 245


                                 ||  '      IN (select inventory_location_id'
                                 ||  '      from WMS_ZONE_LOCATORS'
                                 ||  '      where zone_id= :source_zone_id AND organization_id = :org_id'
                                 || ' AND '
                                 ||  ' WMS_LMS_UTILS. ZONE_LABOR_MGMT_ENABLED(:org_id,:source_zone_id)=''Y'''
                                 ||     ')'
                                 ||  ' )) ';

Line: 267


                                 ||  ' IN (select inventory_location_id '
                                 ||  ' from WMS_ZONE_LOCATORS '
                                 ||  ' where zone_id= :destination_zone_id AND organization_id = :org_id'
                                 || ' AND '
                                 || ' WMS_LMS_UTILS. ZONE_LABOR_MGMT_ENABLED(:org_id,:destination_zone_id)=''Y'''
                                 ||     ')'
                                 ||  ')) ';

Line: 307


                                    ||  ' IN (select inventory_item_id'
                                    ||  ' from MTL_ITEM_CATEGORIES'
                                    ||  ' where category_id= :item_category_id AND organization_id =:org_id'
                                    ||     ')'
                                    ||  ')) ';

Line: 339


l_sql :=' UPDATE wms_els_trx_src '
||      ' SET '
||      ' els_data_id = :els_data_id'
||      ' , source_zone_id = :source_zone'
||      ' , destination_zone_id = :destination_zone'
||      ' , item_category_id = :item_category'
||      ' , match_group = 1'
||      ' , unattributed_flag = NULL'
||      ' , travel_time = (CASE when (travel_and_idle_time > NVL(:travel_time_threshold,0))'
||                           '  then NVL(:travel_time_threshold,travel_and_idle_time) '
||                           '  else travel_and_idle_time '
||                           '  end )'
||      ' ,  idle_time  =  (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
||                                      'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
||                                      ' else 0  '
||                                      ' end )'
||      ' ,  employee_rating_travel = (select labor_rating from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
||                                             ' where  WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID'
||                                             ' AND   WEP.ORGANIZATION_ID = :org_id '
||                                             ' AND   WERS.RATING_TYPE     = ''TRA'' '
||                                             ' AND  ( '
||                                             ' (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
||                                             '  then  NVL(:travel_time_threshold,travel_and_idle_time) '
||                                             '  else  travel_and_idle_time '
||                                             '  end ) '
||                                                   '/:expected_travel_time '
||                                             ' )*100 >= wers.Per_Expected_Time_From '
||                                             ' AND  ( '
||                                             ' (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
||                                             '  then  NVL(:travel_time_threshold,travel_and_idle_time) '
||                                             '  else  travel_and_idle_time '
||                                             '  end ) '
||                                                   '/:expected_travel_time '
||                                             ' )*100 < NVL(wers.Per_Expected_Time_To,100000) '
||                                    ' )'
||      ' , travel_score  =  (select per_score from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
||                                             ' where  WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID'
||                                             ' AND   WEP.ORGANIZATION_ID = :org_id '
||                                             ' AND   WERS.RATING_TYPE     = ''TRA'' '
||                                             ' AND  ( '
||                                             ' (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
||                                             '  then  NVL(:travel_time_threshold,travel_and_idle_time) '
||                                             '  else  travel_and_idle_time '
||                                             '  end ) '
||                                                   '/:expected_travel_time '
||                                             ' )*100 >= wers.Per_Expected_Time_From '
||                                             ' AND  ( '
||                                             ' (CASE when ( travel_and_idle_time > NVL(:travel_time_threshold,0)) '
||                                             '  then  NVL(:travel_time_threshold,travel_and_idle_time) '
||                                             '  else  travel_and_idle_time '
||                                             '  end ) '
||                                                   '/:expected_travel_time '
||                                             ' )*100 < NVL(wers.Per_Expected_Time_To,100000) '
||                                    ' )'
||      ' , employee_rating_txn = (  select labor_rating from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
||                                 ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID '
||                                 ' AND   WEP.ORGANIZATION_ID = :org_id '
||                                 ' AND   WERS.RATING_TYPE     = ''TXN'' '
||                                 ' AND   (transaction_time/:expected_trx_time)*100 >= wers.Per_Expected_Time_From'
||                                 ' AND   (transaction_time/:expected_trx_time)*100 < NVL( wers.Per_Expected_Time_To,100000) '
||                              ' ) '
||      ' , txn_score  =        (  select per_score from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP '
||                                 ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID '
||                                 ' AND   WEP.ORGANIZATION_ID = :org_id '
||                                 ' AND   WERS.RATING_TYPE     = ''TXN'' '
||                                 ' AND   (transaction_time/:expected_trx_time)*100 >= wers.Per_Expected_Time_From'
||                                 ' AND   (transaction_time/:expected_trx_time)*100 < NVL( wers.Per_Expected_Time_To,100000) '
||                              ' ) '
||      ' ,employee_rating_Idle = (  select labor_rating from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP  '
||                                 ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID  '
||                                 ' AND   WEP.ORGANIZATION_ID = :org_id '
||                                 ' AND   WERS.RATING_TYPE     = ''IDL'' '
||                                 ' AND   ((CASE   when  (travel_and_idle_time > NVL(:travel_time_threshold,0) )'
||                                                 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
||                                                 'else 0 '
||                                          'end )'
||                                 ' /:expected_idle_time '
||                                         ')*100 >= wers.Per_Expected_Time_From '
||                                 'AND   ((CASE   when  (travel_and_idle_time > NVL(:travel_time_threshold,0) )'
||                                                 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
||                                                 'else 0 '
||                                         'end )'
||                                 ' /:expected_idle_time '
||                                         ')*100 < NVL(wers.Per_Expected_Time_To,10000) '
||                               ' )'
||        ' ,idle_score  =       (  select per_score from WMS_ELS_RATINGS_SETUP WERS, WMS_ELS_PARAMETERS WEP  '
||                                 ' where WEP.ELS_PARAMETER_ID = WERS.ELS_PARAMETER_ID  '
||                                 ' AND   WEP.ORGANIZATION_ID = :org_id '
||                                 ' AND   WERS.RATING_TYPE     = ''IDL'' '
||                                 ' AND   ((CASE   when  (travel_and_idle_time > NVL(:travel_time_threshold,0) )'
||                                                 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
||                                                 'else 0 '
||                                          'end )'
||                                 ' /:expected_idle_time '
||                                         ')*100 >= wers.Per_Expected_Time_From '
||                                 'AND   ((CASE   when  (travel_and_idle_time > NVL(:travel_time_threshold,0) )'
||                                                 'then (travel_and_idle_time - NVL(:travel_time_threshold,travel_and_idle_time)) '
||                                                 'else 0 '
||                                         'end )'
||                                 ' /:expected_idle_time '
||                                         ')*100 < NVL(wers.Per_Expected_Time_To,100000) '
||                               ' )'  ;

Line: 582


l_update_count  := DBMS_SQL.EXECUTE(c);

Line: 585


 debug('SQL executed Number of rows updated '|| l_update_count,'MATCH_RATE_TRX_RECORDS');

Line: 588


l_total := l_update_count + NVL(l_els_data.num_trx_matched,0);

Line: 594


UPDATE wms_els_individual_tasks_b
SET
num_trx_matched = l_total
WHERE els_data_id = l_els_data.els_data_id;

Line: 629


INSERT INTO WMS_ELS_GROUPED_TASKS_GTMP
(
els_grouped_task_id,
organization_id,
activity_id,
activity_detail_id,
operation_id,
labor_txn_source_id,
source_zone_id,
source_subinventory,
destination_zone_id,
destination_subinventory,
num_transactions,
task_method,
group_size,
sum_travel_time
)
SELECT   WMS_ELS_GROUPED_TASKS_S.NEXTVAL,
         organization_id,
         activity_id,
         activity_detail_id,
         operation_id,
         labor_txn_source_id,
         source_zone_id,
         source_subinventory,
	     destination_zone_id,
	     destination_subinventory,
         num_tasks,
         task_method_id,
         group_size,
         total_travel_time
FROM
(
SELECT
       organization_id,
       activity_id,
       activity_detail_id,
       operation_id,
       labor_txn_source_id,
       source_zone_id,
       source_subinventory,
	   destination_zone_id,
	   destination_subinventory,
       count(*) num_tasks,
       task_method_id,
       group_size,
       SUM(travel_time) total_travel_time
FROM   wms_els_trx_src
WHERE  organization_id = p_org_id
AND    match_group = 1
AND    transaction_date IS NOT NULL
AND    grouped_task_identifier IS NOT NULL
GROUP BY grouped_task_identifier,organization_id,activity_id,
         activity_detail_id,operation_id,labor_txn_source_id,
         source_zone_id,source_subinventory,destination_zone_id,destination_subinventory,
		   task_method_id,group_size
);

Line: 711


l_update_count := 0;

Line: 776


l_sql:= ' UPDATE WMS_ELS_GROUPED_TASKS_GTMP '
||      ' SET els_group_id = :els_group_id '
||      ' where els_group_id IS NULL '
||        l_where_clause ;

Line: 854


l_update_count  := DBMS_SQL.EXECUTE(c);

Line: 857


 debug('SQL executed. Number of rows updated  '||  l_update_count ,'MATCH_RATE_TRX_RECORDS');

Line: 883


SELECT avg(sum_travel_time) into l_avg_travel_time from WMS_ELS_GROUPED_TASKS_GTMP
WHERE els_group_id = l_group_data_id.els_group_id;

Line: 889


UPDATE WMS_ELS_GROUPED_TASKS_B SET actual_travel_time = (NVL(actual_travel_time,0) + l_avg_travel_time)/2
where els_group_id = l_group_data_id.els_group_id;

Line: 898


l_update_count := NULL;

Line: 903


UPDATE wms_els_trx_src SET unattributed_flag = 1 , match_group = 2
WHERE  els_data_id IS NULL and els_trx_src_id <= l_max_id AND organization_id = p_org_id;

Line: 906


l_update_count := SQL%ROWCOUNT;

Line: 909


 debug('Number of rows updated as non-standardized '|| l_update_count,'MATCH_RATE_TRX_RECORDS');