[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl((max(resource_seq_num) + 10),10)
into l_resource_seq_num
from wip_operation_resources
where wip_entity_id = l_workorder
and operation_seq_num = l_operation;
select distinct full_name
into l_employee_name
from PER_ALL_PEOPLE_F
where person_id = p_instance_id
and l_start_time BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
select organization_id
into l_organization_id
from wip_entities
where wip_entity_id = l_workorder;
select resource_code , unit_of_measure
into l_resource_code , l_uom
from bom_resources
where resource_id = l_resource
and organization_id = l_organization_id ;
select distinct department_code
into l_charge_department_code
from bom_departments
where organization_id = l_organization_id
and department_id = l_charge_department;
WIP_EAM_RESOURCE_TRANSACTION.insert_into_wcti(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_wip_entity_id => l_workorder
,p_operation_seq_num => l_operation
,p_organization_id => l_organization_id
,p_transaction_qty => l_measure
,p_transaction_date => l_start_time
,p_resource_seq_num => l_resource_seq_num
,p_uom => l_uom
,p_resource_code => l_resource_code
,p_reason_name => null
,p_reference => null
,p_instance_id => l_instance_id
,p_serial_number => null
,p_charge_dept_id => l_charge_dept_id
,p_actual_resource_rate => l_actual_resource
,p_employee_id => l_emp_id
,p_employee_number => l_emp_no
,x_return_status => l_return_status1
,x_msg_count => l_msg_count1
,x_msg_data => l_msg_data1);
g_msg := 'Insert into WCTI - SUCCESS';
g_msg := 'Insert into WCTI failed';
g_msg := 'Work order details no found - skip resource insertion';
fnd_message.set_name('EAM', 'EAM_WO_INVALID_RES_SELECTED');
l_deleted VARCHAR2(1);
l_deleted := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).deleted;
g_msg := 'Building Block Status : Changed ' || l_changed || ' Deleted : ' || l_deleted;
if( l_deleted = 'N') and (l_changed = 'N') then
g_msg := 'Before get_attribute_id - NEW (N,N)-> (Delete,Change)';
g_msg := 'After get_attribute_id - NEW (N,N)-> (Delete,Change)';
g_msg := 'After Resource Txn - (N,N)-> (Delete,Change)';
if( l_deleted = 'Y') and (l_changed = 'Y') then
-- t_temp_attr_index := l_last_att_index;
g_msg := 'Before get_attribute_id - NEW (Y,Y)-> (Delete,Change)';
g_msg := 'Update OTL Timecard. Resource Transaction reversed.';
g_msg := 'After get_attribute_id - NEW (Y,Y)-> (Delete,Change)';
g_msg := 'Before get_attribute_id - OLD (Y,Y)-> (Delete,Change)';
g_msg := 'After get_attribute_id - OLD (Y,Y)-> (Delete,Change)';
g_msg := 'After Reversing Resource Txn - (Y,Y)-> (Delete,Change)';
if( l_deleted = 'Y') and (l_changed = 'N') then
g_msg := 'Before get_attribute_id - NEW (Y,N)-> (Delete,Change)';
g_msg := 'After get_attribute_id - NEW (Y,N) -> (Delete,Change)';
if( l_deleted = 'N') and (l_changed = 'Y') then
-- t_temp_attr_index := l_last_att_index;
g_msg := 'Before get_attribute_id - NEW (N,Y)-> (Delete,Change)';
g_msg := 'After get_attribute_id - NEW (N,Y) -> (Delete,Change)';
g_msg := 'Before get_attribute_id - OLD (N,Y)-> (Delete,Change)';
g_msg := 'After get_attribute_id - OLD (N,Y)-> (Delete,Change)';
g_msg := 'After Reversing Resource Txn - (N,Y)-> (Delete,Change)';
g_msg := 'After Resource Txn - (N,Y)-> (Delete,Change)';
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status
(p_process => 'Maintenance Retrieval Process'
,p_status => 'SUCCESS'
,p_exception_description => 'Building Block Processed Successfully'
,p_rollback => FALSE);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status
(p_process => 'Maintenance Retrieval Process'
,p_status => 'ERRORS'
,p_exception_description => SUBSTR(SQLERRM,1,200)
,p_rollback => FALSE);
select to_char(employee_id)
into l_person_id
from fnd_user
where user_id = l_user_id;
select days_off,days_on, (days_off+days_on) as total_days
from bom_workday_patterns
where calendar_code = l_calendar_code
and shift_num is null
and seq_num is not null
order by seq_num;
select calendar_code
into l_calendar_code
from mtl_parameters
where organization_id = p_organization_id;
select SUM(days_off + days_on)
into l_day_block
from bom_workday_patterns
where calendar_code = l_calendar_code
and shift_num is null
and seq_num is not null
group by calendar_code;
select calendar_start_date,
calendar_end_date
into l_start_date,
l_end_date
from bom_calendars
where calendar_code = l_calendar_code;
select nvl(exception_type,2)
into l_exception_type
from bom_calendar_exceptions
where calendar_code = l_calendar_code
and exception_date = p_date;
l_current_week_tc.DELETE;
select wip_entity_name into l_wip_entity_name
from wip_entities
where wip_entity_id = l_wip_entity_id
and organization_id = l_org_id;
select distinct msik.concatenated_segments into l_asset_group_name
from mtl_system_items_b_kfv msik, mtl_parameters mp
where msik.inventory_item_id = l_asset_group
and mp.maint_organization_id = l_org_id
and mp.organization_id = msik.organization_id;
select department_code into l_dept_name from
bom_departments where
department_id = l_dept_id
and organization_id = l_org_id;
select department_code into l_charge_dept_name from
bom_departments where
department_id = l_charge_dept
and organization_id = l_org_id;
select resource_code into l_resource_name from
bom_resources where
resource_id = l_resource_id and
organization_id = l_org_id;
select job_type into l_job_type from wip_discrete_jobs where
wip_entity_id = l_wip_entity_id
and organization_id = l_org_id;
select count(*) into l_count from wip_discrete_jobs where
wip_entity_id = l_wip_entity_id and
organization_id = l_org_id and
nvl(asset_group_id,rebuild_item_id) = l_asset_group;
select count(*) into l_count from csi_item_instances where
serial_number = l_asset_number and
inventory_item_id = l_asset_group;
select count(*) into l_count from wip_discrete_jobs where
nvl(asset_number,rebuild_serial_number) = l_asset_number and
wip_entity_id = l_wip_entity_id and
organization_id = l_org_id;
select count(*) into l_count from wip_operations where
wip_entity_id = l_wip_entity_id and
organization_id = l_org_id and
operation_seq_num = l_operation_number;
select count(*) into l_count from wip_operations where
wip_entity_id = l_wip_entity_id and
operation_seq_num = l_operation_number and
organization_id = l_org_id and
department_id = l_dept_id;
select count(*) into l_count from (select br.resource_code,
br.description,
br.resource_type,
br.functional_currency_flag,
2 autocharge_type,
br.unit_of_measure uom_code,
br.unit_of_measure uom,
br.default_basis_type basis_type, ca.activity_id,
ca.activity,
br.standard_rate_flag,
br.organization_id,
to_char(bdr.department_id) as department_id,
(select meaning from mfg_lookups m1 where m1.lookup_type like 'BOM_RESOURCE_TYPE' and m1.lookup_code = 2) meaning,
to_char(br.resource_id) as res_id
from cst_activities ca,
bom_department_resources bdr,
bom_resources br
where br.resource_id = bdr.resource_id and
br.default_activity_id = ca.activity_id (+) and
nvl(ca.disable_date(+),sysdate+1) > sysdate and
nvl(br.disable_date,sysdate+1) > sysdate and (ca.organization_id is
null or ca.organization_id = br.organization_id)
order by br.resource_code
)
where res_id = l_resource_id
and department_id = l_dept_id;
select count(*) into l_count from (select distinct bd.department_code,
bd.organization_id,
bd.description,
to_char(bdri.department_id) as department_id,
to_char(ppf.person_id) as person_id,
bdri.resource_id as resource_id
from bom_dept_res_instances bdri,
bom_departments bd,
bom_resource_employees bre,
per_people_f ppf
where bdri.instance_id = bre.instance_id
and ppf.person_id = bre.person_id
and bd.department_id = bdri.department_id
and sysdate >= ppf.effective_start_date
and sysdate <= ppf.effective_end_date
union
select distinct bd.department_code,
bd.organization_id,
bd.description,
to_char(bdr.department_id) as department_id,
to_char(ppf.person_id) as person_id,
bdri.resource_id as resource_id
from bom_dept_res_instances bdri,
bom_departments bd,
bom_resource_employees bre,
bom_department_resources bdr,
per_people_f ppf
where bdri.instance_id = bre.instance_id
and ppf.person_id = bre.person_id
and sysdate >= ppf.effective_start_date and sysdate <= ppf.effective_end_date
and bd.department_id = bdr.department_id
and bdr.share_from_dept_id = bdri.department_id
and bdr.resource_id = bdri.resource_id
)
where department_id = l_charge_dept
and resource_id = l_resource_id;
select nvl(min(period_start_date), (sysdate - 200000)),
nvl(max(schedule_close_date),(sysdate + 200000))
into l_min_acct_period_date,l_max_acct_period_date
from org_acct_periods
where organization_id = l_org_id
and upper(open_flag) = 'Y';
select date_released into l_wo_released_date
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id
and organization_id = l_org_id;