APPS.WIP_WS_TIME_ENTRY SQL Statements

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

Line: 11

PROCEDURE record_insert(
 p_time_entry_id	                   in number,
 p_organization_id  	               in number,
 p_wip_entity_id     	               in number,
 p_operation_seq_num                 in number,
 p_resource_id                       in number,
 p_resource_seq_num    	             in number,
 p_instance_id  	                   in number,
 p_serial_number          	         in varchar2,
 p_last_update_date     	           in date,
 p_last_updated_by                   in number,
 p_creation_date                     in date,
 p_created_by                        in number,
 p_last_update_login                 in number,
 p_object_version_num                in number,
 p_time_entry_mode                   in number,
 p_cost_flag                         in varchar2,
 p_add_to_rtg                        in varchar2,
 p_status_type                       in number,
 p_start_date                        in date,
 p_end_date                          in date,
 p_projected_completion_date         in date,
 p_duration                          in number,
 p_uom_code                          in varchar2,
 p_employee_id                       in number,
 x_time_entry_id                     out NOCOPY number,
 x_return_status                     out NOCOPY varchar2)
IS
 l_time_entry_id number;

Line: 59

   l_process_status := 2;  --inserted

Line: 65

      select WIP_RESOURCE_ACTUAL_TIMES_S.nextval into l_time_entry_id from dual;

Line: 112

     insert into wip_resource_actual_times
     (time_entry_id,
      organization_id,
      wip_entity_id,
      operation_seq_num,
      resource_id,
      resource_seq_num,
      instance_id,
      serial_number,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login,
      object_version_number,
      time_entry_mode,
      cost_flag,
      add_to_rtg,
      status_type,
      start_date,
      end_date,
      projected_completion_date,
      duration,
      uom_code,
      employee_id,
      process_status)
     values
     (l_time_entry_id,
      p_organization_id,
      p_wip_entity_id,
      p_operation_seq_num,
      p_resource_id,
      p_resource_seq_num,
      p_instance_id,
      p_serial_number,
      fnd_global.user_id,
      sysdate,
      fnd_global.user_id,
      sysdate,
      fnd_global.login_id,
      l_object_version_num,
      l_time_entry_mode,
      p_cost_flag,
      p_add_to_rtg,
      l_status_type,
      p_start_date,
      p_end_date,
      l_projected_completion_date,
      l_duration,
      l_uom_code,
      p_employee_id,
      l_process_status);

Line: 165

     update_actual_start_dates(p_wip_entity_id      => p_wip_entity_id,
                               p_operation_seq_num  => p_operation_seq_num,
                               p_resource_seq_num   => p_resource_seq_num);

Line: 170

        update_proj_completion_dates(p_organization_id => p_organization_id,
                                     p_wip_entity_id => p_wip_entity_id,
                                     p_operation_seq_num => p_operation_seq_num,
                                     p_resource_seq_num => p_resource_seq_num,
                                     p_resource_id => p_resource_id,
                                     p_start_date => p_start_date);

Line: 182

     select organization_code
      into l_organization_code
       from mtl_parameters
      where organization_id = p_organization_id;

Line: 187

     select wip_entity_name
      into l_wip_entity_name
       from wip_entities
      where wip_entity_id = p_wip_entity_id;

Line: 192

     select resource_code, resource_type
     into l_resource_code, l_resource_type
     from bom_resources
     where resource_id = p_resource_id;

Line: 198

       select mec.employee_num
         into l_employee_num
         from mtl_employees_current_view mec
        where mec.employee_id = p_employee_id
          and mec.organization_id = p_organization_id;

Line: 209

     insert into wip_cost_txn_interface
     (created_by,
      created_by_name,
      creation_date,
      last_updated_by,
      last_updated_by_name,
      last_update_date,
      last_update_login,
      operation_seq_num,
      organization_code,
      organization_id,
      process_phase,
      process_status,
      resource_id,
      resource_code,
      resource_seq_num,
      source_code,
      transaction_date,
      transaction_quantity,
      transaction_type,
      transaction_uom,
      entity_type,
      wip_entity_id,
      wip_entity_name,
      employee_id,
      employee_num)
     values
     (fnd_global.user_id,
      fnd_global.user_name,
      sysdate,
      fnd_global.user_id,
      fnd_global.user_name,
      sysdate,
      fnd_global.login_id,
      p_operation_seq_num,
      l_organization_code,
      p_organization_id,
      WIP_CONSTANTS.RES_VAL,
      WIP_CONSTANTS.PENDING,
      p_resource_id,
      l_resource_code,
      p_resource_seq_num,
      WIP_CONSTANTS.SOURCE_CODE,
      sysdate,
      p_duration,  --non time based resource implies no start/end time which means duration is mandatory
      WIP_CONSTANTS.RES_TXN,
      p_uom_code,
      WIP_CONSTANTS.DISCRETE,
      p_wip_entity_id,
      l_wip_entity_name,
      l_employee_id,
      l_employee_num);

Line: 266

END record_insert;

Line: 269

PROCEDURE record_update(
 p_time_entry_id	                   in number,
 p_organization_id  	               in number,
 p_wip_entity_id     	               in number,
 p_operation_seq_num    	           in number,
 p_resource_id                       in number,
 p_resource_seq_num    	             in number,
 p_instance_id  	                   in number,
 p_serial_number          	         in varchar2,
 p_last_update_date     	           in date,
 p_last_updated_by                   in number,
 p_creation_date                     in date,
 p_created_by                        in number,
 p_last_update_login                 in number,
 p_object_version_num                in number,
 p_time_entry_mode                   in number,
 p_cost_flag                         in varchar2,
 p_add_to_rtg                        in varchar2,
 p_status_type                       in number,
 p_start_date                        in date,
 p_end_date                          in date,
 p_projected_completion_date         in date,
 p_duration                          in number,
 p_uom_code                          in varchar2,
 p_employee_id                       in number,
 x_return_status                     out NOCOPY varchar2)
