[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
l_params(9).paramName := 'p_last_update_date';
l_params(9).paramValue := p_last_update_date;
l_params(10).paramName := 'p_last_updated_by';
l_params(10).paramValue := p_last_updated_by;
l_params(13).paramName := 'p_last_update_login';
l_params(13).paramValue := p_last_update_login;
wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_insert',
p_params => l_params,
x_returnStatus => x_return_status);
l_process_status := 2; --inserted
select WIP_RESOURCE_ACTUAL_TIMES_S.nextval into l_time_entry_id from dual;
wip_logger.log('Inserting into wip_resource_actual_times', l_return_status);
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);
wip_logger.log('Calling update_actual_start_dates', l_return_status);
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);
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);
wip_logger.log('Calling derive_insert_wcti', l_return_status);
derive_insert_wcti(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_entry_id => null,
p_employee_id => p_employee_id,
p_duration => p_duration,
p_txn_uom => p_uom_code,
p_hour_uom => l_uom_code,
x_return_status =>l_return_status);
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_insert',
p_procReturnStatus => x_return_status,
p_msg => 'normal completion',
x_returnStatus => l_return_status);
END record_insert;
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;
l_params(9).paramName := 'p_last_update_date';
l_params(9).paramValue := p_last_update_date;
l_params(10).paramName := 'p_last_updated_by';
l_params(10).paramValue := p_last_updated_by;
l_params(13).paramName := 'p_last_update_login';
l_params(13).paramValue := p_last_update_login;
wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_update',
p_params => l_params,
x_returnStatus => x_return_status);
l_process_status := 3; --updated
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;
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;
wip_logger.log('calling update_actual_start_dates()', l_return_status);
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);
wip_logger.log('calling update_proj_completion_dates()', l_return_status);
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);
l_return_status := 'U'; --error condition: non time based resources are never updated
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_update',
p_procReturnStatus => x_return_status,
p_msg => 'normal completion',
x_returnStatus => l_return_status);
END record_update;
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;
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;
wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_delete',
p_params => l_params,
x_returnStatus => x_return_status);
l_process_status := 4; --deleted
open delete_cursor;
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;
if delete_cursor%NOTFOUND then
l_return_status := 'U';
wip_logger.log('delete_cursor not found', l_return_status);
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;
wip_logger.log('calling update_actual_start_dates()', l_return_status);
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);
wip_logger.log('calling update_proj_completion_dates()', l_return_status);
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);
close delete_cursor;
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_delete',
p_procReturnStatus => x_return_status,
p_msg => 'normal completion',
x_returnStatus => l_return_status);
END record_delete;
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;
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;
wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_delete',
p_params => l_params,
x_returnStatus => x_return_status);
l_process_status := 4; --deleted
open delete_cursor;
fetch delete_cursor into l_object_version_num,
l_organization_id,
l_resource_id,
l_resource_seq_num,
l_start_date;
if delete_cursor%NOTFOUND then
l_return_status := 'U';
wip_logger.log('delete_cursor not found', l_return_status);
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;
wip_logger.log('calling update_actual_start_dates()', l_return_status);
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);
wip_logger.log('calling update_proj_completion_dates()', l_return_status);
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);
close delete_cursor;
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.record_delete',
p_procReturnStatus => x_return_status,
p_msg => 'normal completion',
x_returnStatus => l_return_status);
END record_delete;
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;
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*/
);
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;
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;
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;
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;
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;
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;
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;
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;
select greatest(nvl(l_wor_max,0), nvl(l_wrat_max,0)) + 10
into l_resource_seq_num
from dual;
/* 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);
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);
End update_actual_comp_dates;
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;
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;
select distinct wrat.wip_entity_id,
wrat.operation_seq_num,wrat.instance_id
from wip_resource_actual_times wrat
where wrat.organization_id = p_organization_id
and ( (wrat.instance_id in (select instance_id from bom_resource_employees where organization_id=p_organization_id and person_id=p_emp_id ) ) or (employee_id=p_emp_id and instance_id is null) )
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;
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;
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_seq_num = wor.resource_seq_num (+) -- Bug 14742435
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;
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;
/* 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';
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;
wip_logger.log('calling derive_insert_wcti', l_return_status);
derive_insert_wcti(p_organization_id => l_org_id,
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => p_completed_op,
p_resource_seq_num => l_resource_seq_num,
p_resource_id => l_resource_id,
p_entry_id => l_entry_id,
p_employee_id => null,
p_duration => l_duration,
p_txn_uom => l_uom_code,
p_hour_uom => l_uom_code,
x_return_status =>l_return_status);
wip_logger.log('update wip_resource_actual_times', l_return_status);
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;
update_actual_comp_dates(p_wip_entity_id, p_completed_op);
/* 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;
/* 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);
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;
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;
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;
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);
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;
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;
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;
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;
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;
wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_actual_start_dates',
p_params => l_params,
x_returnStatus => l_return_status);
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;
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;
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;
update wip_discrete_jobs set
actual_start_date = l_min_start_date
where wip_entity_id = p_wip_entity_id;
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_actual_start_dates',
p_procReturnStatus => l_return_status,
p_msg => 'normal completion',
x_returnStatus => l_return_status);
END update_actual_start_dates;
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;
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;
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;
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);
wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_actual_completion_dates',
p_params => l_params,
x_returnStatus => l_return_status);
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;
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;
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_actual_completion_dates',
p_procReturnStatus => l_return_status,
p_msg => 'normal completion',
x_returnStatus => l_return_status);
END update_actual_completion_dates;
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;
wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_proj_completion_dates',
p_params => l_params,
x_returnStatus => l_return_status);
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;
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;
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.update_proj_completion_dates',
p_procReturnStatus => l_return_status,
p_msg => 'normal completion',
x_returnStatus => l_return_status);
END update_proj_completion_dates;
cursor job_on_off_status_cursor is select employee_id
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id;
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;
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;
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 = to_char(p_wip_employee_id)
and rownum=1;
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.npw_number = to_char(p_wip_employee_id)
and rownum=1;
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;
wip_logger.log('inserting into wip_resource_actual_times', l_ret_status);
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);
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;
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;
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;
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;
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;
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);
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;
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;
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;
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;
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
/*Fix Bug 9681625,it is unnecessary to filter based on resource_seq_num
because user will not able to clock in if resource is not unique per op*/
--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;
cursor resource_uom_cursor is select unit_of_measure
from bom_resources br
where br.resource_id = p_resource_id;
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;
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;
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);
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);
wip_logger.log('calling record_insert (loop)', x_status);
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);
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;
l_process_status := 3; --updated
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
/*Fix Bug 9721473, remove the filtering on resouce_seq_num from update statement so that
ad hoc resource of Manual Charge type machine resource can be clocked out successfully*/
-- 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;
wip_logger.log('calling record_delete', x_status);
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);
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;
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;
select instance_id
from bom_resource_employees bre
where organization_id = p_org_id
and person_id = p_employee_id;
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;
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 );
select hourly_labor_rate
from wip_employee_labor_rates
where employee_id = p_emp_id
and organization_id = p_org_id
and effective_date =
(select max(effective_date)
from wip_employee_labor_rates
where employee_id = p_emp_id
and organization_id = p_org_id
and effective_date <= trunc(p_txn_date));
This procedure is shared between record_insert and process_time_records
We will not handle exception here because 2 callers expected differnt behavior.
For report usage rate case, caller expected exception to be raised to display on the page.
While for clock in/clock out, based on bug 6891758, we should still allow user to clock out
even resource-charge has exception*/
PROCEDURE derive_insert_wcti(
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_entry_id in number,
p_employee_id in number,
p_duration in number,
p_txn_uom in varchar2,
p_hour_uom in varchar2,
x_return_status out NOCOPY varchar2)
IS
l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
wip_logger.entryPoint(p_procName => 'WIP_WS_TIME_ENTRY.derive_insert_wcti',
p_params => l_params,
x_returnStatus => l_retStatus);
select organization_code
into l_organization_code
from mtl_parameters
where organization_id = p_organization_id;
wip_logger.log('derive_insert_wcti Phase 10', l_retStatus);
select wip_entity_name
into l_wip_entity_name
from wip_entities
where wip_entity_id = p_wip_entity_id;
wip_logger.log('derive_insert_wcti Phase 20', l_retStatus);
select br.resource_code, br.resource_type
into l_resource_code, l_resource_type
from bom_resources br
where br.resource_id = p_resource_id;
wip_logger.log('derive_insert_wcti Phase 30', l_retStatus);
/*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 = p_entry_id
and wrat.employee_id = mec.employee_id
and wrat.organization_id = mec.organization_id;*/
SELECT wrat.employee_id, NVL(P.EMPLOYEE_NUMBER, P.NPW_NUMBER)
into l_employee_id, l_employee_num
FROM PER_PEOPLE_F P,
PER_ASSIGNMENTS_F A,
PER_PERSON_TYPES T,
HR_ORGANIZATION_UNITS ORG,
wip_resource_actual_times wrat
WHERE A.PERSON_ID = P.PERSON_ID AND
ORG.BUSINESS_GROUP_ID = P.BUSINESS_GROUP_ID AND
A.PRIMARY_FLAG = 'Y' AND
A.ASSIGNMENT_TYPE in ('C','E') AND
P.PERSON_TYPE_ID = T.PERSON_TYPE_ID AND
P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID AND
TRUNC(sysdate) BETWEEN P.EFFECTIVE_START_DATE AND
NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1) AND
TRUNC(sysdate) BETWEEN A.EFFECTIVE_START_DATE AND
NVL(A.EFFECTIVE_END_DATE, SYSDATE + 1) AND
(P.NPW_NUMBER IS NOT NULL OR (P.EMPLOYEE_NUMBER IS NOT NULL AND P.CURRENT_EMPLOYEE_FLAG ='Y')) AND
ORG.ORGANIZATION_ID = wrat.organization_id AND
wrat.time_entry_id = p_entry_id AND
wrat.employee_id = P.PERSON_ID;
/*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;*/
SELECT NVL(P.EMPLOYEE_NUMBER, P.NPW_NUMBER)
into l_employee_num
FROM PER_PEOPLE_F P,
PER_ASSIGNMENTS_F A,
PER_PERSON_TYPES T,
HR_ORGANIZATION_UNITS ORG
WHERE A.PERSON_ID = P.PERSON_ID AND
ORG.BUSINESS_GROUP_ID = P.BUSINESS_GROUP_ID AND
A.PRIMARY_FLAG = 'Y' AND
A.ASSIGNMENT_TYPE in ('C','E') AND
P.PERSON_TYPE_ID = T.PERSON_TYPE_ID AND
P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID AND
TRUNC(sysdate) BETWEEN P.EFFECTIVE_START_DATE AND
NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1) AND
TRUNC(sysdate) BETWEEN A.EFFECTIVE_START_DATE AND
NVL(A.EFFECTIVE_END_DATE, SYSDATE + 1) AND
(P.NPW_NUMBER IS NOT NULL OR (P.EMPLOYEE_NUMBER IS NOT NULL AND P.CURRENT_EMPLOYEE_FLAG ='Y')) AND
ORG.ORGANIZATION_ID = p_organization_id AND
P.PERSON_ID = p_employee_id;
wip_logger.log('derive_insert_wcti Phase 40', l_retStatus);
wip_logger.log('derive_insert_wcti:l_resource_seq_num = '|| l_resource_seq_num , l_retStatus);
select wo.department_id, bd.department_code
into l_department_id, l_department_code
from wip_operations wo, bom_departments bd
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_operation_seq_num
and wo.department_id = bd.department_id;
wip_logger.log('derive_insert_wcti Phase 50', l_retStatus);
select wor.basis_type, wor.standard_rate_flag, wor.uom_code,ca.activity_id, ca.activity
into l_basis_type, l_standard_rate_flag, l_primary_uom, l_activity_id, l_activity_name
from wip_operation_resources wor,cst_activities ca
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_operation_seq_num
and wor.resource_seq_num = l_resource_seq_num -- p_resource_seq_num Bug 13481688
and wor.resource_id = p_resource_id
and wor.activity_id = ca.activity_id (+)
and nvl(ca.disable_date(+),sysdate+1) > sysdate
and (ca.organization_id is null or ca.organization_id = p_organization_id);
select br.default_basis_type, br.standard_rate_flag, br.unit_of_measure,ca.activity_id, ca.activity
into l_basis_type, l_standard_rate_flag, l_primary_uom,l_activity_id, l_activity_name
from bom_resources br,cst_activities ca
where br.resource_id = p_resource_id
and br.default_activity_id = ca.activity_id (+)
and nvl(ca.disable_date(+),sysdate+1) > sysdate
and (ca.organization_id is null or ca.organization_id = p_organization_id);
wip_logger.log('derive_insert_wcti Phase 60', l_retStatus);
select primary_item_id, project_id, task_id
into l_primary_item_id, l_project_id, l_task_id
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
wip_logger.log('derive_insert_wcti Phase 70', l_retStatus);
wip_logger.log('derive_insert_wcti Phase 80', l_retStatus);
wip_logger.log('derive_insert_wcti Phase 90', l_retStatus);
wip_logger.log('derive_insert_wcti Phase 100', l_retStatus);
x_group_id := -9999; -- Bug 13481688 , Assiginning dummy value to group_id to identify this group later while inserting the resources.
wip_logger.log('derive_insert_wcti Phase 110', l_retStatus);
wip_logger.log('inserting into wip_cost_txn_interface', l_retStatus);
insert into wip_cost_txn_interface(
group_id, -- Bug 13481688
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,
primary_item_id,--Bug 10097774
acct_period_id,--Bug 10097774
department_id, --Bug 10097774
department_code, --Bug 10097774
resource_type, --Bug 10097774
basis_type, --Bug 10097774
autocharge_type, --Bug 10097774
standard_rate_flag, --Bug 10097774
primary_quantity, --Bug 10097774
primary_uom, --Bug 10097774
actual_resource_rate, --Bug 10097774
project_id, --Bug 10097774
task_id,
activity_id,
activity_name) --Bug 10097774
values(
x_group_id, -- Bug 13481688
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.RES_PROC, --Bug 10097774
WIP_CONSTANTS.PENDING,
p_resource_id,
l_resource_code,
l_resource_seq_num, --p_resource_seq_num, Bug 13481688
WIP_CONSTANTS.SOURCE_CODE,
sysdate,
-- bug 8851845 round transaction_quantity to MAX_DISPLAYED_PRECISION
round(p_duration, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION), --non time based resource implies no start/end time which means duration is mandatory
WIP_CONSTANTS.RES_TXN,
p_txn_uom,
WIP_CONSTANTS.DISCRETE,
p_wip_entity_id,
l_wip_entity_name,
l_employee_id,
l_employee_num,
l_primary_item_id,--Bug 10097774
l_acctPeriodID,--Bug 10097774
l_department_id, --Bug 10097774
l_department_code, --Bug 10097774
l_resource_type, --Bug 10097774
l_basis_type, --Bug 10097774
WIP_CONSTANTS.MANUAL, --Bug 10097774
l_standard_rate_flag, --Bug 10097774
round(l_primary_quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION), --Bug 10097774
l_primary_uom, --Bug 10097774
l_actual_resource_rate,--Bug 10097774
l_project_id,--Bug 10097774
l_task_id,
l_activity_id,
l_activity_name); --Bug 10097774
insert into wip_operation_resources
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
resource_id,
uom_code,
basis_type,
activity_id,
standard_rate_flag,
usage_rate_or_amount,
scheduled_flag,
assigned_units,
autocharge_type,
applied_resource_units,
applied_resource_value,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
start_date,
completion_date)
select distinct
wti.wip_entity_id,
wti.operation_seq_num,
wti.resource_seq_num,
wti.organization_id,
wti.repetitive_schedule_id,
wti.resource_id,
wti.primary_uom,
wti.basis_type,
br.default_activity_id,
wti.standard_rate_flag,
nvl(wti.usage_rate_or_amount,0),
2,
1,
2,
0,
0,
wti.last_update_date,
wti.last_updated_by,
wti.creation_date,
wti.created_by,
wti.last_update_login,
wo.first_unit_start_date,
wo.last_unit_completion_date
from wip_operations wo,
wip_cost_txn_interface wti,
bom_resources br
where wti.group_id = x_group_id
and wti.process_phase = 2
and wti.process_status = 1
and wti.transaction_type = 1
and not exists
(select 'op res already exists'
from wip_operation_resources wor
where wor.wip_entity_id = wti.wip_entity_id
and wor.operation_seq_num = wti.operation_seq_num
and wor.resource_seq_num = wti.resource_seq_num
and wor.organization_id = wti.organization_id
and nvl(wor.repetitive_schedule_id,-1) = nvl(wti.repetitive_schedule_id,-1))
and wti.rowid =
(select min(wti2.rowid)
from wip_cost_txn_interface wti2
where wti2.group_id = x_group_id
and wti2.process_phase = 2
and wti2.process_status = 1
and wti2.transaction_type = 1
and wti2.wip_entity_id = wti.wip_entity_id
and wti2.operation_seq_num = wti.operation_seq_num
and wti2.resource_seq_num = wti.resource_seq_num
and wti2.organization_id = wti.organization_id
and nvl(wti2.repetitive_schedule_id,-1) = nvl(wti.repetitive_schedule_id,-1))
and br.resource_id = wti.resource_id
and wo.wip_entity_id = wti.wip_entity_id
and wo.operation_seq_num = wti.operation_seq_num
and wo.organization_id = wti.organization_id
and nvl(wo.repetitive_schedule_id,-1) = nvl(wti.repetitive_schedule_id,-1) ;
update wip_cost_txn_interface set group_id = NULL where group_id = x_group_id;
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.derive_insert_wcti',
p_procReturnStatus => x_return_status,
p_msg => l_msg,
x_returnStatus => l_retStatus);
wip_logger.exitPoint(p_procName => 'WIP_WS_TIME_ENTRY.derive_insert_wcti',
p_procReturnStatus => x_return_status,
p_msg => l_msg,
x_returnStatus => l_retStatus);
END derive_insert_wcti;
select count(*)
into l_num_jobs
from
(select distinct wrat.wip_entity_id, wrat.operation_seq_num
from wip_resource_actual_times wrat,
bom_resources br
where wrat.organization_id = p_org_id
and wrat.employee_id = p_emp_id
and wrat.start_date is not null
and wrat.end_date is null
and wrat.time_entry_mode <> 8
and wrat.resource_id = br.resource_id
and wrat.organization_id = br.organization_id
and br.resource_type = 2);
select wrat.wip_entity_id,
wrat.operation_seq_num,
wrat.start_date
into l_wip_entity_id, l_op_seq_num, l_start_date
from wip_resource_actual_times wrat,
bom_resources br
where wrat.organization_id = p_org_id
and wrat.employee_id = p_emp_id
and wrat.start_date is not null
and wrat.end_date is null
and wrat.time_entry_mode <> 8
and wrat.resource_id = br.resource_id
and wrat.organization_id = br.organization_id
and br.resource_type = 2;