[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old is null;
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old is null;
select wjdi.interface_id,
we.wip_entity_name,
wjdi.wip_entity_id,
wjdi.operation_seq_num,
msik.concatenated_segments item_name,
wjdi.inventory_item_id_old
from wip_job_dtls_interface wjdi,
wip_entities we,
mtl_system_items_kfv msik
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old = p_inventory_item_id_old
and wjdi.wip_entity_id = we.wip_entity_id
and wjdi.inventory_item_id_old = msik.inventory_item_id
and wjdi.organization_id = msik.organization_id
and not exists (select 1
from wip_requirement_operations wro
where wro.wip_entity_id = wjdi.wip_entity_id
and wro.organization_id = wjdi.organization_id
and wro.operation_seq_num = wjdi.operation_seq_num
and wro.inventory_item_id = wjdi.inventory_item_id_old);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old = p_inventory_item_id_old
and not exists (select 1
from wip_requirement_operations wro
where wro.wip_entity_id = wjdi.wip_entity_id
and wro.organization_id = wjdi.organization_id
and wro.operation_seq_num = wjdi.operation_seq_num
and wro.inventory_item_id = wjdi.inventory_item_id_old);
procedure safe_delete(p_group_id in number,
p_wip_entity_id in number,
p_organization_id in number,
p_substitution_type in number,
p_operation_seq_num in number,
p_inventory_item_id_old in number) IS
x_quantity_issued number;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old = p_inventory_item_id_old
and exists (select 1
from wip_requirement_operations wro
where wro.wip_entity_id = p_wip_entity_id
and wro.organization_id = p_organization_id
and wro.operation_seq_num = p_operation_seq_num
and wro.inventory_item_id = p_inventory_item_id_old
and wro.quantity_issued <> 0);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old = p_inventory_item_id_old
and exists (select 1
from mtl_material_transactions_temp mmtt
where mmtt.transaction_source_id = p_wip_entity_id
and mmtt.organization_id = p_organization_id
and mmtt.operation_seq_num = p_operation_seq_num
and mmtt.inventory_item_id = p_inventory_item_id_old);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old = p_inventory_item_id_old
and exists (select 1
from mtl_material_transactions mmt
where mmt.transaction_source_id = p_wip_entity_id
and mmt.organization_id = p_organization_id
and mmt.operation_seq_num = p_operation_seq_num
and mmt.inventory_item_id = p_inventory_item_id_old);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old = p_inventory_item_id_old
and exists (select 1
from wip_requirement_operations wro
where wro.wip_entity_id = wjdi.wip_entity_id
and wro.organization_id = wjdi.organization_id
and wro.operation_seq_num = wjdi.operation_seq_num
and wro.inventory_item_id = wjdi.inventory_item_id_old
and wro.quantity_issued > 0);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old = p_inventory_item_id_old
and exists (select 1
from mtl_material_transactions_temp mmtt
where mmtt.transaction_source_id = wjdi.wip_entity_id
and mmtt.organization_id = wjdi.organization_id
and mmtt.operation_seq_num = wjdi.operation_seq_num
and mmtt.inventory_item_id = wjdi.inventory_item_id_old);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_old = p_inventory_item_id_old
and exists (select 1
from mtl_material_transactions mmt
where mmt.transaction_source_id = wjdi.wip_entity_id
and mmt.organization_id = wjdi.organization_id
and mmt.operation_seq_num = wjdi.operation_seq_num
and mmt.inventory_item_id = wjdi.inventory_item_id_old);
end safe_delete;
/* main delete, call the above. If any validation fail, it won''t go on
with the next validations */
Procedure Delete_Req(p_group_id in number,
p_wip_entity_id in number,
p_organization_id in number,
p_substitution_type in number) IS
CURSOR req_info(p_group_Id number,
p_wip_entity_id number,
p_organization_id number,
p_substitution_type number) IS
SELECT distinct operation_seq_num,
inventory_item_id_old, inventory_item_id_new,
quantity_per_assembly,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date,
department_id, wip_supply_type, date_required,
required_quantity, quantity_issued, supply_subinventory,
supply_locator_id, mrp_net_flag, mps_required_quantity,
mps_date_required, attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type = p_substitution_type;
Safe_Delete (p_group_id,
p_wip_entity_id,
p_organization_id,
p_substitution_type,
cur_row.operation_seq_num,
cur_row.inventory_item_id_old);
END Delete_Req;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and ( wjdi.inventory_item_id_new is null
or wjdi.quantity_per_assembly is null);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and ( wjdi.inventory_item_id_new is null
or wjdi.quantity_per_assembly is null);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
/* bug#2814045 */
and nvl(wjdi.inventory_item_id_new, -1) <> nvl(wjdi.inventory_item_id_old, -1)
and ( exists (select 1
from wip_requirement_operations wro
where wro.wip_entity_id = wjdi.wip_entity_id
and wro.organization_id = wjdi.organization_id
and wro.operation_seq_num = wjdi.operation_seq_num
and wro.inventory_item_id = wjdi.inventory_item_id_new)
or exists (select 1
from wip_job_dtls_interface wjdi2
where wjdi.interface_id <> wjdi2.interface_id
and wjdi.group_id = wjdi2.group_id
and wjdi.wip_entity_id = wjdi2.wip_entity_id
and wjdi.organization_id = wjdi2.organization_id
and wjdi.operation_seq_num = wjdi2.operation_seq_num
and wjdi.inventory_item_id_new= wjdi2.inventory_item_id_new)
);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
/* Fix for Bug 5632150 */
/* and wjdi.inventory_item_id_new <> wjdi.inventory_item_id_old */
and nvl(wjdi.inventory_item_id_new,-1) <> nvl(wjdi.inventory_item_id_old,-1)
and ( exists (select 1
from wip_requirement_operations wro
where wro.wip_entity_id = wjdi.wip_entity_id
and wro.organization_id = wjdi.organization_id
and wro.operation_seq_num = wjdi.operation_seq_num
and wro.inventory_item_id = wjdi.inventory_item_id_new)
or exists (select 1
from wip_job_dtls_interface wjdi2
where wjdi.interface_id <> wjdi2.interface_id
and wjdi.group_id = wjdi2.group_id
and wjdi.wip_entity_id = wjdi2.wip_entity_id
and wjdi.organization_id = wjdi2.organization_id
and wjdi.operation_seq_num = wjdi2.operation_seq_num
and wjdi.inventory_item_id_new= wjdi2.inventory_item_id_new)
);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
and not exists (select 1
from mtl_system_items msi
where msi.inventory_item_id = wjdi.inventory_item_id_new
and msi.organization_id = wjdi.organization_id);
select wjdi.interface_id
from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
and wjdi.parent_header_id = wjsi.header_id
and wjdi.group_id = wjsi.group_id
and wjdi.organization_id = wjsi.organization_id
and wjdi.wip_entity_id = wjsi.wip_entity_id
and (wjdi.wip_supply_type = wip_constants.phantom
or (wjsi.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)
and wjdi.wip_supply_type <> wip_constants.push));
select wjdi.interface_id
from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
and wjdi.parent_header_id = wjsi.header_id
and wjdi.group_id = wjsi.group_id
and wjdi.organization_id = wjsi.organization_id
and wjdi.wip_entity_id = wjsi.wip_entity_id
and (wjdi.mrp_net_flag not in (wip_constants.yes, wip_constants.no));
select wjdi.interface_id
from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
and wjdi.parent_header_id = wjsi.header_id
and wjdi.group_id = wjsi.group_id
and wjdi.organization_id = wjsi.organization_id
and wjdi.wip_entity_id = wjsi.wip_entity_id
and (upper(wjdi.auto_request_material) not in ('Y', 'N'));
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id=p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in ( wip_constants.running,
wip_constants.warning )
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
and NOT exists ( select 1 from mtl_system_items msi
where msi.inventory_item_id = wjdi.inventory_item_id_new
and msi.organization_id = wjdi.organization_id
and BOM_ENABLED_FLAG = 'Y'
and BOM_ITEM_TYPE = 4
and (( p_profile_value = WIP_CONSTANTS.YES)
or (ENG_ITEM_FLAG = 'N' and p_profile_value = WIP_CONSTANTS.NO))
);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new;
and not exists (select 1
from mtl_system_items msi
where msi.inventory_item_id = wjdi.inventory_item_id_new
and msi.organization_id = wjdi.organization_id);
select wjdi.interface_id
from wip_job_dtls_interface wjdi /*, wip_job_schedule_interface wjsi */
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and (wjdi.inventory_item_id_new = p_inventory_item_id_new
or p_inventory_item_id_new is null)
and (wjdi.inventory_item_id_old = p_inventory_item_id_old
or p_inventory_item_id_old is null)
and (p_inventory_item_id_old is not null or
p_inventory_item_id_new is not null)
/*and (wjdi.parent_header_id = p_parent_header_id or
WIP_JOB_DETAILS.STD_ALONE = 1)*/
/*and wjdi.organization_id = wjsi.organization_id
and wjdi.wip_entity_id = wjsi.wip_entity_id*/
and wjdi.wip_supply_type = wip_constants.op_pull
and not exists
(select 1 from wip_operations
where wip_entity_id = wjdi.wip_entity_id
and organization_id = wjdi.organization_id);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where interface_id = l_inv_row.interface_id;
SELECT interface_id,inventory_item_id_new inventory_item_id,supply_subinventory,supply_locator_id,wip_supply_type
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id=p_group_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type = WIP_JOB_DETAILS.WIP_ADD
AND supply_subinventory is not null;
SELECT wjdi.interface_id,NVL(wjdi.inventory_item_id_new,wjdi.inventory_item_id_old) inventory_item_id,Decode(NVL(wjdi.wip_supply_type, wro.wip_supply_type),
2, Decode(wjdi.supply_subinventory,
NULL,
wro.supply_subinventory,
fnd_api.g_miss_char,
wro.supply_subinventory,
wjdi.supply_subinventory),
3, Decode(wjdi.supply_subinventory,
NULL,
wro.supply_subinventory,
fnd_api.g_miss_char,
wro.supply_subinventory,
wjdi.supply_subinventory),
Decode(wjdi.supply_subinventory,
NULL,
wro.supply_subinventory,
fnd_api.g_miss_char,
NULL,
wjdi.supply_subinventory)) supply_subinventory,
Decode(wjdi.supply_subinventory,
NULL,
wro.supply_locator_id,
fnd_api.g_miss_char,
Decode(NVL(wjdi.wip_supply_type, wro.wip_supply_type),
2, wro.supply_locator_id,
3, wro.supply_locator_id,
NULL),
Decode(wjdi.supply_locator_id,
fnd_api.g_miss_num,
NULL,
NULL,
wro.supply_locator_id,
wjdi.supply_locator_id)) supply_locator_id
,NVL(WJDI.wip_supply_Type,wro.wip_supply_type) wip_supply_type
FROM WIP_JOB_DTLS_INTERFACE wjdi,WIP_REQUIREMENT_OPERATIONS wro
WHERE group_id=p_group_id
AND wjdi.process_phase = WIP_CONSTANTS.ML_VALIDATION
AND wjdi.process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
AND wjdi.wip_entity_id = p_wip_entity_id
AND wro.wip_entity_id=wjdi.wip_entity_id
AND wro.operation_seq_num=wjdi.operation_seq_num
AND wro.inventory_item_id=wjdi.inventory_item_id_old
AND wjdi.organization_id = p_organization_id
AND wjdi.load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND wjdi.substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
select msi.restrict_locators_code,msi.location_control_code,
sub.locator_type, mp.stock_locator_control_code,
wdj.project_id,wdj.task_id
into l_restriclocation,
l_locationcontrol,
l_subLocCtl,
l_orgLocCtl,
l_project_id,l_task_id
from mtl_system_items msi,wip_discrete_jobs wdj,mtl_secondary_inventories sub, mtl_parameters mp
where msi.inventory_item_id = l_inventory_itemid
and msi.organization_id = p_organization_id
and wdj.organization_id=msi.organization_id
and wdj.wip_entity_id=p_wip_entity_id
and sub.secondary_inventory_name = l_subinventory
and sub.organization_id = mp.organization_id
and mp.organization_id = p_organization_id;
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where interface_id = error_tbl(indx).interface_id;
SELECT distinct operation_seq_num,
inventory_item_id_old, inventory_item_id_new,
quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date,
department_id, wip_supply_type, date_required,
required_quantity, quantity_issued,
basis_type, /* LBM Project */
supply_subinventory,
supply_locator_id, mrp_net_flag, mps_required_quantity,
mps_date_required, attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type = p_substitution_type;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
and wjdi.wip_supply_type is null;
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.inventory_item_id_new = p_inventory_item_id_new
and wjdi.wip_supply_type is null;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and ( wjdi.inventory_item_id_old is null
or (wjdi.inventory_item_id_old <> nvl(wjdi.inventory_item_id_new, wjdi.inventory_item_id_old)
and wjdi.quantity_per_assembly is null
and not exists
( select 1
from bom_substitute_components bsc
where bsc.substitute_component_id = wjdi.inventory_item_id_new
and bsc.component_sequence_id =
(select wro.component_sequence_id
from wip_requirement_operations wro
where wro.inventory_item_id = wjdi.inventory_item_id_old
and wro.wip_entity_id = wjdi.wip_entity_id
and wro.operation_seq_num = wjdi.operation_seq_num
and wro.organization_id = wjdi.organization_id
)
and bsc.acd_type is null
)
)
) ;
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and ( wjdi.inventory_item_id_old is null
or ( wjdi.inventory_item_id_old <> nvl(wjdi.inventory_item_id_new, wjdi.inventory_item_id_old)
and wjdi.quantity_per_assembly is null
)
);
SELECT start_quantity
INTO X_start_quantity
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
SELECT nvl(component_yield_factor,1),required_quantity,quantity_per_assembly
INTO x_component_yield_factor_wro,x_required_quantity_wro,x_quantity_per_assembly_wro
FROM wip_requirement_operations
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id_old;
UPDATE WIP_JOB_DTLS_INTERFACE
SET quantity_per_assembly = nvl(X_quantity_per_assembly, quantity_per_assembly),
required_quantity = x_required_quantity,
mps_required_quantity = nvl(x_mps_required_quantity,mps_required_quantity),
component_yield_factor = nvl(x_component_yield_factor,component_yield_factor)
/*Component Yield Enhancement(Bug 4369064)*/
WHERE group_id = p_group_id
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type = p_substitution_type
AND operation_seq_num = p_operation_seq_num
AND inventory_item_id_old = p_inventory_item_id_old;
SELECT distinct operation_seq_num,
inventory_item_id_old, inventory_item_id_new,
quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date,
department_id, wip_supply_type, date_required,
required_quantity, quantity_issued,
basis_type, /* LBM Project */
supply_subinventory,
supply_locator_id, mrp_net_flag, mps_required_quantity,
mps_date_required, attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type = p_substitution_type;
/* Bug 7758528. Material requirements form allows us to update transacted materials.
So call to validate for transactions/ pending transactions will be done only if user
is updating the component itself. */
IF IS_Error(p_group_id,
p_wip_entity_id,
p_organization_id,
p_substitution_type,
cur_row.operation_seq_num,
cur_row.inventory_item_id_old,
cur_row.inventory_item_id_new) = 0 AND
nvl(cur_row.inventory_item_id_new, cur_row.inventory_item_id_old) <> cur_row.inventory_item_id_old THEN
REQ_JOB_NOT_EXIST (p_group_id,
p_wip_entity_id,
p_organization_id,
p_substitution_type,
cur_row.operation_seq_num,
cur_row.inventory_item_id_new);
Safe_Delete (p_group_id,
p_wip_entity_id,
p_organization_id,
p_substitution_type,
cur_row.operation_seq_num,
cur_row.inventory_item_id_old);
IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
SELECT count(*)
INTO x_count
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_status = WIP_CONSTANTS.ERROR
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND inventory_item_id_old = p_inventory_item_id_old;
SELECT count(*)
INTO x_count
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_status = WIP_CONSTANTS.ERROR
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND inventory_item_id_new = p_inventory_item_id_new;
SELECT count(*)
INTO x_count
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_status = WIP_CONSTANTS.ERROR
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND inventory_item_id_old = p_inventory_item_id_old
AND inventory_item_id_new = p_inventory_item_id_new;
IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
SELECT count(*)
INTO x_count
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_status = WIP_CONSTANTS.ERROR
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND inventory_item_id_old IS NULL;
SELECT count(*)
INTO x_count
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_status = WIP_CONSTANTS.ERROR
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND (inventory_item_id_new IS NULL
OR quantity_per_assembly IS NULL);
SELECT count(*)
INTO x_count
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_status = WIP_CONSTANTS.ERROR
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND (inventory_item_id_old IS NULL
OR quantity_per_assembly IS NULL);