IS
 l_process_status number;

Line: 307

   l_process_status := 3;  --updated

Line: 330

     select object_version_number, start_date into l_object_version_num, l_start_date
     from wip_resource_actual_times where time_entry_id = p_time_entry_id;

Line: 346

       update wip_resource_actual_times set
       organization_id = p_organization_id,
       wip_entity_id = p_wip_entity_id,
       operation_seq_num = p_operation_seq_num,
       resource_id = p_resource_id,
       resource_seq_num = p_resource_seq_num,
       instance_id = p_instance_id,
       serial_number = p_serial_number,
       creation_date = p_creation_date,
       created_by = p_created_by,
       time_entry_mode = p_time_entry_mode,
       cost_flag = p_cost_flag,
       add_to_rtg = p_add_to_rtg,
       status_type = p_status_type,
       start_date = p_start_date,
       end_date = p_end_date,
       projected_completion_date = l_projected_completion_date,
       duration = l_duration,
       uom_code = l_uom_code,
       employee_id = p_employee_id,
       process_status = l_process_status,
       object_version_number = p_object_version_num + 1,
       last_update_date = sysdate,
       last_updated_by = fnd_global.user_id,
       last_update_login = fnd_global.login_id
       where time_entry_id = p_time_entry_id;

Line: 374

         update_actual_start_dates(p_wip_entity_id      => p_wip_entity_id,
                                   p_operation_seq_num  => p_operation_seq_num,
                                   p_resource_seq_num   => p_resource_seq_num);

Line: 378

         update_proj_completion_dates(p_organization_id => p_organization_id,
                                      p_wip_entity_id => p_wip_entity_id,
                                      p_operation_seq_num => p_operation_seq_num,
                                      p_resource_seq_num => p_resource_seq_num,
                                      p_resource_id => p_resource_id,
                                      p_start_date => p_start_date);

Line: 391

     l_return_status := 'U';  --error condition: non time based resources are never updated

Line: 395

END record_update;

Line: 398

PROCEDURE record_delete(
 p_time_entry_id	                   in number,
 p_object_version_num                      in number,
 x_return_status                     out NOCOPY varchar2)
IS
 l_process_status number;

Line: 413

 cursor delete_cursor is select object_version_number,
                                organization_id,
                                wip_entity_id,
                                operation_seq_num,
                                resource_id,
                                resource_seq_num,
                                start_date
 from wip_resource_actual_times
 where time_entry_id = p_time_entry_id;

Line: 423

   l_process_status := 4;  --deleted

Line: 426

   open delete_cursor;

Line: 427

   fetch delete_cursor into l_object_version_num,
                            l_organization_id,
                            l_wip_entity_id,
                            l_operation_seq_num,
                            l_resource_id,
                            l_resource_seq_num,
                            l_start_date;

Line: 434

   if delete_cursor%NOTFOUND then
     l_return_status := 'U';

Line: 438

        update wip_resource_actual_times set
        process_status = l_process_status,
        object_version_number = p_object_version_num + 1,
        last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id
        where time_entry_id = p_time_entry_id
           and process_status <> 4;

Line: 447

        update_actual_start_dates(p_wip_entity_id      => l_wip_entity_id,
                                  p_operation_seq_num  => l_operation_seq_num,
                                  p_resource_seq_num   => l_resource_seq_num);

Line: 452

          update_proj_completion_dates(p_organization_id => l_organization_id,
                                       p_wip_entity_id => l_wip_entity_id,
                                       p_operation_seq_num => l_operation_seq_num,
                                       p_resource_seq_num => l_resource_seq_num,
                                       p_resource_id => l_resource_id,
                                       p_start_date => l_start_date);

Line: 463

   close delete_cursor;

Line: 465

END record_delete;

Line: 468

PROCEDURE record_delete(
 p_wip_entity_id	                   in number,
 p_operation_seq_num                 in number,
 p_employee_id                       in number,
 x_return_status                     out NOCOPY varchar2)
IS
 l_process_status number;

Line: 482

 cursor delete_cursor is select object_version_number,
                                organization_id,
                                resource_id,
                                resource_seq_num,
                                start_date
 from wip_resource_actual_times
 where wip_entity_id = p_wip_entity_id
       and operation_seq_num = p_operation_seq_num
       and employee_id = p_employee_id
       and process_status <> 4;

Line: 493

   l_process_status := 4;  --deleted

Line: 496

   open delete_cursor;

Line: 497

   fetch delete_cursor into l_object_version_num,
                            l_organization_id,
                            l_resource_id,
                            l_resource_seq_num,
                            l_start_date;

Line: 502

   if delete_cursor%NOTFOUND then
     l_return_status := 'U';

Line: 505

     update wip_resource_actual_times set
     process_status = l_process_status,
     object_version_number = l_object_version_num + 1,
     last_update_date = sysdate,
     last_updated_by = fnd_global.user_id,
     last_update_login = fnd_global.login_id
     where wip_entity_id = p_wip_entity_id
           and operation_seq_num = p_operation_seq_num
           and employee_id = p_employee_id
           and process_status <> 4;

Line: 516

     update_actual_start_dates(p_wip_entity_id      => p_wip_entity_id,
                               p_operation_seq_num  => p_operation_seq_num,
                               p_resource_seq_num   => l_resource_seq_num);

