[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select employee_id
into l_person_id
from fnd_user
where user_id = p_user_id;
SELECT wdj.material_account, wdj.material_variance_account, wdj.project_id,
wdj.task_id, we.wip_entity_name, wdj.asset_number,wdj.asset_group_id, wdj.organization_id, wdj.priority
INTO l_material_account, l_material_variance_account, l_project_id,
l_task_id, l_wip_entity_name, l_serial_number,l_asset_group_id, l_organization_id, l_priority
FROM wip_discrete_jobs wdj, wip_entities we
WHERE wdj.wip_entity_id = p_wip_entity_id
AND wdj.organization_id = p_organization_id
AND wdj.wip_entity_id = we.wip_entity_id;
select meaning into l_priority_meaning from mfg_lookups where
lookup_code=l_priority
AND lookup_type='WIP_EAM_ACTIVITY_PRIORITY';
SELECT meanv.descriptive_text, meanv.asset_criticality,instance_number
INTO l_descriptive_text, l_asset_criticality,l_asset_number
FROM mtl_eam_asset_numbers_v meanv
WHERE meanv.serial_number = l_serial_number
AND meanv.inventory_item_id = l_asset_group_id
AND meanv.CURRENT_ORGANIZATION_ID = l_organization_id;
select gb.currency_code, to_number(ho.ORG_INFORMATION3)
into l_currency, l_ou_id
from hr_organization_information ho, gl_sets_of_books gb
where gb.set_of_books_id = ho.ORG_INFORMATION1
and ho.organization_id = p_organization_id
and ho.ORG_INFORMATION_CONTEXT = 'Accounting Information';
select bd.location_id
into l_location_id
from bom_departments bd, wip_operations wo
where bd.department_id = wo.department_id
and bd.organization_id = wo.organization_id
and wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_operation_seq_num
and wo.organization_id = p_organization_id;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside create_requisitions : inserting into po_requisitions_interface_all ..'); END IF;
/* Changed for MOAC: Insert org_id as well */
insert into po_requisitions_interface_all (
interface_source_code,
destination_type_code,
authorization_status,
preparer_id, -- person id of the user name
quantity,
destination_organization_id,
deliver_to_location_id,
deliver_to_requestor_id,
source_type_code,
category_id,
item_description,
uom_code,
unit_price,
need_by_date,
wip_entity_id,
wip_operation_seq_num,
charge_account_id,
variance_account_id,
item_id,
wip_resource_seq_num,
suggested_vendor_id,
suggested_vendor_name,
suggested_vendor_site,
suggested_vendor_phone,
suggested_vendor_item_num,
currency_code,
project_id,
task_id,
project_accounting_context,
last_updated_by,
last_update_date,
created_by,
creation_date,
org_id,
reference_num,
NOTE_TO_APPROVER )
values (
'EAM',
'SHOP FLOOR',
'INCOMPLETE',
l_person_id,
p_quantity,
p_organization_id,
l_location_id,
l_person_id,
'VENDOR',
p_category_id,
p_item_description,
p_uom_code,
nvl(p_unit_price,0),
p_need_by_date,
p_wip_entity_id,
p_operation_seq_num,
l_material_account,
l_material_variance_account,
p_inventory_item_id,
p_direct_item_id,
p_suggested_vendor_id,
p_suggested_vendor_name,
p_suggested_vendor_site,
p_suggested_vendor_phone,
p_suggested_vendor_item_num,
l_currency,
l_project_id,
l_task_id,
l_project_acc_context,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_ou_id,
substrb(l_wip_entity_name, 1, 25) ,
l_asset_number||':'||l_DESCRIPTIVE_TEXT||':'||l_ASSET_CRITICALITY||':'||l_priority_meaning
);
SELECT REQIMPORT_GROUP_BY_CODE into l_req_import
FROM PO_SYSTEM_PARAMETERS_ALL where ORG_ID=l_ou_id; -- Changed for bug 6837105
SELECT wip_entity_id,
organization_id,
operation_seq_num as task_number,
to_number(null) as inventory_item_id,
direct_item_sequence_id,
1 as direct_item_type_id,
description,
required_quantity,
unit_price,
uom as uom_code,
purchasing_category_id,
need_by_date as date_required,
auto_request_material,
suggested_vendor_id,
suggested_vendor_name,
suggested_vendor_site,
suggested_vendor_phone,
suggested_vendor_item_num
FROM wip_eam_direct_items
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
UNION ALL
SELECT wro.wip_entity_id,
wro.organization_id,
wro.operation_seq_num as task_number,
wro.inventory_item_id,
to_number(null) as direct_item_sequence_id,
2 as direct_item_type_id,
msi.description,
wro.required_quantity,
wro.unit_price,
msi.primary_uom_code as uom_code,
mic.category_id as purchasing_category_id,
wro.date_required,
wro.auto_request_material,
vendor_id,
wro.suggested_vendor_name,
to_char(null) as suggested_vendor_site,
to_char(null) as suggested_vendor_phone,
to_char(null) as suggested_vendor_item_num
FROM wip_requirement_operations wro,
mtl_system_items_kfv msi,
mtl_item_categories mic ,
mtl_default_category_sets mdcs
WHERE msi.inventory_item_id = wro.inventory_item_id
AND msi.organization_id = wro.organization_id
AND nvl(msi.stock_enabled_flag, 'N') = 'N'
AND wro.inventory_item_id = mic.inventory_item_id
AND wro.organization_id = mic.organization_id
AND mic.category_set_id = mdcs.category_set_id
AND mdcs.functional_area_id = 2
AND wro.wip_entity_id = p_wip_entity_id
AND wro.organization_id = p_organization_id
);
SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
FROM
(SELECT SUM(nvl(quantity,0)) req_qty
FROM po_requisitions_interface_all pria
WHERE pria.wip_entity_id =l_di_record.wip_entity_id
AND pria.destination_organization_id = l_di_record.organization_id
AND pria.wip_operation_seq_num = l_di_record.task_number
AND pria.item_id is null
AND ( pria.wip_resource_seq_num = l_di_record.direct_item_sequence_id OR pria.wip_resource_seq_num IS NULL)
AND pria.item_description(+) = l_di_record.description
AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
UNION ALL
SELECT SUM(nvl(quantity,0)) req_qty
FROM po_requisition_lines_all prla , po_requisition_headers_all prha
WHERE prla.requisition_header_id = prha.requisition_header_id(+)
AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND prla.wip_entity_id =l_di_record.wip_entity_id
AND prla.destination_organization_id = l_di_record.organization_id
AND prla.wip_operation_seq_num = l_di_record.task_number
AND prla.item_id is null
AND ( prla.wip_resource_seq_num = l_di_record.direct_item_sequence_id OR prla.wip_resource_seq_num IS NULL)
AND prla.item_description(+) = l_di_record.description
);
SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
FROM
(SELECT SUM(nvl(quantity,0)) req_qty
FROM po_requisitions_interface_all pria
WHERE pria.wip_entity_id = l_di_record.wip_entity_id
AND pria.destination_organization_id = l_di_record.organization_id
AND pria.wip_operation_seq_num = l_di_record.task_number
AND pria.item_id = l_di_record.inventory_item_id
AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
UNION ALL
SELECT SUM(nvl(quantity,0)) req_qty
FROM po_requisition_lines_all prla , po_requisition_headers_all prha
WHERE prla.requisition_header_id = prha.requisition_header_id(+)
AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND prla.wip_entity_id = l_di_record.wip_entity_id
AND prla.destination_organization_id = l_di_record.organization_id
AND prla.wip_operation_seq_num = l_di_record.task_number
AND prla.item_id = l_di_record.inventory_item_id
);
SELECT wip_entity_id,
organization_id,
operation_seq_num as task_number,
to_number(null) as inventory_item_id,
direct_item_sequence_id,
1 as direct_item_type_id,
description,
required_quantity,
unit_price,
uom as uom_code,
purchasing_category_id,
need_by_date as date_required,
auto_request_material,
SUGGESTED_VENDOR_ID,
suggested_vendor_name,
suggested_vendor_site,
suggested_vendor_phone,
suggested_vendor_item_num
FROM wip_eam_direct_items
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND direct_item_sequence_id = p_direct_item_sequence_id;
SELECT wro.wip_entity_id,
wro.organization_id,
wro.operation_seq_num as task_number,
wro.inventory_item_id,
to_number(null) as direct_item_sequence_id,
2 as direct_item_type_id,
msi.description,
wro.required_quantity,
wro.unit_price,
msi.primary_uom_code as uom_code,
mic.category_id as purchasing_category_id,
wro.date_required,
wro.auto_request_material,
vendor_id,
wro.suggested_vendor_name,
to_char(null) as suggested_vendor_site,
to_char(null) as suggested_vendor_phone,
to_char(null) as suggested_vendor_item_num
FROM wip_requirement_operations wro,
mtl_system_items_kfv msi,
mtl_item_categories mic ,
mtl_default_category_sets mdcs
WHERE msi.inventory_item_id = wro.inventory_item_id
AND msi.organization_id = wro.organization_id
AND nvl(msi.stock_enabled_flag, 'N') = 'N'
AND wro.inventory_item_id = mic.inventory_item_id
AND wro.organization_id = mic.organization_id
AND mic.category_set_id = mdcs.category_set_id
AND mdcs.functional_area_id = 2
AND wro.wip_entity_id = p_wip_entity_id
AND wro.organization_id = p_organization_id
and wro.inventory_item_id = p_inventory_item_id;