[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE wip_lpn_completions wlc
SET last_update_date = SYSDATE,
bom_revision =
(SELECT NVL(wlc.bom_revision, MAX(mir.revision))
FROM mtl_item_revisions mir
WHERE mir.organization_id = wlc.organization_id
AND mir.inventory_item_id = wlc.inventory_item_id
AND mir.effectivity_date <= SYSDATE
AND mir.effectivity_date =
(SELECT MAX(mir2.effectivity_date)
FROM mtl_item_revisions mir2
WHERE mir2.organization_id = wlc.organization_id
AND mir2.inventory_item_id = wlc.inventory_item_id
AND mir2.effectivity_date <= SYSDATE))
WHERE p_header_id = source_id
AND p_header_id <> header_id
AND bom_revision IS NULL
AND EXISTS (
SELECT 'X'
FROM mtl_system_items msi
WHERE msi.organization_id = wlc.organization_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.revision_qty_control_code = 2);
UPDATE wip_lpn_completions wlc
SET last_update_date = sysdate,
error_code = 'HOOWAA'
WHERE p_source_id = source_id
AND source_id <> header_id
AND ((bom_revision is not null
AND ( (EXISTS (
SELECT 'item is under revision control'
FROM mtl_system_items msi
WHERE msi.organization_id = wlc.organization_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.revision_qty_control_code = 2)
AND NOT EXISTS (
SELECT 'revision is effective and not an open/hold eco'
FROM bom_bill_released_revisions_v bbrrv
WHERE bbrrv.inventory_item_id = wlc.inventory_item_id
AND bbrrv.organization_id = wlc.organization_id
AND bbrrv.revision = wlc.bom_revision
AND bbrrv.effectivity_date <= SYSDATE))
OR
(EXISTS (
SELECT 'item is not under revision control'
FROM mtl_system_items msi
WHERE msi.organization_id = wlc.organization_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.revision_qty_control_code = 1))))
OR
(bom_revision IS NULL
AND (EXISTS (
SELECT 'item is under revision control'
FROM mtl_system_items msi
WHERE msi.organization_id = wlc.organization_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.revision_qty_control_code = 2)
AND NOT EXISTS (
SELECT 'any effective revision'
FROM bom_bill_released_revisions_v bbrrv
WHERE bbrrv.inventory_item_id = wlc.inventory_item_id
AND bbrrv.organization_id = wlc.organization_id
AND bbrrv.effectivity_date <= SYSDATE))));
SELECT end_assembly_pegging_flag
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT inventory_item_id, subinventory_code, locator_id, rowid
FROM wip_lpn_completions
WHERE p_header_id = source_id
AND p_header_id <> header_id
AND locator_id is not null
ORDER BY operation_seq_num;
SELECT NVL(mp.project_reference_enabled, 2),
mp.stock_locator_control_code
INTO l_proj_ref_enabled,
l_org_loc_control
FROM mtl_parameters mp
WHERE mp.organization_id = p_org_id;
UPDATE wip_lpn_completions
SET (locator_id, item_project_id, item_task_id) =
(select inventory_location_id, project_id, task_id
from mtl_item_locations
where inventory_location_id = compRec.locator_id
and organization_id = p_org_id)
WHERE rowid = compRec.rowid;
SELECT *
INTO l_wlcRec
FROM wip_lpn_completions
WHERE header_id = p_header_id;
p_last_update_date => l_wlcRec.last_update_date,
p_last_updated_by => l_wlcRec.last_updated_by ,
p_creation_date => l_wlcRec.creation_date ,
p_created_by => l_wlcRec.created_by,
p_last_update_login => l_wlcRec.last_update_login,
p_request_id => null,
p_program_application_id => l_wlcRec.program_application_id,
p_program_id => l_wlcRec.program_id,
p_program_update_date => l_wlcRec.program_update_date,
p_primary_item_id => l_wlcRec.inventory_item_id,
p_class_code => l_wlcRec.accounting_class,
p_scheduled_start_date => l_wlcRec.transaction_date,
p_date_closed => null,
p_planned_quantity => 0,
p_quantity_completed => l_wlcRec.transaction_quantity,
p_quantity_scrapped => 0,
p_mps_sched_comp_date => null,
p_mps_net_quantity => null,
p_bom_revision => l_wlcRec.bom_revision,
p_routing_revision => l_wlcRec.routing_revision,
p_bom_revision_date => l_wlcRec.bom_revision_date,
p_routing_revision_date => l_wlcRec.routing_revision_date,
p_alternate_bom_designator => l_wlcRec.alternate_bom_designator,
p_alternate_routing_designator => l_wlcRec.alternate_routing_designator,
p_completion_subinventory => l_wlcRec.subinventory_code,
p_completion_locator_id => l_wlcRec.locator_id,
p_demand_class => null,
p_scheduled_completion_date => l_wlcRec.transaction_date,
p_schedule_group_id => null,
p_build_sequence => null,
p_line_id => null,
p_project_id => null,
p_task_id => null,
p_status => 1, --open
p_schedule_number => 'WMALPNWOL' || TO_CHAR(l_wlcRec.header_id),
p_scheduled_flag => 2, --not scheduled
p_unit_number => l_wlcRec.end_item_unit_number,
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null);
UPDATE WIP_LPN_COMPLETIONS
SET wip_entity_id = l_wlcRec.wip_entity_id,
last_update_date = sysdate
WHERE l_wlcRec.header_id = header_id
AND l_wlcRec.header_id = source_id;