Line: 521

       update_proj_completion_dates(p_organization_id => l_organization_id,
                                    p_wip_entity_id => p_wip_entity_id,
                                    p_operation_seq_num => p_operation_seq_num,
                                    p_resource_seq_num => l_resource_seq_num,
                                    p_resource_id => l_resource_id,
                                    p_start_date => l_start_date);

Line: 531

   close delete_cursor;

Line: 533

END record_delete;

Line: 545

  update wip_operations
  set employee_id = null
  where wip_entity_id = p_wip_entity_id
    and operation_seq_num = p_operation_seq_num
    and employee_id is not null;

Line: 561

   update wip_resource_actual_times t
   set
      end_date = l_date,
      duration = (l_date - start_date)*24,
      uom_code = l_uom_code,
      process_status = '3',
      object_version_number = object_version_number + 1,
      last_update_date = l_date,
      last_updated_by = fnd_global.user_id,
      last_update_login = fnd_global.login_id
    where wip_entity_id = p_wip_entity_id
      and operation_seq_num = p_operation_seq_num
      and process_status <> 4
      and status_type = 1
      and start_date is not null
      and end_date is null
      and resource_id in (select resource_id from bom_resources where resource_type = 2)
      and (exclude_scheduled_flag is null or
           exclude_scheduled_flag <>
           nvl((select scheduled_flag from wip_operation_resources wor
            where wor.wip_entity_id = t.wip_entity_id
              and wor.operation_seq_num = t.operation_seq_num
              and wor.resource_seq_num = t.resource_seq_num), 2) /* ad-hoc has no scheduled flag*/
          );

Line: 592

 select
 decode(
 ( select count(*)
     from wip_resource_actual_times wrat,
          bom_resources br,
          wip_operation_resources wor
    where wrat.wip_entity_id = p_wip_entity_id
      and wrat.operation_seq_num = p_operation_seq_num
      and wrat.resource_id = br.resource_id
      and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
      and wrat.operation_seq_num = wor.operation_seq_num (+)
      and wrat.resource_id = wor.resource_id (+)
      and wrat.process_status <> 4
      and wrat.status_type = 1
      and br.resource_type = 2  /* labor */
      and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) = p_scheduled_flag
      and wrat.start_date is not null
     and wrat.end_date is null
  ), 0, 1, 0)
  from dual;

Line: 615

 select max(wrat.end_date)
 from wip_resource_actual_times wrat,
      bom_resources br,
      wip_operation_resources wor
 where wrat.wip_entity_id = p_wip_entity_id
   and wrat.operation_seq_num = p_operation_seq_num
   and wrat.resource_id = br.resource_id
   and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
   and wrat.operation_seq_num = wor.operation_seq_num (+)
   and wrat.resource_id = wor.resource_id (+)
   and wrat.process_status <> 4
   and br.resource_type = 2  /* labor */
   and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) = p_scheduled_flag
   and wrat.end_date is not null;

Line: 652

    update wip_resource_actual_times t
    set
      end_date = l_date,
      duration = (l_date - start_date)*24,
      uom_code = l_uom_code,
      process_status = '3',
      object_version_number = object_version_number + 1,
      last_update_date = sysdate,
      last_updated_by = fnd_global.user_id,
      last_update_login = fnd_global.login_id
    where wip_entity_id = p_wip_entity_id
      and operation_seq_num = p_operation_seq_num
      and status_type = 1
      and start_date is not null
      and end_date is null
      and resource_id in (select resource_id from bom_resources where resource_type = 1)
      and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
            from wip_operation_resources wor
           where wor.wip_entity_id = t.wip_entity_id
             and wor.operation_seq_num = t.operation_seq_num
             and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;

Line: 690

    update wip_resource_actual_times t
    set
      end_date = l_date,
      duration = (l_date - start_date)*24,
      process_status = '3',
      object_version_number = object_version_number + 1,
      last_update_date = sysdate,
      last_updated_by = fnd_global.user_id,
      last_update_login = fnd_global.login_id
    where wip_entity_id = p_wip_entity_id
      and operation_seq_num = p_operation_seq_num
      and status_type = 1
      and start_date is not null
      and end_date is null
      and resource_id in (select resource_id from bom_resources where resource_type = 1)
      and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
            from wip_operation_resources wor
           where wor.wip_entity_id = t.wip_entity_id
             and wor.operation_seq_num = t.operation_seq_num
             and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;

Line: 726

    update wip_resource_actual_times t
    set
      end_date = l_date,
      duration = (l_date - start_date)*24,
      process_status = '3',
      object_version_number = object_version_number + 1,
      last_update_date = sysdate,
      last_updated_by = fnd_global.user_id,
      last_update_login = fnd_global.login_id
    where wip_entity_id = p_wip_entity_id
      and operation_seq_num = p_operation_seq_num
      and status_type = 1
      and start_date is not null
      and end_date is null
      and resource_id in (select resource_id from bom_resources where resource_type = 1)
      and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
            from wip_operation_resources wor
           where wor.wip_entity_id = t.wip_entity_id
             and wor.operation_seq_num = t.operation_seq_num
             and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;

Line: 755

    select resource_seq_num
      from wip_resource_actual_times
     where wip_entity_id = p_wip_entity_id
       and operation_seq_num = p_operation_seq_num
       and resource_id = p_resource_id
       and resource_seq_num is not null
       and rownum=1;

Line: 776

    select max(wor.resource_seq_num) seq
      into l_wor_max
      from wip_operation_resources wor
     where wor.wip_entity_id = p_wip_entity_id
       and wor.operation_seq_num = p_operation_seq_num;

Line: 782

    select max(wrat.resource_seq_num) seq
      into l_wrat_max
      from wip_resource_actual_times wrat
     where wrat.wip_entity_id = p_wip_entity_id
       and wrat.operation_seq_num = p_operation_seq_num
       and wrat.resource_seq_num is not null;

