[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 = nvl(p_user_id,fnd_global.user_id);
SELECT wdj.project_id, wdj.task_id, we.wip_entity_name, wdj.maintenance_object_id,
wdj.maintenance_object_type, wdj.organization_id, wdj.priority
INTO l_project_id,l_task_id, l_wip_entity_name, l_maintenance_object_id,
l_maintenance_object_type, 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 count(category_id) into l_available from cst_cat_ele_exp_assocs_v cceav
where category_id = p_category_id and
nvl(cceav.start_date,sysdate-1) <= sysdate and
nvl(cceav.end_date,sysdate+1) >= sysdate;
select decode(cceav.mfg_cost_element_id,
1,wac.material_account,
2,wac.material_overhead_account,
3,wac.resource_account,
4,wac.outside_processing_account,
5,wac.overhead_account,wac.material_account) account_id,
decode(cceav.mfg_cost_element_id,
1,wac.material_variance_account,
2,wac.material_overhead_account,
3,wac.resource_variance_account,
4,wac.outside_proc_variance_account,
5,wac.overhead_variance_account,wac.material_variance_account) variance_account_id
into l_material_account,l_material_variance_account
from cst_cat_ele_exp_assocs_v cceav, wip_accounting_classes wac,wip_discrete_jobs wdj
where wdj.organization_id = p_organization_id and
wdj.wip_entity_id = p_wip_entity_id and
wac.class_code = wdj.class_code and
wac.organization_id= wdj.organization_id and
cceav.category_id =p_category_id and
nvl(cceav.start_date,sysdate-1) <= sysdate and
nvl(cceav.end_date,sysdate+1) >= sysdate;
select wdj.material_account, wdj.material_variance_account
into l_material_account,l_material_variance_account
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id and
wdj.organization_id = p_organization_id;
select meaning into l_priority_meaning from mfg_lookups where
lookup_code=l_priority
AND lookup_type='WIP_EAM_ACTIVITY_PRIORITY';
SELECT serial_number, inventory_item_id, last_vld_organization_id
INTO l_serial_number, l_asset_group_id, l_asset_cur_org_id
FROM csi_item_instances
WHERE instance_id =l_maintenance_object_id;
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 = nvl(l_asset_cur_org_id, l_organization_id);
SELECT msib.description
INTO l_descriptive_text
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id= l_maintenance_object_id
AND msib.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(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : 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,
-- For bug# 14163019, Variance Account would be defaulted from INV params when not inserted
-- 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,
-- For bug# 14163019
-- 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,
nvl(p_user_id,fnd_global.user_id),
sysdate,
nvl(p_user_id,fnd_global.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(pria.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
AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
UNION ALL
SELECT SUM(nvl(prla.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 UPPER(NVL(prla.cancel_flag,'N')) <> 'Y'
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
UNION ALL
SELECT SUM(nvl(pd.quantity_ordered,0)) req_qty
FROM po_distributions_all pd , po_headers_all ph,po_lines_all pl
WHERE pd.po_header_id = ph.po_header_id(+)
AND upper(NVL(ph.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND pd.po_line_id = pl.po_line_id(+)
AND UPPER(NVL(pl.cancel_flag,'N')) <> 'Y'
AND pd.wip_entity_id = l_di_record.wip_entity_id
AND pd.destination_organization_id = l_di_record.organization_id
AND pd.wip_operation_seq_num = l_di_record.task_number
AND pl.item_id is null
AND pd.wip_resource_seq_num = l_di_record.direct_item_sequence_id
AND pd.line_location_id not in(
SELECT nvl(prla.line_location_id,0)
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 UPPER(NVL(prla.cancel_flag,'N')) <> 'Y'
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)
);
SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
FROM
(SELECT SUM(nvl(pria.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
AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
UNION ALL
SELECT SUM(nvl(prla.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 ('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
UNION ALL
SELECT SUM(nvl(pd.quantity_ordered,0)) req_qty
FROM po_distributions_all pd , po_headers_all ph,po_lines_all pl
WHERE pd.po_header_id = ph.po_header_id(+)
AND upper(NVL(ph.authorization_status, 'APPROVED') ) not in ('SYSTEM_SAVED')
AND pd.po_line_id = pl.po_line_id(+)
AND pd.wip_entity_id = l_di_record.wip_entity_id
AND pd.destination_organization_id = l_di_record.organization_id
AND pd.wip_operation_seq_num = l_di_record.task_number
AND pl.item_id is null
AND pd.wip_resource_seq_num = l_di_record.direct_item_sequence_id
AND pd.line_location_id not in(
SELECT nvl(prla.line_location_id,0)
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 ('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)
);
SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
FROM
(SELECT SUM(nvl(pria.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 pria.wip_resource_seq_num is null
AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
UNION ALL
SELECT SUM(nvl(prla.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 UPPER(NVL(prla.cancel_flag,'N')) <> 'Y'
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
AND prla.wip_resource_seq_num is null
UNION ALL
SELECT SUM(nvl(pd.quantity_ordered,0)) req_qty
FROM po_distributions_all pd , po_headers_all ph,po_lines_all pl
WHERE pd.po_header_id = ph.po_header_id(+)
AND upper(NVL(ph.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND UPPER(NVL(pl.cancel_flag,'N')) <> 'Y'
AND pd.po_line_id = pl.po_line_id(+)
AND pd.wip_entity_id = l_di_record.wip_entity_id
AND pd.destination_organization_id = l_di_record.organization_id
AND pd.wip_operation_seq_num = l_di_record.task_number
AND pl.item_id = l_di_record.inventory_item_id
AND pd.wip_resource_seq_num is null
AND pd.line_location_id not in(
SELECT nvl(prla.line_location_id,0)
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 UPPER(NVL(prla.cancel_flag,'N')) <> 'Y'
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 pl.item_id = l_di_record.inventory_item_id
AND prla.wip_resource_seq_num is null)
);
SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
FROM
(SELECT SUM(nvl(pria.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 pria.wip_resource_seq_num is null
AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
UNION ALL
SELECT SUM(nvl(prla.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 ('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
AND prla.wip_resource_seq_num is null
UNION ALL
SELECT SUM(nvl(pd.quantity_ordered,0)) req_qty
FROM po_distributions_all pd , po_headers_all ph,po_lines_all pl
WHERE pd.po_header_id = ph.po_header_id(+)
AND upper(NVL(ph.authorization_status, 'APPROVED') ) not in ('SYSTEM_SAVED')
AND pd.po_line_id = pl.po_line_id(+)
AND pd.wip_entity_id = l_di_record.wip_entity_id
AND pd.destination_organization_id = l_di_record.organization_id
AND pd.wip_operation_seq_num = l_di_record.task_number
AND pl.item_id = l_di_record.inventory_item_id
AND pd.wip_resource_seq_num is null
AND pd.line_location_id not in(
SELECT nvl(prla.line_location_id,0)
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 ('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 pl.item_id = l_di_record.inventory_item_id
AND prla.wip_resource_seq_num is null)
);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : Description Direct Item Update'); END IF;
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;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : Non-Stock Direct Item Update'); END IF;
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;