[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select wp.module_id
into l_module_id
from wip_preference_definitions wp
where wp.preference_id = p_pref_id;
select level_id
into l_level_id
from
(
select level_id
from wip_preference_levels v
where
v.module_id = l_module_id and
v.level_code <= l_level_code and
( v.resp_key is null or v.resp_key = p_resp_key) and
( v.organization_id is null or v.organization_id = p_org_id) and
( v.department_id is null or v.department_id = p_dept_id) and
exists
(
select 1
from wip_preference_values v1
where v1.preference_id = p_pref_id and
v1.level_id = v.level_id
)
order by v.level_code desc
)
where rownum = 1;
select wpv.attribute_value_code
into l_result
from wip_preference_values wpv
where wpv.preference_id = p_pref_id and
wpv.level_id = p_level_id and
rownum = 1;
select bdr.available_24_hours_flag
into l_24hr_resource
from bom_department_resources bdr
where bdr.resource_id = p_resource_id and
bdr.department_id = p_dept_id;
select to_char(l_c_start_date, 'HH24:MI:SS')
into l_c_from_time
from dual;
select to_char(l_c_end_date, 'HH24:MI:SS')
into l_c_to_time
from dual;
select msi.revision_qty_control_code,
msi.lot_control_code,
msi.serial_number_control_code
into l_revision_control_code, l_lot_control_code, l_serial_control_code
from mtl_system_items_b msi
where msi.organization_id = p_org_id
and msi.inventory_item_id = p_component_id;
fnd_msg_pub.Delete_Msg;
select papf.full_name
into l_name
from per_all_people_f papf
where papf.person_id = p_employee_id and
papf.effective_start_date <= l_date and
papf.effective_end_date > l_date;
select papf.full_name
into l_name
from per_all_people_f papf
where papf.person_id = p_employee_id
and rownum = 1;
SELECT count(distinct BRE.PERSON_ID) employee_count,
min(BRE.PERSON_ID) employee_id
FROM PER_PEOPLE_F P,
PER_ASSIGNMENTS_F A,
PER_PERSON_TYPES T,
HR_ORGANIZATION_UNITS ORG,
BOM_RESOURCE_EMPLOYEES BRE
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 = '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.EMPLOYEE_NUMBER IS NOT NULL AND
P.EMPLOYEE_NUMBER = p_employee_number AND
BRE.PERSON_ID = P.PERSON_ID AND
BRE.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND
ORG.ORGANIZATION_ID = p_org_id;
SELECT count(distinct BRE.PERSON_ID) employee_count,
min(BRE.PERSON_ID) employee_id
FROM PER_PEOPLE_F P,
PER_ASSIGNMENTS_F A,
PER_PERSON_TYPES T,
HR_ORGANIZATION_UNITS ORG,
BOM_RESOURCE_EMPLOYEES BRE
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 = 'C' 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 AND
P.NPW_NUMBER = p_employee_number AND
BRE.PERSON_ID = P.PERSON_ID AND
BRE.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND
ORG.ORGANIZATION_ID = p_org_id;
select min(bsd.shift_date)
into l_next_working_date
from bom_shift_dates bsd
where bsd.calendar_code = p_calendar_code
and bsd.shift_date > trunc(p_date,'ddd')
and bsd.seq_num is not null;
select calendar_code
into l_calendar_code
from mtl_parameters
where organization_id = p_org_id;
select bsd.shift_date + at.from_time /(24*60*60),
bsd.shift_date + at.to_time/(24*60*60)
into l_start_date, l_end_date
from bom_shift_dates bsd,
(select min(bst.from_time) from_time,
max(bst.to_time + decode(sign(to_time - from_time), -1, 24*60*60, 0)) to_time,
mp.calendar_code
from bom_shift_times bst, mtl_parameters mp
where bst.calendar_code = mp.calendar_code and
mp.organization_id = p_org_id and
bst.shift_num = p_shift_num
group by mp.calendar_code
) at
where bsd.calendar_code = at.calendar_code and
bsd.exception_set_id = -1 and
bsd.seq_num = p_shift_seq and
bsd.shift_num = p_shift_num;
select bd.department_code
into l_dept_code
from wip_operations wo, bom_departments bd
where wo.department_id = bd.department_id
and wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_op_seq;
select fr.responsibility_key
into l_resp_key
from fnd_responsibility fr
where fr.responsibility_id = fnd_global.RESP_ID
and fr.application_id = fnd_global.RESP_APPL_ID
and rownum = 1;
update wip_discrete_jobs wdj
set wdj.job_note = wdj.job_note || p_msg
where wdj.wip_entity_id = p_wip_entity_id;
select wdj.job_note
into job_note
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id
for update;
select note into l_note from wip_exceptions
where exception_id = p_exception_id;
update wip_exceptions we
set we.note = l_note
where we.exception_id = p_exception_id;
select bsd.shift_date, bst.from_time, bst.to_time
from bom_shift_dates bsd, bom_resource_shifts brs,
bom_shift_times bst, mtl_parameters mp
where mp.organization_id = p_organization_id and
bsd.calendar_code = p_cal_code and
bsd.exception_set_id = mp.calendar_exception_set_id and
bsd.shift_date >= trunc(p_date) - 1 and
bsd.shift_date + bst.to_time/(24*60*60) +
decode(sign(bst.to_time - bst.from_time), -1, 1, 0) > p_date and
brs.department_id = p_dept_id and
brs.resource_id = p_resource_id and
brs.shift_num = bsd.shift_num and
bst.shift_num = brs.shift_num and
bst.calendar_code = bsd.calendar_code and
bsd.seq_num is not null
order by bsd.shift_date + bst.from_time/(24*60*60);
select bdr.available_24_hours_flag
into l_24hr_resource
from bom_department_resources bdr
where bdr.department_id = p_department_id
and bdr.resource_id = p_resource_id;
select nvl(wor.department_id, wo.department_id)
into l_department_id
from wip_operations wo, wip_operation_resources wor
where wo.wip_entity_id = p_wip_entity_id
and wo.organization_id = p_organization_id
and wo.operation_seq_num = p_op_seq_num
and wor.wip_entity_id = wo.wip_entity_id
and wor.organization_id = wo.organization_id
and wor.operation_seq_num = wo.operation_seq_num
and wor.resource_seq_num = p_resource_seq_num;
select least(wor.assigned_units, bdr.capacity_units),
decode(wp.include_resource_utilization,
wip_constants.yes, nvl(bdr.utilization, 1), 1),
decode(wp.include_resource_efficiency,
wip_constants.yes, nvl(bdr.efficiency, 1), 1)
into l_units, l_utilization, l_efficiency
from wip_operation_resources wor,
bom_department_resources bdr,
wip_parameters wp
where wor.organization_id = p_organization_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq_num
and wor.resource_seq_num = p_resource_seq_num
and bdr.resource_id = wor.resource_id
and bdr.department_id = l_department_id
and wp.organization_id = p_organization_id;
select count(distinct wrat.instance_id)
into l_actual_units
from wip_resource_actual_times wrat
where wrat.organization_id = p_organization_id
and wrat.wip_entity_id = p_wip_entity_id
and wrat.operation_seq_num = p_op_seq_num
and wrat.resource_seq_num = p_resource_seq_num
and wrat.status_type = 1
and wrat.end_date is null;
select br.resource_type, wip_ws_time_entry.is_time_uom(br.unit_of_measure)
into l_resource_type, l_is_time_uom
from bom_resources br
where br.organization_id = p_organization_id
and br.resource_id = p_resource_id;
select count(wor.resource_seq_num)
into l_not_projected_count
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq_num
and wor.organization_id = p_organization_id
and wor.projected_completion_date is null
and wor.scheduled_flag in (1, 3, 4);
select max(wor.projected_completion_date)
into l_completion_date
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq_num
and wor.organization_id = p_organization_id
and wor.projected_completion_date is not null
and wor.scheduled_flag in (1, 3, 4);
select to_number(ORG_INFORMATION2) into l_le_id
from hr_organization_information
where organization_id = p_org_id
and org_information_context = 'Accounting Information';
select resource_type
from bom_resources br
where resource_id = p_resource_id;
select person_id
from bom_resource_employees bremp
where instance_id = p_instance_id;
select msik.concatenated_segments
from bom_resource_equipments breq,
mtl_system_items_kfv msik
where breq.instance_id = p_instance_id
and msik.inventory_item_id = breq.inventory_item_id
and msik.organization_id = breq.organization_id;
select user_function_name
into l_name
from fnd_form_functions_vl t
where t.function_name = p_oasf;
select wpv.sequence_number
from wip_preference_values wpv
where wpv.preference_id = p_pref_id
and wpv.level_id = p_level_id
and wpv.attribute_name = p_attribute_name
and wpv.attribute_value_code = p_attribute_val;
select wpv.attribute_value_code
from wip_preference_values wpv
where wpv.preference_id = p_pref_id
and wpv.level_id = p_level_id
and wpv.sequence_number = p_seq_num
and wpv.attribute_name = p_attribute_name;
select decode(nvl(sum(shortage_qty),-1),-1,3,0,2,1)
into l_comp_shortage
from wip_ws_comp_shortage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and SUPPLY_SUBINVENOTRY is null;
select decode(nvl(sum(shortage_qty),-1),-1,3,0,2,1)
into l_comp_shortage
from wip_ws_comp_shortage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and SUPPLY_SUBINVENOTRY is not null;
select decode(nvl(sum(RESOURCE_SHORTAGE),-1),-1,3,0,2,1)
into l_res_shortage
from wip_ws_res_shortage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
SELECT count(1)
INTO l_pgm_count
FROM fnd_concurrent_requests
WHERE program_application_id = p_program_application_id
AND concurrent_program_id = p_concurrent_program_id
AND upper(phase_code) = 'R'
AND argument1 = to_char(p_org_id);
select sequence_number,attribute_value_code
from wip_preference_values
where preference_id = g_pref_id_attachment
and level_id = p_level_id;
select 1 into l_attach_count
from dual
where exists (select 1
from FND_ATTACHED_DOCUMENTS
where entity_name = 'WIP_DISCRETE_JOBS'
and pk1_value = p_job_id
and pk2_value = p_org_id
and rownum = 1);
select 1 into l_attach_count
from dual
where exists (select 1
from FND_ATTACHED_DOCUMENTS
where entity_name = 'WIP_DISCRETE_OPERATIONS'
and pk1_value = p_job_id
and pk2_value = p_op_seq
and pk3_value = p_org_id
and rownum = 1);
select 1 into l_attach_count
from dual
where exists (select 1
from FND_ATTACHED_DOCUMENTS fad,
wip_entities we
where fad.entity_name = 'MTL_SYSTEM_ITEMS'
and fad.pk1_value = p_org_id
and fad.pk2_value = we.primary_item_id
and we.wip_entity_id= p_job_id
and fad.pk1_value = we.organization_id
and rownum = 1);
select 1 into l_attach_count
from dual
where exists (select 1
from FND_ATTACHED_DOCUMENTS fad,
wip_discrete_jobs wdj
where fad.entity_name = 'BOM_BILL_OF_MATERIALS'
and fad.pk1_value = wdj.common_bom_sequence_id
and wdj.wip_entity_id = p_job_id
and wdj.organization_id = p_org_id
and rownum = 1);
select 1 into l_attach_count
from dual
where exists (select 1
from FND_ATTACHED_DOCUMENTS fad,
wip_discrete_jobs wdj
where fad.entity_name = 'BOM_OPERATIONAL_ROUTINGS'
and fad.pk1_value = wdj.common_routing_sequence_id
and wdj.wip_entity_id = p_job_id
and wdj.organization_id = p_org_id
and rownum = 1);
select 1 into l_attach_count
from dual
where exists (select 1
from FND_ATTACHED_DOCUMENTS fad
where fad.entity_name = 'MTL_SYSTEM_ITEMS'
and pk1_value = p_org_id
and pk2_value IN ( select inventory_item_id
from wip_requirement_operations
where wip_entity_id = p_job_id
and operation_seq_num = p_op_seq
and organization_id = p_org_id)
and rownum = 1);
select 1 into l_attach_count
from dual
where exists (select 1
from FND_ATTACHED_DOCUMENTS fad
where fad.entity_name = 'OE_ORDER_HEADERS'
and pk1_value IN ( select header_id
from oe_order_lines_all
where line_id in ( select mr.demand_source_line_id
from mtl_reservations mr
where mr.demand_source_type_id in (2,8)
and mr.supply_source_type_id = 5
and mr.supply_source_header_id = p_job_id
and mr.organization_id = p_org_id ))
and rownum = 1);
select 1 into l_attach_count
from dual
where exists (select 1
from FND_ATTACHED_DOCUMENTS fad
where fad.entity_name = 'OE_ORDER_LINES'
and pk1_value IN ( select mr.demand_source_line_id
from mtl_reservations mr
where mr.demand_source_type_id in (2,8)
and mr.supply_source_type_id = 5
and mr.supply_source_header_id = p_job_id
and mr.organization_id = p_org_id )
and rownum = 1);
select wrat.start_date
into l_shift_date
from wip_resource_actual_times wrat
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;
select to_char(l_shift_date, WIP_CONSTANTS.DATETIME_FMT)
into l_result
from dual;
select we.wip_entity_name
into l_job_name
from wip_entities we
where we.wip_entity_id = l_wip_entity_id
and we.organization_id = p_org_id;
select to_char(l_c_clock_date, WIP_CONSTANTS.DATETIME_FMT)
into l_clock_desc
from dual;
select wip_ws_util.get_jobop_name(l_job_name, l_op_seq)
into l_job_desc
from dual;
select lot_control_code,serial_number_control_code
into l_lot_control,l_serial_control
from mtl_system_items_b
where inventory_item_id = p_comp_item_id
and organization_id = p_org_id;
select abs(nvl(sum(mtln.transaction_quantity),0))
into l_issue_qty
from MTL_TRANSACTION_LOT_NUMBERS mtln
where mtln.transaction_source_type_id = 5
and mtln.transaction_source_id = p_job_id
and mtln.parent_object_type = 2
and mtln.parent_object_number = p_assy_serial
and mtln.parent_item_id = p_assy_item_id
and mtln.organization_id= p_org_id
and mtln.inventory_item_id = p_comp_item_id
and exists (select 1 from mtl_material_transactions mmt
where mmt.transaction_id = mtln.transaction_id
and mmt.transaction_source_type_id = mtln.transaction_source_type_id
and mmt.transaction_source_id = mtln.transaction_source_id
and mmt.organization_id = mtln.organization_id
and mmt.inventory_item_id = mtln.inventory_item_id
and mmt.operation_seq_num = p_op_seq
and mmt.transaction_action_id in (1,27,33,34));
select nvl(sum(decode(mut.receipt_issue_type,1,1,-1)),0)
into l_issue_qty
from
mtl_unit_transactions mut
where mut.transaction_source_type_id = 5
and mut.transaction_source_id = p_job_id
and mut.parent_object_type = 2
and mut.parent_object_number = p_assy_serial
and mut.parent_item_id = p_assy_item_id
and mut.organization_id= p_org_id
and mut.receipt_issue_type in (1,2)
and mut.inventory_item_id = p_comp_item_id
and exists (select 1 from mtl_material_transactions mmt
where mmt.transaction_id = mut.transaction_id
and mmt.transaction_source_type_id = mut.transaction_source_type_id
and mmt.transaction_source_id = mut.transaction_source_id
and mmt.organization_id = mut.organization_id
and mmt.inventory_item_id = mut.inventory_item_id
and mmt.operation_seq_num = p_op_seq
and mmt.transaction_action_id in (1,27,33,34));