Line: 789

    select greatest(nvl(l_wor_max,0), nvl(l_wrat_max,0)) + 10
      into l_resource_seq_num
      from dual;

Line: 799

/* update the operation/resources's actual completion date based on wrat */
procedure update_actual_comp_dates(p_wip_entity_id IN NUMBER,
                                   p_operation_seq_num IN NUMBER)
IS
Begin

  /* update wor's actual completion date */
  update wip_operation_resources wor
  set
    wor.actual_completion_date =
    nvl( ( select max(wrat.end_date)
       from wip_resource_actual_times wrat
       where wrat.wip_entity_id = wor.wip_entity_id
         and wrat.operation_seq_num = wor.operation_seq_num
         and wrat.resource_seq_num = wor.resource_seq_num
         and wrat.process_status <> 4
         and wrat.end_date is not null), sysdate)
  where wip_entity_id = p_wip_entity_id
    and operation_seq_num = p_operation_seq_num
    and not exists (select wrat.end_date
                      from wip_resource_actual_times wrat
                     where wrat.wip_entity_id = wor.wip_entity_id
                       and wrat.operation_seq_num = wor.operation_seq_num
                       and wrat.resource_seq_num = wor.resource_seq_num
                       and wrat.start_date is not null
                       and wrat.end_date is null
                       and wrat.process_status <> 4);

Line: 827

  update wip_operations wo
  set
    wo.actual_completion_date =
    nvl(greatest( ( select max(wor.actual_completion_date)
                      from wip_operation_resources wor
                     where wor.wip_entity_id = wo.wip_entity_id
                       and wor.operation_seq_num = wo.operation_seq_num
                       and wor.actual_completion_date is not null)
                , ( select max(wrat.end_date)
                      from wip_resource_actual_times wrat
                     where wrat.wip_entity_id = wo.wip_entity_id
                       and wrat.operation_seq_num = wo.operation_seq_num
                       and wrat.end_date is not null)
                ), sysdate)
  where wip_entity_id = p_wip_entity_id
    and operation_seq_num = p_operation_seq_num
    and not exists (select 1
                      from wip_resource_actual_times wrat
                     where wrat.wip_entity_id = wo.wip_entity_id
                       and wrat.operation_seq_num = wo.operation_seq_num
                       and wrat.start_date is not null
                       and wrat.end_date is null
                       and wrat.process_status <> 4);

Line: 851

End update_actual_comp_dates;

Line: 860

 select distinct wrat.wip_entity_id,
                 wrat.operation_seq_num
 from wip_resource_actual_times wrat
 where wrat.organization_id = p_organization_id
 and wrat.status_type = 1
 and wrat.process_status = 2 /* only new records */
 and wrat.duration is not null;

Line: 888

 select distinct wrat.wip_entity_id,
                 wrat.operation_seq_num
 from wip_resource_actual_times wrat
 where wrat.organization_id = p_organization_id
 and wrat.instance_id = p_instance_id
 and wrat.status_type = 1
 and wrat.process_status in (2, 3, 4)
 and (wrat.time_entry_mode in (3,4) or wrat.process_status = 2)
 and wrat.duration is not null;

Line: 918

 select operation_seq_num
 from wip_operations
 where wip_entity_id = p_wip_entity_id
       and operation_seq_num >= p_from_op
       and operation_seq_num <= p_to_op;

Line: 992

 select wrat.time_entry_id,
        wrat.object_version_number,
        wrat.add_to_rtg,
        wrat.cost_flag,
        wrat.status_type,
        wrat.process_status,
        wrat.resource_seq_num,
        wrat.organization_id,
        wrat.resource_id,
        wrat.duration,
        wrat.uom_code,
        wrat.end_date,
        wrat.time_entry_mode
 from wip_resource_actual_times wrat,
      bom_resources br,
      wip_operation_resources wor
 where wrat.wip_entity_id = p_wip_entity_id
 and wrat.operation_seq_num = p_completed_op
 and wrat.resource_id = br.resource_id
 and wrat.wip_entity_id = wor.wip_entity_id (+)
 and wrat.operation_seq_num = wor.operation_seq_num (+)
 and wrat.resource_id = wor.resource_id (+)
 and wrat.status_type = 1
 and wrat.process_status <> 4
 and ( wrat.process_status in (2, 3) or
       ( wrat.time_entry_mode in (c_time_entry_mode1,
                                   c_time_entry_mode2, c_time_entry_mode3)
         and (p_instance_id is null or wrat.instance_id = p_instance_id)
     ) )
 ; -- and wrat.duration is not null;

Line: 1026

 select count(*)
   from wip_resource_actual_times wrat,
        wip_operation_resources wor
  where wrat.wip_entity_id = p_wip_entity_id
    and wrat.operation_seq_num = p_completed_op
    and wrat.status_type = 1
    and wrat.process_status <> 4
    and wrat.start_date is not null
    and wrat.end_date is null
    and wrat.wip_entity_id = wor.wip_entity_id
    and wrat.operation_seq_num = wor.operation_seq_num
    and wrat.resource_id = wor.resource_id;

Line: 1097

    /* add the cost_flag and add_to_rtg and resource seq for the inserted rows */
    if ( l_process_status = 2 ) then
      l_change_flag := 'N';

Line: 1125

        update wip_resource_actual_times
        set
          cost_flag = l_cost_flag,
          add_to_rtg = l_add_to_rtg,
          resource_seq_num = l_resource_seq_num,
          object_version_number = l_object_version_num + 1,
          last_update_date = sysdate,
          last_updated_by = fnd_global.user_id,
          last_update_login = fnd_global.login_id
        where time_entry_id = l_entry_id;

Line: 1144

        select organization_code
        into l_organization_code
        from mtl_parameters
        where organization_id = l_org_id;

Line: 1149

        select wip_entity_name
        into l_wip_entity_name
        from wip_entities
        where wip_entity_id = p_wip_entity_id;

Line: 1154

        select resource_code, resource_type
        into l_resource_code, l_resource_type
        from bom_resources
        where resource_id = l_resource_id;

Line: 1165

													select wrat.employee_id, mec.employee_num
													into l_employee_id, l_employee_num
													from wip_resource_actual_times wrat, mtl_employees_current_view mec
													where wrat.time_entry_id = l_entry_id
													and wrat.employee_id = mec.employee_id
													and wrat.organization_id = mec.organization_id;

Line: 1182

        insert into wip_cost_txn_interface(
        created_by,
        created_by_name,
        creation_date,
        last_updated_by,
        last_updated_by_name,
        last_update_date,
        last_update_login,
        operation_seq_num,
        organization_code,
        organization_id,
        process_phase,
        process_status,
        resource_id,
        resource_code,
        resource_seq_num,
        source_code,
        transaction_date,
        transaction_quantity,
        transaction_type,
        transaction_uom,
        entity_type,
        wip_entity_id,
        wip_entity_name,
        employee_id,
        employee_num)
        values(
        fnd_global.user_id,
        fnd_global.user_name,
        sysdate,
        fnd_global.user_id,
        fnd_global.user_name,
        sysdate,
        fnd_global.login_id,
        p_completed_op,
        l_organization_code,
        l_org_id,
        WIP_CONSTANTS.RES_VAL,
        WIP_CONSTANTS.PENDING,
        l_resource_id,
        l_resource_code,
        l_resource_seq_num,
        WIP_CONSTANTS.SOURCE_CODE,
        sysdate,
        l_duration,
        WIP_CONSTANTS.RES_TXN,
        l_uom_code,
        WIP_CONSTANTS.DISCRETE,
        p_wip_entity_id,
        l_wip_entity_name,
        l_employee_id,
        l_employee_num);

Line: 1239

      update wip_resource_actual_times
      set
        status_type = l_completed_status_type,
        process_status = l_completed_process_status,
        object_version_number = l_object_version_num + 1,
        last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id
      where time_entry_id = l_entry_id;

Line: 1258

      update_actual_comp_dates(p_wip_entity_id, p_completed_op);

Line: 1261

  /* for wrat records with process status deleted - delete records */
  delete from wip_resource_actual_times
  where wip_entity_id = p_wip_entity_id
        and operation_seq_num = p_completed_op
        and status_type = 1
        and process_status = 4;

Line: 1268

  /* reset process_status of records of inserted/updated - mark as completed */
  update wip_resource_actual_times set
  process_status = l_completed_process_status,
  last_update_date = sysdate,
  last_updated_by = fnd_global.user_id,
  last_update_login = fnd_global.login_id
  where wip_entity_id = p_wip_entity_id
        and operation_seq_num = p_completed_op
        and status_type = 1
        and process_status in (2,3);

Line: 1287

 select distinct muc.uom_class
 from mtl_uom_conversions  muc,
      mtl_uom_conversions  muc2
 where (muc.uom_class = muc2.uom_class and
       nvl(muc.disable_date, sysdate + 1) > sysdate) and
       nvl(muc2.disable_date, sysdate + 1) > sysdate and
       muc.uom_code = fnd_profile.value('BOM:HOUR_UOM_CODE') and
       muc2.uom_code = p_uom_code;

Line: 1342

 select 1
   from wip_operation_resources wor
  where wor.wip_entity_id = p_wip_entity_id
    and wor.operation_seq_num = p_operation_seq_num
    and wor.autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL
    and rownum = 1;

Line: 1349

 cursor autocharge_type_res_cursor is select wor.autocharge_type
 from wip_operation_resources wor
 where wor.wip_entity_id = p_wip_entity_id
 and wor.operation_seq_num = p_operation_seq_num
 and wor.resource_seq_num = p_resource_seq_num;

Line: 1404

  Since the insertion of a wor is done through cost txn, so this is simplified */
FUNCTION get_add_to_rtg_flag(p_wip_entity_id IN NUMBER,
                             p_operation_seq_num IN NUMBER,
                             p_resource_seq_num IN NUMBER,
                             p_cost_flag IN VARCHAR2,
                             p_time_entry_source IN VARCHAR2) return VARCHAR2
IS
 l_add_to_rtg_flag varchar2(1);

Line: 1441

 cursor org_dept_cursor is select wo.organization_id, wo.department_id
 from wip_operations wo
 where wo.wip_entity_id = p_wip_entity_id
 and wo.operation_seq_num = p_operation_seq_num;

Line: 1458

PROCEDURE update_actual_start_dates(p_wip_entity_id IN NUMBER,
                                    p_operation_seq_num IN NUMBER,
                                    p_resource_seq_num IN NUMBER)
IS
 l_min_start_date date;

Line: 1466

 cursor min_start_date_wrat_cursor is select min(wrat.start_date)
 from wip_resource_actual_times wrat
 where wrat.wip_entity_id = p_wip_entity_id
 and wrat.operation_seq_num = p_operation_seq_num
 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
 and wrat.start_date is not null
 and wrat.process_status <> 4;

Line: 1474

 cursor min_start_date_wor_cursor is select min(wor.actual_start_date)
 from wip_operation_resources wor
 where wor.wip_entity_id = p_wip_entity_id
 and wor.operation_seq_num = p_operation_seq_num
 and wor.actual_start_date is not null;

Line: 1480

 cursor min_start_date_wo_cursor is select min(wo.actual_start_date)
 from wip_operations wo
 where wo.wip_entity_id = p_wip_entity_id
 and wo.actual_start_date is not null;

Line: 1491

      update wip_operation_resources set
      actual_start_date = l_min_start_date
      where wip_entity_id = p_wip_entity_id
            and operation_seq_num = p_operation_seq_num
            and resource_seq_num = p_resource_seq_num;

Line: 1507

    select decode(l_min_start_date, null, l_min_start_date_wrat,
       least(l_min_start_date_wrat, l_min_start_date) )
    into l_min_start_date
    from dual;

Line: 1514

      update wip_operations set
      actual_start_date = l_min_start_date
      where wip_entity_id = p_wip_entity_id
            and operation_seq_num = p_operation_seq_num;

Line: 1525

      update wip_discrete_jobs set
      actual_start_date = l_min_start_date
      where wip_entity_id = p_wip_entity_id;

Line: 1531

END update_actual_start_dates;

Line: 1534

PROCEDURE update_actual_completion_dates(p_wip_entity_id IN NUMBER,
                                         p_operation_seq_num IN NUMBER,
                                         p_resource_seq_num IN NUMBER)
IS
 l_max_end_date date;

Line: 1541

 cursor max_end_date_wrat_cursor is select max(wrat.end_date)
 from wip_resource_actual_times wrat
 where wrat.wip_entity_id = p_wip_entity_id
 and wrat.operation_seq_num = p_operation_seq_num
 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
 and not exists (select wrat.end_date
                 from wip_resource_actual_times wrat
                 where wrat.wip_entity_id = p_wip_entity_id
                 and wrat.operation_seq_num = p_operation_seq_num
                 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
                 and wrat.end_date is null
                 and wrat.process_status <> 4)
 and wrat.process_status <> 4;

Line: 1556

 cursor active_next_resource_cursor is select 'Y'
 from wip_resource_actual_times wrat,
      wip_operation_resources wor
 where wrat.wip_entity_id = p_wip_entity_id
 and wrat.operation_seq_num = p_operation_seq_num
 and wrat.status_type = 1
 and wrat.process_status <> 4
 and wrat.end_date is null
 and wor.scheduled_flag = 4
 and wrat.wip_entity_id = wor.wip_entity_id
 and wrat.operation_seq_num = wor.operation_seq_num
 and wrat.resource_id = wor.resource_id;

Line: 1569

 cursor max_end_date_wor_cursor is select max(wor.actual_completion_date)
 from wip_operation_resources wor
 where wor.wip_entity_id = p_wip_entity_id
 and wor.operation_seq_num = p_operation_seq_num
 and not exists (select wor.actual_completion_date
                 from wip_operation_resources wor
                 where wor.wip_entity_id = p_wip_entity_id
                 and wor.operation_seq_num = p_operation_seq_num
                 and wor.actual_completion_date is null);

Line: 1583

      update wip_operation_resources set
      actual_completion_date = l_max_end_date
      where wip_entity_id = p_wip_entity_id
            and operation_seq_num = p_operation_seq_num
            and resource_seq_num = p_resource_seq_num;

Line: 1599

        update wip_operations set
        actual_completion_date = l_max_end_date
        where wip_entity_id = p_wip_entity_id
              and operation_seq_num = p_operation_seq_num;

Line: 1608

END update_actual_completion_dates;

Line: 1611

PROCEDURE update_proj_completion_dates(p_organization_id IN NUMBER,
                                       p_wip_entity_id IN NUMBER,
                                       p_operation_seq_num IN NUMBER,
                                       p_resource_seq_num IN NUMBER,
                                       p_resource_id IN NUMBER,
                                       p_start_date IN DATE)
IS
 l_projected_completion_date date;

Line: 1627

  update wip_operation_resources set
  projected_completion_date = l_projected_completion_date
  where wip_entity_id = p_wip_entity_id
        and operation_seq_num = p_operation_seq_num
        and resource_seq_num = p_resource_seq_num;

Line: 1640

  update wip_operations set
  projected_completion_date = l_projected_completion_date
  where wip_entity_id = p_wip_entity_id
        and operation_seq_num = p_operation_seq_num;

Line: 1644

END update_proj_completion_dates;

Line: 1653

 cursor job_status_cursor is select employee_id
 from wip_operations wo
 where wo.wip_entity_id = p_wip_entity_id
 and wo.operation_seq_num = p_operation_seq_num;

Line: 1693

    update wip_operations set
    employee_id = p_employee_id
    where wip_entity_id = p_wip_entity_id
          and operation_seq_num = p_operation_seq_num;

Line: 1762

       select  bre.person_id
       from   per_all_people_f papf,
       bom_resource_employees bre
       where  papf.person_id = bre.person_id
       and sysdate between papf.effective_start_date and nvl(papf.effective_end_date,sysdate+1)
       and bre.organization_id = p_org_id
       and papf.employee_number = p_wip_employee_id
       and rownum=1;

Line: 1807

    SELECT 1
    FROM wip_resource_actual_times wrat
    WHERE wrat.wip_entity_id IS NULL
     AND wrat.end_date IS NULL
     and wrat.employee_id = l_person_id
     and wrat.time_entry_mode = 8
     and organization_id = p_org_id;

Line: 1840

     insert into  wip_resource_actual_times
       (TIME_ENTRY_ID,ORGANIZATION_ID,WIP_ENTITY_ID,
        OPERATION_SEQ_NUM,RESOURCE_ID,RESOURCE_SEQ_NUM,
        INSTANCE_ID,SERIAL_NUMBER,TIME_ENTRY_MODE,
        COST_FLAG,ADD_TO_RTG,STATUS_TYPE,START_DATE,
        END_DATE,PROJECTED_COMPLETION_DATE,DURATION,
        UOM_CODE,EMPLOYEE_ID,PROCESS_STATUS,CREATED_BY,
        CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,
        ACTION_FLAG,REQUEST_ID,PROGRAM_ID,
        PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE)
     values
       (WIP_RESOURCE_ACTUAL_TIMES_S.nextval,p_org_id,
        null,null,null,null,null,null,8,'N','N',null,
        sysdate,null,null,null,
        fnd_profile.value('BOM:HOUR_UOM_CODE'),
        l_person_id,1,fnd_global.user_id,
        sysdate,fnd_global.user_id,sysdate,
        fnd_global.login_id,1,null,null,null,
        null,null);

Line: 1883

    select  start_date
    from wip_resource_actual_times wrat
    where wrat.wip_entity_id is null
    and wrat.EMPLOYEE_ID=l_person_id
    and wrat.ORGANIZATION_ID=p_org_id
    and wrat.end_date is null
    and wrat.time_entry_mode = 8;

Line: 1913

       update wip_resource_actual_times set
          end_date = l_date,
          duration = l_duration
          where ORGANIZATION_ID=p_org_id
                and employee_id =l_person_id
                and end_date is null
	        and wip_entity_id is null;

Line: 1941

        select  1
        from wip_resource_actual_times wrat
        where wrat.wip_entity_id is null
	and wrat.EMPLOYEE_ID=l_person_id
	and wrat.ORGANIZATION_ID=p_org_id
	 and wrat.end_date is null;

Line: 1965

       delete from wip_resource_actual_times wrat
       where
       wrat.wip_entity_id is null
       and wrat.EMPLOYEE_ID=l_person_id
       and wrat.ORGANIZATION_ID=p_org_id
       and wrat.end_date is null;

Line: 1989

    SELECT 1
    FROM wip_resource_actual_times wrat
    WHERE wrat.wip_entity_id IS NULL
     AND wrat.end_date IS NULL
     and wrat.employee_id = p_wip_employee_id
     and wrat.time_entry_mode = 8;

Line: 2015

     insert into  wip_resource_actual_times
       (TIME_ENTRY_ID,ORGANIZATION_ID,WIP_ENTITY_ID,
        OPERATION_SEQ_NUM,RESOURCE_ID,RESOURCE_SEQ_NUM,
        INSTANCE_ID,SERIAL_NUMBER,TIME_ENTRY_MODE,
        COST_FLAG,ADD_TO_RTG,STATUS_TYPE,START_DATE,
        END_DATE,PROJECTED_COMPLETION_DATE,DURATION,
        UOM_CODE,EMPLOYEE_ID,PROCESS_STATUS,CREATED_BY,
        CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,
        ACTION_FLAG,REQUEST_ID,PROGRAM_ID,
        PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE)
     values
       (WIP_RESOURCE_ACTUAL_TIMES_S.nextval,p_org_id,
        null,null,null,null,null,null,8,'N','N',null,
        sysdate,null,null,null,
        fnd_profile.value('BOM:HOUR_UOM_CODE'),
        p_wip_employee_id,1,fnd_global.user_id,
        sysdate,fnd_global.user_id,sysdate,
        fnd_global.login_id,1,null,null,null,
        null,null);

Line: 2058

   select  start_date
    from wip_resource_actual_times wrat
    where wrat.wip_entity_id is null
	and wrat.EMPLOYEE_ID=p_wip_employee_id
	and wrat.ORGANIZATION_ID=p_org_id
	 and wrat.end_date is null;

Line: 2083

       update wip_resource_actual_times set
          end_date = l_date,
          duration = l_duration
          where ORGANIZATION_ID=p_org_id
                and employee_id =p_wip_employee_id
                and end_date is null
	        and wip_entity_id is null;

Line: 2111

        select  1
        from wip_resource_actual_times wrat
        where wrat.wip_entity_id is null
	and wrat.EMPLOYEE_ID=p_wip_employee_id
	and wrat.ORGANIZATION_ID=p_org_id
	 and wrat.end_date is null;

Line: 2131

       delete from wip_resource_actual_times wrat
       where
       wrat.wip_entity_id is null
       and wrat.EMPLOYEE_ID=p_wip_employee_id
       and wrat.ORGANIZATION_ID=p_org_id
       and wrat.end_date is null;

Line: 2173

 cursor clock_in_cursor is select 1
 from wip_resource_actual_times wrat
 where wrat.wip_entity_id = p_wip_entity_id
 and wrat.operation_seq_num = p_operation_seq_num
 and wrat.resource_id = p_resource_id
 and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
 and wrat.employee_id = p_employee_id
 and wrat.instance_id = p_instance_id
 and wrat.status_type = 1
 and wrat.end_date is null;

Line: 2184

 cursor resource_uom_cursor is select unit_of_measure
 from bom_resources br
 where br.resource_id = p_resource_id;

Line: 2189

 select scheduled_flag
   from wip_operation_resources wor
  where wor.wip_entity_id = p_wip_entity_id
  and wor.operation_seq_num = p_operation_seq_num
  and wor.resource_seq_num = p_resource_seq_num;

Line: 2197

 select count(*)
  from wip_resource_actual_times wrat, wip_operation_resources wor
 where wrat.wip_entity_id = p_wip_entity_id
   and wrat.operation_seq_num = p_operation_seq_num
   and wrat.wip_entity_id = wor.wip_entity_id (+)
   and wrat.operation_seq_num = wor.operation_seq_num (+)
   and wrat.resource_seq_num = wor.resource_seq_num(+)
   and decode(wor.scheduled_flag, null, 1, 2, 1, wor.scheduled_flag) =
       decode(p_scheduled_flag, null, 1, 2, 1, p_scheduled_flag)
   and wrat.status_type = 1
   and wrat.start_date is not null
   and wrat.end_date is null;

Line: 2211

 select wor.resource_id,
        wor.resource_seq_num,
        wor.uom_code
 from wip_operation_resources wor,
      bom_resources br
 where wor.wip_entity_id = p_wip_entity_id
 and wor.operation_seq_num = p_operation_seq_num
 and decode(wor.scheduled_flag, 2, 1, wor.scheduled_flag) =
     decode(c_scheduled_flag, 2, 1, c_scheduled_flag)
 and br.resource_type = 1 --machine resource
 and wor.resource_id = br.resource_id
 and not exists (select 1
                 from wip_resource_actual_times wrat
                 where wrat.wip_entity_id = p_wip_entity_id
                 and wrat.operation_seq_num = p_operation_seq_num
                 and wrat.resource_id = wor.resource_id
                 and wrat.resource_seq_num = wor.resource_seq_num
                 and wrat.status_type = 1
                 and wrat.end_date is null);

Line: 2265

								record_insert(p_time_entry_id               => null,
																						p_organization_id             => lx_organization_id,
																						p_wip_entity_id               => p_wip_entity_id,
																						p_operation_seq_num           => p_operation_seq_num,
																						p_resource_id                 => p_resource_id,
																						p_resource_seq_num           => p_resource_seq_num,
																						p_instance_id                 => p_instance_id,
																						p_serial_number               => null,
																						p_last_update_date            => null,
																						p_last_updated_by             => null,
																						p_creation_date               => null,
																						p_created_by                  => null,
																						p_last_update_login           => null,
																						p_object_version_num          => null,
																						p_time_entry_mode             => null,
																						p_cost_flag                   => null,
																						p_add_to_rtg                  => null,
																						p_status_type                 => null,
																						p_start_date                  => sysdate,
																						p_end_date                    => null,
																						p_projected_completion_date   => null,
																						p_duration                    => null,
																						p_uom_code                    => l_uom_code,
																						p_employee_id                 => p_employee_id,
																						x_time_entry_id               => lx_time_entry_id,
																						x_return_status               => lx_return_status);

Line: 2317

										record_insert(p_time_entry_id              => null,
																								p_organization_id             => lx_organization_id,
																								p_wip_entity_id               => p_wip_entity_id,
																								p_operation_seq_num           => p_operation_seq_num,
																								p_resource_id                 => l_resource_id,
																								p_resource_seq_num           => l_resource_seq_num,
																								p_instance_id                 => null,
																								p_serial_number               => null,
																								p_last_update_date            => null,
																								p_last_updated_by             => null,
																								p_creation_date               => null,
																								p_created_by                  => null,
																								p_last_update_login           => null,
																								p_object_version_num          => null,
																								p_time_entry_mode             => null,
																								p_cost_flag                   => null,
																								p_add_to_rtg                  => null,
																								p_status_type                 => null,
																								p_start_date                  => sysdate,
																								p_end_date                    => null,
																								p_projected_completion_date   => null,
																								p_duration                    => null,
																								p_uom_code                    => l_uom_code,
																								p_employee_id                 => p_employee_id,
																								x_time_entry_id               => lx_time_entry_id,
																								x_return_status               => lx_return_status);

Line: 2395

 select object_version_number,
        start_date
 from wip_resource_actual_times
 where wip_entity_id = p_wip_entity_id
     and operation_seq_num = p_operation_seq_num
     and resource_id = p_resource_id
   /*  and nvl(resource_seq_num, -1) = nvl(p_resource_seq_num, -1) BUG 7322174*/
     and employee_id = p_employee_id
     and instance_id = p_instance_id
     and status_type = 1
     and end_date is null;

Line: 2408

   l_process_status := 3;  --updated

Line: 2419

     update wip_resource_actual_times set
        end_date = l_date,
        duration = l_duration,
        uom_code = l_uom_code,
        process_status = l_process_status,
        object_version_number = l_object_version_num + 1,
        last_update_date = l_date,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id
        where wip_entity_id = p_wip_entity_id
        and operation_seq_num = p_operation_seq_num
        and resource_id = p_resource_id
        and nvl(resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
        and employee_id = p_employee_id
        and instance_id = p_instance_id
        and status_type = 1
        and end_date is null;

Line: 2469

  record_delete(p_wip_entity_id       => p_wip_entity_id,
                p_operation_seq_num   => p_operation_seq_num,
                p_employee_id         => p_employee_id,
                x_return_status       => lx_return_status);

Line: 2493

 select (wo.quantity_in_queue + wo.quantity_running)
 from wip_operations wo
 where wo.wip_entity_id = p_wip_entity_id
       and wo.operation_seq_num = p_operation_seq_num;

Line: 2511

 select (wo.scheduled_quantity - wo.quantity_completed - nvl(wo.cumulative_scrap_quantity, 0))
 from wip_operations wo
 where wo.wip_entity_id = p_wip_entity_id
       and wo.operation_seq_num = p_operation_seq_num;

Line: 2529

 select instance_id
 from bom_resource_employees bre
 where organization_id = p_org_id
 and person_id = p_employee_id;

Line: 2549

 select count(*)
 from wip_resource_actual_times
 where wip_entity_id = p_wip_entity_id
     and operation_seq_num = nvl(p_operation_seq_num, operation_seq_num)
     and status_type = 1
     and start_date is not null
     and end_date is null;

Line: 2598

    select count(1)
    into l_emp_clock_ins
    from dual
    where exists( select wip_entity_id
                  from wip_resource_actual_times
                  where organization_id = p_organization_id
                  and employee_id = l_person_id
                  and end_date is null
                  and wip_entity_id is not null );